[Home] [Help]
PACKAGE BODY: APPS.OKC_CPL_PVT
Source
1 PACKAGE BODY OKC_CPL_PVT AS
2 /* $Header: OKCSCPLB.pls 120.5 2011/03/10 18:10:50 harchand ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 /*+++++++++++++Start of hand code +++++++++++++++++*/
6 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
7 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
8 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
9 g_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
10 G_EXCEPTION_HALT_VALIDATION exception;
11 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
12 G_VIEW CONSTANT VARCHAR2(200) := 'OKC_K_PARTY_ROLES_V';
13 /*+++++++++++++End of hand code +++++++++++++++++++*/
14 ---------------------------------------------------------------------------
15 -- FUNCTION get_seq_id
16 ---------------------------------------------------------------------------
17 FUNCTION get_seq_id RETURN NUMBER IS
18 BEGIN
19
20 RETURN(okc_p_util.raw_to_number(sys_guid()));
21
22 END get_seq_id;
23
24 ---------------------------------------------------------------------------
25 -- PROCEDURE qc
26 ---------------------------------------------------------------------------
27 PROCEDURE qc IS
28 BEGIN
29
30 null;
31
32 END qc;
33
34 ---------------------------------------------------------------------------
35 -- PROCEDURE change_version
36 ---------------------------------------------------------------------------
37 PROCEDURE change_version IS
38 BEGIN
39
40 null;
41
42 END change_version;
43
44 ---------------------------------------------------------------------------
45 -- PROCEDURE api_copy
46 ---------------------------------------------------------------------------
47 PROCEDURE api_copy IS
48 BEGIN
49
50 null;
51
52 END api_copy;
53
54 ---------------------------------------------------------------------------
55 -- PROCEDURE add_language
56 ---------------------------------------------------------------------------
57 PROCEDURE add_language IS
58 BEGIN
59
60 IF (l_debug = 'Y') THEN
61 okc_debug.Set_Indentation('OKC_CPL_PVT');
62 okc_debug.log('500: Entered add_language', 2);
63 END IF;
64
65 /* Mar/19/03 requested by Ric Ginsberg */
66 /* The following delete and update statements are commented out */
67 /* as a quick workaround to fix the time-consuming table handler issue */
68 /* Eventually we'll need to turn them into a separate fix_language procedure */
69 /*
70
71 DELETE FROM OKC_K_PARTY_ROLES_TL T
72 WHERE NOT EXISTS (
73 SELECT NULL
74 FROM OKC_K_PARTY_ROLES_B B
75 WHERE B.ID = T.ID
76 );
77
78 UPDATE OKC_K_PARTY_ROLES_TL T SET (
79 COGNOMEN,
80 ALIAS) = (SELECT
81 B.COGNOMEN,
82 B.ALIAS
83 FROM OKC_K_PARTY_ROLES_TL B
84 WHERE B.ID = T.ID
85 AND B.LANGUAGE = T.SOURCE_LANG)
86 WHERE (
87 T.ID,
88 T.LANGUAGE)
89 IN (SELECT
90 SUBT.ID,
91 SUBT.LANGUAGE
92 FROM OKC_K_PARTY_ROLES_TL SUBB, OKC_K_PARTY_ROLES_TL SUBT
93 WHERE SUBB.ID = SUBT.ID
94 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
95 AND (SUBB.COGNOMEN <> SUBT.COGNOMEN
96 OR SUBB.ALIAS <> SUBT.ALIAS
97 OR (SUBB.COGNOMEN IS NULL AND SUBT.COGNOMEN IS NOT NULL)
98 OR (SUBB.COGNOMEN IS NOT NULL AND SUBT.COGNOMEN IS NULL)
99 OR (SUBB.ALIAS IS NULL AND SUBT.ALIAS IS NOT NULL)
100 OR (SUBB.ALIAS IS NOT NULL AND SUBT.ALIAS IS NULL)
101 ));
102 */
103 /* Modifying Insert as per performance guidelines given in bug 3723874 */
104 INSERT /*+ append parallel(tt) */ INTO OKC_K_PARTY_ROLES_TL tt (
105 ID,
106 LANGUAGE,
107 SOURCE_LANG,
108 SFWT_FLAG,
109 CREATED_BY,
110 CREATION_DATE,
111 LAST_UPDATED_BY,
112 LAST_UPDATE_DATE,
113 COGNOMEN,
114 ALIAS,
115 LAST_UPDATE_LOGIN)
116 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
117 (SELECT /*+ no_merge ordered parallel(b) */
118 B.ID,
119 L.LANGUAGE_CODE,
120 B.SOURCE_LANG,
121 B.SFWT_FLAG,
122 B.CREATED_BY,
123 B.CREATION_DATE,
124 B.LAST_UPDATED_BY,
125 B.LAST_UPDATE_DATE,
126 B.COGNOMEN,
127 B.ALIAS,
128 B.LAST_UPDATE_LOGIN
129 FROM OKC_K_PARTY_ROLES_TL B, FND_LANGUAGES L
130 WHERE L.INSTALLED_FLAG IN ('I', 'B')
131 AND B.LANGUAGE = USERENV('LANG')
132 ) v , OKC_K_PARTY_ROLES_TL t
133 WHERE t.ID(+) = v.ID
134 AND t.LANGUAGE(+) = v.LANGUAGE_CODE
135 AND t.id IS NULL;
136
137 /* Commenting delete and update for bug 3723874 */
138 /*
139 DELETE FROM OKC_K_PARTY_ROLES_TLH T
140 WHERE NOT EXISTS (
141 SELECT NULL
142 FROM OKC_K_PARTY_ROLES_BH B
143 WHERE B.ID = T.ID
144 AND B.MAJOR_VERSION = T.MAJOR_VERSION
145 );
146
147 UPDATE OKC_K_PARTY_ROLES_TLH T SET (
148 COGNOMEN,
149 ALIAS) = (SELECT
150 B.COGNOMEN,
151 B.ALIAS
152 FROM OKC_K_PARTY_ROLES_TLH B
153 WHERE B.ID = T.ID
154 AND B.MAJOR_VERSION = T.MAJOR_VERSION
155 AND B.LANGUAGE = T.SOURCE_LANG)
156 WHERE (
157 T.ID,
158 T.MAJOR_VERSION,
159 T.LANGUAGE)
160 IN (SELECT
161 SUBT.ID,
162 SUBT.MAJOR_VERSION,
163 SUBT.LANGUAGE
164 FROM OKC_K_PARTY_ROLES_TLH SUBB, OKC_K_PARTY_ROLES_TLH SUBT
165 WHERE SUBB.ID = SUBT.ID
166 AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
167 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
168 AND (SUBB.COGNOMEN <> SUBT.COGNOMEN
169 OR SUBB.ALIAS <> SUBT.ALIAS
170 OR (SUBB.COGNOMEN IS NULL AND SUBT.COGNOMEN IS NOT NULL)
171 OR (SUBB.COGNOMEN IS NOT NULL AND SUBT.COGNOMEN IS NULL)
172 OR (SUBB.ALIAS IS NULL AND SUBT.ALIAS IS NOT NULL)
173 OR (SUBB.ALIAS IS NOT NULL AND SUBT.ALIAS IS NULL)
174 ));
175 */
176 /* Modifying Insert as per performance guidelines given in bug 3723874 */
177
178 INSERT /*+ append parallel(tt) */ INTO OKC_K_PARTY_ROLES_TLH tt(
179 ID,
180 LANGUAGE,
181 MAJOR_VERSION,
182 SOURCE_LANG,
183 SFWT_FLAG,
184 CREATED_BY,
185 CREATION_DATE,
186 LAST_UPDATED_BY,
187 LAST_UPDATE_DATE,
188 COGNOMEN,
189 ALIAS,
190 LAST_UPDATE_LOGIN)
191 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
192 ( SELECT /*+ no_merge ordered parallel(b) */
193 B.ID,
194 L.LANGUAGE_CODE,
195 B.MAJOR_VERSION,
196 B.SOURCE_LANG,
197 B.SFWT_FLAG,
198 B.CREATED_BY,
199 B.CREATION_DATE,
200 B.LAST_UPDATED_BY,
201 B.LAST_UPDATE_DATE,
202 B.COGNOMEN,
203 B.ALIAS,
204 B.LAST_UPDATE_LOGIN
205 FROM OKC_K_PARTY_ROLES_TLH B, FND_LANGUAGES L
206 WHERE L.INSTALLED_FLAG IN ('I', 'B')
207 AND B.LANGUAGE = USERENV('LANG')
208 ) v, OKC_K_PARTY_ROLES_TLH t
209 WHERE t.ID(+) = v.ID
210 AND t.MAJOR_VERSION(+) = v.MAJOR_VERSION
211 AND t.LANGUAGE(+) = v.LANGUAGE_CODE
212 AND t.id IS NULL;
213
214 IF (l_debug = 'Y') THEN
215 okc_debug.log('500: Leaving add_language ', 2);
216 okc_debug.Reset_Indentation;
217 END IF;
218
219 END add_language;
220
221 ---------------------------------------------------------------------------
222 -- FUNCTION get_rec for: OKC_K_PARTY_ROLES_B
223 ---------------------------------------------------------------------------
224 FUNCTION get_rec (
225 p_cpl_rec IN cpl_rec_type,
226 x_no_data_found OUT NOCOPY BOOLEAN
227 ) RETURN cpl_rec_type IS
228 CURSOR cpl_pk_csr (p_id IN NUMBER) IS
229 SELECT
230 ID,
231 CPL_ID,CHR_ID,
232 CLE_ID,
233 DNZ_CHR_ID,
234 RLE_CODE,
235 OBJECT1_ID1,
236 OBJECT1_ID2,
237 JTOT_OBJECT1_CODE,
238 OBJECT_VERSION_NUMBER,
239 CREATED_BY,
240 CREATION_DATE,
241 LAST_UPDATED_BY,
242 LAST_UPDATE_DATE,
243 CODE,
244 FACILITY,
245 MINORITY_GROUP_LOOKUP_CODE,
246 SMALL_BUSINESS_FLAG,
247 WOMEN_OWNED_FLAG,
248 LAST_UPDATE_LOGIN,
249 PRIMARY_YN,
250 CUST_ACCT_ID,
251 BILL_TO_SITE_USE_ID,
252 ATTRIBUTE_CATEGORY,
253 ATTRIBUTE1,
254 ATTRIBUTE2,
255 ATTRIBUTE3,
256 ATTRIBUTE4,
257 ATTRIBUTE5,
258 ATTRIBUTE6,
259 ATTRIBUTE7,
260 ATTRIBUTE8,
261 ATTRIBUTE9,
262 ATTRIBUTE10,
263 ATTRIBUTE11,
264 ATTRIBUTE12,
265 ATTRIBUTE13,
266 ATTRIBUTE14,
267 ATTRIBUTE15,
268 -- R12 Data Model Changes 4485150 Start
269 ORIG_SYSTEM_ID1,
270 ORIG_SYSTEM_REFERENCE1,
271 ORIG_SYSTEM_SOURCE_CODE
272 -- R12 Data Model Changes 4485150 End
273 FROM Okc_K_Party_Roles_B
274 WHERE okc_k_party_roles_b.id = p_id;
275 l_cpl_pk cpl_pk_csr%ROWTYPE;
276 l_cpl_rec cpl_rec_type;
277 BEGIN
278
279 IF (l_debug = 'Y') THEN
280 okc_debug.Set_Indentation('OKC_CPL_PVT');
281 okc_debug.log('600: Entered get_rec', 2);
282 END IF;
283
284 x_no_data_found := TRUE;
285 -- Get current database values
286 OPEN cpl_pk_csr (p_cpl_rec.id);
287 FETCH cpl_pk_csr INTO
288 l_cpl_rec.ID,
289 l_cpl_rec.CPL_ID,l_cpl_rec.CHR_ID,
290 l_cpl_rec.CLE_ID,
291 l_cpl_rec.DNZ_CHR_ID,
292 l_cpl_rec.RLE_CODE,
293 l_cpl_rec.OBJECT1_ID1,
294 l_cpl_rec.OBJECT1_ID2,
295 l_cpl_rec.JTOT_OBJECT1_CODE,
296 l_cpl_rec.OBJECT_VERSION_NUMBER,
297 l_cpl_rec.CREATED_BY,
298 l_cpl_rec.CREATION_DATE,
299 l_cpl_rec.LAST_UPDATED_BY,
300 l_cpl_rec.LAST_UPDATE_DATE,
301 l_cpl_rec.CODE,
302 l_cpl_rec.FACILITY,
303 l_cpl_rec.MINORITY_GROUP_LOOKUP_CODE,
304 l_cpl_rec.SMALL_BUSINESS_FLAG,
305 l_cpl_rec.WOMEN_OWNED_FLAG,
306 l_cpl_rec.LAST_UPDATE_LOGIN,
307 l_cpl_rec.PRIMARY_YN,
308 l_cpl_rec.CUST_ACCT_ID,
309 l_cpl_rec.BILL_TO_SITE_USE_ID,
310 l_cpl_rec.ATTRIBUTE_CATEGORY,
311 l_cpl_rec.ATTRIBUTE1,
312 l_cpl_rec.ATTRIBUTE2,
313 l_cpl_rec.ATTRIBUTE3,
314 l_cpl_rec.ATTRIBUTE4,
315 l_cpl_rec.ATTRIBUTE5,
316 l_cpl_rec.ATTRIBUTE6,
317 l_cpl_rec.ATTRIBUTE7,
318 l_cpl_rec.ATTRIBUTE8,
319 l_cpl_rec.ATTRIBUTE9,
320 l_cpl_rec.ATTRIBUTE10,
321 l_cpl_rec.ATTRIBUTE11,
322 l_cpl_rec.ATTRIBUTE12,
323 l_cpl_rec.ATTRIBUTE13,
324 l_cpl_rec.ATTRIBUTE14,
325 l_cpl_rec.ATTRIBUTE15,
326 -- R12 Data Model Changes 4485150 Start
327 l_cpl_rec.ORIG_SYSTEM_ID1,
328 l_cpl_rec.ORIG_SYSTEM_REFERENCE1,
329 l_cpl_rec.ORIG_SYSTEM_SOURCE_CODE
330 -- R12 Data Model Changes 4485150 End
331 ;
332 x_no_data_found := cpl_pk_csr%NOTFOUND;
333 CLOSE cpl_pk_csr;
334
335 IF (l_debug = 'Y') THEN
336 okc_debug.log('700: Leaving Fn Get_Rec ', 2);
337 okc_debug.Reset_Indentation;
338 END IF;
339
340 RETURN(l_cpl_rec);
341
342 END get_rec;
343
344 FUNCTION get_rec (
345 p_cpl_rec IN cpl_rec_type
346 ) RETURN cpl_rec_type IS
347 l_row_notfound BOOLEAN := TRUE;
348 BEGIN
349
350 RETURN(get_rec(p_cpl_rec, l_row_notfound));
351
352 END get_rec;
353 ---------------------------------------------------------------------------
354 -- FUNCTION get_rec for: OKC_K_PARTY_ROLES_TL
355 ---------------------------------------------------------------------------
356 FUNCTION get_rec (
357 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type,
358 x_no_data_found OUT NOCOPY BOOLEAN
359 ) RETURN okc_k_party_roles_tl_rec_type IS
360 CURSOR cpl_pktl_csr (p_id IN NUMBER,
361 p_language IN VARCHAR2) IS
362 SELECT
363 ID,
364 LANGUAGE,
365 SOURCE_LANG,
366 SFWT_FLAG,
367 CREATED_BY,
368 CREATION_DATE,
369 LAST_UPDATED_BY,
370 LAST_UPDATE_DATE,
371 COGNOMEN,
372 ALIAS,
373 LAST_UPDATE_LOGIN
374 FROM Okc_K_Party_Roles_Tl
375 WHERE okc_k_party_roles_tl.id = p_id
376 AND okc_k_party_roles_tl.language = p_language;
377 l_cpl_pktl cpl_pktl_csr%ROWTYPE;
378 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
379 BEGIN
380
381 IF (l_debug = 'Y') THEN
382 okc_debug.Set_Indentation('OKC_CPL_PVT');
383 okc_debug.log('800: Entered get_rec', 2);
384 END IF;
385
386 x_no_data_found := TRUE;
387 -- Get current database values
388 OPEN cpl_pktl_csr (p_okc_k_party_roles_tl_rec.id,
389 p_okc_k_party_roles_tl_rec.language);
390 FETCH cpl_pktl_csr INTO
391 l_okc_k_party_roles_tl_rec.ID,
392 l_okc_k_party_roles_tl_rec.LANGUAGE,
393 l_okc_k_party_roles_tl_rec.SOURCE_LANG,
394 l_okc_k_party_roles_tl_rec.SFWT_FLAG,
395 l_okc_k_party_roles_tl_rec.CREATED_BY,
396 l_okc_k_party_roles_tl_rec.CREATION_DATE,
397 l_okc_k_party_roles_tl_rec.LAST_UPDATED_BY,
398 l_okc_k_party_roles_tl_rec.LAST_UPDATE_DATE,
399 l_okc_k_party_roles_tl_rec.COGNOMEN,
400 l_okc_k_party_roles_tl_rec.ALIAS,
401 l_okc_k_party_roles_tl_rec.LAST_UPDATE_LOGIN;
402 x_no_data_found := cpl_pktl_csr%NOTFOUND;
403 CLOSE cpl_pktl_csr;
404
405 IF (l_debug = 'Y') THEN
406 okc_debug.Set_Indentation('OKC_CPL_PVT');
407 okc_debug.log('900: Entered get_rec', 2);
408 END IF;
409
410 RETURN(l_okc_k_party_roles_tl_rec);
411
412 END get_rec;
413
414 FUNCTION get_rec (
415 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type
416 ) RETURN okc_k_party_roles_tl_rec_type IS
417 l_row_notfound BOOLEAN := TRUE;
418 BEGIN
419
420 RETURN(get_rec(p_okc_k_party_roles_tl_rec, l_row_notfound));
421
422 END get_rec;
423 ---------------------------------------------------------------------------
424 -- FUNCTION get_rec for: OKC_K_PARTY_ROLES_V
425 ---------------------------------------------------------------------------
426 FUNCTION get_rec (
427 p_cplv_rec IN cplv_rec_type,
428 x_no_data_found OUT NOCOPY BOOLEAN
429 ) RETURN cplv_rec_type IS
430 CURSOR okc_cplv_pk_csr (p_id IN NUMBER) IS
431 SELECT
432 ID,
433 OBJECT_VERSION_NUMBER,
434 SFWT_FLAG,
435 CPL_ID,CHR_ID,
436 CLE_ID,
437 RLE_CODE,
438 DNZ_CHR_ID,
439 OBJECT1_ID1,
440 OBJECT1_ID2,
441 JTOT_OBJECT1_CODE,
442 COGNOMEN,
443 CODE,
444 FACILITY,
445 MINORITY_GROUP_LOOKUP_CODE,
446 SMALL_BUSINESS_FLAG,
447 WOMEN_OWNED_FLAG,
448 ALIAS,
449 PRIMARY_YN,
450 CUST_ACCT_ID,
451 BILL_TO_SITE_USE_ID,
452 ATTRIBUTE_CATEGORY,
453 ATTRIBUTE1,
454 ATTRIBUTE2,
455 ATTRIBUTE3,
456 ATTRIBUTE4,
457 ATTRIBUTE5,
458 ATTRIBUTE6,
459 ATTRIBUTE7,
460 ATTRIBUTE8,
461 ATTRIBUTE9,
462 ATTRIBUTE10,
463 ATTRIBUTE11,
464 ATTRIBUTE12,
465 ATTRIBUTE13,
466 ATTRIBUTE14,
467 ATTRIBUTE15,
468 CREATED_BY,
469 CREATION_DATE,
470 LAST_UPDATED_BY,
471 LAST_UPDATE_DATE,
472 LAST_UPDATE_LOGIN
473 FROM Okc_K_Party_Roles_V
474 WHERE okc_k_party_roles_v.id = p_id;
475 l_okc_cplv_pk okc_cplv_pk_csr%ROWTYPE;
476 l_cplv_rec cplv_rec_type;
477 BEGIN
478
479 IF (l_debug = 'Y') THEN
480 okc_debug.Set_Indentation('OKC_CPL_PVT');
481 okc_debug.log('1000: Entered get_rec', 2);
482 END IF;
483
484 x_no_data_found := TRUE;
485 -- Get current database values
486 OPEN okc_cplv_pk_csr (p_cplv_rec.id);
487 FETCH okc_cplv_pk_csr INTO
488 l_cplv_rec.ID,
489 l_cplv_rec.OBJECT_VERSION_NUMBER,
490 l_cplv_rec.SFWT_FLAG,
491 l_cplv_rec.CPL_ID,l_cplv_rec.CHR_ID,
492 l_cplv_rec.CLE_ID,
493 l_cplv_rec.RLE_CODE,
494 l_cplv_rec.DNZ_CHR_ID,
495 l_cplv_rec.OBJECT1_ID1,
496 l_cplv_rec.OBJECT1_ID2,
497 l_cplv_rec.JTOT_OBJECT1_CODE,
498 l_cplv_rec.COGNOMEN,
499 l_cplv_rec.CODE,
500 l_cplv_rec.FACILITY,
501 l_cplv_rec.MINORITY_GROUP_LOOKUP_CODE,
502 l_cplv_rec.SMALL_BUSINESS_FLAG,
503 l_cplv_rec.WOMEN_OWNED_FLAG,
504 l_cplv_rec.ALIAS,
505 l_cplv_rec.PRIMARY_YN,
506 l_cplv_rec.CUST_ACCT_ID,
507 l_cplv_rec.BILL_TO_SITE_USE_ID,
508 l_cplv_rec.ATTRIBUTE_CATEGORY,
509 l_cplv_rec.ATTRIBUTE1,
510 l_cplv_rec.ATTRIBUTE2,
511 l_cplv_rec.ATTRIBUTE3,
512 l_cplv_rec.ATTRIBUTE4,
513 l_cplv_rec.ATTRIBUTE5,
514 l_cplv_rec.ATTRIBUTE6,
515 l_cplv_rec.ATTRIBUTE7,
516 l_cplv_rec.ATTRIBUTE8,
517 l_cplv_rec.ATTRIBUTE9,
518 l_cplv_rec.ATTRIBUTE10,
519 l_cplv_rec.ATTRIBUTE11,
520 l_cplv_rec.ATTRIBUTE12,
521 l_cplv_rec.ATTRIBUTE13,
522 l_cplv_rec.ATTRIBUTE14,
523 l_cplv_rec.ATTRIBUTE15,
524 l_cplv_rec.CREATED_BY,
525 l_cplv_rec.CREATION_DATE,
526 l_cplv_rec.LAST_UPDATED_BY,
527 l_cplv_rec.LAST_UPDATE_DATE,
528 l_cplv_rec.LAST_UPDATE_LOGIN;
529 x_no_data_found := okc_cplv_pk_csr%NOTFOUND;
530 CLOSE okc_cplv_pk_csr;
531
532 IF (l_debug = 'Y') THEN
533 okc_debug.Set_Indentation('OKC_CPL_PVT');
534 okc_debug.log('1050: Entered get_rec', 2);
535 END IF;
536
537 RETURN(l_cplv_rec);
538
539 END get_rec;
540
541 FUNCTION get_rec (
542 p_cplv_rec IN cplv_rec_type
543 ) RETURN cplv_rec_type IS
544 l_row_notfound BOOLEAN := TRUE;
545 BEGIN
546
547 RETURN(get_rec(p_cplv_rec, l_row_notfound));
548
549 END get_rec;
550
551 ---------------------------------------------------------
552 -- FUNCTION null_out_defaults for: OKC_K_PARTY_ROLES_V --
553 ---------------------------------------------------------
554 FUNCTION null_out_defaults (
555 p_cplv_rec IN cplv_rec_type
556 ) RETURN cplv_rec_type IS
557 l_cplv_rec cplv_rec_type := p_cplv_rec;
558 BEGIN
559
560 IF (l_debug = 'Y') THEN
561 okc_debug.Set_Indentation('OKC_CPL_PVT');
562 okc_debug.log('1200: Entered null_out_defaults', 2);
563 END IF;
564
565 IF (l_cplv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
566 l_cplv_rec.object_version_number := NULL;
567 END IF;
568 IF (l_cplv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
569 l_cplv_rec.sfwt_flag := NULL;
570 END IF;
571 IF (l_cplv_rec.cpl_id = OKC_API.G_MISS_NUM) THEN
572 l_cplv_rec.cpl_id := NULL;
573 END IF;
574 IF (l_cplv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
575 l_cplv_rec.chr_id := NULL;
576 END IF;
577 IF (l_cplv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
578 l_cplv_rec.cle_id := NULL;
579 END IF;
580 IF (l_cplv_rec.rle_code = OKC_API.G_MISS_CHAR) THEN
581 l_cplv_rec.rle_code := NULL;
582 END IF;
583 IF (l_cplv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
584 l_cplv_rec.dnz_chr_id := NULL;
585 END IF;
586 IF (l_cplv_rec.object1_id1 = OKC_API.G_MISS_CHAR) THEN
587 l_cplv_rec.object1_id1 := NULL;
588 END IF;
589 IF (l_cplv_rec.object1_id2 = OKC_API.G_MISS_CHAR) THEN
590 l_cplv_rec.object1_id2 := NULL;
591 END IF;
592 IF (l_cplv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR) THEN
593 l_cplv_rec.JTOT_OBJECT1_CODE := NULL;
594 END IF;
595 IF (l_cplv_rec.cognomen = OKC_API.G_MISS_CHAR) THEN
596 l_cplv_rec.cognomen := NULL;
597 END IF;
598 IF (l_cplv_rec.code = OKC_API.G_MISS_CHAR) THEN
599 l_cplv_rec.code := NULL;
600 END IF;
601 IF (l_cplv_rec.facility = OKC_API.G_MISS_CHAR) THEN
602 l_cplv_rec.facility := NULL;
603 END IF;
604 IF (l_cplv_rec.minority_group_lookup_code = OKC_API.G_MISS_CHAR) THEN
605 l_cplv_rec.minority_group_lookup_code := NULL;
606 END IF;
607 IF (l_cplv_rec.small_business_flag = OKC_API.G_MISS_CHAR) THEN
608 l_cplv_rec.small_business_flag := NULL;
609 END IF;
610 IF (l_cplv_rec.women_owned_flag = OKC_API.G_MISS_CHAR) THEN
611 l_cplv_rec.women_owned_flag := NULL;
612 END IF;
613 IF (l_cplv_rec.alias = OKC_API.G_MISS_CHAR) THEN
614 l_cplv_rec.alias := NULL;
615 END IF;
616 IF (l_cplv_rec.primary_yn = OKC_API.G_MISS_CHAR) THEN
617 l_cplv_rec.primary_yn := NULL;
618 END IF;
619 IF (l_cplv_rec.cust_acct_id = OKC_API.G_MISS_NUM) THEN
620 l_cplv_rec.cust_acct_id := NULL;
621 END IF;
622 IF (l_cplv_rec.bill_to_site_use_id = OKC_API.G_MISS_NUM) THEN
623 l_cplv_rec.bill_to_site_use_id := NULL;
624 END IF;
625 IF (l_cplv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
626 l_cplv_rec.attribute_category := NULL;
627 END IF;
628 IF (l_cplv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
629 l_cplv_rec.attribute1 := NULL;
630 END IF;
631 IF (l_cplv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
632 l_cplv_rec.attribute2 := NULL;
633 END IF;
634 IF (l_cplv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
635 l_cplv_rec.attribute3 := NULL;
636 END IF;
637 IF (l_cplv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
638 l_cplv_rec.attribute4 := NULL;
639 END IF;
640 IF (l_cplv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
641 l_cplv_rec.attribute5 := NULL;
642 END IF;
643 IF (l_cplv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
644 l_cplv_rec.attribute6 := NULL;
645 END IF;
646 IF (l_cplv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
647 l_cplv_rec.attribute7 := NULL;
648 END IF;
649 IF (l_cplv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
650 l_cplv_rec.attribute8 := NULL;
651 END IF;
652 IF (l_cplv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
653 l_cplv_rec.attribute9 := NULL;
654 END IF;
655 IF (l_cplv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
656 l_cplv_rec.attribute10 := NULL;
657 END IF;
658 IF (l_cplv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
659 l_cplv_rec.attribute11 := NULL;
660 END IF;
661 IF (l_cplv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
662 l_cplv_rec.attribute12 := NULL;
663 END IF;
664 IF (l_cplv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
665 l_cplv_rec.attribute13 := NULL;
666 END IF;
667 IF (l_cplv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
668 l_cplv_rec.attribute14 := NULL;
669 END IF;
670 IF (l_cplv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
671 l_cplv_rec.attribute15 := NULL;
672 END IF;
673 IF (l_cplv_rec.created_by = OKC_API.G_MISS_NUM) THEN
674 l_cplv_rec.created_by := NULL;
675 END IF;
676 IF (l_cplv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
677 l_cplv_rec.creation_date := NULL;
678 END IF;
679 IF (l_cplv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
680 l_cplv_rec.last_updated_by := NULL;
681 END IF;
682 IF (l_cplv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
683 l_cplv_rec.last_update_date := NULL;
684 END IF;
685 IF (l_cplv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
686 l_cplv_rec.last_update_login := NULL;
687 END IF;
688
689 IF (l_debug = 'Y') THEN
690 okc_debug.Set_Indentation('OKC_CPL_PVT');
691 okc_debug.log('1150: Entered get_rec', 2);
692 END IF;
693
694 RETURN(l_cplv_rec);
695
696 END null_out_defaults;
697 ---------------------------------------------------------------------------
698 -- PROCEDURE Validate_Attributes
699 ---------------------------------------------------------------------------
700 /*+++++++++++++Start of hand code +++++++++++++++++*/
701
702 -- Start of comments
703 --
704 -- Procedure Name : validate_cle_id
705 -- Description :
706 -- Business Rules :
707 -- Parameters :
708 -- Version : 1.0
709 -- End of comments
710
711 procedure validate_cle_id(x_return_status OUT NOCOPY VARCHAR2,
712 p_cplv_rec IN CPLV_REC_TYPE) is
713 l_dummy_var varchar2(1) := '?';
714 cursor l_cle_csr is
715 select 'x'
716 from OKC_K_LINES_B
717 where id = p_cplv_rec.cle_id;
718 begin
719
720 IF (l_debug = 'Y') THEN
721 okc_debug.Set_Indentation('OKC_CPL_PVT');
722 okc_debug.log('1300: Entered validate_cle_id', 2);
723 END IF;
724
725 x_return_status := OKC_API.G_RET_STS_SUCCESS;
726 if (p_cplv_rec.cle_id = OKC_API.G_MISS_NUM or p_cplv_rec.cle_id is NULL) then
727 IF (l_debug = 'Y') THEN
728 okc_debug.Reset_Indentation;
729 END IF;
730 return;
731 end if;
732 open l_cle_csr;
733 fetch l_cle_csr into l_dummy_var;
734 close l_cle_csr;
735 if (l_dummy_var = '?') then
736 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLE_ID');
737 raise G_EXCEPTION_HALT_VALIDATION;
738 end if;
739
740 IF (l_debug = 'Y') THEN
741 okc_debug.log('1400: Leaving validate_cle_id', 2);
742 okc_debug.Reset_Indentation;
743 END IF;
744
745 exception
746 when G_EXCEPTION_HALT_VALIDATION then
747
748 IF (l_debug = 'Y') THEN
749 okc_debug.log('1500: Exiting validate_cle_id:G_EXCEPTION_HALT_VALIDATION Exception', 2);
750 okc_debug.Reset_Indentation;
751 END IF;
752
753 x_return_status := OKC_API.G_RET_STS_ERROR;
754 when OTHERS then
755
756 IF (l_debug = 'Y') THEN
757 okc_debug.log('1600: Exiting validate_cle_id:OTHERS Exception', 2);
758 okc_debug.Reset_Indentation;
759 END IF;
760
761 if l_cle_csr%ISOPEN then
762 close l_cle_csr;
763 end if;
764 OKC_API.set_message(p_app_name => g_app_name,
765 p_msg_name => g_unexpected_error,
766 p_token1 => g_sqlcode_token,
767 p_token1_value => sqlcode,
768 p_token2 => g_sqlerrm_token,
769 p_token2_value => sqlerrm);
770 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
771
772 end validate_cle_id;
773
774 -- Start of comments
775 --
776 -- Procedure Name : validate_cpl_id
777 -- Description :
778 -- Business Rules :
779 -- Parameters :
780 -- Version : 1.0
781 -- End of comments
782
783 procedure validate_cpl_id(x_return_status OUT NOCOPY VARCHAR2,
784 p_cplv_rec IN CPLV_REC_TYPE) is
785 l_dummy_var varchar2(1) := '?';
786 cursor l_cpl_csr is
787 select 'x'
788 from OKC_K_PARTY_ROLES_B
789 where id = p_cplv_rec.cpl_id;
790 begin
791
792 IF (l_debug = 'Y') THEN
793 okc_debug.Set_Indentation('OKC_CPL_PVT');
794 okc_debug.log('1700: Entered validate_cpl_id', 2);
795 END IF;
796
797 x_return_status := OKC_API.G_RET_STS_SUCCESS;
798 if (p_cplv_rec.cpl_id = OKC_API.G_MISS_NUM or p_cplv_rec.cpl_id is NULL) then
799 x_return_status := OKC_API.G_RET_STS_SUCCESS;
800 IF (l_debug = 'Y') THEN
801 okc_debug.Reset_Indentation;
802 END IF;
803 return;
804 end if;
805 open l_cpl_csr;
806 fetch l_cpl_csr into l_dummy_var;
807 close l_cpl_csr;
808 if (l_dummy_var = '?') then
809 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CPL_ID');
810 x_return_status := OKC_API.G_RET_STS_ERROR;
811 end if;
812
813 IF (l_debug = 'Y') THEN
814 okc_debug.log('1800: Leaving validate_cpl_id', 2);
815 okc_debug.Reset_Indentation;
816 END IF;
817
818 exception
819 when OTHERS then
820
821 IF (l_debug = 'Y') THEN
822 okc_debug.log('1900: Exiting validate_cpl_id:OTHERS Exception', 2);
823 okc_debug.Reset_Indentation;
824 END IF;
825
826 if l_cpl_csr%ISOPEN then
827 close l_cpl_csr;
828 end if;
829 OKC_API.set_message(p_app_name => g_app_name,
830 p_msg_name => g_unexpected_error,
831 p_token1 => g_sqlcode_token,
832 p_token1_value => sqlcode,
833 p_token2 => g_sqlerrm_token,
834 p_token2_value => sqlerrm);
835 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
836
837 end validate_cpl_id;
838
839 -- Start of comments
840 --
841 -- Procedure Name : validate_chr_id
842 -- Description :
843 -- Business Rules :
844 -- Parameters :
845 -- Version : 1.0
846 -- End of comments
847
848 procedure validate_chr_id(x_return_status OUT NOCOPY VARCHAR2,
849 p_cplv_rec IN CPLV_REC_TYPE) is
850 l_dummy_var varchar2(1) := '?';
851 cursor l_chr_csr is
852 select 'x'
853 from OKC_K_HEADERS_ALL_B -- Modified by jvorugan for Bug:4645341 OKC_K_HEADERS_B
854 where id = p_cplv_rec.chr_id;
855 begin
856
857 IF (l_debug = 'Y') THEN
858 okc_debug.Set_Indentation('OKC_CPL_PVT');
859 okc_debug.log('2000: Entered validate_chr_id', 2);
860 END IF;
861
862 x_return_status := OKC_API.G_RET_STS_SUCCESS;
863 if (p_cplv_rec.chr_id = OKC_API.G_MISS_NUM or p_cplv_rec.chr_id is NULL) then
864 x_return_status := OKC_API.G_RET_STS_SUCCESS;
865 IF (l_debug = 'Y') THEN
866 okc_debug.Reset_Indentation;
867 END IF;
868 return;
869 end if;
870 open l_chr_csr;
871 fetch l_chr_csr into l_dummy_var;
872 close l_chr_csr;
873 if (l_dummy_var = '?') then
874 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CHR_ID');
875 x_return_status := OKC_API.G_RET_STS_ERROR;
876 end if;
877
878 IF (l_debug = 'Y') THEN
879 okc_debug.log('2100: Leaving validate_chr_id', 2);
880 okc_debug.Reset_Indentation;
881 END IF;
882
883 exception
884 when OTHERS then
885
886 IF (l_debug = 'Y') THEN
887 okc_debug.log('2200: Exiting validate_chr_id:OTHERS Exception', 2);
888 okc_debug.Reset_Indentation;
889 END IF;
890
891 if l_chr_csr%ISOPEN then
892 close l_chr_csr;
893 end if;
894 OKC_API.set_message(p_app_name => g_app_name,
895 p_msg_name => g_unexpected_error,
896 p_token1 => g_sqlcode_token,
897 p_token1_value => sqlcode,
898 p_token2 => g_sqlerrm_token,
899 p_token2_value => sqlerrm);
900 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
901
902 end validate_chr_id;
903
904 -- Start of comments
905 --
906 -- Procedure Name : validate_rle_code
907 -- Description :
908 -- Business Rules :
909 -- Parameters :
910 -- Version : 1.0
911 -- End of comments
912 procedure validate_rle_code(x_return_status OUT NOCOPY VARCHAR2,
913 p_cplv_rec IN CPLV_REC_TYPE) is
914 --
915 l_dummy_var varchar2(1) := '?';
916 --
917
918 cursor chr_csr is
919 select '!'
920 from
921 OKC_K_HEADERS_ALL_B -- Modified by jvorugan for Bug:4645341 OKC_K_HEADERS_B
922 ,okc_subclass_roles
923 where
924 okc_k_headers_all_b.id = p_cplv_rec.chr_id
925 and okc_subclass_roles.scs_code = okc_k_headers_all_b.scs_code
926 and okc_subclass_roles.rle_code = p_cplv_rec.rle_code
927 and sysdate between okc_subclass_roles.start_date
928 and NVL(okc_subclass_roles.end_date,sysdate)
929 ;
930 --
931 cursor cle_csr is
932 select '!'
933 from
934 okc_k_lines_b
935 ,okc_line_style_roles
936 where
937 okc_k_lines_b.ID = p_cplv_rec.cle_id
938 and okc_line_style_roles.LSE_ID = okc_k_lines_b.LSE_ID
939 and okc_line_style_roles.SRE_ID in
940 (
941 select okc_subclass_roles.ID
942 from
943 OKC_K_HEADERS_ALL_B -- Modified by jvorugan for Bug:4645341 OKC_K_HEADERS_B
944 ,okc_subclass_roles
945 where
946 okc_k_headers_all_b.id = p_cplv_rec.dnz_chr_id
947 and okc_subclass_roles.scs_code = okc_k_headers_all_b.scs_code
948 and okc_subclass_roles.rle_code = p_cplv_rec.rle_code
949 and sysdate between okc_subclass_roles.start_date
950 and NVL(okc_subclass_roles.end_date,sysdate)
951 )
952 ;
953
954 --commenting for Bug 3607178
955 /*
956 --
957 --Bug#3101222
958 ---Check wether it is Service Contrcats or NOT.
959 CURSOR l_Service_Contract_csr IS
960 SELECT 'x'
961 FROM OKC_SUBCLASSES_B SCS ,
962 OKC_K_HEADERS_B HDR
963 WHERE SCS.CODE = HDR.SCS_CODE
964 AND SCS.CLS_CODE = 'SERVICE'
965 AND HDR.ID = p_cplv_rec.chr_id ;
966
967 --Duplicate Party Role Check
968 --Only for service Contract
969 CURSOR l_Duplicate_role_csr IS
970 SELECT 'x'
971 FROM OKC_K_PARTY_ROLES_B PARTY
972 WHERE PARTY.DNZ_CHR_ID = p_cplv_rec.chr_id
973 AND PARTY.CLE_ID IS NULL
974 AND PARTY.RLE_CODE = p_cplv_rec.rle_code ;
975 --Bug#3101222 */
976 begin
977
978 IF (l_debug = 'Y') THEN
979 okc_debug.Set_Indentation('OKC_CPL_PVT');
980 okc_debug.log('2300: Entered validate_rle_code', 2);
981 END IF;
982
983 x_return_status := OKC_API.G_RET_STS_SUCCESS;
984 --1
985 if (p_cplv_rec.rle_code = OKC_API.G_MISS_CHAR) then
986 IF (l_debug = 'Y') THEN
987 okc_debug.Reset_Indentation;
988 END IF;
989 return;
990 end if;
991 --2
992 if (p_cplv_rec.rle_code is NULL) then
993 OKC_API.set_message(p_app_name => g_app_name,
994 p_msg_name => G_REQUIRED_VALUE,
995 p_token1 => G_COL_NAME_TOKEN,
996 p_token1_value => 'RLE_CODE');
997 raise G_EXCEPTION_HALT_VALIDATION;
998 end if;
999 --3
1000
1001 x_return_status := OKC_UTIL.check_lookup_code('OKC_ROLE',p_cplv_rec.rle_code);
1002 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1003 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RLE_CODE');
1004 raise G_EXCEPTION_HALT_VALIDATION;
1005 end if;
1006 --4
1007 if (p_cplv_rec.chr_id is not NULL and p_cplv_rec.chr_id <> OKC_API.G_MISS_NUM) then
1008 open chr_csr;
1009 fetch chr_csr into l_dummy_var;
1010 close chr_csr;
1011 if (l_dummy_var = '?') then
1012 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RLE_CODE');
1013 raise G_EXCEPTION_HALT_VALIDATION;
1014 end if;
1015 end if;
1016 --5
1017 if (p_cplv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) then
1018 IF (l_debug = 'Y') THEN
1019 okc_debug.Reset_Indentation;
1020 END IF;
1021 return;
1022 end if;
1023 if (p_cplv_rec.cle_id is not NULL and p_cplv_rec.cle_id <> OKC_API.G_MISS_NUM) then
1024 open cle_csr;
1025 fetch cle_csr into l_dummy_var;
1026 close cle_csr;
1027 if (l_dummy_var = '?') then
1028 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RLE_CODE');
1029 raise G_EXCEPTION_HALT_VALIDATION;
1030 end if;
1031 end if;
1032 --end
1033
1034 --commenting for Bug 3607178
1035 /*
1036 --BUG#3101222
1037
1038 l_dummy_var := '?';
1039 open l_Service_Contract_csr;
1040 fetch l_Service_Contract_csr into l_dummy_var;
1041 close l_Service_Contract_csr;
1042
1043 -- l_dummy_var='x', Then it is a Service Contract.
1044 IF (l_dummy_var = 'x') THEN
1045 IF (p_cplv_rec.rle_code <> OKC_API.G_MISS_CHAR OR p_cplv_rec.rle_code IS NOT NULL) THEN
1046 l_dummy_var := '?'; --resetting the contents
1047 OPEN l_Duplicate_role_csr;
1048 feTCH l_Duplicate_role_csr INTO l_dummy_var;
1049 CLOSE l_Duplicate_role_csr ;
1050 IF l_dummy_var = 'x' THEN
1051 OKC_API.set_message(G_APP_NAME, 'OKC_DUPLICATE_PARTY_ROLE');
1052 x_return_status := OKC_API.G_RET_STS_ERROR;
1053 raise G_EXCEPTION_HALT_VALIDATION;
1054 END IF;
1055 END IF;
1056 END IF;
1057 --BUG#3101222
1058 */
1059 IF (l_debug = 'Y') THEN
1060 okc_debug.log('2400: Leaving validate_rle_code', 2);
1061 okc_debug.Reset_Indentation;
1062 END IF;
1063
1064 exception
1065 when G_EXCEPTION_HALT_VALIDATION then
1066
1067 IF (l_debug = 'Y') THEN
1068 okc_debug.log('2500: Exiting validate_rle_code:G_EXCEPTION_HALT_VALIDATION Exception', 2);
1069 okc_debug.Reset_Indentation;
1070 END IF;
1071
1072 x_return_status := OKC_API.G_RET_STS_ERROR;
1073 when OTHERS then
1074
1075 IF (l_debug = 'Y') THEN
1076 okc_debug.log('2600: Exiting validate_rle_code:OTHERS Exception', 2);
1077 okc_debug.Reset_Indentation;
1078 END IF;
1079
1080 if chr_csr%ISOPEN then
1081 close chr_csr;
1082 end if;
1083 if cle_csr%ISOPEN then
1084 close cle_csr;
1085 end if;
1086 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1087 p_msg_name => g_unexpected_error,
1088 p_token1 => g_sqlcode_token,
1089 p_token1_value => sqlcode,
1090 p_token2 => g_sqlerrm_token,
1091 p_token2_value => sqlerrm);
1092 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1093
1094 end validate_rle_code;
1095
1096 -- Start of comments
1097 --
1098 -- Procedure Name : validate_small_business_flag
1099 -- Description :
1100 -- Business Rules :
1101 -- Parameters :
1102 -- Version : 1.0
1103 -- End of comments
1104 procedure validate_small_business_flag(x_return_status OUT NOCOPY VARCHAR2,
1105 p_cplv_rec IN CPLV_REC_TYPE) is
1106 begin
1107
1108 IF (l_debug = 'Y') THEN
1109 okc_debug.Set_Indentation('OKC_CPL_PVT');
1110 okc_debug.log('2700: Entered validate_small_business_flag', 2);
1111 END IF;
1112
1113 if (P_CPLV_REC.small_business_flag in ('Y','N',OKC_API.G_MISS_CHAR)
1114 or P_CPLV_REC.small_business_flag is NULL) then
1115 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1116 else
1117 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SMALL_BUSINESS_FLAG');
1118 x_return_status := OKC_API.G_RET_STS_ERROR;
1119 end if;
1120
1121 IF (l_debug = 'Y') THEN
1122 okc_debug.log('2750: Leaving validate_small_bussiness_flag', 2);
1123 okc_debug.Reset_Indentation;
1124 END IF;
1125
1126 end validate_small_business_flag;
1127
1128 -- Start of comments
1129 --
1130 -- Procedure Name : validate_women_owned_flag
1131 -- Description :
1132 -- Business Rules :
1133 -- Parameters :
1134 -- Version : 1.0
1135 -- End of comments
1136 procedure validate_women_owned_flag(x_return_status OUT NOCOPY VARCHAR2,
1137 p_cplv_rec IN CPLV_REC_TYPE) is
1138 begin
1139
1140 IF (l_debug = 'Y') THEN
1141 okc_debug.Set_Indentation('OKC_CPL_PVT');
1142 okc_debug.log('2800: Entered validate_women_owned_flag', 2);
1143 END IF;
1144
1145 if (P_CPLV_REC.women_owned_flag in ('Y','N',OKC_API.G_MISS_CHAR)
1146 or P_CPLV_REC.women_owned_flag is NULL) then
1147 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1148 else
1149 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'WOMEN_OWNED_FLAG');
1150 x_return_status := OKC_API.G_RET_STS_ERROR;
1151 end if;
1152
1153 IF (l_debug = 'Y') THEN
1154 okc_debug.log('2850: Leaving validate_women_owned_flag', 2);
1155 okc_debug.Reset_Indentation;
1156 END IF;
1157
1158 end validate_women_owned_flag;
1159
1160
1161 -- Start of comments
1162 --
1163 -- Procedure Name : validate_JTOT_OBJECT1_CODE
1164 -- Description :
1165 -- Business Rules :
1166 -- Parameters :
1167 -- Version : 1.0
1168 -- End of comments
1169
1170 procedure validate_JTOT_OBJECT1_CODE(x_return_status OUT NOCOPY VARCHAR2,
1171 p_cplv_rec IN cplv_rec_TYPE) is
1172 l_dummy_var varchar2(1) := '?';
1173 --
1174 cursor l_object1_csr is
1175 select '!'
1176 from
1177 okc_role_sources RS
1178 ,OKC_K_HEADERS_ALL_B KH -- Modified by jvorugan for Bug:4645341 okc_k_headers_b KH
1179 where
1180 RS.rle_code = p_cplv_rec.rle_code
1181 and RS.jtot_object_code = p_cplv_rec.jtot_object1_code
1182 and sysdate >= RS.start_date
1183 and (RS.end_date is NULL or RS.end_date>=sysdate)
1184 and KH.ID = p_cplv_rec.DNZ_CHR_ID
1185 and RS.BUY_OR_SELL = KH.BUY_OR_SELL
1186 ;
1187 begin
1188
1189 IF (l_debug = 'Y') THEN
1190 okc_debug.Set_Indentation('OKC_CPL_PVT');
1191 okc_debug.log('2900: Entered validate_JTOT_OBJECT1_CODE', 2);
1192 END IF;
1193
1194 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1195 if (p_cplv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR or p_cplv_rec.jtot_object1_code is NULL) then
1196 IF (l_debug = 'Y') THEN
1197 okc_debug.Reset_Indentation;
1198 END IF;
1199 return;
1200 end if;
1201 --
1202 open l_object1_csr;
1203 fetch l_object1_csr into l_dummy_var;
1204 close l_object1_csr;
1205 if (l_dummy_var = '?') then
1206 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'JTOT_OBJECT1_CODE');
1207 x_return_status := OKC_API.G_RET_STS_ERROR;
1208 end if;
1209
1210 IF (l_debug = 'Y') THEN
1211 okc_debug.log('3000: Leaving validate_JTOT_OBJECT1_CODE', 2);
1212 okc_debug.Reset_Indentation;
1213 END IF;
1214
1215 exception
1216 when OTHERS then
1217
1218 IF (l_debug = 'Y') THEN
1219 okc_debug.log('3100: Exiting validate_JTOT_OBJECT1_CODE:OTHERS Exception', 2);
1220 okc_debug.Reset_Indentation;
1221 END IF;
1222
1223 if l_object1_csr%ISOPEN then
1224 close l_object1_csr;
1225 end if;
1226 OKC_API.set_message(p_app_name => g_app_name,
1227 p_msg_name => g_unexpected_error,
1228 p_token1 => g_sqlcode_token,
1229 p_token1_value => sqlcode,
1230 p_token2 => g_sqlerrm_token,
1231 p_token2_value => sqlerrm);
1232 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1233
1234 end validate_JTOT_OBJECT1_CODE;
1235
1236 -- Start of comments
1237 --
1238 -- Procedure Name : validate_object1_id1
1239 -- Description : to be called from validate record
1240 -- Business Rules :
1241 -- Parameters :
1242 -- Version : 1.0
1243 -- End of comments
1244
1245 procedure validate_object1_id1(x_return_status OUT NOCOPY VARCHAR2,
1246 p_cplv_rec IN cplv_rec_TYPE) is
1247 l_dummy_var VARCHAR2(1) := '?';
1248 L_FROM_TABLE VARCHAR2(200);
1249 L_WHERE_CLAUSE VARCHAR2(2000);
1250
1251 cursor l_object1_csr is
1252 select
1253 from_table
1254 ,trim(where_clause) where_clause
1255 from
1256 jtf_objects_vl OB
1257 where
1258 OB.OBJECT_CODE = p_cplv_rec.jtot_object1_code;
1259
1260 e_no_data_found EXCEPTION;
1261 PRAGMA EXCEPTION_INIT(e_no_data_found,100);
1262 e_too_many_rows EXCEPTION;
1263 PRAGMA EXCEPTION_INIT(e_too_many_rows,-1422);
1264 e_source_not_exists EXCEPTION;
1265 PRAGMA EXCEPTION_INIT(e_source_not_exists,-942);
1266 e_source_not_exists1 EXCEPTION;
1267 PRAGMA EXCEPTION_INIT(e_source_not_exists1,-903);
1268 e_column_not_exists EXCEPTION;
1269 PRAGMA EXCEPTION_INIT(e_column_not_exists,-904);
1270
1271
1272 begin
1273
1274 IF (l_debug = 'Y') THEN
1275 okc_debug.Set_Indentation('OKC_CPL_PVT');
1276 okc_debug.log('3200: Entered validate_object1_id1', 2);
1277 END IF;
1278
1279 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1280 if (p_cplv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR or p_cplv_rec.jtot_object1_code is NULL) then
1281 IF (l_debug = 'Y') THEN
1282 okc_debug.log('3300: Leaving validate_object1_id1', 2);
1283 okc_debug.Reset_Indentation;
1284 END IF;
1285 return;
1286 end if;
1287 if (p_cplv_rec.object1_id1 = OKC_API.G_MISS_CHAR or p_cplv_rec.object1_id1 is NULL) then
1288 IF (l_debug = 'Y') THEN
1289 okc_debug.log('3300: Leaving validate_object1_id1', 2);
1290 okc_debug.Reset_Indentation;
1291 END IF;
1292 return;
1293 end if;
1294 open l_object1_csr;
1295 fetch l_object1_csr into l_from_table, l_where_clause;
1296 close l_object1_csr;
1297 if (l_where_clause is not null) then
1298 l_where_clause := ' and '||l_where_clause;
1299 end if;
1300 EXECUTE IMMEDIATE 'select ''x'' from '||l_from_table||
1301 ' where id1=:object1_id1 and id2=:object1_id2'||l_where_clause
1302 into l_dummy_var
1303 USING p_cplv_rec.object1_id1, p_cplv_rec.object1_id2;
1304
1305 IF (l_debug = 'Y') THEN
1306 okc_debug.log('3300: Leaving validate_object1_id1', 2);
1307 okc_debug.Reset_Indentation;
1308 END IF;
1309
1310 exception
1311 when e_source_not_exists then
1312
1313 IF (l_debug = 'Y') THEN
1314 okc_debug.log('3400: Exiting validate_object1_id1:e_source_not_exists Exception', 2);
1315 okc_debug.Reset_Indentation;
1316 END IF;
1317
1318 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'JTOT_OBJECT1_CODE');
1319 x_return_status := OKC_API.G_RET_STS_ERROR;
1320 when e_source_not_exists1 then
1321
1322 IF (l_debug = 'Y') THEN
1323 okc_debug.log('3500: Exiting validate_object1_id1:e_source_not_exists1 Exception', 2);
1324 okc_debug.Reset_Indentation;
1325 END IF;
1326
1327 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'JTOT_OBJECT1_CODE');
1328 x_return_status := OKC_API.G_RET_STS_ERROR;
1329 when e_column_not_exists then
1330
1331 IF (l_debug = 'Y') THEN
1332 okc_debug.log('3600: Exiting validate_object1_id1:e_column_not_exists Exception', 2);
1333 okc_debug.Reset_Indentation;
1334 END IF;
1335
1336 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_from_table||'.ID1');
1337 x_return_status := OKC_API.G_RET_STS_ERROR;
1338 when e_no_data_found then
1339
1340 IF (l_debug = 'Y') THEN
1341 okc_debug.log('3700: Exiting validate_object1_id1:e_no_data_found Exception', 2);
1342 okc_debug.Reset_Indentation;
1343 END IF;
1344
1345 OKC_API.set_message(G_APP_NAME , 'OKC_INVALID_PARTY');
1346 x_return_status := OKC_API.G_RET_STS_ERROR;
1347 when e_too_many_rows then
1348
1349 IF (l_debug = 'Y') THEN
1350 okc_debug.log('3800: Exiting validate_object1_id1:e_too_many_rows Exception', 2);
1351 okc_debug.Reset_Indentation;
1352 END IF;
1353
1354 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_from_table||'.ID1');
1355 x_return_status := OKC_API.G_RET_STS_ERROR;
1356 when OTHERS then
1357
1358 IF (l_debug = 'Y') THEN
1359 okc_debug.log('3900: Exiting validate_object1_id1:OTHERS Exception', 2);
1360 okc_debug.Reset_Indentation;
1361 END IF;
1362
1363 if l_object1_csr%ISOPEN then
1364 close l_object1_csr;
1365 end if;
1366 OKC_API.set_message(p_app_name => g_app_name,
1367 p_msg_name => g_unexpected_error,
1368 p_token1 => g_sqlcode_token,
1369 p_token1_value => sqlcode,
1370 p_token2 => g_sqlerrm_token,
1371 p_token2_value => sqlerrm);
1372 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1373
1374 end validate_object1_id1;
1375
1376 -- Start of comments
1377 --
1378 -- Procedure Name : validate_dnz_chr_id
1379 -- Description :
1380 -- Business Rules :
1381 -- Parameters :
1382 -- Version : 1.0
1383 -- End of comments
1384
1385 procedure validate_dnz_chr_id(x_return_status OUT NOCOPY VARCHAR2,
1386 p_cplv_rec IN CPLV_REC_TYPE) is
1387 l_dummy varchar2(1) := '?';
1388 cursor Kt_Hr_Mj_Vr is
1389 select '!'
1390 from okc_k_headers_all_b -- Modified by Jvorugan for Bug:4645341 okc_k_headers_b
1391 where id = p_cplv_rec.dnz_chr_id;
1392 begin
1393
1394 IF (l_debug = 'Y') THEN
1395 okc_debug.Set_Indentation('OKC_CPL_PVT');
1396 okc_debug.log('4000: Entered validate_dnz_chr_id', 2);
1397 END IF;
1398
1399 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1400 if (p_cplv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) then
1401 IF (l_debug = 'Y') THEN
1402 okc_debug.Reset_Indentation;
1403 END IF;
1404 return;
1405 end if;
1406 if (p_cplv_rec.dnz_chr_id is NULL) then
1407 OKC_API.set_message(p_app_name => g_app_name,
1408 p_msg_name => G_REQUIRED_VALUE,
1409 p_token1 => G_COL_NAME_TOKEN,
1410 p_token1_value => 'DNZ_CHR_ID');
1411 x_return_status := OKC_API.G_RET_STS_ERROR;
1412 IF (l_debug = 'Y') THEN
1413 okc_debug.Reset_Indentation;
1414 END IF;
1415 return;
1416 end if;
1417 open Kt_Hr_Mj_Vr;
1418 fetch Kt_Hr_Mj_Vr into l_dummy;
1419 close Kt_Hr_Mj_Vr;
1420 if (l_dummy='?') then
1421 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DNZ_CHR_ID');
1422 x_return_status := OKC_API.G_RET_STS_ERROR;
1423 IF (l_debug = 'Y') THEN
1424 okc_debug.Reset_Indentation;
1425 END IF;
1426 return;
1427 end if;
1428
1429 IF (l_debug = 'Y') THEN
1430 okc_debug.log('4100: Leaving validate_dnz_chr_id', 2);
1431 okc_debug.Reset_Indentation;
1432 END IF;
1433
1434 exception
1435 when OTHERS then
1436
1437 IF (l_debug = 'Y') THEN
1438 okc_debug.log('4200: Exiting validate_dnz_chr_id:OTHERS Exception', 2);
1439 okc_debug.Reset_Indentation;
1440 END IF;
1441
1442 OKC_API.set_message(p_app_name => g_app_name,
1443 p_msg_name => g_unexpected_error,
1444 p_token1 => g_sqlcode_token,
1445 p_token1_value => sqlcode,
1446 p_token2 => g_sqlerrm_token,
1447 p_token2_value => sqlerrm);
1448 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1449
1450 end validate_dnz_chr_id;
1451
1452 -- Start of comments
1453 --
1454 -- Procedure Name : validate_primary_yn
1455 -- Description :
1456 -- Business Rules :
1457 -- Parameters :
1458 -- Version : 1.0
1459 -- End of comments
1460 PROCEDURE validate_primary_yn(x_return_status OUT NOCOPY VARCHAR2,
1461 p_cplv_rec IN cplv_rec_type) IS
1462
1463 l_dummy varchar2(1) := '?';
1464
1465 CURSOR l_party_csr IS
1466 SELECT '!'
1467 FROM OKC_K_PARTY_ROLES_B
1468 WHERE Id <> NVL(p_cplv_rec.id,-99999)
1469 AND dnz_chr_id = p_cplv_rec.dnz_chr_id
1470 AND cle_id IS NULL
1471 AND primary_yn = 'Y';
1472
1473
1474
1475 Begin
1476 IF (l_debug = 'Y') THEN
1477 okc_debug.Set_Indentation('OKC_CPL_PVT');
1478 okc_debug.log('4150: Entered validate_primary_yn', 2);
1479 END IF;
1480
1481 -- initialize return status
1482 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1483
1484 -- check that data exists
1485 IF (p_cplv_rec.primary_yn IS NOT NULL AND
1486 p_cplv_rec.primary_yn <> OKC_API.G_MISS_CHAR) THEN
1487
1488 IF p_cplv_rec.primary_yn NOT IN ('Y','N') Then
1489 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1490 p_msg_name => g_invalid_value,
1491 p_token1 => g_col_name_token,
1492 p_token1_value => 'PRIMARY_YN');
1493 -- notify caller of an error
1494 x_return_status := OKC_API.G_RET_STS_ERROR;
1495 -- halt validation
1496 raise G_EXCEPTION_HALT_VALIDATION;
1497 END IF;
1498 END IF; -- end of (p_cplv_rec.primary_yn <> OKC_API.G_MISS_CHAR OR ---
1499
1500 IF (p_cplv_rec.primary_yn = 'Y') THEN
1501 OPEN l_party_csr;
1502 FETCH l_party_csr INTO l_dummy;
1503 CLOSE l_party_csr;
1504 IF (l_dummy='!') THEN
1505 OKC_API.set_message(G_APP_NAME,'OKC_PRIMARY_PARTY_ERROR');
1506 x_return_status := OKC_API.G_RET_STS_ERROR;
1507 IF (l_debug = 'Y') THEN
1508 okc_debug.Reset_Indentation;
1509 END IF;
1510 RETURN;
1511 END IF;
1512 END IF; --end of (p_cplv_rec.primary_yn = 'Y')
1513 IF (l_debug = 'Y') THEN
1514 okc_debug.log('4160: Leaving validate_primary_yn', 2);
1515 okc_debug.Reset_Indentation;
1516 END IF;
1517
1518 exception
1519 when G_EXCEPTION_HALT_VALIDATION then
1520 IF (l_debug = 'Y') THEN
1521 okc_debug.log('4170: Exiting validate_primary_yn:G_EXCEPTION_HALT_VALIDATION Exception', 2);
1522 okc_debug.Reset_Indentation;
1523 END IF;
1524
1525 -- no processing necessary; validation can continue with next column
1526 null;
1527 when OTHERS then
1528 IF (l_debug = 'Y') THEN
1529 okc_debug.log('4180: Exiting validate_primary_yn:OTHERS Exception', 2);
1530 okc_debug.Reset_Indentation;
1531 END IF;
1532
1533 -- store SQL error message on message stack
1534 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1535 p_msg_name => g_unexpected_error,
1536 p_token1 => g_sqlcode_token,
1537 p_token1_value => sqlcode,
1538 p_token2 => g_sqlerrm_token,
1539 p_token2_value => sqlerrm);
1540
1541 -- notify caller of an error as UNEXPETED error
1542 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1543
1544 End validate_primary_yn;
1545
1546 /*+++++++++++++End of hand code +++++++++++++++++++*/
1547 -------------------------------------------------
1548 -- Validate_Attributes for:OKC_K_PARTY_ROLES_V --
1549 -------------------------------------------------
1550 FUNCTION Validate_Attributes (
1551 p_cplv_rec IN cplv_rec_type
1552 ) RETURN VARCHAR2 IS
1553 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1554 /*-------------Commented in favor of hand code------
1555 BEGIN
1556
1557 IF (l_debug = 'Y') THEN
1558 okc_debug.Set_Indentation('OKC_CPL_PVT');
1559 okc_debug.log('4300: Entered Validate_Attributes', 2);
1560 END IF;
1561
1562 IF p_cplv_rec.id = OKC_API.G_MISS_NUM OR
1563 p_cplv_rec.id IS NULL
1564 THEN
1565 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
1566 l_return_status := OKC_API.G_RET_STS_ERROR;
1567 ELSIF p_cplv_rec.object_version_number = OKC_API.G_MISS_NUM OR
1568 p_cplv_rec.object_version_number IS NULL
1569 THEN
1570 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
1571 l_return_status := OKC_API.G_RET_STS_ERROR;
1572 ELSIF p_cplv_rec.rle_code = OKC_API.G_MISS_CHAR OR
1573 p_cplv_rec.rle_code IS NULL
1574 THEN
1575 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rle_code');
1576 l_return_status := OKC_API.G_RET_STS_ERROR;
1577 ELSIF p_cplv_rec.dnz_chr_id = OKC_API.G_MISS_NUM OR
1578 p_cplv_rec.dnz_chr_id IS NULL
1579 THEN
1580 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'dnz_chr_id');
1581 l_return_status := OKC_API.G_RET_STS_ERROR;
1582 END IF;
1583
1584 IF (l_debug = 'Y') THEN
1585 okc_debug.log('4100: Leaving validate_Attributes', 2);
1586 okc_debug.Reset_Indentation;
1587 END IF;
1588
1589 RETURN(l_return_status);
1590
1591 END Validate_Attributes;
1592 ---------------End of the commented code-----------*/
1593 /*+++++++++++++Start of hand code +++++++++++++++++*/
1594 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1595 BEGIN
1596 -- call each column-level validation
1597 validate_cpl_id(x_return_status => l_return_status,
1598 p_cplv_rec => p_cplv_rec);
1599 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1600 return OKC_API.G_RET_STS_UNEXP_ERROR;
1601 end if;
1602 if (l_return_status = OKC_API.G_RET_STS_ERROR
1603 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1604 x_return_status := OKC_API.G_RET_STS_ERROR;
1605 end if;
1606 --
1607 validate_chr_id(x_return_status => l_return_status,
1608 p_cplv_rec => p_cplv_rec);
1609 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1610 return OKC_API.G_RET_STS_UNEXP_ERROR;
1611 end if;
1612 if (l_return_status = OKC_API.G_RET_STS_ERROR
1613 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1614 x_return_status := OKC_API.G_RET_STS_ERROR;
1615 end if;
1616 --
1617 validate_cle_id(x_return_status => l_return_status,
1618 p_cplv_rec => p_cplv_rec);
1619 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1620 return OKC_API.G_RET_STS_UNEXP_ERROR;
1621 end if;
1622 if (l_return_status = OKC_API.G_RET_STS_ERROR
1623 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1624 x_return_status := OKC_API.G_RET_STS_ERROR;
1625 end if;
1626 --
1627 /* --> validate record
1628 validate_rle_code(x_return_status => l_return_status,
1629 p_cplv_rec => p_cplv_rec);
1630 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1631 return OKC_API.G_RET_STS_UNEXP_ERROR;
1632 end if;
1633 if (l_return_status = OKC_API.G_RET_STS_ERROR
1634 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1635 x_return_status := OKC_API.G_RET_STS_ERROR;
1636 end if;
1637 */
1638 --
1639 validate_small_business_flag(x_return_status => l_return_status,
1640 p_cplv_rec => p_cplv_rec);
1641 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1642 return OKC_API.G_RET_STS_UNEXP_ERROR;
1643 end if;
1644 if (l_return_status = OKC_API.G_RET_STS_ERROR
1645 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1646 x_return_status := OKC_API.G_RET_STS_ERROR;
1647 end if;
1648 --
1649 validate_women_owned_flag(x_return_status => l_return_status,
1650 p_cplv_rec => p_cplv_rec);
1651 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1652 return OKC_API.G_RET_STS_UNEXP_ERROR;
1653 end if;
1654 if (l_return_status = OKC_API.G_RET_STS_ERROR
1655 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1656 x_return_status := OKC_API.G_RET_STS_ERROR;
1657 end if;
1658 --
1659 validate_dnz_chr_id(x_return_status => l_return_status,
1660 p_cplv_rec => p_cplv_rec);
1661 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1662 return OKC_API.G_RET_STS_UNEXP_ERROR;
1663 end if;
1664 if (l_return_status = OKC_API.G_RET_STS_ERROR
1665 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1666 x_return_status := OKC_API.G_RET_STS_ERROR;
1667 end if;
1668 --
1669 validate_primary_yn(x_return_status => l_return_status,
1670 p_cplv_rec => p_cplv_rec);
1671 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1672 return OKC_API.G_RET_STS_UNEXP_ERROR;
1673 end If;
1674 If (l_return_status = OKC_API.G_RET_STS_ERROR
1675 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1676 x_return_status := OKC_API.G_RET_STS_ERROR;
1677 end If;
1678
1679 return x_return_status;
1680
1681 IF (l_debug = 'Y') THEN
1682 okc_debug.log('4150: Leaving validate_Attributes', 2);
1683 okc_debug.Reset_Indentation;
1684 END IF;
1685
1686 exception
1687 when OTHERS then
1688 -- store SQL error message on message stack for caller
1689 OKC_API.set_message(p_app_name => g_app_name,
1690 p_msg_name => g_unexpected_error,
1691 p_token1 => g_sqlcode_token,
1692 p_token1_value => sqlcode,
1693 p_token2 => g_sqlerrm_token,
1694 p_token2_value => sqlerrm);
1695 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1696 return x_return_status;
1697 END Validate_Attributes;
1698 /*+++++++++++++End of hand code +++++++++++++++++++*/
1699
1700 ---------------------------------------------------------------------------
1701 -- PROCEDURE Validate_Record
1702 ---------------------------------------------------------------------------
1703 ---------------------------------------------
1704 -- Validate_Record for:OKC_K_PARTY_ROLES_V --
1705 ---------------------------------------------
1706 FUNCTION Validate_Record (
1707 p_cplv_rec IN cplv_rec_type
1708 ) RETURN VARCHAR2 IS
1709 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1710 /*+++++++++++++Start of hand code +++++++++++++++++*/
1711 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1712 --+UK
1713 l_unq_tbl OKC_UTIL.unq_tbl_type;
1714
1715 -- indirection
1716 l_buy_or_sell varchar2(3);
1717 l_access_level varchar2(1);
1718
1719 cursor c_buy_or_sell is
1720 select buy_or_sell
1721 from okc_k_headers_all_b -- Modified by Jvorugan for Bug:4645341 okc_k_headers_b
1722 where id = p_cplv_rec.dnz_chr_id;
1723
1724 cursor c_access_level(p_intent varchar2) is
1725 select access_level
1726 from okc_role_sources
1727 where rle_code = p_cplv_rec.rle_code
1728 and buy_or_sell = p_intent;
1729 --
1730
1731
1732 cursor c1(p_chr_id okc_k_party_roles_b.chr_id%TYPE,
1733 p_rle_code okc_k_party_roles_b.rle_code%TYPE,
1734 p_jtot_object1_code okc_k_party_roles_b.jtot_object1_code%TYPE,
1735 p_object1_id1 okc_k_party_roles_b.object1_id1%TYPE,
1736 p_object1_id2 okc_k_party_roles_b.object1_id2%TYPE) is
1737 select id
1738 from okc_k_party_roles_b
1739 where chr_id = p_chr_id
1740 and rle_code = p_rle_code
1741 and jtot_object1_code = p_jtot_object1_code
1742 and object1_id1 = p_object1_id1
1743 and object1_id2 = p_object1_id2;
1744
1745 cursor c2(p_cle_id okc_k_party_roles_b.cle_id%TYPE,
1746 p_rle_code okc_k_party_roles_b.rle_code%TYPE,
1747 p_jtot_object1_code okc_k_party_roles_b.jtot_object1_code%TYPE,
1748 p_object1_id1 okc_k_party_roles_b.object1_id1%TYPE,
1749 p_object1_id2 okc_k_party_roles_b.object1_id2%TYPE) is
1750 select id
1751 from okc_k_party_roles_b
1752 where cle_id = p_cle_id
1753 and rle_code = p_rle_code
1754 and jtot_object1_code = p_jtot_object1_code
1755 and object1_id1 = p_object1_id1
1756 and object1_id2 = p_object1_id2;
1757
1758 l_id Number;
1759 l_row_found Boolean := False;
1760 --+UK
1761 BEGIN
1762 IF (l_debug = 'Y') THEN
1763 okc_debug.Set_Indentation('OKC_CPL_PVT');
1764 okc_debug.log('5000: Entered Validate_Record', 2);
1765 END IF;
1766 if (p_cplv_rec.chr_id IS NULL and p_cplv_rec.cle_id IS NULL) then
1767 OKC_API.set_message(g_app_name,g_required_value,g_col_name_token,'CHR_ID, CLE_ID');
1768 l_return_status := OKC_API.G_RET_STS_ERROR;
1769 end if;
1770 if ((p_cplv_rec.chr_id IS NOT NULL and p_cplv_rec.chr_id <> OKC_API.G_MISS_NUM) and
1771 (p_cplv_rec.cle_id IS NOT NULL and p_cplv_rec.cle_id <> OKC_API.G_MISS_NUM)) then
1772 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CHR_ID, CLE_ID');
1773 l_return_status := OKC_API.G_RET_STS_ERROR;
1774 end if;
1775 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1776 return OKC_API.G_RET_STS_UNEXP_ERROR;
1777 end if;
1778 if (l_return_status = OKC_API.G_RET_STS_ERROR
1779 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1780 x_return_status := OKC_API.G_RET_STS_ERROR;
1781 end if;
1782 --
1783 validate_rle_code(x_return_status => l_return_status,
1784 p_cplv_rec => p_cplv_rec);
1785 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1786 return OKC_API.G_RET_STS_UNEXP_ERROR;
1787 end if;
1788 if (l_return_status = OKC_API.G_RET_STS_ERROR
1789 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1790 x_return_status := OKC_API.G_RET_STS_ERROR;
1791 end if;
1792 --
1793 -- indirection
1794 Open c_buy_or_sell;
1795 Fetch c_buy_or_sell Into l_buy_or_sell;
1796 Close c_buy_or_sell;
1797
1798 Open c_access_level(l_buy_or_sell);
1799 Fetch c_access_level Into l_access_level;
1800 Close c_access_level;
1801
1802 If l_access_level = 'U' Then -- if user defined
1803
1804 validate_JTOT_OBJECT1_CODE(x_return_status => l_return_status,
1805 p_cplv_rec => p_cplv_rec);
1806 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1807 IF (l_debug = 'Y') THEN
1808 okc_debug.log('5020: Exiting Validate_jtot_object1_code in validate_record:unexp err', 2);
1809 okc_debug.Reset_Indentation;
1810 END IF;
1811 return OKC_API.G_RET_STS_UNEXP_ERROR;
1812 end if;
1813 if (l_return_status = OKC_API.G_RET_STS_ERROR
1814 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1815 x_return_status := OKC_API.G_RET_STS_ERROR;
1816 end if;
1817 --
1818 validate_object1_id1(x_return_status => l_return_status,
1819 p_cplv_rec => p_cplv_rec);
1820 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1821 IF (l_debug = 'Y') THEN
1822 okc_debug.log('5030: Exiting Validate_object1_id1 in validate_record:unexp err', 2);
1823 okc_debug.Reset_Indentation;
1824 END IF;
1825 return OKC_API.G_RET_STS_UNEXP_ERROR;
1826 end if;
1827 if (l_return_status = OKC_API.G_RET_STS_ERROR
1828 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1829 x_return_status := OKC_API.G_RET_STS_ERROR;
1830 end if;
1831 End If; -- if user defined
1832 --
1833 --+UK
1834 If p_cplv_rec.chr_id IS Not Null and p_cplv_rec.chr_id <> OKC_API.G_MISS_NUM Then
1835 Open c1(p_cplv_rec.chr_id,
1836 p_cplv_rec.rle_code,
1837 p_cplv_rec.jtot_object1_code,
1838 p_cplv_rec.object1_id1,
1839 p_cplv_rec.object1_id2);
1840 Fetch c1 into l_id;
1841 l_row_found := c1%FOUND;
1842 Close c1;
1843 Else
1844 Open c2(p_cplv_rec.cle_id,
1845 p_cplv_rec.rle_code,
1846 p_cplv_rec.jtot_object1_code,
1847 p_cplv_rec.object1_id1,
1848 p_cplv_rec.object1_id2);
1849 Fetch c2 into l_id;
1850 l_row_found := c2%FOUND;
1851 Close c2;
1852 End If;
1853 If l_row_found Then
1854 If l_id <> p_cplv_rec.id Then
1855 OKC_API.set_message(G_APP_NAME, 'OKC_DUPLICATE_PARTY');
1856 x_return_status := OKC_API.G_RET_STS_ERROR;
1857 End If;
1858 End If;
1859
1860 -- Bug 1631244 : The following code commented out since it was not using bind
1861 -- variables and parsing was taking place for every party added. Replaced with
1862 -- explicit cursor as above.
1863 /* l_unq_tbl(1).p_col_name := 'RLE_CODE';
1864 l_unq_tbl(1).p_col_val := p_cplv_rec.RLE_CODE;
1865 l_unq_tbl(2).p_col_name := 'CHR_ID';
1866 l_unq_tbl(2).p_col_val := p_cplv_rec.CHR_ID;
1867 l_unq_tbl(3).p_col_name := 'JTOT_OBJECT1_CODE';
1868 l_unq_tbl(3).p_col_val := p_cplv_rec.JTOT_OBJECT1_CODE;
1869 l_unq_tbl(4).p_col_name := 'OBJECT1_ID1';
1870 l_unq_tbl(4).p_col_val := p_cplv_rec.OBJECT1_ID1;
1871 l_unq_tbl(5).p_col_name := 'OBJECT1_ID2';
1872 l_unq_tbl(5).p_col_val := p_cplv_rec.OBJECT1_ID2;
1873 l_unq_tbl(6).p_col_name := 'CLE_ID';
1874 l_unq_tbl(6).p_col_val := p_cplv_rec.CLE_ID;
1875 OKC_UTIL.Check_Comp_Unique(
1876 p_view_name => 'OKC_K_PARTY_ROLES_V',
1877 p_col_tbl => l_unq_tbl,
1878 p_id => p_cplv_rec.ID,
1879 x_return_status => l_return_status);
1880 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1881 return OKC_API.G_RET_STS_UNEXP_ERROR;
1882 end if;
1883 if (l_return_status = OKC_API.G_RET_STS_ERROR
1884 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1885 x_return_status := OKC_API.G_RET_STS_ERROR;
1886 end if; */
1887 --+UK
1888 IF (l_debug = 'Y') THEN
1889 okc_debug.log('5100: Leaving Validate_Record', 2);
1890 okc_debug.Reset_Indentation;
1891 END IF;
1892 return x_return_status;
1893 exception
1894 when OTHERS then
1895 -- store SQL error message on message stack for caller
1896 OKC_API.set_message(p_app_name => g_app_name,
1897 p_msg_name => g_unexpected_error,
1898 p_token1 => g_sqlcode_token,
1899 p_token1_value => sqlcode,
1900 p_token2 => g_sqlerrm_token,
1901 p_token2_value => sqlerrm);
1902 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1903 return x_return_status;
1904 END Validate_Record;
1905 /*+++++++++++++End of hand code +++++++++++++++++*/
1906
1907 ---------------------------------------------------------------------------
1908 -- PROCEDURE Migrate
1909 ---------------------------------------------------------------------------
1910 PROCEDURE migrate (
1911 p_from IN cplv_rec_type,
1912 p_to IN OUT NOCOPY cpl_rec_type
1913 ) IS
1914 BEGIN
1915
1916 p_to.id := p_from.id;
1917 p_to.cpl_id := p_from.cpl_id;
1918 p_to.chr_id := p_from.chr_id;
1919 p_to.cle_id := p_from.cle_id;
1920 p_to.dnz_chr_id := p_from.dnz_chr_id;
1921 p_to.rle_code := p_from.rle_code;
1922 p_to.object1_id1 := p_from.object1_id1;
1923 p_to.object1_id2 := p_from.object1_id2;
1924 p_to.JTOT_OBJECT1_CODE := p_from.JTOT_OBJECT1_CODE;
1925 p_to.object_version_number := p_from.object_version_number;
1926 p_to.created_by := p_from.created_by;
1927 p_to.creation_date := p_from.creation_date;
1928 p_to.last_updated_by := p_from.last_updated_by;
1929 p_to.last_update_date := p_from.last_update_date;
1930 p_to.code := p_from.code;
1931 p_to.facility := p_from.facility;
1932 p_to.minority_group_lookup_code := p_from.minority_group_lookup_code;
1933 p_to.small_business_flag := p_from.small_business_flag;
1934 p_to.women_owned_flag := p_from.women_owned_flag;
1935 p_to.last_update_login := p_from.last_update_login;
1936 p_to.attribute_category := p_from.attribute_category;
1937 p_to.primary_yn := p_from.primary_yn;
1938 p_to.cust_acct_id := p_from.cust_acct_id;
1939 p_to.bill_to_site_use_id:= p_from.bill_to_site_use_id;
1940 p_to.attribute1 := p_from.attribute1;
1941 p_to.attribute2 := p_from.attribute2;
1942 p_to.attribute3 := p_from.attribute3;
1943 p_to.attribute4 := p_from.attribute4;
1944 p_to.attribute5 := p_from.attribute5;
1945 p_to.attribute6 := p_from.attribute6;
1946 p_to.attribute7 := p_from.attribute7;
1947 p_to.attribute8 := p_from.attribute8;
1948 p_to.attribute9 := p_from.attribute9;
1949 p_to.attribute10 := p_from.attribute10;
1950 p_to.attribute11 := p_from.attribute11;
1951 p_to.attribute12 := p_from.attribute12;
1952 p_to.attribute13 := p_from.attribute13;
1953 p_to.attribute14 := p_from.attribute14;
1954 p_to.attribute15 := p_from.attribute15;
1955
1956 END migrate;
1957
1958 PROCEDURE migrate (
1959 p_from IN cpl_rec_type,
1960 p_to IN OUT NOCOPY cplv_rec_type
1961 ) IS
1962 BEGIN
1963
1964 p_to.id := p_from.id;
1965 p_to.cpl_id := p_from.cpl_id;
1966 p_to.chr_id := p_from.chr_id;
1967 p_to.cle_id := p_from.cle_id;
1968 p_to.dnz_chr_id := p_from.dnz_chr_id;
1969 p_to.rle_code := p_from.rle_code;
1970 p_to.object1_id1 := p_from.object1_id1;
1971 p_to.object1_id2 := p_from.object1_id2;
1972 p_to.JTOT_OBJECT1_CODE := p_from.JTOT_OBJECT1_CODE;
1973 p_to.object_version_number := p_from.object_version_number;
1974 p_to.created_by := p_from.created_by;
1975 p_to.creation_date := p_from.creation_date;
1976 p_to.last_updated_by := p_from.last_updated_by;
1977 p_to.last_update_date := p_from.last_update_date;
1978 p_to.code := p_from.code;
1979 p_to.facility := p_from.facility;
1980 p_to.minority_group_lookup_code := p_from.minority_group_lookup_code;
1981 p_to.small_business_flag := p_from.small_business_flag;
1982 p_to.women_owned_flag := p_from.women_owned_flag;
1983 p_to.last_update_login := p_from.last_update_login;
1984 p_to.primary_yn := p_from.primary_yn;
1985 p_to.cust_acct_id := p_from.cust_acct_id;
1986 p_to.bill_to_site_use_id := p_from.bill_to_site_use_id;
1987 p_to.attribute_category := p_from.attribute_category;
1988 p_to.attribute1 := p_from.attribute1;
1989 p_to.attribute2 := p_from.attribute2;
1990 p_to.attribute3 := p_from.attribute3;
1991 p_to.attribute4 := p_from.attribute4;
1992 p_to.attribute5 := p_from.attribute5;
1993 p_to.attribute6 := p_from.attribute6;
1994 p_to.attribute7 := p_from.attribute7;
1995 p_to.attribute8 := p_from.attribute8;
1996 p_to.attribute9 := p_from.attribute9;
1997 p_to.attribute10 := p_from.attribute10;
1998 p_to.attribute11 := p_from.attribute11;
1999 p_to.attribute12 := p_from.attribute12;
2000 p_to.attribute13 := p_from.attribute13;
2001 p_to.attribute14 := p_from.attribute14;
2002 p_to.attribute15 := p_from.attribute15;
2003
2004 END migrate;
2005
2006 PROCEDURE migrate (
2007 p_from IN cplv_rec_type,
2008 p_to IN OUT NOCOPY okc_k_party_roles_tl_rec_type
2009 ) IS
2010 BEGIN
2011
2012 p_to.id := p_from.id;
2013 p_to.sfwt_flag := p_from.sfwt_flag;
2014 p_to.created_by := p_from.created_by;
2015 p_to.creation_date := p_from.creation_date;
2016 p_to.last_updated_by := p_from.last_updated_by;
2017 p_to.last_update_date := p_from.last_update_date;
2018 p_to.cognomen := p_from.cognomen;
2019 p_to.alias := p_from.alias;
2020 p_to.last_update_login := p_from.last_update_login;
2021
2022 END migrate;
2023
2024 PROCEDURE migrate (
2025 p_from IN okc_k_party_roles_tl_rec_type,
2026 p_to IN OUT NOCOPY cplv_rec_type
2027 ) IS
2028 BEGIN
2029
2030 p_to.id := p_from.id;
2031 p_to.sfwt_flag := p_from.sfwt_flag;
2032 p_to.created_by := p_from.created_by;
2033 p_to.creation_date := p_from.creation_date;
2034 p_to.last_updated_by := p_from.last_updated_by;
2035 p_to.last_update_date := p_from.last_update_date;
2036 p_to.cognomen := p_from.cognomen;
2037 p_to.alias := p_from.alias;
2038 p_to.last_update_login := p_from.last_update_login;
2039
2040 END migrate;
2041
2042 ---------------------------------------------------------------------------
2043 -- PROCEDURE validate_row
2044 ---------------------------------------------------------------------------
2045 ------------------------------------------
2046 -- validate_row for:OKC_K_PARTY_ROLES_V --
2047 ------------------------------------------
2048 PROCEDURE validate_row(
2049 p_api_version IN NUMBER,
2050 p_init_msg_list IN VARCHAR2 ,
2051 x_return_status OUT NOCOPY VARCHAR2,
2052 x_msg_count OUT NOCOPY NUMBER,
2053 x_msg_data OUT NOCOPY VARCHAR2,
2054 p_cplv_rec IN cplv_rec_type) IS
2055
2056 l_api_version CONSTANT NUMBER := 1;
2057 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2058 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2059 l_cplv_rec cplv_rec_type := p_cplv_rec;
2060 l_cpl_rec cpl_rec_type;
2061 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
2062 BEGIN
2063
2064 IF (l_debug = 'Y') THEN
2065 okc_debug.Set_Indentation('OKC_CPL_PVT');
2066 okc_debug.log('5200: Entered validate_row', 2);
2067 END IF;
2068
2069 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2070 G_PKG_NAME,
2071 p_init_msg_list,
2072 l_api_version,
2073 p_api_version,
2074 '_PVT',
2075 x_return_status);
2076 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2077 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2078 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2079 RAISE OKC_API.G_EXCEPTION_ERROR;
2080 END IF;
2081 --- Validate all non-missing attributes (Item Level Validation)
2082 l_return_status := Validate_Attributes(l_cplv_rec);
2083 --- If any errors happen abort API
2084 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2085 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2086 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2087 RAISE OKC_API.G_EXCEPTION_ERROR;
2088 END IF;
2089 l_return_status := Validate_Record(l_cplv_rec);
2090 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2091 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2092 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2093 RAISE OKC_API.G_EXCEPTION_ERROR;
2094 END IF;
2095 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2096
2097 IF (l_debug = 'Y') THEN
2098 okc_debug.log('5300: Leaving validate_row', 2);
2099 okc_debug.Reset_Indentation;
2100 END IF;
2101
2102 EXCEPTION
2103 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2104
2105 IF (l_debug = 'Y') THEN
2106 okc_debug.log('5400: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2107 okc_debug.Reset_Indentation;
2108 END IF;
2109
2110 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2111 (
2112 l_api_name,
2113 G_PKG_NAME,
2114 'OKC_API.G_RET_STS_ERROR',
2115 x_msg_count,
2116 x_msg_data,
2117 '_PVT'
2118 );
2119 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2120
2121 IF (l_debug = 'Y') THEN
2122 okc_debug.log('5500: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2123 okc_debug.Reset_Indentation;
2124 END IF;
2125
2126 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2127 (
2128 l_api_name,
2129 G_PKG_NAME,
2130 'OKC_API.G_RET_STS_UNEXP_ERROR',
2131 x_msg_count,
2132 x_msg_data,
2133 '_PVT'
2134 );
2135 WHEN OTHERS THEN
2136
2137 IF (l_debug = 'Y') THEN
2138 okc_debug.log('5600: Exiting validate_row:OTHERS Exception', 2);
2139 okc_debug.Reset_Indentation;
2140 END IF;
2141
2142 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2143 (
2144 l_api_name,
2145 G_PKG_NAME,
2146 'OTHERS',
2147 x_msg_count,
2148 x_msg_data,
2149 '_PVT'
2150 );
2151
2152 END validate_row;
2153 ------------------------------------------
2154 -- PL/SQL TBL validate_row for:CPLV_TBL --
2155 ------------------------------------------
2156 PROCEDURE validate_row(
2157 p_api_version IN NUMBER,
2158 p_init_msg_list IN VARCHAR2 ,
2159 x_return_status OUT NOCOPY VARCHAR2,
2160 x_msg_count OUT NOCOPY NUMBER,
2161 x_msg_data OUT NOCOPY VARCHAR2,
2162 p_cplv_tbl IN cplv_tbl_type) IS
2163
2164 l_api_version CONSTANT NUMBER := 1;
2165 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2166 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2167 i NUMBER := 0;
2168 BEGIN
2169
2170 IF (l_debug = 'Y') THEN
2171 okc_debug.Set_Indentation('OKC_CPL_PVT');
2172 okc_debug.log('5700: Entered validate_row', 2);
2173 END IF;
2174
2175 OKC_API.init_msg_list(p_init_msg_list);
2176 -- Make sure PL/SQL table has records in it before passing
2177 IF (p_cplv_tbl.COUNT > 0) THEN
2178 i := p_cplv_tbl.FIRST;
2179 LOOP
2180 validate_row (
2181 p_api_version => p_api_version,
2182 p_init_msg_list => OKC_API.G_FALSE,
2183 x_return_status => x_return_status,
2184 x_msg_count => x_msg_count,
2185 x_msg_data => x_msg_data,
2186 p_cplv_rec => p_cplv_tbl(i));
2187 EXIT WHEN (i = p_cplv_tbl.LAST);
2188 i := p_cplv_tbl.NEXT(i);
2189 END LOOP;
2190 END IF;
2191
2192 IF (l_debug = 'Y') THEN
2193 okc_debug.log('5800: Leaving validate_row', 2);
2194 okc_debug.Reset_Indentation;
2195 END IF;
2196
2197 EXCEPTION
2198 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2199
2200 IF (l_debug = 'Y') THEN
2201 okc_debug.log('5900: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2202 okc_debug.Reset_Indentation;
2203 END IF;
2204
2205 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2206 (
2207 l_api_name,
2208 G_PKG_NAME,
2209 'OKC_API.G_RET_STS_ERROR',
2210 x_msg_count,
2211 x_msg_data,
2212 '_PVT'
2213 );
2214 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2215
2216 IF (l_debug = 'Y') THEN
2217 okc_debug.log('6000: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2218 okc_debug.Reset_Indentation;
2219 END IF;
2220
2221 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2222 (
2223 l_api_name,
2224 G_PKG_NAME,
2225 'OKC_API.G_RET_STS_UNEXP_ERROR',
2226 x_msg_count,
2227 x_msg_data,
2228 '_PVT'
2229 );
2230 WHEN OTHERS THEN
2231
2232 IF (l_debug = 'Y') THEN
2233 okc_debug.log('6100: Exiting validate_row:OTHERS Exception', 2);
2234 okc_debug.Reset_Indentation;
2235 END IF;
2236
2237 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2238 (
2239 l_api_name,
2240 G_PKG_NAME,
2241 'OTHERS',
2242 x_msg_count,
2243 x_msg_data,
2244 '_PVT'
2245 );
2246
2247 END validate_row;
2248
2249 ---------------------------------------------------------------------------
2250 -- PROCEDURE insert_row
2251 ---------------------------------------------------------------------------
2252 ----------------------------------------
2253 -- insert_row for:OKC_K_PARTY_ROLES_B --
2254 ----------------------------------------
2255 PROCEDURE insert_row(
2256 p_init_msg_list IN VARCHAR2 ,
2257 x_return_status OUT NOCOPY VARCHAR2,
2258 x_msg_count OUT NOCOPY NUMBER,
2259 x_msg_data OUT NOCOPY VARCHAR2,
2260 p_cpl_rec IN cpl_rec_type,
2261 x_cpl_rec OUT NOCOPY cpl_rec_type) IS
2262
2263 l_api_version CONSTANT NUMBER := 1;
2264 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2265 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2266 l_cpl_rec cpl_rec_type := p_cpl_rec;
2267 l_def_cpl_rec cpl_rec_type;
2268 --------------------------------------------
2269 -- Set_Attributes for:OKC_K_PARTY_ROLES_B --
2270 --------------------------------------------
2271 FUNCTION Set_Attributes (
2272 p_cpl_rec IN cpl_rec_type,
2273 x_cpl_rec OUT NOCOPY cpl_rec_type
2274 ) RETURN VARCHAR2 IS
2275 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2276 l_application_id number; /*bug6911126*/
2277 BEGIN
2278
2279 x_cpl_rec := p_cpl_rec;
2280
2281 /*changes made for bug6911126*/
2282 SELECT application_id
2283 INTO l_application_id
2284 FROM OKC_K_HEADERS_ALL_B
2285 WHERE id = x_cpl_rec.dnz_chr_id;
2286
2287 IF l_application_id = 540 THEN
2288 IF x_cpl_rec.orig_system_id1 = OKC_API.G_MISS_NUM THEN
2289 x_cpl_rec.orig_system_id1 := NULL;
2290 END IF;
2291 IF x_cpl_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR THEN
2292 x_cpl_rec.orig_system_reference1 := NULL;
2293 END IF;
2294 IF x_cpl_rec.orig_system_source_code = OKC_API.G_MISS_CHAR THEN
2295 x_cpl_rec.orig_system_source_code := NULL;
2296 END IF;
2297 END IF;
2298 /*end of changes for bug6911126*/
2299
2300 x_cpl_rec.primary_yn := UPPER(x_cpl_rec.PRIMARY_YN);
2301 RETURN(l_return_status);
2302
2303 END Set_Attributes;
2304 BEGIN
2305
2306 IF (l_debug = 'Y') THEN
2307 okc_debug.Set_Indentation('OKC_CPL_PVT');
2308 okc_debug.log('6300: Entered insert_row', 2);
2309 END IF;
2310
2311 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2312 p_init_msg_list,
2313 '_PVT',
2314 x_return_status);
2315 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2316 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2317 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2318 RAISE OKC_API.G_EXCEPTION_ERROR;
2319 END IF;
2320 --- Setting item attributes
2321 l_return_status := Set_Attributes(
2322 p_cpl_rec, -- IN
2323 l_cpl_rec); -- OUT
2324 --- If any errors happen abort API
2325 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2327 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2328 RAISE OKC_API.G_EXCEPTION_ERROR;
2329 END IF;
2330 INSERT INTO OKC_K_PARTY_ROLES_B(
2331 id,
2332 cpl_id,
2333 chr_id,
2334 cle_id,
2335 dnz_chr_id,
2336 rle_code,
2337 object1_id1,
2338 object1_id2,
2339 JTOT_OBJECT1_CODE,
2340 object_version_number,
2341 created_by,
2342 creation_date,
2343 last_updated_by,
2344 last_update_date,
2345 code,
2346 facility,
2347 minority_group_lookup_code,
2348 small_business_flag,
2349 women_owned_flag,
2350 last_update_login,
2351 primary_yn,
2352 cust_acct_id,
2353 bill_to_site_use_id,
2354 attribute_category,
2355 attribute1,
2356 attribute2,
2357 attribute3,
2358 attribute4,
2359 attribute5,
2360 attribute6,
2361 attribute7,
2362 attribute8,
2363 attribute9,
2364 attribute10,
2365 attribute11,
2366 attribute12,
2367 attribute13,
2368 attribute14,
2369 attribute15,
2370 -- R12 Data Model Changes 4485150 Start
2371 orig_system_id1,
2372 orig_system_reference1,
2373 orig_system_source_code
2374 -- R12 Data Model Changes 4485150 End
2375 )
2376 VALUES (
2377 l_cpl_rec.id,
2378 l_cpl_rec.cpl_id,
2379 l_cpl_rec.chr_id,
2380 l_cpl_rec.cle_id,
2381 l_cpl_rec.dnz_chr_id,
2382 l_cpl_rec.rle_code,
2383 l_cpl_rec.object1_id1,
2384 l_cpl_rec.object1_id2,
2385 l_cpl_rec.JTOT_OBJECT1_CODE,
2386 l_cpl_rec.object_version_number,
2387 l_cpl_rec.created_by,
2388 l_cpl_rec.creation_date,
2389 l_cpl_rec.last_updated_by,
2390 l_cpl_rec.last_update_date,
2391 l_cpl_rec.code,
2392 l_cpl_rec.facility,
2393 l_cpl_rec.minority_group_lookup_code,
2394 l_cpl_rec.small_business_flag,
2395 l_cpl_rec.women_owned_flag,
2396 l_cpl_rec.last_update_login,
2397 l_cpl_rec.primary_yn,
2398 l_cpl_rec.cust_acct_id,
2399 l_cpl_rec.bill_to_site_use_id,
2400 l_cpl_rec.attribute_category,
2401 l_cpl_rec.attribute1,
2402 l_cpl_rec.attribute2,
2403 l_cpl_rec.attribute3,
2404 l_cpl_rec.attribute4,
2405 l_cpl_rec.attribute5,
2406 l_cpl_rec.attribute6,
2407 l_cpl_rec.attribute7,
2408 l_cpl_rec.attribute8,
2409 l_cpl_rec.attribute9,
2410 l_cpl_rec.attribute10,
2411 l_cpl_rec.attribute11,
2412 l_cpl_rec.attribute12,
2413 l_cpl_rec.attribute13,
2414 l_cpl_rec.attribute14,
2415 l_cpl_rec.attribute15,
2416 -- R12 Data Model Changes 4485150 Start
2417 l_cpl_rec.orig_system_id1,
2418 l_cpl_rec.orig_system_reference1,
2419 l_cpl_rec.orig_system_source_code
2420 -- R12 Data Model Changes 4485150 End
2421 );
2422 -- Set OUT values
2423 x_cpl_rec := l_cpl_rec;
2424 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2425
2426 IF (l_debug = 'Y') THEN
2427 okc_debug.log('6400: Leaving insert_row', 2);
2428 okc_debug.Reset_Indentation;
2429 END IF;
2430
2431 EXCEPTION
2432 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2433
2434 IF (l_debug = 'Y') THEN
2435 okc_debug.log('6500: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2436 okc_debug.Reset_Indentation;
2437 END IF;
2438
2439 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2440 (
2441 l_api_name,
2442 G_PKG_NAME,
2443 'OKC_API.G_RET_STS_ERROR',
2444 x_msg_count,
2445 x_msg_data,
2446 '_PVT'
2447 );
2448 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2449
2450 IF (l_debug = 'Y') THEN
2451 okc_debug.log('6600: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2452 okc_debug.Reset_Indentation;
2453 END IF;
2454
2455 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2456 (
2457 l_api_name,
2458 G_PKG_NAME,
2459 'OKC_API.G_RET_STS_UNEXP_ERROR',
2460 x_msg_count,
2461 x_msg_data,
2462 '_PVT'
2463 );
2464 WHEN OTHERS THEN
2465
2466 IF (l_debug = 'Y') THEN
2467 okc_debug.log('6700: Exiting insert_row:OTHERS Exception', 2);
2468 okc_debug.Reset_Indentation;
2469 END IF;
2470
2471 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2472 (
2473 l_api_name,
2474 G_PKG_NAME,
2475 'OTHERS',
2476 x_msg_count,
2477 x_msg_data,
2478 '_PVT'
2479 );
2480
2481 END insert_row;
2482 -----------------------------------------
2483 -- insert_row for:OKC_K_PARTY_ROLES_TL --
2484 -----------------------------------------
2485 PROCEDURE insert_row(
2486 p_init_msg_list IN VARCHAR2 ,
2487 x_return_status OUT NOCOPY VARCHAR2,
2488 x_msg_count OUT NOCOPY NUMBER,
2489 x_msg_data OUT NOCOPY VARCHAR2,
2490 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type,
2491 x_okc_k_party_roles_tl_rec OUT NOCOPY okc_k_party_roles_tl_rec_type) IS
2492
2493 l_api_version CONSTANT NUMBER := 1;
2494 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
2495 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2496 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type := p_okc_k_party_roles_tl_rec;
2497 ldefokckpartyrolestlrec okc_k_party_roles_tl_rec_type;
2498 CURSOR get_languages IS
2499 SELECT *
2500 FROM FND_LANGUAGES
2501 WHERE INSTALLED_FLAG IN ('I', 'B');
2502 ---------------------------------------------
2503 -- Set_Attributes for:OKC_K_PARTY_ROLES_TL --
2504 ---------------------------------------------
2505 FUNCTION Set_Attributes (
2506 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type,
2507 x_okc_k_party_roles_tl_rec OUT NOCOPY okc_k_party_roles_tl_rec_type
2508 ) RETURN VARCHAR2 IS
2509 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2510 BEGIN
2511
2512 x_okc_k_party_roles_tl_rec := p_okc_k_party_roles_tl_rec;
2513 x_okc_k_party_roles_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
2514 x_okc_k_party_roles_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
2515 RETURN(l_return_status);
2516
2517 END Set_Attributes;
2518 BEGIN
2519
2520 IF (l_debug = 'Y') THEN
2521 okc_debug.Set_Indentation('OKC_CPL_PVT');
2522 okc_debug.log('6900: Entered insert_row', 2);
2523 END IF;
2524
2525 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2526 p_init_msg_list,
2527 '_PVT',
2528 x_return_status);
2529 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2530 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2531 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2532 RAISE OKC_API.G_EXCEPTION_ERROR;
2533 END IF;
2534 --- Setting item attributes
2535 l_return_status := Set_Attributes(
2536 p_okc_k_party_roles_tl_rec, -- IN
2537 l_okc_k_party_roles_tl_rec); -- OUT
2538 --- If any errors happen abort API
2539 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2540 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2541 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2542 RAISE OKC_API.G_EXCEPTION_ERROR;
2543 END IF;
2544 FOR l_lang_rec IN get_languages LOOP
2545 l_okc_k_party_roles_tl_rec.language := l_lang_rec.language_code;
2546 INSERT INTO OKC_K_PARTY_ROLES_TL(
2547 id,
2548 language,
2549 source_lang,
2550 sfwt_flag,
2551 created_by,
2552 creation_date,
2553 last_updated_by,
2554 last_update_date,
2555 cognomen,
2556 alias,
2557 last_update_login)
2558 VALUES (
2559 l_okc_k_party_roles_tl_rec.id,
2560 l_okc_k_party_roles_tl_rec.language,
2561 l_okc_k_party_roles_tl_rec.source_lang,
2562 l_okc_k_party_roles_tl_rec.sfwt_flag,
2563 l_okc_k_party_roles_tl_rec.created_by,
2564 l_okc_k_party_roles_tl_rec.creation_date,
2565 l_okc_k_party_roles_tl_rec.last_updated_by,
2566 l_okc_k_party_roles_tl_rec.last_update_date,
2567 l_okc_k_party_roles_tl_rec.cognomen,
2568 l_okc_k_party_roles_tl_rec.alias,
2569 l_okc_k_party_roles_tl_rec.last_update_login);
2570 END LOOP;
2571 -- Set OUT values
2572 x_okc_k_party_roles_tl_rec := l_okc_k_party_roles_tl_rec;
2573 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2574
2575 IF (l_debug = 'Y') THEN
2576 okc_debug.log('7000: Leaving insert_row', 2);
2577 okc_debug.Reset_Indentation;
2578 END IF;
2579
2580 EXCEPTION
2581 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2582
2583 IF (l_debug = 'Y') THEN
2584 okc_debug.log('7100: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2585 okc_debug.Reset_Indentation;
2586 END IF;
2587
2588 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2589 (
2590 l_api_name,
2591 G_PKG_NAME,
2592 'OKC_API.G_RET_STS_ERROR',
2593 x_msg_count,
2594 x_msg_data,
2595 '_PVT'
2596 );
2597 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2598
2599 IF (l_debug = 'Y') THEN
2600 okc_debug.log('7200: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2601 okc_debug.Reset_Indentation;
2602 END IF;
2603
2604 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2605 (
2606 l_api_name,
2607 G_PKG_NAME,
2608 'OKC_API.G_RET_STS_UNEXP_ERROR',
2609 x_msg_count,
2610 x_msg_data,
2611 '_PVT'
2612 );
2613 WHEN OTHERS THEN
2614
2615 IF (l_debug = 'Y') THEN
2616 okc_debug.log('7300: Exiting insert_row:OTHERS Exception', 2);
2617 okc_debug.Reset_Indentation;
2618 END IF;
2619
2620 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2621 (
2622 l_api_name,
2623 G_PKG_NAME,
2624 'OTHERS',
2625 x_msg_count,
2626 x_msg_data,
2627 '_PVT'
2628 );
2629
2630 END insert_row;
2631 ----------------------------------------
2632 -- insert_row for:OKC_K_PARTY_ROLES_V --
2633 ----------------------------------------
2634 PROCEDURE insert_row(
2635 p_api_version IN NUMBER,
2636 p_init_msg_list IN VARCHAR2 ,
2637 x_return_status OUT NOCOPY VARCHAR2,
2638 x_msg_count OUT NOCOPY NUMBER,
2639 x_msg_data OUT NOCOPY VARCHAR2,
2640 p_cplv_rec IN cplv_rec_type,
2641 x_cplv_rec OUT NOCOPY cplv_rec_type) IS
2642
2643 l_api_version CONSTANT NUMBER := 1;
2644 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2645 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2646 l_cplv_rec cplv_rec_type;
2647 l_def_cplv_rec cplv_rec_type;
2648 l_cpl_rec cpl_rec_type;
2649 lx_cpl_rec cpl_rec_type;
2650 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
2651 lx_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
2652 -------------------------------
2653 -- FUNCTION fill_who_columns --
2654 -------------------------------
2655 FUNCTION fill_who_columns (
2656 p_cplv_rec IN cplv_rec_type
2657 ) RETURN cplv_rec_type IS
2658 l_cplv_rec cplv_rec_type := p_cplv_rec;
2659 BEGIN
2660
2661 l_cplv_rec.CREATION_DATE := SYSDATE;
2662 l_cplv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2663 l_cplv_rec.LAST_UPDATE_DATE := l_cplv_rec.CREATION_DATE;
2664 l_cplv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2665 l_cplv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2666
2667 RETURN(l_cplv_rec);
2668
2669 END fill_who_columns;
2670 --------------------------------------------
2671 -- Set_Attributes for:OKC_K_PARTY_ROLES_V --
2672 --------------------------------------------
2673 FUNCTION Set_Attributes (
2674 p_cplv_rec IN cplv_rec_type,
2675 x_cplv_rec OUT NOCOPY cplv_rec_type
2676 ) RETURN VARCHAR2 IS
2677 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2678 BEGIN
2679
2680 x_cplv_rec := p_cplv_rec;
2681 x_cplv_rec.OBJECT_VERSION_NUMBER := 1;
2682 x_cplv_rec.SFWT_FLAG := 'N';
2683
2684 RETURN(l_return_status);
2685
2686 END Set_Attributes;
2687 BEGIN
2688
2689 IF (l_debug = 'Y') THEN
2690 okc_debug.Set_Indentation('OKC_CPL_PVT');
2691 okc_debug.log('7600: Entered insert_row', 2);
2692 END IF;
2693
2694 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2695 G_PKG_NAME,
2696 p_init_msg_list,
2697 l_api_version,
2698 p_api_version,
2699 '_PVT',
2700 x_return_status);
2701 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2702 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2703 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2704 RAISE OKC_API.G_EXCEPTION_ERROR;
2705 END IF;
2706 l_cplv_rec := null_out_defaults(p_cplv_rec);
2707 -- Set primary key value
2708 l_cplv_rec.ID := get_seq_id;
2709 --- Setting item attributes
2710 l_return_status := Set_Attributes(
2711 l_cplv_rec, -- IN
2712 l_def_cplv_rec); -- OUT
2713 --- If any errors happen abort API
2714 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2715 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2716 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2717 RAISE OKC_API.G_EXCEPTION_ERROR;
2718 END IF;
2719 l_def_cplv_rec := fill_who_columns(l_def_cplv_rec);
2720 --- Validate all non-missing attributes (Item Level Validation)
2721 l_return_status := Validate_Attributes(l_def_cplv_rec);
2722 --- If any errors happen abort API
2723 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2724 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2725 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2726 RAISE OKC_API.G_EXCEPTION_ERROR;
2727 END IF;
2728 l_return_status := Validate_Record(l_def_cplv_rec);
2729 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2730 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2731 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2732 RAISE OKC_API.G_EXCEPTION_ERROR;
2733 END IF;
2734 --------------------------------------
2735 -- Move VIEW record to "Child" records
2736 --------------------------------------
2737 migrate(l_def_cplv_rec, l_cpl_rec);
2738 migrate(l_def_cplv_rec, l_okc_k_party_roles_tl_rec);
2739 --------------------------------------------
2740 -- Call the INSERT_ROW for each child record
2741 --------------------------------------------
2742 insert_row(
2743 p_init_msg_list,
2744 x_return_status,
2745 x_msg_count,
2746 x_msg_data,
2747 l_cpl_rec,
2748 lx_cpl_rec
2749 );
2750 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2751 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2752 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2753 RAISE OKC_API.G_EXCEPTION_ERROR;
2754 END IF;
2755 migrate(lx_cpl_rec, l_def_cplv_rec);
2756 insert_row(
2757 p_init_msg_list,
2758 x_return_status,
2759 x_msg_count,
2760 x_msg_data,
2761 l_okc_k_party_roles_tl_rec,
2762 lx_okc_k_party_roles_tl_rec
2763 );
2764 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2765 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2766 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2767 RAISE OKC_API.G_EXCEPTION_ERROR;
2768 END IF;
2769 migrate(lx_okc_k_party_roles_tl_rec, l_def_cplv_rec);
2770 -- Set OUT values
2771 x_cplv_rec := l_def_cplv_rec;
2772 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2773
2774 IF (l_debug = 'Y') THEN
2775 okc_debug.log('7700: Leaving insert_row', 2);
2776 okc_debug.Reset_Indentation;
2777 END IF;
2778
2779 EXCEPTION
2780 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2781
2782 IF (l_debug = 'Y') THEN
2783 okc_debug.log('7800: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2784 okc_debug.Reset_Indentation;
2785 END IF;
2786
2787 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2788 (
2789 l_api_name,
2790 G_PKG_NAME,
2791 'OKC_API.G_RET_STS_ERROR',
2792 x_msg_count,
2793 x_msg_data,
2794 '_PVT'
2795 );
2796 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2797
2798 IF (l_debug = 'Y') THEN
2799 okc_debug.log('7900: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2800 okc_debug.Reset_Indentation;
2801 END IF;
2802
2803 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2804 (
2805 l_api_name,
2806 G_PKG_NAME,
2807 'OKC_API.G_RET_STS_UNEXP_ERROR',
2808 x_msg_count,
2809 x_msg_data,
2810 '_PVT'
2811 );
2812 WHEN OTHERS THEN
2813
2814 IF (l_debug = 'Y') THEN
2815 okc_debug.log('8000: Exiting insert_row:OTHERS Exception', 2);
2816 okc_debug.Reset_Indentation;
2817 END IF;
2818
2819 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2820 (
2821 l_api_name,
2822 G_PKG_NAME,
2823 'OTHERS',
2824 x_msg_count,
2825 x_msg_data,
2826 '_PVT'
2827 );
2828
2829 END insert_row;
2830 ----------------------------------------
2831 -- PL/SQL TBL insert_row for:CPLV_TBL --
2832 ----------------------------------------
2833 PROCEDURE insert_row(
2834 p_api_version IN NUMBER,
2835 p_init_msg_list IN VARCHAR2 ,
2836 x_return_status OUT NOCOPY VARCHAR2,
2837 x_msg_count OUT NOCOPY NUMBER,
2838 x_msg_data OUT NOCOPY VARCHAR2,
2839 p_cplv_tbl IN cplv_tbl_type,
2840 x_cplv_tbl OUT NOCOPY cplv_tbl_type) IS
2841
2842 l_api_version CONSTANT NUMBER := 1;
2843 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2844 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2845 i NUMBER := 0;
2846 BEGIN
2847
2848 IF (l_debug = 'Y') THEN
2849 okc_debug.Set_Indentation('OKC_CPL_PVT');
2850 okc_debug.log('8100: Entered insert_row', 2);
2851 END IF;
2852
2853 OKC_API.init_msg_list(p_init_msg_list);
2854 -- Make sure PL/SQL table has records in it before passing
2855 IF (p_cplv_tbl.COUNT > 0) THEN
2856 i := p_cplv_tbl.FIRST;
2857 LOOP
2858 insert_row (
2859 p_api_version => p_api_version,
2860 p_init_msg_list => OKC_API.G_FALSE,
2861 x_return_status => x_return_status,
2862 x_msg_count => x_msg_count,
2863 x_msg_data => x_msg_data,
2864 p_cplv_rec => p_cplv_tbl(i),
2865 x_cplv_rec => x_cplv_tbl(i));
2866 EXIT WHEN (i = p_cplv_tbl.LAST);
2867 i := p_cplv_tbl.NEXT(i);
2868 END LOOP;
2869 END IF;
2870
2871 IF (l_debug = 'Y') THEN
2872 okc_debug.log('8200: Leaving insert_row', 2);
2873 okc_debug.Reset_Indentation;
2874 END IF;
2875
2876 EXCEPTION
2877 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2878
2879 IF (l_debug = 'Y') THEN
2880 okc_debug.log('8300: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2881 okc_debug.Reset_Indentation;
2882 END IF;
2883
2884 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2885 (
2886 l_api_name,
2887 G_PKG_NAME,
2888 'OKC_API.G_RET_STS_ERROR',
2889 x_msg_count,
2890 x_msg_data,
2891 '_PVT'
2892 );
2893 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2894
2895 IF (l_debug = 'Y') THEN
2896 okc_debug.log('8400: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2897 okc_debug.Reset_Indentation;
2898 END IF;
2899
2900 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2901 (
2902 l_api_name,
2903 G_PKG_NAME,
2904 'OKC_API.G_RET_STS_UNEXP_ERROR',
2905 x_msg_count,
2906 x_msg_data,
2907 '_PVT'
2908 );
2909 WHEN OTHERS THEN
2910
2911 IF (l_debug = 'Y') THEN
2912 okc_debug.log('8500: Exiting insert_row:OTHERS Exception', 2);
2913 okc_debug.Reset_Indentation;
2914 END IF;
2915
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
2926 END insert_row;
2927
2928 ---------------------------------------------------------------------------
2929 -- PROCEDURE lock_row
2930 ---------------------------------------------------------------------------
2931 --------------------------------------
2932 -- lock_row for:OKC_K_PARTY_ROLES_B --
2933 --------------------------------------
2934 PROCEDURE lock_row(
2935 p_init_msg_list IN VARCHAR2 ,
2936 x_return_status OUT NOCOPY VARCHAR2,
2937 x_msg_count OUT NOCOPY NUMBER,
2938 x_msg_data OUT NOCOPY VARCHAR2,
2939 p_cpl_rec IN cpl_rec_type) IS
2940
2941 E_Resource_Busy EXCEPTION;
2942 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2943 CURSOR lock_csr (p_cpl_rec IN cpl_rec_type) IS
2944 SELECT OBJECT_VERSION_NUMBER
2945 FROM OKC_K_PARTY_ROLES_B
2946 WHERE ID = p_cpl_rec.id
2947 AND OBJECT_VERSION_NUMBER = p_cpl_rec.object_version_number
2948 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2949
2950 CURSOR lchk_csr (p_cpl_rec IN cpl_rec_type) IS
2951 SELECT OBJECT_VERSION_NUMBER
2952 FROM OKC_K_PARTY_ROLES_B
2953 WHERE ID = p_cpl_rec.id;
2954 l_api_version CONSTANT NUMBER := 1;
2955 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2956 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2957 l_object_version_number OKC_K_PARTY_ROLES_B.OBJECT_VERSION_NUMBER%TYPE;
2958 lc_object_version_number OKC_K_PARTY_ROLES_B.OBJECT_VERSION_NUMBER%TYPE;
2959 l_row_notfound BOOLEAN := FALSE;
2960 lc_row_notfound BOOLEAN := FALSE;
2961 BEGIN
2962
2963 IF (l_debug = 'Y') THEN
2964 okc_debug.Set_Indentation('OKC_CPL_PVT');
2965 okc_debug.log('8600: Entered lock_row', 2);
2966 END IF;
2967
2968 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2969 p_init_msg_list,
2970 '_PVT',
2971 x_return_status);
2972 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2973 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2974 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2975 RAISE OKC_API.G_EXCEPTION_ERROR;
2976 END IF;
2977 BEGIN
2978
2979 IF (l_debug = 'Y') THEN
2980 okc_debug.Set_Indentation('OKC_CPL_PVT');
2981 okc_debug.log('8700: Entered lock_row', 2);
2982 END IF;
2983
2984 OPEN lock_csr(p_cpl_rec);
2985 FETCH lock_csr INTO l_object_version_number;
2986 l_row_notfound := lock_csr%NOTFOUND;
2987 CLOSE lock_csr;
2988
2989 IF (l_debug = 'Y') THEN
2990 okc_debug.log('8800: Leaving lock_row', 2);
2991 okc_debug.Reset_Indentation;
2992 END IF;
2993
2994 EXCEPTION
2995 WHEN E_Resource_Busy THEN
2996
2997 IF (l_debug = 'Y') THEN
2998 okc_debug.log('8900: Exiting lock_row:E_Resource_Busy Exception', 2);
2999 okc_debug.Reset_Indentation;
3000 END IF;
3001
3002 IF (lock_csr%ISOPEN) THEN
3003 CLOSE lock_csr;
3004 END IF;
3005 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3006 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3007 END;
3008
3009 IF ( l_row_notfound ) THEN
3010 OPEN lchk_csr(p_cpl_rec);
3011 FETCH lchk_csr INTO lc_object_version_number;
3012 lc_row_notfound := lchk_csr%NOTFOUND;
3013 CLOSE lchk_csr;
3014 END IF;
3015 IF (lc_row_notfound) THEN
3016 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3017 RAISE OKC_API.G_EXCEPTION_ERROR;
3018 ELSIF lc_object_version_number > p_cpl_rec.object_version_number THEN
3019 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3020 RAISE OKC_API.G_EXCEPTION_ERROR;
3021 ELSIF lc_object_version_number <> p_cpl_rec.object_version_number THEN
3022 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3023 RAISE OKC_API.G_EXCEPTION_ERROR;
3024 ELSIF lc_object_version_number = -1 THEN
3025 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3026 RAISE OKC_API.G_EXCEPTION_ERROR;
3027 END IF;
3028 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3029
3030 IF (l_debug = 'Y') THEN
3031 okc_debug.log('9000: Leaving lock_row', 2);
3032 okc_debug.Reset_Indentation;
3033 END IF;
3034
3035 EXCEPTION
3036 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3037
3038 IF (l_debug = 'Y') THEN
3039 okc_debug.log('9100: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3040 okc_debug.Reset_Indentation;
3041 END IF;
3042
3043 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3044 (
3045 l_api_name,
3046 G_PKG_NAME,
3047 'OKC_API.G_RET_STS_ERROR',
3048 x_msg_count,
3049 x_msg_data,
3050 '_PVT'
3051 );
3052 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3053
3054 IF (l_debug = 'Y') THEN
3055 okc_debug.log('9200: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3056 okc_debug.Reset_Indentation;
3057 END IF;
3058
3059 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3060 (
3061 l_api_name,
3062 G_PKG_NAME,
3063 'OKC_API.G_RET_STS_UNEXP_ERROR',
3064 x_msg_count,
3065 x_msg_data,
3066 '_PVT'
3067 );
3068 WHEN OTHERS THEN
3069
3070 IF (l_debug = 'Y') THEN
3071 okc_debug.log('9300: Exiting lock_row:OTHERS Exception', 2);
3072 okc_debug.Reset_Indentation;
3073 END IF;
3074
3075 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3076 (
3077 l_api_name,
3078 G_PKG_NAME,
3079 'OTHERS',
3080 x_msg_count,
3081 x_msg_data,
3082 '_PVT'
3083 );
3084
3085 END lock_row;
3086 ---------------------------------------
3087 -- lock_row for:OKC_K_PARTY_ROLES_TL --
3088 ---------------------------------------
3089 PROCEDURE lock_row(
3090 p_init_msg_list IN VARCHAR2 ,
3091 x_return_status OUT NOCOPY VARCHAR2,
3092 x_msg_count OUT NOCOPY NUMBER,
3093 x_msg_data OUT NOCOPY VARCHAR2,
3094 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type) IS
3095
3096 E_Resource_Busy EXCEPTION;
3097 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3098 CURSOR lock_csr (p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type) IS
3099 SELECT *
3100 FROM OKC_K_PARTY_ROLES_TL
3101 WHERE ID = p_okc_k_party_roles_tl_rec.id
3102 FOR UPDATE NOWAIT;
3103
3104 l_api_version CONSTANT NUMBER := 1;
3105 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3106 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3107 l_lock_var lock_csr%ROWTYPE;
3108 l_row_notfound BOOLEAN := FALSE;
3109 lc_row_notfound BOOLEAN := FALSE;
3110 BEGIN
3111
3112 IF (l_debug = 'Y') THEN
3113 okc_debug.Set_Indentation('OKC_CPL_PVT');
3114 okc_debug.log('9400: Entered lock_row', 2);
3115 END IF;
3116
3117 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3118 p_init_msg_list,
3119 '_PVT',
3120 x_return_status);
3121 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3122 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3123 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3124 RAISE OKC_API.G_EXCEPTION_ERROR;
3125 END IF;
3126 BEGIN
3127
3128 IF (l_debug = 'Y') THEN
3129 okc_debug.Set_Indentation('OKC_CPL_PVT');
3130 okc_debug.log('9500: Entered lock_row', 2);
3131 END IF;
3132
3133 OPEN lock_csr(p_okc_k_party_roles_tl_rec);
3134 FETCH lock_csr INTO l_lock_var;
3135 l_row_notfound := lock_csr%NOTFOUND;
3136 CLOSE lock_csr;
3137
3138 IF (l_debug = 'Y') THEN
3139 okc_debug.log('9600: Exiting lock_row', 2);
3140 okc_debug.Reset_Indentation;
3141 END IF;
3142
3143 EXCEPTION
3144 WHEN E_Resource_Busy THEN
3145
3146 IF (l_debug = 'Y') THEN
3147 okc_debug.log('9700: Exiting lock_row:E_Resource_Busy Exception', 2);
3148 okc_debug.Reset_Indentation;
3149 END IF;
3150
3151 IF (lock_csr%ISOPEN) THEN
3152 CLOSE lock_csr;
3153 END IF;
3154 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3155 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3156 END;
3157
3158 IF ( l_row_notfound ) THEN
3159 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3160 RAISE OKC_API.G_EXCEPTION_ERROR;
3161 END IF;
3162 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3163
3164 IF (l_debug = 'Y') THEN
3165 okc_debug.log('9800: Exiting lock_row', 2);
3166 okc_debug.Reset_Indentation;
3167 END IF;
3168
3169 EXCEPTION
3170 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3171
3172 IF (l_debug = 'Y') THEN
3173 okc_debug.log('9900: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3174 okc_debug.Reset_Indentation;
3175 END IF;
3176
3177 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3178 (
3179 l_api_name,
3180 G_PKG_NAME,
3181 'OKC_API.G_RET_STS_ERROR',
3182 x_msg_count,
3183 x_msg_data,
3184 '_PVT'
3185 );
3186 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3187
3188 IF (l_debug = 'Y') THEN
3189 okc_debug.log('10000: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3190 okc_debug.Reset_Indentation;
3191 END IF;
3192
3193 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3194 (
3195 l_api_name,
3196 G_PKG_NAME,
3197 'OKC_API.G_RET_STS_UNEXP_ERROR',
3198 x_msg_count,
3199 x_msg_data,
3200 '_PVT'
3201 );
3202 WHEN OTHERS THEN
3203
3204 IF (l_debug = 'Y') THEN
3205 okc_debug.log('10100: Exiting lock_row:OTHERS Exception', 2);
3206 okc_debug.Reset_Indentation;
3207 END IF;
3208
3209 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3210 (
3211 l_api_name,
3212 G_PKG_NAME,
3213 'OTHERS',
3214 x_msg_count,
3215 x_msg_data,
3216 '_PVT'
3217 );
3218
3219 END lock_row;
3220 --------------------------------------
3221 -- lock_row for:OKC_K_PARTY_ROLES_V --
3222 --------------------------------------
3223 PROCEDURE lock_row(
3224 p_api_version IN NUMBER,
3225 p_init_msg_list IN VARCHAR2 ,
3226 x_return_status OUT NOCOPY VARCHAR2,
3227 x_msg_count OUT NOCOPY NUMBER,
3228 x_msg_data OUT NOCOPY VARCHAR2,
3229 p_cplv_rec IN cplv_rec_type) IS
3230
3231 l_api_version CONSTANT NUMBER := 1;
3232 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3233 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3234 l_cpl_rec cpl_rec_type;
3235 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
3236 BEGIN
3237
3238 IF (l_debug = 'Y') THEN
3239 okc_debug.Set_Indentation('OKC_CPL_PVT');
3240 okc_debug.log('10200: Entered lock_row', 2);
3241 END IF;
3242
3243 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3244 G_PKG_NAME,
3245 p_init_msg_list,
3246 l_api_version,
3247 p_api_version,
3248 '_PVT',
3249 x_return_status);
3250 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3251 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3252 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3253 RAISE OKC_API.G_EXCEPTION_ERROR;
3254 END IF;
3255 --------------------------------------
3256 -- Move VIEW record to "Child" records
3257 --------------------------------------
3258 migrate(p_cplv_rec, l_cpl_rec);
3259 migrate(p_cplv_rec, l_okc_k_party_roles_tl_rec);
3260 --------------------------------------------
3261 -- Call the LOCK_ROW for each child record
3262 --------------------------------------------
3263 lock_row(
3264 p_init_msg_list,
3265 x_return_status,
3266 x_msg_count,
3267 x_msg_data,
3268 l_cpl_rec
3269 );
3270 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3272 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3273 RAISE OKC_API.G_EXCEPTION_ERROR;
3274 END IF;
3275 lock_row(
3276 p_init_msg_list,
3277 x_return_status,
3278 x_msg_count,
3279 x_msg_data,
3280 l_okc_k_party_roles_tl_rec
3281 );
3282 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3283 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3284 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3285 RAISE OKC_API.G_EXCEPTION_ERROR;
3286 END IF;
3287 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3288
3289 IF (l_debug = 'Y') THEN
3290 okc_debug.log('10300: Leaving lock_row', 2);
3291 okc_debug.Reset_Indentation;
3292 END IF;
3293
3294 EXCEPTION
3295 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3296
3297 IF (l_debug = 'Y') THEN
3298 okc_debug.log('10400: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3299 okc_debug.Reset_Indentation;
3300 END IF;
3301
3302 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3303 (
3304 l_api_name,
3305 G_PKG_NAME,
3306 'OKC_API.G_RET_STS_ERROR',
3307 x_msg_count,
3308 x_msg_data,
3309 '_PVT'
3310 );
3311 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3312
3313 IF (l_debug = 'Y') THEN
3314 okc_debug.log('10500: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3315 okc_debug.Reset_Indentation;
3316 END IF;
3317
3318 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3319 (
3320 l_api_name,
3321 G_PKG_NAME,
3322 'OKC_API.G_RET_STS_UNEXP_ERROR',
3323 x_msg_count,
3324 x_msg_data,
3325 '_PVT'
3326 );
3327 WHEN OTHERS THEN
3328
3329 IF (l_debug = 'Y') THEN
3330 okc_debug.log('10600: Exiting lock_row:OTHERS Exception', 2);
3331 okc_debug.Reset_Indentation;
3332 END IF;
3333
3334 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3335 (
3336 l_api_name,
3337 G_PKG_NAME,
3338 'OTHERS',
3339 x_msg_count,
3340 x_msg_data,
3341 '_PVT'
3342 );
3343
3344 END lock_row;
3345 --------------------------------------
3346 -- PL/SQL TBL lock_row for:CPLV_TBL --
3347 --------------------------------------
3348 PROCEDURE lock_row(
3349 p_api_version IN NUMBER,
3350 p_init_msg_list IN VARCHAR2 ,
3351 x_return_status OUT NOCOPY VARCHAR2,
3352 x_msg_count OUT NOCOPY NUMBER,
3353 x_msg_data OUT NOCOPY VARCHAR2,
3354 p_cplv_tbl IN cplv_tbl_type) IS
3355
3356 l_api_version CONSTANT NUMBER := 1;
3357 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
3358 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3359 i NUMBER := 0;
3360 BEGIN
3361
3362 IF (l_debug = 'Y') THEN
3363 okc_debug.Set_Indentation('OKC_CPL_PVT');
3364 okc_debug.log('10700: Entered lock_row', 2);
3365 END IF;
3366
3367 OKC_API.init_msg_list(p_init_msg_list);
3368 -- Make sure PL/SQL table has records in it before passing
3369 IF (p_cplv_tbl.COUNT > 0) THEN
3370 i := p_cplv_tbl.FIRST;
3371 LOOP
3372 lock_row (
3373 p_api_version => p_api_version,
3374 p_init_msg_list => OKC_API.G_FALSE,
3375 x_return_status => x_return_status,
3376 x_msg_count => x_msg_count,
3377 x_msg_data => x_msg_data,
3378 p_cplv_rec => p_cplv_tbl(i));
3379 EXIT WHEN (i = p_cplv_tbl.LAST);
3380 i := p_cplv_tbl.NEXT(i);
3381 END LOOP;
3382 END IF;
3383
3384 IF (l_debug = 'Y') THEN
3385 okc_debug.log('10800: Leaving lock_row', 2);
3386 okc_debug.Reset_Indentation;
3387 END IF;
3388
3389 EXCEPTION
3390 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3391
3392 IF (l_debug = 'Y') THEN
3393 okc_debug.log('10900: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3394 okc_debug.Reset_Indentation;
3395 END IF;
3396
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 '_PVT'
3405 );
3406 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3407
3408 IF (l_debug = 'Y') THEN
3409 okc_debug.log('11000: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3410 okc_debug.Reset_Indentation;
3411 END IF;
3412
3413 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3414 (
3415 l_api_name,
3416 G_PKG_NAME,
3417 'OKC_API.G_RET_STS_UNEXP_ERROR',
3418 x_msg_count,
3419 x_msg_data,
3420 '_PVT'
3421 );
3422 WHEN OTHERS THEN
3423
3424 IF (l_debug = 'Y') THEN
3425 okc_debug.log('11100: Exiting lock_row:OTHERS Exception', 2);
3426 okc_debug.Reset_Indentation;
3427 END IF;
3428
3429 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3430 (
3431 l_api_name,
3432 G_PKG_NAME,
3433 'OTHERS',
3434 x_msg_count,
3435 x_msg_data,
3436 '_PVT'
3437 );
3438
3439 END lock_row;
3440
3441 ---------------------------------------------------------------------------
3442 -- PROCEDURE update_row
3443 ---------------------------------------------------------------------------
3444 ----------------------------------------
3445 -- update_row for:OKC_K_PARTY_ROLES_B --
3446 ----------------------------------------
3447 PROCEDURE update_row(
3448 p_init_msg_list IN VARCHAR2 ,
3449 x_return_status OUT NOCOPY VARCHAR2,
3450 x_msg_count OUT NOCOPY NUMBER,
3451 x_msg_data OUT NOCOPY VARCHAR2,
3452 p_cpl_rec IN cpl_rec_type,
3453 x_cpl_rec OUT NOCOPY cpl_rec_type) IS
3454
3455 l_api_version CONSTANT NUMBER := 1;
3456 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
3457 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3458 l_cpl_rec cpl_rec_type := p_cpl_rec;
3459 l_def_cpl_rec cpl_rec_type;
3460 l_row_notfound BOOLEAN := TRUE;
3461 ----------------------------------
3462 -- FUNCTION populate_new_record --
3463 ----------------------------------
3464 FUNCTION populate_new_record (
3465 p_cpl_rec IN cpl_rec_type,
3466 x_cpl_rec OUT NOCOPY cpl_rec_type
3467 ) RETURN VARCHAR2 IS
3468 l_cpl_rec cpl_rec_type;
3469 l_row_notfound BOOLEAN := TRUE;
3470 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3471 BEGIN
3472
3473 IF (l_debug = 'Y') THEN
3474 okc_debug.Set_Indentation('OKC_CPL_PVT');
3475 okc_debug.log('11200: Entered populate_new_record', 2);
3476 END IF;
3477
3478 x_cpl_rec := p_cpl_rec;
3479 -- Get current database values
3480 l_cpl_rec := get_rec(p_cpl_rec, l_row_notfound);
3481 IF (l_row_notfound) THEN
3482 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3483 END IF;
3484 IF (x_cpl_rec.id = OKC_API.G_MISS_NUM)
3485 THEN
3486 x_cpl_rec.id := l_cpl_rec.id;
3487 END IF;
3488 IF (x_cpl_rec.cpl_id = OKC_API.G_MISS_NUM)
3489 THEN
3490 x_cpl_rec.cpl_id := l_cpl_rec.cpl_id;
3491 END IF;
3492 IF (x_cpl_rec.chr_id = OKC_API.G_MISS_NUM)
3493 THEN
3494 x_cpl_rec.chr_id := l_cpl_rec.chr_id;
3495 END IF;
3496 IF (x_cpl_rec.cle_id = OKC_API.G_MISS_NUM)
3497 THEN
3498 x_cpl_rec.cle_id := l_cpl_rec.cle_id;
3499 END IF;
3500 IF (x_cpl_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
3501 THEN
3502 x_cpl_rec.dnz_chr_id := l_cpl_rec.dnz_chr_id;
3503 END IF;
3504 IF (x_cpl_rec.rle_code = OKC_API.G_MISS_CHAR)
3505 THEN
3506 x_cpl_rec.rle_code := l_cpl_rec.rle_code;
3507 END IF;
3508 IF (x_cpl_rec.object1_id1 = OKC_API.G_MISS_CHAR)
3509 THEN
3510 x_cpl_rec.object1_id1 := l_cpl_rec.object1_id1;
3511 END IF;
3512 IF (x_cpl_rec.object1_id2 = OKC_API.G_MISS_CHAR)
3513 THEN
3514 x_cpl_rec.object1_id2 := l_cpl_rec.object1_id2;
3515 END IF;
3516 IF (x_cpl_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
3517 THEN
3518 x_cpl_rec.JTOT_OBJECT1_CODE := l_cpl_rec.JTOT_OBJECT1_CODE;
3519 END IF;
3520 IF (x_cpl_rec.object_version_number = OKC_API.G_MISS_NUM)
3521 THEN
3522 x_cpl_rec.object_version_number := l_cpl_rec.object_version_number;
3523 END IF;
3524 IF (x_cpl_rec.created_by = OKC_API.G_MISS_NUM)
3525 THEN
3526 x_cpl_rec.created_by := l_cpl_rec.created_by;
3527 END IF;
3528 IF (x_cpl_rec.creation_date = OKC_API.G_MISS_DATE)
3529 THEN
3530 x_cpl_rec.creation_date := l_cpl_rec.creation_date;
3531 END IF;
3532 IF (x_cpl_rec.last_updated_by = OKC_API.G_MISS_NUM)
3533 THEN
3534 x_cpl_rec.last_updated_by := l_cpl_rec.last_updated_by;
3535 END IF;
3536 IF (x_cpl_rec.last_update_date = OKC_API.G_MISS_DATE)
3537 THEN
3538 x_cpl_rec.last_update_date := l_cpl_rec.last_update_date;
3539 END IF;
3540 IF (x_cpl_rec.code = OKC_API.G_MISS_CHAR)
3541 THEN
3542 x_cpl_rec.code := l_cpl_rec.code;
3543 END IF;
3544 IF (x_cpl_rec.facility = OKC_API.G_MISS_CHAR)
3545 THEN
3546 x_cpl_rec.facility := l_cpl_rec.facility;
3547 END IF;
3548 IF (x_cpl_rec.minority_group_lookup_code = OKC_API.G_MISS_CHAR)
3549 THEN
3550 x_cpl_rec.minority_group_lookup_code := l_cpl_rec.minority_group_lookup_code;
3551 END IF;
3552 IF (x_cpl_rec.small_business_flag = OKC_API.G_MISS_CHAR)
3553 THEN
3554 x_cpl_rec.small_business_flag := l_cpl_rec.small_business_flag;
3555 END IF;
3556 IF (x_cpl_rec.women_owned_flag = OKC_API.G_MISS_CHAR)
3557 THEN
3558 x_cpl_rec.women_owned_flag := l_cpl_rec.women_owned_flag;
3559 END IF;
3560 IF (x_cpl_rec.last_update_login = OKC_API.G_MISS_NUM)
3561 THEN
3562 x_cpl_rec.last_update_login := l_cpl_rec.last_update_login;
3563 END IF;
3564 IF (x_cpl_rec.primary_yn = OKC_API.G_MISS_CHAR)
3565 THEN
3566 x_cpl_rec.primary_yn := l_cpl_rec.primary_yn;
3567 END IF;
3568 IF (x_cpl_rec.cust_acct_id = OKC_API.G_MISS_NUM)
3569 THEN
3570 x_cpl_rec.cust_acct_id := l_cpl_rec.cust_acct_id;
3571 END IF;
3572 IF (x_cpl_rec.bill_to_site_use_id = OKC_API.G_MISS_NUM)
3573 THEN
3574 x_cpl_rec.bill_to_site_use_id := l_cpl_rec.bill_to_site_use_id;
3575 END IF;
3576 IF (x_cpl_rec.attribute_category = OKC_API.G_MISS_CHAR)
3577 THEN
3578 x_cpl_rec.attribute_category := l_cpl_rec.attribute_category;
3579 END IF;
3580 IF (x_cpl_rec.attribute1 = OKC_API.G_MISS_CHAR)
3581 THEN
3582 x_cpl_rec.attribute1 := l_cpl_rec.attribute1;
3583 END IF;
3584 IF (x_cpl_rec.attribute2 = OKC_API.G_MISS_CHAR)
3585 THEN
3586 x_cpl_rec.attribute2 := l_cpl_rec.attribute2;
3587 END IF;
3588 IF (x_cpl_rec.attribute3 = OKC_API.G_MISS_CHAR)
3589 THEN
3590 x_cpl_rec.attribute3 := l_cpl_rec.attribute3;
3591 END IF;
3592 IF (x_cpl_rec.attribute4 = OKC_API.G_MISS_CHAR)
3593 THEN
3594 x_cpl_rec.attribute4 := l_cpl_rec.attribute4;
3595 END IF;
3596 IF (x_cpl_rec.attribute5 = OKC_API.G_MISS_CHAR)
3597 THEN
3598 x_cpl_rec.attribute5 := l_cpl_rec.attribute5;
3599 END IF;
3600 IF (x_cpl_rec.attribute6 = OKC_API.G_MISS_CHAR)
3601 THEN
3602 x_cpl_rec.attribute6 := l_cpl_rec.attribute6;
3603 END IF;
3604 IF (x_cpl_rec.attribute7 = OKC_API.G_MISS_CHAR)
3605 THEN
3606 x_cpl_rec.attribute7 := l_cpl_rec.attribute7;
3607 END IF;
3608 IF (x_cpl_rec.attribute8 = OKC_API.G_MISS_CHAR)
3609 THEN
3610 x_cpl_rec.attribute8 := l_cpl_rec.attribute8;
3611 END IF;
3612 IF (x_cpl_rec.attribute9 = OKC_API.G_MISS_CHAR)
3613 THEN
3614 x_cpl_rec.attribute9 := l_cpl_rec.attribute9;
3615 END IF;
3616 IF (x_cpl_rec.attribute10 = OKC_API.G_MISS_CHAR)
3617 THEN
3618 x_cpl_rec.attribute10 := l_cpl_rec.attribute10;
3619 END IF;
3620 IF (x_cpl_rec.attribute11 = OKC_API.G_MISS_CHAR)
3621 THEN
3622 x_cpl_rec.attribute11 := l_cpl_rec.attribute11;
3623 END IF;
3624 IF (x_cpl_rec.attribute12 = OKC_API.G_MISS_CHAR)
3625 THEN
3626 x_cpl_rec.attribute12 := l_cpl_rec.attribute12;
3627 END IF;
3628 IF (x_cpl_rec.attribute13 = OKC_API.G_MISS_CHAR)
3629 THEN
3630 x_cpl_rec.attribute13 := l_cpl_rec.attribute13;
3631 END IF;
3632 IF (x_cpl_rec.attribute14 = OKC_API.G_MISS_CHAR)
3633 THEN
3634 x_cpl_rec.attribute14 := l_cpl_rec.attribute14;
3635 END IF;
3636 IF (x_cpl_rec.attribute15 = OKC_API.G_MISS_CHAR)
3637 THEN
3638 x_cpl_rec.attribute15 := l_cpl_rec.attribute15;
3639 END IF;
3640
3641 -- R12 Data Model Changes 4485150 Start
3642 IF (x_cpl_rec.orig_system_id1 = OKC_API.G_MISS_NUM) /* mmadhavi 4485150 : it is G_MISS_NUM */
3643 THEN
3644 x_cpl_rec.orig_system_id1 := l_cpl_rec.orig_system_id1;
3645 END IF;
3646 IF (x_cpl_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR)
3647 THEN
3648 x_cpl_rec.orig_system_reference1 := l_cpl_rec.orig_system_reference1;
3649 END IF;
3650 IF (x_cpl_rec.orig_system_source_code = OKC_API.G_MISS_CHAR)
3651 THEN
3652 x_cpl_rec.orig_system_source_code := l_cpl_rec.orig_system_source_code;
3653 END IF;
3654 -- R12 Data Model Changes 4485150 End
3655
3656 IF (l_debug = 'Y') THEN
3657 okc_debug.log('11350: Leaving populate_new_record ', 2);
3658 okc_debug.Reset_Indentation;
3659 END IF;
3660
3661 RETURN(l_return_status);
3662
3663 END populate_new_record;
3664 --------------------------------------------
3665 -- Set_Attributes for:OKC_K_PARTY_ROLES_B --
3666 --------------------------------------------
3667 FUNCTION Set_Attributes (
3668 p_cpl_rec IN cpl_rec_type,
3669 x_cpl_rec OUT NOCOPY cpl_rec_type
3670 ) RETURN VARCHAR2 IS
3671 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3672 BEGIN
3673
3674 x_cpl_rec := p_cpl_rec;
3675 RETURN(l_return_status);
3676
3677 END Set_Attributes;
3678 -------------------------------------
3679 BEGIN
3680
3681 IF (l_debug = 'Y') THEN
3682 okc_debug.Set_Indentation('OKC_CPL_PVT');
3683 okc_debug.log('11400: Entered update_row', 2);
3684 END IF;
3685
3686 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3687 p_init_msg_list,
3688 '_PVT',
3689 x_return_status);
3690 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3691 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3692 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3693 RAISE OKC_API.G_EXCEPTION_ERROR;
3694 END IF;
3695 --- Setting item attributes
3696 l_return_status := Set_Attributes(
3697 p_cpl_rec, -- IN
3698 l_cpl_rec); -- OUT
3699 --- If any errors happen abort API
3700 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3701 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3702 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3703 RAISE OKC_API.G_EXCEPTION_ERROR;
3704 END IF;
3705 l_return_status := populate_new_record(l_cpl_rec, l_def_cpl_rec);
3706 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3707 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3708 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3709 RAISE OKC_API.G_EXCEPTION_ERROR;
3710 END IF;
3711 UPDATE OKC_K_PARTY_ROLES_B
3712 SET CPL_ID = l_def_cpl_rec.cpl_id,CHR_ID = l_def_cpl_rec.chr_id,
3713 CLE_ID = l_def_cpl_rec.cle_id,
3714 DNZ_CHR_ID = l_def_cpl_rec.dnz_chr_id,
3715 RLE_CODE = l_def_cpl_rec.rle_code,
3716 OBJECT1_ID1 = l_def_cpl_rec.object1_id1,
3717 OBJECT1_ID2 = l_def_cpl_rec.object1_id2,
3718 JTOT_OBJECT1_CODE = l_def_cpl_rec.JTOT_OBJECT1_CODE,
3719 OBJECT_VERSION_NUMBER = l_def_cpl_rec.object_version_number,
3720 CREATED_BY = l_def_cpl_rec.created_by,
3721 CREATION_DATE = l_def_cpl_rec.creation_date,
3722 LAST_UPDATED_BY = l_def_cpl_rec.last_updated_by,
3723 LAST_UPDATE_DATE = l_def_cpl_rec.last_update_date,
3724 CODE = l_def_cpl_rec.code,
3725 FACILITY = l_def_cpl_rec.facility,
3726 MINORITY_GROUP_LOOKUP_CODE = l_def_cpl_rec.minority_group_lookup_code,
3727 SMALL_BUSINESS_FLAG = l_def_cpl_rec.small_business_flag,
3728 WOMEN_OWNED_FLAG = l_def_cpl_rec.women_owned_flag,
3729 LAST_UPDATE_LOGIN = l_def_cpl_rec.last_update_login,
3730 PRIMARY_YN = l_def_cpl_rec.primary_yn,
3731 CUST_ACCT_ID = l_def_cpl_rec.cust_acct_id,
3732 BILL_TO_SITE_USE_ID = l_def_cpl_rec.bill_to_site_use_id,
3733 ATTRIBUTE_CATEGORY = l_def_cpl_rec.attribute_category,
3734 ATTRIBUTE1 = l_def_cpl_rec.attribute1,
3735 ATTRIBUTE2 = l_def_cpl_rec.attribute2,
3736 ATTRIBUTE3 = l_def_cpl_rec.attribute3,
3737 ATTRIBUTE4 = l_def_cpl_rec.attribute4,
3738 ATTRIBUTE5 = l_def_cpl_rec.attribute5,
3739 ATTRIBUTE6 = l_def_cpl_rec.attribute6,
3740 ATTRIBUTE7 = l_def_cpl_rec.attribute7,
3741 ATTRIBUTE8 = l_def_cpl_rec.attribute8,
3742 ATTRIBUTE9 = l_def_cpl_rec.attribute9,
3743 ATTRIBUTE10 = l_def_cpl_rec.attribute10,
3744 ATTRIBUTE11 = l_def_cpl_rec.attribute11,
3745 ATTRIBUTE12 = l_def_cpl_rec.attribute12,
3746 ATTRIBUTE13 = l_def_cpl_rec.attribute13,
3747 ATTRIBUTE14 = l_def_cpl_rec.attribute14,
3748 ATTRIBUTE15 = l_def_cpl_rec.attribute15,
3749 -- R12 Data Model Changes 4485150 Start
3750 ORIG_SYSTEM_ID1 = l_def_cpl_rec.orig_system_id1,
3751 ORIG_SYSTEM_REFERENCE1 = l_def_cpl_rec.orig_system_reference1,
3752 ORIG_SYSTEM_SOURCE_CODE = l_def_cpl_rec.orig_system_source_code
3753 -- R12 Data Model Changes 4485150 End
3754 WHERE ID = l_def_cpl_rec.id;
3755
3756 x_cpl_rec := l_def_cpl_rec;
3757 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3758
3759 IF (l_debug = 'Y') THEN
3760 okc_debug.log('11500: Leaving update_row', 2);
3761 okc_debug.Reset_Indentation;
3762 END IF;
3763
3764 EXCEPTION
3765 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3766
3767 IF (l_debug = 'Y') THEN
3768 okc_debug.log('11600: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3769 okc_debug.Reset_Indentation;
3770 END IF;
3771
3772 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3773 (
3774 l_api_name,
3775 G_PKG_NAME,
3776 'OKC_API.G_RET_STS_ERROR',
3777 x_msg_count,
3778 x_msg_data,
3779 '_PVT'
3780 );
3781 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3782
3783 IF (l_debug = 'Y') THEN
3784 okc_debug.log('11700: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3785 okc_debug.Reset_Indentation;
3786 END IF;
3787
3788 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3789 (
3790 l_api_name,
3791 G_PKG_NAME,
3792 'OKC_API.G_RET_STS_UNEXP_ERROR',
3793 x_msg_count,
3794 x_msg_data,
3795 '_PVT'
3796 );
3797 WHEN OTHERS THEN
3798
3799 IF (l_debug = 'Y') THEN
3800 okc_debug.log('11800: Exiting update_row:OTHERS Exception', 2);
3801 okc_debug.Reset_Indentation;
3802 END IF;
3803
3804 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3805 (
3806 l_api_name,
3807 G_PKG_NAME,
3808 'OTHERS',
3809 x_msg_count,
3810 x_msg_data,
3811 '_PVT'
3812 );
3813
3814 END update_row;
3815 -----------------------------------------
3816 -- update_row for:OKC_K_PARTY_ROLES_TL --
3817 -----------------------------------------
3818 PROCEDURE update_row(
3819 p_init_msg_list IN VARCHAR2 ,
3820 x_return_status OUT NOCOPY VARCHAR2,
3821 x_msg_count OUT NOCOPY NUMBER,
3822 x_msg_data OUT NOCOPY VARCHAR2,
3823 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type,
3824 x_okc_k_party_roles_tl_rec OUT NOCOPY okc_k_party_roles_tl_rec_type) IS
3825
3826 l_api_version CONSTANT NUMBER := 1;
3827 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
3828 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3829 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type := p_okc_k_party_roles_tl_rec;
3830 ldefokckpartyrolestlrec okc_k_party_roles_tl_rec_type;
3831 l_row_notfound BOOLEAN := TRUE;
3832 ----------------------------------
3833 -- FUNCTION populate_new_record --
3834 ----------------------------------
3835 FUNCTION populate_new_record (
3836 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type,
3837 x_okc_k_party_roles_tl_rec OUT NOCOPY okc_k_party_roles_tl_rec_type
3838 ) RETURN VARCHAR2 IS
3839 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
3840 l_row_notfound BOOLEAN := TRUE;
3841 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3842 BEGIN
3843
3844 IF (l_debug = 'Y') THEN
3845 okc_debug.Set_Indentation('OKC_CPL_PVT');
3846 okc_debug.log('11900: Entered populate_new_record', 2);
3847 END IF;
3848
3849 x_okc_k_party_roles_tl_rec := p_okc_k_party_roles_tl_rec;
3850 -- Get current database values
3851 l_okc_k_party_roles_tl_rec := get_rec(p_okc_k_party_roles_tl_rec, l_row_notfound);
3852 IF (l_row_notfound) THEN
3853 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3854 END IF;
3855 IF (x_okc_k_party_roles_tl_rec.id = OKC_API.G_MISS_NUM)
3856 THEN
3857 x_okc_k_party_roles_tl_rec.id := l_okc_k_party_roles_tl_rec.id;
3858 END IF;
3859 IF (x_okc_k_party_roles_tl_rec.language = OKC_API.G_MISS_CHAR)
3860 THEN
3861 x_okc_k_party_roles_tl_rec.language := l_okc_k_party_roles_tl_rec.language;
3862 END IF;
3863 IF (x_okc_k_party_roles_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
3864 THEN
3865 x_okc_k_party_roles_tl_rec.source_lang := l_okc_k_party_roles_tl_rec.source_lang;
3866 END IF;
3867 IF (x_okc_k_party_roles_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3868 THEN
3869 x_okc_k_party_roles_tl_rec.sfwt_flag := l_okc_k_party_roles_tl_rec.sfwt_flag;
3870 END IF;
3871 IF (x_okc_k_party_roles_tl_rec.created_by = OKC_API.G_MISS_NUM)
3872 THEN
3873 x_okc_k_party_roles_tl_rec.created_by := l_okc_k_party_roles_tl_rec.created_by;
3874 END IF;
3875 IF (x_okc_k_party_roles_tl_rec.creation_date = OKC_API.G_MISS_DATE)
3876 THEN
3877 x_okc_k_party_roles_tl_rec.creation_date := l_okc_k_party_roles_tl_rec.creation_date;
3878 END IF;
3879 IF (x_okc_k_party_roles_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
3880 THEN
3881 x_okc_k_party_roles_tl_rec.last_updated_by := l_okc_k_party_roles_tl_rec.last_updated_by;
3882 END IF;
3883 IF (x_okc_k_party_roles_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
3884 THEN
3885 x_okc_k_party_roles_tl_rec.last_update_date := l_okc_k_party_roles_tl_rec.last_update_date;
3886 END IF;
3887 IF (x_okc_k_party_roles_tl_rec.cognomen = OKC_API.G_MISS_CHAR)
3888 THEN
3889 x_okc_k_party_roles_tl_rec.cognomen := l_okc_k_party_roles_tl_rec.cognomen;
3890 END IF;
3891 IF (x_okc_k_party_roles_tl_rec.alias = OKC_API.G_MISS_CHAR)
3892 THEN
3893 x_okc_k_party_roles_tl_rec.alias := l_okc_k_party_roles_tl_rec.alias;
3894 END IF;
3895 IF (x_okc_k_party_roles_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
3896 THEN
3897 x_okc_k_party_roles_tl_rec.last_update_login := l_okc_k_party_roles_tl_rec.last_update_login;
3898 END IF;
3899
3900 IF (l_debug = 'Y') THEN
3901 okc_debug.log('11950: Leaving populate_new_record ', 2);
3902 okc_debug.Reset_Indentation;
3903 END IF;
3904
3905 RETURN(l_return_status);
3906
3907
3908 END populate_new_record;
3909 ---------------------------------------------
3910 -- Set_Attributes for:OKC_K_PARTY_ROLES_TL --
3911 ---------------------------------------------
3912 FUNCTION Set_Attributes (
3913 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type,
3914 x_okc_k_party_roles_tl_rec OUT NOCOPY okc_k_party_roles_tl_rec_type
3915 ) RETURN VARCHAR2 IS
3916 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3917 BEGIN
3918 x_okc_k_party_roles_tl_rec := p_okc_k_party_roles_tl_rec;
3919 x_okc_k_party_roles_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
3920 x_okc_k_party_roles_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
3921
3922 RETURN(l_return_status);
3923
3924 END Set_Attributes;
3925 BEGIN
3926
3927 IF (l_debug = 'Y') THEN
3928 okc_debug.Set_Indentation('OKC_CPL_PVT');
3929 okc_debug.log('12100: Entered update_row', 2);
3930 END IF;
3931
3932 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3933 p_init_msg_list,
3934 '_PVT',
3935 x_return_status);
3936 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3937 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3938 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3939 RAISE OKC_API.G_EXCEPTION_ERROR;
3940 END IF;
3941 --- Setting item attributes
3942 l_return_status := Set_Attributes(
3943 p_okc_k_party_roles_tl_rec, -- IN
3944 l_okc_k_party_roles_tl_rec); -- OUT
3945 --- If any errors happen abort API
3946 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3947 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3948 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3949 RAISE OKC_API.G_EXCEPTION_ERROR;
3950 END IF;
3951 l_return_status := populate_new_record(l_okc_k_party_roles_tl_rec, ldefokckpartyrolestlrec);
3952 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3953 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3954 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3955 RAISE OKC_API.G_EXCEPTION_ERROR;
3956 END IF;
3957 UPDATE OKC_K_PARTY_ROLES_TL
3958 SET CREATED_BY = ldefokckpartyrolestlrec.created_by,
3959 CREATION_DATE = ldefokckpartyrolestlrec.creation_date,
3960 LAST_UPDATED_BY = ldefokckpartyrolestlrec.last_updated_by,
3961 LAST_UPDATE_DATE = ldefokckpartyrolestlrec.last_update_date,
3962 COGNOMEN = ldefokckpartyrolestlrec.cognomen,
3963 ALIAS = ldefokckpartyrolestlrec.alias,
3964 LAST_UPDATE_LOGIN = ldefokckpartyrolestlrec.last_update_login
3965 --+
3966 ,SOURCE_LANG = ldefokckpartyrolestlrec.SOURCE_LANG
3967 --+
3968 WHERE ID = ldefokckpartyrolestlrec.id
3969 --- AND SOURCE_LANG = USERENV('LANG');
3970 --+
3971 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);
3972 --+
3973 UPDATE OKC_K_PARTY_ROLES_TL
3974 SET SFWT_FLAG = 'Y'
3975 WHERE ID = ldefokckpartyrolestlrec.id
3976 AND SOURCE_LANG <> USERENV('LANG');
3977
3978 x_okc_k_party_roles_tl_rec := ldefokckpartyrolestlrec;
3979 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3980
3981 IF (l_debug = 'Y') THEN
3982 okc_debug.log('12200: Leaving update_row', 2);
3983 okc_debug.Reset_Indentation;
3984 END IF;
3985
3986 EXCEPTION
3987 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3988
3989 IF (l_debug = 'Y') THEN
3990 okc_debug.log('12300: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3991 okc_debug.Reset_Indentation;
3992 END IF;
3993
3994 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3995 (
3996 l_api_name,
3997 G_PKG_NAME,
3998 'OKC_API.G_RET_STS_ERROR',
3999 x_msg_count,
4000 x_msg_data,
4001 '_PVT'
4002 );
4003 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4004
4005 IF (l_debug = 'Y') THEN
4006 okc_debug.log('12400: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4007 okc_debug.Reset_Indentation;
4008 END IF;
4009
4010 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4011 (
4012 l_api_name,
4013 G_PKG_NAME,
4014 'OKC_API.G_RET_STS_UNEXP_ERROR',
4015 x_msg_count,
4016 x_msg_data,
4017 '_PVT'
4018 );
4019 WHEN OTHERS THEN
4020
4021 IF (l_debug = 'Y') THEN
4022 okc_debug.log('12500: Exiting update_row:OTHERS Exception', 2);
4023 okc_debug.Reset_Indentation;
4024 END IF;
4025
4026 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4027 (
4028 l_api_name,
4029 G_PKG_NAME,
4030 'OTHERS',
4031 x_msg_count,
4032 x_msg_data,
4033 '_PVT'
4034 );
4035
4036 END update_row;
4037 ----------------------------------------
4038 -- update_row for:OKC_K_PARTY_ROLES_V --
4039 ----------------------------------------
4040 PROCEDURE update_row(
4041 p_api_version IN NUMBER,
4042 p_init_msg_list IN VARCHAR2 ,
4043 x_return_status OUT NOCOPY VARCHAR2,
4044 x_msg_count OUT NOCOPY NUMBER,
4045 x_msg_data OUT NOCOPY VARCHAR2,
4046 p_cplv_rec IN cplv_rec_type,
4047 x_cplv_rec OUT NOCOPY cplv_rec_type) IS
4048
4049 l_api_version CONSTANT NUMBER := 1;
4050 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4051 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4052 l_cplv_rec cplv_rec_type := p_cplv_rec;
4053 l_def_cplv_rec cplv_rec_type;
4054 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
4055 lx_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
4056 l_cpl_rec cpl_rec_type;
4057 lx_cpl_rec cpl_rec_type;
4058 -------------------------------
4059 -- FUNCTION fill_who_columns --
4060 -------------------------------
4061 FUNCTION fill_who_columns (
4062 p_cplv_rec IN cplv_rec_type
4063 ) RETURN cplv_rec_type IS
4064 l_cplv_rec cplv_rec_type := p_cplv_rec;
4065 BEGIN
4066
4067 l_cplv_rec.LAST_UPDATE_DATE := SYSDATE;
4068 l_cplv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4069 l_cplv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4070
4071 RETURN(l_cplv_rec);
4072
4073 END fill_who_columns;
4074 ----------------------------------
4075 -- FUNCTION populate_new_record --
4076 ----------------------------------
4077 FUNCTION populate_new_record (
4078 p_cplv_rec IN cplv_rec_type,
4079 x_cplv_rec OUT NOCOPY cplv_rec_type
4080 ) RETURN VARCHAR2 IS
4081 l_cplv_rec cplv_rec_type;
4082 l_row_notfound BOOLEAN := TRUE;
4083 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4084 BEGIN
4085
4086 IF (l_debug = 'Y') THEN
4087 okc_debug.Set_Indentation('OKC_CPL_PVT');
4088 okc_debug.log('12700: Entered populate_new_record', 2);
4089 END IF;
4090
4091 x_cplv_rec := p_cplv_rec;
4092 -- Get current database values
4093 l_cplv_rec := get_rec(p_cplv_rec, l_row_notfound);
4094 IF (l_row_notfound) THEN
4095 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4096 END IF;
4097 IF (x_cplv_rec.id = OKC_API.G_MISS_NUM)
4098 THEN
4099 x_cplv_rec.id := l_cplv_rec.id;
4100 END IF;
4101 IF (x_cplv_rec.object_version_number = OKC_API.G_MISS_NUM)
4102 THEN
4103 x_cplv_rec.object_version_number := l_cplv_rec.object_version_number;
4104 END IF;
4105 IF (x_cplv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4106 THEN
4107 x_cplv_rec.sfwt_flag := l_cplv_rec.sfwt_flag;
4108 END IF;
4109 IF (x_cplv_rec.cpl_id = OKC_API.G_MISS_NUM)
4110 THEN
4111 x_cplv_rec.cpl_id := l_cplv_rec.cpl_id;
4112 END IF;
4113 IF (x_cplv_rec.chr_id = OKC_API.G_MISS_NUM)
4114 THEN
4115 x_cplv_rec.chr_id := l_cplv_rec.chr_id;
4116 END IF;
4117 IF (x_cplv_rec.cle_id = OKC_API.G_MISS_NUM)
4118 THEN
4119 x_cplv_rec.cle_id := l_cplv_rec.cle_id;
4120 END IF;
4121 IF (x_cplv_rec.rle_code = OKC_API.G_MISS_CHAR)
4122 THEN
4123 x_cplv_rec.rle_code := l_cplv_rec.rle_code;
4124 END IF;
4125 IF (x_cplv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
4126 THEN
4127 x_cplv_rec.dnz_chr_id := l_cplv_rec.dnz_chr_id;
4128 END IF;
4129 IF (x_cplv_rec.object1_id1 = OKC_API.G_MISS_CHAR)
4130 THEN
4131 x_cplv_rec.object1_id1 := l_cplv_rec.object1_id1;
4132 END IF;
4133 IF (x_cplv_rec.object1_id2 = OKC_API.G_MISS_CHAR)
4134 THEN
4135 x_cplv_rec.object1_id2 := l_cplv_rec.object1_id2;
4136 END IF;
4137 IF (x_cplv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
4138 THEN
4139 x_cplv_rec.JTOT_OBJECT1_CODE := l_cplv_rec.JTOT_OBJECT1_CODE;
4140 END IF;
4141 IF (x_cplv_rec.cognomen = OKC_API.G_MISS_CHAR)
4142 THEN
4143 x_cplv_rec.cognomen := l_cplv_rec.cognomen;
4144 END IF;
4145 IF (x_cplv_rec.code = OKC_API.G_MISS_CHAR)
4146 THEN
4147 x_cplv_rec.code := l_cplv_rec.code;
4148 END IF;
4149 IF (x_cplv_rec.facility = OKC_API.G_MISS_CHAR)
4150 THEN
4151 x_cplv_rec.facility := l_cplv_rec.facility;
4152 END IF;
4153 IF (x_cplv_rec.minority_group_lookup_code = OKC_API.G_MISS_CHAR)
4154 THEN
4155 x_cplv_rec.minority_group_lookup_code := l_cplv_rec.minority_group_lookup_code;
4156 END IF;
4157 IF (x_cplv_rec.small_business_flag = OKC_API.G_MISS_CHAR)
4158 THEN
4159 x_cplv_rec.small_business_flag := l_cplv_rec.small_business_flag;
4160 END IF;
4161 IF (x_cplv_rec.women_owned_flag = OKC_API.G_MISS_CHAR)
4162 THEN
4163 x_cplv_rec.women_owned_flag := l_cplv_rec.women_owned_flag;
4164 END IF;
4165 IF (x_cplv_rec.alias = OKC_API.G_MISS_CHAR)
4166 THEN
4167 x_cplv_rec.alias := l_cplv_rec.alias;
4168 END IF;
4169 IF (x_cplv_rec.primary_yn = OKC_API.G_MISS_CHAR)
4170 THEN
4171 x_cplv_rec.primary_yn := l_cplv_rec.primary_yn;
4172 END IF;
4173 IF (x_cplv_rec.cust_acct_id = OKC_API.G_MISS_NUM)
4174 THEN
4175 x_cplv_rec.cust_acct_id := l_cplv_rec.cust_acct_id;
4176 END IF;
4177 IF (x_cplv_rec.bill_to_site_use_id = OKC_API.G_MISS_NUM)
4178 THEN
4179 x_cplv_rec.bill_to_site_use_id := l_cplv_rec.bill_to_site_use_id;
4180 END IF;
4181 IF (x_cplv_rec.attribute_category = OKC_API.G_MISS_CHAR)
4182 THEN
4183 x_cplv_rec.attribute_category := l_cplv_rec.attribute_category;
4184 END IF;
4185 IF (x_cplv_rec.attribute1 = OKC_API.G_MISS_CHAR)
4186 THEN
4187 x_cplv_rec.attribute1 := l_cplv_rec.attribute1;
4188 END IF;
4189 IF (x_cplv_rec.attribute2 = OKC_API.G_MISS_CHAR)
4190 THEN
4191 x_cplv_rec.attribute2 := l_cplv_rec.attribute2;
4192 END IF;
4193 IF (x_cplv_rec.attribute3 = OKC_API.G_MISS_CHAR)
4194 THEN
4195 x_cplv_rec.attribute3 := l_cplv_rec.attribute3;
4196 END IF;
4197 IF (x_cplv_rec.attribute4 = OKC_API.G_MISS_CHAR)
4198 THEN
4199 x_cplv_rec.attribute4 := l_cplv_rec.attribute4;
4200 END IF;
4201 IF (x_cplv_rec.attribute5 = OKC_API.G_MISS_CHAR)
4202 THEN
4203 x_cplv_rec.attribute5 := l_cplv_rec.attribute5;
4204 END IF;
4205 IF (x_cplv_rec.attribute6 = OKC_API.G_MISS_CHAR)
4206 THEN
4207 x_cplv_rec.attribute6 := l_cplv_rec.attribute6;
4208 END IF;
4209 IF (x_cplv_rec.attribute7 = OKC_API.G_MISS_CHAR)
4210 THEN
4211 x_cplv_rec.attribute7 := l_cplv_rec.attribute7;
4212 END IF;
4213 IF (x_cplv_rec.attribute8 = OKC_API.G_MISS_CHAR)
4214 THEN
4215 x_cplv_rec.attribute8 := l_cplv_rec.attribute8;
4216 END IF;
4217 IF (x_cplv_rec.attribute9 = OKC_API.G_MISS_CHAR)
4218 THEN
4219 x_cplv_rec.attribute9 := l_cplv_rec.attribute9;
4220 END IF;
4221 IF (x_cplv_rec.attribute10 = OKC_API.G_MISS_CHAR)
4222 THEN
4223 x_cplv_rec.attribute10 := l_cplv_rec.attribute10;
4224 END IF;
4225 IF (x_cplv_rec.attribute11 = OKC_API.G_MISS_CHAR)
4226 THEN
4227 x_cplv_rec.attribute11 := l_cplv_rec.attribute11;
4228 END IF;
4229 IF (x_cplv_rec.attribute12 = OKC_API.G_MISS_CHAR)
4230 THEN
4231 x_cplv_rec.attribute12 := l_cplv_rec.attribute12;
4232 END IF;
4233 IF (x_cplv_rec.attribute13 = OKC_API.G_MISS_CHAR)
4234 THEN
4235 x_cplv_rec.attribute13 := l_cplv_rec.attribute13;
4236 END IF;
4237 IF (x_cplv_rec.attribute14 = OKC_API.G_MISS_CHAR)
4238 THEN
4239 x_cplv_rec.attribute14 := l_cplv_rec.attribute14;
4240 END IF;
4241 IF (x_cplv_rec.attribute15 = OKC_API.G_MISS_CHAR)
4242 THEN
4243 x_cplv_rec.attribute15 := l_cplv_rec.attribute15;
4244 END IF;
4245 IF (x_cplv_rec.created_by = OKC_API.G_MISS_NUM)
4246 THEN
4247 x_cplv_rec.created_by := l_cplv_rec.created_by;
4248 END IF;
4249 IF (x_cplv_rec.creation_date = OKC_API.G_MISS_DATE)
4250 THEN
4251 x_cplv_rec.creation_date := l_cplv_rec.creation_date;
4252 END IF;
4253 IF (x_cplv_rec.last_updated_by = OKC_API.G_MISS_NUM)
4254 THEN
4255 x_cplv_rec.last_updated_by := l_cplv_rec.last_updated_by;
4256 END IF;
4257 IF (x_cplv_rec.last_update_date = OKC_API.G_MISS_DATE)
4258 THEN
4259 x_cplv_rec.last_update_date := l_cplv_rec.last_update_date;
4260 END IF;
4261 IF (x_cplv_rec.last_update_login = OKC_API.G_MISS_NUM)
4262 THEN
4263 x_cplv_rec.last_update_login := l_cplv_rec.last_update_login;
4264 END IF;
4265
4266 IF (l_debug = 'Y') THEN
4267 okc_debug.log('12750: Leaving populate_new_record ', 2);
4268 okc_debug.Reset_Indentation;
4269 END IF;
4270
4271 RETURN(l_return_status);
4272
4273 END populate_new_record;
4274 --------------------------------------------
4275 -- Set_Attributes for:OKC_K_PARTY_ROLES_V --
4276 --------------------------------------------
4277 FUNCTION Set_Attributes (
4278 p_cplv_rec IN cplv_rec_type,
4279 x_cplv_rec OUT NOCOPY cplv_rec_type
4280 ) RETURN VARCHAR2 IS
4281 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4282 BEGIN
4283
4284 x_cplv_rec := p_cplv_rec;
4285 x_cplv_rec.OBJECT_VERSION_NUMBER := NVL(x_cplv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
4286 RETURN(l_return_status);
4287
4288 END Set_Attributes;
4289 BEGIN
4290
4291 IF (l_debug = 'Y') THEN
4292 okc_debug.Set_Indentation('OKC_CPL_PVT');
4293 okc_debug.log('12900: Entered update_row', 2);
4294 END IF;
4295
4296 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4297 G_PKG_NAME,
4298 p_init_msg_list,
4299 l_api_version,
4300 p_api_version,
4301 '_PVT',
4302 x_return_status);
4303 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4304 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4305 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4306 RAISE OKC_API.G_EXCEPTION_ERROR;
4307 END IF;
4308 --- Setting item attributes
4309 l_return_status := Set_Attributes(
4310 p_cplv_rec, -- IN
4311 l_cplv_rec); -- OUT
4312 --- If any errors happen abort API
4313 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4314 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4315 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4316 RAISE OKC_API.G_EXCEPTION_ERROR;
4317 END IF;
4318 l_return_status := populate_new_record(l_cplv_rec, l_def_cplv_rec);
4319 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4320 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4321 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4322 RAISE OKC_API.G_EXCEPTION_ERROR;
4323 END IF;
4324 l_def_cplv_rec := fill_who_columns(l_def_cplv_rec);
4325 --- Validate all non-missing attributes (Item Level Validation)
4326 l_return_status := Validate_Attributes(l_def_cplv_rec);
4327 --- If any errors happen abort API
4328 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4329 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4330 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4331 RAISE OKC_API.G_EXCEPTION_ERROR;
4332 END IF;
4333 l_return_status := Validate_Record(l_def_cplv_rec);
4334 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4335 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4336 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4337 RAISE OKC_API.G_EXCEPTION_ERROR;
4338 END IF;
4339
4340 --------------------------------------
4341 -- Move VIEW record to "Child" records
4342 --------------------------------------
4343 migrate(l_def_cplv_rec, l_okc_k_party_roles_tl_rec);
4344 migrate(l_def_cplv_rec, l_cpl_rec);
4345 --------------------------------------------
4346 -- Call the UPDATE_ROW for each child record
4347 --------------------------------------------
4348 update_row(
4349 p_init_msg_list,
4350 x_return_status,
4351 x_msg_count,
4352 x_msg_data,
4353 l_okc_k_party_roles_tl_rec,
4354 lx_okc_k_party_roles_tl_rec
4355 );
4356 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4357 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4358 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4359 RAISE OKC_API.G_EXCEPTION_ERROR;
4360 END IF;
4361 migrate(lx_okc_k_party_roles_tl_rec, l_def_cplv_rec);
4362 update_row(
4363 p_init_msg_list,
4364 x_return_status,
4365 x_msg_count,
4366 x_msg_data,
4367 l_cpl_rec,
4368 lx_cpl_rec
4369 );
4370 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4371 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4372 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4373 RAISE OKC_API.G_EXCEPTION_ERROR;
4374 END IF;
4375 migrate(lx_cpl_rec, l_def_cplv_rec);
4376 x_cplv_rec := l_def_cplv_rec;
4377 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4378
4379 IF (l_debug = 'Y') THEN
4380 okc_debug.log('13000: Leaving update_row', 2);
4381 okc_debug.Reset_Indentation;
4382 END IF;
4383
4384 EXCEPTION
4385 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4386
4387 IF (l_debug = 'Y') THEN
4388 okc_debug.log('13100: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
4389 okc_debug.Reset_Indentation;
4390 END IF;
4391
4392 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4393 (
4394 l_api_name,
4395 G_PKG_NAME,
4396 'OKC_API.G_RET_STS_ERROR',
4397 x_msg_count,
4398 x_msg_data,
4399 '_PVT'
4400 );
4401 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4402
4403 IF (l_debug = 'Y') THEN
4404 okc_debug.log('13200: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4405 okc_debug.Reset_Indentation;
4406 END IF;
4407
4408 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4409 (
4410 l_api_name,
4411 G_PKG_NAME,
4412 'OKC_API.G_RET_STS_UNEXP_ERROR',
4413 x_msg_count,
4414 x_msg_data,
4415 '_PVT'
4416 );
4417 WHEN OTHERS THEN
4418
4419 IF (l_debug = 'Y') THEN
4420 okc_debug.log('13300: Exiting update_row:OTHERS Exception', 2);
4421 okc_debug.Reset_Indentation;
4422 END IF;
4423
4424 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4425 (
4426 l_api_name,
4427 G_PKG_NAME,
4428 'OTHERS',
4429 x_msg_count,
4430 x_msg_data,
4431 '_PVT'
4432 );
4433
4434 END update_row;
4435 ----------------------------------------
4436 -- PL/SQL TBL update_row for:CPLV_TBL --
4437 ----------------------------------------
4438 PROCEDURE update_row(
4439 p_api_version IN NUMBER,
4440 p_init_msg_list IN VARCHAR2 ,
4441 x_return_status OUT NOCOPY VARCHAR2,
4442 x_msg_count OUT NOCOPY NUMBER,
4443 x_msg_data OUT NOCOPY VARCHAR2,
4444 p_cplv_tbl IN cplv_tbl_type,
4445 x_cplv_tbl OUT NOCOPY cplv_tbl_type) IS
4446
4447 l_api_version CONSTANT NUMBER := 1;
4448 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
4449 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4450 i NUMBER := 0;
4451 BEGIN
4452
4453 IF (l_debug = 'Y') THEN
4454 okc_debug.Set_Indentation('OKC_CPL_PVT');
4455 okc_debug.log('13400: Entered update_row', 2);
4456 END IF;
4457
4458 OKC_API.init_msg_list(p_init_msg_list);
4459 -- Make sure PL/SQL table has records in it before passing
4460 IF (p_cplv_tbl.COUNT > 0) THEN
4461 i := p_cplv_tbl.FIRST;
4462 LOOP
4463 update_row (
4464 p_api_version => p_api_version,
4465 p_init_msg_list => OKC_API.G_FALSE,
4466 x_return_status => x_return_status,
4467 x_msg_count => x_msg_count,
4468 x_msg_data => x_msg_data,
4469 p_cplv_rec => p_cplv_tbl(i),
4470 x_cplv_rec => x_cplv_tbl(i));
4471 EXIT WHEN (i = p_cplv_tbl.LAST);
4472 i := p_cplv_tbl.NEXT(i);
4473 END LOOP;
4474 END IF;
4475
4476 IF (l_debug = 'Y') THEN
4477 okc_debug.log('13500: Leaving update_row', 2);
4478 okc_debug.Reset_Indentation;
4479 END IF;
4480
4481 EXCEPTION
4482 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4483
4484 IF (l_debug = 'Y') THEN
4485 okc_debug.log('13600: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
4486 okc_debug.Reset_Indentation;
4487 END IF;
4488
4489 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4490 (
4491 l_api_name,
4492 G_PKG_NAME,
4493 'OKC_API.G_RET_STS_ERROR',
4494 x_msg_count,
4495 x_msg_data,
4496 '_PVT'
4497 );
4498 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4499
4500 IF (l_debug = 'Y') THEN
4501 okc_debug.log('13700: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4502 okc_debug.Reset_Indentation;
4503 END IF;
4504
4505 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4506 (
4507 l_api_name,
4508 G_PKG_NAME,
4509 'OKC_API.G_RET_STS_UNEXP_ERROR',
4510 x_msg_count,
4511 x_msg_data,
4512 '_PVT'
4513 );
4514 WHEN OTHERS THEN
4515
4516 IF (l_debug = 'Y') THEN
4517 okc_debug.log('13800: Exiting update_row:OTHERS Exception', 2);
4518 okc_debug.Reset_Indentation;
4519 END IF;
4520
4521 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4522 (
4523 l_api_name,
4524 G_PKG_NAME,
4525 'OTHERS',
4526 x_msg_count,
4527 x_msg_data,
4528 '_PVT'
4529 );
4530
4531 END update_row;
4532
4533 ---------------------------------------------------------------------------
4534 -- PROCEDURE delete_row
4535 ---------------------------------------------------------------------------
4536 ----------------------------------------
4537 -- delete_row for:OKC_K_PARTY_ROLES_B --
4538 ----------------------------------------
4539 PROCEDURE delete_row(
4540 p_init_msg_list IN VARCHAR2 ,
4541 x_return_status OUT NOCOPY VARCHAR2,
4542 x_msg_count OUT NOCOPY NUMBER,
4543 x_msg_data OUT NOCOPY VARCHAR2,
4544 p_cpl_rec IN cpl_rec_type) IS
4545
4546 l_api_version CONSTANT NUMBER := 1;
4547 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
4548 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4549 l_cpl_rec cpl_rec_type:= p_cpl_rec;
4550 l_row_notfound BOOLEAN := TRUE;
4551 BEGIN
4552
4553 IF (l_debug = 'Y') THEN
4554 okc_debug.Set_Indentation('OKC_CPL_PVT');
4555 okc_debug.log('13900: Entered delete_row', 2);
4556 END IF;
4557
4558 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4559 p_init_msg_list,
4560 '_PVT',
4561 x_return_status);
4562 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4563 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4564 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4565 RAISE OKC_API.G_EXCEPTION_ERROR;
4566 END IF;
4567 DELETE FROM OKC_K_PARTY_ROLES_B
4568 WHERE ID = l_cpl_rec.id;
4569
4570 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4571
4572 IF (l_debug = 'Y') THEN
4573 okc_debug.log('14000: Leaving delete_row', 2);
4574 okc_debug.Reset_Indentation;
4575 END IF;
4576
4577 EXCEPTION
4578 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4579
4580 IF (l_debug = 'Y') THEN
4581 okc_debug.log('14100: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
4582 okc_debug.Reset_Indentation;
4583 END IF;
4584
4585 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4586 (
4587 l_api_name,
4588 G_PKG_NAME,
4589 'OKC_API.G_RET_STS_ERROR',
4590 x_msg_count,
4591 x_msg_data,
4592 '_PVT'
4593 );
4594 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4595
4596 IF (l_debug = 'Y') THEN
4597 okc_debug.log('14200: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4598 okc_debug.Reset_Indentation;
4599 END IF;
4600
4601 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4602 (
4603 l_api_name,
4604 G_PKG_NAME,
4605 'OKC_API.G_RET_STS_UNEXP_ERROR',
4606 x_msg_count,
4607 x_msg_data,
4608 '_PVT'
4609 );
4610 WHEN OTHERS THEN
4611
4612 IF (l_debug = 'Y') THEN
4613 okc_debug.log('14300: Exiting delete_row:OTHERS Exception', 2);
4614 okc_debug.Reset_Indentation;
4615 END IF;
4616
4617 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4618 (
4619 l_api_name,
4620 G_PKG_NAME,
4621 'OTHERS',
4622 x_msg_count,
4623 x_msg_data,
4624 '_PVT'
4625 );
4626
4627 END delete_row;
4628 -----------------------------------------
4629 -- delete_row for:OKC_K_PARTY_ROLES_TL --
4630 -----------------------------------------
4631 PROCEDURE delete_row(
4632 p_init_msg_list IN VARCHAR2 ,
4633 x_return_status OUT NOCOPY VARCHAR2,
4634 x_msg_count OUT NOCOPY NUMBER,
4635 x_msg_data OUT NOCOPY VARCHAR2,
4636 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type) IS
4637
4638 l_api_version CONSTANT NUMBER := 1;
4639 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
4640 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4641 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type:= p_okc_k_party_roles_tl_rec;
4642 l_row_notfound BOOLEAN := TRUE;
4643 ---------------------------------------------
4644 -- Set_Attributes for:OKC_K_PARTY_ROLES_TL --
4645 ---------------------------------------------
4646 FUNCTION Set_Attributes (
4647 p_okc_k_party_roles_tl_rec IN okc_k_party_roles_tl_rec_type,
4648 x_okc_k_party_roles_tl_rec OUT NOCOPY okc_k_party_roles_tl_rec_type
4649 ) RETURN VARCHAR2 IS
4650 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4651 BEGIN
4652
4653 x_okc_k_party_roles_tl_rec := p_okc_k_party_roles_tl_rec;
4654 x_okc_k_party_roles_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
4655
4656 RETURN(l_return_status);
4657
4658 END Set_Attributes;
4659 BEGIN
4660
4661 IF (l_debug = 'Y') THEN
4662 okc_debug.Set_Indentation('OKC_CPL_PVT');
4663 okc_debug.log('14500: Entered delete_row', 2);
4664 END IF;
4665
4666 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4667 p_init_msg_list,
4668 '_PVT',
4669 x_return_status);
4670 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4671 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4672 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4673 RAISE OKC_API.G_EXCEPTION_ERROR;
4674 END IF;
4675 --- Setting item attributes
4676 l_return_status := Set_Attributes(
4677 p_okc_k_party_roles_tl_rec, -- IN
4678 l_okc_k_party_roles_tl_rec); -- OUT
4679 --- If any errors happen abort API
4680 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4681 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4682 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4683 RAISE OKC_API.G_EXCEPTION_ERROR;
4684 END IF;
4685 DELETE FROM OKC_K_PARTY_ROLES_TL
4686 WHERE ID = l_okc_k_party_roles_tl_rec.id;
4687
4688 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4689
4690 IF (l_debug = 'Y') THEN
4691 okc_debug.log('14600: Leaving delete_row', 2);
4692 okc_debug.Reset_Indentation;
4693 END IF;
4694
4695 EXCEPTION
4696 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4697
4698 IF (l_debug = 'Y') THEN
4699 okc_debug.log('14700: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
4700 okc_debug.Reset_Indentation;
4701 END IF;
4702
4703 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4704 (
4705 l_api_name,
4706 G_PKG_NAME,
4707 'OKC_API.G_RET_STS_ERROR',
4708 x_msg_count,
4709 x_msg_data,
4710 '_PVT'
4711 );
4712 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4713
4714 IF (l_debug = 'Y') THEN
4715 okc_debug.log('14800: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4716 okc_debug.Reset_Indentation;
4717 END IF;
4718
4719 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4720 (
4721 l_api_name,
4722 G_PKG_NAME,
4723 'OKC_API.G_RET_STS_UNEXP_ERROR',
4724 x_msg_count,
4725 x_msg_data,
4726 '_PVT'
4727 );
4728 WHEN OTHERS THEN
4729
4730 IF (l_debug = 'Y') THEN
4731 okc_debug.log('14900: Exiting delete_row:OTHERS Exception', 2);
4732 okc_debug.Reset_Indentation;
4733 END IF;
4734
4735 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4736 (
4737 l_api_name,
4738 G_PKG_NAME,
4739 'OTHERS',
4740 x_msg_count,
4741 x_msg_data,
4742 '_PVT'
4743 );
4744
4745 END delete_row;
4746 ----------------------------------------
4747 -- delete_row for:OKC_K_PARTY_ROLES_V --
4748 ----------------------------------------
4749 PROCEDURE delete_row(
4750 p_api_version IN NUMBER,
4751 p_init_msg_list IN VARCHAR2 ,
4752 x_return_status OUT NOCOPY VARCHAR2,
4753 x_msg_count OUT NOCOPY NUMBER,
4754 x_msg_data OUT NOCOPY VARCHAR2,
4755 p_cplv_rec IN cplv_rec_type) IS
4756
4757 l_api_version CONSTANT NUMBER := 1;
4758 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
4759 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4760 l_cplv_rec cplv_rec_type := p_cplv_rec;
4761 l_okc_k_party_roles_tl_rec okc_k_party_roles_tl_rec_type;
4762 l_cpl_rec cpl_rec_type;
4763 BEGIN
4764
4765 IF (l_debug = 'Y') THEN
4766 okc_debug.Set_Indentation('OKC_CPL_PVT');
4767 okc_debug.log('15000: Entered delete_row', 2);
4768 END IF;
4769
4770 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4771 G_PKG_NAME,
4772 p_init_msg_list,
4773 l_api_version,
4774 p_api_version,
4775 '_PVT',
4776 x_return_status);
4777 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4778 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4779 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4780 RAISE OKC_API.G_EXCEPTION_ERROR;
4781 END IF;
4782 --------------------------------------
4783 -- Move VIEW record to "Child" records
4784 --------------------------------------
4785 migrate(l_cplv_rec, l_okc_k_party_roles_tl_rec);
4786 migrate(l_cplv_rec, l_cpl_rec);
4787 --------------------------------------------
4788 -- Call the DELETE_ROW for each child record
4789 --------------------------------------------
4790 delete_row(
4791 p_init_msg_list,
4792 x_return_status,
4793 x_msg_count,
4794 x_msg_data,
4795 l_okc_k_party_roles_tl_rec
4796 );
4797 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4798 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4799 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4800 RAISE OKC_API.G_EXCEPTION_ERROR;
4801 END IF;
4802 delete_row(
4803 p_init_msg_list,
4804 x_return_status,
4805 x_msg_count,
4806 x_msg_data,
4807 l_cpl_rec
4808 );
4809 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4810 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4811 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4812 RAISE OKC_API.G_EXCEPTION_ERROR;
4813 END IF;
4814 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4815
4816 IF (l_debug = 'Y') THEN
4817 okc_debug.log('15100: Leaving delete_row', 2);
4818 okc_debug.Reset_Indentation;
4819 END IF;
4820
4821 EXCEPTION
4822 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4823
4824 IF (l_debug = 'Y') THEN
4825 okc_debug.log('15200: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
4826 okc_debug.Reset_Indentation;
4827 END IF;
4828
4829 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4830 (
4831 l_api_name,
4832 G_PKG_NAME,
4833 'OKC_API.G_RET_STS_ERROR',
4834 x_msg_count,
4835 x_msg_data,
4836 '_PVT'
4837 );
4838 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4839
4840 IF (l_debug = 'Y') THEN
4841 okc_debug.log('15300: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4842 okc_debug.Reset_Indentation;
4843 END IF;
4844
4845 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4846 (
4847 l_api_name,
4848 G_PKG_NAME,
4849 'OKC_API.G_RET_STS_UNEXP_ERROR',
4850 x_msg_count,
4851 x_msg_data,
4852 '_PVT'
4853 );
4854 WHEN OTHERS THEN
4855
4856 IF (l_debug = 'Y') THEN
4857 okc_debug.log('15400: Exiting delete_row:OTHERS Exception', 2);
4858 okc_debug.Reset_Indentation;
4859 END IF;
4860
4861 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4862 (
4863 l_api_name,
4864 G_PKG_NAME,
4865 'OTHERS',
4866 x_msg_count,
4867 x_msg_data,
4868 '_PVT'
4869 );
4870
4871 END delete_row;
4872 ----------------------------------------
4873 -- PL/SQL TBL delete_row for:CPLV_TBL --
4874 ----------------------------------------
4875 PROCEDURE delete_row(
4876 p_api_version IN NUMBER,
4877 p_init_msg_list IN VARCHAR2 ,
4878 x_return_status OUT NOCOPY VARCHAR2,
4879 x_msg_count OUT NOCOPY NUMBER,
4880 x_msg_data OUT NOCOPY VARCHAR2,
4881 p_cplv_tbl IN cplv_tbl_type) IS
4882
4883 l_api_version CONSTANT NUMBER := 1;
4884 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
4885 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4886 i NUMBER := 0;
4887 BEGIN
4888
4889 IF (l_debug = 'Y') THEN
4890 okc_debug.Set_Indentation('OKC_CPL_PVT');
4891 okc_debug.log('15500: Entered delete_row', 2);
4892 END IF;
4893
4894 OKC_API.init_msg_list(p_init_msg_list);
4895 -- Make sure PL/SQL table has records in it before passing
4896 IF (p_cplv_tbl.COUNT > 0) THEN
4897 i := p_cplv_tbl.FIRST;
4898 LOOP
4899 delete_row (
4900 p_api_version => p_api_version,
4901 p_init_msg_list => OKC_API.G_FALSE,
4902 x_return_status => x_return_status,
4903 x_msg_count => x_msg_count,
4904 x_msg_data => x_msg_data,
4905 p_cplv_rec => p_cplv_tbl(i));
4906 EXIT WHEN (i = p_cplv_tbl.LAST);
4907 i := p_cplv_tbl.NEXT(i);
4908 END LOOP;
4909 END IF;
4910
4911 IF (l_debug = 'Y') THEN
4912 okc_debug.log('15600: Leaving delete_row', 2);
4913 okc_debug.Reset_Indentation;
4914 END IF;
4915
4916 EXCEPTION
4917 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4918
4919 IF (l_debug = 'Y') THEN
4920 okc_debug.log('15700: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
4921 okc_debug.Reset_Indentation;
4922 END IF;
4923
4924 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4925 (
4926 l_api_name,
4927 G_PKG_NAME,
4928 'OKC_API.G_RET_STS_ERROR',
4929 x_msg_count,
4930 x_msg_data,
4931 '_PVT'
4932 );
4933 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4934
4935 IF (l_debug = 'Y') THEN
4936 okc_debug.log('15800: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4937 okc_debug.Reset_Indentation;
4938 END IF;
4939
4940 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4941 (
4942 l_api_name,
4943 G_PKG_NAME,
4944 'OKC_API.G_RET_STS_UNEXP_ERROR',
4945 x_msg_count,
4946 x_msg_data,
4947 '_PVT'
4948 );
4949 WHEN OTHERS THEN
4950
4951 IF (l_debug = 'Y') THEN
4952 okc_debug.log('15900: Exiting delete_row:OTHERS Exception', 2);
4953 okc_debug.Reset_Indentation;
4954 END IF;
4955
4956 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4957 (
4958 l_api_name,
4959 G_PKG_NAME,
4960 'OTHERS',
4961 x_msg_count,
4962 x_msg_data,
4963 '_PVT'
4964 );
4965
4966 END delete_row;
4967
4968 ---------------------------------------------------------------
4969 -- Procedure for mass insert in OKC_K_PARTY_ROLES _B and TL tables
4970 ---------------------------------------------------------------
4971 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_cplv_tbl cplv_tbl_type) IS
4972 l_tabsize NUMBER := p_cplv_tbl.COUNT;
4973 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
4974
4975 in_id OKC_DATATYPES.NumberTabTyp;
4976 in_object_version_number OKC_DATATYPES.NumberTabTyp;
4977 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
4978 in_cpl_id OKC_DATATYPES.NumberTabTyp;
4979 in_chr_id OKC_DATATYPES.NumberTabTyp;
4980 in_cle_id OKC_DATATYPES.NumberTabTyp;
4981 in_rle_code OKC_DATATYPES.Var30TabTyp;
4982 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
4983 in_object1_id1 OKC_DATATYPES.Var40TabTyp;
4984 in_object1_id2 OKC_DATATYPES.Var200TabTyp;
4985 in_jtot_object1_code OKC_DATATYPES.Var30TabTyp;
4986 in_cognomen OKC_DATATYPES.Var300TabTyp;
4987 in_code OKC_DATATYPES.Var30TabTyp;
4988 in_facility OKC_DATATYPES.Var30TabTyp;
4989 in_minority_group_lookup_code OKC_DATATYPES.Var75TabTyp;
4990 in_small_business_flag OKC_DATATYPES.Var3TabTyp;
4991 in_women_owned_flag OKC_DATATYPES.Var3TabTyp;
4992 in_alias OKC_DATATYPES.Var150TabTyp;
4993 in_primary_yn OKC_DATATYPES.Var3TabTyp;
4994 in_cust_acct_id OKC_DATATYPES.Number15TabTyp;
4995 in_bill_to_site_use_id OKC_DATATYPES.Number15TabTyp;
4996 in_attribute_category OKC_DATATYPES.Var90TabTyp;
4997 in_attribute1 OKC_DATATYPES.Var450TabTyp;
4998 in_attribute2 OKC_DATATYPES.Var450TabTyp;
4999 in_attribute3 OKC_DATATYPES.Var450TabTyp;
5000 in_attribute4 OKC_DATATYPES.Var450TabTyp;
5001 in_attribute5 OKC_DATATYPES.Var450TabTyp;
5002 in_attribute6 OKC_DATATYPES.Var450TabTyp;
5003 in_attribute7 OKC_DATATYPES.Var450TabTyp;
5004 in_attribute8 OKC_DATATYPES.Var450TabTyp;
5005 in_attribute9 OKC_DATATYPES.Var450TabTyp;
5006 in_attribute10 OKC_DATATYPES.Var450TabTyp;
5007 in_attribute11 OKC_DATATYPES.Var450TabTyp;
5008 in_attribute12 OKC_DATATYPES.Var450TabTyp;
5009 in_attribute13 OKC_DATATYPES.Var450TabTyp;
5010 in_attribute14 OKC_DATATYPES.Var450TabTyp;
5011 in_attribute15 OKC_DATATYPES.Var450TabTyp;
5012 in_created_by OKC_DATATYPES.NumberTabTyp;
5013 in_creation_date OKC_DATATYPES.DateTabTyp;
5014 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
5015 in_last_update_date OKC_DATATYPES.DateTabTyp;
5016 in_last_update_login OKC_DATATYPES.NumberTabTyp;
5017 i number;
5018 j number;
5019 BEGIN
5020
5021 -- Initializing return status
5022 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5023 IF (l_debug = 'Y') THEN
5024 okc_debug.Set_Indentation('OKC_CPL_PVT');
5025 okc_debug.log('16000: Entered INSERT_ROW_UPG', 2);
5026 END IF;
5027
5028 i := p_cplv_tbl.FIRST; j:=0;
5029 while i is not null
5030 LOOP
5031 j:=j+1;
5032 in_id (j) := p_cplv_tbl(i).id;
5033 in_object_version_number (j) := p_cplv_tbl(i).object_version_number;
5034 in_sfwt_flag (j) := p_cplv_tbl(i).sfwt_flag;
5035 in_cpl_id (j) := p_cplv_tbl(i).cpl_id;
5036 in_chr_id (j) := p_cplv_tbl(i).chr_id;
5037 in_cle_id (j) := p_cplv_tbl(i).cle_id;
5038 in_rle_code (j) := p_cplv_tbl(i).rle_code;
5039 in_dnz_chr_id (j) := p_cplv_tbl(i).dnz_chr_id;
5040 in_object1_id1 (j) := p_cplv_tbl(i).object1_id1;
5041 in_object1_id2 (j) := p_cplv_tbl(i).object1_id2;
5042 in_jtot_object1_code (j) := p_cplv_tbl(i).jtot_object1_code;
5043 in_cognomen (j) := p_cplv_tbl(i).cognomen;
5044 in_code (j) := p_cplv_tbl(i).code;
5045 in_facility (j) := p_cplv_tbl(i).facility;
5046 in_minority_group_lookup_code(j) := p_cplv_tbl(i).minority_group_lookup_code;
5047 in_small_business_flag (j) := p_cplv_tbl(i).small_business_flag;
5048 in_women_owned_flag (j) := p_cplv_tbl(i).women_owned_flag;
5049 in_alias (j) := p_cplv_tbl(i).alias;
5050 in_primary_yn (j) := p_cplv_tbl(i).primary_yn;
5051 in_cust_acct_id (j) := p_cplv_tbl(i).cust_acct_id;
5052 in_bill_to_site_use_id (j) := p_cplv_tbl(i).bill_to_site_use_id;
5053 in_attribute_category (j) := p_cplv_tbl(i).attribute_category;
5054 in_attribute1 (j) := p_cplv_tbl(i).attribute1;
5055 in_attribute2 (j) := p_cplv_tbl(i).attribute2;
5056 in_attribute3 (j) := p_cplv_tbl(i).attribute3;
5057 in_attribute4 (j) := p_cplv_tbl(i).attribute4;
5058 in_attribute5 (j) := p_cplv_tbl(i).attribute5;
5059 in_attribute6 (j) := p_cplv_tbl(i).attribute6;
5060 in_attribute7 (j) := p_cplv_tbl(i).attribute7;
5061 in_attribute8 (j) := p_cplv_tbl(i).attribute8;
5062 in_attribute9 (j) := p_cplv_tbl(i).attribute9;
5063 in_attribute10 (j) := p_cplv_tbl(i).attribute10;
5064 in_attribute11 (j) := p_cplv_tbl(i).attribute11;
5065 in_attribute12 (j) := p_cplv_tbl(i).attribute12;
5066 in_attribute13 (j) := p_cplv_tbl(i).attribute13;
5067 in_attribute14 (j) := p_cplv_tbl(i).attribute14;
5068 in_attribute15 (j) := p_cplv_tbl(i).attribute15;
5069 in_created_by (j) := p_cplv_tbl(i).created_by;
5070 in_creation_date (j) := p_cplv_tbl(i).creation_date;
5071 in_last_updated_by (j) := p_cplv_tbl(i).last_updated_by;
5072 in_last_update_date (j) := p_cplv_tbl(i).last_update_date;
5073 in_last_update_login (j) := p_cplv_tbl(i).last_update_login;
5074 i:=p_cplv_tbl.next(i);
5075 END LOOP;
5076
5077 FORALL i in 1..l_tabsize
5078 INSERT
5079 INTO OKC_K_PARTY_ROLES_B
5080 (
5081 id,
5082 cpl_id,
5083 chr_id,
5084 cle_id,
5085 dnz_chr_id,
5086 rle_code,
5087 object1_id1,
5088 object1_id2,
5089 jtot_object1_code,
5090 object_version_number,
5091 created_by,
5092 creation_date,
5093 last_updated_by,
5094 last_update_date,
5095 code,
5096 facility,
5097 minority_group_lookup_code,
5098 small_business_flag,
5099 women_owned_flag,
5100 last_update_login,
5101 primary_yn,
5102 cust_acct_id,
5103 bill_to_site_use_id,
5104 attribute_category,
5105 attribute1,
5106 attribute2,
5107 attribute3,
5108 attribute4,
5109 attribute5,
5110 attribute6,
5111 attribute7,
5112 attribute8,
5113 attribute9,
5114 attribute10,
5115 attribute11,
5116 attribute12,
5117 attribute13,
5118 attribute14,
5119 attribute15
5120 -- REMOVE comma from the previous line
5121 )
5122 VALUES (
5123 in_id(i),
5124 in_cpl_id(i),
5125 in_chr_id(i),
5126 in_cle_id(i),
5127 in_dnz_chr_id(i),
5128 in_rle_code(i),
5129 in_object1_id1(i),
5130 in_object1_id2(i),
5131 in_jtot_object1_code(i),
5132 in_object_version_number(i),
5133 in_created_by(i),
5134 in_creation_date(i),
5135 in_last_updated_by(i),
5136 in_last_update_date(i),
5137 in_code(i),
5138 in_facility(i),
5139 in_minority_group_lookup_code(i),
5140 in_small_business_flag(i),
5141 in_women_owned_flag(i),
5142 in_last_update_login(i),
5143 in_primary_yn(i),
5144 in_cust_acct_id(i),
5145 in_bill_to_site_use_id(i),
5146 in_attribute_category(i),
5147 in_attribute1(i),
5148 in_attribute2(i),
5149 in_attribute3(i),
5150 in_attribute4(i),
5151 in_attribute5(i),
5152 in_attribute6(i),
5153 in_attribute7(i),
5154 in_attribute8(i),
5155 in_attribute9(i),
5156 in_attribute10(i),
5157 in_attribute11(i),
5158 in_attribute12(i),
5159 in_attribute13(i),
5160 in_attribute14(i),
5161 in_attribute15(i)
5162 -- REMOVE comma from the previous line
5163 );
5164
5165 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
5166 FORALL i in 1..l_tabsize
5167 INSERT INTO OKC_K_PARTY_ROLES_TL(
5168 id,
5169 language,
5170 source_lang,
5171 sfwt_flag,
5172 created_by,
5173 creation_date,
5174 last_updated_by,
5175 last_update_date,
5176 cognomen,
5177 alias,
5178 last_update_login
5179 -- REMOVE comma from the previous line
5180 )
5181 VALUES (
5182 in_id(i),
5183 OKC_UTIL.g_language_code(lang_i),
5184 l_source_lang,
5185 in_sfwt_flag(i),
5186 in_created_by(i),
5187 in_creation_date(i),
5188 in_last_updated_by(i),
5189 in_last_update_date(i),
5190 in_cognomen(i),
5191 in_alias(i),
5192 in_last_update_login(i)
5193 -- REMOVE comma from the previous line
5194 );
5195 END LOOP;
5196
5197 IF (l_debug = 'Y') THEN
5198 okc_debug.log('16100: Leaving INSERT_ROW_UPG', 2);
5199 okc_debug.Reset_Indentation;
5200 END IF;
5201
5202 EXCEPTION
5203 WHEN OTHERS THEN
5204
5205 IF (l_debug = 'Y') THEN
5206 okc_debug.log('16200: Exiting INSERT_ROW_UPG:OTHERS Exception', 2);
5207 okc_debug.Reset_Indentation;
5208 END IF;
5209 -- store SQL error message on message stack
5210 OKC_API.SET_MESSAGE(
5211 p_app_name => G_APP_NAME,
5212 p_msg_name => G_UNEXPECTED_ERROR,
5213 p_token1 => G_SQLCODE_TOKEN,
5214 p_token1_value => SQLCODE,
5215 p_token2 => G_SQLERRM_TOKEN,
5216 p_token2_value => SQLERRM);
5217 -- notify caller of an error as UNEXPETED error
5218 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5219
5220
5221 -- RAISE;
5222
5223 END INSERT_ROW_UPG;
5224
5225 --This function is called from versioning API OKC_VERSION_PVT
5226 --Old Location: OKCRVERB.pls
5227 --New Location: Base Table API
5228
5229 FUNCTION create_version(
5230 p_chr_id IN NUMBER,
5231 p_major_version IN NUMBER
5232 ) RETURN VARCHAR2 IS
5233
5234 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
5235
5236 BEGIN
5237
5238 IF (l_debug = 'Y') THEN
5239 okc_debug.Set_Indentation('OKC_CPL_PVT');
5240 okc_debug.log('16300: Entered create_version', 2);
5241 END IF;
5242
5243 INSERT INTO okc_k_party_roles_bh
5244 (
5245 major_version,
5246 id,
5247 chr_id,
5248 cle_id,
5249 dnz_chr_id,
5250 rle_code,
5251 object1_id1,
5252 object1_id2,
5253 jtot_object1_code,
5254 object_version_number,
5255 created_by,
5256 creation_date,
5257 last_updated_by,
5258 last_update_date,
5259 code,
5260 facility,
5261 minority_group_lookup_code,
5262 small_business_flag,
5263 women_owned_flag,
5264 last_update_login,
5265 primary_yn,
5266 cust_acct_id,
5267 bill_to_site_use_id,
5268 attribute_category,
5269 attribute1,
5270 attribute2,
5271 attribute3,
5272 attribute4,
5273 attribute5,
5274 attribute6,
5275 attribute7,
5276 attribute8,
5277 attribute9,
5278 attribute10,
5279 attribute11,
5280 attribute12,
5281 attribute13,
5282 attribute14,
5283 attribute15,
5284 cpl_id --,
5285 -- R12 Data Model Changes 4485150 Start
5286 -- orig_system_id1,
5287 -- orig_system_reference1,
5288 -- orig_system_source_code
5289 -- R12 Data Model Changes 4485150 End
5290 )
5291 SELECT
5292 p_major_version,
5293 id,
5294 chr_id,
5295 cle_id,
5296 dnz_chr_id,
5297 rle_code,
5298 object1_id1,
5299 object1_id2,
5300 jtot_object1_code,
5301 object_version_number,
5302 created_by,
5303 creation_date,
5304 last_updated_by,
5305 last_update_date,
5306 code,
5307 facility,
5308 minority_group_lookup_code,
5309 small_business_flag,
5310 women_owned_flag,
5311 last_update_login,
5312 primary_yn,
5313 cust_acct_id,
5314 bill_to_site_use_id,
5315 attribute_category,
5316 attribute1,
5317 attribute2,
5318 attribute3,
5319 attribute4,
5320 attribute5,
5321 attribute6,
5322 attribute7,
5323 attribute8,
5324 attribute9,
5325 attribute10,
5326 attribute11,
5327 attribute12,
5328 attribute13,
5329 attribute14,
5330 attribute15,
5331 cpl_id --,
5332 -- R12 Data Model Changes 4485150 Start
5333 -- orig_system_id1,
5334 -- orig_system_reference1,
5335 -- orig_system_source_code
5336 -- R12 Data Model Changes 4485150 End
5337
5338 FROM okc_k_party_roles_b
5339 WHERE dnz_chr_id = p_chr_id;
5340
5341 --------------------------------
5342 -- Version TL Table
5343 --------------------------------
5344
5345 INSERT INTO okc_k_party_roles_tlh
5346 (
5347 major_version,
5348 id,
5349 language,
5350 source_lang,
5351 sfwt_flag,
5352 created_by,
5353 creation_date,
5354 last_updated_by,
5355 last_update_date,
5356 cognomen,
5357 alias,
5358 last_update_login
5359 )
5360 SELECT
5361 p_major_version,
5362 id,
5363 language,
5364 source_lang,
5365 sfwt_flag,
5366 created_by,
5367 creation_date,
5368 last_updated_by,
5369 last_update_date,
5370 cognomen,
5371 alias,
5372 last_update_login
5373 FROM okc_k_party_roles_tl
5374 WHERE id in (select id
5375 from okc_k_party_roles_b
5376 where dnz_chr_id = p_chr_id);
5377
5378 IF (l_debug = 'Y') THEN
5379 okc_debug.log('16400: Leaving create_version', 2);
5380 okc_debug.Reset_Indentation;
5381 END IF;
5382
5383 RETURN l_return_status;
5384
5385 EXCEPTION
5386 -- other appropriate handlers
5387 WHEN OTHERS THEN
5388
5389 IF (l_debug = 'Y') THEN
5390 okc_debug.log('16500: Exiting create_version:OTHERS Exception', 2);
5391 okc_debug.Reset_Indentation;
5392 END IF;
5393
5394 -- store SQL error message on message stack
5395 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
5396 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
5397 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
5398 p_token1_value => sqlcode,
5399 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
5400 p_token2_value => sqlerrm);
5401
5402 -- notify UNEXPECTED error
5403 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5404 return l_return_status;
5405
5406 END create_version;
5407
5408 --This Function is called from Versioning API OKC_VERSION_PVT
5409 --Old Location:OKCRVERB.pls
5410 --New Location:Base Table API
5411
5412 FUNCTION restore_version(
5413 p_chr_id IN NUMBER,
5414 p_major_version IN NUMBER
5415 ) RETURN VARCHAR2 IS
5416
5417 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
5418
5419 BEGIN
5420
5421 IF (l_debug = 'Y') THEN
5422 okc_debug.Set_Indentation('OKC_CPL_PVT');
5423 okc_debug.log('16600: Entered restore_version', 2);
5424 END IF;
5425
5426 INSERT INTO okc_k_party_roles_tl
5427 (
5428 id,
5429 language,
5430 source_lang,
5431 sfwt_flag,
5432 created_by,
5433 creation_date,
5434 last_updated_by,
5435 last_update_date,
5436 cognomen,
5437 alias,
5438 last_update_login
5439 )
5440 SELECT
5441 id,
5442 language,
5443 source_lang,
5444 sfwt_flag,
5445 created_by,
5446 creation_date,
5447 last_updated_by,
5448 last_update_date,
5449 cognomen,
5450 alias,
5451 last_update_login
5452 FROM okc_k_party_roles_tlh
5453 WHERE id in (SELECT id
5454 FROM okc_k_party_roles_bh
5455 WHERE dnz_chr_id = p_chr_id)
5456 AND major_version = p_major_version;
5457
5458 ----------------------------------------
5459 -- Restoring Base Table
5460 ----------------------------------------
5461
5462 INSERT INTO okc_k_party_roles_b
5463 (
5464 id,
5465 chr_id,
5466 cle_id,
5467 dnz_chr_id,
5468 rle_code,
5469 object1_id1,
5470 object1_id2,
5471 jtot_object1_code,
5472 object_version_number,
5473 created_by,
5474 creation_date,
5475 last_updated_by,
5476 last_update_date,
5477 code,
5478 facility,
5479 minority_group_lookup_code,
5480 small_business_flag,
5481 women_owned_flag,
5482 last_update_login,
5483 primary_yn,
5484 cust_acct_id,
5485 bill_to_site_use_id,
5486 attribute_category,
5487 attribute1,
5488 attribute2,
5489 attribute3,
5490 attribute4,
5491 attribute5,
5492 attribute6,
5493 attribute7,
5494 attribute8,
5495 attribute9,
5496 attribute10,
5497 attribute11,
5498 attribute12,
5499 attribute13,
5500 attribute14,
5501 attribute15,
5502 cpl_id --,
5503 -- R12 Data Model Changes 4485150 Start
5504 /* orig_system_id1,
5505 orig_system_reference1,
5506 orig_system_source_code */
5507 -- R12 Data Model Changes 4485150 End
5508 )
5509 SELECT
5510 id,
5511 chr_id,
5512 cle_id,
5513 dnz_chr_id,
5514 rle_code,
5515 object1_id1,
5516 object1_id2,
5517 jtot_object1_code,
5518 object_version_number,
5519 created_by,
5520 creation_date,
5521 last_updated_by,
5522 last_update_date,
5523 code,
5524 facility,
5525 minority_group_lookup_code,
5526 small_business_flag,
5527 women_owned_flag,
5528 last_update_login,
5529 primary_yn,
5530 cust_acct_id,
5531 bill_to_site_use_id,
5532 attribute_category,
5533 attribute1,
5534 attribute2,
5535 attribute3,
5536 attribute4,
5537 attribute5,
5538 attribute6,
5539 attribute7,
5540 attribute8,
5541 attribute9,
5542 attribute10,
5543 attribute11,
5544 attribute12,
5545 attribute13,
5546 attribute14,
5547 attribute15,
5548 cpl_id --,
5549 -- R12 Data Model Changes 4485150 Start
5550 /* orig_system_id1,
5551 orig_system_reference1,
5552 orig_system_source_code */
5553 -- R12 Data Model Changes 4485150 End
5554 FROM okc_k_party_roles_bh
5555 WHERE dnz_chr_id = p_chr_id
5556 AND major_version = p_major_version;
5557
5558 IF (l_debug = 'Y') THEN
5559 okc_debug.log('16700: Leaving restore_version', 2);
5560 okc_debug.Reset_Indentation;
5561 END IF;
5562
5563 RETURN l_return_status;
5564
5565 EXCEPTION
5566 -- other appropriate handlers
5567 WHEN OTHERS THEN
5568
5569 IF (l_debug = 'Y') THEN
5570 okc_debug.log('16800: Exiting restore_version:OTHERS Exception', 2);
5571 okc_debug.Reset_Indentation;
5572 END IF;
5573
5574 -- store SQL error message on message stack
5575 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
5576 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
5577 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
5578 p_token1_value => sqlcode,
5579 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
5580 p_token2_value => sqlerrm);
5581
5582 -- notify UNEXPECTED error
5583 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5584 return l_return_status;
5585
5586 END restore_version;
5587
5588 END OKC_CPL_PVT;