ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > AR应收帐款凭证

AR应收帐款凭证

原创 Linux操作系统 作者：KawaiPinky 时间：2010-11-10 16:45:55 0 删除 编辑
select USER_NAME,
       CURRENCY_CODE,
       EXCHANGE_RATE,
       ACCOUNT_NUMBER,
       DESCRPTION,
       CODE_ACCOUNTS,
       SOURCEDESCRIPTION,
       sum(ENTERED_DR) AS ENTERED_DR,
       sum(ENTERED_CR) AS ENTERED_CR,
       sum(ACCOUNTED_DR) AS ACCOUNTED_DR,
       sum(ACCOUNTED_CR) AS ACCOUNTED_CR,
       to_char(:STARTDATE, 'yyyy-mm-dd')  as STARTDATE,
       to_char(:ENDDATE, 'yyyy-mm-dd') as ENDDATE
  from (
        SELECT RAC_BILL_PARTY.PARTY_NAME AS USER_NAME,
                CT.INVOICE_CURRENCY_CODE AS CURRENCY_CODE,
                CT.EXCHANGE_RATE,
                RAC_BILL.ACCOUNT_NUMBER,
                CT.COMMENTS AS DESCRPTION,
                SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                       INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
                       INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)) AS CODE_ACCOUNTS,
                SUBSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                      'GL#',
                                                      GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                      'ALL',
                                                      GCC_ORI.CONCATENATED_SEGMENTS),
                       INSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                            'GL#',
                                                            GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                            'ALL',
                                                            GCC_ORI.CONCATENATED_SEGMENTS),
                             '-',
                             1,
                             1) + 1,
                       INSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                            'GL#',
                                                            GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                            'ALL',
                                                            GCC_ORI.CONCATENATED_SEGMENTS),
                             '-',
                             1,
                             2) - 1) SOURCEDESCRIPTION,
               - sum(XAL.ENTERED_DR) AS ENTERED_DR,
                -sum(XAL.ENTERED_CR) AS ENTERED_CR,
               - sum(XAL.ACCOUNTED_DR) AS ACCOUNTED_DR,
               - sum(XAL.ACCOUNTED_CR) AS ACCOUNTED_CR,
               to_char(:STARTDATE, 'yyyy-mm-dd')  as STARTDATE,
               to_char(:ENDDATE, 'yyyy-mm-dd') as ENDDATE
          FROM XLA_AE_LINES                 xal,
                XLA_AE_HEADERS               xah,
                XLA.XLA_TRANSACTION_ENTITIES xte,
                GL_CODE_COMBINATIONS_KFV     GCC_ORI,
                RA_CUST_TRX_LINE_GL_DIST_ALL GD,
                HZ_CUST_ACCOUNTS             RAC_BILL,
                HZ_PARTIES                   RAC_BILL_PARTY,
                RA_CUSTOMER_TRX              CT,
                FND_USER                     FU
         WHERE xal.application_id = xah.application_id
           AND xal.ae_header_id = xah.ae_header_id
           AND XTE.ENTITY_ID = XAH.ENTITY_ID
           AND XAL.CODE_COMBINATION_ID = GCC_ORI.CODE_COMBINATION_ID
           AND xah.application_id = 222
           AND xte.source_id_int_1 = CT.CUSTOMER_TRX_ID
           AND CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
           AND CT.BILL_TO_CUSTOMER_ID = RAC_BILL.CUST_ACCOUNT_ID
           AND RAC_BILL.PARTY_ID = RAC_BILL_PARTY.PARTY_ID
           AND xte.entity_code = 'TRANSACTIONS'
           AND 'REC' = GD.ACCOUNT_CLASS
           AND 'Y' = GD.LATEST_REC_FLAG
           AND CT.CREATED_BY = FU.USER_ID
           AND XAH.EVENT_TYPE_CODE='CM_CREATE'
           AND XAH.description not like '%SH-保证金退回 %'
           AND nvl(CT.DOC_SEQUENCE_VALUE, 1) =
               nvl(:sequence_value, nvl(CT.DOC_SEQUENCE_VALUE, 1))
         &p1
           AND to_char(GD.GL_DATE, 'yyyy-mm-dd') BETWEEN
               to_char(:STARTDATE, 'yyyy-mm-dd') AND
               to_char(:ENDDATE, 'yyyy-mm-dd')
         group by RAC_BILL_PARTY.PARTY_NAME,
                   CT.INVOICE_CURRENCY_CODE,
                   CT.EXCHANGE_RATE,
                   RAC_BILL.ACCOUNT_NUMBER,
                   CT.COMMENTS,
                   SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                          INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
                          INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)),
                   SUBSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                         'GL#',
                                                         GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                         'ALL',
                                                         GCC_ORI.CONCATENATED_SEGMENTS),
                          INSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                               'GL#',
                                                               GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                               'ALL',
                                                               GCC_ORI.CONCATENATED_SEGMENTS),
                                '-',
                                1,
                                1) + 1,
                          INSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                               'GL#',
                                                               GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                               'ALL',
                                                               GCC_ORI.CONCATENATED_SEGMENTS),
                                '-',
                                1,
                                2) - 1)      
        UNION ALL
       
         SELECT RAC_BILL_PARTY.PARTY_NAME AS USER_NAME,
                CT.INVOICE_CURRENCY_CODE AS CURRENCY_CODE,
                CT.EXCHANGE_RATE,
                RAC_BILL.ACCOUNT_NUMBER,
                CT.COMMENTS AS DESCRPTION,
                SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                       INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
                       INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)) AS CODE_ACCOUNTS,
                SUBSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                      'GL#',
                                                      GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                      'ALL',
                                                      GCC_ORI.CONCATENATED_SEGMENTS),
                       INSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                            'GL#',
                                                            GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                            'ALL',
                                                            GCC_ORI.CONCATENATED_SEGMENTS),
                             '-',
                             1,
                             1) + 1,
                       INSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                            'GL#',
                                                            GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                            'ALL',
                                                            GCC_ORI.CONCATENATED_SEGMENTS),
                             '-',
                             1,
                             2) - 1) SOURCEDESCRIPTION,               
                sum(XAL.ENTERED_DR) AS ENTERED_DR,
                sum(XAL.ENTERED_CR) AS ENTERED_CR,
                sum(XAL.ACCOUNTED_DR) AS ACCOUNTED_DR,
                sum(XAL.ACCOUNTED_CR) AS ACCOUNTED_CR,
                to_char(:STARTDATE, 'yyyy-mm-dd')  as STARTDATE,
                to_char(:ENDDATE, 'yyyy-mm-dd') as ENDDATE
          FROM XLA_AE_LINES                 xal,
                XLA_AE_HEADERS               xah,
                XLA.XLA_TRANSACTION_ENTITIES xte,
                GL_CODE_COMBINATIONS_KFV     GCC_ORI,
                RA_CUST_TRX_LINE_GL_DIST_ALL GD,
                HZ_CUST_ACCOUNTS             RAC_BILL,
                HZ_PARTIES                   RAC_BILL_PARTY,
                RA_CUSTOMER_TRX              CT,
                FND_USER                     FU
         WHERE xal.application_id = xah.application_id
           AND xal.ae_header_id = xah.ae_header_id
           AND XTE.ENTITY_ID = XAH.ENTITY_ID
           AND XAL.CODE_COMBINATION_ID = GCC_ORI.CODE_COMBINATION_ID
           AND xah.application_id = 222
           AND xte.source_id_int_1 = CT.CUSTOMER_TRX_ID
           AND CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
           AND CT.BILL_TO_CUSTOMER_ID = RAC_BILL.CUST_ACCOUNT_ID
           AND RAC_BILL.PARTY_ID = RAC_BILL_PARTY.PARTY_ID
           AND xte.entity_code = 'TRANSACTIONS'
           AND 'REC' = GD.ACCOUNT_CLASS
           AND 'Y' = GD.LATEST_REC_FLAG
           AND CT.CREATED_BY = FU.USER_ID
           AND XAH.EVENT_TYPE_CODE='INV_CREATE'
           AND XAH.description not like '%SH-保证金退回 %'
          AND nvl(CT.DOC_SEQUENCE_VALUE, 1) =
               nvl(:sequence_value, nvl(CT.DOC_SEQUENCE_VALUE, 1))
         &p1
           AND to_char(GD.GL_DATE, 'yyyy-mm-dd') BETWEEN
               to_char(:STARTDATE, 'yyyy-mm-dd') AND
               to_char(:ENDDATE, 'yyyy-mm-dd')
         group by RAC_BILL_PARTY.PARTY_NAME,
                   CT.INVOICE_CURRENCY_CODE,
                   CT.EXCHANGE_RATE,
                   RAC_BILL.ACCOUNT_NUMBER,
                   CT.COMMENTS,
                   SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                          INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
                          INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)),
                   SUBSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                         'GL#',
                                                         GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                         'ALL',
                                                         GCC_ORI.CONCATENATED_SEGMENTS),
                          INSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                               'GL#',
                                                               GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                               'ALL',
                                                               GCC_ORI.CONCATENATED_SEGMENTS),
                                '-',
                                1,
                                1) + 1,
                          INSTR(FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                                               'GL#',
                                                               GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                                               'ALL',
                                                               GCC_ORI.CONCATENATED_SEGMENTS),
                                '-',
                                1,
                                2) - 1)      
        )
     
 group by USER_NAME,
          CURRENCY_CODE,
          EXCHANGE_RATE,
          ACCOUNT_NUMBER,
          DESCRPTION,
          CODE_ACCOUNTS,
          SOURCEDESCRIPTION

 
 
application_id  的值 .从ap来的数据为200， 从ar来的数据就是222
 
 
EVENT_TYPE_CODE 

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

0
0
上一篇： 没有了~
下一篇： 账户余额查询SQL（分类帐）
请登录后发表评论 登录
全部评论
KawaiPinky

注册时间：2010-11-10

  • 博文量
    32
  • 访问量
    53030

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593