0015127: Copy product template in simple sales order does not work

CREATE OR REPLACE FUNCTION m_copy_product_template(pinstance_id character varying)
  RETURNS void 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 SLU
* All portions are Copyright (C) 2001-2006 Openbravo SLU
* All Rights Reserved.
* Contributor(s):  ______________________________________.
************************************************************************/
  -- Logistice
  v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2--
  v_Message VARCHAR(2000):=''; --OBTG:VARCHAR2--
  v_Record_ID VARCHAR(32); --OBTG:VARCHAR2--
  -- Parameter
  --TYPE RECORD IS REFCURSOR;
    Cur_Parameter RECORD;
    -- Record Info
    v_Bpartner VARCHAR(32); --OBTG:VARCHAR2--
    v_Mpricelist VARCHAR(32); --OBTG:VARCHAR2--
    v_Currency VARCHAR(32); --OBTG:VARCHAR2--
    v_Warehouse VARCHAR(32); --OBTG:VARCHAR2--
    v_SeqNo NUMERIC;
    v_CorderId VARCHAR(32); --OBTG:varchar2--
    v_Client VARCHAR(32); --OBTG:VARCHAR2--
    v_Org VARCHAR(32); --OBTG:VARCHAR2--
    v_Createdby VARCHAR(32); --OBTG:VARCHAR2--
    v_Dateordered TIMESTAMP;
    v_BillTo VARCHAR(32); --modified by aminur
    v_BpartnerLocation VARCHAR(32); --OBTG:VARCHAR2--
    v_Pricelist NUMERIC; --modified by aminur
    v_Pricelimit NUMERIC;
    v_Pricestd NUMERIC;
    v_Linenetamount NUMERIC;
    v_Discount NUMERIC; --modified by aminur
    v_Tax VARCHAR(32); --OBTG:VARCHAR2--
    v_Issotrx CHAR;
    v_NoOfLines NUMERIC:=0;
    v_projectID VARCHAR(32); --OBTG:varchar2--
    -- Copy
    Cur_ProductTemplate RECORD;
  BEGIN
    --  Update AD_PInstance
    RAISE NOTICE '%','Updating PInstance - Processing ' || PInstance_ID ;
    v_ResultStr:='PInstanceNotFound';
    PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'Y', NULL, NULL) ;
  BEGIN --BODY
    -- Get Parameters
    v_ResultStr:='ReadingParameters';
    FOR Cur_Parameter IN
      (SELECT i.Record_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=PInstance_ID
      ORDER BY p.SeqNo
      )
    LOOP
      v_Record_ID:=Cur_Parameter.Record_ID;
    END LOOP; -- Get Parameter
    RAISE NOTICE '%','  Record_ID=' || v_Record_ID ;
    v_ResultStr:='ReadingOrder';
    SELECT AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, DATEORDERED,
      C_BPARTNER_ID, BILLTO_ID, C_BPARTNER_LOCATION_ID, C_CURRENCY_ID,
      M_WAREHOUSE_ID, M_PRICELIST_ID, C_PROJECT_ID, ISSOTRX
    INTO v_Client, v_Org, v_Createdby, v_Dateordered,             -- modified by aminur
      v_Bpartner, v_BillTo, v_BpartnerLocation, v_Currency,
      v_Warehouse, v_Mpricelist, v_projectID, v_Issotrx
    FROM C_ORDER
    WHERE C_Order_ID=v_Record_ID;
    SELECT COALESCE(MAX(C_ORDERLINE.line), 10)
    INTO v_SeqNo
    FROM C_ORDERLINE
    WHERE C_ORDERLINE.C_ORDER_ID=v_Record_ID;
    -- Record_ID is the Tab_ID to copy to
    v_ResultStr:='Copying';
    FOR Cur_ProductTemplate IN
      (SELECT M_PRODUCT_TEMPLATE.M_PRODUCT_ID,
        M_PRODUCT_TEMPLATE.QTY,
        M_PRODUCT.C_TaxCategory_Id,
        M_PRODUCT.C_UOM_ID
      FROM M_PRODUCT_TEMPLATE,
        M_PRODUCT
      WHERE M_PRODUCT_TEMPLATE.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID
        AND M_PRODUCT_TEMPLATE.TYPE_TEMPLATE IN('2', '3')
        AND M_PRODUCT_TEMPLATE.C_Bpartner_Id=v_Bpartner
      )
    LOOP
      -- Get prices
      SELECT COALESCE(MAX(M_PRODUCTPRICE.PRICESTD), 0) AS PRICESTD,
        COALESCE(MAX(M_PRODUCTPRICE.PRICELIST), 0) AS PRICELIST,
        COALESCE(MAX(M_PRODUCTPRICE.PRICELIMIT), 0) AS PRICELIMIT
      INTO v_Pricestd,
        v_Pricelist,
        v_Pricelimit
      FROM M_PRICELIST_VERSION,
        M_PRODUCTPRICE
      WHERE M_PRICELIST_VERSION.M_PRICELIST_VERSION_ID=M_PRODUCTPRICE.M_PRICELIST_VERSION_ID
        AND M_PRICELIST_VERSION.VALIDFROM=
        (SELECT MAX(VALIDFROM)
        FROM M_PRICELIST_VERSION
        WHERE ISACTIVE='Y'
          AND VALIDFROM<=TO_DATE(NOW())
          AND M_PRICELIST_ID=v_Mpricelist
        )
        AND M_PRICELIST_VERSION.M_PRICELIST_ID=v_Mpricelist
        AND M_PRODUCTPRICE.M_PRODUCT_ID=Cur_ProductTemplate.M_PRODUCT_ID;
      v_Linenetamount:=C_Currency_Round(M_Get_Offers_Price(TO_DATE(NOW()), v_Bpartner, Cur_ProductTemplate.M_Product_ID, v_Pricestd,Cur_ProductTemplate.QTY,v_Mpricelist ) *Cur_ProductTemplate.QTY, v_Currency, NULL) ;
      SELECT C_Gettax(Cur_ProductTemplate.M_PRODUCT_ID, v_Dateordered, v_Org, v_Warehouse, v_BillTo, v_BpartnerLocation, v_projectID, v_Issotrx)
      INTO v_Tax
      FROM DUAL;
      -- Modified by AMINUR
       IF(v_Pricelist <> 0) THEN
       v_Discount:=(v_Pricelist - v_Pricestd) /v_Pricelist;
       ELSE
       v_Discount:=0;
       END IF;
      -- Get next no
      SELECT * INTO  v_CorderId FROM Ad_Sequence_Next('C_OrderLine', v_Client) ;
      -- Insert
      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,
          S_RESOURCEASSIGNMENT_ID, REF_ORDERLINE_ID,
          M_ATTRIBUTESETINSTANCE_ID, ISDESCRIPTION, QUANTITYORDER, M_PRODUCT_UOM_ID,
          PriceStd
        )
        VALUES
        (
          v_CorderId, v_Client, v_Org, 'Y',
          TO_DATE(NOW()), v_Createdby, TO_DATE(NOW()), v_Createdby,
          v_Record_ID, v_SeqNo, v_Bpartner, v_BpartnerLocation,
          v_Dateordered, NULL, NULL, NULL,
          NULL, Cur_ProductTemplate.M_PRODUCT_ID, v_Warehouse, 'N',
          Cur_ProductTemplate.C_UOM_ID, Cur_ProductTemplate.QTY, 0, 0,
          0, NULL, v_Currency, v_Pricelist,
          M_Get_Offers_Price(TO_DATE(NOW()), v_Bpartner, Cur_ProductTemplate.M_Product_ID, v_Pricestd, Cur_ProductTemplate.QTY, v_Mpricelist), v_Pricelimit, v_Linenetamount, v_Discount,
          0, NULL, 0, v_Tax,
          NULL, NULL,
          NULL, 'N', NULL, NULL,
          v_Pricestd
        )
        ;
      -- update translation
      --
      v_SeqNo:=v_SeqNo + 10;
      v_NoOfLines:=v_NoOfLines + 1;
    END LOOP;
    v_Message:='@Copied@=' || v_NoOfLines ;
    ---- <<FINISH_PROCESS>>
    --  Update AD_PInstance
    RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ;
    PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 1, v_Message) ;
    RETURN;
  END; --BODY
EXCEPTION
WHEN OTHERS THEN
  v_ResultStr:= '@ERROR=' || SQLERRM;
  RAISE NOTICE '%',v_ResultStr ;
  PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
  RETURN;
END ; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION m_copy_product_template(character varying) OWNER TO tad;