[Home] [Help]
136: l_pgm_id number;
137: begin
138: select cer.information1,cer.copy_entity_txn_id,cpd.corps_definition_id
139: into l_pgm_id,p_cet_id,p_corps_id
140: from ben_copy_entity_results cer, pqh_corps_definitions cpd
141: where copy_entity_result_id = p_pgm_cer_id
142: and cpd.ben_pgm_id = cer.information1;
143: exception
144: when no_data_found then
155: l_comb_grade pqh_corps_extra_info.information30%type;
156: l_comb_plip_cer pqh_corps_extra_info.information30%type;
157: cursor get_plip_cer (p_grade_id number)is
158: select plip.copy_entity_result_id
159: from ben_copy_entity_results plip, ben_copy_entity_results pln
160: where plip.table_alias = 'CPP' -- plip row
161: and pln.table_alias = 'PLN'
162: and plip.information261 = pln.information1
163: and pln.information294 = p_grade_id
207: l_comb_grade pqh_corps_extra_info.information30%type;
208: l_comb_plip pqh_corps_extra_info.information30%type;
209: cursor get_grade(p_plip_cer_id number)is
210: select information253
211: from ben_copy_entity_results
212: where copy_entity_result_id = p_plip_cer_id;
213: begin
214: -- data passed is having grade_ids concatenated
215: -- what we have to pass is plip_cer_ids concatenated for page to read
298: l_cdd_count number;
299: begin
300: select count(*)
301: into l_cdd_count
302: from ben_copy_entity_results
303: where copy_entity_txn_id = p_copy_entity_txn_id
304: and table_alias = 'CORPS_DOC';
305: if nvl(l_cdd_count,0) > 0 then
306: l_cdd_exists := 'Y';
314: l_cpd_count number;
315: begin
316: select count(*)
317: into l_cpd_count
318: from ben_copy_entity_results
319: where copy_entity_txn_id = p_copy_entity_txn_id
320: and table_alias = 'CPD';
321: if nvl(l_cpd_count,0) > 0 then
322: l_cpd_exists := 'Y';
372: -- step id is there, for UI we need step cer id
373: begin
374: select copy_entity_result_id
375: into l_step_cer_id
376: from ben_copy_entity_results
377: where copy_entity_txn_id = p_copy_entity_txn_id
378: and table_alias = 'COP'
379: and information253 = pth_rec.entity_id;
380: exception
385: hr_utility.set_location('step cer id is '||l_step_cer_id,10);
386: hr_utility.set_location('hier ver is '||l_hierarchy_version_id,11);
387: begin
388: hr_utility.set_location('cer insert api called',55);
389: ben_copy_entity_results_api.create_copy_entity_results
390: (p_effective_date => p_effective_date
391: ,p_copy_entity_txn_id => p_copy_entity_txn_id
392: ,p_result_type_cd => 'DISPLAY'
393: ,p_table_name => l_pth_tr_name
466: if l_continue then
467: for cdd_rec in csr_cdd_rec loop
468: begin
469: hr_utility.set_location('cer insert api called',55);
470: ben_copy_entity_results_api.create_copy_entity_results
471: (p_effective_date => p_effective_date
472: ,p_copy_entity_txn_id => p_copy_entity_txn_id
473: ,p_result_type_cd => 'DISPLAY'
474: ,p_table_name => l_cdd_tr_name
538: hr_utility.set_location('starting grade is '||cpd_rec.starting_grade_id,60);
539: begin
540: select copy_entity_result_id
541: into l_starting_plip_cer_id
542: from ben_copy_entity_results
543: where copy_entity_txn_id = p_copy_entity_txn_id
544: and table_alias = 'CPP'
545: and information253 = cpd_rec.starting_grade_id;
546: exception
554: hr_utility.set_location('starting grade step is '||cpd_rec.starting_grade_step_id,60);
555: begin
556: select copy_entity_result_id
557: into l_starting_oipl_cer_id
558: from ben_copy_entity_results
559: where copy_entity_txn_id = p_copy_entity_txn_id
560: and table_alias = 'COP'
561: and information253 = cpd_rec.starting_grade_step_id;
562: exception
581: hr_utility.set_location('l_review_period flag '||l_review_period,55);
582: hr_utility.set_location('l_pgm_extra_info_id flag '||l_pgm_extra_info_id,55);
583:
584: hr_utility.set_location('cer insert api called',55);
585: ben_copy_entity_results_api.create_copy_entity_results
586: (p_effective_date => p_effective_date
587: ,p_copy_entity_txn_id => p_copy_entity_txn_id
588: ,p_result_type_cd => 'DISPLAY'
589: ,p_table_name => l_cpd_tr_name
652: p_business_group_id in number,
653: p_datetrack_mode in varchar2) is
654: cursor csr_crpth_rec is
655: select *
656: from ben_copy_entity_results
657: where copy_entity_txn_id = p_copy_entity_txn_id
658: and table_alias = 'CRPATH'
659: and dml_operation <> 'REUSE';
660: l_pth_ovn number;
667: l_hierarchy_version_id number;
668: begin
669: l_hierarchy_version_id := get_crpth_hier_ver;
670:
671: update ben_copy_entity_results
672: set dml_operation = 'DELETE'
673: where copy_entity_txn_id = p_copy_entity_txn_id
674: and table_alias in ('CRPATH')
675: and information104 = 'UNLINK';
680: if pth_rec.information232 is null and pth_rec.information160 is not null then
681: hr_utility.set_location('getting corps id from corp_cer',10);
682: select information1
683: into l_cpd_id
684: from ben_copy_entity_results
685: where copy_entity_result_id = pth_rec.information160;
686: else
687: l_cpd_id := pth_rec.information232;
688: end if;
689: if pth_rec.information234 is null and pth_rec.information161 is not null then
690: hr_utility.set_location('getting step id from oipl_cer',10);
691: select information253
692: into l_entity_id
693: from ben_copy_entity_results
694: where copy_entity_result_id = pth_rec.information161;
695: else
696: l_entity_id := pth_rec.information234;
697: end if;
804: p_business_group_id in number,
805: p_datetrack_mode in varchar2) is
806: cursor csr_doc_rec is
807: select *
808: from ben_copy_entity_results
809: where copy_entity_txn_id = p_copy_entity_txn_id
810: and table_alias = 'CORPS_DOC'
811: and dml_operation <> 'REUSE';
812: l_cdd_ovn number;
821: l_cdd_ovn := cdd_rec.information298;
822: if cdd_rec.information160 is null then
823: select information1
824: into l_cpd_id
825: from ben_copy_entity_results
826: where copy_entity_result_id = cdd_rec.GS_MIRROR_SRC_ENTITY_RESULT_ID;
827: else
828: l_cpd_id := cdd_rec.information160;
829: end if;
900: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,4);
901: -- update corp rows with corps id
902: begin
903: hr_utility.set_location('updating plips for cpd :'||p_cpd_id,4);
904: update ben_copy_entity_results
905: set information1 = p_cpd_id
906: where copy_entity_txn_id = p_copy_entity_txn_id
907: and table_alias = 'CPD';
908: hr_utility.set_location('num of corp updated'||sql%rowcount,20);
913: end;
914: -- update crpath rows with corps id
915: begin
916: hr_utility.set_location('updating plips for cpd :'||p_cpd_id,4);
917: update ben_copy_entity_results
918: set information232 = p_cpd_id
919: where copy_entity_txn_id = p_copy_entity_txn_id
920: and table_alias = 'CRPATH';
921: hr_utility.set_location('num of crpaths updated'||sql%rowcount,20);
926: end;
927: -- update cpp rows with corps id
928: begin
929: hr_utility.set_location('updating plips for cpd :'||p_cpd_id,4);
930: update ben_copy_entity_results
931: set information291 = p_cpd_id
932: where copy_entity_txn_id = p_copy_entity_txn_id
933: and table_alias = 'CPP';
934: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
939: end;
940: -- update cdd rows with corps id
941: begin
942: hr_utility.set_location('updating docs for cpd :'||p_cpd_id,4);
943: update ben_copy_entity_results
944: set information160 = p_cpd_id
945: where copy_entity_txn_id = p_copy_entity_txn_id
946: and table_alias = 'CORPS_DOC';
947: hr_utility.set_location('num of docs updated'||sql%rowcount,20);
987: IS
988: CURSOR csr_corp_rec
989: IS
990: SELECT *
991: FROM ben_copy_entity_results
992: WHERE copy_entity_txn_id = p_copy_entity_txn_id
993: AND table_alias = 'CPD'
994: AND dml_operation <> 'REUSE';
995:
1009:
1010: CURSOR c_plip
1011: IS
1012: SELECT copy_entity_result_id, information253
1013: FROM ben_copy_entity_results
1014: WHERE copy_entity_txn_id = p_copy_entity_txn_id
1015: AND table_alias = 'CPP'
1016: AND information104 <> 'UNLINK'
1017: AND result_type_cd = 'DISPLAY'
1019:
1020: CURSOR c_oipl (p_plip_cer_id NUMBER)
1021: IS
1022: SELECT copy_entity_result_id, information253
1023: FROM ben_copy_entity_results
1024: WHERE copy_entity_txn_id = p_copy_entity_txn_id
1025: AND table_alias = 'COP'
1026: AND information104 <> 'UNLINK'
1027: AND gs_parent_entity_result_id = p_plip_cer_id
1036: l_updated := FALSE;
1037:
1038: SELECT DECODE (information38, 'A', 'ACTIVE', 'INACTIVE')
1039: INTO l_status_cd
1040: FROM ben_copy_entity_results
1041: WHERE copy_entity_txn_id = p_copy_entity_txn_id
1042: AND table_alias = 'PGM'
1043: AND dml_operation <> 'REUSE';
1044:
1050:
1051: BEGIN
1052: SELECT information1, information5, information2
1053: INTO l_pgm_id, l_pgm_name, l_pgm_esd
1054: FROM ben_copy_entity_results
1055: WHERE copy_entity_result_id =
1056: cpd_rec.gs_mirror_src_entity_result_id;
1057: EXCEPTION
1058: WHEN OTHERS
1140:
1141: BEGIN
1142: SELECT information253
1143: INTO l_starting_grade_id
1144: FROM ben_copy_entity_results
1145: WHERE copy_entity_result_id = cpd_rec.information162;
1146: EXCEPTION
1147: WHEN OTHERS
1148: THEN
1154:
1155: BEGIN
1156: SELECT information253
1157: INTO l_starting_step_id
1158: FROM ben_copy_entity_results
1159: WHERE copy_entity_result_id = cpd_rec.information169;
1160: EXCEPTION
1161: WHEN OTHERS
1162: THEN
1326: IF l_updated = FALSE
1327: THEN
1328: SELECT information1, information298
1329: INTO l_corps_def_id, l_cpd_ovn
1330: FROM ben_copy_entity_results
1331: WHERE copy_entity_txn_id = p_copy_entity_txn_id
1332: AND table_alias = 'CPD'
1333: AND dml_operation = 'REUSE';
1334: