-- Function: simif_explodebomnotstock(character varying, character varying)

-- DROP FUNCTION simif_explodebomnotstock(character varying, character varying);

CREATE OR REPLACE FUNCTION simif_explodebomnotstock(p_pinstance_id character varying, p_orderline_id character varying)
  RETURNS void AS
$BODY$ DECLARE 
/*************************************************************************
* 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) 2013-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s):  ______________________________________.
************************************************************************/
  -- Logistice
  v_explode CHAR(1);
  v_orderID VARCHAR(32); --OBTG:VARCHAR2--
  v_CBPartner_ID VARCHAR(32); --OBTG:VARCHAR2--
  v_PriceList_ID VARCHAR(32); --OBTG:VARCHAR2--
  v_PriceList_Version_ID VARCHAR(32); --OBTG:VARCHAR2--
  v_Record_ID VARCHAR(32); --OBTG:VARCHAR2--
  v_C_Tax_ID VARCHAR(32) ; --OBTG:VARCHAR2--
  --TYPE RECORD IS REFCURSOR;
  Cur_Parameter RECORD;
  Cur_MPriceListVersion RECORD;
  CUR_BOM_Line RECORD;
  CUR_BOM RECORD;
  v_Line NUMERIC:=0;
  v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2--
  v_result NUMERIC:= 1;  
  v_Precision NUMERIC;
  v_gross_unit_price NUMERIC;
  v_line_gross_amount NUMERIC;
  v_price_actual NUMERIC;
  v_gross_price_list NUMERIC;
  v_line_net_amt NUMERIC;
  v_istaxincluded CHAR(1);
  v_BOMExplodeToZero VARCHAR(32);
  
  BEGIN
  IF (p_PInstance_ID IS NOT NULL) THEN
      --  Update AD_PInstance
      RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ;
      v_ResultStr:='PInstanceNotFound';
      PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
      -- Get Parameters
      v_ResultStr:='ReadingParameters';
      FOR Cur_Parameter IN
        (SELECT i.Record_ID, i.AD_User_ID, p.ParameterName, p.P_String,
          p.P_Number, p.P_Date
        FROM AD_PINSTANCE i
        LEFT JOIN AD_PINSTANCE_PARA p
          ON i.AD_PInstance_ID=p.AD_PInstance_ID
        WHERE i.AD_PInstance_ID=p_PInstance_ID
        ORDER BY p.SeqNo
        )
      LOOP
        v_Record_ID:=Cur_Parameter.Record_ID;
      END LOOP; -- Get Parameter
  ELSE
      v_Record_ID:=p_Orderline_ID;
  END IF;
    RAISE NOTICE '%','  Record_ID=' || v_Record_ID ;
  BEGIN --BODY
     
    SELECT C_ORDER.c_order_id, C_ORDER.c_bpartner_id, c_order.M_PriceList_ID, c_orderline.explode
      INTO v_orderID, v_CBPartner_ID, v_PriceList_ID, v_explode
    FROM C_ORDER JOIN m_pricelist ON c_order.m_pricelist_id = m_pricelist.m_pricelist_id JOIN c_orderline ON c_order.c_order_id = c_orderline.c_order_id
    WHERE c_orderline.c_orderline_id=v_Record_ID  FOR UPDATE;

    IF (v_explode = 'Y') THEN
      v_ResultStr:='@ProdExploded@';
      RAISE EXCEPTION '%', '@ProdExploded@' ; --OBTG:-20000--
    END IF;
    
    v_ResultStr:='Exploding';

    -- Get Price List Version
    FOR Cur_MPriceListVersion IN
      (SELECT v.M_PriceList_Version_ID AS PriceList_Version_ID, o.M_PriceList_ID,
      p.IsTaxIncluded
       FROM M_PRICELIST_VERSION v, C_ORDER o, M_PriceList p
       WHERE v.M_PriceList_ID=o.M_PriceList_ID
         AND v.M_PriceList_ID = p.M_PriceList_ID
         AND v.ValidFrom<=o.DateOrdered
         AND v.IsActive='Y'
         AND o.C_Order_ID=v_orderID
       ORDER BY v.ValidFrom DESC
      )
    LOOP
      v_PriceList_Version_ID:=Cur_MPriceListVersion.PriceList_Version_ID;
      v_IsTaxIncluded:=Cur_MPriceListVersion.IsTaxIncluded;
      EXIT;
    END LOOP;

    -- Check if the preference BOMExplodeToZeroPrice is 'Y'
    SELECT value INTO v_BOMExplodeToZero
    FROM Ad_Preference WHERE property = 'SIMIF_BOMExplodeToZeroPrice';
    IF(v_BOMExplodeToZero IS NULL) THEN
	v_BOMExplodeToZero:='N';
    END IF;
    
    -- Replace Lines
    v_ResultStr:='Inserting lines';
    For Cur_Bom_Line In
      (SELECT l.*,o.M_Warehouse_ID as ord_m_warehouse_id,o.C_BPartner_Location_ID as ord_C_BPartner_Location_ID,o.issotrx
       FROM C_ORDERLINE l,c_order o
       WHERE l.C_Orderline_ID=v_Record_ID
         AND l.c_order_id=o.c_order_id
         AND l.IsActive='Y'
         AND EXISTS
           (SELECT *
            FROM M_PRODUCT p
            WHERE l.M_Product_ID=p.M_Product_ID
              AND p.IsBOM='Y'
              AND p.IsStocked='N'
            )
       ORDER BY l.Line  FOR UPDATE
       )
    LOOP
      v_Line:=CUR_BOM_Line.Line;
      -- Create New Lines
      FOR CUR_BOM IN
        (SELECT b.M_ProductBOM_ID, p.C_UOM_ID, b.BOMQty, b.Description
         FROM M_PRODUCT_BOM b
              INNER JOIN M_PRODUCT p ON (b.M_ProductBOM_ID=p.M_Product_ID)
         WHERE b.M_Product_ID=CUR_BOM_Line.M_Product_ID
           AND b.isactive='Y'
         ORDER BY Line
         )
      LOOP
        V_C_Tax_Id:=C_Gettax(Cur_Bom.M_Productbom_Id, Cur_Bom_Line.Dateordered, 
        Cur_Bom_Line.Ad_Org_Id, Cur_Bom_Line.Ord_M_Warehouse_Id, Cur_Bom_Line.Ord_C_Bpartner_Location_Id, 
        CUR_BOM_Line.ord_C_BPartner_Location_ID, null, CUR_BOM_Line.issotrx) ;

        SELECT PricePrecision INTO v_Precision FROM C_Currency WHERE
        C_Currency_ID = CUR_BOM_Line.C_Currency_ID;

	IF(v_BOMExplodeToZero='Y') THEN
					v_gross_unit_price:=0;
					v_line_gross_amount:=0;
					v_gross_price_list:=0;
					v_price_actual:=0;
					v_line_net_amt:=0;
        ELSE IF(v_IsTaxIncluded='Y') THEN
					v_gross_unit_price:=M_Get_Offers_Price(CUR_BOM_Line.DateOrdered, v_CBPartner_ID, CUR_BOM.M_ProductBOM_ID, M_BOM_PriceStd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID), CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, v_PriceList_ID);
					v_gross_price_list:=M_Get_Offers_Price(CUR_BOM_Line.DateOrdered, v_CBPartner_ID, CUR_BOM.M_ProductBOM_ID, M_BOM_PriceList(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID), CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, v_PriceList_ID);
					v_line_gross_amount:=ROUND(CUR_BOM_Line.QtyOrdered*CUR_BOM.BOMQty*v_gross_unit_price, v_Precision);
					v_price_actual:=ROUND(C_GET_NET_PRICE_FROM_GROSS(V_C_Tax_Id,  M_Get_Offers_Price(CUR_BOM_Line.DateOrdered, v_CBPartner_ID, CUR_BOM.M_ProductBOM_ID, M_BOM_PriceStd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID), CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, v_PriceList_ID), 0, v_Precision, 1),v_Precision);
					v_line_net_amt:=ROUND(CUR_BOM_Line.QtyOrdered*CUR_BOM.BOMQty*v_price_actual, v_Precision);
				ELSE
					v_gross_unit_price:=0;
					v_line_gross_amount:=0;
					v_gross_price_list:=0;
					v_price_actual:=M_Get_Offers_Price(CUR_BOM_Line.DateOrdered, v_CBPartner_ID, CUR_BOM.M_ProductBOM_ID, M_BOM_PriceStd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID), CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, v_PriceList_ID);
					v_line_net_amt:=ROUND(CUR_BOM_Line.QtyOrdered*CUR_BOM.BOMQty*v_gross_unit_price, v_Precision);
				END IF;
	END IF;
        v_Line:=v_Line + 1;
        INSERT
        INTO C_ORDERLINE
          (
            C_OrderLine_ID, AD_Client_ID, AD_Org_ID, IsActive,
            Created, CreatedBy, Updated, UpdatedBy,
            C_Order_ID, Line, C_BPartner_ID, C_BPartner_Location_ID,
            DateOrdered, DatePromised, DateDelivered, DateInvoiced,
            Description, M_Product_ID, M_Warehouse_ID, DirectShip,
            C_UOM_ID, QtyOrdered, QtyReserved, QtyDelivered,
            QtyInvoiced, M_Shipper_ID, C_Currency_ID,
            PriceList,
            PriceActual, PriceLimit, LineNetAmt, Discount,
            FreightAmt, C_Charge_ID, ChargeAmt, C_Tax_ID,
            QuantityOrder, M_Product_Uom_ID, PriceStd,
            c_project_id, a_asset_id, c_costcenter_id,
            user1_id, user2_id, bom_parent_id,
            gross_unit_price, grosspricelist, line_gross_amount, em_simif_m_product_id
          )
        VALUES
          (
            get_uuid(), CUR_BOM_Line.AD_Client_ID, CUR_BOM_Line.AD_Org_ID, CUR_BOM_Line.IsActive,
            TO_DATE(NOW()), '0', TO_DATE(NOW()), '0',
            CUR_BOM_Line.C_Order_ID, v_Line, CUR_BOM_Line.C_BPartner_ID, CUR_BOM_Line.C_BPartner_Location_ID,
            CUR_BOM_Line.DateOrdered, CUR_BOM_Line.DatePromised, CUR_BOM_Line.DateDelivered, CUR_BOM_Line.DateInvoiced,
            CUR_BOM.Description, CUR_BOM.M_ProductBOM_ID, CUR_BOM_Line.M_Warehouse_ID, CUR_BOM_Line.DirectShip,
            CUR_BOM.C_UOM_ID, CUR_BOM_Line.QtyOrdered*CUR_BOM.BOMQty, CUR_BOM_Line.QtyReserved*CUR_BOM.BOMQty, CUR_BOM_Line.QtyDelivered*CUR_BOM.BOMQty,
            CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, CUR_BOM_Line.M_Shipper_ID, CUR_BOM_Line.C_Currency_ID,
            M_BOM_PriceList(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID),
            v_price_actual, M_BOM_PriceLimit(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID),
            v_line_net_amt, CUR_BOM_Line.Discount,
            CUR_BOM_Line.FreightAmt, CUR_BOM_Line.C_Charge_ID, CUR_BOM_Line.ChargeAmt, coalesce(v_C_Tax_ID,CUR_BOM_Line.c_tax_id),
            CUR_BOM_Line.QuantityOrder, CUR_BOM_Line.M_Product_UOM_ID, M_BOM_PriceStd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID),
            CUR_BOM_Line.c_project_id, CUR_BOM_Line.a_asset_id, CUR_BOM_Line.c_costcenter_id,
            CUR_BOM_Line.user1_id, CUR_BOM_Line.user2_id, v_Record_ID,
            v_gross_unit_price, v_gross_price_list, v_line_gross_amount, CUR_BOM.M_ProductBOM_ID
          );
      END LOOP; -- Create New Lines
    END LOOP; -- Replace Lines
    UPDATE c_orderline set explode='Y' where c_orderline_id = v_Record_ID;
    
    IF (p_PInstance_ID IS NOT NULL) THEN
      RAISE NOTICE '%','Updating PInstance - Finished ';
      PERFORM AD_UPDATE_PINSTANCE(p_pinstance_id, NULL, 'N', v_result, '');
    END IF;
   
  END; --BODY
EXCEPTION
WHEN OTHERS THEN
 RAISE NOTICE '%',v_ResultStr ;
 v_ResultStr:= '@ERROR=' || SQLERRM;
  RAISE NOTICE '%',v_ResultStr ;
  IF(p_PInstance_ID IS NOT NULL) THEN
    -- ROLLBACK;
    PERFORM AD_UPDATE_PINSTANCE(p_pinstance_id, NULL, 'N', 0, v_resultstr);
   END IF;
END ; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION simif_explodebomnotstock(character varying, character varying)
  OWNER TO tad;