[Home] [Help]
18: -- opt_name is option name which is combination of point and scale
19: begin
20: select information255,substr(information98,1,30),substr(information5,1,61)
21: into l_scl_id,l_point_name,l_opt_name
22: from ben_copy_entity_results
23: where copy_entity_result_id = p_opt_cer_id;
24: if l_scl_id is not null then
25: l_scl_name := get_scl_name(p_scl_id => l_scl_id);
26: if l_point_name is not null then
48: begin
49: hr_utility.set_location('crset id is '||p_crset_id,20);
50: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,20);
51: hr_utility.set_location('elp id is '||p_elp_id,20);
52: update ben_copy_entity_results
53: set information279 = p_elp_id
54: where copy_entity_txn_id = p_copy_entity_txn_id
55: and table_alias = 'CRRATE'
56: and information160 = p_crset_id;
227: p_point_cer_id in number,
228: p_copy_entity_txn_id in number) return varchar2 is
229: cursor csr_crset is
230: select substr(information151,1,150)
231: from ben_copy_entity_results
232: where copy_entity_txn_id = p_copy_entity_txn_id
233: and table_alias = 'CRSET'
234: and information161 = p_crset_id
235: order by information2;
255: hr_utility.set_location('grd short name to be pulled',11);
256: begin
257: select substr(information102,1,30)
258: into l_grd_name
259: from ben_copy_entity_results
260: where copy_entity_result_id = p_grade_cer_id;
261: exception
262: when others then
263: hr_utility.set_location('issue in selecting grd_name',11);
387: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
388: -- update plip row with plip id
389: begin
390: hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
391: update ben_copy_entity_results
392: set information1 = p_plip_id
393: where copy_entity_result_id = p_plip_cer_id;
394: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
395: exception
399: end;
400: -- update oipl rows with pl id
401: begin
402: hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
403: update ben_copy_entity_results
404: set information261 = p_pl_id
405: where gs_parent_entity_result_id = p_plip_cer_id
406: and table_alias ='COP'
407: and copy_entity_txn_id = p_copy_entity_txn_id;
413: end;
414: -- update epa rows with plip id
415: begin
416: hr_utility.set_location('updating epa for plip:'||p_plip_id,4);
417: update ben_copy_entity_results
418: set information1 = p_plip_id
419: where copy_entity_result_id = p_plip_cer_id;
420: hr_utility.set_location('num of epas updated'||sql%rowcount,20);
421: exception
433: hr_utility.set_location('oipl cer id is '||p_oipl_cer_id,2);
434: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
435: begin
436: hr_utility.set_location('updating oipl:'||p_oipl_id,4);
437: update ben_copy_entity_results
438: set information1 = p_oipl_id
439: where copy_entity_result_id = p_oipl_cer_id;
440: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
441: exception
456: -- update oipl rows with opt id
457: begin
458: -- oipl row is updated with opt id
459: hr_utility.set_location('updating oipl for opt :'||p_opt_id,4);
460: update ben_copy_entity_results
461: set information247 = p_opt_id
462: where copy_entity_txn_id = p_copy_entity_txn_id
463: and table_alias = 'COP'
464: and information262 = p_opt_cer_id;
470: end;
471: -- update abr rows with opt id
472: begin
473: hr_utility.set_location('updating abr for opt:'||p_opt_id,4);
474: update ben_copy_entity_results
475: set information247 = p_opt_id,
476: information170 = p_opt_name
477: where copy_entity_txn_id = p_copy_entity_txn_id
478: and table_alias = 'ABR'
497: -- update plip rows with plan id
498: begin
499: -- plip row is updated with pl id
500: hr_utility.set_location('updating plips for pl :'||p_pl_id,4);
501: update ben_copy_entity_results
502: set information261 = p_pl_id
503: where copy_entity_txn_id = p_copy_entity_txn_id
504: and table_alias = 'CPP'
505: and copy_entity_result_id = p_plip_cer_id;
511: end;
512: -- update abr rows with pl id
513: begin
514: hr_utility.set_location('updating abr for pl:'||p_pl_id,4);
515: update ben_copy_entity_results
516: set information261 = p_pl_id,
517: information170 = p_pl_name
518: where copy_entity_txn_id = p_copy_entity_txn_id
519: and table_alias = 'ABR'
527: -- update oipl rows with plan id
528: begin
529: -- oipl row is updated with pl id
530: hr_utility.set_location('updating oipls for pl :'||p_pl_id,4);
531: update ben_copy_entity_results
532: set information261 = p_pl_id
533: where copy_entity_txn_id = p_copy_entity_txn_id
534: and table_alias = 'COP'
535: and gs_parent_entity_result_id = p_plip_cer_id;
540: raise;
541: end;
542: -- update pln row
543: begin
544: update ben_copy_entity_results
545: set information1 = p_pl_id
546: where copy_entity_result_id = p_pl_cer_id;
547: hr_utility.set_location('num of plans updated'||sql%rowcount,20);
548: exception
564: -- update plip rows with program id
565: begin
566: -- plip row is updated with pgm id
567: hr_utility.set_location('updating plips for pgm :'||p_pgm_id,4);
568: update ben_copy_entity_results
569: set information260 = p_pgm_id
570: where copy_entity_txn_id = p_copy_entity_txn_id
571: and table_alias = 'CPP';
572: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
578: -- update cpd rows with program id
579: begin
580: -- cpd row is updated with pgm id
581: hr_utility.set_location('updating cpd for pgm :'||p_pgm_id,4);
582: update ben_copy_entity_results
583: set information260 = p_pgm_id
584: where copy_entity_txn_id = p_copy_entity_txn_id
585: and table_alias = 'CPD';
586: hr_utility.set_location('num of cpd updated'||sql%rowcount,20);
590: raise;
591: end;
592: -- update pgm row for information1
593: begin
594: update ben_copy_entity_results
595: set information1 = p_pgm_id
596: where copy_entity_result_id = p_pgm_cer_id;
597: hr_utility.set_location('num of pgms updated'||sql%rowcount,20);
598: exception
879: p_effective_date in date
880: ) is
881: cursor c_pgi is
882: select *
883: from ben_copy_entity_results
884: where copy_entity_txn_id = p_copy_entity_txn_id
885: and table_alias = 'PGI'
886: and dml_operation in ('INSERT','UPDATE') ;-- only insert/ updates should be there
887: --
897:
898: BEGIN
899: SELECT information1, information5, information2
900: INTO l_pgm_id, l_pgm_name, l_pgm_esd
901: FROM ben_copy_entity_results
902: WHERE copy_entity_txn_id = p_copy_entity_txn_id
903: and table_alias = 'PGM'
904: and result_type_cd='DISPLAY';
905: EXCEPTION
1054: p_pl_typ_id in number,
1055: p_datetrack_mode in varchar2) is
1056: cursor c_OPT is
1057: select *
1058: from ben_copy_entity_results
1059: where copy_entity_txn_id = p_copy_entity_txn_id
1060: and table_alias = 'OPT'
1061: and dml_operation <> 'REUSE' ;
1062: r_OPT c_OPT%rowtype;
1219: p_pl_typ_id in number,
1220: p_datetrack_mode in varchar2) is
1221: cursor c_pln is
1222: select *
1223: from ben_copy_entity_results
1224: where copy_entity_txn_id = p_copy_entity_txn_id
1225: and table_alias = 'PLN'
1226: and dml_operation <> 'REUSE' ;
1227: r_pln c_pln%rowtype;
1485: p_pl_typ_id in number,
1486: p_datetrack_mode in varchar2) is
1487: cursor c_pgm is
1488: select *
1489: from ben_copy_entity_results
1490: where copy_entity_txn_id = p_copy_entity_txn_id
1491: and table_alias = 'PGM'
1492: and dml_operation in ('INSERT','UPDATE') ;-- only insert/ updates should be there
1493: --
1782: p_effective_date in date,
1783: p_datetrack_mode in varchar2) is
1784: cursor c_cop is
1785: select *
1786: from ben_copy_entity_results
1787: where copy_entity_txn_id = p_copy_entity_txn_id
1788: and table_alias = 'COP'
1789: and dml_operation <> 'REUSE' ;
1790: --
1809: l_effective_date := r_cop.information2;
1810: if r_cop.INFORMATION261 is null and r_cop.gs_parent_entity_result_id is not null then
1811: select information261
1812: into l_pl_id
1813: from ben_copy_entity_results
1814: where copy_entity_result_id = r_cop.gs_parent_entity_result_id;
1815: else
1816: l_pl_id := r_cop.INFORMATION261;
1817: end if;
1817: end if;
1818: if r_cop.INFORMATION247 is null and r_cop.information262 is not null then
1819: select information1
1820: into l_opt_id
1821: from ben_copy_entity_results
1822: where copy_entity_result_id = r_cop.information262;
1823: else
1824: l_opt_id := r_cop.INFORMATION247;
1825: end if;
1979: p_datetrack_mode in varchar2,
1980: p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
1981: cursor c_updated_cpp is
1982: select *
1983: from ben_copy_entity_results
1984: where copy_entity_txn_id = p_copy_entity_txn_id
1985: and table_alias = 'CPP'
1986: and information1 is not null
1987: and dml_operation = 'UPDATE' ; -- only updated plips should be selected
1987: and dml_operation = 'UPDATE' ; -- only updated plips should be selected
1988:
1989: cursor c_cpp is
1990: select *
1991: from ben_copy_entity_results
1992: where copy_entity_txn_id = p_copy_entity_txn_id
1993: and table_alias = 'CPP'
1994: and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
1995: order by information263 desc;-- order by highest seq 1st
2022: l_ovn := r_cpp.information265;
2023: if r_CPP.INFORMATION260 is null then
2024: select information1
2025: into l_pgm_id
2026: from ben_copy_entity_results
2027: where copy_entity_txn_id = p_copy_entity_txn_id
2028: and table_alias = 'PGM'
2029: and copy_entity_result_id = r_cpp.gs_parent_entity_result_id; -- 7610624
2030: else
2032: end if;
2033: if r_CPP.INFORMATION261 is null then
2034: select information1
2035: into l_pl_id
2036: from ben_copy_entity_results
2037: where copy_entity_txn_id = p_copy_entity_txn_id
2038: and table_alias = 'PLN'
2039: and copy_entity_result_id = r_CPP.INFORMATION252;
2040: else
2046: --Added by kgowripe for bug#3532412
2047: if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2048: SELECT information1
2049: INTO l_corps_definition_id
2050: FROM ben_copy_entity_results
2051: WHERE copy_entity_txn_id = p_copy_entity_txn_id
2052: AND table_alias = 'CPD';
2053: else
2054: l_corps_definition_id := r_cpp.information291;
2228: p_effective_date in date,
2229: p_datetrack_mode in varchar2) is
2230: cursor c_elp is
2231: select *
2232: from ben_copy_entity_results
2233: where copy_entity_txn_id = p_copy_entity_txn_id
2234: and table_alias = 'CRSET'
2235: and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
2236: order by information161,information2;
2806: p_effective_date in date,
2807: p_datetrack_mode in varchar2) is
2808: cursor c_cep is
2809: select *
2810: from ben_copy_entity_results
2811: where copy_entity_txn_id = p_copy_entity_txn_id
2812: and table_alias = 'CEP'
2813: and dml_operation in ('INSERT','UPDATE') ; -- only insert/update should be there
2814: --
2834: hr_utility.set_location('for cep_id:'||l_cep_id ||'dml '||r_cep.dml_operation,20);
2835: if r_cep.gs_mirror_src_entity_result_id is not null then
2836: select information1,table_alias
2837: into l_pk, l_tab
2838: from ben_copy_entity_results
2839: where copy_entity_result_id = r_cep.gs_mirror_src_entity_result_id;
2840: hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
2841: if l_tab ='EPA' then
2842: l_epa_id := l_pk;
2937: p_effective_date in date,
2938: p_datetrack_mode in varchar2) is
2939: cursor c_abr is
2940: select *
2941: from ben_copy_entity_results
2942: where copy_entity_txn_id = p_copy_entity_txn_id
2943: and table_alias = 'ABR'
2944: and dml_operation = 'INSERT' ;-- only insert should be there
2945: --
2967: hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2968: begin
2969: select information1
2970: into l_pl_id
2971: from ben_copy_entity_results
2972: where copy_entity_result_id = r_ABR.INFORMATION277;
2973: exception
2974: when others then
2975: l_pl_id := '';
2982: hr_utility.set_location('opt_cer_id :'||r_ABR.INFORMATION278,3);
2983: begin
2984: select information1
2985: into l_opt_id
2986: from ben_copy_entity_results
2987: where copy_entity_result_id = r_ABR.INFORMATION278;
2988: exception
2989: when others then
2990: l_opt_id := '';
3110: ,P_EFFECTIVE_END_DATE => l_effective_end_date
3111: ,P_OBJECT_VERSION_NUMBER => l_ovn
3112: );
3113: hr_utility.set_location('After plsql table ',222);
3114: update ben_copy_entity_results
3115: set information1 = l_abr_id
3116: where copy_entity_result_id = r_abr.copy_entity_result_id;
3117: hr_utility.set_location('abr id updated '||l_abr_id,222);
3118: else
3154: p_effective_date in date,
3155: p_datetrack_mode in varchar2) is
3156: cursor c_epa is
3157: select *
3158: from ben_copy_entity_results
3159: where copy_entity_txn_id = p_copy_entity_txn_id
3160: and table_alias = 'EPA'
3161: and dml_operation = 'INSERT' ; -- only insert should be there
3162: --
3183: if r_epa.gs_mirror_src_entity_result_id is not null then
3184: begin
3185: select information1,table_alias
3186: into l_pk, l_tab
3187: from ben_copy_entity_results
3188: where copy_entity_result_id = r_epa.gs_mirror_src_entity_result_id;
3189: exception
3190: when others then
3191: raise;
3251: ,P_EFFECTIVE_START_DATE => l_effective_start_date
3252: ,P_EFFECTIVE_END_DATE => l_effective_end_date
3253: ,P_OBJECT_VERSION_NUMBER => l_ovn
3254: );
3255: update ben_copy_entity_results
3256: set information1 = l_epa_id
3257: where copy_entity_result_id = r_epa.copy_entity_result_id;
3258: else
3259: l_message_text := 'invalid oper'||r_epa.dml_operation
3293: p_effective_date in date,
3294: p_datetrack_mode in varchar2) is
3295: cursor c_crr is
3296: select *
3297: from ben_copy_entity_results
3298: where copy_entity_txn_id = p_copy_entity_txn_id
3299: and table_alias = 'CRRATE'
3300: and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
3301: order by information230,information169,information160,information2;
3372: hr_utility.set_location('abr_id is null ,getting it'||l_proc,10);
3373: begin
3374: select information1
3375: into l_abr_id
3376: from ben_copy_entity_results
3377: where copy_entity_txn_id = p_copy_entity_txn_id
3378: and copy_entity_result_id = crr_rec.information161;
3379: exception
3380: when no_data_found then
3521: -- elp id is null for crrate while crset id is there
3522: begin
3523: select information277
3524: into l_elp_id
3525: from ben_copy_entity_results
3526: where copy_entity_txn_id = p_copy_entity_txn_id
3527: and table_alias = 'CRSET'
3528: and information161 = crr_rec.information160;
3529: exception
3858: is
3859: begin
3860: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3861:
3862: update ben_copy_entity_results cer
3863: set information263 =
3864: ( select ELIGY_PRFL_ID
3865: from BEN_ELIGY_PRFL_F elp
3866: where elp.BUSINESS_GROUP_ID = p_business_group_id
3905:
3906: -- update the dml operation of those records which have already been
3907: -- taken care by gsp pre push code
3908:
3909: UPDATE ben_copy_entity_results cer
3910: set dml_operation = 'GSPDEL'
3911: where cer.copy_entity_txn_id = p_copy_entity_txn_id
3912: and cer.dml_operation = 'DELETE'
3913: and table_alias in ('CPP','CEP','EPA','COP','OPT','ABR');
3923: p_copy_entity_txn_id => p_copy_entity_txn_id
3924: ,p_delete_failed => l_delete_failed
3925: );
3926:
3927: UPDATE ben_copy_entity_results cer
3928: set number_of_copies = 0
3929: where cer.copy_entity_txn_id = p_copy_entity_txn_id
3930: and p_effective_date between nvl(information2,p_effective_date)
3931: and nvl(information3,p_effective_date)
4035: ,p_business_group_id => p_business_group_id
4036: ,p_effective_date => p_effective_date
4037: );
4038:
4039: UPDATE ben_copy_entity_results cer
4040: set dml_operation = 'DELETE'
4041: where cer.copy_entity_txn_id = p_copy_entity_txn_id
4042: and cer.dml_operation = 'GSPDEL';
4043: