[Home] [Help]
PACKAGE BODY: APPS.PQP_INI_BAL
Source
1 PACKAGE BODY PQP_INI_BAL AS
2 /* $Header: pqpbladj.pkb 115.15 2003/07/16 09:37:13 jcpereir noship $*/
3 g_err_info t_err_info;
4 upgrade_status varchar2(1):= 'C';
5 adjustment_entry_count NUMBER := 0;
6 l_prev_upgrade_status VARCHAR2(2);
7 prev_yr_car_ni_amt NUMBER:=0;
8 prev_yr_mc_ni_amt NUMBER:=0;
9 prev_yr_pc_ni_amt NUMBER:=0;
10
11 PROCEDURE route_balance_amt
12 AS
13 l_effective_date DATE;
14 BEGIN
15
16 SAVEPOINT CARMILEAGE;
17 FOR i in 1..g_sum_bal_info.count
18 LOOP
19 IF g_sum_bal_info(i).PAYE_Taxable = 'N' and g_err_info.count = 0 THEN
20 IF g_sum_bal_info(i).usage_type='C' THEN
21 -- Create Adjustment Entries for Private-Casual Claims which are not PAYE Taxable
22 create_element_entry
23 ( p_effective_date =>g_sum_bal_info(i).effective_date
24 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
25 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
26 ,p_element_name =>g_sum_bal_info(i).element_name||' Pvt Mlg Addl Ele1'
27 ,p_base_element_name =>g_sum_bal_info(i).element_name
28 ,p_entry_value1 =>NULL
29 ,p_entry_value2 =>g_sum_bal_info(i).Processed_Amt
30 ,p_entry_value3 =>g_sum_bal_info(i).Processed_Miles
31 ,p_entry_value4 =>g_sum_bal_info(i).Processed_Act_Miles
32 ,p_entry_value5 =>NULL
33 ,p_entry_value6 =>NULL
34 ,p_entry_value7 =>NULL
35 ,p_entry_value8 =>NULL
36 ,p_entry_value9 =>NULL
37 ,p_entry_value10 =>NULL
38 ,p_entry_value11 =>NULL
39 ,p_entry_value12 =>NULL
40 ,p_entry_value13 =>NULL
41 ,p_entry_value14 =>NULL
42 ,p_entry_value15 =>NULL
43 );
44 create_element_entry
45 ( p_effective_date =>g_sum_bal_info(i).effective_date
46 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
47 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
48 ,p_element_name =>g_sum_bal_info(i).element_name||' Pvt Mlg Addl Ele2'
49 ,p_base_element_name =>g_sum_bal_info(i).element_name
50 ,p_entry_value1 =>NULL
51 ,p_entry_value2 =>g_sum_bal_info(i).Addl_Tax_Amt
52 ,p_entry_value3 =>g_sum_bal_info(i).Addl_Pasg_Amt
53 ,p_entry_value4 =>g_sum_bal_info(i).Addl_Pasg_Miles
54 ,p_entry_value5 =>g_sum_bal_info(i).Addl_Pasg_Act_Miles
55 ,p_entry_value6 =>NULL
56 ,p_entry_value7 =>NULL
57 ,p_entry_value8 =>NULL
58 ,p_entry_value9 =>NULL
59 ,p_entry_value10 =>g_sum_bal_info(i).NI_Amt
60 ,p_entry_value11 =>NULL
61 ,p_entry_value12 =>g_sum_bal_info(i).Taxable_Amt
62 ,p_entry_value13 =>NULL
63 ,p_entry_value14 =>g_sum_bal_info(i).IRAM_Amt
64 ,p_entry_value15 =>NULL
65 );
66
67
68
69 ELSIF g_sum_bal_info(i).usage_type='E' THEN
70 --Create Adjustment Entries for Private-Essential Claims which are not PAYE Taxable
71 create_element_entry
72 ( p_effective_date =>g_sum_bal_info(i).effective_date
73 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
74 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
75 ,p_element_name =>g_sum_bal_info(i).element_name||' Pvt Mlg Addl Ele1'
76 ,p_base_element_name =>g_sum_bal_info(i).element_name
77 ,p_entry_value1 =>NULL
78 ,p_entry_value2 =>NULL
79 ,p_entry_value3 =>NULL
80 ,p_entry_value4 =>NULL
81 ,p_entry_value5 =>NULL
82 ,p_entry_value6 =>NULL
83 ,p_entry_value7 =>NULL
84 ,p_entry_value8 =>g_sum_bal_info(i).Processed_Amt
85 ,p_entry_value9 =>g_sum_bal_info(i).Processed_Miles
86 ,p_entry_value10 =>g_sum_bal_info(i).Processed_Act_Miles
87 ,p_entry_value11 =>NULL
88 ,p_entry_value12 =>NULL
89 ,p_entry_value13 =>NULL
90 ,p_entry_value14 =>NULL
91 ,p_entry_value15 =>NULL
92 );
93
94 create_element_entry
95 ( p_effective_date =>g_sum_bal_info(i).effective_date
96 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
97 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
98 ,p_element_name =>g_sum_bal_info(i).element_name||' Pvt Mlg Addl Ele2'
99 ,p_base_element_name =>g_sum_bal_info(i).element_name
100 ,p_entry_value1 =>NULL
101 ,p_entry_value2 =>g_sum_bal_info(i).Addl_Tax_Amt
102 ,p_entry_value3 =>g_sum_bal_info(i).Addl_Pasg_Amt
103 ,p_entry_value4 =>g_sum_bal_info(i).Addl_Pasg_Miles
104 ,p_entry_value5 =>g_sum_bal_info(i).Addl_Pasg_Act_Miles
105 ,p_entry_value6 =>NULL
106 ,p_entry_value7 =>NULL
107 ,p_entry_value8 =>NULL
108 ,p_entry_value9 =>NULL
109 ,p_entry_value10 =>g_sum_bal_info(i).NI_Amt
110 ,p_entry_value11 =>NULL
111 ,p_entry_value12 =>g_sum_bal_info(i).Taxable_Amt
112 ,p_entry_value13 =>NULL
113 ,p_entry_value14 =>g_sum_bal_info(i).IRAM_Amt
114 ,p_entry_value15 =>NULL
115 );
116
117
118 ELSIF g_sum_bal_info(i).ownership_type='C' THEN
119 --Create Adjustment Entries for Company which are not PAYE Taxable
120 create_element_entry
121 ( p_effective_date =>g_sum_bal_info(i).effective_date
122 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
123 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
124 ,p_element_name =>g_sum_bal_info(i).element_name||' Co Mlg Addl Ele1'
125 ,p_base_element_name =>g_sum_bal_info(i).element_name
126 ,p_entry_value1 =>NULL
127 ,p_entry_value2 =>g_sum_bal_info(i).Processed_Amt
128 ,p_entry_value3 =>g_sum_bal_info(i).Processed_Miles
129 ,p_entry_value4 =>g_sum_bal_info(i).Processed_Act_Miles
130 ,p_entry_value5 =>NULL
131 ,p_entry_value6 =>NULL
132 ,p_entry_value7 =>NULL
133 ,p_entry_value8 =>NULL
134 ,p_entry_value9 =>NULL
135 ,p_entry_value10 =>NULL
136 ,p_entry_value11 =>NULL
137 ,p_entry_value12 =>NULL
138 ,p_entry_value13 =>NULL
139 ,p_entry_value14 =>NULL
140 ,p_entry_value15 =>NULL
141 );
142 create_element_entry
143 ( p_effective_date =>g_sum_bal_info(i).effective_date
144 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
145 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
146 ,p_element_name =>g_sum_bal_info(i).element_name||' Co Mlg Addl Ele2'
147 ,p_base_element_name =>g_sum_bal_info(i).element_name
148 ,p_entry_value1 =>NULL
149 ,p_entry_value2 =>g_sum_bal_info(i).Addl_Pasg_Amt
150 ,p_entry_value3 =>g_sum_bal_info(i).Addl_Pasg_Miles
151 ,p_entry_value4 =>g_sum_bal_info(i).Addl_Pasg_Miles
152 ,p_entry_value5 =>NULL
153 ,p_entry_value6 =>NULL
154 ,p_entry_value7 =>NULL
155 ,p_entry_value8 =>NULL
156 ,p_entry_value9 =>NULL
157 ,p_entry_value10 =>NULL
158 ,p_entry_value11 =>NULL
159 ,p_entry_value12 =>NULL
160 ,p_entry_value13 =>NULL
161 ,p_entry_value14 =>NULL
162 ,p_entry_value15 =>NULL
163 );
164 END IF;
165 ELSIF g_err_info.count = 0 THEN
166 IF g_sum_bal_info(i).usage_type='C' THEN
167 --Create Adjustment Entries for Private-Casual Claims which are PAYE Taxable
168 create_element_entry
169 ( p_effective_date =>g_sum_bal_info(i).effective_date
170 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
171 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
172 ,p_element_name =>g_sum_bal_info(i).element_name||' Pvt Mlg Addl Ele3'
173 ,p_base_element_name =>g_sum_bal_info(i).element_name
174 ,p_entry_value1 =>NULL
175 ,p_entry_value2 =>g_sum_bal_info(i).Processed_Amt
176 ,p_entry_value3 =>NULL
177 ,p_entry_value4 =>g_sum_bal_info(i).Processed_Miles
178 ,p_entry_value5 =>NULL
179 ,p_entry_value6 =>NULL
180 ,p_entry_value7 =>NULL
181 ,p_entry_value8 =>NULL
182 ,p_entry_value9 =>NULL
183 ,p_entry_value10 =>g_sum_bal_info(i).Addl_Pasg_Amt
184 ,p_entry_value11 =>g_sum_bal_info(i).Addl_Pasg_Miles
185 ,p_entry_value12 =>NULL
186 ,p_entry_value13 =>NULL
187 ,p_entry_value14 =>NULL
188 ,p_entry_value15 =>NULL
189 );
190
191 ELSIF g_sum_bal_info(i).usage_type='E' THEN
192 --Create Adjustment Entries for Private-Essential Claims which are PAYE Taxable
193 create_element_entry
194 ( p_effective_date =>g_sum_bal_info(i).effective_date
195 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
196 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
197 ,p_element_name =>g_sum_bal_info(i).element_name||' Pvt Mlg Addl Ele3'
198 ,p_base_element_name =>g_sum_bal_info(i).element_name
199 ,p_entry_value1 =>NULL
200 ,p_entry_value2 =>NULL
201 ,p_entry_value3 =>NULL
202 ,p_entry_value4 =>NULL
203 ,p_entry_value5 =>NULL
204 ,p_entry_value6 =>g_sum_bal_info(i).Processed_Amt
205 ,p_entry_value7 =>NULL
206 ,p_entry_value8 =>g_sum_bal_info(i).Processed_Miles
207 ,p_entry_value9 =>NULL
208 ,p_entry_value10 =>g_sum_bal_info(i).Addl_Pasg_Amt
209 ,p_entry_value11 =>g_sum_bal_info(i).Addl_Pasg_Miles
210 ,p_entry_value12 =>NULL
211 ,p_entry_value13 =>NULL
212 ,p_entry_value14 =>NULL
213 ,p_entry_value15 =>NULL
214 );
215
216
217 ELSIF g_sum_bal_info(i).ownership_type='C' THEN
218 --Create Adjustment Entries for Company Claims which are PAYE Taxable
219 create_element_entry
220 ( p_effective_date =>g_sum_bal_info(i).effective_date
221 ,p_business_group_id =>g_sum_bal_info(i).business_group_id
222 ,p_assignment_id =>g_sum_bal_info(i).assignment_id
223 ,p_element_name =>g_sum_bal_info(i).element_name||' Co Mlg Addl Ele3'
224 ,p_base_element_name =>g_sum_bal_info(i).element_name
225 ,p_entry_value1 =>NULL
226 ,p_entry_value2 =>g_sum_bal_info(i).Processed_Amt
227 ,p_entry_value3 =>NULL
228 ,p_entry_value4 =>g_sum_bal_info(i).Processed_Miles
229 ,p_entry_value5 =>NULL
230 ,p_entry_value6 =>g_sum_bal_info(i).Addl_Pasg_Amt
231 ,p_entry_value7 =>g_sum_bal_info(i).Addl_Pasg_Miles
232 ,p_entry_value8 =>NULL
233 ,p_entry_value9 =>NULL
234 ,p_entry_value10 =>NULL
235 ,p_entry_value11 =>NULL
236 ,p_entry_value12 =>NULL
237 ,p_entry_value13 =>NULL
238 ,p_entry_value14 =>NULL
239 ,p_entry_value15 =>NULL
240 );
241
242 END IF;
243
244
245 END IF; --PAYE Taxable check
246 END LOOP;
247
248 /** If there has been company claims for this assignment , then we need to create an adjustment
249 for this assignment containing the total actual miles claimed under company ownership **/
250 FOR i in 1..g_comp_act_miles.count
251 LOOP
252 create_element_entry
253 ( p_effective_date =>g_sum_bal_info(1).effective_date
254 ,p_business_group_id =>g_sum_bal_info(1).business_group_id
255 ,p_assignment_id =>g_sum_bal_info(1).assignment_id
256 ,p_element_name =>g_comp_act_miles(i).element_name||' Mileage Res2'
257 ,p_base_element_name =>g_comp_act_miles(i).element_name
258 ,p_entry_value1 =>NULL
259 ,p_entry_value2 =>NULL
260 ,p_entry_value3 =>NULL
261 ,p_entry_value4 =>NULL
262 ,p_entry_value5 =>g_comp_act_miles(i).Total_Act_Miles
263 ,p_entry_value6 =>NULL
264 ,p_entry_value7 =>NULL
265 ,p_entry_value8 =>NULL
266 ,p_entry_value9 =>NULL
267 ,p_entry_value10 =>NULL
268 ,p_entry_value11 =>NULL
269 ,p_entry_value12 =>NULL
270 ,p_entry_value13 =>NULL
271 ,p_entry_value14 =>NULL
272 ,p_entry_value15 =>NULL
273 );
274 END LOOP;
275
276 --After the Number of Adjustments have reached a certain point we need to commit
277 IF adjustment_entry_count >= 1000 and g_err_info.count = 0 then
278 COMMIT;
279 adjustment_entry_count := 0;
280 END IF;
281
282 -- This is required during the second try, Suppose the adjustment went successful the second time
283 -- then we need to delete the error log entry that was created the first time
284 IF l_prev_upgrade_status = 'P' THEN
285 DELETE FROM pay_us_rpt_totals
286 WHERE business_group_id=g_sum_bal_info(1).business_group_id
287 AND state_name='CARMILEAGE_UPGRADE'
288 AND tax_unit_id = 250
289 AND location_id = g_sum_bal_info(1).assignment_id;
290 END IF;
291
292 -- This effective date is used to get the date tracked assignment details which
293 -- will be used for the error log
294 l_effective_date := g_sum_bal_info(1).effective_date;
295
296 --Empty the g_sum_bal
297 g_sum_bal_info.delete;
298
299 --Empty the g_comp_act_miles
300 g_comp_act_miles.delete;
301
302 --Empty the cache containing payroll details
303 g_payroll_det_cache.delete;
304
305 IF g_err_info.count > 0 THEN
306 -- Suppose it has errored out for the current assignment
307 -- then we need to rollback to the previous assignment
308 rollback to CARMILEAGE;
309 upgrade_status := 'P'; --Partially Complete
310 END IF;
311 for i in 1..g_err_info.count
312 LOOP
313 -- Insert errored out details into pay_us_rpt_totals
314 -- This will be used during the second run.
315 INSERT INTO
316 pay_us_rpt_totals(
317 business_group_id
318 ,location_id
319 ,location_name
320 ,state_name
321 ,organization_name
322 ,tax_unit_id
323 ) VALUES (
324 g_err_info(i).business_group_id
325 ,g_err_info(i).assignment_id
326 ,g_err_info(i).element_name
327 ,'CARMILEAGE_UPGRADE'
328 ,fnd_date.date_to_canonical(l_effective_date)
329 ,250
330 );
331 END LOOP;
332
333 g_err_info.delete;
334
335 END;
336
337
338 -- In this Procedure we sum up all the related claim entries for a given assignment
339 -- For Example if there are 2 Non Paye Taxable Casual Car Entries and 2 Non Paye Taxable
340 -- Essential MotorCycle entries , then we create one entry containing the sum of the 2 Casual entries
341 -- and another entry containing the sum of the 2 Essential entries.
342 PROCEDURE categorize_balances (p_bal_info IN t_bal_info)
343 AS
344 l_count1 NUMBER:=0;
345 l_count2 NUMBER:=0;
346 l_count3 NUMBER:=0;
347 l_count4 NUMBER:=0;
348 l_count5 NUMBER:=0;
349 l_count6 NUMBER:=0;
350 l_count7 NUMBER:=0;
351 l_count8 NUMBER:=0;
352 l_count9 NUMBER:=0;
353 l_count10 NUMBER:=0;
354 l_count11 NUMBER:=0;
355 l_count12 NUMBER:=0;
356 l_count13 NUMBER:=0;
357 l_count14 NUMBER:=0;
358 l_count15 NUMBER:=0;
359 l_count16 NUMBER:=0;
360 l_count17 NUMBER:=0;
361 l_count18 NUMBER:=0;
362 sumindex NUMBER:=0;
363 l_found NUMBER:=0;
364 l_count NUMBER:=0;
365 car_ni_amt NUMBER:=0;
366 car_tax_amt NUMBER:=0;
367 car_prc_amt NUMBER:=0;
368 car_tot_miles NUMBER:=0;
369 car_ni_diff NUMBER:=0;
370 car_tax_diff NUMBER:=0;
371 current_car_ni_amt NUMBER:=0;
372 current_car_tax_amt NUMBER:=0;
373 current_car_prc_amt NUMBER:=0;
374 current_car_tot_miles NUMBER:=0;
375 mc_ni_amt NUMBER:=0;
376 mc_tax_amt NUMBER:=0;
377 mc_prc_amt NUMBER:=0;
378 mc_tot_miles NUMBER:=0;
379 mc_ni_diff NUMBER:=0;
380 mc_tax_diff NUMBER:=0;
381 current_mc_ni_amt NUMBER:=0;
382 current_mc_tax_amt NUMBER:=0;
383 current_mc_prc_amt NUMBER:=0;
384 current_mc_tot_miles NUMBER:=0;
385 pc_ni_amt NUMBER:=0;
386 pc_tax_amt NUMBER:=0;
387 pc_prc_amt NUMBER:=0;
388 pc_tot_miles NUMBER:=0;
389 pc_ni_diff NUMBER:=0;
390 pc_tax_diff NUMBER:=0;
391 current_pc_ni_amt NUMBER:=0;
392 current_pc_tax_amt NUMBER:=0;
393 current_pc_prc_amt NUMBER:=0;
394 current_pc_tot_miles NUMBER:=0;
395 ret NUMBER;
396 ni_rate VARCHAR2(10);
397 high_band_iram_rate VARCHAR2(10);
398 low_band_iram_rate VARCHAR2(10);
399 err_msg VARCHAR2(100);
400 calculated_ni_amt NUMBER;
401 calculated_tax_amt NUMBER;
402
403
404 PROCEDURE set_value (sumind IN NUMBER,
405 balind IN NUMBER
406 )
407 AS
408 BEGIN
409 g_sum_bal_info(sumind).PAYE_Taxable :=p_bal_info(balind).PAYE_Taxable;
410 g_sum_bal_info(sumind).assignment_id :=p_bal_info(balind).assignment_id;
411 g_sum_bal_info(sumind).business_group_id :=p_bal_info(balind).business_group_id;
412 g_sum_bal_info(sumind).effective_date :=p_bal_info(balind).effective_date;
413 g_sum_bal_info(sumind).Ownership_Type :=p_bal_info(balind).Ownership_Type;
414 g_sum_bal_info(sumind).Vehicle_Type :=p_bal_info(balind).Vehicle_Type;
415 g_sum_bal_info(sumind).Usage_type :=p_bal_info(balind).Usage_type;
416 g_sum_bal_info(sumind).Element_name :=p_bal_info(balind).Element_name;
417 g_sum_bal_info(sumind).Processed_Miles :=NVL(g_sum_bal_info(sumind).Processed_Miles,0)
418 + NVL(p_bal_info(balind).Processed_Miles,0);
419 g_sum_bal_info(sumind).Processed_Act_Miles :=NVL(g_sum_bal_info(sumind).Processed_Act_Miles,0)
420 + NVL(p_bal_info(balind).Processed_Act_Miles,0);
421 g_sum_bal_info(sumind).Processed_Amt :=NVL(g_sum_bal_info(sumind).Processed_Amt,0)
422 + NVL(p_bal_info(balind).Processed_Amt,0);
423 g_sum_bal_info(sumind).IRAM_Amt :=NVL(g_sum_bal_info(sumind).IRAM_Amt,0)
424 + NVL(p_bal_info(balind).IRAM_Amt,0);
425 g_sum_bal_info(sumind).NI_Amt :=NVL(g_sum_bal_info(sumind).NI_Amt,0)
426 + NVL(p_bal_info(balind).NI_Amt,0);
427 g_sum_bal_info(sumind).Taxable_Amt :=NVL(g_sum_bal_info(sumind).Taxable_Amt,0)
428 + NVL(p_bal_info(balind).Taxable_Amt,0);
429 hr_utility.set_location('***** ADDL PROCESSED AMT2: ',p_bal_info(balind).Addl_Pasg_Amt);
430 g_sum_bal_info(sumind).Addl_Pasg_Amt :=NVL(g_sum_bal_info(sumind).Addl_Pasg_Amt,0)
431 + NVL(p_bal_info(balind).Addl_Pasg_Amt,0);
432 g_sum_bal_info(sumind).Addl_Ni_Amt :=NVL(g_sum_bal_info(sumind).Addl_Ni_Amt,0)
433 + NVL(p_bal_info(balind).Addl_Ni_Amt,0);
434 g_sum_bal_info(sumind).Addl_Tax_Amt :=NVL(g_sum_bal_info(sumind).Addl_Tax_Amt,0)
435 + NVL(p_bal_info(balind).Addl_Tax_Amt,0);
436 g_sum_bal_info(sumind).Addl_Pasg_Miles :=NVL(g_sum_bal_info(sumind).Addl_Pasg_Miles,0)
437 + NVL(p_bal_info(balind).Addl_Pasg_Miles,0);
438 g_sum_bal_info(sumind).Addl_Pasg_Act_Miles :=NVL(g_sum_bal_info(sumind).Addl_Pasg_Act_Miles,0)
439 + NVL(p_bal_info(balind).Addl_Pasg_Act_Miles,0);
440
441
442 END;
443
444
445 BEGIN
446
447 car_ni_amt := 0;
448 car_prc_amt := 0;
449 car_tot_miles := 0;
450 mc_ni_amt := 0;
451 mc_prc_amt := 0;
452 mc_tot_miles := 0;
453 pc_ni_amt := 0;
454 pc_prc_amt := 0;
455 pc_tot_miles := 0;
456 -- Roll up all vehicle type info
457 -- The Entries are categorized based on usage type, PAYE Taxable and vehicle type
458 -- The entries belonging to each category will then be summed up.
459 FOR i in 1..p_bal_info.count
460 LOOP
461 IF p_bal_info(i).ownership_type='C' THEN
462 FOR j in 1..g_comp_act_miles.count
463 LOOP
464 IF g_comp_act_miles(j).element_name = p_bal_info(i).element_name THEN
465 g_comp_act_miles(j).total_act_miles := g_comp_act_miles(j).total_act_miles + p_bal_info(i).Processed_Act_Miles;
466 l_found := 1;
467 EXIT;
468 END IF;
469 END LOOP;
470 IF l_found = 0 THEN
471 l_count := g_comp_act_miles.count + 1;
472 g_comp_act_miles(l_count).total_act_miles := p_bal_info(i).Processed_Act_Miles;
473 g_comp_act_miles(l_count).element_name := p_bal_info(i).element_name;
474 END IF;
475 END IF;
476 IF p_bal_info(i).Paye_Taxable ='N' THEN
477 IF p_bal_info(i).usage_type='C' AND
478 p_bal_info(i).vehicle_type ='P' THEN
479 IF l_count1=0 THEN
480 l_count1:=sumindex+1;
481 sumindex:=sumindex+1;
482 END IF;
483 set_value (sumind =>l_count1
484 ,balind =>i
485 ) ;
486 car_ni_amt := car_ni_amt + p_bal_info(i).NI_Amt;
487 car_tax_amt := car_tax_amt + p_bal_info(i).Taxable_Amt;
488 car_prc_amt := car_prc_amt + p_bal_info(i).Processed_Amt;
489 car_tot_miles := car_tot_miles + p_bal_info(i).Processed_Act_Miles;
490 ELSIF p_bal_info(i).usage_type='C' AND
491 p_bal_info(i).vehicle_type ='PM' THEN
492 IF l_count2=0 THEN
493 l_count2:=sumindex+1;
494 sumindex:=sumindex+1;
495 END IF;
496 set_value (sumind =>l_count2
497 ,balind =>i
498 );
499 mc_ni_amt := mc_ni_amt + p_bal_info(i).NI_Amt;
500 mc_tax_amt := mc_tax_amt + p_bal_info(i).Taxable_Amt;
501 mc_prc_amt := mc_prc_amt + p_bal_info(i).Processed_Amt;
502 mc_tot_miles := mc_tot_miles + p_bal_info(i).Processed_Act_Miles;
503 ELSIF p_bal_info(i).usage_type='C' AND
504 p_bal_info(i).vehicle_type ='PP' THEN
505 IF l_count3=0 THEN
506 l_count3:=sumindex+1;
507 sumindex:=sumindex+1;
508 END IF;
509 set_value (sumind =>l_count3
510 ,balind =>i
511 );
512 pc_ni_amt := pc_ni_amt + p_bal_info(i).NI_Amt;
513 pc_tax_amt := pc_tax_amt + p_bal_info(i).Taxable_Amt;
514 pc_prc_amt := pc_prc_amt + p_bal_info(i).Processed_Amt;
515 pc_tot_miles := pc_tot_miles + p_bal_info(i).Processed_Act_Miles;
516 ELSIF p_bal_info(i).usage_type='E' AND
517 p_bal_info(i).vehicle_type ='P' THEN
518 IF l_count4=0 THEN
519 l_count4:=sumindex+1;
520 sumindex:=sumindex+1;
521 END IF;
522 set_value (sumind =>l_count4
523 ,balind =>i
524 );
525 car_ni_amt := car_ni_amt + p_bal_info(i).NI_Amt;
526 car_tax_amt := car_tax_amt + p_bal_info(i).Taxable_Amt;
527 car_prc_amt := car_prc_amt + p_bal_info(i).Processed_Amt;
528 car_tot_miles := car_tot_miles + p_bal_info(i).Processed_Act_Miles;
529 ELSIF p_bal_info(i).usage_type='E' AND
530 p_bal_info(i).vehicle_type ='PM' THEN
531 IF l_count5=0 THEN
532 l_count5:=sumindex+1;
533 sumindex:=sumindex+1;
534 END IF;
535 set_value (sumind =>l_count5
536 ,balind =>i
537 );
538 mc_ni_amt := car_ni_amt + p_bal_info(i).NI_Amt;
539 mc_tax_amt := mc_tax_amt + p_bal_info(i).Taxable_Amt;
540 mc_prc_amt := mc_prc_amt + p_bal_info(i).Processed_Amt;
541 mc_tot_miles := mc_tot_miles + p_bal_info(i).Processed_Act_Miles;
542 ELSIF p_bal_info(i).usage_type='E' AND
543 p_bal_info(i).vehicle_type ='PP' THEN
544 IF l_count6=0 THEN
545 l_count6:=sumindex+1;
546 sumindex:=sumindex+1;
547 END IF;
548 set_value (sumind =>l_count6
549 ,balind =>i
550 );
551 pc_ni_amt := car_ni_amt + p_bal_info(i).NI_Amt;
552 pc_tax_amt := pc_tax_amt + p_bal_info(i).Taxable_Amt;
553 pc_prc_amt := pc_prc_amt + p_bal_info(i).Processed_Amt;
554 pc_tot_miles := pc_tot_miles + p_bal_info(i).Processed_Act_Miles;
555 ELSIF p_bal_info(i).ownership_type='C' AND
556 p_bal_info(i).vehicle_type ='C' THEN
557 IF l_count7=0 THEN
558 l_count7:=sumindex+1;
559 sumindex:=sumindex+1;
560 END IF;
561 set_value (sumind =>l_count7
562 ,balind =>i
563 );
564 ELSIF p_bal_info(i).ownership_type='C' AND
565 p_bal_info(i).vehicle_type ='CM' THEN
566 IF l_count8=0 THEN
567 l_count8:=sumindex+1;
568 sumindex:=sumindex+1;
569 END IF;
570 set_value (sumind =>l_count8
571 ,balind =>i
572 );
573 ELSIF p_bal_info(i).ownership_type='C' AND
574 p_bal_info(i).vehicle_type ='CP' THEN
575 IF l_count9=0 THEN
576 l_count9:=sumindex+1;
577 sumindex:=sumindex+1;
578 END IF;
579 set_value (sumind =>l_count9
580 ,balind =>i
581 );
582 END IF;
583 ELSIF p_bal_info(i).Paye_Taxable ='Y' THEN
584 IF p_bal_info(i).usage_type='C' AND
585 p_bal_info(i).vehicle_type ='P' THEN
586 IF l_count10=0 THEN
587 l_count10:=sumindex+1;
588 sumindex:=sumindex+1;
589 END IF;
590 set_value (sumind =>l_count10
591 ,balind =>i
592 ) ;
593 ELSIF p_bal_info(i).usage_type='C' AND
594 p_bal_info(i).vehicle_type ='PM' THEN
595 IF l_count11=0 THEN
596 l_count11:=sumindex+1;
597 sumindex:=sumindex+1;
598 END IF;
599 set_value (sumind =>l_count11
600 ,balind =>i
601 );
602 ELSIF p_bal_info(i).usage_type='C' AND
603 p_bal_info(i).vehicle_type ='PP' THEN
604 IF l_count12=0 THEN
605 l_count12:=sumindex+1;
606 sumindex:=sumindex+1;
607 END IF;
608 set_value (sumind =>l_count12
609 ,balind =>i
610 );
611 ELSIF p_bal_info(i).usage_type='E' AND
612 p_bal_info(i).vehicle_type ='P' THEN
613 IF l_count13=0 THEN
614 l_count13:=sumindex+1;
615 sumindex:=sumindex+1;
616 END IF;
617 set_value (sumind =>l_count13
618 ,balind =>i
619 );
620 ELSIF p_bal_info(i).usage_type='E' AND
621 p_bal_info(i).vehicle_type ='PM' THEN
622 IF l_count14=0 THEN
623 l_count14:=sumindex+1;
624 sumindex:=sumindex+1;
625 END IF;
626 set_value (sumind =>l_count14
627 ,balind =>i
628 );
629 ELSIF p_bal_info(i).usage_type='E' AND
630 p_bal_info(i).vehicle_type ='PP' THEN
631 IF l_count15=0 THEN
632 l_count15:=sumindex+1;
633 sumindex:=sumindex+1;
634 END IF;
635 set_value (sumind =>l_count15
636 ,balind =>i
637 );
638 ELSIF p_bal_info(i).ownership_type='C' AND
639 p_bal_info(i).vehicle_type ='C' THEN
640 IF l_count16=0 THEN
641 l_count16:=sumindex+1;
642 sumindex:=sumindex+1;
643 END IF;
644 set_value (sumind =>l_count16
645 ,balind =>i
646 );
647 ELSIF p_bal_info(i).ownership_type='C' AND
648 p_bal_info(i).vehicle_type ='CM' THEN
649 IF l_count17=0 THEN
650 l_count17:=sumindex+1;
651 sumindex:=sumindex+1;
652 END IF;
653 set_value (sumind =>l_count17
654 ,balind =>i
655 );
656 ELSIF p_bal_info(i).ownership_type='C' AND
657 p_bal_info(i).vehicle_type ='CP' THEN
658 IF l_count18=0 THEN
659 l_count18:=sumindex+1;
660 sumindex:=sumindex+1;
661 END IF;
662 set_value (sumind =>l_count18
663 ,balind =>i
664 );
665 END IF;
666 END IF;
667 END LOOP;
668
669 IF car_tot_miles <> 0 THEN
670 -- Corrections need to be made to Vehicle Type Ni and Taxable Amt
671 -- Reasons For this Correction :
672 -- After the Upgrade takes place , the balance initialization won't be run immediately.
673 -- In this case there will be a chance that if the Vehicle Type NI amt for a payroll run(just after the update)
674 -- is -ve then this value could be set to 0. Such descrepancies could occur with Taxable amt too. Therefore
675 -- some corrections need to be done. Here we calculate what the NI amt is suppossed to be for that vehicle type.
676 -- We also sum all the adjustment values and the current balance value. If there is any difference beween the 2 values
677 -- then we subtract that difference from the balance adjustment.
678 current_car_ni_amt := get_balance_value(p_bal_info(1).assignment_id,'Car NI Even Amt');
679 current_car_tax_amt := get_balance_value(p_bal_info(1).assignment_id,'Car Taxable Even Amt');
680 current_car_prc_amt := get_balance_value(p_bal_info(1).assignment_id,'Car Casual Even Processed Amt')
681 + get_balance_value(p_bal_info(1).assignment_id,'Car Essential Even Processed Amt');
682 current_car_tot_miles := get_balance_value(p_bal_info(1).assignment_id,'Car Casual Even Actual Miles')
683 + get_balance_value(p_bal_info(1).assignment_id,'Car Essential Even Actual Miles');
684 car_ni_amt := car_ni_amt + current_car_ni_amt;
685 car_tax_amt := car_tax_amt + current_car_tax_amt;
686 car_prc_amt := car_prc_amt + current_car_prc_amt;
687 car_tot_miles := car_tot_miles + current_car_tot_miles;
688 ret := PQP_UTILITIES.PQP_GB_GET_TABLE_VALUE
689 (P_BUSINESS_GROUP_ID => NULL
690 ,P_EFFECTIVE_DATE => sysdate
691 ,P_TABLE_NAME => 'PQP_NIC_MILEAGE_RATES'
692 ,P_COLUMN_NAME => '9999'
693 ,P_ROW_NAME => '99999'
694 ,P_VALUE => ni_rate
695 ,P_ERROR_MSG => err_msg );
696 ret := PQP_UTILITIES.PQP_GB_GET_TABLE_VALUE
697 (P_BUSINESS_GROUP_ID => NULL
698 ,P_EFFECTIVE_DATE => sysdate
699 ,P_TABLE_NAME => 'PQP_INLAND_REV_AUTH_MILEAGE_RATES'
700 ,P_COLUMN_NAME => '9999'
701 ,P_ROW_NAME => '10000'
702 ,P_VALUE => low_band_iram_rate
703 ,P_ERROR_MSG => err_msg );
704 ret := PQP_UTILITIES.PQP_GB_GET_TABLE_VALUE
705 (P_BUSINESS_GROUP_ID => NULL
706 ,P_EFFECTIVE_DATE => sysdate
707 ,P_TABLE_NAME => 'PQP_INLAND_REV_AUTH_MILEAGE_RATES'
708 ,P_COLUMN_NAME => '9999'
709 ,P_ROW_NAME => '99999'
710 ,P_VALUE => high_band_iram_rate
711 ,P_ERROR_MSG => err_msg );
712 calculated_ni_amt := car_prc_amt -(to_number(ni_rate) * car_tot_miles);
713 IF car_tot_miles > 10000 THEN
714 calculated_tax_amt := car_prc_amt - ((car_tot_miles - 10000) * to_number(high_band_iram_rate) + 10000 * to_number(low_band_iram_rate));
715 ELSE
716 calculated_tax_amt := car_prc_amt - (car_tot_miles * to_number(low_band_iram_rate));
717 END IF;
718 IF calculated_ni_amt < 0 THEN
719 calculated_ni_amt := 0;
720 END IF;
721 IF calculated_tax_amt < 0 THEN
722 calculated_tax_amt := 0;
723 END IF;
724 car_ni_diff := car_ni_amt - calculated_ni_amt;
725 car_tax_diff := car_tax_amt - calculated_tax_amt;
726 IF l_count1 <>0 THEN
727 g_sum_bal_info(l_count1).NI_Amt := g_sum_bal_info(l_count1).NI_Amt - car_ni_diff;
728 g_sum_bal_info(l_count1).Taxable_Amt := g_sum_bal_info(l_count1).Taxable_Amt - car_tax_diff;
729 ELSE
730 g_sum_bal_info(l_count4).NI_Amt := g_sum_bal_info(l_count4).NI_Amt - car_ni_diff;
731 g_sum_bal_info(l_count4).Taxable_Amt := g_sum_bal_info(l_count4).Taxable_Amt - car_tax_diff;
732 END IF;
733 END IF;
734
735 IF mc_tot_miles <> 0 THEN
736 -- Calculation for MotorCycle NI Correction
737 current_mc_ni_amt := get_balance_value(p_bal_info(1).assignment_id,'Motorcycle NI Even Amt');
738 current_mc_tax_amt := get_balance_value(p_bal_info(1).assignment_id,'Motorcycle Taxable Even Amt');
739 current_mc_prc_amt := get_balance_value(p_bal_info(1).assignment_id,'Motorcycle Casual Even Processed Amt')
740 + get_balance_value(p_bal_info(1).assignment_id,'Motorcycle Essential Even Processed Amt');
741 current_mc_tot_miles := get_balance_value(p_bal_info(1).assignment_id,'Motorcycle Casual Even Actual Miles')
742 + get_balance_value(p_bal_info(1).assignment_id,'Motorcycle Essential Even Actual Miles');
743 mc_ni_amt := mc_ni_amt + current_mc_ni_amt;
744 mc_tax_amt := mc_tax_amt + current_mc_tax_amt;
745 mc_prc_amt := mc_prc_amt + current_mc_prc_amt;
746 mc_tot_miles := mc_tot_miles + current_mc_tot_miles;
747 ret := PQP_UTILITIES.PQP_GB_GET_TABLE_VALUE
748 (P_BUSINESS_GROUP_ID => NULL
749 ,P_EFFECTIVE_DATE => sysdate
750 ,P_TABLE_NAME => 'PQP_NIC_MILEAGE_RATES'
751 ,P_COLUMN_NAME => '9999'
752 ,P_ROW_NAME => 'MOTOR CYCLE'
753 ,P_VALUE => ni_rate
754 ,P_ERROR_MSG => err_msg );
755 ret := PQP_UTILITIES.PQP_GB_GET_TABLE_VALUE
756 (P_BUSINESS_GROUP_ID => NULL
757 ,P_EFFECTIVE_DATE => sysdate
758 ,P_TABLE_NAME => 'PQP_INLAND_REV_AUTH_MILEAGE_RATES'
759 ,P_COLUMN_NAME => '9999'
760 ,P_ROW_NAME => 'MOTOR CYCLE'
761 ,P_VALUE => low_band_iram_rate
762 ,P_ERROR_MSG => err_msg );
763 calculated_ni_amt := mc_prc_amt -(to_number(ni_rate) * mc_tot_miles);
764 calculated_tax_amt := mc_prc_amt -(to_number(low_band_iram_rate) * mc_tot_miles);
765 IF calculated_ni_amt < 0 THEN
766 calculated_ni_amt := 0;
767 END IF;
768 IF calculated_tax_amt < 0 THEN
769 calculated_tax_amt := 0;
770 END IF;
771 mc_ni_diff := mc_ni_amt - calculated_ni_amt;
772 mc_tax_diff := mc_tax_amt - calculated_tax_amt;
773 IF l_count2 <>0 THEN
774 g_sum_bal_info(l_count2).NI_Amt := g_sum_bal_info(l_count2).NI_Amt - mc_ni_diff;
775 g_sum_bal_info(l_count2).Taxable_Amt := g_sum_bal_info(l_count2).Taxable_Amt - mc_tax_diff;
776 ELSE
777 g_sum_bal_info(l_count5).NI_Amt := g_sum_bal_info(l_count5).NI_Amt - mc_ni_diff;
778 g_sum_bal_info(l_count5).Taxable_Amt := g_sum_bal_info(l_count5).Taxable_Amt - mc_tax_diff;
779 END IF;
780 END IF;
781
782
783 IF pc_tot_miles <> 0 THEN
784 -- Calculation for PedalCycle NI Correction
785 current_pc_ni_amt := get_balance_value(p_bal_info(1).assignment_id,'Pedalcycle NI Even Amt');
786 current_pc_tax_amt := get_balance_value(p_bal_info(1).assignment_id,'Pedalcycle Taxable Even Amt');
787 current_pc_prc_amt := get_balance_value(p_bal_info(1).assignment_id,'Pedalcycle Casual Even Processed Amt')
788 + get_balance_value(p_bal_info(1).assignment_id,'Pedalcycle Essential Even Processed Amt');
789 current_pc_tot_miles := get_balance_value(p_bal_info(1).assignment_id,'Pedalcycle Casual Even Actual Miles')
790 + get_balance_value(p_bal_info(1).assignment_id,'Pedalcycle Essential Even Actual Miles');
791 pc_ni_amt := pc_ni_amt + current_pc_ni_amt;
792 pc_tax_amt := pc_tax_amt + current_pc_tax_amt;
793 pc_prc_amt := pc_prc_amt + current_pc_prc_amt;
794 pc_tot_miles := pc_tot_miles + current_pc_tot_miles;
795 ret := PQP_UTILITIES.PQP_GB_GET_TABLE_VALUE
796 (P_BUSINESS_GROUP_ID => NULL
797 ,P_EFFECTIVE_DATE => sysdate
798 ,P_TABLE_NAME => 'PQP_NIC_MILEAGE_RATES'
799 ,P_COLUMN_NAME => '9999'
800 ,P_ROW_NAME => 'PEDAL CYCLE'
801 ,P_VALUE => ni_rate
802 ,P_ERROR_MSG => err_msg );
803 ret := PQP_UTILITIES.PQP_GB_GET_TABLE_VALUE
804 (P_BUSINESS_GROUP_ID => NULL
805 ,P_EFFECTIVE_DATE => sysdate
806 ,P_TABLE_NAME => 'PQP_INLAND_REV_AUTH_MILEAGE_RATES'
807 ,P_COLUMN_NAME => '9999'
808 ,P_ROW_NAME => 'PEDAL CYCLE'
809 ,P_VALUE => low_band_iram_rate
810 ,P_ERROR_MSG => err_msg );
811 calculated_ni_amt := pc_prc_amt -(to_number(ni_rate) * pc_tot_miles);
812 calculated_tax_amt := pc_prc_amt -(to_number(low_band_iram_rate) * pc_tot_miles);
813 IF calculated_ni_amt < 0 THEN
814 calculated_ni_amt := 0;
815 END IF;
816 IF calculated_tax_amt < 0 THEN
817 calculated_tax_amt := 0;
818 END IF;
819 pc_ni_diff := pc_ni_amt - calculated_ni_amt;
820 pc_tax_diff := pc_tax_amt - calculated_tax_amt;
821 IF l_count3 <>0 THEN
822 g_sum_bal_info(l_count3).NI_Amt := g_sum_bal_info(l_count3).NI_Amt - pc_ni_diff;
823 g_sum_bal_info(l_count3).Taxable_Amt := g_sum_bal_info(l_count3).Taxable_Amt - pc_tax_diff;
824 ELSE
825 g_sum_bal_info(l_count6).NI_Amt := g_sum_bal_info(l_count6).NI_Amt - pc_ni_diff;
826 g_sum_bal_info(l_count6).Taxable_Amt := g_sum_bal_info(l_count6).Taxable_Amt - pc_tax_diff;
827 END IF;
828 END IF;
829
830 -- Add the Previous Year's NI balances(Which are processed in the current year) to the
831 -- Vehicle Type NI balances
832 IF l_count1 <>0 THEN
833 g_sum_bal_info(l_count1).NI_Amt := g_sum_bal_info(l_count1).NI_Amt + prev_yr_car_ni_amt;
834 ELSIF l_count4 <> 0 THEN
835 g_sum_bal_info(l_count4).NI_Amt := g_sum_bal_info(l_count4).NI_Amt + prev_yr_car_ni_amt;
836 END IF;
837 IF l_count2 <>0 THEN
838 g_sum_bal_info(l_count2).NI_Amt := g_sum_bal_info(l_count2).NI_Amt + prev_yr_mc_ni_amt;
839 ELSIF l_count5 <> 0 THEN
840 g_sum_bal_info(l_count5).NI_Amt := g_sum_bal_info(l_count5).NI_Amt + prev_yr_mc_ni_amt;
841 END IF;
842 IF l_count3 <>0 THEN
843 g_sum_bal_info(l_count3).NI_Amt := g_sum_bal_info(l_count3).NI_Amt + prev_yr_pc_ni_amt;
844 ELSIF l_count6 <> 0 THEN
845 g_sum_bal_info(l_count6).NI_Amt := g_sum_bal_info(l_count6).NI_Amt + prev_yr_pc_ni_amt;
846 END IF;
847
848 route_balance_amt;
849 END;
850
851
852
853
854 FUNCTION get_payroll_det (p_assignment_id IN NUMBER
855 ,p_business_group_id IN NUMBER
856 ,p_payroll_id OUT NOCOPY NUMBER
857 ,p_consolidation_set_id OUT NOCOPY NUMBER
858 )
859 RETURN DATE
860 IS
861
862 --Gets Payroll Id and Consolidation Set Id
863 CURSOR c_get_payroll_det (cp_assignment_id NUMBER
864 ,cp_business_group_id NUMBER
865 ,cp_max_date DATE)
866 IS
867 SELECT ppa.payroll_id
868 ,ppa.consolidation_set_id
869 FROM pay_assignment_actions paa
870 ,pay_payroll_actions ppa
871 WHERE paa.assignment_id= cp_assignment_id
872 AND ppa.payroll_action_id=paa.payroll_action_id
873 AND ppa.effective_date = cp_max_date
874 AND ppa.business_group_id= cp_business_group_id
875 AND ppa.action_type in ('R','Q')
876 AND ppa.action_status='C'
877 AND paa.action_status='C'
878 AND paa.run_type_id IS NOT NULL;
879
880
881 --Gets latest payroll run date for the assignment
882 CURSOR c_get_max_date (cp_assignment_id NUMBER
883 ,cp_business_group_id NUMBER
884 )
885 IS
886 SELECT max(ppa.effective_date) effective_date
887 FROM pay_payroll_actions ppa
888 ,pay_assignment_actions paa
889 WHERE paa.assignment_id= cp_assignment_id
890 AND ppa.payroll_action_id=paa.payroll_action_id
891 AND ppa.business_group_id= cp_business_group_id
892 AND ppa.action_type in ('R','Q')
893 AND ppa.action_status='C'
894 AND paa.action_status='C'
895 AND paa.run_type_id IS NOT NULL;
896
897
898 l_get_payroll_det c_get_payroll_det%ROWTYPE;
899 l_effective_date DATE;
900 l_proc varchar2(72) ;--:= g_package ||'get_effective_date';
901 BEGIN
902 hr_utility.set_location(l_proc,10);
903 OPEN c_get_max_date ( p_assignment_id
904 ,p_business_group_id
905 );
906 FETCH c_get_max_date INTO l_effective_date;
907 CLOSE c_get_max_date;
908 OPEN c_get_payroll_det ( p_assignment_id
909 ,p_business_group_id
910 ,l_effective_date
911 );
912 FETCH c_get_payroll_det INTO l_get_payroll_det;
913 hr_utility.set_location(l_proc,20);
914 CLOSE c_get_payroll_det;
915 p_payroll_id :=l_get_payroll_det.payroll_id;
916 p_consolidation_set_id := l_get_payroll_det.consolidation_set_id;
917 RETURN(l_effective_date);
918 hr_utility.set_location(l_proc,30);
919 END;
920
921 -- This function returns the element_type_id given the Element Name
922 FUNCTION get_element_id (p_business_group_id IN NUMBER
923 ,p_element_name IN VARCHAR2
924 ,p_effective_date IN DATE
925 )
926 RETURN NUMBER
927 IS
928
929 Cursor c_element_type is
930 select element_type_id
931 from pay_element_types_f
932 where element_name = p_element_name
933 and business_group_id = p_business_group_id
934 and p_effective_date between effective_start_date
935 and effective_end_date;
936 l_element_id pay_element_types_f.element_type_id%TYPE;
937 BEGIN
938
939 Open c_element_type;
940 Fetch c_element_type into l_element_id;
941 Close c_element_type;
942
943 RETURN l_element_id;
944 END;
945
946 -- This function returns the element_link_id given the Element Type Id and Assignment Id
947 FUNCTION get_element_link
948 (p_assignment_id IN NUMBER
949 ,p_business_group_id IN NUMBER
950 ,p_element_id IN NUMBER
951 ,p_effective_date IN DATE
952 )
953 RETURN NUMBER
954 IS
955 l_element_link_id pay_element_links_f.element_link_id%TYPE;
956
957 BEGIN
958
959 l_element_link_id := hr_entry_api.get_link(
960 p_assignment_id,
961 p_element_id,
962 p_effective_date);
963
964 RETURN l_element_link_id;
965 END get_element_link;
966
967
968
969 -- This procedure creates the balance adjustment entry
970 PROCEDURE create_element_entry
971 ( p_effective_date IN DATE
972 ,p_business_group_id IN NUMBER
973 ,p_assignment_id IN NUMBER
974 ,p_element_name IN VARCHAR2
975 ,p_base_element_name IN VARCHAR2
976 ,p_entry_value1 IN VARCHAR2
977 ,p_entry_value2 IN VARCHAR2
978 ,p_entry_value3 IN VARCHAR2
979 ,p_entry_value4 IN VARCHAR2
980 ,p_entry_value5 IN VARCHAR2
981 ,p_entry_value6 IN VARCHAR2
982 ,p_entry_value7 IN VARCHAR2
983 ,p_entry_value8 IN VARCHAR2
984 ,p_entry_value9 IN VARCHAR2
985 ,p_entry_value10 IN VARCHAR2
986 ,p_entry_value11 IN VARCHAR2
987 ,p_entry_value12 IN VARCHAR2
988 ,p_entry_value13 IN VARCHAR2
989 ,p_entry_value14 IN VARCHAR2
990 ,p_entry_value15 IN VARCHAR2
991 )
992 AS
993
994 l_element_name pay_element_types_f.element_name%TYPE;
995 l_element_type_id pay_element_types_f.element_type_id%TYPE := NULL;
996 l_base_element_type_id pay_element_types_f.element_type_id%TYPE;
997 l_input_val t_input_val;
998 l_element_link_id pay_element_links_f.element_link_id%TYPE;
999 l_row_id VARCHAR2(60);
1000 l_effective_start_date DATE;
1001 l_effective_end_date DATE;
1002 l_element_entry_id NUMBER;
1003 l_object_version_number NUMBER;
1004 l_create_warning BOOLEAN;
1005 l_err_count NUMBER;
1006 l_cache_count NUMBER;
1007
1008
1009
1010 -- Get the Input Value Ids for a given Element Type Id
1011 CURSOR c_get_input_val_id (cp_element_id NUMBER)
1012 IS
1013 SELECT piv.input_value_id
1014 ,piv.display_sequence
1015 ,piv.name
1016 FROM pay_input_values_f piv
1017 WHERE piv.element_type_id =cp_element_id
1018 AND p_effective_date BETWEEN piv.effective_start_date
1019 AND piv.effective_end_date
1020 ORDER BY piv.display_sequence;
1021
1022 l_consol_set_id pay_payroll_actions.consolidation_set_id%TYPE;
1023 --l_base_ele_det c_base_ele_lnk_det%ROWTYPE;
1024 l_get_input_val_id c_get_input_val_id%ROWTYPE;
1025 l_count number:=0;
1026 l_payroll_id per_all_assignments_f.payroll_id%TYPE;
1027 l_effective_date DATE;
1028 BEGIN
1029
1030 --Check the Payroll Cache for Payroll Details
1031 IF g_payroll_det_cache.count > 0 THEN
1032 l_effective_date := g_payroll_det_cache(1).effective_date;
1033 l_payroll_id := g_payroll_det_cache(1).payroll_id;
1034 l_consol_set_id := g_payroll_det_cache(1).consolidation_set_id;
1035 ELSE
1036 l_effective_date :=get_payroll_det
1037 (p_assignment_id =>p_assignment_id
1038 ,p_business_group_id =>p_business_group_id
1039 ,p_payroll_id =>l_payroll_id
1040 ,p_consolidation_set_id =>l_consol_set_id
1041 );
1042 -- Enter the Entry into the cache
1043 -- At any point of time there needs to be only one record in the table
1044 -- That is because we are processing the records assignment wise
1045 -- Therefore once the assignment is processed the table will be emptied
1046 -- and a new entry will be made in the cache when processing the next assignment
1047 g_payroll_det_cache(1).assignment_id := p_assignment_id;
1048 g_payroll_det_cache(1).business_group_id := p_business_group_id;
1049 g_payroll_det_cache(1).payroll_id := l_payroll_id;
1050 g_payroll_det_cache(1).consolidation_set_id := l_consol_set_id;
1051 g_payroll_det_cache(1).effective_date := l_effective_date;
1052 END IF;
1053
1054 hr_utility.set_location('Entering Query for Element Id: '||p_element_name,dbms_utility.get_time);
1055
1056 --Check the Element Cache for Element Type Id
1057 FOR i in 1..g_element_cache.count
1058 LOOP
1059 IF g_element_cache(i).element_name = p_element_name
1060 and g_element_cache(i).business_group_id = p_business_group_id
1061 and g_element_cache(i).effective_date = p_effective_date THEN
1062 l_element_type_id := g_element_cache(i).element_type_id;
1063 exit;
1064 END IF;
1065 END LOOP;
1066
1067 IF l_element_type_id IS NULL THEN -- Not Found in Cache
1068 l_element_type_id :=get_element_id
1069 (p_business_group_id =>p_business_group_id
1070 ,p_element_name =>p_element_name
1071 ,p_effective_date =>p_effective_date
1072 );
1073 -- Enter the Entry into the cache
1074 l_cache_count := g_element_cache.count+1;
1075 g_element_cache(l_cache_count).element_name := p_element_name;
1076 g_element_cache(l_cache_count).business_group_id := p_business_group_id;
1077 g_element_cache(l_cache_count).effective_date := p_effective_date;
1078 g_element_cache(l_cache_count).element_type_id := l_element_type_id;
1079 END IF;
1080
1081 l_base_element_type_id :=get_element_id
1082 (p_business_group_id =>p_business_group_id
1083 ,p_element_name =>p_base_element_name
1084 ,p_effective_date =>p_effective_date
1085 );
1086
1087 --Check the Cache For Input value Ids corresponding the Element Type
1088 FOR i in 1..g_input_val_cache.count
1089 LOOP
1090 IF g_input_val_cache(i).element_type_id = l_element_type_id THEN
1091 l_input_val(1).input_value_id := g_input_val_cache(i).input_val_id1;
1092 l_input_val(2).input_value_id := g_input_val_cache(i).input_val_id2;
1093 l_input_val(3).input_value_id := g_input_val_cache(i).input_val_id3;
1094 l_input_val(4).input_value_id := g_input_val_cache(i).input_val_id4;
1095 l_input_val(5).input_value_id := g_input_val_cache(i).input_val_id5;
1096 l_input_val(6).input_value_id := g_input_val_cache(i).input_val_id6;
1097 l_input_val(7).input_value_id := g_input_val_cache(i).input_val_id7;
1098 l_input_val(8).input_value_id := g_input_val_cache(i).input_val_id8;
1099 l_input_val(9).input_value_id := g_input_val_cache(i).input_val_id9;
1100 l_input_val(10).input_value_id := g_input_val_cache(i).input_val_id10;
1101 l_input_val(11).input_value_id := g_input_val_cache(i).input_val_id11;
1102 l_input_val(12).input_value_id := g_input_val_cache(i).input_val_id12;
1103 l_input_val(13).input_value_id := g_input_val_cache(i).input_val_id13;
1104 l_input_val(14).input_value_id := g_input_val_cache(i).input_val_id14;
1105 l_input_val(15).input_value_id := g_input_val_cache(i).input_val_id15;
1106 exit;
1107 END IF;
1108 END LOOP;
1109
1110 IF l_input_val.count = 0 THEN --Entry Not Present in Cache
1111 OPEN c_get_input_val_id(l_element_type_id);
1112 LOOP
1113 FETCH c_get_input_val_id INTO l_get_input_val_id;
1114 EXIT WHEN c_get_input_val_id%NOTFOUND;
1115 IF l_count=0 and l_get_input_val_id.display_sequence=2 THEN
1116 l_input_val(1).input_value_id :=NULL;
1117 l_input_val(2).input_value_id :=l_get_input_val_id.input_value_id;
1118 l_count:=1;
1119 ELSE
1120 l_count:=l_get_input_val_id.display_sequence;
1121 l_input_val(l_get_input_val_id.display_sequence).input_value_id
1122 :=l_get_input_val_id.input_value_id;
1123 END IF;
1124 END LOOP;
1125 CLOSE c_get_input_val_id;
1126 IF l_input_val.count < 15 THEN
1127 FOR i in l_input_val.count+1..15
1128 LOOP
1129 l_input_val(i).input_value_id :=NULL;
1130 END LOOP;
1131 END IF;
1132
1133 -- Need to create a entry in the Cache
1134 l_cache_count := g_input_val_cache.count+1;
1135 g_input_val_cache(l_cache_count).input_val_id1 := l_input_val(1).input_value_id;
1136 g_input_val_cache(l_cache_count).input_val_id2 := l_input_val(2).input_value_id;
1137 g_input_val_cache(l_cache_count).input_val_id3 := l_input_val(3).input_value_id;
1138 g_input_val_cache(l_cache_count).input_val_id4 := l_input_val(4).input_value_id;
1139 g_input_val_cache(l_cache_count).input_val_id5 := l_input_val(5).input_value_id;
1140 g_input_val_cache(l_cache_count).input_val_id6 := l_input_val(6).input_value_id;
1141 g_input_val_cache(l_cache_count).input_val_id7 := l_input_val(7).input_value_id;
1142 g_input_val_cache(l_cache_count).input_val_id8 := l_input_val(8).input_value_id;
1143 g_input_val_cache(l_cache_count).input_val_id9 := l_input_val(9).input_value_id;
1144 g_input_val_cache(l_cache_count).input_val_id10 := l_input_val(10).input_value_id;
1145 g_input_val_cache(l_cache_count).input_val_id11 := l_input_val(11).input_value_id;
1146 g_input_val_cache(l_cache_count).input_val_id12 := l_input_val(12).input_value_id;
1147 g_input_val_cache(l_cache_count).input_val_id13 := l_input_val(13).input_value_id;
1148 g_input_val_cache(l_cache_count).input_val_id14 := l_input_val(14).input_value_id;
1149 g_input_val_cache(l_cache_count).input_val_id15 := l_input_val(15).input_value_id;
1150
1151
1152 END IF;
1153
1154
1155 /*OPEN c_base_ele_lnk_det(l_base_element_type_id);
1156 FETCH c_base_ele_lnk_det INTO l_base_ele_det;
1157 CLOSE c_base_ele_lnk_det;*/
1158 l_element_link_id:=get_element_link
1159 (p_assignment_id =>p_assignment_id
1160 ,p_business_group_id =>p_business_group_id
1161 ,p_element_id =>l_element_type_id
1162 ,p_effective_date =>l_effective_date
1163 );
1164
1165 hr_utility.set_location('Exiting Function Call for Element Link: '||l_element_link_id,dbms_utility.get_time);
1166
1167 if l_element_type_id is not null then
1168 l_effective_start_date := NULL;
1169 l_effective_end_date := NULL;
1170 hr_utility.set_location('Entering Function to Create Adjustment: '||l_effective_date,dbms_utility.get_time);
1171 -- Create the Balance Adjustment Entry
1172 pay_balance_adjustment_api.create_adjustment(
1173 p_effective_date =>l_effective_date
1174 ,p_assignment_id =>p_assignment_id
1175 ,p_consolidation_set_id =>l_consol_set_id
1176 ,p_element_link_id =>l_element_link_id
1177 --,p_entry_type =>'E'
1178 ,p_input_value_id1 =>l_input_val(1).input_value_id
1179 ,p_input_value_id2 =>l_input_val(2).input_value_id
1180 ,p_input_value_id3 =>l_input_val(3).input_value_id
1181 ,p_input_value_id4 =>l_input_val(4).input_value_id
1182 ,p_input_value_id5 =>l_input_val(5).input_value_id
1183 ,p_input_value_id6 =>l_input_val(6).input_value_id
1184 ,p_input_value_id7 =>l_input_val(7).input_value_id
1185 ,p_input_value_id8 =>l_input_val(8).input_value_id
1186 ,p_input_value_id9 =>l_input_val(9).input_value_id
1187 ,p_input_value_id10 =>l_input_val(10).input_value_id
1188 ,p_input_value_id11 =>l_input_val(11).input_value_id
1189 ,p_input_value_id12 =>l_input_val(12).input_value_id
1190 ,p_input_value_id13 =>l_input_val(13).input_value_id
1191 ,p_input_value_id14 =>l_input_val(14).input_value_id
1192 ,p_input_value_id15 =>l_input_val(15).input_value_id
1193 ,p_entry_value1 =>p_entry_value1
1194 ,p_entry_value2 =>p_entry_value2
1195 ,p_entry_value3 =>p_entry_value3
1196 ,p_entry_value4 =>p_entry_value4
1197 ,p_entry_value5 =>p_entry_value5
1198 ,p_entry_value6 =>p_entry_value6
1199 ,p_entry_value7 =>p_entry_value7
1200 ,p_entry_value8 =>p_entry_value8
1201 ,p_entry_value9 =>p_entry_value9
1202 ,p_entry_value10 =>p_entry_value10
1203 ,p_entry_value11 =>p_entry_value11
1204 ,p_entry_value12 =>p_entry_value12
1205 ,p_entry_value13 =>p_entry_value13
1206 ,p_entry_value14 =>p_entry_value14
1207 ,p_entry_value15 =>p_entry_value15
1208 ,p_element_entry_id =>l_element_entry_id
1209 ,p_effective_start_date =>l_effective_start_date
1210 ,p_effective_end_date =>l_effective_end_date
1211 ,p_object_version_number =>l_object_version_number
1212 ,p_create_warning =>l_create_warning
1213 );
1214
1215 end if;
1216 adjustment_entry_count := adjustment_entry_count + 1;
1217
1218 EXCEPTION
1219 ---------
1220 WHEN OTHERS THEN
1221 l_err_count := g_err_info.count;
1222 g_err_info(l_err_count+1).element_name := p_element_name;
1223 g_err_info(l_err_count+1).business_group_id := p_business_group_id;
1224 g_err_info(l_err_count+1).assignment_id := p_assignment_id;
1225
1226 END create_element_entry;
1227
1228 /*** This Function is used to get the balance values for Vehicle Type Ni amt and
1229 Vehicle Type Taxable Amt. ***/
1230
1231 FUNCTION get_balance_value ( p_assignment_id IN NUMBER
1232 ,p_balance_name IN VARCHAR2
1233 )
1234 return NUMBER
1235 IS
1236 CURSOR c_get_balance_det
1237 IS
1238 SELECT balance_name,balance_type_id
1239 FROM pay_balance_types
1240 WHERE balance_name = p_balance_name;
1241
1242 CURSOR c_assignment_action_id
1243 IS
1244 select max(assignment_action_id)
1245 from pay_assignment_actions
1246 where assignment_id = p_assignment_id;
1247
1248 l_assignment_action_id NUMBER;
1249
1250 cursor c_get_balance_val(cp_balance_type_id NUMBER
1251 ,cp_assignment_action_id NUMBER)
1252 is
1253 select /*+ RULE*/ nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) *
1254 FEED.scale),0) tot
1255 from
1256 pay_balance_feeds_f FEED
1257 ,pay_run_result_values TARGET
1258 ,pay_run_results RR
1259 ,pay_payroll_actions PACT
1260 ,pay_assignment_actions ASSACT
1261 ,pay_payroll_actions BACT
1262 ,per_time_periods BPTP
1263 ,per_time_periods PPTP
1264 ,pay_assignment_actions BAL_ASSACT
1265 ,per_assignments_f ASS
1266 ,per_assignments_f START_ASS
1267 WHERE BAL_ASSACT.assignment_action_id = cp_assignment_action_id
1268 AND BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
1269 AND FEED.balance_type_id = cp_balance_type_id
1270 AND FEED.input_value_id = TARGET.input_value_id
1271 AND TARGET.run_result_id = RR.run_result_id
1272 AND RR.assignment_action_id = ASSACT.assignment_action_id
1273 AND ASSACT.payroll_action_id = PACT.payroll_action_id
1274 AND PACT.effective_date BETWEEN
1275 FEED.effective_start_date AND FEED.effective_end_date
1276 AND RR.status in ('P','PA')
1277 AND BPTP.time_period_id = BACT.time_period_id
1278 AND PPTP.time_period_id = PACT.time_period_id
1279
1280 AND START_ASS.assignment_id = BAL_ASSACT.assignment_id
1281 AND ASS.period_of_service_id = START_ASS.period_of_service_id
1282 AND ASSACT.assignment_id = ASS.assignment_id
1283 AND BACT.effective_date BETWEEN
1284 START_ASS.effective_start_date AND START_ASS.effective_end_date
1285 AND PACT.effective_date BETWEEN
1286 ASS.effective_start_date AND ASS.effective_end_date
1287 AND PACT.effective_date >=
1288 /* find the latest td payroll transfer date - compare each of the */
1289 /* assignment rows with its predecessor looking for the payroll */
1290 /* that had a different tax district at that date */
1291 (SELECT nvl(max(NASS.effective_start_date),
1292 to_date('01/01/0001', 'DD/MM/YYYY'))
1293
1294 FROM per_assignments_f NASS
1295 ,pay_payrolls_f ROLL
1296 ,hr_soft_coding_keyflex FLEX
1297 ,per_assignments_f PASS
1298 ,pay_payrolls_f PROLL
1299 ,hr_soft_coding_keyflex PFLEX
1300 WHERE NASS.assignment_id = ASS.assignment_id
1301 AND ROLL.payroll_id = NASS.payroll_id
1302 AND NASS.effective_start_date BETWEEN
1303 ROLL.effective_start_date AND ROLL.effective_end_date
1304 AND ROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
1305 AND NASS.assignment_id = PASS.assignment_id
1306 AND PASS.effective_end_date = (NASS.effective_start_date - 1)
1307
1308 AND NASS.effective_start_date <= BACT.effective_date
1309 AND PROLL.payroll_id = PASS.payroll_id
1310 AND NASS.effective_start_date BETWEEN
1311 PROLL.effective_start_date AND PROLL.effective_end_date
1312 AND PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
1313 AND NASS.payroll_id <> PASS.payroll_id
1314 AND FLEX.segment1 <> PFLEX.segment1 )
1315 AND EXISTS
1316 /* check that the current assignment tax districts match */
1317 (SELECT NULL
1318 FROM pay_payrolls_f BROLL
1319 ,hr_soft_coding_keyflex BFLEX
1320 ,pay_payrolls_f PROLL
1321
1322 ,hr_soft_coding_keyflex PFLEX
1323 WHERE BACT.payroll_id = BROLL.payroll_id
1324 AND PACT.payroll_id = PROLL.payroll_id
1325 AND BFLEX.soft_coding_keyflex_id = BROLL.soft_coding_keyflex_id
1326 AND PFLEX.soft_coding_keyflex_id = PROLL.soft_coding_keyflex_id
1327 AND BACT.effective_date BETWEEN
1328 BROLL.effective_start_date AND BROLL.effective_end_date
1329 AND BACT.effective_date BETWEEN
1330 PROLL.effective_start_date AND PROLL.effective_end_date
1331 AND BFLEX.segment1 = PFLEX.segment1 )
1332 AND PPTP.regular_payment_date >=
1333 /* fin year start is last two years for a even tax year and last one
1334 * year for a odd tax year
1335
1336 */
1337 to_date('06-04-' || to_char( fnd_number.canonical_to_number(
1338 to_char( BPTP.regular_payment_date,'YYYY'))
1339 + decode(sign(BPTP.regular_payment_date - to_date('06-04-'
1340 || to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
1341 -1,-1,0) -
1342 mod(
1343 fnd_number.canonical_to_number(
1344 to_char( BPTP.regular_payment_date,'YYYY'))
1345 + decode(sign( BPTP.regular_payment_date - to_date('06-04-'
1346 || to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
1347 -1,0,-1),2)
1348 ),'DD-MM-YYYY')
1349
1350 AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence ;
1351
1352
1353
1354
1355 l_get_balance_det c_get_balance_det%ROWTYPE;
1356 l_get_balance_val c_get_balance_val%ROWTYPE;
1357 l_balance_type_id NUMBER := NULL;
1358 l_cache_count NUMBER;
1359 BEGIN
1360
1361 --Check the Balance Cache for Balance Type Id
1362 FOR i in 1..g_balance_cache.count
1363 LOOP
1364 IF g_balance_cache(i).balance_name = p_balance_name THEN
1365 l_balance_type_id := g_balance_cache(i).balance_type_id;
1366 exit;
1367 END IF;
1368 END LOOP;
1369
1370 IF l_balance_type_id IS NULL THEN
1371 OPEN c_get_balance_det;
1372 FETCH c_get_balance_det INTO l_get_balance_det;
1373 CLOSE c_get_balance_det;
1374 l_balance_type_id := l_get_balance_det.balance_type_id;
1375 -- Make An Entry in Balance Cache
1376 l_cache_count := g_balance_cache.count+1;
1377 g_balance_cache(l_cache_count).balance_name := p_balance_name;
1378 g_balance_cache(l_cache_count).balance_type_id := l_balance_type_id;
1379 END IF;
1380
1381 OPEN c_assignment_action_id;
1382 FETCH c_assignment_action_id into l_assignment_action_id;
1383 CLOSE c_assignment_action_id;
1384
1385 OPEN c_get_balance_val(l_balance_type_id
1386 ,l_assignment_action_id);
1387 FETCH c_get_balance_val INTO l_get_balance_val;
1388 return(NVL(l_get_balance_val.tot,0));
1389 CLOSE c_get_balance_val;
1390
1391
1392 return(0);
1393 END;
1394
1395
1396
1397 FUNCTION get_balance_value ( p_assignment_action_id IN NUMBER
1398 ,p_element_entry_id IN NUMBER
1399 ,p_business_group_id IN NUMBER
1400 ,p_payroll_action_id IN NUMBER
1401 ,p_balance_name IN VARCHAR2
1402 )
1403 return NUMBER
1404 IS
1405 CURSOR c_get_balance_det
1406 IS
1407 SELECT balance_name,balance_type_id
1408 FROM pay_balance_types
1409 WHERE balance_name = p_balance_name;
1410
1411 cursor c_get_balance_val(cp_balance_type_id NUMBER
1412 ,cp_assignment_action_id NUMBER
1413 ,cp_element_entry_id NUMBER
1414 ,cp_payroll_action_id NUMBER)
1415 is
1416 SELECT nvl((fnd_number.canonical_to_number(TARGET.result_value)
1417 * FEED.scale),0) tot
1418 FROM pay_run_result_values TARGET
1419 , pay_balance_feeds_f FEED
1420 , pay_run_results RR
1421 , pay_assignment_actions ASSACT
1422 , pay_assignment_actions BAL_ASSACT
1423 , pay_payroll_actions PACT
1424 WHERE BAL_ASSACT.assignment_action_id = cp_assignment_action_id
1425 AND FEED.balance_type_id = cp_balance_type_id
1426 AND FEED.input_value_id = TARGET.input_value_id
1427 AND TARGET.run_result_id = RR.run_result_id
1428 AND RR.assignment_action_id = ASSACT.assignment_action_id
1429 AND ASSACT.payroll_action_id = PACT.payroll_action_id
1430 AND assact.payroll_action_id = cp_payroll_action_id
1431 AND PACT.effective_date between FEED.effective_start_date
1432 AND FEED.effective_end_date
1433 AND RR.status in ('P','PA')
1434 AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
1435 AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
1436 AND (( RR.source_id = cp_element_entry_id and source_type in ( 'E','I'))
1437 OR ( rr.source_type in ('R','V') /* reversal */
1438 AND exists
1439 ( SELECT null from pay_run_results rr1
1440 WHERE rr1.source_id = cp_element_entry_id
1441 AND rr1.run_result_id = rr.source_id
1442 AND rr1.source_type in ( 'E','I'))));
1443
1444
1445
1446
1447 l_get_balance_det c_get_balance_det%ROWTYPE;
1448 l_get_balance_val c_get_balance_val%ROWTYPE;
1449 l_balance_type_id NUMBER := NULL;
1450 l_cache_count NUMBER;
1451 BEGIN
1452
1453 --Check the Balance Cache for Balance Type Id
1454 FOR i in 1..g_balance_cache.count
1455 LOOP
1456 IF g_balance_cache(i).balance_name = p_balance_name THEN
1457 l_balance_type_id := g_balance_cache(i).balance_type_id;
1458 exit;
1459 END IF;
1460 END LOOP;
1461
1462 IF l_balance_type_id IS NULL THEN
1463 OPEN c_get_balance_det;
1464 FETCH c_get_balance_det INTO l_get_balance_det;
1465 CLOSE c_get_balance_det;
1466 l_balance_type_id := l_get_balance_det.balance_type_id;
1467 -- Make An Entry in Balance Cache
1468 l_cache_count := g_balance_cache.count+1;
1469 g_balance_cache(l_cache_count).balance_name := p_balance_name;
1470 g_balance_cache(l_cache_count).balance_type_id := l_balance_type_id;
1471 END IF;
1472
1473 OPEN c_get_balance_val(l_balance_type_id
1474 ,p_assignment_action_id
1475 ,p_element_entry_id
1476 ,p_payroll_action_id);
1477 FETCH c_get_balance_val INTO l_get_balance_val;
1478 return(NVL(l_get_balance_val.tot,0));
1479 CLOSE c_get_balance_val;
1480
1481
1482 return(0);
1483 END;
1484
1485
1486 -------------------------------------------------------------
1487 PROCEDURE Initialize_Balances(p_business_group_id IN NUMBER)
1488 AS
1489 --This cursor gets all the information on mileage claims
1490 --necessary to initialize vehicle balances
1491 --here the logic to get the are based on the date passed to
1492 --the cursor as this date determines the date upto which the
1493 --adjustment has to be done.This date is compared with
1494 --creation date in pay payroll action table since the payroll
1495 --could be run for future dates.
1496 CURSOR c_get_info(cp_patch_status VARCHAR2
1497 ,cp_effective_date DATE)
1498 IS
1499 Select prr.source_id element_entry_id
1500 ,prr.assignment_action_id
1501 ,prr.element_type_id
1502 ,pet.element_name element_name
1503 ,pet.business_group_id business_group_id
1504 ,paa.assignment_id assignment_id
1505 ,paa.payroll_action_id
1506 ,petei.EEI_INFORMATION1 Vehicle_Type
1507 ,ppa.effective_date effective_start_date
1508 ,prr.run_result_id
1509 ,prr.source_type
1510 ,prr.source_id
1511 ,to_char(NULL) usage_type
1512 ,to_char(NULL) Ownership
1513 ,to_char(NULL) additional_passenger
1514 ,to_char(NULL) Paye_taxable
1515 ,to_date(NULL) Claim_end_date
1516 ,to_char(NULL) Rates_table
1517 ,to_number(NULL) Engine_capacity
1518 ,to_char(NULL) Calculation_Method
1519 ,to_number(NULL) Claimed_Mileage
1520 ,to_number(NULL) Actual_Mileage
1521 From pay_element_types_f pet
1522 ,pay_element_type_extra_info petei
1523 ,pay_assignment_actions paa
1524 ,pay_run_results prr
1525 ,pay_payroll_actions ppa
1526 WHERE pet.business_group_id = p_business_group_id
1527 AND pet.element_type_id=petei.element_type_id
1528 AND petei.information_type='PQP_VEHICLE_MILEAGE_INFO'
1529 AND petei.eei_information_category='PQP_VEHICLE_MILEAGE_INFO'
1530 AND petei.EEI_INFORMATION1 in ('C','P','CM','CP','PP','PM')
1531 AND prr.element_type_id=pet.element_type_id
1532 AND prr.assignment_action_id=paa.assignment_action_id
1533 AND ppa.payroll_action_id=paa.payroll_action_id
1534 AND ppa.business_group_id= pet.business_group_id
1535 AND ppa.effective_date >= to_date('04/06/2003','MM/DD/YYYY')
1536 AND TRUNC(ppa.creation_date) < cp_effective_date
1537 AND (cp_patch_status='N'
1538 OR Exists(SELECT 'X'
1539 FROM pay_us_rpt_totals
1540 WHERE state_name = 'CARMILEAGE_UPGRADE'
1541 AND tax_unit_id=250
1542 AND location_id = paa.assignment_id
1543 AND business_group_id = p_business_group_id))
1544 -- AND prr.source_type in ('E','R')
1545 ORDER BY paa.assignment_id,ppa.effective_date,prr.run_result_id;
1546
1547
1548 CURSOR c_get_input_values (cp_run_result_id NUMBER)
1549 IS
1550 SELECT piv.input_value_id
1551 ,piv.name
1552 ,prrv.result_value entry_value
1553 ,piv.display_sequence
1554 FROM pay_input_values_f piv
1555 ,pay_run_result_values prrv
1556 WHERE prrv.run_result_id = cp_run_result_id
1557 AND piv.input_value_id=prrv.input_value_id
1558 AND piv.name IN ('Vehicle Type'
1559 ,'Rate Type'
1560 ,'No of Passengers'
1561 ,'PAYE Taxable'
1562 ,'Claim End Date'
1563 ,'User Rates Table'
1564 ,'Engine Capacity'
1565 ,'Calculation Method'
1566 ,'Claimed Mileage'
1567 ,'Actual Mileage'
1568 );
1569 -- Get Previous Adjustment Process Status. If 'C' , then it is complete.
1570 -- If 'P' then it is Partially Processed. If No Entry exists then the adjustment
1571 -- Process hasn't been run before.
1572 CURSOR c_patch_status
1573 IS
1574 select status
1575 from pay_patch_status
1576 where patch_name = 'CARMILEAGE_BALANCE_ADJ'
1577 and patch_number = p_business_group_id
1578 and phase = 'CARMILEAGE_BALANCE_ADJ';
1579
1580 -- Get the Date of Upgrade.
1581 CURSOR c_upgrade_patch_status
1582 IS
1583 select update_date
1584 from pay_patch_status
1585 where patch_name = 'CARMILEAGE_UPDATE'
1586 and patch_number = -100;
1587
1588 l_update_date DATE;
1589 l_get_input_values c_get_input_values%ROWTYPE;
1590 l_get_info c_get_info%ROWTYPE;
1591 l_bal_info t_bal_info;
1592 l_count NUMBER :=0;
1593 l_assignment_id per_all_assignments_f.assignment_id%TYPE :=NULL;
1594 l_proc_miles NUMBER;
1595 l_act_miles NUMBER;
1596 l_proc_amt NUMBER;
1597 l_iram_amt NUMBER;
1598 l_effective_start_date DATE;
1599 l_effective_end_date DATE;
1600 l_element_entry_date DATE;
1601 l_element_entry_id NUMBER;
1602 l_old_element_entry_id NUMBER:= -1;
1603 l_object_version_number NUMBER;
1604 l_create_warning BOOLEAN;
1605 l_ret_val NUMBER;
1606 l_effective_date DATE;
1607 l_eng_capacity number;
1608 l_rates_table PAY_USER_TABLES.USER_TABLE_NAME%TYPE;
1609 l_calc_method VARCHAR2(1);
1610 l_err_msg VARCHAR2(80);
1611 l_fuel_type VARCHAR2(80);
1612 l_addl_pass_amt NUMBER(9,2);
1613 l_ni_amt NUMBER(9,2);
1614 l_tax_amt NUMBER(9,2);
1615 l_addl_ni_amt NUMBER(9,2);
1616 l_addl_tax_amt NUMBER(9,2);
1617 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
1618 l_tax_free_amt NUMBER(9,2);
1619 l_ni_free_amt NUMBER(9,2);
1620 l_lo_eng_cap NUMBER;
1621 l_hi_eng_cap NUMBER;
1622 l_mileage_band NUMBER;
1623 h_mileage_band NUMBER;
1624 l_band_rate NUMBER;
1625 h_band_rate NUMBER;
1626 l_status_count NUMBER:=1;
1627 total_paye_taxable_cl_miles NUMBER;
1628 --Pick all the element entries for that assignments which are fully or
1629 --Partially processed.
1630
1631
1632 ---Start summing Element level ITD balances for Processed Amt,Processed Miles,Actual Miles
1633 ---stripped by Vehicle Types.
1634
1635
1636 --Pick all the additional passenger element entries sum up the total
1637
1638 --Create element entries for the last Processed or partially processed and enter
1639 --these values to the input values.
1640
1641 BEGIN
1642
1643 OPEN c_patch_status;
1644 FETCH c_patch_status into l_prev_upgrade_status;
1645 CLOSE c_patch_status;
1646 --l_status_count keeps a count of the number of records in pay_patch_status
1647 --If 0 then it this the first run
1648 IF l_prev_upgrade_status is null THEN
1649 l_status_count := 0;
1650 END IF;
1651
1652 l_prev_upgrade_status := NVL(l_prev_upgrade_status,'N');
1653 hr_utility.set_location('Up Grade Status '||l_prev_upgrade_status,1);
1654
1655 IF l_prev_upgrade_status in ('N','P') THEN
1656 OPEN c_upgrade_patch_status;
1657 FETCH c_upgrade_patch_status INTO l_update_date;
1658 CLOSE c_upgrade_patch_status;
1659
1660 OPEN c_get_info(l_prev_upgrade_status
1661 ,l_update_date);
1662 LOOP
1663 FETCH c_get_info INTO l_get_info;
1664 EXIT WHEN c_get_info%NOTFOUND;
1665 OPEN c_get_input_values (l_get_info.run_result_id);
1666 LOOP
1667 FETCH c_get_input_values INTO l_get_input_values;
1668 EXIT WHEN c_get_input_values%NOTFOUND;
1669 IF l_get_input_values.name='Vehicle Type' OR
1670 l_get_input_values.name='Rate Type' THEN
1671 l_get_info.usage_type:=l_get_input_values.entry_value;
1672
1673 IF l_get_info.usage_type='E' OR l_get_info.usage_type='C' THEN
1674 l_get_info.ownership:='P';
1675 ELSIF l_get_info.usage_type='P' OR l_get_info.usage_type='S' THEN
1676 l_get_info.ownership:='C';
1677 END IF;
1678
1679 ELSIF l_get_input_values.name='Claim End Date' THEN
1680 l_get_info.Claim_End_Date:=
1681 fnd_date.canonical_to_date(l_get_input_values.entry_value);
1682
1683 ELSIF l_get_input_values.name='User Rates Table' THEN
1684 l_get_info.Rates_Table:=l_get_input_values.entry_value;
1685
1686 ELSIF l_get_input_values.name='Engine Capacity' THEN
1687 l_get_info.engine_capacity:=l_get_input_values.entry_value;
1688
1689 ELSIF l_get_input_values.name='Calculation Method' THEN
1690 l_get_info.calculation_method:=l_get_input_values.entry_value;
1691
1692 ELSIF l_get_input_values.name='No of Passengers' THEN
1693 l_get_info.additional_passenger:=l_get_input_values.entry_value;
1694
1695 ELSIF l_get_input_values.name='PAYE Taxable' THEN
1696 l_get_info.paye_taxable:=l_get_input_values.entry_value;
1697
1698 ELSIF l_get_input_values.name='Claimed Mileage' THEN
1699 l_get_info.Claimed_Mileage:=l_get_input_values.entry_value;
1700
1701 ELSIF l_get_input_values.name='Actual Mileage' THEN
1702 l_get_info.Actual_Mileage:=l_get_input_values.entry_value;
1703
1704 END IF;
1705
1706
1707
1708 END LOOP;
1709 CLOSE c_get_input_values;
1710
1711
1712 IF l_assignment_id IS NULL
1713 OR l_assignment_id <>l_get_info.assignment_id THEN
1714 IF l_assignment_id IS NOT NULL THEN
1715 --Sum up all the balances and create element entry for that assignment
1716 categorize_balances (p_bal_info => l_bal_info);
1717 l_bal_info.delete;
1718 END IF;
1719 comp_tot_paye_tax_cl_miles := 0;
1720 priv_tot_paye_tax_cl_miles := 0;
1721 prev_yr_car_ni_amt :=0;
1722 prev_yr_mc_ni_amt :=0;
1723 prev_yr_pc_ni_amt :=0;
1724 l_assignment_id :=l_get_info.assignment_id;
1725 END IF;
1726
1727 IF l_get_info.paye_Taxable = 'N' and l_get_info.Claim_End_Date >= to_date('04/06/2003','MM/DD/YYYY') THEN
1728
1729 --Call balance functions
1730 l_count:=l_bal_info.count;
1731 l_effective_date := TRUNC(pqp_car_mileage_functions.
1732 pqp_get_date_paid(l_get_info.payroll_action_id));
1733 --Get the Processed Miles for the particular Entry
1734 l_proc_miles :=pqp_clm_bal.get_vehicletype_balance
1735 (p_assignment_id =>l_get_info.assignment_id
1736 ,p_business_group_id =>l_get_info.business_group_id
1737 ,p_vehicle_type =>l_get_info.vehicle_type
1738 ,p_ownership =>l_get_info.ownership
1739 ,p_usage_type =>l_get_info.usage_type
1740 ,p_balance_name =>'Processed Miles'
1741 ,p_element_entry_id =>l_get_info.element_entry_id
1742 ,p_assignment_action_id =>l_get_info.assignment_action_id
1743 );
1744
1745 --Get the Actual Miles for the particular Entry
1746 l_act_miles :=pqp_clm_bal.get_vehicletype_balance
1747 (p_assignment_id =>l_get_info.assignment_id
1748 ,p_business_group_id =>l_get_info.business_group_id
1749 ,p_vehicle_type =>l_get_info.vehicle_type
1750 ,p_ownership =>l_get_info.ownership
1751 ,p_usage_type =>l_get_info.usage_type
1752 ,p_balance_name =>'Processed Actual Miles'
1753 ,p_element_entry_id =>l_get_info.element_entry_id
1754 ,p_assignment_action_id =>l_get_info.assignment_action_id
1755 );
1756
1757 -- This has been added because previous formula didn't return value to input value ITD_ACT_MILES
1758 -- for company claims. As a result Querying up balance for this value will return 0. Therefore for
1759 -- Company claims we use the run result value.
1760 IF l_get_info.ownership = 'C' THEN
1761 l_act_miles := l_get_info.Actual_Mileage;
1762 IF l_act_miles is NULL then
1763 l_act_miles := l_get_info.Claimed_Mileage;
1764 END IF;
1765 END IF;
1766
1767 --Get the Processed Amt for the particular Entry
1768 l_proc_amt :=pqp_clm_bal.get_vehicletype_balance
1769 (p_assignment_id =>l_get_info.assignment_id
1770 ,p_business_group_id =>l_get_info.business_group_id
1771 ,p_vehicle_type =>l_get_info.vehicle_type
1772 ,p_ownership =>l_get_info.ownership
1773 ,p_usage_type =>l_get_info.usage_type
1774 ,p_balance_name =>'Processed Amt'
1775 ,p_element_entry_id =>l_get_info.element_entry_id
1776 ,p_assignment_action_id =>l_get_info.assignment_action_id
1777 );
1778
1779 hr_utility.set_location('***** PROCESSED AMT: ',l_proc_amt);
1780
1781 --Get the IRAM Amt for the particular Entry
1782 l_iram_amt :=pqp_clm_bal.get_vehicletype_balance
1783 (p_assignment_id =>l_get_info.assignment_id
1784 ,p_business_group_id =>l_get_info.business_group_id
1785 ,p_vehicle_type =>l_get_info.vehicle_type
1786 ,p_ownership =>l_get_info.ownership
1787 ,p_usage_type =>l_get_info.usage_type
1788 ,p_balance_name =>'IRAM Amt'
1789 ,p_element_entry_id =>l_get_info.element_entry_id
1790 ,p_assignment_action_id =>l_get_info.assignment_action_id
1791 );
1792 IF l_get_info.additional_passenger <> 0 THEN
1793 l_ret_val:= pqp_car_mileage_functions.pqp_get_attr_val
1794 (p_assignment_id =>l_get_info.assignment_id
1795 ,p_business_group_id =>l_get_info.business_group_id
1796 ,p_payroll_action_id =>l_get_info.payroll_action_id
1797 ,p_car_type =>l_get_info.usage_type
1798 ,p_cc =>l_eng_capacity
1799 ,p_rates_table =>l_rates_table
1800 ,p_calc_method =>l_calc_method
1801 ,p_error_msg =>l_err_msg
1802 ,p_claim_date =>l_get_info.claim_end_date
1803 ,p_fuel_type =>l_fuel_type
1804 );
1805
1806 l_rates_table :=NVL(l_get_info.rates_table,l_rates_table);
1807 l_eng_capacity:=NVL(l_get_info.engine_capacity,l_eng_capacity);
1808 l_calc_method:=NVL(l_get_info.calculation_method,l_calc_method);
1809 IF l_get_info.source_type <> 'R' and l_get_info.source_type <> 'V' THEN
1810 IF l_get_info.ownership = 'C' THEN
1811 l_ret_val:=pqp_car_mileage_functions.pqp_get_addlpasg_rate
1812 (p_business_group_id =>l_get_info.business_group_id
1813 ,p_payroll_action_id =>l_get_info.payroll_action_id
1814 ,p_vehicle_type =>l_get_info.usage_type
1815 ,p_claimed_mileage =>abs(l_proc_miles)
1816 ,p_itd_miles =>0
1817 ,p_total_passengers =>abs(l_get_info.additional_passenger)
1818 ,p_total_pasg_itd_val =>0
1819 ,p_cc =>l_eng_capacity
1820 ,p_rates_table =>l_rates_table
1821 ,p_claim_end_date =>l_get_info.claim_end_date
1822 ,p_tax_free_amt =>l_addl_pass_amt
1823 ,p_ni_amt =>l_addl_ni_amt
1824 ,p_tax_amt =>l_addl_tax_amt
1825 ,p_err_msg =>l_err_msg
1826 );
1827 ELSE
1828 l_ret_val:=pqp_car_mileage_functions.pqp_get_passenger_rate
1829 (p_business_group_id =>l_get_info.business_group_id
1830 ,p_payroll_action_id =>l_get_info.payroll_action_id
1831 ,p_vehicle_type =>l_get_info.usage_type
1832 ,p_claimed_mileage =>abs(l_proc_miles)
1833 ,p_cl_itd_miles =>0
1834 ,p_actual_mileage =>abs(l_act_miles)
1835 ,p_ac_itd_miles =>0
1836 ,p_total_passengers =>abs(l_get_info.additional_passenger)
1837 ,p_total_pasg_itd_val =>0
1838 ,p_cc =>l_eng_capacity
1839 ,p_rates_table =>l_rates_table
1840 ,p_claim_end_date =>l_get_info.claim_end_date
1841 ,p_tax_free_amt =>l_addl_pass_amt
1842 ,p_ni_amt =>l_addl_ni_amt
1843 ,p_tax_amt =>l_addl_tax_amt
1844 ,p_err_msg =>l_err_msg
1845 );
1846 END IF;
1847 END IF;
1848 -- If the Run Result Entry is caused by Reverse Run then we need to find the
1849 -- the original entry and fine the processed values
1850 IF l_get_info.source_type = 'R' or l_get_info.source_type = 'V' THEN
1851 FOR i in 1..l_bal_info.count
1852 LOOP
1853 IF l_bal_info(i).run_result_id = l_get_info.source_id THEN
1854 l_addl_pass_amt := -l_bal_info(i).Addl_Pasg_Amt;
1855 l_addl_ni_amt := -l_bal_info(i).Addl_Ni_Amt;
1856 l_addl_tax_amt := -l_bal_info(i).Addl_Tax_Amt;
1857 EXIT;
1858 END IF;
1859 END LOOP;
1860 hr_utility.set_location('***** ADDL PROCESSED AMT: ',l_addl_pass_amt);
1861 END IF;
1862
1863 hr_utility.set_location('Addl Pass Amt **'||l_addl_pass_amt,3);
1864 l_bal_info(l_count+1).Addl_Pasg_Amt :=l_addl_pass_amt;
1865 l_bal_info(l_count+1).Addl_Ni_Amt :=l_addl_ni_amt;
1866 l_bal_info(l_count+1).Addl_Tax_Amt :=l_addl_tax_amt;
1867 l_bal_info(l_count+1).Addl_Pasg_Miles :=l_proc_miles;
1868 l_bal_info(l_count+1).Addl_Pasg_Act_Miles :=l_act_miles;
1869 END IF;
1870 --This section must be moved to sum proceure since we cannot get
1871 --itd level taxable amt which we may have to calculate.
1872 IF l_get_info.ownership='P' THEN
1873 l_ni_amt := get_balance_value
1874 (p_assignment_action_id => l_get_info.assignment_action_id
1875 ,p_element_entry_id => l_get_info.element_entry_id
1876 ,p_business_group_id => l_get_info.business_group_id
1877 ,p_payroll_action_id => l_get_info.payroll_action_id
1878 ,p_balance_name => 'Mileage Even Taxable Amt'
1879 );
1880 --Calculate Taxable amt ( In the Previous template , NI Amt and Taxable Amt got inter-changed )
1881 l_tax_amt := get_balance_value
1882 (p_assignment_action_id => l_get_info.assignment_action_id
1883 ,p_element_entry_id => l_get_info.element_entry_id
1884 ,p_business_group_id => l_get_info.business_group_id
1885 ,p_payroll_action_id => l_get_info.payroll_action_id
1886 ,p_balance_name => 'NI Even Amt'
1887 );
1888 l_bal_info(l_count+1).NI_Amt := l_ni_amt;
1889 l_bal_info(l_count+1).Taxable_Amt := l_tax_amt;
1890 END IF;
1891
1892
1893 --l_bal_info(l_count+1).NI_Amt :=0;
1894 --l_bal_info(l_count+1).Taxable_Amt :=0;
1895 l_bal_info(l_count+1).PAYE_Taxable :='N';
1896 l_bal_info(l_count+1).Ownership_Type :=l_get_info.ownership;
1897 l_bal_info(l_count+1).Vehicle_Type :=l_get_info.vehicle_type;
1898 l_bal_info(l_count+1).Usage_Type :=l_get_info.usage_type;
1899 l_bal_info(l_count+1).Element_Name :=l_get_info.element_name;
1900 l_bal_info(l_count+1).Processed_Miles :=l_proc_miles ;
1901 l_bal_info(l_count+1).Processed_Act_Miles :=l_act_miles;
1902 l_bal_info(l_count+1).Processed_Amt :=l_proc_amt;
1903 l_bal_info(l_count+1).IRAM_Amt :=l_iram_amt;
1904 l_bal_info(l_count+1).assignment_id :=l_get_info.assignment_id;
1905 l_bal_info(l_count+1).business_group_id :=l_get_info.business_group_id;
1906 l_bal_info(l_count+1).effective_date :=l_get_info.effective_start_date;
1907 l_bal_info(l_count+1).run_result_id :=l_get_info.run_result_id;
1908
1909
1910
1911 ELSIF l_get_info.element_entry_id <> l_old_element_entry_id
1912 AND l_get_info.Claim_End_Date >= to_date('04/06/2003','MM/DD/YYYY') THEN
1913 --If Entry is Paye Taxable , Amount to be paid will have to be calculated
1914 l_count:=l_bal_info.count;
1915 l_effective_date := TRUNC(pqp_car_mileage_functions.
1916 pqp_get_date_paid(l_get_info.payroll_action_id));
1917 /** Calculation of PAYE Taxable amount **/
1918
1919 IF l_get_info.source_type <> 'R' and l_get_info.source_type <> 'V'THEN
1920 l_ret_val:= pqp_car_mileage_functions.pqp_get_attr_val
1921 (p_assignment_id =>l_get_info.assignment_id
1922 ,p_business_group_id =>l_get_info.business_group_id
1923 ,p_payroll_action_id =>l_get_info.payroll_action_id
1924 ,p_car_type =>l_get_info.usage_type
1925 ,p_cc =>l_eng_capacity
1926 ,p_rates_table =>l_rates_table
1927 ,p_calc_method =>l_calc_method
1928 ,p_error_msg =>l_err_msg
1929 ,p_claim_date =>l_get_info.claim_end_date
1930 ,p_fuel_type =>l_fuel_type
1931 );
1932
1933 l_rates_table :=NVL(l_get_info.rates_table,l_rates_table);
1934 l_eng_capacity:=NVL(l_get_info.engine_capacity,l_eng_capacity);
1935 l_calc_method:=NVL(l_get_info.calculation_method,l_calc_method);
1936
1937
1938 l_ret_val:= pqp_car_mileage_functions.pqp_get_range
1939 (p_assignment_id => l_get_info.assignment_id
1940 ,p_business_group_id => l_get_info.business_group_id
1941 ,p_payroll_action_id =>l_get_info.payroll_action_id
1942 ,p_table_name =>l_rates_table
1943 ,p_row_or_column =>'COL'
1944 ,p_value => l_eng_capacity
1945 ,p_claim_date =>l_get_info.claim_end_date
1946 ,p_low_value =>l_lo_eng_cap
1947 ,p_high_value =>l_hi_eng_cap);
1948 IF l_get_info.ownership = 'C' THEN
1949 total_paye_taxable_cl_miles := comp_tot_paye_tax_cl_miles;
1950 comp_tot_paye_tax_cl_miles := comp_tot_paye_tax_cl_miles + l_get_info.Claimed_Mileage;
1951 ELSE
1952 total_paye_taxable_cl_miles := priv_tot_paye_tax_cl_miles;
1953 priv_tot_paye_tax_cl_miles := priv_tot_paye_tax_cl_miles + l_get_info.Claimed_Mileage;
1954 END IF;
1955
1956 l_ret_val:= pqp_car_mileage_functions.pqp_get_range
1957 (p_assignment_id => l_get_info.assignment_id
1958 ,p_business_group_id => l_get_info.business_group_id
1959 ,p_payroll_action_id =>l_get_info.payroll_action_id
1960 ,p_table_name =>l_rates_table
1961 ,p_row_or_column =>'ROW'
1962 ,p_value => l_get_info.Claimed_Mileage + total_paye_taxable_cl_miles
1963 ,p_claim_date =>l_get_info.claim_end_date
1964 ,p_low_value =>l_mileage_band
1965 ,p_high_value =>h_mileage_band);
1966 -- Get the Lower Mileage Band Rate
1967 l_band_rate:= pqp_car_mileage_functions.pqp_get_table_value
1968 (p_bus_group_id => l_get_info.business_group_id
1969 ,p_payroll_action_id => l_get_info.payroll_action_id
1970 ,p_table_name => l_rates_table
1971 ,p_col_name => l_hi_eng_cap
1972 ,p_row_value => l_mileage_band
1973 ,p_effective_date => l_get_info.claim_end_date
1974 ,p_error_msg => l_err_msg);
1975
1976 -- Get the Higher Mileage Band Rate
1977 h_band_rate:= pqp_car_mileage_functions.pqp_get_table_value
1978 (p_bus_group_id => l_get_info.business_group_id
1979 ,p_payroll_action_id => l_get_info.payroll_action_id
1980 ,p_table_name => l_rates_table
1981 ,p_col_name => l_hi_eng_cap
1982 ,p_row_value => h_mileage_band
1983 ,p_effective_date => l_get_info.claim_end_date
1984 ,p_error_msg => l_err_msg);
1985
1986 IF l_mileage_band <>0 and total_paye_taxable_cl_miles < l_mileage_band THEN
1987 l_proc_amt := ((l_get_info.Claimed_Mileage + total_paye_taxable_cl_miles)-l_mileage_band)*h_band_rate
1988 + (l_mileage_band - total_paye_taxable_cl_miles)*l_band_rate;
1989 ELSE
1990 l_proc_amt := l_get_info.Claimed_Mileage *h_band_rate;
1991 END IF;
1992 END IF;
1993 -- If the Run Result Entry is caused by Reverse Run then we need to find the
1994 -- the original entry and find the processed values
1995 IF l_get_info.source_type = 'R' or l_get_info.source_type = 'V' THEN
1996 FOR i in 1..l_bal_info.count
1997 LOOP
1998 IF l_bal_info(i).run_result_id = l_get_info.source_id THEN
1999 l_proc_amt := -l_bal_info(i).Processed_Amt;
2000 IF l_bal_info(i).Addl_Pasg_Amt IS NOT NULL THEN
2001 l_addl_pass_amt := -l_bal_info(i).Addl_Pasg_Amt;
2002 END IF;
2003 EXIT;
2004 END IF;
2005 END LOOP;
2006 END IF;
2007 hr_utility.set_location('***** PROCESSED AMT: ',l_proc_amt);
2008
2009
2010 IF l_get_info.additional_passenger <> 0 THEN
2011 IF l_get_info.source_type <> 'R' and l_get_info.source_type <> 'V'THEN
2012 l_ret_val:=pqp_car_mileage_functions.pqp_get_addlpasg_rate
2013 (p_business_group_id =>l_get_info.business_group_id
2014 ,p_payroll_action_id =>l_get_info.payroll_action_id
2015 ,p_vehicle_type =>l_get_info.usage_type
2016 ,p_claimed_mileage =>l_proc_miles
2017 ,p_itd_miles =>0
2018 ,p_total_passengers =>l_get_info.additional_passenger
2019 ,p_total_pasg_itd_val =>0
2020 ,p_cc =>l_eng_capacity
2021 ,p_rates_table =>l_rates_table
2022 ,p_claim_end_date =>l_get_info.claim_end_date
2023 ,p_tax_free_amt =>l_addl_pass_amt
2024 ,p_ni_amt =>l_addl_ni_amt
2025 ,p_tax_amt =>l_addl_tax_amt
2026 ,p_err_msg =>l_err_msg
2027 );
2028 END IF;
2029 --l_proc_amt := l_proc_amt + l_addl_pass_amt;
2030 l_bal_info(l_count+1).Addl_Pasg_Amt :=l_addl_pass_amt;
2031 l_bal_info(l_count+1).Addl_Ni_Amt := 0;
2032 l_bal_info(l_count+1).Addl_Tax_Amt := 0;
2033 l_bal_info(l_count+1).Addl_Pasg_Miles := l_get_info.Claimed_Mileage;
2034 l_bal_info(l_count+1).Addl_Pasg_Act_Miles :=0;
2035 END IF;
2036 hr_utility.set_location('***** ADDL PROCESSED AMT: ',l_addl_pass_amt);
2037
2038 l_bal_info(l_count+1).NI_Amt := 0;
2039 l_bal_info(l_count+1).Taxable_Amt := 0;
2040 l_bal_info(l_count+1).PAYE_Taxable :='Y';
2041 l_bal_info(l_count+1).Ownership_Type :=l_get_info.ownership;
2042 l_bal_info(l_count+1).Vehicle_Type :=l_get_info.vehicle_type;
2043 l_bal_info(l_count+1).Usage_Type :=l_get_info.usage_type;
2044 l_bal_info(l_count+1).Element_Name :=l_get_info.element_name;
2045 l_bal_info(l_count+1).Processed_Miles :=l_get_info.Claimed_Mileage;
2046 l_bal_info(l_count+1).Processed_Act_Miles :=0;
2047 l_bal_info(l_count+1).Processed_Amt :=l_proc_amt;
2048 l_bal_info(l_count+1).IRAM_Amt :=0;
2049 l_bal_info(l_count+1).assignment_id :=l_get_info.assignment_id;
2050 l_bal_info(l_count+1).business_group_id :=l_get_info.business_group_id;
2051 l_bal_info(l_count+1).effective_date :=l_get_info.effective_start_date;
2052 l_bal_info(l_count+1).run_result_id :=l_get_info.run_result_id;
2053
2054 ELSIF l_get_info.paye_Taxable = 'N' and l_get_info.ownership='P' THEN
2055 -- This means the claim is not PAYE Taxable but is claimed for the previous year
2056 -- but processed for the current year. This is for NI amt balances.
2057 l_ni_amt := get_balance_value
2058 (p_assignment_action_id => l_get_info.assignment_action_id
2059 ,p_element_entry_id => l_get_info.element_entry_id
2060 ,p_business_group_id => l_get_info.business_group_id
2061 ,p_payroll_action_id => l_get_info.payroll_action_id
2062 ,p_balance_name => 'Mileage Odd Taxable Amt'
2063 );
2064 IF l_get_info.vehicle_type = 'P' THEN
2065 prev_yr_car_ni_amt := prev_yr_car_ni_amt + l_ni_amt;
2066 ELSIF l_get_info.vehicle_type = 'PM' THEN
2067 prev_yr_mc_ni_amt := prev_yr_mc_ni_amt + l_ni_amt;
2068 ELSIF l_get_info.vehicle_type = 'PP' THEN
2069 prev_yr_pc_ni_amt := prev_yr_pc_ni_amt + l_ni_amt;
2070 END IF;
2071 END IF;
2072 l_old_element_entry_id:=l_get_info.element_entry_id;
2073 --END IF;
2074 END LOOP;
2075 CLOSE c_get_info;
2076 l_count:=l_count+1;
2077 l_effective_date := TRUNC(pqp_car_mileage_functions.
2078 pqp_get_date_paid(l_get_info.payroll_action_id));
2079
2080 categorize_balances (p_bal_info => l_bal_info);
2081 --Insert into the pay_patch_status
2082 IF l_status_count=0 THEN
2083 INSERT INTO pay_patch_status
2084 (ID
2085 ,PATCH_NUMBER
2086 ,PATCH_NAME
2087 ,STATUS
2088 ,PHASE
2089 )
2090 VALUES (pay_patch_status_s.NEXTVAL
2091 ,p_business_group_id
2092 ,'CARMILEAGE_BALANCE_ADJ'
2093 ,upgrade_status
2094 ,'CARMILEAGE_BALANCE_ADJ'
2095 );
2096 ELSE
2097 update pay_patch_status
2098 set STATUS = upgrade_status
2099 where patch_name = 'CARMILEAGE_BALANCE_ADJ'
2100 and phase = 'CARMILEAGE_BALANCE_ADJ'
2101 and PATCH_NUMBER = p_business_group_id;
2102 END IF;
2103 END IF;
2104 END initialize_balances;
2105 --------------------------------------------------- End ---------------------------------------------------
2106 END;
2107