[Home] [Help]
PACKAGE BODY: APPS.PQH_DE_OPERATION
Source
1 Package Body Pqh_De_Operation As
2 /* $Header: pqoprpln.pkb 115.9 2002/12/12 05:23:23 vevenkat noship $ */
3 Procedure Insert_Rec
4 (p_BUSINESS_GROUP_ID IN NUMBER
5 ,p_effective_Date IN Date
6 ,p_ENTITY_ID IN Varchar2
7 ,P_Parent_Entity_Id IN Number Default NULL
8 ,p_Operation_Group IN Number
9 ,p_TRN_TYPE IN Varchar2
10 ,p_HIERARCHY_NODE_ID Out NOCOPY NUMBER
11 ,p_Status Out NOCOPY Varchar2
12 ,p_Object_Version_Number Out NOCOPY Number) Is
13
14 l_Hierarchy_Version_Id Per_gen_Hierarchy_Versions.Hierarchy_Version_Id%TYPE;
15 l_Hierarchy_Node_Id Per_gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
16 l_Parent_Hierarchy_Node_Id Per_gen_Hierarchy_Nodes.Parent_Hierarchy_Node_Id%TYPE;
17 l_Node_type Per_gen_Hierarchy_Nodes.Node_Type%TYPE;
18 l_seq Per_gen_Hierarchy_Nodes.Seq%TYPE;
19 l_proc Varchar2(100) := 'Pqh_De_Operation.Insert_Rec';
20
21 Cursor Entity is
22 Select Hierarchy_Node_id
23 from Per_gen_Hierarchy_Nodes a, Pqh_de_operations b
24 Where Parent_hierarchy_Node_id = p_Operation_Group
25 and Node_type = 'OPR_OPTS'
26 and Entity_Id = b.Operation_Number
27 and b.Operation_Id = P_Parent_Entity_Id;
28
29 Cursor Versions is
30 Select Hierarchy_version_Id
31 from Per_gen_hierarchy_Nodes
32 where Hierarchy_Node_Id = p_Operation_Group;
33
34 Begin
35 p_Status := 'Y';
36
37 Open Versions;
38 Fetch Versions into l_Hierarchy_version_id;
39 Close Versions;
40
41 If p_Trn_Type = 'O' then
42 l_Node_type := 'OPR_OPTS';
43 l_Parent_Hierarchy_Node_Id := P_Operation_group;
44 Else
45 l_Node_type := 'OPR_JOB_DTLS';
46 Open Entity;
47 Fetch Entity into l_Parent_Hierarchy_Node_Id;
48 If Entity%NOTFOUND Then
49 p_Status := 'N';
50 Close Entity;
51 Return;
52 End If;
53 Close Entity;
54 End If;
55
56 Select Nvl(Max(Seq),0) + 1
57 Into l_seq
58 From Per_gen_Hierarchy_Nodes
59 Where Hierarchy_Version_Id = l_Hierarchy_Version_Id
60 and Node_type = l_Node_Type;
61
62 Select Per_Gen_Hierarchy_Nodes_s.nextval
63 into l_Hierarchy_Node_Id from Dual;
64
65 Insert into
66 Per_Gen_Hierarchy_Nodes
67 (HIERARCHY_NODE_ID, BUSINESS_GROUP_ID, ENTITY_ID, HIERARCHY_VERSION_ID,
68 NODE_TYPE, PARENT_HIERARCHY_NODE_ID, SEQ, Object_Version_Number)
69 Values
70 (l_Hierarchy_Node_Id, p_BUSINESS_GROUP_ID, p_ENTITY_ID,l_Hierarchy_version_Id,
71 l_Node_type, l_Parent_Hierarchy_Node_Id,l_seq, 1);
72
73 Exception
74 when app_exception.application_exception then
75 if hr_multi_message.exception_add
76 (p_associated_column1 => 'PER_GEN_HIERARCHY_NODES.HIERARCHY_VERSION_ID') then
77 hr_utility.set_location(' Leaving:'||l_proc,60);
78 raise;
79 end if;
80 hr_utility.set_location(' Leaving:'||l_proc,70);
81 End;
82
83 Procedure Populate_Operations
84 (p_Business_group_Id IN Number
85 ,p_Vldtn_Ver_Op_Id IN Number
86 ,p_Hierarchy_Node_Id IN Number
87 ,p_Trn_Type IN Varchar2
88 ,p_Operation_Job_Number IN Varchar2
89 ,p_Operation_Job_Id IN Number) is
90
91 l_Hierarchy_Node_Id Per_Gen_Hierarchy_nodes.Hierarchy_node_Id%TYPE;
92 l_desc Pqh_De_Operations.Description%TYPE;
93 l_Job_Id Pqh_de_Tatigkeit_details.TATIGKEIT_DETAIL_ID%TYPE;
94 l_Wrkplc_Vldtn_Op_Id Pqh_de_Wrkplc_Vldtn_ops.Wrkplc_Vldtn_Op_Id%TYPE;
95 l_WRKPLC_VLDTN_JObFTR_ID Pqh_de_Wrkplc_Vldtn_JobFtrs.WRKPLC_VLDTN_JObFTR_ID%TYPE;
96 l_Object_Version_Number Pqh_de_Wrkplc_Vldtn_ops.Object_Version_Number%TYPE;
97 l_Wrkplc_Vldtn_Job_Id Pqh_de_Wrkplc_Vldtn_Jobs.Wrkplc_Vldtn_Job_Id%TYPE;
98
99
100 /* Cursor Operations is
101 Select Decode(Node_Type,'OPR_OPTS','O','OPR_JOB_DTLS','J','OPR_JOB_FTR','F'), Entity_Id
102 From Per_Gen_Hierarchy_Nodes
103 Where Hierarchy_Version_Id = l_Hierarchy_version_Id
104 Start With Hierarchy_node_id = p_Hierarchy_Node_id
105 Connect By Parent_Hierarchy_Node_Id = Prior Hierarchy_Node_Id; */
106
107 Cursor op_desc Is
108 Select Description
109 From Pqh_De_Operations
110 Where OPERATION_NUMBER = p_Operation_Job_Number;
111
112 Cursor Job_desc(p_Job_Number In Varchar2) is
113 Select TATIGKEIT_DETAIL_ID, Description
114 From Pqh_de_Tatigkeit_details
115 Where TATIGKEIT_NUMBER = p_Job_Number;
116
117 Cursor Operations(p_Hierarchy_Node_Id In Number) is
118 Select Entity_Id, Node_Type, Hierarchy_Node_Id
119 from Per_Gen_Hierarchy_Nodes
120 Where Parent_Hierarchy_Node_id = p_Hierarchy_Node_Id;
121
122
123 Cursor Jobs(p_Hierarchy_Node_Id In Number) is
124 Select Entity_Id, Node_Type, Hierarchy_Node_Id
125 from Per_Gen_Hierarchy_Nodes
126 Where Parent_Hierarchy_Node_id = p_Hierarchy_Node_Id;
127
128 Begin
129 If p_Trn_TYpe = 'O' Then
130 Open op_desc;
131 Fetch op_desc into l_desc;
132 -- BUG FIX 2281356
133 -- if op_desc do not exist it means you havent selected operation so raise exception
134 If op_desc%NOTFOUND then
135 Close op_Desc;
136 hr_utility.set_message(8302, 'PQH_DE_INVALD_OP_SELECT');
137 hr_utility.raise_error;
138 end if;
139
140 Close op_Desc;
141 -- Operations API
142
143 PQH_DE_VLDOPR_API.Insert_Vldtn_Oprn
144 (p_effective_date => Trunc(Sysdate)
145 ,p_business_group_id => P_Business_Group_Id
146 ,p_WRKPLC_VLDTN_VER_ID => p_Vldtn_Ver_Op_Id
147 ,P_WRKPLC_OPERATION_ID => p_Operation_Job_Id
148 ,P_DESCRIPTION => l_Desc
149 ,P_UNIT_PERCENTAGE => NULL
150 ,P_WRKPLC_VLDTN_OP_ID => l_Wrkplc_Vldtn_Op_Id
151 ,p_object_version_number => l_Object_Version_Number);
152
153 For Oprrec in OPerations(p_Hierarchy_Node_Id)
154 Loop
155
156 If Oprrec.Node_Type = 'OPR_JOB_FTR' Then
157
158 PQH_DE_VLDJOBFTR_API.Insert_Vldtn_JObftr
159 (p_effective_date => Trunc(Sysdate)
160 ,p_business_group_id => p_Business_Group_Id
161 ,p_WRKPLC_VLDTN_OPR_JOB_ID => l_Wrkplc_Vldtn_Op_Id
162 ,P_JOB_FEATURE_CODE => Oprrec.Entity_Id
163 ,P_Wrkplc_Vldtn_Opr_job_Type => 'O'
164 ,P_WRKPLC_VLDTN_JObFTR_ID => l_WRKPLC_VLDTN_JObFTR_ID
165 ,p_object_version_number => l_Object_Version_Number);
166
167 Elsif Oprrec.Node_Type = 'OPR_JOB_DTLS' Then
168
169
170 Open Job_desc(Oprrec.Entity_Id);
171 Fetch Job_desc into l_Job_Id, l_desc;
172 Close Job_Desc;
173
174 PQH_DE_VLDJOB_API.Insert_Vldtn_JOb
175 (p_effective_date => Trunc(Sysdate)
176 ,p_business_group_id => p_Business_Group_Id
177 ,p_WRKPLC_VLDTN_OP_ID => l_Wrkplc_Vldtn_Op_Id
178 ,P_WRKPLC_JOB_ID => l_Job_Id
179 ,P_DESCRIPTION => l_desc
180 ,P_WRKPLC_VLDTN_JOb_ID => l_Wrkplc_Vldtn_Job_Id
181 ,p_object_version_number => l_Object_Version_Number);
182
183
184
185 For Jobrec in Jobs(Oprrec.Hierarchy_Node_id)
186 Loop
187 PQH_DE_VLDJOBFTR_API.Insert_Vldtn_JObftr
188 (p_effective_date => Trunc(Sysdate)
189 ,p_business_group_id => p_Business_Group_Id
190 ,p_WRKPLC_VLDTN_OPR_JOB_ID => l_Wrkplc_Vldtn_Job_Id
191 ,P_JOB_FEATURE_CODE => Jobrec.Entity_Id
192 ,P_Wrkplc_Vldtn_Opr_job_Type => 'J'
193 ,P_WRKPLC_VLDTN_JObFTR_ID => l_WRKPLC_VLDTN_JObFTR_ID
194 ,p_object_version_number => l_Object_Version_Number);
195 End Loop;
196
197 End If;
198 End Loop;
199 Else
200 Open Job_desc(p_Operation_Job_Number);
201 Fetch Job_desc into l_Job_Id, l_desc;
202 -- BUG FIX 2281356
203 -- if job_desc do not exist it means you havent selected job so raise exception
204 If Job_desc%NOTFOUND then
205 Close Job_Desc;
206 hr_utility.set_message(8302, 'PQH_DE_INVALD_JOB_SELECT');
207 hr_utility.raise_error;
208 end if;
209 Close Job_Desc;
210
211 PQH_DE_VLDJOB_API.Insert_Vldtn_JOb
212 (p_effective_date => Trunc(Sysdate)
213 ,p_business_group_id => p_Business_Group_Id
214 ,p_WRKPLC_VLDTN_OP_ID => p_Vldtn_Ver_Op_Id
215 ,P_WRKPLC_JOB_ID => p_Operation_Job_Id
216 ,P_DESCRIPTION => l_desc
217 ,P_WRKPLC_VLDTN_JOb_ID => l_Wrkplc_Vldtn_Job_Id
218 ,p_object_version_number => l_Object_Version_Number);
219
220 For Jobrec in Jobs(p_Hierarchy_node_id)
221 Loop
222 PQH_DE_VLDJOBFTR_API.Insert_Vldtn_JObftr
223 (p_effective_date => Trunc(Sysdate)
224 ,p_business_group_id => p_Business_Group_Id
225 ,p_WRKPLC_VLDTN_OPR_JOB_ID => l_Wrkplc_Vldtn_Job_Id
226 ,P_JOB_FEATURE_CODE => Jobrec.Entity_Id
227 ,P_Wrkplc_Vldtn_Opr_job_Type => 'J'
228 ,P_WRKPLC_VLDTN_JObFTR_ID => l_WRKPLC_VLDTN_JObFTR_ID
229 ,p_object_version_number => l_Object_Version_Number);
230 End Loop;
231
232 End If;
233 End;
234
235 Procedure Populate_Operation_PLan
236 (p_WRKPLC_VLDTN_VER_ID IN Number
237 ,p_Business_group_Id IN Number
238 ,p_Effective_Date IN Date) is
239
240 l_Hierarchy_Node_Id Per_gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
241 l_JHierarchy_Node_Id Per_gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
242 l_Hierarchy_Version_Id Per_Gen_Hierarchy_Nodes.Hierarchy_Version_Id%TYPE;
243 l_Parent_Hierarchy_Node_Id Per_Gen_Hierarchy_nodes.Parent_Hierarchy_Node_Id%TYPE;
244 l_Node_type Per_Gen_Hierarchy_nodes.Node_Type%TYPE;
245 l_oprHierarchy_Node_Id Per_gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
246 l_oprHierarchy_Version_Id Per_Gen_Hierarchy_Nodes.Hierarchy_Version_Id%TYPE;
247 l_oprParent_Hierarchy_Node_Id Per_Gen_Hierarchy_nodes.Parent_Hierarchy_Node_Id%TYPE;
248 l_oprNode_type Per_Gen_Hierarchy_nodes.Node_Type%TYPE;
249 l_seq Per_Gen_Hierarchy_nodes.Seq%TYPE;
250
251 Cursor Operations is
252 Select WRKPLC_VLDTN_OP_ID, OPERATION_NUMBER, WRKPLC_OPERATION_ID
253 From Pqh_de_operations a, Pqh_De_Wrkplc_Vldtn_Ops b
254 Where WRKPLC_VLDTN_VER_ID = p_WRKPLC_VLDTN_VER_ID
255 and a.Operation_Id = WRKPLC_OPERATION_ID;
256
257 Cursor Jobs(p_WRKPLC_VLDTN_OP_ID in Number) is
258 Select WRKPLC_VLDTN_JOB_ID, b.DESCRIPTION,
259 WRKPLC_JOB_ID, TATIGKEIT_NUMBER
260 From PQH_DE_TATIGKEIT_DETAILS a, Pqh_De_Wrkplc_Vldtn_JObs b
261 Where WRKPLC_VLDTN_OP_ID = p_WRKPLC_VLDTN_OP_ID
262 and a.TATIGKEIT_DETAIL_ID = WRKPLC_JOB_ID;
263
264 Cursor JobFtrs(p_WRKPLC_VLDTN_OPR_JOB_ID In Number, Type In Varchar2) Is
265 Select WRKPLC_VLDTN_JOBFTR_ID, WRKPLC_VLDTN_OPR_JOB_ID
266 JOB_FEATURE_CODE, WRKPLC_VLDTN_OPR_JOB_TYPE
267 From Pqh_De_Wrkplc_Vldtn_Jobftrs
268 Where WRKPLC_VLDTN_OPR_JOB_ID = p_WRKPLC_VLDTN_OPR_JOB_ID
269 and WRKPLC_VLDTN_OPR_JOB_TYPE = Type;
270
271 Cursor Hierarchy_Data(p_Entity_Id In VARCHAR2, P_Node_Type in Varchar2) is
272 Select Hierarchy_Node_Id, a.Hierarchy_Version_Id, Parent_Hierarchy_Node_Id, Node_type
273 From Per_Gen_Hierarchy_Nodes a, Per_gen_hierarchy_Versions b
274 Where Node_Type = P_Node_Type
275 and Entity_Id = p_Entity_Id
276 and b.Hierarchy_Version_Id = a.Hierarchy_Version_Id
277 and trunc(P_effective_Date) between date_From and nvl(Date_To,trunc(P_effective_Date));
278
279 cursor Seq(p_OprHierarchy_Node_Id In Number, p_Node_Type in Varchar2) Is
280 Select Nvl(Max(Seq),0) + 1
281 From Per_Gen_Hierarchy_Nodes
282 Where Node_type = p_Node_type
283 and Parent_Hierarchy_Node_id = p_OprHierarchy_Node_Id;
284
285 Begin
286 For Oprrec in OPerations
287 Loop
288 l_oprHierarchy_Node_Id := NULL;
289 l_OprHierarchy_Version_Id := NULL;
290 l_OprParent_Hierarchy_Node_Id := NULL;
291 l_OprNode_type := NULL;
292 if( Hierarchy_Data%ISOPEN) THEN
293 close Hierarchy_Data;
294 END IF;
295 Open Hierarchy_Data(Oprrec.OPERATION_NUMBER, 'OPR_OPTS');
296 Fetch Hierarchy_Data into l_OprHierarchy_Node_Id, l_OprHierarchy_Version_Id, l_OprParent_Hierarchy_Node_Id, l_OprNode_type;
297
298 If Hierarchy_Data%FOUND Then
299 close Hierarchy_Data;
300 For Jobrec in Jobs(Oprrec.WRKPLC_VLDTN_OP_ID)
301 Loop
302 l_Hierarchy_Node_Id := NULL;
303 l_Hierarchy_Version_Id := NULL;
304 l_Parent_Hierarchy_Node_Id := NULL;
305 l_Node_type := NULL;
306
310 Close Hierarchy_Data;
307 Open Hierarchy_Data(Jobrec.TATIGKEIT_NUMBER, 'OPR_JOB_DTLS');
308 Fetch Hierarchy_Data into l_Hierarchy_Node_Id, l_Hierarchy_Version_Id, l_Parent_Hierarchy_Node_Id, l_Node_type;
309 If Hierarchy_Data%NotFound Then
311 Select Per_gen_Hierarchy_Nodes_s.Nextval into l_JHierarchy_Node_Id from Dual;
312 l_Seq := Null;
313 Open Seq(l_OprParent_Hierarchy_Node_Id,'OPR_JOB_DTLS');
314 Fetch Seq into l_Seq;
315 Close Seq;
316 Insert into
317 Per_Gen_Hierarchy_Nodes
318 (HIERARCHY_NODE_ID, BUSINESS_GROUP_ID, ENTITY_ID, HIERARCHY_VERSION_ID,
319 NODE_TYPE, PARENT_HIERARCHY_NODE_ID, SEQ, Object_Version_Number)
320 Values
321 (l_JHierarchy_Node_Id, p_BUSINESS_GROUP_ID, Jobrec.TATIGKEIT_NUMBER, l_OprHierarchy_Version_Id,
322 'OPR_JOB_DTLS', l_OprParent_Hierarchy_Node_Id,l_seq, 1);
323
324 For jobftrrec in JobFtrs(Jobrec.WRKPLC_VLDTN_JOB_ID,'J')
325 Loop
326 l_Hierarchy_Node_Id := NULL;
327 l_Hierarchy_Version_Id := NULL;
328 l_Parent_Hierarchy_Node_Id := NULL;
329 l_Node_type := NULL;
330
331 Open Hierarchy_Data(Jobftrrec.JOB_FEATURE_CODE, 'OPR_JOB_FTR');
332 Fetch Hierarchy_Data into l_Hierarchy_Node_Id, l_Hierarchy_Version_Id, l_Parent_Hierarchy_Node_Id, l_Node_type;
333 If Hierarchy_Data%NotFound Then
334 Close HIerarchy_data;
335 Select Per_gen_Hierarchy_Nodes_s.Nextval into l_Hierarchy_Node_Id from Dual;
336 Open Seq(l_OprParent_Hierarchy_Node_Id,'OPR_JOB_DTLS');
337 Fetch Seq into l_Seq;
338 Close Seq;
339 Insert into
340 Per_Gen_Hierarchy_Nodes
341 (HIERARCHY_NODE_ID, BUSINESS_GROUP_ID, ENTITY_ID, HIERARCHY_VERSION_ID,
342 NODE_TYPE, PARENT_HIERARCHY_NODE_ID, SEQ, Object_Version_Number)
343 Values
344 (l_Hierarchy_Node_Id, p_BUSINESS_GROUP_ID, Jobftrrec.JOB_FEATURE_CODE, l_OprHierarchy_Version_Id,
345 'OPR_JOB_FTR', l_JHierarchy_Node_Id, l_seq, 1);
346 Else
347 Close Hierarchy_Data;
348 End If;
349 End Loop;
350 Else
351 Close Hierarchy_Data;
352 End If;
353 End Loop;
354 For jobftrrec in JobFtrs(Oprrec.Wrkplc_Vldtn_op_Id,'O')
355 Loop
356 l_Hierarchy_Node_Id := NULL;
357 l_Hierarchy_Version_Id := NULL;
358 l_Parent_Hierarchy_Node_Id := NULL;
359 l_Node_type := NULL;
360
361 Open Hierarchy_Data(Jobftrrec.JOB_FEATURE_CODE, 'OPR_JOB_FTR');
362 Fetch Hierarchy_Data into l_Hierarchy_Node_Id, l_Hierarchy_Version_Id, l_Parent_Hierarchy_Node_Id, l_Node_type;
363 If Hierarchy_Data%NotFound Then
364 Close HIerarchy_data;
365 Select Per_gen_Hierarchy_Nodes_s.Nextval into l_Hierarchy_Node_Id from Dual;
366 Open Seq(l_OprParent_Hierarchy_Node_Id,'OPR_JOB_DTLS');
367 Fetch Seq into l_Seq;
368 Close Seq;
369 Insert Into
370 Per_Gen_Hierarchy_Nodes
371 (HIERARCHY_NODE_ID, BUSINESS_GROUP_ID, ENTITY_ID, HIERARCHY_VERSION_ID,
372 NODE_TYPE, PARENT_HIERARCHY_NODE_ID, SEQ, Object_Version_Number)
373 Values
374 (l_Hierarchy_Node_Id, p_BUSINESS_GROUP_ID, Jobftrrec.JOB_FEATURE_CODE, l_OprHierarchy_Version_Id,
375 'OPR_JOB_FTR', l_OprParent_Hierarchy_Node_Id, l_seq, 1);
376 Else
377 Close Hierarchy_Data;
378 End If;
379 End Loop;
380 End If;
381 End Loop;
382 End;
383 End;