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;