c_bp_group_acct_copy.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_BP_Group_Acct_Copy
(
	PInstance_ID			IN NUMBER,
	BP_Group_ID				IN NUMBER	DEFAULT NULL	--	direct call
)
AS
/*************************************************************************
 * 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_BP_Group_Acct_Copy.sql,v 1.4 2002/09/27 04:54:37 jjanke Exp $
 ***
 * Title:	Copy Acct Info
 * Description:
 *		Copy Accounting Info to all Business Partner Account Info elements
 *		(existing entries are overwritten)
 ************************************************************************/
	--	Logistics
	ResultStr						VARCHAR2(2000);
	Message							VARCHAR2(2000);
	Record_ID						NUMBER;
	IsProcessing					CHAR(1);
	IsProcessed						CHAR(1);
	myResult						NUMBER := 1;	--	Success
	--	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;
	--
	C_BP_Group_ID					NUMBER;
	--	A Group can only have one client
 	CURSOR	Cur_Acct (BP_Group_ID NUMBER)	IS
		SELECT 	*
		FROM	C_BP_Group_Acct
		WHERE	C_BP_Group_ID = BP_Group_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 = 'C_BP_Group_ID') THEN
 			C_BP_Group_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  C_BP_Group_ID=' || C_BP_Group_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 Call
	IF (BP_Group_ID IS NOT NULL) THEN
 		C_BP_Group_ID := BP_Group_ID;
		DBMS_OUTPUT.PUT_LINE('  C_BP_Group_ID=' || C_BP_Group_ID);
	END IF;

	/**
	 *	For all Accounting Schema of the Group
	 */
	FOR acct IN Cur_Acct (C_BP_Group_ID) LOOP
		--	Update existing Customers
		UPDATE C_BP_Customer_Acct ca
			SET C_Receivable_Acct=acct.C_Receivable_Acct,
				C_PrePayment_Acct=acct.C_PrePayment_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE ca.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_BPartner p 
				WHERE p.C_BPartner_ID=ca.C_BPartner_ID AND p.C_BP_Group_ID=acct.C_BP_Group_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new Customer
		INSERT INTO C_BP_Customer_Acct
			(C_BPartner_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			C_Receivable_Acct, C_PrePayment_Acct)
		SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID,
			p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.C_Receivable_Acct, acct.C_PrePayment_Acct
		FROM	C_BPartner p
		WHERE	p.C_BP_Group_ID=acct.C_BP_Group_ID
			AND NOT EXISTS (SELECT * FROM C_BP_Customer_Acct ca 
				WHERE ca.C_BPartner_ID=p.C_BPartner_ID AND ca.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Customers = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;

		--	Update existing Vendors
		UPDATE C_BP_Vendor_Acct va
			SET V_Liability_Acct=acct.V_Liability_Acct,
				V_Liability_Services_Acct=acct.V_Liability_Services_Acct,
				V_PrePayment_Acct=acct.V_PrePayment_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE va.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_BPartner p 
				WHERE p.C_BPartner_ID=va.C_BPartner_ID AND p.C_BP_Group_ID=acct.C_BP_Group_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new Vendors
		INSERT INTO C_BP_Vendor_Acct
			(C_BPartner_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			V_Liability_Acct, V_Liability_Services_Acct, V_PrePayment_Acct)
		SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID,
			p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.V_Liability_Acct, acct.V_Liability_Services_Acct, acct.V_PrePayment_Acct
		FROM	C_BPartner p
		WHERE	p.C_BP_Group_ID=acct.C_BP_Group_ID
			AND NOT EXISTS (SELECT * FROM C_BP_Vendor_Acct va 
				WHERE va.C_BPartner_ID=p.C_BPartner_ID AND va.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Vendors = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;
	END LOOP;
	Message := '@Created@=' || Created_Total || ':2, @Updated@=' || Updated_Total || ':2';


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

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

END C_BP_Group_Acct_Copy;
/