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