HDL - Element Entry - ValueSets

GET_ASG_DETAILS
Get assignment details for HDL mapping
Data Loader
Table
Character
-----------------------------------------------------------------------------------

PER_ALL_ASSIGNMENTS_M PAAM, PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_SERVICE PPOS, HR_ALL_ORGANIZATION_UNITS_F HAOUF

PAAM.ASSIGNMENT_NUMBER || '*' || HAOUF.ATTRIBUTE1 || '*' || PAPF.PERSON_ID


PAPF.PERSON_NUMBER = :{PARAMETER.P_PER_NUM}
AND :{PARAMETER.P_DATE} BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND :{PARAMETER.P_DATE} BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PPOS.PERIOD_OF_SERVICE_ID = PAAM.PERIOD_OF_SERVICE_ID
AND HAOUF.ORGANIZATION_ID = PAAM.ORGANIZATION_ID
AND :{PARAMETER.P_DATE} BETWEEN HAOUF.EFFECTIVE_START_DATE AND HAOUF.EFFECTIVE_END_DATE

PPOS.DATE_START DESC
======================================================================================================================


GET_FUTURE_ASG
Get assignment details for HDL mapping
Data Loader
Table
Character
-----------------------------------------------------------------------------------

PER_ALL_ASSIGNMENTS_M PAAM, PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_SERVICE PPOS, HR_ALL_ORGANIZATION_UNITS_F HAOUF

PAAM.ASSIGNMENT_NUMBER || '*' || HAOUF.ATTRIBUTE1 || '*' || PAPF.PERSON_ID


PAPF.PERSON_NUMBER = :{PARAMETER.P_PER_NUM}
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PPOS.PERIOD_OF_SERVICE_ID = PAAM.PERIOD_OF_SERVICE_ID
AND HAOUF.ORGANIZATION_ID = PAAM.ORGANIZATION_ID
AND :{PARAMETER.P_DATE} BETWEEN HAOUF.EFFECTIVE_START_DATE AND HAOUF.EFFECTIVE_END_DATE

PPOS.DATE_START DESC
======================================================================================================================


TL_RANDOM_NUMBER
Get random number for multiple entry count
Data Loader
Table
Character


TO_CHAR(ROUND(DBMS_RANDOM.VALUE, 3) * 1000)

DUAL

-------------------------------------------------------------------
SELECT ROUND(DBMS_RANDOM.VALUE, 3) * 1000 SEQ_NBR FROM DUAL
======================================================================================================================


GET_PAY_END_DATE
Get pay end date for US time file
Data Loader
Table
Character
------------------------------------------------------------------------------
PER_LEGISLATIVE_DATA_GROUPS PLDG, PAY_TIME_PERIODS PTP

TO_CHAR(PTP.END_DATE, 'YYYY/MM/DD')

PLDG.LEGISLATION_CODE = 'US'
AND PLDG.LEGISLATIVE_DATA_GROUP_ID = PTP.LEGISLATIVE_DATA_GROUP_ID
AND PTP.PERIOD_TYPE = 'Bi-Week'
AND PTP.PERIOD_CATEGORY = 'E'
AND TO_DATE(:{PARAMETER.DUR},'YYYY/MM/DD') BETWEEN PTP.START_DATE AND PTP.END_DATE
======================================================================================================================

GET_ELEMENT_ENTRY_DETAILS

Description: Get element entry details
Module: Data Loader
Validation Type : Table
Value Data Type : Character
---------------------------------------------------------------------------------------------------------------------------------------
FROM Clause:
PAY_ELEMENT_ENTRIES_F PEEF, PAY_ELEMENT_TYPES_F PETF, PAY_INPUT_VALUES_F PIVF, PAY_ELEMENT_ENTRY_VALUES_F PEEVF

Value Column Name:
PEEF.ELEMENT_ENTRY_ID || '*' || PEEVF.ELEMENT_ENTRY_VALUE_ID || '*' || TO_CHAR(PEEF.EFFECTIVE_START_DATE, 'YYYY/MM/DD') || '*' || PEEVF.SCREEN_ENTRY_VALUE || '*' || PEEF.MULTIPLE_ENTRY_COUNT
ID Column Name:
PEEF.ELEMENT_ENTRY_ID || '*' || PEEVF.ELEMENT_ENTRY_VALUE_ID || '*' || TO_CHAR(PEEF.EFFECTIVE_START_DATE, 'YYYY/MM/DD') || '*' || PEEVF.SCREEN_ENTRY_VALUE || '*' || PEEF.MULTIPLE_ENTRY_COUNT

WHERE Clause:
PEEF.PERSON_ID = :{PARAMETER.P_PERSON_ID}
AND PETF.BASE_ELEMENT_NAME = :{PARAMETER.P_ELEMENT_NAME}
AND SYSDATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
AND PETF.ELEMENT_TYPE_ID = PEEF.ELEMENT_TYPE_ID
AND SYSDATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND PIVF.ELEMENT_TYPE_ID = PEEF.ELEMENT_type_ID
AND PIVF.BASE_NAME in ('Percentage', 'Amount')
AND SYSDATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
AND PEEVF.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID
AND PEEVF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID
AND SYSDATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE

ORDER BY Clause :
PEEF.EFFECTIVE_START_DATE DESC

======================================================================================================================