供应商应付暂估sql

我公司的业务中,供应商的应付暂估大概由三部分组成:1标准订单在接收和退货的时候会产生应付暂估;2一揽子协议在领料出库的时候会产生应付暂估(在接收的时候会在事务处理里产生应付暂估,但是不传总账);3发票匹配的时候会产生应付暂估.

导致总账应付暂估科目余额与供应商应付暂估余额不相等的原因:1一揽子协议在退货的时候会冲暂估应付,但是我公司现存状况无法追溯到该笔冲销对应的供应商;2用户在总账里手工做应付暂估的凭证;3应付模块部分税的科目是应付暂估.

下面时供应商应付暂估的余额sql:
SELECT A.ORG_ID, A.VENDOR_ID, SUM(A.JE) AMOUNT
        FROM (SELECT 'ST' SOURCE_CODE,--标准发票
                     RAE.ORG_ID ORG_ID,
                     RT.VENDOR_ID VENDOR_ID,
                     TO_CHAR(RAE.TRANSACTION_DATE, 'yyyy-mm-dd') GL_DATE,
                     CASE
                       WHEN GCC1.SEGMENT3 = '220206' AND
                            GCC2.SEGMENT3 = '220206' THEN
                        0
                       WHEN GCC1.SEGMENT3 = '220206' AND
                            GCC2.SEGMENT3 <> '220206' THEN
                        -ROUND(RAE.TRANSACTION_QUANTITY * RAE.UNIT_PRICE, 2)
                       WHEN GCC1.SEGMENT3 <> '220206' AND
                            GCC2.SEGMENT3 = '220206' THEN
                        ROUND(RAE.TRANSACTION_QUANTITY * RAE.UNIT_PRICE, 2)
                       ELSE
                        0
                     END JE
                FROM RCV_ACCOUNTING_EVENTS RAE,
                     GL_CODE_COMBINATIONS  GCC1,
                     GL_CODE_COMBINATIONS  GCC2,
                     RCV_TRANSACTIONS      RT
               WHERE GCC1.CODE_COMBINATION_ID = RAE.DEBIT_ACCOUNT_ID
                 AND GCC2.CODE_COMBINATION_ID = RAE.CREDIT_ACCOUNT_ID
                 AND RT.TRANSACTION_ID = RAE.RCV_TRANSACTION_ID
                 AND (GCC1.SEGMENT3 = '220206' OR GCC2.SEGMENT3 = '220206')
                 AND RAE.ORG_ID = 84
                 AND NVL(RT.CONSIGNED_FLAG, 'N') = 'N' --排除一揽子协议
                 AND RT.VENDOR_ID = NVL(null, RT.VENDOR_ID)
                 AND TO_CHAR(RAE.TRANSACTION_DATE, 'yyyy-mm-dd') <=
                     '2012-06-30'
              UNION ALL
              SELECT 'BL' SOURCE_CODE,--一揽子协议
                     PHA.ORG_ID ORG_ID,
                     PHA.VENDOR_ID VENDOR_ID,
                     TO_CHAR(MTA.TRANSACTION_DATE, 'yyyy-mm-dd') GL_DATE,
                     -MTA.BASE_TRANSACTION_VALUE JE
                FROM PO_HEADERS_ALL           PHA,
                     MTL_TRANSACTION_ACCOUNTS MTA,
                     GL_CODE_COMBINATIONS     GCC
               WHERE PHA.PO_HEADER_ID = MTA.TRANSACTION_SOURCE_ID
                 AND MTA.TRANSACTION_SOURCE_TYPE_ID = 1
                 AND GCC.CODE_COMBINATION_ID = MTA.REFERENCE_ACCOUNT
                 AND GCC.SEGMENT3 = '220206'
                 AND PHA.ORG_ID = 84
                 AND PHA.VENDOR_ID = NVL(null, PHA.VENDOR_ID)
                 AND TO_CHAR(MTA.TRANSACTION_DATE, 'yyyy-mm-dd') <=
                     '2012-06-30'
              UNION ALL
              SELECT 'IN' SOURCE_CODE,--发票匹配
                     AIDA.ORG_ID ORG_ID,
                     AIA.VENDOR_ID VENDOR_ID,
                     TO_CHAR(AIDA.ACCOUNTING_DATE, 'yyyy-mm-dd') GL_DATE,
                     -AIDA.AMOUNT JE
                FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
                     AP_INVOICES_ALL              AIA,
                     GL_CODE_COMBINATIONS         GCC
               WHERE AIA.INVOICE_ID = AIDA.INVOICE_ID
                 AND AIDA.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                 AND GCC.SEGMENT3 = '220206'
                 AND AIDA.PO_DISTRIBUTION_ID IS NOT NULL
                 AND NVL(AIDA.POSTED_FLAG, 'N') = 'Y' --是否过账
                 AND AIDA.LINE_TYPE_LOOKUP_CODE IN ('ITEM', 'ACCRUAL') --排除税
                 AND AIDA.ORG_ID = 84
                 AND AIA.VENDOR_ID = NVL(null, AIA.VENDOR_ID)
                 AND TO_CHAR(AIDA.ACCOUNTING_DATE, 'yyyy-mm-dd') <=
                     '2012-06-30') A
       GROUP BY A.ORG_ID, A.VENDOR_ID;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25103190/viewspace-735589/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25103190/viewspace-735589/