src/org/openbravo/erpCommon/ad_actionButton/CreateFile_data.xsql
changeset 423 ecf368072c48
parent 381 c94c8e05919d
child 1605 8a0fe0193bef
equal deleted inserted replaced
422:e00336c36741 423:ecf368072c48
    28     <SqlMethodComment></SqlMethodComment>
    28     <SqlMethodComment></SqlMethodComment>
    29     <Sql>
    29     <Sql>
    30     <![CDATA[
    30     <![CDATA[
    31 				select B.NAME AS BANK,b.codebank, b.codebranch, b.DIGITCONTROL as digitcontrol1, ba.DIGITCONTROL as digitcontrol2, 
    31 				select B.NAME AS BANK,b.codebank, b.codebranch, b.DIGITCONTROL as digitcontrol1, ba.DIGITCONTROL as digitcontrol2, 
    32       ba.CODEACCOUNT, 
    32       ba.CODEACCOUNT, 
    33 			LPAD(B.CODEBANK,4,0)||LPAD(B.CODEBRANCH,4,0)||B.DIGITCONTROL||ba.DIGITCONTROL||LPAD(ba.CODEACCOUNT,10,0) AS N_CUENTA,
    33 			LPAD(B.CODEBANK,4,TO_CHAR('0'))||LPAD(B.CODEBRANCH,4,TO_CHAR('0'))||B.DIGITCONTROL||ba.DIGITCONTROL||LPAD(ba.CODEACCOUNT,10,TO_CHAR('0')) AS N_CUENTA,
    34       lpad(rpad(c.NAME,40,' '),46,' ') AS ENTIDAD, rpad(c.NAME,40,' ') AS ENTIDAD19, oi.TAXID AS NIF, TO_CHAR(r.Duedate  ,'DD')||TO_CHAR(r.Duedate,'MM')||TO_CHAR(r.Duedate,'YY')AS DATEPLANNED, TO_CHAR(now(), 'DD')||TO_CHAR(now(), 'MM')||TO_CHAR(now(), 'YY') AS HOY, 
    34       lpad(rpad(c.NAME,40,' '),46,TO_CHAR(' ')) AS ENTIDAD, rpad(c.NAME,40,' ') AS ENTIDAD19, oi.TAXID AS NIF, TO_CHAR(r.Duedate  ,'DD')||TO_CHAR(r.Duedate,'MM')||TO_CHAR(r.Duedate,'YY')AS DATEPLANNED, TO_CHAR(now(), 'DD')||TO_CHAR(now(), 'MM')||TO_CHAR(now(), 'YY') AS HOY, 
    35 			LPAD(RPAD(LPAD(B.CODEBANK,4,0)||LPAD(B.CODEBRANCH,4,0),74,' '),94,' ') AS ENTOFI, LPAD(B.INE_NUMBER,61,' ') AS INE, 
    35 			LPAD(RPAD(LPAD(B.CODEBANK,4,TO_CHAR('0'))||LPAD(B.CODEBRANCH,4,TO_CHAR('0')),74,' '),94,TO_CHAR(' ')) AS ENTOFI, LPAD(B.INE_NUMBER,61,TO_CHAR(' ')) AS INE, 
    36 			'' AS TERCERO,
    36 			'' AS TERCERO,
    37       '' AS FECHA_FACTURA, '' AS FECHA_VENCIMIENTO, '' AS PAYAMT, '' AS CREDITCARDNUMBER, '' AS N_FACTURA, '' AS N_FACTURA19, '' AS CIUDAD, '' AS DIRECCION,
    37       '' AS FECHA_FACTURA, '' AS FECHA_VENCIMIENTO, '' AS PAYAMT, '' AS CREDITCARDNUMBER, '' AS N_FACTURA, '' AS N_FACTURA19, '' AS CIUDAD, '' AS DIRECCION,
    38       '' AS CONCEPTO, '' AS PLAZA, '' AS LOCALIDAD, '' AS POSTAL, '' AS CODIGO_PROVINCIA, '' AS LINEAS, '' AS ORDENANTES, '' AS HUECO, '' AS CONTENT
    38       '' AS CONCEPTO, '' AS PLAZA, '' AS LOCALIDAD, '' AS POSTAL, '' AS CODIGO_PROVINCIA, '' AS LINEAS, '' AS ORDENANTES, '' AS HUECO, '' AS CONTENT
    39       from c_remittance r, ad_client c, 
    39       from c_remittance r, ad_client c, 
    40 					 c_bankAccount		ba,
    40 					 c_bankAccount		ba,
    42 					 ad_orgInfo				oi
    42 					 ad_orgInfo				oi
    43 		 where r.ad_client_id = c.ad_client_id
    43 		 where r.ad_client_id = c.ad_client_id
    44 		 	 and   r.AD_ORG_ID = oi.ad_org_id
    44 		 	 and   r.AD_ORG_ID = oi.ad_org_id
    45 			 and r.C_BANKACCOUNT_ID = ba.c_bankAccount_id
    45 			 and r.C_BANKACCOUNT_ID = ba.c_bankAccount_id
    46 			 and b.c_bank_id = ba.c_bank_Id
    46 			 and b.c_bank_id = ba.c_bank_Id
    47        and r.C_Remittance_ID = ?
    47        and r.C_Remittance_ID = TO_NUMBER(?)
    48     ]]>
    48     ]]>
    49     </Sql>
    49     </Sql>
    50     <Parameter name="cRemittanceId"/>
    50     <Parameter name="cRemittanceId"/>
    51     </SqlMethod>
    51     </SqlMethod>
    52   <SqlMethod name="selectLineas" type="preparedStatement" return="multiple">
    52   <SqlMethod name="selectLineas" type="preparedStatement" return="multiple">
    53     <SqlMethodComment></SqlMethodComment>
    53     <SqlMethodComment></SqlMethodComment>
    54     <Sql>
    54     <Sql>
    55     <![CDATA[
    55     <![CDATA[
    56       SELECT RPAD(COALESCE(BP.NAME2,BP.NAME),40,' ') AS TERCERO,RPAD(TO_CHAR(COALESCE(C_INVOICE.DATEINVOICED,DP.DATEPLANNED), 'DDMMYY'),14) AS FECHA_FACTURA, 
    56       SELECT RPAD(COALESCE(BP.NAME2,BP.NAME),40,' ') AS TERCERO,RPAD(TO_CHAR(COALESCE(C_INVOICE.DATEINVOICED,DP.DATEPLANNED), 'DDMMYY'),14) AS FECHA_FACTURA, 
    57       TO_CHAR(TO_CHAR(DP.DATEPLANNED, 'DDMMYY')) AS FECHA_VENCIMIENTO,LPAD(ROUND(DP.AMOUNT*100,0),10,0) AS PAYAMT, 
    57       TO_CHAR(TO_CHAR(DP.DATEPLANNED, 'DDMMYY')) AS FECHA_VENCIMIENTO,LPAD(TO_CHAR(ROUND(DP.AMOUNT*100,0)),10,TO_CHAR('0')) AS PAYAMT, 
    58       (SELECT MAX(BP_A.ACCOUNTNO) FROM C_BP_BANKACCOUNT BP_A WHERE BP.C_BPARTNER_ID = BP_A.C_BPARTNER_ID AND BP_A.ISACTIVE='Y')  AS CREDITCARDNUMBER,
    58       (SELECT MAX(BP_A.ACCOUNTNO) FROM C_BP_BANKACCOUNT BP_A WHERE BP.C_BPARTNER_ID = BP_A.C_BPARTNER_ID AND BP_A.ISACTIVE='Y')  AS CREDITCARDNUMBER,
    59       LPAD(BP.VALUE,12,'0') AS N_FACTURA, RPAD(LPAD(BP.VALUE,8,'0'),12,' ') AS N_FACTURA19,L.CITY AS CIUDAD, RPAD(L.ADDRESS1,40,' ') AS DIRECCION, 
    59       LPAD(TO_CHAR(BP.VALUE),12,TO_CHAR('0')) AS N_FACTURA, RPAD(LPAD(TO_CHAR(BP.VALUE),8,TO_CHAR('0')),12,' ') AS N_FACTURA19,L.CITY AS CIUDAD, RPAD(L.ADDRESS1,40,' ') AS DIRECCION, 
    60       TO_CHAR(now(),'DD')||TO_CHAR(now(),'MM')||TO_CHAR(now(),'YY') AS HOY, RPAD(COALESCE(TO_CHAR(DP.DESCRIPTION),' '),40,' ') AS CONCEPTO,
    60       TO_CHAR(now(),'DD')||TO_CHAR(now(),'MM')||TO_CHAR(now(),'YY') AS HOY, RPAD(COALESCE(TO_CHAR(DP.DESCRIPTION),' '),40,' ') AS CONCEPTO,
    61       RPAD(L.CITY,35,' ') AS PLAZA, RPAD(L.CITY,38,' ') AS LOCALIDAD, L.POSTAL AS POSTAL, SUBSTR(L.POSTAL,0,2) AS CODIGO_PROVINCIA, BP.TAXID AS NIF
    61       RPAD(L.CITY,35,' ') AS PLAZA, RPAD(L.CITY,38,' ') AS LOCALIDAD, L.POSTAL AS POSTAL, SUBSTR(L.POSTAL,0,2) AS CODIGO_PROVINCIA, BP.TAXID AS NIF
    62       FROM c_remittanceline S, C_BPARTNER_LOCATION BP_L,
    62       FROM c_remittanceline S, C_BPARTNER_LOCATION BP_L,
    63       C_LOCATION L, C_BPARTNER BP, 
    63       C_LOCATION L, C_BPARTNER BP, 
    64       C_DEBT_PAYMENT DP left join C_INVOICE on DP.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
    64       C_DEBT_PAYMENT DP left join C_INVOICE on DP.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
    65       WHERE S.C_debt_payment_id = DP.C_debt_payment_id
    65       WHERE S.C_debt_payment_id = DP.C_debt_payment_id
    66       AND DP.C_BPARTNER_ID = BP.C_BPARTNER_ID
    66       AND DP.C_BPARTNER_ID = BP.C_BPARTNER_ID
    67       AND BP_L.C_BPARTNER_LOCATION_ID = C_GETBPLOCATIONID(BP.C_BPARTNER_ID, 'B')
    67       AND BP_L.C_BPARTNER_LOCATION_ID = C_GETBPLOCATIONID(BP.C_BPARTNER_ID, 'B')
    68       AND BP_L.C_LOCATION_ID = L.C_LOCATION_ID
    68       AND BP_L.C_LOCATION_ID = L.C_LOCATION_ID
    69       and s.C_Remittance_ID = ?
    69       and s.C_Remittance_ID = TO_NUMBER(?)
    70     ]]>
    70     ]]>
    71     </Sql>
    71     </Sql>
    72     <Parameter name="cRemittanceId"/>
    72     <Parameter name="cRemittanceId"/>
    73     </SqlMethod>
    73     </SqlMethod>
    74   <SqlMethod name="selectTotal" type="preparedStatement" return="multiple">
    74   <SqlMethod name="selectTotal" type="preparedStatement" return="multiple">
    75     <SqlMethodComment></SqlMethodComment>
    75     <SqlMethodComment></SqlMethodComment>
    76     <Sql>
    76     <Sql>
    77     <![CDATA[
    77     <![CDATA[
    78       SELECT RPAD(LPAD(ROUND(sum(DP.AMOUNT)*100,0),10,0),16,' ') AS PAYAMT, 
    78       SELECT RPAD(LPAD(TO_CHAR(ROUND(sum(DP.AMOUNT)*100,0)),10,TO_CHAR('0')),16,TO_CHAR(' ')) AS PAYAMT, 
    79       LPAD(COUNT(*),10,0) AS N_FACTURA 
    79       LPAD(TO_CHAR(COUNT(*)),10,TO_CHAR('0')) AS N_FACTURA 
    80       FROM C_DEBT_PAYMENT DP,
    80       FROM C_DEBT_PAYMENT DP,
    81 			     c_remittanceLine rl
    81 			     c_remittanceLine rl
    82 		 where dp.c_debt_payment_id = rl.c_debt_payment_id
    82 		 where dp.c_debt_payment_id = rl.c_debt_payment_id
    83        and rl.C_Remittance_ID = ?
    83        and rl.C_Remittance_ID = TO_NUMBER(?)
    84     ]]>
    84     ]]>
    85     </Sql>
    85     </Sql>
    86     <Parameter name="cRemittanceId"/>
    86     <Parameter name="cRemittanceId"/>
    87     </SqlMethod>
    87     </SqlMethod>
    88   <SqlMethod name="selectNLineas" type="preparedStatement" return="multiple">
    88   <SqlMethod name="selectNLineas" type="preparedStatement" return="multiple">
    89     <SqlMethodComment></SqlMethodComment>
    89     <SqlMethodComment></SqlMethodComment>
    90     <Sql>
    90     <Sql>
    91     <![CDATA[
    91     <![CDATA[
    92     SELECT RPAD(LPAD(?,10,0),48,' ') AS LINEAS, LPAD(RPAD(LPAD(1,4,0),20,' '),72,' ') AS ORDENANTES, LPAD(' ',72,' ') AS HUECO FROM DUAL
    92     SELECT RPAD(LPAD(TO_CHAR(?),10,TO_CHAR('0')),48,' ') AS LINEAS, LPAD(RPAD(LPAD(TO_CHAR(1),4,TO_CHAR('0')),20,' '),72,TO_CHAR(' ')) AS ORDENANTES, LPAD(' ',72,TO_CHAR(' ')) AS HUECO FROM DUAL
    93     ]]>
    93     ]]>
    94     </Sql>
    94     </Sql>
    95     <Parameter name="nLineas"/>
    95     <Parameter name="nLineas"/>
    96     </SqlMethod>
    96     </SqlMethod>
    97    <SqlMethod name="selectParam" type="preparedStatement" return="String">
    97    <SqlMethod name="selectParam" type="preparedStatement" return="String">
   102         from c_Remittance           r,
   102         from c_Remittance           r,
   103              c_Remittance_Type      rt,
   103              c_Remittance_Type      rt,
   104              c_Remittance_Parameter rp
   104              c_Remittance_Parameter rp
   105 			 where r.C_Remittance_Type_id = rt.C_Remittance_Type_id
   105 			 where r.C_Remittance_Type_id = rt.C_Remittance_Type_id
   106 			   and rp.C_Remittance_Type_Id =  rt.C_Remittance_Type_id
   106 			   and rp.C_Remittance_Type_Id =  rt.C_Remittance_Type_id
   107 				 and r.C_Remittance_id = ?
   107 				 and r.C_Remittance_ID = TO_NUMBER(?)
   108 				 and upper(rp.name)=upper(to_char(?))
   108 				 and upper(rp.name)=upper(to_char(?))
   109     ]]>
   109     ]]>
   110     </Sql>
   110     </Sql>
   111     <Parameter name="cRemittanceId"/>
   111     <Parameter name="cRemittanceId"/>
   112     <Parameter name="param"/>
   112     <Parameter name="param"/>
   119 		FROM C_DEBT_PAYMENT DP,
   119 		FROM C_DEBT_PAYMENT DP,
   120 		     c_remittanceLine rl 
   120 		     c_remittanceLine rl 
   121     WHERE rl.c_debt_payment_id = dp.c_debt_payment_id
   121     WHERE rl.c_debt_payment_id = dp.c_debt_payment_id
   122     AND DP.ISPAID = 'N' 
   122     AND DP.ISPAID = 'N' 
   123     AND DP.C_BPARTNER_ID = NULL
   123     AND DP.C_BPARTNER_ID = NULL
   124 		and rl.c_remittance_id = ?
   124 		and rl.c_remittance_ID = TO_NUMBER(?)
   125     ]]>
   125     ]]>
   126     </Sql>
   126     </Sql>
   127     <Parameter name="cRemittanceId"/>
   127     <Parameter name="cRemittanceId"/>
   128     </SqlMethod>
   128     </SqlMethod>
   129   <SqlMethod name="selectComprobacion2" type="preparedStatement" return="string">
   129   <SqlMethod name="selectComprobacion2" type="preparedStatement" return="string">
   130     <SqlMethodComment></SqlMethodComment>
   130     <SqlMethodComment></SqlMethodComment>
   131     <Sql>
   131     <Sql>
   132     <![CDATA[
   132     <![CDATA[
   133     	SELECT COUNT(*) 
   133     	SELECT COUNT(*) 
   134 		  FROM c_remittanceLine rl 
   134 		  FROM c_remittanceLine rl 
   135     WHERE rl.c_remittance_id = ?
   135     WHERE rl.c_remittance_ID = TO_NUMBER(?)
   136     ]]>
   136     ]]>
   137     </Sql>
   137     </Sql>
   138     <Parameter name="cRemittanceId"/>
   138     <Parameter name="cRemittanceId"/>
   139     </SqlMethod>
   139     </SqlMethod>
   140   <SqlMethod name="selectComprobacion3" type="preparedStatement" return="string">
   140   <SqlMethod name="selectComprobacion3" type="preparedStatement" return="string">
   142     <Sql>
   142     <Sql>
   143     <![CDATA[
   143     <![CDATA[
   144     	select count(*)
   144     	select count(*)
   145 		  from c_remittance
   145 		  from c_remittance
   146 		where processed='Y'
   146 		where processed='Y'
   147 		 and c_remittance_id = ?
   147 		 and c_remittance_ID = TO_NUMBER(?)
   148     ]]>
   148     ]]>
   149     </Sql>
   149     </Sql>
   150     <Parameter name="cRemittanceId"/>
   150     <Parameter name="cRemittanceId"/>
   151     </SqlMethod>
   151     </SqlMethod>
   152   <SqlMethod name="selectComprobacion4" type="preparedStatement" return="string">
   152   <SqlMethod name="selectComprobacion4" type="preparedStatement" return="string">
   157 		 FROM C_DEBT_PAYMENT DP,
   157 		 FROM C_DEBT_PAYMENT DP,
   158 		      c_remittanceLine rl
   158 		      c_remittanceLine rl
   159     WHERE DP.C_DEBT_PAYMENT_ID = rl.C_DEBT_PAYMENT_id
   159     WHERE DP.C_DEBT_PAYMENT_ID = rl.C_DEBT_PAYMENT_id
   160     AND DP.ISPAID = 'N'
   160     AND DP.ISPAID = 'N'
   161     AND DP.C_BPARTNER_ID IS NOT NULL
   161     AND DP.C_BPARTNER_ID IS NOT NULL
   162 		and rl.c_remittance_id = ?
   162 		and rl.c_remittance_ID = TO_NUMBER(?)
   163     ]]>
   163     ]]>
   164     </Sql>
   164     </Sql>
   165     <Parameter name="cRemittanceId"/>
   165     <Parameter name="cRemittanceId"/>
   166     </SqlMethod>
   166     </SqlMethod>
   167 </SqlClass>
   167 </SqlClass>