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