src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
changeset 1787 84217f37d788
parent 1686 e5a629fb644c
child 3024 31a3b2090e77
equal deleted inserted replaced
1786:20694e60728c 1787:84217f37d788
   197       A.C_UOM_ID,uom.UOMSymbol, A.M_Product_ID,p.NAME, A.C_OrderLine_ID,A.Line, uom.stdprecision, 
   197       A.C_UOM_ID,uom.UOMSymbol, A.M_Product_ID,p.NAME, A.C_OrderLine_ID,A.Line, uom.stdprecision, 
   198       B.M_InOutLine_ID, A.Description, (CASE WHEN B.M_InOutLine_ID IS NULL THEN A.quantityOrder ELSE B.quantityOrder END) AS quantityOrder, 
   198       B.M_InOutLine_ID, A.Description, (CASE WHEN B.M_InOutLine_ID IS NULL THEN A.quantityOrder ELSE B.quantityOrder END) AS quantityOrder, 
   199       (CASE WHEN B.M_InOutLine_ID IS NULL THEN A.M_Product_UOM_ID ELSE B.M_Product_UOM_ID END) AS M_Product_UOM_ID, A.M_ATTRIBUTESETINSTANCE_ID, A.ad_org_id
   199       (CASE WHEN B.M_InOutLine_ID IS NULL THEN A.M_Product_UOM_ID ELSE B.M_Product_UOM_ID END) AS M_Product_UOM_ID, A.M_ATTRIBUTESETINSTANCE_ID, A.ad_org_id
   200       FROM C_UOM uom, M_PRODUCT p, 
   200       FROM C_UOM uom, M_PRODUCT p, 
   201       (SELECT l.ad_org_id, l.C_UOM_ID, l.M_Product_ID, l.Line, l.Description, l.C_OrderLine_ID, (l.QtyOrdered-SUM(COALESCE(mp.Qty,0))) AS QTY,
   201       (SELECT l.ad_org_id, l.C_UOM_ID, l.M_Product_ID, l.Line, l.Description, l.C_OrderLine_ID, (l.QtyOrdered-SUM(COALESCE(mp.Qty,0))) AS QTY,
   202       l.quantityOrder*DIVIDE((l.QtyOrdered-SUM(COALESCE(mp.Qty,0))),l.QtyOrdered) as quantityOrder, l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID
   202       l.quantityOrder*C_DIVIDE((l.QtyOrdered-SUM(COALESCE(mp.Qty,0))),l.QtyOrdered) as quantityOrder, l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID
   203         FROM C_ORDERLINE l left join M_MATCHPO mp on l.C_OrderLine_ID=mp.C_OrderLine_ID
   203         FROM C_ORDERLINE l left join M_MATCHPO mp on l.C_OrderLine_ID=mp.C_OrderLine_ID
   204                                                   and mp.C_InvoiceLine_ID IS NOT NULL
   204                                                   and mp.C_InvoiceLine_ID IS NOT NULL
   205         GROUP BY l.ad_org_id,l.C_UOM_ID, l.M_Product_ID, l.Line, l.Description, l.C_OrderLine_ID, l.QtyOrdered, l.quantityOrder, l.M_Product_UOM_ID,
   205         GROUP BY l.ad_org_id,l.C_UOM_ID, l.M_Product_ID, l.Line, l.Description, l.C_OrderLine_ID, l.QtyOrdered, l.quantityOrder, l.M_Product_UOM_ID,
   206         l.M_ATTRIBUTESETINSTANCE_ID) A left join 
   206         l.M_ATTRIBUTESETINSTANCE_ID) A left join 
   207       (SELECT il.M_InOutLine_ID, il.C_OrderLine_ID, (il.MovementQty-SUM(COALESCE(mi.Qty,0))) AS QTY,
   207       (SELECT il.M_InOutLine_ID, il.C_OrderLine_ID, (il.MovementQty-SUM(COALESCE(mi.Qty,0))) AS QTY,
   208       il.quantityOrder*DIVIDE((il.MovementQty-SUM(COALESCE(mi.Qty,0))),(il.MovementQty)) as quantityOrder, il.M_Product_UOM_ID
   208       il.quantityOrder*C_DIVIDE((il.MovementQty-SUM(COALESCE(mi.Qty,0))),(il.MovementQty)) as quantityOrder, il.M_Product_UOM_ID
   209         FROM M_INOUTLINE il left join M_MATCHINV mi on il.M_InOutLine_ID=mi.M_InOutLine_ID,
   209         FROM M_INOUTLINE il left join M_MATCHINV mi on il.M_InOutLine_ID=mi.M_InOutLine_ID,
   210              M_INOUT io
   210              M_INOUT io
   211         WHERE il.C_OrderLine_ID IS NOT NULL
   211         WHERE il.C_OrderLine_ID IS NOT NULL
   212         AND io.M_InOut_ID = il.M_InOut_ID
   212         AND io.M_InOut_ID = il.M_InOut_ID
   213         AND io.Processed = 'Y'
   213         AND io.Processed = 'Y'
   225     <Sql>
   225     <Sql>
   226       <![CDATA[
   226       <![CDATA[
   227         SELECT (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN (l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)) ELSE il.MOVEMENTQTY END) AS ID, 
   227         SELECT (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN (l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)) ELSE il.MOVEMENTQTY END) AS ID, 
   228         l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME, 
   228         l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME, 
   229         l.C_OrderLine_ID,l.Line, max(uom.stdprecision) as stdprecision, il.M_InOutLine_ID as M_InOutLine_ID, l.Description,
   229         l.C_OrderLine_ID,l.Line, max(uom.stdprecision) as stdprecision, il.M_InOutLine_ID as M_InOutLine_ID, l.Description,
   230         (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN l.quantityOrder*DIVIDE((l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)),(l.QtyOrdered)) ELSE il.quantityOrder END) AS quantityOrder, 
   230         (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN l.quantityOrder*C_DIVIDE((l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)),(l.QtyOrdered)) ELSE il.quantityOrder END) AS quantityOrder, 
   231         (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN l.M_Product_UOM_ID ELSE il.M_Product_UOM_ID END) AS M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id
   231         (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN l.M_Product_UOM_ID ELSE il.M_Product_UOM_ID END) AS M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id
   232         FROM C_ORDERLINE l left join (SELECT M_InOutLine.* FROM M_InOutLine, M_InOut
   232         FROM C_ORDERLINE l left join (SELECT M_InOutLine.* FROM M_InOutLine, M_InOut
   233                                       WHERE M_InOut.M_InOut_ID = M_InOutLine.M_InOut_ID
   233                                       WHERE M_InOut.M_InOut_ID = M_InOutLine.M_InOut_ID
   234                                       AND M_InOutLine.IsInvoiced = 'N'
   234                                       AND M_InOutLine.IsInvoiced = 'N'
   235                                       AND M_InOut.Processed='Y') il  on l.C_OrderLine_ID = il.C_OrderLine_ID,
   235                                       AND M_InOut.Processed='Y') il  on l.C_OrderLine_ID = il.C_OrderLine_ID,
   426   <SqlMethod name="selectFromShipmentUpdate" type="preparedStatement" connection="true" return="multiple">
   426   <SqlMethod name="selectFromShipmentUpdate" type="preparedStatement" connection="true" return="multiple">
   427     <SqlMethodComment></SqlMethodComment>
   427     <SqlMethodComment></SqlMethodComment>
   428     <Sql>
   428     <Sql>
   429       <![CDATA[
   429       <![CDATA[
   430       SELECT (l.MovementQty-SUM(COALESCE(mi.Qty,0))) AS ID, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME, l.M_InOutLine_ID,l.Line,
   430       SELECT (l.MovementQty-SUM(COALESCE(mi.Qty,0))) AS ID, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME, l.M_InOutLine_ID,l.Line,
   431       l.C_OrderLine_ID, max(uom.STDPRECISION) as stdprecision, l.Description, l.quantityOrder*DIVIDE((l.MovementQty-SUM(COALESCE(mi.Qty,0))),l.MovementQty) as quantityOrder, 
   431       l.C_OrderLine_ID, max(uom.STDPRECISION) as stdprecision, l.Description, l.quantityOrder*C_DIVIDE((l.MovementQty-SUM(COALESCE(mi.Qty,0))),l.MovementQty) as quantityOrder, 
   432       l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id
   432       l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id
   433       FROM M_INOUTLINE l left join M_MATCHINV mi on l.M_InOutLine_ID=mi.M_InOutLine_ID,
   433       FROM M_INOUTLINE l left join M_MATCHINV mi on l.M_InOutLine_ID=mi.M_InOutLine_ID,
   434       C_UOM uom,  M_PRODUCT p
   434       C_UOM uom,  M_PRODUCT p
   435       WHERE l.C_UOM_ID=uom.C_UOM_ID
   435       WHERE l.C_UOM_ID=uom.C_UOM_ID
   436       AND l.M_Product_ID=p.M_Product_ID
   436       AND l.M_Product_ID=p.M_Product_ID
   490   </SqlMethod>
   490   </SqlMethod>
   491   <SqlMethod name="selectBOM" type="preparedStatement" connection="true" return="multiple">
   491   <SqlMethod name="selectBOM" type="preparedStatement" connection="true" return="multiple">
   492     <SqlMethodComment></SqlMethodComment>
   492     <SqlMethodComment></SqlMethodComment>
   493     <Sql>
   493     <Sql>
   494       <![CDATA[
   494       <![CDATA[
   495       SELECT BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceStd,
   495       SELECT M_BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceStd,
   496       BOM_PriceList(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceList,
   496       M_BOM_PriceList(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceList,
   497       BOM_PriceLimit(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceLimit, 
   497       M_BOM_PriceLimit(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceLimit, 
   498       M_GET_OFFER_PRICE(M_GET_OFFER(to_date(?), ?, pp.M_Product_ID), BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID)) AS PriceActual, 
   498       M_GET_OFFER_PRICE(M_GET_OFFER(to_date(?), ?, pp.M_Product_ID), M_BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID)) AS PriceActual, 
   499       M_GET_OFFER(to_date(?), ?, pp.M_Product_ID) AS M_Offer_ID
   499       M_GET_OFFER(to_date(?), ?, pp.M_Product_ID) AS M_Offer_ID
   500       FROM M_PRODUCTPRICE pp 
   500       FROM M_PRODUCTPRICE pp 
   501       WHERE pp.M_Product_ID=? 
   501       WHERE pp.M_Product_ID=? 
   502       AND pp.M_PriceList_Version_ID=? 
   502       AND pp.M_PriceList_Version_ID=? 
   503       ]]>
   503       ]]>