[Home] [Help]
PACKAGE BODY: APPS.OKL_BILL_UPFRONT_TAX_PVT
Source
1 PACKAGE BODY OKL_BILL_UPFRONT_TAX_PVT AS
2 /* $Header: OKLRBUTB.pls 120.9.12010000.4 2010/04/01 19:49:22 sachandr ship $ */
3
4 -------------------------------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 -------------------------------------------------------------------------------------------------
7 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
8 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
9 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
10 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
11 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
12 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
13 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
14 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UPPERCASE_REQ';
15 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
16 ------------------------------------------------------------------------------------
17 -- GLOBAL EXCEPTION
18 ------------------------------------------------------------------------------------
19 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
20 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
21 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
22 G_API_VERSION CONSTANT NUMBER := 1.0;
23 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
24
25 G_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES';
26 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
27 G_LEVEL_PROCEDURE NUMBER;
28 G_IS_DEBUG_PROCEDURE_ON BOOLEAN;
29 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
30 G_IS_STREAM_BASED_BILLING BOOLEAN := NULL;
31
32 subtype tldv_tbl_type is okl_tld_pvt.tldv_tbl_type;
33 l_tldv_tbl tldv_tbl_type;
34 ------------------------------------------------------------------
35 -- Function GET_TRX_TYPE to extract transaction type
36 ------------------------------------------------------------------
37 FUNCTION get_trx_type
38 (p_name VARCHAR2,
39 p_language VARCHAR2)
40 RETURN NUMBER IS
41
42 CURSOR c_trx_type (cp_name VARCHAR2, cp_language VARCHAR2) IS
43 SELECT id
44 FROM OKL_TRX_TYPES_TL
45 WHERE name = cp_name
46 AND LANGUAGE = cp_language;
47
48 l_trx_type okl_trx_types_v.id%TYPE;
49
50 BEGIN
51
52 l_trx_type := NULL;
53
54 OPEN c_trx_type (p_name, p_language);
55 FETCH c_trx_type INTO l_trx_type;
56 CLOSE c_trx_type;
57
58 RETURN l_trx_type;
59
60 END get_trx_type;
61 ----------------------------------------------------------------------------------
62 -- Start of comments
63 --
64 -- Procedure Name : additional_tai_attr
65 -- Description : private procedure to populate additional hdr attr
66 -- Business Rules : populates all additional attributes for
67 -- okl_trx_ar_invoices_b
68 -- Parameters : p_taiv_rec
69 -- Version : 1.0
70 -- History : akrangan created
71 --
72 -- End Of Comments
73 ----------------------------------------------------------------------------------
74 PROCEDURE additional_tai_attr(
75 p_api_version IN NUMBER
76 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
77 ,x_return_status OUT NOCOPY VARCHAR2
78 ,x_msg_count OUT NOCOPY NUMBER
79 ,x_msg_data OUT NOCOPY VARCHAR2
80 ,p_taiv_rec IN Okl_Trx_Ar_Invoices_Pub.taiv_rec_type
81 ,x_taiv_rec OUT NOCOPY Okl_Trx_Ar_Invoices_Pub.taiv_rec_type
82 )
83 is
84 l_api_name CONSTANT VARCHAR2(30) := 'additional_tai_attr';
85 l_api_version CONSTANT NUMBER := 1.0;
86 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
87
88 l_legal_entity_id okl_trx_ar_invoices_b.legal_entity_id%TYPE;
89
90 l_jtot_object1_code okc_rules_b.jtot_object1_code%TYPE;
91 l_jtot_object2_code okc_rules_b.jtot_object2_code%TYPE;
92 l_object1_id1 okc_rules_b.object1_id1%TYPE;
93 l_object1_id2 okc_rules_b.object1_id2%TYPE;
94
95 CURSOR rule_code_csr(p_khr_id NUMBER, p_rule_category VARCHAR2) IS
96 SELECT jtot_object1_code,
97 object1_id1,
98 object1_id2
99 FROM okc_rules_b
100 WHERE rgp_id =
101 (SELECT id
102 FROM okc_rule_groups_b
103 WHERE dnz_chr_id = p_khr_id
104 AND cle_id IS NULL
105 AND rgd_code = 'LABILL')
106 AND rule_information_category = p_rule_category;
107
108 l_cust_bank_acct okx_rcpt_method_accounts_v.bank_account_id%TYPE;
109
110 --28-May-2008 sechawla 6619311 Moved these cursors to bill_upfront_tax procedure
111 /*
112 CURSOR cust_trx_type_csr(p_sob_id NUMBER, p_org_id NUMBER) IS
113 SELECT id1
114 FROM okx_cust_trx_types_v
115 WHERE name = 'Invoice-OKL'
116 AND set_of_books_id = p_sob_id
117 AND org_id = p_org_id;
118
119 CURSOR cm_trx_type_csr(p_sob_id NUMBER, p_org_id NUMBER) IS
120 SELECT id1
121 FROM okx_cust_trx_types_v
122 WHERE name = 'Credit Memo-OKL'
123 AND set_of_books_id = p_sob_id
124 AND org_id = p_org_id;
125 */
126
127 CURSOR org_id_csr(p_khr_id NUMBER) IS
128 SELECT authoring_org_id
129 FROM okc_k_headers_b
130 WHERE id = p_khr_id;
131
132 --added for rules migration
133 CURSOR cur_address_billto(p_contract_id IN VARCHAR2) IS
134 SELECT a.cust_acct_id cust_account_id,
135 b.cust_acct_site_id,
136 c.standard_terms payment_term_id
137 FROM okc_k_headers_v a,
138 okx_cust_site_uses_v b,
139 hz_customer_profiles c
140 WHERE a.id = p_contract_id
141 AND a.bill_to_site_use_id = b.id1
142 AND a.bill_to_site_use_id = c.site_use_id(+);
143
144 billto_rec cur_address_billto % ROWTYPE;
145
146 CURSOR rcpt_mthd_csr(p_cust_rct_mthd NUMBER) IS
147 SELECT c.receipt_method_id
148 FROM ra_cust_receipt_methods c
149 WHERE c.cust_receipt_method_id = p_cust_rct_mthd;
150
151 -- For bank accounts
152 CURSOR bank_acct_csr(p_id NUMBER) IS
153 SELECT bank_account_id
154 FROM okx_rcpt_method_accounts_v
155 WHERE id1 = p_id;
156
157 -- Default term Id
158 cursor std_terms_csr IS
159 SELECT B.TERM_ID
160 FROM RA_TERMS_TL T, RA_TERMS_B B
161 where T.name = 'IMMEDIATE' and T.LANGUAGE = userenv('LANG')
162 and B.TERM_ID = T.TERM_ID;
163
164 l_term_id okl_trx_ar_invoices_b.irt_id%type; -- cklee 3/20/07
165
166 CURSOR rcpt_method_csr(p_rct_method_id NUMBER) IS
167 SELECT c.creation_method_code
168 FROM ar_receipt_methods m,
169 ar_receipt_classes c
170 WHERE m.receipt_class_id = c.receipt_class_id
171 AND m.receipt_method_id = p_rct_method_id;
172
173 l_rct_method_code ar_receipt_classes.creation_method_code%TYPE;
174
175 --Start code added by pgomes on 20-NOV-2002
176 SUBTYPE khr_id_type IS okl_k_headers_v.khr_id%TYPE;
177 l_khr_id khr_id_type;
178 l_currency_code okl_trx_ar_invoices_b.currency_code%TYPE;
179 l_currency_conversion_type okl_trx_ar_invoices_b.currency_conversion_type%TYPE;
180 l_currency_conversion_rate okl_trx_ar_invoices_b.currency_conversion_rate%TYPE;
181 l_currency_conversion_date okl_trx_ar_invoices_b.currency_conversion_date%TYPE;
182
183 --Get currency conversion attributes for a contract
184 CURSOR l_curr_conv_csr(cp_khr_id IN khr_id_type) IS
185 SELECT currency_code,
186 currency_conversion_type,
187 currency_conversion_rate,
188 currency_conversion_date
189 FROM okl_k_headers_full_v
190 WHERE id = cp_khr_id;
191
192 CURSOR inv_frmt_csr(cp_khr_id IN NUMBER) IS
193 SELECT to_number(rul.rule_information1), -- inf.id, --sechawla 26-may-09 6826580
194 rul.rule_information4 review_invoice_yn
195 FROM okc_rule_groups_v rgp,
196 okc_rules_v rul
197 --, okl_invoice_formats_v inf --sechawla 26-may-09 6826580
198 WHERE rgp.dnz_chr_id = cp_khr_id
199 AND rgp.chr_id = rgp.dnz_chr_id
200 AND rgp.id = rul.rgp_id
201 AND rgp.cle_id IS NULL
202 AND rgp.rgd_code = 'LABILL'
203 AND rul.rule_information_category = 'LAINVD';
204 -- AND rul.rule_information1 = inf.name; --sechawla 26-may-09 6826580
205
206 l_inf_id okl_invoice_formats_v.id%TYPE;
207
208 CURSOR pvt_label_csr(cp_khr_id IN NUMBER) IS
209 SELECT rule_information1 private_label
210 FROM okc_rule_groups_b a,
211 okc_rules_b b
212 WHERE a.dnz_chr_id = cp_khr_id
213 AND a.rgd_code = 'LALABL'
214 AND a.id = b.rgp_id
215 AND b.rule_information_category = 'LALOGO';
216
217 l_private_label okc_rules_b.rule_information1%TYPE;
218
219
220 begin
221 -- Set API savepoint
222 SAVEPOINT additional_tai_attr;
223
224 -- Check for call compatibility
225 IF (NOT FND_API.Compatible_API_Call (l_api_version,
226 p_api_version,
227 l_api_name,
228 G_PKG_NAME ))
229 THEN
230 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
231 END IF;
232
233 -- Initialize message list if requested
234 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
235 FND_MSG_PUB.initialize;
236 END IF;
237
238 -- Initialize API status to success
239 x_return_status := OKL_API.G_RET_STS_SUCCESS;
240
241
242 -- assign all passed in attributes from IN to OUT record
243 x_taiv_rec := p_taiv_rec;
244 l_khr_id := p_taiv_rec.khr_id;
245 IF l_khr_id IS NOT NULL THEN
246 l_currency_code := NULL;
247 l_currency_conversion_type := NULL;
248 l_currency_conversion_rate := NULL;
249 l_currency_conversion_date := NULL;
250
251 FOR cur IN l_curr_conv_csr(l_khr_id)
252 LOOP
253 l_currency_code := cur.currency_code;
254 l_currency_conversion_type := cur.currency_conversion_type;
255 l_currency_conversion_rate := cur.currency_conversion_rate;
256 l_currency_conversion_date := cur.currency_conversion_date;
257 END LOOP;
258
259 -- Private Label
260 l_private_label := NULL;
261
262 OPEN pvt_label_csr(l_khr_id);
263 FETCH pvt_label_csr
264 INTO l_private_label;
265 CLOSE pvt_label_csr;
266 x_taiv_rec.private_label := l_private_label;
267 l_jtot_object1_code := NULL;
268 l_object1_id1 := NULL;
269 l_object1_id2 := NULL;
270 l_jtot_object2_code := NULL;
271
272 IF (p_taiv_rec.legal_entity_id IS NULL OR (p_taiv_rec.legal_entity_id = Okl_Api.G_MISS_NUM)) THEN
273 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(l_khr_id);
274 ELSE
275 l_legal_entity_id := p_taiv_rec.legal_entity_id;
276 END IF;
277 x_taiv_rec.legal_entity_id := l_legal_entity_id;
278 --akrangan added for tax only invoice changes begin
279 --added gmiss condition
280 IF(p_taiv_rec.irm_id IS NULL OR p_taiv_rec.irm_id = Okl_Api.G_MISS_NUM) THEN
281 --akrangan added for tax only invoice changes end
282 OPEN rule_code_csr(l_khr_id, 'LAPMTH');
283 FETCH rule_code_csr
284 INTO l_jtot_object1_code,
285 l_object1_id1,
286 l_object1_id2;
287 CLOSE rule_code_csr;
288
289 IF l_object1_id2 <> '#' THEN
290 x_taiv_rec.irm_id := l_object1_id2;
291 ELSE
292 -- This cursor needs to be removed when the view changes to
293 -- include id2
294 OPEN rcpt_mthd_csr(l_object1_id1);
295 FETCH rcpt_mthd_csr
296 INTO x_taiv_rec.irm_id;
297 CLOSE rcpt_mthd_csr;
298 END IF;
299
300 ELSE
301 x_taiv_rec.irm_id := p_taiv_rec.irm_id;
302 END IF;
303
304 -- Null out local variables
305 l_jtot_object1_code := NULL;
306 l_object1_id1 := NULL;
307 l_jtot_object2_code := NULL;
308
309 billto_rec.cust_account_id := NULL;
310 billto_rec.cust_acct_site_id := NULL;
311 billto_rec.payment_term_id := NULL;
312
313 OPEN cur_address_billto(l_khr_id);
314 FETCH cur_address_billto
315 INTO billto_rec;
316 CLOSE cur_address_billto;
317
318 IF (p_taiv_rec.ixx_id IS NULL OR (p_taiv_rec.ixx_id = Okl_Api.G_MISS_NUM)) THEN
319 x_taiv_rec.ixx_id := billto_rec.cust_account_id;
320 ELSE
321 x_taiv_rec.ixx_id := p_taiv_rec.ixx_id;
322 END IF;
323
324 IF (p_taiv_rec.ibt_id IS NULL OR (p_taiv_rec.ibt_id = Okl_Api.G_MISS_NUM)) THEN
325 x_taiv_rec.ibt_id := billto_rec.cust_acct_site_id;
326 ELSE
327 x_taiv_rec.ibt_id := p_taiv_rec.ibt_id;
328 END IF;
329
330 OPEN std_terms_csr;
331 FETCH std_terms_csr
332 INTO l_term_id;
333 CLOSE std_terms_csr;
334 --akrangan added for tax only invoice changes begin
335 --changed for handling gmiss values
336 IF p_taiv_rec.irt_id IS NULL
337 OR p_taiv_rec.irt_id = Okl_Api.G_MISS_NUM THEN
338 x_taiv_rec.irt_id := l_term_id;
339 ELSE
340 x_taiv_rec.irt_id := p_taiv_rec.irt_id;
341 END IF;
342 --akrangan added for tax only invoice changes end
343 IF (p_taiv_rec.org_id IS NULL OR p_taiv_rec.org_id=OKL_API.G_MISS_NUM) THEN
344
345 OPEN org_id_csr(l_khr_id);
346 FETCH org_id_csr
347 INTO x_taiv_rec.org_id;
348 CLOSE org_id_csr;
349 ELSE
350 x_taiv_rec.org_id := p_taiv_rec.org_id;
351 END IF;
352
353 -- To resolve the bank account for the customer
354 -- If receipt method is manual do not supply customer bank account
355 -- Id. This is required for Auto Invoice Validation
356
357 -- Null out variable
358 l_rct_method_code := NULL;
359
360 OPEN rcpt_method_csr(x_taiv_rec.irm_id);
361 FETCH rcpt_method_csr
362 INTO l_rct_method_code;
363 CLOSE rcpt_method_csr;
364
365 --Null out variables
366 l_jtot_object1_code := NULL;
367 l_object1_id1 := NULL;
368 l_object1_id2 := NULL;
369 l_cust_bank_acct := NULL;
370
371 IF(l_rct_method_code <> 'MANUAL') THEN
372
373 OPEN rule_code_csr(l_khr_id, 'LABACC');
374 FETCH rule_code_csr
375 INTO l_jtot_object1_code,
376 l_object1_id1,
377 l_object1_id2;
378 CLOSE rule_code_csr;
379
380 OPEN bank_acct_csr(l_object1_id1);
381 FETCH bank_acct_csr
382 INTO l_cust_bank_acct;
383 CLOSE bank_acct_csr;
384
385 x_taiv_rec.customer_bank_account_id := l_cust_bank_acct;
386 END IF;
387
388 l_inf_id := NULL;
389
390 OPEN inv_frmt_csr(l_khr_id);
391 FETCH inv_frmt_csr
392 INTO x_taiv_rec.inf_id,
393 x_taiv_rec.invoice_pull_yn;
394 CLOSE inv_frmt_csr;
395
396 END IF; -- IF l_khr_id IS NOT NULL THEN
397
398 --How to get the set_of_books_id ?
399
400 IF (p_taiv_rec.set_of_books_id IS NULL OR p_taiv_rec.set_of_books_id = OKL_API.G_MISS_NUM) THEN
401 x_taiv_rec.set_of_books_id := Okl_Accounting_Util.get_set_of_books_id;
402 ELSE
403 x_taiv_rec.set_of_books_id := p_taiv_rec.set_of_books_id;
404 --TAI
405 END IF;
406
407 --Check for currency code
408
409 IF (p_taiv_rec.currency_code IS NULL OR p_taiv_rec.currency_code=OKL_API.G_MISS_CHAR) THEN
410 x_taiv_rec.currency_code := l_currency_code;
411 ELSE
412 x_taiv_rec.currency_code := p_taiv_rec.currency_code;
413 END IF;
414
415 --Check for currency conversion type
416
417 IF (p_taiv_rec.currency_conversion_type IS NULL OR p_taiv_rec.currency_conversion_type = OKL_API.G_MISS_CHAR) THEN
418 x_taiv_rec.currency_conversion_type := l_currency_conversion_type;
419 ELSE
420 x_taiv_rec.currency_conversion_type := p_taiv_rec.currency_conversion_type;
421 END IF;
422
423 --Check for currency conversion rate
424
425 IF(p_taiv_rec.currency_conversion_type = 'User') THEN
426
427 IF(p_taiv_rec.currency_code = Okl_Accounting_Util.get_func_curr_code) THEN
428 x_taiv_rec.currency_conversion_rate := 1;
429 ELSE
430
431 IF (p_taiv_rec.currency_conversion_rate IS NULL OR p_taiv_rec.currency_conversion_rate=OKL_API.G_MISS_NUM) THEN
432 x_taiv_rec.currency_conversion_rate := l_currency_conversion_rate;
433 ELSE
434 x_taiv_rec.currency_conversion_rate := p_taiv_rec.currency_conversion_rate;
435 END IF;
436
437 END IF;
438
439 ELSE
440 x_taiv_rec.currency_conversion_rate := NULL;
441 END IF;
442
443 --Check for currency conversion date
444
445 IF (p_taiv_rec.currency_conversion_date IS NULL OR p_taiv_rec.currency_conversion_date = OKL_API.G_MISS_DATE) THEN
446 x_taiv_rec.currency_conversion_date := l_currency_conversion_date;
447 ELSE
448 x_taiv_rec.currency_conversion_date := p_taiv_rec.currency_conversion_date;
449 END IF;
450
451
452 IF(p_taiv_rec.currency_conversion_type IS NULL OR p_taiv_rec.currency_conversion_date = OKL_API.G_MISS_DATE) THEN
453 x_taiv_rec.currency_conversion_type := 'User';
454 x_taiv_rec.currency_conversion_rate := 1;
455 x_taiv_rec.currency_conversion_date := SYSDATE;
456 END IF;
457
458 -- Populate Customer TRX-TYPE ID From AR setup
459
460 --28-May-2008 sechawla 6619311
461 --Moved the following code to bill_upfront_tax procedure so that corrcet trx type can be drived
462 --after calculating the total tax amount.
463 /*
464 IF p_taiv_rec.amount < 0 THEN
465 x_taiv_rec.irt_id := NULL;
466
467 OPEN cm_trx_type_csr(x_taiv_rec.set_of_books_id, x_taiv_rec.org_id);
468 FETCH cm_trx_type_csr
469 INTO x_taiv_rec.cust_trx_type_id;
470 CLOSE cm_trx_type_csr;
471 ELSE
472
473 OPEN cust_trx_type_csr(x_taiv_rec.set_of_books_id, x_taiv_rec.org_id);
474 FETCH cust_trx_type_csr
475 INTO x_taiv_rec.cust_trx_type_id;
476 CLOSE cust_trx_type_csr;
477 END IF;
478 */
479 -- Set Tax exempt flag to Standard
480 x_taiv_rec.tax_exempt_flag := 'S';
481 x_taiv_rec.tax_exempt_reason_code := NULL;
482
483
484 -- Get message count and if count is 1, get message info
485 FND_MSG_PUB.Count_And_Get
486 (p_count => x_msg_count,
487 p_data => x_msg_data);
488
489 EXCEPTION
490 WHEN OKL_API.G_EXCEPTION_ERROR THEN
491 ROLLBACK TO additional_tai_attr;
492 x_return_status := OKL_API.G_RET_STS_ERROR;
493 FND_MSG_PUB.Count_And_Get
494 (p_count => x_msg_count,
495 p_data => x_msg_data);
496
497 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
498 ROLLBACK TO additional_tai_attr;
499 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
500 FND_MSG_PUB.Count_And_Get
501 (p_count => x_msg_count,
502 p_data => x_msg_data);
503
504 WHEN OTHERS THEN
505 ROLLBACK TO additional_tai_attr;
506 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
507 OKL_API.Set_Message(p_app_name => G_APP_NAME,
508 p_msg_name => G_UNEXPECTED_ERROR,
509 p_token1 => G_SQLCODE_TOKEN,
510 p_token1_value => SQLCODE,
511 p_token2 => G_SQLERRM_TOKEN,
512 p_token2_value => SQLERRM);
513 FND_MSG_PUB.Count_And_Get
514 (p_count => x_msg_count,
515 p_data => x_msg_data);
516
517 end additional_tai_attr;
518
519 PROCEDURE Bill_Upfront_Tax(
520 p_api_version IN NUMBER,
521 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
522 p_khr_id IN NUMBER,
523 p_trx_id IN NUMBER,
524 p_invoice_date IN DATE,
525 x_return_status OUT NOCOPY VARCHAR2,
526 x_msg_count OUT NOCOPY NUMBER,
527 x_msg_data OUT NOCOPY VARCHAR2) IS
528
529 l_api_name CONSTANT VARCHAR2(30) := 'BILL_UPFRONT_TAX';
530 l_api_version CONSTANT NUMBER := 1;
531 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
532
533 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
534 l_zero_amount CONSTANT NUMBER := 0;
535 l_def_desc CONSTANT VARCHAR2(30) := 'Upfront Tax Billing';
536
537 l_billing_try_id Okl_Trx_Ar_Invoices_V.try_id%TYPE;
538
539 l_init_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
540 l_init_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
541 l_init_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
542 lp_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
543 lp_empty_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;--sechawla 13-may-2008 6619311
544 lp_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
545 lp_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
546 xp_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
547 xp_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
548 xp_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
549
550 CURSOR upfront_tax_csr(p_khr_id IN NUMBER) IS
551 SELECT rl.rule_information2
552 FROM okc_rule_groups_b rg,
553 okc_rules_b rl
554 WHERE rg.dnz_chr_id = p_khr_id
555 AND rg.rgd_code = 'LAHDTX'
556 AND rl.rgp_id = rg.id
557 AND rl.dnz_chr_id = rg.dnz_chr_id
558 AND rl.rule_information_category = 'LASTPR';
559
560 upfront_tax_rec upfront_tax_csr%ROWTYPE;
561 --akrangan ebtax billing impacts coding start
562 CURSOR l_upfront_tax_treatment_csr(cp_khr_id IN NUMBER) IS
563 SELECT r1.rule_information1 asset_upfront_tax
564 FROM okc_rule_groups_b rg,
565 okc_rules_b r1
566 WHERE rg.dnz_chr_id = cp_khr_id
567 AND rg.rgd_code = 'LAHDTX'
568 AND r1.rgp_id = rg.id
569 AND r1.dnz_chr_id = rg.dnz_chr_id
570 AND r1.rule_information_category = 'LASTPR';
571
572 --This cursor selects all asset lines for which upfront tax treatment is set to 'Billied'
573 --at asset level, Plus all asset lines for which tax treatment is set to Null at asset level
574 -- (When asset treatment at hdr (K) level is set to 'Billed', these assets (with null tax treatment)
575 -- will inherit the 'Billed' treatment from the header),
576 --Plus K level taxable lines (K level tax is always 'Billed')
577 --For ALC, following cursor will select taxable lines corresponding to the specific asset
578 --for which Loc is being changed
579 --Since this cursor does not have a filter on the tax line status, it will pick both active and inactive
580 --rows in case of rebook. This will insure that the amount billed during rebook is the difference
581 --of old and new tax.
582
583 CURSOR l_allbilledtaxablelines_csr(cp_khr_id IN NUMBER, cp_trx_id IN NUMBER) IS
584 SELECT trx_id,
585 trx_line_id,
586 entity_code,
587 event_class_code,
588 application_id,
589 trx_level_type,
590 kle_id,
591 org_id
592 FROM okl_tax_sources txs,
593 okc_rule_groups_b rg,
594 okc_rules_b r1
595 WHERE txs.khr_id = cp_khr_id
596 AND txs.application_id = 540
597 AND txs.trx_id = cp_trx_id
598 AND txs.trx_level_type = 'LINE'
599 AND txs.tax_call_type_code = 'UPFRONT_TAX'
600 AND rg.dnz_chr_id = txs.khr_id
601 AND rg.cle_id = txs.kle_id
602 AND rg.rgd_code = 'LAASTX'
603 AND r1.rgp_id = rg.id --akrangan added for perfomance --hash cartesian join fixed
604 AND nvl(r1.rule_information11, 'BILLED') = 'BILLED' --sechawla 08-nov-07 6618649 : changed rule_information1 to rule_information11
605 AND r1.dnz_chr_id = cp_khr_id --sechawla 08-nov-07 6618649 : added for performance
606 AND r1.RULE_INFORMATION_CATEGORY ='LAASTX' --sechawla 08-nov-07 6618649 : Added this condition
607 AND txs.total_tax <> 0
608 UNION
609 SELECT trx_id,
610 trx_line_id,
611 entity_code,
612 event_class_code,
613 application_id,
614 trx_level_type,
615 NULL,
616 org_id
617 FROM okl_tax_sources txs
618 WHERE txs.khr_id = cp_khr_id
619 AND txs.kle_id IS NULL
620 AND txs.application_id = 540
621 AND txs.trx_id = cp_trx_id
622 AND txs.trx_level_type = 'LINE'
623 AND txs.tax_call_type_code = 'UPFRONT_TAX'
624 AND txs.total_tax <> 0 ;
625
626 CURSOR l_billedtaxablelines_csr(cp_khr_id IN NUMBER, cp_trx_id IN NUMBER) IS
627 SELECT trx_id,
628 trx_line_id,
629 entity_code,
630 event_class_code,
631 application_id,
632 trx_level_type,
633 kle_id,
634 org_id
635 FROM okl_tax_sources txs,
636 okc_rule_groups_b rg,
637 okc_rules_b r1
638 WHERE txs.khr_id = cp_khr_id
639 AND txs.application_id = 540
640 AND txs.trx_id = cp_trx_id
641 AND txs.trx_level_type = 'LINE'
642 AND txs.tax_call_type_code = 'UPFRONT_TAX'
643 AND rg.dnz_chr_id = txs.khr_id
644 AND rg.cle_id = txs.kle_id
645 AND rg.rgd_code = 'LAASTX'
646 AND r1.rgp_id = rg.id --akrangan added for perfomance --hash cartesian join fixed
647 AND r1.rule_information11 = 'BILLED' --sechawla 08-nov-07 6618649 : changed rule_information1 to rule_information11
648 AND r1.dnz_chr_id = cp_khr_id --sechawla 08-nov-07 6618649 : added for performance
649 AND r1.RULE_INFORMATION_CATEGORY ='LAASTX' --sechawla 08-nov-07 6618649 : Added this condition
650 AND txs.total_tax <> 0
651 UNION
652 SELECT trx_id,
653 trx_line_id,
654 entity_code,
655 event_class_code,
656 application_id,
657 trx_level_type,
658 NULL,
659 org_id
660 FROM okl_tax_sources txs
661 WHERE txs.khr_id = cp_khr_id
662 AND txs.kle_id IS NULL
663 AND txs.application_id = 540
664 AND txs.trx_id = cp_trx_id
665 AND txs.trx_level_type = 'LINE'
666 AND txs.tax_call_type_code = 'UPFRONT_TAX'
667 AND txs.total_tax <> 0;
668 CURSOR l_fndlanguage_csr IS
669 SELECT language_code,
670 installed_flag
671 FROM fnd_languages
672 WHERE installed_flag IN ('I', 'B')
673 ORDER BY installed_flag;
674
675 -- to get inventory_org_id
676 CURSOR inv_org_id_csr(p_contract_id NUMBER) IS
677 SELECT NVL(inv_organization_id, -99),
678 org_id --akrangan added for tax only invoice changes
679 FROM okc_k_headers_b
680 WHERE id = p_contract_id;
681
682 --sechawla 13-may-2008 6619311 : added this cursor
683 CURSOR zx_lines_csr(cp_trx_id IN NUMBER, cp_trx_line_id IN NUMBER, cp_application_id IN NUMBER,
684 cp_event_class_code IN VARCHAR2,cp_entity_code IN VARCHAR2 , cp_trx_level_type IN VARCHAR2) IS
685 SELECT tax_line_id, tax_amt
686 FROM zx_lines
687 WHERE trx_id = cp_trx_id
688 AND trx_line_id = cp_trx_line_id
689 AND application_id = cp_application_id
690 AND event_class_code = cp_event_class_code
691 AND entity_code = cp_entity_code
692 AND trx_level_type = cp_trx_level_type
693 AND nvl(cancel_flag, 'N') <> 'Y';
694
695 --28-May-2008 sechawla 6619311 Moved these cursors to bill_upfront_tax procedure
696
697 CURSOR cust_trx_type_csr(p_sob_id NUMBER, p_org_id NUMBER) IS
698 SELECT id1
699 FROM okx_cust_trx_types_v
700 WHERE name = 'Invoice-OKL'
701 AND set_of_books_id = p_sob_id
702 AND org_id = p_org_id;
703
704 CURSOR cm_trx_type_csr(p_sob_id NUMBER, p_org_id NUMBER) IS
705 SELECT id1
706 FROM okx_cust_trx_types_v
707 WHERE name = 'Credit Memo-OKL'
708 AND set_of_books_id = p_sob_id
709 AND org_id = p_org_id;
710
711
712
713 l_total_tax_amt NUMBER; --sechawla 13-may-2008 6619311
714
715 l_sty_id NUMBER;
716 l_upfront_tax_treatment_rec l_upfront_tax_treatment_csr%ROWTYPE;
717 TYPE txl_ar_inv_lins_tbl
718 IS TABLE OF okl_txl_ar_inv_lns_b%ROWTYPE
719 INDEX BY BINARY_INTEGER;
720
721 TYPE txl_ar_inv_lns_tl_tbl
722 IS TABLE OF okl_txl_ar_inv_lns_tl%ROWTYPE
723 INDEX BY BINARY_INTEGER;
724
725 TYPE txd_ar_inv_ln_dtls_tbl
726 IS TABLE OF okl_txd_ar_ln_dtls_b%ROWTYPE
727 INDEX BY BINARY_INTEGER;
728
729 TYPE txd_ar_inv_ln_dtls_tl_tbl
730 IS TABLE OF okl_txd_ar_ln_dtls_tl%ROWTYPE
731 INDEX BY BINARY_INTEGER;
732
733 l_txl_ar_inv_lns_tbl txl_ar_inv_lins_tbl;
734 l_txl_ar_inv_lns_tl_tbl txl_ar_inv_lns_tl_tbl;
735 l_txd_ar_inv_ln_dtls_tbl txd_ar_inv_ln_dtls_tbl;
736 i NUMBER;
737 k NUMBER;
738 l_bulk_err_cnt NUMBER;
739 l_source_language fnd_languages.language_code%TYPE;
740 lx_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
741 l_inv_org_id NUMBER;
742
743 -- sechawla 28-may-2008 6619311
744 l_cust_trx_type_id NUMBER;
745 l_cm_try_id Okl_Trx_Ar_Invoices_V.try_id%TYPE;
746
747 --akrangan ebtax billing impacts coding end
748
749 -- Bug 9067996
750
751 CURSOR inv_frmt_csr(cp_khr_id IN NUMBER) IS
752 SELECT to_number(rul.rule_information1), --inf.id, --sechawla 26-may-09 6826580
753 rul.rule_information4 review_invoice_yn
754 FROM okc_rule_groups_v rgp,
755 okc_rules_v rul
756 --, okl_invoice_formats_v inf --sechawla 26-may-09 6826580
757 WHERE rgp.dnz_chr_id = cp_khr_id
758 AND rgp.chr_id = rgp.dnz_chr_id
759 AND rgp.id = rul.rgp_id
760 AND rgp.cle_id IS NULL
761 AND rgp.rgd_code = 'LABILL'
762 AND rul.rule_information_category = 'LAINVD';
763 -- End -- Bug 906799
764
765 BEGIN
766
767 x_return_status := OKL_API.START_ACTIVITY(
768 p_api_name => l_api_name,
769 p_pkg_name => g_pkg_name,
770 p_init_msg_list => p_init_msg_list,
771 l_api_version => l_api_version,
772 p_api_version => p_api_version,
773 p_api_type => G_API_TYPE,
774 x_return_status => x_return_status);
775
776 -- check if activity started successfully
777 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
778 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
779 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
780 RAISE OKL_API.G_EXCEPTION_ERROR;
781 END IF;
782
783 IF (G_DEBUG_ENABLED = 'Y') THEN
784 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
785 END IF;
786 -- Populate Billing Header Record
787 lp_taiv_rec := l_init_taiv_rec;
788
789 lp_taiv_rec.khr_id := p_khr_id;
790 lp_taiv_rec.amount := l_zero_amount; --sechawla 13-may-2008 6619311 : This amount will be updated later with
791 -- total amount on invoice lines.
792
793 -- sechawla 13-may-2008 6619311 : get both billing and Credit Memo trx type IDs
794 l_billing_try_id := get_trx_type ('Billing', 'US');
795 l_cm_try_id := get_trx_type ('Credit Memo', 'US');
796
797 lp_taiv_rec.try_id := l_billing_try_id; -- sechawla 13-may-2008 6619311 : This try_id will get updated later
798 -- to Credit Memo, if total tax amount is negative
799 lp_taiv_rec.trx_status_code := 'SUBMITTED';
800 lp_taiv_rec.date_invoiced := p_invoice_date;
801 lp_taiv_rec.date_entered := SYSDATE;
802 lp_taiv_rec.description := 'Upfront Tax Billing';
803 lp_taiv_rec.okl_source_billing_trx := 'TAX_ONLY_INVOICE_TAX';
804 --akrangan added for tax only invoice changes begin
805 --get inv org and org
806 OPEN inv_org_id_csr(p_khr_id);
807 FETCH inv_org_id_csr
808 INTO l_inv_org_id ,
809 lp_taiv_rec.org_id;
810 CLOSE inv_org_id_csr;
811 --akrangan added for tax only invoice changes end
812 --akrangan added for populating additional attributes for taiv_rec start
813 --The Additional Attributes are required attributes for populatiing
814 -- RA interface tables
815 additional_tai_attr(
816 p_api_version => l_api_version
817 ,p_init_msg_list => p_init_msg_list
818 ,x_return_status => x_return_status
819 ,x_msg_count => x_msg_count
820 ,x_msg_data => x_msg_data
821 ,p_taiv_rec => lp_taiv_rec
822 ,x_taiv_rec => lx_taiv_rec
823 );
824 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
825 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
826 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
827 raise OKL_API.G_EXCEPTION_ERROR;
828 END IF;
829 --akrangan added for populating additional attributes for taiv_rec end
830 -- Create Billing Header Record
831
832
833 OKL_TRX_AR_INVOICES_PUB.insert_trx_ar_invoices(
834 p_api_version => p_api_version
835 ,p_init_msg_list => p_init_msg_list
836 ,x_return_status => x_return_status
837 ,x_msg_count => x_msg_count
838 ,x_msg_data => x_msg_data
839 ,p_taiv_rec => lx_taiv_rec
840 ,x_taiv_rec => xp_taiv_rec);
841
842 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
843 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
844 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
845 raise OKL_API.G_EXCEPTION_ERROR;
846 END IF;
847
848
849 OPEN upfront_tax_csr(p_khr_id => p_khr_id);
850 FETCH upfront_tax_csr INTO upfront_tax_rec;
851 CLOSE upfront_tax_csr;
852 --akrangan ebtax billing impacts coding start
853 l_sty_id := to_number(upfront_tax_rec.rule_information2);
854 OPEN l_upfront_tax_treatment_csr(cp_khr_id => p_khr_id);
855 FETCH l_upfront_tax_treatment_csr INTO l_upfront_tax_treatment_rec;
856 CLOSE l_upfront_tax_treatment_csr;
857
858 --contract level asset upfront tax is set to billed
859 l_txl_ar_inv_lns_tbl.DELETE;
860 i := 1;
861 k := 1;
862 l_total_tax_amt := 0; --sechawla 13-may-2008 6619311
863 IF (l_upfront_tax_treatment_rec.asset_upfront_tax = 'BILLED')
864 THEN
865 -- Loop through the taxable lines
866 FOR l_allbilledtaxablelines_rec IN l_allbilledtaxablelines_csr(p_khr_id,
867 p_trx_id)
868 LOOP
869 --sechawla 13-may-2008 6619311 : added this cursor for loop
870 --for each taxable line, create as many invoice lines as the number of tax lines.
871 --All invoice lines go under a single invoice header.
872 --Each invoice line corresponds to the tax line that needs to be billed
873
874 --Loop through tax lines for each taxable line
875 FOR zx_lines_rec IN zx_lines_csr(l_allbilledtaxablelines_rec.trx_id,
876 l_allbilledtaxablelines_rec.trx_line_id,
877 l_allbilledtaxablelines_rec.application_id,
878 l_allbilledtaxablelines_rec.event_class_code,
879 l_allbilledtaxablelines_rec.entity_code ,
880 l_allbilledtaxablelines_rec.trx_level_type)
881
882
883 LOOP
884 -- Populate Billing Line Tbl
885 l_txl_ar_inv_lns_tbl(i).id := okc_p_util.raw_to_number(sys_guid());
886 l_txl_ar_inv_lns_tbl(i).kle_id := l_allbilledtaxablelines_rec.kle_id;
887 l_txl_ar_inv_lns_tbl(i).amount := zx_lines_rec.tax_amt; --sechawla 13-may-2008 6619311 : changed to store tax amount instead of 0
888
889 --sechawla 13-may-2008 6619311 : calculate total tax to store on the header
890 l_total_tax_amt := l_total_tax_amt + zx_lines_rec.tax_amt;
891
892 l_txl_ar_inv_lns_tbl(i).tai_id := xp_taiv_rec.id;
893 l_txl_ar_inv_lns_tbl(i).sty_id := l_sty_id;
894 l_txl_ar_inv_lns_tbl(i).inv_receiv_line_code := l_line_code;
895 l_txl_ar_inv_lns_tbl(i).line_number := i;
896 l_txl_ar_inv_lns_tbl(i).txs_trx_id := l_allbilledtaxablelines_rec.trx_id;
897 l_txl_ar_inv_lns_tbl(i).txs_trx_line_id := l_allbilledtaxablelines_rec.trx_line_id;
898 l_txl_ar_inv_lns_tbl(i).txl_ar_line_number := i;
899 l_txl_ar_inv_lns_tbl(i).org_id := l_allbilledtaxablelines_rec.org_id;
900 l_txl_ar_inv_lns_tbl(i).created_by := g_user_id;
901 l_txl_ar_inv_lns_tbl(i).creation_date := SYSDATE;
902 l_txl_ar_inv_lns_tbl(i).last_updated_by := g_user_id;
903 l_txl_ar_inv_lns_tbl(i).last_update_date := SYSDATE;
904 l_txl_ar_inv_lns_tbl(i).last_update_login := g_login_id;
905 l_txl_ar_inv_lns_tbl(i).isl_id := NULL;
906 l_txl_ar_inv_lns_tbl(i).ibt_id := NULL;
907 l_txl_ar_inv_lns_tbl(i).tpl_id := NULL;
908 l_txl_ar_inv_lns_tbl(i).cll_id := NULL;
909 l_txl_ar_inv_lns_tbl(i).acn_id_cost := NULL;
910 l_txl_ar_inv_lns_tbl(i).til_id_reverses := NULL;
911 l_txl_ar_inv_lns_tbl(i).object_version_number := 1;
912 l_txl_ar_inv_lns_tbl(i).quantity := 1;
913 l_txl_ar_inv_lns_tbl(i).receivables_invoice_id := NULL;
914 l_txl_ar_inv_lns_tbl(i).late_charge_rec_id := NULL;
915 l_txl_ar_inv_lns_tbl(i).amount_applied := NULL;
916 l_txl_ar_inv_lns_tbl(i).date_bill_period_start := NULL;
917 l_txl_ar_inv_lns_tbl(i).date_bill_period_end := NULL;
918 l_txl_ar_inv_lns_tbl(i).request_id := NULL;
919 l_txl_ar_inv_lns_tbl(i).program_application_id := NULL;
920 l_txl_ar_inv_lns_tbl(i).program_id := NULL;
921 l_txl_ar_inv_lns_tbl(i).program_update_date := NULL;
922 l_txl_ar_inv_lns_tbl(i).attribute_category := NULL;
923 l_txl_ar_inv_lns_tbl(i).attribute1 := NULL;
924 l_txl_ar_inv_lns_tbl(i).attribute2 := NULL;
925 l_txl_ar_inv_lns_tbl(i).attribute3 := NULL;
926 l_txl_ar_inv_lns_tbl(i).attribute4 := NULL;
927 l_txl_ar_inv_lns_tbl(i).attribute5 := NULL;
928 l_txl_ar_inv_lns_tbl(i).attribute6 := NULL;
929 l_txl_ar_inv_lns_tbl(i).attribute7 := NULL;
930 l_txl_ar_inv_lns_tbl(i).attribute8 := NULL;
931 l_txl_ar_inv_lns_tbl(i).attribute9 := NULL;
932 l_txl_ar_inv_lns_tbl(i).attribute10 := NULL;
933 l_txl_ar_inv_lns_tbl(i).attribute11 := NULL;
934 l_txl_ar_inv_lns_tbl(i).attribute12 := NULL;
935 l_txl_ar_inv_lns_tbl(i).attribute13 := NULL;
936 l_txl_ar_inv_lns_tbl(i).attribute14 := NULL;
937 l_txl_ar_inv_lns_tbl(i).attribute15 := NULL;
938 l_txl_ar_inv_lns_tbl(i).inventory_item_id := NULL;
939 l_txl_ar_inv_lns_tbl(i).inventory_org_id := NULL;
940 l_txl_ar_inv_lns_tbl(i).bank_acct_id := NULL;
941 l_txl_ar_inv_lns_tbl(i).qte_line_id := NULL;
942 l_txl_ar_inv_lns_tbl(i).upgrade_from_two_level_yn := NULL;
943
944 --sechawla 13-may-2008 6619311 : store the corresponding tax line reference on each invoice line
945 l_txl_ar_inv_lns_tbl(i).tax_line_id := zx_lines_rec.tax_line_id;
946
947 --populate line detail table
948 l_txd_ar_inv_ln_dtls_tbl(i).id := okc_p_util.raw_to_number(sys_guid());
949 l_txd_ar_inv_ln_dtls_tbl(i).object_version_number := 1;
950 l_txd_ar_inv_ln_dtls_tbl(i).created_by := g_user_id;
951 l_txd_ar_inv_ln_dtls_tbl(i).creation_date := SYSDATE;
952 l_txd_ar_inv_ln_dtls_tbl(i).last_updated_by := g_user_id;
953 l_txd_ar_inv_ln_dtls_tbl(i).last_update_date := SYSDATE;
954 l_txd_ar_inv_ln_dtls_tbl(i).til_id_details := l_txl_ar_inv_lns_tbl(i).id;
955 l_txd_ar_inv_ln_dtls_tbl(i).sty_id := l_sty_id;
956 --added additional attribute tld rec
957 l_txd_ar_inv_ln_dtls_tbl(i).amount := zx_lines_rec.tax_amt; --sechawla 13-may-2008 6619311 : changed to store tax amount instead of 0;
958 l_txd_ar_inv_ln_dtls_tbl(i).inventory_org_id := l_inv_org_id;
959
960 --28-May-2008 sechawla 6619311 : populate khr_id and kle_id
961 l_txd_ar_inv_ln_dtls_tbl(i).khr_id := p_khr_id;
962 l_txd_ar_inv_ln_dtls_tbl(i).kle_id := l_allbilledtaxablelines_rec.kle_id;
963 --
964
965 -- Bug 9067996
966 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
967 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inf id:'||lx_taiv_rec.inf_id);
968 END IF;
969
970 OKL_INTERNAL_BILLING_PVT.Get_Invoice_format(
971 p_api_version => p_api_version
972 ,p_init_msg_list => OKL_API.G_FALSE
973 ,x_return_status => l_return_status
974 ,x_msg_count => x_msg_count
975 ,x_msg_data => x_msg_data
976 ,p_inf_id => lx_taiv_rec.inf_id
977 ,p_sty_id => l_sty_id
978 ,x_invoice_format_type => l_txd_ar_inv_ln_dtls_tbl(i).invoice_format_type
979 ,x_invoice_format_line_type => l_txd_ar_inv_ln_dtls_tbl(i).invoice_format_line_type);
980 --Print Input Variables
981 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
982 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'invoice format type :'||l_txd_ar_inv_ln_dtls_tbl(i).invoice_format_type);
983 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'invoice format line type :'||l_txd_ar_inv_ln_dtls_tbl(i).invoice_format_line_type);
984 END IF;
985
986 -- End Bug 9067996
987
988 --tl table record population
989 FOR l_fndlanguage_rec IN l_fndlanguage_csr
990 LOOP
991 l_txl_ar_inv_lns_tl_tbl(k).id := l_txl_ar_inv_lns_tbl(i).id;
992 IF k = 1 THEN
993 --- Base language
994 l_source_language := l_fndlanguage_rec.language_code;
995 END IF;
996 l_txl_ar_inv_lns_tl_tbl(k).LANGUAGE := l_fndlanguage_rec.language_code;
997 l_txl_ar_inv_lns_tl_tbl(k).source_lang := l_source_language;
998 l_txl_ar_inv_lns_tl_tbl(k).sfwt_flag := 'N';
999 l_txl_ar_inv_lns_tl_tbl(k).description := l_def_desc;
1000 l_txl_ar_inv_lns_tl_tbl(k).created_by := g_user_id;
1001 l_txl_ar_inv_lns_tl_tbl(k).creation_date := SYSDATE;
1002 l_txl_ar_inv_lns_tl_tbl(k).last_updated_by := g_user_id;
1003 l_txl_ar_inv_lns_tl_tbl(k).last_update_date := SYSDATE;
1004 l_txl_ar_inv_lns_tl_tbl(k).last_update_login := g_login_id;
1005 l_txl_ar_inv_lns_tl_tbl(k).error_message := NULL;
1006 --increment the looping variable
1007 k := k + 1;
1008 END LOOP;
1009 --increment the looping variable
1010 i := i + 1;
1011 END LOOP;
1012 END LOOP; --sechawla 13-may-2008 6619311 :Added
1013 ELSE -- total_billed_tax <>0 Asset upfront tax treatment at the hdr level <> 'BILLED'
1014
1015 -- Loop through the taxable lines
1016 FOR l_billedtaxablelines_rec IN l_billedtaxablelines_csr(p_khr_id,
1017 p_trx_id)
1018 LOOP
1019 --sechawla 13-may-2008 6619311 : added this cursor for loop
1020 -- Loop through the tax lines
1021 FOR zx_lines_rec IN zx_lines_csr(l_billedtaxablelines_rec.trx_id,
1022 l_billedtaxablelines_rec.trx_line_id,
1023 l_billedtaxablelines_rec.application_id,
1024 l_billedtaxablelines_rec.event_class_code,
1025 l_billedtaxablelines_rec.entity_code ,
1026 l_billedtaxablelines_rec.trx_level_type)
1027
1028
1029 LOOP
1030
1031 -- Populate Billing Line Tbl
1032 l_txl_ar_inv_lns_tbl(i).id := okc_p_util.raw_to_number(sys_guid());
1033 l_txl_ar_inv_lns_tbl(i).kle_id := l_billedtaxablelines_rec.kle_id;
1034 l_txl_ar_inv_lns_tbl(i).amount := zx_lines_rec.tax_amt;--sechawla 13-may-2008 6619311 : changed to store tax amount instead of 0
1035
1036 --sechawla 13-may-2008 6619311 : calculate total tax to store on the header
1037 l_total_tax_amt := l_total_tax_amt + zx_lines_rec.tax_amt;
1038
1039 l_txl_ar_inv_lns_tbl(i).tai_id := xp_taiv_rec.id;
1040 l_txl_ar_inv_lns_tbl(i).sty_id := l_sty_id;
1041 l_txl_ar_inv_lns_tbl(i).inv_receiv_line_code := l_line_code;
1042 l_txl_ar_inv_lns_tbl(i).line_number := i;
1043 l_txl_ar_inv_lns_tbl(i).txs_trx_id := l_billedtaxablelines_rec.trx_id;
1044 l_txl_ar_inv_lns_tbl(i).txs_trx_line_id := l_billedtaxablelines_rec.trx_line_id;
1045 l_txl_ar_inv_lns_tbl(i).txl_ar_line_number := i;
1046 l_txl_ar_inv_lns_tbl(i).org_id := l_billedtaxablelines_rec.org_id;
1047 l_txl_ar_inv_lns_tbl(i).created_by := g_user_id;
1048 l_txl_ar_inv_lns_tbl(i).creation_date := SYSDATE;
1049 l_txl_ar_inv_lns_tbl(i).last_updated_by := g_user_id;
1050 l_txl_ar_inv_lns_tbl(i).last_update_date := SYSDATE;
1051 l_txl_ar_inv_lns_tbl(i).last_update_login := g_login_id;
1052 l_txl_ar_inv_lns_tbl(i).isl_id := NULL;
1053 l_txl_ar_inv_lns_tbl(i).ibt_id := NULL;
1054 l_txl_ar_inv_lns_tbl(i).tpl_id := NULL;
1055 l_txl_ar_inv_lns_tbl(i).cll_id := NULL;
1056 l_txl_ar_inv_lns_tbl(i).acn_id_cost := NULL;
1057 l_txl_ar_inv_lns_tbl(i).til_id_reverses := NULL;
1058 l_txl_ar_inv_lns_tbl(i).object_version_number := 1;
1059 l_txl_ar_inv_lns_tbl(i).quantity := 1;
1060 l_txl_ar_inv_lns_tbl(i).receivables_invoice_id := NULL;
1061 l_txl_ar_inv_lns_tbl(i).late_charge_rec_id := NULL;
1062 l_txl_ar_inv_lns_tbl(i).amount_applied := NULL;
1063 l_txl_ar_inv_lns_tbl(i).date_bill_period_start := NULL;
1064 l_txl_ar_inv_lns_tbl(i).date_bill_period_end := NULL;
1065 l_txl_ar_inv_lns_tbl(i).request_id := NULL;
1066 l_txl_ar_inv_lns_tbl(i).program_application_id := NULL;
1067 l_txl_ar_inv_lns_tbl(i).program_id := NULL;
1068 l_txl_ar_inv_lns_tbl(i).program_update_date := NULL;
1069 l_txl_ar_inv_lns_tbl(i).attribute_category := NULL;
1070 l_txl_ar_inv_lns_tbl(i).attribute1 := NULL;
1071 l_txl_ar_inv_lns_tbl(i).attribute2 := NULL;
1072 l_txl_ar_inv_lns_tbl(i).attribute3 := NULL;
1073 l_txl_ar_inv_lns_tbl(i).attribute4 := NULL;
1074 l_txl_ar_inv_lns_tbl(i).attribute5 := NULL;
1075 l_txl_ar_inv_lns_tbl(i).attribute6 := NULL;
1076 l_txl_ar_inv_lns_tbl(i).attribute7 := NULL;
1077 l_txl_ar_inv_lns_tbl(i).attribute8 := NULL;
1078 l_txl_ar_inv_lns_tbl(i).attribute9 := NULL;
1079 l_txl_ar_inv_lns_tbl(i).attribute10 := NULL;
1080 l_txl_ar_inv_lns_tbl(i).attribute11 := NULL;
1081 l_txl_ar_inv_lns_tbl(i).attribute12 := NULL;
1082 l_txl_ar_inv_lns_tbl(i).attribute13 := NULL;
1083 l_txl_ar_inv_lns_tbl(i).attribute14 := NULL;
1084 l_txl_ar_inv_lns_tbl(i).attribute15 := NULL;
1085 l_txl_ar_inv_lns_tbl(i).inventory_item_id := NULL;
1086 l_txl_ar_inv_lns_tbl(i).inventory_org_id := NULL;
1087 l_txl_ar_inv_lns_tbl(i).bank_acct_id := NULL;
1088 l_txl_ar_inv_lns_tbl(i).qte_line_id := NULL;
1089 l_txl_ar_inv_lns_tbl(i).upgrade_from_two_level_yn := NULL;
1090
1091 l_txl_ar_inv_lns_tbl(i).tax_line_id := zx_lines_rec.tax_line_id;--sechawla 13-may-2008 6619311 :Added
1092
1093 --populate line detail table
1094 l_txd_ar_inv_ln_dtls_tbl(i).id := okc_p_util.raw_to_number(sys_guid());
1095 l_txd_ar_inv_ln_dtls_tbl(i).object_version_number := 1;
1096 l_txd_ar_inv_ln_dtls_tbl(i).created_by := g_user_id;
1097 l_txd_ar_inv_ln_dtls_tbl(i).creation_date := SYSDATE;
1098 l_txd_ar_inv_ln_dtls_tbl(i).last_updated_by := g_user_id;
1099 l_txd_ar_inv_ln_dtls_tbl(i).last_update_date := SYSDATE;
1100 l_txd_ar_inv_ln_dtls_tbl(i).til_id_details := l_txl_ar_inv_lns_tbl(i).id;
1101 l_txd_ar_inv_ln_dtls_tbl(i).sty_id := l_sty_id;
1102 --added additional attribute tld rec
1103 l_txd_ar_inv_ln_dtls_tbl(i).amount := zx_lines_rec.tax_amt; --sechawla 13-may-2008 6619311 : changed to store tax amount instead of 0;
1104 l_txd_ar_inv_ln_dtls_tbl(i).inventory_org_id := l_inv_org_id;
1105
1106 --28-May-2008 sechawla 6619311 : populate khr_id and kle_id
1107 l_txd_ar_inv_ln_dtls_tbl(i).khr_id := p_khr_id;
1108 l_txd_ar_inv_ln_dtls_tbl(i).kle_id := l_billedtaxablelines_rec.kle_id;
1109 --
1110 -- -- Bug 9067996
1111 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1112 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inf id:'||lx_taiv_rec.inf_id);
1113 END IF;
1114
1115 OKL_INTERNAL_BILLING_PVT.Get_Invoice_format(
1116 p_api_version => p_api_version
1117 ,p_init_msg_list => OKL_API.G_FALSE
1118 ,x_return_status => l_return_status
1119 ,x_msg_count => x_msg_count
1120 ,x_msg_data => x_msg_data
1121 ,p_inf_id => lx_taiv_rec.inf_id
1122 ,p_sty_id => l_sty_id
1123 ,x_invoice_format_type => l_txd_ar_inv_ln_dtls_tbl(i).invoice_format_type
1124 ,x_invoice_format_line_type => l_txd_ar_inv_ln_dtls_tbl(i).invoice_format_line_type);
1125 -- End Bug 9067996
1126
1127 --language tl table tbl population
1128 FOR l_fndlanguage_rec IN l_fndlanguage_csr
1129 LOOP
1130 l_txl_ar_inv_lns_tl_tbl(k).id := l_txl_ar_inv_lns_tbl(i).id;
1131 IF k = 1 THEN
1132 --- Base language
1133 l_source_language := l_fndlanguage_rec.language_code;
1134 END IF;
1135 l_txl_ar_inv_lns_tl_tbl(k).LANGUAGE := l_fndlanguage_rec.language_code;
1136 l_txl_ar_inv_lns_tl_tbl(k).source_lang := l_source_language;
1137 l_txl_ar_inv_lns_tl_tbl(k).sfwt_flag := 'N';
1138 l_txl_ar_inv_lns_tl_tbl(k).description := l_def_desc;
1139 l_txl_ar_inv_lns_tl_tbl(k).created_by := g_user_id;
1140 l_txl_ar_inv_lns_tl_tbl(k).creation_date := SYSDATE;
1141 l_txl_ar_inv_lns_tl_tbl(k).last_updated_by := g_user_id;
1142 l_txl_ar_inv_lns_tl_tbl(k).last_update_date := SYSDATE;
1143 l_txl_ar_inv_lns_tl_tbl(k).last_update_login := g_login_id;
1144 l_txl_ar_inv_lns_tl_tbl(k).error_message := NULL;
1145 --increment the looping variable
1146 k := k + 1;
1147 END LOOP;
1148 --increment the looping variable
1149 i := i + 1;
1150 END LOOP;
1151 END LOOP;
1152 END IF;
1153
1154
1155 --sechawla 13-may-2008 6619311 : Update the invoice header after total tax has been derived.
1156 lp_taiv_rec := lp_empty_taiv_rec;
1157 lx_taiv_rec := lp_empty_taiv_rec;
1158
1159 --28-May-2008 sechawla 6619311
1160 --Moved the following code from additional_tai_attr so that corrcet trx type can be derived
1161 --after calculating the total tax amount.
1162 IF l_total_tax_amt < 0 THEN
1163 lp_taiv_rec.irt_id := NULL;
1164
1165 OPEN cm_trx_type_csr(xp_taiv_rec.set_of_books_id, xp_taiv_rec.org_id);
1166 FETCH cm_trx_type_csr INTO l_cust_trx_type_id;
1167 CLOSE cm_trx_type_csr;
1168
1169 lp_taiv_rec.try_id := l_cm_try_id; --28-May-2008 sechawla 6619311
1170 ELSE
1171
1172 OPEN cust_trx_type_csr(xp_taiv_rec.set_of_books_id, xp_taiv_rec.org_id);
1173 FETCH cust_trx_type_csr INTO l_cust_trx_type_id;
1174 CLOSE cust_trx_type_csr;
1175 END IF;
1176
1177 lp_taiv_rec.id := xp_taiv_rec.id;
1178 lp_taiv_rec.amount := l_total_tax_amt;
1179 lp_taiv_rec.cust_trx_type_id := l_cust_trx_type_id;
1180
1181 OKL_TRX_AR_INVOICES_PUB.update_trx_ar_invoices(
1182 p_api_version => p_api_version
1183 ,p_init_msg_list => p_init_msg_list
1184 ,x_return_status => x_return_status
1185 ,x_msg_count => x_msg_count
1186 ,x_msg_data => x_msg_data
1187 ,p_taiv_rec => lp_taiv_rec
1188 ,x_taiv_rec => lx_taiv_rec);
1189
1190 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1191 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1192 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1193 raise OKL_API.G_EXCEPTION_ERROR;
1194 END IF;
1195
1196
1197 --use for all i loop and bulk insert into invoice lines table
1198 ---Bulk insert into okl_txl_ar_inv_lns_b
1199 BEGIN
1200 IF l_txl_ar_inv_lns_tbl.COUNT > 0
1201 THEN
1202 FORALL i IN l_txl_ar_inv_lns_tbl.FIRST .. l_txl_ar_inv_lns_tbl.LAST save
1203 exceptions
1204 INSERT INTO okl_txl_ar_inv_lns_b
1205 VALUES l_txl_ar_inv_lns_tbl(i);
1206 END IF;
1207
1208 EXCEPTION
1209 WHEN OTHERS THEN
1210 IF SQL%bulk_exceptions.COUNT > 0
1211 THEN
1212 FOR i IN 1 .. SQL%bulk_exceptions.COUNT
1213 LOOP
1214 okl_api.set_message(p_app_name => 'OKL',
1215 p_msg_name => 'OKL_TX_TRX_INS_ERR',
1216 p_token1 => 'TABLE_NAME',
1217 p_token1_value => 'OKL_TXL_AR_INV_LNS_B',
1218 p_token2 => 'ERROR_CODE',
1219 p_token2_value => SQLERRM(-sql%bulk_exceptions(i)
1220 .error_code),
1221 p_token3 => 'ITERATION',
1222 p_token3_value => SQL%bulk_exceptions(i)
1223 .error_index);
1224 END LOOP;
1225 x_return_status := okl_api.g_ret_sts_error;
1226 RAISE okl_api.g_exception_error;
1227 END IF;
1228 END;
1229 --invoice line details population
1230 BEGIN
1231 IF l_txd_ar_inv_ln_dtls_tbl.COUNT > 0
1232 THEN
1233 FORALL i IN l_txd_ar_inv_ln_dtls_tbl.FIRST .. l_txd_ar_inv_ln_dtls_tbl.LAST save
1234 exceptions
1235 INSERT INTO OKL_TXD_AR_LN_DTLS_B
1236 VALUES l_txd_ar_inv_ln_dtls_tbl(i);
1237 END IF;
1238
1239 FOR m in l_txd_ar_inv_ln_dtls_tbl.first..l_txd_ar_inv_ln_dtls_tbl.last LOOP
1240 INSERT INTO okl_txd_ar_ln_dtls_tl(
1241 ID,
1242 LANGUAGE,
1243 SOURCE_LANG,
1244 SFWT_FLAG,
1245 DESCRIPTION,
1246 CREATED_BY,
1247 CREATION_DATE,
1248 LAST_UPDATED_BY,
1249 LAST_UPDATE_DATE,
1250 LAST_UPDATE_LOGIN,
1251 ERROR_MESSAGE)
1252 SELECT tld.ID,
1253 l.language_code,
1254 userenv('LANG'),
1255 'N',
1256 l_def_desc,
1257 g_user_id,
1258 sysdate,
1259 g_user_id,
1260 sysdate,
1261 g_login_id,
1262 null
1263 FROM okl_txd_ar_ln_dtls_b tld, fnd_languages l
1264 WHERE l.installed_flag IN ('I', 'B')
1265 and tld.id = l_txd_ar_inv_ln_dtls_tbl(m).id ;
1266 END LOOP;
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 IF SQL%bulk_exceptions.COUNT > 0
1270 THEN
1271 FOR i IN 1 .. SQL%bulk_exceptions.COUNT
1272 LOOP
1273 okl_api.set_message(p_app_name => 'OKL',
1274 p_msg_name => 'OKL_TX_TRX_INS_ERR',
1275 p_token1 => 'TABLE_NAME',
1276 p_token1_value => 'OKL_TXD_AR_LN_DTLS_B',
1277 p_token2 => 'ERROR_CODE',
1278 p_token2_value => SQLERRM(-sql%bulk_exceptions(i)
1279 .error_code),
1280 p_token3 => 'ITERATION',
1281 p_token3_value => SQL%bulk_exceptions(i)
1282 .error_index);
1283 END LOOP;
1284 x_return_status := okl_api.g_ret_sts_error;
1285 RAISE okl_api.g_exception_error;
1286 END IF;
1287 END;
1288 --Bulk insert into okl_txl_ar_inv_lns_tl
1289 --tl table insert
1290 BEGIN
1291 IF l_txl_ar_inv_lns_tl_tbl.COUNT > 0
1292 THEN
1293 FORALL i IN l_txl_ar_inv_lns_tl_tbl.FIRST .. l_txl_ar_inv_lns_tl_tbl.LAST save
1294 exceptions
1295 INSERT INTO okl_txl_ar_inv_lns_tl
1296 VALUES l_txl_ar_inv_lns_tl_tbl(i);
1297 END IF;
1298
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301 IF SQL%bulk_exceptions.COUNT > 0
1302 THEN
1303 FOR i IN 1 .. SQL%bulk_exceptions.COUNT
1304 LOOP
1305 okl_api.set_message(p_app_name => 'OKL',
1306 p_msg_name => 'OKL_TX_TRX_INS_ERR',
1307 p_token1 => 'TABLE_NAME',
1308 p_token1_value => 'OKL_TXL_AR_INV_LNS_B',
1309 p_token2 => 'ERROR_CODE',
1310 p_token2_value => SQLERRM(-sql%bulk_exceptions(i)
1311 .error_code),
1312 p_token3 => 'ITERATION',
1313 p_token3_value => SQL%bulk_exceptions(i)
1314 .error_index);
1315 END LOOP;
1316 x_return_status := okl_api.g_ret_sts_error;
1317 RAISE okl_api.g_exception_error;
1318 END IF;
1319 END;
1320 --line details table
1321 --akrangan ebtax billing impacts coding ends
1322
1323 -- Bug 9523844
1324 IF lx_taiv_rec.trx_status_code = 'SUBMITTED' THEN
1325
1326 IF l_txd_ar_inv_ln_dtls_tbl.COUNT > 0 THEN
1327 FOR n in l_txd_ar_inv_ln_dtls_tbl.first..l_txd_ar_inv_ln_dtls_tbl.last LOOP
1328 l_tldv_tbl(n).id := l_txd_ar_inv_ln_dtls_tbl(n).id;
1329 l_tldv_tbl(n).object_version_number := l_txd_ar_inv_ln_dtls_tbl(n).object_version_number ;
1330 --l_tldv_tbl(n).error_message := l_txd_ar_inv_ln_dtls_tbl(n).error_message ;
1331 --l_tldv_tbl(n).sfwt_flag := l_txd_ar_inv_ln_dtls_tbl(n).sfwt_flag ;
1332 l_tldv_tbl(n).bch_id := l_txd_ar_inv_ln_dtls_tbl(n).bch_id ;
1333 l_tldv_tbl(n).bgh_id := l_txd_ar_inv_ln_dtls_tbl(n).bgh_id ;
1334 l_tldv_tbl(n).idx_id := l_txd_ar_inv_ln_dtls_tbl(n).idx_id ;
1335 l_tldv_tbl(n).tld_id_reverses := l_txd_ar_inv_ln_dtls_tbl(n).tld_id_reverses ;
1336 l_tldv_tbl(n).sty_id := l_txd_ar_inv_ln_dtls_tbl(n).sty_id ;
1337 l_tldv_tbl(n).sel_id := l_txd_ar_inv_ln_dtls_tbl(n).sel_id ;
1338 l_tldv_tbl(n).til_id_details := l_txd_ar_inv_ln_dtls_tbl(n).til_id_details ;
1339 l_tldv_tbl(n).bcl_id := l_txd_ar_inv_ln_dtls_tbl(n).bcl_id ;
1340 l_tldv_tbl(n).bsl_id := l_txd_ar_inv_ln_dtls_tbl(n).bsl_id ;
1341 l_tldv_tbl(n).amount := l_txd_ar_inv_ln_dtls_tbl(n).amount ;
1342 l_tldv_tbl(n).line_detail_number := l_txd_ar_inv_ln_dtls_tbl(n).line_detail_number ;
1343 l_tldv_tbl(n).receivables_invoice_id := l_txd_ar_inv_ln_dtls_tbl(n).receivables_invoice_id ;
1344 l_tldv_tbl(n).late_charge_yn := l_txd_ar_inv_ln_dtls_tbl(n).late_charge_yn ;
1345 --l_tldv_tbl(n).description := l_txd_ar_inv_ln_dtls_tbl(n).description ;
1346 l_tldv_tbl(n).amount_applied := l_txd_ar_inv_ln_dtls_tbl(n).amount_applied ;
1347 l_tldv_tbl(n).date_calculation := l_txd_ar_inv_ln_dtls_tbl(n).date_calculation ;
1348 l_tldv_tbl(n).fixed_rate_yn := l_txd_ar_inv_ln_dtls_tbl(n).fixed_rate_yn ;
1349 l_tldv_tbl(n).inventory_item_id := l_txd_ar_inv_ln_dtls_tbl(n).inventory_item_id;
1350 l_tldv_tbl(n). attribute_category := l_txd_ar_inv_ln_dtls_tbl(n). attribute_category ;
1351 l_tldv_tbl(n).attribute1 := l_txd_ar_inv_ln_dtls_tbl(n).attribute1 ;
1352 l_tldv_tbl(n).attribute2 := l_txd_ar_inv_ln_dtls_tbl(n).attribute2 ;
1353 l_tldv_tbl(n).attribute3 := l_txd_ar_inv_ln_dtls_tbl(n).attribute3 ;
1354 l_tldv_tbl(n).attribute4 := l_txd_ar_inv_ln_dtls_tbl(n).attribute4 ;
1355 l_tldv_tbl(n).attribute5 := l_txd_ar_inv_ln_dtls_tbl(n).attribute5 ;
1356 l_tldv_tbl(n).attribute6 := l_txd_ar_inv_ln_dtls_tbl(n).attribute6 ;
1357 l_tldv_tbl(n).attribute7 := l_txd_ar_inv_ln_dtls_tbl(n).attribute7 ;
1358 l_tldv_tbl(n).attribute8 := l_txd_ar_inv_ln_dtls_tbl(n).attribute8 ;
1359 l_tldv_tbl(n).attribute9 := l_txd_ar_inv_ln_dtls_tbl(n).attribute9 ;
1360 l_tldv_tbl(n).attribute10 := l_txd_ar_inv_ln_dtls_tbl(n).attribute10 ;
1361 l_tldv_tbl(n).attribute11 := l_txd_ar_inv_ln_dtls_tbl(n).attribute11 ;
1362 l_tldv_tbl(n).attribute12 := l_txd_ar_inv_ln_dtls_tbl(n).attribute12 ;
1363 l_tldv_tbl(n).attribute13 := l_txd_ar_inv_ln_dtls_tbl(n).attribute13 ;
1364 l_tldv_tbl(n).attribute14 := l_txd_ar_inv_ln_dtls_tbl(n).attribute14 ;
1365 l_tldv_tbl(n).attribute15 := l_txd_ar_inv_ln_dtls_tbl(n).attribute15 ;
1366 l_tldv_tbl(n).request_id := l_txd_ar_inv_ln_dtls_tbl(n).request_id ;
1367 l_tldv_tbl(n).program_application_id := l_txd_ar_inv_ln_dtls_tbl(n).program_application_id ;
1368 l_tldv_tbl(n).program_id := l_txd_ar_inv_ln_dtls_tbl(n).program_id ;
1369 l_tldv_tbl(n).program_update_date := l_txd_ar_inv_ln_dtls_tbl(n).program_update_date ;
1370 l_tldv_tbl(n).org_id := l_txd_ar_inv_ln_dtls_tbl(n).org_id ;
1371 l_tldv_tbl(n).inventory_org_id := l_txd_ar_inv_ln_dtls_tbl(n).inventory_org_id ;
1372 l_tldv_tbl(n).created_by := l_txd_ar_inv_ln_dtls_tbl(n).created_by ;
1373 l_tldv_tbl(n).creation_date := l_txd_ar_inv_ln_dtls_tbl(n).creation_date ;
1374 l_tldv_tbl(n).last_updated_by := l_txd_ar_inv_ln_dtls_tbl(n).last_updated_by ;
1375 l_tldv_tbl(n).last_update_date := l_txd_ar_inv_ln_dtls_tbl(n).last_update_date ;
1376 l_tldv_tbl(n).last_update_login := l_txd_ar_inv_ln_dtls_tbl(n).last_update_login ;
1377 l_tldv_tbl(n).TXL_AR_LINE_NUMBER := l_txd_ar_inv_ln_dtls_tbl(n).TXL_AR_LINE_NUMBER ;
1378 l_tldv_tbl(n).INVOICE_FORMAT_TYPE := l_txd_ar_inv_ln_dtls_tbl(n).INVOICE_FORMAT_TYPE ;
1379 l_tldv_tbl(n).INVOICE_FORMAT_LINE_TYPE := l_txd_ar_inv_ln_dtls_tbl(n).INVOICE_FORMAT_LINE_TYPE ;
1380 l_tldv_tbl(n).LATE_CHARGE_ASSESS_DATE := l_txd_ar_inv_ln_dtls_tbl(n).LATE_CHARGE_ASSESS_DATE ;
1381 l_tldv_tbl(n).LATE_INT_ASSESS_DATE := l_txd_ar_inv_ln_dtls_tbl(n).LATE_INT_ASSESS_DATE;
1382 l_tldv_tbl(n).LATE_CHARGE_ASS_YN := l_txd_ar_inv_ln_dtls_tbl(n).LATE_CHARGE_ASS_YN ;
1383 l_tldv_tbl(n).LATE_INT_ASS_YN := l_txd_ar_inv_ln_dtls_tbl(n).LATE_INT_ASS_YN ;
1384 l_tldv_tbl(n).INVESTOR_DISB_STATUS := l_txd_ar_inv_ln_dtls_tbl(n).INVESTOR_DISB_STATUS;
1385 l_tldv_tbl(n).INVESTOR_DISB_ERR_MG := l_txd_ar_inv_ln_dtls_tbl(n).INVESTOR_DISB_ERR_MG;
1386 l_tldv_tbl(n).DATE_DISBURSED := l_txd_ar_inv_ln_dtls_tbl(n).DATE_DISBURSED ;
1387 l_tldv_tbl(n).PAY_STATUS_CODE := l_txd_ar_inv_ln_dtls_tbl(n).PAY_STATUS_CODE ;
1388 l_tldv_tbl(n).RBK_ORI_INVOICE_NUMBER := l_txd_ar_inv_ln_dtls_tbl(n).RBK_ORI_INVOICE_NUMBER ;
1389 l_tldv_tbl(n).RBK_ORI_INVOICE_LINE_NUMBER := l_txd_ar_inv_ln_dtls_tbl(n).RBK_ORI_INVOICE_LINE_NUMBER ;
1390 l_tldv_tbl(n).RBK_ADJUSTMENT_DATE := l_txd_ar_inv_ln_dtls_tbl(n).RBK_ADJUSTMENT_DATE ;
1391 l_tldv_tbl(n).KHR_ID := l_txd_ar_inv_ln_dtls_tbl(n).KHR_ID ;
1392 l_tldv_tbl(n).KLE_ID := l_txd_ar_inv_ln_dtls_tbl(n).KLE_ID ;
1393 l_tldv_tbl(n).TAX_AMOUNT := l_txd_ar_inv_ln_dtls_tbl(n).TAX_AMOUNT ;
1394 END LOOP;
1395
1396 OKL_INTERNAL_BILLING_PVT.create_accounting_dist(p_api_version => p_api_version ,
1397 p_init_msg_list => p_init_msg_list ,
1398 x_return_status => x_return_status ,
1399 x_msg_count => x_msg_count ,
1400 x_msg_data => x_msg_data ,
1401 p_tldv_tbl => l_tldv_tbl,
1402 p_tai_id => lx_taiv_rec.ID
1403 );
1404
1405 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1406 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1407 x_return_status := x_return_status;
1408 END IF;
1409 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1410 END IF;
1411 END IF;
1412 END IF;
1413 -- End Bug 9523844
1414
1415 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1416
1417 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1418
1419 EXCEPTION
1420 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1421
1422 --sechawla 13-may-2008 6619311
1423 IF zx_lines_csr%ISOPEN THEN
1424 CLOSE zx_lines_csr;
1425 END IF;
1426
1427 IF inv_org_id_csr%ISOPEN THEN
1428 CLOSE inv_org_id_csr;
1429 END IF;
1430
1431 IF l_fndlanguage_csr%ISOPEN THEN
1432 CLOSE l_fndlanguage_csr;
1433 END IF;
1434
1435 IF l_billedtaxablelines_csr%ISOPEN THEN
1436 CLOSE l_billedtaxablelines_csr;
1437 END IF;
1438
1439 IF l_allbilledtaxablelines_csr%ISOPEN THEN
1440 CLOSE l_allbilledtaxablelines_csr;
1441 END IF;
1442
1443 IF l_upfront_tax_treatment_csr%ISOPEN THEN
1444 CLOSE l_upfront_tax_treatment_csr;
1445 END IF;
1446
1447 IF upfront_tax_csr%ISOPEN THEN
1448 CLOSE upfront_tax_csr;
1449 END IF;
1450
1451 --28-May-2008 sechawla 6619311
1452 IF cm_trx_type_csr%ISOPEN THEN
1453 CLOSE cm_trx_type_csr;
1454 END IF;
1455
1456 IF cust_trx_type_csr%ISOPEN THEN
1457 CLOSE cust_trx_type_csr;
1458 END IF;
1459
1460 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1461 p_api_name => l_api_name,
1462 p_pkg_name => g_pkg_name,
1463 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1464 x_msg_count => x_msg_count,
1465 x_msg_data => x_msg_data,
1466 p_api_type => g_api_type);
1467
1468 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1469
1470 --sechawla 13-may-2008 6619311
1471 IF zx_lines_csr%ISOPEN THEN
1472 CLOSE zx_lines_csr;
1473 END IF;
1474
1475 IF inv_org_id_csr%ISOPEN THEN
1476 CLOSE inv_org_id_csr;
1477 END IF;
1478
1479 IF l_fndlanguage_csr%ISOPEN THEN
1480 CLOSE l_fndlanguage_csr;
1481 END IF;
1482
1483 IF l_billedtaxablelines_csr%ISOPEN THEN
1484 CLOSE l_billedtaxablelines_csr;
1485 END IF;
1486
1487 IF l_allbilledtaxablelines_csr%ISOPEN THEN
1488 CLOSE l_allbilledtaxablelines_csr;
1489 END IF;
1490
1491 IF l_upfront_tax_treatment_csr%ISOPEN THEN
1492 CLOSE l_upfront_tax_treatment_csr;
1493 END IF;
1494
1495 IF upfront_tax_csr%ISOPEN THEN
1496 CLOSE upfront_tax_csr;
1497 END IF;
1498
1499 --28-May-2008 sechawla 6619311
1500 IF cm_trx_type_csr%ISOPEN THEN
1501 CLOSE cm_trx_type_csr;
1502 END IF;
1503
1504 IF cust_trx_type_csr%ISOPEN THEN
1505 CLOSE cust_trx_type_csr;
1506 END IF;
1507
1508 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1509 p_api_name => l_api_name,
1510 p_pkg_name => g_pkg_name,
1511 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1512 x_msg_count => x_msg_count,
1513 x_msg_data => x_msg_data,
1514 p_api_type => g_api_type);
1515
1516 WHEN OTHERS THEN
1517 --sechawla 13-may-2008 6619311
1518 IF zx_lines_csr%ISOPEN THEN
1519 CLOSE zx_lines_csr;
1520 END IF;
1521
1522 IF inv_org_id_csr%ISOPEN THEN
1523 CLOSE inv_org_id_csr;
1524 END IF;
1525
1526 IF l_fndlanguage_csr%ISOPEN THEN
1527 CLOSE l_fndlanguage_csr;
1528 END IF;
1529
1530 IF l_billedtaxablelines_csr%ISOPEN THEN
1531 CLOSE l_billedtaxablelines_csr;
1532 END IF;
1533
1534 IF l_allbilledtaxablelines_csr%ISOPEN THEN
1535 CLOSE l_allbilledtaxablelines_csr;
1536 END IF;
1537
1538 IF l_upfront_tax_treatment_csr%ISOPEN THEN
1539 CLOSE l_upfront_tax_treatment_csr;
1540 END IF;
1541
1542 IF upfront_tax_csr%ISOPEN THEN
1543 CLOSE upfront_tax_csr;
1544 END IF;
1545
1546 --28-May-2008 sechawla 6619311
1547 IF cm_trx_type_csr%ISOPEN THEN
1548 CLOSE cm_trx_type_csr;
1549 END IF;
1550
1551 IF cust_trx_type_csr%ISOPEN THEN
1552 CLOSE cust_trx_type_csr;
1553 END IF;
1554
1555 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1556 p_api_name => l_api_name,
1557 p_pkg_name => g_pkg_name,
1558 p_exc_name => 'OTHERS',
1559 x_msg_count => x_msg_count,
1560 x_msg_data => x_msg_data,
1561 p_api_type => g_api_type);
1562
1563
1564 END Bill_Upfront_Tax;
1565
1566 END Okl_Bill_Upfront_Tax_Pvt;