1 package body iby_instrholder_pkg as
2 /*$Header: ibyhdisb.pls 115.11 2003/05/30 10:59:43 nmukerje ship $*/
3
4 /*
5 ** Function: holderInstrExists
6 ** Purpose: checks whether the corresponding id of the holder holds the
7 ** instrument or not.
8 */
9 function instrholderExists(i_ecappid in iby_ecapp.ecappid%type,
10 i_hld_type in iby_instrholder.ownerType%type,
11 i_hld_id in iby_instrholder.ownerId%type,
12 i_instr_type in iby_instrholder.instrtype%type,
13 i_instr_id in iby_instrholder.instrid%type)
14 return boolean
15 is
16 l_flag boolean := false;
17 l_junk integer;
18 cursor c_holderinstr(
19 ci_hld_type in iby_instrholder.ownertype%type,
20 ci_hld_id in iby_instrholder.ownerid%type,
21 ci_instr_type in iby_instrholder.instrtype%type,
22 ci_instr_id in iby_instrholder.instrid%type) is
23 select 1
24 from iby_instrholder
25 where ci_hld_type = ownertype
26 and ci_hld_id = ownerid
27 and ci_instr_type = instrtype
28 and ci_instr_id = instrid
29 and activestatus = 1;
30 begin
31 /*
32 ** if cursor is already open close it.
33 */
34 if ( c_holderinstr%isopen ) then
35 close c_holderinstr;
36 end if;
37 /*
38 ** open the cursor with proper input parameters.
39 */
40 open c_holderinstr(i_hld_type, i_hld_id,
41 i_instr_type, i_instr_id);
42 fetch c_holderinstr into l_junk;
43 /*
44 ** if any rows exist that are active then return true,
45 ** otherwise holder_id does not hole the
46 ** the instrument.
47 */
48 if ( c_holderinstr%found ) then
49 l_flag := true;
50 else
51 l_flag := false;
52 end if;
53 close c_holderinstr;
54 return l_flag;
55 end instrholderExists;
56 /*
57 ** Function: payeeAcctExists
58 ** Purpose: checks whether the corresponding id of the holder holds the
59 ** isntrument or not.
60 */
61 function payeeAcctExists(i_ecappid in iby_ecapp.ecappid%type,
62 i_hld_id in iby_instrholder.ownerId%type)
63 return boolean
64 is
65 l_flag boolean := false;
66 l_junk integer;
67 cursor c_holderinstr(
68 ci_hld_id in iby_instrholder.ownerid%type) is
69 select 1
70 from iby_instrholder
71 where 'PAYEE' = ownertype
72 and ci_hld_id = ownerid
73 and instrtype = 'BANKACCOUNT'
74 and activestatus = 1;
75 begin
76 /*
77 ** if cursor is already open close it.
78 */
79 if ( c_holderinstr%isopen ) then
80 close c_holderinstr;
81 end if;
82 /*
83 ** open the cursor with proper input parameters.
84 */
85 open c_holderinstr(i_hld_id);
86 fetch c_holderinstr into l_junk;
87 /*
88 ** if any rows exist that are active then return true,
89 ** otherwise holder_id does not hole the
90 ** the instrument.
91 */
92 if ( c_holderinstr%found ) then
93 l_flag := true;
94 else
95 l_flag := false;
96 end if;
97 close c_holderinstr;
98 return l_flag;
99 end payeeAcctExists;
100 /*
101 ** Procedure: createHolderInstr.
102 ** Purpose: create a row in holder instrument table. This table keeps
103 ** track of the instrument and its holder information.
104 ** In Parameters: i_hld_type, type of the holder. (payee, user, etc..
105 ** i_hld_id, id of the holder.
106 ** i_ecappid, ec application id through which the holder is
107 ** created. instr_type and instr_id are type of instrument
108 ** BANKACCT or CREDITCARD, and it's id respectively.
109 */
110 procedure createHolderInstr(i_ecappid in iby_ecapp.ecappid%type,
111 i_hld_type in iby_instrholder.ownertype%type,
112 i_hld_id in iby_instrholder.ownerid%type,
113 i_hld_address_id in iby_instrholder.owneraddressid%type,
114 i_instr_type in iby_instrholder.instrtype%type,
115 i_instr_id in iby_instrholder.instrid%type)
116 is
117 begin
118 /*
119 ** insert the holder and instrument information and mark the status as
120 ** active.
121 */
122 insert into iby_instrholder ( ownertype, ownerid, owneraddressid,instrtype, instrid, activestatus,
123 last_update_date, last_updated_by, creation_date,
124 created_by,object_version_number)
125 values ( i_hld_type, i_hld_id,i_hld_address_id,i_instr_type, i_instr_id, 1,
126 sysdate, fnd_global.user_id, sysdate,
127 fnd_global.user_id, 1);
128 --commit;
129 end createHolderinstr;
130
131
132 /*
133 ** Procedure: deleteHolderInstr.
134 ** Purpose: marks the record identified by the ownerid, ownertype and
135 ** instrid and instrtype as in inactivated.
136 */
137 procedure deleteHolderInstr(i_ecappid in iby_ecapp.ecappid%type,
138 i_ownertype in iby_instrholder.ownertype%type,
139 i_ownerid in iby_instrholder.ownerid%type,
140 i_instrtype in iby_instrholder.instrtype%type,
141 i_instrid in iby_instrholder.instrid%type)
142 is
143 l_cnt integer;
144
145 -- Constant declaration for the various transaction status
146 -- when the instrument should not be modified or deleted.
147
148 C_COMMUNICATION_ERROR CONSTANT NUMBER(3) := 1;
149 C_REQUEST_PENDING CONSTANT NUMBER(3) := 11;
150 C_SCHED_IN_PROGRESS CONSTANT NUMBER(3) := 12;
151 C_REQUEST_SCHEDULED CONSTANT NUMBER(3) := 13;
152 C_VOICE_AUTH_REQD CONSTANT NUMBER(3) := 21;
153
154 begin
155 /*
156 ** check if there are any pewnding requests for this holderid;
157 ** if so, raise an exception.
158 */
159 if ( i_ownertype = 'USER' ) then
160 select count(*) into l_cnt
161 from iby_trxn_summaries_all ps,
162 iby_instrholder pih
163 where ps.payerinstrid = pih.instrid
164 and pih.ownertype = i_ownertype
165 and pih.ownerid = i_ownerid
166 and pih.instrid = i_instrid
167 and ps.status IN ( C_COMMUNICATION_ERROR,C_REQUEST_PENDING,C_SCHED_IN_PROGRESS,
168 C_REQUEST_SCHEDULED,C_VOICE_AUTH_REQD );
169
170 -- Commented,as payee never registers/modifies an instrument.
171 --else
172 --select count(*) into l_cnt
173 --from iby_trxn_summaries_all ps,
174 --iby_instrholder pih
175 --where ps.payeeinstrid = pih.instrid
176 --and pih.ownertype = i_ownertype
177 --and pih.ownerid = i_ownerid
178 --and pih.instrid = i_instrid
179 --and ps.status IN ( 1, 11, 12, 13, 21);
180 end if;
181 if ( l_cnt <> 0 ) then
182 raise_application_error(-20000, 'IBY_20516#', FALSE);
183 --raise_application_error(-20516, 'Some Payments are Still pending', FALSE);
184 end if;
185 /*
186 ** mark the status of the matched record as '0'(inactive).
187 */
188
189 if (i_instrtype is null) then
190 update iby_instrholder
191 set activestatus = 0,
192 last_update_date = sysdate,
193 last_updated_by = fnd_global.user_id,
194 last_update_login = fnd_global.login_id
195 where ownertype = i_ownertype
196 and ownerid = i_ownerid
197 and activestatus = 1
198 and instrid = i_instrid;
199 else
200 update iby_instrholder
201 set activestatus = 0,
202 last_update_date = sysdate,
203 last_updated_by = fnd_global.user_id,
204 last_update_login = fnd_global.login_id
205 where ownertype = i_ownertype
206 and ownerid = i_ownerid
207 and instrtype = i_instrtype
208 and activestatus = 1
209 and instrid = i_instrid;
210
211 end if;
212
213 if ( sql%notfound ) then
214 raise_application_error(-20000, 'IBY_20511#', FALSE);
215 --raise_application_error(-20511, 'User does not hold instr', FALSE);
216 elsif ( sql%rowcount <> 1 ) then
217 raise_application_error(-20000, 'IBY_20000#', FALSE);
218 --raise_application_error(-20000, ' Rows ' || sql%rowcount || ' matched, so not deleting ', FALSE);
219 end if;
220 commit;
221 end deleteHolderInstr;
222
223 procedure getHolderinstr( i_ecappid in iby_ecapp.ecappid%type,
224 i_hld_type in iby_instrholder.ownertype%type,
225 i_hld_id in iby_instrholder.ownerid%type,
226 o_instr_type out nocopy iby_instrholder.instrtype%type,
227 o_instr_id out nocopy iby_instrholder.instrid%type)
228 is
229 cursor c_holderinstr(ci_hld_type in iby_instrholder.ownertype%type,
230 ci_hld_id in iby_instrholder.ownerid%type) is
231 select instrtype, instrid
232 from iby_instrholder
233 where ci_hld_type = ownertype
234 and activestatus = 1
235 and ci_hld_id = ownerid;
236 begin
237 /*
238 ** close the cursor, if it is open.
239 */
240 if ( c_holderinstr%isopen ) then
241 close c_holderinstr;
242 end if;
243 /*
244 ** open the cursor to extract all the activer records.
245 */
246 open c_holderinstr(i_hld_type, i_hld_id);
247
248 fetch c_holderinstr into o_instr_type, o_instr_id;
249 if ( c_holderinstr%notfound ) then
250 /*
251 ** if not instrument matched then raise an error.
252 */
253 close c_holderinstr;
254 raise_application_error(-20000, 'IBY_20512#', FALSE);
255 --raise_application_error(-20512, 'No instrument Matched ', FALSE);
256 end if;
257 close c_holderinstr;
258 end getHolderInstr;
259 /*
260 ** Procedure: deleteInstr.
261 ** Purpose: marks the record identified by the ownerid, ownertype and
262 ** instrid as inactivated.
263 */
264 procedure deleteInstr( i_ecappid in iby_ecapp.ecappid%type,
265 i_ownertype in iby_instrholder.ownertype%type,
266 i_ownerid in iby_instrholder.ownerid%type,
267 i_instrid in iby_instrholder.instrid%type)
268 is
269 begin
270
271 -- we don't care what type of instrment it is, just delete it
272 deleteHolderInstr(i_ecappid, i_ownertype, i_ownerid, null,
273 i_instrid);
274
275 end deleteInstr;
276 end iby_instrholder_pkg;
[Home] [Help]