c_project_generate.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_Project_Generate
(
	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_Project_Generate.sql,v 1.3 2002/10/23 03:16:57 jjanke Exp $
 ***
 * Title:	Generate Standard Order from Project
 * Description:
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr						VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Record_ID						NUMBER;
	--	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_M_Warehouse_ID				NUMBER;
	--
	v_DocumentNo					VARCHAR2(40);
	--
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 = 'M_Warehouse_ID') THEN
			p_M_Warehouse_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  M_Warehouse_ID=' || p_M_Warehouse_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);


	DECLARE
		CURSOR CUR_Project	IS
			SELECT	*
			FROM	C_Project
			WHERE	C_Project_ID = v_Record_ID;
		CURSOR CUR_PLines IS
			SELECT	*
			FROM	C_ProjectLine
			WHERE	C_Project_ID = v_Record_ID
			  AND	IsActive='Y'
			ORDER BY Line;
		v_C_Order_ID			NUMBER(10);
		v_C_DocType_ID			NUMBER(10);
		v_NextNo				NUMBER(10);
		v_M_PriceList_ID		NUMBER(10);
		v_C_PaymentTerm_ID		NUMBER(10);
		--
		v_Line					NUMBER(10);
		v_PriceList				NUMBER;
		v_PriceLimit			NUMBER;
		v_C_UOM_ID				NUMBER(10);
		v_C_Tax_ID				NUMBER(10);
		v_Discount				NUMBER;
	BEGIN
		FOR p IN CUR_Project LOOP
			v_ResultStr := 'NullTest';
			IF (p.AD_User_ID IS NULL OR p.DateContract IS NULL 
				OR p.C_BPartner_ID IS NULL OR p.C_BPartner_Location_ID IS NULL 
				OR p.C_Currency_ID IS NULL OR p.C_PaymentTerm_ID IS NULL) THEN
				v_Message := 'NeedMoreInfoToCreateOrder';
				GOTO FINISH_PROCESS;
			END IF;

			v_ResultStr := 'NoDocType';
			SELECT	C_DocType_ID
			  INTO	v_C_DocType_ID
			FROM	C_DocType
			WHERE	DocBaseType='SOO' AND DocSubTypeSO = 'SO' AND RowNum = 1
			  AND 	AD_Client_ID = p.AD_Client_ID;
			--
			v_ResultStr := 'NoPriceList';
			SELECT 	M_PriceList_ID
			  INTO	v_M_PriceList_ID
			FROM 	M_PriceList_Version
			WHERE 	M_PriceList_Version_ID=p.M_PriceList_Version_ID;
			--
			AD_Sequence_DocType (v_C_DocType_ID, p.AD_Client_ID, v_DocumentNo);
			AD_Sequence_Next ('C_Order', p.AD_Client_ID, v_C_Order_ID);
			v_ResultStr := 'InsertingHeader';
			INSERT INTO C_Order (C_Order_ID, DocumentNo,
				AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
				IsSOTrx, DocStatus, DocAction, Processed, C_DocType_ID, C_DocTypeTarget_ID,
				Description, SalesRep_ID, DateOrdered, DateAcct, POReference,
				C_BPartner_ID, BillTo_ID, C_BPartner_Location_ID, C_BPartner_Contact_ID,
				C_Currency_ID, C_PaymentTerm_ID, 
				PaymentRule, InvoiceRule, DeliveryRule, DeliveryViaRule, FreightCostRule, PriorityRule,
				M_Warehouse_ID, M_PriceList_ID, C_Project_ID, C_Campaign_ID)
			VALUES (v_C_Order_ID, v_DocumentNo,
				p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
				'Y', 'DR', 'CO', 'N', v_C_DocType_ID,v_C_DocType_ID,
				p.Description, p.AD_User_ID, p.DateContract, p.DateContract, p.POReference,
				p.C_BPartner_ID, p.C_BPartner_Location_ID, p.C_BPartner_Location_ID, p.C_BPartner_Contact_ID,
				p.C_Currency_ID, p.C_PaymentTerm_ID, 
				'P', 'D', 'A', 'P', 'I', '5', -- OnCredit, afterDelivery, Availability, Pickup, Included, 
				p_M_Warehouse_ID, v_M_PriceList_ID, p.C_Project_ID, p.C_Campaign_ID);
			--
			v_Line := 0;
			FOR l IN CUR_PLines LOOP
				v_ResultStr := 'GettingPrice';
				SELECT	NVL(MAX(PriceList),0), NVL(MAX(PriceLimit),0)
				  INTO	v_PriceList, v_PriceLimit
				FROM	M_ProductPrice
				WHERE	M_Product_ID = l.M_Product_ID
				  AND	M_PriceList_Version_ID = p.M_PriceList_Version_ID;
				IF (v_PriceList = 0) THEN
					v_Discount := 0;
				ELSE
					v_Discount := ROUND ((v_PriceList-l.PlannedPrice) / v_PriceList*100, 2);
				END IF;
				-- Get UOM - Tax -- VERY simplified, but should work in most cases
				v_ResultStr := 'NoUOM+Tax';
				SELECT	NVL(MAX(C_UOM_ID),100), MAX(C_Tax_ID)	--	UOM 100=EA
				  INTO	v_C_UOM_ID, v_C_Tax_ID
				FROM	M_Product p, C_Tax t
				WHERE	p.C_TaxCategory_ID=t.C_TaxCategory_ID
				  AND	p.M_Product_ID = l.M_Product_ID
				ORDER BY t.IsDefault DESC;
				IF (v_C_Tax_ID IS NULL) THEN
					v_ResultStr := 'NoTax';
					SELECT 	C_Tax_ID 
					  INTO	v_C_Tax_ID
					FROM 	C_Tax
					WHERE	IsDefault='Y'
					  AND	AD_Client_ID = l.AD_Client_ID;
				END IF;
				--
				AD_Sequence_Next ('C_OrderLine', p.AD_Client_ID, v_NextNo);
				v_ResultStr := 'InsertingLine';
				v_Line := v_Line + 10;
				INSERT INTO C_OrderLine (C_OrderLine_ID, C_Order_ID,
					AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					Line, C_BPartner_ID, C_BPartner_Location_ID, DateOrdered,
					Description, M_Warehouse_ID, M_Product_ID, DirectShip, C_UOM_ID, C_Currency_ID,
					QtyOrdered, PriceList, PriceActual, PriceLimit, 
					LineNetAmt, Discount, C_Tax_ID)
				VALUES (v_NextNo, v_C_Order_ID,
					l.AD_Client_ID, l.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
					v_Line, p.C_BPartner_ID, p.C_BPartner_Location_ID, p.DateContract,
					l.Description, p_M_Warehouse_ID, l.M_Product_ID, 'N', v_C_UOM_ID, p.C_Currency_ID,
					l.PlannedQty, v_PriceList, l.PlannedPrice, v_PriceLimit, 
					ROUND(l.PlannedQty*l.PlannedPrice, 2), v_Discount, v_C_Tax_ID);
			END LOOP;	--	ProjectLine
			v_Message := 'OrderCreated ';
	   	END LOOP;	--	Project
		--
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				v_Message := 'OrderNotCreated ' || v_ResultStr || ': ' || SQLErrM;
	END;
	v_Message := '@C_Invoice_ID@ = ' || v_DocumentNo;


<<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_Project_Generate;
/