1 PACKAGE BODY PQP_NL_EXT_FUNCTIONS AS
2 /* $Header: pqpextff.pkb 120.3 2005/10/30 21:07 vjhanak noship $ */
3
4
5 --cursor to fetch the subcategory for the pension type
6 CURSOR c_get_subcat(c_pt IN varchar2) IS
7 SELECT distinct(pension_sub_category)
8 FROM pqp_pension_types_f
9 WHERE pension_type_id = fnd_number.canonical_to_number(c_pt);
10
11 --cursor to check the enabled flag for the lookup code before logging changes
12 CURSOR chk_if_enabled(c_code IN VARCHAR2) IS
13 SELECT enabled_flag
14 FROM hr_lookups
15 WHERE lookup_type = 'BEN_EXT_CHG_EVT'
16 AND lookup_code = c_code;
17
18 --
19 -- ----------------------------------------------------------------------------
20 -- |-------------------< create_org_pt_ins_chg_evt >-------------------------|
21 -- ----------------------------------------------------------------------------
22 --
23 PROCEDURE create_org_pt_ins_chg_evt (p_organization_id number
24 ,p_org_information1 varchar2
25 ,p_org_information2 varchar2
26 ,p_org_information3 varchar2
27 ,p_org_information6 varchar2
28 ,p_effective_date date
29 ) IS
30
31 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
32 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
33 l_business_group_id ben_ext_chg_evt_log.business_group_id%TYPE;
34 l_sub_cat pqp_pension_types_f.pension_sub_category%TYPE;
35 l_enabled varchar2(1) := 'N';
36
37 CURSOR c_get_bgid IS
38 SELECT business_group_id
39 FROM hr_all_organization_units
40 WHERE organization_id = p_organization_id;
41
42 BEGIN
43
44 --Fetch the business group id for the ORG
45 OPEN c_get_bgid;
46 FETCH c_get_bgid INTO l_business_group_id;
47 CLOSE c_get_bgid;
48
49 --fetch the sub category for the pension type
50 OPEN c_get_subcat(p_org_information3);
51 FETCH c_get_subcat INTO l_sub_cat;
52 CLOSE c_get_subcat;
53
54 --first check if the logging of participation dates is enabled or not
55 OPEN chk_if_enabled('COAPPD');
56 FETCH chk_if_enabled INTO l_enabled;
57 CLOSE chk_if_enabled;
58
59 IF l_enabled = 'Y' THEN
60
61 --Insert a row into the ben_chg_evt_log table for the current ORG for a person_id -1
62
63 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
64 (p_ext_chg_evt_log_id => l_log_id
65 ,p_chg_evt_cd => 'COAPPD'
66 ,p_chg_eff_dt => fnd_date.canonical_to_date(p_org_information1)
67 ,p_prmtr_01 => 'ORG'
68 ,p_prmtr_02 => p_org_information3
69 ,p_prmtr_03 => p_org_information6
70 ,p_prmtr_04 => p_organization_id
71 ,p_prmtr_05 => l_sub_cat
72 ,p_person_id => -1
73 ,p_business_group_id => l_business_group_id
74 ,p_object_version_number => l_ovn
75 ,p_effective_date => fnd_date.canonical_to_date(p_org_information1)
76 ,p_new_val1 => p_org_information1
77 ,p_new_val2 => p_org_information2
78 ,p_old_val1 => null
79 ,p_old_val2 => null
80 );
81 END IF;
82 END create_org_pt_ins_chg_evt;
83
84 --
85 -- ----------------------------------------------------------------------------
86 -- |-------------------< create_org_pt_upd_chg_evt >-------------------------|
87 -- ----------------------------------------------------------------------------
88 --
89 PROCEDURE create_org_pt_upd_chg_evt (p_organization_id number
90 ,p_org_information1 varchar2
91 ,p_org_information2 varchar2
92 ,p_org_information3 varchar2
93 ,p_org_information6 varchar2
94 ,p_org_information1_o varchar2
95 ,p_org_information2_o varchar2
96 ,p_org_information3_o varchar2
97 ,p_org_information6_o varchar2
98 ,p_effective_date date
99 ) IS
100
101 CURSOR c_get_chg_evt_log IS
102 SELECT ext_chg_evt_log_id,chg_evt_cd,chg_eff_dt,prmtr_01,
103 prmtr_02,prmtr_04,prmtr_05,business_group_id,object_version_number
104 FROM ben_ext_chg_evt_log
105 WHERE new_val1 = p_org_information1_o
106 AND new_val2 = nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))
107 AND prmtr_01 = 'ORG'
108 AND prmtr_02 = p_org_information3_o
109 AND prmtr_04 = p_organization_id
110 AND chg_evt_cd = 'COAPPD';
111
112 CURSOR c_get_bgid IS
113 SELECT business_group_id
114 FROM hr_all_organization_units
115 WHERE organization_id = p_organization_id;
116
117 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
118 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
119 l_cd ben_ext_chg_evt_log.chg_evt_cd%TYPE;
120 l_eff_dt ben_ext_chg_evt_log.chg_eff_dt%TYPE;
121 l_prmtr1 ben_ext_chg_evt_log.prmtr_01%TYPE;
122 l_prmtr2 ben_ext_chg_evt_log.prmtr_02%TYPE;
123 l_prmtr4 ben_ext_chg_evt_log.prmtr_04%TYPE;
124 l_prmtr5 ben_ext_chg_evt_log.prmtr_05%TYPE;
125 l_bgid ben_ext_chg_evt_log.business_group_id%TYPE;
126 l_sub_cat pqp_pension_types_f.pension_sub_category%TYPE;
127 l_enabled varchar2(1) := 'N';
128
129 BEGIN
130
131 -- fetch the bgid for the organization
132 OPEN c_get_bgid;
133 FETCH c_get_bgid INTO l_bgid;
134 CLOSE c_get_bgid;
135
136 --fetch the sub category for the pension type
137 OPEN c_get_subcat(p_org_information3);
138 FETCH c_get_subcat INTO l_sub_cat;
139 CLOSE c_get_subcat;
140
141 --first check if the logging of participation dates is enabled or not
142 OPEN chk_if_enabled('COAPPD');
143 FETCH chk_if_enabled INTO l_enabled;
144 CLOSE chk_if_enabled;
145
146 IF l_enabled = 'Y' THEN
147
148 --Insert a row into the ben_chg_evt_log table for each of these persons
149 --If the Pension Type id is not the same as the old pension type id then
150 --Create a new enrollment record, by inserting a null old value for the
151 --start and end dates.
152 --If the pension type id is the same as the old one,then insert the old and
153 -- new values for the start and end dates if a change has occured
154
155 IF p_org_information3 <> p_org_information3_o THEN
156
157 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
158 (p_ext_chg_evt_log_id => l_log_id
159 ,p_chg_evt_cd => 'COAPPD'
160 ,p_chg_eff_dt => fnd_date.canonical_to_date(p_org_information1)
161 ,p_prmtr_01 => 'ORG'
162 ,p_prmtr_02 => p_org_information3
163 ,p_prmtr_03 => p_org_information6
164 ,p_prmtr_04 => p_organization_id
165 ,p_prmtr_05 => l_sub_cat
166 ,p_person_id => -1
167 ,p_business_group_id => l_bgid
168 ,p_object_version_number => l_ovn
169 ,p_effective_date => fnd_date.canonical_to_date(p_org_information1)
170 ,p_new_val1 => p_org_information1
171 ,p_new_val2 => p_org_information2
172 ,p_old_val1 => null
173 ,p_old_val2 => null
174 );
175
176 l_log_id := null;
177 l_ovn := null;
178
179 --for the old pension type, mark the row in the log table invalid
180 OPEN c_get_chg_evt_log;
181 FETCH c_get_chg_evt_log INTO l_log_id,l_cd,l_eff_dt,l_prmtr1
182 ,l_prmtr2,l_prmtr4,l_prmtr5,l_bgid,l_ovn;
183 CLOSE c_get_chg_evt_log;
184 --now update the previous row to mark the validity as invalid
185 ben_EXT_CHG_EVT_api.update_EXT_CHG_EVT
186 (p_ext_chg_evt_log_id => l_log_id
187 ,p_chg_evt_cd => l_cd
188 ,p_chg_eff_dt => l_eff_dt
189 ,p_prmtr_01 => l_prmtr1
190 ,p_prmtr_02 => l_prmtr2
191 ,p_prmtr_03 => 'N' -- mark this row as invalid
192 ,p_prmtr_04 => l_prmtr4
193 ,p_prmtr_05 => l_prmtr5
194 ,p_person_id => -1
195 ,p_business_group_id => l_bgid
196 ,p_object_version_number => l_ovn
197 ,p_effective_date => fnd_date.canonical_to_date(p_org_information1)
198 );
199
200 ELSIF (p_org_information1 <> p_org_information1_o) THEN
201
202 --fetch the sub category for the pension type
203 OPEN c_get_subcat(p_org_information3_o);
204 FETCH c_get_subcat INTO l_sub_cat;
205 CLOSE c_get_subcat;
206
207 --if start date has been changed,insert a new row effective the new start date
208 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
209 (p_ext_chg_evt_log_id => l_log_id
210 ,p_chg_evt_cd => 'COAPPD'
211 ,p_chg_eff_dt => fnd_date.canonical_to_date(p_org_information1)
212 ,p_prmtr_01 => 'ORG'
213 ,p_prmtr_02 => p_org_information3_o
214 ,p_prmtr_03 => p_org_information6
215 ,p_prmtr_04 => p_organization_id
216 ,p_prmtr_05 => l_sub_cat
217 ,p_person_id => -1
218 ,p_business_group_id => l_bgid
219 ,p_object_version_number => l_ovn
220 ,p_effective_date => fnd_date.canonical_to_date(p_org_information1)
221 ,p_new_val1 => p_org_information1
222 ,p_new_val2 => p_org_information2
223 ,p_old_val1 => p_org_information1_o
224 ,p_old_val2 => p_org_information2_o
225 );
226
227 /* l_log_id := null;
228 l_ovn := null;
229
230 OPEN c_get_chg_evt_log;
231 FETCH c_get_chg_evt_log INTO l_log_id,l_cd,l_eff_dt,l_prmtr1
232 ,l_prmtr2,l_prmtr4,l_bgid,l_ovn;
233 CLOSE c_get_chg_evt_log;
234 --now update the previous row to make it invalid
235 ben_EXT_CHG_EVT_api.update_EXT_CHG_EVT
236 (p_ext_chg_evt_log_id => l_log_id
237 ,p_chg_evt_cd => l_cd
238 ,p_chg_eff_dt => l_eff_dt
239 ,p_prmtr_01 => l_prmtr1
240 ,p_prmtr_02 => l_prmtr2
241 ,p_prmtr_03 => 'N'
242 ,p_prmtr_04 => l_prmtr4
243 ,p_person_id => -1
244 ,p_business_group_id => l_bgid
245 ,p_object_version_number => l_ovn
246 ,p_effective_date => fnd_date.canonical_to_date(p_org_information1)
247 ); */
248
249 ELSIF nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)) <>
250 nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot)) THEN
251
252 --fetch the sub category for the pension type
253 OPEN c_get_subcat(p_org_information3_o);
254 FETCH c_get_subcat INTO l_sub_cat;
255 CLOSE c_get_subcat;
256
257 --if end date has been changed,insert a new row effective the effective date passed
258 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
259 (p_ext_chg_evt_log_id => l_log_id
260 ,p_chg_evt_cd => 'COAPPD'
261 ,p_chg_eff_dt => p_effective_date
262 ,p_prmtr_01 => 'ORG'
263 ,p_prmtr_02 => p_org_information3_o
264 ,p_prmtr_03 => p_org_information6
265 ,p_prmtr_04 => p_organization_id
266 ,p_prmtr_05 => l_sub_cat
267 ,p_person_id => -1
268 ,p_business_group_id => l_bgid
269 ,p_object_version_number => l_ovn
270 ,p_effective_date => p_effective_date
271 ,p_new_val1 => p_org_information1
272 ,p_new_val2 => p_org_information2
273 ,p_old_val1 => p_org_information1_o
274 ,p_old_val2 => p_org_information2_o
275 );
276 /*
277 l_log_id := null;
278 l_ovn := null;
279
280 OPEN c_get_chg_evt_log;
281 FETCH c_get_chg_evt_log INTO l_log_id,l_cd,l_eff_dt,l_prmtr1
282 ,l_prmtr2,l_prmtr4,l_bgid,l_ovn;
283 CLOSE c_get_chg_evt_log;
284 --now update the previous row to make it invalid
285 ben_EXT_CHG_EVT_api.update_EXT_CHG_EVT
286 (p_ext_chg_evt_log_id => l_log_id
287 ,p_chg_evt_cd => l_cd
288 ,p_chg_eff_dt => l_eff_dt
289 ,p_prmtr_01 => l_prmtr1
290 ,p_prmtr_02 => l_prmtr2
291 ,p_prmtr_03 => 'N'
292 ,p_prmtr_04 => l_prmtr4
293 ,p_person_id => -1
294 ,p_business_group_id => l_bgid
295 ,p_object_version_number => l_ovn
296 ,p_effective_date => p_effective_date
297 ); */
298
299 END IF;
300 END IF;
301 END create_org_pt_upd_chg_evt;
302
303
304 --
305 -- ----------------------------------------------------------------------------
306 -- |------------------< create_asg_info_ins_chg_evt >--------------------------|
307 -- ----------------------------------------------------------------------------
308 --
309 PROCEDURE create_asg_info_ins_chg_evt (p_assignment_id IN NUMBER
310 ,p_assignment_extra_info_id IN NUMBER
311 ,p_aei_information1 IN VARCHAR2
312 ,p_aei_information2 IN VARCHAR2
313 ,p_aei_information3 IN VARCHAR2
314 ,p_aei_information4 IN VARCHAR2
315 ,p_effective_date IN DATE
316 ,p_abp_reporting_date IN DATE
317 ) IS
318
319 CURSOR c_get_person_id IS
323 AND p_effective_date BETWEEN effective_start_date
320 SELECT person_id,business_group_id
321 FROM per_all_assignments_f
322 WHERE assignment_id = p_assignment_id
324 AND effective_end_date;
325
326 l_person_id per_all_assignments_f.person_id%TYPE;
327 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
328 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
329 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
330 l_sub_cat pqp_pension_types_f.pension_sub_category%TYPE;
331 l_enabled VARCHAR2(1) := 'N';
332
333 BEGIN
334
335 --Fetch the person id from the assignment id
336 OPEN c_get_person_id;
337 FETCH c_get_person_id INTO l_person_id,l_business_group_id;
338 CLOSE c_get_person_id;
339
340 --find the sub category of the pension type
341 OPEN c_get_subcat(p_aei_information3);
342 FETCH c_get_subcat INTO l_sub_cat;
343 CLOSE c_get_subcat;
344
345 --first check if the logging of participation dates is enabled or not
346 OPEN chk_if_enabled('COAPP');
347 FETCH chk_if_enabled INTO l_enabled;
348 CLOSE chk_if_enabled;
349
350 IF l_enabled = 'Y' THEN
351
352 --Insert a row in the ben_chg_evt_log table
353 ben_ext_chg_evt_api.create_ext_chg_evt
354 (p_validate => true
355 ,p_ext_chg_evt_log_id => l_log_id
356 ,p_chg_evt_cd => 'COAPP'
357 ,p_chg_eff_dt => p_effective_date
358 ,p_prmtr_01 => l_sub_cat -- Sub cat
359 ,p_prmtr_02 => p_aei_information3 --PT id
360 ,p_prmtr_03 => fnd_number.number_to_canonical(p_assignment_extra_info_id)
361 ,p_prmtr_04 => p_aei_information4 -- End Reason
362 ,p_prmtr_05 => NULL
363 ,p_prmtr_06 => NULL
364 ,p_prmtr_07 => NULL
365 ,p_prmtr_08 => NULL
366 ,p_prmtr_09 => fnd_date.date_to_canonical(p_abp_reporting_date)
367 ,p_prmtr_10 => fnd_number.number_to_canonical(p_assignment_id)
368 ,p_person_id => l_person_id
369 ,p_business_group_id => l_business_group_id
370 ,p_object_version_number => l_ovn
371 ,p_effective_date => p_effective_date
372 ,p_new_val1 => p_aei_information1
373 ,p_new_val2 => p_aei_information2
374 ,p_old_val1 => NULL
375 ,p_old_val2 => NULL
376 );
377 END IF;
378
379 END create_asg_info_ins_chg_evt;
380
381 --
382 -- ----------------------------------------------------------------------------
383 -- |----------------------< create_asg_info_upd_chg_evt >----------------------|
384 -- ----------------------------------------------------------------------------
385 --
386 PROCEDURE create_asg_info_upd_chg_evt (p_assignment_id IN NUMBER
387 ,p_assignment_extra_info_id IN NUMBER
388 ,p_aei_information1 IN VARCHAR2
389 ,p_aei_information2 IN VARCHAR2
390 ,p_aei_information3 IN VARCHAR2
391 ,p_aei_information4 IN VARCHAR2
392 ,p_aei_information1_o IN VARCHAR2
393 ,p_aei_information2_o IN VARCHAR2
394 ,p_effective_date IN DATE
395 ,p_abp_reporting_date IN DATE
396 ) IS
397
398 CURSOR c_get_person_id IS
399 SELECT person_id,business_group_id
400 FROM per_all_assignments_f
401 WHERE assignment_id = p_assignment_id
402 AND p_effective_date BETWEEN effective_start_date
403 AND effective_end_date;
404
405 CURSOR c_chk_log_exists (c_person_id IN NUMBER) IS
406 SELECT object_version_number
407 ,ext_chg_evt_log_id
408 ,chg_eff_dt
409 FROM ben_ext_chg_evt_log
410 WHERE person_id = c_person_id
411 AND chg_evt_cd = 'COAPP'
412 AND prmtr_03 = fnd_number.number_to_canonical(p_assignment_extra_info_id);
413
414 l_person_id per_all_assignments_f.person_id%TYPE;
415 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
416 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
417 l_sub_cat pqp_pension_types_f.pension_sub_category%TYPE;
418 l_enabled VARCHAR2(1) := 'N';
419 l_log_xst_ovn NUMBER;
420 l_xst_log_id NUMBER;
421 l_xst_log_eff_dt DATE;
422
423 BEGIN
424
425 OPEN c_get_person_id;
426 FETCH c_get_person_id INTO l_person_id,l_business_group_id;
427 CLOSE c_get_person_id;
428
429 --
430 -- Find the sub category of the pension type
431 --
432 OPEN c_get_subcat(p_aei_information3);
433 FETCH c_get_subcat INTO l_sub_cat;
434 CLOSE c_get_subcat;
435
436 --
437 -- First check if the logging of participation dates is enabled or not
438 --
439 OPEN chk_if_enabled('COAPP');
443 IF l_enabled = 'Y' THEN
440 FETCH chk_if_enabled INTO l_enabled;
441 CLOSE chk_if_enabled;
442
444 --
445 -- Check if an existing log row can be updated with the same information
446 -- Else create a new log row by calling the insert procedure
447 --
448 OPEN c_chk_log_exists(l_person_id);
449 FETCH c_chk_log_exists INTO l_log_xst_ovn,l_xst_log_id,l_xst_log_eff_dt;
450
451 IF c_chk_log_exists%FOUND THEN
452 --
453 -- Delete the existing log and create a new one
454 --
455 ben_ext_chg_evt_api.delete_ext_chg_evt
456 (p_validate => FALSE
457 ,p_ext_chg_evt_log_id => l_xst_log_id
458 ,p_object_version_number => l_log_xst_ovn
459 ,p_effective_date => l_xst_log_eff_dt
460 );
461
462 create_asg_info_ins_chg_evt (p_assignment_id => p_assignment_id
463 ,p_assignment_extra_info_id => p_assignment_extra_info_id
464 ,p_aei_information1 => p_aei_information1
465 ,p_aei_information2 => p_aei_information2
466 ,p_aei_information3 => p_aei_information3
467 ,p_aei_information4 => p_aei_information4
468 ,p_effective_date => p_effective_date
469 ,p_abp_reporting_date => p_abp_reporting_date);
470
471 ELSIF c_chk_log_exists%NOTFOUND THEN
472 --
473 -- Create a new row as we could not find an existing log to update
474 --
475 create_asg_info_ins_chg_evt (p_assignment_id => p_assignment_id
476 ,p_assignment_extra_info_id => p_assignment_extra_info_id
477 ,p_aei_information1 => p_aei_information1
478 ,p_aei_information2 => p_aei_information2
479 ,p_aei_information3 => p_aei_information3
480 ,p_aei_information4 => p_aei_information4
481 ,p_effective_date => p_effective_date
482 ,p_abp_reporting_date => p_abp_reporting_date);
483
484 END IF; -- check existing logs
485 CLOSE c_chk_log_exists;
486
487 END IF; -- check enabled flag
488
489 END create_asg_info_upd_chg_evt;
490
491 --
492 -- ----------------------------------------------------------------------------
493 -- |-------------------< create_org_pp_ins_chg_evt >-------------------------|
494 -- ----------------------------------------------------------------------------
495 --
496 PROCEDURE create_org_pp_ins_chg_evt (p_organization_id number
497 ,p_org_information1 varchar2
498 ,p_org_information2 varchar2
499 ,p_org_information3 varchar2
500 ,p_effective_date date
501 ) IS
502
503 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
504 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
505 l_business_group_id ben_ext_chg_evt_log.business_group_id%TYPE;
506 l_enabled varchar2(1) := 'N';
507
508 CURSOR c_get_bgid IS
509 SELECT business_group_id
510 FROM hr_all_organization_units
511 WHERE organization_id = p_organization_id;
512
513 BEGIN
514
515 --Fetch the business group id for the ORG
516 OPEN c_get_bgid;
517 FETCH c_get_bgid INTO l_business_group_id;
518 CLOSE c_get_bgid;
519
520 --first check if the logging of participation dates is enabled or not
521 OPEN chk_if_enabled('COAEN');
522 FETCH chk_if_enabled INTO l_enabled;
523 CLOSE chk_if_enabled;
524
525 IF l_enabled = 'Y' THEN
526
527 --Insert a row into the ben_chg_evt_log table for the current ORG for a person_id -1
528
529 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
530 (p_ext_chg_evt_log_id => l_log_id
531 ,p_chg_evt_cd => 'COAEN'
532 ,p_chg_eff_dt => p_effective_date
533 ,p_prmtr_01 => p_organization_id
534 ,p_person_id => -1
535 ,p_business_group_id => l_business_group_id
536 ,p_object_version_number => l_ovn
537 ,p_effective_date => p_effective_date
538 ,p_new_val1 => p_org_information2
539 ,p_old_val1 => null
540 );
541 END IF;
542 END create_org_pp_ins_chg_evt;
543
544 --
545 -- ----------------------------------------------------------------------------
546 -- |-------------------< create_org_pp_upd_chg_evt >-------------------------|
547 -- ----------------------------------------------------------------------------
548 --
549 PROCEDURE create_org_pp_upd_chg_evt (p_organization_id number
550 ,p_org_information1 varchar2
551 ,p_org_information2 varchar2
552 ,p_org_information3 varchar2
553 ,p_org_information1_o varchar2
557 ) IS
554 ,p_org_information2_o varchar2
555 ,p_org_information3_o varchar2
556 ,p_effective_date date
558
559 CURSOR c_get_bgid IS
560 SELECT business_group_id
561 FROM hr_all_organization_units
562 WHERE organization_id = p_organization_id;
563
564 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
565 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
566 l_bgid ben_ext_chg_evt_log.business_group_id%TYPE;
567 l_enabled varchar2(1) := 'N';
568
569 BEGIN
570
571 -- fetch the bgid for the organization
572 OPEN c_get_bgid;
573 FETCH c_get_bgid INTO l_bgid;
574 CLOSE c_get_bgid;
575
576 --first check if the logging of participation dates is enabled or not
577 OPEN chk_if_enabled('COAEN');
578 FETCH chk_if_enabled INTO l_enabled;
579 CLOSE chk_if_enabled;
580
581 IF l_enabled = 'Y' THEN
582
583 --insert a row in the log table if the registration number has changed
584 IF p_org_information2 <> p_org_information2_o THEN
585
586 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
587 (p_ext_chg_evt_log_id => l_log_id
588 ,p_chg_evt_cd => 'COAEN'
589 ,p_chg_eff_dt => p_effective_date
590 ,p_prmtr_01 => p_organization_id
591 ,p_person_id => -1
592 ,p_business_group_id => l_bgid
593 ,p_object_version_number => l_ovn
594 ,p_effective_date => p_effective_date
595 ,p_new_val1 => p_org_information2
596 ,p_old_val1 => p_org_information2_o
597 );
598
599 END IF;
600 END IF;
601 END create_org_pp_upd_chg_evt;
602
603 --
604 -- ----------------------------------------------------------------------------
605 -- |------------------< create_si_info_ins_chg_evt >--------------------------|
606 -- ----------------------------------------------------------------------------
607 --
608 PROCEDURE create_si_info_ins_chg_evt (p_assignment_id IN number
609 ,p_aei_information1 IN varchar2
610 ,p_aei_information2 IN varchar2
611 ,p_aei_information3 IN varchar2
612 ,p_effective_date IN date
613 ) IS
614
615 CURSOR c_get_person_id IS
616 SELECT person_id,business_group_id
617 FROM per_all_assignments_f
618 WHERE assignment_id = p_assignment_id
619 AND p_effective_date BETWEEN effective_start_date
620 AND effective_end_date;
621
622 l_person_id per_all_assignments_f.person_id%TYPE;
623 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
624 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
625 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
626 l_enabled varchar2(1) := 'N';
627
628 BEGIN
629
630 --Fetch the person id from the assignment id
631 OPEN c_get_person_id;
632 FETCH c_get_person_id INTO l_person_id,l_business_group_id;
633 CLOSE c_get_person_id;
634
635 --first check if the logging of participation dates is enabled or not
636 OPEN chk_if_enabled('COSIPD');
637 FETCH chk_if_enabled INTO l_enabled;
638 CLOSE chk_if_enabled;
639
640 IF l_enabled = 'Y' THEN
641
642 --Insert a row in the ben_chg_evt_log table
643 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
644 (p_validate => true
645 ,p_ext_chg_evt_log_id => l_log_id
646 ,p_chg_evt_cd => 'COSIPD'
647 ,p_chg_eff_dt => p_effective_date
648 ,p_prmtr_01 => fnd_number.number_to_canonical(p_assignment_id)
649 ,p_person_id => l_person_id
650 ,p_business_group_id => l_business_group_id
651 ,p_object_version_number => l_ovn
652 ,p_effective_date => p_effective_date
653 ,p_new_val1 => p_aei_information1
654 ,p_new_val2 => p_aei_information2
655 ,p_old_val1 => null
656 ,p_old_val2 => null
657 );
658 END IF;
659 END create_si_info_ins_chg_evt;
660
661 --
662 -- ----------------------------------------------------------------------------
663 -- |----------------------< create_si_info_upd_chg_evt >----------------------|
664 -- ----------------------------------------------------------------------------
665 --
666 PROCEDURE create_si_info_upd_chg_evt (p_assignment_id number
667 ,p_aei_information1 varchar2
668 ,p_aei_information2 varchar2
669 ,p_aei_information3 varchar2
670 ,p_aei_information1_o varchar2
671 ,p_aei_information2_o varchar2
675
672 ,p_aei_information3_o varchar2
673 ,p_effective_date date
674 ) IS
676 CURSOR c_get_person_id IS
677 SELECT person_id,business_group_id
678 FROM per_all_assignments_f
679 WHERE assignment_id = p_assignment_id
680 AND p_effective_date BETWEEN effective_start_date
681 AND effective_end_date;
682
683 CURSOR c_get_chg_evt_log(c_person_id IN ben_ext_chg_evt_log.person_id%TYPE) IS
684 SELECT ext_chg_evt_log_id,chg_evt_cd,chg_eff_dt,prmtr_01,
685 prmtr_02,business_group_id,object_version_number
686 FROM ben_ext_chg_evt_log
687 WHERE new_val1 = p_aei_information1_o
688 AND new_val2 = nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))
689 AND prmtr_01 = 'ASG'
690 AND prmtr_02 = p_aei_information3_o
691 AND person_id = c_person_id
692 AND chg_evt_cd = 'COSIPD';
693
694 l_person_id per_all_assignments_f.person_id%TYPE;
695 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
696 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
697 l_cd ben_ext_chg_evt_log.chg_evt_cd%TYPE;
698 l_eff_dt ben_ext_chg_evt_log.chg_eff_dt%TYPE;
699 l_prmtr1 ben_ext_chg_evt_log.prmtr_01%TYPE;
700 l_prmtr2 ben_ext_chg_evt_log.prmtr_02%TYPE;
701 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
702 l_enabled varchar2(1) := 'N';
703
704 BEGIN
705
706 OPEN c_get_person_id;
707 FETCH c_get_person_id INTO l_person_id,l_business_group_id;
708 CLOSE c_get_person_id;
709
710 --first check if the logging of participation dates is enabled or not
711 OPEN chk_if_enabled('COSIPD');
712 FETCH chk_if_enabled INTO l_enabled;
713 CLOSE chk_if_enabled;
714
715 IF l_enabled = 'Y' THEN
716
717 IF (p_aei_information1 <> p_aei_information1_o) THEN
718
719 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
720 (p_ext_chg_evt_log_id => l_log_id
721 ,p_chg_evt_cd => 'COSIPD'
722 ,p_chg_eff_dt => fnd_date.canonical_to_date(p_aei_information1)
723 ,p_prmtr_01 => fnd_number.number_to_canonical(p_assignment_id)
724 ,p_person_id => l_person_id
725 ,p_business_group_id => l_business_group_id
726 ,p_object_version_number => l_ovn
727 ,p_effective_date => fnd_date.canonical_to_date(p_aei_information1)
728 ,p_new_val1 => p_aei_information1
729 ,p_new_val2 => p_aei_information2
730 ,p_old_val1 => p_aei_information1_o
731 ,p_old_val2 => p_aei_information2_o
732 );
733
734 ELSIF fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) <>
735 fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
736
737 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
738 (p_ext_chg_evt_log_id => l_log_id
739 ,p_chg_evt_cd => 'COSIPD'
740 ,p_chg_eff_dt => p_effective_date
741 ,p_prmtr_01 => fnd_number.number_to_canonical(p_assignment_id)
742 ,p_person_id => l_person_id
743 ,p_business_group_id => l_business_group_id
744 ,p_object_version_number => l_ovn
745 ,p_effective_date => p_effective_date
746 ,p_new_val1 => p_aei_information1
747 ,p_new_val2 => p_aei_information2
748 ,p_old_val1 => p_aei_information1_o
749 ,p_old_val2 => p_aei_information2_o
750 );
751
752 END IF;
753 END IF;
754 END create_si_info_upd_chg_evt;
755
756 --
757 -- ----------------------------------------------------------------------------
758 -- |------------------< create_sal_info_ins_chg_evt >--------------------------|
759 -- ----------------------------------------------------------------------------
760 --
761 PROCEDURE create_sal_info_ins_chg_evt (p_assignment_id IN number
762 ,p_assignment_extra_info_id IN NUMBER
763 ,p_aei_information1 IN varchar2
764 ,p_aei_information2 IN varchar2
765 ,p_aei_information4 IN varchar2
766 ,p_aei_information5 IN varchar2
767 ,p_aei_information6 IN varchar2
768 ,p_effective_date IN date
769 ,p_abp_reporting_date IN DATE
770 ) IS
771
772 CURSOR c_get_person_id IS
773 SELECT person_id,business_group_id,payroll_id
774 FROM per_all_assignments_f
778
775 WHERE assignment_id = p_assignment_id
776 AND p_effective_date BETWEEN effective_start_date
777 AND effective_end_date;
779 --cursor to fetch the greatest date earned for the assignment
780 CURSOR c_get_max_date_earned IS
781 SELECT max(ppa.date_earned)
782 FROM pay_assignment_actions paa
783 ,pay_payroll_actions ppa
784 WHERE paa.assignment_id = p_assignment_id
785 AND ppa.payroll_action_id = paa.payroll_action_id;
786
787 --cursor to fetch the minimum start date of the assignment
788 CURSOR c_get_min_asg_start IS
789 SELECT min(effective_start_date)
790 FROM per_all_assignments_f
791 WHERE assignment_id = p_assignment_id;
792
793 --cursor to fetch the start date of the first unprocessed
794 --payroll period, given the max date earned
795 CURSOR c_get_next_start_date(c_date_earned IN DATE
796 ,c_payroll_id IN NUMBER) IS
797 SELECT start_date
798 FROM per_time_periods
799 WHERE payroll_id = c_payroll_id
800 AND start_date > c_date_earned
801 AND rownum = 1;
802
803 --cursor to fetch the start date of the first unprocessed
804 --payroll period, given the asg start date and payroll_id
805 CURSOR c_get_first_start_date(c_start_date IN DATE
806 ,c_payroll_id IN NUMBER) IS
807 SELECT start_date
808 FROM per_time_periods
809 WHERE payroll_id = c_payroll_id
810 AND c_start_date BETWEEN start_date
811 AND end_date;
812
813 l_person_id per_all_assignments_f.person_id%TYPE;
814 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
815 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
816 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
817 l_date Date;
818 l_payroll_id Number;
819 l_eff_date Date;
820 l_enabled varchar2(1) := 'N';
821
822 BEGIN
823
824 --Fetch the person id from the assignment id
825 OPEN c_get_person_id;
826 FETCH c_get_person_id INTO l_person_id,l_business_group_id
827 ,l_payroll_id;
828 CLOSE c_get_person_id;
829
830 --fetch the start date of the next pay period for the assignment
831 --this is the effective date that will be used to log the events
832 --first check if any payroll has already been processed,
833 --if so fetch the max date earned
834
835 OPEN c_get_max_date_earned;
836 FETCH c_get_max_date_earned INTO l_date;
837 IF c_get_max_date_earned%FOUND THEN
838 CLOSE c_get_max_date_earned;
839 OPEN c_get_next_start_date(c_date_earned => l_date
840 ,c_payroll_id => l_payroll_id
841 );
842 FETCH c_get_next_start_date INTO l_eff_date;
843 CLOSE c_get_next_start_date;
844 ELSE
845 CLOSE c_get_max_date_earned;
846 --if no payroll has been processed yet, find the
847 --min asg start date and get the first pay period date
848
849 OPEN c_get_min_asg_start;
850 FETCH c_get_min_asg_start INTO l_date;
851 CLOSE c_get_min_asg_start;
852 OPEN c_get_first_start_date(c_start_date => l_date
853 ,c_payroll_id => l_payroll_id
854 );
855 FETCH c_get_first_start_date INTO l_eff_date;
856 CLOSE c_get_first_start_date;
857 END IF;
858
859 --first check if the logging of pension salary changes
860 OPEN chk_if_enabled('COAPS');
861 FETCH chk_if_enabled INTO l_enabled;
862 CLOSE chk_if_enabled;
863
864 IF l_enabled = 'Y' THEN
865
866 IF p_aei_information6 IS NOT NULL THEN
867
868 --Insert a row in the ben_chg_evt_log table
869 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
870 (p_validate => true
871 ,p_ext_chg_evt_log_id => l_log_id
872 ,p_chg_evt_cd => 'COAPS'
873 ,p_chg_eff_dt => nvl(l_eff_date,
874 fnd_date.canonical_to_date(p_aei_information1))
875 ,p_prmtr_01 => p_assignment_id
876 ,p_prmtr_02 => p_aei_information1
877 ,p_person_id => l_person_id
878 ,p_business_group_id => l_business_group_id
879 ,p_object_version_number => l_ovn
880 ,p_effective_date => nvl(l_eff_date,
881 fnd_date.canonical_to_date(p_aei_information1))
882 ,p_new_val1 => p_aei_information6
883 ,p_old_val1 => null
884 );
885 END IF;
886
887 IF p_aei_information4 IS NOT NULL THEN
888
889 -- Insert a row in the ben_chg_evt_log table
890 -- to indicate that the kind of ptpn has changed.
891 ben_ext_chg_evt_api.create_ext_chg_evt
892 (p_validate => FALSE
893 ,p_ext_chg_evt_log_id => l_log_id
894 ,p_chg_evt_cd => 'COAPKOP'
895 ,p_chg_eff_dt => nvl(l_eff_date,
896 fnd_date.canonical_to_date(p_aei_information1))
897 ,p_prmtr_01 => p_aei_information1
898 ,p_prmtr_02 => p_aei_information2
902 ,p_person_id => l_person_id
899 ,p_prmtr_03 => fnd_number.number_to_canonical(p_assignment_extra_info_id)
900 ,p_prmtr_09 => fnd_date.date_to_canonical(p_abp_reporting_date)
901 ,p_prmtr_10 => p_assignment_id
903 ,p_business_group_id => l_business_group_id
904 ,p_object_version_number => l_ovn
905 ,p_effective_date => nvl(l_eff_date,
906 fnd_date.canonical_to_date(p_aei_information1))
907 ,p_new_val1 => p_aei_information4
908 ,p_old_val1 => NULL
909 ,p_new_val2 => p_aei_information5
910 ,p_old_val2 => NULL
911 );
912 END IF;
913
914 END IF;
915
916 END create_sal_info_ins_chg_evt;
917
918 --
919 -- ----------------------------------------------------------------------------
920 -- |------------------< create_sal_info_upd_chg_evt >--------------------------|
921 -- ----------------------------------------------------------------------------
922 --
923 PROCEDURE create_sal_info_upd_chg_evt (p_assignment_id IN number
924 ,p_assignment_extra_info_id IN NUMBER
925 ,p_aei_information1 IN varchar2
926 ,p_aei_information2 IN varchar2
927 ,p_aei_information4 IN varchar2
928 ,p_aei_information5 IN varchar2
929 ,p_aei_information6 IN varchar2
930 ,p_aei_information1_o IN varchar2
931 ,p_aei_information2_o IN varchar2
932 ,p_aei_information4_o IN varchar2
933 ,p_aei_information5_o IN varchar2
934 ,p_aei_information6_o IN varchar2
935 ,p_effective_date IN date
936 ,p_abp_reporting_date IN DATE
937 ) IS
938
939 CURSOR c_get_person_id IS
940 SELECT person_id,business_group_id,payroll_id
941 FROM per_all_assignments_f
942 WHERE assignment_id = p_assignment_id
943 AND p_effective_date BETWEEN effective_start_date
944 AND effective_end_date;
945
946 --cursor to fetch the greatest date earned for the assignment
947 CURSOR c_get_max_date_earned IS
948 SELECT max(ppa.date_earned)
949 FROM pay_assignment_actions paa
950 ,pay_payroll_actions ppa
951 WHERE paa.assignment_id = p_assignment_id
952 AND ppa.payroll_action_id = paa.payroll_action_id;
953
954 --cursor to fetch the minimum start date of the assignment
955 CURSOR c_get_min_asg_start IS
956 SELECT min(effective_start_date)
957 FROM per_all_assignments_f
958 WHERE assignment_id = p_assignment_id;
959
960 --cursor to fetch the start date of the first unprocessed
961 --payroll period, given the max date earned
962 CURSOR c_get_next_start_date(c_date_earned IN DATE
963 ,c_payroll_id IN NUMBER) IS
964 SELECT start_date
965 FROM per_time_periods
966 WHERE payroll_id = c_payroll_id
967 AND start_date > c_date_earned
968 AND rownum = 1;
969
970 --cursor to fetch the start date of the first unprocessed
971 --payroll period, given the asg start date and payroll_id
972 CURSOR c_get_first_start_date(c_start_date IN DATE
973 ,c_payroll_id IN NUMBER) IS
974 SELECT start_date
975 FROM per_time_periods
976 WHERE payroll_id = c_payroll_id
977 AND c_start_date BETWEEN start_date
978 AND end_date;
979
980 CURSOR c_chk_log_exists (c_person_id IN NUMBER) IS
981 SELECT object_version_number
982 ,ext_chg_evt_log_id
983 ,chg_eff_dt
984 FROM ben_ext_chg_evt_log
985 WHERE person_id = c_person_id
986 AND chg_evt_cd = 'COAPKOP'
987 AND prmtr_03 = fnd_number.number_to_canonical(p_assignment_extra_info_id);
988
989 l_person_id per_all_assignments_f.person_id%TYPE;
990 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
991 l_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
992 l_ovn ben_ext_chg_evt_log.object_version_number%TYPE;
993 l_date Date;
994 l_payroll_id Number;
995 l_eff_date Date;
996 l_enabled varchar2(1) := 'N';
997 l_log_xst_ovn NUMBER;
998 l_xst_log_id NUMBER;
999 l_xst_log_eff_dt DATE;
1000
1001 BEGIN
1002
1003 --Fetch the person id from the assignment id
1004 OPEN c_get_person_id;
1005 FETCH c_get_person_id INTO l_person_id,l_business_group_id
1006 ,l_payroll_id;
1007 CLOSE c_get_person_id;
1008
1009 --fetch the start date of the next pay period for the assignment
1010 --this is the effective date that will be used to log the events
1011 --first check if any payroll has already been processed,
1012 --if so fetch the max date earned
1013
1014 OPEN c_get_max_date_earned;
1015 FETCH c_get_max_date_earned INTO l_date;
1016 IF c_get_max_date_earned%FOUND THEN
1020 );
1017 CLOSE c_get_max_date_earned;
1018 OPEN c_get_next_start_date(c_date_earned => l_date
1019 ,c_payroll_id => l_payroll_id
1021 FETCH c_get_next_start_date INTO l_eff_date;
1022 CLOSE c_get_next_start_date;
1023 ELSE
1024 CLOSE c_get_max_date_earned;
1025 --if no payroll has been processed yet, find the
1026 --min asg start date and get the first pay period date
1027
1028 OPEN c_get_min_asg_start;
1029 FETCH c_get_min_asg_start INTO l_date;
1030 CLOSE c_get_min_asg_start;
1031 OPEN c_get_first_start_date(c_start_date => l_date
1032 ,c_payroll_id => l_payroll_id
1033 );
1034 FETCH c_get_first_start_date INTO l_eff_date;
1035 CLOSE c_get_first_start_date;
1036 END IF;
1037
1038 --first check if the logging of pension salary changes
1039 OPEN chk_if_enabled('COAPS');
1040 FETCH chk_if_enabled INTO l_enabled;
1041 CLOSE chk_if_enabled;
1042
1043 IF l_enabled = 'Y' THEN
1044
1045 IF (fnd_date.canonical_to_date(p_aei_information1) <>
1046 fnd_date.canonical_to_date(p_aei_information1_o))
1047 AND p_aei_information6 IS NOT NULL THEN
1048
1049 --Insert a row in the ben_chg_evt_log table
1050 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
1051 (p_validate => true
1052 ,p_ext_chg_evt_log_id => l_log_id
1053 ,p_chg_evt_cd => 'COAPS'
1054 ,p_chg_eff_dt => nvl(l_eff_date,
1055 fnd_date.canonical_to_date(p_aei_information1))
1056 ,p_prmtr_01 => p_assignment_id
1057 ,p_prmtr_02 => p_aei_information1
1058 ,p_person_id => l_person_id
1059 ,p_business_group_id => l_business_group_id
1060 ,p_object_version_number => l_ovn
1061 ,p_effective_date => nvl(l_eff_date,
1062 fnd_date.canonical_to_date(p_aei_information1))
1063 ,p_new_val1 => p_aei_information6
1064 ,p_old_val1 => null
1065 );
1066
1067 ELSIF (nvl(p_aei_information6,'0') <>
1068 nvl(p_aei_information6_o,'0'))
1069 AND p_aei_information6 IS NOT NULL THEN
1070
1071 --Insert a row in the ben_chg_evt_log table
1072 ben_EXT_CHG_EVT_api.create_EXT_CHG_EVT
1073 (p_validate => true
1074 ,p_ext_chg_evt_log_id => l_log_id
1075 ,p_chg_evt_cd => 'COAPS'
1076 ,p_chg_eff_dt => nvl(l_eff_date,
1077 fnd_date.canonical_to_date(p_aei_information1))
1078 ,p_prmtr_01 => p_assignment_id
1079 ,p_prmtr_02 => p_aei_information1
1080 ,p_person_id => l_person_id
1081 ,p_business_group_id => l_business_group_id
1082 ,p_object_version_number => l_ovn
1083 ,p_effective_date => nvl(l_eff_date,
1084 fnd_date.canonical_to_date(p_aei_information1))
1085 ,p_new_val1 => p_aei_information6
1086 ,p_old_val1 => p_aei_information6_o
1087 );
1088
1089 ELSIF (fnd_date.canonical_to_date(p_aei_information1) <>
1090 fnd_date.canonical_to_date(p_aei_information1_o))
1091 AND p_aei_information4 <> p_aei_information4_o
1092 OR
1093 (fnd_date.canonical_to_date(p_aei_information2) <>
1094 fnd_date.canonical_to_date(p_aei_information2_o))
1095 AND p_aei_information4 <> p_aei_information4_o
1096 OR
1097 (fnd_date.canonical_to_date(p_aei_information1) <>
1098 fnd_date.canonical_to_date(p_aei_information1_o))
1099 AND (fnd_date.canonical_to_date(p_aei_information2) <>
1100 fnd_date.canonical_to_date(p_aei_information2_o))
1101 AND p_aei_information4 <> p_aei_information4_o
1102 OR
1103 p_aei_information4 <> p_aei_information4_o
1104 OR
1105 (fnd_date.canonical_to_date(p_aei_information1) <>
1106 fnd_date.canonical_to_date(p_aei_information1_o))
1107 AND p_aei_information4 = p_aei_information4_o
1108 OR
1109 (fnd_date.canonical_to_date(p_aei_information2) <>
1110 fnd_date.canonical_to_date(p_aei_information2_o))
1111 AND p_aei_information4 = p_aei_information4_o
1112 THEN
1113 --
1114 -- Check if an existing log row can be updated with the same information
1115 -- Else create a new log row by calling the insert procedure
1116 --
1117 OPEN c_chk_log_exists(l_person_id);
1118 FETCH c_chk_log_exists INTO l_log_xst_ovn,l_xst_log_id,l_xst_log_eff_dt;
1119
1120 IF c_chk_log_exists%FOUND THEN
1121 --
1122 -- Delete the existing log and create a new one
1123 --
1124 ben_ext_chg_evt_api.delete_ext_chg_evt
1125 (p_validate => FALSE
1126 ,p_ext_chg_evt_log_id => l_xst_log_id
1127 ,p_object_version_number => l_log_xst_ovn
1128 ,p_effective_date => l_xst_log_eff_dt
1129 );
1130
1131 create_sal_info_ins_chg_evt
1132 (p_assignment_id => p_assignment_id
1133 ,p_assignment_extra_info_id => p_assignment_extra_info_id
1134 ,p_aei_information1 => p_aei_information1
1135 ,p_aei_information2 => p_aei_information2
1136 ,p_aei_information4 => p_aei_information4
1137 ,p_aei_information5 => p_aei_information5
1138 ,p_aei_information6 => p_aei_information6
1139 ,p_effective_date => p_effective_date
1140 ,p_abp_reporting_date => p_abp_reporting_date
1141 );
1142
1143
1144 ELSIF c_chk_log_exists%NOTFOUND THEN
1145 --
1146 -- Create a new row as we could not find an existing log to update
1147 --
1148 create_sal_info_ins_chg_evt
1149 (p_assignment_id => p_assignment_id
1150 ,p_assignment_extra_info_id => p_assignment_extra_info_id
1151 ,p_aei_information1 => p_aei_information1
1152 ,p_aei_information2 => p_aei_information2
1153 ,p_aei_information4 => p_aei_information4
1154 ,p_aei_information5 => p_aei_information5
1155 ,p_aei_information6 => p_aei_information6
1156 ,p_effective_date => p_effective_date
1157 ,p_abp_reporting_date => p_abp_reporting_date
1158 );
1159
1160 END IF; -- check existing logs
1161 CLOSE c_chk_log_exists;
1162
1163 END IF;
1164
1165 END IF;
1166
1167 END create_sal_info_upd_chg_evt;
1168
1169 END pqp_nl_ext_functions;
[Home] [Help]