1 package ben_enrollment_action_items as
2 /* $Header: benactcm.pkh 120.2.12010000.1 2008/07/29 12:02:44 appldev ship $ */
3 --
4 --
5 -- Globals used
6 --
7 g_package varchar2(80) := 'ben_enrollment_action_items';
8
9 --
10 -- Constants used
11 --
12 YES_FOUND constant varchar2(30) := 'Y';
13 NOT_FOUND constant varchar2(30) := 'N';
14 YES_DONE constant varchar2(3) := 'Y';
15 NOT_DONE constant varchar2(3) := 'N';
16 USE_DPNT constant varchar2(5) := 'DPNT';
17 USE_BNF constant varchar2(5) := 'BNF';
18 --
19 -- date track mode constants
20 --
21 DTMODE_INSERT constant varchar2(30) := hr_api.g_insert;
22 DTMODE_UPDATE constant varchar2(30) := hr_api.g_update;
23 DTMODE_CORRECT constant varchar2(30) := hr_api.g_correction;
24 DTMODE_DELETE constant varchar2(30) := hr_api.g_delete;
25 DTMODE_ZAP constant varchar2(30) := hr_api.g_zap;
26 DTMODE_SYSDATE constant varchar2(30) := hr_api.g_sys;
27 DTMODE_STARTOFTIME constant varchar2(30) := hr_api.g_sot;
28 DTMODE_ENDOFTIME constant varchar2(30) := hr_api.g_eot;
29
30 -- cursor to get pl data and to see if bnfs are required.
31 -- If bnf's are not rqd, action item isn't rqd either.
32 cursor g_bnf_pl (p_prtt_enrt_rslt_id number
33 ,p_effective_date date) is
34 select decode(pl.bnf_dsgn_cd, 'R', 'Y', null, null, 'N') rqd,
35 pl.pl_id,
36 pl.susp_if_bnf_ssn_nt_prv_cd,
37 pl.susp_if_bnf_dob_nt_prv_cd,
38 pl.susp_if_bnf_adr_nt_prv_cd,
39 pl.susp_if_ctfn_not_bnf_flag,
40 pl.bnf_ctfn_determine_cd,
41 pl.bnf_ctfn_rqd_flag,
42 pl.bnf_dsge_mnr_ttee_rqd_flag
43 from ben_pl_f pl,
44 ben_prtt_enrt_rslt_f pen
45 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
46 and pen.pl_id = pl.pl_id
47 and p_effective_date between
48 pen.effective_start_date and pen.effective_end_date
49 and p_effective_date between
50 pl.effective_start_date and pl.effective_end_date;
51
52 g_bnf_pl_rec g_bnf_pl%rowtype;
53
54 --
55 -- ----------------------------------------------------------------------------
56 -- |-----------------------< determine_action_items >-------------------------|
57 -- ----------------------------------------------------------------------------
58 --
59 procedure determine_action_items
60 (p_prtt_enrt_rslt_id in number
61 ,p_effective_date in date
62 ,p_business_group_id in number
63 ,p_validate in boolean default false
64 ,p_enrt_bnft_id in number default null
65 ,p_datetrack_mode in varchar2 default hr_api.g_correction
66 ,p_post_rslt_flag in varchar2 default 'Y'
67 ,p_rslt_object_version_number in out nocopy number
68 ,p_suspend_flag out nocopy varchar2
69 ,p_dpnt_actn_warning out nocopy boolean
70 ,p_bnf_actn_warning out nocopy boolean
71 ,p_ctfn_actn_warning out nocopy boolean
72 --,p_pcp_actn_warning out boolean
73 --,p_pcp_dpnt_actn_warning out boolean
74 );
75 --
76 -- this procedure is the main driver/entry point for action items
77 -- determines if desginated dependents and benficiaries meet all the criteria
78 -- for PL, PGM, and PTIP where necessary.
79 -- Added by Anil
80 -- ----------------------------------------------------------------------------
81 -- |--------------------< process_cwb_actn_items >------------------------|
82 -- ----------------------------------------------------------------------------
83 --
84 procedure process_cwb_actn_items
85 (p_validate in boolean default FALSE
86 ,p_prtt_enrt_rslt_id in number
87 ,p_effective_date in date
88 ,p_business_group_id in number
89 ,p_datetrack_mode in varchar2 default hr_api.g_correction
90 ,p_post_rslt_flag in varchar2 default 'Y'
91 ,p_rslt_object_version_number in out nocopy number
92 ,p_suspend_flag in out nocopy varchar2);
93 --
94 -- ----------------------------------------------------------------------------
95 -- |----------------------< process_dpnt_actn_items >-------------------------|
96 -- ----------------------------------------------------------------------------
97 --
98 procedure process_dpnt_actn_items
99 (p_validate in boolean default false
100 ,p_prtt_enrt_rslt_id in number
101 ,p_effective_date in date
102 ,p_business_group_id in number
103 ,p_datetrack_mode in varchar2 default hr_api.g_correction
104 ,p_post_rslt_flag in varchar2 default 'Y'
105 ,p_rslt_object_version_number in out nocopy number
106 ,p_suspend_flag in out nocopy varchar2
107 ,p_dpnt_actn_warning out nocopy boolean
108 --Bug No 4525608 to capture the certification required warning
109 ,p_ctfn_actn_warning out nocopy boolean);
110 --End Bug 4525608
111 --
112 -- ----------------------------------------------------------------------------
113 -- |--------------------< process_pcp_actn_items >------------------------|
114 -- ----------------------------------------------------------------------------
115 --
116 procedure process_pcp_actn_items
117 (p_validate in boolean default FALSE
118 ,p_prtt_enrt_rslt_id in number
119 ,p_effective_date in date
120 ,p_business_group_id in number
121 ,p_datetrack_mode in varchar2 default hr_api.g_correction
122 ,p_post_rslt_flag in varchar2 default 'Y'
123 ,p_rslt_object_version_number in out nocopy number
124 ,p_suspend_flag in out nocopy varchar2
125 ,p_pcp_actn_warning out nocopy boolean);
126 --
127 -- ----------------------------------------------------------------------------
128 -- |--------------------< process_pcp_dpnt_actn_items >------------------------|
129 -- ----------------------------------------------------------------------------
130 --
131 procedure process_pcp_dpnt_actn_items
132 (p_validate in boolean default FALSE
133 ,p_prtt_enrt_rslt_id in number
134 ,p_effective_date in date
135 ,p_business_group_id in number
136 ,p_datetrack_mode in varchar2 default hr_api.g_correction
137 ,p_post_rslt_flag in varchar2 default 'Y'
138 ,p_rslt_object_version_number in out nocopy number
139 ,p_suspend_flag in out nocopy varchar2
140 ,p_pcp_dpnt_actn_warning out nocopy boolean);
141 --
142 -- ----------------------------------------------------------------------------
143 -- |-----------------------< process_bnf_actn_items >-------------------------|
144 -- ----------------------------------------------------------------------------
145 --
146 procedure process_bnf_actn_items
147 (p_prtt_enrt_rslt_id in number
148 ,p_effective_date in date
149 ,p_business_group_id in number
150 ,p_validate in boolean default false
151 ,p_datetrack_mode in varchar2 default hr_api.g_correction
152 ,p_post_rslt_flag in varchar2 default 'Y'
153 ,p_rslt_object_version_number in out nocopy number
154 ,p_suspend_flag in out nocopy varchar2
155 ,p_bnf_actn_warning out nocopy boolean);
156 --
157 -- ----------------------------------------------------------------------------
158 -- |---------------------< complete_this_action_item >-----------------------|
159 -- ----------------------------------------------------------------------------
160 --
161 procedure complete_this_action_item
162 (p_prtt_enrt_actn_id in number
163 ,p_effective_date in date
164 ,p_validate in boolean default false
165 ,p_datetrack_mode in varchar2 default hr_api.g_correction
166 ,p_post_rslt_flag in varchar2 default 'Y');
167 --
168 -- this procedure will set the completed date for a single open action item
169 -- for a participant result both dependent and beneficiary
170 --
171 -- ----------------------------------------------------------------------------
172 -- |--------------------< determine_other_actn_items >------------------------|
173 -- ----------------------------------------------------------------------------
174 --
175 procedure determine_other_actn_items
176 (p_prtt_enrt_rslt_id in number
177 ,p_effective_date in date
178 ,p_business_group_id in number
179 ,p_validate in boolean default false
180 ,p_enrt_bnft_id in number default NULL
181 ,p_datetrack_mode in varchar2 default hr_api.g_correction
182 ,p_post_rslt_flag in varchar2 default 'Y'
183 ,p_rslt_object_version_number in out nocopy number
184 ,p_suspend_flag in out nocopy varchar2
185 ,p_ctfn_actn_warning out nocopy boolean
186 );
187 --
188 -- this procedure determines all other enrollment action items that need to
189 -- be written. We are checking the participant enrollment results looking for
190 -- certifications, ENRTCTFN, and required specific rates.
191 --
192 -- ----------------------------------------------------------------------------
193 -- |----------------------------< check_dpnt_ctfn >---------------------------|
194 -- ----------------------------------------------------------------------------
195 --
196 function check_dpnt_ctfn
197 (p_prtt_enrt_actn_id in number
198 ,p_elig_cvrd_dpnt_id in number
199 ,p_effective_date in date)
200 return boolean;
201 --
202 -- this function checks for certifications for an enrollment result.
203 -- check if certifications were provided. For this dependent check
204 -- if the dpnt_dsgn_ctfn_rqd_flag is 'Y' then write action item
205 -- if the dpnt_dsgn_ctfn_recd_dt IS NULL. The recd_dt is filled in via
206 -- a form interface.
207 -- we are also checking for at least one optional certification.
208 -- optional means dpnt_dsgn_ctfn_rqd_flag is 'N' for the ctfn_prvdd entry
209 -- with the dpnt_dsgn_ctfn_recd_dt not NULL. Returns 'Y' or 'N'
210 --
211 -- ----------------------------------------------------------------------------
212 -- |--------------------------< check_bnf_ctfn >------------------------------|
213 -- ----------------------------------------------------------------------------
214 --
215 function check_bnf_ctfn
216 (p_prtt_enrt_actn_id in number
217 ,p_pl_bnf_id in number
218 ,p_effective_date in date)
219 return boolean;
220 --
221 -- this function checks for certifications for an enrollment result.
222 -- check if certifications were provided. For this beneficiary check
223 -- if the bnf_ctfn_rqd_flag is 'Y' then write action item
224 -- if the bnf_ctfn_recd_dt IS NULL. The bnf_ctfn_recd_dt is filled in via
225 -- a form interface. Returns TRUE or FALSE.
226 --
227 -- ----------------------------------------------------------------------------
228 -- |-----------------------------< check_dob >--------------------------------|
229 -- ----------------------------------------------------------------------------
230 --
231 -- This function checks if the person has a date of birth.
232 --
233 function check_dob
234 (p_person_id in number
235 ,p_effective_date in date
236 ,p_business_group_id in number)
237 return boolean;
238 --
239 -- ----------------------------------------------------------------------------
240 -- |-----------------------------< check_adrs >-------------------------------|
241 -- ----------------------------------------------------------------------------
242 --
243 -- This function checks if the person has an address.
244 --
245 function check_adrs
246 (p_prtt_enrt_rslt_id in number
247 ,p_dpnt_bnf_person_id in number
248 ,p_effective_date in date
249 ,p_business_group_id in number)
250 return boolean;
251 --
252 -- ----------------------------------------------------------------------------
253 -- |-----------------------------< check_legid >------------------------------|
254 -- ----------------------------------------------------------------------------
255 --
256 -- This function checks if the person has an SSN or National Identifier.
257 --
258 function check_legid
259 (p_person_id in number
260 ,p_effective_date in date
261 ,p_business_group_id in number)
262 return boolean;
263 --
264 -- ----------------------------------------------------------------------------
265 -- |-------------------------< get_actn_typ_id >------------------------------|
266 -- ----------------------------------------------------------------------------
267 --
268 -- This function returns the action type id for a certain actn type code.
269 --
270 function get_actn_typ_id
271 (p_type_cd in varchar2
272 ,p_business_group_id in number)
273 return number;
274 --
275 -- ----------------------------------------------------------------------------
276 -- |-------------------------< get_prtt_enrt_actn_id >------------------------|
277 -- ----------------------------------------------------------------------------
278 --
279 procedure get_prtt_enrt_actn_id
280 (p_actn_typ_id in number
281 ,p_prtt_enrt_rslt_id in number
282 ,p_elig_cvrd_dpnt_id in number default null
283 ,p_pl_bnf_id in number default null
284 ,p_effective_date in date
285 ,p_business_group_id in number
286 ,p_prtt_enrt_actn_id out nocopy number
287 ,p_cmpltd_dt out nocopy date
288 ,p_object_version_number in out nocopy number);
289 --
290 -- ----------------------------------------------------------------------------
291 -- |-----------------------< write_new_action_item >--------------------------|
292 -- ----------------------------------------------------------------------------
293 --
294 procedure write_new_action_item
295 (p_prtt_enrt_rslt_id in number
296 ,p_rslt_object_version_number in out nocopy number
297 ,p_actn_typ_id in number
298 ,p_elig_cvrd_dpnt_id in number default null
299 ,p_pl_bnf_id in number default null
300 ,p_rqd_flag in varchar2 default 'Y'
301 ,p_cmpltd_dt in date default null
302 ,p_effective_date in date
303 ,p_business_group_id in number
304 ,p_post_rslt_flag in varchar2 default 'Y'
305 ,p_object_version_number out nocopy number
306 ,p_prtt_enrt_actn_id out nocopy number);
307 --
308 procedure process_action_item
309 (p_prtt_enrt_actn_id in out nocopy number
310 ,p_actn_typ_id in number
311 ,p_cmpltd_dt in date
312 ,p_object_version_number in out nocopy number
313 ,p_effective_date in date
314 ,p_rqd_data_found in boolean
315 ,p_prtt_enrt_rslt_id in number
316 ,p_elig_cvrd_dpnt_id in number default null
317 ,p_pl_bnf_id in number default null
318 ,p_rqd_flag in varchar2 default 'Y'
319 ,p_post_rslt_flag in varchar2 default 'Y'
320 ,p_business_group_id in number
321 ,p_datetrack_mode in varchar2
322 ,p_rslt_object_version_number in out nocopy number);
323 --
324 procedure process_new_ctfn_action(
325 p_prtt_enrt_rslt_id in number,
326 p_elig_cvrd_dpnt_id in number default null,
327 p_pl_bnf_id in number default null,
328 p_actn_typ_cd in varchar2,
329 p_ctfn_rqd_flag in varchar2,
330 p_ctfn_recd_dt in date default null,
331 p_business_group_id in number,
332 p_effective_date in date,
333 p_prtt_enrt_actn_id out nocopy number);
334 --
335 --
336 -- ----------------------------------------------------------------------------
337 -- |----------------------------< check_enrt_ctfn >---------------------------|
338 -- ----------------------------------------------------------------------------
339 --
340 function check_enrt_ctfn
341 (p_prtt_enrt_actn_id in number
342 ,p_prtt_enrt_rslt_id in number
343 ,p_effective_date in date)
344 return boolean;
345 --
346 --
347 end ben_enrollment_action_items;
348 --
[Home] [Help]