Join Conditions Used in Oracle Apps tables

Key Joins of Oracle Apps Tables

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_LINES_ALL                                               MTL_SYSTEM_ITEMS_B
item_id                                             =   inventory_item_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_DELIVERY_DETAILS

HEADER_ID                         =   SOURCE_HEADER_ID

OE_ORDER_HEADERS_ALL        WSH_DELIVERY_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