1 package body iby_sched as
2 /*$Header: ibyscfib.pls 120.17 2011/12/29 09:49:08 sugottum ship $*/
3
4 TYPE ecapp_rec_type IS RECORD (
5 ecappid NUMBER,
6 app_short_name VARCHAR2(100)
7 );
8 TYPE ecappTabType IS TABLE OF ecapp_rec_type INDEX BY BINARY_INTEGER;
9
10 procedure cardInfo (in_payerinstrid in iby_trans_fi_v.payerinstrid%type,
11 in_payeeid in iby_trans_fi_v.payeeid%type,
12 in_tangibleid in iby_trans_fi_v.tangibleid%type,
13 out_ccnumber_from out nocopy iby_creditcard_v.ccnumber%type,
14 out_expdate_from out nocopy iby_creditcard_v.expirydate%type,
15 out_accttype_from out nocopy iby_accttype.accttype%type,
16 out_name out nocopy varchar2,
17 out_bankid_to out nocopy iby_ext_bank_accounts_v.bank_party_id%type,
18 out_branchid_to out nocopy iby_ext_bank_accounts_v.branch_party_id%type,
19 out_acctid_to out nocopy iby_ext_bank_accounts_v.ext_bank_account_id%type,
20 out_accttype_to out nocopy iby_accttype.accttype%type,
21 out_acctno out nocopy iby_tangible.acctno%type,
22 out_refinfo out nocopy iby_tangible.refinfo%type,
23 out_memo out nocopy iby_tangible.memo%type,
24 out_currency out nocopy iby_tangible.currencynamecode%type)
25 is
26 cursor c_userCardInfo (cin_payerinstrid in iby_trans_fi_v.payerinstrid%type) is
27
28 select ccnumber,
29 expirydate,
30 accttype
31 from iby_creditcard_v c,
32 iby_trxn_summaries_all d
33 where d.payerinstrid = cin_payerinstrid
34 and d.payerinstrid = c.instrid;
35
36 cursor c_payeeBankInfo (cin_payeeid in iby_trans_fi_v.payeeid%type) is
37 select b.bank_party_id,
38 b.branch_party_id,
39 b.ext_bank_account_id,
40 b.bank_account_type
41 from iby_ext_bank_accounts_v b,
42 iby_trxn_summaries_all d
43 where d.payeeid = cin_payeeid
44 and d.payeeinstrid = b.ext_bank_account_id;
45
46 cursor c_tangibleInfo (cin_tangibleid in iby_trans_fi_v.tangibleid%type) is
47 select acctno,
48 refinfo,
49 memo,
50 currencynamecode
51 from iby_tangible t
52 where t.tangibleid = cin_tangibleid;
53 BEGIN
54 -- get the user's credit card information
55 open c_userCardInfo(in_payerinstrid);
56 fetch c_userCardInfo into out_ccnumber_from,
57 out_expdate_from,
58 out_accttype_from;
59 if c_userCardInfo%NOTFOUND
60 then raise_application_error(-20099, 'No user credit card info found');
61 end if;
62 close c_userCardInfo;
63
64 -- get the payee's bank account information
65 open c_payeeBankInfo(in_payeeid);
66 fetch c_payeeBankInfo into out_bankid_to,
67 out_branchid_to,
68 out_acctid_to,
69 out_accttype_to;
70 if c_payeeBankInfo%NOTFOUND then
71 raise_application_error(-20092, 'No payee bank account info found');
72 end if;
73 close c_payeeBankInfo;
74 -- get the tangible information
75 open c_tangibleInfo(in_tangibleid);
76 fetch c_tangibleInfo into out_acctno,
77 out_refinfo,
78 out_memo,
79 out_currency;
80 if c_tangibleInfo%NOTFOUND
81 then raise_application_error(-20093, 'No tangible info found');
82 end if;
83 close c_tangibleInfo;
84 end cardInfo;
85
86 procedure bankInfo (in_payerinstrid in iby_trans_fi_v.payerinstrid%type,
87 in_payeeid in iby_trans_fi_v.payeeid%type,
88 in_tangibleid in iby_trans_fi_v.tangibleid%type,
89 out_bankid_from out nocopy iby_ext_bank_accounts_v.bank_party_id%type,
90 out_branchid_from out nocopy iby_ext_bank_accounts_v.branch_party_id%type,
91 out_acctid_from out nocopy iby_ext_bank_accounts_v.ext_bank_account_id%type,
92 out_accttype_from out nocopy iby_accttype.accttype%type,
93 out_name out nocopy varchar2,
94 out_bankid_to out nocopy iby_ext_bank_accounts_v.bank_party_id%type,
95 out_branchid_to out nocopy iby_ext_bank_accounts_v.branch_party_id%type,
96 out_acctid_to out nocopy iby_ext_bank_accounts_v.ext_bank_account_id%type,
97 out_accttype_to out nocopy iby_accttype.accttype%type,
98 out_acctno out nocopy iby_tangible.acctno%type,
99 out_refinfo out nocopy iby_tangible.refinfo%type,
100 out_memo out nocopy iby_tangible.memo%type,
101 out_currency out nocopy iby_tangible.currencynamecode%type)
102 is
103 cursor c_userBankInfo (cin_payerinstrid in iby_trans_fi_v.payerinstrid%type) is
104
105 select b.bank_party_id,
106 b.branch_party_id,
107 b.ext_bank_account_id,
108 b.bank_account_type
109 from iby_ext_bank_accounts_v b,
110 iby_trxn_summaries_all d
111 where d.payerinstrid = cin_payerinstrid
112 and d.payerinstrid = b.ext_bank_account_id;
113
114
115 cursor c_payeeBankInfo (cin_payeeid in iby_trans_fi_v.payeeid%type) is
116 select b.bank_party_id,
117 b.branch_party_id,
118 b.ext_bank_account_id,
119 b.bank_account_type
120 from iby_ext_bank_accounts_v b,
121 iby_trxn_summaries_all d
122 where d.payeeid = cin_payeeid
123 and d.payeeinstrid = b.ext_bank_account_id;
124
125 cursor c_tangibleInfo (cin_tangibleid in iby_trans_fi_v.tangibleid%type) is
126 select acctno,
127 refinfo,
128 memo,
129 currencynamecode
130 from iby_tangible t
131 where t.tangibleid = cin_tangibleid;
132 begin
133 -- get the user's bank account information
134 open c_userBankInfo (in_payerinstrid);
135 fetch c_userBankInfo into out_bankid_from,
136 out_branchid_from,
137 out_acctid_from,
138 out_accttype_from;
139 if c_userBankInfo%NOTFOUND
140 then raise_application_error(-20094, 'No user bank account info found');
141 end if;
142 close c_userBankInfo;
143
144 -- get the payee's bank account information
145 open c_payeeBankInfo (in_payeeid);
146 fetch c_payeeBankInfo into out_bankid_to,
147 out_branchid_to,
148 out_acctid_to,
149 out_accttype_to;
150 if c_payeeBankInfo%NOTFOUND
151 then raise_application_error(-20092, 'No payee bank account info found');
152 end if;
153 close c_payeeBankInfo;
154 -- get the tangible information
155 open c_tangibleInfo (in_tangibleid);
156 fetch c_tangibleInfo into out_acctno,
157 out_refinfo,
158 out_memo,
159 out_currency;
160 if c_tangibleInfo%NOTFOUND
161 then raise_application_error(-20093, 'No tangible info found');
162 end if;
163 close c_tangibleInfo;
164 end bankInfo;
165
166
167 -- Overloaded procedure
168 procedure update_ecapp is
169 TYPE txn_mid_TabTyp is TABLE OF iby_trans_core_v.trxnmid%TYPE
170 INDEX BY BINARY_INTEGER;
171
172 o_status VARCHAR2(80);
173 o_errcode VARCHAR2(80);
174 o_errmsg VARCHAR2(80);
175
176 txn_id_Tab JTF_VARCHAR2_TABLE_100;
177 Status_Tab JTF_NUMBER_TABLE;
178 reqtype_Tab JTF_VARCHAR2_TABLE_100;
179 updatedt_Tab JTF_DATE_TABLE;
180 refcode_Tab JTF_VARCHAR2_TABLE_100;
181 o_statusindiv_Tab JTF_VARCHAR2_TABLE_100;
182
183 txn_mid_Tab txn_mid_TabTyp;
184
185 -- String and cursors for dynamic PL/SQL
186 ecapp_name VARCHAR2(30);
187 v_procString VARCHAR2(1000);
188 v_NumRows INTEGER;
189 totalRows INTEGER;
190 extendRows INTEGER:=1;
191 l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_SCHED.update_ecapp';
192 i NUMBER := 0;
193 l_objectCount INTEGER;
194 l_recordCounter NUMBER:=0;
195 l_object_owner VARCHAR2(100);
196 l_updapp_success BOOLEAN;
197
198 CURSOR ecapp_cursor(cin_owner VARCHAR2) IS
199 SELECT distinct(a.ECAPPID),
200 a.APPLICATION_SHORT_NAME FROM IBY_ECAPP_V a
201 WHERE EXISTS
202 (SELECT * FROM dba_objects b WHERE
203 b.object_name = a.application_short_name || '_ECAPP_PKG' and
204 owner=cin_owner);
205
206 -- Updated the where clause so that the transactions initiated from
207 -- OM will also be picked up this cursor (bug# 8239041)
208 -- Replaced view with base tables
209 -- Bug# 9397208
210 -- Bug# 13540887
211 -- Adding 11, 100, 111 to the statuses
212 CURSOR c_trans_core (cin_ecappid in iby_ecapp.ecappid%type) IS
213 SELECT /*+ NO_EXPAND use_nl(B C) INDEX (C IBY_TRXN_CORE_TRXNMID_U1)*/
214 b.transactionid,
215 b.status,
216 b.updatedate,
217 b.reqtype,
218 c.referencecode,
219 b.trxnmid
220 FROM iby_trxn_summaries_all b,
221 iby_trxn_core c
222 WHERE(b.trxnmid = c.trxnmid)
223 AND b.reqtype <> 'ORAPMTREQ'
224 AND b.status not in (1, 0, 11, 100, 111)
225 AND b.needsupdt IN ('Y','F')
226 AND b.ecappid = DECODE(cin_ecappid, '222', b.ecappid,cin_ecappid);
227
228 CURSOR c_trans_fi (cin_ecappid in iby_ecapp.ecappid%type) IS
229 SELECT iby_trans_fi_v.TRANSACTIONID,
230 iby_trans_fi_v.STATUS,
231 iby_trans_fi_v.UPDATEDATE,
232 iby_trans_fi_v.REQTYPE,
233 iby_trans_fi_v.REFERENCECODE,
234 iby_trans_fi_v.TRXNMID
235 FROM iby_trans_fi_v
236 WHERE iby_trans_fi_v.needsupdt IN ('Y','F')
237 AND iby_trans_fi_v.reqtype <> 'ORAPMTREQ'
238 AND iby_trans_fi_v.status not in (1, 0, 11, 100, 111)
239 AND iby_trans_fi_v.ecappid = cin_ecappid;
240
241 /* Bug# 10192851
242 CURSOR c_trans_bankacct (cin_ecappid in iby_ecapp.ecappid%type) IS
243 SELECT iby_trans_bankacct_v.TRANSACTIONID,
244 iby_trans_bankacct_v.STATUS,
245 iby_trans_bankacct_v.UPDATEDATE,
246 iby_trans_bankacct_v.REQTYPE,
247 iby_trans_bankacct_v.REFERENCECODE,
248 iby_trans_bankacct_v.TRXNMID
249 FROM iby_trans_bankacct_v
250 WHERE iby_trans_bankacct_v.needsupdt IN ('Y','F')
251 AND iby_trans_bankacct_v.reqtype <> 'ORAPMTREQ'
252 AND iby_trans_bankacct_v.status <> 0
253 AND iby_trans_bankacct_v.ecappid = cin_ecappid;
254 */
255
256 CURSOR c_trans_bankacct (cin_ecappid in iby_ecapp.ecappid%type) IS
257 SELECT trxn.TRANSACTIONID,
258 trxn.STATUS,
259 trxn.UPDATEDATE,
260 trxn.REQTYPE,
261 trxn.TRXNMID
262 FROM iby_trxn_summaries_all trxn,
263 iby_pmtschemes scheme,
264 iby_bepkeys bep
265 WHERE trxn.needsupdt IN ('Y','F')
266 AND trxn.bepid = scheme.bepid
267 AND trxn.bepid = bep.bepid
268 AND scheme.pmtschemename = 'BANKACCOUNT'
269 AND trxn.reqtype <> 'ORAPMTREQ'
270 AND trxn.status not in (1, 0, 11, 100, 111)
271 AND trxn.ecappid = cin_ecappid;
272
273 CURSOR c_trans_pcard (cin_ecappid in iby_ecapp.ecappid%type) IS
274 SELECT iby_trans_pcard_v.TRANSACTIONID,
275 iby_trans_pcard_v.STATUS,
276 iby_trans_pcard_v.UPDATEDATE,
277 iby_trans_pcard_v.REQTYPE,
278 iby_trans_pcard_v.REFERENCECODE,
279 iby_trans_pcard_v.TRXNMID
280 FROM iby_trans_pcard_v
281 WHERE iby_trans_pcard_v.needsupdt IN ('Y','F')
282 AND iby_trans_pcard_v.reqtype <> 'ORAPMTREQ'
283 AND iby_trans_pcard_v.status not in (1, 0, 11, 100, 111)
284 AND iby_trans_pcard_v.ecappid = cin_ecappid;
285
286 l_ecappTab ecappTabType;
287 l_ecapp_rec ecapp_rec_type;
288
289 BEGIN
290 -- Bug# 8663985
291 IF (ecapp_cursor%ISOPEN) THEN CLOSE ecapp_cursor; END IF;
292 -- Must specify owner when accessing DBA_ and ALL_ views
293 -- Retrieve the owner name and pass it to the cursor
294 select oracle_username into l_object_owner
295 from fnd_oracle_userid
296 where read_only_flag = 'U';
297
298 OPEN ecapp_cursor(l_object_owner);
299 FETCH ecapp_cursor BULK COLLECT INTO l_ecappTab;
300
301 txn_id_Tab := JTF_VARCHAR2_TABLE_100();
302 Status_Tab := JTF_NUMBER_TABLE();
303 reqtype_Tab := JTF_VARCHAR2_TABLE_100();
304 updatedt_Tab := JTF_DATE_TABLE();
305 refcode_Tab := JTF_VARCHAR2_TABLE_100();
306 o_statusindiv_Tab := JTF_VARCHAR2_TABLE_100();
307
308 iby_debug_pub.add('l_ecappTab.COUNT:' || l_ecappTab.COUNT,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
309
310 IF(l_ecappTab.COUNT>0) THEN
311 FOR j in l_ecappTab.FIRST..l_ecappTab.LAST LOOP
312 l_ecapp_rec:=l_ecappTab(j);
313
314 iby_debug_pub.add('Fetching records for ECAPPID:' ||
315 l_ecapp_rec.ecappid,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
316
317 FOR r_trans_core IN c_trans_core(l_ecapp_rec.ecappid) LOOP
318 l_recordCounter := l_recordCounter + 1;
319 txn_id_Tab.extend(extendRows);
320 txn_id_Tab(l_recordCounter) := to_char(r_trans_core.TRANSACTIONID);
321 Status_Tab.extend(extendRows);
322 Status_Tab(l_recordCounter) := r_trans_core.STATUS;
323 reqtype_Tab.extend(extendRows);
324 reqtype_Tab(l_recordCounter) := r_trans_core.REQTYPE;
325 updatedt_Tab.extend(extendRows);
326 updatedt_Tab(l_recordCounter) := r_trans_core.UPDATEDATE;
327 refcode_Tab.extend(extendRows);
328 refcode_Tab(l_recordCounter) := r_trans_core.REFERENCECODE;
329 txn_mid_Tab(l_recordCounter) := r_trans_core.TRXNMID;
330 END LOOP;
331
332 FOR r_trans_fi IN c_trans_fi(l_ecapp_rec.ecappid) LOOP
333 l_recordCounter := l_recordCounter + 1;
334 txn_id_Tab.extend(extendRows);
335 txn_id_Tab(l_recordCounter) := to_char(r_trans_fi.TRANSACTIONID);
336 Status_Tab.extend(extendRows);
337 Status_Tab(l_recordCounter) := r_trans_fi.STATUS;
338 reqtype_Tab.extend(extendRows);
339 reqtype_Tab(l_recordCounter) := r_trans_fi.REQTYPE;
340 updatedt_Tab.extend(extendRows);
341 updatedt_Tab(l_recordCounter) := r_trans_fi.UPDATEDATE;
342 refcode_Tab.extend(extendRows);
343 refcode_Tab(l_recordCounter) := r_trans_fi.REFERENCECODE;
344 txn_mid_Tab(l_recordCounter) := r_trans_fi.TRXNMID;
345 END LOOP;
346
347 FOR r_trans_bankacct IN c_trans_bankacct(l_ecapp_rec.ecappid) LOOP
348 l_recordCounter := l_recordCounter + 1;
349 txn_id_Tab.extend(extendRows);
350 txn_id_Tab(l_recordCounter) := to_char(r_trans_bankacct.TRANSACTIONID);
351 Status_Tab.extend(extendRows);
352 Status_Tab(l_recordCounter) := r_trans_bankacct.STATUS;
353 reqtype_Tab.extend(extendRows);
354 reqtype_Tab(l_recordCounter) := r_trans_bankacct.REQTYPE;
355 updatedt_Tab.extend(extendRows);
356 updatedt_Tab(l_recordCounter) := r_trans_bankacct.UPDATEDATE;
357 refcode_Tab.extend(extendRows);
358 --refcode_Tab(l_recordCounter) := r_trans_bankacct.REFERENCECODE;
359 txn_mid_Tab(l_recordCounter) := r_trans_bankacct.TRXNMID;
360 END LOOP;
361
362 FOR r_trans_pcard IN c_trans_pcard(l_ecapp_rec.ecappid) LOOP
363 l_recordCounter := l_recordCounter + 1;
364 txn_id_Tab.extend(extendRows);
365 txn_id_Tab(l_recordCounter) := to_char(r_trans_pcard.TRANSACTIONID);
366 Status_Tab.extend(extendRows);
367 Status_Tab(l_recordCounter) := r_trans_pcard.STATUS;
368 reqtype_Tab.extend(extendRows);
369 reqtype_Tab(l_recordCounter) := r_trans_pcard.REQTYPE;
370 updatedt_Tab.extend(extendRows);
371 updatedt_Tab(l_recordCounter) := r_trans_pcard.UPDATEDATE;
372 refcode_Tab.extend(extendRows);
373 refcode_Tab(l_recordCounter) := r_trans_pcard.REFERENCECODE;
374 txn_mid_Tab(l_recordCounter) := r_trans_pcard.TRXNMID;
375 END LOOP;
376 END LOOP;
377 END IF;
378 /*
379 * Add begin-end block around dynamic ecapp call. This is to
380 * handle gracefully the exception that is caused when the
381 * ecapp_pkg.update_status() method does not exist.
382 *
383 * Fix for bug 3883880 - rameshsh
384 */
385 IF(l_ecappTab.COUNT>0) THEN
386 FOR j in l_ecappTab.FIRST..l_ecappTab.LAST LOOP
387 l_ecapp_rec:=l_ecappTab(j);
388 iby_debug_pub.add('application_short_name:' || l_ecapp_rec.app_short_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
389 IF (l_recordCounter <> 0) then
390 -- Now dynamically construct the procedure name and invoke it
391
392 -- The procedure string
393 v_procString := 'BEGIN '|| l_ecapp_rec.app_short_name || '_ecapp_pkg.update_status( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10); END; ';
394
395 -- dbms_output.put_line('Proc call: ' || v_procString);
396 if(l_ecapp_rec.app_short_name = 'AR') then
397 iby_debug_pub.add('Invoking update_status Procedure for the application:' ||
398 l_ecapp_rec.app_short_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
399 end if;
400 iby_debug_pub.add('record counter:' || l_recordCounter,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
401
402 BEGIN
403 l_updapp_success := FALSE;
404 EXECUTE IMMEDIATE v_procString USING IN l_recordCounter,
405 IN txn_id_Tab,
406 IN reqtype_Tab,
407 IN Status_Tab,
408 IN updatedt_Tab,
409 IN refcode_Tab,
410 OUT o_status,
411 OUT o_errcode,
412 OUT o_errmsg,
413 IN OUT o_statusindiv_Tab;
414 l_updapp_success := TRUE;
415 EXCEPTION
416 WHEN OTHERS THEN
417 /*
418 * If we reached here it means that either the ecapp name does not
419 * exist in iby_ecapp_v, or that the procedure ecapp_pkg.update_status
420 * does not exist. Both these are ok. Swallow the exception and
421 * all procedure to exit gracefully. Fix for bug 3883880.
422 */
423 --iby_debug_pub.add('Exception Occurred: Either the ecapp name does not exist ||
424 --|| in iby_ecapp_v, or that the procedure ecapp_pkg.update_status does not exist'|| sqlerrm ,
425 --iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
426 iby_debug_pub.add('Crash in inner exception block=' || sqlerrm,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
427 l_updapp_success := FALSE;
428 END;
429
430 iby_debug_pub.add('update_status has been executed for :'||
431 l_ecapp_rec.app_short_name ,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
432
433 -- Doing the bulk update instead of Row-by-Row
434 IF l_updapp_success THEN
435 FORALL j IN 1..l_recordCounter
436 UPDATE iby_trxn_summaries_all
437 SET NeedsUpdt = DECODE(upper(o_statusindiv_Tab(j)), 'TRUE', 'N', 'F')
438 WHERE trxnmid = txn_mid_Tab(j);
439 iby_debug_pub.add('Updation of iby_trxn_summaries_all successful' ,
440 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
441 END IF;
442 END IF;
443 END LOOP;
444 END IF;
445
446 commit;
447
448 EXCEPTION
449 WHEN OTHERS THEN
450 FOR k IN 1..i LOOP
451 UPDATE iby_trxn_summaries_all
452 SET NeedsUpdt = 'F'
453 WHERE trxnmid = txn_mid_Tab(k);
454 END LOOP;
455 commit;
456 raise_application_error(-20000, 'IBY_204610#ECAPP=' || l_ecapp_rec.app_short_name , FALSE );
457 end update_ecapp;
458 -- End of Overloaded Procedure
459
460 procedure update_ecapp (in_ecappid in iby_ecapp.ecappid%type)
461 is
462
463
464 TYPE txn_mid_TabTyp is TABLE OF iby_trans_core_v.trxnmid%TYPE
465 INDEX BY BINARY_INTEGER;
466
467 o_status VARCHAR2(80);
468 o_errcode VARCHAR2(80);
469 o_errmsg VARCHAR2(80);
470
471 txn_id_Tab JTF_VARCHAR2_TABLE_100;
472 Status_Tab JTF_NUMBER_TABLE;
473 reqtype_Tab JTF_VARCHAR2_TABLE_100;
474 updatedt_Tab JTF_DATE_TABLE;
475 refcode_Tab JTF_VARCHAR2_TABLE_100;
476 o_statusindiv_Tab JTF_VARCHAR2_TABLE_100;
477
478 txn_mid_Tab txn_mid_TabTyp;
479
480 -- String and cursors for dynamic PL/SQL
481 ecapp_name VARCHAR2(30);
482 v_procString VARCHAR2(1000);
483 v_NumRows INTEGER;
484 totalRows INTEGER;
485 extendRows INTEGER:=1;
486 l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_SCHED.update_ecapp';
487 i NUMBER := 0;
488
489 -- Updated the where clause so that the transactions initiated from
490 -- OM will also be picked up this cursor (bug# 8239041)
491 CURSOR c_trans_core (cin_ecappid in iby_ecapp.ecappid%type) IS
492 SELECT iby_trans_core_v.TRANSACTIONID,
493 iby_trans_core_v.STATUS,
494 iby_trans_core_v.UPDATEDATE,
495 iby_trans_core_v.REQTYPE,
496 iby_trans_core_v.REFERENCECODE,
497 iby_trans_core_v.TRXNMID
498 FROM iby_trans_core_v
499 WHERE iby_trans_core_v.needsupdt IN ('Y','F')
500 --AND iby_trans_core_v.ecappid = DECODE(cin_ecappid, '222', cin_ecappid, iby_trans_core_v.ecappid);
501 AND iby_trans_core_v.ecappid = DECODE(cin_ecappid, '222', iby_trans_core_v.ecappid,cin_ecappid);
502
503
504 CURSOR c_trans_fi (cin_ecappid in iby_ecapp.ecappid%type) IS
505 SELECT iby_trans_fi_v.TRANSACTIONID,
506 iby_trans_fi_v.STATUS,
507 iby_trans_fi_v.UPDATEDATE,
508 iby_trans_fi_v.REQTYPE,
509 iby_trans_fi_v.REFERENCECODE,
510 iby_trans_fi_v.TRXNMID
511 FROM iby_trans_fi_v
512 WHERE iby_trans_fi_v.needsupdt IN ('Y','F')
513 AND iby_trans_fi_v.ecappid = cin_ecappid;
514
515 -- r_trans_fi c_trans_fi%ROWTYPE;
516
517 CURSOR c_trans_bankacct (cin_ecappid in iby_ecapp.ecappid%type) IS
518 SELECT iby_trans_bankacct_v.TRANSACTIONID,
519 iby_trans_bankacct_v.STATUS,
520 iby_trans_bankacct_v.UPDATEDATE,
521 iby_trans_bankacct_v.REQTYPE,
522 iby_trans_bankacct_v.REFERENCECODE,
523 iby_trans_bankacct_v.TRXNMID
524 FROM iby_trans_bankacct_v
525 WHERE iby_trans_bankacct_v.needsupdt IN ('Y','F')
526 AND iby_trans_bankacct_v.ecappid = cin_ecappid;
527
528 -- r_trans_bankacct c_trans_bankacct%ROWTYPE;
529
530 CURSOR c_trans_pcard (cin_ecappid in iby_ecapp.ecappid%type) IS
531 SELECT iby_trans_pcard_v.TRANSACTIONID,
532 iby_trans_pcard_v.STATUS,
533 iby_trans_pcard_v.UPDATEDATE,
534 iby_trans_pcard_v.REQTYPE,
535 iby_trans_pcard_v.REFERENCECODE,
536 iby_trans_pcard_v.TRXNMID
537 FROM iby_trans_pcard_v
538 WHERE iby_trans_pcard_v.needsupdt IN ('Y','F')
539 AND iby_trans_pcard_v.ecappid = cin_ecappid;
540
541 BEGIN
542 txn_id_Tab := JTF_VARCHAR2_TABLE_100();
543 Status_Tab := JTF_NUMBER_TABLE();
544 reqtype_Tab := JTF_VARCHAR2_TABLE_100();
545 updatedt_Tab := JTF_DATE_TABLE();
546 refcode_Tab := JTF_VARCHAR2_TABLE_100();
547 o_statusindiv_Tab := JTF_VARCHAR2_TABLE_100();
548
549 -- Commented out the below piece of code as extending the tables upfront
550 -- may cause serious performance issues and also mismatch between the number
551 -- of columns initialized in the pl/sql table and the actual values sent
552 -- across to the calling API. Changed the logic so that the extension of table
553 -- happens as and when it is required. By doing that we can minimize the usage
554 -- of select query for retrieving the totalrows.
555 -- For e.g, consider a typical example:
556 -- 1. count(*) from iby_trxn_summaries_all retrieves 838
557 -- 2. JTF tables get initialized with this number
558 -- 3. The "i" value in the For loops is "145" in this scenario
559 -- 4. The remaining elements in the JTF table is null
560 -- 5. To avoid this the tables are getting extended in the loop itself.
561
562 -- finding the total number of rows
563 --SELECT count(*)
564 --INTO totalRows
565 --FROM iby_trxn_summaries_all
566 --WHERE needsupdt IN ('Y','F')
567 --AND ecappid = in_ecappid;
568
569 --IF( totalRows < 1 ) THEN
570 --RETURN;
571 --else
572 --iby_debug_pub.add('Total Rows:' || totalRows,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
573 --iby_debug_pub.add('ecappid:' || in_ecappid,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
574 --END IF;
575
576 --allocation number of elements based on count
577 --dbms_output.put_line('Total Rows: ' || totalRows);
578 --txn_id_Tab.extend(totalRows);
579 --Status_Tab.extend(totalRows);
580 --reqtype_Tab.extend(totalRows);
581 --updatedt_Tab.extend(totalRows);
582 --refcode_Tab.extend(totalRows);
583 --o_statusindiv_Tab.extend(totalRows);
584
585 FOR r_trans_core IN c_trans_core(in_ecappid) LOOP
586 i := i + 1;
587
588 txn_id_Tab.extend(extendRows);
589 txn_id_Tab(i) := to_char(r_trans_core.TRANSACTIONID);
590 Status_Tab.extend(extendRows);
591 Status_Tab(i) := r_trans_core.STATUS;
592 reqtype_Tab.extend(extendRows);
593 reqtype_Tab(i) := r_trans_core.REQTYPE;
594 updatedt_Tab.extend(extendRows);
595 updatedt_Tab(i) := r_trans_core.UPDATEDATE;
596 refcode_Tab.extend(extendRows);
597 refcode_Tab(i) := r_trans_core.REFERENCECODE;
598 txn_mid_Tab(i) := r_trans_core.TRXNMID;
599 END LOOP;
600
601 FOR r_trans_fi IN c_trans_fi(in_ecappid) LOOP
602 i := i + 1;
603
604 txn_id_Tab.extend(extendRows);
605 txn_id_Tab(i) := to_char(r_trans_fi.TRANSACTIONID);
606 Status_Tab.extend(extendRows);
607 Status_Tab(i) := r_trans_fi.STATUS;
608 reqtype_Tab.extend(extendRows);
609 reqtype_Tab(i) := r_trans_fi.REQTYPE;
610 updatedt_Tab.extend(extendRows);
611 updatedt_Tab(i) := r_trans_fi.UPDATEDATE;
612 refcode_Tab.extend(extendRows);
613 refcode_Tab(i) := r_trans_fi.REFERENCECODE;
614 txn_mid_Tab(i) := r_trans_fi.TRXNMID;
615
616 END LOOP;
617
618 FOR r_trans_bankacct IN c_trans_bankacct(in_ecappid) LOOP
619 i := i + 1;
620
621 txn_id_Tab.extend(extendRows);
622 txn_id_Tab(i) := to_char(r_trans_bankacct.TRANSACTIONID);
623 Status_Tab.extend(extendRows);
624 Status_Tab(i) := r_trans_bankacct.STATUS;
625 reqtype_Tab.extend(extendRows);
626 reqtype_Tab(i) := r_trans_bankacct.REQTYPE;
627 updatedt_Tab.extend(extendRows);
628 updatedt_Tab(i) := r_trans_bankacct.UPDATEDATE;
629 refcode_Tab.extend(extendRows);
630 refcode_Tab(i) := r_trans_bankacct.REFERENCECODE;
631 txn_mid_Tab(i) := r_trans_bankacct.TRXNMID;
632
633 END LOOP;
634
635 FOR r_trans_pcard IN c_trans_pcard(in_ecappid) LOOP
636 i := i + 1;
637
638 txn_id_Tab.extend(extendRows);
639 txn_id_Tab(i) := to_char(r_trans_pcard.TRANSACTIONID);
640 Status_Tab.extend(extendRows);
641 Status_Tab(i) := r_trans_pcard.STATUS;
642 reqtype_Tab.extend(extendRows);
643 reqtype_Tab(i) := r_trans_pcard.REQTYPE;
644 updatedt_Tab.extend(extendRows);
645 updatedt_Tab(i) := r_trans_pcard.UPDATEDATE;
646 refcode_Tab.extend(extendRows);
647 refcode_Tab(i) := r_trans_pcard.REFERENCECODE;
648 txn_mid_Tab(i) := r_trans_pcard.TRXNMID;
649
650 END LOOP;
651
652 /*
653 * Add begin-end block around dynamic ecapp call. This is to
654 * handle gracefully the exception that is caused when the
655 * ecapp_pkg.update_status() method does not exist.
656 *
657 * Fix for bug 3883880 - rameshsh
658 */
659 BEGIN
660
661 -- Now getting the application short name
662 iby_debug_pub.add('Enter update_ecapp:',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
663 -- The view iby_ecapp_v has two records for each of the ecappid's. To avoid
664 -- the possible exception, using distinct function
665 SELECT distinct(application_short_name)
666 INTO ecapp_name
667 FROM iby_ecapp_v
668 WHERE ecappid = in_ecappid;
669 iby_debug_pub.add('application_short_name:' || ecapp_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
670 -- dbms_output.put_line('Total count for inner loop : ' || i);
671 --dbms_output.put_line('Sending in : ' || txn_id_Tab(1) ||'** ' ||Status_Tab(1) ||'** '|| updatedt_Tab(1) || '** '|| refcode_Tab(1) ||'** '|| txn_mid_Tab(1));
672 --iby_debug_pub.add('I value:' || i,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
673 IF (i <> 0) then
674
675 -- Now dynamically construct the procedure name and invoke it
676
677 -- The procedure string
678 v_procString := 'BEGIN '|| ecapp_name || '_ecapp_pkg.update_status( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10); END; ';
679
680 -- dbms_output.put_line('Proc call: ' || v_procString);
681 if(ecapp_name = 'AR') then
682 iby_debug_pub.add('Invoking update_status Procedure for the application:' ||
683 ecapp_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
684 end if;
685 EXECUTE IMMEDIATE v_procString USING IN i,
686 IN txn_id_Tab,
687 IN reqtype_Tab,
688 IN Status_Tab,
689 IN updatedt_Tab,
690 IN refcode_Tab,
691 OUT o_status,
692 OUT o_errcode,
693 OUT o_errmsg,
694 IN OUT o_statusindiv_Tab;
695 iby_debug_pub.add('update_status has been executed for :'||
696 ecapp_name ,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
697
698 -- Doing the bulk update instead of Row-by-Row
699 FORALL j IN 1..i
700 --if (o_statusindiv_Tab(j) = 'TRUE') then
701 UPDATE iby_trxn_summaries_all
702 SET NeedsUpdt = DECODE(upper(o_statusindiv_Tab(j)), 'TRUE', 'N', 'F')
703 WHERE trxnmid = txn_mid_Tab(j);
704 --else
705 -- UPDATE iby_trxn_summaries_all
706 -- SET NeedsUpdt = 'F'
707 -- WHERE trxnmid = txn_mid_Tab(j);
708 --end if;
709 --END LOOP;
710 iby_debug_pub.add('Updation of iby_trxn_summaries_all successful' ,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
711
712 END IF;
713
714 EXCEPTION
715 WHEN OTHERS THEN
716
717 /*
718 * If we reached here it means that either the ecapp name does not
719 * exist in iby_ecapp_v, or that the procedure ecapp_pkg.update_status
720 * does not exist. Both these are ok. Swallow the exception and
721 * all procedure to exit gracefully. Fix for bug 3883880.
722 */
723 --iby_debug_pub.add('Exception Occurred: Either the ecapp name does not exist ||
724 --|| in iby_ecapp_v, or that the procedure ecapp_pkg.update_status does not exist'|| sqlerrm ,
725 --iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
726 NULL;
727
728 END;
729
730 commit;
731
732 EXCEPTION
733 WHEN OTHERS THEN
734 FOR k IN 1..i LOOP
735 UPDATE iby_trxn_summaries_all
736 SET NeedsUpdt = 'F'
737 WHERE trxnmid = txn_mid_Tab(k);
738 END LOOP;
739 commit;
740 raise_application_error(-20000, 'IBY_204610#ECAPP=' || ecapp_name , FALSE );
741
742 end update_ecapp;
743
744
745 function updPmtStatus (in_psreqid in iby_trxn_fi.psreqid%type,
746 in_dtpmtprc in varchar2, -- YYYYMMDD
747 in_pmtprcst in varchar2, -- 'PAID','UNPAID','FAILED','PAYFAILED'
748 in_srvrid in iby_trxn_fi.srvid%type,
749 in_refinfo in iby_trxn_fi.referencecode%type)
750 return number -- nonzero if rows were updated.
751 is
752 begin
753 update iby_trxn_summaries_all
754 set status = decode(in_pmtprcst, 'PAID', 0, 'UNPAID', 17, 'FAILED',
755 16, 'PAYFAILED', 16, 16),
756 updatedate = to_date(in_dtpmtprc,'YYYYMMDD'), needsupdt = 'Y'
757 where trxnmid in
758 (select trxnmid from iby_trxn_fi where psreqid = in_psreqid);
759 if SQL%NOTFOUND
760 then return 0;
761 end if;
762 update iby_trxn_fi set srvid = in_srvrid, referencecode = in_refinfo
763 where psreqid = in_psreqid;
764 if SQL%NOTFOUND
765 then return 0;
766 end if;
767 return 1;
768 end updPmtStatus;
769
770 -- This procedure updates the transaction information after it has been processed
771 -- and updates the needsUpdate flag to 'Y' so that ECApps are updated with proper
772 -- information.
773
774 procedure update_trxn_status( i_unchanged_status IN NUMBER,
775 i_numTrxns IN NUMBER,
776 i_status_arr IN JTF_NUMBER_TABLE,
777 i_errLoc_arr IN JTF_NUMBER_TABLE,
778 i_errCode_arr IN JTF_VARCHAR2_TABLE_100,
779 i_errMsg_arr IN JTF_VARCHAR2_TABLE_300,
780 i_tangibleId_arr IN JTF_VARCHAR2_TABLE_100,
781 i_trxnMId_arr IN JTF_NUMBER_TABLE,
782 i_srvrId_arr IN JTF_VARCHAR2_TABLE_100,
783 i_refCode_arr IN JTF_VARCHAR2_TABLE_100,
784 i_auxMsg_arr IN JTF_VARCHAR2_TABLE_300,
785 i_fee_arr IN JTF_NUMBER_TABLE,
786 o_status_arr OUT NOCOPY JTF_NUMBER_TABLE,
787 o_error_code OUT NOCOPY NUMBER,
788 o_error_msg OUT NOCOPY VARCHAR2
789 )
790 IS
791
792 l_index INTEGER;
793 l_status NUMBER;
794 c_FAIL NUMBER := -1;
795 c_SUCCESS NUMBER := 0;
796
797 BEGIN
798
799 o_status_arr := JTF_NUMBER_TABLE();
800 o_status_arr.extend( i_tangibleId_arr.count );
801
802 o_error_code := 0;
803
804 l_index := i_tangibleId_arr.first;
805
806 WHILE (TRUE) LOOP
807
808 l_status := i_status_arr( l_index );
809 o_status_arr( l_index ) := c_SUCCESS;
810
811 BEGIN -- Nested block begins
812
813 UPDATE iby_trxn_summaries_all
814 SET status = decode( l_status, i_unchanged_status, status, l_status),
815 errorlocation = i_errLoc_arr( l_index ),
816 BEPCode = i_errCode_arr( l_index ),
817 BEPMessage = i_errMsg_arr( l_index ),
818 needsupdt = 'Y'
819 WHERE TANGIBLEID = i_tangibleId_arr( l_index )
820 AND status <> -99;
821
822 IF ( SQL%NOTFOUND ) THEN
823 o_status_arr( l_index ) := c_FAIL;
824 ELSE
825 UPDATE iby_trxn_fi
826 SET referencecode = i_refCode_arr( l_index ),
827 srvId = i_srvrId_arr( l_index ),
828 AUXMSG = i_auxMsg_arr( l_index ),
829 PROCESSFEE = i_fee_arr( l_index )
830 WHERE TRXNMID = i_trxnMId_arr( l_index );
831 --(SELECT TRXNMID
832 --FROM IBY_TRXN_SUMMARIES_ALL
833 --WHERE TANGIBLEID = i_tangibleId_arr( l_index )
834 --);
835
836 IF ( SQL%NOTFOUND ) THEN
837 o_status_arr( l_index ) := c_FAIL;
838 ROLLBACK;
839 END IF;
840
841 END IF;
842
843 IF ( o_status_arr( l_index ) <> c_FAIL ) THEN
844 COMMIT;
845 END IF;
846
847 EXCEPTION
848 WHEN OTHERS THEN
849 o_status_arr( l_index ) := c_FAIL;
850 o_error_code := SQLCODE;
851 o_error_msg := SUBSTR(SQLERRM, 1, 200);
852
853 END; -- Nested block ends
854
855 EXIT WHEN ( i_tangibleId_arr.last = l_index );
856
857 l_index := i_tangibleId_arr.next( l_index );
858
859 END LOOP; --end of while loop
860
861 END update_trxn_status;
862
863 end iby_sched;
[Home] [Help]