Index: postscript-Oracle.sql =================================================================== --- postscript-Oracle.sql (revision 9244) +++ postscript-Oracle.sql (working copy) @@ -527,3 +527,670 @@ CALL DBA_RECOMPILE(NULL) /-- END + +--Create temporary tables + +CREATE GLOBAL TEMPORARY TABLE TEMP_SELECTION +( + TEMP_SELECTION_ID VARCHAR2(32 BYTE) NOT NULL +) +ON COMMIT DELETE ROWS; + +CREATE UNIQUE INDEX TEMP_SELECTION_KEY ON TEMP_SELECTION (TEMP_SELECTION_ID); +ALTER TABLE TEMP_SELECTION ADD (CONSTRAINT TEMP_SELECTION_KEY PRIMARY KEY (TEMP_SELECTION_ID)); + + +CREATE GLOBAL TEMPORARY TABLE TEMP_SELECTION2 +( + QUERY_ID VARCHAR2(32 BYTE) NOT NULL, + TEMP_SELECTION_ID VARCHAR2(32 BYTE) NOT NULL +) +ON COMMIT PRESERVE ROWS; + + +CREATE UNIQUE INDEX TEMP_SELECTION2_KEY ON TEMP_SELECTION2 (QUERY_ID, TEMP_SELECTION_ID); +ALTER TABLE TEMP_SELECTION2 ADD (CONSTRAINT TEMP_SELECTION2_KEY PRIMARY KEY (QUERY_ID, TEMP_SELECTION_ID)); + + +CREATE OR REPLACE PROCEDURE M_PRODUCT_BOM_CHECK(pinstance_id IN VARCHAR2) + +AS +/************************************************************************* + * The contents of this file are subject to the Compiere Public + * License 1.1 ("License"); You may not use this file except in + * compliance with the License. You may obtain a copy of the License in + * the legal folder of your Openbravo installation. + * 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 Compiere ERP & Business Solution + * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc. + * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, + * parts created by ComPiere are Copyright (C) ComPiere, Inc.; + * All Rights Reserved. + * Contributor(s): Openbravo SL + * Contributions are Copyright (C) 2001-2008 Openbravo, S.L. + * + * Specifically, this derivative work is based upon the following Compiere + * file and version. + ************************************************************************* + * $Id: M_Product_BOM_Check.sql,v 1.2 2002/05/22 02:48:28 jjanke Exp $ + *** + * Title: Check BOM Structure (free of cycles) + * Description: + * Tree cannot contain BOMs which are already referenced + ************************************************************************/ + -- Logistice + v_ResultStr VARCHAR2(2000):=''; + v_Message VARCHAR2(2000):=''; + v_Record_ID VARCHAR2(32); + -- Parameter + TYPE RECORD IS REF CURSOR; + Cur_Parameter RECORD; + -- Variables + v_Verified CHAR(1):='Y'; + v_IsBOM CHAR(1) ; + v_CountNo NUMBER; + FINISH_PROCESS BOOLEAN:=false; + BEGIN + -- Update AD_PInstance + DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID) ; + v_ResultStr:='PInstanceNotFound'; + 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 + DBMS_OUTPUT.PUT_LINE(' Record_ID=' || v_Record_ID) ; + -- Record ID is M_Product_ID of product to be tested + SELECT IsBOM INTO v_IsBOM FROM M_Product WHERE M_Product_ID=v_Record_ID; + -- No BOM - should not happen, but no problem + IF(v_IsBOM='N') THEN + FINISH_PROCESS:=true; + -- Did not find product + ELSIF(v_IsBOM<>'Y') THEN + RETURN; + END IF; + IF(NOT FINISH_PROCESS) THEN + -- Checking BOM Structure + v_ResultStr:='InsertingRoot'; + -- Table to put all BOMs - duplicate will cause exception + DELETE FROM Temp_Selection2 WHERE Query_ID='0'; + INSERT INTO Temp_Selection2(Query_ID, Temp_Selection_ID) VALUES('0', v_Record_ID) ; + -- Table of root modes + DELETE FROM Temp_Selection; + INSERT INTO Temp_Selection(Temp_Selection_ID) VALUES(v_Record_ID) ; + LOOP + -- How many do we have:1 + SELECT COUNT(*) INTO v_CountNo FROM Temp_Selection; + -- Nothing to do + EXIT WHEN(v_CountNo=0) ; + -- Insert BOM Nodes into "All" table + INSERT + INTO Temp_Selection2 + ( + Query_ID, + Temp_Selection_ID + ) + SELECT '0', + p.M_Product_ID + FROM M_Product p + WHERE v_IsBOM='Y' + AND EXISTS + (SELECT * + FROM M_Product_BOM b + WHERE p.M_Product_ID=b.M_ProductBOM_ID + AND b.M_Product_ID IN + (SELECT Temp_Selection_ID FROM Temp_Selection) + ) + ; + -- Insert BOM Nodes into temporary table + DELETE FROM Temp_Selection2 WHERE Query_ID='1'; + INSERT + INTO Temp_Selection2 + ( + Query_ID, + Temp_Selection_ID + ) + SELECT '1', + p.M_Product_ID + FROM M_Product p + WHERE v_IsBOM='Y' + AND EXISTS + (SELECT * + FROM M_Product_BOM b + WHERE p.M_Product_ID=b.M_ProductBOM_ID + AND b.M_Product_ID IN + (SELECT Temp_Selection_ID FROM Temp_Selection) + ) + ; + -- Copy into root table + DELETE FROM Temp_Selection; + INSERT + INTO Temp_Selection + ( + Temp_Selection_ID + ) + SELECT Temp_Selection_ID FROM Temp_Selection2 WHERE Query_ID='1'; + END LOOP; + END IF; --FINISH_PROCESS + --<> + -- OK + v_Message:='OK'; + UPDATE M_Product SET IsVerified='Y' WHERE M_Product_ID=v_Record_ID; + -- Update AD_PInstance + DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message) ; + AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 1, v_Message) ; + RETURN; + END; --BODY +/*EXCEPTION +WHEN OTHERS THEN + v_ResultStr:= '@ERROR=' || SQLERRM; + DBMS_OUTPUT.PUT_LINE(v_ResultStr) ; + AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ; + -- + UPDATE M_Product SET IsVerified='N' WHERE M_Product_ID=v_Record_ID; + -- Commented by cromero 19102006 COMMIT; + -- + RETURN;*/ +END M_PRODUCT_BOM_CHECK +; +/--END + + +CREATE OR REPLACE PROCEDURE M_PRICELIST_CREATE(pinstance_id IN VARCHAR2) + +AS +/************************************************************************* + * The contents of this file are subject to the Compiere Public + * License 1.1 ("License"); You may not use this file except in + * compliance with the License. You may obtain a copy of the License in + * the legal folder of your Openbravo installation. + * 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 Compiere ERP & Business Solution + * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc. + * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, + * parts created by ComPiere are Copyright (C) ComPiere, Inc.; + * All Rights Reserved. + * Contributor(s): Openbravo SL + * Contributions are Copyright (C) 2001-2008 Openbravo, S.L. + * + * Specifically, this derivative work is based upon the following Compiere + * file and version. + ************************************************************************* + * $Id: M_PriceList_Create.sql,v 1.7 2003/06/16 14:40:03 jjanke Exp $ + *** + * Title: Create Pricelist + * Description: + * Create PriceList by copying purchase prices (M_Product_PO) + * and applying product category discounts (M_CategoryDiscount) + ************************************************************************/ + -- Logistice + v_ResultStr VARCHAR2(2000):=''; + v_Message VARCHAR2(2000):=''; + -- Parameter + TYPE RECORD IS REF CURSOR; + Cur_Parameter RECORD; + -- Parameter Variables + v_PriceList_Version_ID VARCHAR2(32); + v_DeleteOld CHAR(1):='N'; + -- + v_Currency_ID VARCHAR2(32); + v_Client_ID VARCHAR2(32); + v_Org_ID VARCHAR2(32); + v_UpdatedBy NUMBER; + v_StdPrecision NUMBER; + v_DiscountSchema_ID VARCHAR2(32); + v_PriceList_Version_Base_ID VARCHAR2(32); + -- + v_NextNo VARCHAR2(32):=0; + -- Get PL Parameter + Cur_DiscountLine RECORD; + rowcount NUMBER; + BEGIN + -- Update AD_PInstance + DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing') ; + v_ResultStr:='PInstanceNotFound'; + 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_PriceList_Version_ID:=Cur_Parameter.Record_ID; + + SELECT M_PriceList_Version_Base_ID + INTO v_PriceList_Version_Base_ID + FROM M_PriceList p, + M_PriceList_Version v, + C_Currency c + WHERE p.M_PriceList_ID=v.M_PriceList_ID + AND p.C_Currency_ID=c.C_Currency_ID + AND v.M_PriceList_Version_ID=v_PriceList_Version_ID; + IF (v_PriceList_Version_Base_ID IS NULL OR v_PriceList_Version_Base_ID='') THEN + RAISE_APPLICATION_ERROR(-20102, 'Base Pricelist Version required to complete Create PriceList process.'); + END IF; + + IF(Cur_Parameter.ParameterName='DeleteOld') THEN + v_DeleteOld:=Cur_Parameter.P_String; + DBMS_OUTPUT.PUT_LINE(' DeleteOld=' || v_DeleteOld) ; + ELSE + DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || Cur_Parameter.ParameterName) ; + END IF; + END LOOP; -- Get Parameter + DBMS_OUTPUT.PUT_LINE(' PriceList_Version_ID=' || v_PriceList_Version_ID) ; + -- Checking Prerequisites + -- -- PO Prices must exists + v_ResultStr:='CorrectingProductPO'; + DBMS_OUTPUT.PUT_LINE(v_ResultStr) ; + UPDATE M_Product_PO SET PriceList=0 WHERE PriceList IS NULL; + UPDATE M_Product_PO SET PriceLastPO=0 WHERE PriceLastPO IS NULL; + UPDATE M_Product_PO + SET PricePO=PriceLastPO + WHERE(PricePO IS NULL + OR PricePO=0) + AND PriceLastPO<>0; + UPDATE M_Product_PO SET PricePO=0 WHERE PricePO IS NULL; + -- Set default current vendor + UPDATE M_Product_PO + SET IsCurrentVendor='Y' + WHERE IsCurrentVendor='N' + AND NOT EXISTS + (SELECT pp.M_Product_ID + FROM M_Product_PO pp + WHERE pp.M_Product_ID=M_Product_PO.M_Product_ID + GROUP BY pp.M_Product_ID + HAVING COUNT(*)>1 + ) + ; + -- Commented by cromero 19102006 COMMIT; + /** + * Make sure that we have only one active product + */ + v_ResultStr:='CorrectingDuplicates'; + DBMS_OUTPUT.PUT_LINE(v_ResultStr) ; + DECLARE + -- All duplicate products + Cur_Duplicates RECORD; + -- All vendors of Product - expensive first + CURSOR Cur_Vendors (Product_ID VARCHAR) IS + SELECT M_Product_ID, + C_BPartner_ID + FROM M_Product_PO + WHERE IsCurrentVendor='Y' + AND IsActive='Y' + AND M_Product_ID=Product_ID + ORDER BY PriceList DESC; + -- + Product_ID VARCHAR2(32); + BPartner_ID VARCHAR2(32); + BEGIN + FOR Cur_Duplicates IN + (SELECT DISTINCT M_Product_ID + FROM M_Product_PO po + WHERE IsCurrentVendor='Y' + AND IsActive='Y' + AND EXISTS + (SELECT M_Product_ID + FROM M_Product_PO x + WHERE x.M_Product_ID=po.M_Product_ID + GROUP BY M_Product_ID + HAVING COUNT(*)>1 + ) + ORDER BY 1 + ) + LOOP + OPEN Cur_Vendors(Cur_Duplicates.M_Product_ID) ; + FETCH Cur_Vendors INTO Product_ID, + BPartner_ID; -- Leave First + LOOP + FETCH Cur_Vendors INTO Product_ID, + BPartner_ID; -- Get Record ID + EXIT WHEN Cur_Vendors%NOTFOUND; + -- + DBMS_OUTPUT.PUT_LINE(' Record: ' || Product_ID || ' / ' || BPartner_ID) ; + UPDATE M_Product_PO + SET IsCurrentVendor='N' + WHERE M_Product_ID=Product_ID + AND C_BPartner_ID=BPartner_ID; + END LOOP; + CLOSE Cur_Vendors; + END LOOP; + -- Commented by cromero 19102006 COMMIT; + END; + /** Delete Old Data */ + v_ResultStr:='DeletingOld'; + IF(v_DeleteOld='Y') THEN + DELETE + FROM M_ProductPrice + WHERE M_PriceList_Version_ID=v_PriceList_Version_ID; + rowcount:=SQL%ROWCOUNT; + v_Message:='@Deleted@=' || rowcount || ' - '; + DBMS_OUTPUT.PUT_LINE(v_Message) ; + END IF; + -- Get PriceList Info + v_ResultStr:='GetPLInfo'; + DBMS_OUTPUT.PUT_LINE(v_ResultStr) ; + SELECT p.C_Currency_ID, + c.priceprecision, + v.AD_Client_ID, + v.AD_Org_ID, + v.UpdatedBy, + v.M_DiscountSchema_ID, + M_PriceList_Version_Base_ID + INTO v_Currency_ID, + v_StdPrecision, + v_Client_ID, + v_Org_ID, + v_UpdatedBy, + v_DiscountSchema_ID, + v_PriceList_Version_Base_ID + FROM M_PriceList p, + M_PriceList_Version v, + C_Currency c + WHERE p.M_PriceList_ID=v.M_PriceList_ID + AND p.C_Currency_ID=c.C_Currency_ID + AND v.M_PriceList_Version_ID=v_PriceList_Version_ID; + /** + * For All Discount Lines in Sequence + */ + FOR Cur_DiscountLine IN + (SELECT * + FROM M_DiscountSchemaLine + WHERE M_DiscountSchema_ID=v_DiscountSchema_ID + AND IsActive='Y' + ORDER BY SeqNo + ) + LOOP + v_ResultStr:='Parameter Seq=' || Cur_DiscountLine.SeqNo; + -- DBMS_OUTPUT.PUT_LINE(v_ResultStr); + -- Clear Temporary Table + DELETE FROM Temp_Selection; + -- ----------------------------------- + -- Create Selection in temporary table + -- ----------------------------------- + IF(v_PriceList_Version_Base_ID IS NULL) THEN + -- Create Selection from M_Product_PO + INSERT + INTO Temp_Selection + ( + Temp_Selection_ID + ) + SELECT DISTINCT po.M_Product_ID + FROM M_Product p, + M_Product_PO po + WHERE p.M_Product_ID=po.M_Product_ID + AND(p.AD_Client_ID=v_Client_ID + OR p.AD_Client_ID='0') + AND p.IsActive='Y' + AND po.IsActive='Y' + AND po.IsCurrentVendor='Y' -- Optional Restrictions + AND(Cur_DiscountLine.M_Product_Category_ID IS NULL + OR p.M_Product_Category_ID=Cur_DiscountLine.M_Product_Category_ID) + AND(Cur_DiscountLine.C_BPartner_ID IS NULL + OR po.C_BPartner_ID=Cur_DiscountLine.C_BPartner_ID) + AND(Cur_DiscountLine.M_Product_ID IS NULL + OR p.M_Product_ID=Cur_DiscountLine.M_Product_ID) ; + ELSE + -- Create Selection from existing PriceList + INSERT + INTO Temp_Selection + ( + Temp_Selection_ID + ) + SELECT DISTINCT p.M_Product_ID + FROM M_Product p, + M_ProductPrice pp + WHERE p.M_Product_ID=pp.M_Product_ID + AND pp.M_PriceList_Version_ID=v_PriceList_Version_Base_ID + AND p.IsActive='Y' + AND pp.IsActive='Y' -- Optional Restrictions + AND(Cur_DiscountLine.M_Product_Category_ID IS NULL + OR p.M_Product_Category_ID=Cur_DiscountLine.M_Product_Category_ID) + AND(Cur_DiscountLine.C_BPartner_ID IS NULL + OR EXISTS + (SELECT * + FROM M_Product_PO po + WHERE po.M_Product_ID=p.M_Product_ID + AND po.C_BPartner_ID=Cur_DiscountLine.C_BPartner_ID + )) + AND(Cur_DiscountLine.M_Product_ID IS NULL + OR p.M_Product_ID=Cur_DiscountLine.M_Product_ID) ; + END IF; + rowcount:=SQL%ROWCOUNT; + v_Message:=v_Message || '@Selected@=' || rowcount; + -- DBMS_OUTPUT.PUT_LINE(v_Message); + -- Delete Prices in Selection, so that we can insert + IF(v_PriceList_Version_Base_ID IS NULL OR v_PriceList_Version_Base_ID<>v_PriceList_Version_ID) THEN + v_ResultStr:=v_ResultStr || ', Delete'; + DELETE + FROM M_ProductPrice + WHERE M_ProductPrice.M_PriceList_Version_ID=v_PriceList_Version_ID + AND EXISTS + (SELECT * + FROM Temp_Selection s + WHERE M_ProductPrice.M_Product_ID=s.Temp_Selection_ID + ) + ; + rowcount:=SQL%ROWCOUNT; + v_Message:=', @Deleted@=' || rowcount; + END IF; + -- -------------------- + -- Copy (Insert) Prices + -- -------------------- + IF(v_PriceList_Version_Base_ID=v_PriceList_Version_ID) THEN + -- We have Prices already + NULL; + ELSIF(v_PriceList_Version_Base_ID IS NULL) THEN + -- Copy and Convert from Product_PO + v_ResultStr:=v_ResultStr || ',Copy_PO'; + INSERT + INTO M_ProductPrice + ( + M_PriceList_Version_ID, M_Product_ID, AD_Client_ID, AD_Org_ID, + IsActive, Created, CreatedBy, Updated, + UpdatedBy, + PriceList, + PriceStd, + PriceLimit + ) + SELECT v_PriceList_Version_ID, + po.M_Product_ID, v_Client_ID, v_Org_ID, 'Y', + now(), v_UpdatedBy, now(), v_UpdatedBy, + -- Price List + COALESCE(C_Currency_Convert(po.PriceList, po.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0), + -- Price Std + COALESCE(C_Currency_Convert(po.PriceList, po.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0), + -- Price Limit + COALESCE(C_Currency_Convert(po.PricePO, po.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0) + FROM M_Product_PO po + WHERE EXISTS + (SELECT * FROM Temp_Selection s WHERE po.M_Product_ID=s.Temp_Selection_ID) + AND po.IsCurrentVendor='Y' + AND po.IsActive='Y'; + ELSE + -- Copy and Convert from other PriceList_Version + v_ResultStr:=v_ResultStr || ',Copy_PL'; + INSERT + INTO M_ProductPrice + ( + M_PriceList_Version_ID, M_Product_ID, AD_Client_ID, AD_Org_ID, + IsActive, Created, CreatedBy, Updated, + UpdatedBy, + PriceList, + PriceStd, + PriceLimit + ) + SELECT v_PriceList_Version_ID, + pp.M_Product_ID, v_Client_ID, v_Org_ID, 'Y', + now(), v_UpdatedBy, now(), v_UpdatedBy, + -- Price List + COALESCE(C_Currency_Convert(pp.PriceList, pl.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0), + -- Price Std + COALESCE(C_Currency_Convert(pp.PriceStd, pl.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0), + -- Price Limit + COALESCE(C_Currency_Convert(pp.PriceLimit, pl.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0) + FROM M_ProductPrice pp + INNER JOIN M_PriceList_Version plv + ON(pp.M_PriceList_Version_ID=plv.M_PriceList_Version_ID) + INNER JOIN M_PriceList pl + ON(plv.M_PriceList_ID=pl.M_PriceList_ID) + WHERE pp.M_PriceList_Version_ID=v_PriceList_Version_Base_ID + AND EXISTS + (SELECT * FROM Temp_Selection s WHERE pp.M_Product_ID=s.Temp_Selection_ID) + AND pp.IsActive='Y'; + END IF; + rowcount:=SQL%ROWCOUNT; + v_Message:=v_Message || ', @Inserted@=' || rowcount; + -- ----------- + -- Calculation + -- ----------- + v_ResultStr:=v_ResultStr || ',Calc'; + UPDATE M_ProductPrice + SET PriceList=(( + CASE Cur_DiscountLine.List_Base WHEN 'S' THEN PriceStd WHEN 'X' THEN PriceLimit ELSE PriceList + END + ) + Cur_DiscountLine.List_AddAmt) *(1 - Cur_DiscountLine.List_Discount/100), + PriceStd=(( + CASE Cur_DiscountLine.Std_Base WHEN 'L' THEN PriceList WHEN 'X' THEN PriceLimit ELSE PriceStd + END + ) + Cur_DiscountLine.Std_AddAmt) *(1 - Cur_DiscountLine.Std_Discount/100), + PriceLimit=(( + CASE Cur_DiscountLine.Limit_Base WHEN 'L' THEN PriceList WHEN 'S' THEN PriceStd ELSE PriceLimit + END + ) + Cur_DiscountLine.Limit_AddAmt) *(1 - Cur_DiscountLine.Limit_Discount/100) + WHERE M_PriceList_Version_ID=v_PriceList_Version_ID + AND EXISTS + (SELECT * + FROM Temp_Selection s + WHERE s.Temp_Selection_ID=M_ProductPrice.M_Product_ID + ) + ; + -- -------- + -- Rounding (AD_Reference_ID='155') + -- -------- + v_ResultStr:=v_ResultStr || ',Round'; + UPDATE M_ProductPrice + SET PriceList=( + CASE Cur_DiscountLine.List_Rounding WHEN 'N' THEN PriceList WHEN '0' THEN ROUND(PriceList, 0) -- Even .00 + WHEN 'D' THEN ROUND(PriceList, 1) -- Dime .10 + WHEN 'D' THEN ROUND(PriceList, 1) -- Dime .10 + WHEN '2' THEN ROUND(PriceList, 2) -- 2 Deci + WHEN '3' THEN ROUND(PriceList, 3) -- 3 Deci + WHEN '4' THEN ROUND(PriceList, 4) -- 4 Deci + WHEN 'T' THEN ROUND(PriceList, -1) -- Ten 10.00 + WHEN '5' THEN ROUND(PriceList*20, 0) /20 -- Nickle .05 + WHEN 'Q' THEN ROUND(PriceList*4, 0) /4 -- Quarter .25 + ELSE ROUND(PriceList, v_StdPrecision) + END + ), -- Currency + PriceStd=( + CASE Cur_DiscountLine.Std_Rounding WHEN 'N' THEN PriceStd WHEN '0' THEN ROUND(PriceStd, 0) -- Even .00 + WHEN 'D' THEN ROUND(PriceStd, 1) -- Dime .10 + WHEN '2' THEN ROUND(PriceStd, 2) -- 2 Deci + WHEN '3' THEN ROUND(PriceStd, 3) -- 3 Deci + WHEN '4' THEN ROUND(PriceStd, 4) -- 4 Deci + WHEN 'T' THEN ROUND(PriceStd, -1) -- Ten 10.00 + WHEN '5' THEN ROUND(PriceStd*20, 0) /20 -- Nickle .05 + WHEN 'Q' THEN ROUND(PriceStd*4, 0) /4 -- Quarter .25 + ELSE ROUND(PriceStd, v_StdPrecision) + END + ), -- Currency + PriceLimit=( + CASE Cur_DiscountLine.Limit_Rounding WHEN 'N' THEN PriceLimit WHEN '0' THEN ROUND(PriceLimit, 0) -- Even .00 + WHEN 'D' THEN ROUND(PriceLimit, 1) -- Dime .10 + WHEN '2' THEN ROUND(PriceLimit, 2) -- 2 Deci + WHEN '3' THEN ROUND(PriceLimit, 3) -- 3 Deci + WHEN '4' THEN ROUND(PriceLimit, 4) -- 4 Deci + WHEN 'T' THEN ROUND(PriceLimit, -1) -- Ten 10.00 + WHEN '5' THEN ROUND(PriceLimit*20, 0) /20 -- Nickle .05 + WHEN 'Q' THEN ROUND(PriceLimit*4, 0) /4 -- Quarter .25 + ELSE ROUND(PriceLimit, v_StdPrecision) + END + ) -- Currency + WHERE M_PriceList_Version_ID=v_PriceList_Version_ID + AND EXISTS + (SELECT * + FROM Temp_Selection s + WHERE s.Temp_Selection_ID=M_ProductPrice.M_Product_ID + ) + ; + rowcount:=SQL%ROWCOUNT; + v_Message:=v_Message || ', @Updated@=' || rowcount; + -- Fixed Price overwrite + v_ResultStr:=v_ResultStr || ',Fix'; + UPDATE M_ProductPrice + SET PriceList=( + CASE Cur_DiscountLine.List_Base WHEN 'F' THEN Cur_DiscountLine.List_Fixed ELSE PriceList + END + ), + PriceStd=( + CASE Cur_DiscountLine.Std_Base WHEN 'F' THEN Cur_DiscountLine.Std_Fixed ELSE PriceStd + END + ), + PriceLimit=( + CASE Cur_DiscountLine.Limit_Base WHEN 'F' THEN Cur_DiscountLine.Limit_Fixed ELSE PriceLimit + END + ) + WHERE M_PriceList_Version_ID=v_PriceList_Version_ID + AND EXISTS + (SELECT * + FROM Temp_Selection s + WHERE s.Temp_Selection_ID=M_ProductPrice.M_Product_ID + ) + ; + -- + v_NextNo:=v_NextNo + 1; + v_Message:=''; + END LOOP; -- For all DiscountLines + -- Delete Temporary Selection + DELETE FROM Temp_Selection; + --<> + -- Update AD_PInstance + DBMS_OUTPUT.PUT_LINE(v_Message) ; + DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished') ; + AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 1, v_Message) ; + RETURN; + END; --BODY +EXCEPTION + WHEN OTHERS THEN + v_ResultStr:= '@ERROR=' || SQLERRM; + DBMS_OUTPUT.PUT_LINE(v_ResultStr) ; + AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ; + RETURN; +END M_PRICELIST_CREATE +; +/--END + + + + Index: postscript-PostgreSql.sql =================================================================== --- postscript-PostgreSql.sql (revision 9244) +++ postscript-PostgreSql.sql (working copy) @@ -265,3 +265,672 @@ +-- Function: m_product_bom_check(character varying) + +-- DROP FUNCTION m_product_bom_check(character varying); + +CREATE OR REPLACE FUNCTION m_product_bom_check(pinstance_id character varying) + RETURNS void AS +$BODY$ DECLARE +/************************************************************************* + * The contents of this file are subject to the Compiere Public + * License 1.1 ("License"); You may not use this file except in + * compliance with the License. You may obtain a copy of the License in + * the legal folder of your Openbravo installation. + * 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 Compiere ERP & Business Solution + * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc. + * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, + * parts created by ComPiere are Copyright (C) ComPiere, Inc.; + * All Rights Reserved. + * Contributor(s): Openbravo SL + * Contributions are Copyright (C) 2001-2008 Openbravo, S.L. + * + * Specifically, this derivative work is based upon the following Compiere + * file and version. + ************************************************************************* + * $Id: M_Product_BOM_Check.sql,v 1.2 2002/05/22 02:48:28 jjanke Exp $ + *** + * Title: Check BOM Structure (free of cycles) + * Description: + * Tree cannot contain BOMs which are already referenced + ************************************************************************/ + -- 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; + -- Variables + v_Verified CHAR(1):='Y'; + v_IsBOM CHAR(1) ; + v_CountNo NUMERIC; + FINISH_PROCESS BOOLEAN:=false; + 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 ; + -- Record ID is M_Product_ID of product to be tested + SELECT IsBOM INTO v_IsBOM FROM M_Product WHERE M_Product_ID=v_Record_ID; + -- No BOM - should not happen, but no problem + IF(v_IsBOM='N') THEN + FINISH_PROCESS:=true; + -- Did not find product + ELSIF(v_IsBOM<>'Y') THEN + RETURN; + END IF; + IF(NOT FINISH_PROCESS) THEN + -- Checking BOM Structure + v_ResultStr:='InsertingRoot'; + -- create temporary tables + drop table if exists TEMP_SELECTION; + CREATE GLOBAL TEMPORARY TABLE TEMP_SELECTION + ( + TEMP_SELECTION_ID character varying(32) NOT NULL, + CONSTRAINT TEMP_SELECTION_key PRIMARY KEY (TEMP_SELECTION_ID) + ) + ON COMMIT DELETE ROWS; + drop table if exists TEMP_SELECTION2; + CREATE GLOBAL TEMPORARY TABLE TEMP_SELECTION2 + ( + QUERY_ID character varying(32) NOT NULL, + TEMP_SELECTION_ID character varying(32) NOT NULL, + CONSTRAINT TEMP_SELECTION2_key PRIMARY KEY (QUERY_ID, TEMP_SELECTION_ID) + ) + ON COMMIT PRESERVE ROWS; + -- Table to put all BOMs - duplicate will cause exception + DELETE FROM Temp_Selection2 WHERE Query_ID='0'; + INSERT INTO Temp_Selection2(Query_ID, Temp_Selection_ID) VALUES('0', v_Record_ID) ; + -- Table of root modes + DELETE FROM Temp_Selection; + INSERT INTO Temp_Selection(Temp_Selection_ID) VALUES(v_Record_ID) ; + LOOP + -- How many do we have:1 + SELECT COUNT(*) INTO v_CountNo FROM Temp_Selection; + -- Nothing to do + EXIT WHEN(v_CountNo=0) ; + -- Insert BOM Nodes into "All" table + INSERT + INTO Temp_Selection2 + ( + Query_ID, + Temp_Selection_ID + ) + SELECT '0', + p.M_Product_ID + FROM M_Product p + WHERE v_IsBOM='Y' + AND EXISTS + (SELECT * + FROM M_Product_BOM b + WHERE p.M_Product_ID=b.M_ProductBOM_ID + AND b.M_Product_ID IN + (SELECT Temp_Selection_ID FROM Temp_Selection) + ) + ; + -- Insert BOM Nodes into temporary table + DELETE FROM Temp_Selection2 WHERE Query_ID='1'; + INSERT + INTO Temp_Selection2 + ( + Query_ID, + Temp_Selection_ID + ) + SELECT '1', + p.M_Product_ID + FROM M_Product p + WHERE v_IsBOM='Y' + AND EXISTS + (SELECT * + FROM M_Product_BOM b + WHERE p.M_Product_ID=b.M_ProductBOM_ID + AND b.M_Product_ID IN + (SELECT Temp_Selection_ID FROM Temp_Selection) + ) + ; + -- Copy into root table + DELETE FROM Temp_Selection; + INSERT + INTO Temp_Selection + ( + Temp_Selection_ID + ) + SELECT Temp_Selection_ID FROM Temp_Selection2 WHERE Query_ID='1'; + END LOOP; + END IF; --FINISH_PROCESS + ---- <> + -- OK + v_Message:='OK'; + UPDATE M_Product SET IsVerified='Y' WHERE M_Product_ID=v_Record_ID; + -- 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:=v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message; + RAISE NOTICE '%',v_ResultStr ; + PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ; + -- + UPDATE M_Product SET IsVerified='N' WHERE M_Product_ID=v_Record_ID; + -- Commented by cromero 19102006 -- COMMIT; + -- + RETURN; +END; $BODY$ + LANGUAGE 'plpgsql' VOLATILE +/-- END + +-- Function: m_pricelist_create(character varying) + +-- DROP FUNCTION m_pricelist_create(character varying); + +CREATE OR REPLACE FUNCTION m_pricelist_create(pinstance_id character varying) + RETURNS void AS +$BODY$ DECLARE +/************************************************************************* + * The contents of this file are subject to the Compiere Public + * License 1.1 ("License"); You may not use this file except in + * compliance with the License. You may obtain a copy of the License in + * the legal folder of your Openbravo installation. + * 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 Compiere ERP & Business Solution + * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc. + * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, + * parts created by ComPiere are Copyright (C) ComPiere, Inc.; + * All Rights Reserved. + * Contributor(s): Openbravo SL + * Contributions are Copyright (C) 2001-2008 Openbravo, S.L. + * + * Specifically, this derivative work is based upon the following Compiere + * file and version. + ************************************************************************* + * $Id: M_PriceList_Create.sql,v 1.7 2003/06/16 14:40:03 jjanke Exp $ + *** + * Title: Create Pricelist + * Description: + * Create PriceList by copying purchase prices (M_Product_PO) + * and applying product category discounts (M_CategoryDiscount) + ************************************************************************/ + -- Logistice + v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2-- + v_Message VARCHAR(2000):=''; --OBTG:VARCHAR2-- + -- Parameter + --TYPE RECORD IS REFCURSOR; + Cur_Parameter RECORD; + -- Parameter Variables + v_PriceList_Version_ID VARCHAR(32); --OBTG:VARCHAR2-- + v_DeleteOld CHAR(1):='N'; + -- + v_Currency_ID VARCHAR(32); --OBTG:VARCHAR2-- + v_Client_ID VARCHAR(32); --OBTG:VARCHAR2-- + v_Org_ID VARCHAR(32); --OBTG:VARCHAR2-- + v_UpdatedBy NUMERIC; + v_StdPrecision NUMERIC; + v_DiscountSchema_ID VARCHAR(32); --OBTG:VARCHAR2-- + v_PriceList_Version_Base_ID VARCHAR(32); --OBTG:VARCHAR2-- + -- + v_NextNo VARCHAR(32):=0; --OBTG:VARCHAR2-- + -- Get PL Parameter + Cur_DiscountLine RECORD; + rowcount NUMERIC; + BEGIN + -- Update AD_PInstance + RAISE NOTICE '%','Updating PInstance - Processing' ; + v_ResultStr:='PInstanceNotFound'; + PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'Y', NULL, NULL) ; + BEGIN --BODY + --Create temporary table + drop table if exists Temp_Selection; + CREATE GLOBAL TEMPORARY TABLE Temp_Selection + ( + Temp_Selection_ID character varying(32) NOT NULL, + CONSTRAINT Temp_Selection_key PRIMARY KEY (Temp_Selection_ID) + ) + ON COMMIT DELETE ROWS; + -- 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_PriceList_Version_ID:=Cur_Parameter.Record_ID; + + SELECT M_PriceList_Version_Base_ID + INTO v_PriceList_Version_Base_ID + FROM M_PriceList p, + M_PriceList_Version v, + C_Currency c + WHERE p.M_PriceList_ID=v.M_PriceList_ID + AND p.C_Currency_ID=c.C_Currency_ID + AND v.M_PriceList_Version_ID=v_PriceList_Version_ID; + IF (v_PriceList_Version_Base_ID IS NULL OR v_PriceList_Version_Base_ID='') THEN + RAISE EXCEPTION '%', 'Base Pricelist Version required to complete Create PriceList process.'; --OBTG:-20102-- + END IF; + + IF(Cur_Parameter.ParameterName='DeleteOld') THEN + v_DeleteOld:=Cur_Parameter.P_String; + RAISE NOTICE '%',' DeleteOld=' || v_DeleteOld ; + ELSE + RAISE NOTICE '%','*** Unknown Parameter=' || Cur_Parameter.ParameterName ; + END IF; + END LOOP; -- Get Parameter + RAISE NOTICE '%',' PriceList_Version_ID=' || v_PriceList_Version_ID ; + -- Checking Prerequisites + -- -- PO Prices must exists + v_ResultStr:='CorrectingProductPO'; + RAISE NOTICE '%',v_ResultStr ; + UPDATE M_Product_PO SET PriceList=0 WHERE PriceList IS NULL; + UPDATE M_Product_PO SET PriceLastPO=0 WHERE PriceLastPO IS NULL; + UPDATE M_Product_PO + SET PricePO=PriceLastPO + WHERE(PricePO IS NULL + OR PricePO=0) + AND PriceLastPO<>0; + UPDATE M_Product_PO SET PricePO=0 WHERE PricePO IS NULL; + -- Set default current vendor + UPDATE M_Product_PO + SET IsCurrentVendor='Y' + WHERE IsCurrentVendor='N' + AND NOT EXISTS + (SELECT pp.M_Product_ID + FROM M_Product_PO pp + WHERE pp.M_Product_ID=M_Product_PO.M_Product_ID + GROUP BY pp.M_Product_ID + HAVING COUNT(*)>1 + ) + ; + -- Commented by cromero 19102006 -- COMMIT; + /** + * Make sure that we have only one active product + */ + v_ResultStr:='CorrectingDuplicates'; + RAISE NOTICE '%',v_ResultStr ; + DECLARE + -- All duplicate products + Cur_Duplicates RECORD; + -- All vendors of Product - expensive first + DECLARE Cur_Vendors CURSOR (Product_ID VARCHAR) FOR + SELECT M_Product_ID, + C_BPartner_ID + FROM M_Product_PO + WHERE IsCurrentVendor='Y' + AND IsActive='Y' + AND M_Product_ID=Product_ID + ORDER BY PriceList DESC; + -- + Product_ID VARCHAR(32); --OBTG:VARCHAR2-- + BPartner_ID VARCHAR(32); --OBTG:VARCHAR2-- + BEGIN + FOR Cur_Duplicates IN + (SELECT DISTINCT M_Product_ID + FROM M_Product_PO po + WHERE IsCurrentVendor='Y' + AND IsActive='Y' + AND EXISTS + (SELECT M_Product_ID + FROM M_Product_PO x + WHERE x.M_Product_ID=po.M_Product_ID + GROUP BY M_Product_ID + HAVING COUNT(*)>1 + ) + ORDER BY 1 + ) + LOOP + OPEN Cur_Vendors(Cur_Duplicates.M_Product_ID) ; + FETCH Cur_Vendors INTO Product_ID, + BPartner_ID; -- Leave First + LOOP + FETCH Cur_Vendors INTO Product_ID, + BPartner_ID; -- Get Record ID + EXIT WHEN NOT FOUND ; --OBTG:Cur_Vendors-- + -- + RAISE NOTICE '%',' Record: ' || Product_ID || ' / ' || BPartner_ID ; + UPDATE M_Product_PO + SET IsCurrentVendor='N' + WHERE M_Product_ID=Product_ID + AND C_BPartner_ID=BPartner_ID; + END LOOP; + CLOSE Cur_Vendors; + END LOOP; + -- Commented by cromero 19102006 -- COMMIT; + END; + /** Delete Old Data */ + v_ResultStr:='DeletingOld'; + IF(v_DeleteOld='Y') THEN + DELETE + FROM M_ProductPrice + WHERE M_PriceList_Version_ID=v_PriceList_Version_ID; + GET DIAGNOSTICS rowcount:=ROW_COUNT; + v_Message:='@Deleted@=' || rowcount || ' - '; + RAISE NOTICE '%',v_Message ; + END IF; + -- Get PriceList Info + v_ResultStr:='GetPLInfo'; + RAISE NOTICE '%',v_ResultStr ; + SELECT p.C_Currency_ID, + c.priceprecision, + v.AD_Client_ID, + v.AD_Org_ID, + v.UpdatedBy, + v.M_DiscountSchema_ID, + M_PriceList_Version_Base_ID + INTO v_Currency_ID, + v_StdPrecision, + v_Client_ID, + v_Org_ID, + v_UpdatedBy, + v_DiscountSchema_ID, + v_PriceList_Version_Base_ID + FROM M_PriceList p, + M_PriceList_Version v, + C_Currency c + WHERE p.M_PriceList_ID=v.M_PriceList_ID + AND p.C_Currency_ID=c.C_Currency_ID + AND v.M_PriceList_Version_ID=v_PriceList_Version_ID; + /** + * For All Discount Lines in Sequence + */ + FOR Cur_DiscountLine IN + (SELECT * + FROM M_DiscountSchemaLine + WHERE M_DiscountSchema_ID=v_DiscountSchema_ID + AND IsActive='Y' + ORDER BY SeqNo + ) + LOOP + v_ResultStr:='Parameter Seq=' || Cur_DiscountLine.SeqNo; + -- DBMS_OUTPUT.PUT_LINE(v_ResultStr); + -- Clear Temporary Table + DELETE FROM Temp_Selection; + -- ----------------------------------- + -- Create Selection in temporary table + -- ----------------------------------- + IF(v_PriceList_Version_Base_ID IS NULL) THEN + -- Create Selection from M_Product_PO + INSERT + INTO Temp_Selection + ( + Temp_Selection_ID + ) + SELECT DISTINCT po.M_Product_ID + FROM M_Product p, + M_Product_PO po + WHERE p.M_Product_ID=po.M_Product_ID + AND(p.AD_Client_ID=v_Client_ID + OR p.AD_Client_ID='0') + AND p.IsActive='Y' + AND po.IsActive='Y' + AND po.IsCurrentVendor='Y' -- Optional Restrictions + AND(Cur_DiscountLine.M_Product_Category_ID IS NULL + OR p.M_Product_Category_ID=Cur_DiscountLine.M_Product_Category_ID) + AND(Cur_DiscountLine.C_BPartner_ID IS NULL + OR po.C_BPartner_ID=Cur_DiscountLine.C_BPartner_ID) + AND(Cur_DiscountLine.M_Product_ID IS NULL + OR p.M_Product_ID=Cur_DiscountLine.M_Product_ID) ; + ELSE + -- Create Selection from existing PriceList + INSERT + INTO Temp_Selection + ( + Temp_Selection_ID + ) + SELECT DISTINCT p.M_Product_ID + FROM M_Product p, + M_ProductPrice pp + WHERE p.M_Product_ID=pp.M_Product_ID + AND pp.M_PriceList_Version_ID=v_PriceList_Version_Base_ID + AND p.IsActive='Y' + AND pp.IsActive='Y' -- Optional Restrictions + AND(Cur_DiscountLine.M_Product_Category_ID IS NULL + OR p.M_Product_Category_ID=Cur_DiscountLine.M_Product_Category_ID) + AND(Cur_DiscountLine.C_BPartner_ID IS NULL + OR EXISTS + (SELECT * + FROM M_Product_PO po + WHERE po.M_Product_ID=p.M_Product_ID + AND po.C_BPartner_ID=Cur_DiscountLine.C_BPartner_ID + )) + AND(Cur_DiscountLine.M_Product_ID IS NULL + OR p.M_Product_ID=Cur_DiscountLine.M_Product_ID) ; + END IF; + GET DIAGNOSTICS rowcount:=ROW_COUNT; + v_Message:=v_Message || '@Selected@=' || rowcount; + -- DBMS_OUTPUT.PUT_LINE(v_Message); + -- Delete Prices in Selection, so that we can insert + IF(v_PriceList_Version_Base_ID IS NULL OR v_PriceList_Version_Base_ID<>v_PriceList_Version_ID) THEN + v_ResultStr:=v_ResultStr || ', Delete'; + DELETE + FROM M_ProductPrice + WHERE M_ProductPrice.M_PriceList_Version_ID=v_PriceList_Version_ID + AND EXISTS + (SELECT * + FROM Temp_Selection s + WHERE M_ProductPrice.M_Product_ID=s.Temp_Selection_ID + ) + ; + GET DIAGNOSTICS rowcount:=ROW_COUNT; + v_Message:=', @Deleted@=' || rowcount; + END IF; + -- -------------------- + -- Copy (Insert) Prices + -- -------------------- + IF(v_PriceList_Version_Base_ID=v_PriceList_Version_ID) THEN + -- We have Prices already + NULL; + ELSIF(v_PriceList_Version_Base_ID IS NULL) THEN + -- Copy and Convert from Product_PO + v_ResultStr:=v_ResultStr || ',Copy_PO'; + INSERT + INTO M_ProductPrice + ( + M_PriceList_Version_ID, M_Product_ID, AD_Client_ID, AD_Org_ID, + IsActive, Created, CreatedBy, Updated, + UpdatedBy, + PriceList, + PriceStd, + PriceLimit + ) + SELECT v_PriceList_Version_ID, + po.M_Product_ID, v_Client_ID, v_Org_ID, 'Y', + TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, + -- Price List + COALESCE(C_Currency_Convert(po.PriceList, po.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0), + -- Price Std + COALESCE(C_Currency_Convert(po.PriceList, po.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0), + -- Price Limit + COALESCE(C_Currency_Convert(po.PricePO, po.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0) + FROM M_Product_PO po + WHERE EXISTS + (SELECT * FROM Temp_Selection s WHERE po.M_Product_ID=s.Temp_Selection_ID) + AND po.IsCurrentVendor='Y' + AND po.IsActive='Y'; + ELSE + -- Copy and Convert from other PriceList_Version + v_ResultStr:=v_ResultStr || ',Copy_PL'; + INSERT + INTO M_ProductPrice + ( + M_PriceList_Version_ID, M_Product_ID, AD_Client_ID, AD_Org_ID, + IsActive, Created, CreatedBy, Updated, + UpdatedBy, + PriceList, + PriceStd, + PriceLimit + ) + SELECT v_PriceList_Version_ID, + pp.M_Product_ID, v_Client_ID, v_Org_ID, 'Y', + TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, + -- Price List + COALESCE(C_Currency_Convert(pp.PriceList, pl.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0), + -- Price Std + COALESCE(C_Currency_Convert(pp.PriceStd, pl.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0), + -- Price Limit + COALESCE(C_Currency_Convert(pp.PriceLimit, pl.C_Currency_ID, v_Currency_ID, Cur_DiscountLine.ConversionDate, Cur_DiscountLine.ConversionRateType, v_Client_ID, v_Org_ID), 0) + FROM M_ProductPrice pp + INNER JOIN M_PriceList_Version plv + ON(pp.M_PriceList_Version_ID=plv.M_PriceList_Version_ID) + INNER JOIN M_PriceList pl + ON(plv.M_PriceList_ID=pl.M_PriceList_ID) + WHERE pp.M_PriceList_Version_ID=v_PriceList_Version_Base_ID + AND EXISTS + (SELECT * FROM Temp_Selection s WHERE pp.M_Product_ID=s.Temp_Selection_ID) + AND pp.IsActive='Y'; + END IF; + GET DIAGNOSTICS rowcount:=ROW_COUNT; + v_Message:=v_Message || ', @Inserted@=' || rowcount; + -- ----------- + -- Calculation + -- ----------- + v_ResultStr:=v_ResultStr || ',Calc'; + UPDATE M_ProductPrice + SET PriceList=(( + CASE Cur_DiscountLine.List_Base WHEN 'S' THEN PriceStd WHEN 'X' THEN PriceLimit ELSE PriceList + END + ) + Cur_DiscountLine.List_AddAmt) *(1 - Cur_DiscountLine.List_Discount/100), + PriceStd=(( + CASE Cur_DiscountLine.Std_Base WHEN 'L' THEN PriceList WHEN 'X' THEN PriceLimit ELSE PriceStd + END + ) + Cur_DiscountLine.Std_AddAmt) *(1 - Cur_DiscountLine.Std_Discount/100), + PriceLimit=(( + CASE Cur_DiscountLine.Limit_Base WHEN 'L' THEN PriceList WHEN 'S' THEN PriceStd ELSE PriceLimit + END + ) + Cur_DiscountLine.Limit_AddAmt) *(1 - Cur_DiscountLine.Limit_Discount/100) + WHERE M_PriceList_Version_ID=v_PriceList_Version_ID + AND EXISTS + (SELECT * + FROM Temp_Selection s + WHERE s.Temp_Selection_ID=M_ProductPrice.M_Product_ID + ) + ; + -- -------- + -- Rounding (AD_Reference_ID='155') + -- -------- + v_ResultStr:=v_ResultStr || ',Round'; + UPDATE M_ProductPrice + SET PriceList=( + CASE Cur_DiscountLine.List_Rounding WHEN 'N' THEN PriceList WHEN '0' THEN ROUND(PriceList, 0) -- Even .00 + WHEN 'D' THEN ROUND(PriceList, 1) -- Dime .10 + WHEN 'D' THEN ROUND(PriceList, 1) -- Dime .10 + WHEN '2' THEN ROUND(PriceList, 2) -- 2 Deci + WHEN '3' THEN ROUND(PriceList, 3) -- 3 Deci + WHEN '4' THEN ROUND(PriceList, 4) -- 4 Deci + WHEN 'T' THEN ROUND(PriceList, -1) -- Ten 10.00 + WHEN '5' THEN ROUND(PriceList*20, 0) /20 -- Nickle .05 + WHEN 'Q' THEN ROUND(PriceList*4, 0) /4 -- Quarter .25 + ELSE ROUND(PriceList, v_StdPrecision) + END + ), -- Currency + PriceStd=( + CASE Cur_DiscountLine.Std_Rounding WHEN 'N' THEN PriceStd WHEN '0' THEN ROUND(PriceStd, 0) -- Even .00 + WHEN 'D' THEN ROUND(PriceStd, 1) -- Dime .10 + WHEN '2' THEN ROUND(PriceStd, 2) -- 2 Deci + WHEN '3' THEN ROUND(PriceStd, 3) -- 3 Deci + WHEN '4' THEN ROUND(PriceStd, 4) -- 4 Deci + WHEN 'T' THEN ROUND(PriceStd, -1) -- Ten 10.00 + WHEN '5' THEN ROUND(PriceStd*20, 0) /20 -- Nickle .05 + WHEN 'Q' THEN ROUND(PriceStd*4, 0) /4 -- Quarter .25 + ELSE ROUND(PriceStd, v_StdPrecision) + END + ), -- Currency + PriceLimit=( + CASE Cur_DiscountLine.Limit_Rounding WHEN 'N' THEN PriceLimit WHEN '0' THEN ROUND(PriceLimit, 0) -- Even .00 + WHEN 'D' THEN ROUND(PriceLimit, 1) -- Dime .10 + WHEN '2' THEN ROUND(PriceLimit, 2) -- 2 Deci + WHEN '3' THEN ROUND(PriceLimit, 3) -- 3 Deci + WHEN '4' THEN ROUND(PriceLimit, 4) -- 4 Deci + WHEN 'T' THEN ROUND(PriceLimit, -1) -- Ten 10.00 + WHEN '5' THEN ROUND(PriceLimit*20, 0) /20 -- Nickle .05 + WHEN 'Q' THEN ROUND(PriceLimit*4, 0) /4 -- Quarter .25 + ELSE ROUND(PriceLimit, v_StdPrecision) + END + ) -- Currency + WHERE M_PriceList_Version_ID=v_PriceList_Version_ID + AND EXISTS + (SELECT * + FROM Temp_Selection s + WHERE s.Temp_Selection_ID=M_ProductPrice.M_Product_ID + ) + ; + GET DIAGNOSTICS rowcount:=ROW_COUNT; + v_Message:=v_Message || ', @Updated@=' || rowcount; + -- Fixed Price overwrite + v_ResultStr:=v_ResultStr || ',Fix'; + UPDATE M_ProductPrice + SET PriceList=( + CASE Cur_DiscountLine.List_Base WHEN 'F' THEN Cur_DiscountLine.List_Fixed ELSE PriceList + END + ), + PriceStd=( + CASE Cur_DiscountLine.Std_Base WHEN 'F' THEN Cur_DiscountLine.Std_Fixed ELSE PriceStd + END + ), + PriceLimit=( + CASE Cur_DiscountLine.Limit_Base WHEN 'F' THEN Cur_DiscountLine.Limit_Fixed ELSE PriceLimit + END + ) + WHERE M_PriceList_Version_ID=v_PriceList_Version_ID + AND EXISTS + (SELECT * + FROM Temp_Selection s + WHERE s.Temp_Selection_ID=M_ProductPrice.M_Product_ID + ) + ; + -- + v_NextNo:=v_NextNo + 1; + v_Message:=''; + END LOOP; -- For all DiscountLines + -- Delete Temporary Selection + DELETE FROM Temp_Selection; + ---- <> + -- Update AD_PInstance + RAISE NOTICE '%',v_Message ; + RAISE NOTICE '%','Updating PInstance - Finished' ; + 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 +/-- END \ No newline at end of file