[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
324:
325: Cursor csr_Del_Oipl (P_Plip_Cer_Id IN Number) is
326: Select information253 Step_id,
327: information254 Step_Ovn
328: From ben_copy_entity_results
329: Where copy_entity_txn_id = p_copy_entity_txn_id
330: And Gs_parent_entity_result_id = P_Plip_Cer_Id
331: And table_alias = 'COP'
332: And dml_operation = 'DELETE';
345: Select Copy_Entity_Result_id,
346: Information253 Grade_Id,
347: Information255 Scale_Id,
348: Information258 Scale_Cer_Id
349: from Ben_Copy_Entity_Results Cpp
350: Where Copy_Entity_Txn_id = P_Copy_Entity_Txn_Id
351: and Table_Alias = 'CPP'
352: and Result_type_Cd = 'DISPLAY'
353: and Exists
351: and Table_Alias = 'CPP'
352: and Result_type_Cd = 'DISPLAY'
353: and Exists
354: (Select 1
355: from Ben_Copy_Entity_Results
356: Where Copy_Entity_txn_id = P_Copy_Entity_Txn_id
357: and Gs_parent_Entity_Result_id = CPP.Copy_Entity_Result_id
358: and Table_Alias = 'COP'
359: and Dml_Operation = 'DELETE');
368: and Effective_End_Date;
369:
370: Cursor Csr_Del_Scle (P_Scale_Cer_id IN Number) is
371: Select 'N'
372: From Ben_Copy_Entity_Results Opt
373: Where Opt.Copy_Entity_txn_id = P_Copy_Entity_txn_id
374: and Opt.Table_Alias = 'OPT'
375: and Information256 = P_Scale_Cer_Id
376: and Nvl(Dml_Operation,'XX') <> 'DELETE';
377:
378: -- Bug7674132
379: cursor Acty_Rts is
380: select information1 Acty_Base_Rt_id
381: from ben_copy_entity_results
382: where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
383: and table_alias like 'ABR'
384: And dml_operation = 'DELETE';
385:
554:
555:
556: Cursor Csr_Pay_Rts is
557: Select Distinct Information293 Rt_Id
558: From Ben_Copy_Entity_results
559: Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
560: and Table_Alias = 'HRRATE'
561: and Dml_operation = 'DELETE';
562:
564: Select Information1,
565: Information2,
566: Information3,
567: Information298
568: From Ben_Copy_Entity_results
569: Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
570: and Table_Alias = 'HRRATE'
571: and Dml_operation = 'DELETE'
572: and Information293 = P_Rat_Name_Id;
576: From Pay_rates
577: Where Rate_Id = P_Rate_Id
578: and Not Exists
579: (Select 1
580: from Ben_Copy_Entity_results
581: Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
582: and Table_Alias = 'HRRATE'
583: and Nvl(Dml_operation,'XX') <> 'DELETE'
584: and Information293 = P_Rate_Id);
652: information3 effective_end_date,
653: information4 business_group_id,
654: information265 opt_ovn,
655: information257 point_id --bug#8392638
656: From ben_copy_entity_results
657: Where copy_entity_txn_id = p_copy_entity_txn_id
658: And table_alias = 'OPT'
659: And dml_operation ='DELETE';
660: -- And information104 = 'UNLINK';
833: l_grd_payrate_id number;
834: l_scl_payrate_id number;
835: cursor c1 is
836: select copy_entity_result_id,information1,information98
837: from ben_copy_entity_results Scl
838: where copy_entity_txn_id = p_copy_entity_txn_id
839: and table_alias = 'SCALE'
840: and Not Exists
841: (Select 1
838: where copy_entity_txn_id = p_copy_entity_txn_id
839: and table_alias = 'SCALE'
840: and Not Exists
841: (Select 1
842: from ben_copy_entity_results
843: where copy_entity_txn_id = p_copy_entity_txn_id
844: and table_alias = 'OPT'
845: and (information256 = Scl.Copy_Entity_Result_Id or information255 = scl.Information1)
846: and Dml_Operation = 'DELETE')
845: and (information256 = Scl.Copy_Entity_Result_Id or information255 = scl.Information1)
846: and Dml_Operation = 'DELETE')
847: and Exists
848: (Select 1
849: from Ben_Copy_Entity_Results
850: Where copy_entity_txn_id = p_copy_entity_txn_id
851: and table_alias = 'HRRATE'
852: and dml_operation in ('INSERT','UPDATE'));
853:
852: and dml_operation in ('INSERT','UPDATE'));
853:
854: cursor c2(p_scl_cer_id number,p_scale_id number) is
855: select copy_entity_result_id,information1
856: from ben_copy_entity_results
857: where copy_entity_txn_id = p_copy_entity_txn_id
858: and table_alias = 'OPT'
859: and (information256 = p_scl_cer_id or information255 = p_scale_id);
860: l_ovn number;
875: p_ovn => l_ovn);
876: end if;
877: hr_utility.set_location('grd payrate is:'||l_grd_payrate_id,30);
878: begin
879: update ben_copy_entity_results
880: set information293 = l_grd_payrate_id
881: where copy_entity_txn_id = p_copy_entity_txn_id
882: and table_alias = 'HRRATE'
883: and information277 is not null
908: for j in c2(i.copy_entity_result_id, i.information1) loop
909: -- get all the points for the scale
910: hr_utility.set_location('pt cer is:'||j.copy_entity_result_id,28);
911: begin
912: update ben_copy_entity_results
913: set information293 = l_scl_payrate_id
914: where copy_entity_txn_id = p_copy_entity_txn_id
915: and table_alias = 'HRRATE'
916: and information278 = j.copy_entity_result_id
935: hr_utility.set_location('pt writeback start for pt :'||p_point_id,10);
936: hr_utility.set_location('pt cer:'||p_point_cer_id,10);
937: begin
938: -- opt row is updated with point id
939: update ben_copy_entity_results
940: set information257 = p_point_id
941: where copy_entity_txn_id = p_copy_entity_txn_id
942: and table_alias ='OPT'
943: and copy_entity_result_id = p_point_cer_id;
948: raise;
949: end;
950: begin
951: -- oipl rows are to be updated with point id
952: update ben_copy_entity_results
953: set information256 = p_point_id
954: where table_alias = 'COP'
955: and copy_entity_txn_id = p_copy_entity_txn_id
956: and information262 = p_point_cer_id;
961: raise;
962: end;
963: begin
964: -- hrrate rows are to be updated with point id
965: update ben_copy_entity_results
966: set information276 = p_point_id
967: where table_alias = 'HRRATE'
968: and copy_entity_txn_id = p_copy_entity_txn_id
969: and information278 = p_point_cer_id;
998: raise;
999: end;
1000: begin
1001: -- oipl row is updated with step id
1002: update ben_copy_entity_results
1003: set information253 = p_step_id,
1004: information1 = nvl(information1,l_oipl_id)
1005: where copy_entity_txn_id = p_copy_entity_txn_id
1006: and table_alias ='COP'
1018: p_hrrate_id in number) is
1019: begin
1020: begin
1021: -- abr row is updated with grade spine id
1022: update ben_copy_entity_results
1023: set information266 = p_hrrate_id
1024: where copy_entity_txn_id = p_copy_entity_txn_id
1025: and table_alias ='ABR'
1026: and (information277 is null or information277 = p_grade_cer_id)
1037: p_copy_entity_txn_id in number) is
1038: begin
1039: begin
1040: -- oipl row is updated with grade spine id
1041: update ben_copy_entity_results
1042: set information280 = p_grade_spine_id
1043: where copy_entity_txn_id = p_copy_entity_txn_id
1044: and table_alias ='CPP'
1045: and copy_entity_result_id = p_plip_cer_id;
1050: raise;
1051: end;
1052: begin
1053: -- oipl row is updated with grade spine id
1054: update ben_copy_entity_results
1055: set information255 = p_grade_spine_id
1056: where copy_entity_txn_id = p_copy_entity_txn_id
1057: and table_alias ='COP'
1058: and gs_parent_entity_result_id = p_plip_cer_id;
1068: p_scale_cer_id in number) is
1069: begin
1070: begin
1071: -- scale row is updated with Scale id
1072: update ben_copy_entity_results
1073: set information1 = p_scale_id
1074: where copy_entity_txn_id = p_copy_entity_txn_id
1075: and table_alias ='SCALE'
1076: and copy_entity_result_id = p_scale_cer_id;
1081: raise;
1082: end;
1083: begin
1084: -- plip row is updated with Scale id
1085: update ben_copy_entity_results
1086: set information255 = p_scale_id
1087: where copy_entity_txn_id = p_copy_entity_txn_id
1088: and table_alias ='CPP'
1089: and information258 = p_scale_cer_id;
1094: raise;
1095: end;
1096: begin
1097: -- opt rows are to be updated with Scale id
1098: update ben_copy_entity_results
1099: set information255 = p_scale_id
1100: where table_alias = 'OPT'
1101: and copy_entity_txn_id = p_copy_entity_txn_id
1102: and information256 = p_scale_cer_id;
1107: raise;
1108: end;
1109: begin
1110: -- oipl rows are to be updated with Scale id
1111: update ben_copy_entity_results
1112: set information260 = p_scale_id
1113: where table_alias = 'COP'
1114: and copy_entity_txn_id = p_copy_entity_txn_id
1115: and information259 = p_scale_cer_id;
1127: hr_utility.set_location('writing back grd '||p_grade_id,10);
1128: hr_utility.set_location('writing back grdcer '||p_grade_cer_id,10);
1129: begin
1130: -- plip row is updated with Grade id
1131: update ben_copy_entity_results
1132: set information253 = p_grade_id
1133: where copy_entity_txn_id = p_copy_entity_txn_id
1134: and table_alias = 'CPP'
1135: and information252 = p_grade_cer_id;
1140: raise;
1141: end;
1142: begin
1143: -- plan row is updated with Grade id
1144: update ben_copy_entity_results
1145: set information223 = p_grade_id,
1146: information294 = p_grade_id
1147: where copy_entity_result_id = p_grade_cer_id;
1148: hr_utility.set_location('num of pl updated'||sql%rowcount,20);
1152: raise;
1153: end;
1154: begin
1155: -- hrrate row is to be updated with Grade id
1156: update ben_copy_entity_results
1157: set information255 = p_grade_id
1158: where table_alias = 'HRRATE'
1159: and copy_entity_txn_id = p_copy_entity_txn_id
1160: and information277 = p_grade_cer_id;
1235: raise;
1236: end;
1237: hr_utility.set_location('effective date set',1);
1238: begin
1239: update ben_copy_entity_results
1240: set dml_operation = 'DELETE'
1241: where copy_entity_txn_id = p_copy_entity_txn_id
1242: and table_alias in ('COP','OPT','CPP','HRRATE','ABR')
1243: and information104 = 'UNLINK';
1362: begin
1363: hr_utility.set_location('going for pgm datapull',20);
1364: select information50,information41,substr(information5,1,80)
1365: into l_gl_currency,l_gl_freq,l_gl_name
1366: from ben_copy_entity_results
1367: where copy_entity_txn_id = p_copy_entity_txn_id
1368: and result_type_cd ='DISPLAY'
1369: and table_alias = 'PGM';
1370: hr_utility.set_location('curr and freq of pgm fetched',10);
1429: where copy_entity_txn_id = p_copy_entity_txn_id;
1430:
1431: -- Purging the Copy Entity Txn record as it is no longer required --
1432:
1433: Delete from Ben_Copy_Entity_Results
1434: where Copy_Entity_Txn_Id = p_copy_entity_txn_id
1435: and Table_Alias Not In ('PQH_GSP_TASK_LIST','PQH_CORPS_TASK_LIST');
1436:
1437: hr_utility.set_location('txn stat chg to comp',40);
1576: p_business_group_id in number,
1577: p_datetrack_mode in varchar2) is
1578: cursor csr_gsps is
1579: select *
1580: from ben_copy_entity_results
1581: where copy_entity_txn_id = p_copy_entity_txn_id
1582: and table_alias = 'CPP'
1583: and Dml_Operation <> 'DELETE';
1584: l_proc varchar2(61) := 'stage_to_grd_sp' ;
1606: if grd_spine.information255 is null and grd_spine.information258 is not null then
1607: begin
1608: select information1
1609: into l_scale_id
1610: from ben_copy_entity_results
1611: where copy_entity_result_id = grd_spine.information258;
1612: exception
1613: when others then
1614: hr_utility.set_location('scale was created but deleted',10);
1618: end if;
1619: if grd_spine.information253 is null and grd_spine.information252 is not null then
1620: select information223
1621: into l_grade_id
1622: from ben_copy_entity_results
1623: where copy_entity_result_id = grd_spine.information252;
1624: else
1625: l_grade_id := grd_spine.information253;
1626: end if;
1817: l_object varchar2(80);
1818: l_message_text varchar2(2000);
1819: cursor csr_points is
1820: select *
1821: from ben_copy_entity_results
1822: where copy_entity_txn_id = p_copy_entity_txn_id
1823: and table_alias = 'OPT'
1824: and dml_operation in ('INSERT','UPDATE','UPD_INS')
1825: order by information253 desc; -- do highest seq. first
1831: if point_rec.information255 is null and point_rec.information256 is not null then
1832: begin
1833: select information1
1834: into l_scale_id
1835: from ben_copy_entity_results
1836: where copy_entity_result_id = point_rec.information256;
1837: exception
1838: when others then
1839: hr_utility.set_location('scale created but removed',10);
1962: l_concat_segments varchar2(600);
1963: l_message_text varchar2(2000);
1964: cursor csr_grades is
1965: select *
1966: from ben_copy_entity_results
1967: where copy_entity_txn_id = p_copy_entity_txn_id
1968: and table_alias = 'PLN'
1969: and dml_operation in ('INSERT','UPDATE','UPD_INS');
1970: begin
2093: l_message_text varchar2(2000);
2094: l_grd_effstdt date; --DN code for BugId: 3242976
2095: cursor csr_steps is
2096: select *
2097: from ben_copy_entity_results
2098: where copy_entity_txn_id = p_copy_entity_txn_id
2099: and table_alias = 'COP'
2100: and dml_operation = 'INSERT';
2101: begin
2105: l_step_ovn := step_rec.information254;
2106: if step_rec.information255 is null then
2107: select information280
2108: into l_grd_sp_id
2109: from ben_copy_entity_results
2110: where copy_entity_result_id = step_rec.gs_parent_entity_result_id;
2111: else
2112: l_grd_sp_id := step_rec.information255;
2113: end if;
2113: end if;
2114: if step_rec.information256 is null and step_rec.information262 is not null then
2115: select information257
2116: into l_point_id
2117: from ben_copy_entity_results
2118: where copy_entity_result_id = step_rec.information262;
2119: else
2120: l_point_id := step_rec.information256;
2121: end if;
2217: l_db_ovn number;
2218: l_message_text varchar2(2000);
2219: cursor csr_scales is
2220: select *
2221: from ben_copy_entity_results
2222: where copy_entity_txn_id = p_copy_entity_txn_id
2223: and table_alias ='SCALE'
2224: and dml_operation in ('INSERT','UPDATE');
2225: begin
2367: l_message_text varchar2(2000);
2368: l_dml_operation varchar2(30);
2369: cursor csr_hrr is
2370: select *
2371: from ben_copy_entity_results
2372: where copy_entity_txn_id = p_copy_entity_txn_id
2373: and table_alias = 'HRRATE'
2374: and dml_operation in ('INSERT','UPDATE')
2375: order by INFORMATION277,INFORMATION278,INFORMATION2;
2414: hr_utility.set_location('going for getting pl_id ',3);
2415: begin
2416: select information223
2417: into l_grd_sp_id
2418: from ben_copy_entity_results
2419: where copy_entity_result_id = hrr_rec.INFORMATION277;
2420: exception
2421: when others then
2422: l_grd_sp_id := null;
2432: hr_utility.set_location('going for getting opt_id ',3);
2433: begin
2434: select information257
2435: into l_grd_sp_id
2436: from ben_copy_entity_results
2437: where copy_entity_result_id = hrr_rec.INFORMATION278;
2438: exception
2439: when others then
2440: l_grd_sp_id := null;