1 package body iby_ecapp_pkg as
2 /*$Header: ibyecapb.pls 115.11 2002/11/18 22:04:50 jleybovi ship $*/
3
4
5 /*
6 ** Function: ecappShortNameExists.
7 ** Purpose: Check if the specified application short name exists.
8 ** ecappid of the existing one is outputted for modification
9 ** purpose in case this short name is not updated
10 **
11 ** not case sensitive
12 */
13 function ecappShortNameExists(i_app_short_name
14 iby_ecapp.application_short_name%type,
15 o_ecappid out nocopy iby_ecapp.ecappid%type)
16 return boolean
17
18 is
19 l_app_short_name iby_ecapp.application_short_name%type;
20 l_flag boolean := false;
21
22 cursor c_app_short_name (ci_app_short_name
23 iby_ecapp.application_short_name%type)
24 is
25 SELECT application_short_name, ecappid
26 FROM iby_ecapp_v
27 WHERE UPPER(application_short_name) = UPPER(ci_app_short_name);
28
29 begin
30
31 o_ecappid := -1;
32
33 if ( c_app_short_name%isopen) then
34 close c_app_short_name;
35 end if;
36
37 open c_app_short_name( i_app_short_name);
38 fetch c_app_short_name into l_app_short_name, o_ecappid;
39
40 l_flag := c_app_short_name%found;
41
42 close c_app_short_name;
43 return l_flag;
44 end ecappShortNameExists;
45
46
47 /*
48 ** Function: ecappExists.
49 ** Purpose: Check if the specified ecappid exists or not.
50 */
51 function ecappExists(i_ecappid in iby_ecapp.ecappid%type)
52 return boolean
53 is
54 l_ecappid iby_ecapp.ecappid%type;
55 l_flag boolean := false;
56
57 cursor c_ecappid
58 (ci_ecappid iby_ecapp.ecappid%type)
59 is
60 SELECT ecappid
61 FROM iby_ecapp_v
62 WHERE ecappid = ci_ecappid;
63 begin
64 if ( c_ecappid%isopen) then
65 close c_ecappid;
66 end if;
67
68 open c_ecappid( i_ecappid);
69 fetch c_ecappid into l_ecappid;
70
71 l_flag := c_ecappid%found;
72
73 close c_ecappid;
74 return l_flag;
75 end ecappExists;
76
77 /*
78 ** Procedure Name : createEcApp
79 ** Purpose : creates an entry in the ecapp table. Returns the id created
80 ** by the system.
81 **
82 ** Parameters:
83 **
84 ** In : i_ecappname
85 ** Out : io_ecappid.
86 **
87 */
88 procedure createEcApp(i_ecappname iby_ecapp.name%type,
89 i_app_short_name iby_ecapp.application_short_name%type,
90 io_ecappid in out nocopy iby_ecapp.ecappid%type)
91 is
92 NO_SEQUENCE_FOUND EXCEPTION;
93 cursor c_ecappid is
94 select iby_ecapp_s.nextval from dual;
95
96 l_dummy iby_ecapp.ecappid%type;
97
98 begin
99 if ( c_ecappid%isopen ) then
100 close c_ecappid;
101 end if;
102 open c_ecappid;
103 fetch c_ecappid into io_ecappid;
104
105 -- check to make sure short name is unique
106 if (ecappShortNameExists(i_app_short_name, l_dummy)) then
107 raise_application_error(-20000,
108 'IBY_20551#',
109 FALSE);
110 end if;
111
112 INSERT into iby_ecapp (ecappid, name, application_short_name,
113 last_update_date, last_updated_by, creation_date, created_by,
114 last_update_login, object_version_number)
115 VALUES ( io_ecappid, i_ecappname, i_app_short_name,
116 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
117 fnd_global.login_id, 1);
118 close c_ecappid;
119 commit;
120 end createEcApp;
121
122 /*
123 ** Procedure Name : modEcApp
124 ** Purpose : modifies an entry in the ecapp table corresponding to id.
125 **
126 ** Parameters:
127 **
128 ** In : i_ecappid, i_ecappname
129 ** Out : None
130 **
131 */
132 procedure modEcApp(i_ecappid iby_ecapp.ecappid%type,
133 i_ecappname iby_ecapp.name%type,
134 i_app_short_name iby_ecapp.application_short_name%type,
135 i_object_version iby_ecapp.object_version_number%type)
136 is
137
138 l_ecappid iby_ecapp.ecappid%type;
139
140 begin
141
142 -- check the uniqueness of the application short name
143 if (ecappShortNameExists(i_app_short_name, l_ecappid)) then
144 if (l_ecappid <> i_ecappid) then
145 raise_application_error(-20000, 'IBY_20551#',
146 FALSE);
147 end if;
148 end if;
149
150 /*
151 ** update the row corresponding to the ecappid.
152 */
153 --- update only if the object_version_number is correct
154 if (i_object_version < 0) then
155 -- no check in object version number in this case
156 UPDATE iby_ecapp
157 SET name = i_ecappname, application_short_name = i_app_short_name,
158 last_update_date = sysdate,
159 last_update_login = fnd_global.login_id,
160 object_version_number = object_version_number + 1
161 WHERE ecappid = i_ecappid ;
162 else
163 UPDATE iby_ecapp
164 SET name = i_ecappname, application_short_name = i_app_short_name,
165 last_update_date = sysdate,
166 last_update_login = fnd_global.login_id,
167 object_version_number = object_version_number + 1
168 WHERE ecappid = i_ecappid
169 AND object_version_number = i_object_version;
170 end if;
171
172
173 if ( sql%notfound ) then
174 -- no row match
175 raise_application_error(-20550,
176 'Invalid ecappid or object version number',
177 FALSE);
178 -- don't need worry about multiple row match case
179 -- since ecappid is unique
180 end if;
181
182 commit;
183 end modecapp;
184 end iby_ecapp_pkg;
[Home] [Help]