Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Thursday, April 18, 2013

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


Posted by at
Labels:

9 comments:

  1. UnknownNovember 19, 2013 at 12:31 AM

    simply superb

    ReplyDelete
  2. RanaFebruary 25, 2014 at 12:08 AM

    REALLY NICE .................................

    ReplyDelete
  3. UnknownMay 7, 2015 at 8:45 AM

    EXCELLENT.....

    ReplyDelete
  4. UnknownJuly 25, 2016 at 8:41 AM

    very nice,

    can u have conceptwise videos.

    ReplyDelete
  5. UnknownJuly 25, 2016 at 8:43 AM

    how to remember to the above join tables.pls give tips to remember.

    ReplyDelete
  6. UnknownJuly 20, 2018 at 1:03 PM

    Its Very Useful...

    ReplyDelete
  7. AnonymousMarch 25, 2020 at 11:25 AM

    I will recommend anyone looking for Business loan to Le_Meridian they helped me with Four Million USD loan to startup my Quilting business and it's was fast When obtaining a loan from them it was surprising at how easy they were to work with. They can finance up to the amount of $500,000.000.00 (Five Hundred Million Dollars) in any region of the world as long as there 1.9% ROI can be guaranteed on the projects.The process was fast and secure. It was definitely a positive experience.Avoid scammers on here and contact Le_Meridian Funding Service On. lfdsloans@lemeridianfds.com / lfdsloans@outlook.com. WhatsApp...+ 19893943740. if you looking for business loan.

    ReplyDelete
  8. AnonymousJuly 18, 2021 at 7:54 AM

    Very great full.

    Regards
    Virendra Yadav

    ReplyDelete
  9. flynnspencerNovember 22, 2022 at 11:16 AM

    Are you spending your valuable time tracking down payments and keeping the billing system up to date? Are you not receiving your payments on time? Are you finding it difficult matching payments to invoices, constantly having to resend copies of invoices? Accounts Receivable

    ReplyDelete

Subscribe to: Post Comments (Atom)