/**
*    
*    This procedure is called to generate the xml segment for Local Services
*    information for the given order.
*    
*    Possible Error Codes:
*    @ERROR_CODE_INVALID_ORDER
*    
*    @param @inOrderID The primary key of a MoversSuite Orders record.
*    @param @inXmlInterfaceID The primary key of a XmlInterface record.
*    @param @outStatusCode Returns whether or not an error occurred while executing this stored procedure.
*    @param @outErrorCode Returns any errors encountered while executing this stored procedure.
*/
CREATE procedure [dbo].[XmlExportLocalServicesInformation]
    @inOrderID int,
    @inXmlInterfaceID int,
    @outStatusCode int output,
    @outErrorCode int output
as
set nocount on;
-- Initialize the output parameters.
set @outStatusCode    = 0;
set @outErrorCode    = 0;
-- Define the error codes.
declare @ERROR_CODE_INVALID_ORDER    int;
set        @ERROR_CODE_INVALID_ORDER    = 2048;
-- Define the status codes.
declare @STATUS_CODE_PROCESSED    int;
set        @STATUS_CODE_PROCESSED    = 1;
declare @STATUS_CODE_ERROR        int;
set        @STATUS_CODE_ERROR        = 0;
-- Check the order.
declare @theValidOrderFlag bit;
exec    @theValidOrderFlag = dbo.ValidateOrderID @inOrderID;
-- If the order is not valid, return error code @ERROR_CODE_INVALID_ORDER.
if ( 0 = @theValidOrderFlag )
begin
    set @outErrorCode  = @ERROR_CODE_INVALID_ORDER;
    set @outStatusCode = @STATUS_CODE_ERROR;
end
-- Otherwise return the xml segment and set the
-- status code to @STATUS_CODE_PROCESSED.
else
begin
    
    -- Get the Local Services order information
    -- This flag is passed to dbo.GetSysuserName to tell it to format the name like "FirstName LastName".
    declare @theFirstNameFlag bit
    set @theFirstNameFlag = 1

    declare @theMainAddressTypeId int
    select @theMainAddressTypeId = AddressTypeID from AddressType where TypeName = 'Main'

    -- We use various CTEs to load up all of the local service and related data.
    ;with LocalServices as (
        select
            LocalServiceId = LocalService.PriKey,
            dbo.ConvertToStandardDate( LocalService.ServiceDate ) as ServiceDate,
            dbo.ConvertToStandardTime( LocalService.RequestedStartTime, 0 ) as RequestedStartTime,
            dbo.ConvertToStandardTime( LocalService.RequestedEndTime, 0 ) as RequestedEndTime,
            LocalService.WkTicketNo as WorkTicketNumber,
            ServType.[Service] as ServiceType,
            dbo.ConvertToStandardTime( LocalService.JobStart, 0 ) as JobStart,
            dbo.ConvertToStandardTime( LocalService.JobEnd, 0 ) as JobEnd,
            LocalService.TotCrewHrs as CrewHours,
            LocalService.HrsPerMan as HoursPerMan,
            LSStatus.[Status] as [Status],
            LocalService.HrsDrv1Way as HoursDriveOneWay,
            LocalService.Quantity as Quantity,
            LocalService.Crew as Crew,
            LocalService.OriginDestination as OriginDestination,
            dbo.ConvertToStandardDate( LocalService.CreatedOn ) as CreatedDate,
            dbo.GetSysuserName( LocalService.CreatedBy, @theFirstNameFlag ) as CreatedBy,
            dbo.ConvertToStandardDate( LocalService.LastEditedOn ) as LastEditedDate,
            dbo.GetSysuserName( LocalService.LastEditedBy, @theFirstNameFlag ) as LastEditedBy,
            dbo.ConvertToStandardDate( LocalService.SubmittedOn ) as SubmittedDate,
            dbo.GetSysuserName( LocalService.SubmittedBy, @theFirstNameFlag ) as SubmittedBy,
            dbo.ConvertToStandardDate( LocalService.ClearedOn ) as ClearedDate,
            dbo.GetSysuserName( LocalService.ClearedBy, @theFirstNameFlag ) as ClearedBy,
            Branch.[Name] as Branch,
            DispatchStatus.[Status] as DispatchStatus,
            ContainerizationStatus.[Description] as ContainerizationStatus,
            dbo.GetSysuserName( LocalService.VoidedBy, @theFirstNameFlag ) as VoidedBy,
            dbo.ConvertToStandardDate( LocalService.VoidedOn ) as VoidedDate,
            LocalService.CartonCountSource as CartonCountSource,
            dbo.GetSysuserName( LocalService.CancelledBy, @theFirstNameFlag ) as CancelledBy,
            dbo.ConvertToStandardDate( LocalService.CancelledOn ) as CancelledDate,
            OperationalPlan.PlanName as OperationalPlan,
            dbo.ConvertToStandardTime( LocalService.CrewReportTime, 0 ) as CrewReportTime,
            CrewReportLocation.ReportLocation as ReportLocation,
            case when LocalService.IsPlaceHolder = 1 then 'Yes' else 'No' end as IsPlaceHolder,
            LSInstructions.Instruction as ServiceInstructions,

            OriginLocation.[Name] as OriginLocationName,
            OriginLocationContact.Contact as OriginLocationContact,
            OriginLocationAddress.Address1 as OriginLocationAddress1,
            OriginLocationAddress.Address2 as OriginLocationAddress2,
            OriginLocationAddress.Address3 as OriginLocationAddress3,
            OriginLocationAddress.City as OriginLocationCity,
            OriginLocationAddress.[State] as OriginLocationState,
            OriginLocationAddress.PostalCode as OriginLocationPostalCode,
            OriginCountryCode.CountryName as OriginLocationCountry,

            DestinationLocation.[Name] as DestinationLocationName,
            DestinationLocationContact.Contact as DestinationLocationContact,
            DestinationLocationAddress.Address1 as DestinationLocationAddress1,
            DestinationLocationAddress.Address2 as DestinationLocationAddress2,
            DestinationLocationAddress.Address3 as DestinationLocationAddress3,
            DestinationLocationAddress.City as DestinationLocationCity,
            DestinationLocationAddress.[State] as DestinationLocationState,
            DestinationLocationAddress.PostalCode as DestinationLocationPostalCode,
            DestinationCountryCode.CountryName as DestinationLocationCountry

        from LocServ as LocalService
        inner join ServType on ServType.ServiceID = LocalService.ServiceID
        inner join Branch on Branch.BranchPriKey = LocalService.BranchPriKey
        left outer join OrderLocations as OriginLocation on OriginLocation.OrderLocationID = LocalService.OriginOrderLocationFID
        left outer join OrderLocationAddress as OriginLocationAddress on ( OriginLocation.OrderLocationID = OriginLocationAddress.OrderLocationFID and OriginLocationAddress.AddressTypeFID = @theMainAddressTypeId )
        left outer join CountryCodeStandard as OriginCountryCode on OriginLocationAddress.CountryCodeStandardFID = OriginCountryCode.CountryCodeStandardID
        left outer join OrderContacts as OriginLocationContact on OriginLocation.OrderContactFID = OriginLocationContact.OrderContactID
        left outer join OrderLocations as DestinationLocation on DestinationLocation.OrderLocationID = LocalService.DestinationOrderLocationFID
        left outer join OrderLocationAddress as DestinationLocationAddress on ( DestinationLocation.OrderLocationID = DestinationLocationAddress.OrderLocationFID and DestinationLocationAddress.AddressTypeFID = @theMainAddressTypeId )
        left outer join CountryCodeStandard as DestinationCountryCode on DestinationLocationAddress.CountryCodeStandardFID = DestinationCountryCode.CountryCodeStandardID
        left outer join OrderContacts as DestinationLocationContact on DestinationLocation.OrderContactFID = DestinationLocationContact.OrderContactID
        left outer join LSStatus on LSStatus.PriKey = LocalService.ServStatus
        left outer join DispatchStatus on DispatchStatus.DispatchStatusID = LocalService.DispatchStatusFID
        left outer join ContainerizationStatus on ContainerizationStatus.ContainerizationStatusID = LocalService.ContainerizationStatusFID
        left outer join OperationalPlan on OperationalPlan.OperationalPlanID = LocalService.OperationalPlanFID
        left outer join CrewReportLocation on CrewReportLocation.CrewReportLocationID = LocalService.CrewReportLocationFID
        left outer join LSInstructions on LSInstructions.LSPriKey = LocalService.PriKey
        where LocalService.OrdPriKey = @inOrderId
    ),
    LaborRequests as (
        select
            LocalServiceId = LocalServiceLaborRequest.LocalServiceFID,
            LaborType.LaborType,
            LocalServiceLaborRequest.Quantity,
            LocalServiceLaborRequest.LocalServiceLaborRequestID
        from LocalServices
        inner join LocalServiceLaborRequest on LocalServiceLaborRequest.LocalServiceFID = LocalServices.LocalServiceId
        inner join LaborType on LaborType.PriKey = LocalServiceLaborRequest.LaborTypeFID
    ),
    EquipmentRequests as (
        select
            LocalServiceId = LocalServiceEquipmentRequest.LocalServiceFID,
            EqType.[Type] as EquipmentType,
            LocalServiceEquipmentRequest.Quantity,
            LocalServiceEquipmentRequest.LocalServiceEquipmentRequestID
        from LocalServices
        inner join LocalServiceEquipmentRequest on LocalServiceEquipmentRequest.LocalServiceFID = LocalServices.LocalServiceId
        inner join EqType on EqType.TypeNumber = LocalServiceEquipmentRequest.EquipmentTypeFID
    ),
    Materials as (
        -- Material items (outer <Material> element), with inner fields Material, CPUType, EstimatedQuantity
        select
            LocalServiceId = LSMaterial.LocServPriKey,
            MaterialType.[Description] as Material,
            MaterialCPUType.[Type] as CPUType,
            LSMaterial.EstQuantity as EstimatedQuantity,
            MaterialType.SortOrder as SortOrder
        from LocalServices
        inner join LSMaterial on LSMaterial.LocServPriKey = LocalServices.LocalServiceId
        inner join MaterialItemCodeMap on MaterialItemCodeMap.MaterialItemCodeMapID = LSMaterial.MaterialItemCodeMapFID
        inner join MaterialCPUType on MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
        inner join MaterialType on MaterialType.MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
    )

    -- =========================
    -- Unified column schema
    -- =========================
    -- NOTE: Every SELECT below returns ALL these columns, in the same order.
    -- We use the HIDE declaration so that we can sort the rows of all of the
    -- UNION ALL clauses by LocServ.PriKey values but the HIDE prevents that
    -- value from being output in the generated XML.
    select
        Tag,
        Parent,

        -- LocalService (Tag 1)
        [LocalService!1!!hide],
        [LocalService!1!ServiceDate!element],
        [LocalService!1!RequestedStartTime!element],
        [LocalService!1!RequestedEndTime!element],
        [LocalService!1!WorkTicketNumber!element],
        [LocalService!1!ServiceType!element],
        [LocalService!1!JobStart!element],
        [LocalService!1!JobEnd!element],
        [LocalService!1!CrewHours!element],
        [LocalService!1!HoursPerMan!element],
        [LocalService!1!Status!element],
        [LocalService!1!HoursDriveOneWay!element],
        [LocalService!1!Quantity!element],
        [LocalService!1!Crew!element],
        [LocalService!1!OriginDestination!element],
        [LocalService!1!CreatedDate!element],
        [LocalService!1!CreatedBy!element],
        [LocalService!1!LastEditedDate!element],
        [LocalService!1!LastEditedBy!element],
        [LocalService!1!SubmittedDate!element],
        [LocalService!1!SubmittedBy!element],
        [LocalService!1!ClearedDate!element],
        [LocalService!1!ClearedBy!element],
        [LocalService!1!Branch!element],
        [LocalService!1!DispatchStatus!element],
        [LocalService!1!ContainerizationStatus!element],
        [LocalService!1!VoidedBy!element],
        [LocalService!1!VoidedDate!element],
        [LocalService!1!CartonCountSource!element],
        [LocalService!1!CancelledBy!element],
        [LocalService!1!CancelledDate!element],
        [LocalService!1!OperationalPlan!element],
        [LocalService!1!CrewReportTime!element],
        [LocalService!1!ReportLocation!element],
        [LocalService!1!IsPlaceHolder!element],
        [LocalService!1!ServiceInstructions!element],

        -- OriginLocation (Tag 2)
        [OriginLocation!2!!hide],
        [OriginLocation!2!Name!element],
        [OriginLocation!2!Contact!element],
        [OriginLocation!2!Address1!element],
        [OriginLocation!2!Address2!element],
        [OriginLocation!2!Address3!element],
        [OriginLocation!2!City!element],
        [OriginLocation!2!State!element],
        [OriginLocation!2!PostalCode!element],
        [OriginLocation!2!Country!element],

        -- DestinationLocation (Tag 3)
        [DestinationLocation!3!!hide],
        [DestinationLocation!3!Name!element],
        [DestinationLocation!3!Contact!element],
        [DestinationLocation!3!Address1!element],
        [DestinationLocation!3!Address2!element],
        [DestinationLocation!3!Address3!element],
        [DestinationLocation!3!City!element],
        [DestinationLocation!3!State!element],
        [DestinationLocation!3!PostalCode!element],
        [DestinationLocation!3!Country!element],

        -- LaborRequests (Tag 4) and items (Tag 5)
        [LaborRequests!4!!hide],
        [LaborRequest!5!!hide],
        [LaborRequest!5!LaborType!element],
        [LaborRequest!5!Quantity!element],

        -- EquipmentRequests (Tag 6) and items (Tag 7)
        [EquipmentRequests!6!!hide],
        [EquipmentRequest!7!!hide],
        [EquipmentRequest!7!EquipmentType!element],
        [EquipmentRequest!7!Quantity!element],

        -- Materials (Tag 8) and items (Tag 9)
        [Materials!8!!hide],
        [Material!9!!hide],
        [Material!9!Material!element],
        [Material!9!CPUType!element],
        [Material!9!EstimatedQuantity!element]

    from
    (
        select
            -- Root element
            Tag    = 1,
            Parent = null,
            [LocalService!1!!hide]                             = LocalServices.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = LocalServices.ServiceDate,
            [LocalService!1!RequestedStartTime!element]        = LocalServices.RequestedStartTime,
            [LocalService!1!RequestedEndTime!element]          = LocalServices.RequestedEndTime,
            [LocalService!1!WorkTicketNumber!element]          = LocalServices.WorkTicketNumber,
            [LocalService!1!ServiceType!element]               = LocalServices.ServiceType,
            [LocalService!1!JobStart!element]                  = LocalServices.JobStart,
            [LocalService!1!JobEnd!element]                    = LocalServices.JobEnd,
            [LocalService!1!CrewHours!element]                 = LocalServices.CrewHours,
            [LocalService!1!HoursPerMan!element]               = LocalServices.HoursPerMan,
            [LocalService!1!Status!element]                    = LocalServices.[Status],
            [LocalService!1!HoursDriveOneWay!element]          = LocalServices.HoursDriveOneWay,
            [LocalService!1!Quantity!element]                  = LocalServices.Quantity,
            [LocalService!1!Crew!element]                      = LocalServices.Crew,
            [LocalService!1!OriginDestination!element]         = LocalServices.OriginDestination,
            [LocalService!1!CreatedDate!element]               = LocalServices.CreatedDate,
            [LocalService!1!CreatedBy!element]                 = LocalServices.CreatedBy,
            [LocalService!1!LastEditedDate!element]            = LocalServices.LastEditedDate,
            [LocalService!1!LastEditedBy!element]              = LocalServices.LastEditedBy,
            [LocalService!1!SubmittedDate!element]             = LocalServices.SubmittedDate,
            [LocalService!1!SubmittedBy!element]               = LocalServices.SubmittedBy,
            [LocalService!1!ClearedDate!element]               = LocalServices.ClearedDate,
            [LocalService!1!ClearedBy!element]                 = LocalServices.ClearedBy,
            [LocalService!1!Branch!element]                    = LocalServices.Branch,
            [LocalService!1!DispatchStatus!element]            = LocalServices.DispatchStatus,
            [LocalService!1!ContainerizationStatus!element]    = LocalServices.ContainerizationStatus,
            [LocalService!1!VoidedBy!element]                  = LocalServices.VoidedBy,
            [LocalService!1!VoidedDate!element]                = LocalServices.VoidedDate,
            [LocalService!1!CartonCountSource!element]         = LocalServices.CartonCountSource,
            [LocalService!1!CancelledBy!element]               = LocalServices.CancelledBy,
            [LocalService!1!CancelledDate!element]             = LocalServices.CancelledDate,
            [LocalService!1!OperationalPlan!element]           = LocalServices.OperationalPlan,
            [LocalService!1!CrewReportTime!element]            = LocalServices.CrewReportTime,
            [LocalService!1!ReportLocation!element]            = LocalServices.ReportLocation,
            [LocalService!1!IsPlaceHolder!element]             = LocalServices.IsPlaceHolder,
            [LocalService!1!ServiceInstructions!element]       = LocalServices.ServiceInstructions,
            [OriginLocation!2!!hide]                           = null,
            [OriginLocation!2!Name!element]                    = null,
            [OriginLocation!2!Contact!element]                 = null,
            [OriginLocation!2!Address1!element]                = null,
            [OriginLocation!2!Address2!element]                = null,
            [OriginLocation!2!Address3!element]                = null,
            [OriginLocation!2!City!element]                    = null,
            [OriginLocation!2!State!element]                   = null,
            [OriginLocation!2!PostalCode!element]              = null,
            [OriginLocation!2!Country!element]                 = null,
            [DestinationLocation!3!!hide]                      = null,
            [DestinationLocation!3!Name!element]               = null,
            [DestinationLocation!3!Contact!element]            = null,
            [DestinationLocation!3!Address1!element]           = null,
            [DestinationLocation!3!Address2!element]           = null,
            [DestinationLocation!3!Address3!element]           = null,
            [DestinationLocation!3!City!element]               = null,
            [DestinationLocation!3!State!element]              = null,
            [DestinationLocation!3!PostalCode!element]         = null,
            [DestinationLocation!3!Country!element]            = null,
            [LaborRequests!4!!hide]                            = null,
            [LaborRequest!5!!hide]                             = null,
            [LaborRequest!5!LaborType!element]                 = null,
            [LaborRequest!5!Quantity!element]                  = null,
            [EquipmentRequests!6!!hide]                        = null,
            [EquipmentRequest!7!!hide]                         = null,
            [EquipmentRequest!7!EquipmentType!element]         = null,
            [EquipmentRequest!7!Quantity!element]              = null,
            [Materials!8!!hide]                                = null,
            [Material!9!!hide]                                 = null,
            [Material!9!Material!element]                      = null,
            [Material!9!CPUType!element]                       = null,
            [Material!9!EstimatedQuantity!element]             = null
        from LocalServices

        union all
        -- OriginLocations
        select
            Tag    = 2,
            Parent = 1,
            [LocalService!1!!hide]                             = LocalServices.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = null,
            [LocalService!1!RequestedStartTime!element]        = null,
            [LocalService!1!RequestedEndTime!element]          = null,
            [LocalService!1!WorkTicketNumber!element]          = null,
            [LocalService!1!ServiceType!element]               = null,
            [LocalService!1!JobStart!element]                  = null,
            [LocalService!1!JobEnd!element]                    = null,
            [LocalService!1!CrewHours!element]                 = null,
            [LocalService!1!HoursPerMan!element]               = null,
            [LocalService!1!Status!element]                    = null,
            [LocalService!1!HoursDriveOneWay!element]          = null,
            [LocalService!1!Quantity!element]                  = null,
            [LocalService!1!Crew!element]                      = null,
            [LocalService!1!OriginDestination!element]         = null,
            [LocalService!1!CreatedDate!element]               = null,
            [LocalService!1!CreatedBy!element]                 = null,
            [LocalService!1!LastEditedDate!element]            = null,
            [LocalService!1!LastEditedBy!element]              = null,
            [LocalService!1!SubmittedDate!element]             = null,
            [LocalService!1!SubmittedBy!element]               = null,
            [LocalService!1!ClearedDate!element]               = null,
            [LocalService!1!ClearedBy!element]                 = null,
            [LocalService!1!Branch!element]                    = null,
            [LocalService!1!DispatchStatus!element]            = null,
            [LocalService!1!ContainerizationStatus!element]    = null,
            [LocalService!1!VoidedBy!element]                  = null,
            [LocalService!1!VoidedDate!element]                = null,
            [LocalService!1!CartonCountSource!element]         = null,
            [LocalService!1!CancelledBy!element]               = null,
            [LocalService!1!CancelledDate!element]             = null,
            [LocalService!1!OperationalPlan!element]           = null,
            [LocalService!1!CrewReportTime!element]            = null,
            [LocalService!1!ReportLocation!element]            = null,
            [LocalService!1!IsPlaceHolder!element]             = null,
            [LocalService!1!ServiceInstructions!element]       = null,
            [OriginLocation!2!!hide]                           = LocalServices.LocalServiceId,
            [OriginLocation!2!Name!element]                    = LocalServices.OriginLocationName,
            [OriginLocation!2!Contact!element]                 = LocalServices.OriginLocationContact,
            [OriginLocation!2!Address1!element]                = LocalServices.OriginLocationAddress1,
            [OriginLocation!2!Address2!element]                = LocalServices.OriginLocationAddress2,
            [OriginLocation!2!Address3!element]                = LocalServices.OriginLocationAddress3,
            [OriginLocation!2!City!element]                    = LocalServices.OriginLocationCity,
            [OriginLocation!2!State!element]                   = LocalServices.OriginLocationState,
            [OriginLocation!2!PostalCode!element]              = LocalServices.OriginLocationPostalCode,
            [OriginLocation!2!Country!element]                 = LocalServices.OriginLocationCountry,
            [DestinationLocation!3!!hide]                      = null,
            [DestinationLocation!3!Name!element]               = null,
            [DestinationLocation!3!Contact!element]            = null,
            [DestinationLocation!3!Address1!element]           = null,
            [DestinationLocation!3!Address2!element]           = null,
            [DestinationLocation!3!Address3!element]           = null,
            [DestinationLocation!3!City!element]               = null,
            [DestinationLocation!3!State!element]              = null,
            [DestinationLocation!3!PostalCode!element]         = null,
            [DestinationLocation!3!Country!element]            = null,
            [LaborRequests!4!!hide]                            = null,
            [LaborRequest!5!!hide]                             = null,
            [LaborRequest!5!LaborType!element]                 = null,
            [LaborRequest!5!Quantity!element]                  = null,
            [EquipmentRequests!6!!hide]                        = null,
            [EquipmentRequest!7!!hide]                         = null,
            [EquipmentRequest!7!EquipmentType!element]         = null,
            [EquipmentRequest!7!Quantity!element]              = null,
            [Materials!8!!hide]                                = null,
            [Material!9!!hide]                                 = null,
            [Material!9!Material!element]                      = null,
            [Material!9!CPUType!element]                       = null,
            [Material!9!EstimatedQuantity!element]             = null
        from LocalServices
        --group by OriginLocations.LocalServiceId  -- one wrapper row per LocalServiceId

        union all
        -- DestinationLocations
        select
            Tag    = 3,
            Parent = 1,
            [LocalService!1!!hide]                             = LocalServices.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = null,
            [LocalService!1!RequestedStartTime!element]        = null,
            [LocalService!1!RequestedEndTime!element]          = null,
            [LocalService!1!WorkTicketNumber!element]          = null,
            [LocalService!1!ServiceType!element]               = null,
            [LocalService!1!JobStart!element]                  = null,
            [LocalService!1!JobEnd!element]                    = null,
            [LocalService!1!CrewHours!element]                 = null,
            [LocalService!1!HoursPerMan!element]               = null,
            [LocalService!1!Status!element]                    = null,
            [LocalService!1!HoursDriveOneWay!element]          = null,
            [LocalService!1!Quantity!element]                  = null,
            [LocalService!1!Crew!element]                      = null,
            [LocalService!1!OriginDestination!element]         = null,
            [LocalService!1!CreatedDate!element]               = null,
            [LocalService!1!CreatedBy!element]                 = null,
            [LocalService!1!LastEditedDate!element]            = null,
            [LocalService!1!LastEditedBy!element]              = null,
            [LocalService!1!SubmittedDate!element]             = null,
            [LocalService!1!SubmittedBy!element]               = null,
            [LocalService!1!ClearedDate!element]               = null,
            [LocalService!1!ClearedBy!element]                 = null,
            [LocalService!1!Branch!element]                    = null,
            [LocalService!1!DispatchStatus!element]            = null,
            [LocalService!1!ContainerizationStatus!element]    = null,
            [LocalService!1!VoidedBy!element]                  = null,
            [LocalService!1!VoidedDate!element]                = null,
            [LocalService!1!CartonCountSource!element]         = null,
            [LocalService!1!CancelledBy!element]               = null,
            [LocalService!1!CancelledDate!element]             = null,
            [LocalService!1!OperationalPlan!element]           = null,
            [LocalService!1!CrewReportTime!element]            = null,
            [LocalService!1!ReportLocation!element]            = null,
            [LocalService!1!IsPlaceHolder!element]             = null,
            [LocalService!1!ServiceInstructions!element]       = null,
            [OriginLocation!2!!hide]                           = null,
            [OriginLocation!2!Name!element]                    = null,
            [OriginLocation!2!Contact!element]                 = null,
            [OriginLocation!2!Address1!element]                = null,
            [OriginLocation!2!Address2!element]                = null,
            [OriginLocation!2!Address3!element]                = null,
            [OriginLocation!2!City!element]                    = null,
            [OriginLocation!2!State!element]                   = null,
            [OriginLocation!2!PostalCode!element]              = null,
            [OriginLocation!2!Country!element]                 = null,
            [DestinationLocation!3!!hide]                      = LocalServices.LocalServiceId,
            [DestinationLocation!3!Name!element]               = LocalServices.DestinationLocationName,
            [DestinationLocation!3!Contact!element]            = LocalServices.DestinationLocationContact,
            [DestinationLocation!3!Address1!element]           = LocalServices.DestinationLocationAddress1,
            [DestinationLocation!3!Address2!element]           = LocalServices.DestinationLocationAddress2,
            [DestinationLocation!3!Address3!element]           = LocalServices.DestinationLocationAddress3,
            [DestinationLocation!3!City!element]               = LocalServices.DestinationLocationCity,
            [DestinationLocation!3!State!element]              = LocalServices.DestinationLocationState,
            [DestinationLocation!3!PostalCode!element]         = LocalServices.DestinationLocationPostalCode,
            [DestinationLocation!3!Country!element]            = LocalServices.DestinationLocationCountry,
            [LaborRequests!4!!hide]                            = null,
            [LaborRequest!5!!hide]                             = null,
            [LaborRequest!5!LaborType!element]                 = null,
            [LaborRequest!5!Quantity!element]                  = null,
            [EquipmentRequests!6!!hide]                        = null,
            [EquipmentRequest!7!!hide]                         = null,
            [EquipmentRequest!7!EquipmentType!element]         = null,
            [EquipmentRequest!7!Quantity!element]              = null,
            [Materials!8!!hide]                                = null,
            [Material!9!!hide]                                 = null,
            [Material!9!Material!element]                      = null,
            [Material!9!CPUType!element]                       = null,
            [Material!9!EstimatedQuantity!element]             = null
        from LocalServices
        --group by DestinationLocations.LocalServiceId  -- one wrapper row per LocalServiceId

        union all
        -- LaborRequests wrapper
        select
            Tag    = 4,
            Parent = 1,
            [LocalService!1!!hide]                             = LaborRequests.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = null,
            [LocalService!1!RequestedStartTime!element]        = null,
            [LocalService!1!RequestedEndTime!element]          = null,
            [LocalService!1!WorkTicketNumber!element]          = null,
            [LocalService!1!ServiceType!element]               = null,
            [LocalService!1!JobStart!element]                  = null,
            [LocalService!1!JobEnd!element]                    = null,
            [LocalService!1!CrewHours!element]                 = null,
            [LocalService!1!HoursPerMan!element]               = null,
            [LocalService!1!Status!element]                    = null,
            [LocalService!1!HoursDriveOneWay!element]          = null,
            [LocalService!1!Quantity!element]                  = null,
            [LocalService!1!Crew!element]                      = null,
            [LocalService!1!OriginDestination!element]         = null,
            [LocalService!1!CreatedDate!element]               = null,
            [LocalService!1!CreatedBy!element]                 = null,
            [LocalService!1!LastEditedDate!element]            = null,
            [LocalService!1!LastEditedBy!element]              = null,
            [LocalService!1!SubmittedDate!element]             = null,
            [LocalService!1!SubmittedBy!element]               = null,
            [LocalService!1!ClearedDate!element]               = null,
            [LocalService!1!ClearedBy!element]                 = null,
            [LocalService!1!Branch!element]                    = null,
            [LocalService!1!DispatchStatus!element]            = null,
            [LocalService!1!ContainerizationStatus!element]    = null,
            [LocalService!1!VoidedBy!element]                  = null,
            [LocalService!1!VoidedDate!element]                = null,
            [LocalService!1!CartonCountSource!element]         = null,
            [LocalService!1!CancelledBy!element]               = null,
            [LocalService!1!CancelledDate!element]             = null,
            [LocalService!1!OperationalPlan!element]           = null,
            [LocalService!1!CrewReportTime!element]            = null,
            [LocalService!1!ReportLocation!element]            = null,
            [LocalService!1!IsPlaceHolder!element]             = null,
            [LocalService!1!ServiceInstructions!element]       = null,
            [OriginLocation!2!!hide]                           = null,
            [OriginLocation!2!Name!element]                    = null,
            [OriginLocation!2!Contact!element]                 = null,
            [OriginLocation!2!Address1!element]                = null,
            [OriginLocation!2!Address2!element]                = null,
            [OriginLocation!2!Address3!element]                = null,
            [OriginLocation!2!City!element]                    = null,
            [OriginLocation!2!State!element]                   = null,
            [OriginLocation!2!PostalCode!element]              = null,
            [OriginLocation!2!Country!element]                 = null,
            [DestinationLocation!3!!hide]                      = null,
            [DestinationLocation!3!Name!element]               = null,
            [DestinationLocation!3!Contact!element]            = null,
            [DestinationLocation!3!Address1!element]           = null,
            [DestinationLocation!3!Address2!element]           = null,
            [DestinationLocation!3!Address3!element]           = null,
            [DestinationLocation!3!City!element]               = null,
            [DestinationLocation!3!State!element]              = null,
            [DestinationLocation!3!PostalCode!element]         = null,
            [DestinationLocation!3!Country!element]            = null,
            [LaborRequests!4!!hide]                            = 1,
            [LaborRequest!5!!hide]                             = null,
            [LaborRequest!5!LaborType!element]                 = null,
            [LaborRequest!5!Quantity!element]                  = null,
            [EquipmentRequests!6!!hide]                        = null,
            [EquipmentRequest!7!!hide]                         = null,
            [EquipmentRequest!7!EquipmentType!element]         = null,
            [EquipmentRequest!7!Quantity!element]              = null,
            [Materials!8!!hide]                                = null,
            [Material!9!!hide]                                 = null,
            [Material!9!Material!element]                      = null,
            [Material!9!CPUType!element]                       = null,
            [Material!9!EstimatedQuantity!element]             = null
        from LaborRequests
        group by LaborRequests.LocalServiceId  -- one wrapper row per LocalServiceId

        union all
        -- LaborRequest items
        select
            Tag    = 5,
            Parent = 4,
            [LocalService!1!!hide]                             = LaborRequests.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = null,
            [LocalService!1!RequestedStartTime!element]        = null,
            [LocalService!1!RequestedEndTime!element]          = null,
            [LocalService!1!WorkTicketNumber!element]          = null,
            [LocalService!1!ServiceType!element]               = null,
            [LocalService!1!JobStart!element]                  = null,
            [LocalService!1!JobEnd!element]                    = null,
            [LocalService!1!CrewHours!element]                 = null,
            [LocalService!1!HoursPerMan!element]               = null,
            [LocalService!1!Status!element]                    = null,
            [LocalService!1!HoursDriveOneWay!element]          = null,
            [LocalService!1!Quantity!element]                  = null,
            [LocalService!1!Crew!element]                      = null,
            [LocalService!1!OriginDestination!element]         = null,
            [LocalService!1!CreatedDate!element]               = null,
            [LocalService!1!CreatedBy!element]                 = null,
            [LocalService!1!LastEditedDate!element]            = null,
            [LocalService!1!LastEditedBy!element]              = null,
            [LocalService!1!SubmittedDate!element]             = null,
            [LocalService!1!SubmittedBy!element]               = null,
            [LocalService!1!ClearedDate!element]               = null,
            [LocalService!1!ClearedBy!element]                 = null,
            [LocalService!1!Branch!element]                    = null,
            [LocalService!1!DispatchStatus!element]            = null,
            [LocalService!1!ContainerizationStatus!element]    = null,
            [LocalService!1!VoidedBy!element]                  = null,
            [LocalService!1!VoidedDate!element]                = null,
            [LocalService!1!CartonCountSource!element]         = null,
            [LocalService!1!CancelledBy!element]               = null,
            [LocalService!1!CancelledDate!element]             = null,
            [LocalService!1!OperationalPlan!element]           = null,
            [LocalService!1!CrewReportTime!element]            = null,
            [LocalService!1!ReportLocation!element]            = null,
            [LocalService!1!IsPlaceHolder!element]             = null,
            [LocalService!1!ServiceInstructions!element]       = null,
            [OriginLocation!2!!hide]                           = null,
            [OriginLocation!2!Name!element]                    = null,
            [OriginLocation!2!Contact!element]                 = null,
            [OriginLocation!2!Address1!element]                = null,
            [OriginLocation!2!Address2!element]                = null,
            [OriginLocation!2!Address3!element]                = null,
            [OriginLocation!2!City!element]                    = null,
            [OriginLocation!2!State!element]                   = null,
            [OriginLocation!2!PostalCode!element]              = null,
            [OriginLocation!2!Country!element]                 = null,
            [DestinationLocation!3!!hide]                      = null,
            [DestinationLocation!3!Name!element]               = null,
            [DestinationLocation!3!Contact!element]            = null,
            [DestinationLocation!3!Address1!element]           = null,
            [DestinationLocation!3!Address2!element]           = null,
            [DestinationLocation!3!Address3!element]           = null,
            [DestinationLocation!3!City!element]               = null,
            [DestinationLocation!3!State!element]              = null,
            [DestinationLocation!3!PostalCode!element]         = null,
            [DestinationLocation!3!Country!element]            = null,
            [LaborRequests!4!!hide]                            = 1,
            [LaborRequest!5!!hide]                             = LaborRequests.LocalServiceLaborRequestID,
            [LaborRequest!5!LaborType!element]                 = LaborRequests.LaborType,
            [LaborRequest!5!Quantity!element]                  = LaborRequests.Quantity,
            [EquipmentRequests!6!!hide]                        = null,
            [EquipmentRequest!7!!hide]                         = null,
            [EquipmentRequest!7!EquipmentType!element]         = null,
            [EquipmentRequest!7!Quantity!element]              = null,
            [Materials!8!!hide]                                = null,
            [Material!9!!hide]                                 = null,
            [Material!9!Material!element]                      = null,
            [Material!9!CPUType!element]                       = null,
            [Material!9!EstimatedQuantity!element]             = null
        from LaborRequests

        union all
        -- EquipmentRequests wrapper
        select
            Tag    = 6,
            Parent = 1,
            [LocalService!1!!hide]                             = EquipmentRequests.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = null,
            [LocalService!1!RequestedStartTime!element]        = null,
            [LocalService!1!RequestedEndTime!element]          = null,
            [LocalService!1!WorkTicketNumber!element]          = null,
            [LocalService!1!ServiceType!element]               = null,
            [LocalService!1!JobStart!element]                  = null,
            [LocalService!1!JobEnd!element]                    = null,
            [LocalService!1!CrewHours!element]                 = null,
            [LocalService!1!HoursPerMan!element]               = null,
            [LocalService!1!Status!element]                    = null,
            [LocalService!1!HoursDriveOneWay!element]          = null,
            [LocalService!1!Quantity!element]                  = null,
            [LocalService!1!Crew!element]                      = null,
            [LocalService!1!OriginDestination!element]         = null,
            [LocalService!1!CreatedDate!element]               = null,
            [LocalService!1!CreatedBy!element]                 = null,
            [LocalService!1!LastEditedDate!element]            = null,
            [LocalService!1!LastEditedBy!element]              = null,
            [LocalService!1!SubmittedDate!element]             = null,
            [LocalService!1!SubmittedBy!element]               = null,
            [LocalService!1!ClearedDate!element]               = null,
            [LocalService!1!ClearedBy!element]                 = null,
            [LocalService!1!Branch!element]                    = null,
            [LocalService!1!DispatchStatus!element]            = null,
            [LocalService!1!ContainerizationStatus!element]    = null,
            [LocalService!1!VoidedBy!element]                  = null,
            [LocalService!1!VoidedDate!element]                = null,
            [LocalService!1!CartonCountSource!element]         = null,
            [LocalService!1!CancelledBy!element]               = null,
            [LocalService!1!CancelledDate!element]             = null,
            [LocalService!1!OperationalPlan!element]           = null,
            [LocalService!1!CrewReportTime!element]            = null,
            [LocalService!1!ReportLocation!element]            = null,
            [LocalService!1!IsPlaceHolder!element]             = null,
            [LocalService!1!ServiceInstructions!element]       = null,
            [OriginLocation!2!!hide]                           = null,
            [OriginLocation!2!Name!element]                    = null,
            [OriginLocation!2!Contact!element]                 = null,
            [OriginLocation!2!Address1!element]                = null,
            [OriginLocation!2!Address2!element]                = null,
            [OriginLocation!2!Address3!element]                = null,
            [OriginLocation!2!City!element]                    = null,
            [OriginLocation!2!State!element]                   = null,
            [OriginLocation!2!PostalCode!element]              = null,
            [OriginLocation!2!Country!element]                 = null,
            [DestinationLocation!3!!hide]                      = null,
            [DestinationLocation!3!Name!element]               = null,
            [DestinationLocation!3!Contact!element]            = null,
            [DestinationLocation!3!Address1!element]           = null,
            [DestinationLocation!3!Address2!element]           = null,
            [DestinationLocation!3!Address3!element]           = null,
            [DestinationLocation!3!City!element]               = null,
            [DestinationLocation!3!State!element]              = null,
            [DestinationLocation!3!PostalCode!element]         = null,
            [DestinationLocation!3!Country!element]            = null,
            [LaborRequests!4!!hide]                            = null,
            [LaborRequest!5!!hide]                             = null,
            [LaborRequest!5!LaborType!element]                 = null,
            [LaborRequest!5!Quantity!element]                  = null,
            [EquipmentRequests!6!!hide]                        = 1,
            [EquipmentRequest!7!!hide]                         = null,
            [EquipmentRequest!7!EquipmentType!element]         = null,
            [EquipmentRequest!7!Quantity!element]              = null,
            [Materials!8!!hide]                                = null,
            [Material!9!!hide]                                 = null,
            [Material!9!Material!element]                      = null,
            [Material!9!CPUType!element]                       = null,
            [Material!9!EstimatedQuantity!element]             = null
        from EquipmentRequests
        group by EquipmentRequests.LocalServiceId

        union all
        -- EquipmentRequest items
        select
            Tag    = 7,
            Parent = 6,
            [LocalService!1!!hide]                             = EquipmentRequests.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = null,
            [LocalService!1!RequestedStartTime!element]        = null,
            [LocalService!1!RequestedEndTime!element]          = null,
            [LocalService!1!WorkTicketNumber!element]          = null,
            [LocalService!1!ServiceType!element]               = null,
            [LocalService!1!JobStart!element]                  = null,
            [LocalService!1!JobEnd!element]                    = null,
            [LocalService!1!CrewHours!element]                 = null,
            [LocalService!1!HoursPerMan!element]               = null,
            [LocalService!1!Status!element]                    = null,
            [LocalService!1!HoursDriveOneWay!element]          = null,
            [LocalService!1!Quantity!element]                  = null,
            [LocalService!1!Crew!element]                      = null,
            [LocalService!1!OriginDestination!element]         = null,
            [LocalService!1!CreatedDate!element]               = null,
            [LocalService!1!CreatedBy!element]                 = null,
            [LocalService!1!LastEditedDate!element]            = null,
            [LocalService!1!LastEditedBy!element]              = null,
            [LocalService!1!SubmittedDate!element]             = null,
            [LocalService!1!SubmittedBy!element]               = null,
            [LocalService!1!ClearedDate!element]               = null,
            [LocalService!1!ClearedBy!element]                 = null,
            [LocalService!1!Branch!element]                    = null,
            [LocalService!1!DispatchStatus!element]            = null,
            [LocalService!1!ContainerizationStatus!element]    = null,
            [LocalService!1!VoidedBy!element]                  = null,
            [LocalService!1!VoidedDate!element]                = null,
            [LocalService!1!CartonCountSource!element]         = null,
            [LocalService!1!CancelledBy!element]               = null,
            [LocalService!1!CancelledDate!element]             = null,
            [LocalService!1!OperationalPlan!element]           = null,
            [LocalService!1!CrewReportTime!element]            = null,
            [LocalService!1!ReportLocation!element]            = null,
            [LocalService!1!IsPlaceHolder!element]             = null,
            [LocalService!1!ServiceInstructions!element]       = null,
            [OriginLocation!2!!hide]                           = null,
            [OriginLocation!2!Name!element]                    = null,
            [OriginLocation!2!Contact!element]                 = null,
            [OriginLocation!2!Address1!element]                = null,
            [OriginLocation!2!Address2!element]                = null,
            [OriginLocation!2!Address3!element]                = null,
            [OriginLocation!2!City!element]                    = null,
            [OriginLocation!2!State!element]                   = null,
            [OriginLocation!2!PostalCode!element]              = null,
            [OriginLocation!2!Country!element]                 = null,
            [DestinationLocation!3!!hide]                      = null,
            [DestinationLocation!3!Name!element]               = null,
            [DestinationLocation!3!Contact!element]            = null,
            [DestinationLocation!3!Address1!element]           = null,
            [DestinationLocation!3!Address2!element]           = null,
            [DestinationLocation!3!Address3!element]           = null,
            [DestinationLocation!3!City!element]               = null,
            [DestinationLocation!3!State!element]              = null,
            [DestinationLocation!3!PostalCode!element]         = null,
            [DestinationLocation!3!Country!element]            = null,
            [LaborRequests!4!!hide]                            = null,
            [LaborRequest!5!!hide]                             = null,
            [LaborRequest!5!LaborType!element]                 = null,
            [LaborRequest!5!Quantity!element]                  = null,
            [EquipmentRequests!6!!hide]                        = 1,
            [EquipmentRequest!7!!hide]                         = EquipmentRequests.LocalServiceEquipmentRequestID,
            [EquipmentRequest!7!EquipmentType!element]         = EquipmentRequests.EquipmentType,
            [EquipmentRequest!7!Quantity!element]              = EquipmentRequests.Quantity,
            [Materials!8!!hide]                                = null,
            [Material!9!!hide]                                 = null,
            [Material!9!Material!element]                      = null,
            [Material!9!CPUType!element]                       = null,
            [Material!9!EstimatedQuantity!element]             = null
        from EquipmentRequests

        union all
        -- Materials wrapper
        select
            Tag    = 8,
            Parent = 1,
            [LocalService!1!!hide]                             = Materials.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = null,
            [LocalService!1!RequestedStartTime!element]        = null,
            [LocalService!1!RequestedEndTime!element]          = null,
            [LocalService!1!WorkTicketNumber!element]          = null,
            [LocalService!1!ServiceType!element]               = null,
            [LocalService!1!JobStart!element]                  = null,
            [LocalService!1!JobEnd!element]                    = null,
            [LocalService!1!CrewHours!element]                 = null,
            [LocalService!1!HoursPerMan!element]               = null,
            [LocalService!1!Status!element]                    = null,
            [LocalService!1!HoursDriveOneWay!element]          = null,
            [LocalService!1!Quantity!element]                  = null,
            [LocalService!1!Crew!element]                      = null,
            [LocalService!1!OriginDestination!element]         = null,
            [LocalService!1!CreatedDate!element]               = null,
            [LocalService!1!CreatedBy!element]                 = null,
            [LocalService!1!LastEditedDate!element]            = null,
            [LocalService!1!LastEditedBy!element]              = null,
            [LocalService!1!SubmittedDate!element]             = null,
            [LocalService!1!SubmittedBy!element]               = null,
            [LocalService!1!ClearedDate!element]               = null,
            [LocalService!1!ClearedBy!element]                 = null,
            [LocalService!1!Branch!element]                    = null,
            [LocalService!1!DispatchStatus!element]            = null,
            [LocalService!1!ContainerizationStatus!element]    = null,
            [LocalService!1!VoidedBy!element]                  = null,
            [LocalService!1!VoidedDate!element]                = null,
            [LocalService!1!CartonCountSource!element]         = null,
            [LocalService!1!CancelledBy!element]               = null,
            [LocalService!1!CancelledDate!element]             = null,
            [LocalService!1!OperationalPlan!element]           = null,
            [LocalService!1!CrewReportTime!element]            = null,
            [LocalService!1!ReportLocation!element]            = null,
            [LocalService!1!IsPlaceHolder!element]             = null,
            [LocalService!1!ServiceInstructions!element]       = null,
            [OriginLocation!2!!hide]                           = null,
            [OriginLocation!2!Name!element]                    = null,
            [OriginLocation!2!Contact!element]                 = null,
            [OriginLocation!2!Address1!element]                = null,
            [OriginLocation!2!Address2!element]                = null,
            [OriginLocation!2!Address3!element]                = null,
            [OriginLocation!2!City!element]                    = null,
            [OriginLocation!2!State!element]                   = null,
            [OriginLocation!2!PostalCode!element]              = null,
            [OriginLocation!2!Country!element]                 = null,
            [DestinationLocation!3!!hide]                      = null,
            [DestinationLocation!3!Name!element]               = null,
            [DestinationLocation!3!Contact!element]            = null,
            [DestinationLocation!3!Address1!element]           = null,
            [DestinationLocation!3!Address2!element]           = null,
            [DestinationLocation!3!Address3!element]           = null,
            [DestinationLocation!3!City!element]               = null,
            [DestinationLocation!3!State!element]              = null,
            [DestinationLocation!3!PostalCode!element]         = null,
            [DestinationLocation!3!Country!element]            = null,
            [LaborRequests!4!!hide]                            = null,
            [LaborRequest!5!!hide]                             = null,
            [LaborRequest!5!LaborType!element]                 = null,
            [LaborRequest!5!Quantity!element]                  = null,
            [EquipmentRequests!6!!hide]                        = null,
            [EquipmentRequest!7!!hide]                         = null,
            [EquipmentRequest!7!EquipmentType!element]         = null,
            [EquipmentRequest!7!Quantity!element]              = null,
            [Materials!8!!hide]                                = 1,
            [Material!9!!hide]                                 = null,
            [Material!9!Material!element]                      = null,
            [Material!9!CPUType!element]                       = null,
            [Material!9!EstimatedQuantity!element]             = null
        from Materials
        group by Materials.LocalServiceId

        union all
        -- Material items
        select
            Tag    = 9,
            Parent = 8,
            [LocalService!1!!hide]                             = Materials.LocalServiceId,
            [LocalService!1!ServiceDate!element]               = null,
            [LocalService!1!RequestedStartTime!element]        = null,
            [LocalService!1!RequestedEndTime!element]          = null,
            [LocalService!1!WorkTicketNumber!element]          = null,
            [LocalService!1!ServiceType!element]               = null,
            [LocalService!1!JobStart!element]                  = null,
            [LocalService!1!JobEnd!element]                    = null,
            [LocalService!1!CrewHours!element]                 = null,
            [LocalService!1!HoursPerMan!element]               = null,
            [LocalService!1!Status!element]                    = null,
            [LocalService!1!HoursDriveOneWay!element]          = null,
            [LocalService!1!Quantity!element]                  = null,
            [LocalService!1!Crew!element]                      = null,
            [LocalService!1!OriginDestination!element]         = null,
            [LocalService!1!CreatedDate!element]               = null,
            [LocalService!1!CreatedBy!element]                 = null,
            [LocalService!1!LastEditedDate!element]            = null,
            [LocalService!1!LastEditedBy!element]              = null,
            [LocalService!1!SubmittedDate!element]             = null,
            [LocalService!1!SubmittedBy!element]               = null,
            [LocalService!1!ClearedDate!element]               = null,
            [LocalService!1!ClearedBy!element]                 = null,
            [LocalService!1!Branch!element]                    = null,
            [LocalService!1!DispatchStatus!element]            = null,
            [LocalService!1!ContainerizationStatus!element]    = null,
            [LocalService!1!VoidedBy!element]                  = null,
            [LocalService!1!VoidedDate!element]                = null,
            [LocalService!1!CartonCountSource!element]         = null,
            [LocalService!1!CancelledBy!element]               = null,
            [LocalService!1!CancelledDate!element]             = null,
            [LocalService!1!OperationalPlan!element]           = null,
            [LocalService!1!CrewReportTime!element]            = null,
            [LocalService!1!ReportLocation!element]            = null,
            [LocalService!1!IsPlaceHolder!element]             = null,
            [LocalService!1!ServiceInstructions!element]       = null,
            [OriginLocation!2!!hide]                           = null,
            [OriginLocation!2!Name!element]                    = null,
            [OriginLocation!2!Contact!element]                 = null,
            [OriginLocation!2!Address1!element]                = null,
            [OriginLocation!2!Address2!element]                = null,
            [OriginLocation!2!Address3!element]                = null,
            [OriginLocation!2!City!element]                    = null,
            [OriginLocation!2!State!element]                   = null,
            [OriginLocation!2!PostalCode!element]              = null,
            [OriginLocation!2!Country!element]                 = null,
            [DestinationLocation!3!!hide]                      = null,
            [DestinationLocation!3!Name!element]               = null,
            [DestinationLocation!3!Contact!element]            = null,
            [DestinationLocation!3!Address1!element]           = null,
            [DestinationLocation!3!Address2!element]           = null,
            [DestinationLocation!3!Address3!element]           = null,
            [DestinationLocation!3!City!element]               = null,
            [DestinationLocation!3!State!element]              = null,
            [DestinationLocation!3!PostalCode!element]         = null,
            [DestinationLocation!3!Country!element]            = null,
            [LaborRequests!4!!hide]                            = null,
            [LaborRequest!5!!hide]                             = null,
            [LaborRequest!5!LaborType!element]                 = null,
            [LaborRequest!5!Quantity!element]                  = null,
            [EquipmentRequests!6!!hide]                        = null,
            [EquipmentRequest!7!!hide]                         = null,
            [EquipmentRequest!7!EquipmentType!element]         = null,
            [EquipmentRequest!7!Quantity!element]              = null,
            [Materials!8!!hide]                                = 1,
            [Material!9!!hide]                                 = Materials.SortOrder,
            [Material!9!Material!element]                      = Materials.Material,
            [Material!9!CPUType!element]                       = Materials.CPUType,
            [Material!9!EstimatedQuantity!element]             = Materials.EstimatedQuantity
        from Materials
    ) as FinalXml
    order by
        [LocalService!1!!hide],   -- group local service rows together
        Tag,
        [OriginLocation!2!!hide],
        [DestinationLocation!3!!hide],
        [LaborRequest!5!!hide],
        [EquipmentRequest!7!!hide],
        [Material!9!!hide]
    for xml explicit;

    set @outStatusCode = @STATUS_CODE_PROCESSED;
end
GO
GRANT EXECUTE ON  [dbo].[XmlExportLocalServicesInformation] TO [MssExec]
GO