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