1 PACKAGE per_us_vets_tap AS
2 /* $Header: petapvts.pkh 120.0 2005/05/31 22:12:57 appldev noship $ */
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 18-SEP-00 ASAHAY 110.2 added cursor for min count
71 20-SEP-00 ASAHAY 110.3 Commented check for GRE
72 in VETS count
73 28-SEP-00 ASAHAY 110.4 Corrected Date format
74 18-SEP-02 GPERRY 115.7 Fixed WWBUG 2529757
75 Convert to use generic hierarchy
76
77 Package header:
78 */
79 -- Cursors
80
81
82
83 LEVEL_CNT NUMBER;
84
85 CURSOR S_VETS_GRE IS
86 select 'TRANSFER_TAX_UNIT_ID=P',
87 pgn.entity_id,
88 'TRANSFER_ESTABLISHMENT_ID1=P',
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
98 SELECT 'TRANSFER_ESTABLISHMENT_ID=P',
99 pghn.entity_id,
100 'TRANSFER_VETS100_COMPANY_NUMBER=P',
101 hoi1.org_information2,
102 'TRANSFER_TYPE_OF_ORG=P',
103 hoi1.org_information1,
104 'TRANSFER_VETS100_UNIT_NUMBER=P',
105 hlei1.lei_information2,
106 'TRANSFER_SIC=P',
107 hlei2.lei_information3 ,
108 'TRANSFER_HQ_VETS100_REPORTING_NAME=P',
109 upper(nvl(hoi1.org_information1, hou.name)),
110 'TRANSFER_EST_VETS100_REPORTING_NAME=P',
111 upper(nvl(hlei2.lei_information1, hou.name)) ,
112 'TRANSFER_HQLOC_ADDRESS=P',
113 upper(rpad(cloc.address_line_1 ||' '|| cloc.address_line_2 ||' '|| cloc.address_line_3,35)),
114 'TRANSFER_ELOC_ADDRESS=P',
115 upper(rpad(eloc.address_line_1 ||' '|| eloc.address_line_2 ||' '|| eloc.address_line_3,35)) ,
116 'TRANSFER_HQLOC_TOWN_OR_CITY=P',
117 upper(cloc.town_or_city),
118 'TRANSFER_HQLOC_REGION_1=P',
119 upper(cloc.region_1) ,
120 'TRANSFER_ELOC_TOWN_OR_CITY=P',
121 upper(eloc.town_or_city),
122 'TRANSFER_ELOC_REGION_1=P',
123 upper(eloc.region_1),
124 'TRANSFER_HQLOC_REGION_2=P',
125 upper(cloc.region_2) ,
126 'TRANSFER_HQLOC_POSTAL_CODE=P',
127 upper(cloc.postal_code),
128 'TRANSFER_ELOC_REGION_2=P',
129 upper(eloc.region_2) ,
130 'TRANSFER_ELOC_POSTAL_CODE=P',
131 upper(eloc.postal_code),
132 'TRANSFER_HQ_ORAGANIZATION_ID=P',
133 hou.organization_id -- for debug
134 ,'TRANSFER_DUN=P',
135 hoi2.org_information4,
136 'TRANSFER_EIN=P',
137 hoi3.org_information1
138 from
139 hr_location_extra_info hlei1
140 ,hr_location_extra_info hlei2
141 ,per_gen_hierarchy_nodes pghn
142 ,per_gen_hierarchy_nodes pgn
143 ,hr_locations_all eloc
144 ,hr_organization_units hou
145 ,hr_organization_information hoi1
146 ,hr_organization_information hoi2
147 ,hr_organization_information hoi3
148 ,hr_locations_all cloc
149 where pgn.hierarchy_version_id = pghn.hierarchy_version_id
150 and pgn.node_type = 'PAR'
151 and pgn.entity_id = hou.organization_id
152 and hoi1.org_information_context = 'VETS_Spec'
153 and hoi1.organization_id = hou.organization_id
154 and hoi2.org_information_context = 'VETS_EEO_Dup'
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
164 and hlei1.information_type = 'VETS-100 Specific Information'
165 and hlei1.lei_information_category= 'VETS-100 Specific Information'
166 and hlei2.information_type = 'Establishment Information'
167 and hlei2.lei_information_category= 'Establishment Information';
168
169
170 CURSOR S_VETS_JOBS IS
171 SELECT 'TRANSFER_JOB_CATEGORY_NAME=P',
172 upper(rpad(meaning,32,'.'))|| lookup_code,
173 'TRANSFER_JOB_CATEGORY_CODE=P',
174 lookup_code
175 FROM hr_lookups
176 WHERE lookup_type = 'US_EEO1_JOB_CATEGORIES'
177 ORDER BY lookup_code ;
178
179
180 CURSOR S_VETS_VETS IS
181 SELECT 'TRANSFER_NO_DIS_VETS=P',
182 nvl(count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNS',1,'VIETDISNSOP',1,null)),0) ,
183 'TRANSFER_NO_VIET_VETS=P',
184 nvl(count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,'VIETDISNSOP',1,null)),0) ,
185 'TRANSFER_NO_EMPS=P',
186 nvl(count(peo.person_id),0),
187 'TRANSFER_NO_OT_EV=P',
188 nvl(count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,null)),0)
189 FROM per_periods_of_service pds,
190 per_people_f peo,
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')
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
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 <=
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)
221 from per_assignments_f paf2
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')
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 >=
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'
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
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'
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
259 SELECT 'TRANSFER_TOT_NEW_HIRES=P',
260 nvl(count(peo.person_id),0) ,
261 'TRANSFER_NO_NH_DIS_VETS=P',
262 nvl(count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNS',1,'VIETDISNSOP',1,null)),0) ,
263 'TRANSFER_NO_NH_VIET_VETS=P',
264 nvl(count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,'VIETDISNSOP',1,null)),0),
265 'TRANSFER_NO_NH_OT_EV=P',
266 nvl(count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,null)),0),
267 'TRANSFER_NO_NH_SEP_VETS=P',
268 nvl(count(decode(peo.per_information5,'NS',1,'NSDIS',1,'NSOP',1,'NSDISOP',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,'VIETDISNSOP',1,null)),0)
269 FROM per_people_f peo,
270 per_assignments_f ass,
271 hr_organization_information hoi1,
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'))
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')
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)
298 from per_assignments_f paf2
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')
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')
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,
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'
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
336 so distinguish between those companies with 50 or more employees. */
337
338 cursor S_VETS_ASG_COUNT is
339 SELECT 'TRANSFER_COUNT_ASSIGNMENTS=P',
340 count(ass.assignment_id)
341 FROM per_assignments_f ass,
342 hr_organization_information hoi1,
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
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'
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
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
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'
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',
384 count (distinct pds.person_id)
385 FROM per_periods_of_service pds,
386 per_assignments_f ass,
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
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')
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
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
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'
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;