Job Costing - Foreign Batch Import

This utility allows users to create job transactions in CMiC via an ASCII File import of data from another system. This utility allows for the import of "G", "J" and "E" type lines. The Job Costing Foreign Batch Import screen is found on the Utility menu of the Job Costing module. This is the only Import utility that has two different control files depending on the type of data that is being imported. The utility allows for single company imports and multi-company imports and the control file for each type is slightly different. It is possible to use the multi-company import control file for single company imports but not the other way around.

This utility differs from most of the other Import utilities in that the utility permits the user to create transactions while there are still invalid records. This functionality is controlled by the System Data module: Setup > System Options > Projects Tab > 'Allow to build JC Foreign Batch Only When All Records Are Valid’ checkbox. When checked, the system will allow the user to build a Job Costing transaction batch from the imported ASCII file, only when all of the records are validated. When unchecked, the system would allow the user to build a Job Costing batch, with valid records, leaving behind the invalid records. The invalid records will remain in the Import Batch allowing for the user to either delete or correct them so that they can be included in a second batch.

This utility also allows the user to calculate billing rates and override these rates as required.

Import ASCII File

There are two buttons on the Foreign Batch Import screen for different import types.

[Multi Company Import] − Button

This import allows for the transactions being imported to have been created by a different company than the job company.

[Single Company Import] − Button

This import forces the job company code to equal the company that created the transactions.

These two methods require differently formatted ASCII files. Please refer to the ASCII File Layouts – Import for Single & Multi Companies section below.

ASCII File Layouts – Import for Single & Multi Companies

Upload Documents pop-up window

After clicking the [Single Company Import] or [Multi Company Import] button, the Upload Documents pop-up window will appear where users can access the [Download CSV Template] button. This button will download a blank template where users can enter desired information into. Details for each header column can be found in the tables below.

NOTE: The headers are provided for reference only at the top of the template. Once the information is filled in, remove the headers to complete the file.

Multi Company Import

Control File = JCFBCHLM.ctl

Column Name	Screen Prompt	Description	Required	Type	Length
FBH_JC_COMP_CODE	Company Code	Company Code of the transaction. This must be a valid CMiC Company Code	Yes	Char	8
FBH_JC_DSRC_COMP_CODE	Source Company	Originating Company Code – This is the company that created the transaction if the transaction is an inter-company transaction. If the transaction is not inter-company then this should be the same company code as the previous field. – This must be a valid CMiC Company code	Yes	Char	8
FBH_JC_DIST_TYPE_CODE	Type	The type of disbursement ‘J’ for Job Cost ’G’ for General Ledger ‘E’ for Equipment	Yes	Char	1
FBH_JC_SRC_CODE	Source Code	Source of the transaction, e.g. Vendor Code, Employee Code, Equipment Code… If left blank the system will fill in with "IMPORTED JC"	No	Char	16
FBH_JC_SRC_DESC	Source Description	Source description, e.g. Vendor name, Employee Name, Equipment Description. If left blank the system will fill in with "Job Cost Foreign Batch Import".	No	Char	30
FBH_JC_REF_CODE	Reference Code	Reference Code, e.g. Invoice code, Payroll Pay Period, Equipment charge out period… If left blank the system will fill in "SYSTEM CREATED" when the transaction is created.	No	Char	16
FBH_JC_REF_DESC	Reference Description	Reference Description, e.g. AP invoice description, Pay Period description … If left blank the system will fill in "System Created Transaction" when the transaction is created.	No	Char	60
FBH_JC_JOB_CODE	Job	Job Code, this must be a valid CMiC Job Code for the company entered if the Distribution type is "J".	No	Char	10
FBH_JC_PHS_CODE	Phase	Phase code, this must be a valid phase for the company/job if the distribution type is "J".	No	Char	16
FBH_JC_CAT_CODE	Category	Category Code, this must be a valid category for the company/job/phase combination if the distribution type is "J".	No	Char	16
FBH_JC_DEPT_CODE	Department	GL Department, this must be a valid department for the company if the distribution type is "G".	No	Char	6
FBH_JC_ACC_CODE	Account	GL Account, this must be a valid department for the company if the distribution type is "G".	No	Char	16
FBH_JC_REF_DATE 'DD-MON-YYYY'	Trans Date	This is the originating date of the transaction. This is not the post date.	No	Date
FBH_JC_AMT	Amount	Amount of the transaction	Yes	Num	18,2
FBH_JC_UNIT	Units	Quantity of the transaction	No	Num	12,4
FBH_JC_WM_CODE	WM	Weight/Measure code associated with the units. If not applicable, use "NA" or your Not Applicable code	Yes	Char	2
FBH_JC_WBSV_CODE1	WBS Code 1	WBS Code 1 Value - only for transaction type J	No	Char	10
FBH_JC_WBSV_CODE2	WBS Code 2	WBS Code 2 Value – only for transaction type J	No	Char	10
FBH_JC_WBSV_CODE3	WBS Code 3	WBS Code 3 Value – only for transaction type J	No	Char	10
FBH_JC_WBSV_CODE4	WBS Code 4	WBS Code 4 Value – only for transaction type J	No	Char	10
FBH_JC_EQP_CODE	Equipment	Equipment Code, this must be a valid equipment code for the company is the transaction type is "E".	No	Char	10
FBH_JC_TRANCODE_CODE	Tran Code	Equipment Transaction Code, this must be a valid equipment transaction code for the company/equipment if the transaction type is "E".	No	Char	4
FBH_JC_COMPON_CODE	Cat	Equipment Category, this must be a valid equipment category code for the company/equipment/Tran code if the transaction type is "E".	No	Char	8
FBH_JC_TAV_CODE1	TAC Code 1	TAC Code 1 Value – only for transaction type G	No	Char	10
FBH_JC_TAV_CODE2	TAC Code 2	TAC Code 2 Value – only for transaction type G	No	Char	10
FBH_JC_TAV_CODE3	TAC Code 3	TAC Code 3 Value – only for transaction type G	No	Char	10
FBH_JC_TAV_CODE4	TAC Code 4	TAC Code 4 Value – only for transaction type G	No	Char	10
FBH_JC_JOB_LONG_CODE	Job Long Code	This is the job code used by the 3rd party. This field must be a valid job long code already imported into CMiC. If this is not applicable leave this field blank.	No	Char	24
FBH_OVERRIDE_BILL_AMT	Override Bill Amt	Billing Override Amount. If not null, the system will set the billing amount to this value. Only applicable to "J" lines.	No	Num	18,2
FBH_OVERRIDE_BILL_RATE	Override Bill Rate	Billing Rate Override – if not null the system will use this number to calculate the billing amount. Only applicable to "J" lines.	No	Num	18,2
FBH_OVERRIDE_TRADE_CODE	Override Trade Code	Override Trade Code – if not null the system will use this trade code to determine the billing amount. Only applicable to "J" lines.	No	Char	4
FBH_POST_DATE ‘'DD-MON-YYYY"	Post Date	This is the actual post date of the transaction.	Yes	Date
FBH_JC_PHASE_NAME	Phase Name	Phase Name – Only used if the Insert Phase Master Function is active.	No	Char	50
FBH_JC_PHS_CTRL_CODE	Control Phase	Phase Control Code – Only used if the Insert Phase Master Function is active.	No	Char	16

Single Company Import

Control File = JCFBCHLD.ctl

Column Name	Screen Prompt	Description	Required	Type	Length
FBH_JC_COMP_CODE	Company Code	Company Code of the transaction. This must be a valid CMiC Company Code.	Yes	Char	8
FBH_JC_DIST_TYPE_CODE	Type	The type of disbursement: "J" for Job Cost, "G" for General Ledger, "E" for Equipment	Yes	Char	1
FBH_JC_SRC_CODE	Source Company	Source of the transaction, e.g. Vendor Code, Employee Code, Equipment Code, etc. If left blank the system will fill in with "IMPORTED JC".	No	Char	16
FBH_JC_SRC_DESC	Source Description	Source description, e.g. Vendor Name, Employee Name, Equipment Description. If left blank the system will fill in with "Job Cost Foreign Batch Import".	No	Char	30
FBH_JC_REF_CODE	Reference Code	Reference Code, e.g. Invoice Code, Payroll Pay Period, Equipment Charge Out Period, etc. If left blank the system will fill in "SYSTEM CREATED" when the transaction is created.	No	Char	16
FBH_JC_REF_DESC	Reference Description	Reference Description, e.g. AP Invoice Description, Pay Period Description, etc. If left blank the system will fill in "System Created Transaction" when the transaction is created.	No	Char	60
FBH_JC_JOB_CODE	Job	Job Code – This must be a valid CMiC Job Code for the company entered if the distribution type is "J".	No	Char	10
FBH_JC_PHS_CODE	Phase	Phase Code – This must be a valid phase for the company/job if the distribution type is "J".	No	Char	16
FBH_JC_CAT_CODE	Category	Category Code – This must be a valid category for the company/job/phase combination if the distribution type is "J".	No	Char	16
FBH_JC_DEPT_CODE	Department	GL Department – This must be a valid department for the company if the distribution type is "G".	No	Char	6
FBH_JC_ACC_CODE	Account	GL Account – This must be a valid department for the company if the distribution type is "G".	No	Char	16
FBH_JC_REF_DATE 'DD-MON-YYYY'	Trans Date	This is the originating date of the transaction. This is not the post date.	No	Date
FBH_JC_AMT	Amount	Amount of the transaction.	Yes	Num	18,2
FBH_JC_UNIT	Units	Quantity of the transaction.	No	Num	12,4
FBH_JC_WM_CODE	WM	Weight/Measure code associated with the units. If not applicable use "NA" or your Not Applicable code.	Yes	Char	2
FBH_JC_WBSV_CODE1	WBS Code 1	WBS Code 1 Value – only for transaction type J	No	Char	10
FBH_JC_WBSV_CODE2	WBS Code 2	WBS Code 2 Value – only for transaction type J	No	Char	10
FBH_JC_WBSV_CODE3	WBS Code 3	WBS Code 3 Value – only for transaction type J	No	Char	10
FBH_JC_WBSV_CODE4	WBS Code 4	WBS Code 4 Value – only for transaction type J	No	Char	10
FBH_JC_EQP_CODE	Equipment	Equipment Code – This must be a valid equipment code for the company if the transaction type is "E".	No	Char	10
FBH_JC_TRANCODE_CODE	Tran Code	Equipment Transaction Code – This must be a valid equipment transaction code for the company/equipment if the transaction type is "E".	No	Char	4
FBH_JC_COMPON_CODE	Cat	Equipment Category – This must be a valid equipment category code for the company/equipment/Tran code if the transaction type is "E".	No	Char	8
FBH_JC_TAV_CODE1	TAC Code 1	TAC Code 1 Value – Only for transaction type G	No	Char	10
FBH_JC_TAV_CODE2	TAC Code 2	TAC Code 2 Value – Only for transaction type G	No	Char	10
FBH_JC_TAV_CODE3	TAC Code 3	TAC Code 3 Value – Only for transaction type G	No	Char	10
FBH_JC_TAV_CODE4	TAC Code 4	TAC Code 4 Value – Only for transaction type G	No	Char	10
FBH_JC_JOB_LONG_CODE	Job Long Code	This is the job code used by the 3rd party. This field must be a valid job long code already imported into CMiC. If this is not applicable leave this field blank.	No	Char	24
FBH_OVERRIDE_BILL_AMT	Override Bill Amt	Billing Override Amount. If not null, the system will set the billing amount to this value. Only applicable to "J" lines.	No	Num	18,2
FBH_OVERRIDE_BILL_RATE	Override Bill Rate	Billing Rate Override – If not null the system will use this number to calculate the billing amount. Only applicable to "J" lines.	No	Num	18,2
FBH_OVERRIDE_TRADE_CODE	Override Trade Code	Override Trade Code – If not null the system will use this trade code to determine the billing amount. Only applicable to "J" lines.	No	Char	4
FBH_POST_DATE ‘'DD-MON-YYYY"	Post Date	This is the actual post date of the transaction.	Yes	Date
FBH_JC_PHASE_NAME	Phase Name	Phase Name – Only used if the Insert Phase Master Function is active.	No	Char	50
FBH_JC_PHS_CTRL_CODE	Control Phase	Phase Control Code – Only used if the Insert Phase Master Function is active.	No	Char	16

Edit Imported Transactions

Sample of the Import ASCII JC Transactions pop-up window

This screen allows the user to correct imported records, or to delete imported records.

Valid

This field is not editable. If this field is checked it indicates that the record passes the validation rules and can be imported into the CMiC Job Costing Transaction Batch tables.

Record

This field is a numeric field indicating the record number.

Insert Phase Master − Checkbox

When this box is checked the import process will automatically insert new phases into the Phase Master. In this case the Phase Name and Control Phase fields must be filled in.

Drop-down Box

The drop down box is used in combination with the [Calculate Bill Rates] and [Clear Bill Rates] buttons to calculate or clear bill rates.

Validate All − Checkbox

The 'Validate All' checkbox is used in combination with the [Re-validate] button. It means to re-validate both valid and invalid records. If the 'Validate All' checkbox is unchecked, only the invalid records will be re-validated.

Create Transactions

Sample of the Create Transactions function for the Import ASCII JC Transactions screen

This function will move the imported transactions into an unposted Job Costing transaction batch.

The DSRC Company Code, Source Code, and Company Code fields on the screen are for limiting the transactions selected from the imported data.

The Company Code field is optional. The values can be selected from the LOV. If this field is left blank, the system will include all transaction records into the batch.

The batch number must be a valid Job Costing batch for the company specified by the DSRC Company Code field. The [Create Batch] button allows for the creation of either a Cost or a Billings type of batch – The batch type will depend on the type of transactions imported.

The Journal Code field is mandatory. This is the journal that will be assigned to the transaction being created.

The Department Code and Account Code fields will be used to create an offsetting GL transaction if the transactions being imported do not balance.

Transactions will be created by the post date, so all imported records selected according to the criteria will be grouped by the post date into unique transactions.