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