Page tree
Skip to end of metadata
Go to start of metadata

Introduction

The tables shown on this page describe how the GCS data fields are mapped and transformed to generate data for the Unanet import.

Note:  The attached mapping document refers to versions of GCS interface prior to GCSConnect 1.0, which is all installations prior to 5/1/2015:  GCS Mapping Document January 2012.xlsx

Approval Groups and Shells

Z_OUTPUT_APPROVAL_GROUPS_TIME
Z_OUTPUT_APPROVAL_GROUPS_EXPENSE
Z_OUTPUT_APPROVAL_GROUPS_TIME_SHELL
Z_OUTPUT_APPROVAL_GROUPS_EXPENSE_SHELL


For Approval Groups, these two queries read through all employee records in the Employee Time & Expense file (PR43EMF).  The Employee Time & Expense file contains the Employee ID of each employee’s supervisor.  The query creates a row for each distinct Supervisor.

For Approval Group Shells, the logic works exactly the same as for Approval Groups, except that it does not include the Approver field.

Approval Group

GCS Screen

GCS Screen Field

Interface Logic

Unanet Field

Client-Specific Changes or Notes

Employee Time & Expense (PR43EMF)

Supervisor

Approval Group Name: [none_PR01EMF]![EMF_LAST_NAME] & ", " & [none_PR01EMF]![EMF_FIRST_NAME] & "-TIME"

Takes the Supervisor field, and appends '-TIME' or '-EXPENSE' to end of comma-separated last name and first name, so 'SMITH,JOHN-TIME', for example.

Approval Group Name

 

 

Supervisor’s Unanet Username from “Q_UTILITY_USERNAME”

Approver: [Q_UTILITY_USERNAME]![*Username]

Supervisor’s Unanet Username. Please see the Q_UTILITY_USERNAME section below for information on how the Username is derived.

Approver

 
 Supervisor’s Unanet Username from the “USERNAME TABLE”

Description: [none_PR01EMF]![EMF_FIRST_NAME] & " " & [none_PR01EMF]![EMF_LAST_NAME]

Supervisor's First and Last name, separated by a space, like 'John Smith'

Description 


Assignments

Assignments in GCS Premier come from the Contract Work Force File (CT08CWF).  (This is similar to the MLRT in Dynamics SL).  Labor Category, Project, and Bill Rate are all defined.

ASSIGNMENTS

GCS Screen

GCS Screen Field

Interface Logic

Unanet Field

Client-Specific Changes or Notes

Contract Master File (CT01CMF)

Organization will come from another query that contains several possibilities for organization source.

*Organization: IIf(Len([none_CT01CMF]![CMF_DIV_NUM])=1,"0" & [none_CT01CMF]![CMF_DIV_NUM],[none_CT01CMF]![CMF_DIV_NUM])

If the Division Number from the Contract Master File is 1 character, then return the Division Number preceded by a zero. Otherwise, just return the Division Number.

Organization

 

Contract Master File (CT01CMF)

Contract Account and Contract Sub-Account Number

Project Code: [none_CT01CMF]![CMF_ACCT] & "-" & [none_CT01CMF]![CMF_SUB_ACCT]

Contract Account Number, then dash, then Contract Sub-Account Number

Project Code

 

 

 

Task Name: Null

Left blank.

Task Name

 

 

Username will come from another query that contains several possibilities for Username source.

Username: [Q_UTILITY_USERNAME]![*Username]

Username

 

 

Contract Work Force is not date specific in GCS Premier, so no dates are sent to Unanet

Begin Date: Null

Begin Date

 

 

 

End Date: Null

End Date

 

 

 

Delete Flag: Null

Delete Flag

 

Contract Work Force (CT08CWF)

Contract Work Force Budget Hours

Budget Hours: IIf([none_CT08CWF]![CWF_BUDGET_HRS]=0,Null,[none_CT08CWF]![CWF_BUDGET_HRS])

Budget Hours

 

 

 

Exceed Budget: Null

Exceed Budget

 

 

Bill Rate Comes From Labor Category

Bill Rate: "L"

Bill Rate

 

 

Cost Rate Comes From Person Record

Cost Rate: "P"

Cost Rate

 

 

 

Bill Org: Null

Bill Org

 

 

 

Cost Org: Null

Cost Org

 

 

Labor Category is based on the assigned labor category in the Contract Work File.

Labor Category: [None_CT05JCT]![JCT_JOB_CAT] & " - " & [None_CT05JCT]![JCT_DESCRIP]

Labor Category

 

 

 

Location: Null

Location

 

 

 

ETC_Hours: Null

ETC Hours

 

 

 

Use_WBS_Date: "Y"

Use WBS Date

 

 

 

Cost Structure: Null

Cost Structure

 

 

 

Cost Element: Null

Cost Element

 

 

Labor Categories (Master)

Z_OUTPUT_PLC_MAIN

This query takes every two-character GCS Job Category field from the Job Category Table, and appends the Description field.  The Job Category Table contains both Company-Wide and Job-Specific labor categories.  This query pulls both types.  In addition, Job Specific categories can be assigned to individual tasks or to the “ALL” (“ZZZ”) task level.  When assigned to the “ZZZ” task, Labor Categories are applicable to all tasks within the contract, but are superseded by labor categories assigned to the individual task. 

LABOR CATEGORY

GCS Screen

GCS Screen Field

Interface Logic

Unanet Import Field

Client-Specific Changes or Notes

Labor Category File – CT05JCT

Job Category Code

[Q_TABLE_LOCAL_GLC_PLC]![PLC]

The above Q table derives the 'PLC' value by using this query:

[None_CT05JCT]![JCT_JOB_CAT] & " - " & [None_CT05JCT]![JCT_DESCRIP]

Takes 2-character Job Category Code, and appends the Job Category Description, separated by a dash.

Labor_Category

 
   Delete 

 

Description

[none_CT05JCT]![JCT_DESCRIP]

Category Description

Description

 
   Bill_Rate 
   Cost_Rate 
   External_System_Code 
   Active 
   Effective_Date 
     
     

 

Contract

[none_CT05JCT]![JCT_ACCT] & "-" & [none_CT05JCT]![JCT_SUB_ACCT]

Contract Account Number, then dash, then Contract Sub-Account Number

Account Number

 

T&M Billing Rates File – CT04LBF

Billing Rate/Hour

[none_CT04LBF]![LBF_BILLING_RATE]

Takes the Billing Rate/Hour value from the Overall Labor Budget File

Bill Rate

 
 

‘Y’

Active

Active Flag

 

 

Labor Categories (Project)

This query takes every two-character GCS Job Category field from the Job Category Table, and appends the Description field.  The Job Category Table contains both Company-Wide and Job-Specific labor categories, but this query pulls only the Job Categories for which there is a Project specified.

Three fields at the right are included as FYI for the user:  (CMF_ACCT, CMF_SUB_ACCT, and JCT_JOB_CAT)

LABOR CATEGORY

GCS Screen

GCS Screen Field

Interface Logic

Unanet Import Field

Client-Specific Changes or Notes

Labor Category File – CT05JCT

Job Category Code

[none_CT05JCT]![JCT_JOB_CAT] & " - " & [none_CT05JCT]![JCT_DESCRIP]

Two-character Job Category Code appended with the Job Category Description, separated by a dash.

Labor_Category

 
----

[Q_UTILITY_ORG_PROJECT]![*ORG]

Organization Code from Org Project utility table

Project_Org_Code 
----

[Q_UTILITY_ORG_PROJECT]![Project_Code]

Project Code from Org Project utility table

Project_Code 
----nullDelete 
----nullCost_Rate 
Labor Budget File – CT04LBFBilling Rate/Hour

IIf([none_CT04LBF]![LBF_BILLING_RATE] Is Null,0,[none_CT04LBF]![LBF_BILLING_RATE])

Takes Bill rate from the Budget File table. If the Bill rate is null, then return 0.

Bill_Rate 
----nullEffective_Date 
----'N'Default_to_Master_Rate 


ODCs

ODCs from the GCS Premier General Ledger File (GL02GLF) are exported and moved to Unanet as part of the integration.  Expenses that originate in Unanet that are imported into GCS must be excluded from the interface so they are not double counted.  Expenses originating from Unanet are marked with the string, ‘UNANET’ in the REFERENCE-1 field when exported from Unanet.  These records should be omitted from the interface. 

ODCs from GCS Premier are pulled into separate files from the current period and next period as determined by the G/L Config File (GL03GLC).   If the next period is the first period of next year, the ODCs are pulled from the “Next Year G/L File” (GL09GLF2) instead of the current year G/L file (GL02GLF).  Records sent to Unanet are marked in the “COMMENTS” field with identifying fields to prevent a given ODC from being sent more than once to Unanet.   

All expenses are first exported from Unanet (B_IMPORTED_UNANET_Expense) and the query uses these values for comparison so that it does not bring over duplicate ODCs.

Regarding ODCs for Indirects, this is not included in the integration.  All versions of the Interface specifically exclude any Indirect Costs from coming over.   Indirect Accounts in GCS do not have a suffix, so for this flow to be implemented as a custom change, logic that ties the expense to a Unanet Expense Type would have to be written for each Indirect Account.  

 

ODC

GCS Screen

GCS Screen Field

Interface Logic

Unanet Field

Client-Specific Changes or Notes

 

 

*Owner: "Unanet_Admin"

Username

 

 

 

Purpose: "Historical ODCs"

Purpose

 

 

 

Location: "Contract to Date"

Location

 

 

 

Project Organization Code: [Q_UTILITY_ODC_Projects]![*project_org_code]

Project Organization

 

General Ledger File – GL02GLF

GL Account Number & GL Sub-Account Number

Project Code: [Q_UTILITY_ODC_Projects]![ACCT1] & "-" & [Q_UTILITY_ODC_Projects]![ACCT2]

Project Code

 

 

 

Null

Task Name

 

 

If the expense comes through A/P,  use the Transaction Date from the Voucher Distribution File (AP06DIST), otherwise use the Accounting Period End Date.

Expense Date: IIf([none_AP06DIST]![APD_TRANS_DATE_8] Is Null,[Q_UTILITY_AUTOLOAD_PERIODS]![Period_End_Date],[none_AP06DIST]![APD_TRANS_DATE_8])

Expense Date

 

 

GCS Premier uses a 2-digit suffix (which is the third segment in the G/L account number) to identify ODC expense types.  In addition, there is a two-character transaction code which further breaks down the expense.  The suffix/trans code combinations are mapped in A_TABLE_CONFIG_SUFFIX_TRANSCODE with their corresponding Unanet Expense Type

Expense Type: [A_TABLE_CONFIG_SUFFIX_TRANSCODE]![Expense Type]

Expense Type

 

 

 

Currency Code: "USD"

Currency Code

 

General Ledger File (GL02GLF)

Transaction Amount

Amount: ([none_GL02GLF]![GLF_AMT])

Amount

 

 

 

Exchange Rate: 1

Exchange Rate

 

 

 

Payment Method: "Company Paid"

Payment Method

 

 

 

Project Type: [Q_UTILITY_ODC_Projects]![project_type]

Project Type

 

General Ledger File (GL02GLF)

Period Number + Fiscal Year + Posting Sequence Number + Record Number + Account + Sub-Account + Suffix Account + Posting Source + Voucher Number + Transaction Amount

Comments: [none_GL02GLF]![GLF_PD_NUM] & "." & [Q_UTILITY_AUTOLOAD_PERIODS]![Fiscal Year] & "." & [none_GL02GLF]![GLF_SEQ_NUM] & "." & [none_GL02GLF]![GLF_REC_NUM] & "." & [none_GL02GLF]![GLF_ACCOUNT] & "." & [none_GL02GLF]![GLF_SUB_ACCOUNT] & "." & [none_GL02GLF]![GLF_SFQ_UTILITY_ACCOUNT] & "." & [none_GL02GLF]![GLF_SOURCE] & "." & [none_GL02GLF]![GLF_VOUCHER_NO] & "." & [none_GL02GLF]![GLF_AMT]

 

This comment field is the unique identifier used to identify each ODC, to avoid flowing over any duplicates.

Comments

 

 

 

Receipt Provided: "Y"

Receipt Provided

 

 

 

No Receipt Reason: Null

No Receipt Reason

 

Vendor Master File (AP02VMF)

If transaction comes from A/P, use the Vendor Name, otherwise leave blank.

Vendor Name: IIf([none_AP02VMF]![VMF_VENDOR_NAME] Is Null,"No Vendor",[none_AP02VMF]![VMF_VENDOR_NAME])

Vendor Name

 

 

 

VAT Amount: Null

VAT Amount

 

 

 

VAT Location: Null

VAT Location

 

 

Accounting Period End Date

Post Date: [Q_UTILITY_AUTOLOAD_PERIODS]!Period_End_Date

Post Date

 

 


ODCs - Historical Data 

Same file format and logic /mapping as for regular ODC query.  Enter fiscal year into PGL_FISCAL_YEAR.  Pulls individual ODC transactions, just like the regular ODC query.  Query is called ZZ_TIME_EXPORT_PRIOR_YEARS.  

Organizations

Pulls each distinct Client from the Contract Master File to create an Organizations import.

ORGANIZATIONS

GCS Screen

GCS Screen Field

Interface Logic

Unanet Import Field

Client-Specific Changes or Notes

Contract Master File – CT01CMF

 

Division Number or Department

Organizations: [none_CT01CMF]![CMF_CLIENT]

Organization_Code 
  Organization Name: [none_CT01CMF]![CMF_CLIENT]Organization_Name 
  NULLParent_Org_Code 
  Hardcoded as the word 'PROJECTS'. Can be changed.Org_Type 
  NULLSize 
  NULLAccount_Number 
  NULLSIC_Code 
  NULLClassification 
  NULLIndustry 
  NULLSector 
  NULLStock_Symbol 
  NULLURL 
  NULLUser01 - User10 
  NULLDelete 

 

People

The GCS Employee record is used to update general employee information like first and last name.  It is also used to update the employee pay rate schedule in Unanet. 

All people from Unanet are first exported for comparison purposes.  If newly-added person, then flow the full set of data ; If already-existing person, then flow only a subset of the data for that person.  

EMPLOYEES

GCS Screen

GCS Screen Field

Interface Logic

Unanet Field

Client-Specific Changes or Notes

Employee Master File – PR01EMF

and

Employee Time & Expense File – PR43EMF

First Name, Last Name, Email Address

*Username: IIf([B_IMPORTED_UNANET_Person]![*username] Is Null,[Q_UTILITY_USERNAME]![*Username],IIf([B_IMPORTED_UNANET_Person]![email]<>[none_PR43EMF]![EMF4_TE_EMAIL_ADDR],"!RENAME!," & [B_IMPORTED_UNANET_Person]![*username] & "," & [Q_UTILITY_USERNAME]![*Username],[Q_UTILITY_USERNAME]![*Username]))

Q_UTILITY_USERNAME: *Username: IIf([none_PR43EMF]![EMF4_TE_EMAIL_ADDR]="" Or [none_PR43EMF]![EMF4_TE_EMAIL_ADDR] Is Null,[none_PR01EMF]![EMF_FIRST_NAME] & '.' & [none_PR01EMF]![EMF_LAST_NAME],UCase(Left([none_PR43EMF]![EMF4_TE_EMAIL_ADDR],InStr([none_PR43EMF]![EMF4_TE_EMAIL_ADDR],"@")-1)))

If the person does not yet exist in Unanet, then the username will be derived as follows:

    • Use the Employee's Email Address, taking everything before the '@' sign. If the Email Address doesn't exist, then use Firstname.Lastname.

If the person does exist in Unanet, and the user's Unanet email address does not match their GCS/EMF email address, then RENAME the user.

User Name

 
 

First Name

First Name: [none_PR01EMF]![EMF_FIRST_NAME]

Employee's First Name

First Name

 

 

Last Name

 

Last Name: [none_PR01EMF]![EMF_LAST_NAME]

Employee's Last Name

Last Name

 

 

Middle Initial

Middle Initial: [none_PR01EMF]![EMF_MID_INITIAL]

Employee's Middle Initial

Middle Initial

 

 

 

NULL

Suffix

 

 

 

NULL

Nick Name

 

 

Salaried/Hourly

IIf([none_PR01EMF]![EMF_EMPL_TYPE]="C","X",IIf([none_PR01EMF]![EMF_PAY_HRLY_SAL]="S","E","N"))

If contactor, send 'X' (for non-employee). If not a contractor, and if salaried, send 'E', else send 'N' (for non-exempt).

Exempt Status

 

 

 

IIf([Q_UTILITY_IS_PROJ_MANAGER]!EMF_EMPL_ID Is Not Null And [Q_UTILITY_IS_A_SUPERVISOR]!EMF4_TE_SPVSR Is Not Null,"!APPEND!,timesheetUser,unaSourceUser" & [Q_UTILITY_ONE_TIME_VALUES_EMP]!Roles & ",manager,projectViewer",

IIf([Q_UTILITY_IS_PROJ_MANAGER]!EMF_EMPL_ID Is Null And [Q_UTILITY_IS_A_SUPERVISOR]!EMF4_TE_SPVSR Is Not Null,"!APPEND!,timesheetUser,unaSourceUser" & ",manager,viewer",

IIf([Q_UTILITY_IS_PROJ_MANAGER]!EMF_EMPL_ID Is Not Null And [Q_UTILITY_IS_A_SUPERVISOR]!EMF4_TE_SPVSR Is Null,"!APPEND!,timesheetUser,unaSourceUser" & ",manager,projectPlanner", "!APPEND!,timesheetUser,unaSourceUser")))

 

  • If person is PM and a Supervisor, give them "timesheetUser,unaSourceUser" + the basic roles are defined by the consultant in A_TABLE_CONFIG_EMP.EMP_ROLES + ",manager,projectViewer".

  • If person is a Supervisor only, give them "timesheetUser,unaSourceUser" + the basic roles are defined by the consultant in A_TABLE_CONFIG_EMP.EMP_ROLES + ",manager,viewer". --> Should be 'viewer' only.
  • If person is a PM only, give them "timesheetUser,unaSourceUser" + the basic roles are defined by the consultant in A_TABLE_CONFIG_EMP.EMP_ROLES + ",manager,projectPlanner" --> 'projectPlanner' should be 'projectViewer' and 'manager' should be removed.

  • If person is neither PM nor Supervisor, give them "timesheetUser,unaSourceUser".


** Issues to be addressed:

If Supervisor Only – Don't need 'viewer', as it is redundant with 'manager' – If you are 'manager', you already have 'viewer' capabilities.

If PM Only – Don't want 'manager', as this is for supervisors ; projectPlanner is not a valid Unanet role: Should be projectViewer.

A_TABLE_CONFIG_EMP – Not being used well – Should allow consultants to enter even the basic roles here, rather than hardcoding in the query.

Roles

 

 

From Employee Defaults

[A_TABLE_CONFIG_EMPL]![EMP_TIME_PERIOD]

Value configured by user in A_TABLE_CONFIG_EMPL table.

Time Period

 

 

Default Pay Type

[A_TABLE_CONFIG_EMPL]![EMP_PAY_CODE]

Value configured by user in A_TABLE_CONFIG_EMPL table.

Pay Code

 

 

GCS Premier Default Hours Increment.  GCS does not support quarter hours (.25).

[A_TABLE_CONFIG_EMPL]![EMP_HOUR_INCREMENT]

Value configured by user in A_TABLE_CONFIG_EMPL table.

Hour Increment

 

 

Pulled from Supervisor’s Name identified in Employee Time & Expense File (PR43EMF).

IIf(B_IMPORTED_UNANET_Person![*username] Is Not Null, B_IMPORTED_UNANET_Person!expense_approval_group, IIf(none_PR01EMF!EMF_EMPL_TYPE="C","1099",IIf(none_PR01EMF!EMF_STATUS="I","INACTIVE",IIf([Q_UTILITY_APPROVAL_GROUPS_Match]![Approval Group Name] Is Null,"INACTIVE",[Q_UTILITY_APPROVAL_GROUPS_Match]![Approval Group Name] & "-EXPENSE"))))

If Person exists in Unanet already, then use his Expense Approval Group from Unanet. If he does not exist yet in Unanet, then if he is an Employee Type 'C', then send '1099', else if his status is 'I', then send 'INACTIVE'. If he is not a type 'C', then determine his approval group name based his Supervisor's name in the Time & Expense File.

Expense Approval Group

 

 

Employee ID

[none_PR01EMF]![EMF_EMPL_ID]

Employee ID

Person Code

 

 

 

NULL

Identification Code 1

 

 

 

NULL

Identification Code 2

 

 

DOB

Format([none_PR01EMF]![EMF_BIRTH_DATE8],"yyyymmdd")

Person's Date of Birth, in format YYYYMMDD.

Password

 

 

 

NULL

IVR Password

 

Employee Time & Expense – PR43EMF

Email Address

[none_PR43EMF]![EMF4_TE_EMAIL_ADDR]

Person's email address from the Employee Time & Exp file.

E-Mail

 

Employee Master File – PR01EMF

Department

[Q_UTILITY_USERNAME]![ORG]

Q_UTILITY_USERNAME: ORG: "DEPT-" & IIf([none_PR01EMF]![EMF_DEPT_NO]="","00",IIf(Len([none_PR01EMF]![EMF_DEPT_NO])=1,"0" & [none_PR01EMF]![EMF_DEPT_NO],[none_PR01EMF]![EMF_DEPT_NO]))

If the Employee does not have a Department in the Employee Master File, then send 'DEPT-00', otherwise send 'DEPT-' + his 2 digit Department number. If the Department is only one digit, then precede with a zero. So Department 1 would be sent as 'DEPT-01'.

Organization

 

 

 

NULL

Bill Rate

 

Employee Pay History File – PR36EPH

Hourly Rate

Cost Rate: [Q_UTILITY_EMPL_MAX_DATE_RT]!FirstOfEPH_HRLY_RATE

From the Emp Pay History File, the Employee's Hourly Rate with the greatest Date (most recent).

 

Cost Rate

 

Employee Time & Expense – PR43EMF

Pulled from Supervisor’s Name identified in Employee Time & Expense File (PR43EMF).

Time Approval Group: IIf([none_PR01EMF]![EMF_EMPL_TYPE]="C","1099",IIf([none_PR01EMF]![EMF_STATUS]="I","INACTIVE",[Q_UTILITY_APPROVAL_GROUPS_Match]![Approval Group Name] & "-TIME"))

Time Approval Group

 

Employee Master File – PR01EMF

Employee Status

IIf([none_PR01EMF]![EMF_STATUS]="A","Y","N")

Active

 

 

 

“Y”

Receive Timesheet E-Mails

 

 

 

“Y”

Receive Expense E-Mails

 

 

 

“Y”

Auto-Fill Timesheet

 

 

 

NULL

Expense Approval Amount

 

Employee Pay History – PR36EPH

Latest Employee Pay History Date

MUST BE 1ST DAY OF PAY PERIOD FOR SALARIED EMPLOYEES!

Effective Date: IIf([Q_UTILITY_EMPL_MAX_DATE_RT]!EPH_DATE<#1/1/2014#,"1/1/2014",IIf(Day([Q_UTILITY_EMPL_MAX_DATE_RT]!EPH_DATE)<16,Month([Q_UTILITY_EMPL_MAX_DATE_RT]!EPH_DATE) & "/01/" & Year([Q_UTILITY_EMPL_MAX_DATE_RT]!EPH_DATE),Month([Q_UTILITY_EMPL_MAX_DATE_RT]!EPH_DATE) & "/16/" & Year([Q_UTILITY_EMPL_MAX_DATE_RT]!EPH_DATE)))

Effective Date

 

 

 

NULL

Dilution Period

 

 

 

NULL

Default Project Organization

 

 

 

NULL

Default Project

 

 

 

NULL

Default Task

 

Employee Master File

Labor Category

The Labor Category field in the Employee Master is automatically updated from the latest value in Employee Pay History.

Default Labor Category:

Default Labor Category

 

 

 

“Employee”

Preferred Payment Method

 

 

 

NULL

Time-In/Time-Out

 

 

 

Business Week: "WEEK-40"

Business Week

 

 

 

“Y”

Receive Assignment E-Mails

 

Employee Master File – PR01EMF

Default Suffix

User Defined 1: [none_PR01EMF]![EMF_DEFAULT_SFX]

User Defined 1

 

Employee Time & Expense – PR43EMF

Employee’s Vendor ID

User Defined 2: [None_PR43EMF]![EMF4_TE_VENDOR_ID]

User Defined 2

 

 

 

NULL

User Defined 3

 

 

 

NULL

User Defined 4

 

 

 

NULL

User Defined 5

 

 

 

NULL

User Defined 6

 

 

 

NULL

User Defined 7

 

 

 

NULL

User Defined 8

 

 

 

 

NULL

User Defined 9

 

 

 

NULL

User Defined 10

 

Employee Master File – PR01EMF

Earlier of EMF Hire Date or PTO Leave Hire Date

Hire Date: IIf([none_PR01EMF]![EMF_ACC_LEAVE_HIRE_DATE1_8] Is Null,[none_PR01EMF]![EMF_HIRE_DATE8],[none_PR01EMF]![EMF_ACC_LEAVE_HIRE_DATE1_8])

Hire Date

 

 

 

Payment Currency: "USD"

Payment Currency

 

 

 

 

Delete

 

 

 

Cost Structure: "Standard"

Cost Structure

 

Employee Master File – PR01EMF

Based on value of Employee Default Suffix

Cost Element: IIf([none_PR01EMF]![EMF_DEFAULT_SFX]="39","LABOR_39",IIf([none_PR01EMF]![EMF_DEFAULT_SFX]="40","LABOR_40",IIf([none_PR01EMF]![EMF_DEFAULT_SFX]="41"," LABOR_41",IIf([none_PR01EMF]![EMF_DEFAULT_SFX]="42"," LABOR_42","OTHER_SUFFIX")))))

Cost Element

 

 

 

NULL

Unlock

 

Employee Master File – PR01EMF

Employee Work State

Location: [none_PR01EMF]![EMF_WORKING_STATE]

Location

 

 

Hourly/Salaried

Employee Type: IIf([none_PR01EMF]![EMF_EMPL_TYPE]="C","Consultant",IIf([none_PR01EMF]![EMF_PAY_HRLY_SAL]="S","Salary","Hourly"))

Employee Type

 

Projects

The project record in GCS maps directly to the Project in Unanet. 

Project

GCS Screen

GCS Screen Field (First Write)

Interface Logic

Unanet Field

Client-Specific Changes or Notes

Contract Master File – CT01CMF

  

*Organization: IIf([B_IMPORTED_UNANET_PROJECT]![*project_org_code] Is Null,[Q_UTILITY_ORG_PROJECT]![*ORG],IIf([Q_UTILITY_ORG_PROJECT]![*ORG]<>[B_IMPORTED_UNANET_PROJECT]![*project_org_code],"!RENAME!," & [B_IMPORTED_UNANET_PROJECT]![*project_org_code] & "," & [Q_UTILITY_ORG_PROJECT]![*ORG],[Q_UTILITY_ORG_PROJECT]![*ORG]))

If the Project Org does not exist in Unanet, take the Org from the GL Chart of Accounts Division Number.

If it does already exist in Unanet, then if the Project Org Code value in Unanet does not equal the value in GCS, then RENAME to the value in GCS.

Organization

 

 

Contract and Task

Project Code: [none_CT01CMF]![CMF_ACCT] & '-' & [none_CT01CMF]![CMF_SUB_ACCT]

CMF Account code and Subaccount code, separated by a dash.

Project Code

 

 

Contract Type

Project Type: IIf([Q_UTILITY_PROJ_TYPE]![CMF_ACCOUNT_TYPE] Is Null,Null,[Q_UTILITY_PROJ_TYPE]![Project Type])

CMF Account Type, otherwise Project Type.

Project Type

 

 

Active

Project Status: IIf([none_CT01CMF]![CMF_STATUS]="A","Open","Closed")

If CFM status = 'A', then send 'Open', otherwise send 'Closed'.

Project Status

 

 

 

Project Manager: "UNANETADMIN"

Hardcoded value.

Project Manager

 

 

 

Restrict Access: [A_TABLE_CONFIG_PROJ]![PRJ_RESTRICT_ACCESS]

Value configured by user in A_TABLE_CONFIG_PROJ table.

Restrict Access

 

 

 

User Self Sign Up: [A_TABLE_CONFIG_PROJ]![PRJ_SELF_SIGN_UP]

Value configured by user in A_TABLE_CONFIG_PROJ table.

User Self Sign Up

 

 

 

User Self Plan: [A_TABLE_CONFIG_PROJ]![PRJ_SELF_PLAN]

Value configured by user in A_TABLE_CONFIG_PROJ table.

User Self Plan

 

 

 

User Self Sign UP Plans: [A_TABLE_CONFIG_PROJ]![PRJ_SELF_SIGN_UP_PLANS]

Value configured by user in A_TABLE_CONFIG_PROJ table.

User Self Sign Up Plans

 

 

 

Future Charge: [A_TABLE_CONFIG_PROJ]![PRJ_FUTURE_CHARGE]

Value configured by user in A_TABLE_CONFIG_PROJ table.

Future Charge

 

 

 

Null

Time Manager Approval

 

 

 

Null

Expense Manager Approval

 

 

 

Null

Time Charged to Tasks

 

 

 

Null

Expense Charged to Tasks

 

 

 

Null

Expenses Allowed

 

Contract Master File – CT01CMF

Original Start Date

Original Start Date: Format([none_CT01CMF]![CMF_ORIG_BEG_DATE_8],"yyyy-mm-dd")

CMF Original Start Date

Original Start Date

 

 

Original End Date

Original End Date: Format([none_CT01CMF]![CMF_ORIG_end_DATE_8],"yyyy-mm-dd")

CMF Original End Date

Original End Date

 

 

Original Start Date

Revised Start Date: Format([none_CT01CMF]![CMF_ORIG_BEG_DATE_8],"yyyy-mm-dd")

CMF Original Start Date

Revised Start Date

 

 

Later of Original End Date or Mod End Date

Revised End Date: Format(IIf([none_CT01CMF]![CMF_ORIG_END_DATE_8] Is Null Or [none_CT01CMF]![CMF_ORIG_END_DATE_8]<[none_CT01CMF]![CMF_MOD_END_DATE_8],[none_CT01CMF]![CMF_MOD_END_DATE_8],[none_CT01CMF]![CMF_ORIG_END_DATE_8]),"yyyy-mm-dd"

Send the later date of the CMF Original End Date or Modified End Date.

Revised End Date

 

 

 

Null

Completed Date

 

 

 

Budget Hours: IIf([Q_UTILITY_EXTENDED_HRS_DOLLARS]![LBF_ACCT] Is Null And [Q_UTILITY_EXTENDED_HRS_DOLLARS]![LBF_SUB_ACCT] Is Null,Null,[Q_UTILITY_EXTENDED_HRS_DOLLARS]![SumOfCWF_BUDGET_HRS])

This is not often-populated data in GCS, so would typically not generate any value. It assumes that the Contract Work Force (CWF) has budget data entered for employee/project combinations, which is unusual. If it does, then the logic sums budget hours for all employees at the project level as entered in Contract Work Force (CWF).

Budget Hours

 

 

 

Budget Labor Dollars: IIf([Q_UTILITY_EXTENDED_HRS_DOLLARS]![LBF_ACCT] Is Null And [Q_UTILITY_EXTENDED_HRS_DOLLARS]![LBF_SUB_ACCT] Is Null,Null,[Q_UTILITY_EXTENDED_HRS_DOLLARS]![EXT_AMT])

This is not often-populated data in GCS, so would typically not generate any value. It assumes that the Contract Work Force (CWF) has budget data entered for employee/project combinations, which is unusual. It also assumes that the Labor Billing File contains bill rates per labor category, which would only be true for T&M contracts.

If both data values do exist, then the logic sums budget hours for all employees at the project level as entered in Contract Work Force (CWF) and multiples by the billing rate in the LBF.

Budget Labor Dollars

 

 

 

Budget Expense Dollars: [A_TABLE_CONFIG_PROJ]![PRJ_BUDGET_EXPENSE_DOLLARS]

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Budget Expense Dollars

 

 

 

ETC Hours: [A_TABLE_CONFIG_PROJ]![PRJ_ETC_HOURS]

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

ETC Hours

 

 

 

ETC Labor Dollars: [A_TABLE_CONFIG_PROJ]![PRJ_ETC_LABOR_DOLLARS]

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

ETC Labor Dollars

 

 

 

ETC Expense Dollars: [A_TABLE_CONFIG_PROJ]![PRJ_ETC_EXPENSE_DOLLARS]

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

ETC Expense Dollars

 

 

 

Estimated Total Hours: A_TABLE_CONFIG_PROJ![PRJ_ESTIMATED TOTAL_HOURS]

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Estimated Total Hours

 

 

 

Estimated Total Labor Dollars: A_TABLE_CONFIG_PROJ![PRJ_ESTIMATED TOTAL_LABOR_DOLLARS]

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Estimated Total Labor Dollars

 

 

 

Estimated Total Expense Dollars: A_TABLE_CONFIG_PROJ![PRJ_ESTIMATED TOTAL_EXPENSE_DOLLARS]

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Estimated Total Expense Dollars

 

 

Contract ID

Account Number: [none_CT01CMF]![CMF_CONTRACT_ID]

Contract ID from CMF.

Account Number

 

 

Contract Account Name

Project Title: [none_CT01CMF]![CMF_ACCT_NAME])

Contract Account Name from CMF.

Project Title

 

 

 

Null

Comment

 

 

 

Null

Assigned Organizations

 

 

 

Null

Pay Code List

 

 

 

Default Pay Code: [A_TABLE_CONFIG_PROJ]![PRJ_DEFAULT_PAY_CODE]

Value configured by user in A_TABLE_CONFIG_PROJ table.

Default Pay Code

 

 

 

Null

Task Level Assignment

 

 

 

Null

Probability Percentage

 

 

 

Null

Percent Complete

 

 

 

Time In / Out: A_TABLE_CONFIG_PROJ!PRJ_TIME_IN_OUT

Value configured by user in A_TABLE_CONFIG_PROJ table.

Time In / Out

 

 

 

Bill Rate Source: [A_TABLE_CONFIG_PROJ]![PRJ_BILL_RATE_SOURCE]

Value configured by user in A_TABLE_CONFIG_PROJ table.

Bill Rate Source

 

 

 

Cost Rate Source: [A_TABLE_CONFIG_PROJ]![PRJ_COST_RATE_SOURCE]

Value configured by user in A_TABLE_CONFIG_PROJ table.

Cost Rate Source

 

 

 

User Labor Category: [A_TABLE_CONFIG_PROJ]![PRJ_USE_LABOR_CATEGORY]

Value configured by user in A_TABLE_CONFIG_PROJ table.

User Labor Category

 

 

 

Enforce Project / Task Dates: IIf([B_IMPORTED_UNANET_Project]![project_code] Is Null,"Y",Null)

Value configured by user in A_TABLE_CONFIG_PROJ table.

Enforce Project /Task Dates

 

 

 

Leave Balance: [A_TABLE_CONFIG_PROJ]![PRJ_LEAVE_BALANCE]

Value configured by user in A_TABLE_CONFIG_PROJ table.

Leave Balance

 

 

USER DEFINED DESCRIPTION #1

User Defined 1: [none_CT01CMF]![CMF_USER_DESC_1]

User Defined Field #1 - Description. This table contains 3 UDFs, but only the first is used here.

User Defined 1

 

 

 

Null

User Defined 2

 

 

 

Null

User Defined 3

 

 

 

Null

User Defined 4

 

 

 

Null

User Defined 5

 

 

 

Null

User Defined 6

 

 

 

Null

User Defined 7

 

 

 

Null

User Defined 8

 

 

 

Null

User Defined 9

 

 

 

Null

User Defined 10

 

 

 

Budget Cost Labor Dollars: A_TABLE_CONFIG_PROJ!PRJ_BUDGET_COST_LABOR_DOLLARS

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Budget Cost Labor Dollars

 

 

 

Budget Cost Expense Dollars: A_TABLE_CONFIG_PROJ!PRJ_BUDGET_COST_EXPENSE_DOLLARS

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Budget Cost Expense Dollars

 

 

 

ETC Cost Labor Dollars: A_TABLE_CONFIG_PROJ!PRJ_ETC_COST_LABOR_DOLLARS

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

ETC Cost Labor Dollars

 

 

 

ETC Cost Expense Dollars: A_TABLE_CONFIG_PROJ!PRJ_ETC_COST_EXPENSE_DOLLARS

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

ETC Cost Expense Dollars

 

 

 

Estimated Total Cost Labor Dollars: A_TABLE_CONFIG_PROJ!PRJ_ESTIMATED_TOTAL_COST_LABOR_DOLLARS

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Estimated Total Cost Labor Dollars

 

 

 

Estimated Total Cost Expense Dollars: A_TABLE_CONFIG_PROJ!PRJ_ESTIMATED_TOTAL_COST_EXPENSE_DOLLARS

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Estimated Total Cost Expense Dollars

 

 

 

PCT Complete Rule: A_TABLE_CONFIG_PROJ!PRJ_PCT_COMPLETE_RULE

Value configured by user in A_TABLE_CONFIG_PROJ table.

PCT Complete Rule

 

 

 

Project Condition: A_TABLE_CONFIG_PROJ!PRJ_PROJECT_CONDITION

Value configured by user in A_TABLE_CONFIG_PROJ table.

Project Condition

 

 

 

Project Manager Access: A_TABLE_CONFIG_PROJ!PRJ_PROJECT_MANAGER_ACCESS

Value configured by user in A_TABLE_CONFIG_PROJ table.

Project Manager Access

 

 

 

Project Viewer Access: A_TABLE_CONFIG_PROJ!PRJ_PROJECT_VIEWER_ACCESS

Value configured by user in A_TABLE_CONFIG_PROJ table.

Project Viewer Access

 

 

 

Resource Manager Access: A_TABLE_CONFIG_PROJ!PRJ_RESOURCE_MANAGER_ACCESS

Value configured by user in A_TABLE_CONFIG_PROJ table.

Resource Manager Access

 

 

 

Allows Time: [A_TABLE_CONFIG_PROJ]![PRJ_ALLOWS_TIME]

Value configured by user in A_TABLE_CONFIG_PROJ table.

Allows Time

 

 

 

Delete: A_TABLE_CONFIG_PROJ!PRJ_DELETE

Value configured by user in A_TABLE_CONFIG_PROJ table. Likely left blank.

Delete

 

 

 

Null

Owning Organization

 

 

Contract Type

Billing Type: IIf([none_CT01CMF]![CMF_BILLING_CD]="1","FP",IIf([none_CT01CMF]![CMF_BILLING_CD]="2","CP",IIf([none_CT01CMF]![CMF_BILLING_CD]="3","TM",Null)))

If GCS Contract Type = 1, then send 'FP' ; 2 = 'CP', 3 = 'TM'. Otherwise send nothing.

Billing Type

 

 

 

Cost Structure: "Standard"

Hardcoded to 'Standard'.

Cost Structure

 

 

 

Null

Fee Percent

 

 

 

Null

Fee Calculation Method

 

 

Contract Value

Total Value: IIf([none_CT01CMF]![CMF_ORIG_AMT] Is Null,Null,[none_CT01CMF]![CMF_ORIG_AMT]+[none_CT01CMF]![CMF_ORIG_FEE_AMT]+[none_CT01CMF]![CMF_MOD_AMT]+[none_CT01CMF]![CMF_MOD_FEE_AMT])

If Original Contract Cost is null, then send null. Otherwise, send the sum of the Original Contract Code, the Original Fee Amount, and the Modifications Fee Amount.

Total Value

 

 

Funded Value

Funded Value: IIf([none_CT01CMF]![CMF_FUNDED_AMT] Is Null,Null,[none_CT01CMF]![CMF_FUNDED_AMT]+[none_CT01CMF]![CMF_FUNDED_FEE_AMT])

If Funded Contract Amount is null, then send null. Otherwise, send the sum of the Funded Contract Amount and the Funded Contract Fee Amount.

Funded Value

 

 

 

Null

Budget Labor Dollars Cost Burdened

 

 

 

Null

Budget Expense Dollars Cost Burdened

 


Project Administrators           

Project Admins are normally defaulted into Unanet as Unanet_Admin.  The Contract Master File in GCS Premier does not have a validated field in the Contract Master File to adequately derive project administrators, therefore, this information is not usually provided through the Interface.  However, if the client is willing to populate and validate the Contract Master with information, the Interface can be configured to use it.  GCS Premier also has a validated source for this information, but it’s not available unless the client has an active Deltek T&E license.  The file for this is called the Time & Expense Roles file and is stored in CT29TER.

Project Admins

GCS Screen

GCS Screen Field

Interface Logic

Unanet Field

Client-Specific Changes or Notes

 

Organization will come from another query that contains several possibilities for organization source.

*Organization: IIf(Len([none_CT01CMF]![CMF_DIV_NUM])=1,"0" & [none_CT01CMF]![CMF_DIV_NUM],[none_CT01CMF]![CMF_DIV_NUM])

Organization

 

 T&E Roles File (CT29TER)

 

Project Code: [none_CT01CMF]![CMF_ACCT] & "-" & [none_CT01CMF]![CMF_SUB_ACCT]

Project Code

 

 

 Derived from Administrator’s Employee ID in the T&E Roles File (CT29TER)

Project Administrator Name: UCase(Left([Q_UTILITY_EMAIL_VEND_ID]![EMF4_TE_EMAIL_ADDR],InStr([Q_UTILITY_EMAIL_VEND_ID]![EMF4_TE_EMAIL_ADDR],"@")-1))

Project Administrator Name

 

 

 Role from T&E Roles File

Role: [none_CT29TER]![Role]

Role

 

 

 

Primary Indicator: "Y"

Primary Indicator

 

 

 

Delete Flag: Null

Delete Flag

 

 

Time - Historical Data

One-time query to pull historical time data from GCS from any prior year.  Time is pulled from GCS at a summary level, grouped by employee/project/labor category combination for the year.  Consultant needs to plug the fiscal year into the query.  Query is called ZZ_TIME_EXPORT_PRIOR_YEARS.  

ORGANIZATIONS

GCS Screen

GCS Screen Field

Interface Logic

Unanet Import Field

Client-Specific Changes or Notes

Employee Master File – PR01EMF

The Unanet Username could be one of several different possibilities.  The username will be configured and pulled from the “USERNAME TABLE”

Username

User Name

 

 

 

Because summarizing by employee/project/labor category combination, we use last Day of Fiscal Year (THF_FISCAL_YEAR value filled into query on far right). 12/31 is hardcoded.Work Date 
  Project Organization Code: [Q_UTILITY_ODC_Projects]![*project_org_code]Project Org Code 

Contract Master File – CT01CMF

Contract and Task

Project Code: [none_CT01CMF]![CMF_ACCT] & '-' & [none_CT01CMF]![CMF_SUB_ACCT]

Project Code

 
  blankTask Name 
  blankProject Type 
  hardcoded to client's value (R or RT or whatever)Pay Code 
Timesheet History FileHoursSum on employee/project/labor category combinationHours 
 Z_TABLE_PROJECT_LABOR_CATEGORY_MAINPull Labor Category Bill Rate directlyBill Rate 
  Total labor cost dollars / total hours. This is the already-diluated cost rate. To 8 decimal places so calculates correctly in UnanetCost Rate 
  blankProject Org Override 
  blankPerson Org Override 
 Z_TABLE_PROJECT_LABOR_CATEGORY_MAIN Labor Category 
  blankLocation 
  hardcodedComments 
  hardcodedChange Reason 
  hardcodedCost Structure 
  hardcodedCost Element 
  blankTime Period 
  Same logic as for transaction datePost Date 

 

Q_UTILITY_USERNAME


Username is derived via Q_UTILITY_USERNAME using this logic: If the email address exists, then use everything before the '@' symbol. Otherwise use firstname.lastname.

*Username: IIf([none_PR43EMF]![EMF4_TE_EMAIL_ADDR]="" Or [none_PR43EMF]![EMF4_TE_EMAIL_ADDR] Is Null,[none_PR01EMF]![EMF_FIRST_NAME] & '.' & [none_PR01EMF]![EMF_LAST_NAME],UCase(Left([none_PR43EMF]![EMF4_TE_EMAIL_ADDR],InStr([none_PR43EMF]![EMF4_TE_EMAIL_ADDR],"@")-1)))

Person Organization is derived via Q_UTILITY_USERNAME using this logic: If the Department Number exists, the use it ; otherwise, use '0' or '00'.

ORG: "DEPT-" & IIf([none_PR01EMF]![EMF_DEPT_NO]="","00",IIf(Len([none_PR01EMF]![EMF_DEPT_NO])=1,"0" & [none_PR01EMF]![EMF_DEPT_NO],[none_PR01EMF]![EMF_DEPT_NO]))

 

 

  • No labels