Oracle EBS 应收账龄取数SQL

最新推荐文章于 2022-11-07 17:40:38 发布

Jenrry30

最新推荐文章于 2022-11-07 17:40:38 发布

阅读量2.4k

点赞数

分类专栏： AR

本文链接：https://blog.csdn.net/JenrryMath/article/details/84336280

版权

取值来源与总账

SELECT decode(gcc.segment5, '0', gcc.segment6, gcc.segment5) cus_code,
       gcc.segment3,
       trunc(nvl(to_date(gjl.attribute4, 'RRRR/MM/DD HH24:MI:SS'),
                 gjh.default_effective_date)) act_date,
       trunc(to_date('2019-01-31 23:59:59', 'RRRR/MM/DD HH24:MI:SS')) -
       trunc(nvl(to_date(gjl.attribute4, 'RRRR/MM/DD HH24:MI:SS'),
                 gjh.default_effective_date)) + 1 days,
       SUM(decode(sign(to_date('2019-01-01 00:00:00',
                               'RRRR/MM/DD HH24:MI:SS') -
                       trunc(gjl.effective_date)),
                  1,
                  (nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0)),
                  0)) year_begin,
       SUM(decode(sign(to_date('2019-01-01 00:00:00',
                               'RRRR/MM/DD HH24:MI:SS') -
                       trunc(gjl.effective_date)),
                  1,
                  (nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0)),
                  0)) period_begin,
       
       SUM(CASE
             WHEN to_date('2019-01-01 00:00:00', 'RRRR/MM/DD HH24:MI:SS') <=
                  trunc(gjl.effective_date) AND
                  trunc(gjl.effective_date) <=
                  to_date('2019-01-31 23:59:59', 'RRRR/MM/DD HH24:MI:SS') THEN
              nvl(gjl.entered_dr, 0)
             ELSE
              0
           END) period_dr,
       
       SUM(CASE
             WHEN to_date('2019-01-01 00:00:00', 'RRRR/MM/DD HH24:MI:SS') <=
                  trunc(gjl.effective_date) AND
                  trunc(gjl.effective_date) <=
                  to_date('2019-01-31 23:59:59', 'RRRR/MM/DD HH24:MI:SS') THEN
              nvl(gjl.entered_cr, 0)
             ELSE
              0
           END) period_cr,
       
       SUM(decode(sign(to_date('2019-01-01 00:00:00',
                               'RRRR/MM/DD HH24:MI:SS') -
                       trunc(gjl.effective_date)),

最低0.47元/天解锁文章