R12 AP SQLS

select * from ap_invoices_all where invoice_num like '25-apr-2012';

select * from AP_INVOICE_LINES_ALL where invoice_id=1181871;

select * from AP_INVOICE_DISTRIBUTIONS_ALL where invoice_id=1181871;

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=1181871;

select * from AP_PAYMENT_SCHEDULES_ALL where INVOICE_ID=1181871;

--SELECT * FROM AP_ACCOUNTING_EVENTS_ALL WHERE SOURCE_TABLE='AP_INVOICES' AND SOURCE_ID=1181871;

SELECT * FROM XLA.XLA_TRANSACTION_ENTITIES WHERE SOURCE_ID_INT_1='1181871';

SELECT * FROM XLA.XLA_TRANSACTION_ENTITIES WHERE SOURCE_ID_INT_1='1181871' AND ENTITY_CODE='AP_INVOICES';
--TRANSACTION_NUMBER => INVOICE_NUMBER
-- LEDGER_ID
--GET THE EVENT_ID= 4590221

SELECT * FROM XLA.XLA_EVENTS WHERE ENTITY_ID=4590221;
--=> AFTER DRAFT ACCOUNTING
--GET THE EVENT_ID- 4623169
--EVENT_STATUS_CODE U => UNPOSTED
--PROCESS_STATUS_CODE - D => DRAFT
--EVENT_TYPE_CODE
--=> AFTER FINAL ACCOUNTING
--EVENT_STATUS_CODE U => P
--PROCESS_STATUS_CODE - P

SELECT * FROM XLA.XLA_AE_HEADERS WHERE ENTITY_ID=4590221 AND EVENT_ID=4623169;
--=> AFTER DRAFT ACCOUNTING
--ACCOUNTING_ENTRY_STATUS_CODE F
--=> AFTER FINAL ACCOUNTING
--ACCOUNTING_ENTRY_STATUS_CODE F

SELECT * FROM XLA.XLA_AE_LINES WHERE AE_HEADER_ID=8489753;
-- AE LINES WHEN DRAFT

SELECT * FROM XLA.XLA_AE_LINES WHERE AE_HEADER_ID=8489758;
-- AE LINES WHEN FINAL

--AE HEADER ID IS CHANGED AFTER ACCOUTNING TO FINAL



-----------------------------------
--New R12 tables  -> Old 11i Tables
--AP_SUPPLIERS - replaces PO_VENDORS
--AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

SELECT * FROM AP_SUPPLIERS
WHERE VENDOR_ID=1193;
--> GET PARTY ID FROM HERE 160297

SELECT * FROM AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID=1193 AND VENDOR_SITE_ID=57459;
--> GET PATRY SITE ID FROM HERE

--Additional supplier related tables in IBY(Payments) and HZ(TCA): IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.
--HZ_PARTIES - Party data for the suppliers.
--HZ_PARTY_SITES - Party site data for the supplier sites.

SELECT * FROM HZ_PARTIES WHERE PARTY_ID=160297;

SELECT * FROM  HZ_PARTY_SITES WHERE PARTY_ID =160297 AND PARTY_SITE_ID=60405;

SELECT * FROM IBY_EXTERNAL_PAYEES_ALL WHERE PAYEE_PARTY_ID=160297 AND PARTY_SITE_ID=60405;


--Invoices
--Additional table in R12: AP_INVOICE_LINES_ALL
--Allocations - ap_chrg_allocations_all is obsolete in R12
--Taxes
--Functionality provided by E-Business Tax
--New tables in R12
--ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)
--ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)
--ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = invoice_id)
--ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

--Payments
--Functionality moved to central Payments (IBY)

--New IBY tables in R12:
--IBY_PAY_SERVICE_REQUESTS  - Payment Process Request information (11i Terminology is  Payment Batch)
--IBY_PAY_INSTRUCTIONS_ALL - Payment Instruction information
--IBY_DOC_PAYABLES_ALL - Invoice information stored by IBY for generating payment
--IBY_PAYMENTS_ALL - Payment Information

--The following tables are used in PPR in R12 . These were used in 11i for payment batches also.
--AP_INV_SELECTION_CRITERIA_ALL
--AP_SELECTED_INVOICES_ALL

--The following AP tables are still in use in R12 to store payment related information.
--AP_INVOICE_PAYMENTS_ALL
--AP_CHECKS_ALL
--AP_PAYMENT_HISTORY_ALL


--Accounting
--Functionality moved to SubLedger Accounting (SLA)
--New R12 tables:
--XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL
--XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL
--XLA_AE_LINES-> replaces AP_AE_LINES_ALL
--XLA_DISTRIBUTION_LINKS

--Trial Balance
--New R12 Table
--XLA_TRIAL_BALANCES

--AP_LIABILITY_BALANCE-> not used in new R12 transactions
--AP_TRIAL_BALANCE -> not used in new R12 transactions


--Bank Accounts
--Functionality moved 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 -> AP_CHECK_STOCKS_ALL