[Home] [Help]
5705: Select per.effort_report_id,
5706: per.person_id,
5707: min(pal.summary_line_id) summary_line_id
5708: From psp_adjustment_lines_history pal,
5709: Psp_eff_reports per,
5710: Psp_payroll_controls ppc
5711: Where pal.distribution_date between per.start_date and per.end_date
5712: And per.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0)
5713: And ppc.run_id = p_run_id
5737: Select per.effort_report_id,
5738: per.person_id,
5739: min(pregen.summary_line_id)
5740: From psp_pre_gen_dist_lines_history pregen,
5741: Psp_eff_reports per,
5742: Psp_payroll_controls ppc
5743: Where pregen.distribution_date between per.start_date and per.end_date
5744: And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
5745: And ppc.run_id = p_run_id
5772: cursor payroll_er_supercede is
5773: Select per.effort_report_id,
5774: per.person_id,
5775: MIN(dlh.summary_line_id) summary_line_id
5776: From psp_eff_reports per,
5777: Psp_distribution_lines_history DLH,
5778: Psp_payroll_controls ppc,
5779: psp_payroll_sub_lines psub,
5780: psp_payroll_lines ppl
5816: from (
5817: Select per.effort_report_id,
5818: per.person_id,
5819: dlh.summary_line_id summary_line_id
5820: From psp_eff_reports per,
5821: Psp_distribution_lines_history DLH,
5822: Psp_payroll_controls ppc,
5823: psp_payroll_sub_lines psub,
5824: psp_payroll_lines ppl
5855: Select per.effort_report_id,
5856: per.person_id,
5857: pregen.summary_line_id summary_line_id
5858: From psp_pre_gen_dist_lines_history pregen,
5859: Psp_eff_reports per,
5860: Psp_payroll_controls ppc
5861: Where pregen.distribution_date between per.start_date and per.end_date
5862: And per.request_id in ( select -person_id from psp_supercede_persons_gt )
5863: And ppc.run_id = p_run_id
5892: Select per.effort_report_id,
5893: per.person_id,
5894: min(pal.summary_line_id) summary_line_id
5895: From psp_adjustment_lines_history pal,
5896: Psp_eff_reports per,
5897: Psp_payroll_controls ppc,
5898: psp_eff_report_details perd
5899: Where pal.distribution_date between per.start_date and per.end_date
5900: and perd.effort_report_id = per.effort_report_id
5927: Select per.effort_report_id,
5928: per.person_id,
5929: min(pregen.summary_line_id)
5930: From psp_pre_gen_dist_lines_history pregen,
5931: Psp_eff_reports per,
5932: Psp_payroll_controls ppc,
5933: psp_eff_report_details perd
5934: Where pregen.distribution_date between per.start_date and per.end_date
5935: and perd.effort_report_id = per.effort_report_id
5965: cursor payroll_asg_er_supercede is
5966: Select per.effort_report_id,
5967: per.person_id,
5968: MIN(dlh.summary_line_id) summary_line_id
5969: From psp_eff_reports per,
5970: Psp_distribution_lines_history DLH,
5971: Psp_payroll_controls ppc,
5972: psp_payroll_sub_lines psub,
5973: psp_payroll_lines ppl,
6013: from (
6014: Select per.effort_report_id,
6015: per.person_id,
6016: dlh.summary_line_id summary_line_id
6017: From psp_eff_reports per,
6018: Psp_distribution_lines_history DLH,
6019: Psp_payroll_controls ppc,
6020: psp_payroll_sub_lines psub,
6021: psp_payroll_lines ppl,
6055: Select per.effort_report_id,
6056: per.person_id,
6057: pregen.summary_line_id summary_line_id
6058: From psp_pre_gen_dist_lines_history pregen,
6059: Psp_eff_reports per,
6060: Psp_payroll_controls ppc,
6061: psp_eff_report_details perd
6062: Where pregen.distribution_date between per.start_date and per.end_date
6063: and perd.effort_report_id = per.effort_report_id
6090: ))
6091: group by person_id,effort_report_id;
6092: cursor check_emp_overlap is
6093: select count(*)
6094: from psp_Eff_reports per,
6095: psp_summary_lines psl
6096: where per.person_id = psl.person_id
6097: and psl.payroll_control_id in
6098: (select payroll_control_id
6115: l_count_emp_match integer;
6116:
6117: cursor get_eff_id(p_req_id in integer, p_per_id in integer) is
6118: select effort_report_id
6119: from psp_eff_reports
6120: where request_id = p_req_id and person_id = p_per_id;
6121: l_eff_id integer;
6122:
6123: l_proj_segment varchar2(30);
6196: and wf.orig_system_id = h.initiator_person_id
6197: and h.request_id in ( select -person_id
6198: from psp_supercede_persons_gt
6199: where person_id < 0)
6200: and exists (select 1 from psp_eff_reports per
6201: where per.request_id = h.request_id
6202: and per.superceding_request_id = p_SandT_reqid);
6203:
6204: cursor get_per_wf_names is
6204: cursor get_per_wf_names is
6205: select distinct wf.name,
6206: er.person_id
6207: from wf_roles wf,
6208: psp_eff_reports er,
6209: psp_report_templates_h h
6210: where wf.orig_system = 'PER'
6211: and wf.orig_system_id = er.person_id
6212: and h.request_id = er.request_id
6219: cursor get_app_wf_names is
6220: select distinct era.wf_role_name
6221: from psp_eff_report_approvals era,
6222: psp_eff_report_details erd,
6223: psp_eff_reports er,
6224: psp_report_templates_h h
6225: where erd.effort_report_detail_id = era.effort_report_detail_id
6226: and erd.effort_report_id = er.effort_report_id
6227: and er.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0 )
6623: CLOSE get_Con_Program_name_csr;
6624:
6625: --Bug 4339561
6626: -- Pupulating Column superceding_program_name
6627: update psp_eff_reports
6628: set status_code = 'S',
6629: superceding_summary_line_id = t_sum_line_id(i),
6630: superceding_request_id = l_S_and_T_reqid,
6631: superceding_program_name = l_Con_Program_name
6671: select distinct er.request_id, er.person_id
6672: bulk collect into :reqtable, :pertable
6673: from psp_eff_report_details erd,
6674: psp_supercede_persons_gt gt,
6675: psp_eff_reports er
6676: where er.status_code not in
6677: ('||''''||'S'||''''||','||''''||'R'||''''||')
6678: and er.request_id in ('||l_supercede_reqid_str||')
6679: and er.effort_report_id = erd.effort_report_id
6725: select distinct er.request_id, er.person_id
6726: bulk collect into :reqtable, :pertable
6727: from psp_eff_report_details erd,
6728: psp_supercede_persons_gt gt,
6729: psp_eff_reports er
6730: where er.status_code not in
6731: ('||''''||'S'||''''||','||''''||'R'||''''||')
6732: and er.request_id in ('||l_supercede_reqid_str||')
6733: and gt.person_id = er.person_id
6817: CLOSE get_Con_Program_name_csr;
6818: --Bug 4339561
6819: -- Pupulating Column superceding_program_name
6820:
6821: update psp_eff_reports
6822: set status_code = 'S',
6823: superceding_summary_line_id = t_tolerance_sline(i),
6824: superceding_request_id = l_S_and_T_reqid,
6825: superceding_program_name = l_Con_Program_name