Oracle AR - 收款界面上的非数据库项金额查询SQL

最新推荐文章于 2021-04-03 19:07:10 发布

写代码也要符合基本法

最新推荐文章于 2021-04-03 19:07:10 发布

阅读量729

点赞数

分类专栏： Oracle学习笔记文章标签： Oracle 收款金额

本文链接：https://blog.csdn.net/liouzch/article/details/86477683

版权

Oracle学习笔记专栏收录该内容

48 篇文章 5 订阅

订阅专栏

SELECT SUM(decode(ra.status
                 ,'APP'
                 ,decode(ra.confirmed_flag
                        ,'N'
                        ,0
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0))
                 ,'ACTIVITY'
                 ,decode(ra.applied_payment_schedule_id
                        ,-2
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,-3
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,-5
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,-6
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,-8
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,-9
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,decode(ra.receivables_trx_id
                               ,-16
                               ,nvl(nvl(ra.amount_applied_from
                                       ,ra.amount_applied)
                                   ,0)))
                 ,0)) applied_amount --已核销金额
      ,SUM(decode(ra.status
                 ,'ACTIVITY'
                 ,decode(applied_payment_schedule_id
                        ,-3
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,0)
                 ,0)) write_off_amount
      ,SUM(decode(ra.status
                 ,'ACTIVITY'
                 ,decode(applied_payment_schedule_id
                        ,-5
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,0)
                 ,0)) chargeback_amount
      ,SUM(decode(ra.status, 'UNAPP', nvl(ra.amount_applied, 0), 0)) unapplied_amount --未核销金额
      ,SUM(decode(ra.status, 'ACC', nvl(ra.amount_applied, 0), 0)) on_account_amount --记账金额
      ,SUM(decode(ra.status
                 ,'OTHER ACC'
                 ,decode(applied_payment_schedule_id
                        ,-4
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,0)
                 ,0)) claim_amount --现金索赔金额
      ,SUM(decode(ra.status
                 ,'OTHER ACC'
                 ,decode(applied_payment_schedule_id
                        ,-7
                        ,nvl(nvl(ra.amount_applied_from, ra.amount_applied)
                            ,0)
                        ,0)
                 ,0)) prepayment_amount --预付款金额
      ,SUM(decode(ra.status, 'UNID', nvl(ra.amount_applied, 0), 0)) unidentified_amount --未标识金额
      ,SUM(decode(ra.status, 'APP', nvl(ra.earned_discount_taken, 0), 0)) discounts_earned --应得折扣金额
      ,SUM(decode(ra.status, 'APP', nvl(ra.unearned_discount_taken, 0), 0)) discounts_unearned --未得折扣金额
      ,SUM(decode(ra.status
                 ,'APP'
                 ,nvl(ra.acctd_amount_applied_from -
                      ra.acctd_amount_applied_to
                     ,0)
                 ,'ACTIVITY'
                 ,nvl(ra.acctd_amount_applied_from -
                      ra.acctd_amount_applied_to
                     ,0)
                 ,0)) tot_exchange_gain_loss --汇率损益
  FROM ar_receivable_applications_all ra
 WHERE ra.cash_receipt_id = &p_cr_id;

注：SQL来源ARP_PROCESS_RCTS.POST_QUERY_LOGIC。