0028459: It is necessary a modulescript to solve stock inconsistencies

CREATE OR REPLACE FUNCTION m_recalculate_stock(p_client_id character varying)
  RETURNS character varying AS
$BODY$ DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.0  (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 SL
* All portions are Copyright (C) 2009 Openbravo SL
* All Rights Reserved.
* Contributor(s):  ______________________________________.
************************************************************************/
  Cur_Inout RECORD;
  Cur_Internal RECORD;
  Cur_Inventory RECORD;
  Cur_Movement RECORD;
  Cur_Production RECORD;
  Cur_Transaction RECORD;
  Cur_SOrder RECORD;
  Cur_POrder RECORD;
  
  v_QtyOrder numeric;

BEGIN
--M_STORAGE_DETAIL
--Delete all entries of the client
DELETE FROM m_storage_detail
WHERE ad_client_id = p_client_id;

--Recalculate prequantities with the documents in draft status
FOR Cur_Inout IN (
  SELECT il.ad_client_id, il.ad_org_id, il.updatedby, il.m_product_id,
         il.m_locator_id, il.m_attributesetinstance_id, il.c_uom_id,
         il.m_product_uom_id,
         CASE i.movementtype WHEN 'C-' THEN -1 * il.movementqty
                             ELSE il.movementqty
         END as movementqty,
         CASE i.movementtype WHEN 'C-' THEN -1 * il.quantityorder
                             ELSE il.quantityorder
         END as qtyorder
  FROM m_inoutline il, m_inout i, m_product p
  where il.ad_client_id = p_client_id
    and i.m_inout_id = il.m_inout_id
    and il.m_product_id = p.m_product_id
    and il.m_locator_id is not null
    and p.isstocked = 'Y'
    and p.producttype = 'I'
    and i.docstatus = 'DR'
  ) LOOP
    perform M_UPDATE_INVENTORY(Cur_Inout.AD_CLIENT_ID, Cur_Inout.AD_ORG_ID, Cur_Inout.UPDATEDBY,
        Cur_Inout.M_PRODUCT_ID, Cur_Inout.M_LOCATOR_ID, Cur_Inout.M_ATTRIBUTESETINSTANCE_ID,
        Cur_Inout.C_UOM_ID, Cur_Inout.M_PRODUCT_UOM_ID, NULL, NULL, NULL,
        Cur_Inout.movementqty, Cur_Inout.qtyorder);
END LOOP;

FOR Cur_Internal IN ( 
  SELECT il.ad_client_id, il.ad_org_id, il.updatedby, il.m_product_id, il.m_locator_id,
         il.m_attributesetinstance_id, il.c_uom_id, il.m_product_uom_id,
         il.movementqty, il.quantityorder
  FROM m_internal_consumptionline il, m_internal_consumption i, m_product p
  WHERE il.m_product_id = p.m_product_id
    and il.m_internal_consumption_id = i.m_internal_consumption_id
    and p.isstocked = 'Y'
    and p.producttype = 'I'
    and i.processed = 'N'
  ) LOOP
    perform M_UPDATE_INVENTORY(Cur_Internal.AD_CLIENT_ID, Cur_Internal.AD_ORG_ID, Cur_Internal.UPDATEDBY,
        Cur_Internal.M_PRODUCT_ID, Cur_Internal.M_LOCATOR_ID, Cur_Internal.M_ATTRIBUTESETINSTANCE_ID,
        Cur_Internal.C_UOM_ID, Cur_Internal.M_PRODUCT_UOM_ID, NULL, NULL, NULL,
         -1 *Cur_Internal.MOVEMENTQTY, -1 * Cur_Internal.QUANTITYORDER);
END LOOP;

FOR Cur_Inventory IN ( 
  SELECT il.ad_client_id, il.ad_org_id, il.updatedby, il.m_product_id, il.m_locator_id,
         il.m_attributesetinstance_id, il.c_uom_id, il.m_product_uom_id,
         il.qtycount, il.qtybook, il.quantityorder, il.quantityorderbook
  FROM m_inventoryline il, m_inventory i, m_product p
  WHERE il.m_product_id = p.m_product_id
    and il.m_inventory_id = i.m_inventory_id
    and il.ad_client_id = p_client_id
    and p.isstocked = 'Y'
    and p.producttype = 'I'
    and i.processed = 'N'
  ) LOOP
    perform M_UPDATE_INVENTORY(Cur_Inventory.AD_CLIENT_ID, Cur_Inventory.AD_ORG_ID, Cur_Inventory.UPDATEDBY,
        Cur_Inventory.M_PRODUCT_ID, Cur_Inventory.M_LOCATOR_ID, Cur_Inventory.M_ATTRIBUTESETINSTANCE_ID,
        Cur_Inventory.C_UOM_ID, Cur_Inventory.M_PRODUCT_UOM_ID, NULL, NULL, NULL,
        (Cur_Inventory.QTYCOUNT-Cur_Inventory.QTYBOOK),
        (Cur_Inventory.QUANTITYORDER-Cur_Inventory.QUANTITYORDERBOOK));
END LOOP;

FOR Cur_Movement IN (
  SELECT ml.ad_client_id, ml.ad_org_id, ml.updatedby, ml.m_product_id, ml.m_locator_id,
         ml.m_locatorto_id, ml.m_attributesetinstance_id, ml.c_uom_id, ml.m_product_uom_id,
         ml.movementqty, ml.quantityorder
  FROM m_movementline ml, m_movement m, m_product p
  WHERE ml.m_product_id = p.m_product_id
    and ml.m_movement_id = m.m_movement_id
    and ml.ad_client_id = p_client_id
    AND p.isstocked = 'Y'
    AND p.producttype = 'I'
    and m.processed = 'N'
  ) LOOP
    perform M_UPDATE_INVENTORY(Cur_Movement.AD_CLIENT_ID, Cur_Movement.AD_ORG_ID, Cur_Movement.UPDATEDBY,
        Cur_Movement.M_PRODUCT_ID, Cur_Movement.M_LOCATOR_ID, Cur_Movement.M_ATTRIBUTESETINSTANCE_ID,
        Cur_Movement.C_UOM_ID, Cur_Movement.M_PRODUCT_UOM_ID, NULL, NULL, NULL,
        -1 * Cur_Movement.MOVEMENTQTY, -1 * Cur_Movement.QUANTITYORDER) ;
    perform M_UPDATE_INVENTORY(Cur_Movement.AD_CLIENT_ID, Cur_Movement.AD_ORG_ID, Cur_Movement.UPDATEDBY,
        Cur_Movement.M_PRODUCT_ID, Cur_Movement.M_LOCATORTO_ID, Cur_Movement.M_ATTRIBUTESETINSTANCE_ID,
        Cur_Movement.C_UOM_ID, Cur_Movement.M_PRODUCT_UOM_ID, NULL, NULL, NULL,
        Cur_Movement.MOVEMENTQTY, Cur_Movement.QUANTITYORDER);
END LOOP;

FOR Cur_Production IN (
  SELECT pl.ad_client_id, pl.ad_org_id, pl.updatedby, pl.m_product_id, pl.m_locator_id,
         pl.m_attributesetinstance_id, pl.c_uom_id, pl.m_product_uom_id,
         CASE pl.productiontype WHEN '+' THEN pl.movementqty
                                ELSE -1 * pl.movementqty
         END AS movementqty,
         CASE pl.productiontype WHEN '+' THEN pl.quantityorder
                                ELSE -1 * pl.quantityorder
         END AS quantityorder
  FROM m_productionline pl, m_productionplan pp, m_production pr, m_product p
  WHERE pl.m_product_id = p.m_product_id
    and pl.m_productionplan_id = pp.m_productionplan_id
    and pp.m_production_id = pr.m_production_id
    and pl.ad_client_id = p_client_id
    AND p.isstocked = 'Y'
    AND p.producttype = 'I'
    and pr.processed = 'N'
  ) LOOP
    perform M_UPDATE_INVENTORY(Cur_Production.AD_CLIENT_ID, Cur_Production.AD_ORG_ID, Cur_Production.UPDATEDBY,
        Cur_Production.M_PRODUCT_ID, Cur_Production.M_LOCATOR_ID, Cur_Production.M_ATTRIBUTESETINSTANCE_ID,
        Cur_Production.C_UOM_ID, Cur_Production.M_PRODUCT_UOM_ID, NULL, NULL, NULL,
        Cur_Production.MOVEMENTQTY, Cur_Production.QUANTITYORDER) ;
END LOOP;

--Recalculate the on hand quantities from the transaction table
FOR Cur_Transaction IN (
  SELECT t.ad_client_id, t.ad_org_id, t.updatedby, t.m_product_id, t.m_locator_id,
         t.m_attributesetinstance_id, t.c_uom_id, t.m_product_uom_id,
         t.movementqty, t.quantityorder, i.movementdate AS dateinventory
  FROM m_transaction t LEFT JOIN m_inventoryline il
                                 ON t.m_inventoryline_id = il.m_inventoryline_id
                       LEFT JOIN m_inventory i
                                 ON i.m_inventory_id = il.m_inventory_id,
       m_product p
  WHERE t.ad_client_id = p_client_id
    AND t.m_product_id = p.m_product_id
    AND p.isstocked = 'Y'
    AND p.producttype = 'I'
  ) LOOP
    perform M_UPDATE_INVENTORY(Cur_Transaction.AD_CLIENT_ID, Cur_Transaction.AD_ORG_ID, Cur_Transaction.UPDATEDBY,
        Cur_Transaction.M_PRODUCT_ID, Cur_Transaction.M_LOCATOR_ID, Cur_Transaction.M_ATTRIBUTESETINSTANCE_ID,
        Cur_Transaction.C_UOM_ID, Cur_Transaction.M_PRODUCT_UOM_ID,
        Cur_Transaction.MOVEMENTQTY, Cur_Transaction.QUANTITYORDER, Cur_Transaction.DATEINVENTORY,
        NULL, NULL);
END LOOP;

--M_STORAGE_PENDING
DELETE FROM m_storage_pending
WHERE ad_client_id = p_client_id;

FOR Cur_SOrder IN (
  SELECT ol.ad_client_id, ol.ad_org_id, ol.updatedby, ol.m_product_id, ol.m_warehouse_id,
         ol.m_attributesetinstance_id, ol.c_uom_id, ol.m_product_uom_id,
         ol.qtyordered, ol.qtyreserved, ol.quantityorder
  FROM c_order o, c_orderline ol
  WHERE o.c_order_id = ol.c_order_id
    and ol.ad_client_id = p_client_id
    AND ol.qtydelivered <> ol.qtyordered
    AND o.docstatus IN ('CO', 'IP')
    and ol.m_product_id is not null
    and o.issotrx='Y'
  ) LOOP

    v_QtyOrder := NULL;
    IF (Cur_SOrder.QtyReserved=Cur_SOrder.QtyOrdered) THEN
      v_QtyOrder := Cur_SOrder.QuantityOrder;
    ELSIF Cur_SOrder.M_Product_UOM_ID IS NOT NULL THEN
      v_QtyOrder := C_Uom_Convert(Cur_SOrder.QtyReserved, Cur_SOrder.C_UOM_ID, Cur_SOrder.M_Product_UOM_ID, 'Y');
    END IF;

    perform M_UPDATE_STORAGE_PENDING(Cur_SOrder.AD_Client_ID, Cur_SOrder.AD_Org_ID, Cur_SOrder.UpdatedBy,
        Cur_SOrder.M_Product_ID, Cur_SOrder.M_Warehouse_ID, Cur_SOrder.M_AttributeSetInstance_ID,
        Cur_SOrder.C_UOM_ID, Cur_SOrder.M_PRODUCT_UOM_ID,
        Cur_SOrder.qtyReserved, v_QtyOrder, 0, NULL);
END LOOP;

FOR Cur_POrder IN (
  select ol.ad_client_id, ol.ad_org_id, ol.updatedby, ol.m_product_id, ol.m_warehouse_id,
         ol.m_attributesetinstance_id, ol.c_uom_id, ol.m_product_uom_id,
         ol.qtyordered, a.qtyReceived, ol.qtyordered - a.qtyReceived AS qtypending, ol.quantityorder
  from c_order o,
       c_orderline ol left join (select sum(qty) as qtyReceived, c_orderline_id
                from m_matchpo
                where m_inoutline_id is not null
                group by c_orderline_id) a
                on ol.c_orderline_id = a.c_orderline_id
  where ol.c_order_id = o.c_order_id
    and ol.ad_client_id = p_client_id
    and o.issotrx='N'
    AND o.docstatus IN ('CO', 'IP')
    and ol.qtyordered <> COALESCE(a.qtyReceived,0)
  ) LOOP

    v_QtyOrder := NULL;
    IF (Cur_POrder.QtyPending=Cur_POrder.QtyOrdered) THEN
      v_QtyOrder := Cur_POrder.QuantityOrder;
    ELSIF Cur_POrder.M_Product_UOM_ID IS NOT NULL THEN
      v_QtyOrder := C_Uom_Convert(Cur_POrder.QtyPending, Cur_POrder.C_UOM_ID, Cur_POrder.M_Product_UOM_ID, 'Y');
    END IF;

    perform M_UPDATE_STORAGE_PENDING(Cur_POrder.AD_Client_ID, Cur_POrder.AD_Org_ID, Cur_POrder.UpdatedBy,
        Cur_POrder.M_Product_ID, Cur_POrder.M_Warehouse_ID, Cur_POrder.M_AttributeSetInstance_ID,
        Cur_POrder.C_UOM_ID, Cur_POrder.M_PRODUCT_UOM_ID,
        0, null, Cur_POrder.QtyPending, v_QtyOrder);
END LOOP;

return 'hola';
END ; $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION m_recalculate_stock(character varying) OWNER TO tad;