[Home] [Help]
PACKAGE BODY: APPS.OKL_INTERNAL_BILLING_PVT
Source
1 PACKAGE BODY OKL_INTERNAL_BILLING_PVT AS
2 /* $Header: OKLRIARB.pls 120.24 2008/06/17 04:14:35 asahoo noship $ */
3 ----------------------------------------------------------------------------
4 -- Data Structures
5 ----------------------------------------------------------------------------
6 subtype taiv_rec_type is okl_tai_pvt.taiv_rec_type;
7 subtype taiv_tbl_type is okl_tai_pvt.taiv_tbl_type;
8 subtype tilv_rec_type is okl_til_pvt.tilv_rec_type;
9 subtype tilv_tbl_type is okl_til_pvt.tilv_tbl_type;
10 subtype tldv_rec_type is okl_tld_pvt.tldv_rec_type;
11 subtype tldv_tbl_type is okl_tld_pvt.tldv_tbl_type;
12
13 ----------------------------------------------------------------------------
14 -- Variables For Debugging and Logging
15 ----------------------------------------------------------------------------
16 G_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES';
17 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
18 G_LEVEL_PROCEDURE NUMBER;
19 G_IS_DEBUG_PROCEDURE_ON BOOLEAN;
20 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
21 G_IS_STREAM_BASED_BILLING BOOLEAN := NULL;
22
23 --gkhuntet added start.
24 G_SUBMITTED CONSTANT VARCHAR2(30) := 'SUBMITTED';
25 G_MANUAL CONSTANT VARCHAR2(30) := 'MANUAL_INVOICE';
26 --gkhuntet added end.
27
28 ----------------------------------------------------------------------------
29 -- Global Message Constants
30 ----------------------------------------------------------------------------
31
32
33 ----------------------------------------------------------------------------
34 -- Procedures and Functions
35 ----------------------------------------------------------------------------
36
37
38 ----------------------------------------------------------------------------------
39 -- Start of comments
40 --
41 -- Procedure Name : create_accounting_dist
42 -- Description : abstract api to make Accounting transactions
43 -- Parameters :
44 -- p_tldv_tbl: Internal billing invoice/invoce line (OKL_TXD_AR_LN_DTLS_V)
45 -- Version : 1.0
46 -- End of comments
47 ----------------------------------------------------------------------------------
48
49 PROCEDURE create_accounting_dist(
50 p_api_version IN NUMBER
51 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
52 ,x_return_status OUT NOCOPY VARCHAR2
53 ,x_msg_count OUT NOCOPY NUMBER
54 ,x_msg_data OUT NOCOPY VARCHAR2
55 ,p_tldv_tbl IN okl_tld_pvt.tldv_tbl_type
56 ,p_tai_id IN OKL_TRX_AR_INVOICES_B.ID%TYPE
57 )
58 IS
59 l_api_name CONSTANT VARCHAR2(30) := 'create_accounting_dist';
60 l_api_version CONSTANT NUMBER := 1.0;
61 l_til_loop_cnt NUMBER;
62 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
63 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type := p_tldv_tbl;
64 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
65 l_tld_loop_cnt NUMBER;
66 lx_tldv_rec okl_tld_pvt.tldv_rec_type;
67 l_til_id NUMBER;
68 l_trx_header_id OKL_TRX_AR_INVOICES_B.ID%TYPE;
69 l_til_debug_cnt NUMBER;
70 l_tld_debug_cnt NUMBER;
71 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
72
73
74 l_tmpl_identify_rec Okl_Account_Dist_Pub.TMPL_IDENTIFY_REC_TYPE;
75 l_dist_info_rec Okl_Account_Dist_Pub.DIST_INFO_REC_TYPE;
76 l_ctxt_val_tbl Okl_Account_Dist_Pub.CTXT_VAL_TBL_TYPE;
77 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.ACC_GEN_PRIMARY_KEY;
78
79 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.TMPL_IDENTIFY_TBL_TYPE;
80 l_dist_info_tbl Okl_Account_Dist_Pvt.DIST_INFO_TBL_TYPE;
81 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
82 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
83 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
84 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
85 l_tcn_id NUMBER;
86 l_trx_header_tbl Varchar2(50);
87
88 BEGIN
89 SAVEPOINT CREATE_ACCOUNTING_DIST;
90 l_tld_loop_cnt := 0;
91 -- 6. Process accounting distributions;
92 l_tld_loop_cnt := lp_tldv_tbl.first;
93
94 loop
95 --FOR l_tld_loop_cnt in 1 .. lp_tldv_tbl.count loop
96 p_bpd_acc_rec.id := lp_tldv_tbl(l_tld_loop_cnt).id;
97 p_bpd_acc_rec.source_table := 'OKL_TXD_AR_LN_DTLS_B';
98
99
100
101 /* apaul -- Code commented out because new accing API uptake not complete
102 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(p_api_version => p_api_version,
103 p_init_msg_list => p_init_msg_list,
104 x_return_status => l_return_status,
105 x_msg_count => x_msg_count,
106 x_msg_data => x_msg_data,
107 p_bpd_acc_rec => p_bpd_acc_rec);
108
109 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
110 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
111 x_return_status := l_return_status;
112 END IF;
113 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
114 END IF;
115
116 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
117 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Okl_Acc_Call_Pub.CREATE_ACC_TRANS:p_bpd_acc_rec.id: '||to_char(p_bpd_acc_rec.id));
118 END IF;
119 */
120
121 ---- Added by Vpanwar --- Code for new accounting API uptake
122
123 Okl_Acc_Call_Pub.CREATE_ACC_TRANS_NEW(p_api_version => p_api_version,
124 p_init_msg_list => p_init_msg_list,
125 x_return_status => l_return_status,
126 x_msg_count => x_msg_count,
127 x_msg_data => x_msg_data,
128 p_bpd_acc_rec => p_bpd_acc_rec,
129 x_tmpl_identify_rec => l_tmpl_identify_rec,
130 x_dist_info_rec => l_dist_info_rec,
131 x_ctxt_val_tbl => l_ctxt_val_tbl,
132 x_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
133
134 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
135 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
136 x_return_status := l_return_status;
137 END IF;
138 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
139 END IF;
140
141 --debug_proc('Vipp 333...p_bpd_acc_rec.id '||p_bpd_acc_rec.id );
142 --- populate the tables for passing to Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST
143
144 l_acc_gen_tbl(l_tld_loop_cnt).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
145 l_acc_gen_tbl(l_tld_loop_cnt).source_id := l_dist_info_rec.source_id;
146
147 l_ctxt_tbl(l_tld_loop_cnt).ctxt_val_tbl := l_ctxt_val_tbl;
148 l_ctxt_tbl(l_tld_loop_cnt).source_id := l_dist_info_rec.source_id;
149
150 l_tmpl_identify_tbl(l_tld_loop_cnt) := l_tmpl_identify_rec;
151
152 l_dist_info_tbl(l_tld_loop_cnt) := l_dist_info_rec;
153
154 ---- End Added by Vpanwar --- Code for new accounting API uptake
155
156 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
157 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Okl_Acc_Call_Pub.CREATE_ACC_TRANS_NEW:p_bpd_acc_rec.id: '||to_char(p_bpd_acc_rec.id));
158 END IF;
159
160 EXIT WHEN l_tld_loop_cnt = lp_tldv_tbl.LAST;
161 l_tld_loop_cnt := lp_tldv_tbl.NEXT(l_tld_loop_cnt);
162 end loop;
163
164 ---- Added by Vpanwar --- Code for new accounting API uptake
165 l_trx_header_tbl:= 'OKL_TRX_AR_INVOICES_B';
166 l_trx_header_id := p_tai_id;
167 --Call accounting with new signature
168
169 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
170 p_api_version => p_api_version,
171 p_init_msg_list => p_init_msg_list,
172 x_return_status => x_return_status,
173 x_msg_count => x_msg_count,
174 x_msg_data => x_msg_data,
175 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
176 p_dist_info_tbl => l_dist_info_tbl,
177 p_ctxt_val_tbl => l_ctxt_tbl,
178 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
179 x_template_tbl => l_template_out_tbl,
180 x_amount_tbl => l_amount_out_tbl,
181 p_trx_header_id => l_trx_header_id,
182 p_trx_header_table => l_trx_header_tbl);
183
184 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
185 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
186 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
187 RAISE OKL_API.G_EXCEPTION_ERROR;
188 END IF;
189
190 --debug_proc('Vipp 333...l_trx_header_id '||l_trx_header_id );
191 ---- End Added by Vpanwar --- Code for new accounting API uptake
192 /*** End API body ******************************************************/
193
194 -- Get message count and if count is 1, get message info
195 FND_MSG_PUB.Count_And_Get
196 (p_count => x_msg_count,
197 p_data => x_msg_data);
198
199 EXCEPTION
200 WHEN OKL_API.G_EXCEPTION_ERROR THEN
201 ROLLBACK TO CREATE_ACCOUNTING_DIST;
202 x_return_status := OKL_API.G_RET_STS_ERROR;
203 FND_MSG_PUB.Count_And_Get
204 (p_count => x_msg_count,
205 p_data => x_msg_data);
206
207 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
208 ROLLBACK TO CREATE_ACCOUNTING_DIST;
209 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
210 FND_MSG_PUB.Count_And_Get
211 (p_count => x_msg_count,
212 p_data => x_msg_data);
213
214 WHEN OTHERS THEN
215 ROLLBACK TO CREATE_ACCOUNTING_DIST;
216 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
217 OKL_API.Set_Message(p_app_name => G_APP_NAME,
218 p_msg_name => G_UNEXPECTED_ERROR,
219 p_token1 => G_SQLCODE_TOKEN,
220 p_token1_value => SQLCODE,
221 p_token2 => G_SQLERRM_TOKEN,
222 p_token2_value => SQLERRM);
223 FND_MSG_PUB.Count_And_Get
224 (p_count => x_msg_count,
225 p_data => x_msg_data);
226
227 END create_accounting_dist;
228
229
230
231
232 ----------------------------------------------------------------------------------
233 -- Start of comments
234 --
235 -- Procedure Name : additional_tai_attr
236 -- Description : Internal procedure to add additional columns for
237 -- okl_trx_ar_invoices_b
238 -- Business Rules :
239 -- Parameters :
240 --
241 -- Version : 1.0
242 -- End of comments
243 ----------------------------------------------------------------------------------
244 PROCEDURE additional_tai_attr(
245 p_api_version IN NUMBER
246 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
247 ,x_return_status OUT NOCOPY VARCHAR2
248 ,x_msg_count OUT NOCOPY NUMBER
249 ,x_msg_data OUT NOCOPY VARCHAR2
250 ,p_taiv_rec IN taiv_rec_type
251 ,x_taiv_rec OUT NOCOPY taiv_rec_type
252 )
253 is
254 l_api_name CONSTANT VARCHAR2(30) := 'additional_tai_attr';
255 l_api_version CONSTANT NUMBER := 1.0;
256 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
257
258 --START: cklee 3/20/07
259 l_legal_entity_id okl_trx_ar_invoices_b.legal_entity_id%TYPE; -- for LE Uptake project 08-11-2006
260
261 l_jtot_object1_code okc_rules_b.jtot_object1_code%TYPE;
262 l_jtot_object2_code okc_rules_b.jtot_object2_code%TYPE;
263 l_object1_id1 okc_rules_b.object1_id1%TYPE;
264 l_object1_id2 okc_rules_b.object1_id2%TYPE;
265
266 CURSOR rule_code_csr(p_khr_id NUMBER, p_rule_category VARCHAR2) IS
267 SELECT jtot_object1_code,
268 object1_id1,
269 object1_id2
270 FROM okc_rules_b
271 WHERE rgp_id =
272 (SELECT id
273 FROM okc_rule_groups_b
274 WHERE dnz_chr_id = p_khr_id
275 AND cle_id IS NULL
276 AND rgd_code = 'LABILL')
277 AND rule_information_category = p_rule_category;
278
279 l_cust_bank_acct okx_rcpt_method_accounts_v.bank_account_id%TYPE;
280
281 CURSOR cust_trx_type_csr(p_sob_id NUMBER, p_org_id NUMBER) IS
282 SELECT id1
283 FROM okx_cust_trx_types_v
284 WHERE name = 'Invoice-OKL'
285 AND set_of_books_id = p_sob_id
286 AND org_id = p_org_id;
287
288 CURSOR cm_trx_type_csr(p_sob_id NUMBER, p_org_id NUMBER) IS
289 SELECT id1
290 FROM okx_cust_trx_types_v
291 WHERE name = 'Credit Memo-OKL'
292 AND set_of_books_id = p_sob_id
293 AND org_id = p_org_id;
294
295 CURSOR org_id_csr(p_khr_id NUMBER) IS
296 SELECT authoring_org_id
297 FROM okc_k_headers_b
298 WHERE id = p_khr_id;
299
300 --gkhuntet added for Manual Invoices 06-07-2007 start
301 --Cursor to get TRY_ID for the BILLING.
302 CURSOR in_okx_trx_type_csr IS
303 SELECT ID
304 FROM OKL_TRX_TYPES_V
305 WHERE AEP_CODE = 'BILLING';
306
307 --Cursor to get TRY_ID for the CREDIT_MEMO.
308 CURSOR cm_okx_trx_type_csr IS
309 SELECT ID
310 FROM OKL_TRX_TYPES_V
311 WHERE AEP_CODE = 'CREDIT_MEMO';
312 --gkhuntet added for Manual Invoices 06-07-2007 end
313
314 --added for rules migration
315 CURSOR cur_address_billto(p_contract_id IN VARCHAR2) IS
316 SELECT a.cust_acct_id cust_account_id,
317 b.cust_acct_site_id,
318 c.standard_terms payment_term_id
319 FROM okc_k_headers_v a,
320 okx_cust_site_uses_v b,
321 hz_customer_profiles c
322 WHERE a.id = p_contract_id
323 AND a.bill_to_site_use_id = b.id1
324 AND a.bill_to_site_use_id = c.site_use_id(+);
325
326 billto_rec cur_address_billto % ROWTYPE;
327
328 CURSOR rcpt_mthd_csr(p_cust_rct_mthd NUMBER) IS
329 SELECT c.receipt_method_id
330 FROM ra_cust_receipt_methods c
331 WHERE c.cust_receipt_method_id = p_cust_rct_mthd;
332
333 -- For bank accounts
334 CURSOR bank_acct_csr(p_id NUMBER) IS
335 SELECT bank_account_id
336 FROM okx_rcpt_method_accounts_v
337 WHERE id1 = p_id;
338
339 -- Default term Id
340 cursor std_terms_csr IS
341 SELECT B.TERM_ID
342 FROM RA_TERMS_TL T, RA_TERMS_B B
343 where T.name = 'IMMEDIATE' and T.LANGUAGE = userenv('LANG')
344 and B.TERM_ID = T.TERM_ID;
345
346 l_term_id okl_trx_ar_invoices_b.irt_id%type; -- cklee 3/20/07
347
348 CURSOR rcpt_method_csr(p_rct_method_id NUMBER) IS
349 SELECT c.creation_method_code
350 FROM ar_receipt_methods m,
351 ar_receipt_classes c
352 WHERE m.receipt_class_id = c.receipt_class_id
353 AND m.receipt_method_id = p_rct_method_id;
354
355 l_rct_method_code ar_receipt_classes.creation_method_code%TYPE;
356
357 --Start code added by pgomes on 20-NOV-2002
358 SUBTYPE khr_id_type IS okl_k_headers_v.khr_id%TYPE;
359 l_khr_id khr_id_type;
360 l_currency_code okl_trx_ar_invoices_b.currency_code%TYPE;
361 l_currency_conversion_type okl_trx_ar_invoices_b.currency_conversion_type%TYPE;
362 l_currency_conversion_rate okl_trx_ar_invoices_b.currency_conversion_rate%TYPE;
363 l_currency_conversion_date okl_trx_ar_invoices_b.currency_conversion_date%TYPE;
364
365 --Get currency conversion attributes for a contract
366 CURSOR l_curr_conv_csr(cp_khr_id IN khr_id_type) IS
367 SELECT currency_code,
368 currency_conversion_type,
369 currency_conversion_rate,
370 currency_conversion_date
371 FROM okl_k_headers_full_v
372 WHERE id = cp_khr_id;
373
374 --End code added by pgomes on 20-NOV-2002
375
376 -- -------------------------------------------
377 -- To support new fields in XSI and XLS
378 -- Added on 21-MAR-2005
379 -- -------------------------------------------
380 -- rseela BUG# 4733028 Start: fetching review invoice flag
381 CURSOR inv_frmt_csr(cp_khr_id IN NUMBER) IS
382 SELECT inf.id,
383 rul.rule_information4 review_invoice_yn
384 FROM okc_rule_groups_v rgp,
385 okc_rules_v rul,
386 okl_invoice_formats_v inf
387 WHERE rgp.dnz_chr_id = cp_khr_id
388 AND rgp.chr_id = rgp.dnz_chr_id
389 AND rgp.id = rul.rgp_id
390 AND rgp.cle_id IS NULL
391 AND rgp.rgd_code = 'LABILL'
392 AND rul.rule_information_category = 'LAINVD'
393 AND rul.rule_information1 = inf.name;
394
395 l_inf_id okl_invoice_formats_v.id%TYPE;
396
397 -- -------------------------------------------
398 -- To support private label transfers to
399 -- AR. Bug 4525643
400 -- -------------------------------------------
401 CURSOR pvt_label_csr(cp_khr_id IN NUMBER) IS
402 SELECT rule_information1 private_label
403 FROM okc_rule_groups_b a,
404 okc_rules_b b
405 WHERE a.dnz_chr_id = cp_khr_id
406 AND a.rgd_code = 'LALABL'
407 AND a.id = b.rgp_id
408 AND b.rule_information_category = 'LALOGO';
409
410 l_private_label okc_rules_b.rule_information1%TYPE;
411
412 --END: cklee 3/20/07
413
414 begin
415 -- Set API savepoint
416 SAVEPOINT additional_tai_attr;
417 IF (G_DEBUG_ENABLED = 'Y') THEN
418 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
419 END IF;
420 --Print Input Variables
421 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
422 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_taiv_rec.id :'||p_taiv_rec.id);
423 END IF;
424 -- Check for call compatibility
425 IF (NOT FND_API.Compatible_API_Call (l_api_version,
426 p_api_version,
427 l_api_name,
428 G_PKG_NAME ))
429 THEN
430 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
431 END IF;
432
433 -- Initialize message list if requested
434 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
435 FND_MSG_PUB.initialize;
436 END IF;
437
438 -- Initialize API status to success
439 x_return_status := OKL_API.G_RET_STS_SUCCESS;
440
441
442 /*** Begin API body ****************************************************/
443 -- assign all passed in attributes from IN to OUT record
444 x_taiv_rec := p_taiv_rec;
445
446 l_khr_id := p_taiv_rec.khr_id;
447 IF l_khr_id IS NOT NULL THEN
448 -- Changed if condition for bug 4155476
449 --added by pgomes 11/20/2002 (multi-currency er)
450
451 --Start code added by pgomes on 11/21/2002
452 l_currency_code := NULL;
453 l_currency_conversion_type := NULL;
454 l_currency_conversion_rate := NULL;
455 l_currency_conversion_date := NULL;
456
457 FOR cur IN l_curr_conv_csr(l_khr_id)
458 LOOP
459 l_currency_code := cur.currency_code;
460 l_currency_conversion_type := cur.currency_conversion_type;
461 l_currency_conversion_rate := cur.currency_conversion_rate;
462 l_currency_conversion_date := cur.currency_conversion_date;
463 END LOOP;
464
465 --End code added by pgomes on 11/21/2002
466
467 -- Start; Bug 4525643; stmathew
468 -- Private Label
469 l_private_label := NULL;
470
471 OPEN pvt_label_csr(l_khr_id);
472 FETCH pvt_label_csr
473 INTO l_private_label;
474 CLOSE pvt_label_csr;
475 x_taiv_rec.private_label := l_private_label;
476 -- End; Bug 4525643; stmathew
477
478 l_jtot_object1_code := NULL;
479 l_object1_id1 := NULL;
480 l_object1_id2 := NULL;
481 l_jtot_object2_code := NULL;
482
483 -- for LE Uptake project 08-11-2006
484 IF (p_taiv_rec.legal_entity_id IS NULL OR (p_taiv_rec.legal_entity_id = Okl_Api.G_MISS_NUM)) THEN
485 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(l_khr_id);
486 ELSE
487 l_legal_entity_id := p_taiv_rec.legal_entity_id;
488 END IF;
489 x_taiv_rec.legal_entity_id := l_legal_entity_id;
490
491 -- IF l_khr_id IS NOT NULL THEN
492 -- -- Changed if condition for bug 4155476
493
494 IF(p_taiv_rec.irm_id IS NULL) THEN
495 --AND ln_dtls_rec.IXX_ID IS NULL )THEN
496
497 OPEN rule_code_csr(l_khr_id, 'LAPMTH');
498 FETCH rule_code_csr
499 INTO l_jtot_object1_code,
500 l_object1_id1,
501 l_object1_id2;
502 CLOSE rule_code_csr;
503
504 IF l_object1_id2 <> '#' THEN
505 x_taiv_rec.irm_id := l_object1_id2;
506 ELSE
507 -- This cursor needs to be removed when the view changes to
508 -- include id2
509
510 OPEN rcpt_mthd_csr(l_object1_id1);
511 FETCH rcpt_mthd_csr
512 INTO x_taiv_rec.irm_id;
513 CLOSE rcpt_mthd_csr;
514 END IF;
515
516 ELSE
517 x_taiv_rec.irm_id := p_taiv_rec.irm_id;
518 END IF;
519
520 -- Null out local variables
521 l_jtot_object1_code := NULL;
522 l_object1_id1 := NULL;
523 l_jtot_object2_code := NULL;
524
525 billto_rec.cust_account_id := NULL;
526 billto_rec.cust_acct_site_id := NULL;
527 billto_rec.payment_term_id := NULL;
528
529 OPEN cur_address_billto(l_khr_id);
530 FETCH cur_address_billto
531 INTO billto_rec;
532 CLOSE cur_address_billto;
533
534 -- rmunjulu R12 Fixes -- commented
535 -- x_taiv_rec.ixx_id := NVL(p_taiv_rec.ixx_id, billto_rec.cust_account_id);
536 -- x_taiv_rec.ibt_id := NVL(p_taiv_rec.ibt_id, billto_rec.cust_acct_site_id);
537
538 -- rmunjulu R12 Fixes -- changed to check for g_miss
539 IF (p_taiv_rec.ixx_id IS NULL OR (p_taiv_rec.ixx_id = Okl_Api.G_MISS_NUM)) THEN
540 x_taiv_rec.ixx_id := billto_rec.cust_account_id;
541 ELSE
542 x_taiv_rec.ixx_id := p_taiv_rec.ixx_id;
543 END IF;
544
545 IF (p_taiv_rec.ibt_id IS NULL OR (p_taiv_rec.ibt_id = Okl_Api.G_MISS_NUM)) THEN
546 x_taiv_rec.ibt_id := billto_rec.cust_acct_site_id;
547 ELSE
548 x_taiv_rec.ibt_id := p_taiv_rec.ibt_id;
549 END IF;
550
551 OPEN std_terms_csr;
552 FETCH std_terms_csr
553 INTO l_term_id;
554 CLOSE std_terms_csr;
555
556 -- cklee: set when irt_id is null
557 --x_taiv_rec.irt_id := NVL(p_taiv_rec.irt_id, l_term_id); -- 6140771
558 if p_taiv_rec.irt_id is null or p_taiv_rec.irt_id = okl_api.g_miss_num
559 then
560 x_taiv_rec.irt_id := l_term_id;
561 else
562 x_taiv_rec.irt_id := p_taiv_rec.irt_id;
563 end if;
564
565 IF (p_taiv_rec.org_id IS NULL OR p_taiv_rec.org_id=OKL_API.G_MISS_NUM) THEN
566
567 OPEN org_id_csr(l_khr_id);
568 FETCH org_id_csr
569 INTO x_taiv_rec.org_id;
570 CLOSE org_id_csr;
571 ELSE
572 x_taiv_rec.org_id := p_taiv_rec.org_id;
573 --TAI
574 END IF;
575
576 -- To resolve the bank account for the customer
577 -- If receipt method is manual do not supply customer bank account
578 -- Id. This is required for Auto Invoice Validation
579
580 -- Null out variable
581 l_rct_method_code := NULL;
582
583 OPEN rcpt_method_csr(x_taiv_rec.irm_id);
584 FETCH rcpt_method_csr
585 INTO l_rct_method_code;
586 CLOSE rcpt_method_csr;
587
588 --Null out variables
589 l_jtot_object1_code := NULL;
590 l_object1_id1 := NULL;
591 l_object1_id2 := NULL;
592 l_cust_bank_acct := NULL;
593
594 IF(l_rct_method_code <> 'MANUAL') THEN
595
596 OPEN rule_code_csr(l_khr_id, 'LABACC');
597 FETCH rule_code_csr
598 INTO l_jtot_object1_code,
599 l_object1_id1,
600 l_object1_id2;
601 CLOSE rule_code_csr;
602
603 OPEN bank_acct_csr(l_object1_id1);
604 FETCH bank_acct_csr
605 INTO l_cust_bank_acct;
606 CLOSE bank_acct_csr;
607
608 x_taiv_rec.customer_bank_account_id := l_cust_bank_acct;
609 END IF;
610
611 -- New fields added on 21-MAR-2005
612 l_inf_id := NULL;
613 -- rseela BUG# 4733028 Start: populating review invoice flag
614
615 OPEN inv_frmt_csr(l_khr_id);
616 FETCH inv_frmt_csr
617 INTO x_taiv_rec.inf_id,
618 x_taiv_rec.invoice_pull_yn;
619 CLOSE inv_frmt_csr;
620
621 --pgomes 11/22/2002 changed below line to output l_cust_bank_acct instead of l_xsiv_rec.customer_bank_account_id
622 /*** Move the following valiadtion rules to validate_tai_values
623 ELSE
624 -- Else for contract_id
625
626 IF p_ie_tbl1(k).ixx_id IS NULL THEN
627 --d*bms_output.put_line ('IXX_ID must be populated WHEN the contract header IS NULL!');
628 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IXX_ID must be populated WHEN the contract header IS NULL!');
629 ELSE
630 xsi_tbl(l_xsi_cnt).customer_id := p_ie_tbl1(k).ixx_id;
631 END IF;
632
633 IF p_ie_tbl1(k).irm_id IS NULL THEN
634 -- d*bms_output.put_line ('IRM_ID must be populated WHEN the contract header IS NULL!');
635 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IRM_ID must be populated WHEN the contract header IS NULL');
636 ELSE
637 xsi_tbl(l_xsi_cnt).receipt_method_id := p_ie_tbl1(k).irm_id;
638 END IF;
639
640 IF p_ie_tbl1(k).irt_id IS NULL THEN
641 -- d*bms_output.put_line ('IRT_ID must be populated WHEN the contract header IS NULL!');
642 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IRT_ID must be populated WHEN the contract header IS NULL');
643 ELSE
644 xsi_tbl(l_xsi_cnt).term_id := p_ie_tbl1(k).irt_id;
645 END IF;
646
647 IF p_ie_tbl1(k).ibt_id IS NULL THEN
648 --d*bms_output.put_line ('IBT_ID must be populated WHEN the contract header IS NULL!');
649 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IBT_ID must be populated WHEN the contract header IS NULL');
650 ELSE
651 xsi_tbl(l_xsi_cnt).customer_address_id := p_ie_tbl1(k).ibt_id;
652 END IF;
653
654 IF p_ie_tbl1(k).org_id IS NULL THEN
655 --d*bms_output.put_line ('ORG_ID must be populated WHEN the contract header IS NULL!');
656 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'ORG_ID must be populated WHEN the contract header IS NULL');
657 ELSE
658 --l_xsiv_rec.ORG_ID := ln_dtls_rec.ORG_ID; --TAI
659 xsi_tbl(l_xsi_cnt).org_id := NULL;
660 END IF;
661 -- for LE Uptake project 08-11-2006
662 IF ( p_ie_tbl1(k).legal_entity_id IS NULL OR (p_ie_tbl1(k).legal_entity_id = Okl_Api.G_MISS_NUM)) THEN
663 --d*bms_output.put_line ('LEGAL_ENTITY_ID must be populated WHEN the contract header IS NULL!');
664 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'LEGAL_ENTITY_ID must be populated WHEN the contract header IS NULL');
665 ELSE
666 xsi_tbl(l_xsi_cnt).legal_entity_id := p_ie_tbl1(k).legal_entity_id;
667 END IF;
668 -- for LE Uptake project 08-11-2006
669 ***/
670 END IF; -- IF l_khr_id IS NOT NULL THEN
671
672 --How to get the set_of_books_id ?
673
674 IF (p_taiv_rec.set_of_books_id IS NULL OR p_taiv_rec.set_of_books_id = OKL_API.G_MISS_NUM) THEN
675 x_taiv_rec.set_of_books_id := Okl_Accounting_Util.get_set_of_books_id;
676 ELSE
677 x_taiv_rec.set_of_books_id := p_taiv_rec.set_of_books_id;
678 --TAI
679 END IF;
680
681 --Start code added by pgomes on 20-NOV-2002
682 --Check for currency code
683
684 IF (p_taiv_rec.currency_code IS NULL OR p_taiv_rec.currency_code=OKL_API.G_MISS_CHAR) THEN
685 x_taiv_rec.currency_code := l_currency_code;
686 ELSE
687 x_taiv_rec.currency_code := p_taiv_rec.currency_code;
688 END IF;
689
690 --Check for currency conversion type
691
692 IF (p_taiv_rec.currency_conversion_type IS NULL OR p_taiv_rec.currency_conversion_type = OKL_API.G_MISS_CHAR) THEN
693 x_taiv_rec.currency_conversion_type := l_currency_conversion_type;
694 ELSE
695 x_taiv_rec.currency_conversion_type := p_taiv_rec.currency_conversion_type;
696 END IF;
697
698 --Check for currency conversion rate
699
700 -- 6140771
701 IF(x_taiv_rec.currency_conversion_type = 'User') THEN
702
703 IF(x_taiv_rec.currency_code = Okl_Accounting_Util.get_func_curr_code) THEN
704 x_taiv_rec.currency_conversion_rate := 1;
705 ELSE
706
707 IF (p_taiv_rec.currency_conversion_rate IS NULL OR p_taiv_rec.currency_conversion_rate=OKL_API.G_MISS_NUM) THEN
708 x_taiv_rec.currency_conversion_rate := l_currency_conversion_rate;
709 ELSE
710 x_taiv_rec.currency_conversion_rate := p_taiv_rec.currency_conversion_rate;
711 END IF;
712
713 END IF;
714
715 ELSE
716 x_taiv_rec.currency_conversion_rate := NULL;
717 END IF;
718
719 --Check for currency conversion date
720
721 IF (p_taiv_rec.currency_conversion_date IS NULL OR p_taiv_rec.currency_conversion_date = OKL_API.G_MISS_DATE) THEN
722 x_taiv_rec.currency_conversion_date := l_currency_conversion_date;
723 ELSE
724 x_taiv_rec.currency_conversion_date := p_taiv_rec.currency_conversion_date;
725 END IF;
726
727 --End code added by pgomes on 20-NOV-2002
728
729 --Start code added by pgomes on 06-JAN-2003
730
731 -- 6140771
732 /* ankushar 16-Apr-2008 Bug# 6237730, Added condition for defaulting currency rate, date and type
733 start code changes
734 */
735 IF(x_taiv_rec.currency_conversion_type IS NULL OR x_taiv_rec.currency_conversion_type = OKL_API.G_MISS_CHAR
736 OR x_taiv_rec.currency_conversion_date IS NULL OR x_taiv_rec.currency_conversion_date = OKL_API.G_MISS_DATE
737 OR x_taiv_rec.currency_conversion_rate IS NULL OR x_taiv_rec.currency_conversion_rate = OKL_API.G_MISS_NUM) THEN
738 /* ankushar End Changes */
739
740 x_taiv_rec.currency_conversion_type := 'User';
741 x_taiv_rec.currency_conversion_rate := 1;
742 x_taiv_rec.currency_conversion_date := SYSDATE;
743 END IF;
744
745 --End code added by pgomes on 06-JAN-2003
746 -- Populate Customer TRX-TYPE ID From AR setup
747
748 IF p_taiv_rec.amount < 0 THEN
749 x_taiv_rec.irt_id := NULL;
750
751 --OPEN cm_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, xsi_tbl(l_xsi_cnt).org_id);
752 --xsi_tbl(l_xsi_cnt).org_id was null out, so use p_ie_tbl1(k).org_id
753 OPEN cm_trx_type_csr(x_taiv_rec.set_of_books_id, x_taiv_rec.org_id);
754 FETCH cm_trx_type_csr
755 INTO x_taiv_rec.cust_trx_type_id;
756 CLOSE cm_trx_type_csr;
757 ELSE
758 /* ankushar 25-Oct-2007 Bug# 6501426, Transaction Type corrected for Investor
759 start code changes
760 */
761 --Check if Investor-Stake Billing, then do not populate l_cust_trx_id with 'Invoice-OKL', since Investor API is already populating
762 --this value with 'Investor-OKL' as the transaction type value.
763 IF p_taiv_rec.okl_source_billing_trx <> 'INVESTOR_STAKE' THEN
764 --OPEN cust_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, xsi_tbl(l_xsi_cnt).org_id);
765 OPEN cust_trx_type_csr(x_taiv_rec.set_of_books_id, x_taiv_rec.org_id);
766 FETCH cust_trx_type_csr
767 INTO x_taiv_rec.cust_trx_type_id;
768 CLOSE cust_trx_type_csr;
769 END IF;
770 /* ankushar 25-Oct-2007 Bug# 6501426
771 End Changes
772 */
773 END IF;
774
775 --gkhuntet added for Manual Invoices 06-07-2007 start
776 IF p_taiv_rec.okl_source_billing_trx = G_MANUAL THEN
777 IF p_taiv_rec.amount < 0 THEN --TRY_ID for the CREDIT_MEMO.
778 OPEN cm_okx_trx_type_csr;
779 FETCH cm_okx_trx_type_csr
780 INTO x_taiv_rec.try_id;
781 CLOSE cm_okx_trx_type_csr;
782 ELSE --TRY_ID for the BILLING.
783 OPEN in_okx_trx_type_csr;
784 FETCH in_okx_trx_type_csr
785 INTO x_taiv_rec.try_id;
786 CLOSE in_okx_trx_type_csr;
787 END IF;
788 END IF;
789 --gkhuntet added for Manual Invoices 06-07-2007 end
790
791 -- Set Tax exempt flag to Standard
792 x_taiv_rec.tax_exempt_flag := 'S';
793 x_taiv_rec.tax_exempt_reason_code := NULL;
794
795 --start: | 30-Mar-2007 cklee -- validate taiv_rec.trx_status_code and default to |
796 --| 'SUBMITTED' |
797 IF p_taiv_rec.trx_status_code IS NULL or p_taiv_rec.trx_status_code = okl_api.g_miss_char
798 THEN
799 x_taiv_rec.trx_status_code := G_SUBMITTED;
800 END IF;
801 --end: | 30-Mar-2007 cklee -- validate taiv_rec.trx_status_code and default to |
802 --| 'SUBMITTED' |
803
804 /*** End API body ******************************************************/
805
806 -- Get message count and if count is 1, get message info
807 FND_MSG_PUB.Count_And_Get
808 (p_count => x_msg_count,
809 p_data => x_msg_data);
810
811 EXCEPTION
812 WHEN OKL_API.G_EXCEPTION_ERROR THEN
813 ROLLBACK TO additional_tai_attr;
814 x_return_status := OKL_API.G_RET_STS_ERROR;
815 FND_MSG_PUB.Count_And_Get
816 (p_count => x_msg_count,
817 p_data => x_msg_data);
818
819 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
820 ROLLBACK TO additional_tai_attr;
821 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
822 FND_MSG_PUB.Count_And_Get
823 (p_count => x_msg_count,
824 p_data => x_msg_data);
825
826 WHEN OTHERS THEN
827 ROLLBACK TO additional_tai_attr;
828 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
829 OKL_API.Set_Message(p_app_name => G_APP_NAME,
830 p_msg_name => G_UNEXPECTED_ERROR,
831 p_token1 => G_SQLCODE_TOKEN,
832 p_token1_value => SQLCODE,
833 p_token2 => G_SQLERRM_TOKEN,
834 p_token2_value => SQLERRM);
835 FND_MSG_PUB.Count_And_Get
836 (p_count => x_msg_count,
837 p_data => x_msg_data);
838
839 end additional_tai_attr;
840
841 ----------------------------------------------------------------------------------
842 -- Start of comments
843 --
844 -- Procedure Name : additional_txl_attr
845 -- Description : Internal procedure to add additional columns for
846 -- OKL_TXL_AR_INV_LNS_B
847 -- Business Rules :
848 -- Parameters :
849 --
850 -- Version : 1.0
851 -- End of comments
852 ----------------------------------------------------------------------------------
853 PROCEDURE additional_til_attr(
854 p_api_version IN NUMBER
855 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
856 ,x_return_status OUT NOCOPY VARCHAR2
857 ,x_msg_count OUT NOCOPY NUMBER
858 ,x_msg_data OUT NOCOPY VARCHAR2
859 ,p_tilv_rec IN tilv_rec_type
860 ,x_tilv_rec OUT NOCOPY tilv_rec_type
861 )
862 is
863 l_api_name CONSTANT VARCHAR2(30) := 'additional_til_attr';
864 l_api_version CONSTANT NUMBER := 1.0;
865 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
866 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
867
868
869 begin
870 -- Set API savepoint
871 SAVEPOINT additional_til_attr;
872 IF (G_DEBUG_ENABLED = 'Y') THEN
873 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
874 END IF;
875 --Print Input Variables
876 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
877 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_tilv_rec.id :'||p_tilv_rec.id);
878 END IF;
879 -- Check for call compatibility
880 IF (NOT FND_API.Compatible_API_Call (l_api_version,
881 p_api_version,
882 l_api_name,
883 G_PKG_NAME ))
884 THEN
885 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
886 END IF;
887
888 -- Initialize message list if requested
889 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
890 FND_MSG_PUB.initialize;
891 END IF;
892
893 -- Initialize API status to success
894 x_return_status := OKL_API.G_RET_STS_SUCCESS;
895
896
897 /*** Begin API body ****************************************************/
898 -- assign all passed in attributes from IN to OUT record
899 x_tilv_rec := p_tilv_rec;
900
901 -- Copy the following code from okl_stream_billing_pvt
902 -- rmunjulu R12 Fixes -- Populate ADDITIONAL columns in til_tbl -- start
903 -- apaul: Comment out hard coding isl_id
904 --x_tilv_rec.ISL_ID := 1;
905 x_tilv_rec.inv_receiv_line_code := l_line_code;
906 x_tilv_rec.QUANTITY := 1;
907 -- rmunjulu R12 Fixes -- Populate ADDITIONAL columns in til_tbl -- end
908
909
910
911 /*** End API body ******************************************************/
912
913 -- Get message count and if count is 1, get message info
914 FND_MSG_PUB.Count_And_Get
915 (p_count => x_msg_count,
916 p_data => x_msg_data);
917
918 EXCEPTION
919 WHEN OKL_API.G_EXCEPTION_ERROR THEN
920 ROLLBACK TO additional_til_attr;
921 x_return_status := OKL_API.G_RET_STS_ERROR;
922 FND_MSG_PUB.Count_And_Get
923 (p_count => x_msg_count,
924 p_data => x_msg_data);
925
926 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
927 ROLLBACK TO additional_til_attr;
928 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
929 FND_MSG_PUB.Count_And_Get
930 (p_count => x_msg_count,
931 p_data => x_msg_data);
932
933 WHEN OTHERS THEN
934 ROLLBACK TO additional_til_attr;
935 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
936 OKL_API.Set_Message(p_app_name => G_APP_NAME,
937 p_msg_name => G_UNEXPECTED_ERROR,
938 p_token1 => G_SQLCODE_TOKEN,
939 p_token1_value => SQLCODE,
940 p_token2 => G_SQLERRM_TOKEN,
941 p_token2_value => SQLERRM);
942 FND_MSG_PUB.Count_And_Get
943 (p_count => x_msg_count,
944 p_data => x_msg_data);
945
946 end additional_til_attr;
947
948 ----------------------------------------------------------------------------------
949 -- Start of comments
950 --
951 -- Procedure Name : additional_tld_attr
952 -- Description : Internal procedure to add additional columns for
953 -- OKL_TXD_AR_LN_DTLS_B
954 -- Business Rules :
955 -- Parameters :
956 --
957 -- Version : 1.0
958 -- End of comments
959 ----------------------------------------------------------------------------------
960 PROCEDURE additional_tld_attr(
961 p_api_version IN NUMBER
962 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
963 ,x_return_status OUT NOCOPY VARCHAR2
964 ,x_msg_count OUT NOCOPY NUMBER
965 ,x_msg_data OUT NOCOPY VARCHAR2
966 ,p_tldv_rec IN tldv_rec_type
967 ,x_tldv_rec OUT NOCOPY tldv_rec_type
968 )
969 is
970 l_api_name CONSTANT VARCHAR2(30) := 'additional_tld_attr';
971 l_api_version CONSTANT NUMBER := 1.0;
972 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
973 /*
974 l_recv_inv_id NUMBER;
975 CURSOR reverse_csr1(p_tld_id NUMBER) IS
976 SELECT receivables_invoice_id
977 FROM okl_txd_ar_ln_dtls_v
978 WHERE id = p_tld_id;
979
980 CURSOR reverse_csr2(p_til_id NUMBER) IS
981 SELECT receivables_invoice_id
982 FROM okl_txl_ar_inv_lns_v
983 WHERE id = p_til_id;
984
985
986 -- Get currency attributes
987 CURSOR l_curr_csr(cp_currency_code VARCHAR2) IS
988 SELECT c.minimum_accountable_unit,
989 c.PRECISION
990 FROM fnd_currencies c
991 WHERE c.currency_code = cp_currency_code;
992 */
993 -- Get currency attributes
994 CURSOR l_curr_csr(p_khr_id number) IS
995 SELECT c.minimum_accountable_unit,
996 c.PRECISION
997 FROM fnd_currencies c,
998 okl_trx_ar_invoices_b b
999 WHERE c.currency_code = b.currency_code
1000 AND b.khr_id = p_khr_id;
1001
1002
1003 l_min_acct_unit fnd_currencies.minimum_accountable_unit%TYPE;
1004 l_precision fnd_currencies.PRECISION %TYPE;
1005
1006 l_rounded_amount OKL_TXD_AR_LN_DTLS_B.amount%TYPE;
1007
1008 -- to get inventory_org_id bug 4890024 begin
1009 CURSOR inv_org_id_csr(p_contract_id NUMBER) IS
1010 SELECT NVL(inv_organization_id, -99)
1011 FROM okc_k_headers_b
1012 WHERE id = p_contract_id;
1013
1014 begin
1015 -- Set API savepoint
1016 SAVEPOINT additional_tld_attr;
1017 IF (G_DEBUG_ENABLED = 'Y') THEN
1018 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1019 END IF;
1020 --Print Input Variables
1021 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1022 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_tldv_rec.id :'||p_tldv_rec.id);
1023 END IF;
1024 -- Check for call compatibility
1025 IF (NOT FND_API.Compatible_API_Call (l_api_version,
1026 p_api_version,
1027 l_api_name,
1028 G_PKG_NAME ))
1029 THEN
1030 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1031 END IF;
1032
1033 -- Initialize message list if requested
1034 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1035 FND_MSG_PUB.initialize;
1036 END IF;
1037
1038 -- Initialize API status to success
1039 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1040
1041
1042 /*** Begin API body ****************************************************/
1043 -- assign all passed in attributes from IN to OUT record
1044 x_tldv_rec := p_tldv_rec;
1045 /* For R12, okl_arfetch_pub is absolete, so the following logic won't work
1046 since the receivable_invoice_id is null
1047 --For Credit Memo Processing
1048 IF p_tldv_rec.tld_id_reverses IS NOT NULL THEN
1049 -- Null out variables
1050 l_recv_inv_id := NULL;
1051
1052 OPEN reverse_csr1(p_tldv_rec.tld_id_reverses);
1053 FETCH reverse_csr1
1054 INTO l_recv_inv_id;
1055 CLOSE reverse_csr1;
1056 x_tldv_rec.reference_line_id := l_recv_inv_id;
1057 ELSE
1058 x_tldv_rec.reference_line_id := NULL;
1059 END IF;
1060
1061 x_tldv_rec.receivables_invoice_id := NULL;
1062 -- Populated later by fetch
1063 */
1064
1065 IF(p_tldv_rec.inventory_org_id IS NULL OR p_tldv_rec.inventory_org_id=OKL_API.G_MISS_NUM) THEN
1066
1067 OPEN inv_org_id_csr(p_tldv_rec.khr_id);
1068 FETCH inv_org_id_csr
1069 INTO x_tldv_rec.inventory_org_id;
1070 CLOSE inv_org_id_csr;
1071 ELSE
1072 x_tldv_rec.inventory_org_id := p_tldv_rec.inventory_org_id;
1073 END IF;
1074
1075 -- Bug 4890024 end
1076
1077 -------- Rounded Amount --------------
1078 l_rounded_amount := NULL;
1079 l_min_acct_unit := NULL;
1080 l_precision := NULL;
1081
1082 OPEN l_curr_csr(p_tldv_rec.khr_id);
1083 FETCH l_curr_csr
1084 INTO l_min_acct_unit,
1085 l_precision;
1086 CLOSE l_curr_csr;
1087
1088 IF(NVL(l_min_acct_unit, 0) <> 0) THEN
1089 -- Round the amount to the nearest Min Accountable Unit
1090 l_rounded_amount := ROUND(p_tldv_rec.amount / l_min_acct_unit) * l_min_acct_unit;
1091
1092 ELSE
1093 -- Round the amount to the nearest precision
1094 l_rounded_amount := ROUND(p_tldv_rec.amount, l_precision);
1095 END IF;
1096 -------- Rounded Amount --------------
1097 x_tldv_rec.amount := l_rounded_amount;
1098 --TIL
1099 /*** End API body ******************************************************/
1100
1101 -- Get message count and if count is 1, get message info
1102 FND_MSG_PUB.Count_And_Get
1103 (p_count => x_msg_count,
1104 p_data => x_msg_data);
1105
1106 EXCEPTION
1107 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1108 ROLLBACK TO additional_tld_attr;
1109 x_return_status := OKL_API.G_RET_STS_ERROR;
1110 FND_MSG_PUB.Count_And_Get
1111 (p_count => x_msg_count,
1112 p_data => x_msg_data);
1113
1114 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1115 ROLLBACK TO additional_tld_attr;
1116 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1117 FND_MSG_PUB.Count_And_Get
1118 (p_count => x_msg_count,
1119 p_data => x_msg_data);
1120
1121 WHEN OTHERS THEN
1122 ROLLBACK TO additional_tld_attr;
1123 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1124 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1125 p_msg_name => G_UNEXPECTED_ERROR,
1126 p_token1 => G_SQLCODE_TOKEN,
1127 p_token1_value => SQLCODE,
1128 p_token2 => G_SQLERRM_TOKEN,
1129 p_token2_value => SQLERRM);
1130 FND_MSG_PUB.Count_And_Get
1131 (p_count => x_msg_count,
1132 p_data => x_msg_data);
1133
1134 end additional_tld_attr;
1135
1136
1137 ----------------------------------------------------------------------------------
1138 -- Start of comments
1139 --
1140 -- Procedure Name : validate_billing_usage
1141 -- Description : Internal procedure to validate overall billing API usage
1142 -- Business Rules :
1143 -- Parameters :
1144 --
1145 -- Version : 1.0
1146 -- End of comments
1147 ----------------------------------------------------------------------------------
1148 FUNCTION validate_billing_usage(
1149 p_tilv_tbl IN tilv_tbl_type,
1150 p_tldv_tbl IN tldv_tbl_type
1151 ) RETURN VARCHAR2
1152 IS
1153 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1154 l_til_exist BOOLEAN;
1155 l_tld_exist BOOLEAN;
1156
1157 BEGIN
1158
1159 -- 1) if it's 3 layers, set G_IS_STREAM_BASED_BILLING := TRUE;
1160 -- 2) if it's 2 layers, set G_IS_STREAM_BASED_BILLING := FALSE;
1161 -- 3) if p_tilv_tbl.count = 0, throw error
1162
1163 IF p_tilv_tbl.COUNT > 0 AND p_tldv_tbl.COUNT > 0 THEN
1164
1165 G_IS_STREAM_BASED_BILLING := TRUE;
1166 ELSIF p_tilv_tbl.COUNT > 0 AND p_tldv_tbl.COUNT = 0 THEN
1167 G_IS_STREAM_BASED_BILLING := FALSE;
1168 ELSIF p_tilv_tbl.COUNT = 0 THEN
1169
1170 -- developer note: Replace with a proper message
1171 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1172 p_msg_name => G_REQUIRED_VALUE,
1173 p_token1 => G_COL_NAME_TOKEN,
1174 p_token1_value => 'p_tilv_tbl.STY_ID');
1175
1176 raise G_EXCEPTION_HALT_VALIDATION;
1177 ELSE
1178 null;
1179 END IF;
1180 -- Note: Please refer to the business rules from spec API.
1181
1182 RETURN l_return_status;
1183
1184
1185 EXCEPTION
1186 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1187 l_return_status := OKL_API.G_RET_STS_ERROR;
1188 RETURN l_return_status;
1189 WHEN OTHERS THEN
1190 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1191 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1192 p_msg_name => G_UNEXPECTED_ERROR,
1193 p_token1 => G_SQLCODE_TOKEN,
1194 p_token1_value => SQLCODE,
1195 p_token2 => G_SQLERRM_TOKEN,
1196 p_token2_value => SQLERRM);
1197 RETURN l_return_status;
1198 END;
1199
1200
1201 ----------------------------------------------------------------------------------
1202 -- Start of comments
1203 --
1204 -- Procedure Name : validate_TXL_AR_LINE_NUMBER
1205 -- Description : Internal procedure to validate TXL_AR_LINE_NUMBER usage
1206 -- Business Rules :
1207 -- Parameters :
1208 --
1209 -- Version : 1.0
1210 -- End of comments
1211 ----------------------------------------------------------------------------------
1212 FUNCTION validate_TXL_AR_LINE_NUMBER(
1213 p_tilv_tbl IN tilv_tbl_type,
1214 p_tldv_tbl IN tldv_tbl_type
1215 ) RETURN VARCHAR2
1216 IS
1217 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1218 l_til_loop_cnt NUMBER := 0;
1219 l_til_ln_number NUMBER := 0;
1220 l_current NUMBER := 0;
1221 l_tld_loop_cnt NUMBER := 0;
1222 l_total NUMBER := 0;
1223 BEGIN
1224
1225 -- R2): If TXL_AR_LINE_NUMBER exists in p_tilv_tbl, but doesn't exists in p_tldv_tbl, throw error.
1226 -- R3): If TXL_AR_LINE_NUMBER exists in p_tldv_tbl, but doesn't exists in p_tilv_tbl, throw error.
1227 l_til_loop_cnt := p_tilv_tbl.first;
1228 loop
1229
1230 -- for l_til_loop_cnt in 1 .. p_tilv_tbl.count loop
1231 -- Raise Exception if any of til records does not have TXL_AR_LINE_NUMBER
1232 if p_tilv_tbl(l_til_loop_cnt).TXL_AR_LINE_NUMBER is null OR
1233 p_tilv_tbl(l_til_loop_cnt).TXL_AR_LINE_NUMBER = Okl_Api.G_MISS_NUM
1234 then
1235 raise G_EXCEPTION_HALT_VALIDATION;
1236 end if;
1237 l_til_ln_number := p_tilv_tbl(l_til_loop_cnt).TXL_AR_LINE_NUMBER;
1238 l_current := 0;
1239 l_tld_loop_cnt := p_tldv_tbl.first;
1240 loop
1241 --for l_tld_loop_cnt in 1 .. p_tldv_tbl.count loop
1242 -- Raise Exception if any of tld record does not have txl_ar_ln_number
1243 if p_tldv_tbl(l_tld_loop_cnt).TXL_AR_LINE_NUMBER is null OR
1244 p_tilv_tbl(l_til_loop_cnt).TXL_AR_LINE_NUMBER = Okl_Api.G_MISS_NUM
1245 then
1246 raise G_EXCEPTION_HALT_VALIDATION;
1247 end if;
1248 if (p_tldv_tbl(l_tld_loop_cnt).TXL_AR_LINE_NUMBER = l_til_ln_number) then
1249 l_current := l_current+1;
1250 end if;
1251 EXIT WHEN l_tld_loop_cnt = p_tldv_tbl.LAST;
1252 l_tld_loop_cnt := p_tldv_tbl.NEXT(l_tld_loop_cnt);
1253 end loop;
1254 -- Raise Exception if any of the til records have 0 child tld records
1255 if l_current = 0 then
1256 raise G_EXCEPTION_HALT_VALIDATION;
1257 -- raise error
1258 else
1259 l_total := l_total+l_current;
1260 end if;
1261 EXIT WHEN l_til_loop_cnt = p_tilv_tbl.LAST;
1262 l_til_loop_cnt := p_tilv_tbl.NEXT(l_til_loop_cnt);
1263 end loop;
1264
1265 -- Raise Exception if total children of til records is not equivalent to number of tld records
1266 -- If any tld record does not have corresponding til record
1267 if l_total <> p_tldv_tbl.count then
1268 -- raise error
1269 raise G_EXCEPTION_HALT_VALIDATION;
1270 end if;
1271 RETURN l_return_status;
1272
1273
1274 EXCEPTION
1275 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1276 l_return_status := OKL_API.G_RET_STS_ERROR;
1277 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1278 p_msg_name => G_REQUIRED_VALUE,
1279 p_token1 => G_COL_NAME_TOKEN,
1280 p_token1_value => 'TXL_AR_LINE_NUMBER');
1281 RETURN l_return_status;
1282 WHEN OTHERS THEN
1283 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1284 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1285 p_msg_name => G_UNEXPECTED_ERROR,
1286 p_token1 => G_SQLCODE_TOKEN,
1287 p_token1_value => SQLCODE,
1288 p_token2 => G_SQLERRM_TOKEN,
1289 p_token2_value => SQLERRM);
1290 RETURN l_return_status;
1291 END;
1292
1293 ----------------------------------------------------------------------------------
1294 -- Start of comments
1295 --
1296 -- Procedure Name : validate_attributes
1297 -- Description : Internal procedure to validate overall billing API usage
1298 -- Business Rules :
1299 -- Parameters :
1300 --
1301 -- Version : 1.0
1302 -- End of comments
1303 ----------------------------------------------------------------------------------
1304 FUNCTION validate_attributes(
1305 p_taiv_rec IN taiv_rec_type,
1306 p_tilv_tbl IN tilv_tbl_type,
1307 p_tldv_tbl IN tldv_tbl_type
1308 ) RETURN VARCHAR2
1309 IS
1310 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1311 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1312
1313 BEGIN
1314
1315 -- Do formal attribute validation:
1316 l_return_status := validate_billing_usage(p_tilv_tbl => p_tilv_tbl,
1317 p_tldv_tbl => p_tldv_tbl);
1318 --- Store the highest degree of error
1319 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1320 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1321 x_return_status := l_return_status;
1322 END IF;
1323 RAISE G_EXCEPTION_HALT_VALIDATION;
1324 END IF;
1325
1326
1327 -- If it's stream based billing usage (3 layers passed in structure)
1328 IF G_IS_STREAM_BASED_BILLING = TRUE THEN
1329
1330 l_return_status := validate_TXL_AR_LINE_NUMBER(p_tilv_tbl => p_tilv_tbl,
1331 p_tldv_tbl => p_tldv_tbl);
1332 --- Store the highest degree of error
1333 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1334 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1335 x_return_status := l_return_status;
1336 END IF;
1337 RAISE G_EXCEPTION_HALT_VALIDATION;
1338 END IF;
1339
1340 END IF;
1341
1342 RETURN x_return_status;
1343 EXCEPTION
1344 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1345 l_return_status := OKL_API.G_RET_STS_ERROR;
1346 RETURN l_return_status;
1347 WHEN OTHERS THEN
1348 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1349 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1350 p_msg_name => G_UNEXPECTED_ERROR,
1351 p_token1 => G_SQLCODE_TOKEN,
1352 p_token1_value => SQLCODE,
1353 p_token2 => G_SQLERRM_TOKEN,
1354 p_token2_value => SQLERRM);
1355 RETURN l_return_status;
1356 END validate_attributes;
1357 ------------------
1358
1359 ----------------------------------------------------------------------------------
1360 -- Start of comments
1361 --
1362 -- Procedure Name : validate_tai_values
1363 -- Description : Internal procedure to validate p_taiv_rec attributes
1364 -- Business Rules :
1365 -- Parameters :
1366 --
1367 -- Version : 1.0
1368 -- End of comments
1369 ----------------------------------------------------------------------------------
1370 PROCEDURE validate_tai_values(
1371 p_taiv_rec IN taiv_rec_type,
1372 x_return_status OUT NOCOPY VARCHAR2
1373 ) IS
1374 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1375 BEGIN
1376
1377 IF (p_taiv_rec.khr_id IS NULL OR p_taiv_rec.khr_id = Okl_Api.G_MISS_NUM) AND
1378 (p_taiv_rec.ixx_id IS NULL OR p_taiv_rec.ixx_id = Okl_Api.G_MISS_NUM)
1379 THEN
1380 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1381 p_msg_name => G_REQUIRED_VALUE,
1382 p_token1 => G_COL_NAME_TOKEN,
1383 p_token1_value => 'okl_trx_ar_invoices_b.ixx_id');
1384
1385 RAISE G_EXCEPTION_HALT_VALIDATION;
1386 END IF;
1387
1388 IF (p_taiv_rec.khr_id IS NULL OR p_taiv_rec.khr_id = Okl_Api.G_MISS_NUM) AND
1389 (p_taiv_rec.irm_id IS NULL OR p_taiv_rec.irm_id = Okl_Api.G_MISS_NUM)
1390 THEN
1391 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1392 p_msg_name => G_REQUIRED_VALUE,
1393 p_token1 => G_COL_NAME_TOKEN,
1394 p_token1_value => 'okl_trx_ar_invoices_b.irm_id');
1395
1396 RAISE G_EXCEPTION_HALT_VALIDATION;
1397 END IF;
1398
1399 IF (p_taiv_rec.khr_id IS NULL OR p_taiv_rec.khr_id = Okl_Api.G_MISS_NUM) AND
1400 (p_taiv_rec.irt_id IS NULL OR p_taiv_rec.irt_id = Okl_Api.G_MISS_NUM)
1401 THEN
1402 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1403 p_msg_name => G_REQUIRED_VALUE,
1404 p_token1 => G_COL_NAME_TOKEN,
1405 p_token1_value => 'okl_trx_ar_invoices_b.irt_id');
1406
1407 RAISE G_EXCEPTION_HALT_VALIDATION;
1408 END IF;
1409
1410 IF (p_taiv_rec.khr_id IS NULL OR p_taiv_rec.khr_id = Okl_Api.G_MISS_NUM) AND
1411 (p_taiv_rec.ibt_id IS NULL OR p_taiv_rec.ibt_id = Okl_Api.G_MISS_NUM)
1412 THEN
1413 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1414 p_msg_name => G_REQUIRED_VALUE,
1415 p_token1 => G_COL_NAME_TOKEN,
1416 p_token1_value => 'okl_trx_ar_invoices_b.ibt_id');
1417
1418 RAISE G_EXCEPTION_HALT_VALIDATION;
1419 END IF;
1420
1421 IF (p_taiv_rec.khr_id IS NULL OR p_taiv_rec.khr_id = Okl_Api.G_MISS_NUM) AND
1422 (p_taiv_rec.org_id IS NULL OR p_taiv_rec.org_id = Okl_Api.G_MISS_NUM)
1423 THEN
1424 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1425 p_msg_name => G_REQUIRED_VALUE,
1426 p_token1 => G_COL_NAME_TOKEN,
1427 p_token1_value => 'okl_trx_ar_invoices_b.org_id');
1428
1429 RAISE G_EXCEPTION_HALT_VALIDATION;
1430 END IF;
1431
1432 IF (p_taiv_rec.khr_id IS NULL OR p_taiv_rec.khr_id = Okl_Api.G_MISS_NUM) AND
1433 (p_taiv_rec.legal_entity_id IS NULL OR p_taiv_rec.legal_entity_id = Okl_Api.G_MISS_NUM)
1434 THEN
1435 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1436 p_msg_name => G_REQUIRED_VALUE,
1437 p_token1 => G_COL_NAME_TOKEN,
1438 p_token1_value => 'okl_trx_ar_invoices_b.legal_entity_id');
1439
1440 RAISE G_EXCEPTION_HALT_VALIDATION;
1441 END IF;
1442
1443 --start: | 30-Mar-2007 cklee -- validate taiv_rec.trx_status_code and default to |
1444 --| 'SUBMITTED' |
1445 --gkhuntet added for Manual Invoice on 06-07-2007 Start. |
1446 IF p_taiv_rec.okl_source_billing_trx <> G_MANUAL AND
1447 --gkhuntet added for Manual Invoice on 06-07-2007 End.
1448 p_taiv_rec.trx_status_code IS NOT NULL
1449 and p_taiv_rec.trx_status_code <> G_SUBMITTED
1450 THEN
1451 OKL_API.set_message(p_app_name => 'OKL',
1452 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1453 p_token1 => 'COL_NAME',
1454 p_token1_value => 'okl_trx_ar_invoices_b.trx_status_code');
1455 RAISE G_EXCEPTION_HALT_VALIDATION;
1456 END IF;
1457 --end: | 30-Mar-2007 cklee -- validate taiv_rec.trx_status_code and default to |
1458 --| 'SUBMITTED' |
1459
1460
1461 x_return_status := l_return_status;
1462
1463 EXCEPTION
1464 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1465 x_return_status := OKL_API.G_RET_STS_ERROR;
1466 WHEN OTHERS THEN
1467 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1468 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1469 p_msg_name => G_UNEXPECTED_ERROR,
1470 p_token1 => G_SQLCODE_TOKEN,
1471 p_token1_value => SQLCODE,
1472 p_token2 => G_SQLERRM_TOKEN,
1473 p_token2_value => SQLERRM);
1474 END validate_tai_values;
1475
1476
1477 ----------------------------------------------------------------------------------
1478 -- Start of comments
1479 -- Procedure Name : validate_tld_values
1480 -- Description : this procedure checks to make sure calling apis, do not pass
1481 -- values to some of the derived columns of OKL_TXD_AR_LN_DTLS_B
1482 -- The reason this procedure need not be invoked in the beginning
1483 -- is APIs normally would not pass these values and if we invoke
1484 -- it in the beginning then it would have to make an additional
1485 -- loop of p_tldv_rec, which would be not performant
1486 -- This procedure will check for error data and will set error
1487 -- message and the applicable return status
1488 -- Usage : Calling procedure should loop thru all tldv records and call
1489 -- this procedure for each record
1490 -- Calling procedure should handle x_return_status properly
1491 -- and raise proper exception after calling this procedure
1492 -- Version : 1.0
1493 -- End of comments
1494 ----------------------------------------------------------------------------------
1495
1496 PROCEDURE validate_tld_values(
1497 p_tldv_rec IN tldv_rec_type,
1498 p_source IN VARCHAR2,
1499 x_return_status OUT NOCOPY VARCHAR2
1500 ) IS
1501 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1502 BEGIN
1503 -- rmunjulu R12 Fixes -- do not allow caller to pass values to following columns
1504 --INVOICE_FORMAT_LINE_TYPE
1505 --LATE_CHARGE_ASSESS_DATE
1506 --LATE_INT_ASSESS_DATE
1507 --LATE_CHARGE_ASS_YN
1508 --LATE_INT_ASS_YN
1509 --INVESTOR_DISB_STATUS
1510 --INVESTOR_DISB_ERR_MG
1511 --DATE_DISBURSED
1512 --PAY_STATUS_CODE
1513 --TAX_AMOUNT
1514 --INVOICE_FORMAT_TYPE
1515 /*
1516 IF p_tldv_rec.INVOICE_FORMAT_LINE_TYPE IS NOT NULL THEN
1517 OKL_API.set_message(p_app_name => 'OKL',
1518 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1519 p_token1 => 'COL_NAME',
1520 p_token1_value => 'INVOICE_FORMAT_LINE_TYPE');
1521 RAISE G_EXCEPTION_HALT_VALIDATION;
1522 END IF;
1523
1524 IF p_tldv_rec.LATE_CHARGE_ASSESS_DATE IS NOT NULL THEN
1525 OKL_API.set_message(p_app_name => 'OKL',
1526 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1527 p_token1 => 'COL_NAME',
1528 p_token1_value => 'LATE_CHARGE_ASSESS_DATE');
1529 RAISE G_EXCEPTION_HALT_VALIDATION;
1530 END IF;
1531
1532 IF p_tldv_rec.LATE_INT_ASSESS_DATE IS NOT NULL THEN
1533 OKL_API.set_message(p_app_name => 'OKL',
1534 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1535 p_token1 => 'COL_NAME',
1536 p_token1_value => 'LATE_INT_ASSESS_DATE');
1537 RAISE G_EXCEPTION_HALT_VALIDATION;
1538 END IF;
1539
1540 IF p_tldv_rec.LATE_CHARGE_ASS_YN IS NOT NULL THEN
1541 OKL_API.set_message(p_app_name => 'OKL',
1542 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1543 p_token1 => 'COL_NAME',
1544 p_token1_value => 'LATE_CHARGE_ASS_YN');
1545 RAISE G_EXCEPTION_HALT_VALIDATION;
1546 END IF;
1547
1548 IF p_tldv_rec.LATE_INT_ASS_YN IS NOT NULL THEN
1549 OKL_API.set_message(p_app_name => 'OKL',
1550 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1551 p_token1 => 'COL_NAME',
1552 p_token1_value => 'LATE_INT_ASS_YN');
1553 RAISE G_EXCEPTION_HALT_VALIDATION;
1554 END IF;
1555
1556 IF p_tldv_rec.INVESTOR_DISB_STATUS IS NOT NULL THEN
1557 OKL_API.set_message(p_app_name => 'OKL',
1558 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1559 p_token1 => 'COL_NAME',
1560 p_token1_value => 'INVESTOR_DISB_STATUS');
1561 RAISE G_EXCEPTION_HALT_VALIDATION;
1562 END IF;
1563
1564 IF p_tldv_rec.INVESTOR_DISB_ERR_MG IS NOT NULL THEN
1565 OKL_API.set_message(p_app_name => 'OKL',
1566 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1567 p_token1 => 'COL_NAME',
1568 p_token1_value => 'INVESTOR_DISB_ERR_MG');
1569 RAISE G_EXCEPTION_HALT_VALIDATION;
1570 END IF;
1571
1572 IF p_tldv_rec.DATE_DISBURSED IS NOT NULL THEN
1573 OKL_API.set_message(p_app_name => 'OKL',
1574 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1575 p_token1 => 'COL_NAME',
1576 p_token1_value => 'DATE_DISBURSED');
1577 RAISE G_EXCEPTION_HALT_VALIDATION;
1578 END IF;
1579
1580 IF p_tldv_rec.PAY_STATUS_CODE IS NOT NULL THEN
1581 OKL_API.set_message(p_app_name => 'OKL',
1582 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1583 p_token1 => 'COL_NAME',
1584 p_token1_value => 'PAY_STATUS_CODE');
1585 RAISE G_EXCEPTION_HALT_VALIDATION;
1586 END IF;
1587
1588 IF p_tldv_rec.TAX_AMOUNT IS NOT NULL THEN
1589 OKL_API.set_message(p_app_name => 'OKL',
1590 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1591 p_token1 => 'COL_NAME',
1592 p_token1_value => 'TAX_AMOUNT');
1593 RAISE G_EXCEPTION_HALT_VALIDATION;
1594 END IF;
1595
1596 IF p_tldv_rec.INVOICE_FORMAT_TYPE IS NOT NULL THEN
1597 OKL_API.set_message(p_app_name => 'OKL',
1598 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1599 p_token1 => 'COL_NAME',
1600 p_token1_value => 'INVOICE_FORMAT_TYPE');
1601 RAISE G_EXCEPTION_HALT_VALIDATION;
1602 END IF;
1603 */
1604 -- LSM_ID does not exist in tapi yet
1605 /*
1606 IF p_tldv_rec.LSM_ID IS NOT NULL THEN
1607 OKL_API.set_message(p_app_name => 'OKL',
1608 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1609 p_token1 => 'COL_NAME',
1610 p_token1_value => 'LSM_ID');
1611 RAISE G_EXCEPTION_HALT_VALIDATION;
1612 END IF;
1613 */
1614 /*
1615 IF p_tldv_rec.KHR_ID IS NOT NULL THEN
1616 OKL_API.set_message(p_app_name => 'OKL',
1617 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1618 p_token1 => 'COL_NAME',
1619 p_token1_value => 'KHR_ID');
1620 RAISE G_EXCEPTION_HALT_VALIDATION;
1621 END IF;
1622
1623 -- if source not rebook then do not allow this column to be populated by called process
1624 IF nvl(p_source,'*') <> 'REBOOK' AND p_tldv_rec.RBK_ORI_INVOICE_NUMBER IS NOT NULL THEN
1625 OKL_API.set_message(p_app_name => 'OKL',
1626 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1627 p_token1 => 'COL_NAME',
1628 p_token1_value => 'RBK_ORI_INVOICE_NUMBER');
1629 RAISE G_EXCEPTION_HALT_VALIDATION;
1630 END IF;
1631
1632 -- if source not rebook then do not allow this column to be populated by called process
1633 IF nvl(p_source,'*') <> 'REBOOK' AND p_tldv_rec.RBK_ORI_INVOICE_LINE_NUMBER IS NOT NULL THEN
1634 OKL_API.set_message(p_app_name => 'OKL',
1635 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1636 p_token1 => 'COL_NAME',
1637 p_token1_value => 'RBK_ORI_INVOICE_LINE_NUMBER');
1638 RAISE G_EXCEPTION_HALT_VALIDATION;
1639 END IF;
1640
1641 -- if source not rebook then do not allow this column to be populated by called process
1642 IF nvl(p_source,'*') <> 'REBOOK' AND p_tldv_rec.RBK_ADJUSTMENT_DATE IS NOT NULL THEN
1643 OKL_API.set_message(p_app_name => 'OKL',
1644 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1645 p_token1 => 'COL_NAME',
1646 p_token1_value => 'RBK_ADJUSTMENT_DATE');
1647 RAISE G_EXCEPTION_HALT_VALIDATION;
1648 END IF;
1649 */
1650 --start: | 05-Apr-2007 cklee -- Fixed the following: |
1651
1652
1653 IF G_IS_STREAM_BASED_BILLING = TRUE THEN
1654
1655 IF (p_tldv_rec.SEL_ID IS NULL or p_tldv_rec.SEL_ID = okl_api.g_miss_num )
1656 AND (p_source <> 'UBB')
1657 --gkhuntet 26-07-2007
1658 AND (p_source <> 'REMARKETING') THEN ----gkhuntet 26-07-2007 -- apaul 20-June-2007
1659 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1660 p_msg_name => G_REQUIRED_VALUE,
1661 p_token1 => G_COL_NAME_TOKEN,
1662 p_token1_value => 'SEL_ID');
1663 RAISE G_EXCEPTION_HALT_VALIDATION;
1664 END IF;
1665
1666 END IF;
1667
1668 --end: | 05-Apr-2007 cklee -- Fixed the following: |
1669
1670 x_return_status := l_return_status;
1671
1672 EXCEPTION
1673 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1674 x_return_status := OKL_API.G_RET_STS_ERROR;
1675 WHEN OTHERS THEN
1676 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1677 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1678 p_msg_name => G_UNEXPECTED_ERROR,
1679 p_token1 => G_SQLCODE_TOKEN,
1680 p_token1_value => SQLCODE,
1681 p_token2 => G_SQLERRM_TOKEN,
1682 p_token2_value => SQLERRM);
1683 END validate_tld_values;
1684 ----------------------------------------------------------------------------------
1685 -- Start of comments
1686 --
1687 -- Procedure Name : create_non_sel_billing_trx
1688 -- Description : wrapper api to create internal billing transactions
1689 -- Business Rules :
1690 -- Usage:
1691 --
1692 --
1693 -- If caller pass the following parameters with data,
1694 -- ,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
1695 -- ,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
1696 -- then system assume caller is intend to create non-stream based (without stream element)
1697 -- internal billing transactions.
1698 --
1699 -- In this scenario, p_tilv_tbl(n).TXL_AR_LINE_NUMBER is not a required attribute.
1700 -- If user does pass p_tilv_tbl(n).TXL_AR_LINE_NUMBER, system will assume this is a
1701 -- redundant data.
1702 -- System will copy the major attributes (STY_ID, AMOUNT, etc) from p_tilv_rec to
1703 -- create record in OKL_TXD_AR_LN_DTLS_b/tl table (Internal billing invoice/invoce line)
1704 --
1705 -- Note: 1. Assume all calling API will validate attributes before make the call. This is
1706 -- the current architecture and we will adopt all validation logic from calling API
1707 -- to this central API in the future.
1708 -- Parameters :
1709 --
1710 -- p_taiv_rec: Internal billing contract transaction header (okl_trx_ar_invoices_v)
1711 -- p_tilv_tbl: Internal billing contract transaction line (OKL_TXL_AR_INV_LNS_V)
1712 -- Version : 1.0
1713 -- End of comments
1714 ----------------------------------------------------------------------------------
1715
1716 PROCEDURE create_non_sel_billing_trx(
1717 p_api_version IN NUMBER
1718 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1719 ,x_return_status OUT NOCOPY VARCHAR2
1720 ,x_msg_count OUT NOCOPY NUMBER
1721 ,x_msg_data OUT NOCOPY VARCHAR2
1722 ,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
1723 ,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
1724 ,x_taiv_rec OUT NOCOPY okl_tai_pvt.taiv_rec_type
1725 ,x_tilv_tbl OUT NOCOPY okl_til_pvt.tilv_tbl_type
1726 -- start: cklee -- fixed return parameters issues 4/6/07
1727 ,x_tldv_tbl OUT NOCOPY okl_tld_pvt.tldv_tbl_type
1728 -- end: cklee -- fixed return parameters issues 4/6/07
1729 )
1730 is
1731 l_api_name CONSTANT VARCHAR2(30) := 'create_non_sel_billing_trx';
1732 l_api_version CONSTANT NUMBER := 1.0;
1733 l_til_loop_cnt NUMBER;
1734 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1735 lp_taiv_rec okl_tai_pvt.taiv_rec_type := p_taiv_rec;
1736 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
1737 lp_tilv_tbl okl_til_pvt.tilv_tbl_type := p_tilv_tbl;
1738 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
1739 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type;
1740 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
1741 l_taiv_id NUMBER;
1742 lx_tilv_rec okl_til_pvt.tilv_rec_type;
1743 l_tld_loop_cnt NUMBER;
1744 l_til_ln_number NUMBER;
1745 lx_tldv_rec okl_tld_pvt.tldv_rec_type;
1746 l_til_id NUMBER;
1747 l_til_debug_cnt NUMBER;
1748 l_tld_debug_cnt NUMBER;
1749 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
1750 --gkhuntet added start.
1751 l_flag_acc_call VARCHAR2(5);
1752 --gkhuntet added end.
1753
1754 ---- Added by Vpanwar --- Code for new accounting API uptake
1755 l_tmpl_identify_rec Okl_Account_Dist_Pub.TMPL_IDENTIFY_REC_TYPE;
1756 l_dist_info_rec Okl_Account_Dist_Pub.DIST_INFO_REC_TYPE;
1757 l_ctxt_val_tbl Okl_Account_Dist_Pub.CTXT_VAL_TBL_TYPE;
1758 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.ACC_GEN_PRIMARY_KEY;
1759
1760 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.TMPL_IDENTIFY_TBL_TYPE;
1761 l_dist_info_tbl Okl_Account_Dist_Pvt.DIST_INFO_TBL_TYPE;
1762 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
1763 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
1764 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
1765 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
1766 l_trx_header_id NUMBER;
1767 l_trx_header_tbl VARCHAR2(50);
1768 ---- End Added by Vpanwar --- Code for new accounting API uptake
1769
1770 begin
1771 -- Set API savepoint
1772 SAVEPOINT create_non_sel_billing_trx;
1773 IF (G_DEBUG_ENABLED = 'Y') THEN
1774 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1775 END IF;
1776 --Print Input Variables
1777 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1778 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Taiv Parameters '||' Currency Code :'||p_taiv_rec.currency_code||' Currency conversion type :'||p_taiv_rec.currency_conversion_type);
1779 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Currency conversion rate :'||p_taiv_rec.currency_conversion_rate||' Currency conversion date :'||p_taiv_rec.currency_conversion_date);
1780
1781 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'tilv Parameters');
1782 IF (p_tilv_tbl.count > 0) THEN -- 6402950
1783 l_til_debug_cnt := p_tilv_tbl.first;
1784 loop
1785 --for l_til_debug_cnt in 1 .. p_tilv_tbl.count loop
1786 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inv receiv line code :'||p_tilv_tbl(l_til_debug_cnt).inv_receiv_line_code);
1787 EXIT WHEN l_til_debug_cnt = p_tilv_tbl.LAST; -- 6402950
1788 l_til_debug_cnt := p_tilv_tbl.NEXT(l_til_debug_cnt);
1789 end loop;
1790 END IF;
1791
1792 END IF;
1793 -- Check for call compatibility
1794 IF (NOT FND_API.Compatible_API_Call (l_api_version,
1795 p_api_version,
1796 l_api_name,
1797 G_PKG_NAME ))
1798 THEN
1799 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1800 END IF;
1801
1802 -- Initialize message list if requested
1803 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1804 FND_MSG_PUB.initialize;
1805 END IF;
1806
1807 -- Initialize API status to success
1808 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1809
1810
1811 /*** Begin API body ****************************************************/
1812
1813
1814 -- 2. Create okl_trx_ar_invoices_b record: okl_tai_pvt.insert_row;
1815
1816 -- start: cklee -- add additional columns 3/19/07
1817 validate_tai_values(
1818 p_taiv_rec => lp_taiv_rec,
1819 x_return_status => l_return_status);
1820
1821 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1822 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1823 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1824 RAISE OKL_API.G_EXCEPTION_ERROR;
1825 END IF;
1826
1827 additional_tai_attr(
1828 p_api_version => p_api_version,
1829 p_init_msg_list => p_init_msg_list,
1830 x_return_status => l_return_status,
1831 x_msg_count => x_msg_count,
1832 x_msg_data => x_msg_data,
1833 p_taiv_rec => lp_taiv_rec,
1834 x_taiv_rec => lx_taiv_rec);
1835
1836 lp_taiv_rec := lx_taiv_rec;
1837
1838 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1839 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1840 x_return_status := l_return_status;
1841 END IF;
1842 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1843 END IF;
1844 -- end: cklee -- add additional columns 3/19/07
1845
1846 okl_tai_pvt.insert_row(
1847 p_api_version => p_api_version,
1848 p_init_msg_list => p_init_msg_list,
1849 x_return_status => l_return_status,
1850 x_msg_count => x_msg_count,
1851 x_msg_data => x_msg_data,
1852 p_taiv_rec => lp_taiv_rec,
1853 x_taiv_rec => lx_taiv_rec);
1854
1855 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1856 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1857 x_return_status := l_return_status;
1858 END IF;
1859 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1860 END IF;
1861
1862 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1863 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_taiv_rec.id: '||to_char(lx_taiv_rec.id));
1864 END IF;
1865 -- 3. Assign attributes back to lx_taiv_rec along with ID (passed lx_taiv_rec as OUT parameter)
1866
1867 l_taiv_id := lx_taiv_rec.ID;
1868
1869 -- 4. Loop til tbl
1870 l_til_loop_cnt := lp_tilv_tbl.first;
1871 loop
1872 --FOR l_til_loop_cnt in 1 .. lp_tilv_tbl.count loop
1873
1874 -- Assign lx_taiv_rec.ID to lp_til_rec.TAI_ID;
1875 lp_tilv_tbl(l_til_loop_cnt).TAI_ID := l_taiv_id;
1876 --start: | 05-Apr-2007 cklee -- Fixed the following: |
1877 lp_tilv_tbl(l_til_loop_cnt).ORG_ID := lp_taiv_rec.org_id;
1878 --end: | 05-Apr-2007 cklee -- Fixed the following: |
1879
1880 -- start: cklee -- add additional columns 3/19/07
1881
1882 additional_til_attr(
1883 p_api_version => p_api_version,
1884 p_init_msg_list => p_init_msg_list,
1885 x_return_status => l_return_status,
1886 x_msg_count => x_msg_count,
1887 x_msg_data => x_msg_data,
1888 p_tilv_rec => lp_tilv_tbl(l_til_loop_cnt),
1889 x_tilv_rec => lx_tilv_tbl(l_til_loop_cnt));
1890
1891 lp_tilv_tbl(l_til_loop_cnt) := lx_tilv_tbl(l_til_loop_cnt);
1892
1893 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1894 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1895 x_return_status := l_return_status;
1896 END IF;
1897 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1898 END IF;
1899 -- end: cklee -- add additional columns 3/19/07
1900
1901 -- Create okl_TXL_AR_INV_LNS_B record: okl_til_pvt.insert_row;
1902 okl_til_pvt.insert_row(
1903 p_api_version => p_api_version,
1904 p_init_msg_list => p_init_msg_list,
1905 x_return_status => l_return_status,
1906 x_msg_count => x_msg_count,
1907 x_msg_data => x_msg_data,
1908 p_tilv_rec => lp_tilv_tbl(l_til_loop_cnt),
1909 x_tilv_rec => lx_tilv_rec);
1910
1911 -- Error handling lx_taiv_rec;
1912 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1913 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1914 x_return_status := l_return_status;
1915 END IF;
1916 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1917 END IF;
1918
1919 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1920 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tilv_rec.id: '||to_char(lx_tilv_rec.id));
1921 END IF;
1922
1923 -- l_til_ln_number := lx_tilv_rec.TXL_AR_LINE_NUMBER;
1924 l_til_id := lx_tilv_rec.id;
1925
1926 -- Assign attributes back to lx_tilv_rec along with ID;
1927 -- start: cklee -- fixed return parameters issues 4/6/07
1928 lp_tilv_tbl(l_til_loop_cnt) := lx_tilv_rec;
1929 -- end: cklee -- fixed return parameters issues 4/6/07
1930
1931 /***
1932 -- Developer Note:
1933 -- 1. For each TIL record, copy STY_ID, AMOUNT, ORG_ID, INVENTORY_ORG_ID, INVENTORY_ITEM_ID
1934 -- to TLD pl/sql record and call okl_tld_pvt.insert_row() to create TLD.
1935 -- 2. lx_tilv_rec.TXL_AR_LINE_NUMBER is not required for this procesdure
1936 --
1937 ***/
1938 lp_tldv_tbl(l_til_loop_cnt).TIL_ID_DETAILS := l_til_id;
1939 lp_tldv_tbl(l_til_loop_cnt).STY_ID := lx_tilv_rec.STY_ID;
1940 lp_tldv_tbl(l_til_loop_cnt).AMOUNT := lx_tilv_rec.AMOUNT; -- this is 2 level, so we need to copy to tld
1941 lp_tldv_tbl(l_til_loop_cnt).ORG_ID := lx_tilv_rec.ORG_ID;
1942 lp_tldv_tbl(l_til_loop_cnt).INVENTORY_ORG_ID := lx_tilv_rec.INVENTORY_ORG_ID;
1943 lp_tldv_tbl(l_til_loop_cnt).INVENTORY_ITEM_ID := lx_tilv_rec.INVENTORY_ITEM_ID;
1944 -- start: cklee -- Add these columns since these are required columns
1945 lp_tldv_tbl(l_til_loop_cnt).LINE_DETAIL_NUMBER := l_til_loop_cnt;
1946 lp_tldv_tbl(l_til_loop_cnt).KHR_ID := lp_taiv_rec.KHR_ID;
1947 lp_tldv_tbl(l_til_loop_cnt).KLE_ID := lp_tilv_tbl(l_til_loop_cnt).KLE_ID;
1948 -- end: cklee
1949
1950 -- rmunjulu R12 Fixes -- Default invoice_format_type, invoice_format_line_type
1951 Get_Invoice_format(
1952 p_api_version => p_api_version
1953 ,p_init_msg_list => OKL_API.G_FALSE
1954 ,x_return_status => l_return_status
1955 ,x_msg_count => x_msg_count
1956 ,x_msg_data => x_msg_data
1957 ,p_inf_id => lp_taiv_rec.inf_id
1958 ,p_sty_id => lp_tldv_tbl(l_til_loop_cnt).STY_ID
1959 ,x_invoice_format_type => lp_tldv_tbl(l_til_loop_cnt).invoice_format_type
1960 ,x_invoice_format_line_type => lp_tldv_tbl(l_til_loop_cnt).invoice_format_line_type);
1961
1962 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1963 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1964 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1965 RAISE OKL_API.G_EXCEPTION_ERROR;
1966 END IF;
1967
1968 -- start: cklee -- add additional columns 3/19/07
1969 additional_tld_attr(
1970 p_api_version => p_api_version,
1971 p_init_msg_list => p_init_msg_list,
1972 x_return_status => l_return_status,
1973 x_msg_count => x_msg_count,
1974 x_msg_data => x_msg_data,
1975 p_tldv_rec => lp_tldv_tbl(l_til_loop_cnt),
1976 x_tldv_rec => lx_tldv_tbl(l_til_loop_cnt));
1977
1978 lp_tldv_tbl(l_til_loop_cnt) := lx_tldv_tbl(l_til_loop_cnt);
1979
1980 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1981 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1982 x_return_status := l_return_status;
1983 END IF;
1984 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1985 END IF;
1986 -- end: cklee -- add additional columns 3/19/07
1987
1988 okl_tld_pvt.insert_row(
1989 p_api_version => p_api_version,
1990 p_init_msg_list => p_init_msg_list,
1991 x_return_status => l_return_status,
1992 x_msg_count => x_msg_count,
1993 x_msg_data => x_msg_data,
1994 p_tldv_rec => lp_tldv_tbl(l_til_loop_cnt),
1995 x_tldv_rec => lx_tldv_rec);
1996
1997 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1998 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1999 x_return_status := l_return_status;
2000 END IF;
2001 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2002 END IF;
2003 lp_tldv_tbl(l_til_loop_cnt) := lx_tldv_rec;
2004
2005 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2006 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tldv_rec.id: '||to_char(lx_tldv_rec.id));
2007 END IF;
2008
2009 /*
2010 -- Set Loop counter to 0;
2011 l_tld_loop_cnt := 0;
2012 -- Loop tld tbl with user key: TXL_AR_LINE_NUMBER
2013 l_tld_loop_cnt:= lp_tldv_tbl.first;
2014 loop
2015 --FOR l_tld_loop_cnt in 1 .. lp_tldv_tbl.count loop
2016 -- If TXL_AR_LINE_NUMBER matched then
2017
2018 If lp_tldv_tbl(l_tld_loop_cnt).TXL_AR_LINE_NUMBER = l_til_ln_number then
2019 -- Assign lx_til_rec.ID to lp_tld_rec.TIL_ID_DETAILS;
2020 lp_tldv_tbl(l_tld_loop_cnt).TIL_ID_DETAILS := l_til_id;
2021 -- Create okl_TXD_AR_LN_DTLS_B record: okl_tld_pvt.insert_row;
2022 okl_tld_pvt.insert_row(
2023 p_api_version => p_api_version,
2024 p_init_msg_list => p_init_msg_list,
2025 x_return_status => l_return_status,
2026 x_msg_count => x_msg_count,
2027 x_msg_data => x_msg_data,
2028 p_tldv_rec => lp_tldv_tbl(l_tld_loop_cnt),
2029 x_tldv_rec => lx_tldv_rec);
2030 -- Assign attributes back to lx_tldv_rec along with ID;
2031 -- Error handling;
2032 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2033 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2034 x_return_status := l_return_status;
2035 END IF;
2036 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2037 END IF;
2038 lp_tldv_tbl(l_tld_loop_cnt) := lx_tldv_rec;
2039 -- end if;
2040 End If;
2041 EXIT WHEN l_tld_loop_cnt = lp_tldv_tbl.LAST;
2042 l_tld_loop_cnt := lp_tldv_tbl.NEXT(l_tld_loop_cnt);
2043 -- End loop;
2044 end loop;
2045 */
2046 -- 5. End loop;
2047 EXIT WHEN l_til_loop_cnt = lp_tilv_tbl.LAST;
2048 l_til_loop_cnt := lp_tilv_tbl.NEXT(l_til_loop_cnt);
2049 end loop;
2050
2051 x_taiv_rec := lx_taiv_rec;
2052 x_tilv_tbl := lp_tilv_tbl;
2053 -- start: cklee -- fixed return parameters issues 4/6/07
2054 x_tldv_tbl := lp_tldv_tbl;
2055 -- end: cklee -- fixed return parameters issues 4/6/07
2056
2057 --gkhuntet start.
2058 l_flag_acc_call := 'Y';
2059 IF(lx_taiv_rec.okl_source_billing_trx = G_MANUAL
2060 AND lx_taiv_rec. trx_status_code <> 'SUBMITTED') THEN
2061 l_flag_acc_call := 'N';
2062 END IF;
2063
2064
2065 IF(l_flag_acc_call = 'Y') THEN
2066 create_accounting_dist(p_api_version => p_api_version ,
2067 p_init_msg_list => p_init_msg_list ,
2068 x_return_status => l_return_status ,
2069 x_msg_count => x_msg_count ,
2070 x_msg_data => x_msg_data ,
2071 p_tldv_tbl => lp_tldv_tbl ,
2072 p_tai_id => lx_taiv_rec.ID
2073 );
2074 END IF;
2075 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2076 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2077 x_return_status := l_return_status;
2078 END IF;
2079 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2080 END IF;
2081 --gkhuntet end.
2082
2083 /****** Code commented by gkhuntet , call the create_accounting_dist. ******/
2084
2085 /*-- Increase the counter;
2086 l_tld_loop_cnt := 0;
2087 -- 6. Process accounting distributions;
2088 l_tld_loop_cnt := lp_tldv_tbl.first;
2089 loop
2090 --FOR l_tld_loop_cnt in 1 .. lp_tldv_tbl.count loop
2091 p_bpd_acc_rec.id := lp_tldv_tbl(l_tld_loop_cnt).id;
2092 p_bpd_acc_rec.source_table := 'OKL_TXD_AR_LN_DTLS_B';
2093
2094
2095 /* apaul -- Code commented out because new accing API uptake not complete
2096 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(p_api_version => p_api_version,
2097 p_init_msg_list => p_init_msg_list,
2098 x_return_status => l_return_status,
2099 x_msg_count => x_msg_count,
2100 x_msg_data => x_msg_data,
2101 p_bpd_acc_rec => p_bpd_acc_rec);
2102
2103 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2104 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2105 x_return_status := l_return_status;
2106 END IF;
2107 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2108 END IF;
2109
2110 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2111 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Okl_Acc_Call_Pub.CREATE_ACC_TRANS:p_bpd_acc_rec.id: '||to_char(p_bpd_acc_rec.id));
2112 END IF;
2113 */
2114
2115 ---- Added by Vpanwar --- Code for new accounting API uptake
2116 /*
2117 Okl_Acc_Call_Pub.CREATE_ACC_TRANS_NEW(p_api_version => p_api_version,
2118 p_init_msg_list => p_init_msg_list,
2119 x_return_status => l_return_status,
2120 x_msg_count => x_msg_count,
2121 x_msg_data => x_msg_data,
2122 p_bpd_acc_rec => p_bpd_acc_rec,
2123 x_tmpl_identify_rec => l_tmpl_identify_rec,
2124 x_dist_info_rec => l_dist_info_rec,
2125 x_ctxt_val_tbl => l_ctxt_val_tbl,
2126 x_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
2127
2128 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2129 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2130 x_return_status := l_return_status;
2131 END IF;
2132 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2133 END IF;
2134
2135
2136 --- populate the tables for passing to Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST
2137
2138 l_acc_gen_tbl(l_tld_loop_cnt).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
2139 l_acc_gen_tbl(l_tld_loop_cnt).source_id := l_dist_info_rec.source_id;
2140
2141 l_ctxt_tbl(l_tld_loop_cnt).ctxt_val_tbl := l_ctxt_val_tbl;
2142 l_ctxt_tbl(l_tld_loop_cnt).source_id := l_dist_info_rec.source_id;
2143
2144 l_tmpl_identify_tbl(l_tld_loop_cnt) := l_tmpl_identify_rec;
2145
2146 l_dist_info_tbl(l_tld_loop_cnt) := l_dist_info_rec;
2147
2148 ---- End Added by Vpanwar --- Code for new accounting API uptake
2149
2150 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2151 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Okl_Acc_Call_Pub.CREATE_ACC_TRANS_NEW:p_bpd_acc_rec.id: '||to_char(p_bpd_acc_rec.id));
2152 END IF;
2153
2154 EXIT WHEN l_tld_loop_cnt = lp_tldv_tbl.LAST;
2155 l_tld_loop_cnt := lp_tldv_tbl.NEXT(l_tld_loop_cnt);
2156 end loop;
2157
2158 ---- Added by Vpanwar --- Code for new accounting API uptake
2159 l_trx_header_tbl:= 'okl_trx_ar_invoices_b';
2160 l_trx_header_id := lx_taiv_rec.id;
2161 --Call accounting with new signature
2162 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
2163 p_api_version => p_api_version,
2164 p_init_msg_list => p_init_msg_list,
2165 x_return_status => x_return_status,
2166 x_msg_count => x_msg_count,
2167 x_msg_data => x_msg_data,
2168 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
2169 p_dist_info_tbl => l_dist_info_tbl,
2170 p_ctxt_val_tbl => l_ctxt_tbl,
2171 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
2172 x_template_tbl => l_template_out_tbl,
2173 x_amount_tbl => l_amount_out_tbl,
2174 p_trx_header_id => l_trx_header_id,
2175 p_trx_header_table => l_trx_header_tbl);
2176
2177 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2178 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2179 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2180 RAISE OKL_API.G_EXCEPTION_ERROR;
2181 END IF;
2182 */
2183 ---- End Added by Vpanwar --- Code for new accounting API uptake
2184
2185
2186 -- Note: Refer to okl_billing_controller_pvt.bill_streams_master for details
2187 --
2188
2189 /*** End API body ******************************************************/
2190
2191 -- Get message count and if count is 1, get message info
2192 FND_MSG_PUB.Count_And_Get
2193 (p_count => x_msg_count,
2194 p_data => x_msg_data);
2195
2196 EXCEPTION
2197 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2198 ROLLBACK TO create_non_sel_billing_trx;
2199 x_return_status := OKL_API.G_RET_STS_ERROR;
2200 FND_MSG_PUB.Count_And_Get
2201 (p_count => x_msg_count,
2202 p_data => x_msg_data);
2203
2204 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2205 ROLLBACK TO create_non_sel_billing_trx;
2206 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2207 FND_MSG_PUB.Count_And_Get
2208 (p_count => x_msg_count,
2209 p_data => x_msg_data);
2210
2211 WHEN OTHERS THEN
2212 ROLLBACK TO create_non_sel_billing_trx;
2213
2214 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2215 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2216 p_msg_name => G_UNEXPECTED_ERROR,
2217 p_token1 => G_SQLCODE_TOKEN,
2218 p_token1_value => SQLCODE,
2219 p_token2 => G_SQLERRM_TOKEN,
2220 p_token2_value => SQLERRM);
2221 FND_MSG_PUB.Count_And_Get
2222 (p_count => x_msg_count,
2223 p_data => x_msg_data);
2224 end create_non_sel_billing_trx;
2225
2226
2227 ----------------------------------------------------------------------------------
2228 -- Start of comments
2229 --
2230 -- Procedure Name : create_sel_billing_trx
2231 -- Description : wrapper api to create internal billing transactions
2232 -- Business Rules :
2233 --
2234 -- If caller pass the following parameters with data,
2235 -- ,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
2236 -- ,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
2237 -- ,p_tldv_tbl IN okl_tld_pvt.tldv_tbl_type
2238 -- then system assume caller is intend to create stream based (with stream element)
2239 -- internal billing transactions.
2240 --
2241 -- In this scenario, the following rules applied:
2242 -- R1): If p_tilv_tbl(n).TXL_AR_LINE_NUMBER exists, but p_tldv_tbl(n).TXL_AR_LINE_NUMBER
2243 -- doesn't exists, throw error.
2244 -- R2): If p_tldv_tbl(n).TXL_AR_LINE_NUMBER exists, but p_tilv_tbl(n).TXL_AR_LINE_NUMBER
2245 -- doesn't exists, throw error.
2246 --
2247 -- Note:
2248 -- p_tilv_tbl(n).TXL_AR_LINE_NUMBER :
2249 -- User key to link between p_tilv_rec and p_tldv_tbl
2250 --
2251 -- p_tldv_tbl(n).TXL_AR_LINE_NUMBER :
2252 -- User key to link between p_tldv_rec and p_tilv_rec
2253 --
2254 -- Note: In order to process this API properly, you need to pass user enter TXL_AR_LINE_NUMBER
2255 -- to link between p_tilv_rec and p_tldv_tbl.
2256 --
2257 -- Parameters :
2258 --
2259 -- p_taiv_rec: Internal billing contract transaction header (okl_trx_ar_invoices_v)
2260 -- p_tilv_tbl: Internal billing contract transaction line (OKL_TXL_AR_INV_LNS_V)
2261 -- p_tldv_tbl: Internal billing invoice/invoce line (OKL_TXD_AR_LN_DTLS_V)
2262 -- Version : 1.0
2263 -- End of comments
2264 ----------------------------------------------------------------------------------
2265
2266 PROCEDURE create_sel_billing_trx(
2267 p_api_version IN NUMBER
2268 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2269 ,x_return_status OUT NOCOPY VARCHAR2
2270 ,x_msg_count OUT NOCOPY NUMBER
2271 ,x_msg_data OUT NOCOPY VARCHAR2
2272 ,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
2273 ,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
2274 ,p_tldv_tbl IN okl_tld_pvt.tldv_tbl_type
2275 ,x_taiv_rec OUT NOCOPY okl_tai_pvt.taiv_rec_type
2276 ,x_tilv_tbl OUT NOCOPY okl_til_pvt.tilv_tbl_type
2277 ,x_tldv_tbl OUT NOCOPY okl_tld_pvt.tldv_tbl_type
2278 )
2279 is
2280 l_api_name CONSTANT VARCHAR2(30) := 'create_sel_billing_trx';
2281 l_api_version CONSTANT NUMBER := 1.0;
2282 l_til_loop_cnt NUMBER;
2283 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2284 lp_taiv_rec okl_tai_pvt.taiv_rec_type := p_taiv_rec;
2285 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
2286 lp_tilv_tbl okl_til_pvt.tilv_tbl_type := p_tilv_tbl;
2287 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
2288 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type := p_tldv_tbl;
2289 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
2290 l_taiv_id NUMBER;
2291 lx_tilv_rec okl_til_pvt.tilv_rec_type;
2292 l_tld_loop_cnt NUMBER;
2293 l_til_ln_number NUMBER;
2294 lx_tldv_rec okl_tld_pvt.tldv_rec_type;
2295 l_til_id NUMBER;
2296 l_til_debug_cnt NUMBER;
2297 l_tld_debug_cnt NUMBER;
2298 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
2299
2300 ---- Added by Vpanwar --- Code for new accounting API uptake
2301 l_tmpl_identify_rec Okl_Account_Dist_Pub.TMPL_IDENTIFY_REC_TYPE;
2302 l_dist_info_rec Okl_Account_Dist_Pub.DIST_INFO_REC_TYPE;
2303 l_ctxt_val_tbl Okl_Account_Dist_Pub.CTXT_VAL_TBL_TYPE;
2304 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.ACC_GEN_PRIMARY_KEY;
2305
2306 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.TMPL_IDENTIFY_TBL_TYPE;
2307 l_dist_info_tbl Okl_Account_Dist_Pvt.DIST_INFO_TBL_TYPE;
2308 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
2309 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
2310 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
2311 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
2312 l_trx_header_id NUMBER;
2313 l_trx_header_tbl VARCHAR2(50);
2314 ---- End Added by Vpanwar --- Code for new accounting API uptake
2315 begin
2316 -- Set API savepoint
2317 SAVEPOINT create_sel_billing_trx;
2318 IF (G_DEBUG_ENABLED = 'Y') THEN
2319 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2320 END IF;
2321 --Print Input Variables
2322 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2323 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Taiv Parameters '||' Currency Code :'||p_taiv_rec.currency_code||' Currency conversion type :'||p_taiv_rec.currency_conversion_type);
2324 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Currency conversion rate :'||p_taiv_rec.currency_conversion_rate||' Currency conversion date :'||p_taiv_rec.currency_conversion_date);
2325
2326 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'tilv Parameters');
2327 IF (p_tilv_tbl.count > 0) THEN -- 6402950
2328 l_til_debug_cnt := p_tilv_tbl.first;
2329 loop
2330 --for l_til_debug_cnt in 1 .. p_tilv_tbl.count loop
2331 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inv receiv line code :'||p_tilv_tbl(l_til_debug_cnt).inv_receiv_line_code);
2332 EXIT WHEN l_til_debug_cnt = p_tilv_tbl.LAST; -- 6402950
2333 l_til_debug_cnt := p_tilv_tbl.NEXT(l_til_debug_cnt);
2334 end loop;
2335 END IF;
2336
2337 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'tldv Parameters');
2338 IF (lp_tldv_tbl.count > 0) THEN -- 6402950
2339 l_tld_debug_cnt := lp_tldv_tbl.first; -- 6402950
2340 loop
2341 --FOR l_tld_debug_cnt in 1 .. p_tldv_tbl.count LOOP
2342 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'TXL AR LINE NUMBER :'||lp_tldv_tbl(l_tld_debug_cnt).TXL_AR_LINE_NUMBER);
2343 EXIT WHEN l_tld_debug_cnt = lp_tldv_tbl.LAST;
2344 l_tld_debug_cnt := lp_tldv_tbl.NEXT(l_tld_debug_cnt);
2345 END LOOP;
2346 END IF;
2347 END IF;
2348 -- Check for call compatibility
2349 IF (NOT FND_API.Compatible_API_Call (l_api_version,
2350 p_api_version,
2351 l_api_name,
2352 G_PKG_NAME ))
2353 THEN
2354 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2355 END IF;
2356
2357 -- Initialize message list if requested
2358 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2359 FND_MSG_PUB.initialize;
2360 END IF;
2361
2362 -- Initialize API status to success
2363 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2364
2365
2366 /*** Begin API body ****************************************************/
2367
2368 --
2369
2370 -- 2. Create okl_trx_ar_invoices_b record: okl_tai_pvt.insert_row;
2371
2372 -- start: cklee -- add additional columns 3/19/07
2373
2374 validate_tai_values(
2375 p_taiv_rec => lp_taiv_rec,
2376 x_return_status => l_return_status);
2377
2378 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2379 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2380 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2381 RAISE OKL_API.G_EXCEPTION_ERROR;
2382 END IF;
2383
2384 additional_tai_attr(
2385 p_api_version => p_api_version,
2386 p_init_msg_list => p_init_msg_list,
2387 x_return_status => l_return_status,
2388 x_msg_count => x_msg_count,
2389 x_msg_data => x_msg_data,
2390 p_taiv_rec => lp_taiv_rec,
2391 x_taiv_rec => lx_taiv_rec);
2392
2393 lp_taiv_rec := lx_taiv_rec;
2394
2395 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2396 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2397 x_return_status := l_return_status;
2398 END IF;
2399 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2400 END IF;
2401 -- end: cklee -- add additional columns 3/19/07
2402
2403 okl_tai_pvt.insert_row(
2404 p_api_version => p_api_version,
2405 p_init_msg_list => p_init_msg_list,
2406 x_return_status => l_return_status,
2407 x_msg_count => x_msg_count,
2408 x_msg_data => x_msg_data,
2409 p_taiv_rec => lp_taiv_rec,
2410 x_taiv_rec => lx_taiv_rec);
2411
2412 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2413 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2414 x_return_status := l_return_status;
2415 END IF;
2416 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2417 END IF;
2418 -- 3. Assign attributes back to lx_taiv_rec along with ID (passed lx_taiv_rec as OUT parameter)
2419 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2420 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_taiv_rec.id: '||to_char(lx_taiv_rec.id));
2421 END IF;
2422
2423 l_taiv_id := lx_taiv_rec.ID;
2424
2425 -- 4. Loop til tbl
2426 l_til_loop_cnt := lp_tilv_tbl.first;
2427 loop
2428 --FOR l_til_loop_cnt in 1 .. lp_tilv_tbl.count loop
2429
2430 -- Assign lx_taiv_rec.ID to lp_til_rec.TAI_ID;
2431 lp_tilv_tbl(l_til_loop_cnt).TAI_ID := l_taiv_id;
2432 --start: | 05-Apr-2007 cklee -- Fixed the following: |
2433 lp_tilv_tbl(l_til_loop_cnt).ORG_ID := lp_taiv_rec.org_id;
2434 --end: | 05-Apr-2007 cklee -- Fixed the following: |
2435
2436 -- start: cklee -- add additional columns 3/19/07
2437 additional_til_attr(
2438 p_api_version => p_api_version,
2439 p_init_msg_list => p_init_msg_list,
2440 x_return_status => l_return_status,
2441 x_msg_count => x_msg_count,
2442 x_msg_data => x_msg_data,
2443 p_tilv_rec => lp_tilv_tbl(l_til_loop_cnt),
2444 x_tilv_rec => lx_tilv_tbl(l_til_loop_cnt));
2445
2446 lp_tilv_tbl(l_til_loop_cnt) := lx_tilv_tbl(l_til_loop_cnt);
2447
2448 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2449 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2450 x_return_status := l_return_status;
2451 END IF;
2452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2453 END IF;
2454 -- end: cklee -- add additional columns 3/19/07
2455
2456 -- Create okl_TXL_AR_INV_LNS_B record: okl_til_pvt.insert_row;
2457 okl_til_pvt.insert_row(
2458 p_api_version => p_api_version,
2459 p_init_msg_list => p_init_msg_list,
2460 x_return_status => l_return_status,
2461 x_msg_count => x_msg_count,
2462 x_msg_data => x_msg_data,
2463 p_tilv_rec => lp_tilv_tbl(l_til_loop_cnt),
2464 x_tilv_rec => lx_tilv_rec);
2465
2466 -- Error handling lx_taiv_rec;
2467 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2468 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2469 x_return_status := l_return_status;
2470 END IF;
2471 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2472 END IF;
2473 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2474 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tilv_rec.id: '||to_char(lx_tilv_rec.id));
2475 END IF;
2476
2477 l_til_ln_number := lx_tilv_rec.TXL_AR_LINE_NUMBER;
2478 l_til_id := lx_tilv_rec.id;
2479
2480 -- Assign attributes back to lx_tilv_rec along with ID;
2481 -- start: cklee -- fixed return parameters issues 4/6/07
2482 lp_tilv_tbl(l_til_loop_cnt) := lx_tilv_rec;
2483 -- end: cklee -- fixed return parameters issues 4/6/07
2484
2485 -- Set Loop counter to 0;
2486 l_tld_loop_cnt := 0;
2487 -- Loop tld tbl with user key: TXL_AR_LINE_NUMBER
2488 l_tld_loop_cnt:= lp_tldv_tbl.first;
2489 loop
2490 --FOR l_tld_loop_cnt in 1 .. lp_tldv_tbl.count loop
2491 -- If TXL_AR_LINE_NUMBER matched then
2492
2493 If lp_tldv_tbl(l_tld_loop_cnt).TXL_AR_LINE_NUMBER = l_til_ln_number then
2494
2495 -- rmunjulu R12 Fixes -- Validate that passed values for tld table are valid
2496
2497 validate_tld_values(
2498 p_tldv_rec => lp_tldv_tbl(l_til_loop_cnt),
2499 p_source => lp_taiv_rec.okl_source_billing_trx,
2500 x_return_status => l_return_status);
2501
2502 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2503 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2504 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2505 RAISE OKL_API.G_EXCEPTION_ERROR;
2506 END IF;
2507
2508 -- Assign lx_til_rec.ID to lp_tld_rec.TIL_ID_DETAILS;
2509 lp_tldv_tbl(l_tld_loop_cnt).TIL_ID_DETAILS := l_til_id;
2510 lp_tldv_tbl(l_til_loop_cnt).LINE_DETAIL_NUMBER := l_til_loop_cnt;
2511 lp_tldv_tbl(l_tld_loop_cnt).KHR_ID := lp_taiv_rec.KHR_ID;
2512 lp_tldv_tbl(l_til_loop_cnt).KLE_ID := lp_tilv_tbl(l_til_loop_cnt).KLE_ID;
2513 -- Create okl_TXD_AR_LN_DTLS_B record: okl_tld_pvt.insert_row;
2514
2515 -- rmunjulu R12 Fixes -- Added the below to populate the tld columns
2516 -- lp_tldv_tbl(l_til_loop_cnt).STY_ID := lx_tilv_rec.STY_ID;
2517 -- start: cklee til.amount may not be the same as tld amount, so commented the following code
2518 -- lp_tldv_tbl(l_til_loop_cnt).AMOUNT := lx_tilv_rec.AMOUNT;
2519 -- end: cklee til.amount may not be the same as tld amount, so commented the following code
2520 lp_tldv_tbl(l_til_loop_cnt).ORG_ID := lx_tilv_rec.ORG_ID;
2521 lp_tldv_tbl(l_til_loop_cnt).INVENTORY_ORG_ID := lx_tilv_rec.INVENTORY_ORG_ID;
2522 lp_tldv_tbl(l_til_loop_cnt).INVENTORY_ITEM_ID := lx_tilv_rec.INVENTORY_ITEM_ID;
2523
2524 -- rmunjulu R12 Fixes -- Default invoice_format_type, invoice_format_line_type
2525 Get_Invoice_format(
2526 p_api_version => p_api_version
2527 ,p_init_msg_list => OKL_API.G_FALSE
2528 ,x_return_status => l_return_status
2529 ,x_msg_count => x_msg_count
2530 ,x_msg_data => x_msg_data
2531 ,p_inf_id => lp_taiv_rec.inf_id
2532 ,p_sty_id => lp_tldv_tbl(l_til_loop_cnt).STY_ID
2533 ,x_invoice_format_type => lp_tldv_tbl(l_til_loop_cnt).invoice_format_type
2534 ,x_invoice_format_line_type => lp_tldv_tbl(l_til_loop_cnt).invoice_format_line_type);
2535
2536 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2537 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2538 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2539 RAISE OKL_API.G_EXCEPTION_ERROR;
2540 END IF;
2541
2542 -- start: cklee -- add additional columns 3/19/07
2543 additional_tld_attr(
2544 p_api_version => p_api_version,
2545 p_init_msg_list => p_init_msg_list,
2546 x_return_status => l_return_status,
2547 x_msg_count => x_msg_count,
2548 x_msg_data => x_msg_data,
2549 p_tldv_rec => lp_tldv_tbl(l_tld_loop_cnt),
2550 x_tldv_rec => lx_tldv_tbl(l_tld_loop_cnt));
2551
2552 lp_tldv_tbl(l_tld_loop_cnt) := lx_tldv_tbl(l_tld_loop_cnt);
2553
2554 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2555 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2556 x_return_status := l_return_status;
2557 END IF;
2558 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2559 END IF;
2560 -- end: cklee -- add additional columns 3/19/07
2561
2562 okl_tld_pvt.insert_row(
2563 p_api_version => p_api_version,
2564 p_init_msg_list => p_init_msg_list,
2565 x_return_status => l_return_status,
2566 x_msg_count => x_msg_count,
2567 x_msg_data => x_msg_data,
2568 p_tldv_rec => lp_tldv_tbl(l_tld_loop_cnt),
2569 x_tldv_rec => lx_tldv_rec);
2570 -- Assign attributes back to lx_tldv_rec along with ID;
2571 -- Error handling;
2572 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2573 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2574 x_return_status := l_return_status;
2575 END IF;
2576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2577 END IF;
2578 lp_tldv_tbl(l_tld_loop_cnt) := lx_tldv_rec;
2579
2580 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2581 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tldv_rec.id: '||to_char(lx_tldv_rec.id));
2582 END IF;
2583
2584 -- end if;
2585 End If;
2586 EXIT WHEN l_tld_loop_cnt = lp_tldv_tbl.LAST;
2587 l_tld_loop_cnt := lp_tldv_tbl.NEXT(l_tld_loop_cnt);
2588 -- End loop;
2589 end loop;
2590
2591 -- 5. End loop;
2592 EXIT WHEN l_til_loop_cnt = lp_tilv_tbl.LAST;
2593 l_til_loop_cnt := lp_tilv_tbl.NEXT(l_til_loop_cnt);
2594 end loop;
2595
2596 x_taiv_rec := lx_taiv_rec;
2597 x_tilv_tbl := lp_tilv_tbl;
2598 x_tldv_tbl := lp_tldv_tbl;
2599
2600 --gkhuntet start
2601 create_accounting_dist(p_api_version => p_api_version ,
2602 p_init_msg_list => p_init_msg_list ,
2603 x_return_status => l_return_status ,
2604 x_msg_count => x_msg_count ,
2605 x_msg_data => x_msg_data ,
2606 p_tldv_tbl => lp_tldv_tbl ,
2607 p_tai_id => lx_taiv_rec.ID);
2608
2609 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2610 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2611 x_return_status := l_return_status;
2612 END IF;
2613 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2614 END IF;
2615 --gkhuntet end.
2616
2617 /*-- Increase the counter;
2618 l_tld_loop_cnt := 0;
2619 -- 6. Process accounting distributions;
2620 l_tld_loop_cnt := lp_tldv_tbl.first;
2621
2622 loop
2623 --FOR l_tld_loop_cnt in 1 .. lp_tldv_tbl.count loop
2624 p_bpd_acc_rec.id := lp_tldv_tbl(l_tld_loop_cnt).id;
2625 p_bpd_acc_rec.source_table := 'OKL_TXD_AR_LN_DTLS_B';
2626
2627
2628 /* apaul -- Code commented out because new accing API uptake not complete
2629 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(p_api_version => p_api_version,
2630 p_init_msg_list => p_init_msg_list,
2631 x_return_status => l_return_status,
2632 x_msg_count => x_msg_count,
2633 x_msg_data => x_msg_data,
2634 p_bpd_acc_rec => p_bpd_acc_rec);
2635
2636 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2637 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2638 x_return_status := l_return_status;
2639 END IF;
2640 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2641 END IF;
2642
2643 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2644 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Okl_Acc_Call_Pub.CREATE_ACC_TRANS:p_bpd_acc_rec.id: '||to_char(p_bpd_acc_rec.id));
2645 END IF;
2646
2647 */
2648
2649
2650 ---- Added by Vpanwar --- Code for new accounting API uptake
2651 /*
2652 Okl_Acc_Call_Pub.CREATE_ACC_TRANS_NEW(p_api_version => p_api_version,
2653 p_init_msg_list => p_init_msg_list,
2654 x_return_status => l_return_status,
2655 x_msg_count => x_msg_count,
2656 x_msg_data => x_msg_data,
2657 p_bpd_acc_rec => p_bpd_acc_rec,
2658 x_tmpl_identify_rec => l_tmpl_identify_rec,
2659 x_dist_info_rec => l_dist_info_rec,
2660 x_ctxt_val_tbl => l_ctxt_val_tbl,
2661 x_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
2662
2663 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2664 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2665 x_return_status := l_return_status;
2666 END IF;
2667 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2668 END IF;
2669
2670
2671 --- populate the tables for passing to Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST
2672
2673 l_acc_gen_tbl(l_tld_loop_cnt).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
2674 l_acc_gen_tbl(l_tld_loop_cnt).source_id := l_dist_info_rec.source_id;
2675
2676 l_ctxt_tbl(l_tld_loop_cnt).ctxt_val_tbl := l_ctxt_val_tbl;
2677 l_ctxt_tbl(l_tld_loop_cnt).source_id := l_dist_info_rec.source_id;
2678
2679 l_tmpl_identify_tbl(l_tld_loop_cnt) := l_tmpl_identify_rec;
2680
2681 l_dist_info_tbl(l_tld_loop_cnt) := l_dist_info_rec;
2682
2683 ---- End Added by Vpanwar --- Code for new accounting API uptake
2684
2685
2686 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2687 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Okl_Acc_Call_Pub.CREATE_ACC_TRANS_NEW:p_bpd_acc_rec.id: '||to_char(p_bpd_acc_rec.id));
2688 END IF;
2689
2690 EXIT WHEN l_tld_loop_cnt = lp_tldv_tbl.LAST;
2691 l_tld_loop_cnt := lp_tldv_tbl.NEXT(l_tld_loop_cnt);
2692 end loop;
2693
2694 ---- Added by Vpanwar --- Code for new accounting API uptake
2695 l_trx_header_tbl:= 'okl_trx_ar_invoices_b';
2696 l_trx_header_id := lx_taiv_rec.id;
2697 --Call accounting with new signature
2698 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
2699 p_api_version => p_api_version,
2700 p_init_msg_list => p_init_msg_list,
2701 x_return_status => x_return_status,
2702 x_msg_count => x_msg_count,
2703 x_msg_data => x_msg_data,
2704 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
2705 p_dist_info_tbl => l_dist_info_tbl,
2706 p_ctxt_val_tbl => l_ctxt_tbl,
2707 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
2708 x_template_tbl => l_template_out_tbl,
2709 x_amount_tbl => l_amount_out_tbl,
2710 p_trx_header_id => l_trx_header_id,
2711 p_trx_header_table => l_trx_header_tbl);
2712
2713 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2714 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2715 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2716 RAISE OKL_API.G_EXCEPTION_ERROR;
2717 END IF;
2718 */
2719 ---- End Added by Vpanwar --- Code for new accounting API uptake
2720
2721 -- Note: Refer to okl_billing_controller_pvt.bill_streams_master for details
2722 --
2723
2724 /*** End API body ******************************************************/
2725
2726 -- Get message count and if count is 1, get message info
2727 FND_MSG_PUB.Count_And_Get
2728 (p_count => x_msg_count,
2729 p_data => x_msg_data);
2730
2731 EXCEPTION
2732 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2733 ROLLBACK TO create_sel_billing_trx;
2734 x_return_status := OKL_API.G_RET_STS_ERROR;
2735 FND_MSG_PUB.Count_And_Get
2736 (p_count => x_msg_count,
2737 p_data => x_msg_data);
2738
2739 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2740 ROLLBACK TO create_sel_billing_trx;
2741 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2742 FND_MSG_PUB.Count_And_Get
2743 (p_count => x_msg_count,
2744 p_data => x_msg_data);
2745
2746 WHEN OTHERS THEN
2747 ROLLBACK TO create_sel_billing_trx;
2748 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2749 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2750 p_msg_name => G_UNEXPECTED_ERROR,
2751 p_token1 => G_SQLCODE_TOKEN,
2752 p_token1_value => SQLCODE,
2753 p_token2 => G_SQLERRM_TOKEN,
2754 p_token2_value => SQLERRM);
2755 FND_MSG_PUB.Count_And_Get
2756 (p_count => x_msg_count,
2757 p_data => x_msg_data);
2758 end create_sel_billing_trx;
2759
2760 ----------------------------------------------------------------------------------
2761 -- Start of comments
2762 --
2763 -- Procedure Name : CREATE_BILLING_TRX
2764 -- Description : wrapper api to create internal billing transactions
2765 -- Business Rules :
2766 -- Usage:
2767 -- (1) Caller pass 3 layers of billing data:
2768 -- -----------------------------------------
2769 --
2770 -- If caller pass the following parameters with data,
2771 -- ,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
2772 -- ,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
2773 -- ,p_tldv_tbl IN okl_tld_pvt.tldv_tbl_type
2774 -- then system assume caller is intend to create stream based (with stream element)
2775 -- internal billing transactions.
2776 --
2777 -- In this scenario, the following rules applied:
2778 -- R1): If p_tilv_tbl(n).TXL_AR_LINE_NUMBER exists, but p_tldv_tbl(n).TXL_AR_LINE_NUMBER
2779 -- doesn't exists, throw error.
2780 -- R2): If p_tldv_tbl(n).TXL_AR_LINE_NUMBER exists, but p_tilv_tbl(n).TXL_AR_LINE_NUMBER
2781 -- doesn't exists, throw error.
2782 --
2783 -- Note:
2784 -- p_tilv_tbl(n).TXL_AR_LINE_NUMBER :
2785 -- User key to link between p_tilv_rec and p_tldv_tbl
2786 --
2787 -- p_tldv_tbl(n).TXL_AR_LINE_NUMBER :
2788 -- User key to link between p_tldv_rec and p_tilv_rec
2789 --
2790 -- Note: In order to process this API properly, you need to pass user enter TXL_AR_LINE_NUMBER
2791 -- to link between p_tilv_rec and p_tldv_tbl.
2792 --
2793 -- (2) Caller pass 2 layers of billing data:
2794 -- -----------------------------------------
2795 --
2796 -- If caller pass the following parameters with data,
2797 -- ,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
2798 -- ,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
2799 -- then system assume caller is intend to create non-stream based (without stream element)
2800 -- internal billing transactions.
2801 --
2802 -- In this scenario, p_tilv_tbl(n).TXL_AR_LINE_NUMBER is not a required attribute.
2803 -- If user does pass p_tilv_tbl(n).TXL_AR_LINE_NUMBER, system will assume this is a
2804 -- redundant data.
2805 -- System will copy the major attributes (STY_ID, AMOUNT, etc) from p_tilv_rec to
2806 -- create record in OKL_TXD_AR_LN_DTLS_b/tl table (Internal billing invoice/invoce line)
2807 --
2808 -- (3) Caller pass 1 layer of billing data:
2809 -- -----------------------------------------
2810 -- If p_tilv_tbl.count = 0, throw error.
2811 --
2812 -- Note: 1. Assume all calling API will validate attributes before make the call. This is
2813 -- the current architecture and we will adopt all validation logic from calling API
2814 -- to this central API in the future.
2815 -- Parameters :
2816 --
2817 -- p_taiv_rec: Internal billing contract transaction header (okl_trx_ar_invoices_v)
2818 -- p_tilv_tbl: Internal billing contract transaction line (OKL_TXL_AR_INV_LNS_V)
2819 -- p_tldv_tbl: Internal billing invoice/invoce line (OKL_TXD_AR_LN_DTLS_V)
2820 -- Version : 1.0
2821 -- End of comments
2822 ----------------------------------------------------------------------------------
2823 PROCEDURE create_billing_trx(
2824 p_api_version IN NUMBER
2825 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2826 ,x_return_status OUT NOCOPY VARCHAR2
2827 ,x_msg_count OUT NOCOPY NUMBER
2828 ,x_msg_data OUT NOCOPY VARCHAR2
2829 ,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
2830 ,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
2831 ,p_tldv_tbl IN okl_tld_pvt.tldv_tbl_type
2832 ,x_taiv_rec OUT NOCOPY okl_tai_pvt.taiv_rec_type
2833 ,x_tilv_tbl OUT NOCOPY okl_til_pvt.tilv_tbl_type
2834 ,x_tldv_tbl OUT NOCOPY okl_tld_pvt.tldv_tbl_type
2835 )
2836 is
2837 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_BILLING_TRX';
2838 l_api_version CONSTANT NUMBER := 1.0;
2839 l_til_loop_cnt NUMBER;
2840 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2841 lp_taiv_rec okl_tai_pvt.taiv_rec_type := p_taiv_rec;
2842 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
2843 lp_tilv_tbl okl_til_pvt.tilv_tbl_type := p_tilv_tbl;
2844 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
2845 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type := p_tldv_tbl;
2846 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
2847 l_taiv_id NUMBER;
2848 lx_tilv_rec okl_til_pvt.tilv_rec_type;
2849 l_tld_loop_cnt NUMBER;
2850 l_til_ln_number NUMBER;
2851 lx_tldv_rec okl_tld_pvt.tldv_rec_type;
2852 l_til_id NUMBER;
2853 l_til_debug_cnt NUMBER;
2854 l_tld_debug_cnt NUMBER;
2855 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
2856 begin
2857 -- Set API savepoint
2858 SAVEPOINT CREATE_BILLING_TRX;
2859 IF (G_DEBUG_ENABLED = 'Y') THEN
2860 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2861 END IF;
2862 --Print Input Variables
2863 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2864 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Taiv Parameters '||' Currency Code :'||p_taiv_rec.currency_code||' Currency conversion type :'||p_taiv_rec.currency_conversion_type);
2865 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Currency conversion rate :'||p_taiv_rec.currency_conversion_rate||' Currency conversion date :'||p_taiv_rec.currency_conversion_date);
2866
2867 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'tilv Parameters');
2868 IF (p_tilv_tbl.count > 0) THEN -- 6402950
2869 l_til_debug_cnt := p_tilv_tbl.first;
2870 loop
2871 --for l_til_debug_cnt in 1 .. p_tilv_tbl.count loop
2872 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inv receiv line code :'||p_tilv_tbl(l_til_debug_cnt).inv_receiv_line_code);
2873 EXIT WHEN l_til_debug_cnt = p_tilv_tbl.LAST; -- 6402950
2874 l_til_debug_cnt := p_tilv_tbl.NEXT(l_til_debug_cnt);
2875 end loop;
2876 END IF;
2877
2878 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'tldv Parameters');
2879 IF (p_tldv_tbl.count > 0) THEN -- 6402950
2880 l_tld_debug_cnt := lp_tldv_tbl.first; -- 6402950
2881 loop
2882 --FOR l_tld_debug_cnt in 1 .. p_tldv_tbl.count LOOP
2883 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'TXL AR LINE NUMBER :'||p_tldv_tbl(l_tld_debug_cnt).TXL_AR_LINE_NUMBER);
2884 EXIT WHEN l_tld_debug_cnt = lp_tldv_tbl.LAST;
2885 l_tld_debug_cnt := lp_tldv_tbl.NEXT(l_tld_debug_cnt);
2886 END LOOP;
2887 END IF;
2888 END IF;
2889 -- Check for call compatibility
2890 IF (NOT FND_API.Compatible_API_Call (l_api_version,
2891 p_api_version,
2892 l_api_name,
2893 G_PKG_NAME ))
2894 THEN
2895 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2896 END IF;
2897
2898 -- Initialize message list if requested
2899 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2900 FND_MSG_PUB.initialize;
2901 END IF;
2902
2903 -- Initialize API status to success
2904 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2905
2906
2907 /*** Begin API body ****************************************************/
2908
2909 --
2910 -- 1. Validation
2911 l_return_status := validate_attributes(p_taiv_rec => lp_taiv_rec,
2912 p_tilv_tbl => lp_tilv_tbl,
2913 p_tldv_tbl => lp_tldv_tbl);
2914
2915 --- Store the highest degree of error
2916 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2917 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2918 x_return_status := l_return_status;
2919 END IF;
2920 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2921 END IF;
2922
2923 IF G_IS_STREAM_BASED_BILLING = TRUE THEN
2924
2925 create_sel_billing_trx(
2926 p_api_version => p_api_version,
2927 p_init_msg_list => p_init_msg_list,
2928 x_return_status => l_return_status,
2929 x_msg_count => x_msg_count,
2930 x_msg_data => x_msg_data,
2931 p_taiv_rec => lp_taiv_rec,
2932 p_tilv_tbl => lp_tilv_tbl,
2933 p_tldv_tbl => lp_tldv_tbl,
2934 x_taiv_rec => lx_taiv_rec,
2935 x_tilv_tbl => lx_tilv_tbl,
2936 x_tldv_tbl => lx_tldv_tbl);
2937
2938 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2939 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2940 x_return_status := l_return_status;
2941 END IF;
2942 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2943 END IF;
2944
2945 ELSE
2946
2947 create_non_sel_billing_trx(
2948 p_api_version => p_api_version,
2949 p_init_msg_list => p_init_msg_list,
2950 x_return_status => l_return_status,
2951 x_msg_count => x_msg_count,
2952 x_msg_data => x_msg_data,
2953 p_taiv_rec => lp_taiv_rec,
2954 p_tilv_tbl => lp_tilv_tbl,
2955 x_taiv_rec => lx_taiv_rec,
2956 -- start: cklee -- fixed return parameters issues 4/6/07
2957 x_tilv_tbl => lx_tilv_tbl,
2958 x_tldv_tbl => lx_tldv_tbl);
2959 -- end: cklee -- fixed return parameters issues 4/6/07
2960
2961 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2962 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2963 x_return_status := l_return_status;
2964 END IF;
2965 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2966 END IF;
2967
2968 END IF;
2969
2970 -- start: cklee -- fixed return parameters issues 4/6/07
2971 -- Assign to out parametrs
2972 x_taiv_rec := lx_taiv_rec;
2973 x_tilv_tbl := lx_tilv_tbl;
2974 x_tldv_tbl := lx_tldv_tbl;
2975 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2976 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'x_taiv_rec.id: '||to_char(x_taiv_rec.id));
2977 --OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'x_tilv_tbl(1).id: '||to_char(x_tilv_tbl(1).id));
2978 --OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'x_tldv_tbl(1).id: '||to_char(x_tldv_tbl(1).id));
2979 END IF;
2980 -- end: cklee -- fixed return parameters issues 4/6/07
2981
2982 /*** End API body ******************************************************/
2983
2984 -- Get message count and if count is 1, get message info
2985 FND_MSG_PUB.Count_And_Get
2986 (p_count => x_msg_count,
2987 p_data => x_msg_data);
2988
2989 EXCEPTION
2990 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2991 ROLLBACK TO CREATE_BILLING_TRX;
2992 x_return_status := OKL_API.G_RET_STS_ERROR;
2993 FND_MSG_PUB.Count_And_Get
2994 (p_count => x_msg_count,
2995 p_data => x_msg_data);
2996
2997 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2998 ROLLBACK TO CREATE_BILLING_TRX;
2999 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3000 FND_MSG_PUB.Count_And_Get
3001 (p_count => x_msg_count,
3002 p_data => x_msg_data);
3003
3004 WHEN OTHERS THEN
3005 ROLLBACK TO CREATE_BILLING_TRX;
3006 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3007 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3008 p_msg_name => G_UNEXPECTED_ERROR,
3009 p_token1 => G_SQLCODE_TOKEN,
3010 p_token1_value => SQLCODE,
3011 p_token2 => G_SQLERRM_TOKEN,
3012 p_token2_value => SQLERRM);
3013 FND_MSG_PUB.Count_And_Get
3014 (p_count => x_msg_count,
3015 p_data => x_msg_data);
3016
3017 end CREATE_BILLING_TRX;
3018
3019 ----------------------------------------------------------------------------------
3020 -- Start of comments
3021 --
3022 -- Procedure Name : Get_Invoice_format
3023 -- Description : wrapper api to retrieve OKL invoice format type and
3024 -- invoice format line type
3025 -- Business Rules :
3026 -- 1. If passed in inf_id and sty_id matches, get the invoice_format_type and
3027 -- invoice format line type
3028 -- 2. If passed in inf_id matches, but stream is missing, get the defaulted
3029 -- invoice_format_type and invoice format line type
3030 -- 3 If passed in inf_id and sty_id are null, assign null to the
3031 -- invoice_format_type and invoice format line type
3032 -- Parameters :
3033 --
3034 -- Version : 1.0
3035 -- End of comments
3036 ----------------------------------------------------------------------------------
3037 PROCEDURE Get_Invoice_format(
3038 p_api_version IN NUMBER
3039 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3040 ,x_return_status OUT NOCOPY VARCHAR2
3041 ,x_msg_count OUT NOCOPY NUMBER
3042 ,x_msg_data OUT NOCOPY VARCHAR2
3043 ,p_inf_id IN NUMBER DEFAULT NULL
3044 ,p_sty_id IN NUMBER DEFAULT NULL
3045 ,x_invoice_format_type OUT NOCOPY VARCHAR2
3046 ,x_invoice_format_line_type OUT NOCOPY VARCHAR2
3047 )
3048 is
3049 l_api_name CONSTANT VARCHAR2(30) := 'Get_Invoice_format';
3050 l_api_version CONSTANT NUMBER := 1.0;
3051 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3052
3053 CURSOR inv_format_csr ( p_format_id IN NUMBER, p_stream_id IN NUMBER ) IS
3054 SELECT
3055 ity.name ity_name,
3056 ilt.name ilt_name
3057 FROM okl_invoice_types_v ity,
3058 okl_invc_line_types_v ilt,
3059 okl_invc_frmt_strms_v frs,
3060 okl_strm_type_v sty
3061 WHERE ity.inf_id = p_format_id
3062 AND ilt.ity_id = ity.id
3063 AND frs.ilt_id = ilt.id
3064 AND sty.id = frs.sty_id
3065 AND frs.sty_id = p_stream_id;
3066
3067 CURSOR inv_format_default_csr ( p_format_id IN NUMBER ) IS
3068 SELECT
3069 ity.name ity_name,
3070 ilt.name ilt_name
3071 FROM okl_invoice_types_v ity,
3072 okl_invc_line_types_v ilt
3073 WHERE ity.inf_id = p_format_id
3074 AND ilt.ity_id = ity.id;
3075
3076 begin
3077 -- Set API savepoint
3078 SAVEPOINT Get_Invoice_format;
3079 IF (G_DEBUG_ENABLED = 'Y') THEN
3080 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3081 END IF;
3082 --Print Input Variables
3083 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3084 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_inf_id :'||p_inf_id);
3085 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_sty_id :'||p_sty_id);
3086
3087 END IF;
3088 -- Check for call compatibility
3089 IF (NOT FND_API.Compatible_API_Call (l_api_version,
3090 p_api_version,
3091 l_api_name,
3092 G_PKG_NAME ))
3093 THEN
3094 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3095 END IF;
3096
3097 -- Initialize message list if requested
3098 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3099 FND_MSG_PUB.initialize;
3100 END IF;
3101
3102 -- Initialize API status to success
3103 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3104
3105
3106 /*** Begin API body ****************************************************/
3107
3108 IF p_inf_id IS NOT NULL and p_sty_id IS NOT NULL THEN
3109
3110 OPEN inv_format_csr ( p_inf_id, p_sty_id);
3111 --start:| 08-Mar-2005 cklee -- Fixed Get_Invoice_format logic error |
3112 FETCH inv_format_csr INTO x_invoice_format_type, x_invoice_format_line_type;
3113 --end:| 08-Mar-2005 cklee -- Fixed Get_Invoice_format logic error |
3114 CLOSE inv_format_csr;
3115
3116 ELSIF p_inf_id IS NOT NULL and p_sty_id IS NULL THEN
3117
3118 OPEN inv_format_default_csr ( p_inf_id);
3119 --start:| 08-Mar-2005 cklee -- Fixed Get_Invoice_format logic error |
3120 FETCH inv_format_default_csr INTO x_invoice_format_type, x_invoice_format_line_type;
3121 --endt:| 08-Mar-2005 cklee -- Fixed Get_Invoice_format logic error |
3122 CLOSE inv_format_default_csr;
3123
3124 ELSE
3125
3126 x_invoice_format_type := NULL;
3127 x_invoice_format_line_type := NULL;
3128
3129 END IF;
3130
3131 /*** End API body ******************************************************/
3132
3133 -- Get message count and if count is 1, get message info
3134 FND_MSG_PUB.Count_And_Get
3135 (p_count => x_msg_count,
3136 p_data => x_msg_data);
3137
3138 EXCEPTION
3139 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3140 ROLLBACK TO Get_Invoice_format;
3141 x_return_status := OKL_API.G_RET_STS_ERROR;
3142 FND_MSG_PUB.Count_And_Get
3143 (p_count => x_msg_count,
3144 p_data => x_msg_data);
3145
3146 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3147 ROLLBACK TO Get_Invoice_format;
3148 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3149 FND_MSG_PUB.Count_And_Get
3150 (p_count => x_msg_count,
3151 p_data => x_msg_data);
3152
3153 WHEN OTHERS THEN
3154 ROLLBACK TO Get_Invoice_format;
3155 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3156 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3157 p_msg_name => G_UNEXPECTED_ERROR,
3158 p_token1 => G_SQLCODE_TOKEN,
3159 p_token1_value => SQLCODE,
3160 p_token2 => G_SQLERRM_TOKEN,
3161 p_token2_value => SQLERRM);
3162 FND_MSG_PUB.Count_And_Get
3163 (p_count => x_msg_count,
3164 p_data => x_msg_data);
3165
3166 end Get_Invoice_format;
3167
3168
3169
3170 -- Start of comments
3171
3172 -- API name : update_manual_invoice
3173 -- Pre-reqs : None
3174 -- Function : It is Used to Update header in TAI and Insert/Update line
3175 -- in TIL/TLD. And if the trx_status_code is submitted then
3176 -- make a accounting call for all TLD records.
3177 -- Parameters :
3178 -- IN : p_api_version - Standard input parameter
3179 -- p_init_msg_list - Standard input parameter
3180 -- p_taiv_rec - Record type for OKL_TRX_AR_INVOICES_B.
3181 -- p_tilv_tbl -- Table type for OKL_TXL_AR_INV_LNS_B.
3182 -- Version : 1.0
3183 -- History : gkhuntet created.
3184 -- End of comments
3185
3186 PROCEDURE update_manual_invoice(
3187 p_api_version IN NUMBER
3188 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3189 ,x_return_status OUT NOCOPY VARCHAR2
3190 ,x_msg_count OUT NOCOPY NUMBER
3191 ,x_msg_data OUT NOCOPY VARCHAR2
3192 ,p_taiv_rec IN okl_tai_pvt.taiv_rec_type
3193 ,p_tilv_tbl IN okl_til_pvt.tilv_tbl_type
3194 ,x_taiv_rec OUT NOCOPY okl_tai_pvt.taiv_rec_type
3195 ,x_tilv_tbl OUT NOCOPY okl_til_pvt.tilv_tbl_type
3196 ,x_tldv_tbl OUT NOCOPY okl_tld_pvt.tldv_tbl_type
3197 )
3198 IS
3199
3200 l_api_name CONSTANT VARCHAR2(30) := 'update_manual_invoice';
3201 l_api_version CONSTANT NUMBER := 1.0;
3202 l_til_loop_cnt NUMBER;
3203 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3204 lp_taiv_rec okl_tai_pvt.taiv_rec_type := p_taiv_rec;
3205 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
3206 lp_tilv_tbl okl_til_pvt.tilv_tbl_type := p_tilv_tbl;
3207 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
3208 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type;
3209 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
3210 l_taiv_id NUMBER;
3211 lx_tilv_rec okl_til_pvt.tilv_rec_type;
3212 l_tld_loop_cnt NUMBER;
3213 l_til_ln_number NUMBER;
3214 lx_tldv_rec okl_tld_pvt.tldv_rec_type;
3215 l_til_id NUMBER;
3216 til_id NUMBER;
3217 i NUMBER DEFAULT 0;
3218 crt_count NUMBER := 0;
3219 updt_count NUMBER := 0;
3220 l_tilv_Updt_tbl okl_til_pvt.tilv_tbl_type;
3221 l_tilv_Crt_tbl okl_til_pvt.tilv_tbl_type;
3222 l_flag_acc_call VARCHAR2(5);
3223
3224 CURSOR get_tld_csr(p_til_id_details okl_txd_ar_ln_dtls_b.TIL_ID_DETAILS%TYPE) IS
3225 SELECT ID FROM OKL_TXD_AR_LN_DTLS_B
3226 WHERE TIL_ID_DETAILS = p_til_id_details;
3227
3228
3229
3230
3231 BEGIN
3232
3233 SAVEPOINT UPDATE_MANUAL_INVOICE;
3234 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3235 x_return_status := OKL_API.START_ACTIVITY( l_api_name
3236 , g_pkg_name
3237 , p_init_msg_list
3238 , l_api_version
3239 , p_api_version
3240 , '_PVT'
3241 , x_return_status);
3242 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3243 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3244 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3245 RAISE OKL_API.G_EXCEPTION_ERROR;
3246 END IF;
3247
3248 --Validating the TAI Attributes.
3249 validate_tai_values(
3250 p_taiv_rec => lp_taiv_rec,
3251 x_return_status => l_return_status);
3252
3253 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3254 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3255 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3256 RAISE OKL_API.G_EXCEPTION_ERROR;
3257 END IF;
3258 --Updating the TAI table.
3259 okl_tai_pvt.update_row(
3260 p_api_version => p_api_version,
3261 p_init_msg_list => p_init_msg_list,
3262 x_return_status => l_return_status,
3263 x_msg_count => x_msg_count,
3264 x_msg_data => x_msg_data,
3265 p_taiv_rec => lp_taiv_rec,
3266 x_taiv_rec => lx_taiv_rec);
3267
3268 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3269 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3270 x_return_status := l_return_status;
3271 END IF;
3272 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3273 END IF;
3274
3275 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3276 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_taiv_rec.id: '||to_char(lx_taiv_rec.id));
3277 END IF;
3278 x_taiv_rec := lx_taiv_rec;
3279 -- Delete TIL record and correspondingly its TLD record which is not coming in p_tilv_tbl.
3280
3281
3282 --TO filter the insert and update record from the the lp_tilv_tbl.
3283 FOR i IN lp_tilv_tbl.FIRST .. lp_tilv_tbl.LAST LOOP
3284 IF (lp_tilv_tbl(i).id = OKL_API.G_MISS_NUM or
3285 lp_tilv_tbl(i).id IS NULL) THEN
3286 l_tilv_Crt_tbl(crt_count) := lp_tilv_tbl(i);
3287 crt_count := crt_count + 1;
3288 ELSE
3289 l_tilv_Updt_tbl(updt_count) := lp_tilv_tbl(i);
3290 updt_count := updt_count + 1;
3291 END IF;
3292 END LOOP;
3293
3294 /***** Update the TIL records and correspondingly its TLD record. *****/
3295 FOR i IN l_tilv_Updt_tbl.FIRST .. l_tilv_Updt_tbl.LAST LOOP
3296
3297 okl_til_pvt.update_row(
3298 p_api_version => p_api_version ,
3299 p_init_msg_list => p_init_msg_list,
3300 x_return_status => l_return_status,
3301 x_msg_count => x_msg_count,
3302 x_msg_data => x_msg_data,
3303 p_tilv_rec => l_tilv_Updt_tbl(i),
3304 x_tilv_rec => lx_tilv_rec);
3305
3306 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3307 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3308 x_return_status := l_return_status;
3309 END IF;
3310 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3311 END IF;
3312
3313 --Run the Cursor that query record from the TLD on the basis l_tilv_Updt_tbl(i).id
3314 -- Which is TIL_ID_Details in the TLD table.
3315 OPEN get_tld_csr(l_tilv_Updt_tbl(i).ID);
3316 FETCH get_tld_csr INTO til_id;
3317 CLOSE get_tld_csr;
3318
3319 /***
3320 -- Developer Note:
3321 -- 1. For each TIL record, copy STY_ID, AMOUNT, ORG_ID, INVENTORY_ORG_ID, INVENTORY_ITEM_ID
3322 -- to TLD pl/sql record and call okl_tld_pvt.insert_row() to create TLD.
3323 -- 2. lx_tilv_rec.TXL_AR_LINE_NUMBER is not required for this procesdure
3324 ***/
3325 lp_tldv_tbl(i).ID := til_id;
3326 lp_tldv_tbl(i).STY_ID := lx_tilv_rec.STY_ID;
3327 lp_tldv_tbl(i).AMOUNT := lx_tilv_rec.AMOUNT; -- this is 2 level, so we need to copy to tld
3328 lp_tldv_tbl(i).ORG_ID := lx_tilv_rec.ORG_ID;
3329 lp_tldv_tbl(i).INVENTORY_ORG_ID := lx_tilv_rec.INVENTORY_ORG_ID;
3330 lp_tldv_tbl(i).INVENTORY_ITEM_ID := lx_tilv_rec.INVENTORY_ITEM_ID;
3331 --lp_tldv_tbl(i).LINE_DETAIL_NUMBER := l_til_loop_cnt;
3332 lp_tldv_tbl(i).KHR_ID := lp_taiv_rec.KHR_ID;
3333 lp_tldv_tbl(i).KLE_ID := lx_tilv_rec.KLE_ID;
3334
3335 okl_tld_pvt.update_row(
3336 p_api_version => p_api_version,
3337 p_init_msg_list => p_init_msg_list,
3338 x_return_status => l_return_status,
3339 x_msg_count => x_msg_count,
3340 x_msg_data => x_msg_data,
3341 p_tldv_rec => lp_tldv_tbl(i),
3342 x_tldv_rec => lx_tldv_rec);
3343
3344 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3345 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3346 x_return_status := l_return_status;
3347 END IF;
3348 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3349 END IF;
3350 END LOOP;
3351 /***** Updation Completed in TIL and TLD *****/
3352
3353
3354 l_flag_acc_call := 'Y';
3355 IF(lx_taiv_rec.okl_source_billing_trx = G_MANUAL
3356 AND lx_taiv_rec. trx_status_code <> 'SUBMITTED') THEN
3357 l_flag_acc_call := 'N';
3358 END IF;
3359
3360
3361
3362 -- Make Accounting call for all Updated records if the trx_status_code != 'SUBMITTED'
3363 IF(lp_tldv_tbl.COUNT > 0 ) THEN
3364 IF(l_flag_acc_call = 'Y') THEN
3365 create_accounting_dist(p_api_version => p_api_version ,
3366 p_init_msg_list => p_init_msg_list ,
3367 x_return_status => l_return_status ,
3368 x_msg_count => x_msg_count ,
3369 x_msg_data => x_msg_data ,
3370 p_tldv_tbl => lp_tldv_tbl ,
3371 p_tai_id => lx_taiv_rec.ID
3372 );
3373 END IF;
3374 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3375 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3376 x_return_status := l_return_status;
3377 END IF;
3378 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3379 END IF;
3380 END IF;
3381
3382
3383
3384 /***** Insert into TIL records and correspondingly its TLD record. *****/
3385
3386 lp_tldv_tbl.delete;
3387
3388 IF(l_tilv_Crt_tbl.COUNT > 0) THEN
3389 FOR i IN l_tilv_Crt_tbl.FIRST .. l_tilv_Crt_tbl.LAST LOOP
3390 -- Assign lx_taiv_rec.ID to lp_til_rec.TAI_ID;
3391 l_tilv_Crt_tbl(i).TAI_ID := lx_taiv_rec.ID;
3392 l_tilv_Crt_tbl(i).ORG_ID := lx_taiv_rec.org_id;
3393
3394 additional_til_attr(
3395 p_api_version => p_api_version,
3396 p_init_msg_list => p_init_msg_list,
3397 x_return_status => l_return_status,
3398 x_msg_count => x_msg_count,
3399 x_msg_data => x_msg_data,
3400 p_tilv_rec => l_tilv_Crt_tbl(i),
3401 x_tilv_rec => lx_tilv_tbl(i));
3402
3403 l_tilv_Crt_tbl(i) := lx_tilv_tbl(i);
3404
3405 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3406 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3407 x_return_status := l_return_status;
3408 END IF;
3409 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3410 END IF;
3411
3412
3413 -- Create okl_TXL_AR_INV_LNS_B record: okl_til_pvt.insert_row;
3414 okl_til_pvt.insert_row(
3415 p_api_version => p_api_version,
3416 p_init_msg_list => p_init_msg_list,
3417 x_return_status => l_return_status,
3418 x_msg_count => x_msg_count,
3419 x_msg_data => x_msg_data,
3420 p_tilv_rec => l_tilv_Crt_tbl(i),
3421 x_tilv_rec => lx_tilv_rec);
3422 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3423 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3424 x_return_status := l_return_status;
3425 END IF;
3426 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3427 END IF;
3428
3429 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3430 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tilv_rec.id: '||to_char(lx_tilv_rec.id));
3431 END IF;
3432
3433 -- l_til_ln_number := lx_tilv_rec.TXL_AR_LINE_NUMBER;
3434 l_til_id := lx_tilv_rec.id;
3435 /***
3436 -- Developer Note:
3437 -- 1. For each TIL record, copy STY_ID, AMOUNT, ORG_ID, INVENTORY_ORG_ID, INVENTORY_ITEM_ID
3438 -- to TLD pl/sql record and call okl_tld_pvt.insert_row() to create TLD.
3439 -- 2. lx_tilv_rec.TXL_AR_LINE_NUMBER is not required for this procesdure
3440 --
3441 ***/
3442 lp_tldv_tbl(i).TIL_ID_DETAILS := l_til_id;
3443 lp_tldv_tbl(i).STY_ID := lx_tilv_rec.STY_ID;
3444 lp_tldv_tbl(i).AMOUNT := lx_tilv_rec.AMOUNT; -- this is 2 level, so we need to copy to tld
3445 lp_tldv_tbl(i).ORG_ID := lx_tilv_rec.ORG_ID;
3446 lp_tldv_tbl(i).INVENTORY_ORG_ID := lx_tilv_rec.INVENTORY_ORG_ID;
3447 lp_tldv_tbl(i).INVENTORY_ITEM_ID := lx_tilv_rec.INVENTORY_ITEM_ID;
3448 lp_tldv_tbl(i).LINE_DETAIL_NUMBER := i;
3449 lp_tldv_tbl(i).KHR_ID := lx_taiv_rec.KHR_ID;
3450 lp_tldv_tbl(i).KLE_ID := lx_tilv_rec.KLE_ID;
3451
3452 Get_Invoice_format(
3453 p_api_version => p_api_version
3454 ,p_init_msg_list => OKL_API.G_FALSE
3455 ,x_return_status => l_return_status
3456 ,x_msg_count => x_msg_count
3457 ,x_msg_data => x_msg_data
3458 ,p_inf_id => lp_taiv_rec.inf_id
3459 ,p_sty_id => lp_tldv_tbl(i).STY_ID
3460 ,x_invoice_format_type => lp_tldv_tbl(i).invoice_format_type
3461 ,x_invoice_format_line_type => lp_tldv_tbl(i).invoice_format_line_type);
3462
3463 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3464 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3465 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3466 RAISE OKL_API.G_EXCEPTION_ERROR;
3467 END IF;
3468
3469 additional_tld_attr(
3470 p_api_version => p_api_version,
3471 p_init_msg_list => p_init_msg_list,
3472 x_return_status => l_return_status,
3473 x_msg_count => x_msg_count,
3474 x_msg_data => x_msg_data,
3475 p_tldv_rec => lp_tldv_tbl(i),
3476 x_tldv_rec => lx_tldv_tbl(i));
3477
3478 lp_tldv_tbl(i) := lx_tldv_tbl(i);
3479
3480 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3481 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3482 x_return_status := l_return_status;
3483 END IF;
3484 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3485 END IF;
3486
3487 okl_tld_pvt.insert_row(
3488 p_api_version => p_api_version,
3489 p_init_msg_list => p_init_msg_list,
3490 x_return_status => l_return_status,
3491 x_msg_count => x_msg_count,
3492 x_msg_data => x_msg_data,
3493 p_tldv_rec => lp_tldv_tbl(i),
3494 x_tldv_rec => lx_tldv_rec);
3495
3496 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3497 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3498 x_return_status := l_return_status;
3499 END IF;
3500 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3501 END IF;
3502 -- lp_tldv_tbl(l_til_loop_cnt) := lx_tldv_rec;
3503
3504 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3505 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tldv_rec.id: '||to_char(lx_tldv_rec.id));
3506 END IF;
3507
3508 END LOOP;
3509 END IF;
3510 /***** Insertion completed in TIL and TLD *****/
3511
3512 l_flag_acc_call := 'Y';
3513 IF(lx_taiv_rec.okl_source_billing_trx = G_MANUAL
3514 AND lx_taiv_rec. trx_status_code <> 'SUBMITTED') THEN
3515 l_flag_acc_call := 'N';
3516 END IF;
3517
3518
3519 -- Make Accounting call for all inserted records if the trx_status_code = 'SUBMITTED'
3520 IF(lp_tldv_tbl.COUNT > 0 ) THEN
3521 IF(l_flag_acc_call = 'Y') THEN
3522 create_accounting_dist(p_api_version => p_api_version ,
3523 p_init_msg_list => p_init_msg_list ,
3524 x_return_status => l_return_status ,
3525 x_msg_count => x_msg_count ,
3526 x_msg_data => x_msg_data ,
3527 p_tldv_tbl => lp_tldv_tbl ,
3528 p_tai_id => lx_taiv_rec.ID
3529 );
3530 END IF;
3531 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3532 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3533 x_return_status := l_return_status;
3534 END IF;
3535 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3536 END IF;
3537 END IF;
3538
3539
3540 -- Get message count and if count is 1, get message info
3541 FND_MSG_PUB.Count_And_Get
3542 (p_count => x_msg_count,
3543 p_data => x_msg_data);
3544
3545 EXCEPTION
3546 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3547 ROLLBACK TO UPDATE_MANUAL_INVOICE;
3548 x_return_status := OKL_API.G_RET_STS_ERROR;
3549 FND_MSG_PUB.Count_And_Get
3550 (p_count => x_msg_count,
3551 p_data => x_msg_data);
3552
3553 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3554 ROLLBACK TO UPDATE_MANUAL_INVOICE;
3555 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3556 FND_MSG_PUB.Count_And_Get
3557 (p_count => x_msg_count,
3558 p_data => x_msg_data);
3559
3560 WHEN OTHERS THEN
3561 ROLLBACK TO UPDATE_MANUAL_INVOICE;
3562 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3563 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3564 p_msg_name => G_UNEXPECTED_ERROR,
3565 p_token1 => G_SQLCODE_TOKEN,
3566 p_token1_value => SQLCODE,
3567 p_token2 => G_SQLERRM_TOKEN,
3568 p_token2_value => SQLERRM);
3569 FND_MSG_PUB.Count_And_Get
3570 (p_count => x_msg_count,
3571 p_data => x_msg_data);
3572
3573
3574 END update_manual_invoice;
3575
3576
3577
3578 -- Start of comments
3579
3580 -- API name : delete_manual_invoice
3581 -- Pre-reqs : None
3582 -- Function : It is Used to delete the TAI , TIL ,TLD records.
3583 -- Either p_taiv_id or p_tilv_id should be passed.
3584 -- If p_taiv_id is passed then delete TAI ,all TIL of TAI and all TLD of TIL records.
3585 -- And If p_tilv_id is passed then delete TIL and all TLD of TIL.
3586 -- Parameters :
3587 -- IN : p_api_version - Standard input parameter
3588 -- p_init_msg_list - Standard input parameter
3589 -- p_taiv_id - ID of OKL_TRX_AR_INVOICES_B.
3590 -- p_tilv_id -- ID of OKL_TXL_AR_INV_LNS_B.
3591 -- Version : 1.0
3592 -- History : gkhuntet created.
3593 -- End of comments
3594
3595 PROCEDURE delete_manual_invoice(
3596 p_api_version IN NUMBER
3597 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3598 ,x_return_status OUT NOCOPY VARCHAR2
3599 ,x_msg_count OUT NOCOPY NUMBER
3600 ,x_msg_data OUT NOCOPY VARCHAR2
3601 ,p_taiv_id NUMBER
3602 ,p_tilv_id NUMBER
3603 )
3604 IS
3605
3606 l_api_name CONSTANT VARCHAR2(30) := 'update_manual_invoice';
3607 l_api_version CONSTANT NUMBER := 1.0;
3608 l_til_loop_cnt NUMBER;
3609 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3610 lp_tai_id OKL_TRX_AR_INVOICES_B.ID%TYPE :=p_taiv_id;
3611 lp_til_id OKL_TXL_AR_INV_LNS_B.ID%TYPE :=p_tilv_id;
3612 l_tai_id OKL_TRX_AR_INVOICES_B.ID%TYPE;
3613 l_til_id OKL_TXL_AR_INV_LNS_B.ID%TYPE ;
3614 l_til_id OKL_TXD_AR_LN_DTLS_B.ID%TYPE ;
3615 l_taiv_rec okl_tai_pvt.taiv_rec_type;
3616 l_tilv_rec okl_til_pvt.tilv_rec_type;
3617 l_tldv_rec okl_tld_pvt.tldv_rec_type;
3618
3619 CURSOR get_til_dtl_csr IS
3620 SELECT ID til_id
3621 FROM OKL_TXL_AR_INV_LNS_B
3622 WHERE TAI_ID = p_taiv_id;
3623
3624 get_til_dtl_rec get_til_dtl_csr%rowtype;
3625
3626 CURSOR get_tld_dtl_csr (g_til_id OKL_TXL_AR_INV_LNS_B.ID%TYPE) IS
3627 SELECT ID tld_id
3628 FROM OKL_TXD_AR_LN_DTLS_B
3629 WHERE TIL_ID_DETAILS = g_til_id;
3630
3631 get_tld_dtl_rec get_tld_dtl_csr%rowtype;
3632
3633
3634
3635 BEGIN
3636 SAVEPOINT DELETE_MANUAL_INVOICE ;
3637
3638 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3639 x_return_status := OKL_API.START_ACTIVITY( l_api_name
3640 , g_pkg_name
3641 , p_init_msg_list
3642 , l_api_version
3643 , p_api_version
3644 , '_PVT'
3645 , x_return_status);
3646 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3647 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3648 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3649 RAISE OKL_API.G_EXCEPTION_ERROR;
3650 END IF;
3651
3652 -- IF lp_taiv_id is not null then delete TAI , TIL and TLD records else TIL and TLD.
3653 IF lp_tai_id IS NOT NULL THEN
3654 --Retrive all the TIL Records.
3655 FOR get_til_dtl_rec in get_til_dtl_csr
3656 LOOP
3657 l_tilv_rec.id := get_til_dtl_rec.til_id;
3658 --Retrive all the TLD Records for a l_tilv_rec.id.
3659 FOR get_tld_dtl_rec in get_tld_dtl_csr(l_tilv_rec.id )
3660 LOOP
3661 l_tldv_rec.id := get_tld_dtl_rec.tld_id;
3662 --Delete a TLD Record.
3663 OKL_TLD_PVT.delete_row(p_api_version => p_api_version,
3664 p_init_msg_list => p_init_msg_list,
3665 x_return_status => l_return_status,
3666 x_msg_count => x_msg_count,
3667 x_msg_data => x_msg_data,
3668 p_tldv_rec => l_tldv_rec
3669 );
3670 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3671 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3672 x_return_status := l_return_status;
3673 END IF;
3674 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3675 END IF;
3676
3677 END LOOP; --End loop for TLD
3678 --Delete a TIL Record.
3679 OKL_TIL_PVT.delete_row(p_api_version => p_api_version,
3680 p_init_msg_list => p_init_msg_list,
3681 x_return_status => l_return_status,
3682 x_msg_count => x_msg_count,
3683 x_msg_data => x_msg_data,
3684 p_tilv_rec => l_tilv_rec
3685 );
3686 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3687 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3688 x_return_status := l_return_status;
3689 END IF;
3690 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3691 END IF;
3692
3693 END LOOP;--End loop for TIL.
3694 --Delete a TAI Record.
3695 l_taiv_rec.id := lp_tai_id;
3696 OKL_TAI_PVT.delete_row(p_api_version => p_api_version,
3697 p_init_msg_list => p_init_msg_list,
3698 x_return_status => l_return_status,
3699 x_msg_count => x_msg_count,
3700 x_msg_data => x_msg_data,
3701 p_taiv_rec => l_taiv_rec);
3702 IF(l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3703 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3704 x_return_status := l_return_status;
3705 END IF;
3706 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3707 END IF;
3708
3709 ELSIF lp_til_id IS NOT NULL THEN -- Delete a TIL Rccord and all its TLD Records.
3710 FOR get_tld_dtl_rec in get_tld_dtl_csr(lp_til_id)
3711 LOOP
3712 l_tldv_rec.id := get_tld_dtl_rec.tld_id;
3713 --Delete a TLD Record.
3714 OKL_TLD_PVT.delete_row(p_api_version => p_api_version,
3715 p_init_msg_list => p_init_msg_list,
3716 x_return_status => l_return_status,
3717 x_msg_count => x_msg_count,
3718 x_msg_data => x_msg_data,
3719 p_tldv_rec => l_tldv_rec
3720 );
3721 IF(l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3722 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3723 x_return_status := l_return_status;
3724 END IF;
3725 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3726 END IF;
3727
3728 END LOOP; --End loop for TLD.
3729 l_tilv_rec.id := lp_til_id;
3730 --Delete a TIL Record.
3731 OKL_TIL_PVT.delete_row(p_api_version => p_api_version,
3732 p_init_msg_list => p_init_msg_list,
3733 x_return_status => l_return_status,
3734 x_msg_count => x_msg_count,
3735 x_msg_data => x_msg_data,
3736 p_tilv_rec => l_tilv_rec
3737 );
3738 IF(l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3739 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3740 x_return_status := l_return_status;
3741 END IF;
3742 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3743 END IF;
3744
3745 END IF ;
3746
3747
3748
3749 -- Get message count and if count is 1, get message info
3750 FND_MSG_PUB.Count_And_Get
3751 (p_count => x_msg_count,
3752 p_data => x_msg_data);
3753
3754 EXCEPTION
3755 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3756 ROLLBACK TO DELETE_MANUAL_INVOICE;
3757 x_return_status := OKL_API.G_RET_STS_ERROR;
3758 FND_MSG_PUB.Count_And_Get
3759 (p_count => x_msg_count,
3760 p_data => x_msg_data);
3761
3762 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3763 ROLLBACK TO DELETE_MANUAL_INVOICE;
3764 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3765 FND_MSG_PUB.Count_And_Get
3766 (p_count => x_msg_count,
3767 p_data => x_msg_data);
3768
3769 WHEN OTHERS THEN
3770 ROLLBACK TO DELETE_MANUAL_INVOICE;
3771 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3772 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3773 p_msg_name => G_UNEXPECTED_ERROR,
3774 p_token1 => G_SQLCODE_TOKEN,
3775 p_token1_value => SQLCODE,
3776 p_token2 => G_SQLERRM_TOKEN,
3777 p_token2_value => SQLERRM);
3778 FND_MSG_PUB.Count_And_Get
3779 (p_count => x_msg_count,
3780 p_data => x_msg_data);
3781
3782
3783
3784 END delete_manual_invoice;
3785
3786
3787
3788 END OKL_INTERNAL_BILLING_PVT;