c_payselection_post.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_PaySelection_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+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_PaySelection_Post.sql,v 1.9 2003/04/22 16:29:11 jjanke Exp $
 ***
 * Title:	Payment Selection Generate
 * Description:
 *			Create Payments (checks) from Selection
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Result						NUMBER(10) := 0;	--	failure
	--	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
	p_Record_ID						NUMBER(10);
	p_PaymentRule						VARCHAR2(1);
	--
	v_Processed						C_PaySelection.Processed%TYPE;
	v_Count							NUMBER(10) := 0;
	v_TotalAmt						C_PaySelection.TotalAmt%TYPE := 0;

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
		p_Record_ID := p.Record_ID;
		IF (p.ParameterName = 'PaymentRule') THEN
			p_PaymentRule := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  PaymentRule=' || p_PaymentRule);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
		END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || p_Record_ID);
	IF (p_PaymentRule NOT IN ('S','T')) THEN		-- Check/Transfer
		p_PaymentRule := 'S';	-- Not P-OnCredit B-Cash K-CreditCard
	END IF;

	--	Record Info
	BEGIN
		SELECT	Processed
		  INTO	v_Processed
		FROM		C_PaySelection
		WHERE	C_PaySelection_ID = p_Record_ID;
	EXCEPTION WHEN OTHERS THEN
		v_Message := '@SaveErrorRowNotFound@';
		GOTO FINISH_PROCESS;
	END;
		

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

	/**
	 *	Create Checks
	 */
	DECLARE
		CURSOR CUR_PSLine IS
			SELECT	psl.AD_Client_ID, psl.AD_Org_ID, psl.C_PaySelection_ID,	
				i.C_BPartner_ID, 
				SUM(psl.PayAmt) AS PayAmt, Count(*) AS Qty
			FROM	C_PaySelectionLine psl
			  INNER JOIN C_Invoice i ON (psl.C_Invoice_ID=i.C_Invoice_ID)
			WHERE	psl.C_PaySelection_ID = p_Record_ID	-- 
			GROUP BY psl.AD_Client_ID, psl.AD_Org_ID, psl.C_PaySelection_ID,	
				i.C_BPartner_ID;
		--
		v_C_PaySelectionCheck_ID		C_PaySelectionCheck.C_PaySelectionCheck_ID%TYPE;
	BEGIN
		FOR l IN CUR_PSLine LOOP
			AD_Sequence_Next ('C_PaySelectionCheck', l.AD_Org_ID, v_C_PaySelectionCheck_ID);
			INSERT INTO C_PaySelectionCheck
				(AD_Client_ID, AD_Org_ID, IsActive, Created,CreatedBy,Updated,UpdatedBy,
				C_PaySelectionCheck_ID, C_PaySelection_ID,
				C_BPartner_ID, PaymentRule,
				PayAmt, Qty,
				IsPrinted, DocumentNo, C_Payment_ID)
			VALUES
				(l.AD_Client_ID, l.AD_Org_ID, 'Y', SysDate,0,SysDate,0,
				v_C_PaySelectionCheck_ID, l.C_PaySelection_ID,
				l.C_BPartner_ID, p_PaymentRule,	--	Target Payment Rule
				l.PayAmt, l.Qty,
				'N', null, null);
			v_Count := v_Count + 1;
			v_TotalAmt := v_TotalAmt + l.PayAmt;
			--	Create Link for Invoice from Vendor
			UPDATE	C_PaySelectionLine psl
			  SET	C_PaySelectionCheck_ID = v_C_PaySelectionCheck_ID
			WHERE	C_PaySelection_ID = l.C_PaySelection_ID
			  AND EXISTS (SELECT * FROM C_Invoice i 
				WHERE psl.C_Invoice_ID=i.C_Invoice_ID
				  AND i.C_BPartner_ID = l.C_BPartner_ID);
			
			IF (SQL%ROWCOUNT <> l.Qty) THEN
				DBMS_OUTPUT.PUT_LINE('ERROR RowCount=' || SQL%ROWCOUNT || ', Qty=' || l.Qty );				
			END IF;
		END LOOP;
	END;	--	CreateChecks

	/**
	 *	Update Payment Selection
	 */
	UPDATE	C_PaySelection
	  SET	Processed = 'Y',
			TotalAmt = v_TotalAmt
	WHERE	C_PaySelection_ID = p_Record_ID;
	COMMIT;

	v_Message := '@Created@=' || v_Count || ' - ' || v_TotalAmt;
	v_Result := 1;			--	success


<<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,
		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_PaySelection_Post;
/