11i to R12 - A Brief Overview of Table Level Changes

Suppliers: 

AP_SUPPLIERS  replaces PO.PO_VENDORS

AP_SUPPLIER_SITES_ALL replaces  PO.PO_VENDOR_SITES_ALL

But the PO_VENDORS and PO_VENDOR_SITES_ALL are still available as views based on new tables for backward compatibility. 

Supplier linking with HZ (TCA):

HZ_PARTIES - Party data for the suppliers (AP_SUPPLIER.part_id).

HZ_PARTY_SITES - Party site data for the supplier sites (AP_SUPPLIER_SITES_ALL.party_site_id).

Invoices:

New table in R12:   

AP_INVOICE_LINES_ALL

Taxes:

Functionality provided by E-Business Tax new tables in R12

ZX_LINES - Detailed Tax lines for the invoice (trx_id = ap_invoices_all.invoice_id)

ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = ap_invoices_all .invoice_id)

ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = ap_invoices_all.invoice_id)

ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = ap_invoices_all.invoice_id)

Accounting:

Functionality moved to Centralized Sub Ledger Accounting (SLA) model

New tables in R12 :

XLA_EVENTS                  -  (Similar to AP_Accounting_Events_All table but it is not limited to Payables module only)

XLA_AE_HEADERS        - (Similar to AP_AE_HEADERS table but it is not limited to Payables module only)

XLA_AE_LINES                -  (Similar to AP_AE_LINES table but it is not limited to Payables module only)

XLA_TRANSACTION_ENTITIES –  It contains transaction’s header level key info like invoice_id or check_id or cash_receipt_id  in SOURCE_ID_INT_1 column and operating unit (org_id) in SECURITY_ID_INT_1 column.

XLA_DISTRIBUTION_LINKS -    It contains the distribution level info of transactions such as Invoice_Distribution_id and etc

Join examples:

      1 -     Xla_Events.Event_Id  =  Ap_Invoice_Distributions.Accounting_Event_Id   

 

             Alternatively  

                       Xla_Transaction_Entities.Source_Id_Int_1 = Ap_Invoices.Invoice_id

                and Xla_Transaction_Entities.Security_Id_Int_1 = Ap_Invoices.Org_id

                and Xla_Transaction_Entities.Entity_code = ‘AP_INVOICES’

     

      2 -     Xla_Events.Event_Id  =  Ra_Cust_Trx_Line_Gl_Dist_All.Event_Id

           

               Alternatively  

            

                       Xla_Transaction_Entities.Source_Id_Int_1 = Ra_Customer_Trx_all.Customer_Trx _id

                and Xla_Transaction_Entities.Security_Id_Int_1 = Ra_Customer_Trx_all.Org_id

                and Xla_Transaction_Entities.Entity_code = ‘TRANSACTIONS’
 

      3 -     XLA_EVENTS.event_id  =  AP_INVOICE_PAYMENTS.accounting_event_id (AP Payment)

                Alternatively  

                       Xla_Transaction_Entities.Source_Id_Int_1 =AP_Checks_all.Check _id

                and Xla_Transaction_Entities.Security_Id_Int_1 = AP_Checks_all.Org_id

                and Xla_Transaction_Entities.Entity_code = ‘AP_PAYMENTS’

             

      4-      XLA_EVENTS.event_id  =  AR_CASH_RECEIPTS_HISTORY.event_id  (AR Receipts)

              

              Alternatively

  

                       Xla_Transaction_Entities.Source_Id_Int_1 =AR_Cash_Receipts_all.Cash_Receipt _id

                and Xla_Transaction_Entities.Security_Id_Int_1 =AR_Cash_Receipts_all.Org_id

                and Xla_Transaction_Entities.Entity_code = ‘RECEIPTS’

etc

Bank Accounts:

Functionality moved From Payables to Cash Management.

CE_BANK_ACCOUNTS  replaces AP_BANK_ACCOUNTS_ALL

CE_BANK_ACCT_USES_ALL  replaces AP_BANK_ACCOUNT_USES_ALL

CE_PAYMENT_DOCUMENTS replaces AP_CHECK_STOCKS_ALL

The old AP tables will also remain in db for backward compatibility only. All new bank accounts info will be stored in cash management tabeles.

References

1 – AP_CHECKS_ALL

    12.2:  

             AP_CHECKS_ALL.Ce_Bank_Acct_Use_Id = CE_BANK_ACCT_USES_ALL.Bank_Acct_Use_Id

 

    11i :  

             AP_CHECKS_ALL.Bank_Account_Id = AP_BANK_ACCOUNTS_ALL.Bank_Account_Id 

2- AR_CASH_RECEIPTS_ALL

   12.2: 

             AR_CASH_RECEIPTS_ALL.Remit_Bank_Acct_Use_Id = CE_BANK_ACCT_USES_ALL.Bank_Acct_Use_Id

 

   11i:  

             AR_CASH_RECEIPTS_ALL.Remittance_Bank_Account_Id = AP_BANK_ACCOUNTS_ALL.Bank_Account_Id

Customers & Bill To/Ship to Addresses:

AR_CUSTOMERS replaces RA_CUSTOMERS

HZ_CUST_SITE_USES  replaces RA_SITE_USES_ALL

HZ_LOCATIONS replaces RA_ADDRESSES_ALL

Old tables will be truncated and data will only be available in new tables after upgrade.

Additional Tables Involved:

HZ_CUST_ACCT_SITES_ALL

HZ_PARTY_SITES

 Example query:

SELECT AC.CUSTOMER_NUMBER ,

       AC.CUSTOMER_NAME , 

       HCSUA.SITE_USE_CODE ,

       HL.ADDRESS1    ,

       HL.ADDRESS2    ,

       HL.ADDRESS3    ,

       HL.CITY        ,

       HL.POSTAL_CODE ,

       HL.STATE       ,

       HL.COUNTRY

  FROM AR_CUSTOMERS AC ,

       HZ_CUST_ACCT_SITES_ALL HCASA ,

       HZ_PARTY_SITES HPS,

       HZ_CUST_SITE_USES_ALL HCSUA,

       HZ_LOCATIONS HL

WHERE AC.CUSTOMER_ID = HCASA.CUST_ACCOUNT_ID

  AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID

  AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID

  AND HCSUA.PRIMARY_FLAG = 'Y' -- Primary Addresses

  AND HCSUA.STATUS = 'A' -- Active

  AND HPS.LOCATION_ID = HL.LOCATION_ID