m_product_costingupdate.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE M_Product_CostingUpdate
(
	p_PInstance_ID    		IN NUMBER
)
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CPM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: M_Product_CostingUpdate.sql,v 1.3 2002/10/21 04:49:46 jjanke Exp $
 ***
 * Title:	Create the (new) costing information
 * Description:
 *			- (optionally) update FutureCostPrice according to Parameter
 *			- (optionally) set CostStandard to FutureCostPrice
 *			- set CurrentCostPrice to cost depending on primary AcctSchema
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr						VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Result						NUMBER := 1;	-- 0=failure
	v_Record_ID						NUMBER;
	v_AD_User_ID					NUMBER;
	--	Parameter
	CURSOR Cur_Parameter (pp_PInstance NUMBER) IS
		SELECT i.Record_ID, i.AD_User_ID,
			p.ParameterName, p.P_String, p.P_Number, p.P_Date
		FROM AD_PInstance i, AD_PInstance_Para p
		WHERE i.AD_PInstance_ID=pp_PInstance
		AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
	--	Parameter Variables
	p_AD_Client_ID				NUMBER(10) := 0;
	p_M_Product_Category_ID		NUMBER(10) := 0;
	p_SetFutureCostTo			CHAR(2);
	p_M_PriceList_Version_ID	NUMBER(10) := 0;
	p_SetStandardCost			CHAR(1);
	--
	v_CostingMethod				CHAR(1);
	v_No						NUMBER := 0;
	v_No1						NUMBER := 0;

BEGIN
    --  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID);
	v_ResultStr := 'PInstanceNotFound';
    UPDATE AD_PInstance
    SET Created = SysDate,
        IsProcessing = 'Y'
    WHERE AD_PInstance_ID=p_PInstance_ID;
    COMMIT;

	--	Get Parameters
	v_ResultStr := 'ReadingParameters';
	FOR p IN Cur_Parameter (p_PInstance_ID) LOOP
		v_Record_ID := p.Record_ID;
		v_AD_User_ID := p.AD_User_ID;
		IF (p.ParameterName = 'AD_Client_ID') THEN
			p_AD_Client_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  AD_Client_ID=' || p_AD_Client_ID);
		ELSIF (p.ParameterName = 'M_Product_Category_ID') THEN
			p_M_Product_Category_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  M_Product_Category_ID=' || p_M_Product_Category_ID);
		ELSIF (p.ParameterName = 'SetFutureCostTo') THEN
 			p_SetFutureCostTo := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  SetFutureCostTo=' || p_SetFutureCostTo);
		ELSIF (p.ParameterName = 'M_PriceList_Version_ID') THEN
			p_M_PriceList_Version_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  M_PriceList_Version_ID=' || p_M_PriceList_Version_ID);
		ELSIF (p.ParameterName = 'SetStandardCost') THEN
			p_SetStandardCost := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  SetStandardCost=' || p_SetStandardCost);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
	 	END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);


	--	==========	(1) Set Future Cost To	==========
	--	S 	- Standard Cost
	IF (p_SetFutureCostTo = 'S') THEN
		DBMS_OUTPUT.PUT_LINE('Set to Standard Cost');
		UPDATE 	M_Product_Costing pc
		  SET 	FutureCostPrice = CostStandard
	   	WHERE	AD_Client_ID=p_AD_Client_ID
		  AND	(p_M_Product_Category_ID IS NULL OR EXISTS (SELECT * FROM M_Product p
		  			WHERE p.M_Product_Category_ID=p_M_Product_Category_ID 
					  AND p.M_Product_ID=pc.M_Product_ID));
		v_No := SQL%ROWCOUNT;

	--	DP	- Difference PO
	ELSIF (p_SetFutureCostTo = 'DP') THEN
		DBMS_OUTPUT.PUT_LINE('Set to Difference PO');
		UPDATE 	M_Product_Costing pc
		  SET 	FutureCostPrice = CostStandard + (CostStandardPOAmt/CostStandardPOQty)
	   	WHERE	CostStandardPOQty <> 0 AND CostStandardPOAmt <> 0
		  AND	AD_Client_ID=p_AD_Client_ID
		  AND	(p_M_Product_Category_ID IS NULL OR EXISTS (SELECT * FROM M_Product p
		  			WHERE p.M_Product_Category_ID=p_M_Product_Category_ID 
					  AND p.M_Product_ID=pc.M_Product_ID));
		v_No := SQL%ROWCOUNT;

	--	DI	- Difference Invoice
	ELSIF (p_SetFutureCostTo = 'DI') THEN
		DBMS_OUTPUT.PUT_LINE('Set to Difference Inv');
		UPDATE 	M_Product_Costing pc
		  SET 	FutureCostPrice = CostStandard + (CostStandardCumAmt/CostStandardCumQty)
	   	WHERE	CostStandardCumQty <> 0 AND CostStandardCumAmt <> 0
		  AND	AD_Client_ID=p_AD_Client_ID
		  AND	(p_M_Product_Category_ID IS NULL OR EXISTS (SELECT * FROM M_Product p
		  			WHERE p.M_Product_Category_ID=p_M_Product_Category_ID 
					  AND p.M_Product_ID=pc.M_Product_ID));
		v_No := SQL%ROWCOUNT;

	--	P	- Last PO Price
	ELSIF (p_SetFutureCostTo = 'P') THEN
		DBMS_OUTPUT.PUT_LINE('Set to PO Price');
		UPDATE 	M_Product_Costing pc
		  SET 	FutureCostPrice = PriceLastPO
	   	WHERE	PriceLastPO <> 0
		  AND	AD_Client_ID=p_AD_Client_ID
		  AND	(p_M_Product_Category_ID IS NULL OR EXISTS (SELECT * FROM M_Product p
		  			WHERE p.M_Product_Category_ID=p_M_Product_Category_ID 
					  AND p.M_Product_ID=pc.M_Product_ID));
		v_No := SQL%ROWCOUNT;

	--	L	- Last Inv Price
	ELSIF (p_SetFutureCostTo = 'I') THEN
		DBMS_OUTPUT.PUT_LINE('Set to Inv Price');
		UPDATE 	M_Product_Costing pc
		  SET 	FutureCostPrice = PriceLastInv
	   	WHERE	PriceLastInv <> 0
		  AND	AD_Client_ID=p_AD_Client_ID
		  AND	(p_M_Product_Category_ID IS NULL OR EXISTS (SELECT * FROM M_Product p
		  			WHERE p.M_Product_Category_ID=p_M_Product_Category_ID 
					  AND p.M_Product_ID=pc.M_Product_ID));
		v_No := SQL%ROWCOUNT;

	--	A	- Average Cost
	ELSIF (p_SetFutureCostTo = 'A') THEN
		DBMS_OUTPUT.PUT_LINE('Set to Average Cost');
		UPDATE 	M_Product_Costing pc
		  SET 	FutureCostPrice = CostAverage
	   	WHERE	CostAverage <> 0
		  AND	AD_Client_ID=p_AD_Client_ID
		  AND	(p_M_Product_Category_ID IS NULL OR EXISTS (SELECT * FROM M_Product p
		  			WHERE p.M_Product_Category_ID=p_M_Product_Category_ID 
					  AND p.M_Product_ID=pc.M_Product_ID));
		v_No := SQL%ROWCOUNT;

	--	LL	- Price List - Limit
	ELSIF (p_SetFutureCostTo = 'LL' AND p_M_PriceList_Version_ID > 0) THEN
		DBMS_OUTPUT.PUT_LINE('Set to PriceList ' || p_M_PriceList_Version_ID);
		UPDATE 	M_Product_Costing pc
		  SET 	FutureCostPrice = 
				(SELECT pp.PriceLimit FROM M_ProductPrice pp 
				WHERE pp.M_PriceList_Version_ID=p_M_PriceList_Version_ID
				  AND pp.M_Product_ID=pc.M_Product_ID) 
/**		  SET 	FutureCostPrice = C_Currency_Convert (
		  	--	Amount
			(SELECT pp.PriceLimit FROM M_ProductPrice pp 
			WHERE pp.M_PriceList_Version_ID=11
			  AND pp.M_Product_ID=pc.M_Product_ID),
			--	Cur From
			(SELECT C_Currency_ID FROM M_PriceList pl, M_PriceList_Version pv 
			WHERE pv.M_PriceList_ID=pl.M_PriceList_ID 
			  AND pv.M_PriceList_Version_ID=11),
			--	Cur To 
			(SELECT a.C_Currency_ID FROM C_AcctSchema a WHERE a.C_AcctSchema_ID=pc.C_AcctSchema_ID))
**/
	   	WHERE	AD_Client_ID=p_AD_Client_ID 
		  -- we have a price
		  AND EXISTS (SELECT * FROM M_ProductPrice pp
			WHERE pp.M_PriceList_Version_ID=p_M_PriceList_Version_ID
			  AND pp.M_Product_ID=pc.M_Product_ID)
		  -- and the same currency
		  AND EXISTS (SELECT * FROM C_AcctSchema a, M_PriceList pl, M_PriceList_Version pv
		  	WHERE a.C_AcctSchema_ID=pc.C_AcctSchema_ID
			  AND pv.M_PriceList_Version_ID=p_M_PriceList_Version_ID
			  AND pv.M_PriceList_ID=pl.M_PriceList_ID
			  AND pl.C_Currency_ID=a.C_Currency_ID)
		  AND	(p_M_Product_Category_ID IS NULL OR EXISTS (SELECT * FROM M_Product p
		  			WHERE p.M_Product_Category_ID=p_M_Product_Category_ID 
					  AND p.M_Product_ID=pc.M_Product_ID));
		v_No := SQL%ROWCOUNT;
	ELSE
		DBMS_OUTPUT.PUT_LINE('SetFutureCostTo=' || p_SetFutureCostTo);
	END IF;
	DBMS_OUTPUT.PUT_LINE(' - Updated: ' || v_No);

	--	==========	(2) SetStandardCost	==========
	IF (p_SetStandardCost = 'Y') THEN
		DBMS_OUTPUT.PUT_LINE('Set Standard Cost');
		UPDATE 	M_Product_Costing pc
		  SET 	CostStandard = FutureCostPrice
	   	WHERE	AD_Client_ID=p_AD_Client_ID
		  AND	(p_M_Product_Category_ID IS NULL OR EXISTS (SELECT * FROM M_Product p
		  			WHERE p.M_Product_Category_ID=p_M_Product_Category_ID 
					  AND p.M_Product_ID=pc.M_Product_ID));
		v_No1 := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE(' - Updated: ' || v_No1);
  	END IF;

	v_Message := '@Updated@: ' || v_No || '/' || v_No1;


	--	==========	(3) Update CurrentCostPrice depending on Costing Method ==========
	SELECT	a.CostingMethod
	  INTO	v_CostingMethod
	FROM	C_AcctSchema a, AD_ClientInfo ci
	WHERE	a.C_AcctSchema_ID=ci.C_AcctSchema1_ID
	  AND	ci.AD_Client_ID=p_AD_Client_ID;
	--	(A)verage (S)tandard
	DBMS_OUTPUT.PUT_LINE('Update Current Cost ' || v_CostingMethod);
	UPDATE 	M_Product_Costing pc
	  SET 	CurrentCostPrice = DECODE (v_CostingMethod, 'A', CostAverage, CostStandard)
   	WHERE	AD_Client_ID=p_AD_Client_ID;
	DBMS_OUTPUT.PUT_LINE(' - Updated: ' || SQL%ROWCOUNT);


<<FINISH_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
    UPDATE  AD_PInstance
    SET Updated = SysDate,
        IsProcessing = 'N',
        Result = v_Result,			-- 1=success
        ErrorMsg = v_Message
    WHERE   AD_PInstance_ID=p_PInstance_ID;
    COMMIT;
    RETURN;

EXCEPTION
    WHEN  OTHERS THEN
		v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
		DBMS_OUTPUT.PUT_LINE(v_ResultStr);
		ROLLBACK;
        UPDATE  AD_PInstance
        SET Updated = SysDate,
            IsProcessing = 'N',
            Result = 0,             -- failure
            ErrorMsg = v_ResultStr
        WHERE   AD_PInstance_ID=p_PInstance_ID;
        COMMIT;
        RETURN;

END M_Product_CostingUpdate;
/