[Home] [Help]
PACKAGE BODY: APPS.OKS_ARFEEDER_PUB
Source
1 Package Body OKS_ARFEEDER_PUB AS
2 /* $Header: OKSPARFB.pls 120.44.12020000.2 2013/02/05 14:34:24 ansraj ship $ */
3
4
5 Procedure Populate_TR_reference_fields(p_instance_number IN NUMBER,
6 p_contract_number IN VARCHAR2,
7 p_contract_modifier IN VARCHAR2,
8 x_return_status OUT NOCOPY VARCHAR2);
9
10 --mchoudha Fix for bug#4174921
11 --added parameter p_hdr_id
12 Procedure Set_Reference_PB_Value(
13 p_bsl_id IN NUMBER,
14 p_contract_no IN VARCHAR2,
15 p_contract_mod IN VARCHAR2,
16 p_bill_inst_no IN NUMBER,
17 p_amount IN NUMBER,
18 p_int_att10 IN VARCHAR2,
19 p_bcl_cle_id IN NUMBER,
20 p_currency_code IN VARCHAR2,
21 p_hdr_id IN NUMBER,
22 x_msg_cnt OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 x_return_status OUT NOCOPY VARCHAR2
25 );
26 ------------------------------------------
27
28
29 Function get_authoring_org_id (p_chrid NUMBER) Return NUMBER Is
30
31 Cursor l_hdrs_csr Is
32 SELECT Authoring_Org_Id FROM OKC_K_HEADERS_B
33 WHERE id = p_chrid;
34
35 l_orgid NUMBER;
36 BEGIN
37
38 OPEN l_hdrs_csr;
39 FETCH l_hdrs_csr Into l_orgid;
40 CLOSE l_hdrs_csr;
41
42 Return (l_orgid);
43
44 END get_authoring_org_id;
45
46
47 Function get_month Return Varchar2 Is
48 Cursor l_month_csr Is
49 SELECT uom_code FROM okc_time_code_units_b
50 WHERE tce_code = 'MONTH'
51 AND quantity = 1;
52
53 l_mnth varchar2(240);
54 BEGIN
55 OPEN l_month_csr;
56 FETCH l_month_csr Into l_mnth;
57 CLOSE l_month_csr;
58
59 Return (l_mnth);
60
61 END get_month;
62
63
64 Function get_organization_id (p_chrid NUMBER) Return NUMBER Is
65
66 Cursor l_hdrs_csr Is
67 SELECT Org_Id FROM OKC_K_HEADERS_B
68 WHERE id = p_chrid;
69
70 l_orgid NUMBER;
71 l_organization_id NUMBER;
72 BEGIN
73
74 OPEN l_hdrs_csr;
75 FETCH l_hdrs_csr Into l_orgid;
76 CLOSE l_hdrs_csr;
77
78 okc_context.set_okc_org_context(l_orgid, Null);
79 l_organization_id := okc_context.get_okc_organization_id;
80
81 Return (l_organization_id);
82
83 END get_organization_id;
84
85 FUNCTION get_acct_calender (p_set_of_books_id NUMBER) RETURN VARCHAR IS
86 CURSOR l_calender (p_gl_set_of_books_id IN NUMBER) IS
87 SELECT period_set_name FROM gl_sets_of_books
88 WHERE set_of_books_id = p_gl_set_of_books_id;
89
90 l_acct_cal VARCHAR2(15);
91 BEGIN
92 OPEN l_calender(p_set_of_books_id);
93 FETCH l_calender INTO l_acct_cal;
94 CLOSE l_calender;
95
96 RETURN(l_acct_cal);
97
98 END get_acct_calender;
99
100 Function get_set_of_books_id (p_chrid NUMBER) Return NUMBER Is
101
102 Cursor l_hdrs_csr Is Select Org_Id From OKC_K_HEADERS_B
103 Where id = p_chrid;
104
105 Cursor l_org_csr (p_org_id NUMBER) Is
106 SELECT OI2.ORG_INFORMATION3 set_of_books_id
107 FROM
108 HR_ORGANIZATION_INFORMATION OI2,
109 HR_ORGANIZATION_INFORMATION OI1,
110 HR_ALL_ORGANIZATION_UNITS OU
111 WHERE oi1.organization_id = ou.organization_id
112 AND oi2.organization_id = ou.organization_id
113 AND oi1.org_information_context = 'CLASS'
114 AND oi1.org_information1 = 'OPERATING_UNIT'
115 AND oi2.org_information_context = 'Operating Unit Information'
116 AND ou.organization_id = p_org_id;
117
118 /* Above select avoids OKX view usage
119 SELECT set_of_books_id From OKX_ORGANIZATION_DEFS_V
120 WHERE organization_type = 'OPERATING_UNIT'
121 AND information_type = 'Operating Unit Information'
122 AND organization_id = p_org_id;
123 */
124
125 l_orgid NUMBER;
126 l_set_of_books_id NUMBER;
127
128 BEGIN
129
130 OPEN l_hdrs_csr;
131 FETCH l_hdrs_csr Into l_orgid;
132 CLOSE l_hdrs_csr;
133
134 OPEN l_org_csr (l_orgid);
135 FETCH l_org_csr Into l_set_of_books_id ;
136 CLOSE l_org_csr;
137
138 Return (l_set_of_books_id);
139
140 END get_set_of_books_id;
141
142 Procedure Set_line_attribute (p_cle_id IN NUMBER,
143 p_date_billed_From IN DATE,
144 p_block23text IN VARCHAR2,
145 p_invoice_text IN VARCHAR2,
146 p_item_description IN VARCHAR2,
147 p_bill_instance_number IN NUMBER,
148 p_amount IN NUMBER,
149 p_inv_print_flag IN VARCHAR2,
150 p_attribute1 IN VARCHAR2,
151 p_attribute2 IN VARCHAR2,
152 p_attribute3 IN VARCHAR2,
153 p_attribute4 IN VARCHAR2,
154 p_attribute5 IN VARCHAR2,
155 p_attribute6 IN VARCHAR2,
156 p_attribute7 IN VARCHAR2,
157 p_attribute8 IN VARCHAR2,
158 p_attribute9 IN VARCHAR2,
159 p_attribute10 IN VARCHAR2,
160 p_attribute11 IN VARCHAR2,
161 p_attribute12 IN VARCHAR2,
162 p_attribute13 IN VARCHAR2,
163 p_attribute14 IN VARCHAR2,
164 p_attribute15 IN VARCHAR2,
165 p_attribute_category IN VARCHAR2)
166 IS
167 Cursor cur_line_sll_count (p_cle_id in NUMBER,p_date in Date) IS
168 SELECT count(lvl.id) tot_no_of_lvl,sub.nos no_of_lvl
169 from oks_level_elements lvl,
170 (select count(*) nos
171 from oks_level_elements lev
172 where lev.cle_id = p_cle_id
173 and trunc(lev.date_start) <= trunc(p_date)
174 ) sub
175 WHERE lvl.cle_id = p_cle_id
176 GROUP BY sub.nos;
177
178 sll_rec CUR_LINE_SLL_COUNT%ROWTYPE;
179 BEGIN
180
181
182 OPEN cur_line_sll_count(p_cle_id,
183 p_date_billed_from);
184 FETCH cur_line_sll_count into sll_rec;
185 CLOSE cur_line_sll_count;
186
187 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE10 := to_char(sll_rec.no_of_lvl)||' of '||to_char(sll_rec.tot_no_of_lvl);
188
189 G_RAIL_REC.PRINTING_OPTION := 'PRI';
190
191 IF ( Nvl(p_inv_print_flag,'Y') = 'N') THEN ---added nvl as part of bug fix10260151 skuchima
192 G_RAIL_REC.PRINTING_OPTION := 'NOT' ;
193 END IF;
194
195 /* Modified by sjanakir for Bug#7234818 */
196 G_RAIL_REC.DESCRIPTION := substrb(NVL(p_invoice_text, p_item_description)||':',1,240);
197 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE3 := TO_CHAR(p_bill_instance_number);
198 G_RAIL_REC.AMOUNT := p_amount;
199 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE6 := to_char(p_amount);
200
201 G_RAIL_REC.ATTRIBUTE1 := substrb(p_attribute1,0,150);
202 G_RAIL_REC.ATTRIBUTE2 := substrb(p_attribute2,0,150);
203 G_RAIL_REC.ATTRIBUTE3 := substrb(p_attribute3,0,150);
204 G_RAIL_REC.ATTRIBUTE4 := substrb(p_attribute4,0,150);
205 G_RAIL_REC.ATTRIBUTE5 := substrb(p_attribute5,0,150);
206 G_RAIL_REC.ATTRIBUTE6 := substrb(p_attribute6,0,150);
207 G_RAIL_REC.ATTRIBUTE7 := substrb(p_attribute7,0,150);
208 G_RAIL_REC.ATTRIBUTE8 := substrb(p_attribute8,0,150);
209 G_RAIL_REC.ATTRIBUTE9 := substrb(p_attribute9,0,150);
210 G_RAIL_REC.ATTRIBUTE10 := substrb(p_attribute10,0,150);
211 G_RAIL_REC.ATTRIBUTE11 := substrb(p_attribute11,0,150);
212 G_RAIL_REC.ATTRIBUTE12 := substrb(p_attribute12,0,150);
213 G_RAIL_REC.ATTRIBUTE13 := substrb(p_attribute13,0,150);
214 G_RAIL_REC.ATTRIBUTE14 := substrb(p_attribute14,0,150);
215 G_RAIL_REC.ATTRIBUTE15 := substrb(p_attribute15,0,150);
216 G_RAIL_REC.ATTRIBUTE_CATEGORY := substr(p_attribute_category,0,30);
217
218 END Set_line_attribute;
219
220
221 Procedure Set_ref_line_id(
222 p_bill_action IN VARCHAR2,
223 p_lse_id IN NUMBER,
224 p_cle_id IN NUMBER,
225 p_id IN NUMBER,
226 p_date_billed_From IN DATE,
227 p_date_billed_to IN DATE,
228 p_hdr_comm_id IN NUMBER,
229 p_line_comm_id IN NUMBER,
230 p_contract_number IN VARCHAR2,
231 p_con_modifier IN VARCHAR2,
232 p_average IN NUMBER,
233 p_top_line IN VARCHAR2,
234 p_line_payment_mth IN VARCHAR2,
235 p_return_status IN OUT NOCOPY VARCHAR2
236 -- x_cust_trx_id OUT NOCOPY NUMBER,
237 -- x_cust_trx_line_id OUT NOCOPY NUMBER
238 )
239 IS
240 Cursor l_get_cov_lvl(p_cle_id IN NUMBER) IS SELECT id from okc_k_lines_b WHERE cle_id = p_cle_id AND lse_id in (18,25); Cursor Cur_billinstance_hdr (p_date_billed_from IN DATE, p_date_billed_to IN DATE,
241 p_id IN NUMBER,
242 p_contract_number IN VARCHAR2,
243 p_con_modifier IN VARCHAR2)IS
244 SELECT c.Customer_trx_line_id,
245 d.trx_date,
246 d.exchange_rate_type,d.exchange_date, d.exchange_rate
247 FROM OKS_BILL_CONT_LINES a
248 , oks_bill_txn_lines b
249 , ra_customer_trx_all d --Okx_customer_trx_v d
250 , ra_customer_trx_lines_all c --Okx_cust_trx_lines_v c
251 WHERE a.date_billed_to = p_date_billed_to -- Bcl_rec.date_billed_to
252 AND a.cle_id = p_id -- Bcl_rec.cle_id
253 AND a.id = b.bcl_id
254 AND a.bill_action = 'RI'
255 AND c.sales_order = p_contract_number||
256 decode(p_con_modifier,null,'','-'||p_con_modifier)
257 AND c.interface_line_attribute1 = p_contract_number
258 AND nvl(c.interface_line_attribute2,'-') = nvl(p_con_modifier ,'-')
259 AND c.Interface_line_attribute3 = b.bill_instance_number
260 AND c.Interface_line_context = 'OKS CONTRACTS'
261 AND c.customer_trx_id = d.customer_trx_id
262 ORDER BY c.extended_amount ;
263
264 Cursor Cur_billinstance (
265 p_contract_number IN VARCHAR2,
266 p_contract_modifier IN VARCHAR2,
267 p_bill_instance_number IN NUMBER)IS
268 SELECT c.Customer_trx_line_id,
269 --c.customer_trx_id,
270 d.trx_date,
271 d.exchange_rate_type,d.exchange_date, d.exchange_rate
272 FROM ra_customer_trx_all d, --Okx_customer_trx_v d
273 ra_customer_trx_lines_all c --Okx_cust_trx_lines_v c
274 WHERE c.interface_line_attribute1 = p_contract_number
275 AND nvl(c.interface_line_attribute2,'-') = nvl(p_contract_modifier ,'-')
276 AND c.Interface_line_attribute3 = to_char(p_bill_instance_number)
277 AND c.sales_order = p_contract_number||
278 decode(p_contract_modifier,null,'','-'||p_contract_modifier)
279 AND c.Interface_line_context = 'OKS CONTRACTS'
280 AND c.customer_trx_id = d.customer_trx_id
281 ORDER BY c.extended_amount ;
282
283 Cursor Cur_billinstance_dtl (p_date_from IN DATE,
284 p_date_to IN DATE,
285 p_id IN NUMBER,
286 p_contract_number IN VARCHAR2,
287 p_contract_modifier IN VARCHAR2)IS
288 SELECT c.Customer_trx_line_id,
289 --c.customer_trx_id,
290 d.trx_date,
291 d.exchange_rate_type,d.exchange_date, d.exchange_rate
292 FROM OKS_BILL_SUB_LINES a
293 , oks_bill_cont_lines e
294 , oks_bill_txn_lines b
295 , ra_customer_trx_all d
296 , ra_customer_trx_lines_all c
297 WHERE a.DATE_Billed_to = p_date_to -- Bsl_rec.date_billed_to
298 AND a.cle_id = p_id -- Bsl_rec.cle_id
299 AND a.id = b.bsl_id
300 AND a.bcl_id = e.id
301 AND e.bill_action = 'RI'
302 AND c.Interface_line_Attribute1 = p_contract_number
303 AND nvl(c.interface_line_attribute2,'-') = nvl(p_contract_modifier ,'-')
304 AND c.Interface_line_attribute3 = b.bill_instance_number
305 AND c.sales_order = p_contract_number||
306 decode(p_contract_modifier,null,'','-'||p_contract_modifier)
307 AND c.Interface_line_context = 'OKS CONTRACTS'
308 AND c.customer_trx_id = d.customer_trx_id
309 ORDER BY c.extended_amount ;
310
311
312 Cursor bill_inst_number_cur(p_bcl_id in NUMBER) is
313 SELECT average from oks_bill_sub_lines
314 WHERE bcl_id = p_bcl_id;
315
316 Cursor l_get_order_line_id(p_cle_id IN NUMBER) IS
317 SELECT object1_id1
318 FROM okc_k_rel_objs
319 WHERE cle_id = p_cle_id;
320
321 /***
322 Bug# 4435961 - serviceable item check to eliminate freight lines
323 ***/
324 Cursor l_get_ref_id (p_line_id IN NUMBER) IS
325 SELECT txl.customer_trx_line_id ,txh.trx_date trx_date,
326 txh.exchange_rate_type,txh.exchange_date, txh.exchange_rate
327 FROM ra_customer_trx_all txh ,
328 ra_customer_trx_lines_all txl ,
329 mtl_system_items mtl
330 WHERE txl.interface_line_attribute6 = to_char(p_line_id)
331 AND txl.interface_line_context = 'ORDER ENTRY'
332 AND txh.customer_trx_id = txl.customer_trx_id
333 AND txl.inventory_item_id = mtl.inventory_item_id
334 AND mtl.service_item_flag = 'Y';
335
336 Cursor comm_id_cur (p_comm_id IN NUMBER)IS
337 SELECT rl.Customer_trx_line_id
338 FROM ra_customer_trx_lines_all rl
339 WHERE rl.customer_trx_id = p_comm_id;
340
341
342
343 l_comm_id NUMBER;
344 l_sub_line_id NUMBER;
345 l_order_line_id NUMBER;
346 l_bill_instance_number NUMBER;
347
348 l_CONVERSION_TYPE VARCHAR2(30);
349 l_CONVERSION_DATE DATE;
350 l_CONVERSION_RATE NUMBER;
351 l_return_status VARCHAR2(10) := 'S';
352
353 BEGIN
354
355 p_return_status := 'S';
356
357 -----IF (p_bill_action in ('AV','TR','STR')) THEN
358 IF (p_top_line = 'Y') THEN
359 IF (p_bill_action in ('TR','STR')) THEN
360 IF (p_lse_id in (14,19)) THEN
361 OPEN l_get_cov_lvl(p_cle_id);
362 FETCH l_get_cov_lvl into l_sub_line_id;
363 CLOSE l_get_cov_lvl;
364
365 OPEN l_get_order_line_id(l_sub_line_id);
366 FETCH l_get_order_line_id INTO l_order_line_id;
367 IF (l_get_order_line_id%NOTFOUND) THEN
368 CLOSE l_get_order_line_id;
369 OPEN l_get_order_line_id(p_cle_id);
370 FETCH l_get_order_line_id into l_order_line_id;
371 IF (l_get_order_line_id%NOTFOUND) THEN
372 CLOSE l_get_order_line_id;
373 OPEN bill_inst_number_cur(p_id);
374 FETCH bill_inst_number_cur into l_bill_instance_number;
375 CLOSE bill_inst_number_cur;
376
377 /*Average field is used to stored
378 bill_instance_number of parent INV record
379 */
380
381 IF (nvl(l_bill_instance_number,0) <> 0) THEN
382 OPEN Cur_billinstance(p_contract_number,
383 p_con_modifier,
384 l_bill_instance_number);
385
386 FETCH Cur_billinstance INTO G_RAIL_REC.Reference_line_id,
387 --x_cust_trx_id,
388 G_RAIL_REC.TRX_DATE,
389 l_CONVERSION_TYPE,
390 l_CONVERSION_DATE,
391 l_CONVERSION_RATE;
392
393 IF Cur_billinstance%FOUND THEN
394 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
395 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
396 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
397 END IF;
398
399 CLOSE Cur_billinstance;
400
401 IF G_RAIL_REC.Reference_line_id IS NULL THEN ---autoinvoice not run
402
403 Populate_TR_reference_fields(p_instance_number => l_bill_instance_number,
404 p_contract_number => p_contract_number,
405 p_contract_modifier => nvl(p_con_modifier,'-'),
406 x_return_status => l_return_status);
407
408 IF ( l_return_status <> 'S') THEN
409 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id => Failed in populating refrence fields');
410 END IF;
411
412 END IF;
413
414 /*Following if stat is for backward compatibility*/
415 ELSE
416 Open Cur_billinstance_hdr(
417 p_date_billed_from,
418 p_date_billed_to,
419 p_cle_id,
420 p_contract_number,
421 p_con_modifier);
422
423 Fetch Cur_billinstance_hdr INTO G_RAIL_REC.Reference_line_id,
424 --x_cust_trx_id,
425 G_RAIL_REC.TRX_DATE,
426 l_CONVERSION_TYPE,
427 l_CONVERSION_DATE,
428 l_CONVERSION_RATE;
429
430 IF Cur_billinstance_hdr%FOUND THEN
431 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
432 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
433 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
434 END IF;
435
436 Close Cur_billinstance_hdr;
437
438 IF G_RAIL_REC.Reference_line_id IS NULL THEN ---autoinvoice not run
439
440 Populate_TR_reference_fields(p_instance_number => NULL,
441 p_contract_number => p_contract_number,
442 p_contract_modifier => nvl(p_con_modifier,'-'),
443 x_return_status => l_return_status);
444
445 IF ( l_return_status <> 'S') THEN
446 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id => Failed in populating refrence fields');
447 END IF;
448 END IF; ---Reference_line_id chk
449
450 END IF;
451 IF (trunc(G_RAIL_REC.TRX_DATE) < trunc(sysdate)) THEN
452 G_RAIL_REC.TRX_DATE := sysdate;
453 END IF;
454 ELSE -- l_get_order_line_id with line
455 CLOSE l_get_order_line_id;
456 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'LIFO';
457
458 OPEN l_get_ref_id(l_order_line_id);
459 FETCH l_get_ref_id into G_RAIL_REC.Reference_line_id,
460 G_RAIL_REC.TRX_DATE,
461 l_CONVERSION_TYPE,
462 l_CONVERSION_DATE,
463 l_CONVERSION_RATE;
464
465 IF l_get_ref_id%FOUND THEN
466 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
467 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
468 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
469 END IF;
470
471 CLOSE l_get_ref_id;
472
473 IF (trunc(G_RAIL_REC.TRX_DATE) < trunc(sysdate)) Then
474 G_RAIL_REC.TRX_DATE := sysdate;
475 END IF;
476 END IF; --l_get_order_line_id with line
477 ELSE -- l_get_order_line_id with subline
478 CLOSE l_get_order_line_id;
479 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'LIFO';
480
481 OPEN l_get_ref_id(l_order_line_id);
482 FETCH l_get_ref_id into G_RAIL_REC.Reference_line_id,
483 G_RAIL_REC.TRX_DATE,
484 l_CONVERSION_TYPE,
485 l_CONVERSION_DATE,
486 l_CONVERSION_RATE;
487
488 IF l_get_ref_id%FOUND THEN
489 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
490 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
491 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
492 END IF;
493 CLOSE l_get_ref_id;
494
495 IF (trunc(G_RAIL_REC.TRX_DATE) < trunc(sysdate)) THEN
496 G_RAIL_REC.TRX_DATE := sysdate;
497 END IF;
498 END IF; --l_get_order_line_id with subline
499
500 ELSE --p_lse_id in 14,19
501 OPEN bill_inst_number_cur(p_id);
502 FETCH bill_inst_number_cur into l_bill_instance_number;
503 CLOSE bill_inst_number_cur;
504
505 /*Average field is used to stored
506 bill_instance_number of parent INV record
507 */
508 IF (nvl(l_bill_instance_number,0) <> 0) THEN
509 OPEN Cur_billinstance (p_contract_number,
510 p_con_modifier,
511 l_bill_instance_number);
512
513 FETCH Cur_billinstance INTO G_RAIL_REC.Reference_line_id,
514 --x_cust_trx_id,
515 G_RAIL_REC.TRX_DATE,
516 l_CONVERSION_TYPE,
517 l_CONVERSION_DATE,
518 l_CONVERSION_RATE;
519
520 IF Cur_billinstance%FOUND THEN
521 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
522 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
523 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
524 END IF;
525 CLOSE Cur_billinstance;
526
527 IF G_RAIL_REC.Reference_line_id IS NULL THEN ---autoinvoice not run
528
529 Populate_TR_reference_fields(p_instance_number => l_bill_instance_number,
530 p_contract_number => p_contract_number,
531 p_contract_modifier => nvl(p_con_modifier,'-'),
532 x_return_status => l_return_status);
533
534 IF ( l_return_status <> 'S') THEN
535 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id => Failed in populating refrence fields');
536 END IF;
537 END IF;
538
539
540
541 /*Following if stat is for backward compatibility*/
542 ELSE
543 OPEN Cur_billinstance_hdr(
544 p_date_billed_from,
545 p_date_billed_to,
546 p_cle_id,
547 p_contract_number,
548 p_con_modifier);
549 FETCH Cur_billinstance_hdr INTO G_RAIL_REC.Reference_line_id,
550 --x_cust_trx_id,
551 G_RAIL_REC.TRX_DATE,
552 l_CONVERSION_TYPE,
553 l_CONVERSION_DATE,
554 l_CONVERSION_RATE;
555
556 IF Cur_billinstance_hdr%FOUND THEN
557 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
558 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
559 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
560 END IF;
561 CLOSE Cur_billinstance_hdr;
562
563 IF G_RAIL_REC.Reference_line_id IS NULL THEN ---autoinvoice not run
564
565 Populate_TR_reference_fields(p_instance_number => NULL,
566 p_contract_number => p_contract_number,
567 p_contract_modifier => nvl(p_con_modifier,'-'),
568 x_return_status => l_return_status);
569
570 IF ( l_return_status <> 'S') THEN
571 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id => Failed in populating refrence fields');
572 END IF;
573 END IF; ---Reference_line_id chk
574 END IF;
575
576 IF (trunc(G_RAIL_REC.TRX_DATE) < trunc(sysdate)) Then
577 G_RAIL_REC.TRX_DATE := sysdate;
578 END IF;
579
580 END IF; -- p_lse_id in 14,19
581
582
583 END IF; --p_bill_action = 'TR'
584 ELSE --p_top_line = 'N'
585 -----IF (p_bill_action in ('AV','TR','STR')) THEN
586 IF (p_bill_action in ('TR','STR')) THEN
587
588 OPEN l_get_order_line_id(p_cle_id);
589 FETCH l_get_order_line_id into l_order_line_id;
590 IF (l_get_order_line_id%NOTFOUND) THEN
591 CLOSE l_get_order_line_id;
592
593 /*Average field is used to stored
594 bill_instance_number of parent INV record
595 */
596 IF (nvl( p_average,0) <> 0) THEN
597 OPEN Cur_billinstance (
598 p_contract_number,
599 p_con_modifier,
600 p_average);
601
602 FETCH Cur_billinstance INTO G_RAIL_REC.Reference_line_id,
603 --x_cust_trx_id,
604 G_RAIL_REC.TRX_DATE,
605 l_CONVERSION_TYPE,
606 l_CONVERSION_DATE,
607 l_CONVERSION_RATE;
608
609 IF Cur_billinstance%FOUND THEN
610 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
611 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
612 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
613 END IF;
614
615 ClOSE Cur_billinstance;
616
617 IF G_RAIL_REC.Reference_line_id IS NULL THEN ---autoinvoice not run
618
619 Populate_TR_reference_fields(
620 p_instance_number => p_average,
621 p_contract_number => p_contract_number,
622 p_contract_modifier => nvl(p_con_modifier,'-'),
623 x_return_status => l_return_status);
624
625 IF ( l_return_status <> 'S') THEN
626 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id Failed in populating refrence fields');
627 END IF;
628 END IF; ---Reference_line_id chk
629
630 /*Following if stat is for backward compatibility*/
631 ELSE
632 OPEN Cur_billinstance_dtl(
633 p_date_billed_from,
634 p_date_billed_to,
635 p_cle_id,
636 p_contract_number,
637 p_con_modifier);
638 FETCH Cur_billinstance_dtl INTO G_RAIL_REC.Reference_line_id,
639 --x_cust_trx_id,
640 G_RAIL_REC.TRX_DATE,
641 l_CONVERSION_TYPE,
642 l_CONVERSION_DATE,
643 l_CONVERSION_RATE;
644
645 IF Cur_billinstance_dtl%FOUND THEN
646 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
647 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
648 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
649 END IF;
650 CLOSE Cur_billinstance_dtl;
651
652 IF G_RAIL_REC.Reference_line_id IS NULL THEN ---autoinvoice not run
653
654 Populate_TR_reference_fields(p_instance_number => NULL,
655 p_contract_number => p_contract_number,
656 p_contract_modifier => nvl(p_con_modifier,'-'),
657 x_return_status => l_return_status);
658
659 IF ( l_return_status <> 'S') THEN
660 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id Failed in populating refrence fields');
661 END IF;
662 END IF; ---Reference_line_id chk
663
664 END IF;
665
666 IF (trunc(G_RAIL_REC.TRX_DATE) < trunc(sysdate)) Then
667 G_RAIL_REC.TRX_DATE := sysdate;
668 END IF;
669
670 ELSE
671 CLOSE l_get_order_line_id;
672 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'LIFO';
673 OPEN l_get_ref_id(l_order_line_id);
674 FETCH l_get_ref_id into G_RAIL_REC.Reference_line_id,
675 G_RAIL_REC.TRX_DATE,
676 l_CONVERSION_TYPE,
677 l_CONVERSION_DATE,
678 l_CONVERSION_RATE;
679
680 IF l_get_ref_id%FOUND THEN
681 G_RAIL_REC.CONVERSION_TYPE := l_CONVERSION_TYPE;
682 G_RAIL_REC.CONVERSION_DATE := l_CONVERSION_DATE;
683 G_RAIL_REC.CONVERSION_RATE := l_CONVERSION_RATE;
684
685 ELSE --- skip and process after Auto Invoice run
686 /****
687 Refer Bug# 4304841
688 The records are fetched from ra Interface Lines
689 if auto Invoice has not run for OM originated contracts
690 ****/
691
692 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto Invoice not run for Order line '||l_order_line_id);
693 p_return_status := 'E';
694 END IF;
695
696 CLOSE l_get_ref_id;
697
698
699 IF (trunc(G_RAIL_REC.TRX_DATE) < trunc(sysdate)) Then
700 G_RAIL_REC.TRX_DATE := sysdate;
701 END IF;
702
703 END IF; --l_get_order_line_id
704
705 /********
706 ELSE
707
708
709 G_RAIL_REC.reference_line_id := NULL;
710 IF ( nvl(p_line_payment_mth,'XX') <> 'CCR') THEN
711 l_comm_id := NULL;
712
713 IF (p_line_comm_id IS NOT NULL) THEN
714
715 l_comm_id := p_line_comm_id ;
716
717 If G_LOG_YES_NO = 'YES' then
718 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id IPM:line_level '||l_comm_id);
719 End If;
720
721 ELSE
722
723 l_comm_id := p_hdr_comm_id ;
724 If G_LOG_YES_NO = 'YES' then
725 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id IPM:header_level '||l_comm_id);
726 End If;
727
728 END IF;
729
730 IF (l_comm_id IS NOT NULL) THEN
731
732 OPEN comm_id_cur(l_comm_id);
733 FETCH comm_id_cur into G_RAIL_REC.reference_line_id;
734 CLOSE comm_id_cur;
735 If G_LOG_YES_NO = 'YES' then
736 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id IPM:reference_line_id '||G_RAIL_REC.reference_line_id);
737 End If;
738 G_RAIL_REC.CUSTOMER_BANK_ACCOUNT_ID := NULL;
739 G_RAIL_REC.RECEIPT_METHOD_NAME := NULL;
740 G_RAIL_REC.RECEIPT_METHOD_ID := NULL;
741
742
743 ELSE
744
745 G_RAIL_REC.reference_line_id := NULL;
746
747 END IF;
748 END IF;
749
750 *********/
751
752 END IF;
753
754 END IF;
755 --x_cust_trx_line_id := G_RAIL_REC.reference_line_id;
756
757 END Set_ref_line_id;
758
759 Procedure Set_cust_trx_type(p_set_of_books_id IN NUMBER,
760 p_bill_action IN VARCHAR2,
761 p_hdr_sbg_object1_id1 IN NUMBER,
762 p_authoring_org_id IN NUMBER)
763 IS
764
765 --19-NOV-2003 Bug#3266871 mchoudha
766 /*Changed this procedure Set_cust_trx_type
767 to include two cursors Cur_custtrx_type_id1
768 and Cur_custtrx_type_id2 in place of
769 Cur_custtrx_type_id for performance
770 */
771
772 Cursor Cur_custtrx_type_id1 (p_id IN NUMBER,
773 p_type IN VARCHAR2,
774 p_object1_id1 IN NUMBER,
775 p_org_id IN NUMBER) IS
776 Select decode(p_type,'INV',Cust_trx_type_id,'CM',credit_memo_type_id),
777 post_to_gl
778 From RA_CUST_TRX_TYPES_ALL
779 Where SET_OF_BOOKS_ID = p_id
780 And org_id = p_org_id
781 And Cust_trx_type_id = p_object1_id1;
782
783
784 Cursor Cur_custtrx_type_id2(p_id IN NUMBER,
785 p_type IN VARCHAR2,
786 p_org_id IN NUMBER) IS
787
788 Select /*+ PARALLEL(a) */
789
790 decode(p_type,'INV',Cust_trx_type_id,'CM',credit_memo_type_id),
791 post_to_gl
792 From RA_CUST_TRX_TYPES_ALL a
793 Where a.SET_OF_BOOKS_ID = p_id
794 And a.org_id = p_org_id
795 And a.type = 'INV'
796 And a.name = 'Invoice-OKS';
797
798 l_post_to_gl VARCHAR2(1);
799
800 BEGIN
801 -----IF (p_bill_action in ('AV','TR','STR')) THEN
802 IF (p_bill_action in ('TR','STR')) THEN
803 If (p_hdr_sbg_object1_id1 IS NOT NULL) Then
804 Open Cur_custtrx_type_id1(p_set_of_books_id,
805 'CM',
806 p_hdr_sbg_object1_id1,
807 p_authoring_org_id);
808 Fetch Cur_custtrx_type_id1 INTO G_RAIL_REC.CUST_TRX_TYPE_ID,l_post_to_gl;
809 Close Cur_custtrx_type_id1;
810 Else
811 Open Cur_custtrx_type_id2(p_set_of_books_id,
812 'CM',
813 p_authoring_org_id);
814 Fetch Cur_custtrx_type_id2 INTO G_RAIL_REC.CUST_TRX_TYPE_ID,l_post_to_gl;
815 Close Cur_custtrx_type_id2;
816 End If;
817
818 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'UNIT';
819 ELSE
820 If (p_hdr_sbg_object1_id1 IS NOT NULL) Then
821 Open Cur_custtrx_type_id1(p_set_of_books_id,
822 'INV',
823 p_hdr_sbg_object1_id1,
824 p_authoring_org_id);
825 Fetch Cur_custtrx_type_id1 INTO G_RAIL_REC.CUST_TRX_TYPE_ID,l_post_to_gl;
826 Close Cur_custtrx_type_id1;
827 Else
828 Open Cur_custtrx_type_id2(p_set_of_books_id,
829 'INV',
830 p_authoring_org_id);
831 Fetch Cur_custtrx_type_id2 INTO G_RAIL_REC.CUST_TRX_TYPE_ID,l_post_to_gl;
832 Close Cur_custtrx_type_id2;
833 End If;
834 END IF;
835
836 ----added for bug#3902948 (FP:3873737).
837 IF NVL(l_post_to_gl,'N') <> 'Y' THEN
838 G_RAIL_REC.GL_DATE := NULL;
839 END IF;
840
841 END Set_cust_trx_type;
842
843 /***************
844 Procedure to set transaction ext. id for invoice lines if Extension is captured
845 for the contract .
846 Transaction ext. id will be cascaded from contract to lines only if the billto
847 account matches for the Party
848 ***************/
849
850
851 Procedure set_extn_id_at_party ( p_bill_to_site_use_id IN NUMBER,
852 p_ext_id IN NUMBER,
853 p_cust_account_id IN NUMBER,
854 p_org_id IN NUMBER)
855
856 IS
857
858 Cursor line_party_cur(p_cust_account IN NUMBER, p_org_id IN NUMBER)IS
859 SELECT party_id
860 FROM hz_cust_accounts_all
861 WHERE cust_account_id = p_cust_account
862 AND nvl(org_id,p_org_id) = p_org_id;
863
864 Cursor hdr_party_cur(p_bill_to_site_use_id IN NUMBER,
865 p_org_id IN NUMBER) IS
866 SELECT hz.party_id from hz_party_sites hz
867 where hz.party_site_id in (
868 SELECT site.party_site_id from hz_cust_acct_sites_all site
869 where site.cust_acct_site_id in
870 ( select uses.cust_acct_site_id
871 from hz_cust_site_uses_all uses
872 where site_use_id = p_bill_to_site_use_id
873 and site_use_code = 'BILL_TO')
874 and nvl(site.org_id,p_org_id) = p_org_id);
875
876 l_line_party_id number;
877 l_hdr_party_id number;
878
879 BEGIN
880 OPEN line_party_cur(p_cust_account_id,p_org_id);
881 FETCH line_party_cur into l_line_party_id;
882 IF line_party_cur%FOUND THEN
883 OPEN hdr_party_cur(p_bill_to_site_use_id,p_org_id);
884 FETCH hdr_party_cur into l_hdr_party_id;
885 IF hdr_party_cur%FOUND THEN
886 /*Commented and Modified by sjanakir for Bug #6855301
887 IF l_line_party_id = l_line_party_id THEN */
888 IF l_line_party_id = l_hdr_party_id THEN
889 G_RAIL_REC.payment_trxn_extension_id := p_ext_id;
890 /* Commented by sjanakir for Bug #6855301
891 G_RAIL_REC.receipt_method_id := to_number(nvl(FND_PROFILE.VALUE_SPECIFIC('OKS_RECEIPT_METHOD_ID', NULL, NULL, NULL, p_org_id, NULL),'0')); */
892
893 Else
894 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB. no Transaction Ext. Id for line ');
895 End if;
896 End If;
897 Close hdr_party_cur;
898 End If;
899
900 CLOSE line_party_cur;
901
902
903 END set_extn_id_at_party;
904
905
906 /***************
907 Procedure to set commitment for invoice lines if commitment number is captured
908 for the top line.
909 commitment for the header will be cascaded to lines only if the billto account
910 matches for the customer
911 ***************/
912
913 Procedure set_commitment (p_hdr_commitment IN NUMBER,
914 p_bill_to_site_use_id IN NUMBER,
915 p_line_commitment IN NUMBER,
916 p_line_id IN NUMBER,
917 p_dnz_chr_id IN NUMBER,
918 p_cust_account_id IN NUMBER,
919 p_org_id IN NUMBER,
920 p_date IN DATE) /*Added new parameter p_date for bug 14337674*/
921 IS
922
923 /*Commented cursor for bug 14337674
924 Cursor comm_id_cur (p_comm_id IN NUMBER, p_org_id IN NUMBER)IS
925 SELECT rl.Customer_trx_line_id
926 FROM ra_customer_trx_lines_all rl
927 WHERE rl.customer_trx_id = p_comm_id
928 AND rl.org_id = p_org_id; */
929
930 /*Added cursor for bug 14337674*/
931
932 Cursor comm_id_cur (p_comm_id IN NUMBER, p_org_id IN NUMBER)IS
933 SELECT rl.Customer_trx_line_id
934 FROM ra_customer_trx_lines_all rl,ra_customer_trx_all rhdr
935 WHERE rhdr.customer_trx_id = rl.customer_Trx_id
936 AND rl.customer_trx_id = p_comm_id
937 AND rl.org_id = p_org_id
938 AND p_date BETWEEN Nvl(Trunc(rhdr.start_date_commitment),p_date) AND Nvl(Trunc(rhdr.end_date_commitment),p_date);
939
940 /*Added cursor for bug 14337674*/
941
942 Cursor cur_cust_account(p_bill_to_site_use_id IN NUMBER,
943 p_org_id IN NUMBER) IS
944 SELECT site.cust_account_id from hz_cust_acct_sites_all site
945 where site.cust_acct_site_id in
946 ( select uses.cust_acct_site_id
947 from hz_cust_site_uses_all uses
948 where site_use_id = p_bill_to_site_use_id
949 and site_use_code = 'BILL_TO')
950 and nvl(site.org_id,p_org_id) = p_org_id;
951
952 l_cust_account number;
953
954 BEGIN
955
956 IF (p_line_commitment is not null) Then
957 OPEN comm_id_cur(p_line_commitment,p_org_id);
958 FETCH comm_id_cur into G_RAIL_REC.reference_line_id;
959 CLOSE comm_id_cur;
960 If G_LOG_YES_NO = 'YES' then
961 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id IPM:reference_line_id '||G_RAIL_REC.reference_line_id);
962 End If;
963
964 -----check if hdr bill to account matches with the line bill to cascade commitment
965 Elsif (p_hdr_commitment is not null) Then
966 OPEN cur_cust_account(p_bill_to_site_use_id,p_org_id);
967 FETCH cur_cust_account into l_cust_account;
968 CLOSE cur_cust_account;
969
970 If l_cust_account = p_cust_account_id Then
971 OPEN comm_id_cur(p_hdr_commitment,p_org_id);
972 FETCH comm_id_cur into G_RAIL_REC.reference_line_id;
973 CLOSE comm_id_cur;
974
975 If G_LOG_YES_NO = 'YES' then
976 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_ref_line_id IPM:reference_line_id '||G_RAIL_REC.reference_line_id);
977 End If;
978 Else
979 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB. no commitment for line ');
980 End If;
981
982 END IF;
983
984 END set_commitment;
985
986
987 /* This procedure is called twice in program.
988 First time it is called with top_line_id and second time it
989 is called with sub_line_id.
990 */
991 Procedure Set_qty_and_uom(p_cle_id IN NUMBER,
992 p_dnz_chr_id IN NUMBER,
993 p_date_billed_from IN DATE,
994 p_date_billed_to IN DATE,
995 p_bill_action IN VARCHAR2,
996 p_lse_id IN NUMBER,
997 p_top_line IN VARCHAR2)
998 IS
999 Cursor Cur_item(p_id IN NUMBER) IS
1000 SELECT Object1_id1,
1001 Number_of_items,
1002 UOM_code,
1003 object1_id2
1004 FROM OKC_K_ITEMS
1005 WHERE CLE_ID = p_id;
1006
1007 Cursor Prim_uom_cur(p_id In NUMBER, p_org_id IN NUMBER) IS
1008 SELECT primary_uom_code
1009 From mtl_system_items_b
1010 WHERE inventory_item_id = p_id
1011 AND organization_id = p_org_id;
1012
1013
1014 l_status VARCHAR2(10);
1015 item_rec CUR_ITEM%ROWTYPE;
1016 BEGIN
1017 OPEN Cur_item(p_cle_id);
1018 FETCH Cur_item into item_rec;
1019 CLOSE Cur_item;
1020
1021
1022 -- IF (p_top_line = 'Y') THEN
1023
1024 ---IF (p_bill_action in ('AV','TR','STR')) THEN
1025 IF (p_bill_action in ('TR','STR')) THEN
1026 G_RAIL_REC.INVENTORY_ITEM_ID := NULL;
1027 ELSE
1028 G_RAIL_REC.INVENTORY_ITEM_ID := item_rec.Object1_id1;
1029
1030
1031 /********
1032 Bug# 4589116 : Hologic
1033 The profile option determines if contract line inventory org is used
1034 when calculating service line tax thorugh vertex.
1035 ****/
1036 G_RAIL_REC.WAREHOUSE_ID := NULL;
1037 if nvl(fnd_profile.value('OKS_INV_ORG_TAX_COMPUTE'),'NO') = 'YES'
1038 Then
1039 G_RAIL_REC.WAREHOUSE_ID := item_rec.Object1_id2;
1040 End if;
1041
1042 END IF;
1043
1044 G_RAIL_REC.QUANTITY := item_rec.Number_of_items;
1045
1046
1047 OKC_TIME_UTIL_PUB.get_duration
1048 ( p_start_date => p_date_billed_from,
1049 p_end_date => p_date_billed_to,
1050 x_duration => G_RAIL_REC.QUANTITY_ORDERED,
1051 x_timeunit => G_RAIL_REC.UOM_CODE,
1052 x_return_status => l_status
1053 );
1054
1055 IF (p_lse_id in (12,46)) THEN
1056 --The following line of code will be executed only for subscription
1057 --for Bug#4390448
1058 IF p_lse_id = 46 THEN
1059 G_RAIL_REC.QUANTITY_ORDERED := item_rec.Number_of_items;
1060 END IF;
1061
1062 OPEN prim_uom_cur(G_RAIL_REC.INVENTORY_ITEM_ID,
1063 get_organization_id (p_dnz_chr_id));
1064 FETCH prim_uom_cur into G_RAIL_REC.UOM_CODE;
1065 CLOSE prim_uom_cur;
1066 END IF;
1067 ---commented for bug#3121402
1068 -- ELSIF (p_top_line = 'N') THEN
1069 -- G_RAIL_REC.QUANTITY := item_rec.Number_of_items;
1070 -- G_RAIL_REC.QUANTITY_ORDERED := item_rec.Number_of_items;
1071 -- END IF; -- p_top_line
1072 END Set_qty_and_uom;
1073
1074 Procedure Set_salesrep_id( p_dnz_chr_id IN NUMBER)
1075 IS
1076 Cursor l_Salesrep_csr(p_code IN VARCHAR2,
1077 p_dnz_chr_id IN NUMBER) Is
1078 SELECT contact.object1_id1
1079 FROM okc_contacts contact,
1080 okc_k_party_roles_b party
1081 WHERE contact.cpl_id = party.id --p_cpl_id
1082 AND contact.cro_code = p_code
1083 AND party.rle_code in ('VENDOR','MERCHANT')
1084 AND party.dnz_chr_id = p_dnz_chr_id
1085 AND party.cle_id is null;
1086
1087 l_cro_code Varchar2(30);
1088 BEGIN
1089 l_cro_code := FND_PROFILE.VALUE('OKS_VENDOR_CONTACT_ROLE');
1090
1091 OPEN l_Salesrep_csr(l_cro_code, p_dnz_chr_id);
1092 FETCH l_Salesrep_csr into G_RAIL_REC.PRIMARY_SALESREP_ID ;
1093 CLOSE l_Salesrep_csr;
1094
1095 END Set_salesrep_id;
1096
1097
1098 Procedure Set_Attributes(p_contract_number IN VARCHAR2,
1099 p_con_modifier IN VARCHAR2,
1100 p_date_billed_from IN DATE,
1101 p_date_billed_to IN DATE,
1102 p_start_date IN DATE,
1103 p_cle_id IN NUMBER,
1104 p_attribute_category IN VARCHAR2,
1105 p_attribute1 IN VARCHAR2,
1106 p_attribute2 IN VARCHAR2,
1107 p_attribute3 IN VARCHAR2,
1108 p_attribute4 IN VARCHAR2,
1109 p_attribute5 IN VARCHAR2,
1110 p_attribute6 IN VARCHAR2,
1111 p_attribute7 IN VARCHAR2,
1112 p_attribute8 IN VARCHAR2,
1113 p_attribute9 IN VARCHAR2,
1114 p_attribute10 IN VARCHAR2,
1115 p_attribute11 IN VARCHAR2,
1116 p_attribute12 IN VARCHAR2,
1117 p_attribute13 IN VARCHAR2,
1118 p_attribute14 IN VARCHAR2,
1119 p_attribute15 IN VARCHAR2,
1120 p_currency_code IN VARCHAR2,
1121 p_cust_po_number IN VARCHAR2,
1122 p_dnz_chr_id IN NUMBER,
1123 p_org_id IN VARCHAR2,
1124 p_return_status IN OUT NOCOPY VARCHAR2)
1125 IS
1126 Cursor l_get_top_line_lvl_id(p_cle_id IN NUMBER,p_start_date IN DATE) is
1127 SELECT to_char(ole.date_start,'YYYY/MM/DD')
1128 FROM oks_level_elements ole
1129 WHERE ole.cle_id = p_cle_id
1130 AND trunc(p_start_date) >= trunc(ole.date_start)
1131 ORDER BY ole.date_start desc;
1132 --AND p_start_date between ole.date_start and ole.date_end;
1133
1134 BEGIN
1135 p_return_status := 'S';
1136
1137 G_RAIL_REC.BATCH_SOURCE_NAME := 'OKS_CONTRACTS';
1138 G_RAIL_REC.CREATED_BY := FND_GLOBAL.user_id;
1139 G_RAIL_REC.CREATION_DATE := sysdate;
1140 G_RAIL_REC.LAST_UPDATED_BY := FND_GLOBAL.user_id;
1141 G_RAIL_REC.LAST_UPDATE_DATE := sysdate;
1142 G_RAIL_REC.LINE_TYPE := G_LINE_TYPE;
1143 G_RAIL_REC.INTERFACE_LINE_CONTEXT := 'OKS CONTRACTS';
1144 G_RAIL_REC.REASON_CODE := NULL;
1145 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE1 := p_contract_number;
1146 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE2 := NVL(p_con_modifier,'-');
1147 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE4 := to_char(p_date_billed_from ,'YYYY/MM/DD');
1148 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE5 := to_char(p_date_billed_to,'YYYY/MM/DD');
1149
1150 --Added on 3/25 for grouping rule please do not remove the following assignment
1151
1152 OPEN l_get_top_line_lvl_id(p_cle_id , p_date_billed_from);
1153 FETCH l_get_top_line_lvl_id into G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE7;
1154 CLOSE l_get_top_line_lvl_id;
1155
1156 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE8 := to_char(p_start_date,'YYYY/MM/DD');
1157 G_RAIL_REC.RULE_START_DATE := p_date_billed_from;
1158 G_RAIL_REC.RULE_END_DATE := p_date_billed_to;
1159 G_RAIL_REC.SALES_ORDER_DATE := SYSDATE;
1160 G_RAIL_REC.SALES_ORDER_SOURCE := 'OKS_CONTRACTS';
1161 G_RAIL_REC.HEADER_ATTRIBUTE_CATEGORY := substrb(p_attribute_category,0,150);
1162 G_RAIL_REC.HEADER_ATTRIBUTE1 := substrb(p_attribute1,0,150);
1163 G_RAIL_REC.HEADER_ATTRIBUTE2 := substrb(p_attribute2,0,150);
1164 G_RAIL_REC.HEADER_ATTRIBUTE3 := substrb(p_attribute3,0,150);
1165 G_RAIL_REC.HEADER_ATTRIBUTE4 := substrb(p_attribute4,0,150);
1166 G_RAIL_REC.HEADER_ATTRIBUTE5 := substrb(p_attribute5,0,150);
1167 G_RAIL_REC.HEADER_ATTRIBUTE6 := substrb(p_attribute6,0,150);
1168 G_RAIL_REC.HEADER_ATTRIBUTE7 := substrb(p_attribute7,0,150);
1169 G_RAIL_REC.HEADER_ATTRIBUTE8 := substrb(p_attribute8,0,150);
1170 G_RAIL_REC.HEADER_ATTRIBUTE9 := substrb(p_attribute9,0,150);
1171 G_RAIL_REC.HEADER_ATTRIBUTE10 := substrb(p_attribute10,0,150);
1172 G_RAIL_REC.HEADER_ATTRIBUTE11 := substrb(p_attribute11,0,150);
1173 G_RAIL_REC.HEADER_ATTRIBUTE12 := substrb(p_attribute12,0,150);
1174 G_RAIL_REC.HEADER_ATTRIBUTE13 := substrb(p_attribute13,0,150);
1175 G_RAIL_REC.HEADER_ATTRIBUTE14 := substrb(p_attribute14,0,150);
1176 G_RAIL_REC.HEADER_ATTRIBUTE15 := substrb(p_attribute15,0,150);
1177 G_RAIL_REC.CURRENCY_CODE := p_currency_code;
1178 G_RAIL_REC.PURCHASE_ORDER := p_cust_po_number;
1179
1180 IF (p_con_modifier Is not null) THEN
1181 G_RAIL_REC.SALES_ORDER := p_contract_number || '-' || p_con_modifier;
1182 ELSE
1183 G_RAIL_REC.SALES_ORDER := p_contract_number;
1184 END IF;
1185
1186 G_RAIL_REC.ORG_ID := p_org_id;
1187 ----G_RAIL_REC.ORG_ID := get_authoring_org_id (p_dnz_chr_id);
1188
1189
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Attributes => FAILED IN G_RAIL Field ASSIGNMENT FOR '|| p_cle_id||' Error '||sqlerrm);
1193 p_return_status := 'E';
1194
1195 END Set_Attributes;
1196
1197
1198
1199 Procedure Set_comments(p_bill_action IN VARCHAR2)
1200 IS
1201
1202 Cursor fnd_csr (p_bill_action in varchar2) is
1203 select description from fnd_lookups
1204 where lookup_type = 'OKS_BILL_ACTIONS'
1205 and lookup_code = p_bill_action;
1206
1207
1208 BEGIN
1209 OPEN fnd_csr(p_bill_action);
1210 FETCH fnd_csr INTO G_RAIL_REC.COMMENTS;
1211 CLOSE fnd_csr;
1212
1213 /*****
1214 commented as part of bug# 5860501
1215
1216 IF (p_bill_action = 'TR') THEN
1217 G_RAIL_REC.COMMENTS := 'Termination of contract with credit';
1218 ELSIF (p_bill_action = 'RI') THEN
1219 G_RAIL_REC.COMMENTS := 'Regular Bill';
1220 ELSIF (p_bill_action in ('STR','SRI')) THEN
1221 G_RAIL_REC.COMMENTS := 'Settlement Bill';
1222 ELSIF (p_bill_action = 'AV') THEN
1223 G_RAIL_REC.COMMENTS := 'Average Bill';
1224 END IF;
1225 ***/
1226
1227 END Set_comments;
1228
1229
1230 Procedure Set_gl_date(p_cle_id IN NUMBER,
1231 p_date_billed_from IN DATE,
1232 p_bill_action IN VARCHAR2)
1233 IS
1234 Cursor cur_line_sll_rule (p_cle_id IN NUMBER,
1235 p_date_billed_from IN DATE) is
1236
1237 SELECT str.invoice_offset_days --str.action_offset_days
1238 FROM oks_stream_levels_b str,
1239 oks_level_elements lvl
1240 WHERE lvl.cle_id = p_cle_id
1241 AND trunc(p_date_billed_from) between trunc(lvl.date_start)
1242 and trunc(lvl.date_end)
1243 AND lvl.rul_id = str.id;
1244
1245 l_inv_offset NUMBER;
1246
1247 BEGIN
1248
1249 ----IF (p_bill_action NOT IN ('AV','TR','STR')) AND G_RAIL_REC.INVOICING_RULE_ID = -2 THEN
1250 IF (p_bill_action NOT IN ('TR','STR')) AND G_RAIL_REC.INVOICING_RULE_ID = -2 THEN
1251 OPEN cur_line_sll_rule(p_cle_id,p_date_billed_from);
1252 FETCH cur_line_sll_rule INTO l_inv_offset;
1253 CLOSE cur_line_sll_rule;
1254 END IF;
1255
1256
1257
1258 ----IF (p_bill_action in ('AV','TR','STR')) THEN
1259 IF (p_bill_action in ('TR','STR')) THEN
1260 G_RAIL_REC.GL_DATE := NULL;
1261
1262 ELSIF (G_RAIL_REC.INVOICING_RULE_ID = -3) OR
1263 (G_RAIL_REC.INVOICING_RULE_ID = -2 AND l_inv_offset IS NULL) THEN
1264
1265 G_RAIL_REC.GL_DATE := NULL;
1266
1267 ELSIF G_RAIL_REC.INVOICING_RULE_ID = -2 AND l_inv_offset IS NOT NULL THEN
1268
1269 IF TRUNC(p_date_billed_from) >= TRUNC(SYSDATE) THEN
1270
1271 IF (TRUNC(p_date_billed_from) + l_inv_offset) >= TRUNC(SYSDATE) THEN
1272 G_RAIL_REC.GL_DATE := G_RAIL_REC.TRX_DATE;
1273 ELSE --- <sysdate
1274 G_RAIL_REC.GL_DATE := SYSDATE;
1275 END IF;
1276
1277 ELSE ---- bill from < sysdate
1278
1279 IF (TRUNC(p_date_billed_from) + l_inv_offset) >= TRUNC(SYSDATE) THEN
1280 G_RAIL_REC.GL_DATE := G_RAIL_REC.TRX_DATE;
1281 ELSE --- <sysdate
1282 G_RAIL_REC.GL_DATE := NULL;
1283 END IF;
1284
1285 END IF; --chk for top line bill from
1286
1287 END IF;
1288
1289 END Set_gl_date;
1290
1291
1292
1293 Procedure Set_aggrement_and_contacts(p_dnz_chr_id IN NUMBER,
1294 p_cle_id IN NUMBER,
1295 p_date_billed_from IN DATE,
1296 p_bill_action IN VARCHAR2)
1297 IS
1298 Cursor Cur_agg_id(p_id IN NUMBER) IS
1299 SELECT Isa_agreement_id
1300 FROM OKC_GOVERNANCES
1301 WHERE dnz_Chr_id = p_id
1302 AND cle_id Is Null;
1303
1304 Cursor Contact_csr( p_hdr_id NUMBER, p_cle_id NUMBER) Is
1305 SELECT Contact.object1_id1 , Contact.cro_code
1306 FROM Okc_contacts Contact
1307 ,Okc_k_party_roles_B Party
1308 WHERE Contact.cpl_id = Party.id
1309 AND Contact.cro_code in ('CUST_BILLING','CUST_SHIPPING')
1310 AND p_date_billed_from between nvl(contact.start_date,p_date_billed_From) and
1311 nvl(contact.end_date,p_date_billed_from)
1312 AND party.dnz_chr_id = p_hdr_id
1313 AND party.cle_id = p_cle_id
1314 AND party.jtot_object1_code = 'OKX_PARTY';
1315 BEGIN
1316 OPEN Cur_agg_id(p_dnz_chr_id);
1317 FETCH Cur_agg_id into G_RAIL_REC.AGREEMENT_ID;
1318 CLOSE Cur_agg_id;
1319
1320 FOR contact_rec in Contact_csr(p_dnz_chr_id,p_cle_id)
1321 LOOP
1322 IF (contact_rec.cro_code = 'CUST_BILLING') THEN
1323 G_RAIL_REC.ORIG_SYSTEM_BILL_CONTACT_ID := contact_rec.object1_id1;
1324 ELSIF (contact_rec.cro_code = 'CUST_SHIPPING') THEN
1325 ----IF (p_bill_action not in ('AV','TR','STR')) THEN
1326 IF (p_bill_action not in ('TR','STR')) THEN
1327 G_RAIL_REC.ORIG_SYSTEM_SHIP_CONTACT_ID := contact_rec.object1_id1;
1328 END IF;
1329 END IF;
1330 END LOOP;
1331 END Set_aggrement_and_contacts;
1332
1333
1334
1335 ---------------------------------------------------------------------------
1336 -- procedure insert_RA_interface
1337 ---------------------------------------------------------------------------
1338 procedure insert_ra_interface(
1339 x_return_status OUT NOCOPY VARCHAR2,
1340 x_msg_count OUT NOCOPY NUMBER,
1341 x_msg_data OUT NOCOPY VARCHAR2
1342 )
1343 IS
1344 BEGIN
1345 x_return_status := 'S';
1346
1347 If G_LOG_YES_NO = 'YES' then
1348 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => AMOUNT'||G_RAIL_REC.AMOUNT);
1349 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => BATCH_SOURCE_NAME'||G_RAIL_REC.BATCH_SOURCE_NAME);
1350 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => CURRENCY_CODE'||G_RAIL_REC.CURRENCY_CODE);
1351 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => SET_OF_BOOKS_ID '||G_RAIL_REC.SET_OF_BOOKS_ID);
1352 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => ORG_ID '||G_RAIL_REC.ORG_ID);
1353 End If;
1354
1355
1356 IF ((G_RAIL_REC.CONVERSION_TYPE IS NULL) AND
1357 (G_RAIL_REC.CONVERSION_RATE is NULL)) THEN
1358 G_RAIL_REC.CONVERSION_TYPE := 'User';
1359 G_RAIL_REC.CONVERSION_RATE := 1;
1360 END IF;
1361
1362 /*** this should be null to fix bug# 1612349 -- Hari
1363
1364 if nvl(G_RAIL_REC.CONVERSION_TYPE, 'User') <> 'User' THEN
1365 G_RAIL_REC.CONVERSION_RATE := NULL;
1366 else
1367 G_RAIL_REC.CONVERSION_RATE := 1;
1368 end if;
1369 ***/
1370
1371 /***conversion rate should be null if type <> user****/
1372
1373 IF G_RAIL_REC.CONVERSION_TYPE <> 'User' THEN
1374 G_RAIL_REC.CONVERSION_RATE := NULL;
1375 END IF;
1376
1377 INSERT INTO RA_INTERFACE_LINES_ALL (
1378 ACCOUNTING_RULE_ID
1379 ,ACCOUNTING_RULE_DURATION
1380 ,AGREEMENT_ID
1381 ,AMOUNT
1382 ,BATCH_SOURCE_NAME
1383 ,COMMENTS
1384 ,CONVERSION_DATE
1385 ,CONVERSION_RATE
1386 ,CONVERSION_TYPE
1387 ,CREATED_BY
1388 ,CREATION_DATE
1389 ,CREDIT_METHOD_FOR_ACCT_RULE
1390 ,CREDIT_METHOD_FOR_INSTALLMENTS
1391 ,CURRENCY_CODE
1392 ,CUST_TRX_TYPE_ID
1393 ,DESCRIPTION
1394 ,LAST_UPDATED_BY
1395 ,LAST_UPDATE_DATE
1396 ,LINE_TYPE
1397 ,TRX_DATE
1398 ,GL_DATE
1399 ,PRINTING_OPTION
1400 ,INTERFACE_LINE_ATTRIBUTE1
1401 ,INTERFACE_LINE_ATTRIBUTE2
1402 ,INTERFACE_LINE_ATTRIBUTE3
1403 ,INTERFACE_LINE_ATTRIBUTE4
1404 ,INTERFACE_LINE_ATTRIBUTE5
1405 ,INTERFACE_LINE_ATTRIBUTE6
1406 ,INTERFACE_LINE_ATTRIBUTE7
1407 ,INTERFACE_LINE_ATTRIBUTE8
1408 ,INTERFACE_LINE_ATTRIBUTE9
1409 ,INTERFACE_LINE_ATTRIBUTE10
1410 ,INTERFACE_LINE_ATTRIBUTE11
1411 ,INTERFACE_LINE_ATTRIBUTE12
1412 ,INTERFACE_LINE_ATTRIBUTE13
1413 ,INTERFACE_LINE_ATTRIBUTE14
1414 ,INTERFACE_LINE_ATTRIBUTE15
1415 ,INTERFACE_LINE_ID
1416 ,INTERFACE_LINE_CONTEXT
1417 ,INVENTORY_ITEM_ID
1418 ,INVOICING_RULE_ID
1419 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
1420 ,ORIG_SYSTEM_BILL_ADDRESS_ID
1421 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
1422 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
1423 ,ORIG_SYSTEM_BILL_CONTACT_ID
1424 ,ORIG_SYSTEM_SHIP_CONTACT_ID
1425 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
1426 ,PRIMARY_SALESREP_NUMBER
1427 ,PRIMARY_SALESREP_ID
1428 ,PURCHASE_ORDER
1429 ,PURCHASE_ORDER_REVISION
1430 ,PURCHASE_ORDER_DATE
1431 ,CUSTOMER_BANK_ACCOUNT_ID
1432 ,RECEIPT_METHOD_ID
1433 ,RECEIPT_METHOD_NAME
1434 ,QUANTITY
1435 ,QUANTITY_ORDERED
1436 ,REASON_CODE
1437 ,REASON_CODE_MEANING
1438 ,REFERENCE_LINE_ID
1439 ,RULE_START_DATE
1440 ,RULE_END_DATE
1441 ,SALES_ORDER
1442 ,SALES_ORDER_LINE
1443 ,CONTRACT_LINE_ID
1444 ,SALES_ORDER_DATE
1445 ,SALES_ORDER_SOURCE
1446 ,SET_OF_BOOKS_ID
1447 ,TAX_EXEMPT_FLAG
1448 ,TAX_EXEMPT_NUMBER
1449 ,TAX_EXEMPT_REASON_CODE
1450 ,TAX_CODE
1451 ,TERM_ID
1452 ,UNIT_SELLING_PRICE
1453 ,UNIT_STANDARD_PRICE
1454 ,UOM_CODE
1455 ,HEADER_Attribute_CATEGORY
1456 ,HEADER_Attribute1
1457 ,HEADER_Attribute2
1458 ,HEADER_Attribute3
1459 ,HEADER_Attribute4
1460 ,HEADER_Attribute5
1461 ,HEADER_Attribute6
1462 ,HEADER_Attribute7
1463 ,HEADER_Attribute8
1464 ,HEADER_Attribute9
1465 ,HEADER_Attribute10
1466 ,HEADER_Attribute11
1467 ,HEADER_Attribute12
1468 ,HEADER_Attribute13
1469 ,HEADER_Attribute14
1470 ,HEADER_Attribute15
1471 ,Attribute_CATEGORY
1472 ,Attribute1
1473 ,Attribute2
1474 ,Attribute3
1475 ,Attribute4
1476 ,Attribute5
1477 ,Attribute6
1478 ,Attribute7
1479 ,Attribute8
1480 ,Attribute9
1481 ,Attribute10
1482 ,Attribute11
1483 ,Attribute12
1484 ,Attribute13
1485 ,Attribute14
1486 ,Attribute15
1487 ,ORG_ID
1488 ,TRANSLATED_DESCRIPTION
1489 ,invoiced_line_acctg_level
1490 ,Source_data_key1
1491 ,Source_data_key2
1492 ,Source_data_key3
1493 ,Source_data_key4
1494 ,Source_data_key5
1495 ,reference_line_attribute1
1496 ,reference_line_attribute2
1497 ,reference_line_attribute3
1498 ,reference_line_attribute4
1499 ,reference_line_attribute5
1500 ,reference_line_attribute6
1501 ,reference_line_attribute7
1502 ,reference_line_attribute8
1503 ,reference_line_attribute9
1504 ,reference_line_attribute10
1505 ,reference_line_context
1506 ,deferral_exclusion_flag
1507 ,parent_line_id
1508 ,payment_trxn_extension_id
1509 ,warehouse_id
1510 )
1511 VALues (
1512 G_RAIL_REC.ACCOUNTING_RULE_ID
1513 ,G_RAIL_REC.ACCOUNTING_RULE_DURATION
1514 ,G_RAIL_REC.AGREEMENT_ID
1515 ,G_RAIL_REC.AMOUNT
1516 ,G_RAIL_REC.BATCH_SOURCE_NAME
1517 ,G_RAIL_REC.COMMENTS
1518 ,G_RAIL_REC.CONVERSION_DATE
1519 ,G_RAIL_REC.CONVERSION_RATE
1520 ,nvl(G_RAIL_REC.CONVERSION_TYPE, 'User')
1521 ,G_RAIL_REC.CREATED_BY
1522 ,G_RAIL_REC.CREATION_DATE
1523 ,G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE
1524 ,G_RAIL_REC.CREDIT_METHOD_FOR_INSTALLMENTS
1525 ,G_RAIL_REC.CURRENCY_CODE
1526 ,G_RAIL_REC.CUST_TRX_TYPE_ID
1527 ,G_RAIL_REC.DESCRIPTION
1528 ,G_RAIL_REC.LAST_UPDATED_BY
1529 ,G_RAIL_REC.LAST_UPDATE_DATE
1530 ,G_RAIL_REC.LINE_TYPE
1531 ,G_RAIL_REC.TRX_DATE
1532 ,G_RAIL_REC.GL_DATE
1533 ,G_RAIL_REC.PRINTING_OPTION
1534 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE1
1535 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE2
1536 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE3
1537 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE4
1538 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE5
1539 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE6
1540 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE7
1541 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE8
1542 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE9
1543 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE10
1544 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE11
1545 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE12
1546 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE13
1547 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE14
1548 ,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE15
1549 ,G_RAIL_REC.INTERFACE_LINE_ID
1550 ,G_RAIL_REC.INTERFACE_LINE_CONTEXT
1551 ,G_RAIL_REC.INVENTORY_ITEM_ID
1552 ,G_RAIL_REC.INVOICING_RULE_ID
1553 ,G_RAIL_REC.ORIG_SYSTEM_BILL_CUSTOMER_ID
1554 ,G_RAIL_REC.ORIG_SYSTEM_BILL_ADDRESS_ID
1555 ,G_RAIL_REC.ORIG_SYSTEM_SHIP_CUSTOMER_ID
1556 ,G_RAIL_REC.ORIG_SYSTEM_SHIP_ADDRESS_ID
1557 ,G_RAIL_REC.ORIG_SYSTEM_BILL_CONTACT_ID
1558 ,G_RAIL_REC.ORIG_SYSTEM_SHIP_CONTACT_ID
1559 ,G_RAIL_REC.ORIG_SYSTEM_SOLD_CUSTOMER_ID
1560 ,G_RAIL_REC.PRIMARY_SALESREP_NUMBER
1561 ,G_RAIL_REC.PRIMARY_SALESREP_ID
1562 ,G_RAIL_REC.PURCHASE_ORDER
1563 ,G_RAIL_REC.PURCHASE_ORDER_REVISION
1564 ,G_RAIL_REC.PURCHASE_ORDER_DATE
1565 ,G_RAIL_REC.CUSTOMER_BANK_ACCOUNT_ID
1566 ,G_RAIL_REC.RECEIPT_METHOD_ID
1567 ,G_RAIL_REC.RECEIPT_METHOD_NAME
1568 --,G_RAIL_REC.QUANTITY /** for bug# 1882229 ***/
1569 ,G_RAIL_REC.QUANTITY_ORDERED
1570 ,G_RAIL_REC.QUANTITY_ORDERED
1571 ,G_RAIL_REC.REASON_CODE
1572 ,G_RAIL_REC.REASON_CODE_MEANING
1573 ,G_RAIL_REC.REFERENCE_LINE_ID
1574 ,G_RAIL_REC.RULE_START_DATE
1575 ,G_RAIL_REC.RULE_END_DATE
1576 ,G_RAIL_REC.SALES_ORDER
1577 ,G_RAIL_REC.SALES_ORDER_LINE
1578 ,G_RAIL_REC.CONTRACT_LINE_ID
1579 ,G_RAIL_REC.SALES_ORDER_DATE
1580 ,G_RAIL_REC.SALES_ORDER_SOURCE
1581 ,G_RAIL_REC.SET_OF_BOOKS_ID
1582 ,G_RAIL_REC.TAX_EXEMPT_FLAG
1583 ,G_RAIL_REC.TAX_EXEMPT_NUMBER
1584 ,G_RAIL_REC.TAX_EXEMPT_REASON_CODE
1585 ,G_RAIL_REC.TAX_CODE
1586 ,G_RAIL_REC.TERM_ID /*Check it out */
1587 ,G_RAIL_REC.UNIT_SELLING_PRICE
1588 ,G_RAIL_REC.UNIT_STANDARD_PRICE
1589 ,G_RAIL_REC.UOM_CODE
1590 ,G_RAIL_REC.HEADER_Attribute_CATEGORY
1591 ,G_RAIL_REC.HEADER_Attribute1
1592 ,G_RAIL_REC.HEADER_Attribute2
1593 ,G_RAIL_REC.HEADER_Attribute3
1594 ,G_RAIL_REC.HEADER_Attribute4
1595 ,G_RAIL_REC.HEADER_Attribute5
1596 ,G_RAIL_REC.HEADER_Attribute6
1597 ,G_RAIL_REC.HEADER_Attribute7
1598 ,G_RAIL_REC.HEADER_Attribute8
1599 ,G_RAIL_REC.HEADER_Attribute9
1600 ,G_RAIL_REC.HEADER_Attribute10
1601 ,G_RAIL_REC.HEADER_Attribute11
1602 ,G_RAIL_REC.HEADER_Attribute12
1603 ,G_RAIL_REC.HEADER_Attribute13
1604 ,G_RAIL_REC.HEADER_Attribute14
1605 ,G_RAIL_REC.HEADER_Attribute15
1606 ,G_RAIL_REC.Attribute_CATEGORY
1607 ,G_RAIL_REC.Attribute1
1608 ,G_RAIL_REC.Attribute2
1609 ,G_RAIL_REC.Attribute3
1610 ,G_RAIL_REC.Attribute4
1611 ,G_RAIL_REC.Attribute5
1612 ,G_RAIL_REC.Attribute6
1613 ,G_RAIL_REC.Attribute7
1614 ,G_RAIL_REC.Attribute8
1615 ,G_RAIL_REC.Attribute9
1616 ,G_RAIL_REC.Attribute10
1617 ,G_RAIL_REC.Attribute11
1618 ,G_RAIL_REC.Attribute12
1619 ,G_RAIL_REC.Attribute13
1620 ,G_RAIL_REC.Attribute14
1621 ,G_RAIL_REC.Attribute15
1622 ,G_RAIL_REC.Org_Id
1623 ,G_RAIL_REC.TRANSLATED_DESCRIPTION
1624 ,G_RAIL_REC.invoiced_line_acctg_level
1625 ,G_RAIL_REC.Source_data_key1
1626 ,G_RAIL_REC.Source_data_key2
1627 ,G_RAIL_REC.Source_data_key3
1628 ,G_RAIL_REC.Source_data_key4
1629 ,G_RAIL_REC.Source_data_key5
1630 ,G_RAIL_REC.reference_line_attribute1
1631 ,G_RAIL_REC.reference_line_attribute2
1632 ,G_RAIL_REC.reference_line_attribute3
1633 ,G_RAIL_REC.reference_line_attribute4
1634 ,G_RAIL_REC.reference_line_attribute5
1635 ,G_RAIL_REC.reference_line_attribute6
1636 ,G_RAIL_REC.reference_line_attribute7
1637 ,G_RAIL_REC.reference_line_attribute8
1638 ,G_RAIL_REC.reference_line_attribute9
1639 ,G_RAIL_REC.reference_line_attribute10
1640 ,G_RAIL_REC.reference_line_context
1641 ,G_RAIL_REC.deferral_exclusion_flag
1642 ,G_RAIL_REC.parent_line_id
1643 ,G_RAIL_REC.payment_trxn_extension_id
1644 ,G_RAIL_REC.warehouse_id
1645 );
1646
1647 -- G_RAIL_REC := G_INIT_RAIL_REC;
1648
1649 EXCEPTION
1650 When Others Then
1651 x_return_status := 'E';
1652 FND_FILE.PUT_LINE(FND_FILE.LOG,
1653 'OKS_ARFEEDER_PUB.insert_ra_interface => Exception in insert into RA_INTERFACE_LINES '||' SQLCOE = '||sqlcode ||' Sqlerrm = '||sqlerrm);
1654
1655 End insert_ra_interface;
1656 ---------------------------------------------------------------------------
1657 -- procedure insert_RA_revenue_distributions
1658 ---------------------------------------------------------------------------
1659 procedure insert_ra_rev_dist(
1660 x_return_status OUT NOCOPY VARCHAR2,
1661 p_cle_id IN NUMBER
1662 )
1663 IS
1664 CURSOR rev_dist_cur(p_cle_id NUMBER) IS
1665 SELECT ACCOUNT_CLASS,
1666 CODE_COMBINATION_ID,
1667 PERCENT
1668 FROM oks_rev_distributions
1669 WHERE cle_id = p_cle_id;
1670
1671 l_rev_dist rev_dist_cur%ROWTYPE;
1672
1673 BEGIN
1674 x_return_status := 'S';
1675 OPEN rev_dist_cur(p_cle_id);
1676 LOOP
1677 FETCH rev_dist_cur into l_rev_dist;
1678 EXIT WHEN rev_dist_cur%NOTFOUND;
1679
1680 INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL
1681 (ACCOUNT_CLASS,
1682 PERCENT,
1683 CODE_COMBINATION_ID,
1684 INTERFACE_LINE_CONTEXT,
1685 INTERFACE_LINE_ATTRIBUTE1,
1686 INTERFACE_LINE_ATTRIBUTE2,
1687 INTERFACE_LINE_ATTRIBUTE3,
1688 INTERFACE_LINE_ATTRIBUTE4,
1689 INTERFACE_LINE_ATTRIBUTE5,
1690 INTERFACE_LINE_ATTRIBUTE6,
1691 INTERFACE_LINE_ATTRIBUTE7,
1692 INTERFACE_LINE_ATTRIBUTE8,
1693 INTERFACE_LINE_ATTRIBUTE9,
1694 INTERFACE_LINE_ATTRIBUTE10,
1695 INTERFACE_LINE_ATTRIBUTE11,
1696 INTERFACE_LINE_ATTRIBUTE12,
1697 INTERFACE_LINE_ATTRIBUTE13,
1698 INTERFACE_LINE_ATTRIBUTE14,
1699 INTERFACE_LINE_ATTRIBUTE15,
1700 ORG_ID
1701 )
1702 values
1703 (
1704 l_rev_dist.account_class,
1705 l_rev_dist.percent,
1706 l_rev_dist.code_combination_id,
1707 G_RAIL_REC.interface_line_context,
1708 G_RAIL_REC.interface_line_attribute1,
1709 G_RAIL_REC.interface_line_attribute2,
1710 G_RAIL_REC.interface_line_attribute3,
1711 G_RAIL_REC.interface_line_attribute4,
1712 G_RAIL_REC.interface_line_attribute5,
1713 G_RAIL_REC.interface_line_attribute6,
1714 G_RAIL_REC.interface_line_attribute7,
1715 G_RAIL_REC.interface_line_attribute8,
1716 G_RAIL_REC.interface_line_attribute9,
1717 G_RAIL_REC.interface_line_attribute10,
1718 G_RAIL_REC.interface_line_attribute11,
1719 G_RAIL_REC.interface_line_attribute12,
1720 G_RAIL_REC.interface_line_attribute13,
1721 G_RAIL_REC.interface_line_attribute14,
1722 G_RAIL_REC.interface_line_attribute15,
1723 G_RAIL_REC.org_id
1724 ) ;
1725 END LOOP;
1726 CLOSE rev_dist_cur;
1727 EXCEPTION
1728 When Others Then
1729 x_return_status := 'E';
1730 FND_FILE.PUT_LINE(FND_FILE.LOG,
1731 'OKS_ARFEEDER_PUB.insert_ra_rev_dist => Exception in insert into RA_INTERFACE_DISTRIBUTIONS'||' SQLCODE ='||SQLCODE||' SQLERRM = '||SQLERRM);
1732
1733 END;
1734
1735
1736 ---------------------------------------------------------------------------
1737 -- procedure insert_RA_interface_sc
1738 ---------------------------------------------------------------------------
1739 procedure insert_ra_interface_sc(
1740 x_return_status OUT NOCOPY VARCHAR2,
1741 x_msg_count OUT NOCOPY NUMBER,
1742 x_msg_data OUT NOCOPY VARCHAR2,
1743 p_called_from IN NUMBER
1744 )
1745 IS
1746
1747
1748
1749 BEGIN
1750 x_return_status := 'S';
1751
1752 If G_LOG_YES_NO = 'YES' then
1753 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface_sc => AMOUNT'||G_RAISC_REC.SALES_CREDIT_AMOUNT_SPLIT);
1754 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface_sc => PERCENT'||G_RAISC_REC.SALES_CREDIT_PERCENT_SPLIT);
1755 End If;
1756
1757
1758
1759 G_RAISC_REC.SALES_CREDIT_AMOUNT_SPLIT := round(G_RAISC_REC.SALES_CREDIT_AMOUNT_SPLIT,2);
1760
1761 --assign values to G_RAISC_REC
1762
1763 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE1 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE1;
1764 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE2 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE2;
1765 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE3 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE3;
1766 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE4 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE4;
1767 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE5 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE5;
1768 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE6 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE6;
1769 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE7 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE7;
1770 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE8 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE8;
1771 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE9 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE9;
1772 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE10 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE10;
1773 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE11 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE11;
1774 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE12 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE12;
1775 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE13 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE13;
1776 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE14 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE14;
1777 G_RAISC_REC.INTERFACE_LINE_ATTRIBUTE15 := G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE15;
1778 G_RAISC_REC.INTERFACE_LINE_CONTEXT := G_RAIL_REC.INTERFACE_LINE_CONTEXT;
1779 G_RAISC_REC.ORG_ID := G_RAIL_REC.ORG_ID;
1780 G_RAISC_REC.CREATED_BY := G_RAIL_REC.CREATED_BY;
1781 G_RAISC_REC.CREATION_DATE := G_RAIL_REC.CREATION_DATE;
1782 G_RAISC_REC.LAST_UPDATED_BY := G_RAIL_REC.LAST_UPDATED_BY;
1783 G_RAISC_REC.LAST_UPDATE_DATE := G_RAIL_REC.LAST_UPDATE_DATE;
1784
1785
1786
1787 AR_InterfaceSalesCredits_GRP.insert_salescredit(
1788 p_salescredit_rec => G_RAISC_REC,
1789 x_return_status => x_return_status,
1790 x_msg_count => x_msg_count,
1791 x_msg_data => x_msg_data);
1792
1793 IF NVL(x_return_status,'E') <> 'S' THEN
1794 RAISE G_EXCEPTION_HALT_VALIDATION;
1795 END IF;
1796
1797
1798 -- G_RAIL_REC := G_INIT_RAIL_REC;
1799 -- G_RAISC_REC := G_INIT_RAISC_REC;
1800
1801 EXCEPTION
1802 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1803 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in insert RA_INTERFACE_SALESCREDITS ' || sqlerrm);
1804
1805 When Others Then
1806 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1807 FND_FILE.PUT_LINE(FND_FILE.LOG,
1808 'OKS_ARFEEDER_PUB.insert_ra_interface_sc => Exception in insert into RA_INTERFACE_SALESCREDITS'||' SQLCODE = '||SQLCODE ||' SQLERRM = '||SQLERRM);
1809
1810 End insert_ra_interface_sc;
1811
1812
1813 --mchoudha Fix for bug#4174921
1814 --added parameter p_hdr_id
1815 Procedure Sales_credit( p_id IN NUMBER,
1816 p_hdr_id IN Number,
1817 l_return_status OUT NOCOPY Varchar2
1818 )
1819 Is
1820 Cursor Sales_credit_cur IS
1821 Select Ctc_id
1822 ,sales_credit_type_id1
1823 ,percent, sales_group_id
1824 From OKS_K_SALES_CREDITS
1825 Where cle_id = p_id;
1826
1827 --added by mchoudha for bug#4174921
1828 --If line level salescredits are not present then
1829 --pass header level salescredits to AR
1830 --08-APR-2005 mchoudha Fix for bug#4293133
1831 --Added one more condition cle_id is null
1832 --to fetch only header level sales credits
1833 Cursor Sales_credit_hdr_cur IS
1834 Select Ctc_id
1835 ,sales_credit_type_id1
1836 ,percent
1837 ,sales_group_id
1838 From OKS_K_SALES_CREDITS
1839 Where chr_id = p_hdr_id
1840 And cle_id IS NULL;
1841
1842
1843 Sales_credit_rec Sales_credit_cur%ROWTYPE;
1844 l_ret_stat Varchar2(20);
1845 l_msg_cnt NUMBER;
1846 l_msg_data Varchar2(2000);
1847 p_called_from NUMBER;
1848 l_sales_group_id NUMBER;
1849
1850 Begin
1851 OPEN Sales_credit_cur;
1852 FETCH Sales_credit_cur into Sales_credit_rec;
1853 --added by mchoudha for bug#4174921
1854 --If line level salescredits are not present then
1855 --pass header level salescredits to AR
1856 if(Sales_credit_cur%NOTFOUND) THEN
1857 For Sales_credit_hdr_rec in Sales_credit_hdr_cur
1858 Loop
1859 if G_LOG_YES_NO = 'YES' then
1860 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Sales_credit => Header Level RA_Interface_SalesCredit');
1861 end if;
1862 G_RAISC_REC.SALESREP_ID := Sales_credit_hdr_rec.CTC_ID;
1863 G_RAISC_REC.SALES_CREDIT_TYPE_ID := to_number(Sales_credit_hdr_rec.SALES_CREDIT_TYPE_ID1);
1864 G_RAISC_REC.SALES_CREDIT_PERCENT_SPLIT :=Sales_credit_hdr_rec.PERCENT;
1865 G_RAISC_REC.SALESGROUP_ID:= Sales_credit_hdr_rec.sales_group_id;
1866 Insert_ra_interface_sc
1867 (
1868 l_return_status,
1869 l_msg_cnt,
1870 l_msg_data,
1871 p_called_from
1872 );
1873
1874 If l_return_status <> 'S' THEN
1875 oks_bill_rec_pub.get_message
1876 (l_msg_cnt => l_msg_cnt,
1877 l_msg_data => l_msg_data);
1878 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Sales_credit => Insert into RA_Interface_SalesCredit Failed For header id '||p_hdr_id);
1879
1880 End If;
1881
1882
1883 End Loop;--End of sales credit header Loop
1884 else
1885 LOOP
1886 If G_LOG_YES_NO = 'YES' then
1887 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Sales_credit =>Line level RA_Interface_SalesCredit');
1888 End If;
1889 Exit WHEN Sales_credit_cur%NOTFOUND;
1890 G_RAISC_REC.SALESREP_ID := Sales_credit_rec.CTC_ID;
1891 G_RAISC_REC.SALES_CREDIT_TYPE_ID := to_number(Sales_credit_rec.SALES_CREDIT_TYPE_ID1);
1892 G_RAISC_REC.SALES_CREDIT_PERCENT_SPLIT :=Sales_credit_rec.PERCENT;
1893 G_RAISC_REC.SALESGROUP_ID:= Sales_credit_rec.sales_group_id;
1894
1895 Insert_ra_interface_sc
1896 (
1897 X_RETURN_STATUS => L_return_status,
1898 X_MSG_COUNT => l_msg_cnt,
1899 X_MSG_DATA => l_msg_data,
1900 P_CALLED_FROM => p_called_from);
1901
1902
1903
1904 IF (l_return_status <> 'S') THEN
1905 oks_bill_rec_pub.get_message
1906 (L_MSG_CNT => l_msg_cnt,
1907 L_MSG_DATA => l_msg_data);
1908 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Sales_credit => Insert into RA_Interface_SalesCredit Failed For'||p_id);
1909
1910 End If;
1911
1912 Fetch Sales_credit_cur into Sales_credit_rec;
1913 END LOOP;--End of sales credit Loop
1914 end if; --End of Sales_credit_cur%NOTFOUND
1915 CLOSE Sales_credit_cur;
1916
1917 End;
1918
1919 --mchoudha Fix for bug#4174921
1920 --added parameter p_hdr_id
1921 Procedure Set_price_breaks(p_id IN NUMBER,
1922 p_prv IN NUMBER,
1923 p_contract_no IN VARCHAR2,
1924 p_contract_mod IN VARCHAR2,
1925 p_bill_inst_no IN NUMBER,
1926 p_amount IN NUMBER,
1927 p_int_att10 IN VARCHAR2,
1928 p_bcl_cle_id IN NUMBER,
1929 p_currency_code IN VARCHAR2,
1930 p_hdr_id IN Number,
1931 x_msg_cnt OUT NOCOPY NUMBER,
1932 x_msg_data OUT NOCOPY VARCHAR2,
1933 x_return_status OUT NOCOPY VARCHAR2
1934 )
1935 IS
1936
1937
1938 --order by added so that while populating reference fields in termination
1939 ---order can be found out.
1940
1941 Cursor Price_breaks_cur(p_id IN NUMBER) IS
1942 SELECT quantity,
1943 unit_price,
1944 amount
1945 FROM OKS_PRICE_BREAKS
1946 WHERE bsl_id = p_id
1947 ORDER BY quantity_from;
1948
1949 -- Added the sales_order in where condition as part of
1950 -- perf bug 3489672.
1951 Cursor Termination_cur(
1952 p_contract_number IN VARCHAR2,
1953 p_contract_modifier IN VARCHAR2,
1954 p_bill_instance_number IN NUMBER)
1955 IS
1956 SELECT abs(txl.extended_amount) extended_amount,
1957 txl.quantity_ordered,
1958 txl.quantity_invoiced,
1959 txl.unit_selling_price,
1960 txl.gross_unit_selling_price,
1961 txl.gross_extended_amount,
1962 txl.amount_includes_tax_flag,
1963 txl.customer_trx_line_id ,
1964 txh.trx_date
1965 FROM ra_customer_trx_all txh,
1966 ra_customer_trx_lines_all txl
1967 WHERE txl.interface_line_attribute1 = p_contract_number
1968 AND nvl(txl.interface_line_attribute2,'-') = nvl(p_contract_modifier,'-')
1969 AND txl.interface_line_attribute3 = p_bill_instance_number
1970 AND txl.interface_line_context = 'OKS CONTRACTS'
1971 AND txl.customer_trx_id = txh.customer_trx_id
1972 AND txl.extended_amount > 0
1973 AND txl.sales_order = p_contract_number|| decode(p_contract_modifier,null,'','-'||p_contract_modifier)
1974 ORDER BY abs(txl.extended_amount);
1975
1976 Cursor get_tax (p_customer_trx_line_id IN NUMBER) IS
1977 SELECT extended_amount tax_amount
1978 FROM RA_CUSTOMER_TRX_LINES_ALL
1979 WHERE line_type = 'TAX'
1980 AND link_to_cust_trx_line_id = p_customer_trx_line_id;
1981
1982 Price_breaks_rec Price_breaks_cur%ROWTYPE;
1983 inv_rec Termination_cur%ROWTYPE;
1984 l_pb_i NUMBER := 0;
1985 l_check_amount NUMBER := 0;
1986 l_term_amount NUMBER := 0;
1987 l_amount NUMBER := 0;
1988 l_quantity NUMBER := 0;
1989 l_tax_amount NUMBER := 0;
1990 l_extended_amount NUMBER := 0;
1991 l_unit_selling_price NUMBER := 0;
1992 l_int_att10 VARCHAR2(20);
1993 l_inclusive_tax VARCHAR2(10);
1994
1995
1996 BEGIN
1997
1998
1999 -- BUG 3638409
2000 l_int_att10 := p_int_att10;
2001 IF (p_prv =1) THEN
2002 l_pb_i := 0;
2003
2004 FOR Price_breaks_rec in Price_breaks_cur(p_id)
2005 LOOP
2006 l_pb_i := l_pb_i + 1;
2007 G_RAIL_REC.description := 'PB'||l_pb_i;
2008 G_RAIL_REC.quantity_ordered := Price_breaks_rec.quantity;
2009 G_RAIL_REC.quantity := Price_breaks_rec.quantity;
2010 G_RAIL_REC.unit_selling_price := Price_breaks_rec.unit_price;
2011 G_RAIL_REC.amount := Price_breaks_rec.amount;
2012 G_RAIL_REC.interface_line_attribute6 := Price_breaks_rec.amount;
2013 G_RAIL_REC.interface_line_attribute10:= l_int_att10||' for PB'||l_pb_i;
2014
2015
2016 --interface_line_attribute10 is populated with desc to make each line unique
2017 --This is required in case if amount is same for more than one break.
2018 --In such case autoinvoice should not reject the records.
2019
2020 Insert_ra_interface
2021 (
2022 x_return_status,
2023 x_msg_cnt,
2024 x_msg_data
2025 );
2026
2027 IF (x_return_status <> 'S') THEN
2028 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert into RA_Interface_Lines Failed while inserting price breaks ' );
2029 x_return_status := 'E';
2030 END IF; --IF (l_ret_stat <> 'S')
2031
2032
2033 INSERT_RA_REV_DIST( x_return_status,
2034 p_bcl_cle_id);
2035
2036 IF (x_return_status <> 'S') THEN
2037 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert INTO RA_REVENUE_DISTRIBUTIONS failed for Price Break');
2038
2039 x_return_status := 'E';
2040 END IF;
2041
2042 --mchoudha Fix for bug#4174921
2043 --added parameter p_hdr_id
2044 Sales_credit(p_bcl_cle_id ,
2045 p_hdr_id,
2046 x_return_status);
2047
2048 IF ( x_return_status <> 'S') THEN
2049 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert INTO RA_SALES_CREDIT FAILED for Price Breaks');
2050 END IF;
2051
2052 EXIT When x_return_status <> 'S';
2053 END LOOP;
2054
2055 UPDATE oks_bill_txn_lines
2056 SET cycle_refrence = l_int_att10||' for PB'
2057 WHERE bill_instance_number = TO_NUMBER(G_RAIL_REC.interface_line_attribute3);
2058
2059 ELSIF (p_prv = 3) THEN
2060
2061 l_pb_i := 0;
2062 l_check_amount := abs(p_amount);
2063 FOR inv_rec in Termination_cur(p_contract_no,
2064 p_contract_mod,
2065 p_bill_inst_no
2066 )
2067 LOOP
2068
2069 l_inclusive_tax := '';
2070 l_tax_amount := 0;
2071 l_unit_selling_price := 0;
2072
2073 IF (inv_rec.amount_includes_tax_flag = 'Y') THEN
2074
2075 l_extended_amount := inv_rec.gross_extended_amount ;
2076 l_unit_selling_price := inv_rec.gross_unit_selling_price;
2077 ELSE
2078 l_extended_amount := inv_rec.extended_amount ;
2079 l_unit_selling_price := inv_rec.unit_selling_price;
2080 END IF;
2081
2082 --IF (inv_rec.extended_amount <= l_check_amount) THEN
2083 IF (l_extended_amount <= l_check_amount) THEN
2084 l_term_amount := l_extended_amount;
2085 ELSE
2086 l_term_amount := l_check_amount;
2087 END IF;
2088
2089 l_pb_i := l_pb_i + 1;
2090 G_RAIL_REC.reference_line_id := inv_rec.customer_trx_line_id;
2091 G_RAIL_REC.trx_date := inv_rec.trx_date;
2092 G_RAIL_REC.description := 'PBT'||l_pb_i;
2093 G_RAIL_REC.quantity_ordered := round(l_term_amount/l_unit_selling_price);
2094 G_RAIL_REC.quantity := round(l_term_amount/l_unit_selling_price);
2095 G_RAIL_REC.amount := -1 * l_term_amount;
2096 G_RAIL_REC.interface_line_attribute6 := -1 * l_term_amount;
2097
2098 Insert_ra_interface
2099 (
2100 x_return_status,
2101 x_msg_cnt,
2102 x_msg_data
2103 );
2104
2105 IF (x_return_status <> 'S') THEN
2106 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert into RA_Interface_Lines Failed while inserting price breaks ' );
2107 x_return_status := 'E';
2108 END IF; --IF (l_ret_stat <> 'S')
2109
2110 /*Added for bug 14242256 */
2111 If nvl(FND_PROFILE.VALUE('AR_USE_INV_ACCT_FOR_CM_FLAG'),'Y')='N' THEN
2112 INSERT_RA_REV_DIST( x_return_status,
2113 p_bcl_cle_id);
2114 IF (x_return_status <> 'S') THEN
2115 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert INTO RA_REVENUE_DISTRIBUTIONS failed for Price Break');
2116
2117 x_return_status := 'E';
2118 END IF;
2119 END IF;
2120 /*Added for bug 14242256 */
2121
2122
2123
2124 --mchoudha Fix for bug#4174921
2125 --added parameter p_hdr_id
2126 Sales_credit(p_bcl_cle_id ,
2127 p_hdr_id,
2128 x_return_status);
2129
2130 IF ( x_return_status <> 'S') THEN
2131 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert INTO RA_SALES_CREDIT FAILED for Price Breaks');
2132 END IF;
2133
2134
2135 EXIT when x_return_status <> 'S';
2136
2137 l_check_amount := l_check_amount - l_term_amount;
2138
2139 IF (l_check_amount <= 0) THEN
2140 EXIT;
2141 END IF;
2142 END LOOP;
2143 END IF;
2144
2145 END Set_price_breaks;
2146
2147
2148
2149
2150 /*----------------------------------------------------------------------
2151 Returns Payment Method Details for a given Receipt Method Id
2152 ----------------------------------------------------------------------*/
2153 PROCEDURE Get_Pay_Method_Info
2154 ( p_pay_method_id IN NUMBER
2155 , p_pay_method_name OUT NOCOPY VARCHAR2
2156 , x_return_status OUT NOCOPY VARCHAR2
2157 )
2158 IS
2159
2160 CURSOR receipt_csr (pay_id number) is
2161 SELECT name
2162 FROM AR_RECEIPT_METHODS
2163 WHERE RECEIPT_METHOD_ID = pay_id
2164 AND SYSDATE >= NVL(START_DATE, SYSDATE)
2165 AND SYSDATE <= NVL(END_DATE, SYSDATE)
2166 /* Commented and Modification done by sjanakir for Bug #6855301
2167 AND PAYMENT_TYPE_CODE = 'CREDIT_CARD'; */
2168 AND PAYMENT_CHANNEL_CODE = 'CREDIT_CARD';
2169
2170 BEGIN
2171
2172 x_return_status := 'S';
2173 --errorout('In Get Pay Method Info');
2174
2175 OPEN receipt_csr(p_pay_method_id);
2176 FETCH receipt_csr INTO p_pay_method_name;
2177 CLOSE receipt_csr;
2178
2179 --errorout('Get Pay Method Info '||p_pay_method_name);
2180
2181 EXCEPTION
2182 WHEN OTHERS THEN
2183 x_return_status := 'E';
2184 FND_FILE.PUT_LINE(FND_FILE.LOG,
2185 'OKS_ARFEEDER_PUB.Get_Pay_Method_Info => Exception in insert into GET_PAY_METHOD'||' SQLCODE = '||SQLCODE||' SQLERRM = '||SQLERRM);
2186
2187
2188 END Get_Pay_Method_Info;
2189
2190
2191 ---------------------------------------------------------------------------
2192 -- PROCEDURE Get_rec_feeder
2193 ---------------------------------------------------------------------------
2194 PROCEDURE Get_REC_FEEDER
2195 (
2196 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2197 X_MSG_COUNT OUT NOCOPY NUMBER,
2198 X_MSG_DATA OUT NOCOPY VARCHAR2,
2199 P_FLAG IN NUMBER, -- 1 sales_group_id present, 2-not present.
2200 P_CALLED_FROM IN NUMBER,
2201 P_DATE IN DATE,
2202 P_CLE_ID IN NUMBER,
2203 P_PRV IN NUMBER,
2204 P_BILLREP_TBL IN OUT NOCOPY OKS_BILL_REC_PUB.bill_report_tbl_type,
2205 P_BILLREP_TBL_IDX IN NUMBER,
2206 P_BILLREP_ERR_TBL IN OUT NOCOPY OKS_BILL_REC_PUB.billrep_error_tbl_type,
2207 P_BILLREP_ERR_TBL_IDX IN OUT NOCOPY NUMBER
2208 ) Is
2209
2210 Cursor Cur_address_billto(p_id In Varchar2,Code Varchar2) IS
2211 Select a.cust_account_id,
2212 a.cust_acct_site_id,
2213 a.location_id,
2214 c.party_id,
2215 a.id1
2216 From Okx_cust_site_uses_v a, okx_customer_accounts_v c
2217 Where a.id1 = p_id
2218 And c.id1 = a.cust_account_id
2219 AND a.site_use_code = Code;
2220
2221
2222 /* can be used instead of OKX view query above
2223 SELECT ca.cust_account_id, ca.cust_acct_site_id ,
2224 l.location_id ,hca.party_id ,cs.site_use_iD
2225 FROM hz_locations l,
2226 hz_party_sites ps,
2227 hz_cust_accounts hca,
2228 hz_cust_acct_sites_all ca,
2229 hz_cust_site_uses_all cs
2230 WHERE cs.site_use_id = p_id
2231 AND cs.site_use_code = p_code
2232 AND ca.cust_acct_site_id = cs.cust_acct_site_id
2233 AND ps.location_id = l.location_id
2234 AND l.content_source_type = 'USER_ENTERED'
2235 AND ps.party_site_id = ca.party_site_id
2236 AND hca.cust_account_id = ca.cust_account_id;
2237 */
2238
2239
2240
2241 Cursor cur_bcl Is
2242 Select a.id ,
2243 a.btn_id ,
2244 a.date_billed_from,
2245 a.date_billed_to,
2246 a.cle_id,
2247 a.amount,
2248 a.bill_action,
2249 b.dnz_chr_id,
2250 a.date_next_invoice,
2251 b.start_date,
2252 b.lse_id,
2253 a.currency_code,
2254 b.date_terminated ,
2255 c.name lse_name,
2256 b.cust_acct_id,
2257 KLN.trxn_extension_id,
2258 b.line_number top_line_number
2259 From OKC_LINE_STYLES_V c,
2260 OKS_BILL_CONT_LINES a,
2261 OKC_K_LINES_B b,
2262 OKS_K_LINES_B KLN
2263 Where a.btn_id Is Null
2264 And a.amount Is Not Null
2265 And b.id = a.cle_id
2266 And b.id = p_cle_id
2267 AND b.id = KLN.cle_id
2268 And a.bill_action not in ('TR','STR')
2269 And c.id = b.lse_id
2270 And p_cle_id is not null;
2271
2272
2273 CURSOR cur_bcl_pr IS
2274 Select a.id ,
2275 a.btn_id,
2276 a.date_billed_from,
2277 a.date_billed_to,
2278 a.cle_id,
2279 a.amount,
2280 a.bill_action,
2281 b.dnz_chr_id,
2282 a.date_next_invoice,
2283 b.start_date,
2284 b.lse_id,
2285 a.currency_code,
2286 b.date_terminated,
2287 c.name lse_name,
2288 b.cust_acct_id,
2289 KLN.trxn_extension_id,
2290 b.line_number top_line_number
2291 From OKC_LINE_STYLES_V c,
2292 OKS_BCL_PR a,
2293 OKC_K_LINES_B b,
2294 OKS_K_LINES_B KLN
2295 Where a.btn_id is null
2296 And a.amount Is Not Null
2297 And b.id = a.cle_id
2298 And b.id = p_cle_id
2299 AND b.id = KLN.cle_id
2300 And c.id = b.lse_id
2301 And a.bill_action not in ('AV','TR','STR')
2302 And p_cle_id is not null;
2303
2304 --19-NOV-2003 Mani
2305 --added parallel hint on oks_bill_cont_lines
2306
2307 --mchoudha bug#4638641
2308 --added RI also in the bill_action condition of where clause
2309 CURSOR cur_bcl_term IS
2310 Select /*+ PARALLEL(a) */
2311 a.id ,
2312 a.btn_id ,
2313 a.date_billed_from,
2314 a.date_billed_to,
2315 a.cle_id,
2316 a.amount,
2317 a.bill_action,
2318 b.dnz_chr_id,
2319 a.date_next_invoice,
2320 b.start_date,
2321 b.lse_id,
2322 a.currency_code,
2323 b.date_terminated,
2324 c.name lse_name,
2325 b.cust_acct_id,
2326 KLN.trxn_extension_id,
2327 b.line_number top_line_number
2328 From
2329 OKC_LINE_STYLES_V c,
2330 OKS_BILL_CONT_LINES a,
2331 OKC_K_LINES_B b,
2332 OKS_K_LINES_B KLN
2333 Where a.btn_id is null
2334 And a.amount Is Not Null
2335 And b.id = a.cle_id
2336 AND b.id = KLN.cle_id
2337 And c.id = b.lse_id
2338 And a.bill_action in ('AV','TR','STR','SRI','RI')
2339 And p_cle_id is null;
2340
2341
2342 Cursor cur_hdr_Rules (p_cle_id IN NUMBER) is
2343 SELECT
2344 nvl(rhdr.hold_billing,'N') Hold_Billing_flag,
2345 --Modified the below line for bugfix 14737811
2346 nvl(rhdr.ar_interface_yn,DECODE(rhdr.hold_billing,'Y','N','Y')) ar_interface_yn, /* Added By sjanakir for Bug #6821826 */
2347 hdr.inv_rule_id , -- IRE
2348 rhdr.acct_rule_id , -- ARL
2349 rhdr.inv_trx_type , --SBG
2350 nvl(rhdr.summary_trx_yn,'N') summary_trx_yn, --SBG
2351 hdr.payment_term_id , --PTR
2352 rhdr.tax_exemption_id , --TAX
2353 rhdr.tax_status , --TAX
2354 hdr.conversion_type , --CVN
2355 hdr.conversion_rate , --CVN
2356 hdr.conversion_rate_date , --CVN
2357 hdr.bill_to_site_use_id , --BTO
2358 hdr.ship_to_site_use_id , --BTO
2359 rhdr.commitment_id,
2360 hdr.contract_number,
2361 hdr.contract_number_modifier,
2362 hdr.attribute_category,
2363 hdr.attribute1,
2364 hdr.attribute2,
2365 hdr.attribute3,
2366 hdr.attribute4,
2367 hdr.attribute5,
2368 hdr.attribute6,
2369 hdr.attribute7,
2370 hdr.attribute8,
2371 hdr.attribute9,
2372 hdr.attribute10,
2373 hdr.attribute11,
2374 hdr.attribute12,
2375 hdr.attribute13,
2376 hdr.attribute14,
2377 hdr.attribute15,
2378 hdr.currency_code,
2379 hdr.authoring_org_id,
2380 hdr.org_id,
2381 nvl(line.cust_po_number,hdr.cust_po_number) cust_po_number,
2382 nvl(line.payment_type,rhdr.payment_type) payment_type,
2383 rhdr.trxn_extension_id,
2384 --Start fixes of eBTax uptake bug#4756579
2385 rhdr.exempt_certificate_number,
2386 rhdr.exempt_reason_code
2387 --End fixes of eBtax uptake bug#4756579
2388 FROM
2389 OKS_K_HEADERS_B rhdr,
2390 OKC_K_HEADERS_B hdr,
2391 OKS_K_LINES_B line
2392 WHERE line.cle_id = p_cle_id
2393 AND hdr.id = line.dnz_chr_id
2394 AND hdr.id = rhdr.chr_id;
2395
2396 Cursor cur_line_rules(p_cle_id IN NUMBER) IS
2397 SELECT line.bill_to_site_use_id ,
2398 line.ship_to_site_use_id,
2399 rline.commitment_id,
2400 rline.tax_code,
2401 rline.tax_status,
2402 rline.tax_exemption_id,
2403 line.inv_rule_id,
2404 line.cust_acct_id,
2405 rline.acct_rule_id,
2406 rline.invoice_text,
2407 rline.inv_print_flag,
2408 rline.usage_type,
2409 --Start fixes of eBTax uptake bug#4756579
2410 rline.exempt_certificate_number,
2411 rline.exempt_reason_code,
2412 rline.tax_classification_code
2413 --End fixes of eBtax uptake bug#4756579
2414 FROM OKS_K_LINES_V rline,
2415 OKC_K_LINES_B line
2416 WHERE line.id = p_cle_id
2417 AND rline.cle_id = line.id;
2418
2419
2420 Cursor ar_date_cur(p_bcl_id NUMBER) IS
2421 Select min(date_to_interface)
2422 From oks_bill_sub_lines
2423 Where bcl_id = p_bcl_id;
2424
2425 Cursor Cur_bsl(id_in In NUMBER) IS
2426 Select a.id,
2427 a.cle_id,
2428 a.date_billed_from,
2429 a.date_billed_to,
2430 a.average,
2431 a.amount ,
2432 b.date_terminated,
2433 c.cle_id top_line_id ,
2434 b.lse_id ,
2435 rline.invoice_text,
2436 rline.inv_print_flag,
2437 b.line_number sub_line_number
2438 From OKS_BILL_SUB_LINES a,
2439 OKS_BILL_CONT_LINES c,
2440 OKS_K_LINES_V rline,
2441 OKC_K_LINES_B b
2442 Where a.bcl_id = id_in
2443 AND c.id = a.bcl_id
2444 AND a.cle_id = b.id
2445 AND rline.cle_id = b.id;
2446
2447
2448
2449 Cursor Cur_k_lines(id_in IN NUMBER) IS
2450 Select
2451 id
2452 ,start_date
2453 ,end_date
2454 ,item_description
2455 ,block23text
2456 ,attribute1
2457 ,attribute2
2458 ,attribute3
2459 ,attribute4
2460 ,attribute5
2461 ,attribute6
2462 ,attribute7
2463 ,attribute8
2464 ,attribute9
2465 ,attribute10
2466 ,attribute11
2467 ,attribute12
2468 ,attribute13
2469 ,attribute14
2470 ,attribute15
2471 ,attribute_category
2472 From OKC_K_LINES_V
2473 Where id = id_in;
2474 -- changed the where condition of cur_tax for tax exemption project
2475 -- if the exemption id is invalid then null is passed to the interface table
2476
2477 Cursor check_for_account_id(p_bill_to_site_use_id IN NUMBER,
2478 p_org_id IN NUMBER,
2479 p_cust_account_id IN NUMBER) IS
2480 SELECT site.cust_account_id from hz_cust_acct_sites_all site
2481 where site.cust_acct_site_id in
2482 ( select uses.cust_acct_site_id
2483 from hz_cust_site_uses_all uses
2484 where site_use_id = p_bill_to_site_use_id
2485 and site_use_code = 'BILL_TO')
2486 and nvl(site.org_id,p_org_id) = p_org_id
2487 and site.cust_account_id = p_cust_account_id;
2488
2489 l_cust_account number;
2490
2491
2492
2493 Cursor Cur_tax(id_in IN VARCHAR2,l_trx_date IN DATE) IS
2494 --Start fixes of eBTax uptake bug#4756579
2495 SELECT v2.exempt_certificate_number,
2496 v2.exempt_reason_code
2497 FROM zx_exemptions v2
2498 WHERE (trunc(l_trx_date) BETWEEN trunc(v2.EFFECTIVE_FROM)
2499 AND nvl( trunc(v2.EFFECTIVE_TO), trunc(l_trx_date)))
2500 AND v2.tax_exemption_id = id_in;
2501 --End fixes of eBTax uptake bug#4756579
2502
2503
2504 Cursor cur_tax_code (p_id IN VARCHAR2, p_org_id NUMBER) IS
2505 --Start fixes of eBTax uptake bug#4756579
2506 SELECT tax_classification_code
2507 FROM zx_id_tcc_mapping
2508 WHERE tax_rate_code_id = p_id
2509 AND org_id = p_org_id;
2510
2511 Cursor Cur_valid_exemption(p_exempt_number IN VARCHAR2, p_trx_date IN DATE) IS
2512 SELECT v2.exempt_certificate_number,
2513 v2.exempt_reason_code
2514 FROM zx_exemptions v2
2515 WHERE (trunc(p_trx_date) BETWEEN trunc(v2.EFFECTIVE_FROM)
2516 AND nvl( trunc(v2.EFFECTIVE_TO), trunc(p_trx_date)))
2517 AND v2.exempt_certificate_number = p_exempt_number;
2518
2519
2520
2521
2522
2523
2524 --End fixes of eBTax uptake bug#4756579
2525
2526 Cursor Cur_lsl_type(id_in IN NUMBER) IS
2527 Select cl.lse_id,lsl.name
2528 From OKC_LINE_STYLES_V lsl, OKC_K_LINES_B cl
2529 Where lsl.id = cl.lse_id
2530 And cl.id = id_in;
2531
2532
2533 Cursor acct_type(p_id NUMBER) Is
2534 SELECT type,frequency
2535 FROM ra_rules
2536 WHERE rule_id = p_id;
2537
2538 /* Above select avoids OKX view usage
2539 Select type
2540 From OKX_RULES_V
2541 Where id1 = p_id;
2542 */
2543
2544
2545 Cursor Cur_k_headers(p_id NUMBER) Is
2546 Select Contract_number
2547 ,Contract_number_modifier
2548 ,authoring_org_id
2549 ,org_id
2550 ,currency_code
2551 ,cust_po_number
2552 ,attribute1
2553 ,attribute2
2554 ,attribute3
2555 ,attribute4
2556 ,attribute5
2557 ,attribute6
2558 ,attribute7
2559 ,attribute8
2560 ,attribute9
2561 ,attribute10
2562 ,attribute11
2563 ,attribute12
2564 ,attribute13
2565 ,attribute14
2566 ,attribute15
2567 ,attribute_category
2568 From OKC_K_HEADERS_B
2569 Where id = p_id;
2570
2571 --mchoudha Bug#3676706
2572 --changed the condition from trunc(bcl.date_billed_from) = trunc(p_date_from)
2573 --to trunc(bcl.date_billed_from) <= trunc(p_date_from) to consider
2574 --cases where termination date is somewhere between the billing period
2575
2576 Cursor check_summary_billed (p_cle_id IN NUMBER,
2577 p_date_from IN DATE,
2578 p_date_to IN DATE) IS
2579 SELECT btl.bsl_id ,bcl.btn_id from oks_bill_txn_lines btl,
2580 oks_bill_cont_lines bcl
2581 WHERE bcl.cle_id = p_cle_id
2582 AND trunc(bcl.date_billed_from) <= trunc(p_date_from)
2583 AND trunc(bcl.date_billed_to) = trunc(p_date_to)
2584 AND bcl.bill_action = 'RI'
2585 AND bcl.btn_id = btl.btn_id ;
2586
2587
2588 Cursor Cur_Parent_Order_line(p_id IN NUMBER) IS
2589 SELECT last_oe_order_line_id from csi_item_instances
2590 where instance_id in (select to_number(itm.object1_id1) from okc_k_items itm
2591 where itm.cle_id = p_id
2592 and jtot_object1_code = 'OKX_CUSTPROD');
2593
2594
2595 Cursor Sales_credit_cur(p_id NUMBER) IS
2596 Select Ctc_id
2597 ,sales_credit_type_id1
2598 ,percent
2599 From OKS_K_SALES_CREDITS
2600 Where cle_id = p_id;
2601
2602
2603 Cursor chk_price_break_cur(p_id IN NUMBER) IS
2604 SELECT count(1)
2605 FROM oks_price_breaks
2606 WHERE bsl_id = p_id;
2607
2608 Cursor cur_okc_k_item_qty(p_id IN NUMBER) IS
2609 SELECT Number_of_items,
2610 UOM_code
2611 FROM OKC_K_ITEMS
2612 WHERE CLE_ID = p_id;
2613
2614
2615 Cursor Interface_pb_cur IS
2616 SELECT interface_price_break
2617 FROM OKS_K_DEFAULTS
2618 WHERE cdt_type = 'MDT';
2619
2620 Cursor Bill_instance_cur(p_id IN NUMBER) IS
2621 SELECT average
2622 FROM oks_bill_sub_lines
2623 WHERE id = p_id;
2624
2625
2626 -- Modified the following cursor for perf bug#3489672
2627 -- added sales order in where clause.
2628 Cursor Inv_count_cur(p_contract_number IN VARCHAR2,
2629 p_contract_modifier IN VARCHAR2,
2630 p_bill_instance_number IN NUMBER)
2631 IS
2632 SELECT count(*)
2633 FROM ra_customer_trx_all d, --Okx_customer_trx_v d
2634 ra_customer_trx_lines_all c, --Okx_cust_trx_lines_v c
2635 ra_cust_trx_types_all types
2636 WHERE c.interface_line_attribute1 = p_contract_number
2637 AND nvl(c.interface_line_attribute2,'-') = nvl(p_contract_modifier ,'-')
2638 AND c.Interface_line_attribute3 = to_char(p_bill_instance_number)
2639 AND c.Interface_line_context = 'OKS CONTRACTS'
2640 AND c.customer_trx_id = d.customer_trx_id
2641 AND c.sales_order = p_contract_number|| decode(p_contract_modifier,null,'','-'||p_contract_modifier)
2642 AND d.cust_trx_type_id = types.cust_trx_type_id
2643 AND types.type = 'INV' ;
2644
2645 --Start 27-Dec-2005 nechatur Fix for bug#4390448
2646 --Added the following two cursors Cur_bsd and l_usage_csr
2647 Cursor Cur_bsd(p_id IN Number) IS
2648 Select bsd.result result,
2649 bsd.unit_of_measure uom_code
2650 From oks_bill_sub_line_dtls bsd
2651 Where bsd.bsl_id = p_id;
2652
2653 Cursor l_usage_csr(p_id IN Number,p_hdr_id IN Number) Is
2654 Select usage_type
2655 From oks_k_lines_b kln
2656 Where kln.cle_id = p_id;
2657 --End 27-Dec-2005 nechatur Fix for bug#4390448
2658
2659
2660 CURSOR tax_info_csr(p_site_use_id IN NUMBER) IS
2661 SELECT c.party_id,
2662 a.party_site_id
2663 FROM hz_cust_acct_sites a,
2664 hz_cust_site_uses b,
2665 hz_party_sites c
2666 WHERE a.cust_acct_site_id = b.cust_acct_site_id
2667 AND c.party_site_id = a.party_site_id
2668 AND b.site_use_id = p_site_use_id;
2669
2670
2671 CURSOR Cur_Batch_Source_Id(p_org_id IN NUMBER)
2672 IS
2673 SELECT BATCH_SOURCE_ID
2674 FROM ra_batch_sources_all
2675 WHERE org_id = p_org_id
2676 AND NAME = 'OKS_CONTRACTS';
2677
2678
2679 --skuchima added for bug#10260151
2680 CURSOR cur_inv_print_flag(p_id IN number) IS
2681 SELECT inv_print_flag
2682 FROM oks_k_lines_b
2683 WHERE cle_id=p_id;
2684
2685 l_inv_print_flag VARCHAR2(1);
2686
2687
2688 header_rec Cur_k_headers%ROWTYPE;
2689 Bcl_rec Cur_bcl%ROWTYPE;
2690 rul_hdr_rec cur_hdr_Rules%ROWTYPE;
2691 rul_line_rec cur_line_Rules%ROWTYPE;
2692 BSL_rec Cur_BSL%ROWTYPE;
2693 l_bill_profile NUMBER := G_SUM;
2694 l_dnz_chr_id NUMBER;
2695 l_request_id VARCHAR2(60);
2696 l_set_of_books_id NUMBER;
2697 l_trx_date VARCHAR2(30);
2698 l_currency_code VARCHAR2(60);
2699 l_payment_type VARCHAR2(30);
2700 l_customer_account_id NUMBER;
2701 l_ret_stat VARCHAR2(20);
2702 l_msg_cnt NUMBER;
2703 l_msg_count NUMBER;
2704 l_msg_data VARCHAR2(2000);
2705 l_index NUMBER;
2706 l_lse_id NUMBER;
2707 l_order_line_id NUMBER;
2708 l_num_periods NUMBER;
2709 l_lse_name VARCHAR2(240);
2710 l_desc VARCHAR2(2000);
2711 l_sal_person_cnt NUMBER;
2712 l_sales_credit_yn_profile VARCHAR2(5);
2713 l_sales_credit_distr_profile VARCHAR2(20);
2714 l_bf_flag VARCHAR2(3);
2715 l_acct_frequency VARCHAR2(15);
2716 l_acct_calender VARCHAR2(15);
2717 l_sc_rec1 NUMBER;
2718 l_sc_rec2 NUMBER;
2719 l_fail_stat NUMBER := 0; /* set to 1 if any inser or update failed */
2720 l_org_id NUMBER;
2721 l_start_date DATE;
2722 l_End_date DATE;
2723 l_unmapped_date DATE;
2724 l_type VARCHAR2(10);
2725 BSL_rec_NUM NUMBER;
2726 l_bill_instance_number NUMBER;
2727 tax_rec Cur_tax%rowtype;
2728 billto_rec cur_address_billto%rowtype;
2729 sales_credit_rec Sales_credit_cur%rowtype;
2730 lines_rec Cur_k_lines%rowtype;
2731 l_btnv_tbl_in OKS_BTN_PVT.btnv_tbl_type;
2732 l_btnv_tbl_out OKS_BTN_PVT.btnv_tbl_type;
2733 l_bclv_tbl_in OKS_bcl_PVT.bclv_tbl_type;
2734 l_bclv_tbl_out OKS_bcl_PVT.bclv_tbl_type;
2735 l_btlv_tbl_in OKS_BTL_PVT.btlv_tbl_type;
2736 l_btlv_tbl_out OKS_BTL_PVT.btlv_tbl_type;
2737 l_btn_pr_tbl_in OKS_BTN_PRINT_PREVIEW_PVT.btn_pr_tbl_type;
2738 l_btn_pr_tbl_out OKS_BTN_PRINT_PREVIEW_PVT.btn_pr_tbl_type;
2739 l_btl_pr_tbl_in OKS_BTL_PRINT_PREVIEW_PVT.btl_pr_tbl_type;
2740 l_btl_pr_tbl_out OKS_BTL_PRINT_PREVIEW_PVT.btl_pr_tbl_type;
2741 l_amount NUMBER := 0;
2742 l_bill_profile_flag VARCHAR2(4);
2743 l_contact NUMBER;
2744 l_cpl_id NUMBER;
2745 l_bsl_id NUMBER;
2746 l_chk_bcl_id NUMBER;
2747 l_cro_code VARCHAR2(30);
2748 l_sublse_id NUMBER;
2749 l_cust_id NUMBER; --- for CC process
2750 l_site_use_id NUMBER; --- for CC process
2751 l_sub_line_id NUMBER; --- for CC process
2752 l_cc_only BOOLEAN := TRUE ; --- for CC process
2753 l_pay_method_id NUMBER; -- for CC process
2754 l_status VARCHAR2(15);
2755 l_hdr_sbg_object1_id1 NUMBER;
2756 l_ar_date DATE;
2757 l_hold_flag VARCHAR2(10);
2758 l_hdr_summary_flag VARCHAR2(10);
2759 l_line_payment_mth VARCHAR2(10);
2760 l_select_counter NUMBER := 0;
2761 l_reject_counter NUMBER := 0;
2762 l_process_counter NUMBER := 0;
2763 l_interface_pb VARCHAR2(1);
2764 l_price_break_count NUMBER := 0;
2765 l_inv_count NUMBER := 0;
2766 l_bill_instance_no NUMBER := 0;
2767 l_subline_failed BOOLEAN;
2768 l_line_failed BOOLEAN;
2769 l_subline_count NUMBER := 0;
2770 MAIN_CUR_EXCEPTION Exception;
2771 l_usage_type VARCHAR2(3);
2772 report_bill_action VARCHAR2(3) := 'RI';
2773 l_ship_to_party_site_id NUMBER;
2774 l_batch_source_id NUMBER;
2775 l_bill_to_party_site_id NUMBER;
2776 l_bill_to_party_id NUMBER;
2777 l_valid_flag VARCHAR2(3) := 'N';
2778
2779 --Start fixes of eBtax uptake bug#4756579
2780 l_api_name CONSTANT VARCHAR2(30) := 'Get_REC_FEEDER';
2781 --end fixes of eBtax uptake bug#4756579
2782 /* Added by sjanakir for Bug # 6872005 */
2783 /* Modified by cgopinee for PA-DSS enhancement*/
2784 l_cc_expired VARCHAR2(10);
2785
2786 /* Added by sjanakir for Bug # 6855301 */
2787 l_as_of_date DATE;
2788 l_flag Number; /*modified for bug:8943481*/
2789 l_com_date DATE; /*added for bug 14337674*/
2790
2791 FUNCTION Get_receipt_method_id (p_customer_id IN NUMBER,
2792 p_site_use_id IN NUMBER,
2793 p_cc_only IN BOOLEAN,
2794 p_as_of_date IN DATE,
2795 p_org_id IN NUMBER) RETURN NUMBER IS
2796
2797 l_check BOOLEAN := FALSE;
2798 l_primary BOOLEAN := TRUE ;
2799 l_cust_pay_method_id NUMBER;
2800 l_pay_method_id NUMBER;
2801
2802 BEGIN
2803 l_cust_pay_method_id := arp_ext_bank_pkg.get_cust_pay_method
2804 (p_customer_id => p_customer_id,
2805 p_site_use_id => p_site_use_id,
2806 p_pay_method_id => NULL,
2807 p_cc_only => p_cc_only,
2808 p_primary => l_primary,
2809 p_check => l_check,
2810 p_as_of_date => TRUNC(p_as_of_date));
2811
2812 IF (NVL(l_cust_pay_method_id,0) > 0) THEN
2813
2814 SELECT receipt_method_id
2815 INTO l_pay_method_id
2816 FROM ra_cust_receipt_methods
2817 WHERE cust_receipt_method_id = l_cust_pay_method_id;
2818 IF G_LOG_YES_NO = 'YES' THEN
2819 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Payment method id is '||l_pay_method_id);
2820 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Customer Payment method id is '||l_cust_pay_method_id);
2821 END IF;
2822 ELSE
2823
2824 l_pay_method_id := to_number(nvl(FND_PROFILE.VALUE_SPECIFIC('OKS_RECEIPT_METHOD_ID', NULL, NULL, NULL, p_org_id, NULL),'0'));
2825
2826 IF l_pay_method_id = 0 THEN
2827 IF G_LOG_YES_NO = 'YES' THEN
2828 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Payment method id is '||l_pay_method_id);
2829 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Enter a Value for the Profile, OKS: Payment Method for AR Interface');
2830 END IF;
2831 l_msg_data:= 'Enter a Value for the Profile, OKS: Payment Method for AR Interface'; --skuchima 10416595
2832 RAISE MAIN_CUR_EXCEPTION;
2833 END IF;
2834
2835 l_cust_pay_method_id := arp_ext_bank_pkg.process_cust_pay_method
2836 (p_pay_method_id => l_pay_method_id,
2837 p_customer_id => p_customer_id,
2838 p_site_use_id => p_site_use_id,
2839 p_as_of_date => TRUNC(p_as_of_date));
2840 IF G_LOG_YES_NO = 'YES' THEN
2841 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Payment method id from Profile OKS: Payment Method for AR Interface is '||l_pay_method_id);
2842 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Customer Payment method id is '||l_cust_pay_method_id);
2843 END IF;
2844 END IF;
2845
2846 RETURN l_pay_method_id;
2847 EXCEPTION
2848 WHEN OTHERS THEN
2849 RAISE;
2850 END;
2851
2852 /* Added by sjanakir for Bug # 6872005 */
2853
2854 FUNCTION check_CC_valid ( p_trxn_extension_id IN NUMBER) RETURN VARCHAR2 IS
2855
2856 /*Commented and added by cgopinee for PA-DSS enhancement*/
2857 l_card_expired_flag VARCHAR2(10);
2858 /*
2859 l_cc_no VARCHAR2(30);
2860 v_clean_cc VARCHAR2(30);
2861 v_cc_type iby_cc_validate.cctype;
2862 v_cc_valid BOOLEAN := TRUE;
2863 l_exp_date DATE;
2864 */
2865
2866 BEGIN
2867 SELECT NVL(card_expired_flag,'N')
2868 INTO l_card_expired_flag
2869 FROM iby_trxn_extensions_v
2870 WHERE trxn_extension_id = p_trxn_extension_id;
2871
2872 /* Commented by cgopinee for PA DSS Enhancement */
2873 /*
2874 BEGIN
2875 SELECT ic.ccnumber,
2876 NVL(ic.expirydate,SYSDATE +1)
2877 INTO l_cc_no,
2878 l_exp_date
2879 FROM iby_creditcard ic,
2880 iby_pmt_instr_uses_all ipa,
2881 iby_fndcpt_tx_extensions ifte
2882 WHERE ifte.trxn_extension_id = p_trxn_extension_id
2883 AND ifte.instr_assignment_id = ipa.instrument_payment_use_id
2884 AND ipa.instrument_id = ic.instrid;
2885 EXCEPTION
2886 WHEN NO_DATA_FOUND THEN
2887 IF G_LOG_YES_NO = 'YES' THEN
2888 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Credit card details not found for Payment Transaction Extension ID');
2889 END IF;
2890 RAISE MAIN_CUR_EXCEPTION;
2891 WHEN OTHERS THEN
2892 IF G_LOG_YES_NO = 'YES' THEN
2893 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Error retrieving Credit card details for Payment Transaction Extension ID');
2894 END IF;
2895 RAISE MAIN_CUR_EXCEPTION;
2896 END;
2897
2898
2899 iby_cc_validate.stripcc( p_api_version => 1.0,
2900 p_init_msg_list => 'T',
2901 p_cc_id => l_cc_no,
2902 x_return_status => l_ret_stat,
2903 x_msg_count => l_msg_cnt,
2904 x_msg_data => l_msg_data,
2905 x_cc_id => v_clean_cc);
2906
2907
2908 IF l_ret_stat = OKC_API.G_RET_STS_SUCCESS
2909 THEN
2910 iby_cc_validate.getcctype( p_api_version => 1.0,
2911 p_init_msg_list => 'T',
2912 p_cc_id => v_clean_cc,
2913 x_return_status => l_ret_stat,
2914 x_msg_count => l_msg_cnt,
2915 x_msg_data => l_msg_data,
2916 x_cc_type => v_cc_type);
2917 IF l_ret_stat = OKC_API.G_RET_STS_SUCCESS
2918 THEN
2919 IF v_cc_type=IBY_CC_VALIDATE.c_InvalidCC THEN
2920 IF G_LOG_YES_NO = 'YES' THEN
2921 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Credit card number Format is invalid.');
2922 END IF;
2923
2924 RAISE MAIN_CUR_EXCEPTION;
2925 ELSE
2926 IF G_LOG_YES_NO = 'YES' THEN
2927 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Credit card number Format is valid.');
2928 END IF;
2929 END IF;
2930
2931 iby_cc_validate.validatecc( p_api_version => 1.0,
2932 p_init_msg_list => 'T',
2933 p_cc_id => v_clean_cc,
2934 p_expr_date => l_exp_date,
2935 x_return_status => l_ret_stat,
2936 x_msg_count => l_msg_cnt,
2937 x_msg_data => l_msg_data,
2938 x_cc_valid => v_cc_valid);
2939
2940 RETURN v_cc_valid;
2941
2942 ELSE
2943
2944 IF G_LOG_YES_NO = 'YES' THEN
2945 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Error retrieving Credit card Type.');
2946 END IF;
2947
2948 RETURN FALSE;
2949
2950 END IF;
2951 ELSE
2952
2953 IF G_LOG_YES_NO = 'YES' THEN
2954 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Error retrieving Credit card number.');
2955 END IF;
2956
2957 RETURN FALSE;
2958
2959 END IF;*/
2960 RETURN l_card_expired_flag;
2961 EXCEPTION
2962 WHEN OTHERS THEN
2963 RETURN 'Y';
2964 END check_CC_valid;
2965
2966 /* Addition by sjanakir for Bug # 6872005 Ends */
2967
2968 BEGIN
2969
2970 /* GET THE CONCURRENT PROCESS ID */
2971
2972 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2973
2974 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2975
2976 --Fix for bug#4198616
2977 --declared global variable G_LOG_YES_NO and using this variable
2978 --in place OKS_BILLING_PUB.l_write_log to decide whether to log
2979 --messages
2980 G_LOG_YES_NO := Fnd_profile.value('OKS_BILLING_REPORT_AND_LOG');
2981
2982 If G_LOG_YES_NO = 'YES' then ---- added by ansraj for 14016663
2983 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => OKS: Billing Report And Log is set to '||G_LOG_YES_NO);
2984 End If;
2985
2986 /*FOR BILLING REPORT*/
2987 l_subline_failed := FALSE;
2988 l_line_failed := FALSE;
2989
2990
2991 IF (p_prv = 1) THEN
2992 OPEN Cur_bcl;
2993 ELSIF (p_prv = 2) THEN
2994 OPEN Cur_bcl_pr;
2995 ELSIF (p_prv = 3) THEN
2996 OPEN Cur_bcl_term;
2997 END IF;
2998
2999 LOOP
3000 IF (p_prv = 1) THEN
3001 FETCH Cur_bcl into bcl_rec;
3002 EXIT WHEN cur_bcl%NOTFOUND;
3003 ELSIF (p_prv = 2) THEN
3004 FETCH Cur_bcl_pr into bcl_rec;
3005 EXIT WHEN cur_bcl_pr%NOTFOUND;
3006 ELSIF (p_prv = 3) THEN
3007 FETCH Cur_bcl_term into bcl_rec;
3008 EXIT WHEN cur_bcl_term%NOTFOUND;
3009 END IF;
3010
3011 BEGIN
3012 DBMS_TRANSACTION.SAVEPOINT('BEFORE_AR_TRANSACTION');
3013 --Fix for bug#4390448
3014 --initializing the variable l_usage_type to NULL
3015 l_usage_type := NULL;
3016
3017 --Fix for bug#4390448
3018 --get the usage type to be used later
3019 l_lse_id := bcl_rec.lse_id;
3020 Open l_usage_csr(bcl_rec.cle_id,Bcl_rec.dnz_chr_id);
3021 Fetch l_usage_csr into l_usage_type;
3022 Close l_usage_csr;
3023 If G_LOG_YES_NO = 'YES' then ---- added by ansraj for 14016663
3024 FND_FILE.PUT_LINE(FND_FILE.LOG,'Usage Type '||l_usage_type);
3025 End If;
3026 -- End for bug#4390448
3027
3028 l_select_counter := l_select_counter + 1;
3029 l_bill_instance_number := NULL;
3030 l_flag:=0; /*modified for bug:8943481*/
3031 OPEN cur_hdr_rules (bcl_rec.cle_id );
3032 FETCH cur_hdr_rules into rul_hdr_rec;
3033 IF cur_hdr_rules%NOTFOUND THEN
3034 l_flag := 1;
3035 END IF;
3036 CLOSE cur_hdr_rules;
3037
3038
3039 If G_LOG_YES_NO = 'YES' then
3040 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => p_prv '||p_prv);
3041 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => rul_hdr_rec.hold_billing_flag '||nvl(rul_hdr_rec.hold_billing_flag,'N'));
3042 End If;
3043
3044
3045 IF (nvl(rul_hdr_rec.hold_billing_flag,'N') = 'Y') THEN
3046 OPEN ar_date_cur(bcl_rec.id);
3047 FETCH ar_date_cur into l_ar_date;
3048 CLOSE ar_date_cur;
3049 ELSE
3050 l_ar_date := NULL; --sysdate;
3051 END IF;
3052
3053 /*****
3054 IF ((bcl_rec.bill_action not in ('AV','TR','STR'))OR
3055 ((bcl_rec.bill_action in ('AV','TR','STR')) AND
3056 ****/
3057 --BUG FIX 3450592 . added nvl in IF clause
3058 IF ((bcl_rec.bill_action not in ('TR','STR'))OR
3059 ((bcl_rec.bill_action in ('TR','STR')) AND
3060 (nvl(trunc(l_ar_date),trunc(p_date)) <= trunc(p_date))
3061 AND (rul_hdr_rec.ar_interface_yn ='Y') and l_flag = 0)) THEN /* Added By sjanakir for Bug # 6821826 */ /*modified for bug:8943481*/
3062
3063
3064 If G_LOG_YES_NO = 'YES' then
3065 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Processing Line : '|| Bcl_rec.cle_id);
3066 End If;
3067
3068 OPEN Cur_K_headers(Bcl_rec.dnz_chr_id);
3069 FETCH Cur_k_headers into header_rec;
3070 CLOSE Cur_k_headers;
3071
3072
3073 OPEN cur_k_lines(bcl_rec.cle_id);
3074 FETCH cur_k_lines into lines_rec;
3075 CLOSE cur_k_lines;
3076
3077 --Insert Into OKS_BILL_TRANSACTIONS
3078 IF ((p_prv = 1) OR (p_prv = 3)) THEN
3079 l_btnv_tbl_in(1).CURRENCY_CODE := nvl(bcl_rec.CURRENCY_CODE,header_rec.CURRENCY_CODE);
3080 l_btnv_tbl_in(1).trx_number := '-99';
3081
3082 OKS_BILLTRAN_PUB.insert_Bill_Tran_Pub
3083 (
3084 p_api_version => 1.0,
3085 p_init_msg_list => 'T',
3086 x_return_status => l_ret_stat,
3087 x_msg_count => l_msg_cnt,
3088 x_msg_data => l_msg_data,
3089 p_btnv_tbl => l_btnv_tbl_in,
3090 x_btnv_tbl => l_btnv_tbl_out
3091 );
3092 If G_LOG_YES_NO = 'YES' then
3093 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After calling OKS_BILLTRAN_PUB.insert_Bill_Tran_Pub l_ret_status'||l_ret_stat);
3094 End If;
3095
3096 ELSIF (p_prv = 2) THEN
3097 l_btn_pr_tbl_in(1).ID :=oks_bill_rec_pub.get_Seq_id;
3098 l_btn_pr_tbl_in(1).CURRENCY_CODE :=nvl(bcl_rec.CURRENCY_CODE,header_rec.CURRENCY_CODE);
3099 l_btn_pr_tbl_in(1).TRX_NUMBER := '-99';
3100 l_btn_pr_tbl_in(1).TRX_AMOUNT := NULL;
3101 l_btn_pr_tbl_in(1).TRX_CLASS := NULL;
3102 l_btn_pr_tbl_in(1).OBJECT_VERSION_NUMBER:= 1.0;
3103 l_btn_pr_tbl_in(1).CREATED_BY := FND_GLOBAL.user_id;
3104 l_btn_pr_tbl_in(1).CREATION_DATE := sysdate;
3105 l_btn_pr_tbl_in(1).LAST_UPDATED_BY := FND_GLOBAL.user_id;
3106 l_btn_pr_tbl_in(1).LAST_UPDATE_DATE := sysdate;
3107 l_btn_pr_tbl_in(1).TRX_DATE := sysdate;
3108 l_btn_pr_tbl_in(1).LAST_UPDATE_LOGIN := FND_GLOBAL.user_id;
3109 l_btn_pr_tbl_in(1).SECURITY_GROUP_ID := NULL;
3110
3111 OKS_BILLTRAN_PRV_PUB.insert_btn_pr
3112 (
3113 p_api_version => 1.0 ,
3114 p_init_msg_list => 'T',
3115 x_return_status => l_ret_stat ,
3116 x_msg_count => l_msg_count,
3117 x_msg_data => l_msg_data ,
3118 p_btn_pr_tbl => l_btn_pr_tbl_in ,
3119 x_btn_pr_tbl => l_btn_pr_tbl_out
3120 );
3121
3122 END IF;
3123
3124 IF (l_ret_stat = 'S') THEN
3125
3126 IF ((p_prv = 1) OR (p_prv = 3)) THEN
3127
3128 --Update OKS_BILL_CONT_LINES with the BTN ID
3129
3130 UPDATE oks_bill_cont_lines
3131 SET btn_id = l_btnv_tbl_out(1).id
3132 WHERE id = bcl_rec.id ;
3133
3134 If G_LOG_YES_NO = 'YES' then
3135 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Updating btn_id of oks_bill_cont_lines');
3136 End If;
3137
3138 l_btlv_tbl_in(1).btn_id := l_btnv_tbl_out(1).id;
3139 l_btlv_tbl_in(1).bcl_id := bcl_rec.id;
3140
3141 ELSIF (p_prv = 2) THEN
3142 UPDATE oks_bcl_pr
3143 SET BTN_ID = l_btn_pr_tbl_in(1).ID
3144 WHERE ID = bcl_rec.id;
3145
3146 l_btl_pr_tbl_in(1).BTN_ID := l_btn_pr_tbl_in(1).ID;
3147 l_btl_pr_tbl_in(1).BCL_ID := Bcl_rec.id;
3148 END IF;
3149 ELSE -- (l_ret_stat = 'S')
3150 If G_LOG_YES_NO = 'YES' then
3151 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into Bill_Transactions Failed For Line : '||Bcl_rec.cle_id);
3152 End If;
3153
3154 oks_bill_rec_pub.get_message(
3155 l_msg_cnt => l_msg_cnt,
3156 l_msg_data => l_msg_data);
3157 IF (P_PRV <> 3) THEN
3158 x_msg_count := l_msg_cnt;
3159 x_msg_data := l_msg_data;
3160 END IF;
3161
3162 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
3163 RAISE MAIN_CUR_EXCEPTION;
3164 END IF; -- (l_ret_stat = 'S')
3165
3166
3167 -- g_rail_rec.set_of_books_id is set in Set_attributes
3168
3169 /* Initialize AR Record */
3170 G_RAIL_REC.ACCOUNTING_RULE_DURATION := NULL;
3171 G_RAIL_REC.ACCOUNTING_RULE_ID := NULL;
3172 G_RAIL_REC.CUSTOMER_BANK_ACCOUNT_ID := NULL;
3173 G_RAIL_REC.RECEIPT_METHOD_NAME := NULL;
3174 G_RAIL_REC.RECEIPT_METHOD_ID := NULL;
3175 G_RAIL_REC.TRX_DATE := NULL;
3176 G_RAIL_REC.REFERENCE_LINE_ID := NULL;
3177 G_RAIL_REC.ORIG_SYSTEM_BILL_CUSTOMER_ID := NULL;
3178 G_RAIL_REC.ORIG_SYSTEM_BILL_ADDRESS_ID := NULL;
3179 G_RAIL_REC.ORIG_SYSTEM_SOLD_CUSTOMER_ID := NULL;
3180 G_RAIL_REC.ORIG_SYSTEM_SHIP_CUSTOMER_ID := NULL;
3181 G_RAIL_REC.ORIG_SYSTEM_SHIP_ADDRESS_ID := NULL;
3182 G_RAIL_REC.CONVERSION_TYPE := 'User';
3183 G_RAIL_REC.CONVERSION_DATE := NULL;
3184 G_RAIL_REC.CONVERSION_RATE := 1;
3185 G_RAIL_REC.GL_DATE := NULL;
3186 G_RAIL_REC.TERM_ID := NULL;
3187 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3188 G_RAIL_REC.TAX_EXEMPT_FLAG := NULL;
3189 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3190 G_RAIL_REC.TAX_CODE := NULL;
3191 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := NULL;
3192 G_RAIL_REC.CREDIT_METHOD_FOR_INSTALLMENTS := NULL;
3193 G_RAIL_REC.ORIG_SYSTEM_BILL_CONTACT_ID := NULL;
3194 G_RAIL_REC.ORIG_SYSTEM_SHIP_CONTACT_ID := NULL;
3195 G_RAIL_REC.AGREEMENT_ID := NULL;
3196 G_RAIL_REC.TRANSLATED_DESCRIPTION := NULL;
3197 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE9 := NULL;
3198 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE10 := NULL;
3199 G_RAIL_REC.QUANTITY := NULL;
3200 G_RAIL_REC.QUANTITY_ORDERED := NULL;
3201 G_RAIL_REC.UNIT_SELLING_PRICE := NULL;
3202 G_RAIL_REC.INVOICING_RULE_ID := NULL;
3203 G_RAIL_REC.CUST_TRX_TYPE_ID := NULL;
3204 G_RAIL_REC.SET_OF_BOOKS_ID := NULL;
3205
3206 G_RAIL_REC.reference_line_attribute1 := NULL;
3207 G_RAIL_REC.reference_line_attribute2 := NULL;
3208 G_RAIL_REC.reference_line_attribute3 := NULL;
3209 G_RAIL_REC.reference_line_attribute4 := NULL;
3210 G_RAIL_REC.reference_line_attribute5 := NULL;
3211 G_RAIL_REC.reference_line_attribute6 := NULL;
3212 G_RAIL_REC.reference_line_attribute7 := NULL;
3213 G_RAIL_REC.reference_line_attribute8 := NULL;
3214 G_RAIL_REC.reference_line_attribute9 := NULL;
3215 G_RAIL_REC.reference_line_attribute10 := NULL;
3216 G_RAIL_REC.reference_line_context := NULL;
3217 G_RAIL_REC.deferral_exclusion_flag := NULL;
3218 G_RAIL_REC.parent_line_id := NULL;
3219 G_RAIL_REC.PAYMENT_TRXN_EXTENSION_ID := NULL;
3220 G_RAIL_REC.contract_line_id := NULL;
3221 G_RAIL_REC.SALES_ORDER_LINE := NULL;
3222 G_RAIL_REC.PRIMARY_SALESREP_ID := NULL;
3223
3224 l_line_payment_mth := NULL;
3225 l_hdr_sbg_object1_id1 := NULL;
3226 l_acct_calender := NULL;
3227 l_acct_frequency := NULL;
3228 l_num_periods := NULL;
3229
3230
3231 -- -- Rules for The Contract Header
3232
3233 IF (rul_hdr_rec.inv_rule_id IS NOT NULL) THEN
3234 IF (bcl_rec.bill_action in ('TR','STR')) THEN
3235 G_RAIL_REC.INVOICING_RULE_ID := NULL;
3236 ELSE
3237 G_RAIL_REC.INVOICING_RULE_ID := rul_hdr_rec.inv_rule_id;
3238 END IF;
3239 G_RAIL_REC.TRX_DATE := bcl_rec.date_next_invoice;
3240 END IF;
3241
3242 l_hdr_sbg_object1_id1 := rul_hdr_rec.inv_trx_type;
3243 l_hdr_summary_flag := rul_hdr_rec.summary_trx_yn;
3244
3245 G_RAIL_REC.SET_OF_BOOKS_ID := get_set_of_books_id (Bcl_rec.dnz_chr_id);
3246
3247 Set_cust_trx_type(G_RAIL_REC.set_of_books_id,
3248 bcl_rec.bill_action,
3249 l_hdr_sbg_object1_id1,
3250 header_rec.org_id);
3251 If G_LOG_YES_NO = 'YES' then
3252 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Calling Set_cust_trx_type '||G_RAIL_REC.CUST_TRX_TYPE_ID);
3253 End If;
3254
3255 IF (rul_hdr_rec.acct_rule_id IS NOT NULL) THEN
3256 OPEN acct_type(rul_hdr_rec.acct_rule_id);
3257 FETCH acct_type into l_type,l_acct_frequency;
3258 CLOSE acct_type;
3259
3260 IF (l_type = 'ACC_DUR') THEN
3261
3262 l_acct_calender := get_acct_calender(G_RAIL_REC.SET_OF_BOOKS_ID);
3263
3264 l_start_date := Greatest(Bcl_rec.date_billeD_from,lines_rec.start_date);
3265 l_End_date := Least(Bcl_rec.date_billed_to,lines_rec.End_date);
3266 l_End_date := l_End_date + 1;
3267
3268 --G_RAIL_REC.ACCOUNTING_RULE_DURATION := CEIL(MONTHS_BETWEEN(l_End_date,l_start_date));
3269
3270 GL_CALENDAR_PKG.get_num_periods_in_date_range(
3271 CALENDAR_NAME => l_acct_calender,
3272 PERIOD_TYPE => l_acct_frequency,
3273 START_DATE => l_start_date,
3274 END_DATE => l_end_date,
3275 CHECK_MISSING => FALSE,
3276 NUM_PERIODS => l_num_periods,
3277 RETURN_CODE => l_ret_stat,
3278 UNMAPPED_DATE => l_unmapped_date);
3279
3280 If G_LOG_YES_NO = 'YES' then
3281 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Calling GL_CALENDAR_PKG.get_num_periods_in_date_range l_return_status '||l_ret_stat);
3282 End If;
3283
3284 G_RAIL_REC.ACCOUNTING_RULE_DURATION := l_num_periods;
3285 -- This column must be a positive integer for AR
3286
3287 IF (G_RAIL_REC.ACCOUNTING_RULE_DURATION = 0) THEN
3288 G_RAIL_REC.ACCOUNTING_RULE_DURATION := 1;
3289 END IF;
3290 ELSE
3291 G_RAIL_REC.ACCOUNTING_RULE_DURATION := NULL ;
3292 END IF;
3293
3294 G_RAIL_REC.ACCOUNTING_RULE_ID := rul_hdr_rec.acct_rule_id;
3295
3296 ----IF ( bcl_rec.bill_action in ('AV','TR','STR')) THEN
3297 IF ( bcl_rec.bill_action in ('TR','STR')) THEN
3298 G_RAIL_REC.ACCOUNTING_RULE_ID := NULL;
3299 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'UNIT';
3300 G_RAIL_REC.CREDIT_METHOD_FOR_INSTALLMENTS := 'LIFO';
3301 ELSE
3302 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := NULL;
3303 G_RAIL_REC.CREDIT_METHOD_FOR_INSTALLMENTS := NULL;
3304 END IF;
3305
3306 END IF;
3307
3308 -----(bcl_rec.bill_action not in ('AV','TR','STR'))) THEN
3309 IF ((rul_hdr_rec.payment_term_id is NOT NULL) AND
3310 (bcl_rec.bill_action not in ('TR','STR'))) THEN
3311 G_RAIL_REC.TERM_ID := rul_hdr_rec.payment_term_id;
3312 END IF;
3313
3314 --Start fixes of bug#4756579
3315 --Rewritten the tax code for R12 eBTax uptake
3316 --For exemptions:
3317 --(1)if exemption_certificate_number is populated, use that
3318 --(2)otherwise use tax_exemption_id to query exempt_certificate_number
3319 --and exempt_reasion_code from zx_exemptions
3320
3321 IF (rul_hdr_rec.tax_status IS NOT NULL) THEN
3322 G_RAIL_REC.TAX_EXEMPT_FLAG := rul_hdr_rec.tax_status;
3323 -- added cur_date_trans cursor for tax exemption and modified the if condition to
3324 -- pass null if exemption id is invalid
3325 IF G_RAIL_REC.TAX_EXEMPT_FLAG = 'E' and
3326 rul_hdr_rec.exempt_certificate_number IS NULL THEN
3327 --historical contracts
3328 OPEN Cur_tax(rul_hdr_rec.tax_exemption_id,Bcl_rec.DATE_NEXT_INVOICE);
3329 FETCH Cur_tax into tax_rec;
3330 IF cur_tax%notfound then
3331 --exemption is not valid for the date
3332 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3333 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3334 --reset the tax_exempt_flag to 'S'
3335 G_RAIL_REC.TAX_EXEMPT_FLAG := 'S';
3336 ELSE
3337 If G_LOG_YES_NO = 'YES' then
3338 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB. Exempt numer '||tax_rec.exempt_certificate_number||' Reason '||tax_rec.exempt_reason_code);
3339 End If;
3340 G_RAIL_REC.TAX_EXEMPT_NUMBER := tax_rec.exempt_certificate_number;
3341 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := tax_rec.exempt_reason_code;
3342 END IF;
3343
3344 CLOSE cur_tax;
3345 Elsif G_RAIL_REC.TAX_EXEMPT_FLAG = 'E' and
3346 rul_hdr_rec.exempt_certificate_number IS NOT NULL THEN
3347 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := Rul_hdr_rec.exempt_reason_code;
3348 G_RAIL_REC.TAX_EXEMPT_NUMBER := Rul_hdr_rec.exempt_certificate_number;
3349
3350 Elsif G_RAIL_REC.TAX_EXEMPT_FLAG = 'R' Then --- Added this for bug 5600680
3351 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3352 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3353 Elsif G_RAIL_REC.TAX_EXEMPT_FLAG <> 'E' Then
3354 G_RAIL_REC.TAX_EXEMPT_FLAG := 'S';
3355 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3356 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3357 END IF; --IF (G_RAIL_REC.TAX_EXEMPT_FLAG = 'E')
3358
3359 END IF; --IF (rul_hdr_rec.tax_status IS NOT NULL)
3360 --End fixes of bug#4756579
3361
3362 IF (rul_hdr_rec.conversion_type IS NOT NULL) THEN
3363 /*
3364 conversion type cannot be null and hence nvl criteria
3365 is used for assignment of conversion_type
3366 */
3367 G_RAIL_REC.CONVERSION_TYPE :=nvl(rul_hdr_rec.conversion_type ,'User');
3368
3369 /* This if is included to populated rate = 1
3370 if type is user.This is important if the
3371 functional currency is same as transactional
3372 currency. It that case the control will not go
3373 in below if stat
3374 */
3375
3376 IF (G_RAIL_REC.CONVERSION_TYPE = 'User') THEN
3377 G_RAIL_REC.CONVERSION_RATE := 1;
3378 ELSE
3379 G_RAIL_REC.CONVERSION_RATE := NULL;
3380 END IF;
3381
3382 /* If functional currency is not the same
3383 as transactional currency the assignment of
3384 rate use the below logic
3385 */
3386 IF (okc_currency_api.get_ou_currency(header_rec.org_id) <> header_rec.currency_code ) THEN
3387 IF ( G_RAIL_REC.CONVERSION_TYPE = 'User') THEN
3388 G_RAIL_REC.CONVERSION_RATE :=nvl(rul_hdr_rec.conversion_rate,1);
3389 ELSE
3390 G_RAIL_REC.CONVERSION_RATE := NULL;
3391 END IF;
3392 END IF;
3393
3394 /*cgopinee bugfix for 8361496*/
3395 IF (Rul_hdr_rec.conversion_rate_date IS NULL) OR
3396 (FND_PROFILE.VALUE('OKS_CURR_CONV_DATE') = 'SYSDATE' AND G_RAIL_REC.CONVERSION_TYPE <> 'User' )THEN
3397 G_RAIL_REC.CONVERSION_DATE := sysdate;
3398 ELSE
3399 G_RAIL_REC.CONVERSION_DATE := Rul_hdr_rec.conversion_rate_date;
3400 --G_RAIL_REC.CONVERSION_DATE := to_date(Rules_rec.Rule_information2,'YYYY/MM/DD HH24:MI:SS');
3401 END IF;
3402
3403 END IF;
3404
3405
3406 IF (rul_hdr_rec.bill_to_site_use_id IS NOT NULL) THEN
3407 OPEN Cur_ADDRESS_BILLTO(rul_hdr_rec.bill_to_site_use_id,'BILL_TO');
3408 FETCH Cur_address_billto into billto_rec;
3409 CLOSE Cur_ADDRESS_BILLTO;
3410
3411 l_cust_id := billto_rec.cust_account_id;
3412 l_site_use_id := billto_rec.id1;
3413
3414 G_RAIL_REC.ORIG_SYSTEM_BILL_CUSTOMER_ID := billto_rec.cust_account_id;
3415 G_RAIL_REC.ORIG_SYSTEM_BILL_ADDRESS_ID:= billto_rec.cust_acct_site_id;
3416 G_RAIL_REC.ORIG_SYSTEM_SOLD_CUSTOMER_ID := billto_rec.cust_account_id;
3417
3418 END IF;
3419
3420 IF (rul_hdr_rec.ship_to_site_use_id IS NOT NULL) THEN
3421 OPEN Cur_ADDRESS_BILLTO(rul_hdr_rec.ship_to_site_use_id,'SHIP_TO');
3422 FETCH Cur_address_billto into billto_rec;
3423 CLOSE Cur_ADDRESS_BILLTO;
3424
3425 G_RAIL_REC.ORIG_SYSTEM_SHIP_CUSTOMER_ID:= billto_rec.cust_account_id;
3426 G_RAIL_REC.ORIG_SYSTEM_SHIP_ADDRESS_ID:=billto_rec.cust_acct_site_id;
3427 END IF;
3428
3429 ------END OF HEADER RULE READING -------------------
3430
3431 If G_LOG_YES_NO = 'YES' then
3432 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => End Of Header Rule Processing And Beginning Of Line Rule processing');
3433 End If;
3434
3435 ------LINE RULE READ --------------------------------
3436
3437 OPEN cur_line_rules (bcl_rec.cle_id );
3438 FETCH cur_line_rules into rul_line_rec;
3439 CLOSE cur_line_rules;
3440
3441 IF (rul_line_rec.bill_to_site_use_id IS NOT NULL) THEN
3442 OPEN cur_address_billto(rul_line_rec.bill_to_site_use_id,'BILL_TO');
3443 FETCH cur_address_billto into billto_rec;
3444 CLOSE cur_address_billto;
3445
3446 G_RAIL_REC.ORIG_SYSTEM_BILL_CUSTOMER_ID:= billto_rec.cust_account_id;
3447 G_RAIL_REC.ORIG_SYSTEM_BILL_ADDRESS_ID:=billto_rec.cust_acct_site_id;
3448 G_RAIL_REC.ORIG_SYSTEM_SOLD_CUSTOMER_ID:= billto_rec.cust_account_id;
3449
3450 END IF;
3451
3452 IF (rul_line_rec.ship_to_site_use_id IS NOT NULL) THEN
3453 OPEN cur_address_billto(rul_line_rec.ship_to_site_use_id,'SHIP_TO');
3454 FETCH Cur_address_billto into billto_rec;
3455 CLOSE cur_address_billto;
3456
3457 G_RAIL_REC.ORIG_SYSTEM_SHIP_CUSTOMER_ID:= billto_rec.cust_account_id;
3458 G_RAIL_REC.ORIG_SYSTEM_SHIP_ADDRESS_ID:=billto_rec.cust_acct_site_id;
3459
3460 END IF;
3461
3462
3463 --For exemptions:
3464 --(1)if exemption_certificate_number is populated, use that
3465 --(2)otherwise use tax_exemption_id to query exempt_certificate_number
3466 --and exempt_reasion_code from zx_exemptions
3467
3468 IF ( Rul_line_rec.tax_status IS NOT NULL OR
3469 Rul_line_rec.tax_classification_code IS NOT NULL OR
3470 Rul_line_rec.tax_code IS NOT NULL ) Then
3471
3472 G_RAIL_REC.TAX_EXEMPT_FLAG :=
3473 nvl(Rul_line_rec.tax_status,'S');
3474
3475 IF G_RAIL_REC.TAX_EXEMPT_FLAG = 'R' Then
3476 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3477 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3478 ElsIF G_RAIL_REC.TAX_EXEMPT_FLAG <> 'E' Then
3479 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3480 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3481 G_RAIL_REC.TAX_EXEMPT_FLAG := 'S';
3482 ElsIF Rul_line_rec.tax_exemption_id IS NOT NULL THEN
3483 --historical contracts
3484 OPEN Cur_tax(Rul_line_rec.tax_exemption_id,Bcl_rec.DATE_NEXT_INVOICE);
3485 FETCH Cur_tax into tax_rec;
3486 IF cur_tax%notfound then
3487 --exemption is not valid for the date
3488 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3489 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3490 --reset the tax_exempt_flag to 'S'
3491 G_RAIL_REC.TAX_EXEMPT_FLAG := 'S';
3492
3493 ELSE
3494 If G_LOG_YES_NO = 'YES' then
3495 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB. Exempt numer '||tax_rec.exempt_certificate_number||' Reason '||tax_rec.exempt_reason_code);
3496 End If;
3497 G_RAIL_REC.TAX_EXEMPT_NUMBER := tax_rec.exempt_certificate_number;
3498 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := tax_rec.exempt_reason_code;
3499 END IF;
3500
3501 CLOSE cur_tax;
3502 ElsIF
3503 Rul_line_rec.exempt_certificate_number IS NOT NULL THEN
3504 G_RAIL_REC.TAX_EXEMPT_NUMBER := Rul_line_rec.exempt_certificate_number;
3505 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := Rul_line_rec.exempt_reason_code;
3506
3507
3508 OPEN tax_info_csr(Rul_line_rec.ship_to_site_use_id);
3509 FETCH tax_info_csr INTO l_bill_to_party_id, l_ship_to_party_site_id;
3510 CLOSE tax_info_csr;
3511
3512 OPEN tax_info_csr(Rul_line_rec.bill_to_site_use_id);
3513 FETCH tax_info_csr INTO l_bill_to_party_id, l_bill_to_party_site_id;
3514 CLOSE tax_info_csr;
3515
3516 OPEN Cur_Batch_Source_Id(rul_hdr_rec.org_id);
3517 FETCH Cur_Batch_Source_Id INTO l_batch_source_id;
3518 CLOSE Cur_Batch_Source_Id;
3519
3520
3521 If G_LOG_YES_NO = 'YES' then
3522 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB. ZX API '||G_RAIL_REC.tax_exempt_number||' rsn '||G_RAIL_REC.tax_exempt_reason_code||' ship '||Rul_line_rec.ship_to_site_use_id);
3523 FND_FILE.PUT_LINE(FND_FILE.LOG,'ZX API '||Rul_line_rec.bill_to_site_use_id||' cust '||Rul_line_rec.cust_acct_id||' s site '||l_ship_to_party_site_id||' b site '||l_bill_to_party_site_id);
3524 FND_FILE.PUT_LINE(FND_FILE.LOG,'ZX API trx'||G_RAIL_REC.CUST_TRX_TYPE_ID||' Batch '||l_batch_source_id||' date '||Bcl_rec.DATE_NEXT_INVOICE);
3525 end if;
3526
3527
3528 ZX_TCM_VALIDATE_EXEMPT_PKG.VALIDATE_TAX_EXEMPTIONS
3529 (p_tax_exempt_number => G_RAIL_REC.tax_exempt_number,
3530 p_tax_exempt_reason_code => G_RAIL_REC.tax_exempt_reason_code,
3531 p_ship_to_org_id => Rul_line_rec.ship_to_site_use_id,
3532 p_invoice_to_org_id => Rul_line_rec.bill_to_site_use_id,
3533 p_bill_to_cust_account_id => Rul_line_rec.cust_acct_id,
3534 p_ship_to_party_site_id => l_ship_to_party_site_id,
3535 p_bill_to_party_site_id => l_bill_to_party_site_id,
3536 p_org_id => rul_hdr_rec.org_id,
3537 p_bill_to_party_id => l_bill_to_party_id,
3538 p_legal_entity_id => NULL,
3539 p_trx_type_id => G_RAIL_REC.CUST_TRX_TYPE_ID,
3540 p_batch_source_id => l_batch_source_id,
3541 p_trx_date => Bcl_rec.DATE_NEXT_INVOICE,
3542 p_exemption_status => 'PMU', --fix bug 4766994
3543 x_valid_flag => l_valid_flag,
3544 x_return_status => l_ret_stat,
3545 x_msg_count => l_msg_count,
3546 x_msg_data => l_msg_data);
3547
3548
3549 if l_valid_flag <> 'Y' Then
3550 If G_LOG_YES_NO = 'YES' then
3551 FND_FILE.PUT_LINE(FND_FILE.LOG,'After call to ZX API return status '||l_valid_flag||' '||rul_line_rec.exempt_certificate_number||' date '||Bcl_rec.DATE_NEXT_INVOICE);
3552 End If;
3553 --reset the tax_exempt_flag to 'S'
3554 G_RAIL_REC.TAX_EXEMPT_FLAG := 'S';
3555 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3556 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3557 END IF;
3558
3559 ELSE
3560 G_RAIL_REC.TAX_EXEMPT_FLAG := 'S';
3561 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3562 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3563 END IF;
3564
3565 ELSE
3566 If G_RAIL_REC.TAX_EXEMPT_FLAG is NULL Then --- should not override header
3567 G_RAIL_REC.TAX_EXEMPT_FLAG := 'S';
3568 G_RAIL_REC.TAX_EXEMPT_NUMBER := NULL;
3569 G_RAIL_REC.TAX_EXEMPT_REASON_CODE := NULL;
3570 End If;
3571
3572 END IF;---- IF (Rul_line_rec.tax_status IS NOT NULL OR Rul_line_rec.tax_classification_code is not null)
3573
3574 --For tax classification code:
3575 --(1)if tax_classification_code is not null, use that
3576 --(2)otherwise use the old tax_code and query tax_classification_code
3577 --from zx_id_tcc_mapping
3578 IF (Rul_line_rec.tax_classification_code IS NOT NULL) THEN
3579 --new contracts
3580 G_RAIL_REC.TAX_CODE := Rul_line_rec.tax_classification_code;
3581 If G_LOG_YES_NO = 'YES' then
3582 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB. classification code '||G_RAIL_REC.TAX_CODE);
3583 End If;
3584 ELSIF Rul_line_rec.tax_code IS NOT NULL THEN
3585 --old contracts
3586 OPEN cur_tax_code(Rul_line_rec.tax_code, rul_hdr_rec.org_id);
3587 FETCH cur_tax_code into G_RAIL_REC.TAX_CODE;
3588 CLOSE cur_tax_code;
3589 If G_LOG_YES_NO = 'YES' then
3590 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB. derived from tax code '||G_RAIL_REC.TAX_CODE);
3591 End If;
3592
3593 ELSE
3594 G_RAIL_REC.TAX_CODE := NULL;
3595 END IF; -- IF (Rul_line_rec.tax_classification_code IS NOT NULL)
3596 --End fixes of eBtyax uptake bug#4756579
3597
3598 -----IF (bcl_rec.bill_action in ('AV','TR','STR')) THEN
3599 IF (rul_line_rec.inv_rule_id IS NOT NULL) THEN
3600 IF (bcl_rec.bill_action in ('TR','STR')) THEN
3601 G_RAIL_REC.INVOICING_RULE_ID := NULL;
3602 ELSE
3603 G_RAIL_REC.INVOICING_RULE_ID := Rul_line_rec.inv_rule_id;
3604 END IF;
3605 G_RAIL_REC.TRX_DATE := Bcl_rec.DATE_NEXT_INVOICE;
3606 END IF;
3607
3608 IF (rul_line_rec.cust_acct_id IS NOT NULL) THEN
3609 G_RAIL_REC.ORIG_SYSTEM_SOLD_CUSTOMER_ID := Rul_line_rec.cust_acct_id;
3610 END IF;
3611
3612
3613 IF (Rul_line_rec.acct_rule_id is NOT NULL) THEN
3614 OPEN acct_type(rul_line_rec.acct_rule_id);
3615 FETCH acct_type into l_type,l_acct_frequency;
3616 CLOSE acct_type;
3617
3618 IF (l_type = 'ACC_DUR') THEN
3619 l_acct_calender := get_acct_calender(G_RAIL_REC.SET_OF_BOOKS_ID);
3620
3621 l_start_date:=Greatest(Bcl_rec.date_billeD_from,lines_rec.start_date);
3622 l_End_date := Least(Bcl_rec.date_billed_to,lines_rec.End_date);
3623 l_End_date := l_End_date + 1;
3624
3625 --G_RAIL_REC.ACCOUNTING_RULE_DURATION := CEIL(MONTHS_BETWEEN(l_End_date,l_start_date));
3626
3627 GL_CALENDAR_PKG.get_num_periods_in_date_range(
3628 CALENDAR_NAME => l_acct_calender,
3629 PERIOD_TYPE => l_acct_frequency,
3630 START_DATE => l_start_date,
3631 END_DATE => l_end_date,
3632 CHECK_MISSING => FALSE,
3633 NUM_PERIODS => l_num_periods,
3634 RETURN_CODE => l_ret_stat,
3635 UNMAPPED_DATE => l_unmapped_date);
3636
3637 G_RAIL_REC.ACCOUNTING_RULE_DURATION := l_num_periods;
3638
3639 -- This column must be a positive integer for AR
3640 IF (G_RAIL_REC.ACCOUNTING_RULE_DURATION = 0) THEN
3641 G_RAIL_REC.ACCOUNTING_RULE_DURATION := 1;
3642 END IF;
3643 ELSE
3644 G_RAIL_REC.ACCOUNTING_RULE_DURATION := NULL ;
3645 END IF;
3646
3647 G_RAIL_REC.ACCOUNTING_RULE_ID := Rul_line_rec.acct_rule_id;
3648
3649 ---IF (bcl_rec.bill_action in ('AV','TR','STR')) THEN
3650 IF (bcl_rec.bill_action in ('TR','STR')) THEN
3651 G_RAIL_REC.ACCOUNTING_RULE_ID := NULL;
3652 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'UNIT';
3653 G_RAIL_REC.CREDIT_METHOD_FOR_INSTALLMENTS := 'LIFO';
3654 ELSE
3655 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := NULL;
3656 G_RAIL_REC.CREDIT_METHOD_FOR_INSTALLMENTS := NULL;
3657 END IF;
3658
3659 END IF;
3660
3661 ------END OF LINE RULE READING --------------------------------
3662
3663
3664
3665 If G_LOG_YES_NO = 'YES' then
3666 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Done with processing line rules');
3667 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Updated all G_RAIL Field');
3668 End If;
3669
3670
3671
3672 Set_aggrement_and_contacts(bcl_rec.dnz_chr_id,
3673 bcl_rec.cle_id,
3674 bcl_rec.date_billed_from,
3675 bcl_rec.bill_action);
3676 If G_LOG_YES_NO = 'YES' then
3677 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Calling Set_aggrement_and_contacts');
3678 End If;
3679
3680 Set_comments(bcl_rec.bill_action);
3681 If G_LOG_YES_NO = 'YES' then
3682 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Calling Set_comments ');
3683 End If;
3684
3685 Set_attributes(rul_hdr_rec.contract_number,
3686 rul_hdr_rec.contract_number_modifier,
3687 bcl_rec.date_billed_From,
3688 bcl_rec.date_billed_to,
3689 bcl_rec.start_date,
3690 bcl_rec.cle_id,
3691 rul_hdr_rec.attribute_category,
3692 rul_hdr_rec.attribute1,
3693 rul_hdr_rec.attribute2,
3694 rul_hdr_rec.attribute3,
3695 rul_hdr_rec.attribute4,
3696 rul_hdr_rec.attribute5,
3697 rul_hdr_rec.attribute6,
3698 rul_hdr_rec.attribute7,
3699 rul_hdr_rec.attribute8,
3700 rul_hdr_rec.attribute9,
3701 rul_hdr_rec.attribute10,
3702 rul_hdr_rec.attribute11,
3703 rul_hdr_rec.attribute12,
3704 rul_hdr_rec.attribute13,
3705 rul_hdr_rec.attribute14,
3706 rul_hdr_rec.attribute15,
3707 nvl(bcl_rec.currency_code,rul_hdr_rec.currency_code),
3708 rul_hdr_rec.cust_po_number,
3709 bcl_rec.dnz_chr_id,
3710 rul_hdr_rec.org_id,
3711 l_ret_stat
3712 );
3713
3714 -- Added by sjanakir for Bug#6524778
3715 IF l_type IN ('ACC_DUR','A') THEN
3716 G_RAIL_REC.RULE_END_DATE := NULL;
3717 END IF;
3718 -- Addition Ends
3719
3720 If G_LOG_YES_NO = 'YES' then
3721 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Calling Set_attributes ');
3722 End If;
3723
3724
3725 G_RAIL_REC.sales_order_line := bcl_rec.top_line_number;
3726 G_RAIL_REC.contract_line_id := bcl_rec.cle_id;
3727
3728 If G_LOG_YES_NO = 'YES' then
3729 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After setting line number ');
3730 End If;
3731
3732 Set_gl_date ( p_cle_id => bcl_rec.cle_id,
3733 p_date_billed_from => to_date(G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE7,'YYYY/MM/DD HH24:MI:SS'),
3734 p_bill_action => bcl_rec.bill_action);
3735
3736 If G_LOG_YES_NO = 'YES' then
3737 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Calling Set_gl_date ');
3738 End If;
3739
3740
3741 IF (l_ret_stat <> 'S') THEN
3742 IF (P_PRV <> 3) THEN
3743 x_msg_count := l_msg_cnt;
3744 x_msg_data := l_msg_data;
3745 END IF;
3746
3747 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
3748 RAISE MAIN_CUR_EXCEPTION;
3749
3750 END IF;
3751
3752
3753 G_RAIL_REC.reference_line_id := NULL;
3754 G_RAIL_REC.payment_trxn_extension_id := NULL;
3755 G_RAIL_REC.receipt_method_id := NULL;
3756
3757 IF (P_PRV = 1 ) Then
3758
3759 If G_LOG_YES_NO = 'YES' then
3760 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Payment method is '||rul_hdr_rec.payment_type);
3761 End If;
3762
3763 IF nvl(rul_hdr_rec.payment_type,'XX') = 'COM' and bcl_rec.trxn_extension_id is NULL then
3764
3765 /*Added for bug 14337674 */
3766 l_com_date := bcl_rec.date_next_invoice;
3767 IF l_com_date IS NULL THEN
3768 l_com_date := SYSDATE;
3769 END IF;
3770 /*Added for bug 14337674 */
3771
3772 set_commitment(rul_hdr_rec.commitment_id,
3773 rul_hdr_rec.bill_to_site_use_id,
3774 rul_line_rec.commitment_id,
3775 bcl_rec.cle_id,
3776 bcl_rec.dnz_chr_id,
3777 bcl_rec.cust_acct_id,
3778 header_rec.org_id,
3779 l_com_date ); /*Added l_com_date for bug 14337674 */
3780 End if;
3781 ---------------------
3782 IF nvl(rul_hdr_rec.payment_type,'XX')= 'CCR' Then
3783
3784 l_as_of_date := bcl_rec.date_next_invoice;
3785 IF l_as_of_date IS NULL THEN
3786 l_as_of_date := SYSDATE;
3787 END IF;
3788 IF bcl_rec.trxn_extension_id is NOT NULL then
3789 G_RAIL_REC.payment_trxn_extension_id :=
3790 bcl_rec.trxn_extension_id;
3791 /* Commented and Modification done by sjanakir for Bug #6855301
3792 G_RAIL_REC.receipt_method_id := to_number(nvl(FND_PROFILE.VALUE_SPECIFIC('OKS_RECEIPT_METHOD_ID', NULL, NULL, NULL, rul_hdr_rec.org_id, NULL),'0'));*/
3793 G_RAIL_REC.receipt_method_id := Get_receipt_method_id (p_customer_id => l_cust_id,
3794 p_site_use_id => l_site_use_id,
3795 p_cc_only => l_cc_only ,
3796 p_as_of_date => TRUNC(l_as_of_date) ,
3797 p_org_id => rul_hdr_rec.org_id) ;
3798 ---check to ensure line commitment is not overwritten
3799 ElsIf rul_line_rec.commitment_id is NULL Then
3800 set_extn_id_at_party(rul_hdr_rec.bill_to_site_use_id,
3801 rul_hdr_rec.trxn_extension_id,
3802 bcl_rec.cust_acct_id,
3803 header_rec.org_id );
3804 /* Added by sjanakir for Bug #6855301 */
3805 IF (G_RAIL_REC.payment_trxn_extension_id = rul_hdr_rec.trxn_extension_id) THEN
3806 G_RAIL_REC.receipt_method_id := Get_receipt_method_id (p_customer_id => l_cust_id,
3807 p_site_use_id => l_site_use_id,
3808 p_cc_only => l_cc_only,
3809 p_as_of_date => TRUNC(l_as_of_date),
3810 p_org_id => rul_hdr_rec.org_id);
3811 END IF;
3812
3813 End if;
3814 /* Added by sjanakir for Bug# 6872005 */
3815 /* Modified by cgopinee for PA-DSS enhancement*/
3816 l_cc_expired := check_CC_valid ( p_trxn_extension_id => G_RAIL_REC.payment_trxn_extension_id);
3817
3818 IF l_cc_expired ='N' THEN
3819 IF G_LOG_YES_NO = 'YES' THEN
3820 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Credit card is not expired');
3821 END IF;
3822 ELSE
3823 IF G_LOG_YES_NO = 'YES' THEN
3824 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Credit card has expired');
3825 END IF;
3826
3827 l_msg_data:= 'Credit Card has expired for the date billed from '||bcl_rec.date_billed_from|| ' and the date billed to '||bcl_rec.date_billed_to; --skuchima 10416595
3828
3829 RAISE MAIN_CUR_EXCEPTION;
3830 END IF;
3831 /* Addition Ends */
3832
3833
3834 END IF;
3835
3836 END IF;
3837
3838
3839 Set_salesrep_id (bcl_rec.dnz_Chr_id);
3840 If G_LOG_YES_NO = 'YES' then
3841 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Calling Set_salesrep_id ');
3842 End If;
3843
3844 Set_qty_and_uom(bcl_rec.cle_id,
3845 bcl_rec.dnz_chr_id,
3846 bcl_rec.date_billed_from,
3847 bcl_rec.date_billed_to,
3848 bcl_rec.bill_action,
3849 bcl_rec.lse_id,
3850 'Y');
3851 If G_LOG_YES_NO = 'YES' then
3852 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After Calling Set_qty_and_uom ');
3853 End If;
3854
3855
3856 /****
3857 Bug# 5215768. As decided upon By AR team, Ramesh, Aaron, it was decided
3858 to pass Lifo rule to Usage CM lines
3859 Hari - 11-May-2006
3860 *****/
3861 IF bcl_rec.lse_id = 12 and p_prv = 3 Then
3862 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'LIFO';
3863 ELSIF bcl_rec.lse_id = 46 and p_prv = 3 Then
3864 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'UNIT';
3865 End if;
3866
3867 -------------------------------------------------
3868
3869 -- Set l_bill_profile . This parameter is used to decide wheather
3870 -- to do summary billing or detailed billing
3871
3872 -----IF (bcl_rec.bill_action in ('AV','TR','STR')) THEN
3873 IF (bcl_rec.bill_action in ('TR','STR')) THEN
3874 l_bsl_id := NULL;
3875 l_chk_bcl_id := -44;
3876 OPEN check_summary_billed (bcl_rec.cle_id,
3877 bcl_rec.date_billed_from,
3878 bcl_rec.date_billed_to );
3879 FETCH check_summary_billed into l_bsl_id,l_chk_bcl_id;
3880
3881
3882 /*** for OM originated contracts, since there are no records in Bill Transactions
3883 table. The credit transaction should be detailed.
3884 refer bug# 4353365 and 4365540 ****/
3885
3886 IF check_summary_billed%NOTFOUND THEN
3887 l_chk_bcl_id := -44;
3888 End if;
3889
3890 CLOSE check_summary_billed ;
3891 IF ((l_bsl_id IS NULL) AND (l_chk_bcl_id <> -44)) THEN
3892 l_bill_profile := G_SUM;
3893 ELSE
3894 l_bill_profile := G_DET;
3895 END IF;
3896
3897 ELSE
3898 IF (bcl_rec.lse_id = 12) THEN
3899 l_bill_profile := G_DET;
3900 ELSE
3901 IF (l_hdr_summary_flag = 'Y') THEN --Summary flag set in contract
3902 l_bill_profile := G_SUM;
3903 ELSE -- read profile value
3904 l_bill_profile_flag := FND_PROFILE.VALUE('OKS_AR_TRANSACTIONS_SUBMIT_SUMMARY_YN');
3905 IF (l_bill_profile_flag = 'YES') THEN
3906 l_bill_profile := G_SUM;
3907 ELSE
3908 l_bill_profile := G_DET;
3909 END IF;
3910 END IF;
3911 END IF;
3912 END IF;
3913
3914
3915 -- BUG FIX 3401601.
3916 -- Added substrb
3917 G_RAIL_REC.interface_line_attribute9 := substrb(bcl_rec.lse_name,1,30);
3918
3919
3920 IF (l_bill_profile = G_SUM) THEN
3921 If G_LOG_YES_NO = 'YES' then
3922 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Summary Bill Processing');
3923 End If;
3924 Set_ref_line_id( bcl_rec.bill_action,
3925 bcl_rec.lse_id,
3926 bcl_rec.cle_id,
3927 bcl_rec.id,
3928 bcl_rec.date_billed_From,
3929 bcl_rec.date_billed_to,
3930 rul_hdr_rec.commitment_id,
3931 rul_line_rec.commitment_id,
3932 header_rec.contract_number,
3933 header_rec.contract_number_modifier,
3934 NULL,
3935 'Y',
3936 l_line_payment_mth,
3937 l_ret_stat
3938 -- l_cust_trx_id,
3939 -- l_cust_trx_line_id
3940 );
3941
3942 IF (l_ret_stat <> 'S') THEN
3943 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER Auto Invoice Not Run'||Bcl_rec.cle_id);
3944 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
3945 l_msg_data:='OKS_ARFEEDER_PUB.Get_REC_FEEDER Auto Invoice Not Run '||Bcl_rec.cle_id ||
3946 ' for the date billed from '||bcl_rec.date_billed_from|| ' and the date billed to '||bcl_rec.date_billed_to; --skuchima 10416595
3947 RAISE MAIN_CUR_EXCEPTION;
3948 End if;
3949
3950
3951 If G_LOG_YES_NO = 'YES' then
3952 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Before call to insert bill_txn_line');
3953 End If;
3954
3955 IF ((p_prv = 1) OR (p_prv = 3)) THEN
3956
3957
3958 --l_btlv_tbl_in(1).TRX_LINE_AMOUNT := Bcl_rec.amount;
3959 l_btlv_tbl_in(1).TRX_AMOUNT := Bcl_rec.amount;
3960 l_btlv_tbl_in(1).BSL_ID := NULL;
3961
3962 OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub
3963 (
3964 p_api_version => 1.0,
3965 p_init_msg_list => 'T',
3966 x_return_status => l_ret_stat,
3967 x_msg_count => l_msg_cnt,
3968 x_msg_data => l_msg_data,
3969 p_btlv_tbl => l_btlv_tbl_in,
3970 x_btlv_tbl => l_btlv_tbl_out
3971 );
3972 If G_LOG_YES_NO = 'YES' then
3973 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After calling OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub l_return_status'||l_ret_stat);
3974 End If;
3975
3976 ELSIF (p_prv = 2) THEN
3977 l_btl_pr_tbl_in(1).ID := oks_bill_rec_pub.get_seq_id;
3978 l_btl_pr_tbl_in(1).TRX_LINE_AMOUNT := Bcl_rec.amount;
3979 l_btl_pr_tbl_in(1).BSL_ID := NULL;
3980 l_btl_pr_tbl_in(1).OBJECT_VERSION_NUMBER := 1.0;
3981 l_btl_pr_tbl_in(1).CREATED_BY := FND_GLOBAL.user_id;
3982 l_btl_pr_tbl_in(1).CREATION_DATE := sysdate;
3983 l_btl_pr_tbl_in(1).LAST_UPDATED_BY := FND_GLOBAL.user_id;
3984 l_btl_pr_tbl_in(1).LAST_UPDATE_LOGIN:=FND_GLOBAL.user_id;
3985 l_btl_pr_tbl_in(1).LAST_UPDATE_DATE := sysdate;
3986 l_btl_pr_tbl_in(1).BILL_INSTANCE_NUMBER := -99;
3987 l_btl_pr_tbl_in(1).TRX_LINE_AMOUNT := NULL;
3988 l_btl_pr_tbl_in(1).TRX_LINE_TAX_AMOUNT := NULL;
3989 l_btl_pr_tbl_in(1).ATTRIBUTE_CATEGORY := NULL;
3990 l_btl_pr_tbl_in(1).ATTRIBUTE1 := NULL;
3991 l_btl_pr_tbl_in(1).ATTRIBUTE2 := NULL;
3992 l_btl_pr_tbl_in(1).ATTRIBUTE3 := NULL;
3993 l_btl_pr_tbl_in(1).ATTRIBUTE4 := NULL;
3994 l_btl_pr_tbl_in(1).ATTRIBUTE5 := NULL;
3995 l_btl_pr_tbl_in(1).ATTRIBUTE6 := NULL;
3996 l_btl_pr_tbl_in(1).ATTRIBUTE7 := NULL;
3997 l_btl_pr_tbl_in(1).ATTRIBUTE8 := NULL;
3998 l_btl_pr_tbl_in(1).ATTRIBUTE1 := NULL;
3999 l_btl_pr_tbl_in(1).ATTRIBUTE9 := NULL;
4000 l_btl_pr_tbl_in(1).ATTRIBUTE10 := NULL;
4001 l_btl_pr_tbl_in(1).ATTRIBUTE11 := NULL;
4002 l_btl_pr_tbl_in(1).ATTRIBUTE12 := NULL;
4003 l_btl_pr_tbl_in(1).ATTRIBUTE13 := NULL;
4004 l_btl_pr_tbl_in(1).ATTRIBUTE14 := NULL;
4005 l_btl_pr_tbl_in(1).ATTRIBUTE15 := NULL;
4006 l_btl_pr_tbl_in(1).SECURITY_GROUP_ID := NULL;
4007
4008 OKS_BILLTRAN_LINE_PRV_PUB.insert_btl_pr(
4009 p_api_version => 1.0,
4010 p_init_msg_list => 'T',
4011 x_return_status => l_ret_stat,
4012 x_msg_count => l_msg_cnt,
4013 x_msg_data => l_msg_data,
4014 p_btl_pr_tbl => l_btl_pr_tbl_in,
4015 x_btl_pr_tbl => l_btl_pr_tbl_out);
4016 END IF;
4017
4018 IF (l_ret_stat <> 'S') THEN
4019 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER Insert into Bill_Tran_lines Failed For'||Bcl_rec.cle_id);
4020 oks_bill_rec_pub.get_message
4021 (l_msg_cnt => l_msg_cnt,
4022 l_msg_data => l_msg_data);
4023
4024 IF (P_PRV <> 3) THEN
4025 x_msg_count := l_msg_cnt;
4026 x_msg_data := l_msg_data;
4027 END IF;
4028
4029 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4030 RAISE MAIN_CUR_EXCEPTION;
4031 END IF;
4032
4033 l_amount := (bcl_rec.amount);
4034
4035 IF ((p_prv = 1) OR (p_prv = 3)) THEN
4036
4037 Set_line_attribute(bcl_rec.cle_id,
4038 bcl_rec.date_billed_From,
4039 lines_rec.block23text,
4040 rul_line_rec.invoice_text,
4041 lines_rec.item_description,
4042 l_btlv_tbl_out(1).bill_instance_number,
4043 l_amount,
4044 rul_line_rec.inv_print_flag,
4045 lines_rec.attribute1,
4046 lines_rec.attribute2,
4047 lines_rec.attribute3,
4048 lines_rec.attribute4,
4049 lines_rec.attribute5,
4050 lines_rec.attribute6,
4051 lines_rec.attribute7,
4052 lines_rec.attribute8,
4053 lines_rec.attribute9,
4054 lines_rec.attribute10,
4055 lines_rec.attribute11,
4056 lines_rec.attribute12,
4057 lines_rec.attribute13,
4058 lines_rec.attribute14,
4059 lines_rec.attribute15,
4060 lines_rec.attribute_category
4061 );
4062
4063 /* Added for Bug#14809200*/
4064 IF bcl_rec.bill_action in ('TR','STR') THEN
4065 G_RAIL_REC.RULE_START_DATE := NULL;
4066 ELSE
4067 G_RAIL_REC.RULE_START_DATE := bcl_rec.DATE_BILLED_FROM;
4068 END IF;
4069 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER, RULE_START_DATE:' || G_RAIL_REC.RULE_START_DATE);
4070 /* End of Modification for Bug#14809200*/
4071
4072 If G_LOG_YES_NO = 'YES' then
4073 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Before call to ra_interface_lines');
4074 End If;
4075
4076 G_RAIL_REC.invoiced_line_acctg_level := 'S';
4077 G_RAIL_REC.source_data_key1 := bcl_rec.cle_id;
4078 G_RAIL_REC.source_data_key2 := bcl_rec.id;
4079
4080 Insert_ra_interface
4081 (
4082 l_ret_stat,
4083 l_msg_cnt,
4084 l_msg_data
4085 );
4086
4087 IF (l_ret_stat <> 'S') THEN
4088 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into RA_Interface_Lines Failed For'||Bcl_rec.cle_id);
4089 oks_bill_rec_pub.get_message
4090 (l_msg_cnt => l_msg_cnt,
4091 l_msg_data => l_msg_data);
4092
4093
4094 IF (P_PRV <> 3) THEN
4095 x_msg_count := l_msg_cnt;
4096 x_msg_data := l_msg_data;
4097
4098 END IF;
4099 ----DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4100 RAISE MAIN_CUR_EXCEPTION;
4101 END IF;
4102
4103 IF P_PRV = 1 THEN
4104
4105 UPDATE oks_bill_txn_lines
4106 SET cycle_refrence = G_RAIL_REC.interface_line_attribute10
4107 WHERE bill_instance_number = TO_NUMBER(G_RAIL_REC.interface_line_attribute3);
4108 END IF;
4109
4110
4111 If G_LOG_YES_NO = 'YES' then
4112 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => End of Bill processing And Beginning of Revenue');
4113 End If;
4114
4115 /*Added for bug 14242256 */
4116 IF (p_prv <> 3) THEN
4117 insert_ra_rev_dist( l_ret_stat,bcl_rec.cle_id);
4118
4119 IF (l_ret_stat <> 'S') THEN
4120 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_REVENUE_DISTRIBUTIONS'||lines_rec.id);
4121
4122 x_msg_count := l_msg_cnt;
4123 x_msg_data := l_msg_data;
4124
4125 RAISE MAIN_CUR_EXCEPTION;
4126 END IF;
4127
4128 ELSIF (Nvl(fnd_profile.Value('AR_USE_INV_ACCT_FOR_CM_FLAG'),'Y') ='N') THEN
4129 insert_ra_rev_dist( l_ret_stat,bcl_rec.cle_id);
4130
4131 IF (l_ret_stat <> 'S') THEN
4132 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_REVENUE_DISTRIBUTIONS'||lines_rec.id);
4133
4134 RAISE MAIN_CUR_EXCEPTION;
4135 END IF;
4136
4137 END IF;
4138
4139 /*Added for bug 14242256 */
4140 ----insert_ra_rev_dist( l_ret_stat,bcl_rec.cle_id);
4141
4142
4143
4144 If G_LOG_YES_NO = 'YES' then
4145 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => End of Revenue processing And Beginning of Sales Credit');
4146 End If;
4147 --mchoudha Fix for bug#4174921
4148 --added parameter p_hdr_id
4149 Sales_credit(Bcl_rec.cle_id ,
4150 Bcl_rec.dnz_chr_id,
4151 l_ret_stat);
4152
4153 IF (l_ret_stat <> 'S') THEN
4154
4155 IF (P_PRV <> 3) THEN
4156 x_msg_count := l_msg_cnt;
4157 x_msg_data := l_msg_data;
4158
4159 END IF;
4160 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4161 RAISE MAIN_CUR_EXCEPTION;
4162 END IF;
4163
4164 If G_LOG_YES_NO = 'YES' then
4165 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => End of sales credit processing ');
4166 End If;
4167
4168 END IF; -- ((p_prv = 1) OR (p_prv = 3))
4169
4170 ELSIF ( l_bill_profile = G_DET) THEN
4171
4172 If G_LOG_YES_NO = 'YES' then
4173 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Detail Bill Processing');
4174 End If;
4175
4176 ----This is for Usage averaging line to skip report
4177 if bcl_rec.bill_action = 'AV' Then
4178 report_bill_action := 'AV';
4179 end if;
4180
4181 BSL_REC_NUM := 1;
4182
4183 FOR BSL_REC IN Cur_BSL(Bcl_rec.id)
4184 LOOP
4185
4186 If G_LOG_YES_NO = 'YES' then
4187 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Processing Sub_Line Record'||BSL_REC_NUM||'.'||bsl_rec.cle_id);
4188 End If;
4189 BSL_REC_NUM := BSL_REC_NUM + 1;
4190 l_bill_instance_number := NULL;
4191
4192 ----set_qty should only be called from top line
4193 /* Set_qty_and_uom(bsl_rec.cle_id,
4194 bcl_rec.dnz_chr_id,
4195 bsl_rec.date_billed_from,
4196 bsl_rec.date_billed_to,
4197 bcl_rec.bill_action,
4198 bcl_rec.lse_id,
4199 'N');*/
4200
4201
4202 IF (l_lse_id in (19,1,12)) THEN
4203 OPEN Cur_lsl_type(bsl_rec.cle_id);
4204 FETCH Cur_lsl_type into l_sublse_id, l_lse_name;
4205 CLOSE Cur_lsl_type;
4206 l_desc := l_lse_name || ':';
4207 END IF;
4208
4209
4210 /*******
4211 The quantity and the uom_code is populated with instance qty and uom
4212 for covered product. This is bug fix as part of Kronos.
4213 refer bug# 4706155
4214 for covered levels other than products, the derivation of quantity
4215 and the uom will reamin as is.
4216 *************/
4217 /*commented for bug:10010880*/
4218 /* IF bsl_rec.lse_id in (9,25) Then
4219 OPEN cur_okc_k_item_qty(bsl_rec.cle_id);
4220 FETCH cur_okc_k_item_qty into G_RAIL_REC.QUANTITY, G_RAIL_REC.UOM_CODE;
4221 CLOSE cur_okc_k_item_qty;
4222 G_RAIL_REC.QUANTITY_ORDERED := G_RAIL_REC.QUANTITY;
4223 IF p_prv = 3 THEN
4224 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'UNIT';
4225 End if;
4226 END IF;*/
4227
4228 ---- End of kronos fix
4229
4230 /***
4231 This is to honor the contract contengices by sending
4232 the order line id of the goods sold. Th edeferral flag is always
4233 set to 'Y', so that AR will not apply defaulting rules
4234 **/
4235
4236 IF bsl_rec.lse_id in (25,9) Then
4237 OPEN Cur_Parent_Order_line(bsl_rec.cle_id);
4238 Fetch Cur_Parent_Order_line into
4239 G_RAIL_REC.parent_line_id;
4240 Close Cur_Parent_Order_line;
4241 End If;
4242
4243 G_RAIL_REC.deferral_exclusion_flag := 'Y';
4244
4245
4246
4247 G_RAIL_REC.sales_order_line := bcl_rec.top_line_number||'.'||bsl_rec.sub_line_number;
4248 --- Top line id will be overwritten by sub line id for detailed billing
4249 G_RAIL_REC.contract_line_id := bsl_rec.cle_id;
4250
4251
4252 Set_ref_line_id(
4253 bcl_rec.bill_action,
4254 bsl_rec.lse_id,
4255 bsl_rec.cle_id,
4256 bsl_rec.id,
4257 bsl_rec.date_billed_From,
4258 bsl_rec.date_billed_to,
4259 rul_hdr_rec.commitment_id,
4260 rul_line_rec.commitment_id,
4261 header_rec.contract_number,
4262 header_rec.contract_number_modifier,
4263 bsl_rec.average,
4264 'N',
4265 l_line_payment_mth,
4266 l_ret_stat
4267 -- l_cust_trx_id,
4268 -- l_cust_trx_line_id
4269 );
4270
4271 IF (l_ret_stat <> 'S') THEN
4272 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER Auto Invoice Not Run'||Bcl_rec.cle_id);
4273
4274 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4275
4276 l_msg_data:='OKS_ARFEEDER_PUB.Get_REC_FEEDER Auto Invoice Not Run '||Bcl_rec.cle_id ||
4277 ' for the date billed from '||bcl_rec.date_billed_from|| ' and the date billed to '||bcl_rec.date_billed_to; --skuchima 10416595
4278
4279 RAISE MAIN_CUR_EXCEPTION;
4280 End if;
4281
4282 If G_LOG_YES_NO = 'YES' then
4283 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After calling Set_ref_line_id ');
4284 End If;
4285
4286 IF ((p_prv =1) OR ( p_prv = 3)) THEN
4287 /*Added for bug:10010880*/
4288 IF bsl_rec.lse_id in (9,25) Then
4289 OPEN cur_okc_k_item_qty(bsl_rec.cle_id);
4290 FETCH cur_okc_k_item_qty into G_RAIL_REC.QUANTITY, G_RAIL_REC.UOM_CODE;
4291 CLOSE cur_okc_k_item_qty;
4292 G_RAIL_REC.QUANTITY_ORDERED := G_RAIL_REC.QUANTITY;
4293 IF p_prv = 3 THEN
4294 G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE := 'UNIT';
4295 End if;
4296 END IF;
4297 l_btlv_tbl_in(1).BSL_ID := bsl_rec.id;
4298 --l_btlv_tbl_in(1).TRX_LINE_AMOUNT := bsl_rec.amount;
4299 l_btlv_tbl_in(1).TRX_AMOUNT := bsl_rec.amount;
4300
4301 OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub
4302 (
4303 p_api_version => 1.0,
4304 p_init_msg_list => 'T',
4305 x_return_status => l_ret_stat,
4306 x_msg_count => l_msg_cnt,
4307 x_msg_data => l_msg_data,
4308 p_btlv_tbl => l_btlv_tbl_in,
4309 x_btlv_tbl => l_btlv_tbl_out
4310 );
4311 If G_LOG_YES_NO = 'YES' then
4312 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After call to insert bill_txn_lines l_return_status '||l_ret_stat);
4313 End If;
4314
4315 ELSIF (p_prv =2 ) THEN
4316 l_btl_pr_tbl_in(1).ID := oks_bill_rec_pub.get_seq_id;
4317 l_btl_pr_tbl_in(1).TRX_LINE_AMOUNT := Bsl_rec.amount;
4318 l_btl_pr_tbl_in(1).BSL_ID := bsl_rec.id;
4319 l_btl_pr_tbl_in(1).OBJECT_VERSION_NUMBER := 1.0;
4320 l_btl_pr_tbl_in(1).CREATED_BY := FND_GLOBAL.user_id;
4321 l_btl_pr_tbl_in(1).CREATION_DATE := sysdate;
4322 l_btl_pr_tbl_in(1).LAST_UPDATED_BY := FND_GLOBAL.user_id;
4323 l_btl_pr_tbl_in(1).LAST_UPDATE_LOGIN:=FND_GLOBAL.user_id;
4324 l_btl_pr_tbl_in(1).LAST_UPDATE_DATE := sysdate;
4325 l_btl_pr_tbl_in(1).BILL_INSTANCE_NUMBER := -99;
4326 l_btl_pr_tbl_in(1).TRX_LINE_AMOUNT := NULL;
4327 l_btl_pr_tbl_in(1).TRX_LINE_TAX_AMOUNT := NULL;
4328 l_btl_pr_tbl_in(1).ATTRIBUTE_CATEGORY := NULL;
4329 l_btl_pr_tbl_in(1).ATTRIBUTE1 := NULL;
4330 l_btl_pr_tbl_in(1).ATTRIBUTE2 := NULL;
4331 l_btl_pr_tbl_in(1).ATTRIBUTE3 := NULL;
4332 l_btl_pr_tbl_in(1).ATTRIBUTE4 := NULL;
4333 l_btl_pr_tbl_in(1).ATTRIBUTE5 := NULL;
4334 l_btl_pr_tbl_in(1).ATTRIBUTE6 := NULL;
4335 l_btl_pr_tbl_in(1).ATTRIBUTE7 := NULL;
4336 l_btl_pr_tbl_in(1).ATTRIBUTE8 := NULL;
4337 l_btl_pr_tbl_in(1).ATTRIBUTE1 := NULL;
4338 l_btl_pr_tbl_in(1).ATTRIBUTE9 := NULL;
4339 l_btl_pr_tbl_in(1).ATTRIBUTE10 := NULL;
4340 l_btl_pr_tbl_in(1).ATTRIBUTE11 := NULL;
4341 l_btl_pr_tbl_in(1).ATTRIBUTE12 := NULL;
4342 l_btl_pr_tbl_in(1).ATTRIBUTE13 := NULL;
4343 l_btl_pr_tbl_in(1).ATTRIBUTE14 := NULL;
4344 l_btl_pr_tbl_in(1).ATTRIBUTE15 := NULL;
4345 l_btl_pr_tbl_in(1).SECURITY_GROUP_ID := NULL;
4346
4347 OKS_BILLTRAN_LINE_PRV_PUB.insert_btl_pr(
4348 p_api_version => 1.0,
4349 p_init_msg_list => 'T',
4350 x_return_status => l_ret_stat,
4351 x_msg_count => l_msg_cnt,
4352 x_msg_data => l_msg_data,
4353 p_btl_pr_tbl => l_btl_pr_tbl_in,
4354 x_btl_pr_tbl => l_btl_pr_tbl_out);
4355
4356
4357
4358 END IF;
4359
4360 IF (l_ret_stat <> 'S') THEN
4361 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into Bill_Tran_lines Failed For'||bsl_rec.cle_id);
4362 oks_bill_rec_pub.get_message
4363 (l_msg_cnt => l_msg_cnt,
4364 l_msg_data => l_msg_data);
4365
4366 x_msg_count := l_msg_cnt;
4367 x_msg_data := l_msg_data;
4368 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4369 RAISE MAIN_CUR_EXCEPTION;
4370 END IF;
4371
4372 l_amount := (bsl_rec.amount);
4373
4374 --start nechatur Fix for bug#4390448
4375 --pass the billed usage quantity and uom_code to ra_interface_lines
4376 IF l_lse_id = 12 THEN
4377 IF (l_usage_type <> 'NPR') THEN
4378 Open Cur_bsd(Bsl_rec.id);
4379 Fetch Cur_bsd into G_RAIL_REC.QUANTITY_ORDERED,G_RAIL_REC.UOM_CODE;
4380 G_RAIL_REC.QUANTITY := G_RAIL_REC.QUANTITY_ORDERED;
4381 Close Cur_bsd;
4382 END IF;
4383 --
4384 if nvl(l_amount,0) > 0 Then
4385 G_RAIL_REC.unit_selling_price := l_amount/G_RAIL_REC.QUANTITY_ORDERED;
4386 end if;
4387 END IF;
4388 --end nechatur Fix for bug#4390448
4389 OPEN Cur_k_lines(bsl_rec.cle_id);
4390 FETCH Cur_k_lines into lines_rec;
4391 CLOSE Cur_k_lines ;
4392
4393 IF ((p_prv =1) OR (p_prv = 3)) THEN
4394
4395
4396 --skuchima added for bug 10260151 if it is a usage line then even for the detail billing,consider the inv_print flag from line level,as there is no flag at subline level
4397
4398 IF (bcl_rec.lse_id = 12) THEN
4399
4400 OPEN cur_inv_print_flag(bcl_rec.cle_id);
4401 FETCH cur_inv_print_flag into l_inv_print_flag;
4402 CLOSE cur_inv_print_flag;
4403 ELSE
4404 l_inv_print_flag:=bsl_rec.inv_print_flag;
4405 END IF;
4406
4407
4408 Set_line_attribute(
4409 bsl_rec.cle_id,
4410 bsl_rec.date_billed_From,
4411 lines_rec.block23text,
4412 bsl_rec.invoice_text,
4413 lines_rec.item_description,
4414 l_btlv_tbl_out(1).bill_instance_number,
4415 l_amount,
4416 l_inv_print_flag,--bsl_rec.inv_print_flag, skuchima for bug 10011175
4417 lines_rec.attribute1,
4418 lines_rec.attribute2,
4419 lines_rec.attribute3,
4420 lines_rec.attribute4,
4421 lines_rec.attribute5,
4422 lines_rec.attribute6,
4423 lines_rec.attribute7,
4424 lines_rec.attribute8,
4425 lines_rec.attribute9,
4426 lines_rec.attribute10,
4427 lines_rec.attribute11,
4428 lines_rec.attribute12,
4429 lines_rec.attribute13,
4430 lines_rec.attribute14,
4431 lines_rec.attribute15,
4432 lines_rec.attribute_category
4433 );
4434
4435
4436 If G_LOG_YES_NO = 'YES' then
4437 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After calling Set_line_attribute ');
4438 End If;
4439
4440 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE4 :=
4441 to_char(Bsl_rec.DATE_BILLED_FROM,'YYYY/MM/DD');
4442 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE8 :=
4443 to_char(lines_rec.start_date,'YYYY/MM/DD');
4444 G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE5 :=
4445 to_char(Bsl_rec.DATE_BILLED_TO,'YYYY/MM/DD');
4446 --Modified for Bug# 14809200
4447 IF bcl_rec.bill_action in ('TR','STR') THEN
4448 G_RAIL_REC.RULE_START_DATE := NULL;
4449 ELSE
4450 G_RAIL_REC.RULE_START_DATE := Bsl_rec.DATE_BILLED_FROM;
4451 END IF;
4452 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER, RULE_START_DATE:' || G_RAIL_REC.RULE_START_DATE);
4453 /* End of Modification for Bug#14809200*/
4454 -- Added by sjanakir for Bug#6524778
4455 IF l_type IN ('ACC_DUR','A') THEN
4456 G_RAIL_REC.RULE_END_DATE := NULL;
4457 ELSE
4458 G_RAIL_REC.RULE_END_DATE := Bsl_rec.DATE_BILLED_TO;
4459 END IF;
4460 -- Addition Ends
4461
4462 If G_LOG_YES_NO = 'YES' then
4463 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Before insert into ra_interface_lines');
4464 End If;
4465
4466 G_RAIL_REC.invoiced_line_acctg_level := 'D';
4467 G_RAIL_REC.source_data_key1 := bsl_rec.top_line_id;
4468 G_RAIL_REC.source_data_key2 := bcl_rec.id;
4469
4470 l_inv_count := 0;
4471 l_interface_pb := 'N';
4472
4473
4474 IF (( p_prv = 1 ) AND ( bcl_rec.lse_id = 12) AND (rul_line_rec.usage_type <> 'NPR')) THEN
4475 OPEN Interface_pb_cur;
4476 FETCH Interface_pb_cur into l_interface_pb;
4477 CLOSE Interface_pb_cur;
4478
4479 l_price_break_count := 0;
4480
4481 open chk_price_break_cur(bsl_rec.id);
4482 fetch chk_price_break_cur into l_price_break_count;
4483 close chk_price_break_cur;
4484
4485 ELSIF (( p_prv = 3 ) AND ( bcl_rec.lse_id = 12)AND (rul_line_rec.usage_type <> 'NPR')) THEN
4486 OPEN Bill_instance_cur(bsl_rec.id);
4487 FETCH Bill_instance_cur into l_bill_instance_no;
4488 CLOSE Bill_instance_cur;
4489
4490 OPEN Inv_count_cur(
4491 header_rec.contract_number,
4492 header_rec.contract_number_modifier,
4493 l_bill_instance_no);
4494
4495 FETCH Inv_count_cur into l_inv_count;
4496 CLOSE Inv_count_cur;
4497 END IF;
4498
4499
4500 IF ( ((p_prv = 1) AND ( NVL(l_interface_pb,'N') = 'Y') AND
4501 (l_price_break_count <> 0)) OR
4502 ((p_prv = 3 ) AND (l_inv_count > 1)) OR
4503 ( p_prv = 3 AND bcl_rec.bill_action = 'TR' AND l_inv_count= 0 AND
4504 bcl_rec.lse_id = 12 AND rul_line_rec.usage_type <> 'NPR' ) ) THEN
4505
4506 IF (p_prv = 1) THEN
4507 G_RAIL_REC.quantity_ordered := 0;
4508 G_RAIL_REC.quantity := 0;
4509 G_RAIL_REC.amount := 0;
4510 G_RAIL_REC.interface_line_attribute6 := 0;
4511
4512 Insert_ra_interface
4513 (
4514 l_ret_stat,
4515 l_msg_cnt,
4516 l_msg_data
4517 );
4518
4519
4520 IF (l_ret_stat <> 'S') THEN
4521
4522 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into RA_Interface_Lines Failed For'||bsl_rec.cle_id);
4523 oks_bill_rec_pub.get_message
4524 (l_msg_cnt => l_msg_cnt,
4525 l_msg_data => l_msg_data);
4526
4527 x_msg_count := l_msg_cnt;
4528 x_msg_data := l_msg_data;
4529 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4530 RAISE MAIN_CUR_EXCEPTION;
4531 END IF;
4532
4533 INSERT_RA_REV_DIST( l_ret_stat,
4534 bcl_rec.cle_id);
4535
4536 IF (l_ret_stat <> 'S') THEN
4537 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_REVENUE_DISTRIBUTIO NS'||lines_rec.id);
4538
4539 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4540 RAISE MAIN_CUR_EXCEPTION;
4541 END IF;
4542
4543 If G_LOG_YES_NO = 'YES' then
4544 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => End of Revenue processing And Beginning of Sales Credit');
4545 End If;
4546 --mchoudha Fix for bug#4174921
4547 --added parameter p_hdr_id
4548 SALES_CREDIT(Bcl_rec.cle_id ,
4549 Bcl_rec.dnz_chr_id,
4550 l_ret_stat);
4551
4552 IF ( l_ret_stat <> 'S') THEN
4553 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_SALES_CREDIT FAILED'||lines_rec.id);
4554 ----DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4555
4556 l_msg_data:= 'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_SALES_CREDIT FAILED '||lines_rec.id; --skuchima 10416595
4557
4558 RAISE MAIN_CUR_EXCEPTION;
4559 END IF;
4560 If G_LOG_YES_NO = 'YES' then
4561 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => End of sales credit processing ');
4562 End If;
4563 END IF; -----IF (p_prv = 1)
4564
4565 IF p_prv = 3 AND bcl_rec.bill_action = 'TR' AND l_inv_count= 0 THEN ----billing rec not Invoiced
4566 --mchoudha Fix for bug#4174921
4567 --added parameter p_hdr_id
4568 Set_Reference_PB_Value
4569 (
4570 p_bsl_id => bsl_rec.id,
4571 p_contract_no => header_rec.contract_number,
4572 p_contract_mod => header_rec.contract_number_modifier,
4573 p_bill_inst_no => l_bill_instance_no,
4574 p_amount => bsl_rec.amount,
4575 p_int_att10 => g_rail_rec.interface_line_attribute10,
4576 p_bcl_cle_id => bcl_rec.cle_id,
4577 p_currency_code => bcl_rec.currency_code,
4578 p_hdr_id => Bcl_rec.dnz_chr_id,
4579 x_msg_cnt => l_msg_cnt,
4580 x_msg_data => l_msg_data,
4581 x_return_status => l_ret_stat
4582 );
4583 ELSE ----either billing or termination when billing rec are invoiced
4584 --mchoudha Fix for bug#4174921
4585 --added parameter p_hdr_id
4586 Set_price_breaks
4587 (
4588 p_id => bsl_rec.id,
4589 p_prv => p_prv,
4590 p_contract_no => header_rec.contract_number,
4591 p_contract_mod => header_rec.contract_number_modifier,
4592 p_bill_inst_no => l_bill_instance_no,
4593 p_amount => bsl_rec.amount,
4594 p_int_att10 => g_rail_rec.interface_line_attribute10,
4595 p_bcl_cle_id => bcl_rec.cle_id,
4596 p_currency_code => bcl_rec.currency_code,
4597 p_hdr_id => Bcl_rec.dnz_chr_id,
4598 x_msg_cnt => l_msg_cnt,
4599 x_msg_data => l_msg_data,
4600 x_return_status => l_ret_stat
4601 );
4602 END IF; ---l_inv_count chk
4603
4604
4605 IF (l_ret_stat <> 'S') THEN
4606
4607 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into RA_Interface_Lines Failed For'||bsl_rec.cle_id);
4608 oks_bill_rec_pub.get_message
4609 (l_msg_cnt => l_msg_cnt,
4610 l_msg_data => l_msg_data);
4611
4612 x_msg_count := l_msg_cnt;
4613 x_msg_data := l_msg_data;
4614 ----DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4615 RAISE MAIN_CUR_EXCEPTION;
4616 END IF;
4617
4618 ELSE ---normal
4619
4620 Insert_ra_interface
4621 (
4622 l_ret_stat,
4623 l_msg_cnt,
4624 l_msg_data
4625 );
4626
4627 IF (l_ret_stat <> 'S') THEN
4628 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into RA_Interface_Lines Failed For'||bsl_rec.cle_id);
4629 oks_bill_rec_pub.get_message
4630 (l_msg_cnt => l_msg_cnt,
4631 l_msg_data => l_msg_data);
4632
4633 x_msg_count := l_msg_cnt;
4634 x_msg_data := l_msg_data;
4635
4636 ----DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4637 RAISE MAIN_CUR_EXCEPTION;
4638
4639 END IF; --IF (l_ret_stat <> 'S')
4640
4641
4642 IF P_PRV = 1 THEN
4643
4644 UPDATE oks_bill_txn_lines
4645 SET cycle_refrence = G_RAIL_REC.interface_line_attribute10
4646 WHERE bill_instance_number = TO_NUMBER(G_RAIL_REC.interface_line_attribute3);
4647 END IF;
4648
4649
4650 /*Added for bug 14242256 */
4651 IF (p_prv <> 3) THEN
4652 insert_ra_rev_dist( l_ret_stat,bcl_rec.cle_id);
4653 IF (l_ret_stat <> 'S') THEN
4654 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_REVENUE_DISTRIBUTIONS'||lines_rec.id);
4655 RAISE MAIN_CUR_EXCEPTION;
4656 END IF;
4657 ELSIF (Nvl(fnd_profile.Value('AR_USE_INV_ACCT_FOR_CM_FLAG'),'Y') ='N') THEN
4658 insert_ra_rev_dist( l_ret_stat,bcl_rec.cle_id);
4659 IF (l_ret_stat <> 'S') THEN
4660 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_REVENUE_DISTRIBUTIONS'||lines_rec.id);
4661 RAISE MAIN_CUR_EXCEPTION;
4662 END IF;
4663 END IF;
4664 /*Added for bug 14242256 */
4665
4666
4667 If G_LOG_YES_NO = 'YES' then
4668 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => End of Revenue processing And Beginning of Sales Credit');
4669 End If;
4670 --mchoudha Fix for bug#4174921
4671 --added parameter p_hdr_id
4672 SALES_CREDIT(Bcl_rec.cle_id ,
4673 Bcl_rec.dnz_chr_id,
4674 l_ret_stat);
4675
4676 IF ( l_ret_stat <> 'S') THEN
4677 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_SALES_CREDIT FAILED'||lines_rec.id);
4678 ----DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4679 l_msg_data:= 'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_SALES_CREDIT FAILED '||lines_rec.id; ---skuchima
4680 RAISE MAIN_CUR_EXCEPTION;
4681 END IF;
4682 If G_LOG_YES_NO = 'YES' then
4683 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => End of sales credit processing ');
4684 End If;
4685 END IF; --IF ( ((p_prv = 1) AND ( NVL(l_interface_pb,'N') = 'Y'))OR ((p_prv = 3 ) AND (l_inv_count > 1)) )
4686
4687 END IF; --((p_prv =1) OR (p_prv = 3))
4688
4689 END LOOP;-- End OF BSL Loop
4690 END IF; -- SUM OR DET
4691
4692 END IF; -- Bill_action <> 'TR'
4693
4694 l_process_counter := l_process_counter + 1;
4695 EXCEPTION
4696 WHEN MAIN_CUR_EXCEPTION THEN
4697
4698 /*FOR BILLING REPORT*/
4699 --This is done to retrieve the profile value
4700 --because the error can occur before the profile option
4701 --is retrieved in the main logic
4702 IF (bcl_rec.lse_id = 12) THEN
4703 l_bill_profile := G_DET;
4704 ELSE
4705 IF (l_hdr_summary_flag = 'Y') THEN --Summary flag set in contract
4706 l_bill_profile := G_SUM;
4707 ELSE -- read profile value
4708 l_bill_profile_flag := FND_PROFILE.VALUE('OKS_AR_TRANSACTIONS_SUBMIT_SUMMARY_YN');
4709 IF (l_bill_profile_flag = 'YES') THEN
4710 l_bill_profile := G_SUM;
4711 ELSE
4712 l_bill_profile := G_DET;
4713 END IF;
4714 END IF;
4715 END IF;
4716
4717 IF (P_PRV <> 3) THEN
4718 IF (bcl_rec.lse_id = 46 ) THEN
4719 l_line_failed := TRUE;
4720 /*increment rejected for subscription*/
4721 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + bcl_rec.amount;
4722 /*decrement successful for subscription*/
4723 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value - bcl_rec.amount ;
4724 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
4725 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := bcl_rec.cle_id;
4726 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 46;
4727 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := NULL;
4728 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4729
4730 ELSE
4731 IF (l_bill_profile = G_SUM) THEN
4732 l_line_failed := TRUE;
4733 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + bcl_rec.amount;
4734 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value - bcl_rec.amount ;
4735 /*FOR ERROR REPORT*/
4736 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
4737 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := bcl_rec.cle_id;
4738 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id :=bcl_rec.lse_id;
4739 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := NULL;
4740 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4741
4742 ELSE
4743
4744 IF bsl_rec.cle_id IS NULL THEN
4745
4746 l_line_failed := TRUE;
4747 l_subline_failed := TRUE;
4748
4749 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + bcl_rec.amount;
4750 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value - bcl_rec.amount ;
4751 /*FOR ERROR REPORT*/
4752 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
4753 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := bcl_rec.cle_id;
4754 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id :=bcl_rec.lse_id;
4755 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := NULL;
4756 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. '||l_msg_data ;
4757
4758 ELSE
4759
4760 l_subline_failed := TRUE;
4761 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value + bcl_rec.amount;
4762 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines_Value := p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines_Value - bcl_rec.amount ;
4763 /*FOR ERROR REPORT*/
4764 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
4765 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := bcl_rec.cle_id;
4766 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := bcl_rec.lse_id;
4767 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := bsl_rec.cle_id ;
4768 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. '||l_msg_data ;
4769
4770 END IF;---bsl_rec.cle_id is null skuchima 10416595
4771
4772 END IF; -- l_bill_profile = G_SUM
4773 END IF; -- bcl_rec.lse_id = 46
4774 END IF; -- P_PRV <> 3
4775
4776 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert Failed IN ARFEEDER MAIN_CUR_EXCEPTION RAISED '||sqlerrm);
4777 l_reject_counter := l_reject_counter + 1;
4778 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4779 x_return_status := OKC_API.G_RET_STS_ERROR;
4780 WHEN OTHERS THEN
4781
4782 /*FOR BILLING REPORT*/
4783 --This is done to retrieve the profile value
4784 --because the error can occur before the profile option
4785 --is retrieved in the main logic
4786 IF (bcl_rec.lse_id = 12) THEN
4787 l_bill_profile := G_DET;
4788 ELSE
4789 IF (l_hdr_summary_flag = 'Y') THEN --Summary flag set in contract
4790 l_bill_profile := G_SUM;
4791 ELSE -- read profile value
4792 l_bill_profile_flag := FND_PROFILE.VALUE('OKS_AR_TRANSACTIONS_SUBMIT_SUMMARY_YN');
4793 IF (l_bill_profile_flag = 'YES') THEN
4794 l_bill_profile := G_SUM;
4795 ELSE
4796 l_bill_profile := G_DET;
4797 END IF;
4798 END IF;
4799 END IF;
4800
4801 IF (P_PRV <> 3) THEN
4802 IF (bcl_rec.lse_id = 46 ) THEN
4803 l_line_failed := TRUE;
4804 /*increment rejected for subscription*/
4805 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + bcl_rec.amount;
4806 /*decrement successful for subscription*/
4807 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value - bcl_rec.amount ;
4808 /*FOR ERROR REPORT*/
4809 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
4810 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := bcl_rec.cle_id;
4811 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 46;
4812 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := NULL;
4813 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4814 ELSE
4815 IF (l_bill_profile = G_SUM) THEN
4816 l_line_failed := TRUE;
4817 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + bcl_rec.amount;
4818 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value - bcl_rec.amount ;
4819 /*FOR ERROR REPORT*/
4820 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
4821 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := bcl_rec.cle_id;
4822 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id :=bcl_rec.lse_id;
4823 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := NULL;
4824 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4825 ELSE
4826 l_subline_failed := TRUE;
4827 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value + bcl_rec.amount;
4828 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines_Value := p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines_Value - bcl_rec.amount ;
4829 /*FOR ERROR REPORT*/
4830 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
4831 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := bcl_rec.cle_id;
4832 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := bcl_rec.lse_id;
4833 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := bsl_rec.cle_id ;
4834 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4835 END IF; -- l_bill_profile = G_SUM
4836 END IF; -- bcl_rec.lse_id = 46
4837 END IF; -- P_PRV <> 3
4838
4839 x_return_status := OKC_API.G_RET_STS_ERROR;
4840 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert Failed IN ARFEEDER OTHERS EXCEPTION RAISED'||sqlerrm);
4841 l_reject_counter := l_reject_counter + 1;
4842 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4843
4844 END;
4845
4846 IF (p_prv <> 3 and nvl(report_bill_action,'RI') <> 'AV') THEN
4847 --For successful Processing
4848 IF (l_bill_profile = G_SUM) THEN
4849 IF NOT l_line_failed THEN
4850 p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value + nvl(bcl_rec.amount,0);
4851 END IF;
4852 ELSE
4853 IF NOT l_subline_failed THEN
4854 p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines_Value:= p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines_Value + nvl(bcl_rec.amount,0);
4855 END IF;
4856 END IF;
4857 END IF;
4858
4859 END LOOP; -- BCL_CUR Loop
4860
4861 IF (p_prv = 1) THEN
4862 CLOSE cur_bcl;
4863 ELSIF (p_prv = 2) THEN
4864 CLOSE cur_bcl_pr;
4865 ELSIF ( p_prv = 3) THEN
4866 CLOSE cur_bcl_term;
4867 END IF;
4868
4869 /*FOR BILLING REPORT*/
4870 --For error Processing
4871 IF (l_line_failed) THEN
4872 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines:= p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines +1;
4873 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines:= p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines - 1;
4874 END IF;
4875
4876 IF (l_subline_failed) THEN
4877 select count(id) into l_subline_count from oks_bill_sub_lines where bcl_id = Bcl_rec.id;
4878 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines:= p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines +l_subline_count;
4879 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines:= p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines - l_subline_count;
4880 END IF;
4881
4882 IF (p_prv <> 3 and nvl(report_bill_action,'RI') <> 'AV') THEN
4883 --For successful Processing
4884 IF (l_bill_profile = G_SUM) THEN
4885 IF NOT l_line_failed THEN
4886 p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines:= p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines +1;
4887 END IF;
4888 ELSE
4889 IF NOT l_subline_failed THEN
4890 if p_prv = 1 then
4891 select count(id) into l_subline_count from oks_bill_sub_lines where bcl_id = Bcl_rec.id;
4892 elsif p_prv = 2 then
4893 select count(id) into l_subline_count from oks_bsl_pr where bcl_id = Bcl_rec.id;
4894 end if;
4895 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.debug report'||l_subline_count);
4896
4897 p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines:= p_billrep_tbl(p_billrep_tbl_idx).Successful_SubLines +l_subline_count;
4898 END IF;
4899 END IF;
4900 END IF;
4901
4902
4903 EXCEPTION
4904 WHEN G_EXCEPTION_BILLING THEN
4905 --x_return_status := OKC_API.G_RET_STS_ERROR;
4906 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert Failed IN ARFEEDER G_BILLING_EXCEPTION RAISED '||sqlerrm);
4907 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4908 WHEN OTHERS THEN
4909 --x_return_status := OKC_API.G_RET_STS_ERROR;
4910 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert Failed IN ARFEEDER WHEN OTHERS EXCEPTION RAISED'||sqlerrm);
4911 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_AR_TRANSACTION');
4912
4913 End Get_REC_FEEDER;
4914
4915 Procedure Populate_TR_reference_fields(p_instance_number IN NUMBER,
4916 p_contract_number IN VARCHAR2,
4917 p_contract_modifier IN VARCHAR2,
4918 x_return_status OUT NOCOPY VARCHAR2)
4919
4920 IS
4921
4922 Cursor l_reference_summ_csr is
4923
4924 select interface_line_attribute4, interface_line_attribute5, interface_line_attribute6,
4925 interface_line_attribute7, interface_line_attribute8, interface_line_attribute9,
4926 interface_line_attribute10, conversion_date
4927 from ra_interface_lines_all
4928 WHERE interface_line_attribute1 = p_contract_number
4929 and interface_line_attribute3 = to_char(p_instance_number);
4930
4931 l_reference_summ_rec l_reference_summ_csr%ROWTYPE;
4932
4933 Begin
4934
4935 x_return_status := 'S';
4936
4937 G_RAIL_REC.reference_line_context := 'OKS CONTRACTS';
4938 G_RAIL_REC.reference_line_attribute1 := p_contract_number;
4939 G_RAIL_REC.reference_line_attribute2 := NVL(p_contract_modifier,'-');
4940 G_RAIL_REC.reference_line_attribute3 := p_instance_number;
4941
4942 OPEN l_reference_summ_csr;
4943 FETCH l_reference_summ_csr INTO l_reference_summ_rec;
4944
4945 G_RAIL_REC.reference_line_attribute4 := l_reference_summ_rec.interface_line_attribute4;
4946 G_RAIL_REC.reference_line_attribute5 := l_reference_summ_rec.interface_line_attribute5;
4947 G_RAIL_REC.reference_line_attribute6 := l_reference_summ_rec.interface_line_attribute6;
4948 G_RAIL_REC.reference_line_attribute7 := l_reference_summ_rec.interface_line_attribute7;
4949 G_RAIL_REC.reference_line_attribute8 := l_reference_summ_rec.interface_line_attribute8;
4950 G_RAIL_REC.reference_line_attribute9 := l_reference_summ_rec.interface_line_attribute9;
4951 G_RAIL_REC.reference_line_attribute10 := l_reference_summ_rec.interface_line_attribute10;
4952 /*cgopinee bugfix for 8361496*/
4953 G_RAIL_REC.conversion_date := l_reference_summ_rec.conversion_date;
4954
4955 CLOSE l_reference_summ_csr;
4956
4957
4958 EXCEPTION
4959 WHEN OTHERS THEN
4960 x_return_status := 'E';
4961
4962 END Populate_TR_reference_fields;
4963
4964
4965 --mchoudha Fix for bug#4174921
4966 --added parameter p_hdr_id
4967 Procedure Set_Reference_PB_Value(
4968 p_bsl_id IN NUMBER,
4969 p_contract_no IN VARCHAR2,
4970 p_contract_mod IN VARCHAR2,
4971 p_bill_inst_no IN NUMBER,
4972 p_amount IN NUMBER,
4973 p_int_att10 IN VARCHAR2,
4974 p_bcl_cle_id IN NUMBER,
4975 p_currency_code IN VARCHAR2,
4976 p_hdr_id IN NUMBER,
4977 x_msg_cnt OUT NOCOPY NUMBER,
4978 x_msg_data OUT NOCOPY VARCHAR2,
4979 x_return_status OUT NOCOPY VARCHAR2
4980 )
4981 IS
4982
4983 Cursor l_pb_inv_csr IS
4984 SELECT pb.quantity_from,
4985 pb.unit_price,
4986 pb.amount,
4987 btl.bsl_id
4988 FROM OKS_PRICE_BREAKS pb,
4989 oks_bill_txn_lines btl
4990 WHERE pb.bsl_id = btl.bsl_id
4991 AND btl.bill_instance_number = p_bill_inst_no
4992 AND pb.amount > 0
4993 ORDER BY pb.quantity_from;
4994
4995 Cursor l_inv_csr(p_bsl_id IN NUMBER) IS
4996 SELECT quantity,
4997 unit_price,
4998 amount, quantity_from
4999 FROM OKS_PRICE_BREAKS
5000 WHERE bsl_id = p_bsl_id
5001 ORDER BY abs(amount);
5002
5003 l_pb_inv_rec l_pb_inv_csr%ROWTYPE;
5004 inv_rec l_inv_csr%rowtype;
5005
5006 TYPE pb_Type IS RECORD
5007 (
5008 line_index NUMBER,
5009 amount NUMBER);
5010
5011
5012
5013 Type pb_tbl is TABLE of pb_Type index by binary_integer;
5014
5015 l_inv_pb_tbl pb_tbl;
5016 l_pb_i NUMBER;
5017 l_check_amount NUMBER;
5018 l_unit_selling_price NUMBER;
5019 l_extended_amount NUMBER;
5020 l_term_amount NUMBER;
5021 l_inv_bsl_id NUMBER;
5022 l_constant_ri10 VARCHAR2(100);
5023 l_int_att10 VARCHAR2(100);
5024
5025
5026 BEGIN
5027
5028 x_return_status := 'S';
5029
5030 l_constant_ri10 := G_RAIL_REC.reference_line_attribute10; ---REFERENCE FIELD
5031 l_int_att10 := p_int_att10;
5032
5033
5034 l_pb_i := 0;
5035
5036 IF SUBSTR(l_constant_ri10, -2,2) = 'PB' THEN ----price brks to AR
5037 FOR l_pb_inv_REC IN l_pb_inv_csr
5038 LOOP
5039
5040 l_pb_i := l_pb_i + 1;
5041
5042 l_inv_pb_tbl(l_pb_inv_REC.quantity_from).amount := l_pb_inv_REC.amount;
5043 l_inv_pb_tbl(l_pb_inv_REC.quantity_from).line_index := l_pb_i;
5044
5045 l_inv_bsl_id := l_pb_inv_rec.bsl_id;
5046 END LOOP;
5047
5048
5049 IF l_inv_pb_tbl.COUNT > 0 THEN
5050 l_pb_i := 0;
5051 l_check_amount := abs(p_amount);
5052
5053 FOR inv_rec in l_inv_csr(l_inv_bsl_id)
5054 LOOP
5055
5056 l_unit_selling_price := 0;
5057
5058 l_extended_amount := inv_rec.amount ;
5059 l_unit_selling_price := inv_rec.unit_price;
5060
5061
5062 IF (l_extended_amount <= l_check_amount) THEN
5063 l_term_amount := l_extended_amount;
5064 ELSE
5065 l_term_amount := l_check_amount;
5066 END IF;
5067
5068 l_pb_i := l_pb_i + 1;
5069
5070 G_RAIL_REC.description := 'PBT'||l_pb_i;
5071 G_RAIL_REC.quantity_ordered := round(l_term_amount/l_unit_selling_price);
5072 G_RAIL_REC.quantity := round(l_term_amount/l_unit_selling_price);
5073 G_RAIL_REC.amount := -1 * l_term_amount;
5074 G_RAIL_REC.interface_line_attribute6 := -1 * l_term_amount;
5075 G_RAIL_REC.interface_line_attribute10:= l_int_att10||' for PB'||l_pb_i;
5076
5077
5078 G_RAIL_REC.reference_line_attribute6 := l_extended_amount;
5079 G_RAIL_REC.reference_line_attribute10 := l_constant_ri10 || l_inv_pb_tbl(inv_rec.quantity_from).line_index;
5080
5081
5082 Insert_ra_interface
5083 (
5084 x_return_status,
5085 x_msg_cnt,
5086 x_msg_data
5087 );
5088
5089 IF (x_return_status <> 'S') THEN
5090 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert into RA_Interface_Lines Failed while inserting price breaks ' );
5091 x_return_status := 'E';
5092 END IF; --IF (l_ret_stat <> 'S')
5093
5094 /*Added for bug 14242256*/
5095 IF Nvl(fnd_profile.Value('AR_USE_INV_ACCT_FOR_CM_FLAG'),'Y') ='N' THEN
5096 INSERT_RA_REV_DIST( x_return_status,
5097 p_bcl_cle_id);
5098 IF (x_return_status <> 'S') THEN
5099 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert INTO RA_REVENUE_DISTRIBUTIONS failed for Price Break');
5100
5101 x_return_status := 'E';
5102 END IF;
5103 END IF;
5104 /*Added for bug 14242256*/
5105
5106
5107
5108 --mchoudha Fix for bug#4174921
5109 --added parameter p_hdr_id
5110 Sales_credit(p_bcl_cle_id ,
5111 p_hdr_id,
5112 x_return_status);
5113
5114 IF ( x_return_status <> 'S') THEN
5115 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert INTO RA_SALES_CREDIT FAILED for Price Breaks');
5116 END IF;
5117
5118
5119 EXIT when x_return_status <> 'S';
5120
5121 l_check_amount := l_check_amount - l_term_amount;
5122
5123 IF (l_check_amount <= 0) THEN
5124 EXIT;
5125 END IF;
5126 END LOOP;
5127 END IF; ---l_inv_pb_tbl COUNT CHK
5128
5129 ELSE ---PB not passed to AR
5130 Insert_ra_interface
5131 (
5132 x_return_status,
5133 x_msg_cnt,
5134 x_msg_data
5135 );
5136
5137 IF (x_return_status <> 'S') THEN
5138 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert into RA_Interface_Lines Failed while inserting price breaks ' );
5139 x_return_status := 'E';
5140 END IF; --IF (l_ret_stat <> 'S')
5141
5142 /*Added for bug 14242256*/
5143 IF Nvl(fnd_profile.Value('AR_USE_INV_ACCT_FOR_CM_FLAG'),'Y') ='N' THEN
5144 INSERT_RA_REV_DIST( x_return_status,
5145 p_bcl_cle_id);
5146 IF (x_return_status <> 'S') THEN
5147 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert INTO RA_REVENUE_DISTRIBUTIONS failed for Price Break');
5148
5149 x_return_status := 'E';
5150 END IF;
5151 END IF;
5152 /*Added for bug 14242256*/
5153
5154
5155 --mchoudha Fix for bug#4174921
5156 --added parameter p_hdr_id
5157 Sales_credit(p_bcl_cle_id ,
5158 p_hdr_id,
5159 x_return_status);
5160
5161 IF ( x_return_status <> 'S') THEN
5162 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert INTO RA_SALES_CREDIT FAILED for Price Breaks');
5163 END IF;
5164
5165 END IF;
5166
5167
5168 END Set_Reference_PB_Value;
5169
5170
5171 End OKS_ARFEEDER_PUB;