[Home] [Help]
416: and person_id = p_person_id
417: and business_group_id = p_business_group_id
418: and p_effective_date
419: between nvl(date_start, p_effective_date)
420: and nvl(date_end, hr_api.g_eot)));
421:
422: --
423: cursor c_ctfns_pgm is
424: select 'x'
435: and person_id = p_person_id
436: and business_group_id = p_business_group_id
437: and p_effective_date
438: between nvl(date_start, p_effective_date)
439: and nvl(date_end, hr_api.g_eot)));
440: --
441: cursor c_ctfns_ptip is
442: select 'x'
443: from ben_ptip_dpnt_cvg_ctfn_f
453: and person_id = p_person_id
454: and business_group_id = p_business_group_id
455: and p_effective_date
456: between nvl(date_start, p_effective_date)
457: and nvl(date_end, hr_api.g_eot)));
458: --
459: cursor c_ctfns_pl is
460: select 'x'
461: from ben_pl_dpnt_cvg_ctfn_f
471: and person_id = p_person_id
472: and business_group_id = p_business_group_id
473: and p_effective_date
474: between nvl(date_start, p_effective_date)
475: and nvl(date_end, hr_api.g_eot)));
476: --
477: l_dummy varchar2(1);
478: l_return boolean := FALSE;
479: --
1029: -- there are other rules now that we must apply
1030: --
1031: if l_datetrack_mode = DTMODE_ZAP then
1032: -- zap mode converts to correction
1033: l_datetrack_mode := hr_api.g_correction;
1034: --
1035: elsif l_datetrack_mode = DTMODE_DELETE then
1036: -- delete mode converts to update
1037: l_datetrack_mode := DTMODE_UPDATE;
1034: --
1035: elsif l_datetrack_mode = DTMODE_DELETE then
1036: -- delete mode converts to update
1037: l_datetrack_mode := DTMODE_UPDATE;
1038: elsif l_datetrack_mode = hr_api.g_future_change then
1039: l_datetrack_mode := DTMODE_UPDATE;
1040: --
1041: else
1042: -- Bug: 3510501: In all other cases, set date-track mode to 'UPDATE'.
1050: if l_datetrack_mode = DTMODE_UPDATE and
1051: p_eff_date IS NOT NULL and
1052: p_start_date IS NOT NULL and
1053: p_eff_date = p_start_date then
1054: l_datetrack_mode := hr_api.g_correction;
1055: end if;
1056: --
1057: if g_debug then
1058: hr_utility.set_location ('Leaving '||l_proc, 10);
1370: ,p_post_rslt_flag in varchar2 default 'Y'
1371: ,p_business_group_id in number
1372: ,p_object_version_number in out nocopy number
1373: ,p_open_close in varchar2
1374: ,p_datetrack_mode in varchar2 default hr_api.g_correction) is
1375: --
1376: l_proc varchar2(80);
1377: l_datetrack_mode varchar2(30);
1378: -- bug 6010780
1520: ,p_dflt_mode => DTMODE_CORRECT
1521: ,p_eff_date => p_effective_date
1522: ,p_start_date => l_start_date.effective_start_date);
1523: --
1524: -- if p_datetrack_mode = hr_api.g_future_change then -- bug 6010780
1525: --
1526: open c_future_row;
1527: fetch c_future_row into l_object_version_number2;
1528: close c_future_row;
1528: close c_future_row;
1529: --
1530: -- end if;
1531: if l_object_version_number2 is not null
1532: and p_datetrack_mode = hr_api.g_future_change
1533: then -- future rows exists
1534: --
1535: ben_prtt_enrt_actn_api.delete_PRTT_ENRT_ACTN
1536: (
1618: procedure complete_this_action_item
1619: (p_prtt_enrt_actn_id in number
1620: ,p_effective_date in date
1621: ,p_validate in boolean default false
1622: ,p_datetrack_mode in varchar2 default hr_api.g_correction
1623: ,p_post_rslt_flag in varchar2 default 'Y') is
1624: --
1625: -- this procedure will set the completed date for a single open action item
1626: -- for a participant result both dependent and beneficiary
1888: from ben_elig_cvrd_dpnt_f ecd, ben_cvrd_dpnt_ctfn_prvdd_f prv,
1889: ben_per_in_ler pil
1890: where ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1891: and ecd.cvg_strt_dt is not null
1892: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
1893: and ecd.elig_cvrd_dpnt_id = prv.elig_cvrd_dpnt_id
1894: and prv.dpnt_dsgn_ctfn_recd_dt is NULL
1895: and ecd.business_group_id = p_business_group_id
1896: and p_effective_date between ecd.effective_start_date
2579: p_pl_bnf_id => p_pl_bnf_id,
2580: p_rqd_flag => l_rqd_flag, --Bug 6353069
2581: p_post_rslt_flag => 'N',
2582: p_business_group_id => p_business_group_id,
2583: p_datetrack_mode => hr_api.g_update,
2584: p_rslt_object_version_number => l_rslt.object_version_number);
2585: --
2586: if g_debug then
2587: hr_utility.set_location ('Leaving '||l_proc, 10);
3128: procedure determine_dpnt_miss_actn_items
3129: (p_validate in boolean default false
3130: ,p_effective_date in date
3131: ,p_business_group_id in number
3132: ,p_datetrack_mode in varchar2 default hr_api.g_correction
3133: ,p_prtt_enrt_rslt_id in number
3134: ,p_rslt_object_version_number in out nocopy number
3135: ,p_post_rslt_flag in varchar2 default 'Y'
3136: ,p_dpnt_actn_warning out nocopy boolean) is
3302: and person_id = v_person_id
3303: and business_group_id = p_business_group_id
3304: and p_effective_date
3305: between nvl(date_start, p_effective_date)
3306: and nvl(date_end, hr_api.g_eot)));
3307: --
3308: -- Cursor to retrieve dependent certification information at the plan level
3309: --
3310: cursor c_dpnt_ctfn_pl(v_pl_id number, v_dpnt_person_id number, v_person_id number) is
3326: and person_id = v_person_id
3327: and business_group_id = p_business_group_id
3328: and p_effective_date
3329: between nvl(date_start, p_effective_date)
3330: and nvl(date_end, hr_api.g_eot)));
3331: --
3332: -- Cursor to retrieve dependent certification at the program level
3333: --
3334: cursor c_dpnt_ctfn_pgm(v_pgm_id number, v_dpnt_person_id number, v_person_id number) is
3350: and person_id = v_person_id
3351: and business_group_id = p_business_group_id
3352: and p_effective_date
3353: between nvl(date_start, p_effective_date)
3354: and nvl(date_end, hr_api.g_eot)));
3355:
3356: --
3357: -- Cursor to retrieve dependent certifications at the ptip level
3358: --
3375: and person_id = v_person_id
3376: and business_group_id = p_business_group_id
3377: and p_effective_date
3378: between nvl(date_start, p_effective_date)
3379: and nvl(date_end, hr_api.g_eot)));
3380:
3381: --
3382: -- Cursor to fetch the covered dependents for an enrt_rslt_id
3383: --
3392: ben_per_in_ler pil
3393: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3394: and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
3395: and ecd.cvg_strt_dt is not null
3396: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
3397: and ecd.business_group_id = p_business_group_id
3398: and p_effective_date between
3399: ecd.effective_start_date and ecd.effective_end_date
3400: and p_effective_date between
4253: ,p_business_group_id => p_business_group_id
4254: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4255: ,p_rslt_object_version_number => p_rslt_object_version_number
4256: ,p_effective_date => p_effective_date
4257: ,p_datetrack_mode => hr_api.g_zap
4258: ,p_post_rslt_flag => p_post_rslt_flag);
4259: --
4260: if g_debug then
4261: hr_utility.set_location ('Leaving l_ff_ctfns_exists',999);
4412: p_prtt_enrt_rslt_id in number
4413: ,p_effective_date in date
4414: ,p_business_group_id in number
4415: ,p_validate in boolean default FALSE
4416: ,p_datetrack_mode in varchar2 default hr_api.g_correction
4417: ,p_post_rslt_flag in varchar2 default 'Y'
4418: ,p_rslt_object_version_number in out nocopy number
4419: ,p_suspend_flag in out nocopy varchar2
4420: ) is
4518: (p_validate in boolean default FALSE
4519: ,p_prtt_enrt_rslt_id in number
4520: ,p_effective_date in date
4521: ,p_business_group_id in number
4522: ,p_datetrack_mode in varchar2 default hr_api.g_correction
4523: ,p_post_rslt_flag in varchar2 default 'Y'
4524: ,p_rslt_object_version_number in out nocopy number
4525: ,p_suspend_flag in out nocopy varchar2) is
4526: --
4568: ,p_suspend_flag => p_suspend_flag
4569: ,p_validate => p_validate);
4570:
4571: if p_validate then
4572: raise hr_api.validate_enabled;
4573: end if;
4574: --
4575: if g_debug then
4576: hr_utility.set_location ('Leaving '||l_proc, 10);
4577: end if;
4578: --
4579: exception
4580: --
4581: when hr_api.validate_enabled
4582: then
4583: rollback to process_cwb_actn_items;
4584: --
4585: if g_debug then
4604: (p_validate in boolean default FALSE
4605: ,p_prtt_enrt_rslt_id in number
4606: ,p_effective_date in date
4607: ,p_business_group_id in number
4608: ,p_datetrack_mode in varchar2 default hr_api.g_correction
4609: ,p_post_rslt_flag in varchar2 default 'Y'
4610: ,p_allws_flag in varchar2
4611: ,p_dsgn_cd in varchar2
4612: ,p_rslt_object_version_number in out nocopy number
4637: from ben_elig_cvrd_dpnt_f ecd,
4638: ben_per_in_ler pil
4639: where ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4640: and ecd.cvg_strt_dt is not null
4641: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
4642: and ecd.business_group_id = p_business_group_id
4643: and p_effective_date between
4644: ecd.effective_start_date and ecd.effective_end_date
4645: and pil.per_in_ler_id=ecd.per_in_ler_id
4943: ,p_business_group_id => p_business_group_id
4944: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4945: ,p_rslt_object_version_number => p_rslt_object_version_number
4946: ,p_effective_date => p_effective_date
4947: ,p_datetrack_mode => hr_api.g_zap
4948: ,p_post_rslt_flag => p_post_rslt_flag);
4949: --
4950: end loop;
4951: else
5019: procedure complete_dependent_designation
5020: (p_prtt_enrt_rslt_id in number
5021: ,p_effective_date in date
5022: ,p_business_group_id in number
5023: ,p_datetrack_mode in varchar2 default hr_api.g_correction
5024: ,p_post_rslt_flag in varchar2 default 'Y'
5025: ,p_dsgn_cd in varchar2
5026: ,p_rslt_object_version_number in out nocopy number
5027: ,p_dpnt_actn_warning in out nocopy boolean) is
5081: from ben_elig_cvrd_dpnt_f ecd,
5082: ben_per_in_ler pil
5083: where p_prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
5084: and ecd.cvg_strt_dt is not null
5085: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
5086: and ecd.business_group_id = p_business_group_id
5087: and p_effective_date between ecd.effective_start_date
5088: and ecd.effective_end_date
5089: and pil.per_in_ler_id=ecd.per_in_ler_id
5127: per_contact_relationships pcr,
5128: ben_per_in_ler pil
5129: where ecd.prtt_enrt_rslt_id = perslt.prtt_enrt_rslt_id
5130: and ecd.cvg_strt_dt is not null
5131: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
5132: and ecd.business_group_id = p_business_group_id
5133: and p_effective_date between
5134: ecd.effective_start_date and ecd.effective_end_date
5135: and perslt.person_id = pcr.person_id
5308: (p_validate in boolean default FALSE
5309: ,p_prtt_enrt_rslt_id in number
5310: ,p_effective_date in date
5311: ,p_business_group_id in number
5312: ,p_datetrack_mode in varchar2 default hr_api.g_correction
5313: ,p_post_rslt_flag in varchar2 default 'Y'
5314: ,p_rslt_object_version_number in out nocopy number
5315: ,p_suspend_flag in out nocopy varchar2
5316: ,p_dpnt_actn_warning out nocopy boolean
5465: -- End Bug No 4525608
5466:
5467: --
5468: if p_validate then
5469: raise hr_api.validate_enabled;
5470: end if;
5471: --
5472: if g_debug then
5473: hr_utility.set_location ('Leaving '||l_proc, 10);
5474: end if;
5475: --
5476: exception
5477: --
5478: when hr_api.validate_enabled
5479: then
5480: rollback to process_dpnt_actn_items;
5481: --
5482: if g_debug then
5828: select 'Y'
5829: from ben_elig_cvrd_dpnt_f egd
5830: where egd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5831: and egd.per_in_ler_id = p_per_in_ler_id
5832: and egd.cvg_thru_dt = hr_api.g_eot
5833: and not exists (select null
5834: from ben_elig_cvrd_dpnt_f egd2
5835: where egd.elig_cvrd_dpnt_id = egd2.elig_cvrd_dpnt_id
5836: and egd.dpnt_person_id = egd2.dpnt_person_id
6052: ,p_effective_date in date
6053: ,p_business_group_id in number
6054: ,p_validate in boolean default FALSE
6055: ,p_enrt_bnft_id in number default NULL
6056: ,p_datetrack_mode in varchar2 default hr_api.g_correction
6057: ,p_post_rslt_flag in varchar2 default 'Y'
6058: ,p_rslt_object_version_number in out nocopy number
6059: ,p_suspend_flag in out nocopy varchar2
6060: ,p_ctfn_actn_warning out nocopy boolean) is
6392: if (l_prtt_enrt_actn_id is not null) then
6393: --
6394: -- Delete all certifications of type 'BNFT' for the action item
6395: --
6396: If p_datetrack_mode = hr_api.g_zap then
6397: l_datetrack_mode := hr_api.g_zap;
6398: Elsif p_datetrack_mode = hr_api.g_delete then
6399: l_datetrack_mode := hr_api.g_delete;
6400: Elsif p_datetrack_mode = hr_api.g_correction then
6393: --
6394: -- Delete all certifications of type 'BNFT' for the action item
6395: --
6396: If p_datetrack_mode = hr_api.g_zap then
6397: l_datetrack_mode := hr_api.g_zap;
6398: Elsif p_datetrack_mode = hr_api.g_delete then
6399: l_datetrack_mode := hr_api.g_delete;
6400: Elsif p_datetrack_mode = hr_api.g_correction then
6401: l_datetrack_mode := hr_api.g_zap;
6394: -- Delete all certifications of type 'BNFT' for the action item
6395: --
6396: If p_datetrack_mode = hr_api.g_zap then
6397: l_datetrack_mode := hr_api.g_zap;
6398: Elsif p_datetrack_mode = hr_api.g_delete then
6399: l_datetrack_mode := hr_api.g_delete;
6400: Elsif p_datetrack_mode = hr_api.g_correction then
6401: l_datetrack_mode := hr_api.g_zap;
6402: Elsif p_datetrack_mode = hr_api.g_update then
6395: --
6396: If p_datetrack_mode = hr_api.g_zap then
6397: l_datetrack_mode := hr_api.g_zap;
6398: Elsif p_datetrack_mode = hr_api.g_delete then
6399: l_datetrack_mode := hr_api.g_delete;
6400: Elsif p_datetrack_mode = hr_api.g_correction then
6401: l_datetrack_mode := hr_api.g_zap;
6402: Elsif p_datetrack_mode = hr_api.g_update then
6403: l_datetrack_mode := hr_api.g_delete;
6396: If p_datetrack_mode = hr_api.g_zap then
6397: l_datetrack_mode := hr_api.g_zap;
6398: Elsif p_datetrack_mode = hr_api.g_delete then
6399: l_datetrack_mode := hr_api.g_delete;
6400: Elsif p_datetrack_mode = hr_api.g_correction then
6401: l_datetrack_mode := hr_api.g_zap;
6402: Elsif p_datetrack_mode = hr_api.g_update then
6403: l_datetrack_mode := hr_api.g_delete;
6404: Else
6397: l_datetrack_mode := hr_api.g_zap;
6398: Elsif p_datetrack_mode = hr_api.g_delete then
6399: l_datetrack_mode := hr_api.g_delete;
6400: Elsif p_datetrack_mode = hr_api.g_correction then
6401: l_datetrack_mode := hr_api.g_zap;
6402: Elsif p_datetrack_mode = hr_api.g_update then
6403: l_datetrack_mode := hr_api.g_delete;
6404: Else
6405: l_datetrack_mode := hr_api.g_delete;
6398: Elsif p_datetrack_mode = hr_api.g_delete then
6399: l_datetrack_mode := hr_api.g_delete;
6400: Elsif p_datetrack_mode = hr_api.g_correction then
6401: l_datetrack_mode := hr_api.g_zap;
6402: Elsif p_datetrack_mode = hr_api.g_update then
6403: l_datetrack_mode := hr_api.g_delete;
6404: Else
6405: l_datetrack_mode := hr_api.g_delete;
6406: End if;
6399: l_datetrack_mode := hr_api.g_delete;
6400: Elsif p_datetrack_mode = hr_api.g_correction then
6401: l_datetrack_mode := hr_api.g_zap;
6402: Elsif p_datetrack_mode = hr_api.g_update then
6403: l_datetrack_mode := hr_api.g_delete;
6404: Else
6405: l_datetrack_mode := hr_api.g_delete;
6406: End if;
6407: --
6401: l_datetrack_mode := hr_api.g_zap;
6402: Elsif p_datetrack_mode = hr_api.g_update then
6403: l_datetrack_mode := hr_api.g_delete;
6404: Else
6405: l_datetrack_mode := hr_api.g_delete;
6406: End if;
6407: --
6408: for l_enrt_ctfn in c_enrt_ctfn(l_prtt_enrt_actn_id) loop
6409: --
6516: --
6517: end if; -- p_enrt_bnft_id
6518: --
6519: if p_validate then
6520: raise hr_api.validate_enabled;
6521: end if;
6522: --
6523: if g_debug then
6524: hr_utility.set_location ('Leaving ' ||l_proc,10);
6525: end if;
6526: --
6527: exception
6528: --
6529: when hr_api.validate_enabled
6530: then
6531: rollback to determine_other_actn_items;
6532: if g_debug then
6533: hr_utility.set_location ('Leaving ' ||l_proc,10);
6550: ,p_effective_date in date
6551: ,p_business_group_id in number
6552: ,p_elig_cvrd_dpnt_id in number
6553: ,p_validate in boolean default FALSE
6554: ,p_datetrack_mode in varchar2 default hr_api.g_correction
6555: ,p_post_rslt_flag in varchar2 default 'Y'
6556: ,p_pcp_dpnt_dsgn_cd in varchar2
6557: ,p_rslt_object_version_number in out nocopy number
6558: ,p_suspend_flag in out nocopy varchar2
6704: end if; -- rqd_flag = 'Y'
6705: --
6706: p_pcp_dpnt_actn_warning := l_pcp_dpnt_actn_warning;
6707: if p_validate then
6708: raise hr_api.validate_enabled;
6709: end if;
6710: --
6711: if g_debug then
6712: hr_utility.set_location ('Leaving '||l_proc, 10);
6714: --
6715:
6716: exception
6717: --
6718: when hr_api.validate_enabled
6719: then
6720: rollback to determine_pcp_dpnt_actn_items;
6721: --
6722: if g_debug then
6743: (p_prtt_enrt_rslt_id in number
6744: ,p_effective_date in date
6745: ,p_business_group_id in number
6746: ,p_validate in boolean default FALSE
6747: ,p_datetrack_mode in varchar2 default hr_api.g_correction
6748: ,p_post_rslt_flag in varchar2 default 'Y'
6749: ,p_pcp_dsgn_cd in varchar2
6750: ,p_rslt_object_version_number in out nocopy number
6751: ,p_suspend_flag in out nocopy varchar2
6883: end if; -- rqd_flag = 'Y'
6884: --
6885: p_pcp_actn_warning := l_pcp_actn_warning;
6886: if p_validate then
6887: raise hr_api.validate_enabled;
6888: end if;
6889: --
6890: if g_debug then
6891: hr_utility.set_location ('Leaving '||l_proc, 10);
6893: --
6894:
6895: exception
6896: --
6897: when hr_api.validate_enabled
6898: then
6899: rollback to determine_pcp_actn_items;
6900: --
6901: if g_debug then
6924: (p_validate in boolean default FALSE
6925: ,p_prtt_enrt_rslt_id in number
6926: ,p_effective_date in date
6927: ,p_business_group_id in number
6928: ,p_datetrack_mode in varchar2 default hr_api.g_correction
6929: ,p_post_rslt_flag in varchar2 default 'Y'
6930: ,p_rslt_object_version_number in out nocopy number
6931: ,p_suspend_flag in out nocopy varchar2
6932: ,p_pcp_dpnt_actn_warning out nocopy boolean) is
6955: where cop.oipl_id = pen.oipl_id
6956: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6957: and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
6958: and ecd.cvg_strt_dt is not null
6959: and nvl(ecd.cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
6960: and pen.business_group_id = p_business_group_id
6961: and cop.business_group_id = p_business_group_id
6962: and ecd.business_group_id = p_business_group_id
6963: and p_effective_date between ecd.effective_start_date
6978: where pcp.pl_id = pen.pl_id
6979: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6980: and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
6981: and ecd.cvg_strt_dt is not null
6982: and nvl(ecd.cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
6983: and pen.business_group_id = p_business_group_id
6984: and pcp.business_group_id = p_business_group_id
6985: and p_effective_date between ecd.effective_start_date
6986: and ecd.effective_end_date
7077: --
7078: p_pcp_dpnt_actn_warning := l_pcp_dpnt_actn_warning;
7079: --
7080: if p_validate then
7081: raise hr_api.validate_enabled;
7082: end if;
7083: --
7084: if g_debug then
7085: hr_utility.set_location ('Leaving '||l_proc, 10);
7086: end if;
7087: --
7088: exception
7089: --
7090: when hr_api.validate_enabled
7091: then
7092: rollback to process_pcp_dpnt_actn_items;
7093: --
7094: if g_debug then
7116: (p_validate in boolean default FALSE
7117: ,p_prtt_enrt_rslt_id in number
7118: ,p_effective_date in date
7119: ,p_business_group_id in number
7120: ,p_datetrack_mode in varchar2 default hr_api.g_correction
7121: ,p_post_rslt_flag in varchar2 default 'Y'
7122: ,p_rslt_object_version_number in out nocopy number
7123: ,p_suspend_flag in out nocopy varchar2
7124: ,p_pcp_actn_warning out nocopy boolean) is
7226: --
7227: p_pcp_actn_warning := l_pcp_actn_warning;
7228: --
7229: if p_validate then
7230: raise hr_api.validate_enabled;
7231: end if;
7232: --
7233: if g_debug then
7234: hr_utility.set_location ('Leaving '||l_proc, 10);
7235: end if;
7236: --
7237: exception
7238: --
7239: when hr_api.validate_enabled
7240: then
7241: rollback to process_pcp_actn_items;
7242: --
7243: if g_debug then
7517: procedure determine_bnf_miss_actn_items
7518: (p_prtt_enrt_rslt_id in number
7519: ,p_effective_date in date
7520: ,p_business_group_id in number
7521: ,p_datetrack_mode in varchar2 default hr_api.g_correction
7522: ,p_post_rslt_flag in varchar2 default 'Y'
7523: ,p_rslt_object_version_number in out nocopy number
7524: ,p_bnf_actn_warning out nocopy boolean) is
7525: --
7567: and person_id = v_person_id
7568: and business_group_id = p_business_group_id
7569: and p_effective_date
7570: between nvl(date_start, p_effective_date)
7571: and nvl(date_end, hr_api.g_eot)))
7572: and pl.business_group_id = p_business_group_id
7573: and p_effective_date between
7574: pl.effective_start_date and pl.effective_end_date;
7575: --
7587: from ben_pl_bnf_f pbd,
7588: ben_per_in_ler pil
7589: where pbd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7590: and pbd.dsgn_strt_dt is not null
7591: and nvl(pbd.dsgn_thru_dt, hr_api.g_eot) = hr_api.g_eot
7592: and pbd.business_group_id = p_business_group_id
7593: and p_effective_date between pbd.effective_start_date
7594: and pbd.effective_end_date
7595: and pil.per_in_ler_id=pbd.per_in_ler_id
7615: and person_id = v_person_id
7616: and business_group_id = p_business_group_id
7617: and p_effective_date
7618: between nvl(date_start, p_effective_date)
7619: and nvl(date_end, hr_api.g_eot)))
7620: and business_group_id = p_business_group_id
7621: and p_effective_date between effective_start_date
7622: and effective_end_date;
7623: cursor c_person_id is
8309: p_business_group_id => p_business_group_id,
8310: p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
8311: p_rslt_object_version_number => p_rslt_object_version_number,
8312: p_effective_date => p_effective_date,
8313: p_datetrack_mode => hr_api.g_zap,
8314: p_post_rslt_flag => p_post_rslt_flag
8315: );
8316:
8317: -- we need to delete warning also that was created for this action item
8388: procedure determine_bnf_actn_items
8389: (p_prtt_enrt_rslt_id in number
8390: ,p_effective_date in date
8391: ,p_business_group_id in number
8392: ,p_datetrack_mode in varchar2 default hr_api.g_correction
8393: ,p_post_rslt_flag in varchar2 default 'Y'
8394: ,p_rslt_object_version_number in out nocopy number
8395: ,p_bnf_actn_item_open out nocopy boolean
8396: ,p_hack out nocopy varchar2) is
8541: ,p_business_group_id => p_business_group_id
8542: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
8543: ,p_rslt_object_version_number => p_rslt_object_version_number
8544: ,p_effective_date => p_effective_date
8545: ,p_datetrack_mode => hr_api.g_zap
8546: ,p_post_rslt_flag => p_post_rslt_flag);
8547: --
8548: end loop;
8549: --
8622: (p_validate in boolean default FALSE
8623: ,p_prtt_enrt_rslt_id in number
8624: ,p_effective_date in date
8625: ,p_business_group_id in number
8626: ,p_datetrack_mode in varchar2 default hr_api.g_correction
8627: ,p_post_rslt_flag in varchar2 default 'Y'
8628: ,p_rslt_object_version_number in out nocopy number
8629: ,p_bnf_actn_warning in out nocopy boolean) is
8630: --
8768: and plb.business_group_id = p_business_group_id
8769: and p_effective_date between plb.effective_start_date
8770: and plb.effective_end_date
8771: and p_effective_date between nvl(date_start, p_effective_date) -- bug 5362890
8772: and nvl(date_end, hr_api.g_eot)
8773: and pil.per_in_ler_id=plb.per_in_ler_id
8774: and pil.business_group_id=p_business_group_id
8775: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
8776: UNION -- Bug 5156111 : Added union clause
9141: (p_prtt_enrt_rslt_id in number
9142: ,p_effective_date in date
9143: ,p_business_group_id in number
9144: ,p_validate in boolean default FALSE
9145: ,p_datetrack_mode in varchar2 default hr_api.g_correction
9146: ,p_post_rslt_flag in varchar2 default 'Y'
9147: ,p_rslt_object_version_number in out nocopy number
9148: ,p_suspend_flag in out nocopy varchar2
9149: ,p_bnf_actn_warning out nocopy boolean) is
9229: --
9230: p_bnf_actn_warning := l_bnf_actn_warning;
9231: --
9232: if p_validate then
9233: raise hr_api.validate_enabled;
9234: end if;
9235: --
9236: if g_debug then
9237: hr_utility.set_location ('Leaving '||l_proc, 10);
9238: end if;
9239: --
9240: exception
9241: --
9242: when hr_api.validate_enabled
9243: then
9244: --
9245: rollback to process_bnf_actn_items;
9246: if g_debug then
9269: ,p_effective_date in date
9270: ,p_business_group_id in number
9271: ,p_validate in boolean default false
9272: ,p_enrt_bnft_id in number default null
9273: ,p_datetrack_mode in varchar2 default hr_api.g_correction
9274: ,p_post_rslt_flag in varchar2 default 'Y'
9275: ,p_rslt_object_version_number in out nocopy number
9276: ,p_suspend_flag out nocopy varchar2
9277: ,p_dpnt_actn_warning out nocopy boolean
9607: --
9608: -- When in validation only mode raise the Validate_Enabled exception
9609: --
9610: if p_validate then
9611: raise hr_api.validate_enabled;
9612: end if;
9613: --
9614: p_suspend_flag := l_suspend_flag;
9615: --
9618: end if;
9619: --
9620: exception
9621: --
9622: when hr_api.validate_enabled
9623: then
9624: -- rollback to the savepoint
9625: --
9626: ROLLBACK TO determine_action_items;