[Home] [Help]
PACKAGE BODY: APPS.AME_API5
Source
1 package body ame_api5 as
2 /* $Header: ameeapi5.pkb 120.5 2010/08/18 12:59:16 prasashe ship $ */
3 /* procedures */
4 procedure clearItemClassApprovals1(applicationIdIn in number,
5 transactionTypeIn in varchar2,
6 transactionIdIn in varchar2,
7 itemClassIdIn in number,
8 itemIdIn in varchar2 default null)as
9 ameAppId integer;
10 invalidItemClassException exception;
11 cursor chkItemClass is
12 select name
13 from ame_item_classes
14 where item_class_id = itemClassIdIn
15 and sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate);
16 item_class_name ame_item_classes.name%type;
17 errorCode integer;
18 errorMessage ame_util.longestStringType;
19 begin
20 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
21 transactionIdIn => transactionIdIn,
22 transactionTypeIdIn => transactionTypeIn);
23 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
24 transactionTypeIdIn => transactionTypeIn);
25 -- check that the item class being passed in is valid
26 open chkItemClass;
27 fetch chkItemClass into item_class_name;
28 if chkItemClass%notFound then
29 raise invalidItemClassException;
30 end if;
31 -- update the approval status to null
32 update ame_temp_old_approver_lists
33 set approval_status = null
34 where
35 application_id = ameAppId and
36 transaction_id = transactionIdIn and
37 item_class = item_class_name and
38 (itemIdIn is null or
39 item_id = itemIdIn);
40 if sql%found then
41 ame_approver_deviation_pkg.clearDeviationState(
42 applicationIdIn => ameAppId
43 ,transactionIdIn => transactionIdIn );
44 end if;
45 /* update all existing history rows from the Approval Notification History table
46 to indicate the rows were cleared */
47 update AME_TRANS_APPROVAL_HISTORY set
48 date_cleared = sysdate
49 where
50 application_id = ameAppId and
51 transaction_id = transactionIdIn and
52 item_class = item_class_name and
53 (itemIdIn is null or
54 item_id = itemIdIn) and
55 date_cleared is null;
56 exception
57 when invalidItemClassException then
58 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
59 transactionIdIn => transactionIdIn,
60 transactionTypeIdIn => transactionTypeIn);
61 errorCode := -20323;
62 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
63 messageNameIn => 'AME_99999_INVALID_ITEM_CLASS');
64 ame_util.runtimeException(packageNameIn => 'ame_api3',
65 routineNameIn => 'getAvailableInsertions',
66 exceptionNumberIn => errorCode,
67 exceptionStringIn => errorMessage);
68 raise_application_error(errorCode,
69 errorMessage);
70 when others then
71 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
72 transactionIdIn => transactionIdIn,
73 transactionTypeIdIn => transactionTypeIn);
74 ame_util.runtimeException(packageNameIn => 'ame_api2',
75 routineNameIn => 'clearAllApprovals',
76 exceptionNumberIn => sqlcode,
77 exceptionStringIn => sqlerrm);
78 raise;
79 end clearItemClassApprovals1;
80 procedure clearItemClassApprovals2(applicationIdIn in number,
81 transactionTypeIn in varchar2,
82 transactionIdIn in varchar2,
83 itemClassNameIn in varchar2,
84 itemIdIn in varchar2 default null)as
85 ameAppId integer;
86 invalidItemClassException exception;
87 cursor chkItemClass is
88 select item_class_id
89 from ame_item_classes
90 where name = itemClassNameIn
91 and sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate);
92 item_class_id ame_item_classes.item_class_id%type;
93 errorCode integer;
94 errorMessage ame_util.longestStringType;
98 transactionTypeIdIn => transactionTypeIn);
95 begin
96 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
97 transactionIdIn => transactionIdIn,
99 ameAppId := ame_admin_pkg.getApplicationId(fndAppIdIn => applicationIdIn,
100 transactionTypeIdIn => transactionTypeIn);
101 -- check that the item class being passed in is valid
102 open chkItemClass;
103 fetch chkItemClass into item_class_id;
104 if chkItemClass%notFound then
105 raise invalidItemClassException;
106 end if;
107 -- update the approval status to null
108 update ame_temp_old_approver_lists
109 set approval_status = null
110 where
111 application_id = ameAppId and
112 transaction_id = transactionIdIn and
113 item_class = itemClassNameIn and
114 (itemIdIn is null or
115 item_id = itemIdIn);
116 if sql%found then
117 ame_approver_deviation_pkg.clearDeviationState(
118 applicationIdIn => ameAppId
119 ,transactionIdIn => transactionIdIn );
120 end if;
121 /* update all existing history rows from the Approval Notification History table
122 to indicate the rows were cleared */
123 update AME_TRANS_APPROVAL_HISTORY set
124 date_cleared = sysdate
125 where
126 application_id = ameAppId and
127 transaction_id = transactionIdIn and
128 item_class = itemClassNameIn and
129 (itemIdIn is null or
130 item_id = itemIdIn) and
131 date_cleared is null;
132 exception
133 when invalidItemClassException then
134 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
135 transactionIdIn => transactionIdIn,
136 transactionTypeIdIn => transactionTypeIn);
137 errorCode := -20323;
138 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
139 messageNameIn => 'AME_99999_INVALID_ITEM_CLASS');
140 ame_util.runtimeException(packageNameIn => 'ame_api3',
141 routineNameIn => 'getAvailableInsertions',
142 exceptionNumberIn => errorCode,
143 exceptionStringIn => errorMessage);
144 raise_application_error(errorCode,
145 errorMessage);
146 when others then
147 ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
148 transactionIdIn => transactionIdIn,
149 transactionTypeIdIn => transactionTypeIn);
150 ame_util.runtimeException(packageNameIn => 'ame_api2',
151 routineNameIn => 'clearAllApprovals',
152 exceptionNumberIn => sqlcode,
153 exceptionStringIn => sqlerrm);
154 raise;
155 end clearItemClassApprovals2;
156 procedure getApprovalGroupName(groupIdIn in number
157 ,groupNameOut out nocopy ame_util.stringType) as
158 errorCode integer;
159 errorMessage ame_util.longestStringType;
160 begin
161 select name
162 into groupNameOut
163 from ame_approval_groups
164 where approval_group_id = groupIdIn
165 and sysdate between start_date
166 and nvl(end_date - ame_util.oneSecond, sysdate);
167 exception
168 when no_data_found then
169 errorCode := -20001;
170 errorMessage := ame_util.getMessage(
171 applicationShortNameIn => 'PER',
172 messageNameIn => 'AME_400453_GROUP_NOT_DEFINED',
173 tokenNameOneIn => 'APPROVAL_GROUP',
174 tokenValueOneIn => groupIdIn);
175 ame_util.runtimeException(packageNameIn => 'ame_api3',
176 routineNameIn => 'getApprovalGroupId',
177 exceptionNumberIn => errorCode,
178 exceptionStringIn => errorMessage);
179 raise_application_error(errorCode,
180 errorMessage);
181 when others then
182 ame_util.runtimeException(packageNameIn => 'ame_api3',
183 routineNameIn => 'getApprovalGroupId',
184 exceptionNumberIn => sqlcode,
185 exceptionStringIn => sqlerrm);
186 raise;
187 end getApprovalGroupName;
188
189 procedure getAllApproversAndInsertions
190 (applicationIdIn in number
191 ,transactionTypeIn in varchar2
192 ,transactionIdIn in varchar2
193 ,activeApproversYNIn in varchar2 default ame_util.booleanFalse
194 ,coaInsertionsYNIn in varchar2 default ame_util.booleanFalse
195 ,approvalProcessCompleteYNOut out nocopy varchar2
196 ,approversOut out nocopy ame_util.approversTable2
197 ,availableInsertionsOut out nocopy ame_util2.insertionsTable3
198 ) as
199
200 allApprovers ame_util.approversTable2;
201 positionInsertions ame_util.insertionsTable2;
202 nextAvailableInsertionIndex integer;
203 lastActiveApproverIndex integer;
204 tempCount integer;
205 errorCode integer;
206 errorMessage ame_util.longestStringType;
207 procedure copyInsRec2ToInsRec3
211 begin
208 (insertionRec2In in ame_util.insertionRecord2
209 ,insertionRec3Out out nocopy ame_util2.insertionRecord3
210 ) as
212 insertionRec3Out.item_class := insertionRec2In.item_class;
213 insertionRec3Out.item_id := insertionRec2In.item_id;
214 insertionRec3Out.action_type_id := insertionRec2In.action_type_id;
215 insertionRec3Out.group_or_chain_id := insertionRec2In.group_or_chain_id;
216 insertionRec3Out.order_type := insertionRec2In.order_type;
217 insertionRec3Out.parameter := insertionRec2In.parameter;
218 insertionRec3Out.api_insertion := insertionRec2In.api_insertion;
219 insertionRec3Out.authority := insertionRec2In.authority;
220 insertionRec3Out.description := insertionRec2In.description;
221 end copyInsRec2ToInsRec3;
222
223 begin
224 /* Check if the fnd application and transaction type combination is valid. */
225 select count(*)
226 into tempCount
227 from ame_calling_apps
228 where sysdate between start_date and nvl(end_date - (1/86400),sysdate)
229 and fnd_application_id = applicationIdIn
230 and transaction_type_id = transactionTypeIn;
231 if tempCount = 0 then
232 errorCode := -20324;
233 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
234 messageNameIn => 'AME_400800_INVALID_ITEM_ID');
235 raise_application_error(errorCode,
236 errorMessage);
237 end if;
238 --+
239 /* Invoke the engine and generate the approver list */
240 ame_engine.updateTransactionState
241 (isTestTransactionIn => false
242 ,isLocalTransactionIn => false
243 ,fetchConfigVarsIn => true
244 ,fetchOldApproversIn => true
245 ,fetchInsertionsIn => true
246 ,fetchDeletionsIn => true
247 ,fetchAttributeValuesIn => true
248 ,fetchInactiveAttValuesIn => false
249 ,processProductionActionsIn => false
250 ,processProductionRulesIn => false
251 ,updateCurrentApproverListIn => true
252 ,updateOldApproverListIn => false
253 ,processPrioritiesIn => true
254 ,prepareItemDataIn => false
255 ,prepareRuleIdsIn => false
256 ,prepareRuleDescsIn => false
257 ,prepareApproverTreeIn => true
258 ,transactionIdIn => transactionIdIn
259 ,ameApplicationIdIn => null
260 ,fndApplicationIdIn => applicationIdIn
261 ,transactionTypeIdIn => transactionTypeIn
262 );
263
264 /* Get all approvers from the engine */
265 ame_engine.getApprovers
266 (approversOut => allApprovers);
267
268 /* Get transaction's approval status from the engine */
269 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
270
271 nextAvailableInsertionIndex := 0;
272
273 /* Copy approver records from input to output only if approvers are present */
274 if allApprovers.count > 0 then
275 for i in 1 .. allApprovers.count loop
276 if activeApproversYNIn = ame_util.booleanTrue then
277 if allApprovers(i).approval_status is null or
278 allApprovers(i).approval_status in
279 (ame_util.approveAndForwardStatus
280 ,ame_util.approvedStatus
281 ,ame_util.beatByFirstResponderStatus
282 ,ame_util.forwardStatus
283 ,ame_util.noResponseStatus
284 ,ame_util.notifiedStatus
285 ,ame_util.rejectStatus) then
286 ame_util.copyApproverRecord2
287 (approverRecord2In => allApprovers(i)
288 ,approverRecord2Out => approversOut(i)
289 );
290
291 positionInsertions.delete;
292
293 /* Get available insertions at position i from the engine */
294 ame_engine.getInsertions
295 (positionIn => i
296 ,orderTypeIn => null
297 ,coaInsertionsYNIn => coaInsertionsYNIn
298 ,availableInsertionsOut => positionInsertions
299 );
300
301 /* Copy insertion records only if insertions are available at this position */
302 if positionInsertions.count > 0 then
303 for j in 1 .. positionInsertions.count loop
304 nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
305 copyInsRec2ToInsRec3
306 (insertionRec2In => positionInsertions(j)
307 ,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
308 );
309 availableInsertionsOut(nextAvailableInsertionIndex).position := i;
310 end loop;
311 end if;
312
313 end if;
314 else /* if activeApproversYNIn = ame_util.booleanFalse */
315 ame_util.copyApproverRecord2
316 (approverRecord2In => allApprovers(i)
317 ,approverRecord2Out => approversOut(i)
318 );
319
320 positionInsertions.delete;
321
322 /* Get available insertions at position i from the engine */
323 ame_engine.getInsertions
324 (positionIn => i
325 ,orderTypeIn => null
326 ,coaInsertionsYNIn => coaInsertionsYNIn
327 ,availableInsertionsOut => positionInsertions
328 );
329
330 /* Copy insertion records only if insertions are available at this position */
334 copyInsRec2ToInsRec3
331 if positionInsertions.count > 0 then
332 for j in 1 .. positionInsertions.count loop
333 nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
335 (insertionRec2In => positionInsertions(j)
336 ,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
337 );
338 availableInsertionsOut(nextAvailableInsertionIndex).position := i;
339 end loop;
340 end if;
341 end if;
342 end loop;
343 end if;
344
345 /* If all approvers are active or we want all approvers */
346 /* Also takes care when no approvers are present */
347 if allApprovers.count = approversOut.count or
348 activeApproversYNIn = ame_util.booleanFalse then
349
350 positionInsertions.delete;
351
352 /* Get available insertions at position i from the engine */
353 ame_engine.getInsertions
354 (positionIn => allApprovers.count + 1
355 ,orderTypeIn => null
356 ,coaInsertionsYNIn => coaInsertionsYNIn
357 ,availableInsertionsOut => positionInsertions
358 );
359
360 /* Copy insertion records only if insertions are available at this position */
361 if positionInsertions.count > 0 then
362 for j in 1 .. positionInsertions.count loop
363 nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
364 copyInsRec2ToInsRec3
365 (insertionRec2In => positionInsertions(j)
366 ,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
367 );
368 availableInsertionsOut(nextAvailableInsertionIndex).position := allApprovers.count + 1;
369 end loop;
370 end if;
371 else /* If active approvers are required and inactive approvers are present */
372
373 /* Incase no approvers are active lastActiveApproverIndex must be 0 */
374 /* else it will be the last active approver */
375 lastActiveApproverIndex := 0;
376 if approversOut.count > 0 then
377 lastActiveApproverIndex := approversOut.last;
378 end if;
379
380 positionInsertions.delete;
381
382 /* Get available insertions at position lastActiveApproverIndex + 1 from the engine */
383 ame_engine.getInsertions
384 (positionIn => lastActiveApproverIndex + 1
385 ,orderTypeIn => null
386 ,coaInsertionsYNIn => coaInsertionsYNIn
387 ,availableInsertionsOut => positionInsertions
388 );
389
390 /* Copy insertion records only if insertions are available at this position */
391 if positionInsertions.count > 0 then
392 for j in 1 .. positionInsertions.count loop
393 if positionInsertions(j).order_type <> ame_util.beforeApprover then
394 nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
395 copyInsRec2ToInsRec3
396 (insertionRec2In => positionInsertions(j)
397 ,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
398 );
399 availableInsertionsOut(nextAvailableInsertionIndex).position := lastActiveApproverIndex + 1;
400 end if;
401 end loop;
402 end if;
403 end if;
404
405 exception
406 when others then
407 ame_util.runtimeException
408 (packageNameIn => 'ame_api5'
409 ,routineNameIn => 'getAllApproversAndInsertions'
410 ,exceptionNumberIn => sqlcode
411 ,exceptionStringIn => sqlerrm
412 );
413 approvalProcessCompleteYNOut := null;
414 approversOut.delete;
415 availableInsertionsOut.delete;
416 raise;
417 end getAllApproversAndInsertions;
418 end ame_api5;