1 package body iby_bepkeys_pkg as
2 /*$Header: ibybepkb.pls 120.2 2005/10/30 05:49:46 appldev ship $*/
3
4 /*
5 ** Function: bepKeyExists.
6 ** Purpose: Check if the specified payeeid, bepid exists or not.
7 ** Unique constraints on 'bepid', 'key'
8 **
9 ** Previously, we require Unique constraints on 'bepid', 'key' and
10 ** 'payeeid'
11 ** This is giving problem w/ closebatch as each BEP will associate 'key'
12 ** with one payee only
13 **
14 ** It will fail at the following case:
15 ** bepid key payeeid
16 ** 1 oracle payee1
17 ** 1 oracle payee2
18 **
19 ** Given key 'oracle' it won't know which payee it comes from
20 **
21 ** Now we require uniqueness on 'bepid', 'key' alone
22 **
23 ** The output parameters 'o_ownerid, o_bepname' are for error message
24 ** only.
25 */
26
27 function bepKeyExists(i_bepid in iby_bepinfo.bepid%type,
28 i_ownertype in iby_bepkeys.ownertype%type,
29 i_bepkey in iby_bepkeys.key%type,
30 o_ownerid out nocopy iby_bepkeys.ownerid%type,
31 o_bepname out nocopy iby_bepinfo.name%type)
32
33 return boolean
34
35 IS
36
37 l_flag boolean := false;
38
39 -- to check if this key has already been used for given bep
40 -- i.e., all bep keys has to be distinct across all payees for a given bep
41 cursor c_owner ( ci_bepid iby_bepkeys.bepid%type,
42 ci_ownertype iby_bepkeys.ownertype%type,
43 ci_bepkey iby_bepkeys.key%type)
44 is
45 select ownerid, name
46 from iby_bepkeys a, iby_bepinfo b
47 where a.key = ci_bepkey
48 and a.ownertype = ci_ownertype
49 and a.bepid = ci_bepid
50 AND a.bepid = b.bepid;
51
52 BEGIN
53 o_ownerid := NULL;
54 o_bepname := NULL;
55
56 if ( c_owner%isopen) then
57 close c_owner;
58 end if;
59
60 open c_owner(i_bepid, i_ownertype, i_bepkey);
61 fetch c_owner into o_ownerid, o_bepname;
62
63 l_flag := (c_owner%found);
64
65 close c_owner;
66
67 return l_flag;
68 END bepKeyExists;
69
70 /*
71 ** Precedure: deleteBEPKeys
72 ** Purpose: delete ALL bepkeys associated with a payee
73 **
74 **
75 */
76 procedure deleteBEPKeys(i_ownerid in iby_bepkeys.ownerid%type,
77 i_ownertype in iby_bepkeys.ownertype%type)
78 is
79 begin
80 DELETE FROM iby_bepkeys
81 WHERE ownerid = i_ownerid
82 AND ownertype = i_ownertype;
83 end deleteBEPKeys;
84
85
86 /*
87 ** Procedure: createBEPKey.
88 ** Purpose: creates a SINGLE bep key entry in iby_bepkeys table
89 ** parameters: i_ownerid, i_ownertype identifies the owner of the key.
90 ** i_bepid, id of the back end payment systems.
91 */
92 procedure createBEPKey(i_bepid in iby_bepinfo.bepid%type,
93 i_ownertype in iby_bepkeys.ownertype%type,
94 i_ownerid in iby_bepkeys.ownerid%type,
95 i_key in iby_bepkeys.key%type,
96 i_default in iby_bepkeys.defaults%type)
97 is
98 l_bepid iby_bepkeys.bepid%type;
99 l_ownerid iby_bepkeys.ownerid%type;
100 l_bepname iby_bepinfo.name%type;
101 l_bep_account_id iby_bepkeys.bep_account_id%TYPE;
102
103 begin
104
105 --get the bepid based on name of the bep.
106
107 l_bepid := i_bepid;
108 if ( l_bepid = -99 ) then
109 raise_application_error(-20000, 'IBY_20521#', FALSE);
110 --raise_application_error(-20521, 'NO BEP Info matched ', FALSE);
111 end if;
112
113 IF ( bepKeyExists(l_bepid, i_ownertype, i_key, l_ownerid, l_bepname)) THEN
114 ---uniqueness constraints violated
115 raise_application_error(-20000,
116 'IBY_20526#KEY=' || i_key ||
117 '#BEP=' || l_bepname || '#PAYEEID='
118 || l_ownerid, FALSE);
119 END IF;
120
121 SELECT iby_bepkeys_s.NEXTVAL
122 INTO l_bep_account_id
123 FROM dual;
124
125 -- create new keys
126 INSERT INTO iby_bepkeys ( bep_account_id, bepid, ownertype,
127 ownerid, key, defaults,
128 last_update_date, last_updated_by,
129 creation_date, created_by,
130 last_update_login, object_version_number)
131 VALUES ( l_bep_account_id, l_bepid, i_ownertype,
132 i_ownerid, i_key, i_default,
133 sysdate, fnd_global.user_id,
134 sysdate, fnd_global.user_id,
135 fnd_global.login_id, 1);
136 end createBEPKey;
137
138 end iby_bepkeys_pkg;
[Home] [Help]