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