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