TCA - Oracle R12 Trading Community Architecture ER Diagram (Table Joinings)

Oracle Supplier Overview

You must define a supplier before performing most activities within Purchasing and Payables.

•You optionally enter a recommended supplier on a requisition.

•You need a supplier to issue a request for quotation.

•You use that same supplier when you enter a quotation.

•You need supplier information for purchase orders.

•You receive goods or services from suppliers.

•You return goods to suppliers.

•You must pay the supplier for the goods or services purchased.

Set up suppliers to record information about individuals and companies you purchase goods and services from.  You can also enter employees you reimburse for expense reports.  You can designate supplier sites as pay sites, purchasing sites, RFQ only sites, or procurement card sites.  For example, for a single supplier, you can buy from several different sites and send payments to several different sites.  Most supplier information automatically defaults to all supplier sites to facilitate supplier site entry.  However, you can override these defaults and have unique information for each site.

Note that the basic supplier definition is now managed by Trading Community Architecture (TCA), which is the central engine for managing Trading Partner information in E-Business Suite. TCA provides a single common definition that can be used to identify customers, suppliers, and organizations that provide you with goods or services, and are in turn, a customer of your own products or services. The TCA repository stores the key elements that define an organization, identity, business locations, and key contacts, so that different Oracle products use a common trading partner definition.

Query to Get the Oracle AR Invoice Account Details

SELECT                                     --distinct source_distribution_type

         gl.NAME, rctt.NAME AS trx_type, acra.trx_number, hp.party_name,

         --  (select name from gl_ledgers where ledger_id = acra.set_of_bookS_id) lEDGER_NAME,

         acra.customer_trx_id, acra.creation_date,

         gcc1.concatenated_segments AS ACCOUNT, acra.trx_date,

         DECODE (xah1.application_id,

                 200, 'Payables',

                 222, 'Receivables',

                 xah1.application_id

                ) SOURCE,

         xah1.gl_transfer_status_code, xah1.je_category_name,

         --xal1.code_combination_id,

         xal1.accounting_class_code,        

         --xte1.*

         --nvl(sum(xdl1.unrounded_accounted_dr),0) - nvl(sum(xdl1.unrounded_accounted_cr),0) xla_adjustment

         (NVL (SUM (xal1.accounted_dr), 0) - NVL (SUM (xal1.accounted_cr), 0)

         ) accounted_adjustment,

           NVL (SUM (xal1.entered_dr), 0)

         - NVL (SUM (xal1.entered_cr), 0) entered_adjustment

    FROM xla_ae_headers PARTITION (ar) xah1,

         xla_ae_lines PARTITION (ar) xal1,

         --xla_distribution_links xdl1

         --xla_events xe1,

         xla.xla_transaction_entities xte1,

         ra_customer_trx_all acra,

         gl_code_combinations_kfv gcc1,

         gl_ledgers gl,

         apps.ra_cust_trx_types_all rctt,

         hz_cust_accounts hca,

         hz_cust_acct_sites_all hcas,

         hz_cust_site_uses_all hcsu,

         hz_parties hp,

         hr_operating_units hou

   WHERE 1 = 1

     AND acra.set_of_books_id = gl.ledger_id

     AND acra.cust_trx_type_id = rctt.cust_trx_type_id

     AND xah1.application_id = 222

--     AND xah1.ledger_id = 2193                      --ARROW CENTRAL EUROPE

     --AND xah1.accounting_date BETWEEN :PERIOD_START_DATE   AND :PERIOD_END_DATE

     AND xal1.ae_header_id = xah1.ae_header_id

     --AND xal1.code_combination_id = :code_combination_id

     --and xe1.aevent_id=xah1.event_id and xe1.entity_id=xah1.entity_id

     AND xte1.entity_id = xah1.entity_id

     AND acra.customer_trx_id = xte1.source_id_int_1

     AND hca.cust_account_id = hcas.cust_account_id

     AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

     AND hca.party_id = hp.party_id

     AND hcsu.site_use_code = 'BILL_TO'

     AND hou.organization_id = acra.org_id

     AND hcsu.org_id = hou.organization_id

     AND hca.cust_account_id = acra.sold_to_customer_id

     AND gcc1.code_combination_id = xal1.code_combination_id

--and xdl1.ae_header_id=xal1.ae_header_id and xdl1.ae_line_num=xal1.ae_line_num and xdl1.application_id=222

--and aca1.cash_receipt_id=xdl1.applied_to_source_id_num_1

GROUP BY xah1.application_id,

         xah1.gl_transfer_status_code,

         xah1.je_category_name,

         xal1.code_combination_id,

         xal1.accounting_class_code,

         acra.trx_number,

         acra.customer_trx_id,

         acra.creation_date,

         acra.trx_date,

         gcc1.concatenated_segments,

         gl.NAME,

         rctt.NAME,

         hp.party_name

  HAVING (NVL (SUM (xal1.accounted_dr), 0) - NVL (SUM (xal1.accounted_cr), 0)) <> 0

      OR NVL (SUM (xal1.entered_dr), 0) - NVL (SUM (xal1.entered_cr), 0) <> 0

Trading Community Architecture (TCA)

Query to get the Oracle Order to Cash (O2C) Details

select ooha.order_number,ooha.org_id,

       hca.account_name,

       hp.party_name "Customer Name",

       hcasab.orig_system_reference      BILL_TO_ORIG_REF,

       hpsb.status                       BILL_TO_STATUS,

       'ADDRESS1 - '||bill_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||

       'ADDRESS3 - '||bill_loc.address3||','||CHR(10)|| 'CITY     - '||bill_loc.city||','||CHR(10)||

       'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)|| 'COUNTRY  - '|| bill_loc.country  BILL_TO_ADDRESS,

       hcasas.orig_system_reference      SHIP_TO_ORIG_REF,

       hpss.status SHIP_TO_STATUS,

       'ADDRESS1 - '||ship_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||

       'ADDRESS3 - '||ship_loc.address3||','||CHR(10)|| 'CITY     - '||ship_loc.city||','||CHR(10)||

       'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)|| 'COUNTRY  - '|| ship_loc.country  SHIP_TO_ADDRESS,

       oola.inventory_item_id,oola.ordered_item,

       msib.description item_description,

       wnd.name delivery_number,

       rct.trx_number "AR Invoice Number",

       acr.receipt_number "AR Receipt Number",

       gjh.ledger_id,

       gjh.name

  from oe_order_headers_all ooha,

       oe_order_lines_all oola,

       hz_parties hp,

       hz_cust_accounts hca,

       hz_party_sites hpss,

       hz_party_sites hpsb,

       hz_locations bill_loc,

       hz_locations ship_loc,

       hz_cust_acct_sites_all hcasab,

       hz_cust_acct_sites_all hcasas,

       hz_cust_site_uses_all hzsuab,

       hz_cust_site_uses_all hzsuas,

       mtl_system_items_b msib,

       wsh_delivery_details wdd,

       wsh_new_deliveries wnd,

       wsh_delivery_assignments wda,

       ra_customer_trx_all rct,

       ra_customer_trx_lines_all rctl,

       ra_cust_trx_line_gl_dist_all rctld,      

       ar_cash_receipts_all acr,

       xla.xla_transaction_entities xte,

       xla_events xe,

       xla_ae_headers xah,

       xla_ae_lines xal,

       xla_distribution_links xdl,

       gl_import_references gir,

       gl_je_batches gjb,

       gl_je_headers gjh,

       gl_je_lines gjl

 where ooha.order_number = :SalesOrderNumber

   and ooha.org_id = 204

   and hca.cust_account_id    = ooha.sold_to_org_id

   and hp.party_id            = hca.party_id

   and hpss.party_id            = hca.party_id

   and hpsb.party_id            = hca.party_id

   and bill_loc.location_id = hpss.location_id

   and ship_loc.location_id = hpsb.location_id

   AND hcasas.cust_account_id  = hca.cust_account_id

   AND hcasab.cust_account_id  = hca.cust_account_id

   AND hcasas.party_site_id    = hpss.party_site_id

   AND hcasab.party_site_id    = hpsb.party_site_id

   and hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id

   and hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id

   and hzsuas.site_use_id = ooha.ship_to_org_id

   and hzsuab.site_use_id = ooha.invoice_to_org_id          

   and wda.delivery_id        = wnd.delivery_id(+)

   and wdd.delivery_detail_id = wda.delivery_detail_id

   and wdd.source_header_id   = ooha.header_id

   and wdd.source_line_id     = oola.line_id

   and wdd.organization_id    = msib.organization_id(+)

   and wdd.inventory_item_id  =msib.inventory_item_id(+)

   and rct.interface_header_attribute1 = to_char(ooha.order_number)

   and rct.org_id = ooha.org_id

   and rctl.customer_trx_id = rct.customer_trx_id

   and rctl.sales_order = to_char(ooha.order_number)

   and rctld.customer_trx_id = rct.customer_trx_id

   and rctld.customer_trx_line_id = rctl.customer_trx_line_id

   and acr.receipt_number = 'G-1001'

   and acr.pay_from_customer = rct.sold_to_customer_id

   and acr.org_id = ooha.org_id

   and acr.customer_site_use_id = rct.bill_to_site_use_id

   and xte.transaction_number = acr.receipt_number

   and xte.entity_code = 'RECEIPTS'

   and xe.entity_id = xte.entity_id

   and xah.event_id = xe.event_id

   and xal.ae_header_id = xah.ae_header_id

   and xal.accounting_class_code = 'CASH'

   and xdl.ae_header_id = xah.ae_header_id

   and xdl.ae_line_num = xal.ae_line_num

   --and xdl.source_distribution_id_num_1

   and gir.reference_5 = xte.entity_id  -- Entity Id

   and gir.reference_6 = to_char(xe.event_id) --Event Id

   and gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id

   and gir.gl_sl_link_id = xal.gl_sl_link_id

   and gir.created_by = 1318

   and gjb.je_batch_id = gir.je_batch_id

   and gjh.je_batch_id=gjb.je_batch_id

   and gjh.je_header_id = gir.je_header_id

   and gjl.je_header_id=gjh.je_header_id

   and gjl.je_line_num= gir.je_line_num