[Home] [Help]
1: package body pqh_gsp_rates as
2: /* $Header: pqgsprat.pkb 120.3.12010000.1 2008/07/28 12:57:52 appldev ship $ */
3: procedure delete_grrate(p_copy_entity_txn_id in number) is
4: begin
5: delete from ben_copy_entity_results
6: where copy_entity_txn_id = p_copy_entity_txn_id
7: and table_alias ='GRRATE';
8: exception
9: when others then
11: raise;
12: end delete_grrate;
13: procedure delete_gsrate(p_copy_entity_txn_id in number) is
14: begin
15: delete from ben_copy_entity_results
16: where copy_entity_txn_id = p_copy_entity_txn_id
17: and table_alias ='GSRATE';
18: exception
19: when others then
34: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
35: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
36: select information293,information287
37: into p_point_value,p_point_old_value
38: from ben_copy_entity_results
39: where table_alias = 'CRRATE'
40: and copy_entity_txn_id = p_copy_entity_txn_id
41: and information169 = p_point_cer_id
42: and information160 = p_crset_id
62: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
63: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
64: select information297,information287
65: into p_point_value,p_point_old_value
66: from ben_copy_entity_results
67: where table_alias = 'HRRATE'
68: and copy_entity_txn_id = p_copy_entity_txn_id
69: and information278 = p_point_cer_id
70: and p_effective_start_date between information2 and information3
168: l_point5_value := null;
169: l_point5_old_value := null;
170: end if;
171: begin
172: ben_copy_entity_results_api.create_copy_entity_results(
173: p_effective_date => p_effective_date
174: ,p_copy_entity_txn_id => p_copy_entity_txn_id
175: ,p_result_type_cd => 'DISPLAY'
176: ,p_table_name => l_grr_tr_name
290: l_point5_value :=null;
291: l_point5_old_value :=null;
292: end if;
293: begin
294: ben_copy_entity_results_api.create_copy_entity_results(
295: p_effective_date => p_effective_date
296: ,p_copy_entity_txn_id => p_copy_entity_txn_id
297: ,p_result_type_cd => 'DISPLAY'
298: ,p_table_name => l_gsr_tr_name
410: p_business_group_id in number,
411: p_dt_matx in out nocopy t_pt_matx) is
412: cursor csr_dates is
413: select information2 start_date
414: from ben_copy_entity_results
415: where copy_entity_txn_id = p_copy_entity_txn_id
416: and result_type_cd ='DISPLAY' -- which are displayed
417: and table_alias = 'HRRATE' -- check oipl row
418: and information278 = p_point_cer_id -- check rate of point row
425: l_max_end_dt date;
426: begin
427: select count(*),min(information2),max(information3)
428: into l_num_pt_rates,l_min_st_dt,l_max_end_dt
429: from ben_copy_entity_results
430: where copy_entity_txn_id = p_copy_entity_txn_id
431: and result_type_cd ='DISPLAY' -- which are displayed
432: and table_alias = 'HRRATE' -- check hrr row
433: and information278 = p_point_cer_id;
477: p_business_group_id in number,
478: p_dt_matx in out nocopy t_pt_matx) is
479: cursor csr_dates is
480: select information2 start_date
481: from ben_copy_entity_results
482: where copy_entity_txn_id = p_copy_entity_txn_id
483: and result_type_cd ='DISPLAY' -- which are displayed
484: and table_alias = 'CRRATE' -- check oipl row
485: and information169 = p_point_cer_id -- check rate of point row
494: l_max_end_dt date;
495: begin
496: select count(*),min(information2),max(information3)
497: into l_num_pt_rates,l_min_st_dt,l_max_end_dt
498: from ben_copy_entity_results
499: where copy_entity_txn_id = p_copy_entity_txn_id
500: and result_type_cd ='DISPLAY' -- which are displayed
501: and table_alias = 'CRRATE' -- check crr row
502: and information160 = p_crset_id
562: p_dt_matx out nocopy t_pt_matx) is
563: cursor csr_grds is
564: select copy_entity_result_id,information252,information253
565: -- into p_plip_cer_id,p_grade_cer_id,p_grade_id
566: from ben_copy_entity_results
567: where copy_entity_txn_id = p_copy_entity_txn_id
568: and result_type_cd ='DISPLAY' -- which are displayed
569: and information104 = 'LINK' -- checked linked rows
570: and table_alias = 'CPP' -- check plip row
571: order by information263 ; -- for getting the order of grades correct
572: cursor csr_grd_stps(p_plip_cer_id number) is
573: select information262
574: -- into p_point_cer_id
575: from ben_copy_entity_results
576: where copy_entity_txn_id = p_copy_entity_txn_id
577: and result_type_cd ='DISPLAY' -- which are displayed
578: and information104 = 'LINK' -- checked linked rows
579: and table_alias = 'COP' -- check oipl row
592: for grd in csr_grds loop
593: hr_utility.set_location('grd_cer_id is '||grd.copy_entity_result_id,2);
594: select count(*)
595: into l_grd_num_steps
596: from ben_copy_entity_results
597: where copy_entity_txn_id = p_copy_entity_txn_id
598: and result_type_cd ='DISPLAY' -- which are displayed
599: and information104 = 'LINK' -- checked linked rows
600: and table_alias = 'COP' -- check oipl row
751: p_point_cer_id in number,
752: p_effective_date in date,
753: p_new_value in number) is
754: begin
755: update ben_copy_entity_results
756: set INFORMATION297 = p_new_value,
757: dml_operation = nvl(dml_operation,'UPDATE')
758: where information278 = p_point_cer_id
759: and table_alias ='HRRATE'
780: begin
781: begin
782: select copy_entity_result_id
783: into l_old_hrr_cer_id
784: from ben_copy_entity_results
785: where information278 = p_point_cer_id
786: and table_alias ='HRRATE'
787: and p_effective_date between information2 and information3
788: and copy_entity_txn_id = p_copy_entity_txn_id;
847: information293,information300,information298
848: into l_cet_id,l_table_alias,l_table_name,l_table_route_id,l_dml_oper,l_dt_mode,
849: l_pk,l_esd,l_eed,l_bg,l_grade_cer_id,l_point_cer_id,
850: l_payrate_id,l_abr_cer_id,l_hrr_ovn
851: from ben_copy_entity_results
852: where copy_entity_result_id = p_old_hrrate_cer_id;
853: if nvl(l_dml_oper,'REUSE') = 'REUSE' then
854: l_new_dml_oper := 'UPDATE';
855: else
890: end if;
891: if l_upd_curr ='Y' then
892: hr_utility.set_location('updating current row with values',10);
893: begin
894: update ben_copy_entity_results
895: set information294 = p_grd_min_value,
896: information295 = p_grd_max_value,
897: information296 = p_grd_mid_value,
898: information297 = p_value,
901: information3 = l_hrr_eed
902: where copy_entity_result_id = p_old_hrrate_cer_id;
903: --ggnanagu
904:
905: update ben_copy_entity_results
906: set information287 = p_value
907: where copy_entity_result_id = p_old_hrrate_cer_id
908: and nvl(information287,0) =0 ;
909:
919: end if;
920: if l_del_future = 'Y' then
921: hr_utility.set_location('deleting future rows ',10);
922: begin
923: delete from ben_copy_entity_results
924: where copy_entity_txn_id = l_cet_id
925: and table_alias = 'HRRATE'
926: and (information277 is null or information277 = l_grade_cer_id)
927: and (information278 is null or information278 = l_point_cer_id)
934: end if;
935: if l_upd_effdt = 'Y' then
936: hr_utility.set_location('updating effdt of curr row ',10);
937: begin
938: update ben_copy_entity_results
939: set information3 = l_hrr_eed
940: where copy_entity_result_id = p_old_hrrate_cer_id;
941: hr_utility.set_location('old hrrate updated '||p_old_hrrate_cer_id,10);
942: exception
949: hr_utility.set_location('inserting new row ',10);
950: begin
951: -- These mappings have been taken from hrben_to_stage document
952: -- call to create ben_cer is made here using api.
953: ben_copy_entity_results_api.create_copy_entity_results
954: (p_effective_date => p_effective_date
955: ,p_copy_entity_txn_id => l_cet_id
956: ,p_result_type_cd => 'DISPLAY'
957: ,p_table_name => l_table_name
993: begin
994: hr_utility.set_location('applying data to hgrid',10);
995: if p_pl_cer_id is not null then
996: begin
997: update ben_copy_entity_results
998: set information298 = p_value
999: where table_alias ='CPP'
1000: and information252 = p_pl_cer_id
1001: and copy_entity_txn_id = p_copy_entity_txn_id;
1005: raise;
1006: end;
1007: else
1008: begin
1009: update ben_copy_entity_results
1010: set information298 = p_value
1011: where table_alias ='COP'
1012: and information262 = p_point_cer_id
1013: and copy_entity_txn_id = p_copy_entity_txn_id;
1245: for rec in csr_grd_rate loop
1246: begin
1247: -- These mappings have been taken from hrben_to_stage document
1248: -- call to create ben_cer is made here using api.
1249: ben_copy_entity_results_api.create_copy_entity_results
1250: (p_effective_date => p_effective_date
1251: ,p_copy_entity_txn_id => p_copy_entity_txn_id
1252: ,p_result_type_cd => 'DISPLAY'
1253: ,p_table_name => l_hrr_tr_name
1338: for rec in csr_point_rate loop
1339: begin
1340: -- These mappings have been taken from hrben_to_stage document
1341: -- call to create ben_cer is made here using api.
1342: ben_copy_entity_results_api.create_copy_entity_results
1343: (p_effective_date => p_effective_date
1344: ,p_copy_entity_txn_id => p_copy_entity_txn_id
1345: ,p_result_type_cd => 'DISPLAY'
1346: ,p_table_name => l_hrr_tr_name
1380: p_point_cer_id in number default null,
1381: p_new_value in number) is
1382: cursor csr_crrate is
1383: select *
1384: from ben_copy_entity_results
1385: where copy_entity_txn_id = p_copy_entity_txn_id
1386: and table_alias = 'CRRATE'
1387: and (information230 is null or information230 = p_grade_cer_id)
1388: and (information169 is null or information169 = p_point_cer_id)
1448: end if;
1449: end if;
1450: if l_del_future = 'Y' then
1451: hr_utility.set_location('fut rows being deleted',11);
1452: delete from ben_copy_entity_results
1453: where copy_entity_txn_id = p_copy_entity_txn_id
1454: and table_alias = 'CRRATE'
1455: and information160 = p_crset_id
1456: and (information230 is null or information230 = p_grade_cer_id)
1458: and information2 > p_effective_date;
1459: end if;
1460: if l_upd_effdt ='Y' then
1461: hr_utility.set_location('effdt of curr_row being changed',12);
1462: update ben_copy_entity_results
1463: set INFORMATION3 = l_crrate_eed
1464: where copy_entity_result_id = crrate_rec.copy_entity_result_id;
1465: end if;
1466: if l_upd_curr ='Y' then
1464: where copy_entity_result_id = crrate_rec.copy_entity_result_id;
1465: end if;
1466: if l_upd_curr ='Y' then
1467: hr_utility.set_location('curr_row data being changed',13);
1468: update ben_copy_entity_results
1469: set dml_operation = l_dml_operation,
1470: INFORMATION293 = p_new_value,
1471: information3 = l_crrate_eed
1472: where copy_entity_result_id = crrate_rec.copy_entity_result_id;
1470: INFORMATION293 = p_new_value,
1471: information3 = l_crrate_eed
1472: where copy_entity_result_id = crrate_rec.copy_entity_result_id;
1473: --ggnanagu
1474: update ben_copy_entity_results
1475: set INFORMATION287 = p_new_value
1476: where copy_entity_result_id = crrate_rec.copy_entity_result_id
1477: and nvl(information287,0) =0 ;
1478: --ggnanagu
1547: if l_continue then
1548: begin
1549: -- These mappings have been taken from hrben_to_stage document
1550: -- call to create ben_cer is made here using api.
1551: ben_copy_entity_results_api.create_copy_entity_results
1552: (p_effective_date => p_effective_date
1553: ,p_copy_entity_txn_id => p_copy_entity_txn_id
1554: ,p_result_type_cd => 'DISPLAY'
1555: ,p_table_name => l_gsr_tr_name
1629: if l_continue then
1630: begin
1631: -- These mappings have been taken from hrben_to_stage document
1632: -- call to create ben_cer is made here using api.
1633: ben_copy_entity_results_api.create_copy_entity_results
1634: (p_effective_date => p_effective_date
1635: ,p_copy_entity_txn_id => p_copy_entity_txn_id
1636: ,p_result_type_cd => 'DISPLAY'
1637: ,p_table_name => l_grr_tr_name
1708: information290, information291, information228, information2, information3
1709: into l_grade_cer_id, l_point1_cer_id, l_point2_cer_id, l_point3_cer_id,
1710: l_point4_cer_id, l_point5_cer_id, l_point1_value, l_point2_value, l_point3_value,
1711: l_point4_value, l_point5_value, l_step_range, l_esd, l_eed
1712: from ben_copy_entity_results
1713: where copy_entity_result_id = p_gsr_cer_id
1714: and copy_entity_txn_id = p_copy_entity_txn_id;
1715: hr_utility.set_location('values pulled',4);
1716: if p_datetrack_mode ='CORRECTION' then
1739: end if;
1740: end if;
1741: if l_upd_curr ='Y' then
1742: -- correct the gsrate row
1743: update ben_copy_entity_results
1744: set information287 = p_value1,
1745: information288 = p_value2,
1746: information289 = p_value3,
1747: information290 = p_value4,
1751: and copy_entity_txn_id = p_copy_entity_txn_id;
1752: hr_utility.set_location('gsrate row corr',5);
1753: end if;
1754: if l_upd_effdt = 'Y' then
1755: update ben_copy_entity_results
1756: set INFORMATION3 = p_effective_date -1
1757: where copy_entity_result_id = p_gsr_cer_id
1758: and copy_entity_txn_id = p_copy_entity_txn_id;
1759: hr_utility.set_location('curr row end dt',12);
1759: hr_utility.set_location('curr row end dt',12);
1760: end if;
1761: if l_del_future = 'Y' then
1762: -- remove the future rows
1763: delete from ben_copy_entity_results
1764: where copy_entity_txn_id = p_copy_entity_txn_id
1765: and table_alias = 'GSRATE'
1766: and information160 = l_grade_cer_id
1767: and information2 > p_effective_date;
1870: information290, information291, information228, information2, information3
1871: into l_grade_cer_id, l_crset_id, l_plip_cer_id,l_point1_cer_id, l_point2_cer_id, l_point3_cer_id,
1872: l_point4_cer_id, l_point5_cer_id, l_point1_value, l_point2_value, l_point3_value,
1873: l_point4_value, l_point5_value, l_step_range, l_esd, l_eed
1874: from ben_copy_entity_results
1875: where copy_entity_result_id = p_grr_cer_id
1876: and copy_entity_txn_id = p_copy_entity_txn_id;
1877: hr_utility.set_location('values pulled',4);
1878: if p_datetrack_mode ='CORRECTION' then
1901: end if;
1902: end if;
1903: if l_upd_curr ='Y' then
1904: -- correct the grrate row
1905: update ben_copy_entity_results
1906: set information287 = p_value1,
1907: information288 = p_value2,
1908: information289 = p_value3,
1909: information290 = p_value4,
1913: and copy_entity_txn_id = p_copy_entity_txn_id;
1914: hr_utility.set_location('grrate row corr',5);
1915: end if;
1916: if l_upd_effdt = 'Y' then
1917: update ben_copy_entity_results
1918: set INFORMATION3 = p_effective_date -1
1919: where copy_entity_result_id = p_grr_cer_id
1920: and copy_entity_txn_id = p_copy_entity_txn_id;
1921: hr_utility.set_location('curr row end dt',12);
1921: hr_utility.set_location('curr row end dt',12);
1922: end if;
1923: if l_del_future = 'Y' then
1924: -- remove the future rows
1925: delete from ben_copy_entity_results
1926: where copy_entity_txn_id = p_copy_entity_txn_id
1927: and table_alias = 'GRRATE'
1928: and information160 = l_grade_cer_id
1929: and information2 > p_effective_date;
2000: p_point_cer_id in number,
2001: p_copy_entity_txn_id in number,
2002: p_value in number) is
2003: cursor csr_crr is
2004: select * from ben_copy_entity_results
2005: where copy_entity_txn_id = p_copy_entity_txn_id
2006: and information160 is null
2007: and table_alias = 'CRRATE'
2008: and information169 = p_point_cer_id;
2021: else
2022: l_dml_operation := crr_rec.dml_operation;
2023: end if;
2024: end if;
2025: update ben_copy_entity_results
2026: set information293 = p_value,
2027: information160 = p_crset_id,
2028: dml_operation = l_dml_operation
2029: where copy_entity_txn_id = p_copy_entity_txn_id
2031: and information160 is null
2032: and table_alias = 'CRRATE'
2033: and information169 = p_point_cer_id;
2034: --ggnanagu
2035: update ben_copy_entity_results
2036: set INFORMATION287 = p_value
2037: where copy_entity_result_id = crr_rec.copy_entity_result_id
2038: and nvl(information287,0) =0 ;
2039: --ggnanagu
2042: end sync_crrate;
2043: procedure sync_grrate(p_crset_id in number,
2044: p_copy_entity_txn_id in number) is
2045: cursor csr_grr is
2046: select * from ben_copy_entity_results
2047: where copy_entity_txn_id = p_copy_entity_txn_id
2048: and information161 is null
2049: and table_alias = 'GRRATE';
2050: begin
2091:
2092: Cursor csr_grades
2093: Is
2094: select copy_entity_result_id
2095: from ben_copy_entity_results
2096: where table_alias = 'PLN'
2097: and copy_entity_txn_id = p_copy_entity_txn_id;
2098:
2099:
2099:
2100: Cursor csr_grade_std_rates(p_grade_cer_id in number)
2101: IS
2102: select copy_entity_result_id,information1,information2,information3,information294,information295,information296,information297,dml_operation
2103: from ben_copy_entity_results
2104: where table_alias = 'HRRATE'
2105: and information277= p_grade_cer_id
2106: and copy_entity_txn_id = p_copy_entity_txn_id;
2107:
2107:
2108: Cursor csr_grade_cri_rates(p_grade_cer_id in number)
2109: IS
2110: select copy_entity_result_id,information1,information2,information3,information293,dml_operation
2111: from ben_copy_entity_results
2112: where table_alias = 'CRRATE'
2113: and information230= p_grade_cer_id
2114: and copy_entity_txn_id = p_copy_entity_txn_id;
2115:
2122:
2123: Cursor csr_Points
2124: Is
2125: select copy_entity_result_id
2126: from ben_copy_entity_results
2127: where table_alias = 'OPT'
2128: and copy_entity_txn_id = p_copy_entity_txn_id;
2129:
2130:
2130:
2131: Cursor csr_point_std_rates(p_point_cer_id in number)
2132: IS
2133: select copy_entity_result_id,information1,information2,information3,information297,dml_operation
2134: from ben_copy_entity_results
2135: where table_alias = 'HRRATE'
2136: and information278= p_point_cer_id
2137: and copy_entity_txn_id = p_copy_entity_txn_id;
2138:
2138:
2139: Cursor csr_point_cri_rates(p_point_cer_id in number)
2140: IS
2141: select copy_entity_result_id,information1,information2,information3,information293,dml_operation
2142: from ben_copy_entity_results
2143: where table_alias = 'CRRATE'
2144: and information169= p_point_cer_id
2145: and copy_entity_txn_id = p_copy_entity_txn_id;
2146:
2152: and effective_start_date = p_effective_start_date;
2153:
2154: Cursor csr_gsp_task_list is
2155: select copy_entity_result_id
2156: from ben_copy_entity_results
2157: where information109 is null --nvl(information109,0) = 0
2158: and table_alias = 'PQH_GSP_TASK_LIST'
2159: and copy_entity_txn_id = p_copy_entity_txn_id
2160: and rownum < 2 ;
2167: begin
2168:
2169: for rec_gsp_task_list in csr_gsp_task_list
2170: loop
2171: update ben_copy_entity_results
2172: set information109 = 'Y'
2173: where copy_entity_result_id = rec_gsp_task_list.copy_entity_result_id;
2174:
2175: for grades_rec in csr_grades loop
2175: for grades_rec in csr_grades loop
2176:
2177: for grade_std_rates_rec in csr_grade_std_rates(grades_rec.copy_entity_result_id) loop
2178: if grade_std_rates_rec.dml_operation = 'INSERT' then
2179: update ben_copy_entity_results
2180: set information287 = information297
2181: ,information288 = information294
2182: ,information289 = information295
2183: ,information290 = information296
2185: else
2186: Open csr_grd_db_values(grade_std_rates_rec.information1,grade_std_rates_rec.information2);
2187: fetch csr_grd_db_values into l_value,l_grd_min_value,l_grd_max_value,l_grd_mid_value;
2188: close csr_grd_db_values;
2189: update ben_copy_entity_results
2190: set information287 = l_value
2191: ,information288 = l_grd_min_value
2192: ,information289 = l_grd_max_value
2193: ,information290 = l_grd_mid_value
2195: end if;
2196: end loop; -- csr_grade_std_rates
2197:
2198: for grade_cri_rates_rec in csr_grade_cri_rates(grades_rec.copy_entity_result_id) loop
2199: update ben_copy_entity_results
2200: set information287 = information293
2201: where copy_entity_result_id = grade_cri_rates_rec.copy_entity_result_id;
2202: end loop; -- csr_grade_cri_rates
2203:
2206: for pnt in csr_points loop
2207:
2208: for pnt_rate in csr_point_std_rates(pnt.copy_entity_result_id) loop
2209: if pnt_rate.dml_operation = 'INSERT' then
2210: update ben_copy_entity_results
2211: set information287 = information297
2212: where copy_entity_result_id = pnt_rate.copy_entity_result_id;
2213: else
2214: Open csr_pnt_db_values(pnt_rate.information1,pnt_rate.information2);
2213: else
2214: Open csr_pnt_db_values(pnt_rate.information1,pnt_rate.information2);
2215: fetch csr_pnt_db_values into l_value;
2216: close csr_pnt_db_values;
2217: update ben_copy_entity_results
2218: set information287 = l_value
2219: where copy_entity_result_id = pnt_rate.copy_entity_result_id;
2220: end if;
2221: end loop; -- csr_point_std_rates
2220: end if;
2221: end loop; -- csr_point_std_rates
2222:
2223: for pnt_cri_rate in csr_point_cri_rates(pnt.copy_entity_result_id) loop
2224: update ben_copy_entity_results
2225: set information287 = information293
2226: where copy_entity_result_id = pnt_cri_rate.copy_entity_result_id;
2227: end loop; -- csr_point_cri_rates
2228: