1 package pay_us_sqwl_archive as
2 /* $Header: pyussqwl.pkh 120.2.12010000.1 2008/07/27 23:56:48 appldev 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
69 */
70 -- ***********SQWL Cursors Begin *************************
71
72 -- 'level_cnt' will allow the cursors to select function results,
73 -- whether it is a standard fuction such as to_char or a function
74 -- defined in a package (with the correct pragma restriction).
75
76 level_cnt NUMBER;
77
78 --
79 -- Used by most states for State Quarterly Wage Listing.
80 --
81 -- Sets up the tax unit context for each employer to be reported on NB. sets
82 -- up a parameter holding the tax unit identifier which can then be used by
83 -- subsequent cursors to restrict to employees within the employer. The
84 -- payroll action id context is used for the Archive DB Items.
85 -- The Date_Earned Context is used for balances with dimensions of
86 -- "GRE_JD_QTD" -- Notably Pennsylvania SUI_EE_GROSS. Added join to payroll
87 -- action table.
88 --
89 cursor sqwl_employer is
90 select distinct
91 'PAYROLL_ACTION_ID=C', AA.payroll_action_id,
92 'TAX_UNIT_ID=C' , AA.tax_unit_id,
93 'TAX_UNIT_ID=P' , AA.tax_unit_id,
94 'JURISDICTION_CODE=C', SR.jurisdiction_code,
95 'DATE_EARNED=C' ,fnd_date.date_to_canonical(PA.effective_date),
96 'BUSINESS_GROUP_ID=C', PA.business_group_id,
97 'TRANSFER_BUSINESS_GROUP_ID=P', PA.business_group_id
98 from pay_state_rules SR,
99 pay_assignment_actions AA,
100 pay_payroll_actions PA
101 where AA.payroll_action_id = pay_magtape_generic.get_parameter_value
102 ('TRANSFER_PAYROLL_ACTION_ID')
103 and PA.payroll_action_id = AA.payroll_action_id
104 and SR.state_code = pay_magtape_generic.get_parameter_value
105 ('TRANSFER_STATE');
106
107 -- Used by California (Multi Wage Plan) for State Quarterly Wage Listing.
108 -- Added by Ashu Gupta (ashgupta) on 10-FEB-2000
109 --
110 -- Sets up the tax unit context for each employer to be reported on NB. sets
111 -- up a parameter holding the tax unit identifier which can then be used by
112 -- subsequent cursors to restrict to employees within the employer. The
113 -- payroll action id context is used for the Archive DB Items.
114 -- The Date_Earned Context is used for balances with dimensions of
115 -- "GRE_JD_QTD" -- Notably Pennsylvania SUI_EE_GROSS. Added join to payroll
116 -- action table. The order by clause is added in the SQL, so that all the
117 -- records of a GRE come together.
118 --
119 cursor sqwl_employer_m is
120 select distinct
121 'PAYROLL_ACTION_ID=C', AA.payroll_action_id,
122 'TAX_UNIT_ID=C' , AA.tax_unit_id,
123 'TAX_UNIT_ID=P' , AA.tax_unit_id,
124 'JURISDICTION_CODE=C', SR.jurisdiction_code,
125 'DATE_EARNED=C' , fnd_date.date_to_canonical(PA.effective_date),
126 'BUSINESS_GROUP_ID=C', PA.business_group_id,
127 'TRANSFER_COMPANY_SUI_ID=P', hoi.org_information2,
128 'TRANSFER_WAGE_PLAN_CODE=P', hoi.org_information3,
129 'TRANSFER_REPORT_FORMAT=P', pay_magtape_generic.get_parameter_value
130 ('TRANSFER_REPORT_CATEGORY')
131 from pay_payroll_actions PA,
132 pay_assignment_actions AA,
133 pay_state_rules SR,
134 hr_organization_information hoi
135 where AA.payroll_action_id = pay_magtape_generic.get_parameter_value
136 ('TRANSFER_PAYROLL_ACTION_ID')
137 and PA.payroll_action_id = AA.payroll_action_id
138 and SR.state_code = pay_magtape_generic.get_parameter_value
139 ('TRANSFER_STATE')
140 and hoi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
141 and hoi.organization_id = AA.tax_unit_id
142 and hoi.org_information1 = pay_magtape_generic.get_parameter_value
143 ('TRANSFER_STATE')
144 and EXISTS (SELECT /*+ ordered */NULL
145 FROM pay_assignment_actions paa,
146 ff_archive_items fai,
147 ff_user_entities fue
148 WHERE fai.context1 = paa.assignment_action_id
149 AND paa.payroll_action_id = AA.payroll_action_id
150 AND fue.user_entity_id = fai.user_entity_id
151 AND fue.user_entity_name = 'A_SCL_ASG_US_CA_WAGE_PLAN_CODE'
152 AND paa.tax_unit_id = AA.tax_unit_id
153 AND fai.value = hoi.org_information3 )
154 order by 4 ;
155
156
157 --
158 -- Used by most states for State Quarterly Wage Listing.
159 --
160 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
161 -- for an employee. The date_earned context is set to be the least of the
162 -- end of the period being reported and the maximum end date of the
163 -- assignment. This ensures that personal information ie. name etc... is
164 -- current relative to the period being reported on.
165 --
166 cursor sqwl_transmitter is
167 select 'TAX_UNIT_ID=C',
168 pay_magtape_generic.get_parameter_value
169 ('TRANSFER_TRANS_LEGAL_CO_ID'),
170 'JURISDICTION_CODE=C',
171 SR.jurisdiction_code,
172 'PAYROLL_ACTION_ID=C',
173 pay_magtape_generic.get_parameter_value
174 ('TRANSFER_PAYROLL_ACTION_ID'),
175 'TRANSFER_SUI_WAGE_BASE=P',
176 nvl(FFAI.value,' ')
177 from pay_state_rules SR,
178 ff_archive_items ffai,
179 ff_database_items fdi
180 where SR.state_code = pay_magtape_generic.get_parameter_value
181 ('TRANSFER_STATE')
182 and ffai.user_entity_id = fdi.user_entity_id
183 and fdi.user_name = 'A_SUI_TAXABLE_WAGE_BASE'
184 and ffai.context1 = pay_magtape_generic.get_parameter_value
185 ('TRANSFER_PAYROLL_ACTION_ID');
186
187 cursor sqwl_employee is
188 select 'TRANSFER_ASS_ACTION_ID=C', AA.assignment_action_id,
189 'ASSIGNMENT_ACTION_ID=C' , AA.assignment_action_id,
190 'ASSIGNMENT_ID=C' , AA.assignment_id,
191 'ASSIGNMENT_ID=P' , AA.assignment_id, /* Bug 976472 */
192 'DATE_EARNED=C' ,fnd_date.date_to_canonical(pay_magtape_generic.date_earned
193 (PA.effective_date, AA.assignment_id))
194 from per_all_people_f PE,
195 per_all_assignments_f SS,
196 pay_assignment_actions AA,
197 pay_payroll_actions PA
198 where PA.payroll_action_id = pay_magtape_generic.get_parameter_value
199 ('TRANSFER_PAYROLL_ACTION_ID')
200 and AA.payroll_action_id = PA.payroll_action_id
201 and AA.tax_unit_id = pay_magtape_generic.get_parameter_value
202 ('TAX_UNIT_ID')
203 and SS.assignment_id = AA.assignment_id
204 and PE.person_id = SS.person_id
205 /* commented for bug 2464463
206 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
207 SS.effective_start_date and SS.effective_end_date
208 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
209 PE.effective_start_date and PE.effective_end_date
210 */
211
212 /* Added for bug 2464463 */
213 AND SS.effective_start_date =
214 (select max(paf2.effective_start_date)
215 from per_all_assignments_f paf2
216 where paf2.assignment_id = SS.assignment_id
217 and paf2.effective_start_date <= PA.effective_date
218 and paf2.assignment_type = 'E')
219 AND SS.effective_end_date >= PA.start_date
220 AND SS.assignment_type = 'E'
221 AND LEAST(SS.effective_end_date, PA.effective_date)
222 between PE.effective_start_date and PE.effective_end_date
223 /* End of Change for bug 2464463 */
224 order by PE.last_name, PE.first_name, PE.middle_names;
225
226 -- Used in case the report category is RTM
227 -- This cursor expects that every person will have at least a single row
228 -- in ff_archive_items table for wage plan code. Added by ashgupta on
229 -- 10-FEB-2000 for enhancement request req 1063413
230 cursor sqwl_employee_m is
231 select /*+ ORDERED */ '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,
235 'DATE_EARNED=C' , fnd_date.date_to_canonical(pay_magtape_generic.date_earned
236 (PA.effective_date,
237 AA.assignment_id)),
238 'TRANSFER_WAGE_PLAN_CODE=P',pay_magtape_generic.get_parameter_value
239 ('TRANSFER_WAGE_PLAN_CODE'),
240 'TRANSFER_REPORT_FORMAT=P', pay_magtape_generic.get_parameter_value
241 ('TRANSFER_REPORT_CATEGORY')
242 from pay_payroll_actions PA,
243 pay_assignment_actions AA,
244 per_all_assignments_f SS,
245 per_all_people_f PE,
246 ff_archive_items fai,
247 ff_user_entities fue
248 where PA.payroll_action_id = pay_magtape_generic.get_parameter_value
249 ('TRANSFER_PAYROLL_ACTION_ID')
250 and AA.payroll_action_id = PA.payroll_action_id
251 and AA.tax_unit_id = pay_magtape_generic.get_parameter_value
252 ('TAX_UNIT_ID')
253 and SS.assignment_id = AA.assignment_id
254 and PE.person_id = SS.person_id
255 /* commented for bug 2464463
256 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
257 SS.effective_start_date and SS.effective_end_date
258 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
259 PE.effective_start_date and PE.effective_end_date
260 */
261 /* Added for bug 2464463 */
262 AND SS.effective_start_date =
263 (select max(paf2.effective_start_date)
264 from per_all_assignments_f paf2
265 where paf2.assignment_id = SS.assignment_id
266 and paf2.effective_start_date <= PA.effective_date
267 and paf2.assignment_type = 'E')
268 AND SS.effective_end_date >= PA.start_date
269 AND SS.assignment_type = 'E'
270 AND LEAST(SS.effective_end_date, PA.effective_date)
271 between PE.effective_start_date and PE.effective_end_date
272 /* End of Change for bug 2464463 */
273 AND aa.assignment_action_id = fai.context1
274 and fai.value =
275 pay_magtape_generic.get_parameter_value('TRANSFER_WAGE_PLAN_CODE')
276 and fai.user_entity_id = fue.user_entity_id
277 and fue.user_entity_name = 'A_SCL_ASG_US_CA_WAGE_PLAN_CODE'
278 and NOT EXISTS (SELECT value
279 FROM ff_archive_items fai1
280 WHERE fai1.context1 = fai.context1
281 AND fai1.value = fai.value
282 AND fai1.archive_item_id > fai.archive_item_id
283 AND fai1.user_entity_id = fai.user_entity_id)
284 order by PE.last_name, PE.first_name, PE.middle_names;
285
286
287 /**** Bug 976472 *********/
288 --
289 -- Used by NY state for State Quarterly Wage Listing.
290 --
291 -- Sets up the Jurisdiction Code (for NY City and 5 burroughs) contexts
292 -- for an employee. The date_earned context is set to be the least of the
293 -- end of the period being reported and the maximum end date of the
294 -- assignment. This ensures that personal information ie. name etc... is
295 -- current relative to the period being reported on.
296 /******
297 cursor sqwl_employee_jurisdiction is
298 Select distinct
299 'JURISDICTION_CODE=C', pcty.jurisdiction_code
300 from pay_us_emp_city_tax_rules_f pcty,
301 per_assignments_f paf1,
302 per_assignments_f paf
303 where paf.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
304 and paf.effective_end_date >=
305 to_date('01-01-2001','DD-MM-YYYY')
306 and paf.effective_start_date <=
307 to_date('31-03-2001','DD-MM-YYYY')
308 and paf1.person_id = paf.person_id
309 and paf1.effective_end_date >=
310 to_date('01-01-2001','DD-MM-YYYY')
311 and paf1.effective_start_date <=
312 to_date('31-03-2001','DD-MM-YYYY')
313 and pcty.assignment_id = paf1.assignment_id
314 and pcty.effective_start_date <=
315 to_date('31-03-2001','DD-MM-YYYY')
316 and pcty.effective_end_date >=
317 to_date('01-01-2001','DD-MM-YYYY')
318 and pcty.jurisdiction_code in ('33-005-2010',
319 '33-047-2010',
320 '33-061-2010',
321 '33-081-2010',
322 '33-085-2010',
323 '33-119-3230');
324
325 *****/
326 cursor sqwl_employee_jurisdiction is
327 /* commented for bug 2852640
328 Select distinct
329 'JURISDICTION_CODE=C', pcty.jurisdiction_code
330 from pay_us_emp_city_tax_rules_f pcty,
331 per_assignments_f paf1,
332 per_assignments_f paf
333 where paf.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
334 and paf.effective_end_date >= (
335 select
336 decode(
337 to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER') ,'MMYYYY'),'Q'),
338 '4',
339 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Y'),
340 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Q')
341 )
342 from dual
343 )
344 and paf.effective_start_date <=
345 to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE'),'DD-MM-YYYY')
346 and paf1.person_id = paf.person_id
347 and paf1.effective_end_date >=(
348 select
349 decode(
350 to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER') ,'MMYYYY'),'Q'),
351 '4',
352 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Y'),
353 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Q')
354 )
355 from dual
356 )
357 and paf1.effective_start_date <=
358 to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE'),'DD-MM-YYYY')
359 and pcty.assignment_id = paf1.assignment_id
360 and pcty.effective_start_date <=
361 to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE'),'DD-MM-YYYY')
362 and pcty.effective_end_date >=(
363 select
364 decode(
365 to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER') ,'MMYYYY'),'Q'),
366 '4',
367 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Y'),
368 trunc(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY'),'Q')
369 )
370 from dual
371 )
372 and pcty.jurisdiction_code in ('33-005-2010',
373 '33-047-2010',
374 '33-061-2010',
375 '33-081-2010',
376 '33-085-2010',
377 '33-119-3230');
378 */
379 select distinct 'JURISDICTION_CODE=C', context
380 from ff_archive_items fai,
381 ff_archive_item_contexts faic,
382 pay_assignment_actions paa,
383 pay_payroll_actions ppa
384 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
385 and ppa.payroll_action_id = paa.payroll_action_id
386 and paa.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
387 and fai.context1 = paa.assignment_action_id
388 and faic.archive_item_id = fai.archive_item_id
389 and faic.context in ('33-005-2010',
390 '33-047-2010',
391 '33-061-2010',
392 '33-081-2010',
393 '33-085-2010',
394 '33-119-3230');
395
396 /**** End Bug 976472*****/
397 --
398
399 /* added for MMREF SQWLs */
400
401 /* for bug 2752145, commented the join with hr_organization_information,
402 this to remove the dependency on W2 reporting rules for SQWL */
403 cursor mmrf_sqwl_transmitter is
404 select 'TAX_UNIT_ID=C',
405 pay_magtape_generic.get_parameter_value ('TRANSFER_TRANS_LEGAL_CO_ID'),
406 'JURISDICTION_CODE=C', SR.jurisdiction_code,
407 'TRANSFER_JD=P', SR.jurisdiction_code,
408 'ASSIGNMENT_ID=C' , '-1',
409 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
410 'PAYROLL_ACTION_ID=C',
411 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),
412 -- 'TRANSFER_2678_FILER=P', HOI.org_information8,
413 'BUSINESS_GROUP_ID=C' , PPA.business_group_id,
414 'TRANSFER_BUSINESS_GROUP_ID=P',PPA.business_group_id,
415 'TRANSFER_SUI_WAGE_BASE=P', nvl(FFAI.value,' '),
416 'TRANSFER_REPORT_CATEGORY=P', pay_magtape_generic.get_parameter_value
417 ('TRANSFER_REPORT_CATEGORY')
418 from pay_state_rules SR,
419 ff_archive_items ffai,
420 ff_database_items fdi,
421 -- hr_organization_information hoi,
422 pay_payroll_actions ppa
423 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value
424 ('TRANSFER_PAYROLL_ACTION_ID')
425 -- and hoi.organization_id = pay_magtape_generic.get_parameter_value
426 -- ('TRANSFER_TRANS_LEGAL_CO_ID')
427 -- and hoi.org_information_context = 'W2 Reporting Rules'
428 and SR.state_code = pay_magtape_generic.get_parameter_value
429 ('TRANSFER_STATE')
430 and ffai.user_entity_id = fdi.user_entity_id
431 and fdi.user_name = 'A_SUI_TAXABLE_WAGE_BASE'
432 and ffai.context1 = pay_magtape_generic.get_parameter_value
433 ('TRANSFER_PAYROLL_ACTION_ID');
434
435
436
437 cursor mmrf_sqwl_employer is
438 select distinct
439 'PAYROLL_ACTION_ID=C', AA.payroll_action_id,
440 'TAX_UNIT_ID=C' , AA.tax_unit_id,
441 'TAX_UNIT_ID=P' , AA.tax_unit_id,
442 'DATE_EARNED=C' , fnd_date.date_to_canonical(PA.effective_date),
443 'TAX_UNIT_NAME=P' , hou.name
444 from hr_all_organization_units hou,
445 pay_assignment_actions AA,
446 pay_payroll_actions PA
447 where AA.payroll_action_id = pay_magtape_generic.get_parameter_value
448 ('TRANSFER_PAYROLL_ACTION_ID')
449 and PA.payroll_action_id = AA.payroll_action_id
450 and AA.tax_unit_id = hou.organization_id
451 order by hou.name;
452
453 --sackumar
454 cursor mmrf_nysqwl_employer is
455 select /*+ index (hoi hr_organization_informatio_FK1)
456 index(hou hr_organization_units_PK)
457 */ distinct
458 'PAYROLL_ACTION_ID=C', AA.payroll_action_id,
459 'TAX_UNIT_ID=C' , AA.tax_unit_id,
460 'TAX_UNIT_ID=P' , AA.tax_unit_id,
461 'DATE_EARNED=C' , fnd_date.date_to_canonical(PA.effective_date),
462 'TAX_UNIT_NAME=P' , hou.name,
463 'FEIN=P' , hoi.org_information1
464 from hr_all_organization_units hou,
465 hr_organization_information hoi,
466 pay_assignment_actions AA,
467 pay_payroll_actions PA
468 where AA.payroll_action_id = pay_magtape_generic.get_parameter_value
469 ('TRANSFER_PAYROLL_ACTION_ID')
470 and PA.payroll_action_id = AA.payroll_action_id
471 and AA.tax_unit_id = hou.organization_id
472 and hoi.organization_id = hou.organization_id
473 and hoi.org_information_context = 'Employer Identification'
474 order by hoi.org_information1;
475
476 cursor mmrf_sqwl_employee is
477 select 'TRANSFER_ASS_ACTION_ID=C', AA.assignment_action_id,
478 'ASSIGNMENT_ACTION_ID=C' , AA.assignment_action_id,
479 'ASSIGNMENT_ID=C' , AA.assignment_id,
480 'ASSIGNMENT_ID=P' , AA.assignment_id, /* Bug 976472 */
481 'DATE_EARNED=C' ,fnd_date.date_to_canonical(pay_magtape_generic.date_earned(PA.effective_date, AA.assignment_id))
482 from per_all_people_f PE,
483 per_all_assignments_f SS,
484 pay_assignment_actions AA,
485 pay_payroll_actions PA
486 where PA.payroll_action_id = pay_magtape_generic.get_parameter_value
487 ('TRANSFER_PAYROLL_ACTION_ID')
488 and AA.payroll_action_id = PA.payroll_action_id
489 and AA.tax_unit_id = pay_magtape_generic.get_parameter_value
490 ('TAX_UNIT_ID')
491 and SS.assignment_id = AA.assignment_id
492 and PE.person_id = SS.person_id
493 /* commented for bug 2464463
494 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
495 SS.effective_start_date and SS.effective_end_date
496 and pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) between
497 PE.effective_start_date and PE.effective_end_date
498 */
499 /* Added for bug 2464463 */
500 AND SS.effective_start_date =
501 (select max(paf2.effective_start_date)
502 from per_assignments_f paf2
503 where paf2.assignment_id = SS.assignment_id
504 and paf2.effective_start_date <= PA.effective_date
505 and paf2.assignment_type = 'E')
506 AND SS.effective_end_date >= PA.start_date
507 AND SS.assignment_type = 'E'
508 AND LEAST(SS.effective_end_date, PA.effective_date)
509 between PE.effective_start_date and PE.effective_end_date
510 /* End of Change for bug 2464463 */
511 order by PE.last_name, PE.first_name, PE.middle_names;
512
513 /*Bug # 5379670*/
514 cursor sqwl_reconciliation is
515 select 'TRANSFER_DATE_WAGES_PAID_ME=P', hoi.org_information2,
516 'TRANSFER_AMOUNT_WITHHELD_ME=P', hoi.org_information3,
517 'TRANSFER_PAYMENT_DEPOSITED_ME=P', hoi.org_information4
518 from pay_state_rules SR,
519 hr_organization_information hoi
520 where SR.state_code = pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
521 and hoi.org_information_context = 'SQWL Employer Rules 3'
522 and hoi.organization_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
523 and hoi.org_information1 = pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
524 and to_date(hoi.org_information2,'YYYY/MM/DD HH24:MI:SS') between
525 add_months(last_day(to_date(
526 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER')
527 ,'MMYYYY')),-3) + 1
528 and last_day(to_date(pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_QUARTER'),'MMYYYY')) ;
529
530 -- ***********SQWL Cursors Ends *************************
531
532 TYPE char240_data_type_table IS TABLE OF VARCHAR2(240)
533 INDEX BY BINARY_INTEGER;
534 TYPE number_data_type_table IS TABLE OF NUMBER
535 INDEX BY BINARY_INTEGER;
536 g_min_chunk number:= -1;
537 g_archive_flag varchar2(1) := 'N';
538
539 /* Bug 976472 */
540 g_sqwl_state varchar2(2);
541 g_sqwl_jursd varchar2(11);
542 /* End Bug 976472 */
543
544 /* Added by Ashu Gupta on 10-FEB-2000 */
545 g_report_cat pay_report_format_mappings_f.report_category%TYPE;
546
547 procedure range_cursor(pactid in number,
548 sqlstr out nocopy varchar2);
549 procedure action_creation(pactid in number,
550 stperson in number,
551 endperson in number,
552 chunk in number);
553 FUNCTION check_residence_state (
554 p_assignment_id NUMBER,
555 p_period_start DATE,
556 p_period_end DATE,
557 p_state VARCHAR2,
558 p_effective_end_date DATE
559 ) RETURN BOOLEAN;
560
561 procedure archive_data(p_assactid in number, p_effective_date in date);
562 procedure archinit(p_payroll_action_id in number);
563 FUNCTION Update_ff_archive_items (
564 p_payroll_action_id in VARCHAR2
565 )
566 return varchar;
567 /* Bug 773937 */
568 procedure archive_gre_data(p_payroll_action_id in number,
569 p_tax_unit_id in number);
570 /* End of Bug 773937 */
571
572 procedure archive_asg_locs( p_asg_act_id in number
573 ,p_pay_act_id in number
574 ,p_asg_id in number);
575
576 --
577 end pay_us_sqwl_archive;
[Home] [Help]