1 Package Body OKS_ARFETCH_PUB AS
2 /* $Header: OKSPARGB.pls 120.4 2010/05/28 11:23:41 vgujarat ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_ARFETCH_PUB';
6
7 -- Global var holding the Current Error code for the error encountered
8 Current_Error_Code Varchar2(20) := NULL;
9
10 -- Global var holding the User Id
11 user_id NUMBER;
12
13 -- Global var to hold the ERROR value.
14 ERROR NUMBER := 1;
15
16 -- Global var to hold the SUCCESS value.
17 SUCCESS NUMBER := 0;
18
19 -- Global var to hold the commit size.
20 COMMIT_SIZE NUMBER := 10;
21
22 -- Global var to hold the Concurrent Process return value
23 conc_ret_code NUMBER := SUCCESS;
24
25
26 /*------------------------------------------------------------------
27 Concurrent Program Wrapper for AR Fetch Program
28 --------------------------------------------------------------------*/
29 PROCEDURE ARFetch_Main
30 ( ERRBUF OUT NOCOPY VARCHAR2,
31 RETCODE OUT NOCOPY NUMBER )
32 IS
33
34 CONC_STATUS BOOLEAN;
35 l_return_status VARCHAR2(10);
36 --l_retcode NUMBER := SUCCESS;
37
38 BEGIN
39
40
41 user_id := FND_GLOBAL.USER_ID;
42 FND_FILE.PUT_LINE(FND_FILE.LOG, 'User_Id ='||to_char(user_id));
43
44 OKS_ARFETCH_PUB.GET_AR_RECORD(l_return_status);
45 --OKS_ARFETCH_PUB.GET_AR_RECORD(l_retcode);
46
47 --l_retcode := SUCCESS;
48
49 IF (l_return_status = 'S') THEN
50 FND_FILE.PUT_LINE( FND_FILE.LOG,
51 'ARFetch_Main IS successfully completed');
52 ELSE
53 FND_FILE.PUT_LINE( FND_FILE.LOG,
54 'ARFetch_Main is NOT successfully completed' );
55 END IF;
56
57
58 --FND_FILE.PUT_LINE (FND_FILE.LOG,'RETCODE = ' || to_char(l_retcode));
59
60 COMMIT;
61
62 IF (conc_ret_code = SUCCESS) THEN
63 RETCODE := 0;
64 ELSE
65 RETCODE := 1;
66 END IF;
67
68
69 EXCEPTION
70
71 WHEN UTL_FILE.INVALID_PATH THEN
72 --DBMS_OUTPUT.PUT_LINE ('FILE LOCATION OR NAME WAS INVALID');
73 null;
74 WHEN UTL_FILE.INVALID_MODE THEN
75 --DBMS_OUTPUT.PUT_LINE ('FILE OPEN MODE STRING WAS INVALID');
76 null;
77 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
78 --DBMS_OUTPUT.PUT_LINE ('FILE HANDLE WAS INVALID');
79 null;
80 WHEN UTL_FILE.INVALID_OPERATION THEN
81 --DBMS_OUTPUT.PUT_LINE ('FILE IS NOT OPEN FOR WRITTING');
82 null;
83 WHEN UTL_FILE.WRITE_ERROR THEN
84 --DBMS_OUTPUT.PUT_LINE ('OS ERROR OCCURRED DURING WRITE OPERATION');
85 null;
86
87 End ARFetch_Main;
88
89
90 ---------------------------------------------------------------------------
91 -- PROCEDURE GET_AR_RECORD
92 ---------------------------------------------------------------------------
93 PROCEDURE Get_AR_RECORD ( x_return_status OUT NOCOPY VARCHAR2) AS
94
95 TYPE btl_record IS RECORD
96 (txn_lines_id NUMBER,
97 bill_instance_number NUMBER,
98 btn_id NUMBER,
99 bcl_id NUMBER,
100 bsl_id NUMBER,
101 trx_amount NUMBER);
102
103 TYPE btl_tbl IS TABLE OF btl_record index by binary_integer;
104
105 type l_number_tbl is table of number index by binary_integer ;
106 type l_varchar2_tbl is table of varchar2(120) index by binary_integer;
107 type l_date_tbl is table of date index by binary_integer;
108
109 l_txn_id l_number_tbl;
110 l_txn_lines_id l_number_tbl;
111 l_bill_instance_number l_number_tbl;
112 l_btn_id l_number_tbl;
113 l_bcl_id l_number_tbl;
114 l_bsl_id l_number_tbl;
115 l_split_flag l_varchar2_tbl;
116 l_Contract_number l_varchar2_tbl;
117 l_Contract_number_modifier l_varchar2_tbl;
118 l_last_update_date l_date_tbl;
119 l_hdr_id l_number_tbl;
120 l_trx_number l_varchar2_tbl;
121 l_customer_trx_line_id l_number_tbl;
122 l_customer_trx_id l_number_tbl;
123 l_extended_amount l_number_tbl;
124 l_type l_varchar2_tbl;
125 l_trx_date l_date_tbl;
126 l_hdr_id_tmp l_number_tbl;
127 l_currency_code l_varchar2_tbl;
128
129 --Added leading, use_hash and swap_join_inputs along with the paralle.
130 --Also changed the order of FROM clause
131 --Above two changes were did for bug fix 5903326(FP bug for 5882789)
132 --SQL has been modified after talking to performance team.
133 --So Do not change the order of the from clause and the hints.
134
135 --This FP fix is slightly different than the 11.5.10 fix due to the
136 --MOAC functionality. Among all the MOAC enabled tables only one is with
137 --the MOAC predicate and others are with the _ALL table(this is as per MOAC standard
138
139 /*Modified the hints as per the appsperf team suggestion for bug9313726*/
140 -- Cursor get_fetch_records_csr is
141 /* SELECT /*+ leading(BTN,BTXNL,RALINES,HDR,RAHDR) use_nl(BTXNL,HDR,RAHDR) use_hash(RATYPES) swap_join_inputs(RATYPES) parallel(RALINES) */
142
143 Cursor get_fetch_records_csr is
144 SELECT /*+ leading(BTN, BTXNL, RALINES, HDR, RAHDR) use_nl(BTXNL) */
145 btn.id txn_id
146 ,btn.currency_code
147 ,btxnl.id txn_lines_id
148 ,btxnl.bill_instance_number bill_instance_number
149 ,btxnl.btn_id btn_id
150 ,btxnl.bcl_id
151 ,btxnl.bsl_id
152 ,btxnl.split_flag
153 ,hdr.Contract_number
154 ,hdr.Contract_number_modifier
155 ,hdr.last_update_date
156 ,hdr.id hdr_id
157 ,rahdr.trx_number
158 ,ralines.customer_trx_line_id
159 ,ralines.customer_trx_id
160 ,ralines.extended_amount
161 ,ratypes.type
162 ,rahdr.trx_date
163 From oks_bill_transactions btn
164 ,oks_bill_txn_lines btxnl
165 ,RA_CUSTOMER_TRX_LINES RALINES
166 ,okc_k_headers_all_b hdr
167 ,RA_CUSTOMER_TRX_ALL RAHDR
168 ,RA_CUST_TRX_TYPES_ALL RATYPES
169 Where btxnl.btn_id = btn.id
170 And btn.trx_number = '-99'
171 AND RALINES.line_type ='LINE'
172 /* Commented by sjanakir for Bug#7190512
173 And RAHDR.interface_header_attribute1 = hdr.contract_number
174 And RAHDR.interface_header_attribute2 = NVL(hdr.contract_number_modifier,'-') */
175 And RALINES.interface_line_attribute1 = hdr.contract_number
176 And RALINES.interface_line_attribute2 = NVL(hdr.contract_number_modifier,'-')
177 And RALINES.interface_line_attribute3 = to_char(btxnl.bill_instance_number)
178 And RAHDR.customer_trx_id = RALINES.customer_trx_id
179 And RATYPES.cust_trx_type_id = RAHDR.cust_trx_type_id
180 And RALINES.interface_line_context = 'OKS CONTRACTS'
181 And ralines.org_id = HDR.org_id
182 And ralines.org_id = RAHDR.org_id
183 And ralines.org_id = RATYPES.org_id
184 ORDER BY btxnl.bill_instance_number ;
185
186 ---DON'T REMOVE ORDER BY CLAUSE added for bug#4089706
187
188 Cursor l_hdr_csr is
189 Select distinct hdr_id
190 From oks_ar_fetch_temp;
191
192 CURSOR l_btl_csr(p_bill_instance_num NUMBER) IS
193 SELECT id txn_lines_id
194 ,bill_instance_number
195 ,btn_id
196 ,bcl_id
197 ,bsl_id
198 ,trx_amount
199 FROM oks_bill_txn_lines
200 WHERE bill_instance_number = p_bill_instance_num;
201
202 CURSOR l_ra_tax_csr(p_id NUMBER) IS
203 SELECT nvl(sum(ctl.extended_amount),0 )
204 FROM RA_CUSTOMER_TRX_LINES_ALL CTL
205 WHERE CTL.LINK_TO_CUST_TRX_LINE_ID = p_id
206 AND CTL.line_type = 'TAX';
207
208
209
210 l_btl_rec l_btl_csr%ROWTYPE;
211 l_btl_tbl btl_tbl;
212 l_tot_tax_amt NUMBER;
213 l_remaining_trx_amt NUMBER;
214 l_remaining_tax_amt NUMBER;
215 l_line_trx_amt NUMBER;
216 l_line_tax_amt NUMBER;
217 l_total_amt NUMBER;
218 l_index NUMBER;
219 l_previous_btn NUMBER;
220
221
222
223 l_ret_stat VARCHAR2(20);
224 l_msg_cnt NUMBER;
225 l_msg_data VARCHAR2(2000);
226
227 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type ;
228 l_cvmv_out_rec OKC_CVM_PVT.cvmv_rec_type ;
229
230 BEGIN
231 DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
232 x_return_status := 'S';
233 Open get_fetch_records_csr;
234 Loop
235 Fetch get_fetch_records_csr bulk collect into l_txn_id
236 ,l_currency_code
237 ,l_txn_lines_id
238 ,l_bill_instance_number
239 ,l_btn_id
240 ,l_bcl_id
241 ,l_bsl_id
242 ,l_split_flag
243 ,l_Contract_number
244 ,l_Contract_number_modifier
245 ,l_last_update_date
246 ,l_hdr_id
247 ,l_trx_number
248 ,l_customer_trx_line_id
249 ,l_customer_trx_id
250 ,l_extended_amount
251 ,l_type
252 ,l_trx_date limit 1000;
253 If l_txn_id.COUNT > 0 then
254 Begin
255 forall i in l_txn_id.FIRST..l_txn_id.LAST
256 update oks_bill_transactions
257 set trx_date = l_trx_date(i)
258 ,trx_number = l_trx_number(i)
259 ,trx_amount = nvl(trx_amount,0) + l_extended_amount(i)
260 ,trx_class = l_type(i)
261 ,last_updated_by = user_id
262 ,last_update_date = sysdate
263 where id = l_txn_id(i);
264 Exception
265 When others then
266 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update failed on OKS_BILL_TRANSACTIONS , SQLERRM = '|| SQLERRM);
267 Raise;
268 End;
269
270 BEGIN
271 FOR i in l_txn_lines_id.FIRST..l_txn_lines_id.LAST
272 LOOP
273 /*****chk the split flag ,if null just update as usual else if 'P' then
274 retrieve all records from btl with same bill_instance_number and prorate the tax and inv amt.
275 *******/
276
277 IF l_split_flag(i) IS NULL THEN
278
279 /*******Added for P1 bug#4089706. chk previous bill_instance_number if not same then
280 update trx_line_amt and tax_amt to 0 so that for the price break records
281 amt can be added.But later on we have to identify these records******/
282
283 IF l_txn_lines_id(i) <> nvl(l_previous_btn,0) then
284
285 UPDATE oks_bill_txn_lines
286 SET trx_line_tax_amount = 0
287 ,trx_amount = 0
288 ,trx_line_amount = 0
289 WHERE id = l_txn_lines_id(i);
290 END IF;
291
292 /***taking tax amt from cursor because of P1 bug#4125597.
293 before it been added directly from sql in update statment****/
294
295 l_tot_tax_amt := 0;
296
297 ---find the tax amt
298 OPEN l_ra_tax_csr(l_customer_trx_line_id(i));
299 FETCH l_ra_tax_csr INTO l_tot_tax_amt;
300 CLOSE l_ra_tax_csr;
301
302 UPDATE oks_bill_txn_lines
303 SET trx_class = l_type(i)
304 ,trx_number = l_trx_number(i)
305 ,trx_date = l_trx_date(i)
306 ,trx_line_tax_amount = nvl(trx_line_tax_amount,0) + nvl(l_tot_tax_amt,0)
307 ,trx_amount = nvl(trx_amount,0) + l_extended_amount(i)
308 ,trx_line_amount = nvl(trx_line_amount,0) + l_extended_amount(i)
309 ,last_updated_by = user_id
310 ,last_update_date = sysdate
311 WHERE id = l_txn_lines_id(i);
312
313 l_previous_btn := l_txn_lines_id(i);
314
315 ELSIF l_split_flag(i) = 'P' THEN
316 ---Logic to update all records in btl with same bill_instance_number in loop
317
318 l_total_amt := 0;
319 l_tot_tax_amt := 0;
320
321 ---find the tax amt
322 OPEN l_ra_tax_csr(l_customer_trx_line_id(i));
323 FETCH l_ra_tax_csr INTO l_tot_tax_amt;
324 CLOSE l_ra_tax_csr;
325
326 l_btl_tbl.DELETE;
327 l_index := 1;
328
329 FOR l_btl_rec IN l_btl_csr(l_bill_instance_number(i))
330 LOOP
331
332 l_btl_tbl(l_index).txn_lines_id := l_btl_rec.txn_lines_id;
333 l_btl_tbl(l_index).bill_instance_number := l_btl_rec.bill_instance_number;
334 l_btl_tbl(l_index).btn_id := l_btl_rec.btn_id ;
335 l_btl_tbl(l_index).bcl_id := l_btl_rec.bcl_id ;
336 l_btl_tbl(l_index).bsl_id := l_btl_rec.bsl_id ;
337 l_btl_tbl(l_index).trx_amount := l_btl_rec.trx_amount;
338
339
340 l_total_amt := NVL( l_total_amt,0) + l_btl_tbl(l_index).trx_amount;
341 l_index := l_index + 1;
342 END LOOP;
343
344 l_remaining_trx_amt := NVL(l_extended_amount(i),0);
345 l_remaining_tax_amt := NVL(l_tot_tax_amt,0) ;
346
347 IF l_btl_tbl.COUNT > 0 THEN
348
349 FOR l_index IN l_btl_tbl.FIRST .. l_btl_tbl.LAST
350 LOOP
351
352 IF l_index = l_btl_tbl.LAST THEN
353 l_line_trx_amt := l_remaining_trx_amt;
354 l_line_tax_amt := l_remaining_tax_amt;
355 ELSE ---not last one
356
357 IF l_total_amt = 0 THEN
358 l_line_trx_amt := 0;
359 l_line_tax_amt := 0;
360 ELSE
361
362 l_line_trx_amt := OKS_EXTWAR_UTIL_PVT.round_currency_amt(
363 (l_extended_amount(i)/l_total_amt) * l_btl_tbl(l_index).trx_amount,
364 l_currency_code(i)) ;
365
366 l_line_tax_amt := OKS_EXTWAR_UTIL_PVT.round_currency_amt(
367 (l_tot_tax_amt/l_total_amt) * l_btl_tbl(l_index).trx_amount,
368 l_currency_code(i)) ;
369 END IF;
370 l_remaining_trx_amt := NVL(l_remaining_trx_amt,0) - NVL(l_line_trx_amt,0);
371 l_remaining_tax_amt := NVL(l_remaining_tax_amt,0) - NVL(l_line_tax_amt,0);
372 END IF; ---last index chk
373
374
375
376 UPDATE oks_bill_txn_lines
377 SET trx_class = l_type(i)
378 ,trx_number = l_trx_number(i)
379 ,trx_date = l_trx_date(i)
380 ,trx_amount = l_line_trx_amt
381 ,trx_line_amount = l_line_trx_amt
382 ,trx_line_tax_amount = l_line_tax_amt
383 ,last_updated_by = user_id
384 ,last_update_date = sysdate
385 WHERE id = l_btl_tbl(l_index).txn_lines_id;
386
387 END LOOP; ---l_btl_tbl loop
388 END IF; ---l_btl_tbl count chk
389
390 END IF; ---split_flag chk
391 END LOOP; ----end loop for l_txn_lines_id tbl
392
393
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update failed on OKS_BILL_TXN_LINES , SQLERRM = '|| SQLERRM);
398 Raise;
399 END;
400
401 Begin
402 forall k in l_hdr_id.FIRST..l_hdr_id.LAST
403 insert into oks_ar_fetch_temp o
404 ( hdr_id )
405 values ( l_hdr_id(k));
406 Exception
407 When others then
408 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert failed on OKS_AR_FETCH_TEMP , SQLERRM = '|| SQLERRM);
409 Raise;
410 End ;
411 End If;
412 Exit when get_fetch_records_csr%NOTFOUND;
413 End Loop;
414 close get_fetch_records_csr;
415
416 open l_hdr_csr;
417 Loop
418 fetch l_hdr_csr bulk collect into l_hdr_id_tmp limit 10000;
419 If l_hdr_id_tmp.COUNT > 0 then
420 For i in l_hdr_id_tmp.FIRST..l_hdr_id_tmp.lAST
421 Loop
422 okc_cvm_pvt.g_trans_id := 'XXX';
423 l_cvmv_rec.chr_id := l_hdr_id_tmp(i);
424 OKC_CVM_PVT.update_contract_version( p_api_version => 1.0,
425 p_init_msg_list => 'T',
426 x_return_status => l_ret_stat,
427 x_msg_count => l_msg_cnt,
428 x_msg_data => l_msg_data,
429 p_cvmv_rec => l_cvmv_rec,
430 x_cvmv_rec => l_cvmv_out_rec);
431 End Loop;
432 End If;
433 Exit when l_hdr_csr%NOTFOUND;
434 End loop;
435 close l_hdr_csr;
436
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 x_return_status := 'E';
441 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
442 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error'||SQLCODE || '- '||SQLERRM);
443 END Get_AR_RECORD;
444
445 END OKS_ARFETCH_PUB;
[Home] [Help]