[Reservations]Implement M_CREATE_RESERVE_STOCK
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Thu, 18 Oct 2012 10:44:18 +0200
changeset 18648 28bd51b48695
parent 18647 a9b7f10f443a
child 18649 0cd06065418f
[Reservations]Implement M_CREATE_RESERVE_STOCK
src-db/database/model/functions/M_CREATE_RESERVE_STOCK.xml
--- a/src-db/database/model/functions/M_CREATE_RESERVE_STOCK.xml	Thu Oct 18 10:44:00 2012 +0200
+++ b/src-db/database/model/functions/M_CREATE_RESERVE_STOCK.xml	Thu Oct 18 10:44:18 2012 +0200
@@ -1,7 +1,10 @@
 <?xml version="1.0"?>
   <database name="FUNCTION M_CREATE_RESERVE_STOCK">
     <function name="M_CREATE_RESERVE_STOCK" type="VARCHAR">
-      <parameter name="p_reserve_id" type="VARCHAR" mode="in">
+      <parameter name="p_reservation_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_user_id" type="VARCHAR" mode="in">
         <default/>
       </parameter>
       <body><![CDATA[/*************************************************************************
@@ -23,16 +26,102 @@
 /*************
 * Function to reserve stock using the get stock function.
 **************/
-  v_product_id    VARCHAR2(32);
-  v_uom_id        VARCHAR2(32);
-  v_qty           NUMBER;
-  v_message       VARCHAR2(2000) := '';
+  v_product_id                  VARCHAR2(32);
+  v_uom_id                      VARCHAR2(32);
+  v_asi_id                      VARCHAR2(32);
+  v_locator_id                  VARCHAR2(32);
+  v_warehouse_id                VARCHAR2(32);
+  v_client_id                   VARCHAR2(32);
+  v_org_id                      VARCHAR2(32);
+  v_soline_id                   VARCHAR2(32);
+  v_qty                         NUMBER;
+  v_pendingtoreserveqty         NUMBER;
+  v_pendingqty                  NUMBER;
+  v_dummy                     VARCHAR2(2000) := '';
+  v_pinstance_id                VARCHAR2(32) := get_uuid();
+  
+
+  TYPE RECORD IS REF CURSOR;
+  cur_stock                     RECORD;
+
 BEGIN
+  SELECT COALESCE(pendingqty, quantity) - COALESCE(reservedqty,0),
+         m_product_id, c_uom_id, m_attributesetinstance_id, m_locator_id, m_warehouse_id,
+         ad_client_id, ad_org_id, c_orderline_id
+    INTO v_pendingtoreserveqty,
+         v_product_id, v_uom_id, v_asi_id, v_locator_id, v_warehouse_id,
+         v_client_id, v_org_id, v_soline_id
+  FROM m_reservation
+  WHERE m_reservation_id = p_reservation_id;
 
+  IF (v_pendingtoreserveqty <= 0) THEN
+    RETURN '@NothingToReserve@';
+  END IF;
 
+  -- Call M_GET_STOCK
+  DECLARE
+    v_pinstance_result AD_PInstance.result%TYPE;
+    v_pinstance_msg AD_PInstance.errormsg%TYPE;
+    v_warehouse_rule_id VARCHAR2(32);
+  BEGIN
+    INSERT INTO ad_pinstance (
+        ad_pinstance_id, ad_process_id, record_id, isactive, 
+        ad_user_id, ad_client_id, ad_org_id, created, createdby,
+        updated, updatedby
+    ) VALUES (
+        v_pinstance_id, 'FF80818132C964E30132C9747257002E', p_reservation_id, 'Y',
+        p_User_ID, v_client_id, v_Org_ID, now(), p_user_ID,
+        now(), p_user_ID
+    );
 
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '10', 'AD_Client_ID', v_client_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '20', 'AD_Org_ID', v_org_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '30', 'M_Product_ID', v_product_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '40', 'C_Uom_ID', v_uom_id, null, null, null, null, null);
+    --AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '50', 'M_Product_Uom_ID', v_Product_UOM_old, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '60', 'M_Warehouse_ID', v_warehouse_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '70', 'M_Locator_ID', v_locator_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '80', 'M_AttributesetInstance_ID', v_asi_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '90', 'Quantity', null, null, v_pendingtoreserveqty, null, null, null);
+    /*
+    * ADD PROPER PROCESS ID!
+    */
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '100', 'ProcessID', '---', null, null, null, null, null);
 
-  RETURN v_message;
+    IF (v_soline_id IS NOT NULL) THEN
+      SELECT m_warehouse_rule_id INTO v_warehouse_rule_id
+      FROM c_orderline
+      WHERE c_orderline_id = v_soline_id;
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '110', 'M_Warehouse_Rule_ID', v_warehouse_rule_id, null, null, null, null, null);
+    END IF;
+
+    M_GET_STOCK(v_pinstance_ID, 'N');
+    -- Check result
+    SELECT result, errormsg
+      INTO v_pinstance_result, v_pinstance_msg
+    FROM ad_pinstance
+    WHERE ad_pinstance_id = v_pinstance_id;
+    IF (v_pinstance_result = 0) THEN
+      -- Error on m_get_stock
+      RAISE_APPLICATION_ERROR(-20000, v_pinstance_msg);
+    END IF;
+  END; -- End Call M_GET_STOCK
+  
+  v_pendingqty := v_pendingtoreserveqty;
+  FOR cur_stock IN (
+    SELECT *
+    FROM m_stock_proposed
+    WHERE ad_pinstance_id = v_pinstance_id
+  ) LOOP
+    v_qty := LEAST(cur_stock.quantity, v_pendingqty);
+    v_dummy := M_ADD_RESERVED_STOCK(p_reservation_id, 'SD', cur_stock.m_storage_detail_id, v_qty, p_user_id);
+    v_pendingqty := v_pendingqty - v_qty;
+    IF (v_pendingqty <= 0) THEN
+      RETURN '@ReservationFullyReserved@';
+    END IF;
+  END LOOP;
+
+  RETURN '@ReservationPartiallyReserved@';
 END M_CREATE_RESERVE_STOCK]]></body>
     </function>
   </database>