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