1 Package Body OKS_ARFETCH_PUB AS
2 /* $Header: OKSPARGB.pls 120.2.12010000.2 2008/10/22 12:50:11 ssreekum 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 Cursor get_fetch_records_csr is
140 SELECT /*+ leading(BTN,BTXNL,RALINES,HDR,RAHDR) use_nl(BTXNL,HDR,RAHDR) use_hash(RATYPES) swap_join_inputs(RATYPES) parallel(RALINES) */
141 btn.id txn_id
142 ,btn.currency_code
143 ,btxnl.id txn_lines_id
144 ,btxnl.bill_instance_number bill_instance_number
145 ,btxnl.btn_id btn_id
146 ,btxnl.bcl_id
147 ,btxnl.bsl_id
148 ,btxnl.split_flag
149 ,hdr.Contract_number
150 ,hdr.Contract_number_modifier
151 ,hdr.last_update_date
152 ,hdr.id hdr_id
153 ,rahdr.trx_number
154 ,ralines.customer_trx_line_id
155 ,ralines.customer_trx_id
156 ,ralines.extended_amount
157 ,ratypes.type
158 ,rahdr.trx_date
159 From oks_bill_transactions btn
160 ,oks_bill_txn_lines btxnl
161 ,RA_CUSTOMER_TRX_LINES RALINES
162 ,okc_k_headers_all_b hdr
163 ,RA_CUSTOMER_TRX_ALL RAHDR
164 ,RA_CUST_TRX_TYPES_ALL RATYPES
165 Where btxnl.btn_id = btn.id
166 And btn.trx_number = '-99'
167 AND RALINES.line_type ='LINE'
168 /* Commented by sjanakir for Bug#7190512
169 And RAHDR.interface_header_attribute1 = hdr.contract_number
170 And RAHDR.interface_header_attribute2 = NVL(hdr.contract_number_modifier,'-') */
171 And RALINES.interface_line_attribute1 = hdr.contract_number
172 And RALINES.interface_line_attribute2 = NVL(hdr.contract_number_modifier,'-')
173 And RALINES.interface_line_attribute3 = to_char(btxnl.bill_instance_number)
174 And RAHDR.customer_trx_id = RALINES.customer_trx_id
175 And RATYPES.cust_trx_type_id = RAHDR.cust_trx_type_id
176 And RALINES.interface_line_context = 'OKS CONTRACTS'
177 And ralines.org_id = HDR.org_id
178 And ralines.org_id = RAHDR.org_id
179 And ralines.org_id = RATYPES.org_id
180 ORDER BY btxnl.bill_instance_number ;
181
182 ---DON'T REMOVE ORDER BY CLAUSE added for bug#4089706
183
184 Cursor l_hdr_csr is
185 Select distinct hdr_id
186 From oks_ar_fetch_temp;
187
188 CURSOR l_btl_csr(p_bill_instance_num NUMBER) IS
189 SELECT id txn_lines_id
190 ,bill_instance_number
191 ,btn_id
192 ,bcl_id
193 ,bsl_id
194 ,trx_amount
195 FROM oks_bill_txn_lines
196 WHERE bill_instance_number = p_bill_instance_num;
197
198 CURSOR l_ra_tax_csr(p_id NUMBER) IS
199 SELECT nvl(sum(ctl.extended_amount),0 )
200 FROM RA_CUSTOMER_TRX_LINES_ALL CTL
201 WHERE CTL.LINK_TO_CUST_TRX_LINE_ID = p_id
202 AND CTL.line_type = 'TAX';
203
204
205
206 l_btl_rec l_btl_csr%ROWTYPE;
207 l_btl_tbl btl_tbl;
208 l_tot_tax_amt NUMBER;
209 l_remaining_trx_amt NUMBER;
210 l_remaining_tax_amt NUMBER;
211 l_line_trx_amt NUMBER;
212 l_line_tax_amt NUMBER;
213 l_total_amt NUMBER;
214 l_index NUMBER;
215 l_previous_btn NUMBER;
216
217
218
219 l_ret_stat VARCHAR2(20);
220 l_msg_cnt NUMBER;
221 l_msg_data VARCHAR2(2000);
222
223 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type ;
224 l_cvmv_out_rec OKC_CVM_PVT.cvmv_rec_type ;
225
226 BEGIN
227 DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
228 x_return_status := 'S';
229 Open get_fetch_records_csr;
230 Loop
231 Fetch get_fetch_records_csr bulk collect into l_txn_id
232 ,l_currency_code
233 ,l_txn_lines_id
234 ,l_bill_instance_number
235 ,l_btn_id
236 ,l_bcl_id
237 ,l_bsl_id
238 ,l_split_flag
239 ,l_Contract_number
240 ,l_Contract_number_modifier
241 ,l_last_update_date
242 ,l_hdr_id
243 ,l_trx_number
244 ,l_customer_trx_line_id
245 ,l_customer_trx_id
246 ,l_extended_amount
247 ,l_type
248 ,l_trx_date limit 1000;
249 If l_txn_id.COUNT > 0 then
250 Begin
251 forall i in l_txn_id.FIRST..l_txn_id.LAST
252 update oks_bill_transactions
253 set trx_date = l_trx_date(i)
254 ,trx_number = l_trx_number(i)
255 ,trx_amount = nvl(trx_amount,0) + l_extended_amount(i)
256 ,trx_class = l_type(i)
257 ,last_updated_by = user_id
258 ,last_update_date = sysdate
259 where id = l_txn_id(i);
260 Exception
261 When others then
262 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update failed on OKS_BILL_TRANSACTIONS , SQLERRM = '|| SQLERRM);
263 Raise;
264 End;
265
266 BEGIN
267 FOR i in l_txn_lines_id.FIRST..l_txn_lines_id.LAST
268 LOOP
269 /*****chk the split flag ,if null just update as usual else if 'P' then
270 retrieve all records from btl with same bill_instance_number and prorate the tax and inv amt.
271 *******/
272
273 IF l_split_flag(i) IS NULL THEN
274
275 /*******Added for P1 bug#4089706. chk previous bill_instance_number if not same then
276 update trx_line_amt and tax_amt to 0 so that for the price break records
277 amt can be added.But later on we have to identify these records******/
278
279 IF l_txn_lines_id(i) <> nvl(l_previous_btn,0) then
280
281 UPDATE oks_bill_txn_lines
282 SET trx_line_tax_amount = 0
283 ,trx_amount = 0
284 ,trx_line_amount = 0
285 WHERE id = l_txn_lines_id(i);
286 END IF;
287
288 /***taking tax amt from cursor because of P1 bug#4125597.
289 before it been added directly from sql in update statment****/
290
291 l_tot_tax_amt := 0;
292
293 ---find the tax amt
294 OPEN l_ra_tax_csr(l_customer_trx_line_id(i));
295 FETCH l_ra_tax_csr INTO l_tot_tax_amt;
296 CLOSE l_ra_tax_csr;
297
298 UPDATE oks_bill_txn_lines
299 SET trx_class = l_type(i)
300 ,trx_number = l_trx_number(i)
301 ,trx_date = l_trx_date(i)
302 ,trx_line_tax_amount = nvl(trx_line_tax_amount,0) + nvl(l_tot_tax_amt,0)
303 ,trx_amount = nvl(trx_amount,0) + l_extended_amount(i)
304 ,trx_line_amount = nvl(trx_line_amount,0) + l_extended_amount(i)
305 ,last_updated_by = user_id
306 ,last_update_date = sysdate
307 WHERE id = l_txn_lines_id(i);
308
309 l_previous_btn := l_txn_lines_id(i);
310
311 ELSIF l_split_flag(i) = 'P' THEN
312 ---Logic to update all records in btl with same bill_instance_number in loop
313
314 l_total_amt := 0;
315 l_tot_tax_amt := 0;
316
317 ---find the tax amt
318 OPEN l_ra_tax_csr(l_customer_trx_line_id(i));
319 FETCH l_ra_tax_csr INTO l_tot_tax_amt;
320 CLOSE l_ra_tax_csr;
321
322 l_btl_tbl.DELETE;
323 l_index := 1;
324
325 FOR l_btl_rec IN l_btl_csr(l_bill_instance_number(i))
326 LOOP
327
328 l_btl_tbl(l_index).txn_lines_id := l_btl_rec.txn_lines_id;
329 l_btl_tbl(l_index).bill_instance_number := l_btl_rec.bill_instance_number;
330 l_btl_tbl(l_index).btn_id := l_btl_rec.btn_id ;
331 l_btl_tbl(l_index).bcl_id := l_btl_rec.bcl_id ;
332 l_btl_tbl(l_index).bsl_id := l_btl_rec.bsl_id ;
333 l_btl_tbl(l_index).trx_amount := l_btl_rec.trx_amount;
334
335
336 l_total_amt := NVL( l_total_amt,0) + l_btl_tbl(l_index).trx_amount;
337 l_index := l_index + 1;
338 END LOOP;
339
340 l_remaining_trx_amt := NVL(l_extended_amount(i),0);
341 l_remaining_tax_amt := NVL(l_tot_tax_amt,0) ;
342
343 IF l_btl_tbl.COUNT > 0 THEN
344
345 FOR l_index IN l_btl_tbl.FIRST .. l_btl_tbl.LAST
346 LOOP
347
348 IF l_index = l_btl_tbl.LAST THEN
349 l_line_trx_amt := l_remaining_trx_amt;
350 l_line_tax_amt := l_remaining_tax_amt;
351 ELSE ---not last one
352
353 IF l_total_amt = 0 THEN
354 l_line_trx_amt := 0;
355 l_line_tax_amt := 0;
356 ELSE
357
358 l_line_trx_amt := OKS_EXTWAR_UTIL_PVT.round_currency_amt(
359 (l_extended_amount(i)/l_total_amt) * l_btl_tbl(l_index).trx_amount,
360 l_currency_code(i)) ;
361
362 l_line_tax_amt := OKS_EXTWAR_UTIL_PVT.round_currency_amt(
363 (l_tot_tax_amt/l_total_amt) * l_btl_tbl(l_index).trx_amount,
364 l_currency_code(i)) ;
365 END IF;
366 l_remaining_trx_amt := NVL(l_remaining_trx_amt,0) - NVL(l_line_trx_amt,0);
367 l_remaining_tax_amt := NVL(l_remaining_tax_amt,0) - NVL(l_line_tax_amt,0);
368 END IF; ---last index chk
369
370
371
372 UPDATE oks_bill_txn_lines
373 SET trx_class = l_type(i)
374 ,trx_number = l_trx_number(i)
375 ,trx_date = l_trx_date(i)
376 ,trx_amount = l_line_trx_amt
377 ,trx_line_amount = l_line_trx_amt
378 ,trx_line_tax_amount = l_line_tax_amt
379 ,last_updated_by = user_id
380 ,last_update_date = sysdate
381 WHERE id = l_btl_tbl(l_index).txn_lines_id;
382
383 END LOOP; ---l_btl_tbl loop
384 END IF; ---l_btl_tbl count chk
385
386 END IF; ---split_flag chk
387 END LOOP; ----end loop for l_txn_lines_id tbl
388
389
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update failed on OKS_BILL_TXN_LINES , SQLERRM = '|| SQLERRM);
394 Raise;
395 END;
396
397 Begin
398 forall k in l_hdr_id.FIRST..l_hdr_id.LAST
399 insert into oks_ar_fetch_temp o
400 ( hdr_id )
401 values ( l_hdr_id(k));
402 Exception
403 When others then
404 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert failed on OKS_AR_FETCH_TEMP , SQLERRM = '|| SQLERRM);
405 Raise;
406 End ;
407 End If;
408 Exit when get_fetch_records_csr%NOTFOUND;
409 End Loop;
410 close get_fetch_records_csr;
411
412 open l_hdr_csr;
413 Loop
414 fetch l_hdr_csr bulk collect into l_hdr_id_tmp limit 10000;
415 If l_hdr_id_tmp.COUNT > 0 then
416 For i in l_hdr_id_tmp.FIRST..l_hdr_id_tmp.lAST
417 Loop
418 okc_cvm_pvt.g_trans_id := 'XXX';
419 l_cvmv_rec.chr_id := l_hdr_id_tmp(i);
420 OKC_CVM_PVT.update_contract_version( p_api_version => 1.0,
421 p_init_msg_list => 'T',
422 x_return_status => l_ret_stat,
423 x_msg_count => l_msg_cnt,
424 x_msg_data => l_msg_data,
425 p_cvmv_rec => l_cvmv_rec,
426 x_cvmv_rec => l_cvmv_out_rec);
427 End Loop;
428 End If;
429 Exit when l_hdr_csr%NOTFOUND;
430 End loop;
431 close l_hdr_csr;
432
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 x_return_status := 'E';
437 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
438 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error'||SQLCODE || '- '||SQLERRM);
439 END Get_AR_RECORD;
440
441 END OKS_ARFETCH_PUB;
[Home] [Help]