[Home] [Help]
PACKAGE BODY: APPS.AMW_EXCEPTIONS_PKG
Source
1 PACKAGE BODY AMW_EXCEPTIONS_PKG as
2 /*$Header: amwexcpb.pls 120.0 2005/05/31 18:24:24 appldev noship $*/
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_EXCEPTIONS_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwexcpb.pls';
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9
10
11 procedure insert_exception_header_row (
12 p_Exception_Id IN Number,
13 p_Object_Type IN Varchar2,
14 p_Old_pk1 IN Varchar2,
15 p_Old_pk2 IN Varchar2,
16 p_Old_pk3 IN Varchar2,
17 p_Old_pk4 IN Varchar2,
18 p_Old_pk5 IN Varchar2,
19 p_Old_pk6 IN Varchar2,
20 p_New_pk1 IN Varchar2,
21 p_New_pk2 IN Varchar2,
22 p_New_pk3 IN Varchar2,
23 p_New_pk4 IN Varchar2,
24 p_New_pk5 IN Varchar2,
25 p_New_pk6 IN Varchar2,
26 p_Transaction_Type IN Varchar2,
27 p_Justification IN Varchar2,
28 p_person_party_id IN Number,
29 p_existing_ex_id IN Number,
30 p_commit in varchar2 := FND_API.G_FALSE,
31 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
32 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
33 x_return_status out nocopy varchar2,
34 x_msg_count out nocopy number,
35 x_msg_data out nocopy varchar2
36 ) is
37
38 L_API_NAME CONSTANT VARCHAR2(30) := 'insert_exception_header_row';
39
40 l_person_id number;
41 l_header varchar2(4000);
42 l_body varchar2(4000);
43 l_notif_id number;
44 l_ret_status varchar2(30);
45 l_nsp_disp_name varchar2(100);
46 l_sp_disp_name varchar2(100);
47
48
49 begin
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51 IF FND_API.to_Boolean( p_init_msg_list ) THEN
52 FND_MSG_PUB.initialize;
53 END IF;
54 IF FND_GLOBAL.User_Id IS NULL THEN
55 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
56 RAISE FND_API.G_EXC_ERROR;
57 END IF;
58
59 if ( (p_Object_Type = 'PROCESS_VARIANT_ADD') AND (p_Transaction_Type = 'DELETE_EXIST') ) then
60 delete from amw_exceptions_tl where exception_id IN
61 (select exception_id from amw_exceptions_b
62 where old_pk1 = p_Old_pk1 and new_pk1 = p_New_pk1
63 and object_type = 'PROCESS_VARIANT_ADD');
64
65 delete from amw_exceptions_reasons where exception_id IN
66 (select exception_id from amw_exceptions_b
67 where old_pk1 = p_Old_pk1 and new_pk1 = p_New_pk1
68 and object_type = 'PROCESS_VARIANT_ADD');
69
70 delete from amw_exceptions_b
71 where old_pk1 = p_Old_pk1 and new_pk1 = p_New_pk1
72 and object_type = 'PROCESS_VARIANT_ADD';
73
74 else
75
76 if p_existing_ex_id <> 0 then
77 delete from amw_exceptions_b where exception_id = p_existing_ex_id;
78 delete from amw_exceptions_tl where exception_id = p_existing_ex_id;
79 else
80 insert into amw_exceptions_b (
81 Exception_Id,
82 Object_Type,
83 Old_pk1,
84 Old_pk2,
85 Old_pk3,
86 Old_pk4,
87 Old_pk5,
88 Old_pk6,
89 New_pk1,
90 New_pk2,
91 New_pk3,
92 New_pk4,
93 New_pk5,
94 New_pk6,
95 Transaction_Type,
96 Transaction_Date,
97 End_Date,
98 Last_Update_Date,
99 Last_Updated_By,
100 Creation_Date,
101 Created_By,
102 Last_Update_Login,
103 OBJECT_VERSION_NUMBER
104 )
105 values
106 (
107 p_Exception_Id,
108 p_Object_Type,
109 decode(p_Old_pk1, 'IamNull', null, p_Old_pk1),
110 decode(p_Old_pk2, 'IamNull', null, p_Old_pk2),
111 decode(p_Old_pk3, 'IamNull', null, p_Old_pk3),
112 decode(p_Old_pk4, 'IamNull', null, p_Old_pk4),
113 p_Old_pk5,
114 p_Old_pk6,
115 decode(p_New_pk1, 'IamNull', null, p_New_pk1),
116 decode(p_New_pk2, 'IamNull', null, p_New_pk2),
117 decode(p_New_pk3, 'IamNull', null, p_New_pk3),
118 decode(p_New_pk4, 'IamNull', null, p_New_pk4),
119 p_New_pk5,
120 p_New_pk6,
121 p_Transaction_Type,
122 sysdate,
123 null,
124 sysdate,
125 G_USER_ID,
126 sysdate,
127 G_USER_ID,
128 G_LOGIN_ID,
129 1
130 );
131
132
133 insert into amw_exceptions_tl (
134 EXCEPTION_ID,
135 LANGUAGE,
136 SOURCE_LANG,
137 JUSTIFICATION,
138 LAST_UPDATE_DATE,
139 LAST_UPDATED_BY,
140 CREATION_DATE,
141 CREATED_BY,
142 LAST_UPDATE_LOGIN,
143 OLD_PROCESS_NAME,
144 NEW_PROCESS_NAME
145 ) select
146 p_Exception_Id,
147 L.LANGUAGE_CODE,
148 userenv('LANG'),
149 p_Justification,
150 sysdate,
151 G_USER_ID,
152 sysdate,
153 G_USER_ID,
154 G_LOGIN_ID,
155 decode(p_Object_Type,'PROCESS',decode(p_Old_pk3, 'IamNull', null, amw_utility_pvt.get_process_name(p_Old_pk3)),null),
156 decode(p_Object_Type,'PROCESS',decode(p_New_pk3, 'IamNull', null, amw_utility_pvt.get_process_name(p_New_pk3)),null)
157 from FND_LANGUAGES L
158 where L.INSTALLED_FLAG in ('I', 'B')
159 and not exists
160 (select NULL
161 from AMW_EXCEPTIONS_TL T
162 where T.EXCEPTION_ID = p_Exception_Id
163 and T.LANGUAGE = L.LANGUAGE_CODE);
164
165 end if; -- p_existing_ex_id 0
166
167 -- send notification for process variation.
168 -- adding it here since specifying a variant always involves creation of exception
169 if p_Transaction_Type = 'OVERALL_PROC_VAR' and p_Object_Type = 'PROCESS_VARIANT_ADD' then
170
171 l_person_id := 0;
172 if p_person_party_id <> 0 then
173
174 select employee_id
175 into l_person_id
176 from AMW_EMPLOYEES_CURRENT_V
177 where party_id = p_person_party_id;
178
179 select watl.display_name
180 into l_nsp_disp_name
181 from wf_activities_tl watl, wf_activities wa, amw_process ap
182 where ap.process_id = p_Old_pk1
183 and ap.name = wa.name
184 and wa.item_type = 'AUDITMGR'
185 and wa.end_date is null
186 and watl.item_type = 'AUDITMGR'
187 and watl.name = wa.name
188 and watl.version = wa.version
189 and watl.language = userenv('LANG');
190
191 select watl.display_name
192 into l_sp_disp_name
193 from wf_activities_tl watl, wf_activities wa, amw_process ap
194 where ap.process_id = p_New_pk1
195 and ap.name = wa.name
196 and wa.item_type = 'AUDITMGR'
197 and wa.end_date is null
198 and watl.item_type = 'AUDITMGR'
199 and watl.name = wa.name
200 and watl.version = wa.version
201 and watl.language = userenv('LANG');
202
203
204 fnd_message.set_name('AMW', 'AMW_PROC_VAR_HEAD');
205 l_header := fnd_message.get;
206
207 fnd_message.set_name('AMW', 'AMW_PROC_VAR_BODY');
208 fnd_message.set_token('STD', l_sp_disp_name);
209 fnd_message.set_token('NSTD', l_nsp_disp_name);
210 l_body := fnd_message.get;
211
212 AMW_Utility_PVT.send_wf_standalone_message( p_subject => l_header,
213 p_body => l_body,
214 p_send_to_person_id => l_person_id,
215 x_notif_id => l_notif_id,
216 x_return_status => l_ret_status);
217 end if;
218 end if;
219
220 end if; -- (p_Object_Type = 'PROCESS_VARIANT_ADD') AND (p_Transaction_Type = 'DELETE_EXIST')
221
222 exception
223 WHEN FND_API.G_EXC_ERROR THEN
224 ROLLBACK;
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
227
228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
229 ROLLBACK;
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
232
233 WHEN OTHERS THEN
234 ROLLBACK;
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
237 THEN
238 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
239 END IF;
240 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
241
242 end insert_exception_header_row;
243
244
245
246
247
248 procedure insert_exceptions_reasons_row (
249 p_EXCEPTION_ID in number,
250 p_REASON_CODE in varchar2,
251 p_existing_ex_id IN Number,
252 p_commit in varchar2 := FND_API.G_FALSE,
253 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
255 x_return_status out nocopy varchar2,
256 x_msg_count out nocopy number,
257 x_msg_data out nocopy varchar2
258 ) is
259
260 L_API_NAME CONSTANT VARCHAR2(30) := 'insert_exceptions_reasons_row';
261
262 begin
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264 IF FND_API.to_Boolean( p_init_msg_list ) THEN
265 FND_MSG_PUB.initialize;
266 END IF;
267 IF FND_GLOBAL.User_Id IS NULL THEN
268 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271
272 if p_existing_ex_id <> 0 then
273 delete from amw_exceptions_reasons where exception_id = p_existing_ex_id;
274 else
275 insert into amw_exceptions_reasons (
276 EXCEPTION_ID,
277 REASON_CODE,
278 Last_Update_Date,
279 Last_Updated_By,
280 Creation_Date,
281 Created_By,
282 Last_Update_Login,
283 OBJECT_VERSION_NUMBER
284 )
285 values
286 (
287 p_EXCEPTION_ID,
288 p_REASON_CODE,
289 sysdate,
290 G_USER_ID,
291 sysdate,
292 G_USER_ID,
293 G_LOGIN_ID,
294 1
295 );
296 end if; -- p_existing_ex_id 0
297
298 exception
299 WHEN FND_API.G_EXC_ERROR THEN
300 ROLLBACK;
301 x_return_status := FND_API.G_RET_STS_ERROR;
302 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
303
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305 ROLLBACK;
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
307 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
308
309 WHEN OTHERS THEN
313 THEN
310 ROLLBACK;
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
314 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
315 END IF;
316 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
317
318 end insert_exceptions_reasons_row;
319
320
321 procedure ADD_LANGUAGE
322 is
323 begin
324 delete from amw_exceptions_tl T
325 where not exists
326 (select NULL
327 from amw_exceptions_b B
328 where B.exception_id = T.exception_id
329 );
330
331 update amw_exceptions_tl T set (
332 JUSTIFICATION
333 ) = (select
334 B.JUSTIFICATION
335 from amw_exceptions_tl B
336 where B.exception_id = T.exception_id
337 and B.LANGUAGE = T.SOURCE_LANG)
338 where (
339 T.exception_id,
340 T.LANGUAGE
341 ) in (select
342 SUBT.exception_id,
343 SUBT.LANGUAGE
344 from amw_exceptions_tl SUBB, amw_exceptions_tl SUBT
345 where SUBB.exception_id = SUBT.exception_id
346 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
347 and (SUBB.JUSTIFICATION <> SUBT.JUSTIFICATION
348 or (SUBB.JUSTIFICATION is null and SUBT.JUSTIFICATION is not null)
349 or (SUBB.JUSTIFICATION is not null and SUBT.JUSTIFICATION is null)
350 ));
351
352
353 insert into amw_exceptions_tl (
354 exception_id,
355 JUSTIFICATION,
356 LAST_UPDATE_DATE,
357 LAST_UPDATED_BY,
358 CREATION_DATE,
359 CREATED_BY,
360 LAST_UPDATE_LOGIN,
361 SECURITY_GROUP_ID,
362 LANGUAGE,
363 SOURCE_LANG
364 ) select
365 B.exception_id,
366 B.JUSTIFICATION,
367 B.LAST_UPDATE_DATE,
368 B.LAST_UPDATED_BY,
369 B.CREATION_DATE,
370 B.CREATED_BY,
371 B.LAST_UPDATE_LOGIN,
372 B.SECURITY_GROUP_ID,
373 L.LANGUAGE_CODE,
374 B.SOURCE_LANG
375 from amw_exceptions_tl B, FND_LANGUAGES L
376 where L.INSTALLED_FLAG in ('I', 'B')
377 and B.LANGUAGE = userenv('LANG')
378 and not exists
379 (select NULL
380 from amw_exceptions_tl T
381 where T.exception_id = B.exception_id
382 and T.LANGUAGE = L.LANGUAGE_CODE);
383 end ADD_LANGUAGE;
384
385
386 end AMW_EXCEPTIONS_PKG;