KEY JOINS
GL AND AP
****************
GL_CODE_COMBINATIONS AP_INVOICES_ALL
code_combination_id = acct_pay_code_combination_id
GL_CODE_COMBINATIONS AP_INVOICES_DISTRIBUTIONS_ALL
code_combination_id = dist_code_combination_id
GL_SETS_OF_BOOKS AP_INVOICES_ALL
set_of_books_id = set_of_books_id
GL AND AR
**************
GL_CODE_COMBINATIONS RA_CUST_TRX_LINE__GL_DIST_ALL
code_combination_id = code_combination_id
GL AND INV
***************
GL_CODE_COMBINATIONS MTL_SYSTEM_ITEMS_B
code_combination_id = cost_of_sales_account
GL AND PO
***************
GL_CODE_COMBINATIONS PO_DISTRIBUTIONS_ALL
code_combination_id = code_combination_id
PO AND AP
PO_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIONS_ALL
Po_distribution_id = po_distribution_id
PO_VENDORS AP_INVOICES_ALL
vendor_id = vendor_id
PO AND SHIPMENTS
**********************
PO_HEADERS_ALL RCV_TRANSACTIONS
Po_header_id = po_header_id
PO_DISTRIBUTIONS_ALL RCV_TRANSACTIONS
Po_distribution_id = po_distribution_id
SHIPMENTS AND INVOICE
***************************
RCV_TRANSACTIONS AP_INVOICE_DISTRIBUTIONS_ALL
RCV_TRANSACTION_ID = RCV_TRANSACTION_ID
PO AND INVOICE
********************
PO_REQUISITION_LINES_ALL MTL_SYSTEM_ITEMS_B
item_id = inventory_item_id
org_id = organization_id
PO AND HRMS
*****************
PO_HEADERS_ALL HR_EMPLOYEES
Agent_id = employee_id
PO AND REQUISITION
***********************
PO_DISTRIBUTIONS_ALL PO_REQ_DISTRIBUTIONS_ALL
req_distribution_id = distribution_id
SHIPMENTS AND INVOICE
****************************
RCV_TRANSACTIONS MTL_SYSTEM_ITEMS_B
Organization_id = organization_id
INV AND HRMS
*****************
MTL_SYSTEM_ITEMS_B HR_EMPLOYEES
buyer_id = employee_id
OM AND AR
****************
OE_ORDER_HEADERS_ALL RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR( Order_number) = interface_line_attribute1
OE_ORDER_LINES_ALL RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR(Line_id) = interface_line_attribute6
OE_ORDER_LINES_ALL RA_CUSTOMER_TRX_LINES_ALL
reference_customer_trx_line_id = customer_trx_line_id
OM AND SHIPPING
********************
OE_ORDER_HEADERS_ALL WSH_DELIVARY_DETAILS
HEADER_ID = SOURCE_HEADER_ID
OE_ORDER_HEADERS_ALL WSH_DELIVARY_DETAILS
LINE_ID = SOURCE_LINE_ID
AP AND AR (BANKS)
**********************
AR_CASH_RECEIPTS_ALL AP_BANK_ACCOUNTS
REMITTANCE_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID
AP AND AR
*************
HZ_PARTIES AP_INVOICES_ALL
PARTY_ID = PARTY_ID
OM AND CRM
****************
OE_ORDER_LINES_ALL CSI_ITEM_INSTANCES(Install Base)
LINE_ID = LAST_OE_ORDER_LINE_ID
Table Name: Po_Requisition_Headers_All A
Column Names Table Name Column Name
A. REQUISITION_HEADER_ID PO_REQUISITION_LINES_ALL REQUISITION_HEADER_ID
A. TYPE_LOOKUP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPE
A. PREPARER_ID PER_PEOPLE_F PERSON_ID
A. ORG_ID MTL_SYSTEM_ITEMS ORGANIZATION_ID
A. ORG_ID MTL_ORGANIZATIONS ORGANIZATION_ID
Table Name: Po_Requisition_Lines_All B
Column Names Table Name Column Name
B .REQUISITION_HEADER_ID PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_ID
B .REQUISITION_LINE_ID PO_REQ_DISTRIBUTIONS_ALL REQUISITION_LINE_ID
B .LINE_TYPE_ID PO_LINE_TYPES LINE_TYPE_ID
B .ITEM_ID MTL_SYSTEM_ITEMS INVENTORY_ITEM_ID
B .ORG_ID MTL_SYSTEM_ITEMS ORGANIZATION_ID
Table Name: Po_Requisition_Distributions_All C .
Column Names Table Name Column Name
C .REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID
C .DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL REQ_DISTRIBUTION_ID
C .SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_ID
C .CODE_COMBINATION_ID GL_CODE-COMBINATIONS CODE_COMBINATION_ID
Table Name: Po_Distributions_All D .
Column Names Table Name Column Name
D .PO_LINE_ID PO_LINES PO_LINE_ID
D .REQ_DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID
D .PO_DISTRIBUTION_ID AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID
Table Name: Po_Headers_All E .
Column Names Table Name Column Name
E .PO_HEADER_ID PO_LINES PO_HEADER_ID
E .PO_HEADER_ID RCV_SHIPMENT_LINES PO_HEADER_ID
E .VENDOR_ID PO_VENDORS VENDOR_ID
E .AGENT_ID PER_PEOPLE PERSON_ID
E .TYPE_LOOK_UP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPE
Table Name: Po_Lines_All F.
Column Names Table Name Column Name
F.PO_HEADER_ID PO_HEADERS PO_HEADER_ID
F.PO_LINE_ID PO_DISTRIBUTIONS_ALL PO_LINE_ID
F.ITEM_ID MTL_SYSTEM_ITEMS ITEM_ID
Table Name: Rcv_Shipment_Lines G.
Column Names Table Name Column Name
G.PO_HEADER_ID PO_HEADERS PO_HEADER_ID
G.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADERS SHIPMENT_HEADER_ID
Table Name: Ap_Invoices_All H.
Column Names Table Name Column Name
H. INVOICE_ID AP_INVOICE_DISTRIBUTIONS_ALL INVOICE_ID
Table Name: Oe_Order_Headers_All I.
Column Names Table Name Column Name
I.HEADER_ID OE_ORDER_LINES HEADER_ID
I.SOURCE_HEADER_ID WISH_DELIVERY_DETAILS SOURCE_HEADER_ID
I.PRICE_LIST_ID QP_LIST_HEADERS_TL LIST_HEADER_ID
I.ORG_ID MTL_ORGANIZATIONS ORGANIZATION_ID
I.SALESREP_ID JTF_RS_SALESREPS SALESREP_ID
I.ORDER_TYPE_ID OE_TRANSACTION_TYPES TRANSACTION_TYPE_ID
I.ORDER_SOURCE_ID OE_ORDER_SOURCES ORDER_SOURCE_ID
I.ACCOUNTING_RULE_ID RA_RULES RULE_ID
I.PAYMENT_TERM_ID RA_TERMS TERM_ID
I.SOLD_TO_ORG_ID HZ_CUST_ACCOUNTS CUST_ACCOUNT_ID
I.SHIP_FROM_ORG_ID MTL_PARAMETERS ORGANIZATION_ID
I.SHIP_TO_ORG_ID HZ_CUST_SITE_USES_ALL SITE_USE_ID
Table Name: Oe_Order_Lines_All J.
Column Names Table Name Column Name
J.LINE_TYPE_ID OE_TRANSACTION_TYPES_TL TRANSACTION_TYPE_ID
J.INVOICING_RULE_ID RA_RULES RULE_ID
Table Name: Hz_Parties K.
Column Names Table Name Column Name
K.PATY_ID HZ_CUST_ACCOUNTS PATY_ID
K.CUST_ACCOUNT_ID OE_ORDER_LINES SOLD_TO_ORG_ID
Table Name: Hz_Party_Sites_All L.
Column Names Table Name Column Name
L.PATY_ID HZ_PARTIES PATY_ID
L. LOCATION_ID HZ_LOCATIONS LOCATION_ID
Table Name: Wsh_delivery_details M.
Column Names Table Name Column Name
M.SOURCE_HEADER_ID OE_ORDER_HEADERS SOURCE_HEADER_ID
M.DELIVERY_DETAIL_ID WSH_DELIVERY_ASSIGNMENTS DELIVERY_DETAIL_ID
M.DELIVERY_ID WSH_NEW_DELIVERIES DELIVERY_ID
M.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS INVENTORY_ITEM_ID
Table Name: RA_CUSTOMER_TRX_ALL N.
Column Names Table Name Column Name
N.CUSTOMER_TRX_ID AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_ID
N.TERM_ID RA_TERMS TERM_ID
N.CUSTOMER_TRX_ID RA_CUST_TRX_LINE_GL_DIST CUSTOMER_TRX_ID
Table Name: AR_CASH_REC EIPTS_ALL O.
Column Names Table Name Column Name
O.CASH_RECEIPT_ID AR_RECEIVABLE_APPLICATIONS_ALL CASH_RECEIPT_ID
O.SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_ID
================================================================================
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_LOCATION_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;
Column link between OM and AR, You have customer_id in AR table and
sales_to_org_id in OM.
oe_order_lines_all.header_id = RA_CUSTOMER_TRX_LINES_ALL.interface_line_attribut6
AR : RA_CUST_TRX_LINE_GL_DIST_ALL
table has the link to GL_CODE_COMBINATIONS table
join key : COMINATION_CODE_ID
Relation between AR & OP
ra_customer_trx_lines.sales_order = oe_order_headers.order_number
FROM xla.xla_transaction_entities xte,
ra_customer_trx_all rcta,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gim,
gl_je_headers gjh
WHERE xte.source_id_int_1 = rcta.customer_trx_id
AND xte.entity_code = TRANSACTIONS
AND trx_number = 136AE002013
AND xte.entity_id = xe.entity_id
AND xe.event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.gl_sl_link_id = gim.gl_sl_link_id
AND gim.je_header_id = gjh.je_header_id
FROM xla.xla_transaction_entities xte,
ra_customer_trx_all rcta,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gim,
gl_je_headers gjh
WHERE xte.source_id_int_1 = rcta.customer_trx_id
AND xte.entity_code = TRANSACTIONS
AND trx_number = 136AE002013
AND xte.entity_id = xe.entity_id
AND xe.event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.gl_sl_link_id = gim.gl_sl_link_id
AND gim.je_header_id = gjh.je_header_id
FROM oe_order_headers_all ooha
, oe_order_lines_all oola
, ra_customer_trx_all rcta
, ra_customer_trx_lines_all rctla
WHERE ooha.header_id = oola.header_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND order_number = :p_order_number
Order Number INTERFACE_LINE_ATTRIBUTE1
Order Type INTERFACE_LINE_ATTRIBUTE2
Delivery INTERFACE_LINE_ATTRIBUTE3
Waybill INTERFACE_LINE_ATTRIBUTE4
Count INTERFACE_LINE_ATTRIBUTE5
Line ID INTERFACE_LINE_ATTRIBUTE6
Picking Line ID INTERFACE_LINE_ATTRIBUTE7
Bill of Lading INTERFACE_LINE_ATTRIBUTE8
Customer Item Part INTERFACE_LINE_ATTRIBUTE9
Warehouse INTERFACE_LINE_ATTRIBUTE10
Price Adjustment ID INTERFACE_LINE_ATTRIBUTE11
Shipment Number INTERFACE_LINE_ATTRIBUTE12
Option Number INTERFACE_LINE_ATTRIBUTE13
Service Number INTERFACE_LINE_ATTRIBUTE14
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
GROUP by hc.cust_account_id;
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
AND hc.cust_account_id =1847;
GL AND AP
****************
GL_CODE_COMBINATIONS AP_INVOICES_ALL
code_combination_id = acct_pay_code_combination_id
GL_CODE_COMBINATIONS AP_INVOICES_DISTRIBUTIONS_ALL
code_combination_id = dist_code_combination_id
GL_SETS_OF_BOOKS AP_INVOICES_ALL
set_of_books_id = set_of_books_id
GL AND AR
**************
GL_CODE_COMBINATIONS RA_CUST_TRX_LINE__GL_DIST_ALL
code_combination_id = code_combination_id
GL AND INV
***************
GL_CODE_COMBINATIONS MTL_SYSTEM_ITEMS_B
code_combination_id = cost_of_sales_account
GL AND PO
***************
GL_CODE_COMBINATIONS PO_DISTRIBUTIONS_ALL
code_combination_id = code_combination_id
PO AND AP
PO_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIONS_ALL
Po_distribution_id = po_distribution_id
PO_VENDORS AP_INVOICES_ALL
vendor_id = vendor_id
PO AND SHIPMENTS
**********************
PO_HEADERS_ALL RCV_TRANSACTIONS
Po_header_id = po_header_id
PO_DISTRIBUTIONS_ALL RCV_TRANSACTIONS
Po_distribution_id = po_distribution_id
SHIPMENTS AND INVOICE
***************************
RCV_TRANSACTIONS AP_INVOICE_DISTRIBUTIONS_ALL
RCV_TRANSACTION_ID = RCV_TRANSACTION_ID
PO AND INVOICE
********************
PO_REQUISITION_LINES_ALL MTL_SYSTEM_ITEMS_B
item_id = inventory_item_id
org_id = organization_id
PO AND HRMS
*****************
PO_HEADERS_ALL HR_EMPLOYEES
Agent_id = employee_id
PO AND REQUISITION
***********************
PO_DISTRIBUTIONS_ALL PO_REQ_DISTRIBUTIONS_ALL
req_distribution_id = distribution_id
SHIPMENTS AND INVOICE
****************************
RCV_TRANSACTIONS MTL_SYSTEM_ITEMS_B
Organization_id = organization_id
INV AND HRMS
*****************
MTL_SYSTEM_ITEMS_B HR_EMPLOYEES
buyer_id = employee_id
OM AND AR
****************
OE_ORDER_HEADERS_ALL RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR( Order_number) = interface_line_attribute1
OE_ORDER_LINES_ALL RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR(Line_id) = interface_line_attribute6
OE_ORDER_LINES_ALL RA_CUSTOMER_TRX_LINES_ALL
reference_customer_trx_line_id = customer_trx_line_id
OM AND SHIPPING
********************
OE_ORDER_HEADERS_ALL WSH_DELIVARY_DETAILS
HEADER_ID = SOURCE_HEADER_ID
OE_ORDER_HEADERS_ALL WSH_DELIVARY_DETAILS
LINE_ID = SOURCE_LINE_ID
AP AND AR (BANKS)
**********************
AR_CASH_RECEIPTS_ALL AP_BANK_ACCOUNTS
REMITTANCE_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID
AP AND AR
*************
HZ_PARTIES AP_INVOICES_ALL
PARTY_ID = PARTY_ID
OM AND CRM
****************
OE_ORDER_LINES_ALL CSI_ITEM_INSTANCES(Install Base)
LINE_ID = LAST_OE_ORDER_LINE_ID
Table Name: Po_Requisition_Headers_All A
Column Names Table Name Column Name
A. REQUISITION_HEADER_ID PO_REQUISITION_LINES_ALL REQUISITION_HEADER_ID
A. TYPE_LOOKUP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPE
A. PREPARER_ID PER_PEOPLE_F PERSON_ID
A. ORG_ID MTL_SYSTEM_ITEMS ORGANIZATION_ID
A. ORG_ID MTL_ORGANIZATIONS ORGANIZATION_ID
Table Name: Po_Requisition_Lines_All B
Column Names Table Name Column Name
B .REQUISITION_HEADER_ID PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_ID
B .REQUISITION_LINE_ID PO_REQ_DISTRIBUTIONS_ALL REQUISITION_LINE_ID
B .LINE_TYPE_ID PO_LINE_TYPES LINE_TYPE_ID
B .ITEM_ID MTL_SYSTEM_ITEMS INVENTORY_ITEM_ID
B .ORG_ID MTL_SYSTEM_ITEMS ORGANIZATION_ID
Table Name: Po_Requisition_Distributions_All C .
Column Names Table Name Column Name
C .REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID
C .DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL REQ_DISTRIBUTION_ID
C .SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_ID
C .CODE_COMBINATION_ID GL_CODE-COMBINATIONS CODE_COMBINATION_ID
Table Name: Po_Distributions_All D .
Column Names Table Name Column Name
D .PO_LINE_ID PO_LINES PO_LINE_ID
D .REQ_DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID
D .PO_DISTRIBUTION_ID AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID
Table Name: Po_Headers_All E .
Column Names Table Name Column Name
E .PO_HEADER_ID PO_LINES PO_HEADER_ID
E .PO_HEADER_ID RCV_SHIPMENT_LINES PO_HEADER_ID
E .VENDOR_ID PO_VENDORS VENDOR_ID
E .AGENT_ID PER_PEOPLE PERSON_ID
E .TYPE_LOOK_UP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPE
Table Name: Po_Lines_All F.
Column Names Table Name Column Name
F.PO_HEADER_ID PO_HEADERS PO_HEADER_ID
F.PO_LINE_ID PO_DISTRIBUTIONS_ALL PO_LINE_ID
F.ITEM_ID MTL_SYSTEM_ITEMS ITEM_ID
Table Name: Rcv_Shipment_Lines G.
Column Names Table Name Column Name
G.PO_HEADER_ID PO_HEADERS PO_HEADER_ID
G.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADERS SHIPMENT_HEADER_ID
Table Name: Ap_Invoices_All H.
Column Names Table Name Column Name
H. INVOICE_ID AP_INVOICE_DISTRIBUTIONS_ALL INVOICE_ID
Table Name: Oe_Order_Headers_All I.
Column Names Table Name Column Name
I.HEADER_ID OE_ORDER_LINES HEADER_ID
I.SOURCE_HEADER_ID WISH_DELIVERY_DETAILS SOURCE_HEADER_ID
I.PRICE_LIST_ID QP_LIST_HEADERS_TL LIST_HEADER_ID
I.ORG_ID MTL_ORGANIZATIONS ORGANIZATION_ID
I.SALESREP_ID JTF_RS_SALESREPS SALESREP_ID
I.ORDER_TYPE_ID OE_TRANSACTION_TYPES TRANSACTION_TYPE_ID
I.ORDER_SOURCE_ID OE_ORDER_SOURCES ORDER_SOURCE_ID
I.ACCOUNTING_RULE_ID RA_RULES RULE_ID
I.PAYMENT_TERM_ID RA_TERMS TERM_ID
I.SOLD_TO_ORG_ID HZ_CUST_ACCOUNTS CUST_ACCOUNT_ID
I.SHIP_FROM_ORG_ID MTL_PARAMETERS ORGANIZATION_ID
I.SHIP_TO_ORG_ID HZ_CUST_SITE_USES_ALL SITE_USE_ID
Table Name: Oe_Order_Lines_All J.
Column Names Table Name Column Name
J.LINE_TYPE_ID OE_TRANSACTION_TYPES_TL TRANSACTION_TYPE_ID
J.INVOICING_RULE_ID RA_RULES RULE_ID
Table Name: Hz_Parties K.
Column Names Table Name Column Name
K.PATY_ID HZ_CUST_ACCOUNTS PATY_ID
K.CUST_ACCOUNT_ID OE_ORDER_LINES SOLD_TO_ORG_ID
Table Name: Hz_Party_Sites_All L.
Column Names Table Name Column Name
L.PATY_ID HZ_PARTIES PATY_ID
L. LOCATION_ID HZ_LOCATIONS LOCATION_ID
Table Name: Wsh_delivery_details M.
Column Names Table Name Column Name
M.SOURCE_HEADER_ID OE_ORDER_HEADERS SOURCE_HEADER_ID
M.DELIVERY_DETAIL_ID WSH_DELIVERY_ASSIGNMENTS DELIVERY_DETAIL_ID
M.DELIVERY_ID WSH_NEW_DELIVERIES DELIVERY_ID
M.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS INVENTORY_ITEM_ID
Table Name: RA_CUSTOMER_TRX_ALL N.
Column Names Table Name Column Name
N.CUSTOMER_TRX_ID AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_ID
N.TERM_ID RA_TERMS TERM_ID
N.CUSTOMER_TRX_ID RA_CUST_TRX_LINE_GL_DIST CUSTOMER_TRX_ID
Table Name: AR_CASH_REC EIPTS_ALL O.
Column Names Table Name Column Name
O.CASH_RECEIPT_ID AR_RECEIVABLE_APPLICATIONS_ALL CASH_RECEIPT_ID
O.SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_ID
================================================================================
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_LOCATION_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;
Column link between OM and AR, You have customer_id in AR table and
sales_to_org_id in OM.
oe_order_lines_all.header_id = RA_CUSTOMER_TRX_LINES_ALL.interface_line_attribut6
AR : RA_CUST_TRX_LINE_GL_DIST_ALL
table has the link to GL_CODE_COMBINATIONS table
join key : COMINATION_CODE_ID
Relation between AR & OP
ra_customer_trx_lines.sales_order = oe_order_headers.order_number
FROM xla.xla_transaction_entities xte,
ra_customer_trx_all rcta,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gim,
gl_je_headers gjh
WHERE xte.source_id_int_1 = rcta.customer_trx_id
AND xte.entity_code = TRANSACTIONS
AND trx_number = 136AE002013
AND xte.entity_id = xe.entity_id
AND xe.event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.gl_sl_link_id = gim.gl_sl_link_id
AND gim.je_header_id = gjh.je_header_id
FROM xla.xla_transaction_entities xte,
ra_customer_trx_all rcta,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gim,
gl_je_headers gjh
WHERE xte.source_id_int_1 = rcta.customer_trx_id
AND xte.entity_code = TRANSACTIONS
AND trx_number = 136AE002013
AND xte.entity_id = xe.entity_id
AND xe.event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.gl_sl_link_id = gim.gl_sl_link_id
AND gim.je_header_id = gjh.je_header_id
FROM oe_order_headers_all ooha
, oe_order_lines_all oola
, ra_customer_trx_all rcta
, ra_customer_trx_lines_all rctla
WHERE ooha.header_id = oola.header_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND order_number = :p_order_number
Order Number INTERFACE_LINE_ATTRIBUTE1
Order Type INTERFACE_LINE_ATTRIBUTE2
Delivery INTERFACE_LINE_ATTRIBUTE3
Waybill INTERFACE_LINE_ATTRIBUTE4
Count INTERFACE_LINE_ATTRIBUTE5
Line ID INTERFACE_LINE_ATTRIBUTE6
Picking Line ID INTERFACE_LINE_ATTRIBUTE7
Bill of Lading INTERFACE_LINE_ATTRIBUTE8
Customer Item Part INTERFACE_LINE_ATTRIBUTE9
Warehouse INTERFACE_LINE_ATTRIBUTE10
Price Adjustment ID INTERFACE_LINE_ATTRIBUTE11
Shipment Number INTERFACE_LINE_ATTRIBUTE12
Option Number INTERFACE_LINE_ATTRIBUTE13
Service Number INTERFACE_LINE_ATTRIBUTE14
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
GROUP by hc.cust_account_id;
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
AND hc.cust_account_id =1847;
No comments:
Post a Comment