[Home] [Help]
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
5714: And pal.person_id = per.person_id
5715: And ppc.payroll_control_id = pal.payroll_control_id
5716: And per.status_code not in ('S','R')
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
5746: And pregen.person_id = per.person_id
5747: And ppc.payroll_control_id = pregen.payroll_control_id
5748: And per.status_code not in ('S','R')
5778: Psp_payroll_controls ppc,
5779: psp_payroll_sub_lines psub,
5780: psp_payroll_lines ppl
5781: Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
5782: And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
5783: And psub.payroll_line_id = ppl.payroll_line_id
5784: And ppl.person_id = per.person_id
5785: And dlh.distribution_date between per.start_date and per.end_date
5786: And ppc.run_id = p_run_id
5822: Psp_payroll_controls ppc,
5823: psp_payroll_sub_lines psub,
5824: psp_payroll_lines ppl
5825: Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
5826: And per.request_id in (select -person_id from psp_supercede_persons_gt)
5827: And psub.payroll_line_id = ppl.payroll_line_id
5828: And ppl.person_id = per.person_id
5829: And dlh.distribution_date between per.start_date and per.end_date
5830: And ppc.run_id = p_run_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
5864: And pregen.person_id = per.person_id
5865: And ppc.payroll_control_id = pregen.payroll_control_id
5866: And per.status_code not in ('S','R')
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
5901: and perd.assignment_id = pal.assignment_id
5902: And per.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0)
5903: And ppc.run_id = p_run_id
5904: And pal.person_id = per.person_id
5905: And ppc.payroll_control_id = pal.payroll_control_id
5906: And per.status_code not in ('S','R')
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
5936: and perd.assignment_id = pregen.assignment_id
5937: And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
5938: And ppc.run_id = p_run_id
5939: And pregen.person_id = per.person_id
5940: And ppc.payroll_control_id = pregen.payroll_control_id
5941: And per.status_code not in ('S','R')
5974: psp_eff_report_details perd
5975: Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
5976: and perd.effort_report_id = per.effort_report_id
5977: and perd.assignment_id = ppl.assignment_id
5978: And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
5979: And psub.payroll_line_id = ppl.payroll_line_id
5980: And ppl.person_id = per.person_id
5981: And dlh.distribution_date between per.start_date and per.end_date
5982: And ppc.run_id = p_run_id
6022: psp_eff_report_details perd
6023: Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
6024: and perd.effort_report_id = per.effort_report_id
6025: and perd.assignment_id = ppl.assignment_id
6026: And per.request_id in (select -person_id from psp_supercede_persons_gt)
6027: And psub.payroll_line_id = ppl.payroll_line_id
6028: And ppl.person_id = per.person_id
6029: And dlh.distribution_date between per.start_date and per.end_date
6030: And ppc.run_id = p_run_id
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
6064: and perd.assignment_id = pregen.assignment_id
6065: And per.request_id in ( select -person_id from psp_supercede_persons_gt )
6066: And ppc.run_id = p_run_id
6067: And pregen.person_id = per.person_id
6068: And ppc.payroll_control_id = pregen.payroll_control_id
6069: And per.status_code not in ('S','R')
6100: where run_id = p_run_id)
6101: and rownum = 1
6102: and per.request_id in
6103: (select abs(person_id)
6104: from psp_supercede_persons_gt
6105: where person_id < 0);
6106: l_emp_overlap_count integer;
6107:
6108: -- uva isue, supercede for matching asg option
6108: -- uva isue, supercede for matching asg option
6109: cursor check_emp_match_option is
6110: select count(*)
6111: from psp_report_templates_h
6112: where request_id in ( select -person_id from psp_supercede_persons_gt )
6113: and selection_match_level = 'EMP';
6114:
6115: l_count_emp_match integer;
6116:
6182: and dtl.criteria_lookup_type = 'PSP_SELECTION_CRITERIA'
6183: and dtl.criteria_lookup_code = 'FRP'
6184: and dtl.request_id = h.request_id
6185: and h.request_id in ( select -person_id
6186: from psp_supercede_persons_gt
6187: where person_id < 0)
6188: and h.final_recip_notified_flag = 'Y';
6189:
6190: cursor get_init_wf_names is
6194: psp_report_templates_h h
6195: where wf.orig_system = 'PER'
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);
6211: and wf.orig_system_id = er.person_id
6212: and h.request_id = er.request_id
6213: and h.initiator_accept_flag = 'Y'
6214: and er.request_id in ( select -person_id
6215: from psp_supercede_persons_gt
6216: where person_id < 0)
6217: and er.superceding_request_id = p_SandT_reqid;
6218:
6219: cursor get_app_wf_names is
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 )
6228: and er.superceding_request_id = p_SandT_reqid
6229: and era.approval_status <> 'R'
6230: and er.request_id = h.request_id
6231: and h.initiator_accept_flag = 'Y';
6517: fnd_signon.set_session(to_char(sysdate,'dd-mon-yyyy'));
6518: p_return_status := fnd_api.g_ret_sts_success;
6519: l_S_and_T_reqid := fnd_global.conc_request_id;
6520: fnd_stats.gather_table_stats(ownname => 'PSP',
6521: tabname => 'PSP_SUPERCEDE_PERSONS_GT');
6522: open check_er_template_h;
6523: fetch check_er_template_h bulk collect into t_template_id, t_request_id;
6524: close check_er_template_h;
6525:
6530: hr_utility.trace( 'SandT_Supercede--> template_id.count='||t_template_id.count);
6531:
6532: l_supercede_reqid_str := null;
6533:
6534: delete psp_supercede_persons_gt;
6535: for k in 1..t_template_id.count
6536: loop
6537: if t_template_id.count = k or --- supercede for distinct template
6538: (t_template_id.count > k and t_template_id(k) <> t_template_id(k+1)) then
6540: l_supercede_reqid_str := t_request_id(k);
6541: else
6542: l_supercede_reqid_str := l_supercede_reqid_str ||', '|| t_request_id(k);
6543: end if;
6544: insert into psp_supercede_persons_gt
6545: (person_id)
6546: values ( -t_request_id(k));
6547:
6548: open check_emp_overlap;
6640: else -- tolerance set to non null value
6641: --- load session temp table
6642: hr_utility.trace( 'SandT_Supercede--> purge GT table - 1');
6643: forall i in 1..t_person_table.count
6644: insert into psp_supercede_persons_gt
6645: (person_id)
6646: values (t_person_table(i));
6647: hr_utility.trace( 'SandT_Supercede--> insert into GT potential persons Number of recodrs=' ||t_person_table.count||'person_id =' ||t_person_table(1));
6648: savepoint populate_er_tables;
6670: l_sql_string := ' begin
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||')
6724: l_sql_string := ' begin
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||')
6833: end loop;
6834: end if; --- non zero tolerance persons count > 0
6835: end if; -- tolerance = 0
6836: --- delete persons .. retain -persons_ids.. the request_ids.
6837: delete psp_supercede_persons_gt where person_id > 0;
6838: if l_superceded_flag then
6839: -- send superceding notifications
6840: send_notifications(l_supercede_reqid_str,
6841: t_template_id(k),
6847: l_superceded_flag := false;
6848: end if;
6849: end if; -- supercede persons count > 0
6850: l_supercede_reqid_str := null;
6851: delete psp_supercede_persons_gt;
6852: else
6853: l_supercede_reqid_str := null;
6854: delete psp_supercede_persons_gt;
6855: end if; --- only if s_and_t processes emps in er
6850: l_supercede_reqid_str := null;
6851: delete psp_supercede_persons_gt;
6852: else
6853: l_supercede_reqid_str := null;
6854: delete psp_supercede_persons_gt;
6855: end if; --- only if s_and_t processes emps in er
6856: else --- more than on reqid for Template_id
6857: insert into psp_supercede_persons_gt (person_id) values ( -t_request_id(k));
6858: if l_supercede_reqid_str is null then
6853: l_supercede_reqid_str := null;
6854: delete psp_supercede_persons_gt;
6855: end if; --- only if s_and_t processes emps in er
6856: else --- more than on reqid for Template_id
6857: insert into psp_supercede_persons_gt (person_id) values ( -t_request_id(k));
6858: if l_supercede_reqid_str is null then
6859: l_supercede_reqid_str := t_request_id(k);
6860: else
6861: l_supercede_reqid_str := l_supercede_reqid_str ||', '|| t_request_id(k);
6861: l_supercede_reqid_str := l_supercede_reqid_str ||', '|| t_request_id(k);
6862: end if;
6863: end if; -- new template
6864: end loop;
6865: delete psp_supercede_persons_gt;
6866: t_person_table := t_num_15_type(null);
6867: t_efforts_table := t_num_15_type(null);
6868: t_sum_line_id := t_num_15_type(null);
6869: t_person_table.delete;