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