modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/Issue28591UpdatePSD_data.xsql
author Atul Gaware <atul.gaware@openbravo.com>
Tue, 20 Jan 2015 14:57:35 +0530
changeset 25690 91af7013421e
child 25691 a088e7fae5e8
permissions -rw-r--r--
Fixes Issue 28591:Reversing a payment is creating wrong invoice payment plan
details

Problem is Schedule detail was being updated by payment amount now it is taken
care to update using individual schedule detail amount while creating reverse
payment schedule and payment schedule details. Modulescript to fix errorneous data
consists of update invoice paid, outstanding amt, update payment schedule received
and outstanding amount, update payment credit generated info if in case required.
atul@25690
     1
<?xml version="1.0" encoding="UTF-8" ?>
atul@25690
     2
<!--
atul@25690
     3
 *************************************************************************
atul@25690
     4
 * The contents of this file are subject to the Openbravo  Public  License
atul@25690
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
atul@25690
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
atul@25690
     7
 * file except in compliance with the License. You  may  obtain  a copy of
atul@25690
     8
 * the License at http://www.openbravo.com/legal/license.html
atul@25690
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
atul@25690
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
atul@25690
    11
 * License for the specific  language  governing  rights  and  limitations
atul@25690
    12
 * under the License.
atul@25690
    13
 * The Original Code is Openbravo ERP.
atul@25690
    14
 * The Initial Developer of the Original Code is Openbravo SLU
atul@25690
    15
 * All portions are Copyright (C) 2015 Openbravo SLU
atul@25690
    16
 * All Rights Reserved.
atul@25690
    17
 * Contributor(s):  ______________________________________.
atul@25690
    18
 *************************************************************************
atul@25690
    19
-->
atul@25690
    20
<SqlClass name="Issue28591UpdatePSDData" package="org.openbravo.advpaymentmngt.modulescript">
atul@25690
    21
   <SqlClassComment></SqlClassComment>
atul@25690
    22
   <SqlMethod name="select" type="preparedStatement" return="multiple">
atul@25690
    23
    <SqlMethodComment></SqlMethodComment>
atul@25690
    24
    <Sql>
atul@25690
    25
      <![CDATA[
atul@25690
    26
        SELECT '' as fin_payment_scheduledetail_id, '' as outstandingamt, '' as wrongamt, 
atul@25690
    27
        '' as fin_payment_schedule_id, '' as c_invoice_id, '' as c_currency_id, '' as bp_currency_id,
atul@25690
    28
        '' as fin_payment_id, '' as fin_payment_detail_id, '' as isreceipt, '' as c_bpartner_id
atul@25690
    29
        FROM DUAL
atul@25690
    30
      ]]>
atul@25690
    31
    </Sql>
atul@25690
    32
   </SqlMethod>
atul@25690
    33
   <SqlMethod name="selectPSD" type="preparedStatement" return="multiple">
atul@25690
    34
    <SqlMethodComment></SqlMethodComment>
atul@25690
    35
    <Sql>
atul@25690
    36
      <![CDATA[
atul@25690
    37
      select ps.outstandingamt as outstandingamt, max(psd.fin_payment_scheduledetail_id) as fin_payment_scheduledetail_id
atul@25690
    38
      from fin_payment_scheduledetail psd
atul@25690
    39
      LEFT JOIN fin_payment_schedule ps ON ps.fin_payment_schedule_id = COALESCE(psd.fin_payment_schedule_invoice,psd.fin_payment_schedule_order)
atul@25690
    40
      where psd.fin_payment_detail_id is null and ps.outstandingamt > 0
atul@25690
    41
      group by ps.outstandingamt, ps.fin_payment_schedule_id
atul@25690
    42
      having sum(psd.amount) <> ps.outstandingamt
atul@25690
    43
      ]]>
atul@25690
    44
    </Sql>
atul@25690
    45
   </SqlMethod>
atul@25690
    46
   <SqlMethod name="selectWrongPSD" type="preparedStatement" return="multiple">
atul@25690
    47
    <SqlMethodComment></SqlMethodComment>
atul@25690
    48
    <Sql>
atul@25690
    49
      <![CDATA[
atul@25690
    50
      select i.c_invoice_id, fp.c_currency_id, bp.c_bpartner_id, bp.bp_currency_id, fp.fin_payment_id, ps.fin_payment_schedule_id,
atul@25690
    51
      psd.fin_payment_scheduledetail_id, pd.fin_payment_detail_id, ps.outstandingamt as wrongamt, to_char(fp.isreceipt) as isreceipt
atul@25690
    52
      from c_invoice i
atul@25690
    53
      left join c_bpartner bp on bp.c_bpartner_id = i.c_bpartner_id
atul@25690
    54
      left join fin_payment_schedule ps on ps.c_invoice_id = i.c_invoice_id
atul@25690
    55
      left join fin_payment_scheduledetail psd on psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id
atul@25690
    56
      left join fin_payment_detail pd on psd.fin_payment_detail_id = pd.fin_payment_detail_id
atul@25690
    57
      left join fin_payment fp on fp.fin_payment_id = pd.fin_payment_id
atul@25690
    58
      where i.outstandingamt < 0
atul@25690
    59
      and i.outstandingamt = ps.outstandingamt
atul@25690
    60
      and ps.paidamt = psd.amount
atul@25690
    61
      ]]>
atul@25690
    62
   </Sql>
atul@25690
    63
  </SqlMethod>
atul@25690
    64
  <SqlMethod name="updateWrongInvoiceAmt" type="preparedStatement" return="rowcount">
atul@25690
    65
    <SqlMethodComment></SqlMethodComment>
atul@25690
    66
    <Sql>
atul@25690
    67
      <![CDATA[
atul@25690
    68
        update c_invoice set ispaid= case when grandtotal=totalpaid+to_number(?) then 'Y' else 'N' end,  totalpaid=totalpaid+to_number(?),
atul@25690
    69
         LastCalculatedOnDate=LastCalculatedOnDate+to_number(?), outstandingamt=outstandingamt-to_number(?) ,
atul@25690
    70
         DaysTillDue=substract_days(to_timestamp(now()),(select min (duedate) from FIN_Payment_Schedule where  c_invoice_id=?)),
atul@25690
    71
         Percentageoverdue=(select round((sum(case when fp.Paymentdate > ps.duedate then psd.amount else 0 end )*100)/GrandTotal,2)
atul@25690
    72
         from  c_invoice ci, fin_payment_scheduledetail psd , fin_payment_schedule ps,  fin_payment fp,  FIN_Payment_Detail pd
atul@25690
    73
         where ps.c_invoice_id = ci.c_invoice_id
atul@25690
    74
         AND psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id
atul@25690
    75
         and  pd.fin_payment_detail_id=psd.fin_payment_detail_id
atul@25690
    76
         and fp.fin_payment_id=pd.fin_payment_id
atul@25690
    77
         and ci.c_invoice_id=?
atul@25690
    78
         group by  ci.c_invoice_id, ci.grandtotal, ci.totalpaid, ci.LastCalculatedOnDate, ci.DaysTillDue, ci.Percentageoverdue),
atul@25690
    79
         updatedby='0', updated=now()
atul@25690
    80
         where c_invoice_id=?
atul@25690
    81
      ]]>
atul@25690
    82
    </Sql>
atul@25690
    83
    <Parameter name="Amount"/>
atul@25690
    84
    <Parameter name="Amount"/>
atul@25690
    85
    <Parameter name="Amount"/>
atul@25690
    86
    <Parameter name="Amount"/>
atul@25690
    87
    <Parameter name="cInvoiceId"/>
atul@25690
    88
    <Parameter name="cInvoiceId"/>
atul@25690
    89
    <Parameter name="cInvoiceId"/>    
atul@25690
    90
  </SqlMethod>  
atul@25690
    91
  <SqlMethod name="updateWrongPSAmt" type="preparedStatement" return="rowcount">
atul@25690
    92
    <SqlMethodComment></SqlMethodComment>
atul@25690
    93
    <Sql>
atul@25690
    94
      <![CDATA[
atul@25690
    95
        UPDATE FIN_PAYMENT_SCHEDULE SET PAIDAMT = PAIDAMT + TO_NUMBER(?), 
atul@25690
    96
        OUTSTANDINGAMT= OUTSTANDINGAMT - TO_NUMBER(?),
atul@25690
    97
        updatedby='0', updated=now()
atul@25690
    98
        WHERE FIN_PAYMENT_SCHEDULE_ID = ?
atul@25690
    99
      ]]>
atul@25690
   100
    </Sql>
atul@25690
   101
    <Parameter name="outStandingAmount"/>
atul@25690
   102
    <Parameter name="outStandingAmount"/>
atul@25690
   103
    <Parameter name="finPaymentScheduleId"/>
atul@25690
   104
  </SqlMethod>
atul@25690
   105
  <SqlMethod name="updateWrongPSDAmt" type="preparedStatement" return="rowcount">
atul@25690
   106
    <SqlMethodComment></SqlMethodComment>
atul@25690
   107
    <Sql>
atul@25690
   108
      <![CDATA[
atul@25690
   109
        UPDATE FIN_PAYMENT_SCHEDULEDETAIL SET AMOUNT=AMOUNT + TO_NUMBER(?),
atul@25690
   110
        updatedby='0', updated=now()
atul@25690
   111
        WHERE FIN_PAYMENT_SCHEDULEDETAIL_ID = ?
atul@25690
   112
      ]]>
atul@25690
   113
    </Sql>
atul@25690
   114
    <Parameter name="Amount"/>
atul@25690
   115
    <Parameter name="finPaymentScheduledetailId"/>
atul@25690
   116
  </SqlMethod>
atul@25690
   117
  <SqlMethod name="selectFinPaymentDetailId" type="preparedStatement" return="String">
atul@25690
   118
    <SqlMethodComment></SqlMethodComment>
atul@25690
   119
    <Sql>
atul@25690
   120
      <![CDATA[
atul@25690
   121
        SELECT get_uuid() as fin_payment_detail_id
atul@25690
   122
        FROM DUAL        
atul@25690
   123
      ]]>
atul@25690
   124
    </Sql>
atul@25690
   125
  </SqlMethod>
atul@25690
   126
  <SqlMethod name="createCredit" type="preparedStatement" return="rowcount">
atul@25690
   127
    <SqlMethodComment></SqlMethodComment>
atul@25690
   128
    <Sql>
atul@25690
   129
      <![CDATA[
atul@25690
   130
        INSERT INTO fin_payment_detail (
atul@25690
   131
          fin_payment_detail_id, ad_client_id, ad_org_id, isactive,
atul@25690
   132
          createdby, created, updatedby, updated, fin_payment_id, 
atul@25690
   133
          amount, refund, isprepayment
atul@25690
   134
        ) VALUES (
atul@25690
   135
          ?, '0', '0', 'Y',
atul@25690
   136
          '0', NOW(), '0', NOW(), ?,
atul@25690
   137
          TO_NUMBER(?)*(-1), 'N', 'Y'      
atul@25690
   138
        )
atul@25690
   139
      ]]>
atul@25690
   140
    </Sql>
atul@25690
   141
    <Parameter name="finPaymentDetailId"/>
atul@25690
   142
    <Parameter name="finPaymentId"/>
atul@25690
   143
    <Parameter name="Amount"/>
atul@25690
   144
  </SqlMethod>
atul@25690
   145
  <SqlMethod name="createCreditScheduledetail" type="preparedStatement" return="rowcount">
atul@25690
   146
    <SqlMethodComment></SqlMethodComment>
atul@25690
   147
    <Sql>
atul@25690
   148
      <![CDATA[
atul@25690
   149
        INSERT INTO fin_payment_scheduledetail (
atul@25690
   150
          fin_payment_scheduledetail_id, ad_client_id, ad_org_id, isactive,
atul@25690
   151
          createdby, created, updatedby, updated, fin_payment_detail_id, 
atul@25690
   152
          amount
atul@25690
   153
        ) VALUES (
atul@25690
   154
          get_uuid(), '0', '0', 'Y',
atul@25690
   155
          '0', NOW(), '0', NOW(), ?,
atul@25690
   156
          TO_NUMBER(?)*(-1)      
atul@25690
   157
        )
atul@25690
   158
      ]]>
atul@25690
   159
    </Sql>
atul@25690
   160
    <Parameter name="finPaymentDetailId"/>
atul@25690
   161
    <Parameter name="Amount"/>
atul@25690
   162
  </SqlMethod>
atul@25690
   163
  <SqlMethod name="updateCreditGenerated" type="preparedStatement" return="rowcount">
atul@25690
   164
    <SqlMethodComment></SqlMethodComment>
atul@25690
   165
    <Sql>
atul@25690
   166
      <![CDATA[
atul@25690
   167
        UPDATE FIN_PAYMENT SET generated_credit=generated_credit+(TO_NUMBER(?)*-1),
atul@25690
   168
        updatedby='0', updated=now()
atul@25690
   169
        WHERE FIN_PAYMENT_ID = ?
atul@25690
   170
      ]]>
atul@25690
   171
    </Sql>
atul@25690
   172
    <Parameter name="Amount"/>
atul@25690
   173
    <Parameter name="finPaymentId"/>
atul@25690
   174
  </SqlMethod>
atul@25690
   175
  <SqlMethod name="updateWrongPDAmt" type="preparedStatement" return="rowcount">
atul@25690
   176
    <SqlMethodComment></SqlMethodComment>
atul@25690
   177
    <Sql>
atul@25690
   178
      <![CDATA[
atul@25690
   179
        UPDATE FIN_PAYMENT_DETAIL SET AMOUNT=AMOUNT+TO_NUMBER(?),
atul@25690
   180
        updatedby='0', updated=now()
atul@25690
   181
        WHERE FIN_PAYMENT_DETAIL_ID = ?
atul@25690
   182
      ]]>
atul@25690
   183
    </Sql>
atul@25690
   184
    <Parameter name="Amount"/>
atul@25690
   185
    <Parameter name="finPaymentDetailId"/>
atul@25690
   186
  </SqlMethod>
atul@25690
   187
  <SqlMethod name="updatePSDAmount" type="preparedStatement" return="rowcount">
atul@25690
   188
    <SqlMethodComment></SqlMethodComment>
atul@25690
   189
    <Sql>
atul@25690
   190
      <![CDATA[
atul@25690
   191
        UPDATE FIN_PAYMENT_SCHEDULEDETAIL SET AMOUNT=TO_NUMBER(?),
atul@25690
   192
        updatedby='0', updated=now()
atul@25690
   193
        WHERE FIN_PAYMENT_SCHEDULEDETAIL_ID = ?
atul@25690
   194
      ]]>
atul@25690
   195
    </Sql>
atul@25690
   196
    <Parameter name="outStandingAmount"/>
atul@25690
   197
    <Parameter name="finPaymentScheduledetailId"/>
atul@25690
   198
  </SqlMethod>
atul@25690
   199
  <SqlMethod name="updateWrongPSD" type="preparedStatement" return="boolean">
atul@25690
   200
    <SqlMethodComment></SqlMethodComment>
atul@25690
   201
    <Sql>
atul@25690
   202
      <![CDATA[
atul@25690
   203
        SELECT count(*) as exist
atul@25690
   204
        FROM DUAL
atul@25690
   205
        WHERE EXISTS (SELECT 1 FROM ad_preference
atul@25690
   206
                      WHERE attribute = 'Issue28591updateWrongPSD')
atul@25690
   207
      ]]>
atul@25690
   208
    </Sql>
atul@25690
   209
  </SqlMethod>
atul@25690
   210
  <SqlMethod name="createPreference" type="preparedStatement" return="rowcount">
atul@25690
   211
    <SqlMethodComment></SqlMethodComment>
atul@25690
   212
    <Sql>
atul@25690
   213
      <![CDATA[
atul@25690
   214
        INSERT INTO ad_preference (
atul@25690
   215
          ad_preference_id, ad_client_id, ad_org_id, isactive,
atul@25690
   216
          createdby, created, updatedby, updated,
atul@25690
   217
          attribute
atul@25690
   218
        ) VALUES (
atul@25690
   219
          get_uuid(), '0', '0', 'Y',
atul@25690
   220
          '0', NOW(), '0', NOW(),
atul@25690
   221
          'Issue28591updateWrongPSD'
atul@25690
   222
        )
atul@25690
   223
      ]]>
atul@25690
   224
    </Sql>
atul@25690
   225
  </SqlMethod>
atul@25690
   226
 </SqlClass>