Wednesday, October 25, 2017

Important Oracle Apps Tables

Trading Partners Tables
=======================
select * from ece_tp_headers;
select * from ece_tp_details;

db objects Tales
====================
select * from dba_objects;
select * from user_objects;
select * from all_source;

Server Directories Tables
===========================
select * from v$parameter where name like '%utl_file%';

URL table
===============
select * from icx_parameters;

Instance Name Table
===================
 select INSTANCE_NAME  from V$INSTANCE;

Interface Tables/Programs in Oracle Apps R12

Interface Tables/Programs in Oracle Apps R12

=======================================================================
PO Requisition Creation
=======================================================================
## Interface Tables:

PO_REQUISITIONS_INTERFACE_ALL

## Error Table
PO_INTERRFACE_ERRORS

## Base Tables:

PO_REQUISITIONS_HEADERS_ALL                      
PO_REQUISITION_LINES_ALL      
PO_REQ_DISTRIBUTIONS_ALL

## Oracle Seeded Concurrent Program

   Requisition Import
 
=======================================================================
Purchase Orders Creation
=======================================================================
## Interface Tables:

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE

## Error Table
PO_INTERRFACE_ERRORS

## Base Tables:

PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL

## Oracle Seeded Concurrent Program

Import Standard Purchase Orders

=======================================================================
Sales Orders Creation
=======================================================================
## Interface Tables:

OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL

## Base Tables:

OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL

## Oracle Seeded Concurrent Program

 Order Import

=======================================================================
Price List Creation
=======================================================================
## Interface Tables:

QP_INTERFACE_LIST_HEADERS
QP_INTERFACE_LIST_LINES
QP_INTERFACE_PRICING_ATTRIBS

QP_INTERFACE_ERRORS

## Base Tables:

QP_LIST_HEADERS
QP_LIST_LINES
QP_PRICING_ATTRIBUTES

## Oracle Seeded Concurrent Program

QP: Bulk Import of Price List

=======================================================================
Customers Creation
=======================================================================
## Interface Tables:

RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL


## Base Tables:

HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_CUST_ACCOUNTS_ALL
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUSTOMER_PROFILES
HZ_CUST_PROFILE_CLASSES
HZ_PARTY_RELATIONSHIPS
HZ_CONTACT_POINTS
HZ_ORG_CONTACTS

## Oracle Seeded Concurrent Program

Customer Interface

=======================================================================
Items Creation
=======================================================================
## Interface Tables:

MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
MTL_INTERFACE_ERRORS

## Base Tables:

MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES
   
## Oracle Seeded Concurrent Program

   Item Import
=======================================================================
Suppliers Creation
=======================================================================
## Interface Tables:

AP_SUPPILERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACTS_INT
AP_SUPPLIER_INT_REJECTIONS

## Base Tables:

AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

## Oracle Seeded Concurrent Program

Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import

This Post will give you the insert script for inserting data into Price list import interface tables.

INSERT INTO QP_INTERFACE_LIST_HEADERS (
ORIG_SYS_HEADER_REF,
LIST_TYPE_CODE,
NAME,
DESCRIPTION,
CURRENCY_CODE,
ACTIVE_FLAG,
CURRENCY_HEADER_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ROUNDING_FACTOR,
SOURCE_LANG,
LANGUAGE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_HEADER',
'PRL',
'SAMPLE_BLK_PL',
'SAMPLE BULK LOADED PRICE LIST',
'USD',
'Y',
3,
SYSDATE,
NULL,
-1,
'US',
'US',
'INSERT',
'Y',
'P'
)
/

/* Insert Price list line details into QP_INTERFACE_LIST_LINES table. */
INSERT INTO QP_INTERFACE_LIST_LINES (
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
LIST_LINE_TYPE_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ARITHMETIC_OPERATOR,
OPERAND,
PRIMARY_UOM_FLAG,
PRODUCT_PRECEDENCE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_LINE',
'SAMPLE_HEADER',
'PLL',
SYSDATE,
NULL,
'UNIT_PRICE',
100,
'Y',
230,
'INSERT',
'Y',
'P'
)
/


/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */
INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (
ORIG_SYS_PRICING_ATTR_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTR_CODE,
PRODUCT_ATTR_VAL_DISP,
PRODUCT_UOM_CODE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_PATTR',
'SAMPLE_LINE',
'SAMPLE_HEADER',
'ITEM',
'INVENTORY_ITEM_ID',
'123',
'KG',
'INSERT',
'Y',
'P'
)
/

COMMIT;
/


Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...