[Home] [Help]
72: * 04-Jun-04 aprabhak 115.41 Corrected the threading issue.
73: * 04-Jun-04 aprabhak 115.42 Corrected the thread process name
74: * 29-Jun-04 aprabhak 115.43 Fixed the bug #3712169
75: * 30-Jun-04 aprabhak 115.44 bg_id passed to the assignment changes
76: * is obtained ben_cwb_person_info
77: * 06-Jul-04 aprabhak 115.45 Access Change Routine
78: * 09-Jul-04 aprabhak 115.46 Recent Sal Change Message Corrected
79: * 14-Jul-04 aprabhak 115.47 fixed the issues reported in drop 13
80: * 22-Jul-04 aprabhak 115.48 Fixed the caching issues in error report.
278:
279: CURSOR c_info_ovn (v_group_per_in_ler_id IN NUMBER)
280: IS
281: SELECT object_version_number
282: FROM ben_cwb_person_info info
283: WHERE info.group_per_in_ler_id = v_group_per_in_ler_id;
284:
285: CURSOR c_rate_ovn (v_group_per_in_ler_id IN NUMBER, v_pl_id IN NUMBER, v_oipl_id IN NUMBER)
286: IS
309: SELECT bg.NAME
310: , per.full_name
311: , info.business_group_id
312: FROM per_business_groups_perf bg
313: , ben_cwb_person_info info
314: , per_all_people_f per
315: , ben_per_in_ler pil
316: WHERE info.group_per_in_ler_id = v_group_per_in_ler_id
317: AND bg.business_group_id = info.business_group_id
328: , per.employee_number
329: , per.assignment_id
330: , per.business_group_id
331: , per.legislation_code
332: FROM ben_cwb_person_info per
333: WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
334:
335: CURSOR c_prior_assignment(v_group_per_in_ler_id IN NUMBER)
336: IS
376: , per.ass_attribute27
377: , per.ass_attribute28
378: , per.ass_attribute29
379: , per.ass_attribute30
380: FROM ben_cwb_person_info per
381: , per_jobs_tl job
382: , hr_all_positions_f_tl pos
383: , per_grades_tl grades
384: , pay_people_groups ppl_groups
447: , per.business_group_id
448: FROM
449: ben_per_in_ler pil,
450: ben_per_in_ler mgr_pil,
451: ben_cwb_person_info per,
452: ben_cwb_group_hrchy hrchy,
453: per_business_groups_perf bg
454: where pil.per_in_ler_stat_cd = 'STRTD'
455: AND pil.group_pl_id = per.group_pl_id
499: FROM ben_per_in_ler pil
500: , ben_per_in_ler mgr_pil
501: , ben_cwb_group_hrchy hrchy
502: , per_business_groups_perf bg
503: , ben_cwb_person_info per
504: , ben_cwb_person_rates rates
505: , ben_cwb_pl_dsgn dsgn
506: WHERE pil.per_in_ler_stat_cd = 'STRTD'
507: AND pil.group_pl_id = v_pl_id
693: FROM ben_cwb_person_rates rt
694: , ben_oipl_f oipl
695: , ben_cwb_pl_dsgn dsgn
696: , ben_opt_f opt
697: , ben_cwb_person_info info
698: , ben_per_in_ler pil
699: , ben_cwb_xchg xchg
700: WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
701: AND rt.pl_id = dsgn.pl_id
741: FROM ben_cwb_person_rates rt
742: , ben_oipl_f oipl
743: , ben_cwb_pl_dsgn dsgn
744: , ben_opt_f opt
745: , ben_cwb_person_info info
746: , ben_per_in_ler pil
747: , ben_cwb_xchg xchg
748: WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
749: AND rt.group_pl_id = v_group_pl_id
799: , info.fte_factor
800: , rt.currency
801: FROM ben_cwb_person_rates rt
802: , ben_cwb_pl_dsgn dsgn
803: , ben_cwb_person_info info
804: , ben_per_in_ler pil
805: , ben_oipl_f oipl
806: , ben_opt_f opt
807: , ben_cwb_xchg xchg
829: , xtra_info.assignment_id
830: , xtra_info.object_version_number
831: , xtra_info.assignment_extra_info_id
832: FROM per_assignment_extra_info xtra_info
833: , ben_cwb_person_info per
834: WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
835: AND xtra_info.assignment_id = per.assignment_id
836: AND xtra_info.information_type = 'CWBRANK'
837: AND xtra_info.aei_information1 IS NOT NULL
847: , xtra_info.aei_information4
848: , xtra_info.assignment_id
849: , xtra_info.object_version_number
850: FROM per_assignment_extra_info xtra_info
851: , ben_cwb_person_info per
852: WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
853: AND xtra_info.assignment_id = per.assignment_id
854: AND xtra_info.information_type = 'CWBRANK'
855: AND xtra_info.aei_information5 = fnd_date.date_to_canonical(v_eff_dt)
864: , ppp.object_version_number
865: FROM per_all_assignments_f asg
866: , per_pay_bases ppb
867: , per_pay_proposals ppp
868: , ben_cwb_person_info per
869: WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
870: AND asg.assignment_id = per.assignment_id
871: AND v_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
872: AND ppb.pay_basis_id = asg.pay_basis_id
882: CURSOR c_future_pay_proposal (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
883: IS
884: SELECT ppp.proposed_salary_n
885: FROM per_pay_proposals ppp
886: , ben_cwb_person_info per
887: WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
888: AND ppp.assignment_id = per.assignment_id
889: AND ppp.change_date > v_effective_date;
890:
919: , per.base_salary_currency
920: , initcap(base_salary_frequency) base_salary_frequency
921: , pay_annulization_factor
922: , fte_factor
923: FROM ben_cwb_person_info per
924: WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
925:
926: CURSOR c_group_plan_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
927: IS
981: SELECT pil.per_in_ler_id
982: ,nvl(info.custom_name,info.full_name) full_name
983: FROM ben_per_in_ler pil
984: ,ben_cwb_person_groups pgroup
985: ,ben_cwb_person_info info
986: WHERE pil.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
987: AND pil.group_pl_id = v_group_pl_id
988: AND pgroup.group_per_in_ler_id = pil.per_in_ler_id
989: AND pgroup.group_oipl_id = -1
1037: dsgn.uom_precision,
1038: info.pay_annulization_factor,
1039: dsgn.salary_change_reason
1040: FROM ben_cwb_pl_dsgn dsgn,
1041: ben_cwb_person_info info
1042: WHERE dsgn.group_pl_id = v_group_pl_id
1043: AND dsgn.group_pl_id = dsgn.pl_id
1044: AND dsgn.oipl_id = -1
1045: AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
2410: INTO l_info_ovn;
2411: CLOSE c_info_ovn;
2412:
2413: WRITE ('updating post process stat code...');
2414: ben_cwb_person_info_api.update_person_info
2415: (p_group_per_in_ler_id => p_group_per_in_ler_id
2416: , p_post_process_stat_cd => 'PR'
2417: , p_object_version_number => l_info_ovn.object_version_number
2418: );