[Home] [Help]
PACKAGE BODY: APPS.PQH_DE_VLDVER_API
Source
1 Package Body PQH_DE_VLDVER_API as
2 /* $Header: pqverapi.pkb 115.1 2002/12/09 22:40:39 rpasapul noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'PQH_DE_VLDVER_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< Insert_Vldtn_Vern >--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure Insert_Vldtn_Vern
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_business_group_id in number
16 ,p_WRKPLC_VLDTN_ID In Number
17 ,p_VERSION_NUMBER In Number Default NULL
18 ,p_REMUNERATION_JOB_DESCRIPTION In VarChar2 Default NULL
19 ,P_TARIFF_CONTRACT_CODE In Varchar2
20 ,P_TARIFF_GROUP_CODE In Varchar2
21 ,P_JOB_GROUP_ID In Number Default NULL
22 ,P_REMUNERATION_JOB_ID In Number Default NULL
23 ,p_DERIVED_GRADE_ID In Number Default NULL
24 ,P_DERIVED_CASE_GROUP_ID In Number Default NULL
25 ,P_DERIVED_SUBCASGRP_ID In Number Default NULL
26 ,P_USER_ENTERABLE_GRADE_ID In Number Default NULL
27 ,P_USER_ENTERABLE_CASE_GROUP_ID In Number Default NULL
28 ,P_USER_ENTERABLE_SUBCASGRP_ID In Number Default NULL
29 ,P_FREEZE In Varchar2 Default NULL
30 ,p_WRKPLC_VLDTN_VER_ID out nocopy Number
31 ,p_object_version_number out nocopy number) is
32
33
34 --
35 -- Declare cursors and local variables
36 --
37
38 l_proc varchar2(500) := g_package||'Insert_Vldtn_Vern';
39 l_object_Version_Number PQH_DE_WRKPLC_VLDTNS.OBJECT_VERSION_NUMBER%TYPE;
40 L_Effective_Date Date;
41 l_Wrkplc_vldtn_Ver_id PQH_DE_WRKPLC_VLDTN_VERS.WRKPLC_VLDTN_VER_ID%TYPE;
42 l_version_Number PQH_DE_WRKPLC_VLDTN_VERS.VERSION_NUMBER%TYPE;
43 begin
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 --
46 -- Issue a savepoint
47 --
48 savepoint Insert_Vldtn_Vern;
49 --
50 -- Truncate the time portion from all IN date parameters
51 --
52 l_effective_date := Trunc(p_effective_Date);
53 --
54 -- Call Before Process User Hook
55 --
56 Select Nvl(Max(Version_number),0) + 1 into l_Version_Number
57 from Pqh_De_Wrkplc_Vldtn_vers
58 where Wrkplc_Vldtn_Id = p_WRKPLC_VLDTN_ID;
59 begin
60 PQH_DE_VLDVER_BK1.Insert_Vldtn_Vern_b
61 (p_effective_date => l_Effective_Date
62 ,p_business_group_id => p_business_group_id
63 ,P_WRKPLC_VLDTN_ID => P_WRKPLC_VLDTN_ID
64 ,P_VERSION_NUMBER => l_Version_Number
65 ,P_REMUNERATION_JOB_DESCRIPTION => P_REMUNERATION_JOB_DESCRIPTION
66 ,P_TARIFF_CONTRACT_CODE => P_TARIFF_CONTRACT_CODE
67 ,P_TARIFF_GROUP_CODE => P_TARIFF_GROUP_CODE
68 ,P_JOB_GROUP_ID => P_JOB_GROUP_ID
69 ,P_REMUNERATION_JOB_ID => P_REMUNERATION_JOB_ID
70 ,P_DERIVED_GRADE_ID => P_DERIVED_GRADE_ID
71 ,p_DERIVED_CASE_GROUP_ID => P_REMUNERATION_JOB_ID
72 ,p_DERIVED_SUBCASGRP_ID => p_DERIVED_SUBCASGRP_ID
73 ,p_USER_ENTERABLE_GRADE_ID => p_USER_ENTERABLE_GRADE_ID
74 ,P_USER_ENTERABLE_CASE_GROUP_ID => P_USER_ENTERABLE_CASE_GROUP_ID
75 ,P_USER_ENTERABLE_SUBCASGRP_ID => P_USER_ENTERABLE_SUBCASGRP_ID
76 ,p_FREEZE => p_FREEZE);
77
78
79 exception
80 when hr_api.cannot_find_prog_unit then
81 hr_api.cannot_find_prog_unit_error
82 (p_module_name => 'Insert_Vldtn_Vern'
83 ,p_hook_type => 'BP'
84 );
85 end;
86 --
87 -- Validation in addition to Row Handlers
88 --
89 --
90 -- Process Logic
91
92
93 pqh_ver_ins.ins
94 (p_effective_date => l_Effective_Date
95 ,P_WRKPLC_VLDTN_ID => P_WRKPLC_VLDTN_ID
96 ,P_VERSION_NUMBER => l_Version_Number
97 ,p_business_group_id => p_business_group_id
98 ,P_TARIFF_CONTRACT_CODE => P_TARIFF_CONTRACT_CODE
99 ,P_TARIFF_GROUP_CODE => P_TARIFF_GROUP_CODE
100 ,p_FREEZE => p_FREEZE
101 ,P_REMUNERATION_JOB_DESCRIPTION => P_REMUNERATION_JOB_DESCRIPTION
102 ,P_JOB_GROUP_ID => P_JOB_GROUP_ID
103 ,P_REMUNERATION_JOB_ID => P_REMUNERATION_JOB_ID
104 ,P_DERIVED_GRADE_ID => P_DERIVED_GRADE_ID
105 ,p_DERIVED_CASE_GROUP_ID => P_REMUNERATION_JOB_ID
106 ,p_DERIVED_SUBCASGRP_ID => p_DERIVED_SUBCASGRP_ID
107 ,p_USER_ENTERABLE_GRADE_ID => p_USER_ENTERABLE_GRADE_ID
108 ,P_USER_ENTERABLE_CASE_GROUP_ID => P_USER_ENTERABLE_CASE_GROUP_ID
109 ,P_USER_ENTERABLE_SUBCASGRP_ID => P_USER_ENTERABLE_SUBCASGRP_ID
110 ,P_Wrkplc_Vldtn_Ver_id => l_Wrkplc_Vldtn_Ver_Id
111 ,p_object_version_number => l_object_version_Number);
112
113 --
114 -- Call After Process User Hook
115 --
116
117 begin
118 PQH_DE_VLDVER_BK1.Insert_Vldtn_Vern_a
119 (p_effective_date => l_Effective_Date
120 ,p_business_group_id => p_business_group_id
121 ,P_WRKPLC_VLDTN_ID => P_WRKPLC_VLDTN_ID
122 ,P_VERSION_NUMBER => l_Version_Number
123 ,P_REMUNERATION_JOB_DESCRIPTION => P_REMUNERATION_JOB_DESCRIPTION
124 ,P_TARIFF_CONTRACT_CODE => P_TARIFF_CONTRACT_CODE
125 ,P_TARIFF_GROUP_CODE => P_TARIFF_GROUP_CODE
126 ,P_JOB_GROUP_ID => P_JOB_GROUP_ID
127 ,P_REMUNERATION_JOB_ID => P_REMUNERATION_JOB_ID
128 ,P_DERIVED_GRADE_ID => P_DERIVED_GRADE_ID
129 ,p_DERIVED_CASE_GROUP_ID => P_REMUNERATION_JOB_ID
130 ,p_DERIVED_SUBCASGRP_ID => p_DERIVED_SUBCASGRP_ID
131 ,p_USER_ENTERABLE_GRADE_ID => p_USER_ENTERABLE_GRADE_ID
132 ,P_USER_ENTERABLE_CASE_GROUP_ID => P_USER_ENTERABLE_CASE_GROUP_ID
133 ,P_USER_ENTERABLE_SUBCASGRP_ID => P_USER_ENTERABLE_SUBCASGRP_ID
134 ,p_FREEZE => p_FREEZE
135 ,p_Wrkplc_Vldtn_Ver_Id => l_Wrkplc_Vldtn_Ver_Id
136 ,p_object_version_number => l_object_version_Number);
137
138 exception
139 when hr_api.cannot_find_prog_unit then
140 hr_api.cannot_find_prog_unit_error
141 (p_module_name => 'Insert_Vldtn_Vern'
142 ,p_hook_type => 'AP'
143 );
144 end;
145 --
146 -- When in validation only mode raise the Validate_Enabled exception
147 --
148 if p_validate then
149 raise hr_api.validate_enabled;
150 end if;
151 --
152 -- Set all output arguments
153 --
154 p_wrkplc_vldtn_Ver_id := l_Wrkplc_vldtn_Ver_id;
155 p_object_version_number := l_object_version_number;
156
157 --
158 hr_utility.set_location(' Leaving:'||l_proc, 70);
159 exception
160 when hr_api.validate_enabled then
161 --
162 -- As the Validate_Enabled exception has been raised
163 -- we must rollback to the savepoint
164 --
165 rollback to Insert_Vldtn_Vern;
166 --
167 -- Only set output warning arguments
168 -- (Any key or derived arguments must be set to null
169 -- when validation only mode is being used.)
170 --
171 p_wrkplc_vldtn_Ver_id := null;
172 p_object_version_number := null;
173 hr_utility.set_location(' Leaving:'||l_proc, 80);
174
175 when others then
176 p_wrkplc_vldtn_Ver_id := null;
177 p_object_version_number := null;
178 --
179 -- A validation or unexpected error has occured
180 --
181 rollback to Insert_Vldtn_Vern;
182 hr_utility.set_location(' Leaving:'||l_proc, 90);
183 raise;
184 end Insert_Vldtn_Vern;
185
186 --
187 -- ----------------------------------------------------------------------------
188 -- |--------------------------< Update_Vldtn_Defn >--------------------------|
189 -- ----------------------------------------------------------------------------
190
191 procedure Update_Vldtn_Vern
192 (p_validate in boolean default false
193 ,p_effective_date in date
194 ,p_business_group_id in number
195 ,p_WRKPLC_VLDTN_ID In Number Default hr_api.g_Number
196 ,P_VERSION_NUMBER In Number Default hr_api.g_Number
197 ,P_REMUNERATION_JOB_DESCRIPTION In VarChar2 Default hr_api.g_Varchar2
198 ,P_TARIFF_CONTRACT_CODE In Varchar2 Default hr_api.g_VArchar2
199 ,P_TARIFF_GROUP_CODE In Varchar2 Default hr_api.g_Varchar2
200 ,P_JOB_GROUP_ID In Number Default hr_api.g_Number
201 ,P_REMUNERATION_JOB_ID In Number Default hr_api.g_Number
202 ,P_DERIVED_GRADE_ID In Number Default hr_api.g_Number
203 ,P_DERIVED_CASE_GROUP_ID In Number Default hr_api.g_Number
204 ,P_DERIVED_SUBCASGRP_ID In Number Default hr_api.g_Number
205 ,P_USER_ENTERABLE_GRADE_ID In Number Default hr_api.g_Number
206 ,P_USER_ENTERABLE_CASE_GROUP_ID In Number Default hr_api.g_Number
207 ,P_USER_ENTERABLE_SUBCASGRP_ID In Number Default hr_api.g_Number
208 ,P_FREEZE In Varchar2 Default hr_api.g_Varchar2
209 ,p_WRKPLC_VLDTN_VER_ID In Number
210 ,p_object_version_number in out nocopy number) Is
211
212 l_proc varchar2(72) := g_package||'Update_Vldtn_Vern';
213 l_object_Version_Number PQH_DE_WRKPLC_VLDTN_VERS.OBJECT_VERSION_NUMBER%TYPE := P_Object_version_Number;
214 L_Effective_Date Date;
215
216 Begin
217 hr_utility.set_location('Entering:'|| l_proc, 10);
218 --
219 -- Issue a savepoint
220 --
221 savepoint Update_Vldtn_Vern;
222 --
223 -- Truncate the time portion from all IN date parameters
224 --
225 l_effective_date := Trunc(p_effective_Date);
226 --
227 -- Call Before Process User Hook
228 --
229 begin
230
231 PQH_DE_VLDVER_BK2.Update_Vldtn_Vern_b
232 (p_effective_date => l_Effective_Date
233 ,p_business_group_id => p_business_group_id
234 ,P_WRKPLC_VLDTN_ID => P_WRKPLC_VLDTN_ID
235 ,P_VERSION_NUMBER => P_VERSION_NUMBER
236 ,P_REMUNERATION_JOB_DESCRIPTION => P_REMUNERATION_JOB_DESCRIPTION
237 ,P_TARIFF_CONTRACT_CODE => P_TARIFF_CONTRACT_CODE
238 ,P_TARIFF_GROUP_CODE => P_TARIFF_GROUP_CODE
239 ,P_JOB_GROUP_ID => P_JOB_GROUP_ID
240 ,P_REMUNERATION_JOB_ID => P_REMUNERATION_JOB_ID
241 ,P_DERIVED_GRADE_ID => P_DERIVED_GRADE_ID
242 ,P_DERIVED_CASE_GROUP_ID => P_DERIVED_CASE_GROUP_ID
243 ,P_DERIVED_SUBCASGRP_ID => P_DERIVED_SUBCASGRP_ID
244 ,P_USER_ENTERABLE_GRADE_ID => P_USER_ENTERABLE_GRADE_ID
245 ,P_USER_ENTERABLE_CASE_GROUP_ID => P_USER_ENTERABLE_CASE_GROUP_ID
246 ,P_USER_ENTERABLE_SUBCASGRP_ID => P_USER_ENTERABLE_SUBCASGRP_ID
247 ,P_FREEZE => P_FREEZE
248 ,p_WRKPLC_VLDTN_VER_ID => p_Wrkplc_Vldtn_ver_Id
249 ,p_object_version_number => l_Object_Version_Number);
250
251 exception
252 when hr_api.cannot_find_prog_unit then
253 hr_api.cannot_find_prog_unit_error
254 (p_module_name => 'Update_Vldtn_Vern'
255 ,p_hook_type => 'BP'
256 );
257 end;
258
259 pqh_ver_upd.upd
260 (p_effective_date => l_Effective_Date
261 ,p_business_group_id => p_business_group_id
262 ,P_WRKPLC_VLDTN_ID => P_WRKPLC_VLDTN_ID
263 ,P_VERSION_NUMBER => P_VERSION_NUMBER
264 ,P_REMUNERATION_JOB_DESCRIPTION => P_REMUNERATION_JOB_DESCRIPTION
265 ,P_TARIFF_CONTRACT_CODE => P_TARIFF_CONTRACT_CODE
266 ,P_TARIFF_GROUP_CODE => P_TARIFF_GROUP_CODE
267 ,P_JOB_GROUP_ID => P_JOB_GROUP_ID
268 ,P_REMUNERATION_JOB_ID => P_REMUNERATION_JOB_ID
269 ,P_DERIVED_GRADE_ID => P_DERIVED_GRADE_ID
270 ,P_DERIVED_CASE_GROUP_ID => P_DERIVED_CASE_GROUP_ID
271 ,P_DERIVED_SUBCASGRP_ID => P_DERIVED_SUBCASGRP_ID
272 ,P_USER_ENTERABLE_GRADE_ID => P_USER_ENTERABLE_GRADE_ID
273 ,P_USER_ENTERABLE_CASE_GROUP_ID => P_USER_ENTERABLE_CASE_GROUP_ID
274 ,P_USER_ENTERABLE_SUBCASGRP_ID => P_USER_ENTERABLE_SUBCASGRP_ID
275 ,P_FREEZE => P_FREEZE
276 ,p_WRKPLC_VLDTN_VER_ID => p_Wrkplc_Vldtn_ver_Id
277 ,p_object_version_number => l_Object_Version_Number);
278
279 --
280 --
281 -- Call After Process User Hook
282 --
283 begin
284 PQH_DE_VLDVER_BK2.Update_Vldtn_Vern_a
285 (p_effective_date => l_Effective_Date
286 ,p_business_group_id => p_business_group_id
287 ,P_WRKPLC_VLDTN_ID => P_WRKPLC_VLDTN_ID
288 ,P_VERSION_NUMBER => P_VERSION_NUMBER
289 ,P_REMUNERATION_JOB_DESCRIPTION => P_REMUNERATION_JOB_DESCRIPTION
290 ,P_TARIFF_CONTRACT_CODE => P_TARIFF_CONTRACT_CODE
291 ,P_TARIFF_GROUP_CODE => P_TARIFF_GROUP_CODE
292 ,P_JOB_GROUP_ID => P_JOB_GROUP_ID
293 ,P_REMUNERATION_JOB_ID => P_REMUNERATION_JOB_ID
294 ,P_DERIVED_GRADE_ID => P_DERIVED_GRADE_ID
295 ,P_DERIVED_CASE_GROUP_ID => P_DERIVED_CASE_GROUP_ID
296 ,P_DERIVED_SUBCASGRP_ID => P_DERIVED_SUBCASGRP_ID
297 ,P_USER_ENTERABLE_GRADE_ID => P_USER_ENTERABLE_GRADE_ID
298 ,P_USER_ENTERABLE_CASE_GROUP_ID => P_USER_ENTERABLE_CASE_GROUP_ID
299 ,P_USER_ENTERABLE_SUBCASGRP_ID => P_USER_ENTERABLE_SUBCASGRP_ID
300 ,P_FREEZE => P_FREEZE
301 ,p_WRKPLC_VLDTN_VER_ID => p_Wrkplc_Vldtn_ver_Id
302 ,p_object_version_number => l_Object_Version_Number);
303
304
305 exception
306 when hr_api.cannot_find_prog_unit then
307 hr_api.cannot_find_prog_unit_error
308 (p_module_name => 'Update_Vldtn_Vern'
309 ,p_hook_type => 'AP'
310 );
311 end;
312 --
313 -- When in validation only mode raise the Validate_Enabled exception
314 --
315 if p_validate then
316 raise hr_api.validate_enabled;
317 end if;
318 --
319 -- Set all output arguments
320 --
321
322 p_object_version_number := l_object_version_number;
323
324 --
325 hr_utility.set_location(' Leaving:'||l_proc, 70);
326 exception
327 when hr_api.validate_enabled then
328 --
329 -- As the Validate_Enabled exception has been raised
330 -- we must rollback to the savepoint
331 --
332 rollback to Update_Vldtn_Vern;
333 --
334 -- Only set output warning arguments
335 -- (Any key or derived arguments must be set to null
336 -- when validation only mode is being used.)
337 --
338 hr_utility.set_location(' Leaving:'||l_proc, 80);
339
340 when others then
341 p_object_version_number := l_object_version_number;
342 --
343 -- A validation or unexpected error has occured
344 --
345 rollback to Update_Vldtn_Vern;
346 hr_utility.set_location(' Leaving:'||l_proc, 90);
347 raise;
348 end Update_Vldtn_Vern;
349
350 --
351 -- ----------------------------------------------------------------------------
352 -- |-------------------< delete_Vldtn_Defn>------------------------------|
353 -- ----------------------------------------------------------------------------
354 procedure delete_Vldtn_Vern
355 (p_validate in boolean default false
356 ,p_WRKPLC_VLDTN_Ver_ID In Number
357 ,p_object_version_number In number) Is --
358
359 -- Declare cursors and local variables
360 --
361 l_proc varchar2(72) := g_package||'delete_Vldtn_Vern';
362 --
363 begin
364 hr_utility.set_location('Entering:'|| l_proc, 10);
365 --
366 -- Issue a savepoint
367 --
368 savepoint delete_Vldtn_Vern;
369 --
370 -- Call Before Process User Hook
371 --
372 begin
373 PQH_DE_VLDVER_BK3.Delete_Vldtn_Vern_b
374 (p_WRKPLC_VLDTN_VERID => p_WRKPLC_VLDTN_VER_ID
378 hr_api.cannot_find_prog_unit_error
375 ,p_object_version_number => p_object_version_number);
376 exception
377 when hr_api.cannot_find_prog_unit then
379 (p_module_name => 'delete_Vldtn_Vern'
380 ,p_hook_type => 'BP');
381 end;
382 --
383 -- Process Logic
384 --
385 pqh_ver_del.del
386 (p_WRKPLC_VLDTN_Ver_ID => p_WRKPLC_VLDTN_VER_ID
387 ,p_object_version_number => p_object_version_number);
388 --
389 -- Call After Process User Hook
390 --
391 begin
392
393 PQH_DE_VLDVER_BK3.Delete_Vldtn_Vern_a
394 (p_WRKPLC_VLDTN_VER_ID => p_WRKPLC_VLDTN_VER_ID
395 ,p_object_version_number => p_object_version_number);
396
397 exception
398 when hr_api.cannot_find_prog_unit then
399 hr_api.cannot_find_prog_unit_error
400 (p_module_name => 'delete_Vldtn_Vern'
401 ,p_hook_type => 'AP');
402 end;
403 --
404 -- When in validation only mode raise the Validate_Enabled exception
405 --
406 if p_validate then
407 raise hr_api.validate_enabled;
408 end if;
409 --
410 -- Set all output arguments
411 --
412 hr_utility.set_location(' Leaving:'||l_proc, 70);
413 exception
414 when hr_api.validate_enabled then
415 --
416 -- As the Validate_Enabled exception has been raised
417 -- we must rollback to the savepoint
418 --
419 rollback to delete_Vldtn_Vern;
420 --
421 -- Only set output warning arguments
422 -- (Any key or derived arguments must be set to null
423 -- when validation only mode is being used.)
424 --
425 hr_utility.set_location(' Leaving:'||l_proc, 80);
426 when others then
427 --
428 -- A validation or unexpected error has occured
429 --
430 rollback to delete_Vldtn_Vern;
431 hr_utility.set_location(' Leaving:'||l_proc, 90);
432 raise;
433 end delete_Vldtn_Vern;
434
435 end PQH_DE_VLDVER_API;