ALEX_USER (Staging table)

Column name Data type Required

Default

Update able

Data target Comments
EXTERNAL_KEY** VARCHAR (255) Yes* No ALCO_EXTERNAL_ KEY.EXTERNAL_KEY External key for the user - primary key
EMPLOYEE_NUMBER VARCHAR (255) No Yes ALCO_USER.EMPLOYEE_NUMBER Employee number
COST_CENTER_EKEY VARCHAR (255) Yes Yes ALCO_USER.COST_CENTER_ID Cost center external key
USER_FIRST_NAME VARCHAR (255) Yes* Yes ALCO_USER.USER_FIRST_NAME First name
USER_MIDDLE_INITIAL VARCHAR (1) No Yes ALCO_USER.USER_MIDDLE_INITIAL Middle initial
USER_LAST_NAME VARCHAR (255) Yes* Yes ALCO_USER.USER_MIDDLE_INITIAL Last name
USER_PASSWORD VARCHAR (255) Yes* No ALCO_USER.USER_PASSWORD User password
USER_TITLE VARCHAR (255) No Yes ALCO_USER.USER_TITLE Job title
USER_LOCATION_ NAME VARCHAR (255) No ALCO_USER. USER_LOCATION No Location
USER_TELEPHONE VARCHAR (255) No No ALCO_USER. USER_TELEPHONE Phone number
USER_MAILSTOP VARCHAR (255) No No ALCO_USER. USER_MAILSTOP Mail stop code
USER_EMAIL_ ADDRESS VARCHAR (255) No No No ALCO_USER. USER_EMAIL_ ADDRESS E-mail address
USER_EMAIL_ PRODUCT VARCHAR (255) No Yes ALCO_USER. EMAIL_PRODUCT E-mail product
USER_LOGIN_NAME** VARCHAR (255) Yes* No ALCO_USER. USER_LOGIN Login name
FAX_NUMBER VARCHAR (255) No Yes ALCO_USER. USER_FAX_ NUMBER Fax Number
PAYROLL_SYSTEM_ CODE VARCHAR (255) No Yes ALCO_USER. PAYROLL_SYSTEM_CODE Payroll system code
ACTIVE_STATUS SMALLINT Yes* Yes ALCO_USER. ACTIVE_STATUS User's active status flag. 1 = Active 0 = Inactive
USER_SSN VARCHAR (40) No Yes Social security number ALCO_USER. SSN
CURRENCY_EKEY VARCHAR (255) Yes* Yes ALCO_USER. CURRENCY_FMT_DEFN_ID Default currency external key
COUNTRY_EKEY VARCHAR (255) No Yes ALCO_USER. COUNTRY_ID Country external key
DEFAULT_APPROVER_USER_EKEY VARCHAR (255) No Yes ALWF_USER_HIERARCHY. BOSS_USER_ID External key of the approver
CREATION_DATE DATE Yes* No ALCO_USER.CREATION_DATE Creation date of the user.
CREDIT_CARD_EKEY VARCHAR (255) No Yes ALCO_EXTERNAL_KEY.EXTERNAL_ KEY External key of user credit card
CREDIT_CARD_ NUMBER VARCHAR (40) No ALCO_USER. SSN Yes ALCC_CARD. CCARD_ACCOUNT User credit card number
CC_PROCESSOR_ EKEY VARCHAR (255) No Yes ALCC_CARD.CC_PROCESSOR_ID Above credit card's credit card processor external key
CC_PAYMENT_TYPE_ EKEY VARCHAR (255) No Yes ALCC_CARD. PAYMENT_TYPE_ ID Above credit card's payment type external key.
SET_OF_BOOKS_EKEY VARCHAR (255) No Yes Set of books external key ALCO_USER.SET_OF_BOOKS_ID
AUTH_AMOUNT NUMERIC (20, 10) No Yes ALCO_USER.AUTH_AMOUNT Authorized amount
AUTH_AMOUNT_ CURRENCY_EKEY VARCHAR (255) No - if you enter a value for AUTH_ AMOUNT, this is required. Yes ALCO_USER.AUTH_AMOUNT_ID Currency external key for authorized amount
PR_AUTH_AMOUNT NUMERIC (20, 10) No Yes ALCO_USER.SET_OF_BOOKS_ID ALCO_USER.PR_ AUTH_AMOUNT PR authorized amount
PR_AUTH_AMOUNT_ CURRENCY_EKEY VARCHAR (255)

No-if you enter

a value for

PR_AUTH

_AMOUNT, this is

required.

Yes ALCO_USER.PR_ AUTH_AMOUNT_ID Currency external key for PR authorized amount
SYSTEM_CODE_1 VARCHAR (255) No Yes ALCO_USER. SYSTEM_CODE_1 System Code 1
SYSTEM_CODE_2 VARCHAR (255) No Yes ALCO_USER. SYSTEM_CODE_2 System Code 2
SYSTEM_CODE_3 VARCHAR (255) No Yes ALCO_USER. SYSTEM_CODE_3 System Code 3
SYSTEM_CODE_4 VARCHAR (255) No Yes ALCO_USER. SYSTEM_CODE_4 System Code 4
SYSTEM_CODE_5 VARCHAR (255) No Yes ALCO_USER. SYSTEM_CODE_5 System Code 5
SYSTEM_CODE_6 VARCHAR (255) No Yes ALCO_USER. SYSTEM_CODE_6 System Code 6
SYSTEM_CODE_7 VARCHAR (255) No Yes ALCO_USER. SYSTEM_CODE_7 System Code 7
SYSTEM_CODE_8 VARCHAR (255) No Yes ALCO_USER. SYSTEM_CODE_8 System Code 8
START_DATE DATETIME No Yes ALCO_USER. START_DATE Start date
END_DATE DATETIME No Yes ALCO_USER.END_DATE End date
SYSTEM_DATE_1 DATETIME No Yes ALCO_USER.SYSTEM_DATE_1 System date 1
SYSTEM_DATE_2 DATETIME No Yes ALCO_USER.SYSTEM_DATE_2 System date 2
OUT_OF_OFFICE INTEGER (1) Yes No ALCO_USER.OUT_OF_OFFICE 0 means user is in office 1 means user is out of office
PROXY_CAN_SUBMIT SMALLINT No No

ALCO_USER_

PREFERENCE.

PROXY_CAN_SUBMIT

Whether the proxies for the user can submit documents 0 No 1 Yes
AUDIT_GROUP_EKEY VARCHAR (255) No Yes ALCO_USER.AUDIT_GROUP_ID Used only for Expense Management Implementation. Points to alps_audit_group_staging to indicate the audit group a user belongs to. Retained only for backward compatibility of pre-6.1 Extensity Advantage customers.
IS_HTML_CLIENT_ USER SMALLINT Yes Yes ALCO_USER.IS_ THIN_CLIENT_USER

A bit flag that determines whether the user can access the HTML client. These are the individual values for each application:

  • 0 - No HTML client
  • 1 - ER HTML
  • 2 - TP HTML
  • 4 - TS HTML
  • 16 - PR HTML
  • 31 - HTML for all

If more than one application is using the HTML client, the values for various applications need to be added up. For example: If a customer is using ER and TP applications in the HTML client mode, then the value to provide is 3 (which is 1 +2). If a customer is using ER + PR applications in HTML client mode then they need to provide 17 (which is 1 + 16).

The only exception is when all four applications are being used in the HTML mode. In that case, a value of 31 needs to be provided. HISTORY: At one point there was a Procurement Application that was deprecated in Rel6.1 which was given a value of 8 and is reserved for that application.

To get the value of 31 you need the assigned values of the presently available applications plus 8 to account for deprecated Procurement application. Note: As of Release 6.5.0, HTML client is supported only for ER, and hence the only applicable values for this column are 0 and 1.

SECOND_CURRENCY_EKEY VARCHAR(255) No Yes ALCO_USER.SECOND_CURRENCY_ID Some users may need to be reimbursed in either of two currencies. This column stores the secondary currency while CURRENCY_EKEY stores the primary
PROXY_REVIEWER_CAN_SUBMIT SMALLINT No Yes ALCO_USER_PREFERENCE.PROXY_REVIEWER_CAN_SUBMIT Whether proxy review can submit documents 0 No 1 Yes
COMMUTE_MILES NUMERIC(26,16) No Yes ALCO_USER.COMMUTE_MILES The commute miles.
IDENTITY2 VARCHAR(36) No Yes ALCO_USER.IDENTITY2 Database column used to store the Identity2 user claims credentials. This is primary used for SAML2 SSO.
PREFERRED_TIMEZONE VARCHAR(255) No Yes ALCO_USER.PREFERRED_TIMEZONE Preferred timezone of the user
USER_EMAIL_ADDRESS_2 VARCHAR(255) No Yes ALCO_USER.USER_EMAIL_ADDRESS_2 User alternate email address
USER_EMAIL_ADDRESS_3 VARCHAR(255) No Yes ALCO_USER.USER_EMAIL_ADDRESS_3 User alternate email address
PREFERRED_LOCALE VARCHAR(255) No Yes ALCO_USER.PREFERRED_LOCALE Stores the user email locale
FX_SOURCE_EKEY VARCHAR(255) No Yes ALCO_USER.FX_RATE_SOURCE_ID The ekey of the exchange rate source
LONG_USER_ID VARCHAR(128) No Yes ALCO_USER.LONG_USER_ID Stores the userId
SECURITY_BOD_CONTROL VARCHAR(16) No Yes ALCO_USER.SECURITY_BOD_CONTROL Stores whether a security user bod should be published
DEFAULT_MILEAGE_RATE_

ID_EKEY

VARCHAR(255) No Yes ALCO_USER.DEFAULT_MILEAGE_RATE_ID Stores preassigned mileage rate ekey.
DEFAULT_LOCATION_EKEY VARCHAR(255) No Yes ALCO_USER.DEFAULT_LOCATION_ID Ekey to represent the default location

*System required field

** Value must be unique