oracle 发票剩余金额,ORACLE EBS AP发票到付款的数据流

--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_idSELECT * 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_ALLSELECT * 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_IDSELECT * 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');--发票是否被验证的脚本/*BEGINfnd_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'