[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_PAYROLL_ACTIONS_PKG
Source
1 PACKAGE BODY PAY_GB_PAYROLL_ACTIONS_PKG as
2 /* $Header: pypra04t.pkb 120.6 2006/03/08 22:07:01 npershad noship $ */
3
4 /* Constants */
5
6 G_USER_TABLE_NAME varchar2(30) := 'SOE Balances';
7
8 -- DataBase Items
9 -- these are the database items used for the values displayed
10 --
11 G_TAX_PERIOD_ITEM varchar2(40) := 'PAY_STATUTORY_PERIOD_NUMBER';
12 G_TAX_REFNO_ITEM varchar2(40) := 'SCL_PAY_GB_TAX_REFERENCE';
13 G_TAX_CODE_ITEM varchar2(40) := 'PAYE_DETAILS_TAX_CODE_ENTRY_VALUE';
14 G_TAX_BASIS_ITEM varchar2(40) := 'PAYE_DETAILS_TAX_BASIS_ENTRY_VALUE';
15 G_NI_CATEGORY_ITEM varchar2(40) := 'NI_CATEGORY_ENTRY_VALUE';
16 --G_TAX_PHONE_NUM varchar2(40) := 'SCL_PAY_GB_TAX_OFFICE_PHONE_NUMBER';
17
18 -- variables used for cache values
19 G_PAYROLL_ACTION_ID pay_payroll_actions.payroll_action_id%type;
20 G_TAX_PHONE hr_organization_information.org_information8%type;
21 --
22 -- Balance Items
23 --
24 -- the following are the database items used to retrieve the balances
25 -- for this form and report
26 --
27 G_GROSS_PAY_BALANCE varchar2(30) := 'GROSS_PAY_ASG_YTD';
28 G_TAXABLE_PAY_BALANCE varchar2(30) := 'TAXABLE_PAY_ASG_TD_YTD';
29 G_PAYE_BALANCE varchar2(30) := 'PAYE_ASG_TD_YTD';
30 G_PAYE_TRANSFER varchar2(30) := 'PAYE_ASG_TRANSFER_PTD';
31 G_NIABLE_PAY_BALANCE varchar2(30) := 'NIABLE_PAY_ASG_TD_YTD';
32 G_NI_A_EMPLOYEE_BALANCE varchar2(30) := 'NI_A_EMPLOYEE_ASG_TD_YTD';
33 G_NI_B_EMPLOYEE_BALANCE varchar2(30) := 'NI_B_EMPLOYEE_ASG_TD_YTD';
34 G_NI_D_EMPLOYEE_BALANCE varchar2(30) := 'NI_D_EMPLOYEE_ASG_TD_YTD';
35 G_NI_G_EMPLOYEE_BALANCE varchar2(30) := 'NI_G_EMPLOYEE_ASG_TD_YTD';
36 G_NI_F_EMPLOYEE_BALANCE varchar2(30) := 'NI_F_EMPLOYEE_ASG_TD_YTD';
37 G_NI_L_EMPLOYEE_BALANCE varchar2(30) := 'NI_L_EMPLOYEE_ASG_TD_YTD';
38 G_NI_J_EMPLOYEE_BALANCE varchar2(30) := 'NI_J_EMPLOYEE_ASG_TD_YTD';
39 G_NI_E_EMPLOYEE_BALANCE varchar2(30) := 'NI_E_EMPLOYEE_ASG_TD_YTD';
40 G_NI_S_EMPLOYEE_BALANCE varchar2(30) := 'NI_S_EMPLOYEE_ASG_TD_YTD';
41 G_NI_F_EMPLOYEE_TRANSFER varchar2(30) := 'NI_F_EMPLOYEE_ASG_TRANSFER_PTD';
42 G_NI_G_EMPLOYEE_TRANSFER varchar2(30) := 'NI_G_EMPLOYEE_ASG_TRANSFER_PTD';
43 G_NI_A_EMPLOYEE_TRANSFER varchar2(30) := 'NI_A_EMPLOYEE_ASG_TRANSFER_PTD';
44 G_NI_B_EMPLOYEE_TRANSFER varchar2(30) := 'NI_B_EMPLOYEE_ASG_TRANSFER_PTD';
45 G_NI_D_EMPLOYEE_TRANSFER varchar2(30) := 'NI_D_EMPLOYEE_ASG_TRANSFER_PTD';
46 G_NI_E_EMPLOYEE_TRANSFER varchar2(30) := 'NI_E_EMPLOYEE_ASG_TRANSFER_PTD';
47 G_NI_J_EMPLOYEE_TRANSFER varchar2(30) := 'NI_J_EMPLOYEE_ASG_TRANSFER_PTD';
48 G_NI_L_EMPLOYEE_TRANSFER varchar2(30) := 'NI_L_EMPLOYEE_ASG_TRANSFER_PTD';
49 G_NI_S_EMPLOYEE_TRANSFER varchar2(30) := 'NI_S_EMPLOYEE_ASG_TRANSFER_PTD';
50 G_NI_F_ABLE_BALANCE varchar2(30) := 'NI_F_ABLE_ASG_TD_YTD';
51 G_NI_G_ABLE_BALANCE varchar2(30) := 'NI_G_ABLE_ASG_TD_YTD';
52 G_NI_A_ABLE_BALANCE varchar2(30) := 'NI_A_ABLE_ASG_TD_YTD';
53 G_NI_B_ABLE_BALANCE varchar2(30) := 'NI_B_ABLE_ASG_TD_YTD';
54 G_NI_D_ABLE_BALANCE varchar2(30) := 'NI_D_ABLE_ASG_TD_YTD';
55 G_NI_E_ABLE_BALANCE varchar2(30) := 'NI_E_ABLE_ASG_TD_YTD';
56 G_NI_J_ABLE_BALANCE varchar2(30) := 'NI_J_ABLE_ASG_TD_YTD';
57 G_NI_L_ABLE_BALANCE varchar2(30) := 'NI_L_ABLE_ASG_TD_YTD';
58 G_NI_S_ABLE_BALANCE varchar2(30) := 'NI_S_ABLE_ASG_TD_YTD';
59 G_NI_EMPLOYER_BALANCE varchar2(30) := 'NI_EMPLOYER_ASG_TD_YTD';
60 G_NI_EMPLOYER_TRANSFER varchar2(30) := 'NI_EMPLOYER_ASG_TRANSFER_PTD';
61 G_NIABLE_PAY_TRANSFER varchar2(30) := 'NIABLE_PAY_ASG_TRANSFER_PTD';
62 G_NI_F_ABLE_TRANSFER varchar2(30) := 'NI_F_ABLE_ASG_TRANSFER_PTD';
63 G_NI_G_ABLE_TRANSFER varchar2(30) := 'NI_G_ABLE_ASG_TRANSFER_PTD';
64 G_NI_A_ABLE_TRANSFER varchar2(30) := 'NI_A_ABLE_ASG_TRANSFER_PTD';
65 G_NI_B_ABLE_TRANSFER varchar2(30) := 'NI_B_ABLE_ASG_TRANSFER_PTD';
66 G_NI_D_ABLE_TRANSFER varchar2(30) := 'NI_D_ABLE_ASG_TRANSFER_PTD';
67 G_NI_E_ABLE_TRANSFER varchar2(30) := 'NI_E_ABLE_ASG_TRANSFER_PTD';
68 G_NI_J_ABLE_TRANSFER varchar2(30) := 'NI_J_ABLE_ASG_TRANSFER_PTD';
69 G_NI_L_ABLE_TRANSFER varchar2(30) := 'NI_L_ABLE_ASG_TRANSFER_PTD';
70 G_NI_S_ABLE_TRANSFER varchar2(30) := 'NI_S_ABLE_ASG_TRANSFER_PTD';
71
72 G_GROSS_PAY_PTD_BALANCE varchar2(30) := 'GROSS_PAY_ASG_PROC_PTD';
73 G_TAXABLE_PAY_TRANSFER varchar2(30) := 'TAXABLE_PAY_ASG_TRANSFER_PTD';
74
75 G_SUPERAN_BALANCE varchar2(31) := 'SUPERANNUATION_TOTAL_ASG_TD_YTD';
76
77 G_NI_A_TOTAL varchar2(30) := 'NI_A_TOTAL_ASG_TD_PTD';
78 G_NI_B_TOTAL varchar2(30) := 'NI_B_TOTAL_ASG_TD_PTD';
79 G_NI_D_TOTAL varchar2(30) := 'NI_D_TOTAL_ASG_TD_PTD';
80 G_NI_E_TOTAL varchar2(30) := 'NI_E_TOTAL_ASG_TD_PTD';
81 G_NI_F_TOTAL varchar2(30) := 'NI_F_TOTAL_ASG_TD_PTD';
82 G_NI_G_TOTAL varchar2(30) := 'NI_G_TOTAL_ASG_TD_PTD';
83 G_NI_J_TOTAL varchar2(30) := 'NI_J_TOTAL_ASG_TD_PTD';
84 G_NI_L_TOTAL varchar2(30) := 'NI_L_TOTAL_ASG_TD_PTD';
85 G_NI_S_TOTAL varchar2(30) := 'NI_S_TOTAL_ASG_TD_PTD';
86
87 G_NI_C_EMPLOYER varchar2(30) := 'NI_C_EMPLOYER_ASG_TD_YTD';
88 G_NI_S_EMPLOYER varchar2(30) := 'NI_S_EMPLOYER_ASG_TD_YTD';
89
90 --
91 -- Balance Types
92 --
93 -- the following are the types associated with the above balances
94 --
95 g_gross_pay_type varchar2(30) := 'Gross Pay';
96 g_taxable_pay_type varchar2(30) := 'Taxable Pay';
97 g_paye_type varchar2(30) := 'PAYE';
98 g_niable_pay_type varchar2(30) := 'NIable Pay';
99 g_ni_a_employee_type varchar2(30) := 'NI A Employee';
100 g_ni_b_employee_type varchar2(30) := 'NI B Employee';
101 g_ni_d_employee_type varchar2(30) := 'NI D Employee';
102 g_ni_e_employee_type varchar2(30) := 'NI E Employee';
103 g_ni_f_employee_type varchar2(30) := 'NI F Employee';
104 g_ni_g_employee_type varchar2(30) := 'NI G Employee';
105 g_ni_j_employee_type varchar2(30) := 'NI J Employee';
106 g_ni_l_employee_type varchar2(30) := 'NI L Employee';
107 g_ni_s_employee_type varchar2(30) := 'NI S Employee';
108
109 g_ni_a_able_type varchar2(30) := 'NI A Able';
110 g_ni_b_able_type varchar2(30) := 'NI B Able';
111 g_ni_d_able_type varchar2(30) := 'NI D Able';
112 g_ni_e_able_type varchar2(30) := 'NI E Able';
113 g_ni_f_able_type varchar2(30) := 'NI F Able';
114 g_ni_g_able_type varchar2(30) := 'NI G Able';
115 g_ni_j_able_type varchar2(30) := 'NI J Able';
116 g_ni_l_able_type varchar2(30) := 'NI L Able';
117 g_ni_s_able_type varchar2(30) := 'NI S Able';
118
119 g_ni_a_total_type varchar2(30) := 'NI A Total';
120 g_ni_b_total_type varchar2(30) := 'NI B Total';
121 g_ni_d_total_type varchar2(30) := 'NI D Total';
122 g_ni_e_total_type varchar2(30) := 'NI E Total';
123 g_ni_f_total_type varchar2(30) := 'NI F Total';
124 g_ni_g_total_type varchar2(30) := 'NI G Total';
125 g_ni_j_total_type varchar2(30) := 'NI J Total';
126 g_ni_l_total_type varchar2(30) := 'NI L Total';
127 g_ni_s_total_type varchar2(30) := 'NI S Total';
128
129 g_ni_employer_type varchar2(30) := 'NI Employer';
130
131 g_superan_total_type varchar2(30) := 'Superannuation Total';
132
133 g_ni_c_employer_type varchar2(30) := 'NI C Employer';
134 g_ni_s_employer_type varchar2(30) := 'NI S Employer';
135
136 --
137 -- Dimension suffixes
138 --
139 -- the following are the different balance dimension suffixes used by
140 -- the balance items
141 --
142 g_year_to_date varchar2(30) := '_ASG_YTD';
143 g_tax_district_ytd varchar2(30) := '_ASG_TD_YTD';
144 g_period_to_date varchar2(30) := '_ASG_TRANSFER_PTD';
145
146 g_proc_period_to_date varchar2(30) := '_ASG_PROC_PTD';
147
148 --
149 -- The NI Total for each category
150 --
151 g_ni_a_total_value number;
152 g_ni_b_total_value number;
153 g_ni_d_total_value number;
154 g_ni_e_total_value number;
155 g_ni_f_total_value number;
156 g_ni_g_total_value number;
157 g_ni_j_total_value number;
158 g_ni_l_total_value number;
159 g_ni_s_total_value number;
160
161 -- Table Declaration
162 --
163 --
164 -- PLSQL Table of Record is allowed only from PLSQL 2.3
165 --
166 TYPE balance_name_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
167 TYPE balance_suffix_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
168 TYPE balance_id_table IS TABLE OF NUMBER(9) INDEX BY BINARY_INTEGER;
169 TYPE balance_value_table IS TABLE OF NUMBER(12,2) INDEX BY BINARY_INTEGER;
170
171 g_balance_name balance_name_table;
172 g_balance_suffix balance_suffix_table;
173 g_balance_id balance_id_table;
174 g_displayed_balance balance_name_table;
175 g_displayed_value balance_value_table;
176
177 g_empty_balances balance_name_table;
178 g_empty_values balance_value_table;
179
180 g_table_dim NUMBER := 0; -- Table Dimension
181
182 g_tax_code_id NUMBER;
183 g_tax_basis_id NUMBER;
184 g_category_id NUMBER;
185 g_paye_tax_code_id NUMBER;
186 g_paye_tax_basis_id NUMBER;
187
188 g_user_narrative_column_id NUMBER;
189 g_user_sequence_column_id NUMBER;
190
191 --
192 --
193 ------------------------------------------------------------------------------
194 --
195 procedure get_input_values_id
196 is
197
198 CURSOR csr_input_value_id (l_element_name VARCHAR2,
199 l_piv_name VARCHAR2) IS
200 SELECT input_value_id
201 FROM pay_input_values_f piv,
202 pay_element_types_f pet
203 WHERE piv.element_type_id = pet.element_type_id
204 AND pet.element_name = l_element_name
205 AND pet.legislation_code = 'GB'
206 AND piv.name = l_piv_name;
207
208 begin
209
210 OPEN csr_input_value_id('PAYE Details','Tax Code');
211 FETCH csr_input_value_id INTO g_tax_code_id;
212 CLOSE csr_input_value_id;
213
214 OPEN csr_input_value_id('PAYE Details','Tax Basis');
215 FETCH csr_input_value_id INTO g_tax_basis_id;
216 CLOSE csr_input_value_id;
217
218 OPEN csr_input_value_id('NI','Category');
219 FETCH csr_input_value_id INTO g_category_id;
220 CLOSE csr_input_value_id;
221
222 OPEN csr_input_value_id('PAYE','Tax Code');
223 FETCH csr_input_value_id INTO g_paye_tax_code_id;
224 CLOSE csr_input_value_id;
225
226 OPEN csr_input_value_id('PAYE','Tax Basis');
227 FETCH csr_input_value_id INTO g_paye_tax_basis_id;
228 CLOSE csr_input_value_id;
229
230 end get_input_values_id;
231
232
233 procedure total_payment (p_assignment_action_id in number,
234 p_total_payment out nocopy number) is
235 -- Bug 2553453, removed call to fnd_number.canonical_to_number from query
236 -- as conversion already handled in view, and extra call causes problems in
237 -- calculation when working in non-GB environment
238 cursor csr_payment is
239 select sum(result_value)
240 from pay_gb_pay_values_v
241 where base_classification_name in ('Earnings','Direct Net', 'Direct Payment')
242 and p_assignment_action_id = assignment_action_id;
243 --
244 begin
245 --
246 open csr_payment;
247 fetch csr_payment into p_total_payment;
248 close csr_payment;
249 --
250 end total_payment;
251 --
252 --
253 ------------------------------------------------------------------------------
254 --
255 procedure total_deduct (p_assignment_action_id in number,
256 p_total_deduct out nocopy number) is
257 -- Bug 2553453, removed call to fnd_number.canonical_to_number from query
258 -- as conversion already handled in view, and extra call causes problems in
259 -- calculation when working in non-GB environment
260 cursor csr_deduct is select sum(result_value)
261 from pay_gb_pay_values_v
262 where base_classification_name in
263 ('Pre Statutory', 'Statutory', 'Court Orders',
264 'Pre Tax Deductions','PAYE','NI','Voluntary Deductions',
265 'Pre NI Deductions','Pre Tax and NI Deductions')
266 and p_assignment_action_id = assignment_action_id;
267 --
268 begin
269 --
270 open csr_deduct;
271 fetch csr_deduct into p_total_deduct;
272 close csr_deduct;
273 --
274 end total_deduct;
275 --
276 -------------------------------------------------------------------------
277 --
278 -- sets the context for which a database item is to be retrieved and returns
279 -- whether context has been set correctly
280 --
281 function set_database_context (p_database_item in varchar2,
282 p_payroll_action_id in number default null,
283 p_date_earned in varchar2 default null,
284 p_assignment_id in number default null)
285 return boolean is
286 --
287 begin
288 --
289 if p_database_item = G_TAX_PERIOD_ITEM then
290 --
291 if p_payroll_action_id is not null then
292 --
293 pay_balance_pkg.set_context ('payroll_action_id',
294 to_char(p_payroll_action_id));
295 --
296 return true;
297 --
298 else
299 --
300 return false;
301 --
302 end if;
303 --
304 elsif p_database_item = G_TAX_REFNO_ITEM then
305 --
306 if p_date_earned is not null and
307 p_assignment_id is not null then
308 --
309 pay_balance_pkg.set_context ('date_earned',
310 p_date_earned);
311 --
312 pay_balance_pkg.set_context ('assignment_id',
313 to_char(p_assignment_id));
314 --
315 return true;
316 --
317 else
318 --
319 return false;
320 --
321 end if;
322 --
323 end if;
324 --
325 end;
326 --
327 -------------------------------------------------------------------------
328 --
329 -- returns the value associated with a given database item assuming that the
330 -- correct context has already been set
331 --
332 function database_item (p_database_item in varchar2) return varchar2 is
333 --
334 -- constants for calls to database items
335 --
336 l_business_group_id number := null;
337 l_legislation_code varchar2(30) := 'GB';
338 --
339 begin
340 --
341 return pay_balance_pkg.run_db_item
342 (p_database_name => p_database_item,
343 p_bus_group_id => l_business_group_id,
344 p_legislation_code => l_legislation_code);
345 --
346 end;
347 --
348 -------------------------------------------------------------------------
349 -- replaces the tax code, tax basis and category retrievals from database
350 -- items
351 --
352 FUNCTION get_tax_details(p_run_assignment_action_id number,
353 p_input_value_id number,
354 p_paye_input_value_id number,
355 p_date_earned varchar2)
356 RETURN varchar2
357 IS
358 --
359 -- Retrieve the details via the element entry values table
360 --
361 cursor element_type_value(p_assig_act_id NUMBER) is
362 SELECT peev.screen_entry_value
363 FROM pay_element_entry_values_f peev,
364 pay_element_entries_f pee,
365 pay_assignment_actions paa
366 WHERE pee.element_entry_id = peev.element_entry_id
367 AND pee.assignment_id = paa.assignment_id
368 AND paa.assignment_action_id = p_assig_act_id
369 AND peev.input_value_id +0 = p_input_value_id
370 AND to_date(p_date_earned, 'YYYY/MM/DD')
371 BETWEEN
372 pee.effective_start_date
373 AND pee.effective_end_date
374 AND to_date(p_date_earned, 'YYYY/MM/DD')
375 BETWEEN
376 peev.effective_start_date
377 AND peev.effective_end_date;
378 --
379 -- Retrieve the details via the run result
380 --
381 cursor result_type_value(p_piv_id NUMBER, p_assig_act_id NUMBER) is
382 SELECT result_value
383 FROM pay_run_result_values prr,
384 pay_run_results pr,
385 pay_element_types_f pet,
386 pay_input_values_f piv
387 WHERE pr.assignment_action_id = p_assig_act_id
388 and pr.element_type_id = pet.element_type_id
389 and pr.run_result_id = prr.run_result_id
390 and prr.input_value_id = piv.input_value_id
391 and pet.element_type_id = piv.element_type_id
392 and piv.input_value_id = p_piv_id
393 and piv.business_group_id IS NULL
394 and piv.legislation_code = 'GB'
395 and to_date(p_date_earned, 'YYYY/MM/DD')
396 between piv.effective_start_date
397 and piv.effective_end_date
398 and to_date(p_date_earned, 'YYYY/MM/DD')
399 between pet.effective_start_date
400 and pet.effective_end_date
401 and pr.run_result_id = (select nvl(max(pr1.run_result_id),pr.run_result_id)
402 from pay_run_results pr1
403 where pr1.assignment_action_id = p_assig_act_id
404 and pr1.element_type_id = pr.element_type_id
405 and pr1.status = 'P');
406
407 --
408 -- Get the child action
409 --
410 cursor get_child_action(p_assig_act_id number) is
411 select assignment_action_id
412 from pay_assignment_actions
413 where source_action_id = p_assig_act_id
414 order by action_sequence desc;
415
416 --
417 l_legislation_code varchar2(30) := 'GB';
418 pay_result_value varchar2 (60);
419 error_string varchar2 (60);
420 l_child_act_id number;
421 --
422 BEGIN
423 --
424 error_string := to_char(p_input_value_id);
425
426 -- Check for child action
427 open get_child_action(p_run_assignment_action_id);
428 fetch get_child_action into l_child_act_id;
429 close get_child_action;
430
431 if (l_child_act_id is null) then
432 l_child_act_id := p_run_assignment_action_id;
433 end if;
434
435 --
436 -- Retrieve the value from the PAYE run result
437 --
438
439 open result_type_value(p_paye_input_value_id, l_child_act_id);
440 fetch result_type_value into pay_result_value;
441 close result_type_value;
442
443 --
444 -- If the PAYE run result is null, retrieve the value from
445 -- the PAYE Details run result
446 --
447
448 if pay_result_value is null then
449
450 open result_type_value(p_input_value_id, l_child_act_id);
451 fetch result_type_value into pay_result_value;
452 close result_type_value;
453
454 end if;
455
456 --
457 -- The run result values are null, so use the element entry value
458 --
459
460 if pay_result_value is null then
461
462 open element_type_value(l_child_act_id);
463 fetch element_type_value into pay_result_value;
464 close element_type_value;
465
466 end if;
467 --
468 return pay_result_value;
469 --
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 pay_result_value := NULL;
473 hr_utility.trace('TEST pay_result_value : NULL ');
474 return pay_result_value;
475 --
476 END get_tax_details;
477 --
478 -------------------------------------------------------------------------
479 --
480 -- retrieves the values to be displayed by calling database items
481 --
482 procedure get_database_items (p_assignment_id in number,
483 p_run_assignment_action_id in number,
484 p_date_earned in varchar2,
485 p_payroll_action_id in number,
486 p_tax_period in out nocopy varchar2,
487 p_tax_refno in out nocopy varchar2,
488 p_tax_code in out nocopy varchar2,
489 p_tax_basis in out nocopy varchar2,
490 p_ni_category in out nocopy varchar2) is
491 --
492 l_tax_basis varchar2(30);
493 --
494 begin
495 --
496 get_input_values_id;
497
498 -- set context for Tax Period database item and retrieve it
499 --
500 if set_database_context (p_database_item => G_TAX_PERIOD_ITEM,
501 p_payroll_action_id => p_payroll_action_id) then
502 --
503 p_tax_period := database_item (G_TAX_PERIOD_ITEM);
504 --
505 --
506 --
507 -- set context for the Tax Refno database item, which is also
508 -- used for the remaining items, and retrieve the remaining items
509 --
510 if set_database_context (p_database_item => G_TAX_REFNO_ITEM,
511 p_date_earned => p_date_earned,
512 p_assignment_id => p_assignment_id) then
513 --
514 p_tax_refno := database_item (G_TAX_REFNO_ITEM);
515 --
516 p_tax_code := get_tax_details(p_run_assignment_action_id,
517 g_tax_code_id,
518 g_paye_tax_code_id,
519 p_date_earned);
520 -- database_item (G_TAX_CODE_ITEM);
521 --
522 l_tax_basis := get_tax_details(p_run_assignment_action_id,
523 g_tax_basis_id,
524 g_paye_tax_basis_id,
525 p_date_earned);
526 -- database_item (G_TAX_BASIS_ITEM);
527 --
528 -- Tax Basis is translated into its meaning
529 --
530 --p_tax_basis := hr_general.decode_lookup ('GB_TAX_BASIS', l_tax_basis);
531 if l_tax_basis = 'C' then p_tax_basis := 'Cumulative';
532 else p_tax_basis := 'Non Cumul.';
533 end if;
534 --
535 p_ni_category := get_tax_details(p_run_assignment_action_id,
536 g_category_id,
537 g_category_id,
538 p_date_earned);
539 -- database_item (G_NI_CATEGORY_ITEM);
540 --
541 end if;
542 end if;
543 --
544 end;
545 --
546 procedure get_report_db_items (p_assignment_id in number,
547 p_run_assignment_action_id in number,
548 p_date_earned in varchar2,
549 p_payroll_action_id in number,
550 p_tax_period in out nocopy varchar2,
551 p_tax_refno in out nocopy varchar2,
552 p_tax_phone in out nocopy varchar2,
553 p_tax_code in out nocopy varchar2,
554 p_tax_basis in out nocopy varchar2,
555 p_ni_category in out nocopy varchar2) is
556 --
557 l_tax_basis varchar2(30);
558 --
559 begin
560 --
561 get_input_values_id;
562
563 -- set context for Tax Period database item and retrieve it
564 --
565 if set_database_context (p_database_item => G_TAX_PERIOD_ITEM,
566 p_payroll_action_id => p_payroll_action_id) then
567 --
568 p_tax_period := database_item (G_TAX_PERIOD_ITEM);
569 --
570 --
571 -- set context for the Tax Refno database item, which is also
572 -- used for the remaining items, and retrieve the remaining items
573 --
574 if set_database_context (p_database_item => G_TAX_REFNO_ITEM,
575 p_date_earned => p_date_earned,
576 p_assignment_id => p_assignment_id) then
577 --
578 p_tax_refno := database_item (G_TAX_REFNO_ITEM);
579 --
580 -- p_tax_phone := database_item (G_TAX_PHONE_NUM);
581 -- no database item as yet available for tax office telephone
582 -- cache the value that doesn't change unless payroll action changes
583 if nvl(g_payroll_action_id,-1) <> p_payroll_action_id then
584 select max(org_information8) into g_tax_phone
585 from pay_payrolls_f p,
586 pay_payroll_actions pact,
587 hr_soft_coding_keyflex flex,
588 hr_organization_information org
589 where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
590 and org.ORG_INFORMATION_CONTEXT = 'Tax Details References'
591 and org.org_information1 = flex.segment1
592 and p.business_group_id = org.organization_id
593 and pact.payroll_action_id = p_payroll_action_id
594 and pact.payroll_id = p.payroll_id
595 and pact.effective_date between
596 p.effective_start_date and p.effective_end_date;
597
598 g_payroll_action_id := p_payroll_action_id;
599 end if;
600 p_tax_phone := g_tax_phone;
601 --
602 --
603 p_tax_code := get_tax_details(p_run_assignment_action_id,
604 g_tax_code_id,
605 g_paye_tax_code_id,
606 p_date_earned);
607 -- database_item (G_TAX_CODE_ITEM);
608 --
609 l_tax_basis := get_tax_details(p_run_assignment_action_id,
610 g_tax_basis_id,
611 g_paye_tax_basis_id,
612 p_date_earned);
613 -- database_item (G_TAX_BASIS_ITEM);
614 --
615 -- Tax Basis is translated into its meaning
616 --
617 --p_tax_basis := hr_general.decode_lookup ('GB_TAX_BASIS', l_tax_basis,p_date_earned);
618 if l_tax_basis = 'C' then p_tax_basis := 'Cumulative';
619 else p_tax_basis := 'Non Cumul.';
620 end if;
621 --
622 p_ni_category := get_tax_details(p_run_assignment_action_id,
623 g_category_id,
624 g_category_id,
625 p_date_earned);
626 -- database_item (G_NI_CATEGORY_ITEM);
627 --
628 --
629 end if;
630 end if;
631 --
632 end;
633 --
634 ------------------------------------------------------------------------------
635 --
636 -- returns the defined balance ID associated with a given balance database
637 -- item - the balance is defined in terms of its type and the balance
638 -- dimension
639 --
640 -- Bug 358634 included legislation_code to reduce number of values returned
641 --
642 function defined_balance_id (p_balance_type in varchar2,
643 p_dimension_suffix in varchar2) return number is
644 --
645 l_legislation_code varchar2(30) := 'GB';
646 --
647
648 l_table_index NUMBER;
649 l_found BOOLEAN := FALSE;
650
651 l_balance_name VARCHAR2(80);
652 l_balance_suffix VARCHAR2(30);
653
654 CURSOR c_defined_balance IS
655 SELECT
656 defined_balance_id
657 FROM
658 pay_defined_balances PDB,
659 pay_balance_dimensions PBD,
660 pay_balance_types_tl PBT_TL,
661 pay_balance_types PBT
662 WHERE PBT_TL.balance_type_id = PBT.balance_type_id
663 and userenv('LANG') = PBT_TL.language
664 AND PBT_TL.balance_name = p_balance_type
665 AND nvl(PBT.legislation_code,l_legislation_code) = l_legislation_code
666 AND PDB.balance_type_id = PBT.balance_type_id
667 AND PBD.balance_dimension_id = PDB.balance_dimension_id
668 AND nvl(PDB.legislation_code,l_legislation_code) = l_legislation_code
669 AND PBD.database_item_suffix = p_dimension_suffix;
670
671
672 --
673 l_result number;
674 --
675 begin
676 --
677
678 -- g_table_dim this variable holds the table dimension
679
680 l_table_index := 1;
681 --
682 hr_utility.trace(' Index :' || TO_CHAR(l_table_index));
683 hr_utility.trace(' Dim :' || TO_CHAR(g_table_dim));
684 --
685 LOOP
686 IF l_table_index > g_table_dim THEN
687 EXIT;
688 END IF;
689 l_balance_name := g_balance_name(l_table_index);
690 l_balance_suffix := g_balance_suffix(l_table_index);
691
692 IF l_balance_name = p_balance_type AND l_balance_suffix = p_dimension_suffix THEN
693 l_result := g_balance_id(l_table_index);
694 l_found := TRUE;
695 hr_utility.trace(' FOUND !!!!! ');
696 EXIT;
697 END IF;
698 l_table_index := l_table_index + 1;
699 END LOOP;
700 --
701 hr_utility.trace(' Index :' || TO_CHAR(l_table_index));
702 --
703 IF l_found = FALSE THEN -- calculate and insert the new value in the table.
704 --
705 hr_utility.trace(' NOT FOUND, inserted IN position : ' || TO_CHAR(l_table_index));
706 --
707 open c_defined_balance;
708 fetch c_defined_balance into l_result;
709 close c_defined_balance;
710
711 g_balance_name(l_table_index) := p_balance_type;
712 g_balance_suffix(l_table_index) := p_dimension_suffix;
713 g_balance_id(l_table_index) := l_result;
714 g_table_dim := g_table_dim + 1;
715
716 END IF;
717 --
718 --
719 return l_result;
720 end;
721 --
722 ------------------------------------------------------------------------------
723 --
724 -- returns the value associated with a given balance database item
725 -- this is derived by translating the balance name into its balance type
726 -- and dimension
727 -- using the type and dimesnion to derive the defined balance ID
728 -- using the defined balance ID to obtain the current value for the balance
729 -- for the given assignment action ID
730 --
731 function balance_item_value (p_balance_name in varchar2,
732 p_assignment_action_id in number) return number is
733 --
734 l_balance_name varchar2(30);
735 l_balance_type varchar2(30);
736 l_dimension_suffix varchar2(30);
737 l_defined_balance_id number;
738 --
739 begin
740 --
741 if p_balance_name = G_GROSS_PAY_BALANCE then
742 --
743 l_balance_type := g_gross_pay_type;
744 l_dimension_suffix := g_year_to_date;
745 --
746 elsif p_balance_name = G_TAXABLE_PAY_BALANCE then
747 --
748 l_balance_type := g_taxable_pay_type;
749 l_dimension_suffix := g_tax_district_ytd;
750 --
751 elsif p_balance_name = G_PAYE_BALANCE then
752 --
753 l_balance_type := g_paye_type;
754 l_dimension_suffix := g_tax_district_ytd;
755 --
756 elsif p_balance_name = G_NIABLE_PAY_BALANCE then
757 --
758 l_balance_type := g_niable_pay_type;
759 l_dimension_suffix := g_tax_district_ytd;
760 --
761 elsif p_balance_name = G_NI_A_EMPLOYEE_BALANCE then
762 --
763 l_balance_type := g_ni_a_employee_type;
764 l_dimension_suffix := g_tax_district_ytd;
765 --
766 elsif p_balance_name = G_NI_B_EMPLOYEE_BALANCE then
767 --
768 l_balance_type := g_ni_b_employee_type;
769 l_dimension_suffix := g_tax_district_ytd;
770 --
771 elsif p_balance_name = G_NI_D_EMPLOYEE_BALANCE then
772 --
773 l_balance_type := g_ni_d_employee_type;
774 l_dimension_suffix := g_tax_district_ytd;
775 --
776 elsif p_balance_name = G_NI_E_EMPLOYEE_BALANCE then
777 --
778 l_balance_type := g_ni_e_employee_type;
779 l_dimension_suffix := g_tax_district_ytd;
780 --
781 --
782 elsif p_balance_name = G_NI_F_EMPLOYEE_BALANCE then
783 --
784 l_balance_type := g_ni_f_employee_type;
785 l_dimension_suffix := g_tax_district_ytd;
786 --
787 elsif p_balance_name = G_NI_G_EMPLOYEE_BALANCE then
788 --
789 l_balance_type := g_ni_g_employee_type;
790 l_dimension_suffix := g_tax_district_ytd;
791 --
792 elsif p_balance_name = G_NI_J_EMPLOYEE_BALANCE then
793 --
794 l_balance_type := g_ni_j_employee_type;
795 l_dimension_suffix := g_tax_district_ytd;
796 --
797 elsif p_balance_name = G_NI_L_EMPLOYEE_BALANCE then
798 --
799 l_balance_type := g_ni_l_employee_type;
800 l_dimension_suffix := g_tax_district_ytd;
801 --
802 elsif p_balance_name = G_NI_S_EMPLOYEE_BALANCE then
803 --
804 l_balance_type := g_ni_s_employee_type;
805 l_dimension_suffix := g_tax_district_ytd;
806 --
807 elsif p_balance_name = G_NI_A_TOTAL then
808 --
809 l_balance_type := g_ni_a_total_type;
810 l_dimension_suffix := g_tax_district_ytd;
811 --
812 elsif p_balance_name = G_NI_B_TOTAL then
813 --
814 l_balance_type := g_ni_b_total_type;
815 l_dimension_suffix := g_tax_district_ytd;
816 --
817 elsif p_balance_name = G_NI_D_TOTAL then
818 --
819 l_balance_type := g_ni_d_total_type;
820 l_dimension_suffix := g_tax_district_ytd;
821 --
822 elsif p_balance_name = G_NI_E_TOTAL then
823 --
824 l_balance_type := g_ni_e_total_type;
825 l_dimension_suffix := g_tax_district_ytd;
826 --
827 elsif p_balance_name = G_NI_F_TOTAL then
828 --
829 l_balance_type := g_ni_f_total_type;
830 l_dimension_suffix := g_tax_district_ytd;
831 --
832 elsif p_balance_name = G_NI_G_TOTAL then
833 --
834 l_balance_type := g_ni_g_total_type;
835 l_dimension_suffix := g_tax_district_ytd;
836 --
837 elsif p_balance_name = G_NI_J_TOTAL then
838 --
839 l_balance_type := g_ni_j_total_type;
840 l_dimension_suffix := g_tax_district_ytd;
841 --
842 elsif p_balance_name = G_NI_L_TOTAL then
843 --
844 l_balance_type := g_ni_l_total_type;
845 l_dimension_suffix := g_tax_district_ytd;
846 --
847 elsif p_balance_name = G_NI_S_TOTAL then
848 --
849 l_balance_type := g_ni_s_total_type;
850 l_dimension_suffix := g_tax_district_ytd;
851 --
852 end if;
853 --
854 --
855 -- derive defined balance ID
856 --
857 l_defined_balance_id := defined_balance_id
858 (p_balance_type => l_balance_type,
859 p_dimension_suffix => l_dimension_suffix);
860 --
861 --
862 return pay_balance_pkg.get_value
863 (p_defined_balance_id => l_defined_balance_id,
864 p_assignment_action_id => p_assignment_action_id);
865 --
866 --
867 end;
868 --
869 --
870 -------------------------------------------------------------------------------
871 --
872 PROCEDURE find_user_table_values
873 (p_user_row_name in varchar2,
874 p_business_group_id in number,
875 p_seq_no in out nocopy number) is
876 --
877 CURSOR c_find_row_values IS
878 SELECT puci.value intval
879 FROM pay_user_rows pur,
880 pay_user_columns puc,
881 pay_user_tables put,
882 pay_user_column_instances puci
883 WHERE put.user_table_name = g_user_table_name
884 AND put.business_group_id is NULL
885 AND put.legislation_code = 'GB'
886 AND puc.user_column_name = 'Sequence'
887 AND puc.user_table_id = put.user_table_id
888 AND puc.business_group_id is NULL
889 AND puc.legislation_code = 'GB'
890 AND puci.user_column_id = puc.user_column_id
891 AND puci.business_group_id = p_business_group_id
892 AND pur.user_row_id = puci.user_row_id
893 AND pur.row_low_range_or_name = p_user_row_name;
894
895 begin
896
897 for rec in c_find_row_values loop
898
899 p_seq_no := rec.intval;
900
901 end loop;
902
903 end;
904 --
905 -----------------------------------------------------------------------------------
906 --
907 -- Returns the balances for the report.
908 --
909 function report_balance_items (p_balance_name in varchar2,
910 p_dimension in varchar2,
911 p_assignment_action_id in number) return number is
912 --
913 l_defined_balance_id number;
914 --
915 begin
916 --
917 --
918 -- derive defined balance ID
919 --
920 l_defined_balance_id := defined_balance_id
921 (p_balance_type => p_balance_name,
922 p_dimension_suffix => p_dimension);
923 --
924
925 --
926 if l_defined_balance_id is null
927 then
928 return 0;
929 else
930 return pay_balance_pkg.get_value
931 (p_defined_balance_id => l_defined_balance_id,
932 p_assignment_action_id => p_assignment_action_id);
933 end if;
934 --
935 end;
936 --
937 -------------------------------------------------------------------------------
938 -- This section includes overloaded definitions of the functions:
939 -- report_balance_items and get_report_balances. This is to support any possible
940 -- bespoke reports using calls to these functions. Development has changed these
941 -- functions to include extra in parameters following new SOE functionality delivered
942 -- with 1999 EOY3 under bug 879804. mlisieck 16-Jul-99
943 --
944 -- Returns the balances for the report.
945 --
946 function report_balance_items (p_balance_name in varchar2,
947 p_assignment_action_id in number) return number is
948 --
949 l_balance_type varchar2(30);
950 l_dimension_suffix varchar2(30);
951 l_defined_balance_id number;
952 --
953 begin
954 --
955 if p_balance_name = G_GROSS_PAY_BALANCE then
956 --
957 l_balance_type := g_gross_pay_type;
958 l_dimension_suffix := g_year_to_date;
959 --
960 elsif p_balance_name = G_TAXABLE_PAY_BALANCE then
961 --
962 l_balance_type := g_taxable_pay_type;
963 l_dimension_suffix := g_tax_district_ytd;
964 --
965 elsif p_balance_name = G_PAYE_BALANCE then
966 --
967 l_balance_type := g_paye_type;
968 l_dimension_suffix := g_tax_district_ytd;
969 --
970 elsif p_balance_name = G_PAYE_TRANSFER then
971 --
972 l_balance_type := g_paye_type;
973 l_dimension_suffix := g_period_to_date;
974 --
975 elsif p_balance_name = G_NIABLE_PAY_BALANCE then
976 --
977 l_balance_type := g_niable_pay_type;
978 l_dimension_suffix := g_tax_district_ytd;
979 --
980 elsif p_balance_name = G_NI_A_EMPLOYEE_BALANCE then
981 --
982 l_balance_type := g_ni_a_employee_type;
983 l_dimension_suffix := g_tax_district_ytd;
984 --
985 elsif p_balance_name = G_NI_B_EMPLOYEE_BALANCE then
986 --
987 l_balance_type := g_ni_b_employee_type;
988 l_dimension_suffix := g_tax_district_ytd;
989 --
990 elsif p_balance_name = G_NI_D_EMPLOYEE_BALANCE then
991 --
992 l_balance_type := g_ni_d_employee_type;
993 l_dimension_suffix := g_tax_district_ytd;
994 --
995 elsif p_balance_name = G_NI_E_EMPLOYEE_BALANCE then
996 --
997 l_balance_type := g_ni_e_employee_type;
998 l_dimension_suffix := g_tax_district_ytd;
999 --
1000 elsif p_balance_name = G_NI_F_EMPLOYEE_BALANCE then
1001 --
1002 l_balance_type := g_ni_f_employee_type;
1003 l_dimension_suffix := g_tax_district_ytd;
1004 --
1005 elsif p_balance_name = G_NI_G_EMPLOYEE_BALANCE then
1006 --
1007 l_balance_type := g_ni_G_employee_type;
1008 l_dimension_suffix := g_tax_district_ytd;
1009 --
1010 elsif p_balance_name = G_NI_J_EMPLOYEE_BALANCE then
1011 --
1012 l_balance_type := g_ni_j_employee_type;
1013 l_dimension_suffix := g_tax_district_ytd;
1014 --
1015 elsif p_balance_name = G_NI_L_EMPLOYEE_BALANCE then
1016 --
1017 l_balance_type := g_ni_l_employee_type;
1018 l_dimension_suffix := g_tax_district_ytd;
1019 --
1020 elsif p_balance_name = G_NI_S_EMPLOYEE_BALANCE then
1021 --
1022 l_balance_type := g_ni_s_employee_type;
1023 l_dimension_suffix := g_tax_district_ytd;
1024 --
1025 elsif p_balance_name = G_NI_A_EMPLOYEE_TRANSFER then
1026 --
1027 l_balance_type := g_ni_a_employee_type;
1028 l_dimension_suffix := g_period_to_date;
1029 --
1030 elsif p_balance_name = G_NI_B_EMPLOYEE_TRANSFER then
1031 --
1032 l_balance_type := g_ni_b_employee_type;
1033 l_dimension_suffix := g_period_to_date;
1034 --
1035 elsif p_balance_name = G_NI_D_EMPLOYEE_TRANSFER then
1036 --
1037 l_balance_type := g_ni_d_employee_type;
1038 l_dimension_suffix := g_period_to_date;
1039 --
1040 elsif p_balance_name = G_NI_E_EMPLOYEE_TRANSFER then
1041 --
1042 l_balance_type := g_ni_e_employee_type;
1043 l_dimension_suffix := g_period_to_date;
1044 --
1045 elsif p_balance_name = G_NI_F_EMPLOYEE_TRANSFER then
1046 --
1047 l_balance_type := g_ni_f_employee_type;
1048 l_dimension_suffix := g_period_to_date;
1049 --
1050 elsif p_balance_name = G_NI_G_EMPLOYEE_TRANSFER then
1051 --
1052 l_balance_type := g_ni_g_employee_type;
1053 l_dimension_suffix := g_period_to_date;
1054 --
1055 elsif p_balance_name = G_NI_J_EMPLOYEE_TRANSFER then
1056 --
1057 l_balance_type := g_ni_j_employee_type;
1058 l_dimension_suffix := g_period_to_date;
1059 --
1060 elsif p_balance_name = G_NI_L_EMPLOYEE_TRANSFER then
1061 --
1062 l_balance_type := g_ni_l_employee_type;
1063 l_dimension_suffix := g_period_to_date;
1064 --
1065 elsif p_balance_name = G_NI_S_EMPLOYEE_TRANSFER then
1066 --
1067 l_balance_type := g_ni_s_employee_type;
1068 l_dimension_suffix := g_period_to_date;
1069 --
1070 elsif p_balance_name = G_NI_A_ABLE_BALANCE then
1071 --
1072 l_balance_type := g_ni_a_able_type;
1073 l_dimension_suffix := g_tax_district_ytd;
1074 --
1075 elsif p_balance_name = G_NI_B_ABLE_BALANCE then
1076 --
1077 l_balance_type := g_ni_b_able_type;
1078 l_dimension_suffix := g_tax_district_ytd;
1079 --
1080 elsif p_balance_name = G_NI_D_ABLE_BALANCE then
1081 --
1082 l_balance_type := g_ni_d_able_type;
1083 l_dimension_suffix := g_tax_district_ytd;
1084 --
1085 elsif p_balance_name = G_NI_E_ABLE_BALANCE then
1086 --
1087 l_balance_type := g_ni_e_able_type;
1088 l_dimension_suffix := g_tax_district_ytd;
1089 --
1090 elsif p_balance_name = G_NI_F_ABLE_BALANCE then
1091 --
1092 l_balance_type := g_ni_f_able_type;
1093 l_dimension_suffix := g_tax_district_ytd;
1094 --
1095 elsif p_balance_name = G_NI_G_ABLE_BALANCE then
1096 --
1097 l_balance_type := g_ni_g_able_type;
1098 l_dimension_suffix := g_tax_district_ytd;
1099 --
1100 elsif p_balance_name = G_NI_J_ABLE_BALANCE then
1101 --
1102 l_balance_type := g_ni_j_able_type;
1103 l_dimension_suffix := g_tax_district_ytd;
1104 --
1105 elsif p_balance_name = G_NI_L_ABLE_BALANCE then
1106 --
1107 l_balance_type := g_ni_l_able_type;
1108 l_dimension_suffix := g_tax_district_ytd;
1109 --
1110 elsif p_balance_name = G_NI_S_ABLE_BALANCE then
1111 --
1112 l_balance_type := g_ni_s_able_type;
1113 l_dimension_suffix := g_tax_district_ytd;
1114 --
1115 elsif p_balance_name = G_NI_EMPLOYER_BALANCE then
1116 --
1117 l_balance_type := g_ni_employer_type;
1118 l_dimension_suffix := g_tax_district_ytd;
1119 --
1120 elsif p_balance_name = G_NI_EMPLOYER_TRANSFER then
1121 --
1122 l_balance_type := g_ni_employer_type;
1123 l_dimension_suffix := g_period_to_date;
1124 --
1125 elsif p_balance_name = G_NIABLE_PAY_TRANSFER then
1126 --
1127 l_balance_type := g_niable_pay_type;
1128 l_dimension_suffix := g_period_to_date;
1129 --
1130 elsif p_balance_name = G_NI_A_ABLE_TRANSFER then
1131 --
1132 l_balance_type := g_ni_a_able_type;
1133 l_dimension_suffix := g_period_to_date;
1134 --
1135 elsif p_balance_name = G_NI_B_ABLE_TRANSFER then
1136 --
1137 l_balance_type := g_ni_b_able_type;
1138 l_dimension_suffix := g_period_to_date;
1139 --
1140 elsif p_balance_name = G_NI_D_ABLE_TRANSFER then
1141 --
1142 l_balance_type := g_ni_d_able_type;
1143 l_dimension_suffix := g_period_to_date;
1144 --
1145 elsif p_balance_name = G_NI_E_ABLE_TRANSFER then
1146 --
1147 l_balance_type := g_ni_e_able_type;
1148 l_dimension_suffix := g_period_to_date;
1149 --
1150 elsif p_balance_name = G_NI_F_ABLE_TRANSFER then
1151 --
1152 l_balance_type := g_ni_f_able_type;
1153 l_dimension_suffix := g_period_to_date;
1154 --
1155 elsif p_balance_name = G_NI_G_ABLE_TRANSFER then
1156 --
1157 l_balance_type := g_ni_g_able_type;
1158 l_dimension_suffix := g_period_to_date;
1159 --
1160 elsif p_balance_name = G_NI_J_ABLE_TRANSFER then
1161 --
1162 l_balance_type := g_ni_j_able_type;
1163 l_dimension_suffix := g_period_to_date;
1164 --
1165 elsif p_balance_name = G_NI_L_ABLE_TRANSFER then
1166 --
1167 l_balance_type := g_ni_l_able_type;
1168 l_dimension_suffix := g_period_to_date;
1169 --
1170 elsif p_balance_name = G_NI_S_ABLE_TRANSFER then
1171 --
1172 l_balance_type := g_ni_s_able_type;
1173 l_dimension_suffix := g_period_to_date;
1174 --
1175 elsif p_balance_name = G_GROSS_PAY_PTD_BALANCE then
1176 --
1177 l_balance_type := g_gross_pay_type;
1178 l_dimension_suffix := g_proc_period_to_date;
1179 --
1180 elsif p_balance_name = G_TAXABLE_PAY_TRANSFER then
1181 --
1182 l_balance_type := g_taxable_pay_type;
1183 l_dimension_suffix := g_period_to_date;
1184 --
1185 elsif p_balance_name = G_NI_A_TOTAL then
1186 --
1187 l_balance_type := g_ni_a_total_type;
1188 l_dimension_suffix := g_tax_district_ytd;
1189 --
1190 elsif p_balance_name = G_NI_B_TOTAL then
1191 --
1192 l_balance_type := g_ni_b_total_type;
1193 l_dimension_suffix := g_tax_district_ytd;
1194 --
1195 elsif p_balance_name = G_NI_D_TOTAL then
1196 --
1197 l_balance_type := g_ni_d_total_type;
1198 l_dimension_suffix := g_tax_district_ytd;
1199 --
1200 elsif p_balance_name = G_NI_E_TOTAL then
1201 --
1202 l_balance_type := g_ni_e_total_type;
1203 l_dimension_suffix := g_tax_district_ytd;
1204 --
1205 elsif p_balance_name = G_NI_F_TOTAL then
1206 --
1207 l_balance_type := g_ni_f_total_type;
1208 l_dimension_suffix := g_tax_district_ytd;
1209 --
1210 elsif p_balance_name = G_NI_G_TOTAL then
1211 --
1212 l_balance_type := g_ni_g_total_type;
1213 l_dimension_suffix := g_tax_district_ytd;
1214 --
1215 elsif p_balance_name = G_NI_J_TOTAL then
1216 --
1217 l_balance_type := g_ni_j_total_type;
1218 l_dimension_suffix := g_tax_district_ytd;
1219 --
1220 elsif p_balance_name = G_NI_L_TOTAL then
1221 --
1222 l_balance_type := g_ni_l_total_type;
1223 l_dimension_suffix := g_tax_district_ytd;
1224 --
1225 elsif p_balance_name = G_NI_S_TOTAL then
1226 --
1227 l_balance_type := g_ni_s_total_type;
1228 l_dimension_suffix := g_tax_district_ytd;
1229 --
1230 elsif p_balance_name = G_SUPERAN_BALANCE then
1231 --
1232 l_balance_type := g_superan_total_type;
1233 l_dimension_suffix := g_tax_district_ytd;
1234 --
1235 elsif p_balance_name = G_NI_C_EMPLOYER then
1236 --
1237 l_balance_type := g_ni_c_employer_type;
1238 l_dimension_suffix := g_tax_district_ytd;
1239 --
1240 elsif p_balance_name = G_NI_S_EMPLOYER then
1241 --
1242 l_balance_type := g_ni_s_employer_type;
1243 l_dimension_suffix := g_tax_district_ytd;
1244 --
1245 end if;
1246 --
1247 -- derive defined balance ID
1248 --
1249 l_defined_balance_id := defined_balance_id
1250 (p_balance_type => l_balance_type,
1251 p_dimension_suffix => l_dimension_suffix);
1252 --
1253 return pay_balance_pkg.get_value
1254 (p_defined_balance_id => l_defined_balance_id,
1255 p_assignment_action_id => p_assignment_action_id);
1256 --
1257 end;
1258 --
1259 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1260 PROCEDURE get_report_balances (p_assignment_action_id in number,
1261 p_label_1 in out nocopy varchar2,
1262 p_value_1 in out nocopy number,
1263 p_label_2 in out nocopy varchar2,
1264 p_value_2 in out nocopy number,
1265 p_label_3 in out nocopy varchar2,
1266 p_value_3 in out nocopy number,
1267 p_label_4 in out nocopy varchar2,
1268 p_value_4 in out nocopy number,
1269 p_label_5 in out nocopy varchar2,
1270 p_value_5 in out nocopy number,
1271 p_label_6 in out nocopy varchar2,
1272 p_value_6 in out nocopy number,
1273 p_label_7 in out nocopy varchar2,
1274 p_value_7 in out nocopy number,
1275 p_label_8 in out nocopy varchar2,
1276 p_value_8 in out nocopy number,
1277 p_label_9 in out nocopy varchar2,
1278 p_value_9 in out nocopy number,
1279 p_label_a in out nocopy varchar2,
1280 p_value_a in out nocopy number,
1281 p_label_b in out nocopy varchar2,
1282 p_value_b in out nocopy number,
1283 p_label_c in out nocopy varchar2,
1284 p_value_c in out nocopy number) is
1285 --
1286 l_ni_a_employee_value number :=0;
1287 l_ni_b_employee_value number :=0;
1288 l_ni_d_employee_value number :=0;
1289 l_ni_e_employee_value number :=0;
1290 l_ni_f_employee_value number :=0;
1291 l_ni_g_employee_value number :=0;
1292 l_ni_j_employee_value number :=0;
1293 l_ni_l_employee_value number :=0;
1294 l_ni_s_employee_value number :=0;
1295
1296 l_ni_a_able_balance number :=0;
1297 l_ni_b_able_balance number :=0;
1298 l_ni_d_able_balance number :=0;
1299 l_ni_e_able_balance number :=0;
1300 l_ni_f_able_balance number :=0;
1301 l_ni_g_able_balance number :=0;
1302 l_ni_j_able_balance number :=0;
1303 l_ni_l_able_balance number :=0;
1304 l_ni_s_able_balance number :=0;
1305
1306 l_ni_a_able_transfer number :=0;
1307 l_ni_b_able_transfer number :=0;
1308 l_ni_d_able_transfer number :=0;
1309 l_ni_e_able_transfer number :=0;
1310 l_ni_f_able_transfer number :=0;
1311 l_ni_g_able_transfer number :=0;
1312 l_ni_j_able_transfer number :=0;
1313 l_ni_l_able_transfer number :=0;
1314 l_ni_s_able_transfer number :=0;
1315
1316 l_ni_a_total number :=0;
1317 l_ni_b_total number :=0;
1318 l_ni_d_total number :=0;
1319 l_ni_e_total number :=0;
1320 l_ni_f_total number :=0;
1321 l_ni_g_total number :=0;
1322 l_ni_j_total number :=0;
1323 l_ni_l_total number :=0;
1324 l_ni_s_total number :=0;
1325
1326 l_ni_abdefg_total number :=0;
1327 l_ni_c_employer number :=0;
1328 l_ni_s_employer number :=0;
1329
1330 --
1331 BEGIN
1332 --
1333 -- if the assignment action id is not specified then do nothing
1334 --
1335 if p_assignment_action_id is null then
1336 --
1337 return;
1338 --
1339 end if;
1340 --
1341 --
1342 p_label_1 := 'Gross YTD';
1343 --
1344 p_value_1 := report_balance_items
1345 (p_balance_name => G_GROSS_PAY_BALANCE,
1346 p_assignment_action_id => p_assignment_action_id);
1347 --
1348 --
1349 p_label_2 := 'Gross PTD';
1350 --
1351 p_value_2 := report_balance_items
1352 (p_balance_name => G_GROSS_PAY_PTD_BALANCE,
1353 p_assignment_action_id => p_assignment_action_id);
1354 --
1355 --
1356 p_label_3 := 'Taxable YTD';
1357 --
1358 p_value_3 := report_balance_items
1359 (p_balance_name => G_TAXABLE_PAY_BALANCE,
1360 p_assignment_action_id => p_assignment_action_id);
1361 --
1362 --
1363 p_label_4 := 'Taxable PTD ';
1364 --
1365 p_value_4 := report_balance_items
1366 (p_balance_name => G_TAXABLE_PAY_TRANSFER,
1367 p_assignment_action_id => p_assignment_action_id);
1368 --
1369 --
1370 p_label_5 := 'PAYE YTD';
1371 --
1372 p_value_5 := report_balance_items
1373 (p_balance_name => G_PAYE_BALANCE,
1374 p_assignment_action_id => p_assignment_action_id);
1375 --
1376 --
1377 ------------------------------------------------------------------------
1378 -- NI CALCULATION --
1379 ------------------------------------------------------------------------
1380 --
1381 /*
1382 l_ni_a_total := report_balance_items
1383 (p_balance_name => G_NI_A_TOTAL,
1384 p_assignment_action_id => p_assignment_action_id);
1385 --
1386 l_ni_b_total := report_balance_items
1387 (p_balance_name => G_NI_B_TOTAL,
1388 p_assignment_action_id => p_assignment_action_id);
1389 --
1390 l_ni_d_total := report_balance_items
1391 (p_balance_name => G_NI_D_TOTAL,
1392 p_assignment_action_id => p_assignment_action_id);
1393 --
1394 l_ni_e_total := report_balance_items
1395 (p_balance_name => G_NI_E_TOTAL,
1396 p_assignment_action_id => p_assignment_action_id);
1397 --
1398 l_ni_f_total := report_balance_items
1399 (p_balance_name => G_NI_F_TOTAL,
1400 p_assignment_action_id => p_assignment_action_id);
1401 --
1402 l_ni_g_total := report_balance_items
1403 (p_balance_name => G_NI_G_TOTAL,
1404 p_assignment_action_id => p_assignment_action_id);
1405 --
1406 */
1407 -------------------------------------------------------------------------
1408 -- IF l_ni_a_total <> 0 THEN
1409 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'A') = 1 THEN
1410 l_ni_a_total := report_balance_items
1411 (p_balance_name => G_NI_A_TOTAL,
1412 p_assignment_action_id => p_assignment_action_id);
1413 l_ni_a_able_balance := report_balance_items
1414 (p_balance_name => G_NI_A_ABLE_BALANCE,
1415 p_assignment_action_id => p_assignment_action_id);
1416 l_ni_a_able_transfer := report_balance_items
1417 (p_balance_name => G_NI_A_ABLE_TRANSFER,
1418 p_assignment_action_id => p_assignment_action_id);
1419 l_ni_a_employee_value := report_balance_items
1420 (p_balance_name => G_NI_A_EMPLOYEE_BALANCE,
1421 p_assignment_action_id => p_assignment_action_id);
1422 END IF;
1423 --
1424 -- IF l_ni_b_total <> 0 THEN
1425 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'B') = 1 THEN
1426 l_ni_b_total := report_balance_items
1427 (p_balance_name => G_NI_B_TOTAL,
1428 p_assignment_action_id => p_assignment_action_id);
1429 l_ni_b_able_balance := report_balance_items
1430 (p_balance_name => G_NI_B_ABLE_BALANCE,
1431 p_assignment_action_id => p_assignment_action_id);
1432 l_ni_b_able_transfer := report_balance_items
1433 (p_balance_name => G_NI_B_ABLE_TRANSFER,
1434 p_assignment_action_id => p_assignment_action_id);
1435 l_ni_b_employee_value := report_balance_items
1436 (p_balance_name => G_NI_B_EMPLOYEE_BALANCE,
1437 p_assignment_action_id => p_assignment_action_id);
1438 END IF;
1439 --
1440 -- IF l_ni_d_total <> 0 THEN
1441 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'D') = 1 THEN
1442 l_ni_d_total := report_balance_items
1443 (p_balance_name => G_NI_D_TOTAL,
1444 p_assignment_action_id => p_assignment_action_id);
1445 l_ni_d_able_balance := report_balance_items
1446 (p_balance_name => G_NI_D_ABLE_BALANCE,
1447 p_assignment_action_id => p_assignment_action_id);
1448 l_ni_d_able_transfer := report_balance_items
1449 (p_balance_name => G_NI_D_ABLE_TRANSFER,
1450 p_assignment_action_id => p_assignment_action_id);
1451 l_ni_d_employee_value := report_balance_items
1452 (p_balance_name => G_NI_D_EMPLOYEE_BALANCE,
1453 p_assignment_action_id => p_assignment_action_id);
1454 END IF;
1455 --
1456 -- IF l_ni_e_total <> 0 THEN
1457 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'E') = 1 THEN
1458 l_ni_e_total := report_balance_items
1459 (p_balance_name => G_NI_E_TOTAL,
1460 p_assignment_action_id => p_assignment_action_id);
1461 l_ni_e_able_balance := report_balance_items
1462 (p_balance_name => G_NI_E_ABLE_BALANCE,
1463 p_assignment_action_id => p_assignment_action_id);
1464 l_ni_e_able_transfer := report_balance_items
1465 (p_balance_name => G_NI_E_ABLE_TRANSFER,
1466 p_assignment_action_id => p_assignment_action_id);
1467 l_ni_e_employee_value := report_balance_items
1468 (p_balance_name => G_NI_E_EMPLOYEE_BALANCE,
1469 p_assignment_action_id => p_assignment_action_id);
1470 END IF;
1471 --
1472 -- IF l_ni_f_total <> 0 THEN
1473 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'F') = 1 THEN
1474 l_ni_f_total := report_balance_items
1475 (p_balance_name => G_NI_f_TOTAL,
1476 p_assignment_action_id => p_assignment_action_id);
1477 l_ni_f_able_balance := report_balance_items
1478 (p_balance_name => G_NI_F_ABLE_BALANCE,
1479 p_assignment_action_id => p_assignment_action_id);
1480 l_ni_f_able_transfer := report_balance_items
1481 (p_balance_name => G_NI_F_ABLE_TRANSFER,
1482 p_assignment_action_id => p_assignment_action_id);
1483 l_ni_f_employee_value := report_balance_items
1484 (p_balance_name => G_NI_F_EMPLOYEE_BALANCE,
1485 p_assignment_action_id => p_assignment_action_id);
1486 END IF;
1487 --
1488 -- IF l_ni_g_total <> 0 THEN
1489 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'G') = 1 THEN
1490 l_ni_g_total := report_balance_items
1491 (p_balance_name => G_NI_G_TOTAL,
1492 p_assignment_action_id => p_assignment_action_id);
1493 l_ni_g_able_balance := report_balance_items
1494 (p_balance_name => G_NI_G_ABLE_BALANCE,
1495 p_assignment_action_id => p_assignment_action_id);
1496 l_ni_g_able_transfer := report_balance_items
1497 (p_balance_name => G_NI_G_ABLE_TRANSFER,
1498 p_assignment_action_id => p_assignment_action_id);
1499 l_ni_g_employee_value := report_balance_items
1500 (p_balance_name => G_NI_G_EMPLOYEE_BALANCE,
1501 p_assignment_action_id => p_assignment_action_id);
1502 END IF;
1503 --
1504 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'J') = 1 THEN
1505 l_ni_j_total := report_balance_items
1506 (p_balance_name => G_NI_J_TOTAL,
1507 p_assignment_action_id => p_assignment_action_id);
1508 l_ni_j_able_balance := report_balance_items
1509 (p_balance_name => G_NI_J_ABLE_BALANCE,
1510 p_assignment_action_id => p_assignment_action_id);
1511 l_ni_j_able_transfer := report_balance_items
1512 (p_balance_name => G_NI_J_ABLE_TRANSFER,
1513 p_assignment_action_id => p_assignment_action_id);
1514 l_ni_j_employee_value := report_balance_items
1515 (p_balance_name => G_NI_J_EMPLOYEE_BALANCE,
1516 p_assignment_action_id => p_assignment_action_id);
1517 END IF;
1518 --
1519 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'L') = 1 THEN
1520 l_ni_l_total := report_balance_items
1521 (p_balance_name => G_NI_L_TOTAL,
1522 p_assignment_action_id => p_assignment_action_id);
1523 l_ni_l_able_balance := report_balance_items
1524 (p_balance_name => G_NI_L_ABLE_BALANCE,
1525 p_assignment_action_id => p_assignment_action_id);
1526 l_ni_l_able_transfer := report_balance_items
1527 (p_balance_name => G_NI_L_ABLE_TRANSFER,
1528 p_assignment_action_id => p_assignment_action_id);
1529 l_ni_l_employee_value := report_balance_items
1530 (p_balance_name => G_NI_L_EMPLOYEE_BALANCE,
1531 p_assignment_action_id => p_assignment_action_id);
1532 END IF;
1533 --
1534 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'S') = 1 THEN
1535 l_ni_s_total := report_balance_items
1536 (p_balance_name => G_NI_S_TOTAL,
1537 p_assignment_action_id => p_assignment_action_id);
1538 l_ni_s_able_balance := report_balance_items
1539 (p_balance_name => G_NI_S_ABLE_BALANCE,
1540 p_assignment_action_id => p_assignment_action_id);
1541 l_ni_s_able_transfer := report_balance_items
1542 (p_balance_name => G_NI_S_ABLE_TRANSFER,
1543 p_assignment_action_id => p_assignment_action_id);
1544 l_ni_s_employee_value := report_balance_items
1545 (p_balance_name => G_NI_S_EMPLOYEE_BALANCE,
1546 p_assignment_action_id => p_assignment_action_id);
1547 END IF;
1548 --
1549 l_ni_abdefg_total := l_ni_a_total + l_ni_b_total +
1550 l_ni_d_total + l_ni_e_total +
1551 l_ni_f_total + l_ni_g_total +
1552 l_ni_j_total + l_ni_l_total +
1553 l_ni_s_total;
1554
1555 p_label_6 := 'NIable YTD';
1556 p_value_6 := l_ni_a_able_balance + l_ni_b_able_balance +
1557 l_ni_d_able_balance + l_ni_e_able_balance +
1558 l_ni_f_able_balance + l_ni_g_able_balance +
1559 l_ni_j_able_balance + l_ni_l_able_balance +
1560 l_ni_s_able_balance;
1561 --
1562 p_label_7 := 'NIable PTD';
1563 p_value_7 := l_ni_a_able_transfer + l_ni_b_able_transfer +
1564 l_ni_d_able_transfer + l_ni_e_able_transfer +
1565 l_ni_f_able_transfer + l_ni_g_able_transfer +
1566 l_ni_j_able_transfer + l_ni_l_able_transfer +
1567 l_ni_s_able_transfer;
1568 --
1569 p_label_8 := 'NI Ees YTD';
1570 p_value_8 := l_ni_a_employee_value + l_ni_b_employee_value +
1571 l_ni_d_employee_value + l_ni_e_employee_value +
1572 l_ni_f_employee_value + l_ni_g_employee_value +
1573 l_ni_j_employee_value + l_ni_l_employee_value +
1574 l_ni_s_employee_value;
1575 --
1576 l_ni_c_employer := report_balance_items
1577 (p_balance_name => G_NI_C_EMPLOYER,
1578 p_assignment_action_id => p_assignment_action_id);
1579 --
1580 l_ni_s_employer := report_balance_items
1581 (p_balance_name => G_NI_S_EMPLOYER,
1582 p_assignment_action_id => p_assignment_action_id);
1583 --
1584 p_label_9:= 'NI Ers YTD';
1585 -- p_value_b := report_balance_items
1586 -- (p_balance_name => G_NI_EMPLOYER_BALANCE,
1587 -- p_assignment_action_id => p_assignment_action_id);
1588 -- G_NI_EMPLOYER is not forced to be a latest balance in NI formula, so use:
1589 -- NI_x_EMPLOYER = NI_x_TOTAL - NI_x_EMPLOYEE
1590 p_value_9 := l_ni_abdefg_total - p_value_a
1591 + l_ni_c_employer + l_ni_s_employer;
1592 --
1593 p_label_a := 'Superan YTD';
1594 p_value_a := report_balance_items
1595 (p_balance_name => G_SUPERAN_BALANCE,
1596 p_assignment_action_id => p_assignment_action_id);
1597 --
1598 END;
1599 --
1600 -- End of overloded function definitions for bug 879804.
1601 --
1602 -------------------------------------------------------------------------------
1603 --
1604 -- calculates the balance across NI categories A B D E F G
1605 -- eg. NI Total = NI A Total + NI B Total + NI D Total + ...
1606 --
1607 -- The NI Total for each category has already been determined. It is only worth
1608 -- looking for other NI balances if the NI Total for the category is not zero.
1609 --
1610 function report_all_ni_balance (p_balance_name in varchar2,
1611 p_assignment_action_id in number,
1612 p_dimension in varchar2) return number is
1613
1614 l_total number := 0;
1615 l_all_cat_total number := 0;
1616
1617 l_A_balance varchar2(80);
1618 l_B_balance varchar2(80);
1619 l_D_balance varchar2(80);
1620 l_E_balance varchar2(80);
1621 l_F_balance varchar2(80);
1622 l_G_balance varchar2(80);
1623 l_J_balance varchar2(80);
1624 l_L_balance varchar2(80);
1625 l_S_balance varchar2(80);
1626 --
1627 begin
1628 --
1629
1630 l_A_balance := replace(p_balance_name,'NI ','NI A ');
1631 l_B_balance := replace(p_balance_name,'NI ','NI B ');
1632 l_D_balance := replace(p_balance_name,'NI ','NI D ');
1633 l_E_balance := replace(p_balance_name,'NI ','NI E ');
1634 l_F_balance := replace(p_balance_name,'NI ','NI F ');
1635 l_G_balance := replace(p_balance_name,'NI ','NI G ');
1636 l_J_balance := replace(p_balance_name,'NI ','NI J ');
1637 l_L_balance := replace(p_balance_name,'NI ','NI L ');
1638 l_S_balance := replace(p_balance_name,'NI ','NI S ');
1639
1640 -- if g_ni_a_total_value <> 0
1641 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'A') = 1 then
1642 l_total := report_balance_items(p_balance_name => l_A_balance,
1643 p_dimension => p_dimension,
1644 p_assignment_action_id => p_assignment_action_id);
1645
1646 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1647 end if;
1648
1649
1650 --if g_ni_b_total_value <> 0
1651 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'B') = 1 then
1652 l_total := report_balance_items(p_balance_name => l_B_balance,
1653 p_dimension => p_dimension,
1654 p_assignment_action_id => p_assignment_action_id);
1655
1656 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1657 end if;
1658
1659
1660 -- if g_ni_d_total_value <> 0
1661 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'D') = 1 then
1662 l_total := report_balance_items(p_balance_name => l_D_balance,
1663 p_dimension => p_dimension,
1664 p_assignment_action_id => p_assignment_action_id);
1665
1666 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1667 end if;
1668
1669 -- if g_ni_e_total_value <> 0
1670 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'E') = 1 then
1671 l_total := report_balance_items(p_balance_name => l_E_balance,
1672 p_dimension => p_dimension,
1673 p_assignment_action_id => p_assignment_action_id);
1674
1675 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1676 end if;
1677
1678 -- if g_ni_f_total_value <> 0
1679 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'F') = 1 then
1680 l_total := report_balance_items(p_balance_name => l_F_balance,
1681 p_dimension => p_dimension,
1682 p_assignment_action_id => p_assignment_action_id);
1683
1684 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1685 end if;
1686
1687 -- if g_ni_g_total_value <> 0
1688 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'G') = 1 then
1689 l_total := report_balance_items(p_balance_name => l_G_balance,
1690 p_dimension => p_dimension,
1691 p_assignment_action_id => p_assignment_action_id);
1692
1693 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1694 end if;
1695
1696 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'J') = 1 then
1697 l_total := report_balance_items(p_balance_name => l_J_balance,
1698 p_dimension => p_dimension,
1699 p_assignment_action_id => p_assignment_action_id);
1700
1701 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1702 end if;
1703
1704 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'L') = 1 then
1705 l_total := report_balance_items(p_balance_name => l_L_balance,
1706 p_dimension => p_dimension,
1707 p_assignment_action_id => p_assignment_action_id);
1708
1709 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1710 end if;
1711
1712 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'S') = 1 then
1713 l_total := report_balance_items(p_balance_name => l_S_balance,
1714 p_dimension => p_dimension,
1715 p_assignment_action_id => p_assignment_action_id);
1716
1717 l_all_cat_total := l_all_cat_total + nvl(l_total,0);
1718 end if;
1719
1720
1721 return l_all_cat_total;
1722 --
1723 end report_all_ni_balance;
1724 --
1725 -------------------------------------------------------------------------
1726 --
1727 -- The NI Employer Balance is not forced to be the latest balance in NI formula,
1728 -- so the following is used :
1729 -- NI_x_EMPLOYER = NI_x_TOTAL - NI_x_EMPLOYEE + NI_C_EMPLOYER + NI_S_EMPLOYER
1730 --
1731 function report_employer_balance (p_assignment_action_id in number) return number is
1732
1733 l_temp_balance number := 0;
1734 l_employer_balance number := 0;
1735
1736 begin
1737 --
1738 -- The NI Total for each category has already been established, so the NI Total for
1739 -- all categories is the sum of the NI Total for each individual category
1740 --
1741 l_employer_balance := g_ni_a_total_value + g_ni_b_total_value + g_ni_d_total_value
1742 + g_ni_e_total_value + g_ni_f_total_value + g_ni_g_total_value
1743 + g_ni_j_total_value + g_ni_l_total_value + g_ni_s_total_value;
1744
1745 l_temp_balance := report_all_ni_balance
1746 (p_balance_name => 'NI Employee',
1747 p_dimension => g_tax_district_ytd,
1748 p_assignment_action_id => p_assignment_action_id);
1749
1750 l_employer_balance := l_employer_balance - l_temp_balance;
1751
1752 l_temp_balance := report_balance_items
1753 (p_balance_name => 'NI C Employer',
1754 p_dimension => g_tax_district_ytd,
1755 p_assignment_action_id => p_assignment_action_id);
1756
1757 l_employer_balance := l_employer_balance + l_temp_balance;
1758
1759 l_temp_balance := report_balance_items
1760 (p_balance_name => 'NI S Employer',
1761 p_dimension => g_tax_district_ytd,
1762 p_assignment_action_id => p_assignment_action_id);
1763
1764 l_employer_balance := l_employer_balance + l_temp_balance;
1765
1766 return l_employer_balance;
1767 --
1768 end;
1769 --
1770 -------------------------------------------------------------------------------
1771 --
1772 PROCEDURE get_balance_items (p_assignment_action_id in number,
1773 p_gross_pay in out nocopy number,
1774 p_taxable_pay in out nocopy number,
1775 p_paye in out nocopy number,
1776 p_niable_pay in out nocopy number,
1777 p_ni_paid in out nocopy number) is
1778 --
1779 l_ni_a_employee_value number;
1780 l_ni_b_employee_value number;
1781 l_ni_d_employee_value number;
1782 l_ni_e_employee_value number;
1783 l_ni_f_employee_value number;
1784 l_ni_g_employee_value number;
1785 l_ni_j_employee_value number;
1786 l_ni_l_employee_value number;
1787 l_ni_s_employee_value number;
1788
1789 --
1790 BEGIN
1791 --
1792 -- if the assignment action id is not specified then do nothing
1793 --
1794 if p_assignment_action_id is null then
1795 --
1796 return;
1797 --
1798 end if;
1799 --
1800 p_gross_pay := balance_item_value
1801 (p_balance_name => G_GROSS_PAY_BALANCE,
1802 p_assignment_action_id => p_assignment_action_id);
1803 --
1804 p_taxable_pay := balance_item_value
1805 (p_balance_name => G_TAXABLE_PAY_BALANCE,
1806 p_assignment_action_id => p_assignment_action_id);
1807 --
1808 p_paye := balance_item_value
1809 (p_balance_name => G_PAYE_BALANCE,
1810 p_assignment_action_id => p_assignment_action_id);
1811 --
1812 p_niable_pay := balance_item_value
1813 (p_balance_name => G_NIABLE_PAY_BALANCE,
1814 p_assignment_action_id => p_assignment_action_id);
1815 --
1816 --
1817 l_ni_a_employee_value := balance_item_value
1818 (p_balance_name => G_NI_A_EMPLOYEE_BALANCE,
1819 p_assignment_action_id => p_assignment_action_id);
1820 --
1821 l_ni_b_employee_value := balance_item_value
1822 (p_balance_name => G_NI_B_EMPLOYEE_BALANCE,
1823 p_assignment_action_id => p_assignment_action_id);
1824 --
1825 l_ni_d_employee_value := balance_item_value
1826 (p_balance_name => G_NI_D_EMPLOYEE_BALANCE,
1827 p_assignment_action_id => p_assignment_action_id);
1828 --
1829 l_ni_e_employee_value := balance_item_value
1830 (p_balance_name => G_NI_E_EMPLOYEE_BALANCE,
1831 p_assignment_action_id => p_assignment_action_id);
1832 --
1833 l_ni_f_employee_value := balance_item_value
1834 (p_balance_name => G_NI_F_EMPLOYEE_BALANCE,
1835 p_assignment_action_id => p_assignment_action_id);
1836 --
1837 l_ni_g_employee_value := balance_item_value
1838 (p_balance_name => G_NI_G_EMPLOYEE_BALANCE,
1839 p_assignment_action_id => p_assignment_action_id);
1840 --
1841 l_ni_j_employee_value := balance_item_value
1842 (p_balance_name => G_NI_J_EMPLOYEE_BALANCE,
1843 p_assignment_action_id => p_assignment_action_id);
1844 --
1845 l_ni_l_employee_value := balance_item_value
1846 (p_balance_name => G_NI_L_EMPLOYEE_BALANCE,
1847 p_assignment_action_id => p_assignment_action_id);
1848 --
1849 l_ni_s_employee_value := balance_item_value
1850 (p_balance_name => G_NI_S_EMPLOYEE_BALANCE,
1851 p_assignment_action_id => p_assignment_action_id);
1852 --
1853 p_ni_paid := l_ni_a_employee_value + l_ni_b_employee_value +
1854 l_ni_d_employee_value + l_ni_e_employee_value +
1855 l_ni_f_employee_value + l_ni_g_employee_value +
1856 l_ni_j_employee_value + l_ni_l_employee_value +
1857 l_ni_s_employee_value;
1858 --
1859 END;
1860 --
1861 -----------------------------------------------------------------------
1862 --
1863 PROCEDURE get_report_balances (p_assignment_action_id in number,
1864 p_business_group_id in number,
1865 p_label_1 in out nocopy varchar2,
1866 p_value_1 in out nocopy number,
1867 p_label_2 in out nocopy varchar2,
1868 p_value_2 in out nocopy number,
1869 p_label_3 in out nocopy varchar2,
1870 p_value_3 in out nocopy number,
1871 p_label_4 in out nocopy varchar2,
1872 p_value_4 in out nocopy number,
1873 p_label_5 in out nocopy varchar2,
1874 p_value_5 in out nocopy number,
1875 p_label_6 in out nocopy varchar2,
1876 p_value_6 in out nocopy number,
1877 p_label_7 in out nocopy varchar2,
1878 p_value_7 in out nocopy number,
1879 p_label_8 in out nocopy varchar2,
1880 p_value_8 in out nocopy number,
1881 p_label_9 in out nocopy varchar2,
1882 p_value_9 in out nocopy number,
1883 p_label_a in out nocopy varchar2,
1884 p_value_a in out nocopy number,
1885 p_label_b in out nocopy varchar2,
1886 p_value_b in out nocopy number,
1887 p_label_c in out nocopy varchar2,
1888 p_value_c in out nocopy number) is
1889 --
1890 --
1891 CURSOR c_selected_balances IS
1892 SELECT pur.row_low_range_or_name row_name,
1893 puci.value user_desc
1894 FROM pay_user_rows pur,
1895 pay_user_columns puc,
1896 pay_user_tables put,
1897 pay_user_column_instances puci
1898 WHERE put.user_table_name = g_user_table_name
1899 AND put.legislation_code = 'GB'
1900 AND puc.user_column_name = 'Narrative'
1901 AND puc.user_table_id = put.user_table_id
1902 AND puc.legislation_code = 'GB'
1903 AND puci.user_column_id = puc.user_column_id
1904 AND puci.value IS NOT NULL
1905 AND puci.business_group_id = p_business_group_id
1906 AND pur.user_row_id = puci.user_row_id;
1907 --
1908 CURSOR c_balance_check(user_balance varchar2) IS
1909 SELECT 1
1910 FROM pay_balance_types pbt,
1911 pay_balance_dimensions pbd,
1912 pay_defined_balances pdb
1913 WHERE pdb.balance_type_id = pbt.balance_type_id
1914 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1915 AND pbt.balance_name = user_balance
1916 AND nvl(pbt.legislation_code,'GB') = 'GB'
1917 AND nvl(pbd.legislation_code,'GB') = 'GB'
1918 AND nvl(pdb.legislation_code,'GB') = 'GB';
1919
1920 CURSOR csr_user_defined_balance(row_name varchar2) is
1921 SELECT pbt.balance_name,
1922 pbd.database_item_suffix
1923 FROM pay_balance_types pbt,
1924 pay_balance_dimensions pbd,
1925 pay_defined_balances pdb
1926 WHERE pbt.balance_type_id = pdb.balance_type_id
1927 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1928 AND row_name = pbt.balance_name || pbd.database_item_suffix;
1929
1930 CURSOR csr_get_action_type(c_assignment_action_id number) is
1931 SELECT ppa.action_type
1932 FROM pay_payroll_actions ppa,
1933 pay_assignment_actions paa
1934 WHERE ppa.payroll_action_id = paa.payroll_action_id
1935 AND paa.assignment_action_id = c_assignment_action_id;
1936
1937 l_calculated_employer number;
1938 l_calculated_balance number := 0;
1939 l_display_count number := 1;
1940 l_balance_check number;
1941 l_balance_name varchar2(80);
1942 l_dimension varchar2(80);
1943 l_user_bal_dim varchar2(150);
1944 l_user_desc varchar2(30);
1945 l_seq_no number;
1946 l_bal_ix binary_integer;
1947 l_max_seq_no number := 1000;
1948 l_user_defined_row_cnt number := 0;
1949 l_action_type varchar2(5);
1950 l_prepayment_id number;
1951 --
1952 BEGIN
1953 --
1954 -- if the assignment action id is not specified then do nothing
1955 --
1956 if p_assignment_action_id is null then
1957 --
1958 return;
1959 --
1960 end if;
1961 --
1962 --
1963 -- initialise the plsql tables and the NI total balances
1964 --
1965 g_displayed_balance := g_empty_balances;
1966 g_displayed_value := g_empty_values;
1967
1968 g_ni_a_total_value := 0;
1969 g_ni_b_total_value := 0;
1970 g_ni_d_total_value := 0;
1971 g_ni_e_total_value := 0;
1972 g_ni_f_total_value := 0;
1973 g_ni_g_total_value := 0;
1974 g_ni_j_total_value := 0;
1975 g_ni_l_total_value := 0;
1976 g_ni_s_total_value := 0;
1977
1978 --
1979 -- establish the total NI balance for each category. This is done now to
1980 -- cut down on processing when individual NI balances are retrieved later.
1981 --
1982 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'A') = 1 THEN
1983 g_ni_a_total_value := report_balance_items
1984 (p_balance_name => g_ni_a_total_type,
1985 p_dimension => g_tax_district_ytd,
1986 p_assignment_action_id => p_assignment_action_id);
1987 end if;
1988 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'B') = 1 THEN
1989 g_ni_b_total_value := report_balance_items
1990 (p_balance_name => g_ni_b_total_type,
1991 p_dimension => g_tax_district_ytd,
1992 p_assignment_action_id => p_assignment_action_id);
1993 end if;
1994
1995 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'D') = 1 THEN
1996 g_ni_d_total_value := report_balance_items
1997 (p_balance_name => g_ni_d_total_type,
1998 p_dimension => g_tax_district_ytd,
1999 p_assignment_action_id => p_assignment_action_id);
2000 end if;
2001
2002 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'E') = 1 THEN
2003 g_ni_e_total_value := report_balance_items
2004 (p_balance_name => g_ni_e_total_type,
2005 p_dimension => g_tax_district_ytd,
2006 p_assignment_action_id => p_assignment_action_id);
2007 end if;
2008
2009 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'F') = 1 THEN
2010 g_ni_f_total_value := report_balance_items
2011 (p_balance_name => g_ni_f_total_type,
2012 p_dimension => g_tax_district_ytd,
2013 p_assignment_action_id => p_assignment_action_id);
2014 end if;
2015
2016 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'G') = 1 THEN
2017 g_ni_g_total_value := report_balance_items
2018 (p_balance_name => g_ni_g_total_type,
2019 p_dimension => g_tax_district_ytd,
2020 p_assignment_action_id => p_assignment_action_id);
2021 end if;
2022
2023 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'J') = 1 THEN
2024 g_ni_j_total_value := report_balance_items
2025 (p_balance_name => g_ni_j_total_type,
2026 p_dimension => g_tax_district_ytd,
2027 p_assignment_action_id => p_assignment_action_id);
2028 end if;
2029
2030 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'L') = 1 THEN
2031 g_ni_l_total_value := report_balance_items
2032 (p_balance_name => g_ni_l_total_type,
2033 p_dimension => g_tax_district_ytd,
2034 p_assignment_action_id => p_assignment_action_id);
2035 end if;
2036
2037 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'S') = 1 THEN
2038 g_ni_s_total_value := report_balance_items
2039 (p_balance_name => g_ni_s_total_type,
2040 p_dimension => g_tax_district_ytd,
2041 p_assignment_action_id => p_assignment_action_id);
2042 end if;
2043
2044 OPEN csr_get_action_type(p_assignment_action_id);
2045 FETCH csr_get_action_type INTO l_action_type;
2046 CLOSE csr_get_action_type;
2047 --
2048 -- if the action type is Q or R, check to see whether or not the PrePayment has been run
2049 --
2050 if l_action_type in ('Q','R') then
2051 BEGIN
2052 l_prepayment_id := pay_core_utils.get_pp_action_id(l_action_type,p_assignment_action_id);
2053 EXCEPTION
2054 WHEN NO_DATA_FOUND THEN
2055 l_prepayment_id := NULL;
2056 END;
2057 end if;
2058 --
2059 for rec in c_selected_balances loop
2060
2061 l_user_defined_row_cnt := l_user_defined_row_cnt + 1;
2062
2063 if rec.row_name like '%USER-REG'
2064 then
2065 l_user_bal_dim := replace(rec.row_name,' USER-REG');
2066 open csr_user_defined_balance(l_user_bal_dim);
2067 fetch csr_user_defined_balance into l_balance_name,
2068 l_dimension;
2069 close csr_user_defined_balance;
2070 else
2071 l_balance_name := substr(rec.row_name,1,(instr(rec.row_name,' ',-1,1) - 1));
2072 l_dimension := substr(rec.row_name,instr(rec.row_name,' ',-1,1));
2073 l_dimension := replace(l_dimension,' ','_');
2074 end if;
2075
2076 -- if the prepayment hasn't been run then use SOE_RUN dimension instead of PAYMENTS
2077 --
2078 if l_prepayment_id IS NULL then
2079 l_dimension := replace(l_dimension,'PAYMENTS','SOE_RUN');
2080 end if;
2081
2082 open c_balance_check(l_balance_name);
2083 fetch c_balance_check into l_balance_check;
2084 --
2085 -- if the balance is not in the pay_balance_types table, then it is a balance for all
2086 -- NI categories and its value is determined differently
2087 --
2088 if c_balance_check%NOTFOUND
2089 then
2090 l_calculated_balance := report_all_ni_balance
2091 (p_balance_name => l_balance_name,
2092 p_dimension => l_dimension,
2093 p_assignment_action_id => p_assignment_action_id);
2094 else
2095 if l_balance_name = 'NI Employer'
2096 then
2097 l_calculated_balance := report_employer_balance
2098 (p_assignment_action_id => p_assignment_action_id);
2099 else
2100 l_calculated_balance := report_balance_items
2101 (p_balance_name => l_balance_name,
2102 p_dimension => l_dimension,
2103 p_assignment_action_id => p_assignment_action_id);
2104 end if;
2105 end if;
2106
2107 close c_balance_check;
2108 --
2109 -- only balances with non-zero values should be returned for display
2110 --
2111 if l_calculated_balance <> 0
2112 then
2113 --
2114 find_user_table_values(rec.row_name,
2115 p_business_group_id,
2116 l_seq_no);
2117 --
2118 -- if the sequence number is either not defined or is a duplicate then
2119 -- the balance is sorted to the end
2120 --
2121 if g_displayed_balance.EXISTS(l_seq_no) or l_seq_no is NULL
2122 then
2123 l_seq_no := l_max_seq_no;
2124 l_max_seq_no := l_max_seq_no + 10;
2125 end if;
2126
2127 g_displayed_balance(l_seq_no) := rec.user_desc;
2128 g_displayed_value(l_seq_no) := l_calculated_balance;
2129 l_display_count := l_display_count + 1;
2130
2131 end if;
2132 --
2133 end loop;
2134 --
2135 -- If no rows have been defined in the user defined table, then a default set of balances
2136 -- will be displayed, if they have non-zero values
2137 --
2138 if l_user_defined_row_cnt = 0
2139 then
2140
2141 l_display_count := 1;
2142
2143 l_calculated_balance := report_balance_items
2144 (p_balance_name => 'Gross Pay',
2145 p_dimension => '_ASG_YTD',
2146 p_assignment_action_id => p_assignment_action_id);
2147 if l_calculated_balance <> 0
2148 then
2149 g_displayed_balance(l_display_count) := 'Gross YTD';
2150 g_displayed_value(l_display_count) := l_calculated_balance;
2151 l_display_count := l_display_count + 1;
2152 end if;
2153
2154 l_calculated_balance := report_balance_items
2155 (p_balance_name => 'Gross Pay',
2156 p_dimension => '_ASG_PROC_PTD',
2157 p_assignment_action_id => p_assignment_action_id);
2158 if l_calculated_balance <> 0
2159 then
2160 g_displayed_balance(l_display_count) := 'Gross PTD';
2161 g_displayed_value(l_display_count) := l_calculated_balance;
2162 l_display_count := l_display_count + 1;
2163 end if;
2164
2165 l_calculated_balance := report_balance_items
2166 (p_balance_name => 'Taxable Pay',
2167 p_dimension => '_ASG_TD_YTD',
2168 p_assignment_action_id => p_assignment_action_id);
2169 if l_calculated_balance <> 0
2170 then
2171 g_displayed_balance(l_display_count) := 'Taxable YTD';
2172 g_displayed_value(l_display_count) := l_calculated_balance;
2173 l_display_count := l_display_count + 1;
2174 end if;
2175
2176 l_calculated_balance := report_balance_items
2177 (p_balance_name => 'Taxable Pay',
2178 p_dimension => '_ASG_TRANSFER_PTD',
2179 p_assignment_action_id => p_assignment_action_id);
2180 if l_calculated_balance <> 0
2181 then
2182 g_displayed_balance(l_display_count) := 'Taxable PTD';
2183 g_displayed_value(l_display_count) := l_calculated_balance;
2184 l_display_count := l_display_count + 1;
2185 end if;
2186
2187 l_calculated_balance := report_balance_items
2188 (p_balance_name => 'PAYE',
2189 p_dimension => '_ASG_TD_YTD',
2190 p_assignment_action_id => p_assignment_action_id);
2191 if l_calculated_balance <> 0
2192 then
2193 g_displayed_balance(l_display_count) := 'PAYE YTD';
2194 g_displayed_value(l_display_count) := l_calculated_balance;
2195 l_display_count := l_display_count + 1;
2196 end if;
2197
2198 l_calculated_balance := report_all_ni_balance
2199 (p_balance_name => 'NI Able',
2200 p_dimension => '_ASG_TD_YTD',
2201 p_assignment_action_id => p_assignment_action_id);
2202 if l_calculated_balance <> 0
2203 then
2204 g_displayed_balance(l_display_count) := 'NIable YTD';
2205 g_displayed_value(l_display_count) := l_calculated_balance;
2206 l_display_count := l_display_count + 1;
2207 end if;
2208
2209 l_calculated_balance := report_all_ni_balance
2210 (p_balance_name => 'NI Able',
2211 p_dimension => '_ASG_TRANSFER_PTD',
2212 p_assignment_action_id => p_assignment_action_id);
2213 if l_calculated_balance <> 0
2214 then
2215 g_displayed_balance(l_display_count) := 'NIable PTD';
2216 g_displayed_value(l_display_count) := l_calculated_balance;
2217 l_display_count := l_display_count + 1;
2218 end if;
2219
2220 l_calculated_balance := report_all_ni_balance
2221 (p_balance_name => 'NI Employee',
2222 p_dimension => '_ASG_TD_YTD',
2223 p_assignment_action_id => p_assignment_action_id);
2224 if l_calculated_balance <> 0
2225 then
2226 g_displayed_balance(l_display_count) := 'NI Ees YTD';
2227 g_displayed_value(l_display_count) := l_calculated_balance;
2228 l_display_count := l_display_count + 1;
2229 end if;
2230
2231 l_calculated_balance := report_employer_balance
2232 (p_assignment_action_id => p_assignment_action_id);
2233 if l_calculated_balance <> 0
2234 then
2235 g_displayed_balance(l_display_count) := 'NI Ers YTD';
2236 g_displayed_value(l_display_count) := l_calculated_balance;
2237 l_display_count := l_display_count + 1;
2238 end if;
2239
2240 l_calculated_balance := report_balance_items
2241 (p_balance_name => 'Superannuation Total',
2242 p_dimension => '_ASG_TD_YTD',
2243 p_assignment_action_id => p_assignment_action_id);
2244 if l_calculated_balance <> 0
2245 then
2246 g_displayed_balance(l_display_count) := 'Superan YTD';
2247 g_displayed_value(l_display_count) := l_calculated_balance;
2248 l_display_count := l_display_count + 1;
2249 end if;
2250
2251 end if;
2252 --
2253 -- the pl/sql table must hold 12 rows, so add dummy rows if less then 12 rows have
2254 -- been created
2255 --
2256 while l_display_count < 13 loop
2257 l_max_seq_no := l_max_seq_no + 10;
2258 g_displayed_balance(l_max_seq_no) := null;
2259 g_displayed_value(l_max_seq_no) := null;
2260 l_display_count := l_display_count + 1;
2261 end loop;
2262 --
2263 -- populate the output paramters from the pl/sql table
2264 --
2265 l_bal_ix := g_displayed_balance.first;
2266 p_label_1 := g_displayed_balance(l_bal_ix);
2267 p_value_1 := g_displayed_value(l_bal_ix);
2268
2269 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2270 p_label_2 := g_displayed_balance(l_bal_ix);
2271 p_value_2 := g_displayed_value(l_bal_ix);
2272
2273 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2274 p_label_3 := g_displayed_balance(l_bal_ix);
2275 p_value_3 := g_displayed_value(l_bal_ix);
2276
2277 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2278 p_label_4 := g_displayed_balance(l_bal_ix);
2279 p_value_4 := g_displayed_value(l_bal_ix);
2280
2281 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2282 p_label_5 := g_displayed_balance(l_bal_ix);
2283 p_value_5 := g_displayed_value(l_bal_ix);
2284
2285 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2286 p_label_6 := g_displayed_balance(l_bal_ix);
2287 p_value_6 := g_displayed_value(l_bal_ix);
2288
2289 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2290 p_label_7 := g_displayed_balance(l_bal_ix);
2291 p_value_7 := g_displayed_value(l_bal_ix);
2292
2293 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2294 p_label_8 := g_displayed_balance(l_bal_ix);
2295 p_value_8 := g_displayed_value(l_bal_ix);
2296
2297 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2298 p_label_9 := g_displayed_balance(l_bal_ix);
2299 p_value_9 := g_displayed_value(l_bal_ix);
2300
2301 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2302 p_label_a := g_displayed_balance(l_bal_ix);
2303 p_value_a := g_displayed_value(l_bal_ix);
2304
2305 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2306 p_label_b := g_displayed_balance(l_bal_ix);
2307 p_value_b := g_displayed_value(l_bal_ix);
2308
2309 l_bal_ix := g_displayed_balance.next(l_bal_ix);
2310 p_label_c := g_displayed_balance(l_bal_ix);
2311 p_value_c := g_displayed_value(l_bal_ix);
2312 --
2313 --
2314 END;
2315 --
2316 -------------------------------------------------------------------------------
2317 --
2318 procedure formula_inputs_wf (p_session_date in date,
2319 p_payroll_exists in out nocopy varchar2,
2320 p_assignment_action_id in out nocopy number,
2321 p_run_assignment_action_id in out nocopy number,
2322 p_assignment_id in number,
2323 p_payroll_action_id in out nocopy number,
2324 p_date_earned in out nocopy varchar2) is
2325 -- select the latest prepayments action for this individual and get the
2326 -- details of the last run that that action locked
2327 cursor csr_formula is
2328 select /*+ ORDERED USE_NL(paa,ppa,rpaa,rppa) */
2329 to_char(nvl(rppa.date_earned,rppa.effective_date),'YYYY/MM/DD'),
2330 rpaa.payroll_action_id,
2331 rpaa.assignment_action_id,
2332 paa.assignment_action_id
2333 from pay_assignment_actions paa,
2334 pay_payroll_actions ppa,
2335 pay_assignment_actions rpaa,
2336 pay_payroll_actions rppa
2337 where paa.payroll_action_id = ppa.payroll_action_id
2338 and rppa.payroll_action_id = rpaa.payroll_action_id
2339 and paa.assignment_id = rpaa.assignment_id
2340 and paa.assignment_action_id =
2341 (select
2342 to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
2343 from pay_payroll_actions pa,
2344 pay_assignment_actions aa
2345 where pa.action_type in ('U','P')
2346 and aa.action_status = 'C'
2347 and pa.payroll_action_id = aa.payroll_action_id
2348 and aa.assignment_id = p_assignment_id
2349 and pa.effective_date <= p_session_date)
2350 and ppa.action_type in ('P', 'U')
2351 and rpaa.assignment_id = p_assignment_id
2352 and rpaa.action_sequence =
2353 (select max(aa.action_sequence)
2354 from pay_assignment_actions aa,
2355 pay_action_interlocks loc
2356 where loc.locked_action_id = aa.assignment_action_id
2357 and loc.locking_action_id = paa.assignment_action_id);
2358
2359 -- Copied from HR_GBBAL.get_latest_action_id, include action type P and U
2360 cursor csr_formula_2 is
2361 SELECT /*+ USE_NL(paa, ppa) */
2362 --fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) /*Bug 4775025*/
2363 to_number(substr(max(to_char(ppa.effective_date,'J')||lpad(paa.assignment_action_id,15,'0')),8))
2364 FROM pay_assignment_actions paa,
2365 pay_payroll_actions ppa
2366 WHERE
2367 paa.assignment_id = p_assignment_id
2368 AND ppa.payroll_action_id = paa.payroll_action_id
2369 /* Commented below code, removed action_types 'B' and 'I' for bug fix 4775025*/
2370 /* AND (paa.source_action_id is not null
2371 or ppa.action_type in ('U','P'))*/
2372 AND ppa.effective_date <= p_session_date
2373 AND ppa.action_type in ('R', 'Q', 'U', 'P')
2374 AND paa.action_status = 'C';
2375
2376 cursor csr_formula_3(p_assig_act_id NUMBER) is
2377 select to_char(nvl(ppa.date_earned,ppa.effective_date),'YYYY/MM/DD'),
2378 paa.payroll_action_id
2379 from pay_payroll_actions ppa,
2380 pay_assignment_actions paa
2381 where paa.assignment_action_id = p_assig_act_id
2382 and ppa.payroll_action_id = paa.payroll_action_id;
2383
2384 cursor csr_formula_4(p_assig_act_id NUMBER) is
2385 select pact.action_type
2386 from pay_assignment_actions assact,
2387 pay_payroll_actions pact
2388 where assact.assignment_action_id = p_assignment_action_id
2389 and pact.payroll_action_id = assact.payroll_action_id;
2390
2391 cursor csr_formula_5(p_assig_act_id NUMBER) is
2392 select assact.assignment_action_id
2393 from pay_assignment_actions assact,
2394 pay_action_interlocks loc
2395 where loc.locking_action_id = p_assignment_action_id
2396 and assact.assignment_action_id = loc.locked_action_id
2397 order by assact.action_sequence desc;
2398
2399 --
2400 l_assignment_action_id NUMBER;
2401 l_action_type varchar2(1);
2402 --
2403 begin
2404 --
2405 l_assignment_action_id := null;
2406 --
2407 -- open csr_formula;
2408 -- fetch csr_formula into p_date_earned,
2409 -- p_payroll_action_id,
2410 -- p_run_assignment_action_id,
2411 -- p_assignment_action_id;
2412 open csr_formula_2;
2413 fetch csr_formula_2 into p_assignment_action_id;
2414 close csr_formula_2;
2415
2416 if p_assignment_action_id is NOT NULL then
2417 p_payroll_exists := 'TRUE';
2418
2419 open csr_formula_4(p_assignment_action_id);
2420 fetch csr_formula_4 into l_action_type;
2421 close csr_formula_4;
2422
2423 if l_action_type in ('P','U') then
2424 open csr_formula_5(p_assignment_action_id);
2425 fetch csr_formula_5 into p_run_assignment_action_id;
2426 close csr_formula_5;
2427 -- Bug 4584572
2428 else
2429 p_run_assignment_action_id := p_assignment_action_id;
2430 end if;
2431
2432 open csr_formula_3(p_run_assignment_action_id);
2433 fetch csr_formula_3 into p_date_earned,
2434 p_payroll_action_id;
2435 close csr_formula_3;
2436
2437 end if;
2438 -- if csr_formula_2%FOUND then
2439 -- p_payroll_exists := 'TRUE';
2440 -- end if;
2441 -- close csr_formula;
2442 --
2443 end formula_inputs_wf;
2444 --
2445 procedure formula_inputs_hc (p_assignment_action_id in out nocopy number,
2446 p_run_assignment_action_id in out nocopy number,
2447 p_assignment_id in out nocopy number,
2448 p_payroll_action_id in out nocopy number,
2449 p_date_earned in out nocopy varchar2) is
2450 -- if the action is a run then return the run details
2451 -- if the action is a prepayment return the latest run details locked
2452 cursor csr_formula is
2453 -- find what type of action this is
2454 select pact.action_type , assact.assignment_id
2455 from pay_assignment_actions assact,
2456 pay_payroll_actions pact
2457 where assact.assignment_action_id = p_assignment_action_id
2458 and pact.payroll_action_id = assact.payroll_action_id
2459 ;
2460 cursor csr_formula_2 is
2461 -- for prepayment action find the latest interlocked run
2462 select assact.assignment_action_id
2463 from pay_assignment_actions assact,
2464 pay_action_interlocks loc
2465 where loc.locking_action_id = p_assignment_action_id
2466 and assact.assignment_action_id = loc.locked_action_id
2467 order by assact.action_sequence desc
2468 ;
2469 cursor csr_formula_3 is
2470 -- for run action check if its been prepaid
2471 select assact.assignment_action_id
2472 from pay_assignment_actions assact,
2473 pay_payroll_actions pact,
2474 pay_action_interlocks loc
2475 where loc.locked_action_id = p_assignment_action_id
2476 and assact.assignment_action_id = loc.locking_action_id
2477 and pact.payroll_action_id = assact.payroll_action_id
2478 and pact.action_type in ('P','U') /* prepayments only */
2479 order by assact.action_sequence desc
2480 ;
2481 cursor csr_formula_4 is
2482 -- now find the date earned and payroll action of the run action
2483 select pact.payroll_action_id,
2484 to_char(nvl(pact.date_earned,pact.effective_date),'YYYY/MM/DD')
2485 from pay_assignment_actions assact,
2486 pay_payroll_actions pact
2487 where assact.assignment_action_id = p_run_assignment_action_id
2488 and pact.payroll_action_id = assact.payroll_action_id
2489 ;
2490 --
2491 l_action_type varchar2(1);
2492 --
2493 begin
2494 --
2495 open csr_formula;
2496 fetch csr_formula into l_action_type, p_assignment_id;
2497 close csr_formula;
2498 --
2499 if l_action_type in ('P', 'U') then
2500 open csr_formula_2;
2501 fetch csr_formula_2 into p_run_assignment_action_id;
2502 close csr_formula_2;
2503
2504 -- Bug 4584572
2505 -- if its a run action it may or may not have been prepaid
2506
2507 -- Comment out this bit of code because it will always return the prepayment's action_id
2508 -- regardless the type of run being selected.
2509
2510 -- else
2511 -- p_run_assignment_action_id := p_assignment_action_id;
2512 --
2513 -- begin
2514 -- open csr_formula_3;
2515 -- fetch csr_formula_3 into p_assignment_action_id;
2516 -- close csr_formula_3;
2517 -- exception when NO_DATA_FOUND then
2518 -- p_assignment_action_id := p_run_assignment_action_id;
2519 -- end;
2520 else
2521 p_run_assignment_action_id := p_assignment_action_id;
2522 end if;
2523 -- fetch payroll details
2524 open csr_formula_4;
2525 fetch csr_formula_4 into p_payroll_action_id,
2526 p_date_earned;
2527 close csr_formula_4;
2528 --
2529
2530 -- following code superceeded by code above to try and address performance
2531 -- problem 303467 - some change in functionality so retain old logic as
2532 -- reference AS 24-AUG-95
2533 -- open csr_formula;
2534 ---- fetch csr_formula into p_date_earned,
2535 -- p_payroll_action_id,
2536 -- p_assignment_id,
2537 -- p_run_assignment_action_id,
2538 -- p_assignment_action_id;
2539 --close csr_formula;
2540 -- to_char(nvl(rppa.date_earned,rppa.effective_date),'YYYY/MM/DD'),
2541 -- rpaa.payroll_action_id,
2542 -- rpaa.assignment_id,
2543 -- rpaa.assignment_action_id,
2544 -- paa.assignment_action_id
2545 -- from pay_assignment_actions paa,
2546 -- pay_payroll_actions ppa,
2547 -- pay_assignment_actions rpaa,
2548 -- pay_payroll_actions rppa
2549 -- where paa.payroll_action_id = ppa.payroll_action_id
2550 -- and rppa.payroll_action_id = rpaa.payroll_action_id
2551 -- and paa.assignment_action_id = p_assignment_action_id
2552 -- and ( ppa.action_type in ('R', 'Q')
2553 -- and paa.action_status = 'C'
2554 -- and rpaa.assignment_action_id = p_assignment_action_id
2555 -- or ( ppa.action_type in ('P', 'U')
2556 -- and rpaa.action_sequence =
2557 -- (select max(aa.action_sequence)
2558 -- from pay_assignment_actions aa,
2559 -- pay_action_interlocks loc
2560 -- where loc.locked_action_id = aa.assignment_action_id
2561 -- and loc.locking_action_id = p_assignment_action_id)))
2562 --;
2563 --
2564 end formula_inputs_hc;
2565 --
2566 procedure get_home_add(p_person_id IN NUMBER,
2567 p_add1 IN out nocopy VARCHAR2,
2568 p_add2 IN out nocopy VARCHAR2,
2569 p_add3 IN out nocopy VARCHAR2,
2570 p_reg1 IN out nocopy VARCHAR2,
2571 p_reg2 IN out nocopy VARCHAR2,
2572 p_reg3 IN out nocopy VARCHAR2,
2573 p_twnc IN out nocopy VARCHAR2) is
2574 --
2575 cursor homeadd is
2576 select pad.address_line1,
2577 pad.address_line2,
2578 pad.address_line3,
2579 l.meaning,
2580 pad.postal_code,
2581 pad.region_3,
2582 pad.town_or_city
2583 from per_addresses pad,
2584 hr_lookups l
2585 where pad.person_id = p_person_id
2586 and pad.primary_flag = 'Y'
2587 and l.lookup_type(+) = 'GB_COUNTY'
2588 and l.lookup_code(+) = pad.region_1
2589 and sysdate between nvl(pad.date_from, sysdate)
2590 and nvl(pad.date_to, sysdate);
2591 --
2592 begin
2593 --
2594 open homeadd;
2595 --
2596 fetch homeadd into p_add1,
2597 p_add2,
2598 p_add3,
2599 p_reg1,
2600 p_reg2,
2601 p_reg3,
2602 p_twnc;
2603 --
2604 close homeadd;
2605
2606 end get_home_add;
2607 --
2608 procedure get_work_add(p_location_id IN NUMBER,
2609 p_add1 IN out nocopy VARCHAR2,
2610 p_add2 IN out nocopy VARCHAR2,
2611 p_add3 IN out nocopy VARCHAR2,
2612 p_reg1 IN out nocopy VARCHAR2,
2613 p_reg2 IN out nocopy VARCHAR2,
2614 p_reg3 IN out nocopy VARCHAR2,
2615 p_twnc IN out nocopy VARCHAR2) is
2616 --
2617 cursor workadd is
2618 select
2619 hrl.address_line_1,
2620 hrl.address_line_2,
2621 hrl.address_line_3,
2622 l.meaning,
2623 hrl.region_2,
2624 hrl.region_3,
2625 hrl.town_or_city
2626 from hr_locations hrl,
2627 hr_lookups l
2628 where hrl.location_id = p_location_id
2629 and l.lookup_type(+) = 'GB_COUNTY'
2630 and l.lookup_code(+) = hrl.region_1;
2631 --
2632 begin
2633 --
2634 open workadd;
2635 --
2636 fetch workadd into p_add1,
2637 p_add2,
2638 p_add3,
2639 p_reg1,
2640 p_reg2,
2641 p_reg3,
2642 p_twnc;
2643 --
2644 close workadd;
2645 --
2646 end get_work_add;
2647 --
2648 -----------------------------------------------------------------------
2649 --
2650 procedure add_new_soe_balance (p_business_group_id in number,
2651 p_balance_name in varchar2,
2652 p_dimension_name in varchar2) is
2653
2654 --
2655 -- when a new balance/dimension is added to the system this procedure
2656 -- should be called to add the new balance/dimension to the SOE Balance
2657 -- table
2658 --
2659
2660 cursor c_find_table_id is
2661 select put.user_table_id
2662 from pay_user_tables put
2663 where put.user_table_name = g_user_table_name
2664 and put.business_group_id is NULL
2665 and put.legislation_code = 'GB';
2666
2667 cursor csr_dimension_suffix is
2668 select decode(legislation_code,
2669 'GB',replace(database_item_suffix,database_item_suffix,
2670 ' ' || substr(database_item_suffix,2)),
2671 database_item_suffix || ' USER-REG')
2672 from pay_balance_dimensions pbd
2673 where dimension_name = p_dimension_name;
2674
2675 l_user_table_id number;
2676 l_user_row_id number;
2677 l_legislation_code varchar2(2);
2678 l_dimension_suffix varchar2(40);
2679
2680 begin
2681
2682 open c_find_table_id;
2683 fetch c_find_table_id into l_user_table_id;
2684 close c_find_table_id;
2685
2686 select pay_user_rows_s.nextval into l_user_row_id
2687 from dual;
2688
2689 if p_business_group_id is null
2690 then
2691 l_legislation_code := 'GB';
2692 else
2693 l_legislation_code := null;
2694 end if;
2695
2696 open csr_dimension_suffix;
2697 fetch csr_dimension_suffix into l_dimension_suffix;
2698 close csr_dimension_suffix;
2699
2700 -- the column row_low_range or name is varchar2(80), so the
2701 -- total length of the balance name + dimension name cannot
2702 -- be more then 80 characters
2703
2704 if length(p_balance_name || l_dimension_suffix) > 80
2705 then
2706 -- message('SOE Balances has not been updated');
2707 null;
2708 else
2709 insert into PAY_USER_ROWS_F
2710 (USER_ROW_ID,
2711 EFFECTIVE_START_DATE,
2712 EFFECTIVE_END_DATE,
2713 BUSINESS_GROUP_ID,
2714 LEGISLATION_CODE,
2715 USER_TABLE_ID,
2716 ROW_LOW_RANGE_OR_NAME,
2717 DISPLAY_SEQUENCE,
2718 LEGISLATION_SUBGROUP,
2719 ROW_HIGH_RANGE)
2720 values
2721 (l_user_row_id,
2722 to_date('1900/01/01','YYYY/MM/DD'),
2723 to_date('4712/12/31','YYYY/MM/DD'),
2724 p_business_group_id,
2725 l_legislation_code,
2726 l_user_table_id,
2727 p_balance_name || l_dimension_suffix,
2728 NULL,
2729 NULL,
2730 NULL);
2731 end if;
2732
2733 end add_new_soe_balance;
2734 --
2735 -------------------------------------------------------------------------------
2736 --
2737 procedure add_new_soe_balance(p_balance_name in varchar2,
2738 p_dimension_name in varchar2) is
2739
2740 cursor c_find_table_id is
2741 select put.user_table_id
2742 from pay_user_tables put
2743 where put.user_table_name = g_user_table_name
2744 and put.business_group_id is NULL
2745 and put.legislation_code = 'GB';
2746
2747 cursor c_check_row_exists(l_user_table_id number) is
2748 select 1
2749 from pay_user_rows_f pur,
2750 pay_balance_dimensions pbd
2751 where pur.row_low_range_or_name =
2752 p_balance_name ||
2753 replace(pbd.database_item_suffix,pbd.database_item_suffix,
2754 ' '|| substr(pbd.database_item_suffix,2))
2755 and pbd.dimension_name = p_dimension_name
2756 and pur.user_table_id = l_user_table_id;
2757
2758 l_user_table_id number;
2759 l_check_row number;
2760
2761 begin
2762
2763 open c_find_table_id;
2764 fetch c_find_table_id into l_user_table_id;
2765 close c_find_table_id;
2766
2767 open c_check_row_exists(l_user_table_id);
2768 fetch c_check_row_exists into l_check_row;
2769
2770 if c_check_row_exists%NOTFOUND
2771 then
2772
2773 add_new_soe_balance(NULL,
2774 p_balance_name,
2775 p_dimension_name);
2776
2777 end if;
2778
2779 close c_check_row_exists;
2780
2781 end add_new_soe_balance;
2782 --
2783 ---------------------------------------------------------------------------------
2784 --
2785 function GET_SALARY (
2786 --
2787 p_pay_basis_id number,
2788 p_assignment_id number,
2789 p_effective_date date) return varchar2 is
2790 --
2791 -- clone of hr_general.get_salary but fetcH At a given date
2792 -- This cursor gets the screen_entry_value from pay_element_entry_values_f.
2793 -- This is the salary amount
2794 -- obtained when the pay basis isn't null. The pay basis and assignment_id
2795 -- are passed in by the view. A check is made on the effective date of
2796 -- pay_element_entry_values_f and pay_element_entries_f as they're datetracked.
2797 --
2798 cursor csr_lookup is
2799 select sum(eev.screen_entry_value)
2800 from pay_element_entry_values_f eev,
2801 per_pay_bases ppb,
2802 pay_element_entries_f pe
2803 where ppb.pay_basis_id +0 = p_pay_basis_id
2804 and pe.assignment_id = p_assignment_id
2805 and eev.input_value_id = ppb.input_value_id
2806 and eev.element_entry_id = pe.element_entry_id
2807
2808 and eev.input_value_id = ppb.input_value_id
2809 and eev.element_entry_id = pe.element_entry_id
2810 and p_effECtive_date between
2811 eev.effective_start_date and eev.effective_end_date
2812 and p_EFfective_date between
2813 pe.effective_start_date and pe.effective_end_date;
2814 --
2815 v_meaning varchar2(60) := null;
2816 begin
2817 --
2818 -- Only open the cursor if the parameter may retrieve anything
2819 -- In practice, p_assignment_id is always going to be non null;
2820 -- p_pay_basis_id may be null, though. If it is, don't bother trying
2821 -- to fetch a salary.
2822 --
2823 -- If we do have a pay basis, try and get a salary. There may not be one,
2824 -- in which case no problem: just return null.
2825 --
2826 if p_pay_basis_id is not null and p_assignment_id is not null then
2827 open csr_lookup;
2828 fetch csr_lookup into v_meaning;
2829 close csr_lookup;
2830
2831 end if;
2832 --
2833 -- Return the salary value, if this does not exist, return a null value.
2834 --
2835 return v_meaning;
2836 end get_salary;
2837
2838 -----------------------------------------------------------------------
2839
2840
2841 END PAY_GB_PAYROLL_ACTIONS_PKG;