[Home] [Help]
PACKAGE BODY: APPS.OKC_CRJ_PVT
Source
1 PACKAGE BODY OKC_CRJ_PVT AS
2 /* $Header: OKCSCRJB.pls 120.0 2005/05/26 09:43:45 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 /* start added code
6 */
7 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
8 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
9 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
10 G_EXCEPTION_HALT_VALIDATION exception;
11 /* end added code
12 */
13 ---------------------------------------------------------------------------
14 -- FUNCTION get_seq_id
15 ---------------------------------------------------------------------------
16 FUNCTION get_seq_id RETURN NUMBER IS
17 BEGIN
18 RETURN(okc_p_util.raw_to_number(sys_guid()));
19 END get_seq_id;
20
21 ---------------------------------------------------------------------------
22 -- PROCEDURE qc
23 ---------------------------------------------------------------------------
24 PROCEDURE qc IS
25 BEGIN
26 null;
27 END qc;
28
29 ---------------------------------------------------------------------------
30 -- PROCEDURE change_version
31 ---------------------------------------------------------------------------
32 PROCEDURE change_version IS
33 BEGIN
34 null;
35 END change_version;
36
37 ---------------------------------------------------------------------------
38 -- PROCEDURE api_copy
39 ---------------------------------------------------------------------------
40 PROCEDURE api_copy IS
41 BEGIN
42 null;
43 END api_copy;
44
45 ---------------------------------------------------------------------------
46 -- FUNCTION get_rec for: OKC_K_REL_OBJS
47 ---------------------------------------------------------------------------
48 FUNCTION get_rec (
49 p_crj_rec IN crj_rec_type,
50 x_no_data_found OUT NOCOPY BOOLEAN
51 ) RETURN crj_rec_type IS
52 CURSOR crj_pk_csr (p_id IN NUMBER) IS
53 SELECT
54 ID,
55 CLE_ID,
56 CHR_ID,
57 RTY_CODE,
58 OBJECT1_ID1,
59 OBJECT1_ID2,
60 JTOT_OBJECT1_CODE,
61 OBJECT_VERSION_NUMBER,
62 CREATED_BY,
63 CREATION_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATE_LOGIN,
67 ATTRIBUTE_CATEGORY,
68 ATTRIBUTE1,
69 ATTRIBUTE2,
70 ATTRIBUTE3,
71 ATTRIBUTE4,
72 ATTRIBUTE5,
73 ATTRIBUTE6,
74 ATTRIBUTE7,
75 ATTRIBUTE8,
76 ATTRIBUTE9,
77 ATTRIBUTE10,
78 ATTRIBUTE11,
79 ATTRIBUTE12,
80 ATTRIBUTE13,
81 ATTRIBUTE14,
82 ATTRIBUTE15
83 FROM Okc_K_Rel_Objs
84 WHERE okc_k_rel_objs.id = p_id;
85 l_crj_pk crj_pk_csr%ROWTYPE;
86 l_crj_rec crj_rec_type;
87 BEGIN
88 x_no_data_found := TRUE;
89 -- Get current database values
90 OPEN crj_pk_csr (p_crj_rec.id);
91 FETCH crj_pk_csr INTO
92 l_crj_rec.ID,
93 l_crj_rec.CLE_ID,
94 l_crj_rec.CHR_ID,
95 l_crj_rec.RTY_CODE,
96 l_crj_rec.OBJECT1_ID1,
97 l_crj_rec.OBJECT1_ID2,
98 l_crj_rec.JTOT_OBJECT1_CODE,
99 l_crj_rec.OBJECT_VERSION_NUMBER,
100 l_crj_rec.CREATED_BY,
101 l_crj_rec.CREATION_DATE,
102 l_crj_rec.LAST_UPDATED_BY,
103 l_crj_rec.LAST_UPDATE_DATE,
104 l_crj_rec.LAST_UPDATE_LOGIN,
105 l_crj_rec.ATTRIBUTE_CATEGORY,
106 l_crj_rec.ATTRIBUTE1,
107 l_crj_rec.ATTRIBUTE2,
108 l_crj_rec.ATTRIBUTE3,
109 l_crj_rec.ATTRIBUTE4,
110 l_crj_rec.ATTRIBUTE5,
111 l_crj_rec.ATTRIBUTE6,
112 l_crj_rec.ATTRIBUTE7,
113 l_crj_rec.ATTRIBUTE8,
114 l_crj_rec.ATTRIBUTE9,
115 l_crj_rec.ATTRIBUTE10,
116 l_crj_rec.ATTRIBUTE11,
117 l_crj_rec.ATTRIBUTE12,
118 l_crj_rec.ATTRIBUTE13,
119 l_crj_rec.ATTRIBUTE14,
120 l_crj_rec.ATTRIBUTE15;
121 x_no_data_found := crj_pk_csr%NOTFOUND;
122 CLOSE crj_pk_csr;
123 RETURN(l_crj_rec);
124 END get_rec;
125
126 FUNCTION get_rec (
127 p_crj_rec IN crj_rec_type
128 ) RETURN crj_rec_type IS
129 l_row_notfound BOOLEAN := TRUE;
130 BEGIN
131 RETURN(get_rec(p_crj_rec, l_row_notfound));
132 END get_rec;
133 ---------------------------------------------------------------------------
134 -- FUNCTION get_rec for: OKC_K_REL_OBJS_V
135 ---------------------------------------------------------------------------
136 FUNCTION get_rec (
137 p_crjv_rec IN crjv_rec_type,
138 x_no_data_found OUT NOCOPY BOOLEAN
139 ) RETURN crjv_rec_type IS
140 CURSOR okc_crjv_pk_csr (p_id IN NUMBER) IS
141 SELECT
142 ID,
143 OBJECT_VERSION_NUMBER,
144 CLE_ID,
145 CHR_ID,
146 RTY_CODE,
147 OBJECT1_ID1,
148 OBJECT1_ID2,
149 JTOT_OBJECT1_CODE,
150 ATTRIBUTE_CATEGORY,
151 ATTRIBUTE1,
152 ATTRIBUTE2,
153 ATTRIBUTE3,
154 ATTRIBUTE4,
155 ATTRIBUTE5,
156 ATTRIBUTE6,
157 ATTRIBUTE7,
158 ATTRIBUTE8,
159 ATTRIBUTE9,
160 ATTRIBUTE10,
161 ATTRIBUTE11,
162 ATTRIBUTE12,
163 ATTRIBUTE13,
164 ATTRIBUTE14,
165 ATTRIBUTE15,
166 CREATED_BY,
167 CREATION_DATE,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_DATE,
170 LAST_UPDATE_LOGIN
171 FROM Okc_K_Rel_Objs
172 WHERE okc_k_rel_objs.id = p_id;
173 l_okc_crjv_pk okc_crjv_pk_csr%ROWTYPE;
174 l_crjv_rec crjv_rec_type;
175 BEGIN
176 x_no_data_found := TRUE;
177 -- Get current database values
178 OPEN okc_crjv_pk_csr (p_crjv_rec.id);
179 FETCH okc_crjv_pk_csr INTO
180 l_crjv_rec.ID,
181 l_crjv_rec.OBJECT_VERSION_NUMBER,
182 l_crjv_rec.CLE_ID,
183 l_crjv_rec.CHR_ID,
184 l_crjv_rec.RTY_CODE,
185 l_crjv_rec.OBJECT1_ID1,
186 l_crjv_rec.OBJECT1_ID2,
187 l_crjv_rec.JTOT_OBJECT1_CODE,
188 l_crjv_rec.ATTRIBUTE_CATEGORY,
189 l_crjv_rec.ATTRIBUTE1,
190 l_crjv_rec.ATTRIBUTE2,
191 l_crjv_rec.ATTRIBUTE3,
192 l_crjv_rec.ATTRIBUTE4,
193 l_crjv_rec.ATTRIBUTE5,
194 l_crjv_rec.ATTRIBUTE6,
195 l_crjv_rec.ATTRIBUTE7,
196 l_crjv_rec.ATTRIBUTE8,
197 l_crjv_rec.ATTRIBUTE9,
198 l_crjv_rec.ATTRIBUTE10,
199 l_crjv_rec.ATTRIBUTE11,
200 l_crjv_rec.ATTRIBUTE12,
201 l_crjv_rec.ATTRIBUTE13,
202 l_crjv_rec.ATTRIBUTE14,
203 l_crjv_rec.ATTRIBUTE15,
204 l_crjv_rec.CREATED_BY,
205 l_crjv_rec.CREATION_DATE,
206 l_crjv_rec.LAST_UPDATED_BY,
207 l_crjv_rec.LAST_UPDATE_DATE,
208 l_crjv_rec.LAST_UPDATE_LOGIN;
209 x_no_data_found := okc_crjv_pk_csr%NOTFOUND;
210 CLOSE okc_crjv_pk_csr;
211 RETURN(l_crjv_rec);
212 END get_rec;
213
214 FUNCTION get_rec (
215 p_crjv_rec IN crjv_rec_type
216 ) RETURN crjv_rec_type IS
217 l_row_notfound BOOLEAN := TRUE;
218 BEGIN
219 RETURN(get_rec(p_crjv_rec, l_row_notfound));
220 END get_rec;
221
222 ------------------------------------------------------
223 -- FUNCTION null_out_defaults for: OKC_K_REL_OBJS_V --
224 ------------------------------------------------------
225 FUNCTION null_out_defaults (
226 p_crjv_rec IN crjv_rec_type
227 ) RETURN crjv_rec_type IS
228 l_crjv_rec crjv_rec_type := p_crjv_rec;
229 BEGIN
230 IF (l_crjv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
231 l_crjv_rec.object_version_number := NULL;
232 END IF;
233 IF (l_crjv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
234 l_crjv_rec.cle_id := NULL;
235 END IF;
236 IF (l_crjv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
237 l_crjv_rec.chr_id := NULL;
238 END IF;
239 IF (l_crjv_rec.rty_code = OKC_API.G_MISS_CHAR) THEN
240 l_crjv_rec.rty_code := NULL;
241 END IF;
242 IF (l_crjv_rec.object1_id1 = OKC_API.G_MISS_CHAR) THEN
243 l_crjv_rec.object1_id1 := NULL;
244 END IF;
245 IF (l_crjv_rec.object1_id2 = OKC_API.G_MISS_CHAR) THEN
246 l_crjv_rec.object1_id2 := NULL;
247 END IF;
248 IF (l_crjv_rec.JTOT_OBJECT1_CODE = OKC_API.G_MISS_CHAR) THEN
249 l_crjv_rec.JTOT_OBJECT1_CODE := NULL;
250 END IF;
251 IF (l_crjv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
252 l_crjv_rec.attribute_category := NULL;
253 END IF;
254 IF (l_crjv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
255 l_crjv_rec.attribute1 := NULL;
256 END IF;
257 IF (l_crjv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
258 l_crjv_rec.attribute2 := NULL;
259 END IF;
260 IF (l_crjv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
261 l_crjv_rec.attribute3 := NULL;
262 END IF;
263 IF (l_crjv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
264 l_crjv_rec.attribute4 := NULL;
265 END IF;
266 IF (l_crjv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
267 l_crjv_rec.attribute5 := NULL;
268 END IF;
269 IF (l_crjv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
270 l_crjv_rec.attribute6 := NULL;
271 END IF;
272 IF (l_crjv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
273 l_crjv_rec.attribute7 := NULL;
274 END IF;
275 IF (l_crjv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
276 l_crjv_rec.attribute8 := NULL;
277 END IF;
278 IF (l_crjv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
279 l_crjv_rec.attribute9 := NULL;
280 END IF;
281 IF (l_crjv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
282 l_crjv_rec.attribute10 := NULL;
283 END IF;
284 IF (l_crjv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
285 l_crjv_rec.attribute11 := NULL;
286 END IF;
287 IF (l_crjv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
288 l_crjv_rec.attribute12 := NULL;
289 END IF;
290 IF (l_crjv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
291 l_crjv_rec.attribute13 := NULL;
292 END IF;
293 IF (l_crjv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
294 l_crjv_rec.attribute14 := NULL;
295 END IF;
296 IF (l_crjv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
297 l_crjv_rec.attribute15 := NULL;
298 END IF;
299 IF (l_crjv_rec.created_by = OKC_API.G_MISS_NUM) THEN
300 l_crjv_rec.created_by := NULL;
301 END IF;
302 IF (l_crjv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
303 l_crjv_rec.creation_date := NULL;
304 END IF;
305 IF (l_crjv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
306 l_crjv_rec.last_updated_by := NULL;
307 END IF;
308 IF (l_crjv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
309 l_crjv_rec.last_update_date := NULL;
310 END IF;
311 IF (l_crjv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
312 l_crjv_rec.last_update_login := NULL;
313 END IF;
314 RETURN(l_crjv_rec);
315 END null_out_defaults;
316 ---------------------------------------------------------------------------
317 -- Validate_Attributes
318 ---------------------------------------------------------------------------
319 /* start added code
320 */
321 ---------------------------------------------------------------------------
322 -- PROCEDURE validate_attribute_id
323 ---------------------------------------------------------------------------
324 PROCEDURE valid_att_id
325 (
326 p_crjv_rec IN crjv_rec_type
327 ,p_api IN varchar2
328 ,x_return_status OUT NOCOPY VARCHAR2
329 ) IS
330 l_api_name varchar2(300) := 'valid_att_id';
331 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
332 BEGIN
333 x_return_status := OKC_API.G_RET_STS_SUCCESS;
334 IF (-- id null
335 p_crjv_rec.id = OKC_API.G_MISS_NUM
336 OR p_crjv_rec.id IS NULL
337 ) THEN
338 OKC_API.set_message
339 (
340 G_APP_NAME
341 ,G_REQUIRED_VALUE
342 ,G_COL_NAME_TOKEN
343 ,'id'
344 )
345 ;
346 x_return_status := OKC_API.G_RET_STS_ERROR;
347 raise g_exception_halt_validation;
348 END IF; -- id null
349 EXCEPTION
350 WHEN g_exception_halt_validation THEN
351 null;
352 WHEN OTHERS THEN
353 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
354 OKC_API.set_message
355 (
356 G_APP_NAME
357 ,g_unexpected_error
358 ,g_sqlcode_token
359 ,sqlcode
360 ,g_sqlerrm_token
361 ,sqlerrm
362 ,'@'
363 ,l_api_name
364 );
365 END valid_att_id;
366
367 ---------------------------------------------------------------------------
368 -- PROCEDURE valid_att_obj_vers_number
369 ---------------------------------------------------------------------------
370 PROCEDURE valid_att_obj_vers_number
371 (
372 p_crjv_rec IN crjv_rec_type
373 ,p_api IN varchar2
374 ,x_return_status OUT NOCOPY VARCHAR2
375 ) IS
376 l_api_name varchar2(300) := 'valid_att_obj_vers_num';
377 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
378 BEGIN
379 x_return_status := OKC_API.G_RET_STS_SUCCESS;
380 IF (-- object_version_number null
381 p_crjv_rec.object_version_number = OKC_API.G_MISS_NUM
382 OR p_crjv_rec.object_version_number IS NULL
383 ) THEN
384 OKC_API.set_message
385 (
386 G_APP_NAME
387 ,G_REQUIRED_VALUE
388 ,G_COL_NAME_TOKEN
389 ,'object_version_number'
390 )
391 ;
392 x_return_status := OKC_API.G_RET_STS_ERROR;
393 raise g_exception_halt_validation;
394 END IF; -- object_version_number null
395 EXCEPTION
396 WHEN g_exception_halt_validation THEN
397 null;
398 WHEN OTHERS THEN
399 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
400 OKC_API.set_message
401 (
402 G_APP_NAME
403 ,g_unexpected_error
404 ,g_sqlcode_token
405 ,sqlcode
406 ,g_sqlerrm_token
407 ,sqlerrm
408 ,'@'
409 ,l_api_name
410 );
411 END valid_att_obj_vers_number;
412
413 ---------------------------------------------------------------------------
414 -- PROCEDURE valid_att_rty_code
415 ---------------------------------------------------------------------------
416 PROCEDURE valid_att_rty_code
417 (
418 p_crjv_rec IN crjv_rec_type
419 ,p_api IN varchar2
420 ,x_return_status OUT NOCOPY VARCHAR2
421 ) IS
422 l_api_name varchar2(300) := 'valid_att_rty_code';
423 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
424
425 BEGIN
426 x_return_status := OKC_API.G_RET_STS_SUCCESS;
427 IF (-- rel. type code null
428 p_crjv_rec.rty_code = OKC_API.G_MISS_CHAR
429 OR p_crjv_rec.rty_code IS NULL
430 ) THEN
431 OKC_API.set_message
432 (
433 G_APP_NAME
434 ,G_REQUIRED_VALUE
435 ,G_COL_NAME_TOKEN
436 ,'='
437 ,p_crjv_rec.rty_code
438 )
439 ;
440 x_return_status := OKC_API.G_RET_STS_ERROR;
441 raise g_exception_halt_validation;
442 END IF; -- rel. type code null
443 EXCEPTION
444 WHEN g_exception_halt_validation THEN
445 null;
446 WHEN OTHERS THEN
447 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
448 OKC_API.set_message
449 (
450 G_APP_NAME
451 ,g_unexpected_error
452 ,g_sqlcode_token
453 ,sqlcode
454 ,g_sqlerrm_token
455 ,sqlerrm
456 ,'@'
457 ,l_api_name
458 );
459 END valid_att_rty_code;
460
461 ---------------------------------------------------------------------------
462 -- PROCEDURE valid_att_k_hdr_and_line
463 ---------------------------------------------------------------------------
464 PROCEDURE valid_att_k_hdr_and_line
465 (
466 p_crjv_rec IN crjv_rec_type
467 ,p_api IN varchar2
468 ,x_return_status OUT NOCOPY VARCHAR2
469 ) IS
470 l_api_name varchar2(300) := 'valid_att_k_hdr_and_line';
471 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
472 BEGIN
473 x_return_status := OKC_API.G_RET_STS_SUCCESS;
474 IF (-- both contract header and line are null
475 (-- line null
476 p_crjv_rec.cle_id = OKC_API.G_MISS_NUM
477 OR p_crjv_rec.cle_id IS NULL
478 )
479 and
480 (-- header null
481 p_crjv_rec.chr_id = OKC_API.G_MISS_NUM
482 OR p_crjv_rec.chr_id IS NULL
483 )
484 ) THEN
485 OKC_API.set_message
486 (
487 G_APP_NAME
488 ,G_REQUIRED_VALUE
489 ,G_COL_NAME_TOKEN
490 ,'chr_id / cle_id'
491 )
492 ;
493 x_return_status := OKC_API.G_RET_STS_ERROR;
494 raise g_exception_halt_validation;
495 END IF; -- both contract header and line are null
496 EXCEPTION
497 WHEN g_exception_halt_validation THEN
498 null;
499 WHEN OTHERS THEN
500 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
501 OKC_API.set_message
502 (
503 G_APP_NAME
504 ,g_unexpected_error
505 ,g_sqlcode_token
506 ,sqlcode
507 ,g_sqlerrm_token
508 ,sqlerrm
509 ,'@'
510 ,l_api_name
511 );
512 END valid_att_k_hdr_and_line;
513
514 ---------------------------------------------------------------------------
515 -- PROCEDURE valid_att_obj_id
516 ---------------------------------------------------------------------------
517 PROCEDURE valid_att_obj_id
518 (
519 p_crjv_rec IN crjv_rec_type
520 ,p_api IN varchar2
521 ,x_return_status OUT NOCOPY VARCHAR2
522 ) IS
523 l_api_name varchar2(300) := 'valid_att_obj_id';
524 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
525 BEGIN
526 x_return_status := OKC_API.G_RET_STS_SUCCESS;
527 IF (-- rel. type code null
528 p_crjv_rec.JTOT_OBJECT1_CODE = OKC_API.G_MISS_CHAR
529 OR p_crjv_rec.JTOT_OBJECT1_CODE IS NULL
530 ) THEN
531 OKC_API.set_message
532 (
533 G_APP_NAME
534 ,G_REQUIRED_VALUE
535 ,G_COL_NAME_TOKEN
536 ,'JTOT_OBJECT1_CODE'
537 )
538 ;
539 x_return_status := OKC_API.G_RET_STS_ERROR;
540 raise g_exception_halt_validation;
541 END IF; -- rel. type code null
542 EXCEPTION
543 WHEN g_exception_halt_validation THEN
544 null;
545 WHEN OTHERS THEN
546 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
547 OKC_API.set_message
548 (
549 G_APP_NAME
550 ,g_unexpected_error
551 ,g_sqlcode_token
552 ,sqlcode
553 ,g_sqlerrm_token
554 ,sqlerrm
555 ,'@'
556 ,l_api_name
557 );
558 END valid_att_obj_id;
559
560 ---------------------------------------------------------------------------
561 -- PROCEDURE valid_rec_obj_type
562 ---------------------------------------------------------------------------
563 PROCEDURE valid_rec_obj_type
564 (
565 p_crjv_rec IN crjv_rec_type
566 ,p_api IN varchar2
567 ,x_return_status OUT NOCOPY VARCHAR2
568 ) IS
569
570 CURSOR c_check_okx_khead is
571 SELECT 'x' from okc_k_headers_b
572 where id=p_crjv_rec.object1_id1;
573
574 CURSOR c_check_okx_kline is
575 SELECT 'x' from okc_k_lines_b
576 where id=p_crjv_rec.object1_id1;
577
578 l_api_name varchar2(300) := 'valid_rec_obj_type';
579 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
580 l_row_notfound BOOLEAN := TRUE;
581 l_found varchar2(1);
582
583 BEGIN
584 x_return_status := OKC_API.G_RET_STS_SUCCESS;
585 /* start added code
586 */
587
588 /* check object existence
589 */
590 --Bug 2793261
591 if p_crjv_rec.jtot_object1_code='OKX_KHEAD' then
592
593 open c_check_okx_khead;
594 fetch C_check_okx_khead into l_found;
595 close C_check_okx_khead ;
596
597 If l_found = 'x' then
598 l_row_notfound := false;
599 end if;
600
601 elsif p_crjv_rec.jtot_object1_code='OKX_KLINE' then
602
603 open c_check_okx_kline;
604 fetch C_check_okx_kline into l_found;
605 close C_check_okx_kline ;
606
607 If l_found = 'x' then
608 l_row_notfound := false;
609 end if;
610 else
611
612 --End Bug 2793261
613
614 OKC_CRJ_PVT.GET_OBJ_FROM_JTFV
615 (
616 p_crjv_rec.jtot_object1_code
617 ,p_crjv_rec.object1_id1
618 ,p_crjv_rec.object1_id2
619 ,l_row_notfound
620 );
621 end if;
622
623 if (--
624 l_row_notfound
625 ) then
626 OKC_API.set_message
627 (
628 G_APP_NAME
629 ,G_INVALID_VALUE
630 ,G_COL_NAME_TOKEN
631 ,'object'
632 ,'='
633 ,p_crjv_rec.jtot_object1_code
634 || ';' || p_crjv_rec.object1_id1
635 || ';' || p_crjv_rec.object1_id2
636 )
637 ;
638 x_return_status := OKC_API.G_RET_STS_ERROR;
639 raise g_exception_halt_validation;
640 end if; --
641 /* end added code
642 */
643 EXCEPTION
644 WHEN g_exception_halt_validation THEN
645 null;
646 WHEN OTHERS THEN
647 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
648 OKC_API.set_message
649 (
650 G_APP_NAME
651 ,g_unexpected_error
652 ,g_sqlcode_token
653 ,sqlcode
654 ,g_sqlerrm_token
655 ,sqlerrm
656 ,'@'
657 ,l_api_name
658 );
659 END valid_rec_obj_type;
660
661 ---------------------------------------------------------------------------
662 -- PROCEDURE valid_rec_rty_code
663 ---------------------------------------------------------------------------
664 PROCEDURE valid_rec_rty_code
665 (
666 p_crjv_rec IN crjv_rec_type
667 ,p_api IN varchar2
668 ,x_return_status OUT NOCOPY VARCHAR2
669 ) IS
670 l_api_name varchar2(300) := 'valid_rec_rty_code';
671 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
672 l_row_notfound BOOLEAN := TRUE;
673
674 --CURSOR fnd_lookup_pk_csr (p_lookup_code IN VARCHAR2) IS
675 --SELECT
676 -- LOOKUP_TYPE,
677 -- LOOKUP_CODE,
678 -- MEANING,
679 -- DESCRIPTION,
680 -- ENABLED_FLAG,
681 -- START_DATE_ACTIVE,
682 -- END_DATE_ACTIVE
683 --FROM Fnd_Lookups
684 --WHERE fnd_lookups.lookup_code = p_lookup_code;
685 --l_fnd_lookup_pk fnd_lookup_pk_csr%ROWTYPE;
686
687 BEGIN
688 x_return_status := OKC_API.G_RET_STS_SUCCESS;
689 /*
690 IF (p_crjv_rec.RTY_CODE IS NOT NULL) THEN
691 OPEN fnd_lookup_pk_csr(p_crjv_rec.RTY_CODE);
692 FETCH fnd_lookup_pk_csr INTO l_fnd_lookup_pk;
693 l_row_notfound := fnd_lookup_pk_csr%NOTFOUND;
694 CLOSE fnd_lookup_pk_csr;
695 IF (l_row_notfound) THEN
696 OKC_API.set_message
697 (
698 G_APP_NAME
699 ,G_REQUIRED_VALUE
700 ,G_COL_NAME_TOKEN
701 ,'rty_code'
702 )
703 ;
704 x_return_status := OKC_API.G_RET_STS_ERROR;
705 raise g_exception_halt_validation;
706 END IF;
707 END IF;
708 */
709 x_return_status := OKC_UTIL.check_lookup_code
710 (
711 'OKC_REL_OBJ'
712 ,p_crjv_rec.rty_code
713 );
714 if (--
715 x_return_status <> OKC_API.G_RET_STS_SUCCESS
716 ) then
717 OKC_API.set_message
718 (
719 G_APP_NAME
720 ,G_INVALID_VALUE
721 ,G_COL_NAME_TOKEN
722 ,'rty_code'
723 ,'='
724 ,p_crjv_rec.rty_code
725 )
726 ;
727 x_return_status := OKC_API.G_RET_STS_ERROR;
728 raise g_exception_halt_validation;
729 end if; --
730
731 EXCEPTION
732 WHEN g_exception_halt_validation THEN
733 null;
734 WHEN OTHERS THEN
735 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
736 OKC_API.set_message
737 (
738 G_APP_NAME
739 ,g_unexpected_error
740 ,g_sqlcode_token
741 ,sqlcode
742 ,g_sqlerrm_token
743 ,sqlerrm
744 ,'@'
745 ,l_api_name
746 );
747 END valid_rec_rty_code;
748
749 ---------------------------------------------------------------------------
750 -- PROCEDURE valid_rec_cle_id
751 ---------------------------------------------------------------------------
752 PROCEDURE valid_rec_cle_id
753 (
754 p_crjv_rec IN crjv_rec_type
755 ,p_api IN varchar2
756 ,x_return_status OUT NOCOPY VARCHAR2
757 ) IS
758 l_api_name varchar2(300) := 'valid_rec_cle_id';
759 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
760 l_row_notfound BOOLEAN := TRUE;
761
762 CURSOR okc_clev_pk_csr (p_id IN NUMBER) IS
763 SELECT
764 ID,
765 OBJECT_VERSION_NUMBER,
766 SFWT_FLAG,
767 CHR_ID,
768 CLE_ID,
769 LSE_ID,
770 LINE_NUMBER,
771 STS_CODE,
772 DISPLAY_SEQUENCE,
773 TRN_CODE,
774 DNZ_CHR_ID,
775 COMMENTS,
776 ITEM_DESCRIPTION,
777 HIDDEN_IND,
778 PRICE_NEGOTIATED,
779 PRICE_LEVEL_IND,
780 INVOICE_LINE_LEVEL_IND,
781 DPAS_RATING,
782 BLOCK23TEXT,
783 EXCEPTION_YN,
784 TEMPLATE_USED,
785 DATE_TERMINATED,
786 NAME,
787 START_DATE,
788 END_DATE,
789 ATTRIBUTE_CATEGORY,
790 ATTRIBUTE1,
791 ATTRIBUTE2,
792 ATTRIBUTE3,
793 ATTRIBUTE4,
794 ATTRIBUTE5,
795 ATTRIBUTE6,
796 ATTRIBUTE7,
797 ATTRIBUTE8,
798 ATTRIBUTE9,
799 ATTRIBUTE10,
800 ATTRIBUTE11,
801 ATTRIBUTE12,
802 ATTRIBUTE13,
803 ATTRIBUTE14,
804 ATTRIBUTE15,
805 CREATED_BY,
806 CREATION_DATE,
807 LAST_UPDATED_BY,
808 LAST_UPDATE_DATE,
809 PRICE_TYPE,
810 CURRENCY_CODE,
811 LAST_UPDATE_LOGIN
812 FROM Okc_K_Lines_V
813 WHERE okc_k_lines_v.id = p_id;
814 l_okc_clev_pk okc_clev_pk_csr%ROWTYPE;
815
816 BEGIN
817 x_return_status := OKC_API.G_RET_STS_SUCCESS;
818 IF (p_crjv_rec.CLE_ID IS NOT NULL)
819 THEN
820 OPEN okc_clev_pk_csr(p_crjv_rec.CLE_ID);
821 FETCH okc_clev_pk_csr INTO l_okc_clev_pk;
822 l_row_notfound := okc_clev_pk_csr%NOTFOUND;
823 CLOSE okc_clev_pk_csr;
824 IF (l_row_notfound) THEN
825 OKC_API.set_message
826 (
827 G_APP_NAME
828 ,G_REQUIRED_VALUE
829 ,G_COL_NAME_TOKEN
830 ,'cle_id'
831 )
832 ;
833 x_return_status := OKC_API.G_RET_STS_ERROR;
834 raise g_exception_halt_validation;
835 END IF;
836 END IF;
837 EXCEPTION
838 WHEN g_exception_halt_validation THEN
839 null;
840 WHEN OTHERS THEN
841 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
842 OKC_API.set_message
843 (
844 G_APP_NAME
845 ,g_unexpected_error
846 ,g_sqlcode_token
847 ,sqlcode
848 ,g_sqlerrm_token
849 ,sqlerrm
850 ,'@'
851 ,l_api_name
852 );
853 END valid_rec_cle_id;
854
855 ---------------------------------------------------------------------------
856 -- PROCEDURE valid_rec_chr_id
857 ---------------------------------------------------------------------------
858 PROCEDURE valid_rec_chr_id
859 (
860 p_crjv_rec IN crjv_rec_type
861 ,p_api IN varchar2
862 ,x_return_status OUT NOCOPY VARCHAR2
863 ) IS
864 l_api_name varchar2(300) := 'valid_rec_chr_id';
865 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
866 l_row_notfound BOOLEAN := TRUE;
867
868 CURSOR okc_chrv_pk_csr (p_id IN NUMBER) IS
869 SELECT
870 ID,
871 OBJECT_VERSION_NUMBER,
872 SFWT_FLAG,
873 CHR_ID_RESPONSE,
874 CHR_ID_AWARD,
875 STS_CODE,
876 QCL_ID,
877 SCS_CODE,
878 CONTRACT_NUMBER,
879 CURRENCY_CODE,
880 CONTRACT_NUMBER_MODIFIER,
881 ARCHIVED_YN,
882 DELETED_YN,
883 CUST_PO_NUMBER_REQ_YN,
884 PRE_PAY_REQ_YN,
885 CUST_PO_NUMBER,
886 SHORT_DESCRIPTION,
887 COMMENTS,
888 DESCRIPTION,
889 DPAS_RATING,
890 COGNOMEN,
891 TEMPLATE_YN,
892 TEMPLATE_USED,
893 DATE_APPROVED,
894 DATETIME_CANCELLED,
895 AUTO_RENEW_DAYS,
896 DATE_ISSUED,
897 DATETIME_RESPONDED,
898 NON_RESPONSE_REASON,
899 NON_RESPONSE_EXPLAIN,
900 RFP_TYPE,
901 CHR_TYPE,
902 KEEP_ON_MAIL_LIST,
903 SET_ASIDE_REASON,
904 SET_ASIDE_PERCENT,
905 RESPONSE_COPIES_REQ,
906 DATE_CLOSE_PROJECTED,
907 DATETIME_PROPOSED,
908 DATE_SIGNED,
909 DATE_TERMINATED,
910 DATE_RENEWED,
911 TRN_CODE,
912 START_DATE,
913 END_DATE,
914 AUTHORING_ORG_ID,
915 BUY_OR_SELL,
916 ISSUE_OR_RECEIVE,
917 ATTRIBUTE_CATEGORY,
918 ATTRIBUTE1,
919 ATTRIBUTE2,
920 ATTRIBUTE3,
921 ATTRIBUTE4,
922 ATTRIBUTE5,
923 ATTRIBUTE6,
924 ATTRIBUTE7,
925 ATTRIBUTE8,
926 ATTRIBUTE9,
927 ATTRIBUTE10,
928 ATTRIBUTE11,
929 ATTRIBUTE12,
930 ATTRIBUTE13,
931 ATTRIBUTE14,
932 ATTRIBUTE15,
933 CREATED_BY,
934 CREATION_DATE,
935 LAST_UPDATED_BY,
936 LAST_UPDATE_DATE,
937 LAST_UPDATE_LOGIN
938 FROM Okc_K_Headers_V
939 WHERE okc_k_headers_v.id = p_id;
940 l_okc_chrv_pk okc_chrv_pk_csr%ROWTYPE;
941 BEGIN
942 x_return_status := OKC_API.G_RET_STS_SUCCESS;
943 IF (p_crjv_rec.CHR_ID IS NOT NULL) THEN
944 OPEN okc_chrv_pk_csr(p_crjv_rec.CHR_ID);
945 FETCH okc_chrv_pk_csr INTO l_okc_chrv_pk;
946 l_row_notfound := okc_chrv_pk_csr%NOTFOUND;
947 CLOSE okc_chrv_pk_csr;
948 IF (l_row_notfound) THEN
949 OKC_API.set_message
950 (
951 G_APP_NAME
952 ,G_REQUIRED_VALUE
953 ,G_COL_NAME_TOKEN
954 ,'chr_id'
955 )
956 ;
957 x_return_status := OKC_API.G_RET_STS_ERROR;
958 raise g_exception_halt_validation;
959 END IF;
960 END IF;
961 EXCEPTION
962 WHEN g_exception_halt_validation THEN
963 null;
964 WHEN OTHERS THEN
965 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
966 OKC_API.set_message
967 (
968 G_APP_NAME
969 ,g_unexpected_error
970 ,g_sqlcode_token
971 ,sqlcode
972 ,g_sqlerrm_token
973 ,sqlerrm
974 ,'@'
975 ,l_api_name
976 );
977 END valid_rec_chr_id;
978
979 ---------------------------------------------------------------------------
980 -- PROCEDURE valid_rec_unique
981 ---------------------------------------------------------------------------
982 PROCEDURE valid_rec_unique
983 (
984 p_crjv_rec IN crjv_rec_type
985 ,p_api IN varchar2
986 ,x_return_status OUT NOCOPY VARCHAR2
987 ) IS
988 l_api_name varchar2(300) := 'valid_rec_unique';
989 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
990 l_row_notfound BOOLEAN := TRUE;
991
992 CURSOR row_unique
993 (
994 p_crjv_rec crjv_rec_type
995 ) IS
996 SELECT --replaced the SQL for Bug 3094073
997 id
998 FROM
999 okc_k_rel_objs o
1000 WHERE ( nvl(o.chr_id, -99) = nvl(p_crjv_rec.chr_id, -99)
1001 and (-- lines same (or both null)
1002 o.cle_id = p_crjv_rec.cle_id
1003 or (-- both null
1004 o.cle_id is null
1005 and p_crjv_rec.cle_id is null
1006 )
1007 )
1008 and o.rty_code = p_crjv_rec.rty_code
1009 and o.jtot_object1_code = p_crjv_rec.jtot_object1_code
1010 and o.object1_id1 = p_crjv_rec.object1_id1
1011 and (-- object id same (or both null)
1012 o.object1_id2 = p_crjv_rec.object1_id2
1013 or (-- both null
1014 o.object1_id2 is null
1015 and p_crjv_rec.object1_id2 is null
1016 )
1017 )
1018 ) ;
1019 r_row_unique row_unique%rowtype;
1020 BEGIN
1021 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1022
1023 OPEN row_unique
1024 (
1025 p_crjv_rec
1026 );
1027 FETCH row_unique
1028 INTO r_row_unique;
1029 l_row_notfound := row_unique%NOTFOUND;
1030 CLOSE row_unique;
1031 IF (
1032 not(l_row_notfound)
1033 ) THEN
1034
1035 OKC_API.set_message
1036 (
1037 G_APP_NAME
1038 ,'not unique row'
1039 ,'@'
1040 ,l_api_name
1041 );
1042 x_return_status := OKC_API.G_RET_STS_ERROR;
1043 raise g_exception_halt_validation;
1044 END IF;
1045 EXCEPTION
1046 WHEN g_exception_halt_validation THEN
1047 null;
1048 WHEN OTHERS THEN
1049 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1050 OKC_API.set_message
1051 (
1052 G_APP_NAME
1053 ,g_unexpected_error
1054 ,g_sqlcode_token
1055 ,sqlcode
1056 ,g_sqlerrm_token
1057 ,sqlerrm
1058 ,'@'
1059 ,l_api_name
1060 );
1061 END valid_rec_unique;
1062
1063 ---------------------------------------------------------------------------
1064 -- PROCEDURE valid_rec_cardinality
1065 ---------------------------------------------------------------------------
1066 PROCEDURE valid_rec_cardinality
1067 (
1068 p_crjv_rec IN crjv_rec_type
1069 ,p_api IN varchar2
1070 ,x_return_status OUT NOCOPY VARCHAR2
1071 ) IS
1072 l_api_name varchar2(300) := 'valid_rec_cardinality';
1073 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1074 l_row_notfound BOOLEAN := TRUE;
1075
1076 CURSOR row_cardinality_k
1077 (-- contract negotiates only 1 quote (all rest many)
1078 p_crjv_rec crjv_rec_type
1079 ) IS
1080 SELECT
1081 id
1082 FROM
1083 okc_k_rel_objs o
1084 WHERE (-- all data same
1085 (-- headers same (or both null)
1086 o.chr_id = p_crjv_rec.chr_id
1087 or (-- both null
1088 o.chr_id is null
1089 and p_crjv_rec.chr_id is null
1090 )
1091 )
1092 and (-- lines same (or both null)
1093 o.cle_id = p_crjv_rec.cle_id
1094 or (-- both null
1095 o.cle_id is null
1096 and p_crjv_rec.cle_id is null
1097 )
1098 )
1099 --and o.rty_code = p_crjv_rec.rty_code
1100 and o.rty_code = 'CONTRACTNEGOTIATESQUOTE'
1101 and o.jtot_object1_code = p_crjv_rec.jtot_object1_code
1102 )
1103 ;
1104 r_row_cardinality_k row_cardinality_k%rowtype;
1105
1106 CURSOR row_cardinality_obj
1107 (-- objects have only 1 rel. of any type except quote negotiates
1108 p_crjv_rec crjv_rec_type
1109 ) IS
1110 SELECT
1111 id
1112 FROM
1113 okc_k_rel_objs o
1114 WHERE (-- all data same
1115 o.rty_code = p_crjv_rec.rty_code
1116 -- and o.rty_code <> 'CONTRACTNEGOTIATESQUOTE' Bug# 1255862
1117 and o.rty_code not in ('CONTRACTNEGOTIATESQUOTE', 'CONTRACTSERVICESORDER')
1118 and o.jtot_object1_code = p_crjv_rec.jtot_object1_code
1119 and o.object1_id1 = p_crjv_rec.object1_id1
1120 and (-- object id same (or both null)
1121 o.object1_id2 = p_crjv_rec.object1_id2
1122 or (-- both null
1123 o.object1_id2 is null
1124 and p_crjv_rec.object1_id2 is null
1125 )
1126 )
1127 )
1128 ;
1129 r_row_cardinality_obj row_cardinality_obj%rowtype;
1130
1131 BEGIN
1132 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1133 OPEN row_cardinality_k
1134 (
1135 p_crjv_rec
1136 );
1137 FETCH row_cardinality_k
1138 INTO r_row_cardinality_k;
1139 l_row_notfound := row_cardinality_k%NOTFOUND;
1140 CLOSE row_cardinality_k;
1141 IF (
1142 not(l_row_notfound)
1143 ) THEN
1144 OKC_API.set_message
1145 (
1146 G_APP_NAME
1147 ,'not unique row for contract'
1148 ,'@'
1149 ,l_api_name
1150 );
1151 x_return_status := OKC_API.G_RET_STS_ERROR;
1152 raise g_exception_halt_validation;
1153 END IF;
1154 OPEN row_cardinality_obj
1155 (
1156 p_crjv_rec
1157 );
1158 FETCH row_cardinality_obj
1159 INTO r_row_cardinality_obj;
1160 l_row_notfound := row_cardinality_obj%NOTFOUND;
1161 CLOSE row_cardinality_obj;
1162 IF (
1163 not(l_row_notfound)
1164 ) THEN
1165 OKC_API.set_message
1166 (
1167 G_APP_NAME
1168 ,'not unique object rel'
1169 ,'@'
1170 ,l_api_name
1171 );
1172 x_return_status := OKC_API.G_RET_STS_ERROR;
1173 raise g_exception_halt_validation;
1174 END IF;
1175
1176 EXCEPTION
1177 WHEN g_exception_halt_validation THEN
1178 null;
1179 WHEN OTHERS THEN
1180 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1181 OKC_API.set_message
1182 (
1183 G_APP_NAME
1184 ,g_unexpected_error
1185 ,g_sqlcode_token
1186 ,sqlcode
1187 ,g_sqlerrm_token
1188 ,sqlerrm
1189 ,'@'
1190 ,l_api_name
1191 );
1192 END valid_rec_cardinality;
1193
1194 /* end added code
1195 */
1196 ----------------------------------------------
1197 -- Validate_Attributes for:OKC_K_REL_OBJS_V --
1198 ----------------------------------------------
1199 FUNCTION Validate_Attributes
1200 (
1201 p_crjv_rec IN crjv_rec_type
1202 ,p_api IN varchar2
1203 ) RETURN VARCHAR2 IS
1204 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1205 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1206 l_api_name varchar2(300) := 'Validate_Attributes';
1207 BEGIN
1208 /* start mod code
1209 */
1210 valid_att_id
1211 (
1212 p_crjv_rec
1213 ,l_api_name
1214 ,l_return_status
1215 );
1216 if (-- error returned
1217 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1218 ) then
1219 if (-- no error so far
1220 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1221 ) then
1222 x_return_status := l_return_status;
1223 end if; -- no error so far
1224 end if; -- error returned
1225 valid_att_obj_vers_number
1226 (
1227 p_crjv_rec
1228 ,l_api_name
1229 ,l_return_status
1230 );
1231 if (-- error returned
1232 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1233 ) then
1234 if (-- no error so far
1235 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1236 ) then
1237 x_return_status := l_return_status;
1238 end if; -- no error so far
1239 end if; -- error returned
1240 valid_att_rty_code
1241 (
1242 p_crjv_rec
1243 ,l_api_name
1244 ,l_return_status
1245 );
1246 if (-- error returned
1247 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1248 ) then
1249 if (-- no error so far
1250 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1251 ) then
1252 x_return_status := l_return_status;
1253 end if; -- no error so far
1254 end if; -- error returned
1255 valid_att_k_hdr_and_line
1256 (
1257 p_crjv_rec
1258 ,l_api_name
1259 ,l_return_status
1260 );
1261 if (-- error returned
1262 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1263 ) then
1264 if (-- no error so far
1265 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1266 ) then
1267 x_return_status := l_return_status;
1268 end if; -- no error so far
1269 end if; -- error returned
1270 valid_att_obj_id
1271 (
1272 p_crjv_rec
1273 ,l_api_name
1274 ,l_return_status
1275 );
1276 if (-- error returned
1277 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1278 ) then
1279 if (-- no error so far
1280 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1281 ) then
1282 x_return_status := l_return_status;
1283 end if; -- no error so far
1284 end if; -- error returned
1285 valid_rec_obj_type
1286 (
1287 p_crjv_rec
1288 ,l_api_name
1289 ,l_return_status
1290 );
1291 if (-- error returned
1292 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1293 ) then
1294 if (-- no error so far
1295 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1296 ) then
1297 x_return_status := l_return_status;
1298 end if; -- no error so far
1299 end if; -- error returned
1300 valid_rec_rty_code
1301 (
1302 p_crjv_rec
1303 ,l_api_name
1304 ,l_return_status
1305 );
1306 if (-- error returned
1307 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1308 ) then
1309 if (-- no error so far
1310 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1311 ) then
1312 x_return_status := l_return_status;
1313 end if; -- no error so far
1314 end if; -- error returned
1315 valid_rec_cle_id
1316 (
1317 p_crjv_rec
1318 ,l_api_name
1319 ,l_return_status
1320 );
1321 if (-- error returned
1322 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1323 ) then
1324 if (-- no error so far
1325 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1326 ) then
1327 x_return_status := l_return_status;
1328 end if; -- no error so far
1329 end if; -- error returned
1330 valid_rec_chr_id
1331 (
1332 p_crjv_rec
1333 ,l_api_name
1334 ,l_return_status
1335 );
1336 if (-- error returned
1337 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1338 ) then
1339 if (-- no error so far
1340 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1341 ) then
1342 x_return_status := l_return_status;
1343 end if; -- no error so far
1344 end if; -- error returned
1345 valid_rec_unique
1346 (
1347 p_crjv_rec
1348 ,l_api_name
1349 ,l_return_status
1350 );
1351 if (-- error returned
1352 l_return_status <> OKC_API.G_RET_STS_SUCCESS
1353 ) then
1354 if (-- no error so far
1355 x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1356 ) then
1357 x_return_status := l_return_status;
1358 end if; -- no error so far
1359 end if; -- error returned
1360 --
1361 -- 08/31/00
1362 -- Check for cardinality (Q2K) no longer required
1363 --valid_rec_cardinality
1364 -- (
1365 -- p_crjv_rec
1366 -- ,l_api_name
1367 -- ,l_return_status
1368 -- );
1369 -- if (-- error returned
1370 -- l_return_status <> OKC_API.G_RET_STS_SUCCESS
1371 -- ) then
1372 -- if (-- no error so far
1373 -- x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR
1374 -- ) then
1375 -- x_return_status := l_return_status;
1376 -- end if; -- no error so far
1377 -- end if; -- error returned
1378
1379 /* end mod code
1380 */
1381 RETURN(x_return_status);
1382 END Validate_Attributes;
1383
1384 ---------------------------------------------------------------------------
1385 -- PROCEDURE Migrate
1386 ---------------------------------------------------------------------------
1387 PROCEDURE migrate (
1388 p_from IN crjv_rec_type,
1389 p_to OUT NOCOPY crj_rec_type
1390 ) IS
1391 BEGIN
1392 p_to.id := p_from.id;
1393 p_to.cle_id := p_from.cle_id;
1394 p_to.chr_id := p_from.chr_id;
1395 p_to.rty_code := p_from.rty_code;
1396 p_to.object1_id1 := p_from.object1_id1;
1397 p_to.object1_id2 := p_from.object1_id2;
1398 p_to.JTOT_OBJECT1_CODE := p_from.JTOT_OBJECT1_CODE;
1399 p_to.object_version_number := p_from.object_version_number;
1400 p_to.created_by := p_from.created_by;
1401 p_to.creation_date := p_from.creation_date;
1402 p_to.last_updated_by := p_from.last_updated_by;
1403 p_to.last_update_date := p_from.last_update_date;
1404 p_to.last_update_login := p_from.last_update_login;
1405 p_to.attribute_category := p_from.attribute_category;
1406 p_to.attribute1 := p_from.attribute1;
1407 p_to.attribute2 := p_from.attribute2;
1408 p_to.attribute3 := p_from.attribute3;
1409 p_to.attribute4 := p_from.attribute4;
1410 p_to.attribute5 := p_from.attribute5;
1411 p_to.attribute6 := p_from.attribute6;
1412 p_to.attribute7 := p_from.attribute7;
1413 p_to.attribute8 := p_from.attribute8;
1414 p_to.attribute9 := p_from.attribute9;
1415 p_to.attribute10 := p_from.attribute10;
1416 p_to.attribute11 := p_from.attribute11;
1417 p_to.attribute12 := p_from.attribute12;
1418 p_to.attribute13 := p_from.attribute13;
1419 p_to.attribute14 := p_from.attribute14;
1420 p_to.attribute15 := p_from.attribute15;
1421 END migrate;
1422 PROCEDURE migrate (
1423 p_from IN crj_rec_type,
1424 p_to OUT NOCOPY crjv_rec_type
1425 ) IS
1426 BEGIN
1427 p_to.id := p_from.id;
1428 p_to.cle_id := p_from.cle_id;
1429 p_to.chr_id := p_from.chr_id;
1430 p_to.rty_code := p_from.rty_code;
1431 p_to.object1_id1 := p_from.object1_id1;
1432 p_to.object1_id2 := p_from.object1_id2;
1433 p_to.JTOT_OBJECT1_CODE := p_from.JTOT_OBJECT1_CODE;
1434 p_to.object_version_number := p_from.object_version_number;
1435 p_to.created_by := p_from.created_by;
1436 p_to.creation_date := p_from.creation_date;
1437 p_to.last_updated_by := p_from.last_updated_by;
1438 p_to.last_update_date := p_from.last_update_date;
1439 p_to.last_update_login := p_from.last_update_login;
1440 p_to.attribute_category := p_from.attribute_category;
1441 p_to.attribute1 := p_from.attribute1;
1442 p_to.attribute2 := p_from.attribute2;
1443 p_to.attribute3 := p_from.attribute3;
1444 p_to.attribute4 := p_from.attribute4;
1445 p_to.attribute5 := p_from.attribute5;
1446 p_to.attribute6 := p_from.attribute6;
1447 p_to.attribute7 := p_from.attribute7;
1448 p_to.attribute8 := p_from.attribute8;
1449 p_to.attribute9 := p_from.attribute9;
1450 p_to.attribute10 := p_from.attribute10;
1451 p_to.attribute11 := p_from.attribute11;
1452 p_to.attribute12 := p_from.attribute12;
1453 p_to.attribute13 := p_from.attribute13;
1454 p_to.attribute14 := p_from.attribute14;
1455 p_to.attribute15 := p_from.attribute15;
1456 END migrate;
1457
1458 ---------------------------------------------------------------------------
1459 -- PROCEDURE validate_row
1460 ---------------------------------------------------------------------------
1461 ---------------------------------------
1462 -- validate_row for:OKC_K_REL_OBJS_V --
1463 ---------------------------------------
1464 PROCEDURE validate_row(
1465 p_api_version IN NUMBER,
1466 p_init_msg_list IN VARCHAR2 ,
1467 x_return_status OUT NOCOPY VARCHAR2,
1468 x_msg_count OUT NOCOPY NUMBER,
1469 x_msg_data OUT NOCOPY VARCHAR2,
1470 p_crjv_rec IN crjv_rec_type) IS
1471
1472 l_api_version CONSTANT NUMBER := 1;
1473 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1474 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1475 l_crjv_rec crjv_rec_type := p_crjv_rec;
1476 l_crj_rec crj_rec_type;
1477 BEGIN
1478 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1479 G_PKG_NAME,
1480 p_init_msg_list,
1481 l_api_version,
1482 p_api_version,
1483 '_PVT',
1484 x_return_status);
1485 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1486 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1487 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1488 RAISE OKC_API.G_EXCEPTION_ERROR;
1489 END IF;
1490 --- Validate all non-missing attributes (Item Level Validation)
1491 l_return_status := Validate_Attributes(l_crjv_rec, l_api_name);
1492 --- If any errors happen abort API
1493 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1494 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1495 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1496 RAISE OKC_API.G_EXCEPTION_ERROR;
1497 END IF;
1498 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1499 EXCEPTION
1500 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1501 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1502 (
1503 l_api_name,
1504 G_PKG_NAME,
1505 'OKC_API.G_RET_STS_ERROR',
1506 x_msg_count,
1507 x_msg_data,
1508 '_PVT'
1509 );
1510 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1511 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1512 (
1513 l_api_name,
1514 G_PKG_NAME,
1515 'OKC_API.G_RET_STS_UNEXP_ERROR',
1516 x_msg_count,
1517 x_msg_data,
1518 '_PVT'
1519 );
1520 WHEN OTHERS THEN
1521 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1522 (
1523 l_api_name,
1524 G_PKG_NAME,
1525 'OTHERS',
1526 x_msg_count,
1527 x_msg_data,
1528 '_PVT'
1529 );
1530 END validate_row;
1531 ------------------------------------------
1532 -- PL/SQL TBL validate_row for:CRJV_TBL --
1533 ------------------------------------------
1534 PROCEDURE validate_row(
1535 p_api_version IN NUMBER,
1536 p_init_msg_list IN VARCHAR2 ,
1537 x_return_status OUT NOCOPY VARCHAR2,
1538 x_msg_count OUT NOCOPY NUMBER,
1539 x_msg_data OUT NOCOPY VARCHAR2,
1540 p_crjv_tbl IN crjv_tbl_type) IS
1541
1542 l_api_version CONSTANT NUMBER := 1;
1543 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1544 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1545 i NUMBER := 0;
1546 BEGIN
1547 OKC_API.init_msg_list(p_init_msg_list);
1548 -- Make sure PL/SQL table has records in it before passing
1549 IF (p_crjv_tbl.COUNT > 0) THEN
1550 i := p_crjv_tbl.FIRST;
1551 LOOP
1552 validate_row (
1553 p_api_version => p_api_version,
1554 p_init_msg_list => OKC_API.G_FALSE,
1555 x_return_status => x_return_status,
1556 x_msg_count => x_msg_count,
1557 x_msg_data => x_msg_data,
1558 p_crjv_rec => p_crjv_tbl(i));
1559 EXIT WHEN (i = p_crjv_tbl.LAST);
1560 i := p_crjv_tbl.NEXT(i);
1561 END LOOP;
1562 END IF;
1563 EXCEPTION
1564 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1565 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1566 (
1567 l_api_name,
1568 G_PKG_NAME,
1569 'OKC_API.G_RET_STS_ERROR',
1570 x_msg_count,
1571 x_msg_data,
1572 '_PVT'
1573 );
1574 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1575 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1576 (
1577 l_api_name,
1578 G_PKG_NAME,
1579 'OKC_API.G_RET_STS_UNEXP_ERROR',
1580 x_msg_count,
1581 x_msg_data,
1582 '_PVT'
1583 );
1584 WHEN OTHERS THEN
1585 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1586 (
1587 l_api_name,
1588 G_PKG_NAME,
1589 'OTHERS',
1590 x_msg_count,
1591 x_msg_data,
1592 '_PVT'
1593 );
1594 END validate_row;
1595
1596 ---------------------------------------------------------------------------
1597 -- PROCEDURE insert_row
1598 ---------------------------------------------------------------------------
1599 -----------------------------------
1600 -- insert_row for:OKC_K_REL_OBJS --
1601 -----------------------------------
1602 PROCEDURE insert_row(
1603 p_init_msg_list IN VARCHAR2 ,
1604 x_return_status OUT NOCOPY VARCHAR2,
1605 x_msg_count OUT NOCOPY NUMBER,
1606 x_msg_data OUT NOCOPY VARCHAR2,
1607 p_crj_rec IN crj_rec_type,
1608 x_crj_rec OUT NOCOPY crj_rec_type) IS
1609
1610 l_api_version CONSTANT NUMBER := 1;
1611 l_api_name CONSTANT VARCHAR2(30) := 'OBJS_insert_row';
1612 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1613 l_crj_rec crj_rec_type := p_crj_rec;
1614 l_def_crj_rec crj_rec_type;
1615 ---------------------------------------
1616 -- Set_Attributes for:OKC_K_REL_OBJS --
1617 ---------------------------------------
1618 FUNCTION Set_Attributes (
1619 p_crj_rec IN crj_rec_type,
1620 x_crj_rec OUT NOCOPY crj_rec_type
1621 ) RETURN VARCHAR2 IS
1622 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1623 BEGIN
1624 x_crj_rec := p_crj_rec;
1625 RETURN(l_return_status);
1626 END Set_Attributes;
1627 BEGIN
1628 IF (l_debug = 'Y') THEN
1629 okc_util.print_trace(5, ' ');
1630 okc_util.print_trace(5, '>START - OKC_CRJ_PVT.INSERT_ROW -');
1631 END IF;
1632 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1633 p_init_msg_list,
1634 '_PVT',
1635 x_return_status);
1636 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1637 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1638 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1639 RAISE OKC_API.G_EXCEPTION_ERROR;
1640 END IF;
1641 --- Setting item attributes
1642 l_return_status := Set_Attributes(
1643 p_crj_rec, -- IN
1644 l_crj_rec); -- OUT
1645 --- If any errors happen abort API
1646 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1647 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1648 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1649 RAISE OKC_API.G_EXCEPTION_ERROR;
1650 END IF;
1651
1652 INSERT INTO OKC_K_REL_OBJS(
1653 id,
1654 cle_id,
1655 chr_id,
1656 rty_code,
1657 object1_id1,
1658 object1_id2,
1659 JTOT_OBJECT1_CODE,
1660 object_version_number,
1661 created_by,
1662 creation_date,
1663 last_updated_by,
1664 last_update_date,
1665 last_update_login,
1666 attribute_category,
1667 attribute1,
1668 attribute2,
1669 attribute3,
1670 attribute4,
1671 attribute5,
1672 attribute6,
1673 attribute7,
1674 attribute8,
1675 attribute9,
1676 attribute10,
1677 attribute11,
1678 attribute12,
1679 attribute13,
1680 attribute14,
1681 attribute15)
1682 VALUES (
1683 l_crj_rec.id,
1684 l_crj_rec.cle_id,
1685 l_crj_rec.chr_id,
1686 l_crj_rec.rty_code,
1687 l_crj_rec.object1_id1,
1688 l_crj_rec.object1_id2,
1689 l_crj_rec.JTOT_OBJECT1_CODE,
1690 l_crj_rec.object_version_number,
1691 l_crj_rec.created_by,
1692 l_crj_rec.creation_date,
1693 l_crj_rec.last_updated_by,
1694 l_crj_rec.last_update_date,
1695 l_crj_rec.last_update_login,
1696 l_crj_rec.attribute_category,
1697 l_crj_rec.attribute1,
1698 l_crj_rec.attribute2,
1699 l_crj_rec.attribute3,
1700 l_crj_rec.attribute4,
1701 l_crj_rec.attribute5,
1702 l_crj_rec.attribute6,
1703 l_crj_rec.attribute7,
1704 l_crj_rec.attribute8,
1705 l_crj_rec.attribute9,
1706 l_crj_rec.attribute10,
1707 l_crj_rec.attribute11,
1708 l_crj_rec.attribute12,
1709 l_crj_rec.attribute13,
1710 l_crj_rec.attribute14,
1711 l_crj_rec.attribute15);
1712 IF (l_debug = 'Y') THEN
1713 okc_util.print_trace(5, 'Insertion into OKC_K_REL_OBJS:');
1714 okc_util.print_trace(5, '==============================');
1715 okc_util.print_trace(6, 'Id = '||l_crj_rec.id);
1716 okc_util.print_trace(6, 'Contract Id = '||l_crj_rec.chr_id);
1717 okc_util.print_trace(6, 'Contract Line Id = '||l_crj_rec.cle_id);
1718 okc_util.print_trace(6, 'Relation type = '||l_crj_rec.rty_code);
1719 okc_util.print_trace(6, 'Quote object = '||l_crj_rec.jtot_object1_code);
1720 END IF;
1721 IF l_crj_rec.jtot_object1_code = 'OKX_QUOTEHEAD' THEN
1722 IF (l_debug = 'Y') THEN
1723 okc_util.print_trace(6, 'Quote id1 = '||l_crj_rec.object1_id1);
1724 okc_util.print_trace(6, 'Quote id2 = '||l_crj_rec.object1_id2);
1725 END IF;
1726 ELSE
1727 IF (l_debug = 'Y') THEN
1728 okc_util.print_trace(6, 'Quote Line id1 = '||l_crj_rec.object1_id1);
1729 okc_util.print_trace(6, 'Quote Line id2 = '||l_crj_rec.object1_id2);
1730 END IF;
1731 END IF;
1732
1733 -- Set OUT values
1734 x_crj_rec := l_crj_rec;
1735 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1736 IF (l_debug = 'Y') THEN
1737 okc_util.print_trace(5, '<END - OKC_CRJ_PVT.INSERT_ROW -');
1738 END IF;
1739 EXCEPTION
1740 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1741 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1742 (
1743 l_api_name,
1744 G_PKG_NAME,
1745 'OKC_API.G_RET_STS_ERROR',
1746 x_msg_count,
1747 x_msg_data,
1748 '_PVT'
1749 );
1750 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1751 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1752 (
1753 l_api_name,
1754 G_PKG_NAME,
1755 'OKC_API.G_RET_STS_UNEXP_ERROR',
1756 x_msg_count,
1757 x_msg_data,
1758 '_PVT'
1759 );
1760 WHEN OTHERS THEN
1761 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1762 (
1763 l_api_name,
1764 G_PKG_NAME,
1765 'OTHERS',
1766 x_msg_count,
1767 x_msg_data,
1768 '_PVT'
1769 );
1770 END insert_row;
1771 -------------------------------------
1772 -- insert_row for:OKC_K_REL_OBJS_V --
1773 -------------------------------------
1774 PROCEDURE insert_row(
1775 p_api_version IN NUMBER,
1776 p_init_msg_list IN VARCHAR2 ,
1777 x_return_status OUT NOCOPY VARCHAR2,
1778 x_msg_count OUT NOCOPY NUMBER,
1779 x_msg_data OUT NOCOPY VARCHAR2,
1780 p_crjv_rec IN crjv_rec_type,
1781 x_crjv_rec OUT NOCOPY crjv_rec_type) IS
1782
1783 l_api_version CONSTANT NUMBER := 1;
1784 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1785 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1786 l_crjv_rec crjv_rec_type;
1787 l_def_crjv_rec crjv_rec_type;
1788 l_crj_rec crj_rec_type;
1789 lx_crj_rec crj_rec_type;
1790 -------------------------------
1791 -- FUNCTION fill_who_columns --
1792 -------------------------------
1793 FUNCTION fill_who_columns (
1794 p_crjv_rec IN crjv_rec_type
1795 ) RETURN crjv_rec_type IS
1796 l_crjv_rec crjv_rec_type := p_crjv_rec;
1797 BEGIN
1798 l_crjv_rec.CREATION_DATE := SYSDATE;
1799 l_crjv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1800 --l_crjv_rec.LAST_UPDATE_DATE := SYSDATE;
1801 l_crjv_rec.LAST_UPDATE_DATE := l_crjv_rec.CREATION_DATE;
1802 l_crjv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1803 l_crjv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1804 RETURN(l_crjv_rec);
1805 END fill_who_columns;
1806 -----------------------------------------
1807 -- Set_Attributes for:OKC_K_REL_OBJS_V --
1808 -----------------------------------------
1809 FUNCTION Set_Attributes (
1810 p_crjv_rec IN crjv_rec_type,
1811 x_crjv_rec OUT NOCOPY crjv_rec_type
1812 ) RETURN VARCHAR2 IS
1813 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1814 BEGIN
1815 x_crjv_rec := p_crjv_rec;
1816 x_crjv_rec.OBJECT_VERSION_NUMBER := 1;
1817 RETURN(l_return_status);
1818 END Set_Attributes;
1819 BEGIN
1820
1821 IF (l_debug = 'Y') THEN
1822 okc_util.print_trace(4, ' ');
1823 okc_util.print_trace(4, '>START - OKC_CRJ_PVT.INSERT_ROW -');
1824 END IF;
1825 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1826 G_PKG_NAME,
1827 p_init_msg_list,
1828 l_api_version,
1829 p_api_version,
1830 '_PVT',
1831 x_return_status);
1832 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1833 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1834 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1835 RAISE OKC_API.G_EXCEPTION_ERROR;
1836 END IF;
1837 l_crjv_rec := null_out_defaults(p_crjv_rec);
1838 -- Set primary key value
1839 l_crjv_rec.ID := get_seq_id;
1840 --- Setting item attributes
1841 l_return_status := Set_Attributes(
1842 l_crjv_rec, -- IN
1843 l_def_crjv_rec); -- OUT
1844 --- If any errors happen abort API
1845 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1846 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1847 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1848 RAISE OKC_API.G_EXCEPTION_ERROR;
1849 END IF;
1850
1851 IF (l_debug = 'Y') THEN
1852 okc_util.print_trace(5, 'calling fill_who_columns');
1853 END IF;
1854 l_def_crjv_rec := fill_who_columns(l_def_crjv_rec);
1855 --- Validate all non-missing attributes (Item Level Validation)
1856
1857 IF (l_debug = 'Y') THEN
1858 okc_util.print_trace(5, 'calling Validate_Attributes');
1859 okc_util.print_trace(6, 'l_def_crjv_rec.id:' || l_def_crjv_rec.id);
1860 okc_util.print_trace(6, 'l_def_crjv_rec.cle_id:' || l_def_crjv_rec.cle_id);
1861 okc_util.print_trace(6, 'l_def_crjv_rec.chr_id:' || l_def_crjv_rec.chr_id);
1862 okc_util.print_trace(6, 'l_def_crjv_rec.rty_code:' || l_def_crjv_rec.rty_code);
1863 okc_util.print_trace(6, 'l_def_crjv_rec.object1_id1:' || l_def_crjv_rec.object1_id1);
1864 okc_util.print_trace(6, 'l_def_crjv_rec.jtot_object1_id:' || l_def_crjv_rec.jtot_object1_id);
1865 okc_util.print_trace(6, 'l_def_crjv_rec.jtot_object1_code:' || l_def_crjv_rec.jtot_object1_code);
1866 okc_util.print_trace(6, '');
1867 END IF;
1868 l_return_status := Validate_Attributes(l_def_crjv_rec, l_api_name);
1869
1870 --- If any errors happen abort API
1871 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1872 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1873 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1874 RAISE OKC_API.G_EXCEPTION_ERROR;
1875 END IF;
1876 --------------------------------------
1877 -- Move VIEW record to "Child" records
1878 --------------------------------------
1879 IF (l_debug = 'Y') THEN
1880 okc_util.print_trace(5, 'calling migrate');
1881 END IF;
1882 migrate(l_def_crjv_rec, l_crj_rec);
1883 --------------------------------------------
1884 -- Call the INSERT_ROW for each child record
1885 --------------------------------------------
1886
1887 IF (l_debug = 'Y') THEN
1888 okc_util.print_trace(5, 'before insert');
1889 END IF;
1890 insert_row(
1891 p_init_msg_list,
1892 x_return_status,
1893 x_msg_count,
1894 x_msg_data,
1895 l_crj_rec,
1896 lx_crj_rec
1897 );
1898 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1899 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1900 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1901 RAISE OKC_API.G_EXCEPTION_ERROR;
1902 END IF;
1903 migrate(lx_crj_rec, l_def_crjv_rec);
1904 -- Set OUT values
1905 x_crjv_rec := l_def_crjv_rec;
1906 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1907 IF (l_debug = 'Y') THEN
1908 okc_util.print_trace(4, '<END - OKC_CRJ_PVT.INSERT_ROW -');
1909 END IF;
1910 EXCEPTION
1911 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1912 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1913 (
1914 l_api_name,
1915 G_PKG_NAME,
1916 'OKC_API.G_RET_STS_ERROR',
1917 x_msg_count,
1918 x_msg_data,
1919 '_PVT'
1920 );
1921 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1922 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1923 (
1924 l_api_name,
1925 G_PKG_NAME,
1926 'OKC_API.G_RET_STS_UNEXP_ERROR',
1927 x_msg_count,
1928 x_msg_data,
1929 '_PVT'
1930 );
1931 WHEN OTHERS THEN
1932 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1933 (
1934 l_api_name,
1935 G_PKG_NAME,
1936 'OTHERS',
1937 x_msg_count,
1938 x_msg_data,
1939 '_PVT'
1940 );
1941 END insert_row;
1942 ----------------------------------------
1943 -- PL/SQL TBL insert_row for:CRJV_TBL --
1944 ----------------------------------------
1945 PROCEDURE insert_row(
1946 p_api_version IN NUMBER,
1947 p_init_msg_list IN VARCHAR2 ,
1948 x_return_status OUT NOCOPY VARCHAR2,
1949 x_msg_count OUT NOCOPY NUMBER,
1950 x_msg_data OUT NOCOPY VARCHAR2,
1951 p_crjv_tbl IN crjv_tbl_type,
1952 x_crjv_tbl OUT NOCOPY crjv_tbl_type) IS
1953
1954 l_api_version CONSTANT NUMBER := 1;
1955 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1956 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1957 i NUMBER := 0;
1958 BEGIN
1959 OKC_API.init_msg_list(p_init_msg_list);
1960 -- Make sure PL/SQL table has records in it before passing
1961 IF (p_crjv_tbl.COUNT > 0) THEN
1962 i := p_crjv_tbl.FIRST;
1963 LOOP
1964 insert_row (
1965 p_api_version => p_api_version,
1966 p_init_msg_list => OKC_API.G_FALSE,
1967 x_return_status => x_return_status,
1968 x_msg_count => x_msg_count,
1969 x_msg_data => x_msg_data,
1970 p_crjv_rec => p_crjv_tbl(i),
1971 x_crjv_rec => x_crjv_tbl(i));
1972 EXIT WHEN (i = p_crjv_tbl.LAST);
1973 i := p_crjv_tbl.NEXT(i);
1974 END LOOP;
1975 END IF;
1976 EXCEPTION
1977 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1978 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1979 (
1980 l_api_name,
1981 G_PKG_NAME,
1982 'OKC_API.G_RET_STS_ERROR',
1983 x_msg_count,
1984 x_msg_data,
1985 '_PVT'
1986 );
1987 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1988 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1989 (
1990 l_api_name,
1991 G_PKG_NAME,
1992 'OKC_API.G_RET_STS_UNEXP_ERROR',
1993 x_msg_count,
1994 x_msg_data,
1995 '_PVT'
1996 );
1997 WHEN OTHERS THEN
1998 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1999 (
2000 l_api_name,
2001 G_PKG_NAME,
2002 'OTHERS',
2003 x_msg_count,
2004 x_msg_data,
2005 '_PVT'
2006 );
2007 END insert_row;
2008
2009 ---------------------------------------------------------------------------
2010 -- PROCEDURE lock_row
2011 ---------------------------------------------------------------------------
2012 ---------------------------------
2013 -- lock_row for:OKC_K_REL_OBJS --
2014 ---------------------------------
2015 PROCEDURE lock_row(
2016 p_init_msg_list IN VARCHAR2 ,
2017 x_return_status OUT NOCOPY VARCHAR2,
2018 x_msg_count OUT NOCOPY NUMBER,
2019 x_msg_data OUT NOCOPY VARCHAR2,
2020 p_crj_rec IN crj_rec_type) IS
2021
2022 E_Resource_Busy EXCEPTION;
2023 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2024 CURSOR lock_csr (p_crj_rec IN crj_rec_type) IS
2025 SELECT OBJECT_VERSION_NUMBER
2026 FROM OKC_K_REL_OBJS
2027 WHERE ID = p_crj_rec.id
2028 AND OBJECT_VERSION_NUMBER = p_crj_rec.object_version_number
2029 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2030
2031 CURSOR lchk_csr (p_crj_rec IN crj_rec_type) IS
2032 SELECT OBJECT_VERSION_NUMBER
2033 FROM OKC_K_REL_OBJS
2034 WHERE ID = p_crj_rec.id;
2035 l_api_version CONSTANT NUMBER := 1;
2036 l_api_name CONSTANT VARCHAR2(30) := 'OBJS_lock_row';
2037 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2038 l_object_version_number OKC_K_REL_OBJS.OBJECT_VERSION_NUMBER%TYPE;
2039 lc_object_version_number OKC_K_REL_OBJS.OBJECT_VERSION_NUMBER%TYPE;
2040 l_row_notfound BOOLEAN := FALSE;
2041 lc_row_notfound BOOLEAN := FALSE;
2042 BEGIN
2043 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2044 p_init_msg_list,
2045 '_PVT',
2046 x_return_status);
2047 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2048 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2049 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2050 RAISE OKC_API.G_EXCEPTION_ERROR;
2051 END IF;
2052 BEGIN
2053 OPEN lock_csr(p_crj_rec);
2054 FETCH lock_csr INTO l_object_version_number;
2055 l_row_notfound := lock_csr%NOTFOUND;
2056 CLOSE lock_csr;
2057 EXCEPTION
2058 WHEN E_Resource_Busy THEN
2059 IF (lock_csr%ISOPEN) THEN
2060 CLOSE lock_csr;
2061 END IF;
2062 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2063 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2064 END;
2065
2066 IF ( l_row_notfound ) THEN
2067 OPEN lchk_csr(p_crj_rec);
2068 FETCH lchk_csr INTO lc_object_version_number;
2069 lc_row_notfound := lchk_csr%NOTFOUND;
2070 CLOSE lchk_csr;
2071 END IF;
2072 IF (lc_row_notfound) THEN
2073 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2074 RAISE OKC_API.G_EXCEPTION_ERROR;
2075 ELSIF lc_object_version_number > p_crj_rec.object_version_number THEN
2076 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2077 RAISE OKC_API.G_EXCEPTION_ERROR;
2078 ELSIF lc_object_version_number <> p_crj_rec.object_version_number THEN
2079 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2080 RAISE OKC_API.G_EXCEPTION_ERROR;
2081 ELSIF lc_object_version_number = -1 THEN
2082 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2083 RAISE OKC_API.G_EXCEPTION_ERROR;
2084 END IF;
2085 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2086 EXCEPTION
2087 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2088 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2089 (
2090 l_api_name,
2091 G_PKG_NAME,
2092 'OKC_API.G_RET_STS_ERROR',
2093 x_msg_count,
2094 x_msg_data,
2095 '_PVT'
2096 );
2097 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2098 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2099 (
2100 l_api_name,
2101 G_PKG_NAME,
2102 'OKC_API.G_RET_STS_UNEXP_ERROR',
2103 x_msg_count,
2104 x_msg_data,
2105 '_PVT'
2106 );
2107 WHEN OTHERS THEN
2108 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2109 (
2110 l_api_name,
2111 G_PKG_NAME,
2112 'OTHERS',
2113 x_msg_count,
2114 x_msg_data,
2115 '_PVT'
2116 );
2117 END lock_row;
2118 -----------------------------------
2119 -- lock_row for:OKC_K_REL_OBJS_V --
2120 -----------------------------------
2121 PROCEDURE lock_row(
2122 p_api_version IN NUMBER,
2123 p_init_msg_list IN VARCHAR2 ,
2124 x_return_status OUT NOCOPY VARCHAR2,
2125 x_msg_count OUT NOCOPY NUMBER,
2126 x_msg_data OUT NOCOPY VARCHAR2,
2127 p_crjv_rec IN crjv_rec_type) IS
2128
2129 l_api_version CONSTANT NUMBER := 1;
2130 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2131 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2132 l_crj_rec crj_rec_type;
2133 BEGIN
2134 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2135 G_PKG_NAME,
2136 p_init_msg_list,
2137 l_api_version,
2138 p_api_version,
2139 '_PVT',
2140 x_return_status);
2141 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2142 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2143 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2144 RAISE OKC_API.G_EXCEPTION_ERROR;
2145 END IF;
2146 --------------------------------------
2147 -- Move VIEW record to "Child" records
2148 --------------------------------------
2149 migrate(p_crjv_rec, l_crj_rec);
2150 --------------------------------------------
2151 -- Call the LOCK_ROW for each child record
2152 --------------------------------------------
2153 lock_row(
2154 p_init_msg_list,
2155 x_return_status,
2156 x_msg_count,
2157 x_msg_data,
2158 l_crj_rec
2159 );
2160 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2161 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2162 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2163 RAISE OKC_API.G_EXCEPTION_ERROR;
2164 END IF;
2165 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2166 EXCEPTION
2167 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2168 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2169 (
2170 l_api_name,
2171 G_PKG_NAME,
2172 'OKC_API.G_RET_STS_ERROR',
2173 x_msg_count,
2174 x_msg_data,
2175 '_PVT'
2176 );
2177 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2178 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2179 (
2180 l_api_name,
2181 G_PKG_NAME,
2182 'OKC_API.G_RET_STS_UNEXP_ERROR',
2183 x_msg_count,
2184 x_msg_data,
2185 '_PVT'
2186 );
2187 WHEN OTHERS THEN
2188 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2189 (
2190 l_api_name,
2191 G_PKG_NAME,
2192 'OTHERS',
2193 x_msg_count,
2194 x_msg_data,
2195 '_PVT'
2196 );
2197 END lock_row;
2198 --------------------------------------
2199 -- PL/SQL TBL lock_row for:CRJV_TBL --
2200 --------------------------------------
2201 PROCEDURE lock_row(
2202 p_api_version IN NUMBER,
2203 p_init_msg_list IN VARCHAR2 ,
2204 x_return_status OUT NOCOPY VARCHAR2,
2205 x_msg_count OUT NOCOPY NUMBER,
2206 x_msg_data OUT NOCOPY VARCHAR2,
2207 p_crjv_tbl IN crjv_tbl_type) IS
2208
2209 l_api_version CONSTANT NUMBER := 1;
2210 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2211 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2212 i NUMBER := 0;
2213 BEGIN
2214 OKC_API.init_msg_list(p_init_msg_list);
2215 -- Make sure PL/SQL table has records in it before passing
2216 IF (p_crjv_tbl.COUNT > 0) THEN
2217 i := p_crjv_tbl.FIRST;
2218 LOOP
2219 lock_row (
2220 p_api_version => p_api_version,
2221 p_init_msg_list => OKC_API.G_FALSE,
2222 x_return_status => x_return_status,
2223 x_msg_count => x_msg_count,
2224 x_msg_data => x_msg_data,
2225 p_crjv_rec => p_crjv_tbl(i));
2226 EXIT WHEN (i = p_crjv_tbl.LAST);
2227 i := p_crjv_tbl.NEXT(i);
2228 END LOOP;
2229 END IF;
2230 EXCEPTION
2231 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2232 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2233 (
2234 l_api_name,
2235 G_PKG_NAME,
2236 'OKC_API.G_RET_STS_ERROR',
2237 x_msg_count,
2238 x_msg_data,
2239 '_PVT'
2240 );
2241 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2242 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2243 (
2244 l_api_name,
2245 G_PKG_NAME,
2246 'OKC_API.G_RET_STS_UNEXP_ERROR',
2247 x_msg_count,
2248 x_msg_data,
2249 '_PVT'
2250 );
2251 WHEN OTHERS THEN
2252 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2253 (
2254 l_api_name,
2255 G_PKG_NAME,
2256 'OTHERS',
2257 x_msg_count,
2258 x_msg_data,
2259 '_PVT'
2260 );
2261 END lock_row;
2262
2263 ---------------------------------------------------------------------------
2264 -- PROCEDURE update_row
2265 ---------------------------------------------------------------------------
2266 -----------------------------------
2267 -- update_row for:OKC_K_REL_OBJS --
2268 -----------------------------------
2269 PROCEDURE update_row(
2270 p_init_msg_list IN VARCHAR2 ,
2271 x_return_status OUT NOCOPY VARCHAR2,
2272 x_msg_count OUT NOCOPY NUMBER,
2273 x_msg_data OUT NOCOPY VARCHAR2,
2274 p_crj_rec IN crj_rec_type,
2275 x_crj_rec OUT NOCOPY crj_rec_type) IS
2276
2277 l_api_version CONSTANT NUMBER := 1;
2278 l_api_name CONSTANT VARCHAR2(30) := 'OBJS_update_row';
2279 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2280 l_crj_rec crj_rec_type := p_crj_rec;
2281 l_def_crj_rec crj_rec_type;
2282 l_row_notfound BOOLEAN := TRUE;
2283 ----------------------------------
2284 -- FUNCTION populate_new_record --
2285 ----------------------------------
2286 FUNCTION populate_new_record (
2287 p_crj_rec IN crj_rec_type,
2288 x_crj_rec OUT NOCOPY crj_rec_type
2289 ) RETURN VARCHAR2 IS
2290 l_crj_rec crj_rec_type;
2291 l_row_notfound BOOLEAN := TRUE;
2292 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2293 BEGIN
2294 x_crj_rec := p_crj_rec;
2295 -- Get current database values
2296 l_crj_rec := get_rec(p_crj_rec, l_row_notfound);
2297 IF (l_row_notfound) THEN
2298 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2299 END IF;
2300 IF (x_crj_rec.id = OKC_API.G_MISS_NUM)
2301 THEN
2302 x_crj_rec.id := l_crj_rec.id;
2303 END IF;
2304 IF (x_crj_rec.cle_id = OKC_API.G_MISS_NUM)
2305 THEN
2306 x_crj_rec.cle_id := l_crj_rec.cle_id;
2307 END IF;
2308 IF (x_crj_rec.chr_id = OKC_API.G_MISS_NUM)
2309 THEN
2310 x_crj_rec.chr_id := l_crj_rec.chr_id;
2311 END IF;
2312 IF (x_crj_rec.rty_code = OKC_API.G_MISS_CHAR)
2313 THEN
2314 x_crj_rec.rty_code := l_crj_rec.rty_code;
2315 END IF;
2316 IF (x_crj_rec.object1_id1 = OKC_API.G_MISS_CHAR)
2317 THEN
2318 x_crj_rec.object1_id1 := l_crj_rec.object1_id1;
2319 END IF;
2320 IF (x_crj_rec.object1_id2 = OKC_API.G_MISS_CHAR)
2321 THEN
2322 x_crj_rec.object1_id2 := l_crj_rec.object1_id2;
2323 END IF;
2324 IF (x_crj_rec.JTOT_OBJECT1_CODE = OKC_API.G_MISS_CHAR)
2325 THEN
2326 x_crj_rec.JTOT_OBJECT1_CODE := l_crj_rec.JTOT_OBJECT1_CODE;
2327 END IF;
2328 IF (x_crj_rec.object_version_number = OKC_API.G_MISS_NUM)
2329 THEN
2330 x_crj_rec.object_version_number := l_crj_rec.object_version_number;
2331 END IF;
2332 IF (x_crj_rec.created_by = OKC_API.G_MISS_NUM)
2333 THEN
2334 x_crj_rec.created_by := l_crj_rec.created_by;
2335 END IF;
2336 IF (x_crj_rec.creation_date = OKC_API.G_MISS_DATE)
2337 THEN
2338 x_crj_rec.creation_date := l_crj_rec.creation_date;
2339 END IF;
2340 IF (x_crj_rec.last_updated_by = OKC_API.G_MISS_NUM)
2341 THEN
2342 x_crj_rec.last_updated_by := l_crj_rec.last_updated_by;
2343 END IF;
2344 IF (x_crj_rec.last_update_date = OKC_API.G_MISS_DATE)
2345 THEN
2346 x_crj_rec.last_update_date := l_crj_rec.last_update_date;
2347 END IF;
2348 IF (x_crj_rec.last_update_login = OKC_API.G_MISS_NUM)
2349 THEN
2350 x_crj_rec.last_update_login := l_crj_rec.last_update_login;
2351 END IF;
2352 IF (x_crj_rec.attribute_category = OKC_API.G_MISS_CHAR)
2353 THEN
2354 x_crj_rec.attribute_category := l_crj_rec.attribute_category;
2355 END IF;
2356 IF (x_crj_rec.attribute1 = OKC_API.G_MISS_CHAR)
2357 THEN
2358 x_crj_rec.attribute1 := l_crj_rec.attribute1;
2359 END IF;
2360 IF (x_crj_rec.attribute2 = OKC_API.G_MISS_CHAR)
2361 THEN
2362 x_crj_rec.attribute2 := l_crj_rec.attribute2;
2363 END IF;
2364 IF (x_crj_rec.attribute3 = OKC_API.G_MISS_CHAR)
2365 THEN
2366 x_crj_rec.attribute3 := l_crj_rec.attribute3;
2367 END IF;
2368 IF (x_crj_rec.attribute4 = OKC_API.G_MISS_CHAR)
2369 THEN
2370 x_crj_rec.attribute4 := l_crj_rec.attribute4;
2371 END IF;
2372 IF (x_crj_rec.attribute5 = OKC_API.G_MISS_CHAR)
2373 THEN
2374 x_crj_rec.attribute5 := l_crj_rec.attribute5;
2375 END IF;
2376 IF (x_crj_rec.attribute6 = OKC_API.G_MISS_CHAR)
2377 THEN
2378 x_crj_rec.attribute6 := l_crj_rec.attribute6;
2379 END IF;
2380 IF (x_crj_rec.attribute7 = OKC_API.G_MISS_CHAR)
2381 THEN
2382 x_crj_rec.attribute7 := l_crj_rec.attribute7;
2383 END IF;
2384 IF (x_crj_rec.attribute8 = OKC_API.G_MISS_CHAR)
2385 THEN
2386 x_crj_rec.attribute8 := l_crj_rec.attribute8;
2387 END IF;
2388 IF (x_crj_rec.attribute9 = OKC_API.G_MISS_CHAR)
2389 THEN
2390 x_crj_rec.attribute9 := l_crj_rec.attribute9;
2391 END IF;
2392 IF (x_crj_rec.attribute10 = OKC_API.G_MISS_CHAR)
2393 THEN
2394 x_crj_rec.attribute10 := l_crj_rec.attribute10;
2395 END IF;
2396 IF (x_crj_rec.attribute11 = OKC_API.G_MISS_CHAR)
2397 THEN
2398 x_crj_rec.attribute11 := l_crj_rec.attribute11;
2399 END IF;
2400 IF (x_crj_rec.attribute12 = OKC_API.G_MISS_CHAR)
2401 THEN
2402 x_crj_rec.attribute12 := l_crj_rec.attribute12;
2403 END IF;
2404 IF (x_crj_rec.attribute13 = OKC_API.G_MISS_CHAR)
2405 THEN
2406 x_crj_rec.attribute13 := l_crj_rec.attribute13;
2407 END IF;
2408 IF (x_crj_rec.attribute14 = OKC_API.G_MISS_CHAR)
2409 THEN
2410 x_crj_rec.attribute14 := l_crj_rec.attribute14;
2411 END IF;
2412 IF (x_crj_rec.attribute15 = OKC_API.G_MISS_CHAR)
2413 THEN
2414 x_crj_rec.attribute15 := l_crj_rec.attribute15;
2415 END IF;
2416 RETURN(l_return_status);
2417 END populate_new_record;
2418 ---------------------------------------
2419 -- Set_Attributes for:OKC_K_REL_OBJS --
2420 ---------------------------------------
2421 FUNCTION Set_Attributes (
2422 p_crj_rec IN crj_rec_type,
2423 x_crj_rec OUT NOCOPY crj_rec_type
2424 ) RETURN VARCHAR2 IS
2425 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2426 BEGIN
2427 x_crj_rec := p_crj_rec;
2428 RETURN(l_return_status);
2429 END Set_Attributes;
2430 BEGIN
2431 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2432 p_init_msg_list,
2433 '_PVT',
2434 x_return_status);
2435 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2436 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2437 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2438 RAISE OKC_API.G_EXCEPTION_ERROR;
2439 END IF;
2440 --- Setting item attributes
2441 l_return_status := Set_Attributes(
2442 p_crj_rec, -- IN
2443 l_crj_rec); -- OUT
2444 --- If any errors happen abort API
2445 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2446 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2447 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2448 RAISE OKC_API.G_EXCEPTION_ERROR;
2449 END IF;
2450 l_return_status := populate_new_record(l_crj_rec, l_def_crj_rec);
2451 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2452 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2453 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2454 RAISE OKC_API.G_EXCEPTION_ERROR;
2455 END IF;
2456 UPDATE OKC_K_REL_OBJS
2457 SET CLE_ID = l_def_crj_rec.cle_id,
2458 CHR_ID = l_def_crj_rec.chr_id,
2459 RTY_CODE = l_def_crj_rec.rty_code,
2460 OBJECT1_ID1 = l_def_crj_rec.object1_id1,
2461 OBJECT1_ID2 = l_def_crj_rec.object1_id2,
2462 JTOT_OBJECT1_CODE = l_def_crj_rec.JTOT_OBJECT1_CODE,
2463 OBJECT_VERSION_NUMBER = l_def_crj_rec.object_version_number,
2464 CREATED_BY = l_def_crj_rec.created_by,
2465 CREATION_DATE = l_def_crj_rec.creation_date,
2466 LAST_UPDATED_BY = l_def_crj_rec.last_updated_by,
2467 LAST_UPDATE_DATE = l_def_crj_rec.last_update_date,
2468 LAST_UPDATE_LOGIN = l_def_crj_rec.last_update_login,
2469 ATTRIBUTE_CATEGORY = l_def_crj_rec.attribute_category,
2470 ATTRIBUTE1 = l_def_crj_rec.attribute1,
2471 ATTRIBUTE2 = l_def_crj_rec.attribute2,
2472 ATTRIBUTE3 = l_def_crj_rec.attribute3,
2473 ATTRIBUTE4 = l_def_crj_rec.attribute4,
2474 ATTRIBUTE5 = l_def_crj_rec.attribute5,
2475 ATTRIBUTE6 = l_def_crj_rec.attribute6,
2476 ATTRIBUTE7 = l_def_crj_rec.attribute7,
2477 ATTRIBUTE8 = l_def_crj_rec.attribute8,
2478 ATTRIBUTE9 = l_def_crj_rec.attribute9,
2479 ATTRIBUTE10 = l_def_crj_rec.attribute10,
2480 ATTRIBUTE11 = l_def_crj_rec.attribute11,
2481 ATTRIBUTE12 = l_def_crj_rec.attribute12,
2482 ATTRIBUTE13 = l_def_crj_rec.attribute13,
2483 ATTRIBUTE14 = l_def_crj_rec.attribute14,
2484 ATTRIBUTE15 = l_def_crj_rec.attribute15
2485 WHERE ID = l_def_crj_rec.id;
2486
2487 x_crj_rec := l_def_crj_rec;
2488 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2489 EXCEPTION
2490 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2491 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2492 (
2493 l_api_name,
2494 G_PKG_NAME,
2495 'OKC_API.G_RET_STS_ERROR',
2496 x_msg_count,
2497 x_msg_data,
2498 '_PVT'
2499 );
2500 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2501 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2502 (
2503 l_api_name,
2504 G_PKG_NAME,
2505 'OKC_API.G_RET_STS_UNEXP_ERROR',
2506 x_msg_count,
2507 x_msg_data,
2508 '_PVT'
2509 );
2510 WHEN OTHERS THEN
2511 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2512 (
2513 l_api_name,
2514 G_PKG_NAME,
2515 'OTHERS',
2516 x_msg_count,
2517 x_msg_data,
2518 '_PVT'
2519 );
2520 END update_row;
2521 -------------------------------------
2522 -- update_row for:OKC_K_REL_OBJS_V --
2523 -------------------------------------
2524 PROCEDURE update_row(
2525 p_api_version IN NUMBER,
2526 p_init_msg_list IN VARCHAR2 ,
2527 x_return_status OUT NOCOPY VARCHAR2,
2528 x_msg_count OUT NOCOPY NUMBER,
2529 x_msg_data OUT NOCOPY VARCHAR2,
2530 p_crjv_rec IN crjv_rec_type,
2531 x_crjv_rec OUT NOCOPY crjv_rec_type) IS
2532
2533 l_api_version CONSTANT NUMBER := 1;
2534 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2535 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2536 l_crjv_rec crjv_rec_type := p_crjv_rec;
2537 l_def_crjv_rec crjv_rec_type;
2538 l_crj_rec crj_rec_type;
2539 lx_crj_rec crj_rec_type;
2540 -------------------------------
2541 -- FUNCTION fill_who_columns --
2542 -------------------------------
2543 FUNCTION fill_who_columns (
2544 p_crjv_rec IN crjv_rec_type
2545 ) RETURN crjv_rec_type IS
2546 l_crjv_rec crjv_rec_type := p_crjv_rec;
2547 BEGIN
2548 l_crjv_rec.LAST_UPDATE_DATE := SYSDATE;
2549 l_crjv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2550 l_crjv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2551 RETURN(l_crjv_rec);
2552 END fill_who_columns;
2553 ----------------------------------
2554 -- FUNCTION populate_new_record --
2555 ----------------------------------
2556 FUNCTION populate_new_record (
2557 p_crjv_rec IN crjv_rec_type,
2558 x_crjv_rec OUT NOCOPY crjv_rec_type
2559 ) RETURN VARCHAR2 IS
2560 l_crjv_rec crjv_rec_type;
2561 l_row_notfound BOOLEAN := TRUE;
2562 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2563 BEGIN
2564 x_crjv_rec := p_crjv_rec;
2565 -- Get current database values
2566 l_crjv_rec := get_rec(p_crjv_rec, l_row_notfound);
2567 IF (l_row_notfound) THEN
2568 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2569 END IF;
2570 IF (x_crjv_rec.id = OKC_API.G_MISS_NUM)
2571 THEN
2572 x_crjv_rec.id := l_crjv_rec.id;
2573 END IF;
2574 IF (x_crjv_rec.object_version_number = OKC_API.G_MISS_NUM)
2575 THEN
2576 x_crjv_rec.object_version_number := l_crjv_rec.object_version_number;
2577 END IF;
2578 IF (x_crjv_rec.cle_id = OKC_API.G_MISS_NUM)
2579 THEN
2580 x_crjv_rec.cle_id := l_crjv_rec.cle_id;
2581 END IF;
2582 IF (x_crjv_rec.chr_id = OKC_API.G_MISS_NUM)
2583 THEN
2584 x_crjv_rec.chr_id := l_crjv_rec.chr_id;
2585 END IF;
2586 IF (x_crjv_rec.rty_code = OKC_API.G_MISS_CHAR)
2587 THEN
2588 x_crjv_rec.rty_code := l_crjv_rec.rty_code;
2589 END IF;
2590 IF (x_crjv_rec.object1_id1 = OKC_API.G_MISS_CHAR)
2591 THEN
2592 x_crjv_rec.object1_id1 := l_crjv_rec.object1_id1;
2593 END IF;
2594 IF (x_crjv_rec.object1_id2 = OKC_API.G_MISS_CHAR)
2595 THEN
2596 x_crjv_rec.object1_id2 := l_crjv_rec.object1_id2;
2597 END IF;
2598 IF (x_crjv_rec.JTOT_OBJECT1_CODE = OKC_API.G_MISS_CHAR)
2599 THEN
2600 x_crjv_rec.JTOT_OBJECT1_CODE := l_crjv_rec.JTOT_OBJECT1_CODE;
2601 END IF;
2602 IF (x_crjv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2603 THEN
2604 x_crjv_rec.attribute_category := l_crjv_rec.attribute_category;
2605 END IF;
2606 IF (x_crjv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2607 THEN
2608 x_crjv_rec.attribute1 := l_crjv_rec.attribute1;
2609 END IF;
2610 IF (x_crjv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2611 THEN
2612 x_crjv_rec.attribute2 := l_crjv_rec.attribute2;
2613 END IF;
2614 IF (x_crjv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2615 THEN
2616 x_crjv_rec.attribute3 := l_crjv_rec.attribute3;
2617 END IF;
2618 IF (x_crjv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2619 THEN
2620 x_crjv_rec.attribute4 := l_crjv_rec.attribute4;
2621 END IF;
2622 IF (x_crjv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2623 THEN
2624 x_crjv_rec.attribute5 := l_crjv_rec.attribute5;
2625 END IF;
2626 IF (x_crjv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2627 THEN
2628 x_crjv_rec.attribute6 := l_crjv_rec.attribute6;
2629 END IF;
2630 IF (x_crjv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2631 THEN
2632 x_crjv_rec.attribute7 := l_crjv_rec.attribute7;
2633 END IF;
2634 IF (x_crjv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2635 THEN
2636 x_crjv_rec.attribute8 := l_crjv_rec.attribute8;
2637 END IF;
2638 IF (x_crjv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2639 THEN
2640 x_crjv_rec.attribute9 := l_crjv_rec.attribute9;
2641 END IF;
2642 IF (x_crjv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2643 THEN
2644 x_crjv_rec.attribute10 := l_crjv_rec.attribute10;
2645 END IF;
2646 IF (x_crjv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2647 THEN
2648 x_crjv_rec.attribute11 := l_crjv_rec.attribute11;
2649 END IF;
2650 IF (x_crjv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2651 THEN
2652 x_crjv_rec.attribute12 := l_crjv_rec.attribute12;
2653 END IF;
2654 IF (x_crjv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2655 THEN
2656 x_crjv_rec.attribute13 := l_crjv_rec.attribute13;
2657 END IF;
2658 IF (x_crjv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2659 THEN
2660 x_crjv_rec.attribute14 := l_crjv_rec.attribute14;
2661 END IF;
2662 IF (x_crjv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2663 THEN
2664 x_crjv_rec.attribute15 := l_crjv_rec.attribute15;
2665 END IF;
2666 IF (x_crjv_rec.created_by = OKC_API.G_MISS_NUM)
2667 THEN
2668 x_crjv_rec.created_by := l_crjv_rec.created_by;
2669 END IF;
2670 IF (x_crjv_rec.creation_date = OKC_API.G_MISS_DATE)
2671 THEN
2672 x_crjv_rec.creation_date := l_crjv_rec.creation_date;
2673 END IF;
2674 IF (x_crjv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2675 THEN
2676 x_crjv_rec.last_updated_by := l_crjv_rec.last_updated_by;
2677 END IF;
2678 IF (x_crjv_rec.last_update_date = OKC_API.G_MISS_DATE)
2679 THEN
2680 x_crjv_rec.last_update_date := l_crjv_rec.last_update_date;
2681 END IF;
2682 IF (x_crjv_rec.last_update_login = OKC_API.G_MISS_NUM)
2683 THEN
2684 x_crjv_rec.last_update_login := l_crjv_rec.last_update_login;
2685 END IF;
2686 RETURN(l_return_status);
2687 END populate_new_record;
2688 -----------------------------------------
2689 -- Set_Attributes for:OKC_K_REL_OBJS_V --
2690 -----------------------------------------
2691 FUNCTION Set_Attributes (
2692 p_crjv_rec IN crjv_rec_type,
2693 x_crjv_rec OUT NOCOPY crjv_rec_type
2694 ) RETURN VARCHAR2 IS
2695 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2696 BEGIN
2697 x_crjv_rec := p_crjv_rec;
2698 x_crjv_rec.OBJECT_VERSION_NUMBER := NVL(x_crjv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2699 RETURN(l_return_status);
2700 END Set_Attributes;
2701 BEGIN
2702 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2703 G_PKG_NAME,
2704 p_init_msg_list,
2705 l_api_version,
2706 p_api_version,
2707 '_PVT',
2708 x_return_status);
2709 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2710 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2711 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2712 RAISE OKC_API.G_EXCEPTION_ERROR;
2713 END IF;
2714 --- Setting item attributes
2715 l_return_status := Set_Attributes(
2716 p_crjv_rec, -- IN
2717 l_crjv_rec); -- OUT
2718 --- If any errors happen abort API
2719 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2720 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2721 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2722 RAISE OKC_API.G_EXCEPTION_ERROR;
2723 END IF;
2724 l_return_status := populate_new_record(l_crjv_rec, l_def_crjv_rec);
2725 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2726 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2727 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2728 RAISE OKC_API.G_EXCEPTION_ERROR;
2729 END IF;
2730 l_def_crjv_rec := fill_who_columns(l_def_crjv_rec);
2731 --- Validate all non-missing attributes (Item Level Validation)
2732 l_return_status := Validate_Attributes(l_def_crjv_rec, l_api_name);
2733 --- If any errors happen abort API
2734 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2735 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2736 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2737 RAISE OKC_API.G_EXCEPTION_ERROR;
2738 END IF;
2739
2740 --------------------------------------
2741 -- Move VIEW record to "Child" records
2742 --------------------------------------
2743 migrate(l_def_crjv_rec, l_crj_rec);
2744 --------------------------------------------
2745 -- Call the UPDATE_ROW for each child record
2746 --------------------------------------------
2747 update_row(
2748 p_init_msg_list,
2749 x_return_status,
2750 x_msg_count,
2751 x_msg_data,
2752 l_crj_rec,
2753 lx_crj_rec
2754 );
2755 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2756 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2757 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2758 RAISE OKC_API.G_EXCEPTION_ERROR;
2759 END IF;
2760 migrate(lx_crj_rec, l_def_crjv_rec);
2761 x_crjv_rec := l_def_crjv_rec;
2762 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2763 EXCEPTION
2764 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2765 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2766 (
2767 l_api_name,
2768 G_PKG_NAME,
2769 'OKC_API.G_RET_STS_ERROR',
2770 x_msg_count,
2771 x_msg_data,
2772 '_PVT'
2773 );
2774 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2775 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2776 (
2777 l_api_name,
2778 G_PKG_NAME,
2779 'OKC_API.G_RET_STS_UNEXP_ERROR',
2780 x_msg_count,
2781 x_msg_data,
2782 '_PVT'
2783 );
2784 WHEN OTHERS THEN
2785 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2786 (
2787 l_api_name,
2788 G_PKG_NAME,
2789 'OTHERS',
2790 x_msg_count,
2791 x_msg_data,
2792 '_PVT'
2793 );
2794 END update_row;
2795 ----------------------------------------
2796 -- PL/SQL TBL update_row for:CRJV_TBL --
2797 ----------------------------------------
2798 PROCEDURE update_row(
2799 p_api_version IN NUMBER,
2800 p_init_msg_list IN VARCHAR2 ,
2801 x_return_status OUT NOCOPY VARCHAR2,
2802 x_msg_count OUT NOCOPY NUMBER,
2803 x_msg_data OUT NOCOPY VARCHAR2,
2804 p_crjv_tbl IN crjv_tbl_type,
2805 x_crjv_tbl OUT NOCOPY crjv_tbl_type) IS
2806
2807 l_api_version CONSTANT NUMBER := 1;
2808 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2809 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2810 i NUMBER := 0;
2811 BEGIN
2812 OKC_API.init_msg_list(p_init_msg_list);
2813 -- Make sure PL/SQL table has records in it before passing
2814 IF (p_crjv_tbl.COUNT > 0) THEN
2815 i := p_crjv_tbl.FIRST;
2816 LOOP
2817 update_row (
2818 p_api_version => p_api_version,
2819 p_init_msg_list => OKC_API.G_FALSE,
2820 x_return_status => x_return_status,
2821 x_msg_count => x_msg_count,
2822 x_msg_data => x_msg_data,
2823 p_crjv_rec => p_crjv_tbl(i),
2824 x_crjv_rec => x_crjv_tbl(i));
2825 EXIT WHEN (i = p_crjv_tbl.LAST);
2826 i := p_crjv_tbl.NEXT(i);
2827 END LOOP;
2828 END IF;
2829 EXCEPTION
2830 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2831 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2832 (
2833 l_api_name,
2834 G_PKG_NAME,
2835 'OKC_API.G_RET_STS_ERROR',
2836 x_msg_count,
2837 x_msg_data,
2838 '_PVT'
2839 );
2840 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2841 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2842 (
2843 l_api_name,
2844 G_PKG_NAME,
2845 'OKC_API.G_RET_STS_UNEXP_ERROR',
2846 x_msg_count,
2847 x_msg_data,
2848 '_PVT'
2849 );
2850 WHEN OTHERS THEN
2851 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2852 (
2853 l_api_name,
2854 G_PKG_NAME,
2855 'OTHERS',
2856 x_msg_count,
2857 x_msg_data,
2858 '_PVT'
2859 );
2860 END update_row;
2861
2862 ---------------------------------------------------------------------------
2863 -- PROCEDURE delete_row
2864 ---------------------------------------------------------------------------
2865 -----------------------------------
2866 -- delete_row for:OKC_K_REL_OBJS --
2867 -----------------------------------
2868 PROCEDURE delete_row(
2869 p_init_msg_list IN VARCHAR2 ,
2870 x_return_status OUT NOCOPY VARCHAR2,
2871 x_msg_count OUT NOCOPY NUMBER,
2872 x_msg_data OUT NOCOPY VARCHAR2,
2873 p_crj_rec IN crj_rec_type) IS
2874
2875 l_api_version CONSTANT NUMBER := 1;
2876 l_api_name CONSTANT VARCHAR2(30) := 'OBJS_delete_row';
2877 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2878 l_crj_rec crj_rec_type:= p_crj_rec;
2879 l_row_notfound BOOLEAN := TRUE;
2880 BEGIN
2881 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2882 p_init_msg_list,
2883 '_PVT',
2884 x_return_status);
2885 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2886 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2887 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2888 RAISE OKC_API.G_EXCEPTION_ERROR;
2889 END IF;
2890 DELETE FROM OKC_K_REL_OBJS
2891 WHERE ID = l_crj_rec.id;
2892
2893 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2894 EXCEPTION
2895 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2896 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2897 (
2898 l_api_name,
2899 G_PKG_NAME,
2900 'OKC_API.G_RET_STS_ERROR',
2901 x_msg_count,
2902 x_msg_data,
2903 '_PVT'
2904 );
2905 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2906 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2907 (
2908 l_api_name,
2909 G_PKG_NAME,
2910 'OKC_API.G_RET_STS_UNEXP_ERROR',
2911 x_msg_count,
2912 x_msg_data,
2913 '_PVT'
2914 );
2915 WHEN OTHERS THEN
2916 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2917 (
2918 l_api_name,
2919 G_PKG_NAME,
2920 'OTHERS',
2921 x_msg_count,
2922 x_msg_data,
2923 '_PVT'
2924 );
2925 END delete_row;
2926 -------------------------------------
2927 -- delete_row for:OKC_K_REL_OBJS_V --
2928 -------------------------------------
2929 PROCEDURE delete_row(
2930 p_api_version IN NUMBER,
2931 p_init_msg_list IN VARCHAR2 ,
2932 x_return_status OUT NOCOPY VARCHAR2,
2933 x_msg_count OUT NOCOPY NUMBER,
2934 x_msg_data OUT NOCOPY VARCHAR2,
2935 p_crjv_rec IN crjv_rec_type) IS
2936
2937 l_api_version CONSTANT NUMBER := 1;
2938 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2939 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2940 l_crjv_rec crjv_rec_type := p_crjv_rec;
2941 l_crj_rec crj_rec_type;
2942 BEGIN
2943 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2944 G_PKG_NAME,
2945 p_init_msg_list,
2946 l_api_version,
2947 p_api_version,
2948 '_PVT',
2949 x_return_status);
2950 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2951 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2952 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2953 RAISE OKC_API.G_EXCEPTION_ERROR;
2954 END IF;
2955 --------------------------------------
2956 -- Move VIEW record to "Child" records
2957 --------------------------------------
2958 migrate(l_crjv_rec, l_crj_rec);
2959 --------------------------------------------
2960 -- Call the DELETE_ROW for each child record
2961 --------------------------------------------
2962 delete_row(
2963 p_init_msg_list,
2964 x_return_status,
2965 x_msg_count,
2966 x_msg_data,
2967 l_crj_rec
2968 );
2969 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2970 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2971 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2972 RAISE OKC_API.G_EXCEPTION_ERROR;
2973 END IF;
2974 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2975 EXCEPTION
2976 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2977 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2978 (
2979 l_api_name,
2980 G_PKG_NAME,
2981 'OKC_API.G_RET_STS_ERROR',
2982 x_msg_count,
2983 x_msg_data,
2984 '_PVT'
2985 );
2986 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2987 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2988 (
2989 l_api_name,
2990 G_PKG_NAME,
2991 'OKC_API.G_RET_STS_UNEXP_ERROR',
2992 x_msg_count,
2993 x_msg_data,
2994 '_PVT'
2995 );
2996 WHEN OTHERS THEN
2997 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2998 (
2999 l_api_name,
3000 G_PKG_NAME,
3001 'OTHERS',
3002 x_msg_count,
3003 x_msg_data,
3004 '_PVT'
3005 );
3006 END delete_row;
3007 ----------------------------------------
3008 -- PL/SQL TBL delete_row for:CRJV_TBL --
3009 ----------------------------------------
3010 PROCEDURE delete_row(
3011 p_api_version IN NUMBER,
3012 p_init_msg_list IN VARCHAR2 ,
3013 x_return_status OUT NOCOPY VARCHAR2,
3014 x_msg_count OUT NOCOPY NUMBER,
3015 x_msg_data OUT NOCOPY VARCHAR2,
3016 p_crjv_tbl IN crjv_tbl_type) IS
3017
3018 l_api_version CONSTANT NUMBER := 1;
3019 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3020 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3021 i NUMBER := 0;
3022 BEGIN
3023 OKC_API.init_msg_list(p_init_msg_list);
3024 -- Make sure PL/SQL table has records in it before passing
3025 IF (p_crjv_tbl.COUNT > 0) THEN
3026 i := p_crjv_tbl.FIRST;
3027 LOOP
3028 delete_row (
3029 p_api_version => p_api_version,
3030 p_init_msg_list => OKC_API.G_FALSE,
3031 x_return_status => x_return_status,
3032 x_msg_count => x_msg_count,
3033 x_msg_data => x_msg_data,
3034 p_crjv_rec => p_crjv_tbl(i));
3035 EXIT WHEN (i = p_crjv_tbl.LAST);
3036 i := p_crjv_tbl.NEXT(i);
3037 END LOOP;
3038 END IF;
3039 EXCEPTION
3040 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3041 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3042 (
3043 l_api_name,
3044 G_PKG_NAME,
3045 'OKC_API.G_RET_STS_ERROR',
3046 x_msg_count,
3047 x_msg_data,
3048 '_PVT'
3049 );
3050 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3051 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3052 (
3053 l_api_name,
3054 G_PKG_NAME,
3055 'OKC_API.G_RET_STS_UNEXP_ERROR',
3056 x_msg_count,
3057 x_msg_data,
3058 '_PVT'
3059 );
3060 WHEN OTHERS THEN
3061 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3062 (
3063 l_api_name,
3064 G_PKG_NAME,
3065 'OTHERS',
3066 x_msg_count,
3067 x_msg_data,
3068 '_PVT'
3069 );
3070 END delete_row;
3071
3072 ---------------------------------------------------------------------------
3073 -- Start of comments
3074 --
3075 -- Procedure Name : quote_is_renewal
3076 -- Description :
3077 -- Business Rules :
3078 -- Parameters :
3079 -- Version : 1.0
3080 -- End of comments
3081 PROCEDURE quote_is_renewal
3082 (
3083 p_api_version IN NUMBER
3084 ,p_init_msg_list IN VARCHAR2
3085 ,x_return_status OUT NOCOPY VARCHAR2
3086 ,x_msg_count OUT NOCOPY NUMBER
3087 ,x_msg_data OUT NOCOPY VARCHAR2
3088 ,p_crjv_rec IN crjv_rec_type
3089 ,x_true_false out nocopy boolean
3090 ) is
3091
3092 l_api_version CONSTANT NUMBER := 1;
3093 l_api_name CONSTANT VARCHAR2(30) := 'quote_is_renewal';
3094 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3095 l_row_notfound boolean;
3096 l_crjv_rec crjv_rec_type;
3097 i number;
3098
3099 /* CURSOR renew_rel_for_quote
3100 (
3101 p_crjv_rec crjv_rec_type
3102 ) IS
3103 SELECT
3104 id
3105 FROM
3106 okc_k_rel_objs o
3107 WHERE (
3108 o.rty_code = 'QUOTERENEWSCONTRACT'
3109 and o.jtot_object1_code = 'OKX_QUOTEHEAD'
3110 and o.object1_id1 = p_crjv_rec.object1_id1
3111 and (
3112 o.object1_id2 = p_crjv_rec.object1_id2
3113 or (
3114 p_crjv_rec.object1_id2 is null
3115 and o.object1_id2 is null
3116 )
3117 )
3118 )
3119 ;
3120 quotes are versioned with the same number and the rel probably not updated so use code below not above
3121 */
3122 CURSOR renew_rel_for_quote
3123 (
3124 p_crjv_rec crjv_rec_type
3125 ) IS
3126 SELECT
3127 id
3128 FROM
3129 okc_k_rel_objs o
3130 WHERE (-- object with right type and relationship codes
3131 o.rty_code = 'QUOTERENEWSCONTRACT'
3132 and o.jtot_object1_code = 'OKX_QUOTEHEAD'
3133 and exists (-- another quote (or same) with same number as object
3134 select
3135 1
3136 from
3137 okx_quote_headers_v q1
3138 ,okx_quote_headers_v q2
3139 where (
3140 (-- q1 is passed in
3141 q1.id1 = p_crjv_rec.object1_id1
3142 and (
3143 q1.id2 = p_crjv_rec.object1_id2
3144 or (
3145 p_crjv_rec.object1_id2 is null
3146 and q1.id2 = '#'
3147 )
3148 )
3149 )
3150 and (-- q2 has same num as q1
3151 q1.quote_number = q2.quote_number
3152 )
3153 and (-- q2 is the obj rel we're looking for
3154 q2.id1 = o.object1_id1
3155 and (
3156 q2.id2 = o.object1_id2
3157 or (
3158 o.object1_id2 is null
3159 and q2.id2 = '#'
3160 )
3161 )
3162 )
3163 )
3164 )
3165 )
3166 ;
3167 r_renew_rel_for_quote renew_rel_for_quote%rowtype;
3168
3169 BEGIN
3170 OKC_API.init_msg_list(p_init_msg_list);
3171 l_return_status := OKC_API.START_ACTIVITY
3172 (
3173 substr(l_api_name,1,26)
3174 ,p_init_msg_list
3175 ,'_PUB'
3176 ,x_return_status
3177 );
3178 IF (-- unexpected error
3179 l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
3180 ) THEN
3181 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3182 ELSIF (-- standard error
3183 l_return_status = OKC_API.G_RET_STS_ERROR
3184 ) THEN
3185 RAISE OKC_API.G_EXCEPTION_ERROR;
3186 END IF;
3187
3188 l_crjv_rec := null_out_defaults (p_crjv_rec);
3189
3190 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3191 x_true_false := false;
3192
3193 /* is quote for renewal?
3194 */
3195 IF (-- quote to search for
3196 l_crjv_rec.object1_id1 IS NOT NULL
3197 ) THEN
3198 OPEN renew_rel_for_quote
3199 (
3200 l_crjv_rec
3201 );
3202 FETCH renew_rel_for_quote
3203 INTO r_renew_rel_for_quote;
3204 l_row_notfound := renew_rel_for_quote%NOTFOUND;
3205 CLOSE renew_rel_for_quote;
3206 x_true_false := not(l_row_notfound);
3207 END IF;
3208 EXCEPTION
3209 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3210 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3211 (
3212 l_api_name
3213 ,G_PKG_NAME
3214 ,'OKC_API.G_RET_STS_ERROR'
3215 ,x_msg_count
3216 ,x_msg_data
3217 ,'_PUB'
3218 );
3219 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3220 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3221 (
3222 l_api_name
3223 ,G_PKG_NAME
3224 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3225 ,x_msg_count
3226 ,x_msg_data
3227 ,'_PUB'
3228 );
3229 WHEN OTHERS THEN
3230 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3231 (
3232 l_api_name
3233 ,G_PKG_NAME
3234 ,'OTHERS'
3235 ,x_msg_count
3236 ,x_msg_data
3237 ,'_PUB'
3238 );
3239 OKC_API.set_message
3240 (
3241 G_APP_NAME
3242 ,g_unexpected_error
3243 ,g_sqlcode_token
3244 ,sqlcode
3245 ,g_sqlerrm_token
3246 ,sqlerrm
3247 ,'@'
3248 ,l_api_name
3249 );
3250 end quote_is_renewal;
3251
3252 ---------------------------------------------------------------------------
3253 -- Start of comments
3254 --
3255 -- Procedure Name : order_is_renewal
3256 -- Description :
3257 -- Business Rules :
3258 -- Parameters :
3259 -- Version : 1.0
3260 -- End of comments
3261 PROCEDURE order_is_renewal
3262 (
3263 p_api_version IN NUMBER
3264 ,p_init_msg_list IN VARCHAR2
3265 ,x_return_status OUT NOCOPY VARCHAR2
3266 ,x_msg_count OUT NOCOPY NUMBER
3267 ,x_msg_data OUT NOCOPY VARCHAR2
3268 ,p_crjv_rec IN crjv_rec_type
3269 ,x_true_false out nocopy boolean
3270 ) is
3271
3272 l_api_version CONSTANT NUMBER := 1;
3273 l_api_name CONSTANT VARCHAR2(30) := 'order_is_renewal';
3274 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3275 l_row_notfound boolean;
3276 l_crjv_rec crjv_rec_type;
3277 i number;
3278
3279 CURSOR renew_rel_for_order
3280 (
3281 p_crjv_rec crjv_rec_type
3282 ) IS
3283 SELECT
3284 id
3285 FROM
3286 okc_k_rel_objs o
3287 WHERE (
3288 o.rty_code = 'ORDERRENEWSCONTRACT'
3289 and o.jtot_object1_code = 'OKX_ORDERHEAD'
3290 and o.object1_id1 = p_crjv_rec.object1_id1
3291 and (
3292 o.object1_id2 = p_crjv_rec.object1_id2
3293 or (
3294 p_crjv_rec.object1_id2 is null
3295 and o.object1_id2 is null
3296 )
3297 )
3298 )
3299 ;
3300 r_renew_rel_for_order renew_rel_for_order%rowtype;
3301
3302 CURSOR quote_for_order
3303 (
3304 p_crjv_rec crjv_rec_type
3305 ) IS
3306 SELECT
3307 id1
3308 ,id2
3309 FROM
3310 okx_quote_headers_v q
3311 WHERE
3312 (
3313 q.order_id = p_crjv_rec.object1_id1
3314 /* q.order_id1 = p_crjv_rec.object1_id1
3315 and (
3316 q.order_id2 = p_crjv_rec.object1_id2
3317 or (
3318 p_crjv_rec.object1_id2 is null
3319 and q.order_id2 = '#'
3320 )
3321 )
3322 */ )
3323 ;
3324 r_quote_for_order quote_for_order%rowtype;
3325
3326 BEGIN
3327 l_return_status := OKC_API.START_ACTIVITY
3328 (
3329 substr(l_api_name,1,26)
3330 ,p_init_msg_list
3331 ,'_PUB'
3332 ,x_return_status
3333 );
3334 IF (-- unexpected error
3335 l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
3336 ) THEN
3337 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3338 ELSIF (-- standard error
3339 l_return_status = OKC_API.G_RET_STS_ERROR
3340 ) THEN
3341 RAISE OKC_API.G_EXCEPTION_ERROR;
3342 END IF;
3343
3344 l_crjv_rec := null_out_defaults (p_crjv_rec);
3345
3346 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3347 x_true_false := false;
3348
3349 /* is order for renewal?
3350 */
3351 OPEN renew_rel_for_order
3352 (
3353 l_crjv_rec
3354 );
3355 FETCH renew_rel_for_order
3356 INTO r_renew_rel_for_order;
3357 l_row_notfound := renew_rel_for_order%NOTFOUND;
3358 CLOSE renew_rel_for_order;
3359 x_true_false := not(l_row_notfound);
3360
3361 if (-- didn't find rel. for order
3362 l_row_notfound
3363 ) then -- look for rel. on quote related to order
3364 -- might not have managed to implement order rels. in time for 11i release
3365 -- get quote for order
3366 OPEN quote_for_order
3367 (
3368 l_crjv_rec
3369 );
3370 FETCH quote_for_order
3371 INTO r_quote_for_order;
3372 l_row_notfound := quote_for_order%NOTFOUND;
3373 CLOSE quote_for_order;
3374
3375 l_crjv_rec.object1_id1 := r_quote_for_order.id1;
3376 l_crjv_rec.object1_id2 := r_quote_for_order.id2;
3377
3378 IF (-- quote found
3379 not(l_row_notfound)
3380 ) THEN
3381 -- is quote for renewal?
3382 OKC_CRJ_PVT.quote_is_renewal
3383 (
3384 p_api_version => p_api_version
3385 ,p_init_msg_list => p_init_msg_list
3386 ,x_return_status => x_return_status
3387 ,x_msg_count => x_msg_count
3388 ,x_msg_data => x_msg_data
3389 ,p_crjv_rec => l_crjv_rec
3390 ,x_true_false => x_true_false
3391 );
3392 END IF; -- quote found
3393 end if; -- didn't find rel. for order
3394
3395 EXCEPTION
3396 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3397 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3398 (
3399 l_api_name
3400 ,G_PKG_NAME
3401 ,'OKC_API.G_RET_STS_ERROR'
3402 ,x_msg_count
3403 ,x_msg_data
3404 ,'_PUB'
3405 );
3406 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3407 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3408 (
3409 l_api_name
3410 ,G_PKG_NAME
3411 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3412 ,x_msg_count
3413 ,x_msg_data
3414 ,'_PUB'
3415 );
3416 WHEN OTHERS THEN
3417 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3418 (
3419 l_api_name
3420 ,G_PKG_NAME
3421 ,'OTHERS'
3422 ,x_msg_count
3423 ,x_msg_data
3424 ,'_PUB'
3425 );
3426 OKC_API.set_message
3427 (
3428 G_APP_NAME
3429 ,g_unexpected_error
3430 ,g_sqlcode_token
3431 ,sqlcode
3432 ,g_sqlerrm_token
3433 ,sqlerrm
3434 ,'@'
3435 ,l_api_name
3436 );
3437 end order_is_renewal;
3438
3439 ---------------------------------------------------------------------------
3440 -- Start of comments
3441 --
3442 -- Procedure Name : quote_is_subject
3443 -- Description :
3444 -- Business Rules :
3445 -- Parameters :
3446 -- Version : 1.0
3447 -- End of comments
3448 PROCEDURE quote_is_subject
3449 (
3450 p_api_version IN NUMBER
3451 ,p_init_msg_list IN VARCHAR2
3452 ,x_return_status OUT NOCOPY VARCHAR2
3453 ,x_msg_count OUT NOCOPY NUMBER
3454 ,x_msg_data OUT NOCOPY VARCHAR2
3455 ,p_crjv_rec IN crjv_rec_type
3456 ,x_true_false out nocopy boolean
3457 ) is
3458
3459 l_api_version CONSTANT NUMBER := 1;
3460 l_api_name CONSTANT VARCHAR2(30) := 'quote_is_subject';
3461 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3462 l_row_notfound boolean;
3463 l_crjv_rec crjv_rec_type;
3464 i number;
3465
3466 CURSOR subject_rel_for_quote
3467 (
3468 p_crjv_rec crjv_rec_type
3469 ) IS
3470 SELECT
3471 id
3472 FROM
3473 okc_k_rel_objs o
3474 WHERE (-- object with right type and relationship codes
3475 o.rty_code = 'QUOTESUBJECTCONTRACT'
3476 and o.jtot_object1_code = 'OKX_QUOTEHEAD'
3477 and exists (-- another quote (or same) with same number as object
3478 select
3479 1
3480 from
3481 okx_quote_headers_v q1
3482 ,okx_quote_headers_v q2
3483 where (
3484 (-- q1 is passed in
3485 q1.id1 = p_crjv_rec.object1_id1
3486 and (
3487 q1.id2 = p_crjv_rec.object1_id2
3488 or (
3489 p_crjv_rec.object1_id2 is null
3490 and q1.id2 = '#'
3491 )
3492 )
3493 )
3494 and (-- q2 has same num as q1
3495 q1.quote_number = q2.quote_number
3496 )
3497 and (-- q2 is the obj rel we're looking for
3498 q2.id1 = o.object1_id1
3499 and (
3500 q2.id2 = o.object1_id2
3501 or (
3502 o.object1_id2 is null
3503 and q2.id2 = '#'
3504 )
3505 )
3506 )
3507 )
3508 )
3509 )
3510 ;
3511 r_subject_rel_for_quote subject_rel_for_quote%rowtype;
3512
3513 BEGIN
3514 l_return_status := OKC_API.START_ACTIVITY
3515 (
3516 substr(l_api_name,1,26)
3517 ,p_init_msg_list
3518 ,'_PUB'
3519 ,x_return_status
3520 );
3521 IF (-- unexpected error
3522 l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
3523 ) THEN
3524 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3525 ELSIF (-- standard error
3526 l_return_status = OKC_API.G_RET_STS_ERROR
3527 ) THEN
3528 RAISE OKC_API.G_EXCEPTION_ERROR;
3529 END IF;
3530
3531 l_crjv_rec := null_out_defaults (p_crjv_rec);
3532
3533 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3534 x_true_false := false;
3535
3536 /* is quote subject?
3537 */
3538 IF (-- quote to search for
3539 l_crjv_rec.object1_id1 IS NOT NULL
3540 ) THEN
3541 OPEN subject_rel_for_quote
3542 (
3543 l_crjv_rec
3544 );
3545 FETCH subject_rel_for_quote
3546 INTO r_subject_rel_for_quote;
3547 l_row_notfound := subject_rel_for_quote%NOTFOUND;
3548 CLOSE subject_rel_for_quote;
3549 x_true_false := not(l_row_notfound);
3550 END IF;
3551 EXCEPTION
3552 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3553 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3554 (
3555 l_api_name
3556 ,G_PKG_NAME
3557 ,'OKC_API.G_RET_STS_ERROR'
3558 ,x_msg_count
3559 ,x_msg_data
3560 ,'_PUB'
3561 );
3562 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3563 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3564 (
3565 l_api_name
3566 ,G_PKG_NAME
3567 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3568 ,x_msg_count
3569 ,x_msg_data
3570 ,'_PUB'
3571 );
3572 WHEN OTHERS THEN
3573 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3574 (
3575 l_api_name
3576 ,G_PKG_NAME
3577 ,'OTHERS'
3578 ,x_msg_count
3579 ,x_msg_data
3580 ,'_PUB'
3581 );
3582 OKC_API.set_message
3583 (
3584 G_APP_NAME
3585 ,g_unexpected_error
3586 ,g_sqlcode_token
3587 ,sqlcode
3588 ,g_sqlerrm_token
3589 ,sqlerrm
3590 ,'@'
3591 ,l_api_name
3592 );
3593 end quote_is_subject;
3594
3595 ---------------------------------------------------------------------------
3596 -- Start of comments
3597 --
3598 -- Procedure Name : order_is_subject
3599 -- Description :
3600 -- Business Rules :
3601 -- Parameters :
3602 -- Version : 1.0
3603 -- End of comments
3604 PROCEDURE order_is_subject
3605 (
3606 p_api_version IN NUMBER
3607 ,p_init_msg_list IN VARCHAR2
3608 ,x_return_status OUT NOCOPY VARCHAR2
3609 ,x_msg_count OUT NOCOPY NUMBER
3610 ,x_msg_data OUT NOCOPY VARCHAR2
3611 ,p_crjv_rec IN crjv_rec_type
3612 ,x_true_false out nocopy boolean
3613 ) is
3614
3615 l_api_version CONSTANT NUMBER := 1;
3616 l_api_name CONSTANT VARCHAR2(30) := 'order_is_subject';
3617 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3618 l_row_notfound boolean;
3619 l_crjv_rec crjv_rec_type;
3620 i number;
3621
3622 CURSOR subject_rel_for_order
3623 (
3624 p_crjv_rec crjv_rec_type
3625 ) IS
3626 SELECT
3627 id
3628 FROM
3629 okc_k_rel_objs o
3630 WHERE (
3631 o.rty_code = 'ORDERSUBJECTCONTRACT'
3632 and o.jtot_object1_code = 'OKX_ORDERHEAD'
3633 and o.object1_id1 = p_crjv_rec.object1_id1
3634 and (
3635 o.object1_id2 = p_crjv_rec.object1_id2
3636 or (
3637 p_crjv_rec.object1_id2 is null
3638 and o.object1_id2 is null
3639 )
3640 )
3641 )
3642 ;
3643 r_subject_rel_for_order subject_rel_for_order%rowtype;
3644
3645 CURSOR quote_for_order
3646 (
3647 p_crjv_rec crjv_rec_type
3648 ) IS
3649 SELECT
3650 id1
3651 ,id2
3652 FROM
3653 okx_quote_headers_v q
3654 WHERE
3655 (
3656 q.order_id = p_crjv_rec.object1_id1
3657 /* q.order_id1 = p_crjv_rec.object1_id1
3658 and (
3659 q.order_id2 = p_crjv_rec.object1_id2
3660 or (
3661 p_crjv_rec.object1_id2 is null
3662 and q.order_id2 = '#'
3663 )
3664 )
3665 */ )
3666 ;
3667 r_quote_for_order quote_for_order%rowtype;
3668
3669 BEGIN
3670 l_return_status := OKC_API.START_ACTIVITY
3671 (
3672 substr(l_api_name,1,26)
3673 ,p_init_msg_list
3674 ,'_PUB'
3675 ,x_return_status
3676 );
3677 IF (-- unexpected error
3678 l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
3679 ) THEN
3680 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3681 ELSIF (-- standard error
3682 l_return_status = OKC_API.G_RET_STS_ERROR
3683 ) THEN
3684 RAISE OKC_API.G_EXCEPTION_ERROR;
3685 END IF;
3686
3687 l_crjv_rec := null_out_defaults (p_crjv_rec);
3688
3689 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3690 x_true_false := false;
3691
3692 /* is order subject?
3693 */
3694 OPEN subject_rel_for_order
3695 (
3696 l_crjv_rec
3697 );
3698 FETCH subject_rel_for_order
3699 INTO r_subject_rel_for_order;
3700 l_row_notfound := subject_rel_for_order%NOTFOUND;
3701 CLOSE subject_rel_for_order;
3702 x_true_false := not(l_row_notfound);
3703
3704 if (-- didn't find rel. for order
3705 l_row_notfound
3706 ) then -- look for rel. on quote related to order
3707 -- might not have managed to implement order rels. in time for 11i release
3708 -- get quote for order
3709 OPEN quote_for_order
3710 (
3711 l_crjv_rec
3712 );
3713 FETCH quote_for_order
3714 INTO r_quote_for_order;
3715 l_row_notfound := quote_for_order%NOTFOUND;
3716 CLOSE quote_for_order;
3717
3718 l_crjv_rec.object1_id1 := r_quote_for_order.id1;
3719 l_crjv_rec.object1_id2 := r_quote_for_order.id2;
3720
3721 IF (-- quote found
3722 not(l_row_notfound)
3723 ) THEN
3724 -- is quote subject?
3725 OKC_CRJ_PVT.quote_is_subject
3726 (
3727 p_api_version => p_api_version
3728 ,p_init_msg_list => p_init_msg_list
3729 ,x_return_status => x_return_status
3730 ,x_msg_count => x_msg_count
3731 ,x_msg_data => x_msg_data
3732 ,p_crjv_rec => l_crjv_rec
3733 ,x_true_false => x_true_false
3734 );
3735 END IF; -- quote found
3736 end if; -- didn't find rel. for order
3737
3738 EXCEPTION
3739 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3740 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3741 (
3742 l_api_name
3743 ,G_PKG_NAME
3744 ,'OKC_API.G_RET_STS_ERROR'
3745 ,x_msg_count
3746 ,x_msg_data
3747 ,'_PUB'
3748 );
3749 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3750 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3751 (
3752 l_api_name
3753 ,G_PKG_NAME
3754 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3755 ,x_msg_count
3756 ,x_msg_data
3757 ,'_PUB'
3758 );
3759 WHEN OTHERS THEN
3760 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3761 (
3762 l_api_name
3763 ,G_PKG_NAME
3764 ,'OTHERS'
3765 ,x_msg_count
3766 ,x_msg_data
3767 ,'_PUB'
3768 );
3769 OKC_API.set_message
3770 (
3771 G_APP_NAME
3772 ,g_unexpected_error
3773 ,g_sqlcode_token
3774 ,sqlcode
3775 ,g_sqlerrm_token
3776 ,sqlerrm
3777 ,'@'
3778 ,l_api_name
3779 );
3780 end order_is_subject;
3781
3782 ---------------------------------------------------------------------------
3783 -- Start of comments
3784 --
3785 -- Procedure Name : quote_contract_is_ordered
3786 -- Description :
3787 -- Business Rules :
3788 -- Parameters :
3789 -- Version : 1.0
3790 -- End of comments
3791 PROCEDURE quote_contract_is_ordered
3792 (
3793 p_api_version IN NUMBER
3794 ,p_init_msg_list IN VARCHAR2
3795 ,x_return_status OUT NOCOPY VARCHAR2
3796 ,x_msg_count OUT NOCOPY NUMBER
3797 ,x_msg_data OUT NOCOPY VARCHAR2
3798 ,p_crjv_rec IN crjv_rec_type
3799 ,x_true_false out nocopy boolean
3800 ) IS
3801 l_api_name varchar2(30) := 'quote_contract_is_ordered';
3802 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
3803 l_row_notfound BOOLEAN := TRUE;
3804 l_crjv_rec crjv_rec_type;
3805
3806 CURSOR renew_rel_for_quote -- finds contract header if quote is for contract renewal
3807 (
3808 p_crjv_rec crjv_rec_type
3809 ) IS
3810 SELECT
3811 id
3812 ,chr_id
3813 FROM
3814 okc_k_rel_objs o
3815 WHERE (-- object with right type and relationship codes
3816 o.rty_code = 'QUOTERENEWSCONTRACT'
3817 and o.jtot_object1_code = 'OKX_QUOTEHEAD'
3818 and exists (-- another quote (or same) with same number as object
3819 select
3820 1
3821 from
3822 okx_quote_headers_v q1
3823 ,okx_quote_headers_v q2
3824 where (
3825 (-- q1 is passed in
3826 q1.id1 = p_crjv_rec.object1_id1
3827 and (
3828 q1.id2 = p_crjv_rec.object1_id2
3829 or (
3830 p_crjv_rec.object1_id2 is null
3831 and q1.id2 = '#'
3832 )
3833 )
3834 )
3835 and (-- q2 has same num as q1
3836 q1.quote_number = q2.quote_number
3837 )
3838 and (-- q2 is the obj rel we're looking for
3839 q2.id1 = o.object1_id1
3840 and (
3841 q2.id2 = o.object1_id2
3842 or (
3843 o.object1_id2 is null
3844 and q2.id2 = '#'
3845 )
3846 )
3847 )
3848 )
3849 )
3850 )
3851 ;
3852 r_renew_rel_for_quote renew_rel_for_quote%rowtype;
3853
3854 CURSOR quote_contract_order -- finds order for contract
3855 (
3856 p_crjv_rec crjv_rec_type
3857 ,r_renew_rel_for_quote renew_rel_for_quote%rowtype
3858 ) IS
3859 SELECT
3860 object1_id1
3861 ,object1_id2
3862 FROM
3863 okc_k_rel_objs o
3864 WHERE
3865 (
3866 o.chr_id = r_renew_rel_for_quote.chr_id
3867 and o.rty_code = 'CONTRACTSERVICESORDER'
3868 )
3869 ;
3870 r_quote_contract_order quote_contract_order%rowtype;
3871 BEGIN
3872 l_crjv_rec := null_out_defaults (p_crjv_rec);
3873
3874 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3875 x_true_false := false;
3876
3877 IF (-- quote to search for
3878 l_crjv_rec.object1_id1 IS NOT NULL
3879 ) THEN
3880 -- get contract id which quote renews
3881 OPEN renew_rel_for_quote
3882 (
3883 l_crjv_rec
3884 );
3885 FETCH renew_rel_for_quote
3886 INTO r_renew_rel_for_quote;
3887 l_row_notfound := renew_rel_for_quote%NOTFOUND;
3888 CLOSE renew_rel_for_quote;
3889 x_true_false := not(l_row_notfound);
3890 END IF;
3891
3892 IF (-- quote is renewal
3893 not(l_row_notfound)
3894 ) THEN
3895 -- get order for renewed contract
3896 OPEN quote_contract_order
3897 (
3898 l_crjv_rec
3899 ,r_renew_rel_for_quote
3900 );
3901 FETCH quote_contract_order
3902 INTO r_quote_contract_order;
3903 l_row_notfound := quote_contract_order%NOTFOUND;
3904 CLOSE quote_contract_order;
3905 x_true_false := not(l_row_notfound);
3906 END IF;
3907 EXCEPTION
3908 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3909 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3910 (
3911 l_api_name
3912 ,G_PKG_NAME
3913 ,'OKC_API.G_RET_STS_ERROR'
3914 ,x_msg_count
3915 ,x_msg_data
3916 ,'_PUB'
3917 );
3918 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3919 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3920 (
3921 l_api_name
3922 ,G_PKG_NAME
3923 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3924 ,x_msg_count
3925 ,x_msg_data
3926 ,'_PUB'
3927 );
3928 WHEN OTHERS THEN
3929 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3930 (
3931 l_api_name
3932 ,G_PKG_NAME
3933 ,'OTHERS'
3934 ,x_msg_count
3935 ,x_msg_data
3936 ,'_PUB'
3937 );
3938 OKC_API.set_message
3939 (
3940 G_APP_NAME
3941 ,g_unexpected_error
3942 ,g_sqlcode_token
3943 ,sqlcode
3944 ,g_sqlerrm_token
3945 ,sqlerrm
3946 ,'@'
3947 ,l_api_name
3948 );
3949 END quote_contract_is_ordered;
3950
3951 ---------------------------------------------------------------------------
3952 -- Start of comments
3953 --
3954 -- Procedure Name : GET_OBJ_FROM_JTFV
3955 -- Description :
3956 -- Business Rules :
3957 -- Parameters :
3958 -- Version : 1.0
3959 -- End of comments
3960 FUNCTION GET_OBJ_FROM_JTFV
3961 (
3962 p_object_code IN VARCHAR2
3963 ,p_id1 IN NUMBER
3964 ,p_id2 IN VARCHAR2
3965 ) RETURN boolean IS
3966
3967 l_name VARCHAR2(255);
3968 l_id2 varchar2(50);
3969 l_num number;
3970 l_from_table VARCHAR2(200);
3971 l_where_clause VARCHAR2(2000);
3972 l_sql_stmt VARCHAR2(500);
3973 l_not_found BOOLEAN := true;
3974 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJ_FROM_JTFV';
3975
3976 Cursor jtfv_csr IS
3977 SELECT
3978 FROM_TABLE
3979 ,WHERE_CLAUSE
3980 FROM
3981 JTF_OBJECTS_B
3982 WHERE
3983 OBJECT_CODE = p_object_code
3984 ;
3985 Type SOURCE_CSR IS REF CURSOR;
3986 c SOURCE_CSR;
3987
3988 BEGIN
3989 open jtfv_csr;
3990 fetch jtfv_csr into
3991 l_from_table
3992 ,l_where_clause;
3993 l_not_found := jtfv_csr%NOTFOUND;
3994 close jtfv_csr;
3995
3996 If (
3997 l_not_found
3998 ) Then
3999 OKC_API.set_message
4000 (
4001 G_APP_NAME
4002 ,G_UNEXPECTED_ERROR
4003 ,'not found'
4004 ,'no object=' || p_object_code
4005 ,g_sqlerrm_token
4006 ,sqlerrm
4007 ,'@'
4008 ,l_api_name
4009 );
4010 return l_not_found;
4011 End if;
4012
4013 l_sql_stmt := 'SELECT '
4014 || ' 1 '
4015 || ' FROM '
4016 || l_from_table
4017 || ' WHERE '
4018 || ' ( '
4019 || 'ID1 = :id1'
4020 || ' AND '
4021 -- || ' ( '
4022 || ' ID2 = :id2'
4023 /* || ' or '
4024 || ' ( '
4025 || ' ID2 = ''#'' '
4026 || ' AND '
4027 || ' :id2 is null '
4028 || ' )'
4029 || ' )'
4030 */ || ' )';
4031 If (
4032 l_where_clause is not null
4033 ) Then
4034 l_sql_stmt := l_sql_stmt
4035 || ' AND '
4036 || l_where_clause;
4037 End If;
4038
4039 l_not_found := true;
4040
4041 l_id2 := p_id2;
4042 if (
4043 p_id2 is null
4044 ) then
4045 l_id2 := '#';
4046 end if;
4047
4048 IF (l_debug = 'Y') THEN
4049 okc_util.print_trace(6, 'l_sql_stmt:' || l_sql_stmt);
4050 okc_util.print_trace(6, 'p_id1: ' || p_id1 || ', l_id2: ' || l_id2);
4051 okc_util.print_trace(6, 'Operating Unit = '|| sys_context('OKC_CONTEXT','ORG_ID'));
4052 END IF;
4053 open c for l_sql_stmt using p_id1, l_id2;
4054 fetch c into l_num;
4055 l_not_found := c%NOTFOUND;
4056 close c;
4057
4058 If (
4059 l_not_found
4060 ) Then
4061 IF (l_debug = 'Y') THEN
4062 okc_util.print_trace(6, 'temp: not found');
4063 END IF;
4064 OKC_API.set_message
4065 (
4066 G_APP_NAME
4067 ,G_UNEXPECTED_ERROR
4068 ,'not found'
4069 ,to_char(p_id1) || ' not in ' || l_from_table || ' table'
4070 ,g_sqlerrm_token
4071 ,sqlerrm
4072 ,'@'
4073 ,l_api_name
4074 );
4075 return l_not_found;
4076 End if;
4077
4078 return l_not_found;
4079 END GET_OBJ_FROM_JTFV;
4080
4081 ---------------------------------------------------------------------------
4082 -- Start of comments
4083 --
4084 -- Procedure Name : GET_OBJ_FROM_JTFV
4085 -- Description :
4086 -- Business Rules :
4087 -- Parameters :
4088 -- Version : 1.0
4089 -- End of comments
4090 PROCEDURE GET_OBJ_FROM_JTFV
4091 (--
4092 p_object_code IN VARCHAR2
4093 ,p_id1 IN NUMBER
4094 ,p_id2 IN VARCHAR2
4095 ,x_true_false out nocopy boolean
4096 ) IS
4097
4098 l_api_version CONSTANT NUMBER := 1;
4099 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJ_FROM_JTFV';
4100 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4101 i number;
4102
4103 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
4104 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
4105 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
4106 G_EXCEPTION_HALT_VALIDATION exception;
4107
4108 BEGIN
4109 /* check native object
4110 */
4111 x_true_false := GET_OBJ_FROM_JTFV
4112 (
4113 p_object_code
4114 ,p_id1
4115 ,p_id2
4116 );
4117 end GET_OBJ_FROM_JTFV;
4118
4119 ---------------------------------------------------------------
4120 -- Procedure for mass insert in OKC_K_REL_OBJS _B and TL tables
4121 ---------------------------------------------------------------
4122 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_crjv_tbl crjv_tbl_type) IS
4123 l_tabsize NUMBER := p_crjv_tbl.COUNT;
4124 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
4125
4126 in_id OKC_DATATYPES.NumberTabTyp;
4127 in_object_version_number OKC_DATATYPES.NumberTabTyp;
4128 in_cle_id OKC_DATATYPES.NumberTabTyp;
4129 in_chr_id OKC_DATATYPES.NumberTabTyp;
4130 in_rty_code OKC_DATATYPES.Var30TabTyp;
4131 in_object1_id1 OKC_DATATYPES.Var40TabTyp;
4132 in_object1_id2 OKC_DATATYPES.Var200TabTyp;
4133 in_jtot_object1_code OKC_DATATYPES.Var30TabTyp;
4134 in_attribute_category OKC_DATATYPES.Var90TabTyp;
4135 in_attribute1 OKC_DATATYPES.Var450TabTyp;
4136 in_attribute2 OKC_DATATYPES.Var450TabTyp;
4137 in_attribute3 OKC_DATATYPES.Var450TabTyp;
4138 in_attribute4 OKC_DATATYPES.Var450TabTyp;
4139 in_attribute5 OKC_DATATYPES.Var450TabTyp;
4140 in_attribute6 OKC_DATATYPES.Var450TabTyp;
4141 in_attribute7 OKC_DATATYPES.Var450TabTyp;
4142 in_attribute8 OKC_DATATYPES.Var450TabTyp;
4143 in_attribute9 OKC_DATATYPES.Var450TabTyp;
4144 in_attribute10 OKC_DATATYPES.Var450TabTyp;
4145 in_attribute11 OKC_DATATYPES.Var450TabTyp;
4146 in_attribute12 OKC_DATATYPES.Var450TabTyp;
4147 in_attribute13 OKC_DATATYPES.Var450TabTyp;
4148 in_attribute14 OKC_DATATYPES.Var450TabTyp;
4149 in_attribute15 OKC_DATATYPES.Var450TabTyp;
4150 in_created_by OKC_DATATYPES.NumberTabTyp;
4151 in_creation_date OKC_DATATYPES.DateTabTyp;
4152 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
4153 in_last_update_date OKC_DATATYPES.DateTabTyp;
4154 in_last_update_login OKC_DATATYPES.NumberTabTyp;
4155 i number;
4156 j number;
4157 l_created_by NUMBER := FND_GLOBAL.USER_ID;
4158 l_creation_date DATE := SYSDATE;
4159 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
4160 l_last_update_date DATE := SYSDATE;
4161 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
4162 l_id NUMBER;
4163 l_object_version_number NUMBER := 1;
4164 BEGIN
4165 -- initialize return status
4166 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4167
4168 i := p_crjv_tbl.FIRST; j:=0;
4169 while i is not null
4170 LOOP
4171 --IF ID is null or default..generate id
4172 If (p_crjv_tbl(i).id is null) or (p_crjv_tbl(i).id = OKC_API.G_MISS_NUM) Then
4173 l_id := get_seq_id;
4174 Else
4175 l_id := p_crjv_tbl(i).id;
4176 End If;
4177 j:=j+1;
4178 in_id (j) := l_id;
4179 in_object_version_number (j) := l_object_version_number;
4180 in_cle_id (j) := p_crjv_tbl(i).cle_id;
4181 in_chr_id (j) := p_crjv_tbl(i).chr_id;
4182 in_rty_code (j) := p_crjv_tbl(i).rty_code;
4183 in_object1_id1 (j) := p_crjv_tbl(i).object1_id1;
4184 in_object1_id2 (j) := p_crjv_tbl(i).object1_id2;
4185 in_jtot_object1_code (j) := p_crjv_tbl(i).jtot_object1_code;
4186 in_attribute_category (j) := p_crjv_tbl(i).attribute_category;
4187 in_attribute1 (j) := p_crjv_tbl(i).attribute1;
4188 in_attribute2 (j) := p_crjv_tbl(i).attribute2;
4189 in_attribute3 (j) := p_crjv_tbl(i).attribute3;
4190 in_attribute4 (j) := p_crjv_tbl(i).attribute4;
4191 in_attribute5 (j) := p_crjv_tbl(i).attribute5;
4192 in_attribute6 (j) := p_crjv_tbl(i).attribute6;
4193 in_attribute7 (j) := p_crjv_tbl(i).attribute7;
4194 in_attribute8 (j) := p_crjv_tbl(i).attribute8;
4195 in_attribute9 (j) := p_crjv_tbl(i).attribute9;
4196 in_attribute10 (j) := p_crjv_tbl(i).attribute10;
4197 in_attribute11 (j) := p_crjv_tbl(i).attribute11;
4198 in_attribute12 (j) := p_crjv_tbl(i).attribute12;
4199 in_attribute13 (j) := p_crjv_tbl(i).attribute13;
4200 in_attribute14 (j) := p_crjv_tbl(i).attribute14;
4201 in_attribute15 (j) := p_crjv_tbl(i).attribute15;
4202 in_created_by (j) := l_created_by;
4203 in_creation_date (j) := l_creation_date;
4204 in_last_updated_by (j) := l_last_updated_by;
4205 in_last_update_date (j) := l_last_update_date;
4206 in_last_update_login (j) := l_last_update_login;
4207 i:=p_crjv_tbl.next(i);
4208 END LOOP;
4209
4210 FORALL i in 1..l_tabsize
4211 INSERT
4212 INTO OKC_K_REL_OBJS
4213 (
4214 id,
4215 cle_id,
4216 chr_id,
4217 rty_code,
4218 object1_id1,
4219 object1_id2,
4220 jtot_object1_code,
4221 object_version_number,
4222 created_by,
4223 creation_date,
4224 last_updated_by,
4225 last_update_date,
4226 last_update_login,
4227 attribute_category,
4228 attribute1,
4229 attribute2,
4230 attribute3,
4231 attribute4,
4232 attribute5,
4233 attribute6,
4234 attribute7,
4235 attribute8,
4236 attribute9,
4237 attribute10,
4238 attribute11,
4239 attribute12,
4240 attribute13,
4241 attribute14,
4242 attribute15
4243 )
4244 VALUES (
4245 in_id(i),
4246 in_cle_id(i),
4247 in_chr_id(i),
4248 in_rty_code(i),
4249 in_object1_id1(i),
4250 in_object1_id2(i),
4251 in_jtot_object1_code(i),
4252 in_object_version_number(i),
4253 in_created_by(i),
4254 in_creation_date(i),
4255 in_last_updated_by(i),
4256 in_last_update_date(i),
4257 in_last_update_login(i),
4258 in_attribute_category(i),
4259 in_attribute1(i),
4260 in_attribute2(i),
4261 in_attribute3(i),
4262 in_attribute4(i),
4263 in_attribute5(i),
4264 in_attribute6(i),
4265 in_attribute7(i),
4266 in_attribute8(i),
4267 in_attribute9(i),
4268 in_attribute10(i),
4269 in_attribute11(i),
4270 in_attribute12(i),
4271 in_attribute13(i),
4272 in_attribute14(i),
4273 in_attribute15(i)
4274 );
4275
4276 EXCEPTION
4277 WHEN OTHERS THEN
4278 -- store SQL error message on message stack
4279 OKC_API.SET_MESSAGE(
4280 p_app_name => G_APP_NAME,
4281 p_msg_name => G_UNEXPECTED_ERROR,
4282 p_token1 => G_SQLCODE_TOKEN,
4283 p_token1_value => SQLCODE,
4284 p_token2 => G_SQLERRM_TOKEN,
4285 p_token2_value => SQLERRM);
4286 -- notify caller of an error as UNEXPECTED error
4287 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4288
4289 -- RAISE;
4290 END INSERT_ROW_UPG;
4291
4292
4293 END OKC_CRJ_PVT;