ITPub博客

首页 > Linux操作系统 > Linux操作系统 > AR

AR

原创 Linux操作系统 作者:judy_dong 时间:2011-06-17 12:09:48 0 删除 编辑
ar.ra_customer_trx_all rcta where trx_number='10009' 发票号   (rcta.customer_trx_id=apsa.customer_trx_id)
  ar.ar_payment_schedules_all apsa,    应收款表      (apsa.PAYMENT_SCHEDULE_ID= araa.APPLIED_PAYMENT_SCHEDULE_ID)
  ar.ar_receivable_applications_all araa,    AMOUNT_APPLIED ---已核销金额 ( araa.CASH_RECEIPT_ID=acra.CASH_RECEIPT_ID)
  ar.ar_cash_receipts_all acra, receipt_number ='00028722'  收款编号
  ar.ar_cash_receipt_history_all acrha,  round(nvl(acrha.amount,0),2) net_amt,  --收款金额
  ar.ar_receipt_methods arm,
  ar.ra_customers rc,
  ar.RA_ADDRESSES_ALL raa,
  ar.ra_site_uses_all rsua,
 select * from ar_distributions_all    分 录表
  apps.ra_cust_trx_types_all
这是开发按月查询客户余额报表的步骤和SQL脚本,希望对你有用
(1) 收款总额
SELECT SUM (DECODE (acra.currency_code, 'CNY', acra.amount, acra.amount * acra.exchange_rate) )
            FROM   ar_cash_receipts_all acra,
                   ar_cash_receipt_history_all acrha,
                   ra_site_uses_all rsua
            WHERE acra.pay_from_customer = 4481
                  AND acra.customer_site_use_id = rsua.site_use_id
                  AND RSUA.SITE_USE_ID = acra.CUSTOMER_SITE_USE_ID
                  AND rsua.site_use_code = 'BILL_TO'
                  AND acrha.gl_date <= to_date('20030228','yyyymmdd')
                  AND ((
                        ( acra.receipt_method_id = 1042  ----票据类收款的id
                                 AND acrha.status NOT IN( 'REMITTED','CLEARED','RISK_ELIMINATED') ----根据用户何时确定作为收款为准,我这儿应收票据一旦确认就算收款)
                                AND nvl(acrha.current_record_flag,'Y') = 'Y')
                          )
                      OR (acra.receipt_method_id <> 1042                                                                          AND nvl(acrha.current_record_flag,'N') = 'Y'))
                  AND EXISTS (SELECT 'A'
                              FROM ar_cash_receipt_history_all T
                              WHERE T.CASH_RECEIPT_ID = acrha.cash_receipt_id
                              AND T.current_record_flag = 'Y'
                              AND T.status != 'REVERSED')
                  AND acra.org_id = 1
                  AND acra.cash_receipt_id = acrha.cash_receipt_id
                  AND UPPER (acrha.status) != 'REVERSED'
(2)开单总额
SELECT SUM (DECODE (rcta.invoice_currency_code, 'CNY', rctla.extended_amount, rctla.extended_amount * rcta.exchange_rate))
            FROM   ra_customer_trx_all rcta
                 , ra_customer_trx_lines_all rctla
                 , ra_cust_trx_types_all rctta
                 , ra_cust_trx_line_gl_dist_all rctlgda
                 , ra_site_uses_all rsua
            WHERE rcta.bill_to_customer_id = 4481
                  AND rcta.bill_to_site_use_id = rsua.site_use_id
                  AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID -------- added by Devy on 2002/08/27
                  AND rcta.customer_trx_id = rctla.customer_trx_id
                  AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
                  AND UPPER (rctta.TYPE) IN ('INV', 'CM', 'DM')
                  AND UPPER (rctta.post_to_gl) = 'Y'
                  AND UPPER (rctta.accounting_affect_flag) = 'Y'
                  AND rctlgda.gl_date <= to_date('20030331','yyyymmdd')
                  AND rcta.org_id = 1
                  AND UPPER (rctlgda.account_class) = 'REC'
                  AND UPPER (rctlgda.latest_rec_flag) = 'Y'
                  AND rctlgda.customer_trx_id = rcta.customer_trx_id
                  AND UPPER (rcta.complete_flag) = DECODE (
                                                       UPPER ('n')
                                                     , 'Y', UPPER (rcta.complete_flag)
                                                     , 'N', 'Y'
                                                   )
                  AND rsua.site_use_code = 'BILL_TO' 
(3)开票已核销额
SELECT SUM (DECODE(rcta.invoice_currency_code,'CNY',
                       DECODE (
                           UPPER (UPPER (rctta.TYPE) ) || UPPER (araa.application_type)
                         , 'CMCM'
                         , -1 * NVL (araa.amount_applied, 0)
                         , NVL (araa.amount_applied, 0)
                       ),
                       DECODE (
                           UPPER (UPPER (rctta.TYPE) ) || UPPER (araa.application_type)
                         , 'CMCM'
                         , -1 * NVL (araa.amount_applied, 0)
                         , NVL (araa.amount_applied, 0)
                       ) * rcta.exchange_rate
                       )                      
                   )
            FROM   ra_customer_trx_all rcta
                 , ra_cust_trx_types_all rctta
                 , ra_cust_trx_line_gl_dist_all rctlgda
                 , ra_site_uses_all rsua
                 , ar_receivable_applications_all araa
            WHERE rcta.bill_to_customer_id = 4481
                  AND rcta.bill_to_site_use_id = rsua.site_use_id
                  AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID -------- added by Devy on 2002/08/27
                  AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
                  AND UPPER (rctta.TYPE) IN ('INV', 'CM', 'DM')
                  AND UPPER (rctta.post_to_gl) = 'Y'
                  AND UPPER (rctta.accounting_affect_flag) = 'Y'
                  AND rctlgda.gl_date <=  to_date('20030331','yyyymmdd')
                  AND rcta.org_id = 1
                  AND UPPER (rctlgda.account_class) = 'REC'
                  AND UPPER (rctlgda.latest_rec_flag) = 'Y'
                  AND rctlgda.customer_trx_id = rcta.customer_trx_id
                  AND UPPER (rcta.complete_flag) = DECODE (
                                                       UPPER ('n')
                                                     , 'Y', UPPER (rcta.complete_flag)
                                                     , 'N', 'Y'
                                                   )
                  AND rsua.site_use_code = 'BILL_TO' -- 2002/08/22  
                  AND (araa.applied_customer_trx_id = rcta.customer_trx_id
                       OR araa.customer_trx_id = rcta.customer_trx_id
                      )
                  AND araa.display = 'Y'
                  AND araa.gl_date <=  to_date('20030331','yyyymmdd')
(4)收款已核销额
SELECT acra.cash_receipt_id,acra.receipt_number,decode(acra.currency_code,'CNY',
                                          NVL (araa.amount_applied*nvl(araa.trans_to_receipt_rate,1), 0),
                                                            NVL (araa.amount_applied, 0)*acra.exchange_rate*nvl(araa.trans_to_receipt_rate,1))
                                                    
            FROM   ar_cash_receipts_all acra
                 , ar_cash_receipt_history_all acrha
                 , ra_site_uses_all rsua
                 , ar_receivable_applications_all araa
            WHERE acra.pay_from_customer = 4481
                  AND acra.customer_site_use_id = rsua.site_use_id
                  AND RSUA.SITE_USE_ID = acra.CUSTOMER_SITE_USE_ID -------- added by Devy on 2002/08/27
                  AND rsua.site_use_code = 'BILL_TO' -- added by Devy on 2002/07/30
                  AND acrha.gl_date <= to_date('20030331','yyyymmdd')
                  AND ((
                        ( acra.receipt_method_id = 1042
                                 AND        acrha.status NOT IN( 'REMITTED','CLEARED','RISK_ELIMINATED')
                                                                                                        AND nvl(acrha.current_record_flag,'Y') = 'Y'
                                      )
                                     )
                      OR (acra.receipt_method_id <> 1042
                                                                                                 AND nvl(acrha.current_record_flag,'N') = 'Y'))
                  AND EXISTS (SELECT 'A'
                              FROM ar_cash_receipt_history_all T
                              WHERE T.CASH_RECEIPT_ID = acrha.cash_receipt_id
                              AND T.current_record_flag = 'Y'
                              AND T.status != 'REVERSED')
                  AND acra.org_id = 1
                  AND acra.cash_receipt_id = acrha.cash_receipt_id
                  AND UPPER (acrha.status) != 'REVERSED'
                  AND araa.cash_receipt_id = acra.cash_receipt_id
                  AND araa.display = 'Y'
                  AND araa.gl_date <= to_date('20030331','yyyymmdd') -- NO SPECIFY PERIOD_NAME
                  AND araa.applied_customer_trx_id <> -1
(5)汇兑损益
create or replace view AR_EXCHANGE_GAIN_LOSS_V as
select  app.cash_receipt_id,sum(APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO,APP.ACCTD_AMOUNT_APPLIED_FROM)) EXCHANGE_GAIN_LOSS
FROM AR_RECEIVABLE_APPLICATIONS APP
where app.display = 'Y'
group by  app.cash_receipt_id
(6)当期开单总额,当期收款总额
在计算总额时限定rctlgda.gl_date的起始日期
(7)当期开单核销额,当期收款核销额
在计算总额时限定araa.gl_date的起始日期
(8)公式:期末余额 =(开单总额-已核销额)-(收款总额-已核销额)
期初余额 = 期末余额 - (当期开单总额-当期开单核销额)+ (当期收款总额 - 当期收款核销额)

具体报表开发的话我想按照这个思路后应该不难了吧,数据的准确性已经在我这边的客户那核对过,但是由于各个环境情况不一样,可能存在未考虑到的情况。如发现的话,请告知,谢谢

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

上一篇: 没有了~
下一篇: ar 常用表
请登录后发表评论 登录
全部评论

注册时间:2011-06-17

  • 博文量
    15
  • 访问量
    18802