UE Table Maintenance

Pgm: UETABLE – UE Table Maintenance; standard Treeview path: System > User Extensions > Table Maintenance

Once the required extension fields have been created, tables need to be created that can store the data.

Create the UE Table Definition

System Defined – Checkbox

If checked, indicates table is system-defined; otherwise, unchecked indicates user-defined.

Table Code

Enter the table code for the table being defined. Any combination of letters and characters can be used.

Table Description

Enter the description for the table being defined. Any combination of letters and characters can be used.

Attached Table

Enter/select the system table to which the new table will be attached.

Each UE table defined must have an attachment to a system table. The relationship between these two tables will allow for the appropriate prompt(s) to appear on the screens which directly access the primary key of that system table.

For example, if additional information associated with each job needs to be stored, create the desired fields in UE Field Maintenance screen, and then create a table called “Additional Job Information”. Connect this new table with the existing job table. The result would be that every time the job is directly accessed on the system, the User Extension prompts for “Additional Job Information” will appear on the screen.

NOTE: The list of tables displayed in the Attached Table field's LOV cannot be customized by the user. If a table is not listed, then it is not compatible with the user extension. Any request to enable a screen for user extension compatibility would be considered a billable work order.

Display Order

Enter the display order that this extension will appear on the extension palette.

Attachment Type

Select the attachment type that should be used to join the system and the UE tables. There are four different record type attachments from which to choose.

1-1: One row per master record

This type describes an attachment where there is a one to one relationship between the system record and the UE record.

For example, you may create a table to hold the name and address of the Job Site Manager on each job. If there were only one manager per job, you would create a UE table that has a 1-1-attachment type with the job table.

1-Many: Many Rows Distinguished by Date

This type describes an attachment where there is a one to many relationships between the system record and the UE record. In this case, each new record is differentiated by a date.

For example, you may create a table to hold the name and address of the Job Site Manager on each job. If more than one manager works throughout the life of the job and you wish to keep a history of the managers by their start date, you would create a UE table that has a 1-many attachment (distinguished by date) with the job table.

1-Many: Many Rows Distinguished by Trans. Num

This type describes an attachment where there is a one to many relationships between the system record and the UE record. In this case, each new record is differentiated by a transaction number that starts at one and increases as each subsequent record is added to the table.

For example, you may create a table to hold the name and address of the Job Site Manager on each job. If more than one manager works throughout the life of the job and you wish to keep a history of the managers by the sequence in which they worked on the job, you would create a UE table that has a 1-many attachment (distinguished by transaction) with the job table.

1-Many: Many Rows Distinguished by discriminator

This type describes an attachment where there is a one to many relationships between the system record and the UE record. In this case, a unique user-defined column differentiates each new record.

For example, you may create a table to hold the name and address of the Job Site Manager on each job. If more than one manager works throughout the life of the job and you wish to keep a list of the managers who worked on the job, you would create a UE table that has a 1-many attachment (distinguished by discriminator) with the job table. When defining the fields within this table, you define the discriminator as the managers’ name.

Required – Checkbox, Record Validation (Date)

If a user extension table is marked as required, then when a user creates a new record in the table associated with the user extension, then the system will try to enforce that the user extension information be entered as well.

The record validation date allows the user to make a User Extension required, without invalidating previously entered records. If a date is entered in the Record Validation field, then any records entered prior to this date that do not have a user extension will still be considered valid.

Mandatory user extensions are available for Business Partner, Employee Profile, Equipment Maintenance, Job Setup and Job Assign Cost Codes. When the required checkbox is unchecked, mandatory user extensions are not in effect. When the required checkbox is changed to checked, an Options pop-up window will appear with the following options:

Validate All Records Now
Validate At The Time Of Entry

Both selections will work in conjunction with the Record Validation date field. If the Record Validation date field is left null and ‘Validate All Records Now’ is selected, all records in the table will be changed to read ‘N’ in the table and will be invalid in the screen. A user extension will need to be entered in order for the record to become valid.

If the Record Validation date field has a date entered, only those records that were created on or after that date will be changed to invalid until such time as a user extension is entered. A new record will be invalid until a user extension is entered.

If the ‘Validate At The Time Of Entry’ is selected, a new record will be invalid until such time as a user extension is entered. Records previously entered will not be considered invalid unless the form is entered and the setup is on or after the required date. At that time, the user will be asked to enter a user extension and the state of invalid will be in effect until the user extension is entered.

NOTE: If a user extension is set as ‘Required’ against the Job Master table, then the job record will not be considered valid (usable) unless the UE information is entered.

Default Entry Type

Specify the default entry type by selecting either the form or the tabular entry. The form entry will display one record per screen, while the tabular entry will display the screen in a column and row format.

Custom Entry Form

Only use this field if you or CMiC has created a special entry program just for this user extension. This field can only be used if the Default Entry Type field is set to “Custom”.

Linked Table Code

This field should only be used when a user extension is created against an object that is then transformed. For example, if there is a user extension table defined against the invoice registry table, you may want to move the user extension data along with the invoice information when the registered invoice is turned into a voucher. In this case, the Voucher Table would be entered as the linked Table.

[Storage] – Button (Modify the Advanced Storage Maintenance)

Pop-up window launched from the [Storage] button on the UE Table Maintenance screen (standard Treeview path: System > User Extensions > Table Maintenance)

Oracle provides for specific parameters for fine-tuning the manner in which the tables are stored. This screen allows these parameters to be set. This is an optional step and must be done before directly after entering the Attached Table field.

See the Oracle SQL Reference Manual for more details and check with your Database Administrator prior to making any changes into this screen.

[Synonyms] – Button (Create Table Synonyms)

Pop-up window launched from the [Synonyms] button on the UE Table Maintenance screen (standard Treeview path: System > User Extensions > Table Maintenance)

This pop-up window provides the ability to give the new table an alternate name and owner so that it can be found easily when using third-party tools to view the data. This is an optional function.

The ‘All Master Rows’ checkbox option provides the ability to specify whether the view displays rows when user extended data has not been entered. Take for example a situation where a synonym is being created for the extra job table which stores the Project Manager’s name. When the ‘All Master Rows’ option is checked, this indicates to the system to display all the master job table records regardless of whether the project manager has been entered. Leaving this option blank would only display the record if the project manager data exists on the record.

[Prompts] – Button (Define the UE Table Prompts)

Pop-up window launched from [Prompts] button on UE Table Maintenance screen (standard Treeview path: System > User Extensions > Table Maintenance)

The UE table prompts define the button Prompt on the User Extension palette as well as the form title of the screen.

Language Code

The system will default the language code that is being used to define the User Extension table. For example, if English is being used, the code ENG will appear in this field and does not require any change.

If a multi-lingual system is being used, this User Extension could be created in a language other than the one defaulted. In this case, move to a new record and enter/select the appropriate language code for the system that will require this User Extension.

Form Title

The system will default the Form Title field from the UE Table Description field. If the default language code is being used, there is no reason to change this field.

If a multi-lingual system is being used, enter the form title for the appropriate language.

Button Prompt

The button prompt will default from the Table Code field. This prompt can be changed to a more user-friendly button prompt.

[Fields] – Button (Enter the UE Table Fields)

Pop-up window launched from the [Fields] button on the UE Table Maintenance screen (standard Treeview path: System > User Extensions > Table Maintenance)

Once the UE table has been created and linked it to a system table, the fields which will exist within that table must be defined.

The system automatically creates the ‘master key fields’ that are required by Oracle when defining a table. The field ATTACH_SEQ is also a system defined field and will also automatically be created for every table.

NOTE: The connection of the UE table to a system table (i.e. Additional job information to the job table), will automatically create the ‘master key fields’ that are required by oracle when defining a table. These fields will be displayed as reference when this screen is entered but cannot be modified.

Field Code, Field Description

Enter/select the UE field code for the field being added to this table. Only UE field codes can be entered. The field description will display automatically with a valid entry.

Field Number

Enter the field display order number that represents the order in which the fields will display on the input screen.

NOTE: Regardless of the entry in this field, the ‘Master Key’ fields will always display first and the ‘Detail Key’ fields keys will always display second.

Key Type

Select the appropriate key type for the field being entered. There are three different key types: “Master Key Field”, “Detail Key Field”, and “Non-key Field”.

The Master Key Fields will always be defined for you.

The Detail Key Field is used to define the field on which each new record is differentiated. For example, take the situation where you are creating a table to hold the name and address of the Job Site Managers on a job. In order to keep a list of the managers who worked on the job, you would create a UE table that has a 1-many attachment (distinguished by discriminator) with the job table. When defining the fields within this table, you would define the manager’s name as the discriminator by defining the field as a DETAIL KEY.

All other fields would be defined as Non-key Fields.

Sort Order

Assign the sort order to the field. The system allows fields to be sorted in ascending or descending order.

Required – Checkbox

Check the ‘Required’ box if an entry is required into the prompts for this field. This option will default from the UE Field Maintenance table, but it can be modified for use on this table.

Display – Checkbox

Check the ‘Display’ box if this field should be displayed on the input screen.

[Roles] – Button (Specify the UE Table Roles)

Pop-up window launched from the [Roles] button on the UE Table Maintenance screen (standard Treeview path: System > User Extensions > Table Maintenance)

As with all programs in the system, access can be limited to the UE table by assigning a role to this table.

See Roles for more details.

Role

Enter the security role code(s) that apply to this table – if none are entered, everyone will have access to this extension.

Role Information

Modify the insert, update, delete and view options for this role by checking or unchecking the appropriate option box.

[Create Views] – Button

Pop-up window launched from the [Create Fat View] button on the UE Table Maintenance screen (standard Treeview path: System > User Extensions > Table Maintenance)

This button is used to create or recreate all views for a selected UE table. This is useful for creating user-defined views and UE fat views.

When a new UE table is initially created, a BI-friendly user-defined view will also be automatically generated. Since this generation only happens for newly created UE tables, the [Create Views] button can be used to generate a user-defined view on-demand for pre-existing UE tables.

Clicking the button for a selected UE table will also create a fat view. This may include triggers, views, and outer views. An example for a UE fat view creation output would result in the following database objects created:

UETD_DAJOB_UE_FV:

create or replace view uetd_dajob_ue_fv as

select COMP_CODE

,JOB_CODE

,ATTACH_SEQ

,ATTACH_REV

,TESTER

,INFORMATIO

,rowidtochar(rowid) ue_rowid

from UETD_DAJOB_UE

DBT_I_IUD_DAJOB_UE_FV:

CREATE OR REPLACE TRIGGER DBT_I_IUD_DAJOB_UE_FV

INSTEAD OF INSERT OR UPDATE OR DELETE ON UETD_DAJOB_UE_FV

for each row

begin

da.dbk_sys_validate.not_null(:new.COMP_CODE,da.dbk_ue_maintain.f_get_column_prompt('COMP_CODE'));

da.dbk_sys_validate.not_null(:new.JOB_CODE,da.dbk_ue_maintain.f_get_column_prompt('JOB_CODE'));

if inserting then

insert into UETD_DAJOB_UE

( COMP_CODE

,JOB_CODE

,ATTACH_SEQ

,ATTACH_REV

,TESTER

,INFORMATIO

)

values( :new.COMP_CODE

,:new.JOB_CODE

,:new.ATTACH_SEQ

,:new.ATTACH_REV

,:new.TESTER

,:new.INFORMATIO

);

elsif updating then

update UETD_DAJOB_UE

set COMP_CODE = :new.COMP_CODE

,JOB_CODE = :new.JOB_CODE

,ATTACH_SEQ = :new.ATTACH_SEQ

,ATTACH_REV = :new.ATTACH_REV

,TESTER = :new.TESTER

,INFORMATIO = :new.INFORMATIO

where rowid = chartorowid(:old.ue_rowid);

else delete UETD_DAJOB_UE

where rowid = chartorowid(:old.ue_rowid);

end if;

exception

when others then

if sqlcode=-1 then

da.dbk_sys_error.set_error_message('SYS-000039-ERR');

da.dbk_sys_error.raise_proc_error('DA.DBT_I_IUD_DAJOB_UE_FV', 'Uniqueness violation', null);

else

raise;

end if;

end DBT_I_IUD_DAJOB_UE_FV;

UETD_DAJOB_UE_OUTER_VIEW:

create or replace view uetd_dajob_ue_outer_view as

select A.JOB_FCAST_INCL_SUBJOBS_FLG

,A.JOB_ADJ_BUDG_EQUALS_REV_FLG

,A.JOB_PHS_BUDG_UNITS_FLAG

,A.JOB_SI_SEQ_NUM

,A.JOB_REVREC_CURR_DATE

,A.JOB_REVREC_LST_AMT

,A.WORK_LOC

,A.JOB_MUTLI_OVHD_PC_FLAG

,A.JOB_WORK_LOC

,A.JOB_POLICY_NO

,A.JOB_PREVAILING_WAGE

,A.JOB_PL_POLICY_NO

,A.JOB_FULLY_PAID_INVS

,A.JOB_DAYS_OUTST_INV_PAID_TTL

,A.JOB_RATE_BY_JOB_FLAG

,A.JOB_USE_PAY_BILL_RATE_FLAG

,A.JOB_USE_EQP_BILL_RATE_FLAG

,A.JOB_CUST_CONTACT_NAME

,A.JOB_TERM_CODE

,A.JOB_INVOICE_GROUP_CODE

,A.JOB_BILLING_TYPE_CODE

,A.JOB_INVOICE_FORMAT_CODE

,A.JOB_CONSTRUCTION_VALUE

,A.JOB_MAX_HOURLY_RATE

,A.JOB_MAX_BILLING_AMT

,A.JOB_MAX_BILLING_BUDGET_AMT

,A.JOB_BILLING_RATE_TABLE_CODE

,A.JOB_IB_ALLOW_FLAG

,A.JOB_IB_FULL_TARIFF_FLAG

,A.JOB_CONT_TYPE_CODE

,A.JOB_LONG_CODE

,A.JOB_RESERVE_REV_DEPT_CODE

,A.JOB_RESERVE_REV_ACC_CODE

,A.JOB_CONSTRUCTION_VALUE_PCT

,A.JOB_MAX_HOURS

,A.JOB_CILOC_CODE

,A.JOB_JTR_EXP_FLAG

,A.JOB_IB_EXPENSE_CAT_CODE

,A.JOB_ORIGINAL_CONTRACT_AMT

,A.JOB_DEFAULT_DEPT_CODE

,A.JOB_AP_TAX1_CODE

,A.JOB_AP_TAX2_CODE

,A.JOB_AP_TAX3_CODE

,A.JOB_AR_TAX1_CODE

,A.JOB_AR_TAX2_CODE

,A.JOB_AR_TAX3_CODE

,A.JOB_JB_MAP_CODE

,A.JOB_CAL_SAL_CHARGE_RATE

,A.JOB_WIP_OVERRIDE_CONT_AMT

,A.JOB_TAX1_CODE

,A.JOB_TAX2_CODE

,A.JOB_TAX3_CODE

,A.JOB_APPLY_DB_RULES

,A.JOB_ATTACH_ORASEQ

,A.JOB_OBJECT_ORASEQ

,A.JOB_WIP_ROLL_IN_SUBJOB_FLAG

,A.JOB_UE_VALID_FLAG

,A.JOB_PARTN_CODE

,A.JOB_PARTN_TYPE_CODE

,A.JOB_CONTACT_CODE

,A.JOB_ADD_TYPE_CODE

,A.JOB_RULE_CODE

,A.JOB_CREATE_DATE

,A.JOB_PAY_FROM_JOB_COMP_FLAG

,A.JOB_ORIG_BUDGET_EFFECTIVE_DATE

,A.JOB__IU__CREATE_DATE

,A.JOB__IU__CREATE_USER

,A.JOB__IU__UPDATE_DATE

,A.JOB__IU__UPDATE_USER

,A.JOB_COMPLETED_FOR_WIP_FLAG

,A.JOB_EXCLUDE_FROM_WIP_FLAG

,A.JOB_FINALIZE_PROJECTIONS_FLAG

,A.JOB_PW_RATE_CODE

,A.JOB_PW_OV_RATE

,A.JOB_WO_FLAG

,A.JOB_ALLOW_OVERHEAD_FLAG

,A.JOB_DEFAULT_PYOVHD_GRP

,A.JOB_COST_TO_COMPL_OVRD_FLG

,A.JOB_MS_CODE

,A.JOB_SHOW_CPR_AS_COST_AMT_FLAG

,A.JOB_ORIGINAL_FEE_AMT

,A.JOB_PHS_TYPE_REQUIRED_FLG

,A.JOB_BILL_TBL_REV_FCAST_FLG

,A.JOB_SECTOR_CODE

,A.JOB_PAYRATE_SCHEDULE_CODE

,A.JOB_EQP_CHG_NO_RESTART_FLAG

,A.JOB_IGNORE_COSTS_PRIOR_TO_DATE

,A.JOB_JB_RETAINAGE_CODE

,A.JOB_BUDG_OVRD_PROJ_FLAG

,A.JOB_CERTREP_START_DATE

,A.JOB_CERTREP_END_DATE

,A.JOB_CERTREP_SIGNATORY_NAME

,A.JOB_CERTREP_SIGNATORY_TITLE

,A.JOB_CERTREP_CONTRACT_NUMBER

,A.JOB_CERTREP_FRINGE_BEN_PAID

,A.JOB_PROJ_THRESHOLD_PCT

,A.JOB_PUBLIC_SECTOR_FLAG

,A.JOB_UNIT_PRICED_CONTRACT_FLAG

,A.JOB_COMP_CODE

,A.JOB_CODE

,A.JOB_CTRL_CODE

,A.JOB_NAME

,A.JOB_CUST_CODE

,A.JOB_CONTRACT_CODE

,A.JOB_WIP_DEPT_CODE

,A.JOB_WIP_ACC_CODE

,A.JOB_LBC_DEPT_CODE

,A.JOB_LBC_ACC_CODE

,A.JOB_LTC_DEPT_CODE

,A.JOB_LTC_ACC_CODE

,A.JOB_CC_DEPT_CODE

,A.JOB_CC_ACC_CODE

,A.JOB_BILL_DEPT_CODE

,A.JOB_BILL_ACC_CODE

,A.JOB_BILL_AMT

,A.JOB_COST_METH_CODE

,A.JOB_STATUS_CODE

,A.JOB_ACCMETH_CODE

,A.JOB_CERTIFY_CODE

,A.JOB_SIZE_CODE

,A.JOB_WM_CODE

,A.JOB_ACTION_CODE

,A.JOB_EST_START_DATE

,A.JOB_ACT_START_DATE

,A.JOB_EST_COMPL_DATE

,A.JOB_ACT_COMPL_DATE

,A.JOB_LST_ADDON_DATE

,A.JOB_LST_REC_DATE

,A.JOB_REC_AMT

,A.JOB_HB_REC_AMT

,A.JOB_REVREC_AMT

,A.JOB_HB_AMT

,A.JOB_PROFREC_AMT

,A.JOB_LST_REC_PC

,A.JOB_LOC_CODE

,A.JOB_BUDG_UNIT

,A.JOB_COMPL_UNIT

,A.JOB_DISB_AMT

,A.JOB_REVREC_LST_PC

,A.JOB_REVREC_PC

,A.JOB_CONTRACT_AMT

,A.JOB_COST_FLAG

,A.JOB_BILL_FLAG

,A.JOB_SUB_FLAG

,A.JOB_SEC_GROUP

,A.JOB_BUDGCST_SAME_LEVEL_FLAG

,A.JOB_WBSV_CODE1

,A.JOB_WBSV_CODE2

,A.JOB_WBSV_CODE3

,A.JOB_WBSV_CODE4

,A.JOB_WBSV_CODE5

,A.JOB_WBSV_CODE6

,A.JOB_WBSV_CODE7

,A.JOB_WBSV_CODE8

,A.JOB_WBSV_CODE9

,A.JOB_WBSV_CODE10

,A.JOB_WBSV_CODE11

,A.JOB_WBSV_CODE12

,A.JOB_WBSV_REQUIRED_FLAG1

,A.JOB_WBSV_REQUIRED_FLAG2

,A.JOB_WBSV_REQUIRED_FLAG3

,A.JOB_WBSV_REQUIRED_FLAG4

,A.JOB_WBSV_REQUIRED_FLAG5

,A.JOB_WBSV_REQUIRED_FLAG6

,A.JOB_WBSV_REQUIRED_FLAG7

,A.JOB_WBSV_REQUIRED_FLAG8

,A.JOB_WBSV_REQUIRED_FLAG9

,A.JOB_WBSV_REQUIRED_FLAG10

,A.JOB_WBSV_REQUIRED_FLAG11

,A.JOB_WBSV_REQUIRED_FLAG12

,A.JOB_WBSV_EDITABLE_FLAG1

,A.JOB_WBSV_EDITABLE_FLAG2

,A.JOB_WBSV_EDITABLE_FLAG3

,A.JOB_WBSV_EDITABLE_FLAG4

,A.JOB_WBSV_EDITABLE_FLAG5

,A.JOB_WBSV_EDITABLE_FLAG6

,A.JOB_WBSV_EDITABLE_FLAG7

,A.JOB_WBSV_EDITABLE_FLAG8

,A.JOB_WBSV_EDITABLE_FLAG9

,A.JOB_WBSV_EDITABLE_FLAG10

,A.JOB_WBSV_EDITABLE_FLAG11

,A.JOB_WBSV_EDITABLE_FLAG12

,A.JOB_BID_FLAG

,A.JOB_BID_STATUS_CODE

,A.JOB_BID_CODE

,A.JOB_MAKEUP_FLAG

,A.JOB_PM_FLAG

,A.JOB_BILL_METH_CODE

,A.JOB_INV_FORMAT_CODE

,A.JOB_BID_SUBMIT_DATE

,A.JOB_PROPERTY_ID

,A.JOB_AREA_DISTRICT

,A.JOB_PROVINCE_CODE

,A.JOB_REVENUE_GEN_STATE

,A.JOB_UNBILLED_REV_DEPT_CODE

,A.JOB_UNBILLED_REV_ACC_CODE

,A.JOB_HIER

,A.JOB_CHG_SEQ_NUM

,A.JOB_BUDGR_SEQ_NUM

,A.JOB_AUTH_RQ_SEQ_NUM

,UETD_DAJOB_UE.ATTACH_SEQ

,UETD_DAJOB_UE.ATTACH_REV

,UETD_DAJOB_UE.TESTER

,UETD_DAJOB_UE.INFORMATIO

from DA.JCJOB_TABLE A,UETD_DAJOB_UE

where UETD_DAJOB_UE.COMP_CODE (+) = A.JOB_COMP_CODE

and UETD_DAJOB_UE.JOB_CODE (+) = A.JOB_CODE

UETD_DAJOB_UE_VIEW:

create or replace view uetd_dajob_ue_view as