------------------------------------------------------------------------------
-- 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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AS_POS_RPT_Payment_Print]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_AS_POS_RPT_Payment_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

/****** Object:  StoredProcedure [dbo].[sp_RPT_AS_Customer_Sales_Filter]    Script Date: 02/06/2021 08:03:54 ******/
-------------------------------------------------------------------------------
--Description   :sp_RPT_AS_Customer_Sales_Filter
--Purpose       :Simplified Customer Sales from/to DayEnd date
--Functionality :
--Date          :2009/09/02
--Authors       :TB
--MODS:         :
-------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[sp_RPT_AS_Customer_Sales_Filter]
    (
        @FilterP tinyint,
        @FilterC tinyint,
        @DayEndFromDate datetime,
        @DayEndToDate datetime,
        @ChkAllCustomers int,
        @Level tinyint,
        @CustomerID int,
        @CompanyID int
    )

AS

SET NOCOUNT ON

    DECLARE @sql varchar(8000)


-- SET @FilterP = 2
-- SET @FilterC = 1
-- SET @DayEndFromDate = '2009/06/01 16:47:00'
-- SET @DayEndToDate = '2009/06/10 17:01:00'
-- SET @ChkAllCustomers = 1
-- SET @CustomerID = 0
-- SET @CompanyID = 1


    SET @sql = ' SELECT '

    IF @FilterP = 0
        SET @sql = @sql +
            ' o.Lup_AP_Product_Types_ID AS [PHeaderID], ' +
            ' o.[Description] AS [PFilter], '
    ELSE
    IF @FilterP = 1
        SET @sql = @sql +
            ' o.Lup_AP_Product_Groups_ID AS [PHeaderID], ' +
            ' o.[Description] AS [PFilter], '
    ELSE
    IF @FilterP = 2
        SET @sql = @sql +
            ' o.Lup_AP_Product_Departments_ID AS [PHeaderID], ' +
            ' o.[Description] AS [PFilter], '
    ELSE
    IF @FilterP = 3
        SET @sql = @sql +
            ' o.AO_Suppliers_ID AS [PHeaderID], ' +
            ' o.[Supplier_Name] AS [PFilter], '
    ELSE
    IF @FilterP = 4
        SET @sql = @sql +
            ' o.Lup_AP_Product_Manufacturers_ID AS [PHeaderID], ' +
            ' o.[Description] AS [PFilter], '
    ELSE
    IF @FilterP = 5
        SET @sql = @sql +
            ' o.Lup_AP_Product_Locations_ID AS [PHeaderID], ' +
            ' o.[Description] AS [PFilter], '
    ELSE
    IF @FilterP = 6
        SET @sql = @sql +
            ' o.Lup_AC_Sales_Areas_ID AS [PHeaderID], ' +
            ' o.[Description] AS [PFilter], '
    ELSE
    IF @FilterP = 7
        SET @sql = @sql +
            ' o.Lup_AS_Reps_ID AS [PHeaderID], ' +
            ' o.[Description] AS [PFilter], '


    IF @FilterC = 0
        SET @sql = @sql +
            ' ISNULL(x.Lup_AC_Customer_Types_ID, 0) AS [CHeaderID], ' +
             ' ISNULL(x.[Description],'''') AS [CFilter], '
    ELSE
    IF @FilterC = 1
        SET @sql = @sql +
            ' ISNULL(x.Lup_AC_Customer_Groups_ID, 0) AS [CHeaderID], ' +
           ' ISNULL(x.[Description],'''') AS [CFilter], '
    ELSE
    IF @FilterC = 2
        SET @sql = @sql +
            ' ISNULL(x.Sys_AC_Account_Types_ID, 0) AS [CHeaderID], ' +
            ' ISNULL(x.[Description],'''') AS [CFilter], '
    ELSE
    IF @FilterC = 3
        SET @sql = @sql +
            ' ISNULL(x.Sys_AC_Account_Status_ID, 0) AS [CHeaderID], ' +
            ' ISNULL(x.[Description],'''') AS [CFilter], '


    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 + ' p.Description AS [Product], '
    SET @sql = @sql + ' CASE WHEN h.Sys_AW_Transaction_Types_ID = 3 '
    SET @sql = @sql + '      THEN ''INV'' '
    SET @sql = @sql + '      WHEN h.Sys_AW_Transaction_Types_ID = 4 '
    SET @sql = @sql + '      THEN ''C/N'' '
    SET @sql = @sql + '      ELSE ''INV'' '
    SET @sql = @sql + ' END AS [TranType], '
    SET @sql = @sql + ' LEFT(CONVERT(CHAR(10), h.Transaction_Date, 121), 10) AS [TranDate], '
    SET @sql = @sql + ' h.Transaction_Number AS [DocNo], '
    SET @sql = @sql + ' CONVERT(money, d.Quantity) AS [Qty], '
    SET @sql = @sql + ' CONVERT(money, d.Extended_Amount - d.Tax_Amount) AS [TotalNetSales], '
    SET @sql = @sql + ' CONVERT(money, d.Unit_Cost * d.Quantity) AS [TotalCost], '
    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 + ' CONVERT(money, ISNULL(p.Standard_Cost, 0) * d.Quantity) AS [TotalStdCost], '
    SET @sql = @sql + ' CONVERT(money, (d.Extended_Amount - d.Tax_Amount) - (d.Quantity * ISNULL(p.Standard_Cost, 0))) AS [TotalStdGP], '
    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, ISNULL(p.Standard_Cost, 0))) / 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 [Std GP%], '
    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 @FilterP = 0
        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 @FilterP = 1
        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 @FilterP = 2
        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 @FilterP = 3
        SET @sql = @sql +
            ' LEFT JOIN AO_Suppliers o ' +
            ' ON p.Default_Supplier_ID = o.AO_Suppliers_ID '
    ELSE
    IF @FilterP = 4
        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 @FilterP = 5
        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 @FilterP = 6
        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 @FilterP = 7
        SET @sql = @sql +
            ' LEFT JOIN Lup_AS_Reps o ' +
            ' ON c.Lup_AS_Reps_ID = o.Lup_AS_Reps_ID '


    IF @FilterC = 0
        SET @sql = @sql +
            ' LEFT JOIN Lup_AC_Customer_Types x ' +
            ' ON c.Lup_AC_Customer_Types_ID = x.Lup_AC_Customer_Types_ID '
    ELSE
    IF @FilterC = 1
        SET @sql = @sql +
            ' LEFT JOIN Lup_AC_Customer_Groups x ' +
            ' ON c.Lup_AC_Customer_Groups_ID = x.Lup_AC_Customer_Groups_ID '
    ELSE
    IF @FilterC = 2
        SET @sql = @sql +
            ' LEFT JOIN Sys_AC_Account_Types x ' +
            ' ON c.Sys_AC_Account_Types_ID = x.Sys_AC_Account_Types_ID '
    ELSE
    IF @FilterC = 3
        SET @sql = @sql +
            ' LEFT JOIN Sys_AC_Account_Status x ' +
            ' ON c.Sys_AC_Account_Status_ID = x.Sys_AC_Account_Status_ID '


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


    IF @ChkAllCustomers = 0    --Single Customer Account
        BEGIN
            SET @sql = @sql + ' AND h.AC_Customers_ID = ' + CONVERT(varchar(10), @CustomerID) + ' '
        END


    IF @Level = 1
        BEGIN
            SET @sql = @sql + ' ORDER BY [Customer] '
        END
    ELSE
    IF @Level = 2
        BEGIN
            SET @sql = @sql + ' ORDER BY [CFilter], [PFilter], [Customer] '
        END
    ELSE
        BEGIN
            SET @sql = @sql + ' ORDER BY [CFilter], [PFilter], [Customer], [Product], [DocNo], [TranDate] '
        END

--PRINT @sql

EXEC (@sql)
GO
/****** Object:  StoredProcedure [dbo].[sp_AS_POS_RPT_Invoice_Print]    Script Date: 02/06/2021 08:04:12 ******/
-------------------------------------------------------------------------------
--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
-------------------------------------------------------------------------------
ALTER 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 = td.AP_Products_ID AND pb.Lup_AP_Product_Bins_ID = td.Lup_AP_Product_Bins_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
/****** Object:  StoredProcedure [dbo].[sp_AS_Transaction_Detail_Consignment]    Script Date: 02/06/2021 08:04:27 ******/
--------------------------------------------------------------------------------
--Description   :sp_AS_Transaction_Detail_Consignment
--Purpose       :Checks to see if product sold was consignment stock
--Functionality :SELECT 
--Parameters    :@AS_Transaction_Details_ID, @UserID, @CompanyID
--Date          :2015/04/10
--Version		:V6.2
--Authors       :RL
--MODS          :
--------------------------------------------------------------------------------

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

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

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


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

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

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

	IF @IsConsignmentStock = 1
	BEGIN

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

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

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

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

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

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

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

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

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

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

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

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


		END
		CLOSE GetDataGRV
		DEALLOCATE GetDataGRV
	
	END
	
--	RETURN @IsConsignmentStock
GO
/****** Object:  UserDefinedFunction [dbo].[fn_AP_BFWD_Inventory_On_Hand]    Script Date: 02/06/2021 08:04:36 ******/
----------------------------------------------------------------------------
--Description   :dbo.fn_AP_BFWD_Inventory_On_Hand
--Purpose       :Retrieves Opening Inventory On Hand BEFORE a date
--Parameters    :
--Date          :2004/10/09
--Authors       :TB
--Version       :1.0.0
--MODS          :
--RL	V6.0	2014-12-05	:Added transaction type 23 - Stock Transfer to sales query
----------------------------------------------------------------------------

ALTER FUNCTION [dbo].[fn_AP_BFWD_Inventory_On_Hand]
    (
        @ProductID int,
        @OpeningDate datetime
    )

RETURNS float

AS

    BEGIN

        DECLARE @GRV float,
                @ADJ float,
                @SALES float,
                @BFWD float

    -------------------------------------------------------------------------------
    -- GOODS RECEIVED
    -------------------------------------------------------------------------------
        SELECT @GRV = ISNULL(SUM(Qty), 0)
        FROM AO_GRV_Details
        WHERE AP_Products_ID = @ProductID AND
              Date_Captured >= @OpeningDate AND
              ISNULL(Qty, 0) <> 0
    -------------------------------------------------------------------------------
    -- ADJUSTMENTS
    -------------------------------------------------------------------------------
        SELECT @ADJ = ISNULL(SUM(ISNULL(New_On_Hand, 0) - ISNULL(Previous_On_Hand, 0)), 0)
        FROM AP_Inventory_Adjustments
        WHERE AP_Products_ID = @ProductID AND
              Date_Captured >= @OpeningDate AND
             (Sys_AP_Adjustment_Types_ID = 1 OR
              Sys_AP_Adjustment_Types_ID = 6) AND
              ISNULL(New_On_Hand, 0) - ISNULL(Previous_On_Hand, 0) <> 0
    -------------------------------------------------------------------------------
    -- SALES
    -------------------------------------------------------------------------------
        SELECT @SALES = ISNULL(SUM(d.Quantity), 0)
        FROM AS_Transaction_Details d
            INNER JOIN AS_Transactions h
                ON d.AS_Transactions_ID = h.AS_Transactions_ID
        WHERE d.AP_Products_ID = @ProductID AND
              h.Date_Captured >= @OpeningDate AND
             (h.Sys_AW_Transaction_Types_ID = 3 OR			--Tax Invoice
              h.Sys_AW_Transaction_Types_ID = 4 OR			--Tax Credit Note
              h.Sys_AW_Transaction_Types_ID = 23 OR			--Stock Transfer
              h.Sys_AW_Transaction_Types_ID = 33 OR			--Consignment Note
              h.Sys_AW_Transaction_Types_ID = 34 OR			--Consignment Sold
              h.Sys_AW_Transaction_Types_ID = 35 ) AND		--Consignment Returned              
              ISNULL(d.Quantity, 0) <> 0
    -------------------------------------------------------------------------------


        SET @BFWD = @GRV + @ADJ - @SALES


        RETURN(@BFWD)

    
    END
GO

/****** Object:  StoredProcedure [dbo].[sp_AB_Select_Cash_Book_Recon_History]    Script Date: 02/06/2021 14:03:19 ******/
-------------------------------------------------------------------------------
--Description   :sp_AB_Select_Cash_Book_Recon_History
--Purpose       :Returns Cash Book Reconciliation Transactions
--              :for specific Bank, Transaction Type, Accounting Period
--Functionality :Select
--Parameters    :
--Date          :2002/11/23
--Authors       :TB
--MODS          :27/11/02 - Removed Recon-Chk field
--              :09/01/03 - Changed SORT ORDER to TranType, TranDate
--2004.4.9      :22/09/10 - Added NEW input parameters @Month_ID and @Year_ID to fetch
--              :           RECONCILED transactions for ONE Accounting Period
--V6.4	RL	2018/02/07	:Added Sys_AB_Transaction_Types_ID 30 - Cash Book - Tax Payment
--V6.4	RL	2018/02/07	:Added Sys_AB_Transaction_Types_ID 31 - Cash Book - Tax Received
-------------------------------------------------------------------------------
-- NB: Actual DB Column** assignments to Aliases below is REQUIRED for setting
--     (adding or assigning) ADO Field.Values in the recordset
-------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[sp_AB_Select_Cash_Book_Recon_History]
    (
        @Company_Banks_ID int,
    	@Company_ID int,
        @Reconciled tinyint,
    	@Month_ID tinyint,
        @Year_ID smallint,
        @Option tinyint
    )

AS

SET NOCOUNT ON


--> Unreconciled Transactions
    IF @Reconciled = 0
        BEGIN
        --> Cheques and other Debits
            IF @Option = 0
                SELECT
                    t.AB_Cash_Book_Transactions_ID AS [ID], 

                --> Actual DB Column assignments is REQUIRED for setting ADO Field.Values
                ----------------------------------------------------
                    t.Recon_Amount AS [Reconciled],
                    t.Amount AS [Balance],
                    t.Amount AS [Amount], 
                ----------------------------------------------------
                    tt.Transaction_Code AS [Type],
                    t.Reference_No AS [Ref No], 
                    t.Transaction_Date AS [Date], 
                    m.[Description] AS [Month], 
                    y.[Description] AS [Year],
                    t.Transaction_Description AS [Description],
                   (t.Amount - t.Recon_Amount) AS [OldBalance]
                FROM AB_Cash_Book_Transactions t
                    LEFT JOIN Sys_AB_Transaction_Types tt
                    	ON t.Sys_AB_Transaction_Types_ID = tt.Sys_AB_Transaction_Types_ID
                    LEFT JOIN AW_Company_Banks b
                    	ON t.AW_Company_Banks_ID = b.AW_Company_Banks_ID
                    LEFT JOIN Slu_AW_Months m
                    	ON t.Slu_AW_Months_ID = m.Slu_AW_Months_ID
                    LEFT JOIN Slu_AW_Years y
                    	ON t.Slu_AW_Years_ID = y.Slu_AW_Years_ID
                WHERE t.AW_Company_Banks_ID = @Company_Banks_ID AND 
                      t.AW_User_Companies_ID = @Company_ID AND
                      ISNULL(t.Recon_Amount, 0) <> ISNULL(t.Amount, 0) AND
                     (tt.Sys_AB_Transaction_Types_ID = 9 OR         --Cash Book - Cheque
                      tt.Sys_AB_Transaction_Types_ID = 11 OR        --Cash Book - Interest Paid
                      tt.Sys_AB_Transaction_Types_ID = 12 OR        --Cash Book - Bank Charge
                      tt.Sys_AB_Transaction_Types_ID = 13 OR        --Credit Card - Charge
                      tt.Sys_AB_Transaction_Types_ID = 14 OR        --Credit Card - Payment
                      tt.Sys_AB_Transaction_Types_ID = 18 OR        --Cash Book - Debit Order
                      tt.Sys_AB_Transaction_Types_ID = 20 OR        --Cash Book - EFT Payment
                      tt.Sys_AB_Transaction_Types_ID = 21 OR		--Cash Book - EFT Payment          --Cash Book - Buy Association
					  tt.Sys_AB_Transaction_Types_ID = 30)		--Cash Book - Tax Payment       
					  --tt.Sys_AB_Transaction_Types_ID = 31)			--Cash Book - Tax Received       
                ORDER BY tt.Transaction_Code, t.Transaction_Date  

        --> Deposits and other Credits
            ELSE
            IF @Option = 1
                SELECT
                    t.AB_Cash_Book_Transactions_ID AS [ID], 
    
                --> Actual DB Column assignments is REQUIRED for setting ADO Field.Values
                ----------------------------------------------------
                    t.Recon_Amount AS [Reconciled],
                    t.Amount AS [Balance],
                    t.Amount AS [Amount], 
                ----------------------------------------------------
                    tt.Transaction_Code AS [Type],
                    t.Reference_No AS [Ref No], 
                    t.Transaction_Date AS [Date], 
                    m.[Description] AS [Month], 
                    y.[Description] AS [Year],
                    t.Transaction_Description AS [Description],
                   (t.Amount - t.Recon_Amount) AS [OldBalance]
                FROM AB_Cash_Book_Transactions t
                    LEFT JOIN Sys_AB_Transaction_Types tt
                    	ON t.Sys_AB_Transaction_Types_ID = tt.Sys_AB_Transaction_Types_ID
                    LEFT JOIN AW_Company_Banks b
                    	ON t.AW_Company_Banks_ID = b.AW_Company_Banks_ID
                    LEFT JOIN Slu_AW_Months m
                    	ON t.Slu_AW_Months_ID = m.Slu_AW_Months_ID
                    LEFT JOIN Slu_AW_Years y
                    	ON t.Slu_AW_Years_ID = y.Slu_AW_Years_ID
                WHERE t.AW_Company_Banks_ID = @Company_Banks_ID AND 
                      t.AW_User_Companies_ID = @Company_ID AND
                      ISNULL(t.Recon_Amount, 0) <> ISNULL(t.Amount, 0) AND
                     (tt.Sys_AB_Transaction_Types_ID = 10 OR        --Cash Book - Deposit
                      tt.Sys_AB_Transaction_Types_ID = 17 OR        --Cash Book - Interest Received
                      tt.Sys_AB_Transaction_Types_ID = 19 OR          --Cash Book - EFT Deposit
                      tt.Sys_AB_Transaction_Types_ID = 31)			--Cash Book - Tax Received    
                ORDER BY tt.Transaction_Code, t.Transaction_Date 

        END

--> Reconciled Transactions
    ELSE
    IF @Reconciled = 1
        BEGIN
        --> Cheques and other Debits
            IF @Option = 0
                SELECT
                    t.AB_Cash_Book_Transactions_ID AS [ID], 

                --> Actual DB Column assignments is REQUIRED for setting ADO Field.Values
                ----------------------------------------------------
                    t.Recon_Amount AS [Reconciled],
                    t.Amount AS [Balance],
                    t.Amount AS [Amount], 
                ----------------------------------------------------
                    tt.Transaction_Code AS [Type],
                    t.Reference_No AS [Ref No], 
                    t.Transaction_Date AS [Date], 
                    m.[Description] AS [Month], 
                    y.[Description] AS [Year],
                    t.Transaction_Description AS [Description],
                   (t.Amount - t.Recon_Amount) AS [OldBalance]
                FROM AB_Cash_Book_Transactions t
                    LEFT JOIN Sys_AB_Transaction_Types tt
                    	ON t.Sys_AB_Transaction_Types_ID = tt.Sys_AB_Transaction_Types_ID
                    LEFT JOIN AW_Company_Banks b
                    	ON t.AW_Company_Banks_ID = b.AW_Company_Banks_ID
                    LEFT JOIN Slu_AW_Months m
                    	ON t.Slu_AW_Months_ID = m.Slu_AW_Months_ID
                    LEFT JOIN Slu_AW_Years y
                    	ON t.Slu_AW_Years_ID = y.Slu_AW_Years_ID
                WHERE t.AW_Company_Banks_ID = @Company_Banks_ID AND 
                      t.AW_User_Companies_ID = @Company_ID AND
                      t.Slu_AW_Months_ID = @Month_ID AND
                      t.Slu_AW_Years_ID = @Year_ID AND
                     (t.Sys_AB_Transaction_Types_ID = 9 OR         --Cash Book - Cheque
                      t.Sys_AB_Transaction_Types_ID = 11 OR        --Cash Book - Interest Paid
                      t.Sys_AB_Transaction_Types_ID = 12 OR        --Cash Book - Bank Charge
                      t.Sys_AB_Transaction_Types_ID = 13 OR        --Credit Card - Charge
                      t.Sys_AB_Transaction_Types_ID = 14 OR        --Credit Card - Payment
                      t.Sys_AB_Transaction_Types_ID = 18 OR        --Cash Book - Debit Order
                      t.Sys_AB_Transaction_Types_ID = 20 OR        --Cash Book - EFT Payment
                      t.Sys_AB_Transaction_Types_ID = 21 OR          --Cash Book - Buy Association
					  t.Sys_AB_Transaction_Types_ID = 30)			--Cash Book - Tax Payment       
					  --t.Sys_AB_Transaction_Types_ID = 31)			--Cash Book - Tax Received       
                ORDER BY tt.Transaction_Code, t.Transaction_Date 

        --> Deposits and other Credits
            ELSE
            IF @Option = 1
                SELECT
                    t.AB_Cash_Book_Transactions_ID AS [ID], 
    
                --> Actual DB Column assignments is REQUIRED for setting ADO Field.Values
                ----------------------------------------------------
                    t.Recon_Amount AS [Reconciled],
                    t.Amount AS [Balance],
                    t.Amount AS [Amount], 
                ----------------------------------------------------
                    tt.Transaction_Code AS [Type],
                    t.Reference_No AS [Ref No], 
                    t.Transaction_Date AS [Date], 
                    m.[Description] AS [Month], 
                    y.[Description] AS [Year],
                    t.Transaction_Description AS [Description],
                   (t.Amount - t.Recon_Amount) AS [OldBalance]
                FROM AB_Cash_Book_Transactions t
                    LEFT JOIN Sys_AB_Transaction_Types tt
                    	ON t.Sys_AB_Transaction_Types_ID = tt.Sys_AB_Transaction_Types_ID
                    LEFT JOIN AW_Company_Banks b
                    	ON t.AW_Company_Banks_ID = b.AW_Company_Banks_ID
                    LEFT JOIN Slu_AW_Months m
                    	ON t.Slu_AW_Months_ID = m.Slu_AW_Months_ID
                    LEFT JOIN Slu_AW_Years y
                    	ON t.Slu_AW_Years_ID = y.Slu_AW_Years_ID
                WHERE t.AW_Company_Banks_ID = @Company_Banks_ID AND 
                      t.AW_User_Companies_ID = @Company_ID AND
                      t.Slu_AW_Months_ID = @Month_ID AND
                      t.Slu_AW_Years_ID = @Year_ID AND
                     (t.Sys_AB_Transaction_Types_ID = 10 OR        --Cash Book - Deposit
                      t.Sys_AB_Transaction_Types_ID = 17 OR        --Cash Book - Interest Received
                      t.Sys_AB_Transaction_Types_ID = 19 OR          --Cash Book - EFT Deposit
                      t.Sys_AB_Transaction_Types_ID = 31)			--Cash Book - Tax Received    
                ORDER BY tt.Transaction_Code, t.Transaction_Date 

        END


SET NOCOUNT OFF

GO

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

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

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

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


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

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

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

	IF @IsConsignmentStock = 1
	BEGIN

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

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

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

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

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

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

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

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

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

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

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

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


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

-------------------------------------------------------------------------------
--Description   :sp_AB_Select_Cash_Book_Transactions
--Purpose       :Returns the Cash Book Transactions
--              :for specific Bank, Transaction Type
--Date          :2002/03/08
--Authors       :TB
--Version       :1.0.0
-------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[sp_AB_Select_Cash_Book_Transactions]
    (
        @Company_Banks_ID int,
        @Transaction_Type_ID int,
    	@Company_ID int
    )

AS

SET NOCOUNT ON

    SELECT AB_Cash_Book_Transactions_ID AS [ID], 
           AW_Company_Banks_ID AS Bank, 
           Sys_AB_Transaction_Types_ID AS [Transaction Types], 
           Transaction_Date AS [Date], 
           Slu_AW_Months_ID AS [Month], 
           Slu_AW_Years_ID AS [Year],
           Reference_No AS [Ref No], 
           Transaction_Description AS [Description], 
           Amount
    FROM AB_Cash_Book_Transactions 
    WHERE AW_Company_Banks_ID = @Company_Banks_ID AND 
          Sys_AB_Transaction_Types_ID = @Transaction_Type_ID AND 
          AW_User_Companies_ID = @Company_ID 

SET NOCOUNT OFF

GO


/****** Object:  StoredProcedure [dbo].[sp_RPT_AP_Inventory_Adjustments]    Script Date: 03/07/2021 11:17:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---------------------------------------------------------------------------------
--Description   :sp_RPT_AP_Inventory_Adjustments
--Purpose       :Retrieves filtered list of Inventory Adjustments
--Date          :2004/01/08
--Authors       :TB
--MODS          :24/02/06 - New Dynamic definition with COMMON code section
--2004.4.1      :29/09/08 - Modified Product Filter behaviour to show adjustments 
--              :           listed by ALL filters applicable to selected filter
--2004.4.2      :16/11/08 - Added @AdjustType AGAIN !!!!
--2004.4.3      :21/02/09 - Modified ORDER BY from [ORDER BY a.Date_Captured] to
--              :           [ORDER BY [Product], [Date]] to match NEW Stock Take 
--V6.0	RL	2014-09-01	:Goods Returned as an option
---------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[sp_RPT_AP_Inventory_Adjustments]
    (
        @CompanyID int, 
        @AdjustType tinyint,
        @SearchFilter int,
        @SearchDescription varchar(50),
        @chkDateRange tinyint,
        @StartDate datetime,
        @EndDate datetime,
        @NonStock tinyint
    )
AS

SET NOCOUNT ON

    DECLARE @sql varchar(8000)

    SET @SearchDescription = RTRIM(LTRIM(@SearchDescription))


    SET @sql = ' SELECT '

    IF @SearchFilter = 3
        SET @sql = @sql +
            ' o.Lup_AP_Product_Types_ID AS [HeaderID], ' +
            ' o.[Description] AS [Filter], '
    ELSE
    IF @SearchFilter = 4
        SET @sql = @sql +
            ' o.Lup_AP_Product_Groups_ID AS [HeaderID], ' +
            ' o.[Description] AS [Filter], '
    ELSE
    IF @SearchFilter = 5
        SET @sql = @sql +
            ' o.Lup_AP_Product_Departments_ID AS [HeaderID], ' +
            ' o.[Description] AS [Filter], '
    ELSE
    IF @SearchFilter = 1
        SET @sql = @sql +
            ' o.AO_Suppliers_ID AS [HeaderID], ' +
            ' o.[Supplier_Name] AS [Filter], '
    ELSE
    IF @SearchFilter = 2
        SET @sql = @sql +
            ' o.Lup_AP_Product_Manufacturers_ID AS [HeaderID], ' +
            ' o.[Description] AS [Filter], '
    ELSE
    IF @SearchFilter = 6
        SET @sql = @sql +
            ' o.Lup_AP_Product_Locations_ID AS [HeaderID], ' +
            ' o.[Description] AS [Filter], '


    SET @sql = @sql + ' a.AP_Inventory_Adjustments_ID AS [ID], '
    SET @sql = @sql + ' dbo.fn_AB_Get_Ending_GL_Period(month(a.Date_Captured), year(a.Date_Captured)) AS [Period], '
    SET @sql = @sql + ' a.AP_Products_ID AS [ProductID], '
    SET @sql = @sql + ' p.[Description] AS [Product], '
    SET @sql = @sql + ' p.Product_Code AS [Code], '
    SET @sql = @sql + ' t.[Description] AS [Type], '
    SET @sql = @sql + ' a.Note AS [Note], '
    SET @sql = @sql + ' a.Previous_On_Hand AS [Prev O/H], '
    SET @sql = @sql + ' a.New_On_Hand AS [New O/H], '
    SET @sql = @sql + ' a.Previous_Last_Cost AS [Prev Last Cost], '
    SET @sql = @sql + ' a.New_Last_Cost AS [New Last Cost], '
    SET @sql = @sql + ' a.Previous_Average_Cost AS [Prev Ave. Cost], '
    SET @sql = @sql + ' a.New_Average_Cost AS [New Ave. Cost], '
    SET @sql = @sql + ' (ISNULL(a.New_Last_Cost, 0) * ISNULL(a.New_On_Hand, 0)) - (ISNULL(a.Previous_Last_Cost, 0) * ISNULL(a.Previous_On_Hand, 0)) AS [Last Cost Val], '
    SET @sql = @sql + ' (ISNULL(a.New_Average_Cost, 0) * ISNULL(a.New_On_Hand, 0)) - (ISNULL(a.Previous_Average_Cost, 0) * ISNULL(a.Previous_On_Hand, 0)) AS [Ave. Cost Val], '
    SET @sql = @sql + ' a.Date_Captured AS [Date], '
    SET @sql = @sql + ' u.UserName AS [User] '

    SET @sql = @sql + ' FROM AP_Inventory_Adjustments a '
    SET @sql = @sql + ' LEFT JOIN AP_Products p '
    SET @sql = @sql + '         ON a.AP_Products_ID = p.AP_Products_ID '
    SET @sql = @sql + ' LEFT JOIN Sys_AP_Adjustment_Types t '
    SET @sql = @sql + '         ON a.Sys_AP_Adjustment_Types_ID = t.Sys_AP_Adjustment_Types_ID '
    SET @sql = @sql + ' LEFT JOIN AW_Users u '
    SET @sql = @sql + '         ON a.[User_ID] = u.AW_Users_ID '


    IF @SearchFilter = 3
        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 @SearchFilter = 4
        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 @SearchFilter = 5
        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 @SearchFilter = 1
        SET @sql = @sql +
            ' LEFT JOIN AO_Suppliers o ' +
            '     ON p.Default_Supplier_ID = o.AO_Suppliers_ID '
    ELSE
    IF @SearchFilter = 2
        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 @SearchFilter = 6
        SET @sql = @sql +
            ' LEFT JOIN Lup_AP_Product_Locations o ' +
            '     ON p.Lup_AP_Product_Locations_ID = o.Lup_AP_Product_Locations_ID '


    SET @sql = @sql + ' WHERE a.AW_User_Companies_ID = ' + CONVERT(varchar(10), @CompanyID)


    IF @AdjustType = 1
        BEGIN
            SET @sql = @sql + ' AND a.Sys_AP_Adjustment_Types_ID = 1 '
        END
    ELSE
    IF @AdjustType = 2
        BEGIN
            SET @sql = @sql + ' AND a.Sys_AP_Adjustment_Types_ID = 2 '
        END
    ELSE
    IF @AdjustType = 3
        BEGIN
            SET @sql = @sql + ' AND a.Sys_AP_Adjustment_Types_ID = 3 '
        END
    ELSE
    IF @AdjustType = 4
        BEGIN
            SET @sql = @sql + ' AND a.Sys_AP_Adjustment_Types_ID = 4 '
        END
    ELSE
    IF @AdjustType = 5
        BEGIN
            SET @sql = @sql + ' AND a.Sys_AP_Adjustment_Types_ID = 5 '
        END
    ELSE
    IF @AdjustType = 6
        BEGIN
            SET @sql = @sql + ' AND a.Sys_AP_Adjustment_Types_ID = 6 '
        END
    ELSE
    IF @AdjustType = 7
        BEGIN
            SET @sql = @sql + ' AND a.Sys_AP_Adjustment_Types_ID = 7 '
        END

    IF @SearchFilter = 7        --> PRODUCT DESCRIPTION SEARCH
        BEGIN
            SET @sql = @sql + ' AND (p.[Description] LIKE ''%' + @SearchDescription + '%'' OR '
            SET @sql = @sql + '      p.Product_Code LIKE ''%' + @SearchDescription + '%'' ) '
        END


    IF @chkDateRange = 1   --Use Date Range
        BEGIN
            SET @sql = @sql + ' AND (a.Date_Captured >= ' + '''' + CONVERT(varchar(30), @StartDate) + '''' + ' '
            SET @sql = @sql + ' AND  a.Date_Captured <= ' + '''' + CONVERT(varchar(30), @EndDate) + '''' + ') '
        END


    IF @NonStock = 1       --Exclude Non-Stock Items
        BEGIN
            SET @sql = @sql + ' AND p.Slu_AW_YesNo_Non_Stock_Item = 0 '
        END


--    SET @sql = @sql + ' ORDER BY a.Date_Captured '
    SET @sql = @sql + ' ORDER BY [HeaderID], [Date] '


    EXEC (@sql)

GO

CREATE PROCEDURE [dbo].[sp_AS_POS_RPT_Payment_Print]
    (
    	@TransactionID int
    )

AS

SET NOCOUNT ON

    DECLARE @WhichAddress tinyint,
            @WhichName tinyint,
            @AddressOption0 varchar(20),
            @AddressOption1 varchar(20)


    SET @WhichAddress = 0


    SELECT TOP 1 @AddressOption0 = ISNULL(Control_Value, '')
    FROM Sys_AW_Options
    WHERE Control_Type = 'OptionButton' AND
          Control_Name = 'optPOSPrintAddress' AND
          Control_Index = 0

    SELECT TOP 1 @AddressOption1 = ISNULL(Control_Value, '')
    FROM Sys_AW_Options
    WHERE Control_Type = 'OptionButton' AND
          Control_Name = 'optPOSPrintAddress' AND
          Control_Index = 1

    IF @AddressOption0 = 'True'
        BEGIN
            SET @WhichAddress = 0        --Print POSTAL Address
        END
    ELSE
    IF @AddressOption1 = 'True'
        BEGIN
            SET @WhichAddress = 1        --Print STREET Addres
        END


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


	SELECT 
		RTRIM(LTRIM(c.Surname)) + ISNULL(', ' + c.Initials, '') as CustomerName,

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

		ISNULL(c.Street_Address, '') AS [StreetAddress],
		ISNULL(c.Suburb, '') AS [StreetSuburb],
		ISNULL(c.City, '') AS [StreetCity],
		ISNULL(c.Street_Post_Code, '') AS [StreetPostCode],

		ISNULL(c.Delivery_Address, '') AS [DeliverAddress],
		ISNULL(c.Delivery_Suburb, '') AS [DeliverSuburb],
		ISNULL(c.Delivery_City, '') AS [DeliverCity],
		ISNULL(c.Delivery_Post_Code, '') AS [DeliverPostCode],

		CONVERT(VARCHAR(12), c.AC_Customers_ID) AS [CustomerID], 
		ISNULL(c.Customer_Number, '') + '(' + CONVERT(VARCHAR(12), c.AC_Customers_ID) + ')' as [CustomerNo],

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

        t.Sys_AW_Transaction_Types_ID AS [TranTypeID],
		tt.[Description] as TransactionType,
		comp.Tax_Number as CompanyTaxNumber,
		comp.Company_Name as CompanyName,
		comp.Company_Trading_Name AS TradingName,
		comp.Company_Addr1 as CompanyAddress1,
		comp.Company_Addr2 as CompanyAddress2,
		comp.Company_City as CompanyCity,
		comp.Company_Post_Code as CompanyPostCode,
        comp.Company_Registration_Number as CompanyRegNo,
        comp.Tel_No as CompanyTelNo,
        comp.Fax_No as CompanyFaxNo,
		rep.[Description] as RepName,
		t.AS_Transactions_ID as TransactionID,
		t.Transaction_Number as TransactionNo,
		t.Transaction_Date as TransactionDate,
		t.Customer_Reference as TransactionCustomerRef,
        t.Amount AS TransactionAmount,
		t.Total_Amount as TransactionTotalAmount,
		t.Total_Discount_Amount as TransactionTotalDiscount,
		t.Total_Tax_Amount as TransactionTotalTax,
		com.Comment AS TransactionComment,
-------------------------------------------------------------------------------
		pm.Description as PaymentMethod,
		tp.Payor_Name as PaymentPayorName,
		tp.Card_Expiry_Date as PaymentCardExpiry,
		tp.Card_Number as PaymentCardNo,
		tp.Authorisation_Number as PaymentAuthorisationNo,
		tp.Amount as PaymentAmount,
        CONVERT(varchar(4), DATEPART(hour, t.Transaction_Date)) + ':' +
        CONVERT(varchar(4), DATEPART(minute, t.Transaction_Date)) + ':' +
        CONVERT(varchar(4), DATEPART(second, t.Transaction_Date)) AS TransactionTime,
        u.UserName as [UserCode],
--        t.SmallComment AS [Comment],
        t.Comment AS [Comment],
        t.AW_User_Companies_ID AS [CompanyID],
        @WhichAddress AS [AddressType],
        @WhichName AS [NameType],
	    ISNULL(ca.Reg_No, '') AS [Reg_No]

--	FROM vw_AS_Transactions t 
	FROM AS_Transactions t 
		LEFT JOIN AC_Customers c
            ON t.AC_Customers_ID = c.AC_Customers_ID
		LEFT JOIN Sys_AW_Transaction_Types tt
            ON t.Sys_AW_Transaction_Types_ID = tt.Sys_AW_Transaction_Types_ID
		LEFT JOIN AW_User_Companies comp
            ON t.AW_User_Companies_ID = comp.AW_User_Companies_ID
		LEFT JOIN Lup_AS_Reps rep
            ON t.Lup_AS_Reps_ID = rep.Lup_AS_Reps_ID
		LEFT JOIN AS_Transaction_Payments tp
            ON	t.AS_Transactions_ID = tp.AS_Transactions_ID
		LEFT JOIN Sys_AW_Payment_Methods pm
            ON tp.Sys_AW_Payment_Methods_ID = pm.Sys_AW_Payment_Methods_ID
        LEFT JOIN AW_Users u
            ON t.[User_ID] = u.AW_Users_ID
        LEFT JOIN AW_Comments com
            ON t.AW_Comments_ID = com.AW_Comments_ID
        LEFT JOIN AC_Assets ca
            ON t.AS_Transactions_ID = ca.AS_Transactions_ID   
	WHERE t.AS_Transactions_ID = @TransactionID


SET NOCOUNT OFF
GO
