[Home] [Help]
429: and person_id = p_person_id
430: and business_group_id = p_business_group_id
431: and p_effective_date
432: between nvl(date_start, p_effective_date)
433: and nvl(date_end, hr_api.g_eot)));
434:
435: --
436: cursor c_ctfns_pgm is
437: select 'x'
448: and person_id = p_person_id
449: and business_group_id = p_business_group_id
450: and p_effective_date
451: between nvl(date_start, p_effective_date)
452: and nvl(date_end, hr_api.g_eot)));
453: --
454: cursor c_ctfns_ptip is
455: select 'x'
456: from ben_ptip_dpnt_cvg_ctfn_f
466: and person_id = p_person_id
467: and business_group_id = p_business_group_id
468: and p_effective_date
469: between nvl(date_start, p_effective_date)
470: and nvl(date_end, hr_api.g_eot)));
471: --
472: cursor c_ctfns_pl is
473: select 'x'
474: from ben_pl_dpnt_cvg_ctfn_f
484: and person_id = p_person_id
485: and business_group_id = p_business_group_id
486: and p_effective_date
487: between nvl(date_start, p_effective_date)
488: and nvl(date_end, hr_api.g_eot)));
489: --
490: l_dummy varchar2(1);
491: l_return boolean := FALSE;
492: --
1042: -- there are other rules now that we must apply
1043: --
1044: if l_datetrack_mode = DTMODE_ZAP then
1045: -- zap mode converts to correction
1046: l_datetrack_mode := hr_api.g_correction;
1047: --
1048: elsif l_datetrack_mode = DTMODE_DELETE then
1049: -- delete mode converts to update
1050: l_datetrack_mode := DTMODE_UPDATE;
1047: --
1048: elsif l_datetrack_mode = DTMODE_DELETE then
1049: -- delete mode converts to update
1050: l_datetrack_mode := DTMODE_UPDATE;
1051: elsif l_datetrack_mode = hr_api.g_future_change then
1052: l_datetrack_mode := DTMODE_UPDATE;
1053: --
1054: else
1055: -- Bug: 3510501: In all other cases, set date-track mode to 'UPDATE'.
1063: if l_datetrack_mode = DTMODE_UPDATE and
1064: p_eff_date IS NOT NULL and
1065: p_start_date IS NOT NULL and
1066: p_eff_date = p_start_date then
1067: l_datetrack_mode := hr_api.g_correction;
1068: end if;
1069: --
1070: if g_debug then
1071: hr_utility.set_location ('Leaving '||l_proc, 10);
1383: ,p_post_rslt_flag in varchar2 default 'Y'
1384: ,p_business_group_id in number
1385: ,p_object_version_number in out nocopy number
1386: ,p_open_close in varchar2
1387: ,p_datetrack_mode in varchar2 default hr_api.g_correction) is
1388: --
1389: l_proc varchar2(80);
1390: l_datetrack_mode varchar2(30);
1391: -- bug 6010780
1533: ,p_dflt_mode => DTMODE_CORRECT
1534: ,p_eff_date => p_effective_date
1535: ,p_start_date => l_start_date.effective_start_date);
1536: --
1537: -- if p_datetrack_mode = hr_api.g_future_change then -- bug 6010780
1538: --
1539: open c_future_row;
1540: fetch c_future_row into l_object_version_number2;
1541: close c_future_row;
1541: close c_future_row;
1542: --
1543: -- end if;
1544: if l_object_version_number2 is not null
1545: and p_datetrack_mode = hr_api.g_future_change
1546: then -- future rows exists
1547: --
1548: ben_prtt_enrt_actn_api.delete_PRTT_ENRT_ACTN
1549: (
1631: procedure complete_this_action_item
1632: (p_prtt_enrt_actn_id in number
1633: ,p_effective_date in date
1634: ,p_validate in boolean default false
1635: ,p_datetrack_mode in varchar2 default hr_api.g_correction
1636: ,p_post_rslt_flag in varchar2 default 'Y') is
1637: --
1638: -- this procedure will set the completed date for a single open action item
1639: -- for a participant result both dependent and beneficiary
1901: from ben_elig_cvrd_dpnt_f ecd, ben_cvrd_dpnt_ctfn_prvdd_f prv,
1902: ben_per_in_ler pil
1903: where ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1904: and ecd.cvg_strt_dt is not null
1905: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
1906: and ecd.elig_cvrd_dpnt_id = prv.elig_cvrd_dpnt_id
1907: and prv.dpnt_dsgn_ctfn_recd_dt is NULL
1908: and ecd.business_group_id = p_business_group_id
1909: and p_effective_date between ecd.effective_start_date
2592: p_pl_bnf_id => p_pl_bnf_id,
2593: p_rqd_flag => l_rqd_flag, --Bug 6353069
2594: p_post_rslt_flag => 'N',
2595: p_business_group_id => p_business_group_id,
2596: p_datetrack_mode => hr_api.g_update,
2597: p_rslt_object_version_number => l_rslt.object_version_number);
2598: --
2599: if g_debug then
2600: hr_utility.set_location ('Leaving '||l_proc, 10);
3141: procedure determine_dpnt_miss_actn_items
3142: (p_validate in boolean default false
3143: ,p_effective_date in date
3144: ,p_business_group_id in number
3145: ,p_datetrack_mode in varchar2 default hr_api.g_correction
3146: ,p_prtt_enrt_rslt_id in number
3147: ,p_rslt_object_version_number in out nocopy number
3148: ,p_post_rslt_flag in varchar2 default 'Y'
3149: ,p_dpnt_actn_warning out nocopy boolean) is
3315: and person_id = v_person_id
3316: and business_group_id = p_business_group_id
3317: and p_effective_date
3318: between nvl(date_start, p_effective_date)
3319: and nvl(date_end, hr_api.g_eot)));
3320: --
3321: -- Cursor to retrieve dependent certification information at the plan level
3322: --
3323: cursor c_dpnt_ctfn_pl(v_pl_id number, v_dpnt_person_id number, v_person_id number) is
3339: and person_id = v_person_id
3340: and business_group_id = p_business_group_id
3341: and p_effective_date
3342: between nvl(date_start, p_effective_date)
3343: and nvl(date_end, hr_api.g_eot)));
3344: --
3345: -- Cursor to retrieve dependent certification at the program level
3346: --
3347: cursor c_dpnt_ctfn_pgm(v_pgm_id number, v_dpnt_person_id number, v_person_id number) is
3363: and person_id = v_person_id
3364: and business_group_id = p_business_group_id
3365: and p_effective_date
3366: between nvl(date_start, p_effective_date)
3367: and nvl(date_end, hr_api.g_eot)));
3368:
3369: --
3370: -- Cursor to retrieve dependent certifications at the ptip level
3371: --
3388: and person_id = v_person_id
3389: and business_group_id = p_business_group_id
3390: and p_effective_date
3391: between nvl(date_start, p_effective_date)
3392: and nvl(date_end, hr_api.g_eot)));
3393:
3394: --
3395: -- Cursor to fetch the covered dependents for an enrt_rslt_id
3396: --
3405: ben_per_in_ler pil
3406: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3407: and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
3408: and ecd.cvg_strt_dt is not null
3409: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
3410: and ecd.business_group_id = p_business_group_id
3411: and p_effective_date between
3412: ecd.effective_start_date and ecd.effective_end_date
3413: and p_effective_date between
4266: ,p_business_group_id => p_business_group_id
4267: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4268: ,p_rslt_object_version_number => p_rslt_object_version_number
4269: ,p_effective_date => p_effective_date
4270: ,p_datetrack_mode => hr_api.g_zap
4271: ,p_post_rslt_flag => p_post_rslt_flag);
4272: --
4273: if g_debug then
4274: hr_utility.set_location ('Leaving l_ff_ctfns_exists',999);
4425: p_prtt_enrt_rslt_id in number
4426: ,p_effective_date in date
4427: ,p_business_group_id in number
4428: ,p_validate in boolean default FALSE
4429: ,p_datetrack_mode in varchar2 default hr_api.g_correction
4430: ,p_post_rslt_flag in varchar2 default 'Y'
4431: ,p_rslt_object_version_number in out nocopy number
4432: ,p_suspend_flag in out nocopy varchar2
4433: ) is
4531: (p_validate in boolean default FALSE
4532: ,p_prtt_enrt_rslt_id in number
4533: ,p_effective_date in date
4534: ,p_business_group_id in number
4535: ,p_datetrack_mode in varchar2 default hr_api.g_correction
4536: ,p_post_rslt_flag in varchar2 default 'Y'
4537: ,p_rslt_object_version_number in out nocopy number
4538: ,p_suspend_flag in out nocopy varchar2) is
4539: --
4581: ,p_suspend_flag => p_suspend_flag
4582: ,p_validate => p_validate);
4583:
4584: if p_validate then
4585: raise hr_api.validate_enabled;
4586: end if;
4587: --
4588: if g_debug then
4589: hr_utility.set_location ('Leaving '||l_proc, 10);
4590: end if;
4591: --
4592: exception
4593: --
4594: when hr_api.validate_enabled
4595: then
4596: rollback to process_cwb_actn_items;
4597: --
4598: if g_debug then
4617: (p_validate in boolean default FALSE
4618: ,p_prtt_enrt_rslt_id in number
4619: ,p_effective_date in date
4620: ,p_business_group_id in number
4621: ,p_datetrack_mode in varchar2 default hr_api.g_correction
4622: ,p_post_rslt_flag in varchar2 default 'Y'
4623: ,p_allws_flag in varchar2
4624: ,p_dsgn_cd in varchar2
4625: ,p_rslt_object_version_number in out nocopy number
4650: from ben_elig_cvrd_dpnt_f ecd,
4651: ben_per_in_ler pil
4652: where ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4653: and ecd.cvg_strt_dt is not null
4654: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
4655: and ecd.business_group_id = p_business_group_id
4656: and p_effective_date between
4657: ecd.effective_start_date and ecd.effective_end_date
4658: and pil.per_in_ler_id=ecd.per_in_ler_id
4956: ,p_business_group_id => p_business_group_id
4957: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4958: ,p_rslt_object_version_number => p_rslt_object_version_number
4959: ,p_effective_date => p_effective_date
4960: ,p_datetrack_mode => hr_api.g_zap
4961: ,p_post_rslt_flag => p_post_rslt_flag);
4962: --
4963: end loop;
4964: else
5032: procedure complete_dependent_designation
5033: (p_prtt_enrt_rslt_id in number
5034: ,p_effective_date in date
5035: ,p_business_group_id in number
5036: ,p_datetrack_mode in varchar2 default hr_api.g_correction
5037: ,p_post_rslt_flag in varchar2 default 'Y'
5038: ,p_dsgn_cd in varchar2
5039: ,p_rslt_object_version_number in out nocopy number
5040: ,p_dpnt_actn_warning in out nocopy boolean) is
5095: from ben_elig_cvrd_dpnt_f ecd,
5096: ben_per_in_ler pil
5097: where p_prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
5098: and ecd.cvg_strt_dt is not null
5099: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
5100: and ecd.business_group_id = p_business_group_id
5101: and p_effective_date between ecd.effective_start_date
5102: and ecd.effective_end_date
5103: and pil.per_in_ler_id=ecd.per_in_ler_id
5142: per_contact_relationships pcr,
5143: ben_per_in_ler pil
5144: where ecd.prtt_enrt_rslt_id = perslt.prtt_enrt_rslt_id
5145: and ecd.cvg_strt_dt is not null
5146: and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
5147: and ecd.business_group_id = p_business_group_id
5148: and c_effective_date between
5149: ecd.effective_start_date and ecd.effective_end_date
5150: and perslt.person_id = pcr.person_id
5386: (p_validate in boolean default FALSE
5387: ,p_prtt_enrt_rslt_id in number
5388: ,p_effective_date in date
5389: ,p_business_group_id in number
5390: ,p_datetrack_mode in varchar2 default hr_api.g_correction
5391: ,p_post_rslt_flag in varchar2 default 'Y'
5392: ,p_rslt_object_version_number in out nocopy number
5393: ,p_suspend_flag in out nocopy varchar2
5394: ,p_dpnt_actn_warning out nocopy boolean
5543: -- End Bug No 4525608
5544:
5545: --
5546: if p_validate then
5547: raise hr_api.validate_enabled;
5548: end if;
5549: --
5550: if g_debug then
5551: hr_utility.set_location ('Leaving '||l_proc, 10);
5552: end if;
5553: --
5554: exception
5555: --
5556: when hr_api.validate_enabled
5557: then
5558: rollback to process_dpnt_actn_items;
5559: --
5560: if g_debug then
5906: select 'Y'
5907: from ben_elig_cvrd_dpnt_f egd
5908: where egd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5909: and egd.per_in_ler_id = p_per_in_ler_id
5910: and egd.cvg_thru_dt = hr_api.g_eot
5911: and not exists (select null
5912: from ben_elig_cvrd_dpnt_f egd2
5913: where egd.elig_cvrd_dpnt_id = egd2.elig_cvrd_dpnt_id
5914: and egd.dpnt_person_id = egd2.dpnt_person_id
6130: ,p_effective_date in date
6131: ,p_business_group_id in number
6132: ,p_validate in boolean default FALSE
6133: ,p_enrt_bnft_id in number default NULL
6134: ,p_datetrack_mode in varchar2 default hr_api.g_correction
6135: ,p_post_rslt_flag in varchar2 default 'Y'
6136: ,p_rslt_object_version_number in out nocopy number
6137: ,p_suspend_flag in out nocopy varchar2
6138: ,p_ctfn_actn_warning out nocopy boolean) is
6477: if (l_prtt_enrt_actn_id is not null) then
6478: --
6479: -- Delete all certifications of type 'BNFT' for the action item
6480: --
6481: If p_datetrack_mode = hr_api.g_zap then
6482: l_datetrack_mode := hr_api.g_zap;
6483: Elsif p_datetrack_mode = hr_api.g_delete then
6484: l_datetrack_mode := hr_api.g_delete;
6485: Elsif p_datetrack_mode = hr_api.g_correction then
6478: --
6479: -- Delete all certifications of type 'BNFT' for the action item
6480: --
6481: If p_datetrack_mode = hr_api.g_zap then
6482: l_datetrack_mode := hr_api.g_zap;
6483: Elsif p_datetrack_mode = hr_api.g_delete then
6484: l_datetrack_mode := hr_api.g_delete;
6485: Elsif p_datetrack_mode = hr_api.g_correction then
6486: l_datetrack_mode := hr_api.g_zap;
6479: -- Delete all certifications of type 'BNFT' for the action item
6480: --
6481: If p_datetrack_mode = hr_api.g_zap then
6482: l_datetrack_mode := hr_api.g_zap;
6483: Elsif p_datetrack_mode = hr_api.g_delete then
6484: l_datetrack_mode := hr_api.g_delete;
6485: Elsif p_datetrack_mode = hr_api.g_correction then
6486: l_datetrack_mode := hr_api.g_zap;
6487: Elsif p_datetrack_mode = hr_api.g_update then
6480: --
6481: If p_datetrack_mode = hr_api.g_zap then
6482: l_datetrack_mode := hr_api.g_zap;
6483: Elsif p_datetrack_mode = hr_api.g_delete then
6484: l_datetrack_mode := hr_api.g_delete;
6485: Elsif p_datetrack_mode = hr_api.g_correction then
6486: l_datetrack_mode := hr_api.g_zap;
6487: Elsif p_datetrack_mode = hr_api.g_update then
6488: l_datetrack_mode := hr_api.g_delete;
6481: If p_datetrack_mode = hr_api.g_zap then
6482: l_datetrack_mode := hr_api.g_zap;
6483: Elsif p_datetrack_mode = hr_api.g_delete then
6484: l_datetrack_mode := hr_api.g_delete;
6485: Elsif p_datetrack_mode = hr_api.g_correction then
6486: l_datetrack_mode := hr_api.g_zap;
6487: Elsif p_datetrack_mode = hr_api.g_update then
6488: l_datetrack_mode := hr_api.g_delete;
6489: Else
6482: l_datetrack_mode := hr_api.g_zap;
6483: Elsif p_datetrack_mode = hr_api.g_delete then
6484: l_datetrack_mode := hr_api.g_delete;
6485: Elsif p_datetrack_mode = hr_api.g_correction then
6486: l_datetrack_mode := hr_api.g_zap;
6487: Elsif p_datetrack_mode = hr_api.g_update then
6488: l_datetrack_mode := hr_api.g_delete;
6489: Else
6490: l_datetrack_mode := hr_api.g_delete;
6483: Elsif p_datetrack_mode = hr_api.g_delete then
6484: l_datetrack_mode := hr_api.g_delete;
6485: Elsif p_datetrack_mode = hr_api.g_correction then
6486: l_datetrack_mode := hr_api.g_zap;
6487: Elsif p_datetrack_mode = hr_api.g_update then
6488: l_datetrack_mode := hr_api.g_delete;
6489: Else
6490: l_datetrack_mode := hr_api.g_delete;
6491: End if;
6484: l_datetrack_mode := hr_api.g_delete;
6485: Elsif p_datetrack_mode = hr_api.g_correction then
6486: l_datetrack_mode := hr_api.g_zap;
6487: Elsif p_datetrack_mode = hr_api.g_update then
6488: l_datetrack_mode := hr_api.g_delete;
6489: Else
6490: l_datetrack_mode := hr_api.g_delete;
6491: End if;
6492: --
6486: l_datetrack_mode := hr_api.g_zap;
6487: Elsif p_datetrack_mode = hr_api.g_update then
6488: l_datetrack_mode := hr_api.g_delete;
6489: Else
6490: l_datetrack_mode := hr_api.g_delete;
6491: End if;
6492: --
6493: for l_enrt_ctfn in c_enrt_ctfn(l_prtt_enrt_actn_id) loop
6494: --
6601: --
6602: end if; -- p_enrt_bnft_id
6603: --
6604: if p_validate then
6605: raise hr_api.validate_enabled;
6606: end if;
6607: --
6608: if g_debug then
6609: hr_utility.set_location ('Leaving ' ||l_proc,10);
6610: end if;
6611: --
6612: exception
6613: --
6614: when hr_api.validate_enabled
6615: then
6616: rollback to determine_other_actn_items;
6617: if g_debug then
6618: hr_utility.set_location ('Leaving ' ||l_proc,10);
6635: ,p_effective_date in date
6636: ,p_business_group_id in number
6637: ,p_elig_cvrd_dpnt_id in number
6638: ,p_validate in boolean default FALSE
6639: ,p_datetrack_mode in varchar2 default hr_api.g_correction
6640: ,p_post_rslt_flag in varchar2 default 'Y'
6641: ,p_pcp_dpnt_dsgn_cd in varchar2
6642: ,p_rslt_object_version_number in out nocopy number
6643: ,p_suspend_flag in out nocopy varchar2
6789: end if; -- rqd_flag = 'Y'
6790: --
6791: p_pcp_dpnt_actn_warning := l_pcp_dpnt_actn_warning;
6792: if p_validate then
6793: raise hr_api.validate_enabled;
6794: end if;
6795: --
6796: if g_debug then
6797: hr_utility.set_location ('Leaving '||l_proc, 10);
6799: --
6800:
6801: exception
6802: --
6803: when hr_api.validate_enabled
6804: then
6805: rollback to determine_pcp_dpnt_actn_items;
6806: --
6807: if g_debug then
6828: (p_prtt_enrt_rslt_id in number
6829: ,p_effective_date in date
6830: ,p_business_group_id in number
6831: ,p_validate in boolean default FALSE
6832: ,p_datetrack_mode in varchar2 default hr_api.g_correction
6833: ,p_post_rslt_flag in varchar2 default 'Y'
6834: ,p_pcp_dsgn_cd in varchar2
6835: ,p_rslt_object_version_number in out nocopy number
6836: ,p_suspend_flag in out nocopy varchar2
6968: end if; -- rqd_flag = 'Y'
6969: --
6970: p_pcp_actn_warning := l_pcp_actn_warning;
6971: if p_validate then
6972: raise hr_api.validate_enabled;
6973: end if;
6974: --
6975: if g_debug then
6976: hr_utility.set_location ('Leaving '||l_proc, 10);
6978: --
6979:
6980: exception
6981: --
6982: when hr_api.validate_enabled
6983: then
6984: rollback to determine_pcp_actn_items;
6985: --
6986: if g_debug then
7009: (p_validate in boolean default FALSE
7010: ,p_prtt_enrt_rslt_id in number
7011: ,p_effective_date in date
7012: ,p_business_group_id in number
7013: ,p_datetrack_mode in varchar2 default hr_api.g_correction
7014: ,p_post_rslt_flag in varchar2 default 'Y'
7015: ,p_rslt_object_version_number in out nocopy number
7016: ,p_suspend_flag in out nocopy varchar2
7017: ,p_pcp_dpnt_actn_warning out nocopy boolean) is
7040: where cop.oipl_id = pen.oipl_id
7041: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7042: and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
7043: and ecd.cvg_strt_dt is not null
7044: and nvl(ecd.cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
7045: and pen.business_group_id = p_business_group_id
7046: and cop.business_group_id = p_business_group_id
7047: and ecd.business_group_id = p_business_group_id
7048: and p_effective_date between ecd.effective_start_date
7063: where pcp.pl_id = pen.pl_id
7064: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7065: and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
7066: and ecd.cvg_strt_dt is not null
7067: and nvl(ecd.cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
7068: and pen.business_group_id = p_business_group_id
7069: and pcp.business_group_id = p_business_group_id
7070: and p_effective_date between ecd.effective_start_date
7071: and ecd.effective_end_date
7162: --
7163: p_pcp_dpnt_actn_warning := l_pcp_dpnt_actn_warning;
7164: --
7165: if p_validate then
7166: raise hr_api.validate_enabled;
7167: end if;
7168: --
7169: if g_debug then
7170: hr_utility.set_location ('Leaving '||l_proc, 10);
7171: end if;
7172: --
7173: exception
7174: --
7175: when hr_api.validate_enabled
7176: then
7177: rollback to process_pcp_dpnt_actn_items;
7178: --
7179: if g_debug then
7201: (p_validate in boolean default FALSE
7202: ,p_prtt_enrt_rslt_id in number
7203: ,p_effective_date in date
7204: ,p_business_group_id in number
7205: ,p_datetrack_mode in varchar2 default hr_api.g_correction
7206: ,p_post_rslt_flag in varchar2 default 'Y'
7207: ,p_rslt_object_version_number in out nocopy number
7208: ,p_suspend_flag in out nocopy varchar2
7209: ,p_pcp_actn_warning out nocopy boolean) is
7311: --
7312: p_pcp_actn_warning := l_pcp_actn_warning;
7313: --
7314: if p_validate then
7315: raise hr_api.validate_enabled;
7316: end if;
7317: --
7318: if g_debug then
7319: hr_utility.set_location ('Leaving '||l_proc, 10);
7320: end if;
7321: --
7322: exception
7323: --
7324: when hr_api.validate_enabled
7325: then
7326: rollback to process_pcp_actn_items;
7327: --
7328: if g_debug then
7602: procedure determine_bnf_miss_actn_items
7603: (p_prtt_enrt_rslt_id in number
7604: ,p_effective_date in date
7605: ,p_business_group_id in number
7606: ,p_datetrack_mode in varchar2 default hr_api.g_correction
7607: ,p_post_rslt_flag in varchar2 default 'Y'
7608: ,p_rslt_object_version_number in out nocopy number
7609: ,p_bnf_actn_warning out nocopy boolean) is
7610: --
7652: and person_id = v_person_id
7653: and business_group_id = p_business_group_id
7654: and p_effective_date
7655: between nvl(date_start, p_effective_date)
7656: and nvl(date_end, hr_api.g_eot)))
7657: and pl.business_group_id = p_business_group_id
7658: and p_effective_date between
7659: pl.effective_start_date and pl.effective_end_date;
7660: --
7672: from ben_pl_bnf_f pbd,
7673: ben_per_in_ler pil
7674: where pbd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7675: and pbd.dsgn_strt_dt is not null
7676: and nvl(pbd.dsgn_thru_dt, hr_api.g_eot) = hr_api.g_eot
7677: and pbd.business_group_id = p_business_group_id
7678: and p_effective_date between pbd.effective_start_date
7679: and pbd.effective_end_date
7680: and pil.per_in_ler_id=pbd.per_in_ler_id
7700: and person_id = v_person_id
7701: and business_group_id = p_business_group_id
7702: and p_effective_date
7703: between nvl(date_start, p_effective_date)
7704: and nvl(date_end, hr_api.g_eot)))
7705: and business_group_id = p_business_group_id
7706: and p_effective_date between effective_start_date
7707: and effective_end_date;
7708: cursor c_person_id is
8394: p_business_group_id => p_business_group_id,
8395: p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
8396: p_rslt_object_version_number => p_rslt_object_version_number,
8397: p_effective_date => p_effective_date,
8398: p_datetrack_mode => hr_api.g_zap,
8399: p_post_rslt_flag => p_post_rslt_flag
8400: );
8401:
8402: -- we need to delete warning also that was created for this action item
8473: procedure determine_bnf_actn_items
8474: (p_prtt_enrt_rslt_id in number
8475: ,p_effective_date in date
8476: ,p_business_group_id in number
8477: ,p_datetrack_mode in varchar2 default hr_api.g_correction
8478: ,p_post_rslt_flag in varchar2 default 'Y'
8479: ,p_rslt_object_version_number in out nocopy number
8480: ,p_bnf_actn_item_open out nocopy boolean
8481: ,p_hack out nocopy varchar2) is
8626: ,p_business_group_id => p_business_group_id
8627: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
8628: ,p_rslt_object_version_number => p_rslt_object_version_number
8629: ,p_effective_date => p_effective_date
8630: ,p_datetrack_mode => hr_api.g_zap
8631: ,p_post_rslt_flag => p_post_rslt_flag);
8632: --
8633: end loop;
8634: --
8707: (p_validate in boolean default FALSE
8708: ,p_prtt_enrt_rslt_id in number
8709: ,p_effective_date in date
8710: ,p_business_group_id in number
8711: ,p_datetrack_mode in varchar2 default hr_api.g_correction
8712: ,p_post_rslt_flag in varchar2 default 'Y'
8713: ,p_rslt_object_version_number in out nocopy number
8714: ,p_bnf_actn_warning in out nocopy boolean) is
8715: --
8853: and plb.business_group_id = p_business_group_id
8854: and p_effective_date between plb.effective_start_date
8855: and plb.effective_end_date
8856: and p_effective_date between nvl(date_start, p_effective_date) -- bug 5362890
8857: and nvl(date_end, hr_api.g_eot)
8858: and pil.per_in_ler_id=plb.per_in_ler_id
8859: and pil.business_group_id=p_business_group_id
8860: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
8861: UNION -- Bug 5156111 : Added union clause
9226: (p_prtt_enrt_rslt_id in number
9227: ,p_effective_date in date
9228: ,p_business_group_id in number
9229: ,p_validate in boolean default FALSE
9230: ,p_datetrack_mode in varchar2 default hr_api.g_correction
9231: ,p_post_rslt_flag in varchar2 default 'Y'
9232: ,p_rslt_object_version_number in out nocopy number
9233: ,p_suspend_flag in out nocopy varchar2
9234: ,p_bnf_actn_warning out nocopy boolean) is
9314: --
9315: p_bnf_actn_warning := l_bnf_actn_warning;
9316: --
9317: if p_validate then
9318: raise hr_api.validate_enabled;
9319: end if;
9320: --
9321: if g_debug then
9322: hr_utility.set_location ('Leaving '||l_proc, 10);
9323: end if;
9324: --
9325: exception
9326: --
9327: when hr_api.validate_enabled
9328: then
9329: --
9330: rollback to process_bnf_actn_items;
9331: if g_debug then
9354: ,p_effective_date in date
9355: ,p_business_group_id in number
9356: ,p_validate in boolean default false
9357: ,p_enrt_bnft_id in number default null
9358: ,p_datetrack_mode in varchar2 default hr_api.g_correction
9359: ,p_post_rslt_flag in varchar2 default 'Y'
9360: ,p_rslt_object_version_number in out nocopy number
9361: ,p_suspend_flag out nocopy varchar2
9362: ,p_dpnt_actn_warning out nocopy boolean
9692: --
9693: -- When in validation only mode raise the Validate_Enabled exception
9694: --
9695: if p_validate then
9696: raise hr_api.validate_enabled;
9697: end if;
9698: --
9699: p_suspend_flag := l_suspend_flag;
9700: --
9703: end if;
9704: --
9705: exception
9706: --
9707: when hr_api.validate_enabled
9708: then
9709: -- rollback to the savepoint
9710: --
9711: ROLLBACK TO determine_action_items;