import_product.sql

Java写的ERP系统

				END IF;
			EXCEPTION WHEN OTHERS THEN NULL;
			END;
			--	Then Check Item (Name)
			BEGIN
				IF (Product_ID IS NULL AND i.H_ItemDesc IS NOT NULL) THEN
					InfoMsg := 'N';
					SELECT 	M_Product_ID, Name 
					  INTO 	Product_ID, Diagnostic
					FROM 	M_Product p 
					WHERE 	Name = i.H_ItemDesc			--
						AND (p.UPC=i.H_UPC OR (p.UPC IS NULL AND i.H_UPC IS NULL))
						AND NOT EXISTS (SELECT H_ItemDesc FROM I_061_Sync_Item x WHERE p.Name=x.H_ItemDesc
							GROUP BY H_ItemDesc HAVING COUNT(*)<>1)
						AND NOT EXISTS (SELECT Name FROM M_Product x WHERE p.Name=x.Name
							GROUP BY Name HAVING COUNT(*)<>1);
					Diagnostic := 'Name=' || Diagnostic || '|=|' || i.H_ItemDesc;
				END IF;
			EXCEPTION WHEN OTHERS THEN NULL;
			END;
			--	Then Check Help (WBK No)
			BEGIN
				IF (Product_ID IS NULL AND i.H_ItemDefn IS NOT NULL) THEN
					InfoMsg := 'H';
					SELECT 	M_Product_ID, Help 
					  INTO 	Product_ID, Diagnostic
					FROM 	M_Product p 
					WHERE 	Help = i.H_ItemDefn		-- 
						AND (p.UPC=i.H_UPC OR (p.UPC IS NULL AND i.H_UPC IS NULL))
						AND NOT EXISTS (SELECT H_ItemDefn FROM I_061_Sync_Item x WHERE p.Help=x.H_ItemDefn
							GROUP BY H_ItemDefn HAVING COUNT(*)<>1)
						AND NOT EXISTS (SELECT Help FROM M_Product x WHERE p.Help=x.Help
							GROUP BY Help HAVING COUNT(*)<>1);
					Diagnostic := 'Help=' || Diagnostic || '|=|' || i.H_ItemDefn;
				END IF;
			EXCEPTION WHEN OTHERS THEN NULL;
			END;

			--	No Product found
    	    IF (Product_ID IS NULL) THEN
        	    /**
            	 *  Insert new Product	*******************************************
	             */
				ResultStr := 'NewProduct';
        	    AD_Sequence_Next('M_Product', i.AD_Client_ID, NextNo);
            	--
				InfoMsg := 'OK_imported (' || NextNo || ')';
				INSERT INTO M_Product
					(M_Product_ID, AD_Client_ID, AD_Org_ID, IsActive,
					Created, CreatedBy, Updated, UpdatedBy,
					Value, 
					Name, 
					Description, 
					Help,
					UPC, SKU, C_UOM_ID,
					IsSummary, ProductType, IsStocked, IsPurchased, IsSold,
					M_Product_Category_ID, Classification,
					Volume, Weight, ShelfWidth, ShelfHeight, ShelfDepth,
					UnitsPerPallet, C_TaxCategory_ID,
					Discontinued, DiscontinuedBy)
				VALUES
					(NextNo, i.AD_Client_ID, i.AD_Org_ID, 'Y',
					SysDate, 0, SysDate, 0,
					SUBSTR(i.H_Item,1,40),		--	Value 
					SUBSTR(i.H_ItemDesc,1,60), 
					SUBSTR(i.H_ItemDesc,1,255), 
					SUBSTR(i.H_ItemDefn,1,2000),
					SUBSTR(i.H_UPC,1,30), SUBSTR(i.H_Commodity2,1,30), UOM_ID,
					'N', 'I', 'Y', 'Y', 'Y',
					Category_ID, SUBSTR(i.H_ItemClass ,1,1),
					0, 0, 0, 0, 0,
					0, TaxCategory_ID,
					Expiration, i.V_Expiration);

				--	Add PO Info
				IF (BPartner_ID IS NOT NULL) THEN
					ResultStr := 'NewProductPO';
					InfoMsg := InfoMsg || ' + PO(' || BPartner_ID || ')';
					INSERT INTO M_Product_PO
						(M_Product_ID, C_BPartner_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						C_UOM_ID, PriceList, PriceLastPO, C_Currency_ID,
						VendorProductNo, UPC, -- VendorCategory,
						Discontinued, DiscontinuedBy,
						Order_Min, Order_Pack, 
						PriceEffective, IsCurrentVendor)
					VALUES
						(NextNo, BPartner_ID,
						i.AD_Client_ID, i.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
						UOM_ID, i.V_OperAmt_T, 0, Currency_ID,
						SUBSTR(i.H_Commodity1,1,30), i.H_UPC, -- SUBSTR(,1,30),
 				    	Expiration, i.V_Expiration,
						i.H_Quantity_LotsizeMin, i.H_Quantity_LotsizeMlt, 
						i.V_Effective, 'Y');
				END IF;
				--
				ResultStr := 'NewProduct_OK';
    	        UPDATE  I_061_Sync_Item i
        	    SET     I_IsImported = 'Y', 
						I_ErrorMsg = SUBSTR(InfoMsg || ' #' || TotalNo || '# ' || Diagnostic,1,255),
						Processed = 'Y',
						Updated = SysDate
	            WHERE   CURRENT OF Cur_Import;
				NoImported := NoImported + 1;

	        ELSE
    	        /**
        	     *  Update Existing Product **********
            	 */
				--	Name, Description, Help
				ResultStr := 'UpdateProduct';
				InfoMsg := 'OK_updated ' || InfoMsg || '(' || Product_ID || ')';
				--	Value, Name, Description, Help, UPC, SKU
				UPDATE  M_Product
			      SET 	Value = SUBSTR(i.H_Item,1,40),
						Name = SUBSTR(i.H_ItemDesc,1,60),
						Description = SUBSTR(i.H_ItemDesc,1,255),
						Help = SUBSTR(i.H_ItemDefn,1,2000),
						UPC = i.H_UPC,
						SKU = i.H_Commodity2,
						Updated = SysDate,
						IsActive='Y'
				WHERE M_Product_ID=Product_ID;
				--	Classification	(null only)
	            IF (i.H_ItemClass IS NOT NULL) THEN
    	            UPDATE  M_Product
				      SET Classification = i.H_ItemClass
				    WHERE M_Product_ID=Product_ID AND Classification IS NULL;
	            END IF;
				--	Category		(null only)
				IF (i.H_ItemType IS NOT NULL) THEN
            	    UPDATE M_Product
			    	  SET M_Product_Category_ID=Category_ID
				   	WHERE M_Product_ID=Product_ID AND M_Product_Category_ID IS NULL;
				END IF;
        	    --  Status
            	IF (i.H_SyncInd = 'D') THEN
	                UPDATE  M_Product   SET IsActive='N'  WHERE M_Product_ID=Product_ID;
    	        ELSIF (i.H_ItemStatus = 'A') THEN
        	        UPDATE  M_Product   SET IsActive='Y'  WHERE M_Product_ID=Product_ID;
            	ELSIF (i.H_ItemStatus = 'O') THEN    --  Obsolete
                	UPDATE  M_Product   SET Discontinued='Y', DiscontinuedBy=i.V_Expiration WHERE M_Product_ID=Product_ID;
	            ELSIF (i.H_ItemStatus = 'I' OR i.H_ItemStatus = 'H' OR i.H_ItemStatus = 'D') THEN
    	            UPDATE  M_Product   SET IsActive='N'  WHERE M_Product_ID=Product_ID;
        	    END IF;

	            -- Product_PO ----------------
    	        SELECT COUNT(*) INTO No FROM M_Product_PO
        	      WHERE M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID;
            	--
	            IF (No = 0 AND BPartner_ID IS NOT NULL) THEN
    	            /**
        	         *  Create New Product_PO Record
            	     */
					ResultStr := 'UpdateProductNewPO';
					InfoMsg := InfoMsg || ' + insert PO(' || BPartner_ID || ')';
					INSERT INTO M_Product_PO
						(M_Product_ID, C_BPartner_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						C_UOM_ID, PriceList, PriceLastPO, C_Currency_ID,
						VendorProductNo, UPC, -- VendorCategory,
						Discontinued, DiscontinuedBy,
						Order_Min, Order_Pack, 
						PriceEffective, IsCurrentVendor)
					VALUES
						(Product_ID, BPartner_ID,
						i.AD_Client_ID, i.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
						UOM_ID, i.V_OperAmt_T, 0, Currency_ID,
						i.H_Commodity1, i.H_UPC, 
 					    Expiration, i.V_Expiration,
						i.H_Quantity_LotsizeMin, i.H_Quantity_LotsizeMlt, 
						i.V_Effective, 'Y');
	            ELSIF (No = 1) THEN
    	            /**
        	         *  Update Product_PO Record
            	     */
					ResultStr := 'UpdateProductPO';
					InfoMsg := InfoMsg || ' + update PO(' || BPartner_ID || ')';
    	            -- UOM			(null only)
        	        IF (i.H_UOM IS NOT NULL) THEN
            	        UPDATE M_Product_PO SET C_UOM_ID = UOM_ID
                	      WHERE M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID AND C_UOM_ID IS NULL;
	                END IF;
    	            --  Currency + Price + UPC + VendorNo
					UPDATE 	M_Product_PO 
					  SET 	C_Currency_ID=Currency_ID, 
					  		PriceList=i.V_OperAmt_T,
							VendorProductNo=i.H_Commodity1, 
							UPC=i.H_UPC,
						--	IsCurrentVendor = 'Y',
							Updated=SysDate,
							IsActive='Y'
					WHERE 	M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID;
            	    --  Effective 
                	IF (i.V_Effective IS NOT NULL) THEN
                    	UPDATE 	M_Product_PO 
						  SET	PriceEffective=i.V_Effective
    	                WHERE 	M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID;
        	        END IF;
            	    --  Pack + Min Qty	(null only)
                	IF (i.H_Quantity_LotsizeMlt IS NOT NULL) THEN
                    	UPDATE 	M_Product_PO 
						  SET 	Order_Pack=i.H_Quantity_LotsizeMlt
    	                WHERE 	M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID AND Order_Pack IS NULL;
        	        END IF;
            	    IF (i.H_Quantity_LotsizeMin IS NOT NULL) THEN
                	    UPDATE 	M_Product_PO 
						  SET 	Order_Min=i.H_Quantity_LotsizeMin
	                    WHERE 	M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID AND Order_Min IS NULL;
    	            END IF;
        	    	--  Status
            		IF (i.H_SyncInd = 'D') THEN
            		    UPDATE  M_Product_PO 
						  SET 	IsActive='N'  
	                    WHERE 	M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID;
    	    	    ELSIF (i.H_ItemStatus = 'A') THEN
    		            UPDATE  M_Product_PO 
						  SET 	IsActive='Y'  
                	    WHERE 	M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID;
		            ELSIF (i.H_ItemStatus = 'O') THEN    --  Obsolete
    	        	    UPDATE  M_Product_PO 
						  SET 	Discontinued='Y', 
						  		DiscontinuedBy=i.V_Expiration 
                	      WHERE M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID;
	        	    ELSIF (i.H_ItemStatus = 'I' OR i.H_ItemStatus = 'H' OR i.H_ItemStatus = 'D') THEN
    		            UPDATE  M_Product_PO 
						  SET 	IsActive='N'  
            	          WHERE M_Product_ID=Product_ID AND C_BPartner_ID=BPartner_ID;
	            	END IF;

	            END IF; --  Update Product_PO
    	        --
				ResultStr := 'UpdateProduct_OK';
	            UPDATE  I_061_Sync_Item i
				  SET	I_IsImported = 'Y', 
						I_ErrorMsg = SUBSTR(InfoMsg || ' #' || TotalNo || '# ' || Diagnostic,1,255),
						Processed='Y',
						Updated = SysDate
	            WHERE   CURRENT OF Cur_Import;
				NoUpdated := NoUpdated + 1;
        	    --  END Update Existing Product
	        END IF;

		--	Single Record Issue
		EXCEPTION WHEN OTHERS THEN
			Diagnostic := SUBSTR(SQLERRM || ': ' || InfoMsg || ' #' || TotalNo || '# ' || Diagnostic, 1,255);
	    	UPDATE  I_061_Sync_Item i
			  SET	I_ErrorMsg = Diagnostic,
					Updated = SysDate
	        WHERE   CURRENT OF Cur_Import;
		END;
		--
		TotalNo := TotalNo + 1;
    END LOOP;   --  MAIN LOOP

	ResultStr := 'Commit';
    COMMIT;

	--	Correct Discontinued for products with active vendor
	ResultStr := 'CorrectingDiscontinued';
    UPDATE 	M_Product p
	  SET	Discontinued='N', 
	  		DiscontinuedBy=NULL
	WHERE	Discontinued='Y'
	  AND EXISTS (SELECT * FROM M_Product_PO v 
	  				WHERE p.M_Product_ID=v.M_Product_ID AND Discontinued='N');  
	COMMIT;

	--	Delete Description for Products with same Name
	UPDATE	M_Product
	  SET	Description = NULL
	WHERE	Name = Description;
	COMMIT;

    --  Update AD_PInstance
<<END_PROCEDURE>>
--	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished');
--	DBMS_OUTPUT.PUT_LINE('  imported=' || NoImported || ', updated=' || NoUpdated);
    UPDATE  AD_PInstance
    SET Updated = SysDate,
        IsProcessing = 'N',
        Result = 1,                 -- success
        ErrorMsg = NoImported || ' imported, ' || NoUpdated || ' updated'
    WHERE   AD_PInstance_ID=PInstance_ID;
    COMMIT;
    RETURN;

EXCEPTION
    WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE(ResultStr || ': ' || SQLERRM);
		ResultStr := SUBSTR (ResultStr || ':' || SQLERRM,1,2000);
        UPDATE  AD_PInstance
        SET Updated = SysDate,
            IsProcessing = 'N',
            Result = 0,             -- failure
            ErrorMsg = ResultStr
        WHERE   AD_PInstance_ID=PInstance_ID;
        COMMIT;
        RETURN;

END Import_Product;
/