[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT
asg.person_id,
asg.payroll_id,
asg.location_id,
asg.supervisor_id,
asg.organization_id,
greatest(asg.EFFECTIVE_START_DATE,:1),
least(asg.EFFECTIVE_END_DATE,:2),
1,
1,
asg.EFFECTIVE_START_DATE,
asg.EFFECTIVE_END_DATE
FROM
per_assignments_f asg
, per_assignment_status_types past
WHERE asg.supervisor_id = :3
AND asg.assignment_type IN ( ''E'' , ''C'' )
AND asg.primary_flag = ''Y''
AND trunc(asg.effective_start_date) <= :4
AND trunc(asg.effective_end_date) >= :5
AND past.assignment_status_type_id = asg.assignment_status_type_id
AND past.per_system_status IN (''ACTIVE_ASSIGN'', ''ACTIVE_CWK'')';
l_all_direct_sql VARCHAR2(32000) := 'SELECT
DISTINCT
perasn.person_id,
perasn.payroll_id,
perasn.location_id,
perasn.supervisor_id,
perasn.organization_id,
greatest(perasn.EFFECTIVE_START_DATE,:1),
least(perasn.EFFECTIVE_END_DATE,:2) ,
1,
1,
perasn.EFFECTIVE_START_DATE,
perasn.EFFECTIVE_END_DATE
FROM
per_assignments_f perasn
, per_assignment_status_types past
WHERE perasn.person_id <> :3
AND perasn.primary_flag = ''Y''
AND assignment_type IN (''E'' , ''C'' )
START WITH person_id = :4
AND trunc(effective_start_date) <= :5
AND trunc(effective_end_date) >= :6
CONNECT BY PRIOR person_id = supervisor_id
AND ( ( trunc(effective_start_date) <= :7
AND trunc(effective_end_date) >= :8 ) )
AND past.assignment_status_type_id = perasn.assignment_status_type_id
AND past.per_system_status IN (''ACTIVE_ASSIGN'', ''ACTIVE_CWK'')';
l_sql varchar2(32000) DEFAULT 'SELECT
tim.resource_id,
tim.timecard_id,
tim.timecard_ovn,
tim.start_time,
tim.stop_time,
DECODE((trunc(LEAD(tim.start_time,1,NULL) over (partition by tim.resource_id Order by tim.stop_time))-1 - trunc(tim.stop_time)),0,NULL,tim.stop_time + 1),
DECODE((trunc(LEAD(tim.start_time,1,NULL) over (partition by tim.resource_id Order by tim.stop_time))-1 - trunc(tim.stop_time)),0,
NULL,LEAD(tim.start_time,1,NULL) over (partition by tim.resource_id Order by tim.stop_time) -1),
tim.approval_status
FROM
hxc_timecard_summary tim, hxc_temp_tcd temp
WHERE tim.resource_id = temp.resource_id
tim.stop_time >= temp.start_time
and tim.start_time <= temp.stop_time';
' select resource_id, start_time,
stop_time, approval_status,
timecard_exist from HXC_TEMP_TCD';
SELECT tim.resource_id
, tim.timecard_id
, tim.timecard_ovn
, tim.start_time
, tim.stop_time
, decode (lag (tim.start_time) OVER (PARTITION BY tim.resource_id
, temp.period_exist
ORDER BY tim.stop_time) , NULL
, decode (abs (tim.start_time - temp.start_time), tim.start_time - temp.start_time
, temp.start_time), NULL) first_start
, decode (lag (tim.start_time) OVER (PARTITION BY tim.resource_id
, temp.period_exist
ORDER BY tim.stop_time) , NULL
, decode (abs (tim.start_time - temp.start_time), tim.start_time - temp.start_time
, tim.start_time - 1), NULL) first_stop
, decode ((trunc (lead (tim.start_time) OVER (PARTITION BY tim.resource_id
, temp.period_exist
ORDER BY tim.stop_time) ) - 1 - trunc (tim.stop_time)), 0
, NULL, tim.stop_time + 1) next_start
, decode ((trunc (lead (tim.start_time) OVER (PARTITION BY tim.resource_id
, temp.period_exist
ORDER BY tim.stop_time) ) - 1 - trunc (tim.stop_time)), 0
, NULL, nvl ((lead (tim.start_time, 1
, NULL) OVER (PARTITION BY tim.resource_id
, temp.period_exist
ORDER BY tim.stop_time) - 1), temp.stop_time)) next_end
, tim.approval_status
, temp.recurring_period_id
, temp.person_number
, temp.full_name
, temp.payroll_name
, temp.organization_name
, temp.location_name
, temp.supervisor_id
, temp.supervisor_name
, temp.application
, decode (temp.period_exist, 'T'
, 'P', temp.period_exist)
, temp.start_time
, temp.stop_time
FROM hxc_timecard_summary tim
, hxc_temp_tcd temp
WHERE tim.stop_time >= temp.start_time
AND tim.start_time <= temp.stop_time
AND temp.resource_id = tim.resource_id
AND temp.user_id = p_user_id;
SELECT trunc (((p_start_date - 1) + (p_year_days / p_number_per_fiscal_year) * (level - 1)) + 1)
, trunc (((p_start_date - 1) + ((p_year_days / p_number_per_fiscal_year) * (level - 1))) + (p_year_days / p_number_per_fiscal_year))
FROM dual
WHERE trunc (((p_start_date - 1) + ((p_year_days / p_number_per_fiscal_year) * (level - 1))) + (p_year_days / p_number_per_fiscal_year)) <= to_date (p_end_date, fnd_profile.value ('ICX_DATE_FORMAT_MASK'))
CONNECT BY level <= (p_number_per_fiscal_year + 1);
SELECT add_months (p_start_date, (level - 1)) start_date
, (add_months (p_start_date, level) - 1) end_date
FROM dual
WHERE (add_months (p_start_date, level) - 1) <= p_end_date
CONNECT BY level <= ceil (months_between (p_end_date, p_start_date));
SELECT add_months (p_start_date, ((level - 1)*12)) start_date
, (add_months (p_start_date, (level*12)) - 1) end_date
FROM dual
WHERE (add_months (p_start_date, (level*12)) - 1) <= p_end_date
CONNECT BY level <= (ceil (months_between (p_end_date, p_start_date))/12);
SELECT add_months (p_start_date, ((level - 1)*12)) start_date
, (add_months (p_start_date, (level*12)) - 1) end_date
FROM dual
WHERE (add_months (p_start_date, (level*12)) - 1) <= p_end_date
CONNECT BY level <= (ceil (months_between (p_end_date, p_start_date))/12);
SELECT
trunc(((p_start_date - 1) + p_duration_in_days * (level - 1)) + 1) startdate,
trunc(((p_start_date - 1) + (p_duration_in_days * (level - 1))) + p_duration_in_days) enddate
FROM
dual
WHERE trunc(((p_start_date - 1) + (p_duration_in_days * (level - 1))) + p_duration_in_days) <= p_end_date
CONNECT BY level <= trunc(p_end_date - p_start_date + 1) / (p_duration_in_days);
SELECT
last_update_login,
last_update_date
FROM
hxc_time_building_blocks
WHERE scope = 'TIMECARD'
AND time_building_block_id = p_timecard_id
AND object_version_number = p_timecard_ovn
AND date_to = hr_general.end_of_time;
Delete extra records from GTT hxc_temp_tcd.
*/
PROCEDURE delete_temp_extra_rec(p_user_id number)
AS
BEGIN
/**
* Initially, when the data is populated with the global start_time and stop_time (the search criteria)
* the period exist flag will be 'T'. If a person is terminated mid-period then the flag is 'M' and if
* the person is hired (or) rehired then the flag will be 'R'
*
* However, when the start_time and stop_time in the table are
* adjusted based on if a timecard exists between the search criteria (period_exist flag is 'P' or 'p') or
* if the person is terminated mid-period (period_exist flag is 'm') or if the person is hired or re-hired
* mid-period (period_exist flag is 'r') a new row is inserted with the corresponding flag.
*
* Since, hire, rehire and mid-period termination within the search criteria is already captured in the table
* this detail must be preserved after the period generation. Hence the duplicate values are deleted here.
*/
IF g_debug THEN
hr_utility.trace('deleting temp extra records');
DELETE FROM hxc_temp_tcd
WHERE resource_id in (SELECT distinct resource_id FROM hxc_temp_tcd WHERE period_exist = 'P' or period_exist = 'p')
AND period_exist = 'T';
DELETE FROM hxc_temp_tcd
WHERE resource_id in (SELECT distinct resource_id FROM hxc_temp_tcd WHERE period_exist = 'm')
AND period_exist = 'M';
DELETE FROM hxc_temp_tcd
WHERE resource_id in (SELECT distinct resource_id FROM hxc_temp_tcd WHERE period_exist = 'r')
AND period_exist = 'R';
END delete_temp_extra_rec;
PROCEDURE delete_extra_rec(p_user_id number)
AS
BEGIN
IF g_debug THEN
l_proc := g_package||'delete_extra_rec';
DELETE
FROM HXC_TCD_TIMECARDS htt
WHERE EXISTS
(
SELECT 1
FROM HXC_TCD_TIMECARDS htt1
WHERE htt1.resource_id = htt.resource_id
AND trunc (htt1.period_start_date) = trunc (htt.period_start_date)
AND htt1.approval_status <> htt.approval_status
AND htt.approval_status = 'NOTENTERED'
AND htt1.user_id = p_user_id
AND htt1.user_id = htt.user_id
)
AND htt.user_id = p_user_id;
* The following script deletes duplicate records for mid period
* assignment changes.
*/
DELETE
FROM hxc_tcd_timecards t1
WHERE EXISTS
(
SELECT 1
FROM hxc_tcd_timecards t2
WHERE t1.user_id = p_user_id
AND t1.resource_id = t2.resource_id
AND t2.user_id = t1.user_id
AND t1.period_start_date = t2.period_start_date
AND t1.period_end_date = t2.period_end_date
AND t1.approval_status = t2.approval_status
AND (
t1.supervisor <> t2.supervisor
OR t1.organization <> t2.organization
OR t1.location <> t2.location
OR t1.person_type <> t2.person_type
)
AND t1.rowid > t2.rowid
);
l_proc := g_package||'delete_extra_rec';
END delete_extra_rec;
PROCEDURE delete_person_temp(p_recurring_period number, p_user_id number)
AS
BEGIN
IF g_debug THEN
l_proc := g_package||'delete_person_temp';
DELETE FROM HXC_TEMP_TCD
WHERE recurring_period_id <> p_recurring_period
AND user_id = p_user_id
AND PERIOD_EXIST = 'T';
l_proc := g_package||'delete_person_temp';
END delete_person_temp;
DELETE FROM HXC_TEMP_TCD
WHERE user_id = p_user_id;
DELETE FROM HXC_TCD_TIMECARDS
WHERE user_id = p_user_id;
DELETE FROM HXC_TCD_STATUS_COUNT
WHERE user_id = p_user_id;
/* clear data from temporary tables based on fnd_logins. Data less than sysdate will be deleted*/
PROCEDURE clear_all_inactive_logins
AS
CURSOR c_get_inactive_sessions
IS
SELECT distinct hxc.user_id
FROM fnd_logins fnd, hxc_tcd_timecards hxc
WHERE
hxc.user_id = fnd.login_id
AND
(fnd.start_time < sysdate - 1 OR fnd.end_time IS NOT NULL);
DELETE FROM HXC_TCD_TIMECARDS
WHERE user_id = l_login_ids(i);
DELETE FROM HXC_TCD_STATUS_COUNT
WHERE user_id = l_login_ids(i);
* Employees and contingent workers have different service period records and hence we have two cursors to update the
* assignment changes.
*/
PROCEDURE update_assignment_changes(l_user_id number)
AS
CURSOR c_emp_hire_records(p_user_id number,p_period_exist VARCHAR2)
IS
SELECT
htt.resource_id
,htt.start_time
,htt.stop_time
,ppos.date_start period_start_date
,NVL(ppos.actual_termination_date,hr_general.end_of_time) period_end_date
FROM
hxc_temp_tcd htt
,per_periods_of_service ppos
WHERE (htt.period_exist = p_period_exist OR
htt.period_exist = lower(p_period_exist))
AND ppos.person_id = htt.resource_id
AND p_start_date > ppos.date_start
AND p_end_date < NVL(ppos.actual_termination_date, hr_general.end_of_time)
AND htt.user_id = p_user_id;
SELECT
htt.resource_id
,htt.start_time
,htt.stop_time
,ppos.date_start period_start_date
,NVL(ppos.actual_termination_date,hr_general.end_of_time) period_end_date
FROM
hxc_temp_tcd htt
,per_periods_of_placement ppos
WHERE (htt.period_exist = p_period_exist OR
htt.period_exist = lower(p_period_exist))
AND ppos.person_id = htt.resource_id
AND p_start_date > ppos.date_start
AND p_end_date < NVL(ppos.actual_termination_date,hr_general.end_of_time)
AND htt.user_id = p_user_id;
SELECT htt.start_time
, htt.stop_time
, htt.resource_id
, decode (greatest (asg.effective_start_date, htt.start_time), asg.effective_start_date
, 'M', 'R')
FROM per_all_assignments_f asg
, per_assignment_status_types past
, hxc_temp_tcd htt
WHERE asg.person_id = htt.resource_id
AND asg.effective_start_date > p_start_date
AND asg.effective_end_date < p_end_date
AND htt.user_id = p_user_id
AND asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('SUSP_CWK_ASG', 'SUSP_ASSIGN');
SELECT htt.resource_id
, htt.start_time
, htt.stop_time
, min (paaf.effective_start_date) start_date
, max (paaf.effective_end_date) end_date
FROM hxc_temp_tcd htt
, per_all_assignments_f paaf
, per_assignment_status_types past
WHERE paaf.person_id = htt.resource_id
AND paaf.effective_start_date <= p_end_date
AND paaf.effective_end_date >= p_start_date
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
AND htt.user_id = p_user_id
AND period_exist = 'T'
GROUP BY htt.resource_id
, htt.start_time
, htt.stop_time;
SELECT resource_id
, start_time
, stop_time
, period_exist
FROM hxc_temp_tcd
WHERE user_id = p_user_id;
hr_utility.trace('Entering update assignment changes ');
UPDATE hxc_temp_tcd
SET period_exist = 'T',
period_start_date = l_period_start_date(i),
period_end_date = l_period_end_date(i)
WHERE user_id = l_user_id
AND resource_id = l_resource_id(i)
AND start_time = l_start_time(i)
AND stop_time = l_stop_time(i)
AND (period_exist = 'R'
OR period_exist = 'r');
UPDATE hxc_temp_tcd
SET
period_start_date = l_period_start_date(i),
period_end_date = l_period_end_date(i)
WHERE user_id = l_user_id
AND resource_id = l_resource_id(i)
AND start_time = l_start_time(i)
AND stop_time = l_stop_time(i)
AND period_exist = 'T';
UPDATE hxc_temp_tcd
SET period_exist = 'T',
period_start_date = l_period_start_date(i),
period_end_date = l_period_end_date(i)
WHERE user_id = l_user_id
AND resource_id = l_resource_id(i)
AND start_time = l_start_time(i)
AND stop_time = l_stop_time(i)
AND (period_exist = 'm'
OR period_exist = 'M');
UPDATE hxc_temp_tcd
SET period_exist = 'T',
period_start_date = l_period_start_date(i),
period_end_date = l_period_end_date(i)
WHERE user_id = l_user_id
AND resource_id = l_resource_id(i)
AND start_time = l_start_time(i)
AND stop_time = l_stop_time(i)
AND (period_exist = 'R'
OR period_exist = 'r');
UPDATE hxc_temp_tcd
SET period_exist = 'T',
period_start_date = l_period_start_date(i),
period_end_date = l_period_end_date(i)
WHERE user_id = l_user_id
AND resource_id = l_resource_id(i)
AND start_time = l_start_time(i)
AND stop_time = l_stop_time(i)
AND (period_exist = 'm'
OR period_exist = 'M');
UPDATE hxc_temp_tcd
SET period_exist = l_exist(i)
WHERE user_id = l_user_id
AND resource_id = l_resource_id(i)
AND start_time = l_start_time(i)
AND stop_time = l_stop_time(i);
UPDATE hxc_temp_tcd
SET ASSG_START_DATE = l_period_start_date(i)
, ASSG_END_DATE = l_period_end_date(i)
WHERE user_id = l_user_id
AND resource_id = l_resource_id(i)
AND start_time = l_start_time(i)
AND stop_time = l_stop_time(i);
hr_utility.trace('Leaving update assignment changes ');
END update_assignment_changes;
Procedure to update employee_number and person_name in GTT
*/
PROCEDURE update_person_det(p_user_id number)
AS
CURSOR c_person_temp(p_user_id number)
IS
SELECT
nvl(per.employee_number
,per.npw_number) person_number,
per.full_name,
htt.resource_id
FROM
hxc_temp_tcd htt,
per_all_people_f per
WHERE htt.resource_id = per.person_id
AND trunc(per.effective_start_date) <= trunc(SYSDATE)
AND trunc(per.effective_end_date) >= trunc(SYSDATE)
AND htt.user_id = p_user_id;
l_proc := g_package||'update_person_det';
UPDATE hxc_temp_tcd
SET person_number = l_person_ids(i),
full_name = l_person_names(i)
WHERE user_id = p_user_id
AND resource_id = l_resource_ids(i);
END update_person_det;
Procedure to update payroll_name in GTT
*/
PROCEDURE update_payroll(p_user_id IN number)
AS
CURSOR c_payroll_temp(p_user_id number)
IS
SELECT
DISTINCT
pap.payroll_name,
htt.payroll_name
FROM
pay_all_payrolls_f pap, hxc_temp_tcd htt
WHERE pap.payroll_id = htt.payroll_name
AND trunc(pap.effective_start_date) <= trunc(SYSDATE)
AND trunc(pap.effective_end_date) >= trunc(SYSDATE)
AND htt.user_id = p_user_id;
l_proc := g_package||'update_payroll';
UPDATE hxc_temp_tcd
SET payroll_name = l_payroll_names(i)
WHERE user_id = p_user_id
AND payroll_name = l_payroll_ids(i);
END update_payroll;
Procedure to update organization_name in GTT
*/
PROCEDURE update_organization(p_user_id IN number)
AS
CURSOR c_organization_temp(p_user_id number)
IS
SELECT
DISTINCT
hxo.name,
htt.organization_name
FROM
hr_all_organization_units_tl hxo, hxc_temp_tcd htt
WHERE hxo.organization_id = htt.organization_name
AND htt.user_id = p_user_id
AND hxo.language = userenv('LANG');
l_proc := g_package||'update_organization';
UPDATE hxc_temp_tcd
SET organization_name = l_organization_names(i)
WHERE user_id = p_user_id
AND organization_name = l_organization_ids(i);
END update_organization;
Procedure to update location_name in GTT
*/
PROCEDURE update_location(p_user_id IN number)
AS
CURSOR c_location_temp(p_user_id number)
IS
SELECT
DISTINCT
hxl.location_code,
htt.location_name
FROM
hr_locations_all_tl hxl, hxc_temp_tcd htt
WHERE hxl.location_id = htt.location_name
AND htt.user_id = p_user_id
AND hxl.language = userenv('LANG');
l_proc := g_package||'update_location';
UPDATE hxc_temp_tcd
SET location_name = l_location_names(i)
WHERE user_id = p_user_id
AND location_name = l_location_ids(i);
END update_location;
Procedure to update supervisor_name in GTT
*/
PROCEDURE update_supervisor(p_user_id IN number)
AS
CURSOR c_supervisor_temp(p_user_id number)
IS
SELECT
DISTINCT
per.full_name,
htt.supervisor_name
FROM
per_all_people_f per, hxc_temp_tcd htt
WHERE per.person_id = htt.supervisor_name
AND htt.user_id = p_user_id;
l_proc := g_package||'update_supervisor';
UPDATE hxc_temp_tcd
SET supervisor_name = l_supervisor_names(i)
WHERE user_id = p_user_id
AND supervisor_name = l_supervisor_ids(i);
END update_supervisor;
Procedure to update person_type in GTT
*/
PROCEDURE update_person_types(p_user_id IN number)
AS
CURSOR c_person_types(p_user_id number)
IS
SELECT
ppt.user_person_type,
htt.resource_id
FROM
per_person_types ppt,
per_person_type_usages_f ptu,
hxc_temp_tcd htt
WHERE htt.resource_id = ptu.person_id
AND ppt.person_type_id = ptu.person_type_id
AND trunc(ptu.effective_start_date) <= trunc(SYSDATE)
AND trunc(ptu.effective_end_date) >= trunc(SYSDATE)
AND htt.user_id = p_user_id;
l_proc := g_package||'update_person_types';
UPDATE hxc_temp_tcd
SET person_type = l_person_types(i)
WHERE user_id = p_user_id
AND resource_id = l_resource_ids(i);
END update_person_types;
Procedure to update application_set_name in GTT
*/
PROCEDURE update_application(p_user_id IN number)
AS
CURSOR c_timecard_ne(p_user_id number)
IS
SELECT
heg.name application_set_name,
htt.resource_id
FROM
hxc_entity_groups heg,
hxc_temp_tcd htt
WHERE heg.entity_type = 'TIME_RECIPIENTS'
AND htt.application IS NOT NULL
AND heg.entity_group_id = htt.application
AND htt.user_id = p_user_id;
l_proc := g_package||'update_application';
UPDATE hxc_temp_tcd
SET application = l_applications(i)
WHERE user_id = p_user_id
AND resource_id = l_resource_ids(i);
END update_application;
Procedure to update approval_status in GTT
*/
PROCEDURE insert_approval_meaning(p_user_id IN number)
AS
CURSOR c_approval_status(p_user_id number)
IS
SELECT distinct
flv.meaning,
htt.approval_status
FROM
fnd_lookup_values flv,
hxc_temp_tcd htt
WHERE flv.language = userenv('LANG')
AND flv.view_application_id = 3
AND flv.lookup_type = 'HXC_APPROVAL_STATUS'
AND htt.approval_status = flv.lookup_code
AND htt.user_id = p_user_id;
l_proc := g_package||'insert_approval_meaning';
UPDATE hxc_temp_tcd
SET meaning = l_meanings(i)
WHERE user_id = p_user_id
AND approval_status = l_approval_status(i);
END insert_approval_meaning;
PROCEDURE update_last_update_det(p_user_id IN number)
AS
CURSOR c_last_update_det(p_user_id number)
IS
SELECT
tim.last_updated_by,
tim.last_update_date,
htt.timecard_id,
htt.timecard_ovn
FROM
hxc_time_building_blocks tim,
hxc_temp_tcd htt
WHERE tim.scope like 'TIMECARD'
AND htt.timecard_id = tim.time_building_block_id
AND htt.timecard_ovn = tim.object_version_number
AND htt.user_id = p_user_id ;
l_last_update_logins NUMBERTAB;
l_last_update_dates DATETAB;
l_proc := g_package||'update_last_update_det';
OPEN c_last_update_det(p_user_id);
FETCH c_last_update_det BULK COLLECT INTO l_last_update_logins,
l_last_update_dates,
l_timecard_ids,
l_timecard_ovns
LIMIT 500 ;
UPDATE hxc_temp_tcd
SET last_modified_by = l_last_update_logins(i),
last_modified_date = l_last_update_dates(i)
WHERE user_id = p_user_id
AND timecard_id = l_timecard_ids(i)
AND timecard_ovn = l_timecard_ovns(i);
CLOSE c_last_update_det;
END update_last_update_det;
PROCEDURE update_last_update_by(p_user_id IN number)
AS
CURSOR c_update_last_modified_by(p_user_id number)
IS
SELECT DISTINCT
fu.user_name,
htt.last_modified_by
FROM
fnd_user fu,
hxc_temp_tcd htt
WHERE fu.user_id = htt.last_modified_by
AND htt.user_id = p_user_id;
l_proc := g_package||'update_last_update_det';
OPEN c_update_last_modified_by(p_user_id);
FETCH c_update_last_modified_by BULK COLLECT INTO l_user_names,
l_last_modified_by
LIMIT 500;
UPDATE hxc_temp_tcd
SET last_modified_by = l_user_names(i)
WHERE user_id = p_user_id
AND last_modified_by = l_last_modified_by(i)
AND last_modified_by IS NOT NULL;
CLOSE c_update_last_modified_by;
END update_last_update_by;
PROCEDURE insert_notification_id(p_user_id IN number)
AS
CURSOR c_timecards(p_user_id number)
IS
SELECT htt.timecard_id
FROM hxc_temp_tcd htt
WHERE htt.timecard_id IS NOT NULL
AND htt.approval_status LIKE 'SUBMITTED'
AND htt.user_id = p_user_id;
CURSOR c_insert_application_period_id(p_user_id number)
IS
SELECT htt.timecard_id
,hta.application_period_id
FROM hxc_temp_tcd htt
,hxc_tc_ap_links hta
WHERE htt.timecard_id = hta.application_period_id
AND htt.timecard_id IS NOT NULL
AND htt.user_id = p_user_id;
SELECT htal.timecard_id
,wias.notification_id
FROM hxc_tc_ap_links htal
,hxc_app_period_summary haps
,wf_item_attribute_values wiav
,wf_item_activity_statuses wias
,wf_notifications wf
WHERE htal.application_period_id = haps.application_period_id
AND wf.notification_id = wias.notification_id
AND htal.timecard_id = p_timecard_id
AND haps.approval_status = 'SUBMITTED'
AND wiav.item_type = 'HXCEMP'
AND wiav.item_key = haps.approval_item_key
AND wiav.name = 'APR_PERSON_ID'
AND wias.item_type = wiav.item_type
AND wias.item_key = wiav.item_key
AND wias.activity_status = 'NOTIFIED'
AND wf.recipient_role = p_user_name;
l_proc := g_package||'insert_notification_id';
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = fnd_global.user_id;
UPDATE hxc_temp_tcd
SET notification_id = l_notification_ids(j)
WHERE user_id = p_user_id
AND timecard_id = l_timecard_notif_ids(j);
END insert_notification_id;
Procedure to insert data in temporary table hxc_tcd_timecards
for timecards which exist.
*/
PROCEDURE insert_timecard_exist(p_user_id IN number)
AS
l_count number;
l_proc := g_package||'insert_timecard_exist';
INSERT INTO HXC_TCD_TIMECARDS
(
resource_id,
rec_period_id,
period_start_date,
period_end_date,
person_number,
person_name,
approval_status,
supervisor_id,
supervisor,
organization,
location,
person_type,
payroll_name,
last_modified_by,
last_modified_date,
application,
user_id,
notification_id,
timecard_id,
timecard_ovn ,
meaning
)
SELECT
resource_id,
recurring_period_id,
start_time,
stop_time,
person_number,
full_name,
approval_status,
supervisor_id,
supervisor_name,
organization_name,
location_name,
person_type,
payroll_name,
last_modified_by,
last_modified_date,
application,
user_id,
notification_id,
timecard_id,
timecard_ovn,
meaning
FROM
hxc_temp_tcd
WHERE timecard_exist = 'Y'
AND user_id = p_user_id;
END insert_timecard_exist;
Procedure to insert data in temporary table hxc_tcd_timecards
for timecards which does not exist.
*/
PROCEDURE insert_timecard_not_exist(p_user_id IN number)
AS
CURSOR c_temp_data(p_user_id number)
IS
SELECT
resource_id,
start_time,
stop_time,
approval_status,
recurring_period_id,
person_number,
full_name,
payroll_name,
organization_name,
location_name,
supervisor_id,
supervisor_name,
person_type,
application,
meaning,
period_exist,
assg_start_date,
assg_end_date,
period_start_date,
period_end_date
FROM
hxc_temp_tcd
WHERE timecard_exist = 'N'
AND user_id = p_user_id;
SELECT
hrp.recurring_period_id,
hrp.period_type,
hrp.start_date,
to_char(to_date(hrp.start_date)
,'D') day,
hrp.duration_in_days
FROM
hxc_recurring_periods hrp
WHERE
hrp.recurring_period_id = p_recurring_id;
l_proc := g_package||'insert_timecard_not_exist';
SELECT ptt.number_per_fiscal_year INTO l_number_per_fiscal_year
FROM per_time_period_types ptt
WHERE ptt.period_type = l_period_type
AND ptt.system_flag = 'Y';
INSERT INTO HXC_TCD_TIMECARDS
(
resource_id,
rec_period_id,
period_start_date,
period_end_date,
person_number,
person_name,
approval_status,
supervisor_id,
supervisor,
organization,
location,
person_type,
payroll_name,
/* last_modified_by
last_modified_date */
application,
meaning,
user_id
)
VALUES
(
l_timecard(i).resource_id,
l_timecard(i).recurring_period_id,
l_period_start_date(j),
l_period_end_date(j),
l_timecard(i).person_number,
l_timecard(i).full_name,
l_timecard(i).approval_status,
l_timecard(i).supervisor_id,
l_timecard(i).supervisor_name,
l_timecard(i).organization_name,
l_timecard(i).location_name,
l_timecard(i).person_types,
l_timecard(i).payroll_name,
l_timecard(i).application,
l_timecard(i).meaning,
l_user_id
);
INSERT INTO HXC_TCD_TIMECARDS
(
resource_id,
rec_period_id,
period_start_date,
period_end_date,
person_number,
person_name,
approval_status,
supervisor_id,
supervisor,
organization,
location,
person_type,
payroll_name,
/* last_modified_by
last_modified_date */
application,
meaning,
user_id
)
VALUES
(
l_timecard(i).resource_id,
l_timecard(i).recurring_period_id,
l_period_start_date(j),
l_period_end_date(j),
l_timecard(i).person_number,
l_timecard(i).full_name,
l_timecard(i).approval_status,
l_timecard(i).supervisor_id,
l_timecard(i).supervisor_name,
l_timecard(i).organization_name,
l_timecard(i).location_name,
l_timecard(i).person_types,
l_timecard(i).payroll_name,
l_timecard(i).application,
l_timecard(i).meaning,
l_user_id
);
INSERT INTO HXC_TCD_TIMECARDS
(
resource_id,
rec_period_id,
period_start_date,
period_end_date,
person_number,
person_name,
approval_status,
supervisor_id,
supervisor,
organization,
location,
person_type,
payroll_name,
/* last_modified_by
last_modified_date */
application,
meaning,
user_id
)
VALUES
(
l_timecard(i).resource_id,
l_timecard(i).recurring_period_id,
l_period_start_date(j),
l_period_end_date(j),
l_timecard(i).person_number,
l_timecard(i).full_name,
l_timecard(i).approval_status,
l_timecard(i).supervisor_id,
l_timecard(i).supervisor_name,
l_timecard(i).organization_name,
l_timecard(i).location_name,
l_timecard(i).person_types,
l_timecard(i).payroll_name,
l_timecard(i).application,
l_timecard(i).meaning,
l_user_id
);
INSERT INTO HXC_TCD_TIMECARDS
(
resource_id,
rec_period_id,
period_start_date,
period_end_date,
person_number,
person_name,
approval_status,
supervisor_id,
supervisor,
organization,
location,
person_type,
payroll_name,
/* last_modified_by
last_modified_date */
application,
meaning,
user_id
)
VALUES
(
l_timecard(i).resource_id,
l_timecard(i).recurring_period_id,
l_period_start_date(j),
l_period_end_date(j+1),
l_timecard(i).person_number,
l_timecard(i).full_name,
l_timecard(i).approval_status,
l_timecard(i).supervisor_id,
l_timecard(i).supervisor_name,
l_timecard(i).organization_name,
l_timecard(i).location_name,
l_timecard(i).person_types,
l_timecard(i).payroll_name,
l_timecard(i).application,
l_timecard(i).meaning,
l_user_id
);
INSERT INTO HXC_TCD_TIMECARDS
(
resource_id,
rec_period_id,
period_start_date,
period_end_date,
person_number,
person_name,
approval_status,
supervisor_id,
supervisor,
organization,
location,
person_type,
payroll_name,
/* last_modified_by
last_modified_date */
application,
meaning,
user_id
)
VALUES
(
l_timecard(i).resource_id,
l_timecard(i).recurring_period_id,
l_period_start_date(j),
l_period_end_date(j),
l_timecard(i).person_number,
l_timecard(i).full_name,
l_timecard(i).approval_status,
l_timecard(i).supervisor_id,
l_timecard(i).supervisor_name,
l_timecard(i).organization_name,
l_timecard(i).location_name,
l_timecard(i).person_types,
l_timecard(i).payroll_name,
l_timecard(i).application,
l_timecard(i).meaning,
l_user_id
);
INSERT INTO HXC_TCD_TIMECARDS
(
resource_id,
rec_period_id,
period_start_date,
period_end_date,
person_number,
person_name,
approval_status,
supervisor_id,
supervisor,
organization,
location,
person_type,
payroll_name,
/* last_modified_by
last_modified_date */
application,
meaning,
user_id
)
VALUES
(
l_timecard(i).resource_id,
l_timecard(i).recurring_period_id,
l_period_start_date(j),
l_period_end_date(j),
l_timecard(i).person_number,
l_timecard(i).full_name,
l_timecard(i).approval_status,
l_timecard(i).supervisor_id,
l_timecard(i).supervisor_name,
l_timecard(i).organization_name,
l_timecard(i).location_name,
l_timecard(i).person_types,
l_timecard(i).payroll_name,
l_timecard(i).application,
l_timecard(i).meaning,
l_user_id
);
END insert_timecard_not_exist;
Procedure to insert data in temporary table hxc_tcd_status_count
for timecard's count
*/
--- *** Add Login id into the tables here.
PROCEDURE insert_timecard_count(p_user_id IN number)
AS
BEGIN
IF g_debug THEN
l_proc := g_package||'insert_timecard_count';
INSERT INTO HXC_TCD_STATUS_COUNT(supervisor_id,supervisor_name,not_enetered,working,error, submitted,rejected,approved,user_id)
SELECT
supervisor_id,
supervisor,
max(decode(approval_status
,'NOTENTERED'
,cnt
,0)) "Not Entered",
max(decode(approval_status
,'WORKING'
,cnt
,0)) "Working",
max(decode(approval_status
,'ERROR'
,cnt
,0)) "Error",
max(decode(approval_status
,'SUBMITTED'
,cnt
,0)) "Submitted",
max(decode(approval_status
,'REJECTED'
,cnt
,0)) "Rejected",
max(decode(approval_status
,'APPROVED'
,cnt
,0)) "Approved",
user_id
FROM
(SELECT
supervisor_id,
supervisor,
approval_status,
user_id,
count (*) cnt
FROM
hxc_tcd_timecards
GROUP BY
supervisor_id,
approval_status,
supervisor,
user_id )
WHERE user_id = p_user_id
GROUP BY
supervisor_id,
supervisor,
user_id;
l_proc := g_package||'insert_timecard_count';
END insert_timecard_count;
Procedure to update total in temporary table hxc_tcd_status_count
*/
PROCEDURE update_total(p_user_id IN number)
AS
CURSOR c_calc_total(p_user_id number)
IS
SELECT
not_enetered + working + error + submitted + rejected + approved "Total", supervisor_id
FROM
hxc_tcd_status_count
WHERE user_id = p_user_id;
UPDATE hxc_tcd_status_count
SET total = l_totals(i)
WHERE user_id = p_user_id
AND supervisor_id = l_supervisor_ids(i);
END update_total;
INSERT INTO HXC_TEMP_TCD
(resource_id,
start_time,
stop_time,
approval_status,
user_id,
recurring_period_id,
timecard_exist,
person_number,
full_name,
payroll_name,
organization_name,
location_name,
supervisor_id,
supervisor_name,
application,
period_exist
)
VALUES
(
l_person_det(i).person_id,
l_person_det(i).start_date,
l_person_det(i).end_date,
'NOTENTERED',
l_user_id,
l_person_det(i).recurring_period_id,
'N',
l_person_det(i).person_id,
l_person_det(i).person_id,
l_person_det(i).payroll_id,
l_person_det(i).organization_id,
l_person_det(i).location_id,
l_person_det(i).supervisor_id,
l_person_det(i).supervisor_id,
l_person_det(i).application_set_id,
l_flag
);
delete_person_temp(p_recurring_period,l_user_id);
INSERT INTO HXC_TEMP_TCD
(resource_id,
start_time,
stop_time,
approval_status,
user_id,
recurring_period_id,
timecard_exist,
person_number,
full_name,
payroll_name,
organization_name,
location_name,
supervisor_id,
supervisor_name,
application,
period_exist
)
VALUES
(
l_timecards_ne(i).resource_id,
l_timecards_ne(i).first_st_time,
l_timecards_ne(i).first_end_time,
'NOTENTERED',
l_user_id,
l_timecards_ne(i).recurring_period_id,
'N',
l_timecards_ne(i).resource_id,
l_timecards_ne(i).resource_id,
l_timecards_ne(i).payroll_name,
l_timecards_ne(i).organization_name,
l_timecards_ne(i).location_name,
l_timecards_ne(i).supervisor_id,
l_timecards_ne(i).supervisor_id,
l_timecards_ne(i).application,
lower(l_timecards_ne(i).period_exist)
);
INSERT INTO HXC_TEMP_TCD
(resource_id,
start_time,
stop_time,
approval_status,
user_id,
recurring_period_id,
timecard_exist,
person_number,
full_name,
payroll_name,
organization_name,
location_name,
supervisor_id,
supervisor_name,
application,
period_exist
)
VALUES
(
l_timecards_ne(i).resource_id,
l_timecards_ne(i).next_st_time,
l_timecards_ne(i).next_end_time,
'NOTENTERED',
l_user_id,
l_timecards_ne(i).recurring_period_id,
'N',
l_timecards_ne(i).resource_id,
l_timecards_ne(i).resource_id,
l_timecards_ne(i).payroll_name,
l_timecards_ne(i).organization_name,
l_timecards_ne(i).location_name,
l_timecards_ne(i).supervisor_id,
l_timecards_ne(i).supervisor_id,
l_timecards_ne(i).application,
lower(l_timecards_ne(i).period_exist)
);
INSERT INTO HXC_TEMP_TCD
(resource_id,
start_time,
stop_time,
timecard_id,
timecard_ovn,
approval_status,
user_id,
recurring_period_id,
timecard_exist,
person_number,
full_name,
payroll_name,
organization_name,
location_name,
supervisor_id,
supervisor_name,
application,
period_exist
)
VALUES
(
l_timecards_ne(i).resource_id,
l_timecards_ne(i).start_time,
l_timecards_ne(i).stop_time,
l_timecards_ne(i).timecard_id,
l_timecards_ne(i).timecard_ovn,
l_timecards_ne(i).approval_status,
l_user_id,
l_timecards_ne(i).recurring_period_id,
'Y',
l_timecards_ne(i).resource_id,
l_timecards_ne(i).resource_id,
l_timecards_ne(i).payroll_name,
l_timecards_ne(i).organization_name,
l_timecards_ne(i).location_name,
l_timecards_ne(i).supervisor_id,
l_timecards_ne(i).supervisor_id,
l_timecards_ne(i).application,
'P'
);
delete_temp_extra_rec(l_user_id);
update_assignment_changes(l_user_id);
update_person_det(l_user_id);
update_payroll(l_user_id);
update_organization(l_user_id);
update_location(l_user_id);
update_supervisor(l_user_id);
update_person_types(l_user_id);
update_application(l_user_id);
update_last_update_det(l_user_id);
update_last_update_by(l_user_id);
insert_notification_id(l_user_id);
insert_approval_meaning(l_user_id);
insert_timecard_exist(l_user_id);
insert_timecard_not_exist(l_user_id);
delete_extra_rec(l_user_id);
insert_timecard_count(l_user_id);
update_total(l_user_id);
SELECT person_type
,person_name
,person_number
,meaning
,supervisor
,organization
,location
,payroll_name
,application
,period_start_date
,period_end_date
,last_modified_by
,last_modified_date
FROM hxc_tcd_timecards htt
WHERE user_id = fnd_global.LOGIN_ID';
SELECT person_type
,person_name
,person_number
,meaning
,supervisor
,organization
,location
,payroll_name
,application
,period_start_date
,period_end_date
,last_modified_by
,last_modified_date
FROM hxc_tcd_timecards htt
WHERE user_id = fnd_global.login_id
AND supervisor_id = supervisor_id
AND approval_status = 'SUBMITTED';
SELECT full_name INTO l_supervisor_name
FROM per_all_people_f
WHERE person_id = p_supervisor_param
AND trunc (sysdate) BETWEEN effective_start_date
AND effective_end_date;
SELECT DISTINCT
name INTO l_organization_name
FROM hr_all_organization_units_tl
WHERE organization_id = p_organization
AND language = userenv('LANG');
SELECT DISTINCT
location_code INTO l_location_name
FROM hr_locations_all_tl
WHERE location_id = p_location
AND language = userenv('LANG');
query1 := 'SELECT '
|| 'user_name INITIATED_BY, '
|| 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') RUN_DATE '
|| 'from fnd_user '
|| 'where user_id = fnd_global.user_id' ;