fact_acct_reset.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE Fact_Acct_Reset
(
	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: Fact_Acct_Reset.sql,v 1.4 2003/01/27 06:22:11 jjanke Exp $
 ***
 * Title:	Reset Posting Records
 * Description:
 *			Delete Records in Fact_Acct or 
 *			Reset Posted 
 *			for AD_Client_ID and AD_Table_ID
 ************************************************************************/
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);
	p_AD_Table_ID					NUMBER(10);
	p_DeletePosting					VARCHAR(1) := 'N';
	--
	v_Updated						NUMBER(10) := 0;
	v_Deleted						NUMBER(10) := 0;
	v_Cmd							VARCHAR(255);


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 = 'AD_Table_ID') THEN
			p_AD_Table_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  AD_Table_ID=' || p_AD_Table_ID);
		ELSIF (p.ParameterName = 'DeletePosting') THEN
			p_DeletePosting := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  DeletePosting=' || p_DeletePosting);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
		END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);


	DECLARE
		CURSOR Cur_Clients IS
			SELECT	AD_Client_ID
			FROM		AD_Client
			WHERE	(p_AD_Client_ID IS NULL OR AD_Client_ID=p_AD_Client_ID)
			  AND	AD_Client_ID <> 0;
		CURSOR Cur_Tables IS
			SELECT	AD_Table_ID, TableName
			FROM		AD_Table t
			WHERE	(p_AD_Table_ID IS NULL OR t.AD_Table_ID=p_AD_Table_ID)
			  AND	EXISTS (SELECT * FROM AD_Column c
					WHERE t.AD_Table_ID=c.AD_Table_ID AND c.ColumnName='Posted')
			  AND	t.IsView='N';
	BEGIN
		FOR c IN Cur_Clients LOOP
			FOR t IN Cur_Tables LOOP
				DBMS_OUTPUT.PUT_LINE('AD_Client_ID=' || c.AD_Client_ID
					|| ', AD_Table_ID=' || t.AD_Table_ID || ' ' || t.TableName);
				--	Update Table
				v_ResultStr := 'ResetTable:' || t.TableName;
				IF (p_DeletePosting = 'Y') THEN
					v_Cmd := 'UPDATE ' || t.TableName 
						|| ' SET Posted=''N'', Processing=''N'' WHERE AD_Client_ID=' 
						|| c.AD_Client_ID 
						|| ' AND (Posted<>''N'' OR Posted IS NULL OR Processing<>''N'' OR Processing IS NULL)';
				--	DBMS_OUTPUT.PUT_LINE('  executing: ' || v_Cmd);
					EXECUTE IMMEDIATE v_Cmd;
					v_Updated := v_Updated + SQL%ROWCOUNT;
					DBMS_OUTPUT.PUT_LINE('  updated=' || SQL%ROWCOUNT);

					--	Delete Fact
					v_ResultStr := 'DeleteFact';
					DELETE	Fact_Acct
					WHERE	AD_Client_ID = c.AD_Client_ID
					  AND	AD_Table_ID = t.AD_Table_ID;
					v_Deleted := v_Deleted + SQL%ROWCOUNT;
					DBMS_OUTPUT.PUT_LINE('  deleted=' || SQL%ROWCOUNT);
				ELSE
					v_Cmd := 'UPDATE ' || t.TableName 
						|| ' SET Processing=''N'' WHERE AD_Client_ID='
						|| c.AD_Client_ID 
						|| ' AND Processing<>''N'' OR Processing IS NULL';
					EXECUTE IMMEDIATE v_Cmd;
					v_Updated := v_Updated + SQL%ROWCOUNT;
					DBMS_OUTPUT.PUT_LINE('  unlocked=' || SQL%ROWCOUNT);
					v_Cmd := 'UPDATE ' || t.TableName 
						|| ' SET Posted=''N'' WHERE AD_Client_ID='
						|| c.AD_Client_ID 
						|| ' AND Posted NOT IN (''Y'',''N'') AND Processed=''Y''';
					EXECUTE IMMEDIATE v_Cmd;
					v_Updated := v_Updated + SQL%ROWCOUNT;
					DBMS_OUTPUT.PUT_LINE('  updated=' || SQL%ROWCOUNT);
				END IF;
			END LOOP;
		END LOOP;
	END;

	--	Summary info
	v_Message := '@Updated@ = ' || v_Updated || ', @Deleted@ = ' || v_Deleted;


<<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 Fact_Acct_Reset;
/