src/org/openbravo/erpCommon/info/ShipmentReceiptLine_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 * FROM ( SELECT '0' AS RN1, A.* FROM (  
    31           SELECT ml.M_INOUTLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, m.MOVEMENTDATE, m.DOCUMENTNO, 
    32           SELECT ml.M_INOUTLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, m.MOVEMENTDATE, m.DOCUMENTNO, 
    32           m.ISSOTRX, ml.MOVEMENTQTY AS QTY, l.VALUE AS LOCATOR_NAME, a.DESCRIPTION AS ATTRIBUTE_NAME, 
    33           m.ISSOTRX, ml.MOVEMENTQTY AS QTY, l.VALUE AS LOCATOR_NAME, a.DESCRIPTION AS ATTRIBUTE_NAME, 
    33           (ml.LINE || ' - ' || ml.MOVEMENTQTY || ' - ' || m.DOCUMENTNO || ' - ' || m.MOVEMENTDATE || 
    34           (ml.LINE || ' - ' || ml.MOVEMENTQTY || ' - ' || m.DOCUMENTNO || ' - ' || m.MOVEMENTDATE || 
    34           ' - ' || p.NAME) AS LINE_TEXT
    35           ' - ' || p.NAME) AS LINE_TEXT,
       
    36 					ml.M_INOUTLINE_ID || '#' || (ml.LINE || ' - ' || ml.MOVEMENTQTY || ' - ' || m.DOCUMENTNO || ' - ' || m.MOVEMENTDATE || 
       
    37           ' - ' || p.NAME) as rowkey
    35           FROM M_INOUTLINE ml left join M_ATTRIBUTESETINSTANCE a on ml.M_ATTRIBUTESETINSTANCE_ID = a.M_ATTRIBUTESETINSTANCE_ID
    38           FROM M_INOUTLINE ml left join M_ATTRIBUTESETINSTANCE a on ml.M_ATTRIBUTESETINSTANCE_ID = a.M_ATTRIBUTESETINSTANCE_ID
    36                               left join M_LOCATOR l on ml.M_LOCATOR_ID = l.M_LOCATOR_ID,
    39                               left join M_LOCATOR l on ml.M_LOCATOR_ID = l.M_LOCATOR_ID,
    37                M_INOUT m,  M_PRODUCT p, C_BPARTNER b
    40                M_INOUT m,  M_PRODUCT p, C_BPARTNER b
    38           WHERE m.M_INOUT_ID = ml.M_INOUT_ID 
    41           WHERE m.M_INOUT_ID = ml.M_INOUT_ID 
    39           AND m.C_BPARTNER_ID = b.C_BPARTNER_ID 
    42           AND m.C_BPARTNER_ID = b.C_BPARTNER_ID 
    41           AND m.AD_CLIENT_ID IN ('1') 
    44           AND m.AD_CLIENT_ID IN ('1') 
    42           AND m.AD_ORG_ID IN ('1') 
    45           AND m.AD_ORG_ID IN ('1') 
    43           AND m.IsActive='Y' 
    46           AND m.IsActive='Y' 
    44           AND m.ISSOTRX = 'Y' 
    47           AND m.ISSOTRX = 'Y' 
    45           AND ml.ISINVOICED = ?
    48           AND ml.ISINVOICED = ?
    46           ORDER BY b.NAME, m.MOVEMENTDATE, m.DOCUMENTNO, ml.LINE
    49           ORDER BY ml.M_INOUTLINE_ID
       
    50 				) A ) B
       
    51 				WHERE 1=1
    47         ]]>
    52         ]]>
    48         </Sql>
    53         </Sql>
    49         <Field name="position" value="count"/>
    54         <Field name="position" value="count"/>
       
    55 				<Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
    50         <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_CLIENT_ID IN (" text="'1'"/>
    56         <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_CLIENT_ID IN (" text="'1'"/>
    51         <Parameter name="adUserOrg" type="replace" optional="true" after="m.AD_ORG_ID IN (" text="'1'"/>
    57         <Parameter name="adUserOrg" type="replace" optional="true" after="m.AD_ORG_ID IN (" text="'1'"/>
    52         <Parameter name="documentno" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.DocumentNo) LIKE UPPER(?) ]]></Parameter>
    58         <Parameter name="documentno" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.DocumentNo) LIKE UPPER(?) ]]></Parameter>
    53         <Parameter name="description" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.Description) LIKE UPPER(?) ]]></Parameter>
    59         <Parameter name="description" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.Description) LIKE UPPER(?) ]]></Parameter>
    54         <Parameter name="orderReference" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.POReference) LIKE UPPER(?) ]]></Parameter>
    60         <Parameter name="orderReference" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.POReference) LIKE UPPER(?) ]]></Parameter>
    55         <Parameter name="businesPartner" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
    61         <Parameter name="businesPartner" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
    56         <Parameter name="dateFrom" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.MovementDate >= to_date(?) ]]></Parameter>
    62         <Parameter name="dateFrom" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.MovementDate >= to_date(?) ]]></Parameter>
    57         <Parameter name="dateTo" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.MovementDate < to_date(?) ]]></Parameter>
    63         <Parameter name="dateTo" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.MovementDate < to_date(?) ]]></Parameter>
    58         <Parameter name="product" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND ml.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
    64         <Parameter name="product" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND ml.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
    59         <Parameter name="isinvoiced"/>
    65         <Parameter name="isinvoiced"/>
       
    66 				<Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="ml.M_INOUTLINE_ID" />
       
    67         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
    68         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
    60    </SqlMethod>
    69    </SqlMethod>
    61    <SqlMethod name="set" type="constant" return="multiple">
    70    <SqlMethod name="set" type="constant" return="multiple">
    62       <SqlMethodComment></SqlMethodComment>
    71       <SqlMethodComment></SqlMethodComment>
    63       <Sql></Sql>
    72       <Sql></Sql>
    64    </SqlMethod>
    73    </SqlMethod>
    65    <SqlMethod name="selectSOTrx" type="preparedStatement" return="multiple">
    74    <SqlMethod name="selectSOTrx" type="preparedStatement" return="multiple">
    66       <SqlMethodComment></SqlMethodComment>
    75       <SqlMethodComment></SqlMethodComment>
    67       <Sql>
    76       <Sql>
    68       <![CDATA[
    77       <![CDATA[
       
    78 				SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (  
    69           SELECT ml.M_INOUTLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, m.MOVEMENTDATE, m.DOCUMENTNO, 
    79           SELECT ml.M_INOUTLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, m.MOVEMENTDATE, m.DOCUMENTNO, 
    70           m.ISSOTRX, ml.MOVEMENTQTY AS QTY, l.VALUE AS LOCATOR_NAME, a.DESCRIPTION AS ATTRIBUTE_NAME, 
    80           m.ISSOTRX, ml.MOVEMENTQTY AS QTY, l.VALUE AS LOCATOR_NAME, a.DESCRIPTION AS ATTRIBUTE_NAME, 
    71           (ml.LINE || ' - ' || ml.MOVEMENTQTY || ' - ' || m.DOCUMENTNO || ' - ' || m.MOVEMENTDATE || 
    81           (ml.LINE || ' - ' || ml.MOVEMENTQTY || ' - ' || m.DOCUMENTNO || ' - ' || m.MOVEMENTDATE || 
    72           ' - ' || p.NAME) AS LINE_TEXT
    82           ' - ' || p.NAME) AS LINE_TEXT,
       
    83 					ml.M_INOUTLINE_ID || '#' || (ml.LINE || ' - ' || ml.MOVEMENTQTY || ' - ' || m.DOCUMENTNO || ' - ' || m.MOVEMENTDATE || 
       
    84           ' - ' || p.NAME) as rowkey
    73           FROM  M_INOUTLINE ml left join  M_ATTRIBUTESETINSTANCE a on ml.M_ATTRIBUTESETINSTANCE_ID = a.M_ATTRIBUTESETINSTANCE_ID
    85           FROM  M_INOUTLINE ml left join  M_ATTRIBUTESETINSTANCE a on ml.M_ATTRIBUTESETINSTANCE_ID = a.M_ATTRIBUTESETINSTANCE_ID
    74                                left join M_LOCATOR l on ml.M_LOCATOR_ID = l.M_LOCATOR_ID
    86                                left join M_LOCATOR l on ml.M_LOCATOR_ID = l.M_LOCATOR_ID
    75                                left join M_MATCHINV mi on  ml.M_INOUTLINE_ID = mi.M_INOUTLINE_ID,
    87                                left join M_MATCHINV mi on  ml.M_INOUTLINE_ID = mi.M_INOUTLINE_ID,
    76                 M_INOUT m, M_PRODUCT p, C_BPARTNER b 
    88                 M_INOUT m, M_PRODUCT p, C_BPARTNER b 
    77           WHERE m.M_INOUT_ID = ml.M_INOUT_ID 
    89           WHERE m.M_INOUT_ID = ml.M_INOUT_ID 
    82           AND m.IsActive='Y' 
    94           AND m.IsActive='Y' 
    83           AND m.ISSOTRX = 'N' 
    95           AND m.ISSOTRX = 'N' 
    84           GROUP BY ml.M_INOUTLINE_ID, b.NAME, p.NAME, m.MOVEMENTDATE, m.DOCUMENTNO, 
    96           GROUP BY ml.M_INOUTLINE_ID, b.NAME, p.NAME, m.MOVEMENTDATE, m.DOCUMENTNO, 
    85           m.ISSOTRX, ml.MOVEMENTQTY, l.VALUE, a.DESCRIPTION, ml.LINE
    97           m.ISSOTRX, ml.MOVEMENTQTY, l.VALUE, a.DESCRIPTION, ml.LINE
    86           HAVING (ml.MOVEMENTQTY - COALESCE(SUM(mi.QTY),0)) = 0
    98           HAVING (ml.MOVEMENTQTY - COALESCE(SUM(mi.QTY),0)) = 0
    87           ORDER BY b.NAME, m.MOVEMENTDATE, m.DOCUMENTNO, ml.LINE
    99           ORDER BY ml.M_INOUTLINE_ID
       
   100 				) A ) B
       
   101 				WHERE 1=1
    88         ]]>
   102         ]]>
    89         </Sql>
   103         </Sql>
    90         <Field name="position" value="count"/>
   104         <Field name="position" value="count"/>
       
   105 				<Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
    91         <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_CLIENT_ID IN (" text="'1'"/>
   106         <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_CLIENT_ID IN (" text="'1'"/>
    92         <Parameter name="adUserOrg" type="replace" optional="true" after="m.AD_ORG_ID IN (" text="'1'"/>
   107         <Parameter name="adUserOrg" type="replace" optional="true" after="m.AD_ORG_ID IN (" text="'1'"/>
    93         <Parameter name="documentno" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.DocumentNo) LIKE UPPER(?) ]]></Parameter>
   108         <Parameter name="documentno" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.DocumentNo) LIKE UPPER(?) ]]></Parameter>
    94         <Parameter name="description" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.Description) LIKE UPPER(?) ]]></Parameter>
   109         <Parameter name="description" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.Description) LIKE UPPER(?) ]]></Parameter>
    95         <Parameter name="orderReference" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.POReference) LIKE UPPER(?) ]]></Parameter>
   110         <Parameter name="orderReference" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND UPPER(m.POReference) LIKE UPPER(?) ]]></Parameter>
    96         <Parameter name="businesPartner" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
   111         <Parameter name="businesPartner" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
    97         <Parameter name="dateFrom" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.MovementDate >= to_date(?) ]]></Parameter>
   112         <Parameter name="dateFrom" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.MovementDate >= to_date(?) ]]></Parameter>
    98         <Parameter name="dateTo" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.MovementDate < to_date(?) ]]></Parameter>
   113         <Parameter name="dateTo" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND m.MovementDate < to_date(?) ]]></Parameter>
    99         <Parameter name="product" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND ml.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
   114         <Parameter name="product" optional="true" after="AND m.IsActive='Y'"><![CDATA[ AND ml.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
   100         <Parameter name="isinvoiced" optional="true" type="replace" after="(ml.MOVEMENTQTY - COALESCE(SUM(mi.QTY),0)) " text="="/>
   115         <Parameter name="isinvoiced" optional="true" type="replace" after="(ml.MOVEMENTQTY - COALESCE(SUM(mi.QTY),0)) " text="="/>
       
   116 				<Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="ml.M_INOUTLINE_ID" />
       
   117         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
   118         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
   101    </SqlMethod>
   119    </SqlMethod>
   102    <SqlMethod name="selectKey" type="preparedStatement" return="multiple">
   120    <SqlMethod name="selectKey" type="preparedStatement" return="multiple">
   103       <SqlMethodComment></SqlMethodComment>
   121       <SqlMethodComment></SqlMethodComment>
   104       <Sql> 
   122       <Sql> 
   105       <![CDATA[
   123       <![CDATA[
   120           AND m.ISSOTRX = 'Y' 
   138           AND m.ISSOTRX = 'Y' 
   121           AND ml.ISINVOICED = 'N'
   139           AND ml.ISINVOICED = 'N'
   122           ORDER BY b.NAME, m.MOVEMENTDATE, m.DOCUMENTNO, ml.LINE
   140           ORDER BY b.NAME, m.MOVEMENTDATE, m.DOCUMENTNO, ml.LINE
   123       ]]>
   141       ]]>
   124       </Sql>
   142       </Sql>
   125         <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_Client_ID IN (" text="'1'"/>
   143         <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_CLIENT_ID IN (" text="'1'"/>
   126         <Parameter name="adUserOrg" type="replace" optional="true" after="m.AD_Org_ID IN (" text="'1'"/>
   144         <Parameter name="adUserOrg" type="replace" optional="true" after="m.AD_ORG_ID IN (" text="'1'"/>
   127         <Parameter name="key"/>
   145         <Parameter name="key"/>
   128    </SqlMethod>
   146    </SqlMethod>
   129    <SqlMethod name="selectKeySOTrx" type="preparedStatement" return="multiple">
   147    <SqlMethod name="selectKeySOTrx" type="preparedStatement" return="multiple">
   130       <SqlMethodComment></SqlMethodComment>
   148       <SqlMethodComment></SqlMethodComment>
   131       <Sql> 
   149       <Sql> 
   150           m.ISSOTRX, ml.MOVEMENTQTY, l.VALUE, a.DESCRIPTION, ml.LINE
   168           m.ISSOTRX, ml.MOVEMENTQTY, l.VALUE, a.DESCRIPTION, ml.LINE
   151           HAVING (ml.MOVEMENTQTY - COALESCE(SUM(mi.QTY),0)) > 0
   169           HAVING (ml.MOVEMENTQTY - COALESCE(SUM(mi.QTY),0)) > 0
   152           ORDER BY b.NAME, m.MOVEMENTDATE, m.DOCUMENTNO, ml.LINE
   170           ORDER BY b.NAME, m.MOVEMENTDATE, m.DOCUMENTNO, ml.LINE
   153       ]]>
   171       ]]>
   154       </Sql>
   172       </Sql>
   155         <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_Client_ID IN (" text="'1'"/>
   173         <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_CLIENT_ID IN (" text="'1'"/>
   156         <Parameter name="adUserOrg" type="replace" optional="true" after="m.AD_Org_ID IN (" text="'1'"/>
   174         <Parameter name="adUserOrg" type="replace" optional="true" after="m.AD_ORG_ID IN (" text="'1'"/>
   157         <Parameter name="key"/>
   175         <Parameter name="key"/>
   158    </SqlMethod>
   176    </SqlMethod>
   159    <SqlMethod name="selectBPartner" type="preparedStatement" return="String" default="">
   177    <SqlMethod name="selectBPartner" type="preparedStatement" return="String" default="">
   160       <SqlMethodComment></SqlMethodComment>
   178       <SqlMethodComment></SqlMethodComment>
   161       <Sql> 
   179       <Sql>