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