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