CREATE procedure [dbo].[XmlImportProcessOfficeIndustrial]
@inXmlText xml,
@inOrderID int,
@outErrorCode int output,
@outStatusCode int output
as
set nocount on
declare @ERRORCODE_ORDER_NOT_FOUND int
set @ERRORCODE_ORDER_NOT_FOUND = 2040
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
set @outErrorCode = 0
set @outStatusCode = @STATUSCODE_NO_OFFICE_INDUSTRIAL_DATA
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' )
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
if not exists( select 1 from Orders where PriKey = @inOrderID )
begin
set @outStatusCode = @STATUSCODE_ERROR
set @outErrorCode = @ERRORCODE_ORDER_NOT_FOUND
end
else
begin
if( @theAppVendorName is null )
begin
set @theAppVendorName = dbo.GetExtAppVendorFromMssXmlText( @inXmlText )
end
declare @theXmlInterfaceId int
select
@theXmlInterfaceId = XmlInterface.XmlInterfaceID
from XmlInterface
where XmlInterface.VendorName = @theAppVendorName
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'
declare @theExternalApplicationSysUserId int
select @theExternalApplicationSysUserId = SysUserID
from SysUser
where FirstName = 'External' and LastName = 'Application'
declare @theOrderBranchId int
select @theOrderBranchId = BranchPriKey
from Orders
where Orders.PriKey = @inOrderID
;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
update Orders set
OpenOrderInModule = 'Office & Industrial'
where PriKey = @inOrderID
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;
;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)' )
;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
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
;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
inserted.OrderLocationID,
[Source].ExternalID
into @theLocationIds;
;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
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
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
inserted.PriKey,
[Source].ExternalID,
[Source].ServiceInstructions
into @theLocalServiceIds;
;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
insert into LSInstructions
(
LSPriKey,
SysUserFID,
LastModified,
Instruction
)
select
LocalServiceIds.PriKey,
@theExternalApplicationSysUserId,
getdate(),
dbo.HandleCRLFInMemos( LocalServiceIds.ServiceInstructions, 1024 )
from @theLocalServiceIds as LocalServiceIds
where rtrim( isnull( LocalServiceIds.ServiceInstructions, '' ) ) <> ''
;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
;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
;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
end
end
end
if( @outErrorCode > 0 )
begin
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