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