c_validcombination_get.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_ValidCombination_Get
(
	o_C_ValidCombination_ID      OUT NUMBER, 
	--	Mandatory Accounting fields
	p_AD_Client_ID               IN NUMBER, 
	p_AD_Org_ID                  IN NUMBER, 
	p_C_AcctSchema_ID            IN NUMBER, 
	p_Account_ID                 IN NUMBER, 
	--	Optional
	b_C_ValidCombination_ID      IN NUMBER DEFAULT NULL,
	MustBeFullyQualified		 IN CHAR DEFAULT 'N',
	p_Alias						 IN VARCHAR2 DEFAULT NULL,
	p_CreatedBy                  IN NUMBER DEFAULT NULL,
	--	Optional Accounting fields
    p_M_Product_ID               IN NUMBER DEFAULT NULL, 
    p_C_BPartner_ID              IN NUMBER DEFAULT NULL, 
    p_AD_OrgTrx_ID               IN NUMBER DEFAULT NULL, 
    p_C_LocFrom_ID               IN NUMBER DEFAULT NULL, 
    p_C_LocTo_ID                 IN NUMBER DEFAULT NULL, 
    p_C_SalesRegion_ID           IN NUMBER DEFAULT NULL, 
    p_C_Project_ID               IN NUMBER DEFAULT NULL, 
    p_C_Campaign_ID              IN NUMBER DEFAULT NULL, 
    p_C_Activity_ID              IN NUMBER DEFAULT NULL, 
    p_User1_ID                   IN NUMBER DEFAULT NULL, 
    p_User2_ID                   IN NUMBER DEFAULT NULL
)
/*************************************************************************
 * 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_ValidCombination_Get.sql,v 1.3 2002/02/08 05:43:58 jjanke Exp $
 ***
 * Title:	Create Valid Account Code Combination
 * Description:
 *	Get a Valid Account Combination
 *	1) Create template for combination
 *		- if base combination (b_) != NULL, retrieve existing VC
 *		- replace with NOT NULL transaction values (p_) elements
 *	2) Check, if combination exists
 *		- yes, return target combination (t_)
 *		- otherwise create it
 *	Returns NULL if not all mandatory fields have values 
 *	or if MustBeFullyQualified=Y and found combination is not fully qualified
 ************************************************************************/
AS
	--	Base Combination
	CURSOR	Cur_Base_VC	IS
		SELECT 	* 
		FROM 	C_ValidCombination 
		WHERE 	C_ValidCombination_ID=b_C_ValidCombination_ID;
   	--	Target Combination
	t_AD_Client_ID               NUMBER := NULL;
	t_AD_Org_ID                  NUMBER := NULL;
	t_C_AcctSchema_ID            NUMBER := NULL;
	t_Account_ID                 NUMBER := NULL;
	t_M_Product_ID               NUMBER := NULL;
	t_C_BPartner_ID              NUMBER := NULL;
	t_AD_OrgTrx_ID               NUMBER := NULL;
	t_C_LocFrom_ID               NUMBER := NULL;
	t_C_LocTo_ID                 NUMBER := NULL;
	t_C_SalesRegion_ID           NUMBER := NULL;
	t_C_Project_ID               NUMBER := NULL;
	t_C_Campaign_ID              NUMBER := NULL;
	t_C_Activity_ID              NUMBER := NULL;
	t_User1_ID                   NUMBER := NULL;
	t_User2_ID                   NUMBER := NULL;
	t_CreatedBy		             NUMBER := NULL;

	--	Existance Loop - Get first fully qualified matching combination
	CURSOR	Cur_Existing_VC	IS
		SELECT 	*
		FROM 	C_ValidCombination 
			--	Mandatory fields
		WHERE 	AD_Client_ID=t_AD_Client_ID
		  AND	AD_Org_ID=t_AD_Org_ID
		  AND	C_AcctSchema_ID=t_C_AcctSchema_ID
		  AND	Account_ID=t_Account_ID
		  	--	Optional fields
		  AND	(t_M_Product_ID IS NULL OR M_Product_ID=t_M_Product_ID)
		  AND	(t_C_BPartner_ID IS NULL OR C_BPartner_ID=t_C_BPartner_ID)
		  AND	(t_AD_OrgTrx_ID IS NULL OR AD_OrgTrx_ID=t_AD_OrgTrx_ID)
		  AND	(t_C_LocFrom_ID IS NULL OR C_LocFrom_ID=t_C_LocFrom_ID)
		  AND	(t_C_LocTo_ID IS NULL OR C_LocTo_ID=t_C_LocTo_ID)
		  AND	(t_C_SalesRegion_ID IS NULL OR C_SalesRegion_ID=t_C_SalesRegion_ID)
		  AND	(t_C_Project_ID IS NULL OR C_Project_ID=t_C_Project_ID)
		  AND	(t_C_Campaign_ID IS NULL OR C_Campaign_ID=t_C_Campaign_ID)
		  AND	(t_C_Activity_ID IS NULL OR C_Activity_ID=t_C_Activity_ID)
		  AND	(t_User1_ID IS NULL OR User1_ID=t_User1_ID)
		  AND	(t_User2_ID IS NULL OR User2_ID=t_User2_ID)
		  AND	IsActive='Y'
		  	--	Y comes after N
		ORDER BY IsFullyQualified DESC;
	--
	FoundValue				CHAR(1) := 'N';
	RetValue				NUMBER := NULL;
	fullyQualified			CHAR(1);
BEGIN
	--	1a  -- Fill with base info
	IF (b_C_ValidCombination_ID != NULL) THEN
		--	Actually only one value possible, for convineance
		FOR base IN Cur_Base_VC LOOP
			DBMS_OUTPUT.PUT_LINE('Base VC found');
			--	mandatory fields
			t_AD_Client_ID := base.AD_Client_ID;
			t_AD_Org_ID := base.AD_Org_ID;
			t_C_AcctSchema_ID := base.C_AcctSchema_ID;
			t_Account_ID := base.Account_ID;
			--	optional fields
			t_M_Product_ID := base.M_Product_ID;
			t_C_BPartner_ID := base.C_BPartner_ID;
			t_AD_OrgTrx_ID := base.AD_OrgTrx_ID;
			t_C_LocFrom_ID := base.C_LocFrom_ID;
			t_C_LocTo_ID := base.C_LocTo_ID;
			t_C_SalesRegion_ID := base.C_SalesRegion_ID;
			t_C_Project_ID := base.C_Project_ID;
			t_C_Campaign_ID := base.C_Campaign_ID;
			t_C_Activity_ID := base.C_Activity_ID;
			t_User1_ID := base.User1_ID;
			t_User2_ID := base.User2_ID;
		END LOOP;
	END IF;

	--	1b  -- Update target values
	IF (p_AD_Client_ID IS NOT NULL) THEN
		t_AD_Client_ID := p_AD_Client_ID;
	END IF;
	IF (p_AD_Org_ID IS NOT NULL) THEN
		t_AD_Org_ID := p_AD_Org_ID;
	END IF;
	IF (p_C_AcctSchema_ID IS NOT NULL) THEN
		t_C_AcctSchema_ID := p_C_AcctSchema_ID;
	END IF;
	IF (p_Account_ID IS NOT NULL) THEN
		t_Account_ID := p_Account_ID;
	END IF;
	--	optional fields
	IF (p_M_Product_ID IS NOT NULL) THEN
		t_M_Product_ID := p_M_Product_ID;
	END IF;
	IF (p_C_BPartner_ID IS NOT NULL) THEN
		t_C_BPartner_ID := p_C_BPartner_ID;
	END IF;
	IF (p_AD_OrgTrx_ID IS NOT NULL) THEN
		t_AD_OrgTrx_ID := p_AD_OrgTrx_ID;
	END IF;
	IF (p_C_LocFrom_ID IS NOT NULL) THEN
		t_C_LocFrom_ID := p_C_LocFrom_ID;
	END IF;
	IF (p_C_LocTo_ID IS NOT NULL) THEN
		t_C_LocTo_ID := p_C_LocTo_ID;
	END IF;
	IF (p_C_SalesRegion_ID IS NOT NULL) THEN
		t_C_SalesRegion_ID := p_C_SalesRegion_ID;
	END IF;
	IF (p_C_Project_ID IS NOT NULL) THEN
		t_C_Project_ID := p_C_Project_ID;
	END IF;
	IF (p_C_Campaign_ID IS NOT NULL) THEN
		t_C_Campaign_ID := p_C_Campaign_ID;
	END IF;
	IF (p_C_Activity_ID IS NOT NULL) THEN
		t_C_Activity_ID := p_C_Activity_ID;
	END IF;
	IF (p_User1_ID IS NOT NULL) THEN
		t_User1_ID := p_User1_ID;
	END IF;
	IF (p_User2_ID IS NOT NULL) THEN
		t_User2_ID := p_User2_ID;
	END IF;	

	--	Are nandatory elements null?
	IF (t_AD_Client_ID IS NULL OR t_AD_Org_ID IS NULL 
		OR t_C_AcctSchema_ID IS NULL OR t_Account_ID IS NULL) THEN
		DBMS_OUTPUT.PUT_LINE('Mandatory Fields Null');
		o_C_ValidCombination_ID := NULL;
		RETURN;
	END IF;

	--	Existance check
	FOR e IN Cur_Existing_VC LOOP
		IF (FoundValue = 'N') THEN
			FoundValue := 'Y';
			IF (MustBeFullyQualified='Y' AND e.IsFullyQualified='N') THEN
				DBMS_OUTPUT.PUT_LINE('Combination found but not fully qualified - ' || e.C_ValidCombination_ID);
				RetValue := NULL;
	   		END IF;
			RetValue := e.C_ValidCombination_ID;
		END IF;
	END LOOP;
	--	We found a value
	IF (FoundValue = 'Y') THEN
		o_C_ValidCombination_ID := RetValue;
		DBMS_OUTPUT.PUT_LINE('Found Combination');
		RETURN;
	END IF;

	--	No Combination found - so create one
	DBMS_OUTPUT.PUT_LINE('Create new');
	AD_Sequence_Next('C_ValidCombination', t_AD_Client_ID, o_C_ValidCombination_ID);
	t_CreatedBy := p_CreatedBy;
	IF (t_CreatedBy IS NULL) THEN
		t_CreatedBy := 0;
	END IF;

	--	Don't catch trigger or integrity errors - let it fail
    INSERT INTO C_ValidCombination (C_ValidCombination_ID, IsFullyQualified,
        AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, 
        Alias, C_AcctSchema_ID, 
        Account_ID, M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID, 
        C_LocFrom_ID, C_LocTo_ID, C_SalesRegion_ID, C_Project_ID, 
        C_Campaign_ID, C_Activity_ID, User1_ID, User2_ID) 
	VALUES (o_C_ValidCombination_ID, 'N', 
		t_AD_Client_ID, t_AD_Org_ID, 'Y', SysDate, t_CreatedBy, SysDate, t_CreatedBy, 
		p_Alias, t_C_AcctSchema_ID, 
        t_Account_ID, t_M_Product_ID, t_C_BPartner_ID, t_AD_OrgTrx_ID, 
        t_C_LocFrom_ID, t_C_LocTo_ID, t_C_SalesRegion_ID, t_C_Project_ID, 
        t_C_Campaign_ID, t_C_Activity_ID, t_User1_ID, t_User2_ID); 
    COMMIT;

	--	Read Trigger result (fully qualified) 
	IF (MustBeFullyQualified='Y') THEN
		SELECT 	IsFullyQualified 
		  INTO	fullyQualified
	   	FROM	C_ValidCombination
		WHERE	C_ValidCombination_ID=o_C_ValidCombination_ID;
		--
		IF (fullyQualified = 'N') THEN
			DBMS_OUTPUT.PUT_LINE('New Combination not fully qualified - ' || o_C_ValidCombination_ID);
			o_C_ValidCombination_ID := NULL;
		END IF;
	END IF;
END C_ValidCombination_Get;
/