[Home] [Help]
PACKAGE BODY: APPS.OKS_BILL_LEVEL_ELEMENTS_PVT
Source
1 PACKAGE BODY OKS_BILL_LEVEL_ELEMENTS_PVT AS
2 /* $Header: OKSSBLEB.pls 120.0 2005/05/27 15:27:08 appldev noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE load_error_tbl
6 ---------------------------------------------------------------------------
7 PROCEDURE load_error_tbl (
8 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
9 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
10
11 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
12 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
13 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
14 BEGIN
15 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
16 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
17 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
18 -- automatically increments the index by 1, (making it 2), however, when the GET function
19 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
20 -- message 2. To circumvent this problem, check the amount of messages and compensate.
21 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
22 -- will only update the index variable when 1 and only 1 message is on the stack.
23 IF (last_msg_idx = 1) THEN
24 l_msg_idx := FND_MSG_PUB.G_FIRST;
25 END IF;
26 LOOP
27 fnd_msg_pub.get(
28 p_msg_index => l_msg_idx,
29 p_encoded => fnd_api.g_false,
30 p_data => px_error_rec.msg_data,
31 p_msg_index_out => px_error_rec.msg_count);
32 px_error_tbl(j) := px_error_rec;
33 j := j + 1;
34 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
35 END LOOP;
36 END load_error_tbl;
37 ---------------------------------------------------------------------------
38 -- FUNCTION find_highest_exception
39 ---------------------------------------------------------------------------
40 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
41 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
42 FUNCTION find_highest_exception(
43 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
44 ) RETURN VARCHAR2 IS
45 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
46 i INTEGER := 1;
47 BEGIN
48 IF (p_error_tbl.COUNT > 0) THEN
49 i := p_error_tbl.FIRST;
50 LOOP
51 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
52 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
53 l_return_status := p_error_tbl(i).error_type;
54 END IF;
55 END IF;
56 EXIT WHEN (i = p_error_tbl.LAST);
57 i := p_error_tbl.NEXT(i);
58 END LOOP;
59 END IF;
60 RETURN(l_return_status);
61 END find_highest_exception;
62 ---------------------------------------------------------------------------
63 -- FUNCTION get_seq_id
64 ---------------------------------------------------------------------------
65 FUNCTION get_seq_id RETURN NUMBER IS
66 BEGIN
67 RETURN(okc_p_util.raw_to_number(sys_guid()));
68 END get_seq_id;
69
70 ---------------------------------------------------------------------------
71 -- PROCEDURE qc
72 ---------------------------------------------------------------------------
73 PROCEDURE qc IS
74 BEGIN
75 null;
76 END qc;
77
78 ---------------------------------------------------------------------------
79 -- PROCEDURE change_version
80 ---------------------------------------------------------------------------
81 PROCEDURE change_version IS
82 BEGIN
83 null;
84 END change_version;
85
86 ---------------------------------------------------------------------------
87 -- PROCEDURE api_copy
88 ---------------------------------------------------------------------------
89 PROCEDURE api_copy IS
90 BEGIN
91 null;
92 END api_copy;
93
94 ---------------------------------------------------------------------------
95 -- FUNCTION get_rec for: OKS_LEVEL_ELEMENTS_V
96 ---------------------------------------------------------------------------
97 FUNCTION get_rec (
98 p_letv_rec IN letv_rec_type,
99 x_no_data_found OUT NOCOPY BOOLEAN
100 ) RETURN letv_rec_type IS
101 CURSOR oks_let_pk_csr (p_id IN NUMBER) IS
102 SELECT
103 ID,
104 CLE_ID,
105 DNZ_CHR_ID,
106 PARENT_CLE_ID,
107 SEQUENCE_NUMBER,
108 DATE_START,
109 DATE_END,
110 AMOUNT,
111 DATE_REVENUE_RULE_START,
112 DATE_RECEIVABLE_GL,
113 DATE_TRANSACTION,
114 DATE_DUE,
115 OBJECT_VERSION_NUMBER,
116 DATE_PRINT,
117 DATE_TO_INTERFACE,
118 DATE_COMPLETED,
119 RUL_ID,
120 CREATED_BY,
121 CREATION_DATE,
122 LAST_UPDATED_BY,
123 LAST_UPDATE_DATE
124 FROM Oks_Level_Elements_V
125 WHERE oks_level_elements_v.id = p_id;
126 l_oks_let_pk oks_let_pk_csr%ROWTYPE;
127 l_letv_rec letv_rec_type;
128 BEGIN
129 x_no_data_found := TRUE;
130 -- Get current database values
131 OPEN oks_let_pk_csr (p_letv_rec.id);
132 FETCH oks_let_pk_csr INTO
133 l_letv_rec.id,
134 l_letv_rec.cle_id,
135 l_letv_rec.dnz_chr_id,
136 l_letv_rec.parent_cle_id,
137 l_letv_rec.sequence_number,
138 l_letv_rec.date_start,
139 l_letv_rec.date_end,
140 l_letv_rec.amount,
141 l_letv_rec.date_revenue_rule_start,
142 l_letv_rec.date_receivable_gl,
143 l_letv_rec.date_transaction,
144 l_letv_rec.date_due,
145 l_letv_rec.object_version_number,
146 l_letv_rec.date_print,
147 l_letv_rec.date_to_interface,
148 l_letv_rec.date_completed,
149 l_letv_rec.rul_id,
150 l_letv_rec.created_by,
151 l_letv_rec.creation_date,
152 l_letv_rec.last_updated_by,
153 l_letv_rec.last_update_date;
154 x_no_data_found := oks_let_pk_csr%NOTFOUND;
155 CLOSE oks_let_pk_csr;
156 RETURN(l_letv_rec);
157 END get_rec;
158
159 ------------------------------------------------------------------
160 -- This version of get_rec sets error messages if no data found --
161 ------------------------------------------------------------------
162 FUNCTION get_rec (
163 p_letv_rec IN letv_rec_type,
164 x_return_status OUT NOCOPY VARCHAR2
165 ) RETURN letv_rec_type IS
166 l_letv_rec letv_rec_type;
167 l_row_notfound BOOLEAN := TRUE;
168 BEGIN
169 x_return_status := OKC_API.G_RET_STS_SUCCESS;
170 l_letv_rec := get_rec(p_letv_rec, l_row_notfound);
171 IF (l_row_notfound) THEN
172 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
173 x_return_status := OKC_API.G_RET_STS_ERROR;
174 END IF;
175 RETURN(l_letv_rec);
176 END get_rec;
177 -----------------------------------------------------------
178 -- So we don't have to pass an "l_row_notfound" variable --
179 -----------------------------------------------------------
180 FUNCTION get_rec (
181 p_letv_rec IN letv_rec_type
182 ) RETURN letv_rec_type IS
183 l_row_not_found BOOLEAN := TRUE;
184 BEGIN
185 RETURN(get_rec(p_letv_rec, l_row_not_found));
186 END get_rec;
187 ---------------------------------------------------------------------------
188 -- FUNCTION get_rec for: OKS_LEVEL_ELEMENTS
189 ---------------------------------------------------------------------------
190 FUNCTION get_rec (
191 p_let_rec IN let_rec_type,
192 x_no_data_found OUT NOCOPY BOOLEAN
193 ) RETURN let_rec_type IS
194 CURSOR oks_level_elements_pk_csr (p_id IN NUMBER) IS
195 SELECT
196 ID,
197 CLE_ID,
198 DNZ_CHR_ID,
199 PARENT_CLE_ID,
200 SEQUENCE_NUMBER,
201 DATE_START,
202 DATE_END,
203 AMOUNT,
204 DATE_RECEIVABLE_GL,
205 DATE_REVENUE_RULE_START,
206 DATE_TRANSACTION,
207 DATE_DUE,
208 DATE_PRINT,
209 DATE_TO_INTERFACE,
210 DATE_COMPLETED,
211 OBJECT_VERSION_NUMBER,
212 RUL_ID,
213 CREATED_BY,
214 CREATION_DATE,
215 LAST_UPDATED_BY,
216 LAST_UPDATE_DATE
217 FROM Oks_Level_Elements
218 WHERE oks_level_elements.id = p_id;
219 l_oks_level_elements_pk oks_level_elements_pk_csr%ROWTYPE;
220 l_let_rec let_rec_type;
221 BEGIN
222 x_no_data_found := TRUE;
223 -- Get current database values
224 OPEN oks_level_elements_pk_csr (p_let_rec.id);
225 FETCH oks_level_elements_pk_csr INTO
226 l_let_rec.id,
227 l_let_rec.cle_id,
228 l_let_rec.dnz_chr_id,
229 l_let_rec.parent_cle_id,
230 l_let_rec.sequence_number,
231 l_let_rec.date_start,
232 l_let_rec.date_end,
233 l_let_rec.amount,
234 l_let_rec.date_receivable_gl,
235 l_let_rec.date_revenue_rule_start,
236 l_let_rec.date_transaction,
237 l_let_rec.date_due,
238 l_let_rec.date_print,
239 l_let_rec.date_to_interface,
240 l_let_rec.date_completed,
241 l_let_rec.object_version_number,
242 l_let_rec.rul_id,
243 l_let_rec.created_by,
244 l_let_rec.creation_date,
245 l_let_rec.last_updated_by,
246 l_let_rec.last_update_date;
247 x_no_data_found := oks_level_elements_pk_csr%NOTFOUND;
248 CLOSE oks_level_elements_pk_csr;
249 RETURN(l_let_rec);
250 END get_rec;
251
252 ------------------------------------------------------------------
253 -- This version of get_rec sets error messages if no data found --
254 ------------------------------------------------------------------
255 FUNCTION get_rec (
256 p_let_rec IN let_rec_type,
257 x_return_status OUT NOCOPY VARCHAR2
258 ) RETURN let_rec_type IS
259 l_let_rec let_rec_type;
260 l_row_notfound BOOLEAN := TRUE;
261 BEGIN
262 x_return_status := OKC_API.G_RET_STS_SUCCESS;
263 l_let_rec := get_rec(p_let_rec, l_row_notfound);
264 IF (l_row_notfound) THEN
265 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
266 x_return_status := OKC_API.G_RET_STS_ERROR;
267 END IF;
268 RETURN(l_let_rec);
269 END get_rec;
270 -----------------------------------------------------------
271 -- So we don't have to pass an "l_row_notfound" variable --
272 -----------------------------------------------------------
273 FUNCTION get_rec (
274 p_let_rec IN let_rec_type
275 ) RETURN let_rec_type IS
276 l_row_not_found BOOLEAN := TRUE;
277 BEGIN
278 RETURN(get_rec(p_let_rec, l_row_not_found));
279 END get_rec;
280 ---------------------------------------------------------------------------
281 -- FUNCTION null_out_defaults for: OKS_LEVEL_ELEMENTS_V
282 ---------------------------------------------------------------------------
283 FUNCTION null_out_defaults (
284 p_letv_rec IN letv_rec_type
285 ) RETURN letv_rec_type IS
286 l_letv_rec letv_rec_type := p_letv_rec;
287 BEGIN
288 IF (l_letv_rec.id = OKC_API.G_MISS_NUM ) THEN
289 l_letv_rec.id := NULL;
290 END IF;
291 IF (l_letv_rec.cle_id = OKC_API.G_MISS_NUM ) THEN
292 l_letv_rec.cle_id := NULL;
293 END IF;
294 IF (l_letv_rec.dnz_chr_id = OKC_API.G_MISS_NUM ) THEN
295 l_letv_rec.dnz_chr_id := NULL;
296 END IF;
297 IF (l_letv_rec.parent_cle_id = OKC_API.G_MISS_NUM ) THEN
298 l_letv_rec.parent_cle_id := NULL;
299 END IF;
300 IF (l_letv_rec.sequence_number = OKC_API.G_MISS_CHAR ) THEN
301 l_letv_rec.sequence_number := NULL;
302 END IF;
303 IF (l_letv_rec.date_start = OKC_API.G_MISS_DATE ) THEN
304 l_letv_rec.date_start := NULL;
305 END IF;
306 IF (l_letv_rec.date_end = OKC_API.G_MISS_DATE ) THEN
307 l_letv_rec.date_end := NULL;
308 END IF;
309 IF (l_letv_rec.amount = OKC_API.G_MISS_NUM ) THEN
310 l_letv_rec.amount := NULL;
311 END IF;
312 IF (l_letv_rec.date_revenue_rule_start = OKC_API.G_MISS_DATE ) THEN
313 l_letv_rec.date_revenue_rule_start := NULL;
314 END IF;
315 IF (l_letv_rec.date_receivable_gl = OKC_API.G_MISS_DATE ) THEN
316 l_letv_rec.date_receivable_gl := NULL;
317 END IF;
318 IF (l_letv_rec.date_transaction = OKC_API.G_MISS_DATE ) THEN
319 l_letv_rec.date_transaction := NULL;
320 END IF;
321 IF (l_letv_rec.date_due = OKC_API.G_MISS_DATE ) THEN
322 l_letv_rec.date_due := NULL;
323 END IF;
324 IF (l_letv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
325 l_letv_rec.object_version_number := NULL;
326 END IF;
327 IF (l_letv_rec.date_print = OKC_API.G_MISS_DATE ) THEN
328 l_letv_rec.date_print := NULL;
329 END IF;
330 IF (l_letv_rec.date_to_interface = OKC_API.G_MISS_DATE ) THEN
331 l_letv_rec.date_to_interface := NULL;
332 END IF;
333 IF (l_letv_rec.date_completed = OKC_API.G_MISS_DATE ) THEN
334 l_letv_rec.date_completed := NULL;
335 END IF;
336 IF (l_letv_rec.rul_id = OKC_API.G_MISS_NUM ) THEN
337 l_letv_rec.rul_id := NULL;
338 END IF;
339 IF (l_letv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
340 l_letv_rec.created_by := NULL;
341 END IF;
342 IF (l_letv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
343 l_letv_rec.creation_date := NULL;
344 END IF;
345 IF (l_letv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
346 l_letv_rec.last_updated_by := NULL;
347 END IF;
348 IF (l_letv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
349 l_letv_rec.last_update_date := NULL;
350 END IF;
351 RETURN(l_letv_rec);
352 END null_out_defaults;
353 ---------------------------------
354 -- Validate_Attributes for: ID --
355 ---------------------------------
356 PROCEDURE validate_id(
357 x_return_status OUT NOCOPY VARCHAR2,
358 p_id IN NUMBER) IS
359 BEGIN
360 x_return_status := OKC_API.G_RET_STS_SUCCESS;
361 IF (p_id = OKC_API.G_MISS_NUM OR
362 p_id IS NULL)
363 THEN
364 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
365 x_return_status := OKC_API.G_RET_STS_ERROR;
366 RAISE G_EXCEPTION_HALT_VALIDATION;
367 END IF;
368 EXCEPTION
369 WHEN G_EXCEPTION_HALT_VALIDATION THEN
370 null;
371 WHEN OTHERS THEN
372 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
373 ,p_msg_name => G_UNEXPECTED_ERROR
374 ,p_token1 => G_SQLCODE_TOKEN
375 ,p_token1_value => SQLCODE
376 ,p_token2 => G_SQLERRM_TOKEN
377 ,p_token2_value => SQLERRM);
378 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
379 END validate_id;
380 ----------------------------------------------
381 -- Validate_Attributes for: SEQUENCE_NUMBER --
382 ----------------------------------------------
383 PROCEDURE validate_sequence_number(
384 x_return_status OUT NOCOPY VARCHAR2,
385 p_sequence_number IN VARCHAR2) IS
386 BEGIN
387 x_return_status := OKC_API.G_RET_STS_SUCCESS;
388 IF (p_sequence_number = OKC_API.G_MISS_CHAR OR
389 p_sequence_number IS NULL)
390 THEN
391 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sequence_number');
392 x_return_status := OKC_API.G_RET_STS_ERROR;
393 RAISE G_EXCEPTION_HALT_VALIDATION;
394 END IF;
395 EXCEPTION
396 WHEN G_EXCEPTION_HALT_VALIDATION THEN
397 null;
398 WHEN OTHERS THEN
399 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
400 ,p_msg_name => G_UNEXPECTED_ERROR
401 ,p_token1 => G_SQLCODE_TOKEN
402 ,p_token1_value => SQLCODE
403 ,p_token2 => G_SQLERRM_TOKEN
404 ,p_token2_value => SQLERRM);
405 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
406 END validate_sequence_number;
407 ----------------------------------------------------
408 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
409 ----------------------------------------------------
410 PROCEDURE validate_object_version_number(
411 x_return_status OUT NOCOPY VARCHAR2,
412 p_object_version_number IN NUMBER) IS
413 BEGIN
414 x_return_status := OKC_API.G_RET_STS_SUCCESS;
415 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
416 p_object_version_number IS NULL)
417 THEN
418 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
419 x_return_status := OKC_API.G_RET_STS_ERROR;
420 RAISE G_EXCEPTION_HALT_VALIDATION;
421 END IF;
422 EXCEPTION
423 WHEN G_EXCEPTION_HALT_VALIDATION THEN
424 null;
425 WHEN OTHERS THEN
426 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
427 ,p_msg_name => G_UNEXPECTED_ERROR
428 ,p_token1 => G_SQLCODE_TOKEN
429 ,p_token1_value => SQLCODE
430 ,p_token2 => G_SQLERRM_TOKEN
431 ,p_token2_value => SQLERRM);
432 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
433 END validate_object_version_number;
434 ---------------------------------------------------------------------------
435 -- FUNCTION Validate_Attributes
436 ---------------------------------------------------------------------------
437 --------------------------------------------------
438 -- Validate_Attributes for:OKS_LEVEL_ELEMENTS_V --
439 --------------------------------------------------
440 FUNCTION Validate_Attributes (
441 p_letv_rec IN letv_rec_type
442 ) RETURN VARCHAR2 IS
443 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
444 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
445 BEGIN
446 -----------------------------
447 -- Column Level Validation --
448 -----------------------------
449 -- ***
450 -- id
451 -- ***
452 validate_id(x_return_status, p_letv_rec.id);
453 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
454 l_return_status := x_return_status;
455 RAISE G_EXCEPTION_HALT_VALIDATION;
456 END IF;
457
458 -- ***
459 -- sequence_number
460 -- ***
461 validate_sequence_number(x_return_status, p_letv_rec.sequence_number);
462 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
463 l_return_status := x_return_status;
464 RAISE G_EXCEPTION_HALT_VALIDATION;
465 END IF;
466
467 -- ***
468 -- object_version_number
469 -- ***
470 validate_object_version_number(x_return_status, p_letv_rec.object_version_number);
471 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
472 l_return_status := x_return_status;
473 RAISE G_EXCEPTION_HALT_VALIDATION;
474 END IF;
475
476 RETURN(l_return_status);
477 EXCEPTION
478 WHEN G_EXCEPTION_HALT_VALIDATION THEN
479 RETURN(l_return_status);
480 WHEN OTHERS THEN
481 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
482 ,p_msg_name => G_UNEXPECTED_ERROR
483 ,p_token1 => G_SQLCODE_TOKEN
484 ,p_token1_value => SQLCODE
485 ,p_token2 => G_SQLERRM_TOKEN
486 ,p_token2_value => SQLERRM);
487 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
488 RETURN(l_return_status);
489 END Validate_Attributes;
490 ---------------------------------------------------------------------------
491 -- PROCEDURE Validate_Record
492 ---------------------------------------------------------------------------
493 ----------------------------------------------
494 -- Validate Record for:OKS_LEVEL_ELEMENTS_V --
495 ----------------------------------------------
496 FUNCTION Validate_Record (
497 p_letv_rec IN letv_rec_type,
498 p_db_letv_rec IN letv_rec_type
499 ) RETURN VARCHAR2 IS
500 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
501 BEGIN
502 RETURN (l_return_status);
503 END Validate_Record;
504 FUNCTION Validate_Record (
505 p_letv_rec IN letv_rec_type
506 ) RETURN VARCHAR2 IS
507 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
508 l_db_letv_rec letv_rec_type := get_rec(p_letv_rec);
509 BEGIN
510 l_return_status := Validate_Record(p_letv_rec => p_letv_rec,
511 p_db_letv_rec => l_db_letv_rec);
512 RETURN (l_return_status);
513 END Validate_Record;
514
515 ---------------------------------------------------------------------------
516 -- PROCEDURE Migrate
517 ---------------------------------------------------------------------------
518 PROCEDURE migrate (
519 p_from IN letv_rec_type,
520 p_to IN OUT NOCOPY let_rec_type
521 ) IS
522 BEGIN
523 p_to.id := p_from.id;
524 p_to.cle_id := p_from.cle_id;
525 p_to.dnz_chr_id := p_from.dnz_chr_id;
526 p_to.parent_cle_id := p_from.parent_cle_id;
527 p_to.sequence_number := p_from.sequence_number;
528 p_to.date_start := p_from.date_start;
529 p_to.date_end := p_from.date_end;
530 p_to.amount := p_from.amount;
531 p_to.date_receivable_gl := p_from.date_receivable_gl;
532 p_to.date_revenue_rule_start := p_from.date_revenue_rule_start;
533 p_to.date_transaction := p_from.date_transaction;
534 p_to.date_due := p_from.date_due;
535 p_to.date_print := p_from.date_print;
536 p_to.date_to_interface := p_from.date_to_interface;
537 p_to.date_completed := p_from.date_completed;
538 p_to.object_version_number := p_from.object_version_number;
539 p_to.rul_id := p_from.rul_id;
540 p_to.created_by := p_from.created_by;
541 p_to.creation_date := p_from.creation_date;
542 p_to.last_updated_by := p_from.last_updated_by;
543 p_to.last_update_date := p_from.last_update_date;
544 END migrate;
545 PROCEDURE migrate (
546 p_from IN let_rec_type,
547 p_to IN OUT NOCOPY letv_rec_type
548 ) IS
549 BEGIN
550 p_to.id := p_from.id;
551 p_to.cle_id := p_from.cle_id;
552 p_to.dnz_chr_id := p_from.dnz_chr_id;
553 p_to.parent_cle_id := p_from.parent_cle_id;
554 p_to.sequence_number := p_from.sequence_number;
555 p_to.date_start := p_from.date_start;
556 p_to.date_end := p_from.date_end;
557 p_to.amount := p_from.amount;
558 p_to.date_revenue_rule_start := p_from.date_revenue_rule_start;
559 p_to.date_receivable_gl := p_from.date_receivable_gl;
560 p_to.date_transaction := p_from.date_transaction;
561 p_to.date_due := p_from.date_due;
562 p_to.object_version_number := p_from.object_version_number;
563 p_to.date_print := p_from.date_print;
564 p_to.date_to_interface := p_from.date_to_interface;
565 p_to.date_completed := p_from.date_completed;
566 p_to.rul_id := p_from.rul_id;
567 p_to.created_by := p_from.created_by;
568 p_to.creation_date := p_from.creation_date;
569 p_to.last_updated_by := p_from.last_updated_by;
570 p_to.last_update_date := p_from.last_update_date;
571 END migrate;
572 ---------------------------------------------------------------------------
573 -- PROCEDURE validate_row
574 ---------------------------------------------------------------------------
575 -------------------------------------------
576 -- validate_row for:OKS_LEVEL_ELEMENTS_V --
577 -------------------------------------------
578 PROCEDURE validate_row(
579 p_api_version IN NUMBER,
580 p_init_msg_list IN VARCHAR2,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2,
584 p_letv_rec IN letv_rec_type) IS
585
586 l_api_version CONSTANT NUMBER := 1;
587 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
588 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
589 l_letv_rec letv_rec_type := p_letv_rec;
590 l_let_rec let_rec_type;
591 l_let_rec let_rec_type;
592 BEGIN
593 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
594 G_PKG_NAME,
595 p_init_msg_list,
596 l_api_version,
597 p_api_version,
598 '_PVT',
599 x_return_status);
600 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
601 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
602 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
603 RAISE OKC_API.G_EXCEPTION_ERROR;
604 END IF;
605 --- Validate all non-missing attributes (Item Level Validation)
606 l_return_status := Validate_Attributes(l_letv_rec);
607 --- If any errors happen abort API
608 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
609 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
610 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
611 RAISE OKC_API.G_EXCEPTION_ERROR;
612 END IF;
613 l_return_status := Validate_Record(l_letv_rec);
614 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
615 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
616 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
617 RAISE OKC_API.G_EXCEPTION_ERROR;
618 END IF;
619 x_return_status := l_return_status;
620 EXCEPTION
621 WHEN OKC_API.G_EXCEPTION_ERROR THEN
622 x_return_status := OKC_API.HANDLE_EXCEPTIONS
623 (
624 l_api_name,
625 G_PKG_NAME,
626 'OKC_API.G_RET_STS_ERROR',
627 x_msg_count,
628 x_msg_data,
629 '_PVT'
630 );
631 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
632 x_return_status := OKC_API.HANDLE_EXCEPTIONS
633 (
634 l_api_name,
635 G_PKG_NAME,
636 'OKC_API.G_RET_STS_UNEXP_ERROR',
637 x_msg_count,
638 x_msg_data,
639 '_PVT'
640 );
641 WHEN OTHERS THEN
642 x_return_status := OKC_API.HANDLE_EXCEPTIONS
643 (
644 l_api_name,
645 G_PKG_NAME,
646 'OTHERS',
647 x_msg_count,
648 x_msg_data,
649 '_PVT'
650 );
651 END validate_row;
652 ------------------------------------------------------
653 -- PL/SQL TBL validate_row for:OKS_LEVEL_ELEMENTS_V --
654 ------------------------------------------------------
655 PROCEDURE validate_row(
656 p_api_version IN NUMBER,
657 p_init_msg_list IN VARCHAR2,
658 x_return_status OUT NOCOPY VARCHAR2,
659 x_msg_count OUT NOCOPY NUMBER,
660 x_msg_data OUT NOCOPY VARCHAR2,
661 p_letv_tbl IN letv_tbl_type,
662 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
663
664 l_api_version CONSTANT NUMBER := 1;
665 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
666 i NUMBER := 0;
667 BEGIN
668 OKC_API.init_msg_list(p_init_msg_list);
669 -- Make sure PL/SQL table has records in it before passing
670 IF (p_letv_tbl.COUNT > 0) THEN
671 i := p_letv_tbl.FIRST;
672 LOOP
673 DECLARE
674 l_error_rec OKC_API.ERROR_REC_TYPE;
675 BEGIN
676 l_error_rec.api_name := l_api_name;
677 l_error_rec.api_package := G_PKG_NAME;
678 l_error_rec.idx := i;
679 validate_row (
680 p_api_version => p_api_version,
681 p_init_msg_list => OKC_API.G_FALSE,
682 x_return_status => l_error_rec.error_type,
683 x_msg_count => l_error_rec.msg_count,
684 x_msg_data => l_error_rec.msg_data,
685 p_letv_rec => p_letv_tbl(i));
686 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
687 l_error_rec.sqlcode := SQLCODE;
688 load_error_tbl(l_error_rec, px_error_tbl);
689 ELSE
690 x_msg_count := l_error_rec.msg_count;
691 x_msg_data := l_error_rec.msg_data;
692 END IF;
693 EXCEPTION
694 WHEN OKC_API.G_EXCEPTION_ERROR THEN
695 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
696 l_error_rec.sqlcode := SQLCODE;
697 load_error_tbl(l_error_rec, px_error_tbl);
698 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
699 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
700 l_error_rec.sqlcode := SQLCODE;
701 load_error_tbl(l_error_rec, px_error_tbl);
702 WHEN OTHERS THEN
703 l_error_rec.error_type := 'OTHERS';
704 l_error_rec.sqlcode := SQLCODE;
705 load_error_tbl(l_error_rec, px_error_tbl);
706 END;
707 EXIT WHEN (i = p_letv_tbl.LAST);
708 i := p_letv_tbl.NEXT(i);
709 END LOOP;
710 END IF;
711 -- Loop through the error_tbl to find the error with the highest severity
712 -- and return it.
713 x_return_status := find_highest_exception(px_error_tbl);
714 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
715 EXCEPTION
716 WHEN OKC_API.G_EXCEPTION_ERROR THEN
717 x_return_status := OKC_API.HANDLE_EXCEPTIONS
718 (
719 l_api_name,
720 G_PKG_NAME,
721 'OKC_API.G_RET_STS_ERROR',
722 x_msg_count,
723 x_msg_data,
724 '_PVT'
725 );
726 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
727 x_return_status := OKC_API.HANDLE_EXCEPTIONS
728 (
729 l_api_name,
730 G_PKG_NAME,
731 'OKC_API.G_RET_STS_UNEXP_ERROR',
732 x_msg_count,
733 x_msg_data,
734 '_PVT'
735 );
736 WHEN OTHERS THEN
737 x_return_status := OKC_API.HANDLE_EXCEPTIONS
738 (
739 l_api_name,
740 G_PKG_NAME,
741 'OTHERS',
742 x_msg_count,
743 x_msg_data,
744 '_PVT'
745 );
746 END validate_row;
747
748 ------------------------------------------------------
749 -- PL/SQL TBL validate_row for:OKS_LEVEL_ELEMENTS_V --
750 ------------------------------------------------------
751 PROCEDURE validate_row(
752 p_api_version IN NUMBER,
753 p_init_msg_list IN VARCHAR2,
754 x_return_status OUT NOCOPY VARCHAR2,
755 x_msg_count OUT NOCOPY NUMBER,
756 x_msg_data OUT NOCOPY VARCHAR2,
757 p_letv_tbl IN letv_tbl_type) IS
758
759 l_api_version CONSTANT NUMBER := 1;
760 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
761 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
762 l_error_tbl OKC_API.ERROR_TBL_TYPE;
763 BEGIN
764 OKC_API.init_msg_list(p_init_msg_list);
765 -- Make sure PL/SQL table has records in it before passing
766 IF (p_letv_tbl.COUNT > 0) THEN
767 validate_row (
768 p_api_version => p_api_version,
769 p_init_msg_list => OKC_API.G_FALSE,
770 x_return_status => x_return_status,
771 x_msg_count => x_msg_count,
772 x_msg_data => x_msg_data,
773 p_letv_tbl => p_letv_tbl,
774 px_error_tbl => l_error_tbl);
775 END IF;
776 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
777 EXCEPTION
778 WHEN OKC_API.G_EXCEPTION_ERROR THEN
779 x_return_status := OKC_API.HANDLE_EXCEPTIONS
780 (
781 l_api_name,
782 G_PKG_NAME,
783 'OKC_API.G_RET_STS_ERROR',
784 x_msg_count,
785 x_msg_data,
786 '_PVT'
787 );
788 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
789 x_return_status := OKC_API.HANDLE_EXCEPTIONS
790 (
791 l_api_name,
792 G_PKG_NAME,
793 'OKC_API.G_RET_STS_UNEXP_ERROR',
794 x_msg_count,
795 x_msg_data,
796 '_PVT'
797 );
798 WHEN OTHERS THEN
799 x_return_status := OKC_API.HANDLE_EXCEPTIONS
800 (
801 l_api_name,
802 G_PKG_NAME,
803 'OTHERS',
804 x_msg_count,
805 x_msg_data,
806 '_PVT'
807 );
808 END validate_row;
809
810 ---------------------------------------------------------------------------
811 -- PROCEDURE insert_row
812 ---------------------------------------------------------------------------
813 ---------------------------------------
814 -- insert_row for:OKS_LEVEL_ELEMENTS --
815 ---------------------------------------
816 PROCEDURE insert_row(
817 p_init_msg_list IN VARCHAR2,
818 x_return_status OUT NOCOPY VARCHAR2,
819 x_msg_count OUT NOCOPY NUMBER,
820 x_msg_data OUT NOCOPY VARCHAR2,
821 p_let_rec IN let_rec_type,
822 x_let_rec OUT NOCOPY let_rec_type) IS
823
824 l_api_version CONSTANT NUMBER := 1;
825 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
826 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
827 l_let_rec let_rec_type := p_let_rec;
828 l_def_let_rec let_rec_type;
829 -------------------------------------------
830 -- Set_Attributes for:OKS_LEVEL_ELEMENTS --
831 -------------------------------------------
832 FUNCTION Set_Attributes (
833 p_let_rec IN let_rec_type,
834 x_let_rec OUT NOCOPY let_rec_type
835 ) RETURN VARCHAR2 IS
836 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
837 BEGIN
838 x_let_rec := p_let_rec;
839 RETURN(l_return_status);
840 END Set_Attributes;
841 BEGIN
842 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
843 p_init_msg_list,
844 '_PVT',
845 x_return_status);
846 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
847 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
848 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
849 RAISE OKC_API.G_EXCEPTION_ERROR;
850 END IF;
851 --- Setting item atributes
852 l_return_status := Set_Attributes(
853 p_let_rec, -- IN
854 l_let_rec); -- OUT
855 --- If any errors happen abort API
856 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
857 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
858 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
859 RAISE OKC_API.G_EXCEPTION_ERROR;
860 END IF;
861 INSERT INTO OKS_LEVEL_ELEMENTS(
862 id,
863 cle_id,
864 dnz_chr_id,
865 parent_cle_id,
866 sequence_number,
867 date_start,
868 date_end,
869 amount,
870 date_receivable_gl,
871 date_revenue_rule_start,
872 date_transaction,
873 date_due,
874 date_print,
875 date_to_interface,
876 date_completed,
877 object_version_number,
878 rul_id,
879 created_by,
880 creation_date,
881 last_updated_by,
882 last_update_date)
883 VALUES (
884 l_let_rec.id,
885 l_let_rec.cle_id,
886 l_let_rec.dnz_chr_id,
887 l_let_rec.parent_cle_id,
888 l_let_rec.sequence_number,
889 l_let_rec.date_start,
890 l_let_rec.date_end,
891 l_let_rec.amount,
892 l_let_rec.date_receivable_gl,
893 l_let_rec.date_revenue_rule_start,
894 l_let_rec.date_transaction,
895 l_let_rec.date_due,
896 l_let_rec.date_print,
897 l_let_rec.date_to_interface,
898 l_let_rec.date_completed,
899 l_let_rec.object_version_number,
900 l_let_rec.rul_id,
901 l_let_rec.created_by,
902 l_let_rec.creation_date,
903 l_let_rec.last_updated_by,
904 l_let_rec.last_update_date);
905 -- Set OUT values
906 x_let_rec := l_let_rec;
907 x_return_status := l_return_status;
908 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
909 EXCEPTION
910 WHEN OKC_API.G_EXCEPTION_ERROR THEN
911 x_return_status := OKC_API.HANDLE_EXCEPTIONS
912 (
913 l_api_name,
914 G_PKG_NAME,
915 'OKC_API.G_RET_STS_ERROR',
916 x_msg_count,
917 x_msg_data,
918 '_PVT'
919 );
920 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
921 x_return_status := OKC_API.HANDLE_EXCEPTIONS
922 (
923 l_api_name,
924 G_PKG_NAME,
925 'OKC_API.G_RET_STS_UNEXP_ERROR',
926 x_msg_count,
927 x_msg_data,
928 '_PVT'
929 );
930 WHEN OTHERS THEN
931 x_return_status := OKC_API.HANDLE_EXCEPTIONS
932 (
933 l_api_name,
934 G_PKG_NAME,
935 'OTHERS',
936 x_msg_count,
937 x_msg_data,
938 '_PVT'
939 );
940 END insert_row;
941 ------------------------------------------
942 -- insert_row for :OKS_LEVEL_ELEMENTS_V --
943 ------------------------------------------
944 PROCEDURE insert_row(
945 p_api_version IN NUMBER,
946 p_init_msg_list IN VARCHAR2,
947 x_return_status OUT NOCOPY VARCHAR2,
948 x_msg_count OUT NOCOPY NUMBER,
949 x_msg_data OUT NOCOPY VARCHAR2,
950 p_letv_rec IN letv_rec_type,
951 x_letv_rec OUT NOCOPY letv_rec_type) IS
952
953 l_api_version CONSTANT NUMBER := 1;
954 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
955 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
956 l_letv_rec letv_rec_type := p_letv_rec;
957 l_def_letv_rec letv_rec_type;
958 l_let_rec let_rec_type;
959 lx_let_rec let_rec_type;
960 -------------------------------
961 -- FUNCTION fill_who_columns --
962 -------------------------------
963 FUNCTION fill_who_columns (
964 p_letv_rec IN letv_rec_type
965 ) RETURN letv_rec_type IS
966 l_letv_rec letv_rec_type := p_letv_rec;
967 BEGIN
968 l_letv_rec.CREATION_DATE := SYSDATE;
969 l_letv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
970 l_letv_rec.LAST_UPDATE_DATE := l_letv_rec.CREATION_DATE;
971 l_letv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
972 -- l_letv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
973 RETURN(l_letv_rec);
974 END fill_who_columns;
975 ---------------------------------------------
976 -- Set_Attributes for:OKS_LEVEL_ELEMENTS_V --
977 ---------------------------------------------
978 FUNCTION Set_Attributes (
979 p_letv_rec IN letv_rec_type,
980 x_letv_rec OUT NOCOPY letv_rec_type
981 ) RETURN VARCHAR2 IS
982 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
983 BEGIN
984 x_letv_rec := p_letv_rec;
985 x_letv_rec.OBJECT_VERSION_NUMBER := 1;
986 RETURN(l_return_status);
987 END Set_Attributes;
988 BEGIN
989 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
990 G_PKG_NAME,
991 p_init_msg_list,
992 l_api_version,
993 p_api_version,
994 '_PVT',
995 x_return_status);
996 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
997 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
998 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
999 RAISE OKC_API.G_EXCEPTION_ERROR;
1000 END IF;
1001 l_letv_rec := null_out_defaults(p_letv_rec);
1002 -- Set primary key value
1003 l_letv_rec.ID := get_seq_id;
1004 -- Setting item attributes
1005 l_return_Status := Set_Attributes(
1006 l_letv_rec, -- IN
1007 l_def_letv_rec); -- OUT
1008 --- If any errors happen abort API
1009 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1010 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1011 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1012 RAISE OKC_API.G_EXCEPTION_ERROR;
1013 END IF;
1014 l_def_letv_rec := fill_who_columns(l_def_letv_rec);
1015 --- Validate all non-missing attributes (Item Level Validation)
1016 l_return_status := Validate_Attributes(l_def_letv_rec);
1017 --- If any errors happen abort API
1018 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1019 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1020 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1021 RAISE OKC_API.G_EXCEPTION_ERROR;
1022 END IF;
1023 l_return_status := Validate_Record(l_def_letv_rec);
1024 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1025 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1026 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1027 RAISE OKC_API.G_EXCEPTION_ERROR;
1028 END IF;
1029 -----------------------------------------
1030 -- Move VIEW record to "Child" records --
1031 -----------------------------------------
1032 migrate(l_def_letv_rec, l_let_rec);
1033 -----------------------------------------------
1034 -- Call the INSERT_ROW for each child record --
1035 -----------------------------------------------
1036 insert_row(
1037 p_init_msg_list,
1038 l_return_status,
1039 x_msg_count,
1040 x_msg_data,
1041 l_let_rec,
1042 lx_let_rec
1043 );
1044 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1045 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1046 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1047 RAISE OKC_API.G_EXCEPTION_ERROR;
1048 END IF;
1049 migrate(lx_let_rec, l_def_letv_rec);
1050 -- Set OUT values
1051 x_letv_rec := l_def_letv_rec;
1052 x_return_status := l_return_status;
1053 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1054 EXCEPTION
1055 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1056 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1057 (
1058 l_api_name,
1059 G_PKG_NAME,
1060 'OKC_API.G_RET_STS_ERROR',
1061 x_msg_count,
1062 x_msg_data,
1063 '_PVT'
1064 );
1065 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1066 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1067 (
1068 l_api_name,
1069 G_PKG_NAME,
1070 'OKC_API.G_RET_STS_UNEXP_ERROR',
1071 x_msg_count,
1072 x_msg_data,
1073 '_PVT'
1074 );
1075 WHEN OTHERS THEN
1076 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1077 (
1078 l_api_name,
1079 G_PKG_NAME,
1080 'OTHERS',
1081 x_msg_count,
1082 x_msg_data,
1083 '_PVT'
1084 );
1085 END insert_row;
1086 ----------------------------------------
1087 -- PL/SQL TBL insert_row for:LETV_TBL --
1088 ----------------------------------------
1089 PROCEDURE insert_row(
1090 p_api_version IN NUMBER,
1091 p_init_msg_list IN VARCHAR2,
1092 x_return_status OUT NOCOPY VARCHAR2,
1093 x_msg_count OUT NOCOPY NUMBER,
1094 x_msg_data OUT NOCOPY VARCHAR2,
1095 p_letv_tbl IN letv_tbl_type,
1096 x_letv_tbl OUT NOCOPY letv_tbl_type,
1097 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1098
1099 l_api_version CONSTANT NUMBER := 1;
1100 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1101 i NUMBER := 0;
1102 BEGIN
1103 OKC_API.init_msg_list(p_init_msg_list);
1104 -- Make sure PL/SQL table has records in it before passing
1105 IF (p_letv_tbl.COUNT > 0) THEN
1106 i := p_letv_tbl.FIRST;
1107 LOOP
1108 DECLARE
1109 l_error_rec OKC_API.ERROR_REC_TYPE;
1110 BEGIN
1111 l_error_rec.api_name := l_api_name;
1112 l_error_rec.api_package := G_PKG_NAME;
1113 l_error_rec.idx := i;
1114 insert_row (
1115 p_api_version => p_api_version,
1116 p_init_msg_list => OKC_API.G_FALSE,
1117 x_return_status => l_error_rec.error_type,
1118 x_msg_count => l_error_rec.msg_count,
1119 x_msg_data => l_error_rec.msg_data,
1120 p_letv_rec => p_letv_tbl(i),
1121 x_letv_rec => x_letv_tbl(i));
1122 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1123 l_error_rec.sqlcode := SQLCODE;
1124 load_error_tbl(l_error_rec, px_error_tbl);
1125 ELSE
1126 x_msg_count := l_error_rec.msg_count;
1127 x_msg_data := l_error_rec.msg_data;
1128 END IF;
1129 EXCEPTION
1130 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1131 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1132 l_error_rec.sqlcode := SQLCODE;
1133 load_error_tbl(l_error_rec, px_error_tbl);
1134 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1135 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1136 l_error_rec.sqlcode := SQLCODE;
1137 load_error_tbl(l_error_rec, px_error_tbl);
1138 WHEN OTHERS THEN
1139 l_error_rec.error_type := 'OTHERS';
1140 l_error_rec.sqlcode := SQLCODE;
1141 load_error_tbl(l_error_rec, px_error_tbl);
1142 END;
1143 EXIT WHEN (i = p_letv_tbl.LAST);
1144 i := p_letv_tbl.NEXT(i);
1145 END LOOP;
1146 END IF;
1147 -- Loop through the error_tbl to find the error with the highest severity
1148 -- and return it.
1149 x_return_status := find_highest_exception(px_error_tbl);
1150 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1151 EXCEPTION
1152 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1153 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1154 (
1155 l_api_name,
1156 G_PKG_NAME,
1157 'OKC_API.G_RET_STS_ERROR',
1158 x_msg_count,
1159 x_msg_data,
1160 '_PVT'
1161 );
1162 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1163 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1164 (
1165 l_api_name,
1166 G_PKG_NAME,
1167 'OKC_API.G_RET_STS_UNEXP_ERROR',
1168 x_msg_count,
1169 x_msg_data,
1170 '_PVT'
1171 );
1172 WHEN OTHERS THEN
1173 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1174 (
1175 l_api_name,
1176 G_PKG_NAME,
1177 'OTHERS',
1178 x_msg_count,
1179 x_msg_data,
1180 '_PVT'
1181 );
1182 END insert_row;
1183
1184 ----------------------------------------
1185 -- PL/SQL TBL insert_row for:LETV_TBL --
1186 ----------------------------------------
1187 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
1188 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
1189 PROCEDURE insert_row(
1190 p_api_version IN NUMBER,
1191 p_init_msg_list IN VARCHAR2,
1192 x_return_status OUT NOCOPY VARCHAR2,
1193 x_msg_count OUT NOCOPY NUMBER,
1194 x_msg_data OUT NOCOPY VARCHAR2,
1195 p_letv_tbl IN letv_tbl_type,
1196 x_letv_tbl OUT NOCOPY letv_tbl_type) IS
1197
1198 l_api_version CONSTANT NUMBER := 1;
1199 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1200 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1201 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1202 BEGIN
1203 OKC_API.init_msg_list(p_init_msg_list);
1204 -- Make sure PL/SQL table has records in it before passing
1205 IF (p_letv_tbl.COUNT > 0) THEN
1206 insert_row (
1207 p_api_version => p_api_version,
1208 p_init_msg_list => OKC_API.G_FALSE,
1209 x_return_status => x_return_status,
1210 x_msg_count => x_msg_count,
1211 x_msg_data => x_msg_data,
1212 p_letv_tbl => p_letv_tbl,
1213 x_letv_tbl => x_letv_tbl,
1214 px_error_tbl => l_error_tbl);
1215 END IF;
1216 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1217 EXCEPTION
1218 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1219 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1220 (
1221 l_api_name,
1222 G_PKG_NAME,
1223 'OKC_API.G_RET_STS_ERROR',
1224 x_msg_count,
1225 x_msg_data,
1226 '_PVT'
1227 );
1228 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1229 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1230 (
1231 l_api_name,
1232 G_PKG_NAME,
1233 'OKC_API.G_RET_STS_UNEXP_ERROR',
1234 x_msg_count,
1235 x_msg_data,
1236 '_PVT'
1237 );
1238 WHEN OTHERS THEN
1239 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1240 (
1241 l_api_name,
1242 G_PKG_NAME,
1243 'OTHERS',
1244 x_msg_count,
1245 x_msg_data,
1246 '_PVT'
1247 );
1248 END insert_row;
1249
1250 ---------------------------------------------------------------------------
1251 -- PROCEDURE lock_row
1252 ---------------------------------------------------------------------------
1253 -------------------------------------
1254 -- lock_row for:OKS_LEVEL_ELEMENTS --
1255 -------------------------------------
1256 PROCEDURE lock_row(
1257 p_init_msg_list IN VARCHAR2,
1258 x_return_status OUT NOCOPY VARCHAR2,
1259 x_msg_count OUT NOCOPY NUMBER,
1260 x_msg_data OUT NOCOPY VARCHAR2,
1261 p_let_rec IN let_rec_type) IS
1262
1263 E_Resource_Busy EXCEPTION;
1264 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1265 CURSOR lock_csr (p_let_rec IN let_rec_type) IS
1266 SELECT OBJECT_VERSION_NUMBER
1267 FROM OKS_LEVEL_ELEMENTS
1268 WHERE ID = p_let_rec.id
1269 AND OBJECT_VERSION_NUMBER = p_let_rec.object_version_number
1270 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1271
1272 CURSOR lchk_csr (p_let_rec IN let_rec_type) IS
1273 SELECT OBJECT_VERSION_NUMBER
1274 FROM OKS_LEVEL_ELEMENTS
1275 WHERE ID = p_let_rec.id;
1276 l_api_version CONSTANT NUMBER := 1;
1277 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1278 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1279 l_object_version_number OKS_LEVEL_ELEMENTS.OBJECT_VERSION_NUMBER%TYPE;
1280 lc_object_version_number OKS_LEVEL_ELEMENTS.OBJECT_VERSION_NUMBER%TYPE;
1281 l_row_notfound BOOLEAN := FALSE;
1282 lc_row_notfound BOOLEAN := FALSE;
1283 BEGIN
1284 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1285 p_init_msg_list,
1286 '_PVT',
1287 x_return_status);
1288 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1289 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1290 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1291 RAISE OKC_API.G_EXCEPTION_ERROR;
1292 END IF;
1293 BEGIN
1294 OPEN lock_csr(p_let_rec);
1295 FETCH lock_csr INTO l_object_version_number;
1296 l_row_notfound := lock_csr%NOTFOUND;
1297 CLOSE lock_csr;
1298 EXCEPTION
1299 WHEN E_Resource_Busy THEN
1300 IF (lock_csr%ISOPEN) THEN
1301 CLOSE lock_csr;
1302 END IF;
1303 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1304 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1305 END;
1306
1307 IF ( l_row_notfound ) THEN
1308 OPEN lchk_csr(p_let_rec);
1309 FETCH lchk_csr INTO lc_object_version_number;
1310 lc_row_notfound := lchk_csr%NOTFOUND;
1311 CLOSE lchk_csr;
1312 END IF;
1313 IF (lc_row_notfound) THEN
1314 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1315 RAISE OKC_API.G_EXCEPTION_ERROR;
1316 ELSIF lc_object_version_number > p_let_rec.object_version_number THEN
1317 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1318 RAISE OKC_API.G_EXCEPTION_ERROR;
1319 ELSIF lc_object_version_number <> p_let_rec.object_version_number THEN
1320 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1321 RAISE OKC_API.G_EXCEPTION_ERROR;
1322 ELSIF lc_object_version_number = -1 THEN
1323 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1324 RAISE OKC_API.G_EXCEPTION_ERROR;
1325 END IF;
1326 x_return_status := l_return_status;
1327 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1328 EXCEPTION
1329 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1330 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1331 (
1332 l_api_name,
1333 G_PKG_NAME,
1334 'OKC_API.G_RET_STS_ERROR',
1335 x_msg_count,
1336 x_msg_data,
1337 '_PVT'
1338 );
1339 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1340 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1341 (
1342 l_api_name,
1343 G_PKG_NAME,
1344 'OKC_API.G_RET_STS_UNEXP_ERROR',
1345 x_msg_count,
1346 x_msg_data,
1347 '_PVT'
1348 );
1349 WHEN OTHERS THEN
1350 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1351 (
1352 l_api_name,
1353 G_PKG_NAME,
1354 'OTHERS',
1355 x_msg_count,
1356 x_msg_data,
1357 '_PVT'
1358 );
1359 END lock_row;
1360 ----------------------------------------
1361 -- lock_row for: OKS_LEVEL_ELEMENTS_V --
1362 ----------------------------------------
1363 PROCEDURE lock_row(
1364 p_api_version IN NUMBER,
1365 p_init_msg_list IN VARCHAR2,
1366 x_return_status OUT NOCOPY VARCHAR2,
1367 x_msg_count OUT NOCOPY NUMBER,
1368 x_msg_data OUT NOCOPY VARCHAR2,
1369 p_letv_rec IN letv_rec_type) IS
1370
1371 l_api_version CONSTANT NUMBER := 1;
1372 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1373 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1374 l_let_rec let_rec_type;
1375 BEGIN
1376 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1377 G_PKG_NAME,
1378 p_init_msg_list,
1379 l_api_version,
1380 p_api_version,
1381 '_PVT',
1382 x_return_status);
1383 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1384 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1385 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1386 RAISE OKC_API.G_EXCEPTION_ERROR;
1387 END IF;
1388 -----------------------------------------
1389 -- Move VIEW record to "Child" records --
1390 -----------------------------------------
1391 migrate(p_letv_rec, l_let_rec);
1392 ---------------------------------------------
1393 -- Call the LOCK_ROW for each child record --
1394 ---------------------------------------------
1395 lock_row(
1396 p_init_msg_list,
1397 l_return_status,
1398 x_msg_count,
1399 x_msg_data,
1400 l_let_rec
1401 );
1402 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1403 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1404 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1405 RAISE OKC_API.G_EXCEPTION_ERROR;
1406 END IF;
1407 x_return_status := l_return_status;
1408 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1409 EXCEPTION
1410 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1411 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1412 (
1413 l_api_name,
1414 G_PKG_NAME,
1415 'OKC_API.G_RET_STS_ERROR',
1416 x_msg_count,
1417 x_msg_data,
1418 '_PVT'
1419 );
1420 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1421 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1422 (
1423 l_api_name,
1424 G_PKG_NAME,
1425 'OKC_API.G_RET_STS_UNEXP_ERROR',
1426 x_msg_count,
1427 x_msg_data,
1428 '_PVT'
1429 );
1430 WHEN OTHERS THEN
1431 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1432 (
1433 l_api_name,
1434 G_PKG_NAME,
1435 'OTHERS',
1436 x_msg_count,
1437 x_msg_data,
1438 '_PVT'
1439 );
1440 END lock_row;
1441 --------------------------------------
1442 -- PL/SQL TBL lock_row for:LETV_TBL --
1443 --------------------------------------
1444 PROCEDURE lock_row(
1445 p_api_version IN NUMBER,
1446 p_init_msg_list IN VARCHAR2,
1447 x_return_status OUT NOCOPY VARCHAR2,
1448 x_msg_count OUT NOCOPY NUMBER,
1449 x_msg_data OUT NOCOPY VARCHAR2,
1450 p_letv_tbl IN letv_tbl_type,
1451 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1452
1453 l_api_version CONSTANT NUMBER := 1;
1454 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1455 i NUMBER := 0;
1456 BEGIN
1457 OKC_API.init_msg_list(p_init_msg_list);
1458 -- Make sure PL/SQL table has recrods in it before passing
1459 IF (p_letv_tbl.COUNT > 0) THEN
1460 i := p_letv_tbl.FIRST;
1461 LOOP
1462 DECLARE
1463 l_error_rec OKC_API.ERROR_REC_TYPE;
1464 BEGIN
1465 l_error_rec.api_name := l_api_name;
1466 l_error_rec.api_package := G_PKG_NAME;
1467 l_error_rec.idx := i;
1468 lock_row(
1469 p_api_version => p_api_version,
1470 p_init_msg_list => OKC_API.G_FALSE,
1471 x_return_status => l_error_rec.error_type,
1472 x_msg_count => l_error_rec.msg_count,
1473 x_msg_data => l_error_rec.msg_data,
1474 p_letv_rec => p_letv_tbl(i));
1475 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1476 l_error_rec.sqlcode := SQLCODE;
1477 load_error_tbl(l_error_rec, px_error_tbl);
1478 ELSE
1479 x_msg_count := l_error_rec.msg_count;
1480 x_msg_data := l_error_rec.msg_data;
1481 END IF;
1482 EXCEPTION
1483 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1484 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1485 l_error_rec.sqlcode := SQLCODE;
1486 load_error_tbl(l_error_rec, px_error_tbl);
1487 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1488 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1489 l_error_rec.sqlcode := SQLCODE;
1490 load_error_tbl(l_error_rec, px_error_tbl);
1491 WHEN OTHERS THEN
1492 l_error_rec.error_type := 'OTHERS';
1493 l_error_rec.sqlcode := SQLCODE;
1494 load_error_tbl(l_error_rec, px_error_tbl);
1495 END;
1496 EXIT WHEN (i = p_letv_tbl.LAST);
1497 i := p_letv_tbl.NEXT(i);
1498 END LOOP;
1499 END IF;
1500 -- Loop through the error_tbl to find the error with the highest severity
1501 -- and return it.
1502 x_return_status := find_highest_exception(px_error_tbl);
1503 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1504 EXCEPTION
1505 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1506 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1507 (
1508 l_api_name,
1509 G_PKG_NAME,
1510 'OKC_API.G_RET_STS_ERROR',
1511 x_msg_count,
1512 x_msg_data,
1513 '_PVT'
1514 );
1515 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1516 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1517 (
1518 l_api_name,
1519 G_PKG_NAME,
1520 'OKC_API.G_RET_STS_UNEXP_ERROR',
1521 x_msg_count,
1522 x_msg_data,
1523 '_PVT'
1524 );
1525 WHEN OTHERS THEN
1526 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1527 (
1528 l_api_name,
1529 G_PKG_NAME,
1530 'OTHERS',
1531 x_msg_count,
1532 x_msg_data,
1533 '_PVT'
1534 );
1535 END lock_row;
1536 --------------------------------------
1537 -- PL/SQL TBL lock_row for:LETV_TBL --
1538 --------------------------------------
1539 PROCEDURE lock_row(
1540 p_api_version IN NUMBER,
1541 p_init_msg_list IN VARCHAR2,
1542 x_return_status OUT NOCOPY VARCHAR2,
1543 x_msg_count OUT NOCOPY NUMBER,
1544 x_msg_data OUT NOCOPY VARCHAR2,
1545 p_letv_tbl IN letv_tbl_type) IS
1546
1547 l_api_version CONSTANT NUMBER := 1;
1548 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1549 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1550 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1551 BEGIN
1552 OKC_API.init_msg_list(p_init_msg_list);
1553 -- Make sure PL/SQL table has recrods in it before passing
1554 IF (p_letv_tbl.COUNT > 0) THEN
1555 lock_row(
1556 p_api_version => p_api_version,
1557 p_init_msg_list => OKC_API.G_FALSE,
1558 x_return_status => x_return_status,
1559 x_msg_count => x_msg_count,
1560 x_msg_data => x_msg_data,
1561 p_letv_tbl => p_letv_tbl,
1562 px_error_tbl => l_error_tbl);
1563 END IF;
1564 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1565 EXCEPTION
1566 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1567 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1568 (
1569 l_api_name,
1570 G_PKG_NAME,
1571 'OKC_API.G_RET_STS_ERROR',
1572 x_msg_count,
1573 x_msg_data,
1574 '_PVT'
1575 );
1576 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1577 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1578 (
1579 l_api_name,
1580 G_PKG_NAME,
1581 'OKC_API.G_RET_STS_UNEXP_ERROR',
1582 x_msg_count,
1583 x_msg_data,
1584 '_PVT'
1585 );
1586 WHEN OTHERS THEN
1587 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1588 (
1589 l_api_name,
1590 G_PKG_NAME,
1591 'OTHERS',
1592 x_msg_count,
1593 x_msg_data,
1594 '_PVT'
1595 );
1596 END lock_row;
1597 ---------------------------------------------------------------------------
1598 -- PROCEDURE update_row
1599 ---------------------------------------------------------------------------
1600 ---------------------------------------
1601 -- update_row for:OKS_LEVEL_ELEMENTS --
1602 ---------------------------------------
1603 PROCEDURE update_row(
1604 p_init_msg_list IN VARCHAR2,
1605 x_return_status OUT NOCOPY VARCHAR2,
1606 x_msg_count OUT NOCOPY NUMBER,
1607 x_msg_data OUT NOCOPY VARCHAR2,
1608 p_let_rec IN let_rec_type,
1609 x_let_rec OUT NOCOPY let_rec_type) IS
1610
1611 l_api_version CONSTANT NUMBER := 1;
1612 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1613 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1614 l_let_rec let_rec_type := p_let_rec;
1615 l_def_let_rec let_rec_type;
1616 l_row_notfound BOOLEAN := TRUE;
1617 ----------------------------------
1618 -- FUNCTION populate_new_record --
1619 ----------------------------------
1620 FUNCTION populate_new_record (
1621 p_let_rec IN let_rec_type,
1622 x_let_rec OUT NOCOPY let_rec_type
1623 ) RETURN VARCHAR2 IS
1624 l_let_rec let_rec_type;
1625 l_row_notfound BOOLEAN := TRUE;
1626 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1627 BEGIN
1628 x_let_rec := p_let_rec;
1629 -- Get current database values
1630 l_let_rec := get_rec(p_let_rec, l_return_status);
1631 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1632 IF (x_let_rec.id = OKC_API.G_MISS_NUM)
1633 THEN
1634 x_let_rec.id := l_let_rec.id;
1635 END IF;
1636 IF (x_let_rec.cle_id = OKC_API.G_MISS_NUM)
1637 THEN
1638 x_let_rec.cle_id := l_let_rec.cle_id;
1639 END IF;
1640 IF (x_let_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1641 THEN
1642 x_let_rec.dnz_chr_id := l_let_rec.dnz_chr_id;
1643 END IF;
1644 IF (x_let_rec.parent_cle_id = OKC_API.G_MISS_NUM)
1645 THEN
1646 x_let_rec.parent_cle_id := l_let_rec.parent_cle_id;
1647 END IF;
1648 IF (x_let_rec.sequence_number = OKC_API.G_MISS_CHAR)
1649 THEN
1650 x_let_rec.sequence_number := l_let_rec.sequence_number;
1651 END IF;
1652 IF (x_let_rec.date_start = OKC_API.G_MISS_DATE)
1653 THEN
1654 x_let_rec.date_start := l_let_rec.date_start;
1655 END IF;
1656 IF (x_let_rec.date_end = OKC_API.G_MISS_DATE)
1657 THEN
1658 x_let_rec.date_end := l_let_rec.date_end;
1659 END IF;
1660 IF (x_let_rec.amount = OKC_API.G_MISS_NUM)
1661 THEN
1662 x_let_rec.amount := l_let_rec.amount;
1663 END IF;
1664 IF (x_let_rec.date_receivable_gl = OKC_API.G_MISS_DATE)
1665 THEN
1666 x_let_rec.date_receivable_gl := l_let_rec.date_receivable_gl;
1667 END IF;
1668 IF (x_let_rec.date_revenue_rule_start = OKC_API.G_MISS_DATE)
1669 THEN
1670 x_let_rec.date_revenue_rule_start := l_let_rec.date_revenue_rule_start;
1671 END IF;
1672 IF (x_let_rec.date_transaction = OKC_API.G_MISS_DATE)
1673 THEN
1674 x_let_rec.date_transaction := l_let_rec.date_transaction;
1675 END IF;
1676 IF (x_let_rec.date_due = OKC_API.G_MISS_DATE)
1677 THEN
1678 x_let_rec.date_due := l_let_rec.date_due;
1679 END IF;
1680 IF (x_let_rec.date_print = OKC_API.G_MISS_DATE)
1681 THEN
1682 x_let_rec.date_print := l_let_rec.date_print;
1683 END IF;
1684 IF (x_let_rec.date_to_interface = OKC_API.G_MISS_DATE)
1685 THEN
1686 x_let_rec.date_to_interface := l_let_rec.date_to_interface;
1687 END IF;
1688 IF (x_let_rec.date_completed = OKC_API.G_MISS_DATE)
1689 THEN
1690 x_let_rec.date_completed := l_let_rec.date_completed;
1691 END IF;
1692 IF (x_let_rec.object_version_number = OKC_API.G_MISS_NUM)
1693 THEN
1694 x_let_rec.object_version_number := l_let_rec.object_version_number;
1695 END IF;
1696 IF (x_let_rec.rul_id = OKC_API.G_MISS_NUM)
1697 THEN
1698 x_let_rec.rul_id := l_let_rec.rul_id;
1699 END IF;
1700 IF (x_let_rec.created_by = OKC_API.G_MISS_NUM)
1701 THEN
1702 x_let_rec.created_by := l_let_rec.created_by;
1703 END IF;
1704 IF (x_let_rec.creation_date = OKC_API.G_MISS_DATE)
1705 THEN
1706 x_let_rec.creation_date := l_let_rec.creation_date;
1707 END IF;
1708 IF (x_let_rec.last_updated_by = OKC_API.G_MISS_NUM)
1709 THEN
1710 x_let_rec.last_updated_by := l_let_rec.last_updated_by;
1711 END IF;
1712 IF (x_let_rec.last_update_date = OKC_API.G_MISS_DATE)
1713 THEN
1714 x_let_rec.last_update_date := l_let_rec.last_update_date;
1715 END IF;
1716 END IF;
1717 RETURN(l_return_status);
1718 END populate_new_record;
1719 -------------------------------------------
1720 -- Set_Attributes for:OKS_LEVEL_ELEMENTS --
1721 -------------------------------------------
1722 FUNCTION Set_Attributes (
1723 p_let_rec IN let_rec_type,
1724 x_let_rec OUT NOCOPY let_rec_type
1725 ) RETURN VARCHAR2 IS
1726 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1727 BEGIN
1728 x_let_rec := p_let_rec;
1729 x_let_rec.OBJECT_VERSION_NUMBER := p_let_rec.OBJECT_VERSION_NUMBER + 1;
1730 RETURN(l_return_status);
1731 END Set_Attributes;
1732 BEGIN
1733 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1734 p_init_msg_list,
1735 '_PVT',
1736 x_return_status);
1737 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1738 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1739 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1740 RAISE OKC_API.G_EXCEPTION_ERROR;
1741 END IF;
1742 --- Setting item attributes
1743 l_return_status := Set_Attributes(
1744 p_let_rec, -- IN
1745 l_let_rec); -- OUT
1746 --- If any errors happen abort API
1747 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1748 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1749 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1750 RAISE OKC_API.G_EXCEPTION_ERROR;
1751 END IF;
1752 l_return_status := populate_new_record(l_let_rec, l_def_let_rec);
1753 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1754 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1755 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1756 RAISE OKC_API.G_EXCEPTION_ERROR;
1757 END IF;
1758 UPDATE OKS_LEVEL_ELEMENTS
1759 SET CLE_ID = l_def_let_rec.cle_id,
1760 DNZ_CHR_ID = l_def_let_rec.dnz_chr_id,
1761 PARENT_CLE_ID = l_def_let_rec.parent_cle_id,
1762 SEQUENCE_NUMBER = l_def_let_rec.sequence_number,
1763 DATE_START = l_def_let_rec.date_start,
1764 DATE_END = l_def_let_rec.date_end,
1765 AMOUNT = l_def_let_rec.amount,
1766 DATE_RECEIVABLE_GL = l_def_let_rec.date_receivable_gl,
1767 DATE_REVENUE_RULE_START = l_def_let_rec.date_revenue_rule_start,
1768 DATE_TRANSACTION = l_def_let_rec.date_transaction,
1769 DATE_DUE = l_def_let_rec.date_due,
1770 DATE_PRINT = l_def_let_rec.date_print,
1771 DATE_TO_INTERFACE = l_def_let_rec.date_to_interface,
1772 DATE_COMPLETED = l_def_let_rec.date_completed,
1773 OBJECT_VERSION_NUMBER = l_def_let_rec.object_version_number,
1774 RUL_ID = l_def_let_rec.rul_id,
1775 CREATED_BY = l_def_let_rec.created_by,
1776 CREATION_DATE = l_def_let_rec.creation_date,
1777 LAST_UPDATED_BY = l_def_let_rec.last_updated_by,
1778 LAST_UPDATE_DATE = l_def_let_rec.last_update_date
1779 WHERE ID = l_def_let_rec.id;
1780
1781 x_let_rec := l_let_rec;
1782 x_return_status := l_return_status;
1783 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1784 EXCEPTION
1785 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1786 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1787 (
1788 l_api_name,
1789 G_PKG_NAME,
1790 'OKC_API.G_RET_STS_ERROR',
1791 x_msg_count,
1792 x_msg_data,
1793 '_PVT'
1794 );
1795 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1796 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1797 (
1798 l_api_name,
1799 G_PKG_NAME,
1800 'OKC_API.G_RET_STS_UNEXP_ERROR',
1801 x_msg_count,
1802 x_msg_data,
1803 '_PVT'
1804 );
1805 WHEN OTHERS THEN
1806 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1807 (
1808 l_api_name,
1809 G_PKG_NAME,
1810 'OTHERS',
1811 x_msg_count,
1812 x_msg_data,
1813 '_PVT'
1814 );
1815 END update_row;
1816 -----------------------------------------
1817 -- update_row for:OKS_LEVEL_ELEMENTS_V --
1818 -----------------------------------------
1819 PROCEDURE update_row(
1820 p_api_version IN NUMBER,
1821 p_init_msg_list IN VARCHAR2,
1822 x_return_status OUT NOCOPY VARCHAR2,
1823 x_msg_count OUT NOCOPY NUMBER,
1824 x_msg_data OUT NOCOPY VARCHAR2,
1825 p_letv_rec IN letv_rec_type,
1826 x_letv_rec OUT NOCOPY letv_rec_type) IS
1827
1828 l_api_version CONSTANT NUMBER := 1;
1829 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1830 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1831 l_letv_rec letv_rec_type := p_letv_rec;
1832 l_def_letv_rec letv_rec_type;
1833 l_db_letv_rec letv_rec_type;
1834 l_let_rec let_rec_type;
1835 lx_let_rec let_rec_type;
1836 -------------------------------
1837 -- FUNCTION fill_who_columns --
1838 -------------------------------
1839 FUNCTION fill_who_columns (
1840 p_letv_rec IN letv_rec_type
1841 ) RETURN letv_rec_type IS
1842 l_letv_rec letv_rec_type := p_letv_rec;
1843 BEGIN
1844 l_letv_rec.LAST_UPDATE_DATE := SYSDATE;
1845 l_letv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1846 -- l_letv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1847 RETURN(l_letv_rec);
1848 END fill_who_columns;
1849 ----------------------------------
1850 -- FUNCTION populate_new_record --
1851 ----------------------------------
1852 FUNCTION populate_new_record (
1853 p_letv_rec IN letv_rec_type,
1854 x_letv_rec OUT NOCOPY letv_rec_type
1855 ) RETURN VARCHAR2 IS
1856 l_row_notfound BOOLEAN := TRUE;
1857 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1858 BEGIN
1859 x_letv_rec := p_letv_rec;
1860 -- Get current database values
1861 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
1862 -- so it may be verified through LOCK_ROW.
1863 l_db_letv_rec := get_rec(p_letv_rec, l_return_status);
1864 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1865 IF (x_letv_rec.id = OKC_API.G_MISS_NUM)
1866 THEN
1867 x_letv_rec.id := l_db_letv_rec.id;
1868 END IF;
1869 IF (x_letv_rec.cle_id = OKC_API.G_MISS_NUM)
1870 THEN
1871 x_letv_rec.cle_id := l_db_letv_rec.cle_id;
1872 END IF;
1873 IF (x_letv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1874 THEN
1875 x_letv_rec.dnz_chr_id := l_db_letv_rec.dnz_chr_id;
1876 END IF;
1877 IF (x_letv_rec.parent_cle_id = OKC_API.G_MISS_NUM)
1878 THEN
1879 x_letv_rec.parent_cle_id := l_db_letv_rec.parent_cle_id;
1880 END IF;
1881 IF (x_letv_rec.sequence_number = OKC_API.G_MISS_CHAR)
1882 THEN
1883 x_letv_rec.sequence_number := l_db_letv_rec.sequence_number;
1884 END IF;
1885 IF (x_letv_rec.date_start = OKC_API.G_MISS_DATE)
1886 THEN
1887 x_letv_rec.date_start := l_db_letv_rec.date_start;
1888 END IF;
1889 IF (x_letv_rec.date_end = OKC_API.G_MISS_DATE)
1890 THEN
1891 x_letv_rec.date_end := l_db_letv_rec.date_end;
1892 END IF;
1893 IF (x_letv_rec.amount = OKC_API.G_MISS_NUM)
1894 THEN
1895 x_letv_rec.amount := l_db_letv_rec.amount;
1896 END IF;
1897 IF (x_letv_rec.date_revenue_rule_start = OKC_API.G_MISS_DATE)
1898 THEN
1899 x_letv_rec.date_revenue_rule_start := l_db_letv_rec.date_revenue_rule_start;
1900 END IF;
1901 IF (x_letv_rec.date_receivable_gl = OKC_API.G_MISS_DATE)
1902 THEN
1903 x_letv_rec.date_receivable_gl := l_db_letv_rec.date_receivable_gl;
1904 END IF;
1905 IF (x_letv_rec.date_transaction = OKC_API.G_MISS_DATE)
1906 THEN
1907 x_letv_rec.date_transaction := l_db_letv_rec.date_transaction;
1908 END IF;
1909 IF (x_letv_rec.date_due = OKC_API.G_MISS_DATE)
1910 THEN
1911 x_letv_rec.date_due := l_db_letv_rec.date_due;
1912 END IF;
1913 IF (x_letv_rec.date_print = OKC_API.G_MISS_DATE)
1914 THEN
1915 x_letv_rec.date_print := l_db_letv_rec.date_print;
1916 END IF;
1917 IF (x_letv_rec.date_to_interface = OKC_API.G_MISS_DATE)
1918 THEN
1919 x_letv_rec.date_to_interface := l_db_letv_rec.date_to_interface;
1920 END IF;
1921 IF (x_letv_rec.date_completed = OKC_API.G_MISS_DATE)
1922 THEN
1923 x_letv_rec.date_completed := l_db_letv_rec.date_completed;
1924 END IF;
1925 IF (x_letv_rec.rul_id = OKC_API.G_MISS_NUM)
1926 THEN
1927 x_letv_rec.rul_id := l_db_letv_rec.rul_id;
1928 END IF;
1929 IF (x_letv_rec.created_by = OKC_API.G_MISS_NUM)
1930 THEN
1931 x_letv_rec.created_by := l_db_letv_rec.created_by;
1932 END IF;
1933 IF (x_letv_rec.creation_date = OKC_API.G_MISS_DATE)
1934 THEN
1935 x_letv_rec.creation_date := l_db_letv_rec.creation_date;
1936 END IF;
1937 IF (x_letv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1938 THEN
1939 x_letv_rec.last_updated_by := l_db_letv_rec.last_updated_by;
1940 END IF;
1941 IF (x_letv_rec.last_update_date = OKC_API.G_MISS_DATE)
1942 THEN
1943 x_letv_rec.last_update_date := l_db_letv_rec.last_update_date;
1944 END IF;
1945 END IF;
1946 RETURN(l_return_status);
1947 END populate_new_record;
1948 ---------------------------------------------
1949 -- Set_Attributes for:OKS_LEVEL_ELEMENTS_V --
1950 ---------------------------------------------
1951 FUNCTION Set_Attributes (
1952 p_letv_rec IN letv_rec_type,
1953 x_letv_rec OUT NOCOPY letv_rec_type
1954 ) RETURN VARCHAR2 IS
1955 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1956 BEGIN
1957 x_letv_rec := p_letv_rec;
1958 RETURN(l_return_status);
1959 END Set_Attributes;
1960 BEGIN
1961 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1962 G_PKG_NAME,
1963 p_init_msg_list,
1964 l_api_version,
1965 p_api_version,
1966 '_PVT',
1967 x_return_status);
1968 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1969 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1970 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1971 RAISE OKC_API.G_EXCEPTION_ERROR;
1972 END IF;
1973 --- Setting item attributes
1974 l_return_status := Set_Attributes(
1975 p_letv_rec, -- IN
1976 x_letv_rec); -- OUT
1977 --- If any errors happen abort API
1978 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1979 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1980 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1981 RAISE OKC_API.G_EXCEPTION_ERROR;
1982 END IF;
1983 l_return_status := populate_new_record(l_letv_rec, l_def_letv_rec);
1984 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1985 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1986 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1987 RAISE OKC_API.G_EXCEPTION_ERROR;
1988 END IF;
1989 l_def_letv_rec := fill_who_columns(l_def_letv_rec);
1990 --- Validate all non-missing attributes (Item Level Validation)
1991 l_return_status := Validate_Attributes(l_def_letv_rec);
1992 --- If any errors happen abort API
1993 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1994 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1995 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1996 RAISE OKC_API.G_EXCEPTION_ERROR;
1997 END IF;
1998 l_return_status := Validate_Record(l_def_letv_rec, l_db_letv_rec);
1999 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2000 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2001 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2002 RAISE OKC_API.G_EXCEPTION_ERROR;
2003 END IF;
2004
2005 -- Lock the Record
2006 lock_row(
2007 p_api_version => p_api_version,
2008 p_init_msg_list => p_init_msg_list,
2009 x_return_status => l_return_status,
2010 x_msg_count => x_msg_count,
2011 x_msg_data => x_msg_data,
2012 p_letv_rec => p_letv_rec);
2013 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2014 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2015 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2016 RAISE OKC_API.G_EXCEPTION_ERROR;
2017 END IF;
2018
2019 -----------------------------------------
2020 -- Move VIEW record to "Child" records --
2021 -----------------------------------------
2022 migrate(l_def_letv_rec, l_let_rec);
2023 -----------------------------------------------
2024 -- Call the UPDATE_ROW for each child record --
2025 -----------------------------------------------
2026 update_row(
2027 p_init_msg_list,
2028 l_return_status,
2029 x_msg_count,
2030 x_msg_data,
2031 l_let_rec,
2032 lx_let_rec
2033 );
2034 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2035 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2036 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2037 RAISE OKC_API.G_EXCEPTION_ERROR;
2038 END IF;
2039 migrate(lx_let_rec, l_def_letv_rec);
2040 x_letv_rec := l_def_letv_rec;
2041 x_return_status := l_return_status;
2042 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2043 EXCEPTION
2044 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2045 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2046 (
2047 l_api_name,
2048 G_PKG_NAME,
2049 'OKC_API.G_RET_STS_ERROR',
2050 x_msg_count,
2051 x_msg_data,
2052 '_PVT'
2053 );
2054 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2055 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2056 (
2057 l_api_name,
2058 G_PKG_NAME,
2059 'OKC_API.G_RET_STS_UNEXP_ERROR',
2060 x_msg_count,
2061 x_msg_data,
2062 '_PVT'
2063 );
2064 WHEN OTHERS THEN
2065 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2066 (
2067 l_api_name,
2068 G_PKG_NAME,
2069 'OTHERS',
2070 x_msg_count,
2071 x_msg_data,
2072 '_PVT'
2073 );
2074 END update_row;
2075 ----------------------------------------
2076 -- PL/SQL TBL update_row for:letv_tbl --
2077 ----------------------------------------
2078 PROCEDURE update_row(
2079 p_api_version IN NUMBER,
2080 p_init_msg_list IN VARCHAR2,
2081 x_return_status OUT NOCOPY VARCHAR2,
2082 x_msg_count OUT NOCOPY NUMBER,
2083 x_msg_data OUT NOCOPY VARCHAR2,
2084 p_letv_tbl IN letv_tbl_type,
2085 x_letv_tbl OUT NOCOPY letv_tbl_type,
2086 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2087
2088 l_api_version CONSTANT NUMBER := 1;
2089 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2090 i NUMBER := 0;
2091 BEGIN
2092 OKC_API.init_msg_list(p_init_msg_list);
2093 -- Make sure PL/SQL table has records in it before passing
2094 IF (p_letv_tbl.COUNT > 0) THEN
2095 i := p_letv_tbl.FIRST;
2096 LOOP
2097 DECLARE
2098 l_error_rec OKC_API.ERROR_REC_TYPE;
2099 BEGIN
2100 l_error_rec.api_name := l_api_name;
2101 l_error_rec.api_package := G_PKG_NAME;
2102 l_error_rec.idx := i;
2103 update_row (
2104 p_api_version => p_api_version,
2105 p_init_msg_list => OKC_API.G_FALSE,
2106 x_return_status => l_error_rec.error_type,
2107 x_msg_count => l_error_rec.msg_count,
2108 x_msg_data => l_error_rec.msg_data,
2109 p_letv_rec => p_letv_tbl(i),
2110 x_letv_rec => x_letv_tbl(i));
2111 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2112 l_error_rec.sqlcode := SQLCODE;
2113 load_error_tbl(l_error_rec, px_error_tbl);
2114 ELSE
2115 x_msg_count := l_error_rec.msg_count;
2116 x_msg_data := l_error_rec.msg_data;
2117 END IF;
2118 EXCEPTION
2119 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2120 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2121 l_error_rec.sqlcode := SQLCODE;
2122 load_error_tbl(l_error_rec, px_error_tbl);
2123 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2124 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2125 l_error_rec.sqlcode := SQLCODE;
2126 load_error_tbl(l_error_rec, px_error_tbl);
2127 WHEN OTHERS THEN
2128 l_error_rec.error_type := 'OTHERS';
2129 l_error_rec.sqlcode := SQLCODE;
2130 load_error_tbl(l_error_rec, px_error_tbl);
2131 END;
2132 EXIT WHEN (i = p_letv_tbl.LAST);
2133 i := p_letv_tbl.NEXT(i);
2134 END LOOP;
2135 END IF;
2136 -- Loop through the error_tbl to find the error with the highest severity
2137 -- and return it.
2138 x_return_status := find_highest_exception(px_error_tbl);
2139 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2140 EXCEPTION
2141 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2142 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2143 (
2144 l_api_name,
2145 G_PKG_NAME,
2146 'OKC_API.G_RET_STS_ERROR',
2147 x_msg_count,
2148 x_msg_data,
2149 '_PVT'
2150 );
2151 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2152 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2153 (
2154 l_api_name,
2155 G_PKG_NAME,
2156 'OKC_API.G_RET_STS_UNEXP_ERROR',
2157 x_msg_count,
2158 x_msg_data,
2159 '_PVT'
2160 );
2161 WHEN OTHERS THEN
2162 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2163 (
2164 l_api_name,
2165 G_PKG_NAME,
2166 'OTHERS',
2167 x_msg_count,
2168 x_msg_data,
2169 '_PVT'
2170 );
2171 END update_row;
2172
2173 ----------------------------------------
2174 -- PL/SQL TBL update_row for:LETV_TBL --
2175 ----------------------------------------
2176 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
2177 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
2178 PROCEDURE update_row(
2179 p_api_version IN NUMBER,
2180 p_init_msg_list IN VARCHAR2,
2181 x_return_status OUT NOCOPY VARCHAR2,
2182 x_msg_count OUT NOCOPY NUMBER,
2183 x_msg_data OUT NOCOPY VARCHAR2,
2184 p_letv_tbl IN letv_tbl_type,
2185 x_letv_tbl OUT NOCOPY letv_tbl_type) IS
2186
2187 l_api_version CONSTANT NUMBER := 1;
2188 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2189 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2190 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2191 BEGIN
2192 OKC_API.init_msg_list(p_init_msg_list);
2193 -- Make sure PL/SQL table has records in it before passing
2194 IF (p_letv_tbl.COUNT > 0) THEN
2195 update_row (
2196 p_api_version => p_api_version,
2197 p_init_msg_list => OKC_API.G_FALSE,
2198 x_return_status => x_return_status,
2199 x_msg_count => x_msg_count,
2200 x_msg_data => x_msg_data,
2201 p_letv_tbl => p_letv_tbl,
2202 x_letv_tbl => x_letv_tbl,
2203 px_error_tbl => l_error_tbl);
2204 END IF;
2205 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2206 EXCEPTION
2207 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2208 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2209 (
2210 l_api_name,
2211 G_PKG_NAME,
2212 'OKC_API.G_RET_STS_ERROR',
2213 x_msg_count,
2214 x_msg_data,
2215 '_PVT'
2216 );
2217 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2218 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2219 (
2220 l_api_name,
2221 G_PKG_NAME,
2222 'OKC_API.G_RET_STS_UNEXP_ERROR',
2223 x_msg_count,
2224 x_msg_data,
2225 '_PVT'
2226 );
2227 WHEN OTHERS THEN
2228 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2229 (
2230 l_api_name,
2231 G_PKG_NAME,
2232 'OTHERS',
2233 x_msg_count,
2234 x_msg_data,
2235 '_PVT'
2236 );
2237 END update_row;
2238
2239 ---------------------------------------------------------------------------
2240 -- PROCEDURE delete_row
2241 ---------------------------------------------------------------------------
2242 ---------------------------------------
2243 -- delete_row for:OKS_LEVEL_ELEMENTS --
2244 ---------------------------------------
2245 PROCEDURE delete_row(
2246 p_init_msg_list IN VARCHAR2,
2247 x_return_status OUT NOCOPY VARCHAR2,
2248 x_msg_count OUT NOCOPY NUMBER,
2249 x_msg_data OUT NOCOPY VARCHAR2,
2250 p_let_rec IN let_rec_type) IS
2251
2252 l_api_version CONSTANT NUMBER := 1;
2253 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2254 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2255 l_let_rec let_rec_type := p_let_rec;
2256 l_row_notfound BOOLEAN := TRUE;
2257 BEGIN
2258 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2259 p_init_msg_list,
2260 '_PVT',
2261 x_return_status);
2262 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2263 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2264 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2265 RAISE OKC_API.G_EXCEPTION_ERROR;
2266 END IF;
2267
2268 DELETE FROM OKS_LEVEL_ELEMENTS
2269 WHERE ID = p_let_rec.id;
2270
2271 x_return_status := l_return_status;
2272 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2273 EXCEPTION
2274 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2275 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2276 (
2277 l_api_name,
2278 G_PKG_NAME,
2279 'OKC_API.G_RET_STS_ERROR',
2280 x_msg_count,
2281 x_msg_data,
2282 '_PVT'
2283 );
2284 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2285 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2286 (
2287 l_api_name,
2288 G_PKG_NAME,
2289 'OKC_API.G_RET_STS_UNEXP_ERROR',
2290 x_msg_count,
2291 x_msg_data,
2292 '_PVT'
2293 );
2294 WHEN OTHERS THEN
2295 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2296 (
2297 l_api_name,
2298 G_PKG_NAME,
2299 'OTHERS',
2300 x_msg_count,
2301 x_msg_data,
2302 '_PVT'
2303 );
2304 END delete_row;
2305 -----------------------------------------
2306 -- delete_row for:OKS_LEVEL_ELEMENTS_V --
2307 -----------------------------------------
2308 PROCEDURE delete_row(
2309 p_api_version IN NUMBER,
2310 p_init_msg_list IN VARCHAR2,
2311 x_return_status OUT NOCOPY VARCHAR2,
2312 x_msg_count OUT NOCOPY NUMBER,
2313 x_msg_data OUT NOCOPY VARCHAR2,
2314 p_letv_rec IN letv_rec_type) IS
2315
2316 l_api_version CONSTANT NUMBER := 1;
2317 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2318 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2319 l_letv_rec letv_rec_type := p_letv_rec;
2320 l_let_rec let_rec_type;
2321 BEGIN
2322 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2323 G_PKG_NAME,
2324 p_init_msg_list,
2325 l_api_version,
2326 p_api_version,
2327 '_PVT',
2328 x_return_status);
2329 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2330 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2331 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2332 RAISE OKC_API.G_EXCEPTION_ERROR;
2333 END IF;
2334 -----------------------------------------
2335 -- Move VIEW record to "Child" records --
2336 -----------------------------------------
2337 migrate(l_letv_rec, l_let_rec);
2338 -----------------------------------------------
2339 -- Call the DELETE_ROW for each child record --
2340 -----------------------------------------------
2341 delete_row(
2342 p_init_msg_list,
2343 l_return_status,
2344 x_msg_count,
2345 x_msg_data,
2346 l_let_rec
2347 );
2348 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2349 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2350 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2351 RAISE OKC_API.G_EXCEPTION_ERROR;
2352 END IF;
2353 x_return_status := l_return_status;
2354 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2355 EXCEPTION
2356 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2357 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2358 (
2359 l_api_name,
2360 G_PKG_NAME,
2361 'OKC_API.G_RET_STS_ERROR',
2362 x_msg_count,
2363 x_msg_data,
2364 '_PVT'
2365 );
2366 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2367 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2368 (
2369 l_api_name,
2370 G_PKG_NAME,
2371 'OKC_API.G_RET_STS_UNEXP_ERROR',
2372 x_msg_count,
2373 x_msg_data,
2374 '_PVT'
2375 );
2376 WHEN OTHERS THEN
2377 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2378 (
2379 l_api_name,
2380 G_PKG_NAME,
2381 'OTHERS',
2382 x_msg_count,
2383 x_msg_data,
2384 '_PVT'
2385 );
2386 END delete_row;
2387 ----------------------------------------------------
2388 -- PL/SQL TBL delete_row for:OKS_LEVEL_ELEMENTS_V --
2389 ----------------------------------------------------
2390 PROCEDURE delete_row(
2391 p_api_version IN NUMBER,
2392 p_init_msg_list IN VARCHAR2,
2393 x_return_status OUT NOCOPY VARCHAR2,
2394 x_msg_count OUT NOCOPY NUMBER,
2395 x_msg_data OUT NOCOPY VARCHAR2,
2396 p_letv_tbl IN letv_tbl_type,
2397 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2398
2399 l_api_version CONSTANT NUMBER := 1;
2400 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2401 i NUMBER := 0;
2402 BEGIN
2403 OKC_API.init_msg_list(p_init_msg_list);
2404 -- Make sure PL/SQL table has records in it before passing
2405 IF (p_letv_tbl.COUNT > 0) THEN
2406 i := p_letv_tbl.FIRST;
2407 LOOP
2408 DECLARE
2409 l_error_rec OKC_API.ERROR_REC_TYPE;
2410 BEGIN
2411 l_error_rec.api_name := l_api_name;
2412 l_error_rec.api_package := G_PKG_NAME;
2413 l_error_rec.idx := i;
2414 delete_row (
2415 p_api_version => p_api_version,
2416 p_init_msg_list => OKC_API.G_FALSE,
2417 x_return_status => l_error_rec.error_type,
2418 x_msg_count => l_error_rec.msg_count,
2419 x_msg_data => l_error_rec.msg_data,
2420 p_letv_rec => p_letv_tbl(i));
2421 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2422 l_error_rec.sqlcode := SQLCODE;
2423 load_error_tbl(l_error_rec, px_error_tbl);
2424 ELSE
2425 x_msg_count := l_error_rec.msg_count;
2426 x_msg_data := l_error_rec.msg_data;
2427 END IF;
2428 EXCEPTION
2429 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2430 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2431 l_error_rec.sqlcode := SQLCODE;
2432 load_error_tbl(l_error_rec, px_error_tbl);
2433 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2434 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2435 l_error_rec.sqlcode := SQLCODE;
2436 load_error_tbl(l_error_rec, px_error_tbl);
2437 WHEN OTHERS THEN
2438 l_error_rec.error_type := 'OTHERS';
2439 l_error_rec.sqlcode := SQLCODE;
2440 load_error_tbl(l_error_rec, px_error_tbl);
2441 END;
2442 EXIT WHEN (i = p_letv_tbl.LAST);
2443 i := p_letv_tbl.NEXT(i);
2444 END LOOP;
2445 END IF;
2446 -- Loop through the error_tbl to find the error with the highest severity
2447 -- and return it.
2448 x_return_status := find_highest_exception(px_error_tbl);
2449 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2450 EXCEPTION
2451 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2452 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2453 (
2454 l_api_name,
2455 G_PKG_NAME,
2456 'OKC_API.G_RET_STS_ERROR',
2457 x_msg_count,
2458 x_msg_data,
2459 '_PVT'
2460 );
2461 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2462 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2463 (
2464 l_api_name,
2465 G_PKG_NAME,
2466 'OKC_API.G_RET_STS_UNEXP_ERROR',
2467 x_msg_count,
2468 x_msg_data,
2469 '_PVT'
2470 );
2471 WHEN OTHERS THEN
2472 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2473 (
2474 l_api_name,
2475 G_PKG_NAME,
2476 'OTHERS',
2477 x_msg_count,
2478 x_msg_data,
2479 '_PVT'
2480 );
2481 END delete_row;
2482
2483 ----------------------------------------------------
2484 -- PL/SQL TBL delete_row for:OKS_LEVEL_ELEMENTS_V --
2485 ----------------------------------------------------
2486 PROCEDURE delete_row(
2487 p_api_version IN NUMBER,
2488 p_init_msg_list IN VARCHAR2,
2489 x_return_status OUT NOCOPY VARCHAR2,
2490 x_msg_count OUT NOCOPY NUMBER,
2491 x_msg_data OUT NOCOPY VARCHAR2,
2492 p_letv_tbl IN letv_tbl_type) IS
2493
2494 l_api_version CONSTANT NUMBER := 1;
2495 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2496 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2497 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2498 BEGIN
2499 OKC_API.init_msg_list(p_init_msg_list);
2500 -- Make sure PL/SQL table has records in it before passing
2501 IF (p_letv_tbl.COUNT > 0) THEN
2502 delete_row (
2503 p_api_version => p_api_version,
2504 p_init_msg_list => OKC_API.G_FALSE,
2505 x_return_status => x_return_status,
2506 x_msg_count => x_msg_count,
2507 x_msg_data => x_msg_data,
2508 p_letv_tbl => p_letv_tbl,
2509 px_error_tbl => l_error_tbl);
2510 END IF;
2511 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2512 EXCEPTION
2513 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2514 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2515 (
2516 l_api_name,
2517 G_PKG_NAME,
2518 'OKC_API.G_RET_STS_ERROR',
2519 x_msg_count,
2520 x_msg_data,
2521 '_PVT'
2522 );
2523 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2524 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2525 (
2526 l_api_name,
2527 G_PKG_NAME,
2528 'OKC_API.G_RET_STS_UNEXP_ERROR',
2529 x_msg_count,
2530 x_msg_data,
2531 '_PVT'
2532 );
2533 WHEN OTHERS THEN
2534 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2535 (
2536 l_api_name,
2537 G_PKG_NAME,
2538 'OTHERS',
2539 x_msg_count,
2540 x_msg_data,
2541 '_PVT'
2542 );
2543 END delete_row;
2544
2545 END OKS_BILL_LEVEL_ELEMENTS_PVT ;