Joins Between Oracle Apps Tables Like General Ledger(GL) , Account Payable(AP), Account Receivables(AR), Purchasing Orader(PO) ETC

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  INV
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 INV
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
Po_Requisition_Headers_All 
Column Names                   Table Name                                  Column Name
REQUISITION_HEADER_IDPO_REQUISITION_LINES_ALL    REQUISITION_HEADER_ID
TYPE_LOOKUP_CODE        PO_DOCUMENT_TYPES             DOCUMENT_SUBTYPE
PREPARER_ID                   PER_PEOPLE_F                           PERSON_ID
ORG_ID                             MTL_SYSTEM_ITEMS               ORGANIZATION_ID
ORG_ID                             MTL_ORGANIZATIONS            ORGANIZATION_ID
Po_Requisition_Lines_All
Column Names                   Table Name                              Column Name
REQUISITION_HEADER_ID   PO_REQUISITION_HEADERS_ALL  REQUISITION_HEADER_ID
REQUISITION_LINE_ID      PO_REQ_DISTRIBUTIONS_ALL          REQUISITION_LINE_ID
LINE_TYPE_ID               PO_LINE_TYPES                                    LINE_TYPE_ID
ITEM_ID                          MTL_SYSTEM_ITEMS                        INVENTORY_ITEM_ID
ORG_ID                           MTL_SYSTEM_ITEMS                        ORGANIZATION_ID
Po_Requisition_Distributions_All
Column Names                      Table Name                                Column Name
REQUISITION_LINE_ID      PO_REQUISITION_LINES_ALL     REQUISITION_LINE_ID
DISTRIBUTION_ID            PO_DISTRIBUTIONS_ALL            REQ_DISTRIBUTION_ID
SET_OF_BOOKS_ID          GL_SETS_OF_BOOKS                     SET_OF_BOOKS_ID
CODE_COMBINATION_ID  GL_CODE-COMBINATIONS            CODE_COMBINATION_ID
Po_Distributions_All
Column Names                   Table Name                                         Column Name
PO_LINE_ID                   PO_LINES                                          PO_LINE_ID
REQ_DISTRIBUTION_ID   PO_REQ_DISTRIBUTIONS_ALL       DISTRIBUTION_ID
PO_DISTRIBUTION_ID    AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID
Po_Headers_All
Column Names                   Table Name                              Column Name
PO_HEADER_ID                PO_LINES                                 PO_HEADER_ID
PO_HEADER_ID                RCV_SHIPMENT_LINES           PO_HEADER_ID
VENDOR_ID                      PO_VENDORS                          VENDOR_ID
AGENT_ID                       PER_PEOPLE                           PERSON_ID
TYPE_LOOK_UP_CODE     PO_DOCUMENT_TYPES            DOCUMENT_SUBTYPE
Po_Lines_All
Column Names                   Table Name                                    Column Name
PO_HEADER_ID               PO_HEADERS                                PO_HEADER_ID
PO_LINE_ID                     PO_DISTRIBUTIONS_ALL           PO_LINE_ID
ITEM_ID                          MTL_SYSTEM_ITEMS                   ITEM_ID
Rcv_Shipment_Lines
Column Names                   Table Name                                  Column Name
PO_HEADER_ID                PO_HEADERS                              PO_HEADER_ID
SHIPMENT_HEADER_ID   RCV_SHIPMENT_HEADERS  SHIPMENT_HEADER_ID
Ap_Invoices_All
Column Names       Table Name                                          Column Name
INVOICE_ID             AP_INVOICE_DISTRIBUTIONS_ALL  INVOICE_ID
Oe_Order_Headers_All
Column Names                   Table Name                      Column Name
HEADER_ID                        OE_ORDER_LINES                   HEADER_ID
SOURCE_HEADER_ID          WISH_DELIVERY_DETAILS     SOURCE_HEADER_ID
PRICE_LIST_ID                   QP_LIST_HEADERS_TL           LIST_HEADER_ID
ORG_ID                              MTL_ORGANIZATIONS           ORGANIZATION_ID
SALESREP_ID                      JTF_RS_SALESREPS               SALESREP_ID
ORDER_TYPE_ID                  OE_TRANSACTION_TYPES     TRANSACTION_TYPE_ID
ORDER_SOURCE_ID             OE_ORDER_SOURCES             ORDER_SOURCE_ID
ACCOUNTING_RULE_ID        RA_RULES                               RULE_ID
PAYMENT_TERM_ID              RA_TERMS                              TERM_ID
SOLD_TO_ORG_ID               HZ_CUST_ACCOUNTS             CUST_ACCOUNT_ID
SHIP_FROM_ORG_ID            MTL_PARAMETERS                 ORGANIZATION_ID
SHIP_TO_ORG_ID                HZ_CUST_SITE_USES_ALL    SITE_USE_ID
Oe_Order_Lines_All
Column Names       Table Name                             Column Name
LINE_TYPE_ID            OE_TRANSACTION_TYPES_TL     TRANSACTION_TYPE_ID
INVOICING_RULE_ID  RA_RULES                                     RULE_ID
Hz_Parties
Column Names       Table Name                         Column Name
PATY_ID                    HZ_CUST_ACCOUNTS        PATY_ID
CUST_ACCOUNT_ID   OE_ORDER_LINES             SOLD_TO_ORG_ID
Hz_Party_Sites_All
Column Names                   Table Name                Column Name
PATY_ID                              HZ_PARTIES               PATY_ID
 LOCATION_ID                     HZ_LOCATIONS         LOCATION_ID
Wsh_delivery_details
Column Names                 Table Name                              Column Name
SOURCE_HEADER_ID         OE_ORDER_HEADERS                    SOURCE_HEADER_ID
DELIVERY_DETAIL_ID        WSH_DELIVERY_ASSIGNMENTS   DELIVERY_DETAIL_ID
DELIVERY_ID                    WSH_NEW_DELIVERIES                DELIVERY_ID
INVENTORY_ITEM_ID        MTL_SYSTEM_ITEMS                     INVENTORY_ITEM_ID
RA_CUSTOMER_TRX_ALL
Column Names           Table Name                                    Column Name
CUSTOMER_TRX_ID     AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_ID
TERM_ID                     RA_TERMS                                           TERM_ID
CUSTOMER_TRX_ID    RA_CUST_TRX_LINE_GL_DIST        CUSTOMER_TRX_ID
AR_CASH_REC EIPTS_ALL
Column Names                   Table Name                                   Column Name
CASH_RECEIPT_ID               AR_RECEIVABLE_APPLICATIONS_ALL  CASH_RECEIPT_ID
SET_OF_BOOKS_ID             GL_SETS_OF_BOOKS                               SET_OF_BOOKS_ID



No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...