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