[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT DISTINCT ret.resource_id,
sum.timecard_id,
sum.approval_status,
sum.start_time,
TRUNC(sum.stop_time),
FIRST_VALUE(ret.time_building_block_id)
OVER (PARTITION BY ret.timecard_id
ORDER BY ret.last_update_date DESC,
ret.time_building_block_id DESC),
FIRST_VALUE(ret.object_version_number)
OVER (PARTITION BY ret.timecard_id
ORDER BY ret.last_update_date DESC,
ret.time_building_block_id DESC),
FIRST_VALUE(ret.last_update_date)
OVER (PARTITION BY ret.timecard_id
ORDER BY ret.last_update_date DESC,
ret.time_building_block_id DESC)
FROM hxc_pa_latest_details ret,
hxc_timecard_summary sum
WHERE ret.last_update_date >= FND_DATE.canonical_to_date(:SINCEDATE)
AND ret.timecard_id = sum.timecard_id
AND ret.org_id = :ORGID ';
'SELECT DISTINCT ret.resource_id,
sum.timecard_id,
sum.approval_status,
sum.start_time,
TRUNC(sum.stop_time),
FIRST_VALUE(ret.time_building_block_id)
OVER (PARTITION BY ret.timecard_id
ORDER BY ret.last_update_date DESC,
ret.time_building_block_id DESC),
FIRST_VALUE(ret.object_version_number)
OVER (PARTITION BY ret.timecard_id
ORDER BY ret.last_update_date DESC,
ret.time_building_block_id DESC),
FIRST_VALUE(ret.last_update_date)
OVER (PARTITION BY ret.timecard_id
ORDER BY ret.last_update_date DESC,
ret.time_building_block_id DESC)
FROM hxc_pay_latest_details ret,
hxc_timecard_summary sum
WHERE ret.timecard_id = sum.timecard_id
AND ret.business_group_id = BUSINESSID ';
'AND EXISTS ( SELECT 1
FROM per_assignments_f paf /*Bug 12605349*/
WHERE paf.person_id = ret.resource_id
AND ret.start_time BETWEEN paf.effective_start_date
AND paf.effective_end_date
PAYROLLCRITERIA
ORGCRITERIA
GRECRITERIA )';
'AND EXISTS (SELECT 1
FROM per_people_f ppf
WHERE ppf.person_id = ret.resource_id
AND ret.start_time BETWEEN ppf.effective_start_date
AND ppf.effective_end_date)
';
PROCEDURE update_last_touched
IS
CURSOR get_last_touched
IS SELECT det.last_updated_by,
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_pre_timecards rdb,
hxc_time_building_blocks det,
fnd_user fnd
WHERE rdb.ret_user_id = FND_GLOBAL.user_id
AND rdb.lu_bb_id = det.time_building_block_id
AND rdb.lu_ovn = det.object_version_number
AND det.last_updated_by = fnd.user_id
AND fnd.employee_id <> det.resource_id
;
UPDATE hxc_rdb_pre_timecards
SET last_updated_by = usertab(i)
WHERE rowid = CHARTOROWID(rowtab(i));
END update_last_touched;
PROCEDURE update_supervisor
IS
CURSOR get_supervisor
IS SELECT paf.supervisor_id,
ROWIDTOCHAR(tc.rowid)
FROM hxc_rdb_pre_timecards tc,
per_assignments_f paf -- Bug 12605349
WHERE tc.ret_user_id = FND_GLOBAL.user_id
AND tc.resource_id = paf.person_id
AND tc.start_time BETWEEN paf.effective_start_date
AND paf.effective_end_date;
UPDATE hxc_rdb_pre_timecards
SET supervisor_id = suptab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
END update_supervisor;
PROCEDURE update_emp_details
IS
CURSOR get_emp_name
IS SELECT ppf.full_name,
DECODE(ppf.current_npw_flag,'Y',
ppf.npw_number,
ppf.employee_number),
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_pre_timecards rdb,
per_people_f ppf -- Bug 12605349
WHERE trunc(SYSDATE) BETWEEN ppf.effective_start_date --Bug 14026118
AND ppf.effective_end_date
AND rdb.resource_id = ppf.person_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_pre_timecards
SET emp_name = nametab(i),
emp_no = notab(i)
WHERE rowid = chartorowid(rowtab(i));
END update_emp_details;
'SELECT ret.resource_id,
ret.time_building_block_id,
ret.object_version_number ovn,
tc.approval_status,
ret.timecard_id,
ret.start_time date_worked,
ret.attribute1,
ret.attribute2,
ret.attribute3,
ret.measure,
ret.attribute1
from LATEST_DETAILS ret,
hxc_rdb_pre_timecards tc
where ret.timecard_id = tc.timecard_id
and tc.ret_user_id = USERID '
;
INSERT INTO hxc_rdb_pre_details
(resource_id,
time_building_block_id,
ovn,
approval_status,
timecard_id,
date_worked,
attribute1,
attribute2,
attribute3,
measure,
hrs_pm,
ret_user_id )
VALUES
( restab(i),
bbtab(i),
ovntab(i),
stattab(i),
tctab(i),
dwtab(i),
att1tab(i),
att2tab(i),
att3tab(i),
measuretab(i),
DECODE(p_application,'PA',hrspmtab(i),NULL),
FND_GLOBAL.user_id);
INSERT
INTO hxc_rdb_pre_status
(approval_status,
timecards,
ret_user_id)
SELECT approval_status,
count(timecard_id) Timecards,
FND_GLOBAL.user_id
FROM hxc_rdb_pre_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
GROUP by approval_status
UNION
SELECT 'Total',count(*),
FND_GLOBAL.user_id
FROM hxc_rdb_pre_timecards
WHERE ret_user_id = FND_GLOBAL.user_id;
INSERT
INTO hxc_rdb_pre_attributes
(approval_status,
attribute1,
attribute2,
attribute3,
measure,
ret_user_id)
SELECT DISTINCT approval_status,
attribute1,
attribute2,
attribute3,
SUM(measure) OVER (PARTITION BY approval_status,
attribute1||
attribute2||
attribute3) measure,
FND_GLOBAL.user_id
FROM hxc_rdb_pre_details
WHERE ret_user_id = FND_GLOBAL.user_id
UNION
ALL
SELECT DISTINCT 'Total' approval_status,
attribute1,
attribute2,
attribute3,
SUM(measure) OVER (PARTITION BY
attribute1||
attribute2||
attribute3) measure,
fnd_global.user_id
FROM hxc_rdb_pre_details
WHERE ret_user_id = FND_GLOBAL.user_id;
INSERT
INTO hxc_rdb_pre_hrs_pm
(approval_status,
hrs_pm,
timecards,
ret_user_id)
SELECT distinct approval_status,
hrs_pm,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY approval_status,
hrs_pm) ,
fnd_global.user_id
FROM hxc_rdb_pre_details
WHERE ret_user_id = FND_GLOBAL.user_id
UNION
ALL
SELECT DISTINCT 'Total' approval_status,
hrs_pm,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY hrs_pm) measure,
fnd_global.user_id
FROM hxc_rdb_pre_details
WHERE ret_user_id = FND_GLOBAL.user_id;
INSERT into hxc_rdb_pre_hrs_pm
(approval_status,
hrs_pm,
timecards,
ret_user_id)
SELECT DISTINCT approval_status,
supervisor_id,
COUNT(*) OVER (PARTITION BY approval_status,
supervisor_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_pre_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND supervisor_id IS NOT NULL
UNION
ALL
SELECT DISTINCT 'Total' approval_status,
supervisor_id,
COUNT(*) OVER (PARTITION BY supervisor_id),
FND_GLOBAL.user_id
FROM hxc_rdb_pre_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND supervisor_id IS NOT NULL;
PROCEDURE summarize_updated
IS
BEGIN
INSERT
INTO hxc_rdb_pre_updated
(approval_status,
last_updated_by,
timecards,
ret_user_id)
SELECT distinct approval_status,
last_updated_by,
COUNT(*) OVER (PARTITION BY approval_status,
last_updated_by) ,
FND_GLOBAL.user_id
FROM hxc_rdb_pre_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND last_updated_by IS NOT NULL
UNION
ALL
SELECT distinct 'Total' approval_status,
last_updated_by,
COUNT(*) OVER (PARTITION BY last_updated_by),
FND_GLOBAL.user_id
FROM hxc_rdb_pre_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND last_updated_by IS NOT NULL;
END summarize_updated;
IS SELECT ppf.full_name||'('||proj.name||')',
ppf.person_id,
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_pre_hrs_pm rdb,
PA_PROJECT_PARTIES PPP ,
PA_PROJECT_ROLE_TYPES_B PPRT,
per_people_f ppf, -- Bug 12605349
pa_projects_all proj
WHERE PPP.PROJECT_ID = rdb.hrs_pm
AND rdb.ret_user_id = FND_GLOBAL.user_id
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
AND PPRT.role_party_class = 'PERSON'
AND trunc(SYSDATE) BETWEEN ppf.effective_start_date -- Bug 14026118
AND ppf.effective_end_date
AND PPP.RESOURCE_SOURCE_ID = ppf.person_id
AND rdb.hrs_pm = proj.project_id
AND trunc(SYSDATE) BETWEEN trunc(PPP.start_date_active)
AND NVL(trunc(PPP.end_date_active),SYSDATE);
IS SELECT ppf.full_name,
ppf.person_id,
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_pre_hrs_pm rdb,
per_people_f ppf -- Bug 12605349
WHERE trunc(SYSDATE) BETWEEN ppf.effective_start_date -- Bug 14026118
AND ppf.effective_end_date
AND rdb.hrs_pm = ppf.person_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_pre_hrs_pm
SET hrs_pm_name = nametab(i),
resource_id = idtab(i)
WHERE rowid = chartorowid(rowtab(i));
UPDATE hxc_rdb_pre_hrs_pm
SET hrs_pm_name = nametab(i),
resource_id = idtab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
IS SELECT proj.name||' - '||
task.task_number||' - '||
rdb.attribute3,
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_pre_attributes rdb,
pa_projects_all proj,
pa_tasks_expend_v task
WHERE rdb.ret_user_id = FND_GLOBAL.user_id
AND rdb.attribute1 = proj.project_id
AND rdb.attribute2 = task.task_id;
IS SELECT pay.element_name,
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_pre_attributes rdb,
pay_element_types_f_tl pay
WHERE rdb.ret_user_id = FND_GLOBAL.user_id
AND pay.language = USERENV('LANG')
AND rdb.attribute1 = pay.element_type_id;
UPDATE hxc_rdb_pre_attributes
SET attribute_name = atttab(i)
WHERE rowid = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_pre_attributes
SET attribute_name = atttab(i)
WHERE rowid = CHARTOROWID(rowtab(i));
PROCEDURE translate_updated_by
IS
CURSOR get_updated
IS SELECT ppf.full_name,
ppf.person_id,
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_pre_updated rdb,
fnd_user fnd,
per_people_f ppf -- Bug 12605349
WHERE rdb.ret_user_id = FND_GLOBAL.user_id
AND rdb.last_updated_by = fnd.user_id
AND fnd.employee_id = ppf.person_id
AND trunc(SYSDATE) BETWEEN ppf.effective_start_date -- Bug 14026118
AND ppf.effective_end_date;
OPEN get_updated;
FETCH get_updated BULK COLLECT INTO nametab,
idtab,
rowtab LIMIT 500;
UPDATE hxc_rdb_pre_updated
SET last_updated_name = nametab(i),
resource_id = idtab(i)
WHERE rowid = CHARTOROWID(rowtab(i));
END translate_updated_by;
IS SELECT ppf.full_name,
DECODE(ppf.current_npw_flag,'Y',
ppf.npw_number,
ppf.employee_number),
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_pre_skipped rdb,
per_people_f ppf -- Bug 12605349
WHERE trunc(SYSDATE) BETWEEN ppf.effective_start_date -- Bug 14026118
AND ppf.effective_end_date
AND rdb.resource_id = ppf.person_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_pre_skipped
SET emp_name = nametab(i),
emp_no = notab(i)
WHERE rowid = chartorowid(rowtab(i));
INSERT INTO hxc_rdb_pre_timecards
( resource_id,
timecard_id,
approval_status,
start_time,
stop_time,
lu_bb_id,
lu_ovn,
last_update_date,
ret_user_id)
VALUES (restab(i),
tctab(i),
stattab(i),
starttab(i),
stoptab(i),
dettab(i),
ovntab(i),ludtab(i),
FND_GLOBAL.user_id);
l_pa_sql := REPLACE(l_pa_sql,'ret.last_update_date >= FND_DATE.canonical_to_date',
'ret.last_update_date < FND_DATE.canonical_to_date');
INSERT INTO hxc_rdb_pre_skipped
( resource_id,
timecard_id,
approval_status,
start_time,
stop_time,
ret_user_id)
VALUES (restab(i),
tctab(i),
stattab(i),
starttab(i),
stoptab(i),
FND_GLOBAL.user_id);
l_pay_sql := l_pay_sql||' AND ret.last_update_date >= fnd_date.canonical_to_date('''||
fnd_date.date_to_canonical(TO_DATE(p_changes_since,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
)||''') ';
l_pay_sql := l_pay_sql||' AND ret.last_update_date >= fnd_date.canonical_to_date('''||
fnd_date.date_to_canonical(SYSDATE-FND_PROFILE.VALUE('HXC_RETRIEVAL_CHANGES_DATE'))||''') ';
INSERT INTO hxc_rdb_pre_timecards
( resource_id,
timecard_id,
approval_status,
start_time,
stop_time,
lu_bb_id,
lu_ovn,
last_update_date,
ret_user_id)
VALUES (restab(i),
tctab(i),
stattab(i),
starttab(i),
stoptab(i),
dettab(i),
ovntab(i),ludtab(i),
FND_GLOBAL.user_id);
l_pay_sql := REPLACE(l_pay_sql,' AND ret.last_update_date >= fnd_date.canonical_to_date(',
' AND ret.last_update_date < fnd_date.canonical_to_date(');
INSERT INTO hxc_rdb_pre_skipped
( resource_id,
timecard_id,
approval_status,
start_time,
stop_time,
ret_user_id)
VALUES (restab(i),
tctab(i),
stattab(i),
starttab(i),
stoptab(i),
FND_GLOBAL.user_id);
update_last_touched;
update_supervisor;
update_emp_details;
summarize_updated;
translate_updated_by;
IS SELECT ROWIDTOCHAR(loc.rowid)
FROM hxc_rdb_pre_timecards rdb,
hxc_locks loc
WHERE rdb.resource_id = loc.resource_id
AND rdb.start_time = loc.start_time
AND TRUNC(rdb.stop_time) = TRUNC(loc.stop_time)
AND lock_date <= SYSDATE - (1/48);
DELETE FROM HXC_LOCKS
WHERE ROWID = CHARTOROWID(rowtab(i));
IS SELECT ROWIDTOCHAR(rowid)
FROM hxc_rdb_pre_timecards
WHERE ret_user_id = FND_GLOBAL.user_id;
IS SELECT ROWIDTOCHAR(rowid)
FROM hxc_rdb_pre_details
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_pre_timecards
WHERE ROWID = CHARTOROWID(rowtab(i));
DELETE FROM hxc_rdb_pre_details
WHERE ROWID = CHARTOROWID(rowtab(i));
DELETE FROM hxc_rdb_pre_status
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_pre_attributes
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_pre_hrs_pm
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_pre_updated
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_pre_skipped
WHERE ret_user_id = FND_GLOBAL.user_id;
last_update_date varchar2(50),
resource_id varchar2(20),
timecard_id varchar2(20));
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' ;
' || timecard_details_tab(l_index).last_update_date || '
' || timecard_details_tab(l_index).person_name || '
' || timecard_details_tab(l_index).person_number || '
'), '
' || timecard_details_tab(l_index).timecard_id || '
' || timecard_details_tab(l_index).start_time || '
' || timecard_details_tab(l_index).stop_time || '
' || timecard_details_tab(l_index).status || '
' || timecard_details_tab(l_index).last_update_date || '
' || timecard_details_tab(l_index).person_name || '
' || timecard_details_tab(l_index).person_number || '
');
IS SELECT proj.name||' - '||
task.task_number||' - '||
rdb.attribute3,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_pre_tc_details rdb,
pa_projects_all proj,
pa_tasks_expend_v task
WHERE rdb.ret_user_id = FND_GLOBAL.user_id
AND rdb.attribute1 = proj.project_id
AND rdb.attribute2 = task.task_id;
IS SELECT pay.element_name,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_pre_tc_details rdb,
pay_element_types_f_tl pay
WHERE rdb.ret_user_id = FND_GLOBAL.user_id
AND pay.language = USERENV('LANG')
AND rdb.attribute1 = pay.element_type_id;
DELETE FROM hxc_rdb_pre_tc_details
WHERE ret_user_id = FND_GLOBAL.user_id;
INSERT INTO hxc_rdb_pre_tc_details
(time_building_block_id,
date_worked,
measure,
attribute1,
attribute2,
attribute3,
start_time,
stop_time,
timecard_id,
ret_user_id)
SELECT det.time_building_block_id,
det.date_worked,
det.measure,
attribute1,
attribute2,
attribute3,
TO_CHAR(detail.start_time,'HH24:MI'), -- Bug 9656636
TO_CHAR(detail.stop_time,'HH24:MI'),
timecard_id,
FND_GLOBAL.user_id
FROM hxc_rdb_pre_details det,
hxc_time_building_blocks detail
WHERE timecard_id = p_timecard_id
AND det.time_building_block_id = detail.time_building_block_id
AND det.ovn = detail.object_version_number
AND det.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_pre_tc_details
SET attribute_name = atttab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_pre_tc_details
SET attribute_name = atttab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
DELETE FROM hxc_locks
WHERE resource_id = TO_NUMBER(p_resource_id)
AND TRUNC(start_time) = TO_DATE(p_start_time,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
AND TRUNC(stop_time) = TO_DATE(p_stop_time,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
SELECT 1
INTO l_exists
FROM hxc_rdb_logins
WHERE user_id = FND_GLOBAL.user_id
AND login_id = FND_GLOBAL.login_id
AND status = 'VALID';
IS SELECT ROWIDTOCHAR(rdb.rowid),
NVL(fnd.end_time,hr_general.end_of_time)
FROM hxc_rdb_logins rdb,
fnd_logins fnd
WHERE rdb.login_id = fnd.login_id
AND rdb.user_id = fnd_global.user_id
AND rdb.login_id <> fnd_global.login_id;
DELETE FROM hxc_rdb_logins
WHERE user_id = FND_GLOBAL.user_id
AND login_id <> FND_GLOBAL.login_id
AND last_action_date < SYSDATE - (1/48);
SELECT 1
INTO l_exists
FROM hxc_rdb_logins
WHERE user_id = fnd_global.user_id
AND login_id = fnd_global.login_id
AND status = 'INVALID';
INSERT INTO hxc_rdb_logins
(user_id,
login_id,
last_action_date,
status,
notified)
VALUES
(FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
SYSDATE,
'VALID',
'N');
UPDATE hxc_rdb_logins
SET last_action_date = SYSDATE
WHERE user_id = FND_GLOBAL.user_id
AND login_id = FND_GLOBAL.login_id;
DELETE FROM hxc_rdb_logins
WHERE ROWID = CHARTOROWID(rowtab(i))
AND edtab(i) <> hr_general.end_of_time;
UPDATE hxc_rdb_logins
SET status = 'INVALID'
WHERE ROWID = CHARTOROWID(rowtab(i))
AND edtab(i) = hr_general.end_of_time
RETURNING rowid BULK COLLECT INTO l_tab;
SELECT ROWIDTOCHAR(rdb.rowid)
INTO l_rowid
FROM hxc_rdb_logins rdb
WHERE login_id = FND_GLOBAL.login_id
AND user_id = FND_GLOBAL.user_id
AND notified = 'N';
UPDATE hxc_rdb_logins
SET notified = 'Y'
WHERE rowid = chartorowid(l_rowid);