a_asset_create.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE A_Asset_Create
(
	p_PInstance_ID			IN NUMBER,
	p_InOutLine_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+CPM
 * Copyright (C) 1999-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: A_Asset_Create.sql,v 1.4 2003/02/04 05:52:35 jjanke Exp $
 ***
 * Title:	Create Asset and Delivery
 * Description:
 * Open Issues:
 *	- Reversal or Negative Delivery
 *	- Guarantee Extension
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Result						NUMBER := 1;	-- 0=failure
	p_Record_ID					NUMBER;
	p_AD_User_ID					NUMBER;
	--	Parameter
	CURSOR Cur_Parameter (pp_PInstance NUMBER) IS
		SELECT i.Record_ID, i.AD_User_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

	--	Create from ShipmentLine
	CURSOR Cur_ShipLine (pp_InOutLine_ID NUMBER) IS
		SELECT s.AD_Client_ID, s.AD_Org_ID, 
			s.DocumentNo,  s.MovementDate,
			s.C_BPartner_ID, s.C_BPartner_Location_ID, s.C_BPartner_Contact_ID,
			p.Value, p.Name, p.Description, p.Help, p.VersionNo,
			pc.A_Asset_Group_ID, 
			CASE WHEN p.GuaranteeDays IS NOT NULL THEN s.MovementDate+p.GuaranteeDays ELSE NULL END AS GuaranteeDate,
			sl.M_Product_ID, sl.SerNo, sl.Lot, sl.Description AS LineDescription, sl.MovementQty
		FROM M_InOut s
		  INNER JOIN M_InOutLine sl ON (s.M_InOut_ID=sl.M_InOut_ID)
		  INNER JOIN M_Product p ON (sl.M_Product_ID=p.M_Product_ID)
		  INNER JOIN M_Product_Category pc ON (p.M_Product_Category_ID=pc.M_Product_Category_ID)
		WHERE pc.A_Asset_Group_ID IS NOT NULL
		  AND sl.M_InOutLine_ID=pp_InOutLine_ID
		  AND sl.MovementQty > 0;
	--
	v_A_Asset_ID			NUMBER(10);
	v_A_Asset_Delivery_ID	NUMBER(10);
	v_DocumentNo			VARCHAR2(40);
	v_count				NUMBER := 0;
	
BEGIN
	--  Update AD_PInstance
	IF (p_PInstance_ID IS NOT NULL) THEN
		DBMS_OUTPUT.PUT_LINE('A_Asset_Create - 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;
			p_AD_User_ID := p.AD_User_ID;
		--	IF (p.ParameterName = 'xx') THEN
		--		p_xx := p.P_String;
		--		DBMS_OUTPUT.PUT_LINE('  xx=' || p_xx);
		--	ELSE
		--		DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
		-- 	END IF;
		END LOOP;	--	Get Parameter
	END IF;
	
--	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || p_Record_ID || ', InOutLine_ID=' || p_InOutLine_ID);

	--	Create From Shipment Line
	IF (p_InOutLine_ID IS NOT NULL) THEN
		FOR s IN Cur_ShipLine (p_InOutLine_ID) LOOP
			FOR i IN 1 .. s.MovementQty LOOP	
				v_ResultStr := 'InsertingAsset';
				AD_Sequence_Next ('A_Asset', s.AD_Client_ID, v_A_Asset_ID);
				v_DocumentNo := ' ' || s.DocumentNo;
				IF (s.MovementQty > 1) THEN
					v_DocumentNo := v_DocumentNo || '_' || i;
				END IF;
				--
				INSERT INTO A_Asset
					(A_Asset_ID, AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
					Value, Name, 
					Description, Help,
					A_Asset_Group_ID, M_Product_ID, SerNo, Lot, VersionNo,
					GuaranteeDate, AssetServiceDate,
					IsOwned, IsDepreciated, AssetDepreciationDate,
					IsInPosession, LocationComment,
					C_BPartner_ID,C_BPartner_Location_ID,C_BPartner_Contact_ID)
				VALUES
					(v_A_Asset_ID, s.AD_Client_ID,s.AD_Org_ID,'Y',SysDate,0,SysDate,0,
					SUBSTR(s.Value||v_DocumentNo,1,40), SUBSTR(s.Name||v_DocumentNo,1,60), 
					SUBSTR(s.Description||s.LineDescription,1,255), s.Help,
					s.A_Asset_Group_ID, s.M_Product_ID, s.SerNo, s.Lot, s.VersionNo,
					s.GuaranteeDate, s.MovementDate,
					'N', 'N', null,
					'N', v_DocumentNo,
					s.C_BPartner_ID,s.C_BPartner_Location_ID,s.C_BPartner_Contact_ID);
				DBMS_OUTPUT.PUT_LINE('A_Asset_ID=' || v_A_Asset_ID || ' - ' || s.Value||v_DocumentNo);
				
				----
				v_ResultStr := 'InsertingAssetDelivery';
				AD_Sequence_Next ('A_Asset_Delivery', s.AD_Client_ID, v_A_Asset_Delivery_ID);
				--
				INSERT INTO A_Asset_Delivery
					(A_Asset_Delivery_ID, A_Asset_ID, 
					AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
					MovementDate, SerNo, Lot, VersionNo,
					M_InOutLine_ID, C_BPartner_Contact_ID)
				VALUES
					(v_A_Asset_Delivery_ID, v_A_Asset_ID,
					s.AD_Client_ID,s.AD_Org_ID,'Y',SysDate,0,SysDate,0,
					s.MovementDate, s.SerNo, s.Lot, s.VersionNo,
					p_InOutLine_ID, s.C_BPartner_Contact_ID);
				DBMS_OUTPUT.PUT_LINE('  Delivery_ID=' || v_A_Asset_Delivery_ID);
			END LOOP;
			v_Message := '@Created@: ' || s.value || s.MovementQty;
			v_count := v_count + 1;
		END LOOP;
	END IF;
	DBMS_OUTPUT.PUT_LINE('Assets Created=' || v_count);



<<FINISH_PROCESS>>
	DBMS_OUTPUT.PUT_LINE('A_Asset_Create - Finished ' || v_Message);
	--  Update AD_PInstance
	IF (p_PInstance_ID IS NOT NULL) THEN
		UPDATE	AD_PInstance
		SET Updated = SysDate,
			IsProcessing = 'N',
			Result = v_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 A_Asset_Create;
/