How to replace enter in oracle

 select translate(your_column, chr(10)||chr(11)||chr(13), ' ') from your_table;

 

Oracle Purchasing Tables

Usual Oracle Applications PO Tables
PO_HEADERS_ALL: Purchase Order information with Supplier, Site and status
PO_LINES_ALL: PO Lines with Item Information and quantity
PO_LINE_LOCATIONS_ALL: PO Information on Shipments Destination
PO_DISTRIBUTIONS_ALL: PO Distribution where the accounts are
PO_LINE_LOCATIONS_ARCHIVE_ALL: table updated for history on Shipment/Destination Location Information
PO_LINE_TYPES_B: PO Types used in the PO_LINES_ALL
PO_LINES_TYPES_TL: PO Line Types used in MLS
PO_RELEASES_ALL: Stores revision number for the PO
PO_HEADERS_ARCHIVE_ALL: table updated for the history on the status and PO Header changes, Lines, Location and PO Accounts Distribution
PO_LINES_ARCHIVE_ALL: table updated for the history on the lines
PO_DISTRIBUTIONS_ARCHIVE_ALL: table updated for the history on the account distribution
PO_AGENTS_V: Buyer
PO_VENDORS: Supplier Table
PO_VENDOR_SITES: Supplier Sites
PO_VENDOR_CONTACTS: Supplier Contacts

PO_HAZARD_CLASSES: contains code and description for hazardous items which gets automatically printed into purchase order, RFQ and Receipt Travelers

PO_REQUISITION_LINES_ALL: Requisition Lines
PO_REQUISITION_HEADERS_ALL: Requisition Headers
PO_REQ_DISTRIBUTIONS_ALL: Distribution Lines of Requisition where accounts are
PO_APPROVED_SUPPLIER_LIST: Supplier List for Auto-Sourcing
PO_ASL_DOCUMENTS: Advanced Shipment Documents
PO_APPROVAL_LIST_HEADERS: PO Approval Path
PO_APPROVAL_LIST_LINES: PO Approval Path


OTHER RELATED:
MRP_SOURCING_RULES: Used for Auto-Sourcing Rules
MRP_SR_RECEIPT_ORG: Used for Auto-Sourcing Rules
MRP_SR_SOURCE_ORG: Used for Auto-Sourcing Rules
MRP_ASSIGNMENT_SETS: Used for Auto-Sourcing Rules
MRP_SR_ASSIGNMENTS: Used for Auto-Sourcing Rules
RCV_SHIPMENT_HEADERS: Shipment Table Header with grouping information
RCV_SHIPMENT_LINES: Shipment Table lines with item information
RCV_TRANSACTIONS: PO Lines or Requisition received in destination or transit
RCV_ACCOUNTING_EVENTS: Receiving information on accounts
RCV_RECEIVING_SUB_LEDGER: Accounting entries generated for the receiving transactions
RCV_SUB_LEDGER_DETAILS: Detail accounting entries generated for the receiving transactions
RCV_LOT_SUPPLY: Parent for RCV_LOT_TRANSACTIONS
RCV_LOT_TRANSACTIONS: Table housing the information what lot the item is received

INTERFACES:
PO_HEADERS_INTERFACE: Used for creating PO
PO_LINES_INTERFACE: Used for creating PO
PO_DISTRIBUTIONS_INTERFACE: Used for creating PO
PO_REQUISITIONS_INTERFACE: Used for creating Requisition
PO_REQ_DIST_INTERFACE: Used for creating Requisition
PO_INTERFACE_ERRORS: Error created during the processing of the PO or requisition
RCV_INTERFACE: Used for creating Received Items
RCV_HEADERS_INTERFACE: Used for creating Received Group of items
RCV_LOT_INTERFACE: Used for receiving item into a particular lot

How to clean Oracle temp table when it is full

How to clean Oracle temp table when it is full

1. Check status

SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM V$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name


2. Clean up

alter tablespace temp default storage(pctincrease 5);
//to have smon clean up temp tablespace,
alter tablespace temp default storage(pctincrease 0);

How to export Assets data from AP to FA

How to export Assets data from AP to FA

In order to transfer a expense line from AP to FA - (1) Track as Asset flag should be enabled (AP Invoice Lines)
(2) AP line distribution (expense) account should match with the clearing account specified in FA Book controls or category definition
(3) Transfer to GL flag should be Y (you can transfer to FA only after AP to GL transfer is complete)

Program:
Mass Additions Create
Select the FA Book and Period

Data will be transferred to FA as New records in Mass additions table.

Update the record as Assetized and assign FA Category,update the depreciation account, update employee assignments and Location (if any).

Oracle EBS - ap_suppliers to hz_parties

Oracle EBS - AP_SUPPLIER_CONTACTS was not used in R12

AP_SUPPLIER_CONTACTS will be instead of below tables.

HZ_PARTIES

HZ_RELATIONHIPS

HZ_ORG_CONTACTS

hz_contact_points 

The link column in AP_SUPPLIER_CONTACTS map tp the following tables/columns

Per_Party_ID = Party_ID of PERSON Party in HZ_PARTIES
Relationship_ID = Relationship_ID of Rows in HZ_RELATIONHIPS
Rel_Party_ID = Party_ID of PARTY_RELATIONSHIP Party in HZ_PARTIES
Party_Site_ID = Maps to the Party Site created for the PARTY_RELATIONSHIP Party
Org_Contact_ID = Org_Contact_ID from HZ_ORG_CONTACTS
Org_Party_Site_ID = Party_Site_ID of the Supplier Site row.

Assuming you know the party_site_id for an address (which is the value in AP_SUPPLIER_SITES_ALL.party_Site_ID for a site that is associated with that address) you can use this query to get the contact for that address.

SELECT PERSON.person_first_name, PERSON.person_last_name, PTY_REL.address1,
PTY_REL.City, PTY_REL.state, PTY_REL.country,
PTY_REL.Primary_phone_area_code, PTY_REL.primary_phone_number
FROM hz_parties PERSON, hz_parties PTY_REL, ap_supplier_contacts APSC
WHERE APSC.per_party_id = PERSON.party_id
AND APSC.rel_party_id = PTY_REL.party_id
AND APSC.org_party_site_id = <Party_Site_Address_ID>

Oracle Forms - How to create an Alert or Message

How to create an Alert or Message