------------------------------------------------------------------------------
-- ADVANTAGE 2004.5.8
-- NEW OPTIONS, and minor bug fixes
-- 2013-02-21
-- RGL
------------------------------------------------------------------------------
--5.7 Fix

IF NOT EXISTS
(
  SELECT * 
  FROM 
    SysObjects O INNER JOIN SysColumns C
  ON O.ID = C.ID
  WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
        O.Name='Lup_AP_AW_Weight' AND
        C.Name='AW_User_Companies_ID'
) 
ALTER TABLE Lup_AP_AW_Weight ADD AW_User_Companies_ID [int] NULL
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[Lup_AP_AW_Weight].[AW_User_Companies_ID]'
GO


--1
IF NOT EXISTS (SELECT * 
				FROM dbo.sysobjects 
				WHERE id = object_id(N'[dbo].[AS_Transactions_Address]') 
				AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN           
	CREATE TABLE [dbo].[AS_Transactions_Address]
	(
		[AS_Transactions_Address_ID] [int] IDENTITY(1,1) NOT NULL,
		[AS_Transactions_ID] [int] NOT NULL,
		[AS_Transactions_Address_Detail_ID] [int] NOT NULL,	
		[Date_Captured] [datetime] NOT NULL,
		[User_ID] [int] NOT NULL,
		[AW_User_Companies_ID] [int] NOT NULL,
		[Touched] [timestamp] NULL
	CONSTRAINT [PK_AS_Transactions_Address_ID] PRIMARY KEY NONCLUSTERED 
	(
		[AS_Transactions_Address_ID] ASC
	)
	)
	
	CREATE  INDEX [IX_AS_Transactions_ID] ON [dbo].[AS_Transactions_Address]([AS_Transactions_ID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
END
GO        

IF NOT EXISTS (SELECT * 
				FROM dbo.sysobjects 
				WHERE id = object_id(N'[dbo].[AS_Transactions_Address_Detail]') 
				AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN          
	CREATE TABLE [dbo].[AS_Transactions_Address_Detail](
		[AS_Transactions_Address_Detail_ID] [int] IDENTITY(1,1) NOT NULL,
		[AC_Customers_ID] [int] NOT NULL,
		[Inv_Address1] [varchar](40) NULL,
		[Inv_Address2] [varchar](40) NULL,
		[Inv_City] [varchar](30) NULL,
		[Inv_Code] [varchar](10) NULL,		
		[Delivery_Address] [varchar](40) NULL,
		[Delivery_Suburb] [varchar](40) NULL,
		[Delivery_City] [varchar](30) NULL,
		[Delivery_Post_Code] [varchar](10) NULL,
		[Date_Captured] [datetime] NOT NULL,
		[User_ID] [int] NOT NULL,
		[AW_User_Companies_ID] [int] NOT NULL,
		[Touched] [timestamp] NULL
	CONSTRAINT [PK_AS_Transactions_Address_Detail] PRIMARY KEY NONCLUSTERED 
	(
		[AS_Transactions_Address_Detail_ID] ASC
	)
	)
	
	CREATE  INDEX [IX_AC_Customers_ID] ON [dbo].[AS_Transactions_Address_Detail]([AC_Customers_ID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
END
GO

--2
IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AS_Insert_Transactions_Address]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AS_Insert_Transactions_Address]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------------------------------
--Description   :sp_AS_Insert_Transactions_Address
--Purpose       :Keeps a record of address details used on Invoices
--Functionality :Links Address details to an invoice
--Date          :2013-01-02
--Authors       :RL
------------------------------------------------------------------------------- 
CREATE Procedure [dbo].[sp_AS_Insert_Transactions_Address]
    (    	    	
    	@AS_Transactions_ID int,
		@Inv_Address1 varchar(40),
		@Inv_Address2 varchar(40),
		@Inv_City varchar(30),
		@Inv_Code varchar(10),
		@Delivery_Address varchar(40),
		@Delivery_Suburb varchar(40),
		@Delivery_City varchar(30),
		@Delivery_Post_Code varchar(10)
    )
AS 

	SET NOCOUNT ON
    
	DECLARE 
		@AS_Transactions_Address_Detail_ID	int,
		@AC_Customers_ID	int,
    	@User_ID	int,
    	@Company_ID	int

    SELECT 
		@AC_Customers_ID = AC_Customers_ID,
		@User_ID = User_ID,
		@Company_ID = AW_User_Companies_ID
	FROM AS_Transactions
	WHERE AS_Transactions_ID = @AS_Transactions_ID

	SET @AS_Transactions_Address_Detail_ID = 0

	SET @Inv_Address1 = LTRIM(RTRIM(ISNULL(@Inv_Address1,'')))
	SET @Inv_Address2 = LTRIM(RTRIM(ISNULL(@Inv_Address2,'')))
	SET @Inv_Code = LTRIM(RTRIM(ISNULL(@Inv_Code,'')))
	SET @Inv_City = LTRIM(RTRIM(ISNULL(@Inv_City,'')))
	SET @Delivery_Address = LTRIM(RTRIM(ISNULL(@Delivery_Address,'')))
	SET @Delivery_Suburb = LTRIM(RTRIM(ISNULL(@Delivery_Suburb,'')))
	SET @Delivery_City = LTRIM(RTRIM(ISNULL(@Delivery_City,'')))
	SET @Delivery_Post_Code = LTRIM(RTRIM(ISNULL(@Delivery_Post_Code,'')))

	SELECT @AS_Transactions_Address_Detail_ID = AS_Transactions_Address_Detail_ID
	FROM AS_Transactions_Address_Detail
	WHERE AC_Customers_ID = @AC_Customers_ID AND
		  Inv_Address1 = @Inv_Address1 AND
		  Inv_Address2 = @Inv_Address2 AND
		  Inv_Code = @Inv_Code AND
		  Inv_City = @Inv_City AND
		  Delivery_Address = @Delivery_Address AND
		  Delivery_Suburb = @Delivery_Suburb AND
		  Delivery_City = @Delivery_City AND
		  Delivery_Post_Code = @Delivery_Post_Code

	IF @AS_Transactions_Address_Detail_ID = 0
	BEGIN
		INSERT INTO AS_Transactions_Address_Detail
			(
				AC_Customers_ID, 
				Inv_Address1, 
				Inv_Address2, 
				Inv_Code, 
				Inv_City, 
				Delivery_Address, 
				Delivery_Suburb, 
				Delivery_City, 
				Delivery_Post_Code, 
				Date_Captured, 
				User_ID, 
				AW_User_Companies_ID
			)
		VALUES
			(
				@AC_Customers_ID,
				@Inv_Address1,
				@Inv_Address2,
				@Inv_Code,
				@Inv_City,
				@Delivery_Address,
				@Delivery_Suburb,
				@Delivery_City,
				@Delivery_Post_Code,
				GETDATE(),
				@User_ID,
    			@Company_ID
			)
			
		SELECT @AS_Transactions_Address_Detail_ID = Scope_Identity()
		
		INSERT INTO AS_Transactions_Address
			(
				AS_Transactions_ID, 
				AS_Transactions_Address_Detail_ID, 
				Date_Captured, 
				User_ID, 
				AW_User_Companies_ID
			)
		VALUES
			(
				@AS_Transactions_ID,
				@AS_Transactions_Address_Detail_ID,
				GETDATE(),
				@User_ID,
    			@Company_ID
			)	
	END
	ELSE
	BEGIN
		INSERT INTO AS_Transactions_Address
			(
				AS_Transactions_ID, 
				AS_Transactions_Address_Detail_ID, 
				Date_Captured, 
				User_ID, 
				AW_User_Companies_ID
			)
		VALUES
			(
				@AS_Transactions_ID,
				@AS_Transactions_Address_Detail_ID,
				GETDATE(),
				@User_ID,
    			@Company_ID
			)		
	END
Go

--3

IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AS_Select_Transactions_Address]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AS_Select_Transactions_Address]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------------------------------
--Description   :sp_AS_Select_Transactions_Address
--Purpose       :Select address details used on Invoices
--Functionality :Select address details used on Invoices
--Date          :2013-01-02
--Authors       :RL
------------------------------------------------------------------------------- 
CREATE Procedure [dbo].[sp_AS_Select_Transactions_Address]
    (    	    	
    	@AS_Transactions_ID int
    )
AS 

	SET NOCOUNT ON

	IF EXISTS
		(
			SELECT 1
			FROM AS_Transactions_Address
			WHERE AS_Transactions_ID = @AS_Transactions_ID
		)
	BEGIN
		SELECT AS_Transactions_ID,
			   Inv_Address1 AS 'Inv_Address1',
			   Inv_Address2 AS 'Inv_Address2',
			   Inv_City AS 'Inv_City',
			   Inv_Code AS 'Inv_Code',			   
			   Delivery_Address AS 'Delivery_Address',
			   Delivery_Suburb AS 'Delivery_Suburb',
			   Delivery_City AS 'Delivery_City',
			   Delivery_Post_Code AS 'Delivery_Post_Code'
		FROM AS_Transactions_Address_Detail d
			INNER JOIN AS_Transactions_Address a 
				ON a.AS_Transactions_Address_Detail_ID = d.AS_Transactions_Address_Detail_ID	
		WHERE a.AS_Transactions_ID = @AS_Transactions_ID
	END
	ELSE
	BEGIN
		SELECT 
			0 AS 'AS_Transactions_ID',
			'' AS 'Inv_Address1',
			'' AS 'Inv_Address2',
			'' AS 'Inv_City',
			'' AS 'Inv_Code',			
			'' AS 'Delivery_Address',
			'' AS 'Delivery_Suburb',
			'' AS 'Delivery_City',
			'' AS 'Delivery_Post_Code'
	END
Go

--4
--Increase field size. Tierpoint were getting errors do to the fact that they use GRV
--to invoice there callout KM traveled
Alter Table AO_Order_Details
Alter Column Received_Qty Decimal(19,3)
GO

--5
IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AO_Insert_OR_Update_GRV_Detail]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AO_Insert_OR_Update_GRV_Detail]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------------------
--Description   :sp_AO_Insert_OR_Update_GRV_Detail
--Purpose       :Inserts GRV Detail and Updates Inventory Last_Cost, On_Hand, and
--              :        REVAL/DEVAL Stock Adjustment in sp_AP_Update_Inventory_Details
--Functionality :
--Parameters    :
--Date          :2002/03/27
--Authors       :TB
--Version       :1.0.0
--Mods          :21/05/02 - Declared ALL decimals size (9,3)
--              :21/05/02 - Fixed this: On_Hand was only updated
--                              if @Prev_Last_Cost <> @New_Last_Cost
--              :21/05/02 - Moved the Reval/Deval Adjustment to
--              :               sp_AP_Update_Inventory_Details
--              :04/06/02 - Added text to @Note before sp_AP_Update_Inventory_Details
--              :               is called
--              :31/05/03 - Renamed @Unit_Price to @Unit_Last_Cost for clarity
--              :           and renamed @Current* variables to @Prev*
--              :18/10/03 - Added @GRV_ID as new input parameter to
--              :           sp_AP_Update_Inventory_Details get the GRV
--              :           Delivery_Note_Number as GLTran RefNo
--              :19/10/03 - Added ROUND(@New_Last_Cost, 2)
--2004.3.2      :04/10/06 - Removed comments appended to parameters passed to a SP.
--              :           THIS BUG CAUSES PROCEDURES TO NOT EXECUTE !!!!!!!!!!!!!
--2004.5.8	RL	:2013-02-04 - Changed AO_Order_Details.Received_Qty From Decimal(9,3) to Decimal(19,3)
--							Tirepoint uses the GRVs to invoice out the KM traveled on there call outs 
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AO_Insert_OR_Update_GRV_Detail]
    (	
    	@AO_Order_Details_ID int = 0,
    	@AO_GRV_ID int,
    	@AP_Products_ID int,
    	@Qty decimal(9,3),
    	@Unit_Last_Cost money,
    	@TradeDisc money = 0,
    	@RebateDisc money = 0,
    	@OtherDisc money = 0,
    	@NetAmount money = 0,
    	@Tax_Code_ID int,
    	@TaxAmount Money = 0,
    	@TotalCost Money = 0,
    	@UserID int,
    	@CompanyID int
    )

AS

SET NOCOUNT ON

	DECLARE @Prev_Last_Cost money,
            @Prev_On_Hand Decimal(9,3),
    		@Prev_Job_Reserved decimal(9,3),
    		@Prev_Sales_Order_Reserved decimal(9,3),
    		@Prev_Quote_Reserved decimal(9,3),
            @New_Last_Cost money,
            @New_On_Hand Decimal(9,3),
    		@Orders_ID Int,
    		@Received_Qty Decimal(19,3),
    		@Count1 int,
    		@Count2 int,
    		@Net_Purchase_Price money,
    		@Note varchar(70),
    		@User_Name varchar(40),
        --> GL variables for Supplier GL Detail
    		@Use_Rebate_Disc_GL_Acc Bit,
    		@Use_Other_Disc_GL_Acc Bit,
    		@Rebate_Disc_GL_Acc int,
    		@Other_Disc_GL_Acc int,
    		@Purchases_Contol_Acc_ID int,
    		@Suppliers_ID int

    BEGIN
    	BEGIN
    		INSERT INTO AO_GRV_Details
                (
                    AO_GRV_ID,
        			AP_Products_ID,
        			Qty,
        			Unit_Price,
        			Trade_Discount_Amount,	
        			Rebate_Discount_Amount,
        			Other_Discount_Amount,
        			Net_Amount,
        			Sys_AW_Tax_Codes_ID,
        			Tax_Amount,
        			Total_Cost,
        			[User_ID],
        			AW_User_Companies_ID
                )
    		VALUES
                (
                    @AO_GRV_ID ,
        			@AP_Products_ID ,
        			@Qty ,
        			@Unit_Last_Cost ,
        			@TradeDisc ,
        			@RebateDisc ,
        			@OtherDisc ,
        			@NetAmount ,
        			@Tax_Code_ID ,
        			@TaxAmount ,
        			@TotalCost ,
        			@UserID ,
        			@CompanyID
                )
    	END
    	
    	BEGIN	
            SELECT @Prev_On_Hand = ISNULL(On_Hand, 0) ,
                   @Prev_Last_Cost = ISNULL(Last_Cost, 0) ,
                   @Prev_Job_Reserved = ISNULL(Job_Reserved, 0) ,
                   @Prev_Sales_Order_Reserved = ISNULL(Sales_Order_Reserved, 0) ,
                   @Prev_Quote_Reserved = ISNULL(Quote_Reserved, 0)
    		FROM AP_Products
    		WHERE AP_Products_ID = @AP_Products_ID
    
    		SET @New_On_Hand = @Prev_On_Hand + @Qty    --New On_Hand Qty
            
    		BEGIN
    			SELECT @Suppliers_ID = AO_Suppliers_ID
    			FROM AO_GRV
    			WHERE AO_GRV_ID = @AO_GRV_ID
    		
    			SELECT  @Use_Rebate_Disc_GL_Acc = Use_Rebate_Discount_Control_Acc,
    					@Use_Other_Disc_GL_Acc = Use_Other_Discount_Control_Acc,
    					@Rebate_Disc_GL_Acc = AB_Rebate_Discount_Account_ID,
    					@Other_Disc_GL_Acc = AB_Other_Discounts_Account_ID
    			FROM AO_Suppliers 
    			WHERE AO_Suppliers_ID = @Suppliers_ID
    	
    			SELECT @Purchases_Contol_Acc_ID = AB_Accounts_ID 
    			FROM AB_Control_Accounts
    			WHERE Control_Number = 301    --Purchases
    		END

--> CALCULATE NEW LAST COST ----------------------------------------------

            SET @Net_Purchase_Price = ISNULL(@Unit_Last_Cost, 0) * ISNULL(@Qty, 1)

        --> Update Purchase with Net Acquisition Cost
            IF @Use_Rebate_Disc_GL_Acc = 1    -- 1 = YES
				BEGIN
					IF @Rebate_Disc_GL_Acc = @Purchases_Contol_Acc_ID    --IF Same Account as Purchases
                        --> Last_Cost = UnitPrice - (Trade + Rebate) = Net Acquisition Cost
                        SET @Net_Purchase_Price = @Net_Purchase_Price - (ISNULL(@TradeDisc, 0) + isnull(@RebateDisc, 0))
					ELSE
                        --> Last_Cost = UnitPrice - (Trade)
                        SET @Net_Purchase_Price = @Net_Purchase_Price - ISNULL(@TradeDisc, 0)
				END

        --> @Use_Rebate_Disc_GL_Acc = 0    -- 0 = NO
			ELSE    --> Last_Cost = UnitPrice - (Trade + Rebate) = Net Acquisition Cost
                SET @Net_Purchase_Price =  @Net_Purchase_Price - (ISNULL(@TradeDisc, 0) + isnull(@RebateDisc, 0))
    
			IF @Use_Other_Disc_GL_Acc = 1    -- 1 = YES
				BEGIN
					IF @Other_Disc_GL_Acc = @Purchases_Contol_Acc_ID    --IF Same Account as Purchases
                        SET @Net_Purchase_Price = @Net_Purchase_Price - ISNULL(@OtherDisc, 0)
				END

        --> @Use_Other_Disc_GL_Acc = 0    -- 0 = NO
			ELSE    --> Last_Cost = UnitPrice - (Trade + Rebate + Other) = Net Acquisition Cost
				SET @Net_Purchase_Price = @Net_Purchase_Price - ISNULL(@OtherDisc, 0)

------------------------------------------------------------------------
        --> New Last_Cost
            SET @New_Last_Cost = ROUND(@Net_Purchase_Price / ISNULL(@Qty, 1), 2)
------------------------------------------------------------------------
        --> Updates Last_Cost & Inventory Adjustment for Reval/Deval

            SET @Note = 'Goods Received by '

			EXEC sp_AP_Update_Inventory_Details
                    @AO_GRV_ID,
                    @AP_Products_ID,
					@New_Last_Cost,
					@New_On_Hand,
					@Prev_Job_Reserved,
					@Prev_Sales_Order_Reserved,
					@Prev_Quote_Reserved,
					@Note,
					@UserID ,
					@CompanyID
------------------------------------------------------------------------

    	END
    	
    	IF @AO_Order_Details_ID <> 0 
    		BEGIN
    
    			SELECT @Received_Qty = ISNULL(Received_Qty,0),
    				@Orders_ID = AO_Orders_ID	
    			FROM AO_Order_Details 
    			WHERE AO_Order_Details_ID = @AO_Order_Details_ID
    			
    			SET @Received_Qty =  @Received_Qty + @Qty			 
    		
    			UPDATE AO_Order_Details
    			SET Receipt_Date = GetDate(),
    				Received_Qty = @Received_Qty
    			WHERE AO_Order_Details_ID = @AO_Order_Details_ID
    
            --> CERTIFY ORDER as COMPLETE if items received in full
    			SELECT @Count1 = Count(AO_Order_Details_ID)
    			FROM AO_Order_Details
    			WHERE AO_Orders_ID = @Orders_ID
    			
    			SELECT @Count2 = Count(AO_Order_Details_ID)
    			FROM AO_Order_Details
    			WHERE Received_Qty  >= (Order_Qty + Back_Order_Qty) AND
                      AO_Orders_ID = @Orders_ID
    			
    			IF @Count2 = @Count1
    				BEGIN
    					UPDATE AO_Orders
    					SET Complete = 1 
    					WHERE AO_Orders_ID = @Orders_ID 
    				END
    		END			
    END

SET NOCOUNT OFF
Go

--6

IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AP_Get_Product_Lookup_ID]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AP_Get_Product_Lookup_ID]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------
--Description   :sp_AP_Get_Product_Lookup_ID
--Purpose       :INSERT Enterprise Stock
--Date          :2006/06/01
--Authors       :TB
--MODS:         :
--2004.3.2      :11/09/06 - Added @LocationID input parameter and [Location] field
--              :15/09/06 - Added SELECT only ACTIVE lookups (Slu_AW_Active_ID = 1)
--              :30/09/06 - Added [GL Purchases], [GL Sales], [GL COS] fields
--2004.3.4      :27/02/07 - Added input parameters for Lup_AP_Purchase_Categories
--2004.5.0      :10/03/11 - Removed [GL Purchases], [GL Sales], [GL COS] fields
--              :10/03/11 - Replaced get top 1 LookupID if Lookup does not exist
--              :           with INSERT LOOKUP if NOT EXISTS LOOKUP
--2004.5.2      :11/07/11 - Fixed @Supplier field length to new size 40 !!!!
--2004.5.7 RGL  :2012-11-01 - Added Levy and WeightLookup
--2004.5.8 RGL  :2013-02-05 - Added a check to make sure blank (NULL) descriptions are not added
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AP_Get_Product_Lookup_ID]
	(
        @CompanyID int,
        @Manuf varchar(30),
        @Supplier varchar(40),
        @Type varchar(50),
        @Group varchar(50),
        @Dept varchar(50),
        @Location varchar(50),
        @PurchaseCat varchar(50),
        @Levy varchar(50),
        @WeightType varchar(50),
                
        @ManufID int OUTPUT,
        @SupplierID int OUTPUT,
        @TypeID int OUTPUT,
        @GroupID smallint OUTPUT,
        @DeptID int OUTPUT,
        @LocationID int OUTPUT,
        @PurchaseCatID int OUTPUT,
        @LevyID int OUTPUT,
        @WeightTypeID smallint OUTPUT        
	)

AS

SET NOCOUNT ON

	DECLARE @RetCode int

    SET @RetCode = 0

	SET @Manuf = RTRIM(LTRIM(ISNULL(@Manuf,'')))
	SET @Supplier = RTRIM(LTRIM(ISNULL(@Supplier,'')))
	SET @Type = RTRIM(LTRIM(ISNULL(@Type,'')))
	SET @Group = RTRIM(LTRIM(ISNULL(@Group,'')))
	SET @Dept = RTRIM(LTRIM(ISNULL(@Dept,'')))
	SET @Location = RTRIM(LTRIM(ISNULL(@Location,'')))
	SET @PurchaseCat = RTRIM(LTRIM(ISNULL(@PurchaseCat,'')))
	SET @WeightType = RTRIM(LTRIM(ISNULL(@WeightType,'')))
	SET @Levy = RTRIM(LTRIM(ISNULL(@Levy,'')))

    --> Get Manufacturer ID
	IF NOT EXISTS
		(
			SELECT Lup_AP_Product_Manufacturers_ID
			FROM Lup_AP_Product_Manufacturers
			WHERE AW_User_Companies_ID = @CompanyID AND
					[Description] = @Manuf AND
					Slu_AW_Active_ID = 1
		)
		BEGIN
			IF @Manuf <> ''
				BEGIN
					INSERT Lup_AP_Product_Manufacturers (Description, AW_User_Companies_ID) VALUES (@Manuf, @CompanyID)

					SELECT @ManufID = Scope_Identity()
				END
			ELSE
				BEGIN
					SELECT TOP 1 @ManufID = Lup_AP_Product_Manufacturers_ID
					FROM Lup_AP_Product_Manufacturers
					WHERE AW_User_Companies_ID = @CompanyID AND
						 [Description] = '<None>' AND
						Slu_AW_Active_ID = 1			
				END	
        END
	ELSE
        BEGIN
            SELECT TOP 1 @ManufID = Lup_AP_Product_Manufacturers_ID
            FROM Lup_AP_Product_Manufacturers
            WHERE AW_User_Companies_ID = @CompanyID AND
                 [Description] = @Manuf AND
                  Slu_AW_Active_ID = 1
        END

    --> Get Supplier ID
	IF NOT EXISTS
		(
			SELECT AO_Suppliers_ID
			FROM AO_Suppliers
			WHERE AW_User_Companies_ID = @CompanyID AND
					Supplier_Name = @Supplier AND
					Slu_AW_Active_ID = 1
		)
		BEGIN
			IF @Supplier <> ''
				BEGIN
					INSERT AO_Suppliers (Supplier_Name, AW_User_Companies_ID) VALUES (@Supplier, @CompanyID)

					SELECT @SupplierID = Scope_Identity()
				END
			ELSE
				BEGIN
		            SELECT TOP 1 @SupplierID = AO_Suppliers_ID
				    FROM AO_Suppliers
					WHERE AW_User_Companies_ID = @CompanyID AND
						Supplier_Name = '<None>' AND
						Slu_AW_Active_ID = 1				
				END		
		END
	ELSE
		BEGIN
            SELECT TOP 1 @SupplierID = AO_Suppliers_ID
            FROM AO_Suppliers
            WHERE AW_User_Companies_ID = @CompanyID AND
                  Supplier_Name = @Supplier AND
                  Slu_AW_Active_ID = 1
        END

    --> Get Product Type ID
	IF NOT EXISTS
		(
			SELECT Lup_AP_Product_Types_ID
			FROM Lup_AP_Product_Types
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @Type AND
				Slu_AW_Active_ID = 1
		)
		BEGIN
			IF @Type <> ''
				BEGIN
					INSERT Lup_AP_Product_Types (Description, AW_User_Companies_ID) VALUES (@Type, @CompanyID)

					SELECT @TypeID = Scope_Identity()
				END
			ELSE
				BEGIN
					SELECT TOP 1 @TypeID = Lup_AP_Product_Types_ID
					FROM Lup_AP_Product_Types
					WHERE AW_User_Companies_ID = @CompanyID AND
						[Description] = '<None>' AND
						Slu_AW_Active_ID = 1				
				END	
		END
	ELSE
		BEGIN
			SELECT TOP 1 @TypeID = Lup_AP_Product_Types_ID
			FROM Lup_AP_Product_Types
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @Type AND
				Slu_AW_Active_ID = 1
		END

    --> Get Product Group ID
	IF NOT EXISTS
		(
			SELECT Lup_AP_Product_Groups_ID
			FROM Lup_AP_Product_Groups
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @Group AND
				Slu_AW_Active_ID = 1
		)
		BEGIN
			IF @Group <> ''
				BEGIN
					INSERT Lup_AP_Product_Groups (Description, AW_User_Companies_ID) VALUES (@Group, @CompanyID)

					SELECT @GroupID = Scope_Identity()
				END
			ELSE
				BEGIN	
					SELECT TOP 1 @GroupID = Lup_AP_Product_Groups_ID
					FROM Lup_AP_Product_Groups
					WHERE AW_User_Companies_ID = @CompanyID AND
						[Description] = '<None>' AND
						Slu_AW_Active_ID = 1							
				END
		END
	ELSE
		BEGIN
			SELECT TOP 1 @GroupID = Lup_AP_Product_Groups_ID
			FROM Lup_AP_Product_Groups
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @Group AND
				Slu_AW_Active_ID = 1
		END

    --> Get Product Department ID
	IF NOT EXISTS
		(
			SELECT Lup_AP_Product_Departments_ID
			FROM Lup_AP_Product_Departments
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @Dept AND
				Slu_AW_Active_ID = 1
		)
		BEGIN
			IF @Dept <> ''
				BEGIN
					INSERT Lup_AP_Product_Departments (Description, AW_User_Companies_ID) VALUES (@Dept, @CompanyID)

					SELECT @DeptID = Scope_Identity()
				END
			ELSE	
				BEGIN
					SELECT TOP 1 @DeptID = Lup_AP_Product_Departments_ID
					FROM Lup_AP_Product_Departments
					WHERE AW_User_Companies_ID = @CompanyID AND
						[Description] = '<None>' AND
						Slu_AW_Active_ID = 1				
				END
		END
	ELSE
		BEGIN
			SELECT TOP 1 @DeptID = Lup_AP_Product_Departments_ID
			FROM Lup_AP_Product_Departments
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @Dept AND
				Slu_AW_Active_ID = 1
		END

    --> Get Product Location ID
	IF NOT EXISTS
		(
			SELECT Lup_AP_Product_Locations_ID
			FROM Lup_AP_Product_Locations
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @Location AND
				Slu_AW_Active_ID = 1
		)
		BEGIN
			IF @Location <> ''
				BEGIN
					INSERT Lup_AP_Product_Locations (Description, AW_User_Companies_ID) VALUES (@Location, @CompanyID)

					SELECT @LocationID = Scope_Identity()
				END
			ELSE
				BEGIN
					SELECT TOP 1 @LocationID = Lup_AP_Product_Locations_ID
					FROM Lup_AP_Product_Locations
					WHERE AW_User_Companies_ID = @CompanyID AND
						[Description] = '<None>' AND
						Slu_AW_Active_ID = 1				
				END		
		END
	ELSE
		BEGIN
			SELECT TOP 1 @LocationID = Lup_AP_Product_Locations_ID
			FROM Lup_AP_Product_Locations
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @Location AND
				Slu_AW_Active_ID = 1
		END

    --> Get Purchase Category ID
	IF NOT EXISTS
		(
			SELECT Lup_AP_Purchase_Categories_ID
			FROM Lup_AP_Purchase_Categories
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @PurchaseCat AND
				Slu_AW_Active_ID = 1
		)
		BEGIN
			IF @PurchaseCat <> ''
				BEGIN
					INSERT Lup_AP_Purchase_Categories (Description, AW_User_Companies_ID) VALUES (@PurchaseCat, @CompanyID)

					SELECT @PurchaseCatID = Scope_Identity()
				END
			ELSE		
				BEGIN	
					SELECT TOP 1 @PurchaseCatID = Lup_AP_Purchase_Categories_ID
					FROM Lup_AP_Purchase_Categories
					WHERE AW_User_Companies_ID = @CompanyID AND
						[Description] = '<None>' AND
						Slu_AW_Active_ID = 1		
				END		
		END		
	ELSE
		BEGIN
			SELECT TOP 1 @PurchaseCatID = Lup_AP_Purchase_Categories_ID
			FROM Lup_AP_Purchase_Categories
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @PurchaseCat AND
				Slu_AW_Active_ID = 1
		END

	--> Get Levy ID
	SET @LevyID = 0
	IF @Levy <> ''
		BEGIN
			SELECT TOP 1 @LevyID = AP_Products_ID
			FROM AP_Products
			WHERE AW_User_Companies_ID = @CompanyID AND
				Slu_AW_YesNo_Levy_Item = 1 AND
				[Description] = @Levy
		END
    
    --> Get Weight ID
	IF NOT EXISTS
		(
			SELECT Lup_AP_AW_Weight_ID
			FROM Lup_AP_AW_Weight
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @WeightType AND
				Slu_AW_Active_ID = 1
		)
		BEGIN
			IF @WeightType <> ''
				BEGIN
					INSERT Lup_AP_AW_Weight (Description, AW_User_Companies_ID) VALUES (@WeightType, @CompanyID)

					SELECT @WeightTypeID = Scope_Identity()
				END
			ELSE
				BEGIN
					SELECT TOP 1 @WeightTypeID = Lup_AP_AW_Weight_ID
					FROM Lup_AP_AW_Weight
					WHERE AW_User_Companies_ID = @CompanyID AND
						[Description] = '<None>' AND
						Slu_AW_Active_ID = 1				
				END		
		END
	ELSE
		BEGIN
			SELECT TOP 1 @WeightTypeID = Lup_AP_AW_Weight_ID
			FROM Lup_AP_AW_Weight
			WHERE AW_User_Companies_ID = @CompanyID AND
				[Description] = @WeightType AND
				Slu_AW_Active_ID = 1		
		END	

    SET @RetCode = 1

    RETURN @RetCode
Go

--7

IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AP_Select_Products_Lups]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AP_Select_Products_Lups]
GO
/****** Object:  StoredProcedure [dbo].[sp_AP_Select_Products_Lups]    Script Date: 09/17/2012 14:49:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------------------
--Description   :sp_AP_Select_Products_Lups
--Purpose       :Product Catalog Lookups
--Functionality :
--Parameters    :
--Date          :2001/11/13
--Authors       :TB
--MODS          :11/07/02 - Modified SORT ORDER of most LUPS to ALPHA
--2004.2.4      :23/10/05 - Modified Choice 17 and added ALIAS [Plu Tabs]
--              :           causing list field not displaying in Catalog 
--2004.3.4      :26/02/07 - Added Choice 18 as [Purchase Categories]
--2004.4.0      :25/07/07 - Added Choice 19 as [CostPlusGroup]
--2004.5.7 RGL	:2012-10-23 - Added 20 as Unit Weight and 21 as Product Levy
--2004.5.8 RGL  :2013-02-20 - Added <None> as Levy LU
-----------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AP_Select_Products_Lups]
    (
    	@Choice int,
    	@CompanyID int 
    )
AS

SET NOCOUNT ON

    --> 1 - Product Types
    	IF @Choice = 1
    		SELECT Lup_AP_Product_Types_ID AS [ID], 
    		[Description] AS [Product Types] 
    		FROM Lup_AP_Product_Types
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 2 - Product Groups
    	ELSE 
    	IF @Choice = 2	
    		SELECT Lup_AP_Product_Groups_ID AS [ID], 
    		[Description] AS [Product Groups] 
    		FROM Lup_AP_Product_Groups
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 3 - Product Departments
    	ELSE 
    	IF @Choice = 3	
    		SELECT Lup_AP_Product_Departments_ID AS [ID], 
    		[Description] AS [Product Departments] 
    		FROM Lup_AP_Product_Departments	
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 4 - Product Locations
    	ELSE 
    	IF @Choice = 4	
    		SELECT Lup_AP_Product_Locations_ID AS [ID], 
    		[Description] AS [Product Locations] 
    		FROM Lup_AP_Product_Locations	
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 5 - Product Manufacturers
    	ELSE 
    	IF @Choice = 5
    		SELECT Lup_AP_Product_Manufacturers_ID AS [ID], 
    		[Description] AS [Product Manufacturer] 
    		FROM Lup_AP_Product_Manufacturers
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 6 - Default Supplier
    	ELSE 
    	IF @Choice = 6
    		SELECT AO_Suppliers_ID AS [ID], 
    		[Supplier_Name] AS [Default Supplier] 
    		FROM AO_Suppliers
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Supplier_Name]
    
    --> 7 - Units Of Measure
    	IF @Choice = 7
    		SELECT Slu_AP_Units_Of_Measure_ID AS [ID], 
    		[Description] AS [Units Of Measure] 
    		FROM Slu_AP_Units_Of_Measure
            ORDER BY [Description]
    
    --> 8 - Mass Codes
    	ELSE
    	IF @Choice = 8
    		SELECT Lup_AP_Mass_Codes_ID AS [ID],
    		[Description] As [Mass Codes] 
    		FROM Lup_AP_Mass_Codes
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 9 - Active
    	ELSE
    	IF @Choice = 9
    		SELECT Slu_AW_Active_ID AS [ID],
    		[Description] As [Status] 
    		FROM Slu_AW_Active
    
    --> 10 - AW YesNO
    	ELSE
    	IF @Choice = 10
    		SELECT Slu_AW_YesNo_ID AS [ID], 
    		[Description] AS [AW YesNo] 
    		FROM Slu_AW_YesNo
    
    --> 11 - Rounding Codes
    	ELSE
    	IF @Choice = 11
    		SELECT Lup_AP_Rounding_Codes_ID AS [ID],
    		[Description] As [Rounding Codes] 
    		FROM Lup_AP_Rounding_Codes		
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 12 - Tax Codes
    	ELSE
    	IF @Choice = 12
    		SELECT Sys_AW_Tax_Codes_ID AS [ID],
    		[Description] As [Tax Codes] 
    		FROM sys_AW_Tax_Codes
    
    --> 13 - Sales Discount Groups
    	ELSE
    	IF @Choice = 13
    		SELECT Lup_AP_Discount_Groups_ID AS [ID],
    		[Description] As [Discount Groups] 
    		FROM Lup_AP_Discount_Groups
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 14 - Sales Commissions Group
    	ELSE
    	IF @Choice = 14
    		SELECT Lup_AP_Commission_Groups_ID AS [ID],
    		[Description] As [Commissions Group] 
    		FROM Lup_AP_Commission_Groups
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 15 - Sell By Codes
    	ELSE
    	IF @Choice = 15
    		SELECT Lup_AP_Sell_By_Codes_ID AS [ID],
    		[Description] As [Sell By Codes] 
    		FROM Lup_AP_Sell_By_Codes
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    
    --> 16 - Formulas
    	ELSE
    	IF @Choice = 16
    		SELECT AW_Formulas_ID AS [ID],
    		[Description] As [Formulas] 
    		FROM AW_Formulas
    		WHERE AW_User_Companies_ID = @CompanyID
    
    --> 17 - PLU TAB Location
    	ELSE
    	IF @Choice = 17
    		SELECT Lup_AP_PLU_Tabs_ID AS [ID],
    		[Description] As [Plu Tabs] 
    		FROM Lup_AP_PLU_Tabs
    		WHERE AW_User_Companies_ID = @CompanyID

    --> 18 - Purchase Categories
    	ELSE 
    	IF @Choice = 18	
    		SELECT Lup_AP_Purchase_Categories_ID AS [ID], 
    		[Description] AS [Purchase Categories] 
    		FROM Lup_AP_Purchase_Categories	
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]

    --> 19 - Cost+Plus Groups
    	ELSE
    	IF @Choice = 19
    		SELECT Lup_AP_Markup_Groups_ID AS [ID],
    		[Description] As [CostPlusGroup] 
    		FROM Lup_AP_Markup_Groups
    		WHERE AW_User_Companies_ID = @CompanyID
            ORDER BY [Description]
    --> 20 - Unit Weight
    	ELSE
    	IF @Choice = 20
    		SELECT Lup_AP_AW_Weight_ID AS [ID],
    		[Description] As [Unit Weight] 
    		FROM Lup_AP_AW_Weight
            ORDER BY [Description]
    --> 21 - Levy Product
    	ELSE
    	IF @Choice = 21
			SELECT 0 AS [ID],
    		'<None>' As [Levy] 
    		FROM AP_Products
    		WHERE Slu_AW_YesNo_Levy_Item = 1
            UNION
    		SELECT AP_Products_ID AS [ID],
    		[Description] As [Levy] 
    		FROM AP_Products
    		WHERE Slu_AW_YesNo_Levy_Item = 1
            ORDER BY 2

SET NOCOUNT OFF
Go

--8
IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AS_Select_Delivery_Address_Hist]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AS_Select_Delivery_Address_Hist]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------
--Description   :sp_AS_Select_Delivery_Address_Hist
--Purpose       :Select address details used by customer
--Functionality :Select address details used by customer
--Date          :2013-01-02
--Authors       :RL
------------------------------------------------------------------------------- 
CREATE Procedure [dbo].[sp_AS_Select_Delivery_Address_Hist]
    (    	    	
    	@AC_Customers_ID int
    )
AS 

	SELECT Delivery_Address AS 'Delivery_Address',
		   Delivery_Suburb AS 'Delivery_Suburb',
		   Delivery_City AS 'Delivery_City',
		   Delivery_Post_Code AS 'Delivery_Post_Code'
	FROM AS_Transactions_Address_Detail				
	WHERE AC_Customers_ID = @AC_Customers_ID
	ORDER BY Delivery_Address
Go

--9
IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AP_Product_Filter_Lups]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AP_Product_Filter_Lups]
GO
/****** Object:  StoredProcedure [dbo].[sp_AP_Product_Filter_Lups]    Script Date: 09/17/2012 14:49:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------
--Description   :sp_AP_Product_Filter_Lups
--Purpose       :Returns LUPS for Product Filters
--Functionality :
--Date          :2005/04/27
--Authors       :TB
--MODS          :21/10/05 - Added Options 8 = Location, 9 = Added Barcode Types
--2004.3.4      :27/03/07 - Added Added Options 10 = Purchase
--2004.5.7 RGL  :2012-10-20 - Added Product Levy 11
--2004.5.8 RGL  :2013-02-20 - Added <None> as Levy LU
---------------------------------------------------------------------------------
CREATE Procedure [dbo].[sp_AP_Product_Filter_Lups]
    (
    	@Option int,
    	@Company_ID int
    )
AS

SET NOCOUNT ON

        IF @Option = 1    --> Type
        	SELECT Lup_AP_Product_Types_ID AS [ID],
        		  [Description] AS [Type] 
        	FROM Lup_AP_Product_Types
        	WHERE AW_User_Companies_ID = @Company_ID
            ORDER BY [Description] ASC
        
        ELSE
        IF @Option = 2    --> Group
        	SELECT Lup_AP_Product_Groups_ID AS [ID],
        		  [Description] AS [Group] 
        	FROM Lup_AP_Product_Groups	
        	WHERE AW_User_Companies_ID = @Company_ID
            ORDER BY [Description] ASC
        
        ELSE
        IF @Option = 3    --> Department
        	SELECT Lup_AP_Product_Departments_ID AS [ID], 
        		  [Description] AS [Department] 
        	FROM Lup_AP_Product_Departments
        	WHERE AW_User_Companies_ID = @Company_ID
            ORDER BY [Description] ASC
        
        ELSE
        IF @Option = 4    --> Suppliers
        	SELECT AO_Suppliers_ID AS [ID],
        		   Supplier_Name AS [Supplier] 
        	FROM AO_Suppliers
        	WHERE AW_User_Companies_ID = @Company_ID
            ORDER BY Supplier_Name ASC
        
        ELSE
        IF @Option = 5    --> Manufacturer
        	SELECT Lup_AP_Product_Manufacturers_ID AS [ID],
        		  [Description] AS [Manufacturer] 
        	FROM Lup_AP_Product_Manufacturers
        	WHERE AW_User_Companies_ID = @Company_ID
            ORDER BY [Description] ASC
        
        ELSE
        IF @Option = 6    --> Active
        	SELECT Slu_AW_Active_ID AS [ID],
        		[Description] AS [Active]
        	FROM Slu_AW_Active
        
        ELSE
        IF @Option = 7    --> Yes No
          	SELECT Slu_AW_YesNo_ID AS [ID],
                [Description] AS [YesNo]
            FROM Slu_AW_YesNo

    	ELSE 
    	IF @Option = 8    --> Location
    		SELECT Lup_AP_Product_Locations_ID AS [ID],
    			[Description] AS [Location]
    		FROM Lup_AP_Product_Locations
            WHERE AW_User_Companies_ID = @Company_ID
            ORDER BY [Description] ASC

    	ELSE 
    	IF @Option = 9    --> Barcode Types
    		SELECT Sys_AW_Barcode_Types_ID AS [ID],
    		   [Type] AS [Barcode],
               [Value_ID],
               [Mnemonic],
               [Description]
    		FROM Sys_AW_Barcode_Types

    	ELSE 
    	IF @Option = 10    --> Purchase Category
    		SELECT Lup_AP_Purchase_Categories_ID AS [ID],
    			[Description] AS [Purchase]
    		FROM Lup_AP_Purchase_Categories
            WHERE AW_User_Companies_ID = @Company_ID
            ORDER BY [Description] ASC
        ELSE    
    	IF @Option = 11    --> Levy   
    	    SELECT 0 AS [ID],
    		      '<None>' As [Levy]
    		FROM AP_Products
    		WHERE AW_User_Companies_ID = @Company_ID AND
    		   Slu_AW_YesNo_Levy_Item = 1            
            UNION        
            SELECT AP_Products_ID AS [ID],
    		      [Description] As [Levy]
    		FROM AP_Products
    		WHERE AW_User_Companies_ID = @Company_ID AND
    		   Slu_AW_YesNo_Levy_Item = 1
            ORDER BY 2 ASC

SET NOCOUNT OFF
Go

--10
IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AS_Shape_Transaction_Search_Header]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AS_Shape_Transaction_Search_Header]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------
--Description   :sp_AS_Shape_Transaction_Search_Header
--Purpose       :Selects POS Transaction Header Shaped for Document Reprint
--Date          :2003/12/18
--Authors       :TB
--MODS          :18/12/03 - Added [TypeID] field for Customer Transaction Search
--              :06/12/04 - Reversed [Customer] field as (Surnames, Firstnames)
--              :06/12/04 - Added @CompanyID filter in WHERE clause IF @CustomerID <> 0
--              :06/12/04 - Added CASE statement to not calculate GP on PAYMENTS
--2004.3.7      :11/07/07 - Added AW_Comments as [Notes]
--2004.4.0      :11/07/08 - Added Sales Order transaction type = 16
--2004.4.2      :26/10/08 - Replaced [Ref No] (Transaction_Number) field with [No] and
--              :           added [Ref No] (Customer_Reference) field
--2004.4.7      :13/09/09 - Added new Stock Transfer transaction type = 23
--2004.5.8      :2013-02-20 - Added search for AS_Job_References_ID and Serial_No
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AS_Shape_Transaction_Search_Header]
	(
        @CustomerID int,
        @SearchString varchar(50),
        @Filter1 varchar(50) = NULL,
        @Filter2 varchar(50) = NULL,
        @Filter3 varchar(50) = NULL,
        @TransactionTypeID int,
        @StartDate datetime,
        @EndDate datetime,
        @CompanyID int
	)

AS

SET NOCOUNT ON

-------------------------------------------------------------------------------
    IF @CustomerID <> 0
        BEGIN
        --> Specific Transaction Type
            IF @TransactionTypeID <> 0
                BEGIN
                    SELECT DISTINCT
                        t.AS_Transactions_ID AS [ID],
                        t.AC_Customers_ID AS [CustomerID],
                        RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                        tt.[Description] AS [Type],
                        t.Transaction_Date AS [Date],
                        t.Transaction_Number AS [No],
                        t.Customer_Reference AS [Ref No],
                        t.Amount AS [Amount],
                        t.Total_Discount_Amount AS [Discount],
                        t.Total_Tax_Amount AS [VAT],
                        t.Total_Amount AS [Total],
                        t.Payment_Amount AS [Payment],
                        t.Total_Cost AS [Total Cost],

                        CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                  t.Sys_AW_Transaction_Types_ID <> 18 AND
                                  t.Sys_AW_Transaction_Types_ID <> 21 AND
                                  t.Sys_AW_Transaction_Types_ID <> 22
                             THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                             ELSE 0
                        END AS [GP],

                        t.Comment AS [Comment],
                        com.Comment AS [Notes],
                        ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                        act.[Description] AS [Active],
                       	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                    FROM AS_Transactions t
                        INNER JOIN AC_Customers c
                            ON t.AC_Customers_ID = c.AC_Customers_ID
                        INNER JOIN Sys_AW_Transaction_Types tt
                            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                        INNER JOIN AW_Users u
                            ON t.[User_ID] = u.AW_Users_ID
                        INNER JOIN Slu_AW_Active act
                            ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                        LEFT JOIN AW_Comments com
                            ON t.AW_Comments_ID = com.AW_Comments_ID
                    WHERE t.AW_User_Companies_ID = @CompanyID AND
                          t.AC_Customers_ID = @CustomerID AND
                          t.Sys_AW_Transaction_Types_ID = @TransactionTypeID AND
                         (DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                          DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0)
                    ORDER BY t.Transaction_Date DESC
                END
-----------------------------------------
        --> ALL Transaction Types
            ELSE
                BEGIN
                    SELECT DISTINCT
                        t.AS_Transactions_ID AS [ID],
                        t.AC_Customers_ID AS [CustomerID],
                        RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],

                        tt.[Description] AS [Type],
                        t.Transaction_Date AS [Date],
                        t.Transaction_Number AS [No],
                        t.Customer_Reference AS [Ref No],
                        t.Amount AS [Amount],
                        t.Total_Discount_Amount AS [Discount],
                        t.Total_Tax_Amount AS [VAT],
                        t.Total_Amount AS [Total],
                        t.Payment_Amount AS [Payment],
                        t.Total_Cost AS [Total Cost],

                        CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                  t.Sys_AW_Transaction_Types_ID <> 18 AND
                                  t.Sys_AW_Transaction_Types_ID <> 21 AND
                                  t.Sys_AW_Transaction_Types_ID <> 22
                             THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                             ELSE 0
                        END AS [GP],

                        t.Comment AS [Comment],
                        com.Comment AS [Notes],
                        ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                        act.[Description] AS [Active],
                       	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                    FROM AS_Transactions t
                        INNER JOIN AC_Customers c
                            ON t.AC_Customers_ID = c.AC_Customers_ID
                        INNER JOIN Sys_AW_Transaction_Types tt
                            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                        INNER JOIN AW_Users u
                            ON t.[User_ID] = u.AW_Users_ID
                        INNER JOIN Slu_AW_Active act
                            ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                        LEFT JOIN AW_Comments com
                            ON t.AW_Comments_ID = com.AW_Comments_ID
                    WHERE t.AW_User_Companies_ID = @CompanyID AND
                          t.AC_Customers_ID = @CustomerID AND
                          (t.Sys_AW_Transaction_Types_ID = 1 or
                           t.Sys_AW_Transaction_Types_ID = 2 or 
                           t.Sys_AW_Transaction_Types_ID = 3 or 
                           t.Sys_AW_Transaction_Types_ID = 4 or 
                           t.Sys_AW_Transaction_Types_ID = 5 or 
                           t.Sys_AW_Transaction_Types_ID = 7 or 
                           t.Sys_AW_Transaction_Types_ID = 15 or 
                           t.Sys_AW_Transaction_Types_ID = 16 or 
                    	   t.Sys_AW_Transaction_Types_ID = 18 or
                           t.Sys_AW_Transaction_Types_ID = 21 or 
                           t.Sys_AW_Transaction_Types_ID = 22 or 
                    	   t.Sys_AW_Transaction_Types_ID = 23) AND
                           DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                           DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0
                    ORDER BY t.Transaction_Date DESC
                END
        END
-------------------------------------------------------------------------------
    ELSE    --(IF @CustomerID = 0)
        BEGIN
        --> Specific Transaction Type
            IF @TransactionTypeID <> 0
                BEGIN
----------------------------------------->>>>>>>
                    IF @SearchString <> ''
                        BEGIN
----------------------------------------->>>>>>>
                			IF @Filter3 <> ''
                				BEGIN
                					SET @Filter3 = '%' +  @Filter3 + '%'
                					SET @Filter2 = '%' +  @Filter2 + '%'
                					SET @Filter1 = '%' +  @Filter1 + '%'
        
                                    SELECT DISTINCT
                                        t.AS_Transactions_ID AS [ID],
                                        t.AC_Customers_ID AS [CustomerID],
                                        RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                                        tt.[Description] AS [Type],
                                        t.Transaction_Date AS [Date],
                                        t.Transaction_Number AS [No],
                                        t.Customer_Reference AS [Ref No],
                                        t.Amount AS [Amount],
                                        t.Total_Discount_Amount AS [Discount],
                                        t.Total_Tax_Amount AS [VAT],
                                        t.Total_Amount AS [Total],
                                        t.Payment_Amount AS [Payment],
                                        t.Total_Cost AS [Total Cost],

                                        CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 18 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 21 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 22
                                             THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                                             ELSE 0
                                        END AS [GP],

                                        t.Comment AS [Comment],
                                        ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                                        act.[Description] AS [Active],
                                       	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                                    FROM AS_Transactions t
--2004.5.8                                    
										LEFT OUTER JOIN AS_Transaction_Details d
											ON t.AS_Transactions_ID = d.AS_Transactions_ID
--2004.5.8											                                    
                                        INNER JOIN AC_Customers c
                                            ON t.AC_Customers_ID = c.AC_Customers_ID
                                        INNER JOIN Sys_AW_Transaction_Types tt
                                            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                                        LEFT JOIN AC_Assets a
                                            ON c.AC_Customers_ID = a.AC_Customers_ID
                                        LEFT JOIN AW_Comments com
                                            ON t.AW_Comments_ID = com.AW_Comments_ID
                                        INNER JOIN AW_Users u
                                            ON t.[User_ID] = u.AW_Users_ID
                                        INNER JOIN Slu_AW_Active act
                                            ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                                    WHERE t.AW_User_Companies_ID = @CompanyID AND
                                          t.Sys_AW_Transaction_Types_ID = @TransactionTypeID AND
        
                                         ((c.Surname like @Filter1 AND
                                           c.Surname like @Filter2 AND
                                           c.Surname like @Filter3 ) OR 
                                          (c.Firstnames like @Filter1 AND
                                           c.Firstnames like @Filter2 AND
                                           c.Firstnames like @Filter3) OR 
                                          (c.Cell_No like @Filter1 AND
                                           c.Cell_No like @Filter2 AND
                                           c.Cell_No like @Filter3) OR 
                                          (c.Work_Tel_No like @Filter1 AND
                                           c.Work_Tel_No like @Filter2 AND
                                           c.Work_Tel_No like @Filter3) OR 
                                          (c.Home_Tel_No like @Filter1 AND
                                           c.Home_Tel_No like @Filter2 AND
                                           c.Home_Tel_No like @Filter3) OR 
                                          (t.Transaction_Number like @Filter1 AND
                                           t.Transaction_Number like @Filter2 AND
                                           t.Transaction_Number like @Filter3) OR
                                          (t.Customer_Reference like @Filter1 AND
                                           t.Customer_Reference like @Filter2 AND
                                           t.Customer_Reference like @Filter3) OR
                                          (CONVERT(varchar(30), t.Total_Amount) like @Filter1 AND
                                           CONVERT(varchar(30), t.Total_Amount) like @Filter2 AND
                                           CONVERT(varchar(30), t.Total_Amount) like @Filter3) OR
                                          (t.Comment like @Filter1 AND
                                           t.Comment like @Filter2 AND
                                           t.Comment like @Filter3) OR
                                          (com.Comment like @Filter1 AND
                                           com.Comment like @Filter2 AND
                                           com.Comment like @Filter3) OR
--2004.5.8                                          
                                          (d.AS_Job_References_ID like @Filter1 AND
                                           d.AS_Job_References_ID like @Filter2 AND
                                           d.AS_Job_References_ID like @Filter3) OR                                        
                                          (d.Serial_No like @Filter1 AND
                                           d.Serial_No like @Filter2 AND
                                           d.Serial_No like @Filter3) OR
--2004.5.8                                                                                                                               
                                          (a.Reg_No like @Filter1 AND
                                           a.Reg_No like @Filter2 AND
                                           a.Reg_No like @Filter3)) AND
                
                                          (t.Sys_AW_Transaction_Types_ID = 1 or
                                           t.Sys_AW_Transaction_Types_ID = 2 or 
                                           t.Sys_AW_Transaction_Types_ID = 3 or 
                                           t.Sys_AW_Transaction_Types_ID = 4 or 
                                           t.Sys_AW_Transaction_Types_ID = 5 or 
                                           t.Sys_AW_Transaction_Types_ID = 7 or 
                                           t.Sys_AW_Transaction_Types_ID = 15 or 
                                    	   t.Sys_AW_Transaction_Types_ID = 18 or
                                           t.Sys_AW_Transaction_Types_ID = 21 or 
                                           t.Sys_AW_Transaction_Types_ID = 22 or 
                    	                   t.Sys_AW_Transaction_Types_ID = 23) AND
                                           DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                                           DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0
                                    ORDER BY t.Transaction_Date DESC
                                END
        
                			IF @Filter2 <> ''
                				BEGIN
                					SET @Filter2 = '%' +  @Filter2 + '%'
                					SET @Filter1 = '%' +  @Filter1 + '%'
        
                                    SELECT DISTINCT
                                        t.AS_Transactions_ID AS [ID],
                                        t.AC_Customers_ID AS [CustomerID],
                                        RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                                        tt.[Description] AS [Type],
                                        t.Transaction_Date AS [Date],
                                        t.Transaction_Number AS [No],
                                        t.Customer_Reference AS [Ref No],
                                        t.Amount AS [Amount],
                                        t.Total_Discount_Amount AS [Discount],
                                        t.Total_Tax_Amount AS [VAT],
                                        t.Total_Amount AS [Total],
                                        t.Payment_Amount AS [Payment],
                                        t.Total_Cost AS [Total Cost],

                                        CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 18 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 21 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 22
                                             THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                                             ELSE 0
                                        END AS [GP],

                                        t.Comment AS [Comment],
                                        ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                                        act.[Description] AS [Active],
                                       	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                                    FROM AS_Transactions t
--2004.5.8                                    
										LEFT OUTER JOIN AS_Transaction_Details d
											ON t.AS_Transactions_ID = d.AS_Transactions_ID
--2004.5.8											                                    
                                        INNER JOIN AC_Customers c
                                            ON t.AC_Customers_ID = c.AC_Customers_ID
                                        INNER JOIN Sys_AW_Transaction_Types tt
                                            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                                        LEFT JOIN AC_Assets a
                                            ON c.AC_Customers_ID = a.AC_Customers_ID
                                        LEFT JOIN AW_Comments com
                                            ON t.AW_Comments_ID = com.AW_Comments_ID
                                        INNER JOIN AW_Users u
                                            ON t.[User_ID] = u.AW_Users_ID
                                        INNER JOIN Slu_AW_Active act
                                            ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                                    WHERE t.AW_User_Companies_ID = @CompanyID AND
                                          t.Sys_AW_Transaction_Types_ID = @TransactionTypeID AND
        
                                        ((c.Surname like @Filter1 AND
                                          c.Surname like @Filter2) OR
                                         (c.Firstnames like @Filter1 AND
                                          c.Firstnames like @Filter2) OR
                                         (c.Cell_No like @Filter1 AND
                                          c.Cell_No like @Filter2) OR
                                         (c.Work_Tel_No like @Filter1 AND
                                          c.Work_Tel_No like @Filter2) OR
                                         (c.Home_Tel_No like @Filter1 AND
                                          c.Home_Tel_No like @Filter2) OR
                                         (t.Transaction_Number like @Filter1 AND
                                          t.Transaction_Number like @Filter2) OR
                                         (t.Customer_Reference like @Filter1 AND
                                          t.Customer_Reference like @Filter2) OR
                                         (CONVERT(varchar(30), t.Total_Amount) like @Filter1 AND
                                          CONVERT(varchar(30), t.Total_Amount) like @Filter2) OR
                                         (t.Comment like @Filter1 AND
                                          t.Comment like @Filter2) OR
                                         (com.Comment like @Filter1 AND
                                          com.Comment like @Filter2) OR
--2004.5.8                                          
                                          (d.AS_Job_References_ID like @Filter1 AND
                                           d.AS_Job_References_ID like @Filter2) OR                                        
                                          (d.Serial_No like @Filter1 AND
                                           d.Serial_No like @Filter2) OR
--2004.5.8                                                                                    
                                         (a.Reg_No like @Filter1 AND
                                          a.Reg_No like @Filter2)) AND
                
                                         (t.Sys_AW_Transaction_Types_ID = 1 or
                                          t.Sys_AW_Transaction_Types_ID = 2 or 
                                          t.Sys_AW_Transaction_Types_ID = 3 or 
                                          t.Sys_AW_Transaction_Types_ID = 4 or 
                                          t.Sys_AW_Transaction_Types_ID = 5 or 
                                          t.Sys_AW_Transaction_Types_ID = 7 or 
                                          t.Sys_AW_Transaction_Types_ID = 15 or 
                                          t.Sys_AW_Transaction_Types_ID = 18 or
                                          t.Sys_AW_Transaction_Types_ID = 21 or 
                                          t.Sys_AW_Transaction_Types_ID = 22 or 
                    	                  t.Sys_AW_Transaction_Types_ID = 23) AND
                                          DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                                          DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0
                                    ORDER BY t.Transaction_Date DESC
                                END
        
                			IF @Filter1 <> ''
                				BEGIN
                					SET @Filter1 = '%' +  @Filter1 + '%'
        
                                    SELECT DISTINCT
                                        t.AS_Transactions_ID AS [ID],
                                        t.AC_Customers_ID AS [CustomerID],
                                        RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                                        tt.[Description] AS [Type],
                                        t.Transaction_Date AS [Date],
                                        t.Transaction_Number AS [No],
                                        t.Customer_Reference AS [Ref No],
                                        t.Amount AS [Amount],
                                        t.Total_Discount_Amount AS [Discount],
                                        t.Total_Tax_Amount AS [VAT],
                                        t.Total_Amount AS [Total],
                                        t.Payment_Amount AS [Payment],
                                        t.Total_Cost AS [Total Cost],

                                        CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 18 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 21 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 22
                                             THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                                             ELSE 0
                                        END AS [GP],

                                        t.Comment AS [Comment],
                                        ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                                        act.[Description] AS [Active],
                                       	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                                    FROM AS_Transactions t
--2004.5.8                                    
										LEFT OUTER JOIN AS_Transaction_Details d
											ON t.AS_Transactions_ID = d.AS_Transactions_ID
--2004.5.8											                                    
                                        INNER JOIN AC_Customers c
                                            ON t.AC_Customers_ID = c.AC_Customers_ID
                                        INNER JOIN Sys_AW_Transaction_Types tt
                                            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                                        LEFT JOIN AC_Assets a
                                            ON c.AC_Customers_ID = a.AC_Customers_ID
                                        LEFT JOIN AW_Comments com
                                            ON t.AW_Comments_ID = com.AW_Comments_ID
                                        INNER JOIN AW_Users u
                                            ON t.[User_ID] = u.AW_Users_ID
                                        INNER JOIN Slu_AW_Active act
                                            ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                                    WHERE t.AW_User_Companies_ID = @CompanyID AND
                                          t.Sys_AW_Transaction_Types_ID = @TransactionTypeID AND
        
                                         (c.Surname like @Filter1 OR
                                          c.Firstnames like @Filter1 OR
                                          c.Cell_No like @Filter1 OR
                                          c.Work_Tel_No like @Filter1 OR
                                          c.Home_Tel_No like @Filter1 OR
                                          t.Transaction_Number like @Filter1 OR
                                          t.Customer_Reference like @Filter1 OR
                                          CONVERT(varchar(30), t.Total_Amount) like @Filter1 OR
                                          t.Comment like @Filter1 OR
                                          com.Comment like @Filter1 OR
--2004.5.8                                          
                                          d.AS_Job_References_ID like @Filter1 OR                                        
                                          d.Serial_No like @Filter1 OR
--2004.5.8                                          
                                          a.Reg_No like @Filter1) AND
        
                                         (t.Sys_AW_Transaction_Types_ID = 1 or
                                          t.Sys_AW_Transaction_Types_ID = 2 or 
                                          t.Sys_AW_Transaction_Types_ID = 3 or 
                                          t.Sys_AW_Transaction_Types_ID = 4 or 
                                          t.Sys_AW_Transaction_Types_ID = 5 or 
                                          t.Sys_AW_Transaction_Types_ID = 7 or 
                                          t.Sys_AW_Transaction_Types_ID = 15 or 
                                          t.Sys_AW_Transaction_Types_ID = 18 or
                                          t.Sys_AW_Transaction_Types_ID = 21 or 
                                          t.Sys_AW_Transaction_Types_ID = 22 or 
                    	                  t.Sys_AW_Transaction_Types_ID = 23) AND
                                          DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                                          DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0
                                    ORDER BY t.Transaction_Date DESC
                                END
                        END
----------------------------------------->>>>>>>
                    ELSE
                        BEGIN
                            SELECT DISTINCT
                                t.AS_Transactions_ID AS [ID],
                                t.AC_Customers_ID AS [CustomerID],
                                RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                                tt.[Description] AS [Type],
                                t.Transaction_Date AS [Date],
                                t.Transaction_Number AS [No],
                                t.Customer_Reference AS [Ref No],
                                t.Amount AS [Amount],
                                t.Total_Discount_Amount AS [Discount],
                                t.Total_Tax_Amount AS [VAT],
                                t.Total_Amount AS [Total],
                                t.Payment_Amount AS [Payment],
                                t.Total_Cost AS [Total Cost],

                                CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                          t.Sys_AW_Transaction_Types_ID <> 18 AND
                                          t.Sys_AW_Transaction_Types_ID <> 21 AND
                                          t.Sys_AW_Transaction_Types_ID <> 22
                                     THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                                     ELSE 0
                                END AS [GP],

                                t.Comment AS [Comment],
                                ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                                act.[Description] AS [Active],
                               	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                            FROM AS_Transactions t
                                INNER JOIN AC_Customers c
                                    ON t.AC_Customers_ID = c.AC_Customers_ID
                                INNER JOIN Sys_AW_Transaction_Types tt
                                    ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                                INNER JOIN AW_Users u
                                    ON t.[User_ID] = u.AW_Users_ID
                                INNER JOIN Slu_AW_Active act
                                    ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                            WHERE t.Sys_AW_Transaction_Types_ID = @TransactionTypeID AND
                                 (DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                                  DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0)
                            ORDER BY t.Transaction_Date DESC
                        END
----------------------------------------->>>>>>>
                END
-----------------------------------------
        --> ALL Transaction Types
            ELSE
                BEGIN
----------------------------------------->>>>>>>
                    IF @SearchString <> ''
                        BEGIN
----------------------------------------->>>>>>>
                			IF @Filter3 <> ''
                				BEGIN
                					SET @Filter3 = '%' +  @Filter3 + '%'
                					SET @Filter2 = '%' +  @Filter2 + '%'
                					SET @Filter1 = '%' +  @Filter1 + '%'
        
                                    SELECT DISTINCT
                                        t.AS_Transactions_ID AS [ID],
                                        t.AC_Customers_ID AS [CustomerID],
                                        RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                                        tt.[Description] AS [Type],
                                        t.Transaction_Date AS [Date],
                                        t.Transaction_Number AS [No],
                                        t.Customer_Reference AS [Ref No],
                                        t.Amount AS [Amount],
                                        t.Total_Discount_Amount AS [Discount],
                                        t.Total_Tax_Amount AS [VAT],
                                        t.Total_Amount AS [Total],
                                        t.Payment_Amount AS [Payment],
                                        t.Total_Cost AS [Total Cost],

                                        CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 18 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 21 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 22
                                             THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                                             ELSE 0
                                        END AS [GP],

                                        t.Comment AS [Comment],
                                        ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                                        act.[Description] AS [Active],
                                       	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                                    FROM AS_Transactions t
--2004.5.8                                    
										LEFT OUTER JOIN AS_Transaction_Details d
											ON t.AS_Transactions_ID = d.AS_Transactions_ID
--2004.5.8											                                    
                                        INNER JOIN AC_Customers c
                                            ON t.AC_Customers_ID = c.AC_Customers_ID
                                        INNER JOIN Sys_AW_Transaction_Types tt
                                            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                                        LEFT JOIN AC_Assets a
                                            ON c.AC_Customers_ID = a.AC_Customers_ID
                                        LEFT JOIN AW_Comments com
                                            ON t.AW_Comments_ID = com.AW_Comments_ID
                                        INNER JOIN AW_Users u
                                            ON t.[User_ID] = u.AW_Users_ID
                                        INNER JOIN Slu_AW_Active act
                                            ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                                    WHERE t.AW_User_Companies_ID = @CompanyID AND
        
                                         ((c.Surname like @Filter1 AND
                                           c.Surname like @Filter2 AND
                                           c.Surname like @Filter3 ) OR 
                                          (c.Firstnames like @Filter1 AND
                                           c.Firstnames like @Filter2 AND
                                           c.Firstnames like @Filter3) OR 
                                          (c.Cell_No like @Filter1 AND
                                           c.Cell_No like @Filter2 AND
                                           c.Cell_No like @Filter3) OR 
                                          (c.Work_Tel_No like @Filter1 AND
                                           c.Work_Tel_No like @Filter2 AND
                                           c.Work_Tel_No like @Filter3) OR 
                                          (c.Home_Tel_No like @Filter1 AND
                                           c.Home_Tel_No like @Filter2 AND
                                           c.Home_Tel_No like @Filter3) OR 
                                          (t.Transaction_Number like @Filter1 AND
                                           t.Transaction_Number like @Filter2 AND
                                           t.Transaction_Number like @Filter3) OR
                                          (t.Customer_Reference like @Filter1 AND
                                           t.Customer_Reference like @Filter2 AND
                                           t.Customer_Reference like @Filter3) OR
                                          (CONVERT(varchar(30), t.Total_Amount) like @Filter1 AND
                                           CONVERT(varchar(30), t.Total_Amount) like @Filter2 AND
                                           CONVERT(varchar(30), t.Total_Amount) like @Filter3) OR
                                          (t.Comment like @Filter1 AND
                                           t.Comment like @Filter2 AND
                                           t.Comment like @Filter3) OR
                                          (com.Comment like @Filter1 AND
                                           com.Comment like @Filter2 AND
                                           com.Comment like @Filter3) OR
--2004.5.8                                          
                                         (d.AS_Job_References_ID like @Filter1 AND
                                          d.AS_Job_References_ID like @Filter2 AND
                                          d.AS_Job_References_ID like @Filter3) OR
                                         (d.Serial_No like @Filter1 AND
                                          d.Serial_No like @Filter2 AND
                                          d.Serial_No like @Filter3) OR                                          
--2004.5.8                                                                                     
                                          (a.Reg_No like @Filter1 AND
                                           a.Reg_No like @Filter2 AND
                                           a.Reg_No like @Filter3)) AND
                
                                          (t.Sys_AW_Transaction_Types_ID = 1 or
                                           t.Sys_AW_Transaction_Types_ID = 2 or 
                                           t.Sys_AW_Transaction_Types_ID = 3 or 
                                           t.Sys_AW_Transaction_Types_ID = 4 or 
                                           t.Sys_AW_Transaction_Types_ID = 5 or 
                                           t.Sys_AW_Transaction_Types_ID = 7 or 
                                           t.Sys_AW_Transaction_Types_ID = 15 or 
                                    	   t.Sys_AW_Transaction_Types_ID = 18 or
                                           t.Sys_AW_Transaction_Types_ID = 21 or 
                                           t.Sys_AW_Transaction_Types_ID = 22 or 
                    	                   t.Sys_AW_Transaction_Types_ID = 23) AND
                                           DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                                           DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0
                                    ORDER BY t.Transaction_Date DESC
                                END
        
                			IF @Filter2 <> ''
                				BEGIN
                					SET @Filter2 = '%' +  @Filter2 + '%'
                					SET @Filter1 = '%' +  @Filter1 + '%'
        
                                    SELECT DISTINCT
                                        t.AS_Transactions_ID AS [ID],
                                        t.AC_Customers_ID AS [CustomerID],
                                        RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                                        tt.[Description] AS [Type],
                                        t.Transaction_Date AS [Date],
                                        t.Transaction_Number AS [No],
                                        t.Customer_Reference AS [Ref No],
                                        t.Amount AS [Amount],
                                        t.Total_Discount_Amount AS [Discount],
                                        t.Total_Tax_Amount AS [VAT],
                                        t.Total_Amount AS [Total],
                                        t.Payment_Amount AS [Payment],
                                        t.Total_Cost AS [Total Cost],

                                        CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 18 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 21 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 22
                                             THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                                             ELSE 0
                                        END AS [GP],

                                        t.Comment AS [Comment],
                                        ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                                        act.[Description] AS [Active],
                                       	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                                    FROM AS_Transactions t
--2004.5.8                                    
										LEFT OUTER JOIN AS_Transaction_Details d
											ON t.AS_Transactions_ID = d.AS_Transactions_ID
--2004.5.8											
                                        INNER JOIN AC_Customers c
                                            ON t.AC_Customers_ID = c.AC_Customers_ID
                                        INNER JOIN Sys_AW_Transaction_Types tt
                                            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                                        LEFT JOIN AC_Assets a
                                            ON c.AC_Customers_ID = a.AC_Customers_ID
                                        LEFT JOIN AW_Comments com
                                            ON t.AW_Comments_ID = com.AW_Comments_ID
                                        INNER JOIN AW_Users u
                                            ON t.[User_ID] = u.AW_Users_ID
                                        INNER JOIN Slu_AW_Active act
                                            ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                                    WHERE t.AW_User_Companies_ID = @CompanyID AND
        
                                        ((c.Surname like @Filter1 AND
                                          c.Surname like @Filter2) OR
                                         (c.Firstnames like @Filter1 AND
                                          c.Firstnames like @Filter2) OR
                                         (c.Cell_No like @Filter1 AND
                                          c.Cell_No like @Filter2) OR
                                         (c.Work_Tel_No like @Filter1 AND
                                          c.Work_Tel_No like @Filter2) OR
                                         (c.Home_Tel_No like @Filter1 AND
                                          c.Home_Tel_No like @Filter2) OR
                                         (t.Transaction_Number like @Filter1 AND
                                          t.Transaction_Number like @Filter2) OR
                                         (t.Customer_Reference like @Filter1 AND
                                          t.Customer_Reference like @Filter2) OR
                                         (CONVERT(varchar(30), t.Total_Amount) like @Filter1 AND
                                          CONVERT(varchar(30), t.Total_Amount) like @Filter2) OR
                                         (t.Comment like @Filter1 AND
                                          t.Comment like @Filter2) OR
                                         (com.Comment like @Filter1 AND
                                          com.Comment like @Filter2) OR
--2004.5.8                                          
                                         (d.AS_Job_References_ID like @Filter1 AND
                                          d.AS_Job_References_ID like @Filter2) OR
                                         (d.Serial_No like @Filter1 AND
                                          d.Serial_No like @Filter2) OR                                          
--2004.5.8                                          
                                         (a.Reg_No like @Filter1 AND
                                          a.Reg_No like @Filter2)) AND
                
                                         (t.Sys_AW_Transaction_Types_ID = 1 or
                                          t.Sys_AW_Transaction_Types_ID = 2 or 
                                          t.Sys_AW_Transaction_Types_ID = 3 or 
                                          t.Sys_AW_Transaction_Types_ID = 4 or 
                                          t.Sys_AW_Transaction_Types_ID = 5 or 
                                          t.Sys_AW_Transaction_Types_ID = 7 or 
                                          t.Sys_AW_Transaction_Types_ID = 15 or 
                                          t.Sys_AW_Transaction_Types_ID = 16 or 
                                          t.Sys_AW_Transaction_Types_ID = 18 or
                                          t.Sys_AW_Transaction_Types_ID = 21 or 
                                          t.Sys_AW_Transaction_Types_ID = 22 or 
                    	                  t.Sys_AW_Transaction_Types_ID = 23) AND
                                          DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                                          DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0
                                    ORDER BY t.Transaction_Date DESC
                                END
        
                			IF @Filter1 <> ''
                				BEGIN
                					SET @Filter1 = '%' +  @Filter1 + '%'
        
                                    SELECT DISTINCT
                                        t.AS_Transactions_ID AS [ID],
                                        t.AC_Customers_ID AS [CustomerID],
                                        RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                                        tt.[Description] AS [Type],
                                        t.Transaction_Date AS [Date],
                                        t.Transaction_Number AS [No],
                                        t.Customer_Reference AS [Ref No],
                                        t.Amount AS [Amount],
                                        t.Total_Discount_Amount AS [Discount],
                                        t.Total_Tax_Amount AS [VAT],
                                        t.Total_Amount AS [Total],
                                        t.Payment_Amount AS [Payment],
                                        t.Total_Cost AS [Total Cost],

                                        CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 18 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 21 AND
                                                  t.Sys_AW_Transaction_Types_ID <> 22
                                             THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                                             ELSE 0
                                        END AS [GP],

                                        t.Comment AS [Comment],
                                        ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                                        act.[Description] AS [Active],
                                       	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                                    FROM AS_Transactions t
--2004.5.8                                    
										LEFT OUTER JOIN AS_Transaction_Details d
											ON t.AS_Transactions_ID = d.AS_Transactions_ID
--2004.5.8											                                    
                                        INNER JOIN AC_Customers c
                                            ON t.AC_Customers_ID = c.AC_Customers_ID
                                        INNER JOIN Sys_AW_Transaction_Types tt
                                            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                                        LEFT JOIN AC_Assets a
                                            ON c.AC_Customers_ID = a.AC_Customers_ID
                                        LEFT JOIN AW_Comments com
                                            ON t.AW_Comments_ID = com.AW_Comments_ID
                                        INNER JOIN AW_Users u
                                            ON t.[User_ID] = u.AW_Users_ID
                                        INNER JOIN Slu_AW_Active act
                                            ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                                    WHERE t.AW_User_Companies_ID = @CompanyID AND
        
                                         (c.Surname like @Filter1 OR
                                          c.Firstnames like @Filter1 OR
                                          c.Cell_No like @Filter1 OR
                                          c.Work_Tel_No like @Filter1 OR
                                          c.Home_Tel_No like @Filter1 OR
                                          t.Transaction_Number like @Filter1 OR
                                          t.Customer_Reference like @Filter1 OR
                                          CONVERT(varchar(30), t.Total_Amount) like @Filter1 OR
                                          t.Comment like @Filter1 OR
                                          com.Comment like @Filter1 OR
--2004.5.8                                          
                                          d.AS_Job_References_ID like @Filter1 OR
                                          d.Serial_No like @Filter1 OR                                          
--2004.5.8                                                                                    
                                          a.Reg_No like @Filter1) AND
        
                                         (t.Sys_AW_Transaction_Types_ID = 1 or
                                          t.Sys_AW_Transaction_Types_ID = 2 or 
                                          t.Sys_AW_Transaction_Types_ID = 3 or 
                                          t.Sys_AW_Transaction_Types_ID = 4 or 
                                          t.Sys_AW_Transaction_Types_ID = 5 or 
                                          t.Sys_AW_Transaction_Types_ID = 7 or 
                                          t.Sys_AW_Transaction_Types_ID = 15 or 
                                          t.Sys_AW_Transaction_Types_ID = 16 or 
                                          t.Sys_AW_Transaction_Types_ID = 18 or
                                          t.Sys_AW_Transaction_Types_ID = 21 or 
                                          t.Sys_AW_Transaction_Types_ID = 22 or 
                    	                  t.Sys_AW_Transaction_Types_ID = 23) AND
                                          DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                                          DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0
                                    ORDER BY t.Transaction_Date DESC
                                END
                        END
----------------------------------------->>>>>>>
                    ELSE
                        BEGIN
                            SELECT DISTINCT
                                t.AS_Transactions_ID AS [ID],
                                t.AC_Customers_ID AS [CustomerID],
                                RTRIM(LTRIM(ISNULL(c.Surname, '') + ISNULL(', ' + c.FirstNames, ''))) AS [Customer],
                                tt.[Description] AS [Type],
                                t.Transaction_Date AS [Date],
                                t.Transaction_Number AS [No],
                                t.Customer_Reference AS [Ref No],
                                t.Amount AS [Amount],
                                t.Total_Discount_Amount AS [Discount],
                                t.Total_Tax_Amount AS [VAT],
                                t.Total_Amount AS [Total],
                                t.Payment_Amount AS [Payment],
                                t.Total_Cost AS [Total Cost],

                                CASE WHEN t.Sys_AW_Transaction_Types_ID <> 7 AND
                                          t.Sys_AW_Transaction_Types_ID <> 18 AND
                                          t.Sys_AW_Transaction_Types_ID <> 21 AND
                                          t.Sys_AW_Transaction_Types_ID <> 22
                                     THEN CONVERT(money,(t.Total_Amount - t.Total_Tax_Amount) - (t.Total_Cost))
                                     ELSE 0
                                END AS [GP],

                                t.Comment AS [Comment],
                                ISNULL(u.Firstname, '') + ISNULL(' ' + u.Surname, '') AS [User],
                                act.[Description] AS [Active],
                               	t.Sys_AW_Transaction_Types_ID AS [TypeID]
                            FROM AS_Transactions t
                                INNER JOIN AC_Customers c
                                    ON t.AC_Customers_ID = c.AC_Customers_ID
                                INNER JOIN Sys_AW_Transaction_Types tt
                                    ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
                                INNER JOIN AW_Users u
                                    ON t.[User_ID] = u.AW_Users_ID
                                INNER JOIN Slu_AW_Active act
                                    ON t.Slu_AW_Active_ID = act.Slu_AW_Active_ID
                            WHERE (DATEDIFF(d, t.Transaction_Date, @StartDate) <= 0 AND
                                   DATEDIFF(d, t.Transaction_Date, @EndDate) >= 0)
                            ORDER BY t.Transaction_Date DESC
                        END
----------------------------------------->>>>>>>
                END
        END
Go

--11
/****** Object:  StoredProcedure [dbo].[sp_AS_POS_RPT_Invoice_Print]    Script Date: 09/24/2012 05:28:57 ******/
IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AS_POS_RPT_Invoice_Print]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AS_POS_RPT_Invoice_Print]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-------------------------------------------------------------------------------
--Description   :sp_AS_POS_RPT_Invoice_Print
--Purpose       :POS Invoice printing
--Parameters    :
--Date          :2002/04/12
--Authors       :TB
--MODS:         :12/04/02 - Added Time extracted from Transaction_Date
--              :18/04/02 - Added AW_Users.UserName
--              :21/04/02 - Added (AC_Customers_ID) to Customer_Number
--              :22/04/02 - Changed Customer Addresses from Physical to Postal
--              :10/06/02 - Added Company Tel_No & Company Fax_No fields
--              :18/07/02 - Added Discount % per Line Item as [DetailDiscPerc]
--              :10/09/02 - Convert Discount % per Line from SMALLMONEY to MONEY
--              :30/12/02 - Added t.SmallComment AS [Comment] for POS Asset No
--              :26/05/03 - Added ISNULL validation to customer address fields
--              :26/05/03 - Added Cell/Home/Work telephone number (next to Post Code)
--              :09/07/03 - Added Tax_Number (VATNo) effective 1 APR 2004 per SARS
--              :29/01/04 - Added [TranTypeID] to print a PICKLIST with a JOBCARD
--              :09/02/04 - Added [JobSource] to print any JOBCARD source docs
--              :07/06/04 - Added t.AW_User_Companies_ID to locate Company Logo
--              :           path parameter in SpSPOSPrint.CCutSheetLogoA4
--              :17/08/04 - Added p.Pack_Mass to aggregate invoice load mass
--              :21/12/04 - Added XREF to Sys_AW_Payment_Methods and PRINT
--              :08/01/05 - Added [JobRefNo] to print on new MAXIPREST layout
--              :02/02/05 - Added [TextMessage] to print on all invoice documents
--              :19/05/05 - Removed Payment Method from primary SELECT and pre-fetch
--2004.2.4      :28/08/05 - Added t.Amount AS TransactionAmount for OPOS printing
--              :28/10/05 - Added Street and Delivery Adresses
--              :28/10/05 - Added System Option address choice using @WhichAddress
--2004.2.7      :13/04/06 - Replaced SELECT vw_AS_Transactions with AS_Transactions
--2004.3.1      :21/08/09 - Rearranged order of telephone numbers: Work/Cell/Home
--2004.3.2      :08/10/06 - Added [SerialNo] fields
--2004.3.3      :23/11/06 - Added [NameType] for Company Trading Name
--2004.3.4      :23/11/06 - Added Old_Customer_Number as [MerchantNo] for Card Companies
--              :21/03/07 - Added [Make] and [Customer] fields
--2004.3.9      :05/07/08 - Renamed Job XRef from AS_Transaction_Sources
--              :           from 'Jobcard No: ' to 'Sales Order/Job No: '
--2004.4.5      :18/07/09 - Converted into DYNAMIC script to include NEW
--              :           POS Document Print Order of items settings
--2004.4.8      :17/04/10 - Reworked setting DEFAULT message and Added new QUOTE message
--2004.5.0      :26/01/11 - Added [Emails] for all customer e-mail addresses
--              :26/01/11 - Added [CompanyEmail] for company e-mail address
--2004.5.3      :18/07/11 - Added REPLACE syntax to REMOVE single APOSTROPHE in MESSAGES text
--2004.5.7 RGL  :2012-09-20 - Added check to to print tradeing name with company name
--2004.5.8 RGL  :2013-02-20 - Fixed check to to print tradeing name with customer and not company name
-------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp_AS_POS_RPT_Invoice_Print]
    (
    	@TransactionID int
    )
AS

SET NOCOUNT ON

    DECLARE @SortOrder tinyint,
            @TranTypeID tinyint,
            @Message varchar(100),
            @DefaultMessage varchar(100),
            @QuoteMessage varchar(100),
            @PrintDefaultMessage varchar(10),
            @PrintQuoteMessage varchar(10),
            @OrderFilter tinyint,
            @sql varchar(8000)            

    --> Get TranTypeID
    SELECT @TranTypeID = Sys_AW_Transaction_Types_ID
    FROM AS_Transactions
    WHERE AS_Transactions_ID = @TransactionID

    --> Get POS Document Print Sort Order settings
    SELECT @SortOrder = ISNULL(Control_Value, 0)
    FROM Sys_AW_Options
    WHERE Control_Index = 0 AND
    Control_Type = 'CheckBox' AND
    Control_Name = 'chkPOSPrintSortOrder'
    
    --> Get POS Document Print Sort Order Filter
    SELECT @OrderFilter = ISNULL(Control_Index, 0)
    FROM Sys_AW_Options
    WHERE Control_Type = 'OptionButton' AND
    Control_Name = 'optPOSSortOrderFilter' AND
    Control_Value = 'True'

    --------------------------------------------------------------------
    -- Set the MESSAGES text
    --------------------------------------------------------------------
    --> Get the DEFAULT Message text
    SELECT TOP 1 @DefaultMessage = REPLACE(ISNULL(Control_Value, ''), '''', '')
    FROM Sys_AW_Options
    WHERE Control_Index = 0 AND
          Control_Type = 'TextBox' AND
          Control_Name = 'txtPOSDocumentText'

    --> Get the DEFAULT Message CheckBox setting
    SELECT TOP 1 @PrintDefaultMessage = REPLACE(ISNULL(Control_Value, ''), '''', '')
    FROM Sys_AW_Options
    WHERE Control_Index = 0 AND
          Control_Type = 'CheckBox' AND
          Control_Name = 'chkPOSDocumentText'

    --> Get the QUOTE Message text
    SELECT TOP 1 @QuoteMessage = REPLACE(ISNULL(Control_Value, ''), '''', '')
    FROM Sys_AW_Options
    WHERE Control_Index = 0 AND
          Control_Type = 'TextBox' AND
          Control_Name = 'txtPOSQuoteText'

    --> Get the QUOTE Message CheckBox setting
    SELECT TOP 1 @PrintQuoteMessage = REPLACE(ISNULL(Control_Value, ''), '''', '')
    FROM Sys_AW_Options
    WHERE Control_Index = 0 AND
          Control_Type = 'CheckBox' AND
          Control_Name = 'chkPOSQuoteText'

    SET @Message = ''

    IF @TranTypeID = 1        --Quote message
        BEGIN
            IF @PrintQuoteMessage = '1' SET @Message = @QuoteMessage
        END
    ELSE                      --Default message
        BEGIN
            IF @PrintDefaultMessage = '1' SET @Message = @DefaultMessage
        END

    SET @Message = LEFT(@Message, 80)
    --------------------------------------------------------------------

	SET @sql = ' ' 
	SET @sql = @sql + ' DECLARE @WhichAddress tinyint, '
	SET @sql = @sql + ' @WhichName tinyint, '
	SET @sql = @sql + ' @PaymentMethod varchar(20) '

	SET @sql = @sql + ' SET @WhichAddress = 0 '

    --> Get Print POSTAL or STREET Addres (0 = POSTAL, 1 = STREET)
	SET @sql = @sql + ' SELECT @WhichAddress = ISNULL(Control_Index, 0) '
	SET @sql = @sql + ' FROM Sys_AW_Options '
	SET @sql = @sql + ' WHERE Control_Type = ''OptionButton'' AND '
	SET @sql = @sql + ' Control_Name = ''optPOSPrintAddress'' AND '
	SET @sql = @sql + ' Control_Value = ''True'' '

	SET @sql = @sql + ' SELECT TOP 1 @WhichName = ISNULL(Control_Value, ''0'') '
	SET @sql = @sql + ' FROM Sys_AW_Options '
	SET @sql = @sql + ' WHERE Control_Index = 0 AND '
	SET @sql = @sql + ' Control_Type = ''CheckBox'' AND '
	SET @sql = @sql + ' Control_Name = ''chkPOSPrintTradeName'' '

	SET @sql = @sql + ' SELECT TOP 1 @PaymentMethod = LEFT(ISNULL(pm.[Description], ''''), 20) '
	SET @sql = @sql + ' FROM AS_Transactions_Transactions att '
	SET @sql = @sql + ' LEFT JOIN AS_Transaction_Payments tp '
	SET @sql = @sql + ' ON tp.AS_Transactions_ID = att.Destination_AS_Transactions_ID '
	SET @sql = @sql + ' LEFT JOIN Sys_AW_Payment_Methods pm '
	SET @sql = @sql + ' ON pm.Sys_AW_Payment_Methods_ID = tp.Sys_AW_Payment_Methods_ID '
	SET @sql = @sql + ' WHERE att.Source_AS_Transactions_ID = ' + CONVERT(varchar(20), @TransactionID)
	SET @sql = @sql + ' ORDER BY att.Source_AS_Transactions_ID '

    --------------------------------------------------------------------------------------------------
    --The query starts here
    --------------------------------------------------------------------------------------------------
	SET @sql = @sql + ' SELECT '

	--SET @sql = @sql + ' RTRIM(LTRIM(c.Surname)) + ISNULL('', '' + c.Initials, '''') AS [CustomerName], '	
    SET @sql = @sql + ' CASE WHEN @WhichName = 1 '
    SET @sql = @sql + ' THEN RTRIM(LTRIM(c.Surname)) + ISNULL('', '' + c.Initials, '''') + '' / '' + IsNull(c.Firstnames,'''')'
    SET @sql = @sql + ' ELSE RTRIM(LTRIM(c.Surname)) + ISNULL('', '' + c.Initials, '''') '
    SET @sql = @sql + ' END AS [CustomerName], '

    --> NB NB: Customer ADD in POS saves with STREET address copied to POSTAL Address
	SET @sql = @sql + ' ISNULL(c.Post_Box, '''') AS [PostBox], '
	SET @sql = @sql + ' ISNULL(c.Post_Box, '''') AS [PostBox], '
	SET @sql = @sql + ' ISNULL(c.Post_Office, '''') AS [PostOffice], '
	SET @sql = @sql + ' ISNULL(c.City, '''') AS [PostCity], '
	SET @sql = @sql + ' ISNULL(c.Post_Code, '''') AS [PostCode], '

	SET @sql = @sql + ' ISNULL(c.Street_Address, '''') AS [StreetAddress], '
	SET @sql = @sql + ' ISNULL(c.Suburb, '''') AS [StreetSuburb], '
	SET @sql = @sql + ' ISNULL(c.City, '''') AS [StreetCity], '
	SET @sql = @sql + ' ISNULL(c.Street_Post_Code, '''') AS [StreetPostCode], '

	SET @sql = @sql + ' ISNULL(c.Delivery_Address, '''') AS [DeliverAddress], '
	SET @sql = @sql + ' ISNULL(c.Delivery_Suburb, '''') AS [DeliverSuburb], '
	SET @sql = @sql + ' ISNULL(c.Delivery_City, '''') AS [DeliverCity], '
	SET @sql = @sql + ' ISNULL(c.Delivery_Post_Code, '''') AS [DeliverPostCode], '

	SET @sql = @sql + ' ISNULL(c.Customer_Number, '''') + ''('' + CONVERT(VARCHAR(12), c.AC_Customers_ID) + '')'' AS [CustomerNo], '

    SET @sql = @sql + ' CASE WHEN RTRIM(LTRIM(c.Old_Customer_Number)) <> '''' '
    SET @sql = @sql + ' THEN ''Merchant No: '' + RTRIM(LTRIM(c.Old_Customer_Number)) '
    SET @sql = @sql + ' ELSE '''' '
    SET @sql = @sql + ' END AS [MerchantNo], '

    SET @sql = @sql + ' t.Sys_AW_Transaction_Types_ID AS [TranTypeID], '
    SET @sql = @sql + ' tt.[Description] AS [TransactionType], '
    SET @sql = @sql + ' comp.Tax_Number AS [CompanyTaxNumber], '
    SET @sql = @sql + ' comp.E_Mail AS [CompanyEmail], '
    SET @sql = @sql + ' comp.Company_Name AS [CompanyName], '
--    SET @sql = @sql + ' CASE WHEN @WhichName = 1 '
--    SET @sql = @sql + ' THEN comp.Company_Name + '' / '' + IsNull(comp.Company_Trading_Name,'''')'
--    SET @sql = @sql + ' ELSE comp.Company_Name '
--    SET @sql = @sql + ' END AS [CompanyName], '
    
    SET @sql = @sql + ' comp.Company_Trading_Name AS [TradingName], '
    SET @sql = @sql + ' comp.Company_Addr1 AS [CompanyAddress1], '
    SET @sql = @sql + ' comp.Company_Addr2 AS [CompanyAddress2], '
    SET @sql = @sql + ' comp.Company_City AS [CompanyCity], '
    SET @sql = @sql + ' comp.Company_Post_Code AS [CompanyPostCode], '
    SET @sql = @sql + ' comp.Company_Registration_Number AS [CompanyRegNo], '
    SET @sql = @sql + ' comp.Tel_No AS [CompanyTelNo], '
    SET @sql = @sql + ' comp.Fax_No AS [CompanyFaxNo], '
    SET @sql = @sql + ' rep.[Description] AS [RepName], '
    SET @sql = @sql + ' t.AS_Transactions_ID AS [TransactionID], '
    SET @sql = @sql + ' t.Transaction_Number AS [TransactionNo], '
    SET @sql = @sql + ' CONVERT(varchar(12), t.Transaction_Date, 103) AS [TransactionDate], '
    SET @sql = @sql + ' t.Customer_Reference AS [TransactionCustomerRef], '
    SET @sql = @sql + ' t.Amount AS [TransactionAmount], '
    SET @sql = @sql + ' t.Total_Amount AS [TransactionTotalAmount], '
    SET @sql = @sql + ' t.Total_Discount_Amount AS [TransactionTotalDiscount], '
    SET @sql = @sql + ' t.Total_Tax_Amount AS [TransactionTotalTax], '
    SET @sql = @sql + ' com.Comment AS [TransactionComment], '
-------------------------------------------------------------------------------
    SET @sql = @sql + ' p.Product_Code AS [ProductCode], '
    SET @sql = @sql + ' p.Description AS [ProductDescription], '
    SET @sql = @sql + ' td.Quantity AS [DetailQty], '
    SET @sql = @sql + ' td.Unit_Price AS [DetailUnitPrice], '

    SET @sql = @sql + ' CASE WHEN (td.Unit_Price * td.Quantity) <> 0 '
    SET @sql = @sql + ' THEN CONVERT(money,ABS(ROUND((((td.Discount_Amount) / (td.Unit_Price * td.Quantity)) * 100), 2))) '
    SET @sql = @sql + ' ELSE 0.00 '
    SET @sql = @sql + ' END AS [DetailDiscPerc], '

    SET @sql = @sql + ' td.Discount_Amount AS [DetailDiscount] , '
    SET @sql = @sql + ' (td.Unit_Price * td.Quantity) + td.Discount_Amount AS [DetailAmount], '
    SET @sql = @sql + ' td.Tax_Amount AS [DetailTaxAmount], '
    SET @sql = @sql + ' td.Extended_Amount AS [DetailExtendedAmount], '
    SET @sql = @sql + ' CONVERT(varchar(4), DATEPART(hour, t.Transaction_Date)) + '':'' + '
    SET @sql = @sql + ' CONVERT(varchar(4), DATEPART(minute, t.Transaction_Date)) + '':'' + '
    SET @sql = @sql + ' CONVERT(varchar(4), DATEPART(second, t.Transaction_Date)) AS [TransactionTime], '
    SET @sql = @sql + ' u.UserName AS [UserCode], '
    SET @sql = @sql + ' t.Comment AS [Comment], '
    SET @sql = @sql + ' ISNULL(RTRIM(LTRIM(c.Work_Tel_No)), '''') + '
    SET @sql = @sql + ' ISNULL('' / '' + RTRIM(LTRIM(c.Cell_No)), '''') + '
    SET @sql = @sql + ' ISNULL('' / '' + RTRIM(LTRIM(c.Home_Tel_No)), '''') AS [TelNos], '

    SET @sql = @sql + ' ISNULL(RTRIM(LTRIM(c.Tax_Number)), '''') AS [VATNo], '

    SET @sql = @sql + ' CASE WHEN RTRIM(LTRIM(ts.Transaction_Numbers)) <> '''' '
    SET @sql = @sql + ' THEN ''Sales Order/Job No: '' + RTRIM(LTRIM(ts.Transaction_Numbers)) '
    SET @sql = @sql + ' ELSE '''' '
    SET @sql = @sql + ' END AS [JobSource], '

    SET @sql = @sql + ' t.AW_User_Companies_ID AS [CompanyID], '
    SET @sql = @sql + ' ROUND(ISNULL(td.Quantity, 0) * ISNULL(p.Pack_Mass, 0), 2) AS [PackMass], '
    SET @sql = @sql + ' @PaymentMethod AS [PayMethod], '

    SET @sql = @sql + ' CASE WHEN td.AS_Job_References_ID = 0 '
    SET @sql = @sql + ' THEN NULL '
    SET @sql = @sql + ' ELSE td.AS_Job_References_ID '
    SET @sql = @sql + ' END AS [JobRefNo], '

    SET @sql = @sql +  '''' + @Message + '''' + ' AS [TextMessage], '
    SET @sql = @sql + ' @WhichAddress AS [AddressType], '
    SET @sql = @sql + ' td.Serial_No AS [SerialNo], '
    SET @sql = @sql + ' td.Make AS [Make], '
    SET @sql = @sql + ' td.Customer AS [Customer], '
    SET @sql = @sql + ' @WhichName AS [NameType], '
    SET @sql = @sql + ' ISNULL(RTRIM(LTRIM(c.Orders_URL)), '''') + '
    SET @sql = @sql + ' ISNULL(''; '' + RTRIM(LTRIM(c.Home_Email)), '''') + '
    SET @sql = @sql + ' ISNULL(''; '' + RTRIM(LTRIM(c.Home_Email)), '''') AS [Emails] '

--	FROM vw_AS_Transactions t 
    SET @sql = @sql + ' FROM AS_Transactions t '
    SET @sql = @sql + ' LEFT JOIN AC_Customers c '
    SET @sql = @sql + ' ON t.AC_Customers_ID = c.AC_Customers_ID '
    SET @sql = @sql + ' LEFT JOIN Sys_AW_Transaction_Types tt '
    SET @sql = @sql + ' ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID '
    SET @sql = @sql + ' LEFT JOIN AW_User_Companies comp '
    SET @sql = @sql + ' ON t.AW_User_Companies_ID = comp.AW_User_Companies_ID '
    SET @sql = @sql + ' LEFT JOIN Lup_AS_Reps rep '
    SET @sql = @sql + ' ON t.Lup_AS_Reps_ID = rep.Lup_AS_Reps_ID '
    SET @sql = @sql + ' LEFT JOIN AS_Transaction_Details td '
    SET @sql = @sql + ' ON t.AS_Transactions_ID = td.AS_Transactions_ID '
    SET @sql = @sql + ' LEFT JOIN AP_Products p '
    SET @sql = @sql + ' ON td.AP_Products_ID = p.AP_Products_ID '
    SET @sql = @sql + ' LEFT JOIN AW_Users u '
    SET @sql = @sql + ' ON t.[User_ID] = u.AW_Users_ID '
    SET @sql = @sql + ' LEFT JOIN AS_Transaction_Sources ts '
    SET @sql = @sql + ' ON t.AS_Transactions_ID = ts.AS_Transactions_ID '
    SET @sql = @sql + ' LEFT JOIN AW_Comments com '
    SET @sql = @sql + ' ON t.AW_Comments_ID = com.AW_Comments_ID '
    SET @sql = @sql + ' WHERE t.AS_Transactions_ID = ' + CONVERT(varchar(20), @TransactionID)

    IF @SortOrder = 1
        BEGIN
            IF @OrderFilter = 0        --Product Description
                BEGIN
                    SET @sql = @sql + ' ORDER BY p.Description '
                END
            ELSE
            IF @OrderFilter = 1        --Product Code
                BEGIN
                    SET @sql = @sql + ' ORDER BY p.Product_Code '
                END
            ELSE
            IF @OrderFilter = 2        --Product Type
                BEGIN
                    SET @sql = @sql + ' ORDER BY p.Lup_AP_Product_Types_ID, p.Description '
                END
            ELSE
            IF @OrderFilter = 3        --Product Group
                BEGIN
                    SET @sql = @sql + ' ORDER BY p.Lup_AP_Product_Groups_ID, p.Description '
                END
            ELSE
            IF @OrderFilter = 4        --Product Department
                BEGIN
                    SET @sql = @sql + ' ORDER BY p.Lup_AP_Product_Departments_ID, p.Description '
                END
            ELSE
            IF @OrderFilter = 5        --Product Location
                BEGIN
                    SET @sql = @sql + ' ORDER BY p.Lup_AP_Product_Locations_ID, p.Description '
                END
            ELSE
            IF @OrderFilter = 6        --Product Purchase Category
                BEGIN
                    SET @sql = @sql + ' ORDER BY p.Lup_AP_Purchase_Categories_ID, p.Description '
                END
            ELSE
            IF @OrderFilter = 7        --Product Manufacturer
                BEGIN
                    SET @sql = @sql + ' ORDER BY p.Lup_AP_Product_Manufacturers_ID, p.Description '
                END
        END
    ELSE                    --Default
        BEGIN
            SET @sql = @sql + ' ORDER BY AS_Transaction_Details_ID '
        END

EXEC (@sql)
--Select @sql

SET NOCOUNT OFF
Go

--12

IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AW_Tills_Per_User_Select]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AW_Tills_Per_User_Select]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-------------------------------------------------------------------------------
--Description   :sp_AW_Tills_Per_User_Select
--Purpose       :Select Tills per User
--              :
--Tables        :AW_Users, AW_Users_Per_Role, AW_Users_Tills
--Parameters    :
--Date          :2002/02/21
--Authors       :TB
--MODS:         :13/01/04 - Replaced join with AW_Users_Per_Role (Cashier role)
--              :           with using the IsCashier flag in AW_Users
--2004.5.8 RGL  :Changed Order of result set to user name
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AW_Tills_Per_User_Select]
	(
		@Choice int,
		@CompanyID int
	)
AS

SET NOCOUNT ON

	IF @Choice = 1
		SELECT
            Lup_AS_Tills_ID AS [ID],
            [Description] AS [Till] 
		FROM Lup_AS_Tills
		WHERE AW_User_Companies_ID = @CompanyID

	ELSE
	IF @Choice = 2
		SELECT DISTINCT
            u.AW_Users_ID AS [ID],
            ISNULL(ISNULL(u.Surname,'') + ISNULL(', ' + u.Firstname,''),'') AS [User] 
		FROM AW_Users u
        WHERE u.AW_Users_ID > 0 AND
              u.IsCashier = 1
        ORDER BY [User]

	ELSE
	IF @Choice = 3
		SELECT
			t.AW_Users_Tills_ID AS [ID], 
			t.AW_Users_ID AS [User], 
			t.Lup_AS_Tills_ID AS [Till],
			t.AW_User_Companies_ID AS [CompanyID],
			t.[User_ID] AS [UserID],
			ISNULL(ISNULL(u.Surname,'') + ISNULL(', ' + u.Firstname,''),'') AS [User2] 
		FROM AW_Users_Tills t
		INNER JOIN AW_Users u
			ON t.AW_Users_ID = u.AW_Users_ID
		WHERE t.AW_User_Companies_ID = @CompanyID
		ORDER BY [User2]
Go

--13
IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_RPT_AS_DayEnd_Product_Sales_Analysis]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RPT_AS_DayEnd_Product_Sales_Analysis]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-------------------------------------------------------------------------------
--Description   :sp_RPT_AS_DayEnd_Product_Sales_Analysis
--Purpose       :Day-End Sales by Filter with Product Sales detail ONLY
--Functionality :
--Date          :2003/09/11
--Authors       :TB
--MODS:         :15/04/04 - Added 'Cashier' filter
--              :08/08/04 - Truncated [TranDate] field
--              :06/03/05 - Added Product_Code [Code] field
--2004.5.0      :07/03/11 - Modified [Selling] field to reflect Unit_Price ONLY
--              :08/03/11 - Added ISNULL validation of filter descriptions
--2004.5.3      :04/08/11 - Modified [Ave Sell] field to reflect Net Sales Average
--              :04/08/11 - Added [UnitSelling] field to SUM Unit Price value for Average calc
--2004.5.8 RGL  :2013-02-20 - Added product group to order by clause
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_RPT_AS_DayEnd_Product_Sales_Analysis]
    (
        @Option varchar(30),
        @DayEndFromDate datetime,
        @DayEndToDate datetime,
        @CompanyID int
    )

AS

SET NOCOUNT ON

    DECLARE @sql varchar(8000)


-- SET @Option = 'Groups'
-- SET @CompanyID = 1
-- SET @DayEndFromDate = '2002-10-01 17:27:00'
-- SET @DayEndToDate = '2002-10-31 19:44:00'


    SET @sql = ' SELECT '

    SET @sql = @sql + ' d.AW_User_Companies_ID AS [CompanyID], '

    IF @Option = 'Type'
        SET @sql = @sql +
            ' o.Lup_AP_Product_Types_ID AS [HeaderID], ' +
            ' ISNULL(o.[Description], '''') AS [Filter], '
    ELSE
    IF @Option = 'Groups'
        SET @sql = @sql +
            ' o.Lup_AP_Product_Groups_ID AS [HeaderID], ' +
            ' ISNULL(o.[Description], '''') AS [Filter], '
    ELSE
    IF @Option = 'Department'
        SET @sql = @sql +
            ' o.Lup_AP_Product_Departments_ID AS [HeaderID], ' +
            ' ISNULL(o.[Description], '''') AS [Filter], '
    ELSE
    IF @Option = 'Supplier'
        SET @sql = @sql +
            ' o.AO_Suppliers_ID AS [HeaderID], ' +
            ' ISNULL(o.[Supplier_Name], '''') AS [Filter], '
    ELSE
    IF @Option = 'Manufacturer'
        SET @sql = @sql +
            ' o.Lup_AP_Product_Manufacturers_ID AS [HeaderID], ' +
            ' ISNULL(o.[Description], '''') AS [Filter], '
    ELSE
    IF @Option = 'Location'
        SET @sql = @sql +
            ' o.Lup_AP_Product_Locations_ID AS [HeaderID], ' +
            ' ISNULL(o.[Description], '''') AS [Filter], '
    ELSE
    IF @Option = 'Sales Area'
        SET @sql = @sql +
            ' o.Lup_AC_Sales_Areas_ID AS [HeaderID], ' +
            ' ISNULL(o.[Description], '''') AS [Filter], '
    ELSE
    IF @Option = 'Sales Person'
        SET @sql = @sql +
            ' o.Lup_AS_Reps_ID AS [HeaderID], ' +
            ' ISNULL(o.[Description], '''') AS [Filter], '
    ELSE
    IF @Option = 'Cashier'
        SET @sql = @sql +
            ' o.AW_Users_ID AS [HeaderID], ' +
            ' ISNULL(o.[UserName], '''') AS [Filter], '

    SET @sql = @sql + ' ISNULL(p.[Description], '''') AS [Product], '
    SET @sql = @sql + ' ISNULL(p.Product_Code, '''') AS [Code], '

    SET @sql = @sql + ' ISNULL(c.Surname, '''') + '' '' + '
    SET @sql = @sql + ' ISNULL(c.Firstnames, '''') '
    SET @sql = @sql + ' + '' ('' + CONVERT(varchar(10), c.AC_Customers_ID) + '')'' AS [Customer], '

    SET @sql = @sql + ' CONVERT(CHAR(10), h.Transaction_Date, 121) AS [TranDate],  '
    SET @sql = @sql + ' tt.[Description] AS [TranType], '
    SET @sql = @sql + ' h.Transaction_Number AS [TranNo], '
    SET @sql = @sql + ' h.Customer_Reference AS [CustRef], '
    SET @sql = @sql + ' h.Comment AS [ShortComment], '


    SET @sql = @sql + ' CONVERT(money, d.Quantity) AS [Qty], '
    SET @sql = @sql + ' CONVERT(money, d.Unit_Price * d.Quantity) AS [UnitSelling], '
    SET @sql = @sql + ' ISNULL(CONVERT(money, d.Unit_Price), 0) AS [Selling], '
    SET @sql = @sql + ' CONVERT(money, d.Discount_Amount) AS [Discount], '
    SET @sql = @sql + ' CONVERT(money, d.Extended_Amount - d.Tax_Amount) AS [NetSales], '
    SET @sql = @sql + ' CONVERT(money, d.Tax_Amount) AS [Tax], '
    SET @sql = @sql + ' CONVERT(money, d.Extended_Amount) AS [Total], '

    SET @sql = @sql + ' CONVERT(money, d.Unit_Cost * d.Quantity) AS [Cost], '
    SET @sql = @sql + ' CONVERT(money, (d.Extended_Amount - d.Tax_Amount) - (d.Quantity * d.Unit_Cost)) AS [GP], '
    SET @sql = @sql + ' CASE WHEN dbo.fn_AS_Calc_Act_Sales_Value(d.Extended_Amount, d.Tax_Amount) <> 0 '
    SET @sql = @sql + '      THEN CONVERT(money,((dbo.fn_AS_Calc_Act_GP(d.Extended_Amount, d.Tax_Amount, d.Quantity, d.Unit_Cost)) / dbo.fn_AS_Calc_Act_Sales_Value(d.Extended_Amount, d.Tax_Amount) * 100)) '
    SET @sql = @sql + '      ELSE 0.00 '
    SET @sql = @sql + '      END AS [Act GP%], '
    SET @sql = @sql + ' CASE WHEN dbo.fn_AS_Calc_Std_Sales_Value(d.Original_Price, d.Quantity) <> 0 '
    SET @sql = @sql + '      THEN CONVERT(money,((dbo.fn_AS_Calc_Std_GP(d.Original_Price, d.Unit_Cost, d.Quantity)) / dbo.fn_AS_Calc_Std_Sales_Value(d.Original_Price, d.Quantity) * 100)) '
    SET @sql = @sql + '      ELSE 0.00 '
    SET @sql = @sql + '      END AS [Std GP%], '

    SET @sql = @sql + ' CONVERT(money, (d.Unit_Price - d.Original_Price) * d.Quantity) AS [Price Adj], '
    SET @sql = @sql + ' CONVERT(money, d.Discount_Amount - d.Original_Discount) AS [Disc Adj], '

    SET @sql = @sql + ' CASE WHEN d.Quantity <> 0 '
    SET @sql = @sql + '      THEN CONVERT(money, (d.Unit_Cost * d.Quantity) / d.Quantity) '
    SET @sql = @sql + '      ELSE 0.00 '
    SET @sql = @sql + '      END AS [Ave Cost], '

    SET @sql = @sql + ' CASE WHEN d.Quantity <> 0 '
--    SET @sql = @sql + '      THEN CONVERT(money, (d.Unit_Price * d.Quantity) / d.Quantity) '
    SET @sql = @sql + '      THEN CONVERT(money, (d.Extended_Amount - d.Tax_Amount) / d.Quantity) '
    SET @sql = @sql + '      ELSE 0.00 '
    SET @sql = @sql + '      END AS [Ave Sell], '
    
    SET @sql = @sql + ' CASE WHEN d.Quantity <> 0 '
    SET @sql = @sql + '      THEN CONVERT(money, (d.Discount_Amount) / d.Quantity) '
    SET @sql = @sql + '      ELSE 0.00 '
    SET @sql = @sql + '      END AS [Ave Disc], '

    SET @sql = @sql + ' u.UserName AS [User] '
    
    SET @sql = @sql + ' FROM AS_Transaction_Details d '
    SET @sql = @sql + '      INNER JOIN AS_Transactions h '
    SET @sql = @sql + '          ON h.AS_Transactions_ID = d.AS_Transactions_ID '
    SET @sql = @sql + '      INNER JOIN AP_Products p '
    SET @sql = @sql + '          ON d.AP_Products_ID = p.AP_Products_ID '
    SET @sql = @sql + '      INNER JOIN AC_Customers c '
    SET @sql = @sql + '          ON c.AC_Customers_ID = h.AC_Customers_ID '
    SET @sql = @sql + '      LEFT JOIN Sys_AW_Transaction_Types tt '
    SET @sql = @sql + '          ON h.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID '
    SET @sql = @sql + '      LEFT JOIN AW_Users u '
    SET @sql = @sql + '          ON h.User_ID = u.AW_Users_ID '


    IF @Option = 'Type'
        SET @sql = @sql +
            '    LEFT JOIN Lup_AP_Product_Types o ' +
            '        ON p.Lup_AP_Product_Types_ID = o.Lup_AP_Product_Types_ID '
    ELSE
    IF @Option = 'Groups'
        SET @sql = @sql +
            '    LEFT JOIN Lup_AP_Product_Groups o ' +
            '        ON p.Lup_AP_Product_Groups_ID = o.Lup_AP_Product_Groups_ID '
    ELSE
    IF @Option = 'Department'
        SET @sql = @sql +
            '    LEFT JOIN Lup_AP_Product_Departments o ' +
            '        ON p.Lup_AP_Product_Departments_ID = o.Lup_AP_Product_Departments_ID '
    ELSE
    IF @Option = 'Supplier'
        SET @sql = @sql +
            '    LEFT JOIN AO_Suppliers o ' +
            '        ON p.Default_Supplier_ID = o.AO_Suppliers_ID '
    ELSE
    IF @Option = 'Manufacturer'
        SET @sql = @sql +
            '    LEFT JOIN Lup_AP_Product_Manufacturers o ' +
            '        ON p.Lup_AP_Product_Manufacturers_ID = o.Lup_AP_Product_Manufacturers_ID '
    ELSE
    IF @Option = 'Location'
        SET @sql = @sql +
            '    LEFT JOIN Lup_AP_Product_Locations o ' +
            '        ON p.Lup_AP_Product_Locations_ID = o.Lup_AP_Product_Locations_ID '
    ELSE
    IF @Option = 'Sales Area'
        SET @sql = @sql +
            '    LEFT JOIN Lup_AC_Sales_Areas o ' +
            '        ON c.Lup_AC_Sales_Areas_ID = o.Lup_AC_Sales_Areas_ID '
    ELSE
    IF @Option = 'Sales Person'
        SET @sql = @sql +
            '    LEFT JOIN Lup_AS_Reps o ' +
            '        ON c.Lup_AS_Reps_ID = o.Lup_AS_Reps_ID '
    ELSE
    IF @Option = 'Cashier'
        SET @sql = @sql +
            '    LEFT JOIN AW_Users o ' +
            '        ON h.Lup_AS_Cashiers_ID = o.AW_Users_ID '

    SET @sql = @sql +
        ' WHERE d.AW_User_Companies_ID = ' + CONVERT(varchar(10), @CompanyID) + ' AND ' +
        '      (h.Day_End >= ''' + CONVERT(varchar(30), @DayEndFromDate) + ''' AND ' +
        '       h.Day_End <= ''' + CONVERT(varchar(30), @DayEndToDate) + ''') AND ' +
        '      (h.Sys_AW_Transaction_Types_ID = 3 OR ' +
        '       h.Sys_AW_Transaction_Types_ID = 4)'

    SET @sql = @sql +
--        ' ORDER BY [Filter], [Customer], [TranDate] '
        ' ORDER BY [Filter], [Product], [Code], [TranDate] '

--PRINT @sql

EXEC (@sql)
Go

--14
IF  EXISTS (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = OBJECT_ID(N'[dbo].[sp_AC_Select_CBilling_Temp]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_AC_Select_CBilling_Temp]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-------------------------------------------------------------------------------
--Description   :sp_AC_Select_CBilling_Temp
--Functionality :SELECT Centralised Sales transactions
--Parameters    :
--Date          :2005/10/03
--Authors       :TB
--MODS          :
--2004.5.8 RGL 2013-02-20 : Added distinct to select to remove duplicate
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AC_Select_CBilling_Temp]
    (
        @CompanyID int
    )
AS

SET NOCOUNT ON

-- DECLARE @CompanyID int

-- SET @CompanyID = 1
    
    SELECT
		Distinct
        AS_Transactions_ID,
        Lup_AS_CBilling_Providers_ID,
        cat_code,
        drs_acc,
        trs_ref,
        trs_date,
        qty,
        amount,
        gls_acc,
        narr,
        stock_group,
        drs_apply,
        sys_code,
        updated,
        interfaced,
        drs_age_month,
        stock_code,
        area,
        drs_alpha,
        branch,
        vat_amount,
        cost_amount,
        rep_comm,
        process_type,
        rep_code,
        acct_type,
        disc_amnt,
        std_sell_price,
        drs_trs_code,
        trs_type,
        acc_month,
        acc_year,
        con_acct_no,
        zz_int01,
        cntno,
        line_disc1,
        line_disc2,
        stock_sell1,
        no_of_deals,
        zz_date01,
        zz_date02,
        zz_date03,
        special_instr,
        deals_flag,
        deals_number,
        vat_exempt_member,
        cmonth,
        cyear,
        rep_call_status,
        drsdiv_code,
        crs_acc,
        creation_date,
        creation_time,
        creation_userid,
        creation_proc,
        item_number,
        rep_code2,
        qty2,
        deposit_amount,
        job_number,
        line_disc_amnt,
        carhire_cntno,
        currency,
        exch_rate,
        cyear_month,
        zz_date04,
        zz_date05,
        zz_int02,
        zz_int03,
        zz_int04,
        zz_int05,
        zz_dec01,
        zz_dec02,
        zz_dec03,
        zz_dec04,
        zz_dec05,
        zz_log01,
        zz_log02,
        zz_log03,
        zz_log04,
        zz_log05,
        zz_log06,
        zz_log07,
        zz_char01,
        zz_char02,
        zz_char03,
        zz_char04,
        zz_char05,
        key_glstrs,
        key_history,
        dumped,
        company_name,
        branch_name,
        drs_name,
        crs_name,
        prd_grp_descr,
        stk_descry,
        status_ind,
        customer_ref,
        comments1,
        comments2,
        comments3,
        comments4,
        comments5,
        comments6,
        comments7,
        comments8,
        database_id,
        system_id,
        vat_reg_no,
        email,
        customer_cell_number,
        customer_email_address,
        Date_Captured,
        User_ID,
        AW_User_Companies_ID,
        Slu_AW_Active_ID
    FROM AS_Transactions_CBilling_Temp
    WHERE AW_User_Companies_ID = @CompanyID
    ORDER BY AS_Transactions_ID
Go

--15
--Update blank and NULL fields that cause errors when reporting
Update Lup_AP_Product_Locations
Set Description = 'UNKNOWN-'+Cast(Lup_AP_Product_Locations_ID As Varchar(10))
Where IsNull(Description,'') = ''
Go
Update Lup_AP_Product_Departments
Set Description = 'UNKNOWN-'+Cast(Lup_AP_Product_Departments_ID As Varchar(10))
Where IsNull(Description,'') = ''
Go
Update Lup_AP_Product_Groups
Set Description = 'UNKNOWN-'+Cast(Lup_AP_Product_Groups_ID As Varchar(10))
Where IsNull(Description,'') = ''
Go
Update Lup_AP_Product_Manufacturers
Set Description = 'UNKNOWN-'+Cast(Lup_AP_Product_Manufacturers_ID As Varchar(10))
Where IsNull(Description,'') = ''
Go
Update Lup_AP_Product_Types
Set Description = 'UNKNOWN-'+Cast(Lup_AP_Product_Types_ID As Varchar(10))
Where IsNull(Description,'') = ''
Go
Update AO_Suppliers
Set Supplier_Name = 'UNKNOWN-'+Cast(AO_Suppliers_ID As Varchar(10))
Where IsNull(Supplier_Name,'') = ''
Go
Update AW_Users
Set Surname = 'UNKNOWN-'+Cast(AW_Users_ID As Varchar(10))
Where IsNull(Surname,'') = ''
Go

--16
--Change Isoft ftp details to Alacrity
If Exists
(
  Select 1
  From Lup_AS_CBilling_Providers
  Where Description = 'Isoft'
)
Begin
  If NOT Exists
  (
    Select 1
    From Lup_AS_CBilling_Providers
    Where Description = 'Alacrity'
  )
  Begin 
    Update 
      Lup_AS_CBilling_Providers
    Set 
      Description = 'Alacrity'
      ,Server_Name = 'gatekeeper.alacrity.co.za'
      ,Login_Name = 'ftppos'
      ,Login_Password = '!!pos123'
      ,User_ID = -1
      ,AW_User_Companies_ID = 1
      ,Slu_AW_Active_ID = 1
      ,Folder = NULL
    Where 
      Description = 'Isoft'
  End
  ELSE
  BEGIN
	Update 
      Lup_AS_CBilling_Providers
    Set 
      Slu_AW_Active_ID = 2      
    Where 
      Description = 'Isoft'  
  END
End   
Go

--17 Update Product Levy
Update Ap_products 
Set Levy_AP_Product_ID = 0
Where IsNull(Levy_AP_Product_ID,0) = 0
  
---------------------------------------------------------------------------------
-- Updating Table AA_Release
---------------------------------------------------------------------------------
IF NOT EXISTS
    (
        SELECT AA_Release_ID
        FROM AA_Release
        WHERE Release = '2004.5.8'
    )
    BEGIN
        INSERT AA_Release
            (
                Release,
                Updated_by			  
            )
        VALUES
            (
                '2004.5.8',
                'Upgrade CD'
            )
    END
ELSE
    BEGIN
        UPDATE AA_Release
        SET Updated_by = 'Upgrade CD',
            Date_Updated = getdate()
        WHERE Release = '2004.5.8'
    END
Go    
