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
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
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;
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;
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
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
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
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
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
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;
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
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;
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)
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
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
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: /*
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
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,
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
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;
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:
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
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
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
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
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
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
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,
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
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
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
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
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
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
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:
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;