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