FSL Integration between Navision and Oracle Fusion

FSL Navision Tasks

Taskflows:

1) Technicians_From_Navision_To_FSL_TF

2) Technicians_Locations_From_Navision_To_FSL_TF

3) Customer_Locations_From_Nav_West_To_FSL_TF

4) Customer_Associated_Locations_From_Navision_To_FSL_TF

5) Assets_Update_From_Navision_To_FSL_TF

6) Service_Contract_From_Nav_West_To_FSL_TF

7) Entitlement_From_Nav_West_To_FSL_TF

8) Work_Order_LineItem_From_FSL_To_Navision_TF

9) Work_Order_Line_Item_From_Navision_To_FSL_Update

10) StockTransfer_From_FSL_To_Navision_TF

11) Product_Transfer_From_Navision_To_FSL_TF

12) Parts_Order_Details_From_FSL_To_Navision_TF

13) Parts_Order_Details_From_Navision_To_FSL_TF

14) Update_Canceled_Service_Contracts_From_Navision_To_FSL_TF_AM

15) Work_Order_History_Load_Navision_To_FSL_TF

16) Open_Orders_History_from_Navision_to_FSL_TF

Taskflow1: Technicians_From_Navision_To_FSL_TF

Lkp_Navision_Technician_SFDC_Id_From_FSL_To_FF

LKP_Navision_Location_From_FSL_To_FF

LKP_Navision_Service_Territory_From_FSL_To_FF

Technicians_From_Navision_West_To_FSL_Update

Business Logic : The process will sync the Navision Technician Information to Salesfore as per schedule

Task1: Lkp_Navision_Technician_SFDC_Id_From_FSL_To_FF

Task	Lkp_Navision_Technician_SFDC_Id_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Service Resource
Target	Flat File
Target Object	Navision_Technician.csv

Source and Target Fields:

Target Fields	Source Fields
Resource Id	Id
Name	Name
Resource_ID__c	Resource_ID__c

Task2: LKP_Navision_Location_From_FSL_To_FF

Task	LKP_Navision_Location_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	Navision_Location.csv

Data Filter Condition: Location filtered by ERP__c Equals 'Navision'

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Name	RTRIM(Name,'char(39)\|\|s Van')
Email__c	Email__c
Subsidiary__c	Subsidiary__c
Location_ID__c	Location_ID__c

Task3: LKP_Navision_Service_Territory_From_FSL_To_FF

Task	LKP_Navision_Service_Territory_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	ServiceTerritory
Target	Flat File
Target Object	Navision_ServiceTerritory.csv

Data Filter Condition: ServiceTerritory filtered by ERP__c Equals 'Navision'

Source and Target Fields;

Target Fields	Source Fields
Id	Id
Name	Name

Task4: Technicians_From_Navision_West_To_FSL_Update

Task	Technicians_From_Navision_West_To_FSL_Update
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	ServiceResource

Source Saved Query: SQ_Technicians_From_Navision_West_to_FSL

SELECT NAME, Contact,'Technicians' Resource_Type,

CASE WHEN Subsidiary = 'VTI Eire' THEN 'VTI Eire'

WHEN Subsidiary = 'VTI France' THEN 'VTI France'

WHEN Subsidiary = 'VTI Germany' THEN 'VTI Germany'

WHEN Subsidiary = 'VTI NL' THEN 'VTI NL'

WHEN Subsidiary = 'VTI Norway' THEN 'VTI Norway'

WHEN Subsidiary = 'VTI Portugal' THEN 'VTI Portugal'

WHEN Subsidiary = 'VTI Spain' THEN 'VTI Spain'

WHEN Subsidiary = 'VTI Switzerland' THEN 'VTI Switzerland'

WHEN Subsidiary = 'VTI Turkey' THEN 'VTI Turkey'

WHEN Subsidiary = 'VTI UK' THEN 'VTI UK'

WHEN Subsidiary = 'Willett S.r.l. (Italy)' THEN 'VTI Italy'

END Resource_Territory,a.[Technician_No] Resource_Id,a.[Primary_Key] Location_Id,null Emp_num,a.Email,a.Phone_No,null Dept,

'Service Engineer' Role, ( SELECT b.ShipTo_Location FROM "FSL Integration" b

where a.Technician_No = b.Technician_No

and b.type=8

and b.action in (1,2)

) Inventory_Num,

a.Subsidiary,

CASE a.Action

WHEN 3 THEN 'Inactive'

ELSE 'Active'

END Status

FROM "FSL Integration" a

where LEN("Customer_No") = 0

and type =8

AND Subsidiary = 'VTI NL'

AND DATEDIFF(day,[Last_Modified_Date],getdate()) between 0 and 2

ORDER BY A.Technician_No;

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
NAME	Id	lookup( Resource_Id = Resource_ID__c ): Navision_Technician.csv.Id
Contact	Name	Contact
Resource_Type	CurrencyIsoCode	'EUR'
Resource_Territory	ResourceType	DECODE (Resource_Type, 'Technicians', 'T', 'FIELD SERVICE SUPERVISOR' , 'T', 'Customer Service Rep','D', 'T')
Resource_Id	LocationId	lookup( Location_Id = Location_ID__c ): Navision_Location.csv.Id
Location_Id	Department__c	Dept
Emp_num	ERP__c	'Navision'
Email	Email__c	Email
Phone_No	Employee_Number__c	Emp_num
Dept	Inventory__c	Inventory_Num
Role	Operating_Unit__c	Subsidiary
Inventory_Num	Phone__c	Phone_No
Subsidiary	Resource_ID__c	Resource_Id
Status	Role__c	Role
	Service_Territory__c	lookup( Resource_Territory = Name ): Navision_ServiceTerritory.csv.Id
	Workgroup__c	Resource_Territory

Taskflow 2: Technicians_Locations_From_Navision_To_FSL_TF

Lkp_Navision_Technicians_From_FSL_To_FF

Technicians_Locations_From_Navision_West_To_FSL

Lkp_Technicians_From_FSL_To_FF_All

Technicians_Locations_From_Navision_West_To_FSL_All

Business Logic : The process will sync the Navision Technician’s locations Information to Salesfore as per schedule

Task1: Lkp_Navision_Technicians_From_FSL_To_FF

Task	Lkp_Navision_Technicians_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	ServiceResource
Target	Flat File
Target Object	Navision_Technicians.csv

Data Filter Condition: ServiceResource filtered by ERP__c Equals 'Navision' AND Resource_ID__c Is Not Null

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Name	Name
RelatedRecordId	RelatedRecordId
Resource_ID__c	Resource_ID__c
Inventory__c	Inventory__c
ERP__c	ERP__c

Task2: Technicians_Locations_From_Navision_West_To_FSL

Task	Technicians_Locations_From_Navision_West_To_FSL
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Location

Source Saved Query: SQ_Technicians_Locations_From_Navision_West_To_FSL

SELECT a.City, a.Country_Code, a.Email,

( SELECT b.ShipTo_Location

FROM "FSL Integration" b

where a.Technician_No = b.Technician_No

and b.type =8

and b.action in (1,2)

) Inv_Account,

a.Name Location_Name,a.Contact,a.Subsidiary,a.Technician_No Resource_Id,a.[Primary_Key] Location_Id,a.Phone_No,

a.County State,a.type Inventory_Location,a.address Street,a.Postal_Code Zip_Code

FROM "FSL Integration" a

WHERE LEN("Customer_No") = 0

AND type='8'

AND Subsidiary='VTI NL'

AND DATEDIFF(day,[Last_Modified_Date],getdate()) between 0 and 2

ORDER BY A.Technician_No;

Source Fields and Target Fields:

Source Fields	Target Fields	Source Mapped fields
City	Name	Contact\|\|CHR(39)\|\|'s'\|\| ' '\|\|'Van'
Country_Code	CurrencyIsoCode	'EUR'
Email	LocationType	'Van'
Inv_Account	IsMobile	'1'
Location_Name	IsInventoryLocation	'1'
Contact	Active__c	'Yes'
Subsidiary	City__c	City
Resource_Id	Country__c	Country_Code
Location_Id	ERP__c	'NAVISION'
Phone_No	Email__c	Email
State	Inventory_Account__c	Inv_Account
Inventory_Location	Location_ID__c	Location_Id
Street	Phone__c	Phone_No
Zip_Code	Resource_ID_Location_ID__c	Location_Id
	State__c	State
	Street__c	Street
	Subsidiary__c	Subsidiary
	Technician_User__c	lookup( Resource_Id = Resource_ID__c ): Navision_Technicians.csv.RelatedRecordId
	Technician__c	lookup( Resource_Id = Resource_ID__c ): Navision_Technicians.csv.Id
	Zip_Code__c	Zip_Code

Task3: Lkp_Technicians_From_FSL_To_FF_All

Task	Lkp_Technicians_From_FSL_To_FF_All
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Technician
Target	Flat File
Target Object	UAT_ServiceResource.csv

Data Filter Condition: Technician filtered by Resource ID Is Not Null

Source and Target Fields:

Target Fields	Source Fields
Id	Id
RelatedRecordId	RelatedRecordId
Resource_ID__c	Resource_ID__c

Task4: Technicians_Locations_From_Navision_West_To_FSL_All

Task	Technicians_Locations_From_Navision_West_To_FSL_All
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Location

Source Saved query: SQ_Technicians_Locations_From_Navision_West_To_FSL_All

SELECT a.City, a.Country_Code, a.Email, ( SELECT b.ShipTo_Location FROM "FSL Integration" b

where a.Technician_No = b.Technician_No and a.Subsidiary=b.Subsidiary and b.type =8 and b.action in (1,2) ) Inv_Account, a.Name Location_Name,

case when Subsidiary = 'VTI Sweden' then 'SE '+a.Contact when Subsidiary = 'VTI Norway' then 'NO '+a.Contact

when Subsidiary = 'VTI Spain' then 'ES '+a.Contact when Subsidiary = 'VTI Portugal' then 'PT '+a.Contact when Subsidiary = 'VTI Switzerland' then 'CH '+a.Contact

when Subsidiary = 'VTI UK' then 'GB '+a.Contact when Subsidiary = 'VTI Eire' then 'IE '+a.Contact

Else a.Contact end Contact, a.Subsidiary, a.Technician_No Resource_Id, a.[Primary_Key] Location_Id, a.Phone_No, a.County State,

a.type Inventory_Location, a.address Street, a.Postal_Code Zip_Code FROM "FSL Integration" a WHERE LEN("Customer_No") = 0 AND type='8'

AND Subsidiary IN ('VTI Sweden','VTI Norway','VTI Spain','VTI Portugal','VTI Switzerland','VTI UK','VTI Eire')

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
City	Name	Contact\|\|CHR(39)\|\|'s'\|\| ' '\|\|'Van'
Country_Code	CurrencyIsoCode	IIF(Subsidiary='VTI NL','EUR',IIf(Subsidiary='VTI Norway','NOK',IIF(Subsidiary='VTI Switzerland','CHF',IIF(Subsidiary='VTI UK','GBP', IIF(Subsidiary='VTI Sweden','SEK','EUR')))))
Email	LocationType	'Van'
Inv_Account	IsMobile	'1'
Location_Name	IsInventoryLocation	'1'
Contact	Active__c	'Yes'
Subsidiary	City__c	City
Resource_Id	Country__c	Country_Code
Location_Id	ERP__c	'NAVISION'
Phone_No	Email__c	Email
State	Inventory_Account__c	Inv_Account
Inventory_Location	Location_ID__c	Location_Id
Street	Phone__c	Phone_No
Zip_Code	Resource_ID_Location_ID__c	Location_Id
	State__c	State
	Street__c	Street
	Subsidiary__c	Subsidiary
	Technician_User__c	lookup( Resource_Id = Resource_ID__c ): Navision_Technicians.csv.RelatedRecordId
	Technician__c	lookup( Resource_Id = Resource_ID__c ): Navision_Technicians.csv.Id
	Zip_Code__c	Zip_Code

Taskflow3: Customer_Locations_From_Nav_West_To_FSL_TF

Customer_Locations_From_Navision_West_To_FSL

LKP_For_Default_Bill_To_Navision

Update_Default_Bill_To_For_Navision_Customer_Location

Customer_Locations_From_Navision_West_To_FSL_All

Lkp_Navision_Customer_Locations_From_FSL_To_FF_All

LKP_For_Default_Bill_To_Navision_All

Update_Default_Bill_To_For_Navision_All

Business Logic : The process will sync the Customer’s locations Information from Navision to Salesfore as per schedule

Task1: Customer_Locations_From_Navision_West_To_FSL

Task	Customer_Locations_From_Navision_West_To_FSL
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Location

Source Saved query: SQ_Customer_Locations_From_Nav_West_To_FSL

SELECT

a.Name AS Location_Name,a.Name_2 as Name_2,a.address AS Street,a.address_2 AS address_2,a.City,a.County State,a.Postal_Code Zip_Code,

a.Subsidiary,a.[Primary_Key] Location_ID,a.Phone_No,a.customer_no Account_Number,a.Country_Code Country,a.type,a.Service_Zone_Code

FROM "FSL Integration" a

WHERE LEN("Customer_No") <> 0

AND type IN (1,2)

AND Subsidiary = 'VTI NL'

AND Do_Not_Interface = 0

AND DATEDIFF(day,[Last_Modified_Date],getdate()) between 0 and 2

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
Location_Name	Name	Location_Name
Street	CurrencyIsoCode	'EUR'
City	LocationType	'Customer Site'
State	Account_Number__c	Account_Number
Zip_Code	Active__c	'Yes'
Subsidiary	Billing_Location__c	IIF(type=1, 1, 0)
Location_ID	City__c	City
Phone_No	Country__c	Country
Account_Number	ERP__c	'Navision'
Country	Location_ID__c	Location_ID
type	Phone__c	Phone_No
Service_Zone_Code	Resource_ID_Location_ID__c	Location_ID
	Shipping_Location__c	1
	State__c	State
	Street__c	Street
	Subsidiary__c	Subsidiary
	Zip_Code__c	Zip_Code
	Service_Zone__c	Service_Zone_Code

Task2: LKP_For_Default_Bill_To_Navision

Task	LKP_For_Default_Bill_To_Navision
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	Lkp_Default_Location_Nav.csv

Data Filter Condition: Location filtered by ERP__c Equals 'Navision' AND LocationType Not Equals 'Van'

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Resource_ID_Location_ID__c	Resource_ID_Location_ID__c
ERP__c	ERP__c
Location_ID__c	Location_ID__c
LocationType	LocationType

Task3: Update_Default_Bill_To_For_Navision_Customer_Location

Task	Update_Default_Bill_To_For_Navision_Customer_Location
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Location

Source Saved query: SQ_Update_Default_Bill_To_Location_Navision

SELECT [Primary_Key] Location,

[Customer_No],

[Type],

(select [Primary_Key]

from [FSL Integration] A

WHERE a.Customer_No = b.Customer_No

and a.Subsidiary = b.Subsidiary

and a.Type = 1) Bill_To_Location

FROM [FSL Integration] B

WHERE Subsidiary = 'VTI NL'

and type=2

AND DATEDIFF(day,[Last_Modified_Date],getdate()) between 0 and 2

order by [Last_Modified_Date] desc;

Source and Target Fields:

Source	Target	Source mapping fields
Location	Id	lookup( Location = Resource_ID_Location_ID__c ): Lkp_Default_Location_Nav.csv.Id
Bill_To_Location	Default_Bill_To_Location__c	lookup( Bill_To_Location = Resource_ID_Location_ID__c ): Lkp_Default_Location_Nav.csv.Id

Task4: Customer_Locations_From_Navision_West_To_FSL_All

Task	Customer_Locations_From_Navision_West_To_FSL
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Location

Source Saved query: SQ_Customer_Locations_From_Nav_West_To_FSL_All

SELECT a.Name AS Location_Name,

a.Name_2 as Name_2,

a.address AS Street, a.address_2 AS address_2, a.City, a.County State, a.Postal_Code Zip_Code, a.Subsidiary, a.[Primary_Key] Location_ID, a.Phone_No, a.customer_no Account_Number, a.Country_Code Country, a.type, a.Service_Zone_Code FROM "FSL Integration" a WHERE LEN("Customer_No") <> 0 AND type IN (1,2)

AND Subsidiary in ('VTI Sweden','VTI Norway','VTI Spain','VTI Portugal','VTI Switzerland','VTI UK','VTI Eire') AND Do_Not_Interface = 0

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
Location_Name	Location Name	Location_Name
Street	CurrencyIsoCode	IIF(Subsidiary='VTI NL','EUR',IIf(Subsidiary='VTI Norway','NOK',IIF(Subsidiary='VTI Switzerland','CHF',IIF(Subsidiary='VTI UK','GBP', IIF(Subsidiary='VTI Sweden','SEK','EUR')))))
City	LocationType	'Customer Site'
State	Account_Number__c	Account_Number
Zip_Code	Active__c	'Yes'
Subsidiary	Billing_Location__c	IIF(type=1, 1, 0)
Location_ID	City__c	City
Phone_No	Country__c	Country
Account_Number	ERP__c	'Navision'
Country	Location_ID__c	Location_ID
type	Phone__c	Phone_No
Service_Zone_Code	Resource_ID_Location_ID__c	Location_ID
	Shipping_Location__c	1
	State__c	State
	Street__c	Street
	Subsidiary__c	Subsidiary
	Zip_Code__c	Zip_Code
	Service_Zone__c	Service_Zone_Code

Task4: Lkp_Navision_Customer_Locations_From_FSL_To_FF_All

Task	Lkp_Navision_Customer_Locations_From_FSL_To_FF_All
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	Lkp_for_Delete_Location.csv

Data Filter Condition: Location filtered by ERP__c ='Navision' AND LocationType != 'Van'

Source and Target Fields:

Target Fields	Source Fields
Id	Location ID
Resource_ID_Location_ID__c	Resource_ID_Location_ID__c

Task6: LKP_For_Default_Bill_To_Navision_All

Task	LKP_For_Default_Bill_To_Navision_All
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	Lkp_Dafault_Location_Nav.csv

Data Filter condition: Location filtered by ERP__c ='Navision' AND LocationType != 'Van'

Source and Target Fields:

Target Fields	Source Fields
Id	Location ID
Resource_ID_Location_ID__c	Resource_ID_Location_ID__c
ERP__c	ERP__c
Location_ID__c	Location_ID__c
LocationType	LocationType

Task7: Update_Default_Bill_To_For_Navision_All

Task	Update_Default_Bill_To_For_Navision_All
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Location

Source Saved query: SQ_Update_Default_Bill_To_Location_Navision_All

SELECT [Primary_Key] Location, [Customer_No], [Type], (select [Primary_Key] from [FSL Integration] A

WHERE a.Customer_No = b.Customer_No and a.Subsidiary = b.Subsidiary and a.Type = 1) Bill_To_Location

FROM [FSL Integration] B WHERE Subsidiary IN( 'VTI Sweden','VTI Norway','VTI Spain','VTI Portugal','VTI Switzerland','VTI UK','VTI Eire') and type=2

Source and Target Fields:

Source	Target	Source mapping fields
Location	Location ID	lookup( Location = Resource_ID_Location_ID__c ): Lkp_Dafault_Location_Nav.csv.Id
Bill_To_Location	Default_Bill_To_Location__c	lookup( Bill_To_Location = Resource_ID_Location_ID__c ): Lkp_Dafault_Location_Nav.csv.Id

Taskflow4: Customer_Associated_Locations_From_Navision_To_FSL_TF

Lkp_Navision_Accounts_From_FSL_To_FF

Lkp_Navision_Associated_Location_From_FSL_To_FF

Customer_Associated_Locations_From_Navision_To_FSL

LKP_For_Deleted_Locations_From_FSL_To_FF

Update_Deleted_Locations_From_Navision_To_FSL_TF

Delete_Associated_Location_Of_Inactive_Customer_Locations

Lkp_Navision_Accounts_From_FSL_To_FF_All

Lkp_Navision_Associated_Location_From_FSL_To_FF_All

Customer_Associated_Locations_From_Navision_To_FSL_All

Task1: Lkp_Navision_Accounts_From_FSL_To_FF

Task	Lkp_Navision_Accounts_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Account
Target	Flat File
Target Object	Lkp_Associated_Accounts.csv

Data Filter condition: Account filtered by Navision ID Is Not Null

Source and Target Fields:

Target Fields	Source Fields
Id	Account ID
AccountNumber	AccountNumber
Subsidiary__c	Subsidiary__c
Navision_ID__c	Navision_ID__c

Task2: Lkp_Navision_Associated_Location_From_FSL_To_FF

Task	Lkp_Navision_Associated_Location_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	Lkp_Associated_Location.csv

Data Filter Condition: Location filtered by ERP__c = 'Navision' AND LocationType = 'Customer Site'

Source and Target Fields:

Target Fields	Source Fields
Id	Location ID
Resource_ID_Location_ID__c	Resource_ID_Location_ID__c

Task3: Customer_Associated_Locations_From_Navision_To_FSL

Task	Customer_Associated_Locations_From_Navision_To_FSL
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	AssociatedLocation

Source Saved query: SQ_Nav_Customer_Associated_Locations

SELECT

a.Subsidiary,

a.[Primary_Key] Location_ID,

a.customer_no Account_Number,

a.Type

FROM "FSL Integration" a

WHERE LEN("Customer_No") <> 0

AND type IN (1,2)

AND Subsidiary = 'VTI NL'

AND DATEDIFF(day,[Last_Modified_Date],getdate()) between 0 and 2

order by [Last_Modified_Date] desc;

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
Subsidiary	CurrencyIsoCode	'EUR'
Location_ID	ParentRecordId	lookup( Account_Number = AccountNumber, Subsidiary = Subsidiary__c ): Lkp_Associated_Accounts.csv.Id
Account_Number	LocationId	lookup( Location_ID = Resource_ID_Location_ID__c ): Lkp_Associated_Location.csv.Id
Type	Type	decode(Type, 2, 'Ship To', 1, 'Bill To')
	Location_ID__c	Location_ID

Task4: LKP_For_Deleted_Locations_From_FSL_To_FF

Task	LKP_For_Deleted_Locations_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	LKP_For_Deleted_Locations.csv

Data Filter Condition: Location filtered by ERP Equals 'Navision'

Source and Target Fields:

Target	Source
Id	Location ID
Resource_ID_Location_ID__c	Resource_ID_Location_ID__c

Task 5: Update_Deleted_Locations_From_Navision_To_FSL_TF

Task	Update_Deleted_Locations_From_Navision_To_FSL_TF
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Location

Source Saved query: SQ_Update_Deleted_Technicians_From_Navision_To_FSL

select CONVERT(Varchar(100),Primary_Key) AS Primary_Key

FROM "FSL Integration" a

where type IN (1,2,8)

AND ACTION = 3

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
Primary_Key	Id	lookup( Primary_Key = Resource_ID_Location_ID__c ): LKP_For_Deleted_Locations.csv.Id
	Active__c	'No'

Task 6: Delete_Associated_Location_Of_Inactive_Customer_Locations

Task	Delete_Associated_Location_Of_Inactive_Customer_Locations
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Associated Location, Location
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Associated Location

Data Filter Condition: Location filtered by ERP Equals 'Navision' AND Active Equals 'No'

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
Associated Location ID	Associated Location ID	Id

Task7: Lkp_Navision_Accounts_From_FSL_To_FF_All

Task	Lkp_Navision_Accounts_From_FSL_To_FF_All
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Account
Target	Flat File
Target Object	Lkp_Associated_Accounts.csv

Data Filter condition: Account filtered by Navision_ID__c != NULL AND Subsidiary__c IN( 'VTI Sweden','VTI Norway','VTI Spain','VTI Portugal','VTI Switzerland','VTI UK','VTI Eire' )

Source and Target Fields:

Target Fields	Source Fields
Id	Account ID
AccountNumber	AccountNumber
Subsidiary__c	Subsidiary__c
Navision_ID__c	Navision_ID__c

Task7: Lkp_Navision_Associated_Location_From_FSL_To_FF

Task	Lkp_Navision_Associated_Location_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	Lkp_Associated_Location.csv

Data Filter Condition: Location filtered by ERP__c = 'Navision' AND LocationType = 'Customer Site'

Source and Target Fields:

Target Fields	Source Fields
Id	Location ID
Resource_ID_Location_ID__c	Resource_ID_Location_ID__c

Task 8: Customer_Associated_Locations_From_Navision_To_FSL_All

Task	Customer_Associated_Locations_From_Navision_To_FSL_All
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	FSL Integration
Target	PROD_SFDC_NA_INTEGRATION
Target Object	AssociatedLocation

Source Saved query: SQ_Nav_Customer_Associated_Locations_All

SELECT a.Subsidiary, a.[Primary_Key] Location_ID, a.customer_no Account_Number, a.Type,a.SFDC_ID FROM "FSL Integration" a

WHERE LEN("Customer_No") <> 0

AND type IN (1,2) AND Subsidiary IN( 'VTI Sweden','VTI Norway','VTI Spain','VTI Portugal','VTI Switzerland','VTI UK','VTI Eire')

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
Subsidiary	CurrencyIsoCode	IIF(Subsidiary='VTI NL','EUR',IIf(Subsidiary='VTI Norway','NOK',IIF(Subsidiary='VTI Switzerland','CHF',IIF(Subsidiary='VTI UK','GBP', IIF(Subsidiary='VTI Sweden','SEK','EUR')))))
Location_ID	ParentRecordId	lookup( Account_Number = AccountNumber, Subsidiary = Subsidiary__c ): Lkp_Associated_Accounts.csv.Id
Account_Number	LocationId	lookup( Location_ID = Resource_ID_Location_ID__c ): Lkp_Associated_Location.csv.Id
Type	Type	decode(Type, 2, 'Ship To', 1, 'Bill To')
	Location_ID__c	Location_ID

Taskflow 5: Assets_Update_From_Navision_To_FSL_TF

Assets_From_SFDC_To_FF

Product_Lookup_for_CPQ_FF

LKP_Navision_Location_From_FSL_To_FF

Assets_Update_Warranty_From_Navision_To_FSL

Assets_Update_Locations_From_Navision_To_FSL

Assets_Update_Product_From_Navision_To_SFDC

Assets_From_SFDC_To_FF_All

Product_Lookup_for_CPQ_FF_All

LKP_Navision_Location_From_FSL_To_FF_All

Assets_Update_Warranty_From_Navision_To_FSL_All

Assets_Update_Locations_From_Navision_To_FSL_All

Assets_Update_Product_From_Navision_To_SFDC_All

Business Logic : The process will sync the Navision Assets Information to Salesfore as per schedule

Task1: Assets_From_SFDC_To_FF

Task	Assets_From_SFDC_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Asset
Target	Flat File
Target Object	Assets_From_SFDC.csv

Data Filter Condition: Asset filtered by Navision_ID__c Is Not Null

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Name	Name
SerialNumber	SerialNumber
Navision_ID__c	Navision_ID__c
Subsidiary__c	Subsidiary__c

Task2: Product_Lookup_for_CPQ_FF

Task	Product_Lookup_for_CPQ_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Product
Target	Flat File
Target Object	Product_Lookup1.csv

Data Filter Condition: Product filtered by RecordTypeId = '012C0000000QFnP'

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Name	Name
IsActive	IsActive
Inventory_Item_ID__c	Inventory_Item_ID__c
Unique_Id__c	Unique_Id__c
Source_System__c	Source_System__c

Task3: LKP_Navision_Location_From_FSL_To_FF

Task	LKP_Navision_Location_From_FSL_To_FF
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	Navision_Location.csv

Data Filter Condition: Location filtered by ERP__c Equals 'Navision'

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Name	RTRIM(Name,'char(39)\|\|s Van')
Email__c	Email__c
Subsidiary__c	Subsidiary__c
Location_ID__c	Location_ID__c

Task4: Assets_Update_Warranty_From_Navision_To_FSL

Task	Assets_Update_Warranty_From_Navision_To_FSL
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	"VTI NL$Service Item"
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Asset

Saved query: Assets_Update_Warranty_From_Navision_To_FSL

SELECT 'NL' country_code,

'NL-'+SI."No." as NavExt_Id,

SI."No." Number,

SI."Customer No." Customer_No,

SI."Installation Date" Installation_Date,

SI."Description" Description,

SI."Item No." Item_No,

SI."Service Item Group Code" Service_Item_Group_Code,

SI."Serial No." Serial_No,

'VTI NL' subsidiary_code,

[Acceptance Date],

(CASE

WHEN SI."Warranty Starting Date (Labor)" <= GetDate() And SI."Warranty Ending Date (Labor)" >= GetDate()

And SI."Warranty Starting Date (Parts)" <= GetDate() And SI."Warranty Ending Date (Parts)" >= GetDate()

And SI."Warranty Starting Date (Trav.)" <= GetDate() And SI."Warranty Ending Date (Trav.)" >= GetDate()

THEN 'LABOR'+','+'PARTS'+','+'TRAVEL'

WHEN SI."Warranty Starting Date (Labor)" <= GetDate() And SI."Warranty Ending Date (Labor)" >= GetDate()

And SI."Warranty Starting Date (Parts)" <= GetDate() And SI."Warranty Ending Date (Parts)" >= GetDate()

THEN 'LABOR'+','+'PARTS'

WHEN SI."Warranty Starting Date (Labor)" <= GetDate() And SI."Warranty Ending Date (Labor)" >= GetDate()

And SI."Warranty Starting Date (Trav.)" <= GetDate() And SI."Warranty Ending Date (Trav.)" >= GetDate()

THEN 'LABOR'+','+'TRAVEL'

WHEN SI."Warranty Starting Date (Parts)" <= GetDate() And SI."Warranty Ending Date (Parts)" >= GetDate()

And SI."Warranty Starting Date (Trav.)" <= GetDate() And SI."Warranty Ending Date (Trav.)" >= GetDate()

THEN 'PARTS'+','+'TRAVEL'

WHEN SI."Warranty Starting Date (Labor)" <= GetDate() And SI."Warranty Ending Date (Labor)" >= GetDate() THEN 'LABOR'

WHEN SI."Warranty Starting Date (Parts)" <= GetDate() And SI."Warranty Ending Date (Parts)" >= GetDate() THEN 'PARTS'

WHEN SI."Warranty Starting Date (Trav.)" <= GetDate() And SI."Warranty Ending Date (Trav.)" >= GetDate() THEN 'TRAVEL'

ELSE 'NO WARRANTY'

END) FSL_Warranty,

SI."Last modified Date" Last_modified_Date,

'EUR' Currency_code,

SI."Contract No." Contract_Number,

SI."Warranty Starting Date (Labor)" Warranty_StartDate_Labour,

SI."Warranty Ending Date (Labor)" Warranty_EndDate_Labour,

SI."Warranty Starting Date (Parts)" Warranty_StartDate_Parts ,

SI."Warranty Ending Date (Parts)" Warranty_EndDate_Parts,

SI."Warranty Starting Date (Trav.)" Warranty_StartDate_Travel,

SI."Warranty Ending Date (Trav.)" Warranty_EndDate_Travel

from "VTI NL$Service Item" SI

Where "Customer No." <> ''

AND (DATEDIFF(day,SI."Last modified Date",getdate()) between 0 and 5

OR DATEDIFF(day,SI."Warranty Ending Date (Labor)",getdate()) between 0 and 5

OR DATEDIFF(day,SI."Warranty Ending Date (Parts)",getdate()) between 0 and 5

OR DATEDIFF(day,SI."Warranty Ending Date (Trav.)",getdate()) between 0 and 5

OR DATEDIFF(day,SI.[Acceptance Date],getdate()) between 0 and 5)

AND SI."No." IS NOT NULL

order by SI."Last modified Date" desc

Source and Target Fields:

Source Fields	Target Fields	Source Mapped fields
NavExt_Id	Id	lookup( NavExt_Id = Navision_ID__c ): Assets_From_SFDC.csv.Id
Acceptance_Date	Warranty__c	FSL_Warranty
FSL_Warranty	Acceptance_Date__c	IIF( NOT (GET_DATE_PART(Acceptance_Date, 'YYYY' )=1753), Acceptance_Date,NULL)
Warranty_StartDate_Labour	ERP__c	'Navision'
Warranty_EndDate_Labour	Labor_Warranty_End_Date__c	IIF( NOT (GET_DATE_PART(Warranty_EndDate_Labour, 'YYYY' )=1753), Warranty_EndDate_Labour,NULL)
Warranty_StartDate_Parts	Labor_Warranty_Start_Date__c	IIF( NOT (GET_DATE_PART(Warranty_StartDate_Labour, 'YYYY' )=1753), Warranty_StartDate_Labour,NULL)
Warranty_EndDate_Parts	Parts_Warranty_End_Date__c	IIF( NOT (GET_DATE_PART(Warranty_EndDate_Parts, 'YYYY' )=1753), Warranty_EndDate_Parts,NULL)
Warranty_StartDate_Travel	Parts_Warranty_Start_Date__c	IIF( NOT (GET_DATE_PART(Warranty_StartDate_Parts, 'YYYY' )=1753), Warranty_StartDate_Parts,NULL)
Warranty_EndDate_Travel	Ship_Warranty_End_Date__c	IIF( NOT (GET_DATE_PART(Warranty_EndDate_Travel, 'YYYY' )=1753), Warranty_EndDate_Travel,NULL)
	Ship_Warranty_Start_Date__c	IIF( NOT (GET_DATE_PART(Warranty_StartDate_Travel, 'YYYY' )=1753), Warranty_StartDate_Travel,NULL)

Task5: Assets_Update_Locations_From_Navision_To_FSL

Task	Assets_Update_Locations_From_Navision_To_FSL
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	"VTI NL$Service Item"
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Asset

Saved Query: Assets_Update_Locations_From_Navision_To_FSL

SELECT

FI.Name AS Location_Name,

FI.City,

FI.Subsidiary,

FI."Primary_Key" Location_ID,

FI.ShipTo_Location,

FI.Customer_No,

FI.Country_Code Country,

FI.Type ,

SI."Serial No." Serial_No,

SI."Customer No." SI_Customer_No,

SI."Ship-to Code" Ship_To_Code,

SI."Service Item Group Code" Service_Item_Group_Code,

SI."Location of Service Item" Location_of_Service_Item,

'NL'+'-'+SI."No." as Ext_Id

FROM "FSL Integration" FI

LEFT JOIN (SELECT "No.","Customer No.","Ship-to Code","Serial No.","Service Item Group Code","Last modified Date","Location of Service Item" FROM "VTI NL$Service Item" ) SI

ON SI."Customer No." = FI."Customer_No" and SI."Ship-to Code"=FI."ShipTo_Location"

WHERE LEN(FI."Customer_No") <> 0

AND FI.Type IN (1,2)

AND FI.Subsidiary = 'VTI NL'

AND FI.Do_Not_Interface = 0

AND DATEDIFF(day,SI."Last modified Date",getdate()) between 0 and 2

AND SI."No." IS NOT NULL

Source and Target Fields:

Source Fields	Target Fields	Source Mapped Fields
Location_ID	Id	lookup( Ext_Id = Navision_ID__c ): Assets_From_SFDC.csv.Id
Ext_Id	LocationId	lookup( Location_ID = Location_ID__c ): Navision_Location.csv.Id

Task6: Assets_Update_Product_From_Navision_To_SFDC

Task	Assets_Update_Product_From_Navision_To_SFDC
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	"VTI NL$Service Item"
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Asset

Saved Query: Assets_Update_Product_From_Navision_To_SFDC

SELECT 'NL' country_code,

'NL-'+SI."No." as NavExt_Id,

SI."No." Number,

SI."Customer No." Customer_No,

SI."Installation Date" Installation_Date,

SI."Description" Description,

SI."Item No." Item_No,

SI."Service Item Group Code" Service_Item_Group_Code,

SI."Serial No." Serial_No,

'VTI NL' subsidiary_code,

SI."Sales Unit Price" Price,

'EUR' Currency_code,

itm."MCC Item" MCC_Item, itm."No." ItmNumber,

(CASE WHEN itm."MCC Item"=0 THEN itm."No." + '-' + 'VTI NL'

WHEN itm."MCC Item"=1 THEN itm."No."

END) Item_Id,

SI."Contract No." Contract_Number,

SI."Last modified Date" Last_modified_Date

from "VTI NL$Service Item" SI

LEFT JOIN (Select "No.","MCC Item" from "VTI NL$Item") itm

ON itm."No." = SI."Item No."

Where SI."Customer No." <> ''

AND DATEDIFF(day,SI."Last modified Date",getdate()) between 0 and 2

AND SI."No." IS NOT NULL

Source and Target Fields:

Source Fields	Target Fields	Source Mapped Fields
NavExt_Id	Id	lookup( NavExt_Id = Navision_ID__c ): Assets_From_SFDC.csv.Id
Item_Id	Product2Id	lookup( Item_Id = Unique_Id__c ): Product_Lookup1.csv.Id
	ERP__c	'Navision'

Task7: Assets_From_SFDC_To_FF_All

Task	Assets_From_SFDC_To_FF_All
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Asset
Target	Flat File
Target Object	Assets_From_SFDC.csv

Data Filter Condition: Asset filtered by Navision_ID__c != Null and Subsidiary__c IN ( 'VTI Sweden','VTI Norway','VTI Spain','VTI Portugal','VTI Switzerland','VTI UK','VTI Eire' )

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Name	Name
SerialNumber	SerialNumber
Navision_ID__c	Navision_ID__c
Subsidiary__c	Subsidiary__c

Task8: Product_Lookup_for_CPQ_FF_All

Task	Product_Lookup_for_CPQ_FF_All
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Product
Target	Flat File
Target Object	Product_Lookup1.csv

Data Filter Condition: Product filtered by RecordTypeId = '012C0000000QFnP' AND Unique_Id__c <> ''

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Name	Name
IsActive	IsActive
Inventory_Item_ID__c	Inventory_Item_ID__c
Unique_Id__c	Unique_Id__c
Source_System__c	Source_System__c

Task9: LKP_Navision_Location_From_FSL_To_FF_All

Task	LKP_Navision_Location_From_FSL_To_FF_All
Source	PROD_SFDC_NA_INTEGRATION
Source Object	Location
Target	Flat File
Target Object	Navision_Location.csv

Data Filter Condition: Location filtered by ERP__c = 'Navision' and Subsidiary__c in ( 'VTI Sweden','VTI Norway','VTI Spain','VTI Portugal','VTI Switzerland','VTI UK','VTI Eire' )

Source and Target Fields:

Target Fields	Source Fields
Id	Id
Name	RTRIM(Name,'char(39)\|\|s Van')
Email__c	Email__c
Subsidiary__c	Subsidiary__c
Location_ID__c	Location_ID__c

Task4: Assets_Update_Warranty_From_Navision_To_FSL_All

Task	Assets_Update_Warranty_From_Navision_To_FSL_All
Source	PROD_SQL_Server_Nav_West_Prod
Source Object	"VTI Norway$Service Item" "VTI Sweden$Service Item" "VTI Spain$Service Item" "VTI Portugal$Service Item" "VTI Switzerland$Service Item" "VTI UK$Service Item" "VTI Eire$Service Item"
Target	PROD_SFDC_NA_INTEGRATION
Target Object	Asset

Saved query: Assets_Update_Warranty_From_Navision_To_FSL_All

SELECT 'NO' country_code, 'NO-'+SI."No." as NavExt_Id, SI."No." Number, SI."Customer No." Customer_No,

SI."Installation Date" Installation_Date, SI."Description" Description, SI."Item No." Item_No, SI."Service Item Group Code" Service_Item_Group_Code,

SI."Serial No." Serial_No, 'VTI Norway' subsidiary_code,SI.[Acceptance Date], (CASE WHEN SI."Warranty Starting Date (Labor)" <= GetDate() And SI."Warranty Ending Date (Labor)" >= GetDate()

And SI."Warranty Starting Date (Parts)" <= GetDate() And SI."Warranty Ending Date (Parts)" >= GetDate() And SI."Warranty Starting Date (Trav.)" <= GetDate()

And SI."Warranty Ending Date (Trav.)" >= GetDate() THEN 'LABOR'+','+'PARTS'+','+'TRAVEL' WHEN SI."Warranty Starting Date (Labor)" <= GetDate()

And SI."Warranty Ending Date (Labor)" >= GetDate() And SI."Warranty Starting Date (Parts)" <= GetDate() And SI."Warranty Ending Date (Parts)" >= GetDate() THEN 'LABOR'+','+'PARTS'

WHEN SI."Warranty Starting Date (Labor)" <= GetDate() And SI."Warranty Ending Date (Labor)" >= GetDate() And SI."Warranty Starting Date (Trav.)" <= GetDate()

And SI."Warranty Ending Date (Trav.)" >= GetDate() THEN 'LABOR'+','+'TRAVEL' WHEN SI."Warranty Starting Date (Parts)" <= GetDate() And SI."Warranty Ending Date (Parts)" >= GetDate()

And SI."Warranty Starting Date (Trav.)" <= GetDate() And SI."Warranty Ending Date (Trav.)" >= GetDate() THEN 'PARTS'+','+'TRAVEL'

WHEN SI."Warranty Starting Date (Labor)" <= GetDate() And SI."Warranty Ending Date (Labor)" >= GetDate() THEN 'LABOR' WHEN SI."Warranty Starting Date (Parts)" <= GetDate()

And SI."Warranty Ending Date (Parts)" >= GetDate() THEN 'PARTS' WHEN SI."Warranty Starting Date (Trav.)" <= GetDate() And SI."Warranty Ending Date (Trav.)" >= GetDate()

THEN 'TRAVEL' ELSE 'NO WARRANTY' END) FSL_Warranty, SI."Last modified Date" Last_modified_Date, 'NOK' Currency_code, SI."Contract No." Contract_Number,

SI."Warranty Starting Date (Labor)" Warranty_StartDate_Labour, SI."Warranty Ending Date (Labor)" Warranty_EndDate_Labour, SI."Warranty Starting Date (Parts)"

Warranty_StartDate_Parts , SI."Warranty Ending Date (Parts)" Warranty_EndDate_Parts, SI."Warranty Starting Date (Trav.)" Warranty_StartDate_Travel,

SI."Warranty Ending Date (Trav.)" Warranty_EndDate_Travel from "VTI Norway$Service Item" SI Where "Customer No." <> ''

AND (DATEDIFF(day,SI."Last modified Date",getdate()) between 0 and 5

OR DATEDIFF(day,SI."Warranty Ending Date (Labor)",getdate()) between 0 and 5

OR DATEDIFF(day,SI."Warranty Ending Date (Parts)",getdate()) between 0 and 5

OR DATEDIFF(day,SI."Warranty Ending Date (Trav.)",getdate()) between 0 and 5

OR DATEDIFF(day,SI.[Acceptance Date],getdate()) between 0 and 5)

AND SI."No." IS NOT NULL

UNION

SELECT 'SE' country_code, 'SE-'+SI."No." as NavExt_Id, SI."No." Number, SI."Customer No." Customer_No,

SI."Installation Date" Installation_Date, SI."Description" Description, SI."Item No." Item_No, SI."Service Item Group Code" Service_Item_Group_Code,

SI."Serial No." Serial_No, 'VTI Sweden' subsidiary_code, SI.[Acceptance Date], (CASE WHEN SI."Warranty Starting Date (Labor)" <= GetDate() And SI."Warranty Ending Date (Labor)" >= GetDate()