c_cash_post.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_Cash_Post
(
	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: C_Cash_Post.sql,v 1.10 2003/03/17 20:32:25 jjanke Exp $
 ***
 * Title:	Post Cash Book Entry
 * Description:
 *	- Create Payment entry for Transfer
 *  - Create Allocation for Invoices (trigger updates SO_CreditUsed)
 *  - Update Balance and De-Activate
 ************************************************************************/
AS
	 --  Logistice
	v_ResultStr		VARCHAR2 (2000);
	v_Message		VARCHAR2 (2000);
	v_Record_ID		NUMBER(10);

	--  Parameter
	CURSOR Cur_Parameter (pp_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 = pp_PInstance 
		  AND	i.AD_PInstance_ID = p.AD_PInstance_ID (+)
		ORDER BY p.SeqNo;
	--  Parameter Variables

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;
	END LOOP;	 --  Get Parameter
	DBMS_OUTPUT.PUT_LINE ('  Record_ID=' || v_Record_ID);

	/**
	 *	Create Payment for Transfers
	 */
	DECLARE
		CURSOR Cur_Lines IS
			SELECT	c.AD_Client_ID, c.AD_Org_ID, c.UpdatedBy, c.Name, c.StatementDate,
				l.Line, l.C_BankAccount_ID, l.Amount, cb.C_Currency_ID,
				l.DiscountAmt, l.WriteOffAmt
			FROM	C_Cash c, C_CashLine l, C_CashBook cb
			WHERE	c.C_Cash_ID = v_Record_ID
			  AND	c.C_Cash_ID=l.C_Cash_ID
			  AND	c.C_CashBook_ID=cb.C_CashBook_ID
			  AND	l.CashType = 'T'
			  AND	c.Processed='N';
		v_NextNo				NUMBER(10);
		v_C_DocType_ID			NUMBER(10) := NULL;
	BEGIN
		FOR l IN Cur_Lines LOOP
			--	Get DocumentType
			IF (v_C_DocType_ID IS NULL) THEN
				--	We must have one ARReceipt DocType
				v_ResultStr := 'Getting DocType';
				SELECT	C_DocType_ID 
				  INTO	v_C_DocType_ID
				FROM	C_DocType 
				WHERE	AD_Client_ID=l.AD_Client_ID
				  AND	DocBaseType = 'ARR'
				  AND	ROWNUM=1;
			END IF;
			--
			v_ResultStr := 'Creating Payment';
			AD_Sequence_Next ('C_Payment', l.AD_Client_ID, v_NextNo);
			DBMS_OUTPUT.PUT_LINE ('  Creating Payment_ID=' || v_NextNo);
			INSERT INTO C_Payment (C_Payment_ID, DocumentNo,
				AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
				TrxType,TenderType,C_BankAccount_ID,
				C_DocType_ID, DocStatus,DocAction, DateTrx,
				C_Currency_ID, PayAmt, 
				DiscountAmt, WriteOffAmt)
			VALUES (v_NextNo, 'Tr ' || l.Name || ' - ' || l.Line,
				l.AD_Client_ID,l.AD_Org_ID,'Y',SysDate,l.UpdatedBy,SysDate,l.UpdatedBy,
				'X', 'X', l.C_BankAccount_ID,	--	Transfer/Transfer
				v_C_DocType_ID, 'DR', 'CO', l.StatementDate,
				l.C_Currency_ID, l.Amount*-1,	--	Transfer 
				0, 0);	
			--
			C_Payment_Post (NULL, v_NextNo);
		END LOOP;
	END;

	/**
	 *	Create Allocation for Invoices
	 */
	DECLARE
		CURSOR Cur_Lines IS
			SELECT	l.C_CashLine_ID, c.AD_Client_ID, c.AD_Org_ID, c.UpdatedBy, 
				c.Name, c.StatementDate,
				i.C_BPartner_ID,
				l.Line, l.C_Invoice_ID, l.Amount, cb.C_Currency_ID,
				l.DiscountAmt, l.WriteOffAmt
			FROM	C_Cash c, C_CashLine l, C_CashBook cb, C_Invoice i
			WHERE	c.C_Cash_ID = v_Record_ID
			  AND	c.C_Cash_ID=l.C_Cash_ID
			  AND	c.C_CashBook_ID=cb.C_CashBook_ID
			  AND	l.CashType = 'I' 
			  AND	l.C_Invoice_ID = i.C_Invoice_ID
			  AND	c.Processed='N';
		v_NextNo					NUMBER(10);
	BEGIN
		-- see also C_Payment_Post
		FOR l IN Cur_Lines LOOP
			--	Insert Allocation (Trigger updates Open Item Balance)
			v_ResultStr := 'CreateAllocation';
			AD_Sequence_Next ('C_Allocation', l.AD_Client_ID, v_NextNo);
			INSERT INTO C_Allocation (C_Allocation_ID,
				AD_Client_ID,AD_Org_ID, IsActive, Created,CreatedBy, Updated,UpdatedBy,
				AllocationNo, C_Currency_ID, DateTrx, IsManual,
				C_BPartner_ID, C_Order_ID,C_Invoice_ID, C_Payment_ID,C_CashLine_ID,
				Amount, DiscountAmt, WriteOffAmt, Processed,Posted)
			VALUES (v_NextNo,	--	add in Invoice Currency
				l.AD_Client_ID,l.AD_Org_ID, 'Y', SysDate,0, SysDate,0,
				C_Allocation_Seq.NextVal, l.C_Currency_ID, l.StatementDate, 'N',
				l.C_BPartner_ID, NULL, l.C_Invoice_ID, NULL, l.C_CashLine_ID,
				l.Amount, NVL(l.DiscountAmt,0), NVL(l.WriteOffAmt,0), 'Y','N');

			--	Check if invoice is paid
			v_ResultStr := 'UpdateInvoice';
			UPDATE	C_Invoice 
			  SET	IsPaid = DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, 1),
						GrandTotal,'Y','N')		--	hardcoded multiplier
			WHERE	C_Invoice_ID = l.C_Invoice_ID;
			--	Update Links
			UPDATE	C_Invoice 
			  SET	C_CashLine_ID = l.C_CashLine_ID
			WHERE	C_Invoice_ID = l.C_Invoice_ID;
			--	Update Link to Order
			UPDATE	C_Order o
			  SET	C_CashLine_ID = l.C_CashLine_ID
			WHERE EXISTS (SELECT * FROM C_Invoice i 
				WHERE o.C_Order_ID=i.C_Order_ID AND i.C_Invoice_ID=l.C_Invoice_ID);
			--
		END LOOP;
	END;	--	Invoice Allocation

	/**
	 *  Update Balances + De-Activate
	 */
	DECLARE
		v_Total					NUMBER := 0;
		v_Currency_ID			NUMBER(10) := NULL;
		--	CashBook
		v_CB_Currency_ID		NUMBER(10);
		v_CB_Date				DATE;
		--	Lines
		CURSOR Cur_Lines IS
			SELECT	*
			FROM		C_CashLine
			WHERE	C_Cash_ID = v_Record_ID;
	BEGIN
		v_ResultStr := 'GettingCashBookInfo';
		SELECT	cb.C_Currency_ID, c.DateAcct 
		  INTO	v_CB_Currency_ID, v_CB_Date
		FROM	C_CashBook cb, C_Cash c
		WHERE	cb.C_CashBook_ID=c.C_CashBook_ID
		  AND	c.C_Cash_ID=v_Record_ID;
		--	Calculate Tital
		FOR l IN Cur_Lines LOOP
			v_ResultStr := 'GettingTrxCurrency';
			--	Get BPartner_ID and Invoice Currency
			IF (l.C_Invoice_ID IS NOT NULL) THEN
				SELECT	C_Currency_ID
				  INTO	v_Currency_ID
				FROM	C_Invoice
				WHERE	C_Invoice_ID = l.C_Invoice_ID;
			END IF;
			--	Get BankAccount Currency
			IF (l.C_BankAccount_ID IS NOT NULL) THEN
				SELECT	C_Currency_ID 
				  INTO	v_Currency_ID
				FROM	C_BankAccount
				WHERE	C_BankAccount_ID=l.C_BankAccount_ID;
			END IF;
			--	Assume CashBook Currency for Charge
			IF (v_Currency_ID IS NULL) THEN
				v_Currency_ID := v_CB_Currency_ID;
			END IF;

			v_ResultStr := 'CalculatingSum';
			IF (v_Currency_ID <> v_CB_Currency_ID) THEN
				v_Total := v_Total + C_Currency_Convert(l.Amount, v_Currency_ID, v_CB_Currency_ID, v_CB_Date, null, 
					l.AD_Client_ID, l.AD_Org_ID);
			ELSE
				v_Total := v_Total + l.Amount;
			END IF;			
		END LOOP;
		--
		DBMS_OUTPUT.PUT_LINE ('CashJournal Complete - Total=' || v_Total);
		v_ResultStr := 'UpdatingRecord';
		UPDATE	C_Cash
		  SET	StatementDifference = v_Total,
				EndingBalance = BeginningBalance + v_Total,
				Processed = 'Y',
				Updated = SYSDATE
		WHERE	C_Cash_ID = v_Record_ID;
	 END;

<<FINISH_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE ('Updating PInstance - Finished ' || v_Message);

	UPDATE	AD_PInstance
	  SET	Updated = SYSDATE,
			IsProcessing = 'N',
			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 C_Cash_Post;
/