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