[Home] [Help]
PACKAGE BODY: APPS.IBY_SCHED
Source
1 package body iby_sched as
2 /*$Header: ibyscfib.pls 120.4 2005/11/17 01:53:45 syidner ship $*/
3
4 procedure cardInfo (in_payerinstrid in iby_trans_fi_v.payerinstrid%type,
5 in_payeeid in iby_trans_fi_v.payeeid%type,
6 in_tangibleid in iby_trans_fi_v.tangibleid%type,
7 out_ccnumber_from out nocopy iby_creditcard_v.ccnumber%type,
8 out_expdate_from out nocopy iby_creditcard_v.expirydate%type,
9 out_accttype_from out nocopy iby_accttype.accttype%type,
10 out_name out nocopy varchar2,
11 out_bankid_to out nocopy iby_ext_bank_accounts_v.bank_party_id%type,
12 out_branchid_to out nocopy iby_ext_bank_accounts_v.branch_party_id%type,
13 out_acctid_to out nocopy iby_ext_bank_accounts_v.ext_bank_account_id%type,
14 out_accttype_to out nocopy iby_accttype.accttype%type,
15 out_acctno out nocopy iby_tangible.acctno%type,
16 out_refinfo out nocopy iby_tangible.refinfo%type,
17 out_memo out nocopy iby_tangible.memo%type,
18 out_currency out nocopy iby_tangible.currencynamecode%type)
19 is
20 cursor c_userCardInfo (cin_payerinstrid in iby_trans_fi_v.payerinstrid%type) is
21
22 select ccnumber,
23 expirydate,
24 accttype
25 from iby_creditcard_v c,
26 iby_trxn_summaries_all d
27 where d.payerinstrid = cin_payerinstrid
28 and d.payerinstrid = c.instrid;
29
30 cursor c_payeeBankInfo (cin_payeeid in iby_trans_fi_v.payeeid%type) is
31 select b.bank_party_id,
32 b.branch_party_id,
33 b.ext_bank_account_id,
34 b.bank_account_type
35 from iby_ext_bank_accounts_v b,
36 iby_trxn_summaries_all d
37 where d.payeeid = cin_payeeid
38 and d.payeeinstrid = b.ext_bank_account_id;
39
40 cursor c_tangibleInfo (cin_tangibleid in iby_trans_fi_v.tangibleid%type) is
41 select acctno,
42 refinfo,
43 memo,
44 currencynamecode
45 from iby_tangible t
46 where t.tangibleid = cin_tangibleid;
47 BEGIN
48 -- get the user's credit card information
49 open c_userCardInfo(in_payerinstrid);
50 fetch c_userCardInfo into out_ccnumber_from,
51 out_expdate_from,
52 out_accttype_from;
53 if c_userCardInfo%NOTFOUND
54 then raise_application_error(-20099, 'No user credit card info found');
55 end if;
56 close c_userCardInfo;
57
58 -- get the payee's bank account information
59 open c_payeeBankInfo(in_payeeid);
60 fetch c_payeeBankInfo into out_bankid_to,
61 out_branchid_to,
62 out_acctid_to,
63 out_accttype_to;
64 if c_payeeBankInfo%NOTFOUND then
65 raise_application_error(-20092, 'No payee bank account info found');
66 end if;
67 close c_payeeBankInfo;
68 -- get the tangible information
69 open c_tangibleInfo(in_tangibleid);
70 fetch c_tangibleInfo into out_acctno,
71 out_refinfo,
72 out_memo,
73 out_currency;
74 if c_tangibleInfo%NOTFOUND
75 then raise_application_error(-20093, 'No tangible info found');
76 end if;
77 close c_tangibleInfo;
78 end cardInfo;
79
80 procedure bankInfo (in_payerinstrid in iby_trans_fi_v.payerinstrid%type,
81 in_payeeid in iby_trans_fi_v.payeeid%type,
82 in_tangibleid in iby_trans_fi_v.tangibleid%type,
83 out_bankid_from out nocopy iby_ext_bank_accounts_v.bank_party_id%type,
84 out_branchid_from out nocopy iby_ext_bank_accounts_v.branch_party_id%type,
85 out_acctid_from out nocopy iby_ext_bank_accounts_v.ext_bank_account_id%type,
86 out_accttype_from out nocopy iby_accttype.accttype%type,
87 out_name out nocopy varchar2,
88 out_bankid_to out nocopy iby_ext_bank_accounts_v.bank_party_id%type,
89 out_branchid_to out nocopy iby_ext_bank_accounts_v.branch_party_id%type,
90 out_acctid_to out nocopy iby_ext_bank_accounts_v.ext_bank_account_id%type,
91 out_accttype_to out nocopy iby_accttype.accttype%type,
92 out_acctno out nocopy iby_tangible.acctno%type,
93 out_refinfo out nocopy iby_tangible.refinfo%type,
94 out_memo out nocopy iby_tangible.memo%type,
95 out_currency out nocopy iby_tangible.currencynamecode%type)
96 is
97 cursor c_userBankInfo (cin_payerinstrid in iby_trans_fi_v.payerinstrid%type) is
98
99 select b.bank_party_id,
100 b.branch_party_id,
101 b.ext_bank_account_id,
102 b.bank_account_type
103 from iby_ext_bank_accounts_v b,
104 iby_trxn_summaries_all d
105 where d.payerinstrid = cin_payerinstrid
106 and d.payerinstrid = b.ext_bank_account_id;
107
108
109 cursor c_payeeBankInfo (cin_payeeid in iby_trans_fi_v.payeeid%type) is
110 select b.bank_party_id,
111 b.branch_party_id,
112 b.ext_bank_account_id,
113 b.bank_account_type
114 from iby_ext_bank_accounts_v b,
115 iby_trxn_summaries_all d
116 where d.payeeid = cin_payeeid
117 and d.payeeinstrid = b.ext_bank_account_id;
118
119 cursor c_tangibleInfo (cin_tangibleid in iby_trans_fi_v.tangibleid%type) is
120 select acctno,
121 refinfo,
122 memo,
123 currencynamecode
124 from iby_tangible t
125 where t.tangibleid = cin_tangibleid;
126 begin
127 -- get the user's bank account information
128 open c_userBankInfo (in_payerinstrid);
129 fetch c_userBankInfo into out_bankid_from,
130 out_branchid_from,
131 out_acctid_from,
132 out_accttype_from;
133 if c_userBankInfo%NOTFOUND
134 then raise_application_error(-20094, 'No user bank account info found');
135 end if;
136 close c_userBankInfo;
137
138 -- get the payee's bank account information
139 open c_payeeBankInfo (in_payeeid);
140 fetch c_payeeBankInfo into out_bankid_to,
141 out_branchid_to,
142 out_acctid_to,
143 out_accttype_to;
144 if c_payeeBankInfo%NOTFOUND
145 then raise_application_error(-20092, 'No payee bank account info found');
146 end if;
147 close c_payeeBankInfo;
148 -- get the tangible information
149 open c_tangibleInfo (in_tangibleid);
150 fetch c_tangibleInfo into out_acctno,
151 out_refinfo,
152 out_memo,
153 out_currency;
154 if c_tangibleInfo%NOTFOUND
155 then raise_application_error(-20093, 'No tangible info found');
156 end if;
157 close c_tangibleInfo;
158 end bankInfo;
159
160
161 procedure update_ecapp (in_ecappid in iby_ecapp.ecappid%type)
162 is
163
164
165 TYPE txn_mid_TabTyp is TABLE OF iby_trans_core_v.trxnmid%TYPE
166 INDEX BY BINARY_INTEGER;
167
168 o_status VARCHAR2(80);
169 o_errcode VARCHAR2(80);
170 o_errmsg VARCHAR2(80);
171
172 txn_id_Tab JTF_VARCHAR2_TABLE_100;
173 Status_Tab JTF_NUMBER_TABLE;
174 reqtype_Tab JTF_VARCHAR2_TABLE_100;
175 updatedt_Tab JTF_DATE_TABLE;
176 refcode_Tab JTF_VARCHAR2_TABLE_100;
177 o_statusindiv_Tab JTF_VARCHAR2_TABLE_100;
178
179 txn_mid_Tab txn_mid_TabTyp;
180
181 -- String and cursors for dynamic PL/SQL
182 ecapp_name VARCHAR2(30);
183 v_procString VARCHAR2(1000);
184 v_NumRows INTEGER;
185 totalRows INTEGER;
186
187 i NUMBER := 0;
188
189
190 CURSOR c_trans_core (cin_ecappid in iby_ecapp.ecappid%type) IS
191 SELECT iby_trans_core_v.TRANSACTIONID,
192 iby_trans_core_v.STATUS,
193 iby_trans_core_v.UPDATEDATE,
194 iby_trans_core_v.REQTYPE,
195 iby_trans_core_v.REFERENCECODE,
196 iby_trans_core_v.TRXNMID
197 FROM iby_trans_core_v
198 WHERE iby_trans_core_v.needsupdt IN ('Y','F')
199 AND iby_trans_core_v.ecappid = cin_ecappid;
200
201
202 CURSOR c_trans_fi (cin_ecappid in iby_ecapp.ecappid%type) IS
203 SELECT iby_trans_fi_v.TRANSACTIONID,
204 iby_trans_fi_v.STATUS,
205 iby_trans_fi_v.UPDATEDATE,
206 iby_trans_fi_v.REQTYPE,
207 iby_trans_fi_v.REFERENCECODE,
208 iby_trans_fi_v.TRXNMID
209 FROM iby_trans_fi_v
210 WHERE iby_trans_fi_v.needsupdt IN ('Y','F')
211 AND iby_trans_fi_v.ecappid = cin_ecappid;
212
213 -- r_trans_fi c_trans_fi%ROWTYPE;
214
215 CURSOR c_trans_bankacct (cin_ecappid in iby_ecapp.ecappid%type) IS
216 SELECT iby_trans_bankacct_v.TRANSACTIONID,
217 iby_trans_bankacct_v.STATUS,
218 iby_trans_bankacct_v.UPDATEDATE,
219 iby_trans_bankacct_v.REQTYPE,
220 iby_trans_bankacct_v.REFERENCECODE,
221 iby_trans_bankacct_v.TRXNMID
222 FROM iby_trans_bankacct_v
223 WHERE iby_trans_bankacct_v.needsupdt IN ('Y','F')
224 AND iby_trans_bankacct_v.ecappid = cin_ecappid;
225
226 -- r_trans_bankacct c_trans_bankacct%ROWTYPE;
227
228 CURSOR c_trans_pcard (cin_ecappid in iby_ecapp.ecappid%type) IS
229 SELECT iby_trans_pcard_v.TRANSACTIONID,
230 iby_trans_pcard_v.STATUS,
231 iby_trans_pcard_v.UPDATEDATE,
232 iby_trans_pcard_v.REQTYPE,
233 iby_trans_pcard_v.REFERENCECODE,
234 iby_trans_pcard_v.TRXNMID
235 FROM iby_trans_pcard_v
236 WHERE iby_trans_pcard_v.needsupdt IN ('Y','F')
237 AND iby_trans_pcard_v.ecappid = cin_ecappid;
238
239 -- r_trans_bankacct c_trans_bankacct%ROWTYPE;
240
241 BEGIN
242
243 txn_id_Tab := JTF_VARCHAR2_TABLE_100();
244 Status_Tab := JTF_NUMBER_TABLE();
245 reqtype_Tab := JTF_VARCHAR2_TABLE_100();
246 updatedt_Tab := JTF_DATE_TABLE();
247 refcode_Tab := JTF_VARCHAR2_TABLE_100();
248 o_statusindiv_Tab := JTF_VARCHAR2_TABLE_100();
249
250 -- finding the total number of rows
251 SELECT count(*)
252 INTO totalRows
253 FROM iby_trxn_summaries_all
254 WHERE needsupdt IN ('Y','F')
255 AND ecappid = in_ecappid;
256
257 IF( totalRows < 1 ) THEN
258 RETURN;
259 END IF;
260
261 -- allocation number of elements based on count
262 -- dbms_output.put_line('Total Rows: ' || totalRows);
263 txn_id_Tab.extend(totalRows);
264 Status_Tab.extend(totalRows);
265 reqtype_Tab.extend(totalRows);
266 updatedt_Tab.extend(totalRows);
267 refcode_Tab.extend(totalRows);
268 o_statusindiv_Tab.extend(totalRows);
269
270 FOR r_trans_core IN c_trans_core(in_ecappid) LOOP
271 i := i + 1;
272
273 txn_id_Tab(i) := to_char(r_trans_core.TRANSACTIONID);
274 Status_Tab(i) := r_trans_core.STATUS;
275 reqtype_Tab(i) := r_trans_core.REQTYPE;
276 updatedt_Tab(i) := r_trans_core.UPDATEDATE;
277 refcode_Tab(i) := r_trans_core.REFERENCECODE;
278 txn_mid_Tab(i) := r_trans_core.TRXNMID;
279 END LOOP;
280
281 FOR r_trans_fi IN c_trans_fi(in_ecappid) LOOP
282 i := i + 1;
283
284 txn_id_Tab(i) := to_char(r_trans_fi.TRANSACTIONID);
285 Status_Tab(i) := r_trans_fi.STATUS;
286 reqtype_Tab(i) := r_trans_fi.REQTYPE;
287 updatedt_Tab(i) := r_trans_fi.UPDATEDATE;
288 refcode_Tab(i) := r_trans_fi.REFERENCECODE;
289 txn_mid_Tab(i) := r_trans_fi.TRXNMID;
290
291 END LOOP;
292
293 FOR r_trans_bankacct IN c_trans_bankacct(in_ecappid) LOOP
294 i := i + 1;
295
296 txn_id_Tab(i) := to_char(r_trans_bankacct.TRANSACTIONID);
297 Status_Tab(i) := r_trans_bankacct.STATUS;
298 reqtype_Tab(i) := r_trans_bankacct.REQTYPE;
299 updatedt_Tab(i) := r_trans_bankacct.UPDATEDATE;
300 refcode_Tab(i) := r_trans_bankacct.REFERENCECODE;
301 txn_mid_Tab(i) := r_trans_bankacct.TRXNMID;
302
303 END LOOP;
304
305 FOR r_trans_pcard IN c_trans_pcard(in_ecappid) LOOP
306 i := i + 1;
307
308 txn_id_Tab(i) := to_char(r_trans_pcard.TRANSACTIONID);
309 Status_Tab(i) := r_trans_pcard.STATUS;
310 reqtype_Tab(i) := r_trans_pcard.REQTYPE;
311 updatedt_Tab(i) := r_trans_pcard.UPDATEDATE;
312 refcode_Tab(i) := r_trans_pcard.REFERENCECODE;
313 txn_mid_Tab(i) := r_trans_pcard.TRXNMID;
314
315 END LOOP;
316
317 /*
318 * Add begin-end block around dynamic ecapp call. This is to
319 * handle gracefully the exception that is caused when the
320 * ecapp_pkg.update_status() method does not exist.
321 *
322 * Fix for bug 3883880 - rameshsh
323 */
324 BEGIN
325
326 -- Now getting the application short name
327 SELECT application_short_name
328 INTO ecapp_name
329 FROM iby_ecapp_v
330 WHERE ecappid = in_ecappid;
331
332 -- dbms_output.put_line('Total count for inner loop : ' || i);
333 --dbms_output.put_line('Sending in : ' || txn_id_Tab(1) ||'** ' ||Status_Tab(1) ||'** '|| updatedt_Tab(1) || '** '|| refcode_Tab(1) ||'** '|| txn_mid_Tab(1));
334
335 IF (i <> 0) then
336
337 -- Now dynamically construct the procedure name and invoke it
338
339 -- The procedure string
340 v_procString := 'BEGIN '|| ecapp_name || '_ecapp_pkg.update_status( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10); END; ';
341
342 -- dbms_output.put_line('Proc call: ' || v_procString);
343
344 EXECUTE IMMEDIATE v_procString USING IN i,
345 IN txn_id_Tab,
346 IN reqtype_Tab,
347 IN Status_Tab,
348 IN updatedt_Tab,
349 IN refcode_Tab,
350 OUT o_status,
351 OUT o_errcode,
352 OUT o_errmsg,
353 IN OUT o_statusindiv_Tab;
354
355 FOR j IN 1..i LOOP
356 if (o_statusindiv_Tab(j) = 'TRUE') then
357 UPDATE iby_trxn_summaries_all
358 SET NeedsUpdt = 'N'
359 WHERE trxnmid = txn_mid_Tab(j);
360 else
361 UPDATE iby_trxn_summaries_all
362 SET NeedsUpdt = 'F'
363 WHERE trxnmid = txn_mid_Tab(j);
364 end if;
365
366 END LOOP;
367
368 END IF;
369
370 EXCEPTION
371 WHEN OTHERS THEN
372
373 /*
374 * If we reached here it means that either the ecapp name does not
375 * exist in iby_ecapp_v, or that the procedure ecapp_pkg.update_status
376 * does not exist. Both these are ok. Swallow the exception and
377 * all procedure to exit gracefully. Fix for bug 3883880.
378 */
379 NULL;
380
381 END;
382
383 commit;
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 FOR k IN 1..i LOOP
388 UPDATE iby_trxn_summaries_all
389 SET NeedsUpdt = 'F'
390 WHERE trxnmid = txn_mid_Tab(k);
391 END LOOP;
392 commit;
393 raise_application_error(-20000, 'IBY_204610#ECAPP=' || ecapp_name , FALSE );
394
395 end update_ecapp;
396
397
398 function updPmtStatus (in_psreqid in iby_trxn_fi.psreqid%type,
399 in_dtpmtprc in varchar2, -- YYYYMMDD
400 in_pmtprcst in varchar2, -- 'PAID','UNPAID','FAILED','PAYFAILED'
401 in_srvrid in iby_trxn_fi.srvid%type,
402 in_refinfo in iby_trxn_fi.referencecode%type)
403 return number -- nonzero if rows were updated.
404 is
405 begin
406 update iby_trxn_summaries_all
407 set status = decode(in_pmtprcst, 'PAID', 0, 'UNPAID', 17, 'FAILED',
408 16, 'PAYFAILED', 16, 16),
409 updatedate = to_date(in_dtpmtprc,'YYYYMMDD'), needsupdt = 'Y'
410 where trxnmid in
411 (select trxnmid from iby_trxn_fi where psreqid = in_psreqid);
412 if SQL%NOTFOUND
413 then return 0;
414 end if;
415 update iby_trxn_fi set srvid = in_srvrid, referencecode = in_refinfo
416 where psreqid = in_psreqid;
417 if SQL%NOTFOUND
418 then return 0;
419 end if;
420 return 1;
421 end updPmtStatus;
422
423 -- This procedure updates the transaction information after it has been processed
424 -- and updates the needsUpdate flag to 'Y' so that ECApps are updated with proper
425 -- information.
426
427 procedure update_trxn_status( i_unchanged_status IN NUMBER,
428 i_numTrxns IN NUMBER,
429 i_status_arr IN JTF_NUMBER_TABLE,
430 i_errLoc_arr IN JTF_NUMBER_TABLE,
431 i_errCode_arr IN JTF_VARCHAR2_TABLE_100,
432 i_errMsg_arr IN JTF_VARCHAR2_TABLE_300,
433 i_tangibleId_arr IN JTF_VARCHAR2_TABLE_100,
434 i_trxnMId_arr IN JTF_NUMBER_TABLE,
435 i_srvrId_arr IN JTF_VARCHAR2_TABLE_100,
436 i_refCode_arr IN JTF_VARCHAR2_TABLE_100,
437 i_auxMsg_arr IN JTF_VARCHAR2_TABLE_300,
438 i_fee_arr IN JTF_NUMBER_TABLE,
439 o_status_arr OUT NOCOPY JTF_NUMBER_TABLE,
440 o_error_code OUT NOCOPY NUMBER,
441 o_error_msg OUT NOCOPY VARCHAR2
442 )
443 IS
444
445 l_index INTEGER;
446 l_status NUMBER;
447 c_FAIL NUMBER := -1;
448 c_SUCCESS NUMBER := 0;
449
450 BEGIN
451
452 o_status_arr := JTF_NUMBER_TABLE();
453 o_status_arr.extend( i_tangibleId_arr.count );
454
455 o_error_code := 0;
456
457 l_index := i_tangibleId_arr.first;
458
459 WHILE (TRUE) LOOP
460
461 l_status := i_status_arr( l_index );
462 o_status_arr( l_index ) := c_SUCCESS;
463
464 BEGIN -- Nested block begins
465
466 UPDATE iby_trxn_summaries_all
467 SET status = decode( l_status, i_unchanged_status, status, l_status),
468 errorlocation = i_errLoc_arr( l_index ),
469 BEPCode = i_errCode_arr( l_index ),
470 BEPMessage = i_errMsg_arr( l_index ),
471 needsupdt = 'Y'
472 WHERE TANGIBLEID = i_tangibleId_arr( l_index )
473 AND status <> -99;
474
475 IF ( SQL%NOTFOUND ) THEN
476 o_status_arr( l_index ) := c_FAIL;
477 ELSE
478 UPDATE iby_trxn_fi
479 SET referencecode = i_refCode_arr( l_index ),
480 srvId = i_srvrId_arr( l_index ),
481 AUXMSG = i_auxMsg_arr( l_index ),
482 PROCESSFEE = i_fee_arr( l_index )
483 WHERE TRXNMID = i_trxnMId_arr( l_index );
484 --(SELECT TRXNMID
485 --FROM IBY_TRXN_SUMMARIES_ALL
486 --WHERE TANGIBLEID = i_tangibleId_arr( l_index )
487 --);
488
489 IF ( SQL%NOTFOUND ) THEN
490 o_status_arr( l_index ) := c_FAIL;
491 ROLLBACK;
492 END IF;
493
494 END IF;
495
496 IF ( o_status_arr( l_index ) <> c_FAIL ) THEN
497 COMMIT;
498 END IF;
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 o_status_arr( l_index ) := c_FAIL;
503 o_error_code := SQLCODE;
504 o_error_msg := SUBSTR(SQLERRM, 1, 200);
505
506 END; -- Nested block ends
507
508 EXIT WHEN ( i_tangibleId_arr.last = l_index );
509
510 l_index := i_tangibleId_arr.next( l_index );
511
512 END LOOP; --end of while loop
513
514 END update_trxn_status;
515
516 end iby_sched;