Infolinks

Showing posts with label 02C. Show all posts
Showing posts with label 02C. Show all posts

Friday, 22 June 2012

O2C,P2P


==============================================================================================

O2C CYCLE

==============================================================================================

SELECT OOHA.ORG_ID
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
QP_LIST_HEADERS QLH,
RA_SALESREPS RS,
OE_TRANSACTION_TYPES_TL OTTT,
PER_ALL_PEOPLE_F PAPF,
AR_CUSTOMERS AC,
HR_LOCATIONS HL,
HR_LOCATIONS HL1,
/*WSH_TRIPS WT,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_LEGS WDL,*/
MTL_TXN_REQUEST_HEADERS MTRH,
MTL_TXN_REQUEST_LINES MTRL,
RA_CUSTOMER_TRX_ALL RCT,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA,
RA_CUST_TRX_TYPES_ALL RCTTA,
RA_INTERFACE_LINES_ALL RILA,     --WND.DELIVERY_ID,RILA.INTERFACE_LINE_ATTRIBUTE1
RA_INTERFACE_DISTRIBUTIONS_ALL RIDA,  --RIDA.INTERFACE_LINE_ID,RILA.INTERFACE_LINE_ID
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_CASH_RECEIPTS_ALL ACRA,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL,
GL_JE_BATCHES GLB,
--GL_SET_OF_BOOKS GSOB,
GL_BALANCES GB,
GL_IMPORT_REFERENCES GLIMP,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XEV,
ORG_ORGANIZATION_DEFINITIONS OOD,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_ONHAND_QUANTITIES MOQ,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_ITEM_CATEGORIES MIC
WHERE  XAH.APPLICATION_ID=XEV.APPLICATION_ID
AND    XEV.EVENT_ID=XAH.EVENT_ID
AND XAL.APPLICATION_ID=XAH.APPLICATION_ID
AND  XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
AND  XEV.APPLICATION_ID=XDL.APPLICATION_ID
AND XAL.AE_LINE_NUM=XDL.AE_LINE_NUM
AND XAH.AE_HEADER_ID=XDL.AE_HEADER_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE='RCV_RECEVING_SUB_LEDGER'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1='3002'
AND GLIMP.JE_HEADER_ID=GLH.JE_HEADER_ID
AND GLIMP.JE_LINE_NUM=GLL.JE_LINE_NUM
AND GLIMP.JE_BATCH_ID=GLB.JE_BATCH_ID
AND GLH.JE_HEADER_ID=GLL.JE_HEADER_ID
AND GLH.JE_BATCH_ID=GLB.JE_BATCH_ID
AND GLIMP.GL_SL_LINK_ID='245133'
AND GLIMP.GL_SL_LINK_TABLE='XLAJEL'
AND OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND QLH.LIST_HEADER_ID=OOLA.PRICE_LIST_ID
AND RS.SALESREP_ID=OOLA.SALESREP_ID
AND OTTT.TRANSACTION_TYPE_ID=OOLA.ORDERED_ITEM_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_TO_ORG_ID
AND HL.LOCATION_ID=OOLA.INVOICE_TO_ORG_ID
AND HL1.LOCATION_ID=OOHA.SHIP_FROM_ORG_ID
AND OOD.ORGANIZATION_ID=OOHA.SHIP_FROM_ORG_ID
AND OOD.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID=MMT.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID=MOQ.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID=MIC.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID=MIC.INVENTORY_ITEM_ID
AND OOHA.ORDER_NUMBER=RCT.INTERFACE_HEADER_ATTRIBUTE1
AND RCT.CUSTOMER_TRX_ID=RCTLA.INTERFACE_LINE_ATTRIBUTE1
AND RCTLA.CUSTOMER_TRX_LINE_ID=RCTLGDA.CUSTOMER_TRX_LINE_ID
AND RCTLA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND XAL.GL_SL_LINK_ID=GLIMP.GL_SL_LINK_ID
AND RCTLA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND OOHA.CUST_PO_NUMBER=ACRA.RECEIPT_NUMBER

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE '%XLA_TR%'
===================================================================================

 P2P CYCLE

===================================================================================

SELECT
FROM
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
RCV_TRANSACTIONS RT,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
PO_VENDOR_CONTACTS PVC,
HR_LOCATIONS HL,
AP_INVOICES_ALL AIA,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_TERMS AT,
AP_PAYMENT_SCHEDULES_ALL APSA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
AP_ACCOUNTING_EVENTS_ALL AAEA,
AP_AE_HEADERS_ALL AAHA,
AP_AE_LINES_ALL AALA,
AP_BANK_BRANCHES ABB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL,
GL_JE_BATCHES GLB,
GL_SET_OF_BOOKS GSOB,
GL_BALANCES GB,
GL_IMPORT_REFERENCES GLIMP,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XEV,
ORG_ORGANIZATION_DEFINITIONS OOD,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_ONHAND_QUANTITIES MOQ,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_ITEM_CATEGORIES MIC
WHERE  PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND    PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND    PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND    PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND    PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND    PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND    PHA.PO_HEADER_ID=RT.PO_HEADER_ID
AND    RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND    RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND    RSH.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND    PDA.REQ_DISTRIBUTION_ID=RSL.REQ_DISTRIBUTION_ID
AND    PV.VENDOR_ID=PHA.VENDOR_ID
AND    PVS.VENDOR_SITE_ID=PHA.VENDOR_SITE_ID
AND    PVC.VENDOR_CONTACT_ID=PHA.VENDOR_CONTACT_ID
AND    PDA.PO_DISTRIBUTION_ID=AIDA.PO_DISTRIBUTION_ID
AND    AIA.INVOICE_ID=AILA.INVOICE_ID
AND    AIA.INVOICE_ID=AIPA.INVOICE_ID
AND    AIPA.CHECK_ID=ACA.CHECK_ID
AND    AIA.INVOICE_ID=APSA.INVOICE_ID
AND    XAH.APPLICATION_ID=XEV.APPLICATION_ID
AND    XEV.EVENT_ID=XAH.EVENT_ID
AND XAL.APPLICATION_ID=XAH.APPLICATION_ID
AND  XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
AND  XEV.APPLICATION_ID=XDL.APPLICATION_ID
AND XAL.AE_LINE_NUM=XDL.AE_LINE_NUM
AND XAH.AE_HEADER_ID=XDL.AE_HEADER_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE='RCV_RECEVING_SUB_LEDGER'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1='3002'
AND GLIMP.JE_HEADER_ID=GLH.JE_HEADER_ID
AND GLIMP.JE_LINE_NUM=GLL.JE_LINE_NUM
AND GLIMP.JE_BATCH_ID=GLB.JE_BATCH_ID
AND GLH.JE_HEADER_ID=GLL.JE_HEADER_ID
AND GLH.JE_BATCH_ID=GLB.JE_BATCH_ID
AND GLIMP.GL_SL_LINK_ID='245133'
AND GLIMP.GL_SL_LINK_TABLE='XLAJEL'
AND OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND QLH.LIST_HEADER_ID=OOLA.PRICE_LIST_ID
AND RS.SALESREP_ID=OOLA.SALESREP_ID
AND OTTT.TRANSACTION_TYPE_ID=OOLA.ORDERED_ITEM_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_TO_ORG_ID
AND HL.LOCATION_ID=OOLA.INVOICE_TO_ORG_ID
AND HL1.LOCATION_ID=OOHA.SHIP_FROM_ORG_ID
AND OOD.ORGANIZATION_ID=OOHA.SHIP_FROM_ORG_ID
AND OOD.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID=MMT.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID=MOQ.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID=MIC.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID=MIC.INVENTORY_ITEM_ID


=====================================================================================
O2C  FLOW 2ND(ENTER,BOOK,PICK RELEASE,SHIP CONFIRM,AUTO INVOICE,TRANSACTIONS,CASH RECEIPTS,GL)

SELECT OOHA.ORDER_NUMBER,OOLA.ORDERED_QUANTITY,OOHA.FLOW_STATUS_CODE,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
MTL_TXN_REQUEST_HEADERS MTRH,
MTL_TXN_REQUEST_LINES MTRL,
WSH_DELIVERY_LEGS WDL,
WSH_TRIPS WT,
WSH_TRIP_STOPS WTS,
RA_INTERFACE_LINES_ALL RILA,
RA_INTERFACE_DISTRIBUTIONS_ALL RIDA,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA,
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_CASH_RECEIPTS_ALL ACRA,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
GL_BALANCES GB,
GL_IMPORT_REFERENCES GLIMP,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XEV,QP_LIST_HEADERS QLH,
RA_SALESREPS_ALL RS,
OE_TRANSACTION_TYPES_TL OTTT,
PER_ALL_PEOPLE_F PAPF,
AR_CUSTOMERS AC,
HR_LOCATIONS_ALL HL
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND   OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND   WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND   WDA.DELIVERY_ID=WND.DELIVERY_ID
AND   WND.DELIVERY_ID=RILA.INTERFACE_LINE_ID
AND   RILA.INTERFACE_LINE_ID=RIDA.INTERFACE_LINE_ID
AND   OOHA.ORDER_NUMBER=RCTA.INTERFACE_HEADER_ATTRIBUTE1
AND    XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND    XAL.AE_LINE_NUM=XDL.AE_LINE_NUM
AND    XDL.APPLICATION_ID=XTE.APPLICATION_ID
AND    XTE.ENTITY_ID=XEV.APPLICATION_ID
AND    XAH.ENTITY_ID=XDL.AE_HEADER_ID
AND    XDL.EVENT_ID=XEV.ENTITY_ID
AND    XAH.ENTITY_ID=XTE.APPLICATION_ID
AND    XAL.GL_SL_LINK_ID=GLIMP.GL_SL_LINK_ID
AND    GLIMP.JE_HEADER_ID=GJH.JE_HEADER_ID
AND    GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND    GLIMP.JE_LINE_NUM=GJL.JE_LINE_NUM
AND    GJH.JE_BATCH_ID=GJB.JE_BATCH_ID
AND QLH.LIST_HEADER_ID=OOLA.PRICE_LIST_ID
AND RS.SALESREP_ID=OOLA.SALESREP_ID
AND OTTT.TRANSACTION_TYPE_ID=OOLA.ORDERED_ITEM_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_TO_ORG_ID
AND HL.LOCATION_ID=OOLA.INVOICE_TO_ORG_ID
AND RCTA.CUSTOMER_TRX_ID=RCTLA.INTERFACE_LINE_ATTRIBUTE1
AND RCTLA.CUSTOMER_TRX_LINE_ID=RCTLGDA.CUSTOMER_TRX_LINE_ID
AND RCTLA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND ACRA.RECEIPT_NUMBER=RCTA.CUSTOMER_TRX_ID
AND XAL.GL_SL_LINK_ID=GLIMP.GL_SL_LINK_ID
AND RCTLA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND OOHA.CUST_PO_NUMBER=ACRA.RECEIPT_NUMBER
AND XDL.SOURCE_DISTRIBUTION_ID_CHAR_1='RA_CUST_TRX_LINE_GL_DIST_ALL'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1='CUST_TRX_LINE_GL_DIST_ID'

AND OOHA.ORDER_NUMBER='66404'

==============================================================

select * from all_tables  where table_name like 'WSH_TRIP_%' AND TABLESPACE_NAME LIKE 'APPS_%';

HR_ITEM_PROPERTIES_B

HR_ITEM_PROPERTIES_TL

HR_LOCATIONS_ALL

HR_LOCATION_EXTRA_INFO

HR_LOCATION_INFO_TYPES

HR_S_PAYMENT_TYPES

HR_S_USER_ENTITIES

HR_TEMPLATE_ITEMS_B

HR_TEMPLATE_TAB_PAGES_B

HR_TEMPLATE_WINDOWS_B

HR_TRANSACTION_SNAPSHOT

HR_WORKFLOWS_TEMP

HR_WORKING_PERSON_LISTS

HR_ASSIGNMENT_SETS

HR_OWNER_DEFINITIONS

HR_WORKFLOWS

HR_LOCATIONS_ALL_TL

HR_ALL_ORGANIZATION_UNITS_TL

Sunday, 27 May 2012

APPS BASE,INTERFACE TABLES IN (AP,PO,OM,AR,GL,SLA)

APPS BASE,INTERFACE TABLES IN (AP,PO,OM,AR,GL,SLA)
=======

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'AP_E%ALL' AND OWNER='AP'

PAYABLES INTERFACE TABLES
==========================
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT


PAYABLES BASE TABLES
======================
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_SCHEDULES_ALL
AP_PAYMENT_HISTORY_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
AP_BATCHES_ALL
AP_TERMS
AP_LOOKUP_CODES
AP_ACCOUNTING_EVENTS_ALL
AP_BANK_ACCOUNTS_ALL
AP_BANK_ACCOUNT_USES_ALL
AP_CHECKS_ALL
AP_HOLDS_ALL
AP_CARD_CODES
AP_CARDS_ALL
AP_CARD_CODES_ALL
AP_CARD_CODE_SETS_ALL
AP_EXPENSE_REPORTS_ALL
AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL
AP_EXP_REPORT_DISTS_ALL

====================================================================================

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'PO_%E_ALL' AND OWNER='PO'



PO INTERFACE TABLES
====================

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_LINE_LOCATIONS_INTERFACE
PO_DISTRIBUTIONS_INTERFACE

PO REQ INTERFACE TABLES
=======================

PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL

PO BASE TABLES
=================

PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_ALL
PO_RELEASES_ALL
PO_AGENTS
PO_ACTION_HISTORY

AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

PO REQ BASE TABLES
=======================
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

RECEIPTS BASE TABLES
====================

RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS


=======================================================================================


SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'FND_A%L' --AND OWNER='ONT'


OM INTERFACE TABLES
====================

OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL


OM BASE TABLES
====================

OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
OE_ORDER_HOLDS_ALL
OE_ORDER_SOURCES
OE_TRANSACTION_TYPES_TL/ALL
OE_PAYMENT_TYPES_TL

WSH_DELIVERY_DETAILS
WSH_NEW_DELIVERIES
WSH_DELIVERY_ASSIGNMENTS
OE_ORDER_HOLDS
QP_LIST_HEADERS
RA_SALESREPS
OE_TRANSACTION_TYPES_TL
AR_CUSTOMERS
RA_TERMS




====================================================================================


SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'FND_PRO%S' AND OWNER='APPS'

OTHER BASE TABLES
===================

FND_USER
FND_APPLICATION_VL
FND_RESPONSIBILITY_VL
FND_CONCURRENT_PROGRAMS
FND_EXECUTABLES
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_REQUESTS
FND_DATA_GROUPS
FND_SEGMENT_ATTRIBUTE_TYPES
FND_SEGMENT_ATTRIBUTE_VALUES
FND_TABLES
FND_TABLESPACES
FND_FORM
FND_PROFILE_OPTIONS
FND_PROFILE_OPTION_VALUES
FND_PRODUCT_INSTALLATIONS
FND_PRODUCT_GROUPS
FND_PRODUCT_DEPENDENCIES
ORG_ORGANIZATION_DEFINITIONS
PER_ALL_PEOPLE_F(ENAME)
HR_LOCATIONS(BILL_TO,SHIP_TO)

============================================


SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'AR_P%L' AND OWNER='AR'


TCA(TRADING COMMUNITY ARCHITECTURE TABLES)
============================================


HZ_PARTIES
HZ_PARTY_SITES
HZ_CUST_ACCOUNTS
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUSTOMER_PROFILES
HZ_PARTY_RELATIONSHIPS
HZ_CUST_PROFILE_CLASSES
HZ_LOCATIONS
RA_TERMS
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_CUST_TRX_TYPES_ALL
HZ_CONTACT_POINTS
HZ_CUST_CONTACT_POINTS
AR_CUSTOMERS
AR_PAYMENT_SCHEDULES_ALL
AR_ADJUSTMENTS_ALL
AR_DISTRIBUTIONS_ALL
AR_TRANSACTION_HISTORY_ALL
AR_CASH_RECEIPTS_ALL
AR_DISTRIBUTION_SETS_ALL
AR_RECEIVABLES_TRX_ALL
AR_PERIODS
AR_PERIOD_TYPES
AR_VAT_TAX_ALL

RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS

INTERFACE TABLES OF AR
=======================

HZ_PARTY_INTERFACE
HZ_PARTY_INTERFACE_ERRORS
RA_INTERFACE_LINES_ALL
RA_INTERFACE_ERRORS_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_BANKS_INT_ALL
AR_PAYMENTS_INTERFACE_ALL
AR_TAX_INTERFACE
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE

==============================================


SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'GL_I%E' AND OWNER='GL'

GL BASE TABLES
===============

GL_JE_HEADERS
GL_JE_LINES
GL_JE_BATCHES
GL_JE_CATEGORIES
GL_JE_SOURCES
GL_LEDGERS
GL_SETS_OF_BOOKS
GL_DAILY_RATES
GL_CODE_COMBINATIONS
GL_BALANCES
GL_PERIODS
GL_PERIOD_SETS
GL_CURRENCIES
GL_HISTORICAL_RATES
GL_LOOKUPS


GL INTERFACE TABLES
====================

GL_INTERFACE
GL_INTERFACE_HISTORY
GL_DAILY_RATES_INTERFACE
GL_BUDGET_INTERFACE
GL_IEA_INTERFACE

=================================================================================



SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'MTL_I%E' AND OWNER='INV'


INVENTORY (INV) BASE TABLES
============================

MTL_SYSTEM_ITEMS_B
MTL_ITEM_CATEGORIES
MTL_ITEM_LOCATIONS
MTL_ITEM_REVISIONS_B
MTL_PARAMETERS
MTL_ITEM_SUB_INVENTORIES
MTL_ONHAND_QUANTITIES
MTL_CATEGORY_SETS_B
CST_ITEM_COSTS
CST_COST_TYPES
MTL_MATERIAL_TRANSACTIONS
MTL_TRANSACTION_TYPES
MTL_SECONDARY_INVENTORIES
MTL_SERIAL_NUMBERS
MTL_LOT_NUMBERS
MTL_TRANSACTION_ACCOUNTS


INV INTERFACE TABLES
=======================

MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_INTERFACE_ERRORS


================================================================================

                  TABLES WITH LIKS IN A RELATION SHIP
===============================================================================


FIND THE TABLE NAME WITH LIKE OPERATOR IN THE DATABASE
====================================================

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'AP%INT' AND OWNER='AP'

RA_CUST_TRX_LINE_V

INVENTORY TABLES (INV)
=========================

ORG_ORGANIZATION_DEFINITIONS
MTL_SYSTEM_ITEMS_B MSIB,
MTL_ITEM_CATEGORIES MIC,
MTL_SECONDARY_INVENTORIES MSI,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_ONHAND_QUANTITIES MOQ,
MTL_CATEGORIES_B MC,
MTL_RELATED_ITEMS MRI,
MTL_CATEGORY_SETS_B MCS,
CST_ITEM_COSTS CIC,
CST_COST_TYPES CCT
MTL_PARAMETERS MP,
MTL_RESERVATIONS MR,
MTL_ITEM_REVISIONS MIR,

LINKS B/W ORG AND INV
======================

OOD.ORGANIZATION_ID=MSIB.ORGANIZATION_ID

MSIB.ORGANIZATION_ID=MSI.ORGANIZATION_ID

MSIB.ORGANIZATION_ID=MOQ.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID

MSIB.ORGANIZATION_ID=MIC.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=MIC.INVENTORY_ITEM_ID

MIC.CATEGORY_ID=MC.CATEGORY_ID

MIC.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID

MSIB.ORGANIZATION_ID=MMT.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID

MSIB.ORGANIZATION_ID=CIC.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=CIC.INVENTORY_ITEM_ID

MSIB.ORGANIZATION_ID=MRI.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=MRI.INVENTORY_ITEM_ID

CIC.COST_TYPE_ID=CCT.COST_TYPE_ID

MP.ORGANIZATION_ID=OOD.ORGANIZATION_ID

PRLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID

MSIB.ORGANIZATION_ID=PLLA.SHIP_TO_ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=PLA.ITEM_ID
PLA.PO_LINE_ID=PLLA.PO_LINE_ID


PURCHASING TABLES (PO)
==========================

PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PER_ALL_PEOPLE_F PAP

LINKS B/W PO REQUISITION AND PURCHASE ORDER AND RECEIPT
========================================================

PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID

PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID

PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID

PRDA.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID

PDA.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID

PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
PLA.PO_LINE_ID=PLLA.PO_LINE_ID
PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
PHA.PO_HEADER_ID=PDA.PO_HEADER_ID
PLA.PO_LINE_ID=PDA.PO_LINE_ID
PDA.PO_DISTRIBUTION_ID=RSL.PO_DISTRIBUTION_ID
PHA.PO_HEADER_ID=RT.PO_HEADER_ID
PHA.AGENT_ID=PAP.PERSON_ID
PRHA.PREPARER_ID=

RECEIPTS TABLES
================

RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
RCV_TRANSACTIONS RT

LINK B/W RECEIPTS AND PO AND INVOICES
=======================================

RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID

RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID

RSH.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID

RT.PO_HEADER_ID=PHA.PO_HEADER_ID

RT.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID

RT.PO_LINE_ID=PLA.PO_LINE_ID

RT.INVOICE_ID=AIA.INVOICE_ID

AP (ACCOUNT PAYABLES ) TABLES
============================

AP_INVOICES_V
AP_INVOICES_ALL AIA,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_PAYMENT_SCHEDULES_ALL APSA,
AP_CHECKS_ALL ACA,
AP_HISTORY_INVOICES_ALL AHIA,
AP_HISTORY_CHECKS_ALL AHCA1,
AP_HISTORY_INV_PAYMENTS_ALL,
AP_AE_HEADERS AAH,
AP_AE_LINES AAL

LINK B/W ACCOUNTING TABLES
==========================

AAH.AE_HEADER_ID=AAL.AE_HEADER_ID


LINK B/W AP AND RECEIPTS
============================

RT.INVOICE_ID=AIA.INVOICE_ID

AIA.INVOICE_ID=AILA.INVOICE_ID
AIA.INVOICE_ID=AIDA.INVOICE_ID
AILA.INVOICE_ID=AIDA.INVOICE_ID
AILA.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
ACA.CHECK_ID=AIPA.CHECK_ID
APSA.INVOICE_ID=AIPA.INVOICE_ID
ACA.CHECK_ID=AHCA1.CHECH_ID
AHIA.INVOICE_ID=AIA.INVOICE_ID

AIA.QUICK_PO_HEADER_ID=PHA.PO_HEADER_ID


SUPPLIERS TABLES
=================

IN R12

AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL ASSA,
AP_SUPPLIER_CONTACTS APSC,

LINK B/W PO AND SUPPLIERS AND INV
=================================

APS.VENDOR_ID=ASSA.VENDOR_ID
ASSA.VENDOR_SITE_ID=APSC.VENDOR_SITE_ID

PHA.VENDOR_ID=APS.VENDOR_ID

PHA.VENDOR_SITE_ID=SSA.VENDOR_SITE_ID

PHA.VENDOR_CONTACT_ID=APSC.VENDOR_CONTACT_ID

PLLA.SHIP_TO_ORGANIZATION_ID=OOD.ORGANIZATION_ID

PLA.PO_LINE_ID=PLLA.PO_LINE_ID

MSIB.INVENTORY_ITEM_ID=PLA.ITEM_ID


IN 11I

PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
PO_VENDOR_CONTACTS PVC

ORDER MANAGEMENT TABLES (OM)
==============================

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
OE_ORDER_HOLDS OOH,
QP_LIST_HEADERS QLH,
RA_SALESREPS RS,
OE_TRANSACTION_TYPES_TL OTTT,
AR_CUSTOMERS AC,
RA_TERMS RT,


LINK B/W OM AND SALESREPS,PRICELIST,TRANSACTIONS
=================================================

OOHA.HEADER_ID=OOLA.HEADER_ID

OOLA.LINE_ID=WDD.SOURCE_LINE_ID

OOHA.HEADER_ID=WDD.SOURCE_LINE_ID

WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID

WDA.DELIVERY_ID=WND.DELIVERY_ID

OOHA.SALESREP_ID=RS.SALESREP_ID

OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID

OOHA.PRICE_LIST_ID=QLH.LIST_HEADER_ID

OOLA.HEADER_ID=OOH.HEADER_ID
OOLA.LINE_ID=OOH.LINE_ID

WDD.SOURCE_HEADER_ID=OOH.HEADER_ID
WDD.SOURCE_LINE_ID=OOH.LINE_ID

TCA(TRADING COMMUNITY ARCHITECTURE TABLES)
============================================


HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_CUSTOMER_PROFILES,
HZ_PARTY_RELATIONSHIPS RELA,
HZ_CUST_PROFILE_CLASSES
HZ_LOCATIONS HL,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCSU,
RA_TERMS RT
RA_CUSTOMER_TRX_ALL RCT,
RA_CUSTOMER_TRX_LINES_ALL RCTL,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUST_TRX_TYPES_ALL RCTT,
HZ_CONTACT_POINTS HCP,
HZ_CUST_CONTACT_POINTS HCCP,

AR_CUSTOMERS RC,
AR_PAYMENT_SCHEDULES_ALL PAYSCH,
AR_ADJUSTMENTS_ALL ADJA,
AR_DISTRIBUTIONS_ALL DISTA,
AR_TRANSACTION_HISTORY_ALL TRANSHA,
AR_CASH_RECEIPTS_ALL CASHRA,
AR_DISTRIBUTION_SETS_ALL DISTSA,
AR_RECEIVABLES_TRX_ALL RECTA,
AR_PERIODS
AR_PERIOD_TYPES
AR_VAT_TAX_ALL
RA_TERMS
LINKS
======

RCT.CUST_TRX_TYPE_ID=RCTT.CUST_TRX_TYPE_ID

RCT.CUSTOMER_TRX_ID=RCTL.CUSTOMER_TRX_ID

RCT.CUSTOMER_TRX_ID=RCTLG.CUSTOMER_TRX_ID

RCT.TERM_ID=RT.TERM_ID

HP.PARTY_ID=HPS.PARTY_ID
HP.PARTY_ID=HCA.PARTY_ID
HCA.CUST_ACCOUNT_ID=HCAS.CUST_ACCOUNT_ID
HCAS.CUST_ACCT_SITE_ID=HCSU.CUST_ACCT_SITE_ID

HPS.LOCATION_ID=HL.LOCATION_ID

HCA.CUST_ACCOUNT_ID=HCCP.CUST_ACCOUNT_ID

HCP.CONTACT_POINT_ID=HCCP.CONTACT_POINT_ID



SLA(SUB LEDGER ACCOUNTING ) TABLES
===================================

XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_TRANSACTION_ENTITIES XTE,
XLA_DISTRIBUTION_LINKS XDL,
XLA_EVENTS XEVENT

GL (GENERAL LEDGER ) TABLES
===========================
GL_JE_BATCHES GJB,
GL_JE_LINES GJL,
GL_JE_HEADERS GJH,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIMPR,


======JOINS B/W XLA AND GL ==

GL_JE_BATCHES (JE_BATCH_ID)                                   => GL_JE_HEADERS (JE_BATCH_ID)
GL_JE_HEADERS (JE_HEADER_ID)                                  => GL_JE_LINES (JE_HEADER_ID)
GL_JE_LINES (JE_HEADER_ID,  JE_LINE_NUM)                      => GL_IMPORT_REFERENCES (JE_HEADER_ID, JE_LINE_NUM)
GL_IMPORT_REFERENCES (GL_SL_LINK_TABLE, GL_SL_LINK_ID)        => XLA_AE_LINES (GL_SL_LINK_TABLE, GL_SL_LINK_ID)
XLA_AE_LINES (APPLICATION_ID, AE_HEADER_ID)                   => XLA_AE_HEADERS (APPLICATION_ID, AE_HEADER_ID)
XLA_AE_HEADERS (APPLICATION_ID, EVENT_ID)                     => XLA_EVENTS (APPLICATION_ID, EVENT_ID)  
XLA_EVENTS (APPLICATION_ID, ENTITY_ID)                        => XLA.XLA_TRANSACTION_ENTITIES (APPLICATION_ID, ENTITY_ID)

==========

          RA_CUSTOMER_TRX_LINES_ALL RCTL,
          RA_CUSTOMER_TRX_ALL RCT,
          RA_CUST_TRX_TYPES_ALL RCTT,
          RA_CUST_TRX_LINE_GL_DIST_ALL GD,
          SY_UOMS_MST SUOM,
         
          RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
          RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
          SUOM.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE
          RCT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
=====

AP_INVOICES_ALL AI,
          AP_BATCHES_ALL AB,
          AP_LOOKUP_CODES ALC1,
          AP_LOOKUP_CODES ALC2,
          AP_LOOKUP_CODES ALC4,
          AP_OTHER_PERIOD_TYPES AOPT,
          AP_RECURRING_PAYMENTS_ALL ARP,
          AP_TERMS AT,
          AP_SYSTEM_PARAMETERS ASP,
          FND_CURRENCIES FC,
          FND_DOCUMENT_SEQUENCES FDS,
          FND_DOC_SEQUENCE_CATEGORIES FDSC,
          GL_SETS_OF_BOOKS GSOB,
          GL_DAILY_CONVERSION_TYPES GDCT,
          HR_ORGANIZATION_UNITS HOU,
          PO_VENDORS PV,
          PO_VENDOR_SITES_ALL PVS,
          AP_AWT_GROUPS AWT,
          AP_AWT_GROUPS AWT1,
          PA_PROJECTS_ALL PAP,
          PA_TASKS PAT,
          ZX_FC_BUSINESS_CATEGORIES_V ZBC,
          FND_TERRITORIES_TL FND,
          AP_DISTRIBUTION_SETS DSET,
          PO_HEADERS PH,
          IBY_PAYMENT_METHODS_VL IBY1,
          IBY_PAYMENT_REASONS_VL IBY2,
          FND_LOOKUPS IBY3,
          IBY_DELIVERY_CHANNELS_VL IBY4,
          FND_LOOKUPS IBY5,
          IBY_EXT_BANK_ACCOUNTS IBYBNK,
          HZ_PARTIES HP
    WHERE AI.BATCH_ID = AB.BATCH_ID(+)
      AND AI.RECURRING_PAYMENT_ID = ARP.RECURRING_PAYMENT_ID(+)
      AND ARP.REC_PAY_PERIOD_TYPE = AOPT.PERIOD_TYPE(+)
      AND AOPT.MODULE(+) = 'RECURRING PAYMENT'
      AND AI.TERMS_ID = AT.TERM_ID(+)
      AND ALC1.LOOKUP_TYPE(+) = 'INVOICE TYPE'
      AND ALC1.LOOKUP_CODE(+) = AI.INVOICE_TYPE_LOOKUP_CODE
      AND ALC2.LOOKUP_TYPE(+) = 'INVOICE PAYMENT STATUS'
      AND ALC2.LOOKUP_CODE(+) = AI.PAYMENT_STATUS_FLAG
      AND ALC4.LOOKUP_TYPE(+) = 'AP_WFAPPROVAL_STATUS'
      AND ALC4.LOOKUP_CODE(+) = AI.WFAPPROVAL_STATUS
      AND ZBC.CLASSIFICATION_CODE(+) = AI.TRX_BUSINESS_CATEGORY
      AND ZBC.APPLICATION_ID(+) = 200
      AND ZBC.ENTITY_CODE(+) = 'AP_INVOICES'
      AND AI.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE(+)
      AND AI.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)
      AND FDSC.CODE(+) = AI.DOC_CATEGORY_CODE
      AND FDSC.APPLICATION_ID(+) = 200
      AND AI.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
      AND AI.EXPENDITURE_ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
      AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
      AND AI.PROJECT_ID = PAP.PROJECT_ID(+)
      AND AI.TASK_ID = PAT.TASK_ID(+)
      AND AI.AWT_GROUP_ID = AWT.GROUP_ID(+)
      AND AI.PAY_AWT_GROUP_ID = AWT1.GROUP_ID(+)
      AND AI.INVOICE_CURRENCY_CODE = FC.CURRENCY_CODE(+)
      AND AI.ORG_ID = ASP.ORG_ID
      AND FND.TERRITORY_CODE(+) = AI.TAXATION_COUNTRY
      AND (AI.TAXATION_COUNTRY IS NULL OR FND.LANGUAGE = USERENV ('LANG'))
      AND AI.DISTRIBUTION_SET_ID = DSET.DISTRIBUTION_SET_ID(+)
      AND AI.QUICK_PO_HEADER_ID = PH.PO_HEADER_ID(+)
      AND AI.PAYMENT_METHOD_CODE = IBY1.PAYMENT_METHOD_CODE(+)
      AND AI.PAYMENT_REASON_CODE = IBY2.PAYMENT_REASON_CODE(+)
      AND AI.BANK_CHARGE_BEARER = IBY3.LOOKUP_CODE(+)
      AND IBY3.LOOKUP_TYPE(+) = 'IBY_BANK_CHARGE_BEARER'
      AND AI.DELIVERY_CHANNEL_CODE = IBY4.DELIVERY_CHANNEL_CODE(+)
      AND AI.SETTLEMENT_PRIORITY = IBY5.LOOKUP_CODE(+)
      AND IBY5.LOOKUP_TYPE(+) = 'IBY_SETTLEMENT_PRIORITY'
      AND IBYBNK.EXT_BANK_ACCOUNT_ID(+) = AI.EXTERNAL_BANK_ACCOUNT_ID
      AND AI.PARTY_ID = HP.PARTY_ID
      /* AND    HP.PARTY_ID                    = PV.PARTY_ID (+)       BUG:7366363 */
      /*ADDED BELOW LINE FROM AP_INVOICES_V.XDF 120.5
        TO INCLUDE CHANGE DONE IN TO THIS SQL*/
      AND PV.VENDOR_ID(+) = AI.VENDOR_ID                     /* BUG:7366363 */
      AND AI.APPROVAL_READY_FLAG <> 'S';

===================================================================================

SELECT b.NAME                   je_batch_name,

b.description                   je_batch_description,

b.running_total_accounted_dr    je_batch_total_dr,

b.running_total_accounted_cr    je_batch_total_cr,

b.status                        je_batch_status,

b.default_effective_date        je_batch_effective_date,

b.default_period_name           je_batch_period_name,

b.creation_date                 je_batch_creation_date,

u.user_name                     je_batch_created_by,

h.je_category                   je_header_category,

h.je_source                     je_header_source,

h.period_name                   je_header_period_name,

h.NAME                          je_header_journal_name,

h.status                        je_header_journal_status,

h.creation_date                 je_header_created_date,

u1.user_name                    je_header_created_by,

h.description                   je_header_description,

h.running_total_accounted_dr    je_header_total_acctd_dr,

h.running_total_accounted_cr    je_header_total_acctd_cr,

l.je_line_num                   je_lines_line_number,

l.ledger_id                     je_lines_ledger_id,

glcc.concatenated_segments      je_lines_ACCOUNT,

l.entered_dr                    je_lines_entered_dr,

l.entered_cr                    je_lines_entered_cr,

l.accounted_dr                  je_lines_accounted_dr,

l.accounted_cr                  je_lines_accounted_cr,

l.description                   je_lines_description,

glcc1.concatenated_segments     xla_lines_account,

xlal.accounting_class_code      xla_lines_acct_class_code,

xlal.accounted_dr               xla_lines_accounted_dr,

xlal.accounted_cr               xla_lines_accounted_cr,

xlal.description                xla_lines_description,

xlal.accounting_date            xla_lines_accounting_date,

xlate.entity_code               xla_trx_entity_code,

xlate.source_id_int_1           xla_trx_source_id_int_1,

xlate.source_id_int_2           xla_trx_source_id_int_2,

xlate.source_id_int_3           xla_trx_source_id_int_3,

xlate.security_id_int_1         xla_trx_security_id_int_1,

xlate.security_id_int_2         xla_trx_security_id_int_2,

xlate.transaction_number        xla_trx_transaction_number,

rcvt.transaction_type           rcv_trx_transaction_type,

rcvt.transaction_date           rcv_trx_transaction_date,

rcvt.quantity                   rcv_trx_quantity,

rcvt.shipment_header_id         rcv_trx_shipment_header_id,

rcvt.shipment_line_id           rcv_trx_shipment_line_id,

rcvt.destination_type_code      rcv_trx_destination_type_code,

rcvt.po_header_id               rcv_trx_po_header_id,

rcvt.po_line_id                 rcv_trx_po_line_id,

rcvt.po_line_location_id        rcv_trx_po_line_location_id,

rcvt.po_distribution_id         rcv_trx_po_distribution_id,

rcvt.vendor_id                  rcv_trx_vendor_id,

rcvt.vendor_site_id             rcv_trx_vendor_site_id

FROM                           

gl_je_batches                   b,

gl_je_headers                   h,

gl_je_lines                     l,

fnd_user                        u,

fnd_user                        u1,

gl_code_combinations_kfv        glcc,

gl_code_combinations_kfv        glcc1,

gl_import_references            gir,

xla_ae_lines                    xlal,

xla_ae_headers                  xlah,

xla_events                      xlae,

xla.xla_transaction_entities    xlate,

rcv_transactions                rcvt

WHERE

b.created_by                    = u.user_id

AND h.created_by                = u1.user_id

AND b.je_batch_id               = h.je_batch_id

AND h.je_header_id              = l.je_header_id

AND l.code_combination_id       = glcc.code_combination_id

AND l.je_header_id              = gir.je_header_id

AND l.je_line_num               = gir.je_line_num

AND gir.gl_sl_link_table        = xlal.gl_sl_link_table

AND gir.gl_sl_link_id           = xlal.gl_sl_link_id

AND xlal.application_id         = xlah.application_id

AND xlal.ae_header_id           = xlah.ae_header_id

AND xlal.code_combination_id    = glcc1.code_combination_id

AND xlah.application_id         = xlae.application_id

AND xlah.event_id               = xlae.event_id

AND xlae.application_id         = xlate.application_id

AND xlae.entity_id              = xlate.entity_id

AND xlate.source_id_int_1       = rcvt.transaction_id

AND h.je_category               = 'Receiving'

AND b.default_period_name       = '01_APR-2009'

ORDER BY h.je_category;

============================================================================

Procure To Pay Cycle in Oracle Apps R12 (P2P Cycle)

Procure To Pay Cycle in Oracle Apps R12 (P2P Cycle)

Here in this post, I tried to explain the steps involved in Procure to Pay Cycle. This is a pure functional Stuff and helps you to understand the navigation steps.
I tried to keep as simple as Possible for clear understanding. The screenshots given below are taken from R12.1.1 apps instance.

Stage 1: Choosing an Item

Let us choose an item to be procured in our example. 
Go to Purchasing Responsibility and follow the below navigation to check for the suitable item.

The item picked for our example should be purchasable item as above. Click on tools and choose “Categories” to view the below screen.


Stage 2: Creation of Requisition

Follow the below Navigation to reach Requisition Form. 


Create a new Requisition for the item viewed in Stage 1.



Click on Distributions to View the charge Account.

 

Save and Submit for Approval

 

Now note down the Requisition number and open the “Requisition Summary Form” to view the status of it. For our Example, Requisition number is: 14855

Stage 3 : Checking the Status of Requisition


 Query for the Requisition Number and click Find.


 Here for our example purpose, I kept the submitted and approved person has same and hence it shows the status as approved.


 To see the approval status, please follow the below navigation. 


  
Stage 4 : Creation of Purchase Order

For creating a Purchase order, let us use the “Autocreate Documents” Form. Follow the below Navigation

 Query for the Requisition


 Click on Automatic as shown in the above figure to create a Purchase Order

  
Click on “Create” button to create a Purchase order


  
 View the shipment screen to change the “Match Approval Level” to “2-Way”.

Click the “Receiving Controls” to make sure that the “Routing” is made as “Direct Routing”


 Click Save and submit for Approval.


                                    

 Note down the PO Number.
  
Stage 5: Creation of Receipts


Query with the Purchase order created in the above stage.


 Check the check box near to the lines that are received and click save.

  
Click the “Header Button” to view the Receipt Number.


Stage 6: Checking the On Hand

 Go to any Inventory Responsibility and follow the below Navigation


 Query for our Receipt and make sure the Organization is the same as we received.


 Below screen will show that our inventory has been increased by 5 quantities.


Stage 7: Check the Material Transactions

Follow the below Navigation to reach “Material Transactions” Form

 Query for the item and date as below


 Below screen shows the source and transaction Type

 Below screen shows you the Serial Numbers of the items received.

  
Stage 8: Creation of Invoice

Navigate to any Purchasing Responsibility and view à Requests 
Submit the below requests by providing the Receipt number as Parameter to create an invoice.



 Check the status of the program.


Stage 9: Checking the Invoice

Change to any Payables Responsibility and open the invoices Form.

Query for the Purchase order as below,


                                   

 Click “Actions” Button then tick the “Validate Check Box” and press “Ok” to validate the invoice

  
Below screenshot will give you the status of the invoice

  
Stage 10: Creation of Accounting and Payment

Once invoice got approved, we can “Create Accounting” and “Create Payments” via “Action” Button in the “Invoice Form” as we validated the invoice.


Thus the brief description of P2P cycle came to end. 

Monday, 14 May 2012

Order to Cash cycle in Oracle Apps

In this article, we will go through the Order to Cash cycle. The below are the steps in short:


  • Enter the Sales Order
  • Book the Sales Order
  • Launch Pick Release
  • Ship Confirm
  • Create Invoice
  • Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
  • Transfer to General Ledger
  • Journal Import
  • Posting
Let’s get into the details of each step mentioned above.


Enter the Sales Order:


Navigation:

Order Management Super User Operations (USA)>Orders Returns >Sales Orders


Enter the Customer details (Ship to and Bill to address), Order type.





Click on Lines Tab. Enter the Item to be ordered and the quantity required.




Line is scheduled automatically when the Line Item is saved.

Scheduling/unscheduling can be done manually by selecting Schedule/Un schedule from the Actions Menu.


You can check if the item to be ordered is available in the Inventory by clicking on Availability Button.




Save the work.


Underlying Tables affected:


In Oracle, Order information is maintained at the header and line level.

The header information is stored in OE_ORDER_HEADERS_ALL and the line information in OE_ORDER_LINES_ALL when the order is entered. The column called FLOW_STATUS_CODE is available in both the headers and lines tables which tell us the status of the order at each stage.


At this stage, the FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL is ‘Entered’



Book the Sales Order:


Book the Order by clicking on the Book Order button.




Now that the Order is BOOKED, the status on the header is change accordingly.




Underlying tables affected:


At this stage:
The FLOW_STATUS_CODE in the table OE_ORDER_HEADERS_ALL would be
‘BOOKED’
The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will be
‘AWAITING_SHIPPING’.
Record(s) will be created in the table WSH_DELIVERY_DETAILS with
RELEASED_STATUS=’R’ (Ready to Release)
Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS.
At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts

into MTL_DEMAND



Launch Pick Release:

Navigation:

Shipping > Release Sales Order > Release Sales Orders.


Key in Based on Rule and Order Number





In the Shipping Tab key in the below:

Auto Create Delivery: Yes

Auto Pick Confirm: Yes

Auto Pack Delivery: Yes





In the Inventory Tab:

Auto Allocate: Yes

Enter the Warehouse




Click on Execute Now Button.

On successful completion, the below message would pop up as shown below.







Pick Release process in turn will kick off several other requests like Pick Slip Report,

Shipping Exception Report and Auto Pack Report




Underlying Tables affected:

If Autocreate Delivery is set to ‘Yes’ then a new record is created in the table WSH_NEW_DELIVERIES.
DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.
The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse).

Pick Confirm the Order:

IF Auto Pick Confirm in the above step is set to NO, then the following should be done.

Navigation:

Inventory Super User > Move Order> Transact Move Order


In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.



Ship Confirm the Order:


Navigation:


Order Management Super User>Shipping >Transactions.


Query with the Order Number.





Click On Delivery Tab




Click on Ship Confirm.










The Status in Shipping Transaction screen will now be closed.




This will kick off concurrent programs like.INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading

Underlying tables affected:


RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed)

FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be “BOOKED“

FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be “SHIPPED“



Create Invoice:


Run workflow background Process.


Navigation:

Order Management >view >Requests






Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with


INTERFACE_LINE_CONTEXT     =     ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1=     Order_number
INTERFACE_LINE_ATTRIBUTE3=     Delivery_id


and spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.




The Invoice created can be seen using the Receivables responsibility

Navigation:

Receivables Super User> Transactions> Transactions


Query with the Order Number as Reference.




Underlying tables:

RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.

RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

Create receipt:

Navigation:

Receivables> Receipts> Receipts


Enter the information.



Click on Apply Button to apply it to the Invoice.









Underlying tables:

AR_CASH_RECEIPTS_ALL



Transfer to General Ledger:

To transfer the Receivables accounting information to general ledger, run General Ledger Transfer Program.


Navigation:

Receivables> View Requests


Parameters:


  • Give in the Start date and Post through date to specify the date range of the transactions to be transferred.
  • Specify the GL Posted Date, defaults to SYSDATE.
  • Post in summary: This controls how Receivables creates journal entries for your transactions in the interface table. If you select ‘No’, then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission. If you select ‘Yes’, then the program creates one journal entry for each general ledger account.
  • If the Parameter Run Journal Import is set to ‘Yes’, the journal import program is kicked off automatically which transfers journal entries from the interface table to General Ledger, otherwise follow the topic Journal Import to import the journals to General Ledger manually.






.


Underlying tables:


This transfers data about your adjustments, chargeback, credit memos, commitments, debit memos, invoices, and receipts to the GL_INTERFACE table.



Journal Import:


To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.



Navigation:

General Ledger > Journal> Import> Run


Parameters:

  • Select the appropriate Source.
  • Enter one of the following Selection Criteria:
    No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.

    All Group IDs: To import all data for that source that has a group ID. Use this option to import multiple journal batches for the same source with varying group IDs.

    Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.

    If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.
  • Define the Journal Import Run Options (optional)
    Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting from any unbalanced journals to your suspense account.

    Choose Create Summary Journals to have journal import create the following:

    • one journal line for all transactions that share the same account, period, and currency and that has a debit balance

    • one journal line for all transactions that share the same account, period, and currency and that has a credit balance.
  • Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.
  • Choose whether to Import Descriptive Flexfields, and whether to import them with validation.

Click on Import button.





Underlying tables:


GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES



Posting:


We have to Post journal batches that we have imported previously to update the account balances in General Ledger.


Navigation:


General Ledger> Journals > Enter


Query for the unposted journals for a specific period as shown below.





From the list of unposted journals displayed, select one journal at a time and click on Post button to post the journal.









If you know the batch name to be posted you can directly post using the Post window


Navigation:

General Ledger> Journals> Post




Underlying tables:


GL_BALANCES.