1 PACKAGE per_us_vetc_tap AUTHID CURRENT_USER AS
2 /* $Header: petapvtc.pkh 115.6 2002/03/13 07:37:13 pkm ship $ */
3 /*
4 * **************************************************************************
5 *
6
7 Copyright (c) Oracle Corporation (UK) Ltd 1993.
8 All Rights Reserved.
9
10 PRODUCT
11 Oracle Human Resources
12
13 NAME
14
15
16 DESCRIPTION
17 Magnetic tape format procedure.
18
19 1.0 Overview
20
21 A PL/SQL package will be written for each type of magnetic tape. The packag
22 e
23 will include all cursors and procedures required for the particular magneti
24 c
25 tape format. A stored procedure provides the top level of control flow for
26 the magnetic tape file generation. This may call other procedures dependant
27
28 on the state of the cursors and the input parameters.
29
30 The stored procedure will be called before each execution of a
31 formula. Parameters returned as results of the previous formula execution
32 will be passed to the procedure. The procedure must handle all context
33 cursors needed and may also set parameters required by the formula.
34
35 Using NACHA as an example, for the file header record formula, a call
36 to a cursor which fetches legal_company_id must be performed.
37
38 The interface between the 'C' process and the stored procedure will make
39 extensive use of PL/SQL tables. PL/SQL tables are single column tables whic
40 h
41 are accessed by an integer index value. Items in the tables will use indexe
42 s
43 begining with 1 and increasing contiguously to the number of elements. The
44 index number will be used to match items in the name and value tables.
45
46 The first element in the value tables will always be the number of elements
47 available in the table. The elements in the tables will be of type VARCHAR2
48 any conversion necessary should be performed within the PL/SQL procedure.
49
50 The parameters returned by formula execution will be passed
51 to the stored procedure. Parameters may or may not be altered by the PL/SQL
52 procedure and will be passed back to the formula for the next execution.
53 Context tables will always be reset by the PL/SQL procedure.
54
55 The names of the tables used to interface with the PL/SQL procedure are
56 param_names type IN/OUT
57 param_values type IN/OUT
58 context_names type OUT
59 context_values type OUT
60
61 The second item in the output_parameter_value table will be the formula ID
62 of the next formula to be executed (the first item is the number of values
63 in the table).
64
65 Change List
66 -----------
67 Date Name Vers Bug No Description
68 ---- ---- ---- ------ -----------
69 18-AUG-98 ASAHAY 110.0 Created.
70 24-AUG-98 ASAHAY 110.1 VETS100_reporting Name =
71 Consolidated
72 24-SEP-98 ASAHAY 110.2 Corrected typo in S_VETC_VETS_NH
73 18-SEP-00 ASAHAY 110.3 added cursor for min count
74 20-SEP-00 ASAHAY 110.4 commented check for GRE
75 in VETS Count
76 28-SEP-00 ASAHAY 110.5 Corrected Date Formats
77
78 Package header:
79 -- Cursors
80
81 */
82
83
84 LEVEL_CNT NUMBER;
85
86 /*
87 Selects tax_unit_id and/or establishment_id - so indicating what type of organization we are processing. i.e. Whether it is a tax unit only, establishment only or a combination.
88 */
89
90 CURSOR S_VETC_GRE IS
91 SELECT 'TRANSFER_TAX_UNIT_ID=P',
92 htuv.tax_unit_id,
93 'TRANSFER_ESTABLISHMENT_ID1=P',
94 est.establishment_id
95 FROM hr_establishments_v est,
96 hr_organization_units hou1,
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 =
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. */
111
112 CURSOR S_VETC_RE IS
113 SELECT 'TRANSFER_GRE_STATE=P' ,
114 psr1.state_code ,
115 'TRANSFER_EST_STATE=P' ,
116 psr2.state_code ,
117 'TRANSFER_ESTABLISHMENT_ID=P' ,
118 est.establishment_id ,
119 'TRANSFER_MSC=P' ,
120 to_char(count(distinct(est.establishment_id))) ,
121 'TRANSFER_VETS100_COMPANY_NUMBER=P' ,
122 htuv.vets100_company_number ,
123 'TRANSFER_VETS100_UNIT_NUMBER=P' ,
124 est.vets100_unit_number ,
125 'TRANSFER_SIC=P' ,
126 est.sic ,
127 'TRANSFER_HQ_VETS100_REPORTING_NAME=P' ,
128 upper(nvl(htuv.vets100_reporting_name, htuv.name)) ,
129 'TRANSFER_EST_VETS100_REPORTING_NAME=P' ,
130 /* upper(nvl(est.vets100_reporting_name, est.name)) , */
131 'CONSOLIDATED' ,
132 'TRANSFER_HQLOC_ADDRESS=P' ,
133 upper(rpad(cloc.address_line_1 ||' '|| cloc.address_line_2 ||' '||
134 cloc.address_line_3,35)) ,
135 'TRANSFER_ELOC_ADDRESS=P' ,
136 upper(rpad(eloc.address_line_1 ||' '|| eloc.address_line_2 ||' '||
137 eloc.address_line_3,35)) ,
138 'TRANSFER_HQLOC_TOWN_OR_CITY=P' ,
139 upper(cloc.town_or_city) ,
140 'TRANSFER_HQLOC_REGION_1=P' ,
141 upper(cloc.region_1) ,
142 'TRANSFER_ELOC_TOWN_OR_CITY=P' ,
143 upper(eloc.town_or_city) ,
144 'TRANSFER_ELOC_REGION_1=P' ,
145 upper(eloc.region_1) ,
146 'TRANSFER_HQLOC_REGION_2=P' ,
147 upper(cloc.region_2) ,
148 'TRANSFER_HQLOC_POSTAL_CODE=P' ,
149 upper(cloc.postal_code) ,
150 'TRANSFER_ELOC_REGION_2=P' ,
151 upper(eloc.region_2) ,
152 'TRANSFER_ELOC_POSTAL_CODE=P' ,
153 upper(eloc.postal_code) ,
154 'TRANSFER_EIN=P' ,
155 htuv.employer_identification_number ,
156 'TRANSFER_DUN=P' ,
157 htuv.dun_and_bradstreet_number
158 FROM hr_locations cloc ,
159 hr_locations eloc ,
160 hr_tax_units_v htuv ,
161 hr_establishments_v est ,
162 hr_organization_units hou1 ,
163 hr_organization_units hou2 ,
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
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
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 =
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 ,
203 psr2.state_code ,
204 est.establishment_id ,
205 htuv.vets100_company_number ,
206 est.vets100_unit_number ,
207 est.sic ,
208 upper(nvl(htuv.vets100_reporting_name, htuv.name)) ,
209 upper(rpad(cloc.address_line_1 ||' '|| cloc.address_line_2 ||' '||
210 cloc.address_line_3,35)) ,
211 upper(rpad(eloc.address_line_1 ||' '|| eloc.address_line_2 ||' '||
212 eloc.address_line_3,35)) ,
213 upper(cloc.town_or_city) ,
214 upper(cloc.region_1) ,
215 upper(eloc.town_or_city) ,
216 upper(eloc.region_1) ,
217 upper(cloc.region_2) ,
218 upper(cloc.postal_code) ,
219 upper(eloc.region_2) ,
220 upper(eloc.postal_code) ,
221 htuv.employer_identification_number ,
222 htuv.dun_and_bradstreet_number
223 ;
224
225
226 /* This has to be in a separate query becase of the counting mechanism
227 and the fact we have to display a row for the job category even if the
228 category is not used in any jobs */
229
230 CURSOR S_VETC_JOBS IS
231 SELECT 'TRANSFER_JOB_CATEGORY_NAME=P',
232 upper(rpad(meaning,32,'.'))|| lookup_code,
233 'TRANSFER_JOB_CATEGORY_CODE=P',
234 lookup_code
235 FROM hr_lookups
236 WHERE lookup_type = 'US_EEO1_JOB_CATEGORIES'
237 ORDER BY lookup_code ;
238
239
240
241 /* Count all people within job categories where person is assigned to current
242 tax unit and establishment. If the establishment on the assignment is null
243 then we search the hierarchy (using the current establishment as root) to
244 pick up Personnel Organizations and count the employee if his assignment
245 personnel organization is part of the hierarchy. Thereby we can default
246 establishments by making use of existing Personnel Information.
247 */
248
249 CURSOR S_VETC_VETS IS
250 SELECT 'TRANSFER_NO_DIS_VETS=P',
251 count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,'OTEDV',1,'DVOEV',1,null)) no_dis_vets,
252 'TRANSFER_NO_VIET_VETS=P',
253 count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,'DVOEV',1,'VOEVV',1,null)) no_viet_vets,
254 'TRANSFER_NO_OT_EV=P',
255 nvl(count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,'VOEVV',1,null)),0)
256 FROM per_people_f peo,
257 per_assignments_f ass,
258 hr_organization_information hoi1,
259 hr_organization_information hoi2,
260 hr_soft_coding_keyflex scf,
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')
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
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'
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
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
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'))
312 FROM dual
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 =
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
330 /* Count all people within job categories where person is assigned to current
331 tax unit and establishment. If the establishment on the assignment is null
332 then we search the hierarchy (using the current establishment as root) to
333 pick up Personnel Organizations and count the employee if his assignment
334 personnel organization is part of the hierarchy. Thereby we can default
335 establishments by making use of existing Personnel Information.
336 */
337
338 CURSOR S_VETC_VETS_NH IS
339 SELECT 'TRANSFER_TOT_NEW_HIRES=P',
340 count(peo.person_id) tot_new_hires,
341 'TRANSFER_NO_NH_DIS_VETS=P',
342 count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,'OTEDV',1,'DVOEV',1,null))no_nh_dis_vets,
343 'TRANSFER_NO_NH_VIET_VETS=P',
344 count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,'DVOEV',1,'VOEVV',1,null))no_nh_viet_vets,
345 'TRANSFER_NO_NH_OT_EV=P',
346 nvl(count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,'VOEVV',1,null)),0)
347 FROM per_people_f peo,
348 per_assignments_f ass,
349 hr_organization_information hoi1,
350 hr_organization_information hoi2,
351 hr_soft_coding_keyflex scf,
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')
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
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'
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')
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
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
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
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 =
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
424 /*
425 Count employee assignments. We use this to determine company size and so distinguish between those companies with 50 or more employees.
426 */
427
428 cursor S_VETC_ASG_COUNT is
429 SELECT 'TRANSFER_COUNT_ASSIGNMENTS=P',
430 count(ass.assignment_id)
431 FROM per_assignments_f ass,
432 hr_establishments_v hev,
433 hr_locations hl,
434 pay_state_rules psr,
435 hr_organization_information hoi1,
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
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'
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 =
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')
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'))
475 FROM dual
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 =
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',
493 count (distinct pds.person_id)
494 FROM per_periods_of_service pds,
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
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')
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 =
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'))
529 FROM dual
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')
539 START WITH ose.organization_id_parent =
540 pay_magtape_generic.get_parameter_value('TRANSFER_ESTABLISHMENT_ID')
541 ));
542
543 end;
[Home] [Help]