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