Oracle Tables information website - eTRM - Technical Reference

 eTRM - Technical Reference 

see information through below website

http://etrm.oracle.com/pls/trm1222p/etrm_fndnav.ls_object

Insert query into Form Personalization items and get profile value

 =(select distinct(legal_entity)  from org_organization_definitions where operating_unit=FND_PROFILE.VALUE ('ORG_ID'))

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');

   fnd_profile.value('MFG_ORGANIZATION_ID');

   fnd_profile.value('ORG_ID');

   fnd_profile.value('LOGIN_ID');

   fnd_profile.value('USER_ID');

   fnd_profile.value('USERNAME');

   fnd_profile.value('CONCURRENT_REQUEST_ID');

   fnd_profile.value('GL_SET_OF_BKS_ID');

   fnd_profile.value('SO_ORGANIZATION_ID');

   fnd_profile.value('APPL_SHRT_NAME');

   fnd_profile.value('RESP_NAME');

   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;

   FND_GLOBAL.APPS_INTIALIZE;

   FND_GLOBAL.LOGIN_ID;

   FND_GLOBAL.CONC_LOGIN_ID;

   FND_GLOBAL.PROG_APPL_ID;

   FND_GLOBAL.CONC_PROGRAM_ID;

   FND_GLOBAL.CONC_REQUEST_ID;

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