[Home] [Help]
PACKAGE BODY: APPS.OKL_CRQ_PVT
Source
1 PACKAGE BODY OKL_CRQ_PVT AS
2 /* $Header: OKLSCRQB.pls 120.2 2006/07/14 05:05:58 pagarg 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: OKL_CREDIT_REQUESTS_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_crqv_rec IN crqv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN crqv_rec_type IS
100 CURSOR okl_crqv_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 QUOTE_ID,
104 CREDIT_REQ_NUMBER,
105 CREDIT_REQ_ID,
106 CREDIT_AMOUNT,
107 REQUESTED_BY,
108 REQUESTED_DATE,
109 APPROVED_BY,
110 APPROVED_DATE,
111 STATUS,
112 CREDIT_KHR_ID,
113 CURRENCY_CODE,
114 OBJECT_VERSION_NUMBER,
115 ATTRIBUTE_CATEGORY,
116 ATTRIBUTE1,
117 ATTRIBUTE2,
118 ATTRIBUTE3,
119 ATTRIBUTE4,
120 ATTRIBUTE5,
121 ATTRIBUTE6,
122 ATTRIBUTE7,
123 ATTRIBUTE8,
124 ATTRIBUTE9,
125 ATTRIBUTE10,
126 ATTRIBUTE11,
127 ATTRIBUTE12,
128 ATTRIBUTE13,
129 ATTRIBUTE14,
130 ATTRIBUTE15,
131 ORG_ID,
132 CREATED_BY,
133 CREATION_DATE,
134 LAST_UPDATED_BY,
135 LAST_UPDATE_DATE,
136 LAST_UPDATE_LOGIN
137 FROM OKL_CREDIT_REQUESTS
138 WHERE OKL_CREDIT_REQUESTS.id = p_id;
139 l_okl_crqv_pk okl_crqv_pk_csr%ROWTYPE;
140 l_crqv_rec crqv_rec_type;
141 BEGIN
142 x_no_data_found := TRUE;
143 -- Get current database values
144 OPEN okl_crqv_pk_csr (p_crqv_rec.id);
145 FETCH okl_crqv_pk_csr INTO
146 l_crqv_rec.id,
147 l_crqv_rec.quote_id,
148 l_crqv_rec.credit_req_number,
149 l_crqv_rec.credit_req_id,
150 l_crqv_rec.credit_amount,
151 l_crqv_rec.requested_by,
152 l_crqv_rec.requested_date,
153 l_crqv_rec.approved_by,
154 l_crqv_rec.approved_date,
155 l_crqv_rec.status,
156 l_crqv_rec.credit_khr_id,
157 l_crqv_rec.currency_code,
158 l_crqv_rec.object_version_number,
159 l_crqv_rec.attribute_category,
160 l_crqv_rec.attribute1,
161 l_crqv_rec.attribute2,
162 l_crqv_rec.attribute3,
163 l_crqv_rec.attribute4,
164 l_crqv_rec.attribute5,
165 l_crqv_rec.attribute6,
166 l_crqv_rec.attribute7,
167 l_crqv_rec.attribute8,
168 l_crqv_rec.attribute9,
169 l_crqv_rec.attribute10,
170 l_crqv_rec.attribute11,
171 l_crqv_rec.attribute12,
172 l_crqv_rec.attribute13,
173 l_crqv_rec.attribute14,
174 l_crqv_rec.attribute15,
175 l_crqv_rec.org_id,
176 l_crqv_rec.created_by,
177 l_crqv_rec.creation_date,
178 l_crqv_rec.last_updated_by,
179 l_crqv_rec.last_update_date,
180 l_crqv_rec.last_update_login;
181 x_no_data_found := okl_crqv_pk_csr%NOTFOUND;
182 CLOSE okl_crqv_pk_csr;
183 RETURN(l_crqv_rec);
184 END get_rec;
185
186 ------------------------------------------------------------------
187 -- This version of get_rec sets error messages if no data found --
188 ------------------------------------------------------------------
189 FUNCTION get_rec (
190 p_crqv_rec IN crqv_rec_type,
191 x_return_status OUT NOCOPY VARCHAR2
192 ) RETURN crqv_rec_type IS
193 l_crqv_rec crqv_rec_type;
194 l_row_notfound BOOLEAN := TRUE;
195 BEGIN
196 x_return_status := OKC_API.G_RET_STS_SUCCESS;
197 l_crqv_rec := get_rec(p_crqv_rec, l_row_notfound);
198 IF (l_row_notfound) THEN
199 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
200 x_return_status := OKC_API.G_RET_STS_ERROR;
201 END IF;
202 RETURN(l_crqv_rec);
203 END get_rec;
204 -----------------------------------------------------------
205 -- So we don't have to pass an "l_row_notfound" variable --
206 -----------------------------------------------------------
207 FUNCTION get_rec (
208 p_crqv_rec IN crqv_rec_type
209 ) RETURN crqv_rec_type IS
210 l_row_not_found BOOLEAN := TRUE;
211 BEGIN
212 RETURN(get_rec(p_crqv_rec, l_row_not_found));
213 END get_rec;
214 ---------------------------------------------------------------------------
215 -- FUNCTION get_rec for: OKL_CREDIT_REQUESTS
216 ---------------------------------------------------------------------------
217 FUNCTION get_rec (
218 p_crq_rec IN crq_rec_type,
219 x_no_data_found OUT NOCOPY BOOLEAN
220 ) RETURN crq_rec_type IS
221 CURSOR okl_crq_pk_csr (p_id IN NUMBER) IS
222 SELECT
223 ID,
224 QUOTE_ID,
225 CREDIT_REQ_NUMBER,
226 CREDIT_REQ_ID,
227 CREDIT_AMOUNT,
228 REQUESTED_BY,
229 REQUESTED_DATE,
230 APPROVED_BY,
231 APPROVED_DATE,
232 STATUS,
233 CREDIT_KHR_ID,
234 CURRENCY_CODE,
235 OBJECT_VERSION_NUMBER,
236 ATTRIBUTE_CATEGORY,
237 ATTRIBUTE1,
238 ATTRIBUTE2,
239 ATTRIBUTE3,
240 ATTRIBUTE4,
241 ATTRIBUTE5,
242 ATTRIBUTE6,
243 ATTRIBUTE7,
244 ATTRIBUTE8,
245 ATTRIBUTE9,
246 ATTRIBUTE10,
247 ATTRIBUTE11,
248 ATTRIBUTE12,
249 ATTRIBUTE13,
250 ATTRIBUTE14,
251 ATTRIBUTE15,
252 ORG_ID,
253 CREATED_BY,
254 CREATION_DATE,
255 LAST_UPDATED_BY,
256 LAST_UPDATE_DATE,
257 LAST_UPDATE_LOGIN
258 FROM Okl_Credit_Requests
259 WHERE okl_credit_requests.id = p_id;
260 l_okl_crq_pk okl_crq_pk_csr%ROWTYPE;
261 l_crq_rec crq_rec_type;
262 BEGIN
263 x_no_data_found := TRUE;
264 -- Get current database values
265 OPEN okl_crq_pk_csr (p_crq_rec.id);
266 FETCH okl_crq_pk_csr INTO
267 l_crq_rec.id,
268 l_crq_rec.quote_id,
269 l_crq_rec.credit_req_number,
270 l_crq_rec.credit_req_id,
271 l_crq_rec.credit_amount,
272 l_crq_rec.requested_by,
273 l_crq_rec.requested_date,
274 l_crq_rec.approved_by,
275 l_crq_rec.approved_date,
276 l_crq_rec.status,
277 l_crq_rec.credit_khr_id,
278 l_crq_rec.currency_code,
279 l_crq_rec.object_version_number,
280 l_crq_rec.attribute_category,
281 l_crq_rec.attribute1,
282 l_crq_rec.attribute2,
283 l_crq_rec.attribute3,
284 l_crq_rec.attribute4,
285 l_crq_rec.attribute5,
286 l_crq_rec.attribute6,
287 l_crq_rec.attribute7,
288 l_crq_rec.attribute8,
289 l_crq_rec.attribute9,
290 l_crq_rec.attribute10,
291 l_crq_rec.attribute11,
292 l_crq_rec.attribute12,
293 l_crq_rec.attribute13,
294 l_crq_rec.attribute14,
295 l_crq_rec.attribute15,
296 l_crq_rec.org_id,
297 l_crq_rec.created_by,
298 l_crq_rec.creation_date,
299 l_crq_rec.last_updated_by,
300 l_crq_rec.last_update_date,
301 l_crq_rec.last_update_login;
302 x_no_data_found := okl_crq_pk_csr%NOTFOUND;
303 CLOSE okl_crq_pk_csr;
304 RETURN(l_crq_rec);
305 END get_rec;
306
307 ------------------------------------------------------------------
308 -- This version of get_rec sets error messages if no data found --
309 ------------------------------------------------------------------
310 FUNCTION get_rec (
311 p_crq_rec IN crq_rec_type,
312 x_return_status OUT NOCOPY VARCHAR2
313 ) RETURN crq_rec_type IS
314 l_crq_rec crq_rec_type;
315 l_row_notfound BOOLEAN := TRUE;
316 BEGIN
317 x_return_status := OKC_API.G_RET_STS_SUCCESS;
318 l_crq_rec := get_rec(p_crq_rec, l_row_notfound);
319 IF (l_row_notfound) THEN
320 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
321 x_return_status := OKC_API.G_RET_STS_ERROR;
322 END IF;
323 RETURN(l_crq_rec);
324 END get_rec;
325 -----------------------------------------------------------
326 -- So we don't have to pass an "l_row_notfound" variable --
327 -----------------------------------------------------------
328 FUNCTION get_rec (
329 p_crq_rec IN crq_rec_type
330 ) RETURN crq_rec_type IS
331 l_row_not_found BOOLEAN := TRUE;
332 BEGIN
333 RETURN(get_rec(p_crq_rec, l_row_not_found));
334 END get_rec;
335 ---------------------------------------------------------------------------
336 -- FUNCTION null_out_defaults for: OKL_CREDIT_REQUESTS_V
337 ---------------------------------------------------------------------------
338 FUNCTION null_out_defaults (
339 p_crqv_rec IN crqv_rec_type
340 ) RETURN crqv_rec_type IS
341 l_crqv_rec crqv_rec_type := p_crqv_rec;
342 BEGIN
343 IF (l_crqv_rec.id = OKC_API.G_MISS_NUM ) THEN
344 l_crqv_rec.id := NULL;
345 END IF;
346 IF (l_crqv_rec.quote_id = OKC_API.G_MISS_NUM ) THEN
347 l_crqv_rec.quote_id := NULL;
348 END IF;
349 IF (l_crqv_rec.credit_req_number = OKC_API.G_MISS_CHAR ) THEN
350 l_crqv_rec.credit_req_number := NULL;
351 END IF;
352 IF (l_crqv_rec.credit_req_id = OKC_API.G_MISS_NUM ) THEN
353 l_crqv_rec.credit_req_id := NULL;
354 END IF;
355 IF (l_crqv_rec.credit_amount = OKC_API.G_MISS_NUM ) THEN
356 l_crqv_rec.credit_amount := NULL;
357 END IF;
358 IF (l_crqv_rec.requested_by = OKC_API.G_MISS_NUM ) THEN
359 l_crqv_rec.requested_by := NULL;
360 END IF;
361 IF (l_crqv_rec.requested_date = OKC_API.G_MISS_DATE ) THEN
362 l_crqv_rec.requested_date := NULL;
363 END IF;
364 IF (l_crqv_rec.approved_by = OKC_API.G_MISS_NUM ) THEN
365 l_crqv_rec.approved_by := NULL;
366 END IF;
367 IF (l_crqv_rec.approved_date = OKC_API.G_MISS_DATE ) THEN
368 l_crqv_rec.approved_date := NULL;
369 END IF;
370 IF (l_crqv_rec.status = OKC_API.G_MISS_CHAR ) THEN
371 l_crqv_rec.status := NULL;
372 END IF;
373 IF (l_crqv_rec.credit_khr_id = OKC_API.G_MISS_NUM ) THEN
374 l_crqv_rec.credit_khr_id := NULL;
375 END IF;
376 IF (l_crqv_rec.currency_code = OKC_API.G_MISS_CHAR ) THEN
377 l_crqv_rec.currency_code := NULL;
378 END IF;
379 IF (l_crqv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
380 l_crqv_rec.object_version_number := NULL;
381 END IF;
382 IF (l_crqv_rec.attribute_category = OKC_API.G_MISS_CHAR ) THEN
383 l_crqv_rec.attribute_category := NULL;
384 END IF;
385 IF (l_crqv_rec.attribute1 = OKC_API.G_MISS_CHAR ) THEN
386 l_crqv_rec.attribute1 := NULL;
387 END IF;
388 IF (l_crqv_rec.attribute2 = OKC_API.G_MISS_CHAR ) THEN
389 l_crqv_rec.attribute2 := NULL;
390 END IF;
391 IF (l_crqv_rec.attribute3 = OKC_API.G_MISS_CHAR ) THEN
392 l_crqv_rec.attribute3 := NULL;
393 END IF;
394 IF (l_crqv_rec.attribute4 = OKC_API.G_MISS_CHAR ) THEN
395 l_crqv_rec.attribute4 := NULL;
396 END IF;
397 IF (l_crqv_rec.attribute5 = OKC_API.G_MISS_CHAR ) THEN
398 l_crqv_rec.attribute5 := NULL;
399 END IF;
400 IF (l_crqv_rec.attribute6 = OKC_API.G_MISS_CHAR ) THEN
401 l_crqv_rec.attribute6 := NULL;
402 END IF;
403 IF (l_crqv_rec.attribute7 = OKC_API.G_MISS_CHAR ) THEN
404 l_crqv_rec.attribute7 := NULL;
405 END IF;
406 IF (l_crqv_rec.attribute8 = OKC_API.G_MISS_CHAR ) THEN
407 l_crqv_rec.attribute8 := NULL;
408 END IF;
409 IF (l_crqv_rec.attribute9 = OKC_API.G_MISS_CHAR ) THEN
410 l_crqv_rec.attribute9 := NULL;
411 END IF;
412 IF (l_crqv_rec.attribute10 = OKC_API.G_MISS_CHAR ) THEN
413 l_crqv_rec.attribute10 := NULL;
414 END IF;
415 IF (l_crqv_rec.attribute11 = OKC_API.G_MISS_CHAR ) THEN
416 l_crqv_rec.attribute11 := NULL;
417 END IF;
418 IF (l_crqv_rec.attribute12 = OKC_API.G_MISS_CHAR ) THEN
419 l_crqv_rec.attribute12 := NULL;
420 END IF;
421 IF (l_crqv_rec.attribute13 = OKC_API.G_MISS_CHAR ) THEN
422 l_crqv_rec.attribute13 := NULL;
423 END IF;
424 IF (l_crqv_rec.attribute14 = OKC_API.G_MISS_CHAR ) THEN
425 l_crqv_rec.attribute14 := NULL;
426 END IF;
427 IF (l_crqv_rec.attribute15 = OKC_API.G_MISS_CHAR ) THEN
428 l_crqv_rec.attribute15 := NULL;
429 END IF;
430 IF (l_crqv_rec.org_id = OKC_API.G_MISS_NUM ) THEN
431 l_crqv_rec.org_id := NULL;
432 END IF;
433 IF (l_crqv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
434 l_crqv_rec.created_by := NULL;
435 END IF;
436 IF (l_crqv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
437 l_crqv_rec.creation_date := NULL;
438 END IF;
439 IF (l_crqv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
440 l_crqv_rec.last_updated_by := NULL;
441 END IF;
442 IF (l_crqv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
443 l_crqv_rec.last_update_date := NULL;
444 END IF;
445 IF (l_crqv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
446 l_crqv_rec.last_update_login := NULL;
447 END IF;
448 RETURN(l_crqv_rec);
449 END null_out_defaults;
450 ---------------------------------
451 -- Validate_Attributes for: ID --
452 ---------------------------------
453 PROCEDURE validate_id(
454 x_return_status OUT NOCOPY VARCHAR2,
455 p_id IN NUMBER) IS
456 BEGIN
457 x_return_status := OKC_API.G_RET_STS_SUCCESS;
458 IF (p_id = OKC_API.G_MISS_NUM OR
459 p_id IS NULL)
460 THEN
461 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
462 x_return_status := OKC_API.G_RET_STS_ERROR;
463 RAISE G_EXCEPTION_HALT_VALIDATION;
464 END IF;
465 EXCEPTION
466 WHEN G_EXCEPTION_HALT_VALIDATION THEN
467 null;
468 WHEN OTHERS THEN
469 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
470 ,p_msg_name => G_UNEXPECTED_ERROR
471 ,p_token1 => G_SQLCODE_TOKEN
472 ,p_token1_value => SQLCODE
473 ,p_token2 => G_SQLERRM_TOKEN
474 ,p_token2_value => SQLERRM);
475 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
476 END validate_id;
477 ---------------------------------------
478 -- Validate_Attributes for: QUOTE_ID --
479 ---------------------------------------
480 PROCEDURE validate_quote_id(
481 x_return_status OUT NOCOPY VARCHAR2,
482 p_quote_id IN NUMBER) IS
483 BEGIN
484 x_return_status := OKC_API.G_RET_STS_SUCCESS;
485 IF (p_quote_id = OKC_API.G_MISS_NUM OR
486 p_quote_id IS NULL)
487 THEN
488 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'quote_id');
489 x_return_status := OKC_API.G_RET_STS_ERROR;
490 RAISE G_EXCEPTION_HALT_VALIDATION;
491 END IF;
492 EXCEPTION
493 WHEN G_EXCEPTION_HALT_VALIDATION THEN
494 null;
495 WHEN OTHERS THEN
496 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
497 ,p_msg_name => G_UNEXPECTED_ERROR
498 ,p_token1 => G_SQLCODE_TOKEN
499 ,p_token1_value => SQLCODE
500 ,p_token2 => G_SQLERRM_TOKEN
501 ,p_token2_value => SQLERRM);
502 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
503 END validate_quote_id;
504 ------------------------------------------------
505 -- Validate_Attributes for: CREDIT_REQ_NUMBER --
506 ------------------------------------------------
507 PROCEDURE validate_credit_req_number(
508 x_return_status OUT NOCOPY VARCHAR2,
509 p_credit_req_number IN VARCHAR2) IS
510 BEGIN
511 x_return_status := OKC_API.G_RET_STS_SUCCESS;
512 IF (p_credit_req_number = OKC_API.G_MISS_CHAR OR
513 p_credit_req_number IS NULL)
514 THEN
515 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'credit_req_number');
516 x_return_status := OKC_API.G_RET_STS_ERROR;
517 RAISE G_EXCEPTION_HALT_VALIDATION;
518 END IF;
519 EXCEPTION
520 WHEN G_EXCEPTION_HALT_VALIDATION THEN
521 null;
522 WHEN OTHERS THEN
523 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
524 ,p_msg_name => G_UNEXPECTED_ERROR
525 ,p_token1 => G_SQLCODE_TOKEN
526 ,p_token1_value => SQLCODE
527 ,p_token2 => G_SQLERRM_TOKEN
528 ,p_token2_value => SQLERRM);
529 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
530 END validate_credit_req_number;
531 ----------------------------------------------------
532 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
533 ----------------------------------------------------
534 PROCEDURE validate_object_version_number(
535 x_return_status OUT NOCOPY VARCHAR2,
536 p_object_version_number IN NUMBER) IS
537 BEGIN
538 x_return_status := OKC_API.G_RET_STS_SUCCESS;
539 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
540 p_object_version_number IS NULL)
541 THEN
542 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
543 x_return_status := OKC_API.G_RET_STS_ERROR;
544 RAISE G_EXCEPTION_HALT_VALIDATION;
545 END IF;
546 EXCEPTION
547 WHEN G_EXCEPTION_HALT_VALIDATION THEN
548 null;
549 WHEN OTHERS THEN
550 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
551 ,p_msg_name => G_UNEXPECTED_ERROR
552 ,p_token1 => G_SQLCODE_TOKEN
553 ,p_token1_value => SQLCODE
554 ,p_token2 => G_SQLERRM_TOKEN
555 ,p_token2_value => SQLERRM);
556 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
557 END validate_object_version_number;
558 ---------------------------------------------------------------------------
559 -- FUNCTION Validate_Attributes
560 ---------------------------------------------------------------------------
561 ---------------------------------------------------
562 -- Validate_Attributes for:OKL_CREDIT_REQUESTS_V --
563 ---------------------------------------------------
564 FUNCTION Validate_Attributes (
565 p_crqv_rec IN crqv_rec_type
566 ) RETURN VARCHAR2 IS
567 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
568 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
569 BEGIN
570 -----------------------------
571 -- Column Level Validation --
572 -----------------------------
573 -- ***
574 -- id
575 -- ***
576 validate_id(x_return_status, p_crqv_rec.id);
577 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
578 l_return_status := x_return_status;
579 RAISE G_EXCEPTION_HALT_VALIDATION;
580 END IF;
581
582 -- ***
583 -- quote_id
584 -- ***
585 validate_quote_id(x_return_status, p_crqv_rec.quote_id);
586 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
587 l_return_status := x_return_status;
588 RAISE G_EXCEPTION_HALT_VALIDATION;
589 END IF;
590
591 -- ***
592 -- credit_req_number
593 -- ***
594 validate_credit_req_number(x_return_status, p_crqv_rec.credit_req_number);
595 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
596 l_return_status := x_return_status;
597 RAISE G_EXCEPTION_HALT_VALIDATION;
598 END IF;
599
600 -- ***
601 -- object_version_number
602 -- ***
603 validate_object_version_number(x_return_status, p_crqv_rec.object_version_number);
604 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
605 l_return_status := x_return_status;
606 RAISE G_EXCEPTION_HALT_VALIDATION;
607 END IF;
608
609 RETURN(l_return_status);
610 EXCEPTION
611 WHEN G_EXCEPTION_HALT_VALIDATION THEN
612 RETURN(l_return_status);
613 WHEN OTHERS THEN
614 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
615 ,p_msg_name => G_UNEXPECTED_ERROR
616 ,p_token1 => G_SQLCODE_TOKEN
617 ,p_token1_value => SQLCODE
618 ,p_token2 => G_SQLERRM_TOKEN
619 ,p_token2_value => SQLERRM);
620 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
621 RETURN(l_return_status);
622 END Validate_Attributes;
623 ---------------------------------------------------------------------------
624 -- PROCEDURE Validate_Record
625 ---------------------------------------------------------------------------
626 -----------------------------------------------
627 -- Validate Record for:OKL_CREDIT_REQUESTS_V --
628 -----------------------------------------------
629 FUNCTION Validate_Record (
630 p_crqv_rec IN crqv_rec_type,
631 p_db_crqv_rec IN crqv_rec_type
632 ) RETURN VARCHAR2 IS
633 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
634 ------------------------------------
635 -- FUNCTION validate_foreign_keys --
636 ------------------------------------
637 FUNCTION validate_foreign_keys (
638 p_crqv_rec IN crqv_rec_type,
639 p_db_crqv_rec IN crqv_rec_type
640 ) RETURN VARCHAR2 IS
641 item_not_found_error EXCEPTION;
642 CURSOR okc_chrv_pk_csr (p_id IN NUMBER) IS
643 SELECT 'x'
644 FROM Okc_K_Headers_V
645 WHERE okc_k_headers_v.id = p_id;
646 l_okc_chrv_pk okc_chrv_pk_csr%ROWTYPE;
647
648 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
649 l_row_notfound BOOLEAN := TRUE;
650 BEGIN
651 IF ((p_crqv_rec.QUOTE_ID IS NOT NULL)
652 AND
653 (p_crqv_rec.QUOTE_ID <> p_db_crqv_rec.QUOTE_ID))
654 THEN
655 OPEN okc_chrv_pk_csr (p_crqv_rec.QUOTE_ID);
656 FETCH okc_chrv_pk_csr INTO l_okc_chrv_pk;
657 l_row_notfound := okc_chrv_pk_csr%NOTFOUND;
658 CLOSE okc_chrv_pk_csr;
659 IF (l_row_notfound) THEN
660 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'QUOTE_ID');
661 RAISE item_not_found_error;
662 END IF;
663 END IF;
664 RETURN (l_return_status);
665 EXCEPTION
666 WHEN item_not_found_error THEN
667 l_return_status := OKC_API.G_RET_STS_ERROR;
668 RETURN (l_return_status);
669 END validate_foreign_keys;
670 BEGIN
671 l_return_status := validate_foreign_keys(p_crqv_rec, p_db_crqv_rec);
672 RETURN (l_return_status);
673 END Validate_Record;
674 FUNCTION Validate_Record (
675 p_crqv_rec IN crqv_rec_type
676 ) RETURN VARCHAR2 IS
677 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
678 l_db_crqv_rec crqv_rec_type := get_rec(p_crqv_rec);
679 BEGIN
680 l_return_status := Validate_Record(p_crqv_rec => p_crqv_rec,
681 p_db_crqv_rec => l_db_crqv_rec);
682 RETURN (l_return_status);
683 END Validate_Record;
684
685 ---------------------------------------------------------------------------
686 -- PROCEDURE Migrate
687 ---------------------------------------------------------------------------
688 PROCEDURE migrate (
689 p_from IN crqv_rec_type,
690 p_to IN OUT NOCOPY crq_rec_type
691 ) IS
692 BEGIN
693 p_to.id := p_from.id;
694 p_to.quote_id := p_from.quote_id;
695 p_to.credit_req_number := p_from.credit_req_number;
696 p_to.credit_req_id := p_from.credit_req_id;
697 p_to.credit_amount := p_from.credit_amount;
698 p_to.requested_by := p_from.requested_by;
699 p_to.requested_date := p_from.requested_date;
700 p_to.approved_by := p_from.approved_by;
701 p_to.approved_date := p_from.approved_date;
702 p_to.status := p_from.status;
703 p_to.credit_khr_id := p_from.credit_khr_id;
704 p_to.currency_code := p_from.currency_code;
705 p_to.object_version_number := p_from.object_version_number;
706 p_to.attribute_category := p_from.attribute_category;
707 p_to.attribute1 := p_from.attribute1;
708 p_to.attribute2 := p_from.attribute2;
709 p_to.attribute3 := p_from.attribute3;
710 p_to.attribute4 := p_from.attribute4;
711 p_to.attribute5 := p_from.attribute5;
712 p_to.attribute6 := p_from.attribute6;
713 p_to.attribute7 := p_from.attribute7;
714 p_to.attribute8 := p_from.attribute8;
715 p_to.attribute9 := p_from.attribute9;
716 p_to.attribute10 := p_from.attribute10;
717 p_to.attribute11 := p_from.attribute11;
718 p_to.attribute12 := p_from.attribute12;
719 p_to.attribute13 := p_from.attribute13;
720 p_to.attribute14 := p_from.attribute14;
721 p_to.attribute15 := p_from.attribute15;
722 p_to.org_id := p_from.org_id;
723 p_to.created_by := p_from.created_by;
724 p_to.creation_date := p_from.creation_date;
725 p_to.last_updated_by := p_from.last_updated_by;
726 p_to.last_update_date := p_from.last_update_date;
727 p_to.last_update_login := p_from.last_update_login;
728 END migrate;
729 PROCEDURE migrate (
730 p_from IN crq_rec_type,
731 p_to IN OUT NOCOPY crqv_rec_type
732 ) IS
733 BEGIN
734 p_to.id := p_from.id;
735 p_to.quote_id := p_from.quote_id;
736 p_to.credit_req_number := p_from.credit_req_number;
737 p_to.credit_req_id := p_from.credit_req_id;
738 p_to.credit_amount := p_from.credit_amount;
739 p_to.requested_by := p_from.requested_by;
740 p_to.requested_date := p_from.requested_date;
741 p_to.approved_by := p_from.approved_by;
742 p_to.approved_date := p_from.approved_date;
743 p_to.status := p_from.status;
744 p_to.credit_khr_id := p_from.credit_khr_id;
745 p_to.currency_code := p_from.currency_code;
746 p_to.object_version_number := p_from.object_version_number;
747 p_to.attribute_category := p_from.attribute_category;
748 p_to.attribute1 := p_from.attribute1;
749 p_to.attribute2 := p_from.attribute2;
750 p_to.attribute3 := p_from.attribute3;
751 p_to.attribute4 := p_from.attribute4;
752 p_to.attribute5 := p_from.attribute5;
753 p_to.attribute6 := p_from.attribute6;
754 p_to.attribute7 := p_from.attribute7;
755 p_to.attribute8 := p_from.attribute8;
756 p_to.attribute9 := p_from.attribute9;
757 p_to.attribute10 := p_from.attribute10;
758 p_to.attribute11 := p_from.attribute11;
759 p_to.attribute12 := p_from.attribute12;
760 p_to.attribute13 := p_from.attribute13;
761 p_to.attribute14 := p_from.attribute14;
762 p_to.attribute15 := p_from.attribute15;
763 p_to.org_id := p_from.org_id;
764 p_to.created_by := p_from.created_by;
765 p_to.creation_date := p_from.creation_date;
766 p_to.last_updated_by := p_from.last_updated_by;
767 p_to.last_update_date := p_from.last_update_date;
768 p_to.last_update_login := p_from.last_update_login;
769 END migrate;
770 ---------------------------------------------------------------------------
771 -- PROCEDURE validate_row
772 ---------------------------------------------------------------------------
773 --------------------------------------------
774 -- validate_row for:OKL_CREDIT_REQUESTS_V --
775 --------------------------------------------
776 PROCEDURE validate_row(
777 p_api_version IN NUMBER,
778 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
779 x_return_status OUT NOCOPY VARCHAR2,
780 x_msg_count OUT NOCOPY NUMBER,
781 x_msg_data OUT NOCOPY VARCHAR2,
782 p_crqv_rec IN crqv_rec_type) IS
783
784 l_api_version CONSTANT NUMBER := 1;
785 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
786 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
787 l_crqv_rec crqv_rec_type := p_crqv_rec;
788 l_crq_rec crq_rec_type;
789 l_crq_rec crq_rec_type;
790 BEGIN
791 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
792 G_PKG_NAME,
793 p_init_msg_list,
794 l_api_version,
795 p_api_version,
796 '_PVT',
797 x_return_status);
798 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
799 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
800 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
801 RAISE OKC_API.G_EXCEPTION_ERROR;
802 END IF;
803 --- Validate all non-missing attributes (Item Level Validation)
804 l_return_status := Validate_Attributes(l_crqv_rec);
805 --- If any errors happen abort API
806 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
807 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
808 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
809 RAISE OKC_API.G_EXCEPTION_ERROR;
810 END IF;
811 l_return_status := Validate_Record(l_crqv_rec);
812 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
813 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
814 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
815 RAISE OKC_API.G_EXCEPTION_ERROR;
816 END IF;
817 x_return_status := l_return_status;
818 EXCEPTION
819 WHEN OKC_API.G_EXCEPTION_ERROR THEN
820 x_return_status := OKC_API.HANDLE_EXCEPTIONS
821 (
822 l_api_name,
823 G_PKG_NAME,
824 'OKC_API.G_RET_STS_ERROR',
825 x_msg_count,
826 x_msg_data,
827 '_PVT'
828 );
829 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
830 x_return_status := OKC_API.HANDLE_EXCEPTIONS
831 (
832 l_api_name,
833 G_PKG_NAME,
834 'OKC_API.G_RET_STS_UNEXP_ERROR',
835 x_msg_count,
836 x_msg_data,
837 '_PVT'
838 );
839 WHEN OTHERS THEN
840 x_return_status := OKC_API.HANDLE_EXCEPTIONS
841 (
842 l_api_name,
843 G_PKG_NAME,
844 'OTHERS',
845 x_msg_count,
846 x_msg_data,
847 '_PVT'
848 );
849 END validate_row;
850 -------------------------------------------------------
851 -- PL/SQL TBL validate_row for:OKL_CREDIT_REQUESTS_V --
852 -------------------------------------------------------
853 PROCEDURE validate_row(
854 p_api_version IN NUMBER,
855 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
856 x_return_status OUT NOCOPY VARCHAR2,
857 x_msg_count OUT NOCOPY NUMBER,
858 x_msg_data OUT NOCOPY VARCHAR2,
859 p_crqv_tbl IN crqv_tbl_type,
860 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
861
862 l_api_version CONSTANT NUMBER := 1;
863 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
864 i NUMBER := 0;
865 BEGIN
866 OKC_API.init_msg_list(p_init_msg_list);
867 -- Make sure PL/SQL table has records in it before passing
868 IF (p_crqv_tbl.COUNT > 0) THEN
869 i := p_crqv_tbl.FIRST;
870 LOOP
871 DECLARE
872 l_error_rec OKC_API.ERROR_REC_TYPE;
873 BEGIN
874 l_error_rec.api_name := l_api_name;
875 l_error_rec.api_package := G_PKG_NAME;
876 l_error_rec.idx := i;
877 validate_row (
878 p_api_version => p_api_version,
879 p_init_msg_list => OKC_API.G_FALSE,
880 x_return_status => l_error_rec.error_type,
881 x_msg_count => l_error_rec.msg_count,
882 x_msg_data => l_error_rec.msg_data,
883 p_crqv_rec => p_crqv_tbl(i));
884 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
885 l_error_rec.sqlcode := SQLCODE;
886 load_error_tbl(l_error_rec, px_error_tbl);
887 ELSE
888 x_msg_count := l_error_rec.msg_count;
889 x_msg_data := l_error_rec.msg_data;
890 END IF;
891 EXCEPTION
892 WHEN OKC_API.G_EXCEPTION_ERROR THEN
893 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
894 l_error_rec.sqlcode := SQLCODE;
895 load_error_tbl(l_error_rec, px_error_tbl);
896 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
897 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
898 l_error_rec.sqlcode := SQLCODE;
899 load_error_tbl(l_error_rec, px_error_tbl);
900 WHEN OTHERS THEN
901 l_error_rec.error_type := 'OTHERS';
902 l_error_rec.sqlcode := SQLCODE;
903 load_error_tbl(l_error_rec, px_error_tbl);
904 END;
905 EXIT WHEN (i = p_crqv_tbl.LAST);
906 i := p_crqv_tbl.NEXT(i);
907 END LOOP;
908 END IF;
909 -- Loop through the error_tbl to find the error with the highest severity
910 -- and return it.
911 x_return_status := find_highest_exception(px_error_tbl);
912 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
913 EXCEPTION
914 WHEN OKC_API.G_EXCEPTION_ERROR THEN
915 x_return_status := OKC_API.HANDLE_EXCEPTIONS
916 (
917 l_api_name,
918 G_PKG_NAME,
919 'OKC_API.G_RET_STS_ERROR',
920 x_msg_count,
921 x_msg_data,
922 '_PVT'
923 );
924 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
925 x_return_status := OKC_API.HANDLE_EXCEPTIONS
926 (
927 l_api_name,
928 G_PKG_NAME,
929 'OKC_API.G_RET_STS_UNEXP_ERROR',
930 x_msg_count,
931 x_msg_data,
932 '_PVT'
933 );
934 WHEN OTHERS THEN
935 x_return_status := OKC_API.HANDLE_EXCEPTIONS
936 (
937 l_api_name,
938 G_PKG_NAME,
939 'OTHERS',
940 x_msg_count,
941 x_msg_data,
942 '_PVT'
943 );
944 END validate_row;
945
946 -------------------------------------------------------
947 -- PL/SQL TBL validate_row for:OKL_CREDIT_REQUESTS_V --
948 -------------------------------------------------------
949 PROCEDURE validate_row(
950 p_api_version IN NUMBER,
951 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
952 x_return_status OUT NOCOPY VARCHAR2,
953 x_msg_count OUT NOCOPY NUMBER,
954 x_msg_data OUT NOCOPY VARCHAR2,
955 p_crqv_tbl IN crqv_tbl_type) IS
956
957 l_api_version CONSTANT NUMBER := 1;
958 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
959 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
960 l_error_tbl OKC_API.ERROR_TBL_TYPE;
961 BEGIN
962 OKC_API.init_msg_list(p_init_msg_list);
963 -- Make sure PL/SQL table has records in it before passing
964 IF (p_crqv_tbl.COUNT > 0) THEN
965 validate_row (
966 p_api_version => p_api_version,
967 p_init_msg_list => OKC_API.G_FALSE,
968 x_return_status => x_return_status,
969 x_msg_count => x_msg_count,
970 x_msg_data => x_msg_data,
971 p_crqv_tbl => p_crqv_tbl,
972 px_error_tbl => l_error_tbl);
973 END IF;
974 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
975 EXCEPTION
976 WHEN OKC_API.G_EXCEPTION_ERROR THEN
977 x_return_status := OKC_API.HANDLE_EXCEPTIONS
978 (
979 l_api_name,
980 G_PKG_NAME,
981 'OKC_API.G_RET_STS_ERROR',
982 x_msg_count,
983 x_msg_data,
984 '_PVT'
985 );
986 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
987 x_return_status := OKC_API.HANDLE_EXCEPTIONS
988 (
989 l_api_name,
990 G_PKG_NAME,
991 'OKC_API.G_RET_STS_UNEXP_ERROR',
992 x_msg_count,
993 x_msg_data,
994 '_PVT'
995 );
996 WHEN OTHERS THEN
997 x_return_status := OKC_API.HANDLE_EXCEPTIONS
998 (
999 l_api_name,
1000 G_PKG_NAME,
1001 'OTHERS',
1002 x_msg_count,
1003 x_msg_data,
1004 '_PVT'
1005 );
1006 END validate_row;
1007
1008 ---------------------------------------------------------------------------
1009 -- PROCEDURE insert_row
1010 ---------------------------------------------------------------------------
1011 ----------------------------------------
1012 -- insert_row for:OKL_CREDIT_REQUESTS --
1013 ----------------------------------------
1014 PROCEDURE insert_row(
1015 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1016 x_return_status OUT NOCOPY VARCHAR2,
1017 x_msg_count OUT NOCOPY NUMBER,
1018 x_msg_data OUT NOCOPY VARCHAR2,
1019 p_crq_rec IN crq_rec_type,
1020 x_crq_rec OUT NOCOPY crq_rec_type) IS
1021
1022 l_api_version CONSTANT NUMBER := 1;
1023 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1024 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1025 l_crq_rec crq_rec_type := p_crq_rec;
1026 l_def_crq_rec crq_rec_type;
1027 --------------------------------------------
1028 -- Set_Attributes for:OKL_CREDIT_REQUESTS --
1029 --------------------------------------------
1030 FUNCTION Set_Attributes (
1031 p_crq_rec IN crq_rec_type,
1032 x_crq_rec OUT NOCOPY crq_rec_type
1033 ) RETURN VARCHAR2 IS
1034 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1035 BEGIN
1036 x_crq_rec := p_crq_rec;
1037 RETURN(l_return_status);
1038 END Set_Attributes;
1039 BEGIN
1040 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1041 p_init_msg_list,
1042 '_PVT',
1043 x_return_status);
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 --- Setting item atributes
1050 l_return_status := Set_Attributes(
1051 p_crq_rec, -- IN
1052 l_crq_rec); -- OUT
1053 --- If any errors happen abort API
1054 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1055 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1056 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1057 RAISE OKC_API.G_EXCEPTION_ERROR;
1058 END IF;
1059 INSERT INTO OKL_CREDIT_REQUESTS(
1060 id,
1061 quote_id,
1062 credit_req_number,
1063 credit_req_id,
1064 credit_amount,
1065 requested_by,
1066 requested_date,
1067 approved_by,
1068 approved_date,
1069 status,
1070 credit_khr_id,
1071 currency_code,
1072 object_version_number,
1073 attribute_category,
1074 attribute1,
1075 attribute2,
1076 attribute3,
1077 attribute4,
1078 attribute5,
1079 attribute6,
1080 attribute7,
1081 attribute8,
1082 attribute9,
1083 attribute10,
1084 attribute11,
1085 attribute12,
1086 attribute13,
1087 attribute14,
1088 attribute15,
1089 org_id,
1090 created_by,
1091 creation_date,
1092 last_updated_by,
1093 last_update_date,
1094 last_update_login)
1095 VALUES (
1096 l_crq_rec.id,
1097 l_crq_rec.quote_id,
1098 l_crq_rec.credit_req_number,
1099 l_crq_rec.credit_req_id,
1100 l_crq_rec.credit_amount,
1101 l_crq_rec.requested_by,
1102 l_crq_rec.requested_date,
1103 l_crq_rec.approved_by,
1104 l_crq_rec.approved_date,
1105 l_crq_rec.status,
1106 l_crq_rec.credit_khr_id,
1107 l_crq_rec.currency_code,
1108 l_crq_rec.object_version_number,
1109 l_crq_rec.attribute_category,
1110 l_crq_rec.attribute1,
1111 l_crq_rec.attribute2,
1112 l_crq_rec.attribute3,
1113 l_crq_rec.attribute4,
1114 l_crq_rec.attribute5,
1115 l_crq_rec.attribute6,
1116 l_crq_rec.attribute7,
1117 l_crq_rec.attribute8,
1118 l_crq_rec.attribute9,
1119 l_crq_rec.attribute10,
1120 l_crq_rec.attribute11,
1121 l_crq_rec.attribute12,
1122 l_crq_rec.attribute13,
1123 l_crq_rec.attribute14,
1124 l_crq_rec.attribute15,
1125 l_crq_rec.org_id,
1126 l_crq_rec.created_by,
1127 l_crq_rec.creation_date,
1128 l_crq_rec.last_updated_by,
1129 l_crq_rec.last_update_date,
1130 l_crq_rec.last_update_login);
1131 -- Set OUT values
1132 x_crq_rec := l_crq_rec;
1133 x_return_status := l_return_status;
1134 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1135 EXCEPTION
1136 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1137 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1138 (
1139 l_api_name,
1140 G_PKG_NAME,
1141 'OKC_API.G_RET_STS_ERROR',
1142 x_msg_count,
1143 x_msg_data,
1144 '_PVT'
1145 );
1146 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1147 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1148 (
1149 l_api_name,
1150 G_PKG_NAME,
1151 'OKC_API.G_RET_STS_UNEXP_ERROR',
1152 x_msg_count,
1153 x_msg_data,
1154 '_PVT'
1155 );
1156 WHEN OTHERS THEN
1157 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1158 (
1159 l_api_name,
1160 G_PKG_NAME,
1161 'OTHERS',
1162 x_msg_count,
1163 x_msg_data,
1164 '_PVT'
1165 );
1166 END insert_row;
1167 -------------------------------------------
1168 -- insert_row for :OKL_CREDIT_REQUESTS_V --
1169 -------------------------------------------
1170 PROCEDURE insert_row(
1171 p_api_version IN NUMBER,
1172 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1173 x_return_status OUT NOCOPY VARCHAR2,
1174 x_msg_count OUT NOCOPY NUMBER,
1175 x_msg_data OUT NOCOPY VARCHAR2,
1176 p_crqv_rec IN crqv_rec_type,
1177 x_crqv_rec OUT NOCOPY crqv_rec_type) IS
1178
1179 l_api_version CONSTANT NUMBER := 1;
1180 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1181 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1182 l_crqv_rec crqv_rec_type := p_crqv_rec;
1183 l_def_crqv_rec crqv_rec_type;
1184 l_crq_rec crq_rec_type;
1185 lx_crq_rec crq_rec_type;
1186 -------------------------------
1187 -- FUNCTION fill_who_columns --
1188 -------------------------------
1189 FUNCTION fill_who_columns (
1190 p_crqv_rec IN crqv_rec_type
1191 ) RETURN crqv_rec_type IS
1192 l_crqv_rec crqv_rec_type := p_crqv_rec;
1193 BEGIN
1194 l_crqv_rec.CREATION_DATE := SYSDATE;
1195 l_crqv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1196 l_crqv_rec.LAST_UPDATE_DATE := l_crqv_rec.CREATION_DATE;
1197 l_crqv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1198 l_crqv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1199 RETURN(l_crqv_rec);
1200 END fill_who_columns;
1201 ----------------------------------------------
1202 -- Set_Attributes for:OKL_CREDIT_REQUESTS_V --
1203 ----------------------------------------------
1204 FUNCTION Set_Attributes (
1205 p_crqv_rec IN crqv_rec_type,
1206 x_crqv_rec OUT NOCOPY crqv_rec_type
1207 ) RETURN VARCHAR2 IS
1208 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1209 BEGIN
1210 x_crqv_rec := p_crqv_rec;
1211 x_crqv_rec.OBJECT_VERSION_NUMBER := 1;
1212 RETURN(l_return_status);
1213 END Set_Attributes;
1214 BEGIN
1215 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1216 G_PKG_NAME,
1217 p_init_msg_list,
1218 l_api_version,
1219 p_api_version,
1220 '_PVT',
1221 x_return_status);
1222 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1223 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1224 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1225 RAISE OKC_API.G_EXCEPTION_ERROR;
1226 END IF;
1227 l_crqv_rec := null_out_defaults(p_crqv_rec);
1228 -- Set primary key value
1229 l_crqv_rec.ID := get_seq_id;
1230 -- Setting item attributes
1231 l_return_Status := Set_Attributes(
1232 l_crqv_rec, -- IN
1233 l_def_crqv_rec); -- OUT
1234 --- If any errors happen abort API
1235 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1236 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1237 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1238 RAISE OKC_API.G_EXCEPTION_ERROR;
1239 END IF;
1240 l_def_crqv_rec := fill_who_columns(l_def_crqv_rec);
1241 --- Validate all non-missing attributes (Item Level Validation)
1242 l_return_status := Validate_Attributes(l_def_crqv_rec);
1243 --- If any errors happen abort API
1244 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1245 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1246 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1247 RAISE OKC_API.G_EXCEPTION_ERROR;
1248 END IF;
1249 l_return_status := Validate_Record(l_def_crqv_rec);
1250 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1251 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1252 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1253 RAISE OKC_API.G_EXCEPTION_ERROR;
1254 END IF;
1255 -----------------------------------------
1256 -- Move VIEW record to "Child" records --
1257 -----------------------------------------
1258 migrate(l_def_crqv_rec, l_crq_rec);
1259 -----------------------------------------------
1260 -- Call the INSERT_ROW for each child record --
1261 -----------------------------------------------
1262 insert_row(
1263 p_init_msg_list,
1264 l_return_status,
1265 x_msg_count,
1266 x_msg_data,
1267 l_crq_rec,
1268 lx_crq_rec
1269 );
1270 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1272 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1273 RAISE OKC_API.G_EXCEPTION_ERROR;
1274 END IF;
1275 migrate(lx_crq_rec, l_def_crqv_rec);
1276 -- Set OUT values
1277 x_crqv_rec := l_def_crqv_rec;
1278 x_return_status := l_return_status;
1279 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1280 EXCEPTION
1281 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1282 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1283 (
1284 l_api_name,
1285 G_PKG_NAME,
1286 'OKC_API.G_RET_STS_ERROR',
1287 x_msg_count,
1288 x_msg_data,
1289 '_PVT'
1290 );
1291 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1292 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1293 (
1294 l_api_name,
1295 G_PKG_NAME,
1296 'OKC_API.G_RET_STS_UNEXP_ERROR',
1297 x_msg_count,
1298 x_msg_data,
1299 '_PVT'
1300 );
1301 WHEN OTHERS THEN
1302 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1303 (
1304 l_api_name,
1305 G_PKG_NAME,
1306 'OTHERS',
1307 x_msg_count,
1308 x_msg_data,
1309 '_PVT'
1310 );
1311 END insert_row;
1312 ----------------------------------------
1313 -- PL/SQL TBL insert_row for:CRQV_TBL --
1314 ----------------------------------------
1315 PROCEDURE insert_row(
1316 p_api_version IN NUMBER,
1317 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1318 x_return_status OUT NOCOPY VARCHAR2,
1319 x_msg_count OUT NOCOPY NUMBER,
1320 x_msg_data OUT NOCOPY VARCHAR2,
1321 p_crqv_tbl IN crqv_tbl_type,
1322 x_crqv_tbl OUT NOCOPY crqv_tbl_type,
1323 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1324
1325 l_api_version CONSTANT NUMBER := 1;
1326 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1327 i NUMBER := 0;
1328 BEGIN
1329 OKC_API.init_msg_list(p_init_msg_list);
1330 -- Make sure PL/SQL table has records in it before passing
1331 IF (p_crqv_tbl.COUNT > 0) THEN
1332 i := p_crqv_tbl.FIRST;
1333 LOOP
1334 DECLARE
1335 l_error_rec OKC_API.ERROR_REC_TYPE;
1336 BEGIN
1337 l_error_rec.api_name := l_api_name;
1338 l_error_rec.api_package := G_PKG_NAME;
1339 l_error_rec.idx := i;
1340 insert_row (
1341 p_api_version => p_api_version,
1342 p_init_msg_list => OKC_API.G_FALSE,
1343 x_return_status => l_error_rec.error_type,
1344 x_msg_count => l_error_rec.msg_count,
1345 x_msg_data => l_error_rec.msg_data,
1346 p_crqv_rec => p_crqv_tbl(i),
1347 x_crqv_rec => x_crqv_tbl(i));
1348 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1349 l_error_rec.sqlcode := SQLCODE;
1350 load_error_tbl(l_error_rec, px_error_tbl);
1351 ELSE
1352 x_msg_count := l_error_rec.msg_count;
1353 x_msg_data := l_error_rec.msg_data;
1354 END IF;
1355 EXCEPTION
1356 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1357 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1358 l_error_rec.sqlcode := SQLCODE;
1359 load_error_tbl(l_error_rec, px_error_tbl);
1360 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1361 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1362 l_error_rec.sqlcode := SQLCODE;
1363 load_error_tbl(l_error_rec, px_error_tbl);
1364 WHEN OTHERS THEN
1365 l_error_rec.error_type := 'OTHERS';
1366 l_error_rec.sqlcode := SQLCODE;
1367 load_error_tbl(l_error_rec, px_error_tbl);
1368 END;
1369 EXIT WHEN (i = p_crqv_tbl.LAST);
1370 i := p_crqv_tbl.NEXT(i);
1371 END LOOP;
1372 END IF;
1373 -- Loop through the error_tbl to find the error with the highest severity
1374 -- and return it.
1375 x_return_status := find_highest_exception(px_error_tbl);
1376 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1377 EXCEPTION
1378 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1379 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1380 (
1381 l_api_name,
1382 G_PKG_NAME,
1383 'OKC_API.G_RET_STS_ERROR',
1384 x_msg_count,
1385 x_msg_data,
1386 '_PVT'
1387 );
1388 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1389 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1390 (
1391 l_api_name,
1392 G_PKG_NAME,
1393 'OKC_API.G_RET_STS_UNEXP_ERROR',
1394 x_msg_count,
1395 x_msg_data,
1396 '_PVT'
1397 );
1398 WHEN OTHERS THEN
1399 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1400 (
1401 l_api_name,
1402 G_PKG_NAME,
1403 'OTHERS',
1404 x_msg_count,
1405 x_msg_data,
1406 '_PVT'
1407 );
1408 END insert_row;
1409
1410 ----------------------------------------
1411 -- PL/SQL TBL insert_row for:CRQV_TBL --
1412 ----------------------------------------
1413 PROCEDURE insert_row(
1414 p_api_version IN NUMBER,
1415 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1416 x_return_status OUT NOCOPY VARCHAR2,
1417 x_msg_count OUT NOCOPY NUMBER,
1418 x_msg_data OUT NOCOPY VARCHAR2,
1419 p_crqv_tbl IN crqv_tbl_type,
1420 x_crqv_tbl OUT NOCOPY crqv_tbl_type) IS
1421
1422 l_api_version CONSTANT NUMBER := 1;
1423 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1424 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1425 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1426 BEGIN
1427 OKC_API.init_msg_list(p_init_msg_list);
1428 -- Make sure PL/SQL table has records in it before passing
1429 IF (p_crqv_tbl.COUNT > 0) THEN
1430 insert_row (
1431 p_api_version => p_api_version,
1432 p_init_msg_list => OKC_API.G_FALSE,
1433 x_return_status => x_return_status,
1434 x_msg_count => x_msg_count,
1435 x_msg_data => x_msg_data,
1436 p_crqv_tbl => p_crqv_tbl,
1437 x_crqv_tbl => x_crqv_tbl,
1438 px_error_tbl => l_error_tbl);
1439 END IF;
1440 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1441 EXCEPTION
1442 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1443 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1444 (
1445 l_api_name,
1446 G_PKG_NAME,
1447 'OKC_API.G_RET_STS_ERROR',
1448 x_msg_count,
1449 x_msg_data,
1450 '_PVT'
1451 );
1452 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1453 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1454 (
1455 l_api_name,
1456 G_PKG_NAME,
1457 'OKC_API.G_RET_STS_UNEXP_ERROR',
1458 x_msg_count,
1459 x_msg_data,
1460 '_PVT'
1461 );
1462 WHEN OTHERS THEN
1463 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1464 (
1465 l_api_name,
1466 G_PKG_NAME,
1467 'OTHERS',
1468 x_msg_count,
1469 x_msg_data,
1470 '_PVT'
1471 );
1472 END insert_row;
1473
1474 ---------------------------------------------------------------------------
1475 -- PROCEDURE lock_row
1476 ---------------------------------------------------------------------------
1477 --------------------------------------
1478 -- lock_row for:OKL_CREDIT_REQUESTS --
1479 --------------------------------------
1480 PROCEDURE lock_row(
1481 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1482 x_return_status OUT NOCOPY VARCHAR2,
1483 x_msg_count OUT NOCOPY NUMBER,
1484 x_msg_data OUT NOCOPY VARCHAR2,
1485 p_crq_rec IN crq_rec_type) IS
1486
1487 E_Resource_Busy EXCEPTION;
1488 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1489 CURSOR lock_csr (p_crq_rec IN crq_rec_type) IS
1490 SELECT OBJECT_VERSION_NUMBER
1491 FROM OKL_CREDIT_REQUESTS
1492 WHERE ID = p_crq_rec.id
1493 AND OBJECT_VERSION_NUMBER = p_crq_rec.object_version_number
1494 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1495
1496 CURSOR lchk_csr (p_crq_rec IN crq_rec_type) IS
1497 SELECT OBJECT_VERSION_NUMBER
1498 FROM OKL_CREDIT_REQUESTS
1499 WHERE ID = p_crq_rec.id;
1500 l_api_version CONSTANT NUMBER := 1;
1501 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1502 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1503 l_object_version_number OKL_CREDIT_REQUESTS.OBJECT_VERSION_NUMBER%TYPE;
1504 lc_object_version_number OKL_CREDIT_REQUESTS.OBJECT_VERSION_NUMBER%TYPE;
1505 l_row_notfound BOOLEAN := FALSE;
1506 lc_row_notfound BOOLEAN := FALSE;
1507 BEGIN
1508 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1509 p_init_msg_list,
1510 '_PVT',
1511 x_return_status);
1512 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1513 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1514 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1515 RAISE OKC_API.G_EXCEPTION_ERROR;
1516 END IF;
1517 BEGIN
1518 OPEN lock_csr(p_crq_rec);
1519 FETCH lock_csr INTO l_object_version_number;
1520 l_row_notfound := lock_csr%NOTFOUND;
1521 CLOSE lock_csr;
1522 EXCEPTION
1523 WHEN E_Resource_Busy THEN
1524 IF (lock_csr%ISOPEN) THEN
1525 CLOSE lock_csr;
1526 END IF;
1527 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1528 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1529 END;
1530
1531 IF ( l_row_notfound ) THEN
1532 OPEN lchk_csr(p_crq_rec);
1533 FETCH lchk_csr INTO lc_object_version_number;
1534 lc_row_notfound := lchk_csr%NOTFOUND;
1535 CLOSE lchk_csr;
1536 END IF;
1537 IF (lc_row_notfound) THEN
1538 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1539 RAISE OKC_API.G_EXCEPTION_ERROR;
1540 ELSIF lc_object_version_number > p_crq_rec.object_version_number THEN
1541 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1542 RAISE OKC_API.G_EXCEPTION_ERROR;
1543 ELSIF lc_object_version_number <> p_crq_rec.object_version_number THEN
1544 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1545 RAISE OKC_API.G_EXCEPTION_ERROR;
1546 ELSIF lc_object_version_number = -1 THEN
1547 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1548 RAISE OKC_API.G_EXCEPTION_ERROR;
1549 END IF;
1550 x_return_status := l_return_status;
1551 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1552 EXCEPTION
1553 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1554 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1555 (
1556 l_api_name,
1557 G_PKG_NAME,
1558 'OKC_API.G_RET_STS_ERROR',
1559 x_msg_count,
1560 x_msg_data,
1561 '_PVT'
1562 );
1563 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1564 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1565 (
1566 l_api_name,
1567 G_PKG_NAME,
1568 'OKC_API.G_RET_STS_UNEXP_ERROR',
1569 x_msg_count,
1570 x_msg_data,
1571 '_PVT'
1572 );
1573 WHEN OTHERS THEN
1574 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1575 (
1576 l_api_name,
1577 G_PKG_NAME,
1578 'OTHERS',
1579 x_msg_count,
1580 x_msg_data,
1581 '_PVT'
1582 );
1583 END lock_row;
1584 -----------------------------------------
1585 -- lock_row for: OKL_CREDIT_REQUESTS_V --
1586 -----------------------------------------
1587 PROCEDURE lock_row(
1588 p_api_version IN NUMBER,
1589 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1590 x_return_status OUT NOCOPY VARCHAR2,
1591 x_msg_count OUT NOCOPY NUMBER,
1592 x_msg_data OUT NOCOPY VARCHAR2,
1593 p_crqv_rec IN crqv_rec_type) IS
1594
1595 l_api_version CONSTANT NUMBER := 1;
1596 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1597 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1598 l_crq_rec crq_rec_type;
1599 BEGIN
1600 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1601 G_PKG_NAME,
1602 p_init_msg_list,
1603 l_api_version,
1604 p_api_version,
1605 '_PVT',
1606 x_return_status);
1607 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1608 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1609 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1610 RAISE OKC_API.G_EXCEPTION_ERROR;
1611 END IF;
1612 -----------------------------------------
1613 -- Move VIEW record to "Child" records --
1614 -----------------------------------------
1615 migrate(p_crqv_rec, l_crq_rec);
1616 ---------------------------------------------
1617 -- Call the LOCK_ROW for each child record --
1618 ---------------------------------------------
1619 lock_row(
1620 p_init_msg_list,
1621 l_return_status,
1622 x_msg_count,
1623 x_msg_data,
1624 l_crq_rec
1625 );
1626 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1627 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1628 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1629 RAISE OKC_API.G_EXCEPTION_ERROR;
1630 END IF;
1631 x_return_status := l_return_status;
1632 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1633 EXCEPTION
1634 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1635 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1636 (
1637 l_api_name,
1638 G_PKG_NAME,
1639 'OKC_API.G_RET_STS_ERROR',
1640 x_msg_count,
1641 x_msg_data,
1642 '_PVT'
1643 );
1644 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1645 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1646 (
1647 l_api_name,
1648 G_PKG_NAME,
1649 'OKC_API.G_RET_STS_UNEXP_ERROR',
1650 x_msg_count,
1651 x_msg_data,
1652 '_PVT'
1653 );
1654 WHEN OTHERS THEN
1655 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1656 (
1657 l_api_name,
1658 G_PKG_NAME,
1659 'OTHERS',
1660 x_msg_count,
1661 x_msg_data,
1662 '_PVT'
1663 );
1664 END lock_row;
1665 --------------------------------------
1666 -- PL/SQL TBL lock_row for:CRQV_TBL --
1667 --------------------------------------
1668 PROCEDURE lock_row(
1669 p_api_version IN NUMBER,
1670 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1671 x_return_status OUT NOCOPY VARCHAR2,
1672 x_msg_count OUT NOCOPY NUMBER,
1673 x_msg_data OUT NOCOPY VARCHAR2,
1674 p_crqv_tbl IN crqv_tbl_type,
1675 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1676
1677 l_api_version CONSTANT NUMBER := 1;
1678 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1679 i NUMBER := 0;
1680 BEGIN
1681 OKC_API.init_msg_list(p_init_msg_list);
1682 -- Make sure PL/SQL table has recrods in it before passing
1683 IF (p_crqv_tbl.COUNT > 0) THEN
1684 i := p_crqv_tbl.FIRST;
1685 LOOP
1686 DECLARE
1687 l_error_rec OKC_API.ERROR_REC_TYPE;
1688 BEGIN
1689 l_error_rec.api_name := l_api_name;
1690 l_error_rec.api_package := G_PKG_NAME;
1691 l_error_rec.idx := i;
1692 lock_row(
1693 p_api_version => p_api_version,
1694 p_init_msg_list => OKC_API.G_FALSE,
1695 x_return_status => l_error_rec.error_type,
1696 x_msg_count => l_error_rec.msg_count,
1697 x_msg_data => l_error_rec.msg_data,
1698 p_crqv_rec => p_crqv_tbl(i));
1699 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1700 l_error_rec.sqlcode := SQLCODE;
1701 load_error_tbl(l_error_rec, px_error_tbl);
1702 ELSE
1703 x_msg_count := l_error_rec.msg_count;
1704 x_msg_data := l_error_rec.msg_data;
1705 END IF;
1706 EXCEPTION
1707 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1708 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1709 l_error_rec.sqlcode := SQLCODE;
1710 load_error_tbl(l_error_rec, px_error_tbl);
1711 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1712 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1713 l_error_rec.sqlcode := SQLCODE;
1714 load_error_tbl(l_error_rec, px_error_tbl);
1715 WHEN OTHERS THEN
1716 l_error_rec.error_type := 'OTHERS';
1717 l_error_rec.sqlcode := SQLCODE;
1718 load_error_tbl(l_error_rec, px_error_tbl);
1719 END;
1720 EXIT WHEN (i = p_crqv_tbl.LAST);
1721 i := p_crqv_tbl.NEXT(i);
1722 END LOOP;
1723 END IF;
1724 -- Loop through the error_tbl to find the error with the highest severity
1725 -- and return it.
1726 x_return_status := find_highest_exception(px_error_tbl);
1727 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1728 EXCEPTION
1729 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1730 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1731 (
1732 l_api_name,
1733 G_PKG_NAME,
1734 'OKC_API.G_RET_STS_ERROR',
1735 x_msg_count,
1736 x_msg_data,
1737 '_PVT'
1738 );
1739 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1740 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1741 (
1742 l_api_name,
1743 G_PKG_NAME,
1744 'OKC_API.G_RET_STS_UNEXP_ERROR',
1745 x_msg_count,
1746 x_msg_data,
1747 '_PVT'
1748 );
1749 WHEN OTHERS THEN
1750 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1751 (
1752 l_api_name,
1753 G_PKG_NAME,
1754 'OTHERS',
1755 x_msg_count,
1756 x_msg_data,
1757 '_PVT'
1758 );
1759 END lock_row;
1760 --------------------------------------
1761 -- PL/SQL TBL lock_row for:CRQV_TBL --
1762 --------------------------------------
1763 PROCEDURE lock_row(
1764 p_api_version IN NUMBER,
1765 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1766 x_return_status OUT NOCOPY VARCHAR2,
1767 x_msg_count OUT NOCOPY NUMBER,
1768 x_msg_data OUT NOCOPY VARCHAR2,
1769 p_crqv_tbl IN crqv_tbl_type) IS
1770
1771 l_api_version CONSTANT NUMBER := 1;
1772 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1773 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1774 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1775 BEGIN
1776 OKC_API.init_msg_list(p_init_msg_list);
1777 -- Make sure PL/SQL table has recrods in it before passing
1778 IF (p_crqv_tbl.COUNT > 0) THEN
1779 lock_row(
1780 p_api_version => p_api_version,
1781 p_init_msg_list => OKC_API.G_FALSE,
1782 x_return_status => x_return_status,
1783 x_msg_count => x_msg_count,
1784 x_msg_data => x_msg_data,
1785 p_crqv_tbl => p_crqv_tbl,
1786 px_error_tbl => l_error_tbl);
1787 END IF;
1788 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1789 EXCEPTION
1790 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1791 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1792 (
1793 l_api_name,
1794 G_PKG_NAME,
1795 'OKC_API.G_RET_STS_ERROR',
1796 x_msg_count,
1797 x_msg_data,
1798 '_PVT'
1799 );
1800 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1801 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1802 (
1803 l_api_name,
1804 G_PKG_NAME,
1805 'OKC_API.G_RET_STS_UNEXP_ERROR',
1806 x_msg_count,
1807 x_msg_data,
1808 '_PVT'
1809 );
1810 WHEN OTHERS THEN
1811 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1812 (
1813 l_api_name,
1814 G_PKG_NAME,
1815 'OTHERS',
1816 x_msg_count,
1817 x_msg_data,
1818 '_PVT'
1819 );
1820 END lock_row;
1821 ---------------------------------------------------------------------------
1822 -- PROCEDURE update_row
1823 ---------------------------------------------------------------------------
1824 ----------------------------------------
1825 -- update_row for:OKL_CREDIT_REQUESTS --
1826 ----------------------------------------
1827 PROCEDURE update_row(
1828 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1829 x_return_status OUT NOCOPY VARCHAR2,
1830 x_msg_count OUT NOCOPY NUMBER,
1831 x_msg_data OUT NOCOPY VARCHAR2,
1832 p_crq_rec IN crq_rec_type,
1833 x_crq_rec OUT NOCOPY crq_rec_type) IS
1834
1835 l_api_version CONSTANT NUMBER := 1;
1836 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1837 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1838 l_crq_rec crq_rec_type := p_crq_rec;
1839 l_def_crq_rec crq_rec_type;
1840 l_row_notfound BOOLEAN := TRUE;
1841 ----------------------------------
1842 -- FUNCTION populate_new_record --
1843 ----------------------------------
1844 FUNCTION populate_new_record (
1845 p_crq_rec IN crq_rec_type,
1846 x_crq_rec OUT NOCOPY crq_rec_type
1847 ) RETURN VARCHAR2 IS
1848 l_crq_rec crq_rec_type;
1849 l_row_notfound BOOLEAN := TRUE;
1850 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1851 BEGIN
1852 x_crq_rec := p_crq_rec;
1853 -- Get current database values
1854 l_crq_rec := get_rec(p_crq_rec, l_return_status);
1855 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1856 IF (x_crq_rec.id = OKC_API.G_MISS_NUM)
1857 THEN
1858 x_crq_rec.id := l_crq_rec.id;
1859 END IF;
1860 IF (x_crq_rec.quote_id = OKC_API.G_MISS_NUM)
1861 THEN
1862 x_crq_rec.quote_id := l_crq_rec.quote_id;
1863 END IF;
1864 IF (x_crq_rec.credit_req_number = OKC_API.G_MISS_CHAR)
1865 THEN
1866 x_crq_rec.credit_req_number := l_crq_rec.credit_req_number;
1867 END IF;
1868 IF (x_crq_rec.credit_req_id = OKC_API.G_MISS_NUM)
1869 THEN
1870 x_crq_rec.credit_req_id := l_crq_rec.credit_req_id;
1871 END IF;
1872 IF (x_crq_rec.credit_amount = OKC_API.G_MISS_NUM)
1873 THEN
1874 x_crq_rec.credit_amount := l_crq_rec.credit_amount;
1875 END IF;
1876 IF (x_crq_rec.requested_by = OKC_API.G_MISS_NUM)
1877 THEN
1878 x_crq_rec.requested_by := l_crq_rec.requested_by;
1879 END IF;
1880 IF (x_crq_rec.requested_date = OKC_API.G_MISS_DATE)
1881 THEN
1882 x_crq_rec.requested_date := l_crq_rec.requested_date;
1883 END IF;
1884 IF (x_crq_rec.approved_by = OKC_API.G_MISS_NUM)
1885 THEN
1886 x_crq_rec.approved_by := l_crq_rec.approved_by;
1887 END IF;
1888 IF (x_crq_rec.approved_date = OKC_API.G_MISS_DATE)
1889 THEN
1890 x_crq_rec.approved_date := l_crq_rec.approved_date;
1891 END IF;
1892 IF (x_crq_rec.status = OKC_API.G_MISS_CHAR)
1893 THEN
1894 x_crq_rec.status := l_crq_rec.status;
1895 END IF;
1896 IF (x_crq_rec.credit_khr_id = OKC_API.G_MISS_NUM)
1897 THEN
1898 x_crq_rec.credit_khr_id := l_crq_rec.credit_khr_id;
1899 END IF;
1900 IF (x_crq_rec.currency_code = OKC_API.G_MISS_CHAR)
1901 THEN
1902 x_crq_rec.currency_code := l_crq_rec.currency_code;
1903 END IF;
1904 IF (x_crq_rec.object_version_number = OKC_API.G_MISS_NUM)
1905 THEN
1906 x_crq_rec.object_version_number := l_crq_rec.object_version_number;
1907 END IF;
1908 IF (x_crq_rec.attribute_category = OKC_API.G_MISS_CHAR)
1909 THEN
1910 x_crq_rec.attribute_category := l_crq_rec.attribute_category;
1911 END IF;
1912 IF (x_crq_rec.attribute1 = OKC_API.G_MISS_CHAR)
1913 THEN
1914 x_crq_rec.attribute1 := l_crq_rec.attribute1;
1915 END IF;
1916 IF (x_crq_rec.attribute2 = OKC_API.G_MISS_CHAR)
1917 THEN
1918 x_crq_rec.attribute2 := l_crq_rec.attribute2;
1919 END IF;
1920 IF (x_crq_rec.attribute3 = OKC_API.G_MISS_CHAR)
1921 THEN
1922 x_crq_rec.attribute3 := l_crq_rec.attribute3;
1923 END IF;
1924 IF (x_crq_rec.attribute4 = OKC_API.G_MISS_CHAR)
1925 THEN
1926 x_crq_rec.attribute4 := l_crq_rec.attribute4;
1927 END IF;
1928 IF (x_crq_rec.attribute5 = OKC_API.G_MISS_CHAR)
1929 THEN
1930 x_crq_rec.attribute5 := l_crq_rec.attribute5;
1931 END IF;
1932 IF (x_crq_rec.attribute6 = OKC_API.G_MISS_CHAR)
1933 THEN
1934 x_crq_rec.attribute6 := l_crq_rec.attribute6;
1935 END IF;
1936 IF (x_crq_rec.attribute7 = OKC_API.G_MISS_CHAR)
1937 THEN
1938 x_crq_rec.attribute7 := l_crq_rec.attribute7;
1939 END IF;
1940 IF (x_crq_rec.attribute8 = OKC_API.G_MISS_CHAR)
1941 THEN
1942 x_crq_rec.attribute8 := l_crq_rec.attribute8;
1943 END IF;
1944 IF (x_crq_rec.attribute9 = OKC_API.G_MISS_CHAR)
1945 THEN
1946 x_crq_rec.attribute9 := l_crq_rec.attribute9;
1947 END IF;
1948 IF (x_crq_rec.attribute10 = OKC_API.G_MISS_CHAR)
1949 THEN
1950 x_crq_rec.attribute10 := l_crq_rec.attribute10;
1951 END IF;
1952 IF (x_crq_rec.attribute11 = OKC_API.G_MISS_CHAR)
1953 THEN
1954 x_crq_rec.attribute11 := l_crq_rec.attribute11;
1955 END IF;
1956 IF (x_crq_rec.attribute12 = OKC_API.G_MISS_CHAR)
1957 THEN
1958 x_crq_rec.attribute12 := l_crq_rec.attribute12;
1959 END IF;
1960 IF (x_crq_rec.attribute13 = OKC_API.G_MISS_CHAR)
1961 THEN
1962 x_crq_rec.attribute13 := l_crq_rec.attribute13;
1963 END IF;
1964 IF (x_crq_rec.attribute14 = OKC_API.G_MISS_CHAR)
1965 THEN
1966 x_crq_rec.attribute14 := l_crq_rec.attribute14;
1967 END IF;
1968 IF (x_crq_rec.attribute15 = OKC_API.G_MISS_CHAR)
1969 THEN
1970 x_crq_rec.attribute15 := l_crq_rec.attribute15;
1971 END IF;
1972 IF (x_crq_rec.org_id = OKC_API.G_MISS_NUM)
1973 THEN
1974 x_crq_rec.org_id := l_crq_rec.org_id;
1975 END IF;
1976 IF (x_crq_rec.created_by = OKC_API.G_MISS_NUM)
1977 THEN
1978 x_crq_rec.created_by := l_crq_rec.created_by;
1979 END IF;
1980 IF (x_crq_rec.creation_date = OKC_API.G_MISS_DATE)
1981 THEN
1982 x_crq_rec.creation_date := l_crq_rec.creation_date;
1983 END IF;
1984 IF (x_crq_rec.last_updated_by = OKC_API.G_MISS_NUM)
1985 THEN
1986 x_crq_rec.last_updated_by := l_crq_rec.last_updated_by;
1987 END IF;
1988 IF (x_crq_rec.last_update_date = OKC_API.G_MISS_DATE)
1989 THEN
1990 x_crq_rec.last_update_date := l_crq_rec.last_update_date;
1991 END IF;
1992 IF (x_crq_rec.last_update_login = OKC_API.G_MISS_NUM)
1993 THEN
1994 x_crq_rec.last_update_login := l_crq_rec.last_update_login;
1995 END IF;
1996 END IF;
1997 RETURN(l_return_status);
1998 END populate_new_record;
1999 --------------------------------------------
2000 -- Set_Attributes for:OKL_CREDIT_REQUESTS --
2001 --------------------------------------------
2002 FUNCTION Set_Attributes (
2003 p_crq_rec IN crq_rec_type,
2004 x_crq_rec OUT NOCOPY crq_rec_type
2005 ) RETURN VARCHAR2 IS
2006 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2007 BEGIN
2008 x_crq_rec := p_crq_rec;
2009 x_crq_rec.OBJECT_VERSION_NUMBER := p_crq_rec.OBJECT_VERSION_NUMBER + 1;
2010 RETURN(l_return_status);
2011 END Set_Attributes;
2012 BEGIN
2013 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2014 p_init_msg_list,
2015 '_PVT',
2016 x_return_status);
2017 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2018 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2019 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2020 RAISE OKC_API.G_EXCEPTION_ERROR;
2021 END IF;
2022 --- Setting item attributes
2023 l_return_status := Set_Attributes(
2024 p_crq_rec, -- IN
2025 l_crq_rec); -- OUT
2026 --- If any errors happen abort API
2027 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2028 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2029 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2030 RAISE OKC_API.G_EXCEPTION_ERROR;
2031 END IF;
2032 l_return_status := populate_new_record(l_crq_rec, l_def_crq_rec);
2033 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2034 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2035 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2036 RAISE OKC_API.G_EXCEPTION_ERROR;
2037 END IF;
2038 UPDATE OKL_CREDIT_REQUESTS
2039 SET QUOTE_ID = l_def_crq_rec.quote_id,
2040 CREDIT_REQ_NUMBER = l_def_crq_rec.credit_req_number,
2041 CREDIT_REQ_ID = l_def_crq_rec.credit_req_id,
2042 CREDIT_AMOUNT = l_def_crq_rec.credit_amount,
2043 REQUESTED_BY = l_def_crq_rec.requested_by,
2044 REQUESTED_DATE = l_def_crq_rec.requested_date,
2045 APPROVED_BY = l_def_crq_rec.approved_by,
2046 APPROVED_DATE = l_def_crq_rec.approved_date,
2047 STATUS = l_def_crq_rec.status,
2048 CREDIT_KHR_ID = l_def_crq_rec.credit_khr_id,
2049 CURRENCY_CODE = l_def_crq_rec.currency_code,
2050 OBJECT_VERSION_NUMBER = l_def_crq_rec.object_version_number,
2051 ATTRIBUTE_CATEGORY = l_def_crq_rec.attribute_category,
2052 ATTRIBUTE1 = l_def_crq_rec.attribute1,
2053 ATTRIBUTE2 = l_def_crq_rec.attribute2,
2054 ATTRIBUTE3 = l_def_crq_rec.attribute3,
2055 ATTRIBUTE4 = l_def_crq_rec.attribute4,
2056 ATTRIBUTE5 = l_def_crq_rec.attribute5,
2057 ATTRIBUTE6 = l_def_crq_rec.attribute6,
2058 ATTRIBUTE7 = l_def_crq_rec.attribute7,
2059 ATTRIBUTE8 = l_def_crq_rec.attribute8,
2060 ATTRIBUTE9 = l_def_crq_rec.attribute9,
2061 ATTRIBUTE10 = l_def_crq_rec.attribute10,
2062 ATTRIBUTE11 = l_def_crq_rec.attribute11,
2063 ATTRIBUTE12 = l_def_crq_rec.attribute12,
2064 ATTRIBUTE13 = l_def_crq_rec.attribute13,
2065 ATTRIBUTE14 = l_def_crq_rec.attribute14,
2066 ATTRIBUTE15 = l_def_crq_rec.attribute15,
2067 ORG_ID = l_def_crq_rec.org_id,
2068 CREATED_BY = l_def_crq_rec.created_by,
2069 CREATION_DATE = l_def_crq_rec.creation_date,
2070 LAST_UPDATED_BY = l_def_crq_rec.last_updated_by,
2071 LAST_UPDATE_DATE = l_def_crq_rec.last_update_date,
2072 LAST_UPDATE_LOGIN = l_def_crq_rec.last_update_login
2073 WHERE ID = l_def_crq_rec.id;
2074
2075 x_crq_rec := l_crq_rec;
2076 x_return_status := l_return_status;
2077 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2078 EXCEPTION
2079 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2080 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2081 (
2082 l_api_name,
2083 G_PKG_NAME,
2084 'OKC_API.G_RET_STS_ERROR',
2085 x_msg_count,
2086 x_msg_data,
2087 '_PVT'
2088 );
2089 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2090 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2091 (
2092 l_api_name,
2093 G_PKG_NAME,
2094 'OKC_API.G_RET_STS_UNEXP_ERROR',
2095 x_msg_count,
2096 x_msg_data,
2097 '_PVT'
2098 );
2099 WHEN OTHERS THEN
2100 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2101 (
2102 l_api_name,
2103 G_PKG_NAME,
2104 'OTHERS',
2105 x_msg_count,
2106 x_msg_data,
2107 '_PVT'
2108 );
2109 END update_row;
2110 ------------------------------------------
2111 -- update_row for:OKL_CREDIT_REQUESTS_V --
2112 ------------------------------------------
2113 PROCEDURE update_row(
2114 p_api_version IN NUMBER,
2115 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2116 x_return_status OUT NOCOPY VARCHAR2,
2117 x_msg_count OUT NOCOPY NUMBER,
2118 x_msg_data OUT NOCOPY VARCHAR2,
2119 p_crqv_rec IN crqv_rec_type,
2120 x_crqv_rec OUT NOCOPY crqv_rec_type) IS
2121
2122 l_api_version CONSTANT NUMBER := 1;
2123 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2124 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2125 l_crqv_rec crqv_rec_type := p_crqv_rec;
2126 l_def_crqv_rec crqv_rec_type;
2127 l_db_crqv_rec crqv_rec_type;
2128 l_crq_rec crq_rec_type;
2129 lx_crq_rec crq_rec_type;
2130 -------------------------------
2131 -- FUNCTION fill_who_columns --
2132 -------------------------------
2133 FUNCTION fill_who_columns (
2134 p_crqv_rec IN crqv_rec_type
2135 ) RETURN crqv_rec_type IS
2136 l_crqv_rec crqv_rec_type := p_crqv_rec;
2137 BEGIN
2138 l_crqv_rec.LAST_UPDATE_DATE := SYSDATE;
2139 l_crqv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2140 l_crqv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2141 RETURN(l_crqv_rec);
2142 END fill_who_columns;
2143 ----------------------------------
2144 -- FUNCTION populate_new_record --
2145 ----------------------------------
2146 FUNCTION populate_new_record (
2147 p_crqv_rec IN crqv_rec_type,
2148 x_crqv_rec OUT NOCOPY crqv_rec_type
2149 ) RETURN VARCHAR2 IS
2150 l_row_notfound BOOLEAN := TRUE;
2151 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2152 BEGIN
2153 x_crqv_rec := p_crqv_rec;
2154 -- Get current database values
2155 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2156 -- so it may be verified through LOCK_ROW.
2157 l_db_crqv_rec := get_rec(p_crqv_rec, l_return_status);
2158 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2159 IF (x_crqv_rec.id = OKC_API.G_MISS_NUM)
2160 THEN
2161 x_crqv_rec.id := l_db_crqv_rec.id;
2162 END IF;
2163 IF (x_crqv_rec.quote_id = OKC_API.G_MISS_NUM)
2164 THEN
2165 x_crqv_rec.quote_id := l_db_crqv_rec.quote_id;
2166 END IF;
2167 IF (x_crqv_rec.credit_req_number = OKC_API.G_MISS_CHAR)
2168 THEN
2169 x_crqv_rec.credit_req_number := l_db_crqv_rec.credit_req_number;
2170 END IF;
2171 IF (x_crqv_rec.credit_req_id = OKC_API.G_MISS_NUM)
2172 THEN
2173 x_crqv_rec.credit_req_id := l_db_crqv_rec.credit_req_id;
2174 END IF;
2175 IF (x_crqv_rec.credit_amount = OKC_API.G_MISS_NUM)
2176 THEN
2177 x_crqv_rec.credit_amount := l_db_crqv_rec.credit_amount;
2178 END IF;
2179 IF (x_crqv_rec.requested_by = OKC_API.G_MISS_NUM)
2180 THEN
2181 x_crqv_rec.requested_by := l_db_crqv_rec.requested_by;
2182 END IF;
2183 IF (x_crqv_rec.requested_date = OKC_API.G_MISS_DATE)
2184 THEN
2185 x_crqv_rec.requested_date := l_db_crqv_rec.requested_date;
2186 END IF;
2187 IF (x_crqv_rec.approved_by = OKC_API.G_MISS_NUM)
2188 THEN
2189 x_crqv_rec.approved_by := l_db_crqv_rec.approved_by;
2190 END IF;
2191 IF (x_crqv_rec.approved_date = OKC_API.G_MISS_DATE)
2192 THEN
2193 x_crqv_rec.approved_date := l_db_crqv_rec.approved_date;
2194 END IF;
2195 IF (x_crqv_rec.status = OKC_API.G_MISS_CHAR)
2196 THEN
2197 x_crqv_rec.status := l_db_crqv_rec.status;
2198 END IF;
2199 IF (x_crqv_rec.credit_khr_id = OKC_API.G_MISS_NUM)
2200 THEN
2201 x_crqv_rec.credit_khr_id := l_db_crqv_rec.credit_khr_id;
2202 END IF;
2203 IF (x_crqv_rec.currency_code = OKC_API.G_MISS_CHAR)
2204 THEN
2205 x_crqv_rec.currency_code := l_db_crqv_rec.currency_code;
2206 END IF;
2207 IF (x_crqv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2208 THEN
2209 x_crqv_rec.attribute_category := l_db_crqv_rec.attribute_category;
2210 END IF;
2211 IF (x_crqv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2212 THEN
2213 x_crqv_rec.attribute1 := l_db_crqv_rec.attribute1;
2214 END IF;
2215 IF (x_crqv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2216 THEN
2217 x_crqv_rec.attribute2 := l_db_crqv_rec.attribute2;
2218 END IF;
2219 IF (x_crqv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2220 THEN
2221 x_crqv_rec.attribute3 := l_db_crqv_rec.attribute3;
2222 END IF;
2223 IF (x_crqv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2224 THEN
2225 x_crqv_rec.attribute4 := l_db_crqv_rec.attribute4;
2226 END IF;
2227 IF (x_crqv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2228 THEN
2229 x_crqv_rec.attribute5 := l_db_crqv_rec.attribute5;
2230 END IF;
2231 IF (x_crqv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2232 THEN
2233 x_crqv_rec.attribute6 := l_db_crqv_rec.attribute6;
2234 END IF;
2235 IF (x_crqv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2236 THEN
2237 x_crqv_rec.attribute7 := l_db_crqv_rec.attribute7;
2238 END IF;
2239 IF (x_crqv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2240 THEN
2241 x_crqv_rec.attribute8 := l_db_crqv_rec.attribute8;
2242 END IF;
2243 IF (x_crqv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2244 THEN
2245 x_crqv_rec.attribute9 := l_db_crqv_rec.attribute9;
2246 END IF;
2247 IF (x_crqv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2248 THEN
2249 x_crqv_rec.attribute10 := l_db_crqv_rec.attribute10;
2250 END IF;
2251 IF (x_crqv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2252 THEN
2253 x_crqv_rec.attribute11 := l_db_crqv_rec.attribute11;
2254 END IF;
2255 IF (x_crqv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2256 THEN
2257 x_crqv_rec.attribute12 := l_db_crqv_rec.attribute12;
2258 END IF;
2259 IF (x_crqv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2260 THEN
2261 x_crqv_rec.attribute13 := l_db_crqv_rec.attribute13;
2262 END IF;
2263 IF (x_crqv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2264 THEN
2265 x_crqv_rec.attribute14 := l_db_crqv_rec.attribute14;
2266 END IF;
2267 IF (x_crqv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2268 THEN
2269 x_crqv_rec.attribute15 := l_db_crqv_rec.attribute15;
2270 END IF;
2271 IF (x_crqv_rec.org_id = OKC_API.G_MISS_NUM)
2272 THEN
2273 x_crqv_rec.org_id := l_db_crqv_rec.org_id;
2274 END IF;
2275 IF (x_crqv_rec.created_by = OKC_API.G_MISS_NUM)
2276 THEN
2277 x_crqv_rec.created_by := l_db_crqv_rec.created_by;
2278 END IF;
2279 IF (x_crqv_rec.creation_date = OKC_API.G_MISS_DATE)
2280 THEN
2281 x_crqv_rec.creation_date := l_db_crqv_rec.creation_date;
2282 END IF;
2283 IF (x_crqv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2284 THEN
2285 x_crqv_rec.last_updated_by := l_db_crqv_rec.last_updated_by;
2286 END IF;
2287 IF (x_crqv_rec.last_update_date = OKC_API.G_MISS_DATE)
2288 THEN
2289 x_crqv_rec.last_update_date := l_db_crqv_rec.last_update_date;
2290 END IF;
2291 IF (x_crqv_rec.last_update_login = OKC_API.G_MISS_NUM)
2292 THEN
2293 x_crqv_rec.last_update_login := l_db_crqv_rec.last_update_login;
2294 END IF;
2295 END IF;
2296 RETURN(l_return_status);
2297 END populate_new_record;
2298 ----------------------------------------------
2299 -- Set_Attributes for:OKL_CREDIT_REQUESTS_V --
2300 ----------------------------------------------
2301 FUNCTION Set_Attributes (
2302 p_crqv_rec IN crqv_rec_type,
2303 x_crqv_rec OUT NOCOPY crqv_rec_type
2304 ) RETURN VARCHAR2 IS
2305 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2306 BEGIN
2307 x_crqv_rec := p_crqv_rec;
2308 RETURN(l_return_status);
2309 END Set_Attributes;
2310 BEGIN
2311 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2312 G_PKG_NAME,
2313 p_init_msg_list,
2314 l_api_version,
2315 p_api_version,
2316 '_PVT',
2317 x_return_status);
2318 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2319 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2320 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2321 RAISE OKC_API.G_EXCEPTION_ERROR;
2322 END IF;
2323 --- Setting item attributes
2324 l_return_status := Set_Attributes(
2325 p_crqv_rec, -- IN
2326 x_crqv_rec); -- OUT
2327 --- If any errors happen abort API
2328 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2329 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2330 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2331 RAISE OKC_API.G_EXCEPTION_ERROR;
2332 END IF;
2333 l_return_status := populate_new_record(l_crqv_rec, l_def_crqv_rec);
2334 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2335 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2336 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2337 RAISE OKC_API.G_EXCEPTION_ERROR;
2338 END IF;
2339 l_def_crqv_rec := fill_who_columns(l_def_crqv_rec);
2340 --- Validate all non-missing attributes (Item Level Validation)
2341 l_return_status := Validate_Attributes(l_def_crqv_rec);
2342 --- If any errors happen abort API
2343 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2344 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2345 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2346 RAISE OKC_API.G_EXCEPTION_ERROR;
2347 END IF;
2348 l_return_status := Validate_Record(l_def_crqv_rec, l_db_crqv_rec);
2349 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2350 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2351 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2352 RAISE OKC_API.G_EXCEPTION_ERROR;
2353 END IF;
2354
2355 -- Lock the Record
2356 lock_row(
2357 p_api_version => p_api_version,
2358 p_init_msg_list => p_init_msg_list,
2359 x_return_status => l_return_status,
2360 x_msg_count => x_msg_count,
2361 x_msg_data => x_msg_data,
2362 p_crqv_rec => p_crqv_rec);
2363 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2364 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2365 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2366 RAISE OKC_API.G_EXCEPTION_ERROR;
2367 END IF;
2368
2369 -----------------------------------------
2370 -- Move VIEW record to "Child" records --
2371 -----------------------------------------
2372 migrate(l_def_crqv_rec, l_crq_rec);
2373 -----------------------------------------------
2374 -- Call the UPDATE_ROW for each child record --
2375 -----------------------------------------------
2376 update_row(
2377 p_init_msg_list,
2378 l_return_status,
2379 x_msg_count,
2380 x_msg_data,
2381 l_crq_rec,
2382 lx_crq_rec
2383 );
2384 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2385 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2386 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2387 RAISE OKC_API.G_EXCEPTION_ERROR;
2388 END IF;
2389 migrate(lx_crq_rec, l_def_crqv_rec);
2390 x_crqv_rec := l_def_crqv_rec;
2391 x_return_status := l_return_status;
2392 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2393 EXCEPTION
2394 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2395 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2396 (
2397 l_api_name,
2398 G_PKG_NAME,
2399 'OKC_API.G_RET_STS_ERROR',
2400 x_msg_count,
2401 x_msg_data,
2402 '_PVT'
2403 );
2404 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2405 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2406 (
2407 l_api_name,
2408 G_PKG_NAME,
2409 'OKC_API.G_RET_STS_UNEXP_ERROR',
2410 x_msg_count,
2411 x_msg_data,
2412 '_PVT'
2413 );
2414 WHEN OTHERS THEN
2415 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2416 (
2417 l_api_name,
2418 G_PKG_NAME,
2419 'OTHERS',
2420 x_msg_count,
2421 x_msg_data,
2422 '_PVT'
2423 );
2424 END update_row;
2425 ----------------------------------------
2426 -- PL/SQL TBL update_row for:crqv_tbl --
2427 ----------------------------------------
2428 PROCEDURE update_row(
2429 p_api_version IN NUMBER,
2430 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2431 x_return_status OUT NOCOPY VARCHAR2,
2432 x_msg_count OUT NOCOPY NUMBER,
2433 x_msg_data OUT NOCOPY VARCHAR2,
2434 p_crqv_tbl IN crqv_tbl_type,
2435 x_crqv_tbl OUT NOCOPY crqv_tbl_type,
2436 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2437
2438 l_api_version CONSTANT NUMBER := 1;
2439 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2440 i NUMBER := 0;
2441 BEGIN
2442 OKC_API.init_msg_list(p_init_msg_list);
2443 -- Make sure PL/SQL table has records in it before passing
2444 IF (p_crqv_tbl.COUNT > 0) THEN
2445 i := p_crqv_tbl.FIRST;
2446 LOOP
2447 DECLARE
2448 l_error_rec OKC_API.ERROR_REC_TYPE;
2449 BEGIN
2450 l_error_rec.api_name := l_api_name;
2451 l_error_rec.api_package := G_PKG_NAME;
2452 l_error_rec.idx := i;
2453 update_row (
2454 p_api_version => p_api_version,
2455 p_init_msg_list => OKC_API.G_FALSE,
2456 x_return_status => l_error_rec.error_type,
2457 x_msg_count => l_error_rec.msg_count,
2458 x_msg_data => l_error_rec.msg_data,
2459 p_crqv_rec => p_crqv_tbl(i),
2460 x_crqv_rec => x_crqv_tbl(i));
2461 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2462 l_error_rec.sqlcode := SQLCODE;
2463 load_error_tbl(l_error_rec, px_error_tbl);
2464 ELSE
2465 x_msg_count := l_error_rec.msg_count;
2466 x_msg_data := l_error_rec.msg_data;
2467 END IF;
2468 EXCEPTION
2469 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2470 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2471 l_error_rec.sqlcode := SQLCODE;
2472 load_error_tbl(l_error_rec, px_error_tbl);
2473 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2474 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2475 l_error_rec.sqlcode := SQLCODE;
2476 load_error_tbl(l_error_rec, px_error_tbl);
2477 WHEN OTHERS THEN
2478 l_error_rec.error_type := 'OTHERS';
2479 l_error_rec.sqlcode := SQLCODE;
2480 load_error_tbl(l_error_rec, px_error_tbl);
2481 END;
2482 EXIT WHEN (i = p_crqv_tbl.LAST);
2483 i := p_crqv_tbl.NEXT(i);
2484 END LOOP;
2485 END IF;
2486 -- Loop through the error_tbl to find the error with the highest severity
2487 -- and return it.
2488 x_return_status := find_highest_exception(px_error_tbl);
2489 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2490 EXCEPTION
2491 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2492 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2493 (
2494 l_api_name,
2495 G_PKG_NAME,
2496 'OKC_API.G_RET_STS_ERROR',
2497 x_msg_count,
2498 x_msg_data,
2499 '_PVT'
2500 );
2501 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2502 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2503 (
2504 l_api_name,
2505 G_PKG_NAME,
2506 'OKC_API.G_RET_STS_UNEXP_ERROR',
2507 x_msg_count,
2508 x_msg_data,
2509 '_PVT'
2510 );
2511 WHEN OTHERS THEN
2512 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2513 (
2514 l_api_name,
2515 G_PKG_NAME,
2516 'OTHERS',
2517 x_msg_count,
2518 x_msg_data,
2519 '_PVT'
2520 );
2521 END update_row;
2522
2523 ----------------------------------------
2524 -- PL/SQL TBL update_row for:CRQV_TBL --
2525 ----------------------------------------
2526 PROCEDURE update_row(
2527 p_api_version IN NUMBER,
2528 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2529 x_return_status OUT NOCOPY VARCHAR2,
2530 x_msg_count OUT NOCOPY NUMBER,
2531 x_msg_data OUT NOCOPY VARCHAR2,
2532 p_crqv_tbl IN crqv_tbl_type,
2533 x_crqv_tbl OUT NOCOPY crqv_tbl_type) IS
2534
2535 l_api_version CONSTANT NUMBER := 1;
2536 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2537 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2538 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2539 BEGIN
2540 OKC_API.init_msg_list(p_init_msg_list);
2541 -- Make sure PL/SQL table has records in it before passing
2542 IF (p_crqv_tbl.COUNT > 0) THEN
2543 update_row (
2544 p_api_version => p_api_version,
2545 p_init_msg_list => OKC_API.G_FALSE,
2546 x_return_status => x_return_status,
2547 x_msg_count => x_msg_count,
2548 x_msg_data => x_msg_data,
2549 p_crqv_tbl => p_crqv_tbl,
2550 x_crqv_tbl => x_crqv_tbl,
2551 px_error_tbl => l_error_tbl);
2552 END IF;
2553 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2554 EXCEPTION
2555 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2556 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2557 (
2558 l_api_name,
2559 G_PKG_NAME,
2560 'OKC_API.G_RET_STS_ERROR',
2561 x_msg_count,
2562 x_msg_data,
2563 '_PVT'
2564 );
2565 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2566 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2567 (
2568 l_api_name,
2569 G_PKG_NAME,
2570 'OKC_API.G_RET_STS_UNEXP_ERROR',
2571 x_msg_count,
2572 x_msg_data,
2573 '_PVT'
2574 );
2575 WHEN OTHERS THEN
2576 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2577 (
2578 l_api_name,
2579 G_PKG_NAME,
2580 'OTHERS',
2581 x_msg_count,
2582 x_msg_data,
2583 '_PVT'
2584 );
2585 END update_row;
2586
2587 ---------------------------------------------------------------------------
2588 -- PROCEDURE delete_row
2589 ---------------------------------------------------------------------------
2590 ----------------------------------------
2591 -- delete_row for:OKL_CREDIT_REQUESTS --
2592 ----------------------------------------
2593 PROCEDURE delete_row(
2594 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2595 x_return_status OUT NOCOPY VARCHAR2,
2596 x_msg_count OUT NOCOPY NUMBER,
2597 x_msg_data OUT NOCOPY VARCHAR2,
2598 p_crq_rec IN crq_rec_type) IS
2599
2600 l_api_version CONSTANT NUMBER := 1;
2601 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2602 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2603 l_crq_rec crq_rec_type := p_crq_rec;
2604 l_row_notfound BOOLEAN := TRUE;
2605 BEGIN
2606 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2607 p_init_msg_list,
2608 '_PVT',
2609 x_return_status);
2610 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2611 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2612 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2613 RAISE OKC_API.G_EXCEPTION_ERROR;
2614 END IF;
2615
2616 DELETE FROM OKL_CREDIT_REQUESTS
2617 WHERE ID = p_crq_rec.id;
2618
2619 x_return_status := l_return_status;
2620 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2621 EXCEPTION
2622 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2623 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2624 (
2625 l_api_name,
2626 G_PKG_NAME,
2627 'OKC_API.G_RET_STS_ERROR',
2628 x_msg_count,
2629 x_msg_data,
2630 '_PVT'
2631 );
2632 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2633 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2634 (
2635 l_api_name,
2636 G_PKG_NAME,
2637 'OKC_API.G_RET_STS_UNEXP_ERROR',
2638 x_msg_count,
2639 x_msg_data,
2640 '_PVT'
2641 );
2642 WHEN OTHERS THEN
2643 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2644 (
2645 l_api_name,
2646 G_PKG_NAME,
2647 'OTHERS',
2648 x_msg_count,
2649 x_msg_data,
2650 '_PVT'
2651 );
2652 END delete_row;
2653 ------------------------------------------
2654 -- delete_row for:OKL_CREDIT_REQUESTS_V --
2655 ------------------------------------------
2656 PROCEDURE delete_row(
2657 p_api_version IN NUMBER,
2658 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2659 x_return_status OUT NOCOPY VARCHAR2,
2660 x_msg_count OUT NOCOPY NUMBER,
2661 x_msg_data OUT NOCOPY VARCHAR2,
2662 p_crqv_rec IN crqv_rec_type) IS
2663
2664 l_api_version CONSTANT NUMBER := 1;
2665 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2666 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2667 l_crqv_rec crqv_rec_type := p_crqv_rec;
2668 l_crq_rec crq_rec_type;
2669 BEGIN
2670 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2671 G_PKG_NAME,
2672 p_init_msg_list,
2673 l_api_version,
2674 p_api_version,
2675 '_PVT',
2676 x_return_status);
2677 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2678 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2679 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2680 RAISE OKC_API.G_EXCEPTION_ERROR;
2681 END IF;
2682 -----------------------------------------
2683 -- Move VIEW record to "Child" records --
2684 -----------------------------------------
2685 migrate(l_crqv_rec, l_crq_rec);
2686 -----------------------------------------------
2687 -- Call the DELETE_ROW for each child record --
2688 -----------------------------------------------
2689 delete_row(
2690 p_init_msg_list,
2691 l_return_status,
2692 x_msg_count,
2693 x_msg_data,
2694 l_crq_rec
2695 );
2696 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2697 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2698 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2699 RAISE OKC_API.G_EXCEPTION_ERROR;
2700 END IF;
2701 x_return_status := l_return_status;
2702 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2703 EXCEPTION
2704 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2705 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2706 (
2707 l_api_name,
2708 G_PKG_NAME,
2709 'OKC_API.G_RET_STS_ERROR',
2710 x_msg_count,
2711 x_msg_data,
2712 '_PVT'
2713 );
2714 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2715 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2716 (
2717 l_api_name,
2718 G_PKG_NAME,
2719 'OKC_API.G_RET_STS_UNEXP_ERROR',
2720 x_msg_count,
2721 x_msg_data,
2722 '_PVT'
2723 );
2724 WHEN OTHERS THEN
2725 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2726 (
2727 l_api_name,
2728 G_PKG_NAME,
2729 'OTHERS',
2730 x_msg_count,
2731 x_msg_data,
2732 '_PVT'
2733 );
2734 END delete_row;
2735 -----------------------------------------------------
2736 -- PL/SQL TBL delete_row for:OKL_CREDIT_REQUESTS_V --
2737 -----------------------------------------------------
2738 PROCEDURE delete_row(
2739 p_api_version IN NUMBER,
2740 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2741 x_return_status OUT NOCOPY VARCHAR2,
2742 x_msg_count OUT NOCOPY NUMBER,
2743 x_msg_data OUT NOCOPY VARCHAR2,
2744 p_crqv_tbl IN crqv_tbl_type,
2745 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2746
2747 l_api_version CONSTANT NUMBER := 1;
2748 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2749 i NUMBER := 0;
2750 BEGIN
2751 OKC_API.init_msg_list(p_init_msg_list);
2752 -- Make sure PL/SQL table has records in it before passing
2753 IF (p_crqv_tbl.COUNT > 0) THEN
2754 i := p_crqv_tbl.FIRST;
2755 LOOP
2756 DECLARE
2757 l_error_rec OKC_API.ERROR_REC_TYPE;
2758 BEGIN
2759 l_error_rec.api_name := l_api_name;
2760 l_error_rec.api_package := G_PKG_NAME;
2761 l_error_rec.idx := i;
2762 delete_row (
2763 p_api_version => p_api_version,
2764 p_init_msg_list => OKC_API.G_FALSE,
2765 x_return_status => l_error_rec.error_type,
2766 x_msg_count => l_error_rec.msg_count,
2767 x_msg_data => l_error_rec.msg_data,
2768 p_crqv_rec => p_crqv_tbl(i));
2769 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2770 l_error_rec.sqlcode := SQLCODE;
2771 load_error_tbl(l_error_rec, px_error_tbl);
2772 ELSE
2773 x_msg_count := l_error_rec.msg_count;
2774 x_msg_data := l_error_rec.msg_data;
2775 END IF;
2776 EXCEPTION
2777 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2778 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2779 l_error_rec.sqlcode := SQLCODE;
2780 load_error_tbl(l_error_rec, px_error_tbl);
2781 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2782 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2783 l_error_rec.sqlcode := SQLCODE;
2784 load_error_tbl(l_error_rec, px_error_tbl);
2785 WHEN OTHERS THEN
2786 l_error_rec.error_type := 'OTHERS';
2787 l_error_rec.sqlcode := SQLCODE;
2788 load_error_tbl(l_error_rec, px_error_tbl);
2789 END;
2790 EXIT WHEN (i = p_crqv_tbl.LAST);
2791 i := p_crqv_tbl.NEXT(i);
2792 END LOOP;
2793 END IF;
2794 -- Loop through the error_tbl to find the error with the highest severity
2795 -- and return it.
2796 x_return_status := find_highest_exception(px_error_tbl);
2797 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2798 EXCEPTION
2799 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2800 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2801 (
2802 l_api_name,
2803 G_PKG_NAME,
2804 'OKC_API.G_RET_STS_ERROR',
2805 x_msg_count,
2806 x_msg_data,
2807 '_PVT'
2808 );
2809 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2810 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2811 (
2812 l_api_name,
2813 G_PKG_NAME,
2814 'OKC_API.G_RET_STS_UNEXP_ERROR',
2815 x_msg_count,
2816 x_msg_data,
2817 '_PVT'
2818 );
2819 WHEN OTHERS THEN
2820 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2821 (
2822 l_api_name,
2823 G_PKG_NAME,
2824 'OTHERS',
2825 x_msg_count,
2826 x_msg_data,
2827 '_PVT'
2828 );
2829 END delete_row;
2830
2831 -----------------------------------------------------
2832 -- PL/SQL TBL delete_row for:OKL_CREDIT_REQUESTS_V --
2833 -----------------------------------------------------
2834 PROCEDURE delete_row(
2835 p_api_version IN NUMBER,
2836 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2837 x_return_status OUT NOCOPY VARCHAR2,
2838 x_msg_count OUT NOCOPY NUMBER,
2839 x_msg_data OUT NOCOPY VARCHAR2,
2840 p_crqv_tbl IN crqv_tbl_type) IS
2841
2842 l_api_version CONSTANT NUMBER := 1;
2843 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2844 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2845 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2846 BEGIN
2847 OKC_API.init_msg_list(p_init_msg_list);
2848 -- Make sure PL/SQL table has records in it before passing
2849 IF (p_crqv_tbl.COUNT > 0) THEN
2850 delete_row (
2851 p_api_version => p_api_version,
2852 p_init_msg_list => OKC_API.G_FALSE,
2853 x_return_status => x_return_status,
2854 x_msg_count => x_msg_count,
2855 x_msg_data => x_msg_data,
2856 p_crqv_tbl => p_crqv_tbl,
2857 px_error_tbl => l_error_tbl);
2858 END IF;
2859 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2860 EXCEPTION
2861 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2862 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2863 (
2864 l_api_name,
2865 G_PKG_NAME,
2866 'OKC_API.G_RET_STS_ERROR',
2867 x_msg_count,
2868 x_msg_data,
2869 '_PVT'
2870 );
2871 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2872 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2873 (
2874 l_api_name,
2875 G_PKG_NAME,
2876 'OKC_API.G_RET_STS_UNEXP_ERROR',
2877 x_msg_count,
2878 x_msg_data,
2879 '_PVT'
2880 );
2881 WHEN OTHERS THEN
2882 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2883 (
2884 l_api_name,
2885 G_PKG_NAME,
2886 'OTHERS',
2887 x_msg_count,
2888 x_msg_data,
2889 '_PVT'
2890 );
2891 END delete_row;
2892
2893 END OKL_CRQ_PVT;