[Home] [Help]
PACKAGE BODY: APPS.PAY_GTNLOD_PKG
Source
1 package body pay_gtnlod_pkg as
2 /* $Header: pygtnlod.pkb 120.12.12010000.2 2008/08/06 07:22:17 ubhat ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5 --
6 Name :This package defines the cursors needed for GTN to run Multi-Threaded
7 --
8 Change List
9 -----------
10 Date Name Vers Description
11 ----------- ---------- ----- -----------------------------------
12 21-NOV-1999 ssarma 40.0 created
13 19-oct-2000 tclewis 115.1 added procedure load_wc_er_liab
14 to load workers compensation ...
15 (wc2 and wc3) ER liabilities into
16 the pay_reports_totals table to be
17 displayed in the GTN report.
18 02-jan-2001 tmehra 115.2 Added procedure 'load_alien_earnings'
19 to reflect 'Alien/Expat Earnings' also
20 modified procedure 'ee_lod_dedutions'
21 to reflect 'Alien/Expat Deductions'
22 09-OCT-2001 tclewis 115.5 Modified load_prepay procedure to run
23 Multi threaded.
24 31-JAN-2002 tclewis 115.6 Modified the load_ee_tax specificall the
25 query of local taxes to join to the
26 ppa_year_effective_date of the
27 pay_us_local_taxes_v views, to eliminate
28 local taxes being reporting incorrectly.
29 Bug 2060255. Modified the load_message
30 lines routine, adding a cursor look around
31 the outer cursor, to check each row in the
32 pay_pre_payments table With Out payments.
33 19-FEB-2002 tclewis 115.9 Change label "Number of Incomplete Payments'
34 to "Incomplete Payments" and
35 "Number of Complete Payments" back to
36 "Disbursements".
37 16-MAY-2002 ahanda 115.10 Added session var to supress PTD balance calls.
38 22-MAY-2002 sodhingr 115.11 For bug 2304091- Modified the cursor ee_tax
39 removed the join with column paa_year_effective_date as
40 the view pay_us_local_taxes_v has been changed (removed the
41 inline view).
42 10-JUL-2002 tclewis 115.12 modified load_prepay, when inserting data into
43 pay_us_rpt_totals also load pay_pre_payments.pre_payment_id
44 into the pay_us_rpt_totals.location_id (an indexed column).
45 New logic to look for the Pre_payment_id in the table
46 before inserting. This will eliminate duplicates.
47 added same functionality to load_mesg_line for the count
48 of unpaid pre-payments.
49 29-APR-2003 rsirigir 115.13 Bug 1937448,modified the appropriate select
50 statements in the cursor ee_tax, cursor er_tax
51 to include state name,school_district_name,county_name,
52 city_name to user reporting name
53 10-JUN-2002 tclewis 115.15 modified the load_er_liab procedures cursor
54 to join to pet.business_group_id.
55
56 04-AUG-2003 irgonzal 115.16 Bug fix 3046274. Amended cursor prepay
57 and added new condition to process only
58 one "run" asg action.
59 18-NOV-2003 rmonge 115.20 Fix for bug 3168646. Modified the load_prepay
60 program. Added new query to select the maximum
61 assignment_action_id for the payroll runs being
62 processed including the ones for Suplemental
63 runs with a separate check set to yes.
64 20-NOV-2003 irgonzal 115.22 Added the School District code and state abreviation
65 when displaying SD withheld (3271447).
66 6-NOV-2003 tlewis 115.17 Added code to Load EE Credit to handle State EIC.
67 14-APR-2004 schauhan 115.25 Modified the appropiate select statement in the cursor
68 ee_tax to include state_name,school_district_name,county_name
69 and city_name to user_reporting_name.Bug3553730
70 16-APR-2004 schauhan 115.26 Bug 3543649. Changed the query for the cursor prepay in the procedure
71 load_prepay so that query also returns third party payments.
72 04-MAY-2004 irgonzal 115.27 Bug fix 3270485. Modified load_data procedure and commented
73 out the insert into rpt totals.
74 05-May-2004 irgonzal 115.28 Fixed GSCC errors.
75 22-JUL-2004 saurgupt 115.29 Bug 3369218: Modified cursor er_liab in procedure load_er_liab
76 to remove FTS on pay_element_types_f
77 29-SEP-2004 saurgupt 115.30 Bug 3873069: Modified cursor er_liab of procedure load_er_liab
78 and cursor wc_er_liab of procedure load_wc_er_liab. The condition
79 pet.element_name = pbt.balance_name is modified to
80 pet.element_information10 = pbt.balance_type_id. This condition will
81 work even if balance_name of primary balance of element is not
82 equal to element_name.
83 09-DEC-2004 sgajula 115.31 Changed the procedures to implement BRA.
84 11-DEC-2004 sgajula 115.32 Changed the bulk insert block to Simple Insert
85 09-FEB-2005 rdhingra 115.33 Reset varibale l_status to 0 in deduction region
86 05-Mar-2005 rdhingra 115.34 Changed ee_or_er_code = 'ER' in load_er_tax
87 01-JUL-2005 tclewis 115.35 On Behalf of sackumar and saurgupt. Implemened
88 changes for bug 3774591. First change in the
89 load_mesg_line added code to check for the existance
90 of a pre-payment assignment action before counting
91 a payroll run as a unprocessed prepayment.
92 The second issue is to modify the load_prepay
93 In the code to determine the max_action_sequence
94 assignment action, added a check for the existence
95 of run results when pulling the max_action_sequece.
96 18-Jul-2005 sackumar 115.36 For Bug No 4429173. Change the condition for checking the
97 source_action_id in load_prepay procedure.
98 29-Aug-2005 rdhingra 115.37 For Bug No 4568652. Modified cursor cv of procedure
99 load_er_liab.
100 The condition pet.element_name = pbt.balance_name is modified to
101 pet.element_information10 = pbt.balance_type_id.
102 29-Aug-2005 sackumar 115.38 For Performance Bug No 4344971.
103 Introduced Index Hint in the SQL ID 12201224 and 12201189
104 12-SEP-2005 pragupta 115.39 Bug 4534407: Changed the attribute1 in the g_totals_table in
105 the load_er_liab procedure from 'EE-CREDIT' to 'ER-LIAB'. Also
106 added an extra condition in the l_er_liab_where variable.
107 16-SEP-2005 rdhingra 115.40 Added a distinct clause in cursor cv of procedure load_er_liab
108 02-FEB-2006 schauhan 115.41 Changed the dimension for FUTA CREDIT from ASG_GRE_RUN to ASG_JD_GRE_RUN
109 and passed jurisdiction_code to balance call. Bug 4443935.
110 21-MAR-2006 schauhan 115.42 Bug 5021468.
111 10-May-2006 sackumar 115.43 Bug 5068645. modified the dynamic query in load_er_tax procedure.
112 24-May-2006 sackumar 115.44 Bug 5244469. modified the dynamic query in load_er_tax procedure.
113 11-AUG-2006 saurgupt 115.45 Bug 5409416: Modified the procedure load_er_credit. Removed
114 prr.jurisdiction_code from select clause as this will fail if l_futa_from
115 is pay_run_balances table.
116 16-OCT-2006 jdevasah 115.46 Bug 4942114: Dynamic cursors in procedures load_deductions, load_earnings,
117 load_ee_tax, load_er_tax, load_ee_credit, load_er_credit, load_er_liab
118 and load_wc_er_liab are replaced by static procedures. Input parameters
119 to all the above procedures are changed to status flags instead
120 from respective view names.
121
122 16-OCT-2006 jdevasah 115.46 Bug 6998211: Restricted GRE Name to 228 chars as report showing
123 blank when we give gre_name more than 228 chars.
124 */
125 ------------------------------------- Global Varaibles ---------------------------
126 l_start_date pay_payroll_actions.start_date%type;
127 l_end_date pay_payroll_actions.effective_date%type;
128 l_business_group_id pay_payroll_actions.business_group_id%type;
129 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
130 l_effective_date pay_payroll_actions.effective_date%type;
131 l_action_type pay_payroll_actions.action_type%type;
132 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
133 l_assignment_id pay_assignment_actions.assignment_id%type;
134 l_tax_unit_id hr_organization_units.organization_id%type;
135 l_person_id per_all_people_f.person_id%TYPE; -- #1937448
136 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
137 l_gre_name hr_organization_units.name%type;
138 l_organization_id hr_organization_units.organization_id%type;
139 l_org_name hr_organization_units.name%type;
140 l_location_id hr_locations.location_id%type;
141 l_location_code hr_locations.location_code%type;
142 l_ppp_assignment_action_id pay_assignment_actions.assignment_action_id%type;
143 l_bal_value number(20,2);
144 l_leg_param varchar2(240);
145 l_leg_start_date date;
146 l_leg_end_date date;
147 t_payroll_id number(15);
148 t_consolidation_set_id number(15);
149 t_gre_id number(15);
150 t_payroll_action_id pay_payroll_actions.payroll_action_id%type;
151 l_defined_balance_id number;
152 l_row_count number;
153 l_full_name per_all_people_f.full_name%TYPE;
154 l_asg_flag varchar2(1);
155
156 ----------------------------------------------------------------------------------
157 /*-- Bug#4942114 starts -- */
158 -- procedure load_deductions (l_assignment_action_id number,l_ded_view_name varchar2) is
159 -- TYPE cv_typ IS REF CURSOR;
160 -- cv cv_typ;
161
162 procedure load_deductions (l_assignment_action_id number,p_ded_bal_status1 varchar2,p_ded_bal_status2 varchar2) is
163 cursor csr_ded is
164 select classification_name,
165 decode(classification_name,'Pre-Tax Deductions','1','Involuntary Deductions','2','Voluntary Deductions','3','9')subclass,
166 element_name,
167 RUN_VALUE cash_value
168 from PAY_US_GTN_DEDUCT_V
169 where assignment_action_id =l_assignment_action_id
170 and classification_name in ('Pre-Tax Deductions',
171 'Involuntary Deductions',
172 'Voluntary Deductions');
173
174 Cursor csr_ded_rbr is
175 select classification_name,
176 decode(classification_name,'Pre-Tax Deductions','1','Involuntary Deductions','2','Voluntary Deductions','3','9')subclass,
177 element_name,
178 RUN_VALUE cash_value
179 from PAY_US_ASG_RUN_DED_RBR_V
180 where assignment_action_id =l_assignment_action_id
181 and classification_name in ('Pre-Tax Deductions',
182 'Involuntary Deductions',
183 'Voluntary Deductions');
184 /*-- Bug#4942114 ends -- */
185
186 l_classification_name varchar2(100);
187 l_element_name varchar2(100);
188 l_cash_value varchar2(100);
189 l_hours_value varchar2(100);
190 l_subclass varchar2(5);
191 l_ded_temp varchar2(2000);
192 l_status number :=0;
193
194 BEGIN
195 /*-- Bug#4942114 starts -- */
196 /* hr_utility.trace('view name = '|| l_ded_view_name);
197
198 OPEN cv FOR
199 'select classification_name,
200 decode(classification_name,'||'''Pre-Tax Deductions'''||','||'''1'''||','||'''Involuntary Deductions'''||','||'''2'''||','||'''Voluntary Deductions'''||','||'''3'''||','||'''9'''||')subclass,
201 element_name,
202 RUN_VALUE cash_value
203 from '||l_ded_view_name||
204 ' where assignment_action_id ='|| l_assignment_action_id||
205 ' and classification_name in ('||'''Pre-Tax Deductions'''||','
206 ||'''Involuntary Deductions'''||','||
207 '''Voluntary Deductions'''||')';
208
209 hr_utility.trace('statement build success');
210 */
211 hr_utility.trace('Balance Status1 = '|| p_ded_bal_status1);
212 hr_utility.trace('Balance Status2 = '|| p_ded_bal_status2);
213
214 if p_ded_bal_status1 = 'Y' AND p_ded_bal_status2 = 'Y' THEN
215 open csr_ded_rbr;
216 else
217 open csr_ded;
218 end if;
219
220 LOOP
221
222 if p_ded_bal_status1 = 'Y' AND p_ded_bal_status2 = 'Y' THEN
223 FETCH csr_ded_rbr INTO l_classification_name,l_subclass,l_element_name,l_cash_value;
224 EXIT WHEN csr_ded_rbr%NOTFOUND;
225 else
226 FETCH csr_ded INTO l_classification_name,l_subclass,l_element_name,l_cash_value;
227 EXIT WHEN csr_ded%NOTFOUND;
228 end if;
229 hr_utility.trace('-'||l_classification_name||'+'||l_subclass||'+'||l_element_name||'+'||l_cash_value||'-');
230
231 /* FETCH cv INTO l_classification_name,l_subclass,l_element_name,l_cash_value;
232 hr_utility.trace('-'||l_classification_name||'+'||l_subclass||'+'||l_element_name||'+'||l_cash_value||'-');
233 EXIT WHEN cv%NOTFOUND;
234 */
235 /*-- Bug#4942114 ends -- */
236 if l_asg_flag <> 'Y' THEN
237 if l_index <>0 then
241 and g_totals_table(l_temp_index).gre_name = l_gre_name
238 l_status := 0;
239 for l_temp_index in g_totals_table.first..g_totals_table.last LOOP
240 if g_totals_table(l_temp_index).attribute5 = l_element_name
242 and g_totals_table(l_temp_index).organization_name = l_org_name
243 and g_totals_table(l_temp_index).location_name = l_location_code then
244 hr_utility.trace('testing 1');
245 g_totals_table(l_temp_index).value2 := g_totals_table(l_temp_index).value2 +
246 to_number(l_cash_value);
247 hr_utility.trace('for deductions...l_index ='||l_index);
248 hr_utility.trace('element name ='||l_element_name);
249 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
250 hr_utility.trace('gre_name='||l_gre_name);
251 hr_utility.trace('org name='||l_org_name);
252 hr_utility.trace('location='||l_location_code);
253 hr_utility.trace('Toal Cash value ='||to_char(g_totals_table(l_temp_index).value2));
254 hr_utility.trace('Cash Value ='||l_cash_value);
255 l_status := 1;
256 end if;
257 end loop;
258 end if;
259 if l_status <> 1 or l_index = 0 then
260 l_index := l_index + 1;
261 g_totals_table(l_index).gre_name := l_gre_name;
262 g_totals_table(l_index).organization_name := l_org_name;
263 g_totals_table(l_index).location_name := l_location_code;
264 g_totals_table(l_index).tax_unit_id := l_payroll_action_id;
265 g_totals_table(l_index).attribute4 := l_classification_name;
266 g_totals_table(l_index).attribute3 := l_subclass;
267 g_totals_table(l_index).attribute5 := l_element_name;
268 g_totals_table(l_index).value2 := to_number(l_cash_value);
269 g_totals_table(l_index).value3 := NULL;
270 g_totals_table(l_index).attribute1 := 'DEDUCTIONS';
271 g_totals_table(l_index).attribute2 := '4';
272 hr_utility.trace('for deductions...l_index ='||l_index);
273 hr_utility.trace('element name ='||l_element_name);
274 hr_utility.trace('payroll action='||to_char(l_payroll_action_id));
275 hr_utility.trace('gre_name='||l_gre_name);
276 hr_utility.trace('org name='||l_org_name);
277 hr_utility.trace('location='||l_location_code);
278 hr_utility.trace('Cash Value ='||l_cash_value);
279 end if;
280 else
281 insert into pay_us_rpt_totals
282 (tax_unit_id,
283 gre_name,
284 organization_name,
285 location_name,
286 attribute1,
287 value1,
288 attribute2,
289 attribute3,
290 attribute4,
291 attribute5,
292 value2,
293 organization_id,
294 business_group_id,
295 attribute12)
296 values
297 (l_payroll_action_id,
298 l_gre_name,
299 l_org_name,
300 l_location_code,
301 'DEDUCTIONS',
302 l_payroll_action_id,
303 '4',
304 l_subclass,
305 l_classification_name,
306 l_element_name,
307 l_cash_value,
308 l_assignment_action_id,
309 l_person_id,
310 l_full_name
311 );
312 end if;
313 end loop;
314
315 if p_ded_bal_status1 = 'Y' AND p_ded_bal_status2 = 'Y' THEN
316 close csr_ded_rbr;
317 else
318 close csr_ded;
319 end if;
320 -- close cv;
321 hr_utility.trace('l_index ='||to_char(l_index));
322 exception
323 when others then
324 hr_utility.trace('Error occurred load_deductions ...' ||SQLERRM);
325 raise;
326 end load_deductions;
327
328
329 /*-- Bug#4942114 starts -- */
330 -- procedure load_earnings (l_assignment_action_id number,l_earn_view_name varchar2) is
331 -- TYPE cv_typ IS REF CURSOR;
332 -- cv cv_typ;
333
334 procedure load_earnings (l_assignment_action_id number,p_earn_bal_status varchar2) is
335
336 cursor csr_earn_rbr is
337 select classification_name,
338 decode(classification_name,'Earnings','1',
339 'Imputed Earnings','2',
340 'Supplemental Earnings','3',
341 'Non-payroll Payments','4',
342 '9')subclass,
343 element_name,
344 cash_value cash_value,
345 hours_value hours_value
346 from PAY_US_ASG_RUN_EARN_AMT_RBR_V
347 where assignment_action_id = l_assignment_action_id
348 and classification_name in ('Earnings',
349 'Imputed Earnings',
350 'Supplemental Earnings',
351 'Non-payroll Payments');
352
353 cursor csr_earn is
354 select /*+ index(pay_us_gtn_earnings_v.ernv.pec , pay_element_classification_pk)
355 INDEX(pay_us_gtn_earnings_v.ernv.PETTL PAY_ELEMENT_TYPES_F_TL_PK)
356 INDEX(pay_us_gtn_earnings_v.ernv.pet PAY_ELEMENT_TYPES_F_pk)
357 */
358 classification_name,
359 decode(classification_name,'Earnings','1',
360 'Imputed Earnings','2',
361 'Supplemental Earnings','3',
362 'Non-payroll Payments','4',
363 '9')subclass,
364 element_name,
365 cash_value cash_value,
366 hours_value hours_value
370 'Imputed Earnings',
367 from PAY_US_GTN_EARNINGS_V
368 where assignment_action_id = l_assignment_action_id
369 and classification_name in ('Earnings',
371 'Supplemental Earnings',
372 'Non-payroll Payments');
373 /*-- Bug#4942114 ends -- */
374
375 l_classification_name varchar2(100);
376 l_element_name varchar2(100);
377 l_cash_value varchar2(100);
378 l_hours_value varchar2(100);
379 l_subclass varchar2(5);
380 l_earn_temp varchar2(2000);
381 l_status number :=0;
382 BEGIN
383
384 /*-- Bug#4942114 starts -- */
385 -- hr_utility.trace('l_earn_view_name = '|| l_earn_view_name);
386
387
388 -- OPEN cv FOR
389 -- 'select /*+ index(pay_us_gtn_earnings_v.ernv.pec , pay_element_classification_pk)
390 -- INDEX(pay_us_gtn_earnings_v.ernv.PETTL PAY_ELEMENT_TYPES_F_TL_PK)
391 -- INDEX(pay_us_gtn_earnings_v.ernv.pet PAY_ELEMENT_TYPES_F_pk)
392 -- */
393 -- classification_name,
394 -- decode(classification_name,'||'''Earnings'''||','||'''1'''||','||'''Imputed Earnings'''||','||'''2'''||','||'''Supplemental Earnings'''||','||'''3'''||','||'''Non-payroll Payments'''||','||'''4'''||','||'''9'''||')subclass,
395 -- element_name,
396 -- cash_value cash_value,
397 -- hours_value hours_value
398 -- from '||l_earn_view_name||
399 -- ' where assignment_action_id = '||l_assignment_action_id||
400 -- ' and classification_name in ('||'''Earnings'''||','
401 -- ||'''Imputed Earnings'''||','||
402 -- '''Supplemental Earnings'''||','
403 -- ||'''Non-payroll Payments'''||')';
404 -- hr_utility.trace('statement build success');
405
406 if p_earn_bal_status = 'Y' THEN
407 open csr_earn_rbr;
408 else
409 open csr_earn;
410 end if;
411
412
413 LOOP
414
415 if p_earn_bal_status = 'Y' THEN
416 FETCH csr_earn_rbr INTO l_classification_name,l_subclass,l_element_name,l_cash_value,l_hours_value;
417 EXIT WHEN csr_earn_rbr%NOTFOUND;
418 else
419 FETCH csr_earn INTO l_classification_name,l_subclass,l_element_name,l_cash_value,l_hours_value;
420 EXIT WHEN csr_earn%NOTFOUND;
421 end if;
422 -- FETCH cv INTO l_classification_name,l_subclass,l_element_name,l_cash_value,l_hours_value;
423 -- EXIT WHEN cv%NOTFOUND;
424
425 /*-- Bug#4942114 ends -- */
426 if l_asg_flag <> 'Y' THEN
427 if l_index <>0 then
428 l_status :=0;
429 for l_temp_index in g_totals_table.first..g_totals_table.last LOOP
430 if g_totals_table(l_temp_index).attribute3 = l_subclass and
431 g_totals_table(l_temp_index).attribute4 = l_classification_name and
432 g_totals_table(l_temp_index).attribute5 = l_element_name and
433 g_totals_table(l_temp_index).gre_name = l_gre_name and
434 g_totals_table(l_temp_index).organization_name = l_org_name and
435 g_totals_table(l_temp_index).location_name = l_location_code then
436 g_totals_table(l_temp_index).value2 := g_totals_table(l_temp_index).value2 +
437 to_number(l_cash_value);
438 g_totals_table(l_temp_index).value3 := g_totals_table(l_temp_index).value3 +
439 to_number(l_hours_value);
440 l_status := 1;
441 hr_utility.trace('for earnings...l_index ='||l_index);
442 hr_utility.trace('element name ='||l_element_name);
443 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
444 hr_utility.trace('gre_name='||l_gre_name);
445 hr_utility.trace('org name='||l_org_name);
446 hr_utility.trace('location='||l_location_code);
447 hr_utility.trace('Toal Cash value ='||to_char(g_totals_table(l_temp_index).value2));
448 hr_utility.trace('Cash Value ='||l_cash_value);
449
450 end if;
451 end loop;
452 end if;
453 if l_status <> 1 or l_index = 0 then
454 hr_utility.trace('l_status ='||l_status||' l_index ='||l_index);
455 l_index := l_index + 1;
456 g_totals_table(l_index).gre_name := l_gre_name;
457 g_totals_table(l_index).organization_name := l_org_name;
458 g_totals_table(l_index).location_name := l_location_code;
459 g_totals_table(l_index).tax_unit_id := l_payroll_action_id;
460 g_totals_table(l_index).attribute4 := l_classification_name;
461 g_totals_table(l_index).attribute3 := l_subclass;
462 g_totals_table(l_index).attribute5 := l_element_name;
463 g_totals_table(l_index).value2 := to_number(l_cash_value);
464 g_totals_table(l_index).value3 := to_number(l_hours_value);
465 g_totals_table(l_index).attribute1 := 'EARNINGS';
466 g_totals_table(l_index).attribute2 := '1';
467 hr_utility.trace('for earnings...l_index ='||l_index);
468 hr_utility.trace('element name ='||l_element_name);
469 hr_utility.trace('payroll action='||to_char(l_payroll_action_id));
470 hr_utility.trace('gre_name='||l_gre_name);
471 hr_utility.trace('org name='||l_org_name);
472 hr_utility.trace('location='||l_location_code);
473 hr_utility.trace('Cash Value ='||l_cash_value);
474 end if;
475 else
479 organization_name,
476 insert into pay_us_rpt_totals
477 (tax_unit_id,
478 gre_name,
480 location_name,
481 attribute1,
482 value1,
483 attribute2,
484 attribute3,
485 attribute4,
486 attribute5,
487 value2,
488 value3,
489 organization_id,
490 business_group_id,
491 attribute12)
492 values
493 (l_payroll_action_id,
494 l_gre_name,
495 l_org_name,
496 l_location_code,
497 'EARNINGS',
498 l_payroll_action_id,
499 '1',
500 l_subclass,
501 l_classification_name,
502 l_element_name,
503 l_cash_value,
504 l_hours_value,
505 l_assignment_action_id,
506 l_person_id,
507 l_full_name);
508 end if;
509 end loop;
510 /*-- Bug#4942114 starts -- */
511 -- close cv;
512
513 if p_earn_bal_status = 'Y' THEN
514 close csr_earn_rbr;
515 else
516 close csr_earn;
517 end if;
518 /*-- Bug#4942114 ends -- */
519
520 exception
521 when others then
522 hr_utility.trace('Error occurred load_earnings ...' ||SQLERRM);
523 raise;
524 end load_earnings;
525 --------------------------------------------------------------
526 -- Following procedure has been added by tmehra on 02-JAN-2001
527 -- to reflect 'Alien/Expat Earnings'
528 --------------------------------------------------------------
529 -- procedure name : load_alien_earnings
530 --------------------------------------------------------------
531 procedure load_alien_earnings (l_assignment_action_id number) is
532 cursor ee_earn is
533 select /*+ index(pay_us_earnings_amounts_v.pet , pay_element_types_f_pk)*/
534 classification_name,
535 5 sub_class,
536 element_name,
537 run_val cash_value,
538 hours_run_val hours_value
539 from pay_us_earnings_amounts_v
540 where assignment_action_id = l_assignment_action_id
541 and classification_name = 'Alien/Expat Earnings';
542
543 ee_earn_rec ee_earn%rowtype;
544 begin
545 open ee_earn ;
546 loop
547 fetch ee_earn into ee_earn_rec;
548 hr_utility.trace('Number of Earnings Records fetched = '||to_char(ee_earn%ROWCOUNT));
549 exit when ee_earn%notfound;
550 insert into pay_us_rpt_totals
551 (tax_unit_id, gre_name, organization_name, location_name,
552 attribute1,
553 value1,
554 attribute2,
555 attribute3,
556 attribute4,
557 attribute5,
558 value2,
559 value3,
560 organization_id)
561 values
562 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
563 'EARNINGS',
564 l_payroll_action_id,
565 '1',
566 ee_earn_rec.sub_class,
567 ee_earn_rec.classification_name,
568 ee_earn_rec.element_name,
569 ee_earn_rec.cash_value,
570 ee_earn_rec.hours_value,
571 l_assignment_action_id);
572 end loop;
573 close ee_earn;
574 exception
575 when others then
576 hr_utility.trace('Error occurred load_earnings ...' ||SQLERRM);
577 raise;
578 end load_alien_earnings;
579 --------------------------------------------------------------
580 /*-- Bug#4942114 starts -- */
581 /* procedure load_ee_tax (l_assignment_action_id number,l_fed_view_name varchar2,
582 l_state_view_name varchar2,l_local_view_name varchar2) is
583 TYPE cv_typ IS REF CURSOR;
584 cv cv_typ; */
585 procedure load_ee_tax (l_assignment_action_id number,p_fed_bal_status varchar2,p_state_bal_status varchar2,p_local_bal_status varchar2) is
586 cursor csr_ee_tax_rbr is
587 select user_reporting_name,'1' sub_class,run_val,null,null
588 from PAY_US_ASG_RUN_FED_TAX_RBR_V
589 where assignment_action_id = l_assignment_action_id
590 and ee_or_er_code = 'EE'
591 and tax_type_code <> 'EIC'
592 UNION ALL
593 select user_reporting_name ||' '|| state_name,'2' sub_class,run_val,TAX_TYPE_CODE,jurisdiction_code
594 from PAY_US_ASG_RUN_STATE_TAX_RBR_V
595 where assignment_action_id =l_assignment_action_id
596 and ee_or_er_code = 'EE'
597 and tax_type_code <> 'STEIC'
598 UNION ALL
599 select user_reporting_name||' '||(decode(state_name,'INVALID',null,state_name))||
600 ' '|| nvl((decode(school_district_name,'INVALID',null, school_district_name)),
601 nvl((decode(county_name,'INVALID',null,county_name)),
602 (decode(city_name,'INVALID',null,city_name))
603 )
604 ),
605 '3' sub_class,run_val,
606 TAX_TYPE_CODE,
607 jurisdiction_code
608 from PAY_US_ASG_RUN_LOCAL_TAX_RBR_V
609 where assignment_action_id = l_assignment_action_id
610 and ee_or_er_code = 'EE'
611 UNION ALL
612 SELECT 'EE Non W2 FIT Withheld',
613 '4' sub_class,
614 pqp_us_ff_functions.get_nonw2_bal('Non W2 FIT Withheld','run',paa.assignment_action_id,null,paa.tax_unit_id) run_value,
618 WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = 'TRUE'
615 null,
616 null
617 FROM pay_assignment_actions paa
619 AND assignment_action_id = l_assignment_action_id
620 UNION ALL
621 SELECT 'EE Non W2 SIT Withheld',
622 '4',
623 pqp_us_ff_functions.get_nonw2_bal('SIT Alien Withheld','run',paa.assignment_action_id,state.jurisdiction_code,paa.tax_unit_id) run_value,
624 null,
625 null
626 FROM pay_assignment_actions paa,
627 pay_us_emp_state_tax_rules_f state,
628 pay_payroll_actions ppa
629 WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = 'TRUE'
630 AND state.assignment_id = paa.assignment_id
631 AND ppa.payroll_action_id = paa.payroll_action_id
632 AND paa.assignment_action_id =l_assignment_action_id
633 AND ppa.effective_date BETWEEN state.effective_start_date AND state.effective_end_date;
634
635
636
637
638 cursor csr_ee_tax is
639 select user_reporting_name,'1' sub_class,run_val,null,null
640 from PAY_US_FED_TAXES_V
641 where assignment_action_id = l_assignment_action_id
642 and ee_or_er_code = 'EE'
643 and tax_type_code <> 'EIC'
644 UNION ALL
645 select user_reporting_name ||' '|| state_name,'2' sub_class,run_val,TAX_TYPE_CODE,jurisdiction_code
646 from PAY_US_STATE_TAXES_V
647 where assignment_action_id =l_assignment_action_id
648 and ee_or_er_code = 'EE'
649 and tax_type_code <> 'STEIC'
650 UNION ALL
651 select user_reporting_name||' '||(decode(state_name,'INVALID',null,state_name))||
652 ' '|| nvl((decode(school_district_name,'INVALID',null, school_district_name)),
653 nvl((decode(county_name,'INVALID',null,county_name)),
654 (decode(city_name,'INVALID',null,city_name))
655 )
656 ),
657 '3' sub_class,run_val,
658 TAX_TYPE_CODE,
659 jurisdiction_code
660 from PAY_US_LOCAL_TAXES_V
661 where assignment_action_id = l_assignment_action_id
662 and ee_or_er_code = 'EE'
663 UNION ALL
664 SELECT 'EE Non W2 FIT Withheld',
665 '4' sub_class,
666 pqp_us_ff_functions.get_nonw2_bal('Non W2 FIT Withheld','run',paa.assignment_action_id,null,paa.tax_unit_id) run_value,
667 null,
668 null
669 FROM pay_assignment_actions paa
670 WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = 'TRUE'
671 AND assignment_action_id = l_assignment_action_id
672 UNION ALL
673 SELECT 'EE Non W2 SIT Withheld',
674 '4',
675 pqp_us_ff_functions.get_nonw2_bal('SIT Alien Withheld','run',paa.assignment_action_id,state.jurisdiction_code,paa.tax_unit_id) run_value,
676 null,
677 null
678 FROM pay_assignment_actions paa,
679 pay_us_emp_state_tax_rules_f state,
680 pay_payroll_actions ppa
681 WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = 'TRUE'
682 AND state.assignment_id = paa.assignment_id
683 AND ppa.payroll_action_id = paa.payroll_action_id
684 AND paa.assignment_action_id =l_assignment_action_id
685 AND ppa.effective_date BETWEEN state.effective_start_date AND state.effective_end_date;
686 /*-- Bug#4942114 ends -- */
687
688 l_user_reporting_name varchar2(60);
689 l_sub_class varchar2(1);
690 l_run_val number(20,2);
691 l_tax_type_code varchar2(30);
692 l_jurisdiction_code varchar2(11);
693 l_sd_name varchar2(30);
694 l_ee_tax_temp varchar2(3000);
695 l_status number :=0;
696 cursor csr_sd_name(p_state_code varchar2, p_sd_code varchar2)
697 is
698 select STATE_ABBREV||'-'||SCHOOL_DST_NAME
699 from pay_us_school_dsts DS
700 ,pay_us_states st
701 where DS.STATE_CODE = p_state_code
702 and DS.SCHOOL_DST_CODE = p_sd_code
703 and ST.state_code = DS.state_code;
704 BEGIN
705 /*-- Bug#4942114 starts -- */
706 /* hr_utility.trace('view names = '|| l_fed_view_name || l_state_view_name || l_local_view_name);
707
708 OPEN cv FOR
709 'select user_reporting_name,''1'' sub_class,run_val,null,null
710 from '||l_fed_view_name||
711 ' where assignment_action_id ='|| l_assignment_action_id||
712 ' and ee_or_er_code = ''EE''
713 and tax_type_code <> ''EIC'' UNION ALL
714 select user_reporting_name ||'' ''|| state_name,''2'' sub_class,run_val,TAX_TYPE_CODE,jurisdiction_code
715 from '||l_state_view_name||
716 ' where assignment_action_id ='||l_assignment_action_id||
717 ' and ee_or_er_code = ''EE''
718 and tax_type_code <> ''STEIC'' UNION ALL
719 select user_reporting_name||'' ''||(decode(state_name,''INVALID'',null,state_name))||
720 '' ''|| nvl((decode(school_district_name,''INVALID'',null, school_district_name)),
721 nvl(
722 (decode(county_name,''INVALID'',null,county_name)),
723 (decode(city_name,''INVALID'',null,city_name))
724 )),''3'' sub_class,run_val,TAX_TYPE_CODE,jurisdiction_code
725 from '|| l_local_view_name||
726 ' where assignment_action_id = '||l_assignment_action_id||
727 ' and ee_or_er_code = ''EE''
731 pqp_us_ff_functions.get_nonw2_bal(''Non W2 FIT Withheld'',''run'',paa.assignment_action_id,null,paa.tax_unit_id) run_value,
728 UNION ALL
729 SELECT ''EE Non W2 FIT Withheld'' ,
730 ''4'' sub_class,
732 null,
733 null
734 FROM pay_assignment_actions paa
735 WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = ''TRUE''
736 AND assignment_action_id = '||l_assignment_action_id ||' UNION ALL
737 SELECT ''EE Non W2 SIT Withheld'' ,
738 ''4'',
739 pqp_us_ff_functions.get_nonw2_bal(''SIT Alien Withheld'',''run'',paa.assignment_action_id,state.jurisdiction_code,paa.tax_unit_id) run_value,
740 null,
741 null
742 FROM
743 pay_assignment_actions paa,
744 pay_us_emp_state_tax_rules_f state,
745 pay_payroll_actions ppa
746 WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = ''TRUE''
747 AND state.assignment_id = paa.assignment_id
748 AND ppa.payroll_action_id = paa.payroll_action_id
749 AND paa.assignment_action_id ='|| l_assignment_action_id||
750 ' AND ppa.effective_date BETWEEN state.effective_start_date AND state.effective_end_date';
751 hr_utility.trace('statement build success');
752 LOOP
753 FETCH cv INTO l_user_reporting_name,l_sub_class,l_run_val
754 , l_tax_type_code, l_jurisdiction_code; */
755
756 if p_fed_bal_status = 'Y' and p_state_bal_status = 'Y' and p_local_bal_status = 'Y' then
757 open csr_ee_tax_rbr;
758 else
759 open csr_ee_tax;
760 end if;
761
762 loop
763
764 if p_fed_bal_status = 'Y' and p_state_bal_status = 'Y' and p_local_bal_status = 'Y' then
765 FETCH csr_ee_tax_rbr INTO l_user_reporting_name,l_sub_class,l_run_val
766 , l_tax_type_code, l_jurisdiction_code;
767 exit when csr_ee_tax_rbr%NOTFOUND;
768 else
769 FETCH csr_ee_tax INTO l_user_reporting_name,l_sub_class,l_run_val
770 , l_tax_type_code, l_jurisdiction_code;
771 exit when csr_ee_tax%NOTFOUND;
772 end if;
773 /*-- Bug#4942114 ends -- */
774 if l_tax_type_code = 'SCHOOL' then
775 open csr_sd_name(substr(l_jurisdiction_code,1,2), substr(l_jurisdiction_code,4));
776 fetch csr_sd_name into l_sd_name;
777 if csr_sd_name%found then
778 l_user_reporting_name := substr(l_user_reporting_name||' '||
779 l_jurisdiction_code||' '||l_sd_name,1,60);
780 end if;
781 close csr_sd_name;
782 end if;
783 -- EXIT WHEN cv%NOTFOUND; /*-- Bug#4942114 -- */
784 if l_asg_flag <> 'Y' THEN
785 if l_index <>0 then
786 l_status :=0;
787 for l_temp_index in g_totals_table.first..g_totals_table.last LOOP
788 if g_totals_table(l_temp_index).attribute5 = l_user_reporting_name and
789 g_totals_table(l_temp_index).gre_name = l_gre_name and
790 g_totals_table(l_temp_index).organization_name = l_org_name and
791 g_totals_table(l_temp_index).location_name = l_location_code then
792
793 hr_utility.trace('testing 1');
794 g_totals_table(l_temp_index).value2 := g_totals_table(l_temp_index).value2 +
795 to_number(l_run_val);
796 l_status := 1;
797 hr_utility.trace('for ee tax...l_index ='||l_index);
798 hr_utility.trace('element name ='||l_user_reporting_name);
799 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
800 hr_utility.trace('gre_name='||l_gre_name);
801 hr_utility.trace('org name='||l_org_name);
802 hr_utility.trace('location='||l_location_code);
803 hr_utility.trace('Toal Cash value ='||to_char(g_totals_table(l_temp_index).value2));
804 hr_utility.trace('Cash Value ='||l_run_val);
805 end if;
806 end loop;
807 end if;
808 if l_status <> 1 or l_index = 0 then
809 hr_utility.trace('testing 6');
810 l_index := l_index + 1;
811 g_totals_table(l_index).gre_name := l_gre_name;
812 g_totals_table(l_index).organization_name := l_org_name;
813 g_totals_table(l_index).location_name := l_location_code;
814 g_totals_table(l_index).tax_unit_id := l_payroll_action_id;
815 g_totals_table(l_index).attribute4 := 'Tax Deductions';
816 g_totals_table(l_index).attribute3 := '1';
817 g_totals_table(l_index).attribute5 := l_user_reporting_name;
818 g_totals_table(l_index).value2 := to_number(l_run_val);
819 g_totals_table(l_index).value3 := NULL;
820 g_totals_table(l_index).attribute1 := 'EE-TAX';
821 g_totals_table(l_index).attribute2 := '2';
822 end if;
823 hr_utility.trace('for ee tax...l_index ='||l_index);
824 hr_utility.trace('element name ='||l_user_reporting_name);
825 hr_utility.trace('payroll action='||to_char(l_payroll_action_id));
826 hr_utility.trace('gre_name='||l_gre_name);
827 hr_utility.trace('org name='||l_org_name);
828 hr_utility.trace('location='||l_location_code);
829 hr_utility.trace('Cash Value ='||l_run_val);
830 else
831 insert into pay_us_rpt_totals
832 (tax_unit_id, gre_name, organization_name, location_name,
836 attribute3,
833 attribute1,
834 value1,
835 attribute2,
837 attribute4,
838 attribute5,
839 value2,
840 organization_id,
841 business_group_id,
842 attribute12)
843 values
844 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
845 'EE-TAX',
846 l_payroll_action_id,
847 '2',
848 '1', --l_sub_class,
849 'Tax Deductions',
850 l_user_reporting_name,
851 l_run_val,
852 l_assignment_action_id,
853 l_person_id,
854 l_full_name);
855 end if;
856 End loop;
857 /*-- Bug#4942114 starts -- */
858 if p_fed_bal_status = 'Y' and p_state_bal_status = 'Y' and p_local_bal_status = 'Y' then
859 close csr_ee_tax_rbr;
860 else
861 close csr_ee_tax;
862 end if;
863 /*-- Bug#4942114 ends -- */
864 exception
865 when others then
866 hr_utility.trace('Error occurred load_ee_tax ...' ||SQLERRM);
867 raise;
868 end load_ee_tax;
869
870 /*-- Bug#4942114 starts -- */
871 /* procedure load_er_tax (l_assignment_action_id number,l_fed_liab_view_name varchar2,l_state_liab_view_name varchar2,l_local_liab_view_name varchar2) is
872 TYPE cv_typ IS REF CURSOR;
873 cv cv_typ; */
874
875 procedure load_er_tax (l_assignment_action_id number, p_fed_liab_bal_status varchar2,p_state_liab_bal_status varchar2,p_local_bal_status varchar2) is
876
877 cursor csr_er_tax_rbr is
878 select user_reporting_name,'1' sub_class,run_val
879 from PAY_US_ASG_RUN_FED_LIAB_RBR_V
880 where assignment_action_id = l_assignment_action_id
881 and ee_or_er_code = 'ER'
882 and database_item_suffix = decode(upper(user_reporting_name),
883 'ER FUTA LIABILITY' ,'_ASG_JD_GRE_RUN' ,
884 '_ASG_GRE_RUN')
885 UNION ALL
886 select user_reporting_name ||' '|| state_name,'2' sub_class,run_val
887 from PAY_US_ASG_RUN_ST_LIAB_RBR_V
888 where assignment_action_id =l_assignment_action_id
889 and ee_or_er_code = 'ER'
890 UNION ALL
891 select user_reporting_name||' '||(decode(state_name,'INVALID',null,state_name))||
892 ' '|| nvl((decode(school_district_name,'INVALID',null, school_district_name)),
893 nvl((decode(county_name,'INVALID',null,county_name)),
894 (decode(city_name,'INVALID',null,city_name))
895 )
896 ),
897 '3' sub_class,run_val
898 from PAY_US_ASG_RUN_LOCAL_TAX_RBR_V
899 where assignment_action_id = l_assignment_action_id
900 and ee_or_er_code = 'ER';
901
902
903 cursor csr_er_tax is
904 select user_reporting_name,'1' sub_class,run_val
905 from PAY_US_FED_LIABILITIES_V
906 where assignment_action_id = l_assignment_action_id
907 and ee_or_er_code = 'ER'
908 UNION ALL
909 select user_reporting_name ||' '|| state_name,'2' sub_class,run_val
910 from PAY_US_STATE_LIABILITIES_V
911 where assignment_action_id =l_assignment_action_id
912 and ee_or_er_code = 'ER'
913 UNION ALL
914 select user_reporting_name||' '||(decode(state_name,'INVALID',null,state_name))||
915 ' '|| nvl((decode(school_district_name,'INVALID',null, school_district_name)),
916 nvl((decode(county_name,'INVALID',null,county_name)),
917 (decode(city_name,'INVALID',null,city_name))
918 )
919 ),
920 '3' sub_class,run_val
921 from PAY_US_LOCAL_TAXES_V
922 where assignment_action_id = l_assignment_action_id
923 and ee_or_er_code = 'ER';
924
925 /*-- Bug#4942114 ends -- */
926 l_user_reporting_name varchar2(60);
927 l_sub_class varchar2(1);
928 l_run_val number(20,2);
929 l_tax_type_code varchar2(30);
930 l_jurisdiction_code varchar2(11);
931 l_sd_name varchar2(30);
932 l_status number :=0;
933 -- lv_sql_query varchar2(3000); -- Bug#4942114
934 BEGIN
935 /*-- Bug#4942114 ends -- */
936 /* hr_utility.trace('view names = '|| l_fed_liab_view_name || l_state_liab_view_name || l_local_liab_view_name);
937
938 lv_sql_query := 'select user_reporting_name,''1'' sub_class,run_val
939 from ' || l_fed_liab_view_name ||
940 ' where assignment_action_id ='|| l_assignment_action_id||
941 ' and ee_or_er_code = ''ER'' ';
942
943 if l_fed_liab_view_name = 'PAY_US_ASG_RUN_FED_LIAB_RBR_V' then
944 lv_sql_query := lv_sql_query || ' and database_item_suffix = decode
945 (
946 upper(user_reporting_name),
947 ''ER FUTA LIABILITY'' ,
948 ''_ASG_JD_GRE_RUN'' ,
949 ''_ASG_GRE_RUN''
950 ) ' ;
951 end if;
952
953 lv_sql_query := lv_sql_query || ' UNION ALL
954 select user_reporting_name ||'' ''|| state_name,''2'' sub_class,run_val
955 from '||l_state_liab_view_name||
956 ' where assignment_action_id ='||l_assignment_action_id||
957 ' and ee_or_er_code = ''ER''
958 UNION ALL
962 (decode(county_name,''INVALID'',null,county_name)),
959 select user_reporting_name||'' ''||(decode(state_name,''INVALID'',null,state_name))||
960 '' ''|| nvl((decode(school_district_name,''INVALID'',null, school_district_name)),
961 nvl(
963 (decode(city_name,''INVALID'',null,city_name))
964 )),''3'' sub_class,run_val
965 from '|| l_local_liab_view_name||
966 ' where assignment_action_id = '||l_assignment_action_id||
967 ' and ee_or_er_code = ''ER''';
968
969 OPEN cv FOR lv_sql_query;
970
971 hr_utility.trace('statement build success');
972 LOOP
973 fetch cv into l_user_reporting_name, l_sub_class, l_run_val;
974 hr_utility.trace('assignment_Action_id in load_er_tax ='||to_char(l_assignment_action_id));
975 EXIT WHEN cv%NOTFOUND; */
976
977 if p_fed_liab_bal_status = 'Y' and p_state_liab_bal_status = 'Y' and p_local_bal_status = 'Y' then
978 open csr_er_tax_rbr;
979 else
980 open csr_er_tax;
981 end if;
982
983 loop
984
985 if p_fed_liab_bal_status = 'Y' and p_state_liab_bal_status = 'Y' and p_local_bal_status = 'Y' then
986 fetch csr_er_tax_rbr into l_user_reporting_name, l_sub_class, l_run_val;
987 EXIT WHEN csr_er_tax_rbr%NOTFOUND;
988 else
989 fetch csr_er_tax into l_user_reporting_name, l_sub_class, l_run_val;
990 EXIT WHEN csr_er_tax%NOTFOUND;
991 end if;
992 hr_utility.trace('assignment_Action_id in load_er_tax ='||to_char(l_assignment_action_id));
993 /*-- Bug#4942114 ends -- */
994
995
996 if l_asg_flag <> 'Y' THEN
997 if l_index <>0 then
998 l_status :=0;
999 for l_temp_index in g_totals_table.first..g_totals_table.last LOOP
1000 if g_totals_table(l_temp_index).attribute5 = l_user_reporting_name and
1001 g_totals_table(l_temp_index).gre_name = l_gre_name and
1002 g_totals_table(l_temp_index).organization_name = l_org_name and
1003 g_totals_table(l_temp_index).location_name = l_location_code then
1004
1005 hr_utility.trace('testing 1');
1006 g_totals_table(l_temp_index).value2 := g_totals_table(l_temp_index).value2 +
1007 to_number(l_run_val);
1008 l_status := 1;
1009 hr_utility.trace('for er tax...l_index ='||l_index);
1010 hr_utility.trace('element name ='||l_user_reporting_name);
1011 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
1012 hr_utility.trace('gre_name='||l_gre_name);
1013 hr_utility.trace('org name='||l_org_name);
1014 hr_utility.trace('location='||l_location_code);
1015 hr_utility.trace('Toal Cash value ='||to_char(g_totals_table(l_temp_index).value2));
1016 hr_utility.trace('Cash Value ='||l_run_val);
1017
1018 end if;
1019 end loop;
1020 end if;
1021 if l_status <> 1 or l_index = 0 then
1022 hr_utility.trace('testing 6');
1023 l_index := l_index + 1;
1024 g_totals_table(l_index).gre_name := l_gre_name;
1025 g_totals_table(l_index).organization_name := l_org_name;
1026 g_totals_table(l_index).location_name := l_location_code;
1027 g_totals_table(l_index).tax_unit_id := l_payroll_action_id;
1028 g_totals_table(l_index).attribute4 := 'Employer Taxes';
1029 g_totals_table(l_index).attribute3 := '1';
1030 g_totals_table(l_index).attribute5 := l_user_reporting_name;
1031 g_totals_table(l_index).value2 := to_number(l_run_val);
1032 g_totals_table(l_index).value3 := NULL;
1033 g_totals_table(l_index).attribute1 := 'ER-TAX';
1034 g_totals_table(l_index).attribute2 := '6';
1035 hr_utility.trace('for er tax...l_index ='||l_index);
1036 hr_utility.trace('element name ='||l_user_reporting_name);
1037 hr_utility.trace('payroll action='||to_char(l_payroll_action_id));
1038 hr_utility.trace('gre_name='||l_gre_name);
1039 hr_utility.trace('org name='||l_org_name);
1040 hr_utility.trace('location='||l_location_code);
1041 hr_utility.trace('Cash Value ='||l_run_val);
1042 end if;
1043 else
1044 insert into pay_us_rpt_totals
1045 ( tax_unit_id, gre_name, organization_name, location_name,
1046 attribute1,
1047 value1,
1048 attribute2,
1049 attribute3,
1050 attribute4,
1051 attribute5,
1052 value2,
1053 organization_id,
1054 business_group_id,
1055 attribute12)
1056 values
1057 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
1058 'ER-TAX',
1059 l_payroll_action_id,
1060 '6',
1061 '1', --l_sub_class,
1062 'Employer Taxes',
1063 l_user_reporting_name,
1064 l_run_val,
1065 l_assignment_action_id,
1066 l_person_id,
1067 l_full_name);
1068 end if;
1069 end loop;
1070 /*-- Bug#4942114 ends -- */
1071 if p_fed_liab_bal_status = 'Y' and p_state_liab_bal_status = 'Y' and p_local_bal_status = 'Y' then
1072 close csr_er_tax_rbr;
1073 else
1074 close csr_er_tax;
1075 end if;
1076
1077 /*-- Bug#4942114 ends -- */
1078 hr_utility.trace('Leaving load_er_tax');
1079 exception
1080 when others then
1081 hr_utility.trace('Error occurred load_er_tax ...' ||SQLERRM);
1082 raise;
1086 /* procedure load_ee_credit (l_assignment_action_id number,l_fed_view_name varchar2,l_state_view_name varchar2) is
1083 end load_er_tax;
1084 --------------------------------------------------------------------------------
1085 /*-- Bug#4942114 starts -- */
1087 TYPE cv_typ IS REF CURSOR;
1088 cv cv_typ; */
1089
1090 procedure load_ee_credit (l_assignment_action_id number,p_fed_bal_status varchar2, p_state_bal_status varchar2) is
1091
1092 cursor csr_ee_credit_rbr is
1093 select user_reporting_name,run_val
1094 from PAY_US_ASG_RUN_FED_TAX_RBR_V
1095 where assignment_action_id = l_assignment_action_id
1096 and ee_or_er_code = 'EE'
1097 and tax_type_code = 'EIC'
1098 UNION ALL
1099 select user_reporting_name ,run_val
1100 from PAY_US_ASG_RUN_STATE_TAX_RBR_V
1101 where assignment_action_id =l_assignment_action_id
1102 and ee_or_er_code = 'EE'
1103 and tax_type_code = 'STEIC';
1104
1105 cursor csr_ee_credit is
1106 select user_reporting_name,run_val
1107 from PAY_US_FED_TAXES_V
1108 where assignment_action_id = l_assignment_action_id
1109 and ee_or_er_code = 'EE'
1110 and tax_type_code = 'EIC'
1111 UNION ALL
1112 select user_reporting_name ,run_val
1113 from PAY_US_STATE_TAXES_V
1114 where assignment_action_id =l_assignment_action_id
1115 and ee_or_er_code = 'EE'
1116 and tax_type_code = 'STEIC';
1117
1118 /*-- Bug#4942114 ends -- */
1119
1120 l_state_bal_status varchar2(1);
1121 l_user_reporting_name varchar2(60);
1122 l_run_val number(20,2);
1123 l_status number :=0;
1124 BEGIN
1125 /*-- Bug#4942114 starts -- */
1126 /* hr_utility.trace('view names = '|| l_fed_view_name || l_state_view_name);
1127 OPEN cv FOR
1128 'select user_reporting_name,run_val
1129 from '||l_fed_view_name||
1130 ' where assignment_action_id ='|| l_assignment_action_id||
1131 ' and ee_or_er_code = ''EE''
1132 and tax_type_code = ''EIC'' UNION ALL
1133 select user_reporting_name ,run_val
1134 from '||l_state_view_name||
1135 ' where assignment_action_id ='||l_assignment_action_id||
1136 ' and ee_or_er_code = ''EE''
1137 and tax_type_code = ''STEIC''';
1138 hr_utility.trace('statement build success');
1139 loop
1140 fetch cv into l_user_reporting_name, l_run_val;
1141 exit when cv%notfound; */
1142
1143 IF p_fed_bal_status = 'Y' and p_state_bal_status = 'Y' then
1144 open csr_ee_credit_rbr;
1145 else
1146 open csr_ee_credit;
1147 end if;
1148
1149
1150 loop
1151 IF p_fed_bal_status = 'Y' and p_state_bal_status = 'Y' then
1152 fetch csr_ee_credit_rbr into l_user_reporting_name, l_run_val;
1153 exit when csr_ee_credit_rbr%notfound;
1154 else
1155 fetch csr_ee_credit into l_user_reporting_name, l_run_val;
1156 exit when csr_ee_credit%notfound;
1157 END IF;
1158 /*-- Bug#4942114 ends -- */
1159
1160 if l_asg_flag <> 'Y' THEN
1161 if l_index <>0 then
1162 l_status :=0;
1163 for l_temp_index in g_totals_table.first..g_totals_table.last LOOP
1164 if g_totals_table(l_temp_index).attribute5 = l_user_reporting_name and
1165 g_totals_table(l_temp_index).gre_name = l_gre_name and
1166 g_totals_table(l_temp_index).organization_name = l_org_name and
1167 g_totals_table(l_temp_index).location_name = l_location_code then
1168
1169
1170 g_totals_table(l_temp_index).value2 := g_totals_table(l_temp_index).value2 -
1171 to_number(l_run_val);
1172 l_status := 1;
1173 hr_utility.trace('for er credit...l_index ='||l_index);
1174 hr_utility.trace('element name ='||l_user_reporting_name);
1175 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
1176 hr_utility.trace('gre_name='||l_gre_name);
1177 hr_utility.trace('org name='||l_org_name);
1178 hr_utility.trace('location='||l_location_code);
1179 hr_utility.trace('Toal Cash value ='||to_char(g_totals_table(l_temp_index).value2));
1180 hr_utility.trace('Cash Value ='||l_run_val);
1181
1182 end if;
1183 end loop;
1184 end if;
1185 if l_status <> 1 or l_index = 0 then
1186 hr_utility.trace('testing 6');
1187 l_index := l_index + 1;
1188 g_totals_table(l_index).gre_name := l_gre_name;
1189 g_totals_table(l_index).organization_name := l_org_name;
1190 g_totals_table(l_index).location_name := l_location_code;
1191 g_totals_table(l_index).tax_unit_id := l_payroll_action_id;
1192 g_totals_table(l_index).attribute4 := 'Tax Credits';
1193 g_totals_table(l_index).attribute3 := '1';
1194 g_totals_table(l_index).attribute5 := l_user_reporting_name;
1195 g_totals_table(l_index).value2 := -1*to_number(l_run_val);
1196 g_totals_table(l_index).value3 := NULL;
1197 g_totals_table(l_index).attribute1 := 'EE-CREDIT';
1198 g_totals_table(l_index).attribute2 := '3';
1199 end if;
1200 hr_utility.trace('for er credit...l_index ='||l_index);
1201 hr_utility.trace('element name ='||l_user_reporting_name);
1205 hr_utility.trace('location='||l_location_code);
1202 hr_utility.trace('payroll action='||to_char(l_payroll_action_id));
1203 hr_utility.trace('gre_name='||l_gre_name);
1204 hr_utility.trace('org name='||l_org_name);
1206 hr_utility.trace('Cash Value ='||l_run_val);
1207 else
1208 insert into pay_us_rpt_totals
1209 (tax_unit_id, gre_name, organization_name, location_name,
1210 attribute1,
1211 value1,
1212 attribute2,
1213 attribute3,
1214 attribute4,
1215 attribute5,
1216 value2,
1217 organization_id,
1218 business_group_id,
1219 attribute12)
1220 values
1221 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
1222 'EE-CREDIT',
1223 l_payroll_action_id,
1224 '3',
1225 '1',
1226 'Tax Credits',
1227 l_user_reporting_name,
1228 -1*l_run_val,
1229 l_assignment_action_id,
1230 l_person_id,
1231 l_full_name);
1232 end if;
1233 end loop;
1234
1235 /*-- Bug#4942114 starts -- */
1236 -- close cv;
1237 IF p_fed_bal_status = 'Y' and p_state_bal_status = 'Y' then
1238 close csr_ee_credit_rbr;
1239 else
1240 close csr_ee_credit;
1241 end if;
1242 /*-- Bug#4942114 ends -- */
1243 exception
1244 when others then
1245 hr_utility.trace('Error occurred load_ee_credit ...' ||SQLERRM);
1246 raise;
1247 end load_ee_credit;
1248 ---------------------------------------------------------------------------------------------
1249 /*-- Bug#4942114 starts -- */
1250 /* procedure load_er_credit (l_assignment_action_id number,l_futa_where varchar2,l_futa_from varchar2) is
1251 TYPE cv_typ IS REF CURSOR;
1252 cv cv_typ; */
1253
1254 procedure load_er_credit (l_assignment_action_id number,p_futa_status_count number,p_futa_def_bal_id number) is
1255
1256 cursor csr_er_credit_rbr is
1257 select distinct 'ER Tax Credits' classification_name ,'FUTA CREDIT' balance_name ,
1258 pet.element_name element_name, prb.jurisdiction_code jurisdiction_code
1259 from pay_element_types_f pet,
1260 pay_run_balances prb
1261 where l_effective_date between pet.effective_start_date and pet.effective_end_date
1262 and pet.element_name ='FUTA CREDIT'
1263 and prb.defined_balance_id = p_futa_def_bal_id
1264 AND prb.assignment_action_id = l_assignment_action_id;
1265
1266 cursor csr_er_credit is
1267 select distinct 'ER Tax Credits' classification_name ,'FUTA CREDIT' balance_name ,
1268 pet.element_name element_name, prr.jurisdiction_code jurisdiction_code
1269 from pay_element_types_f pet,
1270 pay_run_results prr
1271 where l_effective_date between pet.effective_start_date and pet.effective_end_date
1272 and pet.element_name ='FUTA CREDIT'
1273 and prr.status in ('P','PA')
1274 and pet.element_type_id = prr.element_type_id
1275 and prr.assignment_action_id = l_assignment_action_id;
1276 /*-- Bug#4942114 ends -- */
1277
1278 l_classification_name varchar2(60);
1279 l_balance_name varchar2(60);
1280 l_element_name varchar2(60);
1281 l_run_val number(20,2);
1282 l_status number :=0;
1283 l_jurisdiction_code varchar2(15); -- Bug 4443935
1284 l_tname varchar2(60);
1285 begin
1286
1287 hr_utility.trace('Entered load_csr_er_credit');
1288
1289 /*-- Bug#4942114 starts -- */
1290 /* if (instr(l_futa_from,'prb') > 0) then
1291 l_tname := 'prb';
1292 else
1293 l_tname := 'prr';
1294 end if;
1295
1296 -- ||l_tname||'.jurisdiction_code jurisdiction_code
1297
1298 --hr_utility.trace('l_futa_where ='||l_futa_where);
1299 --hr_utility.trace('l_futa_from ='||l_futa_from);
1300 --hr_utility.trace('l_tname ='||l_tname);
1301 -- Bug 5409416 : Removed prr.jurisdiction_code with
1302 OPEN cv FOR
1303 ' select distinct'||'''ER Tax Credits'''||' classification_name ,'||'''FUTA CREDIT'''||' balance_name ,
1304 pet.element_name element_name,'|| l_tname||'.jurisdiction_code jurisdiction_code
1305 from pay_element_types_f pet,'
1306 || l_futa_from ||
1307 ' where '''||l_effective_date||''' between pet.effective_start_date and pet.effective_end_date
1308 and pet.element_name ='||'''FUTA CREDIT'''||
1309 ' and '||l_futa_where;
1310
1311 hr_utility.trace('statement build success');
1312 loop
1313 fetch cv into l_classification_name,
1314 l_balance_name,
1315 l_element_name,
1316 l_jurisdiction_code;
1317
1318 hr_utility.trace('assignment_Action_id in load_er_credit ='||to_char(l_assignment_action_id));
1319 hr_utility.trace('Number of ER CREDIT Records fetched = '||to_char(cv%ROWCOUNT));
1320 exit when cv%notfound; */
1321
1322 if p_futa_status_count = 1 then
1323 open csr_er_credit_rbr;
1324 else
1325 open csr_er_credit;
1326 end if;
1327
1328 loop
1329 if p_futa_status_count = 1 then
1330 fetch csr_er_credit_rbr into l_classification_name,
1331 l_balance_name,
1332 l_element_name,
1333 l_jurisdiction_code;
1334 hr_utility.trace('assignment_Action_id in load_er_credit ='||to_char(l_assignment_action_id));
1338 fetch csr_er_credit into l_classification_name,
1335 hr_utility.trace('Number of ER CREDIT Records fetched = '||to_char(csr_er_credit_rbr%ROWCOUNT));
1336 exit when csr_er_credit_rbr%notfound;
1337 else
1339 l_balance_name,
1340 l_element_name,
1341 l_jurisdiction_code;
1342 hr_utility.trace('assignment_Action_id in load_er_credit ='||to_char(l_assignment_action_id));
1343 hr_utility.trace('Number of ER CREDIT Records fetched = '||to_char(csr_er_credit%ROWCOUNT));
1344 exit when csr_er_credit%notfound;
1345 end if;
1346
1347 /*-- Bug#4942114 ends -- */
1348 l_bal_value := pay_us_taxbal_view_pkg.us_named_balance('FUTA CREDIT',
1349 'ASG_JD_GRE_RUN',
1350 l_assignment_action_id,
1351 null,
1352 null,
1353 'GRE',
1354 l_tax_unit_id,
1355 l_business_group_id,
1356 l_jurisdiction_code); -- 4443935
1357 hr_utility.trace('l_bal_value : '|| l_bal_value );
1358 if l_asg_flag <> 'Y' THEN
1359 if l_index <>0 then
1360 l_status :=0;
1361 for l_temp_index in g_totals_table.first..g_totals_table.last LOOP
1362 if g_totals_table(l_temp_index).attribute5 = l_element_name and
1363 g_totals_table(l_temp_index).gre_name = l_gre_name and
1364 g_totals_table(l_temp_index).organization_name = l_org_name and
1365 g_totals_table(l_temp_index).location_name = l_location_code then
1366
1367 hr_utility.trace('testing 1');
1368 g_totals_table(l_temp_index).value2 := g_totals_table(l_temp_index).value2 +
1369 to_number(l_bal_value);
1370 l_status := 1;
1371 hr_utility.trace('bulk elename ='|| l_element_name);
1372 hr_utility.trace('bulk cashval='|| l_bal_value);
1373 hr_utility.trace('bulk totalcashval='|| g_totals_table(l_temp_index).value2);
1374 end if;
1375 end loop;
1376 end if;
1377 if l_status <> 1 or l_index = 0 then
1378 hr_utility.trace('testing 6');
1379 l_index := l_index + 1;
1380 g_totals_table(l_index).gre_name := l_gre_name;
1381 g_totals_table(l_index).organization_name := l_org_name;
1382 g_totals_table(l_index).location_name := l_location_code;
1383 g_totals_table(l_index).tax_unit_id := l_payroll_action_id;
1384 g_totals_table(l_index).attribute4 := 'Employer Tax Credits';
1385 g_totals_table(l_index).attribute3 := '1';
1386 g_totals_table(l_index).attribute5 := l_element_name;
1387 g_totals_table(l_index).value2 := to_number(l_bal_value);
1388 g_totals_table(l_index).value3 := NULL;
1389 g_totals_table(l_index).attribute1 := 'ER-CREDIT';
1390 g_totals_table(l_index).attribute2 := '7';
1391 end if;
1392 hr_utility.trace('bulk elename ='|| l_element_name);
1393 hr_utility.trace('bulk cashval='|| l_bal_value);
1394 hr_utility.trace('gre name='||l_gre_name);
1395 hr_utility.trace('org name='||l_org_name);
1396 hr_utility.trace('loc name='||l_location_code);
1397 else
1398 insert into pay_us_rpt_totals
1399 (tax_unit_id, gre_name, organization_name, location_name,
1400 attribute1,
1401 value1,
1402 attribute2,
1403 attribute3,
1404 attribute4,
1405 attribute5,
1406 value2,
1407 organization_id,
1408 business_group_id,
1409 attribute12)
1410 values
1411 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
1412 'ER-CREDIT',
1413 l_payroll_action_id,
1414 '7',
1415 '1',
1416 'Employer Tax Credits',
1417 l_element_name,
1418 l_bal_value,
1419 l_assignment_action_id,
1420 l_person_id,
1421 l_full_name);
1422 end if;
1423 end loop;
1424 /*-- Bug#4942114 starts -- */
1425 if p_futa_status_count = 1 then
1426 close csr_er_credit_rbr;
1427 else
1428 close csr_er_credit;
1429 end if;
1430 /*-- Bug#4942114 ends -- */
1431 end load_er_credit;
1432 --------------------------------------------------------------------------------------------
1433
1434 /*-- Bug#4942114 starts -- */
1435 /* procedure load_er_liab (l_business_group_id number,
1436 l_assignment_action_id number,
1437 l_er_liab_where varchar2,
1438 l_er_liab_from varchar2) is
1439 TYPE cv_typ IS REF CURSOR;
1440 cv cv_typ; */
1441
1442 procedure load_er_liab (l_business_group_id number, l_assignment_action_id number,p_er_liab_status varchar2) is
1443
1444 cursor csr_er_liab_rbr is
1445 select distinct pec.classification_name classification_name,
1446 pbt.balance_name balance_name,
1447 pet.element_name element_name
1448 from pay_balance_types pbt,
1449 pay_element_types_f pet,
1450 pay_element_classifications pec,
1451 pay_run_balances prb,
1452 pay_defined_balances pdb,
1453 pay_balance_dimensions pbd
1454 where pec.classification_name ='Employer Liabilities'
1458 and pet.element_type_id >= 0
1455 and pec.legislation_code ='US'
1456 and pet.classification_id = pec.classification_id
1457 and pet.business_group_id = l_business_group_id
1459 and l_effective_date between pet.effective_start_date
1460 and pet.effective_end_date
1461 and pet.element_information10 = pbt.balance_type_id
1462 and pbt.business_group_id = l_business_group_id
1463 and prb.defined_balance_id = pdb.defined_balance_id
1464 and (pdb.business_group_id = l_business_group_id
1465 or pbd.legislation_code ='US')
1466 and pdb.balance_type_id = pbt.balance_type_id
1467 and pdb.balance_dimension_id = pbd.balance_dimension_id
1468 and pbd.legislation_code = 'US'
1469 and pbd.database_item_suffix = '_ASG_GRE_RUN'
1470 and prb.assignment_action_id = l_assignment_action_id;
1471
1472 cursor csr_er_liab is
1473 select distinct pec.classification_name classification_name,
1474 pbt.balance_name balance_name,
1475 pet.element_name element_name
1476 from pay_balance_types pbt,
1477 pay_element_types_f pet,
1478 pay_element_classifications pec,
1479 pay_run_results prr
1480 where pec.classification_name ='Employer Liabilities'
1481 and pec.legislation_code ='US'
1482 and pet.classification_id = pec.classification_id
1483 and pet.business_group_id = l_business_group_id
1484 and pet.element_type_id >= 0
1485 and l_effective_date between pet.effective_start_date
1486 and pet.effective_end_date
1487 and pet.element_information10 = pbt.balance_type_id
1488 and pbt.business_group_id =l_business_group_id
1489 and prr.element_type_id +0 = pet.element_type_id
1490 and prr.status in ('P','PA')
1491 and prr.assignment_action_id = l_assignment_action_id;
1492 /*-- Bug#4942114 ends -- */
1493 l_classification_name varchar2(60);
1494 l_balance_name varchar2(60);
1495 l_element_name varchar2(60);
1496 l_run_val number(20,2);
1497 l_status number :=0;
1498 BEGIN
1499 hr_utility.trace('entered er_liab');
1500
1501 /*-- Bug#4942114 starts -- */
1502 /*open cv FOR
1503 'select distinct pec.classification_name classification_name,
1504 pbt.balance_name balance_name,
1505 pet.element_name element_name
1506 from
1507 pay_balance_types pbt,
1508 pay_element_types_f pet,
1509 pay_element_classifications pec,'
1510 || l_er_liab_from||
1511 ' where pec.classification_name ='||'''Employer Liabilities'''||
1512 ' and pec.legislation_code ='||'''US'''||
1513 ' and pet.classification_id = pec.classification_id
1514 and pet.business_group_id = '||l_business_group_id||
1515 ' and pet.element_type_id >= 0 -- Bug 3369218: Added to enforce index to
1516 and '''|| l_effective_date||''' between pet.effective_start_date -- remove FTS on pay_element_types_f
1517 and pet.effective_end_date
1518 and pet.element_information10 = pbt.balance_type_id
1519 and pbt.business_group_id ='|| l_business_group_id ||
1520 ' and '||l_er_liab_where;
1521 loop
1522 fetch cv into l_classification_name,
1523 l_balance_name,
1524 l_element_name;
1525 hr_utility.trace('assignment_Action_id in load_er_liab ='||to_char(l_assignment_action_id));
1526 hr_utility.trace('Number of ER LIAB Records fetched = '||to_char(cv%ROWCOUNT));
1527 exit when cv%notfound; */
1528
1529 if p_er_liab_status = 'Y' then
1530 open csr_er_liab_rbr;
1531 else
1532 open csr_er_liab;
1533 end if;
1534
1535 loop
1536
1537 if p_er_liab_status = 'Y' then
1538 fetch csr_er_liab_rbr into l_classification_name,
1539 l_balance_name,
1540 l_element_name;
1541 hr_utility.trace('assignment_Action_id in load_er_liab ='||to_char(l_assignment_action_id));
1542 hr_utility.trace('Number of ER LIAB Records fetched = '||to_char(csr_er_liab_rbr%ROWCOUNT));
1543 exit when csr_er_liab_rbr%notfound;
1544 else
1545 fetch csr_er_liab into l_classification_name,
1546 l_balance_name,
1547 l_element_name;
1548 hr_utility.trace('assignment_Action_id in load_er_liab ='||to_char(l_assignment_action_id));
1549 hr_utility.trace('Number of ER LIAB Records fetched = '||to_char(csr_er_liab%ROWCOUNT));
1550 exit when csr_er_liab%notfound;
1551 end if;
1552 /*-- Bug#4942114 ends -- */
1553 l_bal_value := pay_us_taxbal_view_pkg.us_named_balance(upper(l_balance_name),
1554 'ASG_GRE_RUN',
1555 l_assignment_action_id,
1556 null,
1557 null,
1558 'GRE',
1559 l_tax_unit_id,
1560 l_business_group_id,
1561 null);
1562 if l_asg_flag <> 'Y' THEN
1563 if l_index <>0 then
1564 l_status :=0;
1568 g_totals_table(l_temp_index).organization_name = l_org_name and
1565 for l_temp_index in g_totals_table.first..g_totals_table.last LOOP
1566 if g_totals_table(l_temp_index).attribute5 = l_element_name and
1567 g_totals_table(l_temp_index).gre_name = l_gre_name and
1569 g_totals_table(l_temp_index).location_name = l_location_code then
1570
1571 hr_utility.trace('testing 1');
1572 g_totals_table(l_temp_index).value2 := g_totals_table(l_temp_index).value2 +
1573 to_number(l_bal_value);
1574 l_status := 1;
1575 hr_utility.trace('for er liab...l_index ='||l_index);
1576 hr_utility.trace('element name ='||l_element_name);
1577 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
1578 hr_utility.trace('gre_name='||l_gre_name);
1579 hr_utility.trace('org name='||l_org_name);
1580 hr_utility.trace('location='||l_location_code);
1581 hr_utility.trace('Toal Cash value ='||to_char(g_totals_table(l_temp_index).value2));
1582 hr_utility.trace('Cash Value ='||l_bal_value);
1583 end if;
1584 end loop;
1585 end if;
1586 if l_status <> 1 or l_index = 0 then
1587 hr_utility.trace('testing 6');
1588 l_index := l_index + 1;
1589 g_totals_table(l_index).gre_name := l_gre_name;
1590 g_totals_table(l_index).organization_name := l_org_name;
1591 g_totals_table(l_index).location_name := l_location_code;
1592 g_totals_table(l_index).tax_unit_id := l_payroll_action_id;
1593 g_totals_table(l_index).attribute4 := l_classification_name;
1594 g_totals_table(l_index).attribute3 := '1';
1595 g_totals_table(l_index).attribute5 := l_element_name;
1596 g_totals_table(l_index).value2 := to_number(l_bal_value);
1597 g_totals_table(l_index).value3 := NULL;
1598 g_totals_table(l_index).attribute1 := 'ER-LIAB';
1599 g_totals_table(l_index).attribute2 := '5';
1600 end if;
1601 hr_utility.trace('for er liab...l_index ='||l_index);
1602 hr_utility.trace('element name ='||l_element_name);
1603 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
1604 hr_utility.trace('gre_name='||l_gre_name);
1605 hr_utility.trace('org name='||l_org_name);
1606 hr_utility.trace('location='||l_location_code);
1607 hr_utility.trace('Cash Value ='||l_bal_value);
1608 else
1609 insert into pay_us_rpt_totals
1610 (tax_unit_id, gre_name, organization_name, location_name,
1611 attribute1,
1612 value1,
1613 attribute2,
1614 attribute3,
1615 attribute4,
1616 attribute5,
1617 value2,
1618 organization_id,
1619 business_group_id,
1620 attribute12)
1621 values
1622 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
1623 'ER-LIAB',
1624 l_payroll_action_id,
1625 '5',
1626 '1',
1627 l_classification_name,
1628 l_element_name,
1629 l_bal_value,
1630 l_assignment_action_id,
1631 l_person_id,
1632 l_full_name);
1633 end if;
1634 end loop;
1635 /*-- Bug#4942114 starts -- */
1636 if p_er_liab_status = 'Y' then
1637 close csr_er_liab_rbr;
1638 else
1639 close csr_er_liab;
1640 end if;
1641 /*-- Bug#4942114 ends -- */
1642 exception
1643 when others then
1644 hr_utility.trace('Error occurred load_er_liab ...' ||SQLERRM);
1645 raise;
1646 end load_er_liab;
1647
1648
1649
1650
1651 procedure load_mesg_line (l_assignment_action_id number) is
1652
1653 l_found varchar2(1);
1654 l_ppp_pre_payment_id number(11);
1655 l_dummy_var varchar2(1);
1656
1657 -- #1937448
1658
1659 l_full_name per_all_people_f.full_name%TYPE;
1660 l_payment_method_name pay_org_payment_methods_f.org_payment_method_name%TYPE;
1661 l_account_type fnd_common_lookups.meaning%TYPE;
1662 l_account_number pay_external_accounts.segment3%TYPE;
1663 l_routing_number pay_external_accounts.segment4%TYPE;
1664
1665 cursor person_details is -- #1937448
1666 select ppf.full_name
1667 from per_all_people_f ppf
1668 where ppf.person_id = l_person_id
1669 and l_effective_date
1670 between ppf.effective_start_date and ppf.effective_end_date ;
1671
1672
1673 cursor nacha_details is -- #1937448
1674 select popm.org_payment_method_name Payment_Method_Name,
1675 fcl.meaning,
1676 decode(pea.segment3,null,null,'*****'||substr(pea.segment3,-4,4)),
1677 substr(ltrim(pea.segment4),1,9)
1678 from fnd_common_lookups fcl,
1679 pay_external_accounts pea,
1680 pay_personal_payment_methods_f pppm,
1681 pay_org_payment_methods_f popm,
1682 pay_payment_types ppt,
1683 pay_pre_payments ppp
1684 where fcl.application_id(+) = 800
1685 and fcl.lookup_type(+) = 'US_ACCOUNT_TYPE'
1686 and pea.segment2 = fcl.lookup_code(+)
1687 and pea.external_account_id(+) = pppm.external_account_id
1688 and pppm.personal_payment_method_id(+) = ppp.personal_payment_method_id
1692 between popm.effective_start_date and popm.effective_end_date
1689 and popm.org_payment_method_id = ppp.org_payment_method_id
1690 and ppt.payment_type_name in ('NACHA','Check')
1691 and l_effective_date
1693 and popm.payment_type_id = ppt.payment_type_id
1694 and l_effective_date
1695 between
1696 nvl(pppm.effective_start_date, l_effective_date )
1697 and
1698 nvl(pppm.effective_end_date, l_effective_date)
1699 and ppp.pre_payment_id = l_ppp_pre_payment_id;
1700
1701 cursor ppp_action is
1702 select ppp.pre_payment_id pre_payment_id
1703 from pay_pre_payments ppp,
1704 pay_payroll_actions ppa_ppp,
1705 pay_assignment_actions paa_ppp,
1706 pay_action_interlocks pai
1707 where pai.locked_action_id = l_assignment_action_id
1708 and paa_ppp.assignment_action_id = pai.locking_action_id
1709 and paa_ppp.action_status = 'C'
1710 and ppa_ppp.payroll_action_id = paa_ppp.payroll_action_id
1711 and ppa_ppp.action_type in ('U','P')
1712 and ppa_ppp.action_status = 'C'
1713 and ppp.assignment_action_id = paa_ppp.assignment_action_id;
1714
1715
1716
1717 cursor chk_ppp ( l_ppp_pre_payment_id number) is
1718 select '1' found
1719 from pay_payroll_actions ppa_chk,
1720 pay_assignment_actions paa_chk
1721 where paa_chk.pre_payment_id = l_ppp_pre_payment_id
1722 and ppa_chk.payroll_action_id = paa_chk.payroll_action_id
1723 and ppa_chk.action_type in ('H','M','E')
1724 and ppa_chk.action_status = 'C';
1725
1726
1727 begin
1728 -- initialize the variables
1729 l_full_name := null ;
1730 l_payment_method_name := null ;
1731 l_account_type := null ;
1732 l_account_number := null ;
1733 l_routing_number := null ;
1734 l_found := null ;
1735 --
1736 open person_details;
1737 fetch person_details into l_full_name;
1738 close person_details;
1739
1740 open ppp_action ;
1741 loop
1742 fetch ppp_action into l_ppp_pre_payment_id;
1743 hr_utility.trace('Number of PPP_ACTION Records fetched = '||to_char(ppp_action%ROWCOUNT));
1744 exit when ppp_action%notfound;
1745 /* ppp_action found */
1746
1747 open chk_ppp(l_ppp_pre_payment_id);
1748 fetch chk_ppp into l_found;
1749 hr_utility.trace('Number of CHK_PPP Records fetched = '||to_char(chk_ppp%ROWCOUNT));
1750 if chk_ppp%notfound then
1751
1752 open nacha_details;
1753 fetch nacha_details into l_payment_method_name, l_account_type,
1754 l_account_number, l_routing_number ;
1755 close nacha_details;
1756
1757 BEGIN
1758
1759 SELECT 'X'
1760 INTO l_dummy_var
1761 from pay_us_rpt_totals
1762 where location_id = l_ppp_pre_payment_id
1763 and tax_unit_id = t_payroll_action_id
1764 and attribute4 = 'Unpaid Payments' ;
1765
1766 EXCEPTION
1767
1768 WHEN NO_DATA_FOUND THEN
1769
1770
1771 insert into pay_us_rpt_totals
1772 (tax_unit_id, gre_name, organization_name, location_name,
1773 attribute1,
1774 value1,
1775 attribute2,
1776 attribute3,
1777 attribute4,
1778 attribute5,
1779 attribute6,
1780 attribute7,
1781 attribute8,
1782 attribute9,
1783 attribute10,
1784 attribute11,
1785 value2,
1786 organization_id,
1787 location_id)
1788 values
1789 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
1790 'MESG-LINE',
1791 l_payroll_action_id,
1792 '10',
1793 '2',
1794 'Unpaid Payments',
1795 'Incomplete Payments',
1796 l_full_name,
1797 l_assignment_number,
1798 l_payment_method_name,
1799 l_account_type,
1800 l_account_number,
1801 l_routing_number,
1802 1,
1803 l_assignment_action_id,
1804 l_ppp_pre_payment_id);
1805
1806 END;
1807
1808 end if; /* chk_ppp%notfound */
1809 close chk_ppp;
1810 end loop;
1811
1812 if ppp_action%ROWCOUNT = 0 then
1813
1814 BEGIN
1815 /* bug 3774591 first change */
1816 select 'X'
1817 into l_dummy_var
1818 from pay_payroll_actions ppa_ppp,
1819 pay_assignment_actions paa_ppp,
1823 and paa_ppp.action_status = 'C'
1820 pay_action_interlocks pai
1821 where pai.locked_action_id = l_assignment_action_id
1822 and paa_ppp.assignment_action_id = pai.locking_action_id
1824 and ppa_ppp.payroll_action_id = paa_ppp.payroll_action_id
1825 and ppa_ppp.action_type in ('U','P')
1826 and ppa_ppp.action_status = 'C'
1827 and rownum=1; -- Bug 5021468
1828
1829 EXCEPTION
1830
1831 WHEN NO_DATA_FOUND THEN
1832
1833 insert into pay_us_rpt_totals
1834 (tax_unit_id, gre_name, organization_name, location_name,
1835 attribute1,
1836 value1,
1837 attribute2,
1838 attribute3,
1839 attribute4,
1840 attribute5,
1841 attribute6,
1842 attribute7,
1843 value2,
1844 organization_id)
1845 values
1846 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
1847 'MESG-LINE',
1848 l_payroll_action_id,
1849 '10',
1850 '1',
1851 'Unprocessed Pre-Payments',
1852 'Number of runs w/o Pre-payments',
1853 l_full_name,
1854 l_assignment_number,
1855 1,l_assignment_action_id);
1856
1857 end;
1858 end if;
1859
1860 close ppp_action;
1861
1862 exception
1863 when others then
1864 hr_utility.trace('Error occurred load_mesg_line ...' ||SQLERRM);
1865 raise;
1866 end load_mesg_line;
1867
1868
1869
1870 procedure load_prepay (p_assignment_action_id number ) is
1871
1872 l_pre_pay_aaid number;
1873 l_void varchar2(1);
1874 l_pre_pay_id number;
1875 l_dummy_val varchar2(1);
1876 l_max_sequence_aaid NUMBER;
1877
1878 cursor prepay (p_max_seq_aaid number) is
1879 select PAA_PPP.ASSIGNMENT_ACTION_ID PRE_PAY_AAID,
1880 POPM.ORG_PAYMENT_METHOD_NAME PMT_NAME,
1881 PPP.VALUE VALUE,
1882 PPP.PRE_PAYMENT_ID PMT_ID
1883 from PAY_PAYROLL_ACTIONS PPA_PPP,
1884 PAY_ASSIGNMENT_ACTIONS PAA_PPP,
1885 PAY_ACTION_INTERLOCKS PAI_RUN,
1886 PAY_PAYROLL_ACTIONS PPA_CHK,
1887 PAY_ASSIGNMENT_ACTIONS PAA_CHK,
1888 PAY_ACTION_INTERLOCKS PAI_CHK,
1889 PAY_ORG_PAYMENT_METHODS_F POPM,
1890 PAY_PRE_PAYMENTS PPP
1891 WHERE PAI_RUN.LOCKED_ACTION_ID = p_max_seq_aaid
1892 AND PAI_RUN.LOCKING_ACTION_ID = PAA_PPP.ASSIGNMENT_ACTION_ID
1893 AND PAA_PPP.ACTION_STATUS = 'C'
1894 AND PAA_PPP.PAYROLL_ACTION_ID = PPA_PPP.PAYROLL_ACTION_ID
1895 AND PPA_PPP.ACTION_STATUS = 'C'
1896 AND PAA_PPP.ASSIGNMENT_ACTION_ID = PPP.ASSIGNMENT_ACTION_ID
1897 AND POPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
1898 AND PPA_PPP.EFFECTIVE_DATE BETWEEN
1899 POPM.EFFECTIVE_START_DATE AND POPM.EFFECTIVE_END_DATE
1900 -- AND POPM.DEFINED_BALANCE_ID IS NOT NULL --Bug 3543649
1901 AND PAI_CHK.LOCKED_ACTION_ID = PAA_PPP.ASSIGNMENT_ACTION_ID
1902 AND PAI_CHK.LOCKING_ACTION_ID = PAA_CHK.ASSIGNMENT_ACTION_ID
1903 AND PAA_CHK.ACTION_STATUS = 'C'
1904 AND PAA_CHK.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID
1905 AND PPA_CHK.PAYROLL_ACTION_ID = PAA_CHK.PAYROLL_ACTION_ID
1906 AND PPA_CHK.ACTION_STATUS = 'C'
1907 AND PPA_CHK.ACTION_TYPE IN ('H', 'M')
1908 AND NOT EXISTS
1909 (SELECT NULL
1910 FROM PAY_PAYROLL_ACTIONS PPA_VOID,
1911 PAY_ASSIGNMENT_ACTIONS PAA_VOID,
1912 PAY_ACTION_INTERLOCKS PAI_VOID
1913 WHERE PAI_VOID.LOCKED_ACTION_ID = PAA_CHK.ASSIGNMENT_ACTION_ID
1914 AND PAA_VOID.ASSIGNMENT_ACTION_ID = PAI_VOID.LOCKING_ACTION_ID
1915 AND PAA_VOID.ACTION_STATUS = 'C'
1916 AND PPA_VOID.PAYROLL_ACTION_ID = PAA_VOID.PAYROLL_ACTION_ID
1917 AND PPA_VOID.ACTION_TYPE = 'D'
1918 AND PPA_VOID.ACTION_STATUS = 'C' )
1919 ;
1920
1921 l_pmt_name varchar2(60);
1922 l_pmt_value number(20,2);
1923 begin
1924 hr_utility.trace('Payroll_id = '||to_char(t_payroll_id));
1925 hr_utility.trace('CONC_id = '||to_char(t_consolidation_set_id));
1926 hr_utility.trace('GRE_id = '||to_char(t_gre_id));
1927 hr_utility.trace('Start DT = '||to_char(l_leg_start_date));
1928 hr_utility.trace('END DT = '||to_char(l_leg_end_date));
1929
1930 -- Determine is this assignment_action is the max action sequence.
1931
1932 select paa_outer.assignment_action_id
1933 into l_max_sequence_aaid
1934 from pay_assignment_actions paa_outer
1935 where (paa_outer.payroll_action_id, paa_outer.action_sequence) =
1936 (select paa1.payroll_action_id,
1937 max(paa1.action_sequence)
1938 from pay_assignment_actions paa1,
1939 pay_assignment_actions paa2
1940 where paa1.payroll_action_id = paa2.payroll_action_id
1941 and paa2.assignment_action_id =p_assignment_action_id
1942 and paa1.assignment_id = paa2.assignment_id
1943 -- Bug No 4429173 and paa1.source_action_id is not null
1944 and ((paa1.run_type_id is not null and paa1.source_action_id is not null)
1948 from pay_run_result_values rrv,
1945 or(paa1.run_type_id is null and paa1.source_action_id is null))
1946 and exists (
1947 select 'Y'
1949 pay_input_values_F iv,
1950 pay_run_results rr
1951 where nvl(rrv.result_value,0) <> to_char(0)
1952 and iv.input_value_id = rrv.input_value_id
1953 and iv.name = 'Pay Value'
1954 and rr.run_result_id = rrv.run_result_id
1955 and rr.assignment_action_id = paa1.assignment_action_id
1956 )
1957 group by paa1.payroll_action_id);
1958
1959 IF l_max_sequence_aaid = p_assignment_action_id THEN
1960
1961 open prepay (l_max_sequence_aaid);
1962 loop
1963 fetch prepay into l_pre_pay_aaid,
1964 l_pmt_name,
1965 l_pmt_value,
1966 l_pre_pay_id;
1967 hr_utility.trace('Number of prepay Records fetched = '||to_char(prepay%ROWCOUNT));
1968 exit when prepay%notfound;
1969
1970 BEGIN
1971
1972 SELECT 'X'
1973 INTO l_dummy_val
1974 FROM pay_us_rpt_totals
1975 where location_id = l_pre_pay_id
1976 and tax_unit_id = t_payroll_action_id
1977 and attribute4 = 'Disbursements';
1978
1979 EXCEPTION
1980 WHEN NO_DATA_FOUND THEN
1981 insert into pay_us_rpt_totals
1982 (tax_unit_id, gre_name, organization_name, location_name,
1983 attribute1,
1984 value1,
1985 attribute2,
1986 attribute3,
1987 attribute4,
1988 attribute5,
1989 value2,
1990 organization_id,
1991 location_id)
1992 values
1993 (t_payroll_action_id, l_gre_name, l_org_name, l_location_code,
1994 'PREPAY',
1995 t_payroll_action_id,
1996 '8',
1997 '1',
1998 'Disbursements',
1999 l_pmt_name,
2000 l_pmt_value,
2001 l_pre_pay_aaid,
2002 l_pre_pay_id);
2003
2004 END;
2005
2006 end loop;
2007
2008 close prepay;
2009
2010 END IF;
2011 exception
2012 when others then
2013 hr_utility.trace('Error occurred load_prepay ...' ||SQLERRM);
2014 raise;
2015 end load_prepay;
2016
2017
2018
2019
2020 procedure load_reversals(l_assignment_action_id number) is
2021
2022 l_reverse_amt number(12,2);
2023
2024 begin
2025 hr_utility.trace('Entered Reversals...');
2026 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2027 l_reverse_amt := nvl(pay_balance_pkg.get_value(
2028 p_defined_balance_id => l_defined_balance_id,
2029 p_assignment_action_id => l_assignment_action_id),0);
2030
2031 insert into pay_us_rpt_totals
2032 (tax_unit_id, gre_name, organization_name, location_name,
2033 attribute1,
2034 value1,
2035 attribute2,
2036 attribute3,
2037 attribute4,
2038 attribute5,
2039 value2,organization_id)
2040 values
2041 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
2042 'REVERSAL',
2043 l_payroll_action_id,
2044 '9',
2045 '1',
2046 'Reversals',
2047 'Reversals',
2048 l_reverse_amt, l_assignment_action_id);
2049 hr_utility.trace('Exited Reversals...');
2050 exception
2051 when others then
2052 hr_utility.trace('Error occurred load_reversals ...' ||SQLERRM);
2053 raise;
2054 end load_reversals;
2055
2056 -------------------------------------------------------------------------------------------
2057 /*-- Bug#4942114 starts -- */
2058 /*procedure load_wc_er_liab (l_business_group_id number,
2059 l_assignment_action_id number,
2060 l_wc_er_liab_where varchar2,
2061 l_wc_er_liab_from varchar2
2062 ) is
2063 TYPE cv_typ IS REF CURSOR;
2064 cv cv_typ; */
2065
2066 procedure load_wc_er_liab (l_business_group_id number, l_assignment_action_id number, p_wc_er_liab_status_count number) is
2067
2068 cursor csr_wc_er_liab_rbr is
2069 select pec.classification_name classification_name,
2070 pbt.balance_name balance_name,
2071 pet.element_name element_name,
2072 pftr.sui_jurisdiction_code jurisdiction_code,
2073 pst.state_name state_name
2074 from
2075 pay_balance_types pbt,
2076 pay_element_types_f pet,
2077 pay_element_classifications pec,
2078 pay_assignment_actions paa,
2079 per_all_assignments_f paf,
2083 pay_balance_dimensions pbd,
2080 pay_us_emp_fed_tax_rules_F pftr,
2081 pay_us_states pst,
2082 pay_run_balances prb,
2084 pay_defined_balances pdb
2085 where pec.classification_name ='Employer Taxes'
2086 and pec.legislation_code ='US'
2087 and pet.classification_id = pec.classification_id
2088 and l_effective_date between pet.effective_start_date
2089 and pet.effective_end_date
2090 and pet.element_information10 = pbt.balance_type_id
2091 and pet.element_name in ('Workers Compensation',
2092 'Workers Compensation2 ER',
2093 'Workers Compensation3 ER')
2094 and l_assignment_action_id = paa.assignment_action_id
2095 and paa.assignment_id = paf.assignment_id
2096 and paf.assignment_id = pftr.assignment_id
2097 and l_effective_date between paf.effective_start_date
2098 and paf.effective_end_date
2099 and l_business_group_id = paf.business_group_id
2100 and l_effective_date between pftr.effective_start_date
2101 and pftr.effective_end_date
2102 and pst.state_code = substr(pftr.sui_jurisdiction_code,1,2)
2103 and prb.defined_balance_id = pdb.defined_balance_id
2104 AND pdb.balance_type_id = pbt.balance_type_id
2105 AND pdb.balance_dimension_id = pbd.balance_dimension_id
2106 AND pbd.legislation_code = 'US'
2107 AND pbd.database_item_suffix ='_ASG_JD_GRE_RUN'
2108 AND (pdb.legislation_code ='US'
2109 OR pdb.business_group_id =l_business_group_id)
2110 and prb.assignment_action_id = paa.assignment_action_id
2111 and prb.tax_unit_id = paa.tax_unit_id
2112 and prb.jurisdiction_code = pst.state_code
2113 and prb.tax_unit_id = paa.tax_unit_id;
2114
2115 cursor csr_wc_er_liab is
2116 select pec.classification_name classification_name,
2117 pbt.balance_name balance_name,
2118 pet.element_name element_name,
2119 pftr.sui_jurisdiction_code jurisdiction_code,
2120 pst.state_name state_name
2121 from
2122 pay_balance_types pbt,
2123 pay_element_types_f pet,
2124 pay_element_classifications pec,
2125 pay_assignment_actions paa,
2126 per_all_assignments_f paf,
2127 pay_us_emp_fed_tax_rules_F pftr,
2128 pay_us_states pst,
2129 pay_run_results prr
2130 where pec.classification_name ='Employer Taxes'
2131 and pec.legislation_code ='US'
2132 and pet.classification_id = pec.classification_id
2133 and l_effective_date between pet.effective_start_date
2134 and pet.effective_end_date
2135 and pet.element_information10 = pbt.balance_type_id
2136 and pet.element_name in ('Workers Compensation',
2137 'Workers Compensation2 ER',
2138 'Workers Compensation3 ER')
2139 and l_assignment_action_id = paa.assignment_action_id
2140 and paa.assignment_id = paf.assignment_id
2141 and paf.assignment_id = pftr.assignment_id
2142 and l_effective_date between paf.effective_start_date
2143 and paf.effective_end_date
2144 and l_business_group_id = paf.business_group_id
2145 and l_effective_date between pftr.effective_start_date
2146 and pftr.effective_end_date
2147 and pst.state_code = substr(pftr.sui_jurisdiction_code,1,2)
2148 and prr.element_type_id +0 = pet.element_type_id
2149 and prr.assignment_action_id = paa.assignment_action_id;
2150 /*-- Bug#4942114 ends -- */
2151 l_classification_name varchar2(60);
2152 l_balance_name varchar2(60);
2153 l_element_name varchar2(60);
2154 l_jurisdiction_code varchar2(11);
2155 l_run_val number(20,2);
2156 l_state_name varchar2(60);
2157 l_status number;
2158 BEGIN
2159 hr_utility.trace('entered load_wc_er_liab');
2160
2161 /*-- Bug#4942114 starts -- */
2162 /* hr_utility.trace('l_wc_er_liab_where ='|| l_wc_er_liab_where);
2163 OPEN cv FOR
2164 'select pec.classification_name classification_name,
2165 pbt.balance_name balance_name,
2166 pet.element_name element_name,
2167 pftr.sui_jurisdiction_code jurisdiction_code,
2168 pst.state_name state_name
2169 from
2170 pay_balance_types pbt,
2171 pay_element_types_f pet,
2172 pay_element_classifications pec,
2173 pay_assignment_actions paa,
2174 per_all_assignments_f paf,
2175 pay_us_emp_fed_tax_rules_F pftr,
2176 pay_us_states pst,'
2177 ||l_wc_er_liab_from||
2178 ' where pec.classification_name ='||'''Employer Taxes'''
2179 ||' and pec.legislation_code ='||'''US'''
2183 and pet.element_information10 = pbt.balance_type_id
2180 ||' and pet.classification_id = pec.classification_id
2181 and '''||l_effective_date||''' between pet.effective_start_date
2182 and pet.effective_end_date
2184 and pet.element_name in ('|| '''Workers Compensation'''||','
2185 || '''Workers Compensation2 ER'''||','
2186 || '''Workers Compensation3 ER'''||')
2187 and '||l_assignment_action_id ||' = paa.assignment_action_id
2188 and paa.assignment_id = paf.assignment_id
2189 and paf.assignment_id = pftr.assignment_id
2190 and '''||l_effective_date||''' between paf.effective_start_date
2191 and paf.effective_end_date
2192 and '||l_business_group_id ||'= paf.business_group_id
2193 and '''||l_effective_date||''' between pftr.effective_start_date
2194 and pftr.effective_end_date
2195 and pst.state_code = substr(pftr.sui_jurisdiction_code,1,2)
2196 and '|| l_wc_er_liab_where;
2197 loop
2198 fetch cv into l_classification_name,
2199 l_balance_name,
2200 l_element_name,
2201 l_jurisdiction_code,
2202 l_state_name ;
2203 hr_utility.trace('Number of WC ER LIAB Records fetched = '||to_char(cv%ROWCOUNT));
2204 exit when cv%notfound; */
2205
2206 if p_wc_er_liab_status_count = 3 then
2207 open csr_wc_er_liab_rbr;
2208 else
2209 open csr_wc_er_liab;
2210 end if;
2211
2212 loop
2213
2214 if p_wc_er_liab_status_count = 3 then
2215 fetch csr_wc_er_liab_rbr into l_classification_name,
2216 l_balance_name,
2217 l_element_name,
2218 l_jurisdiction_code,
2219 l_state_name ;
2220 hr_utility.trace('Number of WC ER LIAB Records fetched = '||to_char(csr_wc_er_liab_rbr%ROWCOUNT));
2221 exit when csr_wc_er_liab_rbr%notfound;
2222 else
2223 fetch csr_wc_er_liab into l_classification_name,
2224 l_balance_name,
2225 l_element_name,
2226 l_jurisdiction_code,
2227 l_state_name ;
2228 hr_utility.trace('Number of WC ER LIAB Records fetched = '||to_char(csr_wc_er_liab%ROWCOUNT));
2229 exit when csr_wc_er_liab%notfound;
2230
2231 end if;
2232
2233 /*-- Bug#4942114 ends -- */
2234 l_bal_value := pay_us_taxbal_view_pkg.us_named_balance(
2235 upper(l_balance_name),
2236 'ASG_JD_GRE_RUN',
2237 l_assignment_action_id,
2238 null,
2239 null,
2240 'GRE',
2241 l_tax_unit_id,
2242 l_business_group_id,
2243 l_jurisdiction_code);
2244 if l_asg_flag <> 'Y' THEN
2245 if l_index <>0 then
2246 l_status :=0;
2247 for l_temp_index in g_totals_table.first..g_totals_table.last LOOP
2248 if g_totals_table(l_temp_index).attribute5 = l_element_name||' '||l_state_name and
2249 g_totals_table(l_temp_index).gre_name = l_gre_name and
2250 g_totals_table(l_temp_index).organization_name = l_org_name and
2251 g_totals_table(l_temp_index).location_name = l_location_code then
2252
2253 hr_utility.trace('testing 1');
2254 g_totals_table(l_temp_index).value2 := g_totals_table(l_temp_index).value2 +
2255 to_number(l_bal_value);
2256 l_status := 1;
2257 hr_utility.trace('for wc er liab...l_index ='||l_index);
2258 hr_utility.trace('element name ='||l_element_name||' '||l_state_name);
2259 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
2260 hr_utility.trace('gre_name='||l_gre_name);
2261 hr_utility.trace('org name='||l_org_name);
2262 hr_utility.trace('location='||l_location_code);
2263 hr_utility.trace('Toal Cash value ='||to_char(g_totals_table(l_temp_index).value2));
2264 hr_utility.trace('Cash Value ='||l_bal_value);
2265 end if;
2266 end loop;
2267 end if;
2268 if l_status <> 1 or l_index = 0 then
2269 hr_utility.trace('testing 6');
2270 l_index := l_index + 1;
2271 g_totals_table(l_index).gre_name := l_gre_name;
2272 g_totals_table(l_index).organization_name := l_org_name;
2273 g_totals_table(l_index).location_name := l_location_code;
2274 g_totals_table(l_index).tax_unit_id := l_payroll_action_id;
2275 g_totals_table(l_index).attribute4 := 'Employer Taxes';
2276 g_totals_table(l_index).attribute3 := '1';
2277 g_totals_table(l_index).attribute5 := l_element_name||' '||l_state_name;
2278 g_totals_table(l_index).value2 := to_number(l_bal_value);
2279 g_totals_table(l_index).value3 := NULL;
2280 g_totals_table(l_index).attribute1 := 'ER-TAX';
2281 g_totals_table(l_index).attribute2 := '6';
2282 end if;
2283 hr_utility.trace('for wc er liab...l_index ='||l_index);
2284 hr_utility.trace('element name ='||l_element_name||' '||l_state_name);
2285 hr_utility.trace('payroll action='||to_char(g_totals_table(l_index).tax_unit_id));
2289 hr_utility.trace('Cash Value ='||l_bal_value);
2286 hr_utility.trace('gre_name='||l_gre_name);
2287 hr_utility.trace('org name='||l_org_name);
2288 hr_utility.trace('location='||l_location_code);
2290 else
2291 insert into pay_us_rpt_totals
2292 (tax_unit_id, gre_name, organization_name, location_name,
2293 attribute1,
2294 value1,
2295 attribute2,
2296 attribute3,
2297 attribute4,
2298 attribute5,
2299 value2,
2300 organization_id,
2301 business_group_id,
2302 attribute12)
2303 values
2304 (l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
2305 'ER-TAX',
2306 l_payroll_action_id,
2307 '6',
2308 '1',
2309 'Employer Taxes',
2310 l_element_name||' '||l_state_name,
2311 l_bal_value,
2312 l_assignment_action_id,
2313 l_person_id,
2314 l_full_name);
2315 end if;
2316 end loop;
2317 /*-- Bug#4942114 starts -- */
2318 if p_wc_er_liab_status_count = 3 then
2319 close csr_wc_er_liab_rbr;
2320 else
2321 close csr_wc_er_liab;
2322 end if;
2323 /*-- Bug#4942114 ends -- */
2324 exception
2325 when others then
2326 hr_utility.trace('Error occurred load_wc_er_liab ...' ||SQLERRM);
2327 raise;
2328 end load_wc_er_liab;
2329 -----------------------------------------------------------------------------------
2330 procedure load_data
2331 (
2332 pactid in varchar2, /* payroll action id */
2333 chnkno in number,
2334 ppa_finder in varchar2
2335 ) is
2336
2337 cursor sel_aaid (l_pactid number,
2338 l_chnkno number)
2339 is
2340 select
2341 ppa_arch.start_date start_date,
2342 ppa_arch.effective_date end_date,
2343 ppa_arch.business_group_id business_group_id,
2344 ppa_arch.payroll_action_id payroll_action_id,
2345 ppa.effective_date effective_date,
2346 ppa.action_type action_type,
2347 paa1.assignment_action_id assignment_action_id,
2348 paa1.assignment_id assignment_id,
2349 paa1.tax_unit_id tax_unit_id,
2350 substr(hou.name,1,228) gre_name, /*bug6998211*/
2351 paf.organization_id organization_id,
2352 substr(hou1.name,1,228) organization_name,
2353 paf.location_id location_id,
2354 hrl.location_code location_code
2355 ,paf.assignment_number assignment_number -- #1937448
2356 ,paf.person_id person_id
2357 from hr_locations_all hrl,
2358 hr_all_organization_units hou1,
2359 hr_all_organization_units hou,
2360 per_assignments_f paf,
2361 pay_payroll_actions ppa,
2362 pay_assignment_actions paa1,
2363 pay_action_interlocks pai,
2364 pay_assignment_actions paa,
2365 pay_payroll_actions ppa_arch
2366 where ppa_arch.payroll_action_id = l_pactid
2367 and paa.payroll_action_id = ppa_arch.payroll_action_id
2368 and paa.chunk_number = l_chnkno
2369 and pai.locking_action_id = paa.assignment_action_id
2370 and paa1.assignment_action_id = pai.locked_action_id
2371 and ppa.payroll_action_id = paa1.payroll_action_id
2372 and paf.assignment_id = paa1.assignment_id
2373 and ppa.effective_date between paf.effective_start_date
2374 and paf.effective_end_date
2375 and hrl.location_id = paf.location_id
2376 and hou1.organization_id = paf.organization_id
2377 and hou.organization_id = paa1.tax_unit_id;
2378
2379 l_ded_view_name varchar2(30);
2380 l_earn_view_name varchar2(30);
2381 l_fed_view_name varchar2(30);
2382 l_state_view_name varchar2(30);
2383 l_local_view_name varchar2(30);
2384 l_fed_liab_view_name varchar2(30);
2385 l_state_liab_view_name varchar2(30);
2386 l_futa_where varchar2(2000);
2387 l_futa_from varchar2(2000);
2388 l_er_liab_where varchar2(2000);
2389 l_er_liab_from varchar2(2000);
2390 l_wc_er_liab_where varchar2(2000);
2391 l_wc_er_liab_from varchar2(2000);
2392 begin
2393 -- hr_utility.trace_on('Y','GTN');
2394 l_row_count := 0;
2395 hr_utility.trace('PACTID = '||pactid);
2396 hr_utility.trace('CHNKNO = '||to_char(chnkno));
2397 hr_utility.trace('PPA_FINDER = '||ppa_finder);
2398 begin
2399 select ppa.legislative_parameters,
2400 ppa.business_group_id,
2401 ppa.start_date,
2402 ppa.effective_date,
2403 pay_paygtn_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
2404 pay_paygtn_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
2405 pay_paygtn_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
2406 ppa.payroll_action_id
2407 into l_leg_param,
2408 l_business_group_id,
2409 l_leg_start_date,
2410 l_leg_end_date,
2411 t_consolidation_set_id,
2412 t_payroll_id,
2413 t_gre_id,
2414 t_payroll_action_id
2415 from pay_payroll_actions ppa
2419 raise;
2416 where ppa.payroll_action_id = pactid;
2417 exception when no_data_found then
2418 hr_utility.trace('Legislative Details not found...');
2420 end;
2421
2422 begin
2423 select to_number(ue.creator_id)
2424 into l_defined_balance_id
2425 from ff_user_entities ue,
2426 ff_database_items di
2427 where di.user_name = 'PAYMENTS_ASG_GRE_RUN'
2428 and ue.user_entity_id = di.user_entity_id
2429 and ue.creator_type = 'B'
2430 and nvl(ue.legislation_code,'US') = 'US';
2431 exception when others then
2432 hr_utility.trace('Error getting defined balance id');
2433 raise;
2434 end;
2435
2436 -- #3270485: moved to range_cursor procedure.
2437 --
2438 -- if chnkno = 1 then
2439 -- insert into pay_us_rpt_totals (tax_unit_id,attribute1,organization_id,
2440 -- attribute2,attribute3,attribute4,attribute5)
2441 -- values (pactid,'GTN',ppa_finder,
2442 -- l_leg_param, l_business_group_id,
2443 -- to_char(l_leg_start_date,'MM/DD/YYYY'),
2444 -- to_char(l_leg_end_date,'MM/DD/YYYY'));
2445 -- commit;
2446 -- end if;
2447
2448 pay_us_balance_view_pkg.set_view_mode('ASG');
2449 pay_us_balance_view_pkg.set_calc_all_timetypes_flag(0);
2450 pay_us_balance_view_pkg.set_session_var('PTD', 'FALSE');
2451 pay_us_balance_view_pkg.set_session_var('PYDATE', 'FALSE');
2452 pay_us_balance_view_pkg.set_session_var('MONTH', 'FALSE');
2453 pay_us_balance_view_pkg.set_session_var('QTD', 'FALSE');
2454 pay_us_balance_view_pkg.set_session_var('CURRENT','FALSE');
2455 pay_us_balance_view_pkg.set_session_var('YTD', 'FALSE');
2456
2457 l_ded_view_name := 'PAY_US_GTN_DEDUCT_V';
2458 l_earn_view_name := 'PAY_US_GTN_EARNINGS_V';
2459 l_fed_view_name := 'PAY_US_FED_TAXES_V';
2460 l_state_view_name := 'PAY_US_STATE_TAXES_V';
2461 l_local_view_name := 'PAY_US_LOCAL_TAXES_V';
2462 l_fed_liab_view_name := 'PAY_US_FED_LIABILITIES_V';
2463 l_state_liab_view_name := 'PAY_US_STATE_LIABILITIES_V';
2464 l_futa_where := ' prr.status in ('||'''P'''||','||'''PA'''||')
2465 and pet.element_type_id = prr.element_type_id
2466 and prr.assignment_action_id = ';
2467 l_futa_from := ' pay_run_results prr ';
2468
2469 l_er_liab_where := ' prr.element_type_id +0 = pet.element_type_id
2470 and prr.status in (' || '''P''' || ', ' || '''PA''' || ')
2471 and prr.assignment_action_id = ';
2472 l_er_liab_from := ' pay_run_results prr ';
2473
2474 l_wc_er_liab_where := ' prr.element_type_id +0 = pet.element_type_id
2475 and prr.assignment_action_id = ';
2476 l_wc_er_liab_from := ' pay_run_results prr ';
2477 l_asg_flag := 'N';
2478 open sel_aaid (to_number(pactid),chnkno);
2479 loop
2480 fetch sel_aaid into l_start_date,
2481 l_end_date,
2482 l_business_group_id,
2483 l_payroll_action_id,
2484 l_effective_date,
2485 l_action_type,
2486 l_assignment_action_id,
2487 l_assignment_id,
2488 l_tax_unit_id,
2489 l_gre_name,
2490 l_organization_id,
2491 l_org_name,
2492 l_location_id,
2493 l_location_code,
2494 l_assignment_number,
2495 l_person_id;
2496
2497 hr_utility.trace('Number of Records fetched = '||to_char(sel_aaid%ROWCOUNT));
2498 exit when sel_aaid%notfound;
2499
2500 hr_utility.trace('Chunk No = '||to_char(chnkno));
2501 hr_utility.trace('Start Date = '||to_char(l_start_date));
2502 hr_utility.trace('End Date = '||to_char(l_end_date));
2503 hr_utility.trace('BG ID = '||to_char(l_business_group_id));
2504 hr_utility.trace('Payroll Action ID = '||to_char(l_payroll_action_id));
2505 hr_utility.trace('Effective Date = '||to_char(l_effective_date));
2506 hr_utility.trace('Action Type = '||l_action_type);
2507 hr_utility.trace('Asg Act ID = '||to_char(l_assignment_action_id));
2508 hr_utility.trace('Asg ID = '||to_char(l_assignment_id));
2509 hr_utility.trace('Tax Unit ID = '||to_char(l_tax_unit_id));
2510 hr_utility.trace('GRE Name = '||l_gre_name);
2511 hr_utility.trace('ORG ID = '||to_char(l_organization_id));
2512 hr_utility.trace('ORG Name = '||l_org_name);
2513 hr_utility.trace('Loc ID = '||to_char(l_location_id));
2514 hr_utility.trace('Loc Code = '||l_location_code);
2515 /*--Bug#4942114 starts --*/
2516 /*load_deductions(l_assignment_action_id,l_ded_view_name);
2517 load_earnings (l_assignment_action_id,l_earn_view_name);
2518 -- load_alien_earnings (l_assignment_action_id);
2522 load_er_credit (l_assignment_action_id,l_futa_where||l_assignment_action_id,l_futa_from);
2519 load_ee_tax (l_assignment_action_id,l_fed_view_name,l_state_view_name,l_local_view_name);
2520 load_er_tax (l_assignment_action_id,l_fed_liab_view_name,l_state_liab_view_name,l_local_view_name);
2521 load_ee_credit (l_assignment_action_id,l_fed_view_name,l_state_view_name);
2523 load_er_liab (l_business_group_id,l_assignment_action_id,l_er_liab_where || l_assignment_action_id,l_er_liab_from);
2524 load_wc_er_liab (l_business_group_id,l_assignment_action_id,l_wc_er_liab_where|| l_assignment_action_id,l_wc_er_liab_from); */
2525 /*--Bug#4942114 starts --*/
2526 if l_action_type in ('R','Q') then
2527 load_mesg_line (l_assignment_action_id);
2528 load_prepay (l_assignment_action_id);
2529 end if;
2530 if l_action_type = 'V' then
2531 load_reversals (l_assignment_action_id);
2532 end if;
2533 l_row_count := l_row_count +1;
2534 if l_row_count = 200 then
2535 l_row_count := 0;
2536 commit;
2537 end if;
2538 end loop;
2539 hr_utility.trace('End of LOAD DATA');
2540 close sel_aaid;
2541 if (l_index <>0) then
2542 for x in g_totals_table.first..g_totals_table.last LOOP
2543 INSERT INTO pay_us_rpt_totals(tax_unit_id, gre_name,
2544 organization_name, location_name,
2545 attribute1,
2546 attribute2,
2547 attribute3,
2548 attribute4,
2549 attribute5,
2550 value2,
2551 value3
2552 ) values
2553 (g_totals_table(x).tax_unit_id, g_totals_table(x).gre_name,
2554 g_totals_table(x).organization_name, g_totals_table(x).location_name,
2555 g_totals_table(x).attribute1,
2556 g_totals_table(x).attribute2,
2557 g_totals_table(x).attribute3,
2558 g_totals_table(x).attribute4,
2559 g_totals_table(x).attribute5,
2560 g_totals_table(x).value2,
2561 g_totals_table(x).value3
2562 );
2563 END LOOP;
2564 /*forall x in g_totals_table.first..g_totals_table.last
2565 insert into pay_us_rpt_totals
2566 values
2567 g_totals_table(x);*/
2568 l_index :=0;
2569 g_totals_table.DELETE;
2570 end if;
2571 commit;
2572 exception
2573 when others then
2574 hr_utility.trace('Error occurred load_data ...' ||SQLERRM);
2575 raise;
2576 end load_data;
2577
2578 procedure load_data
2579 (
2580 p_payroll_action_id number ,
2581 p_chunk number,
2582 ppa_finder number ,
2583 -- Bug#4942114 starts
2584 /* p_ded_view_name varchar2 ,
2585 p_earn_view_name varchar2 ,
2586 p_fed_view_name varchar2 ,
2587 p_state_view_name varchar2 ,
2588 p_local_view_name varchar2 ,
2589 p_fed_liab_view_name varchar2 ,
2590 p_state_liab_view_name varchar2 ,
2591 p_futa_where varchar2,
2592 p_futa_from varchar2,
2593 p_er_liab_where varchar2 ,
2594 p_er_liab_from varchar2,
2595 p_wc_er_liab_where varchar2 ,
2596 p_wc_er_liab_from varchar2,
2597 li-- Bug#4942114 ends */
2598 p_ded_bal_status1 varchar2,
2599 p_ded_bal_status2 varchar2,
2600 p_earn_bal_status varchar2,
2601 p_fed_bal_status varchar2,
2602 p_state_bal_status varchar2,
2603 p_local_bal_status varchar2,
2604 p_fed_liab_bal_status varchar2,
2605 p_state_liab_bal_status varchar2,
2606 p_futa_status_count number,
2607 p_futa_def_bal_id number,
2608 p_er_liab_status varchar2,
2609 p_wc_er_liab_status_count number,
2610 p_asg_flag varchar2
2611 ) is
2612
2613
2614
2615 cursor sel_aaid (l_pactid number,l_chunk_no number)
2616 is
2617 select
2618 ppa_arch.start_date start_date,
2619 ppa_arch.effective_date end_date,
2620 ppa_arch.business_group_id business_group_id,
2621 ppa_arch.payroll_action_id payroll_action_id,
2622 ppa.effective_date effective_date,
2623 ppa.action_type action_type,
2624 paa1.assignment_action_id assignment_action_id,
2625 paa1.assignment_id assignment_id,
2626 paa1.tax_unit_id tax_unit_id,
2627 substr(hou.name,1,228) gre_name, /*bug6998211*/
2628 paf.organization_id organization_id,
2629 substr(hou1.name,1,228) organization_name,
2630 paf.location_id location_id,
2631 hrl.location_code location_code
2632 ,paf.assignment_number assignment_number -- #1937448
2633 ,paf.person_id person_id
2634 ,paa.chunk_number chunk_number
2635 from hr_locations_all hrl,
2636 hr_all_organization_units hou1,
2637 hr_all_organization_units hou,
2638 per_assignments_f paf,
2639 pay_payroll_actions ppa,
2640 pay_assignment_actions paa1,
2641 pay_temp_object_actions paa,
2642 pay_payroll_actions ppa_arch
2643 where paa.payroll_action_id = l_pactid
2644 and paa.chunk_number = l_chunk_no
2645 and paa.payroll_action_id = ppa_arch.payroll_action_id
2646 and paa.object_id = paa1.assignment_action_id
2647 and ppa.payroll_action_id = paa1.payroll_action_id
2648 and paf.assignment_id = paa1.assignment_id
2652 and hou1.organization_id = paf.organization_id
2649 and ppa.effective_date between paf.effective_start_date
2650 and paf.effective_end_date
2651 and hrl.location_id = paf.location_id
2653 and hou.organization_id = paa1.tax_unit_id;
2654
2655 cursor sel_empname(l_person_id number,l_effective_date date)
2656 is
2657 select
2658 ppf.full_name
2659 from per_all_people_f ppf
2660 where ppf.person_id = l_person_id
2661 and l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
2662 l_chnk_number number;
2663 begin
2664 -- hr_utility.trace_on('Y','GTN');
2665 l_row_count := 0;
2666 hr_utility.trace('PPCTID = '||p_payroll_action_id);
2667 hr_utility.trace('PPA_FINDER = '||ppa_finder);
2668 hr_utility.trace('p_asg_flag ='||p_asg_flag);
2669
2670 begin
2671 select to_number(ue.creator_id)
2672 into l_defined_balance_id
2673 from ff_user_entities ue,
2674 ff_database_items di
2675 where di.user_name = 'PAYMENTS_ASG_GRE_RUN'
2676 and ue.user_entity_id = di.user_entity_id
2677 and ue.creator_type = 'B'
2678 and nvl(ue.legislation_code,'US') = 'US';
2679 exception when others then
2680 hr_utility.trace('Error getting defined balance id');
2681 raise;
2682 end;
2683
2684 -- #3270485: moved to range_cursor procedure.
2685 --
2686 -- if chnkno = 1 then
2687 -- insert into pay_us_rpt_totals (tax_unit_id,attribute1,organization_id,
2688 -- attribute2,attribute3,attribute4,attribute5)
2689 -- values (pactid,'GTN',ppa_finder,
2690 -- l_leg_param, l_business_group_id,
2691 -- to_char(l_leg_start_date,'MM/DD/YYYY'),
2692 -- to_char(l_leg_end_date,'MM/DD/YYYY'));
2693 -- commit;
2694 -- end if;
2695
2696 l_asg_flag := nvl(p_asg_flag,'N');
2697 hr_utility.trace('l_asg_flag ='||l_asg_flag);
2698 pay_us_balance_view_pkg.set_view_mode('ASG');
2699 pay_us_balance_view_pkg.set_calc_all_timetypes_flag(0);
2700 pay_us_balance_view_pkg.set_session_var('PTD', 'FALSE');
2701 pay_us_balance_view_pkg.set_session_var('PYDATE', 'FALSE');
2702 pay_us_balance_view_pkg.set_session_var('MONTH', 'FALSE');
2703 pay_us_balance_view_pkg.set_session_var('QTD', 'FALSE');
2704 pay_us_balance_view_pkg.set_session_var('CURRENT','FALSE');
2705 pay_us_balance_view_pkg.set_session_var('YTD', 'FALSE');
2706
2707
2708
2709
2710 open sel_aaid (p_payroll_action_id,p_chunk);
2711 loop
2712 fetch sel_aaid into l_start_date,
2713 l_end_date,
2714 l_business_group_id,
2715 l_payroll_action_id,
2716 l_effective_date,
2717 l_action_type,
2718 l_assignment_action_id,
2719 l_assignment_id,
2720 l_tax_unit_id,
2721 l_gre_name,
2722 l_organization_id,
2723 l_org_name,
2724 l_location_id,
2725 l_location_code,
2726 l_assignment_number,
2727 l_person_id,
2728 l_chnk_number;
2729
2730 hr_utility.trace('Number of Records fetched = '||to_char(sel_aaid%ROWCOUNT));
2731
2732 exit when sel_aaid%notfound;
2733 open sel_empname(l_person_id,l_effective_date);
2734 fetch sel_empname into l_full_name;
2735 close sel_empname;
2736 begin
2737 select ppa.legislative_parameters,
2738 ppa.start_date,
2739 ppa.effective_date,
2740 pay_paygtn_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
2741 pay_paygtn_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
2742 pay_paygtn_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
2743 ppa.payroll_action_id
2744 into l_leg_param,
2745 l_leg_start_date,
2746 l_leg_end_date,
2747 t_consolidation_set_id,
2748 t_payroll_id,
2749 t_gre_id,
2750 t_payroll_action_id
2751 from pay_payroll_actions ppa
2752 where ppa.payroll_action_id = l_payroll_action_id;
2753 exception when no_data_found then
2754 hr_utility.trace('Legislative Details not found...');
2755 raise;
2756 end;
2757 hr_utility.trace('Start Date = '||to_char(l_start_date));
2758 hr_utility.trace('End Date = '||to_char(l_end_date));
2759 hr_utility.trace('BG ID = '||to_char(l_business_group_id));
2760 hr_utility.trace('Payroll Action ID = '||to_char(l_payroll_action_id));
2761 hr_utility.trace('Effective Date = '||to_char(l_effective_date));
2762 hr_utility.trace('Action Type = '||l_action_type);
2763 hr_utility.trace('Asg Act ID = '||to_char(l_assignment_action_id));
2764 hr_utility.trace('Asg ID = '||to_char(l_assignment_id));
2765 hr_utility.trace('Tax Unit ID = '||to_char(l_tax_unit_id));
2766 hr_utility.trace('GRE Name = '||l_gre_name);
2767 hr_utility.trace('ORG ID = '||to_char(l_organization_id));
2771 hr_utility.trace('Chunk Number = '||l_chnk_number);
2768 hr_utility.trace('ORG Name = '||l_org_name);
2769 hr_utility.trace('Loc ID = '||to_char(l_location_id));
2770 hr_utility.trace('Loc Code = '||l_location_code);
2772
2773 -- Bug#4942114 starts
2774 /*
2775 hr_utility.trace('p_futa_where = '||p_futa_where);
2776 hr_utility.trace('p_futa_from = '||p_futa_from);
2777 load_deductions(l_assignment_action_id,p_ded_view_name);
2778 load_earnings (l_assignment_action_id,p_earn_view_name);
2779 -- load_alien_earnings (l_assignment_action_id);
2780 load_ee_tax (l_assignment_action_id,p_fed_view_name,p_state_view_name,p_local_view_name);
2781 load_er_tax (l_assignment_action_id,p_fed_liab_view_name,p_state_liab_view_name,p_local_view_name);
2782 load_ee_credit (l_assignment_action_id,p_fed_view_name,p_state_view_name);
2783 load_er_credit (l_assignment_action_id,p_futa_where||l_assignment_action_id,p_futa_from);
2784 load_er_liab (l_business_group_id,l_assignment_action_id,p_er_liab_where || l_assignment_action_id,p_er_liab_from);
2785 load_wc_er_liab (l_business_group_id,l_assignment_action_id,p_wc_er_liab_where,p_wc_er_liab_from);
2786 */
2787
2788 load_deductions(l_assignment_action_id,p_ded_bal_status1,p_ded_bal_status2);
2789 load_earnings (l_assignment_action_id,p_earn_bal_status);
2790 load_ee_tax (l_assignment_action_id,p_fed_bal_status,p_state_bal_status,p_local_bal_status);
2791 load_er_tax (l_assignment_action_id,p_fed_liab_bal_status,p_state_liab_bal_status,p_local_bal_status);
2792 load_ee_credit (l_assignment_action_id,p_fed_bal_status,p_state_bal_status);
2793 load_er_credit (l_assignment_action_id,p_futa_status_count,p_futa_def_bal_id);
2794 load_er_liab (l_business_group_id,l_assignment_action_id,p_er_liab_status);
2795 load_wc_er_liab (l_business_group_id,l_assignment_action_id,p_wc_er_liab_status_count);
2796 -- Bug#4942114 ends
2797
2798 if l_action_type in ('R','Q') then
2799 load_mesg_line (l_assignment_action_id);
2800 load_prepay (l_assignment_action_id);
2801 end if;
2802 if l_action_type = 'V' then
2803 load_reversals (l_assignment_action_id);
2804 end if;
2805 /* l_row_count := l_row_count +1;
2806 if l_row_count = 200 then
2807 l_row_count := 0;
2808 commit;
2809 end if;*/
2810 end loop;
2811 hr_utility.trace('End of LOAD DATA');
2812 close sel_aaid;
2813 if (l_index <>0) then
2814 for x in g_totals_table.first..g_totals_table.last LOOP
2815 INSERT INTO pay_us_rpt_totals(tax_unit_id, gre_name,
2816 organization_name, location_name,
2817 attribute1,
2818 attribute2,
2819 attribute3,
2820 attribute4,
2821 attribute5,
2822 value2,
2823 value3
2824 ) values
2825 (g_totals_table(x).tax_unit_id, g_totals_table(x).gre_name,
2826 g_totals_table(x).organization_name, g_totals_table(x).location_name,
2827 g_totals_table(x).attribute1,
2828 g_totals_table(x).attribute2,
2829 g_totals_table(x).attribute3,
2830 g_totals_table(x).attribute4,
2831 g_totals_table(x).attribute5,
2832 g_totals_table(x).value2,
2833 g_totals_table(x).value3
2834 );
2835 END LOOP;
2836 /*forall x in g_totals_table.first..g_totals_table.last
2837 insert into pay_us_rpt_totals
2838 values
2839 g_totals_table(x);*/
2840 commit;
2841 l_index :=0;
2842 g_totals_table.DELETE;
2843 end if;
2844 -- commit;
2845 exception
2846 when others then
2847 hr_utility.trace('Error occurred load_data ...' ||SQLERRM);
2848 raise;
2849 end load_data;
2850 --
2851 ------------------------------ end load data -------------------------------
2852 end pay_gtnlod_pkg;