c_payselection_createfrom.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_PaySelection_CreateFrom
(
	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_CreateFrom.sql,v 1.9 2003/04/22 16:28:48 jjanke Exp $
 ***
 * Title:	Create Payments from Invoices
 * Description:
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Record_ID					NUMBER;
	v_Result						NUMBER := 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_OnlyDiscount					CHAR(1) := 'N';
	p_OnlyDue						CHAR(1) := 'N';
	p_C_BP_Group_ID				NUMBER(10);
	p_C_BPartner_ID				NUMBER(10);
	p_PaymentRule					VARCHAR2(1);
	--
	v_AD_Client_ID					C_PaySelection.AD_Client_ID%TYPE;
	v_AD_Org_ID					C_PaySelection.AD_Org_ID%TYPE;
	v_PayDate						C_PaySelection.PayDate%TYPE;
	v_C_BankAccount_ID				C_PaySelection.C_BankAccount_ID%TYPE;
	v_C_Currency_ID				C_BankAccount.C_Currency_ID%TYPE;
	--
	v_NextNo						NUMBER(10);
	v_Line						NUMBER(10);
	v_Created						NUMBER(10) := 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
		v_Record_ID := p.Record_ID;
		IF (p.ParameterName = 'OnlyDiscount') THEN
			p_OnlyDiscount := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  OnlyDiscount=' || p_OnlyDiscount);
		ELSIF (p.ParameterName = 'OnlyDue') THEN
			p_OnlyDue := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  OnlyDue=' || p_OnlyDue);
		ELSIF (p.ParameterName = 'PaymentRule') THEN
			p_PaymentRule := p.P_String;
			DBMS_OUTPUT.PUT_LINE('  PaymentRule=' || p_PaymentRule);
		ELSIF (p.ParameterName = 'C_BPartner_ID') THEN
			p_C_BPartner_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  C_BPartner_ID=' || p_C_BPartner_ID);
		ELSIF (p.ParameterName = 'C_BP_Group_ID') THEN
			p_C_BP_Group_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  C_BP_Group_ID=' || p_C_BP_Group_ID);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
		END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);

	--	Read PaymentSelection
	v_ResultStr := 'ReadingPaySelect';
	BEGIN
		SELECT	ps.AD_Client_ID, ps.AD_Org_ID, ps.PayDate, ba.C_BankAccount_ID, ba.C_Currency_ID
		  INTO	v_AD_Client_ID, v_AD_Org_ID, v_PayDate, v_C_BankAccount_ID, v_C_Currency_ID
		FROM	C_PaySelection ps, C_BankAccount ba
		WHERE	ps.C_BankAccount_ID=ba.C_BankAccount_ID
		  AND	C_PaySelection_ID=v_Record_ID;
	EXCEPTION WHEN OTHERS THEN
		v_Message := '@SaveErrorRowNotFound@';
		GOTO FINISH_PROCESS;
	END;
		  

	SELECT	NVL(MAX(Line),0) + 10
	  INTO	v_Line
	FROM		C_PaySelectionLine
	WHERE	C_PaySelection_ID=v_Record_ID;

	DECLARE
		CURSOR	Cur_Invoice IS
			SELECT	C_Invoice_ID, C_Currency_Convert(C_Invoice_Open(C_Invoice_ID),
				C_Currency_ID, v_C_Currency_ID, v_PayDate, null, v_AD_Client_ID, v_AD_Org_ID) AS PayAmt,
				PaymentRule
			FROM	C_Invoice i
			WHERE	IsSOTrx='N' AND IsPaid='N' AND DocStatus IN ('CO','CL')
			  AND AD_Client_ID=v_AD_Client_ID
			  AND NOT EXISTS (SELECT * FROM C_PaySelectionLine psl
				WHERE i.C_Invoice_ID=psl.C_Invoice_ID
				  AND psl.C_PaySelectionCheck_ID IS NOT NULL)
			-- PaymentRule (optional)
			  AND	(p_PaymentRule IS NULL OR PaymentRule=p_PaymentRule)
			-- OnlyDiscount
			  AND (
					(p_OnlyDiscount = 'N' OR C_PaymentTerm_Discount(C_Invoice_Open(C_Invoice_ID), 
						C_PaymentTerm_ID, DateInvoiced, v_PayDate) > 0)
			-- OnlyDue
				OR	(p_OnlyDue = 'N' OR C_PaymentTerm_DueDays(C_PaymentTerm_ID, 
						DateInvoiced, v_PayDate) >= 0)
			  )
			-- Business Partner (optional)
			  AND	(p_C_BPartner_ID IS NULL OR C_BPartner_ID=p_C_BPartner_ID)
			-- Business Partner Group (optional)
			AND		(p_C_BP_Group_ID IS NULL OR EXISTS (SELECT * FROM C_BPartner bp 
					WHERE bp.C_BPartner_ID=i.C_BPartner_ID AND bp.C_BP_Group_ID=p_C_BP_Group_ID));
		--
	BEGIN		
		FOR i IN Cur_Invoice LOOP
			v_ResultStr := 'CreatingLines';
			AD_Sequence_Next('C_PaySelectionLine', v_Record_ID, v_NextNo);
			INSERT INTO C_PaySelectionLine
				(C_PaySelectionLine_ID, C_PaySelection_ID, AD_Client_ID,AD_Org_ID,
				IsActive,Created,CreatedBy,Updated,UpdatedBy,
				Line, PaymentRule, IsManual,
				C_Invoice_ID, PayAmt, DifferenceAmt)
			VALUES
				(v_NextNo, v_Record_ID, v_AD_Client_ID, v_AD_Org_ID,
				'Y', SysDate,0,SysDate,0,
				v_Line, i.PaymentRule, 'N',
				i.C_Invoice_ID, i.PayAmt, 0);
			v_Line := v_Line + 10;
			v_Created := v_Created + 1;
		END LOOP;
	END;

	--	Update Amount	
	UPDATE	C_PaySelection
	  SET	TotalAmt = (SELECT COALESCE(SUM(PayAmt),0) 
		FROM C_PaySelectionLine WHERE C_PaySelection_ID = v_Record_ID)
	WHERE	C_PaySelection_ID = v_Record_ID;

	COMMIT;

	v_Message := '@Created@ = ' || v_Created;
	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 = 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_PaySelection_CreateFrom;
/