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