c_acctschema_default_copy.sql

Java写的ERP系统

		--	Project
		UPDATE C_Project_Acct a
			SET PJ_Asset_Acct=acct.PJ_Asset_Acct, 
				PJ_WIP_Acct=acct.PJ_Asset_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_Project_Acct x
				WHERE x.C_Project_ID=a.C_Project_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO C_Project_Acct
			(C_Project_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			PJ_Asset_Acct, PJ_WIP_Acct)
		SELECT x.C_Project_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.PJ_Asset_Acct, acct.PJ_WIP_Acct
		FROM	C_Project x
		WHERE 	AD_Client_ID=acct.AD_Client_ID 
		  AND NOT EXISTS (SELECT * FROM C_Project_Acct a 
				WHERE a.C_Project_ID=x.C_Project_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Project = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;


		--	Tax
		UPDATE C_Tax_Acct a
			SET T_Due_Acct=acct.T_Due_Acct,
				T_Liability_Acct=acct.T_Liability_Acct,
				T_Credit_Acct=acct.T_Credit_Acct,
				T_Receivables_Acct=acct.T_Receivables_Acct,
				T_Expense_Acct=acct.T_Expense_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_Tax_Acct x
				WHERE x.C_Tax_ID=a.C_Tax_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO C_Tax_Acct
			(C_Tax_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			T_Due_Acct, T_Liability_Acct, T_Credit_Acct, T_Receivables_Acct, T_Expense_Acct)
		SELECT x.C_Tax_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.T_Due_Acct, acct.T_Liability_Acct, acct.T_Credit_Acct, acct.T_Receivables_Acct, acct.T_Expense_Acct
		FROM	C_Tax x
		WHERE 	AD_Client_ID=acct.AD_Client_ID 
		  AND NOT EXISTS (SELECT * FROM C_Tax_Acct a 
				WHERE a.C_Tax_ID=x.C_Tax_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Tax = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;


		--	BankAccount
		UPDATE C_BankAccount_Acct a
			SET B_InTransit_Acct=acct.B_InTransit_Acct, 
				B_Asset_Acct=acct.B_Asset_Acct, 
				B_Expense_Acct=acct.B_Expense_Acct, 
				B_InterestRev_Acct=acct.B_InterestRev_Acct, 
				B_InterestExp_Acct=acct.B_InterestExp_Acct,
				B_UnIdentified_Acct=acct.B_UnIdentified_Acct, 
				B_UnAllocatedCash_Acct=acct.B_UnAllocatedCash_Acct, 
				B_PaymentSelect_Acct=acct.B_PaymentSelect_Acct, 
				B_SettlementGain_Acct=acct.B_SettlementGain_Acct, 
				B_SettlementLoss_Acct=acct.B_SettlementLoss_Acct,
				B_RevaluationGain_Acct=acct.B_RevaluationGain_Acct, 
				B_RevaluationLoss_Acct=acct.B_RevaluationLoss_Acct, 
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_BankAccount_Acct x
				WHERE x.C_BankAccount_ID=a.C_BankAccount_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO C_BankAccount_Acct
			(C_BankAccount_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			B_InTransit_Acct, B_Asset_Acct, B_Expense_Acct, B_InterestRev_Acct, B_InterestExp_Acct,
			B_UnIdentified_Acct, B_UnAllocatedCash_Acct, B_PaymentSelect_Acct,
			B_SettlementGain_Acct, B_SettlementLoss_Acct,
			B_RevaluationGain_Acct, B_RevaluationLoss_Acct)
		SELECT x.C_BankAccount_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.B_InTransit_Acct, acct.B_Asset_Acct, acct.B_Expense_Acct, acct.B_InterestRev_Acct, acct.B_InterestExp_Acct,
			acct.B_UnIdentified_Acct, acct.B_UnAllocatedCash_Acct, acct.B_PaymentSelect_Acct,
			acct.B_SettlementGain_Acct, acct.B_SettlementLoss_Acct,
			acct.B_RevaluationGain_Acct, acct.B_RevaluationLoss_Acct
		FROM	C_BankAccount x
		WHERE 	AD_Client_ID=acct.AD_Client_ID 
		  AND NOT EXISTS (SELECT * FROM C_BankAccount_Acct a 
				WHERE a.C_BankAccount_ID=x.C_BankAccount_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Bank Account = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;


		--	Withholding
		UPDATE C_Withholding_Acct a
			SET Withholding_Acct=acct.Withholding_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_Withholding_Acct x
				WHERE x.C_Withholding_ID=a.C_Withholding_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO C_Withholding_Acct
			(C_Withholding_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			Withholding_Acct)
		SELECT x.C_Withholding_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.Withholding_Acct
		FROM	C_Withholding x
		WHERE 	AD_Client_ID=acct.AD_Client_ID
	 	  AND NOT EXISTS (SELECT * FROM C_Withholding_Acct a 
				WHERE a.C_Withholding_ID=x.C_Withholding_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Withholding = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;

		
		--	Charge
		UPDATE C_Charge_Acct a
			SET Ch_Expense_Acct=acct.Ch_Expense_Acct,
				Ch_Revenue_Acct=acct.Ch_Revenue_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_Charge_Acct x
				WHERE x.C_Charge_ID=a.C_Charge_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO C_Charge_Acct
			(C_Charge_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			Ch_Expense_Acct, Ch_Revenue_Acct)
		SELECT x.C_Charge_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.Ch_Expense_Acct, acct.Ch_Revenue_Acct
		FROM	C_Charge x
		WHERE 	AD_Client_ID=acct.AD_Client_ID
		  AND NOT EXISTS (SELECT * FROM C_Charge_Acct a 
				WHERE a.C_Charge_ID=x.C_Charge_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Charge = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;


		--	Cashbook
		UPDATE C_Cashbook_Acct a
			SET CB_Asset_Acct=acct.CB_Asset_Acct,
				CB_Differences_Acct=acct.CB_Differences_Acct,
				CB_CashTransfer_Acct=acct.CB_CashTransfer_Acct,
				CB_Expense_Acct=acct.CB_Expense_Acct,
				CB_Receipt_Acct=acct.CB_Receipt_Acct,
				Updated=SysDate,
				UpdatedBy=0
		WHERE a.C_AcctSchema_ID=acct.C_AcctSchema_ID 
			AND EXISTS (SELECT * FROM C_Cashbook_Acct x
				WHERE x.C_Cashbook_ID=a.C_Cashbook_ID);
		Updated := SQL%ROWCOUNT;
		--	Insert new 
		INSERT INTO C_Cashbook_Acct
			(C_Cashbook_ID, C_AcctSchema_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
			CB_Asset_Acct, CB_Differences_Acct, CB_CashTransfer_Acct,
			CB_Expense_Acct, CB_Receipt_Acct)
		SELECT x.C_Cashbook_ID, acct.C_AcctSchema_ID,
			x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			acct.CB_Asset_Acct, acct.CB_Differences_Acct, acct.CB_CashTransfer_Acct,
			acct.CB_Expense_Acct, acct.CB_Receipt_Acct
		FROM	C_Cashbook x
		WHERE 	AD_Client_ID=acct.AD_Client_ID 
		  AND NOT EXISTS (SELECT * FROM C_Cashbook_Acct a 
				WHERE a.C_Cashbook_ID=x.C_Cashbook_ID 
					AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID);
		Created := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('Cashbook = ' || Updated || ' / ' || Created);
		Updated_Total := Updated_Total + Updated;
		Created_Total := Created_Total + Created;
	END LOOP;
	Message := '@Created@=' || Created_Total || ', @Updated@=' || Updated_Total;


<<FINISH_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || Message);
    UPDATE  AD_PInstance
    SET Updated = SysDate,
        IsProcessing = 'N',
        Result = 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_AcctSchema_Default_Copy;
/