[Home] [Help]
PACKAGE BODY: APPS.OKE_FORM_PVT
Source
1 PACKAGE BODY OKE_FORM_PVT AS
2 /* $Header: OKEVKPFB.pls 120.1 2005/05/27 15:56:40 appldev $ */
3
4 -- validate record
5 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_form_pvt.';
6 FUNCTION validate_record (
7 p_form_rec IN form_rec_type
8 ) RETURN VARCHAR2 IS
9 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
10 BEGIN
11
12 RETURN(l_return_status);
13
14 END validate_record;
15
16 -- validate individual attributes
17
18 FUNCTION validate_attributes(
19 p_form_rec IN form_rec_type
20 ) RETURN VARCHAR2 IS
21
22 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
23 x_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
24
25
26 PROCEDURE validate_k_header_id(x_return_status OUT NOCOPY VARCHAR2,
27 p_form_rec IN form_rec_type)IS
28
29 l_dummy_val VARCHAR2(1):='?';
30 CURSOR l_csr IS
31 SELECT 'x'
32 FROM OKE_K_HEADERS
33 WHERE K_HEADER_ID = p_form_rec.K_HEADER_ID;
34
35 BEGIN
36
37 x_return_status := OKE_API.G_RET_STS_SUCCESS;
38
39
40 -- check required value - not null
41
42 IF ( p_form_rec.k_header_id = OKE_API.G_MISS_NUM
43 OR p_form_rec.k_header_id IS NULL) THEN
44 OKE_API.SET_MESSAGE(
45 p_app_name =>g_app_name,
46 p_msg_name =>g_required_value,
47 p_token1 =>g_col_name_token,
48 p_token1_value =>'K_HEADER_ID');
49
50 x_return_status := OKE_API.G_RET_STS_ERROR;
51 raise G_EXCEPTION_HALT_VALIDATION;
52 END IF;
53
54
55 OPEN l_csr;
56 FETCH l_csr INTO l_dummy_val;
57 CLOSE l_csr;
58
59 IF (l_dummy_val = '?') THEN
60 OKE_API.SET_MESSAGE(
61 p_app_name =>g_app_name,
62 p_msg_name =>g_no_parent_record,
63 p_token1 =>g_col_name_token,
64 p_token1_value =>'K_HEADER_ID',
65 p_token2 =>g_child_table_token,
66 p_token2_value =>G_VIEW,
67 p_token3 =>g_parent_table_token,
68 p_token3_value =>'OKE_K_HEADERS');
69
70 x_return_status := OKE_API.G_RET_STS_ERROR;
71 END IF;
72
73
74
75
76 EXCEPTION
77 WHEN G_EXCEPTION_HALT_VALIDATION THEN
78 NULL;
79 WHEN OTHERS THEN
80 -- store SQL error message on message stack
81 OKE_API.SET_MESSAGE(
82 p_app_name =>g_app_name,
83 p_msg_name =>G_UNEXPECTED_ERROR,
84 p_token1 =>G_SQLCODE_TOKEN,
85 p_token1_value =>SQLCODE,
86 p_token2 =>G_SQLERRM_TOKEN,
87 p_token2_value =>SQLERRM);
88 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
89
90 IF l_csr%ISOPEN THEN
91 CLOSE l_csr;
92 END IF;
93
94 END validate_k_header_id;
95
96
97 PROCEDURE validate_k_line_id(x_return_status OUT NOCOPY VARCHAR2,
98 p_form_rec IN form_rec_type)IS
99
100 l_dummy_val VARCHAR2(1):='?';
101 CURSOR l_csr IS
102 SELECT 'x'
103 FROM OKE_K_LINES
104 WHERE K_LINE_ID = p_form_rec.K_LINE_ID;
105
106 BEGIN
107 x_return_status := OKE_API.G_RET_STS_SUCCESS;
108 IF ( p_form_rec.k_line_id <> OKE_API.G_MISS_NUM
109 AND p_form_rec.k_line_id IS NOT NULL) THEN
110
111 OPEN l_csr;
112 FETCH l_csr INTO l_dummy_val;
113 CLOSE l_csr;
114
115 IF (l_dummy_val = '?') THEN
116 OKE_API.SET_MESSAGE(
117 p_app_name =>g_app_name,
118 p_msg_name =>g_no_parent_record,
119 p_token1 =>g_col_name_token,
120 p_token1_value =>'K_LINE_ID',
121 p_token2 =>g_child_table_token,
122 p_token2_value =>G_VIEW,
123 p_token3 =>g_parent_table_token,
124 p_token3_value =>G_VIEW);
125
126 x_return_status := OKE_API.G_RET_STS_ERROR;
127 END IF;
128 END IF;
129 EXCEPTION
130 WHEN OTHERS THEN
131 -- store SQL error message on message stack
132 OKE_API.SET_MESSAGE(
133 p_app_name =>g_app_name,
134 p_msg_name =>G_UNEXPECTED_ERROR,
135 p_token1 =>G_SQLCODE_TOKEN,
136 p_token1_value =>SQLCODE,
137 p_token2 =>G_SQLERRM_TOKEN,
138 p_token2_value =>SQLERRM);
139 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
140
141 IF l_csr%ISOPEN THEN
142 CLOSE l_csr;
143 END IF;
144 END validate_k_line_id;
145
146
147
148 PROCEDURE validate_print_form_code(x_return_status OUT NOCOPY VARCHAR2,
149 p_form_rec IN form_rec_type)IS
150
151 l_dummy_val VARCHAR2(1):='?';
152 CURSOR l_csr IS
153 SELECT 'x'
154 FROM OKE_PRINT_FORMS_B
155 WHERE PRINT_FORM_CODE = p_form_rec.PRINT_FORM_CODE;
156
157 BEGIN
158
159 x_return_status := OKE_API.G_RET_STS_SUCCESS;
160
161
162 -- check required value - not null
163
164 IF ( p_form_rec.print_form_code = OKE_API.G_MISS_CHAR
165 OR p_form_rec.print_form_code IS NULL) THEN
166 OKE_API.SET_MESSAGE(
167 p_app_name =>g_app_name,
168 p_msg_name =>g_required_value,
169 p_token1 =>g_col_name_token,
170 p_token1_value =>'PRINT_FORM_CODE');
171
172 x_return_status := OKE_API.G_RET_STS_ERROR;
173 raise G_EXCEPTION_HALT_VALIDATION;
174 END IF;
175
176
177 OPEN l_csr;
178 FETCH l_csr INTO l_dummy_val;
179 CLOSE l_csr;
180
181 IF (l_dummy_val = '?') THEN
182 OKE_API.SET_MESSAGE(
183 p_app_name =>g_app_name,
184 p_msg_name =>g_no_parent_record,
185 p_token1 =>g_col_name_token,
186 p_token1_value =>'PRINT_FORM_CODE',
187 p_token2 =>g_child_table_token,
188 p_token2_value =>G_VIEW,
189 p_token3 =>g_parent_table_token,
190 p_token3_value =>'OKE_PRINT_FORMS_B');
191
192 x_return_status := OKE_API.G_RET_STS_ERROR;
193 END IF;
194
195
196
197 EXCEPTION
198 WHEN G_EXCEPTION_HALT_VALIDATION THEN
199 NULL;
200 WHEN OTHERS THEN
201 -- store SQL error message on message stack
202 OKE_API.SET_MESSAGE(
203 p_app_name =>g_app_name,
204 p_msg_name =>G_UNEXPECTED_ERROR,
205 p_token1 =>G_SQLCODE_TOKEN,
206 p_token1_value =>SQLCODE,
207 p_token2 =>G_SQLERRM_TOKEN,
208 p_token2_value =>SQLERRM);
209 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
210
211 IF l_csr%ISOPEN THEN
212 CLOSE l_csr;
213 END IF;
214
215 END validate_print_form_code;
216
217
218 BEGIN
219
220 validate_k_header_id (x_return_status => l_return_status,
221 p_form_rec => p_form_rec);
222 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
223 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
224 x_return_status := l_return_status;
225 END IF;
226 END IF;
227
228 validate_k_line_id (x_return_status => l_return_status,
229 p_form_rec => p_form_rec);
230 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
231 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
232 x_return_status := l_return_status;
233 END IF;
234 END IF;
235
236
237 validate_print_form_code (x_return_status => l_return_status,
238 p_form_rec => p_form_rec);
239 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
240 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
241 x_return_status := l_return_status;
242 END IF;
243 END IF;
244
245 /* call individual validation procedure */
246 -- return status to caller
247 RETURN(x_return_status);
248
249 END Validate_Attributes;
250
251 FUNCTION null_out_defaults(
252 p_form_rec IN form_rec_type ) RETURN form_rec_type IS
253
254 l_form_rec form_rec_type := p_form_rec;
255 l_api_name CONSTANT VARCHAR2(30) := 'null_out_defaults';
256
257 BEGIN
258
259 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
260 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'call from null out defaults');
261 END IF;
262
263 IF l_form_rec.K_HEADER_ID = OKE_API.G_MISS_NUM THEN
264 l_form_rec.K_HEADER_ID := NULL;
265 END IF;
266
267 IF l_form_rec.K_LINE_ID = OKE_API.G_MISS_NUM THEN
268 l_form_rec.K_LINE_ID := NULL;
269 END IF;
270
271 IF l_form_rec.PRINT_FORM_CODE = OKE_API.G_MISS_CHAR THEN
272 l_form_rec.PRINT_FORM_CODE := NULL;
273 END IF;
274
275 IF l_form_rec.REQUIRED_FLAG = OKE_API.G_MISS_CHAR THEN
276 l_form_rec.REQUIRED_FLAG := NULL;
277 END IF;
278
279 IF l_form_rec.CUSTOMER_FURNISHED_FLAG = OKE_API.G_MISS_CHAR THEN
280 l_form_rec.CUSTOMER_FURNISHED_FLAG := NULL;
281 END IF;
282
283 IF l_form_rec.COMPLETED_FLAG = OKE_API.G_MISS_CHAR THEN
284 l_form_rec.COMPLETED_FLAG := NULL;
285 END IF;
286
287 IF l_form_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
288 l_form_rec.ATTRIBUTE_CATEGORY := NULL;
289 END IF;
290
291 IF l_form_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
292 l_form_rec.ATTRIBUTE1 := NULL;
293 END IF;
294
295 IF l_form_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
296 l_form_rec.ATTRIBUTE2 := NULL;
297 END IF;
298
299 IF l_form_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
300 l_form_rec.ATTRIBUTE3 := NULL;
301 END IF;
302
303 IF l_form_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
304 l_form_rec.ATTRIBUTE4 := NULL;
305 END IF;
306
307 IF l_form_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
308 l_form_rec.ATTRIBUTE5 := NULL;
309 END IF;
310
311 IF l_form_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
312 l_form_rec.ATTRIBUTE6 := NULL;
313 END IF;
314
315 IF l_form_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
316 l_form_rec.ATTRIBUTE7 := NULL;
317 END IF;
318
319 IF l_form_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
320 l_form_rec.ATTRIBUTE8 := NULL;
321 END IF;
322
323 IF l_form_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
324 l_form_rec.ATTRIBUTE9 := NULL;
325 END IF;
326
327 IF l_form_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
328 l_form_rec.ATTRIBUTE10 := NULL;
329 END IF;
330
331 IF l_form_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
332 l_form_rec.ATTRIBUTE11 := NULL;
333 END IF;
334
335 IF l_form_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
336 l_form_rec.ATTRIBUTE12 := NULL;
337 END IF;
338
339 IF l_form_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
340 l_form_rec.ATTRIBUTE13 := NULL;
341 END IF;
342
343 IF l_form_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
344 l_form_rec.ATTRIBUTE14 := NULL;
345 END IF;
346
347 IF l_form_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
348 l_form_rec.ATTRIBUTE15 := NULL;
349 END IF;
350
351 IF l_form_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
352 l_form_rec.CREATED_BY := NULL;
353 END IF;
354
355 IF l_form_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
356 l_form_rec.CREATION_DATE := NULL;
357 END IF;
358
359 IF l_form_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
360 l_form_rec.LAST_UPDATED_BY := NULL;
361 END IF;
362
363 IF l_form_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
364 l_form_rec.LAST_UPDATE_LOGIN := NULL;
365 END IF;
366
367 IF l_form_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
368 l_form_rec.LAST_UPDATE_DATE := NULL;
369 END IF;
370
371 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
372 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'all through null out defaults');
373 END IF;
374
375 RETURN(l_form_rec);
376
377 END null_out_defaults;
378
379
380 FUNCTION get_rec (
381 p_form_rec IN form_rec_type,
382 x_no_data_found OUT NOCOPY BOOLEAN
383 ) RETURN form_rec_type IS
384
385 CURSOR form_pk_csr ( chr_id IN NUMBER,cle_id IN NUMBER, pfm_cd IN NUMBER) IS
386 SELECT
387
388 K_HEADER_ID,
389 K_LINE_ID,
390 PRINT_FORM_CODE,
391 CREATION_DATE,
392 CREATED_BY,
393 LAST_UPDATE_DATE,
394 LAST_UPDATED_BY,
395 LAST_UPDATE_LOGIN,
396 REQUIRED_FLAG,
397 CUSTOMER_FURNISHED_FLAG,
398 COMPLETED_FLAG,
399 ATTRIBUTE_CATEGORY,
400 ATTRIBUTE1 ,
401 ATTRIBUTE2 ,
402 ATTRIBUTE3 ,
403 ATTRIBUTE4 ,
404 ATTRIBUTE5 ,
405 ATTRIBUTE6 ,
406 ATTRIBUTE7 ,
407 ATTRIBUTE8 ,
408 ATTRIBUTE9 ,
409 ATTRIBUTE10 ,
410 ATTRIBUTE11 ,
411 ATTRIBUTE12 ,
412 ATTRIBUTE13 ,
413 ATTRIBUTE14 ,
414 ATTRIBUTE15
415
416
417 FROM OKE_K_PRINT_FORMS a
418 WHERE
419 (a.K_HEADER_ID = chr_id)AND(a.PRINT_FORM_CODE=pfm_cd)
420 AND(
421 ((a.K_LINE_ID IS NULL)AND(cle_id IS NULL)) OR
422 (a.K_LINE_ID = cle_id));
423
424 l_form_pk form_pk_csr%ROWTYPE;
425 l_form_rec form_rec_type;
426 l_api_name CONSTANT VARCHAR2(30) := 'get_rec';
427
428 BEGIN
429 x_no_data_found := TRUE;
430
431 -- get current database value
432 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
433 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'get rec-pos 1');
434 END IF;
435
436 OPEN form_pk_csr(p_form_rec.K_HEADER_ID,p_form_rec.K_LINE_ID,p_form_rec.PRINT_FORM_CODE);
437 FETCH form_pk_csr INTO
438 l_form_rec.K_HEADER_ID ,
439 l_form_rec.K_LINE_ID ,
440 l_form_rec.PRINT_FORM_CODE ,
441 l_form_rec.CREATION_DATE ,
442 l_form_rec.CREATED_BY ,
443 l_form_rec.LAST_UPDATE_DATE ,
444 l_form_rec.LAST_UPDATED_BY ,
445 l_form_rec.LAST_UPDATE_LOGIN ,
446 l_form_rec.REQUIRED_FLAG ,
447 l_form_rec.CUSTOMER_FURNISHED_FLAG ,
448 l_form_rec.COMPLETED_FLAG ,
449 l_form_rec.ATTRIBUTE_CATEGORY ,
450 l_form_rec.ATTRIBUTE1 ,
451 l_form_rec.ATTRIBUTE2 ,
452 l_form_rec.ATTRIBUTE3 ,
453 l_form_rec.ATTRIBUTE4 ,
454 l_form_rec.ATTRIBUTE5 ,
455 l_form_rec.ATTRIBUTE6 ,
456 l_form_rec.ATTRIBUTE7 ,
457 l_form_rec.ATTRIBUTE8 ,
458 l_form_rec.ATTRIBUTE9 ,
459 l_form_rec.ATTRIBUTE10 ,
460 l_form_rec.ATTRIBUTE11 ,
461 l_form_rec.ATTRIBUTE12 ,
462 l_form_rec.ATTRIBUTE13 ,
463 l_form_rec.ATTRIBUTE14 ,
464 l_form_rec.ATTRIBUTE15 ;
465
466 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
467 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'get rec-pos 2');
468 x_no_data_found := form_pk_csr%NOTFOUND;
469 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'get rec-pos 3');
470 CLOSE form_pk_csr;
471 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'get rec-pos 4');
472 END IF;
473 IF(x_no_data_found) THEN
474 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
475 END IF;
479
476 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'get rec-pos 5');
478 END IF;
480 RETURN(l_form_rec);
481
482 END get_rec;
483
484
485
486 -- row level insert
487
488 PROCEDURE insert_row(
489 p_api_version IN NUMBER,
490 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
491 x_return_status OUT NOCOPY VARCHAR2,
492 x_msg_count OUT NOCOPY NUMBER,
493 x_msg_data OUT NOCOPY VARCHAR2,
494 p_form_rec IN form_rec_type,
495 x_form_rec OUT NOCOPY form_rec_type) IS
496
497 l_api_version CONSTANT NUMBER := 1;
498 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
499 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
500 l_form_rec form_rec_type;
501 l_def_form_rec form_rec_type;
502 lx_form_rec form_rec_type;
503 l_seq NUMBER;
504
505 -- FUNCTION fill_who_columns --
506 -------------------------------
507 FUNCTION fill_who_columns (
508 p_form_rec IN form_rec_type
509 ) RETURN form_rec_type IS
510
511 l_form_rec form_rec_type := p_form_rec;
512
513 BEGIN
514
515 l_form_rec.CREATION_DATE := SYSDATE;
516 l_form_rec.CREATED_BY := FND_GLOBAL.USER_ID;
517 l_form_rec.LAST_UPDATE_DATE := SYSDATE;
518 l_form_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
519 l_form_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
520 RETURN(l_form_rec);
521
522 END fill_who_columns;
523
524
525
526 FUNCTION Set_Attributes (
527 p_form_rec IN form_rec_type,
528 x_form_rec OUT NOCOPY form_rec_type
529 ) RETURN VARCHAR2 IS
530 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
531 BEGIN
532 x_form_rec := p_form_rec;
533 x_form_rec.REQUIRED_FLAG := UPPER(x_form_rec.REQUIRED_FLAG);
534 x_form_rec.CUSTOMER_FURNISHED_FLAG :=
535 UPPER(x_form_rec.CUSTOMER_FURNISHED_FLAG);
536 x_form_rec.COMPLETED_FLAG := UPPER(x_form_rec.COMPLETED_FLAG);
537
538 RETURN(l_return_status);
539
540 END Set_Attributes;
541
542
543 BEGIN -- insert
544
545 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
546 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call insert api');
547 END IF;
548
549 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
550 G_PKG_NAME,
551 p_init_msg_list,
552 l_api_version,
553 p_api_version,
554 '_PVT',
555 x_return_status);
556
557
558 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
559 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
560 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
561 RAISE OKE_API.G_EXCEPTION_ERROR;
562 END IF;
563
564 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
565 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call null out defaults');
566 END IF;
567 l_form_rec := null_out_defaults(p_form_rec);
568
569 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
570 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,' called null out defaults');
571 END IF;
572
573 --- Setting item attributes
574 l_return_status := Set_Attributes(
575 l_form_rec, -- IN
576 l_def_form_rec); -- OUT
577
578 --- If any errors happen abort API
579 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
580 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
581 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
582 RAISE OKE_API.G_EXCEPTION_ERROR;
583 END IF;
584
585 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
586 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'attributes set for insert');
587 END IF;
588
589 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
590 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
591 END IF;
592
593 l_def_form_rec := fill_who_columns(l_def_form_rec);
594
595 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
596 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'who column filled for insert');
597 END IF;
598
599 --- Validate all non-missing attributes (Item Level Validation)
600 l_return_status := Validate_Attributes(l_def_form_rec);
601
602 --- If any errors happen abort API
603 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
604 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
605
606 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
610 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
607 RAISE OKE_API.G_EXCEPTION_ERROR;
608 END IF;
609
611 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'attributes validated for insert');
612 END IF;
613 l_return_status := Validate_Record(l_def_form_rec);
614
615 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
616 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
617 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
618 RAISE OKE_API.G_EXCEPTION_ERROR;
619 END IF;
620
621
622 INSERT INTO OKE_K_PRINT_FORMS(
623
624 K_HEADER_ID ,
625 K_LINE_ID ,
626 PRINT_FORM_CODE ,
627 CREATION_DATE ,
628 CREATED_BY ,
629 LAST_UPDATE_DATE ,
630 LAST_UPDATED_BY ,
631 LAST_UPDATE_LOGIN ,
632 REQUIRED_FLAG ,
633 CUSTOMER_FURNISHED_FLAG,
634 COMPLETED_FLAG ,
635 ATTRIBUTE_CATEGORY ,
636 ATTRIBUTE1 ,
637 ATTRIBUTE2 ,
638 ATTRIBUTE3 ,
639 ATTRIBUTE4 ,
640 ATTRIBUTE5 ,
641 ATTRIBUTE6 ,
642 ATTRIBUTE7 ,
643 ATTRIBUTE8 ,
644 ATTRIBUTE9 ,
645 ATTRIBUTE10 ,
646 ATTRIBUTE11 ,
647 ATTRIBUTE12 ,
648 ATTRIBUTE13 ,
649 ATTRIBUTE14 ,
650 ATTRIBUTE15
651 )
652 VALUES(
653 l_def_form_rec.K_HEADER_ID ,
654 l_def_form_rec.K_LINE_ID ,
655 l_def_form_rec.PRINT_FORM_CODE ,
656 l_def_form_rec.CREATION_DATE ,
657 l_def_form_rec.CREATED_BY ,
658 l_def_form_rec.LAST_UPDATE_DATE ,
659 l_def_form_rec.LAST_UPDATED_BY ,
660 l_def_form_rec.LAST_UPDATE_LOGIN ,
661 l_def_form_rec.REQUIRED_FLAG ,
662 l_def_form_rec.CUSTOMER_FURNISHED_FLAG,
663 l_def_form_rec.COMPLETED_FLAG ,
664 l_def_form_rec.ATTRIBUTE_CATEGORY ,
665 l_def_form_rec.ATTRIBUTE1 ,
666 l_def_form_rec.ATTRIBUTE2 ,
667 l_def_form_rec.ATTRIBUTE3 ,
668 l_def_form_rec.ATTRIBUTE4 ,
669 l_def_form_rec.ATTRIBUTE5 ,
670 l_def_form_rec.ATTRIBUTE6 ,
671 l_def_form_rec.ATTRIBUTE7 ,
672 l_def_form_rec.ATTRIBUTE8 ,
673 l_def_form_rec.ATTRIBUTE9 ,
674 l_def_form_rec.ATTRIBUTE10 ,
675 l_def_form_rec.ATTRIBUTE11 ,
676 l_def_form_rec.ATTRIBUTE12 ,
677 l_def_form_rec.ATTRIBUTE13 ,
678 l_def_form_rec.ATTRIBUTE14 ,
679 l_def_form_rec.ATTRIBUTE15
680 );
681
682 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
683 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'record inserted');
684 END IF;
685 -- Set OUT values
686 x_form_rec := l_def_form_rec;
687
688 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
689
690 EXCEPTION
691 WHEN OKE_API.G_EXCEPTION_ERROR THEN
692 x_return_status := OKE_API.HANDLE_EXCEPTIONS
693 (
694 l_api_name,
695 G_PKG_NAME,
696 'OKE_API.G_RET_STS_ERROR',
697 x_msg_count,
698 x_msg_data,
699 '_PVT'
700 );
701 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
702 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
703 (
704 l_api_name,
705 G_PKG_NAME,
706 'OKE_API.G_RET_STS_UNEXP_ERROR',
707 x_msg_count,
708 x_msg_data,
709 '_PVT'
710 );
711 WHEN OTHERS THEN
712 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
713 (
714 l_api_name,
715 G_PKG_NAME,
716 'OTHERS',
717 x_msg_count,
718 x_msg_data,
719 '_PVT'
720 );
721 END insert_row; -- row level
722
723
724
725
726 -- table level insert
727
728 PROCEDURE insert_row(
729 p_api_version IN NUMBER,
730 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
731 x_return_status OUT NOCOPY VARCHAR2,
732 x_msg_count OUT NOCOPY NUMBER,
733 x_msg_data OUT NOCOPY VARCHAR2,
734 p_form_tbl IN form_tbl_type,
735 x_form_tbl OUT NOCOPY form_tbl_type) IS
736
737 l_api_version CONSTANT NUMBER := 1;
738 l_api_name CONSTANT VARCHAR2(30) := 'TBL_insert_row';
739 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
740 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
741 i NUMBER := 0;
742 BEGIN
743
744 OKE_API.init_msg_list(p_init_msg_list);
745 -- Make sure PL/SQL table has records in it before passing
746 IF (p_form_tbl.COUNT > 0) THEN
747 i := p_form_tbl.FIRST;
748 LOOP
749 insert_row (
750 p_api_version => p_api_version,
751 p_init_msg_list => OKE_API.G_FALSE,
752 x_return_status => x_return_status,
756 p_form_rec => p_form_tbl(i),
753 x_msg_count => x_msg_count,
754 x_msg_data => x_msg_data,
755
757 x_form_rec => x_form_tbl(i));
758
759 -- store the highest degree of error
760 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
761 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
762 l_overall_status := x_return_status;
763 End If;
764 End If;
765
766 EXIT WHEN (i = p_form_tbl.LAST);
767
768 i := p_form_tbl.NEXT(i);
769 END LOOP;
770 -- return overall status
771 x_return_status := l_overall_status;
772 END IF;
773
774 EXCEPTION
775 WHEN OKE_API.G_EXCEPTION_ERROR THEN
776 x_return_status := OKE_API.HANDLE_EXCEPTIONS
777 (
778 l_api_name,
779 G_PKG_NAME,
780 'OKE_API.G_RET_STS_ERROR',
781 x_msg_count,
782 x_msg_data,
783 '_PVT'
784 );
785 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
786 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
787 (
788 l_api_name,
789 G_PKG_NAME,
790 'OKE_API.G_RET_STS_UNEXP_ERROR',
791 x_msg_count,
792 x_msg_data,
793 '_PVT'
794 );
795 WHEN OTHERS THEN
796 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
797 (
798 l_api_name,
799 G_PKG_NAME,
800 'OTHERS',
801 x_msg_count,
802 x_msg_data,
803 '_PVT'
804 );
805 END insert_row; -- table level
806
807
808
809
810
811
812
813
814 PROCEDURE update_row(
815 p_api_version IN NUMBER,
816 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
817 x_return_status OUT NOCOPY VARCHAR2,
818 x_msg_count OUT NOCOPY NUMBER,
819 x_msg_data OUT NOCOPY VARCHAR2,
820 p_form_rec IN form_rec_type,
821 x_form_rec OUT NOCOPY form_rec_type) IS
822
823 l_api_version CONSTANT NUMBER := 1.0;
824 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
825 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
826 l_form_rec form_rec_type := p_form_rec;
827 l_def_form_rec form_rec_type;
828 lx_form_rec form_rec_type;
829
830 -------------------------------
831 -- FUNCTION fill_who_columns --
832 -------------------------------
833 FUNCTION fill_who_columns (
834 p_form_rec IN form_rec_type
835 ) RETURN form_rec_type IS
836
837 l_form_rec form_rec_type := p_form_rec;
838
839 BEGIN
840 l_form_rec.LAST_UPDATE_DATE := SYSDATE;
841 l_form_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
842 l_form_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
843 RETURN(l_form_rec);
844 END fill_who_columns;
845
846 ----------------------------------
847 -- FUNCTION populate_new_record --
848 ----------------------------------
849 FUNCTION populate_new_record (
850 p_form_rec IN form_rec_type,
851 x_form_rec OUT NOCOPY form_rec_type
852 ) RETURN VARCHAR2 IS
853
854 l_form_rec form_rec_type;
855 l_row_notfound BOOLEAN := TRUE;
856 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
857
858 BEGIN
859
860 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
861 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call populate new record');
862 END IF;
863
864 x_form_rec := p_form_rec;
865
866 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
867 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call get rec');
868 END IF;
869 -- Get current database values
870
871 l_form_rec := get_rec(p_form_rec, l_row_notfound);
872
873 IF (l_row_notfound) THEN
874 l_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
875 END IF;
876
877 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
878 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'record got');
879 END IF;
880
881 IF x_form_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
882 x_form_rec.CREATION_DATE := l_form_rec.CREATION_DATE;
883 END IF;
884
885 IF x_form_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
886 x_form_rec.CREATED_BY := l_form_rec.CREATED_BY;
887 END IF;
888
889 IF x_form_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
890 x_form_rec.LAST_UPDATE_DATE := l_form_rec.LAST_UPDATE_DATE;
891 END IF;
892
893 IF x_form_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
894 x_form_rec.LAST_UPDATED_BY := l_form_rec.LAST_UPDATED_BY ;
895 END IF;
896
897 IF x_form_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
898 x_form_rec.LAST_UPDATE_LOGIN := l_form_rec.LAST_UPDATE_LOGIN;
902 x_form_rec.REQUIRED_FLAG := l_form_rec.REQUIRED_FLAG;
899 END IF;
900
901 IF x_form_rec.REQUIRED_FLAG = OKE_API.G_MISS_CHAR THEN
903 END IF;
904
905 IF x_form_rec.CUSTOMER_FURNISHED_FLAG = OKE_API.G_MISS_CHAR THEN
906 x_form_rec.CUSTOMER_FURNISHED_FLAG := l_form_rec.CUSTOMER_FURNISHED_FLAG;
907 END IF;
908
909 IF x_form_rec.COMPLETED_FLAG = OKE_API.G_MISS_CHAR THEN
910 x_form_rec.COMPLETED_FLAG := l_form_rec.COMPLETED_FLAG;
911 END IF;
912
913 IF x_form_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
914 x_form_rec.ATTRIBUTE_CATEGORY := l_form_rec.ATTRIBUTE_CATEGORY;
915 END IF;
916
917 IF x_form_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
918 x_form_rec.ATTRIBUTE1 := l_form_rec.ATTRIBUTE1;
919 END IF;
920
921 IF x_form_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
922 x_form_rec.ATTRIBUTE2 := l_form_rec.ATTRIBUTE2;
923 END IF;
924
925 IF x_form_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
926 x_form_rec.ATTRIBUTE3 := l_form_rec.ATTRIBUTE3;
927 END IF;
928
929 IF x_form_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
930 x_form_rec.ATTRIBUTE4 := l_form_rec.ATTRIBUTE4;
931 END IF;
932
933 IF x_form_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
934 x_form_rec.ATTRIBUTE5 := l_form_rec.ATTRIBUTE5;
935 END IF;
936
937 IF x_form_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
938 x_form_rec.ATTRIBUTE6 := l_form_rec.ATTRIBUTE6;
939 END IF;
940
941 IF x_form_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
942 x_form_rec.ATTRIBUTE7 := l_form_rec.ATTRIBUTE7;
943 END IF;
944
945 IF x_form_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
946 x_form_rec.ATTRIBUTE8 := l_form_rec.ATTRIBUTE8;
947 END IF;
948
949 IF x_form_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
950 x_form_rec.ATTRIBUTE9 := l_form_rec.ATTRIBUTE9;
951 END IF;
952
953 IF x_form_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
954 x_form_rec.ATTRIBUTE10 := l_form_rec.ATTRIBUTE10;
955 END IF;
956
957 IF x_form_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
958 x_form_rec.ATTRIBUTE11 := l_form_rec.ATTRIBUTE11;
959 END IF;
960
961 IF x_form_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
962 x_form_rec.ATTRIBUTE12 := l_form_rec.ATTRIBUTE12;
963 END IF;
964
965 IF x_form_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
966 x_form_rec.ATTRIBUTE13 := l_form_rec.ATTRIBUTE13;
967 END IF;
968
969 IF x_form_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
970 x_form_rec.ATTRIBUTE14 := l_form_rec.ATTRIBUTE14;
971 END IF;
972
973 IF x_form_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
974 x_form_rec.ATTRIBUTE15 := l_form_rec.ATTRIBUTE15;
975 END IF;
976
977 RETURN(l_return_status);
978 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
979 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'all the way through populate new record');
980 END IF;
981
982 END populate_new_record;
983
984
985
986 FUNCTION set_attributes(
987 p_form_rec IN form_rec_type,
988 x_form_rec OUT NOCOPY form_rec_type
989 ) RETURN VARCHAR2 IS
990 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
991 BEGIN
992 x_form_rec := p_form_rec;
993 x_form_rec.REQUIRED_FLAG := UPPER(x_form_rec.REQUIRED_FLAG);
994 x_form_rec.CUSTOMER_FURNISHED_FLAG :=
995 UPPER(x_form_rec.CUSTOMER_FURNISHED_FLAG);
996 x_form_rec.COMPLETED_FLAG := UPPER(x_form_rec.COMPLETED_FLAG);
997 RETURN(l_return_status);
998
999 END Set_Attributes;
1000
1001
1002 BEGIN -- update row
1003 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1004 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call api initialization');
1005 END IF;
1006
1007 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1008 G_PKG_NAME,
1009 p_init_msg_list,
1010 l_api_version,
1011 p_api_version,
1012 '_PVT',
1013 x_return_status);
1014
1015
1016 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1017 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1018 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1019 RAISE OKE_API.G_EXCEPTION_ERROR;
1020 END IF;
1021
1022 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1023 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call not pvt');
1024 END IF;
1025
1026 l_return_status := Set_Attributes(
1027 p_form_rec, -- IN
1028 l_form_rec); -- OUT
1029
1030 --- If any errors happen abort API
1031 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1032 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1033 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1034 RAISE OKE_API.G_EXCEPTION_ERROR;
1035 END IF;
1039
1036 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1037 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'attributes set');
1038 END IF;
1040 l_return_status := populate_new_record(l_form_rec, l_def_form_rec);
1041
1042 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1043 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1044 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1045 RAISE OKE_API.G_EXCEPTION_ERROR;
1046 END IF;
1047 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1048 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'record populated');
1049 END IF;
1050
1051 l_def_form_rec := fill_who_columns(l_def_form_rec);
1052 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1053 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'who column filled');
1054 END IF;
1055
1056 --- Validate all non-missing attributes (Item Level Validation)
1057 l_return_status := Validate_Attributes(l_def_form_rec);
1058
1059 --- If any errors happen abort API
1060 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1061 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1062 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1063 RAISE OKE_API.G_EXCEPTION_ERROR;
1064 END IF;
1065 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1066 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'attributes validated');
1067 END IF;
1068 l_return_status := Validate_Record(l_def_form_rec);
1069 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1070 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1071 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1072 RAISE OKE_API.G_EXCEPTION_ERROR;
1073 END IF;
1074
1075 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1076 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'update base table');
1077 END IF;
1078
1079 UPDATE OKE_K_PRINT_FORMS
1080 SET
1081 CREATION_DATE = l_def_form_rec.CREATION_DATE,
1082 CREATED_BY = l_def_form_rec.CREATED_BY,
1083 LAST_UPDATE_DATE = l_def_form_rec.LAST_UPDATE_DATE,
1084 LAST_UPDATED_BY = l_def_form_rec.LAST_UPDATED_BY,
1085 LAST_UPDATE_LOGIN = l_def_form_rec.LAST_UPDATE_LOGIN,
1086 REQUIRED_FLAG = l_def_form_rec.REQUIRED_FLAG,
1087 CUSTOMER_FURNISHED_FLAG = l_def_form_rec.CUSTOMER_FURNISHED_FLAG,
1088 COMPLETED_FLAG = l_def_form_rec.COMPLETED_FLAG,
1089
1090 ATTRIBUTE_CATEGORY = l_def_form_rec.ATTRIBUTE_CATEGORY,
1091 ATTRIBUTE1 = l_def_form_rec.ATTRIBUTE1,
1092 ATTRIBUTE2 = l_def_form_rec.ATTRIBUTE2,
1093 ATTRIBUTE3 = l_def_form_rec.ATTRIBUTE3,
1094 ATTRIBUTE4 = l_def_form_rec.ATTRIBUTE4,
1095 ATTRIBUTE5 = l_def_form_rec.ATTRIBUTE5,
1096 ATTRIBUTE6 = l_def_form_rec.ATTRIBUTE6,
1097 ATTRIBUTE7 = l_def_form_rec.ATTRIBUTE7,
1098 ATTRIBUTE8 = l_def_form_rec.ATTRIBUTE8,
1099 ATTRIBUTE9 = l_def_form_rec.ATTRIBUTE9,
1100 ATTRIBUTE10 = l_def_form_rec.ATTRIBUTE10,
1101 ATTRIBUTE11 = l_def_form_rec.ATTRIBUTE11,
1102 ATTRIBUTE12 = l_def_form_rec.ATTRIBUTE12,
1103 ATTRIBUTE13 = l_def_form_rec.ATTRIBUTE13,
1104 ATTRIBUTE14 = l_def_form_rec.ATTRIBUTE14,
1105 ATTRIBUTE15 = l_def_form_rec.ATTRIBUTE15
1106 WHERE
1107 (K_HEADER_ID = l_def_form_rec.K_HEADER_ID)AND
1108 (PRINT_FORM_CODE = l_def_form_rec.PRINT_FORM_CODE) AND
1109 ((K_LINE_ID = l_def_form_rec.K_LINE_ID)OR
1110 (K_LINE_ID IS NULL)AND(l_def_form_rec.K_LINE_ID IS NULL));
1111
1112 x_form_rec := l_def_form_rec;
1113
1114 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1115
1116 EXCEPTION
1117 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1118 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1119 (
1120 l_api_name,
1121 G_PKG_NAME,
1122 'OKE_API.G_RET_STS_ERROR',
1123 x_msg_count,
1124 x_msg_data,
1125 '_PVT'
1126 );
1127 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1128 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1129 (
1130 l_api_name,
1131 G_PKG_NAME,
1132 'OKE_API.G_RET_STS_UNEXP_ERROR',
1133 x_msg_count,
1134 x_msg_data,
1135 '_PVT'
1136 );
1137 WHEN OTHERS THEN
1138 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1139 (
1140 l_api_name,
1141 G_PKG_NAME,
1142 'OTHERS',
1143 x_msg_count,
1144 x_msg_data,
1145 '_PVT'
1146 );
1147 END update_row; -- row level update
1148
1149
1150
1151 PROCEDURE update_row(
1152 p_api_version IN NUMBER,
1153 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1154 x_return_status OUT NOCOPY VARCHAR2,
1155 x_msg_count OUT NOCOPY NUMBER,
1156 x_msg_data OUT NOCOPY VARCHAR2,
1157 p_form_tbl IN form_tbl_type,
1158 x_form_tbl OUT NOCOPY form_tbl_type) IS
1159
1160 l_api_version CONSTANT NUMBER := 1.0;
1161 l_api_name CONSTANT VARCHAR2(30) := 'TBL_update_row';
1162
1163
1164 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1168
1165 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1166 i NUMBER := 0;
1167 BEGIN
1169 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1170 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'begin table type of update in pvt');
1171 END IF;
1172
1173 OKE_API.init_msg_list(p_init_msg_list);
1174 -- Make sure PL/SQL table has records in it before passing
1175 IF (p_form_tbl.COUNT > 0) THEN
1176 i := p_form_tbl.FIRST;
1177 LOOP
1178
1179 update_row (
1180 p_api_version => p_api_version,
1181 p_init_msg_list => OKE_API.G_FALSE,
1182 x_return_status => x_return_status,
1183 x_msg_count => x_msg_count,
1184 x_msg_data => x_msg_data,
1185 p_form_rec => p_form_tbl(i),
1186 x_form_rec => x_form_tbl(i));
1187
1188 -- store the highest degree of error
1189 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1190 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1191 l_overall_status := x_return_status;
1192 End If;
1193 End If;
1194
1195 EXIT WHEN (i = p_form_tbl.LAST);
1196 i := p_form_tbl.NEXT(i);
1197 END LOOP;
1198 -- return overall status
1199 x_return_status := l_overall_status;
1200 END IF;
1201
1202 EXCEPTION
1203 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1204 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1205 (
1206 l_api_name,
1207 G_PKG_NAME,
1208 'OKE_API.G_RET_STS_ERROR',
1209 x_msg_count,
1210 x_msg_data,
1211 '_PVT'
1212 );
1213
1214 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1215 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1216 (
1217 l_api_name,
1218 G_PKG_NAME,
1219 'OKE_API.G_RET_STS_UNEXP_ERROR',
1220 x_msg_count,
1221 x_msg_data,
1222 '_PVT'
1223 );
1224 WHEN OTHERS THEN
1225 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1226 (
1227 l_api_name,
1228 G_PKG_NAME,
1229 'OTHERS',
1230 x_msg_count,
1231 x_msg_data,
1232 '_PVT'
1233 );
1234 END update_row; -- table level update
1235
1236
1237 -- by line id and pf code
1238 PROCEDURE delete_row(
1239 p_api_version IN NUMBER,
1240 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1241 x_return_status OUT NOCOPY VARCHAR2,
1242 x_msg_count OUT NOCOPY NUMBER,
1243 x_msg_data OUT NOCOPY VARCHAR2,
1244 p_cle_id IN NUMBER,
1245 p_pfm_cd OKE_K_PRINT_FORMS.PRINT_FORM_CODE%TYPE
1246 ) IS
1247
1248 l_api_version CONSTANT NUMBER := 1;
1249 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1250 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1251
1252
1253 BEGIN
1254
1255 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1256 p_init_msg_list,
1257 '_PVT',
1258 x_return_status);
1259
1260
1261 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1262 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1263 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1264 RAISE OKE_API.G_EXCEPTION_ERROR;
1265 END IF;
1266
1267 DELETE FROM OKE_K_PRINT_FORMS
1268 WHERE K_LINE_ID = p_cle_id AND PRINT_FORM_CODE = p_pfm_cd;
1269
1270 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1271
1272 EXCEPTION
1273 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1274 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1275 (
1276 l_api_name,
1277 G_PKG_NAME,
1278 'OKE_API.G_RET_STS_ERROR',
1279 x_msg_count,
1280 x_msg_data,
1281 '_PVT'
1282 );
1283 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1284 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1285 (
1286 l_api_name,
1287 G_PKG_NAME,
1288 'OKE_API.G_RET_STS_UNEXP_ERROR',
1289 x_msg_count,
1290 x_msg_data,
1291 '_PVT'
1292 );
1293 WHEN OTHERS THEN
1294 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1295 (
1296 l_api_name,
1297 G_PKG_NAME,
1298 'OTHERS',
1299 x_msg_count,
1300 x_msg_data,
1301 '_PVT'
1302 );
1303 END delete_row;
1304
1305 PROCEDURE delete_row(
1306 p_api_version IN NUMBER,
1307 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1308 x_return_status OUT NOCOPY VARCHAR2,
1309 x_msg_count OUT NOCOPY NUMBER,
1310 x_msg_data OUT NOCOPY VARCHAR2,
1311 p_chr_id IN NUMBER,
1312 p_pfm_cd OKE_K_PRINT_FORMS.PRINT_FORM_CODE%TYPE
1316 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1313 ) IS
1314
1315 l_api_version CONSTANT NUMBER := 1;
1317 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1318
1319
1320 BEGIN
1321
1322 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1323 p_init_msg_list,
1324 '_PVT',
1325 x_return_status);
1326
1327
1328 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1329 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1330 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1331 RAISE OKE_API.G_EXCEPTION_ERROR;
1332 END IF;
1333
1334 DELETE FROM OKE_K_PRINT_FORMS
1335 WHERE (K_HEADER_ID=p_chr_id) AND (PRINT_FORM_CODE=p_pfm_cd) AND (K_LINE_ID IS NULL);
1336
1337 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1338
1339 EXCEPTION
1340 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1341 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1342 (
1343 l_api_name,
1344 G_PKG_NAME,
1345 'OKE_API.G_RET_STS_ERROR',
1346 x_msg_count,
1347 x_msg_data,
1348 '_PVT'
1349 );
1350 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1351 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1352 (
1353 l_api_name,
1354 G_PKG_NAME,
1355 'OKE_API.G_RET_STS_UNEXP_ERROR',
1356 x_msg_count,
1357 x_msg_data,
1358 '_PVT'
1359 );
1360 WHEN OTHERS THEN
1361 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1362 (
1363 l_api_name,
1364 G_PKG_NAME,
1365 'OTHERS',
1366 x_msg_count,
1367 x_msg_data,
1368 '_PVT'
1369 );
1370 END delete_row;
1371
1372
1373 PROCEDURE delete_row(
1374 p_api_version IN NUMBER,
1375 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1376 x_return_status OUT NOCOPY VARCHAR2,
1377 x_msg_count OUT NOCOPY NUMBER,
1378 x_msg_data OUT NOCOPY VARCHAR2,
1379 p_form_rec IN form_rec_type) IS
1380
1381 l_api_version CONSTANT NUMBER := 1;
1382 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1383 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1384 l_form_rec form_rec_type := p_form_rec;
1385
1386 BEGIN
1387
1388 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1389 p_init_msg_list,
1390 '_PVT',
1391 x_return_status);
1392
1393
1394 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1395 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1396 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1397 RAISE OKE_API.G_EXCEPTION_ERROR;
1398 END IF;
1399
1400 IF(p_form_rec.K_LINE_ID IS NULL) THEN
1401 delete_row (
1402 p_api_version => p_api_version,
1403 p_init_msg_list => OKE_API.G_FALSE,
1404 x_return_status => x_return_status,
1405 x_msg_count => x_msg_count,
1406 x_msg_data => x_msg_data,
1407 p_chr_id => p_form_rec.K_HEADER_ID,
1408 p_pfm_cd => p_form_rec.PRINT_FORM_CODE);
1409 ELSE
1410 delete_row (
1411 p_api_version => p_api_version,
1412 p_init_msg_list => OKE_API.G_FALSE,
1413 x_return_status => x_return_status,
1414 x_msg_count => x_msg_count,
1415 x_msg_data => x_msg_data,
1416 p_cle_id => p_form_rec.K_LINE_ID,
1417 p_pfm_cd => p_form_rec.PRINT_FORM_CODE);
1418 END IF;
1419 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1420
1421 EXCEPTION
1422 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1423 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1424 (
1425 l_api_name,
1426 G_PKG_NAME,
1427 'OKE_API.G_RET_STS_ERROR',
1428 x_msg_count,
1429 x_msg_data,
1430 '_PVT'
1431 );
1432 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1433 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1434 (
1435 l_api_name,
1436 G_PKG_NAME,
1437 'OKE_API.G_RET_STS_UNEXP_ERROR',
1438 x_msg_count,
1439 x_msg_data,
1440 '_PVT'
1441 );
1442 WHEN OTHERS THEN
1443 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1444 (
1445 l_api_name,
1446 G_PKG_NAME,
1447 'OTHERS',
1448 x_msg_count,
1449 x_msg_data,
1450 '_PVT'
1451 );
1452 END delete_row;
1453
1454
1455 -- table level delete
1456
1457 PROCEDURE delete_row(
1461 x_msg_count OUT NOCOPY NUMBER,
1458 p_api_version IN NUMBER,
1459 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1460 x_return_status OUT NOCOPY VARCHAR2,
1462 x_msg_data OUT NOCOPY VARCHAR2,
1463 p_form_tbl IN form_tbl_type) IS
1464
1465 l_api_version CONSTANT NUMBER := 1;
1466 l_api_name CONSTANT VARCHAR2(30) := 'TBL_delete_row';
1467 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1468 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1469 i NUMBER := 0;
1470 BEGIN
1471 OKE_API.init_msg_list(p_init_msg_list);
1472
1473 -- Make sure PL/SQL table has records in it before passing
1474 IF (p_form_tbl.COUNT > 0) THEN
1475 i := p_form_tbl.FIRST;
1476 LOOP
1477 delete_row (
1478 p_api_version => p_api_version,
1479 p_init_msg_list => OKE_API.G_FALSE,
1480 x_return_status => x_return_status,
1481 x_msg_count => x_msg_count,
1482 x_msg_data => x_msg_data,
1483 p_form_rec => p_form_tbl(i));
1484
1485
1486
1487 -- store the highest degree of error
1488 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1489 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1490 l_overall_status := x_return_status;
1491 End If;
1492 End If;
1493
1494 EXIT WHEN (i = p_form_tbl.LAST);
1495 i := p_form_tbl.NEXT(i);
1496 END LOOP;
1497
1498 -- return overall status
1499 x_return_status := l_overall_status;
1500 END IF;
1501
1502 EXCEPTION
1503 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1504 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1505 (
1506 l_api_name,
1507 G_PKG_NAME,
1508 'OKE_API.G_RET_STS_ERROR',
1509 x_msg_count,
1510 x_msg_data,
1511 '_PVT'
1512 );
1513 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1514 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1515 (
1516 l_api_name,
1517 G_PKG_NAME,
1518 'OKE_API.G_RET_STS_UNEXP_ERROR',
1519 x_msg_count,
1520 x_msg_data,
1521 '_PVT'
1522 );
1523 WHEN OTHERS THEN
1524 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1525 (
1526 l_api_name,
1527 G_PKG_NAME,
1528 'OTHERS',
1529 x_msg_count,
1530 x_msg_data,
1531 '_PVT'
1532 );
1533 END delete_row; -- table level delete
1534
1535
1536 -- validate row
1537
1538 PROCEDURE validate_row(
1539 p_api_version IN NUMBER,
1540 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1541 x_return_status OUT NOCOPY VARCHAR2,
1542 x_msg_count OUT NOCOPY NUMBER,
1543 x_msg_data OUT NOCOPY VARCHAR2,
1544 p_form_rec IN form_rec_type
1545 ) IS
1546
1547 l_api_version CONSTANT NUMBER := 1;
1548 l_api_name CONSTANT VARCHAR2(30) := 'B_validate_row';
1549 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1550 l_form_rec form_rec_type := p_form_rec;
1551
1552 BEGIN
1553 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1554 G_PKG_NAME,
1555 p_init_msg_list,
1556 l_api_version,
1557 p_api_version,
1558 '_PVT',
1559 x_return_status);
1560 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1561 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1562 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1563
1564 RAISE OKE_API.G_EXCEPTION_ERROR;
1565 END IF;
1566 --- Validate all non-missing attributes (Item Level Validation)
1567 l_return_status := Validate_Attributes(l_form_rec);
1568 --- If any errors happen abort API
1569 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1570 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1571 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1572 RAISE OKE_API.G_EXCEPTION_ERROR;
1573 END IF;
1574 l_return_status := Validate_Record(l_form_rec);
1575
1576 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1577 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1578 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1579 RAISE OKE_API.G_EXCEPTION_ERROR;
1580 END IF;
1581 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1582 EXCEPTION
1583 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1584 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1585 (
1586 l_api_name,
1587 G_PKG_NAME,
1588 'OKE_API.G_RET_STS_ERROR',
1589 x_msg_count,
1590 x_msg_data,
1591 '_PVT'
1592 );
1593 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1594 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1595 (
1596 l_api_name,
1597 G_PKG_NAME,
1601 '_PVT'
1598 'OKE_API.G_RET_STS_UNEXP_ERROR',
1599 x_msg_count,
1600 x_msg_data,
1602 );
1603 WHEN OTHERS THEN
1604 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1605 (
1606 l_api_name,
1607 G_PKG_NAME,
1608 'OTHERS',
1609 x_msg_count,
1610 x_msg_data,
1611 '_PVT'
1612 );
1613 END validate_row;
1614
1615 PROCEDURE validate_row(
1616 p_api_version IN NUMBER,
1617 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1618 x_return_status OUT NOCOPY VARCHAR2,
1619 x_msg_count OUT NOCOPY NUMBER,
1620 x_msg_data OUT NOCOPY VARCHAR2,
1621 p_form_tbl IN form_tbl_type
1622 ) IS
1623
1624 l_api_version CONSTANT NUMBER := 1;
1625 l_api_name CONSTANT VARCHAR2(30) := 'TBL_validate_row';
1626 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1627 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1628 i NUMBER := 0;
1629 BEGIN
1630 OKE_API.init_msg_list(p_init_msg_list);
1631 -- Make sure PL/SQL table has records in it before passing
1632 IF (p_form_tbl.COUNT > 0) THEN
1633 i := p_form_tbl.FIRST;
1634 LOOP
1635 validate_row (
1636 p_api_version => p_api_version,
1637 p_init_msg_list => OKE_API.G_FALSE,
1638 x_return_status => x_return_status,
1639 x_msg_count => x_msg_count,
1640 x_msg_data => x_msg_data,
1641 p_form_rec => p_form_tbl(i));
1642
1643 -- store the highest degree of error
1644 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1645 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1646 l_overall_status := x_return_status;
1647 End If;
1648 End If;
1649
1650 EXIT WHEN (i = p_form_tbl.LAST);
1651 i := p_form_tbl.NEXT(i);
1652 END LOOP;
1653 -- return overall status
1654 x_return_status := l_overall_status;
1655 END IF;
1656
1657 EXCEPTION
1658 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1659 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1660 (
1661 l_api_name,
1662 G_PKG_NAME,
1663 'OKE_API.G_RET_STS_ERROR',
1664 x_msg_count,
1665 x_msg_data,
1666 '_PVT'
1667 );
1668 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1669 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1670 (
1671 l_api_name,
1672 G_PKG_NAME,
1673 'OKE_API.G_RET_STS_UNEXP_ERROR',
1674 x_msg_count,
1675 x_msg_data,
1676 '_PVT'
1677 );
1678 WHEN OTHERS THEN
1679 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1680 (
1681 l_api_name,
1682 G_PKG_NAME,
1683 'OTHERS',
1684 x_msg_count,
1685 x_msg_data,
1686 '_PVT'
1687 );
1688 END validate_row;
1689
1690
1691
1692
1693 PROCEDURE lock_row(
1694 p_api_version IN NUMBER,
1695 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1696 x_return_status OUT NOCOPY VARCHAR2,
1697 x_msg_count OUT NOCOPY NUMBER,
1698 x_msg_data OUT NOCOPY VARCHAR2,
1699 p_form_rec IN form_rec_type) IS
1700
1701
1702 l_api_version CONSTANT NUMBER := 1;
1703 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1704 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1705 l_row_notfound BOOLEAN := FALSE;
1706
1707 l_chr_id NUMBER;
1708 l_cle_id NUMBER;
1709 l_pfm_cd OKE_K_PRINT_FORMS.PRINT_FORM_CODE%TYPE;
1710
1711 E_Resource_Busy EXCEPTION;
1712 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1713
1714
1715 CURSOR lock_csr (p IN form_rec_type) IS
1716 SELECT k_header_id,k_line_id,print_form_code FROM oke_k_print_forms a
1717 WHERE
1718 (a.K_HEADER_ID = p.K_HEADER_ID)AND(a.PRINT_FORM_CODE=p.PRINT_FORM_CODE)
1719 AND(
1720 ((a.K_LINE_ID IS NULL)AND(p.K_LINE_ID IS NULL)) OR
1721 (a.K_LINE_ID = p.K_LINE_ID))
1722 FOR UPDATE NOWAIT;
1723
1724
1725 BEGIN
1726 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1727 p_init_msg_list,
1728 '_PVT',
1729 x_return_status);
1730 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1731 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1732 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1733 RAISE OKE_API.G_EXCEPTION_ERROR;
1734 END IF;
1735
1736
1737 BEGIN
1738 OPEN lock_csr(p_form_rec);
1739 FETCH lock_csr INTO l_chr_id,l_cle_id,l_pfm_cd;
1740 l_row_notfound := lock_csr%NOTFOUND;
1741 CLOSE lock_csr;
1742
1743
1744 EXCEPTION
1745 WHEN E_Resource_Busy THEN
1746 IF (lock_csr%ISOPEN) THEN
1747 CLOSE lock_csr;
1748 END IF;
1749 OKE_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1750 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1751 END;
1752
1753
1754 IF (l_row_notfound) THEN
1755 OKE_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1756 RAISE OKE_API.G_EXCEPTION_ERROR;
1757 END IF;
1758
1759 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1760
1761 EXCEPTION
1762 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1763 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1764 (
1765 l_api_name,
1766 G_PKG_NAME,
1767 'OKE_API.G_RET_STS_ERROR',
1768 x_msg_count,
1769 x_msg_data,
1770 '_PVT'
1771 );
1772 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1773 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1774 (
1775 l_api_name,
1776 G_PKG_NAME,
1777 'OKE_API.G_RET_STS_UNEXP_ERROR',
1778 x_msg_count,
1779 x_msg_data,
1780 '_PVT'
1781 );
1782 WHEN OTHERS THEN
1783 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1784 (
1785 l_api_name,
1786 G_PKG_NAME,
1787 'OTHERS',
1788 x_msg_count,
1789 x_msg_data,
1790 '_PVT'
1791 );
1792 END lock_row;
1793
1794
1795 END OKE_FORM_PVT;
1796