User Defined Tables Used by the Bilan Social

This set of user defined tables is supplied as part of Oracle HR for France and are used by the Bilan Social Report process. These user defined tables fall into three categories:

For information on how to maintain these tables, see: Updating User Defined Tables

Alternate Values for Lookup Codes

This set of user defined tables are used to convert a Lookup code within a particular Lookup type into an alternative value.

Table Column Description
FR_EMPLOYEE_CATEGORY BS_EMP_CAT This is used to determine the Employee Category key values. The Bilan Social process establishes the employee category of assignments processed, it then finds a corresponding row in the FR_EMPLOYEE_CATEGORY table and returns the value from the BS_EMP_CAT column. This table and column will be delivered as part of a post-install step however the column values must be entered manually.
FR_EMPLOYEE_CATEGORY BS_EMP_CAT_HIERARCHY This is used to determine the relative levels of Employee Category for use in the Promotion Item Type. For instance a move between a category of Cadre to one of Ouvrier may be considered to be a promotion. The Bilan Social process establishes the employee category of assignments processed, it then finds a corresponding row in the FR_EMPLOYEE_CATEGORY table and returns the value from the BS_EMP_CAT_HIERARCHY column. This value should be a number indicating the relative level of the employee categories, with 1 being the highest level.
FR_MEDICAL_EXAMINATION_TYPE BS_MEDICAL_EXAMINATION_TYPE This is used to determine the Medical Examination Type key values.
The Bilan Social process establishes the medical examination type from a person's medical examination record, it then finds a corresponding row in the FR_MEDICAL_EXAMINATION_TYPE table and returns the value from the BS_MEDICAL_EXAMINATION_TYPE column.
This table and column will be delivered as part of a post-install step however the column values must be entered manually
NATIONALITY BS_NATIONALITY This is used to determine the Nationality key values.
The Bilan Social process establishes the nationality from people processed, it then finds a corresponding row in the FR_EMPLOYEE_CATEGORY table and returns the value from the BS_EMP_CAT column.
This table and column will be delivered as part of a post-install step however the column values must be entered manually.

Ranges of values

Each of these user defined tables defines a set of numeric ranges, each range is given a label which will be returned in a Bilan Social key value.

For example, the BS_AGE_BAND table might contain the following data.

Range Low High
Less than 25 0 24
25-40 25 39
40-60 40 59
Over 60 60 1000
Table Columns Description
BS_ABSENCE_DURATION_BAND Low Value High Value This is used in the Absence Duration key type. Each row represents an absence duration range. The ranges and their values should be entered in units of days. This table and column will be delivered as part of a post-install step however the column values must be entered manually.
BS_AGE_BAND Low Value High Value This is used in the Absence Duration key type. Each row represents an age range. The ranges and their values should be entered in units of years. This table and column will be delivered as part of a post-install step however the column values must be entered manually.
BS_PART_TIME_HOURS_BAND Low Value High Value This is used in the Absence Duration key type. Each row represents a part time hours range. The ranges and their values should be entered in units of hours. (It is assumed that all assignments use a common working hours frequency). This table and column will be delivered as part of a post-install step however the column values must be entered manually.
BS_REMUNERATION_ BAND Low Value High Value This is used in the Absence Duration key type. Each row represents remuneration range. The ranges and their values should be entered in units of currency. This table and column will be delivered as part of a post-install step however the column values must be entered manually.
BS_SENIORITY_BAND Low Value High Value This is used in the Seniority key type. Each row represents a length of service range. The ranges and their values should be entered in units of years. This table and column will be delivered as part of a post-install step however the column values must be entered manually.
CAGR Specific

This user defined table defines which segments of a particular Collectively Agreed Grade Structure should be returned in a CAGR key value.

Table Column Description
BS_CAGR_GRADES Segment Name This is used in the CAGR key type. Each row value should be the ID_FLEX_NUM of a collectively agreed grade structure, the Segment Name value should comprise of the list of segments, delimited by a comma, that are required to be returned in a CAGR key value. This table and column will be delivered as part of a post-install step however the column values must be entered manually.