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