发票数据流

--1.发票创建时生成数据如下表
--发票主表
SELECT * FROM AP_INVOICES_ALL A WHERE A.INVOICE_NUM = 'ap001';--id:425774
--发票分配表
SELECT b.prepay_distribution_id,b.* FROM AP_INVOICE_DISTRIBUTIONS_ALL B WHERE B.INVOICE_ID = 425774;
--发票付款计划表,保存发票主表数据自动生成付款计划数据
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL C WHERE C.INVOICE_ID = 425774;

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

--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 = 'ap001'
   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 = 425774;
--CHECK_ID From AP_INVOICE_PAYMENTS_ALL
SELECT * FROM AP_CHECKS_ALL H WHERE H.CHECK_ID = 229199; --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 = 514923; --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 AAE.ACCOUNTING_EVENT_ID=514923;

--6. 发票核销预付款发票的金额
SELECT AID1.INVOICE_ID              发票id
      ,AID1.INVOICE_DISTRIBUTION_ID 发票分配行id
      ,AID1.DESCRIPTION             发票分配行描述
      ,AID1.PREPAY_DISTRIBUTION_ID  预付款发票分配行id
      ,AID1.AMOUNT                  核销金额
      ,AI.INVOICE_NUM               预付款发票编号
      ,AI.DESCRIPTION               预付案发票描述
  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 AID1.TAX_CODE_ID = ATC.TAX_ID(+)
   AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
   AND AID1.INVOICE_ID = 425774; --发票ID
--7. 预付款发票核销发票的金额
SELECT AID1.INVOICE_ID               发票ID                    
      ,AID1.INVOICE_DISTRIBUTION_ID  发票分配行ID
      ,AID1.DESCRIPTION              发票分配行描述
      ,AID1.PREPAY_DISTRIBUTION_ID   预付款发票分配行ID
      ,AID1.AMOUNT                   核销金额                   
      ,AI.INVOICE_NUM                发票编号
      ,AI.DESCRIPTION                发票描述
      ,AID2.INVOICE_ID               预付款发票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 AID2.TAX_CODE_ID = ATC.TAX_ID(+)
   AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
   AND AID2.INVOICE_ID = 362510 --预付款发票ID
   AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN
       ('PREPAYMENT'
       ,'CREDIT'
       ,'DEBIT');

--发票匹配接收时与接收的关联关系
SELECT A.RCV_TRANSACTION_ID--rcv_transactions.transaction_id
      ,A.PO_DISTRIBUTION_ID
      ,A.*
  FROM AP_INVOICE_DISTRIBUTIONS_ALL A
 WHERE A.INVOICE_ID = ?;