[Home] [Help]
5282: Select per.effort_report_id,
5283: per.person_id,
5284: min(pal.summary_line_id) summary_line_id
5285: From psp_adjustment_lines_history pal,
5286: Psp_eff_reports per,
5287: Psp_payroll_controls ppc
5288: Where pal.distribution_date between per.start_date and per.end_date
5289: And per.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0)
5290: And ppc.run_id = p_run_id
5314: Select per.effort_report_id,
5315: per.person_id,
5316: min(pregen.summary_line_id)
5317: From psp_pre_gen_dist_lines_history pregen,
5318: Psp_eff_reports per,
5319: Psp_payroll_controls ppc
5320: Where pregen.distribution_date between per.start_date and per.end_date
5321: And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
5322: And ppc.run_id = p_run_id
5349: cursor payroll_er_supercede is
5350: Select per.effort_report_id,
5351: per.person_id,
5352: MIN(dlh.summary_line_id) summary_line_id
5353: From psp_eff_reports per,
5354: Psp_distribution_lines_history DLH,
5355: Psp_payroll_controls ppc,
5356: psp_payroll_sub_lines psub,
5357: psp_payroll_lines ppl
5393: from (
5394: Select per.effort_report_id,
5395: per.person_id,
5396: dlh.summary_line_id summary_line_id
5397: From psp_eff_reports per,
5398: Psp_distribution_lines_history DLH,
5399: Psp_payroll_controls ppc,
5400: psp_payroll_sub_lines psub,
5401: psp_payroll_lines ppl
5432: Select per.effort_report_id,
5433: per.person_id,
5434: pregen.summary_line_id summary_line_id
5435: From psp_pre_gen_dist_lines_history pregen,
5436: Psp_eff_reports per,
5437: Psp_payroll_controls ppc
5438: Where pregen.distribution_date between per.start_date and per.end_date
5439: And per.request_id in ( select -person_id from psp_supercede_persons_gt )
5440: And ppc.run_id = p_run_id
5469: Select per.effort_report_id,
5470: per.person_id,
5471: min(pal.summary_line_id) summary_line_id
5472: From psp_adjustment_lines_history pal,
5473: Psp_eff_reports per,
5474: Psp_payroll_controls ppc,
5475: psp_eff_report_details perd
5476: Where pal.distribution_date between per.start_date and per.end_date
5477: and perd.effort_report_id = per.effort_report_id
5504: Select per.effort_report_id,
5505: per.person_id,
5506: min(pregen.summary_line_id)
5507: From psp_pre_gen_dist_lines_history pregen,
5508: Psp_eff_reports per,
5509: Psp_payroll_controls ppc,
5510: psp_eff_report_details perd
5511: Where pregen.distribution_date between per.start_date and per.end_date
5512: and perd.effort_report_id = per.effort_report_id
5542: cursor payroll_asg_er_supercede is
5543: Select per.effort_report_id,
5544: per.person_id,
5545: MIN(dlh.summary_line_id) summary_line_id
5546: From psp_eff_reports per,
5547: Psp_distribution_lines_history DLH,
5548: Psp_payroll_controls ppc,
5549: psp_payroll_sub_lines psub,
5550: psp_payroll_lines ppl,
5590: from (
5591: Select per.effort_report_id,
5592: per.person_id,
5593: dlh.summary_line_id summary_line_id
5594: From psp_eff_reports per,
5595: Psp_distribution_lines_history DLH,
5596: Psp_payroll_controls ppc,
5597: psp_payroll_sub_lines psub,
5598: psp_payroll_lines ppl,
5632: Select per.effort_report_id,
5633: per.person_id,
5634: pregen.summary_line_id summary_line_id
5635: From psp_pre_gen_dist_lines_history pregen,
5636: Psp_eff_reports per,
5637: Psp_payroll_controls ppc,
5638: psp_eff_report_details perd
5639: Where pregen.distribution_date between per.start_date and per.end_date
5640: and perd.effort_report_id = per.effort_report_id
5667: ))
5668: group by person_id,effort_report_id;
5669: cursor check_emp_overlap is
5670: select count(*)
5671: from psp_Eff_reports per,
5672: psp_summary_lines psl
5673: where per.person_id = psl.person_id
5674: and psl.payroll_control_id in
5675: (select payroll_control_id
5692: l_count_emp_match integer;
5693:
5694: cursor get_eff_id(p_req_id in integer, p_per_id in integer) is
5695: select effort_report_id
5696: from psp_eff_reports
5697: where request_id = p_req_id and person_id = p_per_id;
5698: l_eff_id integer;
5699:
5700: l_proj_segment varchar2(30);
5773: and wf.orig_system_id = h.initiator_person_id
5774: and h.request_id in ( select -person_id
5775: from psp_supercede_persons_gt
5776: where person_id < 0)
5777: and exists (select 1 from psp_eff_reports per
5778: where per.request_id = h.request_id
5779: and per.superceding_request_id = p_SandT_reqid);
5780:
5781: cursor get_per_wf_names is
5781: cursor get_per_wf_names is
5782: select distinct wf.name,
5783: er.person_id
5784: from wf_roles wf,
5785: psp_eff_reports er,
5786: psp_report_templates_h h
5787: where wf.orig_system = 'PER'
5788: and wf.orig_system_id = er.person_id
5789: and h.request_id = er.request_id
5796: cursor get_app_wf_names is
5797: select distinct era.wf_role_name
5798: from psp_eff_report_approvals era,
5799: psp_eff_report_details erd,
5800: psp_eff_reports er,
5801: psp_report_templates_h h
5802: where erd.effort_report_detail_id = era.effort_report_detail_id
5803: and erd.effort_report_id = er.effort_report_id
5804: and er.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0 )
6196: CLOSE get_Con_Program_name_csr;
6197:
6198: --Bug 4339561
6199: -- Pupulating Column superceding_program_name
6200: update psp_eff_reports
6201: set status_code = 'S',
6202: superceding_summary_line_id = t_sum_line_id(i),
6203: superceding_request_id = l_S_and_T_reqid,
6204: superceding_program_name = l_Con_Program_name
6244: select distinct er.request_id, er.person_id
6245: bulk collect into :reqtable, :pertable
6246: from psp_eff_report_details erd,
6247: psp_supercede_persons_gt gt,
6248: psp_eff_reports er
6249: where er.status_code not in
6250: ('||''''||'S'||''''||','||''''||'R'||''''||')
6251: and er.request_id in ('||l_supercede_reqid_str||')
6252: and er.effort_report_id = erd.effort_report_id
6298: select distinct er.request_id, er.person_id
6299: bulk collect into :reqtable, :pertable
6300: from psp_eff_report_details erd,
6301: psp_supercede_persons_gt gt,
6302: psp_eff_reports er
6303: where er.status_code not in
6304: ('||''''||'S'||''''||','||''''||'R'||''''||')
6305: and er.request_id in ('||l_supercede_reqid_str||')
6306: and gt.person_id = er.person_id
6390: CLOSE get_Con_Program_name_csr;
6391: --Bug 4339561
6392: -- Pupulating Column superceding_program_name
6393:
6394: update psp_eff_reports
6395: set status_code = 'S',
6396: superceding_summary_line_id = t_tolerance_sline(i),
6397: superceding_request_id = l_S_and_T_reqid,
6398: superceding_program_name = l_Con_Program_name