[Home] [Help]
PACKAGE BODY: APPS.OKL_TAI_PVT
Source
1 PACKAGE BODY Okl_Tai_Pvt AS
2 /* $Header: OKLSTAIB.pls 120.16 2008/05/21 00:45:26 sechawla noship $ */
3
4 -----------Start addition, Sunil T. Mathew (04/16/2001)
5 ---------------------------------------------------------------------------
6 -- PROCEDURE validate_id
7 ---------------------------------------------------------------------------
8 PROCEDURE validate_id (p_taiv_rec IN taiv_rec_type,
9 x_return_status OUT NOCOPY VARCHAR2) IS
10 BEGIN
11 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
12
13 IF p_taiv_rec.id = Okl_Api.G_MISS_NUM OR
14 p_taiv_rec.id IS NULL
15 THEN
16 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
17 x_return_status := Okl_Api.G_RET_STS_ERROR;
18 --set error message in message stack
19 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
20 p_msg_name => G_REQUIRED_VALUE,
21 p_token1 => G_COL_NAME_TOKEN,
22 p_token1_value => 'id');
23 RAISE G_EXCEPTION_HALT_VALIDATION;
24
25 END IF;
26
27 END validate_id;
28
29 ---------------------------------------------------------------------------
30 -- PROCEDURE validate_org_id
31 ---------------------------------------------------------------------------
32 PROCEDURE validate_org_id (p_taiv_rec IN taiv_rec_type,
33 x_return_status OUT NOCOPY VARCHAR2) IS
34 BEGIN
35 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
36 IF (p_taiv_rec.org_id IS NOT NULL) THEN
37 x_return_status := Okl_Util.check_org_id(p_taiv_rec.org_id);
38 END IF;
39 END validate_org_id;
40
41 -- for LE Uptake project 08-11-2006
42 ---------------------------------------------------------------------------
43 -- PROCEDURE validate_legal_entity_id
44 ---------------------------------------------------------------------------
45 PROCEDURE validate_legal_entity_id (p_taiv_rec IN taiv_rec_type,
46 x_return_status OUT NOCOPY VARCHAR2) IS
47 l_exists NUMBER(1);
48 item_not_found_error EXCEPTION;
49 BEGIN
50 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
51 IF (p_taiv_rec.legal_entity_id IS NOT NULL) THEN
52 l_exists := OKL_LEGAL_ENTITY_UTIL.check_le_id_exists(p_taiv_rec.legal_entity_id);
53 IF(l_exists <> 1) THEN
54 Okc_Api.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'LEGAL_ENTITY_ID');
55 RAISE item_not_found_error;
56 END IF;
57 END IF;
58 EXCEPTION
59 WHEN item_not_found_error THEN
60 x_return_status := Okc_Api.G_RET_STS_ERROR;
61
62 WHEN OTHERS THEN
63 -- store SQL error message on message stack for caller
64 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
65 ,p_msg_name => g_unexpected_error
66 ,p_token1 => g_sqlcode_token
67 ,p_token1_value =>SQLCODE
68 ,p_token2 => g_sqlerrm_token
69 ,p_token2_value =>SQLERRM);
70
71 -- notify caller of an UNEXPECTED error
72 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
73 END validate_legal_entity_id;
74 -- for LE Uptake project 08-11-2006
75
76 ---------------------------------------------------------------------------
77 -- PROCEDURE validate_amount
78 ---------------------------------------------------------------------------
79 PROCEDURE validate_amount (p_taiv_rec IN taiv_rec_type,
80 x_return_status OUT NOCOPY VARCHAR2) IS
81 BEGIN
82 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
83
84 IF p_taiv_rec.amount = Okl_Api.G_MISS_NUM OR
85 p_taiv_rec.amount IS NULL
86 THEN
87
88 x_return_status := Okl_Api.G_RET_STS_ERROR;
89 --set error message in message stack
90 Okl_Api.SET_MESSAGE(p_app_name => 'OKC',
91 p_msg_name => G_REQUIRED_VALUE,
92 p_token1 => G_COL_NAME_TOKEN,
93 p_token1_value => 'Amount');
94 RAISE G_EXCEPTION_HALT_VALIDATION;
95
96 END IF;
97
98 END validate_amount;
99 ---------------------------------------------------------------------------
100 -- PROCEDURE validate_date_invoiced
101 ---------------------------------------------------------------------------
102 PROCEDURE validate_date_invoiced (p_taiv_rec IN taiv_rec_type,
103 x_return_status OUT NOCOPY VARCHAR2) IS
104 BEGIN
105 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
106
107 IF p_taiv_rec.date_invoiced = Okl_Api.G_MISS_DATE OR
108 p_taiv_rec.date_invoiced IS NULL
109 THEN
110
111 x_return_status := Okl_Api.G_RET_STS_ERROR;
112 --set error message in message stack
113 Okl_Api.SET_MESSAGE(p_app_name => 'OKC',
114 p_msg_name => G_REQUIRED_VALUE,
115 p_token1 => G_COL_NAME_TOKEN,
116 p_token1_value => 'Invoice Date');
117 RAISE G_EXCEPTION_HALT_VALIDATION;
118
119 END IF;
120
121 END validate_date_invoiced;
122
123 ---------------------------------------------------------------------------
124 -- PROCEDURE validate_date_entered
125 ---------------------------------------------------------------------------
126 PROCEDURE validate_date_entered (p_taiv_rec IN taiv_rec_type,
127 x_return_status OUT NOCOPY VARCHAR2) IS
128 BEGIN
129 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
130
131 IF p_taiv_rec.date_entered = Okl_Api.G_MISS_DATE OR
132 p_taiv_rec.date_entered IS NULL
133 THEN
134 x_return_status := Okl_Api.G_RET_STS_ERROR;
135
136 --set error message in message stack
137 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
138 p_msg_name => G_REQUIRED_VALUE,
139 p_token1 => G_COL_NAME_TOKEN,
140 p_token1_value => 'date_entered');
141 RAISE G_EXCEPTION_HALT_VALIDATION;
142 END IF;
143
144 END validate_date_entered;
145
146 ---------------------------------------------------------------------------
147 -- PROCEDURE validate_object_version_number
148 ---------------------------------------------------------------------------
149 PROCEDURE validate_object_version_number (p_taiv_rec IN taiv_rec_type,
150 x_return_status OUT NOCOPY VARCHAR2) IS
151 BEGIN
152 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
153
154 IF p_taiv_rec.object_version_number = Okl_Api.G_MISS_NUM OR
155 p_taiv_rec.object_version_number IS NULL
156 THEN
157
158 x_return_status := Okl_Api.G_RET_STS_ERROR;
159
160 --set error message in message stack
161 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
162 p_msg_name => G_REQUIRED_VALUE,
163 p_token1 => G_COL_NAME_TOKEN,
164 p_token1_value => 'object_version_number');
165 RAISE G_EXCEPTION_HALT_VALIDATION;
166 END IF;
167
168 END validate_object_version_number;
169
170 ---------------------------------------------------------------------------
171 -- PROCEDURE validate_currency_code
172 ---------------------------------------------------------------------------
173 PROCEDURE validate_currency_code (p_taiv_rec IN taiv_rec_type,
174 x_return_status OUT NOCOPY VARCHAR2) IS
175 l_dummy_var VARCHAR2(1) := '0';
176
177 CURSOR l_currency_code_csr IS
178 SELECT '1'
179 FROM fnd_currencies
180 WHERE currency_code = p_taiv_rec.currency_code;
181
182 BEGIN
183 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
184
185 --Check Not Null
186 /* 10-SEP-2001 R.Draguilev
187 The field can be null (bug 1980827)
188 The value can be determined using contract id (khr_id) and contract rules)
189
190 IF (p_taiv_rec.currency_code IS NULL) OR (p_taiv_rec.currency_code=Okl_Api.G_MISS_NUM) THEN
191 x_return_status:=Okl_Api.G_RET_STS_ERROR;
192 --set error message in message stack
193 Okl_Api.SET_MESSAGE (
194 p_app_name => G_APP_NAME,
195 p_msg_name => G_REQUIRED_VALUE,
196 p_token1 => G_COL_NAME_TOKEN,
197 p_token1_value => 'CURRENCY_CODE_ID');
198 RAISE G_EXCEPTION_HALT_VALIDATION;
199 END IF;
200 */
201
202 --Check FK column
203 IF (p_taiv_rec.currency_code IS NOT NULL) THEN
204
205 OPEN l_currency_code_csr;
206 FETCH l_currency_code_csr INTO l_dummy_var;
207 CLOSE l_currency_code_csr;
208
209 IF (l_dummy_var <> '1') THEN
210 x_return_status := Okl_Api.G_RET_STS_ERROR;
211 Okl_Api.SET_MESSAGE(
212 p_app_name => G_APP_NAME,
213 p_msg_name => G_NO_PARENT_RECORD,
214 p_token1 => G_COL_NAME_TOKEN,
215 p_token1_value => 'CURRENCY_CODE_FOR',
216 p_token2 => G_CHILD_TABLE_TOKEN,
217 p_token2_value => G_VIEW,
218 p_token3 => G_PARENT_TABLE_TOKEN,
219 p_token3_value => 'OKL_TRX_AR_INVOICES_V');
220
221 RAISE G_EXCEPTION_HALT_VALIDATION;
222 END IF;
223
224 END IF;
225
226 END validate_currency_code;
227
228 --pjgome 11/18/2002 added procedure validate_curr_conv_type
229 ---------------------------------------------------------------------------
230 -- PROCEDURE validate_curr_conv_type
231 ---------------------------------------------------------------------------
232 PROCEDURE validate_curr_conv_type (p_taiv_rec IN taiv_rec_type,
233 x_return_status OUT NOCOPY VARCHAR2) IS
234 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
235 BEGIN
236 --Check FK column
237 IF (p_taiv_rec.currency_conversion_type IS NOT NULL) THEN
238 --uncomment out the below line of code when currency conversion lookup type is finalized
239 --l_return_status := Okl_Util.CHECK_LOOKUP_CODE(--insert the lookup type ,p_taiv_rec.currency_conversion_type);
240
241 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
242 Okl_Api.SET_MESSAGE(
243 p_app_name => G_APP_NAME,
244 p_msg_name => G_NO_PARENT_RECORD,
245 p_token1 => G_COL_NAME_TOKEN,
246 p_token1_value => 'CURRENCY_CONVERSION_TYPE',
247 p_token2 => G_CHILD_TABLE_TOKEN,
248 p_token2_value => G_VIEW,
249 p_token3 => G_PARENT_TABLE_TOKEN,
250 p_token3_value => 'FND_LOOKUPS');
251 END IF;
252
253 END IF;
254 x_return_status := l_return_status;
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
259 ,p_msg_name => G_UNEXPECTED_ERROR
260 ,p_token1 => G_SQLCODE_TOKEN
261 ,p_token1_value => SQLCODE
262 ,p_token2 => G_SQLERRM_TOKEN
263 ,p_token2_value => SQLERRM);
264 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
265 END validate_curr_conv_type;
266
267 ---------------------------------------------------------------------------
268 -- PROCEDURE validate_ibt_id
269 ---------------------------------------------------------------------------
270 /*****************************************comment out because the okx_bill_tos_v does not exist **
271 PROCEDURE validate_ibt_id (p_taiv_rec IN taiv_rec_type,
272 x_return_status OUT NOCOPY VARCHAR2) IS
273 l_dummy_var VARCHAR2(1) := '0';
274
275 CURSOR l_ibt_id_csr IS
276 SELECT '1'
277 FROM OKX_BILL_TOS_V
278 WHERE id = p_taiv_rec.ibt_id;
279
280 BEGIN
281 x_return_status := Okl_api.G_RET_STS_SUCCESS;
282
283 --Check not null
284 */
285 /* 10-SEP-2001 R.Draguilev
286 The field can be null (bug 1980827)
287 The value can be determined using contract id (khr_id) and contract rules)
288
289 IF (p_taiv_rec.ibt_id IS NULL) OR (p_taiv_rec.ibt_id=Okl_api.G_MISS_NUM) THEN
290 x_return_status:=Okl_api.G_RET_STS_ERROR;
291 --set error message in message stack
292 Okl_api.SET_MESSAGE (
293 p_app_name => G_APP_NAME,
294 p_msg_name => G_REQUIRED_VALUE,
295 p_token1 => G_COL_NAME_TOKEN,
296 p_token1_value => 'IBT_ID');
297 RAISE G_EXCEPTION_HALT_VALIDATION;
298 END IF;
299 */
300 /*
301 --Check FK
302 IF (p_taiv_rec.ibt_id IS NOT NULL) THEN
303
304 OPEN l_ibt_id_csr;
305 FETCH l_ibt_id_csr INTO l_dummy_var;
306 CLOSE l_ibt_id_csr;
307
308 IF (l_dummy_var <> '1') then
309 x_return_status := Okl_api.G_RET_STS_ERROR;
310 Okl_api.SET_MESSAGE(
311 p_app_name => G_APP_NAME,
312 p_msg_name => G_NO_PARENT_RECORD,
313 p_token1 => G_COL_NAME_TOKEN,
314 p_token1_value => 'IBT_ID_FOR',
315 p_token2 => G_CHILD_TABLE_TOKEN,
316 p_token2_value => G_VIEW,
317 p_token3 => G_PARENT_TABLE_TOKEN,
318 p_token3_value => 'OKL_TRX_AR_INVOICES_V');
319
320 RAISE G_EXCEPTION_HALT_VALIDATION;
321 END IF;
322
323 END IF;
324
325 END validate_ibt_id;
326 *********************** End Commented code *********/
327
328 /*****************************************comment out because the okx_cstr_accts_v does not exist **
329 ---------------------------------------------------------------------------
330 -- PROCEDURE validate_ixx_id
331 ---------------------------------------------------------------------------
332 PROCEDURE validate_ixx_id (p_taiv_rec IN taiv_rec_type,
333 x_return_status OUT NOCOPY VARCHAR2) IS
334 l_dummy_var VARCHAR2(1) := '0';
335
336 CURSOR l_ixx_id_csr IS
337 SELECT '1'
338 FROM okx_cstr_accts_v
339 WHERE id = p_taiv_rec.ixx_id;
340
341 BEGIN
342 x_return_status := Okl_api.G_RET_STS_SUCCESS;
343
344 --Check not null
345 */
346 /* 10-SEP-2001 R.Draguilev
347 The field can be null (bug 1980827)
348 The value can be determined using contract id (khr_id) and contract rules)
349
350 IF (p_taiv_rec.ixx_id IS NULL) OR (p_taiv_rec.ixx_id=Okl_api.G_MISS_NUM) THEN
351 x_return_status:=Okl_api.G_RET_STS_ERROR;
352 --set error message in message stack
353 Okl_api.SET_MESSAGE (
354 p_app_name => G_APP_NAME,
355 p_msg_name => G_REQUIRED_VALUE,
356 p_token1 => G_COL_NAME_TOKEN,
357 p_token1_value => 'IXX_ID');
358 RAISE G_EXCEPTION_HALT_VALIDATION;
359 END IF;
360 */
361 /*
362 --Check FK
363 IF (p_taiv_rec.ixx_id IS NOT NULL) THEN
364
365 OPEN l_ixx_id_csr;
366 FETCH l_ixx_id_csr INTO l_dummy_var;
367 CLOSE l_ixx_id_csr;
368
369 IF (l_dummy_var <> '1') then
370 x_return_status := Okl_api.G_RET_STS_ERROR;
371 Okl_api.SET_MESSAGE(
372 p_app_name => G_APP_NAME,
373 p_msg_name => G_NO_PARENT_RECORD,
374 p_token1 => G_COL_NAME_TOKEN,
375 p_token1_value => 'IXX_ID_FOR',
376 p_token2 => G_CHILD_TABLE_TOKEN,
377 p_token2_value => G_VIEW,
378 p_token3 => G_PARENT_TABLE_TOKEN,
379 p_token3_value => 'OKL_TRX_AR_INVOICES_V');
380
381 RAISE G_EXCEPTION_HALT_VALIDATION;
382 END IF;
383
384 END IF;
385
386 END validate_ixx_id;
387 *********************** End Commented code *********/
388
389 ---------------------------------------------------------------------------
390 -- PROCEDURE validate_khr_id
391 ---------------------------------------------------------------------------
392 PROCEDURE validate_khr_id (p_taiv_rec IN taiv_rec_type,
393 x_return_status OUT NOCOPY VARCHAR2) IS
394 l_dummy_var VARCHAR2(1) := '0';
395
396 CURSOR l_khr_id_csr IS
397 SELECT '1'
398 FROM OKL_K_HEADERS_V
399 WHERE id = p_taiv_rec.khr_id;
400
401 BEGIN
402 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
403
404 IF (p_taiv_rec.khr_id IS NOT NULL) THEN
405 OPEN l_khr_id_csr;
406 FETCH l_khr_id_csr INTO l_dummy_var;
407 CLOSE l_khr_id_csr;
408
409 IF (l_dummy_var <> '1') THEN
410 x_return_status := Okl_Api.G_RET_STS_ERROR;
411 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
412 p_msg_name => G_NO_PARENT_RECORD,
413 p_token1 => G_COL_NAME_TOKEN,
414 p_token1_value => 'KHR_ID_FOR',
415 p_token2 => G_CHILD_TABLE_TOKEN,
416 p_token2_value => G_VIEW,
417 p_token3 => G_PARENT_TABLE_TOKEN,
418 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
419
420 RAISE G_EXCEPTION_HALT_VALIDATION;
421 END IF;
422 END IF;
423
424 END validate_khr_id;
425
426 /*****************************************comment out because the okx_receipt_methods_v does not exist **
427 ---------------------------------------------------------------------------
428 -- PROCEDURE validate_irm_id
429 ---------------------------------------------------------------------------
430 PROCEDURE validate_irm_id (p_taiv_rec IN taiv_rec_type,
431 x_return_status OUT NOCOPY VARCHAR2) IS
432 l_dummy_var VARCHAR2(1) := '0';
433
434 CURSOR l_irm_id_csr IS
435 SELECT '1'
436 FROM okx_receipt_methods_v
437 WHERE id = p_taiv_rec.irm_id;
438
439 BEGIN
440 x_return_status := Okl_api.G_RET_STS_SUCCESS;
441
442 IF (p_taiv_rec.irm_id is not null) then
443 OPEN l_irm_id_csr;
444 FETCH l_irm_id_csr INTO l_dummy_var;
445 CLOSE l_irm_id_csr;
446
447 IF (l_dummy_var <> '1') then
448 x_return_status := Okl_api.G_RET_STS_ERROR;
449 Okl_api.SET_MESSAGE(p_app_name => G_APP_NAME,
450 p_msg_name => G_NO_PARENT_RECORD,
451 p_token1 => G_COL_NAME_TOKEN,
452 p_token1_value => 'IRM_ID_FOR',
453 p_token2 => G_CHILD_TABLE_TOKEN,
454 p_token2_value => G_VIEW,
455 p_token3 => G_PARENT_TABLE_TOKEN,
456 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
457
458 RAISE G_EXCEPTION_HALT_VALIDATION;
459 END IF;
460 END IF;
461
462 END validate_irm_id;
463 *********************** End Commented code *********/
464
465 /*****************************************comment out because the okx_receipt_methods_v does not exist **
466 ---------------------------------------------------------------------------
467 -- PROCEDURE validate_irt_id
468 ---------------------------------------------------------------------------
469 PROCEDURE validate_irt_id (p_taiv_rec IN taiv_rec_type,
470 x_return_status OUT NOCOPY VARCHAR2) IS
471 l_dummy_var VARCHAR2(1) := '0';
472
473 CURSOR l_irt_id_csr IS
474 SELECT '1'
475 FROM okx_ra_termses_v
476 WHERE id = p_taiv_rec.irt_id;
477
478 BEGIN
479 x_return_status := Okl_api.G_RET_STS_SUCCESS;
480
481 IF (p_taiv_rec.irt_id is not null) then
482 OPEN l_irt_id_csr;
483 FETCH l_irt_id_csr INTO l_dummy_var;
484 CLOSE l_irt_id_csr;
485
486 IF (l_dummy_var <> '1') then
487 x_return_status := Okl_api.G_RET_STS_ERROR;
488 Okl_api.SET_MESSAGE(p_app_name => G_APP_NAME,
489 p_msg_name => G_NO_PARENT_RECORD,
490 p_token1 => G_COL_NAME_TOKEN,
491 p_token1_value => 'IRT_ID_FOR',
492 p_token2 => G_CHILD_TABLE_TOKEN,
493 p_token2_value => G_VIEW,
494 p_token3 => G_PARENT_TABLE_TOKEN,
495 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
496
497 RAISE G_EXCEPTION_HALT_VALIDATION;
498 END IF;
499 END IF;
500
501 END validate_irt_id;
502 *********************** End Commented code *********/
503
504 ---------------------------------------------------------------------------
505 -- PROCEDURE validate_cra_id
506 ---------------------------------------------------------------------------
507 PROCEDURE validate_cra_id (p_taiv_rec IN taiv_rec_type,
508 x_return_status OUT NOCOPY VARCHAR2) IS
509 l_dummy_var VARCHAR2(1) := '0';
510
511 CURSOR l_cra_id_csr IS
512 SELECT '1'
513 FROM dual;
514 -- OKL_CURE_REP_AMTS_B
515 -- WHERE id = p_taiv_rec.cra_id;
516
517 BEGIN
518 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
519
520 IF (p_taiv_rec.cra_id IS NOT NULL) THEN
521 OPEN l_cra_id_csr;
522 FETCH l_cra_id_csr INTO l_dummy_var;
523 CLOSE l_cra_id_csr;
524
525 IF (l_dummy_var <> '1') THEN
526 x_return_status := Okl_Api.G_RET_STS_ERROR;
527 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
528 p_msg_name => G_NO_PARENT_RECORD,
529 p_token1 => G_COL_NAME_TOKEN,
530 p_token1_value => 'CRA_ID_FOR',
531 p_token2 => G_CHILD_TABLE_TOKEN,
532 p_token2_value => G_VIEW,
533 p_token3 => G_PARENT_TABLE_TOKEN,
534 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
535
536 RAISE G_EXCEPTION_HALT_VALIDATION;
537 END IF;
538 END IF;
539
540 END validate_cra_id;
541
542
543 ---------------------------------------------------------------------------
544 -- PROCEDURE validate_svf_id
545 ---------------------------------------------------------------------------
546 PROCEDURE validate_svf_id (p_taiv_rec IN taiv_rec_type,
547 x_return_status OUT NOCOPY VARCHAR2) IS
548 l_dummy_var VARCHAR2(1) := '0';
549
550 CURSOR l_svf_id_csr IS
551 SELECT '1'
552 FROM OKL_SERVICE_FEES_B
553 WHERE id = p_taiv_rec.svf_id;
554
555 BEGIN
556 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
557
558 --Check FK
559 IF (p_taiv_rec.svf_id IS NOT NULL) THEN
560
561 OPEN l_svf_id_csr;
562 FETCH l_svf_id_csr INTO l_dummy_var;
563 CLOSE l_svf_id_csr;
564
565 IF (l_dummy_var <> '1') THEN
566 x_return_status := Okl_Api.G_RET_STS_ERROR;
567 Okl_Api.SET_MESSAGE(
568 p_app_name => G_APP_NAME,
569 p_msg_name => G_NO_PARENT_RECORD,
570 p_token1 => G_COL_NAME_TOKEN,
571 p_token1_value => 'SVF_ID_FOR',
572 p_token2 => G_CHILD_TABLE_TOKEN,
573 p_token2_value => G_VIEW,
574 p_token3 => G_PARENT_TABLE_TOKEN,
575 p_token3_value => 'OKL_TRX_AR_INVOICES_V');
576
577 RAISE G_EXCEPTION_HALT_VALIDATION;
578 END IF;
579
580 END IF;
581
582 END validate_svf_id;
583
584 ---------------------------------------------------------------------------
585 -- PROCEDURE validate_tap_id
586 ---------------------------------------------------------------------------
587 PROCEDURE validate_tap_id (p_taiv_rec IN taiv_rec_type,
588 x_return_status OUT NOCOPY VARCHAR2) IS
589 l_dummy_var VARCHAR2(1) := '0';
590
591 CURSOR l_tap_id_csr IS
592 SELECT '1'
593 FROM OKL_TRX_AP_INVOICES_B
594 WHERE id = p_taiv_rec.tap_id;
595
596 BEGIN
597 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
598
599 IF (p_taiv_rec.tap_id IS NOT NULL) THEN
600 OPEN l_tap_id_csr;
601 FETCH l_tap_id_csr INTO l_dummy_var;
602 CLOSE l_tap_id_csr;
603
604 IF (l_dummy_var <> '1') THEN
605 x_return_status := Okl_Api.G_RET_STS_ERROR;
606 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
607 p_msg_name => G_NO_PARENT_RECORD,
608 p_token1 => G_COL_NAME_TOKEN,
609 p_token1_value => 'TAP_ID_FOR',
610 p_token2 => G_CHILD_TABLE_TOKEN,
611 p_token2_value => G_VIEW,
612 p_token3 => G_PARENT_TABLE_TOKEN,
613 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
614
615 RAISE G_EXCEPTION_HALT_VALIDATION;
616 END IF;
617 END IF;
618
619 END validate_tap_id;
620
621 ---------------------------------------------------------------------------
622 -- PROCEDURE validate_qte_id
623 ---------------------------------------------------------------------------
624 PROCEDURE validate_qte_id (p_taiv_rec IN taiv_rec_type,
625 x_return_status OUT NOCOPY VARCHAR2) IS
626 l_dummy_var VARCHAR2(1) := '0';
627
628 CURSOR l_qte_id_csr IS
629 SELECT '1'
630 FROM OKL_TRX_QUOTES_B
631 WHERE id = p_taiv_rec.qte_id;
632
633 BEGIN
634 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
635
636 IF (p_taiv_rec.qte_id IS NOT NULL) THEN
637 OPEN l_qte_id_csr;
638 FETCH l_qte_id_csr INTO l_dummy_var;
639 CLOSE l_qte_id_csr;
640
641 IF (l_dummy_var <> '1') THEN
642 x_return_status := Okl_Api.G_RET_STS_ERROR;
643 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
644 p_msg_name => G_NO_PARENT_RECORD,
645 p_token1 => G_COL_NAME_TOKEN,
646 p_token1_value => 'QTE_ID_FOR',
647 p_token2 => G_CHILD_TABLE_TOKEN,
648 p_token2_value => G_VIEW,
649 p_token3 => G_PARENT_TABLE_TOKEN,
650 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
651
652 RAISE G_EXCEPTION_HALT_VALIDATION;
653 END IF;
654 END IF;
655
656 END validate_qte_id;
657
658 ---------------------------------------------------------------------------
659 -- PROCEDURE validate_tcn_id
660 ---------------------------------------------------------------------------
661 PROCEDURE validate_tcn_id (p_taiv_rec IN taiv_rec_type,
662 x_return_status OUT NOCOPY VARCHAR2) IS
663 l_dummy_var VARCHAR2(1) := '0';
664
665 CURSOR l_tcn_id_csr IS
666 SELECT '1'
667 FROM OKL_TRX_CONTRACTS
668 WHERE id = p_taiv_rec.tcn_id;
669
670 BEGIN
671 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
672
673 IF (p_taiv_rec.tcn_id IS NOT NULL) THEN
674 OPEN l_tcn_id_csr;
675 FETCH l_tcn_id_csr INTO l_dummy_var;
676 CLOSE l_tcn_id_csr;
677
678 IF (l_dummy_var <> '1') THEN
679 x_return_status := Okl_Api.G_RET_STS_ERROR;
680 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
681 p_msg_name => G_NO_PARENT_RECORD,
682 p_token1 => G_COL_NAME_TOKEN,
683 p_token1_value => 'TCN_ID_FOR',
684 p_token2 => G_CHILD_TABLE_TOKEN,
685 p_token2_value => G_VIEW,
686 p_token3 => G_PARENT_TABLE_TOKEN,
687 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
688
689 RAISE G_EXCEPTION_HALT_VALIDATION;
690 END IF;
691 END IF;
692
693 END validate_tcn_id;
694
695
696 ---------------------------------------------------------------------------
697 -- PROCEDURE validate_tai_id_reverses
698 ---------------------------------------------------------------------------
699 PROCEDURE validate_tai_id_reverses (p_taiv_rec IN taiv_rec_type,
700 x_return_status OUT NOCOPY VARCHAR2) IS
701 l_dummy_var VARCHAR2(1) := '0';
702
703 CURSOR l_tai_id_reverses_csr IS
704 SELECT '1'
705 FROM OKL_TRX_AR_INVOICES_B
706 WHERE id = p_taiv_rec.tai_id_reverses;
707
708 BEGIN
709 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
710
711 IF (p_taiv_rec.tai_id_reverses IS NOT NULL) THEN
712 OPEN l_tai_id_reverses_csr;
713 FETCH l_tai_id_reverses_csr INTO l_dummy_var;
714 CLOSE l_tai_id_reverses_csr;
715
716 IF (l_dummy_var <> '1') THEN
717 x_return_status := Okl_Api.G_RET_STS_ERROR;
718 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
719 p_msg_name => G_NO_PARENT_RECORD,
720 p_token1 => G_COL_NAME_TOKEN,
721 p_token1_value => 'TAI_ID_REVERSES_FOR',
722 p_token2 => G_CHILD_TABLE_TOKEN,
723 p_token2_value => G_VIEW,
724 p_token3 => G_PARENT_TABLE_TOKEN,
725 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
726
727 RAISE G_EXCEPTION_HALT_VALIDATION;
728 END IF;
729 END IF;
730
731 END validate_tai_id_reverses;
732
733 ---------------------------------------------------------------------------
734 -- PROCEDURE validate_ipy_id
735 ---------------------------------------------------------------------------
736 PROCEDURE validate_ipy_id (p_taiv_rec IN taiv_rec_type,
737 x_return_status OUT NOCOPY VARCHAR2) IS
738 l_dummy_var VARCHAR2(1) := '0';
739
740 CURSOR l_ipy_id_csr IS
741 SELECT '1'
742 FROM OKL_INS_POLICIES_V
743 WHERE id = p_taiv_rec.ipy_id;
744
745 BEGIN
746 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
747
748 IF (p_taiv_rec.ipy_id IS NOT NULL) THEN
749 OPEN l_ipy_id_csr;
750 FETCH l_ipy_id_csr INTO l_dummy_var;
751 CLOSE l_ipy_id_csr;
752
753 IF (l_dummy_var <> '1') THEN
754 x_return_status := Okl_Api.G_RET_STS_ERROR;
755 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
756 p_msg_name => G_NO_PARENT_RECORD,
757 p_token1 => G_COL_NAME_TOKEN,
758 p_token1_value => 'IPY_ID_FOR',
759 p_token2 => G_CHILD_TABLE_TOKEN,
760 p_token2_value => G_VIEW,
761 p_token3 => G_PARENT_TABLE_TOKEN,
762 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
763
764 RAISE G_EXCEPTION_HALT_VALIDATION;
765 END IF;
766 END IF;
767
768 END validate_ipy_id;
769
770 ---------------------------------------------------------------------------
771 -- PROCEDURE validate_trx_status_code
772 ---------------------------------------------------------------------------
773 PROCEDURE validate_trx_status_code (p_taiv_rec IN taiv_rec_type,
774 x_return_status OUT NOCOPY VARCHAR2) IS
775
776 BEGIN
777
778 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
779
780 IF p_taiv_rec.trx_status_code = Okl_Api.G_MISS_CHAR OR
781 p_taiv_rec.trx_status_code IS NULL
782 THEN
783 x_return_status := Okl_Api.G_RET_STS_ERROR;
784 --set error message in message stack
785 Okl_Api.SET_MESSAGE(
786 p_app_name => G_APP_NAME,
787 p_msg_name => G_REQUIRED_VALUE,
788 p_token1 => G_COL_NAME_TOKEN,
789 p_token1_value => 'trx_status_code');
790 RAISE G_EXCEPTION_HALT_VALIDATION;
791 END IF;
792
793 x_return_status := Okl_Util.CHECK_LOOKUP_CODE('OKL_TRANSACTION_STATUS',p_taiv_rec.trx_status_code);
794
795 END validate_trx_status_code;
796
797 -- sjalasut: added new procedure validate_trx_number to validate that the transaction number
798 -- entered on the manual bills page is unique for the operating unit
799 -- sjalasut: start of code changes
800 ---------------------------------------------------------------------------
801 -- PROCEDURE validate_trx_number
802 ---------------------------------------------------------------------------
803 PROCEDURE validate_trx_number (p_taiv_rec IN taiv_rec_type,
804 x_return_status OUT NOCOPY VARCHAR2) IS
805 -- this cursor is performant. but the requirement here is to check for a transaction number
806 -- without any context. i.e. for the current operating unit
807 CURSOR l_trx_number_uniq_csr is
808 SELECT '1'
809 FROM OKL_TRX_AR_INVOICES_V INV
810 WHERE ID <> p_taiv_rec.id
811 AND TRX_NUMBER IS NOT NULL
812 AND TRX_NUMBER = p_taiv_rec.trx_number;
813
814 lv_dummy_var VARCHAR2(1) DEFAULT '0';
815
816 BEGIN
817 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
818 IF(p_taiv_rec.trx_number IS NOT NULL )THEN
819 OPEN l_trx_number_uniq_csr;
820 FETCH l_trx_number_uniq_csr INTO lv_dummy_var;
821 CLOSE l_trx_number_uniq_csr;
822 IF(lv_dummy_var = '1')THEN
823 x_return_status := Okl_Api.G_RET_STS_ERROR;
824 --set error message in message stack
825 Okl_Api.SET_MESSAGE(
826 p_app_name => G_APP_NAME,
827 p_msg_name => 'OKL_SIR_NOT_UNIQUE'
828 );
829 RAISE G_EXCEPTION_HALT_VALIDATION;
830 END IF;
831 END IF;
832 END validate_trx_number;
833 -- sjalasut: end of code changes
834
835
836 ---------------------------------------------------------------------------
837 -- PROCEDURE validate_set_of_books_id
838 ---------------------------------------------------------------------------
839 PROCEDURE validate_set_of_books_id (p_taiv_rec IN taiv_rec_type,
840 x_return_status OUT NOCOPY VARCHAR2) IS
841 l_dummy_var VARCHAR2(1) := '0';
842
843 CURSOR l_set_of_books_id_csr IS
844 SELECT '1'
845 FROM GL_LEDGERS_PUBLIC_V
846 WHERE ledger_id = p_taiv_rec.set_of_books_id;
847
848 BEGIN
849 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
850
851 IF (p_taiv_rec.set_of_books_id IS NOT NULL) THEN
852 OPEN l_set_of_books_id_csr;
853 FETCH l_set_of_books_id_csr INTO l_dummy_var;
854 CLOSE l_set_of_books_id_csr;
855
856 IF (l_dummy_var <> '1') THEN
857 x_return_status := Okl_Api.G_RET_STS_ERROR;
858 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
859 p_msg_name => G_NO_PARENT_RECORD,
860 p_token1 => G_COL_NAME_TOKEN,
861 p_token1_value => 'SET_OF_BOOKS_ID_FOR',
862 p_token2 => G_CHILD_TABLE_TOKEN,
863 p_token2_value => G_VIEW,
864 p_token3 => G_PARENT_TABLE_TOKEN,
865 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
866
867 RAISE G_EXCEPTION_HALT_VALIDATION;
868 END IF;
869 END IF;
870
871 END validate_set_of_books_id;
872
873 ---------------------------------------------------------------------------
874 -- PROCEDURE validate_try_id
875 ---------------------------------------------------------------------------
876 PROCEDURE validate_try_id (p_taiv_rec IN taiv_rec_type,
877 x_return_status OUT NOCOPY VARCHAR2) IS
878 l_dummy_var VARCHAR2(1) := '0';
879
880 CURSOR l_try_id_csr IS
881 SELECT '1'
882 FROM OKL_TRX_TYPES_V
883 WHERE id = p_taiv_rec.try_id;
884
885 BEGIN
886 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
887
888 IF p_taiv_rec.try_id = Okl_Api.G_MISS_NUM OR
889 p_taiv_rec.try_id IS NULL
890 THEN
891
892 x_return_status := Okl_Api.G_RET_STS_ERROR;
893 --set error message in message stack
894 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
895 p_msg_name => G_REQUIRED_VALUE,
896 p_token1 => G_COL_NAME_TOKEN,
897 p_token1_value => 'try_id');
898 RAISE G_EXCEPTION_HALT_VALIDATION;
899
900 END IF;
901
902
903 IF (p_taiv_rec.try_id IS NOT NULL) THEN
904 OPEN l_try_id_csr;
905 FETCH l_try_id_csr INTO l_dummy_var;
906 CLOSE l_try_id_csr;
907
908 IF (l_dummy_var <> '1') THEN
909 x_return_status := Okl_Api.G_RET_STS_ERROR;
910 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
911 p_msg_name => G_NO_PARENT_RECORD,
912 p_token1 => G_COL_NAME_TOKEN,
913 p_token1_value => 'TRY_ID_FOR',
914 p_token2 => G_CHILD_TABLE_TOKEN,
915 p_token2_value => G_VIEW,
916 p_token3 => G_PARENT_TABLE_TOKEN,
917 p_token3_value => 'OKL_TRX_AR_INVOICES_V' );
918
919 RAISE G_EXCEPTION_HALT_VALIDATION;
920 END IF;
921 END IF;
922
923 END validate_try_id;
924
925 -----------End addition, Sunil T. Mathew (04/16/2001)
926
927 --Start code added by pgomes on 19-NOV-2002
928 ---------------------------------------------------------------------------
929 -- PROCEDURE validate_pox_id
930 ---------------------------------------------------------------------------
931 PROCEDURE validate_pox_id (p_taiv_rec IN taiv_rec_type,
932 x_return_status OUT NOCOPY VARCHAR2) IS
933 l_dummy_var VARCHAR2(1) := '0';
934
935 CURSOR l_pox_id_csr IS
936 SELECT '1'
937 FROM OKL_POOL_TRANSACTIONS
938 WHERE id = p_taiv_rec.pox_id;
939
940 BEGIN
941 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
942
943 IF (p_taiv_rec.pox_id IS NOT NULL) THEN
944 OPEN l_pox_id_csr;
945 FETCH l_pox_id_csr INTO l_dummy_var;
946 CLOSE l_pox_id_csr;
947
948 IF (l_dummy_var <> '1') THEN
949 x_return_status := Okl_Api.G_RET_STS_ERROR;
950 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
951 p_msg_name => G_NO_PARENT_RECORD,
952 p_token1 => G_COL_NAME_TOKEN,
953 p_token1_value => 'POX_ID_FOR',
954 p_token2 => G_CHILD_TABLE_TOKEN,
955 p_token2_value => G_VIEW,
956 p_token3 => G_PARENT_TABLE_TOKEN,
957 p_token3_value => 'OKL_POOL_TRANSACTIONS');
958
959 RAISE G_EXCEPTION_HALT_VALIDATION;
960 END IF;
961 END IF;
962
963 END validate_pox_id;
964
965 --End code added by pgomes on 19-NOV-2002
966
967 --Start code added by cklee on 13-Feb-2007
968 ---------------------------------------------------------------------------
969 -- PROCEDURE validate_OKL_Src_BILLING_TRX
970 ---------------------------------------------------------------------------
971 PROCEDURE validate_OKL_Src_BILLING_TRX (p_taiv_rec IN taiv_rec_type,
972 x_return_status OUT NOCOPY VARCHAR2) IS
973 l_dummy_var VARCHAR2(1) := '0';
974
975 CURSOR l_OKL_SOURCE_BILLING_TRX_csr IS
976 SELECT '1'
977 FROM FND_LOOKUPS
978 WHERE lookup_type = 'OKL_SOURCE_BILLING_TRX'
979 AND lookup_code = p_taiv_rec.OKL_SOURCE_BILLING_TRX;
980
981 BEGIN
982 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
983
984 IF p_taiv_rec.OKL_SOURCE_BILLING_TRX = Okl_Api.G_MISS_CHAR OR
985 p_taiv_rec.OKL_SOURCE_BILLING_TRX IS NULL
986 THEN
987 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'OKL_SOURCE_BILLING_TRX');
988 x_return_status := Okl_Api.G_RET_STS_ERROR;
989 --set error message in message stack
990 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
991 p_msg_name => G_REQUIRED_VALUE,
992 p_token1 => G_COL_NAME_TOKEN,
993 p_token1_value => 'OKL_SOURCE_BILLING_TRX');
994 RAISE G_EXCEPTION_HALT_VALIDATION;
995
996 END IF;
997
998 IF (p_taiv_rec.OKL_SOURCE_BILLING_TRX IS NOT NULL) THEN
999 OPEN l_OKL_SOURCE_BILLING_TRX_csr;
1000 FETCH l_OKL_SOURCE_BILLING_TRX_csr INTO l_dummy_var;
1001 CLOSE l_OKL_SOURCE_BILLING_TRX_csr;
1002
1003 IF (l_dummy_var <> '1') THEN
1004 x_return_status := Okl_Api.G_RET_STS_ERROR;
1005 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1006 p_msg_name => G_NO_PARENT_RECORD,
1007 p_token1 => G_COL_NAME_TOKEN,
1008 p_token1_value => 'OKL_SOURCE_BILLING_TRX_FOR',
1009 p_token2 => G_CHILD_TABLE_TOKEN,
1010 p_token2_value => G_VIEW,
1011 p_token3 => G_PARENT_TABLE_TOKEN,
1012 p_token3_value => 'FND_LOOKUPS');
1013
1014 RAISE G_EXCEPTION_HALT_VALIDATION;
1015 END IF;
1016 END IF;
1017
1018 END validate_OKL_Src_BILLING_TRX;
1019
1020 --End code added by cklee on 13-Feb-2007
1021
1022
1023 ---------------------------------------------------------------------------
1024 -- FUNCTION get_seq_id
1025 ---------------------------------------------------------------------------
1026 FUNCTION get_seq_id RETURN NUMBER IS
1027 BEGIN
1028 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
1029 END get_seq_id;
1030
1031 ---------------------------------------------------------------------------
1032 -- PROCEDURE qc
1033 ---------------------------------------------------------------------------
1034 PROCEDURE qc IS
1035 BEGIN
1036 NULL;
1037 END qc;
1038
1039 ---------------------------------------------------------------------------
1040 -- PROCEDURE change_version
1041 ---------------------------------------------------------------------------
1042 PROCEDURE change_version IS
1043 BEGIN
1044 NULL;
1045 END change_version;
1046
1047 ---------------------------------------------------------------------------
1048 -- PROCEDURE api_copy
1049 ---------------------------------------------------------------------------
1050 PROCEDURE api_copy IS
1051 BEGIN
1052 NULL;
1053 END api_copy;
1054
1055 ---------------------------------------------------------------------------
1056 -- PROCEDURE add_language
1057 ---------------------------------------------------------------------------
1058 PROCEDURE add_language IS
1059 BEGIN
1060 DELETE FROM OKL_TRX_AR_INVOICES_TL T
1061 WHERE NOT EXISTS (
1062 SELECT NULL
1063 FROM OKL_TRX_AR_INVOICES_B B
1064 WHERE B.ID = T.ID
1065 AND T.LANGUAGE = USERENV('LANG')
1066 );
1067
1068 UPDATE OKL_TRX_AR_INVOICES_TL T SET (
1069 DESCRIPTION) = (SELECT
1070 B.DESCRIPTION
1071 FROM OKL_TRX_AR_INVOICES_TL B
1072 WHERE B.ID = T.ID
1073 AND B.LANGUAGE = T.SOURCE_LANG)
1074 WHERE (
1075 T.ID,
1076 T.LANGUAGE)
1077 IN (SELECT
1078 SUBT.ID,
1079 SUBT.LANGUAGE
1080 FROM OKL_TRX_AR_INVOICES_TL SUBB, OKL_TRX_AR_INVOICES_TL SUBT
1081 WHERE SUBB.ID = SUBT.ID
1082 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
1083 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1084 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
1085 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
1086 ));
1087
1088 INSERT INTO OKL_TRX_AR_INVOICES_TL (
1089 ID,
1090 LANGUAGE,
1091 SOURCE_LANG,
1092 SFWT_FLAG,
1093 DESCRIPTION,
1094 CREATED_BY,
1095 CREATION_DATE,
1096 LAST_UPDATED_BY,
1097 LAST_UPDATE_DATE,
1098 LAST_UPDATE_LOGIN)
1099 SELECT
1100 B.ID,
1101 L.LANGUAGE_CODE,
1102 B.SOURCE_LANG,
1103 B.SFWT_FLAG,
1104 B.DESCRIPTION,
1105 B.CREATED_BY,
1106 B.CREATION_DATE,
1107 B.LAST_UPDATED_BY,
1108 B.LAST_UPDATE_DATE,
1109 B.LAST_UPDATE_LOGIN
1110 FROM OKL_TRX_AR_INVOICES_TL B, FND_LANGUAGES L
1111 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1112 AND B.LANGUAGE = USERENV('LANG')
1113 AND NOT EXISTS(
1114 SELECT NULL
1115 FROM OKL_TRX_AR_INVOICES_TL T
1116 WHERE T.ID = B.ID
1117 AND T.LANGUAGE = L.LANGUAGE_CODE
1118 );
1119
1120 END add_language;
1121
1122 ---------------------------------------------------------------------------
1123 -- FUNCTION get_rec for: OKL_TRX_AR_INVOICES_B
1124 ---------------------------------------------------------------------------
1125 FUNCTION get_rec (
1126 p_tai_rec IN tai_rec_type,
1127 x_no_data_found OUT NOCOPY BOOLEAN
1128 ) RETURN tai_rec_type IS
1129 CURSOR tai_pk_csr (p_id IN NUMBER) IS
1130 SELECT
1131 ID,
1132 CURRENCY_CODE,
1133 --Start change by pgomes on 15-NOV-2002
1134 CURRENCY_CONVERSION_TYPE,
1135 CURRENCY_CONVERSION_RATE,
1136 CURRENCY_CONVERSION_DATE,
1137 --End change by pgomes on 15-NOV-2002
1138 IBT_ID,
1139 IXX_ID,
1140 KHR_ID,
1141 IRM_ID,
1142 IRT_ID,
1143 CRA_ID,
1144 SVF_ID,
1145 TAP_ID,
1146 QTE_ID,
1147 TCN_ID,
1148 TAI_ID_REVERSES,
1149 DATE_ENTERED,
1150 DATE_INVOICED,
1151 AMOUNT,
1152 OBJECT_VERSION_NUMBER,
1153 AMOUNT_APPLIED,
1154 REQUEST_ID,
1155 PROGRAM_APPLICATION_ID,
1156 PROGRAM_ID,
1157 PROGRAM_UPDATE_DATE,
1158 ORG_ID,
1159 IPY_ID,
1160 SET_OF_BOOKS_ID,
1161 TRX_STATUS_CODE,
1162 TRY_ID,
1163 TRX_NUMBER,
1164 CLG_ID,
1165 POX_ID,
1166 CPY_ID,
1167 ATTRIBUTE_CATEGORY,
1168 ATTRIBUTE1,
1169 ATTRIBUTE2,
1170 ATTRIBUTE3,
1171 ATTRIBUTE4,
1172 ATTRIBUTE5,
1173 ATTRIBUTE6,
1174 ATTRIBUTE7,
1175 ATTRIBUTE8,
1176 ATTRIBUTE9,
1177 ATTRIBUTE10,
1178 ATTRIBUTE11,
1179 ATTRIBUTE12,
1180 ATTRIBUTE13,
1181 ATTRIBUTE14,
1182 ATTRIBUTE15,
1183 CREATED_BY,
1184 CREATION_DATE,
1185 LAST_UPDATED_BY,
1186 LAST_UPDATE_DATE,
1187 LAST_UPDATE_LOGIN,
1188 LEGAL_ENTITY_ID, -- for LE Uptake project 08-11-2006
1189
1190 -- start:30-Jan-07 cklee Billing R12 project
1191 Investor_Agreement_Number,
1192 Investor_Name,
1193 OKL_SOURCE_BILLING_TRX,
1194 INF_ID,
1195 INVOICE_PULL_YN,
1196 CONSOLIDATED_INVOICE_NUMBER,
1197 DUE_DATE,
1198 ISI_ID,
1199 RECEIVABLES_INVOICE_ID,
1200 CUST_TRX_TYPE_ID,
1201 CUSTOMER_BANK_ACCOUNT_ID,
1202 TAX_EXEMPT_FLAG,
1203 TAX_EXEMPT_REASON_CODE,
1204 REFERENCE_LINE_ID,
1205 PRIVATE_LABEL,
1206 -- end:30-Jan-07 cklee Billing R12 project
1207 --gkhuntet start 02-Nov-07
1208 TRANSACTION_DATE
1209 --gkhuntet end 02-Nov-07
1210
1211 FROM Okl_Trx_Ar_Invoices_B
1212 WHERE okl_trx_ar_invoices_b.id = p_id;
1213 l_tai_pk tai_pk_csr%ROWTYPE;
1214 l_tai_rec tai_rec_type;
1215 BEGIN
1216 x_no_data_found := TRUE;
1217 -- Get current database values
1218 OPEN tai_pk_csr (p_tai_rec.id);
1219 FETCH tai_pk_csr INTO
1220 l_tai_rec.ID,
1221 l_tai_rec.CURRENCY_CODE,
1222 --Start change by pgomes on 15-NOV-2002
1223 l_tai_rec.CURRENCY_CONVERSION_TYPE,
1224 l_tai_rec.CURRENCY_CONVERSION_RATE,
1225 l_tai_rec.CURRENCY_CONVERSION_DATE,
1226 --End change by pgomes on 15-NOV-2002
1227 l_tai_rec.IBT_ID,
1228 l_tai_rec.IXX_ID,
1229 l_tai_rec.KHR_ID,
1230 l_tai_rec.IRM_ID,
1231 l_tai_rec.IRT_ID,
1232 l_tai_rec.CRA_ID,
1233 l_tai_rec.SVF_ID,
1234 l_tai_rec.TAP_ID,
1235 l_tai_rec.QTE_ID,
1236 l_tai_rec.TCN_ID,
1237 l_tai_rec.TAI_ID_REVERSES,
1238 l_tai_rec.DATE_ENTERED,
1239 l_tai_rec.DATE_INVOICED,
1240 l_tai_rec.AMOUNT,
1241 l_tai_rec.OBJECT_VERSION_NUMBER,
1242 l_tai_rec.AMOUNT_APPLIED,
1243 l_tai_rec.REQUEST_ID,
1244 l_tai_rec.PROGRAM_APPLICATION_ID,
1245 l_tai_rec.PROGRAM_ID,
1246 l_tai_rec.PROGRAM_UPDATE_DATE,
1247 l_tai_rec.ORG_ID,
1248 l_tai_rec.IPY_ID,
1249 l_tai_rec.SET_OF_BOOKS_ID,
1250 l_tai_rec.TRX_STATUS_CODE,
1251 l_tai_rec.TRY_ID,
1252 l_tai_rec.TRX_NUMBER,
1253 l_tai_rec.CLG_ID,
1254 l_tai_rec.POX_ID,
1255 l_tai_rec.CPY_ID,
1256 l_tai_rec.ATTRIBUTE_CATEGORY,
1257 l_tai_rec.ATTRIBUTE1,
1258 l_tai_rec.ATTRIBUTE2,
1259 l_tai_rec.ATTRIBUTE3,
1260 l_tai_rec.ATTRIBUTE4,
1261 l_tai_rec.ATTRIBUTE5,
1262 l_tai_rec.ATTRIBUTE6,
1263 l_tai_rec.ATTRIBUTE7,
1264 l_tai_rec.ATTRIBUTE8,
1265 l_tai_rec.ATTRIBUTE9,
1266 l_tai_rec.ATTRIBUTE10,
1267 l_tai_rec.ATTRIBUTE11,
1268 l_tai_rec.ATTRIBUTE12,
1269 l_tai_rec.ATTRIBUTE13,
1270 l_tai_rec.ATTRIBUTE14,
1271 l_tai_rec.ATTRIBUTE15,
1272 l_tai_rec.CREATED_BY,
1273 l_tai_rec.CREATION_DATE,
1274 l_tai_rec.LAST_UPDATED_BY,
1275 l_tai_rec.LAST_UPDATE_DATE,
1276 l_tai_rec.LAST_UPDATE_LOGIN,
1277 l_tai_rec.LEGAL_ENTITY_ID, -- for LE Uptake project 08-11-2006
1278
1279 -- start:30-Jan-07 cklee Billing R12 project
1280 l_tai_rec.Investor_Agreement_Number,
1281 l_tai_rec.Investor_Name,
1282 l_tai_rec.OKL_SOURCE_BILLING_TRX,
1283 l_tai_rec.INF_ID,
1284 l_tai_rec.INVOICE_PULL_YN,
1285 l_tai_rec.CONSOLIDATED_INVOICE_NUMBER,
1286 l_tai_rec.DUE_DATE,
1287 l_tai_rec.ISI_ID,
1288 l_tai_rec.RECEIVABLES_INVOICE_ID,
1289 l_tai_rec.CUST_TRX_TYPE_ID,
1290 l_tai_rec.CUSTOMER_BANK_ACCOUNT_ID,
1291 l_tai_rec.TAX_EXEMPT_FLAG,
1292 l_tai_rec.TAX_EXEMPT_REASON_CODE,
1293 l_tai_rec.REFERENCE_LINE_ID,
1294 l_tai_rec.PRIVATE_LABEL,
1295 -- end:30-Jan-07 cklee Billing R12 project
1296 --gkhuntet start 02-Nov-07
1297 l_tai_rec.TRANSACTION_DATE;
1298 --gkhuntet end 02-Nov-07
1299 x_no_data_found := tai_pk_csr%NOTFOUND;
1300 CLOSE tai_pk_csr;
1301 RETURN(l_tai_rec);
1302 END get_rec;
1303
1304 FUNCTION get_rec (
1305 p_tai_rec IN tai_rec_type
1306 ) RETURN tai_rec_type IS
1307 l_row_notfound BOOLEAN := TRUE;
1308 BEGIN
1309 RETURN(get_rec(p_tai_rec, l_row_notfound));
1310 END get_rec;
1311 ---------------------------------------------------------------------------
1312 -- FUNCTION get_rec for: OKL_TRX_AR_INVOICES_TL
1313 ---------------------------------------------------------------------------
1314 FUNCTION get_rec (
1315 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType,
1316 x_no_data_found OUT NOCOPY BOOLEAN
1317 ) RETURN OklTrxArInvoicesTlRecType IS
1318 CURSOR okl_trx_ar_invoices_tl_pk_csr (p_id IN NUMBER,
1319 p_language IN VARCHAR2) IS
1320 SELECT
1321 ID,
1322 LANGUAGE,
1323 SOURCE_LANG,
1324 SFWT_FLAG,
1325 DESCRIPTION,
1326 CREATED_BY,
1327 CREATION_DATE,
1328 LAST_UPDATED_BY,
1329 LAST_UPDATE_DATE,
1330 LAST_UPDATE_LOGIN
1331 FROM Okl_Trx_Ar_Invoices_Tl
1332 WHERE okl_trx_ar_invoices_tl.id = p_id
1333 AND okl_trx_ar_invoices_tl.LANGUAGE = p_language;
1334 l_okl_trx_ar_invoices_tl_pk okl_trx_ar_invoices_tl_pk_csr%ROWTYPE;
1335 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
1336 BEGIN
1337 x_no_data_found := TRUE;
1338 -- Get current database values
1339 OPEN okl_trx_ar_invoices_tl_pk_csr (p_okl_trx_ar_invoices_tl_rec.id,
1340 p_okl_trx_ar_invoices_tl_rec.LANGUAGE);
1341 FETCH okl_trx_ar_invoices_tl_pk_csr INTO
1342 l_okl_trx_ar_invoices_tl_rec.ID,
1343 l_okl_trx_ar_invoices_tl_rec.LANGUAGE,
1344 l_okl_trx_ar_invoices_tl_rec.SOURCE_LANG,
1345 l_okl_trx_ar_invoices_tl_rec.SFWT_FLAG,
1346 l_okl_trx_ar_invoices_tl_rec.DESCRIPTION,
1347 l_okl_trx_ar_invoices_tl_rec.CREATED_BY,
1348 l_okl_trx_ar_invoices_tl_rec.CREATION_DATE,
1349 l_okl_trx_ar_invoices_tl_rec.LAST_UPDATED_BY,
1350 l_okl_trx_ar_invoices_tl_rec.LAST_UPDATE_DATE,
1351 l_okl_trx_ar_invoices_tl_rec.LAST_UPDATE_LOGIN;
1352 x_no_data_found := okl_trx_ar_invoices_tl_pk_csr%NOTFOUND;
1353 CLOSE okl_trx_ar_invoices_tl_pk_csr;
1354 RETURN(l_okl_trx_ar_invoices_tl_rec);
1355 END get_rec;
1356
1357 FUNCTION get_rec (
1358 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType
1359 ) RETURN OklTrxArInvoicesTlRecType IS
1360 l_row_notfound BOOLEAN := TRUE;
1361 BEGIN
1362 RETURN(get_rec(p_okl_trx_ar_invoices_tl_rec, l_row_notfound));
1363 END get_rec;
1364 ---------------------------------------------------------------------------
1365 -- FUNCTION get_rec for: OKL_TRX_AR_INVOICES_V
1366 ---------------------------------------------------------------------------
1367 FUNCTION get_rec (
1368 p_taiv_rec IN taiv_rec_type,
1369 x_no_data_found OUT NOCOPY BOOLEAN
1370 ) RETURN taiv_rec_type IS
1371 CURSOR okl_taiv_pk_csr (p_id IN NUMBER) IS
1372 SELECT
1373 ID,
1374 OBJECT_VERSION_NUMBER,
1375 SFWT_FLAG,
1376 CURRENCY_CODE,
1377 --Start change by pgomes on 15-NOV-2002
1378 CURRENCY_CONVERSION_TYPE,
1379 CURRENCY_CONVERSION_RATE,
1380 CURRENCY_CONVERSION_DATE,
1381 --End change by pgomes on 15-NOV-2002
1382 KHR_ID,
1383 CRA_ID,
1384 TAP_ID,
1385 QTE_ID,
1386 TCN_ID,
1387 TAI_ID_REVERSES,
1388 IBT_ID,
1389 IXX_ID,
1390 IRM_ID,
1391 IRT_ID,
1392 SVF_ID,
1393 AMOUNT,
1394 DATE_INVOICED,
1395 AMOUNT_APPLIED,
1396 DESCRIPTION,
1397 IPY_ID,
1398 SET_OF_BOOKS_ID,
1399 TRX_STATUS_CODE,
1400 TRY_ID,
1401 TRX_NUMBER,
1402 CLG_ID,
1403 POX_ID,
1404 CPY_ID,
1405 ATTRIBUTE_CATEGORY,
1406 ATTRIBUTE1,
1407 ATTRIBUTE2,
1408 ATTRIBUTE3,
1409 ATTRIBUTE4,
1410 ATTRIBUTE5,
1411 ATTRIBUTE6,
1412 ATTRIBUTE7,
1413 ATTRIBUTE8,
1414 ATTRIBUTE9,
1415 ATTRIBUTE10,
1416 ATTRIBUTE11,
1417 ATTRIBUTE12,
1418 ATTRIBUTE13,
1419 ATTRIBUTE14,
1420 ATTRIBUTE15,
1421 DATE_ENTERED,
1422 REQUEST_ID,
1423 PROGRAM_APPLICATION_ID,
1424 PROGRAM_ID,
1425 PROGRAM_UPDATE_DATE,
1426 ORG_ID,
1427 CREATED_BY,
1428 CREATION_DATE,
1429 LAST_UPDATED_BY,
1430 LAST_UPDATE_DATE,
1431 LAST_UPDATE_LOGIN,
1432 LEGAL_ENTITY_ID, -- for LE Uptake project 08-11-2006
1433
1434 -- start:30-Jan-07 cklee Billing R12 project
1435 Investor_Agreement_Number,
1436 Investor_Name,
1437 OKL_SOURCE_BILLING_TRX,
1438 INF_ID,
1439 INVOICE_PULL_YN,
1440 CONSOLIDATED_INVOICE_NUMBER,
1441 DUE_DATE,
1442 ISI_ID,
1443 RECEIVABLES_INVOICE_ID,
1444 CUST_TRX_TYPE_ID,
1445 CUSTOMER_BANK_ACCOUNT_ID,
1446 TAX_EXEMPT_FLAG,
1447 TAX_EXEMPT_REASON_CODE,
1448 REFERENCE_LINE_ID,
1449 PRIVATE_LABEL,
1450 -- end:30-Jan-07 cklee Billing R12 project
1451 --gkhuntet start 02-Nov-07
1452 TRANSACTION_DATE
1453 --gkhuntet end 02-Nov-07
1454 FROM Okl_Trx_Ar_Invoices_V
1455 WHERE okl_trx_ar_invoices_v.id = p_id;
1456 l_okl_taiv_pk okl_taiv_pk_csr%ROWTYPE;
1457 l_taiv_rec taiv_rec_type;
1458 BEGIN
1459 x_no_data_found := TRUE;
1460 -- Get current database values
1461 OPEN okl_taiv_pk_csr (p_taiv_rec.id);
1462 FETCH okl_taiv_pk_csr INTO
1463 l_taiv_rec.ID,
1464 l_taiv_rec.OBJECT_VERSION_NUMBER,
1465 l_taiv_rec.SFWT_FLAG,
1466 l_taiv_rec.CURRENCY_CODE,
1467 --Start change by pgomes on 15-NOV-2002
1468 l_taiv_rec.CURRENCY_CONVERSION_TYPE,
1469 l_taiv_rec.CURRENCY_CONVERSION_RATE,
1470 l_taiv_rec.CURRENCY_CONVERSION_DATE,
1471 --End change by pgomes on 15-NOV-2002
1472 l_taiv_rec.KHR_ID,
1473 l_taiv_rec.CRA_ID,
1474 l_taiv_rec.TAP_ID,
1475 l_taiv_rec.QTE_ID,
1476 l_taiv_rec.TCN_ID,
1477 l_taiv_rec.TAI_ID_REVERSES,
1478 l_taiv_rec.IBT_ID,
1479 l_taiv_rec.IXX_ID,
1480 l_taiv_rec.IRM_ID,
1481 l_taiv_rec.IRT_ID,
1482 l_taiv_rec.SVF_ID,
1483 l_taiv_rec.AMOUNT,
1484 l_taiv_rec.DATE_INVOICED,
1485 l_taiv_rec.AMOUNT_APPLIED,
1486 l_taiv_rec.DESCRIPTION,
1487 l_taiv_rec.IPY_ID,
1488 l_taiv_rec.SET_OF_BOOKS_ID,
1489 l_taiv_rec.TRX_STATUS_CODE,
1490 l_taiv_rec.TRY_ID,
1491 l_taiv_rec.TRX_NUMBER,
1492 l_taiv_rec.CLG_ID,
1493 l_taiv_rec.POX_ID,
1494 l_taiv_rec.CPY_ID,
1495 l_taiv_rec.ATTRIBUTE_CATEGORY,
1496 l_taiv_rec.ATTRIBUTE1,
1497 l_taiv_rec.ATTRIBUTE2,
1498 l_taiv_rec.ATTRIBUTE3,
1499 l_taiv_rec.ATTRIBUTE4,
1500 l_taiv_rec.ATTRIBUTE5,
1501 l_taiv_rec.ATTRIBUTE6,
1502 l_taiv_rec.ATTRIBUTE7,
1503 l_taiv_rec.ATTRIBUTE8,
1504 l_taiv_rec.ATTRIBUTE9,
1505 l_taiv_rec.ATTRIBUTE10,
1506 l_taiv_rec.ATTRIBUTE11,
1507 l_taiv_rec.ATTRIBUTE12,
1508 l_taiv_rec.ATTRIBUTE13,
1509 l_taiv_rec.ATTRIBUTE14,
1510 l_taiv_rec.ATTRIBUTE15,
1511 l_taiv_rec.DATE_ENTERED,
1512 l_taiv_rec.REQUEST_ID,
1513 l_taiv_rec.PROGRAM_APPLICATION_ID,
1514 l_taiv_rec.PROGRAM_ID,
1515 l_taiv_rec.PROGRAM_UPDATE_DATE,
1516 l_taiv_rec.ORG_ID,
1517 l_taiv_rec.CREATED_BY,
1518 l_taiv_rec.CREATION_DATE,
1519 l_taiv_rec.LAST_UPDATED_BY,
1520 l_taiv_rec.LAST_UPDATE_DATE,
1521 l_taiv_rec.LAST_UPDATE_LOGIN,
1522 l_taiv_rec.LEGAL_ENTITY_ID, -- for LE Uptake project 08-11-2006
1523
1524 -- start:30-Jan-07 cklee Billing R12 project
1525 l_taiv_rec.Investor_Agreement_Number,
1526 l_taiv_rec.Investor_Name,
1527 l_taiv_rec.OKL_SOURCE_BILLING_TRX,
1528 l_taiv_rec.INF_ID,
1529 l_taiv_rec.INVOICE_PULL_YN,
1530 l_taiv_rec.CONSOLIDATED_INVOICE_NUMBER,
1531 l_taiv_rec.DUE_DATE,
1532 l_taiv_rec.ISI_ID,
1533 l_taiv_rec.RECEIVABLES_INVOICE_ID,
1534 l_taiv_rec.CUST_TRX_TYPE_ID,
1535 l_taiv_rec.CUSTOMER_BANK_ACCOUNT_ID,
1536 l_taiv_rec.TAX_EXEMPT_FLAG,
1537 l_taiv_rec.TAX_EXEMPT_REASON_CODE,
1538 l_taiv_rec.REFERENCE_LINE_ID,
1539 l_taiv_rec.PRIVATE_LABEL,
1540 -- end:30-Jan-07 cklee Billing R12 project
1541 --gkhuntet start 02-Nov-07
1542 l_taiv_rec.TRANSACTION_DATE;
1543 --gkhuntet end 02-Nov-07;
1544
1545 x_no_data_found := okl_taiv_pk_csr%NOTFOUND;
1546 CLOSE okl_taiv_pk_csr;
1547 RETURN(l_taiv_rec);
1548 END get_rec;
1549
1550 FUNCTION get_rec (
1551 p_taiv_rec IN taiv_rec_type
1552 ) RETURN taiv_rec_type IS
1553 l_row_notfound BOOLEAN := TRUE;
1554 BEGIN
1555 RETURN(get_rec(p_taiv_rec, l_row_notfound));
1556 END get_rec;
1557
1558 -----------------------------------------------------------
1559 -- FUNCTION null_out_defaults for: OKL_TRX_AR_INVOICES_V --
1560 -----------------------------------------------------------
1561 FUNCTION null_out_defaults (
1562 p_taiv_rec IN taiv_rec_type
1563 ) RETURN taiv_rec_type IS
1564 l_taiv_rec taiv_rec_type := p_taiv_rec;
1565 BEGIN
1566 IF (l_taiv_rec.object_version_number = Okl_Api.G_MISS_NUM) THEN
1567 l_taiv_rec.object_version_number := NULL;
1568 END IF;
1569 IF (l_taiv_rec.sfwt_flag = Okl_Api.G_MISS_CHAR) THEN
1570 l_taiv_rec.sfwt_flag := NULL;
1571 END IF;
1572 IF (l_taiv_rec.currency_code = Okl_Api.G_MISS_CHAR) THEN
1573 l_taiv_rec.currency_code := NULL;
1574 END IF;
1575
1576 --Start change by pgomes on 15-NOV-2002
1577 IF (l_taiv_rec.currency_conversion_type = Okl_Api.G_MISS_CHAR) THEN
1578 l_taiv_rec.currency_conversion_type := NULL;
1579 END IF;
1580
1581 IF (l_taiv_rec.currency_conversion_rate = Okl_Api.G_MISS_NUM) THEN
1582 l_taiv_rec.currency_conversion_rate := NULL;
1583 END IF;
1584
1585 IF (l_taiv_rec.currency_conversion_date = Okl_Api.G_MISS_DATE) THEN
1586 l_taiv_rec.currency_conversion_date := NULL;
1587 END IF;
1588 --End change by pgomes on 15-NOV-2002
1589
1590 IF (l_taiv_rec.khr_id = Okl_Api.G_MISS_NUM) THEN
1591 l_taiv_rec.khr_id := NULL;
1592 END IF;
1593 IF (l_taiv_rec.cra_id = Okl_Api.G_MISS_NUM) THEN
1594 l_taiv_rec.cra_id := NULL;
1595 END IF;
1596 IF (l_taiv_rec.tap_id = Okl_Api.G_MISS_NUM) THEN
1597 l_taiv_rec.tap_id := NULL;
1598 END IF;
1599 IF (l_taiv_rec.qte_id = Okl_Api.G_MISS_NUM) THEN
1600 l_taiv_rec.qte_id := NULL;
1601 END IF;
1602 IF (l_taiv_rec.tcn_id = Okl_Api.G_MISS_NUM) THEN
1603 l_taiv_rec.tcn_id := NULL;
1604 END IF;
1605 IF (l_taiv_rec.tai_id_reverses = Okl_Api.G_MISS_NUM) THEN
1606 l_taiv_rec.tai_id_reverses := NULL;
1607 END IF;
1608 IF (l_taiv_rec.ibt_id = Okl_Api.G_MISS_NUM) THEN
1609 l_taiv_rec.ibt_id := NULL;
1610 END IF;
1611 IF (l_taiv_rec.ixx_id = Okl_Api.G_MISS_NUM) THEN
1612 l_taiv_rec.ixx_id := NULL;
1613 END IF;
1614 IF (l_taiv_rec.irm_id = Okl_Api.G_MISS_NUM) THEN
1615 l_taiv_rec.irm_id := NULL;
1616 END IF;
1617 IF (l_taiv_rec.irt_id = Okl_Api.G_MISS_NUM) THEN
1618 l_taiv_rec.irt_id := NULL;
1619 END IF;
1620 IF (l_taiv_rec.svf_id = Okl_Api.G_MISS_NUM) THEN
1621 l_taiv_rec.svf_id := NULL;
1622 END IF;
1623 IF (l_taiv_rec.amount = Okl_Api.G_MISS_NUM) THEN
1624 l_taiv_rec.amount := NULL;
1625 END IF;
1626 IF (l_taiv_rec.date_invoiced = Okl_Api.G_MISS_DATE) THEN
1627 l_taiv_rec.date_invoiced := NULL;
1628 END IF;
1629 IF (l_taiv_rec.amount_applied = Okl_Api.G_MISS_NUM) THEN
1630 l_taiv_rec.amount_applied := NULL;
1631 END IF;
1632 IF (l_taiv_rec.description = Okl_Api.G_MISS_CHAR) THEN
1633 l_taiv_rec.description := NULL;
1634 END IF;
1635
1636 IF (l_taiv_rec.trx_number = Okl_Api.G_MISS_CHAR) THEN
1637 l_taiv_rec.trx_number := NULL;
1638 END IF;
1639 IF (l_taiv_rec.clg_id = Okl_Api.G_MISS_NUM) THEN
1640 l_taiv_rec.clg_id := NULL;
1641 END IF;
1642
1643 --Start code added by pgomes on 19-NOV-2002
1644 IF (l_taiv_rec.pox_id = Okl_Api.G_MISS_NUM) THEN
1645 l_taiv_rec.pox_id := NULL;
1646 END IF;
1647 --End code added by pgomes on 19-NOV-2002
1648
1649 IF (l_taiv_rec.cpy_id = Okl_Api.G_MISS_NUM) THEN
1650 l_taiv_rec.cpy_id := NULL;
1651 END IF;
1652 IF (l_taiv_rec.attribute_category = Okl_Api.G_MISS_CHAR) THEN
1653 l_taiv_rec.attribute_category := NULL;
1654 END IF;
1655 IF (l_taiv_rec.attribute1 = Okl_Api.G_MISS_CHAR) THEN
1656 l_taiv_rec.attribute1 := NULL;
1657 END IF;
1658 IF (l_taiv_rec.attribute2 = Okl_Api.G_MISS_CHAR) THEN
1659 l_taiv_rec.attribute2 := NULL;
1660 END IF;
1661 IF (l_taiv_rec.attribute3 = Okl_Api.G_MISS_CHAR) THEN
1662 l_taiv_rec.attribute3 := NULL;
1663 END IF;
1664 IF (l_taiv_rec.attribute4 = Okl_Api.G_MISS_CHAR) THEN
1665 l_taiv_rec.attribute4 := NULL;
1666 END IF;
1667 IF (l_taiv_rec.attribute5 = Okl_Api.G_MISS_CHAR) THEN
1668 l_taiv_rec.attribute5 := NULL;
1669 END IF;
1670 IF (l_taiv_rec.attribute6 = Okl_Api.G_MISS_CHAR) THEN
1671 l_taiv_rec.attribute6 := NULL;
1672 END IF;
1673 IF (l_taiv_rec.attribute7 = Okl_Api.G_MISS_CHAR) THEN
1674 l_taiv_rec.attribute7 := NULL;
1675 END IF;
1676 IF (l_taiv_rec.attribute8 = Okl_Api.G_MISS_CHAR) THEN
1677 l_taiv_rec.attribute8 := NULL;
1678 END IF;
1679 IF (l_taiv_rec.attribute9 = Okl_Api.G_MISS_CHAR) THEN
1680 l_taiv_rec.attribute9 := NULL;
1681 END IF;
1682 IF (l_taiv_rec.attribute10 = Okl_Api.G_MISS_CHAR) THEN
1683 l_taiv_rec.attribute10 := NULL;
1684 END IF;
1685 IF (l_taiv_rec.attribute11 = Okl_Api.G_MISS_CHAR) THEN
1686 l_taiv_rec.attribute11 := NULL;
1687 END IF;
1688 IF (l_taiv_rec.attribute12 = Okl_Api.G_MISS_CHAR) THEN
1689 l_taiv_rec.attribute12 := NULL;
1690 END IF;
1691 IF (l_taiv_rec.attribute13 = Okl_Api.G_MISS_CHAR) THEN
1692 l_taiv_rec.attribute13 := NULL;
1693 END IF;
1694 IF (l_taiv_rec.attribute14 = Okl_Api.G_MISS_CHAR) THEN
1695 l_taiv_rec.attribute14 := NULL;
1696 END IF;
1697 IF (l_taiv_rec.attribute15 = Okl_Api.G_MISS_CHAR) THEN
1698 l_taiv_rec.attribute15 := NULL;
1699 END IF;
1700 IF (l_taiv_rec.date_entered = Okl_Api.G_MISS_DATE) THEN
1701 l_taiv_rec.date_entered := NULL;
1702 END IF;
1703 IF (l_taiv_rec.request_id = Okl_Api.G_MISS_NUM) THEN
1704 l_taiv_rec.request_id := NULL;
1705 END IF;
1706 IF (l_taiv_rec.program_application_id = Okl_Api.G_MISS_NUM) THEN
1707 l_taiv_rec.program_application_id := NULL;
1708 END IF;
1709 IF (l_taiv_rec.program_id = Okl_Api.G_MISS_NUM) THEN
1710 l_taiv_rec.program_id := NULL;
1711 END IF;
1712 IF (l_taiv_rec.program_update_date = Okl_Api.G_MISS_DATE) THEN
1713 l_taiv_rec.program_update_date := NULL;
1714 END IF;
1715 IF (l_taiv_rec.org_id = Okl_Api.G_MISS_NUM) THEN
1716 l_taiv_rec.org_id := NULL;
1717 END IF;
1718 IF (l_taiv_rec.created_by = Okl_Api.G_MISS_NUM) THEN
1719 l_taiv_rec.created_by := NULL;
1720 END IF;
1721 IF (l_taiv_rec.creation_date = Okl_Api.G_MISS_DATE) THEN
1722 l_taiv_rec.creation_date := NULL;
1723 END IF;
1724 IF (l_taiv_rec.last_updated_by = Okl_Api.G_MISS_NUM) THEN
1725 l_taiv_rec.last_updated_by := NULL;
1726 END IF;
1727 IF (l_taiv_rec.last_update_date = Okl_Api.G_MISS_DATE) THEN
1728 l_taiv_rec.last_update_date := NULL;
1729 END IF;
1730 IF (l_taiv_rec.last_update_login = Okl_Api.G_MISS_NUM) THEN
1731 l_taiv_rec.last_update_login := NULL;
1732 END IF;
1733 IF (l_taiv_rec.TRX_STATUS_CODE = Okl_Api.G_MISS_CHAR) THEN
1734 l_taiv_rec.TRX_STATUS_CODE := NULL;
1735 END IF;
1736 IF (l_taiv_rec.IPY_ID = Okl_Api.G_MISS_NUM) THEN
1737 l_taiv_rec.IPY_ID := NULL;
1738 END IF;
1739 IF (l_taiv_rec.SET_OF_BOOKS_ID = Okl_Api.G_MISS_NUM) THEN
1740 l_taiv_rec.SET_OF_BOOKS_ID := NULL;
1741 END IF;
1742 IF (l_taiv_rec.TRY_ID = Okl_Api.G_MISS_NUM) THEN
1743 l_taiv_rec.TRY_ID := NULL;
1744 END IF;
1745 -- for LE Uptake project 08-11-2006
1746 IF (l_taiv_rec.legal_entity_id = Okl_Api.G_MISS_NUM) THEN
1747 l_taiv_rec.legal_entity_id := NULL;
1748 END IF;
1749 -- for LE Uptake project 08-11-2006
1750
1751 -- start:30-Jan-07 cklee Billing R12 project
1752 -- Investor_Agreement_Number,
1753 IF (l_taiv_rec.Investor_Agreement_Number = Okl_Api.G_MISS_CHAR) THEN
1754 l_taiv_rec.Investor_Agreement_Number := NULL;
1755 END IF;
1756 -- Investor_Name,
1757 IF (l_taiv_rec.Investor_Name = Okl_Api.G_MISS_CHAR) THEN
1758 l_taiv_rec.Investor_Name := NULL;
1759 END IF;
1760
1761 -- OKL_SOURCE_BILLING_TRX,
1762 IF (l_taiv_rec.OKL_SOURCE_BILLING_TRX = Okl_Api.G_MISS_CHAR) THEN
1763 l_taiv_rec.OKL_SOURCE_BILLING_TRX := NULL;
1764 END IF;
1765
1766 -- INF_ID,
1767 IF (l_taiv_rec.INF_ID = Okl_Api.G_MISS_NUM) THEN
1768 l_taiv_rec.INF_ID := NULL;
1769 END IF;
1770
1771 -- INVOICE_PULL_YN,
1772 IF (l_taiv_rec.INVOICE_PULL_YN = Okl_Api.G_MISS_CHAR) THEN
1773 l_taiv_rec.INVOICE_PULL_YN := NULL;
1774 END IF;
1775
1776 -- CONSOLIDATED_INVOICE_NUMBER,
1777 IF (l_taiv_rec.CONSOLIDATED_INVOICE_NUMBER = Okl_Api.G_MISS_CHAR) THEN
1778 l_taiv_rec.CONSOLIDATED_INVOICE_NUMBER := NULL;
1779 END IF;
1780
1781 -- DUE_DATE,
1782 IF (l_taiv_rec.DUE_DATE = Okl_Api.G_MISS_DATE) THEN
1783 l_taiv_rec.DUE_DATE := NULL;
1784 END IF;
1785
1786 -- ISI_ID,
1787 IF (l_taiv_rec.ISI_ID = Okl_Api.G_MISS_NUM) THEN
1788 l_taiv_rec.ISI_ID := NULL;
1789 END IF;
1790
1791 -- RECEIVABLES_INVOICE_ID,
1792 IF (l_taiv_rec.RECEIVABLES_INVOICE_ID = Okl_Api.G_MISS_NUM) THEN
1793 l_taiv_rec.RECEIVABLES_INVOICE_ID := NULL;
1794 END IF;
1795
1796 -- CUST_TRX_TYPE_ID,
1797 IF (l_taiv_rec.CUST_TRX_TYPE_ID = Okl_Api.G_MISS_NUM) THEN
1798 l_taiv_rec.CUST_TRX_TYPE_ID := NULL;
1799 END IF;
1800
1801 -- CUSTOMER_BANK_ACCOUNT_ID,
1802 IF (l_taiv_rec.CUSTOMER_BANK_ACCOUNT_ID = Okl_Api.G_MISS_NUM) THEN
1803 l_taiv_rec.CUSTOMER_BANK_ACCOUNT_ID := NULL;
1804 END IF;
1805
1806 -- TAX_EXEMPT_FLAG,
1807 IF (l_taiv_rec.TAX_EXEMPT_FLAG = Okl_Api.G_MISS_CHAR) THEN
1808 l_taiv_rec.TAX_EXEMPT_FLAG := NULL;
1809 END IF;
1810
1811 -- TAX_EXEMPT_REASON_CODE,
1812 IF (l_taiv_rec.TAX_EXEMPT_REASON_CODE = Okl_Api.G_MISS_CHAR) THEN
1813 l_taiv_rec.TAX_EXEMPT_REASON_CODE := NULL;
1814 END IF;
1815
1816 -- REFERENCE_LINE_ID
1817 IF (l_taiv_rec.REFERENCE_LINE_ID = Okl_Api.G_MISS_NUM) THEN
1818 l_taiv_rec.REFERENCE_LINE_ID := NULL;
1819 END IF;
1820
1821 -- PRIVATE_LABEL
1822 IF (l_taiv_rec.PRIVATE_LABEL = Okl_Api.G_MISS_CHAR) THEN
1823 l_taiv_rec.PRIVATE_LABEL := NULL;
1824 END IF;
1825
1826 -- end:30-Jan-07 cklee Billing R12 project
1827 --gkhuntet start 02-Nov-07
1828 IF (l_taiv_rec. TRANSACTION_DATE = Okl_Api.G_MISS_DATE) THEN
1829 l_taiv_rec. TRANSACTION_DATE := NULL;
1830 END IF;
1831 --gkhuntet end 02-Nov-07
1832 RETURN(l_taiv_rec);
1833 END null_out_defaults;
1834
1835 -- 4916724
1836 ------------------------------------
1837 -- FUNCTION get_unique_trx_number
1838 --------------------------------------
1839
1840 FUNCTION get_unique_trx_number (
1841 p_rec_id IN NUMBER
1842 ) RETURN VARCHAR2 IS
1843 l_counter NUMBER :=0;
1844 l_new_trx_number VARCHAR2(240);
1845 l_rec_exist VARCHAR2(1) DEFAULT '0';
1846
1847 CURSOR l_trx_number_uniq_csr(p_trx_number varchar2) is
1848 SELECT '1'
1849 FROM OKL_TRX_AR_INVOICES_B INV
1850 WHERE ID <> p_rec_id
1851 AND TRX_NUMBER IS NOT NULL
1852 AND TRX_NUMBER = p_trx_number;
1853
1854 BEGIN
1855 IF p_rec_id IS NOT NULL THEN
1856 --Counter to get minimum of last six character
1857 l_counter:=6;
1858 LOOP
1859 -- if the length are same then assign the rec id in the transaction number
1860 if l_counter>=length(to_char(p_rec_id)) then
1861 l_new_trx_number:=p_rec_id;
1862 EXIT;
1863 end if;
1864
1865 l_new_trx_number := SUBSTR(TO_CHAR(p_rec_id),-l_counter);
1866 --check whether the transaction number exisit
1867 OPEN l_trx_number_uniq_csr(l_new_trx_number);
1868 FETCH l_trx_number_uniq_csr INTO l_rec_exist;
1869 CLOSE l_trx_number_uniq_csr;
1870 IF(l_rec_exist = '1')THEN
1871 --increment the counter to fetch one more character from the last
1872 l_counter:=l_counter+1;
1873 --reset the variable
1874 l_rec_exist:='0';
1875 ELSE
1876 --found the unique transaction number so exit from the loop
1877 EXIT;
1878 END IF;
1879 END LOOP;
1880 END IF;
1881 RETURN (l_new_trx_number);
1882
1883 END get_unique_trx_number;
1884
1885 ---------------------------------------------------------------------------
1886 -- PROCEDURE Validate_Attributes
1887 ---------------------------------------------------------------------------
1888 ---------------------------------------------------
1889 -- Validate_Attributes for:OKL_TRX_AR_INVOICES_V --
1890 ---------------------------------------------------
1891 FUNCTION Validate_Attributes (
1892 p_taiv_rec IN taiv_rec_type
1893 ) RETURN VARCHAR2 IS
1894 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1895 -- Added 04/16/2001 -- Sunil Mathew
1896 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1897 BEGIN
1898 -- Start Code , Sunil T. Mathew (04/16/2001)
1899
1900 validate_currency_code(p_taiv_rec, x_return_status);
1901 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1902 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1903 l_return_status := x_return_status;
1904 RAISE G_EXCEPTION_HALT_VALIDATION;
1905 ELSE
1906 l_return_status := x_return_status; -- record that there was an error
1907 END IF;
1908 END IF;
1909
1910
1911 --Start code added by pgomes on 15-NOV-2002
1912 validate_curr_conv_type(p_taiv_rec, x_return_status);
1913 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1914 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1915 l_return_status := x_return_status;
1916 RAISE G_EXCEPTION_HALT_VALIDATION;
1917 ELSE
1918 l_return_status := x_return_status; -- record that there was an error
1919 END IF;
1920 END IF;
1921 --End code added by pgomes on 15-NOV-2002
1922
1923 --validate_ibt_id(p_taiv_rec, x_return_status);
1924 --IF (x_return_status <> Okl_api.G_RET_STS_SUCCESS) THEN
1925 -- IF (x_return_status = Okl_api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1926 -- l_return_status := x_return_status;
1927 -- RAISE G_EXCEPTION_HALT_VALIDATION;
1928 --ELSE
1929 --l_return_status := x_return_status; -- record that there was an error
1930 --END IF;
1931 --END IF;
1932
1933 --validate_ixx_id(p_taiv_rec, x_return_status);
1934 --IF (x_return_status <> Okl_api.G_RET_STS_SUCCESS) THEN
1935 --IF (x_return_status = Okl_api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1936 --l_return_status := x_return_status;
1937 --RAISE G_EXCEPTION_HALT_VALIDATION;
1938 --ELSE
1939 --l_return_status := x_return_status; -- record that there was an error
1940 --END IF;
1941 --END IF;
1942
1943
1944 validate_khr_id(p_taiv_rec, x_return_status);
1945 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1946 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1947 l_return_status := x_return_status;
1948 RAISE G_EXCEPTION_HALT_VALIDATION;
1949 ELSE
1950 l_return_status := x_return_status; -- record that there was an error
1951 END IF;
1952 END IF;
1953
1954
1955 -- validate_irm_id(p_taiv_rec, x_return_status);
1956 -- IF (x_return_status <> Okl_api.G_RET_STS_SUCCESS) THEN
1957 -- IF (x_return_status = Okl_api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1958 -- l_return_status := x_return_status;
1959 -- RAISE G_EXCEPTION_HALT_VALIDATION;
1960 -- ELSE
1961 -- l_return_status := x_return_status; -- record that there was an error
1962 -- END IF;
1963 -- END IF;
1964
1965 -- validate_irt_id(p_taiv_rec, x_return_status);
1966 -- IF (x_return_status <> Okl_api.G_RET_STS_SUCCESS) THEN
1967 -- IF (x_return_status = Okl_api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1968 -- l_return_status := x_return_status;
1969 -- RAISE G_EXCEPTION_HALT_VALIDATION;
1970 -- ELSE
1971 -- l_return_status := x_return_status; -- record that there was an error
1972 -- END IF;
1973 -- END IF;
1974
1975 validate_cra_id(p_taiv_rec, x_return_status);
1976 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1977 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1978 l_return_status := x_return_status;
1979 RAISE G_EXCEPTION_HALT_VALIDATION;
1980 ELSE
1981 l_return_status := x_return_status; -- record that there was an error
1982 END IF;
1983 END IF;
1984
1985 validate_svf_id(p_taiv_rec, x_return_status);
1986 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1987 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1988 l_return_status := x_return_status;
1989 RAISE G_EXCEPTION_HALT_VALIDATION;
1990 ELSE
1991 l_return_status := x_return_status; -- record that there was an error
1992 END IF;
1993 END IF;
1994
1995 validate_tap_id(p_taiv_rec, x_return_status);
1996 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1997 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1998 l_return_status := x_return_status;
1999 RAISE G_EXCEPTION_HALT_VALIDATION;
2000 ELSE
2001 l_return_status := x_return_status; -- record that there was an error
2002 END IF;
2003 END IF;
2004
2005 validate_qte_id(p_taiv_rec, x_return_status);
2006 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2007 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2008 l_return_status := x_return_status;
2009 RAISE G_EXCEPTION_HALT_VALIDATION;
2010 ELSE
2011 l_return_status := x_return_status; -- record that there was an error
2012 END IF;
2013 END IF;
2014
2015 validate_tcn_id(p_taiv_rec, x_return_status);
2016 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2017 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2018 l_return_status := x_return_status;
2019 RAISE G_EXCEPTION_HALT_VALIDATION;
2020 ELSE
2021 l_return_status := x_return_status; -- record that there was an error
2022 END IF;
2023 END IF;
2024
2025 validate_tai_id_reverses(p_taiv_rec, x_return_status);
2026 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2027 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2028 l_return_status := x_return_status;
2029 RAISE G_EXCEPTION_HALT_VALIDATION;
2030 ELSE
2031 l_return_status := x_return_status; -- record that there was an error
2032 END IF;
2033 END IF;
2034
2035 validate_ipy_id(p_taiv_rec, x_return_status);
2036 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2037 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2038 l_return_status := x_return_status;
2039 RAISE G_EXCEPTION_HALT_VALIDATION;
2040 ELSE
2041 l_return_status := x_return_status; -- record that there was an error
2042 END IF;
2043 END IF;
2044
2045 validate_trx_status_code(p_taiv_rec, x_return_status);
2046 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2047 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2048 l_return_status := x_return_status;
2049 RAISE G_EXCEPTION_HALT_VALIDATION;
2050 ELSE
2051 l_return_status := x_return_status; -- record that there was an error
2052 END IF;
2053 END IF;
2054
2055 validate_set_of_books_id(p_taiv_rec, x_return_status);
2056 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2057 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2058 l_return_status := x_return_status;
2059 RAISE G_EXCEPTION_HALT_VALIDATION;
2060 ELSE
2061 l_return_status := x_return_status; -- record that there was an error
2062 END IF;
2063 END IF;
2064
2065 validate_try_id(p_taiv_rec, x_return_status);
2066 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2067 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2068 l_return_status := x_return_status;
2069 RAISE G_EXCEPTION_HALT_VALIDATION;
2070 ELSE
2071 l_return_status := x_return_status; -- record that there was an error
2072 END IF;
2073 END IF;
2074
2075 --Start code added by pgomes on 19-NOV-2002
2076 validate_pox_id(p_taiv_rec, x_return_status);
2077 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2078 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2079 l_return_status := x_return_status;
2080 RAISE G_EXCEPTION_HALT_VALIDATION;
2081 ELSE
2082 l_return_status := x_return_status; -- record that there was an error
2083 END IF;
2084 END IF;
2085 --End code added by pgomes on 19-NOV-2002
2086
2087 --Start code added by cklee on 13-FEB-2007
2088 validate_OKL_Src_BILLING_TRX(p_taiv_rec, x_return_status);
2089 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2090 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2091 l_return_status := x_return_status;
2092 RAISE G_EXCEPTION_HALT_VALIDATION;
2093 ELSE
2094 l_return_status := x_return_status; -- record that there was an error
2095 END IF;
2096 END IF;
2097 --End code added by cklee on 13-FEB-2007
2098
2099 validate_id(p_taiv_rec, x_return_status);
2100 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2101 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2102 l_return_status := x_return_status;
2103 RAISE G_EXCEPTION_HALT_VALIDATION;
2104 ELSE
2105 l_return_status := x_return_status; -- record that there was an error
2106 END IF;
2107 END IF;
2108
2109 validate_id(p_taiv_rec, x_return_status);
2110 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2111 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2112 l_return_status := x_return_status;
2113 RAISE G_EXCEPTION_HALT_VALIDATION;
2114 ELSE
2115 l_return_status := x_return_status; -- record that there was an error
2116 END IF;
2117 END IF;
2118
2119 validate_object_version_number(p_taiv_rec, x_return_status);
2120 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2121 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2122 l_return_status := x_return_status;
2123 RAISE G_EXCEPTION_HALT_VALIDATION;
2124 ELSE
2125 l_return_status := x_return_status; -- record that there was an error
2126 END IF;
2127 END IF;
2128
2129 validate_amount(p_taiv_rec, x_return_status);
2130 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2131 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2132 l_return_status := x_return_status;
2133 RAISE G_EXCEPTION_HALT_VALIDATION;
2134 ELSE
2135 l_return_status := x_return_status; -- record that there was an error
2136 END IF;
2137 END IF;
2138
2139 validate_date_invoiced(p_taiv_rec, x_return_status);
2140 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2141 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2142 l_return_status := x_return_status;
2143 RAISE G_EXCEPTION_HALT_VALIDATION;
2144 ELSE
2145 l_return_status := x_return_status; -- record that there was an error
2146 END IF;
2147 END IF;
2148
2149 validate_date_entered(p_taiv_rec, x_return_status);
2150 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2151 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2152 l_return_status := x_return_status;
2153 RAISE G_EXCEPTION_HALT_VALIDATION;
2154 ELSE
2155 l_return_status := x_return_status; -- record that there was an error
2156 END IF;
2157 END IF;
2158
2159 validate_org_id(p_taiv_rec, x_return_status);
2160 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2161 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2162 l_return_status := x_return_status;
2163 RAISE G_EXCEPTION_HALT_VALIDATION;
2164 ELSE
2165 l_return_status := x_return_status; -- record that there was an error
2166 END IF;
2167 END IF;
2168
2169 -- for LE Uptake project 08-11-2006
2170 IF (p_taiv_rec.legal_entity_id = Okl_Api.G_MISS_NUM OR (p_taiv_rec.legal_entity_id IS NULL))
2171 THEN
2172 RAISE G_EXCEPTION_HALT_VALIDATION;
2173 -- Raise error
2174
2175 ELSE
2176 validate_legal_entity_id(p_taiv_rec, x_return_status);
2177 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2178 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2179 l_return_status := x_return_status;
2180 RAISE G_EXCEPTION_HALT_VALIDATION;
2181 ELSE
2182 l_return_status := x_return_status; -- record that there was an error
2183 END IF;
2184 END IF;
2185 END IF;
2186 -- for LE Uptake project 08-11-2006
2187
2188 -- End Code , Sunil T. Mathew (04/16/2001)
2189
2190 -- sjalasut: added new procedure validate_trx_number to ensure that the trx_number is uniq in the
2191 -- current operating unit. per bug 4057822
2192 -- sjalasut: start of code changes
2193 validate_trx_number(p_taiv_rec, x_return_status);
2194 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2195 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2196 l_return_status := x_return_status;
2197 RAISE G_EXCEPTION_HALT_VALIDATION;
2198 ELSE
2199 l_return_status := x_return_status; -- record that there was an error
2200 END IF;
2201 END IF;
2202 -- sjalasut: end of code changes
2203
2204 RETURN(l_return_status);
2205 END Validate_Attributes;
2206
2207 ---------------------------------------------------------------------------
2208 -- PROCEDURE Validate_Record
2209 ---------------------------------------------------------------------------
2210 -----------------------------------------------
2211 -- Validate_Record for:OKL_TRX_AR_INVOICES_V --
2212 -----------------------------------------------
2213 FUNCTION Validate_Record (
2214 p_taiv_rec IN taiv_rec_type
2215 ) RETURN VARCHAR2 IS
2216 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2217 BEGIN
2218 RETURN (l_return_status);
2219 END Validate_Record;
2220
2221 ---------------------------------------------------------------------------
2222 -- PROCEDURE Migrate
2223 ---------------------------------------------------------------------------
2224 PROCEDURE migrate (
2225 p_from IN taiv_rec_type,
2226 p_to IN OUT NOCOPY tai_rec_type
2227 ) IS
2228 BEGIN
2229 p_to.id := p_from.id;
2230 p_to.currency_code := p_from.currency_code;
2231
2232 --Start change by pgomes on 15-NOV-2002
2233 p_to.currency_conversion_type := p_from.currency_conversion_type;
2234 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
2235 p_to.currency_conversion_date := p_from.currency_conversion_date;
2236 --End change by pgomes on 15-NOV-2002
2237
2238 p_to.ibt_id := p_from.ibt_id;
2239 p_to.ixx_id := p_from.ixx_id;
2240 p_to.khr_id := p_from.khr_id;
2241 p_to.irm_id := p_from.irm_id;
2242 p_to.irt_id := p_from.irt_id;
2243 p_to.cra_id := p_from.cra_id;
2244 p_to.svf_id := p_from.svf_id;
2245 p_to.tap_id := p_from.tap_id;
2246 p_to.qte_id := p_from.qte_id;
2247 p_to.tcn_id := p_from.tcn_id;
2248 p_to.tai_id_reverses := p_from.tai_id_reverses;
2249 p_to.date_entered := p_from.date_entered;
2250 p_to.date_invoiced := p_from.date_invoiced;
2251 p_to.amount := p_from.amount;
2252 p_to.object_version_number := p_from.object_version_number;
2253 p_to.amount_applied := p_from.amount_applied;
2254 p_to.request_id := p_from.request_id;
2255 p_to.program_application_id := p_from.program_application_id;
2256 p_to.program_id := p_from.program_id;
2257 p_to.program_update_date := p_from.program_update_date;
2258 p_to.org_id := p_from.org_id;
2259 p_to.IPY_ID := p_from.IPY_ID;
2260 p_to.SET_OF_BOOKS_ID := p_from.SET_OF_BOOKS_ID;
2261 p_to.TRX_STATUS_CODE := p_from.TRX_STATUS_CODE;
2262 p_to.TRY_ID := p_from.TRY_ID;
2263 p_to.TRX_NUMBER := p_from.TRX_NUMBER;
2264 p_to.CLG_ID := p_from.CLG_ID;
2265 p_to.POX_ID := p_from.POX_ID;
2266 p_to.CPY_ID := p_from.CPY_ID;
2267 p_to.attribute_category := p_from.attribute_category;
2268 p_to.attribute1 := p_from.attribute1;
2269 p_to.attribute2 := p_from.attribute2;
2270 p_to.attribute3 := p_from.attribute3;
2271 p_to.attribute4 := p_from.attribute4;
2272 p_to.attribute5 := p_from.attribute5;
2273 p_to.attribute6 := p_from.attribute6;
2274 p_to.attribute7 := p_from.attribute7;
2275 p_to.attribute8 := p_from.attribute8;
2276 p_to.attribute9 := p_from.attribute9;
2277 p_to.attribute10 := p_from.attribute10;
2278 p_to.attribute11 := p_from.attribute11;
2279 p_to.attribute12 := p_from.attribute12;
2280 p_to.attribute13 := p_from.attribute13;
2281 p_to.attribute14 := p_from.attribute14;
2282 p_to.attribute15 := p_from.attribute15;
2283 p_to.created_by := p_from.created_by;
2284 p_to.creation_date := p_from.creation_date;
2285 p_to.last_updated_by := p_from.last_updated_by;
2286 p_to.last_update_date := p_from.last_update_date;
2287 p_to.last_update_login := p_from.last_update_login;
2288 p_to.legal_entity_id := p_from.legal_entity_id; -- for LE Uptake project 08-11-2006
2289
2290 -- start:30-Jan-07 cklee Billing R12 project
2291 p_to.Investor_Agreement_Number := p_from.Investor_Agreement_Number;
2292 p_to.Investor_Name := p_from.Investor_Name;
2293 p_to.OKL_SOURCE_BILLING_TRX := p_from.OKL_SOURCE_BILLING_TRX;
2294 p_to.INF_ID := p_from.INF_ID;
2295 p_to.INVOICE_PULL_YN := p_from.INVOICE_PULL_YN;
2296 p_to.CONSOLIDATED_INVOICE_NUMBER := p_from.CONSOLIDATED_INVOICE_NUMBER;
2297 p_to.DUE_DATE := p_from.DUE_DATE;
2298 p_to.ISI_ID := p_from.ISI_ID;
2299 p_to.RECEIVABLES_INVOICE_ID := p_from.RECEIVABLES_INVOICE_ID;
2300 p_to.CUST_TRX_TYPE_ID := p_from.CUST_TRX_TYPE_ID;
2301 p_to.CUSTOMER_BANK_ACCOUNT_ID := p_from.CUSTOMER_BANK_ACCOUNT_ID;
2302 p_to.TAX_EXEMPT_FLAG := p_from.TAX_EXEMPT_FLAG;
2303 p_to.TAX_EXEMPT_REASON_CODE := p_from.TAX_EXEMPT_REASON_CODE;
2304 p_to.REFERENCE_LINE_ID := p_from.REFERENCE_LINE_ID;
2305 p_to.PRIVATE_LABEL := p_from.PRIVATE_LABEL;
2306
2307 -- end:30-Jan-07 cklee Billing R12 project
2308 --gkhuntet start 02-Nov-07
2309 p_to.TRANSACTION_DATE := p_from.TRANSACTION_DATE ;
2310 --gkhuntet end 02-Nov-07
2311 END migrate;
2312
2313 PROCEDURE migrate (
2314 p_from IN tai_rec_type,
2315 p_to IN OUT NOCOPY taiv_rec_type
2316 ) IS
2317 BEGIN
2318 p_to.id := p_from.id;
2319 p_to.currency_code := p_from.currency_code;
2320
2321 --Start change by pgomes on 15-NOV-2002
2322 p_to.currency_conversion_type := p_from.currency_conversion_type;
2323 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
2324 p_to.currency_conversion_date := p_from.currency_conversion_date;
2325 --End change by pgomes on 15-NOV-2002
2326
2327 p_to.ibt_id := p_from.ibt_id;
2328 p_to.ixx_id := p_from.ixx_id;
2329 p_to.khr_id := p_from.khr_id;
2330 p_to.irm_id := p_from.irm_id;
2331 p_to.irt_id := p_from.irt_id;
2332 p_to.cra_id := p_from.cra_id;
2333 p_to.svf_id := p_from.svf_id;
2334 p_to.tap_id := p_from.tap_id;
2335 p_to.qte_id := p_from.qte_id;
2336 p_to.tcn_id := p_from.tcn_id;
2337 p_to.tai_id_reverses := p_from.tai_id_reverses;
2338 p_to.date_entered := p_from.date_entered;
2339 p_to.date_invoiced := p_from.date_invoiced;
2340 p_to.amount := p_from.amount;
2341 p_to.object_version_number := p_from.object_version_number;
2342 p_to.amount_applied := p_from.amount_applied;
2343 p_to.request_id := p_from.request_id;
2344 p_to.program_application_id := p_from.program_application_id;
2345 p_to.program_id := p_from.program_id;
2346 p_to.program_update_date := p_from.program_update_date;
2347 p_to.org_id := p_from.org_id;
2348 p_to.IPY_ID := p_from.IPY_ID;
2349 p_to.SET_OF_BOOKS_ID := p_from.SET_OF_BOOKS_ID;
2350 p_to.TRX_STATUS_CODE := p_from.TRX_STATUS_CODE;
2351 p_to.TRY_ID := p_from.TRY_ID;
2352 p_to.TRX_NUMBER := p_from.TRX_NUMBER;
2353 p_to.CLG_ID := p_from.CLG_ID;
2354 p_to.POX_ID := p_from.POX_ID;
2355 p_to.CPY_ID := p_from.CPY_ID;
2356 p_to.attribute_category := p_from.attribute_category;
2357 p_to.attribute1 := p_from.attribute1;
2358 p_to.attribute2 := p_from.attribute2;
2359 p_to.attribute3 := p_from.attribute3;
2360 p_to.attribute4 := p_from.attribute4;
2361 p_to.attribute5 := p_from.attribute5;
2362 p_to.attribute6 := p_from.attribute6;
2363 p_to.attribute7 := p_from.attribute7;
2364 p_to.attribute8 := p_from.attribute8;
2365 p_to.attribute9 := p_from.attribute9;
2366 p_to.attribute10 := p_from.attribute10;
2367 p_to.attribute11 := p_from.attribute11;
2368 p_to.attribute12 := p_from.attribute12;
2369 p_to.attribute13 := p_from.attribute13;
2370 p_to.attribute14 := p_from.attribute14;
2371 p_to.attribute15 := p_from.attribute15;
2372 p_to.created_by := p_from.created_by;
2373 p_to.creation_date := p_from.creation_date;
2374 p_to.last_updated_by := p_from.last_updated_by;
2375 p_to.last_update_date := p_from.last_update_date;
2376 p_to.last_update_login := p_from.last_update_login;
2377 p_to.legal_entity_id := p_from.legal_entity_id; -- for LE Uptake project 08-11-2006
2378
2379 -- start:30-Jan-07 cklee Billing R12 project
2380 p_to.Investor_Agreement_Number := p_from.Investor_Agreement_Number;
2381 p_to.Investor_Name := p_from.Investor_Name;
2382 p_to.OKL_SOURCE_BILLING_TRX := p_from.OKL_SOURCE_BILLING_TRX;
2383 p_to.INF_ID := p_from.INF_ID;
2384 p_to.INVOICE_PULL_YN := p_from.INVOICE_PULL_YN;
2385 p_to.CONSOLIDATED_INVOICE_NUMBER := p_from.CONSOLIDATED_INVOICE_NUMBER;
2386 p_to.DUE_DATE := p_from.DUE_DATE;
2387 p_to.ISI_ID := p_from.ISI_ID;
2388 p_to.RECEIVABLES_INVOICE_ID := p_from.RECEIVABLES_INVOICE_ID;
2389 p_to.CUST_TRX_TYPE_ID := p_from.CUST_TRX_TYPE_ID;
2390 p_to.CUSTOMER_BANK_ACCOUNT_ID := p_from.CUSTOMER_BANK_ACCOUNT_ID;
2391 p_to.TAX_EXEMPT_FLAG := p_from.TAX_EXEMPT_FLAG;
2392 p_to.TAX_EXEMPT_REASON_CODE := p_from.TAX_EXEMPT_REASON_CODE;
2393 p_to.REFERENCE_LINE_ID := p_from.REFERENCE_LINE_ID;
2394 p_to.PRIVATE_LABEL := p_from.PRIVATE_LABEL;
2395
2396 -- end:30-Jan-07 cklee Billing R12 project
2397 --gkhuntet start 02-Nov-07
2398 p_to.TRANSACTION_DATE := p_from.TRANSACTION_DATE ;
2399 --gkhuntet end 02-Nov-07
2400 END migrate;
2401
2402 PROCEDURE migrate (
2403 p_from IN taiv_rec_type,
2404 p_to IN OUT NOCOPY OklTrxArInvoicesTlRecType
2405 ) IS
2406 BEGIN
2407 p_to.id := p_from.id;
2408 p_to.sfwt_flag := p_from.sfwt_flag;
2409 p_to.description := p_from.description;
2410 p_to.created_by := p_from.created_by;
2411 p_to.creation_date := p_from.creation_date;
2412 p_to.last_updated_by := p_from.last_updated_by;
2413 p_to.last_update_date := p_from.last_update_date;
2414 p_to.last_update_login := p_from.last_update_login;
2415 END migrate;
2416 PROCEDURE migrate (
2417 p_from IN OklTrxArInvoicesTlRecType,
2418 p_to IN OUT NOCOPY taiv_rec_type
2419 ) IS
2420 BEGIN
2421 p_to.id := p_from.id;
2422 p_to.sfwt_flag := p_from.sfwt_flag;
2423 p_to.description := p_from.description;
2424 p_to.created_by := p_from.created_by;
2425 p_to.creation_date := p_from.creation_date;
2426 p_to.last_updated_by := p_from.last_updated_by;
2427 p_to.last_update_date := p_from.last_update_date;
2428 p_to.last_update_login := p_from.last_update_login;
2429 END migrate;
2430
2431 ---------------------------------------------------------------------------
2432 -- PROCEDURE validate_row
2433 ---------------------------------------------------------------------------
2434 --------------------------------------------
2435 -- validate_row for:OKL_TRX_AR_INVOICES_V --
2436 --------------------------------------------
2437 PROCEDURE validate_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_taiv_rec IN taiv_rec_type) IS
2444
2445 l_api_version CONSTANT NUMBER := 1;
2446 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2447 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2448 l_taiv_rec taiv_rec_type := p_taiv_rec;
2449 l_tai_rec tai_rec_type;
2450 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
2451 BEGIN
2452 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2453 G_PKG_NAME,
2454 p_init_msg_list,
2455 l_api_version,
2456 p_api_version,
2457 '_PVT',
2458 x_return_status);
2459 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2460 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2461 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2462 RAISE Okl_Api.G_EXCEPTION_ERROR;
2463 END IF;
2464 --- Validate all non-missing attributes (Item Level Validation)
2465 l_return_status := Validate_Attributes(l_taiv_rec);
2466 --- If any errors happen abort API
2467 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2468 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2469 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2470 RAISE Okl_Api.G_EXCEPTION_ERROR;
2471 END IF;
2472 l_return_status := Validate_Record(l_taiv_rec);
2473 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2474 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2475 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2476 RAISE Okl_Api.G_EXCEPTION_ERROR;
2477 END IF;
2478 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2479 EXCEPTION
2480 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2481 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2482 (
2483 l_api_name,
2484 G_PKG_NAME,
2485 'OKL_API.G_RET_STS_ERROR',
2486 x_msg_count,
2487 x_msg_data,
2488 '_PVT'
2489 );
2490 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2491 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2492 (
2493 l_api_name,
2494 G_PKG_NAME,
2495 'OKL_API.G_RET_STS_UNEXP_ERROR',
2496 x_msg_count,
2497 x_msg_data,
2498 '_PVT'
2499 );
2500 WHEN OTHERS THEN
2501 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2502 (
2503 l_api_name,
2504 G_PKG_NAME,
2505 'OTHERS',
2506 x_msg_count,
2507 x_msg_data,
2508 '_PVT'
2509 );
2510 END validate_row;
2511 ------------------------------------------
2512 -- PL/SQL TBL validate_row for:TAIV_TBL --
2513 ------------------------------------------
2514 PROCEDURE validate_row(
2515 p_api_version IN NUMBER,
2516 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2517 x_return_status OUT NOCOPY VARCHAR2,
2518 x_msg_count OUT NOCOPY NUMBER,
2519 x_msg_data OUT NOCOPY VARCHAR2,
2520 p_taiv_tbl IN taiv_tbl_type) IS
2521
2522 l_api_version CONSTANT NUMBER := 1;
2523 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2524 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2525 -- Begin Post-Generation Change
2526 -- overall error status
2527 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2528 -- End Post-Generation Change
2529 i NUMBER := 0;
2530 BEGIN
2531 Okl_Api.init_msg_list(p_init_msg_list);
2532 -- Make sure PL/SQL table has records in it before passing
2533 IF (p_taiv_tbl.COUNT > 0) THEN
2534 i := p_taiv_tbl.FIRST;
2535 LOOP
2536 validate_row (
2537 p_api_version => p_api_version,
2538 p_init_msg_list => Okl_Api.G_FALSE,
2539 x_return_status => x_return_status,
2540 x_msg_count => x_msg_count,
2541 x_msg_data => x_msg_data,
2542 p_taiv_rec => p_taiv_tbl(i));
2543
2544 -- Begin Post-Generation Change
2545 -- store the highest degree of error
2546 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2547 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2548 l_overall_status := x_return_status;
2549 END IF;
2550 END IF;
2551 -- End Post-Generation Change
2552 EXIT WHEN (i = p_taiv_tbl.LAST);
2553 i := p_taiv_tbl.NEXT(i);
2554 END LOOP;
2555 -- Begin Post-Generation Change
2556 -- return overall status
2557 x_return_status := l_overall_status;
2558 -- End Post-Generation Change
2559
2560 END IF;
2561 EXCEPTION
2562 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2563 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2564 (
2565 l_api_name,
2566 G_PKG_NAME,
2567 'OKL_API.G_RET_STS_ERROR',
2568 x_msg_count,
2569 x_msg_data,
2570 '_PVT'
2571 );
2572 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2573 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2574 (
2575 l_api_name,
2576 G_PKG_NAME,
2577 'OKL_API.G_RET_STS_UNEXP_ERROR',
2578 x_msg_count,
2579 x_msg_data,
2580 '_PVT'
2581 );
2582 WHEN OTHERS THEN
2583 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2584 (
2585 l_api_name,
2586 G_PKG_NAME,
2587 'OTHERS',
2588 x_msg_count,
2589 x_msg_data,
2590 '_PVT'
2591 );
2592 END validate_row;
2593
2594 ---------------------------------------------------------------------------
2595 -- PROCEDURE insert_row
2596 ---------------------------------------------------------------------------
2597 ------------------------------------------
2598 -- insert_row for:OKL_TRX_AR_INVOICES_B --
2599 ------------------------------------------
2600 PROCEDURE insert_row(
2601 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2602 x_return_status OUT NOCOPY VARCHAR2,
2603 x_msg_count OUT NOCOPY NUMBER,
2604 x_msg_data OUT NOCOPY VARCHAR2,
2605 p_tai_rec IN tai_rec_type,
2606 x_tai_rec OUT NOCOPY tai_rec_type) IS
2607
2608 l_api_version CONSTANT NUMBER := 1;
2609 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2610 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2611 l_tai_rec tai_rec_type := p_tai_rec;
2612 l_def_tai_rec tai_rec_type;
2613 ----------------------------------------------
2614 -- Set_Attributes for:OKL_TRX_AR_INVOICES_B --
2615 ----------------------------------------------
2616 FUNCTION Set_Attributes (
2617 p_tai_rec IN tai_rec_type,
2618 x_tai_rec OUT NOCOPY tai_rec_type
2619 ) RETURN VARCHAR2 IS
2620 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2621 BEGIN
2622 x_tai_rec := p_tai_rec;
2623 RETURN(l_return_status);
2624 END Set_Attributes;
2625 BEGIN
2626 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2627 p_init_msg_list,
2628 '_PVT',
2629 x_return_status);
2630 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2631 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2632 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2633 RAISE Okl_Api.G_EXCEPTION_ERROR;
2634 END IF;
2635
2636 --gkhuntet start 02-Nov-07
2637 IF(l_tai_rec.TRANSACTION_DATE IS NULL OR l_tai_rec.TRANSACTION_DATE = Okl_Api.G_MISS_DATE)
2638 THEN
2639 l_tai_rec.TRANSACTION_DATE := SYSDATE;
2640 END IF;
2641 --gkhuntet end 02-Nov-07
2642
2643 --- Setting item attributes
2644 l_return_status := Set_Attributes(
2645 p_tai_rec, -- IN
2646 l_tai_rec); -- OUT
2647 --- If any errors happen abort API
2648 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2649 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2650 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2651 RAISE Okl_Api.G_EXCEPTION_ERROR;
2652 END IF;
2653
2654 INSERT INTO OKL_TRX_AR_INVOICES_B(
2655 id,
2656 currency_code,
2657 --Start change by pgomes on 15-NOV-2002
2658 currency_conversion_type,
2659 currency_conversion_rate,
2660 currency_conversion_date,
2661 --End change by pgomes on 15-NOV-2002
2662 ibt_id,
2663 ixx_id,
2664 khr_id,
2665 irm_id,
2666 irt_id,
2667 cra_id,
2668 svf_id,
2669 tap_id,
2670 qte_id,
2671 tcn_id,
2672 tai_id_reverses,
2673 ipy_id, -- Added after postgen trx_type removal
2674 trx_status_code, -- Added after postgen trx_type removal
2675 set_of_books_id, -- Added after postgen trx_type removal
2676 try_id, -- Added after postgen trx_type removal
2677 date_entered,
2678 date_invoiced,
2679 amount,
2680 object_version_number,
2681 amount_applied,
2682 request_id,
2683 program_application_id,
2684 program_id,
2685 program_update_date,
2686 org_id,
2687 TRX_NUMBER,
2688 CLG_ID,
2689 POX_ID,
2690 CPY_ID,
2691 attribute_category,
2692 attribute1,
2693 attribute2,
2694 attribute3,
2695 attribute4,
2696 attribute5,
2697 attribute6,
2698 attribute7,
2699 attribute8,
2700 attribute9,
2701 attribute10,
2702 attribute11,
2703 attribute12,
2704 attribute13,
2705 attribute14,
2706 attribute15,
2707 created_by,
2708 creation_date,
2709 last_updated_by,
2710 last_update_date,
2711 last_update_login,
2712 legal_entity_id,-- for LE Uptake project 08-11-2006
2713
2714 -- start:30-Jan-07 cklee Billing R12 project
2715 Investor_Agreement_Number,
2716 Investor_Name,
2717 OKL_SOURCE_BILLING_TRX,
2718 INF_ID,
2719 INVOICE_PULL_YN,
2720 CONSOLIDATED_INVOICE_NUMBER,
2721 DUE_DATE,
2722 ISI_ID,
2723 RECEIVABLES_INVOICE_ID,
2724 CUST_TRX_TYPE_ID,
2725 CUSTOMER_BANK_ACCOUNT_ID,
2726 TAX_EXEMPT_FLAG,
2727 TAX_EXEMPT_REASON_CODE,
2728 REFERENCE_LINE_ID,
2729 PRIVATE_LABEL,
2730 -- end:30-Jan-07 cklee Billing R12 project
2731 --gkhuntet start 02-Nov-07
2732 TRANSACTION_DATE
2733 --gkhuntet end 02-Nov-07
2734 )
2735
2736 VALUES (
2737 l_tai_rec.id,
2738 l_tai_rec.currency_code,
2739 --Start change by pgomes on 15-NOV-2002
2740 l_tai_rec.currency_conversion_type,
2741 l_tai_rec.currency_conversion_rate,
2742 l_tai_rec.currency_conversion_date,
2743 --End change by pgomes on 15-NOV-2002
2744 l_tai_rec.ibt_id,
2745 l_tai_rec.ixx_id,
2746 l_tai_rec.khr_id,
2747 l_tai_rec.irm_id,
2748 l_tai_rec.irt_id,
2749 l_tai_rec.cra_id,
2750 l_tai_rec.svf_id,
2751 l_tai_rec.tap_id,
2752 l_tai_rec.qte_id,
2753 l_tai_rec.tcn_id,
2754 l_tai_rec.tai_id_reverses,
2755 l_tai_rec.ipy_id, -- Added after postgen trx_type removal
2756 l_tai_rec.trx_status_code, -- Added after postgen trx_type removal
2757 l_tai_rec.set_of_books_id, -- Added after postgen trx_type removal
2758 l_tai_rec.try_id, -- Added after postgen trx_type removal
2759 l_tai_rec.date_entered,
2760 l_tai_rec.date_invoiced,
2761 l_tai_rec.amount,
2762 l_tai_rec.object_version_number,
2763 l_tai_rec.amount_applied,
2764 l_tai_rec.request_id,
2765 l_tai_rec.program_application_id,
2766 l_tai_rec.program_id,
2767 l_tai_rec.program_update_date,
2768 l_tai_rec.org_id,
2769 l_tai_rec.TRX_NUMBER,
2770 l_tai_rec.CLG_ID,
2771 l_tai_rec.POX_ID,
2772 l_tai_rec.CPY_ID,
2773 l_tai_rec.attribute_category,
2774 l_tai_rec.attribute1,
2775 l_tai_rec.attribute2,
2776 l_tai_rec.attribute3,
2777 l_tai_rec.attribute4,
2778 l_tai_rec.attribute5,
2779 l_tai_rec.attribute6,
2780 l_tai_rec.attribute7,
2781 l_tai_rec.attribute8,
2782 l_tai_rec.attribute9,
2783 l_tai_rec.attribute10,
2784 l_tai_rec.attribute11,
2785 l_tai_rec.attribute12,
2786 l_tai_rec.attribute13,
2787 l_tai_rec.attribute14,
2788 l_tai_rec.attribute15,
2789 l_tai_rec.created_by,
2790 l_tai_rec.creation_date,
2791 l_tai_rec.last_updated_by,
2792 l_tai_rec.last_update_date,
2793 l_tai_rec.last_update_login,
2794 l_tai_rec.legal_entity_id, -- for LE Uptake project 08-11-2006
2795
2796 -- start:30-Jan-07 cklee Billing R12 project
2797 l_tai_rec.Investor_Agreement_Number,
2798 l_tai_rec.Investor_Name,
2799 l_tai_rec.OKL_SOURCE_BILLING_TRX,
2800 l_tai_rec.INF_ID,
2801 l_tai_rec.INVOICE_PULL_YN,
2802 l_tai_rec.CONSOLIDATED_INVOICE_NUMBER,
2803 l_tai_rec.DUE_DATE,
2804 l_tai_rec.ISI_ID,
2805 l_tai_rec.RECEIVABLES_INVOICE_ID,
2806 l_tai_rec.CUST_TRX_TYPE_ID,
2807 l_tai_rec.CUSTOMER_BANK_ACCOUNT_ID,
2808 l_tai_rec.TAX_EXEMPT_FLAG,
2809 l_tai_rec.TAX_EXEMPT_REASON_CODE,
2810 l_tai_rec.REFERENCE_LINE_ID,
2811 l_tai_rec.PRIVATE_LABEL,
2812 -- end:30-Jan-07 cklee Billing R12 project
2813 --gkhuntet start 02-Nov-07
2814 l_tai_rec.TRANSACTION_DATE
2815 --gkhuntet end 02-Nov-07
2816 );
2817
2818
2819 -- Set OUT values
2820 x_tai_rec := l_tai_rec;
2821 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2822 EXCEPTION
2823 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2824 x_return_status := 'E';
2825 /*
2826 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2827 (
2828 l_api_name,
2829 G_PKG_NAME,
2830 'OKL_API.G_RET_STS_ERROR',
2831 x_msg_count,
2832 x_msg_data,
2833 '_PVT'
2834 );
2835 */
2836 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2837 x_return_status := 'U';
2838 /*
2839 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2840 (
2841 l_api_name,
2842 G_PKG_NAME,
2843 'OKL_API.G_RET_STS_UNEXP_ERROR',
2844 x_msg_count,
2845 x_msg_data,
2846 '_PVT'
2847 );
2848 */
2849 WHEN OTHERS THEN
2850 x_return_status := 'U';
2851 /*
2852 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2853 (
2854 l_api_name,
2855 G_PKG_NAME,
2856 'OTHERS',
2857 x_msg_count,
2858 x_msg_data,
2859 '_PVT'
2860 );
2861 */
2862 END insert_row;
2863 -------------------------------------------
2864 -- insert_row for:OKL_TRX_AR_INVOICES_TL --
2865 -------------------------------------------
2866 PROCEDURE insert_row(
2867 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2868 x_return_status OUT NOCOPY VARCHAR2,
2869 x_msg_count OUT NOCOPY NUMBER,
2870 x_msg_data OUT NOCOPY VARCHAR2,
2871 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType,
2872 x_okl_trx_ar_invoices_tl_rec OUT NOCOPY OklTrxArInvoicesTlRecType) IS
2873
2874 l_api_version CONSTANT NUMBER := 1;
2875 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
2876 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2877 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType := p_okl_trx_ar_invoices_tl_rec;
2878 ldefokltrxarinvoicestlrec OklTrxArInvoicesTlRecType;
2879 CURSOR get_languages IS
2880 SELECT *
2881 FROM FND_LANGUAGES
2882 WHERE INSTALLED_FLAG IN ('I', 'B');
2883 -----------------------------------------------
2884 -- Set_Attributes for:OKL_TRX_AR_INVOICES_TL --
2885 -----------------------------------------------
2886 FUNCTION Set_Attributes (
2887 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType,
2888 x_okl_trx_ar_invoices_tl_rec OUT NOCOPY OklTrxArInvoicesTlRecType
2889 ) RETURN VARCHAR2 IS
2890 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2891 BEGIN
2892 x_okl_trx_ar_invoices_tl_rec := p_okl_trx_ar_invoices_tl_rec;
2893 x_okl_trx_ar_invoices_tl_rec.LANGUAGE := USERENV('LANG');
2894 x_okl_trx_ar_invoices_tl_rec.SOURCE_LANG := USERENV('LANG');
2895 RETURN(l_return_status);
2896 END Set_Attributes;
2897 BEGIN
2898 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2899 p_init_msg_list,
2900 '_PVT',
2901 x_return_status);
2902 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2903 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2904 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2905 RAISE Okl_Api.G_EXCEPTION_ERROR;
2906 END IF;
2907 --- Setting item attributes
2908 l_return_status := Set_Attributes(
2909 p_okl_trx_ar_invoices_tl_rec, -- IN
2910 l_okl_trx_ar_invoices_tl_rec); -- OUT
2911 --- If any errors happen abort API
2912 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2913 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2914 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2915 RAISE Okl_Api.G_EXCEPTION_ERROR;
2916 END IF;
2917 FOR l_lang_rec IN get_languages LOOP
2918 l_okl_trx_ar_invoices_tl_rec.LANGUAGE := l_lang_rec.language_code;
2919 INSERT INTO OKL_TRX_AR_INVOICES_TL(
2920 id,
2921 LANGUAGE,
2922 source_lang,
2923 sfwt_flag,
2924 description,
2925 created_by,
2926 creation_date,
2927 last_updated_by,
2928 last_update_date,
2929 last_update_login)
2930 VALUES (
2931 l_okl_trx_ar_invoices_tl_rec.id,
2932 l_okl_trx_ar_invoices_tl_rec.LANGUAGE,
2933 l_okl_trx_ar_invoices_tl_rec.source_lang,
2934 l_okl_trx_ar_invoices_tl_rec.sfwt_flag,
2935 l_okl_trx_ar_invoices_tl_rec.description,
2936 l_okl_trx_ar_invoices_tl_rec.created_by,
2937 l_okl_trx_ar_invoices_tl_rec.creation_date,
2938 l_okl_trx_ar_invoices_tl_rec.last_updated_by,
2939 l_okl_trx_ar_invoices_tl_rec.last_update_date,
2940 l_okl_trx_ar_invoices_tl_rec.last_update_login);
2941 END LOOP;
2942 -- Set OUT values
2943 x_okl_trx_ar_invoices_tl_rec := l_okl_trx_ar_invoices_tl_rec;
2944 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2945 EXCEPTION
2946 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2947 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2948 (
2949 l_api_name,
2950 G_PKG_NAME,
2951 'OKL_API.G_RET_STS_ERROR',
2952 x_msg_count,
2953 x_msg_data,
2954 '_PVT'
2955 );
2956 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2957 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2958 (
2959 l_api_name,
2960 G_PKG_NAME,
2961 'OKL_API.G_RET_STS_UNEXP_ERROR',
2962 x_msg_count,
2963 x_msg_data,
2964 '_PVT'
2965 );
2966 WHEN OTHERS THEN
2967 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2968 (
2969 l_api_name,
2970 G_PKG_NAME,
2971 'OTHERS',
2972 x_msg_count,
2973 x_msg_data,
2974 '_PVT'
2975 );
2976 END insert_row;
2977 ------------------------------------------
2978 -- insert_row for:OKL_TRX_AR_INVOICES_V --
2979 ------------------------------------------
2980 PROCEDURE insert_row(
2981 p_api_version IN NUMBER,
2982 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2983 x_return_status OUT NOCOPY VARCHAR2,
2984 x_msg_count OUT NOCOPY NUMBER,
2985 x_msg_data OUT NOCOPY VARCHAR2,
2986 p_taiv_rec IN taiv_rec_type,
2987 x_taiv_rec OUT NOCOPY taiv_rec_type) IS
2988
2989 l_api_version CONSTANT NUMBER := 1;
2990 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2991 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2992 l_taiv_rec taiv_rec_type;
2993 l_def_taiv_rec taiv_rec_type;
2994 l_tai_rec tai_rec_type;
2995 lx_tai_rec tai_rec_type;
2996 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
2997 lx_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
2998 l_legal_entity_id OKL_TRX_AR_INVOICES_V.legal_entity_id%TYPE; -- for LE Uptake project 08-11-2006
2999 -------------------------------
3000 -- FUNCTION fill_who_columns --
3001 -------------------------------
3002 FUNCTION fill_who_columns (
3003 p_taiv_rec IN taiv_rec_type
3004 ) RETURN taiv_rec_type IS
3005 l_taiv_rec taiv_rec_type := p_taiv_rec;
3006 BEGIN
3007 l_taiv_rec.CREATION_DATE := SYSDATE;
3008 l_taiv_rec.CREATED_BY := Fnd_Global.USER_ID;
3009 l_taiv_rec.LAST_UPDATE_DATE := l_taiv_rec.CREATION_DATE;
3010 l_taiv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
3011 l_taiv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
3012 RETURN(l_taiv_rec);
3013 END fill_who_columns;
3014 ----------------------------------------------
3015 -- Set_Attributes for:OKL_TRX_AR_INVOICES_V --
3016 ----------------------------------------------
3017 FUNCTION Set_Attributes (
3018 p_taiv_rec IN taiv_rec_type,
3019 x_taiv_rec OUT NOCOPY taiv_rec_type
3020 ) RETURN VARCHAR2 IS
3021 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3022 BEGIN
3023 x_taiv_rec := p_taiv_rec;
3024 x_taiv_rec.OBJECT_VERSION_NUMBER := 1;
3025 x_taiv_rec.SFWT_FLAG := 'N';
3026
3027 IF (x_taiv_rec.request_id IS NULL OR x_taiv_rec.request_id = Okl_Api.G_MISS_NUM) THEN
3028 SELECT
3029 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
3030 DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
3031 DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
3032 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
3033 INTO
3034 x_taiv_rec.request_id,
3035 x_taiv_rec.program_application_id,
3036 x_taiv_rec.program_id,
3037 x_taiv_rec.program_update_date
3038 FROM dual;
3039 END IF;
3040
3041
3042 RETURN(l_return_status);
3043 END Set_Attributes;
3044 BEGIN
3045 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
3046 G_PKG_NAME,
3047 p_init_msg_list,
3048 l_api_version,
3049 p_api_version,
3050 '_PVT',
3051 x_return_status);
3052 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3053 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3054 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3055 RAISE Okl_Api.G_EXCEPTION_ERROR;
3056 END IF;
3057 l_taiv_rec := null_out_defaults(p_taiv_rec);
3058 -- Set primary key value
3059 l_taiv_rec.ID := get_seq_id;
3060
3061 -- This is to support the screen
3062 IF l_taiv_rec.TRX_NUMBER IS NULL THEN
3063 --Bug: 4916724 :call function to get unique trx numbe
3064 --l_taiv_rec.TRX_NUMBER := SUBSTR(TO_CHAR(l_taiv_rec.ID),-6);
3065 l_taiv_rec.TRX_NUMBER := get_unique_trx_number(l_taiv_rec.ID);
3066 END IF;
3067 -- for LE Uptake project 08-11-2006
3068 IF ( l_taiv_rec.legal_entity_id IS NULL OR (l_taiv_rec.legal_entity_id = Okl_Api.G_MISS_NUM))
3069 THEN
3070 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(l_taiv_rec.khr_id);
3071 IF l_legal_entity_id IS NULL THEN
3072 Okl_Api.set_message(p_app_name => g_app_name,
3073 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
3074 p_token1 => 'CONTRACT_ID',
3075 p_token1_value => l_taiv_rec.khr_id);
3076 RAISE OKL_API.G_EXCEPTION_ERROR;
3077 ELSE
3078 l_taiv_rec.legal_entity_id := l_legal_entity_id;
3079 END IF;
3080 END IF;
3081
3082 --gkhuntet start 02-Nov-07
3083 IF(l_taiv_rec.TRANSACTION_DATE IS NULL OR l_taiv_rec.TRANSACTION_DATE = Okl_Api.G_MISS_DATE)
3084 THEN
3085 l_taiv_rec.TRANSACTION_DATE := SYSDATE;
3086 END IF;
3087 --gkhuntet end 02-Nov-07
3088
3089 -- for LE Uptake project 08-11-2006
3090
3091 --- Setting item attributes
3092 l_return_status := Set_Attributes(
3093 l_taiv_rec, -- IN
3094 l_def_taiv_rec); -- OUT
3095 --- If any errors happen abort API
3096 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3097 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3098 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3099 RAISE Okl_Api.G_EXCEPTION_ERROR;
3100 END IF;
3101 l_def_taiv_rec := fill_who_columns(l_def_taiv_rec);
3102 --- Validate all non-missing attributes (Item Level Validation)
3103 l_return_status := Validate_Attributes(l_def_taiv_rec);
3104 --- If any errors happen abort API
3105 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3106 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3107 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3108 RAISE Okl_Api.G_EXCEPTION_ERROR;
3109 END IF;
3110 l_return_status := Validate_Record(l_def_taiv_rec);
3111 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3112 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3113 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3114 RAISE Okl_Api.G_EXCEPTION_ERROR;
3115 END IF;
3116 --------------------------------------
3117 -- Move VIEW record to "Child" records
3118 --------------------------------------
3119 migrate(l_def_taiv_rec, l_tai_rec);
3120 migrate(l_def_taiv_rec, l_okl_trx_ar_invoices_tl_rec);
3121 --------------------------------------------
3122 -- Call the INSERT_ROW for each child record
3123 --------------------------------------------
3124 insert_row(
3125 p_init_msg_list,
3126 x_return_status,
3127 x_msg_count,
3128 x_msg_data,
3129 l_tai_rec,
3130 lx_tai_rec
3131 );
3132 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3133 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3134 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3135 RAISE Okl_Api.G_EXCEPTION_ERROR;
3136 END IF;
3137 migrate(lx_tai_rec, l_def_taiv_rec);
3138 insert_row(
3139 p_init_msg_list,
3140 x_return_status,
3141 x_msg_count,
3142 x_msg_data,
3143 l_okl_trx_ar_invoices_tl_rec,
3144 lx_okl_trx_ar_invoices_tl_rec
3145 );
3146 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3147 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3148 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3149 RAISE Okl_Api.G_EXCEPTION_ERROR;
3150 END IF;
3151 migrate(lx_okl_trx_ar_invoices_tl_rec, l_def_taiv_rec);
3152 -- Set OUT values
3153 x_taiv_rec := l_def_taiv_rec;
3154 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
3155 EXCEPTION
3156 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3157 x_return_status := 'E';
3158 /*
3159 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3160 (
3161 l_api_name,
3162 G_PKG_NAME,
3163 'Okl_Api.G_RET_STS_ERROR',
3164 x_msg_count,
3165 x_msg_data,
3166 '_PVT'
3167 );
3168 */
3169 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3170 x_return_status := 'U';
3171 /*
3172 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3173 (
3174 l_api_name,
3175 G_PKG_NAME,
3176 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3177 x_msg_count,
3178 x_msg_data,
3179 '_PVT'
3180 );
3181 */
3182 WHEN OTHERS THEN
3183 x_return_status := 'U';
3184 /*
3185 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3186 (
3187 l_api_name,
3188 G_PKG_NAME,
3189 'OTHERS',
3190 x_msg_count,
3191 x_msg_data,
3192 '_PVT'
3193 );
3194 */
3195 END insert_row;
3196 ----------------------------------------
3197 -- PL/SQL TBL insert_row for:TAIV_TBL --
3198 ----------------------------------------
3199 PROCEDURE insert_row(
3200 p_api_version IN NUMBER,
3201 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3202 x_return_status OUT NOCOPY VARCHAR2,
3203 x_msg_count OUT NOCOPY NUMBER,
3204 x_msg_data OUT NOCOPY VARCHAR2,
3205 p_taiv_tbl IN taiv_tbl_type,
3206 x_taiv_tbl OUT NOCOPY taiv_tbl_type) IS
3207
3208 l_api_version CONSTANT NUMBER := 1;
3209 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
3210 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3211 -- Begin Post-Generation Change
3212 -- overall error status
3213 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3214 -- End Post-Generation Change
3215
3216 i NUMBER := 0;
3217 BEGIN
3218 Okl_Api.init_msg_list(p_init_msg_list);
3219 -- Make sure PL/SQL table has records in it before passing
3220 IF (p_taiv_tbl.COUNT > 0) THEN
3221 i := p_taiv_tbl.FIRST;
3222 LOOP
3223 insert_row (
3224 p_api_version => p_api_version,
3225 p_init_msg_list => Okl_Api.G_FALSE,
3226 x_return_status => x_return_status,
3227 x_msg_count => x_msg_count,
3228 x_msg_data => x_msg_data,
3229 p_taiv_rec => p_taiv_tbl(i),
3230 x_taiv_rec => x_taiv_tbl(i));
3231 -- Begin Post-Generation Change
3232 -- store the highest degree of error
3233 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3234 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3235 l_overall_status := x_return_status;
3236 END IF;
3237 END IF;
3238 -- End Post-Generation Change
3239 EXIT WHEN (i = p_taiv_tbl.LAST);
3240 i := p_taiv_tbl.NEXT(i);
3241 END LOOP;
3242 -- Begin Post-Generation Change
3243 -- return overall status
3244 x_return_status := l_overall_status;
3245 -- End Post-Generation Change
3246 END IF;
3247 EXCEPTION
3248 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3249 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3250 (
3251 l_api_name,
3252 G_PKG_NAME,
3253 'OKL_API.G_RET_STS_ERROR',
3254 x_msg_count,
3255 x_msg_data,
3256 '_PVT'
3257 );
3258 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3259 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3260 (
3261 l_api_name,
3262 G_PKG_NAME,
3263 'OKL_API.G_RET_STS_UNEXP_ERROR',
3264 x_msg_count,
3265 x_msg_data,
3266 '_PVT'
3267 );
3268 WHEN OTHERS THEN
3269 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3270 (
3271 l_api_name,
3272 G_PKG_NAME,
3273 'OTHERS',
3274 x_msg_count,
3275 x_msg_data,
3276 '_PVT'
3277 );
3278 END insert_row;
3279
3280 ---------------------------------------------------------------------------
3281 -- PROCEDURE lock_row
3282 ---------------------------------------------------------------------------
3283 ----------------------------------------
3284 -- lock_row for:OKL_TRX_AR_INVOICES_B --
3285 ----------------------------------------
3286 PROCEDURE lock_row(
3287 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3288 x_return_status OUT NOCOPY VARCHAR2,
3289 x_msg_count OUT NOCOPY NUMBER,
3290 x_msg_data OUT NOCOPY VARCHAR2,
3291 p_tai_rec IN tai_rec_type) IS
3292
3293 E_Resource_Busy EXCEPTION;
3294 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3295 CURSOR lock_csr (p_tai_rec IN tai_rec_type) IS
3296 SELECT OBJECT_VERSION_NUMBER
3297 FROM OKL_TRX_AR_INVOICES_B
3298 WHERE ID = p_tai_rec.id
3299 AND OBJECT_VERSION_NUMBER = p_tai_rec.object_version_number
3300 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3301
3302 CURSOR lchk_csr (p_tai_rec IN tai_rec_type) IS
3303 SELECT OBJECT_VERSION_NUMBER
3304 FROM OKL_TRX_AR_INVOICES_B
3305 WHERE ID = p_tai_rec.id;
3306 l_api_version CONSTANT NUMBER := 1;
3307 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3308 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3309 l_object_version_number OKL_TRX_AR_INVOICES_B.OBJECT_VERSION_NUMBER%TYPE;
3310 lc_object_version_number OKL_TRX_AR_INVOICES_B.OBJECT_VERSION_NUMBER%TYPE;
3311 l_row_notfound BOOLEAN := FALSE;
3312 lc_row_notfound BOOLEAN := FALSE;
3313 BEGIN
3314 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
3315 p_init_msg_list,
3316 '_PVT',
3317 x_return_status);
3318 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3319 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3320 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3321 RAISE Okl_Api.G_EXCEPTION_ERROR;
3322 END IF;
3323 BEGIN
3324 OPEN lock_csr(p_tai_rec);
3325 FETCH lock_csr INTO l_object_version_number;
3326 l_row_notfound := lock_csr%NOTFOUND;
3327 CLOSE lock_csr;
3328 EXCEPTION
3329 WHEN E_Resource_Busy THEN
3330 IF (lock_csr%ISOPEN) THEN
3331 CLOSE lock_csr;
3332 END IF;
3333 Okl_Api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3334 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
3335 END;
3336
3337 IF ( l_row_notfound ) THEN
3338 OPEN lchk_csr(p_tai_rec);
3339 FETCH lchk_csr INTO lc_object_version_number;
3340 lc_row_notfound := lchk_csr%NOTFOUND;
3341 CLOSE lchk_csr;
3342 END IF;
3343 IF (lc_row_notfound) THEN
3344 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3345 RAISE Okl_Api.G_EXCEPTION_ERROR;
3346 ELSIF lc_object_version_number > p_tai_rec.object_version_number THEN
3347 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3348 RAISE Okl_Api.G_EXCEPTION_ERROR;
3349 ELSIF lc_object_version_number <> p_tai_rec.object_version_number THEN
3350 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3351 RAISE Okl_Api.G_EXCEPTION_ERROR;
3352 ELSIF lc_object_version_number = -1 THEN
3353 Okl_Api.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3354 RAISE Okl_Api.G_EXCEPTION_ERROR;
3355 END IF;
3356 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
3357 EXCEPTION
3358 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3359 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3360 (
3361 l_api_name,
3362 G_PKG_NAME,
3363 'OKL_API.G_RET_STS_ERROR',
3364 x_msg_count,
3365 x_msg_data,
3366 '_PVT'
3367 );
3368 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3369 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3370 (
3371 l_api_name,
3372 G_PKG_NAME,
3373 'OKL_API.G_RET_STS_UNEXP_ERROR',
3374 x_msg_count,
3375 x_msg_data,
3376 '_PVT'
3377 );
3378 WHEN OTHERS THEN
3379 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3380 (
3381 l_api_name,
3382 G_PKG_NAME,
3383 'OTHERS',
3384 x_msg_count,
3385 x_msg_data,
3386 '_PVT'
3387 );
3388 END lock_row;
3389 -----------------------------------------
3390 -- lock_row for:OKL_TRX_AR_INVOICES_TL --
3391 -----------------------------------------
3392 PROCEDURE lock_row(
3393 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3394 x_return_status OUT NOCOPY VARCHAR2,
3395 x_msg_count OUT NOCOPY NUMBER,
3396 x_msg_data OUT NOCOPY VARCHAR2,
3397 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType) IS
3398
3399 E_Resource_Busy EXCEPTION;
3400 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3401 CURSOR lock_csr (p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType) IS
3402 SELECT *
3403 FROM OKL_TRX_AR_INVOICES_TL
3404 WHERE ID = p_okl_trx_ar_invoices_tl_rec.id
3405 FOR UPDATE NOWAIT;
3406
3407 l_api_version CONSTANT NUMBER := 1;
3408 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3409 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3410 l_lock_var lock_csr%ROWTYPE;
3411 l_row_notfound BOOLEAN := FALSE;
3412 lc_row_notfound BOOLEAN := FALSE;
3413 BEGIN
3414 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
3415 p_init_msg_list,
3416 '_PVT',
3417 x_return_status);
3418 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3419 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3420 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3421 RAISE Okl_Api.G_EXCEPTION_ERROR;
3422 END IF;
3423 BEGIN
3424 OPEN lock_csr(p_okl_trx_ar_invoices_tl_rec);
3425 FETCH lock_csr INTO l_lock_var;
3426 l_row_notfound := lock_csr%NOTFOUND;
3427 CLOSE lock_csr;
3428 EXCEPTION
3429 WHEN E_Resource_Busy THEN
3430 IF (lock_csr%ISOPEN) THEN
3431 CLOSE lock_csr;
3432 END IF;
3433 Okl_Api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3434 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
3435 END;
3436
3437 IF ( l_row_notfound ) THEN
3438 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3439 RAISE Okl_Api.G_EXCEPTION_ERROR;
3440 END IF;
3441 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
3442 EXCEPTION
3443 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3444 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3445 (
3446 l_api_name,
3447 G_PKG_NAME,
3448 'OKL_API.G_RET_STS_ERROR',
3449 x_msg_count,
3450 x_msg_data,
3451 '_PVT'
3452 );
3453 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3454 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3455 (
3456 l_api_name,
3457 G_PKG_NAME,
3458 'OKL_API.G_RET_STS_UNEXP_ERROR',
3459 x_msg_count,
3460 x_msg_data,
3461 '_PVT'
3462 );
3463 WHEN OTHERS THEN
3464 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3465 (
3466 l_api_name,
3467 G_PKG_NAME,
3468 'OTHERS',
3469 x_msg_count,
3470 x_msg_data,
3471 '_PVT'
3472 );
3473 END lock_row;
3474 ----------------------------------------
3475 -- lock_row for:OKL_TRX_AR_INVOICES_V --
3476 ----------------------------------------
3477 PROCEDURE lock_row(
3478 p_api_version IN NUMBER,
3479 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3480 x_return_status OUT NOCOPY VARCHAR2,
3481 x_msg_count OUT NOCOPY NUMBER,
3482 x_msg_data OUT NOCOPY VARCHAR2,
3483 p_taiv_rec IN taiv_rec_type) IS
3484
3485 l_api_version CONSTANT NUMBER := 1;
3486 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3487 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3488 l_tai_rec tai_rec_type;
3489 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
3490 BEGIN
3491 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
3492 G_PKG_NAME,
3493 p_init_msg_list,
3494 l_api_version,
3495 p_api_version,
3496 '_PVT',
3497 x_return_status);
3498 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3499 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3500 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3501 RAISE Okl_Api.G_EXCEPTION_ERROR;
3502 END IF;
3503 --------------------------------------
3504 -- Move VIEW record to "Child" records
3505 --------------------------------------
3506 migrate(p_taiv_rec, l_tai_rec);
3507 migrate(p_taiv_rec, l_okl_trx_ar_invoices_tl_rec);
3508 --------------------------------------------
3509 -- Call the LOCK_ROW for each child record
3510 --------------------------------------------
3511 lock_row(
3512 p_init_msg_list,
3513 x_return_status,
3514 x_msg_count,
3515 x_msg_data,
3516 l_tai_rec
3517 );
3518 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3519 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3520 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3521 RAISE Okl_Api.G_EXCEPTION_ERROR;
3522 END IF;
3523 lock_row(
3524 p_init_msg_list,
3525 x_return_status,
3526 x_msg_count,
3527 x_msg_data,
3528 l_okl_trx_ar_invoices_tl_rec
3529 );
3530 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3531 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3532 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3533 RAISE Okl_Api.G_EXCEPTION_ERROR;
3534 END IF;
3535 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
3536 EXCEPTION
3537 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3538 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3539 (
3540 l_api_name,
3541 G_PKG_NAME,
3542 'OKL_API.G_RET_STS_ERROR',
3543 x_msg_count,
3544 x_msg_data,
3545 '_PVT'
3546 );
3547 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3548 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3549 (
3550 l_api_name,
3551 G_PKG_NAME,
3552 'OKL_API.G_RET_STS_UNEXP_ERROR',
3553 x_msg_count,
3554 x_msg_data,
3555 '_PVT'
3556 );
3557 WHEN OTHERS THEN
3558 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3559 (
3560 l_api_name,
3561 G_PKG_NAME,
3562 'OTHERS',
3563 x_msg_count,
3564 x_msg_data,
3565 '_PVT'
3566 );
3567 END lock_row;
3568 --------------------------------------
3569 -- PL/SQL TBL lock_row for:TAIV_TBL --
3570 --------------------------------------
3571 PROCEDURE lock_row(
3572 p_api_version IN NUMBER,
3573 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3574 x_return_status OUT NOCOPY VARCHAR2,
3575 x_msg_count OUT NOCOPY NUMBER,
3576 x_msg_data OUT NOCOPY VARCHAR2,
3577 p_taiv_tbl IN taiv_tbl_type) IS
3578
3579 l_api_version CONSTANT NUMBER := 1;
3580 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
3581 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3582 -- Begin Post-Generation Change
3583 -- overall error status
3584 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3585 -- End Post-Generation Change
3586
3587 i NUMBER := 0;
3588 BEGIN
3589 Okl_Api.init_msg_list(p_init_msg_list);
3590 -- Make sure PL/SQL table has records in it before passing
3591 IF (p_taiv_tbl.COUNT > 0) THEN
3592 i := p_taiv_tbl.FIRST;
3593 LOOP
3594 lock_row (
3595 p_api_version => p_api_version,
3596 p_init_msg_list => Okl_Api.G_FALSE,
3597 x_return_status => x_return_status,
3598 x_msg_count => x_msg_count,
3599 x_msg_data => x_msg_data,
3600 p_taiv_rec => p_taiv_tbl(i));
3601
3602 -- Begin Post-Generation Change
3603 -- store the highest degree of error
3604 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3605 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3606 l_overall_status := x_return_status;
3607 END IF;
3608 END IF;
3609 -- End Post-Generation Change
3610 EXIT WHEN (i = p_taiv_tbl.LAST);
3611 i := p_taiv_tbl.NEXT(i);
3612 END LOOP;
3613 -- Begin Post-Generation Change
3614 -- return overall status
3615 x_return_status := l_overall_status;
3616 -- End Post-Generation Change
3617 END IF;
3618 EXCEPTION
3619 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3620 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3621 (
3622 l_api_name,
3623 G_PKG_NAME,
3624 'OKL_API.G_RET_STS_ERROR',
3625 x_msg_count,
3626 x_msg_data,
3627 '_PVT'
3628 );
3629 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3630 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3631 (
3632 l_api_name,
3633 G_PKG_NAME,
3634 'OKL_API.G_RET_STS_UNEXP_ERROR',
3635 x_msg_count,
3636 x_msg_data,
3637 '_PVT'
3638 );
3639 WHEN OTHERS THEN
3640 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3641 (
3642 l_api_name,
3643 G_PKG_NAME,
3644 'OTHERS',
3645 x_msg_count,
3646 x_msg_data,
3647 '_PVT'
3648 );
3649 END lock_row;
3650
3651 ---------------------------------------------------------------------------
3652 -- PROCEDURE update_row
3653 ---------------------------------------------------------------------------
3654 ------------------------------------------
3655 -- update_row for:OKL_TRX_AR_INVOICES_B --
3656 ------------------------------------------
3657 PROCEDURE update_row(
3658 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3659 x_return_status OUT NOCOPY VARCHAR2,
3660 x_msg_count OUT NOCOPY NUMBER,
3661 x_msg_data OUT NOCOPY VARCHAR2,
3662 p_tai_rec IN tai_rec_type,
3663 x_tai_rec OUT NOCOPY tai_rec_type) IS
3664
3665 l_api_version CONSTANT NUMBER := 1;
3666 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
3667 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3668 l_tai_rec tai_rec_type := p_tai_rec;
3669 l_def_tai_rec tai_rec_type;
3670 l_row_notfound BOOLEAN := TRUE;
3671 l_legal_entity_id OKL_TRX_AR_INVOICES_B.LEGAL_ENTITY_ID%TYPE; -- for LE Uptake project 08-11-2006
3672 ----------------------------------
3673 -- FUNCTION populate_new_record --
3674 ----------------------------------
3675 FUNCTION populate_new_record (
3676 p_tai_rec IN tai_rec_type,
3677 x_tai_rec OUT NOCOPY tai_rec_type
3678 ) RETURN VARCHAR2 IS
3679 l_tai_rec tai_rec_type;
3680 l_row_notfound BOOLEAN := TRUE;
3681 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3682 BEGIN
3683 x_tai_rec := p_tai_rec;
3684 -- Get current database values
3685 l_tai_rec := get_rec(p_tai_rec, l_row_notfound);
3686 IF (l_row_notfound) THEN
3687 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3688 END IF;
3689 IF (x_tai_rec.id = Okl_Api.G_MISS_NUM)
3690 THEN
3691 x_tai_rec.id := l_tai_rec.id;
3692 END IF;
3693
3694 IF (x_tai_rec.currency_code = Okl_Api.G_MISS_CHAR)
3695 THEN
3696 x_tai_rec.currency_code := l_tai_rec.currency_code;
3697 END IF;
3698
3699 --Start change by pgomes on 15-NOV-2002
3700 IF (x_tai_rec.currency_conversion_type = Okl_Api.G_MISS_CHAR) THEN
3701 x_tai_rec.currency_conversion_type := l_tai_rec.currency_conversion_type;
3702 END IF;
3703
3704 IF (x_tai_rec.currency_conversion_rate = Okl_Api.G_MISS_NUM) THEN
3705 x_tai_rec.currency_conversion_rate := l_tai_rec.currency_conversion_rate;
3706 END IF;
3707
3708 IF (x_tai_rec.currency_conversion_date = Okl_Api.G_MISS_DATE) THEN
3709 x_tai_rec.currency_conversion_date := l_tai_rec.currency_conversion_date;
3710 END IF;
3711 --End change by pgomes on 15-NOV-2002
3712
3713 IF (x_tai_rec.ibt_id = Okl_Api.G_MISS_NUM)
3714 THEN
3715 x_tai_rec.ibt_id := l_tai_rec.ibt_id;
3716 END IF;
3717 IF (x_tai_rec.ixx_id = Okl_Api.G_MISS_NUM)
3718 THEN
3719 x_tai_rec.ixx_id := l_tai_rec.ixx_id;
3720 END IF;
3721 IF (x_tai_rec.khr_id = Okl_Api.G_MISS_NUM)
3722 THEN
3723 x_tai_rec.khr_id := l_tai_rec.khr_id;
3724 END IF;
3725 IF (x_tai_rec.irm_id = Okl_Api.G_MISS_NUM)
3726 THEN
3727 x_tai_rec.irm_id := l_tai_rec.irm_id;
3728 END IF;
3729 IF (x_tai_rec.irt_id = Okl_Api.G_MISS_NUM)
3730 THEN
3731 x_tai_rec.irt_id := l_tai_rec.irt_id;
3732 END IF;
3733 IF (x_tai_rec.cra_id = Okl_Api.G_MISS_NUM)
3734 THEN
3735 x_tai_rec.cra_id := l_tai_rec.cra_id;
3736 END IF;
3737 IF (x_tai_rec.svf_id = Okl_Api.G_MISS_NUM)
3738 THEN
3739 x_tai_rec.svf_id := l_tai_rec.svf_id;
3740 END IF;
3741 IF (x_tai_rec.tap_id = Okl_Api.G_MISS_NUM)
3742 THEN
3743 x_tai_rec.tap_id := l_tai_rec.tap_id;
3744 END IF;
3745 IF (x_tai_rec.qte_id = Okl_Api.G_MISS_NUM)
3746 THEN
3747 x_tai_rec.qte_id := l_tai_rec.qte_id;
3748 END IF;
3749 IF (x_tai_rec.tcn_id = Okl_Api.G_MISS_NUM)
3750 THEN
3751 x_tai_rec.tcn_id := l_tai_rec.tcn_id;
3752 END IF;
3753 IF (x_tai_rec.tai_id_reverses = Okl_Api.G_MISS_NUM)
3754 THEN
3755 x_tai_rec.tai_id_reverses := l_tai_rec.tai_id_reverses;
3756 END IF;
3757 -- Added after postgen changes
3758 IF (x_tai_rec.ipy_id = Okl_Api.G_MISS_NUM)
3759 THEN
3760 x_tai_rec.ipy_id := l_tai_rec.ipy_id;
3761 END IF;
3762 IF (x_tai_rec.trx_status_code = Okl_Api.G_MISS_CHAR)
3763 THEN
3764 x_tai_rec.trx_status_code := l_tai_rec.trx_status_code;
3765 END IF;
3766 IF (x_tai_rec.set_of_books_id = Okl_Api.G_MISS_NUM)
3767 THEN
3768 x_tai_rec.set_of_books_id := l_tai_rec.set_of_books_id;
3769 END IF;
3770 IF (x_tai_rec.try_id = Okl_Api.G_MISS_NUM)
3771 THEN
3772 x_tai_rec.try_id := l_tai_rec.try_id;
3773 END IF;
3774 -- End Addition after postgen changes
3775
3776 IF (x_tai_rec.date_entered = Okl_Api.G_MISS_DATE)
3777 THEN
3778 x_tai_rec.date_entered := l_tai_rec.date_entered;
3779 END IF;
3780 IF (x_tai_rec.date_invoiced = Okl_Api.G_MISS_DATE)
3781 THEN
3782 x_tai_rec.date_invoiced := l_tai_rec.date_invoiced;
3783 END IF;
3784 IF (x_tai_rec.amount = Okl_Api.G_MISS_NUM)
3785 THEN
3786 x_tai_rec.amount := l_tai_rec.amount;
3787 END IF;
3788 IF (x_tai_rec.object_version_number = Okl_Api.G_MISS_NUM)
3789 THEN
3790 x_tai_rec.object_version_number := l_tai_rec.object_version_number;
3791 END IF;
3792 IF (x_tai_rec.amount_applied = Okl_Api.G_MISS_NUM)
3793 THEN
3794 x_tai_rec.amount_applied := l_tai_rec.amount_applied;
3795 END IF;
3796 IF (x_tai_rec.request_id = Okl_Api.G_MISS_NUM)
3797 THEN
3798 x_tai_rec.request_id := l_tai_rec.request_id;
3799 END IF;
3800 IF (x_tai_rec.program_application_id = Okl_Api.G_MISS_NUM)
3801 THEN
3802 x_tai_rec.program_application_id := l_tai_rec.program_application_id;
3803 END IF;
3804 IF (x_tai_rec.program_id = Okl_Api.G_MISS_NUM)
3805 THEN
3806 x_tai_rec.program_id := l_tai_rec.program_id;
3807 END IF;
3808 IF (x_tai_rec.program_update_date = Okl_Api.G_MISS_DATE)
3809 THEN
3810 x_tai_rec.program_update_date := l_tai_rec.program_update_date;
3811 END IF;
3812 IF (x_tai_rec.org_id = Okl_Api.G_MISS_NUM)
3813 THEN
3814 x_tai_rec.org_id := l_tai_rec.org_id;
3815 END IF;
3816 IF (x_tai_rec.TRX_NUMBER = Okl_Api.G_MISS_CHAR)
3817 THEN
3818 x_tai_rec.TRX_NUMBER := l_tai_rec.TRX_NUMBER;
3819 END IF;
3820 IF (x_tai_rec.clg_id = Okl_Api.G_MISS_NUM)
3821 THEN
3822 x_tai_rec.clg_id := l_tai_rec.clg_id;
3823 END IF;
3824
3825 --Start code added by pgomes on 19-NOV-2002
3826 IF (x_tai_rec.pox_id = Okl_Api.G_MISS_NUM)
3827 THEN
3828 x_tai_rec.pox_id := l_tai_rec.pox_id;
3829 END IF;
3830 --End code added by pgomes on 19-NOV-2002
3831
3832 IF (x_tai_rec.cpy_id = Okl_Api.G_MISS_NUM)
3833 THEN
3834 x_tai_rec.cpy_id := l_tai_rec.cpy_id;
3835 END IF;
3836 IF (x_tai_rec.attribute_category = Okl_Api.G_MISS_CHAR)
3837 THEN
3838 x_tai_rec.attribute_category := l_tai_rec.attribute_category;
3839 END IF;
3840 IF (x_tai_rec.attribute1 = Okl_Api.G_MISS_CHAR)
3841 THEN
3842 x_tai_rec.attribute1 := l_tai_rec.attribute1;
3843 END IF;
3844 IF (x_tai_rec.attribute2 = Okl_Api.G_MISS_CHAR)
3845 THEN
3846 x_tai_rec.attribute2 := l_tai_rec.attribute2;
3847 END IF;
3848 IF (x_tai_rec.attribute3 = Okl_Api.G_MISS_CHAR)
3849 THEN
3850 x_tai_rec.attribute3 := l_tai_rec.attribute3;
3851 END IF;
3852 IF (x_tai_rec.attribute4 = Okl_Api.G_MISS_CHAR)
3853 THEN
3854 x_tai_rec.attribute4 := l_tai_rec.attribute4;
3855 END IF;
3856 IF (x_tai_rec.attribute5 = Okl_Api.G_MISS_CHAR)
3857 THEN
3858 x_tai_rec.attribute5 := l_tai_rec.attribute5;
3859 END IF;
3860 IF (x_tai_rec.attribute6 = Okl_Api.G_MISS_CHAR)
3861 THEN
3862 x_tai_rec.attribute6 := l_tai_rec.attribute6;
3863 END IF;
3864 IF (x_tai_rec.attribute7 = Okl_Api.G_MISS_CHAR)
3865 THEN
3866 x_tai_rec.attribute7 := l_tai_rec.attribute7;
3867 END IF;
3868 IF (x_tai_rec.attribute8 = Okl_Api.G_MISS_CHAR)
3869 THEN
3870 x_tai_rec.attribute8 := l_tai_rec.attribute8;
3871 END IF;
3872 IF (x_tai_rec.attribute9 = Okl_Api.G_MISS_CHAR)
3873 THEN
3874 x_tai_rec.attribute9 := l_tai_rec.attribute9;
3875 END IF;
3876 IF (x_tai_rec.attribute10 = Okl_Api.G_MISS_CHAR)
3877 THEN
3878 x_tai_rec.attribute10 := l_tai_rec.attribute10;
3879 END IF;
3880 IF (x_tai_rec.attribute11 = Okl_Api.G_MISS_CHAR)
3881 THEN
3882 x_tai_rec.attribute11 := l_tai_rec.attribute11;
3883 END IF;
3884 IF (x_tai_rec.attribute12 = Okl_Api.G_MISS_CHAR)
3885 THEN
3886 x_tai_rec.attribute12 := l_tai_rec.attribute12;
3887 END IF;
3888 IF (x_tai_rec.attribute13 = Okl_Api.G_MISS_CHAR)
3889 THEN
3890 x_tai_rec.attribute13 := l_tai_rec.attribute13;
3891 END IF;
3892 IF (x_tai_rec.attribute14 = Okl_Api.G_MISS_CHAR)
3893 THEN
3894 x_tai_rec.attribute14 := l_tai_rec.attribute14;
3895 END IF;
3896 IF (x_tai_rec.attribute15 = Okl_Api.G_MISS_CHAR)
3897 THEN
3898 x_tai_rec.attribute15 := l_tai_rec.attribute15;
3899 END IF;
3900 IF (x_tai_rec.created_by = Okl_Api.G_MISS_NUM)
3901 THEN
3902 x_tai_rec.created_by := l_tai_rec.created_by;
3903 END IF;
3904 IF (x_tai_rec.creation_date = Okl_Api.G_MISS_DATE)
3905 THEN
3906 x_tai_rec.creation_date := l_tai_rec.creation_date;
3907 END IF;
3908 IF (x_tai_rec.last_updated_by = Okl_Api.G_MISS_NUM)
3909 THEN
3910 x_tai_rec.last_updated_by := l_tai_rec.last_updated_by;
3911 END IF;
3912 IF (x_tai_rec.last_update_date = Okl_Api.G_MISS_DATE)
3913 THEN
3914 x_tai_rec.last_update_date := l_tai_rec.last_update_date;
3915 END IF;
3916 IF (x_tai_rec.last_update_login = Okl_Api.G_MISS_NUM)
3917 THEN
3918 x_tai_rec.last_update_login := l_tai_rec.last_update_login;
3919 END IF;
3920 -- for LE Uptake project 08-11-2006
3921 IF (x_tai_rec.legal_entity_id = Okl_Api.G_MISS_NUM)
3922 THEN
3923 x_tai_rec.legal_entity_id := l_tai_rec.legal_entity_id;
3924 END IF;
3925 -- for LE Uptake project 08-11-2006
3926
3927 -- start:30-Jan-07 cklee Billing R12 project
3928 --gkhuntet 10-JUL-07 Start
3929 -- Investor_Agreement_Number,
3930 IF (x_tai_rec.Investor_Agreement_Number IS NULL OR x_tai_rec.Investor_Agreement_Number = Okl_Api.G_MISS_CHAR) THEN
3931 x_tai_rec.Investor_Agreement_Number := l_tai_rec.Investor_Agreement_Number;
3932 END IF;
3933
3934 -- Investor_Name,
3935 IF (x_tai_rec.Investor_Name IS NULL OR x_tai_rec.Investor_Name = Okl_Api.G_MISS_CHAR) THEN
3936 x_tai_rec.Investor_Name := l_tai_rec.Investor_Name;
3937 END IF;
3938
3939 -- OKL_SOURCE_BILLING_TRX,
3940 IF (x_tai_rec.OKL_SOURCE_BILLING_TRX IS NULL OR x_tai_rec.OKL_SOURCE_BILLING_TRX = Okl_Api.G_MISS_CHAR) THEN
3941 x_tai_rec.OKL_SOURCE_BILLING_TRX := l_tai_rec.OKL_SOURCE_BILLING_TRX;
3942 END IF;
3943
3944 -- INF_ID,
3945 IF (x_tai_rec.INF_ID IS NULL OR x_tai_rec.INF_ID = Okl_Api.G_MISS_NUM) THEN
3946 x_tai_rec.INF_ID := l_tai_rec.INF_ID;
3947 END IF;
3948
3949 -- INVOICE_PULL_YN,
3950 IF (x_tai_rec.INVOICE_PULL_YN IS NULL OR
3951 x_tai_rec.INVOICE_PULL_YN = Okl_Api.G_MISS_CHAR) THEN
3952 x_tai_rec.INVOICE_PULL_YN := l_tai_rec.INVOICE_PULL_YN;
3953 END IF;
3954
3955 -- CONSOLIDATED_INVOICE_NUMBER,
3956 IF (x_tai_rec.CONSOLIDATED_INVOICE_NUMBER IS NULL OR x_tai_rec.CONSOLIDATED_INVOICE_NUMBER = Okl_Api.G_MISS_CHAR) THEN
3957 x_tai_rec.CONSOLIDATED_INVOICE_NUMBER := l_tai_rec.CONSOLIDATED_INVOICE_NUMBER;
3958 END IF;
3959
3960 -- DUE_DATE,
3961 IF (x_tai_rec.DUE_DATE IS NULL OR x_tai_rec.DUE_DATE = Okl_Api.G_MISS_DATE) THEN
3962 x_tai_rec.DUE_DATE := l_tai_rec.DUE_DATE;
3963 END IF;
3964
3965 -- ISI_ID,
3966 IF (x_tai_rec.ISI_ID IS NULL OR x_tai_rec.ISI_ID = Okl_Api.G_MISS_NUM) THEN
3967 x_tai_rec.ISI_ID := l_tai_rec.ISI_ID;
3968 END IF;
3969
3970 -- RECEIVABLES_INVOICE_ID,
3971 IF (x_tai_rec.RECEIVABLES_INVOICE_ID IS NULL OR x_tai_rec.RECEIVABLES_INVOICE_ID = Okl_Api.G_MISS_NUM) THEN
3972 x_tai_rec.RECEIVABLES_INVOICE_ID := l_tai_rec.RECEIVABLES_INVOICE_ID;
3973 END IF;
3974
3975 -- CUST_TRX_TYPE_ID,
3976 IF (x_tai_rec.CUST_TRX_TYPE_ID IS NULL OR
3977 x_tai_rec.CUST_TRX_TYPE_ID = Okl_Api.G_MISS_NUM) THEN
3978 x_tai_rec.CUST_TRX_TYPE_ID := l_tai_rec.CUST_TRX_TYPE_ID;
3979 END IF;
3980
3981 -- CUSTOMER_BANK_ACCOUNT_ID,
3982 IF (x_tai_rec.CUSTOMER_BANK_ACCOUNT_ID IS NULL OR x_tai_rec.CUSTOMER_BANK_ACCOUNT_ID = Okl_Api.G_MISS_NUM) THEN
3983 x_tai_rec.CUSTOMER_BANK_ACCOUNT_ID := l_tai_rec.CUSTOMER_BANK_ACCOUNT_ID;
3984 END IF;
3985
3986 -- TAX_EXEMPT_FLAG,
3987 IF (x_tai_rec.TAX_EXEMPT_FLAG IS NULL OR
3988 x_tai_rec.TAX_EXEMPT_FLAG = Okl_Api.G_MISS_CHAR) THEN
3989 x_tai_rec.TAX_EXEMPT_FLAG := l_tai_rec.TAX_EXEMPT_FLAG;
3990 END IF;
3991
3992 -- TAX_EXEMPT_REASON_CODE,
3993 IF (x_tai_rec.TAX_EXEMPT_REASON_CODE IS NULL OR x_tai_rec.TAX_EXEMPT_REASON_CODE = Okl_Api.G_MISS_CHAR) THEN
3994 x_tai_rec.TAX_EXEMPT_REASON_CODE := l_tai_rec.TAX_EXEMPT_REASON_CODE;
3995 END IF;
3996
3997 -- REFERENCE_LINE_ID
3998 IF (x_tai_rec.REFERENCE_LINE_ID IS NULL OR x_tai_rec.REFERENCE_LINE_ID = Okl_Api.G_MISS_NUM) THEN
3999 x_tai_rec.REFERENCE_LINE_ID := l_tai_rec.REFERENCE_LINE_ID;
4000 END IF;
4001
4002 -- PRIVATE_LABEL
4003 IF (x_tai_rec.PRIVATE_LABEL IS NULL OR x_tai_rec.PRIVATE_LABEL = Okl_Api.G_MISS_CHAR) THEN
4004 x_tai_rec.PRIVATE_LABEL := l_tai_rec.PRIVATE_LABEL;
4005 END IF;
4006 --gkhuntet 10-JUL-07 End
4007 -- end:30-Jan-07 cklee Billing R12 project
4008 --gkhuntet start 02-Nov-07
4009 IF (x_tai_rec.TRANSACTION_DATE = Okl_Api.G_MISS_DATE) THEN
4010 x_tai_rec.TRANSACTION_DATE := SYSDATE ;
4011 END IF;
4012 --gkhuntet end 02-Nov-07
4013
4014
4015
4016 RETURN(l_return_status);
4017 END populate_new_record;
4018 ----------------------------------------------
4019 -- Set_Attributes for:OKL_TRX_AR_INVOICES_B --
4020 ----------------------------------------------
4021 FUNCTION Set_Attributes (
4022 p_tai_rec IN tai_rec_type,
4023 x_tai_rec OUT NOCOPY tai_rec_type
4024 ) RETURN VARCHAR2 IS
4025 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4026 BEGIN
4027 x_tai_rec := p_tai_rec;
4028 RETURN(l_return_status);
4029 END Set_Attributes;
4030 BEGIN
4031 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
4032 p_init_msg_list,
4033 '_PVT',
4034 x_return_status);
4035 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4036 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4037 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4038 RAISE Okl_Api.G_EXCEPTION_ERROR;
4039 END IF;
4040 --- Setting item attributes
4041 l_return_status := Set_Attributes(
4042 p_tai_rec, -- IN
4043 l_tai_rec); -- OUT
4044 --- If any errors happen abort API
4045 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4046 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4047 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4048 RAISE Okl_Api.G_EXCEPTION_ERROR;
4049 END IF;
4050 l_return_status := populate_new_record(l_tai_rec, l_def_tai_rec);
4051 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4052 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4053 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4054 RAISE Okl_Api.G_EXCEPTION_ERROR;
4055 END IF;
4056
4057 UPDATE OKL_TRX_AR_INVOICES_B
4058 SET CURRENCY_CODE = l_def_tai_rec.currency_code,
4059 --Start change by pgomes on 15-NOV-2002
4060 currency_conversion_type = l_def_tai_rec.currency_conversion_type,
4061 currency_conversion_rate = l_def_tai_rec.currency_conversion_rate,
4062 currency_conversion_date = l_def_tai_rec.currency_conversion_date,
4063 --End change by pgomes on 15-NOV-2002
4064 IBT_ID = l_def_tai_rec.ibt_id,
4065 IXX_ID = l_def_tai_rec.ixx_id,
4066 KHR_ID = l_def_tai_rec.khr_id,
4067 IRM_ID = l_def_tai_rec.irm_id,
4068 IRT_ID = l_def_tai_rec.irt_id,
4069 CRA_ID = l_def_tai_rec.cra_id,
4070 SVF_ID = l_def_tai_rec.svf_id,
4071 TAP_ID = l_def_tai_rec.tap_id,
4072 QTE_ID = l_def_tai_rec.qte_id,
4073 TCN_ID = l_def_tai_rec.tcn_id,
4074 TAI_ID_REVERSES = l_def_tai_rec.tai_id_reverses,
4075 IPY_ID = l_def_tai_rec.ipy_id, -- Added after postgen changes
4076 TRX_STATUS_CODE = l_def_tai_rec.trx_status_code, -- Added after postgen changes
4077 SET_OF_BOOKS_ID = l_def_tai_rec.set_of_books_id, -- Added after postgen changes
4078 TRY_ID = l_def_tai_rec.try_id, -- Added after postgen changes
4079 DATE_ENTERED = l_def_tai_rec.date_entered,
4080 DATE_INVOICED = l_def_tai_rec.date_invoiced,
4081 AMOUNT = l_def_tai_rec.amount,
4082 OBJECT_VERSION_NUMBER = l_def_tai_rec.object_version_number,
4083 AMOUNT_APPLIED = l_def_tai_rec.amount_applied,
4084 REQUEST_ID = l_def_tai_rec.request_id,
4085 PROGRAM_APPLICATION_ID = l_def_tai_rec.program_application_id,
4086 PROGRAM_ID = l_def_tai_rec.program_id,
4087 PROGRAM_UPDATE_DATE = l_def_tai_rec.program_update_date,
4088 ORG_ID = l_def_tai_rec.org_id,
4089 TRX_NUMBER = l_def_tai_rec.trx_number,
4090 CLG_ID = l_def_tai_rec.clg_id,
4091 POX_ID = l_def_tai_rec.pox_id,
4092 CPY_ID = l_def_tai_rec.cpy_id,
4093 ATTRIBUTE_CATEGORY = l_def_tai_rec.attribute_category,
4094 ATTRIBUTE1 = l_def_tai_rec.attribute1,
4095 ATTRIBUTE2 = l_def_tai_rec.attribute2,
4096 ATTRIBUTE3 = l_def_tai_rec.attribute3,
4097 ATTRIBUTE4 = l_def_tai_rec.attribute4,
4098 ATTRIBUTE5 = l_def_tai_rec.attribute5,
4099 ATTRIBUTE6 = l_def_tai_rec.attribute6,
4100 ATTRIBUTE7 = l_def_tai_rec.attribute7,
4101 ATTRIBUTE8 = l_def_tai_rec.attribute8,
4102 ATTRIBUTE9 = l_def_tai_rec.attribute9,
4103 ATTRIBUTE10 = l_def_tai_rec.attribute10,
4104 ATTRIBUTE11 = l_def_tai_rec.attribute11,
4105 ATTRIBUTE12 = l_def_tai_rec.attribute12,
4106 ATTRIBUTE13 = l_def_tai_rec.attribute13,
4107 ATTRIBUTE14 = l_def_tai_rec.attribute14,
4108 ATTRIBUTE15 = l_def_tai_rec.attribute15,
4109 CREATED_BY = l_def_tai_rec.created_by,
4110 CREATION_DATE = l_def_tai_rec.creation_date,
4111 LAST_UPDATED_BY = l_def_tai_rec.last_updated_by,
4112 LAST_UPDATE_DATE = l_def_tai_rec.last_update_date,
4113 LAST_UPDATE_LOGIN = l_def_tai_rec.last_update_login,
4114 LEGAL_ENTITY_ID = l_def_tai_rec.legal_entity_id, -- for LE Uptake project 08-11-2006
4115
4116 -- start:30-Jan-07 cklee Billing R12 project
4117 Investor_Agreement_Number = l_def_tai_rec.Investor_Agreement_Number,
4118 Investor_Name = l_def_tai_rec.Investor_Name,
4119 OKL_SOURCE_BILLING_TRX = l_def_tai_rec.OKL_SOURCE_BILLING_TRX,
4120 INF_ID = l_def_tai_rec.INF_ID,
4121 INVOICE_PULL_YN = l_def_tai_rec.INVOICE_PULL_YN,
4122 CONSOLIDATED_INVOICE_NUMBER = l_def_tai_rec.CONSOLIDATED_INVOICE_NUMBER,
4123 DUE_DATE = l_def_tai_rec.DUE_DATE,
4124 ISI_ID = l_def_tai_rec.ISI_ID,
4125 RECEIVABLES_INVOICE_ID = l_def_tai_rec.RECEIVABLES_INVOICE_ID,
4126 CUST_TRX_TYPE_ID = l_def_tai_rec.CUST_TRX_TYPE_ID,
4127 CUSTOMER_BANK_ACCOUNT_ID = l_def_tai_rec.CUSTOMER_BANK_ACCOUNT_ID,
4128 TAX_EXEMPT_FLAG = l_def_tai_rec.TAX_EXEMPT_FLAG,
4129 TAX_EXEMPT_REASON_CODE = l_def_tai_rec.TAX_EXEMPT_REASON_CODE,
4130 REFERENCE_LINE_ID = l_def_tai_rec.REFERENCE_LINE_ID,
4131 PRIVATE_LABEL = l_def_tai_rec.PRIVATE_LABEL,
4132 -- end:30-Jan-07 cklee Billing R12 project
4133 --gkhuntet start 02-Nov-07
4134 TRANSACTION_DATE = l_def_tai_rec.transaction_date
4135 --gkhuntet end 02-Nov-07
4136
4137
4138
4139 WHERE ID = l_def_tai_rec.id;
4140
4141 x_tai_rec := l_def_tai_rec;
4142 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
4143 EXCEPTION
4144 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
4145 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
4146 (
4147 l_api_name,
4148 G_PKG_NAME,
4149 'OKL_API.G_RET_STS_ERROR',
4150 x_msg_count,
4151 x_msg_data,
4152 '_PVT'
4153 );
4154 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
4155 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
4156 (
4157 l_api_name,
4158 G_PKG_NAME,
4159 'OKL_API.G_RET_STS_UNEXP_ERROR',
4160 x_msg_count,
4161 x_msg_data,
4162 '_PVT'
4163 );
4164 WHEN OTHERS THEN
4165 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
4166 (
4167 l_api_name,
4168 G_PKG_NAME,
4169 'OTHERS',
4170 x_msg_count,
4171 x_msg_data,
4172 '_PVT'
4173 );
4174 END update_row;
4175 -------------------------------------------
4176 -- update_row for:OKL_TRX_AR_INVOICES_TL --
4177 -------------------------------------------
4178 PROCEDURE update_row(
4179 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
4180 x_return_status OUT NOCOPY VARCHAR2,
4181 x_msg_count OUT NOCOPY NUMBER,
4182 x_msg_data OUT NOCOPY VARCHAR2,
4183 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType,
4184 x_okl_trx_ar_invoices_tl_rec OUT NOCOPY OklTrxArInvoicesTlRecType) IS
4185
4186 l_api_version CONSTANT NUMBER := 1;
4187 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
4188 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4189 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType := p_okl_trx_ar_invoices_tl_rec;
4190 ldefokltrxarinvoicestlrec OklTrxArInvoicesTlRecType;
4191 l_row_notfound BOOLEAN := TRUE;
4192 ----------------------------------
4193 -- FUNCTION populate_new_record --
4194 ----------------------------------
4195 FUNCTION populate_new_record (
4196 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType,
4197 x_okl_trx_ar_invoices_tl_rec OUT NOCOPY OklTrxArInvoicesTlRecType
4198 ) RETURN VARCHAR2 IS
4199 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
4200 l_row_notfound BOOLEAN := TRUE;
4201 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4202 BEGIN
4203 x_okl_trx_ar_invoices_tl_rec := p_okl_trx_ar_invoices_tl_rec;
4204 -- Get current database values
4205 l_okl_trx_ar_invoices_tl_rec := get_rec(p_okl_trx_ar_invoices_tl_rec, l_row_notfound);
4206 IF (l_row_notfound) THEN
4207 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
4208 END IF;
4209 IF (x_okl_trx_ar_invoices_tl_rec.id = Okl_Api.G_MISS_NUM)
4210 THEN
4211 x_okl_trx_ar_invoices_tl_rec.id := l_okl_trx_ar_invoices_tl_rec.id;
4212 END IF;
4213 IF (x_okl_trx_ar_invoices_tl_rec.LANGUAGE = Okl_Api.G_MISS_CHAR)
4214 THEN
4215 x_okl_trx_ar_invoices_tl_rec.LANGUAGE := l_okl_trx_ar_invoices_tl_rec.LANGUAGE;
4216 END IF;
4217 IF (x_okl_trx_ar_invoices_tl_rec.source_lang = Okl_Api.G_MISS_CHAR)
4218 THEN
4219 x_okl_trx_ar_invoices_tl_rec.source_lang := l_okl_trx_ar_invoices_tl_rec.source_lang;
4220 END IF;
4221 IF (x_okl_trx_ar_invoices_tl_rec.sfwt_flag = Okl_Api.G_MISS_CHAR)
4222 THEN
4223 x_okl_trx_ar_invoices_tl_rec.sfwt_flag := l_okl_trx_ar_invoices_tl_rec.sfwt_flag;
4224 END IF;
4225 IF (x_okl_trx_ar_invoices_tl_rec.description = Okl_Api.G_MISS_CHAR)
4226 THEN
4227 x_okl_trx_ar_invoices_tl_rec.description := l_okl_trx_ar_invoices_tl_rec.description;
4228 END IF;
4229 IF (x_okl_trx_ar_invoices_tl_rec.created_by = Okl_Api.G_MISS_NUM)
4230 THEN
4231 x_okl_trx_ar_invoices_tl_rec.created_by := l_okl_trx_ar_invoices_tl_rec.created_by;
4232 END IF;
4233 IF (x_okl_trx_ar_invoices_tl_rec.creation_date = Okl_Api.G_MISS_DATE)
4234 THEN
4235 x_okl_trx_ar_invoices_tl_rec.creation_date := l_okl_trx_ar_invoices_tl_rec.creation_date;
4236 END IF;
4237 IF (x_okl_trx_ar_invoices_tl_rec.last_updated_by = Okl_Api.G_MISS_NUM)
4238 THEN
4239 x_okl_trx_ar_invoices_tl_rec.last_updated_by := l_okl_trx_ar_invoices_tl_rec.last_updated_by;
4240 END IF;
4241 IF (x_okl_trx_ar_invoices_tl_rec.last_update_date = Okl_Api.G_MISS_DATE)
4242 THEN
4243 x_okl_trx_ar_invoices_tl_rec.last_update_date := l_okl_trx_ar_invoices_tl_rec.last_update_date;
4244 END IF;
4245 IF (x_okl_trx_ar_invoices_tl_rec.last_update_login = Okl_Api.G_MISS_NUM)
4246 THEN
4247 x_okl_trx_ar_invoices_tl_rec.last_update_login := l_okl_trx_ar_invoices_tl_rec.last_update_login;
4248 END IF;
4249 RETURN(l_return_status);
4250 END populate_new_record;
4251 -----------------------------------------------
4252 -- Set_Attributes for:OKL_TRX_AR_INVOICES_TL --
4253 -----------------------------------------------
4254 FUNCTION Set_Attributes (
4255 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType,
4256 x_okl_trx_ar_invoices_tl_rec OUT NOCOPY OklTrxArInvoicesTlRecType
4257 ) RETURN VARCHAR2 IS
4258 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4259 BEGIN
4260 x_okl_trx_ar_invoices_tl_rec := p_okl_trx_ar_invoices_tl_rec;
4261 x_okl_trx_ar_invoices_tl_rec.LANGUAGE := USERENV('LANG');
4262 x_okl_trx_ar_invoices_tl_rec.SOURCE_LANG := USERENV('LANG');
4263 RETURN(l_return_status);
4264 END Set_Attributes;
4265 BEGIN
4266 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
4267 p_init_msg_list,
4268 '_PVT',
4269 x_return_status);
4270 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4271 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4272 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4273 RAISE Okl_Api.G_EXCEPTION_ERROR;
4274 END IF;
4275 --- Setting item attributes
4276 l_return_status := Set_Attributes(
4277 p_okl_trx_ar_invoices_tl_rec, -- IN
4278 l_okl_trx_ar_invoices_tl_rec); -- OUT
4279 --- If any errors happen abort API
4280 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4281 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4282 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4283 RAISE Okl_Api.G_EXCEPTION_ERROR;
4284 END IF;
4285 l_return_status := populate_new_record(l_okl_trx_ar_invoices_tl_rec, ldefokltrxarinvoicestlrec);
4286 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4287 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4288 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4289 RAISE Okl_Api.G_EXCEPTION_ERROR;
4290 END IF;
4291 UPDATE OKL_TRX_AR_INVOICES_TL
4292 SET DESCRIPTION = ldefokltrxarinvoicestlrec.description,
4293 SOURCE_LANG = ldefokltrxarinvoicestlrec.source_lang,
4294 CREATED_BY = ldefokltrxarinvoicestlrec.created_by,
4295 CREATION_DATE = ldefokltrxarinvoicestlrec.creation_date,
4296 LAST_UPDATED_BY = ldefokltrxarinvoicestlrec.last_updated_by,
4297 LAST_UPDATE_DATE = ldefokltrxarinvoicestlrec.last_update_date,
4298 LAST_UPDATE_LOGIN = ldefokltrxarinvoicestlrec.last_update_login
4299 WHERE ID = ldefokltrxarinvoicestlrec.id
4300 AND USERENV('LANG') in (SOURCE_LANG, LANGUAGE);
4301
4302 UPDATE OKL_TRX_AR_INVOICES_TL
4303 SET SFWT_FLAG = 'Y'
4304 WHERE ID = ldefokltrxarinvoicestlrec.id
4305 AND SOURCE_LANG <> USERENV('LANG');
4306
4307 x_okl_trx_ar_invoices_tl_rec := ldefokltrxarinvoicestlrec;
4308 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
4309 EXCEPTION
4310 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
4311 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
4312 (
4313 l_api_name,
4314 G_PKG_NAME,
4315 'OKL_API.G_RET_STS_ERROR',
4316 x_msg_count,
4317 x_msg_data,
4318 '_PVT'
4319 );
4320 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
4321 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
4322 (
4323 l_api_name,
4324 G_PKG_NAME,
4325 'OKL_API.G_RET_STS_UNEXP_ERROR',
4326 x_msg_count,
4327 x_msg_data,
4328 '_PVT'
4329 );
4330 WHEN OTHERS THEN
4331 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
4332 (
4333 l_api_name,
4334 G_PKG_NAME,
4335 'OTHERS',
4336 x_msg_count,
4337 x_msg_data,
4338 '_PVT'
4339 );
4340 END update_row;
4341 ------------------------------------------
4342 -- update_row for:OKL_TRX_AR_INVOICES_V --
4343 ------------------------------------------
4344 PROCEDURE update_row(
4345 p_api_version IN NUMBER,
4346 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
4347 x_return_status OUT NOCOPY VARCHAR2,
4348 x_msg_count OUT NOCOPY NUMBER,
4349 x_msg_data OUT NOCOPY VARCHAR2,
4350 p_taiv_rec IN taiv_rec_type,
4351 x_taiv_rec OUT NOCOPY taiv_rec_type) IS
4352
4353 l_api_version CONSTANT NUMBER := 1;
4354 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4355 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4356 l_taiv_rec taiv_rec_type := p_taiv_rec;
4357 l_def_taiv_rec taiv_rec_type;
4358 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
4359 lx_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
4360 l_tai_rec tai_rec_type;
4361 lx_tai_rec tai_rec_type;
4362 -------------------------------
4363 -- FUNCTION fill_who_columns --
4364 -------------------------------
4365 FUNCTION fill_who_columns (
4366 p_taiv_rec IN taiv_rec_type
4367 ) RETURN taiv_rec_type IS
4368 l_taiv_rec taiv_rec_type := p_taiv_rec;
4369 BEGIN
4370 l_taiv_rec.LAST_UPDATE_DATE := l_taiv_rec.CREATION_DATE;
4371 l_taiv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
4372 l_taiv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
4373 RETURN(l_taiv_rec);
4374 END fill_who_columns;
4375 ----------------------------------
4376 -- FUNCTION populate_new_record --
4377 ----------------------------------
4378 FUNCTION populate_new_record (
4379 p_taiv_rec IN taiv_rec_type,
4380 x_taiv_rec OUT NOCOPY taiv_rec_type
4381 ) RETURN VARCHAR2 IS
4382 l_taiv_rec taiv_rec_type;
4383 l_row_notfound BOOLEAN := TRUE;
4384 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4385 BEGIN
4386 x_taiv_rec := p_taiv_rec;
4387 -- Get current database values
4388 l_taiv_rec := get_rec(p_taiv_rec, l_row_notfound);
4389 IF (l_row_notfound) THEN
4390 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
4391 END IF;
4392 IF (x_taiv_rec.id = Okl_Api.G_MISS_NUM)
4393 THEN
4394 x_taiv_rec.id := l_taiv_rec.id;
4395 END IF;
4396 IF (x_taiv_rec.object_version_number = Okl_Api.G_MISS_NUM)
4397 THEN
4398 x_taiv_rec.object_version_number := l_taiv_rec.object_version_number;
4399 END IF;
4400 IF (x_taiv_rec.sfwt_flag = Okl_Api.G_MISS_CHAR)
4401 THEN
4402 x_taiv_rec.sfwt_flag := l_taiv_rec.sfwt_flag;
4403 END IF;
4404
4405 IF (x_taiv_rec.currency_code = Okl_Api.G_MISS_CHAR)
4406 THEN
4407 x_taiv_rec.currency_code := l_taiv_rec.currency_code;
4408 END IF;
4409
4410 --Start change by pgomes on 15-NOV-2002
4411 IF (x_taiv_rec.currency_conversion_type = Okl_Api.G_MISS_CHAR) THEN
4412 x_taiv_rec.currency_conversion_type := l_taiv_rec.currency_conversion_type;
4413 END IF;
4414
4415 IF (x_taiv_rec.currency_conversion_rate = Okl_Api.G_MISS_NUM) THEN
4416 x_taiv_rec.currency_conversion_rate := l_taiv_rec.currency_conversion_rate;
4417 END IF;
4418
4419 IF (x_taiv_rec.currency_conversion_date = Okl_Api.G_MISS_DATE) THEN
4420 x_taiv_rec.currency_conversion_date := l_taiv_rec.currency_conversion_date;
4421 END IF;
4422 --End change by pgomes on 15-NOV-2002
4423
4424
4425 IF (x_taiv_rec.khr_id = Okl_Api.G_MISS_NUM)
4426 THEN
4427 x_taiv_rec.khr_id := l_taiv_rec.khr_id;
4428 END IF;
4429 IF (x_taiv_rec.cra_id = Okl_Api.G_MISS_NUM)
4430 THEN
4431 x_taiv_rec.cra_id := l_taiv_rec.cra_id;
4432 END IF;
4433 IF (x_taiv_rec.tap_id = Okl_Api.G_MISS_NUM)
4434 THEN
4435 x_taiv_rec.tap_id := l_taiv_rec.tap_id;
4436 END IF;
4437 IF (x_taiv_rec.qte_id = Okl_Api.G_MISS_NUM)
4438 THEN
4439 x_taiv_rec.qte_id := l_taiv_rec.qte_id;
4440 END IF;
4441 IF (x_taiv_rec.tcn_id = Okl_Api.G_MISS_NUM)
4442 THEN
4443 x_taiv_rec.tcn_id := l_taiv_rec.tcn_id;
4444 END IF;
4445 IF (x_taiv_rec.tai_id_reverses = Okl_Api.G_MISS_NUM)
4446 THEN
4447 x_taiv_rec.tai_id_reverses := l_taiv_rec.tai_id_reverses;
4448 END IF;
4449 --Added after postgen changes
4450 IF (x_taiv_rec.ipy_id = Okl_Api.G_MISS_NUM)
4451 THEN
4452 x_taiv_rec.ipy_id := l_taiv_rec.ipy_id;
4453 END IF;
4454 IF (x_taiv_rec.trx_status_code = Okl_Api.G_MISS_CHAR)
4455 THEN
4456 x_taiv_rec.trx_status_code := l_taiv_rec.trx_status_code;
4457 END IF;
4458 IF (x_taiv_rec.set_of_books_id = Okl_Api.G_MISS_NUM)
4459 THEN
4460 x_taiv_rec.set_of_books_id := l_taiv_rec.set_of_books_id;
4461 END IF;
4462 IF (x_taiv_rec.try_id = Okl_Api.G_MISS_NUM)
4463 THEN
4464 x_taiv_rec.try_id := l_taiv_rec.try_id;
4465 END IF;
4466 --End Addition after postgen changes
4467 IF (x_taiv_rec.ibt_id = Okl_Api.G_MISS_NUM)
4468 THEN
4469 x_taiv_rec.ibt_id := l_taiv_rec.ibt_id;
4470 END IF;
4471 IF (x_taiv_rec.ixx_id = Okl_Api.G_MISS_NUM)
4472 THEN
4473 x_taiv_rec.ixx_id := l_taiv_rec.ixx_id;
4474 END IF;
4475 IF (x_taiv_rec.irm_id = Okl_Api.G_MISS_NUM)
4476 THEN
4477 x_taiv_rec.irm_id := l_taiv_rec.irm_id;
4478 END IF;
4479 IF (x_taiv_rec.irt_id = Okl_Api.G_MISS_NUM)
4480 THEN
4481 x_taiv_rec.irt_id := l_taiv_rec.irt_id;
4482 END IF;
4483 IF (x_taiv_rec.svf_id = Okl_Api.G_MISS_NUM)
4484 THEN
4485 x_taiv_rec.svf_id := l_taiv_rec.svf_id;
4486 END IF;
4487 IF (x_taiv_rec.amount = Okl_Api.G_MISS_NUM)
4488 THEN
4489 x_taiv_rec.amount := l_taiv_rec.amount;
4490 END IF;
4491 IF (x_taiv_rec.date_invoiced = Okl_Api.G_MISS_DATE)
4492 THEN
4493 x_taiv_rec.date_invoiced := l_taiv_rec.date_invoiced;
4494 END IF;
4495 IF (x_taiv_rec.amount_applied = Okl_Api.G_MISS_NUM)
4496 THEN
4497 x_taiv_rec.amount_applied := l_taiv_rec.amount_applied;
4498 END IF;
4499 IF (x_taiv_rec.description = Okl_Api.G_MISS_CHAR)
4500 THEN
4501 x_taiv_rec.description := l_taiv_rec.description;
4502 END IF;
4503
4504 IF (x_taiv_rec.trx_number = Okl_Api.G_MISS_CHAR)
4505 THEN
4506 x_taiv_rec.trx_number := l_taiv_rec.trx_number;
4507 END IF;
4508 IF (x_taiv_rec.clg_id = Okl_Api.G_MISS_NUM)
4509 THEN
4510 x_taiv_rec.clg_id := l_taiv_rec.clg_id;
4511 END IF;
4512
4513 --Start code added by pgomes on 19-NOV-2002
4514 IF (x_taiv_rec.pox_id = Okl_Api.G_MISS_NUM)
4515 THEN
4516 x_taiv_rec.pox_id := l_taiv_rec.pox_id;
4517 END IF;
4518 --End code added by pgomes on 19-NOV-2002
4519
4520 IF (x_taiv_rec.cpy_id = Okl_Api.G_MISS_NUM)
4521 THEN
4522 x_taiv_rec.cpy_id := l_taiv_rec.cpy_id;
4523 END IF;
4524 IF (x_taiv_rec.attribute_category = Okl_Api.G_MISS_CHAR)
4525 THEN
4526 x_taiv_rec.attribute_category := l_taiv_rec.attribute_category;
4527 END IF;
4528 IF (x_taiv_rec.attribute1 = Okl_Api.G_MISS_CHAR)
4529 THEN
4530 x_taiv_rec.attribute1 := l_taiv_rec.attribute1;
4531 END IF;
4532 IF (x_taiv_rec.attribute2 = Okl_Api.G_MISS_CHAR)
4533 THEN
4534 x_taiv_rec.attribute2 := l_taiv_rec.attribute2;
4535 END IF;
4536 IF (x_taiv_rec.attribute3 = Okl_Api.G_MISS_CHAR)
4537 THEN
4538 x_taiv_rec.attribute3 := l_taiv_rec.attribute3;
4539 END IF;
4540 IF (x_taiv_rec.attribute4 = Okl_Api.G_MISS_CHAR)
4541 THEN
4542 x_taiv_rec.attribute4 := l_taiv_rec.attribute4;
4543 END IF;
4544 IF (x_taiv_rec.attribute5 = Okl_Api.G_MISS_CHAR)
4545 THEN
4546 x_taiv_rec.attribute5 := l_taiv_rec.attribute5;
4547 END IF;
4548 IF (x_taiv_rec.attribute6 = Okl_Api.G_MISS_CHAR)
4549 THEN
4550 x_taiv_rec.attribute6 := l_taiv_rec.attribute6;
4551 END IF;
4552 IF (x_taiv_rec.attribute7 = Okl_Api.G_MISS_CHAR)
4553 THEN
4554 x_taiv_rec.attribute7 := l_taiv_rec.attribute7;
4555 END IF;
4556 IF (x_taiv_rec.attribute8 = Okl_Api.G_MISS_CHAR)
4557 THEN
4558 x_taiv_rec.attribute8 := l_taiv_rec.attribute8;
4559 END IF;
4560 IF (x_taiv_rec.attribute9 = Okl_Api.G_MISS_CHAR)
4561 THEN
4562 x_taiv_rec.attribute9 := l_taiv_rec.attribute9;
4563 END IF;
4564 IF (x_taiv_rec.attribute10 = Okl_Api.G_MISS_CHAR)
4565 THEN
4566 x_taiv_rec.attribute10 := l_taiv_rec.attribute10;
4567 END IF;
4568 IF (x_taiv_rec.attribute11 = Okl_Api.G_MISS_CHAR)
4569 THEN
4570 x_taiv_rec.attribute11 := l_taiv_rec.attribute11;
4571 END IF;
4572 IF (x_taiv_rec.attribute12 = Okl_Api.G_MISS_CHAR)
4573 THEN
4574 x_taiv_rec.attribute12 := l_taiv_rec.attribute12;
4575 END IF;
4576 IF (x_taiv_rec.attribute13 = Okl_Api.G_MISS_CHAR)
4577 THEN
4578 x_taiv_rec.attribute13 := l_taiv_rec.attribute13;
4579 END IF;
4580 IF (x_taiv_rec.attribute14 = Okl_Api.G_MISS_CHAR)
4581 THEN
4582 x_taiv_rec.attribute14 := l_taiv_rec.attribute14;
4583 END IF;
4584 IF (x_taiv_rec.attribute15 = Okl_Api.G_MISS_CHAR)
4585 THEN
4586 x_taiv_rec.attribute15 := l_taiv_rec.attribute15;
4587 END IF;
4588 IF (x_taiv_rec.date_entered = Okl_Api.G_MISS_DATE)
4589 THEN
4590 x_taiv_rec.date_entered := l_taiv_rec.date_entered;
4591 END IF;
4592 IF (x_taiv_rec.request_id = Okl_Api.G_MISS_NUM)
4593 THEN
4594 x_taiv_rec.request_id := l_taiv_rec.request_id;
4595 END IF;
4596 IF (x_taiv_rec.program_application_id = Okl_Api.G_MISS_NUM)
4597 THEN
4598 x_taiv_rec.program_application_id := l_taiv_rec.program_application_id;
4599 END IF;
4600 IF (x_taiv_rec.program_id = Okl_Api.G_MISS_NUM)
4601 THEN
4602 x_taiv_rec.program_id := l_taiv_rec.program_id;
4603 END IF;
4604 IF (x_taiv_rec.program_update_date = Okl_Api.G_MISS_DATE)
4605 THEN
4606 x_taiv_rec.program_update_date := l_taiv_rec.program_update_date;
4607 END IF;
4608 IF (x_taiv_rec.org_id = Okl_Api.G_MISS_NUM)
4609 THEN
4610 x_taiv_rec.org_id := l_taiv_rec.org_id;
4611 END IF;
4612 IF (x_taiv_rec.created_by = Okl_Api.G_MISS_NUM)
4613 THEN
4614 x_taiv_rec.created_by := l_taiv_rec.created_by;
4615 END IF;
4616 IF (x_taiv_rec.creation_date = Okl_Api.G_MISS_DATE)
4617 THEN
4618 x_taiv_rec.creation_date := l_taiv_rec.creation_date;
4619 END IF;
4620 IF (x_taiv_rec.last_updated_by = Okl_Api.G_MISS_NUM)
4621 THEN
4622 x_taiv_rec.last_updated_by := l_taiv_rec.last_updated_by;
4623 END IF;
4624 IF (x_taiv_rec.last_update_date = Okl_Api.G_MISS_DATE)
4625 THEN
4626 x_taiv_rec.last_update_date := l_taiv_rec.last_update_date;
4627 END IF;
4628 IF (x_taiv_rec.last_update_login = Okl_Api.G_MISS_NUM)
4629 THEN
4630 x_taiv_rec.last_update_login := l_taiv_rec.last_update_login;
4631 END IF;
4632 -- for LE Uptake project 08-11-2006
4633 IF (x_taiv_rec.legal_entity_id = Okl_Api.G_MISS_NUM)
4634 THEN
4635 x_taiv_rec.legal_entity_id := l_taiv_rec.legal_entity_id;
4636 END IF;
4637 -- for LE Uptake project 08-11-2006
4638
4639 -- start:30-Jan-07 cklee Billing R12 project
4640 --gkhuntet 10-JUL-07 Start
4641 -- Investor_Agreement_Number,
4642 IF (x_taiv_rec.Investor_Name = Okl_Api.G_MISS_CHAR) THEN
4643 x_taiv_rec.Investor_Name := l_tai_rec.Investor_Name;
4644 END IF;
4645
4646 -- OKL_SOURCE_BILLING_TRX,
4647 IF (x_taiv_rec.OKL_SOURCE_BILLING_TRX = Okl_Api.G_MISS_CHAR) THEN
4648 x_taiv_rec.OKL_SOURCE_BILLING_TRX := l_tai_rec.OKL_SOURCE_BILLING_TRX;
4649 END IF;
4650
4651 -- INF_ID,
4652 IF (x_taiv_rec.INF_ID = Okl_Api.G_MISS_NUM) THEN
4653 x_taiv_rec.INF_ID := l_tai_rec.INF_ID;
4654 END IF;
4655
4656 -- INVOICE_PULL_YN,
4657 IF (x_taiv_rec.INVOICE_PULL_YN = Okl_Api.G_MISS_CHAR) THEN
4658 x_taiv_rec.INVOICE_PULL_YN := l_tai_rec.INVOICE_PULL_YN;
4659 END IF;
4660
4661 -- CONSOLIDATED_INVOICE_NUMBER,
4662 IF (x_taiv_rec.CONSOLIDATED_INVOICE_NUMBER = Okl_Api.G_MISS_CHAR) THEN
4663 x_taiv_rec.CONSOLIDATED_INVOICE_NUMBER := l_tai_rec.CONSOLIDATED_INVOICE_NUMBER;
4664 END IF;
4665
4666 -- DUE_DATE,
4667 IF (x_taiv_rec.DUE_DATE = Okl_Api.G_MISS_DATE) THEN
4668 x_taiv_rec.DUE_DATE := l_tai_rec.DUE_DATE;
4669 END IF;
4670
4671 -- ISI_ID,
4672 IF (x_taiv_rec.ISI_ID = Okl_Api.G_MISS_NUM) THEN
4673 x_taiv_rec.ISI_ID := l_tai_rec.ISI_ID;
4674 END IF;
4675
4676 -- RECEIVABLES_INVOICE_ID,
4677 IF (x_taiv_rec.RECEIVABLES_INVOICE_ID = Okl_Api.G_MISS_NUM) THEN
4678 x_taiv_rec.RECEIVABLES_INVOICE_ID := l_tai_rec.RECEIVABLES_INVOICE_ID;
4679 END IF;
4680
4681 -- CUST_TRX_TYPE_ID,
4682 IF (x_taiv_rec.CUST_TRX_TYPE_ID = Okl_Api.G_MISS_NUM) THEN
4683 x_taiv_rec.CUST_TRX_TYPE_ID := l_tai_rec.CUST_TRX_TYPE_ID;
4684 END IF;
4685
4686 -- CUSTOMER_BANK_ACCOUNT_ID,
4687 IF (x_taiv_rec.CUSTOMER_BANK_ACCOUNT_ID = Okl_Api.G_MISS_NUM) THEN
4688 x_taiv_rec.CUSTOMER_BANK_ACCOUNT_ID := l_tai_rec.CUSTOMER_BANK_ACCOUNT_ID;
4689 END IF;
4690
4691 -- TAX_EXEMPT_FLAG,
4692 IF (x_taiv_rec.TAX_EXEMPT_FLAG = Okl_Api.G_MISS_CHAR) THEN
4693 x_taiv_rec.TAX_EXEMPT_FLAG := l_tai_rec.TAX_EXEMPT_FLAG;
4694 END IF;
4695
4696 -- TAX_EXEMPT_REASON_CODE,
4697 IF (x_taiv_rec.TAX_EXEMPT_REASON_CODE = Okl_Api.G_MISS_CHAR) THEN
4698 x_taiv_rec.TAX_EXEMPT_REASON_CODE := l_tai_rec.TAX_EXEMPT_REASON_CODE;
4699 END IF;
4700
4701 -- REFERENCE_LINE_ID
4702 IF (x_taiv_rec.REFERENCE_LINE_ID = Okl_Api.G_MISS_NUM) THEN
4703 x_taiv_rec.REFERENCE_LINE_ID := l_tai_rec.REFERENCE_LINE_ID;
4704 END IF;
4705
4706 -- PRIVATE_LABEL
4707 IF (x_taiv_rec.PRIVATE_LABEL = Okl_Api.G_MISS_CHAR) THEN
4708 x_taiv_rec.PRIVATE_LABEL := l_tai_rec.PRIVATE_LABEL;
4709 END IF;
4710 --gkhuntet 10-JUL-07 END
4711 -- end:30-Jan-07 cklee Billing R12 project
4712
4713 --gkhuntet start 02-Nov-07
4714 IF (x_taiv_rec.TRANSACTION_DATE = Okl_Api.G_MISS_DATE OR x_taiv_rec.TRANSACTION_DATE IS NULL) THEN
4715 x_taiv_rec.TRANSACTION_DATE := l_tai_rec.TRANSACTION_DATE ;
4716 END IF;
4717 --gkhuntet end 02-Nov-07
4718
4719 RETURN(l_return_status);
4720 END populate_new_record;
4721 ----------------------------------------------
4722 -- Set_Attributes for:OKL_TRX_AR_INVOICES_V --
4723 ----------------------------------------------
4724 FUNCTION Set_Attributes (
4725 p_taiv_rec IN taiv_rec_type,
4726 x_taiv_rec OUT NOCOPY taiv_rec_type
4727 ) RETURN VARCHAR2 IS
4728 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4729 BEGIN
4730 x_taiv_rec := p_taiv_rec;
4731 x_taiv_rec.OBJECT_VERSION_NUMBER := NVL(x_taiv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
4732
4733 IF (x_taiv_rec.request_id IS NULL OR x_taiv_rec.request_id = Okl_Api.G_MISS_NUM) THEN
4734 -- Begin Post-Generation Change
4735 SELECT
4736 NVL(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
4737 x_taiv_rec.request_id),
4738 NVL(DECODE(Fnd_Global.PROG_APPL_ID, -1,NULL,Fnd_Global.PROG_APPL_ID),
4739 x_taiv_rec.program_application_id),
4740 NVL(DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
4741 x_taiv_rec.program_id),
4742 DECODE(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE),
4743 NULL,x_taiv_rec.program_update_date,SYSDATE)
4744 INTO
4745 x_taiv_rec.request_id,
4746 x_taiv_rec.program_application_id,
4747 x_taiv_rec.program_id,
4748 x_taiv_rec.program_update_date
4749 FROM dual;
4750 -- End Post-Generation Change
4751 END IF;
4752
4753 RETURN(l_return_status);
4754 END Set_Attributes;
4755 BEGIN
4756 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
4757 G_PKG_NAME,
4758 p_init_msg_list,
4759 l_api_version,
4760 p_api_version,
4761 '_PVT',
4762 x_return_status);
4763 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4764 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4765 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4766 RAISE Okl_Api.G_EXCEPTION_ERROR;
4767 END IF;
4768 --- Setting item attributes
4769 l_return_status := Set_Attributes(
4770 p_taiv_rec, -- IN
4771 l_taiv_rec); -- OUT
4772 --- If any errors happen abort API
4773 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4774 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4775 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4776 RAISE Okl_Api.G_EXCEPTION_ERROR;
4777 END IF;
4778 l_return_status := populate_new_record(l_taiv_rec, l_def_taiv_rec);
4779 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4780 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4781 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4782 RAISE Okl_Api.G_EXCEPTION_ERROR;
4783 END IF;
4784 l_def_taiv_rec := fill_who_columns(l_def_taiv_rec);
4785 --- Validate all non-missing attributes (Item Level Validation)
4786 --l_return_status := Validate_Attributes(l_def_taiv_rec); --20-May-08 sechawla 6619311
4787 --- If any errors happen abort API
4788 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4789 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4790 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4791 RAISE Okl_Api.G_EXCEPTION_ERROR;
4792 END IF;
4793 l_return_status := Validate_Record(l_def_taiv_rec);
4794 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4795 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4796 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4797 RAISE Okl_Api.G_EXCEPTION_ERROR;
4798 END IF;
4799
4800 --------------------------------------
4801 -- Move VIEW record to "Child" records
4802 --------------------------------------
4803 migrate(l_def_taiv_rec, l_okl_trx_ar_invoices_tl_rec);
4804 migrate(l_def_taiv_rec, l_tai_rec);
4805 --------------------------------------------
4806 -- Call the UPDATE_ROW for each child record
4807 --------------------------------------------
4808 update_row(
4809 p_init_msg_list,
4810 x_return_status,
4811 x_msg_count,
4812 x_msg_data,
4813 l_okl_trx_ar_invoices_tl_rec,
4814 lx_okl_trx_ar_invoices_tl_rec
4815 );
4816 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4817 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4818 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4819 RAISE Okl_Api.G_EXCEPTION_ERROR;
4820 END IF;
4821 migrate(lx_okl_trx_ar_invoices_tl_rec, l_def_taiv_rec);
4822 update_row(
4823 p_init_msg_list,
4824 x_return_status,
4825 x_msg_count,
4826 x_msg_data,
4827 l_tai_rec,
4828 lx_tai_rec
4829 );
4830 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4831 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4832 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4833 RAISE Okl_Api.G_EXCEPTION_ERROR;
4834 END IF;
4835 migrate(lx_tai_rec, l_def_taiv_rec);
4836 x_taiv_rec := l_def_taiv_rec;
4837 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
4838 EXCEPTION
4839 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
4840 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
4841 (
4842 l_api_name,
4843 G_PKG_NAME,
4844 'OKL_API.G_RET_STS_ERROR',
4845 x_msg_count,
4846 x_msg_data,
4847 '_PVT'
4848 );
4849 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
4850 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
4851 (
4852 l_api_name,
4853 G_PKG_NAME,
4854 'OKL_API.G_RET_STS_UNEXP_ERROR',
4855 x_msg_count,
4856 x_msg_data,
4857 '_PVT'
4858 );
4859 WHEN OTHERS THEN
4860 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
4861 (
4862 l_api_name,
4863 G_PKG_NAME,
4864 'OTHERS',
4865 x_msg_count,
4866 x_msg_data,
4867 '_PVT'
4868 );
4869 END update_row;
4870 ----------------------------------------
4871 -- PL/SQL TBL update_row for:TAIV_TBL --
4872 ----------------------------------------
4873 PROCEDURE update_row(
4874 p_api_version IN NUMBER,
4875 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
4876 x_return_status OUT NOCOPY VARCHAR2,
4877 x_msg_count OUT NOCOPY NUMBER,
4878 x_msg_data OUT NOCOPY VARCHAR2,
4879 p_taiv_tbl IN taiv_tbl_type,
4880 x_taiv_tbl OUT NOCOPY taiv_tbl_type) IS
4881
4882 l_api_version CONSTANT NUMBER := 1;
4883 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
4884 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4885 -- Begin Post-Generation Change
4886 -- overall error status
4887 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4888 -- End Post-Generation Change
4889 i NUMBER := 0;
4890 BEGIN
4891 Okl_Api.init_msg_list(p_init_msg_list);
4892 -- Make sure PL/SQL table has records in it before passing
4893 IF (p_taiv_tbl.COUNT > 0) THEN
4894 i := p_taiv_tbl.FIRST;
4895 LOOP
4896 update_row (
4897 p_api_version => p_api_version,
4898 p_init_msg_list => Okl_Api.G_FALSE,
4899 x_return_status => x_return_status,
4900 x_msg_count => x_msg_count,
4901 x_msg_data => x_msg_data,
4902 p_taiv_rec => p_taiv_tbl(i),
4903 x_taiv_rec => x_taiv_tbl(i));
4904
4905 -- Begin Post-Generation Change
4906 -- store the highest degree of error
4907 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
4908 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
4909 l_overall_status := x_return_status;
4910 END IF;
4911 END IF;
4912 -- End Post-Generation Change
4913
4914 EXIT WHEN (i = p_taiv_tbl.LAST);
4915 i := p_taiv_tbl.NEXT(i);
4916 END LOOP;
4917 -- Begin Post-Generation Change
4918 -- return overall status
4919 x_return_status := l_overall_status;
4920 -- End Post-Generation Change
4921 END IF;
4922 EXCEPTION
4923 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
4924 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
4925 (
4926 l_api_name,
4927 G_PKG_NAME,
4928 'OKL_API.G_RET_STS_ERROR',
4929 x_msg_count,
4930 x_msg_data,
4931 '_PVT'
4932 );
4933 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
4934 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
4935 (
4936 l_api_name,
4937 G_PKG_NAME,
4938 'OKL_API.G_RET_STS_UNEXP_ERROR',
4939 x_msg_count,
4940 x_msg_data,
4941 '_PVT'
4942 );
4943 WHEN OTHERS THEN
4944 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
4945 (
4946 l_api_name,
4947 G_PKG_NAME,
4948 'OTHERS',
4949 x_msg_count,
4950 x_msg_data,
4951 '_PVT'
4952 );
4953 END update_row;
4954
4955 ---------------------------------------------------------------------------
4956 -- PROCEDURE delete_row
4957 ---------------------------------------------------------------------------
4958 ------------------------------------------
4959 -- delete_row for:OKL_TRX_AR_INVOICES_B --
4960 ------------------------------------------
4961 PROCEDURE delete_row(
4962 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
4963 x_return_status OUT NOCOPY VARCHAR2,
4964 x_msg_count OUT NOCOPY NUMBER,
4965 x_msg_data OUT NOCOPY VARCHAR2,
4966 p_tai_rec IN tai_rec_type) IS
4967
4968 l_api_version CONSTANT NUMBER := 1;
4969 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
4970 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4971 l_tai_rec tai_rec_type:= p_tai_rec;
4972 l_row_notfound BOOLEAN := TRUE;
4973 BEGIN
4974 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
4975 p_init_msg_list,
4976 '_PVT',
4977 x_return_status);
4978 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4979 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4980 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4981 RAISE Okl_Api.G_EXCEPTION_ERROR;
4982 END IF;
4983 DELETE FROM OKL_TRX_AR_INVOICES_B
4984 WHERE ID = l_tai_rec.id;
4985
4986 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
4987 EXCEPTION
4988 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
4989 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
4990 (
4991 l_api_name,
4992 G_PKG_NAME,
4993 'OKL_API.G_RET_STS_ERROR',
4994 x_msg_count,
4995 x_msg_data,
4996 '_PVT'
4997 );
4998 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
4999 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
5000 (
5001 l_api_name,
5002 G_PKG_NAME,
5003 'OKL_API.G_RET_STS_UNEXP_ERROR',
5004 x_msg_count,
5005 x_msg_data,
5006 '_PVT'
5007 );
5008 WHEN OTHERS THEN
5009 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
5010 (
5011 l_api_name,
5012 G_PKG_NAME,
5013 'OTHERS',
5014 x_msg_count,
5015 x_msg_data,
5016 '_PVT'
5017 );
5018 END delete_row;
5019 -------------------------------------------
5020 -- delete_row for:OKL_TRX_AR_INVOICES_TL --
5021 -------------------------------------------
5022 PROCEDURE delete_row(
5023 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
5024 x_return_status OUT NOCOPY VARCHAR2,
5025 x_msg_count OUT NOCOPY NUMBER,
5026 x_msg_data OUT NOCOPY VARCHAR2,
5027 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType) IS
5028
5029 l_api_version CONSTANT NUMBER := 1;
5030 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
5031 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5032 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType:= p_okl_trx_ar_invoices_tl_rec;
5033 l_row_notfound BOOLEAN := TRUE;
5034 -----------------------------------------------
5035 -- Set_Attributes for:OKL_TRX_AR_INVOICES_TL --
5036 -----------------------------------------------
5037 FUNCTION Set_Attributes (
5038 p_okl_trx_ar_invoices_tl_rec IN OklTrxArInvoicesTlRecType,
5039 x_okl_trx_ar_invoices_tl_rec OUT NOCOPY OklTrxArInvoicesTlRecType
5040 ) RETURN VARCHAR2 IS
5041 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5042 BEGIN
5043 x_okl_trx_ar_invoices_tl_rec := p_okl_trx_ar_invoices_tl_rec;
5044 x_okl_trx_ar_invoices_tl_rec.LANGUAGE := USERENV('LANG');
5045 RETURN(l_return_status);
5046 END Set_Attributes;
5047 BEGIN
5048 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
5049 p_init_msg_list,
5050 '_PVT',
5051 x_return_status);
5052 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5053 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5054 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5055 RAISE Okl_Api.G_EXCEPTION_ERROR;
5056 END IF;
5057 --- Setting item attributes
5058 l_return_status := Set_Attributes(
5059 p_okl_trx_ar_invoices_tl_rec, -- IN
5060 l_okl_trx_ar_invoices_tl_rec); -- OUT
5061 --- If any errors happen abort API
5062 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5063 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5064 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5065 RAISE Okl_Api.G_EXCEPTION_ERROR;
5066 END IF;
5067 DELETE FROM OKL_TRX_AR_INVOICES_TL
5068 WHERE ID = l_okl_trx_ar_invoices_tl_rec.id;
5069
5070 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
5071 EXCEPTION
5072 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5073 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
5074 (
5075 l_api_name,
5076 G_PKG_NAME,
5077 'OKL_API.G_RET_STS_ERROR',
5078 x_msg_count,
5079 x_msg_data,
5080 '_PVT'
5081 );
5082 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5083 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
5084 (
5085 l_api_name,
5086 G_PKG_NAME,
5087 'OKL_API.G_RET_STS_UNEXP_ERROR',
5088 x_msg_count,
5089 x_msg_data,
5090 '_PVT'
5091 );
5092 WHEN OTHERS THEN
5093 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
5094 (
5095 l_api_name,
5096 G_PKG_NAME,
5097 'OTHERS',
5098 x_msg_count,
5099 x_msg_data,
5100 '_PVT'
5101 );
5102 END delete_row;
5103 ------------------------------------------
5104 -- delete_row for:OKL_TRX_AR_INVOICES_V --
5105 ------------------------------------------
5106 PROCEDURE delete_row(
5107 p_api_version IN NUMBER,
5108 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
5109 x_return_status OUT NOCOPY VARCHAR2,
5110 x_msg_count OUT NOCOPY NUMBER,
5111 x_msg_data OUT NOCOPY VARCHAR2,
5112 p_taiv_rec IN taiv_rec_type) IS
5113
5114 l_api_version CONSTANT NUMBER := 1;
5115 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
5116 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5117 l_taiv_rec taiv_rec_type := p_taiv_rec;
5118 l_okl_trx_ar_invoices_tl_rec OklTrxArInvoicesTlRecType;
5119 l_tai_rec tai_rec_type;
5120 BEGIN
5121 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
5122 G_PKG_NAME,
5123 p_init_msg_list,
5124 l_api_version,
5125 p_api_version,
5126 '_PVT',
5127 x_return_status);
5128 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5129 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5130 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5131 RAISE Okl_Api.G_EXCEPTION_ERROR;
5132 END IF;
5133 --------------------------------------
5134 -- Move VIEW record to "Child" records
5135 --------------------------------------
5136 migrate(l_taiv_rec, l_okl_trx_ar_invoices_tl_rec);
5137 migrate(l_taiv_rec, l_tai_rec);
5138 --------------------------------------------
5139 -- Call the DELETE_ROW for each child record
5140 --------------------------------------------
5141 delete_row(
5142 p_init_msg_list,
5143 x_return_status,
5144 x_msg_count,
5145 x_msg_data,
5146 l_okl_trx_ar_invoices_tl_rec
5147 );
5148 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5149 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5150 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5151 RAISE Okl_Api.G_EXCEPTION_ERROR;
5152 END IF;
5153 delete_row(
5154 p_init_msg_list,
5155 x_return_status,
5156 x_msg_count,
5157 x_msg_data,
5158 l_tai_rec
5159 );
5160 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
5161 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
5162 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
5163 RAISE Okl_Api.G_EXCEPTION_ERROR;
5164 END IF;
5165 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
5166 EXCEPTION
5167 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5168 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
5169 (
5170 l_api_name,
5171 G_PKG_NAME,
5172 'OKL_API.G_RET_STS_ERROR',
5173 x_msg_count,
5174 x_msg_data,
5175 '_PVT'
5176 );
5177 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5178 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
5179 (
5180 l_api_name,
5181 G_PKG_NAME,
5182 'OKL_API.G_RET_STS_UNEXP_ERROR',
5183 x_msg_count,
5184 x_msg_data,
5185 '_PVT'
5186 );
5187 WHEN OTHERS THEN
5188 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
5189 (
5190 l_api_name,
5191 G_PKG_NAME,
5192 'OTHERS',
5193 x_msg_count,
5194 x_msg_data,
5195 '_PVT'
5196 );
5197 END delete_row;
5198 ----------------------------------------
5199 -- PL/SQL TBL delete_row for:TAIV_TBL --
5200 ----------------------------------------
5201 PROCEDURE delete_row(
5202 p_api_version IN NUMBER,
5203 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
5204 x_return_status OUT NOCOPY VARCHAR2,
5205 x_msg_count OUT NOCOPY NUMBER,
5206 x_msg_data OUT NOCOPY VARCHAR2,
5207 p_taiv_tbl IN taiv_tbl_type) IS
5208
5209 l_api_version CONSTANT NUMBER := 1;
5210 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
5211 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5212 -- Begin Post-Generation Change
5213 -- overall error status
5214 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
5215 -- End Post-Generation Change
5216 i NUMBER := 0;
5217 BEGIN
5218 Okl_Api.init_msg_list(p_init_msg_list);
5219 -- Make sure PL/SQL table has records in it before passing
5220 IF (p_taiv_tbl.COUNT > 0) THEN
5221 i := p_taiv_tbl.FIRST;
5222 LOOP
5223 delete_row (
5224 p_api_version => p_api_version,
5225 p_init_msg_list => Okl_Api.G_FALSE,
5226 x_return_status => x_return_status,
5227 x_msg_count => x_msg_count,
5228 x_msg_data => x_msg_data,
5229 p_taiv_rec => p_taiv_tbl(i));
5230 -- Begin Post-Generation Change
5231 -- store the highest degree of error
5232 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
5233 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
5234 l_overall_status := x_return_status;
5235 END IF;
5236 END IF;
5237 -- End Post-Generation Change
5238 EXIT WHEN (i = p_taiv_tbl.LAST);
5239 i := p_taiv_tbl.NEXT(i);
5240 END LOOP;
5241
5242 -- Begin Post-Generation Change
5243 -- return overall status
5244 x_return_status := l_overall_status;
5245 -- End Post-Generation Change
5246 END IF;
5247 EXCEPTION
5248 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5249 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
5250 (
5251 l_api_name,
5252 G_PKG_NAME,
5253 'OKL_API.G_RET_STS_ERROR',
5254 x_msg_count,
5255 x_msg_data,
5256 '_PVT'
5257 );
5258 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5259 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
5260 (
5261 l_api_name,
5262 G_PKG_NAME,
5263 'OKL_API.G_RET_STS_UNEXP_ERROR',
5264 x_msg_count,
5265 x_msg_data,
5266 '_PVT'
5267 );
5268 WHEN OTHERS THEN
5269 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
5270 (
5271 l_api_name,
5272 G_PKG_NAME,
5273 'OTHERS',
5274 x_msg_count,
5275 x_msg_data,
5276 '_PVT'
5277 );
5278 END delete_row;
5279
5280 END Okl_Tai_Pvt;