src/org/openbravo/erpCommon/info/DebtPayment_data.xsql
changeset 712 aa0c40278c5a
parent 423 ecf368072c48
child 1605 8a0fe0193bef
equal deleted inserted replaced
711:4e8209a9d749 712:aa0c40278c5a
    26    <SqlClassComment></SqlClassComment>
    26    <SqlClassComment></SqlClassComment>
    27    <SqlMethod name="select" type="preparedStatement" return="multiple">
    27    <SqlMethod name="select" type="preparedStatement" return="multiple">
    28       <SqlMethodComment></SqlMethodComment>
    28       <SqlMethodComment></SqlMethodComment>
    29       <Sql>
    29       <Sql>
    30       <![CDATA[
    30       <![CDATA[
    31         SELECT DP.C_DEBT_PAYMENT_ID, Ad_Column_Identifier(TO_CHAR('C_Debt_Payment'), TO_CHAR(dp.C_DEBT_PAYMENT_ID), TO_CHAR(?)) AS NAME, B.NAME AS BPARTNER, I.DOCUMENTNO AS INVOICE, DP.DATEPLANNED,
    31       	SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (
    32         O.DOCUMENTNO AS ORDERNO, DP.AMOUNT, DP.WRITEOFFAMT, C.ISO_CODE AS CURRENCY,
    32 	        SELECT DP.C_DEBT_PAYMENT_ID, B.NAME AS BPARTNER, I.DOCUMENTNO AS INVOICE, DP.DATEPLANNED,
    33         S1.DOCUMENTNO AS DEBTCANCEL, S2.DOCUMENTNO AS DEBTGENERATE, COALESCE(RLT.NAME, RL.NAME) AS PAYMENTRULE
    33 	        O.DOCUMENTNO AS ORDERNO, DP.AMOUNT, DP.WRITEOFFAMT, C.ISO_CODE AS CURRENCY,
    34         FROM C_DEBT_PAYMENT DP left join C_BPARTNER B on DP.C_BPARTNER_ID = B.C_BPARTNER_ID
    34 	        S1.DOCUMENTNO AS DEBTCANCEL, S2.DOCUMENTNO AS DEBTGENERATE, COALESCE(RLT.NAME, RL.NAME) AS PAYMENTRULE,
    35                                left join C_INVOICE I  on DP.C_INVOICE_ID = I.C_INVOICE_ID
    35 	        DP.C_DEBT_PAYMENT_ID || '#' || Ad_Column_Identifier(TO_CHAR('C_Debt_Payment'), TO_CHAR(dp.C_DEBT_PAYMENT_ID), TO_CHAR(?)) AS ROWKEY
    36                                left join C_SETTLEMENT S1 on DP.C_SETTLEMENT_CANCEL_ID = S1.C_SETTLEMENT_ID
    36 	        FROM C_DEBT_PAYMENT DP left join C_BPARTNER B on DP.C_BPARTNER_ID = B.C_BPARTNER_ID
    37                                left join C_ORDER    O on DP.C_ORDER_ID = O.C_ORDER_ID
    37 	                               left join C_INVOICE I  on DP.C_INVOICE_ID = I.C_INVOICE_ID
    38                                left join C_SETTLEMENT S2 on DP.C_SETTLEMENT_GENERATE_ID = S2.C_SETTLEMENT_ID,
    38 	                               left join C_SETTLEMENT S1 on DP.C_SETTLEMENT_CANCEL_ID = S1.C_SETTLEMENT_ID
    39              AD_REF_LIST RL    left join AD_REF_LIST_TRL RLT on RL.AD_REF_LIST_ID = RLT.AD_REF_LIST_ID
    39 	                               left join C_ORDER    O on DP.C_ORDER_ID = O.C_ORDER_ID
    40                                                             AND RLT.AD_LANGUAGE  = ?,
    40 	                               left join C_SETTLEMENT S2 on DP.C_SETTLEMENT_GENERATE_ID = S2.C_SETTLEMENT_ID,
    41             C_CURRENCY C
    41 	             AD_REF_LIST RL    left join AD_REF_LIST_TRL RLT on RL.AD_REF_LIST_ID = RLT.AD_REF_LIST_ID
    42         WHERE DP.C_CURRENCY_ID = C.C_CURRENCY_ID 
    42 	                                                            AND RLT.AD_LANGUAGE  = ?,
    43         AND DP.PAYMENTRULE = RL.VALUE
    43 	            C_CURRENCY C
    44         AND RL.AD_REFERENCE_ID = 195       
    44 	        WHERE DP.C_CURRENCY_ID = C.C_CURRENCY_ID 
    45         AND DP.AD_Client_ID IN ('1') 
    45 	        AND DP.PAYMENTRULE = RL.VALUE
    46         AND DP.AD_Org_ID IN ('1') 
    46 	        AND RL.AD_REFERENCE_ID = 195       
    47         AND DP.IsActive='Y'
    47 	        AND DP.AD_Client_ID IN ('1') 
    48         AND DP.IsValid='Y'
    48 	        AND DP.AD_Org_ID IN ('1') 
       
    49 	        AND DP.IsActive='Y'
       
    50 	        AND DP.IsValid='Y'
       
    51 	        ORDER BY DP.C_DEBT_PAYMENT_ID
       
    52 	        ) A ) B
       
    53 		WHERE 1=1
    49         ]]>
    54         ]]>
    50         </Sql>
    55         </Sql>
    51         <Field name="position" value="count"/>
    56         <Field name="position" value="count"/>
       
    57         <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />        
    52         <Parameter name="language"/>
    58         <Parameter name="language"/>
    53         <Parameter name="language"/>
    59         <Parameter name="language"/>
    54         <Parameter name="adUserClient" type="replace" optional="true" after="DP.AD_Client_ID IN (" text="'1'"/>
    60         <Parameter name="adUserClient" type="replace" optional="true" after="DP.AD_Client_ID IN (" text="'1'"/>
    55         <Parameter name="adUserOrg" type="replace" optional="true" after="DP.AD_Org_ID IN (" text="'1'"/>
    61         <Parameter name="adUserOrg" type="replace" optional="true" after="DP.AD_Org_ID IN (" text="'1'"/>
    56         <Parameter name="businesPartner" optional="true" after="AND DP.IsValid='Y'"><![CDATA[ AND b.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
    62         <Parameter name="businesPartner" optional="true" after="AND DP.IsValid='Y'"><![CDATA[ AND b.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
    57         <Parameter name="dateFrom" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.Dateplanned >= to_date(?) ]]></Parameter>
    63         <Parameter name="dateFrom" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.Dateplanned >= TO_DATE(?) ]]></Parameter>
    58         <Parameter name="dateTo" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.Dateplanned < to_date(?) ]]></Parameter>
    64         <Parameter name="dateTo" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.Dateplanned < TO_DATE(?) ]]></Parameter>
    59         <Parameter name="AmountFrom" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.amount >= ? ]]></Parameter>
    65         <Parameter name="AmountFrom" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.amount >= TO_NUMBER(?) ]]></Parameter>
    60         <Parameter name="AmountTo" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.amount <= ? ]]></Parameter>
    66         <Parameter name="AmountTo" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.amount <= TO_NUMBER(?) ]]></Parameter>
    61         <Parameter name="paymentRule" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.paymentrule = ? ]]></Parameter>
    67         <Parameter name="paymentRule" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.paymentrule = ? ]]></Parameter>
    62         <Parameter name="isPaid" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.ispaid=? ]]></Parameter>
    68         <Parameter name="isPaid" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.ispaid = ? ]]></Parameter>
    63         <Parameter name="isReceipt" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.isreceipt=? ]]></Parameter>
    69         <Parameter name="isReceipt" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.isreceipt = ? ]]></Parameter>
    64         <Parameter name="isPending" optional="true" after="DP.IsValid='Y'" text="AND C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) = ?"/>
    70         <Parameter name="isPending" optional="true" after="DP.IsValid='Y'" text="AND C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) = ?"/>
    65         <Parameter name="invoiceId" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND i.documentno like ? ]]></Parameter>
    71         <Parameter name="invoiceId" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND i.documentno like ? ]]></Parameter>
    66         <Parameter name="orderId" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND o.documentno like ? ]]></Parameter>
    72         <Parameter name="orderId" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND o.documentno like ? ]]></Parameter>
       
    73         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="DP.C_DEBT_PAYMENT_ID" />
       
    74         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
    75         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
    67    </SqlMethod>
    76    </SqlMethod>
    68         
    77         
    69    <SqlMethod name="set" type="constant" return="multiple">
    78    <SqlMethod name="set" type="constant" return="multiple">
    70       <SqlMethodComment></SqlMethodComment>
    79       <SqlMethodComment></SqlMethodComment>
    71       <Sql></Sql>
    80       <Sql></Sql>