[Home] [Help]
PACKAGE BODY: APPS.OKS_PML_PVT
Source
1 PACKAGE BODY OKS_PML_PVT AS
2 /* $Header: OKSSPMLB.pls 120.1 2005/07/15 09:25:35 parkumar noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE load_error_tbl
6 ---------------------------------------------------------------------------
7 PROCEDURE load_error_tbl (
8 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
9 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
10
11 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
12 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
13 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
14 BEGIN
15 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
16 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
17 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
18 -- automatically increments the index by 1, (making it 2), however, when the GET function
19 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
20 -- message 2. To circumvent this problem, check the amount of messages and compensate.
21 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
22 -- will only update the index variable when 1 and only 1 message is on the stack.
23 IF (last_msg_idx = 1) THEN
24 l_msg_idx := FND_MSG_PUB.G_FIRST;
25 END IF;
26 LOOP
27 fnd_msg_pub.get(
28 p_msg_index => l_msg_idx,
29 p_encoded => fnd_api.g_false,
30 p_data => px_error_rec.msg_data,
31 p_msg_index_out => px_error_rec.msg_count);
32 px_error_tbl(j) := px_error_rec;
33 j := j + 1;
34 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
35 END LOOP;
36 END load_error_tbl;
37 ---------------------------------------------------------------------------
38 -- FUNCTION find_highest_exception
39 ---------------------------------------------------------------------------
40 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
41 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
42 FUNCTION find_highest_exception(
43 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
44 ) RETURN VARCHAR2 IS
45 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
46 i INTEGER := 1;
47 BEGIN
48 IF (p_error_tbl.COUNT > 0) THEN
49 i := p_error_tbl.FIRST;
50 LOOP
51 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
52 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
53 l_return_status := p_error_tbl(i).error_type;
54 END IF;
55 END IF;
56 EXIT WHEN (i = p_error_tbl.LAST);
57 i := p_error_tbl.NEXT(i);
58 END LOOP;
59 END IF;
60 RETURN(l_return_status);
61 END find_highest_exception;
62 ---------------------------------------------------------------------------
63 -- FUNCTION get_seq_id
64 ---------------------------------------------------------------------------
65 FUNCTION get_seq_id RETURN NUMBER IS
66 BEGIN
67 RETURN(okc_p_util.raw_to_number(sys_guid()));
68 END get_seq_id;
69
70 ---------------------------------------------------------------------------
71 -- PROCEDURE qc
72 ---------------------------------------------------------------------------
73 PROCEDURE qc IS
74 BEGIN
75 null;
76 END qc;
77
78 ---------------------------------------------------------------------------
79 -- PROCEDURE change_version
80 ---------------------------------------------------------------------------
81 PROCEDURE change_version IS
82 BEGIN
83 null;
84 END change_version;
85
86 ---------------------------------------------------------------------------
87 -- PROCEDURE api_copy
88 ---------------------------------------------------------------------------
89 PROCEDURE api_copy IS
90 BEGIN
91 null;
92 END api_copy;
93
94 ---------------------------------------------------------------------------
95 -- FUNCTION get_rec for: OKS_PM_STREAM_LEVELS_V
96 ---------------------------------------------------------------------------
97 FUNCTION get_rec (
98 p_pmlv_rec IN pmlv_rec_type,
99 x_no_data_found OUT NOCOPY BOOLEAN
100 ) RETURN pmlv_rec_type IS
101 CURSOR oks_pmlv_pk_csr (p_id IN NUMBER) IS
102 SELECT
103 ID,
104 CLE_ID,
105 DNZ_CHR_ID,
106 ACTIVITY_LINE_ID,
107 SEQUENCE_NUMBER,
108 NUMBER_OF_OCCURENCES,
109 START_DATE,
110 END_DATE,
111 FREQUENCY,
112 FREQUENCY_UOM,
113 OFFSET_DURATION,
114 OFFSET_UOM,
115 AUTOSCHEDULE_YN,
116 PROGRAM_APPLICATION_ID,
117 PROGRAM_ID,
118 PROGRAM_UPDATE_DATE,
119 OBJECT_VERSION_NUMBER,
120 SECURITY_GROUP_ID,
121 REQUEST_ID,
122 CREATED_BY,
123 CREATION_DATE,
124 LAST_UPDATED_BY,
125 LAST_UPDATE_DATE,
126 LAST_UPDATE_LOGIN,
127 -- R12 Data Model Changes 4485150 Start
128 ORIG_SYSTEM_ID1 ,
129 ORIG_SYSTEM_REFERENCE1,
130 ORIG_SYSTEM_SOURCE_CODE
131 -- R12 Data Model Changes 4485150 End
132 FROM Oks_Pm_Stream_Levels_V
133 WHERE oks_pm_stream_levels_v.id = p_id;
134 l_oks_pmlv_pk oks_pmlv_pk_csr%ROWTYPE;
135 l_pmlv_rec pmlv_rec_type;
136 BEGIN
137 x_no_data_found := TRUE;
138 -- Get current database values
139 OPEN oks_pmlv_pk_csr (p_pmlv_rec.id);
140 FETCH oks_pmlv_pk_csr INTO
141 l_pmlv_rec.id,
142 l_pmlv_rec.cle_id,
143 l_pmlv_rec.dnz_chr_id,
144 l_pmlv_rec.activity_line_id,
145 l_pmlv_rec.sequence_number,
146 l_pmlv_rec.number_of_occurences,
147 l_pmlv_rec.start_date,
148 l_pmlv_rec.end_date,
149 l_pmlv_rec.frequency,
150 l_pmlv_rec.frequency_uom,
151 l_pmlv_rec.offset_duration,
152 l_pmlv_rec.offset_uom,
153 l_pmlv_rec.autoschedule_yn,
154 l_pmlv_rec.program_application_id,
155 l_pmlv_rec.program_id,
156 l_pmlv_rec.program_update_date,
157 l_pmlv_rec.object_version_number,
158 l_pmlv_rec.security_group_id,
159 l_pmlv_rec.request_id,
160 l_pmlv_rec.created_by,
161 l_pmlv_rec.creation_date,
162 l_pmlv_rec.last_updated_by,
163 l_pmlv_rec.last_update_date,
164 l_pmlv_rec.last_update_login,
165 -- R12 Data Model Changes 4485150 Start
166 l_pmlv_rec.orig_system_id1,
167 l_pmlv_rec.orig_system_reference1,
168 l_pmlv_rec.orig_system_source_code
169 -- R12 Data Model Changes 4485150 End
170 ;
171 x_no_data_found := oks_pmlv_pk_csr%NOTFOUND;
172 CLOSE oks_pmlv_pk_csr;
173 RETURN(l_pmlv_rec);
174 END get_rec;
175
176 ------------------------------------------------------------------
177 -- This version of get_rec sets error messages if no data found --
178 ------------------------------------------------------------------
179 FUNCTION get_rec (
180 p_pmlv_rec IN pmlv_rec_type,
181 x_return_status OUT NOCOPY VARCHAR2
182 ) RETURN pmlv_rec_type IS
183 l_pmlv_rec pmlv_rec_type;
184 l_row_notfound BOOLEAN := TRUE;
185 BEGIN
186 x_return_status := OKC_API.G_RET_STS_SUCCESS;
187 l_pmlv_rec := get_rec(p_pmlv_rec, l_row_notfound);
188 IF (l_row_notfound) THEN
189 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
190 x_return_status := OKC_API.G_RET_STS_ERROR;
191 END IF;
192 RETURN(l_pmlv_rec);
193 END get_rec;
194 -----------------------------------------------------------
195 -- So we don't have to pass an "l_row_notfound" variable --
196 -----------------------------------------------------------
197 FUNCTION get_rec (
198 p_pmlv_rec IN pmlv_rec_type
199 ) RETURN pmlv_rec_type IS
200 l_row_not_found BOOLEAN := TRUE;
201 BEGIN
202 RETURN(get_rec(p_pmlv_rec, l_row_not_found));
203 END get_rec;
204 ---------------------------------------------------------------------------
205 -- FUNCTION get_rec for: OKS_PM_STREAM_LEVELS
206 ---------------------------------------------------------------------------
207 FUNCTION get_rec (
208 p_pml_rec IN pml_rec_type,
209 x_no_data_found OUT NOCOPY BOOLEAN
210 ) RETURN pml_rec_type IS
211 CURSOR oks_pm_stream_levels_pk_csr (p_id IN NUMBER) IS
212 SELECT
213 ID,
214 CLE_ID,
215 DNZ_CHR_ID,
216 ACTIVITY_LINE_ID,
217 SEQUENCE_NUMBER,
218 NUMBER_OF_OCCURENCES,
219 START_DATE,
220 END_DATE,
221 FREQUENCY,
222 FREQUENCY_UOM,
223 OFFSET_DURATION,
224 OFFSET_UOM,
225 AUTOSCHEDULE_YN,
226 PROGRAM_APPLICATION_ID,
227 PROGRAM_ID,
228 PROGRAM_UPDATE_DATE,
229 OBJECT_VERSION_NUMBER,
230 REQUEST_ID,
231 CREATED_BY,
232 CREATION_DATE,
233 LAST_UPDATED_BY,
234 LAST_UPDATE_DATE,
235 LAST_UPDATE_LOGIN,
236 -- R12 Data Model Changes 4485150 Start
237 ORIG_SYSTEM_ID1 ,
238 ORIG_SYSTEM_REFERENCE1,
239 ORIG_SYSTEM_SOURCE_CODE
240 -- R12 Data Model Changes 4485150 End
241 FROM Oks_Pm_Stream_Levels
242 WHERE oks_pm_stream_levels.id = p_id;
243 l_oks_pm_stream_levels_pk oks_pm_stream_levels_pk_csr%ROWTYPE;
244 l_pml_rec pml_rec_type;
245 BEGIN
246 x_no_data_found := TRUE;
247 -- Get current database values
248 OPEN oks_pm_stream_levels_pk_csr (p_pml_rec.id);
249 FETCH oks_pm_stream_levels_pk_csr INTO
250 l_pml_rec.id,
251 l_pml_rec.cle_id,
252 l_pml_rec.dnz_chr_id,
253 l_pml_rec.activity_line_id,
254 l_pml_rec.sequence_number,
255 l_pml_rec.number_of_occurences,
256 l_pml_rec.start_date,
257 l_pml_rec.end_date,
258 l_pml_rec.frequency,
259 l_pml_rec.frequency_uom,
260 l_pml_rec.offset_duration,
261 l_pml_rec.offset_uom,
262 l_pml_rec.autoschedule_yn,
263 l_pml_rec.program_application_id,
264 l_pml_rec.program_id,
265 l_pml_rec.program_update_date,
266 l_pml_rec.object_version_number,
267 l_pml_rec.request_id,
268 l_pml_rec.created_by,
269 l_pml_rec.creation_date,
270 l_pml_rec.last_updated_by,
271 l_pml_rec.last_update_date,
272 l_pml_rec.last_update_login,
273 -- R12 Data Model Changes 4485150 Start
274 l_pml_rec.orig_system_id1,
275 l_pml_rec.orig_system_reference1,
276 l_pml_rec.orig_system_source_code
277 -- R12 Data Model Changes 4485150 End
278 ;
279 x_no_data_found := oks_pm_stream_levels_pk_csr%NOTFOUND;
280 CLOSE oks_pm_stream_levels_pk_csr;
281 RETURN(l_pml_rec);
282 END get_rec;
283
284 ------------------------------------------------------------------
285 -- This version of get_rec sets error messages if no data found --
286 ------------------------------------------------------------------
287 FUNCTION get_rec (
288 p_pml_rec IN pml_rec_type,
289 x_return_status OUT NOCOPY VARCHAR2
290 ) RETURN pml_rec_type IS
291 l_pml_rec pml_rec_type;
292 l_row_notfound BOOLEAN := TRUE;
293 BEGIN
294 x_return_status := OKC_API.G_RET_STS_SUCCESS;
295 l_pml_rec := get_rec(p_pml_rec, l_row_notfound);
296 IF (l_row_notfound) THEN
297 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
298 x_return_status := OKC_API.G_RET_STS_ERROR;
299 END IF;
300 RETURN(l_pml_rec);
301 END get_rec;
302 -----------------------------------------------------------
303 -- So we don't have to pass an "l_row_notfound" variable --
304 -----------------------------------------------------------
305 FUNCTION get_rec (
306 p_pml_rec IN pml_rec_type
307 ) RETURN pml_rec_type IS
308 l_row_not_found BOOLEAN := TRUE;
309 BEGIN
310 RETURN(get_rec(p_pml_rec, l_row_not_found));
311 END get_rec;
312 ---------------------------------------------------------------------------
313 -- FUNCTION null_out_defaults for: OKS_PM_STREAM_LEVELS_V
314 ---------------------------------------------------------------------------
315 FUNCTION null_out_defaults (
316 p_pmlv_rec IN pmlv_rec_type
317 ) RETURN pmlv_rec_type IS
318 l_pmlv_rec pmlv_rec_type := p_pmlv_rec;
319 BEGIN
320 IF (l_pmlv_rec.id = OKC_API.G_MISS_NUM ) THEN
321 l_pmlv_rec.id := NULL;
322 END IF;
323 IF (l_pmlv_rec.cle_id = OKC_API.G_MISS_NUM ) THEN
324 l_pmlv_rec.cle_id := NULL;
325 END IF;
326 IF (l_pmlv_rec.dnz_chr_id = OKC_API.G_MISS_NUM ) THEN
327 l_pmlv_rec.dnz_chr_id := NULL;
328 END IF;
329 IF (l_pmlv_rec.activity_line_id = OKC_API.G_MISS_NUM ) THEN
330 l_pmlv_rec.activity_line_id := NULL;
331 END IF;
332 IF (l_pmlv_rec.sequence_number = OKC_API.G_MISS_NUM ) THEN
333 l_pmlv_rec.sequence_number := NULL;
334 END IF;
335 IF (l_pmlv_rec.number_of_occurences = OKC_API.G_MISS_NUM ) THEN
336 l_pmlv_rec.number_of_occurences := NULL;
337 END IF;
338 IF (l_pmlv_rec.start_date = OKC_API.G_MISS_DATE ) THEN
339 l_pmlv_rec.start_date := NULL;
340 END IF;
341 IF (l_pmlv_rec.end_date = OKC_API.G_MISS_DATE ) THEN
342 l_pmlv_rec.end_date := NULL;
343 END IF;
344 IF (l_pmlv_rec.frequency = OKC_API.G_MISS_NUM ) THEN
345 l_pmlv_rec.frequency := NULL;
346 END IF;
347 IF (l_pmlv_rec.frequency_uom = OKC_API.G_MISS_CHAR ) THEN
348 l_pmlv_rec.frequency_uom := NULL;
349 END IF;
350 IF (l_pmlv_rec.offset_duration = OKC_API.G_MISS_NUM ) THEN
351 l_pmlv_rec.offset_duration := NULL;
352 END IF;
353 IF (l_pmlv_rec.offset_uom = OKC_API.G_MISS_CHAR ) THEN
354 l_pmlv_rec.offset_uom := NULL;
355 END IF;
356 IF (l_pmlv_rec.autoschedule_yn = OKC_API.G_MISS_CHAR ) THEN
357 l_pmlv_rec.autoschedule_yn := NULL;
358 END IF;
359 IF (l_pmlv_rec.program_application_id = OKC_API.G_MISS_NUM ) THEN
360 l_pmlv_rec.program_application_id := NULL;
361 END IF;
362 IF (l_pmlv_rec.program_id = OKC_API.G_MISS_NUM ) THEN
363 l_pmlv_rec.program_id := NULL;
364 END IF;
365 IF (l_pmlv_rec.program_update_date = OKC_API.G_MISS_DATE ) THEN
366 l_pmlv_rec.program_update_date := NULL;
367 END IF;
368 IF (l_pmlv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
369 l_pmlv_rec.object_version_number := NULL;
370 END IF;
371 IF (l_pmlv_rec.security_group_id = OKC_API.G_MISS_NUM ) THEN
372 l_pmlv_rec.security_group_id := NULL;
373 END IF;
374 IF (l_pmlv_rec.request_id = OKC_API.G_MISS_NUM ) THEN
375 l_pmlv_rec.request_id := NULL;
376 END IF;
377 IF (l_pmlv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
378 l_pmlv_rec.created_by := NULL;
379 END IF;
380 IF (l_pmlv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
381 l_pmlv_rec.creation_date := NULL;
382 END IF;
383 IF (l_pmlv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
384 l_pmlv_rec.last_updated_by := NULL;
385 END IF;
386 IF (l_pmlv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
387 l_pmlv_rec.last_update_date := NULL;
388 END IF;
389 IF (l_pmlv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
390 l_pmlv_rec.last_update_login := NULL;
391 END IF;
392 -- R12 Data Model Changes 4485150 Start
393 IF (l_pmlv_rec.orig_system_id1 = OKC_API.G_MISS_NUM ) THEN
394 l_pmlv_rec.orig_system_id1 := NULL;
395 END IF;
396 IF (l_pmlv_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR ) THEN
397 l_pmlv_rec.orig_system_reference1 := NULL;
398 END IF;
399 IF (l_pmlv_rec.orig_system_source_code = OKC_API.G_MISS_CHAR ) THEN
400 l_pmlv_rec.orig_system_source_code := NULL;
401 END IF;
402 -- R12 Data Model Changes 4485150 End
403 RETURN(l_pmlv_rec);
404 END null_out_defaults;
405 ---------------------------------
406 -- Validate_Attributes for: ID --
407 ---------------------------------
408 PROCEDURE validate_id(
409 x_return_status OUT NOCOPY VARCHAR2,
410 p_id IN NUMBER) IS
411 BEGIN
412 x_return_status := OKC_API.G_RET_STS_SUCCESS;
413 IF (p_id = OKC_API.G_MISS_NUM OR
414 p_id IS NULL)
415 THEN
416 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
417 x_return_status := OKC_API.G_RET_STS_ERROR;
418 RAISE G_EXCEPTION_HALT_VALIDATION;
419 END IF;
420 EXCEPTION
421 WHEN G_EXCEPTION_HALT_VALIDATION THEN
422 null;
423 WHEN OTHERS THEN
424 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
425 ,p_msg_name => G_UNEXPECTED_ERROR
426 ,p_token1 => G_SQLCODE_TOKEN
427 ,p_token1_value => SQLCODE
428 ,p_token2 => G_SQLERRM_TOKEN
429 ,p_token2_value => SQLERRM);
430 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
431 END validate_id;
432 ---------------------------------------------------------------------------
433 -- FUNCTION Validate_Attributes
434 ---------------------------------------------------------------------------
435 ----------------------------------------------------
436 -- Validate_Attributes for:OKS_PM_STREAM_LEVELS_V --
437 ----------------------------------------------------
438 FUNCTION Validate_Attributes (
439 p_pmlv_rec IN pmlv_rec_type
440 ) RETURN VARCHAR2 IS
441 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
442 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
443 BEGIN
444 -----------------------------
445 -- Column Level Validation --
446 -----------------------------
447 -- ***
448 -- id
449 -- ***
450 validate_id(x_return_status, p_pmlv_rec.id);
451 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
452 l_return_status := x_return_status;
453 RAISE G_EXCEPTION_HALT_VALIDATION;
454 END IF;
455
456 RETURN(l_return_status);
457 EXCEPTION
458 WHEN G_EXCEPTION_HALT_VALIDATION THEN
459 RETURN(l_return_status);
460 WHEN OTHERS THEN
461 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
462 ,p_msg_name => G_UNEXPECTED_ERROR
463 ,p_token1 => G_SQLCODE_TOKEN
464 ,p_token1_value => SQLCODE
465 ,p_token2 => G_SQLERRM_TOKEN
466 ,p_token2_value => SQLERRM);
467 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
468 RETURN(l_return_status);
469 END Validate_Attributes;
470 ---------------------------------------------------------------------------
471 -- PROCEDURE Validate_Record
472 ---------------------------------------------------------------------------
473 ------------------------------------------------
474 -- Validate Record for:OKS_PM_STREAM_LEVELS_V --
475 ------------------------------------------------
476 FUNCTION Validate_Record (
477 p_pmlv_rec IN pmlv_rec_type,
478 p_db_pmlv_rec IN pmlv_rec_type
479 ) RETURN VARCHAR2 IS
480 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
481 BEGIN
482 RETURN (l_return_status);
483 END Validate_Record;
484 FUNCTION Validate_Record (
485 p_pmlv_rec IN pmlv_rec_type
486 ) RETURN VARCHAR2 IS
487 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
488 l_db_pmlv_rec pmlv_rec_type := get_rec(p_pmlv_rec);
489 BEGIN
490 l_return_status := Validate_Record(p_pmlv_rec => p_pmlv_rec,
491 p_db_pmlv_rec => l_db_pmlv_rec);
492 RETURN (l_return_status);
493 END Validate_Record;
494
495 ---------------------------------------------------------------------------
496 -- PROCEDURE Migrate
497 ---------------------------------------------------------------------------
498 PROCEDURE migrate (
499 p_from IN pmlv_rec_type,
500 p_to IN OUT NOCOPY pml_rec_type
501 ) IS
502 BEGIN
503 p_to.id := p_from.id;
504 p_to.cle_id := p_from.cle_id;
505 p_to.dnz_chr_id := p_from.dnz_chr_id;
506 p_to.activity_line_id := p_from.activity_line_id;
507 p_to.sequence_number := p_from.sequence_number;
508 p_to.number_of_occurences := p_from.number_of_occurences;
509 p_to.start_date := p_from.start_date;
510 p_to.end_date := p_from.end_date;
511 p_to.frequency := p_from.frequency;
512 p_to.frequency_uom := p_from.frequency_uom;
513 p_to.offset_duration := p_from.offset_duration;
514 p_to.offset_uom := p_from.offset_uom;
515 p_to.autoschedule_yn := p_from.autoschedule_yn;
516 p_to.program_application_id := p_from.program_application_id;
517 p_to.program_id := p_from.program_id;
518 p_to.program_update_date := p_from.program_update_date;
519 p_to.object_version_number := p_from.object_version_number;
520 p_to.request_id := p_from.request_id;
521 p_to.created_by := p_from.created_by;
522 p_to.creation_date := p_from.creation_date;
523 p_to.last_updated_by := p_from.last_updated_by;
524 p_to.last_update_date := p_from.last_update_date;
525 p_to.last_update_login := p_from.last_update_login;
526 -- R12 Data Model Changes 4485150 Start
527 p_to.orig_system_id1 := p_from.orig_system_id1;
528 p_to.orig_system_reference1 := p_from.orig_system_reference1;
529 p_to.orig_system_source_code := p_from.orig_system_source_code;
530 -- R12 Data Model Changes 4485150 End
531 END migrate;
532 PROCEDURE migrate (
533 p_from IN pml_rec_type,
534 p_to IN OUT NOCOPY pmlv_rec_type
535 ) IS
536 BEGIN
537 p_to.id := p_from.id;
538 p_to.cle_id := p_from.cle_id;
539 p_to.dnz_chr_id := p_from.dnz_chr_id;
540 p_to.activity_line_id := p_from.activity_line_id;
541 p_to.sequence_number := p_from.sequence_number;
542 p_to.number_of_occurences := p_from.number_of_occurences;
543 p_to.start_date := p_from.start_date;
544 p_to.end_date := p_from.end_date;
545 p_to.frequency := p_from.frequency;
546 p_to.frequency_uom := p_from.frequency_uom;
547 p_to.offset_duration := p_from.offset_duration;
548 p_to.offset_uom := p_from.offset_uom;
549 p_to.autoschedule_yn := p_from.autoschedule_yn;
550 p_to.program_application_id := p_from.program_application_id;
551 p_to.program_id := p_from.program_id;
552 p_to.program_update_date := p_from.program_update_date;
553 p_to.object_version_number := p_from.object_version_number;
554 p_to.request_id := p_from.request_id;
555 p_to.created_by := p_from.created_by;
556 p_to.creation_date := p_from.creation_date;
557 p_to.last_updated_by := p_from.last_updated_by;
558 p_to.last_update_date := p_from.last_update_date;
559 p_to.last_update_login := p_from.last_update_login;
560 -- R12 Data Model Changes 4485150 Start
561 p_to.orig_system_id1 := p_from.orig_system_id1;
562 p_to.orig_system_reference1 := p_from.orig_system_reference1;
563 p_to.orig_system_source_code := p_from.orig_system_source_code;
564 -- R12 Data Model Changes 4485150 End
565 END migrate;
566 ---------------------------------------------------------------------------
567 -- PROCEDURE validate_row
568 ---------------------------------------------------------------------------
569 ---------------------------------------------
570 -- validate_row for:OKS_PM_STREAM_LEVELS_V --
571 ---------------------------------------------
572 PROCEDURE validate_row(
573 p_api_version IN NUMBER,
574 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
575 x_return_status OUT NOCOPY VARCHAR2,
576 x_msg_count OUT NOCOPY NUMBER,
577 x_msg_data OUT NOCOPY VARCHAR2,
578 p_pmlv_rec IN pmlv_rec_type) IS
579
580 l_api_version CONSTANT NUMBER := 1;
581 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
582 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
583 l_pmlv_rec pmlv_rec_type := p_pmlv_rec;
584 l_pml_rec pml_rec_type;
585 l_pml_rec pml_rec_type;
586 BEGIN
587 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
588 G_PKG_NAME,
589 p_init_msg_list,
590 l_api_version,
591 p_api_version,
592 '_PVT',
593 x_return_status);
594 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
595 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
596 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
597 RAISE OKC_API.G_EXCEPTION_ERROR;
598 END IF;
599 --- Validate all non-missing attributes (Item Level Validation)
600 l_return_status := Validate_Attributes(l_pmlv_rec);
601 --- If any errors happen abort API
602 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
603 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
604 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
605 RAISE OKC_API.G_EXCEPTION_ERROR;
606 END IF;
607 l_return_status := Validate_Record(l_pmlv_rec);
608 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
609 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
610 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
611 RAISE OKC_API.G_EXCEPTION_ERROR;
612 END IF;
613 x_return_status := l_return_status;
614 EXCEPTION
615 WHEN OKC_API.G_EXCEPTION_ERROR THEN
616 x_return_status := OKC_API.HANDLE_EXCEPTIONS
617 (
618 l_api_name,
619 G_PKG_NAME,
620 'OKC_API.G_RET_STS_ERROR',
621 x_msg_count,
622 x_msg_data,
623 '_PVT'
624 );
625 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
626 x_return_status := OKC_API.HANDLE_EXCEPTIONS
627 (
628 l_api_name,
629 G_PKG_NAME,
630 'OKC_API.G_RET_STS_UNEXP_ERROR',
631 x_msg_count,
632 x_msg_data,
633 '_PVT'
634 );
635 WHEN OTHERS THEN
636 x_return_status := OKC_API.HANDLE_EXCEPTIONS
637 (
638 l_api_name,
639 G_PKG_NAME,
640 'OTHERS',
641 x_msg_count,
642 x_msg_data,
643 '_PVT'
644 );
645 END validate_row;
646 --------------------------------------------------------
647 -- PL/SQL TBL validate_row for:OKS_PM_STREAM_LEVELS_V --
648 --------------------------------------------------------
649 PROCEDURE validate_row(
650 p_api_version IN NUMBER,
651 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
652 x_return_status OUT NOCOPY VARCHAR2,
653 x_msg_count OUT NOCOPY NUMBER,
654 x_msg_data OUT NOCOPY VARCHAR2,
655 p_pmlv_tbl IN pmlv_tbl_type,
656 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
657
658 l_api_version CONSTANT NUMBER := 1;
659 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
660 i NUMBER := 0;
661 BEGIN
662 OKC_API.init_msg_list(p_init_msg_list);
663 -- Make sure PL/SQL table has records in it before passing
664 IF (p_pmlv_tbl.COUNT > 0) THEN
665 i := p_pmlv_tbl.FIRST;
666 LOOP
667 DECLARE
668 l_error_rec OKC_API.ERROR_REC_TYPE;
669 BEGIN
670 l_error_rec.api_name := l_api_name;
671 l_error_rec.api_package := G_PKG_NAME;
672 l_error_rec.idx := i;
673 validate_row (
674 p_api_version => p_api_version,
675 p_init_msg_list => OKC_API.G_FALSE,
676 x_return_status => l_error_rec.error_type,
677 x_msg_count => l_error_rec.msg_count,
678 x_msg_data => l_error_rec.msg_data,
679 p_pmlv_rec => p_pmlv_tbl(i));
680 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
681 l_error_rec.sqlcode := SQLCODE;
682 load_error_tbl(l_error_rec, px_error_tbl);
683 ELSE
684 x_msg_count := l_error_rec.msg_count;
685 x_msg_data := l_error_rec.msg_data;
686 END IF;
687 EXCEPTION
688 WHEN OKC_API.G_EXCEPTION_ERROR THEN
689 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
690 l_error_rec.sqlcode := SQLCODE;
691 load_error_tbl(l_error_rec, px_error_tbl);
692 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
693 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
694 l_error_rec.sqlcode := SQLCODE;
695 load_error_tbl(l_error_rec, px_error_tbl);
696 WHEN OTHERS THEN
697 l_error_rec.error_type := 'OTHERS';
698 l_error_rec.sqlcode := SQLCODE;
699 load_error_tbl(l_error_rec, px_error_tbl);
700 END;
701 EXIT WHEN (i = p_pmlv_tbl.LAST);
702 i := p_pmlv_tbl.NEXT(i);
703 END LOOP;
704 END IF;
705 -- Loop through the error_tbl to find the error with the highest severity
706 -- and return it.
707 x_return_status := find_highest_exception(px_error_tbl);
708 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
709 EXCEPTION
710 WHEN OKC_API.G_EXCEPTION_ERROR THEN
711 x_return_status := OKC_API.HANDLE_EXCEPTIONS
712 (
713 l_api_name,
714 G_PKG_NAME,
715 'OKC_API.G_RET_STS_ERROR',
716 x_msg_count,
717 x_msg_data,
718 '_PVT'
719 );
720 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
721 x_return_status := OKC_API.HANDLE_EXCEPTIONS
722 (
723 l_api_name,
724 G_PKG_NAME,
725 'OKC_API.G_RET_STS_UNEXP_ERROR',
726 x_msg_count,
727 x_msg_data,
728 '_PVT'
729 );
730 WHEN OTHERS THEN
731 x_return_status := OKC_API.HANDLE_EXCEPTIONS
732 (
733 l_api_name,
734 G_PKG_NAME,
735 'OTHERS',
736 x_msg_count,
737 x_msg_data,
738 '_PVT'
739 );
740 END validate_row;
741
742 --------------------------------------------------------
743 -- PL/SQL TBL validate_row for:OKS_PM_STREAM_LEVELS_V --
744 --------------------------------------------------------
745 PROCEDURE validate_row(
746 p_api_version IN NUMBER,
747 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_msg_data OUT NOCOPY VARCHAR2,
751 p_pmlv_tbl IN pmlv_tbl_type) IS
752
753 l_api_version CONSTANT NUMBER := 1;
754 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
755 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
756 l_error_tbl OKC_API.ERROR_TBL_TYPE;
757 BEGIN
758 OKC_API.init_msg_list(p_init_msg_list);
759 -- Make sure PL/SQL table has records in it before passing
760 IF (p_pmlv_tbl.COUNT > 0) THEN
761 validate_row (
762 p_api_version => p_api_version,
763 p_init_msg_list => OKC_API.G_FALSE,
764 x_return_status => x_return_status,
765 x_msg_count => x_msg_count,
766 x_msg_data => x_msg_data,
767 p_pmlv_tbl => p_pmlv_tbl,
768 px_error_tbl => l_error_tbl);
769 END IF;
770 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
771 EXCEPTION
772 WHEN OKC_API.G_EXCEPTION_ERROR THEN
773 x_return_status := OKC_API.HANDLE_EXCEPTIONS
774 (
775 l_api_name,
776 G_PKG_NAME,
777 'OKC_API.G_RET_STS_ERROR',
778 x_msg_count,
779 x_msg_data,
780 '_PVT'
781 );
782 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
783 x_return_status := OKC_API.HANDLE_EXCEPTIONS
784 (
785 l_api_name,
786 G_PKG_NAME,
787 'OKC_API.G_RET_STS_UNEXP_ERROR',
788 x_msg_count,
789 x_msg_data,
790 '_PVT'
791 );
792 WHEN OTHERS THEN
793 x_return_status := OKC_API.HANDLE_EXCEPTIONS
794 (
795 l_api_name,
796 G_PKG_NAME,
797 'OTHERS',
798 x_msg_count,
799 x_msg_data,
800 '_PVT'
801 );
802 END validate_row;
803
804 ---------------------------------------------------------------------------
805 -- PROCEDURE insert_row
806 ---------------------------------------------------------------------------
807 -----------------------------------------
808 -- insert_row for:OKS_PM_STREAM_LEVELS --
809 -----------------------------------------
810 PROCEDURE insert_row(
811 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
812 x_return_status OUT NOCOPY VARCHAR2,
813 x_msg_count OUT NOCOPY NUMBER,
814 x_msg_data OUT NOCOPY VARCHAR2,
815 p_pml_rec IN pml_rec_type,
816 x_pml_rec OUT NOCOPY pml_rec_type) IS
817
818 l_api_version CONSTANT NUMBER := 1;
819 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
820 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
821 l_pml_rec pml_rec_type := p_pml_rec;
822 l_def_pml_rec pml_rec_type;
823 ---------------------------------------------
824 -- Set_Attributes for:OKS_PM_STREAM_LEVELS --
825 ---------------------------------------------
826 FUNCTION Set_Attributes (
827 p_pml_rec IN pml_rec_type,
828 x_pml_rec OUT NOCOPY pml_rec_type
829 ) RETURN VARCHAR2 IS
830 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
831 BEGIN
832 x_pml_rec := p_pml_rec;
833 RETURN(l_return_status);
834 END Set_Attributes;
835 BEGIN
836 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
837 p_init_msg_list,
838 '_PVT',
839 x_return_status);
840 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
841 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
842 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
843 RAISE OKC_API.G_EXCEPTION_ERROR;
844 END IF;
845 --- Setting item atributes
846 l_return_status := Set_Attributes(
847 p_pml_rec, -- IN
848 l_pml_rec); -- OUT
849 --- If any errors happen abort API
850 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
851 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
852 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
853 RAISE OKC_API.G_EXCEPTION_ERROR;
854 END IF;
855 INSERT INTO OKS_PM_STREAM_LEVELS(
856 id,
857 cle_id,
858 dnz_chr_id,
859 activity_line_id,
860 sequence_number,
861 number_of_occurences,
862 start_date,
863 end_date,
864 frequency,
865 frequency_uom,
866 offset_duration,
867 offset_uom,
868 autoschedule_yn,
869 program_application_id,
870 program_id,
871 program_update_date,
872 object_version_number,
873 request_id,
874 created_by,
875 creation_date,
876 last_updated_by,
877 last_update_date,
878 last_update_login,
879 -- R12 Data Model Changes 4485150 Start
880 orig_system_id1,
881 orig_system_reference1,
882 orig_system_source_code
883 -- R12 Data Model Changes 4485150 End
884 )
885 VALUES (
886 l_pml_rec.id,
887 l_pml_rec.cle_id,
888 l_pml_rec.dnz_chr_id,
889 l_pml_rec.activity_line_id,
890 l_pml_rec.sequence_number,
891 l_pml_rec.number_of_occurences,
892 l_pml_rec.start_date,
893 l_pml_rec.end_date,
894 l_pml_rec.frequency,
895 l_pml_rec.frequency_uom,
896 l_pml_rec.offset_duration,
897 l_pml_rec.offset_uom,
898 l_pml_rec.autoschedule_yn,
899 l_pml_rec.program_application_id,
900 l_pml_rec.program_id,
901 l_pml_rec.program_update_date,
902 l_pml_rec.object_version_number,
903 l_pml_rec.request_id,
904 l_pml_rec.created_by,
905 l_pml_rec.creation_date,
906 l_pml_rec.last_updated_by,
907 l_pml_rec.last_update_date,
908 l_pml_rec.last_update_login,
909 -- R12 Data Model Changes 4485150 Start
910 l_pml_rec.orig_system_id1,
911 l_pml_rec.orig_system_reference1,
912 l_pml_rec.orig_system_source_code
913 -- R12 Data Model Changes 4485150 End
914 );
915 -- Set OUT values
916 x_pml_rec := l_pml_rec;
917 x_return_status := l_return_status;
918 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
919 EXCEPTION
920 WHEN OKC_API.G_EXCEPTION_ERROR THEN
921 x_return_status := OKC_API.HANDLE_EXCEPTIONS
922 (
923 l_api_name,
924 G_PKG_NAME,
925 'OKC_API.G_RET_STS_ERROR',
926 x_msg_count,
927 x_msg_data,
928 '_PVT'
929 );
930 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
931 x_return_status := OKC_API.HANDLE_EXCEPTIONS
932 (
933 l_api_name,
934 G_PKG_NAME,
935 'OKC_API.G_RET_STS_UNEXP_ERROR',
936 x_msg_count,
937 x_msg_data,
938 '_PVT'
939 );
940 WHEN OTHERS THEN
941 x_return_status := OKC_API.HANDLE_EXCEPTIONS
942 (
943 l_api_name,
944 G_PKG_NAME,
945 'OTHERS',
946 x_msg_count,
947 x_msg_data,
948 '_PVT'
949 );
950 END insert_row;
951 --------------------------------------------
952 -- insert_row for :OKS_PM_STREAM_LEVELS_V --
953 --------------------------------------------
954 PROCEDURE insert_row(
955 p_api_version IN NUMBER,
956 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
957 x_return_status OUT NOCOPY VARCHAR2,
958 x_msg_count OUT NOCOPY NUMBER,
959 x_msg_data OUT NOCOPY VARCHAR2,
960 p_pmlv_rec IN pmlv_rec_type,
961 x_pmlv_rec OUT NOCOPY pmlv_rec_type) IS
962
963 l_api_version CONSTANT NUMBER := 1;
964 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
965 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
966 l_pmlv_rec pmlv_rec_type := p_pmlv_rec;
967 l_def_pmlv_rec pmlv_rec_type;
968 l_pml_rec pml_rec_type;
969 lx_pml_rec pml_rec_type;
970 -------------------------------
971 -- FUNCTION fill_who_columns --
972 -------------------------------
973 FUNCTION fill_who_columns (
974 p_pmlv_rec IN pmlv_rec_type
975 ) RETURN pmlv_rec_type IS
976 l_pmlv_rec pmlv_rec_type := p_pmlv_rec;
977 BEGIN
978 l_pmlv_rec.CREATION_DATE := SYSDATE;
979 l_pmlv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
980 l_pmlv_rec.LAST_UPDATE_DATE := l_pmlv_rec.CREATION_DATE;
981 l_pmlv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
982 l_pmlv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
983 RETURN(l_pmlv_rec);
984 END fill_who_columns;
985 -----------------------------------------------
986 -- Set_Attributes for:OKS_PM_STREAM_LEVELS_V --
987 -----------------------------------------------
988 FUNCTION Set_Attributes (
989 p_pmlv_rec IN pmlv_rec_type,
990 x_pmlv_rec OUT NOCOPY pmlv_rec_type
991 ) RETURN VARCHAR2 IS
992 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
993 BEGIN
994 x_pmlv_rec := p_pmlv_rec;
995 x_pmlv_rec.OBJECT_VERSION_NUMBER := 1;
996 RETURN(l_return_status);
997 END Set_Attributes;
998 BEGIN
999 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1000 G_PKG_NAME,
1001 p_init_msg_list,
1002 l_api_version,
1003 p_api_version,
1004 '_PVT',
1005 x_return_status);
1006 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1007 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1008 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1009 RAISE OKC_API.G_EXCEPTION_ERROR;
1010 END IF;
1011 l_pmlv_rec := null_out_defaults(p_pmlv_rec);
1012 -- Set primary key value
1013 l_pmlv_rec.ID := get_seq_id;
1014 -- Setting item attributes
1015 l_return_Status := Set_Attributes(
1016 l_pmlv_rec, -- IN
1017 l_def_pmlv_rec); -- OUT
1018 --- If any errors happen abort API
1019 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1020 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1021 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1022 RAISE OKC_API.G_EXCEPTION_ERROR;
1023 END IF;
1024 l_def_pmlv_rec := fill_who_columns(l_def_pmlv_rec);
1025 --- Validate all non-missing attributes (Item Level Validation)
1026 l_return_status := Validate_Attributes(l_def_pmlv_rec);
1027 --- If any errors happen abort API
1028 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1029 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1030 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1031 RAISE OKC_API.G_EXCEPTION_ERROR;
1032 END IF;
1033 l_return_status := Validate_Record(l_def_pmlv_rec);
1034 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1035 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1036 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1037 RAISE OKC_API.G_EXCEPTION_ERROR;
1038 END IF;
1039 -----------------------------------------
1040 -- Move VIEW record to "Child" records --
1041 -----------------------------------------
1042 migrate(l_def_pmlv_rec, l_pml_rec);
1043 -----------------------------------------------
1044 -- Call the INSERT_ROW for each child record --
1045 -----------------------------------------------
1046 insert_row(
1047 p_init_msg_list,
1048 l_return_status,
1049 x_msg_count,
1050 x_msg_data,
1051 l_pml_rec,
1052 lx_pml_rec
1053 );
1054 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1055 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1056 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1057 RAISE OKC_API.G_EXCEPTION_ERROR;
1058 END IF;
1059 migrate(lx_pml_rec, l_def_pmlv_rec);
1060 -- Set OUT values
1061 x_pmlv_rec := l_def_pmlv_rec;
1062 x_return_status := l_return_status;
1063 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1064 EXCEPTION
1065 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1066 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1067 (
1068 l_api_name,
1069 G_PKG_NAME,
1070 'OKC_API.G_RET_STS_ERROR',
1071 x_msg_count,
1072 x_msg_data,
1073 '_PVT'
1074 );
1075 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1076 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1077 (
1078 l_api_name,
1079 G_PKG_NAME,
1080 'OKC_API.G_RET_STS_UNEXP_ERROR',
1081 x_msg_count,
1082 x_msg_data,
1083 '_PVT'
1084 );
1085 WHEN OTHERS THEN
1086 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1087 (
1088 l_api_name,
1089 G_PKG_NAME,
1090 'OTHERS',
1091 x_msg_count,
1092 x_msg_data,
1093 '_PVT'
1094 );
1095 END insert_row;
1096 ----------------------------------------
1097 -- PL/SQL TBL insert_row for:PMLV_TBL --
1098 ----------------------------------------
1099 PROCEDURE insert_row(
1100 p_api_version IN NUMBER,
1101 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1102 x_return_status OUT NOCOPY VARCHAR2,
1103 x_msg_count OUT NOCOPY NUMBER,
1104 x_msg_data OUT NOCOPY VARCHAR2,
1105 p_pmlv_tbl IN pmlv_tbl_type,
1106 x_pmlv_tbl OUT NOCOPY pmlv_tbl_type,
1107 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1108
1109 l_api_version CONSTANT NUMBER := 1;
1110 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1111 i NUMBER := 0;
1112 BEGIN
1113 OKC_API.init_msg_list(p_init_msg_list);
1114 -- Make sure PL/SQL table has records in it before passing
1115 IF (p_pmlv_tbl.COUNT > 0) THEN
1116 i := p_pmlv_tbl.FIRST;
1117 LOOP
1118 DECLARE
1119 l_error_rec OKC_API.ERROR_REC_TYPE;
1120 BEGIN
1121 l_error_rec.api_name := l_api_name;
1122 l_error_rec.api_package := G_PKG_NAME;
1123 l_error_rec.idx := i;
1124 insert_row (
1125 p_api_version => p_api_version,
1126 p_init_msg_list => OKC_API.G_FALSE,
1127 x_return_status => l_error_rec.error_type,
1128 x_msg_count => l_error_rec.msg_count,
1129 x_msg_data => l_error_rec.msg_data,
1130 p_pmlv_rec => p_pmlv_tbl(i),
1131 x_pmlv_rec => x_pmlv_tbl(i));
1132 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1133 l_error_rec.sqlcode := SQLCODE;
1134 load_error_tbl(l_error_rec, px_error_tbl);
1135 ELSE
1136 x_msg_count := l_error_rec.msg_count;
1137 x_msg_data := l_error_rec.msg_data;
1138 END IF;
1139 EXCEPTION
1140 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1141 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1142 l_error_rec.sqlcode := SQLCODE;
1143 load_error_tbl(l_error_rec, px_error_tbl);
1144 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1145 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1146 l_error_rec.sqlcode := SQLCODE;
1147 load_error_tbl(l_error_rec, px_error_tbl);
1148 WHEN OTHERS THEN
1149 l_error_rec.error_type := 'OTHERS';
1150 l_error_rec.sqlcode := SQLCODE;
1151 load_error_tbl(l_error_rec, px_error_tbl);
1152 END;
1153 EXIT WHEN (i = p_pmlv_tbl.LAST);
1154 i := p_pmlv_tbl.NEXT(i);
1155 END LOOP;
1156 END IF;
1157 -- Loop through the error_tbl to find the error with the highest severity
1158 -- and return it.
1159 x_return_status := find_highest_exception(px_error_tbl);
1160 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1161 EXCEPTION
1162 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1163 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1164 (
1165 l_api_name,
1166 G_PKG_NAME,
1167 'OKC_API.G_RET_STS_ERROR',
1168 x_msg_count,
1169 x_msg_data,
1170 '_PVT'
1171 );
1172 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1173 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1174 (
1175 l_api_name,
1176 G_PKG_NAME,
1177 'OKC_API.G_RET_STS_UNEXP_ERROR',
1178 x_msg_count,
1179 x_msg_data,
1180 '_PVT'
1181 );
1182 WHEN OTHERS THEN
1183 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1184 (
1185 l_api_name,
1186 G_PKG_NAME,
1187 'OTHERS',
1188 x_msg_count,
1189 x_msg_data,
1190 '_PVT'
1191 );
1192 END insert_row;
1193
1194 ----------------------------------------
1195 -- PL/SQL TBL insert_row for:PMLV_TBL --
1196 ----------------------------------------
1197 PROCEDURE insert_row(
1198 p_api_version IN NUMBER,
1199 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1200 x_return_status OUT NOCOPY VARCHAR2,
1201 x_msg_count OUT NOCOPY NUMBER,
1202 x_msg_data OUT NOCOPY VARCHAR2,
1203 p_pmlv_tbl IN pmlv_tbl_type,
1204 x_pmlv_tbl OUT NOCOPY pmlv_tbl_type) IS
1205
1206 l_api_version CONSTANT NUMBER := 1;
1207 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1208 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1209 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1210 BEGIN
1211 OKC_API.init_msg_list(p_init_msg_list);
1212 -- Make sure PL/SQL table has records in it before passing
1213 IF (p_pmlv_tbl.COUNT > 0) THEN
1214 insert_row (
1215 p_api_version => p_api_version,
1216 p_init_msg_list => OKC_API.G_FALSE,
1217 x_return_status => x_return_status,
1218 x_msg_count => x_msg_count,
1219 x_msg_data => x_msg_data,
1220 p_pmlv_tbl => p_pmlv_tbl,
1221 x_pmlv_tbl => x_pmlv_tbl,
1222 px_error_tbl => l_error_tbl);
1223 END IF;
1224 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1225 EXCEPTION
1226 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1227 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1228 (
1229 l_api_name,
1230 G_PKG_NAME,
1231 'OKC_API.G_RET_STS_ERROR',
1232 x_msg_count,
1233 x_msg_data,
1234 '_PVT'
1235 );
1236 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1237 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1238 (
1239 l_api_name,
1240 G_PKG_NAME,
1241 'OKC_API.G_RET_STS_UNEXP_ERROR',
1242 x_msg_count,
1243 x_msg_data,
1244 '_PVT'
1245 );
1246 WHEN OTHERS THEN
1247 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1248 (
1249 l_api_name,
1250 G_PKG_NAME,
1251 'OTHERS',
1252 x_msg_count,
1253 x_msg_data,
1254 '_PVT'
1255 );
1256 END insert_row;
1257
1258 ---------------------------------------------------------------------------
1259 -- PROCEDURE lock_row
1260 ---------------------------------------------------------------------------
1261 ---------------------------------------
1262 -- lock_row for:OKS_PM_STREAM_LEVELS --
1263 ---------------------------------------
1264 PROCEDURE lock_row(
1265 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1266 x_return_status OUT NOCOPY VARCHAR2,
1267 x_msg_count OUT NOCOPY NUMBER,
1268 x_msg_data OUT NOCOPY VARCHAR2,
1269 p_pml_rec IN pml_rec_type) IS
1270
1271 E_Resource_Busy EXCEPTION;
1272 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1273 CURSOR lock_csr (p_pml_rec IN pml_rec_type) IS
1274 SELECT OBJECT_VERSION_NUMBER
1275 FROM OKS_PM_STREAM_LEVELS
1276 WHERE ID = p_pml_rec.id
1277 AND OBJECT_VERSION_NUMBER = p_pml_rec.object_version_number
1278 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1279
1280 CURSOR lchk_csr (p_pml_rec IN pml_rec_type) IS
1281 SELECT OBJECT_VERSION_NUMBER
1282 FROM OKS_PM_STREAM_LEVELS
1283 WHERE ID = p_pml_rec.id;
1284 l_api_version CONSTANT NUMBER := 1;
1285 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1286 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1287 l_object_version_number OKS_PM_STREAM_LEVELS.OBJECT_VERSION_NUMBER%TYPE;
1288 lc_object_version_number OKS_PM_STREAM_LEVELS.OBJECT_VERSION_NUMBER%TYPE;
1289 l_row_notfound BOOLEAN := FALSE;
1290 lc_row_notfound BOOLEAN := FALSE;
1291 BEGIN
1292 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1293 p_init_msg_list,
1294 '_PVT',
1295 x_return_status);
1296 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1297 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1298 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1299 RAISE OKC_API.G_EXCEPTION_ERROR;
1300 END IF;
1301 BEGIN
1302 OPEN lock_csr(p_pml_rec);
1303 FETCH lock_csr INTO l_object_version_number;
1304 l_row_notfound := lock_csr%NOTFOUND;
1305 CLOSE lock_csr;
1306 EXCEPTION
1307 WHEN E_Resource_Busy THEN
1308 IF (lock_csr%ISOPEN) THEN
1309 CLOSE lock_csr;
1310 END IF;
1311 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1312 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1313 END;
1314
1315 IF ( l_row_notfound ) THEN
1316 OPEN lchk_csr(p_pml_rec);
1317 FETCH lchk_csr INTO lc_object_version_number;
1318 lc_row_notfound := lchk_csr%NOTFOUND;
1319 CLOSE lchk_csr;
1320 END IF;
1321 IF (lc_row_notfound) THEN
1322 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1323 RAISE OKC_API.G_EXCEPTION_ERROR;
1324 ELSIF lc_object_version_number > p_pml_rec.object_version_number THEN
1325 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1326 RAISE OKC_API.G_EXCEPTION_ERROR;
1327 ELSIF lc_object_version_number <> p_pml_rec.object_version_number THEN
1328 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1329 RAISE OKC_API.G_EXCEPTION_ERROR;
1330 ELSIF lc_object_version_number = -1 THEN
1331 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1332 RAISE OKC_API.G_EXCEPTION_ERROR;
1333 END IF;
1334 x_return_status := l_return_status;
1335 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1336 EXCEPTION
1337 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1338 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1339 (
1340 l_api_name,
1341 G_PKG_NAME,
1342 'OKC_API.G_RET_STS_ERROR',
1343 x_msg_count,
1344 x_msg_data,
1345 '_PVT'
1346 );
1347 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1348 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1349 (
1350 l_api_name,
1351 G_PKG_NAME,
1352 'OKC_API.G_RET_STS_UNEXP_ERROR',
1353 x_msg_count,
1354 x_msg_data,
1355 '_PVT'
1356 );
1357 WHEN OTHERS THEN
1358 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1359 (
1360 l_api_name,
1361 G_PKG_NAME,
1362 'OTHERS',
1363 x_msg_count,
1364 x_msg_data,
1365 '_PVT'
1366 );
1367 END lock_row;
1368 ------------------------------------------
1369 -- lock_row for: OKS_PM_STREAM_LEVELS_V --
1370 ------------------------------------------
1371 PROCEDURE lock_row(
1372 p_api_version IN NUMBER,
1373 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1374 x_return_status OUT NOCOPY VARCHAR2,
1375 x_msg_count OUT NOCOPY NUMBER,
1376 x_msg_data OUT NOCOPY VARCHAR2,
1377 p_pmlv_rec IN pmlv_rec_type) IS
1378
1379 l_api_version CONSTANT NUMBER := 1;
1380 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1381 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1382 l_pml_rec pml_rec_type;
1383 BEGIN
1384 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1385 G_PKG_NAME,
1386 p_init_msg_list,
1387 l_api_version,
1388 p_api_version,
1389 '_PVT',
1390 x_return_status);
1391 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1392 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1393 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1394 RAISE OKC_API.G_EXCEPTION_ERROR;
1395 END IF;
1396 -----------------------------------------
1397 -- Move VIEW record to "Child" records --
1398 -----------------------------------------
1399 migrate(p_pmlv_rec, l_pml_rec);
1400 ---------------------------------------------
1401 -- Call the LOCK_ROW for each child record --
1402 ---------------------------------------------
1403 lock_row(
1404 p_init_msg_list,
1405 l_return_status,
1406 x_msg_count,
1407 x_msg_data,
1408 l_pml_rec
1409 );
1410 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1411 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1412 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1413 RAISE OKC_API.G_EXCEPTION_ERROR;
1414 END IF;
1415 x_return_status := l_return_status;
1416 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1417 EXCEPTION
1418 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1419 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1420 (
1421 l_api_name,
1422 G_PKG_NAME,
1423 'OKC_API.G_RET_STS_ERROR',
1424 x_msg_count,
1425 x_msg_data,
1426 '_PVT'
1427 );
1428 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1429 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1430 (
1431 l_api_name,
1432 G_PKG_NAME,
1433 'OKC_API.G_RET_STS_UNEXP_ERROR',
1434 x_msg_count,
1435 x_msg_data,
1436 '_PVT'
1437 );
1438 WHEN OTHERS THEN
1439 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1440 (
1441 l_api_name,
1442 G_PKG_NAME,
1443 'OTHERS',
1444 x_msg_count,
1445 x_msg_data,
1446 '_PVT'
1447 );
1448 END lock_row;
1449 --------------------------------------
1450 -- PL/SQL TBL lock_row for:PMLV_TBL --
1451 --------------------------------------
1452 PROCEDURE lock_row(
1453 p_api_version IN NUMBER,
1454 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1455 x_return_status OUT NOCOPY VARCHAR2,
1456 x_msg_count OUT NOCOPY NUMBER,
1457 x_msg_data OUT NOCOPY VARCHAR2,
1458 p_pmlv_tbl IN pmlv_tbl_type,
1459 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1460
1461 l_api_version CONSTANT NUMBER := 1;
1462 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1463 i NUMBER := 0;
1464 BEGIN
1465 OKC_API.init_msg_list(p_init_msg_list);
1466 -- Make sure PL/SQL table has recrods in it before passing
1467 IF (p_pmlv_tbl.COUNT > 0) THEN
1468 i := p_pmlv_tbl.FIRST;
1469 LOOP
1470 DECLARE
1471 l_error_rec OKC_API.ERROR_REC_TYPE;
1472 BEGIN
1473 l_error_rec.api_name := l_api_name;
1474 l_error_rec.api_package := G_PKG_NAME;
1475 l_error_rec.idx := i;
1476 lock_row(
1477 p_api_version => p_api_version,
1478 p_init_msg_list => OKC_API.G_FALSE,
1479 x_return_status => l_error_rec.error_type,
1480 x_msg_count => l_error_rec.msg_count,
1481 x_msg_data => l_error_rec.msg_data,
1482 p_pmlv_rec => p_pmlv_tbl(i));
1483 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1484 l_error_rec.sqlcode := SQLCODE;
1485 load_error_tbl(l_error_rec, px_error_tbl);
1486 ELSE
1487 x_msg_count := l_error_rec.msg_count;
1488 x_msg_data := l_error_rec.msg_data;
1489 END IF;
1490 EXCEPTION
1491 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1492 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1493 l_error_rec.sqlcode := SQLCODE;
1494 load_error_tbl(l_error_rec, px_error_tbl);
1495 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1496 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1497 l_error_rec.sqlcode := SQLCODE;
1498 load_error_tbl(l_error_rec, px_error_tbl);
1499 WHEN OTHERS THEN
1500 l_error_rec.error_type := 'OTHERS';
1501 l_error_rec.sqlcode := SQLCODE;
1502 load_error_tbl(l_error_rec, px_error_tbl);
1503 END;
1504 EXIT WHEN (i = p_pmlv_tbl.LAST);
1505 i := p_pmlv_tbl.NEXT(i);
1506 END LOOP;
1507 END IF;
1508 -- Loop through the error_tbl to find the error with the highest severity
1509 -- and return it.
1510 x_return_status := find_highest_exception(px_error_tbl);
1511 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1512 EXCEPTION
1513 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1514 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1515 (
1516 l_api_name,
1517 G_PKG_NAME,
1518 'OKC_API.G_RET_STS_ERROR',
1519 x_msg_count,
1520 x_msg_data,
1521 '_PVT'
1522 );
1523 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1524 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1525 (
1526 l_api_name,
1527 G_PKG_NAME,
1528 'OKC_API.G_RET_STS_UNEXP_ERROR',
1529 x_msg_count,
1530 x_msg_data,
1531 '_PVT'
1532 );
1533 WHEN OTHERS THEN
1534 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1535 (
1536 l_api_name,
1537 G_PKG_NAME,
1538 'OTHERS',
1539 x_msg_count,
1540 x_msg_data,
1541 '_PVT'
1542 );
1543 END lock_row;
1544 --------------------------------------
1545 -- PL/SQL TBL lock_row for:PMLV_TBL --
1546 --------------------------------------
1547 PROCEDURE lock_row(
1548 p_api_version IN NUMBER,
1549 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 x_msg_count OUT NOCOPY NUMBER,
1552 x_msg_data OUT NOCOPY VARCHAR2,
1553 p_pmlv_tbl IN pmlv_tbl_type) IS
1554
1555 l_api_version CONSTANT NUMBER := 1;
1556 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1557 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1558 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1559 BEGIN
1560 OKC_API.init_msg_list(p_init_msg_list);
1561 -- Make sure PL/SQL table has recrods in it before passing
1562 IF (p_pmlv_tbl.COUNT > 0) THEN
1563 lock_row(
1564 p_api_version => p_api_version,
1565 p_init_msg_list => OKC_API.G_FALSE,
1566 x_return_status => x_return_status,
1567 x_msg_count => x_msg_count,
1568 x_msg_data => x_msg_data,
1569 p_pmlv_tbl => p_pmlv_tbl,
1570 px_error_tbl => l_error_tbl);
1571 END IF;
1572 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1573 EXCEPTION
1574 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1575 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1576 (
1577 l_api_name,
1578 G_PKG_NAME,
1579 'OKC_API.G_RET_STS_ERROR',
1580 x_msg_count,
1581 x_msg_data,
1582 '_PVT'
1583 );
1584 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1585 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1586 (
1587 l_api_name,
1588 G_PKG_NAME,
1589 'OKC_API.G_RET_STS_UNEXP_ERROR',
1590 x_msg_count,
1591 x_msg_data,
1592 '_PVT'
1593 );
1594 WHEN OTHERS THEN
1595 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1596 (
1597 l_api_name,
1598 G_PKG_NAME,
1599 'OTHERS',
1600 x_msg_count,
1601 x_msg_data,
1602 '_PVT'
1603 );
1604 END lock_row;
1605 ---------------------------------------------------------------------------
1606 -- PROCEDURE update_row
1607 ---------------------------------------------------------------------------
1608 -----------------------------------------
1609 -- update_row for:OKS_PM_STREAM_LEVELS --
1610 -----------------------------------------
1611 PROCEDURE update_row(
1612 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1613 x_return_status OUT NOCOPY VARCHAR2,
1614 x_msg_count OUT NOCOPY NUMBER,
1615 x_msg_data OUT NOCOPY VARCHAR2,
1616 p_pml_rec IN pml_rec_type,
1617 x_pml_rec OUT NOCOPY pml_rec_type) IS
1618
1619 l_api_version CONSTANT NUMBER := 1;
1620 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1621 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1622 l_pml_rec pml_rec_type := p_pml_rec;
1623 l_def_pml_rec pml_rec_type;
1624 l_row_notfound BOOLEAN := TRUE;
1625 ----------------------------------
1626 -- FUNCTION populate_new_record --
1627 ----------------------------------
1628 FUNCTION populate_new_record (
1629 p_pml_rec IN pml_rec_type,
1630 x_pml_rec OUT NOCOPY pml_rec_type
1631 ) RETURN VARCHAR2 IS
1632 l_pml_rec pml_rec_type;
1633 l_row_notfound BOOLEAN := TRUE;
1634 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1635 BEGIN
1636 x_pml_rec := p_pml_rec;
1637 -- Get current database values
1638 l_pml_rec := get_rec(p_pml_rec, l_return_status);
1639 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1640 IF (x_pml_rec.id = OKC_API.G_MISS_NUM)
1641 THEN
1642 x_pml_rec.id := l_pml_rec.id;
1643 END IF;
1644 IF (x_pml_rec.cle_id = OKC_API.G_MISS_NUM)
1645 THEN
1646 x_pml_rec.cle_id := l_pml_rec.cle_id;
1647 END IF;
1648 IF (x_pml_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1649 THEN
1650 x_pml_rec.dnz_chr_id := l_pml_rec.dnz_chr_id;
1651 END IF;
1652 IF (x_pml_rec.activity_line_id = OKC_API.G_MISS_NUM)
1653 THEN
1654 x_pml_rec.activity_line_id := l_pml_rec.activity_line_id;
1655 END IF;
1656 IF (x_pml_rec.sequence_number = OKC_API.G_MISS_NUM)
1657 THEN
1658 x_pml_rec.sequence_number := l_pml_rec.sequence_number;
1659 END IF;
1660 IF (x_pml_rec.number_of_occurences = OKC_API.G_MISS_NUM)
1661 THEN
1662 x_pml_rec.number_of_occurences := l_pml_rec.number_of_occurences;
1663 END IF;
1664 IF (x_pml_rec.start_date = OKC_API.G_MISS_DATE)
1665 THEN
1666 x_pml_rec.start_date := l_pml_rec.start_date;
1667 END IF;
1668 IF (x_pml_rec.end_date = OKC_API.G_MISS_DATE)
1669 THEN
1670 x_pml_rec.end_date := l_pml_rec.end_date;
1671 END IF;
1672 IF (x_pml_rec.frequency = OKC_API.G_MISS_NUM)
1673 THEN
1674 x_pml_rec.frequency := l_pml_rec.frequency;
1675 END IF;
1676 IF (x_pml_rec.frequency_uom = OKC_API.G_MISS_CHAR)
1677 THEN
1678 x_pml_rec.frequency_uom := l_pml_rec.frequency_uom;
1679 END IF;
1680 IF (x_pml_rec.offset_duration = OKC_API.G_MISS_NUM)
1681 THEN
1682 x_pml_rec.offset_duration := l_pml_rec.offset_duration;
1683 END IF;
1684 IF (x_pml_rec.offset_uom = OKC_API.G_MISS_CHAR)
1685 THEN
1686 x_pml_rec.offset_uom := l_pml_rec.offset_uom;
1687 END IF;
1688 IF (x_pml_rec.autoschedule_yn = OKC_API.G_MISS_CHAR)
1689 THEN
1690 x_pml_rec.autoschedule_yn := l_pml_rec.autoschedule_yn;
1691 END IF;
1692 IF (x_pml_rec.program_application_id = OKC_API.G_MISS_NUM)
1693 THEN
1694 x_pml_rec.program_application_id := l_pml_rec.program_application_id;
1695 END IF;
1696 IF (x_pml_rec.program_id = OKC_API.G_MISS_NUM)
1697 THEN
1698 x_pml_rec.program_id := l_pml_rec.program_id;
1699 END IF;
1700 IF (x_pml_rec.program_update_date = OKC_API.G_MISS_DATE)
1701 THEN
1702 x_pml_rec.program_update_date := l_pml_rec.program_update_date;
1703 END IF;
1704 IF (x_pml_rec.object_version_number = OKC_API.G_MISS_NUM)
1705 THEN
1706 x_pml_rec.object_version_number := l_pml_rec.object_version_number;
1707 END IF;
1708 IF (x_pml_rec.request_id = OKC_API.G_MISS_NUM)
1709 THEN
1710 x_pml_rec.request_id := l_pml_rec.request_id;
1711 END IF;
1712 IF (x_pml_rec.created_by = OKC_API.G_MISS_NUM)
1713 THEN
1714 x_pml_rec.created_by := l_pml_rec.created_by;
1715 END IF;
1716 IF (x_pml_rec.creation_date = OKC_API.G_MISS_DATE)
1717 THEN
1718 x_pml_rec.creation_date := l_pml_rec.creation_date;
1719 END IF;
1720 IF (x_pml_rec.last_updated_by = OKC_API.G_MISS_NUM)
1721 THEN
1722 x_pml_rec.last_updated_by := l_pml_rec.last_updated_by;
1723 END IF;
1724 IF (x_pml_rec.last_update_date = OKC_API.G_MISS_DATE)
1725 THEN
1726 x_pml_rec.last_update_date := l_pml_rec.last_update_date;
1727 END IF;
1728 IF (x_pml_rec.last_update_login = OKC_API.G_MISS_NUM)
1729 THEN
1730 x_pml_rec.last_update_login := l_pml_rec.last_update_login;
1731 END IF;
1732 -- R12 Data Model Changes 4485150 Start
1733 IF (x_pml_rec.orig_system_id1 = OKC_API.G_MISS_NUM)
1734 THEN
1735 x_pml_rec.orig_system_id1 := l_pml_rec.orig_system_id1;
1736 END IF;
1737 IF (x_pml_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR)
1738 THEN
1739 x_pml_rec.orig_system_reference1 := l_pml_rec.orig_system_reference1;
1740 END IF;
1741 IF (x_pml_rec.orig_system_source_code = OKC_API.G_MISS_CHAR)
1742 THEN
1743 x_pml_rec.orig_system_source_code := l_pml_rec.orig_system_source_code;
1744 END IF;
1745 -- R12 Data Model Changes 4485150 End
1746 END IF;
1747 RETURN(l_return_status);
1748 END populate_new_record;
1749 ---------------------------------------------
1750 -- Set_Attributes for:OKS_PM_STREAM_LEVELS --
1751 ---------------------------------------------
1752 FUNCTION Set_Attributes (
1753 p_pml_rec IN pml_rec_type,
1754 x_pml_rec OUT NOCOPY pml_rec_type
1755 ) RETURN VARCHAR2 IS
1756 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1757 BEGIN
1758 x_pml_rec := p_pml_rec;
1759 x_pml_rec.OBJECT_VERSION_NUMBER := p_pml_rec.OBJECT_VERSION_NUMBER + 1;
1760 RETURN(l_return_status);
1761 END Set_Attributes;
1762 BEGIN
1763 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1764 p_init_msg_list,
1765 '_PVT',
1766 x_return_status);
1767 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1768 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1769 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1770 RAISE OKC_API.G_EXCEPTION_ERROR;
1771 END IF;
1772 --- Setting item attributes
1773 l_return_status := Set_Attributes(
1774 p_pml_rec, -- IN
1775 l_pml_rec); -- OUT
1776 --- If any errors happen abort API
1777 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1778 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1779 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1780 RAISE OKC_API.G_EXCEPTION_ERROR;
1781 END IF;
1782 l_return_status := populate_new_record(l_pml_rec, l_def_pml_rec);
1783 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1784 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1785 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1786 RAISE OKC_API.G_EXCEPTION_ERROR;
1787 END IF;
1788 UPDATE OKS_PM_STREAM_LEVELS
1789 SET CLE_ID = l_def_pml_rec.cle_id,
1790 DNZ_CHR_ID = l_def_pml_rec.dnz_chr_id,
1791 ACTIVITY_LINE_ID = l_def_pml_rec.activity_line_id,
1792 SEQUENCE_NUMBER = l_def_pml_rec.sequence_number,
1793 NUMBER_OF_OCCURENCES = l_def_pml_rec.number_of_occurences,
1794 START_DATE = l_def_pml_rec.start_date,
1795 END_DATE = l_def_pml_rec.end_date,
1796 FREQUENCY = l_def_pml_rec.frequency,
1797 FREQUENCY_UOM = l_def_pml_rec.frequency_uom,
1798 OFFSET_DURATION = l_def_pml_rec.offset_duration,
1799 OFFSET_UOM = l_def_pml_rec.offset_uom,
1800 AUTOSCHEDULE_YN = l_def_pml_rec.autoschedule_yn,
1801 PROGRAM_APPLICATION_ID = l_def_pml_rec.program_application_id,
1802 PROGRAM_ID = l_def_pml_rec.program_id,
1803 PROGRAM_UPDATE_DATE = l_def_pml_rec.program_update_date,
1804 OBJECT_VERSION_NUMBER = l_def_pml_rec.object_version_number,
1805 REQUEST_ID = l_def_pml_rec.request_id,
1806 CREATED_BY = l_def_pml_rec.created_by,
1807 CREATION_DATE = l_def_pml_rec.creation_date,
1808 LAST_UPDATED_BY = l_def_pml_rec.last_updated_by,
1809 LAST_UPDATE_DATE = l_def_pml_rec.last_update_date,
1810 LAST_UPDATE_LOGIN = l_def_pml_rec.last_update_login,
1811 -- R12 Data Model Changes 4485150 Start
1812 ORIG_SYSTEM_ID1 = l_def_pml_rec.orig_system_id1,
1813 ORIG_SYSTEM_REFERENCE1 = l_def_pml_rec.orig_system_reference1,
1814 ORIG_SYSTEM_SOURCE_CODE = l_def_pml_rec.orig_system_source_code
1815 -- R12 Data Model Changes 4485150 End
1816 WHERE ID = l_def_pml_rec.id;
1817
1818 x_pml_rec := l_pml_rec;
1819 x_return_status := l_return_status;
1820 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1821 EXCEPTION
1822 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1823 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1824 (
1825 l_api_name,
1826 G_PKG_NAME,
1827 'OKC_API.G_RET_STS_ERROR',
1828 x_msg_count,
1829 x_msg_data,
1830 '_PVT'
1831 );
1832 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1833 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1834 (
1835 l_api_name,
1836 G_PKG_NAME,
1837 'OKC_API.G_RET_STS_UNEXP_ERROR',
1838 x_msg_count,
1839 x_msg_data,
1840 '_PVT'
1841 );
1842 WHEN OTHERS THEN
1843 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1844 (
1845 l_api_name,
1846 G_PKG_NAME,
1847 'OTHERS',
1848 x_msg_count,
1849 x_msg_data,
1850 '_PVT'
1851 );
1852 END update_row;
1853 -------------------------------------------
1854 -- update_row for:OKS_PM_STREAM_LEVELS_V --
1855 -------------------------------------------
1856 PROCEDURE update_row(
1857 p_api_version IN NUMBER,
1858 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1859 x_return_status OUT NOCOPY VARCHAR2,
1860 x_msg_count OUT NOCOPY NUMBER,
1861 x_msg_data OUT NOCOPY VARCHAR2,
1862 p_pmlv_rec IN pmlv_rec_type,
1863 x_pmlv_rec OUT NOCOPY pmlv_rec_type) IS
1864
1865 l_api_version CONSTANT NUMBER := 1;
1866 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1867 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1868 l_pmlv_rec pmlv_rec_type := p_pmlv_rec;
1869 l_def_pmlv_rec pmlv_rec_type;
1870 l_db_pmlv_rec pmlv_rec_type;
1871 l_pml_rec pml_rec_type;
1872 lx_pml_rec pml_rec_type;
1873 -------------------------------
1874 -- FUNCTION fill_who_columns --
1875 -------------------------------
1876 FUNCTION fill_who_columns (
1877 p_pmlv_rec IN pmlv_rec_type
1878 ) RETURN pmlv_rec_type IS
1879 l_pmlv_rec pmlv_rec_type := p_pmlv_rec;
1880 BEGIN
1881 l_pmlv_rec.LAST_UPDATE_DATE := SYSDATE;
1882 l_pmlv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1883 l_pmlv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1884 RETURN(l_pmlv_rec);
1885 END fill_who_columns;
1886 ----------------------------------
1887 -- FUNCTION populate_new_record --
1888 ----------------------------------
1889 FUNCTION populate_new_record (
1890 p_pmlv_rec IN pmlv_rec_type,
1891 x_pmlv_rec OUT NOCOPY pmlv_rec_type
1892 ) RETURN VARCHAR2 IS
1893 l_row_notfound BOOLEAN := TRUE;
1894 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1895 BEGIN
1896 x_pmlv_rec := p_pmlv_rec;
1897 -- Get current database values
1898 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
1899 -- so it may be verified through LOCK_ROW.
1900 l_db_pmlv_rec := get_rec(p_pmlv_rec, l_return_status);
1901 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1902 IF (x_pmlv_rec.id = OKC_API.G_MISS_NUM)
1903 THEN
1904 x_pmlv_rec.id := l_db_pmlv_rec.id;
1905 END IF;
1906 IF (x_pmlv_rec.cle_id = OKC_API.G_MISS_NUM)
1907 THEN
1908 x_pmlv_rec.cle_id := l_db_pmlv_rec.cle_id;
1909 END IF;
1910 IF (x_pmlv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1911 THEN
1912 x_pmlv_rec.dnz_chr_id := l_db_pmlv_rec.dnz_chr_id;
1913 END IF;
1914 IF (x_pmlv_rec.activity_line_id = OKC_API.G_MISS_NUM)
1915 THEN
1916 x_pmlv_rec.activity_line_id := l_db_pmlv_rec.activity_line_id;
1917 END IF;
1918 IF (x_pmlv_rec.sequence_number = OKC_API.G_MISS_NUM)
1919 THEN
1920 x_pmlv_rec.sequence_number := l_db_pmlv_rec.sequence_number;
1921 END IF;
1922 IF (x_pmlv_rec.number_of_occurences = OKC_API.G_MISS_NUM)
1923 THEN
1924 x_pmlv_rec.number_of_occurences := l_db_pmlv_rec.number_of_occurences;
1925 END IF;
1926 IF (x_pmlv_rec.start_date = OKC_API.G_MISS_DATE)
1927 THEN
1928 x_pmlv_rec.start_date := l_db_pmlv_rec.start_date;
1929 END IF;
1930 IF (x_pmlv_rec.end_date = OKC_API.G_MISS_DATE)
1931 THEN
1932 x_pmlv_rec.end_date := l_db_pmlv_rec.end_date;
1933 END IF;
1934 IF (x_pmlv_rec.frequency = OKC_API.G_MISS_NUM)
1935 THEN
1936 x_pmlv_rec.frequency := l_db_pmlv_rec.frequency;
1937 END IF;
1938 IF (x_pmlv_rec.frequency_uom = OKC_API.G_MISS_CHAR)
1939 THEN
1940 x_pmlv_rec.frequency_uom := l_db_pmlv_rec.frequency_uom;
1941 END IF;
1942 IF (x_pmlv_rec.offset_duration = OKC_API.G_MISS_NUM)
1943 THEN
1944 x_pmlv_rec.offset_duration := l_db_pmlv_rec.offset_duration;
1945 END IF;
1946 IF (x_pmlv_rec.offset_uom = OKC_API.G_MISS_CHAR)
1947 THEN
1948 x_pmlv_rec.offset_uom := l_db_pmlv_rec.offset_uom;
1949 END IF;
1950 IF (x_pmlv_rec.autoschedule_yn = OKC_API.G_MISS_CHAR)
1951 THEN
1952 x_pmlv_rec.autoschedule_yn := l_db_pmlv_rec.autoschedule_yn;
1953 END IF;
1954 IF (x_pmlv_rec.program_application_id = OKC_API.G_MISS_NUM)
1955 THEN
1956 x_pmlv_rec.program_application_id := l_db_pmlv_rec.program_application_id;
1957 END IF;
1958 IF (x_pmlv_rec.program_id = OKC_API.G_MISS_NUM)
1959 THEN
1960 x_pmlv_rec.program_id := l_db_pmlv_rec.program_id;
1961 END IF;
1962 IF (x_pmlv_rec.program_update_date = OKC_API.G_MISS_DATE)
1963 THEN
1964 x_pmlv_rec.program_update_date := l_db_pmlv_rec.program_update_date;
1965 END IF;
1966 IF (x_pmlv_rec.security_group_id = OKC_API.G_MISS_NUM)
1967 THEN
1968 x_pmlv_rec.security_group_id := l_db_pmlv_rec.security_group_id;
1969 END IF;
1970 IF (x_pmlv_rec.request_id = OKC_API.G_MISS_NUM)
1971 THEN
1972 x_pmlv_rec.request_id := l_db_pmlv_rec.request_id;
1973 END IF;
1974 IF (x_pmlv_rec.created_by = OKC_API.G_MISS_NUM)
1975 THEN
1976 x_pmlv_rec.created_by := l_db_pmlv_rec.created_by;
1977 END IF;
1978 IF (x_pmlv_rec.creation_date = OKC_API.G_MISS_DATE)
1979 THEN
1980 x_pmlv_rec.creation_date := l_db_pmlv_rec.creation_date;
1981 END IF;
1982 IF (x_pmlv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1983 THEN
1984 x_pmlv_rec.last_updated_by := l_db_pmlv_rec.last_updated_by;
1985 END IF;
1986 IF (x_pmlv_rec.last_update_date = OKC_API.G_MISS_DATE)
1987 THEN
1988 x_pmlv_rec.last_update_date := l_db_pmlv_rec.last_update_date;
1989 END IF;
1990 IF (x_pmlv_rec.last_update_login = OKC_API.G_MISS_NUM)
1991 THEN
1992 x_pmlv_rec.last_update_login := l_db_pmlv_rec.last_update_login;
1993 END IF;
1994 -- R12 Data Model Changes 4485150 Start
1995 IF (x_pmlv_rec.orig_system_id1 = OKC_API.G_MISS_NUM)
1996 THEN
1997 x_pmlv_rec.orig_system_id1 := l_db_pmlv_rec.orig_system_id1;
1998 END IF;
1999 IF (x_pmlv_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR)
2000 THEN
2001 x_pmlv_rec.orig_system_reference1 := l_db_pmlv_rec.orig_system_reference1;
2002 END IF;
2003 IF (x_pmlv_rec.orig_system_source_code = OKC_API.G_MISS_CHAR)
2004 THEN
2005 x_pmlv_rec.orig_system_source_code := l_db_pmlv_rec.orig_system_source_code;
2006 END IF;
2007 -- R12 Data Model Changes 4485150 End
2008
2009 END IF;
2010 RETURN(l_return_status);
2011 END populate_new_record;
2012 -----------------------------------------------
2013 -- Set_Attributes for:OKS_PM_STREAM_LEVELS_V --
2014 -----------------------------------------------
2015 FUNCTION Set_Attributes (
2016 p_pmlv_rec IN pmlv_rec_type,
2017 x_pmlv_rec OUT NOCOPY pmlv_rec_type
2018 ) RETURN VARCHAR2 IS
2019 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2020 BEGIN
2021 x_pmlv_rec := p_pmlv_rec;
2022 RETURN(l_return_status);
2023 END Set_Attributes;
2024 BEGIN
2025 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2026 G_PKG_NAME,
2027 p_init_msg_list,
2028 l_api_version,
2029 p_api_version,
2030 '_PVT',
2031 x_return_status);
2032 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2033 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2034 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2035 RAISE OKC_API.G_EXCEPTION_ERROR;
2036 END IF;
2037 --- Setting item attributes
2038 l_return_status := Set_Attributes(
2039 p_pmlv_rec, -- IN
2040 x_pmlv_rec); -- OUT
2041 --- If any errors happen abort API
2042 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2043 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2044 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2045 RAISE OKC_API.G_EXCEPTION_ERROR;
2046 END IF;
2047 l_return_status := populate_new_record(l_pmlv_rec, l_def_pmlv_rec);
2048 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2049 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2050 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2051 RAISE OKC_API.G_EXCEPTION_ERROR;
2052 END IF;
2053 l_def_pmlv_rec := fill_who_columns(l_def_pmlv_rec);
2054 --- Validate all non-missing attributes (Item Level Validation)
2055 l_return_status := Validate_Attributes(l_def_pmlv_rec);
2056 --- If any errors happen abort API
2057 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2058 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2059 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2060 RAISE OKC_API.G_EXCEPTION_ERROR;
2061 END IF;
2062 l_return_status := Validate_Record(l_def_pmlv_rec, l_db_pmlv_rec);
2063 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2064 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2065 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2066 RAISE OKC_API.G_EXCEPTION_ERROR;
2067 END IF;
2068
2069 -- Lock the Record
2070 lock_row(
2071 p_api_version => p_api_version,
2072 p_init_msg_list => p_init_msg_list,
2073 x_return_status => l_return_status,
2074 x_msg_count => x_msg_count,
2075 x_msg_data => x_msg_data,
2076 p_pmlv_rec => p_pmlv_rec);
2077 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2078 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2079 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2080 RAISE OKC_API.G_EXCEPTION_ERROR;
2081 END IF;
2082
2083 -----------------------------------------
2084 -- Move VIEW record to "Child" records --
2085 -----------------------------------------
2086 migrate(l_def_pmlv_rec, l_pml_rec);
2087 -----------------------------------------------
2088 -- Call the UPDATE_ROW for each child record --
2089 -----------------------------------------------
2090 update_row(
2091 p_init_msg_list,
2092 l_return_status,
2093 x_msg_count,
2094 x_msg_data,
2095 l_pml_rec,
2096 lx_pml_rec
2097 );
2098 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2099 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2100 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2101 RAISE OKC_API.G_EXCEPTION_ERROR;
2102 END IF;
2103 migrate(lx_pml_rec, l_def_pmlv_rec);
2104 x_pmlv_rec := l_def_pmlv_rec;
2105 x_return_status := l_return_status;
2106 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2107 EXCEPTION
2108 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2109 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2110 (
2111 l_api_name,
2112 G_PKG_NAME,
2113 'OKC_API.G_RET_STS_ERROR',
2114 x_msg_count,
2115 x_msg_data,
2116 '_PVT'
2117 );
2118 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2119 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2120 (
2121 l_api_name,
2122 G_PKG_NAME,
2123 'OKC_API.G_RET_STS_UNEXP_ERROR',
2124 x_msg_count,
2125 x_msg_data,
2126 '_PVT'
2127 );
2128 WHEN OTHERS THEN
2129 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2130 (
2131 l_api_name,
2132 G_PKG_NAME,
2133 'OTHERS',
2134 x_msg_count,
2135 x_msg_data,
2136 '_PVT'
2137 );
2138 END update_row;
2139 ----------------------------------------
2140 -- PL/SQL TBL update_row for:pmlv_tbl --
2141 ----------------------------------------
2142 PROCEDURE update_row(
2143 p_api_version IN NUMBER,
2144 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2145 x_return_status OUT NOCOPY VARCHAR2,
2146 x_msg_count OUT NOCOPY NUMBER,
2147 x_msg_data OUT NOCOPY VARCHAR2,
2148 p_pmlv_tbl IN pmlv_tbl_type,
2149 x_pmlv_tbl OUT NOCOPY pmlv_tbl_type,
2150 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2151
2152 l_api_version CONSTANT NUMBER := 1;
2153 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2154 i NUMBER := 0;
2155 BEGIN
2156 OKC_API.init_msg_list(p_init_msg_list);
2157 -- Make sure PL/SQL table has records in it before passing
2158 IF (p_pmlv_tbl.COUNT > 0) THEN
2159 i := p_pmlv_tbl.FIRST;
2160 LOOP
2161 DECLARE
2162 l_error_rec OKC_API.ERROR_REC_TYPE;
2163 BEGIN
2164 l_error_rec.api_name := l_api_name;
2165 l_error_rec.api_package := G_PKG_NAME;
2166 l_error_rec.idx := i;
2167 update_row (
2168 p_api_version => p_api_version,
2169 p_init_msg_list => OKC_API.G_FALSE,
2170 x_return_status => l_error_rec.error_type,
2171 x_msg_count => l_error_rec.msg_count,
2172 x_msg_data => l_error_rec.msg_data,
2173 p_pmlv_rec => p_pmlv_tbl(i),
2174 x_pmlv_rec => x_pmlv_tbl(i));
2175 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2176 l_error_rec.sqlcode := SQLCODE;
2177 load_error_tbl(l_error_rec, px_error_tbl);
2178 ELSE
2179 x_msg_count := l_error_rec.msg_count;
2180 x_msg_data := l_error_rec.msg_data;
2181 END IF;
2182 EXCEPTION
2183 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2184 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2185 l_error_rec.sqlcode := SQLCODE;
2186 load_error_tbl(l_error_rec, px_error_tbl);
2187 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2188 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2189 l_error_rec.sqlcode := SQLCODE;
2190 load_error_tbl(l_error_rec, px_error_tbl);
2191 WHEN OTHERS THEN
2192 l_error_rec.error_type := 'OTHERS';
2193 l_error_rec.sqlcode := SQLCODE;
2194 load_error_tbl(l_error_rec, px_error_tbl);
2195 END;
2196 EXIT WHEN (i = p_pmlv_tbl.LAST);
2197 i := p_pmlv_tbl.NEXT(i);
2198 END LOOP;
2199 END IF;
2200 -- Loop through the error_tbl to find the error with the highest severity
2201 -- and return it.
2202 x_return_status := find_highest_exception(px_error_tbl);
2203 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2204 EXCEPTION
2205 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2206 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2207 (
2208 l_api_name,
2209 G_PKG_NAME,
2210 'OKC_API.G_RET_STS_ERROR',
2211 x_msg_count,
2212 x_msg_data,
2213 '_PVT'
2214 );
2215 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2216 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2217 (
2218 l_api_name,
2219 G_PKG_NAME,
2220 'OKC_API.G_RET_STS_UNEXP_ERROR',
2221 x_msg_count,
2222 x_msg_data,
2223 '_PVT'
2224 );
2225 WHEN OTHERS THEN
2226 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2227 (
2228 l_api_name,
2229 G_PKG_NAME,
2230 'OTHERS',
2231 x_msg_count,
2232 x_msg_data,
2233 '_PVT'
2234 );
2235 END update_row;
2236
2237 ----------------------------------------
2238 -- PL/SQL TBL update_row for:PMLV_TBL --
2239 ----------------------------------------
2240 PROCEDURE update_row(
2241 p_api_version IN NUMBER,
2242 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2243 x_return_status OUT NOCOPY VARCHAR2,
2244 x_msg_count OUT NOCOPY NUMBER,
2245 x_msg_data OUT NOCOPY VARCHAR2,
2246 p_pmlv_tbl IN pmlv_tbl_type,
2247 x_pmlv_tbl OUT NOCOPY pmlv_tbl_type) IS
2248
2249 l_api_version CONSTANT NUMBER := 1;
2250 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2251 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2252 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2253 BEGIN
2254 OKC_API.init_msg_list(p_init_msg_list);
2255 -- Make sure PL/SQL table has records in it before passing
2256 IF (p_pmlv_tbl.COUNT > 0) THEN
2257 update_row (
2258 p_api_version => p_api_version,
2259 p_init_msg_list => OKC_API.G_FALSE,
2260 x_return_status => x_return_status,
2261 x_msg_count => x_msg_count,
2262 x_msg_data => x_msg_data,
2263 p_pmlv_tbl => p_pmlv_tbl,
2264 x_pmlv_tbl => x_pmlv_tbl,
2265 px_error_tbl => l_error_tbl);
2266 END IF;
2267 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2268 EXCEPTION
2269 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2270 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2271 (
2272 l_api_name,
2273 G_PKG_NAME,
2274 'OKC_API.G_RET_STS_ERROR',
2275 x_msg_count,
2276 x_msg_data,
2277 '_PVT'
2278 );
2279 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2280 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2281 (
2282 l_api_name,
2283 G_PKG_NAME,
2284 'OKC_API.G_RET_STS_UNEXP_ERROR',
2285 x_msg_count,
2286 x_msg_data,
2287 '_PVT'
2288 );
2289 WHEN OTHERS THEN
2290 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2291 (
2292 l_api_name,
2293 G_PKG_NAME,
2294 'OTHERS',
2295 x_msg_count,
2296 x_msg_data,
2297 '_PVT'
2298 );
2299 END update_row;
2300
2301 ---------------------------------------------------------------------------
2302 -- PROCEDURE delete_row
2303 ---------------------------------------------------------------------------
2304 -----------------------------------------
2305 -- delete_row for:OKS_PM_STREAM_LEVELS --
2306 -----------------------------------------
2307 PROCEDURE delete_row(
2308 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2309 x_return_status OUT NOCOPY VARCHAR2,
2310 x_msg_count OUT NOCOPY NUMBER,
2311 x_msg_data OUT NOCOPY VARCHAR2,
2312 p_pml_rec IN pml_rec_type) IS
2313
2314 l_api_version CONSTANT NUMBER := 1;
2315 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2316 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2317 l_pml_rec pml_rec_type := p_pml_rec;
2318 l_row_notfound BOOLEAN := TRUE;
2319 BEGIN
2320 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2321 p_init_msg_list,
2322 '_PVT',
2323 x_return_status);
2324 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2325 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2326 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2327 RAISE OKC_API.G_EXCEPTION_ERROR;
2328 END IF;
2329
2330 DELETE FROM OKS_PM_STREAM_LEVELS
2331 WHERE ID = p_pml_rec.id;
2332
2333 x_return_status := l_return_status;
2334 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2335 EXCEPTION
2336 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2337 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2338 (
2339 l_api_name,
2340 G_PKG_NAME,
2341 'OKC_API.G_RET_STS_ERROR',
2342 x_msg_count,
2343 x_msg_data,
2344 '_PVT'
2345 );
2346 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2347 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2348 (
2349 l_api_name,
2350 G_PKG_NAME,
2351 'OKC_API.G_RET_STS_UNEXP_ERROR',
2352 x_msg_count,
2353 x_msg_data,
2354 '_PVT'
2355 );
2356 WHEN OTHERS THEN
2357 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2358 (
2359 l_api_name,
2360 G_PKG_NAME,
2361 'OTHERS',
2362 x_msg_count,
2363 x_msg_data,
2364 '_PVT'
2365 );
2366 END delete_row;
2367 -------------------------------------------
2368 -- delete_row for:OKS_PM_STREAM_LEVELS_V --
2369 -------------------------------------------
2370 PROCEDURE delete_row(
2371 p_api_version IN NUMBER,
2372 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2373 x_return_status OUT NOCOPY VARCHAR2,
2374 x_msg_count OUT NOCOPY NUMBER,
2375 x_msg_data OUT NOCOPY VARCHAR2,
2376 p_pmlv_rec IN pmlv_rec_type) IS
2377
2378 l_api_version CONSTANT NUMBER := 1;
2379 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2380 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2381 l_pmlv_rec pmlv_rec_type := p_pmlv_rec;
2382 l_pml_rec pml_rec_type;
2383 BEGIN
2384 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2385 G_PKG_NAME,
2386 p_init_msg_list,
2387 l_api_version,
2388 p_api_version,
2389 '_PVT',
2390 x_return_status);
2391 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2392 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2393 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2394 RAISE OKC_API.G_EXCEPTION_ERROR;
2395 END IF;
2396 -----------------------------------------
2397 -- Move VIEW record to "Child" records --
2398 -----------------------------------------
2399 migrate(l_pmlv_rec, l_pml_rec);
2400 -----------------------------------------------
2401 -- Call the DELETE_ROW for each child record --
2402 -----------------------------------------------
2403 delete_row(
2404 p_init_msg_list,
2405 l_return_status,
2406 x_msg_count,
2407 x_msg_data,
2408 l_pml_rec
2409 );
2410 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2411 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2412 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2413 RAISE OKC_API.G_EXCEPTION_ERROR;
2414 END IF;
2415 x_return_status := l_return_status;
2416 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2417 EXCEPTION
2418 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2419 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2420 (
2421 l_api_name,
2422 G_PKG_NAME,
2423 'OKC_API.G_RET_STS_ERROR',
2424 x_msg_count,
2425 x_msg_data,
2426 '_PVT'
2427 );
2428 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2429 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2430 (
2431 l_api_name,
2432 G_PKG_NAME,
2433 'OKC_API.G_RET_STS_UNEXP_ERROR',
2434 x_msg_count,
2435 x_msg_data,
2436 '_PVT'
2437 );
2438 WHEN OTHERS THEN
2439 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2440 (
2441 l_api_name,
2442 G_PKG_NAME,
2443 'OTHERS',
2444 x_msg_count,
2445 x_msg_data,
2446 '_PVT'
2447 );
2448 END delete_row;
2449 ------------------------------------------------------
2450 -- PL/SQL TBL delete_row for:OKS_PM_STREAM_LEVELS_V --
2451 ------------------------------------------------------
2452 PROCEDURE delete_row(
2453 p_api_version IN NUMBER,
2454 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2455 x_return_status OUT NOCOPY VARCHAR2,
2456 x_msg_count OUT NOCOPY NUMBER,
2457 x_msg_data OUT NOCOPY VARCHAR2,
2458 p_pmlv_tbl IN pmlv_tbl_type,
2459 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2460
2461 l_api_version CONSTANT NUMBER := 1;
2462 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2463 i NUMBER := 0;
2464 BEGIN
2465 OKC_API.init_msg_list(p_init_msg_list);
2466 -- Make sure PL/SQL table has records in it before passing
2467 IF (p_pmlv_tbl.COUNT > 0) THEN
2468 i := p_pmlv_tbl.FIRST;
2469 LOOP
2470 DECLARE
2471 l_error_rec OKC_API.ERROR_REC_TYPE;
2472 BEGIN
2473 l_error_rec.api_name := l_api_name;
2474 l_error_rec.api_package := G_PKG_NAME;
2475 l_error_rec.idx := i;
2476 delete_row (
2477 p_api_version => p_api_version,
2478 p_init_msg_list => OKC_API.G_FALSE,
2479 x_return_status => l_error_rec.error_type,
2480 x_msg_count => l_error_rec.msg_count,
2481 x_msg_data => l_error_rec.msg_data,
2482 p_pmlv_rec => p_pmlv_tbl(i));
2483 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2484 l_error_rec.sqlcode := SQLCODE;
2485 load_error_tbl(l_error_rec, px_error_tbl);
2486 ELSE
2487 x_msg_count := l_error_rec.msg_count;
2488 x_msg_data := l_error_rec.msg_data;
2489 END IF;
2490 EXCEPTION
2491 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2492 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2493 l_error_rec.sqlcode := SQLCODE;
2494 load_error_tbl(l_error_rec, px_error_tbl);
2495 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2496 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2497 l_error_rec.sqlcode := SQLCODE;
2498 load_error_tbl(l_error_rec, px_error_tbl);
2499 WHEN OTHERS THEN
2500 l_error_rec.error_type := 'OTHERS';
2501 l_error_rec.sqlcode := SQLCODE;
2502 load_error_tbl(l_error_rec, px_error_tbl);
2503 END;
2504 EXIT WHEN (i = p_pmlv_tbl.LAST);
2505 i := p_pmlv_tbl.NEXT(i);
2506 END LOOP;
2507 END IF;
2508 -- Loop through the error_tbl to find the error with the highest severity
2509 -- and return it.
2510 x_return_status := find_highest_exception(px_error_tbl);
2511 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2512 EXCEPTION
2513 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2514 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2515 (
2516 l_api_name,
2517 G_PKG_NAME,
2518 'OKC_API.G_RET_STS_ERROR',
2519 x_msg_count,
2520 x_msg_data,
2521 '_PVT'
2522 );
2523 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2524 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2525 (
2526 l_api_name,
2527 G_PKG_NAME,
2528 'OKC_API.G_RET_STS_UNEXP_ERROR',
2529 x_msg_count,
2530 x_msg_data,
2531 '_PVT'
2532 );
2533 WHEN OTHERS THEN
2534 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2535 (
2536 l_api_name,
2537 G_PKG_NAME,
2538 'OTHERS',
2539 x_msg_count,
2540 x_msg_data,
2541 '_PVT'
2542 );
2543 END delete_row;
2544
2545 ------------------------------------------------------
2546 -- PL/SQL TBL delete_row for:OKS_PM_STREAM_LEVELS_V --
2547 ------------------------------------------------------
2548 PROCEDURE delete_row(
2549 p_api_version IN NUMBER,
2550 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2551 x_return_status OUT NOCOPY VARCHAR2,
2552 x_msg_count OUT NOCOPY NUMBER,
2553 x_msg_data OUT NOCOPY VARCHAR2,
2554 p_pmlv_tbl IN pmlv_tbl_type) IS
2555
2556 l_api_version CONSTANT NUMBER := 1;
2557 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2558 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2559 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2560 BEGIN
2561 OKC_API.init_msg_list(p_init_msg_list);
2562 -- Make sure PL/SQL table has records in it before passing
2563 IF (p_pmlv_tbl.COUNT > 0) THEN
2564 delete_row (
2565 p_api_version => p_api_version,
2566 p_init_msg_list => OKC_API.G_FALSE,
2567 x_return_status => x_return_status,
2568 x_msg_count => x_msg_count,
2569 x_msg_data => x_msg_data,
2570 p_pmlv_tbl => p_pmlv_tbl,
2571 px_error_tbl => l_error_tbl);
2572 END IF;
2573 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2574 EXCEPTION
2575 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2576 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2577 (
2578 l_api_name,
2579 G_PKG_NAME,
2580 'OKC_API.G_RET_STS_ERROR',
2581 x_msg_count,
2582 x_msg_data,
2583 '_PVT'
2584 );
2585 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2586 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2587 (
2588 l_api_name,
2589 G_PKG_NAME,
2590 'OKC_API.G_RET_STS_UNEXP_ERROR',
2591 x_msg_count,
2592 x_msg_data,
2593 '_PVT'
2594 );
2595 WHEN OTHERS THEN
2596 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2597 (
2598 l_api_name,
2599 G_PKG_NAME,
2600 'OTHERS',
2601 x_msg_count,
2602 x_msg_data,
2603 '_PVT'
2604 );
2605 END delete_row;
2606 FUNCTION Create_Version(
2607 p_id IN NUMBER,
2608 p_major_version IN NUMBER) RETURN VARCHAR2 IS
2609
2610 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2611
2612 BEGIN
2613
2614
2615 INSERT INTO OKS_PM_STREAM_LEVELS_H(
2616 id,
2617 cle_id,
2618 dnz_chr_id,
2619 activity_line_id,
2620 sequence_number,
2621 number_of_occurences,
2622 start_date,
2623 end_date,
2624 frequency,
2625 frequency_uom,
2626 offset_duration,
2627 offset_uom,
2628 autoschedule_yn,
2629 program_application_id,
2630 program_id,
2631 program_update_date,
2632 object_version_number,
2633 request_id,
2634 created_by,
2635 creation_date,
2636 last_updated_by,
2637 last_update_date,
2638 last_update_login,
2639 MAJOR_VERSION
2640 )
2641 SELECT
2642 id,
2643 cle_id,
2644 dnz_chr_id,
2645 activity_line_id,
2646 sequence_number,
2647 number_of_occurences,
2648 start_date,
2649 end_date,
2650 frequency,
2651 frequency_uom,
2652 offset_duration,
2653 offset_uom,
2654 autoschedule_yn,
2655 program_application_id,
2656 program_id,
2657 program_update_date,
2658 object_version_number,
2659 request_id,
2660 created_by,
2661 creation_date,
2662 last_updated_by,
2663 last_update_date,
2664 last_update_login,
2665 p_major_version
2666 FROM OKS_PM_STREAM_LEVELS
2667 WHERE DNZ_CHR_ID = P_Id;
2668
2669 RETURN l_return_status;
2670
2671
2672 EXCEPTION
2673
2674
2675 WHEN OTHERS THEN
2676
2677
2678 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2679 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2680 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2681 p_token1_value => sqlcode,
2682 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2683 p_token2_value => sqlerrm);
2684
2685 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2686
2687 return l_return_status;
2688 END Create_Version;
2689
2690 FUNCTION Restore_Version(
2691 p_id IN NUMBER,
2692 p_major_version IN NUMBER) RETURN VARCHAR2 IS
2693
2694 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2695
2696 BEGIN
2697
2698 DELETE FROM OKS_PM_STREAM_LEVELS
2699 WHERE DNZ_CHR_ID = p_id;
2700
2701 INSERT INTO OKS_PM_STREAM_LEVELS(
2702 id,
2703 cle_id,
2704 dnz_chr_id,
2705 activity_line_id,
2706 sequence_number,
2707 number_of_occurences,
2708 start_date,
2709 end_date,
2710 frequency,
2711 frequency_uom,
2712 offset_duration,
2713 offset_uom,
2714 autoschedule_yn,
2715 program_application_id,
2716 program_id,
2717 program_update_date,
2718 object_version_number,
2719 request_id,
2720 created_by,
2721 creation_date,
2722 last_updated_by,
2723 last_update_date,
2724 last_update_login
2725 )
2726 SELECT
2727 id,
2728 cle_id,
2729 dnz_chr_id,
2730 activity_line_id,
2731 sequence_number,
2732 number_of_occurences,
2733 start_date,
2734 end_date,
2735 frequency,
2736 frequency_uom,
2737 offset_duration,
2738 offset_uom,
2739 autoschedule_yn,
2740 program_application_id,
2741 program_id,
2742 program_update_date,
2743 object_version_number,
2744 request_id,
2745 created_by,
2746 creation_date,
2747 last_updated_by,
2748 last_update_date,
2749 last_update_login
2750 FROM OKS_PM_STREAM_LEVELS_H
2751 WHERE DNZ_CHR_ID = P_Id
2752 AND major_version=p_major_version;
2753
2754 RETURN l_return_status;
2755
2756
2757 EXCEPTION
2758
2759 WHEN OTHERS THEN
2760
2761 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2762 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2763 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2764 p_token1_value => sqlcode,
2765 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2766 p_token2_value => sqlerrm);
2767
2768 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2769
2770 return l_return_status;
2771
2772 END Restore_Version;
2773
2774 END OKS_PML_PVT;