[Home] [Help]
PACKAGE BODY: APPS.OKC_KSQ_PVT
Source
1 PACKAGE BODY OKC_KSQ_PVT AS
2 /* $Header: OKCSKSQB.pls 120.2 2006/08/24 09:39:12 npalepu noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 Type seq_header Is Record (
7 line_code okc_k_seq_header.line_code%TYPE,
8 site_yn okc_k_seq_header.site_yn%TYPE,
9 bg_ou_none okc_k_seq_header.bg_ou_none%TYPE,
10 cls_scs_none okc_k_seq_header.cls_scs_none%TYPE,
11 user_function_yn okc_k_seq_header.user_function_yn%TYPE,
12 pdf_id okc_k_seq_header.pdf_id%TYPE,
13 manual_override_yn okc_k_seq_header.manual_override_yn%TYPE);
14
15 g_seq_header seq_header;
16
17 Type doc_sequence_id_tbl Is Table Of
18 okc_k_seq_lines.doc_sequence_id%TYPE Index By Binary_Integer;
19 Type business_group_id_tbl Is Table Of
20 okc_k_seq_lines.business_group_id%TYPE Index By Binary_Integer;
21 Type operating_unit_id_tbl Is Table Of
22 okc_k_seq_lines.operating_unit_id%TYPE Index By Binary_Integer;
23 Type cls_code_tbl Is Table Of
24 okc_k_seq_lines.cls_code%TYPE Index By Binary_Integer;
25 Type scs_code_tbl Is Table Of
26 okc_k_seq_lines.scs_code%TYPE Index By Binary_Integer;
27 Type manual_override_yn_tbl Is Table Of
28 okc_k_seq_lines.manual_override_yn%TYPE Index By Binary_Integer;
29 Type contract_number_prefix_tbl Is Table Of
30 okc_k_seq_lines.contract_number_prefix%TYPE Index By Binary_Integer;
31 Type contract_number_suffix_tbl Is Table Of
32 okc_k_seq_lines.contract_number_suffix%TYPE Index By Binary_Integer;
33 Type number_format_length_tbl Is Table Of
34 okc_k_seq_lines.number_format_length%TYPE Index By Binary_Integer;
35 Type start_seq_no_tbl Is Table Of
36 okc_k_seq_lines.start_seq_no%TYPE Index By Binary_Integer;
37 Type end_seq_no_tbl Is Table Of
38 okc_k_seq_lines.end_seq_no%TYPE Index By Binary_Integer;
39
40 g_doc_sequence_id_tbl doc_sequence_id_tbl;
41 g_business_group_id_tbl business_group_id_tbl;
42 g_operating_unit_id_tbl operating_unit_id_tbl;
43 g_cls_code_tbl cls_code_tbl;
44 g_scs_code_tbl scs_code_tbl;
45 g_manual_override_yn_tbl manual_override_yn_tbl;
46 g_contract_number_prefix_tbl contract_number_prefix_tbl;
47 g_contract_number_suffix_tbl contract_number_suffix_tbl;
48 g_number_format_length_tbl number_format_length_tbl;
49 g_start_seq_no_tbl start_seq_no_tbl;
50 g_end_seq_no_tbl end_seq_no_tbl;
51
52 g_index Number;
53 g_seq_status Varchar2(30);
54 g_session_id Varchar2(255) := OKC_API.G_MISS_CHAR;
55 g_business_group_id Number := OKC_API.G_MISS_NUM;
56 g_operating_unit_id Number := OKC_API.G_MISS_NUM;
57 g_scs_code Varchar2(30) := OKC_API.G_MISS_CHAR;
58 /************************ HAND-CODED *********************************/
59 -- Start of comments
60 --
61 -- Procedure Name : validate_line_code
62 -- Description :
63 -- Business Rules :
64 -- Parameters :
65 -- Version : 1.0
66 -- End of comments
67 PROCEDURE validate_line_code(x_return_status OUT NOCOPY VARCHAR2,
68 p_ksqv_rec IN ksqv_rec_type) is
69
70 Begin
71 -- initialize return status
72 x_return_status := OKC_API.G_RET_STS_SUCCESS;
73
74 -- check that line_code has some valid value
75 If (p_ksqv_rec.line_code = OKC_API.G_MISS_CHAR or
76 p_ksqv_rec.line_code IS NULL) Then
77 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
78 p_msg_name => g_required_value,
79 p_token1 => g_col_name_token,
80 p_token1_value => 'line_code');
81 -- set error flag
82 x_return_status := OKC_API.G_RET_STS_ERROR;
83 End If;
84
85 exception
86 when OTHERS then
87 -- store SQL error message on message stack
88 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
89 p_msg_name => g_unexpected_error,
90 p_token1 => g_sqlcode_token,
91 p_token1_value => sqlcode,
92 p_token2 => g_sqlerrm_token,
93 p_token2_value => sqlerrm);
94 -- set error flag as UNEXPETED error
95 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
96
97 End validate_line_code;
98
99 -- Start of comments
100 --
101 -- Procedure Name : validate_site_yn
102 -- Description :
103 -- Business Rules :
104 -- Parameters :
105 -- Version : 1.0
106 -- End of comments
107 procedure validate_site_yn(x_return_status OUT NOCOPY VARCHAR2,
108 p_ksqv_rec IN ksqv_rec_type) is
109 Begin
110 -- initialize return status
111 x_return_status := OKC_API.G_RET_STS_SUCCESS;
112
113 -- check that site_yn has some valid value
114 If (p_ksqv_rec.site_yn = OKC_API.G_MISS_CHAR or
115 p_ksqv_rec.site_yn IS NULL)
116 Then
117 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
118 p_msg_name => g_required_value,
119 p_token1 => g_col_name_token,
120 p_token1_value => 'site_yn');
121 -- set error flag
122 x_return_status := OKC_API.G_RET_STS_ERROR;
123
124 -- halt validation
125 raise G_EXCEPTION_HALT_VALIDATION;
126 End If;
127
128 -- check allowed values
129 If (upper(p_ksqv_rec.site_yn) NOT IN ('Y','N')) Then
130 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
131 p_msg_name => g_invalid_value,
132 p_token1 => g_col_name_token,
133 p_token1_value => 'site_yn');
134 -- set error flag
135 x_return_status := OKC_API.G_RET_STS_ERROR;
136 End If;
137
138 exception
139 when G_EXCEPTION_HALT_VALIDATION then
140 -- no processing necessary; validation can continue with next column
141 null;
142
143 when OTHERS then
144 -- store SQL error message on message stack
145 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
146 p_msg_name => g_unexpected_error,
147 p_token1 => g_sqlcode_token,
148 p_token1_value => sqlcode,
149 p_token2 => g_sqlerrm_token,
150 p_token2_value => sqlerrm);
151 -- set error flag as UNEXPETED error
152 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
153 End validate_site_yn;
154
155 -- Start of comments
156 --
157 -- Procedure Name : validate_bg_ou_none
158 -- Description :
159 -- Business Rules :
160 -- Parameters :
161 -- Version : 1.0
162 -- End of comments
163 procedure validate_bg_ou_none(x_return_status OUT NOCOPY VARCHAR2,
164 p_ksqv_rec IN ksqv_rec_type) is
165 Begin
166 -- initialize return status
167 x_return_status := OKC_API.G_RET_STS_SUCCESS;
168
169 -- check that bg_ou_none has some valid value
170 If (p_ksqv_rec.bg_ou_none = OKC_API.G_MISS_CHAR or
171 p_ksqv_rec.bg_ou_none IS NULL)
172 Then
173 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
174 p_msg_name => g_required_value,
175 p_token1 => g_col_name_token,
176 p_token1_value => 'bg_ou_none');
177 -- set error flag
178 x_return_status := OKC_API.G_RET_STS_ERROR;
179
180 -- halt validation
181 raise G_EXCEPTION_HALT_VALIDATION;
182 End If;
183
184 -- check allowed values
185 If (p_ksqv_rec.bg_ou_none NOT IN ('BUG', 'OPU', 'NON')) Then
186 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
187 p_msg_name => g_invalid_value,
188 p_token1 => g_col_name_token,
189 p_token1_value => 'bg_ou_none');
190 -- set error flag
191 x_return_status := OKC_API.G_RET_STS_ERROR;
192 End If;
193
194 exception
195 when G_EXCEPTION_HALT_VALIDATION then
196 -- no processing necessary; validation can continue with next column
197 null;
198
199 when OTHERS then
200 -- store SQL error message on message stack
201 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
202 p_msg_name => g_unexpected_error,
203 p_token1 => g_sqlcode_token,
204 p_token1_value => sqlcode,
205 p_token2 => g_sqlerrm_token,
206 p_token2_value => sqlerrm);
207 -- set error flag as UNEXPETED error
208 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
209 End validate_bg_ou_none;
210
211 -- Start of comments
212 --
213 -- Procedure Name : validate_cls_scs_none
214 -- Description :
215 -- Business Rules :
216 -- Parameters :
217 -- Version : 1.0
218 -- End of comments
219 procedure validate_cls_scs_none(x_return_status OUT NOCOPY VARCHAR2,
220 p_ksqv_rec IN ksqv_rec_type) is
221 Begin
222 -- initialize return status
223 x_return_status := OKC_API.G_RET_STS_SUCCESS;
224
225 -- check that cls_scs_none has valid value
226 If (p_ksqv_rec.cls_scs_none = OKC_API.G_MISS_CHAR or
227 p_ksqv_rec.cls_scs_none IS NULL)
228 Then
229 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
230 p_msg_name => g_required_value,
231 p_token1 => g_col_name_token,
232 p_token1_value => 'cls_scs_none');
233 -- set error flag
234 x_return_status := OKC_API.G_RET_STS_ERROR;
235
236 -- halt validation
237 raise G_EXCEPTION_HALT_VALIDATION;
238 End If;
239
240 -- check allowed values
241 If (upper(p_ksqv_rec.cls_scs_none) NOT IN ('CLS', 'SCS', 'NON')) Then
242 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
243 p_msg_name => g_invalid_value,
244 p_token1 => g_col_name_token,
245 p_token1_value => 'cls_scs_none');
246 -- set error flag
247 x_return_status := OKC_API.G_RET_STS_ERROR;
248 End If;
249
250 exception
251 when G_EXCEPTION_HALT_VALIDATION then
252 -- no processing necessary; validation can continue with next column
253 null;
254
255 when OTHERS then
256 -- store SQL error message on message stack
257 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
258 p_msg_name => g_unexpected_error,
259 p_token1 => g_sqlcode_token,
260 p_token1_value => sqlcode,
261 p_token2 => g_sqlerrm_token,
262 p_token2_value => sqlerrm);
263 -- set error flag as UNEXPETED error
264 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
265 End validate_cls_scs_none;
266
267 -- Start of comments
268 --
269 -- Procedure Name : validate_user_function_yn
270 -- Description :
271 -- Business Rules :
272 -- Parameters :
273 -- Version : 1.0
274 -- End of comments
275 procedure validate_user_function_yn(x_return_status OUT NOCOPY VARCHAR2,
276 p_ksqv_rec IN ksqv_rec_type) is
277 Begin
278 -- initialize return status
279 x_return_status := OKC_API.G_RET_STS_SUCCESS;
280
281 -- check that user_function_yn has valid value
282 If (p_ksqv_rec.user_function_yn = OKC_API.G_MISS_CHAR or
283 p_ksqv_rec.user_function_yn IS NULL)
284 Then
285 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
286 p_msg_name => g_required_value,
287 p_token1 => g_col_name_token,
288 p_token1_value => 'user_function_yn');
289 -- set error flag
290 x_return_status := OKC_API.G_RET_STS_ERROR;
291
292 -- halt validation
293 raise G_EXCEPTION_HALT_VALIDATION;
294 End If;
295
296 -- check allowed values
297 If (upper(p_ksqv_rec.user_function_yn) NOT IN ('Y','N')) Then
298 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
299 p_msg_name => g_invalid_value,
300 p_token1 => g_col_name_token,
301 p_token1_value => 'user_function_yn');
302 -- set error flag
303 x_return_status := OKC_API.G_RET_STS_ERROR;
304 End If;
305
306 exception
307 when G_EXCEPTION_HALT_VALIDATION then
308 -- no processing necessary; validation can continue with next column
309 null;
310
311 when OTHERS then
312 -- store SQL error message on message stack
313 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
314 p_msg_name => g_unexpected_error,
315 p_token1 => g_sqlcode_token,
316 p_token1_value => sqlcode,
317 p_token2 => g_sqlerrm_token,
318 p_token2_value => sqlerrm);
319 -- set error flag as UNEXPETED error
320 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
321 End validate_user_function_yn;
322
323 PROCEDURE validate_pdf_id(x_return_status OUT NOCOPY VARCHAR2,
324 p_ksqv_rec IN ksqv_rec_type) is
325
326 l_dummy_var VARCHAR2(1);
327 l_row_notfound Boolean := False;
328 -- Cursor to make sure it is a valid process def
329 Cursor l_pdfv_csr Is
330 select 'x'
331 from OKC_PROCESS_DEFS_B
332 where ID = p_ksqv_rec.pdf_id;
333 Begin
334 -- initialize return status
335 x_return_status := OKC_API.G_RET_STS_SUCCESS;
336
337 If (p_ksqv_rec.user_function_yn = 'Y') Then
338 Null;
339 -- If no pdf, report it as an error
340 /* If (p_ksqv_rec.pdf_id = OKC_API.G_MISS_NUM or
341 p_ksqv_rec.pdf_id IS NULL) Then
342 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
343 p_msg_name => g_required_value,
344 p_token1 => g_col_name_token,
345 p_token1_value => 'pdf_id');
346 -- set error flag
347 x_return_status := OKC_API.G_RET_STS_ERROR;
348 -- halt validation
349 raise G_EXCEPTION_HALT_VALIDATION;
350 End If; */
351 End If;
352
353 -- Check that it is a valid Process Definition
354 If (p_ksqv_rec.pdf_id IS Not Null) And
355 (p_ksqv_rec.pdf_id <> OKC_API.G_MISS_NUM) Then
356 Open l_pdfv_csr;
357 Fetch l_pdfv_csr Into l_dummy_var;
358 l_row_notfound := l_pdfv_csr%NOTFOUND;
359 Close l_pdfv_csr;
360
361 If l_row_notfound Then
362 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
363 p_msg_name => g_no_parent_record,
364 p_token1 => g_col_name_token,
365 p_token1_value => 'pdf_id',
366 p_token2 => g_child_table_token,
367 p_token2_value => 'OKC_K_SEQ_HEADER_V',
368 p_token3 => g_parent_table_token,
369 p_token3_value => 'OKC_PROCESS_DEFS_V');
370
371 -- notify caller of an error
372 x_return_status := OKC_API.G_RET_STS_ERROR;
373 End If;
374 End If;
375
376 exception
377 when OTHERS then
378 -- store SQL error message on message stack
379 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
380 p_msg_name => g_unexpected_error,
381 p_token1 => g_sqlcode_token,
382 p_token1_value => sqlcode,
383 p_token2 => g_sqlerrm_token,
384 p_token2_value => sqlerrm);
385 -- set error flag as UNEXPETED error
386 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
387
388 End validate_pdf_id;
389
390 -- Start of comments
391 --
392 -- Procedure Name : validate_manual_override_yn
393 -- Description :
394 -- Business Rules :
395 -- Parameters :
396 -- Version : 1.0
397 -- End of comments
398 procedure validate_manual_override_yn(x_return_status OUT NOCOPY VARCHAR2,
399 p_ksqv_rec IN ksqv_rec_type) is
400 Begin
401 -- initialize return status
402 x_return_status := OKC_API.G_RET_STS_SUCCESS;
403
404 -- check that manual_override_yn has valid value
405 If (p_ksqv_rec.manual_override_yn = OKC_API.G_MISS_CHAR or
406 p_ksqv_rec.manual_override_yn IS NULL)
407 Then
408 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
409 p_msg_name => g_required_value,
410 p_token1 => g_col_name_token,
411 p_token1_value => 'manual_override_yn');
412 -- set error flag
413 x_return_status := OKC_API.G_RET_STS_ERROR;
414
415 -- halt validation
416 raise G_EXCEPTION_HALT_VALIDATION;
417 End If;
418
419 -- check allowed values
420 If (upper(p_ksqv_rec.manual_override_yn) NOT IN ('Y','N')) Then
421 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
422 p_msg_name => g_invalid_value,
423 p_token1 => g_col_name_token,
424 p_token1_value => 'manual_override_yn');
425 -- set error flag
426 x_return_status := OKC_API.G_RET_STS_ERROR;
427 End If;
428
429 exception
430 when G_EXCEPTION_HALT_VALIDATION then
431 -- no processing necessary; validation can continue with next column
432 null;
433
434 when OTHERS then
435 -- store SQL error message on message stack
436 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
437 p_msg_name => g_unexpected_error,
438 p_token1 => g_sqlcode_token,
439 p_token1_value => sqlcode,
440 p_token2 => g_sqlerrm_token,
441 p_token2_value => sqlerrm);
442 -- set error flag as UNEXPETED error
443 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
444 End validate_manual_override_yn;
445
446 /*********************** END HAND-CODED ********************************/
447 ---------------------------------------------------------------------------
448 -- FUNCTION get_seq_id
449 ---------------------------------------------------------------------------
450 /* FUNCTION get_seq_id RETURN NUMBER IS
451 BEGIN
452 RETURN(okc_p_util.raw_to_number(sys_guid()));
453 END get_seq_id; */
454
455 ---------------------------------------------------------------------------
456 -- PROCEDURE qc
457 ---------------------------------------------------------------------------
458 PROCEDURE qc IS
459 BEGIN
460 null;
461 END qc;
462
463 ---------------------------------------------------------------------------
464 -- PROCEDURE change_version
465 ---------------------------------------------------------------------------
466 PROCEDURE change_version IS
467 BEGIN
468 null;
469 END change_version;
470
471 ---------------------------------------------------------------------------
472 -- PROCEDURE api_copy
473 ---------------------------------------------------------------------------
474 PROCEDURE api_copy IS
475 BEGIN
476 null;
477 END api_copy;
478
479 ---------------------------------------------------------------------------
480 -- FUNCTION get_rec for: OKC_K_SEQ_HEADER
481 ---------------------------------------------------------------------------
482 FUNCTION get_rec (
483 p_ksq_rec IN ksq_rec_type,
484 x_no_data_found OUT NOCOPY BOOLEAN
485 ) RETURN ksq_rec_type IS
486 CURSOR ksq_pk_csr (p_line_code IN OKC_K_SEQ_HEADER.LINE_CODE%TYPE) IS
487 SELECT LINE_CODE,
488 SITE_YN,
489 BG_OU_NONE,
490 CLS_SCS_NONE,
491 USER_FUNCTION_YN,
492 PDF_ID,
493 MANUAL_OVERRIDE_YN,
494 OBJECT_VERSION_NUMBER,
495 CREATED_BY,
496 CREATION_DATE,
497 LAST_UPDATED_BY,
498 LAST_UPDATE_DATE,
499 LAST_UPDATE_LOGIN
500 FROM OKC_K_SEQ_HEADER
501 WHERE OKC_K_SEQ_HEADER.LINE_CODE = p_line_code;
502 l_ksq_pk ksq_pk_csr%ROWTYPE;
503 l_ksq_rec ksq_rec_type;
504 BEGIN
505 x_no_data_found := TRUE;
506 -- Get current database values
507 OPEN ksq_pk_csr (p_ksq_rec.line_code);
508 FETCH ksq_pk_csr INTO
509 l_ksq_rec.LINE_CODE,
510 l_ksq_rec.SITE_YN,
511 l_ksq_rec.BG_OU_NONE,
512 l_ksq_rec.CLS_SCS_NONE,
513 l_ksq_rec.USER_FUNCTION_YN,
514 l_ksq_rec.PDF_ID,
515 l_ksq_rec.MANUAL_OVERRIDE_YN,
516 l_ksq_rec.OBJECT_VERSION_NUMBER,
517 l_ksq_rec.CREATED_BY,
518 l_ksq_rec.CREATION_DATE,
519 l_ksq_rec.LAST_UPDATED_BY,
520 l_ksq_rec.LAST_UPDATE_DATE,
521 l_ksq_rec.LAST_UPDATE_LOGIN;
522 x_no_data_found := ksq_pk_csr%NOTFOUND;
523 CLOSE ksq_pk_csr;
524 RETURN(l_ksq_rec);
525 END get_rec;
526
527 FUNCTION get_rec (
528 p_ksq_rec IN ksq_rec_type
529 ) RETURN ksq_rec_type IS
530 l_row_notfound BOOLEAN := TRUE;
531 BEGIN
532 RETURN(get_rec(p_ksq_rec, l_row_notfound));
533 END get_rec;
534 ---------------------------------------------------------------------------
535 -- FUNCTION get_rec for: OKC_K_SEQ_HEADER_V
536 ---------------------------------------------------------------------------
537 FUNCTION get_rec (
538 p_ksqv_rec IN ksqv_rec_type,
539 x_no_data_found OUT NOCOPY BOOLEAN
540 ) RETURN ksqv_rec_type IS
541 CURSOR okc_ksqv_pk_csr (p_line_code IN OKC_K_SEQ_HEADER_V.LINE_CODE%TYPE) IS
542 SELECT
543 LINE_CODE,
544 SITE_YN,
545 BG_OU_NONE,
546 CLS_SCS_NONE,
547 USER_FUNCTION_YN,
548 PDF_ID,
549 MANUAL_OVERRIDE_YN,
550 OBJECT_VERSION_NUMBER,
551 CREATED_BY,
552 CREATION_DATE,
553 LAST_UPDATED_BY,
554 LAST_UPDATE_DATE,
555 LAST_UPDATE_LOGIN
556 FROM OKC_K_SEQ_HEADER_V
557 WHERE OKC_K_SEQ_HEADER_V.LINE_CODE = p_line_code;
558 l_okc_ksqv_pk okc_ksqv_pk_csr%ROWTYPE;
559 l_ksqv_rec ksqv_rec_type;
560 BEGIN
561 x_no_data_found := TRUE;
562 -- Get current database values
563 OPEN okc_ksqv_pk_csr (p_ksqv_rec.line_code);
564 FETCH okc_ksqv_pk_csr INTO
565 l_ksqv_rec.LINE_CODE,
566 l_ksqv_rec.SITE_YN,
567 l_ksqv_rec.BG_OU_NONE,
568 l_ksqv_rec.CLS_SCS_NONE,
569 l_ksqv_rec.USER_FUNCTION_YN,
570 l_ksqv_rec.PDF_ID,
571 l_ksqv_rec.MANUAL_OVERRIDE_YN,
572 l_ksqv_rec.OBJECT_VERSION_NUMBER,
573 l_ksqv_rec.CREATED_BY,
574 l_ksqv_rec.CREATION_DATE,
575 l_ksqv_rec.LAST_UPDATED_BY,
576 l_ksqv_rec.LAST_UPDATE_DATE,
577 l_ksqv_rec.LAST_UPDATE_LOGIN;
578 x_no_data_found := okc_ksqv_pk_csr%NOTFOUND;
579 CLOSE okc_ksqv_pk_csr;
580 RETURN(l_ksqv_rec);
581 END get_rec;
582
583 FUNCTION get_rec (
584 p_ksqv_rec IN ksqv_rec_type
585 ) RETURN ksqv_rec_type IS
586 l_row_notfound BOOLEAN := TRUE;
587 BEGIN
588 RETURN(get_rec(p_ksqv_rec, l_row_notfound));
589 END get_rec;
590
591 ------------------------------------------------------
592 -- FUNCTION null_out_defaults for: OKC_K_SEQ_HEADER_V --
593 ------------------------------------------------------
594 FUNCTION null_out_defaults (
595 p_ksqv_rec IN ksqv_rec_type
596 ) RETURN ksqv_rec_type IS
597 l_ksqv_rec ksqv_rec_type := p_ksqv_rec;
598 BEGIN
599 IF (l_ksqv_rec.pdf_id = OKC_API.G_MISS_NUM) THEN
600 l_ksqv_rec.pdf_id := NULL;
601 END IF;
602 IF (l_ksqv_rec.site_yn = OKC_API.G_MISS_CHAR) THEN
603 l_ksqv_rec.site_yn := NULL;
604 END IF;
605 IF (l_ksqv_rec.bg_ou_none = OKC_API.G_MISS_CHAR) THEN
606 l_ksqv_rec.bg_ou_none := NULL;
607 END IF;
608 IF (l_ksqv_rec.cls_scs_none = OKC_API.G_MISS_CHAR) THEN
609 l_ksqv_rec.cls_scs_none := NULL;
610 END IF;
611 IF (l_ksqv_rec.user_function_yn = OKC_API.G_MISS_CHAR) THEN
612 l_ksqv_rec.user_function_yn := NULL;
613 END IF;
614 IF (l_ksqv_rec.manual_override_yn = OKC_API.G_MISS_CHAR) THEN
615 l_ksqv_rec.manual_override_yn := NULL;
616 END IF;
617 IF (l_ksqv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
618 l_ksqv_rec.object_version_number := NULL;
619 END IF;
620 IF (l_ksqv_rec.created_by = OKC_API.G_MISS_NUM) THEN
621 l_ksqv_rec.created_by := NULL;
622 END IF;
623 IF (l_ksqv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
624 l_ksqv_rec.creation_date := NULL;
625 END IF;
626 IF (l_ksqv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
627 l_ksqv_rec.last_updated_by := NULL;
628 END IF;
629 IF (l_ksqv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
630 l_ksqv_rec.last_update_date := NULL;
631 END IF;
632 IF (l_ksqv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
633 l_ksqv_rec.last_update_login := NULL;
634 END IF;
635 RETURN(l_ksqv_rec);
636 END null_out_defaults;
637 ---------------------------------------------------------------------------
638 -- PROCEDURE Validate_Attributes
639 ---------------------------------------------------------------------------
640 ----------------------------------------------
641 -- Validate_Attributes for:OKC_K_SEQ_HEADER_V --
642 ----------------------------------------------
643 FUNCTION Validate_Attributes (
644 p_ksqv_rec IN ksqv_rec_type
645 ) RETURN VARCHAR2 IS
646 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
647 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
648 BEGIN
649 /************************ HAND-CODED *********************************/
650 validate_line_code
651 (x_return_status => l_return_status,
652 p_ksqv_rec => p_ksqv_rec);
653
654 -- store the highest degree of error
655 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
656 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
657 x_return_status := l_return_status;
658 End If;
659 End If;
660
661 validate_pdf_id
662 (x_return_status => l_return_status,
663 p_ksqv_rec => p_ksqv_rec);
664
665 -- store the highest degree of error
666 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
667 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
668 x_return_status := l_return_status;
669 End If;
670 End If;
671
672 validate_site_yn
673 (x_return_status => l_return_status,
674 p_ksqv_rec => p_ksqv_rec);
675
676 -- store the highest degree of error
677 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
678 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
679 x_return_status := l_return_status;
680 End If;
681 End If;
682
683 validate_bg_ou_none
684 (x_return_status => l_return_status,
685 p_ksqv_rec => p_ksqv_rec);
686
687 -- store the highest degree of error
688 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
689 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
690 x_return_status := l_return_status;
691 End If;
692 End If;
693
694 validate_cls_scs_none
695 (x_return_status => l_return_status,
696 p_ksqv_rec => p_ksqv_rec);
697
698 -- store the highest degree of error
699 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
700 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
701 x_return_status := l_return_status;
702 End If;
703 End If;
704
705 validate_user_function_yn
706 (x_return_status => l_return_status,
707 p_ksqv_rec => p_ksqv_rec);
708
709 -- store the highest degree of error
710 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
711 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
712 x_return_status := l_return_status;
713 End If;
714 End If;
715
716 validate_manual_override_yn
717 (x_return_status => l_return_status,
718 p_ksqv_rec => p_ksqv_rec);
719
720 -- store the highest degree of error
721 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
722 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
723 x_return_status := l_return_status;
724 End If;
725 End If;
726
727 RETURN(x_return_status);
728 exception
729 when OTHERS then
730 -- store SQL error message on message stack
731 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
732 p_msg_name => g_unexpected_error,
733 p_token1 => g_sqlcode_token,
734 p_token1_value => sqlcode,
735 p_token2 => g_sqlerrm_token,
736 p_token2_value => sqlerrm);
737 -- notify caller of an UNEXPETED error
738 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
739 -- return status to caller
740 RETURN(x_return_status);
741 /*********************** END HAND-CODED ********************************/
742 END Validate_Attributes;
743
744 ---------------------------------------------------------------------------
745 -- PROCEDURE Validate_Record
746 ---------------------------------------------------------------------------
747 ------------------------------------------
748 -- Validate_Record for:OKC_K_SEQ_HEADER_V --
749 ------------------------------------------
750 FUNCTION Validate_Record (
751 p_ksqv_rec IN ksqv_rec_type
752 ) RETURN VARCHAR2 IS
753
754 -- Cursor to make sure that there is only 1 header per installation
755 CURSOR cur_ksq_1 IS
756 SELECT count(*)
757 FROM OKC_K_SEQ_HEADER;
758 -- WHERE line_code = p_ksqv_rec.line_code;
759
760 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
761 -- l_row_found BOOLEAN := FALSE;
762 l_dummy Number;
763
764 BEGIN
765 OPEN cur_ksq_1;
766 FETCH cur_ksq_1 INTO l_dummy;
767 -- l_row_found := cur_ksq_1%FOUND;
768 CLOSE cur_ksq_1;
769
770 -- If more than 1 setup, stack the error message
771 IF (l_dummy > 1) THEN
772 -- Display the error message
773 OKC_API.set_message(G_APP_NAME,
774 'OKC_K_SEQ_SINGLE_HEADER');
775 l_return_status := OKC_API.G_RET_STS_ERROR;
776 RAISE G_EXCEPTION_HALT_VALIDATION;
777 END IF;
778
779 -- If there is no selection in the header, set the error message
780 If p_ksqv_rec.site_yn = 'N' And -- No site selected
781 p_ksqv_rec.user_function_yn = 'N' And -- Not a User Defined Function
782 p_ksqv_rec.bg_ou_none = 'NON' And -- No Business Group/OU selected
783 p_ksqv_rec.cls_scs_none = 'NON' Then -- No Class/Category selected
784 -- Display the error message
785 OKC_API.set_message(G_APP_NAME,
786 'OKC_NO_K_SEQ_SELECTED');
787 l_return_status := OKC_API.G_RET_STS_ERROR;
788 RAISE G_EXCEPTION_HALT_VALIDATION;
789 END IF;
790
791 If p_ksqv_rec.site_yn = 'Y' Then
792 -- If site has been selected then no other combination can be selected
793 If p_ksqv_rec.bg_ou_none In ('BUG', 'OPU') Or
794 p_ksqv_rec.cls_scs_none In ('CLS', 'SCS') Or
795 p_ksqv_rec.user_function_yn = 'Y' Then
796 -- Display the error message
797 OKC_API.set_message(G_APP_NAME,
798 'OKC_SITE_EXCLUSIVE');
799 l_return_status := OKC_API.G_RET_STS_ERROR;
800 RAISE G_EXCEPTION_HALT_VALIDATION;
801 END IF;
802 END IF;
803
804 If p_ksqv_rec.user_function_yn = 'Y' Then
805 -- If user defined function has been selected then
806 -- no other combination can be selected
807 If p_ksqv_rec.bg_ou_none In ('BUG', 'OPU') Or
808 p_ksqv_rec.cls_scs_none In ('CLS', 'SCS') Then
809 -- Display the error message
810 OKC_API.set_message(G_APP_NAME,
811 'OKC_USER_FUNCTION_EXCLUSIVE');
812 l_return_status := OKC_API.G_RET_STS_ERROR;
813 RAISE G_EXCEPTION_HALT_VALIDATION;
814 END IF;
815 -- For user defined function, function name must be supplied
816 -- Not necessary. We will make it mandatory in the UI itself.
817 /* If p_ksqv_rec.pdf_id = OKC_API.G_MISS_NUM Or
818 p_ksqv_rec.pdf_id Is Null Then
819 -- Display the error message
820 OKC_API.set_message(G_APP_NAME,
821 'OKC_FUNCTION_NAME_UNSELECTED');
822 l_return_status := OKC_API.G_RET_STS_ERROR;
823 RAISE G_EXCEPTION_HALT_VALIDATION;
824 END IF; */
825 END IF;
826 /*********************** END HAND-CODED *************************/
827
828 RETURN (l_return_status);
829 EXCEPTION
830 WHEN G_EXCEPTION_HALT_VALIDATION THEN
831 -- no processing necessary; validation can continue with next column
832 RETURN (l_return_status);
833 END Validate_Record;
834
835 ---------------------------------------------------------------------------
836 -- PROCEDURE Migrate
837 ---------------------------------------------------------------------------
838 PROCEDURE migrate (
839 p_from IN ksqv_rec_type,
840 p_to IN OUT NOCOPY ksq_rec_type
841 ) IS
842 BEGIN
843 p_to.line_code := p_from.line_code;
844 p_to.site_yn := p_from.site_yn;
845 p_to.bg_ou_none := p_from.bg_ou_none;
846 p_to.cls_scs_none := p_from.cls_scs_none;
847 p_to.user_function_yn := p_from.user_function_yn;
848 p_to.pdf_id := p_from.pdf_id;
849 p_to.manual_override_yn := p_from.manual_override_yn;
850 p_to.object_version_number := p_from.object_version_number;
851 p_to.created_by := p_from.created_by;
852 p_to.creation_date := p_from.creation_date;
853 p_to.last_updated_by := p_from.last_updated_by;
854 p_to.last_update_date := p_from.last_update_date;
855 p_to.last_update_login := p_from.last_update_login;
856 END migrate;
857 PROCEDURE migrate (
858 p_from IN ksq_rec_type,
859 p_to IN OUT NOCOPY ksqv_rec_type
860 ) IS
861 BEGIN
862 p_to.line_code := p_from.line_code;
863 p_to.site_yn := p_from.site_yn;
864 p_to.bg_ou_none := p_from.bg_ou_none;
865 p_to.cls_scs_none := p_from.cls_scs_none;
866 p_to.user_function_yn := p_from.user_function_yn;
867 p_to.pdf_id := p_from.pdf_id;
868 p_to.manual_override_yn := p_from.manual_override_yn;
869 p_to.object_version_number := p_from.object_version_number;
870 p_to.created_by := p_from.created_by;
871 p_to.creation_date := p_from.creation_date;
872 p_to.last_updated_by := p_from.last_updated_by;
873 p_to.last_update_date := p_from.last_update_date;
874 p_to.last_update_login := p_from.last_update_login;
875 END migrate;
876
877 ---------------------------------------------------------------------------
878 -- PROCEDURE validate_row
879 ---------------------------------------------------------------------------
880 ---------------------------------------
881 -- validate_row for:OKC_K_SEQ_HEADER_V --
882 ---------------------------------------
883 PROCEDURE validate_row(
884 p_api_version IN NUMBER,
885 p_init_msg_list IN VARCHAR2 ,
886 x_return_status OUT NOCOPY VARCHAR2,
887 x_msg_count OUT NOCOPY NUMBER,
888 x_msg_data OUT NOCOPY VARCHAR2,
889 p_ksqv_rec IN ksqv_rec_type) IS
890
891 l_api_version CONSTANT NUMBER := 1;
892 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
893 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
894 l_ksqv_rec ksqv_rec_type := p_ksqv_rec;
895 l_ksq_rec ksq_rec_type;
896 BEGIN
897 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
898 G_PKG_NAME,
899 p_init_msg_list,
900 l_api_version,
901 p_api_version,
902 '_PVT',
903 x_return_status);
904 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
905 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
906 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
907 RAISE OKC_API.G_EXCEPTION_ERROR;
908 END IF;
909 --- Validate all non-missing attributes (Item Level Validation)
910 l_return_status := Validate_Attributes(l_ksqv_rec);
911 --- If any errors happen abort API
912 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
913 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
914 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
915 RAISE OKC_API.G_EXCEPTION_ERROR;
916 END IF;
917 l_return_status := Validate_Record(l_ksqv_rec);
918 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
919 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
920 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
921 RAISE OKC_API.G_EXCEPTION_ERROR;
922 END IF;
923 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
924 EXCEPTION
925 WHEN OKC_API.G_EXCEPTION_ERROR THEN
926 x_return_status := OKC_API.HANDLE_EXCEPTIONS
927 (
928 l_api_name,
929 G_PKG_NAME,
930 'OKC_API.G_RET_STS_ERROR',
931 x_msg_count,
932 x_msg_data,
933 '_PVT'
934 );
935 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
936 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
937 (
938 l_api_name,
939 G_PKG_NAME,
940 'OKC_API.G_RET_STS_UNEXP_ERROR',
941 x_msg_count,
942 x_msg_data,
943 '_PVT'
944 );
945 WHEN OTHERS THEN
946 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
947 (
948 l_api_name,
949 G_PKG_NAME,
950 'OTHERS',
951 x_msg_count,
952 x_msg_data,
953 '_PVT'
954 );
955 END validate_row;
956 ------------------------------------------
957 -- PL/SQL TBL validate_row for:ksqV_TBL --
958 ------------------------------------------
959 PROCEDURE validate_row(
960 p_api_version IN NUMBER,
961 p_init_msg_list IN VARCHAR2 ,
962 x_return_status OUT NOCOPY VARCHAR2,
963 x_msg_count OUT NOCOPY NUMBER,
964 x_msg_data OUT NOCOPY VARCHAR2,
965 p_ksqv_tbl IN ksqv_tbl_type) IS
966
967 l_api_version CONSTANT NUMBER := 1;
968 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
969 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
970 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
971 i NUMBER := 0;
972 BEGIN
973 OKC_API.init_msg_list(p_init_msg_list);
974 -- Make sure PL/SQL table has records in it before passing
975 IF (p_ksqv_tbl.COUNT > 0) THEN
976 i := p_ksqv_tbl.FIRST;
977 LOOP
978 validate_row (
979 p_api_version => p_api_version,
980 p_init_msg_list => OKC_API.G_FALSE,
981 x_return_status => x_return_status,
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data,
984 p_ksqv_rec => p_ksqv_tbl(i));
985
986 -- store the highest degree of error
987 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
988 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
989 l_overall_status := x_return_status;
990 End If;
991 End If;
992
993 EXIT WHEN (i = p_ksqv_tbl.LAST);
994 i := p_ksqv_tbl.NEXT(i);
995 END LOOP;
996 -- return overall status
997 x_return_status := l_overall_status;
998 END IF;
999 EXCEPTION
1000 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1001 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1002 (
1003 l_api_name,
1004 G_PKG_NAME,
1005 'OKC_API.G_RET_STS_ERROR',
1006 x_msg_count,
1007 x_msg_data,
1008 '_PVT'
1009 );
1010 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1011 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1012 (
1013 l_api_name,
1014 G_PKG_NAME,
1015 'OKC_API.G_RET_STS_UNEXP_ERROR',
1016 x_msg_count,
1017 x_msg_data,
1018 '_PVT'
1019 );
1020 WHEN OTHERS THEN
1021 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1022 (
1023 l_api_name,
1024 G_PKG_NAME,
1025 'OTHERS',
1026 x_msg_count,
1027 x_msg_data,
1028 '_PVT'
1029 );
1030 END validate_row;
1031
1032 ---------------------------------------------------------------------------
1033 -- PROCEDURE insert_row
1034 ---------------------------------------------------------------------------
1035 -----------------------------------
1036 -- insert_row for:OKC_K_SEQ_HEADER --
1037 -----------------------------------
1038 PROCEDURE insert_row(
1039 p_init_msg_list IN VARCHAR2 ,
1040 x_return_status OUT NOCOPY VARCHAR2,
1041 x_msg_count OUT NOCOPY NUMBER,
1042 x_msg_data OUT NOCOPY VARCHAR2,
1043 p_ksq_rec IN ksq_rec_type,
1044 x_ksq_rec OUT NOCOPY ksq_rec_type) IS
1045
1046 l_api_version CONSTANT NUMBER := 1;
1047 l_api_name CONSTANT VARCHAR2(30) := 'K_SEQ_insert_row';
1048 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1049 l_ksq_rec ksq_rec_type := p_ksq_rec;
1050 l_def_ksq_rec ksq_rec_type;
1051 ---------------------------------------
1052 -- Set_Attributes for:OKC_K_SEQ_HEADER --
1053 ---------------------------------------
1054 FUNCTION Set_Attributes (
1055 p_ksq_rec IN ksq_rec_type,
1056 x_ksq_rec OUT NOCOPY ksq_rec_type
1057 ) RETURN VARCHAR2 IS
1058 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1059 BEGIN
1060 x_ksq_rec := p_ksq_rec;
1061 RETURN(l_return_status);
1062 END Set_Attributes;
1063 BEGIN
1064 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1065 p_init_msg_list,
1066 '_PVT',
1067 x_return_status);
1068 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1069 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1070 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1071 RAISE OKC_API.G_EXCEPTION_ERROR;
1072 END IF;
1073 --- Setting item attributes
1074 l_return_status := Set_Attributes(
1075 p_ksq_rec, -- IN
1076 l_ksq_rec); -- OUT
1077 --- If any errors happen abort API
1078 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1079 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1080 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1081 RAISE OKC_API.G_EXCEPTION_ERROR;
1082 END IF;
1083 INSERT INTO OKC_K_SEQ_HEADER(
1084 line_code,
1085 site_yn,
1086 bg_ou_none,
1087 cls_scs_none,
1088 user_function_yn,
1089 pdf_id,
1090 manual_override_yn,
1091 object_version_number,
1092 created_by,
1093 creation_date,
1094 last_updated_by,
1095 last_update_date,
1096 last_update_login)
1097 VALUES (
1098 l_ksq_rec.line_code,
1099 l_ksq_rec.site_yn,
1100 l_ksq_rec.bg_ou_none,
1101 l_ksq_rec.cls_scs_none,
1102 l_ksq_rec.user_function_yn,
1103 l_ksq_rec.pdf_id,
1104 l_ksq_rec.manual_override_yn,
1105 l_ksq_rec.object_version_number,
1106 l_ksq_rec.created_by,
1107 l_ksq_rec.creation_date,
1108 l_ksq_rec.last_updated_by,
1109 l_ksq_rec.last_update_date,
1110 l_ksq_rec.last_update_login);
1111 -- Set OUT values
1112 x_ksq_rec := l_ksq_rec;
1113 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1114 EXCEPTION
1115 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1116 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1117 (
1118 l_api_name,
1119 G_PKG_NAME,
1120 'OKC_API.G_RET_STS_ERROR',
1121 x_msg_count,
1122 x_msg_data,
1123 '_PVT'
1124 );
1125 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1126 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1127 (
1128 l_api_name,
1129 G_PKG_NAME,
1130 'OKC_API.G_RET_STS_UNEXP_ERROR',
1131 x_msg_count,
1132 x_msg_data,
1133 '_PVT'
1134 );
1135 WHEN OTHERS THEN
1136 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1137 (
1138 l_api_name,
1139 G_PKG_NAME,
1140 'OTHERS',
1141 x_msg_count,
1142 x_msg_data,
1143 '_PVT'
1144 );
1145 END insert_row;
1146 -------------------------------------
1147 -- insert_row for:OKC_K_SEQ_HEADER_V --
1148 -------------------------------------
1149 PROCEDURE insert_row(
1150 p_api_version IN NUMBER,
1151 p_init_msg_list IN VARCHAR2 ,
1152 x_return_status OUT NOCOPY VARCHAR2,
1153 x_msg_count OUT NOCOPY NUMBER,
1154 x_msg_data OUT NOCOPY VARCHAR2,
1155 p_ksqv_rec IN ksqv_rec_type,
1156 x_ksqv_rec OUT NOCOPY ksqv_rec_type) IS
1157
1158 l_api_version CONSTANT NUMBER := 1;
1159 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1160 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1161 l_ksqv_rec ksqv_rec_type;
1162 l_def_ksqv_rec ksqv_rec_type;
1163 l_ksq_rec ksq_rec_type;
1164 lx_ksq_rec ksq_rec_type;
1165 -------------------------------
1166 -- FUNCTION fill_who_columns --
1167 -------------------------------
1168 FUNCTION fill_who_columns (
1169 p_ksqv_rec IN ksqv_rec_type
1170 ) RETURN ksqv_rec_type IS
1171 l_ksqv_rec ksqv_rec_type := p_ksqv_rec;
1172 BEGIN
1173 l_ksqv_rec.CREATION_DATE := SYSDATE;
1174 l_ksqv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1175 l_ksqv_rec.LAST_UPDATE_DATE := l_ksqv_rec.CREATION_DATE;
1176 l_ksqv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1177 l_ksqv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1178 RETURN(l_ksqv_rec);
1179 END fill_who_columns;
1180 -----------------------------------------
1181 -- Set_Attributes for:OKC_K_SEQ_HEADER_V --
1182 -----------------------------------------
1183 FUNCTION Set_Attributes (
1184 p_ksqv_rec IN ksqv_rec_type,
1185 x_ksqv_rec OUT NOCOPY ksqv_rec_type
1186 ) RETURN VARCHAR2 IS
1187 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1188 BEGIN
1189 x_ksqv_rec := p_ksqv_rec;
1190 x_ksqv_rec.OBJECT_VERSION_NUMBER := 1;
1191 RETURN(l_return_status);
1192 END Set_Attributes;
1193 BEGIN
1194 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1195 G_PKG_NAME,
1196 p_init_msg_list,
1197 l_api_version,
1198 p_api_version,
1199 '_PVT',
1200 x_return_status);
1201 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1202 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1203 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1204 RAISE OKC_API.G_EXCEPTION_ERROR;
1205 END IF;
1206 l_ksqv_rec := null_out_defaults(p_ksqv_rec);
1207 -- Set primary key value
1208 -- l_ksqv_rec.ID := get_seq_id;
1209 --- Setting item attributes
1210 l_return_status := Set_Attributes(
1211 l_ksqv_rec, -- IN
1212 l_def_ksqv_rec); -- OUT
1213 --- If any errors happen abort API
1214 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1215 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1216 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1217 RAISE OKC_API.G_EXCEPTION_ERROR;
1218 END IF;
1219 l_def_ksqv_rec := fill_who_columns(l_def_ksqv_rec);
1220 --- Validate all non-missing attributes (Item Level Validation)
1221 l_return_status := Validate_Attributes(l_def_ksqv_rec);
1222 --- If any errors happen abort API
1223 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1224 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1225 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1226 RAISE OKC_API.G_EXCEPTION_ERROR;
1227 END IF;
1228 l_return_status := Validate_Record(l_def_ksqv_rec);
1229 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1230 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1231 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1232 RAISE OKC_API.G_EXCEPTION_ERROR;
1233 END IF;
1234 --------------------------------------
1235 -- Move VIEW record to "Child" records
1236 --------------------------------------
1237 migrate(l_def_ksqv_rec, l_ksq_rec);
1238 --------------------------------------------
1239 -- Call the INSERT_ROW for each child record
1240 --------------------------------------------
1241 insert_row(
1242 p_init_msg_list,
1243 x_return_status,
1244 x_msg_count,
1245 x_msg_data,
1246 l_ksq_rec,
1247 lx_ksq_rec
1248 );
1249 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1251 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1252 RAISE OKC_API.G_EXCEPTION_ERROR;
1253 END IF;
1254 migrate(lx_ksq_rec, l_def_ksqv_rec);
1255 -- Set OUT values
1256 x_ksqv_rec := l_def_ksqv_rec;
1257 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1258 EXCEPTION
1259 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1260 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1261 (
1262 l_api_name,
1263 G_PKG_NAME,
1264 'OKC_API.G_RET_STS_ERROR',
1265 x_msg_count,
1266 x_msg_data,
1267 '_PVT'
1268 );
1269 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1270 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1271 (
1272 l_api_name,
1273 G_PKG_NAME,
1274 'OKC_API.G_RET_STS_UNEXP_ERROR',
1275 x_msg_count,
1276 x_msg_data,
1277 '_PVT'
1278 );
1279 WHEN OTHERS THEN
1280 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1281 (
1282 l_api_name,
1283 G_PKG_NAME,
1284 'OTHERS',
1285 x_msg_count,
1286 x_msg_data,
1287 '_PVT'
1288 );
1289 END insert_row;
1290 ----------------------------------------
1291 -- PL/SQL TBL insert_row for:KSQV_TBL --
1292 ----------------------------------------
1293 PROCEDURE insert_row(
1294 p_api_version IN NUMBER,
1295 p_init_msg_list IN VARCHAR2 ,
1296 x_return_status OUT NOCOPY VARCHAR2,
1297 x_msg_count OUT NOCOPY NUMBER,
1298 x_msg_data OUT NOCOPY VARCHAR2,
1299 p_ksqv_tbl IN ksqv_tbl_type,
1300 x_ksqv_tbl OUT NOCOPY ksqv_tbl_type) IS
1301
1302 l_api_version CONSTANT NUMBER := 1;
1303 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1304 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1305 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1306 i NUMBER := 0;
1307 BEGIN
1308 OKC_API.init_msg_list(p_init_msg_list);
1309 -- Make sure PL/SQL table has records in it before passing
1310 IF (p_ksqv_tbl.COUNT > 0) THEN
1311 i := p_ksqv_tbl.FIRST;
1312 LOOP
1313 insert_row (
1314 p_api_version => p_api_version,
1315 p_init_msg_list => OKC_API.G_FALSE,
1316 x_return_status => x_return_status,
1317 x_msg_count => x_msg_count,
1318 x_msg_data => x_msg_data,
1319 p_ksqv_rec => p_ksqv_tbl(i),
1320 x_ksqv_rec => x_ksqv_tbl(i));
1321
1322 -- store the highest degree of error
1323 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1324 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1325 l_overall_status := x_return_status;
1326 End If;
1327 End If;
1328
1329 EXIT WHEN (i = p_ksqv_tbl.LAST);
1330 i := p_ksqv_tbl.NEXT(i);
1331 END LOOP;
1332 -- return overall status
1333 x_return_status := l_overall_status;
1334 END IF;
1335 EXCEPTION
1336 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1337 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1338 (
1339 l_api_name,
1340 G_PKG_NAME,
1341 'OKC_API.G_RET_STS_ERROR',
1342 x_msg_count,
1343 x_msg_data,
1344 '_PVT'
1345 );
1346 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1347 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1348 (
1349 l_api_name,
1350 G_PKG_NAME,
1351 'OKC_API.G_RET_STS_UNEXP_ERROR',
1352 x_msg_count,
1353 x_msg_data,
1354 '_PVT'
1355 );
1356 WHEN OTHERS THEN
1357 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1358 (
1359 l_api_name,
1360 G_PKG_NAME,
1361 'OTHERS',
1362 x_msg_count,
1363 x_msg_data,
1364 '_PVT'
1365 );
1366 END insert_row;
1367
1368 ---------------------------------------------------------------------------
1369 -- PROCEDURE lock_row
1370 ---------------------------------------------------------------------------
1371 ---------------------------------
1372 -- lock_row for:OKC_K_SEQ_HEADER --
1373 ---------------------------------
1374 PROCEDURE lock_row(
1375 p_init_msg_list IN VARCHAR2 ,
1376 x_return_status OUT NOCOPY VARCHAR2,
1377 x_msg_count OUT NOCOPY NUMBER,
1378 x_msg_data OUT NOCOPY VARCHAR2,
1379 p_ksq_rec IN ksq_rec_type) IS
1380
1381 E_Resource_Busy EXCEPTION;
1382 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1383 CURSOR lock_csr (p_ksq_rec IN ksq_rec_type) IS
1384 SELECT OBJECT_VERSION_NUMBER
1385 FROM OKC_K_SEQ_HEADER
1386 WHERE LINE_CODE = p_ksq_rec.line_code
1387 AND OBJECT_VERSION_NUMBER = p_ksq_rec.object_version_number
1388 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1389
1390 CURSOR lchk_csr (p_ksq_rec IN ksq_rec_type) IS
1391 SELECT OBJECT_VERSION_NUMBER
1392 FROM OKC_K_SEQ_HEADER
1393 WHERE LINE_CODE = p_ksq_rec.line_code;
1394 l_api_version CONSTANT NUMBER := 1;
1395 l_api_name CONSTANT VARCHAR2(30) := 'K_SEQ_lock_row';
1396 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1397 l_object_version_number OKC_K_SEQ_HEADER.OBJECT_VERSION_NUMBER%TYPE;
1398 lc_object_version_number OKC_K_SEQ_HEADER.OBJECT_VERSION_NUMBER%TYPE;
1399 l_row_notfound BOOLEAN := FALSE;
1400 lc_row_notfound BOOLEAN := FALSE;
1401 BEGIN
1402 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1403 p_init_msg_list,
1404 '_PVT',
1405 x_return_status);
1406 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1407 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1408 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1409 RAISE OKC_API.G_EXCEPTION_ERROR;
1410 END IF;
1411 BEGIN
1412 OPEN lock_csr(p_ksq_rec);
1413 FETCH lock_csr INTO l_object_version_number;
1414 l_row_notfound := lock_csr%NOTFOUND;
1415 CLOSE lock_csr;
1416 EXCEPTION
1417 WHEN E_Resource_Busy THEN
1418 IF (lock_csr%ISOPEN) THEN
1419 CLOSE lock_csr;
1420 END IF;
1421 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1422 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1423 END;
1424
1425 IF ( l_row_notfound ) THEN
1426 OPEN lchk_csr(p_ksq_rec);
1427 FETCH lchk_csr INTO lc_object_version_number;
1428 lc_row_notfound := lchk_csr%NOTFOUND;
1429 CLOSE lchk_csr;
1430 END IF;
1431 IF (lc_row_notfound) THEN
1432 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1433 RAISE OKC_API.G_EXCEPTION_ERROR;
1434 ELSIF lc_object_version_number > p_ksq_rec.object_version_number THEN
1435 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1436 RAISE OKC_API.G_EXCEPTION_ERROR;
1437 ELSIF lc_object_version_number <> p_ksq_rec.object_version_number THEN
1438 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1439 RAISE OKC_API.G_EXCEPTION_ERROR;
1440 ELSIF lc_object_version_number = -1 THEN
1441 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1442 RAISE OKC_API.G_EXCEPTION_ERROR;
1443 END IF;
1444 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1445 EXCEPTION
1446 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1447 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1448 (
1449 l_api_name,
1450 G_PKG_NAME,
1451 'OKC_API.G_RET_STS_ERROR',
1452 x_msg_count,
1453 x_msg_data,
1454 '_PVT'
1455 );
1456 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1457 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1458 (
1459 l_api_name,
1460 G_PKG_NAME,
1461 'OKC_API.G_RET_STS_UNEXP_ERROR',
1462 x_msg_count,
1463 x_msg_data,
1464 '_PVT'
1465 );
1466 WHEN OTHERS THEN
1467 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1468 (
1469 l_api_name,
1470 G_PKG_NAME,
1471 'OTHERS',
1472 x_msg_count,
1473 x_msg_data,
1474 '_PVT'
1475 );
1476 END lock_row;
1477 -----------------------------------
1478 -- lock_row for:OKC_K_SEQ_HEADER_V --
1479 -----------------------------------
1480 PROCEDURE lock_row(
1481 p_api_version IN NUMBER,
1482 p_init_msg_list IN VARCHAR2 ,
1483 x_return_status OUT NOCOPY VARCHAR2,
1484 x_msg_count OUT NOCOPY NUMBER,
1485 x_msg_data OUT NOCOPY VARCHAR2,
1486 p_ksqv_rec IN ksqv_rec_type) IS
1487
1488 l_api_version CONSTANT NUMBER := 1;
1489 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1490 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1491 l_ksq_rec ksq_rec_type;
1492 BEGIN
1493 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1494 G_PKG_NAME,
1495 p_init_msg_list,
1496 l_api_version,
1497 p_api_version,
1498 '_PVT',
1499 x_return_status);
1500 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1501 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1502 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1503 RAISE OKC_API.G_EXCEPTION_ERROR;
1504 END IF;
1505 --------------------------------------
1506 -- Move VIEW record to "Child" records
1507 --------------------------------------
1508 migrate(p_ksqv_rec, l_ksq_rec);
1509 --------------------------------------------
1510 -- Call the LOCK_ROW for each child record
1511 --------------------------------------------
1512 lock_row(
1513 p_init_msg_list,
1514 x_return_status,
1515 x_msg_count,
1516 x_msg_data,
1517 l_ksq_rec
1518 );
1519 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1520 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1521 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1522 RAISE OKC_API.G_EXCEPTION_ERROR;
1523 END IF;
1524 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1525 EXCEPTION
1526 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1527 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1528 (
1529 l_api_name,
1530 G_PKG_NAME,
1531 'OKC_API.G_RET_STS_ERROR',
1532 x_msg_count,
1533 x_msg_data,
1534 '_PVT'
1535 );
1536 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1537 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1538 (
1539 l_api_name,
1540 G_PKG_NAME,
1541 'OKC_API.G_RET_STS_UNEXP_ERROR',
1542 x_msg_count,
1543 x_msg_data,
1544 '_PVT'
1545 );
1546 WHEN OTHERS THEN
1547 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1548 (
1549 l_api_name,
1550 G_PKG_NAME,
1551 'OTHERS',
1552 x_msg_count,
1553 x_msg_data,
1554 '_PVT'
1555 );
1556 END lock_row;
1557 --------------------------------------
1558 -- PL/SQL TBL lock_row for:ksqV_TBL --
1559 --------------------------------------
1560 PROCEDURE lock_row(
1561 p_api_version IN NUMBER,
1562 p_init_msg_list IN VARCHAR2 ,
1563 x_return_status OUT NOCOPY VARCHAR2,
1564 x_msg_count OUT NOCOPY NUMBER,
1565 x_msg_data OUT NOCOPY VARCHAR2,
1566 p_ksqv_tbl IN ksqv_tbl_type) IS
1567
1568 l_api_version CONSTANT NUMBER := 1;
1569 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1570 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1571 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1572 i NUMBER := 0;
1573 BEGIN
1574 OKC_API.init_msg_list(p_init_msg_list);
1575 -- Make sure PL/SQL table has records in it before passing
1576 IF (p_ksqv_tbl.COUNT > 0) THEN
1577 i := p_ksqv_tbl.FIRST;
1578 LOOP
1579 lock_row (
1580 p_api_version => p_api_version,
1581 p_init_msg_list => OKC_API.G_FALSE,
1582 x_return_status => x_return_status,
1583 x_msg_count => x_msg_count,
1584 x_msg_data => x_msg_data,
1585 p_ksqv_rec => p_ksqv_tbl(i));
1586
1587 -- store the highest degree of error
1588 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1589 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1590 l_overall_status := x_return_status;
1591 End If;
1592 End If;
1593
1594 EXIT WHEN (i = p_ksqv_tbl.LAST);
1595 i := p_ksqv_tbl.NEXT(i);
1596 END LOOP;
1597 -- return overall status
1598 x_return_status := l_overall_status;
1599 END IF;
1600 EXCEPTION
1601 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1602 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1603 (
1604 l_api_name,
1605 G_PKG_NAME,
1606 'OKC_API.G_RET_STS_ERROR',
1607 x_msg_count,
1608 x_msg_data,
1609 '_PVT'
1610 );
1611 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1612 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1613 (
1614 l_api_name,
1615 G_PKG_NAME,
1616 'OKC_API.G_RET_STS_UNEXP_ERROR',
1617 x_msg_count,
1618 x_msg_data,
1619 '_PVT'
1620 );
1621 WHEN OTHERS THEN
1622 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1623 (
1624 l_api_name,
1625 G_PKG_NAME,
1626 'OTHERS',
1627 x_msg_count,
1628 x_msg_data,
1629 '_PVT'
1630 );
1631 END lock_row;
1632
1633 ---------------------------------------------------------------------------
1634 -- PROCEDURE update_row
1635 ---------------------------------------------------------------------------
1636 -----------------------------------
1637 -- update_row for:OKC_K_SEQ_HEADER --
1638 -----------------------------------
1639 PROCEDURE update_row(
1640 p_init_msg_list IN VARCHAR2 ,
1641 x_return_status OUT NOCOPY VARCHAR2,
1642 x_msg_count OUT NOCOPY NUMBER,
1643 x_msg_data OUT NOCOPY VARCHAR2,
1644 p_ksq_rec IN ksq_rec_type,
1645 x_ksq_rec OUT NOCOPY ksq_rec_type) IS
1646
1647 l_api_version CONSTANT NUMBER := 1;
1648 l_api_name CONSTANT VARCHAR2(30) := 'K_SEQ_update_row';
1649 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1650 l_ksq_rec ksq_rec_type := p_ksq_rec;
1651 l_def_ksq_rec ksq_rec_type;
1652 l_row_notfound BOOLEAN := TRUE;
1653 ----------------------------------
1654 -- FUNCTION populate_new_record --
1655 ----------------------------------
1656 FUNCTION populate_new_record (
1657 p_ksq_rec IN ksq_rec_type,
1658 x_ksq_rec OUT NOCOPY ksq_rec_type
1659 ) RETURN VARCHAR2 IS
1660 l_ksq_rec ksq_rec_type;
1661 l_row_notfound BOOLEAN := TRUE;
1662 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1663 BEGIN
1664 x_ksq_rec := p_ksq_rec;
1665 -- Get current database values
1666 l_ksq_rec := get_rec(p_ksq_rec, l_row_notfound);
1667 IF (l_row_notfound) THEN
1668 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1669 END IF;
1670 IF (x_ksq_rec.line_code = OKC_API.G_MISS_CHAR)
1671 THEN
1672 x_ksq_rec.line_code := l_ksq_rec.line_code;
1673 END IF;
1674 IF (x_ksq_rec.site_yn = OKC_API.G_MISS_CHAR)
1675 THEN
1676 x_ksq_rec.site_yn := l_ksq_rec.site_yn;
1677 END IF;
1678 IF (x_ksq_rec.bg_ou_none = OKC_API.G_MISS_CHAR)
1679 THEN
1680 x_ksq_rec.bg_ou_none := l_ksq_rec.bg_ou_none;
1681 END IF;
1682 IF (x_ksq_rec.cls_scs_none = OKC_API.G_MISS_CHAR)
1683 THEN
1684 x_ksq_rec.cls_scs_none := l_ksq_rec.cls_scs_none;
1685 END IF;
1686 IF (x_ksq_rec.user_function_yn = OKC_API.G_MISS_CHAR)
1687 THEN
1688 x_ksq_rec.user_function_yn := l_ksq_rec.user_function_yn;
1689 END IF;
1690 IF (x_ksq_rec.pdf_id = OKC_API.G_MISS_NUM)
1691 THEN
1692 x_ksq_rec.pdf_id := l_ksq_rec.pdf_id;
1693 END IF;
1694 IF (x_ksq_rec.manual_override_yn = OKC_API.G_MISS_CHAR)
1695 THEN
1696 x_ksq_rec.manual_override_yn := l_ksq_rec.manual_override_yn;
1697 END IF;
1698 IF (x_ksq_rec.object_version_number = OKC_API.G_MISS_NUM)
1699 THEN
1700 x_ksq_rec.object_version_number := l_ksq_rec.object_version_number;
1701 END IF;
1702 IF (x_ksq_rec.created_by = OKC_API.G_MISS_NUM)
1703 THEN
1704 x_ksq_rec.created_by := l_ksq_rec.created_by;
1705 END IF;
1706 IF (x_ksq_rec.creation_date = OKC_API.G_MISS_DATE)
1707 THEN
1708 x_ksq_rec.creation_date := l_ksq_rec.creation_date;
1709 END IF;
1710 IF (x_ksq_rec.last_updated_by = OKC_API.G_MISS_NUM)
1711 THEN
1712 x_ksq_rec.last_updated_by := l_ksq_rec.last_updated_by;
1713 END IF;
1714 IF (x_ksq_rec.last_update_date = OKC_API.G_MISS_DATE)
1715 THEN
1716 x_ksq_rec.last_update_date := l_ksq_rec.last_update_date;
1717 END IF;
1718 IF (x_ksq_rec.last_update_login = OKC_API.G_MISS_NUM)
1719 THEN
1720 x_ksq_rec.last_update_login := l_ksq_rec.last_update_login;
1721 END IF;
1722 RETURN(l_return_status);
1723 END populate_new_record;
1724 ---------------------------------------
1725 -- Set_Attributes for:OKC_K_SEQ_HEADER --
1726 ---------------------------------------
1727 FUNCTION Set_Attributes (
1728 p_ksq_rec IN ksq_rec_type,
1729 x_ksq_rec OUT NOCOPY ksq_rec_type
1730 ) RETURN VARCHAR2 IS
1731 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1732 BEGIN
1733 x_ksq_rec := p_ksq_rec;
1734 RETURN(l_return_status);
1735 END Set_Attributes;
1736 BEGIN
1737 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1738 p_init_msg_list,
1739 '_PVT',
1740 x_return_status);
1741 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1742 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1743 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1744 RAISE OKC_API.G_EXCEPTION_ERROR;
1745 END IF;
1746 --- Setting item attributes
1747 l_return_status := Set_Attributes(
1748 p_ksq_rec, -- IN
1749 l_ksq_rec); -- OUT
1750 --- If any errors happen abort API
1751 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1752 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1753 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1754 RAISE OKC_API.G_EXCEPTION_ERROR;
1755 END IF;
1756 l_return_status := populate_new_record(l_ksq_rec, l_def_ksq_rec);
1757 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1758 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1759 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1760 RAISE OKC_API.G_EXCEPTION_ERROR;
1761 END IF;
1762 UPDATE OKC_K_SEQ_HEADER
1763 SET SITE_YN = l_def_ksq_rec.site_yn,
1764 BG_OU_NONE = l_def_ksq_rec.bg_ou_none,
1765 CLS_SCS_NONE = l_def_ksq_rec.cls_scs_none,
1766 USER_FUNCTION_YN = l_def_ksq_rec.user_function_yn,
1767 PDF_ID = l_def_ksq_rec.pdf_id,
1768 MANUAL_OVERRIDE_YN = l_def_ksq_rec.manual_override_yn,
1769 OBJECT_VERSION_NUMBER = l_def_ksq_rec.object_version_number,
1770 CREATED_BY = l_def_ksq_rec.created_by,
1771 CREATION_DATE = l_def_ksq_rec.creation_date,
1772 LAST_UPDATED_BY = l_def_ksq_rec.last_updated_by,
1773 LAST_UPDATE_DATE = l_def_ksq_rec.last_update_date,
1774 LAST_UPDATE_LOGIN = l_def_ksq_rec.last_update_login
1775 WHERE LINE_CODE = l_def_ksq_rec.line_code;
1776
1777 x_ksq_rec := l_def_ksq_rec;
1778 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1779 EXCEPTION
1780 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1781 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1782 (
1783 l_api_name,
1784 G_PKG_NAME,
1785 'OKC_API.G_RET_STS_ERROR',
1786 x_msg_count,
1787 x_msg_data,
1788 '_PVT'
1789 );
1790 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1791 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1792 (
1793 l_api_name,
1794 G_PKG_NAME,
1795 'OKC_API.G_RET_STS_UNEXP_ERROR',
1796 x_msg_count,
1797 x_msg_data,
1798 '_PVT'
1799 );
1800 WHEN OTHERS THEN
1801 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1802 (
1803 l_api_name,
1804 G_PKG_NAME,
1805 'OTHERS',
1806 x_msg_count,
1807 x_msg_data,
1808 '_PVT'
1809 );
1810 END update_row;
1811 -------------------------------------
1812 -- update_row for:OKC_K_SEQ_HEADER_V --
1813 -------------------------------------
1814 PROCEDURE update_row(
1815 p_api_version IN NUMBER,
1816 p_init_msg_list IN VARCHAR2 ,
1817 x_return_status OUT NOCOPY VARCHAR2,
1818 x_msg_count OUT NOCOPY NUMBER,
1819 x_msg_data OUT NOCOPY VARCHAR2,
1820 p_ksqv_rec IN ksqv_rec_type,
1821 x_ksqv_rec OUT NOCOPY ksqv_rec_type) IS
1822
1823 l_api_version CONSTANT NUMBER := 1;
1824 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1825 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1826 l_ksqv_rec ksqv_rec_type := p_ksqv_rec;
1827 l_def_ksqv_rec ksqv_rec_type;
1828 l_ksq_rec ksq_rec_type;
1829 lx_ksq_rec ksq_rec_type;
1830 -------------------------------
1831 -- FUNCTION fill_who_columns --
1832 -------------------------------
1833 FUNCTION fill_who_columns (
1834 p_ksqv_rec IN ksqv_rec_type
1835 ) RETURN ksqv_rec_type IS
1836 l_ksqv_rec ksqv_rec_type := p_ksqv_rec;
1837 BEGIN
1838 l_ksqv_rec.LAST_UPDATE_DATE := SYSDATE;
1839 l_ksqv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1840 l_ksqv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1841 RETURN(l_ksqv_rec);
1842 END fill_who_columns;
1843 ----------------------------------
1844 -- FUNCTION populate_new_record --
1845 ----------------------------------
1846 FUNCTION populate_new_record (
1847 p_ksqv_rec IN ksqv_rec_type,
1848 x_ksqv_rec OUT NOCOPY ksqv_rec_type
1849 ) RETURN VARCHAR2 IS
1850 l_ksqv_rec ksqv_rec_type;
1851 l_row_notfound BOOLEAN := TRUE;
1852 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1853 BEGIN
1854 x_ksqv_rec := p_ksqv_rec;
1855 -- Get current database values
1856 l_ksqv_rec := get_rec(p_ksqv_rec, l_row_notfound);
1857 IF (l_row_notfound) THEN
1858 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1859 END IF;
1860 IF (x_ksqv_rec.line_code = OKC_API.G_MISS_CHAR)
1861 THEN
1862 x_ksqv_rec.line_code := l_ksqv_rec.line_code;
1863 END IF;
1864 IF (x_ksqv_rec.site_yn = OKC_API.G_MISS_CHAR)
1865 THEN
1866 x_ksqv_rec.site_yn := l_ksqv_rec.site_yn;
1867 END IF;
1868 IF (x_ksqv_rec.bg_ou_none = OKC_API.G_MISS_CHAR)
1869 THEN
1870 x_ksqv_rec.bg_ou_none := l_ksqv_rec.bg_ou_none;
1871 END IF;
1872 IF (x_ksqv_rec.cls_scs_none = OKC_API.G_MISS_CHAR)
1873 THEN
1874 x_ksqv_rec.cls_scs_none := l_ksqv_rec.cls_scs_none;
1875 END IF;
1876 IF (x_ksqv_rec.user_function_yn = OKC_API.G_MISS_CHAR)
1877 THEN
1878 x_ksqv_rec.user_function_yn := l_ksqv_rec.user_function_yn;
1879 END IF;
1880 IF (x_ksqv_rec.pdf_id = OKC_API.G_MISS_NUM)
1881 THEN
1882 x_ksqv_rec.pdf_id := l_ksqv_rec.pdf_id;
1883 END IF;
1884 IF (x_ksqv_rec.manual_override_yn = OKC_API.G_MISS_CHAR)
1885 THEN
1886 x_ksqv_rec.manual_override_yn := l_ksqv_rec.manual_override_yn;
1887 END IF;
1888 IF (x_ksqv_rec.object_version_number = OKC_API.G_MISS_NUM)
1889 THEN
1890 x_ksqv_rec.object_version_number := l_ksqv_rec.object_version_number;
1891 END IF;
1892 IF (x_ksqv_rec.created_by = OKC_API.G_MISS_NUM)
1893 THEN
1894 x_ksqv_rec.created_by := l_ksqv_rec.created_by;
1895 END IF;
1896 IF (x_ksqv_rec.creation_date = OKC_API.G_MISS_DATE)
1897 THEN
1898 x_ksqv_rec.creation_date := l_ksqv_rec.creation_date;
1899 END IF;
1900 IF (x_ksqv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1901 THEN
1902 x_ksqv_rec.last_updated_by := l_ksqv_rec.last_updated_by;
1903 END IF;
1904 IF (x_ksqv_rec.last_update_date = OKC_API.G_MISS_DATE)
1905 THEN
1906 x_ksqv_rec.last_update_date := l_ksqv_rec.last_update_date;
1907 END IF;
1908 IF (x_ksqv_rec.last_update_login = OKC_API.G_MISS_NUM)
1909 THEN
1910 x_ksqv_rec.last_update_login := l_ksqv_rec.last_update_login;
1911 END IF;
1912 RETURN(l_return_status);
1913 END populate_new_record;
1914 -----------------------------------------
1915 -- Set_Attributes for:OKC_K_SEQ_HEADER_V --
1916 -----------------------------------------
1917 FUNCTION Set_Attributes (
1918 p_ksqv_rec IN ksqv_rec_type,
1919 x_ksqv_rec OUT NOCOPY ksqv_rec_type
1920 ) RETURN VARCHAR2 IS
1921 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1922 BEGIN
1923 x_ksqv_rec := p_ksqv_rec;
1924 x_ksqv_rec.OBJECT_VERSION_NUMBER := NVL(x_ksqv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1925 RETURN(l_return_status);
1926 END Set_Attributes;
1927 BEGIN
1928 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1929 G_PKG_NAME,
1930 p_init_msg_list,
1931 l_api_version,
1932 p_api_version,
1933 '_PVT',
1934 x_return_status);
1935 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1936 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1937 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1938 RAISE OKC_API.G_EXCEPTION_ERROR;
1939 END IF;
1940 --- Setting item attributes
1941 l_return_status := Set_Attributes(
1942 p_ksqv_rec, -- IN
1943 l_ksqv_rec); -- OUT
1944 --- If any errors happen abort API
1945 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1946 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1947 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1948 RAISE OKC_API.G_EXCEPTION_ERROR;
1949 END IF;
1950 l_return_status := populate_new_record(l_ksqv_rec, l_def_ksqv_rec);
1951 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1952 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1953 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1954 RAISE OKC_API.G_EXCEPTION_ERROR;
1955 END IF;
1956 l_def_ksqv_rec := fill_who_columns(l_def_ksqv_rec);
1957 --- Validate all non-missing attributes (Item Level Validation)
1958 l_return_status := Validate_Attributes(l_def_ksqv_rec);
1959 --- If any errors happen abort API
1960 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1961 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1962 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1963 RAISE OKC_API.G_EXCEPTION_ERROR;
1964 END IF;
1965 l_return_status := Validate_Record(l_def_ksqv_rec);
1966 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1967 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1968 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1969 RAISE OKC_API.G_EXCEPTION_ERROR;
1970 END IF;
1971
1972 --------------------------------------
1973 -- Move VIEW record to "Child" records
1974 --------------------------------------
1975 migrate(l_def_ksqv_rec, l_ksq_rec);
1976 --------------------------------------------
1977 -- Call the UPDATE_ROW for each child record
1978 --------------------------------------------
1979 update_row(
1980 p_init_msg_list,
1981 x_return_status,
1982 x_msg_count,
1983 x_msg_data,
1984 l_ksq_rec,
1985 lx_ksq_rec
1986 );
1987 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1988 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1989 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1990 RAISE OKC_API.G_EXCEPTION_ERROR;
1991 END IF;
1992 migrate(lx_ksq_rec, l_def_ksqv_rec);
1993 x_ksqv_rec := l_def_ksqv_rec;
1994 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1995 EXCEPTION
1996 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1997 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1998 (
1999 l_api_name,
2000 G_PKG_NAME,
2001 'OKC_API.G_RET_STS_ERROR',
2002 x_msg_count,
2003 x_msg_data,
2004 '_PVT'
2005 );
2006 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2007 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2008 (
2009 l_api_name,
2010 G_PKG_NAME,
2011 'OKC_API.G_RET_STS_UNEXP_ERROR',
2012 x_msg_count,
2013 x_msg_data,
2014 '_PVT'
2015 );
2016 WHEN OTHERS THEN
2017 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2018 (
2019 l_api_name,
2020 G_PKG_NAME,
2021 'OTHERS',
2022 x_msg_count,
2023 x_msg_data,
2024 '_PVT'
2025 );
2026 END update_row;
2027 ----------------------------------------
2028 -- PL/SQL TBL update_row for:ksqV_TBL --
2029 ----------------------------------------
2030 PROCEDURE update_row(
2031 p_api_version IN NUMBER,
2032 p_init_msg_list IN VARCHAR2 ,
2033 x_return_status OUT NOCOPY VARCHAR2,
2034 x_msg_count OUT NOCOPY NUMBER,
2035 x_msg_data OUT NOCOPY VARCHAR2,
2036 p_ksqv_tbl IN ksqv_tbl_type,
2037 x_ksqv_tbl OUT NOCOPY ksqv_tbl_type) IS
2038
2039 l_api_version CONSTANT NUMBER := 1;
2040 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2041 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2042 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2043 i NUMBER := 0;
2044 BEGIN
2045 OKC_API.init_msg_list(p_init_msg_list);
2046 -- Make sure PL/SQL table has records in it before passing
2047 IF (p_ksqv_tbl.COUNT > 0) THEN
2048 i := p_ksqv_tbl.FIRST;
2049 LOOP
2050 update_row (
2051 p_api_version => p_api_version,
2052 p_init_msg_list => OKC_API.G_FALSE,
2053 x_return_status => x_return_status,
2054 x_msg_count => x_msg_count,
2055 x_msg_data => x_msg_data,
2056 p_ksqv_rec => p_ksqv_tbl(i),
2057 x_ksqv_rec => x_ksqv_tbl(i));
2058
2059 -- store the highest degree of error
2060 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2061 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2062 l_overall_status := x_return_status;
2063 End If;
2064 End If;
2065
2066 EXIT WHEN (i = p_ksqv_tbl.LAST);
2067 i := p_ksqv_tbl.NEXT(i);
2068 END LOOP;
2069 -- return overall status
2070 x_return_status := l_overall_status;
2071 END IF;
2072 EXCEPTION
2073 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2074 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2075 (
2076 l_api_name,
2077 G_PKG_NAME,
2078 'OKC_API.G_RET_STS_ERROR',
2079 x_msg_count,
2080 x_msg_data,
2081 '_PVT'
2082 );
2083 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2084 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2085 (
2086 l_api_name,
2087 G_PKG_NAME,
2088 'OKC_API.G_RET_STS_UNEXP_ERROR',
2089 x_msg_count,
2090 x_msg_data,
2091 '_PVT'
2092 );
2093 WHEN OTHERS THEN
2094 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2095 (
2096 l_api_name,
2097 G_PKG_NAME,
2098 'OTHERS',
2099 x_msg_count,
2100 x_msg_data,
2101 '_PVT'
2102 );
2103 END update_row;
2104
2105 ---------------------------------------------------------------------------
2106 -- PROCEDURE delete_row
2107 ---------------------------------------------------------------------------
2108 -----------------------------------
2109 -- delete_row for:OKC_K_SEQ_HEADER --
2110 -----------------------------------
2111 PROCEDURE delete_row(
2112 p_init_msg_list IN VARCHAR2 ,
2113 x_return_status OUT NOCOPY VARCHAR2,
2114 x_msg_count OUT NOCOPY NUMBER,
2115 x_msg_data OUT NOCOPY VARCHAR2,
2116 p_ksq_rec IN ksq_rec_type) IS
2117
2118 l_api_version CONSTANT NUMBER := 1;
2119 l_api_name CONSTANT VARCHAR2(30) := 'K_SEQ_delete_row';
2120 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2121 l_ksq_rec ksq_rec_type:= p_ksq_rec;
2122 l_row_notfound BOOLEAN := TRUE;
2123 BEGIN
2124 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2125 p_init_msg_list,
2126 '_PVT',
2127 x_return_status);
2128 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2129 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2130 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2131 RAISE OKC_API.G_EXCEPTION_ERROR;
2132 END IF;
2133 DELETE FROM OKC_K_SEQ_HEADER
2134 WHERE LINE_CODE = l_ksq_rec.line_code;
2135
2136 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2137 EXCEPTION
2138 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2139 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2140 (
2141 l_api_name,
2142 G_PKG_NAME,
2143 'OKC_API.G_RET_STS_ERROR',
2144 x_msg_count,
2145 x_msg_data,
2146 '_PVT'
2147 );
2148 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2149 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2150 (
2151 l_api_name,
2152 G_PKG_NAME,
2153 'OKC_API.G_RET_STS_UNEXP_ERROR',
2154 x_msg_count,
2155 x_msg_data,
2156 '_PVT'
2157 );
2158 WHEN OTHERS THEN
2159 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2160 (
2161 l_api_name,
2162 G_PKG_NAME,
2163 'OTHERS',
2164 x_msg_count,
2165 x_msg_data,
2166 '_PVT'
2167 );
2168 END delete_row;
2169 -------------------------------------
2170 -- delete_row for:OKC_K_SEQ_HEADER_V --
2171 -------------------------------------
2172 PROCEDURE delete_row(
2173 p_api_version IN NUMBER,
2174 p_init_msg_list IN VARCHAR2 ,
2175 x_return_status OUT NOCOPY VARCHAR2,
2176 x_msg_count OUT NOCOPY NUMBER,
2177 x_msg_data OUT NOCOPY VARCHAR2,
2178 p_ksqv_rec IN ksqv_rec_type) IS
2179
2180 l_api_version CONSTANT NUMBER := 1;
2181 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2182 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2183 l_ksqv_rec ksqv_rec_type := p_ksqv_rec;
2184 l_ksq_rec ksq_rec_type;
2185 BEGIN
2186 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2187 G_PKG_NAME,
2188 p_init_msg_list,
2189 l_api_version,
2190 p_api_version,
2191 '_PVT',
2192 x_return_status);
2193 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2194 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2195 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2196 RAISE OKC_API.G_EXCEPTION_ERROR;
2197 END IF;
2198 --------------------------------------
2199 -- Move VIEW record to "Child" records
2200 --------------------------------------
2201 migrate(l_ksqv_rec, l_ksq_rec);
2202 --------------------------------------------
2203 -- Call the DELETE_ROW for each child record
2204 --------------------------------------------
2205 delete_row(
2206 p_init_msg_list,
2207 x_return_status,
2208 x_msg_count,
2209 x_msg_data,
2210 l_ksq_rec
2211 );
2212 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2213 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2214 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2215 RAISE OKC_API.G_EXCEPTION_ERROR;
2216 END IF;
2217 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2218 EXCEPTION
2219 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2220 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2221 (
2222 l_api_name,
2223 G_PKG_NAME,
2224 'OKC_API.G_RET_STS_ERROR',
2225 x_msg_count,
2226 x_msg_data,
2227 '_PVT'
2228 );
2229 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2230 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2231 (
2232 l_api_name,
2233 G_PKG_NAME,
2234 'OKC_API.G_RET_STS_UNEXP_ERROR',
2235 x_msg_count,
2236 x_msg_data,
2237 '_PVT'
2238 );
2239 WHEN OTHERS THEN
2240 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2241 (
2242 l_api_name,
2243 G_PKG_NAME,
2244 'OTHERS',
2245 x_msg_count,
2246 x_msg_data,
2247 '_PVT'
2248 );
2249 END delete_row;
2250 ----------------------------------------
2251 -- PL/SQL TBL delete_row for:ksqV_TBL --
2252 ----------------------------------------
2253 PROCEDURE delete_row(
2254 p_api_version IN NUMBER,
2255 p_init_msg_list IN VARCHAR2 ,
2256 x_return_status OUT NOCOPY VARCHAR2,
2257 x_msg_count OUT NOCOPY NUMBER,
2258 x_msg_data OUT NOCOPY VARCHAR2,
2259 p_ksqv_tbl IN ksqv_tbl_type) IS
2260
2261 l_api_version CONSTANT NUMBER := 1;
2262 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2263 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2264 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2265 i NUMBER := 0;
2266 BEGIN
2267 OKC_API.init_msg_list(p_init_msg_list);
2268 -- Make sure PL/SQL table has records in it before passing
2269 IF (p_ksqv_tbl.COUNT > 0) THEN
2270 i := p_ksqv_tbl.FIRST;
2271 LOOP
2272 delete_row (
2273 p_api_version => p_api_version,
2274 p_init_msg_list => OKC_API.G_FALSE,
2275 x_return_status => x_return_status,
2276 x_msg_count => x_msg_count,
2277 x_msg_data => x_msg_data,
2278 p_ksqv_rec => p_ksqv_tbl(i));
2279
2280 -- store the highest degree of error
2281 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2282 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2283 l_overall_status := x_return_status;
2284 End If;
2285 End If;
2286
2287 EXIT WHEN (i = p_ksqv_tbl.LAST);
2288 i := p_ksqv_tbl.NEXT(i);
2289 END LOOP;
2290 -- return overall status
2291 x_return_status := l_overall_status;
2292 END IF;
2293 EXCEPTION
2294 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2295 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2296 (
2297 l_api_name,
2298 G_PKG_NAME,
2299 'OKC_API.G_RET_STS_ERROR',
2300 x_msg_count,
2301 x_msg_data,
2302 '_PVT'
2303 );
2304 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2305 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2306 (
2307 l_api_name,
2308 G_PKG_NAME,
2309 'OKC_API.G_RET_STS_UNEXP_ERROR',
2310 x_msg_count,
2311 x_msg_data,
2312 '_PVT'
2313 );
2314 WHEN OTHERS THEN
2315 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2316 (
2317 l_api_name,
2318 G_PKG_NAME,
2319 'OTHERS',
2320 x_msg_count,
2321 x_msg_data,
2322 '_PVT'
2323 );
2324 END delete_row;
2325
2326 PROCEDURE Is_K_Autogenerated(
2327 p_scs_code Varchar2,
2328 x_return_status OUT NOCOPY Varchar2) IS
2329 -- Cursor to get the sequence header information
2330 cursor csr_k is
2331 select line_code,
2332 site_yn,
2333 bg_ou_none,
2334 cls_scs_none,
2335 user_function_yn,
2336 pdf_id,
2337 manual_override_yn
2338 from okc_k_seq_header;
2339
2340 -- Cursor to get the sequence line details
2341 cursor csr_l(p_line_code okc_k_seq_header.line_code%TYPE) is
2342 select doc_sequence_id,
2343 business_group_id,
2344 operating_unit_id,
2345 cls_code,
2346 scs_code,
2347 manual_override_yn,
2348 contract_number_prefix,
2349 contract_number_suffix,
2350 number_format_length,
2351 start_seq_no,
2352 end_seq_no
2353 from okc_k_seq_lines
2354 where line_code = p_line_code;
2355
2356 cursor csr_cls is
2357 select cls_code
2358 from okc_subclasses_b
2359 where code = p_scs_code;
2360
2361 -- l_bug Number(15) := Sys_Context('OKC_CONTEXT', 'BUSINESS_GROUP_ID');
2362 -- l_opu Number(15) := Sys_Context('OKC_CONTEXT', 'ORG_ID');
2363 --npalepu modified on 22-Aug-2006 for bug # 5470760
2364 /* l_bug Number := Sys_Context('OKC_CONTEXT', 'BUSINESS_GROUP_ID');
2365 l_opu Number := Sys_Context('OKC_CONTEXT', 'ORG_ID'); */
2366 l_bug Number ;
2367 l_opu Number ;
2368
2369 CURSOR BUSINESS_GROUP_CSR(V_ORG_ID NUMBER) IS
2370 SELECT BUSINESS_GROUP_ID
2371 FROM HR_ALL_ORGANIZATION_UNITS
2372 WHERE ORGANIZATION_ID = V_ORG_ID;
2373 --end npalepu
2374
2375 l_cls_code okc_subclasses_b.cls_code%TYPE;
2376 l_row_notfound Boolean;
2377
2378 Function cls_scs_found (i number) Return Boolean is
2379 l_found Boolean := False;
2380 --
2381 -- Return value of True means either a match has been found or
2382 -- there is no sequence defined at the class/category level
2383 --
2384 Begin
2385 IF (l_debug = 'Y') THEN
2386 Okc_Debug.Set_Indentation('Is_K_Autogenerted');
2387 Okc_Debug.Log('1600: Entering cls_scs_found', 2);
2388 END IF;
2389 If g_seq_header.cls_scs_none = 'NON' Then
2390 IF (l_debug = 'Y') THEN
2391 Okc_Debug.Log('1700: Not set at Class/category level');
2392 END IF;
2393 l_found := True;
2394 Elsif g_seq_header.cls_scs_none = 'CLS' Then
2395 IF (l_debug = 'Y') THEN
2396 Okc_Debug.Log('1800: Set at Class level');
2397 END IF;
2398 If g_cls_code_tbl(i) = l_cls_code Then
2399 l_found := True;
2400 End If;
2401 Elsif g_seq_header.cls_scs_none = 'SCS' Then
2402 IF (l_debug = 'Y') THEN
2403 Okc_Debug.Log('1900: Set at Category level');
2404 END IF;
2405 If g_scs_code_tbl(i) = p_scs_code Then
2406 l_found := True;
2407 End If;
2408 End If;
2409 IF (l_debug = 'Y') THEN
2410 Okc_Debug.Log('2000: Exiting cls_scs_found', 2);
2411 Okc_Debug.Reset_Indentation;
2412 END IF;
2413 Return l_found;
2414 End;
2415 Begin
2416 IF (l_debug = 'Y') THEN
2417 Okc_Debug.Set_Indentation('Is_K_Autogenerted');
2418 Okc_Debug.Log('100: Is_K_Autogenerted', 2);
2419 END IF;
2420 x_return_status := okc_api.g_false;
2421
2422 --NPALEPU ADDED ON 22-AUG-2006 FOR BUG # 5470760
2423 l_opu := MO_GLOBAL.GET_CURRENT_ORG_ID ;
2424
2425 IF l_opu IS NOT NULL THEN
2426 OPEN BUSINESS_GROUP_CSR(l_opu);
2427 FETCH BUSINESS_GROUP_CSR INTO l_bug;
2428 CLOSE BUSINESS_GROUP_CSR;
2429 END IF;
2430 --END NPALEPU
2431
2432 -- Initialize the globals
2433 g_session_id := Sys_Context('USERENV', 'SESSIONID');
2434 g_business_group_id := l_bug;
2435 g_operating_unit_id := l_opu;
2436 g_scs_code := p_scs_code;
2437
2438 open csr_k;
2439 fetch csr_k into g_seq_header;
2440 l_row_notfound := csr_k%NotFound;
2441 Close csr_k;
2442
2443 If l_row_notfound then
2444 -- If there is no header, there is no setup. So
2445 -- return immediately after setting the global status.
2446 IF (l_debug = 'Y') THEN
2447 Okc_Debug.Log('200: No Header Found');
2448 END IF;
2449 g_seq_status := G_NO_SETUP_FOUND;
2450 Raise g_exception_halt_validation;
2451 End If;
2452
2453 If g_seq_header.user_function_yn = 'Y' Then
2454 -- In case of user defined function, there would not be any
2455 -- line details. Just make sure that pdf has been properly
2456 -- set in this case.
2457 IF (l_debug = 'Y') THEN
2458 Okc_Debug.Log('300: Sequence set for User Function');
2459 END IF;
2460 g_seq_status := G_SETUP_FOUND;
2461 If g_seq_header.pdf_id Is Null Then
2462 g_seq_status := G_NO_PDF_FOUND;
2463 Raise g_exception_halt_validation;
2464 End If;
2465 If g_seq_header.manual_override_yn = 'Y' Then
2466 -- If manual override flag is Yes, that means users can
2467 -- overwrite the contract number in the authoring form,
2468 -- So return false.
2469 Raise g_exception_halt_validation;
2470 End If;
2471 x_return_status := okc_api.g_true;
2472 Raise g_exception_halt_validation;
2473 End If;
2474
2475 -- Get the sequence line details
2476 Open csr_l(g_seq_header.line_code);
2477 Fetch csr_l Bulk Collect
2478 Into g_doc_sequence_id_tbl,
2479 g_business_group_id_tbl,
2480 g_operating_unit_id_tbl,
2481 g_cls_code_tbl,
2482 g_scs_code_tbl,
2483 g_manual_override_yn_tbl,
2484 g_contract_number_prefix_tbl,
2485 g_contract_number_suffix_tbl,
2486 g_number_format_length_tbl,
2487 g_start_seq_no_tbl,
2488 g_end_seq_no_tbl;
2489 Close csr_l;
2490
2491 If g_doc_sequence_id_tbl.count = 0 Then
2492 -- If there is no line details, no sequence can be ganarated.
2493 IF (l_debug = 'Y') THEN
2494 Okc_Debug.Log('400: No details found');
2495 END IF;
2496 g_seq_status := G_NO_SETUP_FOUND;
2497 Raise g_exception_halt_validation;
2498 End If;
2499
2500 -- Get the class code for the categroy.
2501 Open csr_cls;
2502 Fetch csr_cls
2503 Into l_cls_code;
2504 Close csr_cls;
2505
2506 g_index := 0;
2507 If g_seq_header.site_yn = 'Y' Then
2508 IF (l_debug = 'Y') THEN
2509 Okc_Debug.Log('500: Sequence set at Site level');
2510 END IF;
2511 g_index := g_doc_sequence_id_tbl.First;
2512 Elsif g_seq_header.bg_ou_none = 'BUG' Then
2513 IF (l_debug = 'Y') THEN
2514 Okc_Debug.Log('600: Sequence set at Business Group level');
2515 END IF;
2516 For i in g_business_group_id_tbl.First..g_business_group_id_tbl.Last
2517 Loop
2518 If g_business_group_id_tbl(i) = l_bug Then
2519 If Cls_Scs_Found(i) Then
2520 g_index := i;
2521 Exit;
2522 End If;
2523 End If;
2524 End Loop;
2525 Elsif g_seq_header.bg_ou_none = 'OPU' Then
2526 IF (l_debug = 'Y') THEN
2527 Okc_Debug.Log('700: Sequence set at Operating Unit level');
2528 END IF;
2529 For i in g_operating_unit_id_tbl.First..g_operating_unit_id_tbl.Last
2530 Loop
2531 If g_operating_unit_id_tbl(i) = l_opu Then
2532 If Cls_Scs_Found(i) Then
2533 g_index := i;
2534 Exit;
2535 End If;
2536 End If;
2537 End Loop;
2538 Elsif g_seq_header.cls_scs_none = 'CLS' Then
2539 IF (l_debug = 'Y') THEN
2540 Okc_Debug.Log('800: Sequence set at Class level');
2541 END IF;
2542 For i in g_cls_code_tbl.First..g_cls_code_tbl.Last
2543 Loop
2544 If g_cls_code_tbl(i) = l_cls_code Then
2545 g_index := i;
2546 Exit;
2547 End If;
2548 End Loop;
2549 Elsif g_seq_header.cls_scs_none = 'SCS' Then
2550 IF (l_debug = 'Y') THEN
2551 Okc_Debug.Log('900: Sequence set at Category level');
2552 END IF;
2553 For i in g_scs_code_tbl.First..g_scs_code_tbl.Last
2554 Loop
2555 If g_scs_code_tbl(i) = p_scs_code Then
2556 g_index := i;
2557 Exit;
2558 End If;
2559 End Loop;
2560 End If;
2561
2562 If g_index = 0 Then
2563 IF (l_debug = 'Y') THEN
2564 Okc_Debug.Log('1000: No matching details found');
2565 END IF;
2566 g_seq_status := G_NO_SETUP_FOUND;
2567 Raise g_exception_halt_validation;
2568 End If;
2569
2570 g_seq_status := G_SETUP_FOUND;
2571
2572 IF (l_debug = 'Y') THEN
2573 Okc_Debug.Log('1100: Matching details found');
2574 END IF;
2575 If g_manual_override_yn_tbl(g_index) = 'Y' Then
2576 IF (l_debug = 'Y') THEN
2577 Okc_Debug.Log('1200: Manual Override is Yes');
2578 END IF;
2579 Raise g_exception_halt_validation;
2580 End If;
2581
2582 x_return_status := okc_api.g_true;
2583 IF (l_debug = 'Y') THEN
2584 Okc_Debug.Log('1300: Exiting Is_K_Autogenerated', 2);
2585 Okc_Debug.Reset_Indentation;
2586 END IF;
2587 Exception
2588 When g_exception_halt_validation Then
2589 IF (l_debug = 'Y') THEN
2590 Okc_Debug.Log('1400: Exiting Is_K_Autogenerated', 2);
2591 Okc_Debug.Reset_Indentation;
2592 END IF;
2593 When Others Then
2594 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2595 p_msg_name => g_unexpected_error,
2596 p_token1 => g_sqlcode_token,
2597 p_token1_value => sqlcode,
2598 p_token2 => g_sqlerrm_token,
2599 p_token2_value => sqlerrm);
2600 -- set error flag as UNEXPETED error
2601 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2602 IF (l_debug = 'Y') THEN
2603 Okc_Debug.Log('1500: Exiting Is_K_Autogenerated', 2);
2604 Okc_Debug.Reset_Indentation;
2605 END IF;
2606 End Is_K_Autogenerated;
2607
2608 PROCEDURE Get_K_Number(
2609 p_scs_code IN VARCHAR2,
2610 p_contract_number_modifier IN VARCHAR2,
2611 x_contract_number OUT NOCOPY VARCHAR2,
2612 x_return_status OUT NOCOPY VARCHAR2) IS
2613
2614 l_seq Number;
2615 l_seq_len Number;
2616 l_doc_sequence_id Number;
2617 l_contract_number okc_k_headers_b.contract_number%TYPE;
2618 l_dummy Varchar2(1);
2619 l_row_found Boolean := False;
2620 l_row_notfound Boolean := False;
2621 l_return_status Varchar2(30);
2622 l_plsql_block Varchar2(32000);
2623 l_seq_profile Varchar2(1);
2624
2625 cursor c1 (p_doc_sequence_id fnd_document_sequences.doc_Sequence_id%TYPE) is
2626 select application_id,
2627 category_code,
2628 set_of_books_id,
2629 method_code
2630 from fnd_doc_sequence_assignments
2631 where doc_sequence_id = p_doc_sequence_id;
2632
2633 c1_rec c1%RowType;
2634
2635 cursor pdf_cur(p_id okc_process_defs_b.id%TYPE) is
2636 select name,
2637 package_name,
2638 procedure_name
2639 from okc_process_defs_v
2640 where id = p_id;
2641 pdf_rec pdf_cur%RowType;
2642
2643 cursor pdf_param_cur(p_id okc_process_def_parameters_v.pdf_id%TYPE) is
2644 select name,
2645 data_type,
2646 replace(default_value, '''', '''''') parm_value
2647 from okc_process_def_parameters_v
2648 where pdf_id = p_id;
2649
2650 --NPALEPU ADDED ON 22-AUG-2006 FOR BUG # 5470760
2651 l_bug Number ;
2652 l_opu Number ;
2653
2654 CURSOR BUSINESS_GROUP_CSR(V_ORG_ID NUMBER) IS
2655 SELECT BUSINESS_GROUP_ID
2656 FROM HR_ALL_ORGANIZATION_UNITS
2657 WHERE ORGANIZATION_ID = V_ORG_ID;
2658 --END NPALEPU
2659
2660 Function Contract_Is_Unique(p_contract_number IN
2661 okc_k_headers_b.contract_number%TYPE)
2662 Return Boolean Is
2663
2664 cursor c2 is
2665 select 'x'
2666 --npalepu modified on 24-AUG-2006 for bug # 5487532
2667 /* from okc_k_headers_b */
2668 from okc_k_headers_all_b
2669 --end npalepu
2670 where contract_number = p_contract_number
2671 and contract_number_modifier is null;
2672
2673 cursor c3 is
2674 select 'x'
2675 --npalepu modified on 24-AUG-2006 for bug # 5487532
2676 /* from okc_k_headers_b */
2677 from okc_k_headers_all_b
2678 --end npalepu
2679 where contract_number = p_contract_number
2680 and contract_number_modifier = p_contract_number_modifier;
2681
2682 l_ret Boolean;
2683 l_dummy Varchar2(1);
2684 Begin
2685 IF (l_debug = 'Y') THEN
2686 Okc_Debug.Set_Indentation('Contract_Is_Unique');
2687 Okc_Debug.Log('100: Entering Contract_Is_Unique', 2);
2688 Okc_Debug.Log('125: Contract Number: '|| p_contract_number, 2);
2689 Okc_Debug.Log('150: Modifier : '|| p_contract_number_modifier, 2);
2690 END IF;
2691 If p_contract_number_modifier Is Null OR
2692 p_contract_number_modifier = OKC_API.G_MISS_CHAR
2693 Then
2694 IF (l_debug = 'Y') THEN
2695 Okc_Debug.Log('200: cursor opened is c2', 2);
2696 END IF;
2697 Open c2;
2698 Fetch c2 Into l_dummy;
2699 l_ret := c2%NotFound;
2700 Close c2;
2701 Else
2702 IF (l_debug = 'Y') THEN
2703 Okc_Debug.Log('300: cursor opened is c3', 2);
2704 END IF;
2705 Open c3;
2706 Fetch c3 Into l_dummy;
2707 l_ret := c3%NotFound;
2708 Close c3;
2709 End If;
2710 If (l_ret) Then
2711 IF (l_debug = 'Y') THEN
2712 Okc_Debug.Log('400: Contract not exists');
2713 END IF;
2714 Else
2715 IF (l_debug = 'Y') THEN
2716 Okc_Debug.Log('500: Contract exists');
2717 END IF;
2718 End If;
2719 IF (l_debug = 'Y') THEN
2720 Okc_Debug.ReSet_Indentation;
2721 END IF;
2722 Return (l_ret);
2723
2724 End;
2725 Begin
2726 IF (l_debug = 'Y') THEN
2727 Okc_Debug.Set_Indentation('Get_K_Number');
2728 Okc_Debug.Log('2100: Entering Get_K_Number', 2);
2729 END IF;
2730 x_return_status := okc_api.g_ret_sts_success;
2731 -- First of check the sequence profile option. If it is
2732 -- not 'Always Used' then we need to loop to retrieve the
2733 -- contract number from DB sequence until a non-existing
2734 -- combination is found.
2735 -- Bug 2351723: allowed "Partially Used"
2736 --
2737
2738 --
2739 -- Bug 2316874, 2310409, 2169921
2740 -- When contract is created from other applications,
2741 -- auto generation cannot be based on the login profile set up
2742 -- 1. If contract creates from a concurrent program,
2743 -- always generate contract numnber, irrespective of profile option
2744 -- set up. In this case, the following code will set the profile
2745 -- option as Always Used
2746 -- 2. If contract is created online from other applications,
2747 -- Any product integrating with OKC should set this profile option
2748 -- in that product level and handle cases.
2749
2750 If (fnd_global.conc_request_id > 0) Then
2751 fnd_profile.put('UNIQUE:SEQ_NUMBERS','A');
2752 End If;
2753
2754 l_seq_profile := Fnd_Profile.Value('UNIQUE:SEQ_NUMBERS');
2755 IF (l_debug = 'Y') THEN
2756 Okc_Debug.Log('2105: Sequence Profile : ' || l_seq_profile);
2757 END IF;
2758 If Nvl(l_seq_profile, 'N') NOT IN ('A','P') Then
2759 Loop
2760 select okc_k_headers_s1.nextval
2761 into l_contract_number
2762 from dual;
2763 IF (l_debug = 'Y') THEN
2764 Okc_Debug.Log('2106: Contract Number : ' || l_contract_number);
2765 END IF;
2766 Exit When Contract_Is_Unique(l_contract_number);
2767 End Loop;
2768 x_contract_number := l_contract_number;
2769 IF (l_debug = 'Y') THEN
2770 Okc_Debug.Log('2107: before Raise g_exception_halt_validation ');
2771 END IF;
2772 Raise g_exception_halt_validation;
2773 IF (l_debug = 'Y') THEN
2774 Okc_Debug.Log('2108: after Raise g_exception_halt_validation ');
2775 END IF;
2776 End If;
2777 --
2778 -- It is quite likely that this procedure will be called in the
2779 -- same session as is_k_autogenerated. If not call that first
2780 -- that will build up all the globals etc.
2781 --
2782
2783 --NPALEPU ADDED ON 22-AUG-2006 FOR BUG # 5470760
2784 l_opu := MO_GLOBAL.GET_CURRENT_ORG_ID ;
2785
2786 IF l_opu IS NOT NULL THEN
2787 OPEN BUSINESS_GROUP_CSR(l_opu);
2788 FETCH BUSINESS_GROUP_CSR INTO l_bug;
2789 CLOSE BUSINESS_GROUP_CSR;
2790 END IF;
2791 --END NPALEPU
2792
2793 If (g_session_id <> Sys_Context('USERENV', 'SESSIONID')) Or
2794 --npalepu modified for bug # 5470760 on 22-AUG-2006
2795 /*(g_business_group_id <> Sys_Context('OKC_CONTEXT', 'BUSINESS_GROUP_ID')) Or
2796 (g_operating_unit_id <> Sys_Context('OKC_CONTEXT', 'ORG_ID')) Or */
2797 (g_business_group_id <> l_bug) Or
2798 (g_operating_unit_id <> l_opu) Or
2799 --end npalepu
2800 (g_scs_code <> p_scs_code) Then
2801 Is_K_Autogenerated(p_scs_code,
2802 l_return_status);
2803 --
2804 -- Return if there is any error
2805 --
2806 If l_return_status Not In (Okc_Api.g_true, Okc_Api.g_false) Then
2807 x_return_status := okc_api.g_ret_sts_error;
2808 Raise g_exception_halt_validation;
2809 End If;
2810 End If;
2811
2812 --
2813 -- Return also in case setup cannot be found
2814 --
2815 If g_seq_status <> G_SETUP_FOUND Then
2816 If g_seq_status = G_NO_SETUP_FOUND Then
2817 Okc_Api.Set_Message('OKC', 'OKC_SEQ_NO_SETUP_FOUND');
2818 Elsif g_seq_status = G_NO_PDF_FOUND Then
2819 Okc_Api.Set_Message('OKC', 'OKC_SEQ_NO_PDF_FOUND');
2820 End If;
2821 x_return_status := okc_api.g_ret_sts_error;
2822 Raise g_exception_halt_validation;
2823 End If;
2824
2825 If g_seq_header.user_function_yn = 'Y' Then
2826 IF (l_debug = 'Y') THEN
2827 Okc_Debug.Log('2110: User Defined Function');
2828 END IF;
2829 --
2830 -- Get the process defs details
2831 --
2832 Open pdf_cur(g_seq_header.pdf_id);
2833 Fetch pdf_cur Into pdf_rec;
2834 l_row_notfound := pdf_cur%NotFound;
2835 Close pdf_cur;
2836 If l_row_notfound Then
2837 --
2838 -- If process defs could not be found, return with error
2839 --
2840 Okc_Api.Set_Message('OKC', 'OKC_SEQ_INVALID_PDF_FOUND');
2841 x_return_status := okc_api.g_ret_sts_error;
2842 Raise g_exception_halt_validation;
2843 End If;
2844 --
2845 -- Start building up the plsql block here
2846 --
2847 l_plsql_block := 'BEGIN ' ||
2848 pdf_rec.package_name || '.' ||
2849 pdf_rec.procedure_name || '( ' ||
2850 'x_contract_number => :1' ||
2851 ',x_return_status => :2 ';
2852 --
2853 -- Collect all the parameters and add them to the block
2854 --
2855 For pdf_param_rec In pdf_param_cur(g_seq_header.pdf_id)
2856 Loop
2857 l_plsql_block := l_plsql_block ||
2858 ',' || pdf_param_rec.name || ' => ' ||
2859 '''' || pdf_param_rec.parm_value || '''';
2860 End Loop;
2861 l_plsql_block := l_plsql_block || ' ); END;';
2862 IF (l_debug = 'Y') THEN
2863 Okc_Debug.Log('2120: Pl/Sql Block : ' || l_plsql_block);
2864 END IF;
2865 --
2866 -- Finally execute this block dynamically
2867 --
2868 Execute Immediate l_plsql_block
2869 Using IN OUT l_contract_number,
2870 IN OUT l_return_status;
2871 IF (l_debug = 'Y') THEN
2872 Okc_Debug.Log('2130: l_contract_number : ' || l_contract_number);
2873 Okc_Debug.Log('2140: l_return_status : ' || l_return_status);
2874 END IF;
2875 If l_return_status <> Okc_Api.g_ret_sts_success Then
2876 x_return_status := l_return_status;
2877 Raise g_exception_halt_validation;
2878 End If;
2879 --
2880 -- Make sure that the contract number returned along with
2881 -- the modifier is still unique. If not return with error.
2882 -- We cannot do a loop here because we do not know what
2883 -- is inside the function. Probably it might return the same
2884 -- value again and then we will be in an infinite loop.
2885 --
2886 If Not Contract_Is_Unique(l_contract_number) Then
2887 --
2888 -- Means the combination already exists
2889 --
2890 OKC_API.SET_MESSAGE(
2891 p_app_name => g_app_name,
2892 p_msg_name => 'OKC_CONTRACT_EXISTS',
2893 p_token1 => 'VALUE1',
2894 p_token1_value => l_contract_number,
2895 p_token2 => 'VALUE2',
2896 p_token2_value => nvl(p_contract_number_modifier,' '));
2897 x_return_status := okc_api.g_ret_sts_error;
2898 Else
2899 --
2900 -- It is a unique combination. Set the contract number
2901 -- and return with success.
2902 --
2903 x_contract_number := l_contract_number;
2904 End If;
2905 Raise g_exception_halt_validation;
2906 End If;
2907
2908 --
2909 -- Proceed in the usual way if it is not user function.
2910 -- Get the fnd sequence details that will be used to
2911 -- retrieve the sequence.
2912 --
2913 Open c1(g_doc_sequence_id_tbl(g_index));
2914 Fetch c1 Into c1_rec;
2915 Close c1;
2916 Loop
2917 l_return_status := Fnd_Seqnum.Get_Seq_Val(
2918 c1_rec.application_id,
2919 c1_rec.category_code,
2920 c1_rec.set_of_books_id,
2921 c1_rec.method_code,
2922 sysdate,
2923 l_seq,
2924 l_doc_sequence_id);
2925
2926 IF (l_debug = 'Y') THEN
2927 Okc_Debug.Log('2200: l_return_status ' || l_return_status);
2928 END IF;
2929 If To_Number(l_return_status) <> FND_SEQNUM.SEQSUCC Then
2930 x_return_status := okc_api.g_ret_sts_error;
2931 Raise g_exception_halt_validation;
2932 End If;
2933 --
2934 -- The sequence number just generated should not exceed
2935 -- the user limit
2936 --
2937 If g_end_seq_no_tbl(g_index) Is Not Null Then
2938 If l_seq > g_end_seq_no_tbl(g_index) Then
2939 Okc_Api.Set_Message('OKC', 'OKC_SEQ_EXCEED_MAX');
2940 x_return_status := okc_api.g_ret_sts_error;
2941 Raise g_exception_halt_validation;
2942 End If;
2943 End If;
2944 --
2945 -- Format the sequence with prefix, suffix and padding
2946 --
2947 l_seq_len := Length(l_seq);
2948 l_seq_len := Greatest(l_seq_len,
2949 Nvl(g_number_format_length_tbl(g_index),
2950 l_seq_len));
2951 l_contract_number := g_contract_number_prefix_tbl(g_index) ||
2952 Lpad(l_seq, l_seq_len, '0') ||
2953 g_contract_number_suffix_tbl(g_index);
2954 IF (l_debug = 'Y') THEN
2955 Okc_Debug.Log('2300: l_contract_Number ' || l_contract_Number);
2956 END IF;
2957 --
2958 -- Make sure this contract number and the modifier
2959 -- still constitute a unique key, otherwise continue
2960 -- with the next number
2961 --
2962 Exit When Contract_Is_Unique(l_contract_number);
2963 End Loop;
2964 x_contract_number := l_contract_number;
2965 IF (l_debug = 'Y') THEN
2966 Okc_Debug.Log('2400: Final contract_Number ' || l_contract_Number);
2967 Okc_Debug.Log('2500: Exiting Get_K_Number', 2);
2968 Okc_Debug.Reset_Indentation;
2969 END IF;
2970 Exception
2971 When G_EXCEPTION_HALT_VALIDATION Then
2972 IF (l_debug = 'Y') THEN
2973 Okc_Debug.Log('2600: Exiting Get_K_Number', 2);
2974 Okc_Debug.Log('2601: G_EXCEPTION_HALT_VALIDATION occured',2);
2975 Okc_Debug.Reset_Indentation;
2976 END IF;
2977 When OTHERS then
2978 -- store SQL error message on message stack
2979 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2980 p_msg_name => g_unexpected_error,
2981 p_token1 => g_sqlcode_token,
2982 p_token1_value => sqlcode,
2983 p_token2 => g_sqlerrm_token,
2984 p_token2_value => sqlerrm);
2985 -- set error flag as UNEXPETED error
2986 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2987 IF (l_debug = 'Y') THEN
2988 Okc_Debug.Log('2700: Exiting Get_K_Number', 2);
2989 Okc_Debug.Reset_Indentation;
2990 END IF;
2991
2992 END Get_K_Number;
2993
2994 END OKC_KSQ_PVT;