[Home] [Help]
PACKAGE BODY: APPS.AME_API6
Source
1 package body ame_api6 as
2 /* $Header: ameeapi6.pkb 120.6 2011/11/14 14:54:40 kkananth ship $ */
3 ambiguousApproverException exception;
4 ambiguousApproverMessage constant ame_util.longestStringType :=
5 ame_util.getMessage(applicationShortNameIn =>'PER',
6 messageNameIn => 'AME_400812_NULL_APPR_REC_NAME');
7 procedure getApproverDetails(nameIn in varchar2
8 ,validityOut out NOCOPY varchar2
9 ,displayNameOut out NOCOPY varchar2
10 ,origSystemIdOut out NOCOPY integer
11 ,origSystemOut out NOCOPY varchar2 ) as
12 begin
13 validityOut := 'INVALID';
14 select
15 display_name,
16 orig_system,
17 orig_system_id
18 into
19 displayNameOut,
20 origSystemOut,
21 origSystemIdOut
22 from wf_roles
23 where
24 name = nameIn and
25 status = 'ACTIVE' and
26 (expiration_date is null or
27 sysdate < expiration_date) and
28 rownum < 2;
29 validityOut := 'VALID';
30 exception
31 when no_data_found then
32 begin
33 select
34 display_name,
35 orig_system,
36 orig_system_id
37 into
38 displayNameOut,
39 origSystemOut,
40 origSystemIdOut
41 from wf_local_roles
42 where
43 name = nameIn and
44 rownum < 2;
45 validityOut := 'INACTIVE';
46 exception
47 when no_data_found then
48 displayNameOut := nameIn;
49 origSystemOut := 'PER';
50 end;
51 end getApproverDetails;
52 procedure getApprovers(applicationIdIn in number
53 ,transactionTypeIn in varchar2
54 ,transactionIdIn in varchar2
55 ,approversOut out nocopy ame_util.approversTable2) as
56 ameApplicationId integer;
57 tempIndex integer;
58 l_valid varchar2(50);
59 l_display_name varchar2(200);
60 cursor approversCursor (applicationIdIn in integer
61 ,transactionIdIn in varchar2) is
62 select atah.row_timestamp row_timestamp
63 ,atah.item_class item_class
64 ,atah.item_id item_id
65 ,atah.name name
66 ,atah.order_number order_number
67 ,atah.approver_category category
68 ,atah.user_comments user_comment
69 ,atah.status status
70 ,atah.authority
71 ,atah.occurrence
72 ,atah.action_type_id
73 ,atah.group_or_chain_id
74 ,atah.api_insertion
75 ,atah.member_order_number
76 from ame_trans_approval_history atah
77 where atah.date_cleared is null
78 and atah.transaction_id = transactionIdIn
79 and atah.application_id = applicationIdIn
80 and atah.row_timestamp =
81 (
82 select max(b.row_timestamp)
83 from ame_trans_approval_history b
84 where atah.transaction_id = b.transaction_id
85 and atah.application_id = b.application_id
86 and atah.name = b.name
87 and atah.approver_category = b.approver_category
88 and atah.item_class = b.item_class
89 and atah.item_id = b.item_id
90 and atah.action_type_id = b.action_type_id
91 and atah.authority = b.authority
92 and atah.group_or_chain_id = b.group_or_chain_id
93 and atah.occurrence = b.occurrence
94 and b.date_cleared is null);
95 begin
96 --+
97 -- get the ame application id.
98 --+
99 tempIndex := 0;
100 ameApplicationId :=
101 ame_admin_pkg.getApplicationId
102 (fndAppIdIn => applicationIdIn
103 ,transactionTypeIdIn => transactionTypeIn);
104 for approver in approversCursor(applicationIdIn => ameApplicationId
105 ,transactionIdIn => transactionIdIn) loop
106 tempIndex := tempIndex + 1;
107 approversOut(tempIndex).name := approver.name;
108 approversOut(tempIndex).display_name := ame_approver_type_pkg.getApproverDisplayName4(nameIn => approver.name);
109 approversOut(tempIndex).item_class := approver.item_class;
110 approversOut(tempIndex).item_id := approver.item_id;
111 approversOut(tempIndex).approver_category := approver.category;
112 approversOut(tempIndex).authority := approver.authority;
113 approversOut(tempIndex).approval_status := approver.status;
114 approversOut(tempIndex).action_type_id := approver.action_type_id;
115 approversOut(tempIndex).group_or_chain_id := approver.group_or_chain_id;
116 approversOut(tempIndex).occurrence := approver.occurrence;
117 approversOut(tempIndex).approver_order_number := approver.order_number;
118 approversOut(tempIndex).api_insertion := approver.api_insertion;
119 approversOut(tempIndex).member_order_number := approver.member_order_number;
120 begin
121 ame_approver_type_pkg.getApproverOrigSystemAndId
122 (nameIn => approver.name
123 ,origSystemOut => approversOut(tempIndex).orig_system
124 ,origSystemIdOut => approversOut(tempIndex).orig_system_id);
125 /*
126 The old approver list does not maintain source. Calling applications requiring
127 source data must get it by calling getNextApprover or getAllApprovers.
128 */
129 exception
130 when others then
131 getApproverDetails(nameIn => approver.name
132 ,validityOut => l_valid
133 ,displayNameOut => l_display_name
134 ,origSystemIdOut => approversOut(tempIndex).orig_system_id
135 ,origSystemOut => approversOut(tempIndex).orig_system);
136 if l_valid = 'INVALID' then
137 approversOut(tempIndex).orig_system_id := null;
138 approversOut(tempIndex).orig_system := null;
139 end if;
140 end;
141 approversOut(tempIndex).source := null;
142 end loop;
143 exception
144 when others then
145 ame_util.runtimeException(packageNameIn => 'ame_api6',
146 routineNameIn => 'getApprovers',
147 exceptionNumberIn => sqlcode,
148 exceptionStringIn => sqlerrm);
149 approversOut.delete;
150 raise;
151 end getApprovers;
152 procedure updateApprovalStatus(applicationIdIn in number,
153 transactionTypeIn in varchar2,
154 transactionIdIn in varchar2,
155 approverIn in ame_util.approverRecord2,
156 notificationIn in ame_util2.notificationRecord
157 default ame_util2.emptyNotificationRecord,
158 forwardeeIn in ame_util.approverRecord2 default
159 ame_util.emptyApproverRecord2,
160 updateItemIn in boolean default false) as
161 errorCode integer;
162 errorMessage ame_util.longStringType;
163 begin
164 /* Validate the input approver. */
165 if(approverIn.name is null) then
166 raise ambiguousApproverException;
167 end if;
168 ame_engine.updateApprovalStatus(applicationIdIn => applicationIdIn,
169 transactionTypeIn => transactionTypeIn,
170 transactionIdIn => transactionIdIn,
171 approverIn => approverIn,
172 notificationIn => notificationIn,
173 forwardeeIn => forwardeeIn,
174 updateItemIn => updateItemIn);
175 exception
176 when ambiguousApproverException then
177 errorCode := -20310;
178 errorMessage := ambiguousApproverMessage;
179 ame_util.runtimeException(packageNameIn => 'ame_api2',
180 routineNameIn => 'updateApprovalStatus',
181 exceptionNumberIn => errorCode,
182 exceptionStringIn => errorMessage);
183 raise_application_error(errorCode,
184 errorMessage);
185 when others then
186 ame_util.runtimeException(packageNameIn => 'ame_api2',
187 routineNameIn => 'updateApprovalStatus',
188 exceptionNumberIn => sqlcode,
189 exceptionStringIn => sqlerrm);
190 raise;
191 end updateApprovalStatus;
192 procedure updateApprovalStatus2(applicationIdIn in number,
193 transactionTypeIn in varchar2,
194 transactionIdIn in varchar2,
195 approvalStatusIn in varchar2,
196 approverNameIn in varchar2,
197 itemClassIn in varchar2 default null,
198 itemIdIn in varchar2 default null,
199 actionTypeIdIn in number default null,
200 groupOrChainIdIn in number default null,
201 occurrenceIn in number default null,
202 notificationIn in ame_util2.notificationRecord
203 default ame_util2.emptyNotificationRecord,
204 forwardeeIn in ame_util.approverRecord2
205 default ame_util.emptyApproverRecord2,
206 updateItemIn in boolean default false) as
207 approver ame_util.approverRecord2;
208 errorCode integer;
209 errorMessage ame_util.longStringType;
210 nullApproverException exception;
211 l_error_code number;
212 begin
213 /* No locking needed here as it is done in updateApprovalStatus */
214 if approverNameIn is not null then
215 approver.name := approverNameIn;
216 else
217 raise nullApproverException;
218 end if;
219 approver.item_class := itemClassIn ;
220 approver.item_id := itemIdIn ;
221 approver.approval_status := approvalStatusIn;
222 approver.action_type_id :=actionTypeIdIn ;
223 approver.group_or_chain_id := groupOrChainIdIn;
224 approver.occurrence := occurrenceIn;
225 begin
226 ame_approver_type_pkg.getOrigSystemIdAndDisplayName(nameIn =>approver.name,
227 origSystemOut => approver.orig_system,
228 origSystemIdOut => approver.orig_system_id,
229 displayNameOut => approver.display_name);
230 exception
231 when others then
232 l_error_code := sqlcode;
233 if l_error_code = -20213 then
234 errorCode := -20224;
235 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
236 messageNameIn => 'AME_400837_INV_APR_FOUND',
237 tokenNameOneIn => 'PROCESS_NAME',
238 tokenValueOneIn => 'ame_api6.updateApprovalStatus2',
239 tokenNameTwoIn => 'NAME',
240 tokenValueTwoIn => approver.name);
241 raise_application_error(errorCode,errorMessage);
242 end if;
243 raise;
244 end;
245 ame_engine.updateApprovalStatus(applicationIdIn => applicationIdIn,
246 transactionIdIn => transactionIdIn,
247 approverIn => approver,
248 transactionTypeIn => transactionTypeIn,
249 notificationIn => notificationIn,
250 forwardeeIn => forwardeeIn,
251 updateItemIn => updateItemIn);
252 exception
253 when nullApproverException then
254 errorCode := -20309;
255 errorMessage := ambiguousApproverMessage;
256 ame_util.runtimeException(packageNameIn => 'ame_api2',
257 routineNameIn => 'updateApprovalStatus2',
258 exceptionNumberIn => errorCode,
259 exceptionStringIn => errorMessage);
260 raise_application_error(errorCode,
261 errorMessage);
262 when others then
263 ame_util.runtimeException(packageNameIn => 'ame_api2',
264 routineNameIn => 'updateApprovalStatus2',
265 exceptionNumberIn => sqlcode,
266 exceptionStringIn => sqlerrm);
267 raise;
268 end updateApprovalStatus2;
269
270 procedure getApprovers2(applicationIdIn in number
271 ,transactionTypeIn in varchar2
272 ,transactionIdIn in varchar2
273 ,approversOut out nocopy ame_util.approversTable2) as
274 ameApplicationId integer;
275 tempIndex integer;
276 l_valid varchar2(50);
277 l_display_name varchar2(200);
278 cursor approversCursor is
279 SELECT ol.name
280 ,ame_approver_type_pkg.getapproverdisplayname4 (ol.name) display_name
281 ,ol.approval_status
282 ,ol.approver_category
283 ,ol.authority
284 ,ol.action_type_id
285 ,ol.group_or_chain_id
286 ,ol.item_class
287 ,ol.item_id
288 FROM ame_temp_old_approver_lists ol
289 ,ame_trans_approval_history h
290 ,fnd_lookups lookup
291 ,fnd_lookups lookup2
292 ,fnd_lookups lookup3
293 ,ame_approval_groups_vl apg
294 WHERE ol.transaction_id = h.transaction_id
295 AND ol.application_id = h.application_id
296 AND ol.name = h.name
297 AND ol.item_class = h.item_class
298 AND ol.item_id = h.item_id
299 AND ol.api_insertion = h.api_insertion
300 AND ol.action_type_id = h.action_type_id
301 AND ol.authority = h.authority
302 AND ol.occurrence = h.occurrence
303 AND lookup.lookup_type (+) = 'AME_APPROVAL_STATUS'
304 AND lookup.lookup_code (+) = h.status
305 AND apg.approval_group_id (+) = ol.group_or_chain_id
306 AND (
307 h.status IS NULL
308 OR h.status = 'APPROVE AND FORWARD'
309 OR h.status = 'APPROVE'
310 OR h.status = 'BEAT BY FIRST RESPONDER'
311 OR h.status = 'FORWARD'
312 OR h.status = 'NO RESPONSE'
313 OR h.status = 'NOTIFIED'
314 OR h.status = 'REJECT'
315 )
316 AND (
317 ol.approval_status IS NULL
318 OR ol.approval_status = 'APPROVE AND FORWARD'
319 OR ol.approval_status = 'APPROVE'
320 OR ol.approval_status = 'BEAT BY FIRST RESPONDER'
321 OR ol.approval_status = 'FORWARD'
322 OR ol.approval_status = 'NO RESPONSE'
323 OR ol.approval_status = 'NOTIFIED'
324 OR ol.approval_status = 'REJECT'
325 )
326 AND h.date_cleared IS NULL
327 AND lookup2.lookup_type = 'AME_APPROVER_CATEGORY'
328 AND ol.approver_category = lookup2.lookup_code
329 AND h.transaction_id = transactionIdIn
330 AND h.application_id =
331 (
332 SELECT application_id
333 FROM ame_calling_apps
334 WHERE SYSDATE BETWEEN start_date
335 AND nvl (end_date
336 ,SYSDATE)
337 AND fnd_application_id = applicationIdIn
338 AND transaction_type_id = transactionTypeIn
339 )
340 AND lookup3.lookup_type (+) = 'FND_WF_ORIG_SYSTEMS'
341 AND lookup3.lookup_code (+) = ame_approver_type_pkg.getapproverorigsystem3 (ol.name)
342 AND h.row_timestamp =
343 (
344 SELECT max (b.row_timestamp)
345 FROM ame_trans_approval_history b
346 WHERE h.transaction_id = b.transaction_id
347 AND h.application_id = b.application_id
348 AND h.name = b.name
349 AND h.approver_category = b.approver_category
350 AND h.item_class = b.item_class
351 AND h.item_id = b.item_id
352 AND h.action_type_id = b.action_type_id
353 AND h.authority = b.authority
354 AND h.group_or_chain_id = b.group_or_chain_id
355 AND h.occurrence = b.occurrence
356 AND b.date_cleared IS NULL
357 )
358 UNION
359 SELECT ol.name
360 ,ame_approver_type_pkg.getapproverdisplayname4 (ol.name) display_name
361 ,ol.approval_status
362 ,ol.approver_category
363 ,ol.authority
364 ,ol.action_type_id
365 ,ol.group_or_chain_id
366 ,ol.item_class
367 ,ol.item_id
368 FROM ame_temp_old_approver_lists ol
369 ,fnd_lookups lookup
370 ,fnd_lookups lookup2
371 ,fnd_lookups lookup3
372 ,ame_approval_groups_vl apg
373 WHERE NOT EXISTS
374 (
375 SELECT x.transaction_id
376 ,x.application_id
377 FROM ame_trans_approval_history x
378 WHERE ol.transaction_id = x.transaction_id
379 AND ol.name = x.name
380 AND ol.application_id = x.application_id
381 AND ol.item_class = x.item_class
382 AND ol.item_id = x.item_id
383 AND ol.api_insertion = x.api_insertion
384 AND ol.action_type_id = x.action_type_id
385 AND ol.authority = x.authority
386 AND ol.occurrence = x.occurrence
387 )
388 AND ol.transaction_id = transactionIdIn
389 AND ol.application_id =
390 (
391 SELECT application_id
392 FROM ame_calling_apps
393 WHERE SYSDATE BETWEEN start_date
394 AND nvl (end_date
395 ,SYSDATE)
396 AND fnd_application_id = applicationIdIn
397 AND transaction_type_id = transactionTypeIn
398 )
399 AND lookup.lookup_type (+) = 'AME_APPROVAL_STATUS'
400 AND lookup.lookup_code (+) = ol.approval_status
401 AND lookup2.lookup_type = 'AME_APPROVER_CATEGORY'
402 AND ol.approver_category = lookup2.lookup_code
403 AND apg.approval_group_id (+) = ol.group_or_chain_id
404 AND lookup3.lookup_type (+) = 'FND_WF_ORIG_SYSTEMS'
405 AND lookup3.lookup_code (+) = ame_approver_type_pkg.getapproverorigsystem3 (ol.name)
406 AND (
407 ol.approval_status IS NULL
408 OR ol.approval_status = 'APPROVE AND FORWARD'
409 OR ol.approval_status = 'APPROVE'
410 OR ol.approval_status = 'BEAT BY FIRST RESPONDER'
411 OR ol.approval_status = 'FORWARD'
412 OR ol.approval_status = 'NO RESPONSE'
413 OR ol.approval_status = 'NOTIFIED'
414 OR ol.approval_status = 'REJECT'
415 );
416
417 begin
418 --+
419 -- get the ame application id.
420 --+
421 tempIndex := 0;
422 ameApplicationId :=
423 ame_admin_pkg.getApplicationId
424 (fndAppIdIn => applicationIdIn
425 ,transactionTypeIdIn => transactionTypeIn);
426
427 for approver in approversCursor loop
428 tempIndex := tempIndex + 1;
429 approversOut(tempIndex).name := approver.name;
430 approversOut(tempIndex).display_name := approver.display_name;
431 approversOut(tempIndex).item_class := approver.item_class;
432 approversOut(tempIndex).item_id := approver.item_id;
433 approversOut(tempIndex).approver_category := approver.approver_category;
434 approversOut(tempIndex).authority := approver.authority;
435 approversOut(tempIndex).approval_status := approver.approval_status;
436 approversOut(tempIndex).action_type_id := approver.action_type_id;
437 approversOut(tempIndex).group_or_chain_id := approver.group_or_chain_id;
438
439 begin
440 ame_approver_type_pkg.getApproverOrigSystemAndId
441 (nameIn => approver.name
442 ,origSystemOut => approversOut(tempIndex).orig_system
443 ,origSystemIdOut => approversOut(tempIndex).orig_system_id);
444 /*
445 The old approver list does not maintain source. Calling applications requiring
446 source data must get it by calling getNextApprover or getAllApprovers.
447 */
448 exception
449 when others then
450 getApproverDetails(nameIn => approver.name
451 ,validityOut => l_valid
452 ,displayNameOut => l_display_name
453 ,origSystemIdOut => approversOut(tempIndex).orig_system_id
454 ,origSystemOut => approversOut(tempIndex).orig_system);
455 if l_valid = 'INVALID' then
456 approversOut(tempIndex).orig_system_id := null;
457 approversOut(tempIndex).orig_system := null;
458 end if;
459 end;
460 approversOut(tempIndex).source := null;
461 end loop;
462 exception
463 when others then
464 ame_util.runtimeException(packageNameIn => 'ame_api6',
465 routineNameIn => 'getApprovers2',
466 exceptionNumberIn => sqlcode,
467 exceptionStringIn => sqlerrm);
468 approversOut.delete;
469 raise;
470 end getApprovers2;
471
472 end ame_api6;