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