[Home] [Help]
PACKAGE BODY: APPS.OKS_SUBSCR_HDR_PVT
Source
1 PACKAGE BODY OKS_SUBSCR_HDR_PVT AS
2 /* $Header: OKSSBHRB.pls 120.2 2005/08/03 05:43:21 parkumar 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 -- PROCEDURE add_language
95 ---------------------------------------------------------------------------
96 PROCEDURE add_language IS
97 BEGIN
98 DELETE FROM OKS_SUBSCR_HEADER_TL T
99 WHERE NOT EXISTS (
100 SELECT NULL
101 FROM OKS_SUBSCR_HEADER_B B
102 WHERE B.ID =T.ID
103 );
104
105 UPDATE OKS_SUBSCR_HEADER_TL T SET(
106 NAME,
107 DESCRIPTION,
108 COMMENTS) = (SELECT
109 B.NAME,
110 B.DESCRIPTION,
111 B.COMMENTS
112 FROM OKS_SUBSCR_HEADER_TL B
113 WHERE B.ID = T.ID
114 AND B.LANGUAGE = T.SOURCE_LANG)
115 WHERE ( T.ID,
116 T.LANGUAGE)
117 IN (SELECT
118 SUBT.ID,
119 SUBT.LANGUAGE
120 FROM OKS_SUBSCR_HEADER_TL SUBB, OKS_SUBSCR_HEADER_TL SUBT
121 WHERE SUBB.ID = SUBT.ID
122 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
123 AND (SUBB.NAME <> SUBT.NAME
124 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
125 OR SUBB.COMMENTS <> SUBT.COMMENTS
126 OR (SUBB.NAME IS NOT NULL AND SUBT.NAME IS NULL)
127 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
128 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
129 ));
130
131 INSERT INTO OKS_SUBSCR_HEADER_TL (
132 ID,
133 NAME,
134 DESCRIPTION,
135 LANGUAGE,
136 SOURCE_LANG,
137 SFWT_FLAG,
138 COMMENTS,
139 CREATED_BY,
140 CREATION_DATE,
141 LAST_UPDATED_BY,
142 LAST_UPDATE_DATE,
143 LAST_UPDATE_LOGIN)
144 SELECT
145 B.ID,
146 B.NAME,
147 B.DESCRIPTION,
148 L.LANGUAGE_CODE,
149 B.SOURCE_LANG,
150 B.SFWT_FLAG,
151 B.COMMENTS,
152 B.CREATED_BY,
153 B.CREATION_DATE,
154 B.LAST_UPDATED_BY,
155 B.LAST_UPDATE_DATE,
156 B.LAST_UPDATE_LOGIN
157 FROM OKS_SUBSCR_HEADER_TL B, FND_LANGUAGES L
158 WHERE L.INSTALLED_FLAG IN ('I', 'B')
159 AND B.LANGUAGE = USERENV('LANG')
160 AND NOT EXISTS (
161 SELECT NULL
162 FROM OKS_SUBSCR_HEADER_TL T
163 WHERE T.ID = B.ID
164 AND T.LANGUAGE = L.LANGUAGE_CODE
165 );
166 END add_language;
167
168 ---------------------------------------------------------------------------
169 -- FUNCTION get_rec for: OKS_SUBSCR_HEADER_V
170 ---------------------------------------------------------------------------
171 FUNCTION get_rec (
172 p_schv_rec IN schv_rec_type,
173 x_no_data_found OUT NOCOPY BOOLEAN
174 ) RETURN schv_rec_type IS
175 CURSOR oks_sch_pk_csr (p_id IN NUMBER) IS
176 SELECT
177 ID,
178 NAME,
179 DESCRIPTION,
180 CLE_ID,
181 DNZ_CHR_ID,
182 INSTANCE_ID,
183 SFWT_FLAG,
184 SUBSCRIPTION_TYPE,
185 ITEM_TYPE,
186 MEDIA_TYPE,
187 STATUS,
188 FREQUENCY,
189 FULFILLMENT_CHANNEL,
190 OFFSET,
191 COMMENTS,
192 UPG_ORIG_SYSTEM_REF,
193 UPG_ORIG_SYSTEM_REF_ID,
194 OBJECT_VERSION_NUMBER,
195 CREATED_BY,
196 CREATION_DATE,
197 LAST_UPDATED_BY,
198 LAST_UPDATE_DATE,
199 LAST_UPDATE_LOGIN
200 FROM Oks_Subscr_Header_V
201 WHERE oks_subscr_header_v.id = p_id;
202 l_oks_sch_pk oks_sch_pk_csr%ROWTYPE;
203 l_schv_rec schv_rec_type;
204 BEGIN
205 x_no_data_found := TRUE;
206 -- Get current database values
207 OPEN oks_sch_pk_csr (p_schv_rec.id);
208 FETCH oks_sch_pk_csr INTO
209 l_schv_rec.id,
210 l_schv_rec.name,
211 l_schv_rec.description,
212 l_schv_rec.cle_id,
213 l_schv_rec.dnz_chr_id,
214 l_schv_rec.instance_id,
215 l_schv_rec.sfwt_flag,
216 l_schv_rec.subscription_type,
217 l_schv_rec.item_type,
218 l_schv_rec.media_type,
219 l_schv_rec.status,
220 l_schv_rec.frequency,
221 l_schv_rec.fulfillment_channel,
222 l_schv_rec.offset,
223 l_schv_rec.comments,
224 l_schv_rec.upg_orig_system_ref,
225 l_schv_rec.upg_orig_system_ref_id,
226 l_schv_rec.object_version_number,
227 l_schv_rec.created_by,
228 l_schv_rec.creation_date,
229 l_schv_rec.last_updated_by,
230 l_schv_rec.last_update_date,
231 l_schv_rec.last_update_login;
232 x_no_data_found := oks_sch_pk_csr%NOTFOUND;
233 CLOSE oks_sch_pk_csr;
234 RETURN(l_schv_rec);
235 END get_rec;
236
237 ------------------------------------------------------------------
238 -- This version of get_rec sets error messages if no data found --
239 ------------------------------------------------------------------
240 FUNCTION get_rec (
241 p_schv_rec IN schv_rec_type,
242 x_return_status OUT NOCOPY VARCHAR2
243 ) RETURN schv_rec_type IS
244 l_schv_rec schv_rec_type;
245 l_row_notfound BOOLEAN := TRUE;
246 BEGIN
247 x_return_status := OKC_API.G_RET_STS_SUCCESS;
248 l_schv_rec := get_rec(p_schv_rec, l_row_notfound);
249 IF (l_row_notfound) THEN
250 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
251 x_return_status := OKC_API.G_RET_STS_ERROR;
252 END IF;
253 RETURN(l_schv_rec);
254 END get_rec;
255 -----------------------------------------------------------
256 -- So we don't have to pass an "l_row_notfound" variable --
257 -----------------------------------------------------------
258 FUNCTION get_rec (
259 p_schv_rec IN schv_rec_type
260 ) RETURN schv_rec_type IS
261 l_row_not_found BOOLEAN := TRUE;
262 BEGIN
263 RETURN(get_rec(p_schv_rec, l_row_not_found));
264 END get_rec;
265 ---------------------------------------------------------------------------
266 -- FUNCTION get_rec for: OKS_SUBSCR_HEADER_B
267 ---------------------------------------------------------------------------
268 FUNCTION get_rec (
269 p_sbh_rec IN sbh_rec_type,
270 x_no_data_found OUT NOCOPY BOOLEAN
271 ) RETURN sbh_rec_type IS
272 CURSOR oks_subscr_header_b_pk_csr (p_id IN NUMBER) IS
273 SELECT
274 ID,
275 CLE_ID,
276 DNZ_CHR_ID,
277 INSTANCE_ID,
278 SUBSCRIPTION_TYPE,
279 ITEM_TYPE,
280 MEDIA_TYPE,
281 STATUS,
282 FREQUENCY,
283 FULFILLMENT_CHANNEL,
284 OFFSET,
285 UPG_ORIG_SYSTEM_REF,
286 UPG_ORIG_SYSTEM_REF_ID,
287 OBJECT_VERSION_NUMBER,
288 CREATED_BY,
289 CREATION_DATE,
290 LAST_UPDATED_BY,
291 LAST_UPDATE_DATE,
292 LAST_UPDATE_LOGIN,
293 -- R12 Data Model Changes 4485150 Start
294 ORIG_SYSTEM_ID1,
295 ORIG_SYSTEM_REFERENCE1,
296 ORIG_SYSTEM_SOURCE_CODE
297 -- R12 Data Model Changes 4485150 End
298 FROM Oks_Subscr_Header_B
299 WHERE oks_subscr_header_b.id = p_id;
300 l_oks_subscr_header_b_pk oks_subscr_header_b_pk_csr%ROWTYPE;
301 l_sbh_rec sbh_rec_type;
302 BEGIN
303 x_no_data_found := TRUE;
304 -- Get current database values
305 OPEN oks_subscr_header_b_pk_csr (p_sbh_rec.id);
306 FETCH oks_subscr_header_b_pk_csr INTO
307 l_sbh_rec.id,
308 l_sbh_rec.cle_id,
309 l_sbh_rec.dnz_chr_id,
310 l_sbh_rec.instance_id,
311 l_sbh_rec.subscription_type,
312 l_sbh_rec.item_type,
313 l_sbh_rec.media_type,
314 l_sbh_rec.status,
315 l_sbh_rec.frequency,
316 l_sbh_rec.fulfillment_channel,
317 l_sbh_rec.offset,
318 l_sbh_rec.upg_orig_system_ref,
319 l_sbh_rec.upg_orig_system_ref_id,
320 l_sbh_rec.object_version_number,
321 l_sbh_rec.created_by,
322 l_sbh_rec.creation_date,
323 l_sbh_rec.last_updated_by,
324 l_sbh_rec.last_update_date,
325 l_sbh_rec.last_update_login,
326 -- R12 Data Model Changes 4485150 Start
327 l_sbh_rec.orig_system_id1,
328 l_sbh_rec.orig_system_reference1,
329 l_sbh_rec.orig_system_source_code
330 -- R12 Data Model Changes 4485150 End
331 ;
332 x_no_data_found := oks_subscr_header_b_pk_csr%NOTFOUND;
333 CLOSE oks_subscr_header_b_pk_csr;
334 RETURN(l_sbh_rec);
335 END get_rec;
336
337 ------------------------------------------------------------------
338 -- This version of get_rec sets error messages if no data found --
339 ------------------------------------------------------------------
340 FUNCTION get_rec (
341 p_sbh_rec IN sbh_rec_type,
342 x_return_status OUT NOCOPY VARCHAR2
343 ) RETURN sbh_rec_type IS
344 l_sbh_rec sbh_rec_type;
345 l_row_notfound BOOLEAN := TRUE;
346 BEGIN
347 x_return_status := OKC_API.G_RET_STS_SUCCESS;
348 l_sbh_rec := get_rec(p_sbh_rec, l_row_notfound);
349 IF (l_row_notfound) THEN
350 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
351 x_return_status := OKC_API.G_RET_STS_ERROR;
352 END IF;
353 RETURN(l_sbh_rec);
354 END get_rec;
355 -----------------------------------------------------------
356 -- So we don't have to pass an "l_row_notfound" variable --
357 -----------------------------------------------------------
358 FUNCTION get_rec (
359 p_sbh_rec IN sbh_rec_type
360 ) RETURN sbh_rec_type IS
361 l_row_not_found BOOLEAN := TRUE;
362 BEGIN
363 RETURN(get_rec(p_sbh_rec, l_row_not_found));
364 END get_rec;
365 ---------------------------------------------------------------------------
366 -- FUNCTION get_rec for: OKS_SUBSCR_HEADER_TL
367 ---------------------------------------------------------------------------
368 FUNCTION get_rec (
369 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type,
370 x_no_data_found OUT NOCOPY BOOLEAN
371 ) RETURN oks_subscr_header_tl_rec_type IS
372 CURSOR oks_subscr_header_tl_pk_csr (p_id IN NUMBER,
373 p_language IN VARCHAR2) IS
374 SELECT
375 ID,
376 NAME,
377 DESCRIPTION,
378 LANGUAGE,
379 SOURCE_LANG,
380 SFWT_FLAG,
381 COMMENTS,
382 CREATED_BY,
383 CREATION_DATE,
384 LAST_UPDATED_BY,
385 LAST_UPDATE_DATE,
386 LAST_UPDATE_LOGIN
387 FROM Oks_Subscr_Header_Tl
388 WHERE oks_subscr_header_tl.id = p_id
389 AND oks_subscr_header_tl.language = p_language;
390 l_oks_subscr_header_tl_pk oks_subscr_header_tl_pk_csr%ROWTYPE;
391 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
392 BEGIN
393 x_no_data_found := TRUE;
394 -- Get current database values
395 OPEN oks_subscr_header_tl_pk_csr (p_oks_subscr_header_tl_rec.id,
396 p_oks_subscr_header_tl_rec.language);
397 FETCH oks_subscr_header_tl_pk_csr INTO
398 l_oks_subscr_header_tl_rec.id,
399 l_oks_subscr_header_tl_rec.name,
400 l_oks_subscr_header_tl_rec.description,
401 l_oks_subscr_header_tl_rec.language,
402 l_oks_subscr_header_tl_rec.source_lang,
403 l_oks_subscr_header_tl_rec.sfwt_flag,
404 l_oks_subscr_header_tl_rec.comments,
405 l_oks_subscr_header_tl_rec.created_by,
406 l_oks_subscr_header_tl_rec.creation_date,
407 l_oks_subscr_header_tl_rec.last_updated_by,
408 l_oks_subscr_header_tl_rec.last_update_date,
409 l_oks_subscr_header_tl_rec.last_update_login;
410 x_no_data_found := oks_subscr_header_tl_pk_csr%NOTFOUND;
411 CLOSE oks_subscr_header_tl_pk_csr;
412 RETURN(l_oks_subscr_header_tl_rec);
413 END get_rec;
414
415 ------------------------------------------------------------------
416 -- This version of get_rec sets error messages if no data found --
417 ------------------------------------------------------------------
418 FUNCTION get_rec (
419 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type,
420 x_return_status OUT NOCOPY VARCHAR2
421 ) RETURN oks_subscr_header_tl_rec_type IS
422 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
423 l_row_notfound BOOLEAN := TRUE;
424 BEGIN
425 x_return_status := OKC_API.G_RET_STS_SUCCESS;
426 l_oks_subscr_header_tl_rec := get_rec(p_oks_subscr_header_tl_rec, l_row_notfound);
427 IF (l_row_notfound) THEN
428 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
429 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'LANGUAGE');
430 x_return_status := OKC_API.G_RET_STS_ERROR;
431 END IF;
432 RETURN(l_oks_subscr_header_tl_rec);
433 END get_rec;
434 -----------------------------------------------------------
435 -- So we don't have to pass an "l_row_notfound" variable --
436 -----------------------------------------------------------
437 FUNCTION get_rec (
438 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type
439 ) RETURN oks_subscr_header_tl_rec_type IS
440 l_row_not_found BOOLEAN := TRUE;
441 BEGIN
442 RETURN(get_rec(p_oks_subscr_header_tl_rec, l_row_not_found));
443 END get_rec;
444 ---------------------------------------------------------------------------
445 -- FUNCTION null_out_defaults for: OKS_SUBSCR_HEADER_V
446 ---------------------------------------------------------------------------
447 FUNCTION null_out_defaults (
448 p_schv_rec IN schv_rec_type
449 ) RETURN schv_rec_type IS
450 l_schv_rec schv_rec_type := p_schv_rec;
451 BEGIN
452 IF (l_schv_rec.id = OKC_API.G_MISS_NUM ) THEN
453 l_schv_rec.id := NULL;
454 END IF;
455 IF (l_schv_rec.name = OKC_API.G_MISS_CHAR ) THEN
456 l_schv_rec.name := NULL;
457 END IF;
458 IF (l_schv_rec.description = OKC_API.G_MISS_CHAR ) THEN
459 l_schv_rec.description := NULL;
460 END IF;
461 IF (l_schv_rec.cle_id = OKC_API.G_MISS_NUM ) THEN
462 l_schv_rec.cle_id := NULL;
463 END IF;
464 IF (l_schv_rec.dnz_chr_id = OKC_API.G_MISS_NUM ) THEN
465 l_schv_rec.dnz_chr_id := NULL;
466 END IF;
467 IF (l_schv_rec.instance_id = OKC_API.G_MISS_NUM ) THEN
468 l_schv_rec.instance_id := NULL;
469 END IF;
470 IF (l_schv_rec.sfwt_flag = OKC_API.G_MISS_CHAR ) THEN
471 l_schv_rec.sfwt_flag := NULL;
472 END IF;
473 IF (l_schv_rec.subscription_type = OKC_API.G_MISS_CHAR ) THEN
474 l_schv_rec.subscription_type := NULL;
475 END IF;
476 IF (l_schv_rec.item_type = OKC_API.G_MISS_CHAR ) THEN
477 l_schv_rec.item_type := NULL;
478 END IF;
479 IF (l_schv_rec.media_type = OKC_API.G_MISS_CHAR ) THEN
480 l_schv_rec.media_type := NULL;
481 END IF;
482 IF (l_schv_rec.status = OKC_API.G_MISS_CHAR ) THEN
483 l_schv_rec.status := NULL;
484 END IF;
485 IF (l_schv_rec.frequency = OKC_API.G_MISS_CHAR ) THEN
486 l_schv_rec.frequency := NULL;
487 END IF;
488 IF (l_schv_rec.fulfillment_channel = OKC_API.G_MISS_CHAR ) THEN
489 l_schv_rec.fulfillment_channel := NULL;
490 END IF;
491 IF (l_schv_rec.offset = OKC_API.G_MISS_NUM ) THEN
492 l_schv_rec.offset := NULL;
493 END IF;
494 IF (l_schv_rec.comments = OKC_API.G_MISS_CHAR ) THEN
495 l_schv_rec.comments := NULL;
496 END IF;
497 IF (l_schv_rec.upg_orig_system_ref = OKC_API.G_MISS_CHAR ) THEN
498 l_schv_rec.upg_orig_system_ref := NULL;
499 END IF;
500 IF (l_schv_rec.upg_orig_system_ref_id = OKC_API.G_MISS_NUM ) THEN
501 l_schv_rec.upg_orig_system_ref_id := NULL;
502 END IF;
503 IF (l_schv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
504 l_schv_rec.object_version_number := NULL;
505 END IF;
506 IF (l_schv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
507 l_schv_rec.created_by := NULL;
508 END IF;
509 IF (l_schv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
510 l_schv_rec.creation_date := NULL;
511 END IF;
512 IF (l_schv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
513 l_schv_rec.last_updated_by := NULL;
514 END IF;
515 IF (l_schv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
516 l_schv_rec.last_update_date := NULL;
517 END IF;
518 IF (l_schv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
519 l_schv_rec.last_update_login := NULL;
520 END IF;
521 RETURN(l_schv_rec);
522 END null_out_defaults;
523 ---------------------------------
524 -- Validate_Attributes for: ID --
525 ---------------------------------
526 PROCEDURE validate_id(
527 x_return_status OUT NOCOPY VARCHAR2,
528 p_id IN NUMBER) IS
529 BEGIN
530 x_return_status := OKC_API.G_RET_STS_SUCCESS;
531 IF (p_id = OKC_API.G_MISS_NUM OR
532 p_id IS NULL)
533 THEN
534 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
535 x_return_status := OKC_API.G_RET_STS_ERROR;
536 RAISE G_EXCEPTION_HALT_VALIDATION;
537 END IF;
538 EXCEPTION
539 WHEN G_EXCEPTION_HALT_VALIDATION THEN
540 null;
541 WHEN OTHERS THEN
542 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
543 ,p_msg_name => G_UNEXPECTED_ERROR
544 ,p_token1 => G_SQLCODE_TOKEN
545 ,p_token1_value => SQLCODE
546 ,p_token2 => G_SQLERRM_TOKEN
547 ,p_token2_value => SQLERRM);
548 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
549 END validate_id;
550 -----------------------------------
551 -- Validate_Attributes for: NAME --
552 -----------------------------------
553 PROCEDURE validate_name(
554 x_return_status OUT NOCOPY VARCHAR2,
555 p_name IN VARCHAR2) IS
556 BEGIN
557 x_return_status := OKC_API.G_RET_STS_SUCCESS;
558 IF (p_name = OKC_API.G_MISS_CHAR OR
559 p_name IS NULL)
560 THEN
561 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'name');
562 x_return_status := OKC_API.G_RET_STS_ERROR;
563 RAISE G_EXCEPTION_HALT_VALIDATION;
564 END IF;
565 EXCEPTION
566 WHEN G_EXCEPTION_HALT_VALIDATION THEN
567 null;
568 WHEN OTHERS THEN
569 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
570 ,p_msg_name => G_UNEXPECTED_ERROR
571 ,p_token1 => G_SQLCODE_TOKEN
572 ,p_token1_value => SQLCODE
573 ,p_token2 => G_SQLERRM_TOKEN
574 ,p_token2_value => SQLERRM);
575 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
576 END validate_name;
577 -----------------------------------------
578 -- Validate_Attributes for: DNZ_CHR_ID --
579 -----------------------------------------
580 PROCEDURE validate_dnz_chr_id(
581 x_return_status OUT NOCOPY VARCHAR2,
582 p_dnz_chr_id IN NUMBER) IS
583 BEGIN
584 x_return_status := OKC_API.G_RET_STS_SUCCESS;
585 IF (p_dnz_chr_id = OKC_API.G_MISS_NUM OR
586 p_dnz_chr_id IS NULL)
587 THEN
588 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'dnz_chr_id');
589 x_return_status := OKC_API.G_RET_STS_ERROR;
590 RAISE G_EXCEPTION_HALT_VALIDATION;
591 END IF;
592 EXCEPTION
593 WHEN G_EXCEPTION_HALT_VALIDATION THEN
594 null;
595 WHEN OTHERS THEN
596 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
597 ,p_msg_name => G_UNEXPECTED_ERROR
598 ,p_token1 => G_SQLCODE_TOKEN
599 ,p_token1_value => SQLCODE
600 ,p_token2 => G_SQLERRM_TOKEN
601 ,p_token2_value => SQLERRM);
602 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
603 END validate_dnz_chr_id;
604 ----------------------------------------
605 -- Validate_Attributes for: SFWT_FLAG --
606 ----------------------------------------
607 PROCEDURE validate_sfwt_flag(
608 x_return_status OUT NOCOPY VARCHAR2,
609 p_sfwt_flag IN VARCHAR2) IS
610 BEGIN
611 x_return_status := OKC_API.G_RET_STS_SUCCESS;
612 IF (p_sfwt_flag = OKC_API.G_MISS_CHAR OR
613 p_sfwt_flag IS NULL)
614 THEN
615 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
616 x_return_status := OKC_API.G_RET_STS_ERROR;
617 RAISE G_EXCEPTION_HALT_VALIDATION;
618 END IF;
619 EXCEPTION
620 WHEN G_EXCEPTION_HALT_VALIDATION THEN
621 null;
622 WHEN OTHERS THEN
623 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
624 ,p_msg_name => G_UNEXPECTED_ERROR
625 ,p_token1 => G_SQLCODE_TOKEN
626 ,p_token1_value => SQLCODE
627 ,p_token2 => G_SQLERRM_TOKEN
628 ,p_token2_value => SQLERRM);
629 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
630 END validate_sfwt_flag;
631 ------------------------------------------------
632 -- Validate_Attributes for: SUBSCRIPTION_TYPE --
633 ------------------------------------------------
634 PROCEDURE validate_subscription_type(
635 x_return_status OUT NOCOPY VARCHAR2,
636 p_subscription_type IN VARCHAR2) IS
637 BEGIN
638 x_return_status := OKC_API.G_RET_STS_SUCCESS;
639 IF (p_subscription_type = OKC_API.G_MISS_CHAR OR
640 p_subscription_type IS NULL)
641 THEN
642 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'subscription_type');
643 x_return_status := OKC_API.G_RET_STS_ERROR;
644 RAISE G_EXCEPTION_HALT_VALIDATION;
645 END IF;
646 EXCEPTION
647 WHEN G_EXCEPTION_HALT_VALIDATION THEN
648 null;
649 WHEN OTHERS THEN
650 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
651 ,p_msg_name => G_UNEXPECTED_ERROR
652 ,p_token1 => G_SQLCODE_TOKEN
653 ,p_token1_value => SQLCODE
654 ,p_token2 => G_SQLERRM_TOKEN
655 ,p_token2_value => SQLERRM);
656 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
657 END validate_subscription_type;
658 ----------------------------------------
659 -- Validate_Attributes for: FREQUENCY --
660 ----------------------------------------
661 PROCEDURE validate_frequency(
662 x_return_status OUT NOCOPY VARCHAR2,
663 p_frequency IN VARCHAR2) IS
664 BEGIN
665 x_return_status := OKC_API.G_RET_STS_SUCCESS;
666 IF (p_frequency = OKC_API.G_MISS_CHAR OR
667 p_frequency IS NULL)
668 THEN
669 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'frequency');
670 x_return_status := OKC_API.G_RET_STS_ERROR;
671 RAISE G_EXCEPTION_HALT_VALIDATION;
672 END IF;
673 EXCEPTION
674 WHEN G_EXCEPTION_HALT_VALIDATION THEN
675 null;
676 WHEN OTHERS THEN
677 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
678 ,p_msg_name => G_UNEXPECTED_ERROR
679 ,p_token1 => G_SQLCODE_TOKEN
680 ,p_token1_value => SQLCODE
681 ,p_token2 => G_SQLERRM_TOKEN
682 ,p_token2_value => SQLERRM);
683 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
684 END validate_frequency;
685 --------------------------------------------------
686 -- Validate_Attributes for: FULFILLMENT_CHANNEL --
687 --------------------------------------------------
688 PROCEDURE validate_fulfillment_channel(
689 x_return_status OUT NOCOPY VARCHAR2,
690 p_fulfillment_channel IN VARCHAR2) IS
691 BEGIN
692 x_return_status := OKC_API.G_RET_STS_SUCCESS;
693 IF (p_fulfillment_channel = OKC_API.G_MISS_CHAR OR
694 p_fulfillment_channel IS NULL)
695 THEN
696 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'fulfillment_channel');
697 x_return_status := OKC_API.G_RET_STS_ERROR;
698 RAISE G_EXCEPTION_HALT_VALIDATION;
699 END IF;
700 EXCEPTION
701 WHEN G_EXCEPTION_HALT_VALIDATION THEN
702 null;
703 WHEN OTHERS THEN
704 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
705 ,p_msg_name => G_UNEXPECTED_ERROR
706 ,p_token1 => G_SQLCODE_TOKEN
707 ,p_token1_value => SQLCODE
708 ,p_token2 => G_SQLERRM_TOKEN
709 ,p_token2_value => SQLERRM);
710 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
711 END validate_fulfillment_channel;
712 ----------------------------------------------------
713 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
714 ----------------------------------------------------
715 PROCEDURE validate_object_version_number(
716 x_return_status OUT NOCOPY VARCHAR2,
717 p_object_version_number IN NUMBER) IS
718 BEGIN
719 x_return_status := OKC_API.G_RET_STS_SUCCESS;
720 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
721 p_object_version_number IS NULL)
722 THEN
723 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
724 x_return_status := OKC_API.G_RET_STS_ERROR;
725 RAISE G_EXCEPTION_HALT_VALIDATION;
726 END IF;
727 EXCEPTION
728 WHEN G_EXCEPTION_HALT_VALIDATION THEN
729 null;
730 WHEN OTHERS THEN
731 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
732 ,p_msg_name => G_UNEXPECTED_ERROR
733 ,p_token1 => G_SQLCODE_TOKEN
734 ,p_token1_value => SQLCODE
735 ,p_token2 => G_SQLERRM_TOKEN
736 ,p_token2_value => SQLERRM);
737 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
738 END validate_object_version_number;
739 ---------------------------------------------------------------------------
740 -- FUNCTION Validate_Attributes
741 ---------------------------------------------------------------------------
742 -------------------------------------------------
743 -- Validate_Attributes for:OKS_SUBSCR_HEADER_V --
744 -------------------------------------------------
745 FUNCTION Validate_Attributes (
746 p_schv_rec IN schv_rec_type
747 ) RETURN VARCHAR2 IS
748 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
749 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
750 BEGIN
751 -----------------------------
752 -- Column Level Validation --
753 -----------------------------
754 -- ***
755 -- id
756 -- ***
757 validate_id(x_return_status, p_schv_rec.id);
758 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
759 l_return_status := x_return_status;
760 RAISE G_EXCEPTION_HALT_VALIDATION;
761 END IF;
762
763 -- ***
764 -- name
765 -- ***
766 validate_name(x_return_status, p_schv_rec.name);
767 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
768 l_return_status := x_return_status;
769 RAISE G_EXCEPTION_HALT_VALIDATION;
770 END IF;
771
772 -- ***
773 -- dnz_chr_id
774 -- ***
775 validate_dnz_chr_id(x_return_status, p_schv_rec.dnz_chr_id);
776 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
777 l_return_status := x_return_status;
778 RAISE G_EXCEPTION_HALT_VALIDATION;
779 END IF;
780
781 -- ***
782 -- sfwt_flag
783 -- ***
784 validate_sfwt_flag(x_return_status, p_schv_rec.sfwt_flag);
785 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
786 l_return_status := x_return_status;
787 RAISE G_EXCEPTION_HALT_VALIDATION;
788 END IF;
789
790 -- ***
791 -- subscription_type
792 -- ***
793 validate_subscription_type(x_return_status, p_schv_rec.subscription_type);
794 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
795 l_return_status := x_return_status;
796 RAISE G_EXCEPTION_HALT_VALIDATION;
797 END IF;
798
799 -- ***
800 -- frequency
801 -- ***
802 validate_frequency(x_return_status, p_schv_rec.frequency);
803 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
804 l_return_status := x_return_status;
805 RAISE G_EXCEPTION_HALT_VALIDATION;
806 END IF;
807
808 -- ***
809 -- fulfillment_channel
810 -- ***
811 validate_fulfillment_channel(x_return_status, p_schv_rec.fulfillment_channel);
812 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
813 l_return_status := x_return_status;
814 RAISE G_EXCEPTION_HALT_VALIDATION;
815 END IF;
816
817 -- ***
818 -- object_version_number
819 -- ***
820 validate_object_version_number(x_return_status, p_schv_rec.object_version_number);
821 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
822 l_return_status := x_return_status;
823 RAISE G_EXCEPTION_HALT_VALIDATION;
824 END IF;
825
826 RETURN(l_return_status);
827 EXCEPTION
828 WHEN G_EXCEPTION_HALT_VALIDATION THEN
829 RETURN(l_return_status);
830 WHEN OTHERS THEN
831 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
832 ,p_msg_name => G_UNEXPECTED_ERROR
833 ,p_token1 => G_SQLCODE_TOKEN
834 ,p_token1_value => SQLCODE
835 ,p_token2 => G_SQLERRM_TOKEN
836 ,p_token2_value => SQLERRM);
837 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
838 RETURN(l_return_status);
839 END Validate_Attributes;
840 ---------------------------------------------------------------------------
841 -- PROCEDURE Validate_Record
842 ---------------------------------------------------------------------------
843 ---------------------------------------------
844 -- Validate Record for:OKS_SUBSCR_HEADER_V --
845 ---------------------------------------------
846 FUNCTION Validate_Record (
847 p_schv_rec IN schv_rec_type,
848 p_db_schv_rec IN schv_rec_type
849 ) RETURN VARCHAR2 IS
850 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
851 BEGIN
852 RETURN (l_return_status);
853 END Validate_Record;
854 FUNCTION Validate_Record (
855 p_schv_rec IN schv_rec_type
856 ) RETURN VARCHAR2 IS
857 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
858 l_db_schv_rec schv_rec_type := get_rec(p_schv_rec);
859 BEGIN
860 l_return_status := Validate_Record(p_schv_rec => p_schv_rec,
861 p_db_schv_rec => l_db_schv_rec);
862 RETURN (l_return_status);
863 END Validate_Record;
864
865 ---------------------------------------------------------------------------
866 -- PROCEDURE Migrate
867 ---------------------------------------------------------------------------
868 PROCEDURE migrate (
869 p_from IN schv_rec_type,
870 p_to IN OUT NOCOPY sbh_rec_type
871 ) IS
872 BEGIN
873 p_to.id := p_from.id;
874 p_to.cle_id := p_from.cle_id;
875 p_to.dnz_chr_id := p_from.dnz_chr_id;
876 p_to.instance_id := p_from.instance_id;
877 p_to.subscription_type := p_from.subscription_type;
878 p_to.item_type := p_from.item_type;
879 p_to.media_type := p_from.media_type;
880 p_to.status := p_from.status;
881 p_to.frequency := p_from.frequency;
882 p_to.fulfillment_channel := p_from.fulfillment_channel;
883 p_to.offset := p_from.offset;
884 p_to.upg_orig_system_ref := p_from.upg_orig_system_ref;
885 p_to.upg_orig_system_ref_id := p_from.upg_orig_system_ref_id;
886 p_to.object_version_number := p_from.object_version_number;
887 p_to.created_by := p_from.created_by;
888 p_to.creation_date := p_from.creation_date;
889 p_to.last_updated_by := p_from.last_updated_by;
890 p_to.last_update_date := p_from.last_update_date;
891 p_to.last_update_login := p_from.last_update_login;
892 END migrate;
893 PROCEDURE migrate (
894 p_from IN sbh_rec_type,
895 p_to IN OUT NOCOPY schv_rec_type
896 ) IS
897 BEGIN
898 p_to.id := p_from.id;
899 p_to.cle_id := p_from.cle_id;
900 p_to.dnz_chr_id := p_from.dnz_chr_id;
901 p_to.instance_id := p_from.instance_id;
902 p_to.subscription_type := p_from.subscription_type;
903 p_to.item_type := p_from.item_type;
904 p_to.media_type := p_from.media_type;
905 p_to.status := p_from.status;
906 p_to.frequency := p_from.frequency;
907 p_to.fulfillment_channel := p_from.fulfillment_channel;
908 p_to.offset := p_from.offset;
909 p_to.upg_orig_system_ref := p_from.upg_orig_system_ref;
910 p_to.upg_orig_system_ref_id := p_from.upg_orig_system_ref_id;
911 p_to.object_version_number := p_from.object_version_number;
912 p_to.created_by := p_from.created_by;
913 p_to.creation_date := p_from.creation_date;
914 p_to.last_updated_by := p_from.last_updated_by;
915 p_to.last_update_date := p_from.last_update_date;
916 p_to.last_update_login := p_from.last_update_login;
917 END migrate;
918 PROCEDURE migrate (
919 p_from IN schv_rec_type,
920 p_to IN OUT NOCOPY oks_subscr_header_tl_rec_type
921 ) IS
922 BEGIN
923 p_to.id := p_from.id;
924 p_to.name := p_from.name;
925 p_to.description := p_from.description;
926 p_to.sfwt_flag := p_from.sfwt_flag;
927 p_to.comments := p_from.comments;
928 p_to.created_by := p_from.created_by;
929 p_to.creation_date := p_from.creation_date;
930 p_to.last_updated_by := p_from.last_updated_by;
931 p_to.last_update_date := p_from.last_update_date;
932 p_to.last_update_login := p_from.last_update_login;
933 END migrate;
934 PROCEDURE migrate (
935 p_from IN oks_subscr_header_tl_rec_type,
936 p_to IN OUT NOCOPY schv_rec_type
937 ) IS
938 BEGIN
939 p_to.id := p_from.id;
940 p_to.name := p_from.name;
941 p_to.description := p_from.description;
942 p_to.sfwt_flag := p_from.sfwt_flag;
943 p_to.comments := p_from.comments;
944 p_to.created_by := p_from.created_by;
945 p_to.creation_date := p_from.creation_date;
946 p_to.last_updated_by := p_from.last_updated_by;
947 p_to.last_update_date := p_from.last_update_date;
948 p_to.last_update_login := p_from.last_update_login;
949 END migrate;
950 ---------------------------------------------------------------------------
951 -- PROCEDURE validate_row
952 ---------------------------------------------------------------------------
953 ------------------------------------------
954 -- validate_row for:OKS_SUBSCR_HEADER_V --
955 ------------------------------------------
956 PROCEDURE validate_row(
957 p_api_version IN NUMBER,
958 p_init_msg_list IN VARCHAR2,
959 x_return_status OUT NOCOPY VARCHAR2,
960 x_msg_count OUT NOCOPY NUMBER,
961 x_msg_data OUT NOCOPY VARCHAR2,
962 p_schv_rec IN schv_rec_type) IS
963
964 l_api_version CONSTANT NUMBER := 1;
965 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
966 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
967 l_schv_rec schv_rec_type := p_schv_rec;
968 l_sbh_rec sbh_rec_type;
969 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
970 BEGIN
971 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
972 G_PKG_NAME,
973 p_init_msg_list,
974 l_api_version,
975 p_api_version,
976 '_PVT',
977 x_return_status);
978 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
979 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
980 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
981 RAISE OKC_API.G_EXCEPTION_ERROR;
982 END IF;
983 --- Validate all non-missing attributes (Item Level Validation)
984 l_return_status := Validate_Attributes(l_schv_rec);
985 --- If any errors happen abort API
986 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
987 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
988 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
989 RAISE OKC_API.G_EXCEPTION_ERROR;
990 END IF;
991 l_return_status := Validate_Record(l_schv_rec);
992 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
993 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
994 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
995 RAISE OKC_API.G_EXCEPTION_ERROR;
996 END IF;
997 x_return_status := l_return_status;
998 EXCEPTION
999 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1000 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1001 (
1002 l_api_name,
1003 G_PKG_NAME,
1004 'OKC_API.G_RET_STS_ERROR',
1005 x_msg_count,
1006 x_msg_data,
1007 '_PVT'
1008 );
1009 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1010 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1011 (
1012 l_api_name,
1013 G_PKG_NAME,
1014 'OKC_API.G_RET_STS_UNEXP_ERROR',
1015 x_msg_count,
1016 x_msg_data,
1017 '_PVT'
1018 );
1019 WHEN OTHERS THEN
1020 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1021 (
1022 l_api_name,
1023 G_PKG_NAME,
1024 'OTHERS',
1025 x_msg_count,
1026 x_msg_data,
1027 '_PVT'
1028 );
1029 END validate_row;
1030 -----------------------------------------------------
1031 -- PL/SQL TBL validate_row for:OKS_SUBSCR_HEADER_V --
1032 -----------------------------------------------------
1033 PROCEDURE validate_row(
1034 p_api_version IN NUMBER,
1035 p_init_msg_list IN VARCHAR2,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2,
1039 p_schv_tbl IN schv_tbl_type,
1040 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1041
1042 l_api_version CONSTANT NUMBER := 1;
1043 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
1044 i NUMBER := 0;
1045 BEGIN
1046 OKC_API.init_msg_list(p_init_msg_list);
1047 -- Make sure PL/SQL table has records in it before passing
1048 IF (p_schv_tbl.COUNT > 0) THEN
1049 i := p_schv_tbl.FIRST;
1050 LOOP
1051 DECLARE
1052 l_error_rec OKC_API.ERROR_REC_TYPE;
1053 BEGIN
1054 l_error_rec.api_name := l_api_name;
1055 l_error_rec.api_package := G_PKG_NAME;
1056 l_error_rec.idx := i;
1057 validate_row (
1058 p_api_version => p_api_version,
1059 p_init_msg_list => OKC_API.G_FALSE,
1060 x_return_status => l_error_rec.error_type,
1061 x_msg_count => l_error_rec.msg_count,
1062 x_msg_data => l_error_rec.msg_data,
1063 p_schv_rec => p_schv_tbl(i));
1064 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1065 l_error_rec.sqlcode := SQLCODE;
1066 load_error_tbl(l_error_rec, px_error_tbl);
1067 ELSE
1068 x_msg_count := l_error_rec.msg_count;
1069 x_msg_data := l_error_rec.msg_data;
1070 END IF;
1071 EXCEPTION
1072 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1073 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1074 l_error_rec.sqlcode := SQLCODE;
1075 load_error_tbl(l_error_rec, px_error_tbl);
1076 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1077 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1078 l_error_rec.sqlcode := SQLCODE;
1079 load_error_tbl(l_error_rec, px_error_tbl);
1080 WHEN OTHERS THEN
1081 l_error_rec.error_type := 'OTHERS';
1082 l_error_rec.sqlcode := SQLCODE;
1083 load_error_tbl(l_error_rec, px_error_tbl);
1084 END;
1085 EXIT WHEN (i = p_schv_tbl.LAST);
1086 i := p_schv_tbl.NEXT(i);
1087 END LOOP;
1088 END IF;
1089 -- Loop through the error_tbl to find the error with the highest severity
1090 -- and return it.
1091 x_return_status := find_highest_exception(px_error_tbl);
1092 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1093 EXCEPTION
1094 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1095 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1096 (
1097 l_api_name,
1098 G_PKG_NAME,
1099 'OKC_API.G_RET_STS_ERROR',
1100 x_msg_count,
1101 x_msg_data,
1102 '_PVT'
1103 );
1104 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1105 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1106 (
1107 l_api_name,
1108 G_PKG_NAME,
1109 'OKC_API.G_RET_STS_UNEXP_ERROR',
1110 x_msg_count,
1111 x_msg_data,
1112 '_PVT'
1113 );
1114 WHEN OTHERS THEN
1115 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1116 (
1117 l_api_name,
1118 G_PKG_NAME,
1119 'OTHERS',
1120 x_msg_count,
1121 x_msg_data,
1122 '_PVT'
1123 );
1124 END validate_row;
1125
1126 -----------------------------------------------------
1127 -- PL/SQL TBL validate_row for:OKS_SUBSCR_HEADER_V --
1128 -----------------------------------------------------
1129 PROCEDURE validate_row(
1130 p_api_version IN NUMBER,
1131 p_init_msg_list IN VARCHAR2,
1132 x_return_status OUT NOCOPY VARCHAR2,
1133 x_msg_count OUT NOCOPY NUMBER,
1134 x_msg_data OUT NOCOPY VARCHAR2,
1135 p_schv_tbl IN schv_tbl_type) IS
1136
1137 l_api_version CONSTANT NUMBER := 1;
1138 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1139 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1140 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1141 BEGIN
1142 OKC_API.init_msg_list(p_init_msg_list);
1143 -- Make sure PL/SQL table has records in it before passing
1144 IF (p_schv_tbl.COUNT > 0) THEN
1145 validate_row (
1146 p_api_version => p_api_version,
1147 p_init_msg_list => OKC_API.G_FALSE,
1148 x_return_status => x_return_status,
1149 x_msg_count => x_msg_count,
1150 x_msg_data => x_msg_data,
1151 p_schv_tbl => p_schv_tbl,
1152 px_error_tbl => l_error_tbl);
1153 END IF;
1154 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1155 EXCEPTION
1156 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1157 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1158 (
1159 l_api_name,
1160 G_PKG_NAME,
1161 'OKC_API.G_RET_STS_ERROR',
1162 x_msg_count,
1163 x_msg_data,
1164 '_PVT'
1165 );
1166 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1167 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1168 (
1169 l_api_name,
1170 G_PKG_NAME,
1171 'OKC_API.G_RET_STS_UNEXP_ERROR',
1172 x_msg_count,
1173 x_msg_data,
1174 '_PVT'
1175 );
1176 WHEN OTHERS THEN
1177 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1178 (
1179 l_api_name,
1180 G_PKG_NAME,
1181 'OTHERS',
1182 x_msg_count,
1183 x_msg_data,
1184 '_PVT'
1185 );
1186 END validate_row;
1187
1188 ---------------------------------------------------------------------------
1189 -- PROCEDURE insert_row
1190 ---------------------------------------------------------------------------
1191 ----------------------------------------
1192 -- insert_row for:OKS_SUBSCR_HEADER_B --
1193 ----------------------------------------
1194 PROCEDURE insert_row(
1195 p_init_msg_list IN VARCHAR2,
1196 x_return_status OUT NOCOPY VARCHAR2,
1197 x_msg_count OUT NOCOPY NUMBER,
1198 x_msg_data OUT NOCOPY VARCHAR2,
1199 p_sbh_rec IN sbh_rec_type,
1200 x_sbh_rec OUT NOCOPY sbh_rec_type) IS
1201
1202 l_api_version CONSTANT NUMBER := 1;
1203 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1204 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1205 l_sbh_rec sbh_rec_type := p_sbh_rec;
1206 l_def_sbh_rec sbh_rec_type;
1207 --------------------------------------------
1208 -- Set_Attributes for:OKS_SUBSCR_HEADER_B --
1209 --------------------------------------------
1210 FUNCTION Set_Attributes (
1211 p_sbh_rec IN sbh_rec_type,
1212 x_sbh_rec OUT NOCOPY sbh_rec_type
1213 ) RETURN VARCHAR2 IS
1214 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1215 BEGIN
1216 x_sbh_rec := p_sbh_rec;
1217 RETURN(l_return_status);
1218 END Set_Attributes;
1219 BEGIN
1220 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1221 p_init_msg_list,
1222 '_PVT',
1223 x_return_status);
1224 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1225 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1226 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1227 RAISE OKC_API.G_EXCEPTION_ERROR;
1228 END IF;
1229 --- Setting item atributes
1230 l_return_status := Set_Attributes(
1231 p_sbh_rec, -- IN
1232 l_sbh_rec); -- OUT
1233 --- If any errors happen abort API
1234 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1235 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1236 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1237 RAISE OKC_API.G_EXCEPTION_ERROR;
1238 END IF;
1239 INSERT INTO OKS_SUBSCR_HEADER_B(
1240 id,
1241 cle_id,
1242 dnz_chr_id,
1243 instance_id,
1244 subscription_type,
1245 item_type,
1246 media_type,
1247 status,
1248 frequency,
1249 fulfillment_channel,
1250 offset,
1251 upg_orig_system_ref,
1252 upg_orig_system_ref_id,
1253 object_version_number,
1254 created_by,
1255 creation_date,
1256 last_updated_by,
1257 last_update_date,
1258 last_update_login,
1259 -- R12 Data Model Changes 4485150 Start
1260 orig_system_id1,
1261 orig_system_reference1,
1262 orig_system_source_code
1263 -- R12 Data Model Changes 4485150 End
1264 )
1265 VALUES (
1266 l_sbh_rec.id,
1267 l_sbh_rec.cle_id,
1268 l_sbh_rec.dnz_chr_id,
1269 l_sbh_rec.instance_id,
1270 l_sbh_rec.subscription_type,
1271 l_sbh_rec.item_type,
1272 l_sbh_rec.media_type,
1273 l_sbh_rec.status,
1274 l_sbh_rec.frequency,
1275 l_sbh_rec.fulfillment_channel,
1276 l_sbh_rec.offset,
1277 l_sbh_rec.upg_orig_system_ref,
1278 l_sbh_rec.upg_orig_system_ref_id,
1279 l_sbh_rec.object_version_number,
1280 l_sbh_rec.created_by,
1281 l_sbh_rec.creation_date,
1282 l_sbh_rec.last_updated_by,
1283 l_sbh_rec.last_update_date,
1284 l_sbh_rec.last_update_login,
1285 -- R12 Data Model Changes 4485150 Start
1286 l_sbh_rec.orig_system_id1,
1287 l_sbh_rec.orig_system_reference1,
1288 l_sbh_rec.orig_system_source_code
1289 -- R12 Data Model Changes 4485150 End
1290 );
1291 -- Set OUT values
1292 x_sbh_rec := l_sbh_rec;
1293 x_return_status := l_return_status;
1294 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1295 EXCEPTION
1296 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1297 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1298 (
1299 l_api_name,
1300 G_PKG_NAME,
1301 'OKC_API.G_RET_STS_ERROR',
1302 x_msg_count,
1303 x_msg_data,
1304 '_PVT'
1305 );
1306 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1307 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1308 (
1309 l_api_name,
1310 G_PKG_NAME,
1311 'OKC_API.G_RET_STS_UNEXP_ERROR',
1312 x_msg_count,
1313 x_msg_data,
1314 '_PVT'
1315 );
1316 WHEN OTHERS THEN
1317 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1318 (
1319 l_api_name,
1320 G_PKG_NAME,
1321 'OTHERS',
1322 x_msg_count,
1323 x_msg_data,
1324 '_PVT'
1325 );
1326 END insert_row;
1327 -----------------------------------------
1328 -- insert_row for:OKS_SUBSCR_HEADER_TL --
1329 -----------------------------------------
1330 PROCEDURE insert_row(
1331 p_init_msg_list IN VARCHAR2,
1332 x_return_status OUT NOCOPY VARCHAR2,
1333 x_msg_count OUT NOCOPY NUMBER,
1334 x_msg_data OUT NOCOPY VARCHAR2,
1335 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type,
1336 x_oks_subscr_header_tl_rec OUT NOCOPY oks_subscr_header_tl_rec_type) IS
1337
1338 l_api_version CONSTANT NUMBER := 1;
1339 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1340 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1341 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type := p_oks_subscr_header_tl_rec;
1342 l_def_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
1343 CURSOR get_languages IS
1344 SELECT *
1345 FROM FND_LANGUAGES
1346 WHERE INSTALLED_FLAG IN ('I', 'B');
1347 ---------------------------------------------
1348 -- Set_Attributes for:OKS_SUBSCR_HEADER_TL --
1349 ---------------------------------------------
1350 FUNCTION Set_Attributes (
1351 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type,
1352 x_oks_subscr_header_tl_rec OUT NOCOPY oks_subscr_header_tl_rec_type
1353 ) RETURN VARCHAR2 IS
1354 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1355 BEGIN
1356 x_oks_subscr_header_tl_rec := p_oks_subscr_header_tl_rec;
1357 x_oks_subscr_header_tl_rec.LANGUAGE := USERENV('LANG');
1358 x_oks_subscr_header_tl_rec.SOURCE_LANG := USERENV('LANG');
1359 RETURN(l_return_status);
1360 END Set_Attributes;
1361 BEGIN
1362 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1363 p_init_msg_list,
1364 '_PVT',
1365 x_return_status);
1366 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1367 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1368 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1369 RAISE OKC_API.G_EXCEPTION_ERROR;
1370 END IF;
1371 --- Setting item attributes
1372 l_return_status := Set_Attributes(
1373 p_oks_subscr_header_tl_rec, -- IN
1374 l_oks_subscr_header_tl_rec); -- OUT
1375 --- If any errors happen abort API
1376 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1377 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1378 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1379 RAISE OKC_API.G_EXCEPTION_ERROR;
1380 END IF;
1381 FOR l_lang_rec IN get_languages LOOP
1382 l_oks_subscr_header_tl_rec.language := l_lang_rec.language_code;
1383 INSERT INTO OKS_SUBSCR_HEADER_TL(
1384 id,
1385 name,
1386 description,
1387 language,
1388 source_lang,
1389 sfwt_flag,
1390 comments,
1391 created_by,
1392 creation_date,
1393 last_updated_by,
1394 last_update_date,
1395 last_update_login)
1396 VALUES (
1397 l_oks_subscr_header_tl_rec.id,
1398 l_oks_subscr_header_tl_rec.name,
1399 l_oks_subscr_header_tl_rec.description,
1400 l_oks_subscr_header_tl_rec.language,
1401 l_oks_subscr_header_tl_rec.source_lang,
1402 l_oks_subscr_header_tl_rec.sfwt_flag,
1403 l_oks_subscr_header_tl_rec.comments,
1404 l_oks_subscr_header_tl_rec.created_by,
1405 l_oks_subscr_header_tl_rec.creation_date,
1406 l_oks_subscr_header_tl_rec.last_updated_by,
1407 l_oks_subscr_header_tl_rec.last_update_date,
1408 l_oks_subscr_header_tl_rec.last_update_login);
1409 END LOOP;
1410 -- Set OUT values
1411 x_oks_subscr_header_tl_rec := l_oks_subscr_header_tl_rec;
1412 x_return_status := l_return_status;
1413 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1414 EXCEPTION
1415 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1416 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1417 (
1418 l_api_name,
1419 G_PKG_NAME,
1420 'OKC_API.G_RET_STS_ERROR',
1421 x_msg_count,
1422 x_msg_data,
1423 '_PVT'
1424 );
1425 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1426 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1427 (
1428 l_api_name,
1429 G_PKG_NAME,
1430 'OKC_API.G_RET_STS_UNEXP_ERROR',
1431 x_msg_count,
1432 x_msg_data,
1433 '_PVT'
1434 );
1435 WHEN OTHERS THEN
1436 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1437 (
1438 l_api_name,
1439 G_PKG_NAME,
1440 'OTHERS',
1441 x_msg_count,
1442 x_msg_data,
1443 '_PVT'
1444 );
1445 END insert_row;
1446 -----------------------------------------
1447 -- insert_row for :OKS_SUBSCR_HEADER_V --
1448 -----------------------------------------
1449 PROCEDURE insert_row(
1450 p_api_version IN NUMBER,
1451 p_init_msg_list IN VARCHAR2,
1452 x_return_status OUT NOCOPY VARCHAR2,
1453 x_msg_count OUT NOCOPY NUMBER,
1454 x_msg_data OUT NOCOPY VARCHAR2,
1455 p_schv_rec IN schv_rec_type,
1456 x_schv_rec OUT NOCOPY schv_rec_type) IS
1457
1458 l_api_version CONSTANT NUMBER := 1;
1459 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1460 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1461 l_schv_rec schv_rec_type := p_schv_rec;
1462 l_def_schv_rec schv_rec_type;
1463 l_sbh_rec sbh_rec_type;
1464 lx_sbh_rec sbh_rec_type;
1465 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
1466 lx_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
1467 -------------------------------
1468 -- FUNCTION fill_who_columns --
1469 -------------------------------
1470 FUNCTION fill_who_columns (
1471 p_schv_rec IN schv_rec_type
1472 ) RETURN schv_rec_type IS
1473 l_schv_rec schv_rec_type := p_schv_rec;
1474 BEGIN
1475 l_schv_rec.CREATION_DATE := SYSDATE;
1476 l_schv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1477 l_schv_rec.LAST_UPDATE_DATE := l_schv_rec.CREATION_DATE;
1478 l_schv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1479 l_schv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1480 RETURN(l_schv_rec);
1481 END fill_who_columns;
1482 --------------------------------------------
1483 -- Set_Attributes for:OKS_SUBSCR_HEADER_V --
1484 --------------------------------------------
1485 FUNCTION Set_Attributes (
1486 p_schv_rec IN schv_rec_type,
1487 x_schv_rec OUT NOCOPY schv_rec_type
1488 ) RETURN VARCHAR2 IS
1489 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1490 BEGIN
1491 x_schv_rec := p_schv_rec;
1492 x_schv_rec.OBJECT_VERSION_NUMBER := 1;
1493 x_schv_rec.SFWT_FLAG := 'N';
1494 RETURN(l_return_status);
1495 END Set_Attributes;
1496 BEGIN
1497 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1498 G_PKG_NAME,
1499 p_init_msg_list,
1500 l_api_version,
1501 p_api_version,
1502 '_PVT',
1503 x_return_status);
1504 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1505 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1506 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1507 RAISE OKC_API.G_EXCEPTION_ERROR;
1508 END IF;
1509 l_schv_rec := null_out_defaults(p_schv_rec);
1510 -- Set primary key value
1511 l_schv_rec.ID := get_seq_id;
1512 -- Setting item attributes
1513 l_return_Status := Set_Attributes(
1514 l_schv_rec, -- IN
1515 l_def_schv_rec); -- OUT
1516 --- If any errors happen abort API
1517 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1518 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1519 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1520 RAISE OKC_API.G_EXCEPTION_ERROR;
1521 END IF;
1522 l_def_schv_rec := fill_who_columns(l_def_schv_rec);
1523 --- Validate all non-missing attributes (Item Level Validation)
1524 l_return_status := Validate_Attributes(l_def_schv_rec);
1525 --- If any errors happen abort API
1526 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1527 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1528 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1529 RAISE OKC_API.G_EXCEPTION_ERROR;
1530 END IF;
1531 l_return_status := Validate_Record(l_def_schv_rec);
1532 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1533 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1534 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1535 RAISE OKC_API.G_EXCEPTION_ERROR;
1536 END IF;
1537 -----------------------------------------
1538 -- Move VIEW record to "Child" records --
1539 -----------------------------------------
1540 migrate(l_def_schv_rec, l_sbh_rec);
1541 migrate(l_def_schv_rec, l_oks_subscr_header_tl_rec);
1542 -----------------------------------------------
1543 -- Call the INSERT_ROW for each child record --
1544 -----------------------------------------------
1545 insert_row(
1546 p_init_msg_list,
1547 l_return_status,
1548 x_msg_count,
1549 x_msg_data,
1550 l_sbh_rec,
1551 lx_sbh_rec
1552 );
1553 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1554 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1555 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1556 RAISE OKC_API.G_EXCEPTION_ERROR;
1557 END IF;
1558 migrate(lx_sbh_rec, l_def_schv_rec);
1559 insert_row(
1560 p_init_msg_list,
1561 l_return_status,
1562 x_msg_count,
1563 x_msg_data,
1564 l_oks_subscr_header_tl_rec,
1565 lx_oks_subscr_header_tl_rec
1566 );
1567 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1568 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1569 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1570 RAISE OKC_API.G_EXCEPTION_ERROR;
1571 END IF;
1572 migrate(lx_oks_subscr_header_tl_rec, l_def_schv_rec);
1573 -- Set OUT values
1574 x_schv_rec := l_def_schv_rec;
1575 x_return_status := l_return_status;
1576 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1577 EXCEPTION
1578 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1579 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1580 (
1581 l_api_name,
1582 G_PKG_NAME,
1583 'OKC_API.G_RET_STS_ERROR',
1584 x_msg_count,
1585 x_msg_data,
1586 '_PVT'
1587 );
1588 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1589 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1590 (
1591 l_api_name,
1592 G_PKG_NAME,
1593 'OKC_API.G_RET_STS_UNEXP_ERROR',
1594 x_msg_count,
1595 x_msg_data,
1596 '_PVT'
1597 );
1598 WHEN OTHERS THEN
1599 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1600 (
1601 l_api_name,
1602 G_PKG_NAME,
1603 'OTHERS',
1604 x_msg_count,
1605 x_msg_data,
1606 '_PVT'
1607 );
1608 END insert_row;
1609 ----------------------------------------
1610 -- PL/SQL TBL insert_row for:SCHV_TBL --
1611 ----------------------------------------
1612 PROCEDURE insert_row(
1613 p_api_version IN NUMBER,
1614 p_init_msg_list IN VARCHAR2,
1615 x_return_status OUT NOCOPY VARCHAR2,
1616 x_msg_count OUT NOCOPY NUMBER,
1617 x_msg_data OUT NOCOPY VARCHAR2,
1618 p_schv_tbl IN schv_tbl_type,
1619 x_schv_tbl OUT NOCOPY schv_tbl_type,
1620 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1621
1622 l_api_version CONSTANT NUMBER := 1;
1623 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1624 i NUMBER := 0;
1625 BEGIN
1626 OKC_API.init_msg_list(p_init_msg_list);
1627 -- Make sure PL/SQL table has records in it before passing
1628 IF (p_schv_tbl.COUNT > 0) THEN
1629 i := p_schv_tbl.FIRST;
1630 LOOP
1631 DECLARE
1632 l_error_rec OKC_API.ERROR_REC_TYPE;
1633 BEGIN
1634 l_error_rec.api_name := l_api_name;
1635 l_error_rec.api_package := G_PKG_NAME;
1636 l_error_rec.idx := i;
1637 insert_row (
1638 p_api_version => p_api_version,
1639 p_init_msg_list => OKC_API.G_FALSE,
1640 x_return_status => l_error_rec.error_type,
1641 x_msg_count => l_error_rec.msg_count,
1642 x_msg_data => l_error_rec.msg_data,
1643 p_schv_rec => p_schv_tbl(i),
1644 x_schv_rec => x_schv_tbl(i));
1645 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1646 l_error_rec.sqlcode := SQLCODE;
1647 load_error_tbl(l_error_rec, px_error_tbl);
1648 ELSE
1649 x_msg_count := l_error_rec.msg_count;
1650 x_msg_data := l_error_rec.msg_data;
1651 END IF;
1652 EXCEPTION
1653 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1654 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1655 l_error_rec.sqlcode := SQLCODE;
1656 load_error_tbl(l_error_rec, px_error_tbl);
1657 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1658 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1659 l_error_rec.sqlcode := SQLCODE;
1660 load_error_tbl(l_error_rec, px_error_tbl);
1661 WHEN OTHERS THEN
1662 l_error_rec.error_type := 'OTHERS';
1663 l_error_rec.sqlcode := SQLCODE;
1664 load_error_tbl(l_error_rec, px_error_tbl);
1665 END;
1666 EXIT WHEN (i = p_schv_tbl.LAST);
1667 i := p_schv_tbl.NEXT(i);
1668 END LOOP;
1669 END IF;
1670 -- Loop through the error_tbl to find the error with the highest severity
1671 -- and return it.
1672 x_return_status := find_highest_exception(px_error_tbl);
1673 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1674 EXCEPTION
1675 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1676 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1677 (
1678 l_api_name,
1679 G_PKG_NAME,
1680 'OKC_API.G_RET_STS_ERROR',
1681 x_msg_count,
1682 x_msg_data,
1683 '_PVT'
1684 );
1685 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1686 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1687 (
1688 l_api_name,
1689 G_PKG_NAME,
1690 'OKC_API.G_RET_STS_UNEXP_ERROR',
1691 x_msg_count,
1692 x_msg_data,
1693 '_PVT'
1694 );
1695 WHEN OTHERS THEN
1696 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1697 (
1698 l_api_name,
1699 G_PKG_NAME,
1700 'OTHERS',
1701 x_msg_count,
1702 x_msg_data,
1703 '_PVT'
1704 );
1705 END insert_row;
1706
1707 ----------------------------------------
1708 -- PL/SQL TBL insert_row for:SCHV_TBL --
1709 ----------------------------------------
1710 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
1711 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
1712 PROCEDURE insert_row(
1713 p_api_version IN NUMBER,
1714 p_init_msg_list IN VARCHAR2,
1715 x_return_status OUT NOCOPY VARCHAR2,
1716 x_msg_count OUT NOCOPY NUMBER,
1717 x_msg_data OUT NOCOPY VARCHAR2,
1718 p_schv_tbl IN schv_tbl_type,
1719 x_schv_tbl OUT NOCOPY schv_tbl_type) IS
1720
1721 l_api_version CONSTANT NUMBER := 1;
1722 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1723 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1724 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1725 BEGIN
1726 OKC_API.init_msg_list(p_init_msg_list);
1727 -- Make sure PL/SQL table has records in it before passing
1728 IF (p_schv_tbl.COUNT > 0) THEN
1729 insert_row (
1730 p_api_version => p_api_version,
1731 p_init_msg_list => OKC_API.G_FALSE,
1732 x_return_status => x_return_status,
1733 x_msg_count => x_msg_count,
1734 x_msg_data => x_msg_data,
1735 p_schv_tbl => p_schv_tbl,
1736 x_schv_tbl => x_schv_tbl,
1737 px_error_tbl => l_error_tbl);
1738 END IF;
1739 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1740 EXCEPTION
1741 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1742 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1743 (
1744 l_api_name,
1745 G_PKG_NAME,
1746 'OKC_API.G_RET_STS_ERROR',
1747 x_msg_count,
1748 x_msg_data,
1749 '_PVT'
1750 );
1751 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1752 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1753 (
1754 l_api_name,
1755 G_PKG_NAME,
1756 'OKC_API.G_RET_STS_UNEXP_ERROR',
1757 x_msg_count,
1758 x_msg_data,
1759 '_PVT'
1760 );
1761 WHEN OTHERS THEN
1762 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1763 (
1764 l_api_name,
1765 G_PKG_NAME,
1766 'OTHERS',
1767 x_msg_count,
1768 x_msg_data,
1769 '_PVT'
1770 );
1771 END insert_row;
1772
1773 ---------------------------------------------------------------------------
1774 -- PROCEDURE lock_row
1775 ---------------------------------------------------------------------------
1776 --------------------------------------
1777 -- lock_row for:OKS_SUBSCR_HEADER_B --
1778 --------------------------------------
1779 PROCEDURE lock_row(
1780 p_init_msg_list IN VARCHAR2,
1781 x_return_status OUT NOCOPY VARCHAR2,
1782 x_msg_count OUT NOCOPY NUMBER,
1783 x_msg_data OUT NOCOPY VARCHAR2,
1784 p_sbh_rec IN sbh_rec_type) IS
1785
1786 E_Resource_Busy EXCEPTION;
1787 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1788 CURSOR lock_csr (p_sbh_rec IN sbh_rec_type) IS
1789 SELECT OBJECT_VERSION_NUMBER
1790 FROM OKS_SUBSCR_HEADER_B
1791 WHERE ID = p_sbh_rec.id
1792 AND OBJECT_VERSION_NUMBER = p_sbh_rec.object_version_number
1793 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1794
1795 CURSOR lchk_csr (p_sbh_rec IN sbh_rec_type) IS
1796 SELECT OBJECT_VERSION_NUMBER
1797 FROM OKS_SUBSCR_HEADER_B
1798 WHERE ID = p_sbh_rec.id;
1799 l_api_version CONSTANT NUMBER := 1;
1800 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1801 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1802 l_object_version_number OKS_SUBSCR_HEADER_B.OBJECT_VERSION_NUMBER%TYPE;
1803 lc_object_version_number OKS_SUBSCR_HEADER_B.OBJECT_VERSION_NUMBER%TYPE;
1804 l_row_notfound BOOLEAN := FALSE;
1805 lc_row_notfound BOOLEAN := FALSE;
1806 BEGIN
1807 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1808 p_init_msg_list,
1809 '_PVT',
1810 x_return_status);
1811 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1812 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1813 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1814 RAISE OKC_API.G_EXCEPTION_ERROR;
1815 END IF;
1816 BEGIN
1817 OPEN lock_csr(p_sbh_rec);
1818 FETCH lock_csr INTO l_object_version_number;
1819 l_row_notfound := lock_csr%NOTFOUND;
1820 CLOSE lock_csr;
1821 EXCEPTION
1822 WHEN E_Resource_Busy THEN
1823 IF (lock_csr%ISOPEN) THEN
1824 CLOSE lock_csr;
1825 END IF;
1826 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1827 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1828 END;
1829
1830 IF ( l_row_notfound ) THEN
1831 OPEN lchk_csr(p_sbh_rec);
1832 FETCH lchk_csr INTO lc_object_version_number;
1833 lc_row_notfound := lchk_csr%NOTFOUND;
1834 CLOSE lchk_csr;
1835 END IF;
1836 IF (lc_row_notfound) THEN
1837 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1838 RAISE OKC_API.G_EXCEPTION_ERROR;
1839 ELSIF lc_object_version_number > p_sbh_rec.object_version_number THEN
1840 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1841 RAISE OKC_API.G_EXCEPTION_ERROR;
1842 ELSIF lc_object_version_number <> p_sbh_rec.object_version_number THEN
1843 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1844 RAISE OKC_API.G_EXCEPTION_ERROR;
1845 ELSIF lc_object_version_number = -1 THEN
1846 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1847 RAISE OKC_API.G_EXCEPTION_ERROR;
1848 END IF;
1849 x_return_status := l_return_status;
1850 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1851 EXCEPTION
1852 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1853 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1854 (
1855 l_api_name,
1856 G_PKG_NAME,
1857 'OKC_API.G_RET_STS_ERROR',
1858 x_msg_count,
1859 x_msg_data,
1860 '_PVT'
1861 );
1862 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1863 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1864 (
1865 l_api_name,
1866 G_PKG_NAME,
1867 'OKC_API.G_RET_STS_UNEXP_ERROR',
1868 x_msg_count,
1869 x_msg_data,
1870 '_PVT'
1871 );
1872 WHEN OTHERS THEN
1873 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1874 (
1875 l_api_name,
1876 G_PKG_NAME,
1877 'OTHERS',
1878 x_msg_count,
1879 x_msg_data,
1880 '_PVT'
1881 );
1882 END lock_row;
1883 ---------------------------------------
1884 -- lock_row for:OKS_SUBSCR_HEADER_TL --
1885 ---------------------------------------
1886 PROCEDURE lock_row(
1887 p_init_msg_list IN VARCHAR2,
1888 x_return_status OUT NOCOPY VARCHAR2,
1889 x_msg_count OUT NOCOPY NUMBER,
1890 x_msg_data OUT NOCOPY VARCHAR2,
1891 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type) IS
1892
1893 E_Resource_Busy EXCEPTION;
1894 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1895 CURSOR lock_csr (p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type) IS
1896 SELECT *
1897 FROM OKS_SUBSCR_HEADER_TL
1898 WHERE ID = p_oks_subscr_header_tl_rec.id
1899 FOR UPDATE NOWAIT;
1900
1901 l_api_version CONSTANT NUMBER := 1;
1902 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1903 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1904 l_lock_var lock_csr%ROWTYPE;
1905 l_row_notfound BOOLEAN := FALSE;
1906 lc_row_notfound BOOLEAN := FALSE;
1907 BEGIN
1908 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1909 p_init_msg_list,
1910 '_PVT',
1911 x_return_status);
1912 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1913 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1914 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1915 RAISE OKC_API.G_EXCEPTION_ERROR;
1916 END IF;
1917 BEGIN
1918 OPEN lock_csr(p_oks_subscr_header_tl_rec);
1919 FETCH lock_csr INTO l_lock_var;
1920 l_row_notfound := lock_csr%NOTFOUND;
1921 CLOSE lock_csr;
1922 EXCEPTION
1923 WHEN E_Resource_Busy THEN
1924 IF (lock_csr%ISOPEN) THEN
1925 CLOSE lock_csr;
1926 END IF;
1927 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1928 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1929 END;
1930
1931 IF ( l_row_notfound ) THEN
1932 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1933 RAISE OKC_API.G_EXCEPTION_ERROR;
1934 END IF;
1935 x_return_status := l_return_status;
1936 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1937 EXCEPTION
1938 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1939 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1940 (
1941 l_api_name,
1942 G_PKG_NAME,
1943 'OKC_API.G_RET_STS_ERROR',
1944 x_msg_count,
1945 x_msg_data,
1946 '_PVT'
1947 );
1948 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1949 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1950 (
1951 l_api_name,
1952 G_PKG_NAME,
1953 'OKC_API.G_RET_STS_UNEXP_ERROR',
1954 x_msg_count,
1955 x_msg_data,
1956 '_PVT'
1957 );
1958 WHEN OTHERS THEN
1959 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1960 (
1961 l_api_name,
1962 G_PKG_NAME,
1963 'OTHERS',
1964 x_msg_count,
1965 x_msg_data,
1966 '_PVT'
1967 );
1968 END lock_row;
1969 ---------------------------------------
1970 -- lock_row for: OKS_SUBSCR_HEADER_V --
1971 ---------------------------------------
1972 PROCEDURE lock_row(
1973 p_api_version IN NUMBER,
1974 p_init_msg_list IN VARCHAR2,
1975 x_return_status OUT NOCOPY VARCHAR2,
1976 x_msg_count OUT NOCOPY NUMBER,
1977 x_msg_data OUT NOCOPY VARCHAR2,
1978 p_schv_rec IN schv_rec_type) IS
1979
1980 l_api_version CONSTANT NUMBER := 1;
1981 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1982 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1983 l_sbh_rec sbh_rec_type;
1984 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
1985 BEGIN
1986 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1987 G_PKG_NAME,
1988 p_init_msg_list,
1989 l_api_version,
1990 p_api_version,
1991 '_PVT',
1992 x_return_status);
1993 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1994 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1995 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1996 RAISE OKC_API.G_EXCEPTION_ERROR;
1997 END IF;
1998 -----------------------------------------
1999 -- Move VIEW record to "Child" records --
2000 -----------------------------------------
2001 migrate(p_schv_rec, l_sbh_rec);
2002 migrate(p_schv_rec, l_oks_subscr_header_tl_rec);
2003 ---------------------------------------------
2004 -- Call the LOCK_ROW for each child record --
2005 ---------------------------------------------
2006 lock_row(
2007 p_init_msg_list,
2008 l_return_status,
2009 x_msg_count,
2010 x_msg_data,
2011 l_sbh_rec
2012 );
2013 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2014 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2015 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2016 RAISE OKC_API.G_EXCEPTION_ERROR;
2017 END IF;
2018 lock_row(
2019 p_init_msg_list,
2020 l_return_status,
2021 x_msg_count,
2022 x_msg_data,
2023 l_oks_subscr_header_tl_rec
2024 );
2025 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2026 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2027 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2028 RAISE OKC_API.G_EXCEPTION_ERROR;
2029 END IF;
2030 x_return_status := l_return_status;
2031 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2032 EXCEPTION
2033 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2034 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2035 (
2036 l_api_name,
2037 G_PKG_NAME,
2038 'OKC_API.G_RET_STS_ERROR',
2039 x_msg_count,
2040 x_msg_data,
2041 '_PVT'
2042 );
2043 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2044 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2045 (
2046 l_api_name,
2047 G_PKG_NAME,
2048 'OKC_API.G_RET_STS_UNEXP_ERROR',
2049 x_msg_count,
2050 x_msg_data,
2051 '_PVT'
2052 );
2053 WHEN OTHERS THEN
2054 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2055 (
2056 l_api_name,
2057 G_PKG_NAME,
2058 'OTHERS',
2059 x_msg_count,
2060 x_msg_data,
2061 '_PVT'
2062 );
2063 END lock_row;
2064 --------------------------------------
2065 -- PL/SQL TBL lock_row for:SCHV_TBL --
2066 --------------------------------------
2067 PROCEDURE lock_row(
2068 p_api_version IN NUMBER,
2069 p_init_msg_list IN VARCHAR2,
2070 x_return_status OUT NOCOPY VARCHAR2,
2071 x_msg_count OUT NOCOPY NUMBER,
2072 x_msg_data OUT NOCOPY VARCHAR2,
2073 p_schv_tbl IN schv_tbl_type,
2074 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2075
2076 l_api_version CONSTANT NUMBER := 1;
2077 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2078 i NUMBER := 0;
2079 BEGIN
2080 OKC_API.init_msg_list(p_init_msg_list);
2081 -- Make sure PL/SQL table has recrods in it before passing
2082 IF (p_schv_tbl.COUNT > 0) THEN
2083 i := p_schv_tbl.FIRST;
2084 LOOP
2085 DECLARE
2086 l_error_rec OKC_API.ERROR_REC_TYPE;
2087 BEGIN
2088 l_error_rec.api_name := l_api_name;
2089 l_error_rec.api_package := G_PKG_NAME;
2090 l_error_rec.idx := i;
2091 lock_row(
2092 p_api_version => p_api_version,
2093 p_init_msg_list => OKC_API.G_FALSE,
2094 x_return_status => l_error_rec.error_type,
2095 x_msg_count => l_error_rec.msg_count,
2096 x_msg_data => l_error_rec.msg_data,
2097 p_schv_rec => p_schv_tbl(i));
2098 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2099 l_error_rec.sqlcode := SQLCODE;
2100 load_error_tbl(l_error_rec, px_error_tbl);
2101 ELSE
2102 x_msg_count := l_error_rec.msg_count;
2103 x_msg_data := l_error_rec.msg_data;
2104 END IF;
2105 EXCEPTION
2106 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2107 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2108 l_error_rec.sqlcode := SQLCODE;
2109 load_error_tbl(l_error_rec, px_error_tbl);
2110 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2111 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2112 l_error_rec.sqlcode := SQLCODE;
2113 load_error_tbl(l_error_rec, px_error_tbl);
2114 WHEN OTHERS THEN
2115 l_error_rec.error_type := 'OTHERS';
2116 l_error_rec.sqlcode := SQLCODE;
2117 load_error_tbl(l_error_rec, px_error_tbl);
2118 END;
2119 EXIT WHEN (i = p_schv_tbl.LAST);
2120 i := p_schv_tbl.NEXT(i);
2121 END LOOP;
2122 END IF;
2123 -- Loop through the error_tbl to find the error with the highest severity
2124 -- and return it.
2125 x_return_status := find_highest_exception(px_error_tbl);
2126 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2127 EXCEPTION
2128 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2129 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2130 (
2131 l_api_name,
2132 G_PKG_NAME,
2133 'OKC_API.G_RET_STS_ERROR',
2134 x_msg_count,
2135 x_msg_data,
2136 '_PVT'
2137 );
2138 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2139 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2140 (
2141 l_api_name,
2142 G_PKG_NAME,
2143 'OKC_API.G_RET_STS_UNEXP_ERROR',
2144 x_msg_count,
2145 x_msg_data,
2146 '_PVT'
2147 );
2148 WHEN OTHERS THEN
2149 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2150 (
2151 l_api_name,
2152 G_PKG_NAME,
2153 'OTHERS',
2154 x_msg_count,
2155 x_msg_data,
2156 '_PVT'
2157 );
2158 END lock_row;
2159 --------------------------------------
2160 -- PL/SQL TBL lock_row for:SCHV_TBL --
2161 --------------------------------------
2162 PROCEDURE lock_row(
2163 p_api_version IN NUMBER,
2164 p_init_msg_list IN VARCHAR2,
2165 x_return_status OUT NOCOPY VARCHAR2,
2166 x_msg_count OUT NOCOPY NUMBER,
2167 x_msg_data OUT NOCOPY VARCHAR2,
2168 p_schv_tbl IN schv_tbl_type) IS
2169
2170 l_api_version CONSTANT NUMBER := 1;
2171 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2172 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2173 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2174 BEGIN
2175 OKC_API.init_msg_list(p_init_msg_list);
2176 -- Make sure PL/SQL table has recrods in it before passing
2177 IF (p_schv_tbl.COUNT > 0) THEN
2178 lock_row(
2179 p_api_version => p_api_version,
2180 p_init_msg_list => OKC_API.G_FALSE,
2181 x_return_status => x_return_status,
2182 x_msg_count => x_msg_count,
2183 x_msg_data => x_msg_data,
2184 p_schv_tbl => p_schv_tbl,
2185 px_error_tbl => l_error_tbl);
2186 END IF;
2187 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2188 EXCEPTION
2189 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2190 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2191 (
2192 l_api_name,
2193 G_PKG_NAME,
2194 'OKC_API.G_RET_STS_ERROR',
2195 x_msg_count,
2196 x_msg_data,
2197 '_PVT'
2198 );
2199 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2200 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2201 (
2202 l_api_name,
2203 G_PKG_NAME,
2204 'OKC_API.G_RET_STS_UNEXP_ERROR',
2205 x_msg_count,
2206 x_msg_data,
2207 '_PVT'
2208 );
2209 WHEN OTHERS THEN
2210 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2211 (
2212 l_api_name,
2213 G_PKG_NAME,
2214 'OTHERS',
2215 x_msg_count,
2216 x_msg_data,
2217 '_PVT'
2218 );
2219 END lock_row;
2220 ---------------------------------------------------------------------------
2221 -- PROCEDURE update_row
2222 ---------------------------------------------------------------------------
2223 ----------------------------------------
2224 -- update_row for:OKS_SUBSCR_HEADER_B --
2225 ----------------------------------------
2226 PROCEDURE update_row(
2227 p_init_msg_list IN VARCHAR2,
2228 x_return_status OUT NOCOPY VARCHAR2,
2229 x_msg_count OUT NOCOPY NUMBER,
2230 x_msg_data OUT NOCOPY VARCHAR2,
2231 p_sbh_rec IN sbh_rec_type,
2232 x_sbh_rec OUT NOCOPY sbh_rec_type) IS
2233
2234 l_api_version CONSTANT NUMBER := 1;
2235 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2236 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2237 l_sbh_rec sbh_rec_type := p_sbh_rec;
2238 l_def_sbh_rec sbh_rec_type;
2239 l_row_notfound BOOLEAN := TRUE;
2240 ----------------------------------
2241 -- FUNCTION populate_new_record --
2242 ----------------------------------
2243 FUNCTION populate_new_record (
2244 p_sbh_rec IN sbh_rec_type,
2245 x_sbh_rec OUT NOCOPY sbh_rec_type
2246 ) RETURN VARCHAR2 IS
2247 l_sbh_rec sbh_rec_type;
2248 l_row_notfound BOOLEAN := TRUE;
2249 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2250 BEGIN
2251 x_sbh_rec := p_sbh_rec;
2252 -- Get current database values
2253 l_sbh_rec := get_rec(p_sbh_rec, l_return_status);
2254 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2255 IF (x_sbh_rec.id = OKC_API.G_MISS_NUM)
2256 THEN
2257 x_sbh_rec.id := l_sbh_rec.id;
2258 END IF;
2259 IF (x_sbh_rec.cle_id = OKC_API.G_MISS_NUM)
2260 THEN
2261 x_sbh_rec.cle_id := l_sbh_rec.cle_id;
2262 END IF;
2263 IF (x_sbh_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2264 THEN
2265 x_sbh_rec.dnz_chr_id := l_sbh_rec.dnz_chr_id;
2266 END IF;
2267 IF (x_sbh_rec.instance_id = OKC_API.G_MISS_NUM)
2268 THEN
2269 x_sbh_rec.instance_id := l_sbh_rec.instance_id;
2270 END IF;
2271 IF (x_sbh_rec.subscription_type = OKC_API.G_MISS_CHAR)
2272 THEN
2273 x_sbh_rec.subscription_type := l_sbh_rec.subscription_type;
2274 END IF;
2275 IF (x_sbh_rec.item_type = OKC_API.G_MISS_CHAR)
2276 THEN
2277 x_sbh_rec.item_type := l_sbh_rec.item_type;
2278 END IF;
2279 IF (x_sbh_rec.media_type = OKC_API.G_MISS_CHAR)
2280 THEN
2281 x_sbh_rec.media_type := l_sbh_rec.media_type;
2282 END IF;
2283 IF (x_sbh_rec.status = OKC_API.G_MISS_CHAR)
2284 THEN
2285 x_sbh_rec.status := l_sbh_rec.status;
2286 END IF;
2287 IF (x_sbh_rec.frequency = OKC_API.G_MISS_CHAR)
2288 THEN
2289 x_sbh_rec.frequency := l_sbh_rec.frequency;
2290 END IF;
2291 IF (x_sbh_rec.fulfillment_channel = OKC_API.G_MISS_CHAR)
2292 THEN
2293 x_sbh_rec.fulfillment_channel := l_sbh_rec.fulfillment_channel;
2294 END IF;
2295 IF (x_sbh_rec.offset = OKC_API.G_MISS_NUM)
2296 THEN
2297 x_sbh_rec.offset := l_sbh_rec.offset;
2298 END IF;
2299 IF (x_sbh_rec.upg_orig_system_ref = OKC_API.G_MISS_CHAR)
2300 THEN
2301 x_sbh_rec.upg_orig_system_ref := l_sbh_rec.upg_orig_system_ref;
2302 END IF;
2303 IF (x_sbh_rec.upg_orig_system_ref_id = OKC_API.G_MISS_NUM)
2304 THEN
2305 x_sbh_rec.upg_orig_system_ref_id := l_sbh_rec.upg_orig_system_ref_id;
2306 END IF;
2307 IF (x_sbh_rec.object_version_number = OKC_API.G_MISS_NUM)
2308 THEN
2309 x_sbh_rec.object_version_number := l_sbh_rec.object_version_number;
2310 END IF;
2311 IF (x_sbh_rec.created_by = OKC_API.G_MISS_NUM)
2312 THEN
2313 x_sbh_rec.created_by := l_sbh_rec.created_by;
2314 END IF;
2315 IF (x_sbh_rec.creation_date = OKC_API.G_MISS_DATE)
2316 THEN
2317 x_sbh_rec.creation_date := l_sbh_rec.creation_date;
2318 END IF;
2319 IF (x_sbh_rec.last_updated_by = OKC_API.G_MISS_NUM)
2320 THEN
2321 x_sbh_rec.last_updated_by := l_sbh_rec.last_updated_by;
2322 END IF;
2323 IF (x_sbh_rec.last_update_date = OKC_API.G_MISS_DATE)
2324 THEN
2325 x_sbh_rec.last_update_date := l_sbh_rec.last_update_date;
2326 END IF;
2327 IF (x_sbh_rec.last_update_login = OKC_API.G_MISS_NUM)
2328 THEN
2329 x_sbh_rec.last_update_login := l_sbh_rec.last_update_login;
2330 END IF;
2331
2332 /*** R12 Data Model Changes Start 27072005***/
2333
2334 IF (x_sbh_rec.orig_system_id1 = OKC_API.G_MISS_NUM)
2335 THEN
2336 x_sbh_rec.orig_system_id1 := l_sbh_rec.orig_system_id1;
2337 END IF;
2338 IF (x_sbh_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR)
2339 THEN
2340 x_sbh_rec.orig_system_reference1 := l_sbh_rec.orig_system_reference1;
2341 END IF;
2342 IF (x_sbh_rec.orig_system_source_code = OKC_API.G_MISS_CHAR)
2343 THEN
2344 x_sbh_rec.orig_system_source_code := l_sbh_rec.orig_system_source_code;
2345 END IF;
2346
2347 /*** R12 Data Model Changes End 27072005 ***/
2348
2349 END IF;
2350 RETURN(l_return_status);
2351 END populate_new_record;
2352 --------------------------------------------
2353 -- Set_Attributes for:OKS_SUBSCR_HEADER_B --
2354 --------------------------------------------
2355 FUNCTION Set_Attributes (
2356 p_sbh_rec IN sbh_rec_type,
2357 x_sbh_rec OUT NOCOPY sbh_rec_type
2358 ) RETURN VARCHAR2 IS
2359 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2360 BEGIN
2361 x_sbh_rec := p_sbh_rec;
2362 x_sbh_rec.OBJECT_VERSION_NUMBER := p_sbh_rec.OBJECT_VERSION_NUMBER + 1;
2363 RETURN(l_return_status);
2364 END Set_Attributes;
2365 BEGIN
2366 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2367 p_init_msg_list,
2368 '_PVT',
2369 x_return_status);
2370 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2371 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2372 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2373 RAISE OKC_API.G_EXCEPTION_ERROR;
2374 END IF;
2375 --- Setting item attributes
2376 l_return_status := Set_Attributes(
2377 p_sbh_rec, -- IN
2378 l_sbh_rec); -- OUT
2379 --- If any errors happen abort API
2380 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2381 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2382 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2383 RAISE OKC_API.G_EXCEPTION_ERROR;
2384 END IF;
2385 l_return_status := populate_new_record(l_sbh_rec, l_def_sbh_rec);
2386 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2387 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2388 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2389 RAISE OKC_API.G_EXCEPTION_ERROR;
2390 END IF;
2391 UPDATE OKS_SUBSCR_HEADER_B
2392 SET CLE_ID = l_def_sbh_rec.cle_id,
2393 DNZ_CHR_ID = l_def_sbh_rec.dnz_chr_id,
2394 INSTANCE_ID = l_def_sbh_rec.instance_id,
2395 SUBSCRIPTION_TYPE = l_def_sbh_rec.subscription_type,
2396 ITEM_TYPE = l_def_sbh_rec.item_type,
2397 MEDIA_TYPE = l_def_sbh_rec.media_type,
2398 STATUS = l_def_sbh_rec.status,
2399 FREQUENCY = l_def_sbh_rec.frequency,
2400 FULFILLMENT_CHANNEL = l_def_sbh_rec.fulfillment_channel,
2401 OFFSET = l_def_sbh_rec.offset,
2402 UPG_ORIG_SYSTEM_REF = l_def_sbh_rec.upg_orig_system_ref,
2403 UPG_ORIG_SYSTEM_REF_ID = l_def_sbh_rec.upg_orig_system_ref_id,
2404 OBJECT_VERSION_NUMBER = l_def_sbh_rec.object_version_number,
2405 CREATED_BY = l_def_sbh_rec.created_by,
2406 CREATION_DATE = l_def_sbh_rec.creation_date,
2407 LAST_UPDATED_BY = l_def_sbh_rec.last_updated_by,
2408 LAST_UPDATE_DATE = l_def_sbh_rec.last_update_date,
2409 LAST_UPDATE_LOGIN = l_def_sbh_rec.last_update_login,
2410 -- R12 Data Model Changes 4485150 Start
2411 ORIG_SYSTEM_ID1 = l_def_sbh_rec.orig_system_id1,
2412 ORIG_SYSTEM_REFERENCE1 = l_def_sbh_rec.orig_system_reference1,
2413 ORIG_SYSTEM_SOURCE_CODE = l_def_sbh_rec.orig_system_source_code
2414 -- R12 Data Model Changes 4485150 End
2415 WHERE ID = l_def_sbh_rec.id;
2416
2417 x_sbh_rec := l_sbh_rec;
2418 x_return_status := l_return_status;
2419 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2420 EXCEPTION
2421 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2422 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2423 (
2424 l_api_name,
2425 G_PKG_NAME,
2426 'OKC_API.G_RET_STS_ERROR',
2427 x_msg_count,
2428 x_msg_data,
2429 '_PVT'
2430 );
2431 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2432 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2433 (
2434 l_api_name,
2435 G_PKG_NAME,
2436 'OKC_API.G_RET_STS_UNEXP_ERROR',
2437 x_msg_count,
2438 x_msg_data,
2439 '_PVT'
2440 );
2441 WHEN OTHERS THEN
2442 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2443 (
2444 l_api_name,
2445 G_PKG_NAME,
2446 'OTHERS',
2447 x_msg_count,
2448 x_msg_data,
2449 '_PVT'
2450 );
2451 END update_row;
2452 -----------------------------------------
2453 -- update_row for:OKS_SUBSCR_HEADER_TL --
2454 -----------------------------------------
2455 PROCEDURE update_row(
2456 p_init_msg_list IN VARCHAR2,
2457 x_return_status OUT NOCOPY VARCHAR2,
2458 x_msg_count OUT NOCOPY NUMBER,
2459 x_msg_data OUT NOCOPY VARCHAR2,
2460 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type,
2461 x_oks_subscr_header_tl_rec OUT NOCOPY oks_subscr_header_tl_rec_type) IS
2462
2463 l_api_version CONSTANT NUMBER := 1;
2464 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2465 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2466 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type := p_oks_subscr_header_tl_rec;
2467 l_def_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
2468 l_row_notfound BOOLEAN := TRUE;
2469 ----------------------------------
2470 -- FUNCTION populate_new_record --
2471 ----------------------------------
2472 FUNCTION populate_new_record (
2473 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type,
2474 x_oks_subscr_header_tl_rec OUT NOCOPY oks_subscr_header_tl_rec_type
2475 ) RETURN VARCHAR2 IS
2476 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
2477 l_row_notfound BOOLEAN := TRUE;
2478 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2479 BEGIN
2480 x_oks_subscr_header_tl_rec := p_oks_subscr_header_tl_rec;
2481 -- Get current database values
2482 l_oks_subscr_header_tl_rec := get_rec(p_oks_subscr_header_tl_rec, l_return_status);
2483 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2484 IF (x_oks_subscr_header_tl_rec.id = OKC_API.G_MISS_NUM)
2485 THEN
2486 x_oks_subscr_header_tl_rec.id := l_oks_subscr_header_tl_rec.id;
2487 END IF;
2488 IF (x_oks_subscr_header_tl_rec.name = OKC_API.G_MISS_CHAR)
2489 THEN
2490 x_oks_subscr_header_tl_rec.name := l_oks_subscr_header_tl_rec.name;
2491 END IF;
2492 IF (x_oks_subscr_header_tl_rec.description = OKC_API.G_MISS_CHAR)
2493 THEN
2494 x_oks_subscr_header_tl_rec.description := l_oks_subscr_header_tl_rec.description;
2495 END IF;
2496 IF (x_oks_subscr_header_tl_rec.language = OKC_API.G_MISS_CHAR)
2497 THEN
2498 x_oks_subscr_header_tl_rec.language := l_oks_subscr_header_tl_rec.language;
2499 END IF;
2500 IF (x_oks_subscr_header_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2501 THEN
2502 x_oks_subscr_header_tl_rec.source_lang := l_oks_subscr_header_tl_rec.source_lang;
2503 END IF;
2504 IF (x_oks_subscr_header_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2505 THEN
2506 x_oks_subscr_header_tl_rec.sfwt_flag := l_oks_subscr_header_tl_rec.sfwt_flag;
2507 END IF;
2508 IF (x_oks_subscr_header_tl_rec.comments = OKC_API.G_MISS_CHAR)
2509 THEN
2510 x_oks_subscr_header_tl_rec.comments := l_oks_subscr_header_tl_rec.comments;
2511 END IF;
2512 IF (x_oks_subscr_header_tl_rec.created_by = OKC_API.G_MISS_NUM)
2513 THEN
2514 x_oks_subscr_header_tl_rec.created_by := l_oks_subscr_header_tl_rec.created_by;
2515 END IF;
2516 IF (x_oks_subscr_header_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2517 THEN
2518 x_oks_subscr_header_tl_rec.creation_date := l_oks_subscr_header_tl_rec.creation_date;
2519 END IF;
2520 IF (x_oks_subscr_header_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2521 THEN
2522 x_oks_subscr_header_tl_rec.last_updated_by := l_oks_subscr_header_tl_rec.last_updated_by;
2523 END IF;
2524 IF (x_oks_subscr_header_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2525 THEN
2526 x_oks_subscr_header_tl_rec.last_update_date := l_oks_subscr_header_tl_rec.last_update_date;
2527 END IF;
2528 IF (x_oks_subscr_header_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2529 THEN
2530 x_oks_subscr_header_tl_rec.last_update_login := l_oks_subscr_header_tl_rec.last_update_login;
2531 END IF;
2532 END IF;
2533 RETURN(l_return_status);
2534 END populate_new_record;
2535 ---------------------------------------------
2536 -- Set_Attributes for:OKS_SUBSCR_HEADER_TL --
2537 ---------------------------------------------
2538 FUNCTION Set_Attributes (
2539 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type,
2540 x_oks_subscr_header_tl_rec OUT NOCOPY oks_subscr_header_tl_rec_type
2541 ) RETURN VARCHAR2 IS
2542 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2543 BEGIN
2544 x_oks_subscr_header_tl_rec := p_oks_subscr_header_tl_rec;
2545 x_oks_subscr_header_tl_rec.LANGUAGE := USERENV('LANG');
2546 x_oks_subscr_header_tl_rec.LANGUAGE := USERENV('LANG');
2547 RETURN(l_return_status);
2548 END Set_Attributes;
2549 BEGIN
2550 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2551 p_init_msg_list,
2552 '_PVT',
2553 x_return_status);
2554 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2555 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2556 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2557 RAISE OKC_API.G_EXCEPTION_ERROR;
2558 END IF;
2559 --- Setting item attributes
2560 l_return_status := Set_Attributes(
2561 p_oks_subscr_header_tl_rec, -- IN
2562 l_oks_subscr_header_tl_rec); -- OUT
2563 --- If any errors happen abort API
2564 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2565 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2566 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2567 RAISE OKC_API.G_EXCEPTION_ERROR;
2568 END IF;
2569 l_return_status := populate_new_record(l_oks_subscr_header_tl_rec, l_def_oks_subscr_header_tl_rec);
2570 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2571 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2572 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2573 RAISE OKC_API.G_EXCEPTION_ERROR;
2574 END IF;
2575 UPDATE OKS_SUBSCR_HEADER_TL
2576 SET NAME = l_def_oks_subscr_header_tl_rec.name,
2577 DESCRIPTION = l_def_oks_subscr_header_tl_rec.description,
2578 COMMENTS = l_def_oks_subscr_header_tl_rec.comments,
2579 CREATED_BY = l_def_oks_subscr_header_tl_rec.created_by,
2580 CREATION_DATE = l_def_oks_subscr_header_tl_rec.creation_date,
2581 LAST_UPDATED_BY = l_def_oks_subscr_header_tl_rec.last_updated_by,
2582 LAST_UPDATE_DATE = l_def_oks_subscr_header_tl_rec.last_update_date,
2583 LAST_UPDATE_LOGIN = l_def_oks_subscr_header_tl_rec.last_update_login
2584 WHERE ID = l_def_oks_subscr_header_tl_rec.id
2585 AND SOURCE_LANG = USERENV('LANG');
2586
2587 UPDATE OKS_SUBSCR_HEADER_TL
2588 SET SFWT_FLAG = 'Y'
2589 WHERE ID = l_def_oks_subscr_header_tl_rec.id
2590 AND SOURCE_LANG <> USERENV('LANG');
2591
2592 x_oks_subscr_header_tl_rec := l_oks_subscr_header_tl_rec;
2593 x_return_status := l_return_status;
2594 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2595 EXCEPTION
2596 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2597 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2598 (
2599 l_api_name,
2600 G_PKG_NAME,
2601 'OKC_API.G_RET_STS_ERROR',
2602 x_msg_count,
2603 x_msg_data,
2604 '_PVT'
2605 );
2606 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2607 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2608 (
2609 l_api_name,
2610 G_PKG_NAME,
2611 'OKC_API.G_RET_STS_UNEXP_ERROR',
2612 x_msg_count,
2613 x_msg_data,
2614 '_PVT'
2615 );
2616 WHEN OTHERS THEN
2617 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2618 (
2619 l_api_name,
2620 G_PKG_NAME,
2621 'OTHERS',
2622 x_msg_count,
2623 x_msg_data,
2624 '_PVT'
2625 );
2626 END update_row;
2627 ----------------------------------------
2628 -- update_row for:OKS_SUBSCR_HEADER_V --
2629 ----------------------------------------
2630 PROCEDURE update_row(
2631 p_api_version IN NUMBER,
2632 p_init_msg_list IN VARCHAR2,
2633 x_return_status OUT NOCOPY VARCHAR2,
2634 x_msg_count OUT NOCOPY NUMBER,
2635 x_msg_data OUT NOCOPY VARCHAR2,
2636 p_schv_rec IN schv_rec_type,
2637 x_schv_rec OUT NOCOPY schv_rec_type) IS
2638
2639 l_api_version CONSTANT NUMBER := 1;
2640 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2641 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2642 l_schv_rec schv_rec_type := p_schv_rec;
2643 l_def_schv_rec schv_rec_type;
2644 l_db_schv_rec schv_rec_type;
2645 l_sbh_rec sbh_rec_type;
2646 lx_sbh_rec sbh_rec_type;
2647 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
2648 lx_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
2649 -------------------------------
2650 -- FUNCTION fill_who_columns --
2651 -------------------------------
2652 FUNCTION fill_who_columns (
2653 p_schv_rec IN schv_rec_type
2654 ) RETURN schv_rec_type IS
2655 l_schv_rec schv_rec_type := p_schv_rec;
2656 BEGIN
2657 l_schv_rec.LAST_UPDATE_DATE := SYSDATE;
2658 l_schv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2659 l_schv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2660 RETURN(l_schv_rec);
2661 END fill_who_columns;
2662 ----------------------------------
2663 -- FUNCTION populate_new_record --
2664 ----------------------------------
2665 FUNCTION populate_new_record (
2666 p_schv_rec IN schv_rec_type,
2667 x_schv_rec OUT NOCOPY schv_rec_type
2668 ) RETURN VARCHAR2 IS
2669 l_row_notfound BOOLEAN := TRUE;
2670 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2671 BEGIN
2672 x_schv_rec := p_schv_rec;
2673 -- Get current database values
2674 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2675 -- so it may be verified through LOCK_ROW.
2676 l_db_schv_rec := get_rec(p_schv_rec, l_return_status);
2677 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2678 IF (x_schv_rec.id = OKC_API.G_MISS_NUM)
2679 THEN
2680 x_schv_rec.id := l_db_schv_rec.id;
2681 END IF;
2682 IF (x_schv_rec.name = OKC_API.G_MISS_CHAR)
2683 THEN
2684 x_schv_rec.name := l_db_schv_rec.name;
2685 END IF;
2686 IF (x_schv_rec.description = OKC_API.G_MISS_CHAR)
2687 THEN
2688 x_schv_rec.description := l_db_schv_rec.description;
2689 END IF;
2690 IF (x_schv_rec.cle_id = OKC_API.G_MISS_NUM)
2691 THEN
2692 x_schv_rec.cle_id := l_db_schv_rec.cle_id;
2693 END IF;
2694 IF (x_schv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2695 THEN
2696 x_schv_rec.dnz_chr_id := l_db_schv_rec.dnz_chr_id;
2697 END IF;
2698 IF (x_schv_rec.instance_id = OKC_API.G_MISS_NUM)
2699 THEN
2700 x_schv_rec.instance_id := l_db_schv_rec.instance_id;
2701 END IF;
2702 IF (x_schv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2703 THEN
2704 x_schv_rec.sfwt_flag := l_db_schv_rec.sfwt_flag;
2705 END IF;
2706 IF (x_schv_rec.subscription_type = OKC_API.G_MISS_CHAR)
2707 THEN
2708 x_schv_rec.subscription_type := l_db_schv_rec.subscription_type;
2709 END IF;
2710 IF (x_schv_rec.item_type = OKC_API.G_MISS_CHAR)
2711 THEN
2712 x_schv_rec.item_type := l_db_schv_rec.item_type;
2713 END IF;
2714 IF (x_schv_rec.media_type = OKC_API.G_MISS_CHAR)
2715 THEN
2716 x_schv_rec.media_type := l_db_schv_rec.media_type;
2717 END IF;
2718 IF (x_schv_rec.status = OKC_API.G_MISS_CHAR)
2719 THEN
2720 x_schv_rec.status := l_db_schv_rec.status;
2721 END IF;
2722 IF (x_schv_rec.frequency = OKC_API.G_MISS_CHAR)
2723 THEN
2724 x_schv_rec.frequency := l_db_schv_rec.frequency;
2725 END IF;
2726 IF (x_schv_rec.fulfillment_channel = OKC_API.G_MISS_CHAR)
2727 THEN
2728 x_schv_rec.fulfillment_channel := l_db_schv_rec.fulfillment_channel;
2729 END IF;
2730 IF (x_schv_rec.offset = OKC_API.G_MISS_NUM)
2731 THEN
2732 x_schv_rec.offset := l_db_schv_rec.offset;
2733 END IF;
2734 IF (x_schv_rec.comments = OKC_API.G_MISS_CHAR)
2735 THEN
2736 x_schv_rec.comments := l_db_schv_rec.comments;
2737 END IF;
2738 IF (x_schv_rec.upg_orig_system_ref = OKC_API.G_MISS_CHAR)
2739 THEN
2740 x_schv_rec.upg_orig_system_ref := l_db_schv_rec.upg_orig_system_ref;
2741 END IF;
2742 IF (x_schv_rec.upg_orig_system_ref_id = OKC_API.G_MISS_NUM)
2743 THEN
2744 x_schv_rec.upg_orig_system_ref_id := l_db_schv_rec.upg_orig_system_ref_id;
2745 END IF;
2746 IF (x_schv_rec.created_by = OKC_API.G_MISS_NUM)
2747 THEN
2748 x_schv_rec.created_by := l_db_schv_rec.created_by;
2749 END IF;
2750 IF (x_schv_rec.creation_date = OKC_API.G_MISS_DATE)
2751 THEN
2752 x_schv_rec.creation_date := l_db_schv_rec.creation_date;
2753 END IF;
2754 IF (x_schv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2755 THEN
2756 x_schv_rec.last_updated_by := l_db_schv_rec.last_updated_by;
2757 END IF;
2758 IF (x_schv_rec.last_update_date = OKC_API.G_MISS_DATE)
2759 THEN
2760 x_schv_rec.last_update_date := l_db_schv_rec.last_update_date;
2761 END IF;
2762 IF (x_schv_rec.last_update_login = OKC_API.G_MISS_NUM)
2763 THEN
2764 x_schv_rec.last_update_login := l_db_schv_rec.last_update_login;
2765 END IF;
2766 END IF;
2767 RETURN(l_return_status);
2768 END populate_new_record;
2769 --------------------------------------------
2770 -- Set_Attributes for:OKS_SUBSCR_HEADER_V --
2771 --------------------------------------------
2772 FUNCTION Set_Attributes (
2773 p_schv_rec IN schv_rec_type,
2774 x_schv_rec OUT NOCOPY schv_rec_type
2775 ) RETURN VARCHAR2 IS
2776 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2777 BEGIN
2778 x_schv_rec := p_schv_rec;
2779 RETURN(l_return_status);
2780 END Set_Attributes;
2781 BEGIN
2782 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2783 G_PKG_NAME,
2784 p_init_msg_list,
2785 l_api_version,
2786 p_api_version,
2787 '_PVT',
2788 x_return_status);
2789 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2791 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2792 RAISE OKC_API.G_EXCEPTION_ERROR;
2793 END IF;
2794 --- Setting item attributes
2795 l_return_status := Set_Attributes(
2796 p_schv_rec, -- IN
2797 x_schv_rec); -- OUT
2798 --- If any errors happen abort API
2799 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2800 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2801 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2802 RAISE OKC_API.G_EXCEPTION_ERROR;
2803 END IF;
2804 l_return_status := populate_new_record(l_schv_rec, l_def_schv_rec);
2805 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2806 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2807 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2808 RAISE OKC_API.G_EXCEPTION_ERROR;
2809 END IF;
2810 l_def_schv_rec := fill_who_columns(l_def_schv_rec);
2811 --- Validate all non-missing attributes (Item Level Validation)
2812 l_return_status := Validate_Attributes(l_def_schv_rec);
2813 --- If any errors happen abort API
2814 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2815 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2816 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2817 RAISE OKC_API.G_EXCEPTION_ERROR;
2818 END IF;
2819 l_return_status := Validate_Record(l_def_schv_rec, l_db_schv_rec);
2820 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2821 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2822 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2823 RAISE OKC_API.G_EXCEPTION_ERROR;
2824 END IF;
2825
2826 -- Lock the Record
2827 lock_row(
2828 p_api_version => p_api_version,
2829 p_init_msg_list => p_init_msg_list,
2830 x_return_status => l_return_status,
2831 x_msg_count => x_msg_count,
2832 x_msg_data => x_msg_data,
2833 p_schv_rec => p_schv_rec);
2834 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2835 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2836 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2837 RAISE OKC_API.G_EXCEPTION_ERROR;
2838 END IF;
2839
2840 -----------------------------------------
2841 -- Move VIEW record to "Child" records --
2842 -----------------------------------------
2843 migrate(l_def_schv_rec, l_sbh_rec);
2844 migrate(l_def_schv_rec, l_oks_subscr_header_tl_rec);
2845 -----------------------------------------------
2846 -- Call the UPDATE_ROW for each child record --
2847 -----------------------------------------------
2848 update_row(
2849 p_init_msg_list,
2850 l_return_status,
2851 x_msg_count,
2852 x_msg_data,
2853 l_sbh_rec,
2854 lx_sbh_rec
2855 );
2856 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2857 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2858 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2859 RAISE OKC_API.G_EXCEPTION_ERROR;
2860 END IF;
2861 migrate(lx_sbh_rec, l_def_schv_rec);
2862 update_row(
2863 p_init_msg_list,
2864 l_return_status,
2865 x_msg_count,
2866 x_msg_data,
2867 l_oks_subscr_header_tl_rec,
2868 lx_oks_subscr_header_tl_rec
2869 );
2870 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2871 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2872 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2873 RAISE OKC_API.G_EXCEPTION_ERROR;
2874 END IF;
2875 migrate(lx_oks_subscr_header_tl_rec, l_def_schv_rec);
2876 x_schv_rec := l_def_schv_rec;
2877 x_return_status := l_return_status;
2878 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2879 EXCEPTION
2880 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2881 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2882 (
2883 l_api_name,
2884 G_PKG_NAME,
2885 'OKC_API.G_RET_STS_ERROR',
2886 x_msg_count,
2887 x_msg_data,
2888 '_PVT'
2889 );
2890 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2891 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2892 (
2893 l_api_name,
2894 G_PKG_NAME,
2895 'OKC_API.G_RET_STS_UNEXP_ERROR',
2896 x_msg_count,
2897 x_msg_data,
2898 '_PVT'
2899 );
2900 WHEN OTHERS THEN
2901 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2902 (
2903 l_api_name,
2904 G_PKG_NAME,
2905 'OTHERS',
2906 x_msg_count,
2907 x_msg_data,
2908 '_PVT'
2909 );
2910 END update_row;
2911 ----------------------------------------
2912 -- PL/SQL TBL update_row for:schv_tbl --
2913 ----------------------------------------
2914 PROCEDURE update_row(
2915 p_api_version IN NUMBER,
2916 p_init_msg_list IN VARCHAR2,
2917 x_return_status OUT NOCOPY VARCHAR2,
2918 x_msg_count OUT NOCOPY NUMBER,
2919 x_msg_data OUT NOCOPY VARCHAR2,
2920 p_schv_tbl IN schv_tbl_type,
2921 x_schv_tbl OUT NOCOPY schv_tbl_type,
2922 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2923
2924 l_api_version CONSTANT NUMBER := 1;
2925 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2926 i NUMBER := 0;
2927 BEGIN
2928 OKC_API.init_msg_list(p_init_msg_list);
2929 -- Make sure PL/SQL table has records in it before passing
2930 IF (p_schv_tbl.COUNT > 0) THEN
2931 i := p_schv_tbl.FIRST;
2932 LOOP
2933 DECLARE
2934 l_error_rec OKC_API.ERROR_REC_TYPE;
2935 BEGIN
2936 l_error_rec.api_name := l_api_name;
2937 l_error_rec.api_package := G_PKG_NAME;
2938 l_error_rec.idx := i;
2939 update_row (
2940 p_api_version => p_api_version,
2941 p_init_msg_list => OKC_API.G_FALSE,
2942 x_return_status => l_error_rec.error_type,
2943 x_msg_count => l_error_rec.msg_count,
2944 x_msg_data => l_error_rec.msg_data,
2945 p_schv_rec => p_schv_tbl(i),
2946 x_schv_rec => x_schv_tbl(i));
2947 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2948 l_error_rec.sqlcode := SQLCODE;
2949 load_error_tbl(l_error_rec, px_error_tbl);
2950 ELSE
2951 x_msg_count := l_error_rec.msg_count;
2952 x_msg_data := l_error_rec.msg_data;
2953 END IF;
2954 EXCEPTION
2955 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2956 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2957 l_error_rec.sqlcode := SQLCODE;
2958 load_error_tbl(l_error_rec, px_error_tbl);
2959 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2960 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2961 l_error_rec.sqlcode := SQLCODE;
2962 load_error_tbl(l_error_rec, px_error_tbl);
2963 WHEN OTHERS THEN
2964 l_error_rec.error_type := 'OTHERS';
2965 l_error_rec.sqlcode := SQLCODE;
2966 load_error_tbl(l_error_rec, px_error_tbl);
2967 END;
2968 EXIT WHEN (i = p_schv_tbl.LAST);
2969 i := p_schv_tbl.NEXT(i);
2970 END LOOP;
2971 END IF;
2972 -- Loop through the error_tbl to find the error with the highest severity
2973 -- and return it.
2974 x_return_status := find_highest_exception(px_error_tbl);
2975 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2976 EXCEPTION
2977 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2978 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2979 (
2980 l_api_name,
2981 G_PKG_NAME,
2982 'OKC_API.G_RET_STS_ERROR',
2983 x_msg_count,
2984 x_msg_data,
2985 '_PVT'
2986 );
2987 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2988 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2989 (
2990 l_api_name,
2991 G_PKG_NAME,
2992 'OKC_API.G_RET_STS_UNEXP_ERROR',
2993 x_msg_count,
2994 x_msg_data,
2995 '_PVT'
2996 );
2997 WHEN OTHERS THEN
2998 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2999 (
3000 l_api_name,
3001 G_PKG_NAME,
3002 'OTHERS',
3003 x_msg_count,
3004 x_msg_data,
3005 '_PVT'
3006 );
3007 END update_row;
3008
3009 ----------------------------------------
3010 -- PL/SQL TBL update_row for:SCHV_TBL --
3011 ----------------------------------------
3012 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
3013 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
3014 PROCEDURE update_row(
3015 p_api_version IN NUMBER,
3016 p_init_msg_list IN VARCHAR2,
3017 x_return_status OUT NOCOPY VARCHAR2,
3018 x_msg_count OUT NOCOPY NUMBER,
3019 x_msg_data OUT NOCOPY VARCHAR2,
3020 p_schv_tbl IN schv_tbl_type,
3021 x_schv_tbl OUT NOCOPY schv_tbl_type) IS
3022
3023 l_api_version CONSTANT NUMBER := 1;
3024 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3025 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3026 l_error_tbl OKC_API.ERROR_TBL_TYPE;
3027 BEGIN
3028 OKC_API.init_msg_list(p_init_msg_list);
3029 -- Make sure PL/SQL table has records in it before passing
3030 IF (p_schv_tbl.COUNT > 0) THEN
3031 update_row (
3032 p_api_version => p_api_version,
3033 p_init_msg_list => OKC_API.G_FALSE,
3034 x_return_status => x_return_status,
3035 x_msg_count => x_msg_count,
3036 x_msg_data => x_msg_data,
3037 p_schv_tbl => p_schv_tbl,
3038 x_schv_tbl => x_schv_tbl,
3039 px_error_tbl => l_error_tbl);
3040 END IF;
3041 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3042 EXCEPTION
3043 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3044 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3045 (
3046 l_api_name,
3047 G_PKG_NAME,
3048 'OKC_API.G_RET_STS_ERROR',
3049 x_msg_count,
3050 x_msg_data,
3051 '_PVT'
3052 );
3053 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3054 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3055 (
3056 l_api_name,
3057 G_PKG_NAME,
3058 'OKC_API.G_RET_STS_UNEXP_ERROR',
3059 x_msg_count,
3060 x_msg_data,
3061 '_PVT'
3062 );
3063 WHEN OTHERS THEN
3064 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3065 (
3066 l_api_name,
3067 G_PKG_NAME,
3068 'OTHERS',
3069 x_msg_count,
3070 x_msg_data,
3071 '_PVT'
3072 );
3073 END update_row;
3074
3075 ---------------------------------------------------------------------------
3076 -- PROCEDURE delete_row
3077 ---------------------------------------------------------------------------
3078 ----------------------------------------
3079 -- delete_row for:OKS_SUBSCR_HEADER_B --
3080 ----------------------------------------
3081 PROCEDURE delete_row(
3082 p_init_msg_list IN VARCHAR2,
3083 x_return_status OUT NOCOPY VARCHAR2,
3084 x_msg_count OUT NOCOPY NUMBER,
3085 x_msg_data OUT NOCOPY VARCHAR2,
3086 p_sbh_rec IN sbh_rec_type) IS
3087
3088 l_api_version CONSTANT NUMBER := 1;
3089 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3090 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3091 l_sbh_rec sbh_rec_type := p_sbh_rec;
3092 l_row_notfound BOOLEAN := TRUE;
3093 BEGIN
3094 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3095 p_init_msg_list,
3096 '_PVT',
3097 x_return_status);
3098 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3099 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3100 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3101 RAISE OKC_API.G_EXCEPTION_ERROR;
3102 END IF;
3103
3104 DELETE FROM OKS_SUBSCR_HEADER_B
3105 WHERE ID = p_sbh_rec.id;
3106
3107 x_return_status := l_return_status;
3108 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3109 EXCEPTION
3110 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3111 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3112 (
3113 l_api_name,
3114 G_PKG_NAME,
3115 'OKC_API.G_RET_STS_ERROR',
3116 x_msg_count,
3117 x_msg_data,
3118 '_PVT'
3119 );
3120 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3121 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3122 (
3123 l_api_name,
3124 G_PKG_NAME,
3125 'OKC_API.G_RET_STS_UNEXP_ERROR',
3126 x_msg_count,
3127 x_msg_data,
3128 '_PVT'
3129 );
3130 WHEN OTHERS THEN
3131 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3132 (
3133 l_api_name,
3134 G_PKG_NAME,
3135 'OTHERS',
3136 x_msg_count,
3137 x_msg_data,
3138 '_PVT'
3139 );
3140 END delete_row;
3141 -----------------------------------------
3142 -- delete_row for:OKS_SUBSCR_HEADER_TL --
3143 -----------------------------------------
3144 PROCEDURE delete_row(
3145 p_init_msg_list IN VARCHAR2,
3146 x_return_status OUT NOCOPY VARCHAR2,
3147 x_msg_count OUT NOCOPY NUMBER,
3148 x_msg_data OUT NOCOPY VARCHAR2,
3149 p_oks_subscr_header_tl_rec IN oks_subscr_header_tl_rec_type) IS
3150
3151 l_api_version CONSTANT NUMBER := 1;
3152 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3153 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3154 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type := p_oks_subscr_header_tl_rec;
3155 l_row_notfound BOOLEAN := TRUE;
3156 BEGIN
3157 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3158 p_init_msg_list,
3159 '_PVT',
3160 x_return_status);
3161 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3162 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3163 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3164 RAISE OKC_API.G_EXCEPTION_ERROR;
3165 END IF;
3166
3167 DELETE FROM OKS_SUBSCR_HEADER_TL
3168 WHERE ID = p_oks_subscr_header_tl_rec.id;
3169
3170 x_return_status := l_return_status;
3171 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3172 EXCEPTION
3173 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3174 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3175 (
3176 l_api_name,
3177 G_PKG_NAME,
3178 'OKC_API.G_RET_STS_ERROR',
3179 x_msg_count,
3180 x_msg_data,
3181 '_PVT'
3182 );
3183 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3184 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3185 (
3186 l_api_name,
3187 G_PKG_NAME,
3188 'OKC_API.G_RET_STS_UNEXP_ERROR',
3189 x_msg_count,
3190 x_msg_data,
3191 '_PVT'
3192 );
3193 WHEN OTHERS THEN
3194 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3195 (
3196 l_api_name,
3197 G_PKG_NAME,
3198 'OTHERS',
3199 x_msg_count,
3200 x_msg_data,
3201 '_PVT'
3202 );
3203 END delete_row;
3204 ----------------------------------------
3205 -- delete_row for:OKS_SUBSCR_HEADER_V --
3206 ----------------------------------------
3207 PROCEDURE delete_row(
3208 p_api_version IN NUMBER,
3209 p_init_msg_list IN VARCHAR2,
3210 x_return_status OUT NOCOPY VARCHAR2,
3211 x_msg_count OUT NOCOPY NUMBER,
3212 x_msg_data OUT NOCOPY VARCHAR2,
3213 p_schv_rec IN schv_rec_type) IS
3214
3215 l_api_version CONSTANT NUMBER := 1;
3216 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3217 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3218 l_schv_rec schv_rec_type := p_schv_rec;
3219 l_oks_subscr_header_tl_rec oks_subscr_header_tl_rec_type;
3220 l_sbh_rec sbh_rec_type;
3221 BEGIN
3222 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3223 G_PKG_NAME,
3224 p_init_msg_list,
3225 l_api_version,
3226 p_api_version,
3227 '_PVT',
3228 x_return_status);
3229 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3230 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3231 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3232 RAISE OKC_API.G_EXCEPTION_ERROR;
3233 END IF;
3234 -----------------------------------------
3235 -- Move VIEW record to "Child" records --
3236 -----------------------------------------
3237 migrate(l_schv_rec, l_oks_subscr_header_tl_rec);
3238 migrate(l_schv_rec, l_sbh_rec);
3239 -----------------------------------------------
3240 -- Call the DELETE_ROW for each child record --
3241 -----------------------------------------------
3242 delete_row(
3243 p_init_msg_list,
3244 l_return_status,
3245 x_msg_count,
3246 x_msg_data,
3247 l_oks_subscr_header_tl_rec
3248 );
3249 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3251 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3252 RAISE OKC_API.G_EXCEPTION_ERROR;
3253 END IF;
3254 delete_row(
3255 p_init_msg_list,
3256 l_return_status,
3257 x_msg_count,
3258 x_msg_data,
3259 l_sbh_rec
3260 );
3261 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3262 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3263 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3264 RAISE OKC_API.G_EXCEPTION_ERROR;
3265 END IF;
3266 x_return_status := l_return_status;
3267 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3268 EXCEPTION
3269 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3270 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3271 (
3272 l_api_name,
3273 G_PKG_NAME,
3274 'OKC_API.G_RET_STS_ERROR',
3275 x_msg_count,
3276 x_msg_data,
3277 '_PVT'
3278 );
3279 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3280 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3281 (
3282 l_api_name,
3283 G_PKG_NAME,
3284 'OKC_API.G_RET_STS_UNEXP_ERROR',
3285 x_msg_count,
3286 x_msg_data,
3287 '_PVT'
3288 );
3289 WHEN OTHERS THEN
3290 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3291 (
3292 l_api_name,
3293 G_PKG_NAME,
3294 'OTHERS',
3295 x_msg_count,
3296 x_msg_data,
3297 '_PVT'
3298 );
3299 END delete_row;
3300 ---------------------------------------------------
3301 -- PL/SQL TBL delete_row for:OKS_SUBSCR_HEADER_V --
3302 ---------------------------------------------------
3303 PROCEDURE delete_row(
3304 p_api_version IN NUMBER,
3305 p_init_msg_list IN VARCHAR2,
3306 x_return_status OUT NOCOPY VARCHAR2,
3307 x_msg_count OUT NOCOPY NUMBER,
3308 x_msg_data OUT NOCOPY VARCHAR2,
3309 p_schv_tbl IN schv_tbl_type,
3310 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
3311
3312 l_api_version CONSTANT NUMBER := 1;
3313 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
3314 i NUMBER := 0;
3315 BEGIN
3316 OKC_API.init_msg_list(p_init_msg_list);
3317 -- Make sure PL/SQL table has records in it before passing
3318 IF (p_schv_tbl.COUNT > 0) THEN
3319 i := p_schv_tbl.FIRST;
3320 LOOP
3321 DECLARE
3322 l_error_rec OKC_API.ERROR_REC_TYPE;
3323 BEGIN
3324 l_error_rec.api_name := l_api_name;
3325 l_error_rec.api_package := G_PKG_NAME;
3326 l_error_rec.idx := i;
3327 delete_row (
3328 p_api_version => p_api_version,
3329 p_init_msg_list => OKC_API.G_FALSE,
3330 x_return_status => l_error_rec.error_type,
3331 x_msg_count => l_error_rec.msg_count,
3332 x_msg_data => l_error_rec.msg_data,
3333 p_schv_rec => p_schv_tbl(i));
3334 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
3335 l_error_rec.sqlcode := SQLCODE;
3336 load_error_tbl(l_error_rec, px_error_tbl);
3337 ELSE
3338 x_msg_count := l_error_rec.msg_count;
3339 x_msg_data := l_error_rec.msg_data;
3340 END IF;
3341 EXCEPTION
3342 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3343 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
3344 l_error_rec.sqlcode := SQLCODE;
3345 load_error_tbl(l_error_rec, px_error_tbl);
3346 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3347 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
3348 l_error_rec.sqlcode := SQLCODE;
3349 load_error_tbl(l_error_rec, px_error_tbl);
3350 WHEN OTHERS THEN
3351 l_error_rec.error_type := 'OTHERS';
3352 l_error_rec.sqlcode := SQLCODE;
3353 load_error_tbl(l_error_rec, px_error_tbl);
3354 END;
3355 EXIT WHEN (i = p_schv_tbl.LAST);
3356 i := p_schv_tbl.NEXT(i);
3357 END LOOP;
3358 END IF;
3359 -- Loop through the error_tbl to find the error with the highest severity
3360 -- and return it.
3361 x_return_status := find_highest_exception(px_error_tbl);
3362 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3363 EXCEPTION
3364 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3365 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3366 (
3367 l_api_name,
3368 G_PKG_NAME,
3369 'OKC_API.G_RET_STS_ERROR',
3370 x_msg_count,
3371 x_msg_data,
3372 '_PVT'
3373 );
3374 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3375 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3376 (
3377 l_api_name,
3378 G_PKG_NAME,
3379 'OKC_API.G_RET_STS_UNEXP_ERROR',
3380 x_msg_count,
3381 x_msg_data,
3382 '_PVT'
3383 );
3384 WHEN OTHERS THEN
3385 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3386 (
3387 l_api_name,
3388 G_PKG_NAME,
3389 'OTHERS',
3390 x_msg_count,
3391 x_msg_data,
3392 '_PVT'
3393 );
3394 END delete_row;
3395
3396 ---------------------------------------------------
3397 -- PL/SQL TBL delete_row for:OKS_SUBSCR_HEADER_V --
3398 ---------------------------------------------------
3399 PROCEDURE delete_row(
3400 p_api_version IN NUMBER,
3401 p_init_msg_list IN VARCHAR2,
3402 x_return_status OUT NOCOPY VARCHAR2,
3403 x_msg_count OUT NOCOPY NUMBER,
3404 x_msg_data OUT NOCOPY VARCHAR2,
3405 p_schv_tbl IN schv_tbl_type) IS
3406
3407 l_api_version CONSTANT NUMBER := 1;
3408 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3409 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3410 l_error_tbl OKC_API.ERROR_TBL_TYPE;
3411 BEGIN
3412 OKC_API.init_msg_list(p_init_msg_list);
3413 -- Make sure PL/SQL table has records in it before passing
3414 IF (p_schv_tbl.COUNT > 0) THEN
3415 delete_row (
3416 p_api_version => p_api_version,
3417 p_init_msg_list => OKC_API.G_FALSE,
3418 x_return_status => x_return_status,
3419 x_msg_count => x_msg_count,
3420 x_msg_data => x_msg_data,
3421 p_schv_tbl => p_schv_tbl,
3422 px_error_tbl => l_error_tbl);
3423 END IF;
3424 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3425 EXCEPTION
3426 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3427 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3428 (
3429 l_api_name,
3430 G_PKG_NAME,
3431 'OKC_API.G_RET_STS_ERROR',
3432 x_msg_count,
3433 x_msg_data,
3434 '_PVT'
3435 );
3436 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3437 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3438 (
3439 l_api_name,
3440 G_PKG_NAME,
3441 'OKC_API.G_RET_STS_UNEXP_ERROR',
3442 x_msg_count,
3443 x_msg_data,
3444 '_PVT'
3445 );
3446 WHEN OTHERS THEN
3447 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3448 (
3449 l_api_name,
3450 G_PKG_NAME,
3451 'OTHERS',
3452 x_msg_count,
3453 x_msg_data,
3454 '_PVT'
3455 );
3456 END delete_row;
3457
3458 END OKS_SUBSCR_HDR_PVT;