[Home] [Help]
PACKAGE BODY: APPS.AME_API5
Source
1 package body ame_api5 as
2 /* $Header: ameeapi5.pkb 120.4 2007/12/12 12:43:13 prasashe noship $ */
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 := -20001;
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;
95 begin
96 ame_engine.lockTransaction(fndApplicationIdIn => applicationIdIn,
97 transactionIdIn => transactionIdIn,
98 transactionTypeIdIn => transactionTypeIn);
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 := -20001;
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
206 procedure copyInsRec2ToInsRec3
207 (insertionRec2In in ame_util.insertionRecord2
208 ,insertionRec3Out out nocopy ame_util2.insertionRecord3
209 ) as
210 begin
211 insertionRec3Out.item_class := insertionRec2In.item_class;
212 insertionRec3Out.item_id := insertionRec2In.item_id;
213 insertionRec3Out.action_type_id := insertionRec2In.action_type_id;
214 insertionRec3Out.group_or_chain_id := insertionRec2In.group_or_chain_id;
215 insertionRec3Out.order_type := insertionRec2In.order_type;
216 insertionRec3Out.parameter := insertionRec2In.parameter;
217 insertionRec3Out.api_insertion := insertionRec2In.api_insertion;
218 insertionRec3Out.authority := insertionRec2In.authority;
219 insertionRec3Out.description := insertionRec2In.description;
220 end copyInsRec2ToInsRec3;
221
222 begin
223 /* Check if the fnd application and transaction type combination is valid. */
224 select count(*)
225 into tempCount
226 from ame_calling_apps
227 where sysdate between start_date and nvl(end_date - (1/86400),sysdate)
228 and fnd_application_id = applicationIdIn
229 and transaction_type_id = transactionTypeIn;
230 if tempCount = 0 then
231 fnd_message.set_name('PER','AME_400791_INV_FND_APPS_TTY');
232 fnd_message.raise_error;
233 end if;
234 --+
235 /* Invoke the engine and generate the approver list */
236 ame_engine.updateTransactionState
237 (isTestTransactionIn => false
238 ,isLocalTransactionIn => false
239 ,fetchConfigVarsIn => true
240 ,fetchOldApproversIn => true
241 ,fetchInsertionsIn => true
242 ,fetchDeletionsIn => true
243 ,fetchAttributeValuesIn => true
244 ,fetchInactiveAttValuesIn => false
245 ,processProductionActionsIn => false
246 ,processProductionRulesIn => false
247 ,updateCurrentApproverListIn => true
248 ,updateOldApproverListIn => false
249 ,processPrioritiesIn => true
250 ,prepareItemDataIn => false
251 ,prepareRuleIdsIn => false
252 ,prepareRuleDescsIn => false
253 ,prepareApproverTreeIn => true
254 ,transactionIdIn => transactionIdIn
255 ,ameApplicationIdIn => null
256 ,fndApplicationIdIn => applicationIdIn
257 ,transactionTypeIdIn => transactionTypeIn
258 );
259
260 /* Get all approvers from the engine */
261 ame_engine.getApprovers
262 (approversOut => allApprovers);
263
264 /* Get transaction's approval status from the engine */
265 approvalProcessCompleteYNOut := ame_engine.getApprovalProcessCompleteYN;
266
267 nextAvailableInsertionIndex := 0;
268
269 /* Copy approver records from input to output only if approvers are present */
270 if allApprovers.count > 0 then
271 for i in 1 .. allApprovers.count loop
272 if activeApproversYNIn = ame_util.booleanTrue then
273 if allApprovers(i).approval_status is null or
274 allApprovers(i).approval_status in
275 (ame_util.approveAndForwardStatus
276 ,ame_util.approvedStatus
277 ,ame_util.beatByFirstResponderStatus
278 ,ame_util.forwardStatus
279 ,ame_util.noResponseStatus
280 ,ame_util.notifiedStatus
281 ,ame_util.rejectStatus) then
282 ame_util.copyApproverRecord2
283 (approverRecord2In => allApprovers(i)
284 ,approverRecord2Out => approversOut(i)
285 );
286
287 positionInsertions.delete;
288
289 /* Get available insertions at position i from the engine */
290 ame_engine.getInsertions
291 (positionIn => i
292 ,orderTypeIn => null
293 ,coaInsertionsYNIn => coaInsertionsYNIn
294 ,availableInsertionsOut => positionInsertions
295 );
296
297 /* Copy insertion records only if insertions are available at this position */
298 if positionInsertions.count > 0 then
299 for j in 1 .. positionInsertions.count loop
300 nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
301 copyInsRec2ToInsRec3
302 (insertionRec2In => positionInsertions(j)
306 end loop;
303 ,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
304 );
305 availableInsertionsOut(nextAvailableInsertionIndex).position := i;
307 end if;
308
309 end if;
310 else /* if activeApproversYNIn = ame_util.booleanFalse */
311 ame_util.copyApproverRecord2
312 (approverRecord2In => allApprovers(i)
313 ,approverRecord2Out => approversOut(i)
314 );
315
316 positionInsertions.delete;
317
318 /* Get available insertions at position i from the engine */
319 ame_engine.getInsertions
320 (positionIn => i
321 ,orderTypeIn => null
322 ,coaInsertionsYNIn => coaInsertionsYNIn
323 ,availableInsertionsOut => positionInsertions
324 );
325
326 /* Copy insertion records only if insertions are available at this position */
327 if positionInsertions.count > 0 then
328 for j in 1 .. positionInsertions.count loop
329 nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
330 copyInsRec2ToInsRec3
331 (insertionRec2In => positionInsertions(j)
332 ,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
333 );
334 availableInsertionsOut(nextAvailableInsertionIndex).position := i;
335 end loop;
336 end if;
337 end if;
338 end loop;
339 end if;
340
341 /* If all approvers are active or we want all approvers */
342 /* Also takes care when no approvers are present */
343 if allApprovers.count = approversOut.count or
344 activeApproversYNIn = ame_util.booleanFalse then
345
346 positionInsertions.delete;
347
348 /* Get available insertions at position i from the engine */
349 ame_engine.getInsertions
350 (positionIn => allApprovers.count + 1
351 ,orderTypeIn => null
352 ,coaInsertionsYNIn => coaInsertionsYNIn
353 ,availableInsertionsOut => positionInsertions
354 );
355
356 /* Copy insertion records only if insertions are available at this position */
357 if positionInsertions.count > 0 then
358 for j in 1 .. positionInsertions.count loop
359 nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
360 copyInsRec2ToInsRec3
361 (insertionRec2In => positionInsertions(j)
362 ,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
363 );
364 availableInsertionsOut(nextAvailableInsertionIndex).position := allApprovers.count + 1;
365 end loop;
366 end if;
367 else /* If active approvers are required and inactive approvers are present */
368
369 /* Incase no approvers are active lastActiveApproverIndex must be 0 */
370 /* else it will be the last active approver */
371 lastActiveApproverIndex := 0;
372 if approversOut.count > 0 then
373 lastActiveApproverIndex := approversOut.last;
374 end if;
375
376 positionInsertions.delete;
377
378 /* Get available insertions at position lastActiveApproverIndex + 1 from the engine */
379 ame_engine.getInsertions
380 (positionIn => lastActiveApproverIndex + 1
381 ,orderTypeIn => null
382 ,coaInsertionsYNIn => coaInsertionsYNIn
383 ,availableInsertionsOut => positionInsertions
384 );
385
386 /* Copy insertion records only if insertions are available at this position */
387 if positionInsertions.count > 0 then
388 for j in 1 .. positionInsertions.count loop
389 if positionInsertions(j).order_type <> ame_util.beforeApprover then
390 nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
391 copyInsRec2ToInsRec3
392 (insertionRec2In => positionInsertions(j)
393 ,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
394 );
395 availableInsertionsOut(nextAvailableInsertionIndex).position := lastActiveApproverIndex + 1;
396 end if;
397 end loop;
398 end if;
399 end if;
400
401 exception
402 when others then
403 ame_util.runtimeException
404 (packageNameIn => 'ame_api5'
405 ,routineNameIn => 'getAllApproversAndInsertions'
406 ,exceptionNumberIn => sqlcode
407 ,exceptionStringIn => sqlerrm
408 );
409 approvalProcessCompleteYNOut := null;
410 approversOut.delete;
411 availableInsertionsOut.delete;
412 raise;
413 end getAllApproversAndInsertions;
414 end ame_api5;