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