src/org/openbravo/erpCommon/ad_reports/AgingDao_data.xsql
changeset 37511 4118743db5cd
parent 37508 89b0b6a30daf
equal deleted inserted replaced
37510:76b5ebe416de 37511:4118743db5cd
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    11  * License for the specific  language  governing  rights  and  limitations
    11  * License for the specific  language  governing  rights  and  limitations
    12  * under the License.
    12  * under the License.
    13  * The Original Code is Openbravo ERP.
    13  * The Original Code is Openbravo ERP.
    14  * The Initial Developer of the Original Code is Openbravo SLU
    14  * The Initial Developer of the Original Code is Openbravo SLU
    15  * All portions are Copyright (C) 2016-2017 Openbravo SLU
    15  * All portions are Copyright (C) 2016-2020 Openbravo SLU
    16  * All Rights Reserved.
    16  * All Rights Reserved.
    17  * Contributor(s):  ______________________________________.
    17  * Contributor(s):  ______________________________________.
    18  ************************************************************************
    18  ************************************************************************
    19 -->
    19 -->
    20 
    20 
   137                         ELSE fp2.documentNo
   137                         ELSE fp2.documentNo
   138                         END                             AS docNo
   138                         END                             AS docNo
   139                 FROM   fin_payment_scheduledetail fpsd
   139                 FROM   fin_payment_scheduledetail fpsd
   140                        INNER JOIN fin_payment_detail fpd ON fpsd.fin_payment_detail_id = fpd.fin_payment_detail_id
   140                        INNER JOIN fin_payment_detail fpd ON fpsd.fin_payment_detail_id = fpd.fin_payment_detail_id
   141                        INNER JOIN fin_payment fp2 ON fpd.fin_payment_id = fp2.fin_payment_id
   141                        INNER JOIN fin_payment fp2 ON fpd.fin_payment_id = fp2.fin_payment_id
       
   142                        LEFT JOIN fin_payment_schedule fpsi ON (fpsd.fin_payment_schedule_invoice = fpsi.fin_payment_schedule_id)
       
   143                        LEFT JOIN c_invoice i ON (fpsi.c_invoice_id = i.c_invoice_id)
   142                 WHERE  (fp2.AD_Org_ID in ('1'))
   144                 WHERE  (fp2.AD_Org_ID in ('1'))
   143                        AND (fp2.status IN ('1'))
   145                        AND (fp2.status IN ('1'))
   144                        AND Trunc(fp2.paymentdate) <= TO_DATE(?)
   146                        AND Trunc(fp2.paymentdate) <= TO_DATE(?)
   145                        AND fp2.isreceipt = ?
   147                        AND fp2.isreceipt = ?
   146                        AND 2=2
   148                        AND 2=2
   147                        AND fpsd.isactive = 'Y'
   149                        AND fpsd.isactive = 'Y'
   148                        AND fpsd.iscanceled = 'N'
   150                        AND fpsd.iscanceled = 'N'
   149                        AND fpsd.fin_payment_schedule_invoice IS NULL
   151                        AND (fpsd.fin_payment_schedule_invoice IS NULL
       
   152                             OR i.DATEINVOICED >= TO_DATE(?))
   150                        AND fpsd.fin_payment_schedule_order IS NOT NULL
   153                        AND fpsd.fin_payment_schedule_order IS NOT NULL
   151                 ) P 
   154                 ) P 
   152                 LEFT JOIN 
   155                 LEFT JOIN 
   153                 (SELECT SUM(fpc.amount) AS USED_CREDIT,
   156                 (SELECT SUM(fpc.amount) AS USED_CREDIT,
   154                          fpc.fin_payment_id_used  AS paymentUsedId
   157                          fpc.fin_payment_id_used  AS paymentUsedId
   184     <Parameter name="paidStatus" type="replace" optional="true" after="AND (fp2.status IN (" text="'1'"/>
   187     <Parameter name="paidStatus" type="replace" optional="true" after="AND (fp2.status IN (" text="'1'"/>
   185     <Parameter name="asOfDate"/>
   188     <Parameter name="asOfDate"/>
   186     <Parameter name="recOrPay"/>
   189     <Parameter name="recOrPay"/>
   187     <Parameter name="cbPartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND fp2.C_BPARTNER_ID IN]]></Parameter>
   190     <Parameter name="cbPartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND fp2.C_BPARTNER_ID IN]]></Parameter>
   188     <Parameter name="excludeReverseds" type="none" optional="true" after="AND 2=2" text=" AND (fp2.fin_rev_payment_id is null AND NOT EXISTS (SELECT 1 FROM fin_payment WHERE fin_rev_payment_id = fp2.fin_payment_id))"/>
   191     <Parameter name="excludeReverseds" type="none" optional="true" after="AND 2=2" text=" AND (fp2.fin_rev_payment_id is null AND NOT EXISTS (SELECT 1 FROM fin_payment WHERE fin_rev_payment_id = fp2.fin_payment_id))"/>
       
   192     <Parameter name="asOfDate"/>
   189     <Parameter name="asOfDate"/>
   193     <Parameter name="asOfDate"/>
   190     <Parameter name="paidStatus" type="replace" optional="true" after="OR ( fp1.status IN (" text="'1'"/>
   194     <Parameter name="paidStatus" type="replace" optional="true" after="OR ( fp1.status IN (" text="'1'"/>
   191     <Parameter name="pgLimit" type="argument" optional="true" after=") B"><![CDATA[LIMIT ]]></Parameter>
   195     <Parameter name="pgLimit" type="argument" optional="true" after=") B"><![CDATA[LIMIT ]]></Parameter>
   192     <Parameter name="oraLimit1" type="argument" optional="true" after=") B"><![CDATA[WHERE ROWNUM <= ]]></Parameter>
   196     <Parameter name="oraLimit1" type="argument" optional="true" after=") B"><![CDATA[WHERE ROWNUM <= ]]></Parameter>
   193     </SqlMethod>
   197     </SqlMethod>