EDI Project Data Field Mapping

This is the mapping information on equivalent fields between the destination ICPAM database and Source DB attributes.

Personal Interface Data (Mandatory Fields)

Column Name

Type

Length

Mandatory/Comments

Updated TS

Datetime

 

Mandatory

Created TS

Datetime

 

Mandatory

first_name

String

255

Mandatory

last_name

String

255

Mandatory

govt_id

String

255

Mandatory (employee ID)

govt_id_spec

String

255

Mandatory. Default mapping through GUI

emp_status

String

255

Mandatory. Transform values through GUI

Site

String

24

Default mapping through GUI

Photo

mediumblob (binary)

 

JPEG supported

Personal Interface Table (Optional Fields)

Column Name

Type

Length

Optional/Comments

middle_name

String

255

Optional

Nickname

String

255

Optional

name_title

String

255

Optional

name_suffix

String

255

Optional

title_in_org

String

255

Optional

Org

String

255

Optional

Dept

String

255

Optional

user_id

String

255

Optional

emp_type

String

255

Optional

dob

Date

 

Optional

doh

Date

 

Optional

employee_number

String

255

Optional

comments

String

255

Optional

home_addr_line1

String

255

Optional

home_addr_line2

String

255

Optional

home_addr_city

String

255

Optional

home_addr_state_province

String

255

Optional

home_addr_country

String

255

Optional

work_addr_line1

String

255

Optional

work_addr_line2

String

255

Optional

work_addr_city

String

255

Optional

work_addr_state_province

String

255

Optional

work_addr_country

String

255

Optional

other_addr_line1

String

255

Optional

other_addr_line2

String

255

Optional

other_addr_city

String

255

Optional

other_addr_state_province

String

255

Optional

other_addr_countr

String

255

Optional

home_phone

String

255

Optional

work_phone

String

255

Optional

mobile_phone

String

255

Optional

other_phone

String

255

Optional

Fax

String

255

Optional

primary_email

String

255

Optional

secondary_email

String

255

Optional

other_email

String

255

Optional

customAttr 0-19

String

255

Optional

customDateAttr 0-3

String

255

Optional

Organization Interface Table

Column Name

Type

Length

Mandatory/Optional/Comments

Updated TS

Datetime

 

Mandatory

Created TS

Datetime

 

Mandatory

name

String

255

Mandatory

comments

String

255

Optional

Department Interface Table

Column Name

Type

Length

Mandatory/Optional/Comments

Updated TS

Datetime

 

Mandatory

Created TS

Datetime

 

Mandatory

name

String

255

Mandatory

OrgName

String

255

Mandatory (organization and department has parent child relationship)

comments

String

255

Optional

Badge Interface Table

Column Name

Type

Length

Mandatory/Comments

Updated TS

Datetime

 

Mandatory

Created TS

Datetime

 

Mandatory

badgeID

String

255

Mandatory

Validity String

255

 

Mandatory

role

String

255

Mandatory. Transform values through GUI

assignedTo

String

255

Optional. To get the badge import to associate to the appropriate users in the ICPAM automatically.

Please refer to the detailed process below.

credTemplateId

String

255

Mandatory

facilityCode

Int

11

Optional

pincode

String

255

Optional

isExemptOk

bit

1

Optional

useLimit

Int

11

Optional

activationDate

Date

 

Optional

expirationDate

Date

 

Optional

Comments

String

255

Optional

tempDeactivationDate

Date

 

Optional

tempDeactivationDays

Int

11

Optional

antiPassbackExempt

bit

1

Optional

useADADoorStrikeTime

bit

1

Optional

executiveCredential

bit

1

Optional

customAttr 0-7

String

255

Optional

customDateAttr 0-1

Date

 

Optional

 

See also:

Creating SQL and Oracle Database Integration Projects Using EDI Studio

Synchronizing Data Using Enterprise Data Integration (EDI)