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