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