[Home] [Help]
PACKAGE BODY: APPS.OKL_DCT_PVT
Source
1 PACKAGE BODY OKL_DCT_PVT AS
2 /* $Header: OKLSDCTB.pls 120.3 2006/07/13 12:55:08 adagur noship $ */
3 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
4 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
5 G_UPPER_CASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKC_UPPER_CASE_REQUIRED';
6 --G_INVALID_END_DATE CONSTANT VARCHAR2(200) := 'INVALID_END_DATE';
7 --G_INVALID_DATE_RANGE CONSTANT VARCHAR2(200) := 'INVALID_DATE_RANGE';
8 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
9 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
10 G_VIEW CONSTANT VARCHAR2(200) := 'OKL_DF_CTGY_RMK_TMS';
11 G_EXCEPTION_HALT_VALIDATION exception;
12 g_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
13 ------------------------------------
14 -- PROCEDURE validate_id--
15 ------------------------------------
16 -- Function Name : validate_id
17 -- Description : To validate the id
18 -- Business Rules :
19 -- Parameters : Record
20 -- Version : 1.0
21
22 PROCEDURE validate_id(
23 x_return_status OUT NOCOPY VARCHAR2,
24 p_dctv_rec IN dctv_rec_type
25 ) IS
26 BEGIN
27 x_return_status := OKC_API.G_RET_STS_SUCCESS;
28
29 IF p_dctv_rec.id = OKC_API.G_MISS_NUM OR
30 p_dctv_rec.id IS NULL
31 THEN
32 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
33 p_msg_name => g_required_value,
34 p_token1 => g_col_name_token,
35 p_token1_value => 'ID');
36
37 x_return_status := OKC_API.G_RET_STS_ERROR;
38 raise G_EXCEPTION_HALT_VALIDATION;
39 else
40 x_return_status := OKC_API.G_RET_STS_SUCCESS;
41 end if;
42 exception
43 when G_EXCEPTION_HALT_VALIDATION then
44 null;
45 when OTHERS then
46 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
47 p_msg_name => g_required_value,
48 p_token1 => g_sqlcode_token,
49 p_token1_value => sqlcode,
50 p_token2 => g_sqlerrm_token,
51 p_token2_value => sqlerrm);
52 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
53 END validate_id;
54 ------------------------------------
55 -- PROCEDURE validate_object_version_number--
56 ------------------------------------
57 -- Function Name : validate_object_version_number
58 -- Description : To validate the object Version Number
59 -- Business Rules :
60 -- Parameters : Record
61 -- Version : 1.0
62
63 PROCEDURE validate_object_version_number (
64 x_return_status OUT NOCOPY VARCHAR2,
65 p_dctv_rec IN dctv_rec_type
66 ) IS
67 BEGIN
68 IF p_dctv_rec.object_version_number = OKC_API.G_MISS_NUM OR
69 p_dctv_rec.object_version_number IS NULL
70 THEN
71 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
72 p_msg_name => g_required_value,
73 p_token1 => g_col_name_token,
74 p_token1_value => 'ObjectVersionNumber');
75
76 x_return_status := OKC_API.G_RET_STS_ERROR;
77 raise G_EXCEPTION_HALT_VALIDATION;
78 ELSE
79 x_return_status := OKC_API.G_RET_STS_SUCCESS;
80 END IF;
81 exception
82 when G_EXCEPTION_HALT_VALIDATION then
83 null;
84 when OTHERS then
85 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
86 p_msg_name => g_required_value,
87 p_token1 => g_sqlcode_token,
88 p_token1_value => sqlcode,
89 p_token2 => g_sqlerrm_token,
90 p_token2_value => sqlerrm);
91 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
92
93 END validate_object_version_number;
94 ------------------------------------
95 -- PROCEDURE validate_rmr_id--
96 ------------------------------------
97 -- Function Name : validate_qte_id
98 -- Description : To validate the RMR Id
99 -- Business Rules :
100 -- Parameters : Record
101 -- Version : 1.0
102
103 PROCEDURE validate_rmr_id (
104 x_return_status OUT NOCOPY VARCHAR2,
105 p_dctv_rec IN dctv_rec_type
106 ) IS
107 -- select the ID of the parent record from the parent table
108 /* CURSOR okl_rmrv_fk_csr IS
109 SELECT 'x'
110 FROM OKX_REMARKET_TEAMS_V
111 WHERE ID = p_dctv_rec.rmr_id;
112 */
113 BEGIN
114 -- initialize return status
115 x_return_status := OKC_API.G_RET_STS_SUCCESS;
116
117 -- data is required
118
119 IF ((p_dctv_rec.rmr_id = OKC_API.G_MISS_NUM) OR
120 (p_dctv_rec.rmr_id IS NULL))
121 THEN
122
123 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
124 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
125 p_token1 => 'PROMPT',
126 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_REMARKETER'));
127
128 /*
129 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
130 p_msg_name => g_required_value,
131 p_token1 => g_col_name_token,
132 p_token1_value => 'RmrId');
133 */
134 -- notify caller of an error
135 x_return_status := OKC_API.G_RET_STS_ERROR;
136 -- halt futher validation of this column
137 raise G_EXCEPTION_HALT_VALIDATION;
138 END IF;
139 -- enforce foreign key
140 /* OPEN okl_rmrv_fk_csr;
141 FETCH okl_rmrv_fk_csr INTO l_dummy_var;
142 CLOSE okl_rmrv_fk_csr;
143
144 -- if l_dummy_var is still set to default, data was not found
145 IF (l_dummy_var = '?') THEN
146 OKC_API.set_message(p_app_name => g_app_name,
147 p_msg_name => g_no_parent_record,
148 p_token1 => g_col_name_token,
149 p_token1_value => 'rmr_id',
150 p_token2 => g_child_table_token,
151 p_token2_value => 'OKL_DF_CTGY_RMK_TMS_B',
152 p_token3 => g_parent_table_token,
153 p_token3_value => 'OKX_REMARKET_TEAMS_V');
154
155 -- notify caller of an error
156 x_return_status := OKC_API.G_RET_STS_ERROR;
157 END IF;
158 */
159 exception
160 when G_EXCEPTION_HALT_VALIDATION then
161 null;
162 when OTHERS then
163 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
164 p_msg_name => g_required_value,
165 p_token1 => g_sqlcode_token,
166 p_token1_value => sqlcode,
167 p_token2 => g_sqlerrm_token,
168 p_token2_value => sqlerrm);
169 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
170 -- verify that cursor was closed
171 /* if okl_rmrv_fk_csr%ISOPEN then
172 close okl_rmrv_fk_csr;
173 end if;
174 */
175 END validate_rmr_id;
176 ------------------------------------
177 -- PROCEDURE validate_ico_id--
178 ------------------------------------
179 -- Function Name : validate_ico_id
180 -- Description : To validate the ICO Id
181 -- Business Rules :
182 -- Parameters : Record
183 -- Version : 1.0
184
185 PROCEDURE validate_ico_id (
186 x_return_status OUT NOCOPY VARCHAR2,
187 p_dctv_rec IN dctv_rec_type
188 ) IS
189 -- select the ID of the parent record from the parent table
190 /* CURSOR okl_icov_fk_csr IS
191 SELECT 'x'
192 FROM OKX_ITEM_CATALOGS_V
193 WHERE ID = p_dctv_rec.ico_id;
194 */
195 BEGIN
196 -- initialize return status
197 x_return_status := OKC_API.G_RET_STS_SUCCESS;
198
199 -- data is required
200 IF ((p_dctv_rec.ico_id = OKC_API.G_MISS_NUM) OR
201 (p_dctv_rec.ico_id IS NULL))
202 THEN
203
204 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
205 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
206 p_token1 => 'PROMPT',
207 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_ITEM_CATALOG'));
208
209 /*
210 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
211 p_msg_name => g_required_value,
212 p_token1 => g_col_name_token,
213 p_token1_value => 'IcoId');
214 */
215 -- notify caller of an error
216 x_return_status := OKC_API.G_RET_STS_ERROR;
217 -- halt further validation of this column
218 raise G_EXCEPTION_HALT_VALIDATION;
219 END IF;
220 -- enforce foreign key
221 /* OPEN okl_icov_fk_csr;
222 FETCH okl_icov_fk_csr INTO l_dummy_var;
223 CLOSE okl_icov_fk_csr;
224
225 -- if l_dummy_var is still set to default, data was not found
226 IF (l_dummy_var = '?') THEN
227 OKC_API.set_message(p_app_name => g_app_name,
228 p_msg_name => g_no_parent_record,
229 p_token1 => g_col_name_token,
230 p_token1_value => 'ico_id',
231 p_token2 => g_child_table_token,
232 p_token2_value => 'OKL_DF_CTGY_RMK_TMS_B',
233 p_token3 => g_parent_table_token,
234 p_token3_value => 'OKX_ITEM_CATALOGS_V');
235
236 -- notify caller of an error
237 x_return_status := OKC_API.G_RET_STS_ERROR;
238 END IF;
239 */
240 exception
241 when G_EXCEPTION_HALT_VALIDATION then
242 null;
243 when OTHERS then
244 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
245 p_msg_name => g_required_value,
246 p_token1 => g_sqlcode_token,
247 p_token1_value => sqlcode,
248 p_token2 => g_sqlerrm_token,
249 p_token2_value => sqlerrm);
250 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
251 /* -- verify that cursor was closed
252 if okl_icov_fk_csr%ISOPEN then
253 close okl_icov_fk_csr;
254 end if;
255 */
256 END validate_ico_id;
257
258 ------------------------------------
259 -- PROCEDURE validate_ILN_ID
260 ------------------------------------
261 -- Function Name : validate_ILN_ID
262 -- Description : To validate the ILN_ID
263 -- Business Rules :
264 -- Parameters : Record
265 -- Version : 1.0
266
267 PROCEDURE validate_ILN_ID (
268 x_return_status OUT NOCOPY VARCHAR2,
269 p_dctv_rec IN dctv_rec_type) IS
270 l_dummy_var varchar2(1) := '?';
271 -- select the ID of the parent record from the parent table
272 /* CURSOR okl_ilnv_csr IS
273 SELECT 'x'
274 FROM OKX_LOCATIONS_V
275 WHERE ID = p_dctv_rec.iln_id;
276 */
277 BEGIN
278 -- initialize return status
279 x_return_status := OKC_API.G_RET_STS_SUCCESS;
280
281 -- data is required
282 if (p_dctv_rec.iln_id is null) OR (p_dctv_rec.iln_id = OKC_API.G_MISS_NUM) then
283 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
284 p_msg_name => g_required_value,
285 p_token1 => g_col_name_token,
286 p_token1_value => 'iln_id');
287
288 -- notify caller of an error
289 x_return_status := OKC_API.G_RET_STS_ERROR;
290
291 -- halt further validation of this column
292 raise G_EXCEPTION_HALT_VALIDATION;
293 end if;
294
295 -- enforce foreign key
296 /*
297 OPEN okl_ilnv_fk_csr;
298 FETCH okl_ilnv_fk_csr INTO l_dummy_var;
299 CLOSE okl_ilnv_fk_csr;
300
301 -- if l_dummy_var is still set to default, data was not found
302 IF (l_dummy_var = '?') THEN
303 OKC_API.set_message(p_app_name => g_app_name,
304 p_msg_name => g_no_parent_record,
305 p_token1 => g_col_name_token,
306 p_token1_value => 'iln_id',
307 p_token2 => g_child_table_token,
308 p_token2_value => 'OKL_DF_CTGY_RMK_TMS_B',
309 p_token3 => g_parent_table_token,
310 p_token3_value => 'OKX_LOCATIONS_V');
311
312 -- notify caller of an error
313
314 x_return_status := OKC_API.G_RET_STS_ERROR;
315 END IF;
316 */
317 exception
318 when G_EXCEPTION_HALT_VALIDATION then
319 null;
320
321 when OTHERS then
322 -- store SQL error on message stack for caller
323 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
324 p_msg_name => g_unexpected_error,
325 p_token1 => g_sqlcode_token,
326 p_token1_value => sqlcode,
327 p_token2 => g_sqlerrm_token,
328 p_token2_value => sqlerrm);
329
330 -- notify caller of an UNEXPECTED error
331 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
332
333 /* -- verify that cursor was closed
334 if okl_ilnv_csr%ISOPEN then
335 close okl_ilnv_csr;
336 end if;
337 */
338
339 END validate_iln_ID;
340
341 ------------------------------------
342 -- PROCEDURE validate_date_effective_from
343 ------------------------------------
344 -- Function Name : validate_date_effective_from
345 -- Description : To validate the date effective from
346 -- Business Rules :
347 -- Parameters : Record
348 -- Version : 1.0
349
350 PROCEDURE validate_date_effective_from (
351 x_return_status OUT NOCOPY VARCHAR2,
352 p_dctv_rec IN dctv_rec_type
353 ) IS
354 BEGIN
355 IF p_dctv_rec.date_effective_from = OKC_API.G_MISS_DATE OR
356 p_dctv_rec.date_effective_from IS NULL
357 THEN
358
359 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
360 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
361 p_token1 => 'PROMPT',
362 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_EFFECTIVE_FROM'));
363
364 /*
365 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
366 p_msg_name => g_required_value,
367 p_token1 => g_col_name_token,
368 p_token1_value => 'date_effective_from');
369 */
370 x_return_status := OKC_API.G_RET_STS_ERROR;
371 raise G_EXCEPTION_HALT_VALIDATION;
372 ELSE
373 x_return_status := OKC_API.G_RET_STS_SUCCESS;
374 END IF;
375 exception
376 when G_EXCEPTION_HALT_VALIDATION then
377 null;
378 when OTHERS then
379 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
380 p_msg_name => g_required_value,
381 p_token1 => g_sqlcode_token,
382 p_token1_value => sqlcode,
383 p_token2 => g_sqlerrm_token,
384 p_token2_value => sqlerrm);
385 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
386
387 END validate_date_effective_from;
388
389 -- Start of comments
390 --
391 -- Procedure Name : validate_effective_dates
392 -- Description :
393 -- Business Rules :
394 -- Parameters :
395 -- Version : 1.0
396 -- End of comments
397 PROCEDURE validate_effective_dates(p_dctv_rec IN dctv_rec_type
398 ,x_return_status OUT NOCOPY VARCHAR2) IS
399
400 BEGIN
401 -- initialize return status
402 x_return_status := OKC_API.G_RET_STS_SUCCESS;
403
404 x_return_status := OKL_UTIL.check_from_to_date_range(
405 p_from_date => p_dctv_rec.date_effective_from
406 ,p_to_date => p_dctv_rec.date_effective_to);
407
408 IF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
409 /*
410 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
411 p_msg_name => G_INVALID_VALUE,
412 p_token1 => G_COL_NAME_TOKEN,
413 p_token1_value => 'date_effective_to');
414 */
415 -- Date Effective To DATE_EFFECTIVE_TO cannot be less than Date Effective From DATE_EFFECTIVE_FROM.
416 OKC_API.SET_MESSAGE(p_app_name => 'OKL',
417 p_msg_name => 'OKL_AM_DATE_EFF_FROM_LESS_TO',
418 p_token1 => 'DATE_EFFECTIVE_TO',
419 p_token1_value => p_dctv_rec.date_effective_to,
420 p_token2 => 'DATE_EFFECTIVE_FROM',
421 p_token2_value => p_dctv_rec.date_effective_from);
422
423
424 raise G_EXCEPTION_HALT_VALIDATION;
425
426 ELSIF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
427
428 raise G_EXCEPTION_HALT_VALIDATION;
429 END IF;
430
431 EXCEPTION
432 WHEN G_EXCEPTION_HALT_VALIDATION then
433 -- No action necessary. Validation can continue to next attribute/column
434 null;
435
436 WHEN OTHERS THEN
437 -- store SQL error message on message stack for caller
438 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
439 ,p_msg_name => G_UNEXPECTED_ERROR
440 ,p_token1 => G_SQLCODE_TOKEN
441 ,p_token1_value => sqlcode
442 ,p_token2 => G_SQLERRM_TOKEN
443 ,p_token2_value => sqlerrm);
444
445 -- notify caller of an UNEXPECTED error
446 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
447
448 END validate_effective_dates;
449
450 -- Start of comments
451 --
452 -- Procedure Name : is_unique
453 -- Description :
454 -- Business Rules :
455 -- Parameters :
456 -- Version : 1.0
457 -- End of comments
458 PROCEDURE is_unique(p_dctv_rec IN dctv_rec_type,
459 x_return_status OUT NOCOPY VARCHAR2) IS
460 -- Cursor to get the id if exists
461 CURSOR okl_dctv_id_csr ( p_id NUMBER := OKC_API.G_MISS_NUM) IS
462 SELECT id
463 FROM OKL_DF_CTGY_RMK_TMS
464 WHERE id = p_id;
465
466 -- Cursor to get the db value of date_effective_to for Remarketer and
467 -- Asset_Category to check for uniqueness when create of new rec
468 CURSOR okl_dctv_cre_data_csr (
469 p_rmr_id NUMBER := OKC_API.G_MISS_NUM,
470 p_ico_id NUMBER := OKC_API.G_MISS_NUM) IS
471 SELECT NVL(date_effective_to, OKL_API.G_MISS_DATE)
472 FROM OKL_DF_CTGY_RMK_TMS
473 WHERE rmr_id = p_rmr_id
474 AND ico_id = p_ico_id
475 ORDER BY date_effective_from DESC;
476
477 -- Cursor to get the db value of date_effective_to for Remarketer and
478 -- Asset_Category to check for uniqueness when update of old rec
479 CURSOR okl_dctv_upd_data_csr (
480 p_id NUMBER := OKC_API.G_MISS_NUM,
481 p_rmr_id NUMBER := OKC_API.G_MISS_NUM,
482 p_ico_id NUMBER := OKC_API.G_MISS_NUM) IS
483 SELECT NVL(date_effective_to, OKL_API.G_MISS_DATE)
484 FROM OKL_DF_CTGY_RMK_TMS
485 WHERE rmr_id = p_rmr_id
486 AND ico_id = p_ico_id
487 AND id <> p_id
488 ORDER BY date_effective_from DESC;
489
490 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
491 l_date_effective_to DATE;
492 l_found BOOLEAN;
493 l_id NUMBER;
494 BEGIN
495 -- initialize return status
496 l_return_status := OKC_API.G_RET_STS_SUCCESS;
497 IF (p_dctv_rec.id IS NOT NULL) THEN
498 OPEN okl_dctv_id_csr(p_dctv_rec.id);
499 FETCH okl_dctv_id_csr INTO l_id;
500 -- If id exists then update mode
501 IF okl_dctv_id_csr%FOUND THEN
502 -- enforce uniqueness
503 OPEN okl_dctv_upd_data_csr(p_dctv_rec.id,
504 p_dctv_rec.rmr_id,
505 p_dctv_rec.ico_id);
506 FETCH okl_dctv_upd_data_csr INTO l_date_effective_to;
507 IF okl_dctv_upd_data_csr%FOUND THEN
508 -- Remarketer assignment for same Remarketer and Item Category exists with Date Effective To DATE_EFFECTIVE_TO more than Date Effective From DATE_EFFECTIVE_FROM entered,
509 -- enter a Date Effective From greater than the existing Date Effective To.
510 IF (l_date_effective_to > p_dctv_rec.date_effective_from) THEN
511 OKC_API.SET_MESSAGE( p_app_name => 'OKL'
512 ,p_msg_name => 'OKL_AM_RMK_ASS_DATES_ERR'
513 ,p_token1 => 'DATE_EFFECTIVE_TO'
514 ,p_token1_value => l_date_effective_to
515 ,p_token2 => 'DATE_EFFECTIVE_FROM'
516 ,p_token2_value => p_dctv_rec.date_effective_from);
517 -- notify caller of an error
518 l_return_status := OKC_API.G_RET_STS_ERROR;
519 END IF;
520 END IF;
521 CLOSE okl_dctv_upd_data_csr;
522 ELSE -- id does not exists, so create mode
523 -- enforce uniqueness
524 OPEN okl_dctv_cre_data_csr(p_dctv_rec.rmr_id,
525 p_dctv_rec.ico_id);
526 FETCH okl_dctv_cre_data_csr INTO l_date_effective_to;
527 IF okl_dctv_cre_data_csr%FOUND THEN
528 -- Error if the previous date_effective_to was null
529 IF (l_date_effective_to = OKL_API.G_MISS_DATE)
530 OR (l_date_effective_to IS NULL) THEN
531 -- Remarketer assignment with same Remarketer and Item Category exists with open Date Effective To, so cannot create a new assignment
532 OKC_API.SET_MESSAGE( p_app_name => 'OKL',
533 p_msg_name => 'OKL_AM_RMK_ASS_EXISTS');
534 -- notify caller of an error
535 l_return_status := OKC_API.G_RET_STS_ERROR;
536 -- Remarketer assignment for same Remarketer and Item Category exists with Date Effective To DATE_EFFECTIVE_TO more than Date Effective From DATE_EFFECTIVE_FROM entered,
537 -- enter a Date Effective From greater than the existing Date Effective To.
538 ELSIF (l_date_effective_to > p_dctv_rec.date_effective_from) THEN
539 OKC_API.SET_MESSAGE( p_app_name => 'OKL'
540 ,p_msg_name => 'OKL_AM_RMK_ASS_DATES_ERR'
541 ,p_token1 => 'DATE_EFFECTIVE_TO'
542 ,p_token1_value => l_date_effective_to
543 ,p_token2 => 'DATE_EFFECTIVE_FROM'
544 ,p_token2_value => p_dctv_rec.date_effective_from);
545 -- notify caller of an error
546 l_return_status := OKC_API.G_RET_STS_ERROR;
547 END IF;
548 END IF;
549 CLOSE okl_dctv_cre_data_csr;
550 END IF;
551 CLOSE okl_dctv_id_csr;
552 END IF;
553 x_return_status := l_return_status;
554 EXCEPTION
555 WHEN OTHERS THEN
556 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
557 p_msg_name => g_required_value,
558 p_token1 => g_sqlcode_token,
559 p_token1_value => sqlcode,
560 p_token2 => g_sqlerrm_token,
561 p_token2_value => sqlerrm);
562 -- verify that cursor was closed
563 IF okl_dctv_id_csr%ISOPEN THEN
564 CLOSE okl_dctv_id_csr;
565 END IF;
566 -- verify that cursor was closed
567 IF okl_dctv_cre_data_csr%ISOPEN THEN
568 CLOSE okl_dctv_cre_data_csr;
569 END IF;
570 -- verify that cursor was closed
571 IF okl_dctv_upd_data_csr%ISOPEN THEN
572 CLOSE okl_dctv_upd_data_csr;
573 END IF;
574 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
575 END is_unique;
576 /*
577 PROCEDURE is_unique(p_dctv_rec IN dctv_rec_type,
578 x_return_status OUT NOCOPY VARCHAR2) IS
579 CURSOR okl_dctv_csr ( p_date_effective_from DATE := OKC_API.G_MISS_DATE,
580 p_rmr_id NUMBER := OKC_API.G_MISS_NUM,
581 p_ico_id NUMBER := OKC_API.G_MISS_NUM,
582 p_iln_id NUMBER := OKC_API.G_MISS_NUM)
583 IS
584 SELECT 'x'
585 FROM OKL_DF_CTGY_RMK_TMS
586 WHERE date_effective_from = p_date_effective_from
587 AND rmr_id = p_rmr_id
588 AND ico_id = p_ico_id
589 AND iln_id = p_iln_id;
590
591 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
592 l_dummy VARCHAR2(1);
593 l_found BOOLEAN;
594
595 BEGIN
596 -- initialize return status
597 l_return_status := OKC_API.G_RET_STS_SUCCESS;
598
599 IF (p_dctv_rec.id IS NOT NULL) THEN
600
601 -- enforce uniqueness
602 OPEN okl_dctv_csr(p_dctv_rec.date_effective_from,
603 p_dctv_rec.rmr_id,
604 p_dctv_rec.ico_id,
605 p_dctv_rec.iln_id);
606
607 FETCH okl_dctv_csr INTO l_dummy;
608 l_found := okl_dctv_csr%FOUND;
609 CLOSE okl_dctv_csr;
610
611 END IF;
612
613 IF (l_found) Then
614 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
615 ,p_msg_name => 'OKL_UNIQUE_KEY_EXISTS'
616 ,p_token1 => 'UK_KEY_VALUE'
617 ,p_token1_value => p_dctv_rec.date_effective_from
618 ,p_token2 => 'UK_KEY_VALUE'
619 ,p_token2_value => p_dctv_rec.rmr_id
620 ,p_token3 => 'UK_KEY_VALUE'
621 ,p_token3_value => p_dctv_rec.ico_id
622 ,p_token4 => 'UK_KEY_VALUE'
623 ,p_token4_value => p_dctv_rec.iln_id);
624 -- notify caller of an error
625 l_return_status := OKC_API.G_RET_STS_ERROR;
626 END IF;
627
628 x_return_status := l_return_status;
629 EXCEPTION
630 WHEN OTHERS THEN
631 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
632 p_msg_name => g_required_value,
633 p_token1 => g_sqlcode_token,
634 p_token1_value => sqlcode,
635 p_token2 => g_sqlerrm_token,
636 p_token2_value => sqlerrm);
637
638 -- verify that cursor was closed
639 IF okl_dctv_csr%ISOPEN THEN
640 CLOSE okl_dctv_csr;
641 END IF;
642
643 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
644
645 END is_unique;
646 */
647 ---------------------------------------------------------------------------
648 -- FUNCTION get_seq_id
649 ---------------------------------------------------------------------------
650 FUNCTION get_seq_id RETURN NUMBER IS
651 BEGIN
652 RETURN(okc_p_util.raw_to_number(sys_guid()));
653 END get_seq_id;
654
655 ---------------------------------------------------------------------------
656 -- PROCEDURE qc
657 ---------------------------------------------------------------------------
658 PROCEDURE qc IS
659 BEGIN
660 null;
661 END qc;
662
663 ---------------------------------------------------------------------------
664 -- PROCEDURE change_version
665 ---------------------------------------------------------------------------
666 PROCEDURE change_version IS
667 BEGIN
668 null;
669 END change_version;
670
671 ---------------------------------------------------------------------------
672 -- PROCEDURE api_copy
673 ---------------------------------------------------------------------------
674 PROCEDURE api_copy IS
675 BEGIN
676 null;
677 END api_copy;
678
679 ---------------------------------------------------------------------------
680 -- FUNCTION get_rec for: OKL_DF_CTGY_RMK_TMS
681 ---------------------------------------------------------------------------
682 FUNCTION get_rec (
683 p_dct_rec IN dct_rec_type,
684 x_no_data_found OUT NOCOPY BOOLEAN
685 ) RETURN dct_rec_type IS
686 CURSOR okl_df_ctgy_rmk_tms_pk_csr (p_id IN NUMBER) IS
687 SELECT
688 ID,
689 ORG_ID,
690 RMR_ID,
691 ICO_ID,
692 ILN_ID,
693 DATE_EFFECTIVE_FROM,
694 OBJECT_VERSION_NUMBER,
695 CREATED_BY,
696 DATE_EFFECTIVE_TO,
697 CREATION_DATE,
698 LAST_UPDATED_BY,
699 LAST_UPDATE_DATE,
700 LAST_UPDATE_LOGIN
701 FROM Okl_Df_Ctgy_Rmk_Tms
702 WHERE okl_df_ctgy_rmk_tms.id = p_id;
703 l_okl_df_ctgy_rmk_tms_pk okl_df_ctgy_rmk_tms_pk_csr%ROWTYPE;
704 l_dct_rec dct_rec_type;
705 BEGIN
706 x_no_data_found := TRUE;
707 -- Get current database values
708 OPEN okl_df_ctgy_rmk_tms_pk_csr (p_dct_rec.id);
709 FETCH okl_df_ctgy_rmk_tms_pk_csr INTO
710 l_dct_rec.ID,
711 l_dct_rec.ORG_ID,
712 l_dct_rec.RMR_ID,
713 l_dct_rec.ICO_ID,
714 l_dct_rec.ILN_ID,
715 l_dct_rec.DATE_EFFECTIVE_FROM,
716 l_dct_rec.OBJECT_VERSION_NUMBER,
717 l_dct_rec.CREATED_BY,
718 l_dct_rec.DATE_EFFECTIVE_TO,
719 l_dct_rec.CREATION_DATE,
720 l_dct_rec.LAST_UPDATED_BY,
721 l_dct_rec.LAST_UPDATE_DATE,
722 l_dct_rec.LAST_UPDATE_LOGIN;
723 x_no_data_found := okl_df_ctgy_rmk_tms_pk_csr%NOTFOUND;
724 CLOSE okl_df_ctgy_rmk_tms_pk_csr;
725 RETURN(l_dct_rec);
726 END get_rec;
727
728 FUNCTION get_rec (
729 p_dct_rec IN dct_rec_type
730 ) RETURN dct_rec_type IS
731 l_row_notfound BOOLEAN := TRUE;
732 BEGIN
733 RETURN(get_rec(p_dct_rec, l_row_notfound));
734 END get_rec;
735 ---------------------------------------------------------------------------
736 -- FUNCTION get_rec for: OKL_DF_CTGY_RMK_TMS_V
737 ---------------------------------------------------------------------------
738 FUNCTION get_rec (
739 p_dctv_rec IN dctv_rec_type,
740 x_no_data_found OUT NOCOPY BOOLEAN
741 ) RETURN dctv_rec_type IS
742 CURSOR okl_dctv_pk_csr (p_id IN NUMBER) IS
743 SELECT
744 ID,
745 OBJECT_VERSION_NUMBER,
746 ORG_ID,
747 RMR_ID,
748 ICO_ID,
749 ILN_ID,
750 DATE_EFFECTIVE_FROM,
751 DATE_EFFECTIVE_TO,
752 CREATED_BY,
753 CREATION_DATE,
754 LAST_UPDATED_BY,
755 LAST_UPDATE_DATE,
756 LAST_UPDATE_LOGIN
757 FROM OKL_DF_CTGY_RMK_TMS
758 WHERE OKL_DF_CTGY_RMK_TMS.id = p_id;
759 l_okl_dctv_pk okl_dctv_pk_csr%ROWTYPE;
760 l_dctv_rec dctv_rec_type;
761 BEGIN
762 x_no_data_found := TRUE;
763 -- Get current database values
764 OPEN okl_dctv_pk_csr (p_dctv_rec.id);
765 FETCH okl_dctv_pk_csr INTO
766 l_dctv_rec.ID,
767 l_dctv_rec.OBJECT_VERSION_NUMBER,
768 l_dctv_rec.ORG_ID,
769 l_dctv_rec.RMR_ID,
770 l_dctv_rec.ICO_ID,
771 l_dctv_rec.ILN_ID,
772 l_dctv_rec.DATE_EFFECTIVE_FROM,
773 l_dctv_rec.DATE_EFFECTIVE_TO,
774 l_dctv_rec.CREATED_BY,
775 l_dctv_rec.CREATION_DATE,
776 l_dctv_rec.LAST_UPDATED_BY,
777 l_dctv_rec.LAST_UPDATE_DATE,
778 l_dctv_rec.LAST_UPDATE_LOGIN;
779 x_no_data_found := okl_dctv_pk_csr%NOTFOUND;
780 CLOSE okl_dctv_pk_csr;
781 RETURN(l_dctv_rec);
782 END get_rec;
783
784 FUNCTION get_rec (
785 p_dctv_rec IN dctv_rec_type
786 ) RETURN dctv_rec_type IS
787 l_row_notfound BOOLEAN := TRUE;
788 BEGIN
789 RETURN(get_rec(p_dctv_rec, l_row_notfound));
790 END get_rec;
791
792 -----------------------------------------------------------
793 -- FUNCTION null_out_defaults for: OKL_DF_CTGY_RMK_TMS_V --
794 -----------------------------------------------------------
795 FUNCTION null_out_defaults (
796 p_dctv_rec IN dctv_rec_type
797 ) RETURN dctv_rec_type IS
798 l_dctv_rec dctv_rec_type := p_dctv_rec;
799 BEGIN
800 IF (l_dctv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
801 l_dctv_rec.object_version_number := NULL;
802 END IF;
803 IF (l_dctv_rec.org_id = OKC_API.G_MISS_NUM) THEN
804 l_dctv_rec.org_id := NULL;
805 END IF;
806 IF (l_dctv_rec.rmr_id = OKC_API.G_MISS_NUM) THEN
807 l_dctv_rec.rmr_id := NULL;
808 END IF;
809 IF (l_dctv_rec.ico_id = OKC_API.G_MISS_NUM) THEN
810 l_dctv_rec.ico_id := NULL;
811 END IF;
812 IF (l_dctv_rec.iln_id = OKC_API.G_MISS_NUM) THEN
813 l_dctv_rec.iln_id := NULL;
814 END IF;
815 IF (l_dctv_rec.date_effective_from = OKC_API.G_MISS_DATE) THEN
816 l_dctv_rec.date_effective_from := NULL;
817 END IF;
818 IF (l_dctv_rec.date_effective_to = OKC_API.G_MISS_DATE) THEN
819 l_dctv_rec.date_effective_to := NULL;
820 END IF;
821 IF (l_dctv_rec.created_by = OKC_API.G_MISS_NUM) THEN
822 l_dctv_rec.created_by := NULL;
823 END IF;
824 IF (l_dctv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
825 l_dctv_rec.creation_date := NULL;
826 END IF;
827 IF (l_dctv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
828 l_dctv_rec.last_updated_by := NULL;
829 END IF;
830 IF (l_dctv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
831 l_dctv_rec.last_update_date := NULL;
832 END IF;
833 IF (l_dctv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
834 l_dctv_rec.last_update_login := NULL;
835 END IF;
836 RETURN(l_dctv_rec);
837 END null_out_defaults;
838 ---------------------------------------------------------------------------
839 -- PROCEDURE Validate_Attributes
840 ---------------------------------------------------------------------------
841 ---------------------------------------------------
842 -- Validate_Attributes for:OKL_DF_CTGY_RMK_TMS_V --
843 ---------------------------------------------------
844 FUNCTION Validate_Attributes (
845 p_dctv_rec IN dctv_rec_type
846 ) RETURN VARCHAR2 IS
847 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
848 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
849 BEGIN
850 validate_id(x_return_status => l_return_status,
851 p_dctv_rec => p_dctv_rec);
852
853 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
854 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
855 x_return_status := l_return_status;
856 end if;
857 end if;
858
859 validate_object_version_number(x_return_status => l_return_status,
860 p_dctv_rec => p_dctv_rec);
861
862 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
863 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
864 x_return_status := l_return_status;
865 end if;
866 end if;
867
868 validate_rmr_id(x_return_status => l_return_status,
869 p_dctv_rec => p_dctv_rec);
870
871 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
872 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
873 x_return_status := l_return_status;
874 end if;
875 end if;
876
877 validate_ico_id(x_return_status => l_return_status,
878 p_dctv_rec => p_dctv_rec);
879
880 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
881 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
882 x_return_status := l_return_status;
883 end if;
884 end if;
885 /*
886 validate_iln_id(x_return_status => l_return_status,
887 p_dctv_rec => p_dctv_rec);
888
889 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
890 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
891 x_return_status := l_return_status;
892 end if;
893 end if;
894 */
895 validate_date_effective_from(x_return_status => l_return_status,
896 p_dctv_rec => p_dctv_rec);
897
898 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
899 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
900 x_return_status := l_return_status;
901 end if;
902 end if;
903
904 /* validate_fk_rmr_id(x_return_status => l_return_status,
905 p_dctv_rec => p_dctv_rec);
906
907 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
908 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
909 x_return_status := l_return_status;
910 end if;
911 end if;
912
913 validate_fk_ico_id(x_return_status => l_return_status,
914 p_dctv_rec => p_dctv_rec);
915
916 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
917 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
918 x_return_status := l_return_status;
919 end if;
920 end if;
921
922
923 validate_fk_iln_id(x_return_status => l_return_status,
924 p_dctv_rec => p_dctv_rec);
925
926 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
927 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
928 x_return_status := l_return_status;
929 end if;
930 end if;
931 */
932 RETURN(x_return_status);
933 EXCEPTION
934 WHEN OTHERS THEN
935 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
936 ,p_msg_name => G_UNEXPECTED_ERROR
937 ,p_token1 => G_SQLCODE_TOKEN
938 ,p_token1_value => SQLCODE
939 ,p_token2 => G_SQLERRM_TOKEN
940 ,p_token2_value => SQLERRM);
941 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
942 RETURN(x_return_status);
943 END Validate_Attributes;
944
945 ---------------------------------------------------------------------------
946 -- PROCEDURE Validate_Record
947 ---------------------------------------------------------------------------
948 -----------------------------------------------
949 -- Validate_Record for:OKL_DF_CTGY_RMK_TMS_V --
950 -----------------------------------------------
951 FUNCTION Validate_Record (
952 p_dctv_rec IN dctv_rec_type
953 ) RETURN VARCHAR2 IS
954 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
955 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
956 BEGIN
957
958 -- check uniqueness
959 is_unique(p_dctv_rec,l_return_status);
960
961 -- store the highest degree of error
962 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
963 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
964 x_return_status := l_return_status;
965 END IF;
966 END IF;
967
968 --
969 -- Date checks
970 --
971 validate_effective_dates(p_dctv_rec => p_dctv_rec
972 ,x_return_status => l_return_status);
973
974 -- store the highest degree of error
975 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
976 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
977 x_return_status := l_return_status;
978 END IF;
979 END IF;
980
981 RETURN (x_return_status);
982
983 EXCEPTION
984 WHEN OTHERS THEN
985 -- store SQL error message on message stack for caller
986 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
987 ,p_msg_name => G_UNEXPECTED_ERROR
988 ,p_token1 => G_SQLCODE_TOKEN
989 ,p_token1_value => sqlcode
990 ,p_token2 => G_SQLERRM_TOKEN
991 ,p_token2_value => sqlerrm);
992
993 --notify caller of an UNEXPECTED error
994 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
995
996 --return status to caller
997 RETURN x_return_status;
998
999 END validate_record;
1000
1001 ---------------------------------------------------------------------------
1002 -- PROCEDURE Migrate
1003 ---------------------------------------------------------------------------
1004 PROCEDURE migrate (
1005 p_from IN dctv_rec_type,
1006 p_to IN OUT NOCOPY dct_rec_type
1007 ) IS
1008 BEGIN
1009 p_to.id := p_from.id;
1010 p_to.org_id := p_from.org_id;
1011 p_to.rmr_id := p_from.rmr_id;
1012 p_to.ico_id := p_from.ico_id;
1013 p_to.iln_id := p_from.iln_id;
1014 p_to.date_effective_from := p_from.date_effective_from;
1015 p_to.object_version_number := p_from.object_version_number;
1016 p_to.created_by := p_from.created_by;
1017 p_to.date_effective_to := p_from.date_effective_to;
1018 p_to.creation_date := p_from.creation_date;
1019 p_to.last_updated_by := p_from.last_updated_by;
1020 p_to.last_update_date := p_from.last_update_date;
1021 p_to.last_update_login := p_from.last_update_login;
1022 END migrate;
1023 PROCEDURE migrate (
1024 p_from IN dct_rec_type,
1025 p_to IN OUT NOCOPY dctv_rec_type
1026 ) IS
1027 BEGIN
1028 p_to.id := p_from.id;
1029 p_to.org_id := p_from.org_id;
1030 p_to.rmr_id := p_from.rmr_id;
1031 p_to.ico_id := p_from.ico_id;
1032 p_to.iln_id := p_from.iln_id;
1033 p_to.date_effective_from := p_from.date_effective_from;
1034 p_to.object_version_number := p_from.object_version_number;
1035 p_to.created_by := p_from.created_by;
1036 p_to.date_effective_to := p_from.date_effective_to;
1037 p_to.creation_date := p_from.creation_date;
1038 p_to.last_updated_by := p_from.last_updated_by;
1039 p_to.last_update_date := p_from.last_update_date;
1040 p_to.last_update_login := p_from.last_update_login;
1041 END migrate;
1042
1043 ---------------------------------------------------------------------------
1044 -- PROCEDURE validate_row
1045 ---------------------------------------------------------------------------
1046 --------------------------------------------
1047 -- validate_row for:OKL_DF_CTGY_RMK_TMS_V --
1048 --------------------------------------------
1049 PROCEDURE validate_row(
1050 p_api_version IN NUMBER,
1051 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1052 x_return_status OUT NOCOPY VARCHAR2,
1053 x_msg_count OUT NOCOPY NUMBER,
1054 x_msg_data OUT NOCOPY VARCHAR2,
1055 p_dctv_rec IN dctv_rec_type) IS
1056
1057 l_api_version CONSTANT NUMBER := 1;
1058 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1059 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1060 l_dctv_rec dctv_rec_type := p_dctv_rec;
1061 l_dct_rec dct_rec_type;
1062 BEGIN
1063 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1064 G_PKG_NAME,
1065 p_init_msg_list,
1066 l_api_version,
1067 p_api_version,
1068 '_PVT',
1069 x_return_status);
1070 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1071 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1072 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1073 RAISE OKC_API.G_EXCEPTION_ERROR;
1074 END IF;
1075 --- Validate all non-missing attributes (Item Level Validation)
1076 l_return_status := Validate_Attributes(l_dctv_rec);
1077 --- If any errors happen abort API
1078 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1079 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1080 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1081 RAISE OKC_API.G_EXCEPTION_ERROR;
1082 END IF;
1083 l_return_status := Validate_Record(l_dctv_rec);
1084 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1085 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1086 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1087 RAISE OKC_API.G_EXCEPTION_ERROR;
1088 END IF;
1089 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1090 EXCEPTION
1091 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1092 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1093 (
1094 l_api_name,
1095 G_PKG_NAME,
1096 'OKC_API.G_RET_STS_ERROR',
1097 x_msg_count,
1098 x_msg_data,
1099 '_PVT'
1100 );
1101 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1102 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1103 (
1104 l_api_name,
1105 G_PKG_NAME,
1106 'OKC_API.G_RET_STS_UNEXP_ERROR',
1107 x_msg_count,
1108 x_msg_data,
1109 '_PVT'
1110 );
1111 WHEN OTHERS THEN
1112 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1113 (
1114 l_api_name,
1115 G_PKG_NAME,
1116 'OTHERS',
1117 x_msg_count,
1118 x_msg_data,
1119 '_PVT'
1120 );
1121 END validate_row;
1122 ------------------------------------------
1123 -- PL/SQL TBL validate_row for:DCTV_TBL --
1124 ------------------------------------------
1125 PROCEDURE validate_row(
1126 p_api_version IN NUMBER,
1127 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1128 x_return_status OUT NOCOPY VARCHAR2,
1129 x_msg_count OUT NOCOPY NUMBER,
1130 x_msg_data OUT NOCOPY VARCHAR2,
1131 p_dctv_tbl IN dctv_tbl_type) IS
1132
1133 l_api_version CONSTANT NUMBER := 1;
1134 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1135 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1136 i NUMBER := 0;
1137
1138 -- Begin Post-Generation Change
1139 -- overall error status
1140 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1141 -- End Post-Generation Change
1142 BEGIN
1143 OKC_API.init_msg_list(p_init_msg_list);
1144 -- Make sure PL/SQL table has records in it before passing
1145 IF (p_dctv_tbl.COUNT > 0) THEN
1146 i := p_dctv_tbl.FIRST;
1147 LOOP
1148 validate_row (
1149 p_api_version => p_api_version,
1150 p_init_msg_list => OKC_API.G_FALSE,
1151 x_return_status => x_return_status,
1152 x_msg_count => x_msg_count,
1153 x_msg_data => x_msg_data,
1154 p_dctv_rec => p_dctv_tbl(i));
1155 -- Begin Post-Generation Change
1156 -- store the highest degree of error
1157 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1158 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1159 l_overall_status := x_return_status;
1160 END IF;
1161 END IF;
1162 -- End Post-Generation Change
1163 EXIT WHEN (i = p_dctv_tbl.LAST);
1164 i := p_dctv_tbl.NEXT(i);
1165 END LOOP;
1166 -- Begin Post-Generation Change
1167 -- return overall status
1168 x_return_status := l_overall_status;
1169 -- End Post-Generation Change
1170 END IF;
1171 EXCEPTION
1172 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1173 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1174 (
1175 l_api_name,
1176 G_PKG_NAME,
1177 'OKC_API.G_RET_STS_ERROR',
1178 x_msg_count,
1179 x_msg_data,
1180 '_PVT'
1181 );
1182 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1183 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1184 (
1185 l_api_name,
1186 G_PKG_NAME,
1187 'OKC_API.G_RET_STS_UNEXP_ERROR',
1188 x_msg_count,
1189 x_msg_data,
1190 '_PVT'
1191 );
1192 WHEN OTHERS THEN
1193 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1194 (
1195 l_api_name,
1196 G_PKG_NAME,
1197 'OTHERS',
1198 x_msg_count,
1199 x_msg_data,
1200 '_PVT'
1201 );
1202 END validate_row;
1203
1204 ---------------------------------------------------------------------------
1205 -- PROCEDURE insert_row
1206 ---------------------------------------------------------------------------
1207 ----------------------------------------
1208 -- insert_row for:OKL_DF_CTGY_RMK_TMS --
1209 ----------------------------------------
1210 PROCEDURE insert_row(
1211 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1212 x_return_status OUT NOCOPY VARCHAR2,
1213 x_msg_count OUT NOCOPY NUMBER,
1214 x_msg_data OUT NOCOPY VARCHAR2,
1215 p_dct_rec IN dct_rec_type,
1216 x_dct_rec OUT NOCOPY dct_rec_type) IS
1217
1218 l_api_version CONSTANT NUMBER := 1;
1219 l_api_name CONSTANT VARCHAR2(30) := 'TMS_insert_row';
1220 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1221 l_dct_rec dct_rec_type := p_dct_rec;
1222 l_def_dct_rec dct_rec_type;
1223 --------------------------------------------
1224 -- Set_Attributes for:OKL_DF_CTGY_RMK_TMS --
1225 --------------------------------------------
1226 FUNCTION Set_Attributes (
1227 p_dct_rec IN dct_rec_type,
1228 x_dct_rec OUT NOCOPY dct_rec_type
1229 ) RETURN VARCHAR2 IS
1230 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1231 BEGIN
1232 x_dct_rec := p_dct_rec;
1233 RETURN(l_return_status);
1234 END Set_Attributes;
1235 BEGIN
1236 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1237 p_init_msg_list,
1238 '_PVT',
1239 x_return_status);
1240 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1241 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1242 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1243 RAISE OKC_API.G_EXCEPTION_ERROR;
1244 END IF;
1245 --- Setting item attributes
1246 l_return_status := Set_Attributes(
1247 p_dct_rec, -- IN
1248 l_dct_rec); -- OUT
1249 --- If any errors happen abort API
1250 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1251 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1252 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1253 RAISE OKC_API.G_EXCEPTION_ERROR;
1254 END IF;
1255 INSERT INTO OKL_DF_CTGY_RMK_TMS(
1256 id,
1257 org_id,
1258 rmr_id,
1259 ico_id,
1260 iln_id,
1261 date_effective_from,
1262 object_version_number,
1263 created_by,
1264 date_effective_to,
1265 creation_date,
1266 last_updated_by,
1267 last_update_date,
1268 last_update_login)
1269 VALUES (
1270 l_dct_rec.id,
1271 l_dct_rec.org_id,
1272 l_dct_rec.rmr_id,
1273 l_dct_rec.ico_id,
1274 l_dct_rec.iln_id,
1275 l_dct_rec.date_effective_from,
1276 l_dct_rec.object_version_number,
1277 l_dct_rec.created_by,
1278 l_dct_rec.date_effective_to,
1279 l_dct_rec.creation_date,
1280 l_dct_rec.last_updated_by,
1281 l_dct_rec.last_update_date,
1282 l_dct_rec.last_update_login);
1283 -- Set OUT values
1284 x_dct_rec := l_dct_rec;
1285 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1286 EXCEPTION
1287 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1288 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1289 (
1290 l_api_name,
1291 G_PKG_NAME,
1292 'OKC_API.G_RET_STS_ERROR',
1293 x_msg_count,
1294 x_msg_data,
1295 '_PVT'
1296 );
1297 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1298 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1299 (
1300 l_api_name,
1301 G_PKG_NAME,
1302 'OKC_API.G_RET_STS_UNEXP_ERROR',
1303 x_msg_count,
1304 x_msg_data,
1305 '_PVT'
1306 );
1307 WHEN OTHERS THEN
1308 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1309 (
1310 l_api_name,
1311 G_PKG_NAME,
1312 'OTHERS',
1313 x_msg_count,
1314 x_msg_data,
1315 '_PVT'
1316 );
1317 END insert_row;
1318 ------------------------------------------
1319 -- insert_row for:OKL_DF_CTGY_RMK_TMS_V --
1320 ------------------------------------------
1321 PROCEDURE insert_row(
1322 p_api_version IN NUMBER,
1323 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1324 x_return_status OUT NOCOPY VARCHAR2,
1325 x_msg_count OUT NOCOPY NUMBER,
1326 x_msg_data OUT NOCOPY VARCHAR2,
1327 p_dctv_rec IN dctv_rec_type,
1328 x_dctv_rec OUT NOCOPY dctv_rec_type) IS
1329
1330 l_api_version CONSTANT NUMBER := 1;
1331 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1332 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1333 l_dctv_rec dctv_rec_type;
1334 l_def_dctv_rec dctv_rec_type;
1335 l_dct_rec dct_rec_type;
1336 lx_dct_rec dct_rec_type;
1337 -------------------------------
1338 -- FUNCTION fill_who_columns --
1339 -------------------------------
1340 FUNCTION fill_who_columns (
1341 p_dctv_rec IN dctv_rec_type
1342 ) RETURN dctv_rec_type IS
1343 l_dctv_rec dctv_rec_type := p_dctv_rec;
1344 BEGIN
1345 l_dctv_rec.CREATION_DATE := SYSDATE;
1346 l_dctv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1347 l_dctv_rec.LAST_UPDATE_DATE := SYSDATE;
1348 l_dctv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1349 l_dctv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1350 RETURN(l_dctv_rec);
1351 END fill_who_columns;
1352 ----------------------------------------------
1353 -- Set_Attributes for:OKL_DF_CTGY_RMK_TMS_V --
1354 ----------------------------------------------
1355 FUNCTION Set_Attributes (
1356 p_dctv_rec IN dctv_rec_type,
1357 x_dctv_rec OUT NOCOPY dctv_rec_type
1358 ) RETURN VARCHAR2 IS
1359 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1360 BEGIN
1361 x_dctv_rec := p_dctv_rec;
1362 x_dctv_rec.OBJECT_VERSION_NUMBER := 1;
1363 -- Default the ORG ID if a value is not passed
1364 IF p_dctv_rec.org_id IS NULL
1365 OR p_dctv_rec.org_id = OKC_API.G_MISS_NUM THEN
1366 x_dctv_rec.org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
1367 END IF;
1368 RETURN(l_return_status);
1369 END Set_Attributes;
1370 BEGIN
1371 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1372 G_PKG_NAME,
1373 p_init_msg_list,
1374 l_api_version,
1375 p_api_version,
1376 '_PVT',
1377 x_return_status);
1378 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1379 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1380 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1381 RAISE OKC_API.G_EXCEPTION_ERROR;
1382 END IF;
1383 l_dctv_rec := null_out_defaults(p_dctv_rec);
1384 -- Set primary key value
1385 l_dctv_rec.ID := get_seq_id;
1386 --- Setting item attributes
1387 l_return_status := Set_Attributes(
1388 l_dctv_rec, -- IN
1389 l_def_dctv_rec); -- OUT
1390 --- If any errors happen abort API
1391 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1392 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1393 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1394 RAISE OKC_API.G_EXCEPTION_ERROR;
1395 END IF;
1396 l_def_dctv_rec := fill_who_columns(l_def_dctv_rec);
1397 --- Validate all non-missing attributes (Item Level Validation)
1398 l_return_status := Validate_Attributes(l_def_dctv_rec);
1399 --- If any errors happen abort API
1400 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1401 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1402 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1403 RAISE OKC_API.G_EXCEPTION_ERROR;
1404 END IF;
1405 l_return_status := Validate_Record(l_def_dctv_rec);
1406 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1407 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1408 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1409 RAISE OKC_API.G_EXCEPTION_ERROR;
1410 END IF;
1411 --------------------------------------
1412 -- Move VIEW record to "Child" records
1413 --------------------------------------
1414 migrate(l_def_dctv_rec, l_dct_rec);
1415 --------------------------------------------
1416 -- Call the INSERT_ROW for each child record
1417 --------------------------------------------
1418 insert_row(
1419 p_init_msg_list,
1420 x_return_status,
1421 x_msg_count,
1422 x_msg_data,
1423 l_dct_rec,
1424 lx_dct_rec
1425 );
1426 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1427 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1428 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1429 RAISE OKC_API.G_EXCEPTION_ERROR;
1430 END IF;
1431 migrate(lx_dct_rec, l_def_dctv_rec);
1432 -- Set OUT values
1433 x_dctv_rec := l_def_dctv_rec;
1434 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1435 EXCEPTION
1436 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1437 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1438 (
1439 l_api_name,
1440 G_PKG_NAME,
1441 'OKC_API.G_RET_STS_ERROR',
1442 x_msg_count,
1443 x_msg_data,
1444 '_PVT'
1445 );
1446 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1447 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1448 (
1449 l_api_name,
1450 G_PKG_NAME,
1451 'OKC_API.G_RET_STS_UNEXP_ERROR',
1452 x_msg_count,
1453 x_msg_data,
1454 '_PVT'
1455 );
1456 WHEN OTHERS THEN
1457 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1458 (
1459 l_api_name,
1460 G_PKG_NAME,
1461 'OTHERS',
1462 x_msg_count,
1463 x_msg_data,
1464 '_PVT'
1465 );
1466 END insert_row;
1467 ----------------------------------------
1468 -- PL/SQL TBL insert_row for:DCTV_TBL --
1469 ----------------------------------------
1470 PROCEDURE insert_row(
1471 p_api_version IN NUMBER,
1472 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1473 x_return_status OUT NOCOPY VARCHAR2,
1474 x_msg_count OUT NOCOPY NUMBER,
1475 x_msg_data OUT NOCOPY VARCHAR2,
1476 p_dctv_tbl IN dctv_tbl_type,
1477 x_dctv_tbl OUT NOCOPY dctv_tbl_type) IS
1478
1479 l_api_version CONSTANT NUMBER := 1;
1480 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1481 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1482 i NUMBER := 0;
1483
1484 -- Begin Post-Generation Change
1485 -- overall error status
1486 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1487 -- End Post-Generation Change
1488 BEGIN
1489 OKC_API.init_msg_list(p_init_msg_list);
1490 -- Make sure PL/SQL table has records in it before passing
1491 IF (p_dctv_tbl.COUNT > 0) THEN
1492 i := p_dctv_tbl.FIRST;
1493 LOOP
1494 insert_row (
1495 p_api_version => p_api_version,
1496 p_init_msg_list => OKC_API.G_FALSE,
1497 x_return_status => x_return_status,
1498 x_msg_count => x_msg_count,
1499 x_msg_data => x_msg_data,
1500 p_dctv_rec => p_dctv_tbl(i),
1501 x_dctv_rec => x_dctv_tbl(i));
1502 -- Begin Post-Generation Change
1503 -- store the highest degree of error
1504 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1505 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1506 l_overall_status := x_return_status;
1507 END IF;
1508 END IF;
1509 -- End Post-Generation Change
1510 EXIT WHEN (i = p_dctv_tbl.LAST);
1511 i := p_dctv_tbl.NEXT(i);
1512 END LOOP;
1513 -- Begin Post-Generation Change
1514 -- return overall status
1515 x_return_status := l_overall_status;
1516 -- End Post-Generation Change
1517 END IF;
1518 EXCEPTION
1519 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1520 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1521 (
1522 l_api_name,
1523 G_PKG_NAME,
1524 'OKC_API.G_RET_STS_ERROR',
1525 x_msg_count,
1526 x_msg_data,
1527 '_PVT'
1528 );
1529 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1530 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1531 (
1532 l_api_name,
1533 G_PKG_NAME,
1534 'OKC_API.G_RET_STS_UNEXP_ERROR',
1535 x_msg_count,
1536 x_msg_data,
1537 '_PVT'
1538 );
1539 WHEN OTHERS THEN
1540 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1541 (
1542 l_api_name,
1543 G_PKG_NAME,
1544 'OTHERS',
1545 x_msg_count,
1546 x_msg_data,
1547 '_PVT'
1548 );
1549 END insert_row;
1550
1551 ---------------------------------------------------------------------------
1552 -- PROCEDURE lock_row
1553 ---------------------------------------------------------------------------
1554 --------------------------------------
1555 -- lock_row for:OKL_DF_CTGY_RMK_TMS --
1556 --------------------------------------
1557 PROCEDURE lock_row(
1558 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1559 x_return_status OUT NOCOPY VARCHAR2,
1560 x_msg_count OUT NOCOPY NUMBER,
1561 x_msg_data OUT NOCOPY VARCHAR2,
1562 p_dct_rec IN dct_rec_type) IS
1563
1564 E_Resource_Busy EXCEPTION;
1565 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1566 CURSOR lock_csr (p_dct_rec IN dct_rec_type) IS
1567 SELECT OBJECT_VERSION_NUMBER
1568 FROM OKL_DF_CTGY_RMK_TMS
1569 WHERE ID = p_dct_rec.id
1570 AND OBJECT_VERSION_NUMBER = p_dct_rec.object_version_number
1571 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1572
1573 CURSOR lchk_csr (p_dct_rec IN dct_rec_type) IS
1574 SELECT OBJECT_VERSION_NUMBER
1575 FROM OKL_DF_CTGY_RMK_TMS
1576 WHERE ID = p_dct_rec.id;
1577 l_api_version CONSTANT NUMBER := 1;
1578 l_api_name CONSTANT VARCHAR2(30) := 'TMS_lock_row';
1579 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1580 l_object_version_number OKL_DF_CTGY_RMK_TMS.OBJECT_VERSION_NUMBER%TYPE;
1581 lc_object_version_number OKL_DF_CTGY_RMK_TMS.OBJECT_VERSION_NUMBER%TYPE;
1582 l_row_notfound BOOLEAN := FALSE;
1583 lc_row_notfound BOOLEAN := FALSE;
1584 BEGIN
1585 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1586 p_init_msg_list,
1587 '_PVT',
1588 x_return_status);
1589 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1590 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1591 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1592 RAISE OKC_API.G_EXCEPTION_ERROR;
1593 END IF;
1594 BEGIN
1595 OPEN lock_csr(p_dct_rec);
1596 FETCH lock_csr INTO l_object_version_number;
1597 l_row_notfound := lock_csr%NOTFOUND;
1598 CLOSE lock_csr;
1599 EXCEPTION
1600 WHEN E_Resource_Busy THEN
1601 IF (lock_csr%ISOPEN) THEN
1602 CLOSE lock_csr;
1603 END IF;
1604 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1605 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1606 END;
1607
1608 IF ( l_row_notfound ) THEN
1609 OPEN lchk_csr(p_dct_rec);
1610 FETCH lchk_csr INTO lc_object_version_number;
1611 lc_row_notfound := lchk_csr%NOTFOUND;
1612 CLOSE lchk_csr;
1613 END IF;
1614 IF (lc_row_notfound) THEN
1615 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1616 RAISE OKC_API.G_EXCEPTION_ERROR;
1617 ELSIF lc_object_version_number > p_dct_rec.object_version_number THEN
1618 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1619 RAISE OKC_API.G_EXCEPTION_ERROR;
1620 ELSIF lc_object_version_number <> p_dct_rec.object_version_number THEN
1621 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1622 RAISE OKC_API.G_EXCEPTION_ERROR;
1623 ELSIF lc_object_version_number = -1 THEN
1624 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1625 RAISE OKC_API.G_EXCEPTION_ERROR;
1626 END IF;
1627 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1628 EXCEPTION
1629 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1630 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1631 (
1632 l_api_name,
1633 G_PKG_NAME,
1634 'OKC_API.G_RET_STS_ERROR',
1635 x_msg_count,
1636 x_msg_data,
1637 '_PVT'
1638 );
1639 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1640 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1641 (
1642 l_api_name,
1643 G_PKG_NAME,
1644 'OKC_API.G_RET_STS_UNEXP_ERROR',
1645 x_msg_count,
1646 x_msg_data,
1647 '_PVT'
1648 );
1649 WHEN OTHERS THEN
1650 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1651 (
1652 l_api_name,
1653 G_PKG_NAME,
1654 'OTHERS',
1655 x_msg_count,
1656 x_msg_data,
1657 '_PVT'
1658 );
1659 END lock_row;
1660 ----------------------------------------
1661 -- lock_row for:OKL_DF_CTGY_RMK_TMS_V --
1662 ----------------------------------------
1663 PROCEDURE lock_row(
1664 p_api_version IN NUMBER,
1665 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1666 x_return_status OUT NOCOPY VARCHAR2,
1667 x_msg_count OUT NOCOPY NUMBER,
1668 x_msg_data OUT NOCOPY VARCHAR2,
1669 p_dctv_rec IN dctv_rec_type) IS
1670
1671 l_api_version CONSTANT NUMBER := 1;
1672 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1673 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1674 l_dct_rec dct_rec_type;
1675 BEGIN
1676 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1677 G_PKG_NAME,
1678 p_init_msg_list,
1679 l_api_version,
1680 p_api_version,
1681 '_PVT',
1682 x_return_status);
1683 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1684 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1685 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1686 RAISE OKC_API.G_EXCEPTION_ERROR;
1687 END IF;
1688 --------------------------------------
1689 -- Move VIEW record to "Child" records
1690 --------------------------------------
1691 migrate(p_dctv_rec, l_dct_rec);
1692 --------------------------------------------
1693 -- Call the LOCK_ROW for each child record
1694 --------------------------------------------
1695 lock_row(
1696 p_init_msg_list,
1697 x_return_status,
1698 x_msg_count,
1699 x_msg_data,
1700 l_dct_rec
1701 );
1702 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1703 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1704 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1705 RAISE OKC_API.G_EXCEPTION_ERROR;
1706 END IF;
1707 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1708 EXCEPTION
1709 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1710 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1711 (
1712 l_api_name,
1713 G_PKG_NAME,
1714 'OKC_API.G_RET_STS_ERROR',
1715 x_msg_count,
1716 x_msg_data,
1717 '_PVT'
1718 );
1719 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1720 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1721 (
1722 l_api_name,
1723 G_PKG_NAME,
1724 'OKC_API.G_RET_STS_UNEXP_ERROR',
1725 x_msg_count,
1726 x_msg_data,
1727 '_PVT'
1728 );
1729 WHEN OTHERS THEN
1730 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1731 (
1732 l_api_name,
1733 G_PKG_NAME,
1734 'OTHERS',
1735 x_msg_count,
1736 x_msg_data,
1737 '_PVT'
1738 );
1739 END lock_row;
1740 --------------------------------------
1741 -- PL/SQL TBL lock_row for:DCTV_TBL --
1742 --------------------------------------
1743 PROCEDURE lock_row(
1744 p_api_version IN NUMBER,
1745 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1746 x_return_status OUT NOCOPY VARCHAR2,
1747 x_msg_count OUT NOCOPY NUMBER,
1748 x_msg_data OUT NOCOPY VARCHAR2,
1749 p_dctv_tbl IN dctv_tbl_type) IS
1750
1751 l_api_version CONSTANT NUMBER := 1;
1752 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1753 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1754 i NUMBER := 0;
1755 -- Begin Post-Generation Change
1756 -- overall error status
1757 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1758 -- End Post-Generation Change
1759 BEGIN
1760 OKC_API.init_msg_list(p_init_msg_list);
1761 -- Make sure PL/SQL table has records in it before passing
1762 IF (p_dctv_tbl.COUNT > 0) THEN
1763 i := p_dctv_tbl.FIRST;
1764 LOOP
1765 lock_row (
1766 p_api_version => p_api_version,
1767 p_init_msg_list => OKC_API.G_FALSE,
1768 x_return_status => x_return_status,
1769 x_msg_count => x_msg_count,
1770 x_msg_data => x_msg_data,
1771 p_dctv_rec => p_dctv_tbl(i));
1772 -- Begin Post-Generation Change
1773 -- store the highest degree of error
1774 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1775 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1776 l_overall_status := x_return_status;
1777 END IF;
1778 END IF;
1779 -- End Post-Generation Change
1780 EXIT WHEN (i = p_dctv_tbl.LAST);
1781 i := p_dctv_tbl.NEXT(i);
1782 END LOOP;
1783 -- Begin Post-Generation Change
1784 -- return overall status
1785 x_return_status := l_overall_status;
1786 -- End Post-Generation Change
1787 END IF;
1788 EXCEPTION
1789 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1790 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1791 (
1792 l_api_name,
1793 G_PKG_NAME,
1794 'OKC_API.G_RET_STS_ERROR',
1795 x_msg_count,
1796 x_msg_data,
1797 '_PVT'
1798 );
1799 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1800 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1801 (
1802 l_api_name,
1803 G_PKG_NAME,
1804 'OKC_API.G_RET_STS_UNEXP_ERROR',
1805 x_msg_count,
1806 x_msg_data,
1807 '_PVT'
1808 );
1809 WHEN OTHERS THEN
1810 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1811 (
1812 l_api_name,
1813 G_PKG_NAME,
1814 'OTHERS',
1815 x_msg_count,
1816 x_msg_data,
1817 '_PVT'
1818 );
1819 END lock_row;
1820
1821 ---------------------------------------------------------------------------
1822 -- PROCEDURE update_row
1823 ---------------------------------------------------------------------------
1824 ----------------------------------------
1825 -- update_row for:OKL_DF_CTGY_RMK_TMS --
1826 ----------------------------------------
1827 PROCEDURE update_row(
1828 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1829 x_return_status OUT NOCOPY VARCHAR2,
1830 x_msg_count OUT NOCOPY NUMBER,
1831 x_msg_data OUT NOCOPY VARCHAR2,
1832 p_dct_rec IN dct_rec_type,
1833 x_dct_rec OUT NOCOPY dct_rec_type) IS
1834
1835 l_api_version CONSTANT NUMBER := 1;
1836 l_api_name CONSTANT VARCHAR2(30) := 'TMS_update_row';
1837 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1838 l_dct_rec dct_rec_type := p_dct_rec;
1839 l_def_dct_rec dct_rec_type;
1840 l_row_notfound BOOLEAN := TRUE;
1841 ----------------------------------
1842 -- FUNCTION populate_new_record --
1843 ----------------------------------
1844 FUNCTION populate_new_record (
1845 p_dct_rec IN dct_rec_type,
1846 x_dct_rec OUT NOCOPY dct_rec_type
1847 ) RETURN VARCHAR2 IS
1848 l_dct_rec dct_rec_type;
1849 l_row_notfound BOOLEAN := TRUE;
1850 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1851 BEGIN
1852 x_dct_rec := p_dct_rec;
1853 -- Get current database values
1854 l_dct_rec := get_rec(p_dct_rec, l_row_notfound);
1855 IF (l_row_notfound) THEN
1856 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1857 END IF;
1858 IF (x_dct_rec.id = OKC_API.G_MISS_NUM)
1859 THEN
1860 x_dct_rec.id := l_dct_rec.id;
1861 END IF;
1862 IF (x_dct_rec.org_id = OKC_API.G_MISS_NUM)
1863 THEN
1864 x_dct_rec.org_id := l_dct_rec.org_id;
1865 END IF;
1866 IF (x_dct_rec.rmr_id = OKC_API.G_MISS_NUM)
1867 THEN
1868 x_dct_rec.rmr_id := l_dct_rec.rmr_id;
1869 END IF;
1870 IF (x_dct_rec.ico_id = OKC_API.G_MISS_NUM)
1871 THEN
1872 x_dct_rec.ico_id := l_dct_rec.ico_id;
1873 END IF;
1874 IF (x_dct_rec.iln_id = OKC_API.G_MISS_NUM)
1875 THEN
1876 x_dct_rec.iln_id := l_dct_rec.iln_id;
1877 END IF;
1878 IF (x_dct_rec.date_effective_from = OKC_API.G_MISS_DATE)
1879 THEN
1880 x_dct_rec.date_effective_from := l_dct_rec.date_effective_from;
1881 END IF;
1882 IF (x_dct_rec.object_version_number = OKC_API.G_MISS_NUM)
1883 THEN
1884 x_dct_rec.object_version_number := l_dct_rec.object_version_number;
1885 END IF;
1886 IF (x_dct_rec.created_by = OKC_API.G_MISS_NUM)
1887 THEN
1888 x_dct_rec.created_by := l_dct_rec.created_by;
1889 END IF;
1890 IF (x_dct_rec.date_effective_to = OKC_API.G_MISS_DATE)
1891 THEN
1892 x_dct_rec.date_effective_to := l_dct_rec.date_effective_to;
1893 END IF;
1894 IF (x_dct_rec.creation_date = OKC_API.G_MISS_DATE)
1895 THEN
1896 x_dct_rec.creation_date := l_dct_rec.creation_date;
1897 END IF;
1898 IF (x_dct_rec.last_updated_by = OKC_API.G_MISS_NUM)
1899 THEN
1900 x_dct_rec.last_updated_by := l_dct_rec.last_updated_by;
1901 END IF;
1902 IF (x_dct_rec.last_update_date = OKC_API.G_MISS_DATE)
1903 THEN
1904 x_dct_rec.last_update_date := l_dct_rec.last_update_date;
1905 END IF;
1906 IF (x_dct_rec.last_update_login = OKC_API.G_MISS_NUM)
1907 THEN
1908 x_dct_rec.last_update_login := l_dct_rec.last_update_login;
1909 END IF;
1910 RETURN(l_return_status);
1911 END populate_new_record;
1912 --------------------------------------------
1913 -- Set_Attributes for:OKL_DF_CTGY_RMK_TMS --
1914 --------------------------------------------
1915 FUNCTION Set_Attributes (
1916 p_dct_rec IN dct_rec_type,
1917 x_dct_rec OUT NOCOPY dct_rec_type
1918 ) RETURN VARCHAR2 IS
1919 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1920 BEGIN
1921 x_dct_rec := p_dct_rec;
1922 RETURN(l_return_status);
1923 END Set_Attributes;
1924 BEGIN
1925 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1926 p_init_msg_list,
1927 '_PVT',
1928 x_return_status);
1929 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1930 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1931 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1932 RAISE OKC_API.G_EXCEPTION_ERROR;
1933 END IF;
1934 --- Setting item attributes
1935 l_return_status := Set_Attributes(
1936 p_dct_rec, -- IN
1937 l_dct_rec); -- OUT
1938 --- If any errors happen abort API
1939 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1940 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1941 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1942 RAISE OKC_API.G_EXCEPTION_ERROR;
1943 END IF;
1944 l_return_status := populate_new_record(l_dct_rec, l_def_dct_rec);
1945 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1946 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1947 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1948 RAISE OKC_API.G_EXCEPTION_ERROR;
1949 END IF;
1950 UPDATE OKL_DF_CTGY_RMK_TMS
1951 SET ORG_ID = l_def_dct_rec.org_id,
1952 RMR_ID = l_def_dct_rec.rmr_id,
1953 ICO_ID = l_def_dct_rec.ico_id,
1954 ILN_ID = l_def_dct_rec.iln_id,
1955 DATE_EFFECTIVE_FROM = l_def_dct_rec.date_effective_from,
1956 OBJECT_VERSION_NUMBER = l_def_dct_rec.object_version_number,
1957 CREATED_BY = l_def_dct_rec.created_by,
1958 DATE_EFFECTIVE_TO = l_def_dct_rec.date_effective_to,
1959 CREATION_DATE = l_def_dct_rec.creation_date,
1960 LAST_UPDATED_BY = l_def_dct_rec.last_updated_by,
1961 LAST_UPDATE_DATE = l_def_dct_rec.last_update_date,
1962 LAST_UPDATE_LOGIN = l_def_dct_rec.last_update_login
1963 WHERE ID = l_def_dct_rec.id;
1964
1965 x_dct_rec := l_def_dct_rec;
1966 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1967 EXCEPTION
1968 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1969 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1970 (
1971 l_api_name,
1972 G_PKG_NAME,
1973 'OKC_API.G_RET_STS_ERROR',
1974 x_msg_count,
1975 x_msg_data,
1976 '_PVT'
1977 );
1978 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1979 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1980 (
1981 l_api_name,
1982 G_PKG_NAME,
1983 'OKC_API.G_RET_STS_UNEXP_ERROR',
1984 x_msg_count,
1985 x_msg_data,
1986 '_PVT'
1987 );
1988 WHEN OTHERS THEN
1989 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1990 (
1991 l_api_name,
1992 G_PKG_NAME,
1993 'OTHERS',
1994 x_msg_count,
1995 x_msg_data,
1996 '_PVT'
1997 );
1998 END update_row;
1999 ------------------------------------------
2000 -- update_row for:OKL_DF_CTGY_RMK_TMS_V --
2001 ------------------------------------------
2002 PROCEDURE update_row(
2003 p_api_version IN NUMBER,
2004 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2005 x_return_status OUT NOCOPY VARCHAR2,
2006 x_msg_count OUT NOCOPY NUMBER,
2007 x_msg_data OUT NOCOPY VARCHAR2,
2008 p_dctv_rec IN dctv_rec_type,
2009 x_dctv_rec OUT NOCOPY dctv_rec_type) IS
2010
2011 l_api_version CONSTANT NUMBER := 1;
2012 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2013 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2014 l_dctv_rec dctv_rec_type := p_dctv_rec;
2015 l_def_dctv_rec dctv_rec_type;
2016 l_dct_rec dct_rec_type;
2017 lx_dct_rec dct_rec_type;
2018 -------------------------------
2019 -- FUNCTION fill_who_columns --
2020 -------------------------------
2021 FUNCTION fill_who_columns (
2022 p_dctv_rec IN dctv_rec_type
2023 ) RETURN dctv_rec_type IS
2024 l_dctv_rec dctv_rec_type := p_dctv_rec;
2025 BEGIN
2026 l_dctv_rec.LAST_UPDATE_DATE := SYSDATE;
2027 l_dctv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2028 l_dctv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2029 RETURN(l_dctv_rec);
2030 END fill_who_columns;
2031 ----------------------------------
2032 -- FUNCTION populate_new_record --
2033 ----------------------------------
2034 FUNCTION populate_new_record (
2035 p_dctv_rec IN dctv_rec_type,
2036 x_dctv_rec OUT NOCOPY dctv_rec_type
2037 ) RETURN VARCHAR2 IS
2038 l_dctv_rec dctv_rec_type;
2039 l_row_notfound BOOLEAN := TRUE;
2040 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2041 BEGIN
2042 x_dctv_rec := p_dctv_rec;
2043 -- Get current database values
2044 l_dctv_rec := get_rec(p_dctv_rec, l_row_notfound);
2045 IF (l_row_notfound) THEN
2046 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2047 END IF;
2048 IF (x_dctv_rec.id = OKC_API.G_MISS_NUM)
2049 THEN
2050 x_dctv_rec.id := l_dctv_rec.id;
2051 END IF;
2052 IF (x_dctv_rec.object_version_number = OKC_API.G_MISS_NUM)
2053 THEN
2054 x_dctv_rec.object_version_number := l_dctv_rec.object_version_number;
2055 END IF;
2056 IF (x_dctv_rec.org_id = OKC_API.G_MISS_NUM)
2057 THEN
2058 x_dctv_rec.org_id := l_dctv_rec.org_id;
2059 END IF;
2060 IF (x_dctv_rec.rmr_id = OKC_API.G_MISS_NUM)
2061 THEN
2062 x_dctv_rec.rmr_id := l_dctv_rec.rmr_id;
2063 END IF;
2064 IF (x_dctv_rec.ico_id = OKC_API.G_MISS_NUM)
2065 THEN
2066 x_dctv_rec.ico_id := l_dctv_rec.ico_id;
2067 END IF;
2068 IF (x_dctv_rec.iln_id = OKC_API.G_MISS_NUM)
2069 THEN
2070 x_dctv_rec.iln_id := l_dctv_rec.iln_id;
2071 END IF;
2072 IF (x_dctv_rec.date_effective_from = OKC_API.G_MISS_DATE)
2073 THEN
2074 x_dctv_rec.date_effective_from := l_dctv_rec.date_effective_from;
2075 END IF;
2076 IF (x_dctv_rec.date_effective_to = OKC_API.G_MISS_DATE)
2077 THEN
2078 x_dctv_rec.date_effective_to := l_dctv_rec.date_effective_to;
2079 END IF;
2080 IF (x_dctv_rec.created_by = OKC_API.G_MISS_NUM)
2081 THEN
2082 x_dctv_rec.created_by := l_dctv_rec.created_by;
2083 END IF;
2084 IF (x_dctv_rec.creation_date = OKC_API.G_MISS_DATE)
2085 THEN
2086 x_dctv_rec.creation_date := l_dctv_rec.creation_date;
2087 END IF;
2088 IF (x_dctv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2089 THEN
2090 x_dctv_rec.last_updated_by := l_dctv_rec.last_updated_by;
2091 END IF;
2092 IF (x_dctv_rec.last_update_date = OKC_API.G_MISS_DATE)
2093 THEN
2094 x_dctv_rec.last_update_date := l_dctv_rec.last_update_date;
2095 END IF;
2096 IF (x_dctv_rec.last_update_login = OKC_API.G_MISS_NUM)
2097 THEN
2098 x_dctv_rec.last_update_login := l_dctv_rec.last_update_login;
2099 END IF;
2100 RETURN(l_return_status);
2101 END populate_new_record;
2102 ----------------------------------------------
2103 -- Set_Attributes for:OKL_DF_CTGY_RMK_TMS_V --
2104 ----------------------------------------------
2105 FUNCTION Set_Attributes (
2106 p_dctv_rec IN dctv_rec_type,
2107 x_dctv_rec OUT NOCOPY dctv_rec_type
2108 ) RETURN VARCHAR2 IS
2109 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2110 BEGIN
2111 x_dctv_rec := p_dctv_rec;
2112 x_dctv_rec.OBJECT_VERSION_NUMBER := NVL(x_dctv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2113 RETURN(l_return_status);
2114 END Set_Attributes;
2115 BEGIN
2116 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2117 G_PKG_NAME,
2118 p_init_msg_list,
2119 l_api_version,
2120 p_api_version,
2121 '_PVT',
2122 x_return_status);
2123 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2124 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2125 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2126 RAISE OKC_API.G_EXCEPTION_ERROR;
2127 END IF;
2128 --- Setting item attributes
2129 l_return_status := Set_Attributes(
2130 p_dctv_rec, -- IN
2131 l_dctv_rec); -- OUT
2132 --- If any errors happen abort API
2133 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2134 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2135 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2136 RAISE OKC_API.G_EXCEPTION_ERROR;
2137 END IF;
2138 l_return_status := populate_new_record(l_dctv_rec, l_def_dctv_rec);
2139 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2140 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2141 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2142 RAISE OKC_API.G_EXCEPTION_ERROR;
2143 END IF;
2144 l_def_dctv_rec := fill_who_columns(l_def_dctv_rec);
2145 --- Validate all non-missing attributes (Item Level Validation)
2146 l_return_status := Validate_Attributes(l_def_dctv_rec);
2147 --- If any errors happen abort API
2148 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2149 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2150 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2151 RAISE OKC_API.G_EXCEPTION_ERROR;
2152 END IF;
2153 l_return_status := Validate_Record(l_def_dctv_rec);
2154 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2155 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2156 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2157 RAISE OKC_API.G_EXCEPTION_ERROR;
2158 END IF;
2159
2160 --------------------------------------
2161 -- Move VIEW record to "Child" records
2162 --------------------------------------
2163 migrate(l_def_dctv_rec, l_dct_rec);
2164 --------------------------------------------
2165 -- Call the UPDATE_ROW for each child record
2166 --------------------------------------------
2167 update_row(
2168 p_init_msg_list,
2169 x_return_status,
2170 x_msg_count,
2171 x_msg_data,
2172 l_dct_rec,
2173 lx_dct_rec
2174 );
2175 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2176 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2177 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2178 RAISE OKC_API.G_EXCEPTION_ERROR;
2179 END IF;
2180 migrate(lx_dct_rec, l_def_dctv_rec);
2181 x_dctv_rec := l_def_dctv_rec;
2182 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2183 EXCEPTION
2184 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2185 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2186 (
2187 l_api_name,
2188 G_PKG_NAME,
2189 'OKC_API.G_RET_STS_ERROR',
2190 x_msg_count,
2191 x_msg_data,
2192 '_PVT'
2193 );
2194 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2195 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2196 (
2197 l_api_name,
2198 G_PKG_NAME,
2199 'OKC_API.G_RET_STS_UNEXP_ERROR',
2200 x_msg_count,
2201 x_msg_data,
2202 '_PVT'
2203 );
2204 WHEN OTHERS THEN
2205 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2206 (
2207 l_api_name,
2208 G_PKG_NAME,
2209 'OTHERS',
2210 x_msg_count,
2211 x_msg_data,
2212 '_PVT'
2213 );
2214 END update_row;
2215 ----------------------------------------
2216 -- PL/SQL TBL update_row for:DCTV_TBL --
2217 ----------------------------------------
2218 PROCEDURE update_row(
2219 p_api_version IN NUMBER,
2220 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2221 x_return_status OUT NOCOPY VARCHAR2,
2222 x_msg_count OUT NOCOPY NUMBER,
2223 x_msg_data OUT NOCOPY VARCHAR2,
2224 p_dctv_tbl IN dctv_tbl_type,
2225 x_dctv_tbl OUT NOCOPY dctv_tbl_type) IS
2226
2227 l_api_version CONSTANT NUMBER := 1;
2228 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2229 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2230 i NUMBER := 0;
2231 -- Begin Post-Generation Change
2232 -- overall error status
2233 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2234 -- End Post-Generation Change
2235 BEGIN
2236 OKC_API.init_msg_list(p_init_msg_list);
2237 -- Make sure PL/SQL table has records in it before passing
2238 IF (p_dctv_tbl.COUNT > 0) THEN
2239 i := p_dctv_tbl.FIRST;
2240 LOOP
2241 update_row (
2242 p_api_version => p_api_version,
2243 p_init_msg_list => OKC_API.G_FALSE,
2244 x_return_status => x_return_status,
2245 x_msg_count => x_msg_count,
2246 x_msg_data => x_msg_data,
2247 p_dctv_rec => p_dctv_tbl(i),
2248 x_dctv_rec => x_dctv_tbl(i));
2249 -- Begin Post-Generation Change
2250 -- store the highest degree of error
2251 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2252 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2253 l_overall_status := x_return_status;
2254 END IF;
2255 END IF;
2256 -- End Post-Generation Change
2257 EXIT WHEN (i = p_dctv_tbl.LAST);
2258 i := p_dctv_tbl.NEXT(i);
2259 END LOOP;
2260 -- Begin Post-Generation Change
2261 -- return overall status
2262 x_return_status := l_overall_status;
2263 -- End Post-Generation Change
2264 END IF;
2265 EXCEPTION
2266 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2267 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2268 (
2269 l_api_name,
2270 G_PKG_NAME,
2271 'OKC_API.G_RET_STS_ERROR',
2272 x_msg_count,
2273 x_msg_data,
2274 '_PVT'
2275 );
2276 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2277 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2278 (
2279 l_api_name,
2280 G_PKG_NAME,
2281 'OKC_API.G_RET_STS_UNEXP_ERROR',
2282 x_msg_count,
2283 x_msg_data,
2284 '_PVT'
2285 );
2286 WHEN OTHERS THEN
2287 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2288 (
2289 l_api_name,
2290 G_PKG_NAME,
2291 'OTHERS',
2292 x_msg_count,
2293 x_msg_data,
2294 '_PVT'
2295 );
2296 END update_row;
2297
2298 ---------------------------------------------------------------------------
2299 -- PROCEDURE delete_row
2300 ---------------------------------------------------------------------------
2301 ----------------------------------------
2302 -- delete_row for:OKL_DF_CTGY_RMK_TMS --
2303 ----------------------------------------
2304 PROCEDURE delete_row(
2305 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2306 x_return_status OUT NOCOPY VARCHAR2,
2307 x_msg_count OUT NOCOPY NUMBER,
2308 x_msg_data OUT NOCOPY VARCHAR2,
2309 p_dct_rec IN dct_rec_type) IS
2310
2311 l_api_version CONSTANT NUMBER := 1;
2312 l_api_name CONSTANT VARCHAR2(30) := 'TMS_delete_row';
2313 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2314 l_dct_rec dct_rec_type:= p_dct_rec;
2315 l_row_notfound BOOLEAN := TRUE;
2316 BEGIN
2317 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2318 p_init_msg_list,
2319 '_PVT',
2320 x_return_status);
2321 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2322 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2323 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2324 RAISE OKC_API.G_EXCEPTION_ERROR;
2325 END IF;
2326 DELETE FROM OKL_DF_CTGY_RMK_TMS
2327 WHERE ID = l_dct_rec.id;
2328
2329 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2330 EXCEPTION
2331 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2332 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2333 (
2334 l_api_name,
2335 G_PKG_NAME,
2336 'OKC_API.G_RET_STS_ERROR',
2337 x_msg_count,
2338 x_msg_data,
2339 '_PVT'
2340 );
2341 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2342 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2343 (
2344 l_api_name,
2345 G_PKG_NAME,
2346 'OKC_API.G_RET_STS_UNEXP_ERROR',
2347 x_msg_count,
2348 x_msg_data,
2349 '_PVT'
2350 );
2351 WHEN OTHERS THEN
2352 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2353 (
2354 l_api_name,
2355 G_PKG_NAME,
2356 'OTHERS',
2357 x_msg_count,
2358 x_msg_data,
2359 '_PVT'
2360 );
2361 END delete_row;
2362 ------------------------------------------
2363 -- delete_row for:OKL_DF_CTGY_RMK_TMS_V --
2364 ------------------------------------------
2365 PROCEDURE delete_row(
2366 p_api_version IN NUMBER,
2367 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2368 x_return_status OUT NOCOPY VARCHAR2,
2369 x_msg_count OUT NOCOPY NUMBER,
2370 x_msg_data OUT NOCOPY VARCHAR2,
2371 p_dctv_rec IN dctv_rec_type) IS
2372
2373 l_api_version CONSTANT NUMBER := 1;
2374 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2375 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2376 l_dctv_rec dctv_rec_type := p_dctv_rec;
2377 l_dct_rec dct_rec_type;
2378 BEGIN
2379 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2380 G_PKG_NAME,
2381 p_init_msg_list,
2382 l_api_version,
2383 p_api_version,
2384 '_PVT',
2385 x_return_status);
2386 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2387 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2388 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2389 RAISE OKC_API.G_EXCEPTION_ERROR;
2390 END IF;
2391 --------------------------------------
2392 -- Move VIEW record to "Child" records
2393 --------------------------------------
2394 migrate(l_dctv_rec, l_dct_rec);
2395 --------------------------------------------
2396 -- Call the DELETE_ROW for each child record
2397 --------------------------------------------
2398 delete_row(
2399 p_init_msg_list,
2400 x_return_status,
2401 x_msg_count,
2402 x_msg_data,
2403 l_dct_rec
2404 );
2405 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2406 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2407 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2408 RAISE OKC_API.G_EXCEPTION_ERROR;
2409 END IF;
2410 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2411 EXCEPTION
2412 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2413 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2414 (
2415 l_api_name,
2416 G_PKG_NAME,
2417 'OKC_API.G_RET_STS_ERROR',
2418 x_msg_count,
2419 x_msg_data,
2420 '_PVT'
2421 );
2422 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2423 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2424 (
2425 l_api_name,
2426 G_PKG_NAME,
2427 'OKC_API.G_RET_STS_UNEXP_ERROR',
2428 x_msg_count,
2429 x_msg_data,
2430 '_PVT'
2431 );
2432 WHEN OTHERS THEN
2433 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2434 (
2435 l_api_name,
2436 G_PKG_NAME,
2437 'OTHERS',
2438 x_msg_count,
2439 x_msg_data,
2440 '_PVT'
2441 );
2442 END delete_row;
2443 ----------------------------------------
2444 -- PL/SQL TBL delete_row for:DCTV_TBL --
2445 ----------------------------------------
2446 PROCEDURE delete_row(
2447 p_api_version IN NUMBER,
2448 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2449 x_return_status OUT NOCOPY VARCHAR2,
2450 x_msg_count OUT NOCOPY NUMBER,
2451 x_msg_data OUT NOCOPY VARCHAR2,
2452 p_dctv_tbl IN dctv_tbl_type) IS
2453
2454 l_api_version CONSTANT NUMBER := 1;
2455 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2456 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2457 i NUMBER := 0;
2458 -- Begin Post-Generation Change
2459 -- overall error status
2460 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2461 -- End Post-Generation Change
2462 BEGIN
2463 OKC_API.init_msg_list(p_init_msg_list);
2464 -- Make sure PL/SQL table has records in it before passing
2465 IF (p_dctv_tbl.COUNT > 0) THEN
2466 i := p_dctv_tbl.FIRST;
2467 LOOP
2468 delete_row (
2469 p_api_version => p_api_version,
2470 p_init_msg_list => OKC_API.G_FALSE,
2471 x_return_status => x_return_status,
2472 x_msg_count => x_msg_count,
2473 x_msg_data => x_msg_data,
2474 p_dctv_rec => p_dctv_tbl(i));
2475 -- Begin Post-Generation Change
2476 -- store the highest degree of error
2477 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2478 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2479 l_overall_status := x_return_status;
2480 END IF;
2481 END IF;
2482 -- End Post-Generation Change
2483 EXIT WHEN (i = p_dctv_tbl.LAST);
2484 i := p_dctv_tbl.NEXT(i);
2485 END LOOP;
2486 -- Begin Post-Generation Change
2487 -- return overall status
2488 x_return_status := l_overall_status;
2489 -- End Post-Generation Change
2490 END IF;
2491 EXCEPTION
2492 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2493 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2494 (
2495 l_api_name,
2496 G_PKG_NAME,
2497 'OKC_API.G_RET_STS_ERROR',
2498 x_msg_count,
2499 x_msg_data,
2500 '_PVT'
2501 );
2502 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2503 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2504 (
2505 l_api_name,
2506 G_PKG_NAME,
2507 'OKC_API.G_RET_STS_UNEXP_ERROR',
2508 x_msg_count,
2509 x_msg_data,
2510 '_PVT'
2511 );
2512 WHEN OTHERS THEN
2513 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2514 (
2515 l_api_name,
2516 G_PKG_NAME,
2517 'OTHERS',
2518 x_msg_count,
2519 x_msg_data,
2520 '_PVT'
2521 );
2522 END delete_row;
2523 END OKL_DCT_PVT;