6 -- Global Variables for Process Parameters
1 PACKAGE BODY pay_gb_edi_p46_car AS
2 /* $Header: pygbp46c.pkb 120.19 2011/12/01 09:34:16 rajganga ship $ */
3
4 g_package CONSTANT VARCHAR2(20):= 'pay_gb_edi_p46_car.';
5
7 g_payroll_id pay_payrolls_f.payroll_id%TYPE;
8 g_start_date DATE;
9 g_end_date DATE;
10 g_business_group_id hr_organization_units.business_group_id%TYPE;
11 g_tax_ref VARCHAR2(20);
12
13 -- Global Variables
14 g_effective_date DATE;
15
16 -- Global Cursors
17 CURSOR c_allocations(p_assignment_id IN NUMBER) IS
18 SELECT vehicle_allocation_id,
19 Vehicle_repository_id,
20 Usage_type,
21 effective_start_date,
22 effective_end_date,
23 private_use_flag -- Added for the bug 10088866
24 FROM pqp_vehicle_allocations_f va
25 WHERE va.assignment_id = p_assignment_id
26 AND ( (va.effective_start_date BETWEEN g_start_date AND g_end_date
27 OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
28 OR (g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
29 AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
30 AND 'C' = (SELECT vr.vehicle_type
31 FROM pqp_vehicle_repository_f vr
32 WHERE vr.vehicle_repository_id =
33 va.vehicle_repository_id
34 AND vr.vehicle_ownership = 'C'
35 AND va.effective_start_date
36 BETWEEN vr.effective_start_date
37 AND vr.effective_end_date)
38 -- AND va.private_use_flag = 'Y' --Added for bug 9215471 -- Commented for 10088866
39 ORDER BY vehicle_allocation_id,
40 effective_start_date;
41 --
42 CURSOR c_first_asg (p_assignment_id IN NUMBER, p_eff_date IN DATE) IS
43 SELECT min(paaf.assignment_id) assignment_id
44 FROM pay_all_payrolls_f papf,
45 hr_soft_coding_keyflex sck,
46 per_all_assignments_f paaf,
47 per_all_assignments_f paaf2,
48 Pqp_vehicle_repository_f vr,
49 Pqp_vehicle_allocations_f va
50 WHERE paaf2.assignment_id = p_assignment_id
51 AND paaf2.person_id = paaf.person_id
52 AND p_eff_date
53 BETWEEN paaf.effective_start_date
54 AND paaf.effective_end_date
55 AND papf.payroll_id = paaf.payroll_id
56 AND p_eff_date
57 BETWEEN papf.effective_start_date
58 AND papf.effective_end_date
59 AND papf.soft_coding_keyflex_id =
60 sck.soft_coding_keyflex_id
61 AND sck.segment1 = g_tax_ref
62 AND paaf.assignment_id = va.assignment_id
63 AND p_eff_date
64 BETWEEN va.effective_start_date
65 AND va.effective_end_date
66 AND va.usage_type = 'P'
67 AND va.vehicle_repository_id =
68 vr.vehicle_repository_id
69 AND p_eff_date
70 BETWEEN vr.effective_start_date
71 AND vr.effective_end_date
72 AND vr.vehicle_ownership = 'C'
73 AND vr.vehicle_type = 'C';
74 --
75 first_asg_rec c_first_asg%ROWTYPE;
76 --
77 CURSOR c_alloc_dates(p_vehicle_allocation_id NUMBER) IS
78 SELECT min(effective_start_Date) min_start_date,
79 max(effective_end_date) max_end_Date
80 FROM pqp_vehicle_allocations_f va
81 WHERE va.vehicle_allocation_id = p_vehicle_allocation_id;
82 --
83 alc_dates_rec c_alloc_dates%ROWTYPE;
84 --
85 CURSOR c_primary_car(p_assignment_id IN NUMBER, p_eff_date IN DATE) IS
86 SELECT va.vehicle_allocation_id,
87 vr.vehicle_repository_id
88 FROM Pqp_vehicle_repository_f vr,
89 Pqp_vehicle_allocations_f va
90 WHERE va.assignment_id = p_assignment_id
91 AND p_eff_date
92 BETWEEN va.effective_start_date
93 AND va.effective_end_date
94 AND va.usage_type = 'P'
95 AND va.vehicle_repository_id =
96 vr.vehicle_repository_id
97 AND p_eff_date
98 BETWEEN vr.effective_start_date
99 AND vr.effective_end_date
100 AND vr.vehicle_ownership = 'C'
101 AND vr.vehicle_type = 'C';
102 --
103 primary_car_rec c_primary_car%ROWTYPE;
104 --
105 CURSOR c_prior_prim_car(p_assignment_id IN NUMBER,
106 P_new_car_start_date IN DATE)
107 IS
108 SELECT va.vehicle_allocation_id,
109 Va.vehicle_repository_id,
110 vr.make,
111 vr.model,
112 vr.engine_capacity_in_cc,
113 va.effective_end_date
114 FROM Pqp_vehicle_allocations_f va,
115 pqp_vehicle_repository_f vr
116 WHERE va.assignment_id = p_assignment_id
117 AND va.effective_end_date
118 BETWEEN (p_new_car_start_date - 30)
119 AND (p_new_car_start_date - 1)
120 AND va.usage_type = 'P'
121 AND va.effective_end_date =
122 (SELECT max(va2.effective_end_date)
123 FROM Pqp_vehicle_allocations_f va2
124 WHERE va2.assignment_id = p_assignment_id
125 AND va2.effective_end_date
126 BETWEEN (p_new_car_start_date - 30)
127 AND (p_new_car_start_date - 1)
128 AND va2.usage_type = 'P')
129 AND va.vehicle_repository_id = vr.vehicle_repository_id
130 AND vr.vehicle_ownership = 'C'
131 AND vr.vehicle_type = 'C'
132 AND va.effective_end_date
133 BETWEEN vr.effective_start_date
134 AND vr.effective_end_Date;
135 --
136 prior_prim_car_rec c_prior_prim_car%ROWTYPE;
137 --
138 CURSOR c_next_prim_car(p_assignment_id IN NUMBER,
139 P_withdrawn_car_end_date IN DATE)
140 IS
141 SELECT va.vehicle_allocation_id,
142 Va.vehicle_repository_id,
143 vr.make,
144 vr.model,
145 vr.engine_capacity_in_cc,
146 va.effective_end_date
147 FROM Pqp_vehicle_allocations_f va,
148 pqp_vehicle_repository_f vr
149 WHERE va.assignment_id = p_assignment_id
150 AND va.effective_start_date
151 BETWEEN (p_withdrawn_car_end_date + 1)
152 AND least((p_withdrawn_car_end_date + 30), g_end_date)
153 AND va.usage_type = 'P'
154 AND va.effective_start_date =
155 (SELECT min(va2.effective_start_date)
156 FROM Pqp_vehicle_allocations_f va2
157 WHERE va2.assignment_id = p_assignment_id
158 AND va2.effective_start_date
159 BETWEEN (p_withdrawn_car_end_date + 1)
160 AND least((p_withdrawn_car_end_date + 30), g_end_date)
161 AND va2.usage_type = 'P')
162 AND va.vehicle_repository_id = vr.vehicle_repository_id
163 AND vr.vehicle_ownership = 'C'
164 AND vr.vehicle_type = 'C'
165 AND va.effective_end_date
166 BETWEEN vr.effective_start_date
167 AND vr.effective_end_Date;
168 --
169 next_prim_car_rec c_next_prim_car%ROWTYPE;
170 --
171 -- Created for the Bug : 11075296. Fuel type changes for EOY 2011/12
172 CURSOR c_vehicle_changes_v4 (
173 p_vehicle_repository_id IN NUMBER) IS
174 SELECT effective_start_date,
175 effective_end_date,
176 h1.description fuel_type
177 FROM pqp_vehicle_repository_f vr1,
178 hr_lookups h1
179 WHERE vr1.vehicle_repository_id = p_vehicle_repository_id
180 AND vr1.effective_start_date BETWEEN g_start_date AND g_end_date
181 AND vr1.fuel_type = h1.lookup_code
182 AND h1.lookup_type = 'PQP_FUEL_TYPE'
183 AND h1.enabled_flag = 'Y'
184 AND trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1))
185 AND EXISTS (SELECT 1
186 FROM pqp_vehicle_repository_f vr2,
187 hr_lookups h2
188 WHERE vr2.vehicle_repository_id =
189 p_vehicle_repository_id
190 AND vr2.effective_end_date =
191 vr1.effective_start_date-1
192 AND vr2.fuel_type = h2.lookup_code
193 AND h2.lookup_type = 'PQP_FUEL_TYPE'
194 AND h2.enabled_flag = 'Y'
195 AND trunc(sysdate) BETWEEN trunc(nvl(h2.start_date_active, sysdate-1)) AND trunc(nvl(h2.end_date_active,sysdate+1))
196 AND decode(h2.description,'B','A'
197 ,'C','A'
198 ,'D','D'
199 ,'L','D'
200 ,'E','E'
201 ,'G','A'
202 ,'H','A'
203 ,'P','A') <> decode(h1.description,'B','A'
204 ,'C','A'
205 ,'D','D'
206 ,'L','D'
207 ,'E','E'
208 ,'G','A'
209 ,'H','A'
210 ,'P','A')
211 )
212 --AND vr2.fuel_type <> vr1.fuel_type)
213 ORDER BY vr1.effective_start_date;
214 --
215 CURSOR c_vehicle_changes(
216 p_vehicle_repository_id IN NUMBER) IS
217 SELECT effective_start_date,
218 effective_end_date,
219 h1.description fuel_type
220 FROM pqp_vehicle_repository_f vr1,
221 hr_lookups h1
222 WHERE vr1.vehicle_repository_id = p_vehicle_repository_id
223 AND vr1.effective_start_date BETWEEN g_start_date AND g_end_date
224 AND vr1.fuel_type = h1.lookup_code
225 AND h1.lookup_type = 'PQP_FUEL_TYPE'
226 AND h1.enabled_flag = 'Y'
227 AND trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1))
228 AND EXISTS (SELECT 1
229 FROM pqp_vehicle_repository_f vr2
230 WHERE vr2.vehicle_repository_id =
231 p_vehicle_repository_id
232 AND vr2.effective_end_date =
233 vr1.effective_start_date-1
234 AND vr2.fuel_type <> vr1.fuel_type)
235 ORDER BY vr1.effective_start_date;
236 --
237 CURSOR c_veh_details(p_assignment_id IN NUMBER,
238 p_eff_date IN DATE) IS
239 SELECT va.vehicle_allocation_id,
240 va.vehicle_repository_id,
241 va.usage_type usage_type,
242 va.effective_start_date,
243 va.effective_end_date
244 FROM Pqp_vehicle_repository_f vr,
245 Pqp_vehicle_allocations_f va
246 WHERE va.assignment_id = p_assignment_id
247 AND p_eff_date BETWEEN va.effective_start_date AND va.effective_end_date
248 AND va.vehicle_repository_id = vr.vehicle_repository_id
249 AND p_eff_date BETWEEN vr.effective_start_date AND vr.effective_end_date
250 AND vr.vehicle_ownership = 'C'
251 AND vr.vehicle_type = 'C';
252 --
253 CURSOR c_tax_ref(p_assignment_id IN NUMBER,
254 p_eff_date IN DATE) IS
255 SELECT flex.segment1 tax_ref
256 FROM hr_soft_coding_keyflex flex,
257 per_assignments_f asg,
258 Pay_payrolls_f ppf
259 WHERE asg.assignment_id = p_assignment_id
260 AND p_eff_date BETWEEN asg.effective_start_date AND asg.effective_end_date
261 AND asg.payroll_id = ppf.payroll_id
262 AND p_eff_date BETWEEN ppf.effective_start_Date and ppf.effective_end_date
263 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id;
264 --
265 l_previous_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
266 l_next_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
267 --
268 --
269 -- Check if the private use flag is changed from Y to NULL or N.
270 -- in this case it is considered as car withdrawal.
271 -- if a car was allocated in the previous financial year and it was withdrawn
272 -- in the current financial year, it has to be reported. If in the current financial
273 -- year the person is already reported, after this if a withdrawal happens, the assignment
274 -- has to be re-reported
275
276 cursor private_flag_check(p_vehicle_aloc_id in number) is
277 SELECT max(va.effective_start_Date)
278 FROM pqp_vehicle_allocations_f va
279 WHERE va.vehicle_allocation_id = p_vehicle_aloc_id
280 and exists (select 1
281 from pqp_vehicle_allocations_f previous_va
282 where previous_va.vehicle_allocation_id = va.vehicle_allocation_id
283 and previous_va.effective_end_date+1 = va.effective_start_Date
284 and nvl(previous_va.PRIVATE_USE_FLAG,'N') <> va.private_use_flag)
285 and ((va.effective_start_date BETWEEN g_start_date AND g_end_date
286 OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
287 OR (g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
288 AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) );
289
290
291 ---
292
293 cursor private_flag_arch(p_vehicle_aloc_id in number) is
294 SELECT va.effective_start_Date,va.effective_end_Date,va.private_use_flag
295 FROM pqp_vehicle_allocations_f va
296 WHERE va.vehicle_allocation_id = p_vehicle_aloc_id
297 and exists (select 1
298 from pqp_vehicle_allocations_f previous_va
299 where previous_va.vehicle_allocation_id = va.vehicle_allocation_id
300 and previous_va.effective_end_date+1 = va.effective_start_Date
301 and nvl(previous_va.PRIVATE_USE_FLAG,'N') <> va.private_use_flag)
302 and ((va.effective_start_date BETWEEN g_start_date AND g_end_date
303 OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
304 OR (g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
305 AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) );
306
307
308 ---
309 --- Cursor to check if a new benenit or withdrawal happened
310
311 cursor withdrawal_check(p_vehicle_aloc_id in number, p_start_date in date)
312 is
313 select va.private_use_flag
314 from pqp_vehicle_allocations_f va
315 where va.vehicle_allocation_id = p_vehicle_aloc_id
316 and p_start_date BETWEEN va.effective_start_date AND va.effective_end_date;
317
318 -----------------------------------------------------
319 -- FUNCTION CHK_ACTION_ARCHIVED --
320 -----------------------------------------------------
321 FUNCTION chk_action_archived(
322 p_assignment_id IN NUMBER,
323 p_vehicle_allocation_id IN NUMBER,
324 p_allocation_start_date IN DATE,
325 p_action_flag IN VARCHAR2)
326 RETURN VARCHAR2 IS
327 --
328 CURSOR c_chk_archive IS
329 SELECT 'Y' found_flag
330 FROM pay_action_information pai,
331 pay_assignment_actions act,
332 pay_payroll_actions ppa
333 WHERE ppa.report_type = 'P46_CAR_EDI'
334 AND ppa.report_qualifier='GB'
335 AND ppa.report_category ='EDI'
336 AND ppa.action_type = 'X'
337 AND g_business_group_id = ppa.business_group_id
341 AND 'AAP' = pai.action_context_type
338 AND ppa.payroll_action_id = act.payroll_action_id
339 AND p_assignment_id = act.assignment_id
340 AND act.assignment_action_id = pai.action_context_id
342 AND 'GB P46 CAR EDI ALLOCATION' = pai.action_information_category
343 AND p_action_flag = pai.action_information1
344 AND to_char(p_vehicle_allocation_id) = pai.action_information2
345 AND fnd_date.date_to_canonical(p_allocation_start_date) = pai.action_information3;
346 --
347 chk_archive_rec c_chk_archive%ROWTYPE;
348 --
349 l_archived_flag VARCHAR2(1) := 'Y';
350 l_proc VARCHAR2(50) := g_package||'CHK_ACTION_ARCHIVED';
351 BEGIN
352 hr_utility.set_location(l_proc, 10);
353 hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
354 hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
355 hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
356 hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
357 --
358 OPEN c_chk_archive;
359 FETCH c_chk_archive INTO chk_archive_rec;
360 IF c_chk_archive%NOTFOUND THEN
361 hr_utility.set_location(l_proc, 20);
362 l_archived_flag := 'N';
363 END IF;
364 CLOSE c_chk_archive;
365 --
366 hr_utility.set_location(l_proc, 30);
367 RETURN l_archived_flag;
368 END chk_action_archived;
369
370 --For Bug 6652235
371 -----------------------------------------------------
372 -- FUNCTION CHK_ACTION_ARCHIVED_V2 --
373 -----------------------------------------------------
374 FUNCTION chk_action_archived_v2(
375 p_assignment_id IN NUMBER,
376 p_vehicle_allocation_id IN NUMBER,
377 p_allocation_start_date IN DATE,
378 p_action_flag IN VARCHAR2,
379 p_orig_allocation_start_date IN DATE default null) -- Added for the bug 10088866
380 RETURN VARCHAR2 IS
381 --
382 CURSOR c_chk_archive IS
383 SELECT 'Y' found_flag
384 FROM pay_action_information pai,
385 pay_assignment_actions act,
386 pay_payroll_actions ppa
387 WHERE ppa.report_type IN ('P46_CAR_EDI_V2','P46_CAR_EDI','P46_CAR_EDI_V3','P46_CAR_EDI_V4','P46_CAR_EDI_V5') --Bug 10095492: Added V4 --Bug 13400872: Added V5
388 AND ppa.report_qualifier='GB'
389 AND ppa.report_category ='EDI'
390 AND ppa.action_type = 'X'
391 AND g_business_group_id = ppa.business_group_id
392 AND ppa.payroll_action_id = act.payroll_action_id
393 AND p_assignment_id = act.assignment_id
394 AND act.assignment_action_id = pai.action_context_id
395 AND 'AAP' = pai.action_context_type
396 AND 'GB P46 CAR EDI ALLOCATION' = pai.action_information_category
397 AND p_action_flag = pai.action_information1
398 AND to_char(p_vehicle_allocation_id) = pai.action_information2
399 AND (fnd_date.date_to_canonical(p_allocation_start_date) = pai.action_information3
400 OR fnd_date.date_to_canonical(p_orig_allocation_start_date) = pai.action_information3); -- Modified for the bug 10088866
401 --
402 chk_archive_rec c_chk_archive%ROWTYPE;
403 --
404 l_archived_flag VARCHAR2(1) := 'Y';
405 l_proc VARCHAR2(50) := g_package||'CHK_ACTION_ARCHIVED_V2';
406 BEGIN
407 hr_utility.set_location(l_proc, 10);
408 hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
409 hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
410 hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
411 hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
412 --
413 OPEN c_chk_archive;
414 FETCH c_chk_archive INTO chk_archive_rec;
415 IF c_chk_archive%NOTFOUND THEN
416 hr_utility.set_location(l_proc, 20);
417 l_archived_flag := 'N';
418 END IF;
419 CLOSE c_chk_archive;
420 --
421 hr_utility.set_location(l_proc, 30);
422 RETURN l_archived_flag;
423 END chk_action_archived_v2;
424
425 -----------------------------------------------------
426 -- RANGE_CURSOR --
427 -----------------------------------------------------
428 PROCEDURE range_cursor (pactid IN NUMBER,
429 sqlstr out nocopy varchar2) IS
430 --
431 CURSOR c_employer IS
432 SELECT
433 substr(org.org_information3,1,36) employer_name,
434 substr(org.org_information4,1,60) employer_address_line,
435 substr(org.org_information2 ,1,40) tax_district_name,
436 organization_id,
437 ppa.effective_date
438 FROM
439 pay_payroll_actions ppa,
440 hr_organization_information org
441 WHERE ppa.payroll_action_id = pactid
442 AND org.org_information_context = 'Tax Details References'
443 AND NVL(org.org_information10,'UK') = 'UK'
444 AND org.organization_id = ppa.business_group_id
445 AND substr(ppa.legislative_parameters,
446 instr(ppa.legislative_parameters,'TAX_REF=') + 8,
447 instr(ppa.legislative_parameters||' ',' ',
448 instr(ppa.legislative_parameters,'TAX_REF=')+8)
449 - instr(ppa.legislative_parameters,'TAX_REF=') - 8)
450 = org.org_information1
451 AND ppa.report_type = 'P46_CAR_EDI'
452 AND report_qualifier = 'GB'
453 AND ppa.report_category = 'EDI';
454 --
455 employer_rec c_employer%ROWTYPE;
456 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
460 BEGIN
457 l_action_info_id pay_action_information.action_information_id%TYPE;
458 l_ovn pay_action_information.object_version_number%TYPE;
459 --
461 --
462 -- hr_utility.trace_on(null, 'RMAKHIJA');
463 hr_utility.set_location('Enering: '||l_proc, 10);
464 hr_utility.trace(l_proc||': payroll_action_id='||pactid);
465 --
466 -- Get Employer information
467 OPEN c_employer;
468 FETCH c_employer INTO employer_rec;
469 IF c_employer%NOTFOUND THEN
470 hr_utility.set_location(l_proc, 20);
471 raise NO_DATA_FOUND;
472 END IF;
473 CLOSE c_employer;
474 --
475 g_effective_date := employer_rec.effective_Date;
476 --
477 hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
478 hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
479 hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
480 hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
481 hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
482 --
483 pay_action_information_api.create_action_information (
484 p_action_information_id => l_action_info_id
485 , p_action_context_id => pactid
486 , p_action_context_type => 'PA'
487 , p_object_version_number => l_ovn
488 , p_assignment_id => NULL
489 , p_effective_date => g_effective_date
490 , p_source_id => NULL
491 , p_source_text => NULL
492 , p_action_information_category => 'GB P46 CAR EDI EMPLOYER DETAIL'
493 , p_action_information1 => upper(employer_rec.organization_id)
494 , p_action_information2 => upper(employer_rec.employer_name)
495 , p_action_information3 => upper(employer_rec.tax_district_name)
496 , p_action_information4 => upper(employer_rec.employer_address_line));
497 --
498 hr_utility.set_location(l_proc, 30);
499 --
500 sqlstr := 'select distinct person_id '||
501 'from per_people_f ppf, '||
502 'pay_payroll_actions ppa '||
503 'where ppa.payroll_action_id = :payroll_action_id '||
504 'and ppa.business_group_id = ppf.business_group_id '||
505 'order by ppf.person_id';
506
507 --
508 hr_utility.set_location('Leaving: '||l_proc, 100);
509 --
510 -- hr_utility.trace_off;
511 EXCEPTION
512 WHEN OTHERS THEN
513 -- Return cursor that selects no rows
514 sqlstr := 'select 1 '||
515 '/* ERROR - Employer Details Fetch failed with: '||
516 sqlerrm(sqlcode)||' */ '||
517 'from dual where to_char(:payroll_action_id) = dummy';
518 hr_utility.set_location(' Leaving: '||l_proc,110);
519 END range_cursor;
520
521 --For Bug 8986543
522 -----------------------------------------------------
523 -- RANGE_CODE_V3 --
524 -----------------------------------------------------
525 PROCEDURE range_code_v3 (pactid IN NUMBER,
526 sqlstr out nocopy varchar2) IS
527 --
528 CURSOR c_employer IS
529 SELECT
530 substr(org.org_information3,1,36) employer_name,
531 substr(org.org_information4,1,60) employer_address_line,
532 substr(org.org_information2 ,1,40) tax_district_name,
533 organization_id,
534 ppa.effective_date
535 FROM
536 pay_payroll_actions ppa,
537 hr_organization_information org
538 WHERE ppa.payroll_action_id = pactid
539 AND org.org_information_context = 'Tax Details References'
540 AND NVL(org.org_information10,'UK') = 'UK'
541 AND org.organization_id = ppa.business_group_id
542 AND substr(ppa.legislative_parameters,
546 - instr(ppa.legislative_parameters,'TAX_REF=') - 8)
543 instr(ppa.legislative_parameters,'TAX_REF=') + 8,
544 instr(ppa.legislative_parameters||' ',' ',
545 instr(ppa.legislative_parameters,'TAX_REF=')+8)
547 = org.org_information1
548 AND ppa.report_type = 'P46_CAR_EDI_V3'
549 AND report_qualifier = 'GB'
550 AND ppa.report_category = 'EDI';
551 --
552 employer_rec c_employer%ROWTYPE;
553 l_proc CONSTANT VARCHAR2(100):= g_package||'range_code_v3';
554 l_action_info_id pay_action_information.action_information_id%TYPE;
555 l_ovn pay_action_information.object_version_number%TYPE;
556 --
557 BEGIN
558 --
559 hr_utility.set_location('Enering: '||l_proc, 10);
560 hr_utility.trace(l_proc||': payroll_action_id='||pactid);
561 --
562 -- Get Employer information
563 OPEN c_employer;
564 FETCH c_employer INTO employer_rec;
565 IF c_employer%NOTFOUND THEN
566 hr_utility.set_location(l_proc, 20);
567 raise NO_DATA_FOUND;
568 END IF;
569 CLOSE c_employer;
570 --
571 g_effective_date := employer_rec.effective_Date;
572 --
573 hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
574 hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
575 hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
576 hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
577 hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
578 --
579 pay_action_information_api.create_action_information (
580 p_action_information_id => l_action_info_id
581 , p_action_context_id => pactid
582 , p_action_context_type => 'PA'
583 , p_object_version_number => l_ovn
584 , p_assignment_id => NULL
585 , p_effective_date => g_effective_date
586 , p_source_id => NULL
587 , p_source_text => NULL
588 , p_action_information_category => 'GB P46 CAR EDI EMPLOYER DETAIL'
589 , p_action_information1 => upper(employer_rec.organization_id)
590 , p_action_information2 => upper(employer_rec.employer_name)
591 , p_action_information3 => upper(employer_rec.tax_district_name)
592 , p_action_information4 => upper(employer_rec.employer_address_line));
593 --
594 hr_utility.set_location(l_proc, 30);
595 --
596 sqlstr := 'select distinct person_id '||
597 'from per_people_f ppf, '||
598 'pay_payroll_actions ppa '||
599 'where ppa.payroll_action_id = :payroll_action_id '||
600 'and ppa.business_group_id = ppf.business_group_id '||
601 'order by ppf.person_id';
602
603 --
604 hr_utility.set_location('Leaving: '||l_proc, 100);
605 --
606 -- hr_utility.trace_off;
607 EXCEPTION
608 WHEN OTHERS THEN
609 -- Return cursor that selects no rows
610 sqlstr := 'select 1 '||
611 '/* ERROR - Employer Details Fetch failed with: '||
612 sqlerrm(sqlcode)||' */ '||
613 'from dual where to_char(:payroll_action_id) = dummy';
614 hr_utility.set_location(' Leaving: '||l_proc,110);
615 END range_code_v3;
616
617
618 --For Bug 10095492
619 -----------------------------------------------------
620 -- RANGE_CODE_V4 --
621 -----------------------------------------------------
622 PROCEDURE range_code_v4 (pactid IN NUMBER,
623 sqlstr out nocopy varchar2) IS
624 --
625 CURSOR c_employer IS
626 SELECT
627 substr(org.org_information3,1,36) employer_name,
628 substr(org.org_information4,1,60) employer_address_line,
629 substr(org.org_information2 ,1,40) tax_district_name,
630 organization_id,
631 ppa.effective_date
632 FROM
633 pay_payroll_actions ppa,
634 hr_organization_information org
635 WHERE ppa.payroll_action_id = pactid
636 AND org.org_information_context = 'Tax Details References'
637 AND NVL(org.org_information10,'UK') = 'UK'
638 AND org.organization_id = ppa.business_group_id
639 AND substr(ppa.legislative_parameters,
640 instr(ppa.legislative_parameters,'TAX_REF=') + 8,
641 instr(ppa.legislative_parameters||' ',' ',
642 instr(ppa.legislative_parameters,'TAX_REF=')+8)
643 - instr(ppa.legislative_parameters,'TAX_REF=') - 8)
644 = org.org_information1
645 AND ppa.report_type = 'P46_CAR_EDI_V4'
646 AND report_qualifier = 'GB'
647 AND ppa.report_category = 'EDI';
648 --
649 employer_rec c_employer%ROWTYPE;
650 l_proc CONSTANT VARCHAR2(100):= g_package||'range_code_v4';
651 l_action_info_id pay_action_information.action_information_id%TYPE;
652 l_ovn pay_action_information.object_version_number%TYPE;
653 --
654 BEGIN
655 --
656 hr_utility.set_location('Enering: '||l_proc, 10);
657 hr_utility.trace(l_proc||': payroll_action_id='||pactid);
658 --
659 -- Get Employer information
660 OPEN c_employer;
661 FETCH c_employer INTO employer_rec;
662 IF c_employer%NOTFOUND THEN
663 hr_utility.set_location(l_proc, 20);
664 raise NO_DATA_FOUND;
665 END IF;
669 --
666 CLOSE c_employer;
667 --
668 g_effective_date := employer_rec.effective_Date;
670 hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
671 hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
672 hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
673 hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
674 hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
675 --
676 pay_action_information_api.create_action_information (
677 p_action_information_id => l_action_info_id
678 , p_action_context_id => pactid
679 , p_action_context_type => 'PA'
680 , p_object_version_number => l_ovn
681 , p_assignment_id => NULL
682 , p_effective_date => g_effective_date
683 , p_source_id => NULL
684 , p_source_text => NULL
685 , p_action_information_category => 'GB P46 CAR EDI EMPLOYER DETAIL'
686 , p_action_information1 => upper(employer_rec.organization_id)
687 , p_action_information2 => upper(employer_rec.employer_name)
688 , p_action_information3 => upper(employer_rec.tax_district_name)
689 , p_action_information4 => upper(employer_rec.employer_address_line));
690 --
691 hr_utility.set_location(l_proc, 30);
692 --
693 sqlstr := 'select distinct person_id '||
694 'from per_people_f ppf, '||
695 'pay_payroll_actions ppa '||
696 'where ppa.payroll_action_id = :payroll_action_id '||
697 'and ppa.business_group_id = ppf.business_group_id '||
698 'order by ppf.person_id';
699
700 --
701 hr_utility.set_location('Leaving: '||l_proc, 100);
702 --
703 -- hr_utility.trace_off;
704 EXCEPTION
705 WHEN OTHERS THEN
706 -- Return cursor that selects no rows
707 sqlstr := 'select 1 '||
708 '/* ERROR - Employer Details Fetch failed with: '||
709 sqlerrm(sqlcode)||' */ '||
710 'from dual where to_char(:payroll_action_id) = dummy';
711 hr_utility.set_location(' Leaving: '||l_proc,110);
712 END range_code_v4;
713
714 --For Bug 13400872
715 -----------------------------------------------------
716 -- RANGE_CODE_V5 --
717 -----------------------------------------------------
718 PROCEDURE range_code_v5 (pactid IN NUMBER,
719 sqlstr out nocopy varchar2) IS
720 --
721 CURSOR c_employer IS
722 SELECT
723 substr(org.org_information3,1,36) employer_name,
724 substr(org.org_information4,1,60) employer_address_line,
725 substr(org.org_information2 ,1,40) tax_district_name,
726 organization_id,
727 ppa.effective_date
728 FROM
729 pay_payroll_actions ppa,
730 hr_organization_information org
731 WHERE ppa.payroll_action_id = pactid
732 AND org.org_information_context = 'Tax Details References'
733 AND NVL(org.org_information10,'UK') = 'UK'
734 AND org.organization_id = ppa.business_group_id
735 AND substr(ppa.legislative_parameters,
736 instr(ppa.legislative_parameters,'TAX_REF=') + 8,
737 instr(ppa.legislative_parameters||' ',' ',
738 instr(ppa.legislative_parameters,'TAX_REF=')+8)
739 - instr(ppa.legislative_parameters,'TAX_REF=') - 8)
740 = org.org_information1
741 AND ppa.report_type = 'P46_CAR_EDI_V5'
742 AND report_qualifier = 'GB'
743 AND ppa.report_category = 'EDI';
744 --
745 employer_rec c_employer%ROWTYPE;
746 l_proc CONSTANT VARCHAR2(100):= g_package||'range_code_v5';
747 l_action_info_id pay_action_information.action_information_id%TYPE;
748 l_ovn pay_action_information.object_version_number%TYPE;
749 --
750 BEGIN
751 --
752 hr_utility.set_location('Enering: '||l_proc, 10);
753 hr_utility.trace(l_proc||': payroll_action_id='||pactid);
754 --
755 -- Get Employer information
756 OPEN c_employer;
757 FETCH c_employer INTO employer_rec;
758 IF c_employer%NOTFOUND THEN
759 hr_utility.set_location(l_proc, 20);
760 raise NO_DATA_FOUND;
761 END IF;
762 CLOSE c_employer;
763 --
764 g_effective_date := employer_rec.effective_Date;
765 --
766 hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
767 hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
768 hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
769 hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
770 hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
771 --
772 pay_action_information_api.create_action_information (
773 p_action_information_id => l_action_info_id
774 , p_action_context_id => pactid
775 , p_action_context_type => 'PA'
776 , p_object_version_number => l_ovn
777 , p_assignment_id => NULL
778 , p_effective_date => g_effective_date
779 , p_source_id => NULL
780 , p_source_text => NULL
781 , p_action_information_category => 'GB P46 CAR EDI EMPLOYER DETAIL'
785 , p_action_information4 => upper(employer_rec.employer_address_line));
782 , p_action_information1 => upper(employer_rec.organization_id)
783 , p_action_information2 => upper(employer_rec.employer_name)
784 , p_action_information3 => upper(employer_rec.tax_district_name)
786 --
787 hr_utility.set_location(l_proc, 30);
788 --
789 sqlstr := 'select distinct person_id '||
790 'from per_people_f ppf, '||
791 'pay_payroll_actions ppa '||
792 'where ppa.payroll_action_id = :payroll_action_id '||
793 'and ppa.business_group_id = ppf.business_group_id '||
794 'order by ppf.person_id';
795
796 --
797 hr_utility.set_location('Leaving: '||l_proc, 100);
798 --
799 -- hr_utility.trace_off;
800 EXCEPTION
801 WHEN OTHERS THEN
802 -- Return cursor that selects no rows
803 sqlstr := 'select 1 '||
804 '/* ERROR - Employer Details Fetch failed with: '||
805 sqlerrm(sqlcode)||' */ '||
806 'from dual where to_char(:payroll_action_id) = dummy';
807 hr_utility.set_location(' Leaving: '||l_proc,110);
808 END range_code_v5;
809
810
811 --For Bug 6652235
812 -----------------------------------------------------
813 -- PROCEDURE CREATE_ASG_ACT_V2 --
814 -----------------------------------------------------
815 PROCEDURE create_asg_act_v2(pactid IN NUMBER,
816 stperson IN NUMBER,
817 endperson IN NUMBER,
818 chunk IN NUMBER) IS
819 --
820 l_proc CONSTANT VARCHAR2(35):= g_package||'create_asg_act';
821 l_actid pay_assignment_actions.assignment_action_id%TYPE;
822 l_ovn pay_action_information.object_version_number%TYPE;
823 --
824 --
825 CURSOR c_param_values IS
826 SELECT to_number( pay_gb_eoy_archive.get_parameter(
827 legislative_parameters,
828 'PAYROLL_ID')) payroll_id,
829 substr( pay_gb_eoy_archive.get_parameter(
830 legislative_parameters,
831 'TAX_REF'),1,20) tax_ref,
832 start_date,
833 effective_date,
834 fnd_date.canonical_to_date(
835 pay_gb_eoy_archive.get_parameter(
836 legislative_parameters,
837 'END_DATE')) end_date,
838 business_group_id
839 FROM pay_payroll_actions
840 WHERE payroll_action_id = pactid;
841 --
842 CURSOR c_asg IS
843 SELECT /* USE_INDEX(va,PQP_VEHICLE_ALLOCATIONS_F_N1)
844 */
845 asg.assignment_id,
846 min(asg.effective_start_date) asg_min_start_date,
847 max(asg.effective_end_date) asg_max_end_date
848 FROM hr_soft_coding_keyflex flex,
849 per_all_assignments_f asg,
850 pay_payrolls_f ppf,
851 pqp_vehicle_allocations_f va,
852 pqp_vehicle_repository_f vr
853 WHERE asg.person_id BETWEEN stperson AND endperson
854 AND asg.business_group_id = g_business_group_id
855 AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
856 AND asg.payroll_id = ppf.payroll_id
857 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
858 AND flex.segment1 = g_tax_ref
859 AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
860 OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
861 OR ( asg.effective_start_date BETWEEN g_start_date AND g_end_Date
862 AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
863 AND va.assignment_id = asg.assignment_id
864 AND va.business_group_id = g_business_group_id
865 AND ( ( va.effective_start_date BETWEEN g_start_date AND g_end_date
866 OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
867 OR ( g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
868 AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
869 AND vr.vehicle_repository_id = va.vehicle_repository_id
870 AND vr.vehicle_ownership = 'C'
871 AND vr.vehicle_type = 'C'
872 AND va.effective_start_date BETWEEN vr.effective_start_date AND vr.effective_end_date
873 GROUP by asg.assignment_id;
874 --
875 l_create_assact_flag VARCHAR2(1);
876 l_action_flag VARCHAR2(1);
877 l_archived_flag VARCHAR2(1);
878 BEGIN
879 --hr_utility.trace_on(null, 'KTHAMPAN');
880 hr_utility.set_location('Entering '||l_proc, 10);
881 --
882 OPEN c_param_values;
883 FETCH c_param_values INTO g_payroll_id,
884 g_tax_ref,
885 g_start_date,
886 g_effective_date,
887 g_end_date,
888 g_business_group_id;
889 CLOSE c_param_values;
890 --
891 hr_utility.set_location(l_proc, 15);
892 hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
893 hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
894 hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
895 hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
896 hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
900 -- Loop through all assignments
897 hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
898 For asg_rec IN c_asg
899 LOOP
901 hr_utility.set_location(l_proc, 20);
902 hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
903 hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
904 hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
905 --
906 l_create_assact_flag := 'N';
907 --
908 FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
909 -- Loop through all vehicle allocations
910 -- over the date range
911 hr_utility.set_location(l_proc, 30);
912 hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
913 hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
914 --
915 L_action_flag := 'X';
916 l_archived_flag := 'Y';
917 --
918 OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
919 FETCH c_alloc_dates INTO alc_dates_rec;
920 CLOSE c_alloc_dates;
921 --
922 hr_utility.set_location(l_proc, 40);
923 hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
924 hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
925 --
926 -- Check whether vehicle was allocated or
927 -- Withdrawn within the date range also
928 -- check that this allocation or withdrawal
929 -- happened when assignment was with input tax ref
930 IF (alc_dates_rec.min_start_date BETWEEN g_start_date AND g_end_Date) AND
931 (alc_dates_rec.min_start_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
932 --
933 -- This is a New Car or replacement action
934 IF alc_rec.usage_type = 'P' THEN
935 ---------------------------------------------
936 -- It's a primary car for the assignment --
937 -- therefore check whether it's the first --
938 -- car of the employee --
939 ---------------------------------------------
940 -- Get first assignment of this person
941 -- in the tax_ref
942 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
943 FETCH c_first_asg INTO first_asg_rec;
944 CLOSE c_first_asg;
945 --
946 hr_utility.set_location(l_proc, 50);
947 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
948 -- Get primary car allocation of the
949 -- first assignment of this person
950 -- in this tax ref
951 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
952 FETCH c_primary_car INTO primary_car_rec;
953 CLOSE c_primary_car;
954 --
955 hr_utility.set_location(l_proc, 60);
956 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
957 --
958 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
959 -- This is the first car allocation
960 -- of this employee in this tax ref
961 -- therefore check if it has replaced
962 -- any prior car
963 OPEN c_prior_prim_car(asg_rec.assignment_id, Alc_dates_rec.min_start_date);
964 FETCH c_prior_prim_car INTO prior_prim_car_rec;
965 IF c_prior_prim_car%FOUND THEN
966 L_action_flag := 'R';
967 ELSE
968 L_action_flag := 'N';
969 END IF;
970 --
971 hr_utility.set_location(l_proc, 70);
972 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
973 --
974 CLOSE c_prior_prim_car;
975 ELSE
976 -- Not the first car of the employee
977 -- report this as new car
978 L_action_flag := 'N';
979 END IF;
980 --
981 hr_utility.set_location(l_proc, 80);
982 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
983 --
984 ELSE
985 -- Not a primary car therefore mark
986 -- it as new car allocation action
987 l_action_flag := 'N';
988 END IF;
989 --
990 hr_utility.set_location(l_proc, 90);
991 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
992 --
993 IF L_action_flag <> 'X' THEN
994 -- Check whether this allocation action
995 -- has been already archived
996 l_archived_flag := 'Y';
997 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
998 alc_rec.vehicle_allocation_id,
999 alc_dates_rec.min_start_date,
1000 l_action_flag);
1001 --
1002 hr_utility.set_location(l_proc, 100);
1006 -- Action has not been archived already
1003 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1004 --
1005 IF l_archived_flag = 'N' THEN
1007 -- therefore create assignment action
1008 l_create_assact_flag := 'Y';
1009 END IF;
1010 END IF;
1011 END IF;
1012 --
1013 L_action_flag := 'X';
1014 l_archived_flag := 'Y';
1015 --
1016 --
1017 IF (alc_dates_rec.max_end_date BETWEEN g_start_date AND g_end_Date) AND
1018 (alc_dates_rec.max_end_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
1019 --
1020 IF alc_rec.usage_type = 'P' THEN
1021 ---------------------------------------------
1022 -- It's a primary car for the assignment --
1023 -- therefore check whether it's the first --
1024 -- car of the employee --
1025 ---------------------------------------------
1026 -- Get first assignment of this person
1027 -- in the tax_ref
1028 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
1029 FETCH c_first_asg INTO first_asg_rec;
1030 CLOSE c_first_asg;
1031 --
1032 hr_utility.set_location(l_proc, 50);
1033 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1034 -- Get primary car allocation of the
1035 -- first assignment of this person
1036 -- in this tax ref
1037 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
1038 FETCH c_primary_car INTO primary_car_rec;
1039 CLOSE c_primary_car;
1040 --
1041 hr_utility.set_location(l_proc, 60);
1042 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1043 --
1044 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1045 --
1046 OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
1047 FETCH c_next_prim_car INTO next_prim_car_rec;
1048 IF c_next_prim_car%FOUND THEN
1049 -- There is a replacement action to
1050 -- Report this car therefore
1051 -- No need to archive this action
1052 NULL;
1053 ELSE
1054 -- This is a withdrawal action
1055 L_action_flag := 'W';
1056 END IF;
1057 --
1058 hr_utility.set_location(l_proc, 70);
1059 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1060 --
1061 CLOSE c_next_prim_car;
1062 ELSE
1063 -- Not the first car of the employee
1064 -- report this as withdrawal car
1065 L_action_flag := 'W';
1066 END IF;
1067 --
1068 --
1069 hr_utility.set_location(l_proc, 80);
1070 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1071 --
1072 ELSE
1073 -- Not a first car therefore mark
1074 -- it as withdrawal car action
1075 l_action_flag := 'W';
1076 END IF;
1077 --
1078 hr_utility.set_location(l_proc, 90);
1079 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1080 --
1081 IF L_action_flag <> 'X' THEN
1082 -- Check whether this allocation action
1083 -- has been already archived
1084 l_archived_flag := 'Y';
1085 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1086 alc_rec.vehicle_allocation_id,
1087 alc_dates_rec.min_start_date,
1088 l_action_flag);
1089 --
1090 hr_utility.set_location(l_proc, 100);
1091 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1092 --
1093 IF l_archived_flag = 'N' THEN
1094 -- Action has not been archived already
1095 -- therefore create assignment action
1096 l_create_assact_flag := 'Y';
1097 END IF;
1098 END IF;
1099 END IF;
1100 -- Check if fuel type has changed within
1101 -- the date range.
1102 FOR vehicle_changes_rec IN c_vehicle_changes(alc_rec.vehicle_repository_id) LOOP
1103 -- Fuel type has changed therefore make sure
1104 -- this change occured after the vehicle was
1105 -- allocated to this assignment
1106 --
1107 hr_utility.set_location(l_proc, 110);
1108 hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
1109 hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
1110 --
1111 IF (vehicle_changes_rec.effective_start_date
1112 BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
1113 (vehicle_changes_rec.effective_start_date
1114 BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
1115 --
1116 -- Check if this fuel change has been
1117 -- already archived
1118 l_archived_flag := 'Y';
1122 'F');
1119 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1120 alc_rec.vehicle_allocation_id,
1121 vehicle_changes_rec.effective_start_date,
1123 --
1124 hr_utility.set_location(l_proc, 120);
1125 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1126 --
1127 IF l_archived_flag = 'N' THEN
1128 -- Action has not been archived already
1129 -- therefore create assignment action
1130 l_create_assact_flag := 'Y';
1131 --
1132 END IF;
1133 END IF;
1134 END LOOP;
1135 --
1136 hr_utility.set_location(l_proc, 130);
1137 --
1138 --
1139 L_action_flag := 'X';
1140 l_archived_flag := 'Y';
1141 --
1142 ----------------------------------------------------
1143 -- If tax ref has changed within the given date --
1144 -- range then check whether it should be reported --
1145 -- as a new allocation or withdrawal --
1146 ----------------------------------------------------
1147 IF (asg_rec.asg_min_start_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
1148 AND (asg_rec.asg_min_start_date BETWEEN g_start_Date and g_end_Date) THEN
1149 -- Assignment has started on this payroll or tax ref during the
1150 -- input date range and this car allocation was active on the
1151 -- start date therefore check whether it is a transfer
1152 -- from another tax ref
1153 hr_utility.set_location(l_proc, 160);
1154 l_previous_tax_ref := NULL;
1155 --
1156 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
1157 FETCH c_tax_ref INTO l_previous_tax_ref;
1158 CLOSE c_tax_ref;
1159 --
1160 hr_utility.set_location(l_proc, 170);
1161 hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
1162 --
1163 IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
1164 -- This assignment has a different prior tax ref
1165 -- therefore it should be reported as a new car
1166 -- allocation on this EDI message.
1167 hr_utility.set_location(l_proc, 180);
1168 l_action_flag := 'N';
1169 --
1170 -- Check whether this allocation action
1171 -- has been already archived
1172 l_archived_flag := 'Y';
1173 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1174 alc_rec.vehicle_allocation_id,
1175 asg_rec.asg_min_start_date,
1176 l_action_flag);
1177 --
1178 hr_utility.set_location(l_proc, 190);
1179 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
1180 --
1181 IF l_archived_flag = 'N' THEN
1182 -- Action has not been archived already
1183 -- therefore create assignment action
1184 l_create_assact_flag := 'Y';
1185 END IF;
1186 --
1187 END IF;
1188 END IF;
1189 --
1190 L_action_flag := 'X';
1191 l_archived_flag := 'Y';
1192 --
1193 IF (asg_rec.asg_max_end_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
1194 AND (asg_rec.asg_max_end_date BETWEEN g_start_Date and g_end_Date) THEN
1195 -- Assignment has ended on this payroll or tax ref during the
1196 -- input date range and this car allocation was active on the
1197 -- end date therefore check whether it is a transfer
1198 -- to another tax ref
1199 hr_utility.set_location(l_proc, 200);
1200 l_next_tax_ref := NULL;
1201 --
1202 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
1203 FETCH c_tax_ref INTO l_next_tax_ref;
1204 CLOSE c_tax_ref;
1205 --
1206 hr_utility.set_location(l_proc, 210);
1207 hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
1208 --
1209 IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
1210 -- This assignment has a different tax ref after end date
1211 -- therefore it should be reported as allocation withdrawal
1212 -- on this EDI message.
1213 hr_utility.set_location(l_proc, 220);
1214 l_action_flag := 'W';
1215 --
1216 -- Check whether this allocation action
1217 -- has been already archived
1218 l_archived_flag := 'Y';
1219 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1220 alc_rec.vehicle_allocation_id,
1221 asg_rec.asg_max_end_date,
1222 l_action_flag);
1223 --
1224 hr_utility.set_location(l_proc, 230);
1225 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
1226 --
1227 IF l_archived_flag = 'N' THEN
1228 -- Action has not been archived already
1229 -- therefore create assignment action
1230 l_create_assact_flag := 'Y';
1231 END IF;
1232 --
1233 END IF;
1237 --
1234 END IF;
1235 --
1236 hr_utility.set_location(l_proc, 240);
1238 hr_utility.set_location(l_proc, 250);
1239 END LOOP;
1240 --
1241 hr_utility.set_location(l_proc, 260);
1242 --
1243 -- If flag is set then create action
1244 IF l_create_assact_flag = 'Y' THEN
1245 --
1246 SELECT pay_assignment_actions_s.nextval
1247 INTO l_actid
1248 FROM dual;
1249
1250 hr_utility.set_location(l_proc, 270);
1251 hr_utility.trace(l_proc||': l_actid='||l_actid);
1252 hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
1253 hr_utility.trace(l_proc||': pactid='||pactid);
1254 hr_utility.trace(l_proc||': chunk='||chunk);
1255 --
1256 hr_nonrun_asact.insact(l_actid,
1257 asg_rec.assignment_id,
1258 pactid,
1259 chunk, NULL);
1260 --
1261 hr_utility.set_location(l_proc, 280);
1262 --
1263 END IF;
1264 END LOOP;
1265 --
1266 hr_utility.set_location('Leaving: '||l_proc,290);
1267 -- hr_utility.trace_off;
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 hr_utility.set_location('Leaving: '||l_proc,300);
1271 -- hr_utility.trace_off;
1272 raise;
1273 END Create_asg_act_v2;
1274
1275
1276 --For Bug 8986543
1277 -----------------------------------------------------
1278 -- PROCEDURE CREATE_ASG_ACT_V3 --
1279 -----------------------------------------------------
1280 PROCEDURE create_asg_act_v3(pactid IN NUMBER,
1281 stperson IN NUMBER,
1282 endperson IN NUMBER,
1283 chunk IN NUMBER) IS
1284 --
1285 l_proc CONSTANT VARCHAR2(100):= g_package||'create_asg_act_v3';
1286 l_actid pay_assignment_actions.assignment_action_id%TYPE;
1287 l_ovn pay_action_information.object_version_number%TYPE;
1288 --
1289 --
1290 CURSOR c_param_values IS
1291 SELECT to_number( pay_gb_eoy_archive.get_parameter(
1292 legislative_parameters,
1293 'PAYROLL_ID')) payroll_id,
1294 substr( pay_gb_eoy_archive.get_parameter(
1295 legislative_parameters,
1296 'TAX_REF'),1,20) tax_ref,
1297 start_date,
1298 effective_date,
1299 fnd_date.canonical_to_date(
1300 pay_gb_eoy_archive.get_parameter(
1301 legislative_parameters,
1302 'END_DATE')) end_date,
1303 business_group_id
1304 FROM pay_payroll_actions
1305 WHERE payroll_action_id = pactid;
1306 --
1307 CURSOR c_asg IS
1308 SELECT /* USE_INDEX(va,PQP_VEHICLE_ALLOCATIONS_F_N1)
1309 */
1310 asg.assignment_id,
1311 min(asg.effective_start_date) asg_min_start_date,
1312 max(asg.effective_end_date) asg_max_end_date
1313 FROM hr_soft_coding_keyflex flex,
1314 per_all_assignments_f asg,
1315 pay_payrolls_f ppf,
1316 pqp_vehicle_allocations_f va,
1317 pqp_vehicle_repository_f vr
1318 WHERE asg.person_id BETWEEN stperson AND endperson
1319 AND asg.business_group_id = g_business_group_id
1320 AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
1321 AND asg.payroll_id = ppf.payroll_id
1322 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1323 AND flex.segment1 = g_tax_ref
1324 AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1325 OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1326 OR ( asg.effective_start_date BETWEEN g_start_date AND g_end_Date
1327 AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
1328 AND va.assignment_id = asg.assignment_id
1329 AND va.business_group_id = g_business_group_id
1330 AND ( ( va.effective_start_date BETWEEN g_start_date AND g_end_date
1331 OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
1332 OR ( g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
1333 AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
1334 AND vr.vehicle_repository_id = va.vehicle_repository_id
1335 AND vr.vehicle_ownership = 'C'
1336 AND vr.vehicle_type = 'C'
1337 AND va.effective_start_date BETWEEN vr.effective_start_date AND vr.effective_end_date
1338 GROUP by asg.assignment_id;
1339 --
1340 l_create_assact_flag VARCHAR2(1);
1341 l_action_flag VARCHAR2(1);
1342 l_archived_flag VARCHAR2(1);
1343 l_private_start_dt date;
1344 l_latest_private_flag varchar2(1);
1345 BEGIN
1346 --hr_utility.trace_on(null, 'KTHAMPAN');
1347 hr_utility.set_location('Entering '||l_proc, 10);
1348 --
1349 OPEN c_param_values;
1350 FETCH c_param_values INTO g_payroll_id,
1351 g_tax_ref,
1352 g_start_date,
1353 g_effective_date,
1354 g_end_date,
1355 g_business_group_id;
1356 CLOSE c_param_values;
1357 --
1358 hr_utility.set_location(l_proc, 15);
1359 hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
1360 hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1361 hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1362 hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1363 hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
1364 hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
1365 For asg_rec IN c_asg
1366 LOOP
1367 -- Loop through all assignments
1368 hr_utility.set_location(l_proc, 20);
1369 hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1370 hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1371 hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1372 --
1373 l_create_assact_flag := 'N';
1374 --
1375 FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1376 -- Loop through all vehicle allocations
1377 -- over the date range
1378 hr_utility.set_location(l_proc, 30);
1379 hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
1380 hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
1381 --
1382 L_action_flag := 'X';
1383 l_archived_flag := 'Y';
1384 --
1385 ---
1386 --- Cursor to check if the private flag was changed from Y to N / N to Y
1387 ---
1388 open private_flag_check(alc_rec.vehicle_allocation_id);
1389 fetch private_flag_check into l_private_start_dt;
1390 close private_flag_check;
1391
1392 hr_utility.set_location(l_proc, 32);
1393 hr_utility.trace(l_proc||': l_private_start_dt='||l_private_start_dt);
1394 if l_private_start_dt is not null then
1395 -- Cursor to check if the change was from Y to N i.e. Withdrawal
1396 -- else was it from N to Y i.e. new Benifit
1397
1398 open withdrawal_check(alc_rec.vehicle_allocation_id,l_private_start_dt );
1399 fetch withdrawal_check into l_latest_private_flag;
1400 close withdrawal_check;
1401
1402 hr_utility.set_location(l_proc, 35);
1403 hr_utility.trace(l_proc||': l_latest_private_flag='||nvl(l_latest_private_flag,'NULL'));
1404
1405 if l_latest_private_flag = 'Y' then
1406 -- This means the value has changed from N to Y so this is a new benefit
1407 L_action_flag := 'N';
1408 elsif nvl(l_latest_private_flag,'N') = 'N' then
1409 -- This means the value has changed from Y to N so this is a withdrawal
1410 L_action_flag := 'W';
1411 end if;
1412
1413 IF L_action_flag <> 'X' THEN
1414 -- Check whether this allocation action
1415 -- has been already archived
1416 l_archived_flag := 'Y';
1417 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1418 alc_rec.vehicle_allocation_id,
1419 l_private_start_dt,
1420 l_action_flag);
1421 --
1422 hr_utility.set_location(l_proc, 38);
1423 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1424 --
1425 IF l_archived_flag = 'N' THEN
1426 -- Action has not been archived already
1427 -- therefore create assignment action
1428
1429 --Assignment action not required for
1430 --replaced car as per new requirements of V3
1431 IF L_action_flag <> 'R'
1432 THEN
1433 l_create_assact_flag := 'Y';
1434 END IF;
1435
1436 END IF;
1440 --
1437 END IF;
1438
1439 end if; -- end if l_private_start_dt l_private_start_dt is not null
1441 OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
1442 FETCH c_alloc_dates INTO alc_dates_rec;
1443 CLOSE c_alloc_dates;
1444 --
1445 hr_utility.set_location(l_proc, 40);
1446 hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1447 hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1448 --
1449 -- Check whether vehicle was allocated or
1450 -- Withdrawn within the date range also
1451 -- check that this allocation or withdrawal
1452 -- happened when assignment was with input tax ref
1453 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1454 hr_utility.trace(l_proc||': asg_rec.asg_min_start_date='|| fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1455 hr_utility.trace(l_proc||': asg_rec.asg_max_end_date='|| fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1456 hr_utility.trace(l_proc||': alc_rec.usage_type='|| alc_rec.usage_type);
1457 IF (alc_dates_rec.min_start_date BETWEEN g_start_date AND g_end_Date) AND
1458 (alc_dates_rec.min_start_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
1459 --
1460 -- This is a New Car or replacement action
1461 IF alc_rec.usage_type = 'P' THEN
1462 ---------------------------------------------
1463 -- It's a primary car for the assignment --
1464 -- therefore check whether it's the first --
1465 -- car of the employee --
1466 ---------------------------------------------
1467 -- Get first assignment of this person
1468 -- in the tax_ref
1469 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
1470 FETCH c_first_asg INTO first_asg_rec;
1471 CLOSE c_first_asg;
1472 --
1473 hr_utility.set_location(l_proc, 50);
1474 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1475 -- Get primary car allocation of the
1476 -- first assignment of this person
1477 -- in this tax ref
1478 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
1479 FETCH c_primary_car INTO primary_car_rec;
1480 CLOSE c_primary_car;
1481 --
1482 hr_utility.set_location(l_proc, 60);
1483 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1484 --
1485 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1486 -- This is the first car allocation
1487 -- of this employee in this tax ref
1488 -- therefore check if it has replaced
1489 -- any prior car
1490 OPEN c_prior_prim_car(asg_rec.assignment_id, Alc_dates_rec.min_start_date);
1491 FETCH c_prior_prim_car INTO prior_prim_car_rec;
1492 IF c_prior_prim_car%FOUND THEN
1493 L_action_flag := 'R';
1494 ELSE
1495 L_action_flag := 'N';
1496 END IF;
1497 --
1498 hr_utility.set_location(l_proc, 70);
1499 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1500 --
1501 CLOSE c_prior_prim_car;
1502 ELSE
1503 -- Not the first car of the employee
1504 -- report this as new car
1505 L_action_flag := 'N';
1506 END IF;
1507 --
1508 hr_utility.set_location(l_proc, 80);
1509 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1510 --
1511 ELSE
1512 -- Not a primary car therefore mark
1513 -- it as new car allocation action
1514 l_action_flag := 'N';
1515 END IF;
1516 --
1517 hr_utility.set_location(l_proc, 90);
1518 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1519 --
1520 IF L_action_flag <> 'X' THEN
1521 -- Check whether this allocation action
1522 -- has been already archived
1523 l_archived_flag := 'Y';
1524 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1525 alc_rec.vehicle_allocation_id,
1526 alc_dates_rec.min_start_date,
1527 l_action_flag);
1528 --
1529 hr_utility.set_location(l_proc, 100);
1530 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1531 --
1532 IF l_archived_flag = 'N' THEN
1533 -- Action has not been archived already
1534 -- therefore create assignment action
1535
1536 --Assignment action not required for
1537 --replaced car as per new requirements of V3
1538 IF L_action_flag <> 'R'
1539 THEN
1540 l_create_assact_flag := 'Y';
1541 END IF;
1542
1543 END IF;
1544 END IF;
1545 END IF;
1546 --
1547 L_action_flag := 'X';
1548 l_archived_flag := 'Y';
1549 --
1550 --
1551 IF (alc_dates_rec.max_end_date BETWEEN g_start_date AND g_end_Date) AND
1552 (alc_dates_rec.max_end_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
1553 --
1554 IF alc_rec.usage_type = 'P' THEN
1555 ---------------------------------------------
1556 -- It's a primary car for the assignment --
1557 -- therefore check whether it's the first --
1558 -- car of the employee --
1559 ---------------------------------------------
1560 -- Get first assignment of this person
1561 -- in the tax_ref
1562 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
1563 FETCH c_first_asg INTO first_asg_rec;
1564 CLOSE c_first_asg;
1565 --
1566 hr_utility.set_location(l_proc, 50);
1567 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1568 -- Get primary car allocation of the
1569 -- first assignment of this person
1570 -- in this tax ref
1571 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
1572 FETCH c_primary_car INTO primary_car_rec;
1573 CLOSE c_primary_car;
1574 --
1575 hr_utility.set_location(l_proc, 60);
1576 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1577 --
1578 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1579 --
1580 OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
1581 FETCH c_next_prim_car INTO next_prim_car_rec;
1582 IF c_next_prim_car%FOUND THEN
1583 -- There is a replacement action to
1584 -- Report this car therefore
1585 -- No need to archive this action
1586 NULL;
1587 ELSE
1588 -- This is a withdrawal action
1589 L_action_flag := 'W';
1590 END IF;
1591 --
1592 hr_utility.set_location(l_proc, 70);
1593 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1594 --
1595 CLOSE c_next_prim_car;
1596 ELSE
1597 -- Not the first car of the employee
1598 -- report this as withdrawal car
1599 L_action_flag := 'W';
1600 END IF;
1601 --
1602 --
1603 hr_utility.set_location(l_proc, 80);
1604 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1605 --
1606 ELSE
1607 -- Not a Primary car therefore mark
1608 -- it as withdrawal car action
1609 l_action_flag := 'W';
1610 END IF;
1611 --
1612 hr_utility.set_location(l_proc, 90);
1613 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1614 --
1615 IF L_action_flag <> 'X' THEN
1616 -- Check whether this allocation action
1617 -- has been already archived
1618 l_archived_flag := 'Y';
1619 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1620 alc_rec.vehicle_allocation_id,
1621 alc_dates_rec.min_start_date,
1622 l_action_flag);
1623 --
1624 hr_utility.set_location(l_proc, 100);
1625 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1626 --
1627 IF l_archived_flag = 'N' THEN
1628 -- Action has not been archived already
1629 -- therefore create assignment action
1630 l_create_assact_flag := 'Y';
1631 END IF;
1632 END IF;
1633 END IF;
1634 -- Check if fuel type has changed within
1635 -- the date range.
1636 FOR vehicle_changes_rec IN c_vehicle_changes(alc_rec.vehicle_repository_id) LOOP
1637 -- Fuel type has changed therefore make sure
1638 -- this change occured after the vehicle was
1639 -- allocated to this assignment
1640 --
1641 hr_utility.set_location(l_proc, 110);
1642 hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
1643 hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
1644 --
1645 IF (vehicle_changes_rec.effective_start_date
1646 BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
1647 (vehicle_changes_rec.effective_start_date
1648 BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
1649 --
1650 -- Check if this fuel change has been
1651 -- already archived
1652 l_archived_flag := 'Y';
1653 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1654 alc_rec.vehicle_allocation_id,
1655 vehicle_changes_rec.effective_start_date,
1656 'F');
1657 --
1658 hr_utility.set_location(l_proc, 120);
1659 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1660 --
1661 IF l_archived_flag = 'N' THEN
1662 -- Action has not been archived already
1663 -- therefore create assignment action
1664 l_create_assact_flag := 'Y';
1665 --
1666 END IF;
1667 END IF;
1668 END LOOP;
1669 --
1670 hr_utility.set_location(l_proc, 130);
1671 --
1672 --
1673 L_action_flag := 'X';
1674 l_archived_flag := 'Y';
1675 --
1676 ----------------------------------------------------
1677 -- If tax ref has changed within the given date --
1678 -- range then check whether it should be reported --
1679 -- as a new allocation or withdrawal --
1680 ----------------------------------------------------
1681 IF (asg_rec.asg_min_start_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
1682 AND (asg_rec.asg_min_start_date BETWEEN g_start_Date and g_end_Date) THEN
1683 -- Assignment has started on this payroll or tax ref during the
1684 -- input date range and this car allocation was active on the
1685 -- start date therefore check whether it is a transfer
1686 -- from another tax ref
1687 hr_utility.set_location(l_proc, 160);
1688 l_previous_tax_ref := NULL;
1689 --
1690 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
1691 FETCH c_tax_ref INTO l_previous_tax_ref;
1692 CLOSE c_tax_ref;
1693 --
1694 hr_utility.set_location(l_proc, 170);
1695 hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
1696 --
1697 IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
1698 -- This assignment has a different prior tax ref
1699 -- therefore it should be reported as a new car
1700 -- allocation on this EDI message.
1701 hr_utility.set_location(l_proc, 180);
1702 l_action_flag := 'N';
1703 --
1704 -- Check whether this allocation action
1705 -- has been already archived
1706 l_archived_flag := 'Y';
1707 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1708 alc_rec.vehicle_allocation_id,
1709 asg_rec.asg_min_start_date,
1710 l_action_flag);
1711 --
1712 hr_utility.set_location(l_proc, 190);
1713 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
1714 --
1715 IF l_archived_flag = 'N' THEN
1716 -- Action has not been archived already
1717 -- therefore create assignment action
1718 l_create_assact_flag := 'Y';
1719 END IF;
1720 --
1721 END IF;
1722 END IF;
1723 --
1724 L_action_flag := 'X';
1725 l_archived_flag := 'Y';
1726 --
1727 IF (asg_rec.asg_max_end_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
1728 AND (asg_rec.asg_max_end_date BETWEEN g_start_Date and g_end_Date) THEN
1729 -- Assignment has ended on this payroll or tax ref during the
1730 -- input date range and this car allocation was active on the
1731 -- end date therefore check whether it is a transfer
1732 -- to another tax ref
1733 hr_utility.set_location(l_proc, 200);
1734 l_next_tax_ref := NULL;
1735 --
1736 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
1737 FETCH c_tax_ref INTO l_next_tax_ref;
1738 CLOSE c_tax_ref;
1739 --
1740 hr_utility.set_location(l_proc, 210);
1741 hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
1742 --
1743 IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
1744 -- This assignment has a different tax ref after end date
1745 -- therefore it should be reported as allocation withdrawal
1746 -- on this EDI message.
1747 hr_utility.set_location(l_proc, 220);
1748 l_action_flag := 'W';
1749 --
1750 -- Check whether this allocation action
1751 -- has been already archived
1752 l_archived_flag := 'Y';
1753 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1754 alc_rec.vehicle_allocation_id,
1755 asg_rec.asg_max_end_date,
1756 l_action_flag);
1757 --
1758 hr_utility.set_location(l_proc, 230);
1759 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
1760 --
1761 IF l_archived_flag = 'N' THEN
1762 -- Action has not been archived already
1763 -- therefore create assignment action
1764 l_create_assact_flag := 'Y';
1765 END IF;
1766 --
1767 END IF;
1768 END IF;
1769 --
1770 hr_utility.set_location(l_proc, 240);
1771 --
1772 hr_utility.set_location(l_proc, 250);
1773 END LOOP;
1774 --
1775 hr_utility.set_location(l_proc, 260);
1776 --
1777 -- If flag is set then create action
1778 IF l_create_assact_flag = 'Y' THEN
1779 --
1780 SELECT pay_assignment_actions_s.nextval
1781 INTO l_actid
1782 FROM dual;
1783
1784 hr_utility.set_location(l_proc, 270);
1785 hr_utility.trace(l_proc||': l_actid='||l_actid);
1786 hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
1787 hr_utility.trace(l_proc||': pactid='||pactid);
1788 hr_utility.trace(l_proc||': chunk='||chunk);
1789 --
1790 hr_nonrun_asact.insact(l_actid,
1791 asg_rec.assignment_id,
1792 pactid,
1793 chunk, NULL);
1794 --
1795 hr_utility.set_location(l_proc, 280);
1796 --
1797 END IF;
1798 END LOOP;
1799 --
1800 hr_utility.set_location('Leaving: '||l_proc,290);
1801 -- hr_utility.trace_off;
1802 EXCEPTION
1803 WHEN OTHERS THEN
1804 hr_utility.set_location('Leaving: '||l_proc,300);
1805 -- hr_utility.trace_off;
1806 raise;
1807 END Create_asg_act_v3;
1808
1809
1810 --For Bug 10095492
1811 -----------------------------------------------------
1812 -- PROCEDURE CREATE_ASG_ACT_V4 --
1813 -----------------------------------------------------
1814 PROCEDURE create_asg_act_v4(pactid IN NUMBER,
1815 stperson IN NUMBER,
1816 endperson IN NUMBER,
1817 chunk IN NUMBER) IS
1818 --
1819 l_proc CONSTANT VARCHAR2(100):= g_package||'create_asg_act_v4';
1820 l_actid pay_assignment_actions.assignment_action_id%TYPE;
1821 l_ovn pay_action_information.object_version_number%TYPE;
1822 --
1823 --
1824 CURSOR c_param_values IS
1825 SELECT to_number( pay_gb_eoy_archive.get_parameter(
1826 legislative_parameters,
1827 'PAYROLL_ID')) payroll_id,
1828 substr( pay_gb_eoy_archive.get_parameter(
1829 legislative_parameters,
1830 'TAX_REF'),1,20) tax_ref,
1831 start_date,
1832 effective_date,
1833 fnd_date.canonical_to_date(
1834 pay_gb_eoy_archive.get_parameter(
1835 legislative_parameters,
1836 'END_DATE')) end_date,
1837 business_group_id
1838 FROM pay_payroll_actions
1839 WHERE payroll_action_id = pactid;
1840 --
1841 CURSOR c_asg IS
1842 SELECT /* USE_INDEX(va,PQP_VEHICLE_ALLOCATIONS_F_N1)
1843 */
1844 asg.assignment_id,
1845 min(asg.effective_start_date) asg_min_start_date,
1846 max(asg.effective_end_date) asg_max_end_date
1847 FROM hr_soft_coding_keyflex flex,
1848 per_all_assignments_f asg,
1849 pay_payrolls_f ppf,
1850 pqp_vehicle_allocations_f va,
1851 pqp_vehicle_repository_f vr
1852 WHERE asg.person_id BETWEEN stperson AND endperson
1853 AND asg.business_group_id = g_business_group_id
1854 AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
1855 AND asg.payroll_id = ppf.payroll_id
1856 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1857 AND flex.segment1 = g_tax_ref
1858 AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1859 OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1860 OR ( asg.effective_start_date BETWEEN g_start_date AND g_end_Date
1861 AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
1862 AND va.assignment_id = asg.assignment_id
1863 AND va.business_group_id = g_business_group_id
1864 AND ( ( va.effective_start_date BETWEEN g_start_date AND g_end_date
1865 OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
1866 OR ( g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
1867 AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
1868 AND vr.vehicle_repository_id = va.vehicle_repository_id
1869 AND vr.vehicle_ownership = 'C'
1870 AND vr.vehicle_type = 'C'
1871 AND va.effective_start_date BETWEEN vr.effective_start_date AND vr.effective_end_date
1872 GROUP by asg.assignment_id;
1873 --
1874 l_create_assact_flag VARCHAR2(1);
1875 l_action_flag VARCHAR2(1);
1876 l_archived_flag VARCHAR2(1);
1877 l_private_start_dt date;
1878 l_latest_private_flag varchar2(1);
1879 BEGIN
1880 --hr_utility.trace_on(null, 'KTHAMPAN');
1881 hr_utility.set_location('Entering '||l_proc, 10);
1882 --
1883 OPEN c_param_values;
1887 g_effective_date,
1884 FETCH c_param_values INTO g_payroll_id,
1885 g_tax_ref,
1886 g_start_date,
1888 g_end_date,
1889 g_business_group_id;
1890 CLOSE c_param_values;
1891 --
1892 hr_utility.set_location(l_proc, 15);
1893 hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
1894 hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1895 hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1896 hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1897 hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
1898 hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
1899 For asg_rec IN c_asg
1900 LOOP
1901 -- Loop through all assignments
1902 hr_utility.set_location(l_proc, 20);
1903 hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1904 hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1905 hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1906 --
1907 l_create_assact_flag := 'N';
1908 --
1909 FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1910 -- Loop through all vehicle allocations
1911 -- over the date range
1912 hr_utility.set_location(l_proc, 30);
1913 hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
1914 hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
1915 --
1916 L_action_flag := 'X';
1917 l_archived_flag := 'Y';
1918 --
1919 ---
1920 --- Cursor to check if the private flag was changed from Y to N / N to Y
1921 ---
1922 open private_flag_check(alc_rec.vehicle_allocation_id);
1923 fetch private_flag_check into l_private_start_dt;
1924 close private_flag_check;
1925
1926 hr_utility.set_location(l_proc, 32);
1927 hr_utility.trace(l_proc||': l_private_start_dt='||l_private_start_dt);
1928 if l_private_start_dt is not null then
1929 -- Cursor to check if the change was from Y to N i.e. Withdrawal
1930 -- else was it from N to Y i.e. new Benifit
1931
1932 open withdrawal_check(alc_rec.vehicle_allocation_id,l_private_start_dt );
1933 fetch withdrawal_check into l_latest_private_flag;
1934 close withdrawal_check;
1935
1936 hr_utility.set_location(l_proc, 35);
1937 hr_utility.trace(l_proc||': l_latest_private_flag='||nvl(l_latest_private_flag,'NULL'));
1938
1939 if l_latest_private_flag = 'Y' then
1940 -- This means the value has changed from N to Y so this is a new benefit
1941 L_action_flag := 'N';
1942 elsif nvl(l_latest_private_flag,'N') = 'N' then
1943 -- This means the value has changed from Y to N so this is a withdrawal
1944 L_action_flag := 'W';
1945 end if;
1946
1947 IF L_action_flag <> 'X' THEN
1948 -- Check whether this allocation action
1949 -- has been already archived
1950 l_archived_flag := 'Y';
1951 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
1952 alc_rec.vehicle_allocation_id,
1953 l_private_start_dt,
1954 l_action_flag);
1955 --
1956 hr_utility.set_location(l_proc, 38);
1957 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1958 --
1959 IF l_archived_flag = 'N' THEN
1960 -- Action has not been archived already
1961 -- therefore create assignment action
1962
1963 --Assignment action not required for
1964 --replaced car as per new requirements of V3
1965
1966 /* Commented below condition for bug 10312065 */
1967 /* Assignment Action is needed for Replacement car from V4 */
1968 -- IF L_action_flag <> 'R'
1969 -- THEN
1970 l_create_assact_flag := 'Y';
1971 -- END IF;
1972
1973 END IF;
1974 END IF;
1975
1976 end if; -- end if l_private_start_dt l_private_start_dt is not null
1977 --
1978 OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
1979 FETCH c_alloc_dates INTO alc_dates_rec;
1980 CLOSE c_alloc_dates;
1981 --
1982 hr_utility.set_location(l_proc, 40);
1983 hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1984 hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1985 --
1986 -- Check whether vehicle was allocated or
1987 -- Withdrawn within the date range also
1988 -- check that this allocation or withdrawal
1989 -- happened when assignment was with input tax ref
1990 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1991 hr_utility.trace(l_proc||': asg_rec.asg_min_start_date='|| fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1992 hr_utility.trace(l_proc||': asg_rec.asg_max_end_date='|| fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1993 hr_utility.trace(l_proc||': alc_rec.usage_type='|| alc_rec.usage_type);
1994 IF (alc_dates_rec.min_start_date BETWEEN g_start_date AND g_end_Date) AND
1995 (alc_dates_rec.min_start_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
1996 --
1997 -- This is a New Car or replacement action
1998 IF alc_rec.usage_type = 'P' THEN
1999 ---------------------------------------------
2000 -- It's a primary car for the assignment --
2001 -- therefore check whether it's the first --
2002 -- car of the employee --
2003 ---------------------------------------------
2004 -- Get first assignment of this person
2005 -- in the tax_ref
2006 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
2007 FETCH c_first_asg INTO first_asg_rec;
2008 CLOSE c_first_asg;
2009 --
2010 hr_utility.set_location(l_proc, 50);
2011 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2012 -- Get primary car allocation of the
2013 -- first assignment of this person
2014 -- in this tax ref
2015 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
2016 FETCH c_primary_car INTO primary_car_rec;
2017 CLOSE c_primary_car;
2018 --
2019 hr_utility.set_location(l_proc, 60);
2020 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2021 --
2022 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2023 -- This is the first car allocation
2024 -- of this employee in this tax ref
2025 -- therefore check if it has replaced
2026 -- any prior car
2027 OPEN c_prior_prim_car(asg_rec.assignment_id, Alc_dates_rec.min_start_date);
2028 FETCH c_prior_prim_car INTO prior_prim_car_rec;
2029 IF c_prior_prim_car%FOUND THEN
2030 L_action_flag := 'R';
2031 ELSE
2032 L_action_flag := 'N';
2033 END IF;
2034 --
2035 hr_utility.set_location(l_proc, 70);
2036 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2037 --
2038 CLOSE c_prior_prim_car;
2039 ELSE
2040 -- Not the first car of the employee
2041 -- report this as new car
2042 L_action_flag := 'N';
2043 END IF;
2044 --
2045 hr_utility.set_location(l_proc, 80);
2046 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2047 --
2048 ELSE
2049 -- Not a primary car therefore mark
2050 -- it as new car allocation action
2051 l_action_flag := 'N';
2052 END IF;
2053 --
2054 hr_utility.set_location(l_proc, 90);
2055 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2056 --
2057 IF L_action_flag <> 'X' THEN
2058 -- Check whether this allocation action
2059 -- has been already archived
2060 l_archived_flag := 'Y';
2061 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2062 alc_rec.vehicle_allocation_id,
2063 alc_dates_rec.min_start_date,
2064 l_action_flag);
2065 --
2066 hr_utility.set_location(l_proc, 100);
2067 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2068 --
2069 IF l_archived_flag = 'N' THEN
2070 -- Action has not been archived already
2071 -- therefore create assignment action
2072
2073 --Assignment action not required for
2074 --replaced car as per new requirements of V3
2075
2076 /* Commented below condition for bug 10312065 */
2077 /* Assignment Action is needed for Replacement car from V4 */
2078 -- IF L_action_flag <> 'R'
2079 -- THEN
2080 l_create_assact_flag := 'Y';
2081 -- END IF;
2082
2083 END IF;
2084 END IF;
2085 END IF;
2086 --
2087 L_action_flag := 'X';
2088 l_archived_flag := 'Y';
2089 --
2090 --
2091 IF (alc_dates_rec.max_end_date BETWEEN g_start_date AND g_end_Date) AND
2092 (alc_dates_rec.max_end_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
2093 --
2094 IF alc_rec.usage_type = 'P' THEN
2095 ---------------------------------------------
2096 -- It's a primary car for the assignment --
2097 -- therefore check whether it's the first --
2098 -- car of the employee --
2099 ---------------------------------------------
2100 -- Get first assignment of this person
2101 -- in the tax_ref
2102 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
2103 FETCH c_first_asg INTO first_asg_rec;
2104 CLOSE c_first_asg;
2105 --
2106 hr_utility.set_location(l_proc, 50);
2107 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2108 -- Get primary car allocation of the
2109 -- first assignment of this person
2110 -- in this tax ref
2111 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
2112 FETCH c_primary_car INTO primary_car_rec;
2113 CLOSE c_primary_car;
2114 --
2115 hr_utility.set_location(l_proc, 60);
2116 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2117 --
2118 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2119 --
2120 OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
2121 FETCH c_next_prim_car INTO next_prim_car_rec;
2122 IF c_next_prim_car%FOUND THEN
2123 -- There is a replacement action to
2124 -- Report this car therefore
2125 -- No need to archive this action
2126 NULL;
2127 ELSE
2128 -- This is a withdrawal action
2129 L_action_flag := 'W';
2130 END IF;
2131 --
2132 hr_utility.set_location(l_proc, 70);
2133 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2134 --
2135 CLOSE c_next_prim_car;
2136 ELSE
2137 -- Not the first car of the employee
2138 -- report this as withdrawal car
2139 L_action_flag := 'W';
2140 END IF;
2141 --
2142 --
2143 hr_utility.set_location(l_proc, 80);
2144 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2145 --
2146 ELSE
2147 -- Not a Primary car therefore mark
2148 -- it as withdrawal car action
2149 l_action_flag := 'W';
2150 END IF;
2151 --
2152 hr_utility.set_location(l_proc, 90);
2153 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2154 --
2155 IF L_action_flag <> 'X' THEN
2156 -- Check whether this allocation action
2157 -- has been already archived
2158 l_archived_flag := 'Y';
2159 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2160 alc_rec.vehicle_allocation_id,
2161 alc_dates_rec.min_start_date,
2162 l_action_flag);
2163 --
2164 hr_utility.set_location(l_proc, 100);
2165 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2166 --
2167 IF l_archived_flag = 'N' THEN
2168 -- Action has not been archived already
2169 -- therefore create assignment action
2170 l_create_assact_flag := 'Y';
2171 END IF;
2172 END IF;
2173 END IF;
2174 -- Check if fuel type has changed within
2175 -- the date range.
2176 FOR vehicle_changes_rec IN c_vehicle_changes_v4(alc_rec.vehicle_repository_id) LOOP
2177 -- Fuel type has changed therefore make sure
2178 -- this change occured after the vehicle was
2179 -- allocated to this assignment
2180 --
2181 hr_utility.set_location(l_proc, 110);
2182 hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
2183 hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
2184 --
2185 IF (vehicle_changes_rec.effective_start_date
2186 BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
2187 (vehicle_changes_rec.effective_start_date
2188 BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
2189 --
2190 -- Check if this fuel change has been
2191 -- already archived
2192 l_archived_flag := 'Y';
2193 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2194 alc_rec.vehicle_allocation_id,
2195 vehicle_changes_rec.effective_start_date,
2196 'F');
2197 --
2198 hr_utility.set_location(l_proc, 120);
2199 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2200 --
2201 IF l_archived_flag = 'N' THEN
2202 -- Action has not been archived already
2203 -- therefore create assignment action
2204 l_create_assact_flag := 'Y';
2205 --
2206 END IF;
2207 END IF;
2208 END LOOP;
2209 --
2210 hr_utility.set_location(l_proc, 130);
2211 --
2212 --
2213 L_action_flag := 'X';
2214 l_archived_flag := 'Y';
2215 --
2216 ----------------------------------------------------
2217 -- If tax ref has changed within the given date --
2218 -- range then check whether it should be reported --
2219 -- as a new allocation or withdrawal --
2220 ----------------------------------------------------
2221 IF (asg_rec.asg_min_start_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
2222 AND (asg_rec.asg_min_start_date BETWEEN g_start_Date and g_end_Date) THEN
2223 -- Assignment has started on this payroll or tax ref during the
2224 -- input date range and this car allocation was active on the
2225 -- start date therefore check whether it is a transfer
2226 -- from another tax ref
2227 hr_utility.set_location(l_proc, 160);
2228 l_previous_tax_ref := NULL;
2229 --
2230 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
2231 FETCH c_tax_ref INTO l_previous_tax_ref;
2232 CLOSE c_tax_ref;
2233 --
2234 hr_utility.set_location(l_proc, 170);
2235 hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
2236 --
2237 IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
2238 -- This assignment has a different prior tax ref
2239 -- therefore it should be reported as a new car
2240 -- allocation on this EDI message.
2241 hr_utility.set_location(l_proc, 180);
2242 l_action_flag := 'N';
2243 --
2244 -- Check whether this allocation action
2245 -- has been already archived
2246 l_archived_flag := 'Y';
2247 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2248 alc_rec.vehicle_allocation_id,
2249 asg_rec.asg_min_start_date,
2250 l_action_flag);
2251 --
2252 hr_utility.set_location(l_proc, 190);
2253 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2254 --
2255 IF l_archived_flag = 'N' THEN
2256 -- Action has not been archived already
2257 -- therefore create assignment action
2258 l_create_assact_flag := 'Y';
2259 END IF;
2260 --
2261 END IF;
2262 END IF;
2263 --
2264 L_action_flag := 'X';
2265 l_archived_flag := 'Y';
2266 --
2267 IF (asg_rec.asg_max_end_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
2268 AND (asg_rec.asg_max_end_date BETWEEN g_start_Date and g_end_Date) THEN
2269 -- Assignment has ended on this payroll or tax ref during the
2270 -- input date range and this car allocation was active on the
2271 -- end date therefore check whether it is a transfer
2272 -- to another tax ref
2273 hr_utility.set_location(l_proc, 200);
2274 l_next_tax_ref := NULL;
2275 --
2276 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
2277 FETCH c_tax_ref INTO l_next_tax_ref;
2278 CLOSE c_tax_ref;
2279 --
2280 hr_utility.set_location(l_proc, 210);
2281 hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
2282 --
2283 IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
2284 -- This assignment has a different tax ref after end date
2285 -- therefore it should be reported as allocation withdrawal
2286 -- on this EDI message.
2287 hr_utility.set_location(l_proc, 220);
2288 l_action_flag := 'W';
2289 --
2290 -- Check whether this allocation action
2291 -- has been already archived
2292 l_archived_flag := 'Y';
2293 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2294 alc_rec.vehicle_allocation_id,
2295 asg_rec.asg_max_end_date,
2296 l_action_flag);
2297 --
2298 hr_utility.set_location(l_proc, 230);
2299 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2300 --
2301 IF l_archived_flag = 'N' THEN
2302 -- Action has not been archived already
2303 -- therefore create assignment action
2304 l_create_assact_flag := 'Y';
2305 END IF;
2306 --
2307 END IF;
2308 END IF;
2309 --
2310 hr_utility.set_location(l_proc, 240);
2311 --
2312 hr_utility.set_location(l_proc, 250);
2313 END LOOP;
2314 --
2315 hr_utility.set_location(l_proc, 260);
2316 --
2317 -- If flag is set then create action
2318 IF l_create_assact_flag = 'Y' THEN
2319 --
2320 SELECT pay_assignment_actions_s.nextval
2321 INTO l_actid
2322 FROM dual;
2323
2324 hr_utility.set_location(l_proc, 270);
2325 hr_utility.trace(l_proc||': l_actid='||l_actid);
2326 hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
2327 hr_utility.trace(l_proc||': pactid='||pactid);
2328 hr_utility.trace(l_proc||': chunk='||chunk);
2329 --
2330 hr_nonrun_asact.insact(l_actid,
2331 asg_rec.assignment_id,
2332 pactid,
2333 chunk, NULL);
2334 --
2335 hr_utility.set_location(l_proc, 280);
2336 --
2337 END IF;
2338 END LOOP;
2339 --
2340 hr_utility.set_location('Leaving: '||l_proc,290);
2341 -- hr_utility.trace_off;
2342 EXCEPTION
2343 WHEN OTHERS THEN
2344 hr_utility.set_location('Leaving: '||l_proc,300);
2345 -- hr_utility.trace_off;
2346 raise;
2347 END Create_asg_act_v4;
2348
2349
2350 --For Bug 13400872
2351 -----------------------------------------------------
2352 -- PROCEDURE CREATE_ASG_ACT_V5 --
2353 -----------------------------------------------------
2354 PROCEDURE create_asg_act_v5(pactid IN NUMBER,
2355 stperson IN NUMBER,
2356 endperson IN NUMBER,
2357 chunk IN NUMBER) IS
2358 --
2359 l_proc CONSTANT VARCHAR2(100):= g_package||'create_asg_act_v5';
2360 l_actid pay_assignment_actions.assignment_action_id%TYPE;
2361 l_ovn pay_action_information.object_version_number%TYPE;
2362 --
2363 --
2364 CURSOR c_param_values IS
2365 SELECT to_number( pay_gb_eoy_archive.get_parameter(
2366 legislative_parameters,
2367 'PAYROLL_ID')) payroll_id,
2368 substr( pay_gb_eoy_archive.get_parameter(
2369 legislative_parameters,
2370 'TAX_REF'),1,20) tax_ref,
2371 start_date,
2372 effective_date,
2373 fnd_date.canonical_to_date(
2374 pay_gb_eoy_archive.get_parameter(
2375 legislative_parameters,
2376 'END_DATE')) end_date,
2377 business_group_id
2378 FROM pay_payroll_actions
2379 WHERE payroll_action_id = pactid;
2380 --
2381 CURSOR c_asg IS
2382 SELECT /* USE_INDEX(va,PQP_VEHICLE_ALLOCATIONS_F_N1)
2383 */
2384 asg.assignment_id,
2385 min(asg.effective_start_date) asg_min_start_date,
2386 max(asg.effective_end_date) asg_max_end_date
2387 FROM hr_soft_coding_keyflex flex,
2388 per_all_assignments_f asg,
2389 pay_payrolls_f ppf,
2390 pqp_vehicle_allocations_f va,
2391 pqp_vehicle_repository_f vr
2392 WHERE asg.person_id BETWEEN stperson AND endperson
2393 AND asg.business_group_id = g_business_group_id
2394 AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
2395 AND asg.payroll_id = ppf.payroll_id
2396 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2397 AND flex.segment1 = g_tax_ref
2398 AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2399 OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2400 OR ( asg.effective_start_date BETWEEN g_start_date AND g_end_Date
2401 AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
2402 AND va.assignment_id = asg.assignment_id
2403 AND va.business_group_id = g_business_group_id
2404 AND ( ( va.effective_start_date BETWEEN g_start_date AND g_end_date
2405 OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
2406 OR ( g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
2407 AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
2408 AND vr.vehicle_repository_id = va.vehicle_repository_id
2409 AND vr.vehicle_ownership = 'C'
2410 AND vr.vehicle_type = 'C'
2411 AND va.effective_start_date BETWEEN vr.effective_start_date AND vr.effective_end_date
2412 GROUP by asg.assignment_id;
2413 --
2414 l_create_assact_flag VARCHAR2(1);
2415 l_action_flag VARCHAR2(1);
2416 l_archived_flag VARCHAR2(1);
2417 l_private_start_dt date;
2418 l_latest_private_flag varchar2(1);
2419 BEGIN
2420 --hr_utility.trace_on(null, 'KTHAMPAN');
2421 hr_utility.set_location('Entering '||l_proc, 10);
2422 --
2423 OPEN c_param_values;
2424 FETCH c_param_values INTO g_payroll_id,
2425 g_tax_ref,
2426 g_start_date,
2427 g_effective_date,
2428 g_end_date,
2429 g_business_group_id;
2430 CLOSE c_param_values;
2431 --
2432 hr_utility.set_location(l_proc, 15);
2433 hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
2434 hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
2435 hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
2436 hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
2437 hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
2438 hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
2439 For asg_rec IN c_asg
2440 LOOP
2441 -- Loop through all assignments
2442 hr_utility.set_location(l_proc, 20);
2443 hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
2444 hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
2445 hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
2446 --
2447 l_create_assact_flag := 'N';
2448 --
2449 FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
2450 -- Loop through all vehicle allocations
2451 -- over the date range
2452 hr_utility.set_location(l_proc, 30);
2453 hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
2454 hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
2455 --
2456 L_action_flag := 'X';
2457 l_archived_flag := 'Y';
2458 --
2459 ---
2460 --- Cursor to check if the private flag was changed from Y to N / N to Y
2461 ---
2462 open private_flag_check(alc_rec.vehicle_allocation_id);
2463 fetch private_flag_check into l_private_start_dt;
2464 close private_flag_check;
2465
2466 hr_utility.set_location(l_proc, 32);
2467 hr_utility.trace(l_proc||': l_private_start_dt='||l_private_start_dt);
2468 if l_private_start_dt is not null then
2469 -- Cursor to check if the change was from Y to N i.e. Withdrawal
2470 -- else was it from N to Y i.e. new Benifit
2471
2472 open withdrawal_check(alc_rec.vehicle_allocation_id,l_private_start_dt );
2473 fetch withdrawal_check into l_latest_private_flag;
2474 close withdrawal_check;
2475
2476 hr_utility.set_location(l_proc, 35);
2480 -- This means the value has changed from N to Y so this is a new benefit
2477 hr_utility.trace(l_proc||': l_latest_private_flag='||nvl(l_latest_private_flag,'NULL'));
2478
2479 if l_latest_private_flag = 'Y' then
2481 L_action_flag := 'N';
2482 elsif nvl(l_latest_private_flag,'N') = 'N' then
2483 -- This means the value has changed from Y to N so this is a withdrawal
2484 L_action_flag := 'W';
2485 end if;
2486
2487 IF L_action_flag <> 'X' THEN
2488 -- Check whether this allocation action
2489 -- has been already archived
2490 l_archived_flag := 'Y';
2491 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2492 alc_rec.vehicle_allocation_id,
2493 l_private_start_dt,
2494 l_action_flag);
2495 --
2496 hr_utility.set_location(l_proc, 38);
2497 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2498 --
2499 IF l_archived_flag = 'N' THEN
2500 -- Action has not been archived already
2501 -- therefore create assignment action
2502
2503 --Assignment action not required for
2504 --replaced car as per new requirements of V3
2505
2506 /* Commented below condition for bug 10312065 */
2507 /* Assignment Action is needed for Replacement car from V4 */
2508 -- IF L_action_flag <> 'R'
2509 -- THEN
2510 l_create_assact_flag := 'Y';
2511 -- END IF;
2512
2513 END IF;
2514 END IF;
2515
2516 end if; -- end if l_private_start_dt l_private_start_dt is not null
2517 --
2518 OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
2519 FETCH c_alloc_dates INTO alc_dates_rec;
2520 CLOSE c_alloc_dates;
2521 --
2522 hr_utility.set_location(l_proc, 40);
2523 hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2524 hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2525 --
2526 -- Check whether vehicle was allocated or
2527 -- Withdrawn within the date range also
2528 -- check that this allocation or withdrawal
2529 -- happened when assignment was with input tax ref
2530 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2531 hr_utility.trace(l_proc||': asg_rec.asg_min_start_date='|| fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
2532 hr_utility.trace(l_proc||': asg_rec.asg_max_end_date='|| fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
2533 hr_utility.trace(l_proc||': alc_rec.usage_type='|| alc_rec.usage_type);
2534 IF (alc_dates_rec.min_start_date BETWEEN g_start_date AND g_end_Date) AND
2535 (alc_dates_rec.min_start_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
2536 --
2537 -- This is a New Car or replacement action
2538 IF alc_rec.usage_type = 'P' THEN
2539 ---------------------------------------------
2540 -- It's a primary car for the assignment --
2541 -- therefore check whether it's the first --
2542 -- car of the employee --
2543 ---------------------------------------------
2544 -- Get first assignment of this person
2545 -- in the tax_ref
2546 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
2547 FETCH c_first_asg INTO first_asg_rec;
2548 CLOSE c_first_asg;
2549 --
2550 hr_utility.set_location(l_proc, 50);
2551 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2552 -- Get primary car allocation of the
2553 -- first assignment of this person
2554 -- in this tax ref
2555 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
2556 FETCH c_primary_car INTO primary_car_rec;
2557 CLOSE c_primary_car;
2558 --
2559 hr_utility.set_location(l_proc, 60);
2560 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2561 --
2562 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2563 -- This is the first car allocation
2564 -- of this employee in this tax ref
2565 -- therefore check if it has replaced
2566 -- any prior car
2567 OPEN c_prior_prim_car(asg_rec.assignment_id, Alc_dates_rec.min_start_date);
2568 FETCH c_prior_prim_car INTO prior_prim_car_rec;
2569 IF c_prior_prim_car%FOUND THEN
2570 L_action_flag := 'R';
2571 ELSE
2572 L_action_flag := 'N';
2573 END IF;
2574 --
2575 hr_utility.set_location(l_proc, 70);
2576 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2577 --
2578 CLOSE c_prior_prim_car;
2579 ELSE
2580 -- Not the first car of the employee
2581 -- report this as new car
2582 L_action_flag := 'N';
2583 END IF;
2584 --
2588 ELSE
2585 hr_utility.set_location(l_proc, 80);
2586 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2587 --
2589 -- Not a primary car therefore mark
2590 -- it as new car allocation action
2591 l_action_flag := 'N';
2592 END IF;
2593 --
2594 hr_utility.set_location(l_proc, 90);
2595 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2596 --
2597 IF L_action_flag <> 'X' THEN
2598 -- Check whether this allocation action
2599 -- has been already archived
2600 l_archived_flag := 'Y';
2601 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2602 alc_rec.vehicle_allocation_id,
2603 alc_dates_rec.min_start_date,
2604 l_action_flag);
2605 --
2606 hr_utility.set_location(l_proc, 100);
2607 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2608 --
2609 IF l_archived_flag = 'N' THEN
2610 -- Action has not been archived already
2611 -- therefore create assignment action
2612
2613 --Assignment action not required for
2614 --replaced car as per new requirements of V3
2615
2616 /* Commented below condition for bug 10312065 */
2617 /* Assignment Action is needed for Replacement car from V4 */
2618 -- IF L_action_flag <> 'R'
2619 -- THEN
2620 l_create_assact_flag := 'Y';
2621 -- END IF;
2622
2623 END IF;
2624 END IF;
2625 END IF;
2626 --
2627 L_action_flag := 'X';
2628 l_archived_flag := 'Y';
2629 --
2630 --
2631 IF (alc_dates_rec.max_end_date BETWEEN g_start_date AND g_end_Date) AND
2632 (alc_dates_rec.max_end_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
2633 --
2634 IF alc_rec.usage_type = 'P' THEN
2635 ---------------------------------------------
2636 -- It's a primary car for the assignment --
2637 -- therefore check whether it's the first --
2638 -- car of the employee --
2639 ---------------------------------------------
2640 -- Get first assignment of this person
2641 -- in the tax_ref
2642 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
2643 FETCH c_first_asg INTO first_asg_rec;
2644 CLOSE c_first_asg;
2645 --
2646 hr_utility.set_location(l_proc, 50);
2647 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2648 -- Get primary car allocation of the
2649 -- first assignment of this person
2650 -- in this tax ref
2651 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
2652 FETCH c_primary_car INTO primary_car_rec;
2653 CLOSE c_primary_car;
2654 --
2655 hr_utility.set_location(l_proc, 60);
2656 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2657 --
2658 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2659 --
2660 OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
2661 FETCH c_next_prim_car INTO next_prim_car_rec;
2662 IF c_next_prim_car%FOUND THEN
2663 -- There is a replacement action to
2664 -- Report this car therefore
2665 -- No need to archive this action
2666 NULL;
2667 ELSE
2668 -- This is a withdrawal action
2669 L_action_flag := 'W';
2670 END IF;
2671 --
2672 hr_utility.set_location(l_proc, 70);
2673 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2674 --
2675 CLOSE c_next_prim_car;
2676 ELSE
2677 -- Not the first car of the employee
2678 -- report this as withdrawal car
2679 L_action_flag := 'W';
2680 END IF;
2681 --
2682 --
2683 hr_utility.set_location(l_proc, 80);
2684 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2685 --
2686 ELSE
2687 -- Not a Primary car therefore mark
2688 -- it as withdrawal car action
2689 l_action_flag := 'W';
2690 END IF;
2691 --
2692 hr_utility.set_location(l_proc, 90);
2693 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2694 --
2695 IF L_action_flag <> 'X' THEN
2696 -- Check whether this allocation action
2697 -- has been already archived
2698 l_archived_flag := 'Y';
2699 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2700 alc_rec.vehicle_allocation_id,
2701 alc_dates_rec.min_start_date,
2702 l_action_flag);
2706 --
2703 --
2704 hr_utility.set_location(l_proc, 100);
2705 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2707 IF l_archived_flag = 'N' THEN
2708 -- Action has not been archived already
2709 -- therefore create assignment action
2710 l_create_assact_flag := 'Y';
2711 END IF;
2712 END IF;
2713 END IF;
2714 -- Check if fuel type has changed within
2715 -- the date range.
2716 FOR vehicle_changes_rec IN c_vehicle_changes_v4(alc_rec.vehicle_repository_id) LOOP
2717 -- Fuel type has changed therefore make sure
2718 -- this change occured after the vehicle was
2719 -- allocated to this assignment
2720 --
2721 hr_utility.set_location(l_proc, 110);
2722 hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
2723 hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
2724 --
2725 IF (vehicle_changes_rec.effective_start_date
2726 BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
2727 (vehicle_changes_rec.effective_start_date
2728 BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
2729 --
2730 -- Check if this fuel change has been
2731 -- already archived
2732 l_archived_flag := 'Y';
2733 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2734 alc_rec.vehicle_allocation_id,
2735 vehicle_changes_rec.effective_start_date,
2736 'F');
2737 --
2738 hr_utility.set_location(l_proc, 120);
2739 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2740 --
2741 IF l_archived_flag = 'N' THEN
2742 -- Action has not been archived already
2743 -- therefore create assignment action
2744 l_create_assact_flag := 'Y';
2745 --
2746 END IF;
2747 END IF;
2748 END LOOP;
2749 --
2750 hr_utility.set_location(l_proc, 130);
2751 --
2752 --
2753 L_action_flag := 'X';
2754 l_archived_flag := 'Y';
2755 --
2756 ----------------------------------------------------
2757 -- If tax ref has changed within the given date --
2758 -- range then check whether it should be reported --
2759 -- as a new allocation or withdrawal --
2760 ----------------------------------------------------
2761 IF (asg_rec.asg_min_start_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
2762 AND (asg_rec.asg_min_start_date BETWEEN g_start_Date and g_end_Date) THEN
2763 -- Assignment has started on this payroll or tax ref during the
2764 -- input date range and this car allocation was active on the
2765 -- start date therefore check whether it is a transfer
2766 -- from another tax ref
2767 hr_utility.set_location(l_proc, 160);
2768 l_previous_tax_ref := NULL;
2769 --
2770 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
2771 FETCH c_tax_ref INTO l_previous_tax_ref;
2772 CLOSE c_tax_ref;
2773 --
2774 hr_utility.set_location(l_proc, 170);
2775 hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
2776 --
2777 IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
2778 -- This assignment has a different prior tax ref
2779 -- therefore it should be reported as a new car
2780 -- allocation on this EDI message.
2781 hr_utility.set_location(l_proc, 180);
2782 l_action_flag := 'N';
2783 --
2784 -- Check whether this allocation action
2785 -- has been already archived
2786 l_archived_flag := 'Y';
2787 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2788 alc_rec.vehicle_allocation_id,
2789 asg_rec.asg_min_start_date,
2790 l_action_flag);
2791 --
2792 hr_utility.set_location(l_proc, 190);
2793 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2794 --
2795 IF l_archived_flag = 'N' THEN
2796 -- Action has not been archived already
2797 -- therefore create assignment action
2798 l_create_assact_flag := 'Y';
2799 END IF;
2800 --
2801 END IF;
2802 END IF;
2803 --
2804 L_action_flag := 'X';
2805 l_archived_flag := 'Y';
2806 --
2807 IF (asg_rec.asg_max_end_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
2808 AND (asg_rec.asg_max_end_date BETWEEN g_start_Date and g_end_Date) THEN
2809 -- Assignment has ended on this payroll or tax ref during the
2810 -- input date range and this car allocation was active on the
2811 -- end date therefore check whether it is a transfer
2812 -- to another tax ref
2816 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
2813 hr_utility.set_location(l_proc, 200);
2814 l_next_tax_ref := NULL;
2815 --
2817 FETCH c_tax_ref INTO l_next_tax_ref;
2818 CLOSE c_tax_ref;
2819 --
2820 hr_utility.set_location(l_proc, 210);
2821 hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
2822 --
2823 IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
2824 -- This assignment has a different tax ref after end date
2825 -- therefore it should be reported as allocation withdrawal
2826 -- on this EDI message.
2827 hr_utility.set_location(l_proc, 220);
2828 l_action_flag := 'W';
2829 --
2830 -- Check whether this allocation action
2831 -- has been already archived
2832 l_archived_flag := 'Y';
2833 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
2834 alc_rec.vehicle_allocation_id,
2835 asg_rec.asg_max_end_date,
2836 l_action_flag);
2837 --
2838 hr_utility.set_location(l_proc, 230);
2839 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2840 --
2841 IF l_archived_flag = 'N' THEN
2842 -- Action has not been archived already
2843 -- therefore create assignment action
2844 l_create_assact_flag := 'Y';
2845 END IF;
2846 --
2847 END IF;
2848 END IF;
2849 --
2850 hr_utility.set_location(l_proc, 240);
2851 --
2852 hr_utility.set_location(l_proc, 250);
2853 END LOOP;
2854 --
2855 hr_utility.set_location(l_proc, 260);
2856 --
2857 -- If flag is set then create action
2858 IF l_create_assact_flag = 'Y' THEN
2859 --
2860 SELECT pay_assignment_actions_s.nextval
2861 INTO l_actid
2862 FROM dual;
2863
2864 hr_utility.set_location(l_proc, 270);
2865 hr_utility.trace(l_proc||': l_actid='||l_actid);
2866 hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
2867 hr_utility.trace(l_proc||': pactid='||pactid);
2868 hr_utility.trace(l_proc||': chunk='||chunk);
2869 --
2870 hr_nonrun_asact.insact(l_actid,
2871 asg_rec.assignment_id,
2872 pactid,
2873 chunk, NULL);
2874 --
2875 hr_utility.set_location(l_proc, 280);
2876 --
2877 END IF;
2878 END LOOP;
2879 --
2880 hr_utility.set_location('Leaving: '||l_proc,290);
2881 -- hr_utility.trace_off;
2882 EXCEPTION
2883 WHEN OTHERS THEN
2884 hr_utility.set_location('Leaving: '||l_proc,300);
2885 -- hr_utility.trace_off;
2886 raise;
2887 END Create_asg_act_v5;
2888
2889
2890 -----------------------------------------------------
2891 -- PROCEDURE ARCHIVE_ALLOCATION_ACTION --
2892 -----------------------------------------------------
2893 PROCEDURE archive_allocation_action(
2894 p_assignment_id IN NUMBER,
2895 p_asg_act_id IN NUMBER,
2896 p_vehicle_allocation_id IN NUMBER,
2897 p_vehicle_repository_id IN NUMBER,
2898 p_action_flag IN VARCHAR2,
2899 p_fuel_type_change_date IN DATE DEFAULT NULL,
2900 p_allocation_start_date IN DATE,
2901 p_allocation_end_date IN DATE,
2902 p_version_type IN VARCHAR2 DEFAULT NULL) IS -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
2903 --
2904 l_eff_date DATE;
2905 l_tax_year_start DATE;
2906 l_date_first_avail PAY_ACTION_INFORMATION.ACTION_INFORMATION14%TYPE;
2907 l_second_car_flag PAY_ACTION_INFORMATION.ACTION_INFORMATION15%TYPE;
2908 --
2909 CURSOR c_person(p_assignment_id IN NUMBER) IS
2910 SELECT p.last_name
2911 , p.title
2912 , p.first_name
2913 , p.middle_names
2914 , p.person_id
2915 , p.national_identifier
2916 , p.date_of_birth --For Bug 6652235
2917 , p.sex --For Bug 6652235
2918 FROM per_people_f p, per_assignments_f a
2919 WHERE a.assignment_id = p_assignment_id
2920 AND l_eff_date between
2921 a.effective_start_date and a.effective_end_date
2922 AND a.person_id = p.person_id
2923 AND l_eff_date between
2924 p.effective_start_date and p.effective_end_date;
2925 --
2926 person_rec c_person%ROWTYPE;
2927 --
2928 CURSOR c_addr(p_person_id IN NUMBER) IS
2929 SELECT addr.address_line1,
2930 addr.address_line2,
2931 addr.address_line3,
2932 addr.town_or_city,
2933 substr(hr_general.decode_lookup('GB_COUNTY',
2934 addr.region_1), 1, 35) region_1,
2935 addr.country,
2936 addr.postal_code
2937 FROM per_addresses addr
2938 WHERE addr.person_id = p_person_id
2939 AND addr.primary_flag = 'Y'
2940 AND l_eff_date BETWEEN addr.date_from
2941 AND nvl(addr.date_to, fnd_date.canonical_to_date('4712/12/31'));
2942 --
2943 addr_rec c_addr%ROWTYPE;
2944 --
2945 CURSOR c_alloc IS
2946 SELECT capital_contribution,
2947 private_contribution,
2951 AND l_eff_date between effective_start_date
2948 fuel_benefit
2949 FROM pqp_vehicle_allocations_f
2950 WHERE vehicle_allocation_id = p_vehicle_allocation_id
2952 and effective_end_Date;
2953 --
2954 alloc_rec c_alloc%ROWTYPE;
2955 --
2956 CURSOR c_car IS
2957 SELECT registration_number,
2958 vehicle_type,
2959 vehicle_id_number,
2960 make,
2961 model,
2962 initial_registration,
2963 last_registration_renew_date,
2964 engine_capacity_in_cc,
2965 -- Included for the EOY Changes. Bug : 11075296
2966 case p_version_type
2967 WHEN 'P46_CAR_V4' then
2968 decode(h1.description,'B','A'
2969 ,'C','A'
2970 ,'D','D'
2971 ,'L','D'
2972 ,'E','E'
2973 ,'G','A'
2974 ,'H','A'
2975 ,'P','A')
2976
2977 WHEN 'P46_CAR_V5' then
2978 decode(h1.description,'B','A'
2979 ,'C','A'
2980 ,'D','D'
2981 ,'L','D'
2982 ,'E','E'
2983 ,'G','A'
2984 ,'H','A'
2985 ,'P','A')
2986
2987 else
2988 h1.description
2989 end as fuel_type,
2990 currency_code,
2991 list_price ,
2992 accessory_value_at_startdate,
2993 accessory_value_added_later,
2994 market_value_classic_car,
2995 fiscal_ratings,
2996 fiscal_ratings_uom,
2997 shared_vehicle,
2998 vehicle_status,
2999 taxation_method
3000 FROM pqp_vehicle_repository_f,
3001 hr_lookups h1
3002 WHERE vehicle_repository_id = p_vehicle_repository_id
3003 AND l_eff_date BETWEEN effective_start_date AND effective_end_Date
3004 AND fuel_type = h1.lookup_code
3005 AND h1.lookup_type = 'PQP_FUEL_TYPE'
3006 AND h1.enabled_flag = 'Y'
3007 AND trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1));
3008 --
3009 car_rec c_car%ROWTYPE;
3010 --
3011
3012 -- Added the below cursor for the bug 9354919
3013 CURSOR c_interval_of_payment IS
3014 SELECT val_information10
3015 FROM pqp_vehicle_allocations_f
3016 WHERE vehicle_allocation_id = p_vehicle_allocation_id
3017 AND l_eff_date between effective_start_date
3018 and effective_end_Date;
3019
3020 l_action_info_id PAY_ACTION_INFORMATION.ACTION_INFORMATION_ID%TYPE;
3021 l_ovn PAY_ACTION_INFORMATION.OBJECT_VERSION_NUMBER%TYPE;
3022 l_proc CONSTANT VARCHAR2(50):= g_package||'archive_allocation_action';
3023 l_interval_of_payment VARCHAR2(1); -- Added the for the bug 9354919
3024 --
3025 BEGIN
3026 hr_utility.set_location('Entering '||l_proc, 10);
3027 hr_utility.trace('p_assignment_id='||p_assignment_id);
3028 hr_utility.trace('p_asg_act_id='||p_asg_act_id);
3029 hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
3030 hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
3031 hr_utility.trace('p_action_flag='||p_action_flag);
3032 hr_utility.trace('p_fuel_type_change_date='||fnd_date.date_to_displaydate(p_fuel_type_change_date));
3033 hr_utility.trace('p_allocation_start_date='||fnd_date.date_to_displaydate(p_allocation_start_date));
3034 hr_utility.trace('p_allocation_end_date='||fnd_date.date_to_displaydate(p_allocation_end_date));
3035 -- Get data as of the action date within
3036 -- the date range, effective date is:
3037 IF p_action_flag = 'F' THEN
3038 -- Get data as of the fuel type change date
3039 l_eff_date := p_fuel_type_Change_date;
3040 ELSIF p_action_flag in ('N', 'R') THEN
3041 l_eff_date := p_allocation_start_date;
3042 ELSIF p_action_flag = 'W' THEN
3043 l_eff_date := p_allocation_end_date;
3044 END IF;
3045 hr_utility.set_location(l_proc, 20);
3046 hr_utility.trace('l_eff_date='||fnd_date.date_to_displaydate(l_eff_date));
3047 --
3048 -- Get person details
3049 OPEN c_person(p_assignment_id);
3050 FETCH c_person INTO person_rec;
3051 CLOSE c_person;
3052 --
3053 hr_utility.set_location(l_proc, 30);
3054 -- Get Address Details
3055 OPEN c_addr(person_rec.person_id);
3056 FETCH c_addr INTO addr_rec;
3057 CLOSE c_addr;
3058 --
3059 hr_utility.set_location(l_proc, 40);
3060 -- Get Allocation details
3061 OPEN c_alloc;
3062 FETCH c_alloc INTO alloc_rec;
3063 CLOSE c_alloc;
3064 --
3065 hr_utility.set_location(l_proc, 50);
3066 -- Get Car details
3067 OPEN c_car;
3068 FETCH c_car INTO car_rec;
3069 CLOSE c_car;
3070
3071 -- Modification for the bug 9354919 starts here
3072 OPEN c_interval_of_payment;
3073 FETCH c_interval_of_payment INTO l_interval_of_payment;
3074 CLOSE c_interval_of_payment;
3075 -- Modification for the bug 9354919 ends here
3076
3077 --
3078 hr_utility.set_location(l_proc, 60);
3079 ---------------------------------------------
3080 -- Archive Person and Address details --
3081 ---------------------------------------------
3082 hr_utility.trace('Person ID : ' || person_rec.person_id);
3083 hr_utility.trace('Last Name : ' || person_rec.last_name);
3084 hr_utility.trace('First Nme : ' || person_rec.first_name);
3085 hr_utility.trace('Title : ' || person_rec.title);
3086 hr_utility.trace('NINO : ' || person_rec.national_identifier);
3087 hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
3088 hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
3092 hr_utility.trace('Postal : ' || addr_rec.postal_code);
3089 hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
3090 hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
3091 hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
3093 hr_utility.trace('DOB : ' || fnd_date.date_to_canonical(person_rec.date_of_birth)); --For Bug 6652235
3094 hr_utility.trace('Gender : ' || person_rec.sex); --For Bug 6652235
3095
3096 pay_action_information_api.create_action_information (
3097 p_action_information_id => l_action_info_id
3098 , p_action_context_id => p_asg_act_id
3099 , p_action_context_type => 'AAP'
3100 , p_object_version_number => l_ovn
3101 , p_assignment_id => p_assignment_id
3102 , p_effective_date => g_effective_date
3103 , p_source_id => NULL
3104 , p_source_text => NULL
3105 , p_action_information_category => 'GB P46 CAR EDI EMPLOYEE DETAIL'
3106 , p_action_information1 => person_rec.person_id
3107 , p_action_information2 => upper(person_rec.last_name)
3108 , p_action_information3 => upper(person_rec.first_name)
3109 , p_action_information4 => upper(person_rec.title)
3110 , p_action_information5 => nvl(person_rec.national_identifier, 'NONE')
3111 , p_action_information6 => upper(addr_rec.address_line1)
3112 , p_action_information7 => upper(addr_rec.address_line2)
3113 , p_action_information8 => upper(addr_rec.address_line3)
3114 , p_action_information9 => upper(addr_rec.town_or_city)
3115 , p_action_information10 => upper(addr_rec.region_1)
3116 , p_action_information11 => upper(addr_rec.postal_code)
3117 , p_action_information12 => fnd_date.date_to_canonical(person_rec.date_of_birth) --For Bug 6652235
3118 , p_action_information13 => upper(person_rec.sex)); --For Bug 6652235
3119 --
3120 hr_utility.set_location(l_proc, 70);
3121 ---------------------------------------------
3122 -- Archive Vehicle Allocation Details --
3123 ---------------------------------------------
3124 IF g_effective_date <
3125 fnd_date.canonical_to_date(
3126 to_char(g_effective_date, 'YYYY')||'04/06') THEN
3127 -- Effective Date between 1st Jan
3128 -- to 5th Apr of the calendar year
3129 l_tax_year_start := fnd_date.canonical_to_date(
3130 to_char(to_number(to_char(g_effective_date, 'YYYY')) -1)
3131 ||'04/06');
3132 ELSE
3133 -- Effective Date between 06th Apr
3134 -- to 31st Dec of the calendar year
3135 l_tax_year_start := fnd_date.canonical_to_date(
3136 to_char(g_effective_date, 'YYYY')||'04/06');
3137 END IF;
3138 --
3139 hr_utility.set_location(l_proc, 80);
3140 --
3141 IF p_allocation_start_date >= l_tax_year_start THEN
3142 -- Car was first allocated in this tax year
3143 -- Archive Date Car First Available
3144 l_date_first_avail := fnd_date.date_to_canonical(p_allocation_start_date);
3145 ELSE
3146 l_date_first_avail := NULL;
3147 END IF;
3148 --
3149 hr_utility.set_location(l_proc, 90);
3150 hr_utility.trace('l_date_first_avail='||l_date_first_avail);
3151 --
3152 ---------------------------------------------
3153 -- Check whether it's the primary car of --
3154 -- the employee --
3155 ---------------------------------------------
3156 -- Get first assignment of this person
3157 -- in the tax_ref
3158 OPEN c_first_asg(p_assignment_id, l_eff_date);
3159 FETCH c_first_asg INTO first_asg_rec;
3160 CLOSE c_first_asg;
3161 --
3162 hr_utility.set_location(l_proc, 100);
3163 --
3164 -- Get primary car allocation of the
3165 -- first assignment of this person
3166 -- in this tax ref
3167 OPEN c_primary_car(first_asg_rec.assignment_id, l_eff_date);
3168 FETCH c_primary_car INTO primary_car_rec;
3169 CLOSE c_primary_car;
3170 --
3171 hr_utility.set_location(l_proc, 110);
3172 --
3173 IF primary_car_rec.vehicle_allocation_id
3174 = p_vehicle_allocation_id THEN
3175 -- This is the primary car allocation
3176 -- of this employee in this tax ref
3177 l_second_car_flag := 'N';
3178 ELSE
3179 -- This is not primary car allocation
3180 -- therefore mark it as a second car.
3181 l_second_car_flag := 'Y';
3182 END IF;
3183 --
3184 hr_utility.set_location(l_proc, 120);
3185 hr_utility.trace('l_second_car_flag='||l_second_car_flag);
3186 --
3187 -- To indicate the Fuel Type change date in EDI File. Also checked
3188 -- that this is not Second/Further car.
3189 IF NOT(p_fuel_type_Change_date is NULL) AND p_action_flag = 'F' THEN
3190 l_date_first_avail := fnd_date.date_to_canonical(p_fuel_type_Change_date);
3191 hr_utility.trace('p_fuel_type_change_date='||p_fuel_type_Change_date);
3192 END IF;
3193
3194
3195 IF p_action_flag = 'N' THEN
3196 hr_utility.set_location(l_proc, 130);
3197 --
3198 pay_action_information_api.create_action_information (
3199 p_action_information_id => l_action_info_id
3200 , p_action_context_id => p_asg_act_id
3201 , p_action_context_type => 'AAP'
3202 , p_object_version_number => l_ovn
3203 , p_assignment_id => p_assignment_id
3204 , p_effective_date => g_effective_date
3205 , p_source_id => NULL
3206 , p_source_text => NULL
3207 , p_action_information_category => 'GB P46 CAR EDI ALLOCATION'
3211 , p_action_information4 => fnd_date.date_to_canonical(p_allocation_end_date)
3208 , p_action_information1 => p_action_flag
3209 , p_action_information2 => p_vehicle_allocation_id
3210 , p_action_information3 => fnd_date.date_to_canonical(p_allocation_start_date)
3212 , p_action_information5 => p_vehicle_repository_id
3213 , p_action_information6 => to_char(nvl(car_rec.list_price,0)*100)
3214 , p_action_information7 => to_char(nvl(car_rec.accessory_value_at_startdate,0)*100)
3215 , p_action_information8 => to_char(nvl(alloc_rec.capital_contribution,0)*100)
3216 , p_action_information9 => to_char(nvl(alloc_rec.private_contribution,0)*100)
3217 , p_action_information10 => car_rec.fuel_type
3218 , p_action_information11 => car_rec.fiscal_ratings
3219 , p_action_information12 => alloc_rec.fuel_benefit
3220 , p_action_information13 => fnd_date.date_to_canonical(car_rec.initial_registration)
3221 , p_action_information14 => l_date_first_avail
3222 , p_action_information15 => l_second_car_flag
3223 , p_action_information16 => upper(car_rec.make || ' ' || car_rec.model)
3224 , p_action_information17 => to_char(car_rec.engine_capacity_in_cc)
3225 , p_action_information22 => nvl(l_interval_of_payment,'Y')); -- Added for the bug 9354919
3226 --
3227 hr_utility.set_location(l_proc, 140);
3228 ELSIF p_action_flag = 'R' THEN
3229 hr_utility.set_location(l_proc, 150);
3230 --
3231 pay_action_information_api.create_action_information (
3232 p_action_information_id => l_action_info_id
3233 , p_action_context_id => p_asg_act_id
3234 , p_action_context_type => 'AAP'
3235 , p_object_version_number => l_ovn
3236 , p_assignment_id => p_assignment_id
3237 , p_effective_date => g_effective_date
3238 , p_source_id => NULL
3239 , p_source_text => NULL
3240 , p_action_information_category => 'GB P46 CAR EDI ALLOCATION'
3241 , p_action_information1 => p_action_flag
3242 , p_action_information2 => p_vehicle_allocation_id
3243 , p_action_information3 => fnd_date.date_to_canonical(p_allocation_start_date)
3244 , p_action_information4 => fnd_date.date_to_canonical(p_allocation_end_date)
3245 , p_action_information5 => p_vehicle_repository_id
3246 , p_action_information6 => to_char(nvl(car_rec.list_price,0)*100)
3247 , p_action_information7 => to_char(nvl(car_rec.accessory_value_at_startdate,0)*100)
3248 , p_action_information8 => to_char(nvl(alloc_rec.capital_contribution,0)*100)
3249 , p_action_information9 => to_char(nvl(alloc_rec.private_contribution,0)*100)
3250 , p_action_information10 => car_rec.fuel_type
3251 , p_action_information11 => car_rec.fiscal_ratings
3252 , p_action_information12 => alloc_rec.fuel_benefit
3253 , p_action_information13 => fnd_date.date_to_canonical(car_rec.initial_registration)
3254 , p_action_information14 => l_date_first_avail
3255 , p_action_information15 => l_second_car_flag
3256 , p_action_information16 => upper(car_rec.make || ' ' || car_rec.model)
3257 , p_action_information17 => to_char(car_rec.engine_capacity_in_cc)
3258 , p_action_information18 => upper(ltrim(rtrim(prior_prim_car_rec.make)) || ' ' || ltrim(rtrim(prior_prim_car_rec.model)))
3259 , p_action_information19 => to_char(prior_prim_car_rec.engine_capacity_in_cc)
3260 , p_action_information20 => to_char(prior_prim_car_rec.vehicle_allocation_id)
3261 , p_action_information21 => fnd_date.date_to_canonical(prior_prim_car_rec.effective_end_date)
3262 , p_action_information22 => nvl(l_interval_of_payment,'Y') -- Added for the bug 9354919
3263 );
3264 --
3265 hr_utility.set_location(l_proc, 160);
3266 ELSIF p_action_flag = 'W' THEN
3267 hr_utility.set_location(l_proc, 170);
3268 --
3269 pay_action_information_api.create_action_information (
3270 p_action_information_id => l_action_info_id
3271 , p_action_context_id => p_asg_act_id
3272 , p_action_context_type => 'AAP'
3273 , p_object_version_number => l_ovn
3274 , p_assignment_id => p_assignment_id
3275 , p_effective_date => g_effective_date
3276 , p_source_id => NULL
3277 , p_source_text => NULL
3278 , p_action_information_category => 'GB P46 CAR EDI ALLOCATION'
3279 , p_action_information1 => p_action_flag
3280 , p_action_information2 => p_vehicle_allocation_id
3281 , p_action_information3 => fnd_date.date_to_canonical(p_allocation_start_date)
3282 , p_action_information4 => fnd_date.date_to_canonical(p_allocation_end_date)
3283 , p_action_information5 => p_vehicle_repository_id
3284 , p_action_information6 => to_char(nvl(car_rec.list_price,0)*100)
3285 , p_action_information7 => to_char(nvl(car_rec.accessory_value_at_startdate,0)*100)
3286 , p_action_information8 => to_char(nvl(alloc_rec.capital_contribution,0)*100)
3287 , p_action_information9 => to_char(nvl(alloc_rec.private_contribution,0)*100)
3288 , p_action_information10 => car_rec.fuel_type
3289 , p_action_information11 => car_rec.fiscal_ratings
3290 , p_action_information12 => alloc_rec.fuel_benefit
3291 , p_action_information13 => fnd_date.date_to_canonical(car_rec.initial_registration)
3295 , p_action_information17 => to_char(car_rec.engine_capacity_in_cc)
3292 , p_action_information14 => NULL
3293 , p_action_information15 => NULL
3294 , p_action_information16 => upper(car_rec.make || ' ' || car_rec.model)
3296 , p_action_information22 => nvl(l_interval_of_payment,'Y') -- Added for the bug 9354919
3297 );
3298 --
3299 hr_utility.set_location(l_proc, 180);
3300 ELSIF p_action_flag = 'F' THEN
3301 hr_utility.set_location(l_proc, 190);
3302 --
3303 pay_action_information_api.create_action_information (
3304 p_action_information_id => l_action_info_id
3305 , p_action_context_id => p_asg_act_id
3306 , p_action_context_type => 'AAP'
3307 , p_object_version_number => l_ovn
3308 , p_assignment_id => p_assignment_id
3309 , p_effective_date => g_effective_date
3310 , p_source_id => NULL
3311 , p_source_text => NULL
3312 , p_action_information_category => 'GB P46 CAR EDI ALLOCATION'
3313 , p_action_information1 => p_action_flag
3314 , p_action_information2 => p_vehicle_allocation_id
3315 , p_action_information3 => fnd_date.date_to_canonical(p_fuel_type_change_Date)
3316 , p_action_information4 => fnd_date.date_to_canonical(p_allocation_end_date)
3317 , p_action_information5 => p_vehicle_repository_id
3318 , p_action_information6 => to_char(nvl(car_rec.list_price,0)*100)
3319 , p_action_information7 => to_char(nvl(car_rec.accessory_value_at_startdate,0)*100)
3320 , p_action_information8 => to_char(nvl(alloc_rec.capital_contribution,0)*100)
3321 , p_action_information9 => to_char(nvl(alloc_rec.private_contribution,0)*100)
3322 , p_action_information10 => car_rec.fuel_type
3323 , p_action_information11 => car_rec.fiscal_ratings
3324 , p_action_information12 => alloc_rec.fuel_benefit
3325 , p_action_information13 => fnd_date.date_to_canonical(car_rec.initial_registration)
3326 , p_action_information14 => l_date_first_avail
3327 , p_action_information15 => l_second_car_flag
3328 , p_action_information16 => upper(car_rec.make || ' ' || car_rec.model)
3329 , p_action_information17 => to_char(car_rec.engine_capacity_in_cc)
3330 , p_action_information18 => upper(car_rec.make || ' ' || car_rec.model)
3331 , p_action_information19 => to_char(car_rec.engine_capacity_in_cc)
3332 , p_action_information20 => to_char(p_vehicle_allocation_id)
3333 , p_action_information21 => fnd_date.date_to_canonical(p_fuel_type_change_date-1)
3334 , p_action_information22 => nvl(l_interval_of_payment,'Y') -- Added for the bug 9354919
3335 );
3336 --
3337 hr_utility.set_location(l_proc, 200);
3338 END IF;
3339 --
3340 hr_utility.set_location('Leaving '||l_proc, 300);
3341 END archive_allocation_action;
3342
3343
3344
3345 --For Bug 6652235
3346 -----------------------------------------------------
3347 -- PROCEDURE ARCHIVE_CODE_V2 --
3348 -----------------------------------------------------
3349 PROCEDURE archive_code_v2(p_assactid IN NUMBER,
3350 p_effective_date IN DATE) IS
3351 --
3352 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code_v2';
3353 --
3354 --
3355 CURSOR c_asg IS
3356 SELECT asg.assignment_id,
3357 min(asg.effective_start_date) asg_min_start_date,
3358 max(asg.effective_end_date) asg_max_end_date
3359 FROM hr_soft_coding_keyflex flex,
3360 per_all_assignments_f asg,
3361 Pay_payrolls_f ppf,
3362 pay_assignment_actions act
3363 WHERE act.assignment_action_id = p_assactid
3364 AND act.assignment_id = asg.assignment_id
3365 AND asg.payroll_id +0 = nvl(g_payroll_id,asg.payroll_id)
3366 AND asg.payroll_id = ppf.payroll_id
3367 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
3368 AND flex.segment1 = g_tax_ref
3369 AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
3370 OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
3371 OR (asg.effective_start_date BETWEEN g_start_date AND g_end_Date
3372 AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
3373 GROUP by asg.assignment_id;
3374 --
3375 l_action_flag VARCHAR2(1);
3376 l_archived_flag VARCHAR2(1);
3377 --
3378 BEGIN
3379 --hr_utility.trace_on(null, 'KTHAMPAN');
3380 hr_utility.set_location('Entering '||l_proc, 10);
3381 --
3382 For asg_rec IN c_asg
3383 LOOP
3384 -- Loop through all assignments
3385 hr_utility.set_location(l_proc, 20);
3386 hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
3387 hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
3388 hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
3389 --
3390 FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
3391 -- Loop through all vehicle allocations
3392 -- over the date range
3393 hr_utility.set_location(l_proc, 30);
3394 hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
3395 hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
3396 --
3397 L_action_flag := 'X';
3398 l_archived_flag := 'Y';
3399 --
3400 OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
3401 FETCH c_alloc_dates INTO alc_dates_rec;
3402 CLOSE c_alloc_dates;
3403 --
3404 hr_utility.set_location(l_proc, 40);
3405 hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
3406 hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
3407 --
3408 -- Check whether vehicle was allocated or
3409 -- Withdrawn within the date range also
3410 -- check that this allocation or withdrawal
3411 -- happened when assignment was with input tax ref
3412 IF (alc_dates_rec.min_start_date BETWEEN g_start_date AND g_end_Date) AND
3413 (alc_dates_rec.min_start_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
3414 --
3415 -- This is a New Car or replacement action
3416 IF alc_rec.usage_type = 'P' THEN
3417 ---------------------------------------------
3418 -- It's a primary car for the assignment --
3419 -- therefore check whether it's the first --
3420 -- car of the employee --
3421 ---------------------------------------------
3422 -- Get first assignment of this person
3423 -- in the tax_ref
3424 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
3425 FETCH c_first_asg INTO first_asg_rec;
3426 CLOSE c_first_asg;
3427 --
3428 hr_utility.set_location(l_proc, 50);
3429 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
3430 -- Get primary car allocation of the
3431 -- first assignment of this person
3432 -- in this tax ref
3433 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
3434 FETCH c_primary_car INTO primary_car_rec;
3435 CLOSE c_primary_car;
3436 --
3437 hr_utility.set_location(l_proc, 60);
3438 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
3439 --
3440 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
3441 -- This is the first car allocation
3442 -- of this employee in this tax ref
3443 -- therefore check if it has replaced
3444 -- any prior car
3445 OPEN c_prior_prim_car(asg_rec.assignment_id, Alc_dates_rec.min_start_date);
3446 FETCH c_prior_prim_car INTO prior_prim_car_rec;
3447 IF c_prior_prim_car%FOUND THEN
3448 L_action_flag := 'R';
3449 ELSE
3450 L_action_flag := 'N';
3451 END IF;
3452 --
3453 hr_utility.set_location(l_proc, 70);
3454 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3455 --
3456 CLOSE c_prior_prim_car;
3457 ELSE
3458 -- Not the first car of the employee
3459 -- report this as new car
3460 L_action_flag := 'N';
3461 END IF;
3462 --
3463 hr_utility.set_location(l_proc, 80);
3464 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3465 --
3466 ELSE
3467 -- Not a primary car therefore mark
3468 -- it as new car allocation action
3469 l_action_flag := 'N';
3470 END IF;
3471 --
3472 hr_utility.set_location(l_proc, 90);
3473 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3474 --
3475 IF L_action_flag <> 'X' THEN
3476 -- Check whether this allocation action
3477 -- has been already archived
3478 l_archived_flag := 'Y';
3479 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
3480 alc_rec.vehicle_allocation_id,
3481 alc_dates_rec.min_start_date,
3482 l_action_flag);
3483 --
3484 hr_utility.set_location(l_proc, 100);
3488 -- Action has not been archived already
3485 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
3486 --
3487 IF l_archived_flag = 'N' THEN
3489 -- therefore archive it.
3490 hr_utility.set_location(l_proc, 105);
3491 hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
3492 hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
3493 hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
3494 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3495 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
3496 hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
3497 --
3498 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
3499 p_asg_act_id => p_assactid,
3500 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
3501 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
3502 p_action_flag => l_action_flag,
3503 p_allocation_start_date => alc_dates_rec.min_start_date,
3504 p_allocation_end_date => alc_dates_rec.max_end_date);
3505 END IF;
3506 END IF;
3507 END IF;
3508 --
3509 L_action_flag := 'X';
3510 l_archived_flag := 'Y';
3511 --
3512 IF (alc_dates_rec.max_end_date BETWEEN g_start_date AND g_end_Date) AND
3513 (alc_dates_rec.max_end_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
3514 --
3515 IF alc_rec.usage_type = 'P' THEN
3516 ---------------------------------------------
3517 -- It's a primary car for the assignment --
3518 -- therefore check whether it's the first --
3519 -- car of the employee --
3520 ---------------------------------------------
3521 -- Get first assignment of this person
3522 -- in the tax_ref
3523 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
3524 FETCH c_first_asg INTO first_asg_rec;
3525 CLOSE c_first_asg;
3526 --
3527 hr_utility.set_location(l_proc, 50);
3528 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
3529 -- Get primary car allocation of the
3530 -- first assignment of this person
3531 -- in this tax ref
3532 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
3533 FETCH c_primary_car INTO primary_car_rec;
3534 CLOSE c_primary_car;
3535 --
3536 hr_utility.set_location(l_proc, 60);
3537 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
3538 --
3539 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
3540 --
3541 OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
3542 FETCH c_next_prim_car INTO next_prim_car_rec;
3543 IF c_next_prim_car%FOUND THEN
3544 -- There is a replacement action to
3545 -- Report this car therefore
3546 -- No need to archive this action
3547 NULL;
3548 ELSE
3549 -- This is a withdrawal action
3550 L_action_flag := 'W';
3551 END IF;
3552 --
3553 hr_utility.set_location(l_proc, 70);
3554 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3555 --
3556 CLOSE c_next_prim_car;
3557 ELSE
3558 -- Not the first car of the employee
3559 -- report this as withdrawal car
3560 L_action_flag := 'W';
3561 END IF;
3562 --
3563 --
3564 hr_utility.set_location(l_proc, 80);
3565 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3566 --
3567 ELSE
3568 -- Not a first car therefore mark
3569 -- it as withdrawal car action
3570 l_action_flag := 'W';
3571 END IF;
3572 --
3573 hr_utility.set_location(l_proc, 90);
3574 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3575 --
3576 IF L_action_flag <> 'X' THEN
3577 -- Check whether this allocation action
3578 -- has been already archived
3579 l_archived_flag := 'Y';
3580 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
3581 alc_rec.vehicle_allocation_id,
3582 alc_dates_rec.min_start_date,
3583 l_action_flag);
3584 --
3585 hr_utility.set_location(l_proc, 100);
3586 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
3587 --
3588 IF l_archived_flag = 'N' THEN
3589 -- Action has not been archived already
3590 -- therefore archive it.
3591 hr_utility.set_location(l_proc, 105);
3592 hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
3593 hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
3594 hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
3595 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3596 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
3597 hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
3598 --
3599 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
3600 p_asg_act_id => p_assactid,
3601 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
3602 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
3603 p_action_flag => l_action_flag,
3604 p_allocation_start_date => alc_dates_rec.min_start_date,
3605 p_allocation_end_date => alc_dates_rec.max_end_date);
3606 END IF;
3607 END IF;
3608 END IF;
3609 -- Check if fuel type has changed within
3610 -- the date range.
3611 FOR vehicle_changes_rec IN c_vehicle_changes(alc_rec.vehicle_repository_id) LOOP
3612 -- Fuel type has changed therefore make sure
3613 -- this change occured after the vehicle was
3614 -- allocated to this assignment
3615 --
3616 hr_utility.set_location(l_proc, 110);
3617 hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
3618 hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
3619 --
3620 IF (vehicle_changes_rec.effective_start_date
3621 BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
3625 -- Check if this fuel change has been
3622 (vehicle_changes_rec.effective_start_date
3623 BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) THEN
3624 --
3626 -- already archived
3627 l_archived_flag := 'Y';
3628 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
3629 alc_rec.vehicle_allocation_id,
3630 vehicle_changes_rec.effective_start_date,
3631 'F');
3632 --
3633 hr_utility.set_location(l_proc, 120);
3634 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
3635 --
3636 IF l_archived_flag = 'N' THEN
3637 -- Action has not been archived already
3638 -- therefore archive it.
3639 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
3640 p_asg_act_id => p_assactid,
3641 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
3642 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
3643 p_fuel_type_change_date => vehicle_changes_rec.effective_start_date,
3644 p_action_flag => 'F',
3645 p_allocation_start_date => alc_dates_rec.min_start_date,
3646 p_allocation_end_date => alc_dates_rec.max_end_date);
3647 --
3648 END IF;
3649 END IF;
3650 END LOOP;
3651 --
3652 hr_utility.set_location(l_proc, 130);
3653 --
3654 L_action_flag := 'X';
3655 l_archived_flag := 'Y';
3656 --
3657 ----------------------------------------------------
3658 -- If tax ref has changed within the given date --
3659 -- range then check whether it should be reported --
3660 -- as a new allocation or withdrawal --
3661 ----------------------------------------------------
3662 IF (asg_rec.asg_min_start_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
3663 AND (asg_rec.asg_min_start_date BETWEEN g_start_Date and g_end_Date) THEN
3664 -- Assignment has started on this payroll or tax ref during the
3665 -- input date range and this car allocation was active on the
3666 -- start date therefore check whether it is a transfer
3667 -- from another tax ref
3668 hr_utility.set_location(l_proc, 160);
3669 l_previous_tax_ref := NULL;
3670 --
3671 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
3672 FETCH c_tax_ref INTO l_previous_tax_ref;
3673 CLOSE c_tax_ref;
3674 --
3675 hr_utility.set_location(l_proc, 170);
3676 hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
3677 --
3678 IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
3679 -- This assignment has a different prior tax ref
3680 -- therefore it should be reported as a new car
3681 -- allocation on this EDI message.
3682 hr_utility.set_location(l_proc, 180);
3683 l_action_flag := 'N';
3684 --
3685 -- Check whether this allocation action
3686 -- has been already archived
3687 l_archived_flag := 'Y';
3688 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
3689 alc_rec.vehicle_allocation_id,
3690 asg_rec.asg_min_start_date,
3691 l_action_flag);
3692 --
3693 hr_utility.set_location(l_proc, 190);
3694 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
3695 --
3696 IF l_archived_flag = 'N' THEN
3697 -- Action has not been archived already
3698 -- therefore archive it.
3699 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
3700 p_asg_act_id => p_assactid,
3701 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
3702 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
3703 p_action_flag => l_action_flag,
3704 p_allocation_start_date => asg_rec.asg_min_start_date,
3705 p_allocation_end_date => asg_rec.asg_max_end_date);
3706 END IF;
3707 --
3708 END IF;
3709 END IF;
3710 --
3711 L_action_flag := 'X';
3712 l_archived_flag := 'Y';
3713 --
3714 IF (asg_rec.asg_max_end_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
3715 AND (asg_rec.asg_max_end_date BETWEEN g_start_Date and g_end_Date) THEN
3716 -- Assignment has ended on this payroll or tax ref during the
3717 -- input date range and this car allocation was active on the
3718 -- end date therefore check whether it is a transfer
3719 -- to another tax ref
3720 hr_utility.set_location(l_proc, 200);
3721 l_next_tax_ref := NULL;
3722 --
3723 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
3724 FETCH c_tax_ref INTO l_next_tax_ref;
3725 CLOSE c_tax_ref;
3726 --
3730 IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
3727 hr_utility.set_location(l_proc, 210);
3728 hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
3729 --
3731 -- This assignment has a different tax ref after end date
3732 -- therefore it should be reported as allocation withdrawal
3733 -- on this EDI message.
3734 hr_utility.set_location(l_proc, 220);
3735 l_action_flag := 'W';
3736 --
3737 -- Check whether this allocation action
3738 -- has been already archived
3739 l_archived_flag := 'Y';
3740 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
3741 alc_rec.vehicle_allocation_id,
3742 asg_rec.asg_max_end_date,
3743 l_action_flag);
3744 --
3745 hr_utility.set_location(l_proc, 230);
3746 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
3747 --
3748 IF l_archived_flag = 'N' THEN
3749 -- Action has not been archived already
3750 -- therefore archive it.
3751 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
3752 p_asg_act_id => p_assactid,
3753 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
3754 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
3755 p_action_flag => l_action_flag,
3756 p_allocation_start_date => asg_rec.asg_min_start_date,
3757 p_allocation_end_date => asg_rec.asg_max_end_date);
3758 END IF;
3759 --
3760 END IF;
3761 END IF;
3762 --
3763 hr_utility.set_location(l_proc, 240);
3764 --
3765 hr_utility.set_location(l_proc, 250);
3766 END LOOP;
3767 --
3768 hr_utility.set_location(l_proc, 260);
3769 --
3770 END LOOP;
3771 --
3772 hr_utility.set_location('Leaving: '||l_proc,290);
3773 --hr_utility.trace_off;
3774 EXCEPTION
3775 WHEN OTHERS THEN
3776 hr_utility.set_location('Leaving: '||l_proc,300);
3777 -- hr_utility.trace_off;
3778 raise;
3779 END archive_code_v2;
3780
3781 --For Bug 8986543
3782 -----------------------------------------------------
3783 -- PROCEDURE ARCHIVE_CODE_V3 --
3784 -----------------------------------------------------
3785 PROCEDURE archive_code_v3(p_assactid IN NUMBER,
3786 p_effective_date IN DATE) IS
3787 --
3788 l_proc CONSTANT VARCHAR2(100):= g_package||'archive_code_v3';
3789 --
3790 --
3791 CURSOR c_asg IS
3792 SELECT asg.assignment_id,
3793 min(asg.effective_start_date) asg_min_start_date,
3794 max(asg.effective_end_date) asg_max_end_date
3795 FROM hr_soft_coding_keyflex flex,
3796 per_all_assignments_f asg,
3797 Pay_payrolls_f ppf,
3798 pay_assignment_actions act
3799 WHERE act.assignment_action_id = p_assactid
3800 AND act.assignment_id = asg.assignment_id
3801 AND asg.payroll_id +0 = nvl(g_payroll_id,asg.payroll_id)
3802 AND asg.payroll_id = ppf.payroll_id
3803 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
3804 AND flex.segment1 = g_tax_ref
3805 AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
3806 OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
3807 OR (asg.effective_start_date BETWEEN g_start_date AND g_end_Date
3808 AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
3809 GROUP by asg.assignment_id;
3810 --
3811 l_action_flag VARCHAR2(1);
3812 l_archived_flag VARCHAR2(1);
3813 --
3814 BEGIN
3815
3816 hr_utility.set_location('Entering '||l_proc, 10);
3817 --
3818 For asg_rec IN c_asg
3819 LOOP
3820 -- Loop through all assignments
3821 hr_utility.set_location(l_proc, 20);
3822 hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
3823 hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
3824 hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
3825 --
3826 FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
3827 -- Loop through all vehicle allocations
3828 -- over the date range
3829 hr_utility.set_location(l_proc, 30);
3830 hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
3831 hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
3832 --
3833 L_action_flag := 'X';
3834 l_archived_flag := 'Y';
3835 --
3836 OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
3837 FETCH c_alloc_dates INTO alc_dates_rec;
3838 CLOSE c_alloc_dates;
3839 --
3840 hr_utility.set_location(l_proc, 40);
3841 hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
3842 hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
3843
3844 -- Moved the code here from below for the bug 10088866
3845 -- Start moving for the bug 10088866
3846 -- Private flag logic
3847 --
3848 --
3849
3850 L_action_flag := 'X';
3851 l_archived_flag := 'Y';
3852 --
3853 FOR private_flag_iterator IN private_flag_arch(alc_rec.vehicle_allocation_id) LOOP
3854 hr_utility.set_location(l_proc, 250);
3855 hr_utility.trace(l_proc||', private_flag_iterator.private_use_flag='||private_flag_iterator.private_use_flag);
3856
3857 L_action_flag := 'X';
3858 l_archived_flag := 'Y';
3859
3860 IF private_flag_iterator.private_use_flag = 'Y' THEN
3861 L_action_flag := 'N';
3862 ELSIF NVL(private_flag_iterator.private_use_flag,'N') = 'N' THEN
3863 L_action_flag := 'W';
3864 END IF;
3865
3866 l_archived_flag := 'Y';
3867 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
3868 alc_rec.vehicle_allocation_id,
3869 private_flag_iterator.effective_start_date,
3870 l_action_flag,
3871 private_flag_iterator.effective_start_date);
3872 --
3873 hr_utility.set_location(l_proc, 260);
3874 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
3875 --
3876 IF l_archived_flag = 'N' THEN
3877 -- Action has not been archived already
3878 -- therefore archive it.
3879
3880 -- if the private_use_flag is set to N -Y or Y- N, the allocation
3881 -- end date will be EOT. If it is EOT, Set it to the Withdrawal Date to
3882 -- avoid the error 'Date Car Withdrawn must not be later than Date of Message'
3883
3884 if private_flag_iterator.effective_end_date = hr_general.END_OF_TIME then
3885 private_flag_iterator.effective_end_date := private_flag_iterator.effective_start_date;
3886 end if;
3887
3888 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
3889 p_asg_act_id => p_assactid,
3890 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
3891 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
3892 p_action_flag => L_action_flag,
3893 p_allocation_start_date => private_flag_iterator.effective_start_date,
3894 p_allocation_end_date => private_flag_iterator.effective_end_date);
3895 END IF;
3896 END LOOP; -- private_flag_iterator
3897
3898 -- End moving for the bug 10088866
3899
3900 --
3901 -- Check whether vehicle was allocated or
3902 -- Withdrawn within the date range also
3903 -- check that this allocation or withdrawal
3904 -- happened when assignment was with input tax ref
3905 IF (alc_dates_rec.min_start_date BETWEEN g_start_date AND g_end_Date) AND
3906 (alc_dates_rec.min_start_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
3907 --
3908 -- This is a New Car or replacement action
3909 IF alc_rec.usage_type = 'P' THEN
3910 ---------------------------------------------
3911 -- It's a primary car for the assignment --
3912 -- therefore check whether it's the first --
3913 -- car of the employee --
3914 ---------------------------------------------
3915 -- Get first assignment of this person
3916 -- in the tax_ref
3917 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
3918 FETCH c_first_asg INTO first_asg_rec;
3919 CLOSE c_first_asg;
3920 --
3921 hr_utility.set_location(l_proc, 50);
3922 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
3923 -- Get primary car allocation of the
3924 -- first assignment of this person
3925 -- in this tax ref
3926 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
3927 FETCH c_primary_car INTO primary_car_rec;
3928 CLOSE c_primary_car;
3929 --
3930 hr_utility.set_location(l_proc, 60);
3931 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
3932 --
3933 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
3934 -- This is the first car allocation
3935 -- of this employee in this tax ref
3936 -- therefore check if it has replaced
3937 -- any prior car
3938 OPEN c_prior_prim_car(asg_rec.assignment_id, Alc_dates_rec.min_start_date);
3939 FETCH c_prior_prim_car INTO prior_prim_car_rec;
3940 IF c_prior_prim_car%FOUND THEN
3941 L_action_flag := 'R';
3942 ELSE
3943 L_action_flag := 'N';
3944 END IF;
3945 --
3946 hr_utility.set_location(l_proc, 70);
3947 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3948 --
3949 CLOSE c_prior_prim_car;
3950 ELSE
3951 -- Not the first car of the employee
3952 -- report this as new car
3953 L_action_flag := 'N';
3954 END IF;
3955 --
3956 hr_utility.set_location(l_proc, 80);
3957 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3958 --
3959 ELSE
3960 -- Not a primary car therefore mark
3961 -- it as new car allocation action
3962 l_action_flag := 'N';
3963 END IF;
3964 --
3965 hr_utility.set_location(l_proc, 90);
3966 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3967 --
3968 IF L_action_flag <> 'X' THEN
3969 -- Check whether this allocation action
3970 -- has been already archived
3971 l_archived_flag := 'Y';
3972 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
3973 alc_rec.vehicle_allocation_id,
3974 alc_dates_rec.min_start_date,
3975 l_action_flag,
3976 alc_rec.effective_start_date); -- Added for the bug 10088866
3977 --
3978 hr_utility.set_location(l_proc, 100);
3979 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
3980 --
3981 IF (l_archived_flag = 'N' AND l_action_flag <> 'R') --No need to archive replaced car as per new requirements of V3
3982 THEN
3983 -- Action has not been archived already
3984 -- therefore archive it.
3985 hr_utility.set_location(l_proc, 105);
3986 hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
3987 hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
3988 hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
3989 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
3990 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
3991 hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
3992 --
3993 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
3994 p_asg_act_id => p_assactid,
3995 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
3996 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
3997 p_action_flag => l_action_flag,
3998 p_allocation_start_date => alc_dates_rec.min_start_date,
3999 p_allocation_end_date => alc_dates_rec.max_end_date);
4000 END IF;
4001 END IF;
4002 END IF;
4003 --
4004 L_action_flag := 'X';
4005 l_archived_flag := 'Y';
4006 --
4007 IF (alc_dates_rec.max_end_date BETWEEN g_start_date AND g_end_Date) AND
4008 (alc_dates_rec.max_end_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4009 --
4010 IF alc_rec.usage_type = 'P' THEN
4011 ---------------------------------------------
4012 -- It's a primary car for the assignment --
4013 -- therefore check whether it's the first --
4014 -- car of the employee --
4015 ---------------------------------------------
4016 -- Get first assignment of this person
4017 -- in the tax_ref
4018 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
4019 FETCH c_first_asg INTO first_asg_rec;
4020 CLOSE c_first_asg;
4021 --
4022 hr_utility.set_location(l_proc, 50);
4023 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
4024 -- Get primary car allocation of the
4025 -- first assignment of this person
4026 -- in this tax ref
4027 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
4028 FETCH c_primary_car INTO primary_car_rec;
4029 CLOSE c_primary_car;
4030 --
4031 hr_utility.set_location(l_proc, 60);
4032 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
4033 --
4034 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
4035 --
4036 OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
4037 FETCH c_next_prim_car INTO next_prim_car_rec;
4038 IF c_next_prim_car%FOUND THEN
4039 -- There is a replacement action to
4040 -- Report this car therefore
4041 -- No need to archive this action
4042 NULL;
4043 ELSE
4044 -- This is a withdrawal action
4045 L_action_flag := 'W';
4046 END IF;
4047 --
4048 hr_utility.set_location(l_proc, 70);
4049 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4050 --
4051 CLOSE c_next_prim_car;
4052 ELSE
4053 -- Not the first car of the employee
4054 -- report this as withdrawal car
4055 L_action_flag := 'W';
4056 END IF;
4057 --
4058 --
4059 hr_utility.set_location(l_proc, 80);
4060 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4061 --
4062 ELSE
4063 -- Not a first car therefore mark
4064 -- it as withdrawal car action
4065 l_action_flag := 'W';
4066 END IF;
4067 --
4068 hr_utility.set_location(l_proc, 90);
4069 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4070 --
4071 IF L_action_flag <> 'X' THEN
4072 -- Check whether this allocation action
4073 -- has been already archived
4074 l_archived_flag := 'Y';
4075 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4076 alc_rec.vehicle_allocation_id,
4077 alc_dates_rec.min_start_date,
4078 l_action_flag,
4079 alc_rec.effective_start_date); -- Added for the bug 10088866
4080 --
4081 hr_utility.set_location(l_proc, 100);
4082 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
4083 --
4084 IF l_archived_flag = 'N' THEN
4085 -- Action has not been archived already
4086 -- therefore archive it.
4087 hr_utility.set_location(l_proc, 105);
4088 hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
4089 hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
4090 hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
4091 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4092 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
4093 hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
4094 --
4095 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4096 p_asg_act_id => p_assactid,
4097 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4098 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4099 p_action_flag => l_action_flag,
4100 p_allocation_start_date => alc_dates_rec.min_start_date,
4101 p_allocation_end_date => alc_dates_rec.max_end_date);
4102 END IF;
4103 END IF;
4104 END IF;
4105 -- Check if fuel type has changed within
4106 -- the date range.
4107 FOR vehicle_changes_rec IN c_vehicle_changes(alc_rec.vehicle_repository_id) LOOP
4108 -- Fuel type has changed therefore make sure
4109 -- this change occured after the vehicle was
4110 -- allocated to this assignment
4111 --
4112 hr_utility.set_location(l_proc, 110);
4113 hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
4114 hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
4115 --
4116 IF (vehicle_changes_rec.effective_start_date
4117 BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
4118 (vehicle_changes_rec.effective_start_date
4119 BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4120 --
4121 -- Check if this fuel change has been
4122 -- already archived
4123 l_archived_flag := 'Y';
4124 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4125 alc_rec.vehicle_allocation_id,
4126 vehicle_changes_rec.effective_start_date,
4127 'F',
4128 alc_rec.effective_start_date); -- Added for the bug 10088866
4129 --
4130 hr_utility.set_location(l_proc, 120);
4131 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
4132 --
4133 IF l_archived_flag = 'N' THEN
4134 -- Action has not been archived already
4135 -- therefore archive it.
4136 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4137 p_asg_act_id => p_assactid,
4138 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4139 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4140 p_fuel_type_change_date => vehicle_changes_rec.effective_start_date,
4141 p_action_flag => 'F',
4142 p_allocation_start_date => alc_dates_rec.min_start_date,
4143 p_allocation_end_date => alc_dates_rec.max_end_date);
4144 --
4145 END IF;
4146 END IF;
4147 END LOOP;
4148 --
4149 hr_utility.set_location(l_proc, 130);
4150 --
4151 L_action_flag := 'X';
4152 l_archived_flag := 'Y';
4153 --
4154 ----------------------------------------------------
4155 -- If tax ref has changed within the given date --
4156 -- range then check whether it should be reported --
4157 -- as a new allocation or withdrawal --
4158 ----------------------------------------------------
4159 IF (asg_rec.asg_min_start_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
4160 AND (asg_rec.asg_min_start_date BETWEEN g_start_Date and g_end_Date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4161 -- Assignment has started on this payroll or tax ref during the
4162 -- input date range and this car allocation was active on the
4163 -- start date therefore check whether it is a transfer
4164 -- from another tax ref
4165 hr_utility.set_location(l_proc, 160);
4166 l_previous_tax_ref := NULL;
4167 --
4168 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
4169 FETCH c_tax_ref INTO l_previous_tax_ref;
4170 CLOSE c_tax_ref;
4171 --
4172 hr_utility.set_location(l_proc, 170);
4173 hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
4174 --
4175 IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
4176 -- This assignment has a different prior tax ref
4177 -- therefore it should be reported as a new car
4178 -- allocation on this EDI message.
4179 hr_utility.set_location(l_proc, 180);
4180 l_action_flag := 'N';
4181 --
4182 -- Check whether this allocation action
4183 -- has been already archived
4184 l_archived_flag := 'Y';
4185 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4186 alc_rec.vehicle_allocation_id,
4187 asg_rec.asg_min_start_date,
4188 l_action_flag,
4189 alc_rec.effective_start_date); -- Added for the bug 10088866
4190 --
4191 hr_utility.set_location(l_proc, 190);
4192 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
4193 --
4194 IF l_archived_flag = 'N' THEN
4195 -- Action has not been archived already
4196 -- therefore archive it.
4197 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4198 p_asg_act_id => p_assactid,
4199 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4200 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4201 p_action_flag => l_action_flag,
4202 p_allocation_start_date => asg_rec.asg_min_start_date,
4203 p_allocation_end_date => asg_rec.asg_max_end_date);
4204 END IF;
4205 --
4206 END IF;
4207 END IF;
4208 --
4209 L_action_flag := 'X';
4210 l_archived_flag := 'Y';
4211 --
4212 IF (asg_rec.asg_max_end_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
4213 AND (asg_rec.asg_max_end_date BETWEEN g_start_Date and g_end_Date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4214 -- Assignment has ended on this payroll or tax ref during the
4215 -- input date range and this car allocation was active on the
4216 -- end date therefore check whether it is a transfer
4217 -- to another tax ref
4218 hr_utility.set_location(l_proc, 200);
4219 l_next_tax_ref := NULL;
4220 --
4221 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
4222 FETCH c_tax_ref INTO l_next_tax_ref;
4223 CLOSE c_tax_ref;
4224 --
4225 hr_utility.set_location(l_proc, 210);
4226 hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
4227 --
4228 IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
4229 -- This assignment has a different tax ref after end date
4230 -- therefore it should be reported as allocation withdrawal
4231 -- on this EDI message.
4232 hr_utility.set_location(l_proc, 220);
4233 l_action_flag := 'W';
4234 --
4235 -- Check whether this allocation action
4236 -- has been already archived
4237 l_archived_flag := 'Y';
4238 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4239 alc_rec.vehicle_allocation_id,
4240 asg_rec.asg_max_end_date,
4241 l_action_flag,
4242 alc_rec.effective_start_date); -- Added for the bug 10088866
4243 --
4244 hr_utility.set_location(l_proc, 230);
4245 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
4246 --
4247 IF l_archived_flag = 'N' THEN
4248 -- Action has not been archived already
4249 -- therefore archive it.
4250 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4251 p_asg_act_id => p_assactid,
4252 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4253 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4254 p_action_flag => l_action_flag,
4255 p_allocation_start_date => asg_rec.asg_min_start_date,
4256 p_allocation_end_date => asg_rec.asg_max_end_date);
4257 END IF;
4258 --
4259 END IF;
4260 END IF;
4261 --
4262 hr_utility.set_location(l_proc, 240);
4263
4264 hr_utility.set_location(l_proc, 390);
4265 END LOOP;
4266 --
4267 hr_utility.set_location(l_proc, 400);
4268 --
4269 END LOOP;
4270 --
4271 hr_utility.set_location('Leaving: '||l_proc,450);
4272 --hr_utility.trace_off;
4273 EXCEPTION
4274 WHEN OTHERS THEN
4275 hr_utility.set_location('Leaving: '||l_proc,300);
4276 -- hr_utility.trace_off;
4277 raise;
4278 END archive_code_v3;
4279
4280 --For Bug 10095492
4281 -----------------------------------------------------
4282 -- PROCEDURE ARCHIVE_CODE_V4 --
4283 -----------------------------------------------------
4284 PROCEDURE archive_code_v4(p_assactid IN NUMBER,
4285 p_effective_date IN DATE) IS
4286 --
4287 l_proc CONSTANT VARCHAR2(100):= g_package||'archive_code_v4';
4288 --
4289 --
4290 CURSOR c_asg IS
4291 SELECT asg.assignment_id,
4292 min(asg.effective_start_date) asg_min_start_date,
4293 max(asg.effective_end_date) asg_max_end_date
4294 FROM hr_soft_coding_keyflex flex,
4295 per_all_assignments_f asg,
4296 Pay_payrolls_f ppf,
4297 pay_assignment_actions act
4298 WHERE act.assignment_action_id = p_assactid
4299 AND act.assignment_id = asg.assignment_id
4300 AND asg.payroll_id +0 = nvl(g_payroll_id,asg.payroll_id)
4301 AND asg.payroll_id = ppf.payroll_id
4302 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
4303 AND flex.segment1 = g_tax_ref
4304 AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4305 OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4306 OR (asg.effective_start_date BETWEEN g_start_date AND g_end_Date
4307 AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
4308 GROUP by asg.assignment_id;
4309 --
4310 l_action_flag VARCHAR2(1);
4311 l_archived_flag VARCHAR2(1);
4312 --
4313 BEGIN
4314
4315 hr_utility.set_location('Entering '||l_proc, 10);
4316 --
4317 For asg_rec IN c_asg
4318 LOOP
4319 -- Loop through all assignments
4320 hr_utility.set_location(l_proc, 20);
4321 hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
4322 hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
4323 hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
4324 --
4325 FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
4326 -- Loop through all vehicle allocations
4327 -- over the date range
4328 hr_utility.set_location(l_proc, 30);
4329 hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
4330 hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
4331 --
4332 L_action_flag := 'X';
4333 l_archived_flag := 'Y';
4334 --
4335 OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
4336 FETCH c_alloc_dates INTO alc_dates_rec;
4337 CLOSE c_alloc_dates;
4338 --
4339 hr_utility.set_location(l_proc, 40);
4340 hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
4341 hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
4342
4343 -- Moved the code here from below for the bug 10088866
4344 -- Start moving for the bug 10088866
4345 -- Private flag logic
4346 --
4347 --
4348
4349 L_action_flag := 'X';
4350 l_archived_flag := 'Y';
4351 --
4352 FOR private_flag_iterator IN private_flag_arch(alc_rec.vehicle_allocation_id) LOOP
4353 hr_utility.set_location(l_proc, 250);
4354 hr_utility.trace(l_proc||', private_flag_iterator.private_use_flag='||private_flag_iterator.private_use_flag);
4355
4356 L_action_flag := 'X';
4357 l_archived_flag := 'Y';
4358
4359 IF private_flag_iterator.private_use_flag = 'Y' THEN
4360 L_action_flag := 'N';
4361 ELSIF NVL(private_flag_iterator.private_use_flag,'N') = 'N' THEN
4362 L_action_flag := 'W';
4363 END IF;
4364
4365 l_archived_flag := 'Y';
4366 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4367 alc_rec.vehicle_allocation_id,
4368 private_flag_iterator.effective_start_date,
4369 l_action_flag,
4370 private_flag_iterator.effective_start_date);
4371 --
4372 hr_utility.set_location(l_proc, 260);
4373 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
4374 --
4375 IF l_archived_flag = 'N' THEN
4376 -- Action has not been archived already
4377 -- therefore archive it.
4378
4379 -- if the private_use_flag is set to N -Y or Y- N, the allocation
4380 -- end date will be EOT. If it is EOT, Set it to the Withdrawal Date to
4381 -- avoid the error 'Date Car Withdrawn must not be later than Date of Message'
4382
4383 if private_flag_iterator.effective_end_date = hr_general.END_OF_TIME then
4384 private_flag_iterator.effective_end_date := private_flag_iterator.effective_start_date;
4385 end if;
4386
4387 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4388 p_asg_act_id => p_assactid,
4389 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4390 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4391 p_action_flag => L_action_flag,
4392 p_allocation_start_date => private_flag_iterator.effective_start_date,
4393 p_allocation_end_date => private_flag_iterator.effective_end_date,
4394 p_version_type => 'P46_CAR_V4'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
4395 END IF;
4396 END LOOP; -- private_flag_iterator
4397
4398 -- End moving for the bug 10088866
4399
4400 --
4401 -- Check whether vehicle was allocated or
4402 -- Withdrawn within the date range also
4403 -- check that this allocation or withdrawal
4404 -- happened when assignment was with input tax ref
4405 IF (alc_dates_rec.min_start_date BETWEEN g_start_date AND g_end_Date) AND
4406 (alc_dates_rec.min_start_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4407 --
4408 -- This is a New Car or replacement action
4409 IF alc_rec.usage_type = 'P' THEN
4410 ---------------------------------------------
4411 -- It's a primary car for the assignment --
4412 -- therefore check whether it's the first --
4413 -- car of the employee --
4414 ---------------------------------------------
4415 -- Get first assignment of this person
4416 -- in the tax_ref
4417 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
4418 FETCH c_first_asg INTO first_asg_rec;
4419 CLOSE c_first_asg;
4420 --
4421 hr_utility.set_location(l_proc, 50);
4422 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
4423 -- Get primary car allocation of the
4424 -- first assignment of this person
4425 -- in this tax ref
4426 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
4427 FETCH c_primary_car INTO primary_car_rec;
4428 CLOSE c_primary_car;
4429 --
4430 hr_utility.set_location(l_proc, 60);
4431 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
4432 --
4433 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
4434 -- This is the first car allocation
4435 -- of this employee in this tax ref
4436 -- therefore check if it has replaced
4437 -- any prior car
4438 OPEN c_prior_prim_car(asg_rec.assignment_id, Alc_dates_rec.min_start_date);
4439 FETCH c_prior_prim_car INTO prior_prim_car_rec;
4440 IF c_prior_prim_car%FOUND THEN
4441 L_action_flag := 'R';
4442 ELSE
4443 L_action_flag := 'N';
4444 END IF;
4445 --
4446 hr_utility.set_location(l_proc, 70);
4447 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4448 --
4449 CLOSE c_prior_prim_car;
4450 ELSE
4451 -- Not the first car of the employee
4452 -- report this as new car
4453 L_action_flag := 'N';
4454 END IF;
4455 --
4456 hr_utility.set_location(l_proc, 80);
4457 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4458 --
4459 ELSE
4460 -- Not a primary car therefore mark
4461 -- it as new car allocation action
4462 l_action_flag := 'N';
4463 END IF;
4464 --
4465 hr_utility.set_location(l_proc, 90);
4466 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4467 --
4468 IF L_action_flag <> 'X' THEN
4469 -- Check whether this allocation action
4470 -- has been already archived
4471 l_archived_flag := 'Y';
4472 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4473 alc_rec.vehicle_allocation_id,
4474 alc_dates_rec.min_start_date,
4475 l_action_flag,
4476 alc_rec.effective_start_date); -- Added for the bug 10088866
4477 --
4478 hr_utility.set_location(l_proc, 100);
4479 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
4480 --
4481 IF (l_archived_flag = 'N')
4482 THEN
4483 -- Action has not been archived already
4484 -- therefore archive it.
4485 hr_utility.set_location(l_proc, 105);
4486 hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
4487 hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
4488 hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
4489 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4490 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
4494 p_asg_act_id => p_assactid,
4491 hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
4492 --
4493 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4495 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4496 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4497 p_action_flag => l_action_flag,
4498 p_allocation_start_date => alc_dates_rec.min_start_date,
4499 p_allocation_end_date => alc_dates_rec.max_end_date,
4500 p_version_type => 'P46_CAR_V4'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
4501 END IF;
4502 END IF;
4503 END IF;
4504 --
4505 L_action_flag := 'X';
4506 l_archived_flag := 'Y';
4507 --
4508 IF (alc_dates_rec.max_end_date BETWEEN g_start_date AND g_end_Date) AND
4509 (alc_dates_rec.max_end_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4510 --
4511 IF alc_rec.usage_type = 'P' THEN
4512 ---------------------------------------------
4513 -- It's a primary car for the assignment --
4514 -- therefore check whether it's the first --
4515 -- car of the employee --
4516 ---------------------------------------------
4517 -- Get first assignment of this person
4518 -- in the tax_ref
4519 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
4520 FETCH c_first_asg INTO first_asg_rec;
4521 CLOSE c_first_asg;
4522 --
4523 hr_utility.set_location(l_proc, 50);
4524 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
4525 -- Get primary car allocation of the
4526 -- first assignment of this person
4527 -- in this tax ref
4528 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
4529 FETCH c_primary_car INTO primary_car_rec;
4530 CLOSE c_primary_car;
4531 --
4532 hr_utility.set_location(l_proc, 60);
4533 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
4534 --
4535 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
4536 --
4537 OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
4538 FETCH c_next_prim_car INTO next_prim_car_rec;
4539 IF c_next_prim_car%FOUND THEN
4540 -- There is a replacement action to
4541 -- Report this car therefore
4542 -- No need to archive this action
4543 NULL;
4544 ELSE
4545 -- This is a withdrawal action
4546 L_action_flag := 'W';
4547 END IF;
4548 --
4549 hr_utility.set_location(l_proc, 70);
4550 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4551 --
4552 CLOSE c_next_prim_car;
4553 ELSE
4554 -- Not the first car of the employee
4555 -- report this as withdrawal car
4556 L_action_flag := 'W';
4557 END IF;
4558 --
4559 --
4560 hr_utility.set_location(l_proc, 80);
4561 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4562 --
4563 ELSE
4564 -- Not a first car therefore mark
4565 -- it as withdrawal car action
4566 l_action_flag := 'W';
4567 END IF;
4568 --
4569 hr_utility.set_location(l_proc, 90);
4570 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4571 --
4572 IF L_action_flag <> 'X' THEN
4573 -- Check whether this allocation action
4574 -- has been already archived
4575 l_archived_flag := 'Y';
4576 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4577 alc_rec.vehicle_allocation_id,
4578 alc_dates_rec.min_start_date,
4579 l_action_flag,
4580 alc_rec.effective_start_date); -- Added for the bug 10088866
4581 --
4582 hr_utility.set_location(l_proc, 100);
4583 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
4584 --
4585 IF l_archived_flag = 'N' THEN
4586 -- Action has not been archived already
4587 -- therefore archive it.
4588 hr_utility.set_location(l_proc, 105);
4589 hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
4590 hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
4591 hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
4592 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4593 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
4594 hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
4595 --
4596 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4597 p_asg_act_id => p_assactid,
4598 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4599 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4600 p_action_flag => l_action_flag,
4601 p_allocation_start_date => alc_dates_rec.min_start_date,
4602 p_allocation_end_date => alc_dates_rec.max_end_date,
4603 p_version_type => 'P46_CAR_V4'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
4604 END IF;
4605 END IF;
4606 END IF;
4607 -- Check if fuel type has changed within
4608 -- the date range.
4609 FOR vehicle_changes_rec IN c_vehicle_changes_v4(alc_rec.vehicle_repository_id) LOOP
4610 -- Fuel type has changed therefore make sure
4611 -- this change occured after the vehicle was
4612 -- allocated to this assignment
4613 --
4614 hr_utility.set_location(l_proc, 110);
4615 hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
4616 hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
4617 --
4618 IF (vehicle_changes_rec.effective_start_date
4619 BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
4620 (vehicle_changes_rec.effective_start_date
4621 BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4622 --
4623 -- Check if this fuel change has been
4624 -- already archived
4625 l_archived_flag := 'Y';
4626 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4627 alc_rec.vehicle_allocation_id,
4628 vehicle_changes_rec.effective_start_date,
4629 'F',
4630 alc_rec.effective_start_date); -- Added for the bug 10088866
4631 --
4632 hr_utility.set_location(l_proc, 120);
4633 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
4634 --
4635 IF l_archived_flag = 'N' THEN
4636 -- Action has not been archived already
4637 -- therefore archive it.
4638 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4639 p_asg_act_id => p_assactid,
4640 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4641 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4642 p_fuel_type_change_date => vehicle_changes_rec.effective_start_date,
4643 p_action_flag => 'F',
4644 p_allocation_start_date => alc_dates_rec.min_start_date,
4645 p_allocation_end_date => alc_dates_rec.max_end_date,
4646 p_version_type => 'P46_CAR_V4'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
4647 --
4648 END IF;
4649 END IF;
4650 END LOOP;
4651 --
4652 hr_utility.set_location(l_proc, 130);
4653 --
4654 L_action_flag := 'X';
4655 l_archived_flag := 'Y';
4656 --
4657 ----------------------------------------------------
4658 -- If tax ref has changed within the given date --
4659 -- range then check whether it should be reported --
4660 -- as a new allocation or withdrawal --
4661 ----------------------------------------------------
4662 IF (asg_rec.asg_min_start_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
4663 AND (asg_rec.asg_min_start_date BETWEEN g_start_Date and g_end_Date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4664 -- Assignment has started on this payroll or tax ref during the
4665 -- input date range and this car allocation was active on the
4666 -- start date therefore check whether it is a transfer
4667 -- from another tax ref
4668 hr_utility.set_location(l_proc, 160);
4669 l_previous_tax_ref := NULL;
4670 --
4671 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
4672 FETCH c_tax_ref INTO l_previous_tax_ref;
4673 CLOSE c_tax_ref;
4674 --
4675 hr_utility.set_location(l_proc, 170);
4676 hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
4677 --
4678 IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
4679 -- This assignment has a different prior tax ref
4680 -- therefore it should be reported as a new car
4681 -- allocation on this EDI message.
4682 hr_utility.set_location(l_proc, 180);
4683 l_action_flag := 'N';
4684 --
4685 -- Check whether this allocation action
4686 -- has been already archived
4687 l_archived_flag := 'Y';
4688 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4689 alc_rec.vehicle_allocation_id,
4690 asg_rec.asg_min_start_date,
4691 l_action_flag,
4692 alc_rec.effective_start_date); -- Added for the bug 10088866
4693 --
4694 hr_utility.set_location(l_proc, 190);
4695 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
4696 --
4697 IF l_archived_flag = 'N' THEN
4698 -- Action has not been archived already
4699 -- therefore archive it.
4700 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4701 p_asg_act_id => p_assactid,
4702 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4703 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4704 p_action_flag => l_action_flag,
4705 p_allocation_start_date => asg_rec.asg_min_start_date,
4706 p_allocation_end_date => asg_rec.asg_max_end_date,
4707 p_version_type => 'P46_CAR_V4'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
4708 END IF;
4709 --
4710 END IF;
4711 END IF;
4712 --
4713 L_action_flag := 'X';
4714 l_archived_flag := 'Y';
4715 --
4716 IF (asg_rec.asg_max_end_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
4717 AND (asg_rec.asg_max_end_date BETWEEN g_start_Date and g_end_Date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4718 -- Assignment has ended on this payroll or tax ref during the
4719 -- input date range and this car allocation was active on the
4720 -- end date therefore check whether it is a transfer
4721 -- to another tax ref
4722 hr_utility.set_location(l_proc, 200);
4723 l_next_tax_ref := NULL;
4724 --
4725 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
4726 FETCH c_tax_ref INTO l_next_tax_ref;
4727 CLOSE c_tax_ref;
4728 --
4729 hr_utility.set_location(l_proc, 210);
4730 hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
4734 -- therefore it should be reported as allocation withdrawal
4731 --
4732 IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
4733 -- This assignment has a different tax ref after end date
4735 -- on this EDI message.
4736 hr_utility.set_location(l_proc, 220);
4737 l_action_flag := 'W';
4738 --
4739 -- Check whether this allocation action
4740 -- has been already archived
4741 l_archived_flag := 'Y';
4742 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4743 alc_rec.vehicle_allocation_id,
4744 asg_rec.asg_max_end_date,
4745 l_action_flag,
4746 alc_rec.effective_start_date); -- Added for the bug 10088866
4747 --
4748 hr_utility.set_location(l_proc, 230);
4749 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
4750 --
4751 IF l_archived_flag = 'N' THEN
4752 -- Action has not been archived already
4753 -- therefore archive it.
4754 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4755 p_asg_act_id => p_assactid,
4756 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4757 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4758 p_action_flag => l_action_flag,
4759 p_allocation_start_date => asg_rec.asg_min_start_date,
4760 p_allocation_end_date => asg_rec.asg_max_end_date,
4761 p_version_type => 'P46_CAR_V4'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
4762 END IF;
4763 --
4764 END IF;
4765 END IF;
4766 --
4767 hr_utility.set_location(l_proc, 240);
4768
4769 hr_utility.set_location(l_proc, 390);
4770 END LOOP;
4771 --
4772 hr_utility.set_location(l_proc, 400);
4773 --
4774 END LOOP;
4775 --
4776 hr_utility.set_location('Leaving: '||l_proc,450);
4777 --hr_utility.trace_off;
4778 EXCEPTION
4779 WHEN OTHERS THEN
4780 hr_utility.set_location('Leaving: '||l_proc,300);
4781 -- hr_utility.trace_off;
4782 raise;
4783 END archive_code_v4;
4784
4785
4786 --For Bug 13400872
4787 -----------------------------------------------------
4788 -- PROCEDURE ARCHIVE_CODE_V5 --
4789 -----------------------------------------------------
4790 PROCEDURE archive_code_v5(p_assactid IN NUMBER,
4791 p_effective_date IN DATE) IS
4792 --
4793 l_proc CONSTANT VARCHAR2(100):= g_package||'archive_code_v5';
4794 --
4795 --
4796 CURSOR c_asg IS
4797 SELECT asg.assignment_id,
4798 min(asg.effective_start_date) asg_min_start_date,
4799 max(asg.effective_end_date) asg_max_end_date
4800 FROM hr_soft_coding_keyflex flex,
4801 per_all_assignments_f asg,
4802 Pay_payrolls_f ppf,
4803 pay_assignment_actions act
4804 WHERE act.assignment_action_id = p_assactid
4805 AND act.assignment_id = asg.assignment_id
4806 AND asg.payroll_id +0 = nvl(g_payroll_id,asg.payroll_id)
4807 AND asg.payroll_id = ppf.payroll_id
4808 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
4809 AND flex.segment1 = g_tax_ref
4810 AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4811 OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4812 OR (asg.effective_start_date BETWEEN g_start_date AND g_end_Date
4813 AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
4814
4815 GROUP by asg.assignment_id;
4816 --
4817 l_action_flag VARCHAR2(1);
4818 l_archived_flag VARCHAR2(1);
4819 --
4820 BEGIN
4821
4822 hr_utility.set_location('Entering '||l_proc, 10);
4823 --
4824 For asg_rec IN c_asg
4825 LOOP
4826 -- Loop through all assignments
4827 hr_utility.set_location(l_proc, 20);
4828 hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
4829 hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
4830 hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
4831 --
4832 FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
4833 -- Loop through all vehicle allocations
4834 -- over the date range
4835 hr_utility.set_location(l_proc, 30);
4836 hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
4837 hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
4838 --
4839 L_action_flag := 'X';
4840 l_archived_flag := 'Y';
4841 --
4842 OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
4843 FETCH c_alloc_dates INTO alc_dates_rec;
4844 CLOSE c_alloc_dates;
4845 --
4846 hr_utility.set_location(l_proc, 40);
4847 hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
4848 hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
4849
4850 -- Moved the code here from below for the bug 10088866
4851 -- Start moving for the bug 10088866
4852 -- Private flag logic
4856 L_action_flag := 'X';
4853 --
4854 --
4855
4857 l_archived_flag := 'Y';
4858 --
4859 FOR private_flag_iterator IN private_flag_arch(alc_rec.vehicle_allocation_id) LOOP
4860 hr_utility.set_location(l_proc, 250);
4861 hr_utility.trace(l_proc||', private_flag_iterator.private_use_flag='||private_flag_iterator.private_use_flag);
4862
4863 L_action_flag := 'X';
4864 l_archived_flag := 'Y';
4865
4866 IF private_flag_iterator.private_use_flag = 'Y' THEN
4867 L_action_flag := 'N';
4868 ELSIF NVL(private_flag_iterator.private_use_flag,'N') = 'N' THEN
4869 L_action_flag := 'W';
4870 END IF;
4871
4872 l_archived_flag := 'Y';
4873 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4874 alc_rec.vehicle_allocation_id,
4875 private_flag_iterator.effective_start_date,
4876 l_action_flag,
4877 private_flag_iterator.effective_start_date);
4878 --
4879 hr_utility.set_location(l_proc, 260);
4880 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
4881 --
4882 IF l_archived_flag = 'N' THEN
4883 -- Action has not been archived already
4884 -- therefore archive it.
4885
4886 -- if the private_use_flag is set to N -Y or Y- N, the allocation
4887 -- end date will be EOT. If it is EOT, Set it to the Withdrawal Date to
4888 -- avoid the error 'Date Car Withdrawn must not be later than Date of Message'
4889
4890 if private_flag_iterator.effective_end_date = hr_general.END_OF_TIME then
4891 private_flag_iterator.effective_end_date := private_flag_iterator.effective_start_date;
4892 end if;
4893
4894 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
4895 p_asg_act_id => p_assactid,
4896 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
4897 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
4898 p_action_flag => L_action_flag,
4899 p_allocation_start_date => private_flag_iterator.effective_start_date,
4900 p_allocation_end_date => private_flag_iterator.effective_end_date,
4901 p_version_type => 'P46_CAR_V5'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
4902 END IF;
4903 END LOOP; -- private_flag_iterator
4904
4905 -- End moving for the bug 10088866
4906
4907 --
4908 -- Check whether vehicle was allocated or
4909 -- Withdrawn within the date range also
4910 -- check that this allocation or withdrawal
4911 -- happened when assignment was with input tax ref
4912 IF (alc_dates_rec.min_start_date BETWEEN g_start_date AND g_end_Date) AND
4913 (alc_dates_rec.min_start_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
4914 --
4915 -- This is a New Car or replacement action
4916 IF alc_rec.usage_type = 'P' THEN
4917 ---------------------------------------------
4918 -- It's a primary car for the assignment --
4919 -- therefore check whether it's the first --
4920 -- car of the employee --
4921 ---------------------------------------------
4922 -- Get first assignment of this person
4923 -- in the tax_ref
4924 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
4925 FETCH c_first_asg INTO first_asg_rec;
4926 CLOSE c_first_asg;
4927 --
4928 hr_utility.set_location(l_proc, 50);
4929 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
4930 -- Get primary car allocation of the
4931 -- first assignment of this person
4932 -- in this tax ref
4933 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
4934 FETCH c_primary_car INTO primary_car_rec;
4935 CLOSE c_primary_car;
4936 --
4937 hr_utility.set_location(l_proc, 60);
4938 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
4939 --
4940 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
4941 -- This is the first car allocation
4942 -- of this employee in this tax ref
4943 -- therefore check if it has replaced
4944 -- any prior car
4945 OPEN c_prior_prim_car(asg_rec.assignment_id, Alc_dates_rec.min_start_date);
4946 FETCH c_prior_prim_car INTO prior_prim_car_rec;
4947 IF c_prior_prim_car%FOUND THEN
4948 L_action_flag := 'R';
4949 ELSE
4950 L_action_flag := 'N';
4951 END IF;
4952 --
4953 hr_utility.set_location(l_proc, 70);
4954 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4955 --
4956 CLOSE c_prior_prim_car;
4957 ELSE
4958 -- Not the first car of the employee
4959 -- report this as new car
4960 L_action_flag := 'N';
4961 END IF;
4962 --
4963 hr_utility.set_location(l_proc, 80);
4964 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4965 --
4966 ELSE
4967 -- Not a primary car therefore mark
4968 -- it as new car allocation action
4969 l_action_flag := 'N';
4970 END IF;
4971 --
4972 hr_utility.set_location(l_proc, 90);
4973 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4974 --
4975 IF L_action_flag <> 'X' THEN
4976 -- Check whether this allocation action
4977 -- has been already archived
4978 l_archived_flag := 'Y';
4979 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
4980 alc_rec.vehicle_allocation_id,
4981 alc_dates_rec.min_start_date,
4982 l_action_flag,
4983 alc_rec.effective_start_date); -- Added for the bug 10088866
4984 --
4985 hr_utility.set_location(l_proc, 100);
4986 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
4987 --
4988 IF (l_archived_flag = 'N')
4989 THEN
4990 -- Action has not been archived already
4991 -- therefore archive it.
4992 hr_utility.set_location(l_proc, 105);
4993 hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
4994 hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
4995 hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
4996 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
4997 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
4998 hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
4999 --
5000 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
5001 p_asg_act_id => p_assactid,
5002 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
5003 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
5004 p_action_flag => l_action_flag,
5005 p_allocation_start_date => alc_dates_rec.min_start_date,
5006 p_allocation_end_date => alc_dates_rec.max_end_date,
5007 p_version_type => 'P46_CAR_V5'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
5008 END IF;
5009 END IF;
5010 END IF;
5011 --
5012 L_action_flag := 'X';
5013 l_archived_flag := 'Y';
5014 --
5015 IF (alc_dates_rec.max_end_date BETWEEN g_start_date AND g_end_Date) AND
5016 (alc_dates_rec.max_end_date BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
5017 --
5018 IF alc_rec.usage_type = 'P' THEN
5019 ---------------------------------------------
5020 -- It's a primary car for the assignment --
5021 -- therefore check whether it's the first --
5022 -- car of the employee --
5023 ---------------------------------------------
5024 -- Get first assignment of this person
5025 -- in the tax_ref
5026 OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
5027 FETCH c_first_asg INTO first_asg_rec;
5028 CLOSE c_first_asg;
5029 --
5030 hr_utility.set_location(l_proc, 50);
5031 hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
5032 -- Get primary car allocation of the
5033 -- first assignment of this person
5034 -- in this tax ref
5035 OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
5036 FETCH c_primary_car INTO primary_car_rec;
5037 CLOSE c_primary_car;
5038 --
5039 hr_utility.set_location(l_proc, 60);
5040 hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
5041 --
5042 IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
5043 --
5044 OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
5045 FETCH c_next_prim_car INTO next_prim_car_rec;
5046 IF c_next_prim_car%FOUND THEN
5047 -- There is a replacement action to
5048 -- Report this car therefore
5049 -- No need to archive this action
5050 NULL;
5051 ELSE
5052 -- This is a withdrawal action
5053 L_action_flag := 'W';
5054 END IF;
5055 --
5056 hr_utility.set_location(l_proc, 70);
5057 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
5058 --
5059 CLOSE c_next_prim_car;
5060 ELSE
5061 -- Not the first car of the employee
5062 -- report this as withdrawal car
5063 L_action_flag := 'W';
5064 END IF;
5065 --
5066 --
5067 hr_utility.set_location(l_proc, 80);
5068 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
5069 --
5070 ELSE
5071 -- Not a first car therefore mark
5072 -- it as withdrawal car action
5073 l_action_flag := 'W';
5074 END IF;
5075 --
5076 hr_utility.set_location(l_proc, 90);
5077 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
5078 --
5079 IF L_action_flag <> 'X' THEN
5080 -- Check whether this allocation action
5081 -- has been already archived
5082 l_archived_flag := 'Y';
5083 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
5084 alc_rec.vehicle_allocation_id,
5085 alc_dates_rec.min_start_date,
5086 l_action_flag,
5087 alc_rec.effective_start_date); -- Added for the bug 10088866
5088 --
5089 hr_utility.set_location(l_proc, 100);
5090 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
5091 --
5092 IF l_archived_flag = 'N' THEN
5093 -- Action has not been archived already
5094 -- therefore archive it.
5095 hr_utility.set_location(l_proc, 105);
5096 hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
5097 hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
5098 hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
5099 hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
5100 hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
5101 hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
5102 --
5103 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
5104 p_asg_act_id => p_assactid,
5105 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
5106 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
5107 p_action_flag => l_action_flag,
5108 p_allocation_start_date => alc_dates_rec.min_start_date,
5109 p_allocation_end_date => alc_dates_rec.max_end_date,
5110 p_version_type => 'P46_CAR_V5'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
5111 END IF;
5112 END IF;
5113 END IF;
5114 -- Check if fuel type has changed within
5115 -- the date range.
5116 FOR vehicle_changes_rec IN c_vehicle_changes_v4(alc_rec.vehicle_repository_id) LOOP
5117 -- Fuel type has changed therefore make sure
5118 -- this change occured after the vehicle was
5119 -- allocated to this assignment
5120 --
5121 hr_utility.set_location(l_proc, 110);
5122 hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
5123 hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
5124 --
5125 IF (vehicle_changes_rec.effective_start_date
5126 BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
5127 (vehicle_changes_rec.effective_start_date
5128 BETWEEN asg_rec.asg_min_start_date AND asg_rec.asg_max_end_date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
5129 --
5130 -- Check if this fuel change has been
5131 -- already archived
5132 l_archived_flag := 'Y';
5133 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
5134 alc_rec.vehicle_allocation_id,
5135 vehicle_changes_rec.effective_start_date,
5136 'F',
5137 alc_rec.effective_start_date); -- Added for the bug 10088866
5138 --
5139 hr_utility.set_location(l_proc, 120);
5140 hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
5141 --
5142 IF l_archived_flag = 'N' THEN
5143 -- Action has not been archived already
5144 -- therefore archive it.
5145 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
5146 p_asg_act_id => p_assactid,
5147 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
5148 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
5149 p_fuel_type_change_date => vehicle_changes_rec.effective_start_date,
5150 p_action_flag => 'F',
5151 p_allocation_start_date => alc_dates_rec.min_start_date,
5152 p_allocation_end_date => alc_dates_rec.max_end_date,
5153 p_version_type => 'P46_CAR_V5'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
5154 --
5155 END IF;
5156 END IF;
5157 END LOOP;
5158 --
5159 hr_utility.set_location(l_proc, 130);
5160 --
5161 L_action_flag := 'X';
5162 l_archived_flag := 'Y';
5163 --
5164 ----------------------------------------------------
5165 -- If tax ref has changed within the given date --
5166 -- range then check whether it should be reported --
5167 -- as a new allocation or withdrawal --
5168 ----------------------------------------------------
5172 -- input date range and this car allocation was active on the
5169 IF (asg_rec.asg_min_start_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
5170 AND (asg_rec.asg_min_start_date BETWEEN g_start_Date and g_end_Date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
5171 -- Assignment has started on this payroll or tax ref during the
5173 -- start date therefore check whether it is a transfer
5174 -- from another tax ref
5175 hr_utility.set_location(l_proc, 160);
5176 l_previous_tax_ref := NULL;
5177 --
5178 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
5179 FETCH c_tax_ref INTO l_previous_tax_ref;
5180 CLOSE c_tax_ref;
5181 --
5182 hr_utility.set_location(l_proc, 170);
5183 hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
5184 --
5185 IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
5186 -- This assignment has a different prior tax ref
5187 -- therefore it should be reported as a new car
5188 -- allocation on this EDI message.
5189 hr_utility.set_location(l_proc, 180);
5190 l_action_flag := 'N';
5191 --
5192 -- Check whether this allocation action
5193 -- has been already archived
5194 l_archived_flag := 'Y';
5195 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
5196 alc_rec.vehicle_allocation_id,
5197 asg_rec.asg_min_start_date,
5198 l_action_flag,
5199 alc_rec.effective_start_date); -- Added for the bug 10088866
5200 --
5201 hr_utility.set_location(l_proc, 190);
5202 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
5203 --
5204 IF l_archived_flag = 'N' THEN
5205 -- Action has not been archived already
5206 -- therefore archive it.
5207 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
5208 p_asg_act_id => p_assactid,
5209 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
5210 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
5211 p_action_flag => l_action_flag,
5212 p_allocation_start_date => asg_rec.asg_min_start_date,
5213 p_allocation_end_date => asg_rec.asg_max_end_date,
5214 p_version_type => 'P46_CAR_V5'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
5215 END IF;
5216 --
5217 END IF;
5218 END IF;
5219 --
5220 L_action_flag := 'X';
5221 l_archived_flag := 'Y';
5222 --
5223 IF (asg_rec.asg_max_end_date BETWEEN alc_dates_rec.min_start_date AND alc_dates_rec.max_end_date)
5224 AND (asg_rec.asg_max_end_date BETWEEN g_start_Date and g_end_Date) and alc_rec.private_use_flag ='Y' THEN -- Modified for the bug 10088866
5225 -- Assignment has ended on this payroll or tax ref during the
5226 -- input date range and this car allocation was active on the
5227 -- end date therefore check whether it is a transfer
5228 -- to another tax ref
5229 hr_utility.set_location(l_proc, 200);
5230 l_next_tax_ref := NULL;
5231
5232 --
5233 OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
5234 FETCH c_tax_ref INTO l_next_tax_ref;
5235 CLOSE c_tax_ref;
5236 --
5237 hr_utility.set_location(l_proc, 210);
5238 hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
5239 --
5240 IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
5241 -- This assignment has a different tax ref after end date
5242 -- therefore it should be reported as allocation withdrawal
5243 -- on this EDI message.
5244 hr_utility.set_location(l_proc, 220);
5245 l_action_flag := 'W';
5246 --
5247 -- Check whether this allocation action
5248 -- has been already archived
5249 l_archived_flag := 'Y';
5250 l_archived_flag := chk_action_archived_v2(asg_rec.assignment_id,
5251 alc_rec.vehicle_allocation_id,
5252 asg_rec.asg_max_end_date,
5253 l_action_flag,
5254 alc_rec.effective_start_date); -- Added for the bug 10088866
5255 --
5256 hr_utility.set_location(l_proc, 230);
5257 hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
5258 --
5259 IF l_archived_flag = 'N' THEN
5260 -- Action has not been archived already
5261 -- therefore archive it.
5262 archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
5263 p_asg_act_id => p_assactid,
5264 p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
5265 p_vehicle_repository_id => alc_rec.vehicle_repository_id,
5266 p_action_flag => l_action_flag,
5267 p_allocation_start_date => asg_rec.asg_min_start_date,
5268 p_allocation_end_date => asg_rec.asg_max_end_date,
5269 p_version_type => 'P46_CAR_V5'); -- Added the parameter for the Bug : 11075296. Fuel Type changes for EOY 2011/12
5270 END IF;
5271 --
5272 END IF;
5273 END IF;
5274 --
5275 hr_utility.set_location(l_proc, 240);
5276
5277 hr_utility.set_location(l_proc, 390);
5278 END LOOP;
5279 --
5280 hr_utility.set_location(l_proc, 400);
5281 --
5282 END LOOP;
5283 --
5284 hr_utility.set_location('Leaving: '||l_proc,450);
5285 --hr_utility.trace_off;
5286 EXCEPTION
5287 WHEN OTHERS THEN
5288 hr_utility.set_location('Leaving: '||l_proc,300);
5289 -- hr_utility.trace_off;
5290 raise;
5291 END archive_code_v5;
5292
5293 END pay_gb_edi_p46_car;
[Home] [Help]