eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_EXT_THREAD dependencies on BEN_EXT_CRIT_VAL

Line 1244: ben_ext_crit_val b

1240:
1241: CURSOR get_pl_val IS
1242: SELECT b.val_1
1243: FROM ben_ext_crit_typ a,
1244: ben_ext_crit_val b
1245: WHERE a.ext_crit_typ_id = b.ext_crit_typ_id
1246: and a.crit_typ_cd = 'BPL'
1247: and a.ext_crit_prfl_id = p_ext_crit_prfl_id;
1248:

Line 2241: ,ben_Ext_crit_val xcv

2237: --
2238: cursor c_celt(p_type varchar2) is
2239: select 'X'
2240: from ben_ext_crit_typ xct
2241: ,ben_Ext_crit_val xcv
2242: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
2243: and xct.crit_typ_cd = p_type
2244: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id ;
2245: --

Line 2631: from ben_ext_crit_typ ect, ben_ext_crit_val ecv

2627: l_text varchar2(70);
2628: --
2629: cursor c1 is
2630: select ecv.ext_crit_val_id
2631: from ben_ext_crit_typ ect, ben_ext_crit_val ecv
2632: where ect.crit_typ_cd = 'ADV'
2633: and ect.ext_crit_typ_id = ecv.ext_crit_typ_id
2634: and ect.ext_crit_prfl_id = p_ext_crit_prfl_id
2635: ;

Line 2829: ,ben_ext_crit_val ecv

2825:
2826: cursor c_non_asg_crit is
2827: select 'X'
2828: from ben_ext_crit_typ ect
2829: ,ben_ext_crit_val ecv
2830: ,pay_datetracked_events pde
2831: ,pay_dated_tables pdt
2832: where ect.ext_crit_prfl_id = p_ext_crit_prfl_id
2833: and ect.crit_typ_cd = 'CPE'

Line 2853: ,ben_ext_crit_val ecv

2849:
2850: cursor c_non_asg_tabel_id is
2851: select pde.dated_table_id,pde.business_group_id ,pde.LEGISLATION_CODE,pde.update_type
2852: from ben_ext_crit_typ ect
2853: ,ben_ext_crit_val ecv
2854: ,pay_datetracked_events pde
2855: ,pay_dated_tables pdt
2856: where ect.ext_crit_prfl_id = p_ext_crit_prfl_id
2857: and ect.crit_typ_cd = 'CPE'

Line 3027: ben_ext_crit_val xcv

3023: l_proc varchar2(80);
3024: cursor c_crit_all is
3025: select xcv.val_1
3026: from ben_ext_crit_typ xct,
3027: ben_ext_crit_val xcv
3028: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3029: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3030: and xct.crit_typ_cd = p_ext_crit_typ;
3031:

Line 3119: ben_ext_crit_val xcv

3115: cursor c_pid is
3116: select 'Y',
3117: xct.excld_flag
3118: from ben_ext_crit_typ xct,
3119: ben_ext_crit_val xcv
3120: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3121: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3122: and xct.crit_typ_cd = 'PID';
3123: --

Line 3133: ben_ext_crit_val xcv

3129: cursor c_por is
3130: select 'Y',
3131: xct.excld_flag
3132: from ben_ext_crit_typ xct,
3133: ben_ext_crit_val xcv
3134: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3135: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3136: and xct.crit_typ_cd = 'POR';
3137: --

Line 3147: ben_ext_crit_val xcv

3143: cursor c_pas is
3144: select 'Y',
3145: xct.excld_flag
3146: from ben_ext_crit_typ xct,
3147: ben_ext_crit_val xcv
3148: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3149: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3150: and xct.crit_typ_cd = 'PAS';
3151: --

Line 3162: ben_ext_crit_val xcv

3158: cursor c_plo is
3159: select 'Y',
3160: xct.excld_flag
3161: from ben_ext_crit_typ xct,
3162: ben_ext_crit_val xcv
3163: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3164: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3165: and xct.crit_typ_cd = 'PLO';
3166: --

Line 3176: ben_ext_crit_val xcv

3172: cursor c_pbg is
3173: select 'Y',
3174: xct.excld_flag
3175: from ben_ext_crit_typ xct,
3176: ben_ext_crit_val xcv
3177: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3178: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3179: and xct.crit_typ_cd = 'PBG';
3180: --

Line 3191: ben_ext_crit_val xcv

3187: cursor c_pbgr is
3188: select 'Y',
3189: xct.excld_flag
3190: from ben_ext_crit_typ xct,
3191: ben_ext_crit_val xcv
3192: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3193: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3194: and xct.crit_typ_cd = 'PBGR';
3195: --

Line 3206: ben_ext_crit_val xcv

3202: cursor c_ppt is
3203: select 'Y',
3204: xct.excld_flag
3205: from ben_ext_crit_typ xct,
3206: ben_ext_crit_val xcv
3207: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3208: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3209: and xct.crit_typ_cd = 'PPT';
3210: --

Line 3225: ,ben_ext_crit_val xcv

3221: cursor c_cce is
3222: select 'Y',
3223: xct.excld_flag
3224: from ben_ext_crit_typ xct
3225: ,ben_ext_crit_val xcv
3226: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3227: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3228: and xct.crit_typ_cd = 'CCE';
3229: --

Line 3238: ,ben_ext_crit_val xcv

3234: cursor c_cpe is
3235: select 'Y',
3236: xct.excld_flag
3237: from ben_ext_crit_typ xct
3238: ,ben_ext_crit_val xcv
3239: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3240: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3241: and xct.crit_typ_cd = 'CPE';
3242:

Line 3257: ben_ext_crit_val xcv

3253: xct.excld_flag,
3254: xcv.val_1,
3255: xcv.val_2
3256: from ben_ext_crit_typ xct,
3257: ben_ext_crit_val xcv
3258: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3259: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3260: and xct.crit_typ_cd = 'CAD';
3261:

Line 3264: l_cad_val1 ben_ext_crit_val.val_1%type ;

3260: and xct.crit_typ_cd = 'CAD';
3261:
3262: l_cad_exists varchar2(1) := 'N';
3263: l_cad_exclude varchar2(1);
3264: l_cad_val1 ben_ext_crit_val.val_1%type ;
3265: l_cad_val2 ben_ext_crit_val.val_2%type ;
3266: l_cad_date_from date;
3267: l_cad_date_to date;
3268: -- person data link

Line 3265: l_cad_val2 ben_ext_crit_val.val_2%type ;

3261:
3262: l_cad_exists varchar2(1) := 'N';
3263: l_cad_exclude varchar2(1);
3264: l_cad_val1 ben_ext_crit_val.val_1%type ;
3265: l_cad_val2 ben_ext_crit_val.val_2%type ;
3266: l_cad_date_from date;
3267: l_cad_date_to date;
3268: -- person data link
3269:

Line 3275: ben_ext_crit_val xcv

3271: Select 'Y',
3272: xct.excld_flag,
3273: xcv.val_1
3274: from ben_ext_crit_typ xct,
3275: ben_ext_crit_val xcv
3276: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3277: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3278: and xct.crit_typ_cd = 'PDL';
3279:

Line 3282: l_pdl_val1 ben_ext_crit_val.val_1%type ;

3278: and xct.crit_typ_cd = 'PDL';
3279:
3280: l_pdl_exists varchar2(1) := 'N';
3281: l_pdl_exclude varchar2(1);
3282: l_pdl_val1 ben_ext_crit_val.val_1%type ;
3283:
3284: -- CWB
3285: cursor c_wplr is
3286: select 'Y',

Line 3290: ben_ext_crit_val xcv

3286: select 'Y',
3287: xct.excld_flag ,
3288: xct.EXT_CRIT_TYP_ID
3289: from ben_ext_crit_typ xct,
3290: ben_ext_crit_val xcv
3291: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3292: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3293: and xct.crit_typ_cd = 'WPLPR';
3294: --

Line 3308: ben_ext_crit_val xcv

3304: Select 'Y',
3305: xct.excld_flag,
3306: xcv.val_1
3307: from ben_ext_crit_typ xct,
3308: ben_ext_crit_val xcv
3309: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3310: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3311: and xct.crit_typ_cd = 'PASGSET';
3312:

Line 3315: l_pasgset_val1 ben_ext_crit_val.val_1%type ;

3311: and xct.crit_typ_cd = 'PASGSET';
3312:
3313: l_pasgset_exists varchar2(1) := 'N';
3314: l_pasgset_exclude varchar2(1);
3315: l_pasgset_val1 ben_ext_crit_val.val_1%type ;
3316:
3317:
3318:
3319:

Line 3330: ben_ext_crit_val xcv

3326: xct.excld_flag,
3327: xcv.val_1,
3328: xcv.val_2
3329: from ben_ext_crit_typ xct,
3330: ben_ext_crit_val xcv
3331: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3332: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3333: and xct.crit_typ_cd = 'CED';
3334: --

Line 3337: l_ced_val1 ben_ext_crit_val.val_1%type;

3333: and xct.crit_typ_cd = 'CED';
3334: --
3335: l_ced_exists varchar2(1) := 'N';
3336: l_ced_exclude varchar2(1);
3337: l_ced_val1 ben_ext_crit_val.val_1%type;
3338: l_ced_val2 ben_ext_crit_val.val_2%type;
3339: l_ced_date_from date;
3340: l_ced_date_to date;
3341: --

Line 3338: l_ced_val2 ben_ext_crit_val.val_2%type;

3334: --
3335: l_ced_exists varchar2(1) := 'N';
3336: l_ced_exclude varchar2(1);
3337: l_ced_val1 ben_ext_crit_val.val_1%type;
3338: l_ced_val2 ben_ext_crit_val.val_2%type;
3339: l_ced_date_from date;
3340: l_ced_date_to date;
3341: --
3342:

Line 3351: ben_ext_crit_val xcv

3347: cursor c_mtp is
3348: select 'Y',
3349: xct.excld_flag
3350: from ben_ext_crit_typ xct,
3351: ben_ext_crit_val xcv
3352: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3353: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3354: and xct.crit_typ_cd = 'MTP';
3355: --

Line 3367: ben_ext_crit_val xcv

3363: xct.excld_flag,
3364: xcv.val_1,
3365: xcv.val_2
3366: from ben_ext_crit_typ xct,
3367: ben_ext_crit_val xcv
3368: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3369: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3370: and xct.crit_typ_cd = 'MTBSDT';
3371: --

Line 3374: l_mtbsdt_val1 ben_ext_crit_val.val_1%type;

3370: and xct.crit_typ_cd = 'MTBSDT';
3371: --
3372: l_mtbsdt_exists varchar2(1) := 'N';
3373: l_mtbsdt_exclude varchar2(1);
3374: l_mtbsdt_val1 ben_ext_crit_val.val_1%type;
3375: l_mtbsdt_val2 ben_ext_crit_val.val_2%type;
3376: l_mtbsdt_date_from date;
3377: l_mtbsdt_date_to date;
3378:

Line 3375: l_mtbsdt_val2 ben_ext_crit_val.val_2%type;

3371: --
3372: l_mtbsdt_exists varchar2(1) := 'N';
3373: l_mtbsdt_exclude varchar2(1);
3374: l_mtbsdt_val1 ben_ext_crit_val.val_1%type;
3375: l_mtbsdt_val2 ben_ext_crit_val.val_2%type;
3376: l_mtbsdt_date_from date;
3377: l_mtbsdt_date_to date;
3378:
3379: --

Line 3396: ben_ext_crit_val xcv

3392: xct.excld_flag,
3393: xcv.val_1,
3394: xcv.val_2
3395: from ben_ext_crit_typ xct,
3396: ben_ext_crit_val xcv
3397: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3398: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3399: and xct.crit_typ_cd = 'MSDT';
3400: --

Line 3403: l_msdt_val1 ben_ext_crit_val.val_1%type;

3399: and xct.crit_typ_cd = 'MSDT';
3400: --
3401: l_msdt_exists varchar2(1) := 'N';
3402: l_msdt_exclude varchar2(1);
3403: l_msdt_val1 ben_ext_crit_val.val_1%type;
3404: l_msdt_val2 ben_ext_crit_val.val_2%type;
3405: l_msdt_date_from date;
3406: l_msdt_date_to date;
3407:

Line 3404: l_msdt_val2 ben_ext_crit_val.val_2%type;

3400: --
3401: l_msdt_exists varchar2(1) := 'N';
3402: l_msdt_exclude varchar2(1);
3403: l_msdt_val1 ben_ext_crit_val.val_1%type;
3404: l_msdt_val2 ben_ext_crit_val.val_2%type;
3405: l_msdt_date_from date;
3406: l_msdt_date_to date;
3407:
3408: -- Person payroll id

Line 3413: ben_ext_crit_val xcv

3409: cursor c_rrl is
3410: select 'Y',
3411: xct.excld_flag
3412: from ben_ext_crit_typ xct,
3413: ben_ext_crit_val xcv
3414: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
3415: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
3416: and xct.crit_typ_cd = 'RRL';
3417: --

Line 3880: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

3876:
3877: /*
3878: l_dynamic_sql := l_dynamic_sql ||
3879: '(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
3880: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
3881: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
3882: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
3883: 'xct.crit_typ_cd = ''PID'') ';
3884: */

Line 3916: '( SELECT /+ ORDERED USE_NL (xct, xcv, aset) / 1 FROM ben_ext_crit_typ xct, ben_ext_crit_val xcv, hr_assignment_sets aset ' ||

3912: l_dynamic_sql := l_dynamic_sql || ' and ( not exists ';
3913: end if ;
3914: --- this logic is taken from the pkg pyadcutl.pkb
3915: l_dynamic_sql := l_dynamic_sql ||
3916: '( SELECT /*+ ORDERED USE_NL (xct, xcv, aset) */ 1 FROM ben_ext_crit_typ xct, ben_ext_crit_val xcv, hr_assignment_sets aset ' ||
3917: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
3918: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
3919: ' xct.crit_typ_cd = ''PASGSET'' and to_number(xcv.val_1) = aset.assignment_set_id ' ||
3920: ' and (not exists (select 1 from hr_assignment_set_amendments hasa ' ||

Line 3942: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

3938: /*
3939: if l_pay_spl_process = 'N' then
3940: l_dynamic_sql := l_dynamic_sql ||
3941: '(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
3942: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
3943: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
3944: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
3945: 'xct.crit_typ_cd = ''POR''))) ';
3946:

Line 3980: ' from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

3976:
3977: /*
3978: l_dynamic_sql := l_dynamic_sql ||
3979: '(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1)) |
3980: ' from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
3981: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
3982: ' and ben_asg.assignment_status_type_id = to_number(xcv.val_1) '||
3983: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id '||
3984: ' and xct.crit_typ_cd = ''PAS''))) ';

Line 3998: ' from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

3994: end if;
3995: /*
3996: l_dynamic_sql := l_dynamic_sql ||
3997: '(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1)) ' ||
3998: ' from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
3999: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
4000: ' and ben_asg.location_id = to_number(xcv.val_1) '||
4001: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
4002: 'xct.crit_typ_cd = ''PLO''))) ';

Line 4043: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

4039: l_dynamic_sql := l_dynamic_sql || ' and ((per.benefit_group_id is null) or (per.benefit_group_id not in ';
4040: end if;
4041: l_dynamic_sql := l_dynamic_sql ||
4042: '(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
4043: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
4044: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
4045: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
4046: 'xct.crit_typ_cd = ''PBG''))) ';
4047: else

Line 4056: '(select ''x'' from per_all_people_f per , ben_ext_crit_typ xct, ben_ext_crit_val xcv where ' ||

4052: l_dynamic_sql := l_dynamic_sql || ' and ( ( not exists ';
4053: end if;
4054:
4055: l_dynamic_sql := l_dynamic_sql ||
4056: '(select ''x'' from per_all_people_f per , ben_ext_crit_typ xct, ben_ext_crit_val xcv where ' ||
4057: ' per.person_id = ben_asg.person_id ' ||
4058: ' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
4059: ''',''DD-MM-RRRR'') between per.effective_start_date and per.effective_end_date ' ||
4060: ' and per.benefit_group_id = to_number(xcv.val_1) '||

Line 4095: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

4091:
4092: /*
4093: l_dynamic_sql := l_dynamic_sql ||
4094: '(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
4095: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
4096: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
4097: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
4098: 'xct.crit_typ_cd = ''PBGR'') ';
4099: */

Line 4140: ' from ben_ext_crit_typ xct , ben_ext_crit_val xcv ' ||

4136: ' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
4137: ''',''DD-MM-RRRR'') between ptu.effective_start_date and ptu.effective_end_date ' ||
4138: ' and ptu.person_type_id in '||
4139: ' (select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1)) ' ||
4140: ' from ben_ext_crit_typ xct , ben_ext_crit_val xcv ' ||
4141: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
4142: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
4143: ' xct.crit_typ_cd = ''PPT'')) ';
4144:

Line 4172: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

4168:
4169:
4170: /*
4171: ' (select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
4172: from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
4173: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
4174: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id '||
4175: ' and ptu.person_type_id = to_number(xcv.val_1) and '||
4176: ' xct.crit_typ_cd = ''PPT'')) ';

Line 4202: '(select xcv.val_1 from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

4198:
4199: /*
4200:
4201: l_dynamic_BEN_sql := l_dynamic_BEN_sql ||
4202: '(select xcv.val_1 from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
4203: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
4204: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id '||
4205: ' and xct.crit_typ_cd = ''CCE'') ';
4206: */

Line 4531: '(select xcv.val_1 from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||

4527: else
4528: l_dynamic_sql := l_dynamic_sql || ' and to_char(pcm.cm_typ_id) not in ';
4529: end if;
4530: l_dynamic_sql := l_dynamic_sql ||
4531: '(select xcv.val_1 from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
4532: ' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
4533: ' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
4534: 'xct.crit_typ_cd = ''MTP'') ';
4535: end if;

Line 4641: ' ( select 1 from ben_ext_crit_val cvl , ben_enrt_perd enp , ben_per_in_ler pil' ||

4637: else
4638: l_dynamic_sql := l_dynamic_sql || ' and not exists ' ;
4639: end if ;
4640: l_dynamic_sql := l_dynamic_sql ||
4641: ' ( select 1 from ben_ext_crit_val cvl , ben_enrt_perd enp , ben_per_in_ler pil' ||
4642: ' where cpi.group_per_in_ler_id = pil.per_in_ler_id and pil.group_pl_id = cvl.val_2 ' ||
4643: ' and cvl.EXT_CRIT_TYP_ID = ' || l_wlpr_EXT_CRIT_TYP_ID ||
4644: ' and enp.enrt_perd_id = cvl.val_1 and pil.lf_evt_ocrd_dt = enp.ASND_LF_EVT_DT ) ' ;
4645: end if;

Line 4982: ben_ext_crit_val xcv

4978: xct.excld_flag,
4979: xcv.val_1,
4980: xcv.val_2
4981: from ben_ext_crit_typ xct,
4982: ben_ext_crit_val xcv
4983: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
4984: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
4985: and xct.crit_typ_cd = 'CED';
4986:

Line 4994: ben_ext_crit_val xcv

4990: xct.excld_flag,
4991: xcv.val_1,
4992: xcv.val_2
4993: from ben_ext_crit_typ xct,
4994: ben_ext_crit_val xcv
4995: where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
4996: and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
4997: and xct.crit_typ_cd = 'CAD';
4998:

Line 5027: l_val1 ben_ext_crit_val.val_1%type ;

5023: p_chg_eff_dt_from date ;
5024: p_chg_eff_dt_to date ;
5025: l_actl_exclude_flag varchar2(1) ;
5026: l_eff_exclude_flag varchar2(1) ;
5027: l_val1 ben_ext_crit_val.val_1%type ;
5028: l_val2 ben_ext_crit_val.val_1%type ;
5029:
5030:
5031: Begin

Line 5028: l_val2 ben_ext_crit_val.val_1%type ;

5024: p_chg_eff_dt_to date ;
5025: l_actl_exclude_flag varchar2(1) ;
5026: l_eff_exclude_flag varchar2(1) ;
5027: l_val1 ben_ext_crit_val.val_1%type ;
5028: l_val2 ben_ext_crit_val.val_1%type ;
5029:
5030:
5031: Begin
5032: