[Home] [Help]
97: hr_organization_units hou2,
98: hr_tax_units_v htuv
99: WHERE htuv.tax_unit_id(+) = hou1.organization_id
100: AND hou1.organization_id =
101: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_ID')
102: AND est.establishment_id(+) = hou2.organization_id
103: AND hou2.organization_id =
104: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_ID')
105: AND hou2.business_group_id =
100: AND hou1.organization_id =
101: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_ID')
102: AND est.establishment_id(+) = hou2.organization_id
103: AND hou2.organization_id =
104: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_ID')
105: AND hou2.business_group_id =
106: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID');
107:
108:
102: AND est.establishment_id(+) = hou2.organization_id
103: AND hou2.organization_id =
104: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_ID')
105: AND hou2.business_group_id =
106: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID');
107:
108:
109:
110: /* Select company and establishment details. Will loop through hierarchy if we have GRE parent and EST siblings - producing one page per GRE/EST pairing. */
164: pay_state_rules psr1 ,
165: pay_state_rules psr2
166: WHERE htuv.tax_unit_id (+) = hou1.organization_id
167: AND hou1.organization_id =
168: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
169: AND cloc.location_id(+) = htuv.location_id
170: AND cloc.region_2 = psr1.state_code
171: AND eloc.location_id(+) = est.location_id
172: AND est.establishment_id = hou2.organization_id
171: AND eloc.location_id(+) = est.location_id
172: AND est.establishment_id = hou2.organization_id
173: AND eloc.region_2 = psr2.state_code
174: AND eloc.region_2 =
175: nvl(pay_magtape_generic.get_parameter_value('TRANSFER_STATE'), eloc.region_2)
176: AND ((hou2.organization_id =
177: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID1')
178: AND
179: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID1')
173: AND eloc.region_2 = psr2.state_code
174: AND eloc.region_2 =
175: nvl(pay_magtape_generic.get_parameter_value('TRANSFER_STATE'), eloc.region_2)
176: AND ((hou2.organization_id =
177: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID1')
178: AND
179: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID1')
180: is not null)
181: OR
175: nvl(pay_magtape_generic.get_parameter_value('TRANSFER_STATE'), eloc.region_2)
176: AND ((hou2.organization_id =
177: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID1')
178: AND
179: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID1')
180: is not null)
181: OR
182: ( pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
183: is not null
178: AND
179: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID1')
180: is not null)
181: OR
182: ( pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
183: is not null
184: AND est.establishment_id in
185: --
186: -- Start with the GRE, head down to find the establishments,
187: (
188: select ose.organization_id_child
189: from per_org_structure_elements ose
190: where ose.org_structure_version_id +0 =
191: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
192: connect by prior ose.organization_id_child = ose.organization_id_parent
193: and ose.org_structure_version_id +0 =
194: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
195: start with ose.organization_id_parent =
190: where ose.org_structure_version_id +0 =
191: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
192: connect by prior ose.organization_id_child = ose.organization_id_parent
193: and ose.org_structure_version_id +0 =
194: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
195: start with ose.organization_id_parent =
196: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
197: and ose.org_structure_version_id + 0 =
198: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
192: connect by prior ose.organization_id_child = ose.organization_id_parent
193: and ose.org_structure_version_id +0 =
194: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
195: start with ose.organization_id_parent =
196: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
197: and ose.org_structure_version_id + 0 =
198: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
199: )
200: ) )
194: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
195: start with ose.organization_id_parent =
196: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
197: and ose.org_structure_version_id + 0 =
198: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
199: )
200: ) )
201: group by
202: psr1.state_code ,
261: per_jobs job
262: WHERE peo.person_id = ass.person_id
263: AND job.job_information_category = 'US'
264: AND job.date_from <=
265: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
266: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
267: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
268: AND job.job_information1 =
269: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
263: AND job.job_information_category = 'US'
264: AND job.date_from <=
265: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
266: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
267: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
268: AND job.job_information1 =
269: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
270: AND ass.job_id = job.job_id
271: AND peo.effective_start_date <=
265: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
266: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
267: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
268: AND job.job_information1 =
269: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
270: AND ass.job_id = job.job_id
271: AND peo.effective_start_date <=
272: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
273: AND peo.effective_end_date >=
268: AND job.job_information1 =
269: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
270: AND ass.job_id = job.job_id
271: AND peo.effective_start_date <=
272: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
273: AND peo.effective_end_date >=
274: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
275: AND peo.current_employee_flag = 'Y'
276: AND ass.assignment_type = 'E'
270: AND ass.job_id = job.job_id
271: AND peo.effective_start_date <=
272: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
273: AND peo.effective_end_date >=
274: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
275: AND peo.current_employee_flag = 'Y'
276: AND ass.assignment_type = 'E'
277: AND ass.primary_flag = 'Y'
278: AND ass.assignment_status_type_id+0 = hoi1.org_information1
277: AND ass.primary_flag = 'Y'
278: AND ass.assignment_status_type_id+0 = hoi1.org_information1
279: AND hoi1.org_information_context = 'Reporting Statuses'
280: AND hoi1.organization_id =
281: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
282: AND ass.employment_category = hoi2.org_information1
283: AND hoi2.organization_id =
284: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
285: AND hoi2.org_information_context = 'Reporting Categories'
280: AND hoi1.organization_id =
281: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
282: AND ass.employment_category = hoi2.org_information1
283: AND hoi2.organization_id =
284: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
285: AND hoi2.org_information_context = 'Reporting Categories'
286: AND ass.effective_start_date <=
287: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
288: AND ass.effective_end_date >=
283: AND hoi2.organization_id =
284: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
285: AND hoi2.org_information_context = 'Reporting Categories'
286: AND ass.effective_start_date <=
287: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
288: AND ass.effective_end_date >=
289: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
290: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
291: -- AND scf.segment1 =
285: AND hoi2.org_information_context = 'Reporting Categories'
286: AND ass.effective_start_date <=
287: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
288: AND ass.effective_end_date >=
289: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
290: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
291: -- AND scf.segment1 =
292: -- pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
293: /* AND EXISTS
288: AND ass.effective_end_date >=
289: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
290: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
291: -- AND scf.segment1 =
292: -- pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
293: /* AND EXISTS
294: (
295: SELECT htuv.tax_unit_id
296: FROM hr_tax_units_v htuv,
297: hr_locations gloc
298: WHERE htuv.tax_unit_id = scf.segment1
299: AND htuv.location_id = gloc.location_id
300: AND gloc.region_2 = -- :gre_state
301: pay_magtape_generic.get_parameter_value('TRANSFER_GRE_STATE')
302: ) */
303: AND (scf.segment9 = -- :establishment_id
304: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
305: OR scf.segment9 is null
300: AND gloc.region_2 = -- :gre_state
301: pay_magtape_generic.get_parameter_value('TRANSFER_GRE_STATE')
302: ) */
303: AND (scf.segment9 = -- :establishment_id
304: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
305: OR scf.segment9 is null
306: AND
307: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
308: AND ass.organization_id IN
303: AND (scf.segment9 = -- :establishment_id
304: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
305: OR scf.segment9 is null
306: AND
307: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
308: AND ass.organization_id IN
309: (
310: SELECT -- :establishment_id
311: to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID'))
307: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
308: AND ass.organization_id IN
309: (
310: SELECT -- :establishment_id
311: to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID'))
312: FROM dual
313: UNION
314: SELECT ose.organization_id_child
315: FROM per_org_structure_elements ose
313: UNION
314: SELECT ose.organization_id_child
315: FROM per_org_structure_elements ose
316: WHERE ose.org_structure_version_id + 0 =
317: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
318: -- AND ose.organization_id_child = ass.organization_id
319: CONNECT BY PRIOR ose.organization_id_child =
320: ose.organization_id_parent
321: AND ose.org_structure_version_id + 0 =
318: -- AND ose.organization_id_child = ass.organization_id
319: CONNECT BY PRIOR ose.organization_id_child =
320: ose.organization_id_parent
321: AND ose.org_structure_version_id + 0 =
322: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
323: -- AND ose.organization_id_child = ass.organization_id
324: START WITH ose.organization_id_parent =
325: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
326: )
321: AND ose.org_structure_version_id + 0 =
322: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
323: -- AND ose.organization_id_child = ass.organization_id
324: START WITH ose.organization_id_parent =
325: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
326: )
327: );
328:
329:
352: per_jobs job
353: WHERE peo.person_id = ass.person_id
354: AND job.job_information_category = 'US'
355: AND job.date_from <=
356: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
357: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
358: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
359: AND job.job_information1 =
360: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
354: AND job.job_information_category = 'US'
355: AND job.date_from <=
356: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
357: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
358: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
359: AND job.job_information1 =
360: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
361: AND ass.job_id = job.job_id
362: AND peo.effective_start_date <=
356: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
357: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
358: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
359: AND job.job_information1 =
360: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
361: AND ass.job_id = job.job_id
362: AND peo.effective_start_date <=
363: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
364: AND peo.effective_end_date >=
359: AND job.job_information1 =
360: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
361: AND ass.job_id = job.job_id
362: AND peo.effective_start_date <=
363: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
364: AND peo.effective_end_date >=
365: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
366: AND peo.current_employee_flag = 'Y'
367: AND peo.start_date between
361: AND ass.job_id = job.job_id
362: AND peo.effective_start_date <=
363: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
364: AND peo.effective_end_date >=
365: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
366: AND peo.current_employee_flag = 'Y'
367: AND peo.start_date between
368: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
369: and
364: AND peo.effective_end_date >=
365: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
366: AND peo.current_employee_flag = 'Y'
367: AND peo.start_date between
368: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
369: and
370: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
371: AND ass.assignment_type = 'E'
372: AND ass.primary_flag = 'Y'
366: AND peo.current_employee_flag = 'Y'
367: AND peo.start_date between
368: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
369: and
370: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
371: AND ass.assignment_type = 'E'
372: AND ass.primary_flag = 'Y'
373: AND ass.assignment_status_type_id+0 = hoi1.org_information1
374: AND hoi1.org_information_context = 'Reporting Statuses'
372: AND ass.primary_flag = 'Y'
373: AND ass.assignment_status_type_id+0 = hoi1.org_information1
374: AND hoi1.org_information_context = 'Reporting Statuses'
375: AND hoi1.organization_id =
376: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
377: AND ass.employment_category = hoi2.org_information1
378: AND hoi2.org_information_context = 'Reporting Categories'
379: AND hoi2.organization_id =
380: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
376: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
377: AND ass.employment_category = hoi2.org_information1
378: AND hoi2.org_information_context = 'Reporting Categories'
379: AND hoi2.organization_id =
380: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
381: AND ass.effective_start_date <=
382: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
383: AND ass.effective_end_date >=
384: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
378: AND hoi2.org_information_context = 'Reporting Categories'
379: AND hoi2.organization_id =
380: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
381: AND ass.effective_start_date <=
382: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
383: AND ass.effective_end_date >=
384: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
385: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
386: -- AND scf.segment1 =
380: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
381: AND ass.effective_start_date <=
382: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
383: AND ass.effective_end_date >=
384: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
385: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
386: -- AND scf.segment1 =
387: -- pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
388: /* AND EXISTS
383: AND ass.effective_end_date >=
384: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
385: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
386: -- AND scf.segment1 =
387: -- pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
388: /* AND EXISTS
389: (
390: SELECT htuv.tax_unit_id
391: FROM hr_tax_units_v htuv,
392: hr_locations gloc
393: WHERE htuv.tax_unit_id = scf.segment1
394: AND htuv.location_id = gloc.location_id
395: AND gloc.region_2 = -- :gre_state
396: pay_magtape_generic.get_parameter_value('TRANSFER_GRE_STATE')
397: ) */
398: AND (scf.segment9 = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
399: OR scf.segment9 is null
400: AND -- :P_ORG_STRUCTURE_VERSION_ID
394: AND htuv.location_id = gloc.location_id
395: AND gloc.region_2 = -- :gre_state
396: pay_magtape_generic.get_parameter_value('TRANSFER_GRE_STATE')
397: ) */
398: AND (scf.segment9 = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
399: OR scf.segment9 is null
400: AND -- :P_ORG_STRUCTURE_VERSION_ID
401: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
402: AND ass.organization_id IN
397: ) */
398: AND (scf.segment9 = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
399: OR scf.segment9 is null
400: AND -- :P_ORG_STRUCTURE_VERSION_ID
401: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
402: AND ass.organization_id IN
403: (
404: SELECT
405: to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID'))
401: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
402: AND ass.organization_id IN
403: (
404: SELECT
405: to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID'))
406: FROM dual
407: UNION
408: SELECT ose.organization_id_child
409: FROM per_org_structure_elements ose
407: UNION
408: SELECT ose.organization_id_child
409: FROM per_org_structure_elements ose
410: WHERE ose.org_structure_version_id + 0 =
411: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
412: -- AND ose.organization_id_child = ass.organization_id
413: CONNECT BY PRIOR ose.organization_id_child =
414: ose.organization_id_parent
415: AND ose.org_structure_version_id + 0 =
412: -- AND ose.organization_id_child = ass.organization_id
413: CONNECT BY PRIOR ose.organization_id_child =
414: ose.organization_id_parent
415: AND ose.org_structure_version_id + 0 =
416: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
417: -- AND ose.organization_id_child = ass.organization_id
418: START WITH ose.organization_id_parent =
419: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
420: )
415: AND ose.org_structure_version_id + 0 =
416: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
417: -- AND ose.organization_id_child = ass.organization_id
418: START WITH ose.organization_id_parent =
419: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
420: )
421: );
422:
423:
436: hr_soft_coding_keyflex scf,
437: per_jobs job
438: WHERE job.job_information_category = 'US'
439: AND job.date_from <=
440: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
441: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
442: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
443: AND job.job_information1 is not null
444: AND ass.job_id = job.job_id
438: WHERE job.job_information_category = 'US'
439: AND job.date_from <=
440: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
441: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
442: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
443: AND job.job_information1 is not null
444: AND ass.job_id = job.job_id
445: AND ass.business_group_id+0 =
446: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
442: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
443: AND job.job_information1 is not null
444: AND ass.job_id = job.job_id
445: AND ass.business_group_id+0 =
446: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
447: AND ass.assignment_type = 'E'
448: AND ass.primary_flag = 'Y'
449: AND ass.assignment_status_type_id +0 = hoi1.org_information1
450: AND hoi1.org_information_context = 'Reporting Statuses'
448: AND ass.primary_flag = 'Y'
449: AND ass.assignment_status_type_id +0 = hoi1.org_information1
450: AND hoi1.org_information_context = 'Reporting Statuses'
451: AND hoi1.organization_id =
452: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
453: AND ass.effective_start_date <=
454: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
455: AND ass.effective_end_date >=
456: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
450: AND hoi1.org_information_context = 'Reporting Statuses'
451: AND hoi1.organization_id =
452: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
453: AND ass.effective_start_date <=
454: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
455: AND ass.effective_end_date >=
456: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
457: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
458: AND hev.establishment_id =
452: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
453: AND ass.effective_start_date <=
454: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
455: AND ass.effective_end_date >=
456: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
457: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
458: AND hev.establishment_id =
459: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
460: AND hev.location_id = hl.location_id
455: AND ass.effective_end_date >=
456: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
457: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
458: AND hev.establishment_id =
459: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
460: AND hev.location_id = hl.location_id
461: AND hl.region_2 = psr.state_code
462: AND hl.region_2 =
463: nvl(pay_magtape_generic.get_parameter_value('TRANSFER_STATE'), hl.region_2)
459: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
460: AND hev.location_id = hl.location_id
461: AND hl.region_2 = psr.state_code
462: AND hl.region_2 =
463: nvl(pay_magtape_generic.get_parameter_value('TRANSFER_STATE'), hl.region_2)
464: AND scf.segment1 =
465: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
466: AND (scf.segment9 =
467: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
461: AND hl.region_2 = psr.state_code
462: AND hl.region_2 =
463: nvl(pay_magtape_generic.get_parameter_value('TRANSFER_STATE'), hl.region_2)
464: AND scf.segment1 =
465: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
466: AND (scf.segment9 =
467: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
468: OR scf.segment9 is null
469: AND -- :P_ORG_STRUCTURE_VERSION_ID
463: nvl(pay_magtape_generic.get_parameter_value('TRANSFER_STATE'), hl.region_2)
464: AND scf.segment1 =
465: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
466: AND (scf.segment9 =
467: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
468: OR scf.segment9 is null
469: AND -- :P_ORG_STRUCTURE_VERSION_ID
470: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
471: AND ass.organization_id IN
466: AND (scf.segment9 =
467: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
468: OR scf.segment9 is null
469: AND -- :P_ORG_STRUCTURE_VERSION_ID
470: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
471: AND ass.organization_id IN
472: (
473: SELECT
474: to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID'))
470: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
471: AND ass.organization_id IN
472: (
473: SELECT
474: to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID'))
475: FROM dual
476: UNION
477: SELECT ose.organization_id_child
478: FROM per_org_structure_elements ose
476: UNION
477: SELECT ose.organization_id_child
478: FROM per_org_structure_elements ose
479: WHERE ose.org_structure_version_id + 0 =
480: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
481: -- AND ose.organization_id_child = ass.organization_id
482: CONNECT BY PRIOR ose.organization_id_child =
483: ose.organization_id_parent
484: AND ose.org_structure_version_id + 0 =
481: -- AND ose.organization_id_child = ass.organization_id
482: CONNECT BY PRIOR ose.organization_id_child =
483: ose.organization_id_parent
484: AND ose.org_structure_version_id + 0 =
485: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
486: -- AND ose.organization_id_child = ass.organization_id
487: START WITH ose.organization_id_parent =
488: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
489: ));
484: AND ose.org_structure_version_id + 0 =
485: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
486: -- AND ose.organization_id_child = ass.organization_id
487: START WITH ose.organization_id_parent =
488: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
489: ));
490:
491: cursor S_VETC_MIN_COUNT is
492: SELECT 'TRANSFER_MIN_COUNT_ASSIGNMENTS=P',
495: per_assignments_f ass,
496: hr_soft_coding_keyflex scf,
497: per_jobs job
498: WHERE pds.date_start <
499: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
500: AND (nvl(pds.actual_termination_date,
501: to_date('12/31/4712','MM/DD/YYYY')) >
502: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))
503: AND pds.person_id = ass.person_id
498: WHERE pds.date_start <
499: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
500: AND (nvl(pds.actual_termination_date,
501: to_date('12/31/4712','MM/DD/YYYY')) >
502: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))
503: AND pds.person_id = ass.person_id
504: AND ass.assignment_type = 'E'
505: AND ass.primary_flag = 'Y'
506: AND ass.business_group_id =
503: AND pds.person_id = ass.person_id
504: AND ass.assignment_type = 'E'
505: AND ass.primary_flag = 'Y'
506: AND ass.business_group_id =
507: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
508: AND ass.effective_start_date <
509: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
510: and ass.effective_end_date >
511: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
505: AND ass.primary_flag = 'Y'
506: AND ass.business_group_id =
507: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
508: AND ass.effective_start_date <
509: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
510: and ass.effective_end_date >
511: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
512: AND ass.job_id = job.job_id
513: AND job.job_information_category = 'US'
507: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
508: AND ass.effective_start_date <
509: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
510: and ass.effective_end_date >
511: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
512: AND ass.job_id = job.job_id
513: AND job.job_information_category = 'US'
514: AND job.date_from <=
515: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
511: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
512: AND ass.job_id = job.job_id
513: AND job.job_information_category = 'US'
514: AND job.date_from <=
515: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
516: AND nvl(job.date_to,to_date('31/12/4712','DD/MM/YYYY')) >=
517: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
518: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
519: AND scf.segment1 =
513: AND job.job_information_category = 'US'
514: AND job.date_from <=
515: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
516: AND nvl(job.date_to,to_date('31/12/4712','DD/MM/YYYY')) >=
517: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
518: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
519: AND scf.segment1 =
520: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
521: AND (scf.segment9 =
516: AND nvl(job.date_to,to_date('31/12/4712','DD/MM/YYYY')) >=
517: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
518: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
519: AND scf.segment1 =
520: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
521: AND (scf.segment9 =
522: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
523: OR scf.segment9 is null
524: AND pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
518: AND ass.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
519: AND scf.segment1 =
520: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
521: AND (scf.segment9 =
522: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
523: OR scf.segment9 is null
524: AND pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
525: AND ass.organization_id IN
526: (
520: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID')
521: AND (scf.segment9 =
522: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
523: OR scf.segment9 is null
524: AND pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
525: AND ass.organization_id IN
526: (
527: SELECT to_number(pay_magtape_generic.get_parameter_value(
528: 'TRANSFER_ESTABLISHMENT_ID'))
523: OR scf.segment9 is null
524: AND pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID') <> -1
525: AND ass.organization_id IN
526: (
527: SELECT to_number(pay_magtape_generic.get_parameter_value(
528: 'TRANSFER_ESTABLISHMENT_ID'))
529: FROM dual
530: UNION
531: SELECT ose.organization_id_child
530: UNION
531: SELECT ose.organization_id_child
532: FROM per_org_structure_elements ose
533: WHERE ose.org_structure_version_id + 0 =
534: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
535: CONNECT BY PRIOR ose.organization_id_child =
536: ose.organization_id_parent
537: AND ose.org_structure_version_id + 0 =
538: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
534: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
535: CONNECT BY PRIOR ose.organization_id_child =
536: ose.organization_id_parent
537: AND ose.org_structure_version_id + 0 =
538: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
539: START WITH ose.organization_id_parent =
540: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
541: ));
542:
536: ose.organization_id_parent
537: AND ose.org_structure_version_id + 0 =
538: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
539: START WITH ose.organization_id_parent =
540: pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
541: ));
542:
543: end;