c_acctschema_default_copy.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_AcctSchema_Default_Copy
(
	PInstance_ID    		IN NUMBER,
	Client_ID				IN NUMBER DEFAULT NULL	--	direct calls
)
/*************************************************************************
 * 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+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_AcctSchema_Default_Copy.sql,v 1.2 2001/11/26 05:25:18 jjanke Exp $
 ***
 * Title:	Copy Accounting Schema information to elements 
 * Description:
 *			Existing entities are overwritten) cascade all!
 ************************************************************************/
AS
	--	Logistice
	ResultStr						VARCHAR2(2000);
	Message							VARCHAR2(2000);
	Record_ID						NUMBER;
	--	Parameter
	CURSOR Cur_Parameter (PInstance NUMBER) IS
		SELECT i.Record_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=PInstance
		AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
	--
	AD_Client_ID					NUMBER;
	--
 	CURSOR	Cur_Acct (Client_ID NUMBER) 	IS
		SELECT 	*
		FROM	C_AcctSchema_Default
		WHERE	AD_Client_ID = Client_ID;
	--
	Updated							NUMBER := 0;
	Created							NUMBER := 0;
	Updated_Total					NUMBER := 0;
	Created_Total					NUMBER := 0;

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

	--	Get Parameters
	ResultStr := 'ReadingParameters';
	FOR p IN Cur_Parameter (PInstance_ID) LOOP
		Record_ID := p.Record_ID;
		IF (p.ParameterName = 'AD_Client_ID') THEN
 			AD_Client_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  AD_Client_ID=' || AD_Client_ID);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
	 	END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || Record_ID);

	--	Direct Calls
	IF (Client_ID IS NOT NULL) THEN
		AD_Client_ID := Client_ID;
		DBMS_OUTPUT.PUT_LINE('  AD_Client_ID=' || AD_Client_ID);
	END IF;


	/**
	 *	For all Accounting Schema
	 */
	FOR acct IN Cur_Acct (AD_Client_ID) LOOP
		--	Update existing Product Category
		UPDATE M_Product_Category_Acct pa
			SET P_Revenue_Acct=acct.P_Revenue_Acct, 
				P_Expense_Acct=acct.P_Expense_Acct, 
				P_Asset_Acct=acct.P_Asset_Acct, 
				P_CoGs_Acct=acct.P_CoGs_Acct, 
				P_PurchasePriceVariance_Acct=acct.P_PurchasePriceVariance_Acct,
				P_InvoicePriceVariance_Acct=acct.P_InvoicePriceVariance_Acct,
				P_TradeDiscountRec_Acct=acct.P_TradeDiscountRec_Acct,
				P_TradeDiscountGrant_Acct=acct.P_TradeDiscountGrant_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE pa.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM M_Product_Category p 
				WHERE p.M_Product_Category_ID=pa.M_Product_Category_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new Product Category
		INSERT INTO M_Product_Category_Acct
			(M_Product_Category_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			P_Revenue_Acct, P_Expense_Acct, P_Asset_Acct, P_CoGs_Acct, 
			P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct,
			P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct)
		SELECT p.M_Product_Category_ID, acct.C_AcctSchema_ID,
			p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct, 
			acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct,
			acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct
		FROM	M_Product_Category p
		WHERE 	AD_Client_ID=acct.AD_Client_ID
		  AND NOT EXISTS (SELECT * FROM M_Product_Category_Acct pa 
				WHERE pa.M_Product_Category_ID=p.M_Product_Category_ID 
					AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Product Category = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;
		

		--	Business Partner Group
		UPDATE C_BP_Group_Acct a
			SET C_Receivable_Acct=acct.C_Receivable_Acct,
				C_PrePayment_Acct=acct.C_PrePayment_Acct, 
				V_Liability_Acct=acct.V_Liability_Acct, 
				V_Liability_Services_Acct=acct.V_Liability_Services_Acct, 
				V_PrePayment_Acct=acct.V_PrePayment_Acct,
				PayDiscount_Exp_Acct=acct.PayDiscount_Exp_Acct, 
				PayDiscount_Rev_Acct=acct.PayDiscount_Rev_Acct, 
				WriteOff_Acct=acct.WriteOff_Acct,
				UnRealizedGain_Acct=acct.UnRealizedGain_Acct, 
				UnRealizedLoss_Acct=acct.UnRealizedLoss_Acct, 
				RealizedGain_Acct=acct.RealizedGain_Acct, 
				RealizedLoss_Acct=acct.RealizedLoss_Acct,
				NotInvoicedReceipts_Acct=acct.NotInvoicedReceipts_Acct, 
				UnEarnedRevenue_Acct=acct.UnEarnedRevenue_Acct, 
				NotInvoicedRevenue_Acct=acct.NotInvoicedRevenue_Acct, 
				NotInvoicedReceivables_Acct=acct.NotInvoicedReceivables_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_BP_Group_Acct x
				WHERE x.C_BP_Group_ID=a.C_BP_Group_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO C_BP_Group_Acct
			(C_BP_Group_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			C_Receivable_Acct, C_PrePayment_Acct, 
			V_Liability_Acct, V_Liability_Services_Acct, V_PrePayment_Acct, 
			PayDiscount_Exp_Acct, PayDiscount_Rev_Acct, WriteOff_Acct,
			UnRealizedGain_Acct, UnRealizedLoss_Acct, RealizedGain_Acct, RealizedLoss_Acct,
			NotInvoicedReceipts_Acct, UnEarnedRevenue_Acct, 
			NotInvoicedRevenue_Acct, NotInvoicedReceivables_Acct)
		SELECT x.C_BP_Group_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.C_Receivable_Acct, acct.C_PrePayment_Acct, 
			acct.V_Liability_Acct, acct.V_Liability_Services_Acct, acct.V_PrePayment_Acct, 
			acct.PayDiscount_Exp_Acct, acct.PayDiscount_Rev_Acct, acct.WriteOff_Acct,
			acct.UnRealizedGain_Acct, acct.UnRealizedLoss_Acct, acct.RealizedGain_Acct, acct.RealizedLoss_Acct,
			acct.NotInvoicedReceipts_Acct, acct.UnEarnedRevenue_Acct, 
			acct.NotInvoicedRevenue_Acct, acct.NotInvoicedReceivables_Acct
		FROM	C_BP_Group x
		WHERE 	AD_Client_ID=acct.AD_Client_ID 
		  AND NOT EXISTS (SELECT * FROM C_BP_Group_Acct a 
				WHERE a.C_BP_Group_ID=x.C_BP_Group_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Business Group = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;


		--	Business Partner - Employee
		UPDATE C_BP_Employee_Acct a
			SET E_Expense_Acct=acct.E_Expense_Acct,
				E_PrePayment_Acct=acct.E_PrePayment_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_BP_Employee_Acct x
				WHERE x.C_BPartner_ID=a.C_BPartner_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO C_BP_Employee_Acct
			(C_BPartner_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			E_Expense_Acct, E_PrePayment_Acct)
		SELECT x.C_BPartner_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.E_Expense_Acct, acct.E_PrePayment_Acct
		FROM	C_BPartner x
		WHERE 	AD_Client_ID=acct.AD_Client_ID 
		  AND NOT EXISTS (SELECT * FROM C_BP_Employee_Acct a 
				WHERE a.C_BPartner_ID=x.C_BPartner_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Employees = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;


		--	Warehouse
		UPDATE M_Warehouse_Acct a
			SET W_Inventory_Acct=acct.W_Inventory_Acct,
				W_Differences_Acct=acct.W_Differences_Acct,
				W_Revaluation_Acct=acct.W_Revaluation_Acct,
				W_InvActualAdjust_Acct=acct.W_InvActualAdjust_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM M_Warehouse_Acct x
				WHERE x.M_Warehouse_ID=a.M_Warehouse_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO M_Warehouse_Acct
			(M_Warehouse_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			W_Inventory_Acct, W_Differences_Acct, W_Revaluation_Acct, W_InvActualAdjust_Acct)
		SELECT x.M_Warehouse_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.W_Inventory_Acct, acct.W_Differences_Acct, acct.W_Revaluation_Acct, acct.W_InvActualAdjust_Acct
		FROM	M_Warehouse x
		WHERE 	AD_Client_ID=acct.AD_Client_ID
		  AND NOT EXISTS (SELECT * FROM M_Warehouse_Acct a 
				WHERE a.M_Warehouse_ID=x.M_Warehouse_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Warehouse = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;