1 package body iby_accppmtmthd_pkg as
2 /*$Header: ibyacpmb.pls 115.7 2002/11/15 23:45:00 jleybovi ship $*/
3
4 /*
5 ** Procedure: getMPayeeId
6 ** Purpose: retrieve mpayeeid from iby_payee table based on payeeid
7 */
8 Procedure getMPayeeId(i_payeeid in iby_payee.payeeid%type,
9 o_mpayeeid out nocopy iby_payee.mpayeeid%type)
10 is
11 cursor c_get_mpayeeid(ci_payeeid iby_payee.payeeid%type) is
12 SELECT mpayeeid from iby_payee
13 WHERE payeeid = ci_payeeid;
14 BEGIN
15 open c_get_mpayeeid(i_payeeid);
16 fetch c_get_mpayeeid into o_mpayeeid;
17 if ( c_get_mpayeeid%notfound ) then
18 --raise_application_error(-20305, 'Payee not registered', FALSE);
19 raise_application_error(-20000, 'IBY_20305#', FALSE);
20 end if;
21 END;
22
23 /*
24 ** Function: pmtMthdExists.
25 ** Purpose: Check if the specified payeeid and pmtmethod exists or not.
26 */
27 function pmtMthdExists(i_ecappid in iby_accppmtmthd.ecappid%type,
28 i_payeeid in iby_accppmtmthd.payeeid%type,
29 i_instrtype in iby_accttype.instrtype%type,
30 i_accttype in iby_accttype.accttype%type,
31 o_status out nocopy iby_accppmtmthd.status%type)
32 return boolean
33 is
34 l_flag boolean := false;
35 cursor c_pmtmthd (ci_ecappid in iby_accppmtmthd.ecappid%type,
36 ci_payeeid in iby_accppmtmthd.payeeid%type,
37 ci_instrtype iby_accttype.instrtype%type,
38 ci_accttype iby_accttype.accttype%type)
39 is
40 SELECT status
41 FROM iby_accppmtmthd accp, iby_accttype acct
42 WHERE accp.payeeid = ci_payeeid
43 AND accp.ecappid = ci_ecappid
44 AND accp.accttypeid = acct.accttypeid
45 AND acct.instrtype = ci_instrtype
46 AND acct.accttype = ci_accttype;
47 begin
48 if ( c_pmtmthd%isopen) then
49 close c_pmtmthd;
50 end if;
51 /*
52 ** open the cursor, which retrieves all the rows that match the ecappid and
53 ** payeeid, instrtype, and accttype.
54 */
55 open c_pmtmthd(i_ecappid, i_payeeid, i_instrtype, i_accttype);
56 fetch c_pmtmthd into o_status;
57 /*
58 ** if payeeid and ecappid already exist then return true otherwise flase.
59 */
60 l_flag := c_pmtmthd%found;
61
62 close c_pmtmthd;
63 return l_flag;
64 end pmtMthdExists;
65
66
67 /*
68 ** Procedure: Creats an accepted payment method for the payee specified
69 ** Parameters:
70 ** i_ecappid : Ec Application's id.
71 ** i_payeetype : Type of the payee class. This identifies the table to
72 ** accessed.
73 ** i_payeeid : id of the payee.
74 ** i_instrtype : type of the instrument. Ex, BANKACCT, CREDITCARD etc.
75 ** i_accttype : Type of the account. Example, Checking, SAVINGS, VISA, MATERCARD.
76 */
77 procedure createAccpPmtMthd(i_ecappid in iby_accppmtmthd.ecappid%type,
78 i_payeeid in iby_accppmtmthd.payeeid%type,
79 i_instrtype in iby_accttype.instrtype%type,
80 i_accttype in iby_accttype.accttype%type )
81 is
82
83 l_accttypeid iby_accttype.accttypeid%type;
84 l_mpayeeid iby_accppmtmthd.mpayeeid%type;
85 l_status iby_accppmtmthd.status%type;
86
87 begin
88
89 -- check to make sure input ecappid is valid
90 if (not iby_ecapp_pkg.ecappExists(i_ecappid)) then
91 --raise_application_error(-20550, 'ECApp id not registered',FALSE);
92 raise_application_error(-20000, 'IBY_20550#', FALSE);
93 end if;
94
95 -- check to make sure input payeeid is valid, and obtain mpayeeid
96 getMPayeeId(i_payeeid, l_mpayeeid);
97
98 -- for preexisted pmtMthd, just set it to active, otherwise, create an
99 -- entry
100 if (pmtMthdExists(i_ecappid, i_payeeid, i_instrtype, i_accttype,
101 l_status)) then
102 if (l_status = 1) then
103 -- already added
104 raise_application_error(-20000, 'IBY_20500#', FALSE);
105 --raise_application_error(-20500, 'Accepted Pmt Mthd Already Exists.', FALSE);
106 else
107 UPDATE iby_accppmtmthd
108 SET status = 1,
109 last_update_date = sysdate,
110 last_updated_by = fnd_global.user_id,
111 last_update_login = fnd_global.login_id
112 WHERE payeeid = i_payeeid
113 AND ecappid = i_ecappid
114 AND status = 0
115 AND accttypeid in ( SELECT accttypeid
116 FROM iby_accttype acct
117 WHERE acct.instrtype = i_instrtype
118 AND acct.accttype = i_accttype );
119 end if;
120 else
121
122 -- brand new, add it in
123 -- create an acct type as needed
124 iby_accttype_pkg.createAccttype(i_accttype, i_instrtype,
125 l_accttypeid);
126 INSERT INTO iby_accppmtmthd ( ecappid, mpayeeid, payeeid,
127 accttypeid, status,
128 last_update_date, last_updated_by,
129 creation_date, created_by,
130 last_update_login, object_version_number)
131 VALUES ( i_ecappid, l_mpayeeid, i_payeeid, l_accttypeid , 1,
132 sysdate, fnd_global.user_id,
133 sysdate, fnd_global.user_id,
134 fnd_global.login_id, 1);
135 end if;
136
137 commit;
138 end;
139
140
141 /*
142 ** Procedure: Deletes an accepted payment method by a payeeid.
143 ** i_ecappid : Ec Application's id.
144 ** i_payeetype : Type of the payee class. This identifies the table to
145 ** accessed.
146 ** i_payeeid : id of the payee.
147 ** i_instrtype : type of the instrument. Ex, BANKACCT, CREDITCARD etc.
148 ** i_accttype : Type of the account. Example, Checking, SAVINGS, VISA, MATERCARD.
149 */
150 procedure deleteAccpPmtMthd(i_ecappid iby_accppmtmthd.ecappid%type,
151 i_payeeid iby_accppmtmthd.payeeid%type,
152 i_instrtype iby_accttype.instrtype%type,
153 i_accttype iby_accttype.accttype%type)
154 is
155 begin
156 /*
157 ** Update the iby_accppmtmthd table to mark the row as inactive.
158 ** If there are no rows present, then raise an exception.
159 */
160 UPDATE iby_accppmtmthd
161 SET status = 0,
162 last_update_date = sysdate,
163 last_updated_by = fnd_global.user_id,
164 last_update_login = fnd_global.login_id
165 WHERE payeeid = i_payeeid
166 AND ecappid = i_ecappid
167 AND status = 1
168 AND accttypeid in ( SELECT accttypeid
169 FROM iby_accttype acct
170 WHERE acct.instrtype = i_instrtype
171 AND acct.accttype = i_accttype );
172 if ( sql%notfound ) then
173 raise_application_error(-20000, 'IBY_20501#', FALSE);
174 --raise_application_error(-20501, 'NO Accepted Pmt Mthd Objects matched ', FALSE);
175
176 -- multiple row match will NEVER happen
177 --elsif ( sql%rowcount <> 1 ) then
178 --raise_application_error(-20000, ' Rows ' || sql%rowcount || ' matched, so not deleting ', FALSE);
179
180 end if;
181 commit;
182 end;
183 end iby_accppmtmthd_pkg;
[Home] [Help]