-------------------------------------------------------------------------------
--Description   :4. 2004.3.8 changes since 2004.3.7.sql
--Purpose       :MOVE Product Dept Min GP to Profit Protector
--Functionality :1. MOVE Product Dept Min GP to Profit Protector
--              :        WHERE Product Dept Min GP is USED (MinGP% > 0)
--              :        WHERE POS System Option 'chkMinPOSGPPerc(0)' to
--              :              Prevent discounting below MINIMUM allowable GP %
--              :              is Checked
--              :2. DELETE the old system option
--              :Uses 2 CURSORS
--Date          :2008/05/31
--Authors       :TB
--MODS:         :2004.3.8
-------------------------------------------------------------------------------

DECLARE @DeptID int,
        @MinGP decimal(7, 2),
        @MoveCNT int,
        @UpdateCNT int,
        @SysOptionValue tinyint,
        @ProductID int


SET NOCOUNT ON


    SET @MoveCNT = 0
    SET @UpdateCNT = 0
    SET @SysOptionValue = 0


    SELECT @SysOptionValue = Control_Value
    FROM Sys_AW_Options
    WHERE Control_Type = 'CheckBox' AND
          Control_Name = 'chkMinPOSGPPerc' AND
          Control_Index = 0


    --> SELECT Product Departments
    	DECLARE Lookup_Department CURSOR FOR
            (
                SELECT Lup_AP_Product_Departments_ID,
                       ISNULL(Min_GP_Percentage, 0)
                FROM Lup_AP_Product_Departments
            )
    
    	OPEN Lookup_Department
    
    	FETCH NEXT FROM Lookup_Department 
    	INTO
            @DeptID,
            @MinGP
    	
    	WHILE @@FETCH_STATUS = 0
        	BEGIN
                IF ISNUMERIC(@MinGP) = 1
                    BEGIN
                    --> MOVE Product Departments to Profit Protector
                    	DECLARE Product_Department CURSOR FOR
                            (
                                SELECT AP_Products_ID
                                FROM AP_Products
                                WHERE Lup_AP_Product_Departments_ID = @DeptID
                            )
                    
                    	OPEN Product_Department
                    
                    	FETCH NEXT FROM Product_Department 
                    	INTO
                            @ProductID
                    	
                    	WHILE @@FETCH_STATUS = 0
                        	BEGIN
                                UPDATE AP_Products
                                SET Max_Discount_Perc = @MinGP,
                                    Slu_AW_YesNo_Max_Discount_Perc = @SysOptionValue
                                WHERE AP_Products_ID = @ProductID
    
                                SET @UpdateCNT = @UpdateCNT + 1
    
                            	FETCH NEXT FROM Product_Department 
                            	INTO
                                    @ProductID
                        	END
    
                        SET @MoveCNT = @MoveCNT + 1
    
                    	CLOSE Product_Department
    
                    	DEALLOCATE Product_Department
                    END
    
            	FETCH NEXT FROM Lookup_Department 
            	INTO
                    @DeptID,
                    @MinGP
        	END
    	
    	CLOSE Lookup_Department
    
    	DEALLOCATE Lookup_Department



--PRINT 'Moved Departments ' + CONVERT(varchar(30), @MoveCNT) + ' with Products ' + CONVERT(varchar(30), @UpdateCNT)


------------------------------------

IF EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkMinPOSGPPerc' AND
              Control_Index = 0
    )
    BEGIN
        DELETE
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkMinPOSGPPerc' AND
              Control_Index = 0
    END

------------------------------------