ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 预收账款 sql script

预收账款 sql script

原创 Linux操作系统 作者:smilefish 时间:2019-04-29 11:18:05 0 删除 编辑

以期间2009-08为例(分客户汇总)

begin
  fnd_client_info.set_org_context(82);
end;

------

select
ku.customer_name 客户,
qc.qcye 期初余额,
bq.bq_cr 本期借方,
bq.bq_dr 本期贷方,
qm.qmye  期末余额
from
(SELECT rc.customer_name  customer_name
  FROM ar_cash_receipts acr, ar_receivable_applications ara,ra_customers rc
WHERE 1 = 1
   and ara.CASH_RECEIPT_ID(+) = acr.CASH_RECEIPT_ID
   AND to_char(ara.GL_DATE,'yyyy-mm-dd')<'2009-07-26'
   and ara.STATUS = 'UNAPP' and acr.PAY_FROM_CUSTOMER=rc.customer_id
having sum(ara.AMOUNT_APPLIED) <> 0
group by rc.customer_name
union
SELECT rc.customer_name
  FROM ar_cash_receipts acr, ar_receivable_applications ara,ra_customers rc
WHERE 1 = 1
   and ara.CASH_RECEIPT_ID(+) = acr.CASH_RECEIPT_ID
   AND to_char(ara.GL_DATE,'yyyy-mm-dd')<'2009-08-26'
   and ara.STATUS = 'UNAPP' and acr.PAY_FROM_CUSTOMER=rc.customer_id
having sum(ara.AMOUNT_APPLIED) <> 0
group by rc.customer_name
) ku
left join
(SELECT rc.customer_name ,
       sum(ara.ACCTD_AMOUNT_APPLIED_FROM) qcye
  FROM ar_cash_receipts acr, ar_receivable_applications ara,ra_customers rc
WHERE 1 = 1
   and ara.CASH_RECEIPT_ID(+) = acr.CASH_RECEIPT_ID
   AND to_char(ara.GL_DATE,'yyyy-mm-dd')<'2009-07-26'
   and ara.STATUS = 'UNAPP' and acr.PAY_FROM_CUSTOMER=rc.customer_id
having sum(ara.AMOUNT_APPLIED) <> 0
group by rc.customer_name
) qc
on  ku.customer_name=qc.customer_name
left join
(
select
aag.third_party_name customer_name,
sum(nvl(aag.accounted_cr,0)) bq_cr,
sum(nvl(aag.accounted_dr,0)) bq_dr
from
GL_JE_JOURNAL_LINES_V gj ,
ar_ael_gl_rec_all_v  aag
where
gj.set_of_books_id=1 and gj.LINE_CODE_COMBINATION_ID in (1466,1028,7947)
and gj.je_header_id=aag.je_header_id
and gj.LINE_JE_LINE_NUM=aag.je_line_num
and aag.acct_line_type='UNAPP'
and gj.PERIOD_NAME='08-2009'
group by
aag.third_party_name
) bq
on   ku.customer_name=bq.customer_name 
left join
(SELECT rc.customer_name ,
       sum(ara.ACCTD_AMOUNT_APPLIED_FROM) qmye
  FROM ar_cash_receipts acr, ar_receivable_applications ara,ra_customers rc
WHERE 1 = 1
   and ara.CASH_RECEIPT_ID(+) = acr.CASH_RECEIPT_ID
   AND to_char(ara.GL_DATE,'yyyy-mm-dd')<'2009-08-26'
   and ara.STATUS = 'UNAPP' and acr.PAY_FROM_CUSTOMER=rc.customer_id
having sum(ara.AMOUNT_APPLIED) <> 0
group by rc.customer_name
) qm
on  ku.customer_name=qm.customer_name

        

 

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

请登录后发表评论 登录
全部评论

注册时间:2004-12-31

  • 博文量
    23
  • 访问量
    16345