[Home] [Help]
PACKAGE BODY: APPS.PQH_DE_OPR_GRP
Source
1 Package Body PQH_DE_OPR_GRP As
2 /* $Header: pqtskpln.pkb 120.0 2005/05/29 02:50:49 appldev noship $ */
3
4
5 Function Node_Sequence(P_Hierarchy_version_id IN Number,
6 P_Parent_Hierarchy_Id IN Number)
7 Return Number is
8 Cursor Seq is
9 Select Nvl(max(SEQ),0) + 1
10 From Per_gen_Hierarchy_Nodes
11 Where Hierarchy_Version_Id = p_Hierarchy_Version_id
12 and Parent_Hierarchy_Node_Id = P_Parent_Hierarchy_Id;
13
14 l_Seq Per_Gen_hierarchy_nodes.Seq%TYPE;
15
16 Begin
17 open Seq;
18 Fetch Seq into l_seq;
19 Close Seq;
20 Return l_Seq;
21 End;
22
23
24 Procedure copy_Hierarchy
25 (P_Hierarchy_version_id IN Number,
26 P_Parent_Hierarchy_id IN Number,
27 P_Hierarchy_Id IN Number,
28 p_Business_group_Id IN Number,
29 p_Effective_Date IN Date) Is
30
31 Cursor C1 IS
32 Select Node_Type , Entity_Id , Hierarchy_Node_id , Parent_Hierarchy_Node_Id , Hierarchy_Version_Id , ATTRIBUTE_CATEGORY,
33 ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ,
34 ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 ,
35 ATTRIBUTE19 , ATTRIBUTE20 , ATTRIBUTE21 , ATTRIBUTE22 , ATTRIBUTE23 , ATTRIBUTE24 , ATTRIBUTE25 , ATTRIBUTE26 , ATTRIBUTE27 , ATTRIBUTE28 ,
36 ATTRIBUTE29 , ATTRIBUTE30 , INFORMATION1 , INFORMATION2 , INFORMATION3 , INFORMATION4 , INFORMATION5 , INFORMATION6 , INFORMATION7 , INFORMATION8 ,
37 INFORMATION9 , INFORMATION10, INFORMATION11, INFORMATION12, INFORMATION13, INFORMATION14, INFORMATION15, INFORMATION16, INFORMATION17, INFORMATION18,
38 INFORMATION19 , INFORMATION20, INFORMATION21, INFORMATION22, INFORMATION23, INFORMATION24, INFORMATION25, INFORMATION26, INFORMATION27, INFORMATION28,
39 INFORMATION29 , INFORMATION30, INFORMATION_CATEGORY
40 From Per_gen_Hierarchy_Nodes a
41 Start with Hierarchy_Node_Id = P_Hierarchy_Id
42 Connect by Parent_Hierarchy_Node_Id = Prior Hierarchy_Node_id;
43
44 Cursor C2 (P_Parent_Hierarchy_Node_id IN Number) is
45 Select Node_Type, Entity_id
46 from Per_Gen_hierarchy_Nodes
47 Where Hierarchy_Node_Id = P_Parent_Hierarchy_Node_id;
48
49 Cursor c3(P_Node_type IN Varchar2,
50 P_Entity_Id Varchar2) Is
51 Select hierarchy_Node_Id
52 from Per_Gen_Hierarchy_Nodes
53 Where Hierarchy_version_Id = p_Hierarchy_version_id
54 and Node_type = P_Node_Type
55 and Entity_Id = P_Entity_Id
56 and Request_Id = -999;
57
58 l_Hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
59 l_Parent_hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Parent_Hierarchy_Node_Id%TYPE;
60 l_Object_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
61 l_Node_type Per_Gen_Hierarchy_Nodes.Node_type%TYPE;
62 l_Entity_Id Per_Gen_Hierarchy_Nodes.Entity_Id%TYPE;
63
64 Begin
65 l_Parent_hierarchy_Node_Id := NULL;
66 For C1rec in C1
67 Loop
68 If l_Parent_hierarchy_Node_Id Is NULL Then
69 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
70 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
71 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
72 P_ENTITY_ID => C1rec.Entity_id,
73 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
74 P_NODE_TYPE => C1rec.Node_type,
75 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_hierarchy_Id),
76 P_PARENT_HIERARCHY_NODE_ID => P_Parent_hierarchy_Id,
77 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
78 P_REQUEST_ID => -999,
79 P_ATTRIBUTE_CATEGORY => C1rec.Attribute_Category,
80 P_ATTRIBUTE1 => C1rec.Attribute1,
81 P_ATTRIBUTE2 => C1rec.Attribute2,
82 P_ATTRIBUTE3 => C1rec.Attribute3,
83 P_ATTRIBUTE4 => C1rec.Attribute4,
84 P_ATTRIBUTE5 => C1rec.Attribute5,
85 P_ATTRIBUTE6 => C1rec.Attribute6,
86 P_ATTRIBUTE7 => C1rec.Attribute7,
87 P_ATTRIBUTE8 => C1rec.Attribute8,
88 P_ATTRIBUTE9 => C1rec.Attribute9,
89 P_ATTRIBUTE10 => C1rec.Attribute10,
90 P_ATTRIBUTE11 => C1rec.Attribute11,
91 P_ATTRIBUTE12 => C1rec.Attribute12,
92 P_ATTRIBUTE13 => C1rec.Attribute13,
93 P_ATTRIBUTE14 => C1rec.Attribute14,
94 P_ATTRIBUTE15 => C1rec.Attribute15,
95 P_ATTRIBUTE16 => C1rec.Attribute16,
96 P_ATTRIBUTE17 => C1rec.Attribute17,
97 P_ATTRIBUTE18 => C1rec.Attribute18,
98 P_ATTRIBUTE19 => C1rec.Attribute19,
99 P_ATTRIBUTE20 => C1rec.Attribute20,
100 P_ATTRIBUTE21 => C1rec.Attribute21,
101 P_ATTRIBUTE22 => C1rec.Attribute22,
102 P_ATTRIBUTE23 => C1rec.Attribute23,
103 P_ATTRIBUTE24 => C1rec.Attribute24,
104 P_ATTRIBUTE25 => C1rec.Attribute25,
105 P_ATTRIBUTE26 => C1rec.Attribute26,
106 P_ATTRIBUTE27 => C1rec.Attribute27,
107 P_ATTRIBUTE28 => C1rec.Attribute28,
108 P_ATTRIBUTE29 => C1rec.Attribute29,
109 P_ATTRIBUTE30 => C1rec.Attribute30,
110 P_INFORMATION_CATEGORY => C1rec.Information_Category,
111 P_INFORMATION1 => C1rec.Information1,
112 P_INFORMATION2 => C1rec.Information2,
113 P_INFORMATION3 => C1rec.Information3,
114 P_INFORMATION4 => C1rec.Information4,
115 P_INFORMATION5 => C1rec.Information5,
116 P_INFORMATION6 => C1rec.Information6,
117 P_INFORMATION7 => C1rec.Information7,
118 P_INFORMATION8 => C1rec.Information8,
119 P_INFORMATION9 => C1rec.Information9,
120 P_INFORMATION10 => C1rec.Information10,
121 P_INFORMATION11 => C1rec.Information11,
122 P_INFORMATION12 => C1rec.Information12,
123 P_INFORMATION13 => C1rec.Information13,
124 P_INFORMATION14 => C1rec.Information14,
125 P_INFORMATION15 => C1rec.Information15,
126 P_INFORMATION16 => C1rec.Information16,
127 P_INFORMATION17 => C1rec.Information17,
128 P_INFORMATION18 => C1rec.Information18,
129 P_INFORMATION19 => C1rec.Information19,
130 P_INFORMATION20 => C1rec.Information20,
131 P_INFORMATION21 => C1rec.Information21,
132 P_INFORMATION22 => C1rec.Information22,
133 P_INFORMATION23 => C1rec.Information23,
134 P_INFORMATION24 => C1rec.Information24,
135 P_INFORMATION25 => C1rec.Information25,
136 P_INFORMATION26 => C1rec.Information26,
137 P_INFORMATION27 => C1rec.Information27,
138 P_INFORMATION28 => C1rec.Information28,
139 P_INFORMATION29 => C1rec.Information29,
140 P_INFORMATION30 => C1rec.Information30,
141 P_EFFECTIVE_DATE => p_Effective_Date);
142 l_Parent_hierarchy_Node_Id := Nvl(P_Parent_hierarchy_Id, 0);
143 Else
144 Open C2(C1rec.Parent_hierarchy_node_Id);
145 Fetch C2 into L_Node_Type, l_Entity_Id;
146 Close C2;
147 Open C3(l_Node_type, l_Entity_Id);
148 Fetch C3 into l_Parent_Hierarchy_Node_id;
149 Close C3;
150 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
151 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
152 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
153 P_ENTITY_ID => C1rec.Entity_id,
154 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
155 P_NODE_TYPE => C1rec.Node_type,
156 P_SEQ => Node_Sequence(P_Hierarchy_version_id,l_Parent_Hierarchy_Node_Id),
157 P_PARENT_HIERARCHY_NODE_ID => l_Parent_hierarchy_Node_id,
158 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
159 P_REQUEST_ID => -999,
160 P_ATTRIBUTE_CATEGORY => C1rec.Attribute_Category,
161 P_ATTRIBUTE1 => C1rec.Attribute1,
162 P_ATTRIBUTE2 => C1rec.Attribute2,
163 P_ATTRIBUTE3 => C1rec.Attribute3,
164 P_ATTRIBUTE4 => C1rec.Attribute4,
165 P_ATTRIBUTE5 => C1rec.Attribute5,
166 P_ATTRIBUTE6 => C1rec.Attribute6,
167 P_ATTRIBUTE7 => C1rec.Attribute7,
168 P_ATTRIBUTE8 => C1rec.Attribute8,
169 P_ATTRIBUTE9 => C1rec.Attribute9,
170 P_ATTRIBUTE10 => C1rec.Attribute10,
171 P_ATTRIBUTE11 => C1rec.Attribute11,
172 P_ATTRIBUTE12 => C1rec.Attribute12,
173 P_ATTRIBUTE13 => C1rec.Attribute13,
174 P_ATTRIBUTE14 => C1rec.Attribute14,
175 P_ATTRIBUTE15 => C1rec.Attribute15,
176 P_ATTRIBUTE16 => C1rec.Attribute16,
177 P_ATTRIBUTE17 => C1rec.Attribute17,
178 P_ATTRIBUTE18 => C1rec.Attribute18,
179 P_ATTRIBUTE19 => C1rec.Attribute19,
180 P_ATTRIBUTE20 => C1rec.Attribute20,
181 P_ATTRIBUTE21 => C1rec.Attribute21,
182 P_ATTRIBUTE22 => C1rec.Attribute22,
183 P_ATTRIBUTE23 => C1rec.Attribute23,
184 P_ATTRIBUTE24 => C1rec.Attribute24,
185 P_ATTRIBUTE25 => C1rec.Attribute25,
186 P_ATTRIBUTE26 => C1rec.Attribute26,
187 P_ATTRIBUTE27 => C1rec.Attribute27,
188 P_ATTRIBUTE28 => C1rec.Attribute28,
189 P_ATTRIBUTE29 => C1rec.Attribute29,
190 P_ATTRIBUTE30 => C1rec.Attribute30,
191 P_INFORMATION_CATEGORY => C1rec.Information_Category,
192 P_INFORMATION1 => C1rec.Information1,
193 P_INFORMATION2 => C1rec.Information2,
194 P_INFORMATION3 => C1rec.Information3,
195 P_INFORMATION4 => C1rec.Information4,
196 P_INFORMATION5 => C1rec.Information5,
197 P_INFORMATION6 => C1rec.Information6,
198 P_INFORMATION7 => C1rec.Information7,
199 P_INFORMATION8 => C1rec.Information8,
200 P_INFORMATION9 => C1rec.Information9,
201 P_INFORMATION10 => C1rec.Information10,
202 P_INFORMATION11 => C1rec.Information11,
203 P_INFORMATION12 => C1rec.Information12,
204 P_INFORMATION13 => C1rec.Information13,
205 P_INFORMATION14 => C1rec.Information14,
206 P_INFORMATION15 => C1rec.Information15,
207 P_INFORMATION16 => C1rec.Information16,
208 P_INFORMATION17 => C1rec.Information17,
209 P_INFORMATION18 => C1rec.Information18,
210 P_INFORMATION19 => C1rec.Information19,
211 P_INFORMATION20 => C1rec.Information20,
212 P_INFORMATION21 => C1rec.Information21,
213 P_INFORMATION22 => C1rec.Information22,
214 P_INFORMATION23 => C1rec.Information23,
215 P_INFORMATION24 => C1rec.Information24,
216 P_INFORMATION25 => C1rec.Information25,
217 P_INFORMATION26 => C1rec.Information26,
218 P_INFORMATION27 => C1rec.Information27,
219 P_INFORMATION28 => C1rec.Information28,
220 P_INFORMATION29 => C1rec.Information29,
221 P_INFORMATION30 => C1rec.Information30,
222 P_EFFECTIVE_DATE => p_Effective_Date);
223 End If;
224
225 End Loop;
226 Update Per_Gen_Hierarchy_Nodes
227 Set REQUEST_ID = 0
228 Where REQUEST_ID = -999;
229 End;
230
231 Procedure Main
232 (P_Type IN Varchar2,
233 P_Trntype IN Varchar2,
234 P_Code IN Varchar2 Default NULL,
235 P_Description IN Varchar2 Default NULL,
236 p_Code_Id IN Number Default NULL,
237 P_Hierarchy_version_id IN Number Default NULL,
238 P_Parent_Hierarchy_id IN Number Default NULL,
239 P_Hierarchy_Id IN Number Default NULL,
240 p_Object_Version_Number IN Number Default NULL,
241 p_Business_group_Id IN Number ,
242 p_Effective_Date IN Date) Is
243
244 l_Hierarchy_id Per_Gen_Hierarchy.Hierarchy_Id%TYPE;
245 l_Hierarchy_Version_id Per_Gen_Hierarchy_Versions.Hierarchy_version_Id%TYPE;
246 l_HObject_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
247 l_Object_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
248 l_VObject_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
249 l_Hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
250 /* l_Node_id Pqh_de_operations.Operation_Id%TYPE; */
251 l_version_count Number(15);
252
253 Cursor C1 IS
254 Select Hierarchy_Node_id, Object_version_number
255 From Per_gen_Hierarchy_Nodes a
256 Start with Hierarchy_Node_Id = P_Hierarchy_Id
257 Connect by Parent_Hierarchy_Node_Id = Prior Hierarchy_Node_id
258 Order By Nvl(Parent_Hierarchy_Node_Id,0) Desc;
259
260 Cursor C2 is
261 Select Pgh.Hierarchy_id, pgh.Object_version_number hovn,
262 pgv.Hierarchy_version_id, pgv.Object_Version_number vovn
263 From Per_Gen_hierarchy_Versions pgv, Per_gen_hierarchy pgh
264 Where Hierarchy_Version_id = P_Hierarchy_version_id
265 and pgv.Hierarchy_id = pgh.Hierarchy_id;
266
267 Cursor C3 is
268 Select Hierarchy_Node_Id, Object_Version_Number
269 From Per_Gen_Hierarchy_Nodes
270 Start With Hierarchy_Version_id = P_Hierarchy_Version_id
271 and Parent_hierarchy_node_id is NULL
272 Connect By Parent_hierarchy_Node_id = Prior Hierarchy_Node_id
273 Order By Nvl(Parent_Hierarchy_Node_id,0) Desc;
274
275 Cursor C4 is
276 Select count(*)
277 From Per_Gen_Hierarchy_Versions pgv, Per_Gen_Hierarchy pgh
278 Where pgh.Hierarchy_id = (Select Hierarchy_id
279 From Per_Gen_Hierarchy_Versions
280 Where Hierarchy_Version_Id = P_Hierarchy_Version_Id)
281 and pgv.hierarchy_id = pgh.hierarchy_id;
282
283
284 Begin
285
286 If p_Type = 'P' and P_Trntype = 'I' Then
287
288 Per_hierarchy_api.CREATE_HIERARCHY
289 (P_HIERARCHY_ID => l_Hierarchy_id ,
290 P_BUSINESS_GROUP_ID => p_Business_group_Id,
291 P_NAME => P_Description ,
292 P_TYPE => 'OPERATION_PLAN' ,
293 P_OBJECT_VERSION_NUMBER => l_HObject_version_Number,
294 P_EFFECTIVE_DATE => p_Effective_Date);
295
296 Per_hierarchy_versions_api.create_hierarchy_versions
297 (P_HIERARCHY_VERSION_ID => l_Hierarchy_Version_id,
298 P_BUSINESS_GROUP_ID => p_Business_group_Id,
299 P_VERSION_NUMBER => 1,
300 P_HIERARCHY_ID => l_Hierarchy_id,
301 P_DATE_FROM => P_EFFECTIVE_DATE,
302 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number ,
303 P_STATUS => 'A',
304 P_VALIDATE_FLAG => 'Y',
305 P_EFFECTIVE_DATE => p_Effective_Date);
306
307 ElsIf P_Trntype = 'R' Then
308 --Get the number of versions for the hierarchy
309 Open C4;
310 Fetch C4 into l_version_count;
311 Close C4;
312 For C2Rec in C2
313 Loop
314
315 For C3rec in C3
316 Loop
317 l_Object_Version_Number := C3rec.Object_version_Number;
318 Per_Hierarchy_Nodes_api.DELETE_HIERARCHY_NODES
319 (P_Hierarchy_Node_Id => C3rec.Hierarchy_Node_id,
320 P_Object_Version_Number => l_Object_Version_Number);
321 End Loop;
322
323 l_object_version_number := c2rec.vovn;
324 Per_Hierarchy_versions_api.DELETE_HIERARCHY_VERSIONS
325 (P_HIERARCHY_VERSION_ID => C2rec.Hierarchy_version_id,
326 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
327 P_EFFECTIVE_DATE => P_Effective_Date);
328
329 if(l_version_count < 2) then
330 l_object_version_number := c2rec.hovn;
331 Per_Hierarchy_api.Delete_Hierarchy
332 (P_Hierarchy_Id => C2rec.Hierarchy_id,
333 P_Object_Version_Number => l_Object_Version_Number);
334 End if;
335 End Loop;
336
337 ElsIf p_Type = 'G' and P_Trntype = 'I' Then
338
339 /* PQH_DE_OPERATION_GROUPS_API.INSERT_OPERATION_GROUPS
340 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
341 P_OPERATION_GROUP_CODE => P_Code,
342 P_DESCRIPTION => P_Description,
343 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
344 P_OPERATION_GROUP_ID => l_Node_id,
345 P_OBJECT_VERSION_NUMBER => l_Object_version_Number); */
346
347 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
348 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
349 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
350 P_ENTITY_ID => P_Code,
351 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
352 P_NODE_TYPE => 'OPR_GROUP',
353 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
354 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
355 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
356 P_EFFECTIVE_DATE => p_Effective_Date);
357
358 ElsIf p_Type = 'G' and P_Trntype = 'U' Then
359
360 l_Object_version_Number := P_Object_version_Number;
361
362 /* PQH_DE_OPERATION_GROUPS_API.UPDATE_OPERATION_GROUPS
363 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
364 P_OPERATION_GROUP_ID => P_Code_Id,
365 P_OBJECT_VERSION_NUMBER => l_Object_Version_Number,
366 P_OPERATION_GROUP_CODE => P_Code,
367 P_DESCRIPTION => P_Description); */
368
369
370 ElsIf p_Type = 'O' and P_Trntype = 'I' Then
371
372 /* PQH_DE_OPERATIONS_API.INSERT_OPERATIONS
373 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
374 P_OPERATION_NUMBER => P_Code,
375 P_DESCRIPTION => P_Description,
376 P_OPERATION_ID => L_Node_id,
377 P_OBJECT_VERSION_NUMBER => l_Object_version_Number); */
378
379 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
380 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
381 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
382 P_ENTITY_ID => P_Code,
383 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
384 P_NODE_TYPE => 'OPR_OPTS',
385 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
386 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
387 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
388 P_EFFECTIVE_DATE => p_Effective_Date);
389
390 ElsIf p_Type = 'O' and P_Trntype = 'U' Then
391
392 l_Object_version_Number := P_Object_version_Number;
393
394 /* PQH_DE_OPERATIONS_API.UPDATE_OPERATIONS
395 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
396 P_OPERATION_NUMBER => P_Code,
397 P_DESCRIPTION => P_Description,
398 P_OPERATION_ID => P_Code_Id,
399 P_OBJECT_VERSION_NUMBER => l_Object_Version_Number); */
400
401 ElsIf p_Type = 'J' and P_Trntype = 'I' Then
402
403 /* PQH_DE_TKTDTLS_API.INSERT_TKT_DTLS
404 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
405 P_TATIGKEIT_NUMBER => P_Code,
406 P_DESCRIPTION => P_Description,
407 P_TATIGKEIT_DETAIL_ID => L_Node_id,
408 P_OBJECT_VERSION_NUMBER => l_Object_version_Number); */
409
410 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
411 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
412 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
413 P_ENTITY_ID => P_Code,
414 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
415 P_NODE_TYPE => 'OPR_JOB_DTLS',
416 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
417 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
418 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
419 P_EFFECTIVE_DATE => p_Effective_Date);
420
421 ElsIf p_Type = 'J' and P_Trntype = 'U' Then
422
423 l_Object_version_Number := P_Object_version_Number;
424
425 /* PQH_DE_TKTDTLS_API.UPDATE_TKT_DTLS
426 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
427 P_TATIGKEIT_NUMBER => P_Code,
428 P_DESCRIPTION => P_Description,
429 P_TATIGKEIT_DETAIL_ID => P_Code_Id,
430 P_OBJECT_VERSION_NUMBER => l_Object_Version_Number); */
431
432 ElsIf p_Type = 'F' and P_Trntype = 'I' Then
433
434 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
435 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
436 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
437 P_ENTITY_ID => P_Code,
438 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
439 P_NODE_TYPE => 'OPR_JOB_FTR',
440 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
441 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
442 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
443 P_EFFECTIVE_DATE => p_Effective_Date);
444
445 ElsIf P_Trntype = 'S' Then
446
447 null;
448
449 /* PQH_DE_OPR_GRP.copy_Hierarchy
450 (P_Hierarchy_version_id => P_Hierarchy_version_id,
451 P_Parent_Hierarchy_id => P_Parent_Hierarchy_id,
452 P_Hierarchy_Id => P_Hierarchy_Id,
453 p_Business_group_Id => P_BUSINESS_GROUP_ID,
454 p_Effective_Date => p_Effective_Date); */
455
456 ElsIf P_Trntype = 'D' Then
457
458 For C1rec in C1
459 Loop
460
461 l_Object_version_Number := C1rec.Object_version_Number;
462
463 Per_Hierarchy_Nodes_Api.DELETE_HIERARCHY_NODES
464 (P_HIERARCHY_NODE_ID => C1rec.Hierarchy_Node_Id,
465 P_OBJECT_VERSION_NUMBER => l_Object_Version_Number);
466
467 End Loop;
468
469 End If;
470 End;
471
472 Procedure Copy_Hierarchy_version
473 (P_Type IN Varchar2,
474 P_Name IN Varchar2 Default NULL,
475 P_Date_From IN Date Default NULL,
476 P_Date_To IN Date Default NULL,
477 P_Hierarchy_Id IN Number Default NULL,
478 P_Hierarchy_Version_Id IN Number Default NULL,
479 P_Version_Number IN Number Default NULL,
480 P_Business_Group_id IN Number ,
481 P_Effective_Date IN Date ,
482 P_New_hierarchy_Id OUT NOCOPY Number ,
483 P_New_Hierarchy_Version_Id OUT NOCOPY Number) Is
484
485 Cursor Hierarchy is
486 Select Type, ATTRIBUTE_CATEGORY, ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ,
487 ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 ,
488 ATTRIBUTE19 , ATTRIBUTE20 , ATTRIBUTE21 , ATTRIBUTE22 , ATTRIBUTE23 , ATTRIBUTE24 , ATTRIBUTE25 , ATTRIBUTE26 , ATTRIBUTE27 , ATTRIBUTE28 ,
489 ATTRIBUTE29 , ATTRIBUTE30 , INFORMATION1 , INFORMATION2 , INFORMATION3 , INFORMATION4 , INFORMATION5 , INFORMATION6 , INFORMATION7 , INFORMATION8 ,
490 INFORMATION9 , INFORMATION10, INFORMATION11, INFORMATION12, INFORMATION13, INFORMATION14, INFORMATION15, INFORMATION16, INFORMATION17, INFORMATION18,
491 INFORMATION19, INFORMATION20, INFORMATION21, INFORMATION22, INFORMATION23, INFORMATION24, INFORMATION25, INFORMATION26, INFORMATION27, INFORMATION28,
492 INFORMATION29, INFORMATION30, INFORMATION_CATEGORY
493 From Per_Gen_Hierarchy
494 Where Hierarchy_id = P_Hierarchy_Id;
495
496 Cursor Hierarchy_version is
497 Select Hierarchy_Version_id, VERSION_NUMBER, HIERARCHY_ID , DATE_FROM , DATE_TO , STATUS , VALIDATE_FLAG, ATTRIBUTE_CATEGORY,
498 ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ,
499 ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 ,
500 ATTRIBUTE19 , ATTRIBUTE20 , ATTRIBUTE21 , ATTRIBUTE22 , ATTRIBUTE23 , ATTRIBUTE24 , ATTRIBUTE25 , ATTRIBUTE26 , ATTRIBUTE27 , ATTRIBUTE28 ,
501 ATTRIBUTE29 , ATTRIBUTE30 , INFORMATION1 , INFORMATION2 , INFORMATION3 , INFORMATION4 , INFORMATION5 , INFORMATION6 , INFORMATION7 , INFORMATION8 ,
502 INFORMATION9 , INFORMATION10, INFORMATION11, INFORMATION12, INFORMATION13, INFORMATION14, INFORMATION15, INFORMATION16, INFORMATION17, INFORMATION18,
503 INFORMATION19 , INFORMATION20, INFORMATION21, INFORMATION22, INFORMATION23, INFORMATION24, INFORMATION25, INFORMATION26, INFORMATION27, INFORMATION28,
504 INFORMATION29 , INFORMATION30, INFORMATION_CATEGORY
505 From Per_Gen_Hierarchy_Versions
506 Where ((P_Hierarchy_Version_Id is not NULL and Hierarchy_Version_Id = p_Hierarchy_Version_Id)
507 or (Hierarchy_Id = P_Hierarchy_Id and P_Effective_Date between Date_From and Nvl(Date_To,p_Effective_Date)));
508
509 Cursor Nodes(C_Hierarchy_Version_id In NUMBER) is
510 Select Hierarchy_Node_id
511 From Per_Gen_Hierarchy_Nodes
512 Where Hierarchy_Version_id = C_Hierarchy_version_id
513 and Parent_Hierarchy_Node_Id is NULL;
514
515 l_hierarchy_id Per_Gen_Hierarchy.Hierarchy_id%TYPE;
516 l_Hierarchy_version_id Per_Gen_Hierarchy_Versions.Hierarchy_Version_Id%TYPE;
517 l_Object_version_Number Per_Gen_hierarchy.Object_Version_Number%TYPE;
518
519 Begin
520
521 If P_Type = 'H' Then
522
523 For Hierarchy_Rec in Hierarchy
524 Loop
525 Per_hierarchy_api.CREATE_HIERARCHY
526 (P_HIERARCHY_ID => l_Hierarchy_id ,
527 P_BUSINESS_GROUP_ID => p_Business_group_Id,
528 P_NAME => P_Name ,
529 P_TYPE => Hierarchy_rec.Type ,
530 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
531 P_ATTRIBUTE_CATEGORY => Hierarchy_rec.Attribute_Category,
532 P_ATTRIBUTE1 => Hierarchy_rec.Attribute1,
533 P_ATTRIBUTE2 => Hierarchy_rec.Attribute2,
534 P_ATTRIBUTE3 => Hierarchy_rec.Attribute3,
535 P_ATTRIBUTE4 => Hierarchy_rec.Attribute4,
536 P_ATTRIBUTE5 => Hierarchy_rec.Attribute5,
537 P_ATTRIBUTE6 => Hierarchy_rec.Attribute6,
538 P_ATTRIBUTE7 => Hierarchy_rec.Attribute7,
539 P_ATTRIBUTE8 => Hierarchy_rec.Attribute8,
540 P_ATTRIBUTE9 => Hierarchy_rec.Attribute9,
541 P_ATTRIBUTE10 => Hierarchy_rec.Attribute10,
542 P_ATTRIBUTE11 => Hierarchy_rec.Attribute11,
543 P_ATTRIBUTE12 => Hierarchy_rec.Attribute12,
544 P_ATTRIBUTE13 => Hierarchy_rec.Attribute13,
545 P_ATTRIBUTE14 => Hierarchy_rec.Attribute14,
546 P_ATTRIBUTE15 => Hierarchy_rec.Attribute15,
547 P_ATTRIBUTE16 => Hierarchy_rec.Attribute16,
548 P_ATTRIBUTE17 => Hierarchy_rec.Attribute17,
549 P_ATTRIBUTE18 => Hierarchy_rec.Attribute18,
550 P_ATTRIBUTE19 => Hierarchy_rec.Attribute19,
551 P_ATTRIBUTE20 => Hierarchy_rec.Attribute20,
552 P_ATTRIBUTE21 => Hierarchy_rec.Attribute21,
553 P_ATTRIBUTE22 => Hierarchy_rec.Attribute22,
554 P_ATTRIBUTE23 => Hierarchy_rec.Attribute23,
555 P_ATTRIBUTE24 => Hierarchy_rec.Attribute24,
556 P_ATTRIBUTE25 => Hierarchy_rec.Attribute25,
557 P_ATTRIBUTE26 => Hierarchy_rec.Attribute26,
558 P_ATTRIBUTE27 => Hierarchy_rec.Attribute27,
559 P_ATTRIBUTE28 => Hierarchy_rec.Attribute28,
560 P_ATTRIBUTE29 => Hierarchy_rec.Attribute29,
561 P_ATTRIBUTE30 => Hierarchy_rec.Attribute30,
562 P_INFORMATION_CATEGORY => Hierarchy_rec.Information_Category,
563 P_INFORMATION1 => Hierarchy_rec.Information1,
564 P_INFORMATION2 => Hierarchy_rec.Information2,
565 P_INFORMATION3 => Hierarchy_rec.Information3,
566 P_INFORMATION4 => Hierarchy_rec.Information4,
567 P_INFORMATION5 => Hierarchy_rec.Information5,
568 P_INFORMATION6 => Hierarchy_rec.Information6,
569 P_INFORMATION7 => Hierarchy_rec.Information7,
570 P_INFORMATION8 => Hierarchy_rec.Information8,
571 P_INFORMATION9 => Hierarchy_rec.Information9,
572 P_INFORMATION10 => Hierarchy_rec.Information10,
573 P_INFORMATION11 => Hierarchy_rec.Information11,
574 P_INFORMATION12 => Hierarchy_rec.Information12,
575 P_INFORMATION13 => Hierarchy_rec.Information13,
576 P_INFORMATION14 => Hierarchy_rec.Information14,
577 P_INFORMATION15 => Hierarchy_rec.Information15,
578 P_INFORMATION16 => Hierarchy_rec.Information16,
579 P_INFORMATION17 => Hierarchy_rec.Information17,
580 P_INFORMATION18 => Hierarchy_rec.Information18,
581 P_INFORMATION19 => Hierarchy_rec.Information19,
582 P_INFORMATION20 => Hierarchy_rec.Information20,
583 P_INFORMATION21 => Hierarchy_rec.Information21,
584 P_INFORMATION22 => Hierarchy_rec.Information22,
585 P_INFORMATION23 => Hierarchy_rec.Information23,
586 P_INFORMATION24 => Hierarchy_rec.Information24,
587 P_INFORMATION25 => Hierarchy_rec.Information25,
588 P_INFORMATION26 => Hierarchy_rec.Information26,
589 P_INFORMATION27 => Hierarchy_rec.Information27,
590 P_INFORMATION28 => Hierarchy_rec.Information28,
591 P_INFORMATION29 => Hierarchy_rec.Information29,
592 P_INFORMATION30 => Hierarchy_rec.Information30,
593 P_EFFECTIVE_DATE => p_Effective_Date);
594 l_Object_version_Number := NULL;
595 End Loop;
596 P_New_Hierarchy_Id := l_Hierarchy_id;
597 End If;
598
599 If P_Type in ('H','V') then
600
601
602 For Hierarchy_Ver_rec in Hierarchy_version Loop
603 Per_hierarchy_versions_api.create_hierarchy_versions
604 (P_HIERARCHY_VERSION_ID => l_Hierarchy_Version_id,
605 P_BUSINESS_GROUP_ID => p_Business_group_Id,
606 P_VERSION_NUMBER => Nvl(P_Version_Number,1),
607 P_HIERARCHY_ID => Nvl(l_Hierarchy_id,Hierarchy_Ver_Rec.Hierarchy_Id),
608 P_DATE_FROM => Nvl(P_Date_From,P_EFFECTIVE_DATE),
609 P_DATE_TO => P_Date_To,
610 P_OBJECT_VERSION_NUMBER => l_Object_version_Number ,
611 P_STATUS => 'A',
612 P_VALIDATE_FLAG => 'Y',
613 P_ATTRIBUTE_CATEGORY => Hierarchy_Ver_rec.Attribute_Category,
614 P_ATTRIBUTE1 => Hierarchy_Ver_rec.Attribute1,
615 P_ATTRIBUTE2 => Hierarchy_Ver_rec.Attribute2,
616 P_ATTRIBUTE3 => Hierarchy_Ver_rec.Attribute3,
617 P_ATTRIBUTE4 => Hierarchy_Ver_rec.Attribute4,
618 P_ATTRIBUTE5 => Hierarchy_Ver_rec.Attribute5,
619 P_ATTRIBUTE6 => Hierarchy_Ver_rec.Attribute6,
620 P_ATTRIBUTE7 => Hierarchy_Ver_rec.Attribute7,
621 P_ATTRIBUTE8 => Hierarchy_Ver_rec.Attribute8,
622 P_ATTRIBUTE9 => Hierarchy_Ver_rec.Attribute9,
623 P_ATTRIBUTE10 => Hierarchy_Ver_rec.Attribute10,
624 P_ATTRIBUTE11 => Hierarchy_Ver_rec.Attribute11,
625 P_ATTRIBUTE12 => Hierarchy_Ver_rec.Attribute12,
626 P_ATTRIBUTE13 => Hierarchy_Ver_rec.Attribute13,
627 P_ATTRIBUTE14 => Hierarchy_Ver_rec.Attribute14,
628 P_ATTRIBUTE15 => Hierarchy_Ver_rec.Attribute15,
629 P_ATTRIBUTE16 => Hierarchy_Ver_rec.Attribute16,
630 P_ATTRIBUTE17 => Hierarchy_Ver_rec.Attribute17,
631 P_ATTRIBUTE18 => Hierarchy_Ver_rec.Attribute18,
632 P_ATTRIBUTE19 => Hierarchy_Ver_rec.Attribute19,
633 P_ATTRIBUTE20 => Hierarchy_Ver_rec.Attribute20,
634 P_ATTRIBUTE21 => Hierarchy_Ver_rec.Attribute21,
635 P_ATTRIBUTE22 => Hierarchy_Ver_rec.Attribute22,
636 P_ATTRIBUTE23 => Hierarchy_Ver_rec.Attribute23,
637 P_ATTRIBUTE24 => Hierarchy_Ver_rec.Attribute24,
638 P_ATTRIBUTE25 => Hierarchy_Ver_rec.Attribute25,
639 P_ATTRIBUTE26 => Hierarchy_Ver_rec.Attribute26,
640 P_ATTRIBUTE27 => Hierarchy_Ver_rec.Attribute27,
641 P_ATTRIBUTE28 => Hierarchy_Ver_rec.Attribute28,
642 P_ATTRIBUTE29 => Hierarchy_Ver_rec.Attribute29,
643 P_ATTRIBUTE30 => Hierarchy_Ver_rec.Attribute30,
644 P_INFORMATION_CATEGORY => Hierarchy_Ver_rec.Information_Category,
645 P_INFORMATION1 => Hierarchy_Ver_rec.Information1,
646 P_INFORMATION2 => Hierarchy_Ver_rec.Information2,
647 P_INFORMATION3 => Hierarchy_Ver_rec.Information3,
648 P_INFORMATION4 => Hierarchy_Ver_rec.Information4,
649 P_INFORMATION5 => Hierarchy_Ver_Rec.Information5,
650 P_INFORMATION6 => Hierarchy_Ver_Rec.Information6,
651 P_INFORMATION7 => Hierarchy_Ver_Rec.Information7,
652 P_INFORMATION8 => Hierarchy_Ver_Rec.Information8,
653 P_INFORMATION9 => Hierarchy_Ver_Rec.Information9,
654 P_INFORMATION10 => Hierarchy_Ver_Rec.Information10,
655 P_INFORMATION11 => Hierarchy_Ver_Rec.Information11,
656 P_INFORMATION12 => Hierarchy_Ver_Rec.Information12,
657 P_INFORMATION13 => Hierarchy_Ver_Rec.Information13,
658 P_INFORMATION14 => Hierarchy_Ver_Rec.Information14,
659 P_INFORMATION15 => Hierarchy_Ver_Rec.Information15,
660 P_INFORMATION16 => Hierarchy_Ver_Rec.Information16,
661 P_INFORMATION17 => Hierarchy_Ver_Rec.Information17,
662 P_INFORMATION18 => Hierarchy_Ver_Rec.Information18,
663 P_INFORMATION19 => Hierarchy_Ver_Rec.Information19,
664 P_INFORMATION20 => Hierarchy_Ver_Rec.Information20,
665 P_INFORMATION21 => Hierarchy_Ver_Rec.Information21,
666 P_INFORMATION22 => Hierarchy_Ver_Rec.Information22,
667 P_INFORMATION23 => Hierarchy_Ver_Rec.Information23,
668 P_INFORMATION24 => Hierarchy_Ver_Rec.Information24,
669 P_INFORMATION25 => Hierarchy_Ver_Rec.Information25,
670 P_INFORMATION26 => Hierarchy_Ver_Rec.Information26,
671 P_INFORMATION27 => Hierarchy_Ver_Rec.Information27,
672 P_INFORMATION28 => Hierarchy_Ver_Rec.Information28,
673 P_INFORMATION29 => Hierarchy_Ver_Rec.Information29,
674 P_INFORMATION30 => Hierarchy_Ver_Rec.Information30,
675 P_EFFECTIVE_DATE => p_Effective_Date);
676 P_New_Hierarchy_Version_Id := l_Hierarchy_Version_id;
677 For Node_Rec in Nodes(Hierarchy_Ver_Rec.Hierarchy_Version_id)
678 Loop
679 Pqh_De_Opr_Grp.copy_Hierarchy
680 (P_Hierarchy_version_id => l_Hierarchy_Version_Id,
681 P_Parent_Hierarchy_id => NULL,
682 P_Hierarchy_Id => Node_rec.Hierarchy_Node_id,
683 p_Business_group_Id => P_Business_group_id,
684 p_Effective_Date => P_Effective_Date);
685 End Loop;
686
687 End Loop;
688
689 End If;
690 exception when others then
691 p_new_hierarchy_id := null;
692 p_new_hierarchy_version_id := null;
693 raise;
694
695 End;
696 End;