• ORACLE EBS AP invoice 到付款的数据流


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


    --2.Invoice验证并创建会计科目时产生的数据如下表
    --Invoice验证时产生的数据
    SELECT *
      FROM AP_ACCOUNTING_EVENTS_ALL D
     WHERE D.SOURCE_ID = 697444
       AND D.SOURCE_TABLE = 'AP_INVOICES'; --From ap_invoices_all.invoice_id
    --Invoice创建会计科目时产生的分录
    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.Invoice分录查询(SOURCE_TABLE栏位分别是AP_INVOICE_DISTRIBUTIONS和AP_INVOICES,代表Invoice的分录和Invoice分配的分录)
    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.Invoice付款时产生的数据如下表
    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. Invoice核销预付款Invoice的金额
    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; --InvoiceID
    --6. 预付款Invoice核销Invoice的金额
    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 --预付款InvoiceID
       AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN
           ('PREPAYMENT'
           ,'CREDIT'
           ,'DEBIT');
    --Invoice是否被验证的脚本


    /*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) --Invoice是否已验证,已验证的状态为APPROVED
          ,AI.*
      FROM AP_INVOICES_ALL AI
     WHERE AI.INVOICE_NUM IN ('2011110888'
                             ,'20111202001');




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


    --日记账分录
    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;


    --Invoice匹配接收时与接收的关联关系


    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'


    --应付总账分录追溯到Invoice
    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'
  • 相关阅读:
    iostream迭代器操作"txt文本文件"无法写入的思考
    Qt 相对路径 绝对路径
    "lambda"和“bind”的初步思考
    "partition"和“stable_partition”的思考
    "accumulate"的思考
    顺序容器“inset”的思考
    C++重载函数 const形参 引用指针 const_cast
    C++ 可变参数的函数
    JDK8流式处理常用例子
    JDK8时间新API
  • 原文地址:https://www.cnblogs.com/wanghang/p/6299064.html
Copyright © 2020-2023  润新知