[Home] [Help]
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
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
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,
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:
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;
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;
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,
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: ------------------------------------
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,
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:
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;
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;
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,
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--
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
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,
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: */
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
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,
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
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,
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;
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))
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,
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: */
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
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,
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
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,
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;
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,
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');
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:
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;
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,
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,
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;
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,
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: */
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;
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;
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,
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
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);
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,
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: */
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',
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:
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
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
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:
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
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
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
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
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
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
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
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'
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
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,
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'
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'
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;
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,
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
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
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'
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
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
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
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'
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
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,
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: ---------------------------------------------------------------------------
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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);
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:
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;
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:
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;
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:
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;
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:
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;
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: /*
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;
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: */
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;
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:
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;
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:
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;
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;
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: */
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
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);
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;
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:
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;
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:
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
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:
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
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,
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,
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;
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)
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);
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
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;
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);
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
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;
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;
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);
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
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
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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
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
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
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));
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;
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
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,
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',
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: );
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,
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',
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: );
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',
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 --
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;
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
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;
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
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(
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
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;
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(
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,
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,
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: (
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,
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',
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: );
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,
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',
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: );
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',
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;
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
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;
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,
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;
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);
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
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;
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;
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);
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)
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);
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;
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);
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
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;
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;
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: --------------------------------------
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
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: --------------------------------------
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;
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);
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
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;
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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
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
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),
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;
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
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,
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',
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: );
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,
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',
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: );
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',
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
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;
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
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;
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
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);
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;
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
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;
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
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;
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
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;
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;
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
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
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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,
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,
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;
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: --------------------------------------
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
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: --------------------------------------
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;
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);
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
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
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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;
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
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
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));
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;
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
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,
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',
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: );
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,
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',
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: );
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',
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: ----------------------------------
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);
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;
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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);
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;
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
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;
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
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(
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
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;
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);
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
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;
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;
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
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,
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,
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: (
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,
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',
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: );
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,
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',
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: );
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',
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;
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);
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;
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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);
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);
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,
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;
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
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(
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
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;
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);
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
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;
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;
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);
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)
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);
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;
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);
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
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;
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;
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:
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: --------------------------------------
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
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;
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);
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;
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);
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: (
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,
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',
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: );
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,
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',
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: );
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',
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;
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
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
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),
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;
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
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,
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',
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: );
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,
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',
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: );
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',
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
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,
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
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;
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
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;
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:
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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,
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,
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;
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: --------------------------------------
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
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: --------------------------------------
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;
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);
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
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
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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;
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
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
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));
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;
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
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,
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',
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: );
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,
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',
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: );
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',