[Home] [Help]
PACKAGE BODY: APPS.PQH_DE_VLD_NEWVER_DEL
Source
1 Package Body PQH_DE_VLD_NEWVER_DEL As
2 /* $Header: pqhverdl.pkb 115.2 2002/12/03 00:07:35 rpasapul noship $ */
3 Procedure PQH_DE_CREATE_VERSION
4 (p_WRKPLC_VLDTN_VER_ID In Number,
5 P_Business_Group_Id In Number) is
6
7 L_Wrkplc_Vldtn_Ver_Id Pqh_De_Wrkplc_Vldtn_Vers.Wrkplc_Vldtn_Ver_Id%TYPE;
8 l_object_Version_Number Pqh_De_Wrkplc_Vldtn_Vers.object_Version_Number%TYPE;
9 l_Opr_Object_Version_Number Pqh_De_Wrkplc_Vldtn_Ops.object_Version_Number%TYPE;
10 l_Wrkplc_Vldtn_Op_Id Pqh_De_Wrkplc_Vldtn_Ops.Wrkplc_Vldtn_Op_Id%TYPE;
11 l_Job_Object_Version_Number Pqh_De_Wrkplc_Vldtn_Jobs.object_Version_Number%TYPE;
12 l_Wrkplc_Vldtn_Job_Id Pqh_De_Wrkplc_Vldtn_Jobs.Wrkplc_Vldtn_JOb_Id%TYPE;
13 l_Jobftr_Object_Version_Number Pqh_De_Wrkplc_Vldtn_JobFtrs.object_Version_Number%TYPE;
14 l_Wrkplc_Vldtn_JobFTr_Id Pqh_De_Wrkplc_Vldtn_JobFTRS.Wrkplc_Vldtn_JObFTr_Id%TYPE;
15 l_WRKPLC_VLDTN_LVLNUM_Id Pqh_de_Wrkplc_Vldtn_Lvlnums.WRKPLC_VLDTN_LVLNUM_Id%TYPE;
16 l_Lvlnum_Object_Version_Number Pqh_De_Wrkplc_Vldtn_Lvlnums.object_Version_Number%TYPE;
17
18
19 Cursor wrkplc_vldtn_defn is
20 Select b.Wrkplc_Vldtn_Id,VALIDATION_NAME, EMPLOYMENT_TYPE, REMUNERATION_REGULATION, WRKPLC_VLDTN_VER_ID, VERSION_NUMBER,
21 REMUNERATION_JOB_DESCRIPTION, JOB_GROUP_ID, REMUNERATION_JOB_ID, DERIVED_GRADE_ID, DERIVED_CASE_GROUP_ID,
22 DERIVED_SUBCASGRP_ID, USER_ENTERABLE_GRADE_ID, USER_ENTERABLE_CASE_GROUP_ID, USER_ENTERABLE_SUBCASGRP_ID,
23 Tariff_contract_code, Tariff_Group_Code
24 From Pqh_De_Wrkplc_Vldtns b, Pqh_De_Wrkplc_Vldtn_Vers a
25 Where a.WRKPLC_VLDTN_VER_ID = p_WRKPLC_VLDTN_VER_ID and
26 a.Business_Group_Id = P_Business_Group_Id and
27 b.Wrkplc_Vldtn_Id = a.Wrkplc_Vldtn_Id and
28 b.Business_Group_Id = a.Business_Group_Id;
29
30 Cursor Wrkplc_Vldtn_Oprs (p_WRKPLC_VLDTN_VER_ID In Number) is
31 Select WRKPLC_VLDTN_OP_ID, WRKPLC_OPERATION_ID, DESCRIPTION, UNIT_PERCENTAGE
32 From Pqh_De_Wrkplc_Vldtn_Ops
33 Where Wrkplc_Vldtn_Ver_Id = P_Wrkplc_Vldtn_Ver_Id and
34 Business_Group_id = P_Business_Group_Id;
35
36 Cursor Wrkplc_Vldtn_Jobs (p_Wrkplc_Vldtn_Op_Id in Number) is
37 Select WRKPLC_VLDTN_JOB_ID, WRKPLC_JOB_ID, DESCRIPTION
38 From Pqh_De_Wrkplc_Vldtn_Jobs
39 Where Wrkplc_Vldtn_Op_Id = P_Wrkplc_Vldtn_Op_Id and
40 Business_Group_Id = p_Business_Group_Id;
41
42 Cursor Wrkplc_Vldtn_JobFtrs (P_Wrkplc_Vldtn_OpJob_Id In Number,P_Wrkplc_Vldtn_OprJob_Type In Varchar2) is
43 Select WRKPLC_VLDTN_JOBFTR_ID, JOB_FEATURE_CODE
44 From PQH_DE_WRKPLC_VLDTN_JOBFTRS
45 Where WRKPLC_VLDTN_OPR_JOB_TYPE = P_Wrkplc_Vldtn_OprJob_Type and
46 WRKPLC_VLDTN_OPR_JOB_ID = P_Wrkplc_Vldtn_OpJob_Id and
47 Business_group_id = p_Business_group_id;
48
49 Cursor Wrkplc_Vldtn_Lvlnum (p_WRKPLC_VLDTN_VER_ID In Number) is
50 Select WRKPLC_VLDTN_LVLNUM_ID, LEVEL_NUMBER_ID, LEVEL_CODE_ID
51 From Pqh_De_Wrkplc_Vldtn_Lvlnums
52 where Wrkplc_Vldtn_Ver_id = p_WRKPLC_VLDTN_VER_ID and
53 Business_group_Id = p_Business_Group_Id;
54
55 Begin
56 For Defrec in wrkplc_vldtn_defn
57 Loop
58 -- Call Vldtn Definition Api
59 -- Call Vldtn Version Api
60 PQH_DE_VLDVER_API.Insert_Vldtn_Vern
61 (p_effective_date => Trunc(Sysdate)
62 ,p_business_group_id => p_Business_group_id
63 ,p_WRKPLC_VLDTN_ID => Defrec.Wrkplc_Vldtn_Id
64 ,P_VERSION_NUMBER => Defrec.version_number
65 ,P_REMUNERATION_JOB_DESCRIPTION => Defrec.REMUNERATION_JOB_DESCRIPTION
66 ,P_TARIFF_CONTRACT_CODE => Defrec.Tariff_Contract_Code
67 ,P_TARIFF_GROUP_CODE => Defrec.Tariff_Group_Code
68 ,P_JOB_GROUP_ID => Defrec.Job_Group_Id
69 ,P_REMUNERATION_JOB_ID => Defrec.Remuneration_Job_Id
70 ,P_DERIVED_GRADE_ID => Defrec.Derived_Grade_Id
71 ,P_DERIVED_CASE_GROUP_ID => Defrec.Derived_Case_Group_Id
72 ,P_DERIVED_SUBCASGRP_ID => Defrec.Derived_Subcasgrp_Id
73 ,P_USER_ENTERABLE_GRADE_ID => Defrec.USER_ENTERABLE_GRADE_ID
74 ,P_USER_ENTERABLE_CASE_GROUP_ID => Defrec.USER_ENTERABLE_CASE_GROUP_ID
75 ,P_USER_ENTERABLE_SUBCASGRP_ID => Defrec.USER_ENTERABLE_SUBCASGRP_ID
76 ,P_FREEZE => 'U'
77 ,p_WRKPLC_VLDTN_VER_ID => l_Wrkplc_Vldtn_ver_Id
78 ,p_object_version_number => l_Object_Version_Number);
79
80 -- Employment Type White Collar Worker - Common Part
81
82 If Defrec.Employment_type = 'WC' and Defrec.REMUNERATION_REGULATION = 'CP' Then
83 For Oprrec in Wrkplc_Vldtn_Oprs(Defrec.WRKPLC_VLDTN_VER_ID)
84 Loop
85 --- Call Operation Api
86 PQH_DE_VLDOPR_API.Insert_Vldtn_Oprn
87 (p_effective_date => Trunc(Sysdate)
88 ,p_business_group_id => P_Business_Group_Id
89 ,p_WRKPLC_VLDTN_VER_ID => l_Wrkplc_Vldtn_Ver_Id
90 ,P_WRKPLC_OPERATION_ID => Oprrec.Wrkplc_Operation_Id
91 ,P_DESCRIPTION => Oprrec.Description
92 ,P_UNIT_PERCENTAGE => Oprrec.Unit_Percentage
93 ,P_WRKPLC_VLDTN_OP_ID => l_Wrkplc_Vldtn_Op_Id
94 ,p_object_version_number => l_Opr_Object_Version_Number);
95
96 For JobFtrRec in Wrkplc_Vldtn_JobFtrs(Oprrec.Wrkplc_Vldtn_Op_Id,'O')
97 Loop
98 -- Call Job Feature Api ---
99 PQH_DE_VLDJOBFTR_API.Insert_Vldtn_JObftr
100 (p_effective_date => Trunc(Sysdate)
101 ,p_business_group_id => p_Business_Group_Id
102 ,p_WRKPLC_VLDTN_OPR_JOB_ID => l_Wrkplc_Vldtn_Op_Id
103 ,P_JOB_FEATURE_CODE => JobFtrRec.JOB_FEATURE_CODE
104 ,P_Wrkplc_Vldtn_Opr_job_Type => 'O'
105 ,P_WRKPLC_VLDTN_JObFTR_ID => l_WRKPLC_VLDTN_JObFTR_ID
106 ,p_object_version_number => l_Jobftr_Object_Version_Number);
107 End Loop;
108 For Jobrec in Wrkplc_Vldtn_Jobs(Oprrec.Wrkplc_Vldtn_Op_Id)
109 Loop
110 PQH_DE_VLDJOB_API.Insert_Vldtn_JOb
111 (p_effective_date => Trunc(Sysdate)
112 ,p_business_group_id => p_Business_Group_Id
113 ,p_WRKPLC_VLDTN_OP_ID => l_Wrkplc_Vldtn_Op_Id
114 ,P_WRKPLC_JOB_ID => JobRec.Wrkplc_Job_Id
115 ,P_DESCRIPTION => Jobrec.Description
116 ,P_WRKPLC_VLDTN_JOb_ID => l_Wrkplc_Vldtn_Job_Id
117 ,p_object_version_number => l_Job_Object_Version_Number);
118
119 For JobFtrRec in Wrkplc_Vldtn_JobFtrs(jobRec.WRKPLC_VLDTN_JOB_ID,'J')
120 Loop
121 -- Call Job Feature Api ---
122 PQH_DE_VLDJOBFTR_API.Insert_Vldtn_JObftr
123 (p_effective_date => Trunc(Sysdate)
124 ,p_business_group_id => p_Business_Group_Id
125 ,p_WRKPLC_VLDTN_OPR_JOB_ID => l_Wrkplc_Vldtn_Op_Id
126 ,P_JOB_FEATURE_CODE => JobFtrRec.JOB_FEATURE_CODE
127 ,P_Wrkplc_Vldtn_Opr_job_Type => 'J'
128 ,P_WRKPLC_VLDTN_JObFTR_ID => l_WRKPLC_VLDTN_JObFTR_ID
129 ,p_object_version_number => l_Jobftr_Object_Version_Number);
130 End Loop;
131 End Loop;
132 End Loop;
133 Elsif Defrec.Employment_type = 'CS' Then
134 For LvlRec in Wrkplc_Vldtn_Lvlnum(Defrec.WRKPLC_VLDTN_VER_ID)
135 Loop
136 -- Call Level Number API
137 PQH_DE_VLDLVL_API.Insert_Vldtn_Lvl
138 (p_effective_date => Trunc(Sysdate)
139 ,p_business_group_id => p_Business_Group_Id
140 ,P_WRKPLC_VLDTN_VER_ID => l_Wrkplc_Vldtn_Ver_Id
141 ,P_LEVEL_NUMBER_ID => lvlrec.LEVEL_NUMBER_ID
142 ,P_LEVEL_CODE_ID => lvlrec.Level_Code_Id
143 ,P_WRKPLC_VLDTN_LVLNUM_Id => l_WRKPLC_VLDTN_LVLNUM_Id
144 ,p_object_version_number => l_lvlnum_Object_Version_Number);
145 End Loop;
146 End If;
147 End Loop;
148 End;
149
150 Procedure PQH_DE_DELETE_VERSION
151 (p_WRKPLC_VLDTN_VER_ID In Number,
152 P_Business_Group_Id In Number) is
153
154 Cursor wrkplc_vldtn_defn is
155 Select b.Wrkplc_Vldtn_Id,VALIDATION_NAME, EMPLOYMENT_TYPE, REMUNERATION_REGULATION, WRKPLC_VLDTN_VER_ID, VERSION_NUMBER,
156 REMUNERATION_JOB_DESCRIPTION, JOB_GROUP_ID, REMUNERATION_JOB_ID, DERIVED_GRADE_ID, DERIVED_CASE_GROUP_ID,
157 DERIVED_SUBCASGRP_ID, USER_ENTERABLE_GRADE_ID, USER_ENTERABLE_CASE_GROUP_ID, USER_ENTERABLE_SUBCASGRP_ID, Freeze,
158 a.Object_Version_Number Vers_ovn_Num, b.Object_Version_Number Def_ovn_Num
159 From Pqh_De_Wrkplc_Vldtns b, Pqh_De_Wrkplc_Vldtn_Vers a
160 Where a.WRKPLC_VLDTN_VER_ID = p_WRKPLC_VLDTN_VER_ID and
161 a.Business_Group_Id = P_Business_Group_Id and
162 b.Wrkplc_Vldtn_Id = a.Wrkplc_Vldtn_Id and
163 b.Business_Group_Id = a.Business_Group_Id;
164
165 Cursor Wrkplc_Vldtn_Oprs (p_WRKPLC_VLDTN_VER_ID In Number) is
166 Select WRKPLC_VLDTN_OP_ID, WRKPLC_OPERATION_ID, DESCRIPTION, UNIT_PERCENTAGE, Object_Version_Number
167 From Pqh_De_Wrkplc_Vldtn_Ops
168 Where Wrkplc_Vldtn_Ver_Id = P_Wrkplc_Vldtn_Ver_Id and
169 Business_Group_id = P_Business_Group_Id;
170
171 Cursor Wrkplc_Vldtn_Jobs (p_Wrkplc_Vldtn_Op_Id in Number) is
172 Select WRKPLC_VLDTN_JOB_ID, WRKPLC_JOB_ID, DESCRIPTION, Object_version_Number
173 From Pqh_De_Wrkplc_Vldtn_Jobs
174 Where Wrkplc_Vldtn_Op_Id = P_Wrkplc_Vldtn_Op_Id and
175 Business_Group_Id = p_Business_Group_Id;
176
177 Cursor Wrkplc_Vldtn_JobFtrs (P_Wrkplc_Vldtn_OpJob_Id In Number,P_Wrkplc_Vldtn_OprJob_Type In Varchar2) is
178 Select WRKPLC_VLDTN_JOBFTR_ID, JOB_FEATURE_CODE, Object_Version_Number
179 From PQH_DE_WRKPLC_VLDTN_JOBFTRS
180 Where WRKPLC_VLDTN_OPR_JOB_TYPE = P_Wrkplc_Vldtn_OprJob_Type and
181 WRKPLC_VLDTN_OPR_JOB_ID = P_Wrkplc_Vldtn_OpJob_Id and
182 Business_group_id = p_Business_group_id;
183
184 Cursor Wrkplc_Vldtn_Lvlnum (p_WRKPLC_VLDTN_VER_ID In Number) is
185 Select WRKPLC_VLDTN_LVLNUM_ID, LEVEL_NUMBER_ID, LEVEL_CODE_ID, Object_Version_Number
186 From Pqh_De_Wrkplc_Vldtn_Lvlnums
187 where Wrkplc_Vldtn_Ver_id = p_WRKPLC_VLDTN_VER_ID and
188 Business_group_Id = p_Business_Group_Id;
189 l_vldcnt Number;
190
191 Begin
192 For Defrec in wrkplc_vldtn_defn
193 Loop
194
195 If Defrec.Freeze = 'F' then
196 Hr_utility.Set_message(8302,'DE_PQH_VERDEL');
197 Hr_utility.raise_Error;
198
199 Else
200 -- Employment Type White Collar Worker - Common Part
201 If Defrec.Employment_type = 'WC' and Defrec.REMUNERATION_REGULATION = 'CP' Then
202
203 For Oprrec in Wrkplc_Vldtn_Oprs(Defrec.WRKPLC_VLDTN_VER_ID)
204 Loop
205
206 For JobFtrRec in Wrkplc_Vldtn_JobFtrs(Oprrec.Wrkplc_Vldtn_Op_Id,'O')
207 Loop
208 -- Call Job Feature Api ---
209 PQH_DE_VLDJOBFTR_API.Delete_Vldtn_JobFtr
210 (p_WRKPLC_VLDTN_JobFtr_Id => Jobftrrec.WRKPLC_VLDTN_JOBFTR_ID
211 ,p_object_version_number => Jobftrrec.Object_Version_Number);
212 End Loop;
213
214 For Jobrec in Wrkplc_Vldtn_Jobs(Oprrec.Wrkplc_Vldtn_Op_Id)
215 Loop
216
217 For JobFtrRec in Wrkplc_Vldtn_JobFtrs(jobRec.WRKPLC_VLDTN_JOB_ID,'J')
218 Loop
219 -- Call Job Feature Api ---
220 PQH_DE_VLDJOBFTR_API.Delete_Vldtn_JobFtr
221 (p_WRKPLC_VLDTN_JobFtr_Id => Jobftrrec.WRKPLC_VLDTN_JOBFTR_ID
222 ,p_object_version_number => Jobftrrec.Object_Version_Number);
223
224 End Loop;
225
226 PQH_DE_VLDJOB_API.Delete_Vldtn_Job
227 (p_WRKPLC_VLDTN_Job_Id => Jobrec.WRKPLC_VLDTN_Job_Id
228 ,p_object_version_number => Jobrec.Object_Version_Number);
229
230 End Loop;
231
232 Pqh_de_vldopr_Api.delete_Vldtn_Oprn
233 (p_WRKPLC_VLDTN_OP_ID => oprrec.Wrkplc_vldtn_op_id
234 ,p_object_version_number => Oprrec.Object_version_number);
235
236 End Loop;
237
238 Elsif Defrec.Employment_type = 'CS' Then
239
240 For LvlRec in Wrkplc_Vldtn_Lvlnum(Defrec.WRKPLC_VLDTN_VER_ID)
241 Loop
242 PQH_DE_VLDLVL_API.Delete_Vldtn_Lvl
243 (P_WRKPLC_VLDTN_Lvlnum_Id => Lvlrec.WRKPLC_VLDTN_Lvlnum_Id
244 ,p_object_version_number => Lvlrec.Object_Version_Number);
245 End Loop;
246
247 End If; --- Employment Type
248
249 End If; -- Freeze
250
251 PQH_DE_VLDVER_API.Delete_Vldtn_Vern
252 (p_WRKPLC_VLDTN_VER_ID => Defrec.WRKPLC_VLDTN_VER_ID
253 ,p_object_version_number => Defrec.Vers_ovn_Num);
254
255 Select Count(*) into l_vldcnt
256 from Pqh_De_Wrkplc_Vldtn_vers
257 Where WRKPLC_VLDTN_ID = Defrec.WRKPLC_VLDTN_ID;
258
259 If Nvl(l_vldcnt,0) = 0 Then
260 PQH_DE_VLDDEF_API.Delete_Vldtn_Defn
261 (p_WRKPLC_VLDTN_ID => Defrec.WRKPLC_VLDTN_ID
262 ,p_object_version_number => Defrec.Def_ovn_Num);
263 End If;
264
265 End Loop;
266 End;
267
268 Procedure PQH_DE_DELETE_OPERATION
269 (p_WRKPLC_VLDTN_OP_ID In Number,
270 P_Business_Group_Id In Number) is
271
272 Cursor Wrkplc_Vldtn_Oprs is
273 Select WRKPLC_VLDTN_OP_ID, WRKPLC_OPERATION_ID, DESCRIPTION, UNIT_PERCENTAGE, Object_Version_Number
274 From Pqh_De_Wrkplc_Vldtn_Ops
275 Where WRKPLC_VLDTN_OP_ID = P_WRKPLC_VLDTN_OP_ID and
276 Business_Group_id = P_Business_Group_Id;
277
278 Cursor Wrkplc_Vldtn_Jobs (p_Wrkplc_Vldtn_Op_Id in Number) is
279 Select WRKPLC_VLDTN_JOB_ID, WRKPLC_JOB_ID, DESCRIPTION, Object_version_Number
280 From Pqh_De_Wrkplc_Vldtn_Jobs
281 Where Wrkplc_Vldtn_Op_Id = P_Wrkplc_Vldtn_Op_Id and
282 Business_Group_Id = p_Business_Group_Id;
283
284 Cursor Wrkplc_Vldtn_JobFtrs (P_Wrkplc_Vldtn_OpJob_Id In Number,P_Wrkplc_Vldtn_OprJob_Type In Varchar2) is
285 Select WRKPLC_VLDTN_JOBFTR_ID, JOB_FEATURE_CODE, Object_Version_Number
286 From PQH_DE_WRKPLC_VLDTN_JOBFTRS
287 Where WRKPLC_VLDTN_OPR_JOB_TYPE = P_Wrkplc_Vldtn_OprJob_Type and
288 WRKPLC_VLDTN_OPR_JOB_ID = P_Wrkplc_Vldtn_OpJob_Id and
289 Business_group_id = p_Business_group_id;
290
291 l_vldcnt Number;
292
293 Begin
294 For Oprrec in Wrkplc_Vldtn_Oprs
295 Loop
296 For JobFtrRec in Wrkplc_Vldtn_JobFtrs(Oprrec.Wrkplc_Vldtn_Op_Id,'O')
297 Loop
298 -- Call Job Feature Api ---
299 PQH_DE_VLDJOBFTR_API.Delete_Vldtn_JobFtr
300 (p_WRKPLC_VLDTN_JobFtr_Id => Jobftrrec.WRKPLC_VLDTN_JOBFTR_ID
301 ,p_object_version_number => Jobftrrec.Object_Version_Number);
302 End Loop;
303
304 For Jobrec in Wrkplc_Vldtn_Jobs(Oprrec.Wrkplc_Vldtn_Op_Id)
305 Loop
306 For JobFtrRec in Wrkplc_Vldtn_JobFtrs(jobRec.WRKPLC_VLDTN_JOB_ID,'J')
307 Loop
308 -- Call Job Feature Api ---
309 PQH_DE_VLDJOBFTR_API.Delete_Vldtn_JobFtr
310 (p_WRKPLC_VLDTN_JobFtr_Id => Jobftrrec.WRKPLC_VLDTN_JOBFTR_ID
311 ,p_object_version_number => Jobftrrec.Object_Version_Number);
312 End Loop;
313 PQH_DE_VLDJOB_API.Delete_Vldtn_Job
314 (p_WRKPLC_VLDTN_Job_Id => Jobrec.WRKPLC_VLDTN_Job_Id
315 ,p_object_version_number => Jobrec.Object_Version_Number);
316 End Loop;
317
318 Pqh_de_vldopr_Api.delete_Vldtn_Oprn
319 (p_WRKPLC_VLDTN_OP_ID => oprrec.Wrkplc_vldtn_op_id
320 ,p_object_version_number => Oprrec.Object_version_number);
321
322 End Loop;
323
324 End;
325
326 Procedure PQH_DE_DELETE_JOB
327 (p_WRKPLC_VLDTN_JOB_ID In Number,
328 P_Business_Group_Id In Number) is
329
330 Cursor Wrkplc_Vldtn_Jobs is
331 Select WRKPLC_VLDTN_JOB_ID, WRKPLC_JOB_ID, DESCRIPTION, Object_version_Number
332 From Pqh_De_Wrkplc_Vldtn_Jobs
333 Where Wrkplc_Vldtn_Job_Id = p_WRKPLC_VLDTN_JOB_ID and
334 Business_Group_Id = p_Business_Group_Id;
335
336 Cursor Wrkplc_Vldtn_JobFtrs (P_Wrkplc_Vldtn_OpJob_Id In Number,P_Wrkplc_Vldtn_OprJob_Type In Varchar2) is
337 Select WRKPLC_VLDTN_JOBFTR_ID, JOB_FEATURE_CODE, Object_Version_Number
338 From PQH_DE_WRKPLC_VLDTN_JOBFTRS
339 Where WRKPLC_VLDTN_OPR_JOB_TYPE = P_Wrkplc_Vldtn_OprJob_Type and
340 WRKPLC_VLDTN_OPR_JOB_ID = P_Wrkplc_Vldtn_OpJob_Id and
341 Business_group_id = p_Business_group_id;
342
343 l_vldcnt Number;
344
345 Begin
346 For Jobrec in Wrkplc_Vldtn_Jobs
347 Loop
348 For JobFtrRec in Wrkplc_Vldtn_JobFtrs(jobRec.WRKPLC_VLDTN_JOB_ID,'J')
349 Loop
350 -- Call Job Feature Api ---
351 PQH_DE_VLDJOBFTR_API.Delete_Vldtn_JobFtr
352 (p_WRKPLC_VLDTN_JobFtr_Id => Jobftrrec.WRKPLC_VLDTN_JOBFTR_ID
353 ,p_object_version_number => Jobftrrec.Object_Version_Number);
354 End Loop;
355 PQH_DE_VLDJOB_API.Delete_Vldtn_Job
356 (p_WRKPLC_VLDTN_Job_Id => Jobrec.WRKPLC_VLDTN_Job_Id
357 ,p_object_version_number => Jobrec.Object_Version_Number);
358 End Loop;
359 End;
360
361 Procedure PQH_DE_IN_UP_CALL
362 (
363 P_EFFECTIVE_DATE IN DATE
364 ,P_BUSINESS_GROUP_ID IN NUMBER
365 ,P_WRKPLC_VLDTN_VER_ID IN NUMBER
366 ,P_LEVEL_NUMBER_ID IN NUMBER
367 ,P_LEVEL_CODE_ID IN NUMBER
368 ,P_WRKPLC_VLDTN_LVLNUM_ID IN OUT NOCOPY NUMBER
369 ,P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER
370 ,P_RETURN_STATUS OUT NOCOPY VARCHAR2
371 ) is
372 Cnt Number;
373 -- Variables for IN/OUT parameters
374 l_wrkplc_vldtn_lvlnum_id number := P_WRKPLC_VLDTN_LVLNUM_ID;
375 l_object_version_number number := p_object_version_number;
376
377 Cursor Chk_Ins_Upd(p_Wrkplc_Val_Ver_Id IN Number, p_Lvl_Code_Id IN Number, p_Lvl_Number_Id IN Number)
378 is
379 Select count(*) from
380 pqh_de_wrkplc_vldtn_lvlnums
381 where WRKPLC_VLDTN_VER_ID = p_Wrkplc_Val_Ver_Id
382 and LEVEL_NUMBER_ID = p_Lvl_Number_Id
383 and LEVEL_CODE_ID = p_Lvl_Code_Id;
384 Begin
385 Open Chk_Ins_Upd(P_WRKPLC_VLDTN_VER_ID, P_LEVEL_CODE_ID, P_LEVEL_NUMBER_ID);
386 Fetch Chk_Ins_Upd into Cnt;
387 Close Chk_Ins_Upd;
388 if(Cnt>0)
389 then
390 -- Calling the update Proc.
391
392 pqh_de_vldlvl_swi.UPDATE_VLDTN_LVL
393 (
394 p_effective_date => P_EFFECTIVE_DATE
395 ,p_business_group_id => P_BUSINESS_GROUP_ID
396 ,p_wrkplc_vldtn_ver_id => P_WRKPLC_VLDTN_VER_ID
397 ,p_level_number_id => P_LEVEL_NUMBER_ID
398 ,p_level_code_id => P_LEVEL_CODE_ID
399 ,p_wrkplc_vldtn_lvlnum_id => P_WRKPLC_VLDTN_LVLNUM_ID
400 ,p_object_version_number => P_OBJECT_VERSION_NUMBER
401 ,p_return_status => P_RETURN_STATUS
402 );
403
404
405
406 else
407
408 pqh_de_vldlvl_swi.INSERT_VLDTN_LVL
409 (
410 p_effective_date => P_EFFECTIVE_DATE
411 ,p_business_group_id => P_BUSINESS_GROUP_ID
412 ,p_wrkplc_vldtn_ver_id => P_WRKPLC_VLDTN_VER_ID
413 ,p_level_number_id => P_LEVEL_NUMBER_ID
414 ,p_level_code_id => P_LEVEL_CODE_ID
415 ,p_wrkplc_vldtn_lvlnum_id => P_WRKPLC_VLDTN_LVLNUM_ID
416 ,p_object_version_number => P_OBJECT_VERSION_NUMBER
417 ,p_return_status => P_RETURN_STATUS
418 );
419 end if;
420 exception when others then
421 p_wrkplc_vldtn_lvlnum_id := L_WRKPLC_VLDTN_LVLNUM_ID;
422 p_object_version_number := l_object_version_number;
423 --Intentionally not setting the return status to null as error status should be returned.
424 end PQH_DE_IN_UP_CALL;
425 End;