[Home] [Help]
PACKAGE BODY: APPS.OKL_ALS_PVT
Source
1 PACKAGE BODY OKL_ALS_PVT AS
2 /* $Header: OKLSALSB.pls 120.4 2007/02/27 07:03:13 dpsingh noship $ */
3
4 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
5 G_ITEM_NOT_FOUND_ERROR EXCEPTION;
6
7
8 ---------------------------------------------------------------------------
9 -- PROCEDURE Validate_Unique_ALS_Record
10 ---------------------------------------------------------------------------
11 PROCEDURE Validate_Unique_ALS_Record(x_return_status OUT NOCOPY VARCHAR2
12 ,p_ALSv_rec IN ALSv_rec_type)
13 IS
14
15 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
16 l_unq_tbl OKC_UTIL.unq_tbl_type;
17 l_ALSv_status VARCHAR2(1);
18 l_start_date DATE;
19 l_end_date DATE;
20 l_dummy VARCHAR2(1);
21 l_row_found BOOLEAN := TRUE;
22
23 CURSOR l_unique_csr
24 IS
25 SELECT '1'
26 FROM OKL_AG_SOURCE_MAPS
27 WHERE ae_line_type = p_alsv_rec.ae_line_type
28 AND source = p_alsv_rec.source
29 AND ID <> p_alsv_rec.id;
30
31 BEGIN
32 OPEN l_unique_csr ;
33 FETCH l_unique_csr INTO l_dummy ;
34 l_row_found := l_unique_csr%FOUND;
35 CLOSE l_unique_csr;
36
37 IF (l_row_found) THEN
38 okl_api.set_message('OKL',G_OKL_ENTITY_NOT_UNIQUE, 'ENTITY_NAME', 'Account Generator Source');
39 l_return_status := okl_api.G_RET_STS_ERROR;
40 RAISE G_EXCEPTION_HALT_VALIDATION;
41 END IF;
42 x_return_status := l_return_status;
43
44 EXCEPTION
45 WHEN G_EXCEPTION_HALT_VALIDATION THEN
46 x_return_status := l_return_status;
47 WHEN OTHERS THEN
48 okl_api.set_message(G_APP_NAME,G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
49 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
50
51 END Validate_Unique_ALS_Record;
52
53
54 ---------------------------------------------------------------------------
55 -- FUNCTION get_seq_id
56 ---------------------------------------------------------------------------
57 ---------------------------------------------------------------------------
58 -- FUNCTION get_seq_id
59 ---------------------------------------------------------------------------
60 FUNCTION get_seq_id RETURN NUMBER IS
61 BEGIN
62 RETURN(okc_p_util.raw_to_number(sys_guid()));
63 END get_seq_id;
64
65 ---------------------------------------------------------------------------
66 -- PROCEDURE qc
67 ---------------------------------------------------------------------------
68 PROCEDURE qc IS
69 BEGIN
70 NULL;
71 END qc;
72
73 ---------------------------------------------------------------------------
74 -- PROCEDURE change_version
75 ---------------------------------------------------------------------------
76 PROCEDURE change_version IS
77 BEGIN
78 NULL;
79 END change_version;
80
81 ---------------------------------------------------------------------------
82 -- PROCEDURE api_copy
83 ---------------------------------------------------------------------------
84 PROCEDURE api_copy IS
85 BEGIN
86 NULL;
87 END api_copy;
88
89 ---------------------------------------------------------------------------
90 -- FUNCTION get_rec for: OKL_AG_SOURCE_MAPS
91 ---------------------------------------------------------------------------
92 FUNCTION get_rec (
93 p_ALS_rec IN ALS_rec_type,
94 x_no_data_found OUT NOCOPY BOOLEAN
95 ) RETURN ALS_rec_type IS
96 CURSOR OKL_AG_SOURCE_MAPS_pk_csr (p_id IN NUMBER) IS
97 SELECT
98 ID,
99 OBJECT_VERSION_NUMBER,
100 ORG_ID,
101 AE_LINE_TYPE,
102 SOURCE,
103 PRIMARY_KEY_COLUMN,
104 SELECT_COLUMN,
105 CREATED_BY,
106 CREATION_DATE,
107 LAST_UPDATED_BY,
108 LAST_UPDATE_DATE,
109 LAST_UPDATE_LOGIN
110 FROM OKL_AG_SOURCE_MAPS
111 WHERE OKL_AG_SOURCE_MAPS.id = p_id;
112 l_OKL_AG_SOURCE_MAPS_pk OKL_AG_SOURCE_MAPS_pk_csr%ROWTYPE;
113 l_ALS_rec ALS_rec_type;
114 BEGIN
115 x_no_data_found := TRUE;
116 -- Get current database values
117 OPEN OKL_AG_SOURCE_MAPS_pk_csr (p_ALS_rec.id);
118 FETCH OKL_AG_SOURCE_MAPS_pk_csr INTO
119 l_ALS_rec.ID,
120 l_ALS_rec.OBJECT_VERSION_NUMBER,
121 l_ALS_rec.ORG_ID,
122 l_ALS_rec.AE_LINE_TYPE,
123 l_ALS_rec.SOURCE,
124 l_ALS_rec.PRIMARY_KEY_COLUMN,
125 l_ALS_rec.SELECT_COLUMN,
126 l_ALS_rec.CREATED_BY,
127 l_ALS_rec.CREATION_DATE,
128 l_ALS_rec.LAST_UPDATED_BY,
129 l_ALS_rec.LAST_UPDATE_DATE,
130 l_ALS_rec.LAST_UPDATE_LOGIN;
131 x_no_data_found := OKL_AG_SOURCE_MAPS_pk_csr%NOTFOUND;
132 CLOSE OKL_AG_SOURCE_MAPS_pk_csr;
133 RETURN(l_ALS_rec);
134 END get_rec;
135
136 FUNCTION get_rec (
137 p_ALS_rec IN ALS_rec_type
138 ) RETURN ALS_rec_type IS
139 l_row_notfound BOOLEAN := TRUE;
140 BEGIN
141 RETURN(get_rec(p_ALS_rec, l_row_notfound));
142 END get_rec;
143 ---------------------------------------------------------------------------
144 -- FUNCTION get_rec for: OKL_AG_SOURCE_MAPS_V
145 ---------------------------------------------------------------------------
146 FUNCTION get_rec (
147 p_ALSv_rec IN ALSv_rec_type,
148 x_no_data_found OUT NOCOPY BOOLEAN
149 ) RETURN ALSv_rec_type IS
150 CURSOR okl_ALSv_pk_csr (p_id IN NUMBER) IS
151 SELECT
152 ID,
153 OBJECT_VERSION_NUMBER,
154 AE_LINE_TYPE,
155 SOURCE,
156 PRIMARY_KEY_COLUMN,
157 SELECT_COLUMN,
158 ORG_ID,
159 CREATED_BY,
160 CREATION_DATE,
161 LAST_UPDATED_BY,
162 LAST_UPDATE_DATE,
163 LAST_UPDATE_LOGIN
164 FROM OKL_AG_SOURCE_MAPS
165 WHERE OKL_AG_SOURCE_MAPS.id = p_id;
166 l_okl_ALSv_pk okl_ALSv_pk_csr%ROWTYPE;
167 l_ALSv_rec ALSv_rec_type;
168 BEGIN
169 x_no_data_found := TRUE;
170 -- Get current database values
171 OPEN okl_ALSv_pk_csr (p_ALSv_rec.id);
172 FETCH okl_ALSv_pk_csr INTO
173 l_ALSv_rec.ID,
174 l_ALSv_rec.OBJECT_VERSION_NUMBER,
175 l_ALSv_rec.AE_LINE_TYPE,
176 l_ALSv_rec.SOURCE,
177 l_ALSv_rec.PRIMARY_KEY_COLUMN,
178 l_ALSv_rec.SELECT_COLUMN,
179 l_ALSv_rec.ORG_ID,
180 l_ALSv_rec.CREATED_BY,
181 l_ALSv_rec.CREATION_DATE,
182 l_ALSv_rec.LAST_UPDATED_BY,
183 l_ALSv_rec.LAST_UPDATE_DATE,
184 l_ALSv_rec.LAST_UPDATE_LOGIN;
185 x_no_data_found := okl_ALSv_pk_csr%NOTFOUND;
186 CLOSE okl_ALSv_pk_csr;
187 RETURN(l_ALSv_rec);
188 END get_rec;
189
190 FUNCTION get_rec (
191 p_ALSv_rec IN ALSv_rec_type
192 ) RETURN ALSv_rec_type IS
193 l_row_notfound BOOLEAN := TRUE;
194 BEGIN
195 RETURN(get_rec(p_ALSv_rec, l_row_notfound));
196 END get_rec;
197
198 --------------------------------------------------------
199 -- FUNCTION null_out_defaults for: OKL_AG_SOURCE_MAPS_V --
200 --------------------------------------------------------
201 FUNCTION null_out_defaults (
202 p_ALSv_rec IN ALSv_rec_type
203 ) RETURN ALSv_rec_type IS
204 l_ALSv_rec ALSv_rec_type := p_ALSv_rec;
205 BEGIN
206 IF (l_ALSv_rec.object_version_number = okl_api.G_MISS_NUM) THEN
207 l_ALSv_rec.object_version_number := NULL;
208 END IF;
209 IF (l_ALSv_rec.AE_LINE_TYPE = okl_api.G_MISS_CHAR) THEN
210 l_ALSv_rec.AE_LINE_TYPE := NULL;
211 END IF;
212 IF (l_ALSv_rec.SOURCE = okl_api.G_MISS_CHAR) THEN
213 l_ALSv_rec.SOURCE := NULL;
214 END IF;
215 IF (l_ALSv_rec.PRIMARY_KEY_COLUMN = okl_api.G_MISS_CHAR) THEN
216 l_ALSv_rec.PRIMARY_KEY_COLUMN := NULL;
217 END IF;
218 IF (l_ALSv_rec.SELECT_COLUMN = okl_api.G_MISS_CHAR) THEN
219 l_ALSv_rec.SELECT_COLUMN := NULL;
220 END IF;
221 IF (l_ALSv_rec.org_id = okl_api.G_MISS_NUM) THEN
222 l_ALSv_rec.org_id := NULL;
223 END IF;
224 IF (l_ALSv_rec.created_by = okl_api.G_MISS_NUM) THEN
225 l_ALSv_rec.created_by := NULL;
226 END IF;
227 IF (l_ALSv_rec.creation_date = okl_api.G_MISS_DATE) THEN
228 l_ALSv_rec.creation_date := NULL;
229 END IF;
230 IF (l_ALSv_rec.last_updated_by = okl_api.G_MISS_NUM) THEN
231 l_ALSv_rec.last_updated_by := NULL;
232 END IF;
233 IF (l_ALSv_rec.last_update_date = okl_api.G_MISS_DATE) THEN
234 l_ALSv_rec.last_update_date := NULL;
235 END IF;
236 IF (l_ALSv_rec.last_update_login = okl_api.G_MISS_NUM) THEN
237 l_ALSv_rec.last_update_login := NULL;
238 END IF;
239 RETURN(l_ALSv_rec);
240 END null_out_defaults;
241 ---------------------------------------------------------------------------
242 -- PROCEDURE Validate_Attributes
243 ---------------------------------------------------------------------------
244 ------------------------------------------------
245 -- Validate_Attributes for:OKL_AG_SOURCE_MAPS_V --
246 ------------------------------------------------
247 FUNCTION Validate_Attributes (
248 p_ALSv_rec IN ALSv_rec_type
249 ) RETURN VARCHAR2 IS
250
251 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
252 x_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
253
254 BEGIN
255 IF p_alsv_rec.ae_line_type = okl_api.G_MISS_CHAR OR
256 p_alsv_rec.ae_line_type IS NULL OR
257 p_alsv_rec.ae_line_type = 'NONE' THEN
258 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Account Type');
259 l_return_status := okl_api.G_RET_STS_ERROR;
260 RETURN(l_return_status);
261 END IF;
262
263 IF p_alsv_rec.source = okl_api.G_MISS_CHAR OR
264 p_alsv_rec.source IS NULL OR
265 p_alsv_rec.source = 'NONE' THEN
266 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Source Table');
267 l_return_status := okl_api.G_RET_STS_ERROR;
268 RETURN(l_return_status);
269 END IF;
270
271 IF p_alsv_rec.select_column = okl_api.G_MISS_CHAR OR
272 p_alsv_rec.select_column IS NULL OR
273 p_alsv_rec.select_column = 'NONE' THEN
274 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Select Column');
275 l_return_status := okl_api.G_RET_STS_ERROR;
276 RETURN(l_return_status);
277 END IF;
278 x_return_status := l_return_status;
279
280 RETURN(x_return_status);
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 -- store SQL error message on message stack for caller
285 okl_api.SET_MESSAGE(p_app_name => g_app_name,
286 p_msg_name => g_unexpected_error,
287 p_token1 => g_sqlcode_token,
288 p_token1_value => SQLCODE,
289 p_token2 => g_sqlerrm_token,
290 p_token2_value => SQLERRM);
291
292 --notify caller of an UNEXPECTED error
293 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
294
295 --return status to caller
296 RETURN x_return_status;
297 END;
298
299 --END Validate_Attributes;
300
301 ---------------------------------------------------------------------------
302 -- PROCEDURE Validate_Record
303 ---------------------------------------------------------------------------
304 --------------------------------------------
305 -- Validate_Record for:OKL_AG_SOURCE_MAPS_V --
306 --------------------------------------------
307 FUNCTION Validate_Record (
308 p_ALSv_rec IN ALSv_rec_type
309 ) RETURN VARCHAR2 IS
310 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
311 x_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
312 BEGIN
313 -- Validate_Unique_Tcn_Record
314 Validate_Unique_ALS_Record(x_return_status, p_ALSv_rec);
315 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
316 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
317 -- need to leave
318 l_return_status := x_return_status;
319 RAISE G_EXCEPTION_HALT_VALIDATION;
320 ELSE
321 -- record that there was an error
322 l_return_status := x_return_status;
323 END IF;
324 END IF;
325
326 RETURN(l_return_status);
327 EXCEPTION
328 WHEN G_EXCEPTION_HALT_VALIDATION THEN
329 -- exit with return status
330 NULL;
331 RETURN (l_return_status);
332
333 WHEN OTHERS THEN
334 -- store SQL error message on message stack for caller
335 okl_api.SET_MESSAGE(p_app_name => g_app_name,
336 p_msg_name => g_unexpected_error,
337 p_token1 => g_sqlcode_token,
338 p_token1_value => SQLCODE,
339 p_token2 => g_sqlerrm_token,
340 p_token2_value => SQLERRM);
341 -- notify caller of an UNEXPECTED error
342 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
343 RETURN(l_return_status);
344 END Validate_Record;
345
346 ---------------------------------------------------------------------------
347 -- PROCEDURE Migrate
348 ---------------------------------------------------------------------------
349 PROCEDURE migrate (
350 p_from IN ALSv_rec_type,
351 p_to IN OUT NOCOPY ALS_rec_type
352 ) IS
353 BEGIN
354 p_to.id := p_from.id;
355 p_to.object_version_number := p_from.object_version_number;
356 p_to.org_id := p_from.org_id;
357 p_to.AE_LINE_TYPE := p_from.AE_LINE_TYPE;
358 p_to.SOURCE := p_from.SOURCE;
359 p_to.PRIMARY_KEY_COLUMN := p_from.PRIMARY_KEY_COLUMN;
360 p_to.SELECT_COLUMN := p_from.SELECT_COLUMN;
361 p_to.created_by := p_from.created_by;
362 p_to.creation_date := p_from.creation_date;
363 p_to.last_updated_by := p_from.last_updated_by;
364 p_to.last_update_date := p_from.last_update_date;
365 p_to.last_update_login := p_from.last_update_login;
366 END migrate;
367 PROCEDURE migrate (
368 p_from IN ALS_rec_type,
369 p_to OUT NOCOPY ALSv_rec_type
370 ) IS
371 BEGIN
372 p_to.id := p_from.id;
373 p_to.object_version_number := p_from.object_version_number;
374 p_to.org_id := p_from.org_id;
375 p_to.AE_LINE_TYPE := p_from.AE_LINE_TYPE;
376 p_to.SOURCE := p_from.SOURCE;
377 p_to.PRIMARY_KEY_COLUMN := p_from.PRIMARY_KEY_COLUMN;
378 p_to.SELECT_COLUMN := p_from.SELECT_COLUMN;
379 p_to.created_by := p_from.created_by;
380 p_to.creation_date := p_from.creation_date;
381 p_to.last_updated_by := p_from.last_updated_by;
382 p_to.last_update_date := p_from.last_update_date;
383 p_to.last_update_login := p_from.last_update_login;
384 END migrate;
385
386 ---------------------------------------------------------------------------
387 -- PROCEDURE validate_row
388 ---------------------------------------------------------------------------
389 -----------------------------------------
390 -- validate_row for:OKL_AG_SOURCE_MAPS_V --
391 -----------------------------------------
392 PROCEDURE validate_row(
393 p_api_version IN NUMBER,
394 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398 p_ALSv_rec IN ALSv_rec_type) IS
399
400 l_api_version CONSTANT NUMBER := 1;
401 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
402 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
403 l_ALSv_rec ALSv_rec_type := p_ALSv_rec;
404 l_ALS_rec ALS_rec_type;
405 BEGIN
406 l_return_status := okl_api.START_ACTIVITY(l_api_name,
407 G_PKG_NAME,
408 p_init_msg_list,
409 l_api_version,
410 p_api_version,
411 '_PVT',
412 x_return_status);
413 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
414 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
415 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
416 RAISE okl_api.G_EXCEPTION_ERROR;
417 END IF;
418 --- Validate all non-missing attributes (Item Level Validation)
419 l_return_status := Validate_Attributes(l_ALSv_rec);
420 --- If any errors happen abort API
421 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
422 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
423 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
424 RAISE okl_api.G_EXCEPTION_ERROR;
425 END IF;
426 l_return_status := Validate_Record(l_ALSv_rec);
427 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
428 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
429 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
430 RAISE okl_api.G_EXCEPTION_ERROR;
431 END IF;
432 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
433 EXCEPTION
434 WHEN okl_api.G_EXCEPTION_ERROR THEN
435 x_return_status := okl_api.HANDLE_EXCEPTIONS
436 (
437 l_api_name,
438 G_PKG_NAME,
439 'okl_api.G_RET_STS_ERROR',
440 x_msg_count,
441 x_msg_data,
442 '_PVT'
443 );
444 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
445 x_return_status :=okl_api.HANDLE_EXCEPTIONS
446 (
447 l_api_name,
448 G_PKG_NAME,
449 'okl_api.G_RET_STS_UNEXP_ERROR',
450 x_msg_count,
451 x_msg_data,
452 '_PVT'
453 );
454 WHEN OTHERS THEN
455 x_return_status :=okl_api.HANDLE_EXCEPTIONS
456 (
457 l_api_name,
458 G_PKG_NAME,
459 'OTHERS',
460 x_msg_count,
461 x_msg_data,
462 '_PVT'
463 );
464 END validate_row;
465 ------------------------------------------
466 -- PL/SQL TBL validate_row for:ALSV_TBL --
467 ------------------------------------------
468 PROCEDURE validate_row(
469 p_api_version IN NUMBER,
470 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_msg_count OUT NOCOPY NUMBER,
473 x_msg_data OUT NOCOPY VARCHAR2,
474 p_ALSv_tbl IN ALSv_tbl_type) IS
475
476 l_api_version CONSTANT NUMBER := 1;
477 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
478 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
479 l_overall_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
480 i NUMBER := 0;
481 BEGIN
482 okl_api.init_msg_list(p_init_msg_list);
483 -- Make sure PL/SQL table has records in it before passing
484 IF (p_ALSv_tbl.COUNT > 0) THEN
485 i := p_ALSv_tbl.FIRST;
486 LOOP
487 validate_row (
488 p_api_version => p_api_version,
489 p_init_msg_list => okl_api.G_FALSE,
490 x_return_status => x_return_status,
491 x_msg_count => x_msg_count,
492 x_msg_data => x_msg_data,
493 p_ALSv_rec => p_ALSv_tbl(i));
494 IF x_return_status <> okl_api.G_RET_STS_SUCCESS THEN
495 IF l_overall_status <> okl_api.G_RET_STS_UNEXP_ERROR THEN
496 l_overall_status := x_return_status;
497 END IF;
498 END IF;
499 EXIT WHEN (i = p_ALSv_tbl.LAST);
500 i := p_ALSv_tbl.NEXT(i);
501 END LOOP;
502 x_return_status := l_overall_status;
503 END IF;
504 EXCEPTION
505 WHEN okl_api.G_EXCEPTION_ERROR THEN
506 x_return_status := okl_api.HANDLE_EXCEPTIONS
507 (
508 l_api_name,
509 G_PKG_NAME,
510 'okl_api.G_RET_STS_ERROR',
511 x_msg_count,
512 x_msg_data,
513 '_PVT'
514 );
515 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
516 x_return_status :=okl_api.HANDLE_EXCEPTIONS
517 (
518 l_api_name,
519 G_PKG_NAME,
520 'okl_api.G_RET_STS_UNEXP_ERROR',
521 x_msg_count,
522 x_msg_data,
523 '_PVT'
524 );
525 WHEN OTHERS THEN
526 x_return_status :=okl_api.HANDLE_EXCEPTIONS
527 (
528 l_api_name,
529 G_PKG_NAME,
530 'OTHERS',
531 x_msg_count,
532 x_msg_data,
533 '_PVT'
534 );
535 END validate_row;
536
537 ---------------------------------------------------------------------------
538 -- PROCEDURE insert_row
539 ---------------------------------------------------------------------------
540 -------------------------------------
541 -- insert_row for:OKL_AG_SOURCE_MAPS --
542 -------------------------------------
543 PROCEDURE insert_row(
544 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2,
548 p_ALS_rec IN ALS_rec_type,
549 x_ALS_rec OUT NOCOPY ALS_rec_type) IS
550
551 l_api_version CONSTANT NUMBER := 1;
552 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_insert_row';
553 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
554 l_ALS_rec ALS_rec_type := p_ALS_rec;
555 l_def_ALS_rec ALS_rec_type;
556 -----------------------------------------
557 -- Set_Attributes for:OKL_AG_SOURCE_MAPS --
558 -----------------------------------------
559
560 FUNCTION Set_Attributes (
561 p_ALS_rec IN ALS_rec_type,
562 x_ALS_rec OUT NOCOPY ALS_rec_type
563 ) RETURN VARCHAR2 IS
564 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
565 BEGIN
566 x_ALS_rec := p_ALS_rec;
567 RETURN(l_return_status);
568 END Set_Attributes;
569 BEGIN
570 l_return_status := okl_api.START_ACTIVITY(l_api_name,
571 p_init_msg_list,
572 '_PVT',
573 x_return_status);
574 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
575 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
576 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
577 RAISE okl_api.G_EXCEPTION_ERROR;
578 END IF;
579
580 --- Setting item attributes
581 l_return_status := Set_Attributes(
582 p_ALS_rec, -- IN
583 l_ALS_rec); -- OUT
584 --- If any errors happen abort API
585 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
586 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
587 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
588 RAISE okl_api.G_EXCEPTION_ERROR;
589 END IF;
590
591 -- Defaulting the PRIMARY_KEY_COLUMN Added by Saran
592
593 l_als_rec.primary_key_column := 'DEFAULT';
594
595
596 INSERT INTO OKL_AG_SOURCE_MAPS(
597 id,
598 object_version_number,
599 org_id,
600 AE_LINE_TYPE,
601 SOURCE,
602 PRIMARY_KEY_COLUMN,
603 SELECT_COLUMN,
604 created_by,
605 creation_date,
606 last_updated_by,
607 last_update_date,
608 last_update_login)
609 VALUES (
610 l_ALS_rec.id,
611 l_ALS_rec.object_version_number,
612 l_ALS_rec.org_id,
613 l_ALS_rec.AE_LINE_TYPE,
614 l_ALS_rec.SOURCE,
615 l_ALS_rec.PRIMARY_KEY_COLUMN,
616 l_ALS_rec.SELECT_COLUMN,
617 l_ALS_rec.created_by,
618 l_ALS_rec.creation_date,
619 l_ALS_rec.last_updated_by,
620 l_ALS_rec.last_update_date,
621 l_ALS_rec.last_update_login);
622 -- Set OUT values
623 x_ALS_rec := l_ALS_rec;
624 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
625 EXCEPTION
626 WHEN okl_api.G_EXCEPTION_ERROR THEN
627 x_return_status := okl_api.HANDLE_EXCEPTIONS
628 (
629 l_api_name,
630 G_PKG_NAME,
631 'okl_api.G_RET_STS_ERROR',
632 x_msg_count,
633 x_msg_data,
634 '_PVT'
635 );
636 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
637 x_return_status :=okl_api.HANDLE_EXCEPTIONS
638 (
639 l_api_name,
640 G_PKG_NAME,
641 'okl_api.G_RET_STS_UNEXP_ERROR',
642 x_msg_count,
643 x_msg_data,
644 '_PVT'
645 );
646 WHEN OTHERS THEN
647 x_return_status :=okl_api.HANDLE_EXCEPTIONS
648 (
649 l_api_name,
650 G_PKG_NAME,
651 'OTHERS',
652 x_msg_count,
653 x_msg_data,
654 '_PVT'
655 );
656 END insert_row;
657 ---------------------------------------
658 -- insert_row for:OKL_AG_SOURCE_MAPS_V --
659 ---------------------------------------
660 PROCEDURE insert_row(
661 p_api_version IN NUMBER,
662 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
663 x_return_status OUT NOCOPY VARCHAR2,
664 x_msg_count OUT NOCOPY NUMBER,
665 x_msg_data OUT NOCOPY VARCHAR2,
666 p_ALSv_rec IN ALSv_rec_type,
667 x_ALSv_rec OUT NOCOPY ALSv_rec_type) IS
668
669 l_api_version CONSTANT NUMBER := 1;
670 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
671 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
672 l_ALSv_rec ALSv_rec_type;
673 l_def_ALSv_rec ALSv_rec_type;
674 l_ALS_rec ALS_rec_type;
675 lx_ALS_rec ALS_rec_type;
676 -------------------------------
677 -- FUNCTION fill_who_columns --
678 -------------------------------
679 FUNCTION fill_who_columns (
680 p_ALSv_rec IN ALSv_rec_type
681 ) RETURN ALSv_rec_type IS
682 l_ALSv_rec ALSv_rec_type := p_ALSv_rec;
683 BEGIN
684 l_ALSv_rec.CREATION_DATE := SYSDATE;
685 l_ALSv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
686 l_ALSv_rec.LAST_UPDATE_DATE := SYSDATE;
687 l_ALSv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
688 l_ALSv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
689 RETURN(l_ALSv_rec);
690 END fill_who_columns;
691 -------------------------------------------
692 -- Set_Attributes for:OKL_AG_SOURCE_MAPS_V --
693 -------------------------------------------
694 FUNCTION Set_Attributes (
695 p_ALSv_rec IN ALSv_rec_type,
696 x_ALSv_rec OUT NOCOPY ALSv_rec_type
697 ) RETURN VARCHAR2 IS
698 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
699 BEGIN
700 x_ALSv_rec := p_ALSv_rec;
701 x_ALSv_rec.OBJECT_VERSION_NUMBER := 1;
702 x_ALSv_rec.ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID();
703 RETURN(l_return_status);
704 END Set_Attributes;
705 BEGIN
706 l_return_status := okl_api.START_ACTIVITY(l_api_name,
707 G_PKG_NAME,
708 p_init_msg_list,
709 l_api_version,
710 p_api_version,
711 '_PVT',
712 x_return_status);
713
714 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
715 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
716 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
717 RAISE okl_api.G_EXCEPTION_ERROR;
718 END IF;
719 l_ALSv_rec := null_out_defaults(p_ALSv_rec);
720 -- Set primary key value
721 l_ALSv_rec.ID := get_seq_id;
722 --- Setting item attributes
723 l_return_status := Set_Attributes(
724 l_ALSv_rec, -- IN
725 l_def_ALSv_rec); -- OUT
726 --- If any errors happen abort API
727 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
728 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
729 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
730 RAISE okl_api.G_EXCEPTION_ERROR;
731 END IF;
732 l_def_ALSv_rec := fill_who_columns(l_def_ALSv_rec);
733 --- Validate all non-missing attributes (Item Level Validation)
734 l_return_status := Validate_Attributes(l_def_ALSv_rec);
735 --- If any errors happen abort API
736 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
737 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
738 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
739 RAISE okl_api.G_EXCEPTION_ERROR;
740 END IF;
741 l_return_status := Validate_Record(l_def_ALSv_rec);
742 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
743 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
744 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
745 RAISE okl_api.G_EXCEPTION_ERROR;
746 END IF;
747 --------------------------------------
748 -- Move VIEW record to "Child" records
749 --------------------------------------
750 migrate(l_def_ALSv_rec, l_ALS_rec);
751 --------------------------------------------
752 -- Call the INSERT_ROW for each child record
753 --------------------------------------------
754 insert_row(
755 p_init_msg_list,
756 x_return_status,
757 x_msg_count,
758 x_msg_data,
759 l_ALS_rec,
760 lx_ALS_rec
761 );
762 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
763 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
764 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
765 RAISE okl_api.G_EXCEPTION_ERROR;
766 END IF;
767 migrate(lx_ALS_rec, l_def_ALSv_rec);
768 -- Set OUT values
769 x_ALSv_rec := l_def_ALSv_rec;
770 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
771 EXCEPTION
772 WHEN okl_api.G_EXCEPTION_ERROR THEN
773 x_return_status := okl_api.HANDLE_EXCEPTIONS
774 (
775 l_api_name,
776 G_PKG_NAME,
777 'okl_api.G_RET_STS_ERROR',
778 x_msg_count,
779 x_msg_data,
780 '_PVT'
781 );
782 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
783 x_return_status :=okl_api.HANDLE_EXCEPTIONS
784 (
785 l_api_name,
786 G_PKG_NAME,
787 'okl_api.G_RET_STS_UNEXP_ERROR',
788 x_msg_count,
789 x_msg_data,
790 '_PVT'
791 );
792 WHEN OTHERS THEN
793 x_return_status :=okl_api.HANDLE_EXCEPTIONS
794 (
795 l_api_name,
796 G_PKG_NAME,
797 'OTHERS',
798 x_msg_count,
799 x_msg_data,
800 '_PVT'
801 );
802 END insert_row;
803 ----------------------------------------
804 -- PL/SQL TBL insert_row for:ALSV_TBL --
805 ----------------------------------------
806 PROCEDURE insert_row(
807 p_api_version IN NUMBER,
808 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
809 x_return_status OUT NOCOPY VARCHAR2,
810 x_msg_count OUT NOCOPY NUMBER,
811 x_msg_data OUT NOCOPY VARCHAR2,
812 p_ALSv_tbl IN ALSv_tbl_type,
813 x_ALSv_tbl OUT NOCOPY ALSv_tbl_type) IS
814
815 l_api_version CONSTANT NUMBER := 1;
816 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
817 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
818 l_overall_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
819 i NUMBER := 0;
820 BEGIN
821 okl_api.init_msg_list(p_init_msg_list);
822 -- Make sure PL/SQL table has records in it before passing
823 IF (p_ALSv_tbl.COUNT > 0) THEN
824 i := p_ALSv_tbl.FIRST;
825 LOOP
826 insert_row (
827 p_api_version => p_api_version,
828 p_init_msg_list => okl_api.G_FALSE,
829 x_return_status => x_return_status,
830 x_msg_count => x_msg_count,
831 x_msg_data => x_msg_data,
832 p_ALSv_rec => p_ALSv_tbl(i),
833 x_ALSv_rec => x_ALSv_tbl(i));
834 IF x_return_status <> okl_api.G_RET_STS_SUCCESS THEN
835 IF l_overall_status <> okl_api.G_RET_STS_UNEXP_ERROR THEN
836 l_overall_status := x_return_status;
837 END IF;
838 END IF;
839 EXIT WHEN (i = p_ALSv_tbl.LAST);
840 i := p_ALSv_tbl.NEXT(i);
841 END LOOP;
842 x_return_status := l_overall_status;
843 END IF;
844 EXCEPTION
845 WHEN okl_api.G_EXCEPTION_ERROR THEN
846 x_return_status := okl_api.HANDLE_EXCEPTIONS
847 (
848 l_api_name,
849 G_PKG_NAME,
850 'okl_api.G_RET_STS_ERROR',
851 x_msg_count,
852 x_msg_data,
853 '_PVT'
854 );
855 WHEN okl_api.G_EXCEPTION_UNEXPECTED_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_UNEXP_ERROR',
861 x_msg_count,
862 x_msg_data,
863 '_PVT'
864 );
865 WHEN OTHERS THEN
866 x_return_status :=okl_api.HANDLE_EXCEPTIONS
867 (
868 l_api_name,
869 G_PKG_NAME,
870 'OTHERS',
871 x_msg_count,
872 x_msg_data,
873 '_PVT'
874 );
875 END insert_row;
876
877 ---------------------------------------------------------------------------
878 -- PROCEDURE lock_row
879 ---------------------------------------------------------------------------
880 -----------------------------------
881 -- lock_row for:OKL_AG_SOURCE_MAPS --
882 -----------------------------------
883 PROCEDURE lock_row(
884 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
885 x_return_status OUT NOCOPY VARCHAR2,
886 x_msg_count OUT NOCOPY NUMBER,
887 x_msg_data OUT NOCOPY VARCHAR2,
888 p_ALS_rec IN ALS_rec_type) IS
889
890 E_Resource_Busy EXCEPTION;
891 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
892 CURSOR lock_csr (p_ALS_rec IN ALS_rec_type) IS
893 SELECT OBJECT_VERSION_NUMBER
894 FROM OKL_AG_SOURCE_MAPS
895 WHERE ID = p_ALS_rec.id
896 AND OBJECT_VERSION_NUMBER = p_ALS_rec.object_version_number
897 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
898
899 CURSOR lchk_csr (p_ALS_rec IN ALS_rec_type) IS
900 SELECT OBJECT_VERSION_NUMBER
901 FROM OKL_AG_SOURCE_MAPS
902 WHERE ID = p_ALS_rec.id;
903 l_api_version CONSTANT NUMBER := 1;
904 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_lock_row';
905 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
906 l_object_version_number OKL_AG_SOURCE_MAPS.OBJECT_VERSION_NUMBER%TYPE;
907 lc_object_version_number OKL_AG_SOURCE_MAPS.OBJECT_VERSION_NUMBER%TYPE;
908 l_row_notfound BOOLEAN := FALSE;
909 lc_row_notfound BOOLEAN := FALSE;
910 BEGIN
911 l_return_status := okl_api.START_ACTIVITY(l_api_name,
912 p_init_msg_list,
913 '_PVT',
914 x_return_status);
915 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
916 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
917 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
918 RAISE okl_api.G_EXCEPTION_ERROR;
919 END IF;
920 BEGIN
921 OPEN lock_csr(p_ALS_rec);
922 FETCH lock_csr INTO l_object_version_number;
923 l_row_notfound := lock_csr%NOTFOUND;
924 CLOSE lock_csr;
925 EXCEPTION
926 WHEN E_Resource_Busy THEN
927 IF (lock_csr%ISOPEN) THEN
928 CLOSE lock_csr;
929 END IF;
930 okl_api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
931 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
932 END;
933
934 IF ( l_row_notfound ) THEN
935 OPEN lchk_csr(p_ALS_rec);
936 FETCH lchk_csr INTO lc_object_version_number;
937 lc_row_notfound := lchk_csr%NOTFOUND;
938 CLOSE lchk_csr;
939 END IF;
940 IF (lc_row_notfound) THEN
941 okl_api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
942 RAISE okl_api.G_EXCEPTION_ERROR;
943 ELSIF lc_object_version_number > p_ALS_rec.object_version_number THEN
944 okl_api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
945 RAISE okl_api.G_EXCEPTION_ERROR;
946 ELSIF lc_object_version_number <> p_ALS_rec.object_version_number THEN
947 okl_api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
948 RAISE okl_api.G_EXCEPTION_ERROR;
949 ELSIF lc_object_version_number = -1 THEN
950 okl_api.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
951 RAISE okl_api.G_EXCEPTION_ERROR;
952 END IF;
953 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
954 EXCEPTION
955 WHEN okl_api.G_EXCEPTION_ERROR THEN
956 x_return_status := okl_api.HANDLE_EXCEPTIONS
957 (
958 l_api_name,
959 G_PKG_NAME,
960 'okl_api.G_RET_STS_ERROR',
961 x_msg_count,
962 x_msg_data,
963 '_PVT'
964 );
965 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
966 x_return_status :=okl_api.HANDLE_EXCEPTIONS
967 (
968 l_api_name,
969 G_PKG_NAME,
970 'okl_api.G_RET_STS_UNEXP_ERROR',
971 x_msg_count,
972 x_msg_data,
973 '_PVT'
974 );
975 WHEN OTHERS THEN
976 x_return_status :=okl_api.HANDLE_EXCEPTIONS
977 (
978 l_api_name,
979 G_PKG_NAME,
980 'OTHERS',
981 x_msg_count,
982 x_msg_data,
983 '_PVT'
984 );
985 END lock_row;
986 -------------------------------------
987 -- lock_row for:OKL_AG_SOURCE_MAPS_V --
988 -------------------------------------
989 PROCEDURE lock_row(
990 p_api_version IN NUMBER,
991 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
992 x_return_status OUT NOCOPY VARCHAR2,
993 x_msg_count OUT NOCOPY NUMBER,
994 x_msg_data OUT NOCOPY VARCHAR2,
995 p_ALSv_rec IN ALSv_rec_type) IS
996
997 l_api_version CONSTANT NUMBER := 1;
998 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
999 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1000 l_ALS_rec ALS_rec_type;
1001 BEGIN
1002 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1003 G_PKG_NAME,
1004 p_init_msg_list,
1005 l_api_version,
1006 p_api_version,
1007 '_PVT',
1008 x_return_status);
1009 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1010 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1011 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1012 RAISE okl_api.G_EXCEPTION_ERROR;
1013 END IF;
1014 --------------------------------------
1015 -- Move VIEW record to "Child" records
1016 --------------------------------------
1017 migrate(p_ALSv_rec, l_ALS_rec);
1018 --------------------------------------------
1019 -- Call the LOCK_ROW for each child record
1020 --------------------------------------------
1021 lock_row(
1022 p_init_msg_list,
1023 x_return_status,
1024 x_msg_count,
1025 x_msg_data,
1026 l_ALS_rec
1027 );
1028 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1029 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1030 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1031 RAISE okl_api.G_EXCEPTION_ERROR;
1032 END IF;
1033 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1034 EXCEPTION
1035 WHEN okl_api.G_EXCEPTION_ERROR THEN
1036 x_return_status := okl_api.HANDLE_EXCEPTIONS
1037 (
1038 l_api_name,
1039 G_PKG_NAME,
1040 'okl_api.G_RET_STS_ERROR',
1041 x_msg_count,
1042 x_msg_data,
1043 '_PVT'
1044 );
1045 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1046 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1047 (
1048 l_api_name,
1049 G_PKG_NAME,
1050 'okl_api.G_RET_STS_UNEXP_ERROR',
1051 x_msg_count,
1052 x_msg_data,
1053 '_PVT'
1054 );
1055 WHEN OTHERS THEN
1056 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1057 (
1058 l_api_name,
1059 G_PKG_NAME,
1060 'OTHERS',
1061 x_msg_count,
1062 x_msg_data,
1063 '_PVT'
1064 );
1065 END lock_row;
1066 --------------------------------------
1067 -- PL/SQL TBL lock_row for:ALSV_TBL --
1068 --------------------------------------
1069 PROCEDURE lock_row(
1070 p_api_version IN NUMBER,
1071 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1072 x_return_status OUT NOCOPY VARCHAR2,
1073 x_msg_count OUT NOCOPY NUMBER,
1074 x_msg_data OUT NOCOPY VARCHAR2,
1075 p_ALSv_tbl IN ALSv_tbl_type) IS
1076
1077 l_api_version CONSTANT NUMBER := 1;
1078 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1079 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1080 l_overall_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1081 i NUMBER := 0;
1082 BEGIN
1083 okl_api.init_msg_list(p_init_msg_list);
1084 -- Make sure PL/SQL table has records in it before passing
1085 IF (p_ALSv_tbl.COUNT > 0) THEN
1086 i := p_ALSv_tbl.FIRST;
1087 LOOP
1088 lock_row (
1089 p_api_version => p_api_version,
1090 p_init_msg_list => okl_api.G_FALSE,
1091 x_return_status => x_return_status,
1092 x_msg_count => x_msg_count,
1093 x_msg_data => x_msg_data,
1094 p_ALSv_rec => p_ALSv_tbl(i));
1095 IF x_return_status <> okl_api.G_RET_STS_SUCCESS THEN
1096 IF l_overall_status <> okl_api.G_RET_STS_UNEXP_ERROR THEN
1097 l_overall_status := x_return_status;
1098 END IF;
1099 END IF;
1100 EXIT WHEN (i = p_ALSv_tbl.LAST);
1101 i := p_ALSv_tbl.NEXT(i);
1102 END LOOP;
1103 x_return_status := l_overall_status;
1104 END IF;
1105 EXCEPTION
1106 WHEN okl_api.G_EXCEPTION_ERROR THEN
1107 x_return_status := okl_api.HANDLE_EXCEPTIONS
1108 (
1109 l_api_name,
1110 G_PKG_NAME,
1111 'okl_api.G_RET_STS_ERROR',
1112 x_msg_count,
1113 x_msg_data,
1114 '_PVT'
1115 );
1116 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1117 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1118 (
1119 l_api_name,
1120 G_PKG_NAME,
1121 'okl_api.G_RET_STS_UNEXP_ERROR',
1122 x_msg_count,
1123 x_msg_data,
1124 '_PVT'
1125 );
1126 WHEN OTHERS THEN
1127 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1128 (
1129 l_api_name,
1130 G_PKG_NAME,
1131 'OTHERS',
1132 x_msg_count,
1133 x_msg_data,
1134 '_PVT'
1135 );
1136 END lock_row;
1137
1138 ---------------------------------------------------------------------------
1139 -- PROCEDURE update_row
1140 ---------------------------------------------------------------------------
1141 -------------------------------------
1142 -- update_row for:OKL_AG_SOURCE_MAPS --
1143 -------------------------------------
1144 PROCEDURE update_row(
1145 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1146 x_return_status OUT NOCOPY VARCHAR2,
1147 x_msg_count OUT NOCOPY NUMBER,
1148 x_msg_data OUT NOCOPY VARCHAR2,
1149 p_ALS_rec IN ALS_rec_type,
1150 x_ALS_rec OUT NOCOPY ALS_rec_type) IS
1151
1152 l_api_version CONSTANT NUMBER := 1;
1153 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_update_row';
1154 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1155 l_ALS_rec ALS_rec_type := p_ALS_rec;
1156 l_def_ALS_rec ALS_rec_type;
1157 l_row_notfound BOOLEAN := TRUE;
1158 ----------------------------------
1159 -- FUNCTION populate_new_record --
1160 ----------------------------------
1161 FUNCTION populate_new_record (
1162 p_ALS_rec IN ALS_rec_type,
1163 x_ALS_rec OUT NOCOPY ALS_rec_type
1164 ) RETURN VARCHAR2 IS
1165 l_ALS_rec ALS_rec_type;
1166 l_row_notfound BOOLEAN := TRUE;
1167 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1168 BEGIN
1169 x_ALS_rec := p_ALS_rec;
1170 -- Get current database values
1171 l_ALS_rec := get_rec(p_ALS_rec, l_row_notfound);
1172 IF (l_row_notfound) THEN
1173 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
1174 END IF;
1175 IF (x_ALS_rec.id = okl_api.G_MISS_NUM)
1176 THEN
1177 x_ALS_rec.id := l_ALS_rec.id;
1178 END IF;
1179 IF (x_ALS_rec.object_version_number = okl_api.G_MISS_NUM)
1180 THEN
1181 x_ALS_rec.object_version_number := l_ALS_rec.object_version_number;
1182 END IF;
1183 IF (x_ALS_rec.org_id = okl_api.G_MISS_NUM)
1184 THEN
1185 x_ALS_rec.org_id := l_ALS_rec.org_id;
1186 END IF;
1187 IF (x_ALS_rec.AE_LINE_TYPE = okl_api.G_MISS_CHAR)
1188 THEN
1189 x_ALS_rec.AE_LINE_TYPE := l_ALS_rec.AE_LINE_TYPE;
1190 END IF;
1191 IF (x_ALS_rec.SOURCE = okl_api.G_MISS_CHAR)
1192 THEN
1193 x_ALS_rec.SOURCE := l_ALS_rec.SOURCE;
1194 END IF;
1195 IF (x_ALS_rec.PRIMARY_KEY_COLUMN = okl_api.G_MISS_CHAR)
1196 THEN
1197 x_ALS_rec.PRIMARY_KEY_COLUMN := l_ALS_rec.PRIMARY_KEY_COLUMN;
1198 END IF;
1199 IF (x_ALS_rec.SELECT_COLUMN = okl_api.G_MISS_CHAR)
1200 THEN
1201 x_ALS_rec.SELECT_COLUMN := l_ALS_rec.SELECT_COLUMN;
1202 END IF;
1203 IF (x_ALS_rec.created_by = okl_api.G_MISS_NUM)
1204 THEN
1205 x_ALS_rec.created_by := l_ALS_rec.created_by;
1206 END IF;
1207 IF (x_ALS_rec.creation_date = okl_api.G_MISS_DATE)
1208 THEN
1209 x_ALS_rec.creation_date := l_ALS_rec.creation_date;
1210 END IF;
1211 IF (x_ALS_rec.last_updated_by = okl_api.G_MISS_NUM)
1212 THEN
1213 x_ALS_rec.last_updated_by := l_ALS_rec.last_updated_by;
1214 END IF;
1215 IF (x_ALS_rec.last_update_date = okl_api.G_MISS_DATE)
1216 THEN
1217 x_ALS_rec.last_update_date := l_ALS_rec.last_update_date;
1218 END IF;
1219 IF (x_ALS_rec.last_update_login = okl_api.G_MISS_NUM)
1220 THEN
1221 x_ALS_rec.last_update_login := l_ALS_rec.last_update_login;
1222 END IF;
1223 RETURN(l_return_status);
1224 END populate_new_record;
1225 -----------------------------------------
1226 -- Set_Attributes for:OKL_AG_SOURCE_MAPS --
1227 -----------------------------------------
1228 FUNCTION Set_Attributes (
1229 p_ALS_rec IN ALS_rec_type,
1230 x_ALS_rec OUT NOCOPY ALS_rec_type
1231 ) RETURN VARCHAR2 IS
1232 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1233 BEGIN
1234 x_ALS_rec := p_ALS_rec;
1235 x_ALS_rec.OBJECT_VERSION_NUMBER := NVL(x_ALS_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1236 RETURN(l_return_status);
1237 END Set_Attributes;
1238 BEGIN
1239 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1240 p_init_msg_list,
1241 '_PVT',
1242 x_return_status);
1243 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1244 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1245 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1246 RAISE okl_api.G_EXCEPTION_ERROR;
1247 END IF;
1248 --- Setting item attributes
1249 l_return_status := Set_Attributes(
1250 p_ALS_rec, -- IN
1251 l_ALS_rec); -- OUT
1252 --- If any errors happen abort API
1253 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1254 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1255 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1256 RAISE okl_api.G_EXCEPTION_ERROR;
1257 END IF;
1258 l_return_status := populate_new_record(l_ALS_rec, l_def_ALS_rec);
1259 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1260 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1261 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1262 RAISE okl_api.G_EXCEPTION_ERROR;
1263 END IF;
1264 UPDATE OKL_AG_SOURCE_MAPS
1265 SET OBJECT_VERSION_NUMBER = l_def_ALS_rec.object_version_number,
1266 ORG_ID = l_def_ALS_rec.org_id,
1267 AE_LINE_TYPE = l_def_ALS_rec.AE_LINE_TYPE,
1268 SOURCE = l_def_ALS_rec.SOURCE,
1269 PRIMARY_KEY_COLUMN = l_def_ALS_rec.PRIMARY_KEY_COLUMN,
1270 SELECT_COLUMN = l_def_ALS_rec.SELECT_COLUMN,
1271 CREATED_BY = l_def_ALS_rec.created_by,
1272 CREATION_DATE = l_def_ALS_rec.creation_date,
1273 LAST_UPDATED_BY = l_def_ALS_rec.last_updated_by,
1274 LAST_UPDATE_DATE = l_def_ALS_rec.last_update_date,
1275 LAST_UPDATE_LOGIN = l_def_ALS_rec.last_update_login
1276 WHERE ID = l_def_ALS_rec.id;
1277
1278 x_ALS_rec := l_def_ALS_rec;
1279 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1280 EXCEPTION
1281 WHEN okl_api.G_EXCEPTION_ERROR THEN
1282 x_return_status := okl_api.HANDLE_EXCEPTIONS
1283 (
1284 l_api_name,
1285 G_PKG_NAME,
1286 'okl_api.G_RET_STS_ERROR',
1287 x_msg_count,
1288 x_msg_data,
1289 '_PVT'
1290 );
1291 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1292 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1293 (
1294 l_api_name,
1295 G_PKG_NAME,
1296 'okl_api.G_RET_STS_UNEXP_ERROR',
1297 x_msg_count,
1298 x_msg_data,
1299 '_PVT'
1300 );
1301 WHEN OTHERS THEN
1302 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1303 (
1304 l_api_name,
1305 G_PKG_NAME,
1306 'OTHERS',
1307 x_msg_count,
1308 x_msg_data,
1309 '_PVT'
1310 );
1311 END update_row;
1312 ---------------------------------------
1313 -- update_row for:OKL_AG_SOURCE_MAPS_V --
1314 ---------------------------------------
1315 PROCEDURE update_row(
1316 p_api_version IN NUMBER,
1317 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1318 x_return_status OUT NOCOPY VARCHAR2,
1319 x_msg_count OUT NOCOPY NUMBER,
1320 x_msg_data OUT NOCOPY VARCHAR2,
1321 p_ALSv_rec IN ALSv_rec_type,
1322 x_ALSv_rec OUT NOCOPY ALSv_rec_type) IS
1323
1324 l_api_version CONSTANT NUMBER := 1;
1325 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1326 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1327 l_ALSv_rec ALSv_rec_type := p_ALSv_rec;
1328 l_def_ALSv_rec ALSv_rec_type;
1329 l_ALS_rec ALS_rec_type;
1330 lx_ALS_rec ALS_rec_type;
1331 -------------------------------
1332 -- FUNCTION fill_who_columns --
1333 -------------------------------
1334 FUNCTION fill_who_columns (
1335 p_ALSv_rec IN ALSv_rec_type
1336 ) RETURN ALSv_rec_type IS
1337 l_ALSv_rec ALSv_rec_type := p_ALSv_rec;
1338 BEGIN
1339 l_ALSv_rec.LAST_UPDATE_DATE := SYSDATE;
1340 l_ALSv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1341 l_ALSv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1342 RETURN(l_ALSv_rec);
1343 END fill_who_columns;
1344 ----------------------------------
1345 -- FUNCTION populate_new_record --
1346 ----------------------------------
1347 FUNCTION populate_new_record (
1348 p_ALSv_rec IN ALSv_rec_type,
1349 x_ALSv_rec OUT NOCOPY ALSv_rec_type
1350 ) RETURN VARCHAR2 IS
1351 l_ALSv_rec ALSv_rec_type;
1352 l_row_notfound BOOLEAN := TRUE;
1353 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1354 BEGIN
1355 x_ALSv_rec := p_ALSv_rec;
1356 -- Get current database values
1357 l_ALSv_rec := get_rec(p_ALSv_rec, l_row_notfound);
1358 IF (l_row_notfound) THEN
1359 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
1360 END IF;
1361 IF (x_ALSv_rec.id = okl_api.G_MISS_NUM)
1362 THEN
1363 x_ALSv_rec.id := l_ALSv_rec.id;
1364 END IF;
1365 IF (x_ALSv_rec.object_version_number = okl_api.G_MISS_NUM)
1366 THEN
1367 x_ALSv_rec.object_version_number := l_ALSv_rec.object_version_number;
1368 END IF;
1369 IF (x_ALSv_rec.AE_LINE_TYPE = okl_api.G_MISS_CHAR)
1370 THEN
1371 x_ALSv_rec.AE_LINE_TYPE := l_ALSv_rec.AE_LINE_TYPE;
1372 END IF;
1373 IF (x_ALSv_rec.SOURCE = okl_api.G_MISS_CHAR)
1374 THEN
1375 x_ALSv_rec.SOURCE := l_ALSv_rec.SOURCE;
1376 END IF;
1377 IF (x_ALSv_rec.PRIMARY_KEY_COLUMN = okl_api.G_MISS_CHAR)
1378 THEN
1379 x_ALSv_rec.PRIMARY_KEY_COLUMN := l_ALSv_rec.PRIMARY_KEY_COLUMN;
1380 END IF;
1381 IF (x_ALSv_rec.SELECT_COLUMN = okl_api.G_MISS_CHAR)
1382 THEN
1383 x_ALSv_rec.SELECT_COLUMN := l_ALSv_rec.SELECT_COLUMN;
1384 END IF;
1385 IF (x_ALSv_rec.org_id = okl_api.G_MISS_NUM)
1386 THEN
1387 x_ALSv_rec.org_id := l_ALSv_rec.org_id;
1388 END IF;
1389 IF (x_ALSv_rec.created_by = okl_api.G_MISS_NUM)
1390 THEN
1391 x_ALSv_rec.created_by := l_ALSv_rec.created_by;
1392 END IF;
1393 IF (x_ALSv_rec.creation_date = okl_api.G_MISS_DATE)
1394 THEN
1395 x_ALSv_rec.creation_date := l_ALSv_rec.creation_date;
1396 END IF;
1397 IF (x_ALSv_rec.last_updated_by = okl_api.G_MISS_NUM)
1398 THEN
1399 x_ALSv_rec.last_updated_by := l_ALSv_rec.last_updated_by;
1400 END IF;
1401 IF (x_ALSv_rec.last_update_date = okl_api.G_MISS_DATE)
1402 THEN
1403 x_ALSv_rec.last_update_date := l_ALSv_rec.last_update_date;
1404 END IF;
1405 IF (x_ALSv_rec.last_update_login = okl_api.G_MISS_NUM)
1406 THEN
1407 x_ALSv_rec.last_update_login := l_ALSv_rec.last_update_login;
1408 END IF;
1409 RETURN(l_return_status);
1410 END populate_new_record;
1411 -------------------------------------------
1412 -- Set_Attributes for:OKL_AG_SOURCE_MAPS_V --
1413 -------------------------------------------
1414 FUNCTION Set_Attributes (
1415 p_ALSv_rec IN ALSv_rec_type,
1416 x_ALSv_rec OUT NOCOPY ALSv_rec_type
1417 ) RETURN VARCHAR2 IS
1418 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1419 BEGIN
1420 x_ALSv_rec := p_ALSv_rec;
1421 RETURN(l_return_status);
1422 END Set_Attributes;
1423 BEGIN
1424 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1425 G_PKG_NAME,
1426 p_init_msg_list,
1427 l_api_version,
1428 p_api_version,
1429 '_PVT',
1430 x_return_status);
1431
1432 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1433 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1434 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1435 RAISE okl_api.G_EXCEPTION_ERROR;
1436 END IF;
1437 --- Setting item attributes
1438 l_return_status := Set_Attributes(
1439 p_ALSv_rec, -- IN
1440 l_ALSv_rec); -- OUT
1441 --- If any errors happen abort API
1442 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1443 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1444 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1445 RAISE okl_api.G_EXCEPTION_ERROR;
1446 END IF;
1447 l_return_status := populate_new_record(l_ALSv_rec, l_def_ALSv_rec);
1448 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1449 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1450 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1451 RAISE okl_api.G_EXCEPTION_ERROR;
1452 END IF;
1453 l_def_ALSv_rec := fill_who_columns(l_def_ALSv_rec);
1454 --- Validate all non-missing attributes (Item Level Validation)
1455 l_return_status := Validate_Attributes(l_def_ALSv_rec);
1456 --- If any errors happen abort API
1457 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1458 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1459 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1460 RAISE okl_api.G_EXCEPTION_ERROR;
1461 END IF;
1462 l_return_status := Validate_Record(l_def_ALSv_rec);
1463 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1464 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1465 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1466 RAISE okl_api.G_EXCEPTION_ERROR;
1467 END IF;
1468
1469 --------------------------------------
1470 -- Move VIEW record to "Child" records
1471 --------------------------------------
1472 migrate(l_def_ALSv_rec, l_ALS_rec);
1473
1474 -- Lock the Record
1475 lock_row(
1476 p_api_version => p_api_version,
1477 p_init_msg_list => p_init_msg_list,
1478 x_return_status => l_return_status,
1479 x_msg_count => x_msg_count,
1480 x_msg_data => x_msg_data,
1481 p_ALSv_rec => l_ALSv_rec);
1482 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1483 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1484 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1485 RAISE OKL_API.G_EXCEPTION_ERROR;
1486 END IF;
1487 --------------------------------------------
1488 -- Call the UPDATE_ROW for each child record
1489 --------------------------------------------
1490 update_row(
1491 p_init_msg_list,
1492 x_return_status,
1493 x_msg_count,
1494 x_msg_data,
1495 l_ALS_rec,
1496 lx_ALS_rec
1497 );
1498 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1499 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1500 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1501 RAISE okl_api.G_EXCEPTION_ERROR;
1502 END IF;
1503 migrate(lx_ALS_rec, l_def_ALSv_rec);
1504 x_ALSv_rec := l_def_ALSv_rec;
1505 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1506 EXCEPTION
1507 WHEN okl_api.G_EXCEPTION_ERROR THEN
1508 x_return_status := OKL_API.G_RET_STS_ERROR;
1509 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1510 x_return_status :=OKL_API.G_RET_STS_UNEXP_ERROR;
1511 WHEN OTHERS THEN
1512 x_return_status :=OKL_API.G_RET_STS_ERROR;
1513 END update_row;
1514 ----------------------------------------
1515 -- PL/SQL TBL update_row for:ALSV_TBL --
1516 ----------------------------------------
1517 PROCEDURE update_row(
1518 p_api_version IN NUMBER,
1519 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1520 x_return_status OUT NOCOPY VARCHAR2,
1521 x_msg_count OUT NOCOPY NUMBER,
1522 x_msg_data OUT NOCOPY VARCHAR2,
1523 p_ALSv_tbl IN ALSv_tbl_type,
1524 x_ALSv_tbl OUT NOCOPY ALSv_tbl_type) IS
1525
1526 l_api_version CONSTANT NUMBER := 1;
1527 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1528 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1529 l_overall_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1530 i NUMBER := 0;
1531 BEGIN
1532 okl_api.init_msg_list(p_init_msg_list);
1533 -- Make sure PL/SQL table has records in it before passing
1534 IF (p_ALSv_tbl.COUNT > 0) THEN
1535 i := p_ALSv_tbl.FIRST;
1536 LOOP
1537 update_row (
1538 p_api_version => p_api_version,
1539 p_init_msg_list => okl_api.G_FALSE,
1540 x_return_status => x_return_status,
1541 x_msg_count => x_msg_count,
1542 x_msg_data => x_msg_data,
1543 p_ALSv_rec => p_ALSv_tbl(i),
1544 x_ALSv_rec => x_ALSv_tbl(i));
1545 IF x_return_status <> okl_api.G_RET_STS_SUCCESS THEN
1546 IF l_overall_status <> okl_api.G_RET_STS_UNEXP_ERROR THEN
1547 l_overall_status := x_return_status;
1548 END IF;
1549 END IF;
1550 EXIT WHEN (i = p_ALSv_tbl.LAST);
1551 i := p_ALSv_tbl.NEXT(i);
1552 END LOOP;
1553 x_return_status := l_overall_status;
1554 END IF;
1555 EXCEPTION
1556 WHEN okl_api.G_EXCEPTION_ERROR THEN
1557 x_return_status := okl_api.HANDLE_EXCEPTIONS
1558 (
1559 l_api_name,
1560 G_PKG_NAME,
1561 'okl_api.G_RET_STS_ERROR',
1562 x_msg_count,
1563 x_msg_data,
1564 '_PVT'
1565 );
1566 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1567 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1568 (
1569 l_api_name,
1570 G_PKG_NAME,
1571 'okl_api.G_RET_STS_UNEXP_ERROR',
1572 x_msg_count,
1573 x_msg_data,
1574 '_PVT'
1575 );
1576 WHEN OTHERS THEN
1577 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1578 (
1579 l_api_name,
1580 G_PKG_NAME,
1581 'OTHERS',
1582 x_msg_count,
1583 x_msg_data,
1584 '_PVT'
1585 );
1586 END update_row;
1587
1588 ---------------------------------------------------------------------------
1589 -- PROCEDURE delete_row
1590 ---------------------------------------------------------------------------
1591 -------------------------------------
1592 -- delete_row for:OKL_AG_SOURCE_MAPS --
1593 -------------------------------------
1594 PROCEDURE delete_row(
1595 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1596 x_return_status OUT NOCOPY VARCHAR2,
1597 x_msg_count OUT NOCOPY NUMBER,
1598 x_msg_data OUT NOCOPY VARCHAR2,
1599 p_ALS_rec IN ALS_rec_type) IS
1600
1601 l_api_version CONSTANT NUMBER := 1;
1602 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_delete_row';
1603 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1604 l_ALS_rec ALS_rec_type:= p_ALS_rec;
1605 l_row_notfound BOOLEAN := TRUE;
1606 BEGIN
1607 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1608 p_init_msg_list,
1609 '_PVT',
1610 x_return_status);
1611 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1612 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1613 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1614 RAISE okl_api.G_EXCEPTION_ERROR;
1615 END IF;
1616 DELETE FROM OKL_AG_SOURCE_MAPS
1617 WHERE ID = l_ALS_rec.id;
1618
1619 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1620 EXCEPTION
1621 WHEN okl_api.G_EXCEPTION_ERROR THEN
1622 x_return_status := okl_api.HANDLE_EXCEPTIONS
1623 (
1624 l_api_name,
1625 G_PKG_NAME,
1626 'okl_api.G_RET_STS_ERROR',
1627 x_msg_count,
1628 x_msg_data,
1629 '_PVT'
1630 );
1631 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1632 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1633 (
1634 l_api_name,
1635 G_PKG_NAME,
1636 'okl_api.G_RET_STS_UNEXP_ERROR',
1637 x_msg_count,
1638 x_msg_data,
1639 '_PVT'
1640 );
1641 WHEN OTHERS THEN
1642 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1643 (
1644 l_api_name,
1645 G_PKG_NAME,
1646 'OTHERS',
1647 x_msg_count,
1648 x_msg_data,
1649 '_PVT'
1650 );
1651 END delete_row;
1652 ---------------------------------------
1653 -- delete_row for:OKL_AG_SOURCE_MAPS_V --
1654 ---------------------------------------
1655 PROCEDURE delete_row(
1656 p_api_version IN NUMBER,
1657 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1658 x_return_status OUT NOCOPY VARCHAR2,
1659 x_msg_count OUT NOCOPY NUMBER,
1660 x_msg_data OUT NOCOPY VARCHAR2,
1661 p_ALSv_rec IN ALSv_rec_type) IS
1662
1663 l_api_version CONSTANT NUMBER := 1;
1664 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1665 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1666 l_ALSv_rec ALSv_rec_type := p_ALSv_rec;
1667 l_ALS_rec ALS_rec_type;
1668 BEGIN
1669 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1670 G_PKG_NAME,
1671 p_init_msg_list,
1672 l_api_version,
1673 p_api_version,
1674 '_PVT',
1675 x_return_status);
1676 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1677 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1678 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1679 RAISE okl_api.G_EXCEPTION_ERROR;
1680 END IF;
1681 --------------------------------------
1682 -- Move VIEW record to "Child" records
1683 --------------------------------------
1684 migrate(l_ALSv_rec, l_ALS_rec);
1685 --------------------------------------------
1686 -- Call the DELETE_ROW for each child record
1687 --------------------------------------------
1688 delete_row(
1689 p_init_msg_list,
1690 x_return_status,
1691 x_msg_count,
1692 x_msg_data,
1693 l_ALS_rec
1694 );
1695 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1696 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1697 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1698 RAISE okl_api.G_EXCEPTION_ERROR;
1699 END IF;
1700 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1701 EXCEPTION
1702 WHEN okl_api.G_EXCEPTION_ERROR THEN
1703 x_return_status := okl_api.HANDLE_EXCEPTIONS
1704 (
1705 l_api_name,
1706 G_PKG_NAME,
1707 'okl_api.G_RET_STS_ERROR',
1708 x_msg_count,
1709 x_msg_data,
1710 '_PVT'
1711 );
1712 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1713 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1714 (
1715 l_api_name,
1716 G_PKG_NAME,
1717 'okl_api.G_RET_STS_UNEXP_ERROR',
1718 x_msg_count,
1719 x_msg_data,
1720 '_PVT'
1721 );
1722 WHEN OTHERS THEN
1723 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1724 (
1725 l_api_name,
1726 G_PKG_NAME,
1727 'OTHERS',
1728 x_msg_count,
1729 x_msg_data,
1730 '_PVT'
1731 );
1732 END delete_row;
1733 ----------------------------------------
1734 -- PL/SQL TBL delete_row for:ALSV_TBL --
1735 ----------------------------------------
1736 PROCEDURE delete_row(
1737 p_api_version IN NUMBER,
1738 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1739 x_return_status OUT NOCOPY VARCHAR2,
1740 x_msg_count OUT NOCOPY NUMBER,
1741 x_msg_data OUT NOCOPY VARCHAR2,
1742 p_ALSv_tbl IN ALSv_tbl_type) IS
1743
1744 l_api_version CONSTANT NUMBER := 1;
1745 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1746 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1747 l_overall_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1748 i NUMBER := 0;
1749 BEGIN
1750 okl_api.init_msg_list(p_init_msg_list);
1751 -- Make sure PL/SQL table has records in it before passing
1752 IF (p_ALSv_tbl.COUNT > 0) THEN
1753 i := p_ALSv_tbl.FIRST;
1754 LOOP
1755 delete_row (
1756 p_api_version => p_api_version,
1757 p_init_msg_list => okl_api.G_FALSE,
1758 x_return_status => x_return_status,
1759 x_msg_count => x_msg_count,
1760 x_msg_data => x_msg_data,
1761 p_ALSv_rec => p_ALSv_tbl(i));
1762 IF x_return_status <> okl_api.G_RET_STS_SUCCESS THEN
1763 IF l_overall_status <> okl_api.G_RET_STS_UNEXP_ERROR THEN
1764 l_overall_status := x_return_status;
1765 END IF;
1766 END IF;
1767 EXIT WHEN (i = p_ALSv_tbl.LAST);
1768 i := p_ALSv_tbl.NEXT(i);
1769 END LOOP;
1770 x_return_status := l_overall_status;
1771 END IF;
1772 EXCEPTION
1773 WHEN okl_api.G_EXCEPTION_ERROR THEN
1774 x_return_status := okl_api.HANDLE_EXCEPTIONS
1775 (
1776 l_api_name,
1777 G_PKG_NAME,
1778 'okl_api.G_RET_STS_ERROR',
1779 x_msg_count,
1780 x_msg_data,
1781 '_PVT'
1782 );
1783 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1784 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1785 (
1786 l_api_name,
1787 G_PKG_NAME,
1788 'okl_api.G_RET_STS_UNEXP_ERROR',
1789 x_msg_count,
1790 x_msg_data,
1791 '_PVT'
1792 );
1793 WHEN OTHERS THEN
1794 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1795 (
1796 l_api_name,
1797 G_PKG_NAME,
1798 'OTHERS',
1799 x_msg_count,
1800 x_msg_data,
1801 '_PVT'
1802 );
1803 END delete_row;
1804 END OKL_ALS_PVT;