atm_update.lwm 10.5 KB
Newer Older
1 2 3 4 5
<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
liyuan.chen's avatar
liyuan.chen committed
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153
                Select to_char(v.calendar_date, 'yyyy/mm/dd') calendar_date,
                        v.named_day,
                        v.purchase_amount,
                        v.down_payment,
                        v.lease_amount,
                           nvl(v.purchase_amount, 0) + nvl(down_payment, 0) +
                           nvl(lease_amount, 0) total_amount,
                        outflow_amount
                    From (Select t.calendar_date,
                              decode(to_char(t.calendar_date, 'd'),
                                     1,
                                     '日',
                                     2,
                                     '月',
                                     3,
                                     '火',
                                     4,
                                     '水',
                                     5,
                                     '木',
                                     6,
                                     '金',
                                     7,
                                     '土') named_day,
                              (Select Sum(ccc.due_amount - nvl(ccc.received_amount, 0))
                               From con_contract_cashflow ccc
                               Where ccc.cf_item = 8
                                     And ccc.due_date = t.calendar_date
                                     And ccc.cf_status = 'RELEASE'
                                     And Exists
                                     (Select 1
                                      From con_contract ct
                                      Where ct.contract_status In
                                            ('INCEPT', 'PENDING', 'ETING', 'REPURING')
                                            And ct.contract_id = ccc.contract_id
                                            And ct.data_class = 'NORMAL')) purchase_amount, --留购金
                              (Select Sum(ccc.due_amount - nvl(ccc.received_amount, 0))
                               From con_contract_cashflow ccc
                               Where ccc.cf_item = 2
                                     And ccc.due_date = t.calendar_date
                                     And ccc.cf_status = 'RELEASE'
                                     And Exists
                                     (Select 1
                                      From con_contract ct
                                      Where ct.contract_status In
                                            ('INCEPT', 'PENDING', 'ETING', 'REPURING')
                                            And ct.contract_id = ccc.contract_id
                                            And ct.data_class = 'NORMAL')) down_payment, --首付款
                              (Select Sum(ccc.due_amount)
                               From con_contract_cashflow ccc
                               Where ccc.cf_item In (1, 200, 250, 11)
                                     And ccc.due_date = t.calendar_date
                                     And ccc.cf_status = 'RELEASE'
                                     And Exists
                                     (Select 1
                                      From con_contract ct
                                      Where ct.contract_status In
                                            ('INCEPT', 'PENDING', 'ETING', 'REPURING')
                                            And ct.contract_id = ccc.contract_id
                                            And ct.data_class = 'NORMAL')) lease_amount, --租金
                              (Case
                               When to_char(Sysdate, 'dd') <= 20 And
                                    to_char(t.calendar_date, 'yyyymmdd') =
                                    to_char(Sysdate, 'yyyymm') ||
                                    decode(to_char(to_date(to_char(Sysdate, 'yyyymm') || 20,
                                                           'yyyymmdd'),
                                                   'd'),
                                           1,
                                           18,
                                           7,
                                           19,
                                           20) Then
                                   --当月的二十号统计数据
                                   NVL((Select Sum(nvl(c.lease_item_amount, 0))
                                        From con_contract c
                                        Where c.contract_status <> 'CANCEL'
                                              And c.data_class = 'NORMAL'
                                              And to_char(add_months(c.lease_start_date, 1),
                                                          'yyyymm') = to_char(Sysdate, 'yyyymm')
                                              And nvl(c.payment_deduction, 'NO_DEDUCTION') =
                                                  'NO_DEDUCTION'
                                              And nvl(c.secondary_lease, 'NO') = 'NO'),
                                       0) +
                                   NVL((Select Sum(nvl(c.lease_item_amount, 0) -
                                                   nvl(c.down_payment, 0))
                                        From con_contract c
                                        Where c.contract_status <> 'CANCEL'
                                              And c.data_class = 'NORMAL'
                                              And to_char(add_months(c.lease_start_date, 1),
                                                          'yyyymm') = to_char(Sysdate, 'yyyymm')
                                              And nvl(c.payment_deduction, 'NO_DEDUCTION') <>
                                                  'NO_DEDUCTION'
                                              And nvl(c.secondary_lease, 'NO') = 'NO'),
                                       0)
                               When to_char(Sysdate, 'dd') > 20 And
                                    to_char(t.calendar_date, 'yyyymmdd') =
                                    decode(to_char(to_date((to_char(add_months(Sysdate, 1),
                                                                    'yyyymm') || 20),
                                                           'yyyymmdd'),
                                                   'd'),
                                           1,
                                           to_char(add_months(Sysdate, 1), 'yyyymm') || 18,
                                           7,
                                           to_char(add_months(Sysdate, 1), 'yyyymm') || 19,
                                           to_char(add_months(Sysdate, 1), 'yyyymm') || 20) Then
                                   NVL((Select Sum(nvl(c.lease_item_amount, 0))
                                        From con_contract c
                                        Where c.contract_status <> 'CANCEL'
                                              And c.data_class = 'NORMAL'
                                              And to_char(c.lease_start_date, 'yyyymm') =
                                                  to_char(Sysdate, 'yyyymm')
                                              And nvl(c.payment_deduction, 'NO_DEDUCTION') =
                                                  'NO_DEDUCTION'
                                              And nvl(c.secondary_lease, 'NO') = 'NO'),
                                       0) + NVL((Select Sum(nvl(c.lease_item_amount, 0) -
                                                            nvl(c.down_payment, 0))
                                                 From con_contract c
                                                 Where c.contract_status <> 'CANCEL'
                                                       And c.data_class = 'NORMAL'
                                                       And to_char(c.lease_start_date, 'yyyymm') =
                                                           to_char(Sysdate, 'yyyymm')
                                                       And nvl(c.payment_deduction, 'NO_DEDUCTION') <>
                                                           'NO_DEDUCTION'
                                                       And nvl(c.secondary_lease, 'NO') = 'NO'),
                                                0)
                               Else
                                   Null
                               End) outflow_amount
                          From (Select s.calendar_date
                                From fnd_calendar_details s
                                Where trunc(s.calendar_date) >= trunc(Sysdate)
                                      And trunc(s.calendar_date) <= Any
                                          (Select trunc(due_date)
                                           From con_contract_cashflow ccc
                                           Where ccc.cf_status = 'RELEASE'
                                                 And Exists
                                                 (Select 1
                                                  From con_contract t
                                                  Where t.contract_status In
                                                        ('INCEPT',
                                                         'PENDING',
                                                         'ETING',
                                                         'REPURING')
                                                        And t.contract_id = ccc.contract_id
                                                        And t.data_class = 'NORMAL'))
                                Order By s.calendar_date) t) v

                ]]></bm:query-sql>
154 155 156 157 158 159 160 161 162 163 164 165 166 167
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
            	begin
            		contract_item_query_pkg.send_excel_mail(p_file_name => ${@file_name},
                                                            p_file_path => ${@file_path} ,
                                                            p_file_size => ${@file_size},
                                                            p_user_id   => -1
                                                            );
            	end;
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
</bm:model>