c_payment_post.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_Payment_Post
(
	p_PInstance_ID			IN NUMBER,
	p_Payment_ID			IN NUMBER	-- DEFAULT NULL	
)
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+CPM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_Payment_Post.sql,v 1.15 2003/04/24 06:11:50 jjanke Exp $
 ***
 * Title:	Post Payments
 * Description:
 *	- Unlock XL
 * 	- CLose if COmplete
 *	- VPid if not COmplete
 *	- ReverseCorrect
 *		- Check if COmplete
 *		- Create reverse payment and post
 *	- COmplete 
 *		- Check online status
 *		- Create allocation
 *		- Update Invoice/Order
 *		- Set Status COmplete
 ************************************************************************/
	--	Logistics
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Record_ID					NUMBER(10);
	v_NextNo						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
	--	Record variables
	v_Processing					CHAR(1);
	v_Processed					CHAR(1);
	v_DocAction					CHAR(2);
	v_DocStatus					CHAR(2);
	v_BPartner_ID					NUMBER(10);
	v_PayAmt						NUMBER;
	v_DiscountAmt					NUMBER;
	v_WriteOffAmt					NUMBER;
	v_OverUnderAmt					C_Payment.OverUnderAmt%TYPE;
	v_AllocationAmt				C_Allocation.Amount%TYPE;
	v_DateTrx						DATE;
	v_Currency_ID					NUMBER(10);
	v_Client_ID					NUMBER(10);
	v_Org_ID						NUMBER(10);
	v_Invoice_ID					NUMBER(10);
	v_IsOnline					CHAR(1);
	v_IsApproved					CHAR(1);
	v_IsReceipt					CHAR(1);
	v_DocType_ID					NUMBER (10);
	v_Result						NVARCHAR2(40);
	--
BEGIN
	IF (p_PInstance_ID IS NOT NULL) THEN
		--  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
	ELSIF (p_Payment_ID IS NOT NULL) THEN
		v_Record_ID := p_Payment_ID;
	ELSE
		v_Message := '@NoParameters@';
		GOTO FINISH_PROCESS;		
	END IF;
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);

	/**
	 *	Read Payment
	 */
	v_ResultStr := 'ReadingPayment';
	SELECT	Processing, Processed, DocAction, DocStatus, C_BPartner_ID, 
		PayAmt, DiscountAmt, WriteOffAmt, OverUnderAmt, DateTrx, C_Currency_ID,
		AD_Client_ID, AD_Org_ID, C_Invoice_ID, C_DocType_ID,
		IsOnline, IsApproved, R_Result
	  INTO	v_Processing, v_Processed, v_DocAction, v_DocStatus, v_BPartner_ID, 
		v_PayAmt, v_DiscountAmt, v_WriteOffAmt, v_OverUnderAmt, v_DateTrx, v_Currency_ID,
		v_Client_ID, v_Org_ID, v_Invoice_ID, v_DocType_ID,
		v_IsOnline, v_IsApproved, v_Result
	FROM	C_Payment
	WHERE	C_Payment_ID = v_Record_ID
	FOR UPDATE;
	--	Handle potential Null -> 0
	IF (v_DiscountAmt IS NULL) THEN
		v_DiscountAmt := 0;
	END IF;
	IF (v_WriteOffAmt IS NULL) THEN
		v_WriteOffAmt := 0;
	END IF;
	IF (v_OverUnderAmt IS NULL) THEN
		v_OverUnderAmt := 0;
	END IF;

	DBMS_OUTPUT.PUT_LINE('DocAction=' || v_DocAction || ', DocStatus=' || v_DocStatus);

	/**
	 *	Order Closed, Voided, Reversed - No action possible
	 */
	IF (v_DocStatus IN ('CL', 'VO', 'RE')) THEN
		v_Message := '@AlreadyPosted@';
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Unlock / Other process locked
	 */
	IF (v_DocAction = 'XL') THEN
		UPDATE	C_Payment
		  SET	Processing = 'N',
				DocAction = '--',
				Updated = SysDate
		WHERE	C_Payment_ID = v_Record_ID;
		GOTO FINISH_PROCESS;
	END IF;
	IF (v_Processing = 'Y') THEN
		v_Message := '@OtherProcessActive@';
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	CLose (only completed trx)
	 */
	IF (v_DocAction = 'CL') THEN
		IF (v_DocStatus <> 'CO') THEN
			v_Message := '@ActionNotAllowedHere@';
		ELSE
			UPDATE	C_Payment
			  SET	DocStatus = 'CL',	--	CLosed
					DocAction = '--',
					Processing = 'N',
					Processed = 'Y',
					Updated = SysDate
			WHERE	C_Payment_ID = v_Record_ID;
		END IF;
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Void (not completed trx)
	 */
	IF (v_DocAction = 'VO') THEN
		IF (v_DocStatus = 'CO') THEN
			v_Message := '@ActionNotAllowedHere@';
		ELSE
			UPDATE	C_Payment
			  SET	DocStatus = 'VO',	--	Void
					PayAmt = 0,
					DocAction = '--',
					Processing = 'N',
					Processed = 'Y',
					Updated = SysDate
			WHERE	C_Payment_ID = v_Record_ID;
		END IF;
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Reverse Correct
	 */
	IF (v_DocAction = 'RC') THEN 
		--	Must be COmplete
		IF (v_DocStatus <> 'CO') THEN
			v_Message := '@ActionNotAllowedHere@';
			GOTO FINISH_PROCESS;
		END IF;
		/**
		 *	Create negative payment and allocation and post
		 */
		DECLARE
			v_NextNoPayment					NUMBER(10);
		BEGIN
			--	Create Reversal
			v_ResultStr := 'CreateReversal';
			AD_Sequence_Next ('C_Payment', v_Client_ID, v_NextNoPayment);
			DBMS_OUTPUT.PUT_LINE('Reverse Payment ID=' || v_NextNoPayment);
			INSERT INTO C_Payment
				(C_Payment_ID, DocumentNo, DocStatus,DocAction,
				AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
				TrxType, C_BankAccount_ID, TenderType,
				CreditCardType,CreditCardNumber,CreditCardVV,CreditCardExpMM,CreditCardExpYY,
				MICR,RoutingNo,AccountNo,CheckNo,
				A_Name,A_Street,A_City,A_State,A_ZIP,A_Ident_DL,A_Ident_SSN,A_EMail,
				VoiceAuthCode,Orig_TrxID,
				PONum, DiscountAmt,TaxAmt,
				IsApproved,R_PNRef,R_Result,R_RespMsg,R_AuthCode,R_AVSAddr,R_AVSZIP,R_Info,
				Processing,OProcessing,Processed,Posted,IsReconciled, C_DocType_ID,IsReceipt, IsAllocated,
				C_BP_BankAccount_ID,C_PaymentBatch_ID,
				C_Currency_ID, PayAmt,WriteOffAmt, DateTrx, OverUnderAmt, IsOverUnderPayment, 
				C_BPartner_ID, C_Invoice_ID)
			SELECT v_NextNoPayment, DocumentNo ||' **' , 'DR','CO',
				AD_Client_ID,AD_Org_ID,'Y',SysDate,0,SysDate,0,
				TrxType, C_BankAccount_ID, TenderType,
				CreditCardType,CreditCardNumber,CreditCardVV,CreditCardExpMM,CreditCardExpYY,
				MICR,RoutingNo,AccountNo,CheckNo,
				A_Name,A_Street,A_City,A_State,A_ZIP,A_Ident_DL,A_Ident_SSN,A_EMail,
				VoiceAuthCode,Orig_TrxID,
				PONum, DiscountAmt*-1,TaxAmt*-1,
				'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,
				'N','N','N','N','N', C_DocType_ID,IsReceipt, 'Y',
				C_BP_BankAccount_ID,NULL,
				C_Currency_ID, PayAmt*-1,WriteOffAmt*-1, SysDate, OverUnderAmt*-1, IsOverUnderPayment, 
				C_BPartner_ID, C_Invoice_ID
			FROM	C_Payment
			WHERE	C_Payment_ID = v_Record_ID;
			--	Post
			v_ResultStr := 'PostReversal';
			C_Payment_Post (NULL, v_NextNoPayment);
			--	Unlink
			IF (v_Invoice_ID IS NOT NULL) THEN
				DBMS_OUTPUT.PUT_LINE('  Unlink Payment from Invoice ID=' || v_Invoice_ID);
				UPDATE	C_Invoice 
				  SET	C_Payment_ID = NULL
				WHERE	C_Invoice_ID = v_Invoice_ID;
				--	Un-Link to Order
				UPDATE	C_Order o
				  SET	C_Payment_ID = NULL
				WHERE EXISTS (SELECT * FROM C_Invoice i 
					WHERE o.C_Order_ID=i.C_Order_ID AND i.C_Invoice_ID=v_Invoice_ID);
			END IF;
		END;
		--	Update Status
		UPDATE	C_Payment
		  SET	DocStatus = 'RE',	--	REversed
				DocAction = '--',
				Processed = 'Y',
				Updated = SysDate
		WHERE	C_Payment_ID = v_Record_ID;
		GOTO FINISH_PROCESS;
	END IF;	--	DocAction = 'RC'

	/**
	 *	Everything done
	 */
	IF (v_Processed = 'Y') THEN
		v_Message := '@AlreadyPosted@';
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Order already processed
	 */
	IF (v_DocStatus <> ('DR')) THEN
		v_Message := '@AlreadyPosted@';
		GOTO FINISH_PROCESS;
	END IF;


	/*************************************************************************/

	/**
	 *	Unsuccessful Online Payment
	 */
	IF (v_IsOnline = 'Y' AND v_IsApproved = 'N' AND v_Result IS NOT NULL) THEN
		v_Message := '@OnlinePaymentFailed@';
		GOTO FINISH_PROCESS;
	END IF;

	--	Check Online Status
	IF (v_IsOnline = 'Y' AND v_IsApproved <> 'Y') THEN
		v_Message := '@PaymentNotProcessed@';
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Create invoice Allocation
	 *	--	See also C_Cash_Post
	 */
	IF (v_Invoice_ID IS NOT NULL) THEN		--	Single Invoice
		--	calculate actual allocation
		IF (v_OverUnderAmt < 0 AND v_PayAmt > 0) THEN
			v_AllocationAmt := v_PayAmt+v_OverUnderAmt;		--	overpayment (negative)
		ELSE
			v_AllocationAmt := v_PayAmt;					--	underpayment
		END IF;

		AD_Sequence_Next ('C_Allocation', v_Client_ID, v_NextNo);
		v_ResultStr := 'CreateAllocation ' || v_NextNo;
		DBMS_OUTPUT.PUT_LINE(v_ResultStr || ' - Invoice=' || v_Invoice_ID);
		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,
			v_Client_ID,v_Org_ID, 'Y', SysDate,0, SysDate,0,
			C_Allocation_Seq.NextVal, v_Currency_ID, v_DateTrx, 'N',
			v_BPartner_ID, NULL,v_Invoice_ID, v_Record_ID,NULL,
			v_AllocationAmt, v_DiscountAmt, v_WriteOffAmt, 'Y','N');

		--	Check if invoice is paid
		v_ResultStr := 'UpdateInvoice ' || v_Invoice_ID;
		UPDATE	C_Invoice 
		  SET	IsPaid = DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, 1), 
					GrandTotal, 'Y', 'N')	--	hardcoded Invoice/CreditMemo multiplier
		WHERE	C_Invoice_ID = v_Invoice_ID;
		--	If initiated through batch update links
		IF (p_PInstance_ID IS NOT NULL) THEN
			UPDATE	C_Invoice 
			  SET	C_Payment_ID = v_Record_ID
			WHERE	C_Invoice_ID = v_Invoice_ID;
			--	Create Link to Order
			UPDATE	C_Order o
			  SET	C_Payment_ID = v_Record_ID
			WHERE EXISTS (SELECT * FROM C_Invoice i 
				WHERE o.C_Order_ID=i.C_Order_ID AND i.C_Invoice_ID=v_Invoice_ID);
		END IF;
	ELSE		--	C_Invoice_ID is NULL -- For all Invoices in Payment Selection
		DECLARE
			CURSOR CUR_Invoices IS
				SELECT psc.C_BPartner_ID, psl.C_Invoice_ID, psl.PayAmt, psl.DifferenceAmt
				FROM C_PaySelectionLine psl
				  INNER JOIN C_PaySelectionCheck psc 
					ON (psl.C_PaySelectionCheck_ID=psc.C_PaySelectionCheck_ID)
				WHERE psc.C_Payment_ID=v_Record_ID;
		BEGIN
			FOR i IN CUR_Invoices LOOP
				AD_Sequence_Next ('C_Allocation', v_Client_ID, v_NextNo);
				v_ResultStr := 'CreateAllocation ' || v_NextNo;
				DBMS_OUTPUT.PUT_LINE(v_ResultStr || ' - Invoice=' || i.C_Invoice_ID);
				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,
					v_Client_ID,v_Org_ID, 'Y', SysDate,0, SysDate,0,
					C_Allocation_Seq.NextVal, v_Currency_ID, v_DateTrx, 'N',
					i.C_BPartner_ID, NULL, i.C_Invoice_ID, v_Record_ID,NULL,
					i.PayAmt, i.DifferenceAmt, 0, 'Y','N');
				--	Check if invoice is paid
				v_ResultStr := 'UpdateInvoice ' || i.C_Invoice_ID;
				UPDATE	C_Invoice 
				  SET	IsPaid = DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, 1), 
							GrandTotal, 'Y', 'N')	--	hardcoded Invoice/CreditMemo multiplier
				WHERE	C_Invoice_ID = i.C_Invoice_ID;
				--	Set Link
				UPDATE	C_Invoice
				  SET	C_Payment_ID = v_Record_ID
				WHERE	C_Invoice_ID = i.C_Invoice_ID
				  AND	IsPaid = 'Y'
				  AND	C_Payment_ID IS NULL;
			END LOOP;
		END;
	END IF;

	-- Set Receipt
	SELECT	CASE DocBaseType WHEN 'ARR' THEN 'Y' ELSE 'N' END
	  INTO	v_IsReceipt
	FROM		C_DocType 
	WHERE	C_DocType_ID=v_DocType_ID;

	--	Set Status "COmplete"
	v_ResultStr := 'CompletePayment';
	UPDATE	C_Payment
	  SET	DocStatus = 'CO',	--	COmpleted
			DocAction = '--',
			Processed = 'Y',
			IsAllocated = DECODE(C_Payment_Allocated(C_Payment_ID, C_Currency_ID), PayAmt, 'Y', 'N'),
			IsReceipt = v_IsReceipt,
			Updated = SysDate
	WHERE	C_Payment_ID = v_Record_ID;

	/**
	 *	Prepayment Order for fully paid Orders - init shipment
	 */
	IF (v_Invoice_ID IS NOT NULL AND v_OverUnderAmt = 0) THEN
		DECLARE
			v_C_Order_ID			NUMBER(10);
			v_InOut_ID			NUMBER(10) := 0;
		BEGIN
			v_ResultStr := 'GetPrePayOrder';
			SELECT MAX(o.C_Order_ID)
			  INTO v_C_Order_ID
			FROM C_Order o
			  INNER JOIN C_Invoice i ON (o.C_Order_ID=i.C_Order_ID)
			WHERE o.DocStatus = 'WP'
			  AND i.C_Invoice_ID=v_Invoice_ID;
			--	We have a Prepayment Order
			IF (v_C_Order_ID IS NOT NULL) THEN
				DBMS_OUTPUT.PUT_LINE('PrePay C_Order_ID=' || v_C_Order_ID);
				--	Update Order
				UPDATE	C_Order o
				  SET	DocStatus = 'IP'		--	In Process
				WHERE	C_Order_ID = v_C_Order_ID;
				--	Process Shipment
				v_ResultStr := 'CreateShipment';
				M_InOut_Create(NULL, v_C_Order_ID, NULL, 'Y', v_InOut_ID);	--	Force Delivery
				DBMS_OUTPUT.PUT_LINE('  Shipment - ' || v_InOut_ID);
				IF (v_InOut_ID IS NULL OR v_InOut_ID = 0) THEN
					v_Message := 'InOutCreateFailed';
					DBMS_OUTPUT.PUT_LINE('InOutCreateFailed - C_Order_ID=' || v_C_Order_ID);
					ROLLBACK;
				ELSE
					UPDATE	C_Order o
					  SET	DocStatus = 'CO'	--	Complete
					WHERE	C_Order_ID = v_C_Order_ID;
				END IF;
			END IF;
		END;
	END IF;
	
<<FINISH_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
	IF (p_PInstance_ID IS NOT NULL) THEN
		UPDATE	AD_PInstance
		SET Updated = SysDate,
			IsProcessing = 'N',
			Result = 1,					-- success
			ErrorMsg = v_Message
		WHERE	AD_PInstance_ID=p_PInstance_ID;
		COMMIT;
	END IF;
	RETURN;

EXCEPTION
	WHEN  OTHERS THEN
		v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
		DBMS_OUTPUT.PUT_LINE(v_ResultStr);
		ROLLBACK;
		IF (p_PInstance_ID IS NOT NULL) THEN
			UPDATE	AD_PInstance
			SET Updated = SysDate,
				IsProcessing = 'N',
				Result = 0,				-- failure
				ErrorMsg = v_ResultStr
			WHERE	AD_PInstance_ID=p_PInstance_ID;
			COMMIT;
		END IF;
		RETURN;

END C_Payment_Post;
/