import_product.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE Import_Product
(
    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+CPM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: Import_Product.sql,v 1.5 2002/10/23 03:16:57 jjanke Exp $
 ***
 * Title:	Import Products
 * Description:
 *		Import Products from I_061_Sync_Item for Client_ID
 ************************************************************************/
AS
	--	Parameter
	CURSOR Cur_Parameter (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=PInstance
		AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
    Client_ID       NUMBER := 1000000;
	Record_ID       NUMBER;

	-- 
 	No              NUMBER;
    ResultStr       VARCHAR(2000);
    General_Error   EXCEPTION;
    NextNo          NUMBER;

	--	All items to be imported
    CURSOR  Cur_Import IS
        SELECT 	* 
		FROM 	I_061_Sync_Item
        WHERE 	AD_Client_ID=Client_ID 
		  AND	I_IsImported='N' AND I_ErrorMsg IS NULL
		ORDER BY I_ID
        FOR UPDATE;
	--	UPDATE 	I_061_Sync_Item SET I_IsImported='N', I_ErrorMsg = NULL, Processed='N'

    Product_ID      NUMBER;
    Currency_ID     NUMBER;
    UOM_ID          NUMBER;
	Expiration		CHAR(1) := 'N';
	Category_ID		NUMBER;
	TaxCategory_ID	NUMBER;
	BPartner_ID		NUMBER;
	--
	NoImported		NUMBER := 0;
	NoUpdated		NUMBER := 0;
	InfoMsg			VARCHAR2(60);
	--
	Diagnostic		VARCHAR2(255);
	TotalNo			Number := 0;

BEGIN
	--	Get Parameters
	ResultStr := 'ReadingParameters';
	FOR p IN Cur_Parameter (PInstance_ID) LOOP
		Record_ID := p.Record_ID;
		IF (p.ParameterName = 'AD_Client_ID') THEN
 			Client_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  AD_Client_ID=' || Client_ID);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
	 	END IF;
	END LOOP;	--	Get Parameter
--	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || Record_ID);


	--  Update AD_PInstance
--	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing');
    ResultStr := 'PInstanceNotFound';    
    UPDATE 	AD_PInstance
      SET 	Created = SysDate,
        	IsProcessing = 'Y'
    WHERE 	AD_PInstance_ID=PInstance_ID;
    COMMIT;

    --  Do we have something to process?
    ResultStr := 'NoRecords';    
    SELECT  COUNT(*) 
	  INTO 	No
    FROM    I_061_Sync_Item
    WHERE   AD_Client_ID = Client_ID
      AND 	I_IsImported = 'N';
    IF (No = 0) THEN
        RAISE General_Error;
    END IF;

    --  Reset ErrorMessages
    UPDATE  I_061_Sync_Item
      SET   I_ErrorMsg = NULL
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N';
    --  Ignore not active records
    UPDATE  I_061_Sync_Item
      SET   I_ErrorMsg = 'I_Ignored'
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N' 
      AND   I_ErrorMsg IS NULL AND IsActive='N';
    COMMIT;

	--	Check Value/H_Item
    UPDATE  I_061_Sync_Item i
      SET   I_ErrorMsg = 'IE_NOItem'
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N' AND I_ErrorMsg IS NULL
		AND H_Item IS NULL;

    --  Check UOM	--
    UPDATE  I_061_Sync_Item i
      SET   H_UOM = 'EA'				--	Default UOM hardcoded
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N' AND H_UOM IS NULL;	
	--
    UPDATE  I_061_Sync_Item i
      SET   I_ErrorMsg = 'IE_UOMInvalid'
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N' AND I_ErrorMsg IS NULL
      AND NOT EXISTS (SELECT * FROM C_UOM u WHERE TRIM(i.H_UOM)=TRIM(u.X12DE355));

    --  Check CUR	--
	UPDATE  I_061_Sync_Item i			--	Default Cur from AcctSchema
      SET   V_OperAmt_T_Cur = (SELECT ISO_Code FROM AD_ClientInfo ci,C_AcctSchema a,C_Currency c
								WHERE ci.C_AcctSchema1_ID=a.C_AcctSchema_ID
								AND a.C_Currency_ID=c.C_Currency_ID
								AND ci.AD_Client_ID=Client_ID AND RowNum=1)
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N' AND V_OperAmt_T_Cur IS NULL;
	--    
	UPDATE  I_061_Sync_Item i
      SET   I_ErrorMsg = 'IE_CurInvalid'
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N' AND I_ErrorMsg IS NULL
      AND NOT EXISTS (SELECT * FROM C_Currency c WHERE i.V_OperAmt_T_Cur=c.ISO_Code);

    --  Check PartnrID  -- (No default)
    UPDATE  I_061_Sync_Item i
      SET   I_ErrorMsg = 'IE_BPartnerInvalid'
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N' AND I_ErrorMsg IS NULL
      AND NOT EXISTS (SELECT * FROM C_BPartner p WHERE i.H_PartnrID=p.Value)
	  AND i.H_PartnrID IS NOT NULL;

	--	Check ItemType
    UPDATE  I_061_Sync_Item i			--	Default Item Type
      SET   H_ItemType = (SELECT Value FROM M_Product_Category 
							WHERE IsDefault='Y' AND AD_Client_ID=Client_ID AND RowNum=1)
    WHERE   AD_Client_ID=Client_ID AND I_IsImported='N' AND H_ItemType IS NULL;	
	--
    UPDATE  I_061_Sync_Item i
      SET   I_ErrorMsg = 'IE_ProdCategoryInvalid'
    WHERE   AD_Client_ID=Client_ID 
	  AND 	I_IsImported='N' AND I_ErrorMsg IS NULL
      AND NOT EXISTS (SELECT * FROM M_Product_Category p WHERE i.H_ItemType=p.Value)
	  AND i.H_ItemType IS NOT NULL;

  	--	Check Vendor/VendorNo uniqueness
    UPDATE  I_061_Sync_Item i
      SET   I_ErrorMsg = 'IE_VendorNoNotUnique'
    WHERE   AD_Client_ID=Client_ID 
	  AND 	I_IsImported='N' AND I_ErrorMsg IS NULL
	  AND 	(H_PartnrID, H_Commodity1) IN
		(SELECT H_PartnrID, H_Commodity1 FROM I_061_Sync_Item
		GROUP BY H_PartnrID, H_Commodity1 HAVING COUNT(*) > 1);

	--	Check Value uniqueness
    UPDATE  I_061_Sync_Item i
      SET   I_ErrorMsg = 'IE_ItemValueNotUnique'
    WHERE   AD_Client_ID=Client_ID 
	  AND 	I_IsImported='N' AND I_ErrorMsg IS NULL
	  AND 	H_Item IN
		(SELECT H_Item FROM I_061_Sync_Item
		GROUP BY H_Item HAVING COUNT(*) > 1);


	--	Make sure UPC is not just zeroes
	UPDATE	I_061_Sync_Item i
	  SET	H_UPC = NULL
	WHERE	SUBSTR(H_UPC,1,8) = '00000000';
	--	
    COMMIT;


	--	Default TaxCategory
	SELECT	C_TaxCategory_ID 
	  INTO	TaxCategory_ID
	FROM	C_TaxCategory 
	WHERE	IsDefault='Y' 
	  AND	AD_Client_ID=Client_ID
	  AND 	RowNum=1;
	IF (TaxCategory_ID IS NULL)	THEN --	Mandatory - get something
		SELECT	C_TaxCategory_ID 
		  INTO	TaxCategory_ID
		FROM	C_TaxCategory 
		WHERE 	AD_Client_ID=Client_ID
		  AND	RowNum=1;
	END IF;

    /**
     *  Main Loop
     */
    FOR i IN Cur_Import LOOP
		BEGIN
			--	Reset Info
			Diagnostic := '';
			InfoMsg := '';

	        --  Get UOM ID	
			ResultStr := 'Loop-UOM:' || i.H_UOM;
    	    SELECT 	C_UOM_ID 
			  INTO 	UOM_ID 
			FROM 	C_UOM 
			WHERE 	TRIM(X12DE355)=TRIM(i.H_UOM) AND RowNum=1;
			--	Get Currency ID
			ResultStr := 'Loop-CUR:' || i.V_OperAmt_T_Cur;
        	SELECT 	C_Currency_ID 
			  INTO 	Currency_ID 
			FROM 	C_Currency 
			WHERE 	ISO_Code=i.V_OperAmt_T_Cur AND RowNum=1;
			--	Get Category ID
 			ResultStr := 'Loop-Cat:' || i.H_ItemType;
			Category_ID := NULL;
  			IF (i.H_ItemType IS NOT NULL) THEN
				SELECT 	M_Product_Category_ID 
				  INTO 	Category_ID 
				FROM 	M_Product_Category 
				WHERE 	Value=i.H_ItemType AND RowNum=1;
			END IF;
			--	Get BPartner_ID
			BPartner_ID := NULL;
			BEGIN
				SELECT	C_BPartner_ID 	
				  INTO 	BPartner_ID
				FROM	C_BPartner
				WHERE	Value=i.H_PartnrID;
			EXCEPTION WHEN OTHERS THEN NULL;
			END;
			--	Get Expiration
			IF (i.H_ItemStatus = 'O') THEN		--	Obsolete
				Expiration := 'Y';
			ELSE
				Expiration := 'N';
			END IF;

			--	---------------------------
    	    --  == Identify/find Product ==
			--	---------------------------
			ResultStr := 'Loop-Product';
			Product_ID := NULL;
			--	First Check UPC
			BEGIN
				IF (i.H_UPC IS NOT NULL) THEN
					InfoMsg := 'U';
					SELECT 	M_Product_ID, UPC 
					  INTO 	Product_ID, Diagnostic
					FROM 	M_Product p 
					WHERE UPC=i.H_UPC					-- should be unique
						AND NOT EXISTS (SELECT H_UPC FROM I_061_Sync_Item x WHERE p.UPC=x.H_UPC
							GROUP BY H_UPC HAVING COUNT(*)<>1)
						AND NOT EXISTS (SELECT UPC FROM M_Product x WHERE p.UPC=x.UPC
							GROUP BY UPC HAVING COUNT(*)<>1);
					Diagnostic := 'UPC=' || Diagnostic || '|=|' || i.H_UPC;
					--	Make sure that we can overwrite product
					UPDATE M_Product p
					  SET  	Value = Value || '@',
					  		Description = Description || ' UPC->' || i.H_UPC,
					  		IsActive = 'N'
					WHERE 	p.Value=i.H_Item				--	the unique value
					  AND	p.M_Product_ID <> Product_ID;	-- 	but not the same product
					--
					UPDATE 	M_Product_PO 
					  SET	VendorProductNo = VendorProductNo || '@'
					WHERE	C_BPartner_ID=BPartner_ID AND VendorProductNo=i.H_Commodity1	-- unique value
					  AND	M_Product_ID <> Product_ID;		--	 but not same product
				END IF;
			EXCEPTION WHEN OTHERS THEN NULL;
			END;
			--	Then Check Vendor/Product 
			BEGIN
				IF (Product_ID IS NULL AND BPartner_ID IS NOT NULL) THEN
					InfoMsg := 'P';
					SELECT 	M_Product_ID, C_BPartner_ID||','||VendorProductNo 
					  INTO 	Product_ID, Diagnostic
					FROM 	M_Product_PO po 
					WHERE 	C_BPartner_ID=BPartner_ID 	-- should be unique
				  		AND VendorProductNo=i.H_Commodity1
						AND (po.UPC=i.H_UPC OR (po.UPC IS NULL AND i.H_UPC IS NULL))
						AND NOT EXISTS (SELECT H_PartnrID,H_Commodity1 FROM I_061_Sync_Item x 
							WHERE i.H_PartnrID=x.H_PartnrID AND po.VendorProductNo=x.H_Commodity1
							GROUP BY H_PartnrID,H_Commodity1 HAVING COUNT(*)<>1);
					Diagnostic := 'Vendor=' || Diagnostic || '|=|' || BPartner_ID || '/' || i.H_PartnrID || ',' || i.H_Commodity1;
				END IF;
			EXCEPTION WHEN OTHERS THEN NULL;
			END;
			--	Then Check Item (Value)
			BEGIN
				IF (Product_ID IS NULL AND i.H_ITEM IS NOT NULL) THEN
					InfoMsg := 'V';
					SELECT 	M_Product_ID, Value 
					  INTO 	Product_ID, Diagnostic
					FROM 	M_Product p 
					WHERE 	Value = i.H_Item
					--	AND p.UPC IS NULL	--	don't overwrite Products with UPC
						AND NOT EXISTS (SELECT H_Item FROM I_061_Sync_Item x WHERE i.H_Item=x.H_Item
							GROUP BY H_Item HAVING COUNT(*)<>1);
					Diagnostic := 'Value=' || Diagnostic || '|=|' || i.H_Item;