[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name INTO l_rec_period
FROM hxc_recurring_periods
WHERE recurring_period_id = l_rec_period_id;
SELECT full_name INTO l_supervisor_name
FROM per_all_people_f
WHERE person_id = l_supervisor_id
AND sysdate between effective_start_date and effective_end_date;
SELECT name INTO l_org_name
FROM hr_all_organization_units
WHERE organization_id = l_org_id;
SELECT location_code INTO l_location_name
FROM hr_locations_all
WHERE location_id = l_location_id;
-- the selected column is an MIN value without a GROUP BY
-- it would always return a value -- a NULL date.
-- This would create problems in the following Preference
-- Evaluation call.
-- Added the GROUP BY clause so no row is returned when
-- the WHERE clause fails.
l_EvalDateSql := 'SELECT GREATEST(:1,tmp.start_date)
FROM
(SELECT min(effective_start_date) start_date
FROM per_all_assignments_f
WHERE person_id = :2
AND assignment_type IN (''E'', ''C'')
AND primary_flag = ''Y''
AND ((trunc(effective_start_date) <= trunc(:3)
AND trunc(effective_end_date) >= trunc(:4))
OR (effective_start_date = (SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE person_id = :5
AND assignment_type IN (''E'', ''C'')
AND primary_flag = ''Y''
AND trunc(effective_start_date) > trunc(:6)
AND trunc(effective_start_date) <= trunc(:7)))) GROUP BY person_id ) tmp';
SELECT 'Y' FROM dual
WHERE (
EXISTS (
SELECT 'Y'
FROM hxc_timecard_summary
WHERE resource_id = p_person_id
AND TRUNC(start_time) >= p_period_start_date
AND TRUNC(stop_time) < p_period_end_date)
AND NOT EXISTS (
SELECT 'Y'
FROM hxc_timecard_summary
WHERE resource_id = p_person_id
AND TRUNC(start_time) > p_period_start_date
AND TRUNC(stop_time) <= p_period_end_date)
)
AND EXISTS
(SELECT 'Y'
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND assignment_type IN('E','C')
AND primary_flag = 'Y'
AND trunc(effective_start_date) <= trunc(p_period_start_date)
AND trunc(effective_end_date) >= trunc(p_period_end_date));
l_sql := 'SELECT max(effective_start_date)
FROM per_all_assignments_f
WHERE person_id = :1
AND assignment_type IN (''E'', ''C'')
AND primary_flag = ''Y''
AND ((effective_start_date <= :2
AND effective_end_date >= :3)
OR (effective_start_date > :4
AND effective_start_date <= :5)
OR (effective_start_date <= :6
AND effective_end_date <= :7))';
SELECT start_time INTO l_tc_start_time FROM hxc_timecard_summary WHERE timecard_id = p_timecard_id;
--In case of mid period reverse termination, check if a timecard already exists in the selected timecard period
--but with the end date as the termination date
--If yes, then there will be a not entered timecard for the rest of the timecard period
-- Bug 8205132
-- Added input parameters for the cursor.
OPEN c_chk_tc_exists( p_person_id => p_person_id,
p_period_start_date => l_period_start_date,
p_period_end_date => l_period_end_date );
l_sql := 'SELECT TRUNC(stop_time) + 1
FROM hxc_timecard_summary
WHERE resource_id = :1
AND TRUNC(start_time) >= :2
AND TRUNC(stop_time) < :3';
l_pref_table.DELETE;
SELECT max(effective_end_date) INTO l_tc_end_date
FROM per_all_assignments_f
WHERE person_id = p_person_id
and effective_end_date >= g_tc_start_date
and primary_flag = 'Y'
and assignment_type in ('E','C');
SELECT LEAST(l_pref_end_date,l_tc_end_date,l_period_end_date) INTO l_tc_end_date FROM dual;
SELECT stop_time INTO l_tc_stop_date FROM hxc_timecard_summary WHERE timecard_id = p_timecard_id;
l_sql := 'SELECT temp.*, ppt.user_person_type PERSON_TYPE, hasv.application_set_name APPLICATION
FROM
(SELECT distinct person_id,
person_name,
person_number,
(NVL(tim.approval_status,''NOTENTERED'')) AS approval_status,
supervisor_name,
organization,
location,
payroll,
hxc_tcd_xml_pkg.get_timecard_start_date(person_id,tim.timecard_id) AS start_date,
hxc_tcd_xml_pkg.get_timecard_end_date(person_id,tim.timecard_id) AS end_date,
(SELECT user_name FROM fnd_user
WHERE user_id = (SELECT last_updated_by FROM hxc_time_building_blocks
WHERE scope = ''TIMECARD''
AND resource_id = person_id
AND time_building_block_id = tim.timecard_id
AND date_to = hr_general.end_of_time)
) AS last_updated_by,
(SELECT last_update_date FROM hxc_time_building_blocks
WHERE scope = ''TIMECARD''
AND resource_id = person_id
AND time_building_block_id = tim.timecard_id
AND date_to = hr_general.end_of_time
) AS last_update_date
FROM HXC_TCD_DETAILS_V v,
((SELECT resource_id, timecard_id, approval_status FROM hxc_timecard_summary
WHERE resource_id IN (
';
(SELECT person_id AS
resource_id,
NULL timecard_id,
NULL approval_status
FROM per_all_assignments_f paaf
WHERE person_id IN(';
(SELECT ''Y''
FROM hxc_timecard_summary
WHERE resource_id = paaf.person_id
AND TRUNC(start_time) >= :3
AND TRUNC(stop_time) < :4)
AND NOT EXISTS
(SELECT ''Y''
FROM hxc_timecard_summary
WHERE resource_id = paaf.person_id
AND TRUNC(start_time) > :31
AND TRUNC(stop_time) <= :41) )
AND EXISTS
(SELECT ''Y''
FROM per_all_assignments_f
WHERE person_id = paaf.person_id
AND assignment_type IN(''E'', ''C'')
AND primary_flag = ''Y''
AND((TRUNC(effective_start_date) <= :5
AND TRUNC(effective_end_date) >= :6) OR(TRUNC(effective_start_date) > :7
AND TRUNC(effective_start_date) <= :8)))))
tim ';
INSERT INTO HXC_TCD_TMP_RPT(
RESOURCE_ID ,
REC_PERIOD_ID ,
PERIOD_START_DATE,
PERIOD_END_DATE ,
SUPERVISOR_ID ,
LOCATION_ID ,
ORGANIZATION_ID,
SEL_SUPERVISOR_ID,
SEL_TC_STATUS ,
RPT_STATUS ,
PERSON_TYPE ,
PERSON_NAME ,
PERSON_NUMBER ,
APPROVAL_STATUS,
SUPERVISOR ,
ORGANIZATION ,
LOCATION ,
PAYROLL ,
APPLICATION ,
TC_START_DATE ,
TC_END_DATE ,
LAST_MODIFIED_BY,
LAST_MODIFIED_DATE
)
VALUES(l_resource_id,
l_rec_period_id,
l_period_start_date,
l_period_end_date,
l_supervisor_id,
l_location_id,
l_org_id,
l_sel_supervisor_id,
l_sel_tc_status,
l_rpt_status,
l_tcd_rpt(i).PERSON_TYPE,
l_tcd_rpt(i).PERSON_NAME,
l_tcd_rpt(i).PERSON_NUMBER,
l_tcd_rpt(i).APPROVAL_STATUS,
l_tcd_rpt(i).SUPERVISOR,
l_tcd_rpt(i).ORGANIZATION,
l_tcd_rpt(i).LOCATION,
l_tcd_rpt(i).PAYROLL,
l_tcd_rpt(i).APPLICATION,
l_tcd_rpt(i).TC_START_DATE,
l_tcd_rpt(i).TC_END_DATE,
l_tcd_rpt(i).LAST_MODIFIED_BY,
l_tcd_rpt(i).LAST_MODIFIED_DATE);
-- Selected a link from dashboard summary
l_supervisor := l_sel_supervisor_id;
-- Selected a link from dashboard summary totals
l_supervisor := l_supervisor_id;
l_directsSQL := 'SELECT DISTINCT person_id
FROM per_all_assignments_f paaf
WHERE assignment_type IN (''E'', ''C'')
AND primary_flag = ''Y''
AND supervisor_id = :1
AND (
( trunc(effective_start_date) <= :2
AND trunc(effective_end_date) >= :3
)
OR
( trunc(effective_start_date) > :4
AND trunc(effective_end_date) >= :5
AND trunc(effective_end_date) > :6
)
OR
( trunc(effective_start_date) > :7
AND trunc(effective_end_date) < :8
)
) ';
l_directsSQL := 'SELECT DISTINCT person_id
FROM per_all_assignments_f paaf
WHERE assignment_type IN (''E'', ''C'')
AND primary_flag = ''Y''
AND supervisor_id = :1
AND trunc(effective_start_date) <= :2
AND trunc(effective_end_date) >= :3 ';
l_allEmpSQL := 'SELECT distinct person_id
FROM per_all_assignments_f asgn
WHERE person_id <> :1
AND primary_flag = ''Y''
AND assignment_type in (''E'',''C'')';
l_sql := 'DELETE FROM HXC_TCD_TMP_RPT
WHERE RESOURCE_ID = :1
AND REC_PERIOD_ID = :2
AND PERIOD_START_DATE = :3
AND PERIOD_END_DATE = :4
AND SUPERVISOR_ID = :5
AND LOCATION_ID = :6
AND ORGANIZATION_ID = :7
AND SEL_SUPERVISOR_ID = :8
AND SEL_TC_STATUS = :9
AND RPT_STATUS = :10';