[Home] [Help]
PACKAGE BODY: APPS.PAY_US_MARK_W2C_PAPER
Source
1 PACKAGE BODY pay_us_mark_w2c_paper AS
2 /* $Header: payusmarkw2cpapr.pkb 120.0.12020000.2 2013/04/12 05:28:17 skchalla ship $*/
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_mark_w2c_paper
21 File Name : payusmarkw2cpapr.pkb
22
23 Description : Mark all assignment action included in W-2c Report process
24 confirming W-2c paper submitted to Govt. Once a corrected
25 assignment is marked as submitted, this assignment will not
26 be picked up by "Federeal W-2c Magnetic Media" process.
27
28 Uses :
29
30 Change List
31 -----------
32 Date Name Vers Bug No Description
33 ---- ---- ------ ------- -----------
34 10-Oct-2003 ppanda 115.0 Created.
35 02-DEC-2003 ppanda 115.1 3275044 A fatal error will be raised
36 when no W-2c paper assignment action
37 picked up by the process
38
39 10-NOV-2004 asasthan 115.2 3264740 Detial report provided.
40 10-NOV-2004 meshah 115.3 Fixed a gscc error.
41 18-NOV-2004 asasthan 115.4 3264740 Updated output_type for HTML
42 14-MAR-2005 sackumar 115.6 4222032 Change in the Range Cursor removing redundant
43 use of bind Variable (:payroll_action_id)
44 09-Apr-2013 skchalla 115.7 16617759 Modified the code to pickup the actions for W-2c PDF
45 along with the W-2c Paper
46 *******************************************************************/
47
48 /******************************************************************
49 ** Package Local Variables
50 ******************************************************************/
51 gv_package varchar2(50);
52 gv_title VARCHAR2(100);
53
54 /*******************************************************************
55 ** Procedure return the values for the Payroll Action of
56 ** the "Mark Paper W-2c and Exclude From Future Tapes" process.
57 ** This is used in Range Code and Action Creation.
58 ******************************************************************/
59
60 PROCEDURE get_payroll_action_info
61 (
62 p_payroll_action_id in number,
63 p_start_date in out nocopy date,
64 p_end_date in out nocopy date,
65 p_report_type in out nocopy varchar2,
66 p_report_qualifier in out nocopy varchar2,
67 p_business_group_id in out nocopy number,
68 p_seq_num in out nocopy number
69 )
70 IS
71 cursor c_payroll_action(cp_payroll_action_id in number) is
72 select ppa.start_date
73 ,ppa.effective_date
74 ,ppa.report_type
75 ,ppa.report_qualifier
76 ,ppa.business_group_id
77 ,pay_us_payroll_utils.get_parameter('S_N',
78 ppa.legislative_parameters)
79 from pay_payroll_actions ppa
80 where payroll_action_id = cp_payroll_action_id;
81
82 ld_start_date date;
83 ld_end_date date;
84 lv_report_type varchar2(50);
85 lv_report_qualifier varchar2(50);
86 ln_business_group_id number;
87 ln_seq_num number;
88
89 BEGIN
90 hr_utility.set_location(gv_package || '.get_payroll_action_info', 10);
91
92 open c_payroll_action(p_payroll_action_id);
93 fetch c_payroll_action into
94 ld_start_date,
95 ld_end_date,
96 lv_report_type,
97 lv_report_qualifier,
98 ln_business_group_id,
99 ln_seq_num;
100 if c_payroll_action%notfound then
101 hr_utility.set_location( gv_package || '.get_payroll_action_info',20);
102 hr_utility.trace('Payroll Action '||to_char(p_payroll_action_id)||' Not found');
103 hr_utility.raise_error;
104 end if;
105 close c_payroll_action;
106 hr_utility.set_location(gv_package || '.get_payroll_action_info', 30);
107 p_start_date := ld_start_date;
108 p_end_date := ld_end_date;
109 p_report_type := lv_report_type;
110 p_report_qualifier := lv_report_qualifier;
111 p_business_group_id := ln_business_group_id;
112 p_seq_num := ln_seq_num;
113
114 hr_utility.set_location(gv_package || '.get_payroll_action_info', 40);
115 END get_payroll_action_info;
116
117 --------------------------------------------------------------------------
118 --Name
119 -- preprocess_check
120 -- Purpose
121 -- This function checks whether W-2c paper genrated and waiting for W-2c mag
122 -- to pick up for processing. If it doesn't find even a single W-2c paper
123 -- assignment action, it logs a message for user
124 -- Arguments
125 -- p_pactid payroll_action_id for the report
126 -- p_year_start start date of the period for which the report
127 -- has been requested
128 -- p_year_end end date of the period
129 -- p_business_group_id business group for which the report is being run
130 --
131 --Notes
132 --
133 --
134 --
135 -----------------------------------------------------------------------------
136 FUNCTION preprocess_check (p_payroll_action_id IN NUMBER,
137 p_start_date IN DATE,
138 p_end_date IN DATE,
139 p_business_group_id IN NUMBER
140 ) RETURN BOOLEAN
141 IS
142 lb_return_value BOOLEAN;
143 ln_w2c_paper_asgn_actid number;
144 lv_message_text varchar2(200);
145 lv_message_preprocess varchar2(200);
146
147 cursor get_w2c_paper_assignments (cp_business_group_id in number
148 ,cp_start_date in date
149 ,cp_end_date in date
150 )
151 IS
152 select paa.assignment_Action_id
153 from pay_assignment_actions paa,
154 per_all_assignments_f paf,
155 pay_payroll_actions ppa
156 where ppa.business_group_id = cp_business_group_id
157 and ppa.effective_date between cp_start_date and cp_end_date
158 and ppa.action_type = 'X'
159 and ppa.report_type IN ('W-2C PAPER','W2C_XML')
160 and ppa.action_status = 'C'
161 and ppa.payroll_action_id = paa.payroll_action_id
162 and paf.assignment_id = paa.assignment_id
163 and paf.effective_start_date <= ppa.effective_date
164 and paf.effective_end_date >= ppa.start_date
165 and paf.assignment_type = 'E'
166 and not exists
167 (select 'x' from pay_Action_interlocks pai,
168 pay_assignment_actions paa1,
169 pay_payroll_actions ppa1
170 where pai.locked_action_id = paa.assignment_action_id
171 and paa1.assignment_action_id = pai.locking_action_id
172 and ppa1.payroll_action_id = paa1.payroll_action_id
173 and ppa1.effective_date between cp_start_date and cp_end_date
174 and ppa1.action_type = 'X'
175 and ppa1.report_type = 'MARK_W2C_PAPER'
176 and ppa1.report_category = 'RT'
177 and ppa1.action_status = 'C')
178 and not exists
179 (select 'x' from pay_Action_interlocks pai,
180 pay_assignment_actions paa1,
181 pay_payroll_actions ppa1
182 where pai.locked_action_id = paa.assignment_action_id
183 and paa1.assignment_action_id = pai.locking_action_id
184 and ppa1.payroll_action_id = paa1.payroll_action_id
185 and ppa1.effective_date between cp_start_date and cp_end_date
186 and ppa1.action_type = 'X'
187 and ppa1.report_type = 'W2C'
188 and ppa1.report_qualifier = 'FED'
189 and ppa1.report_category = 'RM'
190 and ppa1.action_status = 'C');
191 BEGIN
192 hr_utility.set_location(gv_package || '.preprocess_check', 10);
193 lb_return_value := TRUE;
194 ln_w2c_paper_asgn_actid := 0;
195 lv_message_text := '';
196 lv_message_preprocess := 'Pre-Process check';
197 --
198 -- Determine whether any W-2c paper assignment action exist to mark
199 -- W-2c Paper and exclude from future tapes. If not log an error message
200 -- for user
201 --
202 OPEN get_w2c_paper_assignments(p_business_group_id,
203 p_start_date,
204 p_end_date);
205 FETCH get_w2c_paper_assignments INTO ln_w2c_paper_asgn_actid;
206 if (get_w2c_paper_assignments%ROWCOUNT = 0
207 or get_w2c_paper_assignments%NOTFOUND )
208 then
209 hr_utility.set_location(gv_package || '.preprocess_check', 20);
210 CLOSE get_w2c_paper_assignments;
211 /* message to user -- unable to find W-2c Paper report
212 to exclude from future tapes */
213 lv_message_text := 'No W-2c paper printed to mark and exclude from Tape';
214 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
215 pay_core_utils.push_token('record_name',lv_message_preprocess);
216 pay_core_utils.push_token('description',lv_message_text);
217 lb_return_value := FALSE;
218 raise hr_utility.hr_error;
219 hr_utility.set_location(gv_package || '.preprocess_check', 30);
220 else
221 CLOSE get_w2c_paper_assignments;
222 lb_return_value := TRUE;
223 hr_utility.set_location(gv_package || '.preprocess_check', 30);
224 end if;
225 hr_utility.set_location(gv_package || '.preprocess_check', 40);
226 return lb_return_value;
227 END preprocess_check;
228 -- End of Function Preprocess_Check
229
230
231 /******************************************************************
232 ** Range Code to pick all the distinct assignment_ids
233 ** that need to be marked as submitted to governement.
234 *******************************************************************/
235 PROCEDURE mark_w2c_range_cursor( p_payroll_action_id in number
236 ,p_sqlstr out nocopy varchar2)
237 IS
238
239 ld_start_date date;
240 ld_end_date date;
241 lv_report_type varchar2(30);
242 lv_report_qualifier varchar2(30);
243 ln_business_group_id number;
244 ln_seq_num number;
245
246 lv_sql_string varchar2(10000);
247 BEGIN
248 hr_utility.set_location(gv_package || '.mark_w2c_range_cursor', 10);
249 get_payroll_action_info(p_payroll_action_id
250 ,ld_start_date
251 ,ld_end_date
252 ,lv_report_type
253 ,lv_report_qualifier
254 ,ln_business_group_id
255 ,ln_seq_num
256 );
257
258 hr_utility.trace('ld_start_date = ' || ld_start_date);
259 hr_utility.trace('ld_end_date = ' || ld_end_date);
260 hr_utility.trace('lv_report_type = ' || lv_report_type);
261 hr_utility.trace('lv_report_qualifier = ' || lv_report_qualifier);
262 hr_utility.trace('ln_business_group_id = ' || ln_business_group_id);
263 hr_utility.trace('ln_seq_num = ' || to_char(ln_seq_num));
264
265 hr_utility.set_location(gv_package || '.mark_w2c_range_cursor', 15);
266 if preprocess_check ( p_payroll_action_id
267 ,ld_start_date
268 ,ld_end_date
269 ,ln_business_group_id
270 )
271 then
272 hr_utility.trace('W-2c paper Assignments exist to process' );
273 else
274 hr_utility.trace('W-2c paper Assignments does not exist to process');
275 end if;
276
277 hr_utility.set_location(gv_package || '.mark_w2c_range_cursor', 20);
278 if lv_report_type = 'MARK_W2C_PAPER' then
279 hr_utility.set_location(gv_package || '.mark_w2c_range_cursor', 30);
280 lv_sql_string :=
281 'select distinct paf.person_id
282 from pay_assignment_actions paa,
283 per_all_assignments_f paf,
284 pay_payroll_actions ppa
285 where ppa.business_group_id = '|| ln_business_group_id || '
286 and ppa.effective_date between to_date(''' ||
287 to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
288 and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
289 and ppa.action_type = ''X''
290 and ppa.report_type IN (''W-2C PAPER'',''W2C_XML'')
291 and ppa.action_status = ''C''
292 and ppa.payroll_action_id = paa.payroll_action_id
293 and paf.assignment_id = paa.assignment_id
294 and paf.effective_start_date <= ppa.effective_date
295 and paf.effective_end_date >= ppa.start_date
296 and paf.assignment_type = ''E''
297 and :payroll_action_id is not null
298 and not exists
299 (select ''x'' from pay_Action_interlocks pai,
300 pay_assignment_actions paa1,
301 pay_payroll_actions ppa1
302 where pai.locked_action_id = paa.assignment_action_id
303 and paa1.assignment_action_id = pai.locking_action_id
304 and ppa1.payroll_action_id = paa1.payroll_action_id
305 and ppa1.effective_date between to_date(''' ||
306 to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
307 and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
308 and ppa1.action_type = ''X''
309 and ppa1.report_type = ''MARK_W2C_PAPER''
310 and ppa1.action_status = ''C'')
311 and not exists
312 (select ''x'' from pay_Action_interlocks pai,
313 pay_assignment_actions paa1,
314 pay_payroll_actions ppa1
315 where pai.locked_action_id = paa.assignment_action_id
316 and paa1.assignment_action_id = pai.locking_action_id
317 and ppa1.payroll_action_id = paa1.payroll_action_id
318 and ppa1.effective_date between to_date(''' ||
319 to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
320 and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
321 and ppa1.action_type = ''X''
322 and ppa1.report_type = ''W2C''
323 and ppa1.report_qualifier = ''FED''
324 and ppa1.action_status = ''C'')';
325 p_sqlstr := lv_sql_string;
326 hr_utility.set_location(gv_package || '.mark_w2c_range_cursor', 40);
327 hr_utility.trace('p_sqlstr = ' ||substr(p_sqlstr,1,100));
328 hr_utility.trace('p_sqlstr = ' ||substr(p_sqlstr,2000,100));
329 hr_utility.trace('length of p_sqlstr <' || to_char(length(p_sqlstr))||'>' );
330 hr_utility.trace('Procedure mark_w2c_range_cursor completed successfully');
331
332 else
333 hr_utility.trace('Procedure mark_w2c_range_cursor Unsucessful ... ');
334 end if;
335
336 end mark_w2c_range_cursor;
337
338 /*******************************************************************
339 ** Action Creation Code to create assignment actions for all the
340 ** the assignment_ids that need to be marked as submitted to governement
341 *******************************************************************/
342 PROCEDURE mark_w2c_action_creation( p_payroll_action_id in number
343 ,p_start_person_id in number
344 ,p_end_person_id in number
345 ,p_chunk in number)
346 IS
347 cursor get_w2c_paper_assignments (cp_business_group_id in number
348 ,cp_start_date in date
349 ,cp_end_date in date
350 ,cp_start_person_id in number
351 ,cp_end_person_id in number)
352 IS
353 select paa.assignment_id,
354 paa.tax_unit_id,
355 paf.person_id,
356 paa.assignment_Action_id
357 from pay_assignment_actions paa,
358 per_all_assignments_f paf,
359 pay_payroll_actions ppa
360 where ppa.business_group_id = cp_business_group_id
361 and ppa.effective_date between cp_start_date and cp_end_date
362 and ppa.action_type = 'X'
363 and ppa.report_type IN ('W-2C PAPER','W2C_XML')
364 and ppa.action_status = 'C'
365 and ppa.payroll_action_id = paa.payroll_action_id
366 and paf.assignment_id = paa.assignment_id
367 and paf.effective_start_date <= ppa.effective_date
368 and paf.effective_end_date >= ppa.start_date
369 and paf.assignment_type = 'E'
370 and paf.person_id between cp_start_person_id
371 and cp_end_person_id
372 and not exists
373 (select 'x' from pay_Action_interlocks pai,
374 pay_assignment_actions paa1,
375 pay_payroll_actions ppa1
376 where pai.locked_action_id = paa.assignment_action_id
377 and paa1.assignment_action_id = pai.locking_action_id
378 and ppa1.payroll_action_id = paa1.payroll_action_id
379 and ppa1.effective_date between cp_start_date and cp_end_date
380 and ppa1.action_type = 'X'
381 and ppa1.report_type = 'MARK_W2C_PAPER'
382 and ppa1.report_category = 'RT'
383 and ppa1.action_status = 'C')
384 and not exists
385 (select 'x' from pay_Action_interlocks pai,
386 pay_assignment_actions paa1,
387 pay_payroll_actions ppa1
388 where pai.locked_action_id = paa.assignment_action_id
389 and paa1.assignment_action_id = pai.locking_action_id
390 and ppa1.payroll_action_id = paa1.payroll_action_id
391 and ppa1.effective_date between cp_start_date and cp_end_date
392 and ppa1.action_type = 'X'
393 and ppa1.report_type = 'W2C'
394 and ppa1.report_qualifier = 'FED'
395 and ppa1.report_category = 'RM'
396 and ppa1.action_status = 'C');
397
398 ld_start_date DATE;
399 ld_end_date DATE;
400 lv_report_type VARCHAR2(30);
401 lv_report_qualifier VARCHAR2(30);
402 ln_business_group_id NUMBER;
403 ln_seq_num NUMBER;
404
405 /* Assignment Record Local Variables */
406 ln_assignment_id number;
407 ln_emp_tax_unit_id number;
408 ln_person_id number;
409 ln_assignment_action_id number;
410
411 PROCEDURE action_creation (lp_person_id in number,
412 lp_assignment_id number,
413 lp_assignment_action_id number,
414 lp_tax_unit_id number,
415 ld_start_date date,
416 ld_end_date date,
417 ln_seq_num number)
418 IS
419
420 cursor ee_details (cp_person_id in number,
421 cp_end_date in date) is
422 select ppf.full_name, ppf.national_identifier, ppf.employee_number,
423 paf.assignment_number
424 from per_all_people_f ppf,
425 per_all_assignments_f paf
426 where ppf.person_id = cp_person_id
427 and paf.person_id = ppf.person_id
428 and cp_end_date between ppf.effective_start_date
429 and ppf.effective_end_date
430 and cp_end_date between paf.effective_start_date
431 and paf.effective_end_date;
432
433
434
435 cursor gre_name (cp_tax_unit_id in varchar2) is
436 SELECT name
437 FROM hr_organization_units
438 WHERE organization_id = cp_tax_unit_id;
439
440 cursor get_paper_details (cp_assignment_action_id in number) is
441 select creation_date
442 from pay_payroll_actions ppa,
443 pay_assignment_actions paa
444 where paa.assignment_action_id = cp_assignment_action_id
445 and ppa.payroll_action_id = paa.payroll_action_id;
446
447
448
449 ln_w2c_asg_action NUMBER := 0;
450 lv_full_name per_all_people_f.full_name%type;
451 lv_national_identifier per_all_people_f.national_identifier%type;
452 lv_employee_number per_all_people_f.employee_number%type;
453 lv_assignment_number per_all_assignments_f.assignment_number%type;
454 lv_gre_name hr_organization_units.name%type;
455 lv_year varchar2(4) ;
456 lv_creation_date varchar2(20) ;
457 lv_sysdate varchar2(20) ;
458
459 BEGIN
460 hr_utility.set_location(gv_package || '.action_creation', 10);
461 /* Create an assignment action for this person */
462
463
464 lv_year := to_char(ld_end_date,'YYYY'); --MOD
465 open ee_details (lp_person_id,ld_end_date);
466 fetch ee_details into lv_full_name,
467 lv_national_identifier,
468 lv_employee_number,
469 lv_assignment_number;
470
471 close ee_details;
472
473
474 open gre_name (lp_tax_unit_id);
475 fetch gre_name into lv_gre_name;
476 close gre_name;
477
478 open get_paper_details (lp_assignment_action_id);
479 fetch get_paper_details into lv_creation_date;
480 close get_paper_details;
481
482 select sysdate into lv_sysdate from dual;
483
484
485 select pay_assignment_actions_s.nextval
486 into ln_w2c_asg_action
487 from dual;
488 hr_utility.set_location(gv_package || '.action_creation', 20);
489 hr_utility.trace('New w2c Action = ' || to_char(ln_w2c_asg_action));
490
491 /* Insert into pay_assignment_actions. */
492 hr_utility.trace('Creating Assignment Action');
493
494 hr_nonrun_asact.insact(ln_w2c_asg_action
495 ,lp_assignment_id
496 ,p_payroll_action_id
497 ,p_chunk
498 ,lp_tax_unit_id);
499
500 /* Update the serial number column with the person id
501 so that the W2C report will not have
502 to do an additional checking against the assignment
503 table */
504
505 hr_utility.set_location(gv_package || '.action_creation', 30);
506 hr_utility.trace('updating asg action');
507 update pay_assignment_actions aa
508 set aa.serial_number = lp_person_id
509 where aa.assignment_action_id = ln_w2c_asg_action;
510
511 /* Interlock the w2c report action with current mark w2c action */
512
513 hr_utility.trace('Locking Action = ' || ln_w2c_asg_action);
514 hr_utility.trace('Locked Action = ' || lp_assignment_action_id);
515 hr_nonrun_asact.insint(ln_w2c_asg_action
516 ,lp_assignment_action_id);
517 hr_utility.set_location(gv_package || '.action_creation', 40);
518
519
520 insert into pay_us_rpt_totals
521 (GRE_NAME,
522 STATE_NAME,
523 ATTRIBUTE1, -- FULL_NAME
524 ATTRIBUTE2, -- NATIONAL_IDENTIFIER
525 ATTRIBUTE3, -- EMPLOYEE_NUMBER
526 ATTRIBUTE4, -- ASSIGNMENT_NUMBER
527 ATTRIBUTE5, -- ASSIGNMENT_ACTION_ID
528 ATTRIBUTE6, -- YEAR
529 SESSION_ID, -- SESSION_ID
530 ATTRIBUTE7, -- PAPER_CREATION_DATE
531 ATTRIBUTE8 -- SYSDATE
532 )
533 VALUES
534 (lv_gre_name,
535 'MARKW2C_PROCESS',
536 lv_full_name,
537 lv_national_identifier,
538 lv_employee_number,
539 lv_assignment_number,
540 lp_assignment_action_id,
541 lv_year,
542 ln_seq_num,
543 lv_creation_date,
544 lv_sysdate
545 );
546
547
548 hr_utility.trace('Inserted lv_gre_name ' || lv_gre_name);
549 hr_utility.trace('Inserted lv_full_name ' || lv_full_name);
550 hr_utility.trace('Inserted lv_natidentifier' ||lv_national_identifier);
551 hr_utility.trace('Inserted lv_employee_number' ||lv_employee_number);
552 hr_utility.trace('Inserted lv_assignment_number' ||lv_assignment_number);
553 hr_utility.trace('Inserted lp_aaid' ||to_char(lp_assignment_action_id));
554
555 end action_creation; -- End of Local function Action_Creation
556 --
557 -- Action Creation Main Logic
558 --
559 begin
560 --{
561 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 10);
562 hr_utility.trace('Entered Mark_W2c_action_creation ');
563 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
564 hr_utility.trace('p_start_person_id = '|| to_char(p_start_person_id));
565 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
566 hr_utility.trace('p_chunk = '|| to_char(p_chunk));
567
568 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 15);
569 get_payroll_action_info(p_payroll_action_id
570 ,ld_start_date
571 ,ld_end_date
572 ,lv_report_type
573 ,lv_report_qualifier
574 ,ln_business_group_id
575 ,ln_seq_num);
576
577 hr_utility.trace('ld_start_date = ' || ld_start_date);
578 hr_utility.trace('ld_end_date = ' || ld_end_date);
579 hr_utility.trace('lv_report_type = ' || lv_report_type);
580 hr_utility.trace('lv_report_qualifier = ' || lv_report_qualifier);
581 hr_utility.trace('ln_business_group_id = ' || ln_business_group_id);
582 hr_utility.trace('ln_seq_num = ' || to_char(ln_seq_num));
583
584 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 20);
585 open get_w2c_paper_assignments (ln_business_group_id
586 ,ld_start_date
587 ,ld_end_date
588 ,p_start_person_id
589 ,p_end_person_id
590 );
591 loop
592 --{
593 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 30);
594 fetch get_w2c_paper_assignments into ln_assignment_id,
595 ln_emp_tax_unit_id,
596 ln_person_id,
597 ln_assignment_action_id;
598
599 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 40);
600
601 if get_w2c_paper_assignments%ROWCOUNT = 0 then
602 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 50);
603 hr_utility.trace('No Person found for reporting in this chunk');
604 end if;
605
606 EXIT WHEN get_w2c_paper_assignments%NOTFOUND;
607
608 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 60);
609 hr_utility.trace('ln_assignment_id =' || ln_assignment_id);
610 hr_utility.trace('ln_emp_tax_unit_id =' || ln_emp_tax_unit_id);
611 hr_utility.trace('ln_person_id =' || ln_person_id);
612 hr_utility.trace('ln_assignment_action_id =' || ln_assignment_action_id);
613
614 if ln_person_id is not null then
615 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 70);
616 action_creation(ln_person_id,
617 ln_assignment_id,
618 ln_assignment_action_id,
619 ln_emp_tax_unit_id,
620 ld_start_date,
621 ld_end_date,
622 ln_seq_num);
623 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 80);
624
625 end if;
626 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 90);
627 --}
628 end loop;
629 close get_w2c_paper_assignments;
630 hr_utility.trace('Action Creation for Mark_W2c_Paper completed Successfully');
631 hr_utility.set_location(gv_package || '.mark_w2c_action_creation', 100);
632 --}
633 end mark_w2c_action_creation;
634 -- End of Procedure mar_w2c_action_creation
635 --alka --
636
637 /*****************************************************************
638 ** This procudure returns the Mandatory Static Labels and the
639 ** Other Additional Static columns.
640 *****************************************************************/
641 FUNCTION employee_header(p_output_file_type in varchar2)
642 RETURN VARCHAR2
643 IS
644
645 lv_format1 varchar2(32000);
646 lv_format2 varchar2(32000);
647
648 BEGIN
649
650 hr_utility.set_location(gv_package || '.formated_static_header', 10);
651 hr_utility.trace('Entered employee_header');
652
653
654 lv_format1 :=
655 pay_us_payroll_utils.formated_data_string (
656 p_input_string => 'GRE Name'
657 ,p_bold => 'Y'
658 ,p_output_file_type => p_output_file_type) ||
659 pay_us_payroll_utils.formated_data_string (
660 p_input_string => 'Employee''s Name'
661 ,p_bold => 'Y'
662 ,p_output_file_type => p_output_file_type) ||
663 pay_us_payroll_utils.formated_data_string (
664 p_input_string => 'Social Security Number'
665 ,p_bold => 'Y'
666 ,p_output_file_type => p_output_file_type) ||
667 pay_us_payroll_utils.formated_data_string (
668 p_input_string => 'Employee Number'
669 ,p_bold => 'Y'
670 ,p_output_file_type => p_output_file_type) ||
671 pay_us_payroll_utils.formated_data_string (
672 p_input_string => 'Assignment Number'
673 ,p_bold => 'Y'
674 ,p_output_file_type => p_output_file_type) ||
675 pay_us_payroll_utils.formated_data_string (
676 p_input_string => 'Paper Creation Date'
677 ,p_bold => 'Y'
678 ,p_output_file_type => p_output_file_type) ;
679
680 hr_utility.trace('Leaving employee_header');
681 RETURN (lv_format1);
682 END employee_header;
683
684
685 FUNCTION employee_data (
686 p_tax_unit_name in varchar2
687 ,p_full_name in varchar2
688 ,p_national_identifier in varchar2
689 ,p_employee_number in varchar2
690 ,p_assignment_number in varchar2
691 ,p_assignment_action_id in varchar2
692 ,p_year in varchar2
693 ,p_creation_date in varchar2
694 ,p_sysdate in varchar2
695 ,p_output_file_type in varchar2 )
696
697 RETURN VARCHAR2
698 IS
699
700 lv_format1 VARCHAR2(32000);
701 lv_format2 VARCHAR2(32000);
702
703
704 BEGIN
705
706 hr_utility.set_location(gv_package || '.formated_static_data', 10);
707 hr_utility.trace('Entered employee_data');
708 hr_utility.trace('ER Name = '||p_tax_unit_name);
709 hr_utility.trace('Year = '||p_year);
710 hr_utility.trace('EE Name = '||p_full_name);
711
712 lv_format1 :=
713 pay_us_payroll_utils.formated_data_string (
714 p_input_string => p_tax_unit_name
715 ,p_output_file_type => p_output_file_type)||
716 pay_us_payroll_utils.formated_data_string (
717 p_input_string => p_full_name
718 ,p_output_file_type => p_output_file_type)||
719 pay_us_payroll_utils.formated_data_string (
720 p_input_string => p_national_identifier
721 ,p_output_file_type => p_output_file_type)||
722 pay_us_payroll_utils.formated_data_string (
723 p_input_string => p_employee_number
724 ,p_output_file_type => p_output_file_type)||
725 pay_us_payroll_utils.formated_data_string (
726 p_input_string => p_assignment_number
727 ,p_output_file_type => p_output_file_type)||
728 pay_us_payroll_utils.formated_data_string (
729 p_input_string => p_creation_date
730 ,p_output_file_type => p_output_file_type) ;
731
732 hr_utility.set_location(gv_package || '.formated_static_data', 20);
733
734
735 hr_utility.trace('Static Data1 = ' || lv_format1);
736 hr_utility.set_location(gv_package || '.formated_static_data', 40);
737 hr_utility.trace('Built employee_data');
738
739 return (lv_format1);
740 hr_utility.trace('Leaving employee_data');
741 END employee_data;
742
743
744 PROCEDURE select_ee_details(errbuf OUT nocopy VARCHAR2,
745 retcode OUT nocopy NUMBER,
746 p_seq_num IN VARCHAR2,
747 p_output_file_type IN VARCHAR2)
748 IS
749
750 cursor c_get_lookup_code(cp_lookup_meaning in varchar2) is
751 select lookup_code from hr_lookups
752 where lookup_type = 'REPORT_OUTPUT_TYPE'
753 and meaning = cp_lookup_meaning
754 and application_id = 800;
755
756 cursor c_ee_details (c_seq_num in VARCHAR2) is
757 SELECT
758 gre_name,
759 attribute1, --full_name,
760 attribute2, --national_identifier,
761 attribute3, -- employee_number,
762 attribute4, -- assignment_number,
763 attribute6, -- year
764 attribute7, -- PAPER_CREATION_DATE
765 attribute8 -- Sysdate
766 FROM pay_us_rpt_totals
767 WHERE state_name = 'MARKW2C_PROCESS'
768 AND session_id = to_number(c_seq_num)
769 ORDER BY attribute6,gre_name, attribute1,attribute4,attribute5;
770
771 lv_gre_name varchar2(240);
772 lv_full_name varchar2(240);
773 lv_ssn varchar2(240);
774 lv_ee_number varchar2(240);
775 lv_asg_number varchar2(240);
776 lv_aaid varchar2(240);
777 lv_year varchar2(240);
778 lv_creation_date varchar2(240);
779 lv_sysdate varchar2(240);
780 lv_data_row varchar2(32000);
781 lv_output_file_type varchar2(240);
782
783 BEGIN
784 hr_utility.trace('Entered Main package');
785 hr_utility.trace('p_seq_num = '||p_seq_num);
786
787 open c_get_lookup_code(p_output_file_type);
788 fetch c_get_lookup_code into lv_output_file_type;
789 close c_get_lookup_code;
790
791
792 OPEN c_ee_details(p_seq_num);
793 hr_utility.trace('Opened c_ee_details');
794 LOOP
795 lv_gre_name := null;
796 lv_full_name := null;
797 lv_ssn := null;
798 lv_ee_number := null;
799 lv_asg_number := null;
800 lv_year := null;
801 lv_creation_date := null;
802 lv_sysdate := null;
803
804 FETCH c_ee_details INTO lv_gre_name,
805 lv_full_name,
806 lv_ssn,
807 lv_ee_number,
808 lv_asg_number,
809 lv_year,
810 lv_creation_date,
811 lv_sysdate;
812
813 hr_utility.trace('Fetched c_ee_details');
814 EXIT WHEN c_ee_details%notfound;
815
816 if c_ee_details%ROWCOUNT =1 THEN
817
818 FND_FILE.PUT_LINE(FND_FILE.OUTPUT
819 ,pay_us_payroll_utils.formated_header_string(
820 gv_title || ' - Tax Year: ' ||
821 lv_year || ' as of '|| lv_sysdate
822 ,lv_output_file_type ));
823
824
825 if lv_output_file_type ='HTML' THEN
826 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
827 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=center>');
828 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
829 end if;
830
831 fnd_file.put_line(fnd_file.output
832 ,employee_header(lv_output_file_type));
833
834 if p_output_file_type ='HTML' then
835 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
836 end if;
837
838 end if;
839
840 lv_data_row := employee_data (
841 p_tax_unit_name => lv_gre_name
842 ,p_full_name => lv_full_name
843 ,p_national_identifier => lv_ssn
844 ,p_employee_number => lv_ee_number
845 ,p_assignment_number => lv_asg_number
846 ,p_assignment_action_id => lv_aaid
847 ,p_year => lv_year
848 ,p_creation_date => lv_creation_date
849 ,p_sysdate => lv_sysdate
850 ,p_output_file_type => lv_output_file_type);
851
852 if p_output_file_type ='HTML' then
853 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
854 end if;
855
856 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
857
858 END LOOP;
859 CLOSE c_ee_details;
860
861 if p_output_file_type ='HTML' then
862 UPDATE fnd_concurrent_requests
863 SET output_file_type = 'HTML'
864 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
865 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
866 end if;
867
868 DELETE FROM pay_us_rpt_totals
869 WHERE session_id = to_number(p_seq_num);
870
871 END select_ee_details;
872
873 Begin
874 --hr_utility.trace_on(null,'MARKW2C');
875 gv_package := 'pay_us_mark_w2c_paper';
876 gv_title := 'Assignments Marked to be Excluded from W-2c Tape';
877 END pay_us_mark_w2c_paper;