modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql
changeset 9076 c4f1a5c7d7c2
child 10772 802557f42def
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql	Fri Dec 10 19:47:45 2010 +0100
@@ -0,0 +1,92 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!--
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html 
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License. 
+ * The Original Code is Openbravo ERP. 
+ * The Initial Developer of the Original Code is Openbravo SLU 
+ * All portions are Copyright (C) 2009 Openbravo SLU 
+ * All Rights Reserved. 
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+-->
+
+<SqlClass name="ReportOrderNotShippedData" package="org.openbravo.reports.ordersawaitingdelivery.erpCommon.ad_reports">
+  <SqlClassComment></SqlClassComment>
+  <SqlMethod name="select" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+    <![CDATA[SELECT ORG.NAME AS ORGNAME, BP.NAME AS BPNAME, 
+    O.DOCUMENTNO, O.POREFERENCE, TO_DATE(O.DATEORDERED) AS DATEORDERED, TO_DATE(O.DATEPROMISED) AS DATEPROMISED, 
+    REFLISTV.NAME AS DELIVERYRULE, COALESCE(DLOC.NAME, BPADD.NAME) AS DELIVERYLOCATION,
+    PR.NAME || ' ' || COALESCE(TO_CHAR(ASI.DESCRIPTION), '') AS PRODNAME, OL.M_ATTRIBUTESETINSTANCE_ID,
+    OL.QTYORDERED || ' ' || COALESCE(TO_CHAR(UO.UOMSYMBOL), '') AS ORDEREDQTY, 
+    (OL.QTYORDERED-OL.QTYDELIVERED) || ' ' || COALESCE(TO_CHAR(UO.UOMSYMBOL), '') AS PENDINGQTY, 
+    SUM(SD.QTYONHAND) || ' ' || COALESCE(TO_CHAR(UO.UOMSYMBOL), '') AS QTYINSTOCK
+    FROM AD_ORG ORG, C_BPARTNER BP, C_BPARTNER_LOCATION BPADD, 
+    M_PRODUCT PR, C_UOM UO, 
+    C_ORDER O
+    LEFT JOIN AD_REF_LIST_V REFLISTV
+    ON REFLISTV.VALUE = O.DELIVERYRULE
+    LEFT JOIN C_BPARTNER_LOCATION DLOC
+    ON DLOC.C_BPARTNER_LOCATION_ID = O.DELIVERY_LOCATION_ID,
+    C_ORDERLINE OL
+    LEFT JOIN M_ATTRIBUTESETINSTANCE ASI
+    ON OL.M_ATTRIBUTESETINSTANCE_ID = ASI.M_ATTRIBUTESETINSTANCE_ID
+    LEFT JOIN M_STORAGE_DETAIL SD
+    ON OL.M_PRODUCT_ID = SD.M_PRODUCT_ID
+    AND OL.C_UOM_ID = SD.C_UOM_ID
+    AND COALESCE(OL.M_ATTRIBUTESETINSTANCE_ID,'0') = COALESCE(SD.M_ATTRIBUTESETINSTANCE_ID,'0')
+    AND COALESCE(OL.M_PRODUCT_UOM_ID, '-1') = COALESCE(SD.M_PRODUCT_UOM_ID,'-1')
+    AND SD.M_LOCATOR_ID IN (SELECT LOC.M_LOCATOR_ID
+    FROM M_LOCATOR LOC
+    WHERE LOC.M_WAREHOUSE_ID = OL.M_WAREHOUSE_ID)
+    WHERE O.AD_ORG_ID = ORG.AD_ORG_ID
+    AND O.C_BPARTNER_ID = BP.C_BPARTNER_ID
+    AND O.C_BPARTNER_LOCATION_ID = BPADD.C_BPARTNER_LOCATION_ID
+    AND O.C_ORDER_ID = OL.C_ORDER_ID
+    AND O.DOCSTATUS IN ('CO','CL')
+    AND O.ISSOTRX = 'Y'
+    AND OL.M_PRODUCT_ID = PR.M_PRODUCT_ID
+    AND OL.C_UOM_ID = UO.C_UOM_ID
+    AND EXISTS (SELECT 1
+    FROM C_ORDER ORD, C_ORDERLINE ORDL
+    WHERE ORD.C_ORDER_ID = O.C_ORDER_ID
+    AND ORD.C_ORDER_ID = ORDL.C_ORDER_ID
+    AND ORDL.QTYORDERED <> ORDL.QTYDELIVERED)
+    AND REFLISTV.AD_REFERENCE_ID = '151'
+    AND REFLISTV.AD_LANGUAGE = ?
+    AND O.AD_CLIENT_ID IN ('1')
+    AND O.AD_ORG_ID IN ('1')
+    AND 1=1
+    GROUP BY ORG.NAME, BP.NAME, O.DOCUMENTNO, O.POREFERENCE, O.DATEORDERED, O.DATEPROMISED,
+    REFLISTV.NAME, DLOC.NAME, BPADD.NAME, PR.NAME, ASI.DESCRIPTION, OL.M_ATTRIBUTESETINSTANCE_ID,
+    OL.QTYORDERED, UO.UOMSYMBOL, OL.QTYDELIVERED
+    ORDER BY ORGNAME, BPNAME, DOCUMENTNO]]></Sql>
+    <Parameter name="adLanguage"/>
+    <Parameter name="adUserClient" type="replace" optional="true" after="O.AD_CLIENT_ID IN (" text="'1'"/>
+    <Parameter name="adUserOrg" type="replace" optional="true" after="O.AD_ORG_ID IN (" text="'1'"/>    
+    <Parameter name="dateFrom" optional="true" after="1=1"><![CDATA[ AND O.DATEORDERED >= TO_DATE(?) ]]></Parameter>
+    <Parameter name="dateTo" optional="true" after="1=1"><![CDATA[ AND O.DATEORDERED < TO_DATE(?) ]]></Parameter>
+    <Parameter name="cBpartnerId" optional="true" after="1=1">AND O.C_BPARTNER_ID = ?</Parameter>
+    <Parameter name="deliveryTerms" optional="true" after="1=1">AND O.DELIVERYRULE = ?</Parameter>
+    <Parameter name="orderDocNo" optional="true" after="1=1">AND UPPER(O.DOCUMENTNO) LIKE '%'||UPPER(?)||'%'</Parameter>    
+    <Parameter name="orderRef" optional="true" after="1=1">AND UPPER(O.POREFERENCE) LIKE '%'||UPPER(?)||'%'</Parameter>    
+    <Parameter name="cOrgId" optional="true" after="1=1">AND O.AD_ORG_ID = ?</Parameter>    
+  </SqlMethod>
+  <SqlMethod name="bPartnerDescription" type="preparedStatement" return="String" default="">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+    SELECT MAX(NAME) AS NAME FROM C_BPARTNER 
+    WHERE C_BPARTNER_ID = ?
+    </Sql>
+    <Parameter name="cBpartnerId"/>
+  </SqlMethod>
+</SqlClass>