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