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