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