/*
* Builds a list of the existing Item Code to-Service Codes in order to build a
* worksheet to assist in the setup of van line service code mappings to Item Codes.
*/
CREATE PROCEDURE [dbo].[ItemCodeVanlineMappingImportWorksheet]
    @inIncludeMappings bit
as
set nocount on

set @inIncludeMappings = isnull( @inIncludeMappings, 0 )

-- The tabs are so that the results can simply be pasted into an Excel workbook
select
    concat(
        isnull( VanLineGroup.VanlineExternalCode, '' ),
        char(9),
        isnull( ItemCodeVanLineMapping.VanlineServiceCode, '' ),
        char(9),
        isnull( ItemCodeVanLineMapping.VanlineCartonCode, '' ),
        char(9),
        ItemCode.ItemCode,
        char(9),
        isnull( ItemCodeVanLineMapping.VanlineDescription, '' ),
        char(9),
        ItemCode.[Description],
        char(9),
        isnull( ItemCode.GLGroup, '' ),
        char(9),
        isnull( ServiceCode.ServiceCode, '' ),
        char(9),
        isnull( ServiceCode.[Description], '' ),
        char(9),
        isnull( RevGroups.[Description], '' )
    )
from ItemCode
left outer join dbo.ServiceCode on ServiceCode.ServiceCodePriKey = ItemCode.ServiceCodePriKey
left outer join dbo.RevGroups on RevGroups.RGPriKey = ItemCode.RGPriKey
left outer join ItemCodeVanLineMapping on @inIncludeMappings = 1 and ItemCodeVanLineMapping.ItemCodeFID = ItemCode.ICPriKey
left outer join VanLineGroup on VanLineGroup.VanLineGroupID = ItemCodeVanLineMapping.VanLineGroupFID
order by ItemCode.ItemCode, VanLineGroup.VanlineExternalCode
GO
GRANT EXECUTE ON  [dbo].[ItemCodeVanlineMappingImportWorksheet] TO [MssExec]
GO