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