------------------------------------------------------------------------------
-- ADVANTAGE 2019.6.5.0000
-- Functions, Views, Stored Procedures
-- 2019-02-04
------------------------------------------------------------------------------

-- 6.5.2 Build
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AC_Get_Invoice_Terms_Due]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_AC_Get_Invoice_Terms_Due]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AC_Get_Statement_Terms_Due]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_AC_Get_Statement_Terms_Due]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AS_Transaction_Detail_Consignment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_AS_Transaction_Detail_Consignment]
GO

-- 6.5.3 Build
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RPT_AP_Barcode_Labels]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RPT_AP_Barcode_Labels]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RPT_AP_Barcode_Labels_Print]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RPT_AP_Barcode_Labels_Print]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AW_Select_Period_Procedures]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_AW_Select_Period_Procedures]
GO

-------------------------------------------------------------------------------
--Description   :sp_AC_Get_Statement_Terms_Due
--Purpose       :Select Customer Account Ageing Totals
--Parameters    :
--Date          :2004/01/04
--Authors       :TB
--Version       :1.0.0
--MODS          :25/06/03 - Removed @CompanyID to fn_AC_Customer_Ageing_Non_Grouped
--              :           to enable multi-company processing - superfluous
--              :22/09/03 - Added @CompanyID filter to sp_AW_Period_Procedures_Select
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AC_Get_Statement_Terms_Due]
	(
        @Date datetime,
        @CompanyID int,
        @CustomerID int,
        @DaysTerms int
    )

AS

SET NOCOUNT ON

    DECLARE
        @Midnight datetime,
        @D1 dateTime,
        @D2 datetime,
        @D3 datetime,
        @D4 datetime,
        @D5 datetime,
        @D6 datetime,
        @D7 datetime


-- SET @Choice = 2
-- SET @Date = '2003/10/10 12:19:00'
-- SET @CompanyID = 1
-- SET @CustomerID = 4
-- SET @DaysTerms = 21


    SET @Midnight = CONVERT(datetime, DATEADD(dd, 1, ROUND(CONVERT(float, @Date), 0, 1)))


    EXEC sp_AW_Period_Procedures_Select
                             @Midnight,
                             @D1 OUTPUT,
                             @D2 OUTPUT,
                             @D3 OUTPUT,
                             @D4 OUTPUT,
                             @D5 OUTPUT,
                             @D6 OUTPUT,
                             @D7 OUTPUT,
                             1,
                             @CompanyID

	SELECT
		CustomerID,
		SUM(Payment) AS [Payment],
		SUM(Amount) AS [Amount],
		SUM([Current]) AS [Current],
		SUM([30 Days]) AS [30 Days],
		SUM([60 Days]) AS [60 Days],
		SUM([90 Days]) AS [90 Days],
		SUM([120 Days]) AS [120 Days],
		SUM([150 Days]) AS [150 Days],
		SUM([180+ Days]) AS [180+ Days],
		(
			SELECT TOP 1 ISNULL(MIN(DATEDIFF(d, @Midnight, Transaction_Date + @DaysTerms)), 0)
            FROM AS_Transactions
        	WHERE AC_Customers_ID = @CustomerID AND
                  AW_User_Companies_ID = @CompanyID AND
                 (Sys_AW_Transaction_Types_ID = 3 OR        --Invoice
                  Sys_AW_Transaction_Types_ID = 4 OR        --Refund
                  Sys_AW_Transaction_Types_ID = 5 OR        --Journal
                  Sys_AW_Transaction_Types_ID = 7 OR        --Payment
                  Sys_AW_Transaction_Types_ID = 15 OR       --Interest Journal
                  Sys_AW_Transaction_Types_ID = 18 OR       --Settlement Discount
                  Sys_AW_Transaction_Types_ID = 21 OR       --Back Office Payment
                  Sys_AW_Transaction_Types_ID = 22) AND     --EFT Payment
                  ISNULL(Total_Amount, 0) + ISNULL(Payment_Amount, 0) <> 0  AND 
				  Transaction_Date <= @Date
		) AS [TotalDays]
		FROM dbo.fn_AC_Customer_Ageing_Non_Grouped
--        ( @CompanyID, @CustomerID, @Midnight, @D1, @D2, @D3, @D4, @D5, @D6, @D7 ) 
        ( @CustomerID, @Midnight, @D1, @D2, @D3, @D4, @D5, @D6, @D7 ) 
	GROUP BY CustomerID


SET NOCOUNT OFF
GO 

-------------------------------------------------------------------------------
--Description   :sp_AC_Get_Invoice_Terms_Due
--Purpose       :Select Customer Account Ageing and Overdue Invoice Totals
--Parameters    :
--Date          :2004/01/04
--Authors       :TB
--Version       :1.0.0
--MODS          :26/01/04 - Added @TotalDays and @TranCNT to calculate Days Overdue
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AC_Get_Invoice_Terms_Due]
	(
        @Choice tinyint,
        @Date datetime,
        @CompanyID int,
        @CustomerID int,
        @DaysTerms int,
        @Amount money OUTPUT,
        @TotalDays int OUTPUT,
        @TranCNT int OUTPUT
    )

AS

SET NOCOUNT ON

    DECLARE
        @Midnight datetime

-- SET @Choice = 2
-- SET @Date = '2003/10/10 12:19:00'
-- SET @CompanyID = 1
-- SET @CustomerID = 4
-- SET @DaysTerms = 21


    SET @Midnight = CONVERT(datetime, DATEADD(dd, 1, ROUND(CONVERT(float, @Date), 0, 1)))
    SET @Amount = 0

--> CUSTOMER On Invoice Terms amount DUE
    IF @Choice = 1
    	BEGIN

    		SELECT @Amount = ISNULL(SUM(Total_Amount), 0) + ISNULL(SUM(Payment_Amount), 0)
        	FROM AS_Transactions
        	WHERE AC_Customers_ID = @CustomerID AND
                  AW_User_Companies_ID = @CompanyID AND
                 (Sys_AW_Transaction_Types_ID = 3 OR        --Invoice
                  Sys_AW_Transaction_Types_ID = 4 OR        --Refund
                  Sys_AW_Transaction_Types_ID = 5 OR        --Journal
                  Sys_AW_Transaction_Types_ID = 7 OR        --Payment
                  Sys_AW_Transaction_Types_ID = 15 OR       --Interest Journal
                  Sys_AW_Transaction_Types_ID = 18 OR       --Settlement Discount
                  Sys_AW_Transaction_Types_ID = 21 OR       --Back Office Payment
                  Sys_AW_Transaction_Types_ID = 22) AND     --EFT Payment
                  ISNULL(Total_Amount, 0) + ISNULL(Payment_Amount, 0) <> 0 -- AND
                --> If Transaction EXACTLY @DaysTerms OLD
                  -- DATEDIFF(d, @Midnight, Transaction_Date + @DaysTerms) = 0

    	END

    ELSE
--> CUSTOMER On Invoice Terms amount OVERDUE
    IF @Choice = 2
    	BEGIN

    		SELECT @Amount = ISNULL(SUM(Total_Amount), 0) + ISNULL(SUM(Payment_Amount), 0),
                   @TotalDays = ISNULL(MIN(DATEDIFF(d, @Midnight, Transaction_Date + @DaysTerms)), 0),
                   @TranCNT = ISNULL(COUNT(Sys_AW_Transaction_Types_ID), 0)
        	FROM AS_Transactions
        	WHERE AC_Customers_ID = @CustomerID AND
                  AW_User_Companies_ID = @CompanyID AND
                 (Sys_AW_Transaction_Types_ID = 3 OR        --Invoice
                  Sys_AW_Transaction_Types_ID = 4 OR        --Refund
                  Sys_AW_Transaction_Types_ID = 5 OR        --Journal
                  Sys_AW_Transaction_Types_ID = 7 OR        --Payment
                  Sys_AW_Transaction_Types_ID = 15 OR       --Interest Journal
                  Sys_AW_Transaction_Types_ID = 18 OR       --Settlement Discount
                  Sys_AW_Transaction_Types_ID = 21 OR       --Back Office Payment
                  Sys_AW_Transaction_Types_ID = 22) AND     --EFT Payment
                  ISNULL(Total_Amount, 0) + ISNULL(Payment_Amount, 0) <> 0 
                --> If Transaction OLDER THAN @DaysTerms
					 AND Transaction_Date <= @Date
                  

    	END


SET NOCOUNT OFF
GO 


--------------------------------------------------------------------------------
--Description   :sp_AS_Transaction_Detail_Consignment
--Purpose       :Checks to see if product sold was consignment stock
--Functionality :SELECT 
--Parameters    :@AS_Transaction_Details_ID, @UserID, @CompanyID
--Date          :2015/04/10
--Version		:V6.2
--Authors       :RL
--MODS          :
--------------------------------------------------------------------------------

CREATE Procedure [dbo].[sp_AS_Transaction_Detail_Consignment]
    (
    	@ProductID				int,
    	@SoldQty				decimal(9,3),
    	@UserID					int,
		@CompanyID				int,
		@IsConsignmentStock		int Output    	
    )

AS
	DECLARE @ConsignmentStockQty		decimal(9,3),
			@Current_OnHandQty			decimal(9,3),
			@ConsignmentGRVQty			decimal(9,3),
			@OnHand						decimal(9,3),
			@AO_GRV_ID					int,
			@AO_GRV_Details_ID			int,
			@Con_AO_GRV_ID				int,
			@Con_AO_GRV_Details_ID		int,
			@Qty_Left					decimal(9,3),
			@ConsignmentGRVed			decimal(9,3),
			@CurrentConsignmentGRVQty	decimal(9,3),
			
			@Suppliers_ID				int,
			@AW_Transaction_Type		int,
    		@Order_ID					int,
    		@DateReceived				DateTime,
    		@DelNoteNo					varchar(20),
    		@SupplierRef				varchar(20),
    					
    		@AP_Products_ID				int,
    		@Det_Qty					decimal(9,3),
    		@Det_Unit_Price				money,
    		@Det_Trade_Discount_Amount	money,
    		@Det_Rebate_Discount_Amount	money,
    		@Det_Other_Discount_Amount	money,
    		@Det_Net_Amount				money,
    		@Det_Tax_Amount				Money,
    		@Det_Total_Cost				Money,
    		@Det_Tax_Code_ID			int,
			
    		@GRV_Qty					decimal(9,3),
    		@GRV_Unit_Price				money,
    		@GRV_Trade_Discount_Amount	money,
    		@GRV_Rebate_Discount_Amount	money,
    		@GRV_Other_Discount_Amount	money,
    		@GRV_Net_Amount				money,
    		@GRV_Tax_Amount				Money,
    		@GRV_Total_Cost				Money,
    		@GRV_DateReceived			DateTime,
    		@Comment					varchar(20),
    		@Slu_AW_Years_ID			int,  		
			@Slu_AW_Months_ID			int,
    		@Year						int,  		
			@Month						int,
			@ProductsQty				decimal(9,3),
			@ProductConsignmentStockQty		decimal(9,3),
			@ProductOffSite	decimal(9,3),
			
			-- Tax fix / 2019-01-28 
			@TaxRate decimal(5,3)
			
	SET @ConsignmentGRVQty = 0
	SET @IsConsignmentStock = 0
	SET @ConsignmentGRVed = 0
	SET @CurrentConsignmentGRVQty = 0
	
	SELECT @ConsignmentStockQty = dbo.fn_AP_Get_Consignment_Stock_Qty(@ProductID)
	
--Insert Into Temp_Debug Values('@ConsignmentStockQty',Convert(Varchar(100),@ConsignmentStockQty))  
 	SELECT @ProductOffSite = dbo.fn_AP_Get_Products_Out_On_Consignment(@ProductID)
 	SELECT @ProductOffSite = 0
 	
	SELECT  @Current_OnHandQty = On_Hand + @ProductOffSite- @ConsignmentStockQty,
			@OnHand = On_Hand
	FROM AP_Products
	WHERE AP_Products_ID = @ProductID
	
--Insert Into Temp_Debug Values('28 - @Current_OnHandQty',Convert(Varchar(100),@Current_OnHandQty))   	
--Insert Into Temp_Debug Values('28 - @OnHand',Convert(Varchar(100),@OnHand))   
--Insert Into Temp_Debug Values('28 - @SoldQty',Convert(Varchar(100),@SoldQty))  
 
--SELECT @ProductConsignmentStockQty = dbo.fn_AP_Get_Consignment_Stock_Qty(@ProductID)

--Insert Into Temp_Debug Values('28 - @ProductConsignmentStockQty',Convert(Varchar(100),@ProductConsignmentStockQty))   	


	SET @IsConsignmentStock = 0	
	IF @Current_OnHandQty < 0
		SET @Current_OnHandQty = 0
		
	IF @ConsignmentStockQty <> 0
	BEGIN
		IF @Current_OnHandQty < @SoldQty
		BEGIN
			SET @ConsignmentGRVQty = @SoldQty - @Current_OnHandQty
			IF @ConsignmentGRVQty > @ConsignmentStockQty
				SET @ConsignmentGRVQty = @ConsignmentStockQty
				
			SET @IsConsignmentStock = 1
		END
	END

--Insert Into Temp_Debug Values('28 - @Current_OnHandQty',Convert(Varchar(100),@Current_OnHandQty))   	
--Insert Into Temp_Debug Values('28 - @OnHand',Convert(Varchar(100),@OnHand))   
--Insert Into Temp_Debug Values('28 - @SoldQty',Convert(Varchar(100),@SoldQty))   	
	
--Select @ProductsQty = On_hand
--From AP_Products 
--Where AP_Products_ID = @ProductID

--Insert Into Temp_Debug Values('28 - @ProductsQty',Convert(Varchar(100),@ProductsQty))   	
	
--Insert Into Temp_Debug Values('@IsConsignmentStock',Convert(Varchar(100),@IsConsignmentStock))   	

	IF @IsConsignmentStock = 1
	BEGIN

		SELECT 
			m.AO_GRV_ID, 
			d.AO_GRV_Details_ID, 			
			m.AO_Suppliers_ID,
    		m.AO_Orders_ID,
    		m.Date_Received,
    		m.Delivery_Note_Number, 
    		m.Supplier_Reference,
    		d.Qty,
    		d.Qty_Left,
    		d.Unit_Price,
    		d.Trade_Discount_Amount,
    		d.Rebate_Discount_Amount,
    		d.Other_Discount_Amount,
    		d.Net_Amount,
    		d.Tax_Amount,
    		d.Total_Cost,
    		d.Sys_AW_Tax_Codes_ID	
		FROM AO_GRV m
			INNER JOIN AO_GRV_Details d
				ON m.AO_GRV_ID = d.AO_GRV_ID
		WHERE d.AP_Products_ID = @ProductID
		AND m.Sys_AW_Transaction_Types_ID = 31
		AND d.Qty_Left > 0
		ORDER BY m.Date_Received ASC, AO_GRV_Details_ID ASC
		
		DECLARE GetDataGRV CURSOR
		FOR
		SELECT 
			m.AO_GRV_ID, 
			d.AO_GRV_Details_ID, 			
			m.AO_Suppliers_ID,
    		m.AO_Orders_ID,
    		m.Date_Received,
    		m.Delivery_Note_Number, 
    		m.Supplier_Reference,
    		d.Qty,
    		d.Qty_Left,
    		d.Unit_Price,
    		d.Trade_Discount_Amount,
    		d.Rebate_Discount_Amount,
    		d.Other_Discount_Amount,
    		d.Net_Amount,
    		d.Tax_Amount,
    		d.Total_Cost,
    		d.Sys_AW_Tax_Codes_ID	
		FROM AO_GRV m
			INNER JOIN AO_GRV_Details d
				ON m.AO_GRV_ID = d.AO_GRV_ID
		WHERE d.AP_Products_ID = @ProductID
		AND m.Sys_AW_Transaction_Types_ID = 31
		AND d.Qty_Left > 0
		ORDER BY m.Date_Received ASC, AO_GRV_Details_ID ASC
		
		OPEN GetDataGRV
		FETCH NEXT FROM GetDataGRV INTO 
				@Con_AO_GRV_ID, 
				@Con_AO_GRV_Details_ID, 
				@Suppliers_ID,
				@Order_ID,
				@DateReceived,
				@DelNoteNo,
				@SupplierRef,
				@Det_Qty,
				@Qty_Left,
	  			@Det_Unit_Price,
    			@Det_Trade_Discount_Amount,
    			@Det_Rebate_Discount_Amount,
    			@Det_Other_Discount_Amount,
    			@Det_Net_Amount,
    			@Det_Tax_Amount,
    			@Det_Total_Cost,
    			@Det_Tax_Code_ID
							
		--Select * From AO_GRV_Details		
		WHILE @@Fetch_Status = 0
		BEGIN
		
--Insert Into Temp_Debug Values('28-1 - @Qty_Left',Convert(Varchar(100),@Qty_Left))   	
--Insert Into Temp_Debug Values('28-1 - @ConsignmentGRVQty',Convert(Varchar(100),@ConsignmentGRVQty))   	
--Insert Into Temp_Debug Values('28-1 - @CurrentConsignmentGRVQty',Convert(Varchar(100),@CurrentConsignmentGRVQty))   	
--Insert Into Temp_Debug Values('28-1 - @ConsignmentGRVed',Convert(Varchar(100),@ConsignmentGRVed))   	
--Insert Into Temp_Debug Values('28-1 - @GRV_Qty',Convert(Varchar(100),@GRV_Qty))   	
--Insert Into Temp_Debug Values('28-1 - @Det_Unit_Price',Convert(Varchar(100),@Det_Unit_Price)) 
--Insert Into Temp_Debug Values('28-1 - @Con_AO_GRV_ID',Convert(Varchar(100),@Con_AO_GRV_ID)) 	
		
			IF @Qty_Left > @ConsignmentGRVQty
			BEGIN
				SET @CurrentConsignmentGRVQty = @ConsignmentGRVQty
				SET @ConsignmentGRVed = @ConsignmentGRVed + @ConsignmentGRVQty
			END
			ELSE
			BEGIN
				SET @CurrentConsignmentGRVQty = @Qty_Left
				SET @ConsignmentGRVed = @ConsignmentGRVQty + @Qty_Left		
			END	
			
			SET @GRV_Qty = @CurrentConsignmentGRVQty
			SET @GRV_Unit_Price = @Det_Unit_Price
			
			SET @GRV_Trade_Discount_Amount = 0
			SET @GRV_Rebate_Discount_Amount = 0
			SET @GRV_Other_Discount_Amount = 0
			
--Insert Into Temp_Debug Values('28-2 - @Qty_Left',Convert(Varchar(100),@Qty_Left))   	
--Insert Into Temp_Debug Values('28-2 - @ConsignmentGRVQty',Convert(Varchar(100),@ConsignmentGRVQty))   	
--Insert Into Temp_Debug Values('28-2 - @CurrentConsignmentGRVQty',Convert(Varchar(100),@CurrentConsignmentGRVQty))   	
--Insert Into Temp_Debug Values('28-2 - @ConsignmentGRVed',Convert(Varchar(100),@ConsignmentGRVed))   	
--Insert Into Temp_Debug Values('28-2 - @GRV_Qty',Convert(Varchar(100),@GRV_Qty))   	

IF @GRV_Qty <> 0
BEGIN
			
			IF @Det_Qty <> 0 And @GRV_Qty <> 0
			BEGIN
				SET @GRV_Trade_Discount_Amount = (Convert(Decimal(12,3),@Det_Trade_Discount_Amount) / @Det_Qty) * @GRV_Qty
    			SET @GRV_Rebate_Discount_Amount = (Convert(Decimal(12,3),@Det_Rebate_Discount_Amount) / @Det_Qty) * @GRV_Qty
    			SET @GRV_Other_Discount_Amount = (Convert(Decimal(12,3),@Det_Other_Discount_Amount) / @Det_Qty) * @GRV_Qty
    		END
    		
    		SET @GRV_Net_Amount = (@GRV_Qty * Convert(Decimal(12,3),@GRV_Unit_Price) ) 
    																				- Convert(Decimal(12,3),@GRV_Trade_Discount_Amount)
    																				- Convert(Decimal(12,3),@GRV_Rebate_Discount_Amount)
    																				- Convert(Decimal(12,3),@GRV_Other_Discount_Amount)
			
			-- Get tax rate 														
			EXEC [sp_AW_Get_Tax_Rate] 'S', @TaxRate OUTPUT;		
    		SET @GRV_Total_Cost = Convert(Decimal(12,3),@GRV_Net_Amount) * Cast((@TaxRate / 100)+1 as Decimal(5,2))
    		SET @GRV_Tax_Amount = Convert(Decimal(12,3),@GRV_Total_Cost) - Convert(Decimal(12,3),@GRV_Net_Amount)    		
    		    		
    		SET @GRV_DateReceived = GETDATE() --Date received is set to today. If original date received is used it will affect the Supplier Ageing
		
		    SET @Comment = 'GRV ' + CONVERT(Varchar(16),@AO_GRV_ID)
		
			SET @Slu_AW_Months_ID = DATEPART(mm, @GRV_DateReceived)
			SET @Year = DATEPART(yy, @GRV_DateReceived)

			SELECT @Slu_AW_Years_ID = Slu_AW_Years_ID
			FROM Slu_AW_Years
			WHERE [Description] = CONVERT(varchar(10), @Year)

			IF @Slu_AW_Months_ID = 0 OR @Slu_AW_Years_ID = 0
			BEGIN
				SELECT	@Slu_AW_Months_ID = Slu_AW_Months_ID,
						@Slu_AW_Years_ID = Slu_AW_Years_ID
				FROM AW_User_Companies
				WHERE AW_User_Companies_ID = @CompanyID
			END

			--34 - Consignment Sold Begin
			SET @GRV_Net_Amount = @GRV_Net_Amount * -1
			SET @GRV_Tax_Amount = @GRV_Tax_Amount * -1
			SET @GRV_Total_Cost = @GRV_Total_Cost * -1
			SET @GRV_Trade_Discount_Amount = @GRV_Trade_Discount_Amount * -1
			SET @GRV_Rebate_Discount_Amount = @GRV_Rebate_Discount_Amount * -1
			SET @GRV_Other_Discount_Amount = @GRV_Other_Discount_Amount * -1
			SET @GRV_Qty = @GRV_Qty * -1

			EXEC @AO_GRV_ID = sp_AO_Insert_GRV    
    			@Suppliers_ID,
    			34, --@AW_Transaction_Type int,
    			@Order_ID,
    			@GRV_DateReceived,
    			@DelNoteNo,
    			@SupplierRef,
    			@GRV_Trade_Discount_Amount,
    			@GRV_Rebate_Discount_Amount,
    			@GRV_Other_Discount_Amount,
    			@GRV_Net_Amount,
    			@GRV_Tax_Amount,
    			@GRV_Total_Cost,
    			@Con_AO_GRV_ID,
    			@UserID,
    			@CompanyID        
    
--SELECT @ProductConsignmentStockQty = dbo.fn_AP_Get_Consignment_Stock_Qty(@ProductID)
--Insert Into Temp_Debug Values('(34 Begin)28 - @ProductConsignmentStockQty',Convert(Varchar(100),@ProductConsignmentStockQty))   	

--Select @ProductsQty = On_hand
--From AP_Products 
--Where AP_Products_ID = @ProductID
--Insert Into Temp_Debug Values('(34 Begin)28 - @ProductsQty',Convert(Varchar(100),@ProductsQty))   	
--Insert Into Temp_Debug Values('28 - @GRV_Qty',Convert(Varchar(100),@GRV_Qty))   	
--Insert Into Temp_Debug Values('28 - @GRV_Unit_Price',Convert(Varchar(100),@GRV_Unit_Price))   	
    
			EXEC @AO_GRV_Details_ID = sp_AO_Insert_OR_Update_GRV_Detail   
    			0, --@AO_Order_Details_ID int = 0, Order delt with when the consignment stock came in
    			@AO_GRV_ID,
    			@ProductID,
    			@GRV_Qty,
    			@GRV_Unit_Price, --@Unit_Last_Cost money,
    			@GRV_Trade_Discount_Amount,
    			@GRV_Rebate_Discount_Amount,
    			@GRV_Other_Discount_Amount,
    			@GRV_Net_Amount,
    			@Det_Tax_Code_ID,
    			@GRV_Tax_Amount,
    			@GRV_Total_Cost,
    			@Con_AO_GRV_Details_ID,
    			@UserID,
    			@CompanyID			

--SELECT @ProductConsignmentStockQty = dbo.fn_AP_Get_Consignment_Stock_Qty(@ProductID)
--Insert Into Temp_Debug Values('(34 End)28 - @ProductConsignmentStockQty',Convert(Varchar(100),@ProductConsignmentStockQty))   	

--Select @ProductsQty = On_hand
--From AP_Products 
--Where AP_Products_ID = @ProductID
--Insert Into Temp_Debug Values('(34 End)28 - @ProductsQty',Convert(Varchar(100),@ProductsQty))   	
		
			EXEC sp_AO_Insert_AO_Transactions
			   	@Suppliers_ID,
    			@SupplierRef,
    			34, --@Transaction_Type tinyint,		
    			@DelNoteNo, --@Invoice_No varchar(20),
    			@GRV_DateReceived, --@Invoice_Date datetime,
    			@AO_GRV_ID,
    			@GRV_Trade_Discount_Amount,
    			@GRV_Rebate_Discount_Amount,
    			@GRV_Other_Discount_Amount,
    			@GRV_Net_Amount,
    			@GRV_Tax_Amount,
    			@GRV_Total_Cost,
    			@Slu_AW_Months_ID,
    			@Slu_AW_Years_ID,
    			@UserID,
    			@CompanyID,
				0, --@GLAccountID int = 0,
				@Comment -- GRV + @AO_GRV_ID
	
			--34 - Consignment Sold End
			
			--19 - GRV Begin
			SET @GRV_Net_Amount = @GRV_Net_Amount * -1
			SET @GRV_Tax_Amount = @GRV_Tax_Amount * -1
			SET @GRV_Total_Cost = @GRV_Total_Cost * -1
			SET @GRV_Trade_Discount_Amount = @GRV_Trade_Discount_Amount * -1
			SET @GRV_Rebate_Discount_Amount = @GRV_Rebate_Discount_Amount * -1
			SET @GRV_Other_Discount_Amount = @GRV_Other_Discount_Amount * -1
			SET @GRV_Qty = @GRV_Qty * -1		
		
			EXEC @AO_GRV_ID = sp_AO_Insert_GRV    
    			@Suppliers_ID,
    			19, --@AW_Transaction_Type int,
    			@Order_ID,
    			@GRV_DateReceived,
    			@DelNoteNo,
    			@SupplierRef,
    			@GRV_Trade_Discount_Amount,
    			@GRV_Rebate_Discount_Amount,
    			@GRV_Other_Discount_Amount,
    			@GRV_Net_Amount,
    			@GRV_Tax_Amount,
    			@GRV_Total_Cost,
    			@Con_AO_GRV_ID,
    			@UserID,
    			@CompanyID    
    			    
--SELECT @ProductConsignmentStockQty = dbo.fn_AP_Get_Consignment_Stock_Qty(@ProductID)
--Insert Into Temp_Debug Values('(19 Begin)28 - @ProductConsignmentStockQty',Convert(Varchar(100),@ProductConsignmentStockQty))   	

--Select @ProductsQty = On_hand
--From AP_Products 
--Where AP_Products_ID = @ProductID
--Insert Into Temp_Debug Values('(19 Begin)28 - @ProductsQty',Convert(Varchar(100),@ProductsQty))   	
    
			EXEC @AO_GRV_Details_ID = sp_AO_Insert_OR_Update_GRV_Detail   
    			0, --@AO_Order_Details_ID int = 0, Order delt with when the consignment stock came in
    			@AO_GRV_ID,
    			@ProductID,
    			@GRV_Qty,
    			@GRV_Unit_Price, --@Unit_Last_Cost money,
    			@GRV_Trade_Discount_Amount,
    			@GRV_Rebate_Discount_Amount,
    			@GRV_Other_Discount_Amount,
    			@GRV_Net_Amount,
    			@Det_Tax_Code_ID,
    			@GRV_Tax_Amount,
    			@GRV_Total_Cost,
    			@Con_AO_GRV_Details_ID,
    			@UserID,
    			@CompanyID
    			
--SELECT @ProductConsignmentStockQty = dbo.fn_AP_Get_Consignment_Stock_Qty(@ProductID)
--Insert Into Temp_Debug Values('(19 End)28 - @ProductConsignmentStockQty',Convert(Varchar(100),@ProductConsignmentStockQty))   	

--Select @ProductsQty = On_hand
--From AP_Products 
--Where AP_Products_ID = @ProductID
--Insert Into Temp_Debug Values('(19 End)28 - @ProductsQty',Convert(Varchar(100),@ProductsQty))   	

		
			EXEC sp_AO_Insert_AO_Transactions
			   	@Suppliers_ID,
    			@SupplierRef,
    			8, --@Transaction_Type tinyint,		
    			@DelNoteNo, --@Invoice_No varchar(20),
    			@GRV_DateReceived, --@Invoice_Date datetime,
    			@AO_GRV_ID,
    			@GRV_Trade_Discount_Amount,
    			@GRV_Rebate_Discount_Amount,
    			@GRV_Other_Discount_Amount,
    			@GRV_Net_Amount,
    			@GRV_Tax_Amount,
    			@GRV_Total_Cost,
    			@Slu_AW_Months_ID,
    			@Slu_AW_Years_ID,
    			@UserID,
    			@CompanyID,
				0, --@GLAccountID int = 0,
				@Comment -- GRV + @AO_GRV_ID
        
        	UPDATE AO_GRV_Details
        	SET Qty_Sold = Qty_Sold + @GRV_Qty,
        		Qty_Left = Qty_Left - @GRV_Qty
        	WHERE AO_GRV_Details_ID = Convert(Int,@Con_AO_GRV_Details_ID)   
        	   	
        	--19 - GRV End
        	SET @ConsignmentGRVQty = @ConsignmentGRVQty - @GRV_Qty
END        		
			FETCH NEXT FROM GetDataGRV INTO 
					@Con_AO_GRV_ID, 
					@Con_AO_GRV_Details_ID, 
					@Suppliers_ID,
					@Order_ID,
					@DateReceived,
					@DelNoteNo,
					@SupplierRef,
					@Det_Qty,
					@Qty_Left,
	  				@Det_Unit_Price,
    				@Det_Trade_Discount_Amount,
    				@Det_Rebate_Discount_Amount,
    				@Det_Other_Discount_Amount,
    				@Det_Net_Amount,
    				@Det_Tax_Amount,
    				@Det_Total_Cost,
    				@Det_Tax_Code_ID


		END
		CLOSE GetDataGRV
		DEALLOCATE GetDataGRV
	
	END
	
--	RETURN @IsConsignmentStock
GO

--sp_RPT_AP_Barcode_Labels 1, -1, 8, -1, -1, -1, -1, 0, 0, 0
-------------------------------------------------------------------------------
--Description   :sp_RPT_AP_Barcode_Labels
--Purpose       :Inventory Barcode Labels
--Date          :2005/10/18
--Authors       :TB
--MODS:         :
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_RPT_AP_Barcode_Labels]
	(
    	@CompanyID int,
    	@SupplierID int = -1,
    	@ManufacturerID int = -1,
    	@TypeID int = -1,
    	@GroupID int = -1,
    	@DepartmentID int = -1,
      	@LocationID int = -1,
        @OnHandOnly int,
        @Sort int,
        @NonStock int
	)

AS

SET NOCOUNT ON

	DECLARE @Filter varchar(4000),
            @SQL varchar(4000)

	SET @Filter = ''
    SET @SQL = ''


    	IF @SupplierID > 0 
    		BEGIN

    			IF @Filter = ''
    				SET @Filter = ' WHERE ' + ' p.Default_Supplier_ID = ' +
                                    convert(varchar(20), @SupplierID)
    			ELSE
    				SET @Filter = @Filter + ' AND ' + ' p.Default_Supplier_ID = ' +
                                    convert(varchar(20), @SupplierID)
    		END
    --> Manufacturer
    	IF @ManufacturerID > 0 
    		BEGIN

    			IF @Filter = ''
    				SET @Filter = ' WHERE ' + ' p.Lup_AP_Product_Manufacturers_ID = ' +
                                    convert(varchar(20), @ManufacturerID)
    			ELSE
    				SET @Filter = @Filter + ' AND ' + ' p.Lup_AP_Product_Manufacturers_ID = ' +
                                    convert(varchar(20), @ManufacturerID)
    		END
    --> Type
    	IF @TypeID > 0 
    		BEGIN

    			IF @Filter = ''
    				SET @Filter = ' WHERE ' + ' p.Lup_AP_Product_Types_ID = ' +
                                    convert(varchar(20), @TypeID)
    			ELSE
    				SET @Filter = @Filter + ' AND ' + ' p.Lup_AP_Product_Types_ID = ' +
                                    convert(varchar(20), @TypeID)
    		END
    --> Group
    	IF @GroupID > 0 
    		BEGIN

    			IF @Filter = ''
    				SET @Filter = ' WHERE ' + ' p.Lup_AP_Product_Groups_ID = ' +
                                    convert(varchar(20), @GroupID)
    			ELSE
    				SET @Filter = @Filter + ' AND ' + ' p.Lup_AP_Product_Groups_ID = ' +
                                    convert(varchar(20), @GroupID)
    		END
    --> Department
    	IF @DepartmentID > 0 
    		BEGIN

    			IF @Filter = ''
    				SET @Filter = ' WHERE ' + ' p.Lup_AP_Product_Departments_ID = ' +
                                    convert(varchar(20), @DepartmentID)
    			ELSE
    				SET @Filter = @Filter + ' AND ' + ' p.Lup_AP_Product_Departments_ID = ' +
                                    convert(varchar(20), @DepartmentID)
    		END
    --> Location
    	IF @LocationID  > 0 
    		BEGIN

    			IF @Filter = ''
    				SET @Filter = ' WHERE ' + ' p.Lup_AP_Product_Locations_ID = ' +
                                    convert(varchar(20), @LocationID)
    			ELSE
    				SET @Filter = @Filter + ' AND ' + ' p.Lup_AP_Product_Locations_ID = ' +
                                    convert(varchar(20), @LocationID)
        	END

		IF @Filter = ''
            BEGIN
				SET @Filter = ' WHERE ' + ' p.AW_User_Companies_ID = ' +
                                convert(varchar(20), @CompanyID)
            END
		ELSE
            BEGIN
				SET @Filter = @Filter + ' AND ' + ' p.AW_User_Companies_ID = ' +
                                convert(varchar(20), @CompanyID)
            END

        IF @OnHandOnly = 1
            BEGIN
    			IF @Filter = ''
    				SET @Filter = ' WHERE ' + ' p.AW_User_Companies_ID = ' +
                                    convert(varchar(20), @CompanyID) +
                                  ' AND ' + ' p.On_Hand <> 0 '
    			ELSE
    				SET @Filter = @Filter + ' AND ' + ' p.On_Hand <> 0 '
            END


        IF @NonStock = 1
            BEGIN
                SET @Filter = @Filter + ' AND ' + ' p.Slu_AW_YesNo_Non_Stock_Item <> 1 '
            END


    	SET @SQL = 'SELECT '
        SET @SQL = @SQL + ' p.AW_User_Companies_ID AS [CompanyID], '
        SET @SQL = @SQL + ' p.AP_Products_ID AS [ID], '
        SET @SQL = @SQL + ' p.[Description] AS [Description], '
        SET @SQL = @SQL + ' p.Product_Code AS [ProductCode], '
        SET @SQL = @SQL + ' p.On_Hand AS [OnHand], '
        SET @sql = @sql + ' CAST(ISNULL(p.Product_Code, '''') '
        SET @sql = @sql + '     + '' ('' + CONVERT(varchar(10), p.AP_Products_ID) + '')'' AS VARCHAR) + '' / Price: '' + '
        SET @sql = @sql + ' CAST(CONVERT(VARCHAR, CAST((CASE WHEN p.Price2_Effective_Date IS NULL OR (p.Price1_Effective_Date > p.Price2_Effective_Date) THEN '
        SET @sql = @sql + '			p.Price1_Price1 '
        SET @sql = @sql + '		 ELSE p.Price2_Price1 END) AS MONEY), 1) AS VARCHAR) AS [CompositeCode] '
        SET @SQL = @SQL + ' FROM AP_Products p '
        SET @SQL = @SQL + @Filter

        IF @Sort = 1
            BEGIN
                SET @SQL = @SQL + ' ORDER BY p.Product_Code '
            END
        ELSE
            BEGIN

                SET @SQL = @SQL + ' ORDER BY p.[Description] '
            END


    EXEC (@SQL)

SET NOCOUNT OFF	
GO

-------------------------------------------------------------------------------
--Description   :[sp_RPT_AP_Barcode_Labels_Print]
--Purpose       :Barcode Labels Print
--Date          :2013/07/17
--Authors       :RL
--MODS:         :
-------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp_RPT_AP_Barcode_Labels_Print]
	(
    	@CompanyID int,
    	@AP_Products_ID int,
    	@AO_GRV_ID int,
        @Qty int
	)
AS

SET NOCOUNT ON

	DECLARE @Filter varchar(4000),
            @SQL varchar(4000),
            @NonStock int,
            @Sort int

	SET @Filter = ''
    SET @SQL = ''
	SET @NonStock = 0
	SET @Sort = 0
		
	SELECT @NonStock = Control_Value
	FROM Sys_AW_Options
	WHERE Control_Name = 'chkBarExcludeNoNStock'

	SELECT @Sort = Control_Value
	FROM Sys_AW_Options
	WHERE Control_Name = 'chkBarOrdProductCode'

	IF @Filter = ''
    BEGIN
		SET @Filter = ' AND ' + ' p.AW_User_Companies_ID = ' +
                                convert(varchar(20), @CompanyID)
	END
	ELSE
	BEGIN
		SET @Filter = @Filter + ' AND ' + ' p.AW_User_Companies_ID = ' +
                                convert(varchar(20), @CompanyID)
	END

	IF @NonStock = 1
	BEGIN
		SET @Filter = @Filter + ' AND ' + ' p.Slu_AW_YesNo_Non_Stock_Item <> 1 '
	END

	IF @AP_Products_ID <> 0
	BEGIN
    	SET @SQL = 'SELECT '
        SET @SQL = @SQL + ' p.AW_User_Companies_ID AS [CompanyID], '
        SET @SQL = @SQL + ' p.AP_Products_ID AS [ID], '
        SET @SQL = @SQL + ' p.[Description] AS [Description], '
        SET @SQL = @SQL + ' p.Product_Code AS [ProductCode], '
        SET @SQL = @SQL + ' ''' + CONVERT(varchar(20), @Qty) + ''' AS [OnHand], '
		SET @sql = @sql + ' CAST(ISNULL(p.Product_Code, '''') '
        SET @sql = @sql + '     + '' ('' + CONVERT(varchar(10), p.AP_Products_ID) + '')'' AS VARCHAR) + '' / Price: '' + '
        SET @sql = @sql + ' CAST(CONVERT(VARCHAR, CAST((CASE WHEN p.Price2_Effective_Date IS NULL OR (p.Price1_Effective_Date > p.Price2_Effective_Date) THEN '
        SET @sql = @sql + '			p.Price1_Price1 '
        SET @sql = @sql + '		 ELSE p.Price2_Price1 END) AS MONEY), 1) AS VARCHAR) AS [CompositeCode]'
        SET @SQL = @SQL + ' FROM AP_Products p '
        --SET @SQL = @SQL + ' WHERE p.AP_Products_ID = Cast( ''' + @AP_Products_ID + ''',varchar(10))'
        SET @SQL = @SQL + ' WHERE p.AP_Products_ID = ' + CONVERT(varchar(20), @AP_Products_ID) + ''
        SET @SQL = @SQL + ' AND IsNull(p.Slu_AW_YesNo_Levy_Item,0) = 0 '
       
        SET @SQL = @SQL + @Filter
    END    
	ELSE
	BEGIN
    	SET @SQL = 'SELECT '
        SET @SQL = @SQL + ' p.AW_User_Companies_ID AS [CompanyID], '
        SET @SQL = @SQL + ' p.AP_Products_ID AS [ID], '
        SET @SQL = @SQL + ' p.[Description] AS [Description], '
        SET @SQL = @SQL + ' p.Product_Code AS [ProductCode], '
        SET @SQL = @SQL + ' d.Qty AS [OnHand], '
        SET @sql = @sql + ' CAST(ISNULL(p.Product_Code, '''') '
        SET @sql = @sql + '     + '' ('' + CONVERT(varchar(10), p.AP_Products_ID) + '')'' AS VARCHAR) + '' / Price: '' + '
        SET @sql = @sql + ' CAST(CONVERT(VARCHAR, CAST((CASE WHEN p.Price2_Effective_Date IS NULL OR (p.Price1_Effective_Date > p.Price2_Effective_Date) THEN '
        SET @sql = @sql + '			p.Price1_Price1 '
        SET @sql = @sql + '		 ELSE p.Price2_Price1 END) AS MONEY), 1) AS VARCHAR) AS [CompositeCode]'
        SET @SQL = @SQL + ' FROM AP_Products p '
        SET @SQL = @SQL + ' INNER JOIN AO_GRV_Details d ON '
        SET @SQL = @SQL + ' p.AP_Products_ID = d.AP_Products_ID '        
        SET @SQL = @SQL + ' INNER JOIN AO_GRV g ON '
        SET @SQL = @SQL + ' g.AO_GRV_ID = d.AO_GRV_ID '        
        --SET @SQL = @SQL + ' WHERE g.AO_GRV_ID = Cast( ''' + @AO_GRV_ID + ''' ,varchar(10)) +'
        SET @SQL = @SQL + ' WHERE g.AO_GRV_ID = ' + CONVERT(varchar(20), @AO_GRV_ID) +''
        SET @SQL = @SQL + ' AND IsNull(p.Slu_AW_YesNo_Levy_Item,0) = 0 '               
        SET @SQL = @SQL + @Filter		
	END
        IF @Sort = 1
            BEGIN
                SET @SQL = @SQL + ' ORDER BY p.Product_Code '
            END
        ELSE
            BEGIN

                SET @SQL = @SQL + ' ORDER BY p.[Description] '
            END


    EXEC (@SQL)
    --Select @SQL

SET NOCOUNT OFF	
GO

------------------------------------------------------------------------------
--Description   :sp_AW_Select_Period_Procedures
--Purpose       :Retrieves list of Period Procedures in DESCENDING date order
--              :for Customer Statement Printing
--Parameters    :
--Date          :2002/03/05
--Authors       :TB
--Version       :1.0.0
--MODS          :24/04/02 - Select AW_Period_Procedures_ID, not ..Types_ID
--              :07/08/02 - Select ONLY Period Procedure Type 1 (Customers)
--2004.3.4      :09/03/07 - Added @ProcedureTypeID input parameter for Supplier
--              :           Period Procedure
------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AW_Select_Period_Procedures]
	(
        @ProcedureTypeID int,
        @CompanyID int
	)

AS

SET NOCOUNT ON

    SELECT
        AW_Period_Procedures_ID AS [ID],
        Sys_AW_Period_Procedure_Types_ID AS [TypesID], 
        [Description] + ' (' + CONVERT(varchar(20), Month_End_Date) + ')' AS [MonthEnd]
    FROM AW_Period_Procedures 
    WHERE AW_User_Companies_ID = @CompanyID AND
          Sys_AW_Period_Procedure_Types_ID = @ProcedureTypeID
    ORDER BY Month_End_Date DESC

SET NOCOUNT OFF
GO

