Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0005229 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Openbravo ERP] 00. Application dictionary | minor | always | 2008-09-24 12:56 | 2009-04-21 11:06 | |||
| Reporter | Dowid | View Status | public | |||||
| Assigned To | Dowid | |||||||
| Priority | normal | Resolution | fixed | Fixed in Version | ||||
| Status | closed | Fix in branch | pi | Fixed in SCM revision | 9767 | |||
| Projection | none | ETA | none | Target Version | ||||
| OS | Windows | Database | Oracle | Java version | ||||
| OS Version | SP2 | Database version | XE | Ant version | ||||
| Product Version | SCM revision | |||||||
| Review Assigned To | ||||||||
| Web browser | ||||||||
| Modules | Core | |||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0005229: Temporary tables T_Selection and T_Selection2 have been ported from v2.22 to 2.35 improperly | |||||||
| Description | Temporary tables T_Selection and T_Selection2 have been ported from v2.22 to 2.35 improperly. Temporary tables have been ported as regular tables. | |||||||
| Proposed Solution | move T_Selection and T_Selection2 tables into the prescript. | |||||||
| Tags | No tags attached. | |||||||
| Attached Files |  postscript.patch [^] (54,973 bytes) 2008-10-28 16:05 [Show Content] [Hide Content]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
+ --<<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;
+ --<<FINISH_PROCESS>>
+ -- 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
+ ---- <<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;
+ ---- <<FINISH_PROCESS>>
+ -- 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
| |||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
 Relationships |
|
Notes |
|
|
(0009362) Dowid (reporter) 2008-10-06 09:46 |
This bug leads these functions M_PRICELIST_CREATE(),M_PRODUCT_BOM_CHECK() to work incorrectly, if 2 users launch them simultaneously. |
|
(0009802) Dowid (reporter) 2008-10-28 16:05 |
2 Temporary tables (T_Selection, T_Selection2) are used by 2 functions (M_PRICELIST_CREATE(),M_PRODUCT_BOM_CHECK(). The DBSourceManager doesn't support the creation of TEMPORARY tables, however these tables can be created in the postscript. This would be fine, but Oracle and Postgre create TEMPORARY tables differently. Oracle creates the temporary tables during the creation of a "schema" and they exist in the schema with regular tables. Postgre on the other hand, creates these tables during the runtime. For these reasons we will have 2 function variants, one for postgre, and one for oracle. The proposed solution for this bug. 1. Delete C_Selection.xml, C_Selection2.xml, M_PRICELIST_CREATE.xml and M_PRODUCT_BOM_CHECK.xml 2. Modify postscript-Oracle.sql, postscript-PostgreSql.sql by using patch. Please Note: 1. I used prefix Temp_ for temporary tables 2. Postscript-PostgreSql.sql will be correct for postgre version 8.3. For version 8.2 and lower modifications are required. Every "select * from temp_table" will have to be replaced by "Execute 'Select * from Temp_table'". 3. There is another radical solution for this bug, however it requires to abandon the use of temporary tables. This solution would require to re-write the logic of functions that use temporary tables so that they do not use them. |
|
(0009831) Dowid (reporter) 2008-10-29 17:41 edited on: 2008-11-05 16:52 |
New improved solution for this bug. 1. Add C_CREATE_TEMPORAL_TABLES() function. This function should be empty for oracle and will create temporary tables for postgre 2. The same functions for postgre and Oracle (M_PRICELIST_CREATE(),M_PRODUCT_BOM_CHECK()) using C_CREATE_TEMPORAL_TABLES 3. modify (M_PRICELIST_CREATE(),M_PRODUCT_BOM_CHECK()) with EXECUTE for support Postgres 8.2 4. Add Creation Temporary tables into the prescript Oracle 5. Use the C_TEMP prefix for the functions and temporal tables 6. Add "C_CREATE_TEMPORARY_TABLES" into the OpenbravoExcludeFilter.getExcludedFunctions() and add "C_TEMP_SELECTION", "C_TEMP_SELECTION" into the OpenbravoExcludeFilter.getExcludedTables() |
|
(0010055) svnbot (reporter) 2008-11-06 18:47 |
Repository: openbravo Revision: 9767 Author: dowid Date: 2008-11-06 18:47:27 +0100 (Thu, 06 Nov 2008) Fixed bug 0005229: Temporary tables T_Selection and T_Selection2 have been ported from v2.22 to 2.35 improperly --- U trunk/src-db/database/model/functions/M_PRICELIST_CREATE.xml U trunk/src-db/database/model/functions/M_PRODUCT_BOM_CHECK.xml U trunk/src-db/database/model/prescript-Oracle.sql U trunk/src-db/database/model/prescript-PostgreSql.sql D trunk/src-db/database/model/tables/C_SELECTION.xml D trunk/src-db/database/model/tables/C_SELECTION2.xml U trunk/src-db/src/com/openbravo/db/OpenbravoExcludeFilter.java --- https://dev.openbravo.com/websvn/openbravo/?rev=9767&sc=1 [^] |
|
Notes |
|
Issue History |
|||
| Date Modified | Username | Field | Change |
| 2008-09-24 12:56 | Dowid | New Issue | |
| 2008-09-24 12:58 | Dowid | Relationship added | blocks 0005215 |
| 2008-09-26 10:07 | Dowid | Issue Monitored: Dowid | |
| 2008-10-06 09:46 | Dowid | Note Added: 0009362 | |
| 2008-10-24 12:15 | Dowid | Relationship deleted | blocks 0005215 |
| 2008-10-24 12:16 | Dowid | Relationship added | related to 0005215 |
| 2008-10-28 16:05 | Dowid | Note Added: 0009802 | |
| 2008-10-28 16:05 | Dowid | File Added: postscript.patch | |
| 2008-10-29 17:24 | cromero | Project | @4@ => Openbravo ERP |
| 2008-10-29 17:25 | cromero | Category | => 00. Application dictionary |
| 2008-10-29 17:41 | Dowid | Note Added: 0009831 | |
| 2008-10-30 14:31 | Dowid | Status | new => scheduled |
| 2008-10-30 14:31 | Dowid | Assigned To | => Dowid |
| 2008-10-30 14:31 | Dowid | fix_in_branch | => trunk |
| 2008-11-04 16:06 | Dowid | Note Edited: 0009831 | |
| 2008-11-05 16:52 | Dowid | Note Edited: 0009831 | |
| 2008-11-06 18:47 | svnbot | Checkin | |
| 2008-11-06 18:47 | svnbot | Note Added: 0010055 | |
| 2008-11-06 18:47 | svnbot | Status | scheduled => resolved |
| 2008-11-06 18:47 | svnbot | Resolution | open => fixed |
| 2008-11-06 18:47 | svnbot | svn_revision | => 9767 |
| 2008-12-02 13:02 | jaimetorre | sf_bug_id | 0 => 2377094 |
| 2009-01-10 18:09 | rafaroda | Relationship added | related to 0006521 |
| 2009-04-21 11:06 | psarobe | Status | resolved => closed |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |