[Home] [Help]
24: --
25: ,p_opt_id in number default null
26: --
27: ) is
28: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
29: l_proc varchar2(72) := g_package||'create_rate_results';
30: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
31:
32: --
26: --
27: ) is
28: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
29: l_proc varchar2(72) := g_package||'create_rate_results';
30: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
31:
32: --
33: cursor c_parent_result(c_parent_pk_id number,
34: c_parent_table_name varchar2,
33: cursor c_parent_result(c_parent_pk_id number,
34: c_parent_table_name varchar2,
35: c_copy_entity_txn_id number) is
36: select copy_entity_result_id mirror_src_entity_result_id
37: from ben_copy_entity_results cpe,
38: pqh_table_route trt
39: where cpe.information1= c_parent_pk_id
40: and cpe.result_type_cd = 'DISPLAY'
41: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
92: where abr.acty_base_rt_id = c_acty_base_rt_id
93: -- and abr.business_group_id = p_business_group_id
94: and not exists (
95: select /* */ null
96: from ben_copy_entity_results cpe
97: -- pqh_table_route trt
98: where copy_entity_txn_id = p_copy_entity_txn_id
99: -- and trt.table_route_id = cpe.table_route_id
100: and ( -- c_mirror_src_entity_result_id is null or
196: where apf.acty_rt_pymt_sched_id = c_acty_rt_pymt_sched_id
197: --and apf.business_group_id = p_business_group_id
198: and not exists (
199: select /* */ null
200: from ben_copy_entity_results cpe
201: -- pqh_table_route trt
202: where copy_entity_txn_id = p_copy_entity_txn_id
203: -- and trt.table_route_id = cpe.table_route_id
204: and ( --l_mirror_src_entity_result_id is null or
228: where psq.pymt_sched_py_freq_id = c_pymt_sched_py_freq_id
229: --and psq.business_group_id = p_business_group_id
230: and not exists (
231: select /* */ null
232: from ben_copy_entity_results cpe
233: -- pqh_table_route trt
234: where copy_entity_txn_id = p_copy_entity_txn_id
235: -- and trt.table_route_id = cpe.table_route_id
236: and ( --c_mirror_src_entity_result_id is null or
260: where apr.actl_prem_id = c_actl_prem_id
261: -- and apr.business_group_id = p_business_group_id
262: and not exists (
263: select /* */ null
264: from ben_copy_entity_results cpe
265: -- pqh_table_route trt
266: where copy_entity_txn_id = p_copy_entity_txn_id
267: -- and trt.table_route_id = cpe.table_route_id
268: and ( -- c_mirror_src_entity_result_id is null or
292: where apl.acty_rt_ptd_lmt_id = c_acty_rt_ptd_lmt_id
293: -- and apl.business_group_id = p_business_group_id
294: and not exists (
295: select /* */ null
296: from ben_copy_entity_results cpe
297: -- pqh_table_route trt
298: where copy_entity_txn_id = p_copy_entity_txn_id
299: -- and trt.table_route_id = cpe.table_route_id
300: and ( -- c_mirror_src_entity_result_id is null or
325: where avr.acty_vrbl_rt_id = c_acty_vrbl_rt_id
326: -- and avr.business_group_id = p_business_group_id
327: and not exists (
328: select /* */ null
329: from ben_copy_entity_results cpe
330: -- pqh_table_route trt
331: where copy_entity_txn_id = p_copy_entity_txn_id
332: -- and trt.table_route_id = cpe.table_route_id
333: and ( -- c_mirror_src_entity_result_id is null or
340: and information3 = avr.effective_end_date);
341: --
342: cursor c_avr_drp(c_acty_vrbl_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
343: select distinct cpe.information262 vrbl_rt_prfl_id
344: from ben_copy_entity_results cpe
345: -- pqh_table_route trt
346: where copy_entity_txn_id = p_copy_entity_txn_id
347: -- and trt.table_route_id = cpe.table_route_id
348: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
372: where mtr.mtchg_rt_id = c_mtchg_rt_id
373: -- and mtr.business_group_id = p_business_group_id
374: and not exists (
375: select /* */ null
376: from ben_copy_entity_results cpe
377: -- pqh_table_route trt
378: where copy_entity_txn_id = p_copy_entity_txn_id
379: -- and trt.table_route_id = cpe.table_route_id
380: and ( -- c_mirror_src_entity_result_id is null or
404: where pmr.prtl_mo_rt_prtn_val_id = c_prtl_mo_rt_prtn_val_id
405: -- and pmr.business_group_id = p_business_group_id
406: and not exists (
407: select /* */ null
408: from ben_copy_entity_results cpe
409: -- pqh_table_route trt
410: where copy_entity_txn_id = p_copy_entity_txn_id
411: -- and trt.table_route_id = cpe.table_route_id
412: and ( -- c_mirror_src_entity_result_id is null or
437: where pdl.ptd_lmt_id = c_ptd_lmt_id
438: -- and pdl.business_group_id = p_business_group_id
439: and not exists (
440: select /* */ null
441: from ben_copy_entity_results cpe
442: -- pqh_table_route trt
443: where copy_entity_txn_id = p_copy_entity_txn_id
444: -- and trt.table_route_id = cpe.table_route_id
445: and ( -- c_mirror_src_entity_result_id is null or
469: where eiv.extra_input_value_id = c_extra_input_value_id
470: -- and eiv.business_group_id = p_business_group_id
471: and not exists (
472: select /* */ null
473: from ben_copy_entity_results cpe
474: -- pqh_table_route trt
475: where copy_entity_txn_id = p_copy_entity_txn_id
476: -- and trt.table_route_id = cpe.table_route_id
477: and ( -- c_mirror_src_entity_result_id is null or
485: cursor c_element_type_id(c_acty_base_rt_id in number,
486: c_copy_entity_txn_id in number,
487: c_table_alias varchar2) is
488: select cpe.information174 element_type_id
489: from ben_copy_entity_results cpe
490: -- pqh_table_route tre
491: where cpe.information1 = c_acty_base_rt_id
492: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
493: -- and cpe.table_route_id = tre.table_route_id
514: where abc.acty_base_rt_ctfn_id = c_acty_base_rt_ctfn_id
515: -- and abc.business_group_id = p_business_group_id
516: and not exists (
517: select /* */ null
518: from ben_copy_entity_results cpe
519: -- pqh_table_route trt
520: where copy_entity_txn_id = p_copy_entity_txn_id
521: -- and trt.table_route_id = cpe.table_route_id
522: and ( -- c_mirror_src_entity_result_id is null or
535:
536: cursor c_object_exists(c_pk_id number,
537: c_table_alias varchar2) is
538: select null
539: from ben_copy_entity_results cpe
540: -- pqh_table_route trt
541: where copy_entity_txn_id = p_copy_entity_txn_id
542: -- and trt.table_route_id = cpe.table_route_id
543: and cpe.table_alias = c_table_alias
547:
548: l_mirror_src_entity_result_id number(15);
549: l_table_route_id number(15);
550: l_result_type_cd varchar2(30);
551: l_information5 ben_copy_entity_results.information5%type;
552: l_number_of_copies number(15);
553:
554: TYPE rt_ref_csr_typ IS REF CURSOR;
555: c_parent_rec rt_ref_csr_typ;
776: end if;
777:
778: -- PDW
779:
780: ben_copy_entity_results_api.create_copy_entity_results(
781: p_copy_entity_result_id => l_copy_entity_result_id,
782: p_copy_entity_txn_id => p_copy_entity_txn_id,
783: p_result_type_cd => l_result_type_cd,
784: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
1334: end if;
1335: --
1336: l_copy_entity_result_id := null;
1337: l_object_version_number := null;
1338: ben_copy_entity_results_api.create_copy_entity_results(
1339: p_copy_entity_result_id => l_copy_entity_result_id,
1340: p_copy_entity_txn_id => p_copy_entity_txn_id,
1341: p_result_type_cd => l_result_type_cd,
1342: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
1447: l_result_type_cd := 'DISPLAY';
1448: --
1449: l_copy_entity_result_id := null;
1450: l_object_version_number := null;
1451: ben_copy_entity_results_api.create_copy_entity_results(
1452: p_copy_entity_result_id => l_copy_entity_result_id,
1453: p_copy_entity_txn_id => p_copy_entity_txn_id,
1454: p_result_type_cd => l_result_type_cd,
1455: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
1554: end if;
1555: --
1556: l_copy_entity_result_id := null;
1557: l_object_version_number := null;
1558: ben_copy_entity_results_api.create_copy_entity_results(
1559: p_copy_entity_result_id => l_copy_entity_result_id,
1560: p_copy_entity_txn_id => p_copy_entity_txn_id,
1561: p_result_type_cd => l_result_type_cd,
1562: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
1659: end if;
1660: --
1661: l_copy_entity_result_id := null;
1662: l_object_version_number := null;
1663: ben_copy_entity_results_api.create_copy_entity_results(
1664: p_copy_entity_result_id => l_copy_entity_result_id,
1665: p_copy_entity_txn_id => p_copy_entity_txn_id,
1666: p_result_type_cd => l_result_type_cd,
1667: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
1816: end if;
1817: --
1818: l_copy_entity_result_id := null;
1819: l_object_version_number := null;
1820: ben_copy_entity_results_api.create_copy_entity_results(
1821: p_copy_entity_result_id => l_copy_entity_result_id,
1822: p_copy_entity_txn_id => p_copy_entity_txn_id,
1823: p_result_type_cd => l_result_type_cd,
1824: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
1931: end if;
1932: --
1933: l_copy_entity_result_id := null;
1934: l_object_version_number := null;
1935: ben_copy_entity_results_api.create_copy_entity_results(
1936: p_copy_entity_result_id => l_copy_entity_result_id,
1937: p_copy_entity_txn_id => p_copy_entity_txn_id,
1938: p_result_type_cd => l_result_type_cd,
1939: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
2094: end if;
2095: --
2096: l_copy_entity_result_id := null;
2097: l_object_version_number := null;
2098: ben_copy_entity_results_api.create_copy_entity_results(
2099: p_copy_entity_result_id => l_copy_entity_result_id,
2100: p_copy_entity_txn_id => p_copy_entity_txn_id,
2101: p_result_type_cd => l_result_type_cd,
2102: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
2259: l_result_type_cd := 'DISPLAY';
2260: --
2261: l_copy_entity_result_id := null;
2262: l_object_version_number := null;
2263: ben_copy_entity_results_api.create_copy_entity_results(
2264: p_copy_entity_result_id => l_copy_entity_result_id,
2265: p_copy_entity_txn_id => p_copy_entity_txn_id,
2266: p_result_type_cd => l_result_type_cd,
2267: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
2365: end if;
2366: --
2367: l_copy_entity_result_id := null;
2368: l_object_version_number := null;
2369: ben_copy_entity_results_api.create_copy_entity_results(
2370: p_copy_entity_result_id => l_copy_entity_result_id,
2371: p_copy_entity_txn_id => p_copy_entity_txn_id,
2372: p_result_type_cd => l_result_type_cd,
2373: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
2479: ,p_object_version_number out nocopy number
2480: ,p_effective_date in date
2481: ,p_parent_entity_result_id in number
2482: ) is
2483: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
2484: l_proc varchar2(72) := g_package||'create_rate_results';
2485: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
2486: --
2487: cursor c_parent_result(c_parent_pk_id number,
2481: ,p_parent_entity_result_id in number
2482: ) is
2483: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
2484: l_proc varchar2(72) := g_package||'create_rate_results';
2485: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
2486: --
2487: cursor c_parent_result(c_parent_pk_id number,
2488: c_parent_table_name varchar2,
2489: c_copy_entity_txn_id number) is
2487: cursor c_parent_result(c_parent_pk_id number,
2488: c_parent_table_name varchar2,
2489: c_copy_entity_txn_id number) is
2490: select copy_entity_result_id mirror_src_entity_result_id
2491: from ben_copy_entity_results cpe,
2492: pqh_table_route trt
2493: where cpe.information1= c_parent_pk_id
2494: and cpe.result_type_cd = 'DISPLAY'
2495: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
2509: ---
2510: l_mirror_src_entity_result_id number(15);
2511: l_table_route_id number(15);
2512: l_result_type_cd varchar2(30);
2513: l_information5 ben_copy_entity_results.information5%type;
2514: l_number_of_copies number(15);
2515: ---------------------------------------------------------------
2516: -- START OF BEN_CVG_AMT_CALC_MTHD_F ----------------------
2517: ---------------------------------------------------------------
2528: where ccm.cvg_amt_calc_mthd_id = c_cvg_amt_calc_mthd_id
2529: -- and ccm.business_group_id = p_business_group_id
2530: and not exists (
2531: select /* */ null
2532: from ben_copy_entity_results cpe
2533: -- pqh_table_route trt
2534: where copy_entity_txn_id = p_copy_entity_txn_id
2535: -- and trt.table_route_id = cpe.table_route_id
2536: and ( -- c_mirror_src_entity_result_id is null or
2560: where brr.bnft_vrbl_rt_rl_id = c_bnft_vrbl_rt_rl_id
2561: -- and brr.business_group_id = p_business_group_id
2562: and not exists (
2563: select /* */ null
2564: from ben_copy_entity_results cpe
2565: -- pqh_table_route trt
2566: where copy_entity_txn_id = p_copy_entity_txn_id
2567: -- and trt.table_route_id = cpe.table_route_id
2568: and ( -- c_mirror_src_entity_result_id is null or
2593: where bvr.bnft_vrbl_rt_id = c_bnft_vrbl_rt_id
2594: -- and bvr.business_group_id = p_business_group_id
2595: and not exists (
2596: select /* */ null
2597: from ben_copy_entity_results cpe
2598: -- pqh_table_route trt
2599: where copy_entity_txn_id = p_copy_entity_txn_id
2600: -- and trt.table_route_id = cpe.table_route_id
2601: and ( -- c_mirror_src_entity_result_id is null or
2608: and information3 = bvr.effective_end_date);
2609: --
2610: cursor c_bvr_drp(c_bnft_vrbl_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
2611: select distinct cpe.information262 vrbl_rt_prfl_id
2612: from ben_copy_entity_results cpe
2613: -- pqh_table_route trt
2614: where copy_entity_txn_id = p_copy_entity_txn_id
2615: -- and trt.table_route_id = cpe.table_route_id
2616: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
2656: end if;
2657: --
2658: l_copy_entity_result_id := null;
2659: l_object_version_number := null;
2660: ben_copy_entity_results_api.create_copy_entity_results(
2661: p_copy_entity_result_id => l_copy_entity_result_id,
2662: p_copy_entity_txn_id => p_copy_entity_txn_id,
2663: p_result_type_cd => l_result_type_cd,
2664: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
2887: end if;
2888: --
2889: l_copy_entity_result_id := null;
2890: l_object_version_number := null;
2891: ben_copy_entity_results_api.create_copy_entity_results(
2892: p_copy_entity_result_id => l_copy_entity_result_id,
2893: p_copy_entity_txn_id => p_copy_entity_txn_id,
2894: p_result_type_cd => l_result_type_cd,
2895: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
3008: end if;
3009: --
3010: l_copy_entity_result_id := null;
3011: l_object_version_number := null;
3012: ben_copy_entity_results_api.create_copy_entity_results(
3013: p_copy_entity_result_id => l_copy_entity_result_id,
3014: p_copy_entity_txn_id => p_copy_entity_txn_id,
3015: p_result_type_cd => l_result_type_cd,
3016: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
3116: ,p_object_version_number out nocopy number
3117: ,p_effective_date in date
3118: ,p_parent_entity_result_id in number
3119: ) is
3120: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
3121: l_proc varchar2(72) := g_package||'create_rate_results';
3122: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
3123: --
3124: cursor c_parent_result(c_parent_pk_id number,
3118: ,p_parent_entity_result_id in number
3119: ) is
3120: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
3121: l_proc varchar2(72) := g_package||'create_rate_results';
3122: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
3123: --
3124: cursor c_parent_result(c_parent_pk_id number,
3125: c_parent_table_name varchar2,
3126: c_copy_entity_txn_id number) is
3124: cursor c_parent_result(c_parent_pk_id number,
3125: c_parent_table_name varchar2,
3126: c_copy_entity_txn_id number) is
3127: select copy_entity_result_id mirror_src_entity_result_id
3128: from ben_copy_entity_results cpe,
3129: pqh_table_route trt
3130: where cpe.information1= c_parent_pk_id
3131: and cpe.result_type_cd = 'DISPLAY'
3132: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
3146: ---
3147: l_mirror_src_entity_result_id number(15);
3148: l_table_route_id number(15);
3149: l_result_type_cd varchar2(30);
3150: l_information5 ben_copy_entity_results.information5%type;
3151: l_number_of_copies number(15);
3152: ---------------------------------------------------------------
3153: -- START OF BEN_ACTL_PREM_F ----------------------
3154: ---------------------------------------------------------------
3165: where apr.actl_prem_id = c_actl_prem_id
3166: -- and apr.business_group_id = p_business_group_id
3167: and not exists (
3168: select /* */ null
3169: from ben_copy_entity_results cpe
3170: -- pqh_table_route trt
3171: where copy_entity_txn_id = p_copy_entity_txn_id
3172: -- and trt.table_route_id = cpe.table_route_id
3173: and ( -- c_mirror_src_entity_result_id is null or
3198: where pmr.prtl_mo_rt_prtn_val_id = c_prtl_mo_rt_prtn_val_id
3199: -- and pmr.business_group_id = p_business_group_id
3200: and not exists (
3201: select /* */ null
3202: from ben_copy_entity_results cpe
3203: -- pqh_table_route trt
3204: where copy_entity_txn_id = p_copy_entity_txn_id
3205: -- and trt.table_route_id = cpe.table_route_id
3206: and ( -- c_mirror_src_entity_result_id is null or
3230: where apv.actl_prem_vrbl_rt_id = c_actl_prem_vrbl_rt_id
3231: -- and apv.business_group_id = p_business_group_id
3232: and not exists (
3233: select /* */ null
3234: from ben_copy_entity_results cpe
3235: -- pqh_table_route trt
3236: where copy_entity_txn_id = p_copy_entity_txn_id
3237: -- and trt.table_route_id = cpe.table_route_id
3238: and ( -- c_mirror_src_entity_result_id is null or
3245: and information3 = apv.effective_end_date);
3246: --
3247: cursor c_apv_dpr(c_actl_prem_vrbl_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
3248: select distinct cpe.information262 vrbl_rt_prfl_id
3249: from ben_copy_entity_results cpe
3250: -- pqh_table_route trt
3251: where copy_entity_txn_id = p_copy_entity_txn_id
3252: -- and trt.table_route_id = cpe.table_route_id
3253: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
3277: where ava.actl_prem_vrbl_rt_rl_id = c_actl_prem_vrbl_rt_rl_id
3278: -- and ava.business_group_id = p_business_group_id
3279: and not exists (
3280: select /* */ null
3281: from ben_copy_entity_results cpe
3282: -- pqh_table_route trt
3283: where copy_entity_txn_id = p_copy_entity_txn_id
3284: -- and trt.table_route_id = cpe.table_route_id
3285: and ( -- c_mirror_src_entity_result_id is null or
3325: end if;
3326: --
3327: l_copy_entity_result_id := null;
3328: l_object_version_number := null;
3329: ben_copy_entity_results_api.create_copy_entity_results(
3330: p_copy_entity_result_id => l_copy_entity_result_id,
3331: p_copy_entity_txn_id => p_copy_entity_txn_id,
3332: p_result_type_cd => l_result_type_cd,
3333: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
3605: end if;
3606: --
3607: l_copy_entity_result_id := null;
3608: l_object_version_number := null;
3609: ben_copy_entity_results_api.create_copy_entity_results(
3610: p_copy_entity_result_id => l_copy_entity_result_id,
3611: p_copy_entity_txn_id => p_copy_entity_txn_id,
3612: p_result_type_cd => l_result_type_cd,
3613: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
3766: end if;
3767: --
3768: l_copy_entity_result_id := null;
3769: l_object_version_number := null;
3770: ben_copy_entity_results_api.create_copy_entity_results(
3771: p_copy_entity_result_id => l_copy_entity_result_id,
3772: p_copy_entity_txn_id => p_copy_entity_txn_id,
3773: p_result_type_cd => l_result_type_cd,
3774: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
3886: end if;
3887: --
3888: l_copy_entity_result_id := null;
3889: l_object_version_number := null;
3890: ben_copy_entity_results_api.create_copy_entity_results(
3891: p_copy_entity_result_id => l_copy_entity_result_id,
3892: p_copy_entity_txn_id => p_copy_entity_txn_id,
3893: p_result_type_cd => l_result_type_cd,
3894: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
4003: ,p_object_version_number out nocopy number
4004: ,p_effective_date in date
4005: ,p_no_dup_rslt in varchar2 default null
4006: ) is
4007: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
4008: l_proc varchar2(72) := g_package||'create_drpar_results';
4009: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
4010: --
4011: cursor c_parent_result(c_parent_pk_id number,
4005: ,p_no_dup_rslt in varchar2 default null
4006: ) is
4007: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
4008: l_proc varchar2(72) := g_package||'create_drpar_results';
4009: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
4010: --
4011: cursor c_parent_result(c_parent_pk_id number,
4012: c_parent_table_name varchar2,
4013: c_copy_entity_txn_id number) is
4011: cursor c_parent_result(c_parent_pk_id number,
4012: c_parent_table_name varchar2,
4013: c_copy_entity_txn_id number) is
4014: select copy_entity_result_id mirror_src_entity_result_id
4015: from ben_copy_entity_results cpe,
4016: pqh_table_route trt
4017: where cpe.information1= c_parent_pk_id
4018: and cpe.result_type_cd = 'DISPLAY'
4019: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
4038: -- c_parent_table_name varchar2,
4039: c_parent_table_alias varchar2,
4040: c_copy_entity_txn_id number) is
4041: select min(cpe.information2) min_esd
4042: from ben_copy_entity_results cpe
4043: -- pqh_table_route trt
4044: where cpe.information1= c_parent_pk_id
4045: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
4046: --and cpe.table_route_id = trt.table_route_id
4052:
4053: l_mirror_src_entity_result_id number(15);
4054: l_table_route_id number(15);
4055: l_result_type_cd varchar2(30);
4056: l_information5 ben_copy_entity_results.information5%type;
4057: l_number_of_copies number(15);
4058:
4059: ---------------------------------------------------------------
4060: -- START OF BEN_AGE_FCTR ----------------------
4066: where agf.age_fctr_id = c_age_fctr_id
4067: -- and agf.business_group_id = p_business_group_id
4068: and not exists (
4069: select /* */ null
4070: from ben_copy_entity_results cpe
4071: -- pqh_table_route trt
4072: where copy_entity_txn_id = p_copy_entity_txn_id
4073: -- and trt.table_route_id = cpe.table_route_id
4074: and ( -- c_mirror_src_entity_result_id is null or
4092: where cla.cmbn_age_los_fctr_id = c_cmbn_age_los_fctr_id
4093: -- and cla.business_group_id = p_business_group_id
4094: and not exists (
4095: select /* */ null
4096: from ben_copy_entity_results cpe
4097: -- pqh_table_route trt
4098: where copy_entity_txn_id = p_copy_entity_txn_id
4099: -- and trt.table_route_id = cpe.table_route_id
4100: and ( -- c_mirror_src_entity_result_id is null or
4120: where clf.comp_lvl_fctr_id = c_comp_lvl_fctr_id
4121: -- and clf.business_group_id = p_business_group_id
4122: and not exists (
4123: select /* */ null
4124: from ben_copy_entity_results cpe
4125: -- pqh_table_route trt
4126: where copy_entity_txn_id = p_copy_entity_txn_id
4127: -- and trt.table_route_id = cpe.table_route_id
4128: and ( -- c_mirror_src_entity_result_id is null or
4136: l_out_clf_result_id number(15);
4137: --
4138: cursor c_clf_drp(c_comp_lvl_fctr_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
4139: select distinct cpe.information225 bnfts_bal_id
4140: from ben_copy_entity_results cpe
4141: -- pqh_table_route trt
4142: where copy_entity_txn_id = p_copy_entity_txn_id
4143: -- and trt.table_route_id = cpe.table_route_id
4144: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
4159: where hwf.hrs_wkd_in_perd_fctr_id = c_hrs_wkd_in_perd_fctr_id
4160: -- and hwf.business_group_id = p_business_group_id
4161: and not exists (
4162: select /* */ null
4163: from ben_copy_entity_results cpe
4164: -- pqh_table_route trt
4165: where copy_entity_txn_id = p_copy_entity_txn_id
4166: -- and trt.table_route_id = cpe.table_route_id
4167: and ( -- c_mirror_src_entity_result_id is null or
4176: l_out_hwf_result_id number(15);
4177: --
4178: cursor c_hwf_drp(c_hrs_wkd_in_perd_fctr_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
4179: select distinct cpe.information225 bnfts_bal_id
4180: from ben_copy_entity_results cpe
4181: -- pqh_table_route trt
4182: where copy_entity_txn_id = p_copy_entity_txn_id
4183: -- and trt.table_route_id = cpe.table_route_id
4184: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
4200: where lsf.los_fctr_id = c_los_fctr_id
4201: -- and lsf.business_group_id = p_business_group_id
4202: and not exists (
4203: select /* */ null
4204: from ben_copy_entity_results cpe
4205: -- pqh_table_route trt
4206: where copy_entity_txn_id = p_copy_entity_txn_id
4207: -- and trt.table_route_id = cpe.table_route_id
4208: and ( -- c_mirror_src_entity_result_id is null or
4226: where pff.pct_fl_tm_fctr_id = c_pct_fl_tm_fctr_id
4227: -- and pff.business_group_id = p_business_group_id
4228: and not exists (
4229: select /* */ null
4230: from ben_copy_entity_results cpe
4231: -- pqh_table_route trt
4232: where copy_entity_txn_id = p_copy_entity_txn_id
4233: -- and trt.table_route_id = cpe.table_route_id
4234: and ( -- c_mirror_src_entity_result_id is null or
4271:
4272: cursor c_object_exists(c_pk_id number,
4273: c_table_alias varchar2) is
4274: select null
4275: from ben_copy_entity_results cpe
4276: -- pqh_table_route trt
4277: where copy_entity_txn_id = p_copy_entity_txn_id
4278: -- and trt.table_route_id = cpe.table_route_id
4279: and cpe.table_alias = c_table_alias
4320: l_result_type_cd := 'DISPLAY';
4321: --
4322: l_copy_entity_result_id := null;
4323: l_object_version_number := null;
4324: ben_copy_entity_results_api.create_copy_entity_results(
4325: p_copy_entity_result_id => l_copy_entity_result_id,
4326: p_copy_entity_txn_id => p_copy_entity_txn_id,
4327: p_result_type_cd => l_result_type_cd,
4328: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
4493: l_result_type_cd := 'DISPLAY';
4494: --
4495: l_copy_entity_result_id := null;
4496: l_object_version_number := null;
4497: ben_copy_entity_results_api.create_copy_entity_results(
4498: p_copy_entity_result_id => l_copy_entity_result_id,
4499: p_copy_entity_txn_id => p_copy_entity_txn_id,
4500: p_result_type_cd => l_result_type_cd,
4501: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
4680: fetch c_parent_esd into l_bnb_bnfts_bal_esd;
4681: close c_parent_esd;
4682: end if;
4683: --
4684: ben_copy_entity_results_api.create_copy_entity_results(
4685: p_copy_entity_result_id => l_copy_entity_result_id,
4686: p_copy_entity_txn_id => p_copy_entity_txn_id,
4687: p_result_type_cd => l_result_type_cd,
4688: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
4922: fetch c_parent_esd into l_bnb_bnfts_bal_esd;
4923: close c_parent_esd;
4924: end if;
4925:
4926: ben_copy_entity_results_api.create_copy_entity_results(
4927: p_copy_entity_result_id => l_copy_entity_result_id,
4928: p_copy_entity_txn_id => p_copy_entity_txn_id,
4929: p_result_type_cd => l_result_type_cd,
4930: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
5124: l_result_type_cd := 'DISPLAY';
5125: --
5126: l_copy_entity_result_id := null;
5127: l_object_version_number := null;
5128: ben_copy_entity_results_api.create_copy_entity_results(
5129: p_copy_entity_result_id => l_copy_entity_result_id,
5130: p_copy_entity_txn_id => p_copy_entity_txn_id,
5131: p_result_type_cd => l_result_type_cd,
5132: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
5322: l_result_type_cd := 'DISPLAY';
5323: --
5324: l_copy_entity_result_id := null;
5325: l_object_version_number := null;
5326: ben_copy_entity_results_api.create_copy_entity_results(
5327: p_copy_entity_result_id => l_copy_entity_result_id,
5328: p_copy_entity_txn_id => p_copy_entity_txn_id,
5329: p_result_type_cd => l_result_type_cd,
5330: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
5436: ,p_number_of_copies in number default 0
5437: ,p_object_version_number out nocopy number
5438: ,p_effective_date in date
5439: ) is
5440: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
5441: l_proc varchar2(72) := g_package||'create_vrb_rt_elg_prf_results';
5442: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
5443: --
5444: ---------------------------------------------------------------
5438: ,p_effective_date in date
5439: ) is
5440: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
5441: l_proc varchar2(72) := g_package||'create_vrb_rt_elg_prf_results';
5442: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
5443: --
5444: ---------------------------------------------------------------
5445: -- START OF BEN_VRBL_RT_ELIG_PRFL_F ----------------------
5446: ---------------------------------------------------------------
5456: where vep.vrbl_rt_elig_prfl_id = c_vrbl_rt_elig_prfl_id
5457: -- and vep.business_group_id = p_business_group_id
5458: and not exists (
5459: select /* */ null
5460: from ben_copy_entity_results cpe
5461: -- pqh_table_route trt
5462: where copy_entity_txn_id = p_copy_entity_txn_id
5463: -- and trt.table_route_id = cpe.table_route_id
5464: and ( -- c_mirror_src_entity_result_id is null or
5496:
5497: l_mirror_src_entity_result_id number(15);
5498: l_table_route_id number(15);
5499: l_result_type_cd varchar2(30);
5500: l_information5 ben_copy_entity_results.information5%type;
5501: l_number_of_copies number(15);
5502:
5503: l_mndtry_flag ben_vrbl_rt_elig_prfl_f.mndtry_flag%type;
5504:
5552: end if;
5553: --
5554: l_copy_entity_result_id := null;
5555: l_object_version_number := null;
5556: ben_copy_entity_results_api.create_copy_entity_results(
5557: p_copy_entity_result_id => l_copy_entity_result_id,
5558: p_copy_entity_txn_id => p_copy_entity_txn_id,
5559: p_result_type_cd => l_result_type_cd,
5560: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
5673: ,p_effective_date in date
5674: ,p_parent_entity_result_id in number
5675: ,p_no_dup_rslt in varchar2 default null
5676: ) is
5677: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
5678: l_proc varchar2(72) := g_package||'create_rate_results';
5679: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
5680: --
5681: cursor c_parent_result(c_parent_pk_id number,
5675: ,p_no_dup_rslt in varchar2 default null
5676: ) is
5677: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
5678: l_proc varchar2(72) := g_package||'create_rate_results';
5679: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
5680: --
5681: cursor c_parent_result(c_parent_pk_id number,
5682: c_parent_table_name varchar2,
5683: c_copy_entity_txn_id number) is
5681: cursor c_parent_result(c_parent_pk_id number,
5682: c_parent_table_name varchar2,
5683: c_copy_entity_txn_id number) is
5684: select copy_entity_result_id mirror_src_entity_result_id
5685: from ben_copy_entity_results cpe,
5686: pqh_table_route trt
5687: where cpe.information1= c_parent_pk_id
5688: and cpe.result_type_cd = 'DISPLAY'
5689: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
5703: ---
5704: l_mirror_src_entity_result_id number(15);
5705: l_table_route_id number(15);
5706: l_result_type_cd varchar2(30);
5707: l_information5 ben_copy_entity_results.information5%type;
5708: l_number_of_copies number(15);
5709: l_mapping_id number;
5710: l_mapping_name varchar2(600);
5711: l_mapping_id1 number;
5723: where vpf.vrbl_rt_prfl_id = c_vrbl_rt_prfl_id
5724: -- and vpf.business_group_id = p_business_group_id
5725: and not exists (
5726: select /* */ null
5727: from ben_copy_entity_results cpe
5728: -- pqh_table_route trt
5729: where copy_entity_txn_id = p_copy_entity_txn_id
5730: -- and trt.table_route_id = cpe.table_route_id
5731: and ( -- c_mirror_src_entity_result_id is null or
5756: where art.age_rt_id = c_age_rt_id
5757: -- and art.business_group_id = p_business_group_id
5758: and not exists (
5759: select /* */ null
5760: from ben_copy_entity_results cpe
5761: -- pqh_table_route trt
5762: where copy_entity_txn_id = p_copy_entity_txn_id
5763: -- and trt.table_route_id = cpe.table_route_id
5764: and ( -- c_mirror_src_entity_result_id is null or
5774: --
5775: cursor c_art_drp(c_age_rt_id number,c_mirror_src_entity_result_id number,
5776: c_table_alias varchar2 ) is
5777: select distinct cpe.information246 age_fctr_id
5778: from ben_copy_entity_results cpe
5779: -- pqh_table_route trt
5780: where copy_entity_txn_id = p_copy_entity_txn_id
5781: -- and trt.table_route_id = cpe.table_route_id
5782: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
5803: where asr.asnt_set_rt_id = c_asnt_set_rt_id
5804: -- and asr.business_group_id = p_business_group_id
5805: and not exists (
5806: select /* */ null
5807: from ben_copy_entity_results cpe
5808: -- pqh_table_route trt
5809: where copy_entity_txn_id = p_copy_entity_txn_id
5810: -- and trt.table_route_id = cpe.table_route_id
5811: and ( -- c_mirror_src_entity_result_id is null or
5845: where brg.benfts_grp_rt_id = c_benfts_grp_rt_id
5846: -- and brg.business_group_id = p_business_group_id
5847: and not exists (
5848: select /* */ null
5849: from ben_copy_entity_results cpe
5850: -- pqh_table_route trt
5851: where copy_entity_txn_id = p_copy_entity_txn_id
5852: -- and trt.table_route_id = cpe.table_route_id
5853: and ( -- c_mirror_src_entity_result_id is null or
5862: l_out_brg_result_id number(15);
5863: --
5864: cursor c_brg_bg(c_benfts_grp_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
5865: select distinct cpe.information222 benfts_grp_id
5866: from ben_copy_entity_results cpe
5867: -- pqh_table_route trt
5868: where copy_entity_txn_id = p_copy_entity_txn_id
5869: -- and trt.table_route_id = cpe.table_route_id
5870: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
5892: where bur.brgng_unit_rt_id = c_brgng_unit_rt_id
5893: -- and bur.business_group_id = p_business_group_id
5894: and not exists (
5895: select /* */ null
5896: from ben_copy_entity_results cpe
5897: -- pqh_table_route trt
5898: where copy_entity_txn_id = p_copy_entity_txn_id
5899: -- and trt.table_route_id = cpe.table_route_id
5900: and ( -- c_mirror_src_entity_result_id is null or
5924: where cmr.cmbn_age_los_rt_id = c_cmbn_age_los_rt_id
5925: -- and cmr.business_group_id = p_business_group_id
5926: and not exists (
5927: select /* */ null
5928: from ben_copy_entity_results cpe
5929: -- pqh_table_route trt
5930: where copy_entity_txn_id = p_copy_entity_txn_id
5931: -- and trt.table_route_id = cpe.table_route_id
5932: and ( -- c_mirror_src_entity_result_id is null or
5941: l_out_cmr_result_id number(15);
5942: --
5943: cursor c_cmr_drp(c_cmbn_age_los_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
5944: select distinct cpe.information223 cmbn_age_los_fctr_id
5945: from ben_copy_entity_results cpe
5946: -- pqh_table_route trt
5947: where copy_entity_txn_id = p_copy_entity_txn_id
5948: -- and trt.table_route_id = cpe.table_route_id
5949: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
5970: where clr.comp_lvl_rt_id = c_comp_lvl_rt_id
5971: -- and clr.business_group_id = p_business_group_id
5972: and not exists (
5973: select /* */ null
5974: from ben_copy_entity_results cpe
5975: -- pqh_table_route trt
5976: where copy_entity_txn_id = p_copy_entity_txn_id
5977: -- and trt.table_route_id = cpe.table_route_id
5978: and ( -- c_mirror_src_entity_result_id is null or
5987: l_out_clr_result_id number(15);
5988: --
5989: cursor c_clr_drp(c_comp_lvl_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
5990: select distinct cpe.information254 comp_lvl_fctr_id
5991: from ben_copy_entity_results cpe
5992: -- pqh_table_route trt
5993: where copy_entity_txn_id = p_copy_entity_txn_id
5994: -- and trt.table_route_id = cpe.table_route_id
5995: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
6016: where dbr.dsbld_rt_id = c_dsbld_rt_id
6017: -- and dbr.business_group_id = p_business_group_id
6018: and not exists (
6019: select /* */ null
6020: from ben_copy_entity_results cpe
6021: -- pqh_table_route trt
6022: where copy_entity_txn_id = p_copy_entity_txn_id
6023: -- and trt.table_route_id = cpe.table_route_id
6024: and ( -- c_mirror_src_entity_result_id is null or
6049: where esr.ee_stat_rt_id = c_ee_stat_rt_id
6050: -- and esr.business_group_id = p_business_group_id
6051: and not exists (
6052: select /* */ null
6053: from ben_copy_entity_results cpe
6054: -- pqh_table_route trt
6055: where copy_entity_txn_id = p_copy_entity_txn_id
6056: -- and trt.table_route_id = cpe.table_route_id
6057: and ( -- c_mirror_src_entity_result_id is null or
6105: where ftr.fl_tm_pt_tm_rt_id = c_fl_tm_pt_tm_rt_id
6106: -- and ftr.business_group_id = p_business_group_id
6107: and not exists (
6108: select /* */ null
6109: from ben_copy_entity_results cpe
6110: -- pqh_table_route trt
6111: where copy_entity_txn_id = p_copy_entity_txn_id
6112: -- and trt.table_route_id = cpe.table_route_id
6113: and ( -- c_mirror_src_entity_result_id is null or
6139: where gnr.gndr_rt_id = c_gndr_rt_id
6140: -- and gnr.business_group_id = p_business_group_id
6141: and not exists (
6142: select /* */ null
6143: from ben_copy_entity_results cpe
6144: -- pqh_table_route trt
6145: where copy_entity_txn_id = p_copy_entity_txn_id
6146: -- and trt.table_route_id = cpe.table_route_id
6147: and ( -- c_mirror_src_entity_result_id is null or
6172: where grr.grade_rt_id = c_grade_rt_id
6173: -- and grr.business_group_id = p_business_group_id
6174: and not exists (
6175: select /* */ null
6176: from ben_copy_entity_results cpe
6177: -- pqh_table_route trt
6178: where copy_entity_txn_id = p_copy_entity_txn_id
6179: -- and trt.table_route_id = cpe.table_route_id
6180: and ( -- c_mirror_src_entity_result_id is null or
6220: where hsr.hrly_slrd_rt_id = c_hrly_slrd_rt_id
6221: -- and hsr.business_group_id = p_business_group_id
6222: and not exists (
6223: select /* */ null
6224: from ben_copy_entity_results cpe
6225: -- pqh_table_route trt
6226: where copy_entity_txn_id = p_copy_entity_txn_id
6227: -- and trt.table_route_id = cpe.table_route_id
6228: and ( -- c_mirror_src_entity_result_id is null or
6252: where hwr.hrs_wkd_in_perd_rt_id = c_hrs_wkd_in_perd_rt_id
6253: -- and hwr.business_group_id = p_business_group_id
6254: and not exists (
6255: select /* */ null
6256: from ben_copy_entity_results cpe
6257: -- pqh_table_route trt
6258: where copy_entity_txn_id = p_copy_entity_txn_id
6259: -- and trt.table_route_id = cpe.table_route_id
6260: and ( -- c_mirror_src_entity_result_id is null or
6269: l_out_hwr_result_id number(15);
6270: --
6271: cursor c_hwr_drp(c_hrs_wkd_in_perd_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
6272: select distinct cpe.information224 hrs_wkd_in_perd_fctr_id
6273: from ben_copy_entity_results cpe
6274: -- pqh_table_route trt
6275: where copy_entity_txn_id = p_copy_entity_txn_id
6276: -- and trt.table_route_id = cpe.table_route_id
6277: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
6298: where lmm.lbr_mmbr_rt_id = c_lbr_mmbr_rt_id
6299: -- and lmm.business_group_id = p_business_group_id
6300: and not exists (
6301: select /* */ null
6302: from ben_copy_entity_results cpe
6303: -- pqh_table_route trt
6304: where copy_entity_txn_id = p_copy_entity_txn_id
6305: -- and trt.table_route_id = cpe.table_route_id
6306: and ( -- c_mirror_src_entity_result_id is null or
6331: where ler.lgl_enty_rt_id = c_lgl_enty_rt_id
6332: -- and ler.business_group_id = p_business_group_id
6333: and not exists (
6334: select /* */ null
6335: from ben_copy_entity_results cpe
6336: -- pqh_table_route trt
6337: where copy_entity_txn_id = p_copy_entity_txn_id
6338: -- and trt.table_route_id = cpe.table_route_id
6339: and ( -- c_mirror_src_entity_result_id is null or
6385: where lar.loa_rsn_rt_id = c_loa_rsn_rt_id
6386: -- and lar.business_group_id = p_business_group_id
6387: and not exists (
6388: select /* */ null
6389: from ben_copy_entity_results cpe
6390: -- pqh_table_route trt
6391: where copy_entity_txn_id = p_copy_entity_txn_id
6392: -- and trt.table_route_id = cpe.table_route_id
6393: and ( -- c_mirror_src_entity_result_id is null or
6452: where lsr.los_rt_id = c_los_rt_id
6453: -- and lsr.business_group_id = p_business_group_id
6454: and not exists (
6455: select /* */ null
6456: from ben_copy_entity_results cpe
6457: -- pqh_table_route trt
6458: where copy_entity_txn_id = p_copy_entity_txn_id
6459: -- and trt.table_route_id = cpe.table_route_id
6460: and ( -- c_mirror_src_entity_result_id is null or
6470: --
6471: cursor c_lsr_drp(c_los_rt_id number,c_mirror_src_entity_result_id number,
6472: c_table_alias varchar2 ) is
6473: select distinct cpe.information243 los_fctr_id
6474: from ben_copy_entity_results cpe
6475: -- pqh_table_route trt
6476: where copy_entity_txn_id = p_copy_entity_txn_id
6477: -- and trt.table_route_id = cpe.table_route_id
6478: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
6499: where lrn.lvg_rsn_rt_id = c_lvg_rsn_rt_id
6500: -- and lrn.business_group_id = p_business_group_id
6501: and not exists (
6502: select /* */ null
6503: from ben_copy_entity_results cpe
6504: -- pqh_table_route trt
6505: where copy_entity_txn_id = p_copy_entity_txn_id
6506: -- and trt.table_route_id = cpe.table_route_id
6507: and ( -- c_mirror_src_entity_result_id is null or
6532: where our.org_unit_rt_id = c_org_unit_rt_id
6533: -- and our.business_group_id = p_business_group_id
6534: and not exists (
6535: select /* */ null
6536: from ben_copy_entity_results cpe
6537: -- pqh_table_route trt
6538: where copy_entity_txn_id = p_copy_entity_txn_id
6539: -- and trt.table_route_id = cpe.table_route_id
6540: and ( -- c_mirror_src_entity_result_id is null or
6580: where pfr.pct_fl_tm_rt_id = c_pct_fl_tm_rt_id
6581: -- and pfr.business_group_id = p_business_group_id
6582: and not exists (
6583: select /* */ null
6584: from ben_copy_entity_results cpe
6585: -- pqh_table_route trt
6586: where copy_entity_txn_id = p_copy_entity_txn_id
6587: -- and trt.table_route_id = cpe.table_route_id
6588: and ( -- c_mirror_src_entity_result_id is null or
6597: l_out_pfr_result_id number(15);
6598: --
6599: cursor c_pfr_drp(c_pct_fl_tm_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
6600: select distinct cpe.information233 pct_fl_tm_fctr_id
6601: from ben_copy_entity_results cpe
6602: -- pqh_table_route trt
6603: where copy_entity_txn_id = p_copy_entity_txn_id
6604: -- and trt.table_route_id = cpe.table_route_id
6605: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
6626: where ptr.per_typ_rt_id = c_per_typ_rt_id
6627: -- and ptr.business_group_id = p_business_group_id
6628: and not exists (
6629: select /* */ null
6630: from ben_copy_entity_results cpe
6631: -- pqh_table_route trt
6632: where copy_entity_txn_id = p_copy_entity_txn_id
6633: -- and trt.table_route_id = cpe.table_route_id
6634: and ( -- c_mirror_src_entity_result_id is null or
6679: where prt.poe_rt_id = c_poe_rt_id
6680: -- and prt.business_group_id = p_business_group_id
6681: and not exists (
6682: select /* */ null
6683: from ben_copy_entity_results cpe
6684: -- pqh_table_route trt
6685: where copy_entity_txn_id = p_copy_entity_txn_id
6686: -- and trt.table_route_id = cpe.table_route_id
6687: and ( -- c_mirror_src_entity_result_id is null or
6712: where pgr.ppl_grp_rt_id = c_ppl_grp_rt_id
6713: -- and pgr.business_group_id = p_business_group_id
6714: and not exists (
6715: select /* */ null
6716: from ben_copy_entity_results cpe
6717: -- pqh_table_route trt
6718: where copy_entity_txn_id = p_copy_entity_txn_id
6719: -- and trt.table_route_id = cpe.table_route_id
6720: and ( -- c_mirror_src_entity_result_id is null or
6745: where pzr.pstl_zip_rt_id = c_pstl_zip_rt_id
6746: -- and pzr.business_group_id = p_business_group_id
6747: and not exists (
6748: select /* */ null
6749: from ben_copy_entity_results cpe
6750: -- pqh_table_route trt
6751: where copy_entity_txn_id = p_copy_entity_txn_id
6752: -- and trt.table_route_id = cpe.table_route_id
6753: and ( -- c_mirror_src_entity_result_id is null or
6762: l_out_pzr_result_id number(15);
6763: --
6764: cursor c_pzr_pstl(c_pstl_zip_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
6765: select distinct cpe.information245 pstl_zip_rng_id
6766: from ben_copy_entity_results cpe
6767: -- pqh_table_route trt
6768: where copy_entity_txn_id = p_copy_entity_txn_id
6769: -- and trt.table_route_id = cpe.table_route_id
6770: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
6791: where pr_.pyrl_rt_id = c_pyrl_rt_id
6792: -- and pr_.business_group_id = p_business_group_id
6793: and not exists (
6794: select /* */ null
6795: from ben_copy_entity_results cpe
6796: -- pqh_table_route trt
6797: where copy_entity_txn_id = p_copy_entity_txn_id
6798: -- and trt.table_route_id = cpe.table_route_id
6799: and ( -- c_mirror_src_entity_result_id is null or
6839: where pbr.py_bss_rt_id = c_py_bss_rt_id
6840: -- and pbr.business_group_id = p_business_group_id
6841: and not exists (
6842: select /* */ null
6843: from ben_copy_entity_results cpe
6844: -- pqh_table_route trt
6845: where copy_entity_txn_id = p_copy_entity_txn_id
6846: -- and trt.table_route_id = cpe.table_route_id
6847: and ( -- c_mirror_src_entity_result_id is null or
6880: where shr.schedd_hrs_rt_id = c_schedd_hrs_rt_id
6881: -- and shr.business_group_id = p_business_group_id
6882: and not exists (
6883: select /* */ null
6884: from ben_copy_entity_results cpe
6885: -- pqh_table_route trt
6886: where copy_entity_txn_id = p_copy_entity_txn_id
6887: -- and trt.table_route_id = cpe.table_route_id
6888: and ( -- c_mirror_src_entity_result_id is null or
6913: where sar.svc_area_rt_id = c_svc_area_rt_id
6914: -- and sar.business_group_id = p_business_group_id
6915: and not exists (
6916: select /* */ null
6917: from ben_copy_entity_results cpe
6918: -- pqh_table_route trt
6919: where copy_entity_txn_id = p_copy_entity_txn_id
6920: -- and trt.table_route_id = cpe.table_route_id
6921: and ( -- c_mirror_src_entity_result_id is null or
6930: l_out_sar_result_id number(15);
6931: --
6932: cursor c_sar_srv(c_svc_area_rt_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
6933: select distinct cpe.information241 svc_area_id
6934: from ben_copy_entity_results cpe
6935: -- pqh_table_route trt
6936: where copy_entity_txn_id = p_copy_entity_txn_id
6937: -- and trt.table_route_id = cpe.table_route_id
6938: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
6959: where tur.tbco_use_rt_id = c_tbco_use_rt_id
6960: -- and tur.business_group_id = p_business_group_id
6961: and not exists (
6962: select /* */ null
6963: from ben_copy_entity_results cpe
6964: -- pqh_table_route trt
6965: where copy_entity_txn_id = p_copy_entity_txn_id
6966: -- and trt.table_route_id = cpe.table_route_id
6967: and ( -- c_mirror_src_entity_result_id is null or
6992: where tcv.ttl_cvg_vol_rt_id = c_ttl_cvg_vol_rt_id
6993: -- and tcv.business_group_id = p_business_group_id
6994: and not exists (
6995: select /* */ null
6996: from ben_copy_entity_results cpe
6997: -- pqh_table_route trt
6998: where copy_entity_txn_id = p_copy_entity_txn_id
6999: -- and trt.table_route_id = cpe.table_route_id
7000: and ( -- c_mirror_src_entity_result_id is null or
7025: where ttp.ttl_prtt_rt_id = c_ttl_prtt_rt_id
7026: -- and ttp.business_group_id = p_business_group_id
7027: and not exists (
7028: select /* */ null
7029: from ben_copy_entity_results cpe
7030: -- pqh_table_route trt
7031: where copy_entity_txn_id = p_copy_entity_txn_id
7032: -- and trt.table_route_id = cpe.table_route_id
7033: and ( -- c_mirror_src_entity_result_id is null or
7058: where vmr.vrbl_mtchg_rt_id = c_vrbl_mtchg_rt_id
7059: -- and vmr.business_group_id = p_business_group_id
7060: and not exists (
7061: select /* */ null
7062: from ben_copy_entity_results cpe
7063: -- pqh_table_route trt
7064: where copy_entity_txn_id = p_copy_entity_txn_id
7065: -- and trt.table_route_id = cpe.table_route_id
7066: and ( -- c_mirror_src_entity_result_id is null or
7090: where vpr.vrbl_rt_prfl_rl_id = c_vrbl_rt_prfl_rl_id
7091: -- and vpr.business_group_id = p_business_group_id
7092: and not exists (
7093: select /* */ null
7094: from ben_copy_entity_results cpe
7095: -- pqh_table_route trt
7096: where copy_entity_txn_id = p_copy_entity_txn_id
7097: -- and trt.table_route_id = cpe.table_route_id
7098: and ( -- c_mirror_src_entity_result_id is null or
7123: where wlr.wk_loc_rt_id = c_wk_loc_rt_id
7124: -- and wlr.business_group_id = p_business_group_id
7125: and not exists (
7126: select /* */ null
7127: from ben_copy_entity_results cpe
7128: -- pqh_table_route trt
7129: where copy_entity_txn_id = p_copy_entity_txn_id
7130: -- and trt.table_route_id = cpe.table_route_id
7131: and ( -- c_mirror_src_entity_result_id is null or
7171: where cty.comptncy_rt_id = c_comptncy_rt_id
7172: -- and cty.business_group_id = p_business_group_id
7173: and not exists (
7174: select /* */ null
7175: from ben_copy_entity_results cpe
7176: -- pqh_table_route trt
7177: where copy_entity_txn_id = p_copy_entity_txn_id
7178: -- and trt.table_route_id = cpe.table_route_id
7179: and ( -- c_mirror_src_entity_result_id is null or
7225: where jrt.job_rt_id = c_job_rt_id
7226: -- and jrt.business_group_id = p_business_group_id
7227: and not exists (
7228: select /* */ null
7229: from ben_copy_entity_results cpe
7230: -- pqh_table_route trt
7231: where copy_entity_txn_id = p_copy_entity_txn_id
7232: -- and trt.table_route_id = cpe.table_route_id
7233: and ( -- c_mirror_src_entity_result_id is null or
7277: where pst.pstn_rt_id = c_pstn_rt_id
7278: -- and pst.business_group_id = p_business_group_id
7279: and not exists (
7280: select /* */ null
7281: from ben_copy_entity_results cpe
7282: -- pqh_table_route trt
7283: where copy_entity_txn_id = p_copy_entity_txn_id
7284: -- and trt.table_route_id = cpe.table_route_id
7285: and ( -- c_mirror_src_entity_result_id is null or
7325: where qtr.qual_titl_rt_id = c_qual_titl_rt_id
7326: --and qtr.business_group_id = p_business_group_id
7327: and not exists (
7328: select /* */ null
7329: from ben_copy_entity_results cpe
7330: -- pqh_table_route trt
7331: where copy_entity_txn_id = p_copy_entity_txn_id
7332: -- and trt.table_route_id = cpe.table_route_id
7333: and ( -- c_mirror_src_entity_result_id is null or
7365: where cqr.cbr_quald_bnf_rt_id = c_cbr_quald_bnf_rt_id
7366: --and cqr.business_group_id = p_business_group_id
7367: and not exists (
7368: select /* */ null
7369: from ben_copy_entity_results cpe
7370: -- pqh_table_route trt
7371: where copy_entity_txn_id = p_copy_entity_txn_id
7372: -- and trt.table_route_id = cpe.table_route_id
7373: and ( -- c_mirror_src_entity_result_id is null or
7397: where cpn.cntng_prtn_prfl_rt_id = c_cntng_prtn_prfl_rt_id
7398: --and cpn.business_group_id = p_business_group_id
7399: and not exists (
7400: select /* */ null
7401: from ben_copy_entity_results cpe
7402: -- pqh_table_route trt
7403: where copy_entity_txn_id = p_copy_entity_txn_id
7404: -- and trt.table_route_id = cpe.table_route_id
7405: and ( -- c_mirror_src_entity_result_id is null or
7429: where dcl.dpnt_cvrd_othr_pl_rt_id = c_dpnt_cvrd_othr_pl_rt_id
7430: --and dcl.business_group_id = p_business_group_id
7431: and not exists (
7432: select /* */ null
7433: from ben_copy_entity_results cpe
7434: -- pqh_table_route trt
7435: where copy_entity_txn_id = p_copy_entity_txn_id
7436: -- and trt.table_route_id = cpe.table_route_id
7437: and ( -- c_mirror_src_entity_result_id is null or
7461: where dcp.dpnt_cvrd_plip_rt_id = c_dpnt_cvrd_plip_rt_id
7462: --and dcp.business_group_id = p_business_group_id
7463: and not exists (
7464: select /* */ null
7465: from ben_copy_entity_results cpe
7466: -- pqh_table_route trt
7467: where copy_entity_txn_id = p_copy_entity_txn_id
7468: -- and trt.table_route_id = cpe.table_route_id
7469: and ( -- c_mirror_src_entity_result_id is null or
7493: where dco.dpnt_cvrd_othr_ptip_rt_id = c_dpnt_cvrd_othr_ptip_rt_id
7494: --and dco.business_group_id = p_business_group_id
7495: and not exists (
7496: select /* */ null
7497: from ben_copy_entity_results cpe
7498: -- pqh_table_route trt
7499: where copy_entity_txn_id = p_copy_entity_txn_id
7500: -- and trt.table_route_id = cpe.table_route_id
7501: and ( -- c_mirror_src_entity_result_id is null or
7525: where dop.dpnt_cvrd_othr_pgm_rt_id = c_dpnt_cvrd_othr_pgm_rt_id
7526: --and dop.business_group_id = p_business_group_id
7527: and not exists (
7528: select /* */ null
7529: from ben_copy_entity_results cpe
7530: -- pqh_table_route trt
7531: where copy_entity_txn_id = p_copy_entity_txn_id
7532: -- and trt.table_route_id = cpe.table_route_id
7533: and ( -- c_mirror_src_entity_result_id is null or
7557: where pap.prtt_anthr_pl_rt_id = c_prtt_anthr_pl_rt_id
7558: --and pap.business_group_id = p_business_group_id
7559: and not exists (
7560: select /* */ null
7561: from ben_copy_entity_results cpe
7562: -- pqh_table_route trt
7563: where copy_entity_txn_id = p_copy_entity_txn_id
7564: -- and trt.table_route_id = cpe.table_route_id
7565: and ( -- c_mirror_src_entity_result_id is null or
7589: where eao.enrld_anthr_oipl_rt_id = c_enrld_anthr_oipl_rt_id
7590: --and eao.business_group_id = p_business_group_id
7591: and not exists (
7592: select /* */ null
7593: from ben_copy_entity_results cpe
7594: -- pqh_table_route trt
7595: where copy_entity_txn_id = p_copy_entity_txn_id
7596: -- and trt.table_route_id = cpe.table_route_id
7597: and ( -- c_mirror_src_entity_result_id is null or
7621: where enl.enrld_anthr_pl_rt_id = c_enrld_anthr_pl_rt_id
7622: --and enl.business_group_id = p_business_group_id
7623: and not exists (
7624: select /* */ null
7625: from ben_copy_entity_results cpe
7626: -- pqh_table_route trt
7627: where copy_entity_txn_id = p_copy_entity_txn_id
7628: -- and trt.table_route_id = cpe.table_route_id
7629: and ( -- c_mirror_src_entity_result_id is null or
7653: where ear.enrld_anthr_plip_rt_id = c_enrld_anthr_plip_rt_id
7654: --and ear.business_group_id = p_business_group_id
7655: and not exists (
7656: select /* */ null
7657: from ben_copy_entity_results cpe
7658: -- pqh_table_route trt
7659: where copy_entity_txn_id = p_copy_entity_txn_id
7660: -- and trt.table_route_id = cpe.table_route_id
7661: and ( -- c_mirror_src_entity_result_id is null or
7685: where ent.enrld_anthr_ptip_rt_id = c_enrld_anthr_ptip_rt_id
7686: --and ent.business_group_id = p_business_group_id
7687: and not exists (
7688: select /* */ null
7689: from ben_copy_entity_results cpe
7690: -- pqh_table_route trt
7691: where copy_entity_txn_id = p_copy_entity_txn_id
7692: -- and trt.table_route_id = cpe.table_route_id
7693: and ( -- c_mirror_src_entity_result_id is null or
7717: where epm.enrld_anthr_pgm_rt_id = c_enrld_anthr_pgm_rt_id
7718: --and epm.business_group_id = p_business_group_id
7719: and not exists (
7720: select /* */ null
7721: from ben_copy_entity_results cpe
7722: -- pqh_table_route trt
7723: where copy_entity_txn_id = p_copy_entity_txn_id
7724: -- and trt.table_route_id = cpe.table_route_id
7725: and ( -- c_mirror_src_entity_result_id is null or
7749: where noc.no_othr_cvg_rt_id = c_no_othr_cvg_rt_id
7750: --and noc.business_group_id = p_business_group_id
7751: and not exists (
7752: select /* */ null
7753: from ben_copy_entity_results cpe
7754: -- pqh_table_route trt
7755: where copy_entity_txn_id = p_copy_entity_txn_id
7756: -- and trt.table_route_id = cpe.table_route_id
7757: and ( -- c_mirror_src_entity_result_id is null or
7782: where omr.optd_mdcr_rt_id = c_optd_mdcr_rt_id
7783: --and omr.business_group_id = p_business_group_id
7784: and not exists (
7785: select /* */ null
7786: from ben_copy_entity_results cpe
7787: -- pqh_table_route trt
7788: where copy_entity_txn_id = p_copy_entity_txn_id
7789: -- and trt.table_route_id = cpe.table_route_id
7790: and ( -- c_mirror_src_entity_result_id is null or
7814: where dot.dpnt_othr_ptip_rt_id = c_dpnt_othr_ptip_rt_id
7815: --and dot.business_group_id = p_business_group_id
7816: and not exists (
7817: select /* */ null
7818: from ben_copy_entity_results cpe
7819: -- pqh_table_route trt
7820: where copy_entity_txn_id = p_copy_entity_txn_id
7821: -- and trt.table_route_id = cpe.table_route_id
7822: and ( -- c_mirror_src_entity_result_id is null or
7847: where prr.perf_rtng_rt_id = c_perf_rtng_rt_id
7848: --and prr.business_group_id = p_business_group_id
7849: and not exists (
7850: select /* */ null
7851: from ben_copy_entity_results cpe
7852: -- pqh_table_route trt
7853: where copy_entity_txn_id = p_copy_entity_txn_id
7854: -- and trt.table_route_id = cpe.table_route_id
7855: and ( -- c_mirror_src_entity_result_id is null or
7880: where qig.qua_in_gr_rt_id = c_qua_in_gr_rt_id
7881: --and qig.business_group_id = p_business_group_id
7882: and not exists (
7883: select /* */ null
7884: from ben_copy_entity_results cpe
7885: -- pqh_table_route trt
7886: where copy_entity_txn_id = p_copy_entity_txn_id
7887: -- and trt.table_route_id = cpe.table_route_id
7888: and ( -- c_mirror_src_entity_result_id is null or
7913: where opr.othr_ptip_rt_id = c_othr_ptip_rt_id
7914: --and opr.business_group_id = p_business_group_id
7915: and not exists (
7916: select /* */ null
7917: from ben_copy_entity_results cpe
7918: -- pqh_table_route trt
7919: where copy_entity_txn_id = p_copy_entity_txn_id
7920: -- and trt.table_route_id = cpe.table_route_id
7921: and ( -- c_mirror_src_entity_result_id is null or
7934:
7935: cursor c_object_exists(c_pk_id number,
7936: c_table_alias varchar2) is
7937: select null
7938: from ben_copy_entity_results cpe
7939: -- pqh_table_route trt
7940: where copy_entity_txn_id = p_copy_entity_txn_id
7941: -- and trt.table_route_id = cpe.table_route_id
7942: and cpe.table_alias = c_table_alias
7986: end if;
7987: --
7988: l_copy_entity_result_id := null;
7989: l_object_version_number := null;
7990: ben_copy_entity_results_api.create_copy_entity_results(
7991: p_copy_entity_result_id => l_copy_entity_result_id,
7992: p_copy_entity_txn_id => p_copy_entity_txn_id,
7993: p_result_type_cd => l_result_type_cd,
7994: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
8345: end if;
8346: --
8347: l_copy_entity_result_id := null;
8348: l_object_version_number := null;
8349: ben_copy_entity_results_api.create_copy_entity_results(
8350: p_copy_entity_result_id => l_copy_entity_result_id,
8351: p_copy_entity_txn_id => p_copy_entity_txn_id,
8352: p_result_type_cd => l_result_type_cd,
8353: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
8495: p_copy_entity_txn_id);
8496: --
8497: hr_utility.set_location('l_mapping_id '||l_mapping_id,100);
8498: hr_utility.set_location('l_mapping_name '||l_mapping_name,100);
8499: ben_copy_entity_results_api.create_copy_entity_results(
8500: p_copy_entity_result_id => l_copy_entity_result_id,
8501: p_copy_entity_txn_id => p_copy_entity_txn_id,
8502: p_result_type_cd => l_result_type_cd,
8503: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
8604: end if;
8605: --
8606: l_copy_entity_result_id := null;
8607: l_object_version_number := null;
8608: ben_copy_entity_results_api.create_copy_entity_results(
8609: p_copy_entity_result_id => l_copy_entity_result_id,
8610: p_copy_entity_txn_id => p_copy_entity_txn_id,
8611: p_result_type_cd => l_result_type_cd,
8612: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
8721: end if;
8722: --
8723: l_copy_entity_result_id := null;
8724: l_object_version_number := null;
8725: ben_copy_entity_results_api.create_copy_entity_results(
8726: p_copy_entity_result_id => l_copy_entity_result_id,
8727: p_copy_entity_txn_id => p_copy_entity_txn_id,
8728: p_result_type_cd => l_result_type_cd,
8729: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
8824: end if;
8825: --
8826: l_copy_entity_result_id := null;
8827: l_object_version_number := null;
8828: ben_copy_entity_results_api.create_copy_entity_results(
8829: p_copy_entity_result_id => l_copy_entity_result_id,
8830: p_copy_entity_txn_id => p_copy_entity_txn_id,
8831: p_result_type_cd => l_result_type_cd,
8832: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
8946: end if;
8947: --
8948: l_copy_entity_result_id := null;
8949: l_object_version_number := null;
8950: ben_copy_entity_results_api.create_copy_entity_results(
8951: p_copy_entity_result_id => l_copy_entity_result_id,
8952: p_copy_entity_txn_id => p_copy_entity_txn_id,
8953: p_result_type_cd => l_result_type_cd,
8954: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
9068: end if;
9069: --
9070: l_copy_entity_result_id := null;
9071: l_object_version_number := null;
9072: ben_copy_entity_results_api.create_copy_entity_results(
9073: p_copy_entity_result_id => l_copy_entity_result_id,
9074: p_copy_entity_txn_id => p_copy_entity_txn_id,
9075: p_result_type_cd => l_result_type_cd,
9076: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
9200: --
9201: hr_utility.set_location('l_mapping_id '||l_mapping_id,100);
9202: hr_utility.set_location('l_mapping_name '||l_mapping_name,100);
9203: --
9204: ben_copy_entity_results_api.create_copy_entity_results(
9205: p_copy_entity_result_id => l_copy_entity_result_id,
9206: p_copy_entity_txn_id => p_copy_entity_txn_id,
9207: p_result_type_cd => l_result_type_cd,
9208: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
9309: end if;
9310: --
9311: l_copy_entity_result_id := null;
9312: l_object_version_number := null;
9313: ben_copy_entity_results_api.create_copy_entity_results(
9314: p_copy_entity_result_id => l_copy_entity_result_id,
9315: p_copy_entity_txn_id => p_copy_entity_txn_id,
9316: p_result_type_cd => l_result_type_cd,
9317: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
9411: end if;
9412: --
9413: l_copy_entity_result_id := null;
9414: l_object_version_number := null;
9415: ben_copy_entity_results_api.create_copy_entity_results(
9416: p_copy_entity_result_id => l_copy_entity_result_id,
9417: p_copy_entity_txn_id => p_copy_entity_txn_id,
9418: p_result_type_cd => l_result_type_cd,
9419: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
9553: --
9554: hr_utility.set_location('l_mapping_id '||l_mapping_id,100);
9555: hr_utility.set_location('l_mapping_name '||l_mapping_name,100);
9556:
9557: ben_copy_entity_results_api.create_copy_entity_results(
9558: p_copy_entity_result_id => l_copy_entity_result_id,
9559: p_copy_entity_txn_id => p_copy_entity_txn_id,
9560: p_result_type_cd => l_result_type_cd,
9561: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
9660: end if;
9661: --
9662: l_copy_entity_result_id := null;
9663: l_object_version_number := null;
9664: ben_copy_entity_results_api.create_copy_entity_results(
9665: p_copy_entity_result_id => l_copy_entity_result_id,
9666: p_copy_entity_txn_id => p_copy_entity_txn_id,
9667: p_result_type_cd => l_result_type_cd,
9668: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
9764: end if;
9765: --
9766: l_copy_entity_result_id := null;
9767: l_object_version_number := null;
9768: ben_copy_entity_results_api.create_copy_entity_results(
9769: p_copy_entity_result_id => l_copy_entity_result_id,
9770: p_copy_entity_txn_id => p_copy_entity_txn_id,
9771: p_result_type_cd => l_result_type_cd,
9772: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
9887: end if;
9888: --
9889: l_copy_entity_result_id := null;
9890: l_object_version_number := null;
9891: ben_copy_entity_results_api.create_copy_entity_results(
9892: p_copy_entity_result_id => l_copy_entity_result_id,
9893: p_copy_entity_txn_id => p_copy_entity_txn_id,
9894: p_result_type_cd => l_result_type_cd,
9895: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
10031: --
10032: hr_utility.set_location('l_mapping_id '||l_mapping_id,100);
10033: hr_utility.set_location('l_mapping_name '||l_mapping_name,100);
10034:
10035: ben_copy_entity_results_api.create_copy_entity_results(
10036: p_copy_entity_result_id => l_copy_entity_result_id,
10037: p_copy_entity_txn_id => p_copy_entity_txn_id,
10038: p_result_type_cd => l_result_type_cd,
10039: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
10198: hr_utility.set_location('l_mapping_id1 '||l_mapping_id1,100);
10199: hr_utility.set_location('l_mapping_name1 '||l_mapping_name1,100);
10200: --
10201:
10202: ben_copy_entity_results_api.create_copy_entity_results(
10203: p_copy_entity_result_id => l_copy_entity_result_id,
10204: p_copy_entity_txn_id => p_copy_entity_txn_id,
10205: p_result_type_cd => l_result_type_cd,
10206: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
10314: end if;
10315: --
10316: l_copy_entity_result_id := null;
10317: l_object_version_number := null;
10318: ben_copy_entity_results_api.create_copy_entity_results(
10319: p_copy_entity_result_id => l_copy_entity_result_id,
10320: p_copy_entity_txn_id => p_copy_entity_txn_id,
10321: p_result_type_cd => l_result_type_cd,
10322: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
10433: end if;
10434: --
10435: l_copy_entity_result_id := null;
10436: l_object_version_number := null;
10437: ben_copy_entity_results_api.create_copy_entity_results(
10438: p_copy_entity_result_id => l_copy_entity_result_id,
10439: p_copy_entity_txn_id => p_copy_entity_txn_id,
10440: p_result_type_cd => l_result_type_cd,
10441: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
10574: --
10575:
10576: l_copy_entity_result_id := null;
10577: l_object_version_number := null;
10578: ben_copy_entity_results_api.create_copy_entity_results(
10579: p_copy_entity_result_id => l_copy_entity_result_id,
10580: p_copy_entity_txn_id => p_copy_entity_txn_id,
10581: p_result_type_cd => l_result_type_cd,
10582: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
10685: end if;
10686: --
10687: l_copy_entity_result_id := null;
10688: l_object_version_number := null;
10689: ben_copy_entity_results_api.create_copy_entity_results(
10690: p_copy_entity_result_id => l_copy_entity_result_id,
10691: p_copy_entity_txn_id => p_copy_entity_txn_id,
10692: p_result_type_cd => l_result_type_cd,
10693: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
10834: p_copy_entity_txn_id);
10835: --
10836:
10837: hr_utility.set_location('l_parent_rec.per_typ_rt_id '||l_parent_rec.per_typ_rt_id,100);
10838: ben_copy_entity_results_api.create_copy_entity_results(
10839: p_copy_entity_result_id => l_copy_entity_result_id,
10840: p_copy_entity_txn_id => p_copy_entity_txn_id,
10841: p_result_type_cd => l_result_type_cd,
10842: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
10945: end if;
10946: --
10947: l_copy_entity_result_id := null;
10948: l_object_version_number := null;
10949: ben_copy_entity_results_api.create_copy_entity_results(
10950: p_copy_entity_result_id => l_copy_entity_result_id,
10951: p_copy_entity_txn_id => p_copy_entity_txn_id,
10952: p_result_type_cd => l_result_type_cd,
10953: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
11076: end if;
11077: --
11078: l_copy_entity_result_id := null;
11079: l_object_version_number := null;
11080: ben_copy_entity_results_api.create_copy_entity_results(
11081: p_copy_entity_result_id => l_copy_entity_result_id,
11082: p_copy_entity_txn_id => p_copy_entity_txn_id,
11083: p_result_type_cd => l_result_type_cd,
11084: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
11180: end if;
11181: --
11182: l_copy_entity_result_id := null;
11183: l_object_version_number := null;
11184: ben_copy_entity_results_api.create_copy_entity_results(
11185: p_copy_entity_result_id => l_copy_entity_result_id,
11186: p_copy_entity_txn_id => p_copy_entity_txn_id,
11187: p_result_type_cd => l_result_type_cd,
11188: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
11336:
11337: l_copy_entity_result_id := null;
11338: l_object_version_number := null;
11339: hr_utility.set_location('l_parent_rec.pyrl_rt_id '||l_parent_rec.pyrl_rt_id,100);
11340: ben_copy_entity_results_api.create_copy_entity_results(
11341: p_copy_entity_result_id => l_copy_entity_result_id,
11342: p_copy_entity_txn_id => p_copy_entity_txn_id,
11343: p_result_type_cd => l_result_type_cd,
11344: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
11475: hr_utility.set_location('l_mapping_id '||l_mapping_id,100);
11476: --
11477: l_copy_entity_result_id := null;
11478: l_object_version_number := null;
11479: ben_copy_entity_results_api.create_copy_entity_results(
11480: p_copy_entity_result_id => l_copy_entity_result_id,
11481: p_copy_entity_txn_id => p_copy_entity_txn_id,
11482: p_result_type_cd => l_result_type_cd,
11483: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
11585: end if;
11586: --
11587: l_copy_entity_result_id := null;
11588: l_object_version_number := null;
11589: ben_copy_entity_results_api.create_copy_entity_results(
11590: p_copy_entity_result_id => l_copy_entity_result_id,
11591: p_copy_entity_txn_id => p_copy_entity_txn_id,
11592: p_result_type_cd => l_result_type_cd,
11593: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
11758: end if;
11759: --
11760: l_copy_entity_result_id := null;
11761: l_object_version_number := null;
11762: ben_copy_entity_results_api.create_copy_entity_results(
11763: p_copy_entity_result_id => l_copy_entity_result_id,
11764: p_copy_entity_txn_id => p_copy_entity_txn_id,
11765: p_result_type_cd => l_result_type_cd,
11766: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
11874: end if;
11875: --
11876: l_copy_entity_result_id := null;
11877: l_object_version_number := null;
11878: ben_copy_entity_results_api.create_copy_entity_results(
11879: p_copy_entity_result_id => l_copy_entity_result_id,
11880: p_copy_entity_txn_id => p_copy_entity_txn_id,
11881: p_result_type_cd => l_result_type_cd,
11882: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
11977: end if;
11978: --
11979: l_copy_entity_result_id := null;
11980: l_object_version_number := null;
11981: ben_copy_entity_results_api.create_copy_entity_results(
11982: p_copy_entity_result_id => l_copy_entity_result_id,
11983: p_copy_entity_txn_id => p_copy_entity_txn_id,
11984: p_result_type_cd => l_result_type_cd,
11985: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
12107: end if;
12108: --
12109: l_copy_entity_result_id := null;
12110: l_object_version_number := null;
12111: ben_copy_entity_results_api.create_copy_entity_results(
12112: p_copy_entity_result_id => l_copy_entity_result_id,
12113: p_copy_entity_txn_id => p_copy_entity_txn_id,
12114: p_result_type_cd => l_result_type_cd,
12115: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
12235: end if;
12236: --
12237: l_copy_entity_result_id := null;
12238: l_object_version_number := null;
12239: ben_copy_entity_results_api.create_copy_entity_results(
12240: p_copy_entity_result_id => l_copy_entity_result_id,
12241: p_copy_entity_txn_id => p_copy_entity_txn_id,
12242: p_result_type_cd => l_result_type_cd,
12243: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
12369: end if;
12370: --
12371: l_copy_entity_result_id := null;
12372: l_object_version_number := null;
12373: ben_copy_entity_results_api.create_copy_entity_results(
12374: p_copy_entity_result_id => l_copy_entity_result_id,
12375: p_copy_entity_txn_id => p_copy_entity_txn_id,
12376: p_result_type_cd => l_result_type_cd,
12377: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
12531: l_mapping_column_name2,
12532: p_copy_entity_txn_id);
12533: --
12534:
12535: ben_copy_entity_results_api.create_copy_entity_results(
12536: p_copy_entity_result_id => l_copy_entity_result_id,
12537: p_copy_entity_txn_id => p_copy_entity_txn_id,
12538: p_result_type_cd => l_result_type_cd,
12539: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
12694: --
12695:
12696: l_copy_entity_result_id := null;
12697: l_object_version_number := null;
12698: ben_copy_entity_results_api.create_copy_entity_results(
12699: p_copy_entity_result_id => l_copy_entity_result_id,
12700: p_copy_entity_txn_id => p_copy_entity_txn_id,
12701: p_result_type_cd => l_result_type_cd,
12702: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
12846: --
12847:
12848: l_copy_entity_result_id := null;
12849: l_object_version_number := null;
12850: ben_copy_entity_results_api.create_copy_entity_results(
12851: p_copy_entity_result_id => l_copy_entity_result_id,
12852: p_copy_entity_txn_id => p_copy_entity_txn_id,
12853: p_result_type_cd => l_result_type_cd,
12854: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
12995: end if;
12996:
12997: l_copy_entity_result_id := null;
12998: l_object_version_number := null;
12999: ben_copy_entity_results_api.create_copy_entity_results(
13000: p_copy_entity_result_id => l_copy_entity_result_id,
13001: p_copy_entity_txn_id => p_copy_entity_txn_id,
13002: p_result_type_cd => l_result_type_cd,
13003: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13134: --
13135:
13136: l_copy_entity_result_id := null;
13137: l_object_version_number := null;
13138: ben_copy_entity_results_api.create_copy_entity_results(
13139: p_copy_entity_result_id => l_copy_entity_result_id,
13140: p_copy_entity_txn_id => p_copy_entity_txn_id,
13141: p_result_type_cd => l_result_type_cd,
13142: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13245: end if;
13246: --
13247: l_copy_entity_result_id := null;
13248: l_object_version_number := null;
13249: ben_copy_entity_results_api.create_copy_entity_results(
13250: p_copy_entity_result_id => l_copy_entity_result_id,
13251: p_copy_entity_txn_id => p_copy_entity_txn_id,
13252: p_result_type_cd => l_result_type_cd,
13253: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13346: end if;
13347: --
13348: l_copy_entity_result_id := null;
13349: l_object_version_number := null;
13350: ben_copy_entity_results_api.create_copy_entity_results(
13351: p_copy_entity_result_id => l_copy_entity_result_id,
13352: p_copy_entity_txn_id => p_copy_entity_txn_id,
13353: p_result_type_cd => l_result_type_cd,
13354: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13469: end if;
13470: --
13471: l_copy_entity_result_id := null;
13472: l_object_version_number := null;
13473: ben_copy_entity_results_api.create_copy_entity_results(
13474: p_copy_entity_result_id => l_copy_entity_result_id,
13475: p_copy_entity_txn_id => p_copy_entity_txn_id,
13476: p_result_type_cd => l_result_type_cd,
13477: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13570: end if;
13571: --
13572: l_copy_entity_result_id := null;
13573: l_object_version_number := null;
13574: ben_copy_entity_results_api.create_copy_entity_results(
13575: p_copy_entity_result_id => l_copy_entity_result_id,
13576: p_copy_entity_txn_id => p_copy_entity_txn_id,
13577: p_result_type_cd => l_result_type_cd,
13578: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13672: end if;
13673: --
13674: l_copy_entity_result_id := null;
13675: l_object_version_number := null;
13676: ben_copy_entity_results_api.create_copy_entity_results(
13677: p_copy_entity_result_id => l_copy_entity_result_id,
13678: p_copy_entity_txn_id => p_copy_entity_txn_id,
13679: p_result_type_cd => l_result_type_cd,
13680: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13774: end if;
13775: --
13776: l_copy_entity_result_id := null;
13777: l_object_version_number := null;
13778: ben_copy_entity_results_api.create_copy_entity_results(
13779: p_copy_entity_result_id => l_copy_entity_result_id,
13780: p_copy_entity_txn_id => p_copy_entity_txn_id,
13781: p_result_type_cd => l_result_type_cd,
13782: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13877: end if;
13878: --
13879: l_copy_entity_result_id := null;
13880: l_object_version_number := null;
13881: ben_copy_entity_results_api.create_copy_entity_results(
13882: p_copy_entity_result_id => l_copy_entity_result_id,
13883: p_copy_entity_txn_id => p_copy_entity_txn_id,
13884: p_result_type_cd => l_result_type_cd,
13885: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
13980: end if;
13981: --
13982: l_copy_entity_result_id := null;
13983: l_object_version_number := null;
13984: ben_copy_entity_results_api.create_copy_entity_results(
13985: p_copy_entity_result_id => l_copy_entity_result_id,
13986: p_copy_entity_txn_id => p_copy_entity_txn_id,
13987: p_result_type_cd => l_result_type_cd,
13988: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14082: end if;
14083: --
14084: l_copy_entity_result_id := null;
14085: l_object_version_number := null;
14086: ben_copy_entity_results_api.create_copy_entity_results(
14087: p_copy_entity_result_id => l_copy_entity_result_id,
14088: p_copy_entity_txn_id => p_copy_entity_txn_id,
14089: p_result_type_cd => l_result_type_cd,
14090: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14183: end if;
14184: --
14185: l_copy_entity_result_id := null;
14186: l_object_version_number := null;
14187: ben_copy_entity_results_api.create_copy_entity_results(
14188: p_copy_entity_result_id => l_copy_entity_result_id,
14189: p_copy_entity_txn_id => p_copy_entity_txn_id,
14190: p_result_type_cd => l_result_type_cd,
14191: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14285: end if;
14286: --
14287: l_copy_entity_result_id := null;
14288: l_object_version_number := null;
14289: ben_copy_entity_results_api.create_copy_entity_results(
14290: p_copy_entity_result_id => l_copy_entity_result_id,
14291: p_copy_entity_txn_id => p_copy_entity_txn_id,
14292: p_result_type_cd => l_result_type_cd,
14293: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14387: end if;
14388: --
14389: l_copy_entity_result_id := null;
14390: l_object_version_number := null;
14391: ben_copy_entity_results_api.create_copy_entity_results(
14392: p_copy_entity_result_id => l_copy_entity_result_id,
14393: p_copy_entity_txn_id => p_copy_entity_txn_id,
14394: p_result_type_cd => l_result_type_cd,
14395: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14488: end if;
14489: --
14490: l_copy_entity_result_id := null;
14491: l_object_version_number := null;
14492: ben_copy_entity_results_api.create_copy_entity_results(
14493: p_copy_entity_result_id => l_copy_entity_result_id,
14494: p_copy_entity_txn_id => p_copy_entity_txn_id,
14495: p_result_type_cd => l_result_type_cd,
14496: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14587: end if;
14588: --
14589: l_copy_entity_result_id := null;
14590: l_object_version_number := null;
14591: ben_copy_entity_results_api.create_copy_entity_results(
14592: p_copy_entity_result_id => l_copy_entity_result_id,
14593: p_copy_entity_txn_id => p_copy_entity_txn_id,
14594: p_result_type_cd => l_result_type_cd,
14595: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14687: end if;
14688: --
14689: l_copy_entity_result_id := null;
14690: l_object_version_number := null;
14691: ben_copy_entity_results_api.create_copy_entity_results(
14692: p_copy_entity_result_id => l_copy_entity_result_id,
14693: p_copy_entity_txn_id => p_copy_entity_txn_id,
14694: p_result_type_cd => l_result_type_cd,
14695: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14789: end if;
14790: --
14791: l_copy_entity_result_id := null;
14792: l_object_version_number := null;
14793: ben_copy_entity_results_api.create_copy_entity_results(
14794: p_copy_entity_result_id => l_copy_entity_result_id,
14795: p_copy_entity_txn_id => p_copy_entity_txn_id,
14796: p_result_type_cd => l_result_type_cd,
14797: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14892: end if;
14893: --
14894: l_copy_entity_result_id := null;
14895: l_object_version_number := null;
14896: ben_copy_entity_results_api.create_copy_entity_results(
14897: p_copy_entity_result_id => l_copy_entity_result_id,
14898: p_copy_entity_txn_id => p_copy_entity_txn_id,
14899: p_result_type_cd => l_result_type_cd,
14900: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
14994: end if;
14995: --
14996: l_copy_entity_result_id := null;
14997: l_object_version_number := null;
14998: ben_copy_entity_results_api.create_copy_entity_results(
14999: p_copy_entity_result_id => l_copy_entity_result_id,
15000: p_copy_entity_txn_id => p_copy_entity_txn_id,
15001: p_result_type_cd => l_result_type_cd,
15002: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
15090: ,p_object_version_number out nocopy number
15091: ,p_effective_date in date
15092: ,p_parent_entity_result_id in number
15093: ) is
15094: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
15095: l_proc varchar2(72) := g_package||'create_rate_results';
15096: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
15097: --
15098: cursor c_parent_result(c_parent_pk_id number,
15092: ,p_parent_entity_result_id in number
15093: ) is
15094: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
15095: l_proc varchar2(72) := g_package||'create_rate_results';
15096: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
15097: --
15098: cursor c_parent_result(c_parent_pk_id number,
15099: c_parent_table_name varchar2,
15100: c_copy_entity_txn_id number) is
15098: cursor c_parent_result(c_parent_pk_id number,
15099: c_parent_table_name varchar2,
15100: c_copy_entity_txn_id number) is
15101: select copy_entity_result_id mirror_src_entity_result_id
15102: from ben_copy_entity_results cpe,
15103: pqh_table_route trt
15104: where cpe.information1= c_parent_pk_id
15105: and cpe.result_type_cd = 'DISPLAY'
15106: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
15120: ---
15121: l_mirror_src_entity_result_id number(15);
15122: l_table_route_id number(15);
15123: l_result_type_cd varchar2(30);
15124: l_information5 ben_copy_entity_results.information5%type;
15125: l_number_of_copies number(15);
15126: ---------------------------------------------------------------
15127: -- START OF BEN_BNFT_PRVDR_POOL_F ----------------------
15128: ---------------------------------------------------------------
15149: where bpp.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
15150: --and bpp.business_group_id = p_business_group_id
15151: and not exists (
15152: select /* */ null
15153: from ben_copy_entity_results cpe
15154: -- pqh_table_route trt
15155: where copy_entity_txn_id = p_copy_entity_txn_id
15156: -- and trt.table_route_id = cpe.table_route_id
15157: and ( -- c_mirror_src_entity_result_id is null or
15181: where abp.aplcn_to_bnft_pool_id = c_aplcn_to_bnft_pool_id
15182: --and abp.business_group_id = p_business_group_id
15183: and not exists (
15184: select /* */ null
15185: from ben_copy_entity_results cpe
15186: -- pqh_table_route trt
15187: where copy_entity_txn_id = p_copy_entity_txn_id
15188: -- and trt.table_route_id = cpe.table_route_id
15189: and ( -- c_mirror_src_entity_result_id is null or
15213: where bpr.bnft_pool_rlovr_rqmt_id = c_bnft_pool_rlovr_rqmt_id
15214: --and bpr.business_group_id = p_business_group_id
15215: and not exists (
15216: select /* */ null
15217: from ben_copy_entity_results cpe
15218: -- pqh_table_route trt
15219: where copy_entity_txn_id = p_copy_entity_txn_id
15220: -- and trt.table_route_id = cpe.table_route_id
15221: and ( -- c_mirror_src_entity_result_id is null or
15265: end if;
15266: --
15267: l_copy_entity_result_id := null;
15268: l_object_version_number := null;
15269: ben_copy_entity_results_api.create_copy_entity_results(
15270: p_copy_entity_result_id => l_copy_entity_result_id,
15271: p_copy_entity_txn_id => p_copy_entity_txn_id,
15272: p_result_type_cd => l_result_type_cd,
15273: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
15482: end if;
15483: --
15484: l_copy_entity_result_id := null;
15485: l_object_version_number := null;
15486: ben_copy_entity_results_api.create_copy_entity_results(
15487: p_copy_entity_result_id => l_copy_entity_result_id,
15488: p_copy_entity_txn_id => p_copy_entity_txn_id,
15489: p_result_type_cd => l_result_type_cd,
15490: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
15579: end if;
15580: --
15581: l_copy_entity_result_id := null;
15582: l_object_version_number := null;
15583: ben_copy_entity_results_api.create_copy_entity_results(
15584: p_copy_entity_result_id => l_copy_entity_result_id,
15585: p_copy_entity_txn_id => p_copy_entity_txn_id,
15586: p_result_type_cd => l_result_type_cd,
15587: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
15775: ,p_number_of_copies in number default 0
15776: ,p_object_version_number out nocopy number
15777: ,p_effective_date in date
15778: ) is
15779: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
15780: l_proc varchar2(72) := g_package||'create_rate_results';
15781: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
15782: --
15783: cursor c_parent_result(c_parent_pk_id number,
15777: ,p_effective_date in date
15778: ) is
15779: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
15780: l_proc varchar2(72) := g_package||'create_rate_results';
15781: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
15782: --
15783: cursor c_parent_result(c_parent_pk_id number,
15784: c_parent_table_name varchar2,
15785: c_copy_entity_txn_id number) is
15783: cursor c_parent_result(c_parent_pk_id number,
15784: c_parent_table_name varchar2,
15785: c_copy_entity_txn_id number) is
15786: select copy_entity_result_id mirror_src_entity_result_id
15787: from ben_copy_entity_results cpe,
15788: pqh_table_route trt
15789: where cpe.information1= c_parent_pk_id
15790: and cpe.result_type_cd = 'DISPLAY'
15791: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
15805: ---
15806: l_mirror_src_entity_result_id number(15);
15807: l_table_route_id number(15);
15808: l_result_type_cd varchar2(30);
15809: l_information5 ben_copy_entity_results.information5%type;
15810: l_number_of_copies number(15);
15811: ---------------------------------------------------------------
15812: -- START OF BEN_SVC_AREA_F ----------------------
15813: ---------------------------------------------------------------
15818: where sva.svc_area_id = c_svc_area_id
15819: --and sva.business_group_id = p_business_group_id
15820: and not exists (
15821: select /* */ null
15822: from ben_copy_entity_results cpe
15823: -- pqh_table_route trt
15824: where copy_entity_txn_id = p_copy_entity_txn_id
15825: -- and trt.table_route_id = cpe.table_route_id
15826: and ( -- c_mirror_src_entity_result_id is null or
15850: where saz.svc_area_pstl_zip_rng_id = c_svc_area_pstl_zip_rng_id
15851: --and saz.business_group_id = p_business_group_id
15852: and not exists (
15853: select /* */ null
15854: from ben_copy_entity_results cpe
15855: -- pqh_table_route trt
15856: where copy_entity_txn_id = p_copy_entity_txn_id
15857: -- and trt.table_route_id = cpe.table_route_id
15858: and ( -- c_mirror_src_entity_result_id is null or
15867: l_out_saz_result_id number(15);
15868: --
15869: cursor c_saz_pstl(c_svc_area_pstl_zip_rng_id number,c_mirror_src_entity_result_id number,c_table_alias varchar2 ) is
15870: select distinct cpe.information245 pstl_zip_rng_id
15871: from ben_copy_entity_results cpe
15872: -- pqh_table_route trt
15873: where copy_entity_txn_id = p_copy_entity_txn_id
15874: -- and trt.table_route_id = cpe.table_route_id
15875: and mirror_src_entity_result_id = c_mirror_src_entity_result_id
15885:
15886: cursor c_object_exists(c_pk_id number,
15887: c_table_alias varchar2) is
15888: select null
15889: from ben_copy_entity_results cpe
15890: -- pqh_table_route trt
15891: where copy_entity_txn_id = p_copy_entity_txn_id
15892: -- and trt.table_route_id = cpe.table_route_id
15893: and cpe.table_alias = c_table_alias
15934: end if;
15935: --
15936: l_copy_entity_result_id := null;
15937: l_object_version_number := null;
15938: ben_copy_entity_results_api.create_copy_entity_results(
15939: p_copy_entity_result_id => l_copy_entity_result_id,
15940: p_copy_entity_txn_id => p_copy_entity_txn_id,
15941: p_result_type_cd => l_result_type_cd,
15942: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
16031: end if;
16032: --
16033: l_copy_entity_result_id := null;
16034: l_object_version_number := null;
16035: ben_copy_entity_results_api.create_copy_entity_results(
16036: p_copy_entity_result_id => l_copy_entity_result_id,
16037: p_copy_entity_txn_id => p_copy_entity_txn_id,
16038: p_result_type_cd => l_result_type_cd,
16039: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
16129: ,p_number_of_copies in number default 0
16130: ,p_object_version_number out nocopy number
16131: ,p_effective_date in date
16132: ) is
16133: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
16134: l_proc varchar2(72) := g_package||'create_rate_results';
16135: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
16136: --
16137: cursor c_parent_result(c_parent_pk_id number,
16131: ,p_effective_date in date
16132: ) is
16133: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
16134: l_proc varchar2(72) := g_package||'create_rate_results';
16135: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
16136: --
16137: cursor c_parent_result(c_parent_pk_id number,
16138: c_parent_table_name varchar2,
16139: c_copy_entity_txn_id number) is
16137: cursor c_parent_result(c_parent_pk_id number,
16138: c_parent_table_name varchar2,
16139: c_copy_entity_txn_id number) is
16140: select copy_entity_result_id mirror_src_entity_result_id
16141: from ben_copy_entity_results cpe,
16142: pqh_table_route trt
16143: where cpe.information1= c_parent_pk_id
16144: and cpe.result_type_cd = 'DISPLAY'
16145: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
16159: ---
16160: l_mirror_src_entity_result_id number(15);
16161: l_table_route_id number(15);
16162: l_result_type_cd varchar2(30);
16163: l_information5 ben_copy_entity_results.information5%type;
16164: l_number_of_copies number(15);
16165: ---------------------------------------------------------------
16166: -- START OF BEN_PSTL_ZIP_RNG_F ----------------------
16167: ---------------------------------------------------------------
16172: where rzr.pstl_zip_rng_id = c_pstl_zip_rng_id
16173: --and rzr.business_group_id = p_business_group_id
16174: and not exists (
16175: select /* */ null
16176: from ben_copy_entity_results cpe
16177: -- pqh_table_route trt
16178: where copy_entity_txn_id = p_copy_entity_txn_id
16179: -- and trt.table_route_id = cpe.table_route_id
16180: and ( -- c_mirror_src_entity_result_id is null or
16192: ---------------------------------------------------------------
16193: cursor c_object_exists(c_pk_id number,
16194: c_table_alias varchar2) is
16195: select null
16196: from ben_copy_entity_results cpe
16197: -- pqh_table_route trt
16198: where copy_entity_txn_id = p_copy_entity_txn_id
16199: -- and trt.table_route_id = cpe.table_route_id
16200: and cpe.table_alias = c_table_alias
16241: end if;
16242: --
16243: l_copy_entity_result_id := null;
16244: l_object_version_number := null;
16245: ben_copy_entity_results_api.create_copy_entity_results(
16246: p_copy_entity_result_id => l_copy_entity_result_id,
16247: p_copy_entity_txn_id => p_copy_entity_txn_id,
16248: p_result_type_cd => l_result_type_cd,
16249: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
16322: ,p_number_of_copies in number default 0
16323: ,p_object_version_number out nocopy number
16324: ,p_effective_date in date
16325: ) is
16326: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
16327: l_proc varchar2(72) := g_package||'create_rate_results';
16328: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
16329: --
16330: cursor c_parent_result(c_parent_pk_id number,
16324: ,p_effective_date in date
16325: ) is
16326: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
16327: l_proc varchar2(72) := g_package||'create_rate_results';
16328: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
16329: --
16330: cursor c_parent_result(c_parent_pk_id number,
16331: c_parent_table_name varchar2,
16332: c_copy_entity_txn_id number) is
16330: cursor c_parent_result(c_parent_pk_id number,
16331: c_parent_table_name varchar2,
16332: c_copy_entity_txn_id number) is
16333: select copy_entity_result_id mirror_src_entity_result_id
16334: from ben_copy_entity_results cpe,
16335: pqh_table_route trt
16336: where cpe.information1= c_parent_pk_id
16337: and cpe.result_type_cd = 'DISPLAY'
16338: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
16352: ---
16353: l_mirror_src_entity_result_id number(15);
16354: l_table_route_id number(15);
16355: l_result_type_cd varchar2(30);
16356: l_information5 ben_copy_entity_results.information5%type;
16357: l_number_of_copies number(15);
16358: ---------------------------------------------------------------
16359: -- START OF BEN_BNFTS_BAL_F ----------------------
16360: ---------------------------------------------------------------
16365: where bnb.bnfts_bal_id = c_bnfts_bal_id
16366: --and bnb.business_group_id = p_business_group_id
16367: and not exists (
16368: select /* */ null
16369: from ben_copy_entity_results cpe
16370: -- pqh_table_route trt
16371: where copy_entity_txn_id = p_copy_entity_txn_id
16372: -- and trt.table_route_id = cpe.table_route_id
16373: and ( -- c_mirror_src_entity_result_id is null or
16385: ---------------------------------------------------------------
16386: cursor c_object_exists(c_pk_id number,
16387: c_table_alias varchar2) is
16388: select null
16389: from ben_copy_entity_results cpe
16390: -- pqh_table_route trt
16391: where copy_entity_txn_id = p_copy_entity_txn_id
16392: -- and trt.table_route_id = cpe.table_route_id
16393: and cpe.table_alias = c_table_alias
16434: end if;
16435: --
16436: l_copy_entity_result_id := null;
16437: l_object_version_number := null;
16438: ben_copy_entity_results_api.create_copy_entity_results(
16439: p_copy_entity_result_id => l_copy_entity_result_id,
16440: p_copy_entity_txn_id => p_copy_entity_txn_id,
16441: p_result_type_cd => l_result_type_cd,
16442: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
16516: ,p_number_of_copies in number default 0
16517: ,p_object_version_number out nocopy number
16518: ,p_effective_date in date
16519: ) is
16520: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
16521: l_proc varchar2(72) := g_package||'create_rate_results';
16522: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
16523: --
16524: cursor c_parent_result(c_parent_pk_id number,
16518: ,p_effective_date in date
16519: ) is
16520: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
16521: l_proc varchar2(72) := g_package||'create_rate_results';
16522: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
16523: --
16524: cursor c_parent_result(c_parent_pk_id number,
16525: c_parent_table_name varchar2,
16526: c_copy_entity_txn_id number) is
16524: cursor c_parent_result(c_parent_pk_id number,
16525: c_parent_table_name varchar2,
16526: c_copy_entity_txn_id number) is
16527: select copy_entity_result_id mirror_src_entity_result_id
16528: from ben_copy_entity_results cpe,
16529: pqh_table_route trt
16530: where cpe.information1= c_parent_pk_id
16531: and cpe.result_type_cd = 'DISPLAY'
16532: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
16546: ---
16547: l_mirror_src_entity_result_id number(15);
16548: l_table_route_id number(15);
16549: l_result_type_cd varchar2(30);
16550: l_information5 ben_copy_entity_results.information5%type;
16551: l_number_of_copies number(15);
16552: ---------------------------------------------------------------
16553: -- START OF BEN_BENFTS_GRP ----------------------
16554: ---------------------------------------------------------------
16559: where bng.benfts_grp_id = c_benfts_grp_id
16560: --and bng.business_group_id = p_business_group_id
16561: and not exists (
16562: select /* */ null
16563: from ben_copy_entity_results cpe
16564: -- pqh_table_route trt
16565: where copy_entity_txn_id = p_copy_entity_txn_id
16566: -- and trt.table_route_id = cpe.table_route_id
16567: and ( -- c_mirror_src_entity_result_id is null or
16578: ---------------------------------------------------------------
16579: cursor c_object_exists(c_pk_id number,
16580: c_table_alias varchar2) is
16581: select null
16582: from ben_copy_entity_results cpe
16583: -- pqh_table_route trt
16584: where copy_entity_txn_id = p_copy_entity_txn_id
16585: -- and trt.table_route_id = cpe.table_route_id
16586: and cpe.table_alias = c_table_alias
16619: l_result_type_cd := 'DISPLAY';
16620: --
16621: l_copy_entity_result_id := null;
16622: l_object_version_number := null;
16623: ben_copy_entity_results_api.create_copy_entity_results(
16624: p_copy_entity_result_id => l_copy_entity_result_id,
16625: p_copy_entity_txn_id => p_copy_entity_txn_id,
16626: p_result_type_cd => l_result_type_cd,
16627: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
16698: ,p_number_of_copies in number default 0
16699: ,p_object_version_number out nocopy number
16700: ,p_effective_date in date
16701: ) is
16702: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
16703: l_proc varchar2(72) := g_package||'create_acrs_ptip_cvg_results';
16704: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
16705: --
16706: cursor c_parent_result(c_parent_pk_id number,
16700: ,p_effective_date in date
16701: ) is
16702: l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%TYPE;
16703: l_proc varchar2(72) := g_package||'create_acrs_ptip_cvg_results';
16704: l_object_version_number ben_copy_entity_results.object_version_number%TYPE;
16705: --
16706: cursor c_parent_result(c_parent_pk_id number,
16707: c_parent_table_name varchar2,
16708: c_copy_entity_txn_id number) is
16706: cursor c_parent_result(c_parent_pk_id number,
16707: c_parent_table_name varchar2,
16708: c_copy_entity_txn_id number) is
16709: select copy_entity_result_id mirror_src_entity_result_id
16710: from ben_copy_entity_results cpe,
16711: pqh_table_route trt
16712: where cpe.information1= c_parent_pk_id
16713: and cpe.result_type_cd = 'DISPLAY'
16714: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
16728: ---
16729: l_mirror_src_entity_result_id number(15);
16730: l_table_route_id number(15);
16731: l_result_type_cd varchar2(30);
16732: l_information5 ben_copy_entity_results.information5%type;
16733: l_number_of_copies number(15);
16734: ---------------------------------------------------------------
16735: -- START OF BEN_ACRS_PTIP_CVG_F----------------------
16736: ---------------------------------------------------------------
16747: where apc.acrs_ptip_cvg_id = c_acrs_ptip_cvg_id
16748: --and apc.business_group_id = p_business_group_id
16749: and not exists (
16750: select /* */ null
16751: from ben_copy_entity_results cpe
16752: -- pqh_table_route trt
16753: where copy_entity_txn_id = p_copy_entity_txn_id
16754: -- and trt.table_route_id = cpe.table_route_id
16755: and ( -- c_mirror_src_entity_result_id is null or
16788: l_result_type_cd := 'DISPLAY';
16789: --
16790: l_copy_entity_result_id := null;
16791: l_object_version_number := null;
16792: ben_copy_entity_results_api.create_copy_entity_results(
16793: p_copy_entity_result_id => l_copy_entity_result_id,
16794: p_copy_entity_txn_id => p_copy_entity_txn_id,
16795: p_result_type_cd => l_result_type_cd,
16796: p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,