c_invoice_acct.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_Invoice_AcctGen
(
	PInstance_ID		IN NUMBER,
	Invoice_ID			IN NUMBER DEFAULT NULL
)
/******************************************************************************
 * ** Compiere Product **             Copyright (c) 1999-2001 Accorto, Inc. USA
 * Open  Source  Software        Provided "AS IS" without warranty or liability
 * When you use any parts (changed or unchanged), add  "Powered by Compiere" to
 * your product name;  See license details http://www.compiere.org/license.html
 ******************************************************************************
 *	Create Account postings for Invoices
 */
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;
	--
	CURSOR Cur_AcctSchema (Client NUMBER) IS
		SELECT 	* 
		FROM 	C_AcctSchema
		WHERE C_AcctSchema_ID = (SELECT C_AcctSchema1_ID
			FROM AD_ClientInfo WHERE AD_Client_ID=Client)
		OR C_AcctSchema_ID = (SELECT C_AcctSchema2_ID
			FROM AD_ClientInfo WHERE Acct2_Active='Y' AND AD_Client_ID=Client)
		OR C_AcctSchema_ID = (SELECT C_AcctSchema3_ID
			FROM AD_ClientInfo WHERE Acct3_Active='Y' AND AD_Client_ID=Client);

	CURSOR Cur_Invoice (ID NUMBER) IS
		SELECT	*
		FROM	C_Invoice
		WHERE	IsTransferred = 'N'
		  AND	(ID IS NULL OR C_Invoice_ID = ID)
		FOR UPDATE;
	CURSOR Cur_InvoiceLine (ID NUMBER) IS
		SELECT	*
		FROM	C_InvoiceLine
		WHERE	C_Invoice_ID = ID;
	CURSOR Cur_InvoiceTax (ID NUMBER) IS
		SELECT	*
		FROM	C_InvoiceTax
		WHERE	C_Invoice_ID = ID;

BEGIN
	IF (PInstance_ID IS NOT NULL) THEN
	    --  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;
		END LOOP;	--	Get Parameter
	ELSE
		Record_ID := Invoice_ID;
	END IF;
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || Record_ID);

	--	Process all invoices
	FOR i IN Cur_Invoice (Record_ID) LOOP
		--	for all accounting schema
		FOR a IN Cur_AcctSchema (i.AD_Client_ID) LOOP
			
			NULL;
	  	END LOOP;	--	For all accounting schema
	END LOOP;	--	Process all invoices


<<FINISH_PROCESS>>
	IF (PInstance_ID IS NOT NULL) THEN
		--  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;
	END IF;
    RETURN;

EXCEPTION
    WHEN  OTHERS THEN
		ResultStr := ResultStr || ': ' || SQLERRM || ' - ' || Message;
		DBMS_OUTPUT.PUT_LINE(ResultStr);
		ROLLBACK;
		IF (PInstance_ID IS NOT NULL) THEN
        	UPDATE  AD_PInstance
	        SET Updated = SysDate,
    	        IsProcessing = 'N',
        	    Result = 0,             -- failure
            	ErrorMsg = ResultStr
	        WHERE   AD_PInstance_ID=PInstance_ID;
	        COMMIT;
		ELSE
			DECLARE
				v_Code 	NUMBER 			:= SQLCode;
			BEGIN
				INSERT INTO DBA_ErrorLog (DBA_ErrorLog_ID,Created,Code,Msg,Info)
				VALUES (DBA_ErrorLog_Seq.NextVal,SysDate,v_Code,ResultStr,'C_Invoice_Acct');
			END;
		END IF;
        RETURN;
END C_Invoice_AcctGen;
/