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