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

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

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

---------------------------------------------------------------------------------
--Description   :sp_AO_Select_Orders
--Purpose       :Fetches a Supplier Order 
--Functionality :
--Parameters    :
--Date          :2002/01/15
--Authors       :TB 
--Version       :1.0
--MODS          :06/11/03 - Added JOINS to display user-friendly descriptions
--V6.0	RL	2014-09-18 :Added Fields in selects for pended orders
---------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_AO_Select_Orders]
    (
    	@OrderStatus tinyint = 0,
    	@SupplierID int = 0,
    	@CompanyID int			
    )

-- @OrderStatus:    0=All, 1=Pending, 2=Outstanding, 3=Completed

AS

SET NOCOUNT ON

    BEGIN 
    --> 0 = All Orders
    	IF @OrderStatus = 0	
    		SELECT
                O.AO_Orders_ID AS [ID],
                O.Order_Number AS [Order No],
				O.Order_Date AS [Order Date],
				O.Date_Ordered AS [Date Ordered],
                B.AW_Remote_Access_ID AS [Branch_Codes_ID],
                B.NodeName AS [Ordered By],
                B.NodeName AS [Branch_Codes],                    
                O.AO_Suppliers_ID AS [SupplierID],
                S.Supplier_Name AS [Supplier],
                O.Net_Amount AS [Net Amount],
                O.Tax_Total AS [Tax],
                O.Tax_Total AS [Tax Total],
                O.Order_Total AS [Total Amount],
                O.Order_Total AS [Order Total],
                (SELECT Sum(ISNULL(Order_Qty,0)) FROM AO_Order_Details 
                 WHERE AO_Orders_ID = O.AO_Orders_ID GROUP BY AO_Orders_ID ) AS [Order Qty],
                M.[Description] AS [Order Method],
                O.Send_To_Address AS [Deliver To],
                O.Delivery_Addr1 AS [Address],
                O.Delivery_Addr2 AS [Suburb],
                O.Delivery_Addr3 AS [City],
                O.Postal_Code AS [Post Code],
                O.Invoice_Total AS [Invoice Total],
                O.Admin_Fee  AS [Admin Fee],
                O.Trade_Discount_Total AS [Trade Disc],
                O.Trade_Discount_Total AS [Trade Discount],                
                O.Rebate_Discount_Total AS [Rebate Disc],
                O.Rebate_Discount_Total AS [Rebate Discount],
                O.Other_Discount_Total AS [Other Disc],
                O.Other_Discount_Total AS [Other Discount],
                O.Version AS [Version],
                O.Supplier_Rep AS [Supplier Rep],
                O.Supplier_Type AS [Supplier Type],
                O.Confirmation AS [Confirmed],
                O.Confirmation_Date AS [Confirm Date],
                O.Our_Ref AS [Our Ref],
                O.Supplier_Reference AS [Supplier Ref],
                O.Invoice_Type AS [Invoice Type],
                O.Date_Captured AS [Date Captured],
                O.[User_ID] AS [User ID],
                U.UserName AS [User],
                O.AW_User_Companies_ID AS [Company],
                A.[Description] AS [Active]
    		FROM AO_Orders O
                LEFT JOIN AO_Suppliers S
                    ON O.AO_Suppliers_ID = S.AO_Suppliers_ID
                LEFT JOIN AW_Remote_Access B
                    ON O.AW_Remote_Access_ID = B.AW_Remote_Access_ID
                LEFT JOIN Sys_AO_Delivery_Methods M
                    ON O.Sys_AO_Delivery_Methods_ID = M.Sys_AO_Delivery_Methods_ID
                LEFT JOIN AW_Users U
                    ON O.[User_ID] = U.AW_Users_ID
                LEFT JOIN Slu_AW_Active A
                    ON O.Slu_AW_Active_ID = A.Slu_AW_Active_ID
    		WHERE O.AW_User_Companies_ID = @CompanyID
    
    --> 1 = Pending Orders
    	ELSE 
    	IF @OrderStatus = 1 
    		BEGIN
    		IF @SupplierID = 0
        		SELECT
                      O.AO_Orders_ID AS [ID],
                O.Order_Number AS [Order No],
				O.Order_Date AS [Order Date],
				O.Date_Ordered AS [Date Ordered],
                B.AW_Remote_Access_ID AS [Branch_Codes_ID],
                B.NodeName AS [Ordered By],
                B.NodeName AS [Branch_Codes],                    
                O.AO_Suppliers_ID AS [SupplierID],
                S.Supplier_Name AS [Supplier],
                O.Net_Amount AS [Net Amount],
                O.Tax_Total AS [Tax],
                O.Tax_Total AS [Tax Total],
                O.Order_Total AS [Total Amount],
                O.Order_Total AS [Order Total],
                (SELECT Sum(ISNULL(Order_Qty,0)) FROM AO_Order_Details 
                 WHERE AO_Orders_ID = O.AO_Orders_ID GROUP BY AO_Orders_ID ) AS [Order Qty],
                M.[Description] AS [Order Method],
                O.Send_To_Address AS [Deliver To],
                O.Delivery_Addr1 AS [Address],
                O.Delivery_Addr2 AS [Suburb],
                O.Delivery_Addr3 AS [City],
                O.Postal_Code AS [Post Code],
                O.Invoice_Total AS [Invoice Total],
                O.Admin_Fee  AS [Admin Fee],
                O.Trade_Discount_Total AS [Trade Disc],
                O.Trade_Discount_Total AS [Trade Discount],                
                O.Rebate_Discount_Total AS [Rebate Disc],
                O.Rebate_Discount_Total AS [Rebate Discount],
                O.Other_Discount_Total AS [Other Disc],
                O.Other_Discount_Total AS [Other Discount],
                O.Version AS [Version],
                O.Supplier_Rep AS [Supplier Rep],
                O.Supplier_Type AS [Supplier Type],
                O.Confirmation AS [Confirmed],
                O.Confirmation_Date AS [Confirm Date],
                O.Our_Ref AS [Our Ref],
                O.Supplier_Reference AS [Supplier Ref],
                O.Invoice_Type AS [Invoice Type],
                O.Date_Captured AS [Date Captured],
                O.[User_ID] AS [User ID],
                U.UserName AS [User],
                O.AW_User_Companies_ID AS [Company],
                A.[Description] AS [Active]
        		FROM AO_Orders O
                    LEFT JOIN AO_Suppliers S
                        ON O.AO_Suppliers_ID = S.AO_Suppliers_ID
                    LEFT JOIN AW_Remote_Access B
                        ON O.AW_Remote_Access_ID = B.AW_Remote_Access_ID
                    LEFT JOIN Sys_AO_Delivery_Methods M
                        ON O.Sys_AO_Delivery_Methods_ID = M.Sys_AO_Delivery_Methods_ID
                    LEFT JOIN AW_Users U
                        ON O.[User_ID] = U.AW_Users_ID
                    LEFT JOIN Slu_AW_Active A
                        ON O.Slu_AW_Active_ID = A.Slu_AW_Active_ID
    			WHERE O.Date_Ordered IS NULL AND
                      O.Complete = 0 AND 
                      O.AW_User_Companies_ID = @CompanyID
    
    		ELSE 
    		IF @SupplierID <> 0
        		SELECT
                   O.AO_Orders_ID AS [ID],
                O.Order_Number AS [Order No],
				O.Order_Date AS [Order Date],
				O.Date_Ordered AS [Date Ordered],
                B.AW_Remote_Access_ID AS [Branch_Codes_ID],
                B.NodeName AS [Ordered By],
                B.NodeName AS [Branch_Codes],                    
                O.AO_Suppliers_ID AS [SupplierID],
                S.Supplier_Name AS [Supplier],
                O.Net_Amount AS [Net Amount],
                O.Tax_Total AS [Tax],
                O.Tax_Total AS [Tax Total],
                O.Order_Total AS [Total Amount],
                O.Order_Total AS [Order Total],
                (SELECT Sum(ISNULL(Order_Qty,0)) FROM AO_Order_Details 
                 WHERE AO_Orders_ID = O.AO_Orders_ID GROUP BY AO_Orders_ID ) AS [Order Qty],
                M.[Description] AS [Order Method],
                O.Send_To_Address AS [Deliver To],
                O.Delivery_Addr1 AS [Address],
                O.Delivery_Addr2 AS [Suburb],
                O.Delivery_Addr3 AS [City],
                O.Postal_Code AS [Post Code],
                O.Invoice_Total AS [Invoice Total],
                O.Admin_Fee  AS [Admin Fee],
                O.Trade_Discount_Total AS [Trade Disc],
                O.Trade_Discount_Total AS [Trade Discount],                
                O.Rebate_Discount_Total AS [Rebate Disc],
                O.Rebate_Discount_Total AS [Rebate Discount],
                O.Other_Discount_Total AS [Other Disc],
                O.Other_Discount_Total AS [Other Discount],
                O.Version AS [Version],
                O.Supplier_Rep AS [Supplier Rep],
                O.Supplier_Type AS [Supplier Type],
                O.Confirmation AS [Confirmed],
                O.Confirmation_Date AS [Confirm Date],
                O.Our_Ref AS [Our Ref],
                O.Supplier_Reference AS [Supplier Ref],
                O.Invoice_Type AS [Invoice Type],
                O.Date_Captured AS [Date Captured],
                O.[User_ID] AS [User ID],
                U.UserName AS [User],
                O.AW_User_Companies_ID AS [Company],
                A.[Description] AS [Active]
        		FROM AO_Orders O
                    LEFT JOIN AO_Suppliers S
                        ON O.AO_Suppliers_ID = S.AO_Suppliers_ID
                    LEFT JOIN AW_Remote_Access B
                        ON O.AW_Remote_Access_ID = B.AW_Remote_Access_ID
                    LEFT JOIN Sys_AO_Delivery_Methods M
                        ON O.Sys_AO_Delivery_Methods_ID = M.Sys_AO_Delivery_Methods_ID
                    LEFT JOIN AW_Users U
                        ON O.[User_ID] = U.AW_Users_ID
                    LEFT JOIN Slu_AW_Active A
                        ON O.Slu_AW_Active_ID = A.Slu_AW_Active_ID
    			WHERE O.Date_Ordered IS NULL AND
                      O.Complete = 0 AND 
                      O.AO_Suppliers_ID = @SupplierID AND
                      O.AW_User_Companies_ID = @CompanyID
    
    		END
    --> 2 = Outstanding Orders
    	ELSE 
    	IF @OrderStatus = 2 
    		BEGIN
    		IF @SupplierID = 0
        		SELECT
                    O.AO_Orders_ID AS [ID],
                O.Order_Number AS [Order No],
				O.Order_Date AS [Order Date],
				O.Date_Ordered AS [Date Ordered],
                B.AW_Remote_Access_ID AS [Branch_Codes_ID],
                B.NodeName AS [Ordered By],
                B.NodeName AS [Branch_Codes],                    
                O.AO_Suppliers_ID AS [SupplierID],
                S.Supplier_Name AS [Supplier],
                O.Net_Amount AS [Net Amount],
                O.Tax_Total AS [Tax],
                O.Tax_Total AS [Tax Total],
                O.Order_Total AS [Total Amount],
                O.Order_Total AS [Order Total],
                (SELECT Sum(ISNULL(Order_Qty,0)) FROM AO_Order_Details 
                 WHERE AO_Orders_ID = O.AO_Orders_ID GROUP BY AO_Orders_ID ) AS [Order Qty],
                M.[Description] AS [Order Method],
                O.Send_To_Address AS [Deliver To],
                O.Delivery_Addr1 AS [Address],
                O.Delivery_Addr2 AS [Suburb],
                O.Delivery_Addr3 AS [City],
                O.Postal_Code AS [Post Code],
                O.Invoice_Total AS [Invoice Total],
                O.Admin_Fee  AS [Admin Fee],
                O.Trade_Discount_Total AS [Trade Disc],
                O.Trade_Discount_Total AS [Trade Discount],                
                O.Rebate_Discount_Total AS [Rebate Disc],
                O.Rebate_Discount_Total AS [Rebate Discount],
                O.Other_Discount_Total AS [Other Disc],
                O.Other_Discount_Total AS [Other Discount],
                O.Version AS [Version],
                O.Supplier_Rep AS [Supplier Rep],
                O.Supplier_Type AS [Supplier Type],
                O.Confirmation AS [Confirmed],
                O.Confirmation_Date AS [Confirm Date],
                O.Our_Ref AS [Our Ref],
                O.Supplier_Reference AS [Supplier Ref],
                O.Invoice_Type AS [Invoice Type],
                O.Date_Captured AS [Date Captured],
                O.[User_ID] AS [User ID],
                U.UserName AS [User],
                O.AW_User_Companies_ID AS [Company],
                A.[Description] AS [Active]
        		FROM AO_Orders O
                    LEFT JOIN AO_Suppliers S
                        ON O.AO_Suppliers_ID = S.AO_Suppliers_ID
                    LEFT JOIN AW_Remote_Access B
                        ON O.AW_Remote_Access_ID = B.AW_Remote_Access_ID
                    LEFT JOIN Sys_AO_Delivery_Methods M
                        ON O.Sys_AO_Delivery_Methods_ID = M.Sys_AO_Delivery_Methods_ID
                    LEFT JOIN AW_Users U
                        ON O.[User_ID] = U.AW_Users_ID
                    LEFT JOIN Slu_AW_Active A
                        ON O.Slu_AW_Active_ID = A.Slu_AW_Active_ID
    			WHERE O.Date_Ordered IS NOT NULL AND
                      O.Complete = 0 AND
                      O.AW_User_Companies_ID = @CompanyID
    		ELSE
    		IF @SupplierID <> 0
        		SELECT
                     O.AO_Orders_ID AS [ID],
                O.Order_Number AS [Order No],
				O.Order_Date AS [Order Date],
				O.Date_Ordered AS [Date Ordered],
                B.AW_Remote_Access_ID AS [Branch_Codes_ID],
                B.NodeName AS [Ordered By],
                B.NodeName AS [Branch_Codes],                    
                O.AO_Suppliers_ID AS [SupplierID],
                S.Supplier_Name AS [Supplier],
                O.Net_Amount AS [Net Amount],
                O.Tax_Total AS [Tax],
                O.Tax_Total AS [Tax Total],
                O.Order_Total AS [Total Amount],
                O.Order_Total AS [Order Total],
                (SELECT Sum(ISNULL(Order_Qty,0)) FROM AO_Order_Details 
                 WHERE AO_Orders_ID = O.AO_Orders_ID GROUP BY AO_Orders_ID ) AS [Order Qty],
                M.[Description] AS [Order Method],
                O.Send_To_Address AS [Deliver To],
                O.Delivery_Addr1 AS [Address],
                O.Delivery_Addr2 AS [Suburb],
                O.Delivery_Addr3 AS [City],
                O.Postal_Code AS [Post Code],
                O.Invoice_Total AS [Invoice Total],
                O.Admin_Fee  AS [Admin Fee],
                O.Trade_Discount_Total AS [Trade Disc],
                O.Trade_Discount_Total AS [Trade Discount],                
                O.Rebate_Discount_Total AS [Rebate Disc],
                O.Rebate_Discount_Total AS [Rebate Discount],
                O.Other_Discount_Total AS [Other Disc],
                O.Other_Discount_Total AS [Other Discount],
                O.Version AS [Version],
                O.Supplier_Rep AS [Supplier Rep],
                O.Supplier_Type AS [Supplier Type],
                O.Confirmation AS [Confirmed],
                O.Confirmation_Date AS [Confirm Date],
                O.Our_Ref AS [Our Ref],
                O.Supplier_Reference AS [Supplier Ref],
                O.Invoice_Type AS [Invoice Type],
                O.Date_Captured AS [Date Captured],
                O.[User_ID] AS [User ID],
                U.UserName AS [User],
                O.AW_User_Companies_ID AS [Company],
                A.[Description] AS [Active]
        		FROM AO_Orders O
                    LEFT JOIN AO_Suppliers S
                        ON O.AO_Suppliers_ID = S.AO_Suppliers_ID
                    LEFT JOIN AW_Remote_Access B
                        ON O.AW_Remote_Access_ID = B.AW_Remote_Access_ID
                    LEFT JOIN Sys_AO_Delivery_Methods M
                        ON O.Sys_AO_Delivery_Methods_ID = M.Sys_AO_Delivery_Methods_ID
                    LEFT JOIN AW_Users U
                        ON O.[User_ID] = U.AW_Users_ID
                    LEFT JOIN Slu_AW_Active A
                        ON O.Slu_AW_Active_ID = A.Slu_AW_Active_ID
    			WHERE O.Date_Ordered IS NOT NULL AND
                      O.Complete = 0 AND
                      O.AO_Suppliers_ID = @SupplierID AND
                      O.AW_User_Companies_ID = @CompanyID
    		END
    
    --> 3 = Completed Orders
    	ELSE 
    	IF @OrderStatus = 3
    		BEGIN
    		IF @SupplierID = 0
        		SELECT
                   O.AO_Orders_ID AS [ID],
                O.Order_Number AS [Order No],
				O.Order_Date AS [Order Date],
				O.Date_Ordered AS [Date Ordered],
                B.AW_Remote_Access_ID AS [Branch_Codes_ID],
                B.NodeName AS [Ordered By],
                B.NodeName AS [Branch_Codes],                    
                O.AO_Suppliers_ID AS [SupplierID],
                S.Supplier_Name AS [Supplier],
                O.Net_Amount AS [Net Amount],
                O.Tax_Total AS [Tax],
                O.Tax_Total AS [Tax Total],
                O.Order_Total AS [Total Amount],
                O.Order_Total AS [Order Total],
                (SELECT Sum(ISNULL(Order_Qty,0)) FROM AO_Order_Details 
                 WHERE AO_Orders_ID = O.AO_Orders_ID GROUP BY AO_Orders_ID ) AS [Order Qty],
                M.[Description] AS [Order Method],
                O.Send_To_Address AS [Deliver To],
                O.Delivery_Addr1 AS [Address],
                O.Delivery_Addr2 AS [Suburb],
                O.Delivery_Addr3 AS [City],
                O.Postal_Code AS [Post Code],
                O.Invoice_Total AS [Invoice Total],
                O.Admin_Fee  AS [Admin Fee],
                O.Trade_Discount_Total AS [Trade Disc],
                O.Trade_Discount_Total AS [Trade Discount],                
                O.Rebate_Discount_Total AS [Rebate Disc],
                O.Rebate_Discount_Total AS [Rebate Discount],
                O.Other_Discount_Total AS [Other Disc],
                O.Other_Discount_Total AS [Other Discount],
                O.Version AS [Version],
                O.Supplier_Rep AS [Supplier Rep],
                O.Supplier_Type AS [Supplier Type],
                O.Confirmation AS [Confirmed],
                O.Confirmation_Date AS [Confirm Date],
                O.Our_Ref AS [Our Ref],
                O.Supplier_Reference AS [Supplier Ref],
                O.Invoice_Type AS [Invoice Type],
                O.Date_Captured AS [Date Captured],
                O.[User_ID] AS [User ID],
                U.UserName AS [User],
                O.AW_User_Companies_ID AS [Company],
                A.[Description] AS [Active]
        		FROM AO_Orders O
                    LEFT JOIN AO_Suppliers S
                        ON O.AO_Suppliers_ID = S.AO_Suppliers_ID
                    LEFT JOIN AW_Remote_Access B
                        ON O.AW_Remote_Access_ID = B.AW_Remote_Access_ID
                    LEFT JOIN Sys_AO_Delivery_Methods M
                        ON O.Sys_AO_Delivery_Methods_ID = M.Sys_AO_Delivery_Methods_ID
                    LEFT JOIN AW_Users U
                        ON O.[User_ID] = U.AW_Users_ID
                    LEFT JOIN Slu_AW_Active A
                        ON O.Slu_AW_Active_ID = A.Slu_AW_Active_ID
    			WHERE O.Complete = 1 AND
                      O.AW_User_Companies_ID = @CompanyID
    		ELSE
    		IF @SupplierID <> 0
        		SELECT
                O.AO_Orders_ID AS [ID],
                O.Order_Number AS [Order No],
				O.Order_Date AS [Order Date],
				O.Date_Ordered AS [Date Ordered],
                B.AW_Remote_Access_ID AS [Branch_Codes_ID],
                B.NodeName AS [Ordered By],
                B.NodeName AS [Branch_Codes],                    
                O.AO_Suppliers_ID AS [SupplierID],
                S.Supplier_Name AS [Supplier],
                O.Net_Amount AS [Net Amount],
                O.Tax_Total AS [Tax],
                O.Tax_Total AS [Tax Total],
                O.Order_Total AS [Total Amount],
                O.Order_Total AS [Order Total],
                (SELECT Sum(ISNULL(Order_Qty,0)) FROM AO_Order_Details 
                 WHERE AO_Orders_ID = O.AO_Orders_ID GROUP BY AO_Orders_ID ) AS [Order Qty],
                M.[Description] AS [Order Method],
                O.Send_To_Address AS [Deliver To],
                O.Delivery_Addr1 AS [Address],
                O.Delivery_Addr2 AS [Suburb],
                O.Delivery_Addr3 AS [City],
                O.Postal_Code AS [Post Code],
                O.Invoice_Total AS [Invoice Total],
                O.Admin_Fee  AS [Admin Fee],
                O.Trade_Discount_Total AS [Trade Disc],
                O.Trade_Discount_Total AS [Trade Discount],                
                O.Rebate_Discount_Total AS [Rebate Disc],
                O.Rebate_Discount_Total AS [Rebate Discount],
                O.Other_Discount_Total AS [Other Disc],
                O.Other_Discount_Total AS [Other Discount],
                O.Version AS [Version],
                O.Supplier_Rep AS [Supplier Rep],
                O.Supplier_Type AS [Supplier Type],
                O.Confirmation AS [Confirmed],
                O.Confirmation_Date AS [Confirm Date],
                O.Our_Ref AS [Our Ref],
                O.Supplier_Reference AS [Supplier Ref],
                O.Invoice_Type AS [Invoice Type],
                O.Date_Captured AS [Date Captured],
                O.[User_ID] AS [User ID],
                U.UserName AS [User],
                O.AW_User_Companies_ID AS [Company],
                A.[Description] AS [Active]
        		FROM AO_Orders O
                    LEFT JOIN AO_Suppliers S
                        ON O.AO_Suppliers_ID = S.AO_Suppliers_ID
                    LEFT JOIN AW_Remote_Access B
                        ON O.AW_Remote_Access_ID = B.AW_Remote_Access_ID
                    LEFT JOIN Sys_AO_Delivery_Methods M
                        ON O.Sys_AO_Delivery_Methods_ID = M.Sys_AO_Delivery_Methods_ID
                    LEFT JOIN AW_Users U
                        ON O.[User_ID] = U.AW_Users_ID
                    LEFT JOIN Slu_AW_Active A
                        ON O.Slu_AW_Active_ID = A.Slu_AW_Active_ID
    			WHERE O.Complete = 1 AND
                      O.AO_Suppliers_ID = @SupplierID AND
                      O.AW_User_Companies_ID = @CompanyID
    		END
    	END

SET NOCOUNT OFF
GO


/****** Object:  StoredProcedure [dbo].[sp_AS_POS_RPT_Invoice_Print]    Script Date: 2020/02/27 10:04:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
--RL	V6.0	:2014-01-28 - Use product print description instead of description
--RL	V6.0	:2014-04-22 - Return Customer ID
--RL	V6.0	:2014-07-19 - Return TDHeader & TDDetail
--RL	V6.1	:2015-03-13 - Fixed - If Firstname was blank it still displayed the /
--RL	V6.3	:2017-03-21 - Reworked Reg_No And Millage
--RL	V6.4	:2018-04-12 - Cell_No and EMail for document has been moved to AS_Transactions_Address_Detail table
--WS/NZ	V6.5	:2020-02-27 - Added @Print_Bin_POS system option to print the bin on the document product detail comment line
-------------------------------------------------------------------------------
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)
            
	DECLARE @Customer_Name varchar(100),
 			@Inv_Address1 varchar(40),
 			@Inv_Address2 varchar(40),
 			@Inv_City varchar(30),
			@Inv_Code varchar(10),
			@Tax_Number varchar(16),
			@AW_YesNo_Invoice tinyint,
			
			@Delivery_Address varchar(40),
			@Delivery_Suburb varchar(40),
			@Delivery_City varchar(30),
			@Delivery_Post_Code varchar(10),
			@AW_YesNo_Delivery_Address tinyint,
			@AC_Customers_ID int,
			@IPay_Link varchar(100),
			@EMail varchar(100),
			@Cell_No varchar(100),
			@Print_Bin_POS bit
            
    --> 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'

	--> Get the Print Bin POS on Document setting
	SELECT TOP 1 @Print_Bin_POS = REPLACE(ISNULL(Control_Value, 0), '''', '')
    FROM Sys_AW_Options
    WHERE Control_Index = 0 AND
          Control_Type = 'CheckBox' AND
          Control_Name = 'chkPrintBinPOS'

    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 @AC_Customers_ID = 0
    SET @AW_YesNo_Invoice = 0
    SET @AW_YesNo_Delivery_Address = 0
    
    SELECT @AC_Customers_ID = AC_Customers_ID
    FROM AS_Transactions_Address a
	INNER JOIN AS_Transactions_Address_Detail d ON
		a.AS_Transactions_Address_Detail_ID = d.AS_Transactions_Address_Detail_ID
	WHERE a.AS_Transactions_ID = @TransactionID

    IF @AC_Customers_ID <> 0
	BEGIN
		SELECT	@Customer_Name = REPLACE(ISNULL(Customer_Name, ''), '''', ''),
				@Inv_Address1 = REPLACE(ISNULL(Inv_Address1, ''), '''', ''),
				@Inv_Address2 = REPLACE(ISNULL(Inv_Address2, ''), '''', ''), 
				@Inv_City = REPLACE(ISNULL(Inv_City, ''), '''', ''),
				@Inv_Code = REPLACE(ISNULL(Inv_Code, ''), '''', ''),
				@Tax_Number = REPLACE(ISNULL(Tax_Number, ''), '''', ''),
				@AW_YesNo_Invoice = AW_YesNo_Invoice,
				@Delivery_Address = REPLACE(ISNULL(Delivery_Address, ''), '''', ''),
				@Delivery_Suburb = REPLACE(ISNULL(Delivery_Suburb, ''), '''', ''),
				@Delivery_City = REPLACE(ISNULL(Delivery_City, ''), '''', ''),
				@Delivery_Post_Code = REPLACE(ISNULL(Delivery_Post_Code, ''), '''', ''),
				@AW_YesNo_Delivery_Address = AW_YesNo_Delivery_Address,
				--V6.4 Added to Inv/Del Address
				@Cell_No = REPLACE(ISNULL(Cell_No, ''), '''', ''),
				@EMail = REPLACE(ISNULL(EMail, ''), '''', '')
		FROM AS_Transactions_Address a
		INNER JOIN AS_Transactions_Address_Detail d ON 
			a.AS_Transactions_Address_Detail_ID = d.AS_Transactions_Address_Detail_ID
		WHERE a.AS_Transactions_ID = @TransactionID
	END    
	
	--V6.4 Added i-Pay link to show on Invoices
	IF @TranTypeID = 3
	BEGIN
		SELECT TOP 1 @IPay_Link = ISNULL(IPay_URL,'')
		FROM AS_Transaction_Payments_IPay
		WHERE AS_Transactions_ID = @TransactionID
		AND Ipay_Action IN ('PAY SUC','PAYDONE')
	END

	SELECT @IPay_Link = ISNULL(@IPay_Link,'')
	
    --------------------------------------------------------------------

	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 '

	IF @AW_YesNo_Invoice = 1
	BEGIN
      SET @sql = @sql + '''' + @Customer_Name + '''' + ' AS [CustomerName], '	  
	END
	ELSE
	BEGIN		
		--RL V6.1 Fixed - If Firstname was blank it still displayed the /
		SET @sql = @sql + ' CASE WHEN @WhichName = 1 '
		SET @sql = @sql + ' THEN '
		SET @sql = @sql + ' 	CASE WHEN ISNULL(c.Initials,'''') = '''' '
		SET @sql = @sql + ' 	THEN '
		SET @sql = @sql + ' 		CASE WHEN ISNULL(c.Firstnames,'''') = '''' '
		SET @sql = @sql + ' 		THEN LTRIM(RTRIM(c.Surname)) '
		SET @sql = @sql + ' 			ELSE LTRIM(RTRIM(c.Surname)) + '' / '' + LTRIM(RTRIM(c.Firstnames)) '
		SET @sql = @sql + ' 		END '
		SET @sql = @sql + ' 	ELSE '
		SET @sql = @sql + ' 		CASE WHEN ISNULL(c.Firstnames,'''') = '''' '
		SET @sql = @sql + ' 		THEN LTRIM(RTRIM(c.Surname)) + '', '' + LTRIM(RTRIM(c.Initials)) '
		SET @sql = @sql + ' 			ELSE LTRIM(RTRIM(c.Surname)) + '', '' + LTRIM(RTRIM(c.Initials)) + '' / '' + LTRIM(RTRIM(c.Firstnames)) '
		SET @sql = @sql + ' 		END '
		SET @sql = @sql + ' 	END '
		SET @sql = @sql + ' ELSE '
		SET @sql = @sql + ' 	CASE WHEN ISNULL(c.Initials,'''') = '''' '
		SET @sql = @sql + ' 		THEN LTRIM(RTRIM(c.Surname)) '
		SET @sql = @sql + ' 	ELSE LTRIM(RTRIM(c.Surname)) + '', '' + LTRIM(RTRIM(c.Initials)) '
		SET @sql = @sql + ' 	END '
		SET @sql = @sql + ' END AS [CustomerName], '
	END
	           	
    --> NB NB: Customer ADD in POS saves with STREET address copied to POSTAL Address
    --> POSTAL Address
	IF @AW_YesNo_Invoice = 1
	BEGIN
		SET @sql = @sql + '''' + @Inv_Address1 + '''' + ' AS [PostBox], '
		SET @sql = @sql + '''' + @Inv_Address1 + '''' + ' AS [PostBox], '      
		SET @sql = @sql + '''' + @Inv_Address2 + '''' + ' AS [PostOffice], '
		SET @sql = @sql + '''' + @Inv_City + '''' + ' AS [PostCity], '
		SET @sql = @sql + '''' + @Inv_Code + '''' + ' AS [PostCode], '
	END
	ELSE
	BEGIN
		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], '
	END

	IF @AW_YesNo_Invoice = 1
	BEGIN
		SET @sql = @sql + '''' + @Inv_Address1 + '''' + ' AS [StreetAddress], '      
		SET @sql = @sql + '''' + @Inv_Address2 + '''' + ' AS [StreetSuburb], '
		SET @sql = @sql + '''' + @Inv_City + '''' + ' AS [StreetCity], '
		SET @sql = @sql + '''' + @Inv_Code + '''' + ' AS [StreetPostCode], '
	END
	ELSE
	BEGIN
		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], '
	END

	IF @AW_YesNo_Delivery_Address = 1
	BEGIN
		SET @sql = @sql + '''' + @Delivery_Address + '''' + ' AS [DeliverAddress], '      
		SET @sql = @sql + '''' + @Delivery_Suburb + '''' + ' AS [DeliverSuburb], '
		SET @sql = @sql + '''' + @Delivery_City + '''' + ' AS [DeliverCity], '
		SET @sql = @sql + '''' + @Delivery_Post_Code + '''' + ' AS [DeliverPostCode], '
	END
	ELSE
	BEGIN
		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], '
	END

	SET @sql = @sql + ' CONVERT(VARCHAR(12), c.AC_Customers_ID) AS [CustomerID], '
	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 + ' comp.Delivery_Addr1 AS [Delivery_Addr1], '
    SET @sql = @sql + ' comp.Delivery_Addr2 AS [Delivery_Addr2], '
    SET @sql = @sql + ' comp.Delivery_Addr3 AS [Delivery_Addr3], '            
    SET @sql = @sql + ' rep.[Description] AS [RepName], '
    SET @sql = @sql + ' t.AS_Transactions_ID AS [TransactionID], '
	SET @sql = @sql + ' t.Transaction_Number + ISNULL(tranNumbers.Number_Suffix, '''') AS [TransactionNo], '
	--SET @sql = @sql + ' t.Transaction_Number + tranNumbers.Number_Suffix 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], '
    --RL 2014-01-28 Vs 6.0 Use print discription if there is a value instead of Description
    SET @sql = @sql + ' CASE WHEN LEN(ISNULL(Print_Description,'''')) > 0 '
    SET @sql = @sql + ' THEN p.Print_Description '
    SET @sql = @sql + ' ELSE p.Description '
    SET @sql = @sql + ' END AS [ProductDescription], '
    
    --V6.3 RL 2017/04/21 Added back order qty to display on Sales Order document
    SET @sql = @sql + ' (td.Quantity + td.Back_Order) AS [DetailQty], '    
    
    SET @sql = @sql + ' td.Unit_Price AS [DetailUnitPrice], '

    SET @sql = @sql + ' CASE WHEN (td.Unit_Price * (td.Quantity + td.Back_Order)) <> 0 '
    SET @sql = @sql + ' THEN CONVERT(money,ABS(ROUND((((td.Discount_Amount) / (td.Unit_Price * (td.Quantity + td.Back_Order))) * 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.Back_Order)) + 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], '

  	IF @AW_YesNo_Invoice = 1
	BEGIN
		SET @sql = @sql + '''' + @Cell_No + '''' + ' AS [TelNos], '      
	END
	ELSE
	BEGIN
		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], '
	END
	--> TAX Number
    --SET @sql = @sql + ' ISNULL(RTRIM(LTRIM(c.Tax_Number)), '''') AS [VATNo], '
	IF @AW_YesNo_Invoice = 1
	BEGIN
		SET @sql = @sql + '''' + @Tax_Number + '''' + ' AS [VATNo], '      
	END
	ELSE
	BEGIN
		SET @sql = @sql + ' ISNULL(RTRIM(LTRIM(c.Tax_Number)), '''') AS [VATNo], '
	END

    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], '
    
  	IF @AW_YesNo_Invoice = 1
	BEGIN
		SET @sql = @sql + '''' + @EMail + '''' + ' AS [Emails], '      
	END
	ELSE
	BEGIN
		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], '
	END
	    
    SET @sql = @sql + ' td.TDHeader AS [TDHeader], '

	IF @Print_Bin_POS = 1
	BEGIN	
	    SET @sql = @sql + ' ISNULL(td.TDDetail, '''') + '' Bin :'' + ISNULL(b.Description, '''')  AS [TDDetail], '
	END
	ELSE
	BEGIN
		SET @sql = @sql + ' td.TDDetail AS [TDDetail], '
	END

	SET @sql = @sql + ' ISNULL(ca.Reg_No, '''') AS [Reg_No], '
	SET @sql = @sql + ' ISNULL(ca.Mileage, '''') AS [Mileage], '	
	--V6.4
	SET @sql = @sql + '''' + @IPay_Link + '''' + ' AS [IPay_Link] '

--	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 AP_Products_Bins pb '
	SET @sql = @sql + ' ON pb.AP_Products_ID = p.AP_Products_ID '
	SET @sql = @sql + ' LEFT JOIN Lup_AP_Product_Bins b '
	SET @sql = @sql + ' ON pb.Lup_AP_Product_Bins_ID = b.Lup_AP_Product_Bins_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 + ' LEFT JOIN AC_Assets ca '
    SET @sql = @sql + ' ON t.AS_Transactions_ID = ca.AS_Transactions_ID '
	SET @sql = @sql + ' LEFT JOIN Sys_AW_Transaction_Numbers tranNumbers '

    SET @sql = @sql + ' ON t.Sys_AW_Transaction_Types_ID = tranNumbers.Sys_AW_Transaction_Types_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

