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