eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PER_US_VETS_TAP dependencies on PAY_MAGTAPE_GENERIC

Line 93: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')

89: pgn.entity_id
90: FROM per_gen_hierarchy_versions pgv,
91: per_gen_hierarchy_nodes pgn
92: where pgv.hierarchy_version_id =
93: pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
94: AND pgv.hierarchy_version_id = pgn.hierarchy_version_id
95: and pgn.node_type = 'PAR';
96: --
97: CURSOR S_VETS_RE IS

Line 159: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')

155: and hoi2.organization_id = hou.organization_id
156: and hoi3.org_information_context = 'Employer Identification'
157: and hoi3.organization_id = hou.organization_id
158: and hou.location_id(+)= cloc.location_id
159: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
160: and pghn.node_type = 'EST'
161: and eloc.location_id = pghn.entity_id
162: and hlei1.location_id = pghn.entity_id
163: and hlei1.location_id = hlei2.location_id

Line 195: WHERE (pds.date_start <= to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

191: per_assignments_f ass,
192: hr_organization_information hoi1,
193: hr_organization_information hoi2,
194: per_jobs job
195: WHERE (pds.date_start <= to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
196: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) >=
197: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
198: or pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
199: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

Line 197: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

193: hr_organization_information hoi2,
194: per_jobs job
195: WHERE (pds.date_start <= to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
196: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) >=
197: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
198: or pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
199: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
200: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
201: between pds.date_start

Line 198: or pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

194: per_jobs job
195: WHERE (pds.date_start <= to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
196: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) >=
197: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
198: or pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
199: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
200: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
201: between pds.date_start
202: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))

Line 199: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

195: WHERE (pds.date_start <= to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
196: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) >=
197: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
198: or pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
199: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
200: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
201: between pds.date_start
202: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
203: and pds.person_id = ass.person_id

Line 200: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

196: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) >=
197: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
198: or pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
199: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
200: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
201: between pds.date_start
202: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
203: and pds.person_id = ass.person_id
204: and peo.person_id = ass.person_id

Line 207: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

203: and pds.person_id = ass.person_id
204: and peo.person_id = ass.person_id
205: AND job.job_information_category = 'US'
206: AND job.date_from <=
207: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
208: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
209: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
210: AND job.job_information1 =
211: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')

Line 209: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

205: AND job.job_information_category = 'US'
206: AND job.date_from <=
207: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
208: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
209: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
210: AND job.job_information1 =
211: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
212: AND ass.job_id = job.job_id
213: AND peo.effective_start_date <=

Line 211: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')

207: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
208: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
209: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
210: AND job.job_information1 =
211: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
212: AND ass.job_id = job.job_id
213: AND peo.effective_start_date <=
214: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
215: AND peo.effective_end_date >=

Line 214: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

210: AND job.job_information1 =
211: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
212: AND ass.job_id = job.job_id
213: AND peo.effective_start_date <=
214: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
215: AND peo.effective_end_date >=
216: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
217: AND peo.current_employee_flag = 'Y'
218: AND ass.assignment_type = 'E'

Line 216: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

212: AND ass.job_id = job.job_id
213: AND peo.effective_start_date <=
214: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
215: AND peo.effective_end_date >=
216: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
217: AND peo.current_employee_flag = 'Y'
218: AND ass.assignment_type = 'E'
219: AND ass.primary_flag = 'Y'
220: AND ass.effective_start_date = (select max(paf2.effective_start_date)

Line 226: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))

222: where paf2.person_id = ass.person_id
223: and paf2.primary_flag = 'Y'
224: and paf2.assignment_type = 'E'
225: and paf2.effective_start_date <=
226: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))
227: AND to_char(ass.assignment_status_type_id) = hoi1.org_information1
228: AND hoi1.org_information_context = 'Reporting Statuses'
229: AND hoi1.organization_id =
230: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

Line 230: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

226: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))
227: AND to_char(ass.assignment_status_type_id) = hoi1.org_information1
228: AND hoi1.org_information_context = 'Reporting Statuses'
229: AND hoi1.organization_id =
230: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
231: AND ass.employment_category = hoi2.org_information1
232: AND hoi2.organization_id =
233: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
234: AND hoi2.org_information_context = 'Reporting Categories'

Line 233: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

229: AND hoi1.organization_id =
230: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
231: AND ass.employment_category = hoi2.org_information1
232: AND hoi2.organization_id =
233: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
234: AND hoi2.org_information_context = 'Reporting Categories'
235: AND ass.effective_start_date <=
236: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
237: AND ass.effective_end_date >=

Line 236: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

232: AND hoi2.organization_id =
233: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
234: AND hoi2.org_information_context = 'Reporting Categories'
235: AND ass.effective_start_date <=
236: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
237: AND ass.effective_end_date >=
238: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
239: AND ass.location_id in
240: (select entity_id

Line 238: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

234: AND hoi2.org_information_context = 'Reporting Categories'
235: AND ass.effective_start_date <=
236: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
237: AND ass.effective_end_date >=
238: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
239: AND ass.location_id in
240: (select entity_id
241: from per_gen_hierarchy_nodes
242: where node_type = 'EST'

Line 243: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')

239: AND ass.location_id in
240: (select entity_id
241: from per_gen_hierarchy_nodes
242: where node_type = 'EST'
243: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
244: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
245: union
246: select pghn2.entity_id
247: from per_gen_hierarchy_nodes pghn,

Line 244: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')

240: (select entity_id
241: from per_gen_hierarchy_nodes
242: where node_type = 'EST'
243: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
244: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
245: union
246: select pghn2.entity_id
247: from per_gen_hierarchy_nodes pghn,
248: per_gen_hierarchy_nodes pghn2

Line 249: where pghn.entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')

245: union
246: select pghn2.entity_id
247: from per_gen_hierarchy_nodes pghn,
248: per_gen_hierarchy_nodes pghn2
249: where pghn.entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
250: and pghn.hierarchy_version_id = pghn2.hierarchy_version_id
251: and pghn2.parent_hierarchy_node_id = pghn.hierarchy_node_id
252: and pghn2.node_type = 'LOC'
253: and pghn.node_type = 'EST'

Line 254: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID'))

250: and pghn.hierarchy_version_id = pghn2.hierarchy_version_id
251: and pghn2.parent_hierarchy_node_id = pghn.hierarchy_node_id
252: and pghn2.node_type = 'LOC'
253: and pghn.node_type = 'EST'
254: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID'))
255: ;
256:
257:
258: CURSOR S_VETS_VETS_NH IS

Line 276: and pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

272: hr_organization_information hoi2,
273: per_periods_of_service pds,
274: per_jobs job
275: WHERE peo.person_id = ass.person_id
276: and pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
277: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
278: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
279: between pds.date_start
280: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'))

Line 277: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

273: per_periods_of_service pds,
274: per_jobs job
275: WHERE peo.person_id = ass.person_id
276: and pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
277: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
278: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
279: between pds.date_start
280: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'))
281: and pds.person_id = peo.person_id

Line 278: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

274: per_jobs job
275: WHERE peo.person_id = ass.person_id
276: and pds.date_start between to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
277: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
278: and to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
279: between pds.date_start
280: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'))
281: and pds.person_id = peo.person_id
282: AND job.job_information_category = 'US'

Line 284: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

280: and nvl(pds.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'))
281: and pds.person_id = peo.person_id
282: AND job.job_information_category = 'US'
283: AND job.date_from <=
284: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
285: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
286: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
287: AND job.job_information1 =
288: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')

Line 286: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

282: AND job.job_information_category = 'US'
283: AND job.date_from <=
284: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
285: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
286: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
287: AND job.job_information1 =
288: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
289: AND ass.job_id = job.job_id
290: AND peo.effective_start_date <=

Line 288: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')

284: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
285: AND nvl(job.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) >=
286: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
287: AND job.job_information1 =
288: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
289: AND ass.job_id = job.job_id
290: AND peo.effective_start_date <=
291: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
292: AND peo.effective_end_date >=

Line 291: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

287: AND job.job_information1 =
288: pay_magtape_generic.get_parameter_value('TRANSFER_JOB_CATEGORY_CODE')
289: AND ass.job_id = job.job_id
290: AND peo.effective_start_date <=
291: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
292: AND peo.effective_end_date >=
293: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
294: AND peo.current_employee_flag = 'Y'
295: AND ass.assignment_type = 'E'

Line 293: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

289: AND ass.job_id = job.job_id
290: AND peo.effective_start_date <=
291: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
292: AND peo.effective_end_date >=
293: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
294: AND peo.current_employee_flag = 'Y'
295: AND ass.assignment_type = 'E'
296: AND ass.primary_flag = 'Y'
297: AND ass.effective_start_date = (select max(paf2.effective_start_date)

Line 303: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))

299: where paf2.person_id = ass.person_id
300: and paf2.primary_flag = 'Y'
301: and paf2.assignment_type = 'E'
302: and paf2.effective_start_date <=
303: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))
304: AND to_char(ass.assignment_status_type_id) = hoi1.org_information1
305: AND hoi1.org_information_context = 'Reporting Statuses'
306: AND hoi1.organization_id =
307: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

Line 307: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

303: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))
304: AND to_char(ass.assignment_status_type_id) = hoi1.org_information1
305: AND hoi1.org_information_context = 'Reporting Statuses'
306: AND hoi1.organization_id =
307: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
308: AND ass.employment_category = hoi2.org_information1
309: AND hoi2.org_information_context = 'Reporting Categories'
310: AND hoi2.organization_id =
311: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

Line 311: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

307: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
308: AND ass.employment_category = hoi2.org_information1
309: AND hoi2.org_information_context = 'Reporting Categories'
310: AND hoi2.organization_id =
311: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
312: AND ass.effective_start_date <=
313: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
314: AND ass.effective_end_date >=
315: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

Line 313: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

309: AND hoi2.org_information_context = 'Reporting Categories'
310: AND hoi2.organization_id =
311: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
312: AND ass.effective_start_date <=
313: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
314: AND ass.effective_end_date >=
315: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
316: AND ass.location_id in
317: (select entity_id

Line 315: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

311: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
312: AND ass.effective_start_date <=
313: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
314: AND ass.effective_end_date >=
315: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
316: AND ass.location_id in
317: (select entity_id
318: from per_gen_hierarchy_nodes
319: where node_type = 'EST'

Line 320: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')

316: AND ass.location_id in
317: (select entity_id
318: from per_gen_hierarchy_nodes
319: where node_type = 'EST'
320: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
321: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
322: union
323: select pghn2.entity_id
324: from per_gen_hierarchy_nodes pghn,

Line 321: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')

317: (select entity_id
318: from per_gen_hierarchy_nodes
319: where node_type = 'EST'
320: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
321: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
322: union
323: select pghn2.entity_id
324: from per_gen_hierarchy_nodes pghn,
325: per_gen_hierarchy_nodes pghn2

Line 326: where pghn.entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')

322: union
323: select pghn2.entity_id
324: from per_gen_hierarchy_nodes pghn,
325: per_gen_hierarchy_nodes pghn2
326: where pghn.entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
327: and pghn.hierarchy_version_id = pghn2.hierarchy_version_id
328: and pghn2.parent_hierarchy_node_id = pghn.hierarchy_node_id
329: and pghn2.node_type = 'LOC'
330: and pghn.node_type = 'EST'

Line 331: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID'))

327: and pghn.hierarchy_version_id = pghn2.hierarchy_version_id
328: and pghn2.parent_hierarchy_node_id = pghn.hierarchy_node_id
329: and pghn2.node_type = 'LOC'
330: and pghn.node_type = 'EST'
331: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID'))
332: ;
333:
334:
335: /* Count employee assignments. We use this to determine company size and

Line 347: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

343: -- hr_organization_information hoi2,
344: per_jobs job
345: WHERE job.job_information_category = 'US'
346: AND job.date_from <=
347: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
348: AND nvl(job.date_to, to_date('4712/12/31', 'YYYY/MM/DD')) >=
349: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
350: AND job.job_information1 is not null
351: AND ass.job_id = job.job_id

Line 349: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

345: WHERE job.job_information_category = 'US'
346: AND job.date_from <=
347: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
348: AND nvl(job.date_to, to_date('4712/12/31', 'YYYY/MM/DD')) >=
349: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
350: AND job.job_information1 is not null
351: AND ass.job_id = job.job_id
352: AND ass.business_group_id +0 =
353: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

Line 353: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

349: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
350: AND job.job_information1 is not null
351: AND ass.job_id = job.job_id
352: AND ass.business_group_id +0 =
353: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
354: AND ass.assignment_type = 'E'
355: AND ass.primary_flag = 'Y'
356: AND to_char(ass.assignment_status_type_id) = hoi1.org_information1
357: AND hoi1.org_information_context = 'Reporting Statuses'

Line 359: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

355: AND ass.primary_flag = 'Y'
356: AND to_char(ass.assignment_status_type_id) = hoi1.org_information1
357: AND hoi1.org_information_context = 'Reporting Statuses'
358: AND hoi1.organization_id =
359: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
360: AND ass.effective_start_date <=
361: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
362: AND ass.effective_end_date >=
363: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

Line 361: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

357: AND hoi1.org_information_context = 'Reporting Statuses'
358: AND hoi1.organization_id =
359: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
360: AND ass.effective_start_date <=
361: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
362: AND ass.effective_end_date >=
363: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
364: AND ass.location_id IN
365: (select entity_id

Line 363: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

359: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
360: AND ass.effective_start_date <=
361: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
362: AND ass.effective_end_date >=
363: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
364: AND ass.location_id IN
365: (select entity_id
366: from per_gen_hierarchy_nodes
367: where node_type = 'EST'

Line 368: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')

364: AND ass.location_id IN
365: (select entity_id
366: from per_gen_hierarchy_nodes
367: where node_type = 'EST'
368: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
369: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
370: union
371: select pghn2.entity_id
372: from per_gen_hierarchy_nodes pghn,

Line 369: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')

365: (select entity_id
366: from per_gen_hierarchy_nodes
367: where node_type = 'EST'
368: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
369: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
370: union
371: select pghn2.entity_id
372: from per_gen_hierarchy_nodes pghn,
373: per_gen_hierarchy_nodes pghn2

Line 374: where pghn.entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')

370: union
371: select pghn2.entity_id
372: from per_gen_hierarchy_nodes pghn,
373: per_gen_hierarchy_nodes pghn2
374: where pghn.entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
375: and pghn.hierarchy_version_id = pghn2.hierarchy_version_id
376: and pghn2.parent_hierarchy_node_id = pghn.hierarchy_node_id
377: and pghn2.node_type = 'LOC'
378: and pghn.node_type = 'EST'

Line 379: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID'))

375: and pghn.hierarchy_version_id = pghn2.hierarchy_version_id
376: and pghn2.parent_hierarchy_node_id = pghn.hierarchy_node_id
377: and pghn2.node_type = 'LOC'
378: and pghn.node_type = 'EST'
379: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID'))
380: ;
381:
382: cursor S_VETS_MIN_COUNT is
383: SELECT 'TRANSFER_MIN_COUNT_ASSIGNMENTS=P',

Line 391: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

387: hr_organization_information hoi1,
388: hr_organization_information hoi2,
389: per_jobs job
390: WHERE pds.date_start <=
391: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
392: AND (nvl(pds.actual_termination_date,
393: to_date('12/31/4712','MM/DD/YYYY')) >=
394: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))
395: AND pds.person_id = ass.person_id

Line 394: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))

390: WHERE pds.date_start <=
391: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
392: AND (nvl(pds.actual_termination_date,
393: to_date('12/31/4712','MM/DD/YYYY')) >=
394: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS'))
395: AND pds.person_id = ass.person_id
396: AND ass.assignment_type = 'E'
397: AND ass.primary_flag = 'Y'
398: AND ass.business_group_id =

Line 399: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')

395: AND pds.person_id = ass.person_id
396: AND ass.assignment_type = 'E'
397: AND ass.primary_flag = 'Y'
398: AND ass.business_group_id =
399: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
400: AND ass.effective_start_date <
401: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
402: and ass.effective_end_date >
403: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

Line 401: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

397: AND ass.primary_flag = 'Y'
398: AND ass.business_group_id =
399: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
400: AND ass.effective_start_date <
401: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
402: and ass.effective_end_date >
403: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
404: AND ass.job_id = job.job_id
405: AND job.job_information_category = 'US'

Line 403: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

399: pay_magtape_generic.get_parameter_value('TRANSFER_BG_ID')
400: AND ass.effective_start_date <
401: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
402: and ass.effective_end_date >
403: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
404: AND ass.job_id = job.job_id
405: AND job.job_information_category = 'US'
406: AND job.date_from <=
407: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

Line 407: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')

403: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
404: AND ass.job_id = job.job_id
405: AND job.job_information_category = 'US'
406: AND job.date_from <=
407: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
408: AND nvl(job.date_to,to_date('31/12/4712','DD/MM/YYYY')) >=
409: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
410: AND ass.location_id IN
411: (select entity_id

Line 409: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')

405: AND job.job_information_category = 'US'
406: AND job.date_from <=
407: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_END'),'YYYY/MM/DD HH24:MI:SS')
408: AND nvl(job.date_to,to_date('31/12/4712','DD/MM/YYYY')) >=
409: to_date(pay_magtape_generic.get_parameter_value('TRANSFER_DATE_START'),'YYYY/MM/DD HH24:MI:SS')
410: AND ass.location_id IN
411: (select entity_id
412: from per_gen_hierarchy_nodes
413: where node_type = 'EST'

Line 414: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')

410: AND ass.location_id IN
411: (select entity_id
412: from per_gen_hierarchy_nodes
413: where node_type = 'EST'
414: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
415: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
416: union
417: select pghn2.entity_id
418: from per_gen_hierarchy_nodes pghn,

Line 415: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')

411: (select entity_id
412: from per_gen_hierarchy_nodes
413: where node_type = 'EST'
414: and entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
415: and hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID')
416: union
417: select pghn2.entity_id
418: from per_gen_hierarchy_nodes pghn,
419: per_gen_hierarchy_nodes pghn2

Line 420: where pghn.entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')

416: union
417: select pghn2.entity_id
418: from per_gen_hierarchy_nodes pghn,
419: per_gen_hierarchy_nodes pghn2
420: where pghn.entity_id = pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
421: and pghn.hierarchy_version_id = pghn2.hierarchy_version_id
422: and pghn2.parent_hierarchy_node_id = pghn.hierarchy_node_id
423: and pghn2.node_type = 'LOC'
424: and pghn.node_type = 'EST'

Line 425: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID'))

421: and pghn.hierarchy_version_id = pghn2.hierarchy_version_id
422: and pghn2.parent_hierarchy_node_id = pghn.hierarchy_node_id
423: and pghn2.node_type = 'LOC'
424: and pghn.node_type = 'EST'
425: and pghn.hierarchy_version_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_STR_V_ID'))
426: ;
427:
428: END per_us_vets_tap;