1 package pay_us_sqwl_archive AUTHID CURRENT_USER as
2 /* $Header: pyussqwl.pkh 120.3.12020000.3 2013/01/29 05:00:24 sjawid ship $ */
3 --
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
8 * Chertsey, England. *
9 * *
10 * All rights reserved. *
11 * *
12 * This material has been provided pursuant to an agreement *
13 * containing restrictions on its use. The material is also *
14 * protected by copyright law. No part of this material may *
15 * be copied or distributed, transmitted or transcribed, in *
16 * any form or by any means, electronic, mechanical, magnetic, *
17 * manual, or otherwise, or disclosed to third parties without *
18 * the express written permission of Oracle Corporation UK Ltd, *
19 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
20 * England. *
21 * *
22 ******************************************************************
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ----------- ---------- ----- ------- -----------------------------------
28 06-MAY-1998 NBRISTOW
29 08-AUG-98 achauhan Added rout nocopy ines for the Year End Pre-Process
30 04-DEC-98 vmehta Changed definition for
31 check_residence_state
32 27-OCT-99 rpotnuru 110.0 Created from existing file and
33 Added two global variables g_sqwl_state and
34 g_sqwl_jursd to fix NY burroughs problem
35 03-DEC-99 rpotnuru 110.1 Added a function update_ff_archive_items
36 which will update the value to 0 in case of 4th Qtr
37 NY sqwl in case the employee doesnt have balances
38 for 4th Qtr.
39
40 10-FEB-2000 ashgupta 40.2 Added the global variable g_report_cat
41 This variable is set in the archinit
42 proc and is used in archive_Data proc.
43 This contains the category of the
44 SQWL i.e. RTM/RTS for the City of
45 Oakland. Enhancement Req 1063413
46 13-JUN-2000 asasthan 115.3 Q2 2000 changes in 11i.
47 05-JUN-2001 tclewis 115.4 Added procedure archive_asg_locs. This
48 will archive the Assignment locations as of
49 the 12th of the month, for each month of the
50 quarter.
51 21-FEB-2002 asasthan 115.6 Added dbdrv and checkfile.
52 21-FEB-2002 asasthan 115.7 Removed previous EOY specific procedures
53 not required by SQWLs.
54 These are :
55
56 PROCEDURE EOY_RANGE_CURSOR
57 PROCEDURE EOY_ACTION_CREATION
58 PROCEDURE EOY_ARCHIVE_DATA
59 PROCEDURE EOY_ARCHINIT
60 06-AUG-2003 fusman 115.8 3094891 Moved all the sqwl cursors to pay_us_sqwl_archive package header.
61 11-Jan-2005 sackumar 115.11 4869678 Modified the cursor sqwl_employer_m to remove Merge Join Cartesian
62 Modified the cursor sqwl_employee_m to remove Merge Join Cartesian
63 Modified the cursor mmrf_nysqwl_employer to remove Full Table Scan on
64 hr_all_organization_units and hr_organization_information.
65 Also replaced per_all_people_f by per_people_f
66 and per_all_assignment_f by per_assignment_f to reduce the shared memory.
67 16-Aug-2006 sackumar 115.12 5379670 Created a global cursor MESQWL_RECONCILIATION.
68 28-JUN-11 rosuri 115.13 12664972 Modified the cursor mmrf_sqwl_employer.
69 25-JAN-13 sjawid 115.14 14456648 Added cursor smwl_employee
70 29-JAN-13 sjawid 115.15 14456648 Modified cursor smwl_employee
71 */
72 -- ***********SQWL Cursors Begin *************************
73
74 -- 'level_cnt' will allow the cursors to select function results,
75 -- whether it is a standard fuction such as to_char or a function
76 -- defined in a package (with the correct pragma restriction).
77
78 level_cnt NUMBER;
79
80 --
81 -- Used by most states for State Quarterly Wage Listing.
82 --
83 -- Sets up the tax unit context for each employer to be reported on NB. sets
84 -- up a parameter holding the tax unit identifier which can then be used by
85 -- subsequent cursors to restrict to employees within the employer. The
86 -- payroll action id context is used for the Archive DB Items.
87 -- The Date_Earned Context is used for balances with dimensions of
88 -- "GRE_JD_QTD" -- Notably Pennsylvania SUI_EE_GROSS. Added join to payroll
89 -- action table.
90 --
91 cursor sqwl_employer is
92 select distinct
93 'PAYROLL_ACTION_ID=C', AA.payroll_action_id,
94 'TAX_UNIT_ID=C' , AA.tax_unit_id,
95 'TAX_UNIT_ID=P' , AA.tax_unit_id,
96 'JURISDICTION_CODE=C', SR.jurisdiction_code,
97 'DATE_EARNED=C' ,fnd_date.date_to_canonical(PA.effective_date),
98 'BUSINESS_GROUP_ID=C', PA.business_group_id,
99 'TRANSFER_BUSINESS_GROUP_ID=P', PA.business_group_id
100 from pay_state_rules SR,
101 pay_assignment_actions AA,
102 pay_payroll_actions PA
103 where AA.payroll_action_id = pay_magtape_generic.get_parameter_value
104 ('TRANSFER_PAYROLL_ACTION_ID')
105 and PA.payroll_action_id = AA.payroll_action_id
106 and SR.state_code = pay_magtape_generic.get_parameter_value
107 ('TRANSFER_STATE');
108
109 -- Used by California (Multi Wage Plan) for State Quarterly Wage Listing.
110 -- Added by Ashu Gupta (ashgupta) on 10-FEB-2000
111 --
112 -- Sets up the tax unit context for each employer to be reported on NB. sets
113 -- up a parameter holding the tax unit identifier which can then be used by
114 -- subsequent cursors to restrict to employees within the employer. The
115 -- payroll action id context is used for the Archive DB Items.
116 -- The Date_Earned Context is used for balances with dimensions of
117 -- "GRE_JD_QTD" -- Notably Pennsylvania SUI_EE_GROSS. Added join to payroll
118 -- action table. The order by clause is added in the SQL, so that all the
119 -- records of a GRE come together.
120 --
121 cursor sqwl_employer_m is
122 select distinct
123 'PAYROLL_ACTION_ID=C', AA.payroll_action_id,
124 'TAX_UNIT_ID=C' , AA.tax_unit_id,
125 'TAX_UNIT_ID=P' , AA.tax_unit_id,
126 'JURISDICTION_CODE=C', SR.jurisdiction_code,
127 'DATE_EARNED=C' , fnd_date.date_to_canonical(PA.effective_date),
128 'BUSINESS_GROUP_ID=C', PA.business_group_id,
129 'TRANSFER_COMPANY_SUI_ID=P', hoi.org_information2,
130 'TRANSFER_WAGE_PLAN_CODE=P', hoi.org_information3,
131 'TRANSFER_REPORT_FORMAT=P', pay_magtape_generic.get_parameter_value
132 ('TRANSFER_REPORT_CATEGORY')
133 from pay_payroll_actions PA,
134 pay_assignment_actions AA,
135 pay_state_rules SR,
136 hr_organization_information hoi
137 where AA.payroll_action_id = pay_magtape_generic.get_parameter_value
138 ('TRANSFER_PAYROLL_ACTION_ID')
139 and PA.payroll_action_id = AA.payroll_action_id
140 and SR.state_code = pay_magtape_generic.get_parameter_value
141 ('TRANSFER_STATE')
142 and hoi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
143 and hoi.organization_id = AA.tax_unit_id
144 and hoi.org_information1 = pay_magtape_generic.get_parameter_value
145 ('TRANSFER_STATE')
146 and EXISTS (SELECT /*+ ordered */NULL
147 FROM pay_assignment_actions paa,
148 ff_archive_items fai,
149 ff_user_entities fue
150 WHERE fai.context1 = paa.assignment_action_id
151 AND paa.payroll_action_id = AA.payroll_action_id
152 AND fue.user_entity_id = fai.user_entity_id
153 AND fue.user_entity_name = 'A_SCL_ASG_US_CA_WAGE_PLAN_CODE'
154 AND paa.tax_unit_id = AA.tax_unit_id
155 AND fai.value = hoi.org_information3 )
156 order by 4 ;
157
158
159 --
160 -- Used by most states for State Quarterly Wage Listing.
161 --
162 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
163 -- for an employee. The date_earned context is set to be the least of the
164 -- end of the period being reported and the maximum end date of the
165 -- assignment. This ensures that personal information ie. name etc... is
166 -- current relative to the period being reported on.
167 --
168 cursor sqwl_transmitter is
169 select 'TAX_UNIT_ID=C',
170 pay_magtape_generic.get_parameter_value
171 ('TRANSFER_TRANS_LEGAL_CO_ID'),
172 'JURISDICTION_CODE=C',
173 SR.jurisdiction_code,
174 'PAYROLL_ACTION_ID=C',
175 pay_magtape_generic.get_parameter_value
176 ('TRANSFER_PAYROLL_ACTION_ID'),
177 'TRANSFER_SUI_WAGE_BASE=P',
178 nvl(FFAI.value,' ')
179 from pay_state_rules SR,
180 ff_archive_items ffai,
181 ff_database_items fdi
182 where SR.state_code = pay_magtape_generic.get_parameter_value
183 ('TRANSFER_STATE')
184 and ffai.user_entity_id = fdi.user_entity_id
185 and fdi.user_name = 'A_SUI_TAXABLE_WAGE_BASE'
186 and ffai.context1 = pay_magtape_generic.get_parameter_value
187 ('TRANSFER_PAYROLL_ACTION_ID');
188
189 cursor sqwl_employee is
190 select 'TRANSFER_ASS_ACTION_ID=C', AA.assignment_action_id,
191 'ASSIGNMENT_ACTION_ID=C' , AA.assignment_action_id,
192 'ASSIGNMENT_ID=C' , AA.assignment_id,
193 'ASSIGNMENT_ID=P' , AA.assignment_id, /* Bug 976472 */
194 'DATE_EARNED=C' ,fnd_date.date_to_canonical(pay_magtape_generic.date_earned
195 (PA.effective_date, AA.assignment_id))
196 from per_all_people_f PE,
197 per_all_assignments_f SS,
198 pay_assignment_actions AA,
199 pay_payroll_actions PA
200 where PA.payroll_action_id = pay_magtape_generic.get_parameter_value
201 ('TRANSFER_PAYROLL_ACTION_ID')
202 and AA.payroll_action_id = PA.payroll_action_id
203 and AA.tax_unit_id = pay_magtape_generic.get_parameter_value
204 ('TAX_UNIT_ID')
205 and SS.assignment_id = AA.assignment_id
206 and PE.person_id = SS.person_id
207 /* commented for bug 2464463
208 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
209 SS.effective_start_date and SS.effective_end_date
210 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
211 PE.effective_start_date and PE.effective_end_date
212 */
213
214 /* Added for bug 2464463 */
215 AND SS.effective_start_date =
216 (select max(paf2.effective_start_date)
217 from per_all_assignments_f paf2
218 where paf2.assignment_id = SS.assignment_id
219 and paf2.effective_start_date <= PA.effective_date
220 and paf2.assignment_type = 'E')
221 AND SS.effective_end_date >= PA.start_date
222 AND SS.assignment_type = 'E'
223 AND LEAST(SS.effective_end_date, PA.effective_date)
224 between PE.effective_start_date and PE.effective_end_date
225 /* End of Change for bug 2464463 */
226 order by PE.last_name, PE.first_name, PE.middle_names;
227
228 /*Bug:14456648 : This cursor used in SMWL process (IL state). */
229
230 cursor smwl_employee is
231 select 'TRANSFER_ASS_ACTION_ID=C', AA.assignment_action_id,
232 'ASSIGNMENT_ACTION_ID=C' , AA.assignment_action_id,
233 'ASSIGNMENT_ID=C' , AA.assignment_id,
234 'ASSIGNMENT_ID=P' , AA.assignment_id, /* Bug 976472 */
235 'DATE_EARNED=C' ,fnd_date.date_to_canonical(pay_magtape_generic.date_earned
236 (PA.effective_date, AA.assignment_id))
237 from per_all_people_f PE,
238 per_all_assignments_f SS,
239 pay_assignment_actions AA,
240 pay_payroll_actions PA
241 where PA.payroll_action_id = pay_magtape_generic.get_parameter_value
242 ('TRANSFER_PAYROLL_ACTION_ID')
243 and AA.payroll_action_id = PA.payroll_action_id
244 and AA.tax_unit_id = pay_magtape_generic.get_parameter_value
245 ('TAX_UNIT_ID')
246 and SS.assignment_id = AA.assignment_id
247 and PE.person_id = SS.person_id
248 AND nvl(AA.serial_number,'0') <> 'X'
249 AND SS.effective_start_date =
250 (select max(paf2.effective_start_date)
251 from per_all_assignments_f paf2
252 where paf2.assignment_id = SS.assignment_id
253 and paf2.effective_start_date <= PA.effective_date
254 and paf2.assignment_type = 'E')
255 AND SS.effective_end_date >= PA.start_date
256 AND SS.assignment_type = 'E'
257 AND LEAST(SS.effective_end_date, PA.effective_date)
258 between PE.effective_start_date and PE.effective_end_date
259 order by PE.last_name, PE.first_name, PE.middle_names;
260
261
262
263 -- Used in case the report category is RTM
264 -- This cursor expects that every person will have at least a single row
265 -- in ff_archive_items table for wage plan code. Added by ashgupta on
266 -- 10-FEB-2000 for enhancement request req 1063413
267 cursor sqwl_employee_m is
268 select /*+ ORDERED */ 'TRANSFER_ASS_ACTION_ID=C', AA.assignment_action_id,
269 'ASSIGNMENT_ACTION_ID=C' , AA.assignment_action_id,
270 'ASSIGNMENT_ID=C' , AA.assignment_id,
271 'ASSIGNMENT_ID=P' , AA.assignment_id,
272 'DATE_EARNED=C' , fnd_date.date_to_canonical(pay_magtape_generic.date_earned
273 (PA.effective_date,
274 AA.assignment_id)),
275 'TRANSFER_WAGE_PLAN_CODE=P',pay_magtape_generic.get_parameter_value
276 ('TRANSFER_WAGE_PLAN_CODE'),
277 'TRANSFER_REPORT_FORMAT=P', pay_magtape_generic.get_parameter_value
278 ('TRANSFER_REPORT_CATEGORY')
279 from pay_payroll_actions PA,
280 pay_assignment_actions AA,
281 per_all_assignments_f SS,
282 per_all_people_f PE,
283 ff_archive_items fai,
284 ff_user_entities fue
285 where PA.payroll_action_id = pay_magtape_generic.get_parameter_value
286 ('TRANSFER_PAYROLL_ACTION_ID')
287 and AA.payroll_action_id = PA.payroll_action_id
288 and AA.tax_unit_id = pay_magtape_generic.get_parameter_value
289 ('TAX_UNIT_ID')
290 and SS.assignment_id = AA.assignment_id
291 and PE.person_id = SS.person_id
292 /* commented for bug 2464463
293 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
294 SS.effective_start_date and SS.effective_end_date
295 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
296 PE.effective_start_date and PE.effective_end_date
297 */
298 /* Added for bug 2464463 */
299 AND SS.effective_start_date =
300 (select max(paf2.effective_start_date)
301 from per_all_assignments_f paf2
302 where paf2.assignment_id = SS.assignment_id
303 and paf2.effective_start_date <= PA.effective_date
304 and paf2.assignment_type = 'E')
305 AND SS.effective_end_date >= PA.start_date
306 AND SS.assignment_type = 'E'
307 AND LEAST(SS.effective_end_date, PA.effective_date)
308 between PE.effective_start_date and PE.effective_end_date
309 /* End of Change for bug 2464463 */
310 AND aa.assignment_action_id = fai.context1
311 and fai.value =
312 pay_magtape_generic.get_parameter_value('TRANSFER_WAGE_PLAN_CODE')
313 and fai.user_entity_id = fue.user_entity_id
314 and fue.user_entity_name = 'A_SCL_ASG_US_CA_WAGE_PLAN_CODE'
315 and NOT EXISTS (SELECT value
316 FROM ff_archive_items fai1
317 WHERE fai1.context1 = fai.context1
318 AND fai1.value = fai.value
319 AND fai1.archive_item_id > fai.archive_item_id
320 AND fai1.user_entity_id = fai.user_entity_id)
321 order by PE.last_name, PE.first_name, PE.middle_names;
322
323
324 /**** Bug 976472 *********/
325 --
326 -- Used by NY state for State Quarterly Wage Listing.
327 --
328 -- Sets up the Jurisdiction Code (for NY City and 5 burroughs) contexts
329 -- for an employee. The date_earned context is set to be the least of the
330 -- end of the period being reported and the maximum end date of the
331 -- assignment. This ensures that personal information ie. name etc... is
332 -- current relative to the period being reported on.
333 /******
334 cursor sqwl_employee_jurisdiction is
335 Select distinct
336 'JURISDICTION_CODE=C', pcty.jurisdiction_code
337 from pay_us_emp_city_tax_rules_f pcty,
338 per_assignments_f paf1,
339 per_assignments_f paf
340 where paf.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
341 and paf.effective_end_date >=
342 to_date('01-01-2001','DD-MM-YYYY')
343 and paf.effective_start_date <=
344 to_date('31-03-2001','DD-MM-YYYY')
345 and paf1.person_id = paf.person_id
346 and paf1.effective_end_date >=
347 to_date('01-01-2001','DD-MM-YYYY')
348 and paf1.effective_start_date <=
349 to_date('31-03-2001','DD-MM-YYYY')
350 and pcty.assignment_id = paf1.assignment_id
351 and pcty.effective_start_date <=
352 to_date('31-03-2001','DD-MM-YYYY')
353 and pcty.effective_end_date >=
354 to_date('01-01-2001','DD-MM-YYYY')
355 and pcty.jurisdiction_code in ('33-005-2010',
356 '33-047-2010',
357 '33-061-2010',
358 '33-081-2010',
359 '33-085-2010',
360 '33-119-3230');
361
362 *****/
363 cursor sqwl_employee_jurisdiction is
364 /* commented for bug 2852640
365 Select distinct
366 'JURISDICTION_CODE=C', pcty.jurisdiction_code
367 from pay_us_emp_city_tax_rules_f pcty,
368 per_assignments_f paf1,
369 per_assignments_f paf
370 where paf.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
371 and paf.effective_end_date >= (
372 select
373 decode(
374 to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER') ,'MMYYYY'),'Q'),
375 '4',
376 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Y'),
377 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Q')
378 )
379 from dual
380 )
381 and paf.effective_start_date <=
382 to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE'),'DD-MM-YYYY')
383 and paf1.person_id = paf.person_id
384 and paf1.effective_end_date >=(
385 select
386 decode(
387 to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER') ,'MMYYYY'),'Q'),
388 '4',
389 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Y'),
390 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Q')
391 )
392 from dual
393 )
394 and paf1.effective_start_date <=
395 to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE'),'DD-MM-YYYY')
396 and pcty.assignment_id = paf1.assignment_id
397 and pcty.effective_start_date <=
398 to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE'),'DD-MM-YYYY')
399 and pcty.effective_end_date >=(
400 select
401 decode(
402 to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER') ,'MMYYYY'),'Q'),
403 '4',
404 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Y'),
405 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Q')
406 )
407 from dual
408 )
409 and pcty.jurisdiction_code in ('33-005-2010',
410 '33-047-2010',
411 '33-061-2010',
412 '33-081-2010',
413 '33-085-2010',
414 '33-119-3230');
415 */
416 select distinct 'JURISDICTION_CODE=C', context
417 from ff_archive_items fai,
418 ff_archive_item_contexts faic,
419 pay_assignment_actions paa,
420 pay_payroll_actions ppa
421 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
422 and ppa.payroll_action_id = paa.payroll_action_id
423 and paa.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
424 and fai.context1 = paa.assignment_action_id
425 and faic.archive_item_id = fai.archive_item_id
426 and faic.context in ('33-005-2010',
427 '33-047-2010',
428 '33-061-2010',
429 '33-081-2010',
430 '33-085-2010',
431 '33-119-3230');
432
433 /**** End Bug 976472*****/
434 --
435
436 /* added for MMREF SQWLs */
437
438 /* for bug 2752145, commented the join with hr_organization_information,
439 this to remove the dependency on W2 reporting rules for SQWL */
440 cursor mmrf_sqwl_transmitter is
441 select 'TAX_UNIT_ID=C',
442 pay_magtape_generic.get_parameter_value ('TRANSFER_TRANS_LEGAL_CO_ID'),
443 'JURISDICTION_CODE=C', SR.jurisdiction_code,
444 'TRANSFER_JD=P', SR.jurisdiction_code,
445 'ASSIGNMENT_ID=C' , '-1',
446 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
447 'PAYROLL_ACTION_ID=C',
448 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),
449 -- 'TRANSFER_2678_FILER=P', HOI.org_information8,
450 'BUSINESS_GROUP_ID=C' , PPA.business_group_id,
451 'TRANSFER_BUSINESS_GROUP_ID=P',PPA.business_group_id,
452 'TRANSFER_SUI_WAGE_BASE=P', nvl(FFAI.value,' '),
453 'TRANSFER_REPORT_CATEGORY=P', pay_magtape_generic.get_parameter_value
454 ('TRANSFER_REPORT_CATEGORY')
455 from pay_state_rules SR,
456 ff_archive_items ffai,
457 ff_database_items fdi,
458 -- hr_organization_information hoi,
459 pay_payroll_actions ppa
460 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value
461 ('TRANSFER_PAYROLL_ACTION_ID')
462 -- and hoi.organization_id = pay_magtape_generic.get_parameter_value
463 -- ('TRANSFER_TRANS_LEGAL_CO_ID')
464 -- and hoi.org_information_context = 'W2 Reporting Rules'
465 and SR.state_code = pay_magtape_generic.get_parameter_value
466 ('TRANSFER_STATE')
467 and ffai.user_entity_id = fdi.user_entity_id
468 and fdi.user_name = 'A_SUI_TAXABLE_WAGE_BASE'
469 and ffai.context1 = pay_magtape_generic.get_parameter_value
470 ('TRANSFER_PAYROLL_ACTION_ID');
471
472
473
474 cursor mmrf_sqwl_employer is
475 select distinct
476 'PAYROLL_ACTION_ID=C', AA.payroll_action_id,
477 'TAX_UNIT_ID=C' , AA.tax_unit_id,
478 'TAX_UNIT_ID=P' , AA.tax_unit_id,
479 'DATE_EARNED=C' , fnd_date.date_to_canonical(PA.effective_date),
480 'TAX_UNIT_NAME=P' , substr(hou.name,1,81)
481 from hr_all_organization_units hou,
482 pay_assignment_actions AA,
483 pay_payroll_actions PA
484 where AA.payroll_action_id = pay_magtape_generic.get_parameter_value
485 ('TRANSFER_PAYROLL_ACTION_ID')
486 and PA.payroll_action_id = AA.payroll_action_id
487 and AA.tax_unit_id = hou.organization_id
488 order by substr(hou.name,1,81); /* Bug 12664972 internal_prm_values table in package pay_mag_tape
489 is a table of varchar(81). The value of TAX_UNIT_NAME returned from this cursor gooes into this
490 table. So restricting the size of the columns*/
491
492 --sackumar
493 cursor mmrf_nysqwl_employer is
494 select /*+ index (hoi hr_organization_informatio_FK1)
495 index(hou hr_organization_units_PK)
496 */ distinct
497 'PAYROLL_ACTION_ID=C', AA.payroll_action_id,
498 'TAX_UNIT_ID=C' , AA.tax_unit_id,
499 'TAX_UNIT_ID=P' , AA.tax_unit_id,
500 'DATE_EARNED=C' , fnd_date.date_to_canonical(PA.effective_date),
501 'TAX_UNIT_NAME=P' , hou.name,
502 'FEIN=P' , hoi.org_information1
503 from hr_all_organization_units hou,
504 hr_organization_information hoi,
505 pay_assignment_actions AA,
506 pay_payroll_actions PA
507 where AA.payroll_action_id = pay_magtape_generic.get_parameter_value
508 ('TRANSFER_PAYROLL_ACTION_ID')
509 and PA.payroll_action_id = AA.payroll_action_id
510 and AA.tax_unit_id = hou.organization_id
511 and hoi.organization_id = hou.organization_id
512 and hoi.org_information_context = 'Employer Identification'
513 order by hoi.org_information1;
514
515 cursor mmrf_sqwl_employee is
516 select 'TRANSFER_ASS_ACTION_ID=C', AA.assignment_action_id,
517 'ASSIGNMENT_ACTION_ID=C' , AA.assignment_action_id,
518 'ASSIGNMENT_ID=C' , AA.assignment_id,
519 'ASSIGNMENT_ID=P' , AA.assignment_id, /* Bug 976472 */
520 'DATE_EARNED=C' ,fnd_date.date_to_canonical(pay_magtape_generic.date_earned(PA.effective_date, AA.assignment_id))
521 from per_all_people_f PE,
522 per_all_assignments_f SS,
523 pay_assignment_actions AA,
524 pay_payroll_actions PA
525 where PA.payroll_action_id = pay_magtape_generic.get_parameter_value
526 ('TRANSFER_PAYROLL_ACTION_ID')
527 and AA.payroll_action_id = PA.payroll_action_id
528 and AA.tax_unit_id = pay_magtape_generic.get_parameter_value
529 ('TAX_UNIT_ID')
530 and SS.assignment_id = AA.assignment_id
531 and PE.person_id = SS.person_id
532 /* commented for bug 2464463
533 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
534 SS.effective_start_date and SS.effective_end_date
535 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
536 PE.effective_start_date and PE.effective_end_date
537 */
538 /* Added for bug 2464463 */
539 AND SS.effective_start_date =
540 (select max(paf2.effective_start_date)
541 from per_assignments_f paf2
542 where paf2.assignment_id = SS.assignment_id
543 and paf2.effective_start_date <= PA.effective_date
544 and paf2.assignment_type = 'E')
545 AND SS.effective_end_date >= PA.start_date
546 AND SS.assignment_type = 'E'
547 AND LEAST(SS.effective_end_date, PA.effective_date)
548 between PE.effective_start_date and PE.effective_end_date
549 /* End of Change for bug 2464463 */
550 order by PE.last_name, PE.first_name, PE.middle_names;
551
552 /*Bug # 5379670*/
553 cursor sqwl_reconciliation is
554 select 'TRANSFER_DATE_WAGES_PAID_ME=P', hoi.org_information2,
555 'TRANSFER_AMOUNT_WITHHELD_ME=P', hoi.org_information3,
556 'TRANSFER_PAYMENT_DEPOSITED_ME=P', hoi.org_information4
557 from pay_state_rules SR,
558 hr_organization_information hoi
559 where SR.state_code = pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
560 and hoi.org_information_context = 'SQWL Employer Rules 3'
561 and hoi.organization_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
562 and hoi.org_information1 = pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
563 and to_date(hoi.org_information2,'YYYY/MM/DD HH24:MI:SS') between
564 add_months(last_day(to_date(
565 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER')
566 ,'MMYYYY')),-3) + 1
567 and last_day(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY')) ;
568
569 -- ***********SQWL Cursors Ends *************************
570
571 TYPE char240_data_type_table IS TABLE OF VARCHAR2(240)
572 INDEX BY BINARY_INTEGER;
573 TYPE number_data_type_table IS TABLE OF NUMBER
574 INDEX BY BINARY_INTEGER;
575 g_min_chunk number:= -1;
576 g_archive_flag varchar2(1) := 'N';
577
578 /* Bug 976472 */
579 g_sqwl_state varchar2(2);
580 g_sqwl_jursd varchar2(11);
581 /* End Bug 976472 */
582
583 /* Added by Ashu Gupta on 10-FEB-2000 */
584 g_report_cat pay_report_format_mappings_f.report_category%TYPE;
585
586 procedure range_cursor(pactid in number,
587 sqlstr out nocopy varchar2);
588 procedure action_creation(pactid in number,
589 stperson in number,
590 endperson in number,
591 chunk in number);
592 FUNCTION check_residence_state (
593 p_assignment_id NUMBER,
594 p_period_start DATE,
595 p_period_end DATE,
596 p_state VARCHAR2,
597 p_effective_end_date DATE
598 ) RETURN BOOLEAN;
599
600 procedure archive_data(p_assactid in number, p_effective_date in date);
601 procedure archinit(p_payroll_action_id in number);
602 FUNCTION Update_ff_archive_items (
603 p_payroll_action_id in VARCHAR2
604 )
605 return varchar;
606 /* Bug 773937 */
607 procedure archive_gre_data(p_payroll_action_id in number,
608 p_tax_unit_id in number);
609 /* End of Bug 773937 */
610
611 procedure archive_asg_locs( p_asg_act_id in number
612 ,p_pay_act_id in number
613 ,p_asg_id in number);
614
615 --
616 end pay_us_sqwl_archive;
[Home] [Help]