[Home] [Help]
PACKAGE BODY: APPS.OKL_BCT_PVT
Source
1 PACKAGE BODY OKL_BCT_PVT AS
2 /* $Header: OKLSBCTB.pls 120.2 2007/05/11 22:47:38 asahoo ship $ */
3
4 -- The lock_row and the validate_row procedures are not available.
5
6 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200):='OKC_NO_PARENT_RECORD';
7 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) :='OKC_CONTRACTS_UNEXPECTED_ERROR';
8 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
9 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
10 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
11
12 PROCEDURE api_copy IS
13 BEGIN
14 null;
15 END api_copy;
16
17 PROCEDURE change_version IS
18 BEGIN
19 null;
20 END change_version;
21
22 --------------------------------------------------------------------------------
23 -- Procedure get_rec for OKL_BOOK_CONTROLLER_TRX
24 --------------------------------------------------------------------------------
25 FUNCTION get_rec(
26 p_bct_rec IN okl_bct_rec,
27 x_no_data_found OUT NOCOPY BOOLEAN
28 )RETURN okl_bct_rec IS
29 CURSOR bct_pk_csr(p_batch_number IN NUMBER,
30 p_srl_number IN NUMBER) IS
31 SELECT
32 USER_ID,
33 ORG_ID,
34 BATCH_NUMBER,
35 PROCESSING_SRL_NUMBER,
36 KHR_ID,
37 PROGRAM_NAME,
38 PROG_SHORT_NAME,
39 CONC_REQ_ID,
40 PROGRESS_STATUS,
41 CREATED_BY,
42 CREATION_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATE_LOGIN,
46 ACTIVE_FLAG
47 FROM OKL_BOOK_CONTROLLER_TRX
48 WHERE OKL_BOOK_CONTROLLER_TRX.BATCH_NUMBER = p_batch_number
49 AND OKL_BOOK_CONTROLLER_TRX.PROCESSING_SRL_NUMBER = p_srl_number;
50 l_bct_pk bct_pk_csr%ROWTYPE;
51 l_bct_rec okl_bct_rec;
52 BEGIN
53 x_no_data_found:= TRUE;
54 --Get current data base values
55 OPEN bct_pk_csr(p_bct_rec.batch_number,
56 p_bct_rec.processing_srl_number);
57 FETCH bct_pk_csr INTO
58 l_bct_rec.USER_ID,
59 l_bct_rec.ORG_ID,
60 l_bct_rec.BATCH_NUMBER,
61 l_bct_rec.PROCESSING_SRL_NUMBER,
62 l_bct_rec.KHR_ID,
63 l_bct_rec.PROGRAM_NAME,
64 l_bct_rec.PROG_SHORT_NAME,
65 l_bct_rec.CONC_REQ_ID,
66 l_bct_rec.PROGRESS_STATUS,
67 l_bct_rec.CREATED_BY,
68 l_bct_rec.CREATION_DATE,
69 l_bct_rec.LAST_UPDATED_BY,
70 l_bct_rec.LAST_UPDATE_DATE,
71 l_bct_rec.LAST_UPDATE_LOGIN,
72 l_bct_rec.ACTIVE_FLAG;
73 x_no_data_found := bct_pk_csr%NOTFOUND;
74 CLOSE bct_pk_csr;
75 RETURN (l_bct_rec);
76 END get_rec;
77
78 FUNCTION get_rec(
79 p_bct_rec IN okl_bct_rec
80 )RETURN okl_bct_rec IS
81 l_row_notfound BOOLEAN:=TRUE;
82 BEGIN
83 RETURN(get_rec(p_bct_rec,l_row_notfound));
84 END get_rec;
85
86 ---------------------------------------------------------------------------
87 -- FUNCTION null_out_defaults for: OKL_BOOK_CONTROLLER_TRX
88 ---------------------------------------------------------------------------
89 FUNCTION null_out_defaults(
90 p_bct_rec IN okl_bct_rec
91 )RETURN okl_bct_rec IS
92 l_bct_rec okl_bct_rec := p_bct_rec;
93 BEGIN
94 IF (l_bct_rec.USER_ID=OKL_API.G_MISS_NUM) THEN
95 l_bct_rec.USER_ID:=NULL;
96 END IF;
97 IF (l_bct_rec.ORG_ID=OKL_API.G_MISS_NUM) THEN
98 l_bct_rec.ORG_ID:=NULL;
99 END IF;
100 IF (l_bct_rec.BATCH_NUMBER=OKL_API.G_MISS_NUM) THEN
101 l_bct_rec.BATCH_NUMBER:=NULL;
102 END IF;
103 IF (l_bct_rec.PROCESSING_SRL_NUMBER=OKL_API.G_MISS_NUM) THEN
104 l_bct_rec.PROCESSING_SRL_NUMBER:=NULL;
105 END IF;
106 IF (l_bct_rec.KHR_ID=OKL_API.G_MISS_NUM) THEN
107 l_bct_rec.KHR_ID:=NULL;
108 END IF;
109 IF (l_bct_rec.PROGRAM_NAME=OKL_API.G_MISS_CHAR) THEN
110 l_bct_rec.PROGRAM_NAME:=NULL;
111 END IF;
112 IF (l_bct_rec.PROG_SHORT_NAME=OKL_API.G_MISS_CHAR) THEN
113 l_bct_rec.PROG_SHORT_NAME:=NULL;
114 END IF;
115 IF (l_bct_rec.CONC_REQ_ID=OKL_API.G_MISS_NUM) THEN
116 l_bct_rec.CONC_REQ_ID:=NULL;
117 END IF;
118 IF (l_bct_rec.PROGRESS_STATUS=OKL_API.G_MISS_CHAR) THEN
119 l_bct_rec.PROGRESS_STATUS:=NULL;
120 END IF;
121 IF (l_bct_rec.CREATED_BY=OKL_API.G_MISS_NUM) THEN
122 l_bct_rec.CREATED_BY:=NULL;
123 END IF;
124 IF (l_bct_rec.CREATION_DATE=OKL_API.G_MISS_DATE) THEN
125 l_bct_rec.CREATION_DATE:=NULL;
126 END IF;
127 IF (l_bct_rec.LAST_UPDATED_BY=OKL_API.G_MISS_NUM) THEN
128 l_bct_rec.LAST_UPDATED_BY:=NULL;
129 END IF;
130 IF (l_bct_rec.LAST_UPDATE_DATE=OKL_API.G_MISS_DATE) THEN
131 l_bct_rec.LAST_UPDATE_DATE:=NULL;
132 END IF;
133 IF (l_bct_rec.LAST_UPDATE_LOGIN=OKL_API.G_MISS_NUM) THEN
134 l_bct_rec.LAST_UPDATE_LOGIN:=NULL;
135 END IF;
136 IF (l_bct_rec.ACTIVE_FLAG=OKL_API.G_MISS_CHAR) THEN
137 l_bct_rec.ACTIVE_FLAG:=NULL;
138 END IF;
139 RETURN(l_bct_rec);
140 END null_out_defaults;
141
142 -----------------------------------------------------------------------------
143 -- PROCEDURE validate_user_id
144 -----------------------------------------------------------------------------
145 -- Start of comments
146 --
147 -- procedure Name : validate_user_id
148 -- Description : Procedure to validate user id
149 -- Business Rules :
150 -- Parameters : p_user_id,x_return_status
151 -- Version : 1.0
152 -- History : XX-XXX-XXXX vthiruva Created
153 -- End of comments
154
155 PROCEDURE validate_user_id(
156 p_user_id IN NUMBER,
157 x_return_status OUT NOCOPY VARCHAR2) IS
158
159 BEGIN
160 -- initialize return status
161 x_return_status := OKL_API.G_RET_STS_SUCCESS;
162
163 -- data is required
164 IF (p_user_id IS NULL) OR (p_user_id = OKL_API.G_MISS_NUM) THEN
165 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
166 p_msg_name => g_required_value,
167 p_token1 => g_col_name_token,
168 p_token1_value => 'user_id');
169
170 -- halt further validation of this column
171 RAISE G_EXCEPTION_HALT_VALIDATION;
172 END IF;
173
174 EXCEPTION
175 WHEN G_EXCEPTION_HALT_VALIDATION THEN
176 -- notify caller of an error
177 x_return_status := OKL_API.G_RET_STS_ERROR;
178
179 WHEN OTHERS THEN
180 -- store SQL error message on message stack for caller
181 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
182 p_msg_name => g_unexpected_error,
183 p_token1 => g_sqlcode_token,
184 p_token1_value => sqlcode,
185 p_token2 => g_sqlerrm_token,
186 p_token2_value => sqlerrm);
187
188 -- notify caller of an UNEXPECTED error
189 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
190
191 END validate_user_id;
192
193 -----------------------------------------------------------------------------
194 -- PROCEDURE validate_org_id
195 -----------------------------------------------------------------------------
196 -- Start of comments
197 --
198 -- procedure Name : validate_org_id
199 -- Description : Procedure to validate org id
200 -- Business Rules :
201 -- Parameters : p_org_id,x_return_status
202 -- Version : 1.0
203 -- History : XX-XXX-XXXX vthiruva Created
204 -- End of comments
205
206 PROCEDURE validate_org_id(
207 p_org_id IN NUMBER,
208 x_return_status OUT NOCOPY VARCHAR2) IS
209
210 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
211 BEGIN
212 -- initialize return status
213 x_return_status := OKL_API.G_RET_STS_SUCCESS;
214
215 -- check org id validity using the generic function in okl_util
216 l_return_status := okl_util.check_org_id (p_org_id,'N');
217
218 IF ( l_return_status = OKL_API.G_RET_STS_ERROR) THEN
219 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
220 p_msg_name => g_invalid_value,
221 p_token1 => g_col_name_token,
222 p_token1_value => 'org_id');
223
224 -- notify caller of an error
225 x_return_status := OKL_API.G_RET_STS_ERROR;
226 RAISE G_EXCEPTION_HALT_VALIDATION;
227
228 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
229 -- notify caller of an error
230 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
231 RAISE G_EXCEPTION_HALT_VALIDATION;
232
233 END IF;
234
235 EXCEPTION
236 WHEN G_EXCEPTION_HALT_VALIDATION then
237 -- No action necessary.
238 --Validation can continue to next attribute/column
239 NULL;
240
241 WHEN OTHERS THEN
242 -- store SQL error message on message stack for caller
243 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
244 p_msg_name => g_unexpected_error,
245 p_token1 => g_sqlcode_token,
246 p_token1_value => sqlcode,
247 p_token2 => g_sqlerrm_token,
248 p_token2_value => sqlerrm);
249
250 -- notify caller of an UNEXPECTED error
251 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
252
253 END validate_org_id;
254
255 -----------------------------------------------------------------------------
256 -- PROCEDURE validate_batch_number
257 -----------------------------------------------------------------------------
258 -- Start of comments
259 --
260 -- procedure Name : validate_batch_number
261 -- Description : Procedure to validate batch number
262 -- Business Rules :
263 -- Parameters : p_batch_number,x_return_status
264 -- Version : 1.0
265 -- History : XX-XXX-XXXX vthiruva Created
266 -- End of comments
267
268 PROCEDURE validate_batch_number(
269 p_batch_number IN NUMBER,
270 x_return_status OUT NOCOPY VARCHAR2) IS
271
272 BEGIN
273 -- initialize return status
274 x_return_status := OKL_API.G_RET_STS_SUCCESS;
275
276 -- data is required
277 IF (p_batch_number IS NULL) OR (p_batch_number = OKL_API.G_MISS_NUM) THEN
278 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
279 p_msg_name => g_required_value,
280 p_token1 => g_col_name_token,
281 p_token1_value => 'batch_number');
282
283 -- halt further validation of this column
284 RAISE G_EXCEPTION_HALT_VALIDATION;
285 END IF;
286
287 EXCEPTION
288 WHEN G_EXCEPTION_HALT_VALIDATION THEN
289 -- notify caller of an error
290 x_return_status := OKL_API.G_RET_STS_ERROR;
291
292 WHEN OTHERS THEN
293 -- store SQL error message on message stack for caller
294 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
295 p_msg_name => g_unexpected_error,
296 p_token1 => g_sqlcode_token,
297 p_token1_value => sqlcode,
298 p_token2 => g_sqlerrm_token,
299 p_token2_value => sqlerrm);
300
301 -- notify caller of an UNEXPECTED error
302 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
303
304 END validate_batch_number;
305
306 -----------------------------------------------------------------------------
307 -- PROCEDURE validate_processing_srl_number
308 -----------------------------------------------------------------------------
309 -- Start of comments
310 --
311 -- procedure Name : validate_processing_srl_number
312 -- Description : Procedure to validate processing serial number
313 -- Business Rules :
314 -- Parameters : p_srl_number,x_return_status
315 -- Version : 1.0
316 -- History : XX-XXX-XXXX vthiruva Created
317 -- End of comments
318
319 PROCEDURE validate_processing_srl_number(
320 p_srl_number IN NUMBER,
321 x_return_status OUT NOCOPY VARCHAR2) IS
322
323 BEGIN
324 -- initialize return status
325 x_return_status := OKL_API.G_RET_STS_SUCCESS;
326
327 -- data is required
328 IF (p_srl_number IS NULL) OR (p_srl_number = OKL_API.G_MISS_NUM) THEN
329 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
330 p_msg_name => g_required_value,
331 p_token1 => g_col_name_token,
332 p_token1_value => 'processing_srl_number');
333
334 -- halt further validation of this column
335 RAISE G_EXCEPTION_HALT_VALIDATION;
336 END IF;
337
338 EXCEPTION
339 WHEN G_EXCEPTION_HALT_VALIDATION THEN
340 -- notify caller of an error
341 x_return_status := OKL_API.G_RET_STS_ERROR;
342
343 WHEN OTHERS THEN
344 -- store SQL error message on message stack for caller
345 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
346 p_msg_name => g_unexpected_error,
347 p_token1 => g_sqlcode_token,
348 p_token1_value => sqlcode,
349 p_token2 => g_sqlerrm_token,
350 p_token2_value => sqlerrm);
351
352 -- notify caller of an UNEXPECTED error
353 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
354
355 END validate_processing_srl_number;
356
357 -----------------------------------------------------------------------------
358 -- PROCEDURE validate_khr_id
359 -----------------------------------------------------------------------------
360 -- Start of comments
361 --
362 -- procedure Name : validate_khr_id
363 -- Description : Procedure to validate contract id
364 -- Business Rules :
365 -- Parameters : p_khr_id,x_return_status
366 -- Version : 1.0
367 -- History : XX-XXX-XXXX vthiruva Created
368 -- End of comments
369
370 PROCEDURE validate_khr_id(
371 p_khr_id IN NUMBER,
372 x_return_status OUT NOCOPY VARCHAR2) IS
373
374 BEGIN
375 -- initialize return status
376 x_return_status := OKL_API.G_RET_STS_SUCCESS;
377
378 -- data is required
379 IF (p_khr_id IS NULL) OR (p_khr_id = OKL_API.G_MISS_NUM) THEN
380 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
381 p_msg_name => g_required_value,
382 p_token1 => g_col_name_token,
383 p_token1_value => 'khr_id');
384
385 -- halt further validation of this column
386 RAISE G_EXCEPTION_HALT_VALIDATION;
387 END IF;
388
389 EXCEPTION
390 WHEN G_EXCEPTION_HALT_VALIDATION THEN
391 -- notify caller of an error
392 x_return_status := OKL_API.G_RET_STS_ERROR;
393
394 WHEN OTHERS THEN
395 -- store SQL error message on message stack for caller
396 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
397 p_msg_name => g_unexpected_error,
398 p_token1 => g_sqlcode_token,
399 p_token1_value => sqlcode,
400 p_token2 => g_sqlerrm_token,
401 p_token2_value => sqlerrm);
402
403 -- notify caller of an UNEXPECTED error
404 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
405
406 END validate_khr_id;
407
408 -----------------------------------------------------------------------------
409 -- PROCEDURE validate_program_name
410 -----------------------------------------------------------------------------
411 -- Start of comments
412 --
413 -- procedure Name : validate_program_name
414 -- Description : Procedure to validate program name
415 -- Business Rules :
416 -- Parameters : p_program_name,x_return_status
417 -- Version : 1.0
418 -- History : XX-XXX-XXXX vthiruva Created
419 -- End of comments
420
421 PROCEDURE validate_program_name(
422 p_program_name IN VARCHAR2,
423 x_return_status OUT NOCOPY VARCHAR2) IS
424
425 BEGIN
426 -- initialize return status
427 x_return_status := OKL_API.G_RET_STS_SUCCESS;
428
429 -- data is required
430 IF (p_program_name IS NULL) OR (p_program_name = OKL_API.G_MISS_CHAR) THEN
431 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
432 p_msg_name => g_required_value,
433 p_token1 => g_col_name_token,
434 p_token1_value => 'program_name');
435
436 -- halt further validation of this column
437 RAISE G_EXCEPTION_HALT_VALIDATION;
438 END IF;
439
440 EXCEPTION
441 WHEN G_EXCEPTION_HALT_VALIDATION THEN
442 -- notify caller of an error
443 x_return_status := OKL_API.G_RET_STS_ERROR;
444
445 WHEN OTHERS THEN
446 -- store SQL error message on message stack for caller
447 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
448 p_msg_name => g_unexpected_error,
449 p_token1 => g_sqlcode_token,
450 p_token1_value => sqlcode,
451 p_token2 => g_sqlerrm_token,
452 p_token2_value => sqlerrm);
453
454 -- notify caller of an UNEXPECTED error
455 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
456
457 END validate_program_name;
458
459 -----------------------------------------------------------------------------
460 -- PROCEDURE validate_prog_short_name
461 -----------------------------------------------------------------------------
462 -- Start of comments
463 --
464 -- procedure Name : validate_prog_short_name
465 -- Description : Procedure to validate program short name
466 -- Business Rules :
467 -- Parameters : p_prog_short_name,x_return_status
468 -- Version : 1.0
469 -- History : XX-XXX-XXXX vthiruva Created
470 -- End of comments
471
472 PROCEDURE validate_prog_short_name(
473 p_prog_short_name IN VARCHAR2,
474 x_return_status OUT NOCOPY VARCHAR2) IS
475
476 BEGIN
477 -- initialize return status
478 x_return_status := OKL_API.G_RET_STS_SUCCESS;
479
480 -- data is required
481 IF (p_prog_short_name IS NULL) OR (p_prog_short_name = OKL_API.G_MISS_CHAR) THEN
482 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
483 p_msg_name => g_required_value,
484 p_token1 => g_col_name_token,
485 p_token1_value => 'prog_short_name');
486
487 -- halt further validation of this column
488 RAISE G_EXCEPTION_HALT_VALIDATION;
489 END IF;
490
491 EXCEPTION
492 WHEN G_EXCEPTION_HALT_VALIDATION THEN
493 -- notify caller of an error
494 x_return_status := OKL_API.G_RET_STS_ERROR;
495
496 WHEN OTHERS THEN
497 -- store SQL error message on message stack for caller
498 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
499 p_msg_name => g_unexpected_error,
500 p_token1 => g_sqlcode_token,
501 p_token1_value => sqlcode,
502 p_token2 => g_sqlerrm_token,
503 p_token2_value => sqlerrm);
504
505 -- notify caller of an UNEXPECTED error
506 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
507
508 END validate_prog_short_name;
509
510 -----------------------------------------------------------------------------
511 -- PROCEDURE validate_progress_status
512 -----------------------------------------------------------------------------
513 -- Start of comments
514 --
515 -- procedure Name : validate_progress_status
516 -- Description : Procedure to validate progress status
517 -- Business Rules :
518 -- Parameters : p_progress_status,x_return_status
519 -- Version : 1.0
520 -- History : XX-XXX-XXXX vthiruva Created
521 -- End of comments
522
523 PROCEDURE validate_progress_status(
524 p_progress_status IN VARCHAR2,
525 x_return_status OUT NOCOPY VARCHAR2) IS
526
527 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
528 BEGIN
529 -- initialize return status
530 x_return_status := OKL_API.G_RET_STS_SUCCESS;
531
532 -- If value passed
533 IF (p_progress_status IS NULL) OR
534 (p_progress_status = OKL_API.G_MISS_CHAR) THEN
535 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
536 p_msg_name => g_required_value,
537 p_token1 => g_col_name_token,
538 p_token1_value => 'progress_status');
539
540 -- halt further validation of this column
541 RAISE G_EXCEPTION_HALT_VALIDATION;
542
543 ELSE
544 -- check the status code is a valid value
545 l_return_status := okl_util.check_lookup_code(
546 p_lookup_type => 'OKL_BKG_CONTL_STATUS',
547 p_lookup_code => p_progress_status);
548
549 IF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
550 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
551 p_msg_name => g_invalid_value,
552 p_token1 => g_col_name_token,
553 p_token1_value => 'progress_status');
554
555 -- notify caller of an error
556 x_return_status := OKL_API.G_RET_STS_ERROR;
557 RAISE G_EXCEPTION_HALT_VALIDATION;
558
559 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
560 -- notify caller of an error
561 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
562 RAISE G_EXCEPTION_HALT_VALIDATION;
563 END IF;
564
565 END IF;
566
567 EXCEPTION
568 WHEN G_EXCEPTION_HALT_VALIDATION then
569 -- No action necessary.
570 -- Validation can continue to next attribute/column
571 NULL;
572
573 WHEN OTHERS THEN
574 -- store SQL error message on message stack for caller
575 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
576 p_msg_name => g_unexpected_error,
577 p_token1 => g_sqlcode_token,
578 p_token1_value => sqlcode,
579 p_token2 => g_sqlerrm_token,
580 p_token2_value => sqlerrm);
581
582 -- notify caller of an UNEXPECTED error
583 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
584 END validate_progress_status;
585
586 -----------------------------------------------------------------
587 -- Validate_Attributes for:OKL_BOOK_CONTROLLER_TRX --
588 -----------------------------------------------------------------
589 FUNCTION Validate_Attributes (
590 p_bct_rec IN okl_bct_rec
591 )RETURN VARCHAR2 IS
592 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
593 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
594 BEGIN
595 -- call each column-level validation
596 validate_user_id(p_user_id => p_bct_rec.user_id,
597 x_return_status => l_return_status);
598
599 -- store the highest degree of error
600 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
601 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
602 x_return_status := l_return_status;
603 END IF;
604 END IF;
605
606 validate_org_id(p_org_id => p_bct_rec.org_id,
607 x_return_status => l_return_status);
608
609 -- store the highest degree of error
610 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
611 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
612 x_return_status := l_return_status;
613 END IF;
614 END IF;
615
616 validate_batch_number(p_batch_number => p_bct_rec.batch_number,
617 x_return_status => l_return_status);
618
619 -- store the highest degree of error
620 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
621 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
622 x_return_status := l_return_status;
623 END IF;
624 END IF;
625
626 validate_processing_srl_number(
627 p_srl_number => p_bct_rec.processing_srl_number,
628 x_return_status => l_return_status);
629
630 -- store the highest degree of error
631 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
632 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
633 x_return_status := l_return_status;
634 END IF;
635 END IF;
636
637 validate_khr_id(p_khr_id => p_bct_rec.khr_id,
638 x_return_status => l_return_status);
639
640 -- store the highest degree of error
641 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
642 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
643 x_return_status := l_return_status;
644 END IF;
645 END IF;
646
647 validate_program_name(p_program_name => p_bct_rec.program_name,
648 x_return_status => l_return_status);
649
650 -- store the highest degree of error
651 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
652 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
653 x_return_status := l_return_status;
654 END IF;
655 END IF;
656
657 validate_prog_short_name(p_prog_short_name => p_bct_rec.prog_short_name,
658 x_return_status => l_return_status);
659
660 -- store the highest degree of error
661 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
662 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
663 x_return_status := l_return_status;
664 END IF;
665 END IF;
666
667 validate_progress_status(p_progress_status => p_bct_rec.progress_status,
668 x_return_status => l_return_status);
669
670 -- store the highest degree of error
671 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
672 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
673 x_return_status := l_return_status;
674 END IF;
675 END IF;
676
677 RETURN (x_return_status);
678 END Validate_Attributes;
679 ------------------------------------------------------
680 -- Validate Record for:OKL_BOOK_CONTROLLER_TRX --
681 ------------------------------------------------------
682 FUNCTION Validate_Record (
683 p_bct_rec IN okl_bct_rec
684 )RETURN VARCHAR2 IS
685 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
686 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
687 BEGIN
688 RETURN (x_return_status);
689 END Validate_Record;
690 --------------------------------------------------------------------------------
691 -- Procedure insert_row for:OKL_BOOK_CONTROLLER_TRX --
692 --------------------------------------------------------------------------------
693 PROCEDURE insert_row(
694 p_api_version IN NUMBER ,
695 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2,
699 p_bct_rec IN okl_bct_rec,
700 x_bct_rec OUT NOCOPY okl_bct_rec)IS
701
702 l_api_version CONSTANT NUMBER:=1;
703 l_api_name CONSTANT VARCHAR2(30):='insert_row';
704 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
705 l_bct_rec okl_bct_rec;
706 l_def_bct_rec okl_bct_rec;
707
708 FUNCTION fill_who_columns(
709 p_bct_rec IN okl_bct_rec
710 )RETURN okl_bct_rec IS
711 l_bct_rec okl_bct_rec:=p_bct_rec;
712 BEGIN
713 l_bct_rec.CREATION_DATE := SYSDATE;
714 l_bct_rec.CREATED_BY := FND_GLOBAL.USER_ID;
715 l_bct_rec.LAST_UPDATE_DATE := SYSDATE;
716 l_bct_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
717 l_bct_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
718 RETURN (l_bct_rec);
719 END fill_who_columns;
720
721 FUNCTION Set_Attributes(
722 p_bct_rec IN okl_bct_rec,
723 x_bct_rec OUT NOCOPY okl_bct_rec
724 )RETURN VARCHAR2 IS
725 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
726 BEGIN
727 x_bct_rec := p_bct_rec;
728 RETURN (l_return_status);
729 END Set_Attributes;
730 --procedure begins here
731 BEGIN
732 l_return_status := OKL_API.START_ACTIVITY(
733 l_api_name,
734 G_PKG_NAME,
735 p_init_msg_list,
736 l_api_version,
737 p_api_version,
738 '_PVT',
739 x_return_status);
740 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
741 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
742 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
743 RAISE OKL_API.G_EXCEPTION_ERROR;
744 END IF;
745
746 l_bct_rec:=null_out_defaults(p_bct_rec);
747
748 --Setting Item Attributes
749 l_return_status:=Set_Attributes(l_bct_rec,l_def_bct_rec);
750 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
751 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
752 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
753 RAISE OKL_API.G_EXCEPTION_ERROR;
754 END IF;
755
756 l_def_bct_rec := fill_who_columns(l_def_bct_rec);
757
758 l_return_status := Validate_Attributes(l_def_bct_rec);
759 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
760 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
761 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
762 RAISE OKL_API.G_EXCEPTION_ERROR;
763 END IF;
764
765 l_return_status := Validate_Record(l_def_bct_rec);
766 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
767 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
768 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
769 RAISE OKL_API.G_EXCEPTION_ERROR;
770 END IF;
771
772 INSERT INTO OKL_BOOK_CONTROLLER_TRX(
773 USER_ID,
774 ORG_ID,
775 BATCH_NUMBER,
776 PROCESSING_SRL_NUMBER,
777 KHR_ID,
778 PROGRAM_NAME,
779 PROG_SHORT_NAME,
780 CONC_REQ_ID,
781 PROGRESS_STATUS,
782 CREATED_BY,
783 CREATION_DATE,
784 LAST_UPDATED_BY,
785 LAST_UPDATE_DATE,
786 LAST_UPDATE_LOGIN,
787 ACTIVE_FLAG)
788 VALUES(
789 l_def_bct_rec.USER_ID,
790 l_def_bct_rec.ORG_ID,
791 l_def_bct_rec.BATCH_NUMBER,
792 l_def_bct_rec.PROCESSING_SRL_NUMBER,
793 l_def_bct_rec.KHR_ID,
794 l_def_bct_rec.PROGRAM_NAME,
795 l_def_bct_rec.PROG_SHORT_NAME,
796 l_def_bct_rec.CONC_REQ_ID,
797 l_def_bct_rec.PROGRESS_STATUS,
798 l_def_bct_rec.CREATED_BY,
799 l_def_bct_rec.CREATION_DATE,
800 l_def_bct_rec.LAST_UPDATED_BY,
801 l_def_bct_rec.LAST_UPDATE_DATE,
802 l_def_bct_rec.LAST_UPDATE_LOGIN,
803 l_def_bct_rec.ACTIVE_FLAG);
804
805 --Set OUT Values
806 x_bct_rec:= l_def_bct_rec;
807 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
808
809 EXCEPTION
810 WHEN G_EXCEPTION_HALT_VALIDATION then
811 -- No action necessary. Validation can continue to next attribute/column
812 null;
813
814 WHEN OKL_API.G_EXCEPTION_ERROR THEN
815 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
816 l_api_name,
817 G_PKG_NAME,
818 'OKL_API.G_RET_STS_ERROR',
819 x_msg_count,
820 x_msg_data,
821 '_PVT');
822
823 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
824 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
825 l_api_name,
826 G_PKG_NAME,
827 'OKL_API.G_RET_STS_UNEXP_ERROR',
828 x_msg_count,
829 x_msg_data,
830 '_PVT');
831
832 WHEN OTHERS THEN
833 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
834 l_api_name,
835 G_PKG_NAME,
836 'OTHERS',
837 x_msg_count,
838 x_msg_data,
839 '_PVT');
840 END insert_row;
841 --------------------------------------------------------------------------------
842 -- Procedure insert_row with PL/SQL table for:OKL_BOOK_CONTROLLER_TRX --
843 --------------------------------------------------------------------------------
844 PROCEDURE insert_row(
845 p_api_version IN NUMBER ,
846 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
847 x_return_status OUT NOCOPY VARCHAR2,
848 x_msg_count OUT NOCOPY NUMBER,
849 x_msg_data OUT NOCOPY VARCHAR2,
850 p_bct_tbl IN okl_bct_tbl,
851 x_bct_tbl OUT NOCOPY okl_bct_tbl)IS
852
853 l_api_version CONSTANT NUMBER:=1;
854 l_api_name CONSTANT VARCHAR2(30):='insert_row_tbl';
855 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
856 i NUMBER:=0;
857 l_overall_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
858 BEGIN
859 OKL_API.init_msg_list(p_init_msg_list);
860 -- Make sure PL/SQL table has records in it before passing
861 IF (p_bct_tbl.COUNT > 0) THEN
862 i := p_bct_tbl.FIRST;
863 LOOP
864 insert_row(p_api_version => p_api_version,
865 p_init_msg_list => OKL_API.G_FALSE,
866 x_return_status => x_return_status,
867 x_msg_count => x_msg_count,
868 x_msg_data => x_msg_data,
869 p_bct_rec => p_bct_tbl(i),
870 x_bct_rec => x_bct_tbl(i));
871
872 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
873 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
874 l_overall_status := x_return_status;
875 END IF;
876 END IF;
877
878 EXIT WHEN (i = p_bct_tbl.LAST);
879 i := p_bct_tbl.NEXT(i);
880 END LOOP;
881 x_return_status := l_overall_status;
882 END IF;
883
884 EXCEPTION
885 WHEN G_EXCEPTION_HALT_VALIDATION then
886 -- No action necessary. Validation can continue to next attribute/column
887 null;
888
889 WHEN OKL_API.G_EXCEPTION_ERROR THEN
890 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
891 l_api_name,
892 G_PKG_NAME,
893 'OKL_API.G_RET_STS_ERROR',
894 x_msg_count,
895 x_msg_data,
896 '_PVT');
897
898 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
899 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
900 l_api_name,
901 G_PKG_NAME,
902 'OKL_API.G_RET_STS_UNEXP_ERROR',
903 x_msg_count,
904 x_msg_data,
905 '_PVT');
906
907 WHEN OTHERS THEN
908 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
909 l_api_name,
910 G_PKG_NAME,
911 'OTHERS',
912 x_msg_count,
913 x_msg_data,
914 '_PVT');
915 END insert_row;
916 --------------------------------------------------------------------------------
917 -- Procedure update_row for:OKL_BOOK_CONTROLLER_TRX --
918 --------------------------------------------------------------------------------
919 PROCEDURE update_row(
920 p_api_version IN NUMBER ,
921 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
922 x_return_status OUT NOCOPY VARCHAR2,
923 x_msg_count OUT NOCOPY NUMBER,
924 x_msg_data OUT NOCOPY VARCHAR2,
925 p_bct_rec IN okl_bct_rec,
926 x_bct_rec OUT NOCOPY okl_bct_rec)IS
927
928 l_api_version CONSTANT NUMBER:=1;
929 l_api_name CONSTANT VARCHAR2(30):='update_row';
930 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
931 l_bct_rec okl_bct_rec:=p_bct_rec;
932 l_def_bct_rec okl_bct_rec;
933
934 FUNCTION fill_who_columns(
935 p_bct_rec IN okl_bct_rec
936 )RETURN okl_bct_rec IS
937 l_bct_rec okl_bct_rec:=p_bct_rec;
938 BEGIN
939 l_bct_rec.LAST_UPDATE_DATE := SYSDATE;
940 l_bct_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
941 l_bct_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
942 RETURN (l_bct_rec );
943 END fill_who_columns;
944
945 FUNCTION populate_new_record(
946 p_bct_rec IN okl_bct_rec,
947 x_bct_rec OUT NOCOPY okl_bct_rec
948 )RETURN VARCHAR2 is
949 l_bct_rec okl_bct_rec;
950 l_row_notfound BOOLEAN:=TRUE;
951 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
952 BEGIN
953
954 x_bct_rec := p_bct_rec;
955 --Get current database values
956 l_bct_rec := get_rec(p_bct_rec,l_row_notfound);
957 IF(l_row_notfound) THEN
958 l_return_status:= OKL_API.G_RET_STS_UNEXP_ERROR;
959 END IF;
960
961 IF (x_bct_rec.USER_ID = OKL_API.G_MISS_NUM) THEN
962 x_bct_rec.USER_ID:=l_bct_rec.USER_ID;
963 END IF;
964 IF (x_bct_rec.ORG_ID = OKL_API.G_MISS_NUM) THEN
965 x_bct_rec.ORG_ID:=l_bct_rec.ORG_ID;
966 END IF;
967 IF (x_bct_rec.BATCH_NUMBER = OKL_API.G_MISS_NUM) THEN
968 x_bct_rec.BATCH_NUMBER:=l_bct_rec.BATCH_NUMBER;
969 END IF;
970 IF (x_bct_rec.PROCESSING_SRL_NUMBER = OKL_API.G_MISS_NUM) THEN
971 x_bct_rec.PROCESSING_SRL_NUMBER:=l_bct_rec.PROCESSING_SRL_NUMBER;
972 END IF;
973 IF (x_bct_rec.KHR_ID = OKL_API.G_MISS_NUM) THEN
974 x_bct_rec.KHR_ID:=l_bct_rec.KHR_ID;
975 END IF;
976 IF (x_bct_rec.PROGRAM_NAME = OKL_API.G_MISS_CHAR) THEN
977 x_bct_rec.PROGRAM_NAME:=l_bct_rec.PROGRAM_NAME;
978 END IF;
979 IF (x_bct_rec.PROG_SHORT_NAME = OKL_API.G_MISS_CHAR) THEN
980 x_bct_rec.PROG_SHORT_NAME:=l_bct_rec.PROG_SHORT_NAME;
981 END IF;
982 IF (x_bct_rec.CONC_REQ_ID = OKL_API.G_MISS_NUM) THEN
983 x_bct_rec.CONC_REQ_ID:=l_bct_rec.CONC_REQ_ID;
984 END IF;
985 IF (x_bct_rec.PROGRESS_STATUS = OKL_API.G_MISS_CHAR) THEN
986 x_bct_rec.PROGRESS_STATUS:=l_bct_rec.PROGRESS_STATUS;
987 END IF;
988 IF (x_bct_rec.CREATED_BY = OKL_API.G_MISS_NUM) THEN
989 x_bct_rec.CREATED_BY:=l_bct_rec.CREATED_BY;
990 END IF;
991 IF (x_bct_rec.CREATION_DATE = OKL_API.G_MISS_DATE) THEN
992 x_bct_rec.CREATION_DATE:=l_bct_rec.CREATION_DATE;
993 END IF;
994 IF (x_bct_rec.LAST_UPDATED_BY = OKL_API.G_MISS_NUM) THEN
995 x_bct_rec.LAST_UPDATED_BY:=l_bct_rec.LAST_UPDATED_BY;
996 END IF;
997 IF (x_bct_rec.LAST_UPDATE_DATE = OKL_API.G_MISS_DATE) THEN
998 x_bct_rec.LAST_UPDATE_DATE:=l_bct_rec.LAST_UPDATE_DATE;
999 END IF;
1000 IF (x_bct_rec.LAST_UPDATE_LOGIN = OKL_API.G_MISS_NUM) THEN
1001 x_bct_rec.LAST_UPDATE_LOGIN:=l_bct_rec.LAST_UPDATE_LOGIN;
1002 END IF;
1003 IF (x_bct_rec.ACTIVE_FLAG = OKL_API.G_MISS_CHAR) THEN
1004 x_bct_rec.ACTIVE_FLAG:=l_bct_rec.ACTIVE_FLAG;
1005 END IF;
1006 RETURN(l_return_status);
1007 END populate_new_record;
1008
1009 FUNCTION Set_Attributes(
1010 p_bct_rec IN okl_bct_rec,
1011 x_bct_rec OUT NOCOPY okl_bct_rec
1012 )RETURN VARCHAR2 IS
1013 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1014 BEGIN
1015 x_bct_rec := p_bct_rec;
1016 RETURN (l_return_status);
1017 END Set_Attributes;
1018 --procedure begins here
1019 BEGIN
1020 l_return_status := OKL_API.START_ACTIVITY(
1021 l_api_name,
1022 G_PKG_NAME,
1023 p_init_msg_list,
1024 l_api_version,
1025 p_api_version,
1026 '_PVT',
1027 x_return_status);
1028 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1029 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1030 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1031 RAISE OKL_API.G_EXCEPTION_ERROR;
1032 END IF;
1033
1034 --Setting Item Attributes
1035 l_return_status:=Set_Attributes(p_bct_rec,l_bct_rec);
1036 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1037 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1038 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1039 RAISE OKL_API.G_EXCEPTION_ERROR;
1040 END IF;
1041
1042 l_return_status := populate_new_record(l_bct_rec,l_def_bct_rec);
1043 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1044 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1045 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1046 RAISE OKL_API.G_EXCEPTION_ERROR;
1047 END IF;
1048
1049 l_def_bct_rec := fill_who_columns(l_def_bct_rec);
1050
1051 l_return_status := Validate_Attributes(l_def_bct_rec);
1052 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1053 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1054 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1055 RAISE OKL_API.G_EXCEPTION_ERROR;
1056 END IF;
1057
1058 l_return_status := Validate_Record(l_def_bct_rec);
1059 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1060 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1061 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1062 RAISE OKL_API.G_EXCEPTION_ERROR;
1063 END IF;
1064
1065
1066 UPDATE OKL_BOOK_CONTROLLER_TRX
1067 SET
1068 USER_ID= l_def_bct_rec.USER_ID,
1069 ORG_ID= l_def_bct_rec.ORG_ID,
1070 BATCH_NUMBER= l_def_bct_rec.BATCH_NUMBER,
1071 PROCESSING_SRL_NUMBER= l_def_bct_rec.PROCESSING_SRL_NUMBER,
1072 KHR_ID= l_def_bct_rec.KHR_ID,
1073 PROGRAM_NAME= l_def_bct_rec.PROGRAM_NAME,
1074 PROG_SHORT_NAME= l_def_bct_rec.PROG_SHORT_NAME,
1075 CONC_REQ_ID= l_def_bct_rec.CONC_REQ_ID,
1076 PROGRESS_STATUS= l_def_bct_rec.PROGRESS_STATUS,
1077 CREATED_BY= l_def_bct_rec.CREATED_BY,
1078 CREATION_DATE= l_def_bct_rec.CREATION_DATE,
1079 LAST_UPDATED_BY= l_def_bct_rec.LAST_UPDATED_BY,
1080 LAST_UPDATE_DATE= l_def_bct_rec.LAST_UPDATE_DATE,
1081 LAST_UPDATE_LOGIN= l_def_bct_rec.LAST_UPDATE_LOGIN,
1082 ACTIVE_FLAG= l_def_bct_rec.ACTIVE_FLAG
1083 WHERE BATCH_NUMBER = l_def_bct_rec.BATCH_NUMBER
1084 AND PROCESSING_SRL_NUMBER = l_def_bct_rec.PROCESSING_SRL_NUMBER;
1085
1086 --Set OUT Values
1087 x_bct_rec:= l_def_bct_rec;
1088 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1089
1090 EXCEPTION
1091 WHEN G_EXCEPTION_HALT_VALIDATION then
1092 -- No action necessary. Validation can continue to next attribute/column
1093 null;
1094
1095 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1096 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1097 l_api_name,
1098 G_PKG_NAME,
1099 'OKL_API.G_RET_STS_ERROR',
1100 x_msg_count,
1101 x_msg_data,
1102 '_PVT');
1103
1104 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1105 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1106 l_api_name,
1107 G_PKG_NAME,
1108 'OKL_API.G_RET_STS_UNEXP_ERROR',
1109 x_msg_count,
1110 x_msg_data,
1111 '_PVT');
1112
1113 WHEN OTHERS THEN
1114 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1115 l_api_name,
1116 G_PKG_NAME,
1117 'OTHERS',
1118 x_msg_count,
1119 x_msg_data,
1120 '_PVT');
1121 END update_row;
1122 --------------------------------------------------------------------------------
1123 -- Procedure update_row with PL/SQL table for:OKL_BOOK_CONTROLLER_TRX --
1124 --------------------------------------------------------------------------------
1125 PROCEDURE update_row(
1126 p_api_version IN NUMBER ,
1127 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1128 x_return_status OUT NOCOPY VARCHAR2,
1129 x_msg_count OUT NOCOPY NUMBER,
1130 x_msg_data OUT NOCOPY VARCHAR2,
1131 p_bct_tbl IN okl_bct_tbl,
1132 x_bct_tbl OUT NOCOPY okl_bct_tbl)IS
1133
1134 l_api_version CONSTANT NUMBER:=1;
1135 l_api_name CONSTANT VARCHAR2(30):='update_row_tbl';
1136 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1137 i NUMBER:=0;
1138 l_overall_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1139 BEGIN
1140 OKL_API.init_msg_list(p_init_msg_list);
1141 -- Make sure PL/SQL table has records in it before passing
1142 IF (p_bct_tbl.COUNT > 0) THEN
1143 i := p_bct_tbl.FIRST;
1144 LOOP
1145 update_row (p_api_version => p_api_version,
1146 p_init_msg_list => OKL_API.G_FALSE,
1147 x_return_status => x_return_status,
1148 x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data,
1150 p_bct_rec => p_bct_tbl(i),
1151 x_bct_rec => x_bct_tbl(i));
1152 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1153 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1154 l_overall_status := x_return_status;
1155 END IF;
1156 END IF;
1157
1158 EXIT WHEN (i = p_bct_tbl.LAST);
1159 i := p_bct_tbl.NEXT(i);
1160 END LOOP;
1161 x_return_status := l_overall_status;
1162 END IF;
1163
1164 EXCEPTION
1165 WHEN G_EXCEPTION_HALT_VALIDATION then
1166 -- No action necessary. Validation can continue to next attribute/column
1167 null;
1168
1169 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1170 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1171 l_api_name,
1172 G_PKG_NAME,
1173 'OKL_API.G_RET_STS_ERROR',
1174 x_msg_count,
1175 x_msg_data,
1176 '_PVT');
1177
1178 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1179 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1180 l_api_name,
1181 G_PKG_NAME,
1182 'OKL_API.G_RET_STS_UNEXP_ERROR',
1183 x_msg_count,
1184 x_msg_data,
1185 '_PVT');
1186
1187 WHEN OTHERS THEN
1188 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1189 l_api_name,
1190 G_PKG_NAME,
1191 'OTHERS',
1192 x_msg_count,
1193 x_msg_data,
1194 '_PVT');
1195 END update_row;
1196 --------------------------------------------------------------------------------
1197 -- Procedure delete_row for:OKL_BOOK_CONTROLLER_TRX --
1198 --------------------------------------------------------------------------------
1199 PROCEDURE delete_row(
1200 p_api_version IN NUMBER ,
1201 p_init_msg_list IN VARCHAR2 DEFAULT OKL_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_bct_rec IN okl_bct_rec)IS
1206
1207 l_api_version CONSTANT NUMBER:=1;
1208 l_api_name CONSTANT VARCHAR2(30):='delete_row';
1209 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1210 l_bct_rec okl_bct_rec := p_bct_rec;
1211 l_row_notfound BOOLEAN:=TRUE;
1212
1213 BEGIN
1214 l_return_status := OKL_API.START_ACTIVITY(
1215 l_api_name,
1216 G_PKG_NAME,
1217 p_init_msg_list,
1218 l_api_version,
1219 p_api_version,
1220 '_PVT',
1221 x_return_status);
1222 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1223 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1224 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1225 RAISE OKL_API.G_EXCEPTION_ERROR;
1226 END IF;
1227
1228 DELETE FROM OKL_BOOK_CONTROLLER_TRX
1229 WHERE BATCH_NUMBER=l_bct_rec.batch_number
1230 AND PROCESSING_SRL_NUMBER = l_bct_rec.processing_srl_number;
1231
1232 EXCEPTION
1233 WHEN G_EXCEPTION_HALT_VALIDATION then
1234 -- No action necessary. Validation can continue to next attribute/column
1235 null;
1236
1237 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1238 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1239 l_api_name,
1240 G_PKG_NAME,
1241 'OKL_API.G_RET_STS_ERROR',
1242 x_msg_count,
1243 x_msg_data,
1244 '_PVT');
1245
1246 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1247 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1248 l_api_name,
1249 G_PKG_NAME,
1250 'OKL_API.G_RET_STS_UNEXP_ERROR',
1251 x_msg_count,
1252 x_msg_data,
1253 '_PVT');
1254
1255 WHEN OTHERS THEN
1256 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1257 l_api_name,
1258 G_PKG_NAME,
1259 'OTHERS',
1260 x_msg_count,
1261 x_msg_data,
1262 '_PVT');
1263 END delete_row;
1264 --------------------------------------------------------------------------------
1265 -- Procedure delete_row with PL/SQL table for:OKL_BOOK_CONTROLLER_TRX --
1266 --------------------------------------------------------------------------------
1267 PROCEDURE delete_row(
1268 p_api_version IN NUMBER ,
1269 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1270 x_return_status OUT NOCOPY VARCHAR2,
1271 x_msg_count OUT NOCOPY NUMBER,
1272 x_msg_data OUT NOCOPY VARCHAR2,
1273 p_bct_tbl IN okl_bct_tbl)IS
1274
1275 l_api_version CONSTANT NUMBER:=1;
1276 l_api_name CONSTANT VARCHAR2(30):='delete_row_tbl';
1277 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1278 i NUMBER:=0;
1279 l_overall_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1280 BEGIN
1281 OKL_API.init_msg_list(p_init_msg_list);
1282 -- Make sure PL/SQL table has records in it before passing
1283 IF (p_bct_tbl.COUNT > 0) THEN
1284 i := p_bct_tbl.FIRST;
1285 LOOP
1286 delete_row(p_api_version => p_api_version,
1287 p_init_msg_list => OKL_API.G_FALSE,
1288 x_return_status => x_return_status,
1289 x_msg_count => x_msg_count,
1290 x_msg_data => x_msg_data,
1291 p_bct_rec => p_bct_tbl(i));
1292 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1293 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1294 l_overall_status := x_return_status;
1295 END IF;
1296 END IF;
1297
1298 EXIT WHEN (i = p_bct_tbl.LAST);
1299 i := p_bct_tbl.NEXT(i);
1300 END LOOP;
1301 x_return_status := l_overall_status;
1302 END IF;
1303
1304 EXCEPTION
1305 WHEN G_EXCEPTION_HALT_VALIDATION then
1306 -- No action necessary. Validation can continue to next attribute/column
1307 null;
1308
1309 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1310 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1311 l_api_name,
1312 G_PKG_NAME,
1313 'OKL_API.G_RET_STS_ERROR',
1314 x_msg_count,
1315 x_msg_data,
1316 '_PVT');
1317
1318 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1319 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1320 l_api_name,
1321 G_PKG_NAME,
1322 'OKL_API.G_RET_STS_UNEXP_ERROR',
1323 x_msg_count,
1324 x_msg_data,
1325 '_PVT');
1326
1327 WHEN OTHERS THEN
1328 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1329 l_api_name,
1330 G_PKG_NAME,
1331 'OTHERS',
1332 x_msg_count,
1333 x_msg_data,
1334 '_PVT');
1335 END delete_row;
1336
1337 END OKL_BCT_PVT;