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