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