[Warehouse]Format m_get_stock procedure.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Thu, 04 Oct 2012 11:02:56 +0200
changeset 18636 a2190673380b
parent 18635 ecaa80a3fae4
child 18637 187d7bc60d4c
[Warehouse]Format m_get_stock procedure.
src-db/database/model/functions/M_GET_STOCK.xml
--- a/src-db/database/model/functions/M_GET_STOCK.xml	Thu Oct 04 09:58:49 2012 +0200
+++ b/src-db/database/model/functions/M_GET_STOCK.xml	Thu Oct 04 11:02:56 2012 +0200
@@ -49,9 +49,7 @@
   v_TableID  VARCHAR2(32):='-1';
   v_AuxID VARCHAR2(32):='-1';
   v_LineNo NUMBER;
-  v_SelectedAttSetInstanceID VARCHAR2(32) ;
   v_Count NUMBER:=1;
-  v_ProductName M_Product.NAME%TYPE;
   v_ProcessID VARCHAR2(32) ;
   
 BEGIN
@@ -63,63 +61,56 @@
 
   -- Get Parameters
   v_ResultStr:='ReadingParameters';
-  FOR Cur_Parameter IN
-    (SELECT i.Record_ID,
-      p.ParameterName,
-      p.P_String,
-      p.P_Number,
-      p.P_Date,
-      i.CreatedBy AS AD_User_ID
-    FROM AD_PInstance i
-    LEFT JOIN AD_PInstance_Para p
-      ON i.AD_PInstance_ID=p.AD_PInstance_ID
-    WHERE i.AD_PInstance_ID=PInstance_ID
-    ORDER BY p.SeqNo
-    )
-  LOOP
+  FOR Cur_Parameter IN (
+      SELECT i.record_id, p.parametername, p.p_string, p.p_number, p.p_date, i.createdby AS ad_user_id
+      FROM ad_pinstance i
+      LEFT JOIN ad_pinstance_para p ON i.ad_pinstance_id=p.ad_pinstance_id
+      WHERE i.ad_pinstance_id=pinstance_id
+      ORDER BY p.seqno
+  ) LOOP
     v_Record_ID:=Cur_Parameter.Record_ID;
     v_AD_User_ID:=Cur_Parameter.AD_User_ID;
-    
-    IF(Cur_Parameter.ParameterName='Quantity') THEN
+
+    IF (Cur_Parameter.ParameterName = 'Quantity') THEN
       v_Quantity:=Cur_Parameter.P_Number;
       DBMS_OUTPUT.PUT_LINE(' Quantity=' || v_Quantity);
-    ELSIF(Cur_Parameter.ParameterName='M_Product_ID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'M_Product_ID') THEN
       v_ProductID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' M_Product_ID=' || v_ProductID);
-    ELSIF(Cur_Parameter.ParameterName='C_Uom_ID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'C_Uom_ID') THEN
       v_UomID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' C_Uom_ID=' || v_UomID);
-    ELSIF(Cur_Parameter.ParameterName='M_Product_Uom_ID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'M_Product_Uom_ID') THEN
       v_ProductUomID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' M_Product_Uom_ID=' || v_ProductUomID);
-    ELSIF(Cur_Parameter.ParameterName='M_Locator_ID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'M_Locator_ID') THEN
       v_LocatorID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' M_Locator_ID=' || v_LocatorID);    
-    ELSIF(Cur_Parameter.ParameterName='M_Warehouse_ID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'M_Warehouse_ID') THEN
       v_WarehouseID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' M_Warehouse_ID=' || v_WarehouseID);
-    ELSIF(Cur_Parameter.ParameterName='AD_Client_ID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'AD_Client_ID') THEN
       v_ClientID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' AD_Client_ID=' || v_ClientID);
-    ELSIF(Cur_Parameter.ParameterName='AD_Org_ID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'AD_Org_ID') THEN
       v_OrgID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' AD_Org_ID=' || v_OrgID);
-    ELSIF(Cur_Parameter.ParameterName='M_AttributesetInstance_ID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'M_AttributesetInstance_ID') THEN
       v_AttributesetinstanceID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' M_AttributesetInstance_ID=' || v_AttributesetinstanceID);
-    ELSIF(Cur_Parameter.ParameterName='UniqueAttribute') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'UniqueAttribute') THEN
       v_UniqueAttribute:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' UniqueAttribute=' || v_UniqueAttribute);
-    ELSIF(Cur_Parameter.ParameterName='TableId') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'TableId') THEN
       v_TableID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' TableId=' || v_TableID);
-    ELSIF(Cur_Parameter.ParameterName='AuxID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'AuxID') THEN
       v_AuxID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' AuxID=' || v_AuxID);    
-    ELSIF(Cur_Parameter.ParameterName='LineNo') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'LineNo') THEN
       v_LineNo:=Cur_Parameter.P_Number;
       DBMS_OUTPUT.PUT_LINE(' LineNo=' || v_LineNo);
-    ELSIF(Cur_Parameter.ParameterName='ProcessID') THEN
+    ELSIF (Cur_Parameter.ParameterName = 'ProcessID') THEN
       v_ProcessID:=Cur_Parameter.P_String;
       DBMS_OUTPUT.PUT_LINE(' ProcessID=' || v_ProcessID);
     ELSE
@@ -128,84 +119,71 @@
   END LOOP; -- Get Parameter
   DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID) ;
 
-        -- Insert Values from Auxiliar STOCK
-
-    FOR Cur_Stock  IN
-    (
-      SELECT MS.M_STORAGE_DETAIL_ID, COALESCE(MS.Quantity, 0) AS Qty, MS.QtyOrder AS QtyOrder, MS.UPDATED AS lastupdated
-      FROM M_STORAGE_DETAIL T        
-       JOIN M_STOCK_AUX MS ON T.M_STORAGE_DETAIL_ID = MS.M_STORAGE_DETAIL_ID
-       JOIN M_LOCATOR L ON T.M_LOCATOR_ID=L.M_LOCATOR_ID
-       JOIN M_WAREHOUSE W ON L.M_WAREHOUSE_ID = W.M_WAREHOUSE_ID
-       JOIN AD_ORG_WAREHOUSE ow ON W.M_WAREHOUSE_ID = ow.M_WAREHOUSE_ID
-                LEFT JOIN M_PRODUCT_UOM PU ON T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
-                LEFT JOIN C_UOM U ON PU.C_UOM_ID = U.C_UOM_ID
-        WHERE T.M_PRODUCT_ID=v_ProductID
-        AND MS.AUX_ID = v_AuxID
-        AND MS.AD_TABLE_ID = v_TableID
-        AND MS.LINE < v_LineNo
-        AND COALESCE(T.C_UOM_ID, '-1') = COALESCE(v_UomID, T.C_UOM_ID, '-1')
-        AND COALESCE(T.M_Product_UOM_id, '-1') = COALESCE(v_ProductUomID, T.M_Product_UOM_id, '-1')
-        AND COALESCE(T.M_ATTRIBUTESETINSTANCE_ID, '-1') = COALESCE(v_AttributesetinstanceID, T.M_ATTRIBUTESETINSTANCE_ID, '-1')
-        AND COALESCE(L.M_LOCATOR_ID, '-1') = COALESCE(v_LocatorID, L.M_LOCATOR_ID, '-1')
-        AND COALESCE(W.M_WAREHOUSE_ID, '-1') = COALESCE(v_WarehouseID, W.M_WAREHOUSE_ID, '-1')
-        AND ow.AD_ORG_ID = v_orgid
-        AND COALESCE(MS.Quantity, 0)>0
-        ORDER BY OW.PRIORITY, L.PRIORITYNO, LASTUPDATED
-      )
-      LOOP
-        INSERT 
-        INTO M_STOCK_PROPOSED
-        (
+  -- Insert Values from Auxiliar STOCK
+  FOR Cur_Stock IN (
+      SELECT ms.m_storage_detail_id, COALESCE(ms.quantity, 0) AS qty, ms.qtyorder AS qtyorder, ms.UPDATED AS lastupdated
+      FROM m_storage_detail t
+          JOIN m_stock_aux ms ON t.m_storage_detail_id = ms.m_storage_detail_id
+          JOIN m_locator l ON t.m_locator_id = l.m_locator_id
+          JOIN m_warehouse w ON l.m_warehouse_id = w.m_warehouse_id
+          JOIN ad_org_warehouse ow ON w.m_warehouse_id = ow.m_warehouse_id
+          LEFT JOIN m_product_uom pu ON t.m_product_uom_id = pu.m_product_uom_id
+          LEFT JOIN c_uom u ON pu.c_uom_id = u.c_uom_id
+      WHERE t.m_product_id = v_productid
+        AND ms.aux_id = v_auxid
+        AND ms.ad_table_id = v_tableid
+        AND ms.line < v_lineno
+        AND COALESCE(t.c_uom_id, '-1') = COALESCE(v_uomid, t.c_uom_id, '-1')
+        AND COALESCE(t.m_product_uom_id, '-1') = COALESCE(v_productuomid, t.m_product_uom_id, '-1')
+        AND COALESCE(t.m_attributesetinstance_id, '-1') = COALESCE(v_attributesetinstanceid, t.m_attributesetinstance_id, '-1')
+        AND COALESCE(l.m_locator_id, '-1') = COALESCE(v_locatorid, l.m_locator_id, '-1')
+        AND COALESCE(w.m_warehouse_id, '-1') = COALESCE(v_warehouseid, w.m_warehouse_id, '-1')
+        AND ow.ad_org_id = v_orgid
+        AND COALESCE(ms.quantity, 0)>0
+      ORDER BY ow.priority, l.priorityno, lastupdated
+  ) LOOP
+    INSERT INTO m_stock_proposed (
         m_stock_proposed_id, ad_client_id, ad_org_id, isactive, 
         created , createdby, updated, updatedby,
         ad_pinstance_id, priority, m_storage_detail_id, quantity, qtyorder
-        )
-        VALUES
-        (
+    ) VALUES (
         get_uuid(), v_ClientID, v_OrgID, 'Y',
         now(), v_AD_User_ID, now(), v_AD_User_ID,
         pinstance_id, v_Count, Cur_Stock.M_STORAGE_DETAIL_ID, Cur_Stock.Qty, Cur_Stock.qtyorder
-        );
-        v_Count:=v_Count+1;
-      END LOOP;
+    );
+    v_Count:=v_Count+1;
+  END LOOP;
 
-    -- Insert Values from Storage Detail
-    FOR Cur_Stock  IN
-    (
-      SELECT T.M_STORAGE_DETAIL_ID, COALESCE(T.QtyOnHand, 0) AS Qty, T.QtyOrderOnHand AS QtyOrder, L.PRIORITYNO, T.UPDATED AS lastupdated
-      FROM M_STORAGE_DETAIL T 
-       JOIN M_LOCATOR L ON T.M_LOCATOR_ID=L.M_LOCATOR_ID
-       JOIN M_WAREHOUSE W ON L.M_WAREHOUSE_ID = W.M_WAREHOUSE_ID
-       JOIN AD_ORG_WAREHOUSE ow ON W.M_WAREHOUSE_ID = ow.M_WAREHOUSE_ID
-           LEFT JOIN M_PRODUCT_UOM PU ON T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
-           LEFT JOIN C_UOM U ON PU.C_UOM_ID = U.C_UOM_ID
-      WHERE T.M_PRODUCT_ID=v_ProductID
-        AND COALESCE(T.C_UOM_ID, '-1') = COALESCE(v_UomID, T.C_UOM_ID, '-1')
-        AND COALESCE(T.M_Product_UOM_id, '-1') = COALESCE(v_ProductUomID, T.M_Product_UOM_id, '-1')
-        AND COALESCE(T.M_ATTRIBUTESETINSTANCE_ID, '-1') = COALESCE(v_AttributesetinstanceID, T.M_ATTRIBUTESETINSTANCE_ID, '-1')
-        AND COALESCE(L.M_LOCATOR_ID, '-1') = COALESCE(v_LocatorID, L.M_LOCATOR_ID, '-1')
-        AND COALESCE(W.M_WAREHOUSE_ID, '-1') = COALESCE(v_WarehouseID, W.M_WAREHOUSE_ID, '-1')
-        AND ow.AD_ORG_ID = v_orgid
-        AND COALESCE(T.QtyOnHand, 0)>0
-        ORDER BY OW.PRIORITY, L.PRIORITYNO, LASTUPDATED
-      )
-      LOOP
-        INSERT 
-        INTO M_STOCK_PROPOSED
-        (
+  -- Insert Values from Storage Detail
+  FOR Cur_Stock  IN (
+      SELECT t.m_storage_detail_id, COALESCE(t.qtyonhand, 0) AS qty, t.qtyorderonhand AS qtyorder, l.priorityno, t.UPDATED AS lastupdated
+      FROM m_storage_detail t 
+          JOIN m_locator l ON t.m_locator_id=l.m_locator_id
+          JOIN m_warehouse w ON l.m_warehouse_id = w.m_warehouse_id
+          JOIN ad_org_warehouse ow ON w.m_warehouse_id = ow.m_warehouse_id
+          LEFT JOIN m_product_uom pu ON t.m_product_uom_id = pu.m_product_uom_id
+          LEFT JOIN c_uom u ON pu.c_uom_id = u.c_uom_id
+      WHERE t.m_product_id=v_productid
+        AND COALESCE(t.c_uom_id, '-1') = COALESCE(v_uomid, t.c_uom_id, '-1')
+        AND COALESCE(t.m_product_uom_id, '-1') = COALESCE(v_productuomid, t.m_product_uom_id, '-1')
+        AND COALESCE(t.m_attributesetinstance_id, '-1') = COALESCE(v_attributesetinstanceid, t.m_attributesetinstance_id, '-1')
+        AND COALESCE(l.m_locator_id, '-1') = COALESCE(v_locatorid, l.m_locator_id, '-1')
+        AND COALESCE(w.m_warehouse_id, '-1') = COALESCE(v_warehouseid, w.m_warehouse_id, '-1')
+        AND ow.ad_org_id = v_orgid
+        AND COALESCE(t.qtyonhand, 0)>0
+      ORDER BY ow.priority, l.priorityno, lastupdated
+  ) LOOP
+    INSERT INTO m_stock_proposed (
         m_stock_proposed_id, ad_client_id, ad_org_id, isactive, 
         created , createdby, updated, updatedby,
         ad_pinstance_id, priority, m_storage_detail_id, quantity, qtyorder
-        )
-        VALUES
-        (
+    ) VALUES (
         get_uuid(), v_ClientID, v_OrgID, 'Y',
         now(), v_AD_User_ID, now(), v_AD_User_ID,
         pinstance_id, v_Count, Cur_Stock.M_STORAGE_DETAIL_ID, Cur_Stock.Qty, Cur_Stock.QtyOrder
-        );
-        v_Count:=v_Count+1;
-      END LOOP;
+    );
+    v_Count:=v_Count+1;
+  END LOOP;
 
   --M_Get_Stock - Finish Process Extension Point
   SELECT count(*) INTO v_count
@@ -238,50 +216,52 @@
     END;
   END IF;
 
+  IF (v_UniqueAttribute = 'Y') THEN
+    DECLARE
+      v_SelectedAttSetInstanceID VARCHAR2(32) ;
+      v_ProductName M_Product.NAME%TYPE;
+    BEGIN
+      DELETE FROM m_stock_proposed ms
+      WHERE ad_pinstance_id = pinstance_id
+        AND EXISTS (SELECT 1
+                    FROM m_storage_detail t
+                    WHERE t.m_storage_detail_id = ms.m_storage_detail_id
+                    AND t.m_attributesetinstance_id = '0');
+      v_SelectedAttSetInstanceID := null;
 
-  IF (v_UniqueAttribute='Y') THEN
-    DELETE FROM M_STOCK_PROPOSED MS
-    WHERE AD_PINSTANCE_ID = pinstance_id
-      AND EXISTS (SELECT '1'
-      FROM M_STORAGE_DETAIL T
-      WHERE T.M_STORAGE_DETAIL_ID = MS.M_STORAGE_DETAIL_ID
-      AND T.M_ATTRIBUTESETINSTANCE_ID = '0'
-      ); 
-    v_SelectedAttSetInstanceID:=null;
-
-    FOR Cur_MStock IN (
-        SELECT A.M_ATTRIBUTESETINSTANCE_ID
-        FROM (
-          SELECT SUM(MS.QUANTITY) AS QTY, T.M_ATTRIBUTESETINSTANCE_ID, MIN(PRIORITY) AS PRIORITY
-          FROM M_STOCK_PROPOSED MS
-               JOIN M_STORAGE_DETAIL T ON MS.M_STORAGE_DETAIL_ID = T.M_STORAGE_DETAIL_ID
-          WHERE AD_PINSTANCE_ID = pinstance_id
-          GROUP BY T.M_ATTRIBUTESETINSTANCE_ID
-          HAVING SUM(MS.QUANTITY) >= v_Quantity
-          ) A
-        ORDER BY A.PRIORITY
-    ) LOOP
-      v_SelectedAttSetInstanceID := Cur_MStock.M_ATTRIBUTESETINSTANCE_ID;
-      EXIT;
-    END LOOP;
-
-    IF (v_SelectedAttSetInstanceID is NULL) THEN
-      -- Return with error result. Not enough stocked with unique attribute.
-      SELECT name INTO v_productname
-      FROM m_product
-      WHERE m_product_id = v_productid;
-      v_Message := '@Product@: ' || v_productname ||'. @NotEnoughAttUniqueStock@';
-      AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_Message, p_calledfromapp) ;
-      RETURN;
-    ELSE
-      DELETE FROM M_STOCK_PROPOSED MS
-      WHERE AD_PINSTANCE_ID = pinstance_id
-        AND EXISTS (SELECT '1'
-                    FROM M_STORAGE_DETAIL T
-                    WHERE T.M_STORAGE_DETAIL_ID = MS.M_STORAGE_DETAIL_ID
-                      AND T.M_ATTRIBUTESETINSTANCE_ID <> v_SelectedAttSetInstanceID
-                   );
-    END IF;
+      FOR Cur_MStock IN (
+          SELECT A.m_attributesetinstance_id
+          FROM (
+            SELECT sum(ms.quantity) AS qty, t.m_attributesetinstance_id, MIN(priority) AS priority
+            FROM m_stock_proposed ms
+                 JOIN m_storage_detail t ON ms.m_storage_detail_id = t.m_storage_detail_id
+            WHERE ad_pinstance_id = pinstance_id
+            GROUP BY t.m_attributesetinstance_id
+            HAVING sum(ms.quantity) >= v_quantity
+            ) A
+          ORDER BY A.priority
+      ) LOOP
+        v_SelectedAttSetInstanceID := Cur_MStock.M_ATTRIBUTESETINSTANCE_ID;
+        EXIT;
+      END LOOP;
+  
+      IF (v_SelectedAttSetInstanceID IS NULL) THEN
+        -- Return with error result. Not enough stocked with unique attribute.
+        SELECT name INTO v_productname
+        FROM m_product
+        WHERE m_product_id = v_productid;
+        v_Message := '@Product@: ' || v_productname ||'. @NotEnoughAttUniqueStock@';
+        AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_Message, p_calledfromapp) ;
+        RETURN;
+      ELSE
+        DELETE FROM M_STOCK_PROPOSED MS
+        WHERE AD_PINSTANCE_ID = pinstance_id
+          AND EXISTS (SELECT 1
+                      FROM M_STORAGE_DETAIL T
+                      WHERE T.M_STORAGE_DETAIL_ID = MS.M_STORAGE_DETAIL_ID
+                        AND T.M_ATTRIBUTESETINSTANCE_ID <> v_SelectedAttSetInstanceID);
+      END IF;
+    END;
   END IF;
 
   --  Update AD_PInstance