[Home] [Help]
5: l_vpf_cer_id number;
6: begin
7: select copy_entity_result_id
8: into l_vpf_cer_id
9: from ben_copy_entity_results
10: where copy_entity_txn_id = p_copy_entity_txn_id
11: and result_type_cd ='DISPLAY'
12: and table_alias = 'VPF'
13: and information1 = p_vpf_id;
27: procedure update_crset_type(p_copy_entity_txn_id in number,
28: p_crset_id in number,
29: p_crset_type in varchar2) is
30: begin
31: update ben_copy_entity_results
32: set information100 = p_crset_type
33: where copy_entity_txn_id = p_copy_entity_txn_id
34: and table_alias ='CRSET'
35: and information161 = p_crset_id;
53: return 'N';
54: elsif p_oipl_cer_id is not null and p_point_cer_id is null then
55: select information262
56: into l_point_cer_id
57: from ben_copy_entity_results
58: where copy_entity_result_id = p_oipl_cer_id;
59: elsif p_plip_cer_id is not null and p_pl_cer_id is null then
60: select information252
61: into l_grade_cer_id
58: where copy_entity_result_id = p_oipl_cer_id;
59: elsif p_plip_cer_id is not null and p_pl_cer_id is null then
60: select information252
61: into l_grade_cer_id
62: from ben_copy_entity_results
63: where copy_entity_result_id = p_plip_cer_id;
64: elsif p_point_cer_id is not null then
65: l_point_cer_id := p_point_cer_id;
66: elsif p_pl_cer_id is not null then
68: end if;
69: if l_point_cer_id is not null then
70: select count(*)
71: into l_count
72: from ben_copy_entity_results
73: where copy_entity_txn_id = p_copy_entity_txn_id
74: and table_alias ='CRRATE'
75: and p_effective_date between information2 and information3
76: and information169 = l_point_cer_id
78: end if;
79: if l_grade_cer_id is not null then
80: select count(*)
81: into l_count
82: from ben_copy_entity_results
83: where copy_entity_txn_id = p_copy_entity_txn_id
84: and table_alias ='CRRATE'
85: and p_effective_date between information2 and information3
86: and information230 = l_grade_cer_id
100: begin
101: hr_utility.set_location('vpf_cer_id is '||p_vpf_cer_id,1);
102: select information1
103: into l_elp_id
104: from ben_copy_entity_results
105: where copy_entity_txn_id = p_copy_entity_txn_id
106: and table_alias ='ELP'
107: and parent_entity_result_id = p_vpf_cer_id
108: and p_effective_date between information2 and information3;
238: begin
239: hr_utility.set_location('abr cer is'||p_abr_cer_id,10);
240: select information261,information247,information277,information278
241: into p_pl_id,p_opt_id,p_grade_cer_id,p_point_cer_id
242: from ben_copy_entity_results
243: where copy_entity_result_id = p_abr_cer_id;
244: hr_utility.set_location('pl is'||p_pl_id,10);
245: hr_utility.set_location('opt is'||p_opt_id,10);
246: if p_pl_id is not null then
273: begin
274: hr_utility.set_location('getting abr cer for pl cer'||p_pl_cer_id,10);
275: select copy_entity_result_id
276: into l_abr_cer_id
277: from ben_copy_entity_results
278: where INFORMATION277 = p_pl_cer_id
279: and table_alias = 'ABR'
280: and copy_entity_txn_id = p_copy_entity_txn_id
281: and p_effective_date between information2 and information3;
301: begin
302: hr_utility.set_location('getting abr cer for opt cer'||p_opt_cer_id,10);
303: select copy_entity_result_id
304: into l_abr_cer_id
305: from ben_copy_entity_results
306: where INFORMATION278 = p_opt_cer_id
307: and table_alias = 'ABR'
308: and copy_entity_txn_id = p_copy_entity_txn_id
309: and p_effective_date between information2 and information3;
337: begin
338: hr_utility.set_location('inside is_hrr_exists',10);
339: select copy_entity_result_id
340: into l_hrr_cer_id
341: from ben_copy_entity_results
342: where copy_entity_txn_id = p_copy_entity_txn_id
343: and table_alias = 'HRRATE'
344: and (information277 is null or information277 = p_grade_cer_id)
345: and (information278 is null or information278 = p_point_cer_id)
369: l_count number;
370: begin
371: hr_utility.set_location('inside is_crr_exists',10);
372: select count(*) into l_count
373: from ben_copy_entity_results
374: where copy_entity_txn_id = p_copy_entity_txn_id
375: and table_alias = 'CRRATE'
376: and (information230 is null or information230 = p_grade_cer_id)
377: and (information169 is null or information169 = p_point_cer_id)
457: end if;
458: if l_continue then
459: begin
460: hr_utility.set_location('cer insert api called',55);
461: ben_copy_entity_results_api.create_copy_entity_results
462: (p_effective_date => p_effective_date
463: ,p_copy_entity_txn_id => p_copy_entity_txn_id
464: ,p_result_type_cd => 'DISPLAY'
465: ,p_table_name => l_crr_tr_name
643: if l_continue then
644: begin
645: -- These mappings have been taken from hrben_to_stage document
646: -- call to create ben_cer is made here using api.
647: ben_copy_entity_results_api.create_copy_entity_results
648: (p_effective_date => p_effective_date
649: ,p_copy_entity_txn_id => p_copy_entity_txn_id
650: ,p_result_type_cd => 'DISPLAY'
651: ,p_table_name => l_crs_tr_name
729: p_ff_id in number default null,
730: p_ff_name in varchar2 default null) is
731: cursor csr_crset is
732: select *
733: from ben_copy_entity_results
734: where copy_entity_txn_id = p_copy_entity_txn_id
735: and table_alias = 'CRSET'
736: and information161 = p_crset_id
737: and p_effective_date between information2 and information3;
816: end if;
817: end if;
818: if l_del_future = 'Y' then
819: hr_utility.set_location('deleting future recs',6);
820: delete from ben_copy_entity_results
821: where copy_entity_txn_id = p_copy_entity_txn_id
822: and table_alias = 'CRSET'
823: and information161 = p_crset_id
824: and information2 > p_effective_date;
824: and information2 > p_effective_date;
825: end if;
826: if l_upd_effdt ='Y' then
827: hr_utility.set_location('updating effdt of curr row',6);
828: update ben_copy_entity_results
829: set INFORMATION3 = l_crset_eed
830: where copy_entity_result_id = crset_rec.copy_entity_result_id;
831: end if;
832: if l_upd_curr ='Y' then
830: where copy_entity_result_id = crset_rec.copy_entity_result_id;
831: end if;
832: if l_upd_curr ='Y' then
833: hr_utility.set_location('updating curr row with new values',6);
834: update ben_copy_entity_results
835: set dml_operation = l_dml_operation,
836: INFORMATION232 = p_loc_id,
837: INFORMATION233 = p_job_id,
838: INFORMATION234 = p_org_id,
1005: p_crset_type out nocopy varchar2,
1006: p_ins_flag out nocopy boolean) is
1007: cursor csr_crset is
1008: select information151,copy_entity_result_id,information161,information2,information3,information100
1009: from ben_copy_entity_results
1010: where table_alias ='CRSET'
1011: and copy_entity_txn_id = p_copy_entity_txn_id
1012: and information277 = p_elp_id;
1013: begin
1079: l_loc_cer_id number;
1080: l_hyphen varchar2(30);
1081: cursor csr_elig_prof is
1082: select *
1083: from ben_copy_entity_results
1084: where information1 = p_elp_id
1085: and copy_entity_txn_id = p_copy_entity_txn_id
1086: and table_alias ='ELP'
1087: order by result_type_cd;
1086: and table_alias ='ELP'
1087: order by result_type_cd;
1088: cursor csr_elig_crit (l_elp_cer_id number) is
1089: select *
1090: from ben_copy_entity_results
1091: where parent_entity_result_id = l_elp_cer_id
1092: and copy_entity_txn_id = p_copy_entity_txn_id
1093: and l_elp_esd between information2 and information3;
1094: begin
1303: INFORMATION101 bu_cd,
1304: INFORMATION102 fp_cd,
1305: INFORMATION103 pr_cd,
1306: INFORMATION104 event_type
1307: from ben_copy_entity_results
1308: where copy_entity_txn_id = p_copy_entity_txn_id
1309: and table_alias ='CRSET'
1310: and information161 = p_crset_id
1311: and p_effective_date between information2 and information3;
1394: INFORMATION4 bg , -- end dt
1395: INFORMATION1 VRBL_RT_PRFL_ID , -- pk of database row
1396: INFORMATION265 ovn
1397: into l_abr_cer_id,l_vpf_name, l_vpf_value, l_vpf_esd, l_vpf_eed, l_bg, l_vpf_id, l_vpf_ovn
1398: from ben_copy_entity_results
1399: where copy_entity_result_id = p_vpf_cer_id
1400: and information72 ='GSPSA'
1401: and INFORMATION77 = 'A';
1402: exception
1487: p_scale_id in number,
1488: p_scale_cer_id in number) is
1489: begin
1490: -- this routine will update scale_cer_id for all plips
1491: update ben_copy_entity_results
1492: set information255 = p_scale_id,
1493: information258 = p_scale_cer_id
1494: where table_alias = 'CPP'
1495: and copy_entity_txn_id = p_copy_entity_txn_id
1514: p_opt_cer_id in number) is
1515: begin
1516: hr_utility.set_location('inside chg_parent for oipl'||p_oipl_cer_id,10);
1517: begin
1518: update ben_copy_entity_results
1519: set parent_entity_result_id = p_oipl_cer_id,
1520: gs_parent_entity_result_id = p_oipl_cer_id
1521: where table_alias = 'ELP'
1522: and copy_entity_txn_id = p_copy_entity_txn_id
1545: -- The following Update Call is commented as the same is taken care by plan Copy APIs
1546: -- update_txn_table_route(p_copy_entity_txn_id);
1547: hr_utility.set_location('table routes updated ',25);
1548: begin
1549: update ben_copy_entity_results
1550: set parent_entity_result_id = p_plip_cer_id,
1551: gs_parent_entity_result_id = p_plip_cer_id
1552: where table_alias = 'COP'
1553: and copy_entity_txn_id = p_copy_entity_txn_id
1566: p_effective_date => trunc(sysdate));
1567: raise;
1568: end;
1569: begin
1570: update ben_copy_entity_results
1571: set parent_entity_result_id = p_plip_cer_id,
1572: gs_parent_entity_result_id = p_plip_cer_id
1573: where table_alias = 'ELP'
1574: and copy_entity_txn_id = p_copy_entity_txn_id
1609: p_table_name => l_cpp_tr_name);
1610: if p_pl_cer_id is not null then
1611: select information102,information307,information308
1612: into l_grd_short_name,l_grd_date_from,l_grd_date_to
1613: from ben_copy_entity_results
1614: where copy_entity_result_id = p_pl_cer_id;
1615: end if;
1616: begin
1617: select max(information263) into l_ordr_num
1614: where copy_entity_result_id = p_pl_cer_id;
1615: end if;
1616: begin
1617: select max(information263) into l_ordr_num
1618: from ben_copy_entity_results
1619: where copy_entity_txn_id = p_copy_entity_txn_id
1620: and table_alias = 'CPP';
1621: l_ordr_num := nvl(l_ordr_num,0) + 1;
1622: hr_utility.set_location('ord_num is '||l_ordr_num,20);
1621: l_ordr_num := nvl(l_ordr_num,0) + 1;
1622: hr_utility.set_location('ord_num is '||l_ordr_num,20);
1623: end;
1624: begin
1625: ben_copy_entity_results_api.create_copy_entity_results(
1626: p_effective_date => p_effective_date
1627: ,p_copy_entity_txn_id => p_copy_entity_txn_id
1628: ,p_gs_mr_src_entity_result_id => p_pgm_cer_id
1629: ,p_gs_parent_entity_result_id => p_pgm_cer_id
1676: l_start_date date;
1677: begin
1678: select information307
1679: into l_start_date
1680: from ben_copy_entity_results
1681: where copy_entity_result_id = p_grade_cer_id;
1682: return l_start_date;
1683: exception
1684: when others then
1691: p_business_group_id in number,
1692: p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
1693: cursor csr_scl is
1694: select copy_entity_result_id,information1
1695: from ben_copy_entity_results
1696: where copy_entity_txn_id = p_copy_entity_txn_id
1697: and table_alias ='SCALE';
1698: begin
1699: -- update_txn_table_route(p_copy_entity_txn_id);
1727: procedure cep_to_stage(p_cep_cer_id in number,
1728: p_copy_entity_txn_id in number) is
1729: begin
1730: hr_utility.set_location('inside cep_to_stage ',10);
1731: update ben_copy_entity_results
1732: set gs_parent_entity_result_id = parent_entity_result_id,
1733: gs_mirror_src_entity_result_id = mirror_src_entity_result_id
1734: where copy_entity_txn_id = p_copy_entity_txn_id
1735: and copy_entity_result_id = p_cep_cer_id
1751: procedure epa_to_stage(p_epa_cer_id in number,
1752: p_copy_entity_txn_id in number) is
1753: begin
1754: hr_utility.set_location('inside epa_to_stage ',10);
1755: update ben_copy_entity_results
1756: set gs_parent_entity_result_id = parent_entity_result_id,
1757: gs_mirror_src_entity_result_id = mirror_src_entity_result_id
1758: where copy_entity_txn_id = p_copy_entity_txn_id
1759: and copy_entity_result_id = p_epa_cer_id
1778: p_elpro_id in number,
1779: p_business_group_id in number) is
1780: Cursor Cs_Cep is
1781: Select Cep.Result_Type_cd
1782: From Ben_Copy_Entity_results ELP,
1783: Ben_Copy_Entity_Results CEP
1784: Where Elp.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
1785: and Elp.Copy_Entity_Result_id = p_Elp_Cer_Id
1786: and Cep.Copy_Entity_Txn_Id = Elp.Copy_Entity_Txn_id
1779: p_business_group_id in number) is
1780: Cursor Cs_Cep is
1781: Select Cep.Result_Type_cd
1782: From Ben_Copy_Entity_results ELP,
1783: Ben_Copy_Entity_Results CEP
1784: Where Elp.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
1785: and Elp.Copy_Entity_Result_id = p_Elp_Cer_Id
1786: and Cep.Copy_Entity_Txn_Id = Elp.Copy_Entity_Txn_id
1787: and Cep.Copy_Entity_Result_id = Elp.Mirror_Src_Entity_result_id
1786: and Cep.Copy_Entity_Txn_Id = Elp.Copy_Entity_Txn_id
1787: and Cep.Copy_Entity_Result_id = Elp.Mirror_Src_Entity_result_id
1788: and Elp.information1 is not null;
1789:
1790: L_Result_type_Cd Ben_Copy_Entity_Results.Result_type_Cd%TYPE;
1791: begin
1792:
1793: Open Cs_Cep;
1794: Fetch Cs_Cep into l_Result_type_Cd;
1796:
1797: hr_utility.set_location('inside elp_to_stage ',10);
1798: begin
1799: hr_utility.set_location('updating crit rows ',15);
1800: update ben_copy_entity_results
1801: set gs_parent_entity_result_id = parent_entity_result_id,
1802: gs_mirror_src_entity_result_id = mirror_src_entity_result_id,
1803: information101 = information1,
1804: information1 = null
1818: raise;
1819: end;
1820: begin
1821: hr_utility.set_location('updating elp row ',15);
1822: update ben_copy_entity_results
1823: set gs_parent_entity_result_id = parent_entity_result_id,
1824: gs_mirror_src_entity_result_id = mirror_src_entity_result_id,
1825: Result_type_cd = Nvl(l_Result_type_Cd, result_type_Cd)
1826: where copy_entity_txn_id = p_copy_entity_txn_id
1825: Result_type_cd = Nvl(l_Result_type_Cd, result_type_Cd)
1826: where copy_entity_txn_id = p_copy_entity_txn_id
1827: and copy_entity_result_id = p_elp_cer_id;
1828:
1829: update ben_copy_entity_results
1830: set result_type_cd = 'NO DISPLAY'
1831: where copy_entity_txn_id = p_copy_entity_txn_id
1832: and copy_entity_result_id = p_elp_cer_id
1833: and table_alias = 'ELP'
2376: begin
2377: hr_utility.set_location('bus area is'||p_business_area,10);
2378: hr_utility.set_location('cet is'||p_copy_entity_txn_id,10);
2379: select count(*) into l_plip_row
2380: from ben_copy_entity_results
2381: where copy_entity_txn_id = p_copy_entity_txn_id
2382: and table_alias ='CPP'
2383: and Result_type_Cd = 'DISPLAY'
2384: and information104 ='LINK'; -- plip record is linked as well
2385:
2386: hr_utility.set_location('grade in GL'||l_plip_row,10);
2387:
2388: select count(*) into l_oipl_row
2389: from ben_copy_entity_results
2390: where copy_entity_txn_id = p_copy_entity_txn_id
2391: and table_alias ='COP'
2392: and Result_Type_Cd = 'DISPLAY';
2393: hr_utility.set_location('steps'||l_oipl_row,20);
2392: and Result_Type_Cd = 'DISPLAY';
2393: hr_utility.set_location('steps'||l_oipl_row,20);
2394:
2395: select count(*) into l_rate_row
2396: from ben_copy_entity_results
2397: where copy_entity_txn_id = p_copy_entity_txn_id
2398: and table_alias = 'ABR'
2399: and Result_Type_Cd = 'DISPLAY';
2400: hr_utility.set_location('rates'||l_rate_row,30);
2399: and Result_Type_Cd = 'DISPLAY';
2400: hr_utility.set_location('rates'||l_rate_row,30);
2401:
2402: select count(*) into l_rule_row
2403: from ben_copy_entity_results
2404: where copy_entity_txn_id = p_copy_entity_txn_id
2405: and table_alias ='CEP'
2406: and Result_Type_Cd = 'DISPLAY';
2407: hr_utility.set_location('rules'||l_rule_row,40);
2431: hr_utility.set_location('cet is'||p_copy_entity_txn_id,101);
2432: begin
2433: select copy_entity_result_id
2434: into l_ctrl_rec_cer_id
2435: from ben_copy_entity_results
2436: where copy_entity_txn_id = p_copy_entity_txn_id
2437: and table_alias = p_business_area;
2438: hr_utility.set_location('ctrl_rec_cer is'||l_ctrl_rec_cer_id,50);
2439: exception
2445: raise;
2446: end;
2447:
2448: begin
2449: update ben_copy_entity_results
2450: set information100 = 'Y'
2451: ,information101 = 'Y'
2452: ,information102 = l_grd_exists
2453: ,information103 = l_step_exists
2478: get_table_route_details(p_table_alias => l_table_alias,
2479: p_table_route_id => l_table_route_id,
2480: p_table_name => l_table_route_name);
2481: -- create a row for the control record with the values of flags for this txn.
2482: ben_copy_entity_results_api.create_copy_entity_results
2483: (p_effective_date => p_effective_date
2484: ,p_copy_entity_txn_id => p_copy_entity_txn_id
2485: ,p_result_type_cd => 'DISPLAY'
2486: ,p_table_name => l_table_route_name
2519: get_table_route_details(p_table_alias => l_table_alias,
2520: p_table_route_id => l_table_route_id,
2521: p_table_name => l_table_route_name);
2522: -- create a row for the control record with the values of flags for this txn.
2523: ben_copy_entity_results_api.create_copy_entity_results
2524: (p_effective_date => p_effective_date
2525: ,p_copy_entity_txn_id => p_copy_entity_txn_id
2526: ,p_result_type_cd => 'DISPLAY'
2527: ,p_table_name => l_table_route_name
3065: and lf_evt_oper_cd ='SYNC';
3066: Cursor csr_rate_st_dt (p_ler_id in number)
3067: is
3068: select information17
3069: from ben_copy_entity_results
3070: where table_alias = 'LEN'
3071: and information257 = p_ler_id
3072: and copy_entity_txn_id = p_copy_entity_txn_id;
3073: begin
3074: hr_utility.set_location('inside pgm update ',10);
3075: begin
3076: select information16,information14,information4
3077: into l_sal_update_cd,l_dflt_step_cd,l_business_group_id
3078: from ben_copy_entity_results
3079: where copy_entity_result_id = p_pgm_cer_id;
3080: open csr_ler_id(l_business_group_id);
3081: fetch csr_ler_id into l_ler_id;
3082: if csr_ler_id%FOUND then
3105: l_enrt_cd := 'PQH_GSP_GSP';
3106: end if;
3107: hr_utility.set_location('prog_style is '||l_enrt_cd,35);
3108: begin
3109: update ben_copy_entity_results
3110: set information_category = 'GRADE_LADDER',
3111: information16 = l_sal_upd_flag,
3112: information51 = l_enrt_cd,
3113: information14 = l_dflt_step_cd,
3182: if l_pl_cer_id is null and l_opt_cer_id is null then
3183: hr_utility.set_location('rate is for something else',100);
3184: else
3185: begin
3186: update ben_copy_entity_results
3187: set information277 = l_pl_cer_id,
3188: information278 = l_opt_cer_id,
3189: gs_mirror_src_entity_result_id = mirror_src_entity_result_id
3190: where copy_entity_result_id = p_abr_cer_id;
3209: p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
3210: p_business_group_id in number) is
3211: cursor csr_grd_scales is
3212: select gs.parent_spine_id scale_id,gs.grade_id grade_id
3213: from ben_copy_entity_results cer, per_grade_spines_f gs
3214: where copy_entity_txn_id = p_copy_entity_txn_id
3215: and cer.information294 = gs.grade_id
3216: and table_alias = 'PLN'
3217: and p_effective_date between gs.effective_start_date and gs.effective_end_date
3248: hr_utility.set_location('pgm id is'||p_pgm_id,10);
3249: if p_pgm_id is not null then
3250: for i in c1 loop
3251: l_old_flag := 'N';
3252: BEN_COPY_ENTITY_RESULTS_API.CREATE_COPY_ENTITY_RESULTS(
3253: p_copy_entity_result_id => l_copy_entity_result_id
3254: ,p_copy_entity_txn_id => p_copy_entity_txn_id
3255: ,p_result_type_cd => 'DISPLAY'
3256: ,p_information_category => 'PQH_GSP_EXTRA_INFO'
3266: ,p_dml_operation => 'UPDATE'
3267: );
3268: end loop;
3269: if l_old_flag = 'Y' then
3270: BEN_COPY_ENTITY_RESULTS_API.CREATE_COPY_ENTITY_RESULTS(
3271: p_copy_entity_result_id => l_copy_entity_result_id
3272: ,p_copy_entity_txn_id => p_copy_entity_txn_id
3273: ,p_result_type_cd => 'DISPLAY'
3274: ,p_information_category => 'PQH_GSP_EXTRA_INFO'
3314: l_proc varchar2(61) := 'hr_to_stage' ;
3315: cursor csr_txn_cer is
3316: select copy_entity_result_id,table_alias,information1,information5,information253,
3317: result_type_cd,parent_entity_result_id,information261,mirror_src_entity_result_id
3318: from ben_copy_entity_results
3319: where copy_entity_txn_id = p_copy_entity_txn_id
3320: and ((table_alias in ('PGM','PLN','OPT','CPP','COP','EPA','CEP') and result_type_cd ='DISPLAY')
3321: or table_alias in ('ABR','AVR','ELP','VPF','VEP'))
3322: and copy_entity_result_id > nvl(p_start_cer_id,0)
3523: end if;
3524: end if;
3525: if p_opt_cer_id is not null then
3526: begin
3527: update ben_copy_entity_results set
3528: information98 = nvl(l_spinal_point,information98),
3529: information253 = nvl(l_point_seq,information253),
3530: information254 = nvl(l_point_ovn,information254),
3531: information255 = nvl(l_scale_id,information255),
3550: p_effective_date in date) is
3551: --
3552: -- this routine will be getting called when the data is to be loaded into staging area
3553: -- ben routine will be calling this routine.
3554: -- Data will be written into ben_copy_entity_results row provided for this purpose
3555: --
3556: l_proc varchar2(61) :='Grade_to_stage';
3557: l_date_from date;
3558: l_date_to date;
3575: if p_pl_cer_id is not null then
3576: -- update plan row with Grade data
3577: begin
3578: -- These mappings have been taken from hrben_to_stage document ben_pl_f addition columns section.
3579: update ben_copy_entity_results set
3580: information5 = nvl(l_grade_name,information5), -- plan name is overridden by Grade Name
3581: information102 = nvl(l_short_name,information102),
3582: information221 = nvl(l_grade_definition_id,information221),
3583: information222 = nvl(l_grd_ovn,information222),
3642: p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
3643: --
3644: -- this routine will be getting called when the data is to be loaded into staging area
3645: -- ben routine will be calling this routine.
3646: -- Data will be written into ben_copy_entity_results row provided for this purpose
3647: --
3648: l_proc varchar2(61) :='grade_to_plip_stage';
3649: l_date_from date;
3650: l_date_to date;
3730: if p_plip_cer_id is not null then
3731: begin
3732: hr_utility.set_location('going for update ',40);
3733: hr_utility.set_location('starting step is'||l_starting_step,370);
3734: update ben_copy_entity_results set
3735: information5 = nvl(l_grade_name,information5), -- plip name is same as plan name and overridden
3736: information306 = nvl(l_date_from,information306),
3737: information307 = nvl(l_date_to,information307),
3738: information253 = nvl(p_grade_id,information253),
3829: end if;
3830: if p_oipl_cer_id is not null then
3831: hr_utility.set_location('going for update '||l_proc,20);
3832: begin
3833: update ben_copy_entity_results set
3834: information253 = nvl(l_step_id, information253),
3835: information255 = nvl(l_grade_spine_id,information255),
3836: information256 = nvl(l_point_id,information256),
3837: information262 = nvl(l_point_cer_id,information262),
3877: l_check varchar2(30);
3878: begin
3879: select 'x'
3880: into l_check
3881: from ben_copy_entity_results
3882: where copy_entity_txn_id = p_copy_entity_txn_id
3883: and table_alias ='HRRATE'
3884: and information299 = p_abr_id;
3885: if sql%found then
3955: if l_continue then
3956: begin
3957: -- These mappings have been taken from hrben_to_stage document
3958: -- call to create ben_cer is made here using api.
3959: ben_copy_entity_results_api.create_copy_entity_results
3960: (p_effective_date => p_effective_date
3961: ,p_copy_entity_txn_id => p_copy_entity_txn_id
3962: ,p_result_type_cd => 'DISPLAY'
3963: ,p_table_name => l_hrr_tr_name
4042: if l_continue then
4043: begin
4044: -- These mappings have been taken from hrben_to_stage document
4045: -- call to create ben_cer is made here using api.
4046: ben_copy_entity_results_api.create_copy_entity_results
4047: (p_effective_date => p_effective_date
4048: ,p_copy_entity_txn_id => p_copy_entity_txn_id
4049: ,p_result_type_cd => 'DISPLAY'
4050: ,p_table_name => l_hrr_tr_name
4121: if l_continue then
4122: begin
4123: -- These mappings have been taken from hrben_to_stage document
4124: -- call to create ben_cer is made here using api.
4125: ben_copy_entity_results_api.create_copy_entity_results
4126: (p_effective_date => p_effective_date
4127: ,p_copy_entity_txn_id => p_copy_entity_txn_id
4128: ,p_result_type_cd => 'DISPLAY'
4129: ,p_table_name => l_abr_tr_name
4185: p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
4186: p_scale_cer_id out nocopy number) is
4187: --
4188: -- this routine will be getting called when the scale is selected for update or for attaching.
4189: -- Data will be written into ben_copy_entity_results row provided for this purpose
4190: --
4191: l_proc varchar2(61) :='Scale_to_stage';
4192: l_scale_name per_parent_spines.name%type;
4193: l_scale_ovn number;
4194: l_increment_frequency per_parent_spines.increment_frequency%type;
4195: l_bg number;
4196: l_increment_period per_parent_spines.increment_period%type;
4197: l_scl_tr_id number;
4198: l_scl_tr_name ben_copy_entity_results.table_name%type;
4199: l_scl_cer_ovn number;
4200: l_information_category varchar2(30);
4201: l_information1 varchar2(150);
4202: l_information2 varchar2(150);
4245: -- create scale row in staging area
4246: begin
4247: -- These mappings have been taken from hrben_to_stage document
4248: -- call to create ben_cer is made here using api.
4249: ben_copy_entity_results_api.create_copy_entity_results
4250: (p_effective_date => p_effective_date
4251: ,p_copy_entity_txn_id => p_copy_entity_txn_id
4252: ,p_result_type_cd => 'DISPLAY'
4253: ,p_table_name => l_scl_tr_name
4280: l_max_cer_id number;
4281: begin
4282: select max(copy_entity_result_id)
4283: into l_max_cer_id
4284: from ben_copy_entity_results
4285: where copy_entity_txn_id = p_copy_entity_txn_id;
4286: hr_utility.set_location('max cer is'||l_max_cer_id,10);
4287: return l_max_cer_id;
4288: exception
4295: from pqh_table_route
4296: where from_clause ='OAB';
4297: begin
4298: for i in tr loop
4299: update ben_copy_entity_results
4300: set table_alias = nvl(table_alias,i.table_alias)
4301: , table_name = nvl(table_name,substr(i.display_name,1,30))
4302: where copy_entity_txn_id = p_copy_entity_txn_id
4303: and table_route_id = i.table_route_id;
4337: p_user_id in number,
4338: p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
4339: p_copy_entity_txn_id out nocopy number ) is
4340: cursor csr_txns is select txn.copy_entity_txn_id
4341: from pqh_copy_entity_txns txn, ben_copy_entity_results cer
4342: where txn.context_business_group_id = p_business_group_id
4343: and txn.status ='VIEW'
4344: and txn.context ='GSP'
4345: and cer.copy_entity_txn_id = txn.copy_entity_txn_id
4408: p_user_id in number,
4409: p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
4410: p_copy_entity_txn_id out nocopy number ) is
4411: cursor csr_txns is select txn.copy_entity_txn_id
4412: from pqh_copy_entity_txns txn, ben_copy_entity_results cer,
4413: pqh_copy_entity_attribs attr
4414: where txn.context_business_group_id = p_business_group_id
4415: and attr.copy_entity_txn_id = txn.copy_entity_txn_id
4416: --changes for bug no 6030246 starts here
4577: p_scale_id in number) return number is
4578: l_scl_cer_id number;
4579: cursor csr_scale is
4580: select copy_entity_result_id
4581: from ben_copy_entity_results
4582: where copy_entity_txn_id = p_copy_entity_txn_id
4583: and table_alias ='SCALE'
4584: and information1 = p_scale_id;
4585: begin
4605: p_grd_id in number) return number is
4606: l_grd_cer_id number;
4607: cursor csr_grd is
4608: select copy_entity_result_id
4609: from ben_copy_entity_results
4610: where copy_entity_txn_id = p_copy_entity_txn_id
4611: and table_alias ='PLN'
4612: and information223 = p_grd_id;
4613: begin
4638: hr_utility.set_location('grd'||p_grade_id,15);
4639: if p_grade_id is not null then
4640: select copy_entity_result_id
4641: into l_plip_cer_id
4642: from ben_copy_entity_results
4643: where copy_entity_txn_id = p_copy_entity_txn_id
4644: and table_alias ='CPP'
4645: and information253 = p_grade_id;
4646: else
4645: and information253 = p_grade_id;
4646: else
4647: select copy_entity_result_id
4648: into l_plip_cer_id
4649: from ben_copy_entity_results
4650: where copy_entity_txn_id = p_copy_entity_txn_id
4651: and table_alias ='CPP'
4652: and information252 = p_pl_cer_id;
4653: end if;
4673: p_pl_id in number) return number is
4674: l_pl_cer_id number;
4675: cursor csr_pln is
4676: select copy_entity_result_id
4677: from ben_copy_entity_results
4678: where copy_entity_txn_id = p_copy_entity_txn_id
4679: and table_alias ='PLN'
4680: and information1 = p_pl_id;
4681: begin
4702: p_ep_id in number) return number is
4703: l_ep_cer_id number;
4704: cursor csr_epn is
4705: select copy_entity_result_id
4706: from ben_copy_entity_results
4707: where copy_entity_txn_id = p_copy_entity_txn_id
4708: and table_alias ='ELP'
4709: and copy_entity_result_id > p_start_cer
4710: and (information1 = p_ep_id OR information101 = p_ep_id) ;
4731: p_opt_id in number) return number is
4732: l_opt_cer_id number;
4733: cursor csr_opt is
4734: select copy_entity_result_id
4735: from ben_copy_entity_results
4736: where copy_entity_txn_id = p_copy_entity_txn_id
4737: and table_alias ='OPT'
4738: and information1 = p_opt_id;
4739: begin
4758: p_point_id in number) return number is
4759: l_pt_cer_id number;
4760: cursor csr_opt is
4761: select copy_entity_result_id
4762: from ben_copy_entity_results
4763: where copy_entity_txn_id = p_copy_entity_txn_id
4764: and table_alias ='OPT'
4765: and information257 = p_point_id;
4766: begin
4787: p_pl_id in number) return number is
4788: l_oipl_cer_id number;
4789: cursor csr_oipl is
4790: select copy_entity_result_id
4791: from ben_copy_entity_results
4792: where copy_entity_txn_id = p_copy_entity_txn_id
4793: and table_alias ='COP'
4794: and information253 = p_step_id;
4795: begin
4798: hr_utility.set_location('checking oipl ofr opt '||p_option_id,10);
4799: hr_utility.set_location('checking oipl for pl '||p_pl_id,12);
4800: select copy_entity_result_id
4801: into l_oipl_cer_id
4802: from ben_copy_entity_results
4803: where copy_entity_txn_id = p_copy_entity_txn_id
4804: and table_alias ='COP'
4805: and information247 = p_option_id
4806: and information261 = p_pl_id;
4992: end if;
4993: else
4994: hr_utility.set_location('pl cer is updating with plip'||p_in_pl_cer_id,48);
4995: begin
4996: update ben_copy_entity_results
4997: set gs_mirror_src_entity_result_id = p_plip_cer_id,
4998: mirror_src_entity_result_id = p_plip_cer_id,
4999: information104 = ''
5000: where copy_entity_result_id = p_in_pl_cer_id;
5115: p_ceiling_flag => l_ceiling_flag,
5116: p_point_value => l_point_value,
5117: p_scale_id => l_scale_id);
5118: begin
5119: ben_copy_entity_results_api.create_copy_entity_results(
5120: p_effective_date => p_effective_date
5121: ,p_copy_entity_txn_id => p_copy_entity_txn_id
5122: ,p_gs_parent_entity_result_id => p_plip_cer_id
5123: ,p_parent_entity_result_id => p_plip_cer_id
5143: ,p_information260 => l_scale_id
5144: ,p_copy_entity_result_id => p_oipl_cer_id
5145: ,p_object_version_number => l_oipl_cer_ovn);
5146: if l_ceiling_flag = 'Y' then
5147: update ben_copy_entity_results
5148: set information262 = p_oipl_cer_id,
5149: information259 = l_step_id
5150: where copy_entity_result_id = p_plip_cer_id;
5151: end if;
5227: hr_utility.set_location('pl cer id is'||p_pl_cer_id,16);
5228: hr_utility.set_location('plip cer id is'||p_plip_cer_id,17);
5229: if p_pl_cer_id is not null then
5230: begin
5231: update ben_copy_entity_results
5232: set gs_mirror_src_entity_result_id = p_plip_cer_id,
5233: mirror_src_entity_result_id = p_plip_cer_id
5234: where copy_entity_result_id = p_pl_cer_id;
5235: exception
5339: -- attribute FUTURE_DATA_EXISTS properly set so that they can properly
5340: -- set the datetrack_mode and dml_operation
5341: -- The following code is copied from BEN_PDW_COPY_BEN_TO_STG.mark_future_data_exists
5342:
5343: update ben_copy_entity_results a
5344: set future_data_exists ='Y'
5345: where a.copy_entity_txn_id = p_copy_entity_txn_id
5346: and a.future_data_exists is null
5347: and a.information3 < to_date('4712/12/31','YYYY/MM/DD')
5345: where a.copy_entity_txn_id = p_copy_entity_txn_id
5346: and a.future_data_exists is null
5347: and a.information3 < to_date('4712/12/31','YYYY/MM/DD')
5348: and exists
5349: ( select 'Y' from ben_copy_entity_results b
5350: where b.copy_entity_txn_id = a.copy_entity_txn_id
5351: and b.table_alias = a.table_alias
5352: and b.information1 = a.information1
5353: and b.information2 = a.information3+1);
5384: p_grd_ovn => l_grd_ovn,
5385: p_grade_definition_id => l_grade_definition_id);
5386: end if;
5387: begin
5388: ben_copy_entity_results_api.create_copy_entity_results(
5389: p_effective_date => p_effective_date
5390: ,p_copy_entity_txn_id => p_copy_entity_txn_id
5391: ,p_gs_mr_src_entity_result_id => p_plip_cer_id
5392: ,p_mirror_src_entity_result_id => p_plip_cer_id
5469: p_information5 => l_information5);
5470: end if;
5471: end if;
5472: begin
5473: ben_copy_entity_results_api.create_copy_entity_results(
5474: p_effective_date => p_effective_date
5475: ,p_copy_entity_txn_id => p_copy_entity_txn_id
5476: ,p_result_type_cd => 'DISPLAY'
5477: ,p_table_name => l_opt_tr_name
5541: select spinal_point_id
5542: from per_spinal_points
5543: where parent_spine_id = p_scale_id
5544: and not exists (select 1 from
5545: ben_copy_entity_results
5546: where copy_entity_txn_id = p_copy_entity_txn_id
5547: and table_alias ='OPT'
5548: and information257 = spinal_point_id) ;
5549: --bug 6277443
5834:
5835: Cursor csr_pgm_details
5836: is
5837: select information70 calc_method,information51 prog_style
5838: from ben_copy_entity_results
5839: where copy_entity_txn_id = p_copy_entity_txn_id
5840: and table_alias = 'PGM'
5841: and result_type_cd = 'DISPLAY';
5842:
5842:
5843: Cursor csr_grd_details
5844: is
5845: select copy_entity_result_id
5846: from ben_copy_entity_results
5847: where copy_entity_txn_id = p_copy_entity_txn_id
5848: AND table_alias = 'PLN'
5849: AND result_type_cd ='DISPLAY';
5850:
5850:
5851: Cursor csr_opt_details
5852: is
5853: select copy_entity_result_id
5854: from ben_copy_entity_results
5855: where copy_entity_txn_id = p_copy_entity_txn_id
5856: AND table_alias = 'OPT';
5857:
5858: l_calc_method varchar2(240);