O​R​A​C​L​E​ ​E​B​S​ ​A​P​发​票​到​付​款​的​数​据​流

--1.发票创建时生成数据如下表
--发票主表
SELECT * FROM AP_INVOICES_ALL A WHERE A.INVOICE_NUM = '20111213001';
--发票分配表
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL B WHERE B.INVOICE_ID = 697444;
--发票付款计划表
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL C WHERE C.INVOICE_ID = 697444; --From ap_invoices_all.invoice_id


--2.发票验证并创建会计科目时产生的数据如下表
--发票验证时产生的数据
SELECT *
  FROM AP_ACCOUNTING_EVENTS_ALL D
 WHERE D.SOURCE_ID = 697444
   AND D.SOURCE_TABLE = 'AP_INVOICES'; --From ap_invoices_all.invoice_id
--发票创建会计科目时产生的分录
SELECT * FROM AP_AE_HEADERS_ALL E WHERE E.ACCOUNTING_EVENT_ID = 1093101; --From AP_ACCOUNTING_EVENTS_ALL.souce_id = invoice_id
SELECT * FROM AP_AE_LINES_ALL G WHERE G.AE_HEADER_ID = 1088969;


--3.发票分录查询(SOURCE_TABLE栏位分别是AP_INVOICE_DISTRIBUTIONS和AP_INVOICES，代表发票的分录和发票分配的分录)
SELECT AAL.SOURCE_TABLE, AAL.*
  FROM AP_INVOICES_ALL          AIA,
       AP_ACCOUNTING_EVENTS_ALL AAE,
       AP_AE_HEADERS_ALL        AAH,
       AP_AE_LINES_ALL          AAL
 WHERE AIA.INVOICE_NUM = '20111213001'
   AND AIA.INVOICE_ID = AAE.SOURCE_ID
   AND AAE.SOURCE_TABLE = 'AP_INVOICES'
   AND AAH.ACCOUNTING_EVENT_ID = AAE.ACCOUNTING_EVENT_ID
   AND AAH.AE_HEADER_ID = AAL.AE_HEADER_ID;


--4.发票付款时产生的数据如下表
SELECT * FROM AP_INVOICE_PAYMENTS_ALL F WHERE F.INVOICE_ID = 697444;
--CHECK_ID From AP_INVOICE_PAYMENTS_ALL
SELECT * FROM AP_CHECKS_ALL H WHERE H.CHECK_ID = 2357756; --CHECK_ID From AP_INVOICE_PAYMENTS_ALL.CHECK_ID
--ACCOUNTING_EVENT_ID From AP_INVOICE_PAYMENTS_ALL.ACCOUNTING_EVENT_ID
SELECT *
  FROM AP_ACCOUNTING_EVENTS_ALL D
 WHERE D.ACCOUNTING_EVENT_ID = 1086193; --ACCOUNTING_EVENT_ID From AP_INVOICE_PAYMENTS_ALL.ACCOUNTING_EVENT_ID


--5.付款分录查询
SELECT AAL.*
  FROM AP_CHECKS_ALL            ACA,
       AP_ACCOUNTING_EVENTS_ALL AAE
       -- ,AP_INVOICE_PAYMENTS_ALL  AIP
      ,
       AP_AE_HEADERS_ALL AAH,
       AP_AE_LINES_ALL   AAL
 WHERE ACA.CHECK_ID = AAE.SOURCE_ID
   AND AAE.SOURCE_TABLE = 'AP_CHECKS'
   AND AAE.ACCOUNTING_EVENT_ID = AAH.ACCOUNTING_EVENT_ID
   AND AAH.AE_HEADER_ID = AAL.AE_HEADER_ID
      -- AND AAL.SOURCE_ID = AIP.INVOICE_PAYMENT_ID--负债科目
      --AND AAL.SOURCE_ID = ACA.CHECK_ID--现金科目
   AND AAH.ACCOUNTING_DATE >= TO_DATE('20110907', 'yyyymmdd')
   AND AAH.ACCOUNTING_DATE < TO_DATE('20110908', 'yyyymmdd')
   AND AAH.GL_TRANSFER_FLAG = 'Y'
   AND AAH.ORG_ID = 236;


--6. 发票核销预付款发票的金额
SELECT AID1.ROWID ROW_ID,
       AID1.INVOICE_ID INVOICE_ID,
       AID1.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID,
       AID1.PREPAY_DISTRIBUTION_ID PREPAY_DISTRIBUTION_ID,
       AID1.DISTRIBUTION_LINE_NUMBER PREPAY_DIST_NUMBER,
       (-1) * AID1.AMOUNT PREPAY_AMOUNT_APPLIED,
       NVL(AID2.PREPAY_AMOUNT_REMAINING, AID2.AMOUNT) PREPAY_AMOUNT_REMAINING,
       AID1.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID,
       AID1.ACCOUNTING_DATE ACCOUNTING_DATE,
       AID1.PERIOD_NAME PERIOD_NAME,
       AID1.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
       AID1.DESCRIPTION DESCRIPTION,
AID1.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID,
       AID1.RCV_TRANSACTION_ID RCV_TRANSACTION_ID,
       AID1.ORG_ID ORG_ID,
       AI.INVOICE_NUM PREPAY_NUMBER,
       AI.VENDOR_ID VENDOR_ID,
       AI.VENDOR_SITE_ID VENDOR_SITE_ID,
       ATC.TAX_ID TAX_ID,
       ATC.NAME TAX_CODE
  FROM AP_INVOICES_ALL              AI,
       AP_INVOICE_DISTRIBUTIONS_ALL AID1,
       AP_INVOICE_DISTRIBUTIONS_ALL AID2,
       AP_TAX_CODES                 ATC
 WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
   AND AI.INVOICE_ID = AID2.INVOICE_ID
   AND AID1.AMOUNT < 0
   AND NVL(AID1.REVERSAL_FLAG, 'N') != 'Y'
   AND AID1.TAX_CODE_ID = ATC.TAX_ID(+)
   AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
   AND AID1.INVOICE_ID = 676404; --发票ID
--6. 预付款发票核销发票的金额
SELECT AID1.ROWID ROW_ID,
       AID1.INVOICE_ID INVOICE_ID,
       AID1.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID,
       AID1.PREPAY_DISTRIBUTION_ID PREPAY_DISTRIBUTION_ID,
       AID2.DISTRIBUTION_LINE_NUMBER PREPAY_DIST_NUMBER,
       (-1) * AID1.AMOUNT PREPAY_AMOUNT_APPLIED,
       AID1.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID,
       AID1.ACCOUNTING_DATE ACCOUNTING_DATE,
       AID1.PERIOD_NAME PERIOD_NAME,
       AID1.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
       AID1.DESCRIPTION DESCRIPTION,
       AID1.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID,
       AID1.RCV_TRANSACTION_ID RCV_TRANSACTION_ID,
       AID1.ORG_ID ORG_ID,
       AI.INVOICE_NUM INVOICE_NUM,
       AI.VENDOR_ID VENDOR_ID,
       AI.VENDOR_SITE_ID VENDOR_SITE_ID,
 ATC.TAX_ID TAX_ID,
       ATC.NAME TAX_CODE,
       AID2.INVOICE_ID PREPAY_ID
  FROM AP_INVOICES_ALL              AI,
       AP_INVOICE_DISTRIBUTIONS_ALL AID1,
       AP_INVOICE_DISTRIBUTIONS_ALL AID2,
       AP_TAX_CODES                 ATC
 WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
   AND AI.INVOICE_ID = AID1.INVOICE_ID
   AND AID1.AMOUNT < 0
   AND NVL(AID1.REVERSAL_FLAG, 'N') != 'Y'
   AND AID2.TAX_CODE_ID = ATC.TAX_ID(+)
   AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
   AND AID2.INVOICE_ID = 676444 --预付款发票ID
   AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT', 'CREDIT', 'DEBIT');
--发票是否被验证的脚本


/*BEGIN
fnd_client_info.set_org_context(236);
END;*/
SELECT AP_INVOICES_PKG.GET_APPROVAL_STATUS(AI.INVOICE_ID,
                                           AI.INVOICE_AMOUNT,
                                           AI.PAYMENT_STATUS_FLAG,
                                           AI.INVOICE_TYPE_LOOKUP_CODE) --发票是否已验证，已验证的状态为APPROVED
      ,
       AI.*
  FROM AP_INVOICES_ALL AI
 WHERE AI.INVOICE_NUM IN ('2011110888', '20111202001');


--应付发票及付款日记账分录追溯


--日记账分录
SELECT AAL.*, GJL.*
  FROM AP_AE_HEADERS_ALL AAH,
       AP_AE_LINES_ALL   AAL,
       GL_JE_HEADERS     GJH,
 GL_JE_LINES       GJL
 WHERE AAH.AE_HEADER_ID = AAL.AE_HEADER_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
   AND GJH.JE_SOURCE = 'Payables'
   AND GJH.PERIOD_NAME = 'Dec-11'
   AND AAH.AE_HEADER_ID = 1097269;


--发票匹配接收时与接收的关联关系


SELECT A.RCV_TRANSACTION_ID --rcv_transactions.transaction_id
      ,
       A.PO_DISTRIBUTION_ID,
       A.*
  FROM AP_INVOICE_DISTRIBUTIONS_ALL A
 WHERE A.INVOICE_ID = 703771;


--应付总账分录追溯到付款
SELECT DISTINCT ACA.CHECK_NUMBER,
                (SELECT INVOICE_NUM
                   FROM AP_INVOICES_ALL AIA
                  WHERE AIA.INVOICE_ID = AIP.INVOICE_ID) INVOICE_NUM
  FROM AP_AE_HEADERS_ALL        AAH,
       AP_AE_LINES_ALL          AAL,
       GL_JE_HEADERS            GJH,
       GL_JE_LINES              GJL,
       GL_JE_BATCHES            GJB,
       AP_CHECKS_ALL            ACA,
       AP_ACCOUNTING_EVENTS_ALL AAE,
       AP_INVOICE_PAYMENTS_ALL  AIP
 WHERE AAH.AE_HEADER_ID = AAL.AE_HEADER_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
   AND GJH.JE_SOURCE = 'Payables'
   AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
    
   AND ACA.CHECK_ID = AAE.SOURCE_ID
   AND AAE.SOURCE_TABLE = 'AP_CHECKS'
   AND AAE.ACCOUNTING_EVENT_ID = AAH.ACCOUNTING_EVENT_ID
      
   AND AIP.CHECK_ID = ACA.CHECK_ID
      
   AND GJH.PERIOD_NAME = 'Jan-12'
   AND GJB.NAME = '12.01.11报销 51026 Payables 25920611: A 4855700';
-- AND gjh.name = '800346773 Purchase Invoices CNY'


--应付总账分录追溯到发票
SELECT DISTINCT AIA.INVOICE_NUM
  FROM AP_AE_HEADERS_ALL AAH,
       AP_AE_LINES_ALL   AAL,
       GL_JE_HEADERS     GJH,
       GL_JE_LINES       GJL,
       GL_JE_BATCHES     GJB
       
      ,
       AP_INVOICES_ALL          AIA,
       AP_ACCOUNTING_EVENTS_ALL AAE


 WHERE AAH.AE_HEADER_ID = AAL.AE_HEADER_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
   AND GJH.JE_SOURCE = 'Payables'
   AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
      
   AND AIA.INVOICE_ID = AAE.SOURCE_ID
   AND AAE.SOURCE_TABLE = 'AP_INVOICES'
   AND AAH.ACCOUNTING_EVENT_ID = AAE.ACCOUNTING_EVENT_ID
      
   AND GJH.PERIOD_NAME = 'Jan-12'
   AND GJB.NAME = '12.01.11报销 51026 Payables 25920611: A 4855700'
-- AND gjh.name = '800346773 Purchase Invoices CNY'