AP Invoice flow Technical Details with Invoice Functional

AP Invoice Technical Details with Functional Inputs

When Invoice Booked and Saved
===========================
One row created in ap_invoices_all and its distribution lines created in ap_invoice_distributions_all

When Invoice Validated :
======================
ap_invoice_distributions_all.MATCH_STATUS_FLAG='A'
ap_invoice_distributions_all.ACCOUNTING_EVENT_ID=NOT NULL(Here 1370092)
one row created in ap_accounting_events_all with accounting_event_id=ap_invoice_distributions_all.ACCOUNTING_EVENT_ID ap_accounting_events_all.EVENT_STATUS_CODE='CREATED' ap_accounting_events_all.SOURCE_TABLE='AP_INVOICES'
ap_accounting_events_all.SOURCE_ID=ap_invoice_distributions_all.INVOICE_ID=
AP_INVOICE_ALL.INVOICE_ID

When Invoice Accounted :
=====================
ap_invoice_distributions_all.ACCRUAL_POSTED_FLAG='Y'
ap_invoice_distributions_all.POSTED_FLAG='Y' ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'
ONE ROW CREATED IN AP_AE_HEADERS_ALL where AP_AE_HEADERS_ALL. accounting_event_id=ap_accounting_events_all.accounting_event_id Rows created in ap_ae_lines_all
where AP_AE_HEADERS_ALL.ae_header_id=ap_ae_lines_all.ae_header_id as
below The number of rows generally created in ap_ae_lines_all counted as 1)
one row for invoice with ap_ae_lines_all.AE_LINE_TYPE_CODE='LIABILITY'
ap_ae_lines_all.SOURCE_TABLE='AP_INVOICES' ,ap_ae_lines_all.source_id=ap_invoices_all.invoice_id2)
Other rows are created for the invoice distribution lines (one line per invoice distribution line).ap_ae_lines_all.AE_LINE_TYPE_CODE='CHARGE',
SOURCE_TABLE='AP_INVOICE_DISTRIBUTIONS',
ap_ae_lines_all.SOURCE_ID=AP_INVOICE_DISTRIBUTIONS.INVOICE_ID

When Invoice Approved : 
========================
ap_invoices_all.WFAPPROVAL_STATUS='MANUALLY APPROVED', initially it was 'REQUIRED'

When Payment Created
=====================
when payment created the one record created in ap_checks_all table.

When Payment Accounted
=============================
When payment document accounted then one row is created in ap_accounting_events_all table.
AP_invoice_payments_all.ACCOUNTING_EVENT_ID=
ap_accounting_events_all.ACCOUNTING_EVENT_ID
ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'. andap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id.

After Doing the Payment (paid) of invoice with Created Payment Document
=============================================================
ap_invoices_all.payment_status_flag='Y' BEFORE 'N'It creates the linKing between ap_invoices_all and ap_checks_all by AP_INVOICE_PAYMENTS_ALL.one row created in AP_INVOICE_PAYMENTS_ALL with reference of invoice id.
AP_INVOICE_PAYMENTS_ALL.ACCRUAL_POSTED_FLAG='Y'
AP_INVOICE_PAYMENTS_ALL.CASH_POSTED_FLAG='Y'
AP_INVOICE_PAYMENTS_ALL.POSTED_FLAG='Y'and when get void the AP_INVOICE_PAYMENTS_ALL.REVERSAL_FLAG='Y' unless it is 'N'
When payment got accounted the one row created in ap_accounting_events_all with ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'

After Clearing Check from cash management
=====================================
open payment document and create accounting for it, showing partial now.
after successfull accounting of the document:
one line is created in AP_PAYMENT_HISTORY_all with new accounting _event_id.
AP_PAYMENT_HISTORY_all.accounting_event_id=
ap_accounting_events_all.accounting_event_idone new line created in ap_accounting_events_all with EVENT_TYPE_CODE='PAYMENT CLEARING'and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'
ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id andAP_PAYMENT_HISTORY_all.accounting_event_id=
ap_accounting_events_all.accounting_event_id
one row created in AP_AE_HEADERS_ALL with new accounting_event_id and two rows in this case created in ap_ae_lines_all with AE_LINE_TYPE_CODE='CASH CLEARING ' AND 'CASH',SOURCE_TABLE='AP_CHECKS'.


 AP invoice to XLA link
 ======================
xla_distribution_links l  table
liked in AP_INVOIVES_ALL table and AP_INVOICE_DISTRIBUTIONS_ALL
  AND l.applied_to_source_id_num_1 = i.invoice_id
  AND l.source_distribution_id_num_1 = ad.invoice_distribution_id

 xla.xla_transaction_entities xte  table =   xte.entity_code = 'AP_INVOICES'

  XLA to GL 
 ===========

apps.gl_import_references r,
apps.xla_ae_lines al,

LINK
=====
 and al.gl_sl_link_id = r.gl_sl_link_id

When Posted in GL (GL_POSTING)
===============================
after running the request "Payables Transfer to General Ledger".The ap_ae_lines_all.GL_SL_LINK_ID populates.AP_AE_HEADERS_ALL.GL_TRANSFER_FLAG='Y'AP_AE_HEADERS_ALL.
GL_TRANSFER_RUN_ID IS NOT NULL AP_AE_HEADERS_ALL.TRIAL_BALANCE_FLAG='Y'

Query 
=========

/* Formatted on 12/26/2013 9:33:44 AM (QP5 v5.114.809.3010) */
SELECT   A.ORG_ID "ORG ID",
         E.VENDOR_NAME "VENDOR NAME",
         UPPER (E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
         F.VENDOR_SITE_CODE "VENDOR SITE",
         F.ADDRESS_LINE1 "ADDRESS",
         F.CITY "CITY",
         F.COUNTRY "COUNTRY",
         TO_CHAR (TRUNC (D.CREATION_DATE)) "PO DATE",
         D.SEGMENT1 "PO NUMBER",
         D.TYPE_LOOKUP_CODE "PO TYPE",
         C.QUANTITY_ORDERED "QTY ORDERED",
         C.QUANTITY_CANCELLED "QTY CANCALLED",
         G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
         G.UNIT_PRICE "UNIT PRICE",
         (NVL (C.QUANTITY_ORDERED, 0) - NVL (C.QUANTITY_CANCELLED, 0))
         * NVL (G.UNIT_PRICE, 0)
            "PO Line Amount",
         (SELECT   DECODE (PH.APPROVED_FLAG, 'Y', 'Approved')
            FROM   PO.PO_HEADERS_ALL PH
           WHERE   PH.PO_HEADER_ID = D.PO_HEADER_ID)
            "PO STATUS",
         A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
         A.INVOICE_AMOUNT "INVOICE AMOUNT",
         TO_CHAR (TRUNC (A.INVOICE_DATE)) "INVOICE DATE",
         A.INVOICE_NUM "INVOICE NUMBER",
         (SELECT   DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   AP.AP_INVOICE_DISTRIBUTIONS_ALL X
           WHERE   X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)
            "Invoice Approved?",
         A.AMOUNT_PAID,
         H.AMOUNT,
         I.CHECK_NUMBER "CHEQUE NUMBER",
         TO_CHAR (TRUNC (I.CHECK_DATE)) "PAYMENT DATE"
  FROM   AP.AP_INVOICES_ALL A,
         AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
         PO.PO_DISTRIBUTIONS_ALL C,
         PO.PO_HEADERS_ALL D,
         PO.PO_VENDORS E,
         PO.PO_VENDOR_SITES_ALL F,
         PO.PO_LINES_ALL G,
         AP.AP_INVOICE_PAYMENTS_ALL H,
         AP.AP_CHECKS_ALL I
 WHERE       A.INVOICE_ID = B.INVOICE_ID
         AND B.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+)
         AND C.PO_HEADER_ID = D.PO_HEADER_ID(+)
         AND E.VENDOR_ID(+) = D.VENDOR_ID
         AND F.VENDOR_SITE_ID(+) = D.VENDOR_SITE_ID
         AND D.PO_HEADER_ID = G.PO_HEADER_ID
         AND C.PO_LINE_ID = G.PO_LINE_ID
         AND A.INVOICE_ID = H.INVOICE_ID
         AND H.CHECK_ID = I.CHECK_ID
         AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
         AND C.PO_HEADER_ID IS NOT NULL
         AND A.PAYMENT_STATUS_FLAG = 'Y'
         AND D.TYPE_LOOKUP_CODE != 'BLANKET';


account paybles(ap)module important tables in oracle apps 

===============================================

--ACCOUNT PAYBLES(AP) IMPORTANT TABLES

--INVOICES

Select * from   AP_INVOICES_ALL --IN 11I

Select * from   AP_INVOICE_DISTRIBUTIONS_ALL  --IN 11I

Select * from   AP_INVOICES_ALL

Select * from   AP_INVOICE_LINES_ALL

Select * from   AP_INVOICE_DISTRIBUTIONS_ALL

--ACCOUNTING

Select * from   AP_AE_HEADERS_ALL  11I 

Select * from   AP_AE_LINES_ALL  11I 

--JOURNALS

Select * from   GL_JE_HEADERS

Select * from   GL_JE_LINES

--PAYMENTS

Select * from   AP_INVOICE_PAYMENTS_ALL

Select * from   AP_CHECKS_ALL

--DISTRIBUTION SETS

Select * from   AP_DISTRIBUTION_SETS_ALL

Select * from   AP_DISTRIBUTION_SET_LINES_ALL

--PAYMENT TERMS

Select * from   AP_TERMS_TL

Select * from   AP_TERMS_LINES

--SCHEDULE PAYMENTS

Select * from   AP_PAYMENT_SCHEDULES_ALL

--HOLD

Select * from   AP_HOLDS_ALL

--SUPPLIERS

Select * from   PO_VENDORS

Select * from   PO_VENDOR_SITES_ALL

Select * from   PO_VENDOR_CONTACTS

Select * from AP_SUPPLIERS

Select * from AP_SUPPLIERS_SITS_ALL

--INVOICE TYPES

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='INVOICE_TYPE')

--INVOICE DIST TYPES

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='INVOICE_DISTRIBUTION_TYPE')

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='SOURCE')