[Home] [Help]
PACKAGE BODY: APPS.OKL_BPD_ADVANCED_BILLING_PVT
Source
1 PACKAGE BODY OKL_BPD_ADVANCED_BILLING_PVT AS
2 /* $Header: OKLRABLB.pls 120.41.12020000.2 2012/11/21 12:12:14 venkatho ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9 --start: | 02-APR-07 cklee R12 Billing enhancement project |
10 G_MODULE VARCHAR2(255) := 'LEASE.RECEIVABLES.BILLING';
11 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
12 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
13 -- vpanwar 20-July-07 -- interface line length increase from 30 to 150
14 G_AR_DATA_LENGTH CONSTANT VARCHAR2(4) := '150';
15 -- end vpanwar 20-July-07 -- interface line length increase from 30 to 150
16 G_ACC_SYS_OPTION VARCHAR2(4);
17 -- G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_BPD_ADVANCED_BILLING_PVT';
18 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
19 G_REQUIRED_VALUE CONSTANT VARCHAR2(30) := 'OKL_REQUIRED_VALUE';
20 G_COL_NAME_TOKEN CONSTANT VARCHAR2(30) := OKL_API.G_COL_NAME_TOKEN;
21
22 ----------------------------------------------------------------------------------
23 -- Start of comments
24 --
25 -- Procedure Name : nullout_rec_method
26 -- Description : This logic is migrated from okl_internal_to_external
27 -- Business Rules :
28 -- Parameters :
29 --
30 -- Version : 1.0
31 -- End of comments
32 ----------------------------------------------------------------------------------
33 PROCEDURE nullout_rec_method(
34 p_contract_id IN NUMBER
35 ,p_Quote_number IN NUMBER
36 ,p_sty_id IN NUMBER
37 ,p_customer_bank_account_id IN NUMBER
38 ,p_receipt_method_id IN NUMBER -- irm_id
39 ,p_payment_trxn_extension_id IN NUMBER --Bug 7623549
40 ,x_customer_bank_account_id OUT NOCOPY NUMBER
41 ,x_receipt_method_id OUT NOCOPY NUMBER
42 ,x_payment_trxn_extension_id OUT NOCOPY NUMBER --Bug 7623549
43 )
44 IS
45 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
46 lx_remrkt_sty_id number;
47
48 BEGIN
49
50 IF p_contract_id IS NOT NULL THEN
51 --bug 5160519 : Sales Order Billing
52 -- Order Management sales for remarketing, these billing details are
53 --purely from the Order, so if payment method,Bank Account is not passed,
54 --then pass as NULL.
55
56 --get primary stream type for remarketing stream
57 Okl_Streams_Util.get_primary_stream_type(p_contract_id,
58 'ASSET_SALE_RECEIVABLE',
59 l_return_status,
60 lx_remrkt_sty_id);
61
62 IF l_return_status = Okl_Api.g_ret_sts_success THEN
63
64 IF(lx_remrkt_sty_id = p_sty_id) THEN
65
66 x_customer_bank_account_id := NULL;
67 x_receipt_method_id := NULL;
68 x_payment_trxn_extension_id:=NULL; -- Bug 7623549
69 ELSE
70 x_customer_bank_account_id := p_customer_bank_account_id;
71 x_receipt_method_id := p_receipt_method_id;
72 x_payment_trxn_extension_id:= p_payment_trxn_extension_id; -- Bug 7623549
73
74 END IF;
75 END IF;
76
77 --bug 5160519 : end
78
79 --bug 5160519 : Lease Vendor Billing
80 -- For termination quote to Lease Vendor AND repurchase quote to Lease Vendor
81 -- on VPA...the payment method should be taken from the Vendor Billing Details,
82 -- if NULL, then as per above, pass nothing to AR and let AR default to Primary
83 -- payment method
84
85 IF p_Quote_number IS NOT NULL THEN
86 -- if termination record
87 x_receipt_method_id := NULL;
88 x_customer_bank_account_id := NULL;
89 x_payment_trxn_extension_id:=NULL; -- Bug 7623549
90 ELSE
91 x_customer_bank_account_id := p_customer_bank_account_id;
92 x_receipt_method_id := p_receipt_method_id;
93 x_payment_trxn_extension_id:= p_payment_trxn_extension_id; -- Bug 7623549
94
95 END IF;
96
97 END IF; -- IF p_contract_id IS NOT NULL THEN
98 --bug 5160519:end
99
100 --bug 5160519
101 --if not remarketing invoice
102
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
107 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in nullout_rec_method');
108 END IF;
109
110 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
111 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_BPD_ADVANCED_BILLING_PVT',
112 'EXCEPTION :'||'OTHERS');
113 END IF;
114
115 end nullout_rec_method;
116
117 ----------------------------------------------------------------------------------
118 -- Start of comments
119 --
120 -- Procedure Name : Get_chr_inv_grp
121 -- Description :
122 -- Business Rules :
123 -- Parameters :
124 --
125 -- Version : 1.0
126 -- End of comments
127 ----------------------------------------------------------------------------------
128 PROCEDURE Get_chr_inv_grp(
129 p_inf_id IN NUMBER
130 ,p_sty_id IN NUMBER
131 ,x_group_by_contract_yn OUT NOCOPY VARCHAR2
132 ,x_contract_level_yn OUT NOCOPY VARCHAR2
133 ,x_group_asset_yn OUT NOCOPY VARCHAR2
134 ,x_invoice_group OUT NOCOPY VARCHAR2
135 )
136 is
137
138 CURSOR inv_format_csr ( p_format_id IN NUMBER, p_stream_id IN NUMBER ) IS
139 SELECT inf.contract_level_yn, -- Multi-contract Y/N
140 ity.group_by_contract_yn, -- Provide Contract Details
141 ity.group_asset_yn, -- Combine Assets
142 inf.name -- invoice group
143 FROM okl_invoice_formats_v inf,
144 okl_invoice_types_v ity,
145 okl_invc_line_types_v ilt,
146 okl_invc_frmt_strms_v frs,
147 okl_strm_type_v sty
148 WHERE inf.id = ity.inf_id
149 AND ity.inf_id = p_format_id
150 AND ilt.ity_id = ity.id
151 AND frs.ilt_id = ilt.id
152 AND sty.id = frs.sty_id
153 AND frs.sty_id = p_stream_id;
154
155 CURSOR inv_format_default_csr ( p_format_id IN NUMBER ) IS
156 SELECT inf.contract_level_yn, -- Multi-contract Y/N
157 ity.group_by_contract_yn, -- Provide Contract Details
158 ity.group_asset_yn, -- Combine Assets
159 inf.name -- invoice group
160 FROM okl_invoice_formats_v inf,
161 okl_invoice_types_v ity,
162 okl_invc_line_types_v ilt
163 WHERE inf.id = ity.inf_id
164 AND ity.inf_id = p_format_id
165 AND ilt.ity_id = ity.id;
166
167 begin
168
169 IF p_inf_id IS NOT NULL and p_sty_id IS NOT NULL THEN
170
171 OPEN inv_format_csr ( p_inf_id, p_sty_id);
172 FETCH inv_format_csr INTO x_contract_level_yn,
173 x_group_by_contract_yn,
174 x_group_asset_yn,
175 x_invoice_group;
176 CLOSE inv_format_csr;
177
178 ELSIF p_inf_id IS NOT NULL and p_sty_id IS NULL THEN
179
180 OPEN inv_format_default_csr ( p_inf_id);
181 FETCH inv_format_default_csr INTO x_contract_level_yn,
182 x_group_by_contract_yn,
183 x_group_asset_yn,
184 x_invoice_group;
185 CLOSE inv_format_default_csr;
186
187 ELSE
188
189 x_group_by_contract_yn := NULL;
190 x_contract_level_yn := NULL;
191 x_group_asset_yn := NULL;
192 x_invoice_group := NULL;
193
194 END IF;
195
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
200 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in Get_chr_inv_grp');
201 END IF;
202
203 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
204 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_BPD_ADVANCED_BILLING_PVT',
205 'EXCEPTION :'||'OTHERS');
206 END IF;
207
208 end Get_chr_inv_grp;
209
210 ----------------------------------------------------------------------------------
211 -- Start of comments
212 --
213 -- Procedure Name : Get_acct_disb
214 -- Description :
215 -- Business Rules :
216 -- Parameters :
217 --
218 -- Version : 1.0
219 -- End of comments
220 ----------------------------------------------------------------------------------
221 PROCEDURE Get_acct_disb(
222 p_tld_id IN NUMBER
223 ,x_account_class OUT NOCOPY VARCHAR2
224 ,x_dist_amount OUT NOCOPY VARCHAR2
225 ,x_dist_percent OUT NOCOPY VARCHAR2
226 ,x_code_combination_id OUT NOCOPY VARCHAR2
227 )
228 is
229
230 -- Selects distributions created by the accounting Engine
231 CURSOR acc_dstrs_csr(p_source_id IN NUMBER, p_source_table IN VARCHAR2) IS
232 SELECT cr_dr_flag,
233 code_combination_id,
234 source_id,
235 amount,
236 percentage,
237 --Start code changes for rev rec by fmiao on 10/05/2004
238 NVL(comments, '-99') comments --End code changes for rev rec by fmiao on 10/05/2004
239 FROM okl_trns_acc_dstrs
240 WHERE source_id = p_source_id
241 AND source_table = p_source_table;
242
243 begin
244
245
246 FOR acc_dtls_rec IN acc_dstrs_csr(p_tld_id, 'OKL_TXD_AR_LN_DTLS_B')
247 LOOP
248
249 x_code_combination_id := acc_dtls_rec.code_combination_id;
250 x_dist_amount := acc_dtls_rec.amount;
251 x_dist_percent := acc_dtls_rec.percentage;
252
253 IF acc_dtls_rec.amount > 0 THEN
254
255 IF(acc_dtls_rec.cr_dr_flag = 'C') THEN
256 x_account_class := 'REV';
257 ELSE
258 x_account_class := 'REC';
259 END IF;
260 ELSE
261 IF(acc_dtls_rec.cr_dr_flag = 'C') THEN
262 x_account_class := 'REC';
263 ELSE
264 x_account_class := 'REV';
265 END IF;
266 END IF;
267
268 --Start code changes for rev rec by fmiao on 10/05/2004
269 IF(acc_dtls_rec.comments = 'CASH_RECEIPT'
270 AND x_account_class <> 'REC')
271 OR(acc_dtls_rec.comments <> 'CASH_RECEIPT') THEN
272
273 IF(acc_dtls_rec.comments = 'CASH_RECEIPT') THEN
274 x_account_class := 'UNEARN';
275 END IF;
276
277 END IF;
278
279 END LOOP;
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
284 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in Get_acct_disb');
285 END IF;
286
287 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
288 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_BPD_ADVANCED_BILLING_PVT',
289 'EXCEPTION :'||'OTHERS');
290 END IF;
291
292 end Get_acct_disb;
293
294 ----------------------------------------------------------------------------------
295 -- Start of comments
296 --
297 -- Procedure Name : get_customer_id
298 -- Description :
299 -- Business Rules :
300 -- Parameters :
301 --
302 -- Version : 1.0
303 -- End of comments
304 ----------------------------------------------------------------------------------
305
306 PROCEDURE get_customer_id
307 ( l_contract_number IN VARCHAR2
308 ,l_customer_id OUT NOCOPY NUMBER
309 )
310 IS
311 --modified by pgomes on 21-aug-2003 for rules migration
312 CURSOR get_khr_id_csr ( p_contract_number VARCHAR2 ) IS
313 SELECT cust_acct_id
314 FROM okc_k_headers_b
315 where contract_number = p_contract_number;
316
317 --commented out by pgomes on 21-aug-2003 for rules migration
318 BEGIN
319
320 --modified by pgomes on 21-aug-2003 for rules migration
321 -- Get the customer acct id
322 OPEN get_khr_id_csr(l_contract_number);
323 FETCH get_khr_id_csr INTO l_customer_id;
324 CLOSE get_khr_id_csr;
325
326 --commented out by pgomes on 21-aug-2003 for rules migration
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
331 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in Get_Customer_Id');
332 END IF;
333
334 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
335 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_BPD_ADVANCED_BILLING_PVT',
336 'EXCEPTION :'||'OTHERS');
337 END IF;
338
339 END get_customer_id;
340 --end: | 02-APR-07 cklee R12 Billing enhancement project |
341
342
343 --------------------------------------------
344 -- Prepare batch_source Name record
345 --------------------------------------------
346 PROCEDURE PREPARE_BATCH_SOURCE_REC(
347 x_return_status OUT NOCOPY VARCHAR2
348 ,p_trx_date IN DATE
349 ,l_batch_source_rec OUT NOCOPY AR_INVOICE_API_PUB.batch_source_rec_type)
350 IS
351 CURSOR batch_src_csr( p_in_date DATE ) IS
352 SELECT BATCH_SOURCE_ID
353 FROM ra_batch_sources_all
354 WHERE NAME = 'OKL_MANUAL'
355 AND START_DATE <= p_in_date
356 AND (END_DATE IS NULL OR p_in_date > END_DATE)
357 --gkhuntet staRT
358 AND ORG_ID = MO_GLOBAL.get_current_org_id();
359 --gkhuntet end.
360 BEGIN
361
362 x_return_status := OKL_API.G_RET_STS_SUCCESS;
363 -------------------------------------------
364 -- Fetch Batch Source Id for OKL_CONTRACTS
365 -------------------------------------------
366 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Fetching Batch Source');
367 -- Establish Savepoint for rollback
368 DBMS_TRANSACTION.SAVEPOINT('PREPARE_BATCH_SOURCE_REC_PVT');
369 -- Savepoint established
370
371 OPEN batch_src_csr( p_trx_date );
372 FETCH batch_src_csr INTO l_batch_source_rec.batch_source_id;
373 IF (batch_src_csr%NOTFOUND) THEN
374 CLOSE batch_src_csr;
375 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error: Unable to retrieve Batch Source');
376 RAISE Okl_Api.G_EXCEPTION_ERROR;
377 END IF;
378 CLOSE batch_src_csr;
379 l_batch_source_rec.default_date := p_trx_date;
380
381 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'In PREPARE_BATCH_SOURCE_REC, p_trx_date: '||p_trx_date
382 ||' l_batch_source_rec.default_date: '||l_batch_source_rec.default_date);
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
387 END PREPARE_BATCH_SOURCE_REC;
388
389
390 --------------------------------------------
391 -- Prepare Transaction Header Table
392 --------------------------------------------
393 PROCEDURE PREPARE_TRX_HDR_TBL(
394 x_return_status OUT NOCOPY VARCHAR2
395 --start: | 02-APR-07 cklee R12 Billing enhancement project |
396 -- ,r_xsiv_rec IN Okl_Ext_Sell_Invs_Pub.xsiv_rec_type
397 -- ,r_xlsv_rec IN Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type
398 ,xfer_rec IN OKL_ARINTF_PVT.xfer_rec_type
399 --end: | 02-APR-07 cklee R12 Billing enhancement project |
400 -- rkuttiya added for bug 7209767
401 ,p_source IN VARCHAR2
402 ,l_trx_header_tbl OUT NOCOPY AR_INVOICE_API_PUB.trx_header_tbl_type
403 )
404 IS
405
406 lx_customer_id number;
407 --Bug 7623549 START
408
409 --11-MAY-10 sechawla 9692959
410 --l_api_version NUMBER := '1.0';
411 l_api_version NUMBER := 1.0;
412 --11-MAY-10 sechawla 9692959
413
414 l_init_msg_list VARCHAR2(1):= OKL_API.G_FALSE;
415 l_khr_id NUMBER;
416 l_return_status VARCHAR2(100);
417 l_msg_count NUMBER;
418 l_msg_data VARCHAR2(2000);
419 l_payment_trxn_extension_id NUMBER := NULL;
420 l_creation_method_code AR_RECEIPT_CLASSES.CREATION_METHOD_CODE%TYPE;
421 l_bank_line_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
422 --Bug 7623549 END
423
424 l_ship_to NUMBER;
425 l_kle_id NUMBER;
426 l_top_kle_id NUMBER;
427 l_chr_id okc_k_lines_b.chr_id%TYPE;
428 l_asset_name okc_k_lines_v.name%TYPE;
429 l_install_location_id NUMBER;
430 l_location_id NUMBER;
431 --start: 02-APR-07 cklee R12 Billing enhancement project |
432 -- l_exempt_flg okl_ext_sell_invs_v.tax_exempt_flag%TYPE;
433 l_exempt_flg okl_trx_ar_invoices_b.tax_exempt_flag%TYPE;
434 --end: 02-APR-07 cklee R12 Billing enhancement project |
435
436 --start: 02-APR-07 cklee R12 Billing enhancement project |
437 /*commented out for R12
438 CURSOR get_kle_id ( p_lsm_id NUMBER ) IS
439 SELECT kle_id
440 FROM OKL_CNSLD_AR_STRMS_V
441 WHERE id = p_lsm_id;
442
443 CURSOR check_top_line ( p_cle_id NUMBER ) IS
444 SELECT chr_id
445 FROM okc_k_lines_b
446 where id = p_cle_id;
447
448 CURSOR derive_top_line_id (p_lsm_id NUMBER) IS
449 SELECT FA.ID
450 FROM OKC_K_HEADERS_B CHR,
451 OKC_K_LINES_B TOP_CLE,
452 OKC_LINE_STYLES_b TOP_LSE,
453 OKC_K_LINES_B SUB_CLE,
454 OKC_LINE_STYLES_b SUB_LSE,
455 OKC_K_ITEMS CIM,
456 OKC_K_LINES_V FA,
457 OKC_LINE_STYLES_B AST_LSE,
458 OKL_CNSLD_AR_STRMS_B LSM
459 WHERE
460 CHR.ID = TOP_CLE.DNZ_CHR_ID AND
461 TOP_CLE.LSE_ID = TOP_LSE.ID AND
462 TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
463 TOP_CLE.ID = SUB_CLE.CLE_ID AND
464 SUB_CLE.LSE_ID = SUB_LSE.ID AND
465 SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
466 SUB_CLE.ID = LSM.KLE_ID AND
467 LSM.ID = p_lsm_id AND
468 CIM.CLE_ID = SUB_CLE.ID AND
469 CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
470 CIM.OBJECT1_ID1 = FA.ID AND
471 FA.LSE_ID = AST_LSE.ID AND
472 AST_LSE.LTY_CODE = 'FREE_FORM1';
473 */
474 --end: 02-APR-07 cklee R12 Billing enhancement project |
475
476 CURSOR top_line_asset ( p_cle_id NUMBER ) IS
477 SELECT name
478 FROM okc_k_lines_v
479 WHERE id = p_cle_id;
480
481
482 CURSOR Ship_to_csr( p_kle_top_line IN NUMBER ) IS
483 SELECT --cim.object1_id1 item_instance,
484 --cim.object1_id2 "#",
485 csi.install_location_id
486 , csi.location_id
487 FROM csi_item_instances csi,
488 okc_k_items cim,
489 okc_k_lines_b inst,
490 okc_k_lines_b ib,
491 okc_line_styles_b lse
492 WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
493 AND cim.cle_id = ib.id
494 AND ib.cle_id = inst.id
495 AND inst.lse_id = lse.id
496 AND lse.lty_code = 'FREE_FORM2'
497 AND inst.cle_id = p_kle_top_line;
498
499 CURSOR Ship_to_csr2( p_customer_num NUMBER, p_install_location NUMBER, p_location NUMBER, p_org_id NUMBER ) IS
500 SELECT a.CUST_ACCT_SITE_ID
501 FROM hz_cust_acct_sites_all a,
502 hz_cust_site_uses_all b,
503 hz_party_sites c
504 WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID AND
505 b.site_use_code = 'SHIP_TO' AND
506 a.party_site_id = c.party_site_id AND
507 a.cust_account_id = p_customer_num AND
508 a.org_id = p_org_id AND
509 c.party_site_id = p_install_location AND
510 c.location_id = p_location;
511 CURSOR tax_exmpt_csr ( p_flg VARCHAR2 ) IS
512 SELECT decode(p_flg,'S','S','E','E','R','R','S')
513 FROM DUAL;
514
515 --stmathew - Bug 4372869/4222231..start
516 CURSOR sales_rep_csr(p_salesrep_id IN ra_salesreps.salesrep_id%TYPE) IS
517 SELECT SALESREP_ID, NAME
518 FROM ra_salesreps
519 WHERE SALESREP_ID = p_salesrep_id;
520
521 --cursor to fetch the sales rep for the contract.
522 CURSOR get_sales_rep(p_contract_number okc_k_headers_b.contract_number%TYPE) IS
523 SELECT contact.object1_id1
524 FROM okc_k_headers_b hdr, okc_contacts contact
525 WHERE contact.dnz_chr_id = hdr.id
526 AND hdr.contract_number = p_contract_number
527 AND contact.cro_code = 'SALESPERSON';
528
529 l_salesrep_id ra_salesreps.SALESREP_ID%TYPE;
530 l_salesrep_name ra_salesreps.NAME%TYPE;
531 l_sales_person okc_contacts_v.object1_id1%TYPE;
532 --stmathew - Bug 4372869/4222231..end
533 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
534 l_is_top_line number;
535 is_top_line_flag boolean;
536
537 CURSOR is_top_line ( p_cle_id NUMBER ) IS
538 SELECT 1
539 FROM okc_k_lines_b kle
540 where kle.id = p_cle_id
541 and kle.cle_id is null; -- it's top line
542
543 CURSOR get_top_line ( p_cle_id NUMBER ) IS
544 select cle.id--, lse.lty_code
545 from okc_k_lines_b cle--,
546 -- okc_line_styles_b lse
547 -- where lse.id = cle.lse_id
548 -- and cle.cle_id is null
549 where cle.cle_id is null -- it's top line
550 start with cle.id = p_cle_id
551 connect by cle.id = prior cle.cle_id;
552
553 CURSOR get_top_line_name ( p_cle_id NUMBER ) IS
554 select cle.name
555 from OKC_K_LINES_V cle
556 where cle.id = p_cle_id;
557
558 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
559
560 lxfer_rec OKL_ARINTF_PVT.xfer_rec_type;
561
562 -- rmunjulu R12 Fixes
563 l_customer_address_id OKL_TRX_AR_INVOICES_V.ibt_id%TYPE;
564 l_customer_bank_account_id OKL_TRX_AR_INVOICES_V.customer_bank_account_id%TYPE;
565 l_recept_method_id OKL_TRX_AR_INVOICES_V.irm_id%TYPE;
566
567 BEGIN
568 -- Assign IN record to local record
569 lxfer_rec := xfer_rec;
570
571 x_return_status := OKL_API.G_RET_STS_SUCCESS;
572
573 --start: 02-APR-07 cklee R12 Billing enhancement project |
574 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
575 -- 1. Check if it's a top line
576 OPEN is_top_line(lxfer_rec.kle_id);
577 FETCH is_top_line INTO l_is_top_line;
578 is_top_line_flag := is_top_line%FOUND;
579 CLOSE is_top_line;
580
581 -- 2. get top line if needed
582 IF NOT is_top_line_flag THEN
583 OPEN get_top_line(lxfer_rec.kle_id);
584 FETCH get_top_line INTO lxfer_rec.kle_id;
585 CLOSE get_top_line;
586 END IF;
587
588 -- 3. get top line name (asset number)
589 OPEN get_top_line_name(lxfer_rec.kle_id);
590 FETCH get_top_line_name INTO lxfer_rec.ASSET_NUMBER;
591 CLOSE get_top_line_name;
592
593 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
594
595 l_chr_id := lxfer_rec.khr_id;
596 l_kle_id := lxfer_rec.kle_id;
597 /* commented out for R12
598 -- To find the top line kle_id
599 l_kle_id := NULL;
600 l_top_kle_id := NULL;
601
602 -- Find top line kle_id
603 OPEN get_kle_id ( r_xlsv_rec.lsm_id );
604 FETCH get_kle_id INTO l_top_kle_id;
605 CLOSE get_kle_id;
606
607 l_chr_id := NULL;
608
609 OPEN check_top_line( l_top_kle_id );
610 FETCH check_top_line INTO l_chr_id;
611 CLOSE check_top_line;
612
613 IF l_chr_id IS NOT NULL THEN
614 l_kle_id := l_top_kle_id;
615 ELSE
616 l_top_kle_id := NULL;
617 OPEN derive_top_line_id ( r_xlsv_rec.lsm_id );
618 FETCH derive_top_line_id INTO l_top_kle_id;
619 CLOSE derive_top_line_id;
620 l_kle_id := l_top_kle_id;
621 END IF;
622 */
623 --end: 02-APR-07 cklee R12 Billing enhancement project |
624
625 l_asset_name := NULL;
626 OPEN top_line_asset ( l_kle_id );
627 FETCH top_line_asset INTO l_asset_name;
628 CLOSE top_line_asset;
629
630 OPEN ship_to_csr(l_kle_id);
631 FETCH ship_to_csr INTO l_install_location_id, l_location_id;
632 CLOSE ship_to_csr;
633
634 --start: 02-APR-07 cklee R12 Billing enhancement project |
635 -- Migrated the following logic from oklarintf_pvt
636 -- Check if Vendor is the same as the customer on the Contract
637 lx_customer_id := NULL;
638 get_customer_id( lxfer_rec.CONTRACT_NUMBER ,lx_customer_id );
639
640 l_ship_to := NULL;
641 --start: 02-APR-07 cklee R12 Billing enhancement project |
642 -- OPEN Ship_to_csr2( r_xsiv_rec.CUSTOMER_ID, l_install_location_id, l_location_id, r_xsiv_rec.ORG_ID);
643 OPEN Ship_to_csr2( lx_customer_id, l_install_location_id, l_location_id, lxfer_rec.ORG_ID);
644 --end: 02-APR-07 cklee R12 Billing enhancement project |
645 FETCH Ship_to_csr2 INTO l_ship_to;
646 CLOSE Ship_to_csr2;
647
648 IF ( lx_customer_id = lxfer_rec.CUSTOMER_ID ) THEN
649 NULL;
650 ELSE
651 l_ship_to := NULL;
652 END IF;
653 --end: 02-APR-07 cklee R12 Billing enhancement project |
654
655 --stmathew - Bug 4372869/4222231..start
656 l_salesrep_id := NULL;
657 l_salesrep_name := NULL;
658 --if the contract has an associated sales rep, fetch the
659 --salesrep id and name
660 --start: 02-APR-07 cklee R12 Billing enhancement project |
661 -- OPEN get_sales_rep(r_xlsv_rec.xtrx_contract);
662 OPEN get_sales_rep(lxfer_rec.contract_number);
663 --end: 02-APR-07 cklee R12 Billing enhancement project |
664 FETCH get_sales_rep INTO l_sales_person;
665 IF get_sales_rep%FOUND THEN
666 OPEN sales_rep_csr(l_sales_person);
667 FETCH sales_rep_csr INTO l_salesrep_id, l_salesrep_name;
668 CLOSE sales_rep_csr;
669 END IF;
670 CLOSE get_sales_rep;
671 --stmathew - Bug 4372869/4222231..end
672
673
674 l_trx_header_tbl(1).trx_header_id := 110; --???????????????????????????
675 l_trx_header_tbl(1).trx_number := NULL;
676 --start: 02-APR-07 cklee R12 Billing enhancement project |
677 -- l_trx_header_tbl(1).trx_date := r_xsiv_rec.TRX_DATE;
678 -- l_trx_header_tbl(1).trx_currency := r_xsiv_rec.CURRENCY_CODE;
679 l_trx_header_tbl(1).trx_date := lxfer_rec.TRX_DATE;
680 l_trx_header_tbl(1).trx_currency := lxfer_rec.CURRENCY_CODE;
681 l_trx_header_tbl(1).reference_number := NULL;
682 l_trx_header_tbl(1).trx_class := NULL;
683 -- l_trx_header_tbl(1).cust_trx_type_id := r_xsiv_rec.CUST_TRX_TYPE_ID;
684 l_trx_header_tbl(1).cust_trx_type_id := lxfer_rec.CUST_TRX_TYPE_ID;
685 -- l_trx_header_tbl(1).gl_date := SYSDATE; --r_xsiv_rec.TRX_DATE;
686 l_trx_header_tbl(1).gl_date := lxfer_rec.TRX_DATE;-- the same as oklarintf_pvt
687 -- l_trx_header_tbl(1).bill_to_customer_id := r_xsiv_rec.CUSTOMER_ID;
688 l_trx_header_tbl(1).bill_to_customer_id := lxfer_rec.CUSTOMER_ID;
689 l_trx_header_tbl(1).bill_to_account_number := NULL;
690 l_trx_header_tbl(1).bill_to_customer_name := NULL;
691 l_trx_header_tbl(1).bill_to_contact_id := NULL;
692 --end: 02-APR-07 cklee R12 Billing enhancement project |
693 --start: 02-APR-07 cklee R12 Billing enhancement project |
694 -- get bill-to information from contract line if any
695
696 -- rmunjulu - R12 Fixes -- take the customer_address_id, customer_bank_account_id
697 -- and receipt_method_id into different variables
698 -- or else (since the in and out varaibles are same) the values are getting passed
699 -- as NULLs
700 l_customer_address_id := lxfer_rec.CUSTOMER_ADDRESS_ID;
701 l_customer_bank_account_id := lxfer_rec.CUSTOMER_BANK_ACCOUNT_ID;
702 l_recept_method_id := lxfer_rec.RECEIPT_METHOD_ID;
703
704 OKL_ARINTF_PVT.get_cust_config_from_line(
705 p_kle_id => lxfer_rec.KLE_ID
706 ,p_customer_address_id => l_customer_address_id
707 ,p_customer_bank_account_id => l_customer_bank_account_id
708 ,p_receipt_method_id => l_recept_method_id
709 ,x_customer_address_id => lxfer_rec.CUSTOMER_ADDRESS_ID
710 ,x_customer_bank_account_id => lxfer_rec.CUSTOMER_BANK_ACCOUNT_ID
711 ,x_receipt_method_id => lxfer_rec.RECEIPT_METHOD_ID
712 ,x_creation_method_code => l_creation_method_code --Bug 7623549
713 ,x_bank_line_id1 => l_bank_line_id1 --Bug 7623549
714 );
715 --end: | 28-Mar-07 cklee R12 Billing enhancement project
716
717 --start: 02-APR-07 cklee R12 Billing enhancement project |
718 -- l_trx_header_tbl(1).bill_to_address_id := r_xsiv_rec.CUSTOMER_ADDRESS_ID;
719 l_trx_header_tbl(1).bill_to_address_id := lxfer_rec.CUSTOMER_ADDRESS_ID;
720 l_trx_header_tbl(1).bill_to_site_use_id := NULL;
721 -- l_trx_header_tbl(1).ship_to_customer_id := r_xsiv_rec.CUSTOMER_ID;
722 l_trx_header_tbl(1).ship_to_customer_id := lxfer_rec.CUSTOMER_ID;
723 l_trx_header_tbl(1).ship_to_account_number := NULL;
724 l_trx_header_tbl(1).ship_to_customer_name := NULL;
725 l_trx_header_tbl(1).ship_to_contact_id := NULL;
726 l_trx_header_tbl(1).ship_to_address_id := NVL(l_ship_to , lxfer_rec.CUSTOMER_ADDRESS_ID);
727 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '.... l_ship_to '||l_ship_to);
728 l_trx_header_tbl(1).ship_to_site_use_id := NULL;
729 l_trx_header_tbl(1).sold_to_customer_id := NULL;
730 -- l_trx_header_tbl(1).term_id := r_xsiv_rec.TERM_ID;
731 -- l_trx_header_tbl(1).LEGAL_ENTITY_ID := r_xsiv_rec.LEGAL_ENTITY_ID; -- for LE Uptake project 08-11-2006
732 l_trx_header_tbl(1).term_id := lxfer_rec.TERM_ID;
733 l_trx_header_tbl(1).LEGAL_ENTITY_ID := lxfer_rec.LEGAL_ENTITY_ID; -- for LE Uptake project 08-11-2006
734 l_trx_header_tbl(1).ORG_ID := lxfer_rec.ORG_ID; -- migrated from oklarintf_pvt
735
736 --stmathew - Bug 4372869/4222231..start
737 --removed hardcoded null and passing salesrep id and name if present
738 l_trx_header_tbl(1).primary_salesrep_id := l_salesrep_id;
739 l_trx_header_tbl(1).primary_salesrep_name := l_salesrep_name;
740 --stmathew - Bug 4372869/4222231..end
741
742 l_trx_header_tbl(1).territory_id := NULL;
743 l_trx_header_tbl(1).remit_to_address_id := NULL;
744 l_trx_header_tbl(1).invoicing_rule_id := NULL;
745 l_trx_header_tbl(1).printing_option := NULL;
746
747 l_trx_header_tbl(1).purchase_order := NULL;
748 l_trx_header_tbl(1).purchase_order_revision := NULL;
749 l_trx_header_tbl(1).purchase_order_date := NULL;
750 l_trx_header_tbl(1).comments
751 -- := NVL (NVL (r_xlsv_rec.DESCRIPTION, r_xsiv_rec.DESCRIPTION), 'OKL Billing');
752 := NVL (NVL (lxfer_rec.LINE_DESCRIPTION, lxfer_rec.HDR_DESCRIPTION), 'OKL Billing');
753 --end: 02-APR-07 cklee R12 Billing enhancement project |
754
755 l_trx_header_tbl(1).internal_notes := NULL;
756 l_trx_header_tbl(1).finance_charges := NULL;
757 --start: 02-APR-07 cklee R12 Billing enhancement project |
758 -- l_trx_header_tbl(1).receipt_method_id := r_xsiv_rec.RECEIPT_METHOD_ID;
759
760 --rkuttiya added the IF condition for PPD for bug # 7558039
761 IF p_source <> 'PRINCIPAL_PAYDOWN' THEN
762 -- rmunjulu -- R12 Fixes -- do not null out record before assigning to trx_header_tbl
763 l_trx_header_tbl(1).receipt_method_id := lxfer_rec.RECEIPT_METHOD_ID;
764
765 --Bug 7623549 START
766 l_trx_header_tbl(1).customer_bank_account_id := lxfer_rec.CUSTOMER_BANK_ACCOUNT_ID;
767
768 IF (l_creation_method_code = 'AUTOMATIC') THEN
769 l_khr_id := lxfer_rec.khr_id;
770 OKL_ARINTF_PVT.get_auto_bank_dtls(
771 p_api_version => l_api_version,
772 p_init_msg_list => l_init_msg_list,
773 p_khr_id => l_khr_id,
774 p_customer_address_id => lxfer_rec.CUSTOMER_ADDRESS_ID,
775 p_bank_id => lxfer_rec.CUSTOMER_BANK_ACCOUNT_ID,
776 p_trx_date => lxfer_rec.trx_date,
777 x_payment_trxn_extension_id => l_payment_trxn_extension_id,
778 x_customer_bank_account_id => l_trx_header_tbl(1).customer_bank_account_id,
779 x_return_status => l_return_status,
780 x_msg_count => l_msg_count,
781 x_msg_data => l_msg_data
782 );
783
784 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
787 RAISE FND_API.G_EXC_ERROR;
788 END IF;
789 -- For Automatic receipt method, bank account id should be NULL as the column is obsoleted
790 l_trx_header_tbl(1).customer_bank_account_id := NULL;
791 lxfer_rec.CUSTOMER_BANK_ACCOUNT_ID := NULL;
792 END IF;
793 --Bug 7623549 END
794
795 -- Null out receive mathod and bank account for Sales Order and Termination Quote,
796 -- These values will be taken from the AR setup for the customer.
797 nullout_rec_method(
798 p_contract_id => lxfer_rec.khr_id
799 ,p_Quote_number => lxfer_rec.Quote_number
800 ,p_sty_id => lxfer_rec.sty_id
801 ,p_customer_bank_account_id => l_trx_header_tbl(1).customer_bank_account_id
802 ,p_receipt_method_id => l_trx_header_tbl(1).receipt_method_id -- irm_id
803 ,p_payment_trxn_extension_id => l_payment_trxn_extension_id -- Bug 7623549
804 ,x_customer_bank_account_id => l_trx_header_tbl(1).CUSTOMER_BANK_ACCOUNT_ID
805 ,x_receipt_method_id => l_trx_header_tbl(1).receipt_method_id
806 ,x_payment_trxn_extension_id => l_trx_header_tbl(1).payment_trxn_extension_id -- Bug 7623549
807 );
808 END IF; --rkuttiya added
809
810 --l_trx_header_tbl(1).receipt_method_id := lxfer_rec.RECEIPT_METHOD_ID; -- rmunjulu -- R12 Fixes
811 --end: 02-APR-07 cklee R12 Billing enhancement project |
812 l_trx_header_tbl(1).related_customer_trx_id := NULL;
813 l_trx_header_tbl(1).agreement_id := NULL;
814 l_trx_header_tbl(1).ship_via := NULL;
815 l_trx_header_tbl(1).ship_date_actual := NULL;
816 l_trx_header_tbl(1).waybill_number := NULL;
817 l_trx_header_tbl(1).fob_point := NULL;
818 --start: 02-APR-07 cklee R12 Billing enhancement project |
819 -- l_trx_header_tbl(1).customer_bank_account_id := r_xsiv_rec.CUSTOMER_BANK_ACCOUNT_ID;
820 --rkuttiya added for bug # 7558039
821 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Source : '|| p_source);
822 IF p_source <> 'PRINCIPAL_PAYDOWN' THEN
823 l_trx_header_tbl(1).customer_bank_account_id := lxfer_rec.CUSTOMER_BANK_ACCOUNT_ID;
824 ELSE
825 l_trx_header_tbl(1).payment_trxn_extension_id := NULL;
826 END IF;
827 l_trx_header_tbl(1).default_ussgl_transaction_code
828 := NULL;
829 l_trx_header_tbl(1).status_trx := NULL;
830 l_trx_header_tbl(1).paying_customer_id := NULL;
831 l_trx_header_tbl(1).paying_site_use_id := NULL;
832
833 l_exempt_flg := NULL;
834 -- OPEN tax_exmpt_csr ( r_xsiv_rec.TAX_EXEMPT_FLAG );
835 OPEN tax_exmpt_csr ( lxfer_rec.TAX_EXEMPT_FLAG );
836 --end: 02-APR-07 cklee R12 Billing enhancement project |
837 FETCH tax_exmpt_csr INTO l_exempt_flg;
838 CLOSE tax_exmpt_csr;
839
840 l_trx_header_tbl(1).default_tax_exempt_flag
841 := l_exempt_flg;
842 l_trx_header_tbl(1).doc_sequence_value := NULL;
843 l_trx_header_tbl(1).attribute_category := NULL;
844 l_trx_header_tbl(1).attribute1 := NULL;
845 l_trx_header_tbl(1).attribute2 := NULL;
846 l_trx_header_tbl(1).attribute3 := NULL;
847 l_trx_header_tbl(1).attribute4 := NULL;
848 l_trx_header_tbl(1).attribute5 := NULL;
849 l_trx_header_tbl(1).attribute6 := NULL;
850 l_trx_header_tbl(1).attribute7 := NULL;
851 l_trx_header_tbl(1).attribute8 := NULL;
852 l_trx_header_tbl(1).attribute9 := NULL;
853 l_trx_header_tbl(1).attribute10 := NULL;
854 l_trx_header_tbl(1).global_attribute_category := NULL;
855 l_trx_header_tbl(1).global_attribute1 := NULL;
856 l_trx_header_tbl(1).global_attribute2 := NULL;
857 l_trx_header_tbl(1).global_attribute3 := NULL;
858 l_trx_header_tbl(1).global_attribute4 := NULL;
859 l_trx_header_tbl(1).global_attribute5 := NULL;
860 l_trx_header_tbl(1).global_attribute6 := NULL;
861 l_trx_header_tbl(1).global_attribute7 := NULL;
862 l_trx_header_tbl(1).global_attribute8 := NULL;
863 l_trx_header_tbl(1).global_attribute9 := NULL;
864 l_trx_header_tbl(1).global_attribute10 := NULL;
865 l_trx_header_tbl(1).global_attribute11 := NULL;
866 l_trx_header_tbl(1).global_attribute12 := NULL;
867 l_trx_header_tbl(1).global_attribute13 := NULL;
868 l_trx_header_tbl(1).global_attribute14 := NULL;
869 l_trx_header_tbl(1).global_attribute15 := NULL;
870 l_trx_header_tbl(1).global_attribute16 := NULL;
871 l_trx_header_tbl(1).global_attribute17 := NULL;
872 l_trx_header_tbl(1).global_attribute18 := NULL;
873 l_trx_header_tbl(1).global_attribute19 := NULL;
874 l_trx_header_tbl(1).global_attribute20 := NULL;
875 l_trx_header_tbl(1).global_attribute21 := NULL;
876 l_trx_header_tbl(1).global_attribute22 := NULL;
877 l_trx_header_tbl(1).global_attribute23 := NULL;
878 l_trx_header_tbl(1).global_attribute24 := NULL;
879 l_trx_header_tbl(1).global_attribute25 := NULL;
880 l_trx_header_tbl(1).global_attribute26 := NULL;
881 l_trx_header_tbl(1).global_attribute27 := NULL;
882 l_trx_header_tbl(1).global_attribute28 := NULL;
883 l_trx_header_tbl(1).global_attribute29 := NULL;
884 l_trx_header_tbl(1).global_attribute30 := NULL;
885
886
887 EXCEPTION
888 WHEN OTHERS THEN
889 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
890 END PREPARE_TRX_HDR_TBL;
891
892 --------------------------------------------
893 -- Prepare Transaction Lines Table
894 --------------------------------------------
895 PROCEDURE PREPARE_TRX_LNS_TBL(
896 x_return_status OUT NOCOPY VARCHAR2
897 ,p_num IN NUMBER
898 --start: | 02-APR-07 cklee R12 Billing enhancement project |
899 -- ,r_xsiv_rec IN Okl_Ext_Sell_Invs_Pub.xsiv_rec_type
900 -- ,r_xlsv_rec IN Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type
901 ,xfer_rec IN OKL_ARINTF_PVT.xfer_rec_type
902 --end: | 02-APR-07 cklee R12 Billing enhancement project |
903 ,l_trx_lines_tbl OUT NOCOPY AR_INVOICE_API_PUB.trx_line_tbl_type)
904 IS
905
906 l_group_by_contract_yn okl_invoice_types_v.group_by_contract_yn%type;
907 l_group_asset_yn okl_invoice_types_v.group_asset_yn%type;
908 l_contract_level_yn okl_invoice_formats_v.contract_level_yn%type;
909 l_invoice_group okl_invoice_formats_v.name%type;
910 l_khr_id okc_k_headers_b.id%type;
911 x_tax_det_rec OKL_PROCESS_SALES_TAX_PVT.tax_det_rec_type;
912 l_api_version NUMBER := 1.0;
913 x_msg_count NUMBER;
914 x_msg_data VARCHAR2(4000);
915 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
916
917 l_ship_to NUMBER;
918 l_kle_id NUMBER;
919 l_top_kle_id NUMBER;
920 l_chr_id okc_k_lines_b.chr_id%TYPE;
921 l_asset_name okc_k_lines_v.name%TYPE;
922 l_install_location_id NUMBER;
923 l_location_id NUMBER;
924 --start: 02-APR-07 cklee R12 Billing enhancement project |
925 -- l_exempt_flg okl_ext_sell_invs_v.tax_exempt_flag%TYPE;
926 l_exempt_flg okl_trx_ar_invoices_b.tax_exempt_flag%TYPE;
927 /* commented out for R12
928 CURSOR get_kle_id ( p_lsm_id NUMBER ) IS
929 SELECT kle_id
930 FROM OKL_CNSLD_AR_STRMS_V
931 WHERE id = p_lsm_id;
932
933 CURSOR check_top_line ( p_cle_id NUMBER ) IS
934 SELECT chr_id
935 FROM okc_k_lines_b
936 where id = p_cle_id;
937
938 CURSOR derive_top_line_id (p_lsm_id NUMBER) IS
939 SELECT FA.ID
940 FROM OKC_K_HEADERS_B CHR,
941 OKC_K_LINES_B TOP_CLE,
942 OKC_LINE_STYLES_b TOP_LSE,
943 OKC_K_LINES_B SUB_CLE,
944 OKC_LINE_STYLES_b SUB_LSE,
945 OKC_K_ITEMS CIM,
946 OKC_K_LINES_V FA,
947 OKC_LINE_STYLES_B AST_LSE,
948 OKL_CNSLD_AR_STRMS_B LSM
949 WHERE
950 CHR.ID = TOP_CLE.DNZ_CHR_ID AND
951 TOP_CLE.LSE_ID = TOP_LSE.ID AND
952 TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
953 TOP_CLE.ID = SUB_CLE.CLE_ID AND
954 SUB_CLE.LSE_ID = SUB_LSE.ID AND
955 SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
956 SUB_CLE.ID = LSM.KLE_ID AND
957 LSM.ID = p_lsm_id AND
958 CIM.CLE_ID = SUB_CLE.ID AND
959 CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
960 CIM.OBJECT1_ID1 = FA.ID AND
961 FA.LSE_ID = AST_LSE.ID AND
962 AST_LSE.LTY_CODE = 'FREE_FORM1';
963 */
964 --end: 02-APR-07 cklee R12 Billing enhancement project |
965
966 CURSOR top_line_asset ( p_cle_id NUMBER ) IS
967 SELECT name
968 FROM okc_k_lines_v
969 WHERE id = p_cle_id;
970
971
972 CURSOR Ship_to_csr( p_kle_top_line IN NUMBER ) IS
973 SELECT --cim.object1_id1 item_instance,
974 --cim.object1_id2 "#",
975 csi.install_location_id
976 , csi.location_id
977 FROM csi_item_instances csi,
978 okc_k_items cim,
979 okc_k_lines_b inst,
980 okc_k_lines_b ib,
981 okc_line_styles_b lse
982 WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
983 AND cim.cle_id = ib.id
984 AND ib.cle_id = inst.id
985 AND inst.lse_id = lse.id
986 AND lse.lty_code = 'FREE_FORM2'
987 AND inst.cle_id = p_kle_top_line;
988
989 CURSOR Ship_to_csr2( p_customer_num NUMBER, p_install_location NUMBER, p_location NUMBER, p_org_id NUMBER ) IS
990 SELECT a.CUST_ACCT_SITE_ID
991 FROM hz_cust_acct_sites_all a,
992 hz_cust_site_uses_all b,
993 hz_party_sites c
994 WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID AND
995 b.site_use_code = 'SHIP_TO' AND
996 a.party_site_id = c.party_site_id AND
997 a.cust_account_id = p_customer_num AND
998 a.org_id = p_org_id AND
999 c.party_site_id = p_install_location AND
1000 c.location_id = p_location;
1001
1002
1003 --start: 02-APR-07 cklee R12 Billing enhancement project |
1004 -- migrated from oklarintf_pvt
1005 lx_customer_id NUMBER;
1006 l_temp_sold_fee VARCHAR2(1);
1007 CURSOR sold_service_fee_csr ( p_cle_id NUMBER ) IS
1008 SELECT '1'
1009 FROM okc_k_lines_v a,
1010 okc_line_styles_v b
1011 WHERE a.lse_id = b.id
1012 AND b.lty_code = 'SOLD_SERVICE'
1013 AND a.id = p_cle_id;
1014
1015 CURSOR get_service_inv_csr ( p_cle_id NUMBER ) IS
1016 SELECT c.object1_id1
1017 FROM okc_k_lines_v a,
1018 okc_line_styles_v b,
1019 okc_k_items c
1020 WHERE a.lse_id = b.id
1021 AND b.lty_code = 'SOLD_SERVICE'
1022 AND a.id = p_cle_id
1023 AND c.cle_id = a.id;
1024 --end: 02-APR-07 cklee R12 Billing enhancement project |
1025
1026 l_inv_id NUMBER;
1027 l_uom_code mtl_system_items.primary_uom_code%TYPE;
1028
1029 CURSOR get_inv_item_id ( p_fin_asset_line_id NUMBER ) IS
1030 SELECT c.OBJECT1_ID1
1031 FROM okc_k_lines_b a,
1032 okc_line_styles_b b,
1033 okc_k_items c
1034 WHERE a.cle_id = p_fin_asset_line_id
1035 AND b.lty_code = 'ITEM'
1036 AND a.lse_id = b.id
1037 AND a.id = c.cle_id;
1038
1039 CURSOR get_uom_code ( p_inv_item_id NUMBER ) IS
1040 SELECT primary_uom_code
1041 FROM mtl_system_items
1042 WHERE inventory_item_id = p_inv_item_id;
1043
1044 CURSOR tax_exmpt_csr ( p_flg VARCHAR2 ) IS
1045 SELECT decode(p_flg,'S','S','E','E','R','R','S')
1046 FROM DUAL;
1047
1048 CURSOR tax_exmpt_reason_csr ( p_flg VARCHAR2 ) IS
1049 SELECT decode(p_flg,'E','MANUFACTURER',NULL)
1050 FROM dual;
1051
1052 l_reason_code VARCHAR2(30);
1053
1054 --start: 02-APR-07 cklee R12 Billing enhancement project |
1055 -- Migrated logic from okl_arintf_pvt
1056 CURSOR get_memo_line_id_csr IS
1057 SELECT MEMO_LINE_ID
1058 FROM ar_memo_lines
1059 WHERE NAME = 'Lease Upfront Tax';
1060
1061 l_memo_line_id ar_memo_lines.memo_line_id%TYPE;
1062 --end: 02-APR-07 cklee R12 Billing enhancement project |
1063 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1064 l_is_top_line number;
1065 is_top_line_flag boolean;
1066
1067 CURSOR is_top_line ( p_cle_id NUMBER ) IS
1068 SELECT 1
1069 FROM okc_k_lines_b kle
1070 where kle.id = p_cle_id
1071 and kle.cle_id is null; -- it's top line
1072
1073 CURSOR get_top_line ( p_cle_id NUMBER ) IS
1074 select cle.id--, lse.lty_code
1075 from okc_k_lines_b cle--,
1076 -- okc_line_styles_b lse
1077 -- where lse.id = cle.lse_id
1078 -- and cle.cle_id is null
1079 where cle.cle_id is null -- it's top line
1080 start with cle.id = p_cle_id
1081 connect by cle.id = prior cle.cle_id;
1082
1083 CURSOR get_top_line_name ( p_cle_id NUMBER ) IS
1084 select cle.name
1085 from OKC_K_LINES_V cle
1086 where cle.id = p_cle_id;
1087
1088 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1089 --start: 02-APR-07 cklee R12 Billing enhancement project |
1090 CURSOR l_get_inv_org_yn_csr(cp_org_id IN NUMBER) IS
1091 SELECT lease_inv_org_yn
1092 FROM OKL_SYSTEM_PARAMS
1093 WHERE org_id = cp_org_id;
1094
1095 l_rev_rec_basis okl_strm_type_b.accrual_yn%type;
1096 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
1097 l_use_inv_org VARCHAR2(10) := NULL;
1098 --end: 02-APR-07 cklee R12 Billing enhancement project |
1099
1100 lxfer_rec OKL_ARINTF_PVT.xfer_rec_type;
1101
1102 BEGIN
1103
1104 -- Assign to local record
1105 lxfer_rec := xfer_rec;
1106
1107 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1108
1109 --start: 02-APR-07 cklee R12 Billing enhancement project |
1110 -- Migrated logic from okl_arintf_pvt
1111 -- get memo line id for tax only invoices
1112 l_memo_line_id := NULL;
1113 OPEN get_memo_line_id_csr;
1114 FETCH get_memo_line_id_csr INTO l_memo_line_id;
1115 CLOSE get_memo_line_id_csr;
1116
1117 if l_memo_line_id is null then
1118 FND_FILE.PUT_LINE(FND_FILE.LOG,
1119 'WARNING: A memo line with name -- Lease Upfront Tax,
1120 must exist to import tax-only invoices.');
1121 end if;
1122 --end: 02-APR-07 cklee R12 Billing enhancement project |
1123
1124 -- To find the top line kle_id
1125 l_kle_id := NULL;
1126 l_top_kle_id := NULL;
1127
1128 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1129 -- 1. Check if it's a top line
1130 OPEN is_top_line(lxfer_rec.kle_id);
1131 FETCH is_top_line INTO l_is_top_line;
1132 is_top_line_flag := is_top_line%FOUND;
1133 CLOSE is_top_line;
1134
1135 -- 2. get top line if needed
1136 IF NOT is_top_line_flag THEN
1137 OPEN get_top_line(lxfer_rec.kle_id);
1138 FETCH get_top_line INTO lxfer_rec.kle_id;
1139 CLOSE get_top_line;
1140 END IF;
1141
1142 -- 3. get top line name (asset number)
1143 OPEN get_top_line_name(lxfer_rec.kle_id);
1144 FETCH get_top_line_name INTO lxfer_rec.ASSET_NUMBER;
1145 CLOSE get_top_line_name;
1146
1147 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1148 l_chr_id := lxfer_rec.khr_id;
1149 l_kle_id := lxfer_rec.kle_id;
1150 /* commented out for R12
1151 -- Find top line kle_id
1152 OPEN get_kle_id ( r_xlsv_rec.lsm_id );
1153 FETCH get_kle_id INTO l_top_kle_id;
1154 CLOSE get_kle_id;
1155
1156 l_chr_id := NULL;
1157 OPEN check_top_line( l_top_kle_id );
1158 FETCH check_top_line INTO l_chr_id;
1159 CLOSE check_top_line;
1160
1161 IF l_chr_id IS NOT NULL THEN
1162 l_kle_id := l_top_kle_id;
1163 ELSE
1164 l_top_kle_id := NULL;
1165 OPEN derive_top_line_id ( r_xlsv_rec.lsm_id );
1166 FETCH derive_top_line_id INTO l_top_kle_id;
1167 CLOSE derive_top_line_id;
1168 l_kle_id := l_top_kle_id;
1169 END IF;
1170 */
1171 --end: 02-APR-07 cklee R12 Billing enhancement project |
1172 l_asset_name := NULL;
1173 OPEN top_line_asset ( l_kle_id );
1174 FETCH top_line_asset INTO l_asset_name;
1175 CLOSE top_line_asset;
1176
1177 OPEN ship_to_csr(l_kle_id);
1178 FETCH ship_to_csr INTO l_install_location_id, l_location_id;
1179 CLOSE ship_to_csr;
1180
1181
1182 --start: 02-APR-07 cklee R12 Billing enhancement project |
1183 -- Migrated the following logic from oklarintf_pvt
1184 -- Check if Vendor is the same as the customer on the Contract
1185 lx_customer_id := NULL;
1186 get_customer_id( lxfer_rec.CONTRACT_NUMBER ,lx_customer_id );
1187
1188 l_ship_to := NULL;
1189 -- OPEN Ship_to_csr2( r_xsiv_rec.CUSTOMER_ID, l_install_location_id, l_location_id, r_xsiv_rec.ORG_ID);
1190 OPEN Ship_to_csr2( lx_customer_id, l_install_location_id, l_location_id, lxfer_rec.ORG_ID);
1191 --end: 02-APR-07 cklee R12 Billing enhancement project |
1192 FETCH Ship_to_csr2 INTO l_ship_to;
1193 CLOSE Ship_to_csr2;
1194
1195 IF ( lx_customer_id = lxfer_rec.CUSTOMER_ID ) THEN
1196 NULL;
1197 ELSE
1198 l_ship_to := NULL;
1199 END IF;
1200
1201 --start: 02-APR-07 cklee R12 Billing enhancement project |
1202 -- Get Inventory_item_id
1203 l_inv_id := NULL;
1204 -- OPEN get_inv_item_id ( l_kle_id );
1205 -- FETCH get_inv_item_id INTO l_inv_id;
1206 -- CLOSE get_inv_item_id;
1207 -- Migrated the following logic from oklarintf_pvt
1208 l_temp_sold_fee := NULL;
1209 OPEN sold_service_fee_csr ( l_kle_id );
1210 FETCH sold_service_fee_csr INTO l_temp_sold_fee;
1211 CLOSE sold_service_fee_csr;
1212
1213 IF l_temp_sold_fee = '1' THEN
1214 -- Get Inventory_item_id
1215 l_inv_id := NULL;
1216 OPEN get_service_inv_csr ( l_kle_id );
1217 FETCH get_service_inv_csr INTO l_inv_id;
1218 CLOSE get_service_inv_csr;
1219 ELSE
1220 -- Get Inventory_item_id
1221 l_inv_id := NULL;
1222 OPEN get_inv_item_id ( l_kle_id );
1223 FETCH get_inv_item_id INTO l_inv_id;
1224 CLOSE get_inv_item_id;
1225 END IF;
1226
1227 -- Get UOM Code
1228 l_uom_code := NULL;
1229 IF lxfer_rec.INVENTORY_ITEM_ID IS NULL THEN
1230 lxfer_rec.INVENTORY_ITEM_ID := l_inv_id;
1231 OPEN get_uom_code ( l_inv_id );
1232 FETCH get_uom_code INTO l_uom_code;
1233 CLOSE get_uom_code;
1234 ELSE
1235 OPEN get_uom_code ( lxfer_rec.INVENTORY_ITEM_ID );
1236 FETCH get_uom_code INTO l_uom_code;
1237 CLOSE get_uom_code;
1238 END IF;
1239
1240 -- Get UOM Code
1241 -- l_uom_code := NULL;
1242 -- OPEN get_uom_code ( l_inv_id );
1243 -- FETCH get_uom_code INTO l_uom_code;
1244 -- CLOSE get_uom_code;
1245 --end: 02-APR-07 cklee R12 Billing enhancement project |
1246
1247 l_trx_lines_tbl(1).trx_header_id := 110; --????????????????????
1248 l_trx_lines_tbl(1).trx_line_id := p_num;
1249 l_trx_lines_tbl(1).link_to_trx_line_id := NULL;
1250 l_trx_lines_tbl(1).LINE_NUMBER := 1; --p_num;
1251 l_trx_lines_tbl(1).REASON_CODE := NULL;
1252 --start: 02-APR-07 cklee R12 Billing enhancement project |
1253 -- l_trx_lines_tbl(1).INVENTORY_ITEM_ID :=l_inv_id;
1254 l_trx_lines_tbl(1).INVENTORY_ITEM_ID := lxfer_rec.INVENTORY_ITEM_ID;
1255 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '.... Inventory_Item Id '||l_inv_id);
1256 l_trx_lines_tbl(1).DESCRIPTION
1257 -- := NVL (NVL (r_xlsv_rec.DESCRIPTION, r_xsiv_rec.DESCRIPTION), 'OKL Billing');
1258 := NVL (NVL (lxfer_rec.LINE_DESCRIPTION, lxfer_rec.HDR_DESCRIPTION), 'OKL Billing');
1259 -- l_trx_lines_tbl(1).QUANTITY_ORDERED := r_xlsv_rec.QUANTITY;
1260
1261 --start: migrated from oklarintf_pvt
1262 -- l_trx_lines_tbl(1).QUANTITY_ORDERED := lxfer_rec.QUANTITY;
1263 l_trx_lines_tbl(1).QUANTITY_ORDERED := NULL;
1264 if lxfer_rec.AMOUNT = 0 then
1265 l_trx_lines_tbl(1).QUANTITY_INVOICED := 0;
1266 else
1267 l_trx_lines_tbl(1).QUANTITY_INVOICED := lxfer_rec.QUANTITY;
1268 end if;
1269 -- l_trx_lines_tbl(1).QUANTITY_INVOICED := 1;
1270 --end: migrated from oklarintf_pvt
1271 -- l_trx_lines_tbl(1).UNIT_STANDARD_PRICE := r_xlsv_rec.AMOUNT;
1272 -- l_trx_lines_tbl(1).UNIT_SELLING_PRICE := r_xlsv_rec.AMOUNT;
1273 l_trx_lines_tbl(1).UNIT_STANDARD_PRICE := lxfer_rec.AMOUNT;
1274 l_trx_lines_tbl(1).UNIT_SELLING_PRICE := lxfer_rec.AMOUNT;
1275 --end: 02-APR-07 cklee R12 Billing enhancement project |
1276 l_trx_lines_tbl(1).SALES_ORDER := NULL;
1277 l_trx_lines_tbl(1).SALES_ORDER_LINE := NULL;
1278 l_trx_lines_tbl(1).SALES_ORDER_DATE := NULL;
1279 l_trx_lines_tbl(1).ACCOUNTING_RULE_ID := NULL;
1280
1281 l_trx_lines_tbl(1).LINE_TYPE := 'LINE';
1282 l_trx_lines_tbl(1).ATTRIBUTE_CATEGORY := NULL;
1283 l_trx_lines_tbl(1).ATTRIBUTE1 := NULL;
1284 l_trx_lines_tbl(1).ATTRIBUTE2 := NULL;
1285 l_trx_lines_tbl(1).ATTRIBUTE3 := NULL;
1286 l_trx_lines_tbl(1).ATTRIBUTE4 := NULL;
1287 l_trx_lines_tbl(1).ATTRIBUTE5 := NULL;
1288 l_trx_lines_tbl(1).ATTRIBUTE6 := NULL;
1289 l_trx_lines_tbl(1).ATTRIBUTE7 := NULL;
1290 l_trx_lines_tbl(1).ATTRIBUTE8 := NULL;
1291 l_trx_lines_tbl(1).ATTRIBUTE9 := NULL;
1292 l_trx_lines_tbl(1).ATTRIBUTE10 := NULL;
1293 l_trx_lines_tbl(1).ATTRIBUTE11 := NULL;
1294 l_trx_lines_tbl(1).ATTRIBUTE12 := NULL;
1295 l_trx_lines_tbl(1).ATTRIBUTE13 := NULL;
1296 l_trx_lines_tbl(1).ATTRIBUTE14 := NULL;
1297 l_trx_lines_tbl(1).ATTRIBUTE15 := NULL;
1298 l_trx_lines_tbl(1).RULE_START_DATE := NULL;
1299 l_trx_lines_tbl(1).INTERFACE_LINE_CONTEXT := 'OKL_CONTRACTS';
1300 --start: 02-APR-07 cklee R12 Billing enhancement project |
1301 /*
1302 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE1
1303 := SUBSTR(LTRIM(RTRIM(r_xsiv_rec.XTRX_CONS_INVOICE_NUMBER)),1,30);
1304 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE2
1305 := SUBSTR(LTRIM(RTRIM(r_xsiv_rec.XTRX_FORMAT_TYPE)),1,30);
1306 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE3
1307 := SUBSTR(LTRIM(RTRIM(r_xsiv_rec.XTRX_INVOICE_PULL_YN)),1,30);
1308 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE4
1309 := SUBSTR(LTRIM(RTRIM(r_xsiv_rec.XTRX_PRIVATE_LABEL)),1,30);
1310 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE5
1311 := SUBSTR(LTRIM(RTRIM(r_xlsv_rec.XTRX_CONS_LINE_NUMBER)),1,30);
1312 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE6
1313 := SUBSTR(LTRIM(RTRIM(r_xlsv_rec.XTRX_CONTRACT)),1,30);
1314 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE7
1315 := SUBSTR(LTRIM(RTRIM(l_asset_name)),1,30);
1316 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE8
1317 := SUBSTR(LTRIM(RTRIM(r_xlsv_rec.XTRX_STREAM_GROUP)),1,30);
1318 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE9
1319 := SUBSTR(LTRIM(RTRIM(r_xlsv_rec.XTRX_STREAM_TYPE)),1,30);
1320 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE10
1321 := SUBSTR (r_xlsv_rec.XTRX_CONS_STREAM_ID, 1, 20);
1322 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE11
1323 := SUBSTR (r_xlsv_rec.XTRX_CONS_STREAM_ID, 21);
1324 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE12 := NULL;
1325 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE13 := NULL;
1326 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE14 := NULL;
1327 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE15 := NULL;
1328 */
1329 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1330 -- get invoice group related data
1331 Get_chr_inv_grp(
1332 p_inf_id => lxfer_rec.inf_id
1333 ,p_sty_id => lxfer_rec.sty_id
1334 ,x_group_by_contract_yn => l_group_by_contract_yn
1335 ,x_contract_level_yn => l_contract_level_yn
1336 ,x_group_asset_yn => l_group_asset_yn
1337 ,x_invoice_group => l_invoice_group
1338 );
1339
1340 l_khr_id := lxfer_rec.KHR_ID;
1341 lxfer_rec.KHR_ID := NULL;
1342 --l_kle_id := l_xfer_tbl(k).KLE_ID;
1343 --l_xfer_tbl(k).KLE_ID := NULL;
1344 IF (l_group_by_contract_yn = 'Y' OR l_contract_level_yn = 'N') THEN
1345 lxfer_rec.KHR_ID := l_khr_id;
1346 --IF l_group_by_assets_yn = 'N' THEN
1347 -- l_xfer_tbl(k).KLE_ID := l_kle_id;
1348 --END IF;
1349 END IF;
1350 --end: | 15-FEB-07 cklee R12 Billing enhancement project
1351
1352 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE1 := NULL;
1353 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE2 := SUBSTR(TRIM(l_invoice_group),1,G_AR_DATA_LENGTH);
1354 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE3 := SUBSTR(TRIM(lxfer_rec.INVOICE_PULL_YN),1,G_AR_DATA_LENGTH);
1355 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(lxfer_rec.PRIVATE_LABEL),1,G_AR_DATA_LENGTH);
1356 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE5 := NULL;
1357 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE6 := SUBSTR(TRIM(lxfer_rec.CONTRACT_NUMBER),1,G_AR_DATA_LENGTH);
1358 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE7 := SUBSTR(TRIM(lxfer_rec.ASSET_NUMBER),1,G_AR_DATA_LENGTH);
1359 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE8 := SUBSTR(TRIM(lxfer_rec.INVOICE_FORMAT_LINE_TYPE),1,G_AR_DATA_LENGTH);
1360 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE9 := SUBSTR(TRIM(lxfer_rec.STREAM_TYPE),1,G_AR_DATA_LENGTH);
1361 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(lxfer_rec.TXN_ID)),1,G_AR_DATA_LENGTH);
1362 -- if the source of the billing trx is termination quote, the OKL billing trx number is Quite_number
1363 IF lxfer_rec.OKL_SOURCE_BILLING_TRX = 'TERMINATION_QUOTE' THEN
1364 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE11 := SUBSTR(TRIM(lxfer_rec.Quote_number),1,G_AR_DATA_LENGTH);
1365 END IF;
1366 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE12 := SUBSTR(TRIM(TO_CHAR(lxfer_rec.KHR_ID)),1,G_AR_DATA_LENGTH);
1367 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE13 := SUBSTR(TRIM(lxfer_rec.OKL_SOURCE_BILLING_TRX),1,G_AR_DATA_LENGTH);
1368 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(lxfer_rec.TXN_ID)),1,G_AR_DATA_LENGTH);
1369 l_trx_lines_tbl(1).INTERFACE_LINE_ATTRIBUTE15 := SUBSTR(TRIM(lxfer_rec.INVOICE_FORMAT_TYPE),1,G_AR_DATA_LENGTH);
1370
1371 --end: 02-APR-07 cklee R12 Billing enhancement project |
1372
1373 l_trx_lines_tbl(1).SALES_ORDER_SOURCE := NULL;
1374 --start: 02-APR-07 cklee R12 Billing enhancement project |
1375 -- l_trx_lines_tbl(1).AMOUNT := r_xlsv_rec.AMOUNT;
1376 l_trx_lines_tbl(1).AMOUNT := lxfer_rec.AMOUNT;
1377 --end: 02-APR-07 cklee R12 Billing enhancement project |
1378 l_trx_lines_tbl(1).TAX_PRECEDENCE := NULL;
1379 l_trx_lines_tbl(1).TAX_RATE := NULL;
1380 l_trx_lines_tbl(1).TAX_EXEMPTION_ID := NULL;
1381
1382 --start: 02-APR-07 cklee R12 Billing enhancement project |
1383 -- Migrated logic from okl_arintf_pvt
1384 if lxfer_rec.AMOUNT = 0 then
1385 l_trx_lines_tbl(1).MEMO_LINE_ID := l_memo_line_id;
1386 else
1387 l_trx_lines_tbl(1).MEMO_LINE_ID := NULL;
1388 end if;
1389 --end: 02-APR-07 cklee R12 Billing enhancement project |
1390
1391 l_trx_lines_tbl(1).UOM_CODE := l_uom_code;
1392 l_trx_lines_tbl(1).DEFAULT_USSGL_TRANSACTION_CODE := NULL;
1393 l_trx_lines_tbl(1).DEFAULT_USSGL_TRX_CODE_CONTEXT := NULL;
1394 l_trx_lines_tbl(1).VAT_TAX_ID := NULL;
1395
1396 l_exempt_flg := NULL;
1397 --start: 02-APR-07 cklee R12 Billing enhancement project |
1398 -- OPEN tax_exmpt_csr ( r_xsiv_rec.TAX_EXEMPT_FLAG );
1399 OPEN tax_exmpt_csr ( lxfer_rec.TAX_EXEMPT_FLAG );
1400 --end: 02-APR-07 cklee R12 Billing enhancement project |
1401 FETCH tax_exmpt_csr INTO l_exempt_flg;
1402 CLOSE tax_exmpt_csr;
1403
1404 l_trx_lines_tbl(1).TAX_EXEMPT_FLAG := l_exempt_flg;
1405 l_trx_lines_tbl(1).TAX_EXEMPT_NUMBER := NULL;
1406
1407 l_reason_code := NULL;
1408 OPEN tax_exmpt_reason_csr ( l_exempt_flg );
1409 FETCH tax_exmpt_reason_csr INTO l_reason_code;
1410 CLOSE tax_exmpt_reason_csr;
1411
1412 l_trx_lines_tbl(1).TAX_EXEMPT_REASON_CODE := l_reason_code;
1413 l_trx_lines_tbl(1).TAX_VENDOR_RETURN_CODE := NULL;
1414
1415 --start: 02-APR-07 cklee R12 Billing enhancement project |
1416 IF lxfer_rec.try_name IN ('Billing', 'Credit Memo') THEN
1417
1418 OKL_PROCESS_SALES_TAX_PVT.get_tax_determinants(
1419 p_api_version => l_api_version,
1420 p_init_msg_list => l_init_msg_list,
1421 x_return_status => x_return_status,
1422 x_msg_count => x_msg_count,
1423 x_msg_data => x_msg_data,
1424 p_source_trx_id => lxfer_rec.TXN_ID,
1425 p_source_trx_name => lxfer_rec.try_name,
1426 p_source_table => 'OKL_TXD_AR_LN_DTLS_B',
1427 x_tax_det_rec => x_tax_det_rec);
1428
1429
1430 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1431 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1432 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1433 RAISE OKL_API.G_EXCEPTION_ERROR;
1434 END IF;
1435
1436 -- l_trx_lines_tbl(1).TAX_CODE := x_tax_det_rec.x_TAX_CODE;
1437 /* inv_lines_tbl(hdr_cnt).TRX_BUSINESS_CATEGORY := x_tax_det_rec.x_TRX_BUSINESS_CATEGORY; */
1438 /* inv_lines_tbl(hdr_cnt).PRODUCT_CATEGORY := x_tax_det_rec.x_PRODUCT_CATEGORY; */
1439 /* inv_lines_tbl(hdr_cnt).PRODUCT_TYPE := x_tax_det_rec.x_PRODUCT_TYPE; */
1440 /* inv_lines_tbl(hdr_cnt).LINE_INTENDED_USE := x_tax_det_rec.x_LINE_INTENDED_USE; */
1441 /* inv_lines_tbl(hdr_cnt).USER_DEFINED_FISC_CLASS := x_tax_det_rec.x_USER_DEFINED_FISC_CLASS; */
1442 /* inv_lines_tbl(hdr_cnt).ASSESSABLE_VALUE := x_tax_det_rec.x_ASSESSABLE_VALUE; */
1443 /* inv_lines_tbl(hdr_cnt).DEFAULT_TAXATION_COUNTRY := x_tax_det_rec.x_DEFAULT_TAXATION_COUNTRY; */
1444 /* inv_lines_tbl(hdr_cnt).UPSTREAM_TRX_REPORTED_FLAG := x_tax_det_rec.x_UPSTREAM_TRX_REPORTED_FLAG; */
1445
1446 END IF;
1447 --end: 02-APR-07 cklee R12 Billing enhancement project |
1448
1449
1450 l_trx_lines_tbl(1).MOVEMENT_ID := NULL;
1451 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE1 := NULL;
1452 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE2 := NULL;
1453 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE3 := NULL;
1454 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE4 := NULL;
1455 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE5 := NULL;
1456 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE6 := NULL;
1457 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE7 := NULL;
1458 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE8 := NULL;
1459 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE9 := NULL;
1460 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE10 := NULL;
1461 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE11 := NULL;
1462 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE12 := NULL;
1463 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE13 := NULL;
1464 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE14 := NULL;
1465 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE15 := NULL;
1466 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE16 := NULL;
1467 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE17 := NULL;
1468 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE18 := NULL;
1469 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE19 := NULL;
1470 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE20 := NULL;
1471 l_trx_lines_tbl(1).GLOBAL_ATTRIBUTE_CATEGORY := NULL;
1472 l_trx_lines_tbl(1).AMOUNT_INCLUDES_TAX_FLAG := NULL;
1473
1474 --start: 02-APR-07 cklee R12 Billing enhancement project
1475 -- Migrated from OKL_ARIntf_Pvt |
1476 OPEN l_get_inv_org_yn_csr( l_org_id );
1477 FETCH l_get_inv_org_yn_csr INTO l_use_inv_org;
1478 CLOSE l_get_inv_org_yn_csr;
1479
1480 -- Populate warehouse_id
1481 IF (NVL(l_use_inv_org, 'N') = 'Y') THEN
1482 --if it is a Remarketing invoice
1483 IF (lxfer_rec.inventory_org_id IS NOT NULL) THEN
1484 l_trx_lines_tbl(1).warehouse_id := lxfer_rec.inventory_org_id;
1485 END IF;
1486 ELSE
1487 l_trx_lines_tbl(1).warehouse_id := NULL;
1488 END IF;
1489 -- l_trx_lines_tbl(1).WAREHOUSE_ID := NULL;
1490 --end: 02-APR-07 cklee R12 Billing enhancement project |
1491
1492 l_trx_lines_tbl(1).CONTRACT_LINE_ID := NULL;
1493 l_trx_lines_tbl(1).SOURCE_DATA_KEY1 := NULL;
1494 l_trx_lines_tbl(1).SOURCE_DATA_KEY2 := NULL;
1495 l_trx_lines_tbl(1).SOURCE_DATA_KEY3 := NULL;
1496 l_trx_lines_tbl(1).SOURCE_DATA_KEY4 := NULL;
1497 l_trx_lines_tbl(1).SOURCE_DATA_KEY5 := NULL;
1498 l_trx_lines_tbl(1).INVOICED_LINE_ACCTG_LEVEL := NULL;
1499 l_trx_lines_tbl(1).SHIP_DATE_ACTUAL := NULL;
1500
1501 EXCEPTION
1502 WHEN OTHERS THEN
1503 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1504 END PREPARE_TRX_LNS_TBL;
1505
1506
1507 --------------------------------------------
1508 -- Prepare Distributions Table
1509 --------------------------------------------
1510 PROCEDURE PREPARE_TRX_DIST_TBL(
1511 x_return_status OUT NOCOPY VARCHAR2
1512 ,p_line_id IN NUMBER
1513 --start: | 02-APR-07 cklee R12 Billing enhancement project |
1514 -- ,r_xsiv_rec IN Okl_Ext_Sell_Invs_Pub.xsiv_rec_type
1515 -- ,r_xlsv_rec IN Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type
1516 ,xfer_rec IN OKL_ARINTF_PVT.xfer_rec_type
1517 --end: | 02-APR-07 cklee R12 Billing enhancement project |
1518 ,l_trx_dist_tbl OUT NOCOPY AR_INVOICE_API_PUB.trx_dist_tbl_type )
1519 IS
1520 --start: | 02-APR-07 cklee R12 Billing enhancement project |
1521 /* commented out for R12
1522 CURSOR dist_csr ( p_xls_id NUMBER ) IS
1523 SELECT *
1524 FROM OKL_XTD_SELL_INVS_V
1525 WHERE XLS_ID = p_xls_id;
1526 */
1527 --end: | 02-APR-07 cklee R12 Billing enhancement project |
1528
1529 i NUMBER;
1530 lxfer_rec OKL_ARINTF_PVT.xfer_rec_type;
1531 BEGIN
1532
1533 -- Assign IN to local record
1534 lxfer_rec := xfer_rec;
1535
1536 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1537
1538 i := 0;
1539 --start: | 02-APR-07 cklee R12 Billing enhancement project |
1540 /* commented out for R12
1541 FOR dist_rec IN dist_csr ( r_xlsv_rec.id ) LOOP
1542 i := i + 1;
1543
1544 l_trx_dist_tbl(i).trx_dist_id := i;
1545 l_trx_dist_tbl(i).trx_header_id := 110;
1546
1547 IF dist_rec.ACCOUNT_CLASS <> 'REC' THEN
1548 l_trx_dist_tbl(i).trx_LINE_ID := p_line_id;
1549 END IF;
1550
1551 l_trx_dist_tbl(i).ACCOUNT_CLASS := dist_rec.ACCOUNT_CLASS;
1552 l_trx_dist_tbl(i).AMOUNT := dist_rec.AMOUNT;
1553 l_trx_dist_tbl(i).acctd_amount := dist_rec.AMOUNT;
1554 l_trx_dist_tbl(i).PERCENT := dist_rec.PERCENT;
1555 l_trx_dist_tbl(i).CODE_COMBINATION_ID := dist_rec.CODE_COMBINATION_ID;
1556 l_trx_dist_tbl(i).ATTRIBUTE_CATEGORY := NULL;
1557 l_trx_dist_tbl(i).ATTRIBUTE1 := NULL;
1558 l_trx_dist_tbl(i).ATTRIBUTE2 := NULL;
1559 l_trx_dist_tbl(i).ATTRIBUTE3 := NULL;
1560 l_trx_dist_tbl(i).ATTRIBUTE4 := NULL;
1561 l_trx_dist_tbl(i).ATTRIBUTE5 := NULL;
1562 l_trx_dist_tbl(i).ATTRIBUTE6 := NULL;
1563 l_trx_dist_tbl(i).ATTRIBUTE7 := NULL;
1564 l_trx_dist_tbl(i).ATTRIBUTE8 := NULL;
1565 l_trx_dist_tbl(i).ATTRIBUTE9 := NULL;
1566 l_trx_dist_tbl(i).ATTRIBUTE10 := NULL;
1567 l_trx_dist_tbl(i).ATTRIBUTE11 := NULL;
1568 l_trx_dist_tbl(i).ATTRIBUTE12 := NULL;
1569 l_trx_dist_tbl(i).ATTRIBUTE13 := NULL;
1570 l_trx_dist_tbl(i).ATTRIBUTE14 := NULL;
1571 l_trx_dist_tbl(i).ATTRIBUTE15 := NULL;
1572 l_trx_dist_tbl(i).COMMENTS := NULL;
1573 END LOOP;
1574 */
1575 IF G_ACC_SYS_OPTION = 'ATS' THEN
1576 -- get accounting disb via internal billing details table
1577 Get_acct_disb(
1578 p_tld_id => lxfer_rec.txn_id
1579 ,x_account_class => lxfer_rec.account_class
1580 ,x_dist_amount => lxfer_rec.dist_amount
1581 ,x_dist_percent => lxfer_rec.dist_percent
1582 ,x_code_combination_id => lxfer_rec.code_combination_id
1583 );
1584
1585 IF (lxfer_rec.rev_rec_basis = 'CASH_RECEIPT' AND lxfer_rec.AMOUNT < 0
1586 AND lxfer_rec.ACCOUNT_CLASS = 'REV') THEN
1587 l_trx_dist_tbl(i).ACCOUNT_CLASS := 'UNEARN';
1588 ELSE
1589 l_trx_dist_tbl(i).ACCOUNT_CLASS := lxfer_rec.ACCOUNT_CLASS;
1590 END IF;
1591 l_trx_dist_tbl(i).AMOUNT := lxfer_rec.DIST_AMOUNT;
1592 l_trx_dist_tbl(i).PERCENT := lxfer_rec.DIST_PERCENT;
1593
1594 l_trx_dist_tbl(i).acctd_amount := lxfer_rec.DIST_AMOUNT; -- cklee
1595
1596 l_trx_dist_tbl(i).CODE_COMBINATION_ID := lxfer_rec.CODE_COMBINATION_ID;
1597 /*
1598 l_trx_dist_tbl(i).ORG_ID := lxfer_rec.ORG_ID;
1599 l_trx_dist_tbl(i).CREATED_BY := G_user_id;
1600 l_trx_dist_tbl(i).CREATION_DATE := sysdate;
1601 l_trx_dist_tbl(i).LAST_UPDATED_BY := G_user_id;
1602 l_trx_dist_tbl(i).LAST_UPDATE_DATE := sysdate;
1603 */
1604 END IF; -- IF G_ACC_SYS_OPTION = 'ATS' THEN
1605
1606 --end: | 02-APR-07 cklee R12 Billing enhancement project |
1607
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1611 END PREPARE_TRX_DIST_TBL;
1612
1613 ------------------------------------------------------------------
1614 -- Procedure ADVANCED_BILLING to bill outstanding stream elements
1615 ------------------------------------------------------------------
1616 PROCEDURE ADVANCED_BILLING
1617 (p_api_version IN NUMBER
1618 ,p_init_msg_list IN VARCHAR2
1619 ,x_return_status OUT NOCOPY VARCHAR2
1620 ,x_msg_count OUT NOCOPY NUMBER
1621 ,x_msg_data OUT NOCOPY VARCHAR2
1622 ,p_contract_number IN VARCHAR2
1623 ,p_from_bill_date IN DATE
1624 ,p_to_bill_date IN DATE
1625 ,p_source IN VARCHAR2
1626 ,x_ar_inv_tbl OUT NOCOPY ar_inv_tbl_type
1627 ,p_ppd_flow IN VARCHAR2 DEFAULT 'N'
1628 ) IS
1629
1630 -- ---------------------------------------------------------
1631 -- Declare variables required by APIs
1632 -- ---------------------------------------------------------
1633 l_api_version CONSTANT NUMBER := 1;
1634 l_api_name CONSTANT VARCHAR2(30) := 'ADVANCED_BILLING';
1635 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1636
1637 --start: | 02-APR-07 cklee R12 Billing enhancement project
1638
1639 --gkhuntet - FP Bug 5516814..start
1640 CURSOR c_get_adv_rcpt_for_cont(cp_cont_number IN VARCHAR2) IS
1641 SELECT DISTINCT c.contract_number
1642 FROM okl_trx_csh_rcpt_all_b a, okl_txl_rcpt_apps_b b,
1643 okc_k_headers_b c
1644 WHERE a.FULLY_APPLIED_FLAG = 'N'
1645 AND a.receipt_type = 'ADV'
1646 AND a.id = b.rct_id_details
1647 AND b.khr_id = c.id
1648 AND c.sts_code IN ('BOOKED', 'EVERGREEN','TERMINATED')
1649 AND c.contract_number = NVL(cp_cont_number ,c.contract_number)
1650 --gkhuntet - FP Bug 5516814..end
1651 --dkagrawa added union for ppd to to select contract number even if there is no advance receipt for contract
1652 UNION
1653 SELECT cp_cont_number
1654 FROM dual
1655 WHERE p_ppd_flow = 'Y';
1656
1657 CURSOR l_get_inv_org_yn_csr(cp_org_id IN NUMBER) IS
1658 SELECT lease_inv_org_yn
1659 FROM OKL_SYSTEM_PARAMS
1660 WHERE org_id = cp_org_id;
1661
1662 l_rev_rec_basis okl_strm_type_b.accrual_yn%type;
1663 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
1664 l_use_inv_org VARCHAR2(10) := NULL;
1665
1666 l_group_by_contract_yn okl_invoice_types_v.group_by_contract_yn%type;
1667 l_group_asset_yn okl_invoice_types_v.group_asset_yn%type;
1668 l_contract_level_yn okl_invoice_formats_v.contract_level_yn%type;
1669 l_invoice_group okl_invoice_formats_v.name%type;
1670 l_khr_id okc_k_headers_b.id%type;
1671
1672 CURSOR acc_sys_option is
1673 select account_derivation
1674 from okl_sys_acct_opts;
1675 --end: | 02-APR-07 cklee R12 Billing enhancement project
1676
1677 --start: | 02-APR-07 cklee R12 Billing enhancement project |
1678 /*commented out for R12
1679 CURSOR xsi_csr ( p_contract_number IN VARCHAR2 ) IS
1680 SELECT
1681 A.ID,
1682 A.OBJECT_VERSION_NUMBER,
1683 A.SFWT_FLAG,
1684 A.ISI_ID,
1685 A.TRX_DATE,
1686 A.CUSTOMER_ID,
1687 A.RECEIPT_METHOD_ID,
1688 A.TERM_ID,
1689 A.CURRENCY_CODE,
1690 A.CURRENCY_CONVERSION_TYPE,
1691 A.CURRENCY_CONVERSION_RATE,
1692 A.CURRENCY_CONVERSION_DATE,
1693 A.CUSTOMER_ADDRESS_ID,
1694 A.SET_OF_BOOKS_ID,
1695 A.RECEIVABLES_INVOICE_ID,
1696 A.CUST_TRX_TYPE_ID,
1697 A.INVOICE_MESSAGE,
1698 A.DESCRIPTION,
1699 A.XTRX_CONS_INVOICE_NUMBER,
1700 A.XTRX_FORMAT_TYPE,
1701 A.XTRX_PRIVATE_LABEL,
1702 A.ATTRIBUTE_CATEGORY,
1703 A.ATTRIBUTE1,
1704 A.ATTRIBUTE2,
1705 A.ATTRIBUTE3,
1706 A.ATTRIBUTE4,
1707 A.ATTRIBUTE5,
1708 A.ATTRIBUTE6,
1709 A.ATTRIBUTE7,
1710 A.ATTRIBUTE8,
1711 A.ATTRIBUTE9,
1712 A.ATTRIBUTE10,
1713 A.ATTRIBUTE11,
1714 A.ATTRIBUTE12,
1715 A.ATTRIBUTE13,
1716 A.ATTRIBUTE14,
1717 A.ATTRIBUTE15,
1718 A.REFERENCE_LINE_ID,
1719 A.TRX_NUMBER,
1720 A.CUSTOMER_BANK_ACCOUNT_ID,
1721 A.TAX_EXEMPT_FLAG,
1722 A.TAX_EXEMPT_REASON_CODE,
1723 A.XTRX_INVOICE_PULL_YN,
1724 A.TRX_STATUS_CODE,
1725 A.REQUEST_ID,
1726 A.PROGRAM_APPLICATION_ID,
1727 A.PROGRAM_ID,
1728 A.PROGRAM_UPDATE_DATE,
1729 A.ORG_ID,
1730 A.CREATED_BY,
1731 A.CREATION_DATE,
1732 A.LAST_UPDATED_BY,
1733 A.LAST_UPDATE_DATE,
1734 A.LAST_UPDATE_LOGIN,
1735 A.LEGAL_ENTITY_ID -- for LE Uptake project 08-11-2006
1736 FROM Okl_Ext_Sell_Invs_V a,
1737 Okl_Xtl_Sell_Invs_V b
1738 WHERE a.ID = b.XSI_ID_DETAILS
1739 AND xtrx_contract = p_contract_number
1740 AND trx_status_code = 'ENTERED';
1741
1742
1743 CURSOR xls_csr ( p_xsi_id IN NUMBER ) IS
1744 SELECT
1745 B.ID,
1746 B.OBJECT_VERSION_NUMBER,
1747 B.SFWT_FLAG,
1748 B.TLD_ID,
1749 B.LSM_ID,
1750 B.TIL_ID,
1751 B.ILL_ID,
1752 B.XSI_ID_DETAILS,
1753 B.LINE_TYPE,
1754 B.DESCRIPTION,
1755 B.AMOUNT,
1756 B.QUANTITY,
1757 B.XTRX_CONS_LINE_NUMBER,
1758 B.XTRX_CONTRACT,
1759 B.XTRX_ASSET,
1760 B.XTRX_STREAM_GROUP,
1761 B.XTRX_STREAM_TYPE,
1762 B.XTRX_CONS_STREAM_ID,
1763 B.ISL_ID,
1764 B.SEL_ID,
1765 B.ATTRIBUTE_CATEGORY,
1766 B.ATTRIBUTE1,
1767 B.ATTRIBUTE2,
1768 B.ATTRIBUTE3,
1769 B.ATTRIBUTE4,
1770 B.ATTRIBUTE5,
1771 B.ATTRIBUTE6,
1772 B.ATTRIBUTE7,
1773 B.ATTRIBUTE8,
1774 B.ATTRIBUTE9,
1775 B.ATTRIBUTE10,
1776 B.ATTRIBUTE11,
1777 B.ATTRIBUTE12,
1778 B.ATTRIBUTE13,
1779 B.ATTRIBUTE14,
1780 B.ATTRIBUTE15,
1781 B.REQUEST_ID,
1782 B.PROGRAM_APPLICATION_ID,
1783 B.PROGRAM_ID,
1784 B.PROGRAM_UPDATE_DATE,
1785 B.ORG_ID,
1786 B.CREATED_BY,
1787 B.CREATION_DATE,
1788 B.LAST_UPDATED_BY,
1789 B.LAST_UPDATE_DATE,
1790 B.LAST_UPDATE_LOGIN
1791 FROM Okl_Xtl_Sell_Invs_V b
1792 WHERE b.xsi_id_details = p_xsi_id;
1793 */
1794 -- added the following cusor to retrieve billing from internal tables
1795
1796 CURSOR xfer_csr ( p_contract_number IN VARCHAR2 ) IS
1797 SELECT
1798 TAI.ID TAI_ID
1799 -- , TIL.AMOUNT AMOUNT
1800 -- 19-Mar-2007 cklee -- Change amount referece to TLD instead |
1801 , TLD.AMOUNT AMOUNT
1802 , TIL.DESCRIPTION LINE_DESCRIPTION
1803 , NVL(TLD.INVENTORY_ITEM_ID, TIL.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID
1804 , TIL.inv_receiv_line_code LINE_TYPE
1805 , TIL.QUANTITY
1806 , TIL.LINE_NUMBER
1807 , NVL(TLD.STY_ID, TIL.STY_ID) STY_ID
1808 , KHR.ID KHR_ID
1809 , KHR.CONTRACT_NUMBER
1810 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1811 -- , KLE.NAME ASSET_NUMBER
1812 , NULL ASSET_NUMBER
1813 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1814 , TLD.INVOICE_FORMAT_LINE_TYPE -- STREAM_GROUP
1815 , STY.NAME STREAM_TYPE
1816 , TAI.CURRENCY_CODE
1817 , TAI.currency_conversion_date
1818 , TAI.currency_conversion_rate
1819 , TAI.currency_conversion_type
1820 , TAI.CUST_TRX_TYPE_ID
1821 , TAI.IBT_ID CUSTOMER_ADDRESS_ID
1822 -- , TAI.CUSTOMER_BANK_ACCOUNT_ID
1823 , NVL(TIL.bank_acct_id, TAI.CUSTOMER_BANK_ACCOUNT_ID) CUSTOMER_BANK_ACCOUNT_ID
1824 , TAI.IXX_ID CUSTOMER_ID
1825 , TAI.DESCRIPTION HDR_DESCRIPTION
1826 , NULL INVOICE_MESSAGE
1827 , TAI.ORG_ID
1828 , TAI.IRM_ID RECEIPT_METHOD_ID
1829 , TAI.SET_OF_BOOKS_ID
1830 , TAI.TAX_EXEMPT_FLAG
1831 , TAI.IRT_ID TERM_ID
1832 , TAI.DATE_INVOICED TRX_DATE
1833 -- , TAI.TRX_NUMBER
1834 --if auto-transaction generation is turn on, invoice_number (trx_number) is not a required column.
1835 , NULL TRX_NUMBER -- refer to metalink: Note:277086.1
1836 , TAI.CONSOLIDATED_INVOICE_NUMBER
1837 , TLD.INVOICE_FORMAT_TYPE
1838 , TAI.INVOICE_PULL_YN
1839 , TAI.PRIVATE_LABEL
1840 , TAI.LEGAL_ENTITY_ID -- for LE Uptake project 08-11-2006
1841 , NULL ACCOUNT_CLASS
1842 , NULL DIST_AMOUNT
1843 , NULL DIST_PERCENT
1844 , NULL CODE_COMBINATION_ID
1845 -- , XLS.LSM_ID
1846 , STY.ACCRUAL_YN rev_rec_basis
1847 , NULL CM_ACCT_RULE
1848 , TLD.TLD_ID_REVERSES rev_txn_id
1849 -- , NULL REV_LSM_ID
1850 , NVL(TLD.INVENTORY_ORG_ID, TIL.INVENTORY_ORG_ID) INVENTORY_ORG_ID
1851 , KHR.inv_organization_id WARE_HOUSE_ID
1852 , NVL(TLD.KLE_ID, TIL.KLE_ID) KLE_ID
1853 , NULL SHIP_TO
1854 , NULL l_inv_id
1855 , NULL uom_code
1856 , TLD.ID TXN_ID
1857 --
1858 -- R12 additional columns pass to AR interface
1859 , TAI.OKL_SOURCE_BILLING_TRX
1860 , TAI.Investor_Agreement_Number
1861 , TAI.Investor_Name
1862 , (select qte.quote_number from OKL_TRX_QUOTES_B qte where qte.id = TAI.QTE_ID) Quote_number
1863 , NULL rbk_request_number
1864 , TLD.RBK_ORI_INVOICE_NUMBER
1865 , TLD.RBK_ORI_INVOICE_LINE_NUMBER
1866 , TLD.RBK_ADJUSTMENT_DATE
1867 , TAI.INF_ID
1868 , TAI.TRY_ID
1869 , TRYT.NAME TRY_NAME
1870 -- start: bug 6744584 .. racheruv. get the contingency_id and pass to AR
1871 -- for cash basis rev rec method on stream type.
1872 , STY.CONTINGENCY_ID
1873 -- end : bug 6744584 .. racheruv
1874 FROM OKL_TXD_AR_LN_DTLS_B TLD,
1875 OKL_TXL_AR_INV_LNS_V TIL,
1876 OKL_TRX_AR_INVOICES_V TAI,
1877 OKC_K_HEADERS_ALL_B KHR,
1878 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1879 -- OKC_K_LINES_V KLE,
1880 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1881 OKL_STRM_TYPE_V STY,
1882 OKL_TRX_TYPES_TL TRYT--,
1883 -- OKL_PARALLEL_PROCESSES OPP
1884 WHERE TLD.STY_ID = STY.ID
1885 AND TLD.TIL_ID_DETAILS = TIL.ID
1886 AND TIL.TAI_ID = TAI.ID
1887 AND TAI.KHR_ID = KHR.ID
1888 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1889 -- AND KLE.ID = TIL.KLE_ID
1890 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1891 AND TAI.TRY_ID = TRYT.ID
1892 AND TRYT.LANGUAGE = 'US'
1893 AND TAI.TRX_STATUS_CODE = 'SUBMITTED'
1894 AND TLD.receivables_invoice_id IS null
1895 AND KHR.CONTRACT_NUMBER = p_contract_number
1896 AND TAI.okl_source_billing_trx = 'STREAM' -- cklee 04/10/07 handle regular stream billing only
1897 -- AND OPP.OBJECT_TYPE = 'XTRX_CONTRACT'
1898 -- AND OPP.OBJECT_VALUE = KHR.CONTRACT_NUMBER
1899 -- AND OPP.ASSIGNED_PROCESS = p_assigned_process
1900 ORDER BY TAI.ID
1901 ;
1902
1903 lxfer_rec OKL_ARINTF_PVT.xfer_rec_type;
1904 -- xfer_rec OKL_ARINTF_PVT.xfer_rec_type;
1905
1906 --end: | 02-APR-07 cklee R12 Billing enhancement project |
1907 -- ------------------------------------------
1908 -- Variable definition for AR API call
1909 -- ------------------------------------------
1910 l_batch_source_rec AR_INVOICE_API_PUB.batch_source_rec_type;
1911 l_init_batch_source_rec AR_INVOICE_API_PUB.batch_source_rec_type;
1912
1913 l_contingency_tbl AR_INVOICE_API_PUB.trx_contingencies_tbl_type;
1914 l_init_contingency_tbl AR_INVOICE_API_PUB.trx_contingencies_tbl_type;
1915
1916 l_trx_header_tbl AR_INVOICE_API_PUB.trx_header_tbl_type;
1917 l_init_trx_header_tbl AR_INVOICE_API_PUB.trx_header_tbl_type;
1918
1919 l_trx_lines_tbl AR_INVOICE_API_PUB.trx_line_tbl_type;
1920 l_init_trx_lines_tbl AR_INVOICE_API_PUB.trx_line_tbl_type;
1921
1922 --start: | 02-APR-07 cklee R12 Billing enhancement project |
1923 /*commented out for R12
1924 --Bug# 4488818: Sales Tax Billing
1925 l_tax_indx NUMBER;
1926 l_tax_line_number NUMBER;
1927 */
1928 --end: | 02-APR-07 cklee R12 Billing enhancement project |
1929
1930 l_trx_dist_tbl AR_INVOICE_API_PUB.trx_dist_tbl_type;
1931 l_init_trx_dist_tbl AR_INVOICE_API_PUB.trx_dist_tbl_type;
1932
1933 l_trx_salescredits_tbl AR_INVOICE_API_PUB.trx_salescredits_tbl_type;
1934 l_init_trx_salescredits_tbl AR_INVOICE_API_PUB.trx_salescredits_tbl_type;
1935
1936 --start: | 02-APR-07 cklee R12 Billing enhancement project |
1937 /* commented out for R12
1938 l_init_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
1939 r_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
1940
1941 l_init_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
1942 r_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
1943 */
1944 --end: | 02-APR-07 cklee R12 Billing enhancement project |
1945
1946 l_num_cnt NUMBER;
1947 l_customer_trx_id NUMBER;
1948 l_msg_count NUMBER;
1949 l_msg_data VARCHAR2(2000);
1950 l_cnt NUMBER;
1951 l_err_cnt NUMBER;
1952 l_ar_inv_num ra_customer_trx_all.trx_number%TYPE;
1953
1954 -- ------------------------------------------
1955 -- For Screen Display of AR Invoice Number
1956 -- ------------------------------------------
1957 CURSOR ar_invs_csr( p_cust_trx_id NUMBER ) IS
1958 SELECT trx_number
1959 FROM ra_customer_trx_all
1960 WHERE customer_trx_id = p_cust_trx_id;
1961
1962 CURSOR ar_trx_errs_csr( p_hdr_id NUMBER ) IS
1963 SELECT *
1964 FROM ar_trx_errors_gt
1965 WHERE TRX_HEADER_ID = p_hdr_id;
1966
1967 l_ar_inv_disp_num VARCHAR2(3000);
1968
1969 AR_API_CALL_EXCP EXCEPTION;
1970
1971 --start: | 02-APR-07 cklee R12 Billing enhancement project |
1972 /* commented out for R12
1973 CURSOR tax_amount_csr( p_cust_trx_id NUMBER ) IS
1974 SELECT SUM(NVL( extended_amount ,0))
1975 FROM ra_customer_trx_lines
1976 WHERE customer_trx_id = p_cust_trx_id AND
1977 LINE_TYPE = 'TAX';
1978
1979 l_tax_amount ra_customer_trx_lines.extended_amount%TYPE;
1980
1981 l_lln_id okl_cnsld_ar_lines_v.id%TYPE;
1982 l_cnr_id okl_cnsld_ar_hdrs_v.id%TYPE;
1983 l_due_date okl_cnsld_ar_hdrs_v.due_date%TYPE;
1984
1985 CURSOR cnr_lln_csr( p_lsm_id NUMBER ) IS
1986 SELECT cnr.id cnr_id, lln.id lln_id
1987 FROM okl_cnsld_ar_hdrs_v cnr,
1988 okl_cnsld_ar_lines_v lln,
1989 okl_cnsld_ar_strms_v lsm
1990 WHERE cnr.id = lln.cnr_id
1991 AND lln.id = lsm.lln_id
1992 AND lsm.id = p_lsm_id;
1993
1994
1995 CURSOR ar_due_date_csr ( p_cust_trx_id NUMBER ) IS
1996 SELECT due_date
1997 FROM ar_payment_schedules_all
1998 WHERE customer_trx_id = p_cust_trx_id;
1999
2000 CURSOR get_accrual_csr ( p_lsm_id NUMBER ) IS
2001 SELECT NVL(sty.accrual_yn, '1')
2002 FROM okl_cnsld_ar_strms_v lsm
2003 , okl_strm_type_v sty
2004 WHERE lsm.id = p_lsm_id
2005 AND lsm.sty_id = sty.id;
2006
2007
2008 l_rev_rec_basis okl_strm_type_b.accrual_yn%type;
2009
2010 CURSOR sales_rep_csr IS
2011 SELECT SALESREP_ID, SALESREP_NUMBER
2012 FROM ra_salesreps
2013 WHERE NAME = 'No Sales Credit';
2014
2015
2016 CURSOR sales_type_credit_csr IS
2017 SELECT sales_credit_type_id
2018 FROM so_sales_credit_types
2019 WHERE name = 'Quota Sales Credit';
2020
2021 l_salesrep_id ra_salesreps.SALESREP_ID%TYPE;
2022 l_salesrep_number ra_salesreps.SALESREP_NUMBER%TYPE;
2023 l_sales_type_credit so_sales_credit_types.sales_credit_type_id%TYPE;
2024 */
2025 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2026
2027 CURSOR seq_csr IS
2028 SELECT AR_INTERFACE_CONTS_S.nextval
2029 FROM DUAL;
2030
2031 CURSOR err_csr IS
2032 SELECT error_message, invalid_value
2033 FROM ar_trx_errors_gt;
2034
2035 -- For PPD process error reporting
2036
2037 --gkhuntet FP Bug 5516814 start
2038 l_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT
2039 p_contract_number;
2040 --gkhuntet FP Bug 5516814 end
2041
2042 l_overall_err_sts VARCHAR2(1);
2043
2044 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2045 /* commented out for R12
2046 --Bug# 4488818: Sales Tax Billing
2047 CURSOR xfer_tax_csr(p_trx_id IN NUMBER) IS
2048 SELECT TXS.TRX_BUSINESS_CATEGORY,
2049 TTD.ID,
2050 TTD.TAXABLE_AMT,
2051 TTD.TAX_RATE_CODE,
2052 TTD.TAX_AMT,
2053 TTD.tax_rate_id
2054 FROM OKL_TAX_SOURCES TXS,
2055 OKL_TAX_TRX_DETAILS TTD
2056 WHERE TXS.TRX_LINE_ID = p_trx_id
2057 AND TTD.TXS_ID = TXS.ID;
2058
2059 l_tax_trx_line_id NUMBER;
2060 */
2061 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2062
2063 i Number;
2064 j Number;
2065
2066 --Added as part of Bug# 14119181
2067 l_cust_acct_id NUMBER;
2068 l_cust_party_id NUMBER;
2069 l_bankruptcy_flag VARCHAR2(1);
2070 l_bkr_khr_id NUMBER;
2071
2072 CURSOR c_get_khr_id(p_contract_nbr VARCHAR2) IS
2073 SELECT id
2074 FROM okc_k_headers_b
2075 WHERE contract_number = p_contract_nbr;
2076
2077 BEGIN
2078
2079 ------------------------------------------------------------
2080 -- Start processing
2081 ------------------------------------------------------------
2082 l_overall_err_sts := Okl_Api.G_RET_STS_SUCCESS;
2083
2084 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2085
2086 l_return_status := Okl_Api.START_ACTIVITY(
2087 p_api_name => l_api_name,
2088 p_pkg_name => G_PKG_NAME,
2089 p_init_msg_list => p_init_msg_list,
2090 l_api_version => l_api_version,
2091 p_api_version => p_api_version,
2092 p_api_type => '_PVT',
2093 x_return_status => l_return_status);
2094
2095 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2096 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2097 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2098 RAISE Okl_Api.G_EXCEPTION_ERROR;
2099 END IF;
2100 --gkhuntet FP Bug 5516814 start
2101 FOR c_get_adv_rcpt_for_cont_rec IN c_get_adv_rcpt_for_cont (p_contract_number)
2102 LOOP
2103 Begin
2104 l_contract_number:= c_get_adv_rcpt_for_cont_rec.contract_number;
2105 --gkhuntet FP Bug 5516814 end
2106
2107
2108
2109
2110 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2111 -- Verify if contract number is passed in
2112 -- gboomina Bug 7168534 - start
2113 -- passing correct variable for contract number
2114 IF (l_contract_number is null or l_contract_number = okl_api.g_miss_char ) THEN -- rmunjulu R12 Fixes
2115 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2116 p_msg_name => G_REQUIRED_VALUE,
2117 p_token1 => G_COL_NAME_TOKEN,
2118 p_token1_value => 'OKL_BPD_ADVANCED_BILLING_PVT.ADVANCED_BILLING.p_contract_number');
2119
2120 RAISE OKL_API.G_EXCEPTION_ERROR;
2121 END IF;
2122 -- gboomina Bug 7168534 - end
2123 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2124
2125
2126 -- Start of code added as part of Bug# 14119181
2127 BEGIN
2128
2129 l_cust_acct_id := NULL;
2130 l_cust_party_id := NULL;
2131 l_bkr_khr_id := NULL;
2132
2133 OPEN c_get_khr_id(l_contract_number);
2134 FETCH c_get_khr_id INTO l_bkr_khr_id;
2135 CLOSE c_get_khr_id;
2136
2137 IF (l_bkr_khr_id IS NOT NULL)
2138 THEN
2139
2140 okl_stream_billing_pvt.get_customer_dtls (p_khr_id => l_bkr_khr_id,
2141 p_cust_acct_id => l_cust_acct_id,
2142 p_cust_party_id => l_cust_party_id);
2143 END IF;
2144
2145 l_bankruptcy_flag := NULL;
2146
2147 IF (l_cust_acct_id IS NOT NULL AND l_cust_party_id IS NOT NULL)
2148 THEN
2149
2150 l_bankruptcy_flag := Okl_Stream_Billing_Pvt.get_bankruptcy_status_new (p_cust_acct_id => l_cust_acct_id,
2151 p_cust_party_id => l_cust_party_id
2152 );
2153 END IF;
2154
2155 IF (l_bankruptcy_flag = 'Y') THEN
2156
2157 IF (G_DEBUG_ENABLED = 'Y') THEN
2158 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2159 END IF;
2160
2161 IF (G_IS_DEBUG_STATEMENT_ON = TRUE) THEN
2162 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoices of contract# ' || l_contract_number || ' not billed due to bankruptcy.');
2163 END IF;
2164 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoices of contract# ' || l_contract_number || ' not billed due to bankruptcy.');
2165
2166 END IF;
2167
2168 END;
2169 -- End of code added as part of Bug# 14119181
2170
2171 IF (l_bankruptcy_flag = 'N') --IF (l_bankruptcy_flag = 'N') Condition is Added as part of Bug# 14119181
2172 THEN
2173
2174 BEGIN -- BEGIN is added as part of Bug# 14119181
2175 -- ----------------------------------------------
2176 -- Bill eligible streams
2177 -- ----------------------------------------------
2178 Okl_Stream_Billing_Pvt.bill_streams (
2179 p_api_version => p_api_version,
2180 p_init_msg_list => p_init_msg_list,
2181 x_return_status => l_return_status,
2182 x_msg_count => x_msg_count,
2183 x_msg_data => x_msg_data,
2184 --p_contract_number => p_contract_number,
2185 p_from_bill_date => p_from_bill_date,
2186 p_contract_number =>l_contract_number ,
2187 --gkhuntet FP Bug 5516814 end
2188 p_to_bill_date => p_to_bill_date,
2189 p_source => p_source);
2190
2191 IF p_source = 'PRINCIPAL_PAYDOWN' THEN
2192 l_overall_err_sts := l_return_status;
2193 END IF;
2194
2195
2196 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2197 /* For R12, Okl_Internal_To_External.Internal_To_External logic has been migrated to okl_internal_billing_pvt.create_billing_trx();
2198 --so no need to call API here.
2199 IF p_source = 'TERM_QUOTE' THEN
2200 Okl_Internal_To_External.Internal_To_External(
2201 p_api_version => p_api_version,
2202 p_init_msg_list => p_init_msg_list,
2203 x_return_status => l_return_status,
2204 x_msg_count => x_msg_count,
2205 x_msg_data => x_msg_data,
2206 p_contract_number => p_contract_number);
2207 END IF;
2208
2209
2210 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2211 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
2212 p_msg_name => 'OKL_BPD_RTA_REC_ERR',
2213 p_token1 => 'TABLE',
2214 p_token1_value => 'AR BATCH SOURCE');
2215 RAISE OKL_API.G_EXCEPTION_ERROR;
2216 END IF;
2217
2218 -- ----------------------------------------------
2219 -- Contract Specific Consolidation
2220 -- ----------------------------------------------
2221 Okl_Cons_Bill.create_cons_bill(p_contract_number => p_contract_number,
2222 p_api_version => p_api_version,
2223 p_init_msg_list => p_init_msg_list,
2224 x_return_status => x_return_status,
2225 x_msg_count => x_msg_count,
2226 x_msg_data => x_msg_data);
2227
2228 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2229 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
2230 p_msg_name => 'OKL_BPD_RTA_REC_ERR',
2231 p_token1 => 'TABLE',
2232 p_token1_value => 'AR BATCH SOURCE');
2233 RAISE OKL_API.G_EXCEPTION_ERROR;
2234 END IF;
2235 */
2236 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2237
2238 --start: | 02-APR-07 cklee R12 Billing enhancement project
2239 OPEN acc_sys_option;
2240 FETCH acc_sys_option INTO G_ACC_SYS_OPTION;
2241 CLOSE acc_sys_option;
2242 -- ----------------------------
2243 -- Work out common parameters
2244 -- ----------------------------
2245
2246 /* OPEN l_get_inv_org_yn_csr( l_org_id ); */
2247 /* FETCH l_get_inv_org_yn_csr INTO l_use_inv_org; */
2248 /* CLOSE l_get_inv_org_yn_csr; */
2249
2250 --end: | 02-APR-07 cklee R12 Billing enhancement project
2251
2252
2253 l_num_cnt := 0;
2254 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2255 --gkhuntet FP Bug 5516814 start
2256 FOR xfer_rec in xfer_csr( l_contract_number ) LOOP
2257
2258 --gkhuntet FP Bug 5516814 end
2259 -- FOR xsi_rec in xsi_csr( p_contract_number ) LOOP
2260 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2261 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2262 -- lxfer_rec := xfer_rec;
2263 -- somehow we are not able to pass xfer_rec to procedure so we have to copy to local record
2264 -- lxfer_rec := xfer_rec;
2265 lxfer_rec.TAI_ID := xfer_rec.TAI_ID;
2266 lxfer_rec.AMOUNT := xfer_rec.AMOUNT;
2267 lxfer_rec.LINE_DESCRIPTION := xfer_rec.LINE_DESCRIPTION;
2268 lxfer_rec.INVENTORY_ITEM_ID := xfer_rec.INVENTORY_ITEM_ID;
2269 lxfer_rec.LINE_TYPE := xfer_rec.LINE_TYPE;
2270 lxfer_rec.QUANTITY := xfer_rec.QUANTITY;
2271 lxfer_rec.LINE_NUMBER := xfer_rec.LINE_NUMBER;
2272 lxfer_rec.STY_ID := xfer_rec.STY_ID;
2273 lxfer_rec.KHR_ID := xfer_rec.KHR_ID;
2274 lxfer_rec.CONTRACT_NUMBER := xfer_rec.CONTRACT_NUMBER;
2275 lxfer_rec.ASSET_NUMBER := xfer_rec.ASSET_NUMBER;
2276 lxfer_rec.INVOICE_FORMAT_LINE_TYPE := xfer_rec.INVOICE_FORMAT_LINE_TYPE;
2277 lxfer_rec.STREAM_TYPE := xfer_rec.STREAM_TYPE;
2278 lxfer_rec.CURRENCY_CODE := xfer_rec.CURRENCY_CODE;
2279 lxfer_rec.currency_conversion_date := xfer_rec.currency_conversion_date;
2280 lxfer_rec.currency_conversion_rate := xfer_rec.currency_conversion_rate;
2281 lxfer_rec.currency_conversion_type := xfer_rec.currency_conversion_type;
2282 lxfer_rec.CUST_TRX_TYPE_ID := xfer_rec.CUST_TRX_TYPE_ID;
2283 lxfer_rec.CUSTOMER_ADDRESS_ID := xfer_rec.CUSTOMER_ADDRESS_ID;
2284 lxfer_rec.CUSTOMER_BANK_ACCOUNT_ID := xfer_rec.CUSTOMER_BANK_ACCOUNT_ID;
2285 lxfer_rec.CUSTOMER_ID := xfer_rec.CUSTOMER_ID;
2286 lxfer_rec.HDR_DESCRIPTION := xfer_rec.HDR_DESCRIPTION;
2287 lxfer_rec.INVOICE_MESSAGE := xfer_rec.INVOICE_MESSAGE;
2288 lxfer_rec.ORG_ID := xfer_rec.ORG_ID;
2289 lxfer_rec.RECEIPT_METHOD_ID := xfer_rec.RECEIPT_METHOD_ID;
2290 lxfer_rec.SET_OF_BOOKS_ID := xfer_rec.SET_OF_BOOKS_ID;
2291 lxfer_rec.TAX_EXEMPT_FLAG := xfer_rec.TAX_EXEMPT_FLAG;
2292 lxfer_rec.TERM_ID := xfer_rec.TERM_ID;
2293 lxfer_rec.TRX_DATE := xfer_rec.TRX_DATE;
2294 lxfer_rec.TRX_NUMBER := xfer_rec.TRX_NUMBER;
2295 lxfer_rec.CONSOLIDATED_INVOICE_NUMBER := xfer_rec.CONSOLIDATED_INVOICE_NUMBER;
2296 lxfer_rec.INVOICE_FORMAT_TYPE := xfer_rec.INVOICE_FORMAT_TYPE;
2297 lxfer_rec.INVOICE_PULL_YN := xfer_rec.INVOICE_PULL_YN;
2298 lxfer_rec.PRIVATE_LABEL := xfer_rec.PRIVATE_LABEL;
2299 lxfer_rec.LEGAL_ENTITY_ID := xfer_rec.LEGAL_ENTITY_ID;
2300 lxfer_rec.ACCOUNT_CLASS := xfer_rec.ACCOUNT_CLASS;
2301 lxfer_rec.DIST_AMOUNT := xfer_rec.DIST_AMOUNT;
2302 lxfer_rec.DIST_PERCENT := xfer_rec.DIST_PERCENT;
2303 lxfer_rec.CODE_COMBINATION_ID := xfer_rec.CODE_COMBINATION_ID;
2304 lxfer_rec.rev_rec_basis := xfer_rec.rev_rec_basis;
2305 lxfer_rec.cm_acct_rule := xfer_rec.cm_acct_rule;
2306 lxfer_rec.rev_txn_id := xfer_rec.rev_txn_id;
2307 lxfer_rec.INVENTORY_ORG_ID := xfer_rec.INVENTORY_ORG_ID;
2308 lxfer_rec.ware_house_id := xfer_rec.ware_house_id;
2309 lxfer_rec.kle_id := xfer_rec.kle_id;
2310 lxfer_rec.ship_to := xfer_rec.ship_to;
2311 lxfer_rec.l_inv_id := xfer_rec.l_inv_id;
2312 lxfer_rec.uom_code := xfer_rec.uom_code;
2313 lxfer_rec.txn_id := xfer_rec.txn_id;
2314 lxfer_rec.OKL_SOURCE_BILLING_TRX := xfer_rec.OKL_SOURCE_BILLING_TRX;
2315 lxfer_rec.Investor_Agreement_Number := xfer_rec.Investor_Agreement_Number;
2316 lxfer_rec.Investor_Name := xfer_rec.Investor_Name;
2317 lxfer_rec.Quote_number := xfer_rec.Quote_number;
2318 lxfer_rec.rbk_request_number := xfer_rec.rbk_request_number;
2319 lxfer_rec.RBK_ORI_INVOICE_NUMBER := xfer_rec.RBK_ORI_INVOICE_NUMBER;
2320 lxfer_rec.RBK_ORI_INVOICE_LINE_NUMBER := xfer_rec.RBK_ORI_INVOICE_LINE_NUMBER;
2321 lxfer_rec.RBK_ADJUSTMENT_DATE := xfer_rec.RBK_ADJUSTMENT_DATE;
2322 lxfer_rec.INF_ID := xfer_rec.INF_ID;
2323 lxfer_rec.TRY_ID := xfer_rec.TRY_ID;
2324 lxfer_rec.TRY_NAME := xfer_rec.TRY_NAME;
2325 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2326 l_num_cnt := l_num_cnt + 1;
2327 ---------------------------------------
2328 -- Prepare batch_source rec
2329 ---------------------------------------
2330 l_batch_source_rec := l_init_batch_source_rec;
2331
2332 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Preparing Batch rec '||l_num_cnt);
2333 PREPARE_BATCH_SOURCE_REC(
2334 x_return_status
2335 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2336 ,xfer_rec.TRX_DATE
2337 -- ,xsi_rec.TRX_DATE
2338 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2339 ,l_batch_source_rec );
2340 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Done Preparing Batch rec '||l_num_cnt);
2341
2342 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2343 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
2344 p_msg_name => 'OKL_BPD_RTA_REC_ERR',
2345 p_token1 => 'TABLE',
2346 p_token1_value => 'AR BATCH SOURCE'
2347 );
2348 RAISE OKL_API.G_EXCEPTION_ERROR;
2349 END IF;
2350
2351 ---------------------------------------
2352 -- Prepare trx hdr tbl
2353 ---------------------------------------
2354 l_trx_header_tbl := l_init_trx_header_tbl;
2355
2356 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2357 /* commented out for R12
2358 -- -----------------------------------------
2359 -- Initialize XSI record
2360 -- -----------------------------------------
2361 r_xsiv_rec := l_init_xsiv_rec;
2362
2363 -- ------------------------------
2364 -- Populate XSI record
2365 -- ------------------------------
2366 r_xsiv_rec.ID := xsi_rec.ID;
2367 r_xsiv_rec.OBJECT_VERSION_NUMBER := xsi_rec.OBJECT_VERSION_NUMBER;
2368 r_xsiv_rec.SFWT_FLAG := xsi_rec.SFWT_FLAG;
2369 r_xsiv_rec.ISI_ID := xsi_rec.ISI_ID;
2370 r_xsiv_rec.TRX_DATE := xsi_rec.TRX_DATE;
2371 r_xsiv_rec.CUSTOMER_ID := xsi_rec.CUSTOMER_ID;
2372 r_xsiv_rec.RECEIPT_METHOD_ID := xsi_rec.RECEIPT_METHOD_ID;
2373 r_xsiv_rec.TERM_ID := xsi_rec.TERM_ID;
2374 r_xsiv_rec.CURRENCY_CODE := xsi_rec.CURRENCY_CODE;
2375 r_xsiv_rec.CURRENCY_CONVERSION_TYPE := xsi_rec.CURRENCY_CONVERSION_TYPE;
2376 r_xsiv_rec.CURRENCY_CONVERSION_RATE := xsi_rec.CURRENCY_CONVERSION_RATE;
2377 r_xsiv_rec.CURRENCY_CONVERSION_DATE := xsi_rec.CURRENCY_CONVERSION_DATE;
2378 r_xsiv_rec.CUSTOMER_ADDRESS_ID := xsi_rec.CUSTOMER_ADDRESS_ID;
2379 r_xsiv_rec.SET_OF_BOOKS_ID := xsi_rec.SET_OF_BOOKS_ID;
2380 r_xsiv_rec.RECEIVABLES_INVOICE_ID := xsi_rec.RECEIVABLES_INVOICE_ID;
2381 r_xsiv_rec.CUST_TRX_TYPE_ID := xsi_rec.CUST_TRX_TYPE_ID;
2382 r_xsiv_rec.INVOICE_MESSAGE := xsi_rec.INVOICE_MESSAGE;
2383 r_xsiv_rec.DESCRIPTION := xsi_rec.DESCRIPTION;
2384 r_xsiv_rec.XTRX_CONS_INVOICE_NUMBER := xsi_rec.XTRX_CONS_INVOICE_NUMBER;
2385 r_xsiv_rec.XTRX_FORMAT_TYPE := xsi_rec.XTRX_FORMAT_TYPE;
2386 r_xsiv_rec.XTRX_PRIVATE_LABEL := xsi_rec.XTRX_PRIVATE_LABEL;
2387 r_xsiv_rec.ATTRIBUTE_CATEGORY := xsi_rec.ATTRIBUTE_CATEGORY;
2388 r_xsiv_rec.ATTRIBUTE1 := xsi_rec.ATTRIBUTE1;
2389 r_xsiv_rec.ATTRIBUTE2 := xsi_rec.ATTRIBUTE2;
2390 r_xsiv_rec.ATTRIBUTE3 := xsi_rec.ATTRIBUTE3;
2391 r_xsiv_rec.ATTRIBUTE4 := xsi_rec.ATTRIBUTE4;
2392 r_xsiv_rec.ATTRIBUTE5 := xsi_rec.ATTRIBUTE5;
2393 r_xsiv_rec.ATTRIBUTE6 := xsi_rec.ATTRIBUTE6;
2394 r_xsiv_rec.ATTRIBUTE7 := xsi_rec.ATTRIBUTE7;
2395 r_xsiv_rec.ATTRIBUTE8 := xsi_rec.ATTRIBUTE8;
2396 r_xsiv_rec.ATTRIBUTE9 := xsi_rec.ATTRIBUTE9;
2397 r_xsiv_rec.ATTRIBUTE10 := xsi_rec.ATTRIBUTE10;
2398 r_xsiv_rec.ATTRIBUTE11 := xsi_rec.ATTRIBUTE11;
2399 r_xsiv_rec.ATTRIBUTE12 := xsi_rec.ATTRIBUTE12;
2400 r_xsiv_rec.ATTRIBUTE13 := xsi_rec.ATTRIBUTE13;
2401 r_xsiv_rec.ATTRIBUTE14 := xsi_rec.ATTRIBUTE14;
2402 r_xsiv_rec.ATTRIBUTE15 := xsi_rec.ATTRIBUTE15;
2403 r_xsiv_rec.REFERENCE_LINE_ID := xsi_rec.REFERENCE_LINE_ID;
2404 r_xsiv_rec.TRX_NUMBER := xsi_rec.TRX_NUMBER;
2405 r_xsiv_rec.CUSTOMER_BANK_ACCOUNT_ID := xsi_rec.CUSTOMER_BANK_ACCOUNT_ID;
2406 r_xsiv_rec.TAX_EXEMPT_FLAG := xsi_rec.TAX_EXEMPT_FLAG;
2407 r_xsiv_rec.TAX_EXEMPT_REASON_CODE := xsi_rec.TAX_EXEMPT_REASON_CODE;
2408 r_xsiv_rec.XTRX_INVOICE_PULL_YN := xsi_rec.XTRX_INVOICE_PULL_YN;
2409 r_xsiv_rec.TRX_STATUS_CODE := xsi_rec.TRX_STATUS_CODE;
2410 r_xsiv_rec.REQUEST_ID := xsi_rec.REQUEST_ID;
2411 r_xsiv_rec.PROGRAM_APPLICATION_ID := xsi_rec.PROGRAM_APPLICATION_ID;
2412 r_xsiv_rec.PROGRAM_ID := xsi_rec.PROGRAM_ID;
2413 r_xsiv_rec.PROGRAM_UPDATE_DATE := xsi_rec.PROGRAM_UPDATE_DATE;
2414 r_xsiv_rec.ORG_ID := xsi_rec.ORG_ID;
2415 r_xsiv_rec.CREATED_BY := xsi_rec.CREATED_BY;
2416 r_xsiv_rec.CREATION_DATE := xsi_rec.CREATION_DATE;
2417 r_xsiv_rec.LAST_UPDATED_BY := xsi_rec.LAST_UPDATED_BY;
2418 r_xsiv_rec.LAST_UPDATE_DATE := xsi_rec.LAST_UPDATE_DATE;
2419 r_xsiv_rec.LAST_UPDATE_LOGIN := xsi_rec.LAST_UPDATE_LOGIN;
2420 r_xsiv_rec.LEGAL_ENTITY_ID := xsi_rec.LEGAL_ENTITY_ID; -- for LE Uptake project 08-11-2006
2421
2422 -- -----------------------------------------
2423 -- Initialize XLS record
2424 -- -----------------------------------------
2425 r_xlsv_rec := l_init_xlsv_rec;
2426
2427 -- ------------------------------
2428 -- Populate XLS record
2429 -- ------------------------------
2430 FOR xls_rec IN xls_csr( r_xsiv_rec.id ) LOOP
2431
2432 r_xlsv_rec.ID := xls_rec.ID;
2433 r_xlsv_rec.OBJECT_VERSION_NUMBER := xls_rec.OBJECT_VERSION_NUMBER;
2434 r_xlsv_rec.SFWT_FLAG := xls_rec.SFWT_FLAG;
2435 r_xlsv_rec.TLD_ID := xls_rec.TLD_ID;
2436 r_xlsv_rec.LSM_ID := xls_rec.LSM_ID;
2437 r_xlsv_rec.TIL_ID := xls_rec.TIL_ID;
2438 r_xlsv_rec.ILL_ID := xls_rec.ILL_ID;
2439 r_xlsv_rec.XSI_ID_DETAILS := xls_rec.XSI_ID_DETAILS;
2440 r_xlsv_rec.LINE_TYPE := xls_rec.LINE_TYPE;
2441 r_xlsv_rec.DESCRIPTION := xls_rec.DESCRIPTION;
2442 r_xlsv_rec.AMOUNT := xls_rec.AMOUNT;
2443 r_xlsv_rec.QUANTITY := xls_rec.QUANTITY;
2444 r_xlsv_rec.XTRX_CONS_LINE_NUMBER := xls_rec.XTRX_CONS_LINE_NUMBER;
2445 r_xlsv_rec.XTRX_CONTRACT := xls_rec.XTRX_CONTRACT;
2446 r_xlsv_rec.XTRX_ASSET := xls_rec.XTRX_ASSET;
2447 r_xlsv_rec.XTRX_STREAM_GROUP := xls_rec.XTRX_STREAM_GROUP;
2448 r_xlsv_rec.XTRX_STREAM_TYPE := xls_rec.XTRX_STREAM_TYPE;
2449 r_xlsv_rec.XTRX_CONS_STREAM_ID := xls_rec.XTRX_CONS_STREAM_ID;
2450 r_xlsv_rec.ISL_ID := xls_rec.ISL_ID;
2451 r_xlsv_rec.SEL_ID := xls_rec.SEL_ID;
2452 r_xlsv_rec.ATTRIBUTE_CATEGORY := xls_rec.ATTRIBUTE_CATEGORY;
2453 r_xlsv_rec.ATTRIBUTE1 := xls_rec.ATTRIBUTE1;
2454 r_xlsv_rec.ATTRIBUTE2 := xls_rec.ATTRIBUTE2;
2455 r_xlsv_rec.ATTRIBUTE3 := xls_rec.ATTRIBUTE3;
2456 r_xlsv_rec.ATTRIBUTE4 := xls_rec.ATTRIBUTE4;
2457 r_xlsv_rec.ATTRIBUTE5 := xls_rec.ATTRIBUTE5;
2458 r_xlsv_rec.ATTRIBUTE6 := xls_rec.ATTRIBUTE6;
2459 r_xlsv_rec.ATTRIBUTE7 := xls_rec.ATTRIBUTE7;
2460 r_xlsv_rec.ATTRIBUTE8 := xls_rec.ATTRIBUTE8;
2461 r_xlsv_rec.ATTRIBUTE9 := xls_rec.ATTRIBUTE9;
2462 r_xlsv_rec.ATTRIBUTE10 := xls_rec.ATTRIBUTE10;
2463 r_xlsv_rec.ATTRIBUTE11 := xls_rec.ATTRIBUTE11;
2464 r_xlsv_rec.ATTRIBUTE12 := xls_rec.ATTRIBUTE12;
2465 r_xlsv_rec.ATTRIBUTE13 := xls_rec.ATTRIBUTE13;
2466 r_xlsv_rec.ATTRIBUTE14 := xls_rec.ATTRIBUTE14;
2467 r_xlsv_rec.ATTRIBUTE15 := xls_rec.ATTRIBUTE15;
2468 r_xlsv_rec.REQUEST_ID := xls_rec.REQUEST_ID;
2469 r_xlsv_rec.PROGRAM_APPLICATION_ID := xls_rec.PROGRAM_APPLICATION_ID;
2470 r_xlsv_rec.PROGRAM_ID := xls_rec.PROGRAM_ID;
2471 r_xlsv_rec.PROGRAM_UPDATE_DATE := xls_rec.PROGRAM_UPDATE_DATE;
2472 r_xlsv_rec.ORG_ID := xls_rec.ORG_ID;
2473 r_xlsv_rec.CREATED_BY := xls_rec.CREATED_BY;
2474 r_xlsv_rec.CREATION_DATE := xls_rec.CREATION_DATE;
2475 r_xlsv_rec.LAST_UPDATED_BY := xls_rec.LAST_UPDATED_BY;
2476 r_xlsv_rec.LAST_UPDATE_DATE := xls_rec.LAST_UPDATE_DATE;
2477 r_xlsv_rec.LAST_UPDATE_LOGIN := xls_rec.LAST_UPDATE_LOGIN;
2478
2479 END LOOP;
2480 */
2481 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2482
2483 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Preparing Trx Hdr tbl '||l_num_cnt);
2484 PREPARE_TRX_HDR_TBL(
2485 x_return_status
2486 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2487 ,lxfer_rec
2488 -- ,r_xsiv_rec
2489 -- ,r_xlsv_rec
2490 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2491 ,p_source
2492 ,l_trx_header_tbl );
2493
2494 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Done Preparing Trx Hdr tbl '||l_num_cnt);
2495
2496
2497 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2498 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
2499 p_msg_name => 'OKL_BPD_RTA_REC_ERR',
2500 p_token1 => 'TABLE',
2501 p_token1_value => 'AR Transaction Header Table '
2502 );
2503 RAISE OKL_API.G_EXCEPTION_ERROR;
2504 END IF;
2505
2506 ---------------------------------------
2507 -- Prepare trx lines tbl
2508 ---------------------------------------
2509 l_trx_lines_tbl := l_init_trx_lines_tbl;
2510
2511 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Preparing Trx Lines tbl '||l_num_cnt);
2512 PREPARE_TRX_LNS_TBL(
2513 x_return_status
2514 ,l_num_cnt
2515 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2516 ,lxfer_rec
2517 -- ,r_xsiv_rec
2518 -- ,r_xlsv_rec
2519 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2520 ,l_trx_lines_tbl );
2521 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Done Preparing Trx Lines tbl '||l_num_cnt);
2522
2523 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2524 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
2525 p_msg_name => 'OKL_BPD_RTA_REC_ERR',
2526 p_token1 => 'TABLE',
2527 p_token1_value => 'AR Transaction Lines Table '
2528 );
2529 RAISE OKL_API.G_EXCEPTION_ERROR;
2530 END IF;
2531
2532 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2533 /* commented out for R12
2534 --Bug# 4488818: Sales Tax Billing
2535 l_tax_trx_line_id := NULL;
2536
2537 IF r_xlsv_rec.til_id IS NOT NULL THEN
2538 l_tax_trx_line_id := r_xlsv_rec.til_id ;
2539 ELSE
2540 l_tax_trx_line_id := r_xlsv_rec.tld_id ;
2541 END IF;
2542
2543 l_tax_line_number := 0;
2544 FOR xfer_tax_rec IN xfer_tax_csr(l_tax_trx_line_id) LOOP
2545
2546 l_tax_indx := l_trx_lines_tbl.count + 1;
2547 l_tax_line_number := l_tax_line_number + 1;
2548
2549 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Preparing Trx Tax Lines tbl '||(l_num_cnt + l_tax_line_number));
2550 --
2551 l_trx_lines_tbl(l_tax_indx).trx_header_id := 110;
2552 l_trx_lines_tbl(l_tax_indx).trx_line_id := (l_num_cnt + l_tax_line_number);
2553 l_trx_lines_tbl(l_tax_indx).link_to_trx_line_id := l_num_cnt;
2554 l_trx_lines_tbl(l_tax_indx).LINE_NUMBER := l_tax_line_number;
2555
2556 l_trx_lines_tbl(l_tax_indx).DESCRIPTION
2557 := NVL (NVL (r_xlsv_rec.DESCRIPTION, r_xsiv_rec.DESCRIPTION), 'OKL Billing')|| ' - Tax';
2558 l_trx_lines_tbl(l_tax_indx).LINE_TYPE := 'TAX';
2559
2560 l_trx_lines_tbl(l_tax_indx).INTERFACE_LINE_CONTEXT := 'OKL_MANUAL';
2561 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE1 := l_trx_lines_tbl(1).ATTRIBUTE1;
2562 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE2 := l_trx_lines_tbl(1).ATTRIBUTE2;
2563 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE3 := l_trx_lines_tbl(1).ATTRIBUTE3;
2564 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE4 := l_trx_lines_tbl(1).ATTRIBUTE4;
2565 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE5 := l_trx_lines_tbl(1).ATTRIBUTE5;
2566 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE6 := l_trx_lines_tbl(1).ATTRIBUTE6;
2567 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE7 := l_trx_lines_tbl(1).ATTRIBUTE7;
2568 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE8 := l_trx_lines_tbl(1).ATTRIBUTE8;
2569 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE9 := l_trx_lines_tbl(1).ATTRIBUTE9;
2570 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE10 := SUBSTR(xfer_tax_rec.ID, 1, 20);
2571 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE11 := SUBSTR (xfer_tax_rec.ID, 21);
2572 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE12 := NULL;
2573 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE13 := NULL;
2574 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE14 := NULL;
2575 l_trx_lines_tbl(l_tax_indx).ATTRIBUTE15 := NULL;
2576
2577 l_trx_lines_tbl(l_tax_indx).AMOUNT := xfer_tax_rec.TAX_AMT;
2578 l_trx_lines_tbl(l_tax_indx).TAX_PRECEDENCE := NULL;
2579 l_trx_lines_tbl(l_tax_indx).TAX_RATE := NULL;
2580 l_trx_lines_tbl(l_tax_indx).TAX_EXEMPTION_ID := NULL;
2581 l_trx_lines_tbl(l_tax_indx).VAT_TAX_ID := xfer_tax_rec.TAX_RATE_ID;
2582 --
2583
2584 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Done Preparing Trx Tax Lines tbl '||(l_num_cnt + l_tax_line_number));
2585 END LOOP;
2586 --Bug# 4488818: Sales Tax Billing
2587 */
2588 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2589
2590 ---------------------------------------
2591 -- Prepare trx distributions tbl
2592 ---------------------------------------
2593 l_trx_dist_tbl := l_init_trx_dist_tbl;
2594
2595 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Preparing Trx Dist tbl '||l_num_cnt);
2596 PREPARE_TRX_DIST_TBL(
2597 x_return_status
2598 ,l_num_cnt
2599 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2600 ,lxfer_rec
2601 -- ,r_xsiv_rec
2602 -- ,r_xlsv_rec
2603 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2604 ,l_trx_dist_tbl );
2605 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Done Preparing trx dist tbl '||l_num_cnt);
2606
2607 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2608 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
2609 p_msg_name => 'OKL_BPD_RTA_REC_ERR',
2610 p_token1 => 'TABLE',
2611 p_token1_value => 'AR Transaction Distributions Table '
2612 );
2613 RAISE OKL_API.G_EXCEPTION_ERROR;
2614 END IF;
2615
2616 l_trx_salescredits_tbl := l_init_trx_salescredits_tbl;
2617
2618 /* l_salesrep_id := NULL; */
2619 /* l_salesrep_number := NULL; */
2620 /* l_sales_type_credit := NULL; */
2621 /* */
2622 /* OPEN sales_type_credit_csr; */
2623 /* FETCH sales_type_credit_csr INTO l_sales_type_credit; */
2624 /* CLOSE sales_type_credit_csr; */
2625 /* */
2626 /* l_trx_salescredits_tbl(1).SALESCREDIT_PERCENT_SPLIT := 100; */
2627 /* l_trx_salescredits_tbl(1).SALES_CREDIT_TYPE_ID := l_sales_type_credit; */
2628 /* l_trx_salescredits_tbl(1).SALES_CREDIT_TYPE_NAME := 'Quota Sales Credit'; */
2629 /* l_trx_salescredits_tbl(1).SALESREP_ID := -3; */
2630 /* l_trx_salescredits_tbl(1).SALESREP_NUMBER := -3; */
2631
2632 l_customer_trx_id := NULL;
2633 l_msg_count := 0;
2634 l_msg_data := NULL;
2635
2636 -----------------------------
2637 -- Create AR Invoice
2638 -----------------------------
2639 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Invoking Invoice API for'
2640 ||' Contract Number '||xfer_rec.CONTRACT_NUMBER
2641 ||',Stream Type '||xfer_rec.STREAM_TYPE
2642 ||',Invoice Date '||xfer_rec.TRX_DATE
2643 ||',Currency Code '||xfer_rec.CURRENCY_CODE);
2644 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Before calling invoice Api ');
2645
2646 -- Establish Savepoint for rollback
2647 DBMS_TRANSACTION.SAVEPOINT('AR_INVOICE_API_PVT');
2648 -- Savepoint established
2649
2650 -- -----------------------------------------
2651 -- Check for revenue based cash recognition
2652 -- -----------------------------------------
2653 -- Start comment
2654 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2655 /*
2656 l_rev_rec_basis := NULL;
2657 OPEN get_accrual_csr ( r_xlsv_rec.LSM_ID );
2658 FETCH get_accrual_csr INTO l_rev_rec_basis;
2659 CLOSE get_accrual_csr;
2660 */
2661 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2662
2663 l_contingency_tbl := l_init_contingency_tbl;
2664 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2665 -- IF l_rev_rec_basis = 'CASH_RECEIPT' THEN
2666 IF xfer_rec.rev_rec_basis = 'CASH_RECEIPT' THEN
2667 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2668
2669 --strat: cklee 4/9/07
2670 -- Migrated the following logic from oklarintf_pvt
2671 --Added if clause by bkatraga for bug 5616268
2672 --Accounting_rule_id will not be populated in case of on-account credit memo
2673 IF ((xfer_rec.AMOUNT >= 0) OR (xfer_rec.rev_txn_id IS NOT NULL)) THEN
2674 l_trx_header_tbl(1).INVOICING_RULE_ID := -2;
2675 l_trx_lines_tbl(1).ACCOUNTING_RULE_ID := 1;
2676 END IF;
2677 --end bkatraga
2678
2679 -- l_trx_header_tbl(1).INVOICING_RULE_ID := -2;
2680 -- l_trx_lines_tbl(1).ACCOUNTING_RULE_ID := 1;
2681 -- Added
2682 l_trx_lines_tbl(1).RULE_START_DATE := l_trx_header_tbl(1).trx_date;
2683 -- Added
2684 l_trx_lines_tbl(1).OVERRIDE_AUTO_ACCOUNTING_FLAG := 'Y';
2685
2686 --end: cklee 4/9/07
2687
2688 OPEN seq_csr;
2689 FETCH seq_csr INTO l_contingency_tbl(1).trx_contingency_id ;
2690 CLOSE seq_csr;
2691
2692 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2693 --start: bug 6744584 .. racheruv: populate contingency_id from stream type table.
2694
2695 --l_contingency_tbl(1).CONTINGENCY_ID := l_contingency_tbl(1).trx_contingency_id;
2696
2697 l_contingency_tbl(1).CONTINGENCY_ID := xfer_rec.contingency_id;
2698
2699 --end: bug 6744584.. racheruv.
2700 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2701
2702 l_contingency_tbl(1).trx_line_id := l_num_cnt;
2703 --start: bug 6744584 .. racheruv: populate contingency_id from stream type table.
2704 -- contingency_code is not required now.
2705 --l_contingency_tbl(1).contingency_code := 'OKL_COLLECTIBILITY';
2706 --end : bug 6744584 .. racheruv: contingency_code is not required anymore.
2707 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2708 ELSE
2709 l_trx_header_tbl(1).INVOICING_RULE_ID := NULL;
2710 l_trx_lines_tbl(1).ACCOUNTING_RULE_ID := NULL;
2711 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2712 END IF;
2713 -- End comment
2714
2715 --rkuttiya adding debug messages for getting values of attributes passed to
2716 --
2717 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Batch Source Id '||l_batch_source_rec.batch_source_id);
2718 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Default Date '||
2719 l_batch_source_rec.default_date);
2720
2721 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Trx Header Id :
2722 '||l_trx_header_tbl(1).trx_header_id);
2723 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Trx_Number :
2724 '||l_trx_header_tbl(1).trx_number);
2725 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Trx Date :'||l_trx_header_tbl(1).trx_date);
2726 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Trx_Currency
2727 :'||l_trx_header_tbl(1).trx_currency);
2728 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Receipt Method Id : '||
2729 l_trx_header_tbl(1).receipt_method_id);
2730 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Payment Trxn Extn Id :
2731 '||l_trx_header_tbl(1).payment_trxn_extension_id);
2732 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Customer Bank Account Id
2733 :'||l_trx_header_tbl(1).customer_bank_account_id);
2734 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Bill to Customer Id:
2735 '||l_trx_header_tbl(1).bill_to_customer_id);
2736 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Bill to Address Id:
2737 '||l_trx_header_tbl(1).bill_to_address_id);
2738 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Ship to customer id:
2739 '||l_trx_header_tbl(1).ship_to_customer_id);
2740 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Ship to address id:
2741 '||l_trx_header_tbl(1).ship_to_address_id);
2742 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Org Id: '||l_trx_header_tbl(1).org_id);
2743 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Legal Entity Id:
2744 '||l_trx_header_tbl(1).legal_entity_id);
2745 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Billing Date
2746 :'||l_trx_header_tbl(1).billing_date);
2747 fnd_file.put_line(fnd_file.output,'Term Id: '||l_trx_header_tbl(1).term_id);
2748 fnd_file.put_line(fnd_file.output,'Comments" '||l_trx_header_tbl(1).comments);
2749
2750 fnd_file.put_line(fnd_file.output,'Lines Table');
2751
2752 FOR i in l_trx_lines_tbl.FIRST..l_trx_lines_tbl.LAST LOOP
2753 fnd_file.put_line(fnd_file.output,'Trx Header Id :'||
2754 l_trx_lines_tbl(i).trx_header_id);
2755 fnd_file.put_line(fnd_file.output,'Trx Line Id
2756 :'||l_trx_lines_tbl(i).trx_line_id);
2757 fnd_file.put_line(fnd_file.output,'Line
2758 Number:'||l_trx_lines_tbl(i).line_number);
2759 fnd_file.put_line(fnd_file.output,'Inventory Item
2760 Id:'||l_trx_lines_tbl(i).inventory_item_id);
2761 fnd_file.put_line(fnd_file.output,'Quantity Ordered
2762 :'||l_trx_lines_tbl(i).quantity_ordered);
2763 fnd_file.put_line(fnd_file.output,'unit standard
2764 price:'||l_trx_lines_tbl(i).unit_standard_price);
2765 fnd_file.put_line(fnd_file.output,'unit selling price
2766 '||l_trx_lines_tbl(i).unit_selling_price);
2767 fnd_file.put_line(fnd_file.output,'line type:'||l_trx_lines_tbl(i).line_type);
2768 fnd_file.put_line(fnd_file.output,'Interface Line
2769 Context:'||l_trx_lines_tbl(i).interface_line_context);
2770 fnd_file.put_line(fnd_file.output,'Interface Line Attribute6
2771 :'||l_trx_lines_tbl(i).interface_line_attribute6);
2772 fnd_file.put_line(fnd_file.output,'Interface Line Attribute7
2773 :'||l_trx_lines_tbl(i).interface_line_attribute7);
2774 fnd_file.put_line(fnd_file.output,'Interface Line
2775 Attribute9:'||l_trx_lines_tbl(i).interface_line_Attribute9);
2776 fnd_file.put_line(fnd_file.output,'Interface Line
2777 Attribute10:'||l_trx_lines_tbl(i).interfacE_line_attribute10);
2778 fnd_file.put_line(fnd_file.output,'Interface Line Attribute
2779 13:'||l_trx_lines_tbl(i).interfacE_line_Attribute13);
2780 fnd_file.put_line(fnd_file.output,'Interface Line Atribute14
2781 :'||l_trx_lines_tbl(i).interface_line_attribute14);
2782 fnd_file.put_line(fnd_file.output,'Amount :'||l_trx_lines_tbl(i).amount);
2783 fnd_file.put_line(fnd_file.output,'UOM Code :'||l_trx_lines_tbl(i).UOM_CODE);
2784 fnd_file.put_line(fnd_file.output,'Tax Exempt Flag
2785 :'||l_trx_lines_tbl(i).tax_exempt_flag);
2786
2787 End LOOP;
2788
2789 fnd_file.put_line(fnd_file.output,'Distributions Table');
2790
2791 FOR j in L_TRX_DIST_TBL.FIRST..L_TRX_DIST_TBL.LAST LOOP
2792 fnd_file.put_line(fnd_file.output,'Trx Dist Id :'||
2793 l_trx_dist_tbl(j).trx_dist_id);
2794 fnd_file.put_line(fnd_file.output,'Trx Header Id
2795 :'||l_trx_dist_tbl(j).trx_line_id);
2796 fnd_file.put_line(fnd_file.output,'Trx Line Id
2797 :'||l_trx_dist_tbl(j).trx_line_id);
2798 fnd_file.put_line(fnd_file.output,'Account class
2799 :'||l_trx_dist_tbl(j).account_class);
2800 fnd_file.put_line(fnd_file.output,'Amount
2801 :'||l_trx_dist_tbl(j).amount);
2802 fnd_file.put_line(fnd_file.output,'acctd_amount
2803 :'||l_trx_dist_tbl(j).acctd_amount);
2804 fnd_file.put_line(fnd_file.output,'Percent
2805 '||l_trx_dist_tbl(j).percent);
2806 fnd_file.put_line(fnd_file.output,'Code Combination Id
2807 :'||l_trx_dist_tbl(j).code_combination_id);
2808 End LOOP;
2809
2810 AR_INVOICE_API_PUB.create_single_invoice(
2811 p_api_version => 1.0,
2812 p_batch_source_rec => l_batch_source_rec,
2813 p_trx_header_tbl => l_trx_header_tbl,
2814 p_trx_lines_tbl => l_trx_lines_tbl,
2815 p_trx_dist_tbl => l_trx_dist_tbl,
2816 p_trx_salescredits_tbl => l_trx_salescredits_tbl,
2817 p_trx_contingencies_tbl => l_contingency_tbl,
2818 x_customer_trx_id => l_customer_trx_id,
2819 x_return_status => l_return_status,
2820 x_msg_count => l_msg_count,
2821 x_msg_data => l_msg_data);
2822
2823 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'After calling Invoice API'||
2824 l_return_status);
2825
2826 x_ar_inv_tbl(l_num_cnt).receivables_invoice_id := l_customer_trx_id;
2827
2828 IF (p_source = 'PRINCIPAL_PAYDOWN' AND l_customer_trx_id IS NULL) THEN
2829 -- cklee 4/4/07 note: set a proper error message!
2830 l_overall_err_sts := 'E';
2831 END IF;
2832
2833 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'After calling Invoice API '||l_num_cnt
2834 ||'.. Assigned l_customer_trx_id = '||l_customer_trx_id|| ' ret sts '||l_return_status);
2835
2836
2837 -- Post Call Processing Block
2838 FOR err_rec IN err_csr LOOP
2839 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error_message: '||err_rec.error_message);
2840 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Invalid_value: '||err_rec.Invalid_value);
2841 END LOOP;
2842
2843 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2844 x_msg_count := l_msg_count;
2845 x_msg_data := l_msg_data;
2846 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
2847 p_msg_name => 'OKL_BPD_RTA_TXN_ERR1',
2848 p_token1 => 'TXN',
2849 p_token1_value => 'Receivables Invoice'
2850 );
2851 RAISE AR_API_CALL_EXCP;
2852 ELSE
2853
2854 SELECT count(*) INTO l_cnt
2855 FROM ar_trx_errors_gt;
2856
2857 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Count is : '||l_cnt);
2858 IF l_cnt > 0 THEN
2859 l_err_cnt := 0;
2860 FOR ar_trx_rec IN ar_trx_errs_csr( 110 ) LOOP
2861 l_err_cnt := l_err_cnt + 1;
2862 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
2863 p_msg_name => 'OKL_BPD_RTA_GEN_ERR1',
2864 p_token1 => 'NUM',
2865 p_token1_value => to_char(l_err_cnt),
2866 p_token2 => 'MESSAGE',
2867 p_token2_value => ar_trx_rec.ERROR_MESSAGE
2868 );
2869 END LOOP;
2870 x_return_status := 'E';
2871 RAISE AR_API_CALL_EXCP;
2872 END IF;
2873 END IF;
2874
2875 l_ar_inv_num := NULL;
2876 OPEN ar_invs_csr ( l_customer_trx_id );
2877 FETCH ar_invs_csr INTO l_ar_inv_num;
2878 CLOSE ar_invs_csr;
2879
2880 -----------------------------------------------
2881 -- Keep appending created AR Invoice Numbers
2882 -----------------------------------------------
2883 l_ar_inv_disp_num := l_ar_inv_disp_num||' '||l_ar_inv_num;
2884
2885 --------------------------------------
2886 -- Post invoice creation updates for
2887 -- referential integrity
2888 --------------------------------------
2889 --start: | 02-APR-07 cklee R12 Billing enhancement project |
2890 /*
2891 l_tax_amount := 0;
2892
2893 OPEN tax_amount_csr ( l_customer_trx_id );
2894 FETCH tax_amount_csr INTO l_tax_amount;
2895 CLOSE tax_amount_csr;
2896
2897 l_cnr_id := NULL;
2898 l_lln_id := NULL;
2899 OPEN cnr_lln_csr( r_xlsv_rec.LSM_ID );
2900 FETCH cnr_lln_csr INTO l_cnr_id, l_lln_id;
2901 CLOSE cnr_lln_csr;
2902
2903 l_due_date := NULL;
2904 OPEN ar_due_date_csr ( l_customer_trx_id );
2905 FETCH ar_due_date_csr INTO l_due_date;
2906 CLOSE ar_due_date_csr;
2907
2908 UPDATE Okl_Cnsld_Ar_Strms_b
2909 SET RECEIVABLES_INVOICE_ID = l_customer_trx_id,
2910 tax_amount = NVL(l_tax_amount,0)
2911 WHERE ID = r_xlsv_rec.LSM_ID;
2912
2913 UPDATE Okl_Cnsld_Ar_Lines_B
2914 SET TAX_AMOUNT = NVL(TAX_AMOUNT,0)+NVL(l_tax_amount,0)
2915 WHERE ID = l_lln_id;
2916
2917 UPDATE Okl_Cnsld_Ar_Hdrs_B
2918 SET amount = NVL(amount,0) + NVL(l_tax_amount,0),
2919 due_date = l_due_date
2920 WHERE ID = l_cnr_id;
2921
2922 UPDATE Okl_Ext_Sell_Invs_B
2923 SET RECEIVABLES_INVOICE_ID = l_customer_trx_id,
2924 TRX_STATUS_CODE = 'PROCESSED'
2925 WHERE ID = r_xsiv_rec.id;
2926
2927 UPDATE Okl_Txl_Ar_Inv_Lns_B
2928 SET RECEIVABLES_INVOICE_ID = l_customer_trx_id
2929 WHERE id = xfer_rec.TIL_ID;
2930 */
2931 UPDATE Okl_Txd_Ar_Ln_Dtls_B
2932 SET RECEIVABLES_INVOICE_ID = l_customer_trx_id
2933 WHERE id = xfer_rec.TXN_ID;
2934 --end: | 02-APR-07 cklee R12 Billing enhancement project |
2935
2936 END LOOP; -- For each rec in XSI loop
2937
2938 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Assigned Invoice Numbers: '||l_ar_inv_disp_num);
2939
2940 -- -------------------------------------------------------
2941 -- Invoke Advance Receipts
2942 -- -------------------------------------------------------
2943 IF p_source = 'ADVANCE_RECEIPTS' THEN
2944 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Begin: Apply Advance Receipts');
2945 OKL_BPD_ADVANCED_CASH_APP_PUB.ADVANCED_CASH_APP
2946 ( p_api_version => p_api_version
2947 ,p_init_msg_list => p_init_msg_list
2948 ,x_return_status => x_return_status
2949 ,x_msg_count => x_msg_count
2950 ,x_msg_data => x_msg_data
2951 --gkhuntet FP Bug 5516814 start
2952 ,p_contract_num => l_contract_number
2953 --gkhuntet FP Bug 5516814 end
2954 -- ,p_contract_num => p_contract_number
2955 ,p_cross_currency_allowed => 'Y'
2956 );
2957
2958 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'End: Apply Advance Receipts');
2959 END IF;
2960
2961 IF p_source = 'PRINCIPAL_PAYDOWN' THEN
2962 x_return_status := l_overall_err_sts;
2963 END IF;
2964
2965 ------------------------------------------------------------
2966 -- End processing
2967 ------------------------------------------------------------
2968 Okl_Api.END_ACTIVITY (
2969 x_msg_count => x_msg_count,
2970 x_msg_data => x_msg_data);
2971
2972 END; -- END is added as part of Bug# 14119181
2973 END IF; --END IF (l_bankruptcy_flag = 'N')is added as part of Bug# 14119181
2974
2975 --gkhuntet FP Bug 5516814 start
2976 End;
2977 End loop;
2978 --gkhuntet FP Bug 5516814 end
2979
2980
2981 EXCEPTION
2982 ------------------------------------------------------------
2983 -- Exception handling
2984 ------------------------------------------------------------
2985
2986 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2987 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
2988 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2989 p_api_name => l_api_name,
2990 p_pkg_name => G_PKG_NAME,
2991 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
2992 x_msg_count => x_msg_count,
2993 x_msg_data => x_msg_data,
2994 p_api_type => '_PVT');
2995
2996 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2997 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
2998 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2999 p_api_name => l_api_name,
3000 p_pkg_name => G_PKG_NAME,
3001 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3002 x_msg_count => x_msg_count,
3003 x_msg_data => x_msg_data,
3004 p_api_type => '_PVT');
3005 WHEN AR_API_CALL_EXCP THEN
3006 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (AR_API_CALL_EXCP) => '||SQLERRM);
3007 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3008 p_api_name => 'AR_INVOICE_API',
3009 p_pkg_name => G_PKG_NAME,
3010 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3011 x_msg_count => x_msg_count,
3012 x_msg_data => x_msg_data,
3013 p_api_type => '_PVT');
3014
3015 WHEN OTHERS THEN
3016 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
3017 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3018 p_api_name => l_api_name,
3019 p_pkg_name => G_PKG_NAME,
3020 p_exc_name => 'OTHERS',
3021 x_msg_count => x_msg_count,
3022 x_msg_data => x_msg_data,
3023 p_api_type => '_PVT');
3024
3025
3026
3027
3028 END ADVANCED_BILLING;
3029
3030
3031 END OKL_BPD_ADVANCED_BILLING_PVT;