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