[Home] [Help]
PACKAGE BODY: APPS.BEN_CREATE_PTNL_LER_SS
Source
1 PACKAGE BODY ben_create_ptnl_ler_ss AS
2 /* $Header: belerwrs.pkb 120.0 2005/05/28 03:22:21 appldev noship $*/
3
4 -- Package scope global variables.
5
6 g_package varchar2(31) := 'BEN_CREATE_PTNL_LER_SS';
7 g_api_name varchar2(72):= g_package||'.PROCESS_API';
8 g_data_error exception;
9
10
11
12
13 --This is an overloaded procedure which will call the actual procedure
14 PROCEDURE create_ptnl_ler_for_per
15 (p_validate in varchar2 default 'N'
16 ,p_ptnl_ler_for_per_id out nocopy varchar2
17 ,p_csd_by_ptnl_ler_for_per_id in varchar2 default null
18 ,p_lf_evt_ocrd_dt in out nocopy varchar2
19 ,p_ptnl_ler_for_per_stat_cd in varchar2 default null
20 ,p_ptnl_ler_for_per_src_cd in varchar2 default null
21 ,p_mnl_dt in varchar2 default null
22 ,p_enrt_perd_id in varchar2 default null
23 ,p_ler_id in varchar2 default null
24 ,p_person_id in varchar2 default null
25 ,p_business_group_id in varchar2 default null
26 ,p_dtctd_dt in varchar2 default null
27 ,p_procd_dt in varchar2 default null
28 ,p_unprocd_dt in varchar2 default null
29 ,p_voidd_dt in varchar2 default null
30 ,p_mnlo_dt in varchar2 default null
31 ,p_ntfn_dt in varchar2 default null
32 ,p_request_id in varchar2 default null
33 ,p_program_application_id in varchar2 default null
34 ,p_program_id in varchar2 default null
35 ,p_program_update_date in varchar2 default null
36 ,p_object_version_number out nocopy varchar2
37 ,p_effective_date in varchar2
38 ,p_item_type in varchar2
39 ,p_item_key in varchar2
40 ,p_activity_id in varchar2
41 ,p_login_person_id in varchar2 default null
42 ,P_flow_mode in varchar2
43 ,p_subflow_mode in varchar2
44 ,p_life_event_name in varchar2
45 ,p_transaction_step_id out nocopy varchar2
46 ,p_error_message out nocopy long
47 ,p_hire_dt in varchar2 default null
48 ) is
49
50 l_ptnl_ler_for_per_id ben_ptnl_ler_for_per.ptnl_ler_for_per_id%type := to_number(p_ptnl_ler_for_per_id);
51 l_object_version_number ben_ptnl_ler_for_per.object_version_number%type := to_number(p_object_version_number);
52 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type := to_number(p_transaction_step_id);
53 l_lf_evt_ocrd_date date;
54 l_ocrd_dt_cd hr_lookups.lookup_code%TYPE; -- UTF8 varchar2(30);
55
56 cursor get_ler is
57 select ler.ocrd_dt_det_cd
58 from ben_ler_f ler
59 where ler.business_group_id = to_number(p_business_group_id)
60 and ler.ler_id = to_number(p_ler_id)
61 and to_date(p_effective_date,hr_transaction_ss.g_date_format)
62 between ler.effective_start_date
63 and ler.effective_end_date;
64 BEGIN
65 -- CALL THE ACTUAL PROCEDURE HERE
66 /************cobra change********************/
67 --
68 if p_hire_dt is not null then
69
70 if (to_date(p_lf_evt_ocrd_dt,hr_transaction_ss.g_date_format) <=
71 to_date(p_hire_dt,hr_transaction_ss.g_date_format)) then
72 p_lf_evt_ocrd_dt := p_hire_dt;
73 end if;
74 end if;
75 open get_ler;
76 fetch get_ler into l_ocrd_dt_cd;
77 if get_ler%found then
78 if l_ocrd_dt_cd is not null then
79 ben_determine_date.main
80 (p_date_cd => l_ocrd_dt_cd
81 ,p_effective_date => to_date(p_effective_date,hr_transaction_ss.g_date_format)
82 ,p_lf_evt_ocrd_dt => to_date(p_lf_evt_ocrd_dt,hr_transaction_ss.g_date_format)
83 ,p_returned_date => l_lf_evt_ocrd_date
84 );
85 else
86 l_lf_evt_ocrd_date := to_date(p_lf_evt_ocrd_dt,hr_transaction_ss.g_date_format);
87 end if;
88 else
89 l_lf_evt_ocrd_date := to_date(p_lf_evt_ocrd_dt,hr_transaction_ss.g_date_format);
90 end if;
91 /********************************************/
92 --l_lf_evt_ocrd_date := to_date(p_lf_evt_ocrd_dt,hr_transaction_ss.g_date_format);
93 -- Replace the strings by date and number where required
94 create_ptnl_ler_for_per
95 (p_validate => p_validate -- in varchar2 default 'N'
96 ,p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id -- out number
97 ,p_csd_by_ptnl_ler_for_per_id => to_number(p_csd_by_ptnl_ler_for_per_id) -- in number default null
98 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date -- in date default null
99 ,p_ptnl_ler_for_per_stat_cd => p_ptnl_ler_for_per_stat_cd -- in varchar2 default null
100 ,p_ptnl_ler_for_per_src_cd => p_ptnl_ler_for_per_src_cd -- in varchar2 default null
101 ,p_mnl_dt => to_date(p_mnl_dt,hr_transaction_ss.g_date_format) -- in date default null
102 ,p_enrt_perd_id => to_number(p_enrt_perd_id) -- in number default null
103 ,p_ler_id => to_number(p_ler_id) -- in number default null
104 ,p_person_id => to_number(p_person_id) -- in number default null
105 ,p_business_group_id => to_number(p_business_group_id) -- in number
106 ,p_dtctd_dt => to_date(p_dtctd_dt,hr_transaction_ss.g_date_format) -- in date default null
107 ,p_procd_dt => to_date(p_procd_dt,hr_transaction_ss.g_date_format) -- in date default null
108 ,p_unprocd_dt => to_date(p_unprocd_dt,hr_transaction_ss.g_date_format) -- in date default null
109 ,p_voidd_dt => to_date(p_voidd_dt,hr_transaction_ss.g_date_format) -- in date default null
110 ,p_mnlo_dt => to_date(p_mnlo_dt,hr_transaction_ss.g_date_format) -- in date default null
111 ,p_ntfn_dt => to_date(p_ntfn_dt,hr_transaction_ss.g_date_format) -- in date default null
112 ,p_request_id => to_number(p_request_id) -- in number default null
113 ,p_program_application_id => to_number(p_program_application_id) -- in number default null
114 ,p_program_id => to_number(p_program_id) -- in number default null
115 ,p_program_update_date => to_date(p_program_update_date,hr_transaction_ss.g_date_format) -- in date default null
116 ,p_object_version_number => l_object_version_number --out number
117 ,p_effective_date => to_date(p_effective_date,hr_transaction_ss.g_date_format) -- in date
118 ,p_item_type => p_item_type -- in varchar2
119 ,p_item_key => p_item_key -- in varchar2
120 ,p_activity_id => to_number(p_activity_id) -- in number
121 ,p_login_person_id => to_number(p_login_person_id) -- in number default null
122 ,P_flow_mode => P_flow_mode -- in varchar2 -- This may have a value of Insert, Update
123 ,p_subflow_mode => p_subflow_mode -- in varchar2
124 ,p_life_event_name => p_life_event_name -- in varchar2
125 ,p_transaction_step_id => l_transaction_step_id -- out number
126 ,p_error_message => p_error_message -- out long
127 );
128 -- assign the out parameters
129 p_ptnl_ler_for_per_id := to_char(l_ptnl_ler_for_per_id);
130 p_object_version_number:= to_char(l_object_version_number);
131 p_transaction_step_id := to_char(l_transaction_step_id);
132 p_lf_evt_ocrd_dt := to_char(l_lf_evt_ocrd_date,hr_transaction_ss.g_date_format);
133 --
134 EXCEPTION
135 when others then
136 raise;
137 END create_ptnl_ler_for_per;
138
139 PROCEDURE create_ptnl_ler_for_per
140 (p_validate in varchar2 default 'N'
141 ,p_ptnl_ler_for_per_id out nocopy number
142 ,p_csd_by_ptnl_ler_for_per_id in number default null
143 ,p_lf_evt_ocrd_dt in date default null
144 ,p_ptnl_ler_for_per_stat_cd in varchar2 default null
145 ,p_ptnl_ler_for_per_src_cd in varchar2 default null
146 ,p_mnl_dt in date default null
147 ,p_enrt_perd_id in number default null
148 ,p_ler_id in number default null
149 ,p_person_id in number default null
150 ,p_business_group_id in number
151 ,p_dtctd_dt in date default null
152 ,p_procd_dt in date default null
153 ,p_unprocd_dt in date default null
154 ,p_voidd_dt in date default null
155 ,p_mnlo_dt in date default null
156 ,p_ntfn_dt in date default null
157 ,p_request_id in number default null
158 ,p_program_application_id in number default null
159 ,p_program_id in number default null
160 ,p_program_update_date in date default null
161 ,p_object_version_number out nocopy number
162 ,p_effective_date in date
163 ,p_item_type in varchar2
164 ,p_item_key in varchar2
165 ,p_activity_id in number
166 ,p_login_person_id in number default null
167 ,P_flow_mode in varchar2 -- This may have a value of Insert, Update
168 ,p_subflow_mode in varchar2 -- This may have a value of Insert and Cobra
169 ,p_life_event_name in varchar2
170 ,p_transaction_step_id out nocopy number
171 ,p_error_message out nocopy long
172 ) IS
173
174 --declare a cursor to check if the person actually exists in the database
175 cursor csr_person is select person_id from per_all_people_f where person_id = p_person_id;
176
177
178 l_transaction_table hr_transaction_ss.transaction_table;
179 l_count INTEGER := 0;
180 l_transaction_id hr_api_transaction_steps.transaction_id%type default null;
181 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
182 l_trans_object_version_number hr_api_transaction_steps.object_version_number%type;
183
184 -- variable for the create person call
185 l_original_person_id per_all_people_f.person_id%type;
186 l_review_item_name varchar2(50);
187 l_result varchar2(100) default null;
188 l_person_id per_all_people_f.person_id%type := p_person_id;
189 l_object_version_number per_all_people_f.object_version_number%type ;
190 l_effective_start_date date;
191 l_effective_end_date date ;
192 l_full_name per_all_people_f.full_name%type;
193 l_comment_id number;
194 l_name_combination_warning boolean ;
195 l_orig_hire_warning boolean ;
196 l_proc varchar2(72) := g_package||'CREATE_PTNL_LER_FOR_PER';
197 -- dummy variables
198 l_dummy_num number;
199 l_dummy_date date;
200 l_dummy_char varchar2(1000);
201 l_dummy_bool boolean;
202
203 BEGIN
204 hr_utility.set_location('Entering:'|| l_proc, 5);
205
206
207 -- Call the actual API.
208 --
209 -- In case the person Id doesn't exists in the database
210 -- we need to do the validation there are three cases.
211 -- 1) The person actually exists in database( Page invoked from menu)
212 -- 2) The page is being used in normal registration flow ( one row in Transaction table)
213 -- 3) The page is being used in Cobra registration flow( Two rows in transaction tables)
214 --
215 savepoint create_life_event;
216 open csr_person;
217 fetch csr_person into l_original_person_id;
218 if csr_person%notfound then
219 hr_contact_api.create_person
220 (p_validate => false
221 ,p_start_date => p_effective_date
222 ,p_business_group_id => p_business_group_id
223 ,p_last_name => 'XcXXXXX'-- can hard code it is not going to be committed
224 ,p_sex => 'M'
225 ,p_person_id => l_person_id
226 ,p_object_version_number => l_object_version_number
227 ,p_effective_start_date => l_effective_start_date
228 ,p_effective_end_date => l_effective_end_date
229 ,p_full_name => l_full_name
230 ,p_comment_id => l_comment_id
231 ,p_name_combination_warning => l_name_combination_warning
232 ,p_orig_hire_warning => l_orig_hire_warning
233 );
234 end if;
235 close csr_person;
236
237 -- We should have a value for l_person_id by now
238 if (l_person_id is not null ) then
239 --
240 -- call the api to create potential life event
241
242 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
243 (p_validate => true
244 ,p_ptnl_ler_for_per_id => l_dummy_num -- out number
245 ,p_csd_by_ptnl_ler_for_per_id => p_csd_by_ptnl_ler_for_per_id -- in number default null
246 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt -- in date default null
247 ,p_ptnl_ler_for_per_stat_cd => p_ptnl_ler_for_per_stat_cd -- in varchar2 default null
248 ,p_ptnl_ler_for_per_src_cd => p_ptnl_ler_for_per_src_cd -- in varchar2 default null
249 ,p_mnl_dt => p_mnl_dt -- in date default null
250 ,p_enrt_perd_id => p_enrt_perd_id -- in number default null
251 ,p_ler_id => p_ler_id -- in number default null
252 ,p_person_id => l_person_id -- in number default null
253 ,p_business_group_id => p_business_group_id -- in number default null
254 ,p_dtctd_dt => p_dtctd_dt -- in date default null
255 ,p_procd_dt => p_procd_dt -- in date default null
256 ,p_unprocd_dt => p_unprocd_dt -- in date default null
257 ,p_voidd_dt => p_voidd_dt -- in date default null
258 ,p_mnlo_dt => p_mnlo_dt -- in date default null
259 ,p_ntfn_dt => p_ntfn_dt -- in date default null
260 ,p_request_id => p_request_id -- in number default null
261 ,p_program_application_id => p_program_application_id -- in number default null
262 ,p_program_id => p_program_id -- in number default null
263 ,p_program_update_date => p_program_update_date -- in date default null
264 ,p_object_version_number => p_object_version_number -- out number
265 ,p_effective_date => p_effective_date --in date
266 );
267 end if;
268
269
270 -- Now rollback all the changes which have been made.
271 --
272 ROLLBACK to create_life_event;
273 --
274 -- -----------------------------------------------------------------------------
275 -- We will write the data to transaction tables.
276 -- Determine if a transaction step exists for this activity
277 -- if a transaction step does exist then the transaction_step_id and
278 -- object_version_number are set (i.e. not null).
279 -- -----------------------------------------------------------------------------
280 --
281 --
282 -- First, check if transaction id exists or not
283 --
284 l_transaction_id := hr_transaction_ss.get_transaction_id
285 (p_item_type => p_item_type
286 ,p_item_key => p_item_key);
287 --
288 IF l_transaction_id is null THEN
289
290 -- Start a Transaction
291
292 hr_transaction_ss.start_transaction
293 (itemtype => p_item_type
294 ,itemkey => p_item_key
295 ,actid => p_activity_id
296 ,funmode => 'RUN'
297 ,p_login_person_id => nvl(p_login_person_id, p_person_id) -- PB : Modification
298 ,result => l_result);
299
300 -- need to take care of l_result
301
302 l_transaction_id := hr_transaction_ss.get_transaction_id
303 (p_item_type => p_item_type
304 ,p_item_key => p_item_key);
305 ELSE
306 -- since transaction id is present look for the step if it already exists
307 get_step(p_item_type => p_item_type
308 ,p_item_key => p_item_key
309 ,p_activity_id => p_activity_id
310 ,p_api_name => g_api_name
311 ,p_flow_mode => p_flow_mode
312 ,p_subflow_mode=> p_subflow_mode
313 ,p_transaction_step_id => l_transaction_step_id
314 ,p_object_version_number => l_trans_object_version_number);
315
316 END IF;
317 -- if l_transaction_step_id is null then create a transaction step Id
318 -- otherwise just update the current step
319 if( l_transaction_step_id is null) then
320 -- Create a transaction step
321 --
322 hr_transaction_api.create_transaction_step
323 (p_validate => false
324 ,p_creator_person_id => nvl(p_login_person_id, p_person_id) -- PB : Modification
325 ,p_transaction_id => l_transaction_id
326 ,p_api_name => g_package || '.PROCESS_API'
327 ,p_item_type => p_item_type
328 ,p_item_key => p_item_key
329 ,p_activity_id => p_activity_id
330 ,p_transaction_step_id => l_transaction_step_id
331 ,p_object_version_number => l_trans_object_version_number);
332
333 end if;
334 --
335
336
337 l_count := 1;
338 l_transaction_table(l_count).param_name := 'P_CSD_BY_PTNL_LER_FOR_PER_ID';
339 l_transaction_table(l_count).param_value := p_csd_by_ptnl_ler_for_per_id;
340 l_transaction_table(l_count).param_data_type := 'NUMBER';
341
342
343 l_count := l_count + 1;
344 l_transaction_table(l_count).param_name := 'P_LF_EVT_OCRD_DT';
345 l_transaction_table(l_count).param_value := to_char(p_lf_evt_ocrd_dt,
346 hr_transaction_ss.g_date_format);
347 l_transaction_table(l_count).param_data_type := 'DATE';
348
349
350 l_count := l_count + 1;
351 l_transaction_table(l_count).param_name := 'P_PTNL_LER_FOR_PER_STAT_CD';
352 l_transaction_table(l_count).param_value := p_ptnl_ler_for_per_stat_cd;
353 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
354
355 l_count := l_count + 1;
356 l_transaction_table(l_count).param_name := 'P_PTNL_LER_FOR_PER_SRC_CD';
357 l_transaction_table(l_count).param_value := p_ptnl_ler_for_per_src_cd;
358 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
359
360
361 l_count := l_count + 1;
362 l_transaction_table(l_count).param_name := 'P_MNL_DT';
363 l_transaction_table(l_count).param_value := to_char(p_mnl_dt,
364 hr_transaction_ss.g_date_format);
365 l_transaction_table(l_count).param_data_type := 'DATE';
366
367 l_count := l_count + 1;
368 l_transaction_table(l_count).param_name := 'P_ENRT_PERD_ID';
369 l_transaction_table(l_count).param_value := p_enrt_perd_id;
370 l_transaction_table(l_count).param_data_type := 'NUMBER';
371
372 l_count := l_count + 1;
373 l_transaction_table(l_count).param_name := 'P_LER_ID';
374 l_transaction_table(l_count).param_value := p_ler_id;
375 l_transaction_table(l_count).param_data_type := 'NUMBER';
376
377 l_count := l_count + 1;
378 l_transaction_table(l_count).param_name := 'P_PERSON_ID';
379 l_transaction_table(l_count).param_value := p_person_id;
380 l_transaction_table(l_count).param_data_type := 'NUMBER';
381
382 l_count := l_count + 1;
383 l_transaction_table(l_count).param_name := 'P_BUSINESS_GROUP_ID';
384 l_transaction_table(l_count).param_value := p_business_group_id;
385 l_transaction_table(l_count).param_data_type := 'NUMBER';
386
387 l_count := l_count + 1;
388 l_transaction_table(l_count).param_name := 'P_DTCTD_DT';
389 l_transaction_table(l_count).param_value := to_char(p_dtctd_dt,
390 hr_transaction_ss.g_date_format);
391 l_transaction_table(l_count).param_data_type := 'DATE';
392
393 l_count := l_count + 1;
394 l_transaction_table(l_count).param_name := 'P_PROCD_DT';
395 l_transaction_table(l_count).param_value := to_char(p_procd_dt,
396 hr_transaction_ss.g_date_format);
397 l_transaction_table(l_count).param_data_type := 'DATE';
398
399 l_count := l_count + 1;
400 l_transaction_table(l_count).param_name := 'P_UNPROCD_DT';
401 l_transaction_table(l_count).param_value := to_char(p_unprocd_dt,
402 hr_transaction_ss.g_date_format);
403 l_transaction_table(l_count).param_data_type := 'DATE';
404
405 l_count := l_count + 1;
406 l_transaction_table(l_count).param_name := 'P_VOIDD_DT';
407 l_transaction_table(l_count).param_value := to_char(p_voidd_dt,
408 hr_transaction_ss.g_date_format);
409 l_transaction_table(l_count).param_data_type := 'DATE';
410
411 l_count := l_count + 1;
412 l_transaction_table(l_count).param_name := 'P_MNLO_DT';
413 l_transaction_table(l_count).param_value := to_char(p_mnlo_dt,
414 hr_transaction_ss.g_date_format);
415 l_transaction_table(l_count).param_data_type := 'DATE';
416
417 l_count := l_count + 1;
418 l_transaction_table(l_count).param_name := 'P_NTFN_DT';
419 l_transaction_table(l_count).param_value := to_char(p_ntfn_dt,
420 hr_transaction_ss.g_date_format);
421 l_transaction_table(l_count).param_data_type := 'DATE';
422
423 l_count := l_count + 1;
424 l_transaction_table(l_count).param_name := 'P_REQUEST_ID';
425 l_transaction_table(l_count).param_value := p_request_id;
426 l_transaction_table(l_count).param_data_type := 'NUMBER';
427
428 l_count := l_count + 1;
429 l_transaction_table(l_count).param_name := 'P_PROGRAM_APPLICATION_ID';
430 l_transaction_table(l_count).param_value := p_program_application_id;
431 l_transaction_table(l_count).param_data_type := 'NUMBER';
432
433
434 l_count := l_count + 1;
435 l_transaction_table(l_count).param_name := 'P_PROGRAM_ID';
436 l_transaction_table(l_count).param_value := p_program_id;
437 l_transaction_table(l_count).param_data_type := 'NUMBER';
438
439 l_count := l_count + 1;
440 l_transaction_table(l_count).param_name := 'P_PROGRAM_UPDATE_DATE';
441 l_transaction_table(l_count).param_value := to_char(p_program_update_date,
442 hr_transaction_ss.g_date_format);
443 l_transaction_table(l_count).param_data_type := 'DATE';
444
445
446 l_count := l_count + 1;
447 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
448 l_transaction_table(l_count).param_value := to_char(p_effective_date,
449 hr_transaction_ss.g_date_format);
450 l_transaction_table(l_count).param_data_type := 'DATE';
451
452 l_count := l_count + 1;
453 l_transaction_table(l_count).param_name := 'P_ITEM_TYPE';
454 l_transaction_table(l_count).param_value := p_item_type;
455 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
456
457 l_count := l_count + 1;
458 l_transaction_table(l_count).param_name := 'P_ITEM_KEY';
459 l_transaction_table(l_count).param_value := p_item_key;
460 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
461
462 l_count := l_count + 1;
463 l_transaction_table(l_count).param_name := 'P_ACTIVITY_ID';
464 l_transaction_table(l_count).param_value := p_activity_id;
465 l_transaction_table(l_count).param_data_type := 'NUMBER';
466
467
468 l_count := l_count + 1;
469 l_transaction_table(l_count).param_name := 'P_LOGIN_PERSON_ID';
470 l_transaction_table(l_count).param_value := p_login_person_id;
471 l_transaction_table(l_count).param_data_type := 'NUMBER';
472
473 l_count := l_count + 1;
474 l_transaction_table(l_count).param_name := 'P_FLOW_MODE';
475 l_transaction_table(l_count).param_value := p_flow_mode;
476 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
477
478 l_count := l_count + 1;
479 l_transaction_table(l_count).param_name := 'P_SUBFLOW_MODE';
480 l_transaction_table(l_count).param_value := p_subflow_mode;
481 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
482
483 l_count := l_count + 1;
484 l_transaction_table(l_count).param_name := 'P_LIFE_EVENT_NAME';
485 l_transaction_table(l_count).param_value := p_life_event_name;
486 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
487
488
489 l_count := l_count + 1;
490 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
491 l_transaction_table(l_count).param_value := p_activity_id;
492 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
493
494 l_review_item_name := wf_engine.GetActivityAttrText(itemtype => p_item_type,
495 itemkey => p_item_key,
496 actid => p_activity_id,
497 aname => gv_wf_review_region_item);
498
499 l_count := l_count + 1;
500 l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
501 l_transaction_table(l_count).param_value := l_review_item_name;
502 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
503
504
505 hr_transaction_ss.save_transaction_step
506 (p_item_type => p_item_type
507 ,p_item_key => p_item_key
508 ,p_actid => p_activity_id
509 ,p_login_person_id => nvl(p_login_person_id, p_person_id) -- PB Modification
510 ,p_transaction_step_id => l_transaction_step_id
511 ,p_api_name => g_api_name
512 ,p_transaction_data => l_transaction_table);
513 -- put the out parameter for transaction step
514 -- This will be null if any error is raised in processing.
515 p_transaction_step_id := l_transaction_step_id;
516
517 if p_subflow_mode = 'COBRA' then
518 wf_engine.SetItemAttrText (itemtype => p_item_type,
519 itemkey => p_item_key,
520 aname => 'LIFE_EVENT_TRANSACTION_STEP',
521 avalue => to_char(l_transaction_step_id));
522 end if;
523
524
525 hr_utility.set_location('Leaving:'|| l_proc, 10);
526
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
531 p_error_message => p_error_message);
532 raise;
533 END create_ptnl_ler_for_per;
534
535
536
537
538 -- ---------------------------------------------------------------------------
539 -- ---------------------- < get_ptnl_ler_from_tt> -------------------------
540 -- ---------------------------------------------------------------------------
541 -- Purpose: This procedure will get transaction data which are saved earlier
542 -- in the current transaction. This is invoked when a user click BACK
543 -- button to go back from the Review page to Update page to correct
544 -- typos or make further changes. Hence, we need to use the item_type
545 -- item_key passed in to retrieve the transaction record.
546 -- This is an overloaded version.
547 -- ---------------------------------------------------------------------------
548 --
549 PROCEDURE get_ptnl_ler_data_from_tt
550 (p_item_type in varchar2
551 ,p_item_key in varchar2
552 ,p_activity_id in varchar2
553 ,p_trans_rec_count out nocopy number
554 ,p_csd_by_ptnl_ler_for_per_id out nocopy number -- in number default null
555 ,p_lf_evt_ocrd_dt out nocopy date -- in date default null
556 ,p_ptnl_ler_for_per_stat_cd out nocopy varchar2 -- in varchar2 default null
557 ,p_ptnl_ler_for_per_src_cd out nocopy varchar2 -- in varchar2 default null
558 ,p_mnl_dt out nocopy date -- in date default null
559 ,p_enrt_perd_id out nocopy number -- in number default null
560 ,p_ler_id out nocopy number -- in number default null
561 ,p_person_id out nocopy number -- in number default null
562 ,p_business_group_id out nocopy number -- in number default null
563 ,p_dtctd_dt out nocopy date -- in date default null
564 ,p_procd_dt out nocopy date -- in date default null
565 ,p_unprocd_dt out nocopy date -- in date default null
566 ,p_voidd_dt out nocopy date -- in date default null
567 ,p_mnlo_dt out nocopy date -- in date default null
568 ,p_ntfn_dt out nocopy date -- in date default null
569 ,p_request_id out nocopy number -- in number default null
570 ,p_program_application_id out nocopy number -- in number default null
571 ,p_program_id out nocopy number -- in number default null
572 ,p_program_update_date out nocopy date -- in date default null
573 ,p_effective_date out nocopy date
574 ,p_flow_mode in varchar2
575 ,p_subflow_mode in varchar2
576 ,p_life_event_name out nocopy varchar2
577 ) is
578 l_transaction_id hr_api_transaction_steps.transaction_id%type default null;
579 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
580 l_trans_object_version_number hr_api_transaction_steps.object_version_number%type;
581 l_proc varchar2(72) := g_package||'.GET_PTNL_LER_DATA_FROM_TT';
582
583
584 BEGIN
585
586 hr_utility.set_location('Entering:'|| l_proc, 5);
587
588 --
589 -- This call will get us the step Id for the Api name passed in
590 -- and the subflow mode passed in
591
592 get_step(p_item_type => p_item_type
593 ,p_item_key => p_item_key
594 ,p_activity_id => p_activity_id
595 ,p_api_name => g_api_name
596 ,p_flow_mode => p_flow_mode
597 ,p_subflow_mode => p_subflow_mode
598 ,p_transaction_step_id => l_transaction_step_id
599 ,p_object_version_number => l_trans_object_version_number);
600 --
601 --
602 -- -------------------------------------------------------------------
603 -- There are some changes made earlier in the transaction.
604 -- Retrieve the data and return to caller.
605 -- -------------------------------------------------------------------
606 --
607 -- Now get the transaction data for the given step
608 get_ptnl_ler_data_from_tt
609 (p_transaction_step_id => l_transaction_step_id
610 ,p_csd_by_ptnl_ler_for_per_id => p_csd_by_ptnl_ler_for_per_id
611 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
612 ,p_ptnl_ler_for_per_stat_cd => p_ptnl_ler_for_per_stat_cd
613 ,p_ptnl_ler_for_per_src_cd => p_ptnl_ler_for_per_src_cd
614 ,p_mnl_dt => p_mnl_dt
615 ,p_enrt_perd_id => p_enrt_perd_id
616 ,p_ler_id => p_ler_id
617 ,p_person_id => p_person_id
618 ,p_business_group_id => p_business_group_id
619 ,p_dtctd_dt => p_dtctd_dt
620 ,p_procd_dt => p_procd_dt
621 ,p_unprocd_dt => p_unprocd_dt
622 ,p_voidd_dt => p_voidd_dt
623 ,p_mnlo_dt => p_mnlo_dt
624 ,p_ntfn_dt => p_ntfn_dt
625 ,p_request_id => p_request_id
626 ,p_program_application_id => p_program_application_id
627 ,p_program_id => p_program_id
628 ,p_program_update_date => p_program_update_date
629 ,p_effective_date => p_effective_date
630 ,p_flow_mode => p_flow_mode
631 ,p_subflow_mode => p_subflow_mode
632 ,p_life_event_name => p_life_event_name
633 );
634
635
636
637 hr_utility.set_location('Leaving:'|| l_proc, 10);
638
639 EXCEPTION
640 WHEN g_data_error THEN
641 RAISE;
642
643 END get_ptnl_ler_data_from_tt;
644
645
646 -- ---------------------------------------------------------------------------
647 -- ---------------------- < get_ptnl_ler_data_from_tt> -------------------------
648 -- ---------------------------------------------------------------------------
649 -- Purpose: This procedure will get transaction data which are pending for
650 -- approval in workflow for a transaction step id.
651 -- ---------------------------------------------------------------------------
652 --
653 procedure get_ptnl_ler_data_from_tt
654 (p_transaction_step_id in number
655 ,p_csd_by_ptnl_ler_for_per_id out nocopy number -- in number default null
656 ,p_lf_evt_ocrd_dt out nocopy date -- in date default null
657 ,p_ptnl_ler_for_per_stat_cd out nocopy varchar2 -- in varchar2 default null
658 ,p_ptnl_ler_for_per_src_cd out nocopy varchar2 -- in varchar2 default null
659 ,p_mnl_dt out nocopy date -- in date default null
660 ,p_enrt_perd_id out nocopy number -- in number default null
661 ,p_ler_id out nocopy number -- in number default null
662 ,p_person_id out nocopy number -- in number default null
663 ,p_business_group_id out nocopy number -- in number default null
664 ,p_dtctd_dt out nocopy date -- in date default null
665 ,p_procd_dt out nocopy date -- in date default null
666 ,p_unprocd_dt out nocopy date -- in date default null
667 ,p_voidd_dt out nocopy date -- in date default null
668 ,p_mnlo_dt out nocopy date -- in date default null
669 ,p_ntfn_dt out nocopy date -- in date default null
670 ,p_request_id out nocopy number -- in number default null
671 ,p_program_application_id out nocopy number -- in number default null
672 ,p_program_id out nocopy number -- in number default null
673 ,p_program_update_date out nocopy date -- in date default null
674 ,p_effective_date out nocopy date
675 ,p_flow_mode in varchar2
676 ,p_subflow_mode in varchar2
677 ,p_life_event_name out nocopy varchar2
678 )is
679 l_proc varchar2(72) := g_package||'GET_PTNL_LER_DATA_FROM_TT';
680
681 begin
682 hr_utility.set_location('Entering:'|| l_proc, 5);
683
684 --
685 p_csd_by_ptnl_ler_for_per_id:= hr_transaction_api.get_number_value
686 (p_transaction_step_id => p_transaction_step_id
687 ,p_name => 'P_CSD_BY_PTNL_LER_FOR_PER_ID');
688 --
689 p_lf_evt_ocrd_dt := hr_transaction_api.get_date_value
690 (p_transaction_step_id => p_transaction_step_id
691 ,p_name => 'P_LF_EVT_OCRD_DT');
692 --
693 p_ptnl_ler_for_per_stat_cd := hr_transaction_api.get_varchar2_value
694 (p_transaction_step_id => p_transaction_step_id
695 ,p_name => 'P_PTNL_LER_FOR_PER_STAT_CD');
696 --
697 p_ptnl_ler_for_per_src_cd := hr_transaction_api.get_varchar2_value
698 (p_transaction_step_id => p_transaction_step_id
699 ,p_name => 'P_PTNL_LER_FOR_PER_SRC_CD');
700 --
701 p_mnl_dt := hr_transaction_api.get_date_value
702 (p_transaction_step_id => p_transaction_step_id
703 ,p_name => 'P_MNL_DT');
704 --
705 p_enrt_perd_id := hr_transaction_api.get_number_value
706 (p_transaction_step_id => p_transaction_step_id
707 ,p_name => 'P_ENRT_PERD_ID');
708
709 --
710 p_ler_id := hr_transaction_api.get_number_value
711 (p_transaction_step_id => p_transaction_step_id
712 ,p_name => 'P_LER_ID');
713 --
714 p_person_id :=
715 hr_transaction_api.get_number_value
716 (p_transaction_step_id => p_transaction_step_id
717 ,p_name => 'P_PERSON_ID');
718 --
719 p_business_group_id :=
720 hr_transaction_api.get_number_value
721 (p_transaction_step_id => p_transaction_step_id
722 ,p_name => 'P_BUSINESS_GROUP_ID');
723 --
724 p_dtctd_dt :=
725 hr_transaction_api.get_date_value
726 (p_transaction_step_id => p_transaction_step_id
727 ,p_name => 'P_DTCTD_DT');
728
729
730 p_procd_dt :=
731 hr_transaction_api.get_date_value
732 (p_transaction_step_id => p_transaction_step_id
733 ,p_name => 'P_PROCD_DT');
734 --
735 p_unprocd_dt :=
736 hr_transaction_api.get_date_value
737 (p_transaction_step_id => p_transaction_step_id
738 ,p_name => 'P_UNPROCD_DT');
739 --
740 p_voidd_dt :=
741 hr_transaction_api.get_date_value
742 (p_transaction_step_id => p_transaction_step_id
743 ,p_name => 'P_VOIDD_DT');
744 --
745 p_mnlo_dt :=
746 hr_transaction_api.get_date_value
747 (p_transaction_step_id => p_transaction_step_id
748 ,p_name => 'P_MNLO_DT');
749 --
750 p_ntfn_dt :=
751 hr_transaction_api.get_date_value
752 (p_transaction_step_id => p_transaction_step_id
753 ,p_name => 'P_NTFN_DT');
754 --
755 p_request_id :=
756 hr_transaction_api.get_number_value
757 (p_transaction_step_id => p_transaction_step_id
758 ,p_name => 'P_REQUEST_ID');
759 --
760 p_program_application_id :=
761 hr_transaction_api.get_number_value
762 (p_transaction_step_id => p_transaction_step_id
763 ,p_name => 'P_PROGRAM_APPLICATION_ID');
764 --
765 p_program_id :=
766 hr_transaction_api.get_number_value
767 (p_transaction_step_id => p_transaction_step_id
768 ,p_name => 'P_PROGRAM_ID');
769 --
770 p_program_update_date :=
771 hr_transaction_api.get_date_value
772 (p_transaction_step_id => p_transaction_step_id
773 ,p_name => 'P_PROGRAM_UPDATE_DATE');
774 --
775 p_effective_date :=
776 hr_transaction_api.get_date_value
777 (p_transaction_step_id => p_transaction_step_id
778 ,p_name => 'P_EFFECTIVE_DATE');
779
780 --
781 /*
782 p_flow_mode :=
783 hr_transaction_api.get_varchar2_value
784 (p_transaction_step_id => p_transaction_step_id
785 ,p_name => 'P_FLOW_MODE');
786
787 --
788 p_subflow_mode :=
789 hr_transaction_api.get_varchar2_value
790 (p_transaction_step_id => p_transaction_step_id
791 ,p_name => 'P_SUBFLOW_MODE');
792
793 */
794 --
795 p_life_event_name :=
796 hr_transaction_api.get_varchar2_value
797 (p_transaction_step_id => p_transaction_step_id
798 ,p_name => 'P_LIFE_EVENT_NAME');
799
800 hr_utility.set_location('Leaving:'|| l_proc, 10);
801 --
802
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 RAISE;
807
808 END get_ptnl_ler_data_from_tt;
809
810 /*----------------------------------------------------------------------------
811 | |
812 | Name : process_api |
813 | |
814 | Purpose : This will procedure is invoked whenever approver |
815 | approves the address change. |
816 | |
817 -----------------------------------------------------------------------------*/
818 --
819 PROCEDURE process_api
820 (p_validate IN BOOLEAN DEFAULT FALSE
821 ,p_transaction_step_id IN NUMBER DEFAULT NULL
822 )is
823
824 l_validate BOOLEAN := false;
825 l_dummy_num number;
826 l_effective_date date;
827 l_ovn NUMBER;
828 l_ptnl_ler ben_ptnl_ler_for_per%ROWTYPE;
829 l_proc varchar2(72) := g_package||'PROCESS_API';
830
831 BEGIN
832
833 hr_utility.set_location('Entering:'|| l_proc, 5);
834
835 --
836 if p_validate is not null then
837 l_validate := p_validate;
838 end if;
839
840 -- Get the data from the transaction tables.
841
842 -- This may be a New user registration / Cobra Registration and we may not
843 -- have a person in the database.
844 if (( hr_process_person_ss.g_person_id is not null) and
845 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID)) then
846 l_ptnl_ler.person_id := hr_process_person_ss.g_person_id;
847 end if;
848 --
849 if l_ptnl_ler.person_id is null then
850 l_ptnl_ler.person_id :=
851 hr_transaction_api.get_number_value
852 (p_transaction_step_id => p_transaction_step_id
853 ,p_name => 'P_PERSON_ID');
854 end if;
855
856 if l_ptnl_ler.person_id is null then
857 null;
858 else
859 --
860 l_ptnl_ler.csd_by_ptnl_ler_for_per_id:= hr_transaction_api.get_number_value
861 (p_transaction_step_id => p_transaction_step_id
862 ,p_name => 'P_CSD_BY_PTNL_LER_FOR_PER_ID');
863 --
864 l_ptnl_ler.lf_evt_ocrd_dt := hr_transaction_api.get_date_value
865 (p_transaction_step_id => p_transaction_step_id
866 ,p_name => 'P_LF_EVT_OCRD_DT');
867 --
868 l_ptnl_ler.ptnl_ler_for_per_stat_cd := hr_transaction_api.get_varchar2_value
869 (p_transaction_step_id => p_transaction_step_id
870 ,p_name => 'P_PTNL_LER_FOR_PER_STAT_CD');
871 --
872 l_ptnl_ler.ptnl_ler_for_per_src_cd := hr_transaction_api.get_varchar2_value
873 (p_transaction_step_id => p_transaction_step_id
874 ,p_name => 'P_PTNL_LER_FOR_PER_SRC_CD');
875 --
876 l_ptnl_ler.mnl_dt := hr_transaction_api.get_date_value
877 (p_transaction_step_id => p_transaction_step_id
878 ,p_name => 'P_MNL_DT');
879 --
880 l_ptnl_ler.enrt_perd_id := hr_transaction_api.get_number_value
881 (p_transaction_step_id => p_transaction_step_id
882 ,p_name => 'P_ENRT_PERD_ID');
883
884 --
885 l_ptnl_ler.ler_id := hr_transaction_api.get_number_value
886 (p_transaction_step_id => p_transaction_step_id
887 ,p_name => 'P_LER_ID');
888 --
889 l_ptnl_ler.business_group_id :=
890 hr_transaction_api.get_number_value
891 (p_transaction_step_id => p_transaction_step_id
892 ,p_name => 'P_BUSINESS_GROUP_ID');
893 --
894 l_ptnl_ler.dtctd_dt :=
895 hr_transaction_api.get_date_value
896 (p_transaction_step_id => p_transaction_step_id
897 ,p_name => 'P_DTCTD_DT');
898
899
900 l_ptnl_ler.procd_dt :=
901 hr_transaction_api.get_date_value
902 (p_transaction_step_id => p_transaction_step_id
903 ,p_name => 'P_PROCD_DT');
904 --
905 l_ptnl_ler.unprocd_dt :=
906 hr_transaction_api.get_date_value
907 (p_transaction_step_id => p_transaction_step_id
908 ,p_name => 'P_UNPROCD_DT');
909 --
910 l_ptnl_ler.voidd_dt :=
911 hr_transaction_api.get_date_value
912 (p_transaction_step_id => p_transaction_step_id
913 ,p_name => 'P_VOIDD_DT');
914 --
915 l_ptnl_ler.mnlo_dt :=
916 hr_transaction_api.get_date_value
917 (p_transaction_step_id => p_transaction_step_id
918 ,p_name => 'P_MNLO_DT');
919 --
920 l_ptnl_ler.ntfn_dt :=
921 hr_transaction_api.get_date_value
922 (p_transaction_step_id => p_transaction_step_id
923 ,p_name => 'P_NTFN_DT');
924 --
925 l_ptnl_ler.request_id :=
926 hr_transaction_api.get_number_value
927 (p_transaction_step_id => p_transaction_step_id
928 ,p_name => 'P_REQUEST_ID');
929 --
930 l_ptnl_ler.program_application_id :=
931 hr_transaction_api.get_number_value
932 (p_transaction_step_id => p_transaction_step_id
933 ,p_name => 'P_PROGRAM_APPLICATION_ID');
934 --
935 l_ptnl_ler.program_id :=
936 hr_transaction_api.get_number_value
937 (p_transaction_step_id => p_transaction_step_id
938 ,p_name => 'P_PROGRAM_ID');
939 --
940 l_ptnl_ler.program_update_date :=
941 hr_transaction_api.get_date_value
942 (p_transaction_step_id => p_transaction_step_id
943 ,p_name => 'P_PROGRAM_UPDATE_DATE');
944 --
945 l_effective_date :=
946 hr_transaction_api.get_date_value
947 (p_transaction_step_id => p_transaction_step_id
948 ,p_name => 'P_EFFECTIVE_DATE');
949
950 --
951 -- These two can be removed if not used in the future.
952 /*
953 l_ptnl_ler.p_flow_mode :=
954 hr_transaction_api.get_varchar2_value
955 (p_transaction_step_id => p_transaction_step_id
956 ,p_name => 'P_FLOW_MODE');
957 --
958 l_ptnl_ler.p_life_event_name :=
959 hr_transaction_api.get_varchar2_value
960 (p_transaction_step_id => p_transaction_step_id
961 ,p_name => 'P_LIFE_EVENT_NAME');
962 */
963
964 -- This may be a New user registration / Cobra Registration and we may not
965 -- have a person in the database.
966 /*if (( hr_process_person_ss.g_person_id is not null) and
967 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID)) then
968 l_ptnl_ler.person_id := hr_process_person_ss.g_person_id;
969 end if;*/
970
971
972 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
973 (p_validate => l_validate
974 ,p_ptnl_ler_for_per_id => l_dummy_num -- out number
975 ,p_csd_by_ptnl_ler_for_per_id => l_ptnl_ler.csd_by_ptnl_ler_for_per_id -- in number default null
976 ,p_lf_evt_ocrd_dt => l_ptnl_ler.lf_evt_ocrd_dt -- in date default null
977 ,p_ptnl_ler_for_per_stat_cd => l_ptnl_ler.ptnl_ler_for_per_stat_cd -- in varchar2 default null
978 ,p_ptnl_ler_for_per_src_cd => l_ptnl_ler.ptnl_ler_for_per_src_cd -- in varchar2 default null
979 ,p_mnl_dt => l_ptnl_ler.mnl_dt -- in date default null
980 ,p_enrt_perd_id => l_ptnl_ler.enrt_perd_id -- in number default null
981 ,p_ler_id => l_ptnl_ler.ler_id -- in number default null
982 ,p_person_id => l_ptnl_ler.person_id -- in number default null
983 ,p_business_group_id => l_ptnl_ler.business_group_id -- in number default null
984 ,p_dtctd_dt => l_ptnl_ler.dtctd_dt -- in date default null
985 ,p_procd_dt => l_ptnl_ler.procd_dt -- in date default null
986 ,p_unprocd_dt => l_ptnl_ler.unprocd_dt -- in date default null
987 ,p_voidd_dt => l_ptnl_ler.voidd_dt -- in date default null
988 ,p_mnlo_dt => l_ptnl_ler.mnlo_dt -- in date default null
989 ,p_ntfn_dt => l_ptnl_ler.ntfn_dt -- in date default null
990 ,p_request_id => l_ptnl_ler.request_id -- in number default null
991 ,p_program_application_id => l_ptnl_ler.program_application_id -- in number default null
992 ,p_program_id => l_ptnl_ler.program_id -- in number default null
993 ,p_program_update_date => l_ptnl_ler.program_update_date -- in date default null
994 ,p_object_version_number => l_ovn -- out number
995 ,p_effective_date => l_effective_date --in date
996 );
997 end if;
998
999 hr_utility.set_location('Leaving:'|| l_proc, 10);
1000
1001 END process_api;
1002
1003 procedure get_step(
1004 p_item_type in varchar2
1005 ,p_item_key in varchar2
1006 ,p_activity_id in varchar2
1007 ,p_api_name in varchar2
1008 ,p_flow_mode in varchar2
1009 ,p_subflow_mode in varchar2
1010 ,p_transaction_step_id out nocopy number
1011 ,p_object_version_number out nocopy number
1012 ) is
1013 /* This procedure gets the transaction_step_id based on a given item_type and
1014 item_key
1015 */
1016 --
1017 cursor transaction_step is
1018 select transaction_step_id
1019 , object_version_number
1020 from hr_api_transaction_steps
1021 where item_type=p_item_type
1022 and item_key=p_item_key
1023 --and activity_id = p_activity_id --because when this is called from review page the activity id is different
1024 and api_name=p_api_name;
1025 --
1026 l_subflow_mode varchar2(31);
1027 begin
1028 open transaction_step;
1029 fetch transaction_step into p_transaction_step_id,p_object_version_number;
1030
1031 -- check if the step exists for the particular subflow
1032 loop
1033 l_subflow_mode :=
1034 hr_transaction_api.get_varchar2_value
1035 (p_transaction_step_id => p_transaction_step_id
1036 ,p_name => 'P_SUBFLOW_MODE');
1037 exit when l_subflow_mode = p_subflow_mode ;
1038 fetch transaction_step into p_transaction_step_id,p_object_version_number;
1039 if transaction_step%notfound then
1040 p_transaction_step_id:=null;
1041 p_object_version_number:=null;
1042 exit;
1043
1044 end if;
1045
1046 end loop;
1047 -- if no step is present it will return null for the step_id and transaction id otherwise
1048 -- the step_id and transaction_id will be returned for the subflow_mode
1049
1050 close transaction_step;
1051 end get_step;
1052
1053
1054 END ben_create_ptnl_ler_ss;