/**
*    
*    Imports Office & Industrial info, Contacts, Locations and Local Services from XML.
* NOTE: This process is INSERT-ONLY. Office & Industrial data is only pushed to MoversSuite once.
*
*    Error Codes:
*    
*    @param @inXmlText            The MSS import format XML containing the external interfaces and Order Id.
*    @param @outErrorCode        The error code which indicates any errors that happened in the session call.
*    @param @outStatusCode        The status code which indicates what happened in the session call.
*/

CREATE procedure [dbo].[XmlImportProcessOfficeIndustrial]
    @inXmlText xml,
    @inOrderID int,
    @outErrorCode int output,
    @outStatusCode int output
as
set nocount on

-- define our constant error codes that will be used
declare @ERRORCODE_ORDER_NOT_FOUND            int
set        @ERRORCODE_ORDER_NOT_FOUND            = 2040

-- Define status codes
declare @STATUSCODE_OFFICE_INDUSTRIAL_DATA_INSERTED int
set @STATUSCODE_OFFICE_INDUSTRIAL_DATA_INSERTED = 0
declare @STATUSCODE_NO_OFFICE_INDUSTRIAL_DATA int
set @STATUSCODE_NO_OFFICE_INDUSTRIAL_DATA = 1
declare @STATUSCODE_ERROR    int
set @STATUSCODE_ERROR     = 2

-- Initialiaze outputs
set @outErrorCode    = 0
set @outStatusCode    = @STATUSCODE_NO_OFFICE_INDUSTRIAL_DATA

-- Declare and initialize local variables used in this stored proc
declare @theErrorData    varchar(max)
set @theErrorData = ''
declare @theAppVendorName varchar(50)
declare @theMainAddressTypeId int
select @theMainAddressTypeId = AddressTypeID from AddressType where TypeName = 'Main'
declare @theDefaultCountryCode int
select @theDefaultCountryCode = PhoneCountryCode from CountryCodeStandard where CountryCodeStandardID = dbo.udfGetDefaultCountryCodeStandardID()
declare @theDefaultCountryCodeId int
select @theDefaultCountryCodeId = dbo.udfGetDefaultCountryCodeStandardID()
declare @theLocalServiceIdleStatus int = ( select PriKey from LSStatus where LSStatus.[Status] = 'Idle' )

-- The Xml must contain an OfficeIndustrial element with a Order parent element or there is nothing to do.
declare @theOIElementExists bit
;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
select @theOIElementExists = 1 from @inXmlText.nodes( 'MSS:Order/MSS:OfficeIndustrial' ) as OrderTable(OrderColumn)
if ( 1 = @theOIElementExists )
begin

    -- Make sure the specified order exists.
    if not exists( select 1 from Orders where PriKey = @inOrderID )
    begin
        set @outStatusCode = @STATUSCODE_ERROR
        set @outErrorCode = @ERRORCODE_ORDER_NOT_FOUND
    end
    else
    begin

        -- Only lookup @theAppVendorName once, if we get here
        if( @theAppVendorName is null )
        begin
            set @theAppVendorName = dbo.GetExtAppVendorFromMssXmlText( @inXmlText )
        end

        declare @theXmlInterfaceId int
        select
            @theXmlInterfaceId = XmlInterface.XmlInterfaceID
        from XmlInterface
        where XmlInterface.VendorName = @theAppVendorName

        -- Get the Sysuser ID of the project manager
        declare @theProjectManagerId int
        ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
        select @theProjectManagerId = SysUserFID
        from @inXmlText.nodes( 'MSS:Order/MSS:OfficeIndustrial' ) as XmlTable(OandI)
        inner join XmlUserMap on XmlUserMap.UserID = OandI.value( 'MSS:ProjectManager[1]', 'varchar(256)' )
        inner join XmlInterface on XmlInterface.XmlInterfaceID = XmlUserMap.XmlInterfaceFID
        inner join SysUser on SysUser.SysUserID = XmlUserMap.SysUserFID
        inner join [Status] on SysUser.[Status] = [Status].PriKey
        where
            XmlInterface.XmlInterfaceID = @theXmlInterfaceId and
            [Status].[Status] = 'ACTIVE'

        -- Get the External Application user prikey.
        declare    @theExternalApplicationSysUserId    int
        select @theExternalApplicationSysUserId = SysUserID
        from SysUser
        where FirstName = 'External'    and LastName = 'Application'

        -- Get the branch of the order.
        declare @theOrderBranchId int
        select @theOrderBranchId = BranchPriKey
        from Orders
        where Orders.PriKey = @inOrderID

        -- Insert the general Office & Industrial data
        ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
        insert into OfficeIndustrialOrder
        (
            OrderFID,
            ProjectManager,
            JobStartDate,
            JobEndDate,
            HeadCount,
            ClientNote,
            OperationalNote
        )
        select
            @inOrderID,
            @theProjectManagerId,
            OandI.value( 'MSS:JobStartDate[1]', 'datetime' ),
            OandI.value( 'MSS:JobEndDate[1]', 'datetime' ),
            OandI.value( 'MSS:HeadCount[1]', 'int' ),
            dbo.HandleCRLFInMemos( OandI.value( 'MSS:ClientNote[1]', 'varchar(max)' ), 0 ),
            dbo.HandleCRLFInMemos( OandI.value( 'MSS:OperationalNote[1]', 'varchar(max)' ), 0 )
        from @inXmlText.nodes( 'MSS:Order/MSS:OfficeIndustrial' ) as XmlTable(OandI)
        where not exists( select top 1 1 from OfficeIndustrialOrder where OrderFID = @inOrderID )

        if ( @@ROWCOUNT > 0 )
        begin
            set @outStatusCode = @STATUSCODE_OFFICE_INDUSTRIAL_DATA_INSERTED

            -- Mark this order so that it will default to opening via the O&I module.
            update Orders set
                OpenOrderInModule = 'Office & Industrial'
            where PriKey = @inOrderID

            -- If there are any Order Contacts in the XML, import those.
            declare @theOrderContactsExist bit
            ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
            select @theOrderContactsExist = 1 from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderContacts/MSS:OrderContact' ) as ContactTable(ContactColumn)
            if ( 1 = @theOrderContactsExist )
            begin
                declare @theContactIds table( OrderContactId int, ExternalID varchar(32) )
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                merge OrderContacts
                using
                (
                    select
                        Contact.value( 'MSS:ExternalID[1]', 'varchar(32)' ) as ExternalID,
                        Contact.value( 'MSS:ContactName[1]', 'varchar(30)' ) as Contact,
                        Contact.value( 'MSS:Title[1]', 'varchar(50)' ) as Title,
                        Contact.value( 'MSS:Email[1]', 'varchar(120)' ) as Email,
                        Contact.value( 'MSS:CompanyName[1]', 'varchar(100)' ) as [Name]
                    from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderContacts/MSS:OrderContact' ) as XmlTable(Contact)
                ) as [Source]
                on 1 = 0
                when not matched then
                insert
                (
                    OrderFID,
                    Contact,
                    Title,
                    Email,
                    [Name],
                    ImportedFlag
                )
                values
                (
                    @inOrderID,
                    [Source].Contact,
                    [Source].Title,
                    [Source].Email,
                    [Source].[Name],
                    1
                )
                output
                    inserted.OrderContactID,
                    [Source].ExternalID    
                into @theContactIds;

                -- Insert the address for the contact.
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into OrderContactAddress
                (
                    OrderContactFID,
                    AddressTypeFID,
                    Address1,
                    Address2,
                    Address3,
                    City,
                    [State],
                    PostalCode,
                    CountryCodeStandardFID
                )
                select
                    OrderContactFID = ContactIds.OrderContactId,
                    AddressTypeFID = @theMainAddressTypeId,
                    Address1 = ContactAddress.value( 'MSS:Address1[1]', 'Address' ),
                    Address2 = ContactAddress.value( 'MSS:Address2[1]', 'Address' ),
                    Address3 = ContactAddress.value( 'MSS:Address3[1]', 'Address' ),
                    City = ContactAddress.value( 'MSS:City[1]', 'AddressCity' ),
                    [State] = ContactAddress.value( 'MSS:State[1]', 'AddressState' ),
                    Postal = ContactAddress.value( 'MSS:Postal[1]', 'AddressPostalCode' ),
                    CountryCode = CountryCodeStandard.CountryCodeStandardID
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderContacts/MSS:OrderContact/MSS:OrderContactAddress' ) as XmlTable(ContactAddress)
                inner join @theContactIds as ContactIds on ContactIds.ExternalID = ContactAddress.value( '../MSS:ExternalID[1]', 'varchar(32)' )
                left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = ContactAddress.value( 'MSS:CountryCode[1]', 'varchar(3)' )
            
                -- Insert Order Contact Phone Numbers
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into OrderContactPhoneNumber
                (
                    OrderContactFID,
                    CountryCodeStandardFID,
                    AreaCode,
                    LocalNumber,
                    Extension,
                    OrderContactPhoneTypeFID
                )
                select
                    ContactIds.OrderContactId,
                    case
                        when CountryCodeStandard.CountryCodeStandardID is not null then CountryCodeStandard.CountryCodeStandardID
                        when ContactPhone.value( 'MSS:PhoneCountryCode[1]', 'int' ) = @theDefaultCountryCode then @theDefaultCountryCodeId
                        else ( select top 1 CountryCodeStandardID from CountryCodeStandard where CountryCodeStandard.PhoneCountryCode = ContactPhone.value( 'MSS:PhoneCountryCode[1]', 'int' ) )
                    end,
                    ContactPhone.value( 'MSS:AreaCode[1]', 'PhoneAreaCode' ),
                    ContactPhone.value( 'MSS:LocalNumber[1]', 'PhoneLocalNumber' ),
                    ContactPhone.value( 'MSS:Extension[1]', 'PhoneExtension' ),                
                    OrderContactPhoneType.OrderContactPhoneTypeID
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderContacts/MSS:OrderContact/MSS:OrderContactPhoneNumbers/MSS:OrderContactPhoneNumber' ) as XmlTable(ContactPhone)
                inner join @theContactIds as ContactIds on ContactIds.ExternalID = ContactPhone.value( '../../MSS:ExternalID[1]', 'varchar(32)' )
                inner join OrderContactPhoneType on OrderContactPhoneType.TypeName = ContactPhone.value( 'MSS:Type[1]', 'varchar(50)' )
                left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = ContactPhone.value( 'MSS:CountryCode[1]', 'varchar(3)' )
        
            -- end if ( 1 = @theOrderContactsExist )
            end

            -- If there are any Order Locations in the XML, import those.
            declare @theLocationIds table( OrderLocationId int, ExternalID varchar(32) )
            declare @theOrderLocationsExist bit
            ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
            select @theOrderLocationsExist = 1 from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderLocations/MSS:OrderLocation' ) as XmlTable(LocationColumn)
            if ( 1 = @theOrderLocationsExist )
            begin
            
                -- Insert Order Locations.
                -- This is done using a "merge" statement so that we can output a table that matches each location's external ID with the primary key of the inserted record.
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                merge OrderLocations
                using
                (
                    select
                        @inOrderID as OrderId,
                        [Location].value( 'MSS:ExternalID[1]', 'varchar(32)' ) as ExternalID,
                        [Location].value( 'MSS:Name[1]', 'varchar(100)' ) as [Location],
                        ContactIds.OrderContactId,
                        1 as Imported,
                        null as BuildingFid,
                        dbo.HandleCRLFInMemos( [Location].value( 'MSS:CrossSt[1]', 'varchar(1024)' ), 1024 ) as CrossStreet,
                        dbo.HandleCRLFInMemos( [Location].value( 'MSS:ServiceEntrance[1]', 'varchar(1024)' ), 1024 ) as ServiceEntrance,
                        dbo.HandleCRLFInMemos( [Location].value( 'MSS:SiteNote[1]', 'varchar(1024)' ), 1024 ) as SiteNote,
                        OrderLocationAddressType.OrderLocationAddressTypeID
                    from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderLocations/MSS:OrderLocation' ) as XmlTable([Location])
                    left outer join @theContactIds as ContactIds on ContactIds.ExternalID = [Location].value( 'MSS:ContactExternalID[1]', 'varchar(32)' )
                    left outer join OrderLocationAddressType on OrderLocationAddressType.TypeName = [Location].value( 'MSS:OrderLocationAddressType[1]', 'varchar(1024)' )
                ) as [Source]
                on 1 = 0
                when not matched then
                insert
                (
                    OrderFID,
                    [Name],
                    OrderContactFID,
                    ImportedFlag,
                    BuildingFID,
                    CrossStreet,
                    ServiceEntrance,
                    SiteNote,
                    OrderLocationAddressTypeFID
                )
                values
                (
                    [Source].OrderId,
                    [Source].[Location],
                    [Source].OrderContactId,
                    [Source].Imported,
                    [Source].BuildingFid,
                    [Source].CrossStreet,
                    [Source].ServiceEntrance,
                    [Source].SiteNote,
                    [Source].OrderLocationAddressTypeID
                )
                -- Output the External ID and primary key of each inserted Order Location, so that we can link them to Order Location Addresses below.
                output
                    inserted.OrderLocationID,
                    [Source].ExternalID    
                into @theLocationIds;

                -- Insert Order Location Addresses
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into OrderLocationAddress
                (
                    OrderLocationFID,
                    AddressTypeFID,
                    AddressLocationTypeFID,
                    Address1,
                    Address2,
                    Address3,
                    City,
                    State,
                    PostalCode,
                    CountryCodeStandardFID
                )
                select
                    LocationIds.OrderLocationId,
                    @theMainAddressTypeId,
                    XmlAddressLocationTypeMap.AddressLocationTypeFID,
                    Address1 = [LocationAddress].value( 'MSS:Address1[1]', 'Address' ),
                    Address2 = [LocationAddress].value( 'MSS:Address2[1]', 'Address' ),
                    Address3 = [LocationAddress].value( 'MSS:Address3[1]', 'Address' ),
                    City = [LocationAddress].value( 'MSS:City[1]', 'AddressCity' ),
                    [State] = [LocationAddress].value( 'MSS:State[1]', 'AddressState' ),
                    Postal = [LocationAddress].value( 'MSS:Postal[1]', 'AddressPostalCode' ),
                    CountryCode = CountryCodeStandard.CountryCodeStandardID
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderLocations/MSS:OrderLocation/MSS:OrderLocationAddress' ) as XmlTable([LocationAddress])
                inner join @theLocationIds as LocationIds on LocationIds.ExternalID = [LocationAddress].value( '../MSS:ExternalID[1]', 'varchar(32)' )
                left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = [LocationAddress].value( 'MSS:CountryCode[1]', 'varchar(3)' )
                left outer join XmlAddressLocationTypeMap on
                    XmlAddressLocationTypeMap.ExternalCode = [LocationAddress].value( 'MSS:AddressLocationType[1]', 'varchar(32)' ) and
                    XmlAddressLocationTypeMap.XmlInterfaceFID = @theXmlInterfaceId
        
            -- end if ( 1 = @theOrderLocationsExist )
            end

            -- If there are any Local Services in the XML, import them.
            declare @theLSElementExists bit
            ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
            select @theLSElementExists = 1 from @inXmlText.nodes( 'MSS:Order/MSS:OfficeIndustrial/MSS:LocalServices/MSS:LocalService' ) as XmlTable(LocalServiceColumn)
            if ( 1 = @theLSElementExists )
            begin

                -- Insert Local Services.
                -- This is done using a "merge" statement so that we can output a table that matches each location's external ID with the primary key of the inserted record.
                declare @theLocalServiceIds table( PriKey int, ExternalID varchar(32), ServiceInstructions varchar(1024) )
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                merge LocServ
                using
                (
                    select
                        @inOrderID as OrderId,
                        LS.value( 'MSS:ExternalID[1]', 'varchar(32)' ) as ExternalID,
                        XmlInterfaceServiceTypeMap.ServTypeFid,
                        LS.value( 'MSS:Location[1]', 'varchar(1)' ) as OriginDestination,
                        LS.value( 'MSS:ServiceDate[1]', 'datetime' ) as ServiceDate,
                        LS.value( 'MSS:ServiceTime[1]', 'datetime' ) as ServiceTime,
                        LS.value( 'MSS:HoursPerMan[1]', 'int' ) as HoursPerMan,
                        getdate() as CreatedOn,
                        @theExternalApplicationSysUserId as CreatedBy,
                        @theOrderBranchId as BranchId,
                        OriginLocationId.OrderLocationId as OriginOrderLocationId,
                        DestinationLocationId.OrderLocationId as DestinationOrderLocationId,
                        @theLocalServiceIdleStatus as ServStatus,
                        LS.value( 'MSS:ServiceInstructions[1]','varchar(1024)' ) as ServiceInstructions,
                        0 as TotCrewHrs,
                        0 as HrsDrv1Way,
                        0 as Quantity
                    from @inXmlText.nodes( 'MSS:Order/MSS:OfficeIndustrial/MSS:LocalServices/MSS:LocalService' ) as XmlTable(LS)
                    left outer join @theLocationIds as OriginLocationId on OriginLocationId.ExternalID = LS.value( 'MSS:OriginLocationExternalID[1]', 'varchar(32)' )
                    left outer join @theLocationIds as DestinationLocationId on DestinationLocationId.ExternalID = LS.value( 'MSS:DestinationLocationExternalID[1]', 'varchar(32)' )
                    left outer join XmlInterfaceServiceTypeMap on XmlInterfaceServiceTypeMap.ExternalCode = LS.value( 'MSS:Type[1]', 'varchar(32)' )
                ) as [Source]
                on 1 = 0
                when not matched then
                insert
                (
                    OrdPriKey,
                    ServiceId,
                    OriginDestination,
                    ServiceDate,
                    RequestedStartTime,
                    HrsPerMan,
                    CreatedOn,
                    CreatedBy,
                    BranchPriKey,
                    OriginOrderLocationFID,
                    DestinationOrderLocationFID,
                    ServStatus,
                    TotCrewHrs,
                    HrsDrv1Way,
                    Quantity
                )
                values
                (
                    [Source].OrderId,
                    [Source].ServTypeFid,
                    [Source].OriginDestination,
                    [Source].ServiceDate,
                    [Source].ServiceTime,
                    [Source].HoursPerMan,
                    [Source].CreatedOn,
                    [Source].CreatedBy,
                    [Source].BranchId,
                    [Source].OriginOrderLocationId,
                    [Source].DestinationOrderLocationId,
                    [Source].ServStatus,
                    [Source].TotCrewHrs,
                    [Source].HrsDrv1Way,
                    [Source].Quantity
                )
                -- Output the External ID and primary key of each inserted LocServ, so that we can link them to crew requests and equipment requests below.
                output
                    inserted.PriKey,
                    [Source].ExternalID,
                    [Source].ServiceInstructions
                into @theLocalServiceIds;

                -- Insert Instructions
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into LSInstructions
                (
                    LSPriKey,
                    SysUserFID,
                    LastModified,
                    Instruction
                )
                select
                    LocalServiceIds.PriKey,
                    @theExternalApplicationSysUserId,
                    getdate(),
                    -- The nature of the XML data type will strip the CRs out but MoversSuite needs them if LFs are present.
                    dbo.HandleCRLFInMemos( LocalServiceIds.ServiceInstructions, 1024 )
                from @theLocalServiceIds as LocalServiceIds
                where rtrim( isnull( LocalServiceIds.ServiceInstructions, '' ) ) <> ''

                -- Insert Labor Requests
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into LocalServiceLaborRequest
                (
                    LocalServiceFID,
                    LaborTypeFID,
                    Quantity
                )
                select
                    LocalServiceIds.PriKey,
                    XmlInterfaceLaborTypeMap.LaborTypeFid,
                    [LaborRequests].value( 'MSS:Quantity[1]', 'int' )
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:LocalServices/MSS:LocalService/MSS:LaborRequests/MSS:LaborRequest' ) as XmlTable(LaborRequests)
                inner join @theLocalServiceIds as LocalServiceIds on LocalServiceIds.ExternalID = [LaborRequests].value( '../../MSS:ExternalID[1]', 'varchar(32)' )
                left outer join XmlInterfaceLaborTypeMap on
                    XmlInterfaceLaborTypeMap.ExternalCode = [LaborRequests].value( 'MSS:LaborType[1]', 'varchar(32)' ) and
                    XmlInterfaceLaborTypeMap.XmlInterfaceFid = @theXmlInterfaceId

                -- Insert Equipment Requests
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into LocalServiceEquipmentRequest
                (
                    LocalServiceFID,
                    EquipmentTypeFID,
                    Quantity
                )
                select
                    LocalServiceIds.PriKey,
                    XmlInterfaceEqTypeMap.EqTypeFid,
                    [EquipmentRequests].value( 'MSS:Quantity[1]', 'int' )
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:LocalServices/MSS:LocalService/MSS:EquipmentRequests/MSS:EquipmentRequest' ) as XmlTable(EquipmentRequests)
                inner join @theLocalServiceIds as LocalServiceIds on LocalServiceIds.ExternalID = [EquipmentRequests].value( '../../MSS:ExternalID[1]', 'varchar(32)' )
                left outer join XmlInterfaceEqTypeMap on
                    XmlInterfaceEqTypeMap.ExternalCode = [EquipmentRequests].value( 'MSS:EquipmentType[1]', 'varchar(32)' ) and
                    XmlInterfaceEqTypeMap.XmlInterfaceFid = @theXmlInterfaceId

                -- Insert Materials
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into LSMaterial
                (
                    LocServPriKey,
                    ActQuantity,
                    MaterialItemCodeMapFID,
                    EstQuantity
                )
                select
                    LocalServiceIds.PriKey,
                    null,
                    MaterialItemCodeMap.MaterialItemCodeMapID,
                    [Materials].value( 'MSS:Quantity[1]', 'int' )
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:LocalServices/MSS:LocalService/MSS:Materials/MSS:Material' ) as XmlTable(Materials)
                inner join @theLocalServiceIds as LocalServiceIds on LocalServiceIds.ExternalID = [Materials].value( '../../MSS:ExternalID[1]', 'varchar(32)' )
                left outer join XmlItemCode on
                    XmlItemCode.XmlInterfaceFID = @theXmlInterfaceId and
                    XmlItemCode.XmlItemCode = [Materials].value( 'MSS:ExternalCode[1]', 'varchar(50)' )
                left outer join MaterialCPUType on
                    MaterialCPUType.[Type] = [Materials].value( 'MSS:CPUType[1]', 'varchar(32)' )
                left outer join MaterialItemCodeMap on
                    MaterialItemCodeMap.MaterialTypeFID = XmlItemCode.MaterialTypeFID and
                    MaterialItemCodeMap.MaterialCPUTypeFID = MaterialCPUType.MaterialCPUTypeID
                    
                -- Set the crew count based on labor requests.
                ;with LocServCrewCounts ( LocServId, CrewCount ) as
                (
                    select
                        LocalServiceLaborRequest.LocalServiceFID,
                        sum( LocalServiceLaborRequest.Quantity ) as CrewCount
                    from LocalServiceLaborRequest
                    inner join @theLocalServiceIds as LocalServiceIds on LocalServiceIds.PriKey = LocalServiceLaborRequest.LocalServiceFID
                    group by LocalServiceLaborRequest.LocalServiceFID
                )
                update LocServ set
                    Crew = LocServCrewCounts.CrewCount
                from LocServ
                inner join LocServCrewCounts on LocServCrewCounts.LocServId = LocServ.PriKey

            -- end if ( 1 = @@theLSElementExists )
            end

        -- end "else"
        end
    end

-- end if exists( select 1 from openxml( @theXmlDoc, @theElement ) )
end

if( @outErrorCode > 0 )
begin
    -- Handle errors
    declare @theXmlText nvarchar(max) = cast( @inXmlText as nvarchar(max) )
    exec XmlImportHandleError
        @inErrorCode = @outErrorCode,
        @inOrderID = @inOrderID,
        @inXmlData = @inXmlText,
        @inClearErrors = 0
end
GO
GRANT EXECUTE ON  [dbo].[XmlImportProcessOfficeIndustrial] TO [MssExec]
GO