[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
1. After Insert Row Handler User Hook Call on
HXC_PREF_HIERARCHIES
2. After Update Row Handler User Hook Call on
HXC_PREF_HIERARCHIES
3. After Update Row Handler User Hook Call on
HXC_RECURRING_PERIODS
4. Before Process Business Process User Hook Call
on UPDATE_TIME_DEFINITION
I. The package Creates/Updates rows in pay_time_definitions
and per_time_periods as and when rows are created/updated
in HXC_PREF_HIERARCHIES
II. The package Updates a row in pay_time_definitions as and
when a row is updated in HXC_RECURRING_PERIODS
Change List
-----------
Name Date Version Bug Text
-------------- ----------- ------- ------- -----------------------------
rdhingra 24-Jan-2006 115.0 FLSA Created
asasthan 05-OCT-2006 120.1 5560111 Start date of OT period
is used to
create row in pay_time_def
*****************************************************************************/
/******************************************************************************
Name : INSERT_TIME_DEF_HIERARCHY
Scope : LOCAL
Description : This procedure calls core api to insert a row in
pay_time_ definitions which inturn inserts rows in
per_time_periods too.
******************************************************************************/
TABLE_DOES_NOT_EXIST EXCEPTION;
PROCEDURE insert_time_def_hierarchy (
p_business_group_id IN NUMBER,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL
) IS
lv_td_exist VARCHAR2 (1);
hr_utility.TRACE('Entering INSERT_TIME_DEF_HIERARCHY');
'SELECT ''Y''
FROM hxc_retrieval_rule_comps_v hrrc,
hxc_retrieval_rules_v hrr
WHERE hrrc.time_recipient = ''Payroll''
AND hrr.retrieval_rule_id = hrrc.retrieval_rule_id
AND hrr.retrieval_rule_id = '|| p_attribute2 ;
'SELECT NAME,
period_type,
start_date,
duration_in_days
FROM hxc_recurring_periods
WHERE recurring_period_id = '|| p_attribute3;
'hxc_us_time_definitions_hook.insert_time_def_hierarchy');
'Error while inserting a row in pay_time_definitions'
);
'SELECT /*+ INDEX (ptd PAY_TIME_DEFINITIONS_N1) */
''Y''
FROM pay_time_definitions ptd
WHERE ptd.creator_type = ''OTL_W''
and ptd.creator_id = '|| p_attribute3;
'hxc_us_time_definitions_hook.insert_time_def_hierarchy');
'Error while inserting a row in pay_time_definitions'
);
hr_utility.TRACE ('Leaving INSERT_TIME_DEF_HIERARCHY');
'hxc_us_time_definitions_hook.insert_time_def_hierarchy');
'Error while inserting a row in pay_time_definitions'
);
END insert_time_def_hierarchy;
Name : UPDATE_TIME_DEF_RECURRING
Scope : LOCAL
Description : This procedure calls core api to update the time definition
name if it already exists
******************************************************************************/
PROCEDURE update_time_def_recurring (
p_recurring_period_id IN NUMBER
,p_name IN VARCHAR2
) IS
ld_start_date pay_time_definitions.start_date%TYPE;
hr_utility.TRACE('Entering UPDATE_TIME_DEF_RECURRING');
'SELECT /*+ INDEX (ptd PAY_TIME_DEFINITIONS_N1) */
ptd.time_definition_id,
ptd.start_date,
ptd.object_version_number
FROM pay_time_definitions ptd
WHERE ptd.creator_type = ''OTL_W''
AND ptd.creator_id = '|| p_recurring_period_id;
pay_time_definition_api.update_time_definition
/*This table is not date_tracked as of now*/
(p_validate => FALSE
,p_effective_date => ld_start_date
,p_time_definition_id => ln_time_definition_id
,p_definition_name => p_name
,p_object_version_number => ln_ovn
);
hr_utility.TRACE ('Leaving UPDATE_TIME_DEF_RECURRING');
'hxc_us_time_definitions_hook.update_time_def_recurring');
END update_time_def_recurring;
is getting updated in the time_definition created by OTL
******************************************************************************/
PROCEDURE status_otl_time_def (
p_time_definition_id IN NUMBER
,p_definition_name IN VARCHAR2
,p_period_type IN VARCHAR2
,p_start_date IN DATE
,p_period_time_definition_id IN NUMBER
) IS
ln_is_error NUMBER;
'SELECT definition_name
,period_type
,start_date
,period_time_definition_id
FROM pay_time_definitions
WHERE time_definition_id = ' || p_time_definition_id;
hr_utility.set_message(801, 'PAY_US_INVALID_UPDATE');
Name : INSERT_USER_HOOK_HIERARCHY
Scope : GLOBAL
Description : This procedure is called by AFTER INSERT Row Level handler
User Hook of HXC_PREF_HIERARCHIES to insert a row in
pay_time_definitions if it does not already exist.
******************************************************************************/
PROCEDURE insert_user_hook_hierarchy (
p_business_group_id IN NUMBER,
p_legislation_code IN VARCHAR2 DEFAULT NULL,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL
) IS
BEGIN
hr_utility.TRACE('Entering HXC_US_TIME_DEFINITIONS_HOOK.INSERT_USER_HOOK_HIERARCHY');
insert_time_def_hierarchy ( p_business_group_id => p_business_group_id
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
);
hr_utility.TRACE ('Leaving HXC_US_TIME_DEFINITIONS_HOOK.INSERT_USER_HOOK_HIERARCHY');
END insert_user_hook_hierarchy;
Name : UPDATE_USER_HOOK_HIERARCHY
Scope : GLOBAL
Description : This procedure is called by AFTER UPDATE Row Level handler
User Hook of HXC_PREF_HIERARCHIES to insert a row in
pay_time_definitions if it does not already exist.
******************************************************************************/
PROCEDURE update_user_hook_hierarchy (
p_business_group_id IN NUMBER,
p_legislation_code IN VARCHAR2 DEFAULT NULL,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL
) IS
BEGIN
hr_utility.TRACE('Entering HXC_US_TIME_DEFINITIONS_HOOK.UPDATE_USER_HOOK_HIERARCHY');
insert_time_def_hierarchy ( p_business_group_id => p_business_group_id
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
);
hr_utility.TRACE ('Leaving HXC_US_TIME_DEFINITIONS_HOOK.UPDATE_USER_HOOK_HIERARCHY');
END update_user_hook_hierarchy;
Name : UPDATE_USER_HOOK_RECURRING
Scope : GLOBAL
Description : This procedure is called by AFTER UPDATE Row Level handler
User Hook of HXC_RECURRING_PERIODS_API.
******************************************************************************/
PROCEDURE update_user_hook_recurring (
p_recurring_period_id IN NUMBER
,p_name IN VARCHAR2
) IS
BEGIN
hr_utility.TRACE('Entering HXC_US_TIME_DEFINITIONS_HOOK.UPDATE_USER_HOOK_RECURRING');
update_time_def_recurring ( p_recurring_period_id => p_recurring_period_id
,p_name => p_name
);
hr_utility.TRACE ('Leaving HXC_US_TIME_DEFINITIONS_HOOK.UPDATE_USER_HOOK_RECURRING');
END update_user_hook_recurring;
Name : UPDATE_USER_HOOK_TIMEDEF
Scope : GLOBAL
Description : This procedure is called by Before Process
Business Process User Hook of PAY_TIME_DEFINITION_API.
******************************************************************************/
PROCEDURE update_user_hook_timedef (
p_time_definition_id IN NUMBER
,p_definition_name IN VARCHAR2
,p_period_type IN VARCHAR2
,p_start_date IN DATE
,p_period_time_definition_id IN NUMBER
,p_creator_id IN NUMBER
,p_creator_type IN VARCHAR2
) IS
lv_rec_pd_exist VARCHAR2(1);
hr_utility.TRACE('Entering HXC_US_TIME_DEFINITIONS_HOOK.UPDATE_USER_HOOK_TIMEDEF');
'SELECT ''Y''
FROM hxc_recurring_periods
WHERE recurring_period_id = ' || p_creator_id;
is getting updated*/
status_otl_time_def(
p_time_definition_id => p_time_definition_id
,p_definition_name => p_definition_name
,p_period_type => p_period_type
,p_start_date => p_start_date
,p_period_time_definition_id => p_period_time_definition_id
);
hr_utility.TRACE ('Leaving HXC_US_TIME_DEFINITIONS_HOOK.UPDATE_USER_HOOK_TIMEDEF');
END update_user_hook_timedef;