[Home] [Help]
PACKAGE BODY: APPS.AMS_APPROVERS_PKG
Source
1 package body AMS_APPROVERS_PKG as
2 /* $Header: amslaprb.pls 120.1 2005/06/27 05:38:04 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_APPROVER_ID in NUMBER,
6 X_SEEDED_FLAG in VARCHAR2,
7 X_ACTIVE_FLAG in VARCHAR2,
8 X_START_DATE_ACTIVE in DATE,
9 X_END_DATE_ACTIVE in DATE,
10 X_OBJECT_VERSION_NUMBER in NUMBER,
11 --X_SECURITY_GROUP_ID in NUMBER,
12 X_AMS_APPROVAL_DETAIL_ID in NUMBER,
13 X_APPROVER_SEQ in NUMBER,
14 X_APPROVER_TYPE in VARCHAR2,
15 X_OBJECT_APPROVER_ID in NUMBER,
16 X_NOTIFICATION_TYPE in VARCHAR2,
17 X_NOTIFICATION_TIMEOUT in NUMBER,
18 X_CREATION_DATE in DATE,
19 X_CREATED_BY in NUMBER,
20 X_LAST_UPDATE_DATE in DATE,
21 X_LAST_UPDATED_BY in NUMBER,
22 X_LAST_UPDATE_LOGIN in NUMBER
23 ) is
24 begin
25 insert into AMS_APPROVERS (
26 SEEDED_FLAG,
27 ACTIVE_FLAG,
28 START_DATE_ACTIVE,
29 END_DATE_ACTIVE,
30 APPROVER_ID,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_LOGIN,
36 OBJECT_VERSION_NUMBER,
37 --SECURITY_GROUP_ID,
38 AMS_APPROVAL_DETAIL_ID,
39 APPROVER_SEQ,
40 APPROVER_TYPE,
41 OBJECT_APPROVER_ID,
42 NOTIFICATION_TYPE,
43 NOTIFICATION_TIMEOUT
44 ) values (
45 X_SEEDED_FLAG,
46 nvl(X_ACTIVE_FLAG, 'Y'),
47 X_START_DATE_ACTIVE,
48 X_END_DATE_ACTIVE,
49 X_APPROVER_ID,
50 X_LAST_UPDATE_DATE,
51 X_LAST_UPDATED_BY,
52 X_CREATION_DATE,
53 X_CREATED_BY,
54 X_LAST_UPDATE_LOGIN,
55 X_OBJECT_VERSION_NUMBER,
56 --X_SECURITY_GROUP_ID,
57 X_AMS_APPROVAL_DETAIL_ID,
58 X_APPROVER_SEQ,
59 X_APPROVER_TYPE,
60 X_OBJECT_APPROVER_ID,
61 X_NOTIFICATION_TYPE,
62 X_NOTIFICATION_TIMEOUT
63 );
64
65 end INSERT_ROW;
66
67 procedure LOCK_ROW (
68 X_APPROVER_ID in NUMBER,
69 X_SEEDED_FLAG in VARCHAR2,
70 X_ACTIVE_FLAG in VARCHAR2,
71 X_START_DATE_ACTIVE in DATE,
72 X_END_DATE_ACTIVE in DATE,
73 X_OBJECT_VERSION_NUMBER in NUMBER,
74 -- X_SECURITY_GROUP_ID in NUMBER,
75 X_AMS_APPROVAL_DETAIL_ID in NUMBER,
76 X_APPROVER_SEQ in NUMBER,
77 X_APPROVER_TYPE in VARCHAR2,
78 X_OBJECT_APPROVER_ID in NUMBER,
79 X_NOTIFICATION_TYPE in VARCHAR2,
80 X_NOTIFICATION_TIMEOUT in NUMBER
81 ) is
82 cursor c1 is select
83 SEEDED_FLAG,
84 ACTIVE_FLAG,
85 START_DATE_ACTIVE,
86 END_DATE_ACTIVE,
87 OBJECT_VERSION_NUMBER,
88 --SECURITY_GROUP_ID,
89 AMS_APPROVAL_DETAIL_ID,
90 APPROVER_SEQ,
91 APPROVER_TYPE,
92 OBJECT_APPROVER_ID,
93 NOTIFICATION_TYPE,
94 NOTIFICATION_TIMEOUT,
95 APPROVER_ID
96 from AMS_APPROVERS
97 where APPROVER_ID = X_APPROVER_ID
98 for update of APPROVER_ID nowait;
99 begin
100 for tlinfo in c1 loop
101 if ( (tlinfo.APPROVER_ID = X_APPROVER_ID)
102 AND ((tlinfo.SEEDED_FLAG = X_SEEDED_FLAG)
103 OR ((tlinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
104 AND ((tlinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
105 OR ((tlinfo.ACTIVE_FLAG is null) AND (X_ACTIVE_FLAG is null)))
106 AND ((tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
107 OR ((tlinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
108 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
109 OR ((tlinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
110 AND ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
111 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
112 --AND ((tlinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
113 -- OR ((tlinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
114 AND (tlinfo.AMS_APPROVAL_DETAIL_ID = X_AMS_APPROVAL_DETAIL_ID)
115 AND ((tlinfo.APPROVER_SEQ = X_APPROVER_SEQ)
116 OR ((tlinfo.APPROVER_SEQ is null) AND (X_APPROVER_SEQ is null)))
117 AND ((tlinfo.APPROVER_TYPE = X_APPROVER_TYPE)
118 OR ((tlinfo.APPROVER_TYPE is null) AND (X_APPROVER_TYPE is null)))
119 AND ((tlinfo.OBJECT_APPROVER_ID = X_OBJECT_APPROVER_ID)
120 OR ((tlinfo.OBJECT_APPROVER_ID is null) AND (X_OBJECT_APPROVER_ID is null)))
121 AND ((tlinfo.NOTIFICATION_TYPE = X_NOTIFICATION_TYPE)
122 OR ((tlinfo.NOTIFICATION_TYPE is null) AND (X_NOTIFICATION_TYPE is null)))
123 AND ((tlinfo.NOTIFICATION_TIMEOUT = X_NOTIFICATION_TIMEOUT)
124 OR ((tlinfo.NOTIFICATION_TIMEOUT is null) AND (X_NOTIFICATION_TIMEOUT is null)))
125 ) then
126 null;
127 else
128 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
129 app_exception.raise_exception;
130 end if;
131 end loop;
132 return;
133 end LOCK_ROW;
134
135 procedure UPDATE_ROW (
136 X_APPROVER_ID in NUMBER,
137 X_SEEDED_FLAG in VARCHAR2,
138 X_ACTIVE_FLAG in VARCHAR2,
139 X_START_DATE_ACTIVE in DATE,
140 X_END_DATE_ACTIVE in DATE,
141 X_OBJECT_VERSION_NUMBER in NUMBER,
142 -- X_SECURITY_GROUP_ID in NUMBER,
143 X_AMS_APPROVAL_DETAIL_ID in NUMBER,
144 X_APPROVER_SEQ in NUMBER,
145 X_APPROVER_TYPE in VARCHAR2,
146 X_OBJECT_APPROVER_ID in NUMBER,
147 X_NOTIFICATION_TYPE in VARCHAR2,
148 X_NOTIFICATION_TIMEOUT in NUMBER,
149 X_LAST_UPDATE_DATE in DATE,
150 X_LAST_UPDATED_BY in NUMBER,
151 X_LAST_UPDATE_LOGIN in NUMBER
152 ) is
153 begin
154 update AMS_APPROVERS set
155 SEEDED_FLAG = X_SEEDED_FLAG,
156 ACTIVE_FLAG = nvl(X_ACTIVE_FLAG, 'Y'),
157 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
158 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
159 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
160 --SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
161 AMS_APPROVAL_DETAIL_ID = X_AMS_APPROVAL_DETAIL_ID,
162 APPROVER_SEQ = X_APPROVER_SEQ,
163 APPROVER_TYPE = X_APPROVER_TYPE,
164 OBJECT_APPROVER_ID = X_OBJECT_APPROVER_ID,
165 NOTIFICATION_TYPE = X_NOTIFICATION_TYPE,
166 NOTIFICATION_TIMEOUT = X_NOTIFICATION_TIMEOUT,
167 APPROVER_ID = X_APPROVER_ID,
168 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
169 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
171 where APPROVER_ID = X_APPROVER_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176 end UPDATE_ROW;
177
178 procedure DELETE_ROW (
179 X_APPROVER_ID in NUMBER
180 ) is
181 begin
182 delete from AMS_APPROVERS
183 where APPROVER_ID = X_APPROVER_ID;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188
189 end DELETE_ROW;
190
191 procedure LOAD_ROW (
192 X_APPROVER_ID in NUMBER,
193 X_SEEDED_FLAG in VARCHAR2,
194 X_ACTIVE_FLAG in VARCHAR2,
195 X_START_DATE_ACTIVE in DATE,
196 X_END_DATE_ACTIVE in DATE,
197 X_OBJECT_VERSION_NUMBER in NUMBER,
198 --X_SECURITY_GROUP_ID in NUMBER,
199 X_AMS_APPROVAL_DETAIL_ID in NUMBER,
200 X_APPROVER_SEQ in NUMBER,
201 X_APPROVER_TYPE in VARCHAR2,
202 X_OBJECT_APPROVER_ID in NUMBER,
203 X_NOTIFICATION_TYPE in VARCHAR2,
204 X_NOTIFICATION_TIMEOUT in NUMBER,
205 X_OWNER in VARCHAR2
206 ) IS
207 l_user_id number := 0;
208 l_obj_verno number;
209 l_approver_id number;
210 l_dummy_char varchar2(1);
211 l_row_id varchar2(100);
212
213 cursor c_obj_verno is
214 select object_version_number
215 from AMS_APPROVERS
216 where approver_id = X_APPROVER_ID;
217
218 cursor c_chk_apr_exists is
219 select 'x'
220 from AMS_APPROVERS
221 where approver_id = X_APPROVER_ID;
222
223 cursor c_get_apr_id is
224 select AMS_APPROVERS_S.nextval
225 from dual;
226
227 BEGIN
228 if X_OWNER = 'SEED' then
229 l_user_id := 1;
230 end if;
231
232 open c_chk_apr_exists;
233 fetch c_chk_apr_exists into l_dummy_char;
234 if c_chk_apr_exists%notfound
235 then
236 close c_chk_apr_exists;
237 if X_APPROVER_ID is null
238 then
239 open c_get_apr_id;
240 fetch c_get_apr_id into l_approver_id;
241 close c_get_apr_id;
242 else
243 l_approver_id := X_APPROVER_ID;
244 end if;
245 l_obj_verno := 1;
246 AMS_APPROVERS_PKG.INSERT_ROW(
247 X_ROWID => l_row_id,
248 X_APPROVER_ID => l_approver_id,
249 X_SEEDED_FLAG => X_SEEDED_FLAG,
250 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
251 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
252 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
253 X_OBJECT_VERSION_NUMBER => l_obj_verno,
254 --X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
255 X_AMS_APPROVAL_DETAIL_ID => X_AMS_APPROVAL_DETAIL_ID,
256 X_APPROVER_SEQ => X_APPROVER_SEQ,
257 X_APPROVER_TYPE => X_APPROVER_TYPE,
258 X_OBJECT_APPROVER_ID => X_OBJECT_APPROVER_ID,
259 X_NOTIFICATION_TYPE => X_NOTIFICATION_TYPE,
260 X_NOTIFICATION_TIMEOUT => X_NOTIFICATION_TYPE,
261 X_CREATION_DATE => SYSDATE,
262 X_CREATED_BY => l_user_id,
263 X_LAST_UPDATE_DATE => SYSDATE,
264 X_LAST_UPDATED_BY => l_user_id,
265 X_LAST_UPDATE_LOGIN => 0
266 );
267 else
268 close c_chk_apr_exists;
269 open c_obj_verno;
270 fetch c_obj_verno into l_obj_verno;
271 close c_obj_verno;
272 -- assigning value for l_user_status_id
273 l_approver_id := X_APPROVER_ID;
274 AMS_APPROVERS_PKG.UPDATE_ROW(
275 X_APPROVER_ID => l_approver_id,
276 X_SEEDED_FLAG => X_SEEDED_FLAG,
277 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
278 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
279 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
280 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
281 --X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
282 X_AMS_APPROVAL_DETAIL_ID => X_AMS_APPROVAL_DETAIL_ID,
283 X_APPROVER_SEQ => X_APPROVER_SEQ,
284 X_APPROVER_TYPE => X_APPROVER_TYPE,
285 X_OBJECT_APPROVER_ID => X_OBJECT_APPROVER_ID,
286 X_NOTIFICATION_TYPE => X_NOTIFICATION_TYPE,
287 X_NOTIFICATION_TIMEOUT => X_NOTIFICATION_TIMEOUT,
288 X_LAST_UPDATE_DATE => SYSDATE,
289 X_LAST_UPDATED_BY => l_user_id,
290 X_LAST_UPDATE_LOGIN => 0
291 );
292 END IF;
293
294 END LOAD_ROW;
295
296 END AMS_APPROVERS_PKG;
297
298