[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
' SELECT DISTINCT SUM.time_building_block_id,
SUM.object_version_number,
SUM.resource_id,
batch_id,
old_batch_id,
retro_batch_id,
SUM.start_time,
TRUNC(SUM.stop_time)
FROM hxc_ret_pay_latest_details ret,
hxc_time_building_blocks SUM
WHERE ret.start_time BETWEEN :p_start_date
AND :p_end_date
AND ret.timecard_id = SUM.time_building_block_id
AND business_group_id = FND_PROFILE.VALUE(''PER_BUSINESS_GROUP_ID'')
PERSONCRITERIA
PAYROLLCRITERIA
BATCHCRITERIA
ORGCRITERIA
';
' SELECT DISTINCT SUM.time_building_block_id,
SUM.object_version_number,
SUM.resource_id,
exp_group,
old_exp_group,
retro_exp_group,
SUM.start_time,
TRUNC(SUM.stop_time)
FROM hxc_ret_pa_latest_details ret,
hxc_time_building_blocks SUM
WHERE ret.start_time BETWEEN :p_start_date
AND :p_end_date
AND ret.timecard_id = SUM.time_building_block_id
AND ret.org_id = NVL(Pa_Moac_Utils.Get_Current_Org_Id,FND_PROFILE.VALUE(''ORG_ID''))
PERSONCRITERIA
BATCHCRITERIA
ORGCRITERIA
';
'AND ( EXISTS (SELECT 1
FROM pay_batch_headers pbh
WHERE pbh.batch_id = ret.batch_id
AND pbh.batch_reference = ''BATCHREF'' )
OR EXISTS (SELECT 1
FROM pay_batch_headers pbh
WHERE pbh.batch_id = ret.retro_batch_id
AND pbh.batch_reference = ''BATCHREF'' )
)
';
'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
AND paf.payroll_id = PAYROLL )'
;
'AND EXISTS ( SELECT 1
FROM per_assignments_f paf /*Bug 9656063*/
WHERE paf.person_id = ret.resource_id
AND ret.start_time BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.organization_id = ORGANIZATION )'
;
'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 find_and_update_old
IS
-- Bug 9701527
-- Added the NOT EXISTS to avoid same batch coming
-- twice because batch_id and old_batch_id are same.
CURSOR find_old_tcs
IS SELECT timecard_id,
approval_status,
resource_id,
batch_id,
old_batch_id,
retro_batch_id,
start_time,
stop_time,
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_post_timecards rdb
WHERE ret_user_id = FND_GLOBAL.user_id
AND old_batch_id IS NOT NULL
AND retro_batch_id IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM hxc_rdb_post_timecards rdb2
WHERE rdb2.ret_user_id = FND_GLOBAL.user_id
AND rdb2.batch_id = rdb.old_batch_id );
IS SELECT MAX(ROWIDTOCHAR(rdb.rowid)),
timecard_id,
NVL(batch_id,'0'),
NVL(retro_batch_id,'0')
FROM hxc_rdb_post_timecards rdb
WHERE ret_user_id = FND_GLOBAL.user_id
GROUP BY timecard_id,batch_id,retro_batch_id;
UPDATE hxc_rdb_post_timecards
SET old_batch_id = NULL
WHERE ROWID = CHARTOROWID(rowtab(i));
INSERT INTO hxc_rdb_post_timecards
(timecard_id,
approval_status,
resource_id,
batch_id,
start_time,
stop_time,
ret_user_id)
VALUES
(tctab(i),
statustab(i),
restab(i),
oldtab(i),
starttab(i),
stoptab(i),
FND_GLOBAL.user_id);
UPDATE hxc_rdb_post_timecards
SET old_batch_id = NULL
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND timecard_id = tctab(i)
AND NVL(batch_id,'0') = batchtab(i)
AND NVL(retro_batch_id,'0') = rettab(i)
AND ROWID <> CHARTOROWID(rowtab(i));
END find_and_update_old;
PROCEDURE delete_duplicate_tcs
IS
CURSOR get_rank
IS SELECT ROWIDTOCHAR(ROWID),
RANK() OVER ( PARTITION BY timecard_id
ORDER BY TO_NUMBER(approval_status) DESC ) rank
FROM hxc_rdb_post_timecards rdb
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND ROWID = CHARTOROWID(tctab(i))
AND ranktab(i) <> 1 ;
END delete_duplicate_tcs;
PROCEDURE update_supervisor
IS
CURSOR get_supervisor
IS SELECT asg.supervisor_id,
asg.payroll_id,
asg.organization_id,
asg.job_id,
ROWIDTOCHAR(tc.ROWID)
FROM hxc_rdb_post_timecards tc,
per_assignments_f asg -- Bug 12605349
WHERE tc.ret_user_id = FND_GLOBAL.user_id
AND tc.resource_id = asg.person_id
AND tc.start_time BETWEEN asg.effective_start_date
AND asg.effective_end_date;
UPDATE hxc_rdb_post_timecards
SET supervisor_id = suptab(i),
payroll_id = paytab(i),
org_job_id = DECODE(p_application,'PAY', orgtab(i),
'PA', jobtab(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_post_timecards rdb,
per_people_f ppf -- Bug 12605349
WHERE SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND rdb.resource_id = ppf.person_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_post_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,
ret.timecard_id,
ret.start_time date_worked,
ret.attribute1,
ret.attribute2,
ret.attribute3,
ret.measure,
ret.old_attribute1,
ret.old_attribute2,
ret.old_attribute3,
ret.old_measure,
ret.attribute1,
ret.pbl_id,
ret.retro_pbl_id,
ret.old_pbl_id,
ret.batch_id,
ret.retro_batch_id,
ret.request_id,
ret.old_request_id,
ret.old_batch_id
FROM hxc_ret_pay_latest_details ret,
hxc_rdb_post_timecards tc
WHERE ret.timecard_id = tc.timecard_id
AND NVL(tc.batch_id,''0'') = NVL(ret.batch_id,''0'')
AND NVL(tc.retro_batch_id,''0'') = NVL(ret.retro_batch_id,''0'')
AND tc.ret_user_id = USERID '
;
'SELECT ret.resource_id,
ret.time_building_block_id,
ret.object_version_number ovn,
ret.timecard_id,
ret.start_time date_worked,
ret.attribute1,
ret.attribute2,
ret.attribute3,
ret.measure,
ret.old_attribute1,
ret.old_attribute2,
ret.old_attribute3,
ret.old_measure,
ret.attribute1,
ret.pei_id,
ret.retro_pei_id,
ret.old_pei_id,
ret.exp_group,
ret.retro_exp_group,
ret.request_id,
ret.old_request_id,
ret.old_exp_group
FROM hxc_ret_pa_latest_details ret,
hxc_rdb_post_timecards tc
WHERE ret.timecard_id = tc.timecard_id
AND tc.ret_user_id = USERID
AND NVL(tc.batch_id,''0'') = NVL(ret.exp_group,''0'')
AND NVL(tc.retro_batch_id,''0'') = NVL(ret.retro_exp_group,''0'')
';
INSERT INTO hxc_rdb_post_details
(resource_id,
time_building_block_id,
ovn,
timecard_id,
date_worked,
attribute1,
attribute2,
attribute3,
measure,
old_attribute1,
old_attribute2,
old_attribute3,
old_measure,
hrs_pm,
rec_line_id,
rec_retro_line_id,
adj_rec_line_id,
batch_id,
retro_batch_id,
request_id,
old_request_id,
old_batch_id,
ret_user_id )
VALUES
( restab(i),
bbtab(i),
ovntab(i),
tctab(i),
dwtab(i),
att1tab(i),
att2tab(i),
att3tab(i),
measuretab(i),
oatt1tab(i),
oatt2tab(i),
oatt3tab(i),
omeasuretab(i),
DECODE(p_application,'PA',hrspmtab(i),NULL),
recline(i),
retroline(i),
orecline(i),
batchid(i),
rbatchid(i),
reqid(i),
rreqid(i),
obatchid(i),
FND_GLOBAL.user_id);
'SELECT ret.resource_id,
ret.time_building_block_id,
ret.object_version_number ovn,
ret.timecard_id,
ret.start_time date_worked,
ret.old_attribute1,
ret.old_attribute2,
ret.old_attribute3,
ret.old_measure,
NULL,
NULL,
NULL,
NULL,
ret.old_attribute1,
ret.old_pbl_id,
NULL,
NULL,
ret.old_batch_id,
NULL,
ret.old_request_id,
NULL,
NULL
FROM hxc_ret_pay_latest_details ret,
hxc_rdb_post_timecards tc
WHERE ret.timecard_id = tc.timecard_id
AND tc.batch_id = ret.old_batch_id
AND ret.old_pbl_id <> NVL(ret.pbl_id,0)
AND tc.ret_user_id = USERID '
;
'SELECT ret.resource_id,
ret.time_building_block_id,
ret.object_version_number ovn,
ret.timecard_id,
ret.start_time date_worked,
ret.old_attribute1,
ret.old_attribute2,
ret.old_attribute3,
ret.old_measure,
NULL,
NULL,
NULL,
NULL,
ret.old_attribute1,
ret.old_pei_id,
NULL,
NULL,
ret.old_exp_group,
NULL,
ret.old_request_id,
NULL,
NULL
FROM hxc_ret_pa_latest_details ret,
hxc_rdb_post_timecards tc
WHERE ret.timecard_id = tc.timecard_id
AND tc.ret_user_id = USERID
AND tc.batch_id = ret.old_exp_group
AND ret.old_pei_id <> NVL(ret.pei_id,0)
';
INSERT INTO hxc_rdb_post_details
(resource_id,
time_building_block_id,
ovn,
timecard_id,
date_worked,
attribute1,
attribute2,
attribute3,
measure,
old_attribute1,
old_attribute2,
old_attribute3,
old_measure,
hrs_pm,
rec_line_id,
rec_retro_line_id,
adj_rec_line_id,
batch_id,
retro_batch_id,
request_id,
old_request_id,
old_batch_id,
ret_user_id )
VALUES
( restab(i),
bbtab(i),
ovntab(i),
tctab(i),
dwtab(i),
att1tab(i),
att2tab(i),
att3tab(i),
measuretab(i),
oatt1tab(i),
oatt2tab(i),
oatt3tab(i),
omeasuretab(i),
DECODE(p_application,'PA',hrspmtab(i),NULL),
recline(i),
retroline(i),
orecline(i),
batchid(i),
rbatchid(i),
reqid(i),
rreqid(i),
obatchid(i),
FND_GLOBAL.user_id);
PROCEDURE update_statuses
IS
-- To pick up the guys which are active now.
CURSOR get_summary
IS SELECT sum.approval_status,
rdb.timecard_id
FROM hxc_rdb_post_timecards rdb,
hxc_timecard_summary sum
WHERE rdb.ret_user_id = FND_GLOBAL.user_id
AND rdb.timecard_id = sum.timecard_id;
IS SELECT ROWIDTOCHAR(rowid)
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND approval_status NOT IN ( SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'HXC_APPROVAL_STATUS'
AND language = USERENV('LANG') );
IS SELECT rdb.timecard_id,
sum.timecard_id,
sum.approval_status,
ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_post_timecards rdb,
hxc_timecard_summary sum
WHERE rdb.ret_user_id = FND_GLOBAL.user_id
AND rdb.approval_status = 'RDBDELETED'
AND rdb.resource_id = sum.resource_id
AND rdb.start_time = sum.start_time
AND rdb.stop_time = TRUNC(sum.stop_time)
AND rdb.timecard_id <> sum.timecard_id ;
UPDATE hxc_rdb_post_timecards
SET approval_status = stattab(i)
WHERE ret_user_id = FND_GLOBAL.user_id
AND timecard_id = tctab(i);
UPDATE hxc_rdb_post_timecards
SET approval_status = 'RDBDELETED'
WHERE rowid = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_timecards
SET approval_status = statustab(i),
timecard_id = sumtab(i)
WHERE ROWID = CHARTOROWID(rowidtab(i));
UPDATE hxc_rdb_post_details
SET timecard_id = sumtab(i)
WHERE timecard_id = rdbtab(i);
END update_statuses;
PROCEDURE update_retro_batches
IS
CURSOR pick_timecards
IS SELECT ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_post_timecards rdb
WHERE ret_user_id = FND_GLOBAL.user_id
AND retro_batch_id IS NOT NULL;
IS SELECT ROWIDTOCHAR(rdb.rowid)
FROM hxc_rdb_post_details rdb
WHERE ret_user_id = FND_GLOBAL.user_id
AND retro_batch_id IS NOT NULL;
UPDATE hxc_rdb_post_timecards
SET retro_batch_id = retro_batch_id||'(Retro)'
WHERE rowid = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_details
SET retro_batch_id = retro_batch_id||'(Retro)'
WHERE rowid = CHARTOROWID(rowtab(i));
END update_retro_batches;
PROCEDURE update_partially_retrieved(p_application IN VARCHAR2)
IS
CURSOR get_partially_retrieved_pay
IS SELECT ROWIDTOCHAR(tc.ROWID)
FROM hxc_rdb_post_timecards tc,
hxc_pay_latest_details pay
WHERE tc.ret_user_id = FND_GLOBAL.user_id
AND tc.timecard_id = pay.timecard_id;
IS SELECT ROWIDTOCHAR(tc.ROWID)
FROM hxc_rdb_post_timecards tc,
hxc_pa_latest_details pay
WHERE tc.ret_user_id = FND_GLOBAL.user_id
AND tc.timecard_id = pay.timecard_id;
UPDATE hxc_rdb_post_timecards
SET partially_retrieved = 'Y'
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_timecards
SET partially_retrieved = 'Y'
WHERE ROWID = CHARTOROWID(rowtab(i));
END update_partially_retrieved;
INSERT
INTO hxc_rdb_post_batches
(batch_id,
timecards,
retro_flag,
ret_user_id)
SELECT DISTINCT batch_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id) Timecards,
'N',
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND batch_id IS NOT NULL
UNION
SELECT DISTINCT retro_batch_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id) Timecards,
'Y',
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND retro_batch_id IS NOT NULL
AND NVL(retro_batch_id,'0') <> NVL(batch_id,'0')
AND NVL(retro_batch_id,'0') <> NVL(old_batch_id,'0') ;
INSERT
INTO hxc_rdb_post_attributes
(batch_id,
attribute1,
attribute2,
attribute3,
measure,
negative_flag,
ret_user_id)
SELECT DISTINCT batch_id,
attribute1,
attribute2,
attribute3,
SUM(measure) OVER (PARTITION BY batch_id,
attribute1,
attribute2,
attribute3),
1,
FND_GLOBAL.user_id
FROM hxc_rdb_post_details
WHERE rec_line_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id
UNION
SELECT DISTINCT '0' batch_id,
attribute1,
attribute2,
attribute3,
SUM(measure) OVER (PARTITION BY attribute1,
attribute2,
attribute3),
1,
FND_GLOBAL.user_id
FROM hxc_rdb_post_details
WHERE rec_line_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id;
INSERT
INTO hxc_rdb_post_attributes
(batch_id,
attribute1,
attribute2,
attribute3,
measure,
negative_flag,
ret_user_id)
SELECT DISTINCT retro_batch_id,
old_attribute1,
old_attribute2,
old_attribute3,
SUM(-1*old_measure) OVER (PARTITION BY retro_batch_id,
old_attribute1,
old_attribute2,
old_attribute3),
-1,
FND_GLOBAL.user_id
FROM hxc_rdb_post_details
WHERE rec_retro_line_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id
UNION
SELECT DISTINCT '0' retro_batch_id,
old_attribute1,
old_attribute2,
old_attribute3,
SUM(-1*old_measure) OVER (PARTITION BY old_attribute1,
old_attribute2,
old_attribute3),
-1,
FND_GLOBAL.user_id
FROM hxc_rdb_post_details
WHERE rec_retro_line_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id;
INSERT INTO HXC_RDB_POST_ATTRIBUTES
( attribute1,
attribute2,
attribute3,
measure,
batch_id,
total,
negative_flag,
ret_user_id
)
SELECT attribute1,
attribute2,
attribute3,
SUM(measure),
batch_id,
' (Total) 'total,
1,
FND_GLOBAL.user_id
FROM hxc_rdb_post_attributes
WHERE ret_user_id = FND_GLOBAL.user_id
GROUP BY batch_id,
attribute1,
attribute2,
attribute3 ;
INSERT INTO hxc_rdb_post_hrs_pm
(batch_id,
hrs_pm,
timecards,
ret_user_id)
SELECT DISTINCT batch_id,
supervisor_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
supervisor_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND supervisor_id IS NOT NULL
AND batch_id IS NOT NULL
UNION
SELECT DISTINCT retro_batch_id,
supervisor_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
supervisor_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND supervisor_id IS NOT NULL
AND retro_batch_id IS NOT NULL
UNION
SELECT '0' batch_id,
supervisor_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY supervisor_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND supervisor_id IS NOT NULL;
INSERT
INTO hxc_rdb_post_hrs_pm
(batch_id,
hrs_pm,
timecards,
ret_user_id)
SELECT DISTINCT batch_id,
hrs_pm,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
hrs_pm) ,
fnd_global.user_id
FROM hxc_rdb_post_details
WHERE ret_user_id = FND_GLOBAL.user_id
AND batch_id IS NOT NULL
UNION
SELECT DISTINCT retro_batch_id,
hrs_pm,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
hrs_pm) ,
fnd_global.user_id
FROM hxc_rdb_post_details
WHERE ret_user_id = FND_GLOBAL.user_id
AND retro_batch_id IS NOT NULL
UNION
SELECT DISTINCT '0' batch_id,
hrs_pm,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY hrs_pm) measure,
fnd_global.user_id
FROM hxc_rdb_post_details
WHERE ret_user_id = FND_GLOBAL.user_id;
INSERT INTO hxc_rdb_post_payroll_exp_type
(batch_id,
payroll_exp_id,
timecards,
ret_user_id)
SELECT DISTINCT batch_id,
payroll_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
payroll_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND payroll_id IS NOT NULL
AND batch_id IS NOT NULL
UNION
SELECT DISTINCT retro_batch_id,
payroll_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
payroll_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND payroll_id IS NOT NULL
AND retro_batch_id IS NOT NULL
UNION
SELECT '0' batch_id,
payroll_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY payroll_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND payroll_id IS NOT NULL;
INSERT INTO hxc_rdb_post_payroll_exp_type
(batch_id,
payroll_exp_id,
timecards,
ret_user_id)
SELECT DISTINCT batch_id,
attribute3,
SUM(measure) OVER (PARTITION BY batch_id,
attribute3) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_attributes
WHERE ret_user_id = FND_GLOBAL.user_id
AND total IS NULL ;
INSERT INTO hxc_rdb_post_partial_timecards
(batch_id,
start_time,
stop_time,
timecards,
ret_user_id)
SELECT DISTINCT batch_id,
start_time,
stop_time,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
start_time,
stop_time),
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE partially_retrieved = 'Y'
AND batch_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id
UNION
SELECT DISTINCT retro_batch_id,
start_time,
stop_time,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
start_time,
stop_time),
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE partially_retrieved = 'Y'
AND retro_batch_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id
UNION
SELECT DISTINCT '0' batch_id,
start_time,
stop_time,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY start_time,
stop_time),
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE partially_retrieved = 'Y'
AND ret_user_id = FND_GLOBAL.user_id ;
INSERT INTO hxc_rdb_post_dist_timecards
(batch_id,
start_time,
stop_time,
timecards,
ret_user_id)
SELECT DISTINCT batch_id,
start_time,
stop_time,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
start_time,
stop_time),
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE batch_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id
UNION
SELECT DISTINCT retro_batch_id,
start_time,
stop_time,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
start_time,
stop_time),
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE retro_batch_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id
UNION
SELECT DISTINCT '0' batch_id,
start_time,
stop_time,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY start_time,
stop_time),
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id ;
INSERT INTO hxc_rdb_post_org_job
(batch_id,
org_job_id,
timecards,
ret_user_id)
SELECT DISTINCT batch_id,
org_job_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
org_job_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND org_job_id IS NOT NULL
AND batch_id IS NOT NULL
UNION
SELECT DISTINCT retro_batch_id,
org_job_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
org_job_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND org_job_id IS NOT NULL
AND retro_batch_id IS NOT NULL
UNION
SELECT '0' batch_id,
org_job_id,
COUNT(DISTINCT timecard_id) OVER (PARTITION BY org_job_id) ,
FND_GLOBAL.user_id
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id
AND org_job_id IS NOT NULL;
IS SELECT ppf.full_name||'('||proj.name||')',
ppf.person_id,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_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 SYSDATE BETWEEN ppf.effective_start_date
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_post_hrs_pm rdb,
per_people_f ppf -- Bug 12605349
WHERE SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND rdb.hrs_pm = ppf.person_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_post_hrs_pm
SET hrs_pm_name = nametab(i),
resource_id = idtab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_hrs_pm
SET hrs_pm_name = nametab(i),
resource_id = idtab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
IS SELECT pbh.batch_name||DECODE(retro_flag,'Y','(Retro)'),
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_batches rdb,
pay_batch_headers pbh
WHERE REPLACE(rdb.batch_id,'(Retro)') = pbh.batch_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_post_batches
SET batch_name = nametab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_batches
SET batch_name = batch_id
WHERE ret_user_id = FND_GLOBAL.user_id;
IS SELECT proj.name||' - '||
task.task_number||' - '||
rdb.attribute3,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_attributes rdb,
pa_projects_all proj,
pa_tasks_expend_v task /*Bug 16391367*/
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_post_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_post_attributes
SET attribute_name = atttab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_attributes
SET attribute_name = atttab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
IS SELECT ppf.full_name,
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
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 SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND PPP.RESOURCE_SOURCE_ID = ppf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPP.start_date_active)
AND NVL(TRUNC(PPP.end_date_active),SYSDATE);
IS SELECT ppf.payroll_name,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_payroll_exp_type rdb,
pay_payrolls_f ppf
WHERE rdb.payroll_exp_id = ppf.payroll_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_post_payroll_exp_type
SET payroll_exp_name = nametab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_payroll_exp_type
SET payroll_exp_name = payroll_exp_id
WHERE ret_user_id = FND_GLOBAL.user_id;
IS SELECT ppf.full_name,
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
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 SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND PPP.RESOURCE_SOURCE_ID = ppf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPP.start_date_active)
AND NVL(TRUNC(PPP.end_date_active),SYSDATE);
IS SELECT org.name,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_org_job rdb,
hr_organization_units org -- Bug 12605349
WHERE rdb.org_job_id = org.organization_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
IS SELECT job.name,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_org_job rdb,
per_jobs job
WHERE rdb.org_job_id = job.job_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_post_org_job
SET org_job_name = nametab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_org_job
SET org_job_name = nametab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
INSERT INTO hxc_rdb_post_timecards
(timecard_id,
approval_status,
resource_id,
batch_id,
old_batch_id,
retro_batch_id,
start_time,
stop_time,
ret_user_id)
VALUES
( tctab(i),
statustab(i),
restab(i),
batchtab(i),
oldtab(i),
rettab(i),
starttab(i),
stoptab(i),
FND_GLOBAL.user_id);
INSERT INTO hxc_rdb_post_timecards
(timecard_id,
approval_status,
resource_id,
batch_id,
old_batch_id,
retro_batch_id,
start_time,
stop_time,
ret_user_id)
VALUES
( tctab(i),
statustab(i),
restab(i),
batchtab(i),
oldtab(i),
rettab(i),
starttab(i),
stoptab(i),
FND_GLOBAL.user_id);
find_and_update_old;
delete_duplicate_tcs;
update_supervisor;
update_emp_details;
update_partially_retrieved(p_application);
update_statuses;
update_retro_batches;
IS SELECT ROWIDTOCHAR(ROWID)
FROM hxc_rdb_post_timecards
WHERE ret_user_id = FND_GLOBAL.user_id;
IS SELECT ROWIDTOCHAR(ROWID)
FROM hxc_rdb_post_details
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_timecards
WHERE ROWID = CHARTOROWID(rowtab(i));
DELETE FROM hxc_rdb_post_details
WHERE ROWID = CHARTOROWID(rowtab(i));
DELETE FROM hxc_rdb_post_batches
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_attributes
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_hrs_pm
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_payroll_exp_type
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_partial_timecards
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_dist_timecards
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_org_job
WHERE ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_tc_details
WHERE ret_user_id = FND_GLOBAL.user_id;
IS SELECT proj.name||' - '||
task.task_number||' - '||
rdb.attribute3,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_tc_details rdb,
pa_projects_all proj,
pa_tasks_expend_v task /*Bug 16391367*/
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_post_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;
IS SELECT pbh.batch_name,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_tc_details rdb,
pay_batch_headers pbh
WHERE REPLACE(rdb.batch_id,'(Retro)') = pbh.batch_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
IS SELECT pbh.batch_name,
ROWIDTOCHAR(rdb.ROWID)
FROM hxc_rdb_post_tc_details rdb,
pay_batch_headers pbh
WHERE rdb.old_batch_id = pbh.batch_id
AND rdb.ret_user_id = FND_GLOBAL.user_id;
DELETE FROM hxc_rdb_post_tc_details
WHERE ret_user_id = FND_GLOBAL.user_id;
INSERT INTO hxc_rdb_post_tc_details
(time_building_block_id,
date_worked,
measure,
attribute1,
attribute2,
attribute3,
rec_line_id,
batch_id,
request_id,
adj_rec_line_id,
old_batch_id,
old_request_id,
timecard_id,
ret_user_id)
SELECT DISTINCT time_building_block_id,
date_worked,
measure,
attribute1,
attribute2,
attribute3,
rec_line_id,
batch_id,
request_id,
NULL,
NULL,
NULL,
timecard_id,
FND_GLOBAL.user_id
FROM hxc_rdb_post_details det
WHERE timecard_id = p_timecard_id
AND rec_line_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id
UNION
ALL
SELECT DISTINCT time_building_block_id,
date_worked,
-1*old_measure,
old_attribute1,
old_attribute2,
old_attribute3,
rec_retro_line_id,
retro_batch_id,
request_id,
adj_rec_line_id,
old_batch_id,
old_request_id,
timecard_id,
FND_GLOBAL.user_id
FROM hxc_rdb_post_details det
WHERE timecard_id = p_timecard_id
AND rec_retro_line_id IS NOT NULL
AND ret_user_id = FND_GLOBAL.user_id ;
UPDATE hxc_rdb_post_tc_details
SET attribute_name = atttab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_tc_details
SET batch_name = batch_id,
old_line_details = RTRIM(adj_rec_line_id||' - '||old_batch_id||' - '||old_request_id,' - ')
WHERE timecard_id = p_timecard_id
AND ret_user_id = FND_GLOBAL.user_id;
UPDATE hxc_rdb_post_tc_details
SET attribute_name = atttab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_tc_details
SET batch_name = nametab(i)
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_rdb_post_tc_details
SET old_line_details = adj_rec_line_id||' - '||nametab(i)||' - '||old_request_id
WHERE ROWID = CHARTOROWID(rowtab(i));
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' ;