[Home] [Help]
11: -- if no row doesnot exist, then we have to create it
12: -- make sure ptip row also exists else, we have to create it
13: -- do we need row in ben_opt_typ_pl_typ for plan copy
14: begin
15: update ben_copy_entity_results
16: set information248 = p_pt_id
17: where copy_entity_txn_id = p_copy_entity_txn_id
18: and table_alias = 'PLN';
19: exception
321:
322: Cursor csr_Del_Oipl (P_Plip_Cer_Id IN Number) is
323: Select information253 Step_id,
324: information254 Step_Ovn
325: From ben_copy_entity_results
326: Where copy_entity_txn_id = p_copy_entity_txn_id
327: And Gs_parent_entity_result_id = P_Plip_Cer_Id
328: And table_alias = 'COP'
329: And dml_operation = 'DELETE';
342: Select Copy_Entity_Result_id,
343: Information253 Grade_Id,
344: Information255 Scale_Id,
345: Information258 Scale_Cer_Id
346: from Ben_Copy_Entity_Results Cpp
347: Where Copy_Entity_Txn_id = P_Copy_Entity_Txn_Id
348: and Table_Alias = 'CPP'
349: and Result_type_Cd = 'DISPLAY'
350: and Exists
348: and Table_Alias = 'CPP'
349: and Result_type_Cd = 'DISPLAY'
350: and Exists
351: (Select 1
352: from Ben_Copy_Entity_Results
353: Where Copy_Entity_txn_id = P_Copy_Entity_Txn_id
354: and Gs_parent_Entity_Result_id = CPP.Copy_Entity_Result_id
355: and Table_Alias = 'COP'
356: and Dml_Operation = 'DELETE');
365: and Effective_End_Date;
366:
367: Cursor Csr_Del_Scle (P_Scale_Cer_id IN Number) is
368: Select 'N'
369: From Ben_Copy_Entity_Results Opt
370: Where Opt.Copy_Entity_txn_id = P_Copy_Entity_txn_id
371: and Opt.Table_Alias = 'OPT'
372: and Information256 = P_Scale_Cer_Id
373: and Nvl(Dml_Operation,'XX') <> 'DELETE';
524:
525:
526: Cursor Csr_Pay_Rts is
527: Select Distinct Information293 Rt_Id
528: From Ben_Copy_Entity_results
529: Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
530: and Table_Alias = 'HRRATE'
531: and Dml_operation = 'DELETE';
532:
534: Select Information1,
535: Information2,
536: Information3,
537: Information298
538: From Ben_Copy_Entity_results
539: Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
540: and Table_Alias = 'HRRATE'
541: and Dml_operation = 'DELETE'
542: and Information293 = P_Rat_Name_Id;
546: From Pay_rates
547: Where Rate_Id = P_Rate_Id
548: and Not Exists
549: (Select 1
550: from Ben_Copy_Entity_results
551: Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
552: and Table_Alias = 'HRRATE'
553: and Nvl(Dml_operation,'XX') <> 'DELETE'
554: and Information293 = P_Rate_Id);
619: information2 effective_start_date,
620: information3 effective_end_date,
621: information4 business_group_id,
622: information265 opt_ovn
623: From ben_copy_entity_results
624: Where copy_entity_txn_id = p_copy_entity_txn_id
625: And table_alias = 'OPT'
626: And dml_operation ='DELETE';
627: -- And information104 = 'UNLINK';
794: l_grd_payrate_id number;
795: l_scl_payrate_id number;
796: cursor c1 is
797: select copy_entity_result_id,information1,information98
798: from ben_copy_entity_results Scl
799: where copy_entity_txn_id = p_copy_entity_txn_id
800: and table_alias = 'SCALE'
801: and Not Exists
802: (Select 1
799: where copy_entity_txn_id = p_copy_entity_txn_id
800: and table_alias = 'SCALE'
801: and Not Exists
802: (Select 1
803: from ben_copy_entity_results
804: where copy_entity_txn_id = p_copy_entity_txn_id
805: and table_alias = 'OPT'
806: and (information256 = Scl.Copy_Entity_Result_Id or information255 = scl.Information1)
807: and Dml_Operation = 'DELETE')
806: and (information256 = Scl.Copy_Entity_Result_Id or information255 = scl.Information1)
807: and Dml_Operation = 'DELETE')
808: and Exists
809: (Select 1
810: from Ben_Copy_Entity_Results
811: Where copy_entity_txn_id = p_copy_entity_txn_id
812: and table_alias = 'HRRATE'
813: and dml_operation in ('INSERT','UPDATE'));
814:
813: and dml_operation in ('INSERT','UPDATE'));
814:
815: cursor c2(p_scl_cer_id number,p_scale_id number) is
816: select copy_entity_result_id,information1
817: from ben_copy_entity_results
818: where copy_entity_txn_id = p_copy_entity_txn_id
819: and table_alias = 'OPT'
820: and (information256 = p_scl_cer_id or information255 = p_scale_id);
821: l_ovn number;
836: p_ovn => l_ovn);
837: end if;
838: hr_utility.set_location('grd payrate is:'||l_grd_payrate_id,30);
839: begin
840: update ben_copy_entity_results
841: set information293 = l_grd_payrate_id
842: where copy_entity_txn_id = p_copy_entity_txn_id
843: and table_alias = 'HRRATE'
844: and information277 is not null
869: for j in c2(i.copy_entity_result_id, i.information1) loop
870: -- get all the points for the scale
871: hr_utility.set_location('pt cer is:'||j.copy_entity_result_id,28);
872: begin
873: update ben_copy_entity_results
874: set information293 = l_scl_payrate_id
875: where copy_entity_txn_id = p_copy_entity_txn_id
876: and table_alias = 'HRRATE'
877: and information278 = j.copy_entity_result_id
896: hr_utility.set_location('pt writeback start for pt :'||p_point_id,10);
897: hr_utility.set_location('pt cer:'||p_point_cer_id,10);
898: begin
899: -- opt row is updated with point id
900: update ben_copy_entity_results
901: set information257 = p_point_id
902: where copy_entity_txn_id = p_copy_entity_txn_id
903: and table_alias ='OPT'
904: and copy_entity_result_id = p_point_cer_id;
909: raise;
910: end;
911: begin
912: -- oipl rows are to be updated with point id
913: update ben_copy_entity_results
914: set information256 = p_point_id
915: where table_alias = 'COP'
916: and copy_entity_txn_id = p_copy_entity_txn_id
917: and information262 = p_point_cer_id;
922: raise;
923: end;
924: begin
925: -- hrrate rows are to be updated with point id
926: update ben_copy_entity_results
927: set information276 = p_point_id
928: where table_alias = 'HRRATE'
929: and copy_entity_txn_id = p_copy_entity_txn_id
930: and information278 = p_point_cer_id;
959: raise;
960: end;
961: begin
962: -- oipl row is updated with step id
963: update ben_copy_entity_results
964: set information253 = p_step_id,
965: information1 = nvl(information1,l_oipl_id)
966: where copy_entity_txn_id = p_copy_entity_txn_id
967: and table_alias ='COP'
979: p_hrrate_id in number) is
980: begin
981: begin
982: -- abr row is updated with grade spine id
983: update ben_copy_entity_results
984: set information266 = p_hrrate_id
985: where copy_entity_txn_id = p_copy_entity_txn_id
986: and table_alias ='ABR'
987: and (information277 is null or information277 = p_grade_cer_id)
998: p_copy_entity_txn_id in number) is
999: begin
1000: begin
1001: -- oipl row is updated with grade spine id
1002: update ben_copy_entity_results
1003: set information280 = p_grade_spine_id
1004: where copy_entity_txn_id = p_copy_entity_txn_id
1005: and table_alias ='CPP'
1006: and copy_entity_result_id = p_plip_cer_id;
1011: raise;
1012: end;
1013: begin
1014: -- oipl row is updated with grade spine id
1015: update ben_copy_entity_results
1016: set information255 = p_grade_spine_id
1017: where copy_entity_txn_id = p_copy_entity_txn_id
1018: and table_alias ='COP'
1019: and gs_parent_entity_result_id = p_plip_cer_id;
1029: p_scale_cer_id in number) is
1030: begin
1031: begin
1032: -- scale row is updated with Scale id
1033: update ben_copy_entity_results
1034: set information1 = p_scale_id
1035: where copy_entity_txn_id = p_copy_entity_txn_id
1036: and table_alias ='SCALE'
1037: and copy_entity_result_id = p_scale_cer_id;
1042: raise;
1043: end;
1044: begin
1045: -- plip row is updated with Scale id
1046: update ben_copy_entity_results
1047: set information255 = p_scale_id
1048: where copy_entity_txn_id = p_copy_entity_txn_id
1049: and table_alias ='CPP'
1050: and information258 = p_scale_cer_id;
1055: raise;
1056: end;
1057: begin
1058: -- opt rows are to be updated with Scale id
1059: update ben_copy_entity_results
1060: set information255 = p_scale_id
1061: where table_alias = 'OPT'
1062: and copy_entity_txn_id = p_copy_entity_txn_id
1063: and information256 = p_scale_cer_id;
1068: raise;
1069: end;
1070: begin
1071: -- oipl rows are to be updated with Scale id
1072: update ben_copy_entity_results
1073: set information260 = p_scale_id
1074: where table_alias = 'COP'
1075: and copy_entity_txn_id = p_copy_entity_txn_id
1076: and information259 = p_scale_cer_id;
1088: hr_utility.set_location('writing back grd '||p_grade_id,10);
1089: hr_utility.set_location('writing back grdcer '||p_grade_cer_id,10);
1090: begin
1091: -- plip row is updated with Grade id
1092: update ben_copy_entity_results
1093: set information253 = p_grade_id
1094: where copy_entity_txn_id = p_copy_entity_txn_id
1095: and table_alias = 'CPP'
1096: and information252 = p_grade_cer_id;
1101: raise;
1102: end;
1103: begin
1104: -- plan row is updated with Grade id
1105: update ben_copy_entity_results
1106: set information223 = p_grade_id,
1107: information294 = p_grade_id
1108: where copy_entity_result_id = p_grade_cer_id;
1109: hr_utility.set_location('num of pl updated'||sql%rowcount,20);
1113: raise;
1114: end;
1115: begin
1116: -- hrrate row is to be updated with Grade id
1117: update ben_copy_entity_results
1118: set information255 = p_grade_id
1119: where table_alias = 'HRRATE'
1120: and copy_entity_txn_id = p_copy_entity_txn_id
1121: and information277 = p_grade_cer_id;
1196: raise;
1197: end;
1198: hr_utility.set_location('effective date set',1);
1199: begin
1200: update ben_copy_entity_results
1201: set dml_operation = 'DELETE'
1202: where copy_entity_txn_id = p_copy_entity_txn_id
1203: and table_alias in ('COP','OPT','CPP','HRRATE','ABR')
1204: and information104 = 'UNLINK';
1323: begin
1324: hr_utility.set_location('going for pgm datapull',20);
1325: select information50,information41,substr(information5,1,80)
1326: into l_gl_currency,l_gl_freq,l_gl_name
1327: from ben_copy_entity_results
1328: where copy_entity_txn_id = p_copy_entity_txn_id
1329: and result_type_cd ='DISPLAY'
1330: and table_alias = 'PGM';
1331: hr_utility.set_location('curr and freq of pgm fetched',10);
1390: where copy_entity_txn_id = p_copy_entity_txn_id;
1391:
1392: -- Purging the Copy Entity Txn record as it is no longer required --
1393:
1394: Delete from Ben_Copy_Entity_Results
1395: where Copy_Entity_Txn_Id = p_copy_entity_txn_id
1396: and Table_Alias Not In ('PQH_GSP_TASK_LIST','PQH_CORPS_TASK_LIST');
1397:
1398: hr_utility.set_location('txn stat chg to comp',40);
1537: p_business_group_id in number,
1538: p_datetrack_mode in varchar2) is
1539: cursor csr_gsps is
1540: select *
1541: from ben_copy_entity_results
1542: where copy_entity_txn_id = p_copy_entity_txn_id
1543: and table_alias = 'CPP'
1544: and Dml_Operation <> 'DELETE';
1545: l_proc varchar2(61) := 'stage_to_grd_sp' ;
1567: if grd_spine.information255 is null and grd_spine.information258 is not null then
1568: begin
1569: select information1
1570: into l_scale_id
1571: from ben_copy_entity_results
1572: where copy_entity_result_id = grd_spine.information258;
1573: exception
1574: when others then
1575: hr_utility.set_location('scale was created but deleted',10);
1579: end if;
1580: if grd_spine.information253 is null and grd_spine.information252 is not null then
1581: select information223
1582: into l_grade_id
1583: from ben_copy_entity_results
1584: where copy_entity_result_id = grd_spine.information252;
1585: else
1586: l_grade_id := grd_spine.information253;
1587: end if;
1778: l_object varchar2(80);
1779: l_message_text varchar2(2000);
1780: cursor csr_points is
1781: select *
1782: from ben_copy_entity_results
1783: where copy_entity_txn_id = p_copy_entity_txn_id
1784: and table_alias = 'OPT'
1785: and dml_operation in ('INSERT','UPDATE','UPD_INS')
1786: order by information253 desc; -- do highest seq. first
1792: if point_rec.information255 is null and point_rec.information256 is not null then
1793: begin
1794: select information1
1795: into l_scale_id
1796: from ben_copy_entity_results
1797: where copy_entity_result_id = point_rec.information256;
1798: exception
1799: when others then
1800: hr_utility.set_location('scale created but removed',10);
1922: l_concat_segments varchar2(600);
1923: l_message_text varchar2(2000);
1924: cursor csr_grades is
1925: select *
1926: from ben_copy_entity_results
1927: where copy_entity_txn_id = p_copy_entity_txn_id
1928: and table_alias = 'PLN'
1929: and dml_operation in ('INSERT','UPDATE','UPD_INS');
1930: begin
2053: l_message_text varchar2(2000);
2054: l_grd_effstdt date; --DN code for BugId: 3242976
2055: cursor csr_steps is
2056: select *
2057: from ben_copy_entity_results
2058: where copy_entity_txn_id = p_copy_entity_txn_id
2059: and table_alias = 'COP'
2060: and dml_operation = 'INSERT';
2061: begin
2065: l_step_ovn := step_rec.information254;
2066: if step_rec.information255 is null then
2067: select information280
2068: into l_grd_sp_id
2069: from ben_copy_entity_results
2070: where copy_entity_result_id = step_rec.gs_parent_entity_result_id;
2071: else
2072: l_grd_sp_id := step_rec.information255;
2073: end if;
2073: end if;
2074: if step_rec.information256 is null and step_rec.information262 is not null then
2075: select information257
2076: into l_point_id
2077: from ben_copy_entity_results
2078: where copy_entity_result_id = step_rec.information262;
2079: else
2080: l_point_id := step_rec.information256;
2081: end if;
2177: l_db_ovn number;
2178: l_message_text varchar2(2000);
2179: cursor csr_scales is
2180: select *
2181: from ben_copy_entity_results
2182: where copy_entity_txn_id = p_copy_entity_txn_id
2183: and table_alias ='SCALE'
2184: and dml_operation in ('INSERT','UPDATE');
2185: begin
2326: l_message_text varchar2(2000);
2327: l_dml_operation varchar2(30);
2328: cursor csr_hrr is
2329: select *
2330: from ben_copy_entity_results
2331: where copy_entity_txn_id = p_copy_entity_txn_id
2332: and table_alias = 'HRRATE'
2333: and dml_operation in ('INSERT','UPDATE')
2334: order by INFORMATION277,INFORMATION278,INFORMATION2;
2373: hr_utility.set_location('going for getting pl_id ',3);
2374: begin
2375: select information223
2376: into l_grd_sp_id
2377: from ben_copy_entity_results
2378: where copy_entity_result_id = hrr_rec.INFORMATION277;
2379: exception
2380: when others then
2381: l_grd_sp_id := null;
2391: hr_utility.set_location('going for getting opt_id ',3);
2392: begin
2393: select information257
2394: into l_grd_sp_id
2395: from ben_copy_entity_results
2396: where copy_entity_result_id = hrr_rec.INFORMATION278;
2397: exception
2398: when others then
2399: l_grd_sp_id := null;