[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_deleted BOOLEAN := TRUE;
select outer.effort_report_id, outer.person_id, ppf.full_name,
-- people.full_name approver_name,
pert.begin_date, pert.End_date, pbg.name
from psp_effort_reports outer,
psp_effort_report_templates pert ,
per_all_people_f ppf,
-- wf_notifications wfis,
per_business_groups pbg
-- ,
-- per_assignments_f assignment,
-- per_people_f people
where outer.status_code in ('N', 'A')
and outer.template_id = pert.template_id
and pert.report_type='N'
and pbg.BUSINESS_GROUP_ID = pert.BUSINESS_GROUP_ID
/* Commented for bug 5048771
and wfis.notification_id = (select max(wfas.notification_id)
from wf_item_activity_statuses wfas
where wfas.item_type='PSPEFFWF'
and wfas.item_key= outer.effort_report_id || outer.VERSION_NUM)
*/
and outer.person_id =ppf.person_id
and ppf.effective_start_date = ( SELECT MAX (ppf2.effective_start_date)
FROM per_all_people_f ppf2
WHERE ppf.person_id = ppf2.person_id
AND ppf2.effective_start_date <=pert.end_date
AND ppf2.effective_end_date >= pert.begin_date)
-- and pert.begin_date between ppf.effective_start_date and ppf.effective_End_date
-- AND assignment.person_id = ppf.person_id
-- AND assignment.supervisor_id = people.person_id (+)
-- AND assignment.assignment_type ='E'
-- AND trunc(SYSDATE) BETWEEN people.effective_start_date (+) AND people.effective_end_date(+)
-- AND trunc(SYSDATE) BETWEEN assignment.effective_start_date AND assignment.effective_end_date
-- AND assignment.primary_flag = 'Y'
;
SELECT 1 from pay_element_sets
where ELEMENT_SET_NAME = p_element_set_name;
SELECT DISTINCT business_group_id
from psp_effort_report_elements;
select distinct element_type_id
from psp_effort_report_elements
where use_in_effort_report='Y'
and business_group_id =l_business_group_id;
select effort_report_id, outer.person_id, ppf.full_name ,
-- people.full_name approver_name,
pert.begin_date, pert.end_date , pal.adjustment_batch_name, pbg.name
from psp_effort_reports outer,
psp_effort_report_templates pert,
per_all_people_f ppf,
----wf_notifications wfis,
psp_adjustment_control_table pal,
per_business_groups pbg
-- ,
-- per_assignments_f assignment,
-- per_people_f people
where outer.person_id = pal.person_id
and pert.end_date >= pal.distribution_start_date
and pert.begin_date <= pal.distribution_end_date
and pal.approver_id is null
and outer.status_code in ('S')
and outer.person_id =ppf.person_id
and outer.template_id = pert.template_id
-- and pert.begin_date between ppf.effective_Start_date and ppf.effective_end_date
and ppf.effective_start_date = ( SELECT MAX (ppf2.effective_start_date)
FROM per_all_people_f ppf2
WHERE ppf.person_id = ppf2.person_id
AND ppf2.effective_start_date <=pert.end_date
AND ppf2.effective_end_date >= pert.begin_date)
and pbg.BUSINESS_GROUP_ID = pert.BUSINESS_GROUP_ID
---and wfis.notification_id = ( --- removed this condn and added void check for 4665930
---- SELECT ias.notification_id
and pal.void is null
/* Commented for bug 5048771
and exists (select 1
FROM wf_lookups l_at,
wf_lookups l_as,
wf_activities_vl a,
wf_process_activities pa,
wf_item_types_vl it,
wf_items i,
wf_item_activity_statuses ias
WHERE ias.item_type = 'PSPADJWF'
AND ias.item_key = pal.adjustment_batch_name
AND i.item_type = 'PSPADJWF'
AND i.item_key = pal.adjustment_batch_name
AND i.begin_date between a.begin_date AND nvl(a.end_date, i.begin_date)
AND i.item_type = it.name
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name
AND pa.activity_item_type = a.item_type
AND l_at.lookup_type = 'WFENG_ACTIVITY_TYPE'
AND l_at.lookup_code = a.type
AND l_as.lookup_type = 'WFENG_STATUS'
AND l_as.lookup_code = ias.activity_status
AND a.name = 'NOT_APPROVAL_REQUIRED')
*/
-- AND assignment.person_id = ppf.person_id
-- AND assignment.supervisor_id = people.person_id (+)
-- AND assignment.assignment_type ='E'
-- AND trunc(SYSDATE) BETWEEN people.effective_start_date (+) AND people.effective_end_date(+)
-- AND trunc(SYSDATE) BETWEEN assignment.effective_start_date AND assignment.effective_end_date
-- AND assignment.primary_flag = 'Y'
;
select effort_report_id, outer.person_id, ppf.full_name ,
-- people.full_name approver_name,
pert.begin_date, pert.end_date, pal.adjustment_batch_name, pbg.name
from psp_effort_reports outer,
psp_effort_report_templates pert,
per_all_people_f ppf,
----wf_notifications wfis,
psp_adjustment_control_table pal,
psp_payroll_controls ppc,
per_business_groups pbg
-- ,
-- per_assignments_f assignment,
-- per_people_f people
where outer.person_id = pal.person_id
and pert.end_date >= pal.distribution_start_date
and pert.begin_date <= pal.distribution_end_date
and pal.ADJUSTMENT_BATCH_NAME = ppc.BATCH_NAME
and ppc.SOURCE_TYPE = 'A'
and ppc.STATUS_CODE = 'N'
and outer.status_code = 'S'
and outer.person_id =ppf.person_id
and outer.template_id = pert.template_id
-- and pert.begin_date between ppf.effective_Start_date and ppf.effective_end_date
and ppf.effective_start_date = ( SELECT MAX (ppf2.effective_start_date)
FROM per_all_people_f ppf2
WHERE ppf.person_id = ppf2.person_id
AND ppf2.effective_start_date <=pert.end_date
AND ppf2.effective_end_date >= pert.begin_date)
and pbg.BUSINESS_GROUP_ID = pert.BUSINESS_GROUP_ID
-- and wfis.notification_id = (
-- SELECT ias.notification_id
and void is null ---added for 4665930
/* Commented for bug 5048771
and exists (select 1 --- 4665930
FROM wf_lookups l_at,
wf_lookups l_as,
wf_activities_vl a,
wf_process_activities pa,
wf_item_types_vl it,
wf_items i,
wf_item_activity_statuses ias
WHERE ias.item_type = 'PSPADJWF'
AND ias.item_key = pal.adjustment_batch_name
AND i.item_type = 'PSPADJWF'
AND i.item_key = pal.adjustment_batch_name
AND i.begin_date between a.begin_date AND nvl(a.end_date, i.begin_date)
AND i.item_type = it.name
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name
AND pa.activity_item_type = a.item_type
AND l_at.lookup_type = 'WFENG_ACTIVITY_TYPE'
AND l_at.lookup_code = a.type
AND l_as.lookup_type = 'WFENG_STATUS'
AND l_as.lookup_code = ias.activity_status
AND a.name = 'NOT_APPROVAL_REQUIRED')
*/
-- AND assignment.person_id = ppf.person_id
-- AND assignment.supervisor_id = people.person_id (+)
-- AND assignment.assignment_type ='E'
-- AND trunc(SYSDATE) BETWEEN people.effective_start_date (+) AND people.effective_end_date(+)
-- AND trunc(SYSDATE) BETWEEN assignment.effective_start_date AND assignment.effective_end_date
-- AND assignment.primary_flag = 'Y'
;
eff_master_rec.effort_report_id.delete;
eff_master_rec.person_id.delete;
eff_master_rec.full_name.delete;
eff_master_rec.start_date.delete;
eff_master_rec.end_date.delete;
eff_master_rec.effort_report_id.delete;
eff_master_rec.person_id.delete;
eff_master_rec.full_name.delete;
eff_master_rec.start_date.delete;
eff_master_rec.end_date.delete;
eff_master_rec.effort_report_id.delete;
eff_master_rec.person_id.delete;
eff_master_rec.full_name.delete;
eff_master_rec.start_date.delete;
eff_master_rec.end_date.delete;
hr_utility.trace(' Create Elements: pay_element_sets_pkg.insert_row');
pay_element_sets_pkg.insert_row(l_rowid, l_element_Set_id, l_business_group_id, null, l_element_set_name,'C', 'LD Eff Reports Migration Set', null, null);
hr_utility.trace(' Create Elements: pay_element_type_rules_pkg.insert_row');
pay_element_type_rules_pkg.insert_row(l_rowid, eff_element_rec.element_type_id(i), l_element_Set_id, 'I',
sysdate, fnd_global.user_id, fnd_global.user_id, fnd_global.user_id, sysdate);
/* Delete the obsolete menu items */
-- fnd_file.put_line( FND_FILE.LOG, ' Before menu delete ');
hr_utility.trace(' Delete Menus : START');
hr_utility.trace(' Delete Menu : Effort Report Period Summary');
delete_flag => 'Y');
hr_utility.trace(' Delete Menu : Effort Report Creation');
delete_flag => 'Y');
hr_utility.trace(' Delete Menu :Adhoc Effort Report Creation');
delete_flag => 'Y');
hr_utility.trace(' Delete Menu :Review Effort Report');
delete_flag => 'Y');
hr_utility.trace(' Delete Menu :Review Effort Report');
delete_flag => 'Y');
hr_utility.trace(' Delete Menu :Effort Report Aging');
delete_flag => 'Y');
hr_utility.trace(' Delete Menu :Effort Report Messages');
delete_flag => 'Y');
hr_utility.trace(' Delete Menu :Setup: Effort Report Element Types');
delete_flag => 'Y');
hr_utility.trace(' Delete Menu :Setup: Create Notification Users');
delete_flag => 'Y');
select psp_upgrade_115_s.nextval
into l_current_run
from dual;
hr_utility.trace(' Insert into psp_upgrade_115');
INSERT into psp_upgrade_115(run_id, phase,object_name,date_time,status,error_message)
VALUES (l_current_run,10000,'PSP_UPGRADE_115',sysdate,'R','Migrated Effort Reports') ;