[Home] [Help]
PACKAGE BODY: APPS.OKE_CHR_PVT
Source
1 PACKAGE BODY OKE_CHR_PVT AS
2 /*$Header: OKEVCHRB.pls 120.1 2008/05/27 08:10:40 serukull ship $ */
3
4 FUNCTION Validate_Attributes(p_chr_rec IN chr_rec_type)
5 RETURN VARCHAR2;
6
7 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := OKE_API.G_CHILD_TABLE_TOKEN;
8 G_CHILD_RECORD_EXISTS CONSTANT VARCHAR2(200) := 'OKE_CANNOT_DELETE_MASTER';
9 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_CHILD_TABLE_TOKEN;
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKE__CONTRACTS_UNEXPECTED_ERROR';
11
12 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
13 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
14 G_VIEW CONSTANT VARCHAR2(200) := 'OKE_K_HEADERS_V';
15 G_EXCEPTION_HALT_VALIDATION exception;
16 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
17
18
19
20
21
22
23
24
25
26
27
28 /* Bug 7115155 Start */
29 FUNCTION IS_BOA (P_TYPE_CODE IN OKE_K_TYPES_B.K_TYPE_CODE%type)
30 RETURN BOOLEAN
31 IS
32 l_type_class_code OKE_K_TYPES_B.TYPE_CLASS_CODE%TYPE;
33 BEGIN
34
35 SELECT TYPE_CLASS_CODE
36 INTO l_type_class_code
37 FROM OKE_K_TYPES_B
38 WHERE K_TYPE_CODE=P_TYPE_CODE;
39
40 IF l_type_class_code='BOA' THEN
41 RETURN TRUE;
42 ELSE
43 RETURN FALSE;
44 END IF;
45
46 END IS_BOA;
47 /* Bug 7115155 End*/
48
49
50
51 PROCEDURE validate_program_id (x_return_status OUT NOCOPY VARCHAR2,
52 p_chr_rec IN chr_rec_type)IS
53 l_dummy_val VARCHAR2(1):= '?';
54 CURSOR l_csr IS
55 SELECT 'x'
56 FROM OKE_PROGRAMS
57 WHERE PROGRAM_ID = p_chr_rec.PROGRAM_ID
58 AND SYSDATE BETWEEN START_DATE
59 AND NVL(END_DATE+1 , SYSDATE )
60 ;
61
62 BEGIN
63
64 x_return_status := OKE_API.G_RET_STS_SUCCESS;
65
66 IF ( p_chr_rec.program_id <> OKE_API.G_MISS_NUM
67 AND p_chr_rec.program_id IS NOT NULL) THEN
68
69 OPEN l_csr;
70 FETCH l_csr INTO l_dummy_val;
71 CLOSE l_csr;
72
73 IF (l_dummy_val = '?') THEN
74 OKE_API.SET_MESSAGE(
75 p_app_name =>g_app_name,
76 p_msg_name =>g_no_parent_record,
77 p_token1 =>g_col_name_token,
78 p_token1_value =>'PROGRAM_ID',
79 p_token2 =>g_child_table_token,
80 p_token2_value =>G_VIEW,
81 p_token3 =>g_parent_table_token,
82 p_token3_value =>'OKE_PROGRAMS');
83
84 x_return_status := OKE_API.G_RET_STS_ERROR;
85 END IF;
86 END IF;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 -- store SQL error message on message stack
91 OKE_API.SET_MESSAGE(
92 p_app_name =>g_app_name,
93 p_msg_name =>G_UNEXPECTED_ERROR,
94 p_token1 =>G_SQLCODE_TOKEN,
95 p_token1_value =>SQLCODE,
96 p_token2 =>G_SQLERRM_TOKEN,
97 p_token2_value =>SQLERRM);
98 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
99
100 IF l_csr%ISOPEN THEN
101 CLOSE l_csr;
102 END IF;
103
104 END validate_program_id;
105
106
107
108 PROCEDURE validate_boa_id (x_return_status OUT NOCOPY VARCHAR2,
109 p_chr_rec IN chr_rec_type)IS
110 l_dummy_val VARCHAR2(1):= '?';
111 CURSOR l_csr IS
112 SELECT 'x'
113 FROM OKE_K_HEADERS
114 WHERE K_HEADER_ID = p_chr_rec.BOA_ID;
115
116 BEGIN
117
118 x_return_status := OKE_API.G_RET_STS_SUCCESS;
119
120 IF ( p_chr_rec.boa_id <> OKE_API.G_MISS_NUM
121 AND p_chr_rec.boa_id IS NOT NULL) THEN
122
123 OPEN l_csr;
124 FETCH l_csr INTO l_dummy_val;
125 CLOSE l_csr;
126
127 IF (l_dummy_val = '?') THEN
128 OKE_API.SET_MESSAGE(
129 p_app_name =>g_app_name,
130 p_msg_name =>g_no_parent_record,
131 p_token1 =>g_col_name_token,
132 p_token1_value =>'BOA_ID',
133 p_token2 =>g_child_table_token,
134 p_token2_value =>G_VIEW,
135 p_token3 =>g_parent_table_token,
136 p_token3_value =>G_VIEW);
137
138 x_return_status := OKE_API.G_RET_STS_ERROR;
139 END IF;
140 END IF;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 -- store SQL error message on message stack
145 OKE_API.SET_MESSAGE(
146 p_app_name =>g_app_name,
147 p_msg_name =>G_UNEXPECTED_ERROR,
148 p_token1 =>G_SQLCODE_TOKEN,
149 p_token1_value =>SQLCODE,
150 p_token2 =>G_SQLERRM_TOKEN,
151 p_token2_value =>SQLERRM);
152 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
153
154 IF l_csr%ISOPEN THEN
155 CLOSE l_csr;
156 END IF;
157
158 END validate_boa_id;
159
160 PROCEDURE validate_project_id (x_return_status OUT NOCOPY VARCHAR2,
161 p_chr_rec IN chr_rec_type)IS
162 l_dummy_val VARCHAR2(1):= '?';
163 CURSOR l_csr IS
164 SELECT 'x'
165 FROM PA_PROJECTS_ALL
166 WHERE PROJECT_ID = p_chr_rec.PROJECT_ID;
167
168 BEGIN
169
170 x_return_status := OKE_API.G_RET_STS_SUCCESS;
171
172 IF ( p_chr_rec.project_id <> OKE_API.G_MISS_NUM
173 AND p_chr_rec.project_id IS NOT NULL) THEN
174
175 OPEN l_csr;
176 FETCH l_csr INTO l_dummy_val;
177 CLOSE l_csr;
178
179 IF (l_dummy_val = '?') THEN
180 OKE_API.SET_MESSAGE(
181 p_app_name =>g_app_name,
182 p_msg_name =>g_no_parent_record,
183 p_token1 =>g_col_name_token,
184 p_token1_value =>'PROJECT_ID',
185 p_token2 =>g_child_table_token,
186 p_token2_value =>G_VIEW,
187 p_token3 =>g_parent_table_token,
188 p_token3_value =>'PA_PROJECTS_ALL');
189
190 x_return_status := OKE_API.G_RET_STS_ERROR;
191 END IF;
192 END IF;
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 -- store SQL error message on message stack
197 OKE_API.SET_MESSAGE(
198 p_app_name =>g_app_name,
199 p_msg_name =>G_UNEXPECTED_ERROR,
200 p_token1 =>G_SQLCODE_TOKEN,
201 p_token1_value =>SQLCODE,
202 p_token2 =>G_SQLERRM_TOKEN,
203 p_token2_value =>SQLERRM);
204 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
205
206 IF l_csr%ISOPEN THEN
207 CLOSE l_csr;
208 END IF;
209
210 END validate_project_id;
211
212
213 -- DATE PROBLEM
214
215 PROCEDURE validate_priority_code(x_return_status OUT NOCOPY VARCHAR2,
216 p_chr_rec IN chr_rec_type)IS
217 l_dummy_val VARCHAR2(1):= '?';
218 CURSOR l_csr IS
219 SELECT 'x' FROM OKE_PRIORITY_CODES_VL
220 WHERE PRIORITY_CODE = p_chr_rec.priority_code;
221
222 BEGIN
223 x_return_status := OKE_API.G_RET_STS_SUCCESS;
224
225
226 IF ( p_chr_rec.priority_code <> OKE_API.G_MISS_CHAR
227 AND p_chr_rec.priority_code IS NOT NULL) THEN
228
229 OPEN l_csr;
230 FETCH l_csr INTO l_dummy_val;
231 CLOSE l_csr;
232
233 IF (l_dummy_val = '?') THEN
234 OKE_API.SET_MESSAGE(
235 p_app_name =>g_app_name,
236 p_msg_name =>g_no_parent_record,
237 p_token1 =>g_col_name_token,
238 p_token1_value =>'PRIORITY_CODE',
239 p_token2 =>g_child_table_token,
240 p_token2_value =>G_VIEW,
241 p_token3 =>g_parent_table_token,
242 p_token3_value =>'OKE_PRIORITY_CODES_VL');
243
244 x_return_status := OKE_API.G_RET_STS_ERROR;
245 END IF;
246 End If;
247
248 EXCEPTION
249 WHEN G_EXCEPTION_HALT_VALIDATION THEN
250 NULL;
251
252 WHEN OTHERS THEN
253 -- store SQL error message on message stack
254 OKE_API.SET_MESSAGE(
255 p_app_name =>g_app_name,
256 p_msg_name =>G_UNEXPECTED_ERROR,
257 p_token1 =>G_SQLCODE_TOKEN,
258 p_token1_value =>SQLCODE,
259 p_token2 =>G_SQLERRM_TOKEN,
260 p_token2_value =>SQLERRM);
261 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
262
263 END validate_priority_code;
264
265
266
267
268 PROCEDURE validate_product_code(x_return_status OUT NOCOPY VARCHAR2,
269 p_chr_rec IN chr_rec_type)IS
270 l_dummy_val VARCHAR2(1):= '?';
271
272
273 CURSOR l_csr IS
274 SELECT 'x'
275 FROM FND_LOOKUP_VALUES_VL
276 WHERE VIEW_APPLICATION_ID = 777 AND LOOKUP_TYPE = 'PRODUCT_LINE'
277 AND NVL(ENABLED_FLAG , 'Y') = 'Y'
278 AND LOOKUP_CODE = p_chr_rec.PRODUCT_LINE_CODE
279 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE , SYSDATE - 1)
280 AND NVL(END_DATE_ACTIVE , SYSDATE + 1);
281
282
283 BEGIN
284 x_return_status := OKE_API.G_RET_STS_SUCCESS;
285
286
287 IF ( p_chr_rec.product_line_code <> OKE_API.G_MISS_CHAR
288 AND p_chr_rec.product_line_code IS NOT NULL) THEN
289
290 OPEN l_csr;
291 FETCH l_csr INTO l_dummy_val;
292 CLOSE l_csr;
293
294 IF (l_dummy_val = '?') THEN
295 OKE_API.SET_MESSAGE(
296 p_app_name =>g_app_name,
297 p_msg_name =>g_no_parent_record,
298 p_token1 =>g_col_name_token,
299 p_token1_value =>'PRODUCT_LINE_CODE',
300 p_token2 =>g_child_table_token,
301 p_token2_value =>G_VIEW,
302 p_token3 =>g_parent_table_token,
303 p_token3_value =>'FND_LOOKUP_VALUES_VL');
304
305 x_return_status := OKE_API.G_RET_STS_ERROR;
306 END IF;
307 End If;
308
309 EXCEPTION
310 WHEN G_EXCEPTION_HALT_VALIDATION THEN
311 NULL;
312
313 WHEN OTHERS THEN
314 -- store SQL error message on message stack
315 OKE_API.SET_MESSAGE(
316 p_app_name =>g_app_name,
317 p_msg_name =>G_UNEXPECTED_ERROR,
318 p_token1 =>G_SQLCODE_TOKEN,
319 p_token1_value =>SQLCODE,
320 p_token2 =>G_SQLERRM_TOKEN,
321 p_token2_value =>SQLERRM);
322 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
323
324 END validate_product_code;
325
326
327
328
329 PROCEDURE validate_country_code(x_return_status OUT NOCOPY VARCHAR2,
330 p_chr_rec IN chr_rec_type)IS
331 l_dummy_val VARCHAR2(1):= '?';
332
333
334 CURSOR l_csr IS
335 SELECT 'x'
336 FROM FND_TERRITORIES_VL
337 WHERE TERRITORY_CODE = p_chr_rec.COUNTRY_OF_ORIGIN_CODE;
338
339 BEGIN
340 x_return_status := OKE_API.G_RET_STS_SUCCESS;
341
342
343 IF ( p_chr_rec.country_of_origin_code <> OKE_API.G_MISS_CHAR
344 AND p_chr_rec.country_of_origin_code IS NOT NULL) THEN
345
346 OPEN l_csr;
347 FETCH l_csr INTO l_dummy_val;
348 CLOSE l_csr;
349
350 IF (l_dummy_val = '?') THEN
351 OKE_API.SET_MESSAGE(
352 p_app_name =>g_app_name,
353 p_msg_name =>g_no_parent_record,
354 p_token1 =>g_col_name_token,
355 p_token1_value =>'COUNTRY_OF_ORIGIN_CODE',
356 p_token2 =>g_child_table_token,
357 p_token2_value =>G_VIEW,
358 p_token3 =>g_parent_table_token,
359 p_token3_value =>'FND_TERRITORIES_VL');
360
361 x_return_status := OKE_API.G_RET_STS_ERROR;
362 END IF;
363 End If;
364
365 EXCEPTION
366 WHEN G_EXCEPTION_HALT_VALIDATION THEN
367 NULL;
368
369 WHEN OTHERS THEN
370 -- store SQL error message on message stack
371 OKE_API.SET_MESSAGE(
372 p_app_name =>g_app_name,
373 p_msg_name =>G_UNEXPECTED_ERROR,
374 p_token1 =>G_SQLCODE_TOKEN,
375 p_token1_value =>SQLCODE,
376 p_token2 =>G_SQLERRM_TOKEN,
377 p_token2_value =>SQLERRM);
378 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
379
380 END validate_country_code;
381
382
383 PROCEDURE validate_vat_code(x_return_status OUT NOCOPY VARCHAR2,
384 p_chr_rec IN chr_rec_type)IS
385 l_dummy_val VARCHAR2(1):= '?';
386
387 CURSOR get_info IS
388 SELECT AUTHORING_ORG_ID,BUY_OR_SELL
389 FROM OKC_K_HEADERS_B
390 WHERE ID = p_chr_rec.K_HEADER_ID;
391
392 l_org NUMBER;
393 l_intent VARCHAR2(30);
394
395 CURSOR l_csr IS
396 SELECT 'x'
397 FROM OKE_TAX_CODES_V
398 WHERE ( ORG_ID IS NULL OR ORG_ID = l_org )
399 AND BUY_OR_SELL = l_intent
400 AND SYSDATE BETWEEN NVL( START_DATE , SYSDATE - 1 )
401 AND NVL( END_DATE , SYSDATE + 1 )
402 AND ENABLED_FLAG = 'Y'
403 AND TAX_CODE = p_chr_rec.VAT_CODE;
404
405 BEGIN
406 x_return_status := OKE_API.G_RET_STS_SUCCESS;
407
408 OPEN get_info;
409 FETCH get_info INTO l_org,l_intent;
410 CLOSE get_info;
411
412 IF ( p_chr_rec.vat_code <> OKE_API.G_MISS_CHAR
413 AND p_chr_rec.vat_code IS NOT NULL) THEN
414
415 OPEN l_csr;
416 FETCH l_csr INTO l_dummy_val;
417 CLOSE l_csr;
418
419 IF (l_dummy_val = '?') THEN
420 OKE_API.SET_MESSAGE(
421 p_app_name =>g_app_name,
422 p_msg_name =>g_no_parent_record,
423 p_token1 =>g_col_name_token,
424 p_token1_value =>'VAT_CODE',
425 p_token2 =>g_child_table_token,
426 p_token2_value =>G_VIEW,
427 p_token3 =>g_parent_table_token,
428 p_token3_value =>'OKE_TAX_CODES_V');
429
430 x_return_status := OKE_API.G_RET_STS_ERROR;
431 END IF;
432 End If;
433
434 EXCEPTION
435 WHEN G_EXCEPTION_HALT_VALIDATION THEN
436 NULL;
437
438 WHEN OTHERS THEN
439 -- store SQL error message on message stack
440 OKE_API.SET_MESSAGE(
441 p_app_name =>g_app_name,
442 p_msg_name =>G_UNEXPECTED_ERROR,
443 p_token1 =>G_SQLCODE_TOKEN,
444 p_token1_value =>SQLCODE,
445 p_token2 =>G_SQLERRM_TOKEN,
446 p_token2_value =>SQLERRM);
447 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
448
449 END validate_vat_code;
450
451
452
453
454
455 PROCEDURE validate_prime_k_alias(x_return_status OUT NOCOPY VARCHAR2,
456 p_chr_rec IN chr_rec_type)IS
457
458 BEGIN
459 x_return_status := OKE_API.G_RET_STS_SUCCESS;
460 -- call column length utility
461 /* OKE_UTIL.CHECK_LENGTH(p_view_name =>G_VIEW,
462 p_col_name =>'PRIME_K_ALIAS',
463 p_col_value =>p_chr_rec.PRIME_K_ALIAS,
464 x_return_status =>x_return_status);*/
465
466 IF x_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
467 RAISE G_EXCEPTION_HALT_VALIDATION;
468 END IF;
469
470 EXCEPTION
471 WHEN G_EXCEPTION_HALT_VALIDATION THEN
472 NULL;
473
474 WHEN OTHERS THEN
475 -- store SQL error message on message stack
476 OKE_API.SET_MESSAGE(
477 p_app_name =>g_app_name,
478 p_msg_name =>G_UNEXPECTED_ERROR,
479 p_token1 =>G_SQLCODE_TOKEN,
480 p_token1_value =>SQLCODE,
481 p_token2 =>G_SQLERRM_TOKEN,
482 p_token2_value =>SQLERRM);
483 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
484
485 END validate_prime_k_alias;
486
487 PROCEDURE validate_prime_k_number(x_return_status OUT NOCOPY VARCHAR2,
488 p_chr_rec IN chr_rec_type)IS
489
490 BEGIN
491 x_return_status := OKE_API.G_RET_STS_SUCCESS;
492 -- call column length utility
493 /* OKE_UTIL.CHECK_LENGTH(p_view_name =>G_VIEW,
494 p_col_name =>'PRIME_K_NUMBER',
495 p_col_value =>p_chr_rec.prime_k_number,
496 x_return_status =>x_return_status);*/
497
498
499
500 IF x_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
501 RAISE G_EXCEPTION_HALT_VALIDATION;
502 END IF;
503
504 EXCEPTION
505 WHEN G_EXCEPTION_HALT_VALIDATION THEN
506 NULL;
507
508 WHEN OTHERS THEN
509 -- store SQL error message on message stack
510 OKE_API.SET_MESSAGE(
511 p_app_name =>g_app_name,
512 p_msg_name =>G_UNEXPECTED_ERROR,
513 p_token1 =>G_SQLCODE_TOKEN,
514 p_token1_value =>SQLCODE,
515 p_token2 =>G_SQLERRM_TOKEN,
516 p_token2_value =>SQLERRM);
517 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
518
519 END validate_prime_k_number;
520
521
522 PROCEDURE validate_k_type_code(x_return_status OUT NOCOPY VARCHAR2,
523 p_chr_rec IN chr_rec_type)IS
524
525 l_dummy_val VARCHAR2(1):= '?';
526 CURSOR l_csr IS
527 SELECT 'x' FROM OKE_K_TYPES_B
528 WHERE K_TYPE_CODE = p_chr_rec.k_type_code;
529
530 BEGIN
531 x_return_status := OKE_API.G_RET_STS_SUCCESS;
532
533 IF ( p_chr_rec.k_type_code= OKE_API.G_MISS_CHAR
534 OR p_chr_rec.k_type_code IS NULL) THEN
535 OKE_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 =>'K_TYPE_CODE');
539 x_return_status := OKE_API.G_RET_STS_ERROR;
540 RAISE G_EXCEPTION_HALT_VALIDATION;
541 END IF;
542
543
544 open l_csr;
545 fetch l_csr into l_dummy_val;
546 close l_csr;
547
548
549 IF (l_dummy_val = '?') THEN
550 OKE_API.SET_MESSAGE(
551 p_app_name =>g_app_name,
552 p_msg_name =>g_no_parent_record,
553 p_token1 =>g_col_name_token,
554 p_token1_value =>'K_TYPE_CODE',
555 p_token2 =>g_child_table_token,
556 p_token2_value =>G_VIEW,
557 p_token3 =>g_parent_table_token,
558 p_token3_value =>'OKE_K_TYPES_B');
559
560 x_return_status := OKE_API.G_RET_STS_ERROR;
561 END IF;
562
563
564 EXCEPTION
565 WHEN G_EXCEPTION_HALT_VALIDATION THEN
566 NULL;
567
568 WHEN OTHERS THEN
569 -- store SQL error message on message stack
570 OKE_API.SET_MESSAGE(
571 p_app_name =>g_app_name,
572 p_msg_name =>G_UNEXPECTED_ERROR,
573 p_token1 =>G_SQLCODE_TOKEN,
574 p_token1_value =>SQLCODE,
575 p_token2 =>G_SQLERRM_TOKEN,
576 p_token2_value =>SQLERRM);
577 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
578
579 END validate_k_type_code;
580
581
582
583 PROCEDURE validate_authorizing_reason(x_return_status OUT NOCOPY VARCHAR2,
584 p_chr_rec IN chr_rec_type)IS
585
586 BEGIN
587 x_return_status := OKE_API.G_RET_STS_SUCCESS;
588 -- call column length utility
589 /* OKE_UTIL.CHECK_LENGTH(p_view_name =>G_VIEW,
590 p_col_name =>'AUTHORIZING_REASON',
591 p_col_value =>p_chr_rec.authorizing_reason,
592 x_return_status =>x_return_status);*/
593
594 IF x_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
595 RAISE G_EXCEPTION_HALT_VALIDATION;
596 END IF;
597
598 EXCEPTION
599 WHEN G_EXCEPTION_HALT_VALIDATION THEN
600 NULL;
601
602 WHEN OTHERS THEN
603 -- store SQL error message on message stack
604 OKE_API.SET_MESSAGE(
605 p_app_name =>g_app_name,
606 p_msg_name =>G_UNEXPECTED_ERROR,
607 p_token1 =>G_SQLCODE_TOKEN,
608 p_token1_value =>SQLCODE,
609 p_token2 =>G_SQLERRM_TOKEN,
610 p_token2_value =>SQLERRM);
611 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
612
613 END validate_authorizing_reason;
614
615
616 PROCEDURE validate_award_cancel_date(x_return_status OUT NOCOPY VARCHAR2,
617 p_chr_rec IN chr_rec_type)IS
618
619 BEGIN
620 x_return_status := OKE_API.G_RET_STS_SUCCESS;
621 -- check the date is valid
622
623 /*
624 IF ( p_chr_rec.award_cancel_date <> OKE_API.G_MISS_DATE
625 AND p_chr_rec.award_cancel_date IS NOT NULL) THEN
626
627 IF ( p_chr_rec.award_date <> OKE_API.G_MISS_DATE
628 AND p_chr_rec.award_date IS NOT NULL) THEN
629 IF (p_chr_rec.award_cancel_date < p_chr_rec.award_date) THEN
630 x_return_status := OKE_API.G_RET_STS_ERROR;
631 END IF;
632 ELSE
633 x_return_status := OKE_API.G_RET_STS_ERROR;
634 END IF;
635
636 END IF;
637 */
638 IF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
639 OKE_API.SET_MESSAGE(
640 p_app_name =>g_app_name,
641 p_msg_name =>'OKE_NOT_AWARD_CONTRACT',
642 p_token1 =>G_COL_NAME_TOKEN,
643 p_token1_value =>'AWARD_CANCEL_DATE');
644 END IF;
645
646 EXCEPTION
647
648 WHEN OTHERS THEN
649 -- store SQL error message on message stack
650 OKE_API.SET_MESSAGE(
651 p_app_name =>g_app_name,
652 p_msg_name =>G_UNEXPECTED_ERROR,
653 p_token1 =>G_SQLCODE_TOKEN,
654 p_token1_value =>SQLCODE,
655 p_token2 =>G_SQLERRM_TOKEN,
656 p_token2_value =>SQLERRM);
657 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
658
659 END validate_award_cancel_date;
660
661
662
663 PROCEDURE validate_date_definitized(x_return_status OUT NOCOPY VARCHAR2,
664 p_chr_rec IN chr_rec_type)IS
665
666 BEGIN
667 x_return_status := OKE_API.G_RET_STS_SUCCESS;
668 -- check the date is valid
669 -- take out the validation, since no requirement specified
670
671 /* IF ( p_chr_rec.date_definitized <> OKE_API.G_MISS_DATE
672 AND p_chr_rec.date_definitized IS NOT NULL) THEN
673
674 IF ( p_chr_rec.award_date <> OKE_API.G_MISS_DATE
675 AND p_chr_rec.award_date IS NOT NULL)THEN
676 IF (p_chr_rec.date_definitized < p_chr_rec.award_date) THEN
677 x_return_status := OKE_API.G_RET_STS_ERROR;
678 END IF;
679
680 ELSE
681 x_return_status := OKE_API.G_RET_STS_ERROR;
682
683 END IF;
684
685 END IF; */
686
687
688
689
690 IF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
691 OKE_API.SET_MESSAGE(
692 p_app_name =>g_app_name,
693 p_msg_name =>'OKE_NOT_AWARD_CONTRACT',
694 p_token1 =>G_COL_NAME_TOKEN,
695 p_token1_value =>'DATE_DEFINITIZED');
696 END IF;
697
698 EXCEPTION
699
700 WHEN OTHERS THEN
701 -- store SQL error message on message stack
702 OKE_API.SET_MESSAGE(
703 p_app_name =>g_app_name,
704 p_msg_name =>G_UNEXPECTED_ERROR,
705 p_token1 =>G_SQLCODE_TOKEN,
706 p_token1_value =>SQLCODE,
707 p_token2 =>G_SQLERRM_TOKEN,
708 p_token2_value =>SQLERRM);
709 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
710
711 END validate_date_definitized;
712
713
714 PROCEDURE validate_date_received(x_return_status OUT NOCOPY VARCHAR2,
715 p_chr_rec IN chr_rec_type)IS
716
717 BEGIN
718 x_return_status := OKE_API.G_RET_STS_SUCCESS;
719 -- check the date is valid
720
721 IF ( p_chr_rec.date_received <> OKE_API.G_MISS_DATE
722 AND p_chr_rec.date_received IS NOT NULL) THEN
723
724 IF ( p_chr_rec.date_issued <> OKE_API.G_MISS_DATE
725 AND p_chr_rec.date_issued IS NOT NULL)THEN
726 IF (p_chr_rec.date_received < p_chr_rec.date_issued) THEN
727 x_return_status := OKE_API.G_RET_STS_ERROR;
728 END IF;
729
730 END IF;
731
732 END IF;
733
734 IF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
735 OKE_API.SET_MESSAGE(
736 p_app_name =>g_app_name,
737 p_msg_name =>'OKE_ISSUED_FIRST',
738 p_token1 =>G_COL_NAME_TOKEN,
739 p_token1_value =>'DATE_RECEIVED');
740 END IF;
741
742 EXCEPTION
743
744 WHEN OTHERS THEN
745 -- store SQL error message on message stack
746 OKE_API.SET_MESSAGE(
747 p_app_name =>g_app_name,
748 p_msg_name =>G_UNEXPECTED_ERROR,
749 p_token1 =>G_SQLCODE_TOKEN,
750 p_token1_value =>SQLCODE,
751 p_token2 =>G_SQLERRM_TOKEN,
752 p_token2_value =>SQLERRM);
753 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
754
755 END validate_date_received;
756
757
758
759 PROCEDURE validate_award_date(x_return_status OUT NOCOPY VARCHAR2,
760 p_chr_rec IN chr_rec_type)IS
761
762 CURSOR l_getdate IS
763 SELECT start_date from okc_k_headers_b
764 WHERE id = p_chr_rec.k_header_id;
765
766 l_startdate DATE;
767
768 BEGIN
769 x_return_status := OKE_API.G_RET_STS_SUCCESS;
770 -- check the date is valid
771 /*
772 open l_getdate;
773 fetch l_getdate into l_startdate;
774 close l_getdate;
775
776 IF ( p_chr_rec.award_date <> OKE_API.G_MISS_DATE
777 AND p_chr_rec.award_date IS NOT NULL) THEN
778
779 IF ( l_startdate <> OKE_API.G_MISS_DATE
780 AND l_startdate IS NOT NULL) THEN
781
782 IF ( p_chr_rec.AWARD_DATE > l_startdate) THEN
783 x_return_status := OKE_API.G_RET_STS_ERROR;
784 END IF;
785 END IF;
786 END IF;
787 */
788 IF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
789 OKE_API.SET_MESSAGE(
790 p_app_name =>g_app_name,
791 p_msg_name =>'OKE_INVALID_AWARD_DATE');
792 END IF;
793
794 EXCEPTION
795
796 WHEN OTHERS THEN
797 -- store SQL error message on message stack
798 OKE_API.SET_MESSAGE(
799 p_app_name =>g_app_name,
800 p_msg_name =>G_UNEXPECTED_ERROR,
801 p_token1 =>G_SQLCODE_TOKEN,
802 p_token1_value =>SQLCODE,
803 p_token2 =>G_SQLERRM_TOKEN,
804 p_token2_value =>SQLERRM);
805 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
806
807 END validate_award_date;
808
809
810 PROCEDURE validate_booked_flag(x_return_status OUT NOCOPY VARCHAR2,
811 p_chr_rec IN chr_rec_type)IS
812
813 BEGIN
814 x_return_status := OKE_API.G_RET_STS_SUCCESS;
815
816 IF ( p_chr_rec.booked_flag <> OKE_API.G_MISS_CHAR
817 AND p_chr_rec.booked_flag IS NOT NULL) THEN
818 IF (UPPER(p_chr_rec.booked_flag) NOT IN ('Y','N')) THEN
819 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
820 p_msg_name =>G_REQUIRED_VALUE,
821 p_token1 =>G_COL_NAME_TOKEN,
822 p_token1_value =>'BOOKED_FLAG');
823 x_return_status := OKE_API.G_RET_STS_ERROR;
824 RAISE G_EXCEPTION_HALT_VALIDATION;
825 END IF;
826
827 END IF;
828
829 EXCEPTION
830 WHEN G_EXCEPTION_HALT_VALIDATION THEN
831 NULL;
832
833 WHEN OTHERS THEN
834 -- store SQL error message on message stack
835 OKE_API.SET_MESSAGE(
836 p_app_name =>g_app_name,
837 p_msg_name =>G_UNEXPECTED_ERROR,
838 p_token1 =>G_SQLCODE_TOKEN,
839 p_token1_value =>SQLCODE,
840 p_token2 =>G_SQLERRM_TOKEN,
841 p_token2_value =>SQLERRM);
842 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
843
844 END validate_booked_flag;
845
846 PROCEDURE validate_open_flag(x_return_status OUT NOCOPY VARCHAR2,
847 p_chr_rec IN chr_rec_type)IS
848
849 BEGIN
850 x_return_status := OKE_API.G_RET_STS_SUCCESS;
851
852 IF ( p_chr_rec.open_flag <> OKE_API.G_MISS_CHAR
853 AND p_chr_rec.open_flag IS NOT NULL) THEN
854 IF (UPPER(p_chr_rec.open_flag) NOT IN ('Y','N')) THEN
855 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
856 p_msg_name =>G_REQUIRED_VALUE,
857 p_token1 =>G_COL_NAME_TOKEN,
858 p_token1_value =>'OPEN_FLAG');
859 x_return_status := OKE_API.G_RET_STS_ERROR;
860 RAISE G_EXCEPTION_HALT_VALIDATION;
861 END IF;
862
863 END IF;
864
865 EXCEPTION
866 WHEN G_EXCEPTION_HALT_VALIDATION THEN
867 NULL;
868
869 WHEN OTHERS THEN
870 -- store SQL error message on message stack
871 OKE_API.SET_MESSAGE(
872 p_app_name =>g_app_name,
873 p_msg_name =>G_UNEXPECTED_ERROR,
874 p_token1 =>G_SQLCODE_TOKEN,
875 p_token1_value =>SQLCODE,
876 p_token2 =>G_SQLERRM_TOKEN,
877 p_token2_value =>SQLERRM);
878 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
879
880 END validate_open_flag;
881
882 PROCEDURE validate_cfe_flag(x_return_status OUT NOCOPY VARCHAR2,
883 p_chr_rec IN chr_rec_type)IS
884
885 BEGIN
886 x_return_status := OKE_API.G_RET_STS_SUCCESS;
887
888 IF ( p_chr_rec.cfe_flag <> OKE_API.G_MISS_CHAR
889 AND p_chr_rec.cfe_flag IS NOT NULL) THEN
890 IF (UPPER(p_chr_rec.cfe_flag) NOT IN ('Y','N')) THEN
891 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
892 p_msg_name =>G_REQUIRED_VALUE,
893 p_token1 =>G_COL_NAME_TOKEN,
894 p_token1_value =>'CFE_FLAG');
895 x_return_status := OKE_API.G_RET_STS_ERROR;
896 RAISE G_EXCEPTION_HALT_VALIDATION;
897 END IF;
898
899 END IF;
900
901 EXCEPTION
902 WHEN G_EXCEPTION_HALT_VALIDATION THEN
903 NULL;
904
905 WHEN OTHERS THEN
906 -- store SQL error message on message stack
907 OKE_API.SET_MESSAGE(
908 p_app_name =>g_app_name,
909 p_msg_name =>G_UNEXPECTED_ERROR,
910 p_token1 =>G_SQLCODE_TOKEN,
911 p_token1_value =>SQLCODE,
912 p_token2 =>G_SQLERRM_TOKEN,
913 p_token2_value =>SQLERRM);
914 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
915
916 END validate_cfe_flag;
917
918 PROCEDURE validate_export_flag(x_return_status OUT NOCOPY VARCHAR2,
919 p_chr_rec IN chr_rec_type)IS
920
921 BEGIN
922 x_return_status := OKE_API.G_RET_STS_SUCCESS;
923
924 IF ( p_chr_rec.export_flag <> OKE_API.G_MISS_CHAR
925 AND p_chr_rec.export_flag IS NOT NULL) THEN
926 IF (UPPER(p_chr_rec.export_flag) NOT IN ('Y','N')) THEN
927 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
928 p_msg_name =>G_REQUIRED_VALUE,
929 p_token1 =>G_COL_NAME_TOKEN,
930 p_token1_value =>'EXPORT_FLAG');
931 x_return_status := OKE_API.G_RET_STS_ERROR;
932 RAISE G_EXCEPTION_HALT_VALIDATION;
933 END IF;
934
935 END IF;
936
937 EXCEPTION
938 WHEN G_EXCEPTION_HALT_VALIDATION THEN
939 NULL;
940
941 WHEN OTHERS THEN
942 -- store SQL error message on message stack
943 OKE_API.SET_MESSAGE(
944 p_app_name =>g_app_name,
945 p_msg_name =>G_UNEXPECTED_ERROR,
946 p_token1 =>G_SQLCODE_TOKEN,
947 p_token1_value =>SQLCODE,
948 p_token2 =>G_SQLERRM_TOKEN,
949 p_token2_value =>SQLERRM);
950 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
951
952 END validate_export_flag;
953
954 PROCEDURE validate_human_subject_flag(x_return_status OUT NOCOPY VARCHAR2,
955 p_chr_rec IN chr_rec_type)IS
956
957 BEGIN
958 x_return_status := OKE_API.G_RET_STS_SUCCESS;
959
960 IF ( p_chr_rec.human_subject_flag <> OKE_API.G_MISS_CHAR
961 AND p_chr_rec.human_subject_flag IS NOT NULL) THEN
962 IF (UPPER(p_chr_rec.human_subject_flag) NOT IN ('Y','N')) THEN
963 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
964 p_msg_name =>G_REQUIRED_VALUE,
965 p_token1 =>G_COL_NAME_TOKEN,
966 p_token1_value =>'HUMAN_SUBJECT_FLAG');
967 x_return_status := OKE_API.G_RET_STS_ERROR;
968 RAISE G_EXCEPTION_HALT_VALIDATION;
969 END IF;
970
971 END IF;
972
973 EXCEPTION
974 WHEN G_EXCEPTION_HALT_VALIDATION THEN
975 NULL;
976
977 WHEN OTHERS THEN
978 -- store SQL error message on message stack
979 OKE_API.SET_MESSAGE(
980 p_app_name =>g_app_name,
981 p_msg_name =>G_UNEXPECTED_ERROR,
982 p_token1 =>G_SQLCODE_TOKEN,
983 p_token1_value =>SQLCODE,
984 p_token2 =>G_SQLERRM_TOKEN,
985 p_token2_value =>SQLERRM);
986 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
987
988 END validate_human_subject_flag;
989
990 PROCEDURE validate_cqa_flag(x_return_status OUT NOCOPY VARCHAR2,
991 p_chr_rec IN chr_rec_type)IS
992
993 BEGIN
994 x_return_status := OKE_API.G_RET_STS_SUCCESS;
995
996 IF ( p_chr_rec.cqa_flag <> OKE_API.G_MISS_CHAR
997 AND p_chr_rec.cqa_flag IS NOT NULL) THEN
998 IF (UPPER(p_chr_rec.cqa_flag) NOT IN ('Y','N')) THEN
999 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1000 p_msg_name =>G_REQUIRED_VALUE,
1001 p_token1 =>G_COL_NAME_TOKEN,
1002 p_token1_value =>'CQA_FLAG');
1003 x_return_status := OKE_API.G_RET_STS_ERROR;
1004 RAISE G_EXCEPTION_HALT_VALIDATION;
1005 END IF;
1006
1007 END IF;
1008
1009 EXCEPTION
1010 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1011 NULL;
1012
1013 WHEN OTHERS THEN
1014 -- store SQL error message on message stack
1015 OKE_API.SET_MESSAGE(
1016 p_app_name =>g_app_name,
1017 p_msg_name =>G_UNEXPECTED_ERROR,
1018 p_token1 =>G_SQLCODE_TOKEN,
1019 p_token1_value =>SQLCODE,
1020 p_token2 =>G_SQLERRM_TOKEN,
1021 p_token2_value =>SQLERRM);
1022 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1023
1024 END validate_cqa_flag;
1025
1026 PROCEDURE validate_interim_rpt_req_flag(x_return_status OUT NOCOPY VARCHAR2,
1027 p_chr_rec IN chr_rec_type)IS
1028
1029 BEGIN
1030 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1031
1032 IF ( p_chr_rec.interim_rpt_req_flag <> OKE_API.G_MISS_CHAR
1033 AND p_chr_rec.interim_rpt_req_flag IS NOT NULL) THEN
1034 IF (UPPER(p_chr_rec.interim_rpt_req_flag) NOT IN ('Y','N')) THEN
1035 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1036 p_msg_name =>G_REQUIRED_VALUE,
1037 p_token1 =>G_COL_NAME_TOKEN,
1038 p_token1_value =>'INTERIM_RPT_REQ_FLAG');
1039 x_return_status := OKE_API.G_RET_STS_ERROR;
1040 RAISE G_EXCEPTION_HALT_VALIDATION;
1041 END IF;
1042
1043 END IF;
1044
1045 EXCEPTION
1046 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1047 NULL;
1048
1049 WHEN OTHERS THEN
1050 -- store SQL error message on message stack
1051 OKE_API.SET_MESSAGE(
1052 p_app_name =>g_app_name,
1053 p_msg_name =>G_UNEXPECTED_ERROR,
1054 p_token1 =>G_SQLCODE_TOKEN,
1055 p_token1_value =>SQLCODE,
1056 p_token2 =>G_SQLERRM_TOKEN,
1057 p_token2_value =>SQLERRM);
1058 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1059
1060 END validate_interim_rpt_req_flag;
1061
1062 PROCEDURE validate_penalty_clause_flag(x_return_status OUT NOCOPY VARCHAR2,
1063 p_chr_rec IN chr_rec_type)IS
1064
1065 BEGIN
1066 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1067
1068 IF ( p_chr_rec.penalty_clause_flag <> OKE_API.G_MISS_CHAR
1069 AND p_chr_rec.penalty_clause_flag IS NOT NULL) THEN
1070 IF (UPPER(p_chr_rec.penalty_clause_flag) NOT IN ('Y','N')) THEN
1071 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1072 p_msg_name =>G_REQUIRED_VALUE,
1073 p_token1 =>G_COL_NAME_TOKEN,
1074 p_token1_value =>'PENALTY_CLAUSE_FLAG');
1075 x_return_status := OKE_API.G_RET_STS_ERROR;
1076 RAISE G_EXCEPTION_HALT_VALIDATION;
1077 END IF;
1078
1079 END IF;
1080
1081 EXCEPTION
1082 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1083 NULL;
1084
1085 WHEN OTHERS THEN
1086 -- store SQL error message on message stack
1087 OKE_API.SET_MESSAGE(
1088 p_app_name =>g_app_name,
1089 p_msg_name =>G_UNEXPECTED_ERROR,
1090 p_token1 =>G_SQLCODE_TOKEN,
1091 p_token1_value =>SQLCODE,
1092 p_token2 =>G_SQLERRM_TOKEN,
1093 p_token2_value =>SQLERRM);
1094 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1095
1096 END validate_penalty_clause_flag;
1097
1098 PROCEDURE validate_reporting_flag(x_return_status OUT NOCOPY VARCHAR2,
1099 p_chr_rec IN chr_rec_type)IS
1100
1101 BEGIN
1102 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1103
1104 IF ( p_chr_rec.reporting_flag <> OKE_API.G_MISS_CHAR
1105 AND p_chr_rec.reporting_flag IS NOT NULL) THEN
1106 IF (UPPER(p_chr_rec.reporting_flag) NOT IN ('Y','N')) THEN
1107 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1108 p_msg_name =>G_REQUIRED_VALUE,
1109 p_token1 =>G_COL_NAME_TOKEN,
1110 p_token1_value =>'REPROTING_FLAG');
1111 x_return_status := OKE_API.G_RET_STS_ERROR;
1112 RAISE G_EXCEPTION_HALT_VALIDATION;
1113 END IF;
1114
1115 END IF;
1116
1117 EXCEPTION
1118 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1119 NULL;
1120
1121 WHEN OTHERS THEN
1122 -- store SQL error message on message stack
1123 OKE_API.SET_MESSAGE(
1124 p_app_name =>g_app_name,
1125 p_msg_name =>G_UNEXPECTED_ERROR,
1126 p_token1 =>G_SQLCODE_TOKEN,
1127 p_token1_value =>SQLCODE,
1128 p_token2 =>G_SQLERRM_TOKEN,
1129 p_token2_value =>SQLERRM);
1130 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1131
1132 END validate_reporting_flag;
1133
1134 PROCEDURE validate_sb_plan_req_flag(x_return_status OUT NOCOPY VARCHAR2,
1135 p_chr_rec IN chr_rec_type)IS
1136
1137 BEGIN
1138 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1139
1140 IF ( p_chr_rec.sb_plan_req_flag <> OKE_API.G_MISS_CHAR
1141 AND p_chr_rec.sb_plan_req_flag IS NOT NULL) THEN
1142 IF (UPPER(p_chr_rec.sb_plan_req_flag) NOT IN ('Y','N')) THEN
1143 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1144 p_msg_name =>G_REQUIRED_VALUE,
1145 p_token1 =>G_COL_NAME_TOKEN,
1146 p_token1_value =>'SB_PLAN_REQ_FLAG');
1147 x_return_status := OKE_API.G_RET_STS_ERROR;
1148 RAISE G_EXCEPTION_HALT_VALIDATION;
1149 END IF;
1150
1151 END IF;
1152
1153 EXCEPTION
1154 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1155 NULL;
1156
1157 WHEN OTHERS THEN
1158 -- store SQL error message on message stack
1159 OKE_API.SET_MESSAGE(
1160 p_app_name =>g_app_name,
1161 p_msg_name =>G_UNEXPECTED_ERROR,
1162 p_token1 =>G_SQLCODE_TOKEN,
1163 p_token1_value =>SQLCODE,
1164 p_token2 =>G_SQLERRM_TOKEN,
1165 p_token2_value =>SQLERRM);
1166 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1167
1168 END validate_sb_plan_req_flag;
1169
1170 PROCEDURE validate_sb_report_flag(x_return_status OUT NOCOPY VARCHAR2,
1171 p_chr_rec IN chr_rec_type)IS
1172
1173 BEGIN
1174 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1175
1176 IF ( p_chr_rec.sb_report_flag <> OKE_API.G_MISS_CHAR
1177 AND p_chr_rec.sb_report_flag IS NOT NULL) THEN
1178 IF (UPPER(p_chr_rec.sb_report_flag) NOT IN ('Y','N')) THEN
1179 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1180 p_msg_name =>G_REQUIRED_VALUE,
1181 p_token1 =>G_COL_NAME_TOKEN,
1182 p_token1_value =>'SB_REPORT_FLAG');
1183 x_return_status := OKE_API.G_RET_STS_ERROR;
1184 RAISE G_EXCEPTION_HALT_VALIDATION;
1185 END IF;
1186
1187 END IF;
1188
1189 EXCEPTION
1190 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1191 NULL;
1192
1193 WHEN OTHERS THEN
1194 -- store SQL error message on message stack
1195 OKE_API.SET_MESSAGE(
1196 p_app_name =>g_app_name,
1197 p_msg_name =>G_UNEXPECTED_ERROR,
1198 p_token1 =>G_SQLCODE_TOKEN,
1199 p_token1_value =>SQLCODE,
1200 p_token2 =>G_SQLERRM_TOKEN,
1201 p_token2_value =>SQLERRM);
1202 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1203
1204 END validate_sb_report_flag;
1205
1206 PROCEDURE validate_nte_warning_flag(x_return_status OUT NOCOPY VARCHAR2,
1207 p_chr_rec IN chr_rec_type)IS
1208
1209 BEGIN
1210 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1211
1212 IF ( p_chr_rec.nte_warning_flag <> OKE_API.G_MISS_CHAR
1213 AND p_chr_rec.nte_warning_flag IS NOT NULL) THEN
1214 IF (UPPER(p_chr_rec.nte_warning_flag) NOT IN ('Y','N')) THEN
1215 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1216 p_msg_name =>G_REQUIRED_VALUE,
1217 p_token1 =>G_COL_NAME_TOKEN,
1218 p_token1_value =>'NTE_WARNING_FLAG');
1219 x_return_status := OKE_API.G_RET_STS_ERROR;
1220 RAISE G_EXCEPTION_HALT_VALIDATION;
1221 END IF;
1222
1223 END IF;
1224
1225 EXCEPTION
1226 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1227 NULL;
1228
1229 WHEN OTHERS THEN
1230 -- store SQL error message on message stack
1231 OKE_API.SET_MESSAGE(
1232 p_app_name =>g_app_name,
1233 p_msg_name =>G_UNEXPECTED_ERROR,
1234 p_token1 =>G_SQLCODE_TOKEN,
1235 p_token1_value =>SQLCODE,
1236 p_token2 =>G_SQLERRM_TOKEN,
1237 p_token2_value =>SQLERRM);
1238 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1239
1240 END validate_nte_warning_flag;
1241
1242
1243 PROCEDURE validate_bill_without_def_flag(x_return_status OUT NOCOPY VARCHAR2,
1244 p_chr_rec IN chr_rec_type)IS
1245
1246 BEGIN
1247 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1248
1249 IF ( p_chr_rec.bill_without_def_flag <> OKE_API.G_MISS_CHAR
1250 AND p_chr_rec.bill_without_def_flag IS NOT NULL) THEN
1251 IF (UPPER(p_chr_rec.bill_without_def_flag) NOT IN ('Y','N')) THEN
1252 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1253 p_msg_name =>G_REQUIRED_VALUE,
1254 p_token1 =>G_COL_NAME_TOKEN,
1255 p_token1_value =>'BILL_WITHOUT_DEF_FLAG');
1256 x_return_status := OKE_API.G_RET_STS_ERROR;
1257 RAISE G_EXCEPTION_HALT_VALIDATION;
1258 END IF;
1259
1260 END IF;
1261
1262 EXCEPTION
1263 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1264 NULL;
1265
1266 WHEN OTHERS THEN
1267 -- store SQL error message on message stack
1268 OKE_API.SET_MESSAGE(
1269 p_app_name =>g_app_name,
1270 p_msg_name =>G_UNEXPECTED_ERROR,
1271 p_token1 =>G_SQLCODE_TOKEN,
1272 p_token1_value =>SQLCODE,
1273 p_token2 =>G_SQLERRM_TOKEN,
1274 p_token2_value =>SQLERRM);
1275 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1276
1277 END validate_bill_without_def_flag;
1278
1279
1280
1281
1282 PROCEDURE validate_cas_flag(x_return_status OUT NOCOPY VARCHAR2,
1283 p_chr_rec IN chr_rec_type)IS
1284
1285 BEGIN
1286 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1287
1288 IF ( p_chr_rec.cas_flag <> OKE_API.G_MISS_CHAR
1289 AND p_chr_rec.cas_flag IS NOT NULL) THEN
1290 IF (UPPER(p_chr_rec.cas_flag) NOT IN ('Y','N')) THEN
1291 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1292 p_msg_name =>G_REQUIRED_VALUE,
1293 p_token1 =>G_COL_NAME_TOKEN,
1294 p_token1_value =>'CAS_FLAG');
1295 x_return_status := OKE_API.G_RET_STS_ERROR;
1296 RAISE G_EXCEPTION_HALT_VALIDATION;
1297 END IF;
1298
1299 END IF;
1300
1301 EXCEPTION
1302 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1303 NULL;
1304
1305 WHEN OTHERS THEN
1306 -- store SQL error message on message stack
1307 OKE_API.SET_MESSAGE(
1308 p_app_name =>g_app_name,
1309 p_msg_name =>G_UNEXPECTED_ERROR,
1310 p_token1 =>G_SQLCODE_TOKEN,
1311 p_token1_value =>SQLCODE,
1312 p_token2 =>G_SQLERRM_TOKEN,
1313 p_token2_value =>SQLERRM);
1314 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1315
1316 END validate_cas_flag;
1317
1318 PROCEDURE validate_classified_flag(x_return_status OUT NOCOPY VARCHAR2,
1319 p_chr_rec IN chr_rec_type)IS
1320
1321 BEGIN
1322 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1323
1324 IF ( p_chr_rec.classified_flag <> OKE_API.G_MISS_CHAR
1325 AND p_chr_rec.classified_flag IS NOT NULL) THEN
1326 IF (UPPER(p_chr_rec.classified_flag) NOT IN ('Y','N')) THEN
1327 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1328 p_msg_name =>G_REQUIRED_VALUE,
1329 p_token1 =>G_COL_NAME_TOKEN,
1330 p_token1_value =>'CLASSIFIED_FLAG');
1331 x_return_status := OKE_API.G_RET_STS_ERROR;
1332 RAISE G_EXCEPTION_HALT_VALIDATION;
1333 END IF;
1334
1335 END IF;
1336
1337 EXCEPTION
1338 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1339 NULL;
1340
1341 WHEN OTHERS THEN
1342 -- store SQL error message on message stack
1343 OKE_API.SET_MESSAGE(
1344 p_app_name =>g_app_name,
1345 p_msg_name =>G_UNEXPECTED_ERROR,
1346 p_token1 =>G_SQLCODE_TOKEN,
1347 p_token1_value =>SQLCODE,
1348 p_token2 =>G_SQLERRM_TOKEN,
1349 p_token2_value =>SQLERRM);
1350 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1351
1352 END validate_classified_flag;
1353
1354
1355 PROCEDURE validate_client_approve_req(x_return_status OUT NOCOPY VARCHAR2,
1356 p_chr_rec IN chr_rec_type)IS
1357
1358 BEGIN
1359 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1360
1361 IF ( p_chr_rec.client_approve_req_flag <> OKE_API.G_MISS_CHAR
1362 AND p_chr_rec.client_approve_req_flag IS NOT NULL) THEN
1363 IF (UPPER(p_chr_rec.client_approve_req_flag) NOT IN ('Y','N')) THEN
1364 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1365 p_msg_name =>G_REQUIRED_VALUE,
1366 p_token1 =>G_COL_NAME_TOKEN,
1367 p_token1_value =>'CLIENT_APPROVE_REQ_FLAG');
1368 x_return_status := OKE_API.G_RET_STS_ERROR;
1369 RAISE G_EXCEPTION_HALT_VALIDATION;
1370 END IF;
1371
1372 END IF;
1373
1374 EXCEPTION
1375 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1376 NULL;
1377
1378 WHEN OTHERS THEN
1379 -- store SQL error message on message stack
1380 OKE_API.SET_MESSAGE(
1381 p_app_name =>g_app_name,
1382 p_msg_name =>G_UNEXPECTED_ERROR,
1383 p_token1 =>G_SQLCODE_TOKEN,
1384 p_token1_value =>SQLCODE,
1385 p_token2 =>G_SQLERRM_TOKEN,
1386 p_token2_value =>SQLERRM);
1387 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1388
1389 END validate_client_approve_req;
1390
1391 PROCEDURE validate_dcaa_audit_req_flag(x_return_status OUT NOCOPY VARCHAR2,
1392 p_chr_rec IN chr_rec_type)IS
1393
1394 BEGIN
1395 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1396
1397 IF ( p_chr_rec.dcaa_audit_req_flag <> OKE_API.G_MISS_CHAR
1398 AND p_chr_rec.dcaa_audit_req_flag IS NOT NULL) THEN
1399 IF (UPPER(p_chr_rec.dcaa_audit_req_flag) NOT IN ('Y','N')) THEN
1400 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1401 p_msg_name =>G_REQUIRED_VALUE,
1402 p_token1 =>G_COL_NAME_TOKEN,
1403 p_token1_value =>'DCAA_AUDIT_REQ_FLAG');
1404 x_return_status := OKE_API.G_RET_STS_ERROR;
1405 RAISE G_EXCEPTION_HALT_VALIDATION;
1406 END IF;
1407
1408 END IF;
1409
1410 EXCEPTION
1411 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1412 NULL;
1413
1414 WHEN OTHERS THEN
1415 -- store SQL error message on message stack
1416 OKE_API.SET_MESSAGE(
1417 p_app_name =>g_app_name,
1418 p_msg_name =>G_UNEXPECTED_ERROR,
1419 p_token1 =>G_SQLCODE_TOKEN,
1420 p_token1_value =>SQLCODE,
1421 p_token2 =>G_SQLERRM_TOKEN,
1422 p_token2_value =>SQLERRM);
1423 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1424
1425 END validate_dcaa_audit_req_flag;
1426
1427 PROCEDURE validate_oh_rates_final_flag(x_return_status OUT NOCOPY VARCHAR2,
1428 p_chr_rec IN chr_rec_type)IS
1429
1430 BEGIN
1431 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1432
1433 IF ( p_chr_rec.oh_rates_final_flag <> OKE_API.G_MISS_CHAR
1434 AND p_chr_rec.OH_RATES_FINAL_FLAG IS NOT NULL) THEN
1435 IF (UPPER(p_chr_rec.OH_RATES_FINAL_FLAG) NOT IN ('Y','N')) THEN
1436 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1437 p_msg_name =>G_REQUIRED_VALUE,
1438 p_token1 =>G_COL_NAME_TOKEN,
1439 p_token1_value =>'OH_RATES_FINAL_FLAG');
1440 x_return_status := OKE_API.G_RET_STS_ERROR;
1441 RAISE G_EXCEPTION_HALT_VALIDATION;
1442 END IF;
1443
1444 END IF;
1445
1446 EXCEPTION
1447 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1448 NULL;
1449
1450 WHEN OTHERS THEN
1451 -- store SQL error message on message stack
1452 OKE_API.SET_MESSAGE(
1453 p_app_name =>g_app_name,
1454 p_msg_name =>G_UNEXPECTED_ERROR,
1455 p_token1 =>G_SQLCODE_TOKEN,
1456 p_token1_value =>SQLCODE,
1457 p_token2 =>G_SQLERRM_TOKEN,
1458 p_token2_value =>SQLERRM);
1459 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1460
1461 END validate_oh_rates_final_flag;
1462
1463
1464
1465
1466 PROCEDURE validate_COST_OF_MONEY(x_return_status OUT NOCOPY VARCHAR2,
1467 p_chr_rec IN chr_rec_type)IS
1468
1469 BEGIN
1470 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1471
1472 IF ( p_chr_rec.COST_OF_MONEY <> OKE_API.G_MISS_CHAR
1473 AND p_chr_rec.COST_OF_MONEY IS NOT NULL) THEN
1474 IF (UPPER(p_chr_rec.COST_OF_MONEY) NOT IN ('Y','N')) THEN
1475 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1476 p_msg_name =>G_REQUIRED_VALUE,
1477 p_token1 =>G_COL_NAME_TOKEN,
1478 p_token1_value =>'COST_OF_MONEY');
1479 x_return_status := OKE_API.G_RET_STS_ERROR;
1480 RAISE G_EXCEPTION_HALT_VALIDATION;
1481 END IF;
1482
1483 END IF;
1484
1485 EXCEPTION
1486 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1487 NULL;
1488
1489 WHEN OTHERS THEN
1490 -- store SQL error message on message stack
1491 OKE_API.SET_MESSAGE(
1492 p_app_name =>g_app_name,
1493 p_msg_name =>G_UNEXPECTED_ERROR,
1494 p_token1 =>G_SQLCODE_TOKEN,
1495 p_token1_value =>SQLCODE,
1496 p_token2 =>G_SQLERRM_TOKEN,
1497 p_token2_value =>SQLERRM);
1498 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1499
1500 END validate_COST_OF_MONEY;
1501
1502 PROCEDURE validate_COST_SHARE_FLAG(x_return_status OUT NOCOPY VARCHAR2,
1503 p_chr_rec IN chr_rec_type)IS
1504
1505 BEGIN
1506 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1507
1508 IF ( p_chr_rec.COST_SHARE_FLAG <> OKE_API.G_MISS_CHAR
1509 AND p_chr_rec.COST_SHARE_FLAG IS NOT NULL) THEN
1510 IF (UPPER(p_chr_rec.COST_SHARE_FLAG) NOT IN ('Y','N')) THEN
1511 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1512 p_msg_name =>G_REQUIRED_VALUE,
1513 p_token1 =>G_COL_NAME_TOKEN,
1514 p_token1_value =>'COST_SHARE_FLAG');
1515 x_return_status := OKE_API.G_RET_STS_ERROR;
1516 RAISE G_EXCEPTION_HALT_VALIDATION;
1517 END IF;
1518
1519 END IF;
1520
1521 EXCEPTION
1522 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1523 NULL;
1524
1525 WHEN OTHERS THEN
1526 -- store SQL error message on message stack
1527 OKE_API.SET_MESSAGE(
1528 p_app_name =>g_app_name,
1529 p_msg_name =>G_UNEXPECTED_ERROR,
1530 p_token1 =>G_SQLCODE_TOKEN,
1531 p_token1_value =>SQLCODE,
1532 p_token2 =>G_SQLERRM_TOKEN,
1533 p_token2_value =>SQLERRM);
1534 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1535
1536 END validate_COST_SHARE_FLAG;
1537
1538 PROCEDURE validate_PROGRESS_PAYMENT_FLAG(x_return_status OUT NOCOPY VARCHAR2,
1539 p_chr_rec IN chr_rec_type)IS
1540
1541 BEGIN
1542 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1543
1544 IF ( p_chr_rec.PROGRESS_PAYMENT_FLAG <> OKE_API.G_MISS_CHAR
1545 AND p_chr_rec.PROGRESS_PAYMENT_FLAG IS NOT NULL) THEN
1546 IF (UPPER(p_chr_rec.PROGRESS_PAYMENT_FLAG) NOT IN ('Y','N')) THEN
1547 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1548 p_msg_name =>G_REQUIRED_VALUE,
1549 p_token1 =>G_COL_NAME_TOKEN,
1550 p_token1_value =>'PROGRESS_PAYMENT_FLAG');
1551 x_return_status := OKE_API.G_RET_STS_ERROR;
1552 RAISE G_EXCEPTION_HALT_VALIDATION;
1553 END IF;
1554
1555 END IF;
1556
1557 EXCEPTION
1558 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1559 NULL;
1560
1561 WHEN OTHERS THEN
1562 -- store SQL error message on message stack
1563 OKE_API.SET_MESSAGE(
1564 p_app_name =>g_app_name,
1565 p_msg_name =>G_UNEXPECTED_ERROR,
1566 p_token1 =>G_SQLCODE_TOKEN,
1567 p_token1_value =>SQLCODE,
1568 p_token2 =>G_SQLERRM_TOKEN,
1569 p_token2_value =>SQLERRM);
1570 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1571
1572 END validate_PROGRESS_PAYMENT_FLAG;
1573
1574 PROCEDURE validate_DEFINITIZED_FLAG(x_return_status OUT NOCOPY VARCHAR2,
1575 p_chr_rec IN chr_rec_type)IS
1576
1577 BEGIN
1578 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1579
1580 IF ( p_chr_rec.DEFINITIZED_FLAG <> OKE_API.G_MISS_CHAR
1581 AND p_chr_rec.DEFINITIZED_FLAG IS NOT NULL) THEN
1582 IF (UPPER(p_chr_rec.DEFINITIZED_FLAG) NOT IN ('Y','N')) THEN
1583 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1584 p_msg_name =>G_REQUIRED_VALUE,
1585 p_token1 =>G_COL_NAME_TOKEN,
1586 p_token1_value =>'DEFINITIZED_FLAG');
1587 x_return_status := OKE_API.G_RET_STS_ERROR;
1588 RAISE G_EXCEPTION_HALT_VALIDATION;
1589 END IF;
1590
1591 END IF;
1592
1593 EXCEPTION
1594 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1595 NULL;
1596
1597 WHEN OTHERS THEN
1598 -- store SQL error message on message stack
1599 OKE_API.SET_MESSAGE(
1600 p_app_name =>g_app_name,
1601 p_msg_name =>G_UNEXPECTED_ERROR,
1602 p_token1 =>G_SQLCODE_TOKEN,
1603 p_token1_value =>SQLCODE,
1604 p_token2 =>G_SQLERRM_TOKEN,
1605 p_token2_value =>SQLERRM);
1606 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1607
1608 END validate_DEFINITIZED_FLAG;
1609
1610 PROCEDURE validate_FINANCIAL_CTRL_VERIF(x_return_status OUT NOCOPY VARCHAR2,
1611 p_chr_rec IN chr_rec_type)IS
1612
1613 BEGIN
1614 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1615
1616 IF ( p_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG <> OKE_API.G_MISS_CHAR
1617 AND p_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG IS NOT NULL) THEN
1618 IF (UPPER(p_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG) NOT IN ('Y','N')) THEN
1619 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1620 p_msg_name =>G_REQUIRED_VALUE,
1621 p_token1 =>G_COL_NAME_TOKEN,
1622 p_token1_value =>'FINANCIAL_CTRL_VERIFIED_FLAG');
1623 x_return_status := OKE_API.G_RET_STS_ERROR;
1624 RAISE G_EXCEPTION_HALT_VALIDATION;
1625 END IF;
1626
1627 END IF;
1628
1629 EXCEPTION
1630 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1631 NULL;
1632
1633 WHEN OTHERS THEN
1634 -- store SQL error message on message stack
1635 OKE_API.SET_MESSAGE(
1636 p_app_name =>g_app_name,
1637 p_msg_name =>G_UNEXPECTED_ERROR,
1638 p_token1 =>G_SQLCODE_TOKEN,
1639 p_token1_value =>SQLCODE,
1640 p_token2 =>G_SQLERRM_TOKEN,
1641 p_token2_value =>SQLERRM);
1642 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1643
1644 END validate_FINANCIAL_CTRL_VERIF;
1645
1646
1647 --
1648 -- Derive K_Number_Disp value
1649 --
1650 FUNCTION K_Number_Disp
1651 ( X_K_Header_ID IN NUMBER
1652 , X_BOA_ID IN NUMBER
1653 ) RETURN VARCHAR2
1654 IS
1655
1656 Value1 VARCHAR2(120);
1657 Value2 VARCHAR2(120);
1658
1659 CURSOR c ( C_CHR_ID NUMBER ) IS
1660 SELECT contract_number
1661 FROM okc_k_headers_b
1662 WHERE ID = C_CHR_ID;
1663
1664 BEGIN
1665
1666 OPEN c ( X_K_Header_ID );
1667 FETCH c INTO Value1;
1668 CLOSE c;
1669
1670 IF ( X_BOA_ID IS NOT NULL ) THEN
1671
1672 OPEN c ( X_BOA_ID );
1673 FETCH c INTO Value2;
1674 CLOSE c;
1675
1676 RETURN ( substr(Value2 || '/' || Value1 , 1 , 240) );
1677
1678 ELSE
1679
1680 RETURN ( Value1 );
1681
1682 END IF;
1683
1684 END K_Number_Disp;
1685
1686 /* Bug 7115155 Start*/
1687
1688 PROCEDURE UPDATE_DO(P_BOA_ID IN NUMBER)
1689 IS
1690 L_K_NUMBER_DISP VARCHAR2(120);
1691 CURSOR c_do
1692 IS
1693 SELECT k_header_id
1694 FROM oke_k_headers
1695 WHERE boa_id=P_BOA_ID
1696 FOR UPDATE NOWAIT;
1697 BEGIN
1698
1699 FOR l_do_rec IN c_do
1700 LOOP
1701 L_K_NUMBER_DISP := K_Number_Disp(l_do_rec.k_header_id,p_boa_id);
1702 UPDATE oke_k_headers
1703 SET k_number_disp= L_K_NUMBER_DISP
1704 WHERE CURRENT OF c_do;
1705 END LOOP;
1706
1707 END UPDATE_DO;
1708
1709 /* Bug 7115155 End*/
1710
1711 -- Get record from OKE_K_HEADERS
1712
1713 FUNCTION get_rec(
1714 p_chr_rec IN chr_rec_type,
1715 x_no_data_found OUT NOCOPY BOOLEAN)
1716 RETURN chr_rec_type IS
1717
1718 CURSOR chr_pk_csr(p_id IN NUMBER) IS
1719 SELECT
1720 K_HEADER_ID,
1721 PROGRAM_ID,
1722 PROJECT_ID,
1723 BOA_ID,
1724 K_TYPE_CODE,
1725 PRIORITY_CODE,
1726 PRIME_K_ALIAS,
1727 PRIME_K_NUMBER,
1728 AUTHORIZE_DATE,
1729 AUTHORIZING_REASON,
1730 AWARD_CANCEL_DATE,
1731 AWARD_DATE,
1732 DATE_DEFINITIZED,
1733 DATE_ISSUED,
1734 DATE_NEGOTIATED,
1735 DATE_RECEIVED,
1736 DATE_SIGN_BY_CONTRACTOR,
1737 DATE_SIGN_BY_CUSTOMER,
1738 FAA_APPROVE_DATE,
1739 FAA_REJECT_DATE,
1740 BOOKED_FLAG,
1741 OPEN_FLAG,
1742 CFE_FLAG,
1743 VAT_CODE,
1744 COUNTRY_OF_ORIGIN_CODE,
1745 EXPORT_FLAG,
1746 HUMAN_SUBJECT_FLAG,
1747 CQA_FLAG,
1748 INTERIM_RPT_REQ_FLAG,
1749 NO_COMPETITION_AUTHORIZE,
1750 PENALTY_CLAUSE_FLAG,
1751 PRODUCT_LINE_CODE,
1752 REPORTING_FLAG,
1753 SB_PLAN_REQ_FLAG,
1754 SB_REPORT_FLAG,
1755 NTE_AMOUNT,
1756 NTE_WARNING_FLAG,
1757 BILL_WITHOUT_DEF_FLAG,
1758 CAS_FLAG,
1759 CLASSIFIED_FLAG,
1760 CLIENT_APPROVE_REQ_FLAG,
1761 COST_OF_MONEY,
1762 DCAA_AUDIT_REQ_FLAG,
1763 COST_SHARE_FLAG,
1764 OH_RATES_FINAL_FLAG,
1765 PROGRESS_PAYMENT_FLAG,
1766 PROGRESS_PAYMENT_LIQ_RATE,
1767 PROGRESS_PAYMENT_RATE,
1768 ALTERNATE_LIQUIDATION_RATE,
1769 PROP_DELIVERY_LOCATION,
1770 PROP_DUE_DATE_TIME,
1771 PROP_DUE_TIME,
1772 PROP_EXPIRE_DATE,
1773 COPIES_REQUIRED,
1774 SIC_CODE,
1775 TECH_DATA_WH_RATE,
1776 CREATED_BY,
1777 CREATION_DATE,
1778 LAST_UPDATED_BY,
1779 LAST_UPDATE_LOGIN,
1780 LAST_UPDATE_DATE,
1781 FINANCIAL_CTRL_VERIFIED_FLAG,
1782 DEFINITIZED_FLAG,
1783 COST_OF_SALE_RATE,
1784 LINE_VALUE_TOTAL,
1785 UNDEF_LINE_VALUE_TOTAL,
1786 -- END_DATE,
1787 OWNING_ORGANIZATION_ID,
1788 DEFAULT_TASK_ID
1789 FROM OKE_K_HEADERS
1790 WHERE K_HEADER_ID = p_id;
1791
1792 l_chr_pk chr_pk_csr%ROWTYPE;
1793 l_chr_rec chr_rec_type;
1794 l_temp VARCHAR2(1) := 'Y';
1795 BEGIN
1796 x_no_data_found := TRUE;
1797
1798 -- Get current database values
1799
1800 OPEN chr_pk_csr(p_chr_rec.k_header_id);
1801 FETCH chr_pk_csr INTO
1802
1803 l_chr_rec.K_HEADER_ID,
1804 l_chr_rec.PROGRAM_ID,
1805 l_chr_rec.PROJECT_ID,
1806 l_chr_rec.BOA_ID,
1807 l_chr_rec.K_TYPE_CODE,
1808 l_chr_rec.PRIORITY_CODE,
1809 l_chr_rec.PRIME_K_ALIAS,
1810 l_chr_rec.PRIME_K_NUMBER,
1811 l_chr_rec.AUTHORIZE_DATE,
1812 l_chr_rec.AUTHORIZING_REASON,
1813 l_chr_rec.AWARD_CANCEL_DATE,
1814 l_chr_rec.AWARD_DATE,
1815 l_chr_rec.DATE_DEFINITIZED,
1816 l_chr_rec.DATE_ISSUED,
1817 l_chr_rec.DATE_NEGOTIATED,
1818 l_chr_rec.DATE_RECEIVED,
1819 l_chr_rec.DATE_SIGN_BY_CONTRACTOR,
1820 l_chr_rec.DATE_SIGN_BY_CUSTOMER,
1821 l_chr_rec.FAA_APPROVE_DATE,
1822 l_chr_rec.FAA_REJECT_DATE,
1823 l_chr_rec.BOOKED_FLAG,
1824 l_chr_rec.OPEN_FLAG,
1825 l_chr_rec.CFE_FLAG,
1826 l_chr_rec.VAT_CODE,
1827 l_chr_rec.COUNTRY_OF_ORIGIN_CODE,
1828 l_chr_rec.EXPORT_FLAG,
1829 l_chr_rec.HUMAN_SUBJECT_FLAG,
1830 l_chr_rec.CQA_FLAG,
1831 l_chr_rec.INTERIM_RPT_REQ_FLAG,
1832 l_chr_rec.NO_COMPETITION_AUTHORIZE,
1833 l_chr_rec.PENALTY_CLAUSE_FLAG,
1834 l_chr_rec.PRODUCT_LINE_CODE,
1835 l_chr_rec.REPORTING_FLAG,
1836 l_chr_rec.SB_PLAN_REQ_FLAG,
1837 l_chr_rec.SB_REPORT_FLAG,
1838 l_chr_rec.NTE_AMOUNT,
1839 l_chr_rec.NTE_WARNING_FLAG,
1840 l_chr_rec.BILL_WITHOUT_DEF_FLAG,
1841 l_chr_rec.CAS_FLAG,
1842 l_chr_rec.CLASSIFIED_FLAG,
1843 l_chr_rec.CLIENT_APPROVE_REQ_FLAG,
1844 l_chr_rec.COST_OF_MONEY,
1845 l_chr_rec.DCAA_AUDIT_REQ_FLAG,
1846 l_chr_rec.COST_SHARE_FLAG,
1847 l_chr_rec.OH_RATES_FINAL_FLAG,
1848 l_chr_rec.PROGRESS_PAYMENT_FLAG,
1849 l_chr_rec.PROGRESS_PAYMENT_LIQ_RATE,
1850 l_chr_rec.PROGRESS_PAYMENT_RATE,
1851 l_chr_rec.ALTERNATE_LIQUIDATION_RATE,
1852 l_chr_rec.PROP_DELIVERY_LOCATION,
1853 l_chr_rec.PROP_DUE_DATE_TIME,
1854 l_chr_rec.PROP_DUE_TIME,
1855 l_chr_rec.PROP_EXPIRE_DATE,
1856 l_chr_rec.COPIES_REQUIRED,
1857 l_chr_rec.SIC_CODE,
1858 l_chr_rec.TECH_DATA_WH_RATE,
1859 l_chr_rec.CREATED_BY,
1860 l_chr_rec.CREATION_DATE,
1861 l_chr_rec.LAST_UPDATED_BY,
1862 l_chr_rec.LAST_UPDATE_LOGIN,
1863 l_chr_rec.LAST_UPDATE_DATE,
1864 l_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG,
1865 l_chr_rec.DEFINITIZED_FLAG,
1866 l_chr_rec.COST_OF_SALE_RATE,
1867 l_chr_rec.LINE_VALUE_TOTAL,
1868 l_chr_rec.UNDEF_LINE_VALUE_TOTAL,
1869 -- l_chr_rec.END_DATE,
1870 l_chr_rec.OWNING_ORGANIZATION_ID,
1871 l_chr_rec.DEFAULT_TASK_ID;
1872
1873
1874 x_no_data_found := chr_pk_csr%NOTFOUND;
1875
1876 close chr_pk_csr;
1877
1878 IF x_no_data_found = TRUE THEN
1879 l_temp := 'N';
1880 END IF;
1881
1882 RETURN(l_chr_rec);
1883
1884 END get_rec;
1885
1886 FUNCTION get_rec(
1887
1888 p_chr_rec IN chr_rec_type)
1889 RETURN chr_rec_type IS
1890
1891 l_row_notfound BOOLEAN := TRUE;
1892
1893 BEGIN
1894
1895 RETURN(get_rec(p_chr_rec, l_row_notfound));
1896
1897 END get_rec;
1898
1899 FUNCTION null_out_defaults(
1900 p_chr_rec IN chr_rec_type) RETURN chr_rec_type IS
1901 x_chr_rec chr_rec_type := p_chr_rec;
1902
1903 BEGIN
1904
1905 IF x_chr_rec.PROGRAM_ID = OKE_API.G_MISS_NUM THEN
1906 x_chr_rec.PROGRAM_ID := NULL;
1907 END IF;
1908
1909 IF (x_chr_rec.PROJECT_ID = OKE_API.G_MISS_NUM) THEN
1910 x_chr_rec.PROJECT_ID := NULL;
1911 END IF;
1912
1913
1914 IF (x_chr_rec.BOA_ID = OKE_API.G_MISS_NUM) THEN
1915 x_chr_rec.BOA_ID := NULL;
1916 END IF;
1917
1918 IF (x_chr_rec.K_TYPE_CODE = OKE_API.G_MISS_CHAR) THEN
1919 x_chr_rec.K_TYPE_CODE := NULL;
1920 END IF;
1921
1922 IF (x_chr_rec.PRIORITY_CODE = OKE_API.G_MISS_CHAR) THEN
1923 x_chr_rec.PRIORITY_CODE := NULL;
1924 END IF;
1925
1926 IF (x_chr_rec.PRIME_K_ALIAS = OKE_API.G_MISS_CHAR) THEN
1927 x_chr_rec.PRIME_K_ALIAS := NULL;
1928 END IF;
1929
1930 IF (x_chr_rec.PRIME_K_NUMBER = OKE_API.G_MISS_CHAR) THEN
1931 x_chr_rec.PRIME_K_NUMBER := NULL;
1932 END IF;
1933
1934 IF (x_chr_rec.AUTHORIZE_DATE = OKE_API.G_MISS_DATE) THEN
1935 x_chr_rec.AUTHORIZE_DATE := NULL;
1936 END IF;
1937
1938 IF (x_chr_rec.AUTHORIZING_REASON = OKE_API.G_MISS_CHAR) THEN
1939 x_chr_rec.AUTHORIZING_REASON := NULL;
1940 END IF;
1941
1942 IF (x_chr_rec.AWARD_CANCEL_DATE = OKE_API.G_MISS_DATE) THEN
1943 x_chr_rec.AWARD_CANCEL_DATE := NULL;
1944 END IF;
1945
1946 IF (x_chr_rec.AWARD_DATE = OKE_API.G_MISS_DATE) THEN
1947 x_chr_rec.AWARD_DATE := NULL;
1948 END IF;
1949
1950 IF (x_chr_rec.DATE_DEFINITIZED = OKE_API.G_MISS_DATE) THEN
1951 x_chr_rec.DATE_DEFINITIZED := NULL;
1952 END IF;
1953
1954 IF (x_chr_rec.DATE_ISSUED = OKE_API.G_MISS_DATE) THEN
1955 x_chr_rec.DATE_ISSUED := NULL;
1956 END IF;
1957
1958 IF (x_chr_rec.DATE_NEGOTIATED = OKE_API.G_MISS_DATE) THEN
1959 x_chr_rec.DATE_NEGOTIATED := NULL;
1960 END IF;
1961
1962 IF (x_chr_rec.DATE_RECEIVED = OKE_API.G_MISS_DATE) THEN
1963 x_chr_rec.DATE_RECEIVED := NULL;
1964 END IF;
1965
1966 IF (x_chr_rec.DATE_SIGN_BY_CONTRACTOR = OKE_API.G_MISS_DATE) THEN
1967 x_chr_rec.DATE_SIGN_BY_CONTRACTOR := NULL;
1968 END IF;
1969
1970 IF (x_chr_rec.FAA_APPROVE_DATE = OKE_API.G_MISS_DATE) THEN
1971 x_chr_rec.FAA_APPROVE_DATE := NULL;
1972 END IF;
1973
1974 IF (x_chr_rec.FAA_REJECT_DATE = OKE_API.G_MISS_DATE) THEN
1975 x_chr_rec.FAA_REJECT_DATE := NULL;
1976 END IF;
1977
1978 IF (x_chr_rec.BOOKED_FLAG = OKE_API.G_MISS_CHAR) THEN
1979 x_chr_rec.BOOKED_FLAG := NULL;
1980 END IF;
1981
1982 IF (x_chr_rec.OPEN_FLAG = OKE_API.G_MISS_CHAR) THEN
1983 x_chr_rec.OPEN_FLAG := NULL;
1984 END IF;
1985
1986 IF (x_chr_rec.CFE_FLAG = OKE_API.G_MISS_CHAR) THEN
1987 x_chr_rec.CFE_FLAG := NULL;
1988 END IF;
1989
1990 IF (x_chr_rec.VAT_CODE = OKE_API.G_MISS_CHAR) THEN
1991 x_chr_rec.VAT_CODE := NULL;
1992 END IF;
1993
1994 IF (x_chr_rec.COUNTRY_OF_ORIGIN_CODE = OKE_API.G_MISS_CHAR) THEN
1995 x_chr_rec.COUNTRY_OF_ORIGIN_CODE := NULL;
1996 END IF;
1997
1998 IF (x_chr_rec.EXPORT_FLAG = OKE_API.G_MISS_CHAR) THEN
1999 x_chr_rec.EXPORT_FLAG := NULL;
2000 END IF;
2001
2002 IF (x_chr_rec.HUMAN_SUBJECT_FLAG = OKE_API.G_MISS_CHAR) THEN
2003 x_chr_rec.HUMAN_SUBJECT_FLAG := NULL;
2004 END IF;
2005
2006 IF (x_chr_rec.CQA_FLAG = OKE_API.G_MISS_CHAR) THEN
2007 x_chr_rec.CQA_FLAG := NULL;
2008 END IF;
2009
2010 IF (x_chr_rec.INTERIM_RPT_REQ_FLAG = OKE_API.G_MISS_CHAR) THEN
2011 x_chr_rec.INTERIM_RPT_REQ_FLAG := NULL;
2012 END IF;
2013
2014 IF (x_chr_rec.NO_COMPETITION_AUTHORIZE = OKE_API.G_MISS_CHAR) THEN
2015 x_chr_rec.NO_COMPETITION_AUTHORIZE := NULL;
2016 END IF;
2017
2018 IF (x_chr_rec.PENALTY_CLAUSE_FLAG = OKE_API.G_MISS_CHAR) THEN
2019 x_chr_rec.PENALTY_CLAUSE_FLAG := NULL;
2020 END IF;
2021
2022 IF (x_chr_rec.PRODUCT_LINE_CODE = OKE_API.G_MISS_CHAR) THEN
2023 x_chr_rec.PRODUCT_LINE_CODE := NULL;
2024 END IF;
2025
2026 IF (x_chr_rec.REPORTING_FLAG = OKE_API.G_MISS_CHAR) THEN
2027 x_chr_rec.REPORTING_FLAG := NULL;
2028 END IF;
2029
2030 IF (x_chr_rec.SB_PLAN_REQ_FLAG = OKE_API.G_MISS_CHAR) THEN
2031 x_chr_rec.SB_PLAN_REQ_FLAG := NULL;
2032 END IF;
2033
2034 IF (x_chr_rec.SB_REPORT_FLAG = OKE_API.G_MISS_CHAR) THEN
2035 x_chr_rec.SB_REPORT_FLAG := NULL;
2036 END IF;
2037
2038 IF (x_chr_rec.NTE_AMOUNT = OKE_API.G_MISS_NUM) THEN
2039 x_chr_rec.NTE_AMOUNT := NULL;
2040 END IF;
2041
2042 IF (x_chr_rec.NTE_WARNING_FLAG = OKE_API.G_MISS_CHAR) THEN
2043 x_chr_rec.NTE_WARNING_FLAG := NULL;
2044 END IF;
2045
2046 IF (x_chr_rec.BILL_WITHOUT_DEF_FLAG = OKE_API.G_MISS_CHAR) THEN
2047 x_chr_rec.BILL_WITHOUT_DEF_FLAG := NULL;
2048 END IF;
2049
2050 IF (x_chr_rec.CAS_FLAG = OKE_API.G_MISS_CHAR) THEN
2051 x_chr_rec.CAS_FLAG := NULL;
2052 END IF;
2053
2054 IF (x_chr_rec.CLASSIFIED_FLAG = OKE_API.G_MISS_CHAR) THEN
2055 x_chr_rec.CLASSIFIED_FLAG := NULL;
2056 END IF;
2057
2058 IF (x_chr_rec.CLIENT_APPROVE_REQ_FLAG = OKE_API.G_MISS_CHAR) THEN
2059 x_chr_rec.CLIENT_APPROVE_REQ_FLAG := NULL;
2060 END IF;
2061
2062 IF (x_chr_rec.COST_OF_MONEY = OKE_API.G_MISS_CHAR) THEN
2063 x_chr_rec.COST_OF_MONEY := NULL;
2064 END IF;
2065
2066 IF (x_chr_rec.DCAA_AUDIT_REQ_FLAG = OKE_API.G_MISS_CHAR) THEN
2067 x_chr_rec.DCAA_AUDIT_REQ_FLAG := NULL;
2068 END IF;
2069
2070 IF (x_chr_rec.COST_SHARE_FLAG = OKE_API.G_MISS_CHAR) THEN
2071 x_chr_rec.COST_SHARE_FLAG := NULL;
2072 END IF;
2073
2074 IF (x_chr_rec.OH_RATES_FINAL_FLAG = OKE_API.G_MISS_CHAR) THEN
2075 x_chr_rec.OH_RATES_FINAL_FLAG := NULL;
2076 END IF;
2077
2078 IF (x_chr_rec.PROGRESS_PAYMENT_FLAG = OKE_API.G_MISS_CHAR) THEN
2079 x_chr_rec.PROGRESS_PAYMENT_FLAG := NULL;
2080 END IF;
2081
2082 IF (x_chr_rec.PROGRESS_PAYMENT_LIQ_RATE = OKE_API.G_MISS_NUM) THEN
2083 x_chr_rec.PROGRESS_PAYMENT_LIQ_RATE := NULL;
2084 END IF;
2085
2086 IF (x_chr_rec.PROGRESS_PAYMENT_RATE = OKE_API.G_MISS_NUM) THEN
2087 x_chr_rec.PROGRESS_PAYMENT_RATE := NULL;
2088 END IF;
2089
2090 IF (x_chr_rec.ALTERNATE_LIQUIDATION_RATE = OKE_API.G_MISS_NUM) THEN
2091 x_chr_rec.ALTERNATE_LIQUIDATION_RATE := NULL;
2092 END IF;
2093
2094 IF (x_chr_rec.PROP_DELIVERY_LOCATION = OKE_API.G_MISS_CHAR) THEN
2095 x_chr_rec.PROP_DELIVERY_LOCATION := NULL;
2096 END IF;
2097
2098 IF (x_chr_rec.PROP_DUE_DATE_TIME = OKE_API.G_MISS_DATE) THEN
2099 x_chr_rec.PROP_DUE_DATE_TIME := NULL;
2100 END IF;
2101
2102 IF (x_chr_rec.PROP_DUE_TIME = OKE_API.G_MISS_CHAR) THEN
2103 x_chr_rec.PROP_DUE_TIME := NULL;
2104 END IF;
2105
2106 IF (x_chr_rec.PROP_EXPIRE_DATE = OKE_API.G_MISS_DATE) THEN
2107 x_chr_rec.PROP_EXPIRE_DATE := NULL;
2108 END IF;
2109
2110 IF (x_chr_rec.COPIES_REQUIRED = OKE_API.G_MISS_NUM) THEN
2111 x_chr_rec.COPIES_REQUIRED := NULL;
2112 END IF;
2113
2114 IF (x_chr_rec.SIC_CODE = OKE_API.G_MISS_CHAR) THEN
2115 x_chr_rec.SIC_CODE := NULL;
2116 END IF;
2117
2118 IF (x_chr_rec.TECH_DATA_WH_RATE = OKE_API.G_MISS_NUM) THEN
2119 x_chr_rec.TECH_DATA_WH_RATE := NULL;
2120 END IF;
2121
2122
2123 IF (x_chr_rec.CREATED_BY = OKE_API.G_MISS_NUM) THEN
2124 x_chr_rec.CREATED_BY := NULL;
2125 END IF;
2126
2127 IF (x_chr_rec.CREATION_DATE = OKE_API.G_MISS_DATE) THEN
2128 x_chr_rec.CREATION_DATE := NULL;
2129 END IF;
2130
2131 IF (x_chr_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM) THEN
2132 x_chr_rec.LAST_UPDATED_BY := NULL;
2133 END IF;
2134
2135 IF (x_chr_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE) THEN
2136 x_chr_rec.LAST_UPDATE_DATE := NULL;
2137 END IF;
2138
2139 IF (x_chr_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM) THEN
2140 x_chr_rec.LAST_UPDATE_LOGIN := NULL;
2141 END IF;
2142
2143 IF (x_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG = OKE_API.G_MISS_CHAR) THEN
2144 x_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG := NULL;
2145 END IF;
2146
2147 IF (x_chr_rec.DEFINITIZED_FLAG = OKE_API.G_MISS_CHAR) THEN
2148 x_chr_rec.DEFINITIZED_FLAG := NULL;
2149 END IF;
2150
2151 IF (x_chr_rec.COST_OF_SALE_RATE = OKE_API.G_MISS_NUM) THEN
2152 x_chr_rec.COST_OF_SALE_RATE := NULL;
2153 END IF;
2154
2155 IF (x_chr_rec.LINE_VALUE_TOTAL = OKE_API.G_MISS_NUM) THEN
2156 x_chr_rec.LINE_VALUE_TOTAL := NULL;
2157 END IF;
2158
2159 IF (x_chr_rec.UNDEF_LINE_VALUE_TOTAL = OKE_API.G_MISS_NUM) THEN
2160 x_chr_rec.UNDEF_LINE_VALUE_TOTAL := NULL;
2161 END IF;
2162
2163 -- IF (x_chr_rec.END_DATE = OKE_API.G_MISS_DATE) THEN
2164 -- x_chr_rec.END_DATE := NULL;
2165 -- END IF;
2166
2167 IF (x_chr_rec.OWNING_ORGANIZATION_ID = OKE_API.G_MISS_NUM) THEN
2168 x_chr_rec.OWNING_ORGANIZATION_ID := NULL;
2169 END IF;
2170
2171 IF (x_chr_rec.DEFAULT_TASK_ID = OKE_API.G_MISS_NUM) THEN
2172 x_chr_rec.DEFAULT_TASK_ID := NULL;
2173 END IF;
2174
2175 RETURN(x_chr_rec);
2176
2177 END null_out_defaults;
2178
2179
2180
2181 FUNCTION validate_attributes(
2182 p_chr_rec IN chr_rec_type)
2183 RETURN VARCHAR2 IS
2184
2185 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2186 x_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2187
2188 BEGIN
2189
2190 validate_program_id (x_return_status => l_return_status,
2191 p_chr_rec => p_chr_rec);
2192 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2193 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2194 x_return_status := l_return_status;
2195 END IF;
2196 END IF;
2197
2198
2199 validate_boa_id (x_return_status => l_return_status,
2200 p_chr_rec => p_chr_rec);
2201
2202 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2203 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2204 x_return_status := l_return_status;
2205 END IF;
2206 END IF;
2207
2208 validate_project_id (x_return_status => l_return_status,
2209 p_chr_rec => p_chr_rec);
2210
2211 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2212 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2213 x_return_status := l_return_status;
2214 END IF;
2215 END IF;
2216
2217
2218 validate_k_type_code(x_return_status => l_return_status,
2219 p_chr_rec => p_chr_rec);
2220
2221 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2222 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2223 x_return_status := l_return_status;
2224 END IF;
2225 END IF;
2226
2227 validate_priority_code(x_return_status => l_return_status,
2228 p_chr_rec => p_chr_rec);
2229
2230 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2231 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2232 x_return_status := l_return_status;
2233 END IF;
2234 END IF;
2235
2236 validate_product_code(x_return_status => l_return_status,
2237 p_chr_rec => p_chr_rec);
2238
2239 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2240 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2241 x_return_status := l_return_status;
2242 END IF;
2243 END IF;
2244
2245 validate_country_code(x_return_status => l_return_status,
2246 p_chr_rec => p_chr_rec);
2247
2248 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2249 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2250 x_return_status := l_return_status;
2251 END IF;
2252 END IF;
2253
2254 validate_vat_code(x_return_status => l_return_status,
2255 p_chr_rec => p_chr_rec);
2256
2257 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2258 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2259 x_return_status := l_return_status;
2260 END IF;
2261 END IF;
2262
2263
2264
2265
2266 validate_prime_k_alias(x_return_status => l_return_status,
2267 p_chr_rec => p_chr_rec);
2268
2269 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2270 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2271 x_return_status := l_return_status;
2272 END IF;
2273 END IF;
2274
2275
2276
2277 validate_prime_k_number(x_return_status => l_return_status,
2278 p_chr_rec => p_chr_rec);
2279
2280 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2281 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2282 x_return_status := l_return_status;
2283 END IF;
2284 END IF;
2285
2286 validate_authorizing_reason(x_return_status => l_return_status,
2287 p_chr_rec => p_chr_rec);
2288
2289 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2290 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2291 x_return_status := l_return_status;
2292 END IF;
2293 END IF;
2294
2295
2296 validate_award_cancel_date(x_return_status => l_return_status,
2297 p_chr_rec => p_chr_rec);
2298
2299 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2300 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2301 x_return_status := l_return_status;
2302 END IF;
2303 END IF;
2304
2305 validate_date_definitized(x_return_status => l_return_status,
2306 p_chr_rec => p_chr_rec);
2307
2308 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2309 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2310 x_return_status := l_return_status;
2311 END IF;
2312 END IF;
2313
2314 validate_date_received(x_return_status => l_return_status,
2315 p_chr_rec => p_chr_rec);
2316
2317 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2318 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2319 x_return_status := l_return_status;
2320 END IF;
2321
2322 END IF;
2323
2324 validate_booked_flag(x_return_status => l_return_status,
2325 p_chr_rec => p_chr_rec);
2326
2327 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2328 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2329 x_return_status := l_return_status;
2330 END IF;
2331
2332 END IF;
2333
2334 validate_open_flag(x_return_status => l_return_status,
2335 p_chr_rec => p_chr_rec);
2336
2337 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2338 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2339 x_return_status := l_return_status;
2340 END IF;
2341
2342 END IF;
2343
2344 validate_cfe_flag(x_return_status => l_return_status,
2345 p_chr_rec => p_chr_rec);
2346
2347 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2348 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2349 x_return_status := l_return_status;
2350 END IF;
2351 END IF;
2352
2353
2354 validate_export_flag(x_return_status => l_return_status,
2355 p_chr_rec => p_chr_rec);
2356
2357 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2358 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2359 x_return_status := l_return_status;
2360 END IF;
2361 END IF;
2362
2363 validate_human_subject_flag(x_return_status => l_return_status,
2364 p_chr_rec => p_chr_rec);
2365
2366 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2367 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2368 x_return_status := l_return_status;
2369 END IF;
2370 END IF;
2371
2372 validate_cqa_flag(x_return_status => l_return_status,
2373 p_chr_rec => p_chr_rec);
2374
2375 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2376 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2377 x_return_status := l_return_status;
2378 END IF;
2379 END IF;
2380
2381 validate_interim_rpt_req_flag(x_return_status => l_return_status,
2382 p_chr_rec => p_chr_rec);
2383
2384 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2385 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2386 x_return_status := l_return_status;
2387 END IF;
2388 END IF;
2389
2390 validate_penalty_clause_flag(x_return_status => l_return_status,
2391 p_chr_rec => p_chr_rec);
2392
2393 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2394 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2395 x_return_status := l_return_status;
2396 END IF;
2397 END IF;
2398
2399 validate_reporting_flag(x_return_status => l_return_status,
2400 p_chr_rec => p_chr_rec);
2401
2402 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2403 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2404 x_return_status := l_return_status;
2405 END IF;
2406 END IF;
2407
2408 validate_sb_plan_req_flag(x_return_status => l_return_status,
2409 p_chr_rec => p_chr_rec);
2410
2411 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2412 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2413 x_return_status := l_return_status;
2414 END IF;
2415 END IF;
2416
2417 validate_sb_report_flag(x_return_status => l_return_status,
2418 p_chr_rec => p_chr_rec);
2419
2420 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2421 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2422 x_return_status := l_return_status;
2423 END IF;
2424 END IF;
2425
2426 validate_nte_warning_flag(x_return_status => l_return_status,
2427 p_chr_rec => p_chr_rec);
2428
2429 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2430 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2431 x_return_status := l_return_status;
2432 END IF;
2433 END IF;
2434
2435 validate_bill_without_def_flag(x_return_status => l_return_status,
2436 p_chr_rec => p_chr_rec);
2437
2438 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2439 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2440 x_return_status := l_return_status;
2441 END IF;
2442 END IF;
2443
2444 validate_cas_flag(x_return_status => l_return_status,
2445 p_chr_rec => p_chr_rec);
2446
2447 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2448 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2449 x_return_status := l_return_status;
2450 END IF;
2451 END IF;
2452
2453 validate_classified_flag(x_return_status => l_return_status,
2454 p_chr_rec => p_chr_rec);
2455
2456 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2457 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2458 x_return_status := l_return_status;
2459 END IF;
2460 END IF;
2461
2462 validate_client_approve_req(x_return_status => l_return_status,
2463 p_chr_rec => p_chr_rec);
2464
2465 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2466 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2467 x_return_status := l_return_status;
2468 END IF;
2469 END IF;
2470
2471 validate_dcaa_audit_req_flag(x_return_status => l_return_status,
2472 p_chr_rec => p_chr_rec);
2473
2474 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2475 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2476 x_return_status := l_return_status;
2477 END IF;
2478 END IF;
2479
2480 validate_client_approve_req(x_return_status => l_return_status,
2481 p_chr_rec => p_chr_rec);
2482
2483 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2484 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2485 x_return_status := l_return_status;
2486 END IF;
2487 END IF;
2488
2489 validate_OH_RATES_FINAL_FLAG(x_return_status => l_return_status,
2490 p_chr_rec => p_chr_rec);
2491
2492 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2493 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2494 x_return_status := l_return_status;
2495 END IF;
2496 END IF;
2497
2498 RETURN(x_return_status);
2499
2500 END validate_attributes;
2501
2502 FUNCTION validate_record(
2503 p_chr_rec IN chr_rec_type) RETURN VARCHAR2 IS
2504
2505 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2506
2507 BEGIN
2508 RETURN(l_return_status);
2509
2510 END validate_record;
2511
2512 -- Insert row into OKE_K_HEADERS
2513
2514 PROCEDURE insert_row(
2515 p_api_version IN NUMBER,
2516 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2517 x_return_status OUT NOCOPY VARCHAR2,
2518 x_msg_count OUT NOCOPY NUMBER,
2519 x_msg_data OUT NOCOPY VARCHAR2,
2520 p_chr_rec IN chr_rec_type,
2521 x_chr_rec OUT NOCOPY chr_rec_type)IS
2522
2523 l_api_version CONSTANT NUMBER :=1;
2524 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2525 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2526 l_chr_rec chr_rec_type := p_chr_rec;
2527 l_def_chr_rec chr_rec_type;
2528
2529 l_k_number_disp VARCHAR2(120);
2530
2531 FUNCTION fill_who_column(
2532 p_chr_rec IN chr_rec_type) RETURN chr_rec_type IS
2533
2534 l_chr_rec chr_rec_type := p_chr_rec;
2535
2536 BEGIN
2537
2538 l_chr_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2539 l_chr_rec.CREATION_DATE := SYSDATE;
2540 l_chr_rec.LAST_UPDATE_DATE := SYSDATE;
2541 l_chr_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2542 l_chr_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2543
2544 RETURN(l_chr_rec);
2545
2546 END fill_who_column;
2547
2548 --Set Attributes for OKE_K_HEADERS
2549
2550 FUNCTION set_attributes(
2551 p_chr_rec IN chr_rec_type,
2552 x_chr_rec OUT NOCOPY chr_rec_type) RETURN VARCHAR2 IS
2553
2554 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2555
2556 BEGIN
2557 x_chr_rec := p_chr_rec;
2558
2559
2560
2561 x_chr_rec.BOOKED_FLAG := UPPER(x_chr_rec.BOOKED_FLAG);
2562 x_chr_rec.OPEN_FLAG := UPPER(x_chr_rec.OPEN_FLAG);
2563 x_chr_rec.CFE_FLAG := UPPER(x_chr_rec.CFE_FLAG);
2564 x_chr_rec.EXPORT_FLAG := UPPER(x_chr_rec.EXPORT_FLAG);
2565 x_chr_rec.HUMAN_SUBJECT_FLAG := UPPER(x_chr_rec.HUMAN_SUBJECT_FLAG);
2566 x_chr_rec.CQA_FLAG := UPPER(x_chr_rec.CQA_FLAG);
2567 x_chr_rec.INTERIM_RPT_REQ_FLAG := UPPER(x_chr_rec.INTERIM_RPT_REQ_FLAG);
2568 x_chr_rec.PENALTY_CLAUSE_FLAG := UPPER(x_chr_rec.PENALTY_CLAUSE_FLAG);
2569 x_chr_rec.REPORTING_FLAG := UPPER(x_chr_rec.REPORTING_FLAG);
2570 x_chr_rec.NTE_WARNING_FLAG := UPPER(x_chr_rec.NTE_WARNING_FLAG);
2571 x_chr_rec.BILL_WITHOUT_DEF_FLAG := UPPER(x_chr_rec.BILL_WITHOUT_DEF_FLAG);
2572 x_chr_rec.CAS_FLAG := UPPER(x_chr_rec.CAS_FLAG);
2573 x_chr_rec.CLASSIFIED_FLAG := UPPER(x_chr_rec.CLASSIFIED_FLAG);
2574 x_chr_rec.CLIENT_APPROVE_REQ_FLAG := UPPER(x_chr_rec.CLIENT_APPROVE_REQ_FLAG);
2575 x_chr_rec.DCAA_AUDIT_REQ_FLAG := UPPER(x_chr_rec.DCAA_AUDIT_REQ_FLAG);
2576 x_chr_rec.COST_SHARE_FLAG := UPPER(x_chr_rec.COST_SHARE_FLAG);
2577 x_chr_rec.OH_RATES_FINAL_FLAG := UPPER(x_chr_rec.OH_RATES_FINAL_FLAG);
2578 x_chr_rec.PROGRESS_PAYMENT_FLAG := UPPER(x_chr_rec.PROGRESS_PAYMENT_FLAG);
2579 x_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG := UPPER(x_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG);
2580 x_chr_rec.DEFINITIZED_FLAG := UPPER(x_chr_rec.DEFINITIZED_FLAG);
2581
2582
2583 RETURN(l_return_status);
2584
2585 END set_attributes;
2586
2587 BEGIN
2588
2589 l_return_status := OKE_API.START_ACTIVITY(
2590 l_api_name,
2591 p_init_msg_list,
2592 '_PVT',
2593 x_return_status);
2594
2595 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2596
2597 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2598
2599 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2600
2601 RAISE OKE_API.G_EXCEPTION_ERROR;
2602
2603 END IF;
2604
2605
2606 l_chr_rec := null_out_defaults(p_chr_rec);
2607
2608 -- Get primary key
2609 -- l_chr_rec.K_HEADER_ID := get_id;
2610
2611
2612 l_return_status := set_attributes(l_chr_rec, l_def_chr_rec);
2613
2614
2615
2616 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2617
2618 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2619
2620 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2621
2622 RAISE OKE_API.G_EXCEPTION_ERROR;
2623
2624 END IF;
2625
2626 l_def_chr_rec := fill_who_column(l_def_chr_rec);
2627
2628 -- Validate all non-missing attributes(Item level validation)
2629 l_return_status := validate_attributes(l_def_chr_rec);
2630
2631 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2632
2633 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2634
2635 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2636
2637 RAISE OKE_API.G_EXCEPTION_ERROR;
2638
2639 END IF;
2640
2641
2642 l_return_status := validate_record(l_def_chr_rec);
2643
2644 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2645
2646 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2647
2648 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2649
2650 RAISE OKE_API.G_EXCEPTION_ERROR;
2651
2652 END IF;
2653
2654 l_k_number_disp := K_Number_Disp( l_def_chr_rec.K_HEADER_ID
2655 , l_def_chr_rec.BOA_ID );
2656
2657
2658 INSERT INTO OKE_K_HEADERS(
2659
2660 K_HEADER_ID,
2661 PROGRAM_ID,
2662 PROJECT_ID,
2663 BOA_ID,
2664 K_NUMBER_DISP,
2665 K_TYPE_CODE,
2666 PRIORITY_CODE,
2667 PRIME_K_ALIAS,
2668 PRIME_K_NUMBER,
2669 AUTHORIZE_DATE,
2670 AUTHORIZING_REASON,
2671 AWARD_CANCEL_DATE,
2672 AWARD_DATE,
2673 DATE_DEFINITIZED,
2674 DATE_ISSUED,
2675 DATE_NEGOTIATED,
2676 DATE_RECEIVED,
2677 DATE_SIGN_BY_CONTRACTOR,
2678 DATE_SIGN_BY_CUSTOMER,
2679 FAA_APPROVE_DATE,
2680 FAA_REJECT_DATE,
2681 BOOKED_FLAG,
2682 OPEN_FLAG,
2683 CFE_FLAG,
2684 VAT_CODE,
2685 COUNTRY_OF_ORIGIN_CODE,
2686 EXPORT_FLAG,
2687 HUMAN_SUBJECT_FLAG,
2688 CQA_FLAG,
2689 INTERIM_RPT_REQ_FLAG,
2690 NO_COMPETITION_AUTHORIZE,
2691 PENALTY_CLAUSE_FLAG,
2692 PRODUCT_LINE_CODE,
2693 REPORTING_FLAG,
2694 SB_PLAN_REQ_FLAG,
2695 SB_REPORT_FLAG,
2696 NTE_AMOUNT,
2697 NTE_WARNING_FLAG,
2698 BILL_WITHOUT_DEF_FLAG,
2699 CAS_FLAG,
2700 CLASSIFIED_FLAG,
2701 CLIENT_APPROVE_REQ_FLAG,
2702 COST_OF_MONEY,
2703 DCAA_AUDIT_REQ_FLAG,
2704 COST_SHARE_FLAG,
2705 OH_RATES_FINAL_FLAG,
2706 PROGRESS_PAYMENT_FLAG,
2707 PROGRESS_PAYMENT_LIQ_RATE,
2708 PROGRESS_PAYMENT_RATE,
2709 ALTERNATE_LIQUIDATION_RATE,
2710 PROP_DELIVERY_LOCATION,
2711 PROP_DUE_DATE_TIME,
2712 PROP_DUE_TIME,
2713 PROP_EXPIRE_DATE,
2714 COPIES_REQUIRED,
2715 SIC_CODE,
2716 TECH_DATA_WH_RATE,
2717 CREATED_BY,
2718 CREATION_DATE,
2719 LAST_UPDATED_BY,
2720 LAST_UPDATE_LOGIN,
2721 LAST_UPDATE_DATE,
2722 FINANCIAL_CTRL_VERIFIED_FLAG,
2723 DEFINITIZED_FLAG,
2724 COST_OF_SALE_RATE,
2725 LINE_VALUE_TOTAL,
2726 UNDEF_LINE_VALUE_TOTAL,
2727 -- END_DATE,
2728 OWNING_ORGANIZATION_ID,
2729 DEFAULT_TASK_ID
2730 )
2731 VALUES(
2732 l_def_chr_rec.K_HEADER_ID,
2733 l_def_chr_rec.PROGRAM_ID,
2734 l_def_chr_rec.PROJECT_ID,
2735 l_def_chr_rec.BOA_ID,
2736 l_k_number_disp,
2737 l_def_chr_rec.K_TYPE_CODE,
2738 l_def_chr_rec.PRIORITY_CODE,
2739 l_def_chr_rec.PRIME_K_ALIAS,
2740 l_def_chr_rec.PRIME_K_NUMBER,
2741 l_def_chr_rec.AUTHORIZE_DATE,
2742 l_def_chr_rec.AUTHORIZING_REASON,
2743 l_def_chr_rec.AWARD_CANCEL_DATE,
2744 l_def_chr_rec.AWARD_DATE,
2745 l_def_chr_rec.DATE_DEFINITIZED,
2746 l_def_chr_rec.DATE_ISSUED,
2747 l_def_chr_rec.DATE_NEGOTIATED,
2748 l_def_chr_rec.DATE_RECEIVED,
2749 l_def_chr_rec.DATE_SIGN_BY_CONTRACTOR,
2750 l_def_chr_rec.DATE_SIGN_BY_CUSTOMER,
2751 l_def_chr_rec.FAA_APPROVE_DATE,
2752 l_def_chr_rec.FAA_REJECT_DATE,
2753 l_def_chr_rec.BOOKED_FLAG,
2754 l_def_chr_rec.OPEN_FLAG,
2755 l_def_chr_rec.CFE_FLAG,
2756 l_def_chr_rec.VAT_CODE,
2757 l_def_chr_rec.COUNTRY_OF_ORIGIN_CODE,
2758 l_def_chr_rec.EXPORT_FLAG,
2759 l_def_chr_rec.HUMAN_SUBJECT_FLAG,
2760 l_def_chr_rec.CQA_FLAG,
2761 l_def_chr_rec.INTERIM_RPT_REQ_FLAG,
2762 l_def_chr_rec.NO_COMPETITION_AUTHORIZE,
2763 l_def_chr_rec.PENALTY_CLAUSE_FLAG,
2764 l_def_chr_rec.PRODUCT_LINE_CODE,
2765 l_def_chr_rec.REPORTING_FLAG,
2766 l_def_chr_rec.SB_PLAN_REQ_FLAG,
2767 l_def_chr_rec.SB_REPORT_FLAG,
2768 l_def_chr_rec.NTE_AMOUNT,
2769 l_def_chr_rec.NTE_WARNING_FLAG,
2770 l_def_chr_rec.BILL_WITHOUT_DEF_FLAG,
2771 l_def_chr_rec.CAS_FLAG,
2772 l_def_chr_rec.CLASSIFIED_FLAG,
2773 l_def_chr_rec.CLIENT_APPROVE_REQ_FLAG,
2774 l_def_chr_rec.COST_OF_MONEY,
2775 l_def_chr_rec.DCAA_AUDIT_REQ_FLAG,
2776 l_def_chr_rec.COST_SHARE_FLAG,
2777 l_def_chr_rec.OH_RATES_FINAL_FLAG,
2778 l_def_chr_rec.PROGRESS_PAYMENT_FLAG,
2779 l_def_chr_rec.PROGRESS_PAYMENT_LIQ_RATE,
2780 l_def_chr_rec.PROGRESS_PAYMENT_RATE,
2781 l_def_chr_rec.ALTERNATE_LIQUIDATION_RATE,
2782 l_def_chr_rec.PROP_DELIVERY_LOCATION,
2783 l_def_chr_rec.PROP_DUE_DATE_TIME,
2784 l_def_chr_rec.PROP_DUE_TIME,
2785 l_def_chr_rec.PROP_EXPIRE_DATE,
2786 l_def_chr_rec.COPIES_REQUIRED,
2787 l_def_chr_rec.SIC_CODE,
2788 l_def_chr_rec.TECH_DATA_WH_RATE,
2789 l_def_chr_rec.CREATED_BY,
2790 l_def_chr_rec.CREATION_DATE,
2791 l_def_chr_rec.LAST_UPDATED_BY,
2792 l_def_chr_rec.LAST_UPDATE_LOGIN,
2793 l_def_chr_rec.LAST_UPDATE_DATE,
2794 l_def_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG,
2795 l_def_chr_rec.DEFINITIZED_FLAG,
2796 l_def_chr_rec.COST_OF_SALE_RATE,
2797 l_def_chr_rec.LINE_VALUE_TOTAL,
2798 l_def_chr_rec.UNDEF_LINE_VALUE_TOTAL,
2799 -- l_def_chr_rec.END_DATE,
2800 l_def_chr_rec.OWNING_ORGANIZATION_ID,
2801 l_def_chr_rec.DEFAULT_TASK_ID);
2802
2803 -- Set OUT values
2804
2805 x_chr_rec := l_def_chr_rec;
2806
2807
2808 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
2809
2810 EXCEPTION
2811 WHEN OKE_API.G_EXCEPTION_ERROR THEN
2812
2813 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2814 (
2815 l_api_name,
2816 G_PKG_NAME,
2817 'OKE_API.G_RET_STS_ERROR',
2818 x_msg_count,
2819 x_msg_data,
2820 '_PVT');
2821 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2822 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2823 (
2824 l_api_name,
2825 G_PKG_NAME,
2826 'OKE_API.G_RET_STS_UNEXP_ERROR',
2827 x_msg_count,
2828 x_msg_data,
2829 '_PVT');
2830
2831 WHEN OTHERS THEN
2832 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2833 (
2834 l_api_name,
2835 G_PKG_NAME,
2836 'OTHERS',
2837 x_msg_count,
2838 x_msg_data,
2839 '_PVT');
2840
2841 END insert_row;
2842
2843
2844
2845 PROCEDURE insert_row(
2846 p_api_version IN NUMBER,
2847 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2848 x_return_status OUT NOCOPY VARCHAR2,
2849 x_msg_count OUT NOCOPY NUMBER,
2850 x_msg_data OUT NOCOPY VARCHAR2,
2851 p_chr_tbl IN chr_tbl_type,
2852 x_chr_tbl OUT NOCOPY chr_tbl_type) IS
2853
2854 l_api_version CONSTANT NUMBER := 1;
2855 l_api_name CONSTANT VARCHAR2(30) := 'TBL_insert_row';
2856
2857 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2858 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2859 i NUMBER := 0;
2860 l_chr_rec chr_rec_type;
2861
2862 BEGIN
2863
2864 OKE_API.init_msg_list(p_init_msg_list);
2865
2866 IF (p_chr_tbl.COUNT > 0) THEN
2867 i := p_chr_tbl.FIRST;
2868 LOOP
2869 x_return_status := OKE_API.G_RET_STS_SUCCESS;
2870 l_chr_rec := p_chr_tbl(i);
2871
2872 insert_row(
2873 p_api_version =>p_api_version,
2874 p_init_msg_list =>p_init_msg_list,
2875 x_return_status =>x_return_status,
2876 x_msg_count =>x_msg_count,
2877 x_msg_data =>x_msg_data,
2878 p_chr_rec =>l_chr_rec,
2879 x_chr_rec =>x_chr_tbl(i));
2880
2881 IF x_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
2882 IF l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
2883 l_overall_status := x_return_status;
2884 END IF;
2885 END IF;
2886
2887 EXIT WHEN (i = p_chr_tbl.LAST);
2888 i := p_chr_tbl.NEXT(i);
2889
2890 END LOOP;
2891
2892 x_return_status := l_overall_status;
2893
2894 END IF;
2895
2896 EXCEPTION
2897 WHEN OKE_API.G_EXCEPTION_ERROR THEN
2898
2899 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2900 (
2901 l_api_name,
2902 G_PKG_NAME,
2903 'OKE_API.G_RET_STS_ERROR',
2904 x_msg_count,
2905 x_msg_data,
2906 '_PVT');
2907 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2908 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2909 (
2910 l_api_name,
2911 G_PKG_NAME,
2912 'OKE_API.G_RET_STS_UNEXP_ERROR',
2913 x_msg_count,
2914 x_msg_data,
2915 '_PVT');
2916
2917 WHEN OTHERS THEN
2918 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2919 (
2920 l_api_name,
2921 G_PKG_NAME,
2922 'OTHERS',
2923 x_msg_count,
2924 x_msg_data,
2925 '_PVT');
2926
2927 END insert_row;
2928
2929 PROCEDURE update_row(
2930 p_api_version IN NUMBER,
2931 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2932 x_return_status OUT NOCOPY VARCHAR2,
2933 x_msg_count OUT NOCOPY NUMBER,
2934 x_msg_data OUT NOCOPY VARCHAR2,
2935 p_chr_rec IN chr_rec_type,
2936 x_chr_rec OUT NOCOPY chr_rec_type)IS
2937
2938 l_api_version CONSTANT NUMBER := 1;
2939 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2940
2941 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2942 l_chr_rec chr_rec_type;
2943
2944 l_def_chr_rec chr_rec_type;
2945 l_row_nofound BOOLEAN := TRUE;
2946
2947 l_k_number_disp VARCHAR2(120);
2948 l_k_number_disp_old VARCHAR2(120);
2949
2950 FUNCTION get_k_number_disp(P_HEADER_ID IN NUMBER) RETURN VARCHAR2 IS
2951 l_k_number VARCHAR2(120);
2952 BEGIN
2953 select k_number_disp into l_k_number
2954 from oke_k_headers
2955 where k_header_id=P_HEADER_ID;
2956
2957 return l_k_number;
2958 exception
2959 when others then
2960 null;
2961 END get_k_number_disp;
2962
2963 FUNCTION populate_new_record(
2964 p_chr_rec IN chr_rec_type,
2965 x_chr_rec OUT NOCOPY chr_rec_type) RETURN VARCHAR2 IS
2966
2967 l_chr_rec chr_rec_type;
2968 l_row_notfound BOOLEAN := TRUE;
2969
2970 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2971
2972 BEGIN
2973 x_chr_rec := p_chr_rec;
2974 -- Get current database value
2975 l_chr_rec := get_rec(p_chr_rec, l_row_notfound);
2976
2977 IF (l_row_notfound) THEN
2978 l_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2979 END IF;
2980
2981 IF (x_chr_rec.K_HEADER_ID = OKE_API.G_MISS_NUM) THEN
2982 x_chr_rec.K_HEADER_ID := l_chr_rec.K_HEADER_ID;
2983 END IF;
2984
2985 IF (x_chr_rec.PROGRAM_ID = OKE_API.G_MISS_NUM) THEN
2986 x_chr_rec.PROGRAM_ID := l_chr_rec.PROGRAM_ID;
2987 END IF;
2988
2989 IF (x_chr_rec.PROJECT_ID = OKE_API.G_MISS_NUM) THEN
2990 x_chr_rec.PROJECT_ID := l_chr_rec.PROJECT_ID;
2991 END IF;
2992
2993
2994 IF (x_chr_rec.BOA_ID = OKE_API.G_MISS_NUM) THEN
2995 x_chr_rec.BOA_ID := l_chr_rec.BOA_ID;
2996 END IF;
2997
2998 IF (x_chr_rec.K_TYPE_CODE = OKE_API.G_MISS_CHAR) THEN
2999 x_chr_rec.K_TYPE_CODE := l_chr_rec.K_TYPE_CODE;
3000 END IF;
3001
3002 IF (x_chr_rec.PRIORITY_CODE = OKE_API.G_MISS_CHAR) THEN
3003 x_chr_rec.PRIORITY_CODE := l_chr_rec.PRIORITY_CODE;
3004 END IF;
3005
3006 IF (x_chr_rec.PRIME_K_ALIAS = OKE_API.G_MISS_CHAR) THEN
3007 x_chr_rec.PRIME_K_ALIAS := l_chr_rec.PRIME_K_ALIAS;
3008 END IF;
3009
3010 IF (x_chr_rec.AUTHORIZE_DATE = OKE_API.G_MISS_DATE) THEN
3011 x_chr_rec.AUTHORIZE_DATE := l_chr_rec.AUTHORIZE_DATE;
3012 END IF;
3013
3014 IF (x_chr_rec.AUTHORIZING_REASON = OKE_API.G_MISS_CHAR) THEN
3015 x_chr_rec.AUTHORIZING_REASON := l_chr_rec.AUTHORIZING_REASON;
3016 END IF;
3017
3018 IF (x_chr_rec.AWARD_CANCEL_DATE = OKE_API.G_MISS_DATE) THEN
3019 x_chr_rec.AWARD_CANCEL_DATE := l_chr_rec.AWARD_CANCEL_DATE;
3020 END IF;
3021
3022 IF (x_chr_rec.AWARD_DATE = OKE_API.G_MISS_DATE) THEN
3023 x_chr_rec.AWARD_DATE := l_chr_rec.AWARD_DATE;
3024 END IF;
3025
3026 IF (x_chr_rec.DATE_DEFINITIZED = OKE_API.G_MISS_DATE) THEN
3027 x_chr_rec.DATE_DEFINITIZED := l_chr_rec.DATE_DEFINITIZED ;
3028 END IF;
3029
3030 IF (x_chr_rec.DATE_ISSUED = OKE_API.G_MISS_DATE) THEN
3031 x_chr_rec.DATE_ISSUED := l_chr_rec.DATE_ISSUED;
3032 END IF;
3033
3034 IF (x_chr_rec.DATE_NEGOTIATED = OKE_API.G_MISS_DATE) THEN
3035 x_chr_rec.DATE_NEGOTIATED := l_chr_rec.DATE_NEGOTIATED;
3036 END IF;
3037
3038 IF (x_chr_rec.DATE_RECEIVED = OKE_API.G_MISS_DATE) THEN
3039 x_chr_rec.DATE_RECEIVED := l_chr_rec.DATE_RECEIVED;
3040 END IF;
3041
3042 IF (x_chr_rec.DATE_SIGN_BY_CONTRACTOR = OKE_API.G_MISS_DATE) THEN
3043 x_chr_rec.DATE_SIGN_BY_CONTRACTOR := l_chr_rec.DATE_SIGN_BY_CONTRACTOR ;
3044 END IF;
3045
3046 IF (x_chr_rec.FAA_APPROVE_DATE = OKE_API.G_MISS_DATE) THEN
3047 x_chr_rec.FAA_APPROVE_DATE := l_chr_rec.FAA_APPROVE_DATE;
3048 END IF;
3049
3050 IF (x_chr_rec.FAA_REJECT_DATE = OKE_API.G_MISS_DATE) THEN
3051 x_chr_rec.FAA_REJECT_DATE := l_chr_rec.FAA_REJECT_DATE ;
3052 END IF;
3053
3054 IF (x_chr_rec.BOOKED_FLAG = OKE_API.G_MISS_CHAR) THEN
3055 x_chr_rec.BOOKED_FLAG := l_chr_rec.BOOKED_FLAG ;
3056 END IF;
3057
3058 IF (x_chr_rec.OPEN_FLAG = OKE_API.G_MISS_CHAR) THEN
3059 x_chr_rec.OPEN_FLAG := l_chr_rec.OPEN_FLAG ;
3060 END IF;
3061
3062 IF (x_chr_rec.CFE_FLAG = OKE_API.G_MISS_CHAR) THEN
3063 x_chr_rec.CFE_FLAG := l_chr_rec.CFE_FLAG ;
3064 END IF;
3065
3066 IF (x_chr_rec.VAT_CODE = OKE_API.G_MISS_CHAR) THEN
3067 x_chr_rec.VAT_CODE := l_chr_rec.VAT_CODE;
3068 END IF;
3069
3070 IF (x_chr_rec.COUNTRY_OF_ORIGIN_CODE = OKE_API.G_MISS_CHAR) THEN
3071 x_chr_rec.COUNTRY_OF_ORIGIN_CODE := l_chr_rec.COUNTRY_OF_ORIGIN_CODE ;
3072 END IF;
3073
3074 IF (x_chr_rec.EXPORT_FLAG = OKE_API.G_MISS_CHAR) THEN
3075 x_chr_rec.EXPORT_FLAG := l_chr_rec.EXPORT_FLAG;
3076 END IF;
3077
3078 IF (x_chr_rec.HUMAN_SUBJECT_FLAG = OKE_API.G_MISS_CHAR) THEN
3079 x_chr_rec.HUMAN_SUBJECT_FLAG := l_chr_rec.HUMAN_SUBJECT_FLAG;
3080 END IF;
3081
3082 IF (x_chr_rec.CQA_FLAG = OKE_API.G_MISS_CHAR) THEN
3083 x_chr_rec.CQA_FLAG := l_chr_rec.CQA_FLAG;
3084 END IF;
3085
3086 IF (x_chr_rec.INTERIM_RPT_REQ_FLAG = OKE_API.G_MISS_CHAR) THEN
3087 x_chr_rec.INTERIM_RPT_REQ_FLAG := l_chr_rec.INTERIM_RPT_REQ_FLAG ;
3088 END IF;
3089
3090 IF (x_chr_rec.NO_COMPETITION_AUTHORIZE = OKE_API.G_MISS_CHAR) THEN
3091 x_chr_rec.NO_COMPETITION_AUTHORIZE := l_chr_rec.NO_COMPETITION_AUTHORIZE ;
3092 END IF;
3093
3094 IF (x_chr_rec.PENALTY_CLAUSE_FLAG = OKE_API.G_MISS_CHAR) THEN
3095 x_chr_rec.PENALTY_CLAUSE_FLAG := l_chr_rec.PENALTY_CLAUSE_FLAG;
3096 END IF;
3097
3098 IF (x_chr_rec.PRODUCT_LINE_CODE = OKE_API.G_MISS_CHAR) THEN
3099 x_chr_rec.PRODUCT_LINE_CODE := l_chr_rec.PRODUCT_LINE_CODE ;
3100 END IF;
3101
3102 IF (x_chr_rec.REPORTING_FLAG = OKE_API.G_MISS_CHAR) THEN
3103 x_chr_rec.REPORTING_FLAG := l_chr_rec.REPORTING_FLAG;
3104 END IF;
3105
3106 IF (x_chr_rec.SB_PLAN_REQ_FLAG = OKE_API.G_MISS_CHAR) THEN
3107 x_chr_rec.SB_PLAN_REQ_FLAG := l_chr_rec.SB_PLAN_REQ_FLAG;
3108 END IF;
3109
3110 IF (x_chr_rec.SB_REPORT_FLAG = OKE_API.G_MISS_CHAR) THEN
3111 x_chr_rec.SB_REPORT_FLAG := l_chr_rec.SB_REPORT_FLAG;
3112 END IF;
3113
3114 IF (x_chr_rec.NTE_AMOUNT = OKE_API.G_MISS_NUM) THEN
3115 x_chr_rec.NTE_AMOUNT := l_chr_rec.NTE_AMOUNT;
3116 END IF;
3117
3118 IF (x_chr_rec.NTE_WARNING_FLAG = OKE_API.G_MISS_CHAR) THEN
3119 x_chr_rec.NTE_WARNING_FLAG := l_chr_rec.NTE_WARNING_FLAG;
3120 END IF;
3121
3122 IF (x_chr_rec.BILL_WITHOUT_DEF_FLAG = OKE_API.G_MISS_CHAR) THEN
3123 x_chr_rec.BILL_WITHOUT_DEF_FLAG := l_chr_rec.BILL_WITHOUT_DEF_FLAG;
3124 END IF;
3125
3126 IF (x_chr_rec.CAS_FLAG = OKE_API.G_MISS_CHAR) THEN
3127 x_chr_rec.CAS_FLAG := l_chr_rec.CAS_FLAG;
3128 END IF;
3129
3130
3131 IF (x_chr_rec.CLASSIFIED_FLAG = OKE_API.G_MISS_CHAR) THEN
3132 x_chr_rec.CLASSIFIED_FLAG := l_chr_rec.CLASSIFIED_FLAG;
3133 END IF;
3134
3135 IF (x_chr_rec.CLIENT_APPROVE_REQ_FLAG = OKE_API.G_MISS_CHAR) THEN
3136 x_chr_rec.CLIENT_APPROVE_REQ_FLAG := l_chr_rec.CLIENT_APPROVE_REQ_FLAG;
3137 END IF;
3138
3139 IF (x_chr_rec.COST_OF_MONEY = OKE_API.G_MISS_CHAR) THEN
3140 x_chr_rec.COST_OF_MONEY := l_chr_rec.COST_OF_MONEY;
3141 END IF;
3142
3143 IF (x_chr_rec.DCAA_AUDIT_REQ_FLAG = OKE_API.G_MISS_CHAR) THEN
3144 x_chr_rec.DCAA_AUDIT_REQ_FLAG := l_chr_rec.DCAA_AUDIT_REQ_FLAG;
3145 END IF;
3146
3147 IF (x_chr_rec.COST_SHARE_FLAG = OKE_API.G_MISS_CHAR) THEN
3148 x_chr_rec.COST_SHARE_FLAG := l_chr_rec.COST_SHARE_FLAG;
3149 END IF;
3150
3151 IF (x_chr_rec.OH_RATES_FINAL_FLAG = OKE_API.G_MISS_CHAR) THEN
3152 x_chr_rec.OH_RATES_FINAL_FLAG := l_chr_rec.OH_RATES_FINAL_FLAG;
3153 END IF;
3154
3155 IF (x_chr_rec.PROGRESS_PAYMENT_FLAG = OKE_API.G_MISS_CHAR) THEN
3156 x_chr_rec.PROGRESS_PAYMENT_FLAG := l_chr_rec.PROGRESS_PAYMENT_FLAG;
3157 END IF;
3158
3159 IF (x_chr_rec.PROGRESS_PAYMENT_LIQ_RATE = OKE_API.G_MISS_NUM) THEN
3160 x_chr_rec.PROGRESS_PAYMENT_LIQ_RATE := l_chr_rec.PROGRESS_PAYMENT_LIQ_RATE;
3161 END IF;
3162
3163 IF (x_chr_rec.PROGRESS_PAYMENT_RATE = OKE_API.G_MISS_NUM) THEN
3164 x_chr_rec.PROGRESS_PAYMENT_RATE := l_chr_rec.PROGRESS_PAYMENT_RATE;
3165 END IF;
3166
3167 IF (x_chr_rec.ALTERNATE_LIQUIDATION_RATE = OKE_API.G_MISS_NUM) THEN
3168 x_chr_rec.ALTERNATE_LIQUIDATION_RATE := l_chr_rec.ALTERNATE_LIQUIDATION_RATE;
3169 END IF;
3170
3171 IF (x_chr_rec.PROP_DELIVERY_LOCATION = OKE_API.G_MISS_CHAR) THEN
3172 x_chr_rec.PROP_DELIVERY_LOCATION := l_chr_rec.PROP_DELIVERY_LOCATION;
3173 END IF;
3174
3175 IF (x_chr_rec.PROP_DUE_DATE_TIME = OKE_API.G_MISS_DATE) THEN
3176 x_chr_rec.PROP_DUE_DATE_TIME := l_chr_rec.PROP_DUE_DATE_TIME;
3177 END IF;
3178
3179 IF (x_chr_rec.PROP_DUE_TIME = OKE_API.G_MISS_CHAR) THEN
3180 x_chr_rec.PROP_DUE_TIME := l_chr_rec.PROP_DUE_TIME;
3181 END IF;
3182
3183
3184 IF (x_chr_rec.PROP_EXPIRE_DATE = OKE_API.G_MISS_DATE) THEN
3185 x_chr_rec.PROP_EXPIRE_DATE := l_chr_rec.PROP_EXPIRE_DATE;
3186 END IF;
3187
3188 IF (x_chr_rec.COPIES_REQUIRED = OKE_API.G_MISS_NUM) THEN
3189 x_chr_rec.COPIES_REQUIRED := l_chr_rec.COPIES_REQUIRED;
3190 END IF;
3191
3192 IF (x_chr_rec.SIC_CODE = OKE_API.G_MISS_CHAR) THEN
3193 x_chr_rec.SIC_CODE := l_chr_rec.SIC_CODE;
3194 END IF;
3195
3196 IF (x_chr_rec.TECH_DATA_WH_RATE = OKE_API.G_MISS_NUM) THEN
3197 x_chr_rec.TECH_DATA_WH_RATE := l_chr_rec.TECH_DATA_WH_RATE;
3198 END IF;
3199
3200 IF (x_chr_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM) THEN
3201 x_chr_rec.LAST_UPDATE_LOGIN := l_chr_rec.LAST_UPDATE_LOGIN;
3202 END IF;
3203
3204 IF (x_chr_rec.COST_OF_SALE_RATE = OKE_API.G_MISS_NUM) THEN
3205 x_chr_rec.COST_OF_SALE_RATE := l_chr_rec.COST_OF_SALE_RATE;
3206 END IF;
3207
3208 IF (x_chr_rec.LINE_VALUE_TOTAL = OKE_API.G_MISS_NUM) THEN
3209 x_chr_rec.LINE_VALUE_TOTAL := l_chr_rec.LINE_VALUE_TOTAL;
3210 END IF;
3211
3212 IF (x_chr_rec.UNDEF_LINE_VALUE_TOTAL = OKE_API.G_MISS_NUM) THEN
3213 x_chr_rec.UNDEF_LINE_VALUE_TOTAL := l_chr_rec.UNDEF_LINE_VALUE_TOTAL;
3214 END IF;
3215
3216 -- IF (x_chr_rec.END_DATE = OKE_API.G_MISS_DATE) THEN
3217 -- x_chr_rec.END_DATE := l_chr_rec.END_DATE;
3218 -- END IF;
3219
3220 IF (x_chr_rec.OWNING_ORGANIZATION_ID = OKE_API.G_MISS_NUM) THEN
3221 x_chr_rec.OWNING_ORGANIZATION_ID := l_chr_rec.OWNING_ORGANIZATION_ID;
3222 END IF;
3223
3224 IF (x_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG = OKE_API.G_MISS_CHAR) THEN
3225 x_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG := l_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG;
3226 END IF;
3227
3228 IF (x_chr_rec.DEFINITIZED_FLAG = OKE_API.G_MISS_CHAR) THEN
3229 x_chr_rec.DEFINITIZED_FLAG := l_chr_rec.DEFINITIZED_FLAG;
3230 END IF;
3231
3232 IF (x_chr_rec.DEFAULT_TASK_ID = OKE_API.G_MISS_NUM) THEN
3233 x_chr_rec.DEFAULT_TASK_ID := l_chr_rec.DEFAULT_TASK_ID;
3234 END IF;
3235
3236 RETURN(l_return_status);
3237 END populate_new_record;
3238
3239 FUNCTION fill_who_column(
3240 p_chr_rec IN chr_rec_type) RETURN chr_rec_type IS
3241 l_chr_rec chr_rec_type := p_chr_rec;
3242 BEGIN
3243
3244 l_chr_rec.LAST_UPDATE_DATE := SYSDATE;
3245 l_chr_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3246 l_chr_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3247
3248 RETURN(l_chr_rec);
3249
3250 END fill_who_column;
3251
3252
3253 FUNCTION set_attributes(
3254
3255 p_chr_rec IN chr_rec_type,
3256 x_chr_rec OUT NOCOPY chr_rec_type) RETURN VARCHAR2 IS
3257
3258 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3259
3260 BEGIN
3261
3262 x_chr_rec := p_chr_rec;
3263
3264 x_chr_rec.BOOKED_FLAG := UPPER(x_chr_rec.BOOKED_FLAG);
3265 x_chr_rec.OPEN_FLAG := UPPER(x_chr_rec.OPEN_FLAG);
3266 x_chr_rec.CFE_FLAG := UPPER(x_chr_rec.CFE_FLAG);
3267 x_chr_rec.EXPORT_FLAG := UPPER(x_chr_rec.EXPORT_FLAG);
3268 x_chr_rec.HUMAN_SUBJECT_FLAG := UPPER(x_chr_rec.HUMAN_SUBJECT_FLAG);
3269 x_chr_rec.CQA_FLAG := UPPER(x_chr_rec.CQA_FLAG);
3270 x_chr_rec.INTERIM_RPT_REQ_FLAG := UPPER(x_chr_rec.INTERIM_RPT_REQ_FLAG);
3271 x_chr_rec.PENALTY_CLAUSE_FLAG := UPPER(x_chr_rec.PENALTY_CLAUSE_FLAG);
3272 x_chr_rec.REPORTING_FLAG := UPPER(x_chr_rec.REPORTING_FLAG);
3273 x_chr_rec.NTE_WARNING_FLAG := UPPER(x_chr_rec.NTE_WARNING_FLAG);
3274 x_chr_rec.BILL_WITHOUT_DEF_FLAG := UPPER(x_chr_rec.BILL_WITHOUT_DEF_FLAG);
3275 x_chr_rec.CAS_FLAG := UPPER(x_chr_rec.CAS_FLAG);
3276 x_chr_rec.CLASSIFIED_FLAG := UPPER(x_chr_rec.CLASSIFIED_FLAG);
3277 x_chr_rec.CLIENT_APPROVE_REQ_FLAG := UPPER(x_chr_rec.CLIENT_APPROVE_REQ_FLAG);
3278 x_chr_rec.DCAA_AUDIT_REQ_FLAG := UPPER(x_chr_rec.DCAA_AUDIT_REQ_FLAG);
3279 x_chr_rec.COST_SHARE_FLAG := UPPER(x_chr_rec.COST_SHARE_FLAG);
3280 x_chr_rec.OH_RATES_FINAL_FLAG := UPPER(x_chr_rec.OH_RATES_FINAL_FLAG);
3281 x_chr_rec.PROGRESS_PAYMENT_FLAG := UPPER(x_chr_rec.PROGRESS_PAYMENT_FLAG);
3282 x_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG := UPPER(x_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG);
3283 x_chr_rec.DEFINITIZED_FLAG := UPPER(x_chr_rec.DEFINITIZED_FLAG);
3284
3285 RETURN(l_return_status);
3286
3287 END set_attributes;
3288
3289 BEGIN
3290
3291 l_return_status := OKE_API.START_ACTIVITY(
3292 l_api_name,
3293 p_init_msg_list,
3294 '_PVT',
3295 x_return_status);
3296
3297 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3298
3299 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3300
3301 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3302
3303 RAISE OKE_API.G_EXCEPTION_ERROR;
3304
3305 END IF;
3306
3307 l_return_status := set_attributes(
3308 p_chr_rec,
3309 l_chr_rec);
3310
3311 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3312
3313 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3314
3315 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3316
3317 RAISE OKE_API.G_EXCEPTION_ERROR;
3318
3319 END IF;
3320
3321
3322 l_return_status := populate_new_record(
3323 l_chr_rec,
3324 l_def_chr_rec);
3325
3326
3327 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3328
3329 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3330
3331 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3332
3333 RAISE OKE_API.G_EXCEPTION_ERROR;
3334
3335 END IF;
3336
3337
3338 l_def_chr_rec := fill_who_column(l_def_chr_rec);
3339
3340 -- Validate all non_missing attributes (Item level validation)
3341
3342 l_return_status := validate_attributes(l_def_chr_rec);
3343
3344
3345 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3346
3347 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3348
3349 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3350
3351 RAISE OKE_API.G_EXCEPTION_ERROR;
3352
3353 END IF;
3354
3355 l_return_status := validate_record(l_def_chr_rec);
3356
3357
3358 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3359
3360 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3361
3362 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3363
3364 RAISE OKE_API.G_EXCEPTION_ERROR;
3365
3366 END IF;
3367
3368 l_k_number_disp_old := get_k_number_disp( l_def_chr_rec.K_HEADER_ID);
3369 l_k_number_disp := K_Number_Disp( l_def_chr_rec.K_HEADER_ID
3370 , l_def_chr_rec.BOA_ID );
3371
3372 UPDATE OKE_K_HEADERS
3373 SET PROGRAM_ID = l_def_chr_rec.PROGRAM_ID,
3374 PROJECT_ID = l_def_chr_rec.PROJECT_ID,
3375 BOA_ID = l_def_chr_rec.BOA_ID,
3376 K_NUMBER_DISP = l_k_number_disp,
3377 K_TYPE_CODE = l_def_chr_rec.K_TYPE_CODE,
3378 PRIORITY_CODE = l_def_chr_rec.PRIORITY_CODE,
3379 PRIME_K_ALIAS = l_def_chr_rec.PRIME_K_ALIAS,
3380 PRIME_K_NUMBER = l_def_chr_rec.PRIME_K_NUMBER,
3381 AUTHORIZE_DATE = l_def_chr_rec.AUTHORIZE_DATE,
3382 AUTHORIZING_REASON = l_def_chr_rec.AUTHORIZING_REASON,
3383 AWARD_CANCEL_DATE = l_def_chr_rec.AWARD_CANCEL_DATE,
3384 AWARD_DATE = l_def_chr_rec.AWARD_DATE,
3385 DATE_DEFINITIZED = l_def_chr_rec.DATE_DEFINITIZED,
3386 DATE_ISSUED = l_def_chr_rec.DATE_ISSUED,
3387 DATE_NEGOTIATED = l_def_chr_rec.DATE_NEGOTIATED,
3388 DATE_RECEIVED = l_def_chr_rec.DATE_RECEIVED,
3389 DATE_SIGN_BY_CONTRACTOR = l_def_chr_rec.DATE_SIGN_BY_CONTRACTOR,
3390 DATE_SIGN_BY_CUSTOMER = l_def_chr_rec.DATE_SIGN_BY_CUSTOMER,
3391 FAA_APPROVE_DATE = l_def_chr_rec.FAA_APPROVE_DATE,
3392 FAA_REJECT_DATE = l_def_chr_rec.FAA_REJECT_DATE,
3393 BOOKED_FLAG = l_def_chr_rec.BOOKED_FLAG,
3394 OPEN_FLAG = l_def_chr_rec.OPEN_FLAG,
3395 CFE_FLAG = l_def_chr_rec.CFE_FLAG,
3396 VAT_CODE = l_def_chr_rec.VAT_CODE,
3397 COUNTRY_OF_ORIGIN_CODE = l_def_chr_rec.COUNTRY_OF_ORIGIN_CODE,
3398 EXPORT_FLAG = l_def_chr_rec.EXPORT_FLAG,
3399 HUMAN_SUBJECT_FLAG = l_def_chr_rec.HUMAN_SUBJECT_FLAG,
3400 CQA_FLAG = l_def_chr_rec.CQA_FLAG,
3401 INTERIM_RPT_REQ_FLAG = l_def_chr_rec.INTERIM_RPT_REQ_FLAG,
3402 NO_COMPETITION_AUTHORIZE = l_def_chr_rec.NO_COMPETITION_AUTHORIZE,
3403 PENALTY_CLAUSE_FLAG = l_def_chr_rec.PENALTY_CLAUSE_FLAG,
3404 PRODUCT_LINE_CODE = l_def_chr_rec.PRODUCT_LINE_CODE,
3405 REPORTING_FLAG = l_def_chr_rec.REPORTING_FLAG,
3406 SB_PLAN_REQ_FLAG = l_def_chr_rec.SB_PLAN_REQ_FLAG,
3407 SB_REPORT_FLAG = l_def_chr_rec.SB_REPORT_FLAG,
3408 NTE_AMOUNT = l_def_chr_rec.NTE_AMOUNT,
3409 NTE_WARNING_FLAG = l_def_chr_rec.NTE_WARNING_FLAG,
3410 BILL_WITHOUT_DEF_FLAG = l_def_chr_rec.BILL_WITHOUT_DEF_FLAG,
3411 CAS_FLAG = l_def_chr_rec.CAS_FLAG,
3412 CLASSIFIED_FLAG = l_def_chr_rec.CLASSIFIED_FLAG,
3413 CLIENT_APPROVE_REQ_FLAG = l_def_chr_rec.CLIENT_APPROVE_REQ_FLAG,
3414 COST_OF_MONEY = l_def_chr_rec.COST_OF_MONEY,
3415 DCAA_AUDIT_REQ_FLAG = l_def_chr_rec.DCAA_AUDIT_REQ_FLAG,
3416 COST_SHARE_FLAG = l_def_chr_rec.COST_SHARE_FLAG,
3417 OH_RATES_FINAL_FLAG = l_def_chr_rec.OH_RATES_FINAL_FLAG,
3418 PROGRESS_PAYMENT_FLAG = l_def_chr_rec.PROGRESS_PAYMENT_FLAG,
3419 PROGRESS_PAYMENT_LIQ_RATE = l_def_chr_rec.PROGRESS_PAYMENT_LIQ_RATE,
3420 PROGRESS_PAYMENT_RATE = l_def_chr_rec.PROGRESS_PAYMENT_RATE,
3421 ALTERNATE_LIQUIDATION_RATE = l_def_chr_rec.ALTERNATE_LIQUIDATION_RATE,
3422 PROP_DELIVERY_LOCATION = l_def_chr_rec.PROP_DELIVERY_LOCATION,
3423 PROP_DUE_DATE_TIME = l_def_chr_rec.PROP_DUE_DATE_TIME,
3424 PROP_DUE_TIME = l_def_chr_rec.PROP_DUE_TIME,
3425 PROP_EXPIRE_DATE = l_def_chr_rec.PROP_EXPIRE_DATE,
3426 COPIES_REQUIRED = l_def_chr_rec.COPIES_REQUIRED,
3427 SIC_CODE = l_def_chr_rec.SIC_CODE,
3428 TECH_DATA_WH_RATE = l_def_chr_rec.TECH_DATA_WH_RATE,
3429 FINANCIAL_CTRL_VERIFIED_FLAG = l_def_chr_rec.FINANCIAL_CTRL_VERIFIED_FLAG,
3430 DEFINITIZED_FLAG = l_def_chr_rec.DEFINITIZED_FLAG,
3431 COST_OF_SALE_RATE = l_def_chr_rec.COST_OF_SALE_RATE,
3432 LINE_VALUE_TOTAL = l_def_chr_rec.LINE_VALUE_TOTAL,
3433 UNDEF_LINE_VALUE_TOTAL = l_def_chr_rec.UNDEF_LINE_VALUE_TOTAL,
3434 -- END_DATE = l_def_chr_rec.END_DATE,
3435 OWNING_ORGANIZATION_ID = l_def_chr_rec.OWNING_ORGANIZATION_ID,
3436 DEFAULT_TASK_ID = l_def_chr_rec.DEFAULT_TASK_ID
3437
3438 WHERE K_HEADER_ID = l_def_chr_rec.K_HEADER_ID;
3439
3440 x_chr_rec := l_def_chr_rec;
3441
3442 -- bug 7115155
3443 IF ( l_k_number_disp<>l_k_number_disp_old
3444 and
3445 IS_BOA(x_chr_rec.k_type_code)
3446 )
3447 THEN
3448 update_do(x_chr_rec.k_header_id);
3449 END IF;
3450
3451
3452 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
3453
3454 EXCEPTION
3455 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3456
3457 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3458 (
3459 l_api_name,
3460 G_PKG_NAME,
3461 'OKE_API.G_RET_STS_ERROR',
3462 x_msg_count,
3463 x_msg_data,
3464 '_PVT');
3465 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3466 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3467 (
3468 l_api_name,
3469 G_PKG_NAME,
3470 'OKE_API.G_RET_STS_UNEXP_ERROR',
3471 x_msg_count,
3472 x_msg_data,
3473 '_PVT');
3474
3475 WHEN OTHERS THEN
3476 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3477 (
3478 l_api_name,
3479 G_PKG_NAME,
3480 'OTHERS',
3481 x_msg_count,
3482 x_msg_data,
3483 '_PVT');
3484
3485 END update_row;
3486
3487 PROCEDURE change_version IS
3488 BEGIN
3489 null;
3490 END;
3491
3492 PROCEDURE api_copy IS
3493 BEGIN
3494 null;
3495 END;
3496
3497 PROCEDURE update_row(
3498 p_api_version IN NUMBER,
3499 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3500 x_return_status OUT NOCOPY VARCHAR2,
3501 x_msg_count OUT NOCOPY NUMBER,
3502 x_msg_data OUT NOCOPY VARCHAR2,
3503 p_chr_tbl IN chr_tbl_type,
3504 x_chr_tbl OUT NOCOPY chr_tbl_type)IS
3505
3506 l_api_version CONSTANT NUMBER := 1;
3507 l_api_name CONSTANT VARCHAR2(30) := 'TBL_update_row';
3508
3509 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3510 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3511 i NUMBER := 0;
3512
3513 BEGIN
3514 IF (p_chr_tbl.COUNT > 0) THEN
3515 i := p_chr_tbl.FIRST;
3516 LOOP
3517
3518 update_row(
3519 p_api_version =>p_api_version,
3520 p_init_msg_list =>OKE_API.G_FALSE,
3521 x_return_status =>x_return_status,
3522 x_msg_count =>x_msg_count,
3523 x_msg_data =>x_msg_data,
3524 p_chr_rec =>p_chr_tbl(i),
3525 x_chr_rec =>x_chr_tbl(i));
3526
3527 IF x_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
3528 IF l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
3529 l_overall_status := x_return_status;
3530 END IF;
3531 END IF;
3532
3533 EXIT WHEN (i = p_chr_tbl.LAST);
3534 i := p_chr_tbl.NEXT(i);
3535
3536 END LOOP;
3537
3538 x_return_status := l_overall_status;
3539
3540 END IF;
3541
3542 EXCEPTION
3543
3544 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3545
3546 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3547 (
3548 l_api_name,
3549 G_PKG_NAME,
3550 'OKE_API.G_RET_STS_ERROR',
3551 x_msg_count,
3552 x_msg_data,
3553 '_PVT');
3554 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3555 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3556 (
3557 l_api_name,
3558 G_PKG_NAME,
3559 'OKE_API.G_RET_STS_UNEXP_ERROR',
3560 x_msg_count,
3561 x_msg_data,
3562 '_PVT');
3563
3564 WHEN OTHERS THEN
3565 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3566 (
3567 l_api_name,
3568 G_PKG_NAME,
3569 'OTHERS',
3570 x_msg_count,
3571 x_msg_data,
3572 '_PVT');
3573
3574 END update_row;
3575
3576 PROCEDURE delete_row(
3577
3578 p_api_version IN NUMBER,
3579 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3580 x_return_status OUT NOCOPY VARCHAR2,
3581 x_msg_count OUT NOCOPY NUMBER,
3582 x_msg_data OUT NOCOPY VARCHAR2,
3583 p_chr_rec IN chr_rec_type)IS
3584
3585 l_api_version CONSTANT NUMBER := 1;
3586 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3587
3588 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3589 l_chr_rec chr_rec_type := p_chr_rec;
3590 l_row_notfound BOOLEAN := TRUE;
3591
3592 BEGIN
3593
3594 l_return_status := OKE_API.START_ACTIVITY(
3595 l_api_name,
3596 p_init_msg_list,
3597 '_PVT',
3598 x_return_status);
3599
3600 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3601
3602 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3603
3604 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3605
3606 RAISE OKE_API.G_EXCEPTION_ERROR;
3607
3608 END IF;
3609
3610 DELETE FROM OKE_K_HEADERS
3611 WHERE K_HEADER_ID = l_chr_rec.K_HEADER_ID;
3612
3613 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
3614
3615 EXCEPTION
3616 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3617
3618 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3619 (
3620 l_api_name,
3621 G_PKG_NAME,
3622 'OKE_API.G_RET_STS_ERROR',
3623 x_msg_count,
3624 x_msg_data,
3625 '_PVT');
3626 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3627 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3628 (
3629 l_api_name,
3630 G_PKG_NAME,
3631 'OKE_API.G_RET_STS_UNEXP_ERROR',
3632 x_msg_count,
3633 x_msg_data,
3634 '_PVT');
3635
3636 WHEN OTHERS THEN
3637 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3638 (
3639 l_api_name,
3640 G_PKG_NAME,
3641 'OTHERS',
3642 x_msg_count,
3643 x_msg_data,
3644 '_PVT');
3645
3646 END delete_row;
3647
3648 PROCEDURE delete_row(
3649 p_api_version IN NUMBER,
3650 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3651 x_return_status OUT NOCOPY VARCHAR2,
3652 x_msg_count OUT NOCOPY NUMBER,
3653 x_msg_data OUT NOCOPY VARCHAR2,
3654 p_chr_tbl IN chr_tbl_type)IS
3655
3656 l_api_version CONSTANT NUMBER := 1;
3657 l_api_name CONSTANT VARCHAR2(30) := 'TBL_delete_row';
3658
3659 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3660 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3661 i NUMBER := 0;
3662 l_dummy_val NUMBER;
3663 CURSOR l_csr IS
3664 SELECT COUNT(1)
3665 FROM OKE_K_HEADERS
3666 WHERE OKE_K_HEADERS.K_HEADER_ID = p_chr_tbl(i).K_HEADER_ID;
3667
3668 BEGIN
3669
3670 OKE_API.init_msg_list(p_init_msg_list);
3671
3672 IF (p_chr_tbl.COUNT > 0) THEN
3673 i := p_chr_tbl.FIRST;
3674 LOOP
3675 -- Check whether detail record exists
3676 OPEN l_csr;
3677 FETCH l_csr INTO l_dummy_val;
3678 CLOSE l_csr;
3679
3680 IF (l_dummy_val = 0) THEN
3681
3682 delete_row(
3683 p_api_version =>p_api_version,
3684 p_init_msg_list =>OKE_API.G_FALSE,
3685 x_return_status =>x_return_status,
3686 x_msg_count =>x_msg_count,
3687 x_msg_data =>x_msg_data,
3688 p_chr_rec =>p_chr_tbl(i));
3689
3690 IF x_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
3691 IF l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
3692 l_overall_status := x_return_status;
3693 END IF;
3694 END IF;
3695
3696 ELSE
3697 OKE_API.SET_MESSAGE(
3698 p_app_name =>g_app_name,
3699 p_msg_name =>G_CHILD_RECORD_EXISTS);
3700 x_return_status := OKE_API.G_RET_STS_ERROR;
3701
3702 IF l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
3703 l_overall_status := x_return_status;
3704 END IF;
3705
3706 END IF;
3707
3708 EXIT WHEN(i = p_chr_tbl.LAST);
3709 i := p_chr_tbl.NEXT(i);
3710
3711 END LOOP;
3712
3713 x_return_status := l_overall_status;
3714
3715 END IF;
3716
3717 EXCEPTION
3718 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3719
3720 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3721 (
3722 l_api_name,
3723 G_PKG_NAME,
3724 'OKE_API.G_RET_STS_ERROR',
3725 x_msg_count,
3726 x_msg_data,
3727 '_PVT');
3728 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3729 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3730 (
3731 l_api_name,
3732 G_PKG_NAME,
3733 'OKE_API.G_RET_STS_UNEXP_ERROR',
3734 x_msg_count,
3735 x_msg_data,
3736 '_PVT');
3737
3738 WHEN OTHERS THEN
3739 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3740 (
3741 l_api_name,
3742 G_PKG_NAME,
3743 'OTHERS',
3744 x_msg_count,
3745 x_msg_data,
3746 '_PVT');
3747
3748 END delete_row;
3749
3750 PROCEDURE validate_row(
3751 p_api_version IN NUMBER,
3752 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3753 x_return_status OUT NOCOPY VARCHAR2,
3754 x_msg_count OUT NOCOPY NUMBER,
3755 x_msg_data OUT NOCOPY VARCHAR2,
3756 p_chr_rec IN chr_rec_type)IS
3757
3758 l_api_version CONSTANT NUMBER := 1;
3759 l_api_name CONSTANT VARCHAR2(30) := 'B_validate_row';
3760
3761 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3762 l_chr_rec chr_rec_type := p_chr_rec;
3763 l_row_notfound BOOLEAN := TRUE;
3764
3765 BEGIN
3766
3767 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
3768 G_PKG_NAME,
3769 p_init_msg_list,
3770 l_api_version,
3771 p_api_version,
3772 '_PVT',
3773 x_return_status);
3774
3775 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3776
3777 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3778
3779 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3780
3781 RAISE OKE_API.G_EXCEPTION_ERROR;
3782
3783 END IF;
3784
3785 l_return_status := validate_attributes(l_chr_rec);
3786
3787 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3788
3789 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3790
3791 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3792
3793 RAISE OKE_API.G_EXCEPTION_ERROR;
3794
3795 END IF;
3796
3797 l_return_status := validate_record(l_chr_rec);
3798
3799 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3800
3801 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3802
3803 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3804
3805 RAISE OKE_API.G_EXCEPTION_ERROR;
3806
3807 END IF;
3808
3809 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
3810
3811 EXCEPTION
3812 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3813
3814 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3815 (
3816 l_api_name,
3817 G_PKG_NAME,
3818 'OKE_API.G_RET_STS_ERROR',
3819 x_msg_count,
3820 x_msg_data,
3821 '_PVT');
3822 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3823 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3824 (
3825 l_api_name,
3826 G_PKG_NAME,
3827 'OKE_API.G_RET_STS_UNEXP_ERROR',
3828 x_msg_count,
3829 x_msg_data,
3830 '_PVT');
3831
3832 WHEN OTHERS THEN
3833 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3834 (
3835 l_api_name,
3836 G_PKG_NAME,
3837 'OTHERS',
3838 x_msg_count,
3839 x_msg_data,
3840 '_PVT');
3841
3842 END validate_row;
3843
3844
3845 PROCEDURE validate_row(
3846 p_api_version IN NUMBER,
3847 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3848 x_return_status OUT NOCOPY VARCHAR2,
3849 x_msg_count OUT NOCOPY NUMBER,
3850 x_msg_data OUT NOCOPY VARCHAR2,
3851 p_chr_tbl IN chr_tbl_type)IS
3852
3853 l_api_version CONSTANT NUMBER := 1;
3854 l_api_name CONSTANT VARCHAR2(30) := 'TBL_validate_row';
3855
3856 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3857 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3858 i NUMBER := 0;
3859
3860
3861 BEGIN
3862
3863 OKE_API.init_msg_list(p_init_msg_list);
3864
3865 IF (p_chr_tbl.COUNT > 0) THEN
3866 i := p_chr_tbl.FIRST;
3867 LOOP
3868 validate_row(
3869 p_api_version =>p_api_version,
3870 p_init_msg_list =>OKE_API.G_FALSE,
3871 x_return_status =>x_return_status,
3872 x_msg_count =>x_msg_count,
3873 x_msg_data =>x_msg_data,
3874 p_chr_rec =>p_chr_tbl(i));
3875 IF x_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
3876 IF l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
3877 l_overall_status := x_return_status;
3878 END IF;
3879 END IF;
3880
3881 EXIT WHEN(i = p_chr_tbl.LAST);
3882 i := p_chr_tbl.NEXT(i);
3883
3884 END LOOP;
3885
3886 x_return_status := l_overall_status;
3887
3888 END IF;
3889
3890 EXCEPTION
3891 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3892
3893 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3894 (
3895 l_api_name,
3896 G_PKG_NAME,
3897 'OKE_API.G_RET_STS_ERROR',
3898 x_msg_count,
3899 x_msg_data,
3900 '_PVT');
3901 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3902 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3903 (
3904 l_api_name,
3905 G_PKG_NAME,
3906 'OKE_API.G_RET_STS_UNEXP_ERROR',
3907 x_msg_count,
3908 x_msg_data,
3909 '_PVT');
3910
3911 WHEN OTHERS THEN
3912 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3913 (
3914 l_api_name,
3915 G_PKG_NAME,
3916 'OTHERS',
3917 x_msg_count,
3918 x_msg_data,
3919 '_PVT');
3920
3921 END validate_row;
3922
3923
3924 END OKE_CHR_PVT;
3925