1 PACKAGE BODY pay_ip_employee_balances AS
2 /* $Header: payipempbalance.pkb 120.6.12020000.1 2012/08/29 05:51:05 sjawid noship $ */
3
4 /*******************************************************************************************
5 ******************************************************************
6 * *
7 * Copyright (C) 1993 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_ip_employee_balances
22
23 Description : The package is used by the Employee Balances SS Page
24 to fetch the earnings and deduction balances.
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- ---------- ------ --------- -----------------------------------
30 31-Jan-2012 vvijayku 120.0 Created.
31 09-Mar-2012 vvijayku 120.1 13772336 Corrected the insertion of ARR and
32 ACC tags to the pay_us_rpt_totals
33 table.
34 15-Mar-2012 vvijayku 120.2 13827477 Added new conditions to prevent the
35 fetching and display of the balance
36 value if either none of the dimensions
37 are selected in the SS page or if
38 there are no elements fetched for the
39 selected Element Classification.
40 also added a new Function for the
41 validation of the Assignment.
42 29-Mar-2012 vvijayku 120.3 13902938 Did the following changes in the
43 package:
44 1) Implemented the Balance Initialization
45 code to retrieve the balance
46 initialization balance values.
47 2) Consolidated the fetching of
48 balance values code into the
49 function fetch_and_store_balances
50 3) Consolidated the Insertion of
51 balance values to pay_us_rpt_totals
52 code into the function
53 populate_tab_rpt_totals
54 4) Renamed the variables used in the
55 package to follow a standard
56 pattern.
57 5) Modified the Assignment
58 validation code in the function
59 validate_assignment.
60 6) Made use of pay_ip_route_support to correctly
61 fetch the tax_year and tax_quarter.
62 03-Apr-2012 vvijayku 120.4 13902938 Changed the code that fetches the l_pay_start_date
63 to make use of the beginning of Tax Year instead
64 of the beginning of the Calendar Year.
65 06-Apr-2012 vvijayku 120.5 13902938 Removed all the occurrences of per_all_assignments_f
66 and replaced it with per_assignments_f. Replaced the
67 use of trunc(p_session_date,'YYYY') to
68 the use of Tax Year using pay_ip_route_support.tax_year
69 function.
70 ********************************************************************************************/
71
72 /*******************************************************************************************
73 Description
74 -----------
75 PL/SQL Tables used :
76 ------------------
77 1) t_eb_bal_context_tab - This is the pl/sql table that holds the context data that is required
78 to fetch the balance values using the pay_balance_pkg package.
79
80 2) t_eb_bal_result_tab - This is the pl/sql table which is passed to the pay_balance_pkg
81 package which will hold the retrieved balance values along with the
82 respective defined balance ids.
83
84 3) t_eb_balance_value_tab - This is the pl/sql table that holds the defined balance ids for
85 which the balance values are to be bulk fetched using the
86 pay_balance_pkg package.
87
88 4) t_earnings_tab - This is the pl/sql table that holds the details of the earning type elements
89 along with the primary balance type id for which the balance values are to
90 be fetched.
91
92 5) t_deduction_tab - This is the pl/sql table that holds the details of the deduction type
93 elements along with the primary balance type id, arrears balance type id
94 and accruals balance type id for which the balance values are to be
95 fetched.
96
97 6) t_defbal_details_tab - This is the pl/sql table that holds the details of the defined
98 balance ids for the each of the Primary Balance ids, Arrears balance
99 type ids, Accruals balance type ids and Assignment level and Person
100 level PTD, MTD, QTD, YTD dimensions along with the element name,
101 element type id, balance type id, balance value, balance dimension id
102 and database item suffix of the dimension. This information is held
103 in a verticle format i.e. each record of the pl/sql table holds
104 the information for a distinct defined balance id.
105
106 7) t_ele_bal_horizontal_tab - This is the pl/sql table that holds the data present in the
107 pl/sql table t_defbal_details_tab but in the horizontal format
108 i.e. the information is categorized into Primary Balance data,
109 Arrears balance data and Accruals Balance data. Each of these
110 types of data has a single record in this table. The Balance
111 values for the defined balances of the Primary Balance is stored
112 in the corresponding row for the Primary Balance similarly the
113 balance values for Arrears and Accruals balances are stored in
114 their respective rows.
115
116 Procedures:
117 -----------
118 1) purge_old_data - This procedure is called from the Submit button on the SS Page.
119 This will clear the old element balance data that is present in the
120 table pay_us_rpt_totals. This step is necessary before the new set of balances
121 are fetched and inserted in the table pay_us_rpt_totals.In short, for every query
122 old data is removed and set of new data as per the current selection is populated in the
123 table pay_us_rpt_totals.
124
125 2) populate_element_info - This is the main procedure which is used to fetch and insert the
126 element balance values in the table pay_us_rpt_totals.
127
128 Following is the code flow in the procedure:
129
130 1) The legislation code is fetched using the cursor c_leg_code.
131 2) The element classification id is fetched using the cursor
132 c_get_class_name. The cursor uses the element classification
133 name that is passed as a parameter to the procedure and the
134 legislation code fetched in step 1.
135 3) Next the cursor c_action_type_tu is used to identify what is
136 the latest payroll process action type for the current assignment.
137 4) If the Action type returned in step 3 is 'Q' or 'R'
138 then if
139 the assignment_action_id passed to the procedure IS NOT -1 (page is opened
140 in assignment_action mode)
141 then.if
142 the element classification is of the type DEDUCTIONS
143 then
144 using the cursor c_element_info_asact_dedn the relevant element
145 information is fetched and stored in the pl/sql table t_deduction_tab
146
147 else if the element classification is of they type EARNINGS
148 then
149 using the cursor c_element_info_asact_earn the relevant element
150 information is fetched and stored in the pl/sql table t_earnings_tab.
151 5) If the Action type returned in step 3 is 'Q' or 'R',
152 then if
153 the assignment_action_id passed to the procedure IS -1 (page is opened
154 in Date mode)
155 then if
156 the element classification is of the type DEDUCTIONS
157 then
158 using the cursor c_element_info_date_dedn the relevant element
159 information is fetched and stored in the pl/sql table t_deduction_tab
160
161 else if the element classification is of they type EARNINGS
162 then
163 using the cursor c_element_info_date_earn the relevant element
164 information is fetched and stored in the pl/sql table t_earnings_tab.
165 6) If the Action type returned in step 3 is 'I'
166 then if
167 the element classification is of the type DEDUCTIONS
168 then
169 using the cursor c_element_info_bi_dedn the relevant element
170 information is fetched and stored in the pl/sql table t_deduction_tab
171
172 else if the element classification is of they type EARNINGS
173 then
174 using the cursor c_element_info_bi_earn the relevant element
175 information is fetched and stored in the pl/sql table t_earnings_tab.
176 7) Once the relevant element information is stored in
177 t_earnings_tab or t_deduction_tab, based on the type of element
178 classification, the function fetch_and_store_balances is called to
179 fetch and store the element balance values in the pl/sql table
180 t_ele_bal_horizontal_tab.
181 7.1) The function fetch_and_store_balances, uses the Primary Balance
182 type id information stored in t_earnings_tab/t_deduction_tab based
183 on whether the element classification is of the type EARNINGS or
184 DEDUCTIONS respectively and fetches and stores the Defined Balance
185 Id information in the pl/sql table t_defbal_details_tab. To fetch
186 this information cursor c_get_asg_def_bal_id is called when
187 ASG level balance values are required and cursor c_get_per_def_bal_id
188 is called when PER level balances are required.
189 7.2) If the type of element classification is DEDUCTIONS and the
190 balance level to be fetched is ASG then the
191 cursors c_get_asg_arr_defbal and c_get_asg_acc_defbal are called
192 to fetch and store the Arrears and Accruals Defined balance ids
193 respectively in the pl/sql table t_defbal_details_tab.
194 7.3))create a horizontal data structure to store all the Balance Values
195 for Primary Balance, Arrears Balance and Acrruals Balance for
196 the given element in individual rows in a pl/sql table i.e. the
197 balance values for all the defined balances of the primary balance
198 should be stored in a single row and similarly the balance value
199 of arrears defined balance id should be stored in a single row
200 and balance value of accruals balance should be stored in a single
201 row. This table structure is created using the pl/sql table
202 t_ele_bal_horizontal_tab. Maximum possible rows in the table is 3.
203 7.4)if the p_assignment_action_id IS NOT -1 (assignment action mode) then
204 the relevant defined balance ids from t_defbal_details_tab is
205 stored in t_eb_balance_value_tab which is later passed to the
206 bulk fetch the balance values for the given p_assignment_action_id
207 The balance values are stored in the pl/sql table t_eb_bal_result_tab
208 which is later transferred to the table t_defbal_details_tab.
209 7.5)In step 7.4 if the p_assignment_action_id IS -1 (Date mode) then the
210 relevant defined balance ids are passed as parameter to the
211 function get_bal and the returned balance value is directly
212 stored in t_defbal_details_tab.
213 7.6)The balance dimension details and the balance value stored in
214 t_defbal_details_tab are transferred to t_ele_bal_horizontal_tab.
215 8) The function populate_tab_rpt_totals is executed which inserts the
216 data present in t_ele_bal_horizontal_tab to the table pay_us_rpt_totals.
217 This data in pay_us_rpt_totals is later queried by the SS Page and
218 displayed in the Results Region.
219 ********************************************************************************************/
220
221 l_package VARCHAR2(30) := 'pay_ip_employee_balances.';
222 t_eb_bal_context_tab pay_balance_pkg.t_context_tab;
223 t_eb_bal_result_tab pay_balance_pkg.t_detailed_bal_out_tab;
224 t_eb_balance_value_tab pay_balance_pkg.t_balance_value_tab;
225
226 t_earnings_tab earn_tbl;
227 t_deduction_tab dedn_tbl;
228
229
230 /******************************************************************************
231 Name : purge_old_data
232 Purpose : This procedure will purge the old balance data from the table
233 pay_us_rpt_totals
234 ******************************************************************************/
235 PROCEDURE purge_old_data (p_purge varchar2)
236 IS
237 PRAGMA AUTONOMOUS_TRANSACTION;
238 l_procedure varchar2(22);
239 BEGIN
240 l_procedure :='populate_element_info';
241
242 IF p_purge = 'Y' THEN
243 hr_utility.set_location(l_package||l_procedure, 10);
244
245 DELETE PAY_US_RPT_TOTALS
246 WHERE GRE_NAME = 'IP_VIEW_BALANCE'
247 AND ORGANIZATION_NAME IN ('ASG','PER');
248 COMMIT;
249
250 hr_utility.set_location(l_package||l_procedure, 20);
251 END IF;
252 END;
253
254
255 /******************************************************************************
256 * Name : validate_assignment
257 * Purpose : This function is used to check whether the assignment is
258 * valid as on the effective date passed.
259 ******************************************************************************/
260 FUNCTION validate_assignment(p_assignment_id number, p_effective_date varchar2, p_business_group_id number)
261 RETURN varchar2 IS
262 l_valid_asg varchar2(1) := 'Y';
263 l_function varchar2(22);
264 BEGIN
265 l_function :='validate_assignment';
266 hr_utility.set_location(l_package||l_function, 10);
267
268 SELECT 'Y'
269 INTO l_valid_asg
270 FROM per_assignments_f paf
271 WHERE paf.assignment_id = p_assignment_id
272 AND paf.effective_start_date <= to_date(p_effective_date,'YYYY-MM-DD')
273 AND paf.effective_end_date >= pay_ip_route_support.tax_year(p_business_group_id,to_date(p_effective_date,'YYYY-MM-DD'))
274 AND rownum=1;
275
276 RETURN l_valid_asg;
277
278 hr_utility.set_location(l_package||l_function, 20);
279 EXCEPTION WHEN NO_DATA_FOUND THEN
280 hr_utility.set_location(l_package||l_function, 30);
281 RETURN 'N';
282 END;
283
284 /******************************************************************************
285 Name : populate_element_info
286 Purpose : This procedure fetches the elements for which the balances are to
287 be retrieved.For the selected elements balance values are fetched and
288 stored in a PL/SQL table which is then used to populate the table
289 pay_us_rpt_totals.
290 The View Employee Balance SS Page fetches data from this table and display it on the page
291 following are the parameters passed to the procedure.
292
293 1) p_assignment_id - The assignment id of the employee
294 2) p_assignment_action_id - Assignemnt action id of the Quick pay or
295 Payroll run. Values can be
296 a) -1 When page is invoked in Date Mode
297 b) A valid Assignment Action id.
298 3) p_classification_name - Element Classification name selected in
299 VEB SS Page.
300 4) p_session_date - Effective Date entered by the user in the SS Page
301 5) p_tax_unit_id - Tax Unit Id to which the assignment belongs as on
302 the p_session_date. Values can be
303 a) -1 If the loclisation doesnt support tax unit id
304 b) A valid Tax Unit Id (TU legislation)
305 6) p_per_month - Whether Person level MTD Balance value should be
306 fetched or not. Valid values can be 0 (No), b) 1 (Yes)
307 7) p_per_qtd - Whether Person level QTD Balance value should be
308 fetched or not. Valid values can be a) 0 (No), b) 1 (Yes)
309 8) p_per_ytd - Whether Person level YTD Balance value should be
310 fetched or not. Valid values can be a) 0 (No), b) 1 (Yes)
311 9) p_asg_ptd - Whether Asg level PTD Balance value should be fetched
312 or not. Valid values can be a) 0 (No), b) 1 (Yes)
313 10)p_asg_month - Whether Asg level MTD Balance value should be
314 fetched or not. Valid values can be a) 0 (No), b) 1 (Yes)
315 11)p_asg_qtd - Whether Asg level QTD Balance value should be fetched
316 or not. Valid values can be a) 0 (No), b) 1 (Yes)
317 12)p_asg_ytd - Whether Asg level YTD Balance value should be fetched
318 or not. Valid values can be a) 0 (No), b) 1 (Yes)
319 13)p_asg_arr_itd - Whether Asg level Arrears balance value should
320 be fetched or not. Valid values can be a) 0 (No),
321 b) 1 (Yes)
322 14)p_asg_acc_itd - Whether Asg level Accruals balance value should
323 be fetched or not. Valid values can be a) 0 (No),
324 b) 1 (Yes)
325 15)p_business_group_id - Business Group Id to which the emp belongs to
326 16)p_balance_level - The level of balance required. Valid values
327 can be
328 a) PER (Person level)
329 b) ASG (Assignment level)
330 ******************************************************************************/
331 PROCEDURE populate_element_info( p_assignment_id in number,
332 p_assignment_action_id in number,
333 p_classification_name in pay_element_classifications.classification_name%TYPE,
334 p_session_date in pay_element_types_f.effective_start_date%TYPE,
335 p_tax_unit_id in number,
336 p_per_month in number,
337 p_per_qtd in number,
338 p_per_ytd in number,
339 p_asg_ptd in number,
340 p_asg_month in number,
341 p_asg_qtd in number,
342 p_asg_ytd in number,
343 p_asg_arr_itd in number,
344 p_asg_acc_itd in number,
345 p_business_group_id in pay_element_types_f.business_group_id%TYPE,
346 p_balance_level in varchar2
347 )
348 IS PRAGMA AUTONOMOUS_TRANSACTION;
349
350 l_dim_qtd varchar2(20);
351 l_dim_ytd varchar2(20);
352 l_dim_mtd varchar2(20);
353 l_legislation_code varchar2(10);
354 l_classification_id number;
355 l_action_date date;
356 l_pay_start_date date;
357 i number;
358 j number;
359 k number;
360 l_flag varchar2(5);
361 l_insert_flag varchar2(5);
362 l_return_value number;
363 l_return_insert number;
364 l_type pay_payroll_actions.action_type%TYPE;
365 l_procedure VARCHAR2(22);
366
367 /******************************************************************************
368 * Cursor to get the legislation code based on the Business Group Id.
369 ******************************************************************************/
370
371 CURSOR c_leg_code IS
372 SELECT legislation_code
373 FROM per_business_groups
374 WHERE business_group_id = p_business_group_id;
375
376 /******************************************************************************
377 * Cursor to get the Element Classification Id based on the Element Classification
378 * name and the legislation code.
379 ******************************************************************************/
380
381 CURSOR c_get_class_name (p_leg_code varchar2) IS
382 SELECT c.classification_id
383 FROM pay_element_classifications c
384 WHERE UPPER(c.classification_name) = UPPER(p_classification_name)
385 AND c.legislation_code = p_leg_code;
386
387 /******************************************************************************
388 * Cursor to determine the Order of precedence of Quick Pay run (Q), Payroll
389 * run (R) and Balance Initialization (I) for a given assignment id. This
390 * cursor is based on Tax Unit Id.
391 ******************************************************************************/
392
393 CURSOR c_action_type_tu IS
394 SELECT pay.action_type
395 FROM pay_assignment_actions pac
396 ,pay_payroll_actions pay
397 WHERE pay.payroll_action_id = pac.payroll_action_id
398 AND pay.action_type IN ('Q','R','I')
399 AND pac.assignment_id = p_assignment_id
400 AND pac.action_status = 'C'
401 AND pac.tax_unit_id = p_tax_unit_id
402 AND EXISTS
403 (SELECT 'x'
404 FROM pay_run_results prr,
405 pay_assignment_actions pac1
406 WHERE prr.assignment_action_id = pac1.assignment_action_id
407 AND pac1.assignment_id = pac.assignment_id
408 )
409 ORDER BY decode(pay.action_type,'Q','1','R','1','I','2','3');
410
411 /******************************************************************************
412 * Cursor to determine the Order of precedence of Quick Pay run (Q), Payroll
413 * run (R) and Balance Initialization (I) for a given assignment id. This
414 * cursor is based on Business Group Id.
415 ******************************************************************************/
416
417 CURSOR c_action_type_bg IS
418 SELECT pay.action_type
419 FROM pay_assignment_actions pac
420 ,pay_payroll_actions pay
421 WHERE pay.payroll_action_id = pac.payroll_action_id
422 AND pay.business_group_id = p_business_group_id
423 AND pay.action_type IN ('Q','R','I')
424 AND pac.assignment_id = p_assignment_id
425 AND pac.action_status = 'C'
426 AND EXISTS
427 (SELECT 'x'
428 FROM pay_run_results prr
429 ,pay_assignment_actions pac1
430 WHERE prr.assignment_action_id = pac1.assignment_action_id
431 AND pac1.assignment_id = pac.assignment_id)
432 ORDER BY decode(pay.action_type,'Q','1','R','1','I','2','3');
433
434 /******************************************************************************
435 * Cursor to get the assignment level Defined Balance Ids for the given Primary
436 * Balance type Id.
437 ******************************************************************************/
438
439 CURSOR c_get_asg_def_bal_id (p_balance_type_id varchar2) IS
440 SELECT DISTINCT pet.element_name,
441 pet.element_type_id,
442 pdb_call.defined_balance_id,
443 pbt.balance_type_id,
444 pbd.balance_dimension_id,
445 pbd.database_item_suffix
446 FROM pay_defined_balances pdb_call,
447 pay_balance_dimensions pbd,
448 pay_balance_types pbt,
449 pay_element_types_f pet
450 WHERE pbt.balance_type_id = fnd_number.canonical_to_number(p_balance_type_id)
451 AND ((pbt.business_group_id IS NOT NULL AND pbt.business_group_id = p_business_group_id)
452 OR (pbt.legislation_code IS NOT NULL AND pbt.legislation_code = l_legislation_code))
453 AND pet.classification_id = l_classification_id
454 AND pet.element_information10 = fnd_number.number_to_canonical(pbt.balance_type_id)
455 AND pbt.balance_type_id = pdb_call.balance_type_id
456 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
457 AND ((p_tax_unit_id <> -1 AND pbd.database_item_suffix IN ('_ASG_TU_PTD','_ASG_TU_MONTH','_ASG_TU_TQTD','_ASG_TU_TYTD'))
458 OR (p_tax_unit_id = -1 AND pbd.database_item_suffix IN ('_ASG_PTD','_ASG_MONTH','_ASG_TQTD','_ASG_TYTD')))
459 AND pbd.legislation_code = l_legislation_code
460 ORDER BY pbd.database_item_suffix;
461
462 /******************************************************************************
463 * Cursor to get the person level Defined Balance Ids for the given Primary
464 * Balance type Id.
465 ******************************************************************************/
466
467 CURSOR c_get_per_def_bal_id (p_balance_type_id varchar2) IS
468 SELECT DISTINCT pet.element_name,
469 pet.element_type_id,
470 pdb_call.defined_balance_id,
471 pbt.balance_type_id,
472 pbd.balance_dimension_id,
473 pbd.database_item_suffix
474 FROM pay_defined_balances pdb_call,
475 pay_balance_dimensions pbd,
476 pay_balance_types pbt,
477 pay_element_types_f pet
478 WHERE pbt.balance_type_id = fnd_number.canonical_to_number(p_balance_type_id)
479 AND ((pbt.business_group_id IS NOT NULL AND pbt.business_group_id = p_business_group_id)
480 OR (pbt.legislation_code IS NOT NULL AND pbt.legislation_code = l_legislation_code))
481 AND pet.classification_id = l_classification_id
482 AND pet.element_information10 = fnd_number.number_to_canonical(pbt.balance_type_id)
483 AND pbt.balance_type_id = pdb_call.balance_type_id
484 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
485 AND ((p_tax_unit_id <> -1 AND pbd.database_item_suffix IN ('_PER_TU_MONTH','_PER_TU_TQTD','_PER_TU_TYTD'))
486 OR (p_tax_unit_id = -1 AND pbd.database_item_suffix IN ('_PER_MONTH','_PER_TQTD','_PER_TYTD')))
487 AND pbd.legislation_code = l_legislation_code
488 ORDER BY pbd.database_item_suffix;
489
490 /******************************************************************************
491 * Cursor to get the assignment level Defined Balance Ids for the given Primary
492 * Balance type Id for Arrears Type Balance.
493 ******************************************************************************/
494
495 CURSOR c_get_asg_arr_defbal (p_balance_type_id varchar2) is
496 SELECT DISTINCT pet.element_name,
497 pet.element_type_id,
498 pdb_call.defined_balance_id,
499 pbt.balance_type_id,
500 pbd.balance_dimension_id
501 FROM pay_defined_balances pdb_call,
502 pay_balance_dimensions pbd,
503 pay_balance_types pbt,
504 pay_element_types_f pet
505 WHERE pbt.balance_type_id = fnd_number.canonical_to_number(p_balance_type_id)
506 AND ((pbt.business_group_id IS NOT NULL AND pbt.business_group_id = p_business_group_id)
507 OR (pbt.legislation_code IS NOT NULL AND pbt.legislation_code = l_legislation_code))
508 AND pet.classification_id = l_classification_id
509 AND pet.element_information11 = fnd_number.number_to_canonical(pbt.balance_type_id)
510 AND pbt.balance_type_id = pdb_call.balance_type_id
511 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
512 AND ((p_tax_unit_id <> -1 AND pbd.database_item_suffix = '_ASG_TU_ITD')
513 OR (p_tax_unit_id = -1 AND pbd.database_item_suffix = '_ASG_ITD'))
514 AND pbd.legislation_code = l_legislation_code;
515
516 /******************************************************************************
517 * Cursor to get the assignment level Defined Balance Ids for the given Primary
518 * Balance type Id for Accruals Type Balance.
519 ******************************************************************************/
520
521 CURSOR c_get_asg_acc_defbal (p_balance_type_id varchar2) IS
522 SELECT DISTINCT pet.element_name,
523 pet.element_type_id,
524 pdb_call.defined_balance_id,
525 pbt.balance_type_id,
526 pbd.balance_dimension_id
527 FROM pay_defined_balances pdb_call,
528 pay_balance_dimensions pbd,
529 pay_balance_types pbt,
530 pay_element_types_f pet
531 WHERE pbt.balance_type_id = fnd_number.canonical_to_number(p_balance_type_id)
532 AND ((pbt.business_group_id IS NOT NULL AND pbt.business_group_id = p_business_group_id)
533 OR (pbt.legislation_code IS NOT NULL AND pbt.legislation_code = l_legislation_code))
534 AND pet.classification_id = l_classification_id
535 AND pet.element_information13 = fnd_number.number_to_canonical(pbt.balance_type_id)
536 AND pbt.balance_type_id = pdb_call.balance_type_id
537 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
538 AND ((p_tax_unit_id <> -1 AND pbd.database_item_suffix = '_ASG_TU_ITD')
539 OR (p_tax_unit_id = -1 AND pbd.database_item_suffix = '_ASG_ITD'))
540 AND pbd.legislation_code = l_legislation_code;
541
542
543
544 /******************************************************************************
545 * Cursor to get element_information for Earnings type Elements
546 * in case complete quickpay or payroll run processes have been identified.
547 * This cursor is used in the date mode.
548 ******************************************************************************/
549
550 CURSOR c_element_info_date_earn IS
551 SELECT DISTINCT pet.element_name
552 ,pet.element_type_id
553 ,pet.classification_id
554 ,pet.element_information10
555 FROM pay_element_types_f pet
556 ,pay_element_types_f pet2
557 ,pay_element_entries_f ee
558 WHERE pet2.classification_id = l_classification_id
559 AND pet2.element_information10 IS NOT NULL
560 AND ee.effective_end_date >= l_pay_start_date
561 AND ee.effective_start_date <= nvl(l_action_date , p_session_date )
562 AND ee.effective_start_date BETWEEN pet2.effective_start_date AND pet2.effective_end_date
563 AND pet2.element_type_id = pet.element_type_id
564 AND PET.effective_start_date =
565 (SELECT max(pet1.effective_start_date)
566 FROM pay_element_types_f pet1
567 WHERE pet1.element_type_id = pet.element_type_id
568 AND pet1.effective_start_date <= p_session_date )
569 AND ee.assignment_id = p_assignment_id
570 AND EXISTS
571 (SELECT prr.element_type_id
572 FROM pay_run_results prr
573 WHERE prr.source_id = ee.element_entry_id
574 AND prr.source_type IN ( 'E' , 'I' )
575 AND prr.element_type_id + 0 = pet.element_type_id
576 )
577 ORDER BY 2;
578
579
580 /******************************************************************************
581 * Cursor to get element_information for Deductions type Elements
582 * in case complete quickpay or payroll run processes have been identified.
583 * This cursor is used in the date mode.
584 ******************************************************************************/
585
586 CURSOR c_element_info_date_dedn IS
587 SELECT DISTINCT pet.element_name
588 ,pet.element_type_id
589 ,pet.classification_id
590 ,pet.element_information10
591 ,pet.element_information11
592 ,pet.element_information13
593 FROM pay_element_types_f pet
594 ,pay_element_types_f pet2
595 ,pay_element_entries_f ee
596 WHERE pet2.classification_id = l_classification_id
597 AND pet2.element_information10 IS NOT NULL
598 AND ee.effective_end_date >= l_pay_start_date
599 AND ee.effective_start_date <= nvl(l_action_date , p_session_date )
600 AND ee.effective_start_date BETWEEN pet2.effective_start_date AND pet2.effective_end_date
601 AND pet2.element_type_id = pet.element_type_id
602 AND PET.effective_start_date =
603 (SELECT max(pet1.effective_start_date)
604 FROM pay_element_types_f pet1
605 WHERE pet1.element_type_id = pet.element_type_id
606 AND pet1.effective_start_date <= p_session_date )
607 AND ee.assignment_id = p_assignment_id
608 AND EXISTS
609 (SELECT prr.element_type_id
610 FROM pay_run_results prr
611 WHERE prr.source_id = ee.element_entry_id
612 AND prr.source_type IN ( 'E' , 'I' )
613 AND prr.element_type_id + 0 = pet.element_type_id
614 )
615 ORDER BY 2;
616
617
618
619 /******************************************************************************
620 * Cursor to get element_information for Earnings type Elements
621 * in case complete quickpay or payroll run processes have been identified.
622 * This cursor is used in the assignment action mode.
623 ******************************************************************************/
624
625 CURSOR c_element_info_asact_earn IS
626 SELECT DISTINCT pet.element_name
627 ,pet.element_type_id
628 ,pet.classification_id
629 ,pet.element_information10
630 FROM pay_element_types_f pet
631 ,pay_run_results prr
632 WHERE prr.assignment_action_id = p_assignment_action_id
633 AND pet.element_type_id = prr.element_type_id
634 AND pet.business_group_id = p_business_group_id
635 AND pet.classification_id = l_classification_id
636 AND pet.element_information10 IS NOT NULL;
637
638 /******************************************************************************
639 * Cursor to get element_information for Deductions type Elements
640 * in case complete quickpay or payroll run processes have been identified.
641 * This cursor is used in the assignment action mode.
642 ******************************************************************************/
643
644 CURSOR c_element_info_asact_dedn IS
645 SELECT DISTINCT pet.element_name
646 ,pet.element_type_id
647 ,pet.classification_id
648 ,pet.element_information10
649 ,pet.element_information11
650 ,pet.element_information13
651 FROM pay_element_types_f pet
652 ,pay_run_results prr
653 WHERE prr.assignment_action_id = p_assignment_action_id
654 AND pet.element_type_id = prr.element_type_id
655 AND pet.business_group_id = p_business_group_id
656 AND pet.classification_id = l_classification_id
657 AND pet.element_information10 IS NOT NULL;
658
659
660 /******************************************************************************
661 * Cursor to get element information in case balance initialization is completed
662 * and element classification chosen is of the type Earnings.
663 ******************************************************************************/
664 CURSOR c_element_info_bi_earn IS
665 SELECT DISTINCT pet2. element_name
666 ,pet2.element_type_id
667 ,pet2.classification_id
668 ,pet2.element_information10
669 FROM pay_element_classifications ec
670 ,pay_element_types_f et
671 ,pay_element_links_f el
672 ,pay_element_entries_f ee
673 ,pay_element_entry_values_f eev
674 ,pay_balance_feeds_f pbf
675 ,pay_element_types_f pet2
676 ,pay_element_classifications pec
677 ,pay_input_values_f piv
678 ,pay_balance_types pbt
679 WHERE ec.classification_name = 'Balance Initialization'
680 AND ec.legislation_code IS NULL
681 AND ee.assignment_id = p_assignment_id
682 AND ee.element_link_id = el.element_link_id
683 AND el.element_type_id = et.element_type_id
684 AND et.classification_id = ec.classification_id
685 AND ee.element_entry_id = eev.element_entry_id
686 AND eev.input_value_id = pbf.input_value_id
687 AND piv.input_value_id = pbf.input_value_id
688 AND et.element_type_id = piv.element_type_id
689 AND nvl(l_action_date ,p_session_date) BETWEEN pbf.effective_start_date
690 AND pbf.effective_end_date
691 AND pbf.balance_type_id = pet2.element_information10
692 AND pbt.balance_type_id = pet2.element_information10
693 AND pet2.element_information10 IS NOT NULL
694 AND pet2.classification_id = pec.classification_id
695 AND pec.classification_name = p_classification_name
696 AND pec.legislation_code = l_legislation_code
697 AND nvl(l_action_date, p_session_date ) BETWEEN pet2.effective_start_date
698 AND pet2.effective_end_date
699 AND eev.screen_entry_value IS NOT NULL
700 ORDER BY 2;
701
702 /******************************************************************************
703 * Cursor to get element information in case balance initialization is completed
704 * and element classification chosen is of the type Deductions.
705 ******************************************************************************/
706 CURSOR c_element_info_bi_dedn IS
707 SELECT DISTINCT pet2. element_name
708 ,pet2.element_type_id
709 ,pet2.classification_id
710 ,pet2.element_information10
711 ,pet2.element_information11
712 ,pet2.element_information13
713 FROM pay_element_classifications ec
714 ,pay_element_types_f et
715 ,pay_element_links_f el
716 ,pay_element_entries_f ee
717 ,pay_element_entry_values_f eev
718 ,pay_balance_feeds_f pbf
719 ,pay_element_types_f pet2
720 ,pay_element_classifications pec
721 ,pay_input_values_f piv
722 ,pay_balance_types pbt
723 WHERE ec.classification_name = 'Balance Initialization'
724 AND ec.legislation_code IS NULL
725 AND ee.assignment_id = p_assignment_id
726 AND ee.element_link_id = el.element_link_id
727 AND el.element_type_id = et.element_type_id
728 AND et.classification_id = ec.classification_id
729 AND ee.element_entry_id = eev.element_entry_id
730 AND eev.input_value_id = pbf.input_value_id
731 AND piv.input_value_id = pbf.input_value_id
732 AND et.element_type_id = piv.element_type_id
733 AND nvl(l_action_date ,p_session_date) BETWEEN pbf.effective_start_date
734 AND pbf.effective_end_date
735 AND pbf.balance_type_id = pet2.element_information10
736 AND pbt.balance_type_id = pet2.element_information10
737 AND pet2.element_information10 IS NOT NULL
738 AND pet2.classification_id = pec.classification_id
739 AND pec.classification_name = p_classification_name
740 AND pec.legislation_code = l_legislation_code
741 AND nvl(l_action_date, p_session_date ) BETWEEN pet2.effective_start_date
742 AND pet2.effective_end_date
743 AND eev.screen_entry_value IS NOT NULL
744 ORDER BY 2;
745
746 /******************************************************************************
747 * Name : get_defined_bal
748 * Purpose : This function is used to get the defined balance ids based on
749 * balance type id and balance dimension id.
750 ******************************************************************************/
751 FUNCTION get_defined_bal (p_bal_id in number
752 ,p_dimension in varchar2)
753 RETURN number IS
754 l_defbal_id number;
755 l_function varchar2(16);
756 l_dim_id number;
757 BEGIN
758 l_function :='get_defined_bal';
759 hr_utility.set_location(l_package||l_function, 10);
760
761 SELECT balance_dimension_id INTO l_dim_id
762 FROM pay_balance_dimensions
763 WHERE database_item_suffix = p_dimension
764 AND legislation_code = l_legislation_code;
765
766 SELECT defined_balance_id INTO l_defbal_id
767 FROM pay_defined_balances
768 WHERE balance_type_id = p_bal_id
769 AND balance_dimension_id = l_dim_id
770 AND nvl(business_group_id,p_business_group_id) = p_business_group_id
771 AND nvl(legislation_code,l_legislation_code) = l_legislation_code;
772
773 hr_utility.set_location(l_package||l_function, 20);
774
775 RETURN l_defbal_id;
776
777 EXCEPTION
778 WHEN NO_DATA_FOUND THEN
779 hr_utility.set_location(l_package||l_function, 30);
780 RETURN -1;
781 END;
782
783
784
785 /******************************************************************************
786 * Name : get_bal
787 * Purpose : This function is used to get balance values based on defined
788 * balance ids
789 ******************************************************************************/
790
791 FUNCTION get_bal (p_defbal_id IN number
792 ,p_bal_type_id IN number)
793 RETURN NUMBER IS
794 l_last_process_date DATE;
795 l_date DATE;
796 l_ytd_id number(9);
797 l_qtd_id number(9);
798 l_mtd_id number(9);
799 l_temp_assignment_id per_assignments_f.assignment_id%TYPE;
800 l_function varchar2(9);
801 l_value number := NULL;
802
803 BEGIN
804 l_function :='get_bal';
805 hr_utility.set_location(l_package||l_function, 10);
806 IF (p_assignment_action_id = -1 ) THEN
807 IF p_balance_level='PER' THEN
808 l_ytd_id := get_defined_bal(p_bal_type_id,l_dim_ytd);
809 l_qtd_id := get_defined_bal(p_bal_type_id,l_dim_qtd);
810 l_mtd_id := get_defined_bal(p_bal_type_id,l_dim_mtd);
811
812 hr_utility.set_location(l_package||l_function, 20);
813
814 BEGIN
815 IF p_tax_unit_id <> -1 THEN
816 /*To check whether p_assignment_id exists as of p_session_date
817 when the localization is based on Tax Unit Id architecture.
818 */
819 SELECT paf.assignment_id
820 INTO l_temp_assignment_id
821 FROM per_assignments_f paf,
822 hr_soft_coding_keyflex hsk
823 WHERE paf.assignment_id = p_assignment_id
824 AND paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
825 AND p_session_date BETWEEN paf.effective_start_date
826 AND paf.effective_end_date
827 AND hsk.segment1 = to_char(p_tax_unit_id);
828
829 ELSE
830 /*To check whether p_assignment_id exists as of p_session_date
831 when the localization is not based on Tax Unit Id architecture.
832 */
833 SELECT paf.assignment_id
834 INTO l_temp_assignment_id
835 FROM per_assignments_f paf
836 WHERE paf.assignment_id = p_assignment_id
837 AND paf.business_group_id = p_business_group_id
838 AND p_session_date BETWEEN paf.effective_start_date
839 AND paf.effective_end_date;
840 END IF;
841 l_temp_assignment_id := to_number(p_assignment_id);
842
843 hr_utility.set_location(l_package||l_function, 30);
844
845 EXCEPTION
846 WHEN NO_DATA_FOUND THEN
847 BEGIN
848 IF p_tax_unit_id <> -1 THEN
849 /*Attempt to find any assignment id for the person as of p_session_date
850 when the localization is based on the Tax Unit Id architecture.
851 */
852 hr_utility.set_location(l_package||l_function, 40);
853
854 SELECT paf2.assignment_id
855 INTO l_temp_assignment_id
856 FROM per_assignments_f paf1,
857 per_assignments_f paf2,
858 hr_soft_coding_keyflex hsk
859 WHERE paf1.assignment_id = p_assignment_id
860 AND paf2.person_id = paf1.person_id
861 AND paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
862 AND p_session_date BETWEEN paf2.effective_start_date
863 AND paf2.effective_end_date
864 AND hsk.segment1 = to_char(p_tax_unit_id)
865 AND rownum=1;
866 ELSE
867 /*Attempt to find any assignment id for the person as of p_session_date
868 when the localization is not based on the Tax Unit Id architecture.
869 */
870 SELECT paf2.assignment_id
871 INTO l_temp_assignment_id
872 FROM per_assignments_f paf1,
873 per_assignments_f paf2
874 WHERE paf1.assignment_id = p_assignment_id
875 AND paf2.person_id = paf1.person_id
876 AND paf2.business_group_id = p_business_group_id
877 AND p_session_date BETWEEN paf2.effective_start_date
878 AND paf2.effective_end_date
879 AND rownum=1;
880 END IF;
881 hr_utility.set_location(l_package||l_function, 50);
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 BEGIN
885 IF p_tax_unit_id <> -1 THEN
886 /* Attempt to find an assignment id for the person with end date < p_session_date
887 and greater than pay_ip_route_support.tax_year(p_business_group_id,p_session_date)
888 when the localization is based on Tax Unit Id architecture.
889 */
890 hr_utility.set_location(l_package||l_function, 60);
891 SELECT paf2.assignment_id
892 INTO l_temp_assignment_id
893 FROM per_assignments_f paf1,
894 per_assignments_f paf2,
895 hr_soft_coding_keyflex hsk
896 WHERE paf1.assignment_id = p_assignment_id
897 AND paf2.person_id = paf1.person_id
898 AND paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
899 AND hsk.segment1 = to_char(p_tax_unit_id)
900 AND paf2.effective_end_date < p_session_date
901 AND paf2.effective_end_date >= pay_ip_route_support.tax_year(p_business_group_id,p_session_date)
902 AND paf2.effective_end_date =
903 (SELECT MAX(paf3.effective_end_date)
904 FROM per_assignments_f paf3
905 WHERE paf3.person_id = paf1.person_id
906 AND paf3.effective_end_date < p_session_date
907 )
908 AND rownum=1;
909 ELSE
910 /* Attempt to find an assignment id for the person with end date < p_session_date
911 and greater than pay_ip_route_support.tax_year(p_business_group_id,p_session_date)
912 when the localization is not based on Tax Unit Id architecture.
913 */
914 SELECT paf2.assignment_id
915 INTO l_temp_assignment_id
916 FROM per_assignments_f paf1,
917 per_assignments_f paf2
918 WHERE paf1.assignment_id = p_assignment_id
919 AND paf2.person_id = paf1.person_id
920 AND paf2.business_group_id = p_business_group_id
921 AND paf2.effective_end_date < p_session_date
922 AND paf2.effective_end_date >= pay_ip_route_support.tax_year(p_business_group_id,p_session_date)
923 AND paf2.effective_end_date =
924 (SELECT MAX(paf3.effective_end_date)
925 FROM per_assignments_f paf3
926 WHERE paf3.person_id = paf1.person_id
927 AND paf3.effective_end_date < p_session_date
928 )
929 AND rownum=1;
930
931 END IF;
932 hr_utility.set_location(l_package||l_function, 70);
933 EXCEPTION
934 WHEN NO_DATA_FOUND THEN
935 hr_utility.set_location(l_package||l_function, 80);
936 NULL;
937 END;
938 END;
939 END;
940 ELSE -- ELSE clause for p_balance_level='PER'
941 hr_utility.set_location(l_package||l_function, 90);
942
943 l_temp_assignment_id := p_assignment_id;
944 l_ytd_id := get_defined_bal(p_bal_type_id,l_dim_ytd);
945 l_qtd_id := get_defined_bal(p_bal_type_id,l_dim_qtd);
946 END IF; -- END IF clause for p_balance_level='PER'
947 hr_utility.set_location(l_package||l_function, 100);
948
949 /* find the actual termination of final process date for the assignment*/
950 l_date := payvwele.get_fpd_or_atd(p_assignment_id => l_temp_assignment_id,
951 p_session_date => p_session_date);
952 IF l_date IS NOT NULL THEN
953 IF p_session_date < l_date THEN
954 l_last_process_date := p_session_date;
955 ELSE
956 l_last_process_date := l_date;
957 END IF;
958 END IF;
959
960 hr_utility.set_location(l_package||l_function,110);
961
962 IF l_date IS NULL THEN
963 l_date := p_session_date; -- Current Employee
964 ELSIF l_date >= p_session_date THEN
965 l_date := p_session_date; -- Current Employee
966 ELSIF l_date < pay_ip_route_support.tax_year(p_business_group_id,p_session_date) THEN
967 -- Assignment terminated before this year so no balances for this year
968 l_date := to_date('31-12-4712','DD-MM-YYYY');
969 ELSIF l_date < trunc(p_session_date, 'MONTH') THEN
970 -- Assignment terminated this year but before this month
971 IF l_date >= pay_ip_route_support.tax_quarter(p_business_group_id,p_session_date) THEN
972 -- Assignment terminated this quarter, show QTD and YTD balances
973 IF l_ytd_id = p_defbal_id OR l_qtd_id = p_defbal_id THEN
974 l_date := l_last_process_date;
975 ELSE
976 l_date := p_session_date;
977 END IF;
978 ELSE
979 IF l_ytd_id = p_defbal_id THEN
980 l_date := l_last_process_date;
981 ELSE
982 l_date := p_session_date;
983 END IF;
984
985 IF l_qtd_id = p_defbal_id THEN
986 l_value := 0;
987 RETURN l_value;
988 END IF;
989
990 IF p_balance_level = 'PER' AND l_mtd_id = p_defbal_id THEN
991 l_value := 0;
992 RETURN l_value;
993 END IF;
994 END IF;
995 ELSE
996 -- Assignment terminated this year and this month show all balances
997 l_date := p_session_date;
998 END IF;
999 hr_utility.set_location(l_package||l_function,120);
1000
1001 -- set TAX_UNIT_ID context
1002 IF p_tax_unit_id <> -1 THEN
1003 pay_balance_pkg.set_context ('TAX_UNIT_ID', TO_CHAR(p_tax_unit_id));
1004 END IF;
1005
1006 hr_utility.trace('p_defbal_id: '||to_char(p_defbal_id));
1007 hr_utility.trace('l_temp_assignment_id: '||to_char(l_temp_assignment_id));
1008 hr_utility.trace('l_date: '||to_char(l_date));
1009
1010 IF l_date <> to_date('31-12-4712','DD-MM-YYYY') THEN
1011 hr_utility.set_location(l_package||l_function, 130);
1012
1013 l_value := pay_balance_pkg.get_value_lock( p_defined_balance_id => p_defbal_id,
1014 p_assignment_id => l_temp_assignment_id,
1015 p_virtual_date => l_date,
1016 p_asg_lock => 'N' );
1017
1018 END IF;
1019 hr_utility.set_location(l_package||l_function, 140);
1020
1021 END IF;-- END IF clause for p_assignment_action_id = -1
1022
1023 hr_utility.set_location(l_package||l_function, 150);
1024
1025 RETURN l_value;
1026
1027 EXCEPTION
1028 WHEN NO_DATA_FOUND THEN
1029 hr_utility.set_location(l_package||l_function, 160);
1030 RETURN NULL;
1031 END; --End of function get_bal
1032
1033
1034 /******************************************************************************
1035 * Name : fetch_and_store_balances
1036 * Purpose : This function is used to populate the pl/sql tables and fetch the
1037 * element balance values.
1038 ******************************************************************************/
1039
1040 FUNCTION fetch_and_store_balances
1041 RETURN NUMBER IS
1042 l_function VARCHAR2(30);
1043 BEGIN
1044 l_function :='fetch_and_store_balances';
1045 hr_utility.set_location(l_package||l_function, 10);
1046 IF UPPER(p_classification_name) IN ('INVOLUNTARY DEDUCTIONS' ,
1047 'VOLUNTARY DEDUCTIONS',
1048 'PRE-TAX DEDUCTIONS',
1049 'TAX DEDUCTIONS') THEN
1050
1051 hr_utility.set_location(l_package||l_function, 20);
1052
1053 t_defbal_details_tab.delete; -- purging the pl/sql table to clear any old data.
1054 FOR i IN 0 .. t_deduction_tab.count-1
1055 LOOP
1056 IF t_deduction_tab.exists(i) THEN
1057 IF p_balance_level = 'ASG' THEN
1058 /*The following code fetches the element details and Assignment
1059 level defined balance details for Deductions Type elements and are stored
1060 in the pl/sql table t_defbal_details_tab.
1061 */
1062 k:=0;
1063 FOR k IN c_get_asg_def_bal_id (t_deduction_tab(i).element_information10) -- Used to fetch the Primary Balance Details
1064 LOOP
1065 t_defbal_details_tab(k.defined_balance_id).ele_name := k.element_name;
1066 t_defbal_details_tab(k.defined_balance_id).ele_type_id := k.element_type_id;
1067 t_defbal_details_tab(k.defined_balance_id).def_bal_id := k.defined_balance_id;
1068 t_defbal_details_tab(k.defined_balance_id).bal_value := NULL;
1069 t_defbal_details_tab(k.defined_balance_id).bal_type_id := k.balance_type_id;
1070 t_defbal_details_tab(k.defined_balance_id).bal_dim_id := k.balance_dimension_id;
1071 t_defbal_details_tab(k.defined_balance_id).defbal_type := k.database_item_suffix;
1072 END LOOP;
1073 hr_utility.set_location(l_package||l_function, 30);
1074
1075 --Fetch the Arrears Balance Details
1076 k:=0;
1077 FOR k IN c_get_asg_arr_defbal (t_deduction_tab(i).element_information11)
1078 LOOP
1079 t_defbal_details_tab(k.defined_balance_id).ele_name := k.element_name;
1080 t_defbal_details_tab(k.defined_balance_id).ele_type_id := k.element_type_id;
1081 t_defbal_details_tab(k.defined_balance_id).def_bal_id := k.defined_balance_id;
1082 t_defbal_details_tab(k.defined_balance_id).bal_value := NULL;
1083 t_defbal_details_tab(k.defined_balance_id).bal_type_id := k.balance_type_id;
1084 t_defbal_details_tab(k.defined_balance_id).bal_dim_id := k.balance_dimension_id;
1085 t_defbal_details_tab(k.defined_balance_id).defbal_type := 'ASG_ARR_ITD';
1086 END LOOP;
1087 hr_utility.set_location(l_package||l_function, 40);
1088
1089 --Fetch the Accruals Balance Details
1090 k:=0;
1091 FOR k IN c_get_asg_acc_defbal (t_deduction_tab(i).element_information13)
1092 LOOP
1093 t_defbal_details_tab(k.defined_balance_id).ele_name := k.element_name;
1094 t_defbal_details_tab(k.defined_balance_id).ele_type_id := k.element_type_id;
1095 t_defbal_details_tab(k.defined_balance_id).def_bal_id := k.defined_balance_id;
1096 t_defbal_details_tab(k.defined_balance_id).bal_value := NULL;
1097 t_defbal_details_tab(k.defined_balance_id).bal_type_id := k.balance_type_id;
1098 t_defbal_details_tab(k.defined_balance_id).bal_dim_id := k.balance_dimension_id;
1099 t_defbal_details_tab(k.defined_balance_id).defbal_type := 'ASG_ACC_ITD';
1100 END LOOP;
1101
1102 hr_utility.set_location(l_package||l_function, 50);
1103
1104 ELSIF p_balance_level = 'PER' THEN
1105 /*The following code is written to fetch the element details and Person
1106 level defined balance details for Deductions Type elements and are stored
1107 in the pl/sql table t_defbal_details_tab*/
1108 k:=0;
1109 FOR k IN c_get_per_def_bal_id (t_deduction_tab(i).element_information10) -- Used to fetch the Primary Balance Details
1110 LOOP
1111 t_defbal_details_tab(k.defined_balance_id).ele_name := k.element_name;
1112 t_defbal_details_tab(k.defined_balance_id).ele_type_id := k.element_type_id;
1113 t_defbal_details_tab(k.defined_balance_id).def_bal_id := k.defined_balance_id;
1114 t_defbal_details_tab(k.defined_balance_id).bal_value := NULL;
1115 t_defbal_details_tab(k.defined_balance_id).bal_type_id := k.balance_type_id;
1116 t_defbal_details_tab(k.defined_balance_id).bal_dim_id := k.balance_dimension_id;
1117 t_defbal_details_tab(k.defined_balance_id).defbal_type := k.database_item_suffix;
1118 END LOOP;
1119 hr_utility.set_location(l_package||l_function, 60);
1120 END IF; -- END IF clause for p_balance_level
1121 END IF; -- END IF clause for t_deduction_tab.exists(i)
1122 END LOOP;
1123
1124 hr_utility.set_location(l_package||l_function, 70);
1125
1126 ELSE -- ELSE clause for UPPER(classification_name)
1127 hr_utility.set_location(l_package||l_function, 80);
1128
1129 -- Purge the pl/sql table to clear any old data.
1130 t_defbal_details_tab.delete;
1131 FOR i IN 0 .. t_earnings_tab.count-1
1132 LOOP
1133 IF t_earnings_tab.exists(i) THEN
1134 IF p_balance_level = 'ASG' THEN
1135 /*The following code is written to fetch the element details and Assignment
1136 level defined balance details for Earnings Type elements and are stored
1137 in the pl/sql table t_defbal_details_tab.
1138 */
1139 k:=0;
1140 FOR k IN c_get_asg_def_bal_id (t_earnings_tab(i).element_information10) -- Used to fetch the Primary Balance Details
1141 LOOP
1142 t_defbal_details_tab(k.defined_balance_id).ele_name := k.element_name;
1143 t_defbal_details_tab(k.defined_balance_id).ele_type_id := k.element_type_id;
1144 t_defbal_details_tab(k.defined_balance_id).def_bal_id := k.defined_balance_id;
1145 t_defbal_details_tab(k.defined_balance_id).bal_value := NULL;
1146 t_defbal_details_tab(k.defined_balance_id).bal_type_id := k.balance_type_id;
1147 t_defbal_details_tab(k.defined_balance_id).bal_dim_id := k.balance_dimension_id;
1148 t_defbal_details_tab(k.defined_balance_id).defbal_type := k.database_item_suffix;
1149 END LOOP;
1150 hr_utility.set_location(l_package||l_function, 90);
1151
1152 ELSIF p_balance_level = 'PER' THEN
1153 /*The following code is written to fetch the element details and Person
1154 level defined balance details for Earnings Type elements and are stored
1155 in the pl/sql table t_defbal_details_tab.
1156 */
1157
1158 --fetch the Primary Balance Details
1159 k:=0;
1160 FOR k IN c_get_per_def_bal_id (t_earnings_tab(i).element_information10)
1161 LOOP
1162 t_defbal_details_tab(k.defined_balance_id).ele_name := k.element_name;
1163 t_defbal_details_tab(k.defined_balance_id).ele_type_id := k.element_type_id;
1164 t_defbal_details_tab(k.defined_balance_id).def_bal_id := k.defined_balance_id;
1165 t_defbal_details_tab(k.defined_balance_id).bal_value := NULL;
1166 t_defbal_details_tab(k.defined_balance_id).bal_type_id := k.balance_type_id;
1167 t_defbal_details_tab(k.defined_balance_id).bal_dim_id := k.balance_dimension_id;
1168 t_defbal_details_tab(k.defined_balance_id).defbal_type := k.database_item_suffix;
1169 END LOOP;
1170 hr_utility.set_location(l_package||l_function, 100);
1171 END IF; -- END IF clause for p_balance_level
1172 END IF; -- END IF clause for t_earnings_tab.exists(i)
1173 END LOOP;
1174
1175 hr_utility.set_location(l_package||l_function, 110);
1176
1177 END IF; -- END IF clause for UPPER(classification_name)
1178 hr_utility.set_location(l_package||l_function, 120);
1179
1180 -- purging the pl/sql table to clear any old data.
1181 t_ele_bal_horizontal_tab.delete;
1182
1183 /*The data stored in the pl/sql table t_defbal_details_tab is in a vertical
1184 form i.e. details of every defined balance of a given element is being
1185 stored in a new row in the pl/sql table. The following code is written to
1186 create a pl/sql table structure which is of a horizontal form i.e. all the
1187 defined balance details of the primary balance for the given element is
1188 stored in a single row and all the defined balance details of the arrears
1189 balance and the accruals balance for the given element is stored in
1190 seperate rows in the pl/sql table t_ele_bal_horizontal_tab
1191 */
1192
1193 FOR i IN t_defbal_details_tab.first..t_defbal_details_tab.last
1194 LOOP
1195 IF t_defbal_details_tab.exists(i) THEN
1196 IF (t_defbal_details_tab(i).defbal_type in ('_ASG_MONTH','_ASG_TU_MONTH','_PER_MONTH','_PER_TU_MONTH'))THEN
1197 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ele_name := t_defbal_details_tab(i).ele_name;
1198 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ele_type_id := t_defbal_details_tab(i).ele_type_id;
1199 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).bal_type_id := t_defbal_details_tab(i).bal_type_id;
1200 ELSIF (t_defbal_details_tab(i).defbal_type in ('ASG_ARR_ITD')) THEN
1201 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ele_name := t_defbal_details_tab(i).ele_name;
1202 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ele_type_id := t_defbal_details_tab(i).ele_type_id;
1203 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).bal_type_id := t_defbal_details_tab(i).bal_type_id;
1204 ELSIF (t_defbal_details_tab(i).defbal_type in ('ASG_ACC_ITD')) THEN
1205 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ele_name := t_defbal_details_tab(i).ele_name;
1206 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ele_type_id := t_defbal_details_tab(i).ele_type_id;
1207 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).bal_type_id := t_defbal_details_tab(i).bal_type_id;
1208 END IF;
1209 END IF;
1210 END LOOP;
1211 hr_utility.set_location(l_package||l_function, 130);
1212
1213 -- The page is called in assignment action mode
1214 IF p_assignment_action_id <> -1 THEN
1215 IF p_tax_unit_id <> -1 THEN
1216 t_eb_bal_context_tab(1).tax_unit_id:= p_tax_unit_id;
1217 ELSE
1218 t_eb_bal_context_tab(1).tax_unit_id:= NULL;
1219 END IF;
1220 hr_utility.set_location(l_package||l_function, 140);
1221
1222 -- purging the pl/sql table to clear any old data.
1223 t_eb_balance_value_tab.delete;
1224
1225 /*The following code is written to identify the specific defined balance ids for which
1226 the balance values are supposed to be fetched. These defined balance ids are identified
1227 using the Defined Balance Type stored in t_defbal_details_tab and the dimension option
1228 selected at the SS Page level that are passed to the current package. The selected defined
1229 balance ids are stored in the pl/sql table t_eb_balance_value_tab which is later passed to
1230 the pay_balance_pkg to bulk fetch the balance values.
1231 */
1232
1233 j:=1;
1234 FOR i IN t_defbal_details_tab.first..t_defbal_details_tab.last
1235 LOOP
1236 IF t_defbal_details_tab.exists(i) THEN
1237 hr_utility.trace('Defined Balance Type is: '||t_defbal_details_tab(i).defbal_type);
1238 IF (t_defbal_details_tab(i).defbal_type IN ('_ASG_PTD','_ASG_TU_PTD') AND p_asg_ptd = 1) OR
1239 (t_defbal_details_tab(i).defbal_type IN ('_ASG_MONTH','_ASG_TU_MONTH') AND p_asg_month = 1) OR
1240 (t_defbal_details_tab(i).defbal_type IN ('_ASG_TQTD','_ASG_TU_TQTD') AND p_asg_qtd = 1) OR
1241 (t_defbal_details_tab(i).defbal_type IN ('_ASG_TYTD','_ASG_TU_TYTD') AND p_asg_ytd = 1) OR
1242 (t_defbal_details_tab(i).defbal_type = 'ASG_ARR_ITD' AND p_asg_arr_itd = 1) OR
1243 (t_defbal_details_tab(i).defbal_type = 'ASG_ACC_ITD' AND p_asg_acc_itd = 1) OR
1244 (t_defbal_details_tab(i).defbal_type IN ('_PER_MONTH','_PER_TU_MONTH') AND p_per_month = 1) OR
1245 (t_defbal_details_tab(i).defbal_type IN ('_PER_TQTD','_PER_TU_TQTD') AND p_per_qtd = 1) OR
1246 (t_defbal_details_tab(i).defbal_type IN ('_PER_TYTD','_PER_TU_TYTD') AND p_per_ytd = 1)
1247 THEN
1248 t_eb_balance_value_tab(j).defined_balance_id:=t_defbal_details_tab(i).def_bal_id;
1249 hr_utility.trace('Defined Balance id to be sent to pay_balance_pkg is '||to_char(t_eb_balance_value_tab(j).defined_balance_id));
1250 j:=j+1;
1251 END IF;
1252 END IF;
1253 END LOOP;
1254
1255 /*The following is the pay_balance_pkg call to bulk fetch the balance values.
1256 We pass the assignment action id for the current Quick pay or Payroll Run,
1257 t_eb_balance_value_tab pl/sql table with the required defined balance ids,
1258 t_eb_bal_context_tab pl/sql table with the balance context values,
1259 t_eb_bal_result_tab emplty pl/sql table that will hold the balance values
1260 along with the defined balance ids as parameters to the pay_balance_pkg call.
1261 */
1262 hr_utility.set_location(l_package||l_function, 150);
1263 pay_balance_pkg.get_value
1264 (p_assignment_action_id => p_assignment_action_id
1265 ,p_defined_balance_lst => t_eb_balance_value_tab
1266 ,p_context_lst => t_eb_bal_context_tab
1267 ,p_output_table => t_eb_bal_result_tab
1268 );
1269
1270 hr_utility.set_location(l_package||l_function, 160);
1271
1272 FOR i IN t_eb_bal_result_tab.first..t_eb_bal_result_tab.last
1273 LOOP
1274 IF t_eb_bal_result_tab.exists(i) THEN
1275 /*The following code is used to populate the balance value column in the
1276 t_defbal_details_tab pl/sql table with the balance values stored in the
1277 pl/sql table t_eb_bal_result_tab.
1278 */
1279 t_defbal_details_tab(t_eb_bal_result_tab(i).defined_balance_id).bal_value := t_eb_bal_result_tab(i).balance_value;
1280 END IF;
1281 END LOOP;
1282 ELSE -- ELSE clause for p_assignment_action_id <> -1
1283 /*The following code is written to identify the specific defined balance ids for which
1284 the balance values are supposed to be fetched. These defined balance ids are identified
1285 using the Defined Balance Type stored in t_defbal_details_tab and the dimension option
1286 selected at the SS Page level that are passed to the current package. The selected defined
1287 balance ids and associated balance type ids are passed to the get_bal function which returns
1288 the balance value for the current defined balance id. These balance values are stored in the
1289 pl/sql table t_defbal_details_tab.
1290 */
1291
1292 hr_utility.set_location(l_package||l_function, 170);
1293
1294 FOR i IN t_defbal_details_tab.first..t_defbal_details_tab.last
1295 LOOP
1296 IF t_defbal_details_tab.exists(i) THEN
1297 IF (t_defbal_details_tab(i).defbal_type IN ('_ASG_PTD','_ASG_TU_PTD') AND p_asg_ptd = 1) OR
1298 (t_defbal_details_tab(i).defbal_type IN ('_ASG_MONTH','_ASG_TU_MONTH') AND p_asg_month = 1) OR
1299 (t_defbal_details_tab(i).defbal_type IN ('_ASG_TQTD','_ASG_TU_TQTD') AND p_asg_qtd = 1) OR
1300 (t_defbal_details_tab(i).defbal_type IN ('_ASG_TYTD','_ASG_TU_TYTD') AND p_asg_ytd = 1) OR
1301 (t_defbal_details_tab(i).defbal_type = 'ASG_ARR_ITD' AND p_asg_arr_itd = 1) OR
1302 (t_defbal_details_tab(i).defbal_type = 'ASG_ACC_ITD' AND p_asg_acc_itd = 1) OR
1303 (t_defbal_details_tab(i).defbal_type IN ('_PER_MONTH','_PER_TU_MONTH') AND p_per_month = 1) OR
1304 (t_defbal_details_tab(i).defbal_type IN ('_PER_TQTD','_PER_TU_TQTD') AND p_per_qtd = 1) OR
1305 (t_defbal_details_tab(i).defbal_type IN ('_PER_TYTD','_PER_TU_TYTD') AND p_per_ytd = 1)
1306 THEN
1307 t_defbal_details_tab(i).bal_value := get_bal(t_defbal_details_tab(i).def_bal_id,t_defbal_details_tab(i).bal_type_id);
1308 hr_utility.trace('Defined Balance id for which the balance value needs to be fetched is '||to_char(t_defbal_details_tab(i).def_bal_id));
1309 END IF;
1310 END IF;
1311 END LOOP;
1312 hr_utility.set_location(l_package||l_function, 180);
1313 END IF; -- END IF clause for p_assignment_action_id <> -1
1314
1315 hr_utility.set_location(l_package||l_function, 190);
1316 FOR i IN t_defbal_details_tab.first..t_defbal_details_tab.last
1317 LOOP
1318 IF t_defbal_details_tab.exists(i) THEN
1319 IF t_defbal_details_tab(i).defbal_type IN ('_ASG_PTD','_ASG_TU_PTD') THEN
1320 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ptd_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1321 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ptd_bal_value := t_defbal_details_tab(i).bal_value;
1322 ELSIF t_defbal_details_tab(i).defbal_type IN ('_ASG_MONTH','_ASG_TU_MONTH') THEN
1323 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).mtd_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1324 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).mtd_bal_value := t_defbal_details_tab(i).bal_value;
1325 ELSIF t_defbal_details_tab(i).defbal_type IN ('_ASG_TQTD','_ASG_TU_TQTD') THEN
1326 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).qtd_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1327 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).qtd_bal_value := t_defbal_details_tab(i).bal_value;
1328 ELSIF t_defbal_details_tab(i).defbal_type IN ('_ASG_TYTD','_ASG_TU_TYTD') THEN
1329 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ytd_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1330 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ytd_bal_value := t_defbal_details_tab(i).bal_value;
1331 ELSIF t_defbal_details_tab(i).defbal_type = 'ASG_ARR_ITD' THEN
1332 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).arr_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1333 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).arr_bal_value := t_defbal_details_tab(i).bal_value;
1334 ELSIF t_defbal_details_tab(i).defbal_type = 'ASG_ACC_ITD' THEN
1335 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).acc_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1336 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).acc_bal_value := t_defbal_details_tab(i).bal_value;
1337 ELSIF t_defbal_details_tab(i).defbal_type IN ('_PER_MONTH','_PER_TU_MONTH') THEN
1338 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).mtd_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1339 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).mtd_bal_value := t_defbal_details_tab(i).bal_value;
1340 ELSIF t_defbal_details_tab(i).defbal_type IN ('_PER_TQTD','_PER_TU_TQTD') THEN
1341 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).qtd_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1342 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).qtd_bal_value := t_defbal_details_tab(i).bal_value;
1343 ELSIF t_defbal_details_tab(i).defbal_type IN ('_PER_TYTD','_PER_TU_TYTD') THEN
1344 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ytd_bal_dim_id := t_defbal_details_tab(i).bal_dim_id;
1345 t_ele_bal_horizontal_tab(t_defbal_details_tab(i).bal_type_id).ytd_bal_value := t_defbal_details_tab(i).bal_value;
1346 END IF;
1347 END IF;
1348 END LOOP;
1349 hr_utility.set_location(l_package||l_function, 200);
1350
1351 RETURN 1;
1352
1353 EXCEPTION
1354 WHEN OTHERS THEN
1355 raise_application_error(-20101, 'Error in '||l_package||l_function || ' - ' || sqlerrm);
1356 RETURN 0;
1357 END; -- END of fetch_and_store_balances function
1358
1359 /******************************************************************************
1360 * Name : populate_tab_rpt_totals
1361 * Purpose : This function is used to insert the data in the pl/sql table
1362 * t_ele_bal_horizontal_tab into pay_us_rpt_totals_table.
1363 ******************************************************************************/
1364
1365 FUNCTION populate_tab_rpt_totals (p_insert_flag varchar2)
1366 RETURN NUMBER IS
1367 l_function VARCHAR2(30);
1368 l_id_value number;
1369 BEGIN
1370 l_function :='populate_tab_rpt_totals';
1371 hr_utility.set_location(l_package||l_function, 10);
1372
1373 IF p_insert_flag = 'Y' THEN
1374 hr_utility.set_location(l_package||l_function, 20);
1375 FOR i IN t_ele_bal_horizontal_tab.first..t_ele_bal_horizontal_tab.last
1376 LOOP
1377 IF t_ele_bal_horizontal_tab.exists(i) THEN
1378 IF t_ele_bal_horizontal_tab(i).arr_bal_dim_id IS NOT NULL THEN
1379 l_id_value := 2; -- Arrears Balance
1380 ELSIF t_ele_bal_horizontal_tab(i).acc_bal_dim_id IS NOT NULL THEN
1381 l_id_value := 3; -- Accruals Balance
1382 ELSE
1383 l_id_value := 1; -- Primary Balance
1384 END IF;
1385 INSERT INTO pay_us_rpt_totals
1386 (GRE_NAME,
1387 ORGANIZATION_ID,
1388 ORGANIZATION_NAME,
1389 LOCATION_NAME,
1390 LOCATION_ID,
1391 STATE_NAME,
1392 TAX_UNIT_ID,
1393 VALUE2,
1394 VALUE3,
1395 VALUE4,
1396 VALUE5,
1397 VALUE6,
1398 VALUE7,
1399 VALUE8,
1400 VALUE9,
1401 VALUE10,
1402 VALUE11,
1403 VALUE12,
1404 VALUE13,
1405 VALUE14)
1406 VALUES ('IP_VIEW_BALANCE',
1407 p_assignment_id,
1408 p_balance_level,
1409 t_ele_bal_horizontal_tab(i).ele_name,
1410 t_ele_bal_horizontal_tab(i).ele_type_id,
1411 fnd_date.date_to_canonical(p_session_date),
1412 l_id_value,
1413 t_ele_bal_horizontal_tab(i).bal_type_id,
1414 t_ele_bal_horizontal_tab(i).ptd_bal_dim_id,
1415 t_ele_bal_horizontal_tab(i).ptd_bal_value,
1416 t_ele_bal_horizontal_tab(i).mtd_bal_dim_id,
1417 t_ele_bal_horizontal_tab(i).mtd_bal_value,
1418 t_ele_bal_horizontal_tab(i).qtd_bal_dim_id,
1419 t_ele_bal_horizontal_tab(i).qtd_bal_value,
1420 t_ele_bal_horizontal_tab(i).ytd_bal_dim_id,
1421 t_ele_bal_horizontal_tab(i).ytd_bal_value,
1422 t_ele_bal_horizontal_tab(i).arr_bal_dim_id,
1423 t_ele_bal_horizontal_tab(i).arr_bal_value,
1424 t_ele_bal_horizontal_tab(i).acc_bal_dim_id,
1425 t_ele_bal_horizontal_tab(i).acc_bal_value);
1426 END IF; -- END IF clause for t_ele_bal_horizontal_tab.exists(i)
1427 END LOOP;
1428 hr_utility.set_location(l_package||l_function, 30);
1429 RETURN 1;
1430 END IF; -- END IF clause for p_insert_flag
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 raise_application_error(-20101, 'Error in '||l_package||l_function || ' - ' || sqlerrm);
1435 RETURN 0;
1436 END; -- END of populate_tab_rpt_totals function
1437
1438
1439 BEGIN -- populate_element_info
1440
1441 l_procedure :='populate_element_info';
1442
1443 hr_utility.set_location(l_package||l_procedure, 10);
1444 hr_utility.trace('p_assignment_id: '||to_char(p_assignment_id));
1445 hr_utility.trace('p_assignment_action_id: '||to_char(p_assignment_action_id));
1446 hr_utility.trace('p_classification_name: '||to_char(p_classification_name));
1447 hr_utility.trace('p_session_date: '||to_char(p_session_date));
1448 hr_utility.trace('p_tax_unit_id: '||to_char(p_tax_unit_id));
1449 hr_utility.trace('p_per_month: '||to_char(p_per_month));
1450 hr_utility.trace('p_per_qtd: '||to_char(p_per_qtd));
1451 hr_utility.trace('p_per_ytd: '||to_char(p_per_ytd));
1452 hr_utility.trace('p_asg_ptd: '||to_char(p_asg_ptd));
1453 hr_utility.trace('p_asg_month: '||to_char(p_asg_month));
1454 hr_utility.trace('p_asg_qtd: '||to_char(p_asg_qtd));
1455 hr_utility.trace('p_asg_ytd: '||to_char(p_asg_ytd));
1456 hr_utility.trace('p_asg_arr_itd: '||to_char(p_asg_arr_itd));
1457 hr_utility.trace('p_asg_acc_itd: '||to_char(p_asg_acc_itd));
1458 hr_utility.trace('p_business_group_id: '||to_char(p_business_group_id));
1459 hr_utility.trace('p_balance_level: '||to_char(p_balance_level));
1460
1461 l_action_date := payvwele.get_action_date(p_assignment_action_id);
1462
1463 SELECT MIN(ptp.start_date) first_period_start_date
1464 INTO l_pay_start_date
1465 FROM per_assignments_f paf,
1466 per_time_periods ptp
1467 WHERE paf.assignment_id = p_assignment_id
1468 AND ptp.payroll_id = paf.payroll_id
1469 AND ptp.regular_payment_date >= pay_ip_route_support.tax_year(p_business_group_id,nvl(l_action_date, p_session_date));
1470
1471 hr_utility.trace('l_action_date: '||to_char(l_action_date));
1472 hr_utility.trace('l_pay_start_date: '||to_char(l_pay_start_date));
1473
1474 /*The following code sets the balance dimension name based on
1475 the balance level and a valid tax unit id value.
1476 */
1477 IF p_balance_level='ASG' THEN
1478 hr_utility.set_location(l_package||l_procedure, 20);
1479 IF p_asg_month = 0 THEN
1480 l_dim_mtd := NULL;
1481 ELSIF p_asg_month = 1 AND p_tax_unit_id <> -1 THEN
1482 l_dim_mtd := '_ASG_TU_MONTH';
1483 ELSE
1484 l_dim_mtd := '_ASG_MONTH';
1485 END IF;
1486
1487 IF p_asg_qtd = 0 THEN
1488 l_dim_qtd := NULL;
1489 ELSIF p_asg_qtd = 1 AND p_tax_unit_id <> -1 THEN
1490 l_dim_qtd := '_ASG_TU_TQTD';
1491 ELSE
1492 l_dim_qtd := '_ASG_TQTD';
1493 END IF;
1494
1495 IF p_asg_ytd = 0 THEN
1496 l_dim_ytd := NULL;
1497 ELSIF p_asg_ytd = 1 AND p_tax_unit_id <> -1 THEN
1498 l_dim_ytd := '_ASG_TU_TYTD';
1499 ELSE
1500 l_dim_ytd := '_ASG_TYTD';
1501 END IF;
1502
1503 hr_utility.trace('l_dim_mtd: '||to_char(l_dim_mtd));
1504 hr_utility.trace('l_dim_qtd: '||to_char(l_dim_qtd));
1505 hr_utility.trace('l_dim_ytd: '||to_char(l_dim_ytd));
1506
1507 -- balance level is PER
1508 ELSE --ELSE clause of p_balance_level
1509
1510 hr_utility.set_location(l_package||l_procedure, 30);
1511 IF p_per_month = 0 THEN
1512 l_dim_mtd := NULL;
1513 ELSIF p_per_qtd = 1 AND p_tax_unit_id <> -1 THEN
1514 l_dim_mtd := '_PER_TU_MONTH';
1515 ELSE
1516 l_dim_mtd := '_PER_MONTH';
1517 END IF;
1518
1519 IF p_per_qtd = 0 THEN
1520 l_dim_qtd := NULL;
1521 ELSIF p_per_qtd = 1 AND p_tax_unit_id <> -1 THEN
1522 l_dim_qtd := '_PER_TU_TQTD';
1523 ELSE
1524 l_dim_qtd := '_PER_TQTD';
1525 END IF;
1526
1527 IF p_per_ytd = 0 THEN
1528 l_dim_ytd := NULL;
1529 ELSIF p_per_ytd = 1 AND p_tax_unit_id <> -1 THEN
1530 l_dim_ytd := '_PER_TU_TYTD';
1531 ELSE
1532 l_dim_ytd := '_PER_TYTD';
1533 END IF;
1534
1535 hr_utility.trace('l_dim_mtd: '||to_char(l_dim_mtd));
1536 hr_utility.trace('l_dim_qtd: '||to_char(l_dim_qtd));
1537 hr_utility.trace('l_dim_ytd: '||to_char(l_dim_ytd));
1538
1539 END IF; -- END IF clause of p_balance_level
1540
1541 hr_utility.set_location(l_package||l_procedure, 40);
1542
1543 OPEN c_leg_code;
1544 FETCH c_leg_code INTO l_legislation_code;
1545 CLOSE c_leg_code;
1546
1547 OPEN c_get_class_name (l_legislation_code);
1548 FETCH c_get_class_name INTO l_classification_id;
1549 CLOSE c_get_class_name;
1550
1551 /*Based on whether a valid tax_unit_id has been passed
1552 either c_action_type_tu or c_action_type_bg is called
1553 to fetch the latest payroll process action type.
1554 */
1555 IF p_tax_unit_id <> -1 THEN
1556 OPEN c_action_type_tu;
1557 FETCH c_action_type_tu INTO l_type;
1558 CLOSE c_action_type_tu;
1559 ELSIF p_tax_unit_id = -1 THEN
1560 OPEN c_action_type_bg;
1561 FETCH c_action_type_bg INTO l_type;
1562 CLOSE c_action_type_bg;
1563 END IF;
1564
1565 hr_utility.set_location(l_package||l_procedure, 50);
1566 hr_utility.trace('l_legislation_code: '||to_char(l_legislation_code));
1567 hr_utility.trace('l_classification_id: '||to_char(l_classification_id));
1568 hr_utility.trace('l_type: '||to_char(l_type));
1569
1570 -- If none of the check boxes are checked,nothing to fetch
1571 IF ((p_per_month = 0) AND (p_per_qtd = 0) AND (p_per_ytd = 0) AND (p_asg_ptd = 0) AND (p_asg_month = 0)
1572 AND (p_asg_qtd = 0) AND (p_asg_ytd = 0) AND (p_asg_arr_itd = 0) AND (p_asg_acc_itd = 0)) THEN
1573
1574 hr_utility.set_location(l_package||l_procedure, 60);
1575 hr_utility.trace('No Elements will be displayed as no options have been chosen');
1576 ELSE -- Atleast one of the options has been selected at the page level.
1577 hr_utility.set_location(l_package||l_procedure, 70);
1578 IF l_type ='Q' or l_type = 'R' THEN
1579 IF p_assignment_action_id <> -1 THEN
1580 i:=0;
1581 l_flag := 'N';
1582 hr_utility.set_location(l_package||l_procedure, 80);
1583 IF UPPER(p_classification_name) IN ('PRE-TAX DEDUCTIONS',
1584 'INVOLUNTARY DEDUCTIONS',
1585 'VOLUNTARY DEDUCTIONS',
1586 'TAX DEDUCTIONS') THEN
1587 hr_utility.set_location(l_package||l_procedure, 90);
1588 t_deduction_tab.delete;
1589 OPEN c_element_info_asact_dedn;
1590 LOOP
1591 FETCH c_element_info_asact_dedn INTO
1592 t_deduction_tab(i).element_name
1593 ,t_deduction_tab(i).element_type_id
1594 ,t_deduction_tab(i).classification_id
1595 ,t_deduction_tab(i).element_information10
1596 ,t_deduction_tab(i).element_information11
1597 ,t_deduction_tab(i).element_information13;
1598 IF c_element_info_asact_dedn%FOUND THEN
1599 l_flag := 'Y'; -- Flag set to 'Y' if any element details are found.
1600 END IF;
1601 EXIT WHEN c_element_info_asact_dedn%NOTFOUND;
1602 i:=i+1;
1603 END LOOP;
1604 hr_utility.set_location(l_package||l_procedure, 100);
1605 CLOSE c_element_info_asact_dedn;
1606
1607 -- The element classification is of the EARNIGNS type.
1608 ELSE -- ELSE clause of UPPER(p_classification_name) IN ('PRE-TAX DEDUCTIONS'
1609 hr_utility.set_location(l_package||l_procedure, 110);
1610 t_earnings_tab.delete;
1611 OPEN c_element_info_asact_earn ;
1612 LOOP
1613 FETCH c_element_info_asact_earn INTO
1614 t_earnings_tab(i).element_name
1615 ,t_earnings_tab(i).element_type_id
1616 ,t_earnings_tab(i).classification_id
1617 ,t_earnings_tab(i).element_information10;
1618 IF c_element_info_asact_earn%FOUND THEN
1619 l_flag := 'Y'; -- Flag set to 'Y' if any element details are found.
1620 END IF;
1621 EXIT WHEN c_element_info_asact_earn%NOTFOUND;
1622 i:=i+1;
1623 END LOOP;
1624 hr_utility.set_location(l_package||l_procedure, 120);
1625 CLOSE c_element_info_asact_earn;
1626 END IF; -- END IF clause of UPPER(p_classification_name) IN ('PRE-TAX DEDUCTIONS'
1627
1628 hr_utility.set_location(l_package||l_procedure, 130);
1629
1630 IF l_flag = 'Y' THEN -- Only if the element details were found proceed further to fetch and insert the balance values.
1631 hr_utility.set_location(l_package||l_procedure, 140);
1632
1633 -- call the function to fetch defined balances,balance values and populate the pl/sql tables
1634 l_return_value := FETCH_AND_STORE_BALANCES();
1635
1636 hr_utility.trace('l_return_value in Quick Pay/Payroll Run and Assignment Action Mode is: '||to_char(l_return_value));
1637
1638 IF l_return_value = 1 THEN
1639 l_insert_flag := 'Y';
1640 hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Assignment Action Mode is: '||to_char(l_insert_flag));
1641 ELSE
1642 l_insert_flag := 'N';
1643 hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Assignment Action Mode is: '||to_char(l_insert_flag));
1644 END IF;
1645
1646 -- call the function to populate the table pay_us_rpt_totals
1647 l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);
1648
1649 ELSIF l_flag = 'N' THEN -- no elements are fetched
1650 hr_utility.set_location(l_package||l_procedure, 150);
1651 hr_utility.trace('No Elements were fetched for the given Classification Name - Quick Pay/Payroll Run and Assignment Action Mode');
1652 END IF; -- END IF clause for l_flag
1653
1654 -- The page is opened in Date mode
1655 ELSE -- ELSE clause for p_assignment_action_id <> -1
1656 hr_utility.set_location(l_package||l_procedure, 160);
1657 IF UPPER(p_classification_name) IN ('EARNINGS',
1658 'SUPPLEMENTAL EARNINGS',
1659 'DIRECT PAYMENT',
1660 'TAXABLE BENEFITS',
1661 'EMPLOYER CHARGES') THEN
1662 hr_utility.set_location(l_package||l_procedure, 170);
1663 t_earnings_tab.delete;
1664 i:=0;
1665 l_flag := 'N';
1666 OPEN c_element_info_date_earn;
1667 LOOP
1668 FETCH c_element_info_date_earn
1669 INTO t_earnings_tab(i).element_name
1670 ,t_earnings_tab(i).element_type_id
1671 ,t_earnings_tab(i).classification_id
1672 ,t_earnings_tab(i).element_information10;
1673 IF c_element_info_date_earn%FOUND THEN
1674 l_flag := 'Y'; -- Flag set to 'Y' if any element details are found.
1675 END IF;
1676 EXIT WHEN c_element_info_date_earn %NOTFOUND;
1677 i:=i+1;
1678 END LOOP;
1679 hr_utility.set_location(l_package||l_procedure, 180);
1680 CLOSE c_element_info_date_earn;
1681
1682 -- The element classification is the DEDUCTIONS type.
1683 ELSE -- ELSE Clause for UPPER(p_classification_name) IN ('EARNINGS'
1684 hr_utility.set_location(l_package||l_procedure, 190);
1685 t_deduction_tab.delete;
1686 i:=0;
1687 l_flag := 'N';
1688 OPEN c_element_info_date_dedn ;
1689 LOOP
1690 FETCH c_element_info_date_dedn
1691 INTO t_deduction_tab(i).element_name
1692 ,t_deduction_tab(i).element_type_id
1693 ,t_deduction_tab(i).classification_id
1694 ,t_deduction_tab(i).element_information10
1695 ,t_deduction_tab(i).element_information11
1696 ,t_deduction_tab(i).element_information13;
1697 IF c_element_info_date_dedn%FOUND THEN
1698 l_flag := 'Y'; -- Flag set to 'Y' if any element details are found.
1699 END IF;
1700 EXIT WHEN c_element_info_date_dedn %NOTFOUND;
1701 i:=i+1;
1702 END LOOP;
1703 hr_utility.set_location(l_package||l_procedure, 200);
1704 CLOSE c_element_info_date_dedn;
1705 END IF; -- END IF clause for UPPER(p_classification_name) IN ('EARNINGS'
1706 hr_utility.set_location(l_package||l_procedure, 210);
1707 IF l_flag = 'Y' THEN -- Only if the element details were found proceed further to fetch and insert the balance values.
1708 hr_utility.set_location(l_package||l_procedure, 220);
1709
1710 -- call the function to fetch defined balances,balance values and populate the pl/sql tables
1711 l_return_value := FETCH_AND_STORE_BALANCES();
1712
1713 hr_utility.trace('l_return_value in Quick Pay/Payroll Run and Date Mode is: '||to_char(l_return_value));
1714
1715 IF l_return_value = 1 THEN
1716 l_insert_flag := 'Y';
1717 hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Date Mode is: '||to_char(l_insert_flag));
1718 ELSE
1719 l_insert_flag := 'N';
1720 hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Date Mode is: '||to_char(l_insert_flag));
1721 END IF;
1722
1723 -- call the function to populate the table pay_us_rpt_totals
1724 l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);
1725
1726 ELSIF l_flag = 'N' THEN -- no elements are fetched
1727 hr_utility.set_location(l_package||l_procedure, 230);
1728 hr_utility.trace('No Elements were fetched for the given Classification Name - Quick Pay/Payroll Run and Date Mode');
1729 END IF; -- END IF clause for l_flag
1730 END IF; -- END IF clause for p_assignment_action_id <> -1
1731 ELSIF l_type='I' THEN -- Balance Initialization case
1732 hr_utility.set_location(l_package||l_procedure, 240);
1733
1734 i:=0;
1735 l_flag := 'N';
1736 IF UPPER(p_classification_name) IN ('PRE-TAX DEDUCTIONS',
1737 'INVOLUNTARY DEDUCTIONS',
1738 'VOLUNTARY DEDUCTIONS',
1739 'TAX DEDUCTIONS') THEN
1740 hr_utility.set_location(l_package||l_procedure, 250);
1741 t_deduction_tab.delete;
1742 OPEN c_element_info_bi_dedn;
1743 LOOP
1744 FETCH c_element_info_bi_dedn INTO
1745 t_deduction_tab(i).element_name
1746 ,t_deduction_tab(i).element_type_id
1747 ,t_deduction_tab(i).classification_id
1748 ,t_deduction_tab(i).element_information10
1749 ,t_deduction_tab(i).element_information11
1750 ,t_deduction_tab(i).element_information13;
1751 IF c_element_info_bi_dedn%FOUND THEN
1752 l_flag := 'Y'; -- Flag set to 'Y' if any element details are found.
1753 END IF;
1754 EXIT WHEN c_element_info_bi_dedn%NOTFOUND;
1755 i:=i+1;
1756 END LOOP;
1757 hr_utility.set_location(l_package||l_procedure, 260);
1758 CLOSE c_element_info_bi_dedn;
1759
1760 -- The element classifications is of the EARNINGS type.
1761 ELSE -- ELSE clause for UPPER(p_classification_name) IN ('PRE-TAX DEDUCTIONS'
1762 hr_utility.set_location(l_package||l_procedure, 270);
1763 t_earnings_tab.delete;
1764 OPEN c_element_info_bi_earn ;
1765 LOOP
1766 FETCH c_element_info_bi_earn INTO
1767 t_earnings_tab(i).element_name
1768 ,t_earnings_tab(i).element_type_id
1769 ,t_earnings_tab(i).classification_id
1770 ,t_earnings_tab(i).element_information10;
1771 IF c_element_info_bi_earn%FOUND THEN
1772 l_flag := 'Y'; -- Flag set to 'Y' if any element details are found.
1773 END IF;
1774 EXIT WHEN c_element_info_bi_earn%NOTFOUND;
1775 i:=i+1;
1776 END LOOP;
1777 hr_utility.set_location(l_package||l_procedure, 280);
1778 CLOSE c_element_info_bi_earn;
1779 END IF; -- END IF clause for UPPER(p_classification_name) IN ('PRE-TAX DEDUCTIONS'
1780 hr_utility.set_location(l_package||l_procedure, 290);
1781
1782 IF l_flag = 'Y' THEN -- Only if the element details were found proceed further to fetch and insert the balance values.
1783 hr_utility.set_location(l_package||l_procedure, 300);
1784
1785 -- call the function to fetch defined balances,balance values and populate the pl/sql tables
1786 l_return_value := FETCH_AND_STORE_BALANCES();
1787
1788 hr_utility.trace('l_return_value in Balance Initialization and Date Mode is: '||to_char(l_return_value));
1789
1790 IF l_return_value = 1 THEN
1791 l_insert_flag := 'Y';
1792 hr_utility.trace('Insert Flag value in Balance Initialization and Date Mode is: '||to_char(l_insert_flag));
1793 ELSE
1794 l_insert_flag := 'N';
1795 hr_utility.trace('Insert Flag value in Balance Initialization and Date Mode is: '||to_char(l_insert_flag));
1796 END IF;
1797
1798 -- call the function to populate the table pay_us_rpt_totals
1799 l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);
1800
1801 ELSIF l_flag = 'N' THEN -- no elements are fetched
1802 hr_utility.set_location(l_package||l_procedure, 310);
1803 hr_utility.trace('No Elements were fetched for the given Classification Name - Balance Initialization and Date Mode');
1804 END IF; -- END IF clause for l_flag
1805 END IF; -- END IF clause for l_type
1806 END IF; -- END IF clause for the IF statement that checks whether any of the dimension options on the SS Page were selcted or not.
1807
1808 COMMIT;
1809
1810 EXCEPTION
1811 WHEN others THEN
1812 ROLLBACK;
1813 hr_utility.set_location(l_package||l_procedure, 320);
1814 raise_application_error(-20101, 'Error in '||l_package||l_procedure || ' - ' || sqlerrm);
1815 END;
1816
1817 END pay_ip_employee_balances;
1818
[Home] [Help]