1 package body ame_approver_deviation_pkg as
2 /* $Header: ameaprdv.pkb 120.11 2008/02/21 12:45:31 prasashe noship $ */
3 /*check if the any approver is deviated*/
4 function IsTransactionDeviated(deviationListIn in deviationReasonList)
5 return boolean as
6 tempIndex number;
7 begin
8 tempIndex := deviationListIn.first;
9 while(tempIndex is not null) loop
10 if deviationListIn(tempIndex).reason is not null and deviationListIn(tempIndex).reason in
11 ( insertReason
12 ,suppressReason
13 ,forwardReason
14 ,timeoutReason
15 ,firstauthReason
16 ,firstauthHandlerInsReason
17 ,forwarHandlerAuthInsReason
18 ,reassignStatus
19 ,forwardForwardeeReason
20 ,forwardEngInsReason
21 ,forwardRemandReason) then
22 return true;
23 end if;
24 tempIndex := deviationListIn.next(tempIndex);
25 end loop;
26 return false;
27 end IsTransactionDeviated;
28 /*mark the transaction as deviated if not done already*/
29 procedure markTransactionDeviation(applicationIdIn in number
30 ,tranasactionId in varchar2
31 ,transactionRequesterIn in varchar2
32 ,transactionDescriptionIn in varchar2) as
33 deviationflag varchar2(1);
34 cursor chkTransDeviation(applId in number,transIdIn in varchar2) is
35 select trans_deviation_flag /*allowed values are Y and N*/
36 from ame_temp_transactions
37 where application_id = applId
38 and transaction_id = transIdIn;
39 begin
40 open chkTransDeviation(applicationIdIn,tranasactionId);
41 fetch chkTransDeviation into deviationflag;
42 close chkTransDeviation;
43 if deviationflag is null then
44 begin
45 update ame_temp_transactions
46 set trans_deviation_flag = 'Y'
47 ,end_date = sysdate
48 ,transaction_requestor = transactionRequesterIn
49 ,transaction_description = transactionDescriptionIn
50 where application_id = applicationIdIn
51 and transaction_id = tranasactionId;
52 exception
53 when others then
54 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
55 routineNameIn => 'markTransactionDeviation',
56 exceptionNumberIn => sqlcode,
57 exceptionStringIn => sqlerrm);
58 end;
59 return;
60 end if;
61 if deviationflag = 'Y' then
62 return;
63 elsif deviationflag = 'N' then
64 begin
65 update ame_temp_transactions
66 set trans_deviation_flag = 'Y'
67 ,end_date = sysdate
68 ,transaction_requestor = transactionRequesterIn
69 ,transaction_description = transactionDescriptionIn
70 where application_id = applicationIdIn
71 and transaction_id = tranasactionId;
72 exception
73 when others then
74 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
75 routineNameIn => 'markTransactionDeviation',
76 exceptionNumberIn => sqlcode,
77 exceptionStringIn => sqlerrm);
78 end;
79 end if;
80 end markTransactionDeviation;
81 /*The following method is used to evaluate the attribute transaction description*/
82 procedure getTransactionDecription( applicationIdIn in number
83 ,transactionIdIn in varchar2
84 ,descriptionOut out nocopy varchar2) as
85 tempStatic varchar2(2);
86 querystring ame_util.longestStringType;
87 dynamicQuery ame_util.longestStringType;
88 dynamicCursor integer;
89 tempAttributeValues1 dbms_sql.varchar2_table;
90 rowsFound integer;
91 cursor getAttributeDetails(applnIdIn in number) is
92 select is_static
93 ,query_string
94 from ame_attribute_usages atu
95 ,ame_attributes attr
96 where attr.name = 'DESCRIPTION_OF_TRANSACTION'
97 and attr.attribute_id = atu.attribute_id
98 and application_id = applnIdIn
99 and sysdate between attr.start_date and nvl(attr.end_date,sysdate)
100 and sysdate between atu.start_date and nvl(atu.end_date,sysdate);
101 begin
102 open getAttributeDetails(applicationIdIn);
103 fetch getAttributeDetails into tempStatic,querystring;
104 close getAttributeDetails;
105 if tempStatic is null or querystring is null then
106 return;
107 end if;
108 if tempStatic = 'Y' then
109 descriptionOut := querystring;
110 return;
111 else
112 dynamicQuery := ame_util.removeReturns(stringIn => querystring,
113 replaceWithSpaces => true);
114 dynamicCursor := dbms_sql.open_cursor;
115 dbms_sql.parse(dynamicCursor,
116 dynamicQuery,
117 dbms_sql.native);
118 if(instrb(dynamicQuery, ame_util.transactionIdPlaceholder, 1, 1) > 0) then
119 dbms_sql.bind_variable(dynamicCursor,
120 ame_util.transactionIdPlaceholder,
121 transactionIdIn,
122 50);
123 end if;
124 dbms_sql.define_array(dynamicCursor,
125 1,
126 tempAttributeValues1,
127 100,
128 1);
129 rowsFound := dbms_sql.execute(dynamicCursor);
130 loop
131 rowsFound := dbms_sql.fetch_rows(dynamicCursor);
132 dbms_sql.column_value(dynamicCursor,
133 1,
134 tempAttributeValues1);
135 exit when rowsFound < 100;
136 end loop;
137 dbms_sql.close_cursor(dynamicCursor);
138 if tempAttributeValues1.count > 1 then
139 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
140 routineNameIn => 'getTransactionDecription',
141 exceptionNumberIn => -20001,
142 exceptionStringIn => 'sql returned incorrect number of rows');
143 descriptionOut := null;
144 return;
145 end if;
146 descriptionOut := tempAttributeValues1(1);
147 return;
148 end if;
149 exception
150 when others then
151 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
152 routineNameIn => 'getTransactionDecription',
153 exceptionNumberIn => sqlcode,
154 exceptionStringIn => sqlerrm);
155 descriptionOut := null;
156 dbms_sql.close_cursor(dynamicCursor);
157 end getTransactionDecription;
158 /*This method insert the approverlist into the table. This method will be called only
159 when approvalprocessis completed. This method does the following
160 1. mark the transaction as deviated if not already done and mark the transaction as
161 completed.
162 2. insert the record if the transaction is not already completed. This to prevent
163 the insertion of the same record in repeatdely. To implement this we need the following
164 check in other place
165 a) clear the record from detailed table and trans_deviation_flag when approver status is cleared
166 b) approver list changes even after the approval process is complete and now the transaction has
167 new set of approver. In this case AME engine is responsible to clear the record from detailed table
168 and the trans_deviation_flag from master table*/
169 procedure insertDeviations( applicationIdIn in number
170 ,tranasactionIdIn in varchar2
171 ,deviationListIn in deviationReasonList
172 ,finalapproverListIn in ame_util.approversTable2) as
173 tempTransSequenceId integer;
174 approverDeviationId integer;
175 tempTransReq integer;
176 tempTransDescr varchar2(100);
177 tempwfNmae varchar2(100);
178 tempDisplayName varchar2(100);
179 tempApproverIndex number;
180 tempReason varchar2(100);
181 tempDate date;
182 cursor isTxnComplete(applnIdIn in number, txnIdIn in varchar2) is
183 select temp_transactions_id
184 from ame_temp_transactions
185 where application_id = applnIdIn
186 and transaction_id = txnIdIn
187 and end_date is not null;
188 cursor gettempTranskey(applnIdIn in number, txnIdIn in varchar2) is
189 select temp_transactions_id
190 from ame_temp_transactions
191 where application_id = applnIdIn
192 and transaction_id = txnIdIn;
193 begin
194 open isTxnComplete(applicationIdIn,tranasactionIdIn);
195 fetch isTxnComplete into tempTransSequenceId;
196 close isTxnComplete;
197 if tempTransSequenceId is not null then
198 return;
199 else
200 open gettempTranskey(applicationIdIn,tranasactionIdIn);
201 fetch gettempTranskey into tempTransSequenceId;
202 close gettempTranskey;
203 end if;
204 if finalapproverListIn.count = 0 then
205 return;
206 end if;
207 begin
208 getTransactionDecription( applicationIdIn => applicationIdIn
209 ,transactionIdIn => tranasactionIdIn
210 ,descriptionOut => tempTransDescr);
211 tempTransReq := ame_engine.getHeaderAttValue2(ame_util.transactionRequestorAttribute);
212 if tempTransReq is not null then
213 tempwfNmae := ame_approver_type_pkg.getWfRolesName(ame_util.perOrigSystem,tempTransReq,'false');
214 if tempwfNmae is not null then
215 tempDisplayName := ame_approver_type_pkg.getApproverDisplayName(tempwfNmae);
216 else
217 tempDisplayName := 'INVALID:'||tempTransReq;
218 end if;
219 end if;
220 exception
221 when others then
222 null;
223 end;
224 if IsTransactionDeviated(deviationListIn) then
225 markTransactionDeviation(applicationIdIn,tranasactionIdIn,tempDisplayName,tempTransDescr);
226 else
227 update ame_temp_transactions
228 set end_date = sysdate
229 ,trans_deviation_flag = 'N'
230 ,transaction_requestor = tempDisplayName
231 ,transaction_description = tempTransDescr
232 where application_id = applicationIdIn
233 and transaction_id = tranasactionIdIn;
234 end if;
235 for approverIndex in 1..finalapproverListIn.count loop
236 tempReason := null;
237 tempDate := null;
238 if deviationListIn.exists(approverIndex) then
239 tempReason := deviationListIn(approverIndex).reason;
240 tempDate := deviationListIn(approverIndex).effectiveDate;
241 end if;
242 begin
243 approverDeviationId := null;
244 select ame_txn_approvers_s.nextval
245 into approverDeviationId from dual;
246 insert into ame_txn_approvers
247 (
248 txn_approvers_id
249 ,temp_transactions_id
250 ,name
251 ,orig_system
252 ,orig_system_id
253 ,display_name
254 ,approver_category
255 ,api_insertion
256 ,authority
257 ,approval_status
258 ,action_type_id
259 ,group_or_chain_id
260 ,occurrence
261 ,source
262 ,item_class
263 ,item_id
264 ,item_class_order_number
265 ,item_order_number
266 ,sub_list_order_number
267 ,action_type_order_number
268 ,group_or_chain_order_number
269 ,member_order_number
270 ,approver_order_number
271 ,effective_date
272 ,reason
273 ,txn_attribute_1
274 ,txn_attribute_2
275 ,txn_attribute_3
276 ,txn_attribute_4
277 ,txn_attribute_5
278 ,txn_attribute_6
279 ,txn_attribute_7
280 ,txn_attribute_8
281 ,txn_attribute_9
282 ,txn_attribute_10
283 )values
284 (
285 approverDeviationId
286 ,tempTransSequenceId
287 ,finalapproverListIn(approverIndex).name
288 ,finalapproverListIn(approverIndex).orig_system
289 ,finalapproverListIn(approverIndex).orig_system_id
290 ,finalapproverListIn(approverIndex).display_name
291 ,finalapproverListIn(approverIndex).approver_category
292 ,finalapproverListIn(approverIndex).api_insertion
293 ,finalapproverListIn(approverIndex).authority
294 ,finalapproverListIn(approverIndex).approval_status
295 ,finalapproverListIn(approverIndex).action_type_id
296 ,finalapproverListIn(approverIndex).group_or_chain_id
297 ,finalapproverListIn(approverIndex).occurrence
298 ,finalapproverListIn(approverIndex).source
299 ,finalapproverListIn(approverIndex).item_class
300 ,finalapproverListIn(approverIndex).item_id
301 ,finalapproverListIn(approverIndex).item_class_order_number
302 ,finalapproverListIn(approverIndex).item_order_number
303 ,finalapproverListIn(approverIndex).sub_list_order_number
304 ,finalapproverListIn(approverIndex).action_type_order_number
305 ,finalapproverListIn(approverIndex).group_or_chain_order_number
306 ,finalapproverListIn(approverIndex).member_order_number
307 ,finalapproverListIn(approverIndex).approver_order_number
308 ,tempDate
309 ,tempReason
310 ,null
311 ,null
312 ,null
313 ,null
314 ,null
315 ,null
316 ,null
317 ,null
318 ,null
319 ,null
320 );
321 exception
322 when others then
323 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
324 routineNameIn => 'insertDeviations',
325 exceptionNumberIn => sqlcode,
326 exceptionStringIn => sqlerrm);
327 end;
328 end loop;
329 end insertDeviations;
330 /*this method is used by the deviation report sql for translation
331 of the deviation reason*/
332 function getreasonDescription(reasonIn in varchar2) return varchar2 as
333 templookupcode varchar2(100);
334 descrOut varchar2(100);
335 begin
336 if reasonIn = insertReason then
337 templookupcode := 'INSERT';
338 elsif reasonIn = suppressReason then
339 templookupcode := 'SUPPRESS';
340 elsif reasonIn = forwardReason then
341 templookupcode := 'FORWARDEE';
342 elsif reasonIn = timeoutReason then
343 templookupcode := 'SURROGATE';
344 elsif reasonIn = firstauthReason then
345 templookupcode := 'FIRSTAUTH';
346 elsif reasonIn = firstauthHandlerInsReason then
347 templookupcode := 'FIRSTAUTHHANDLERINS';
348 elsif reasonIn = forwarHandlerAuthInsReason then
349 templookupcode := 'FORWARDHANDLERAUTHINS';
350 elsif reasonIn = reassignStatus then
351 templookupcode := 'REASSIGN';
352 elsif reasonIn = forwardForwardeeReason then
353 templookupcode := 'FORWARDERREPEAT';
354 elsif reasonIn = forwardEngInsReason then
355 templookupcode := 'FORWARDENGINS';
356 elsif reasonIn = forwardRemandReason then
357 templookupcode := 'FORWARDREMAND';
358 elsif reasonIn is null then
359 templookupcode := null;
360 end if;
361 begin
362 select meaning
363 into descrOut
364 from fnd_lookups
365 where lookup_type = 'AME_DEVIATION_REASON'
366 and lookup_code = templookupcode;
367 exception
368 when others then
369 descrOut := null;
370 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
371 routineNameIn => 'getreasonDescription',
375 return descrOut;
372 exceptionNumberIn => sqlcode,
373 exceptionStringIn => sqlerrm);
374 end;
376 end getreasonDescription;
377 /*This check if the approvla process is not complete but has been already
378 registered as complete. This can happen when trans was completed but due t some change
379 trans start again with new deviated list*/
380 procedure updateDeviationState( applicationIdIn in number
381 ,tranasactionIdIn in varchar2
382 ,deviationListIn in deviationReasonList
383 ,approvalProcessCompleteYNIn in varchar2
384 ,finalapproverListIn in ame_util.approversTable2) as
385 tempTransactionKey integer;
386 recordConfig varchar2(100) := 'recordDeviations';
387 cursor getTxnKey(applnId in number, transIdIn in varchar2) is
388 select temp_transactions_id
389 from ame_temp_transactions
390 where application_id = applnId
391 and transaction_id = transIdIn
392 and end_date is not null;
393 begin
394 begin
395 if ame_engine.getConfigVarValue(recordConfig) = ame_util.no then
396 return;
397 end if;
398 exception
399 when others then
400 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
401 routineNameIn => 'updateDeviationState',
402 exceptionNumberIn => sqlcode,
403 exceptionStringIn => sqlerrm);
404 return;
405 end;
406 if approvalProcessCompleteYNIn not in
407 (ame_util2.completeFullyApproved,
408 ame_util2.completeFullyRejected) then
409 open getTxnKey(applicationIdIn,tranasactionIdIn);
410 fetch getTxnKey into tempTransactionKey;
411 close getTxnKey;
412 if tempTransactionKey is null then
413 return;
414 end if;
415 begin
416 delete from ame_txn_approvers
417 where temp_transactions_id = tempTransactionKey;
418 update ame_temp_transactions
419 set end_Date = null
420 ,trans_deviation_flag = null
421 ,transaction_requestor = null
422 ,transaction_description = null
423 where temp_transactions_id = tempTransactionKey;
424 return;
425 exception
426 when others then
427 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
428 routineNameIn => 'updateDeviationState',
429 exceptionNumberIn => sqlcode,
430 exceptionStringIn => sqlerrm);
431 end;
432 else
433 insertDeviations( applicationIdIn => applicationIdIn
434 ,tranasactionIdIn => tranasactionIdIn
435 ,deviationListIn => deviationListIn
436 ,finalapproverListIn => finalapproverListIn);
437 end if;
438 end updateDeviationState;
439 /*Thie method will be called whenever any clear in transaction state is determined
440 This method will simply removes the approver list from deviation table and
441 sets the transaction state as not completed in ame_temtransaction table*/
442 procedure clearDeviationState( applicationIdIn in number
443 ,transactionIdIn in varchar2 ) as
444 tempTransSeq number;
445 cursor getTransKey(applnId in number, transIdIn in varchar2) is
446 select temp_transactions_id
447 from ame_temp_transactions
448 where application_id = applnId
449 and transaction_id = transIdIn
450 and end_date is not null;
451 begin
452 open getTransKey(applicationIdIn,transactionIdIn);
453 fetch getTransKey into tempTransSeq;
454 close getTransKey;
455 if tempTransSeq is not null then
456 begin
457 delete from ame_txn_approvers
458 where temp_transactions_id = tempTransSeq;
459 update ame_temp_transactions
460 set end_date = null
461 ,transaction_requestor = null
462 ,transaction_description = null
463 ,trans_deviation_flag = null
464 where temp_transactions_id = tempTransSeq;
465 /*This method is added to check if the approval process is completed even after the changes*/
466 ame_engine.updateTransactionState(isTestTransactionIn => false,
467 isLocalTransactionIn => false,
468 fetchConfigVarsIn => true,
469 fetchOldApproversIn => true,
470 fetchInsertionsIn => true,
471 fetchDeletionsIn => true,
472 fetchAttributeValuesIn => true,
473 fetchInactiveAttValuesIn => false,
474 processProductionActionsIn => false,
475 processProductionRulesIn => false,
476 updateCurrentApproverListIn => true,
477 updateOldApproverListIn => true,
478 prepareApproverTreeIn => true,
479 processPrioritiesIn => true,
480 prepareItemDataIn => false,
481 prepareRuleIdsIn => false,
482 prepareRuleDescsIn => false,
483 transactionIdIn => transactionIdIn,
484 ameApplicationIdIn => applicationIdIn,
485 fndApplicationIdIn => null,
486 transactionTypeIdIn => null );
487 exception
488 when others then
489 ame_util.runtimeException(packageNameIn => 'ame_approver_deviation_pkg',
490 routineNameIn => 'clearDeviationState',
491 exceptionNumberIn => sqlcode,
492 exceptionStringIn => sqlerrm);
493 end;
494 end if;
495 end clearDeviationState;
496 function validateDate return boolean is
497 errbuf varchar2(1000);
498 retcode varchar2(100);
499 endDateinFuture exception;
500 startDateinFurture exception;
501 priorStartDateExp exception;
505 if P_ENDDATE < P_STARTDATE then
502 errorMessage ame_util.longStringType;
503 errorCode integer;
504 begin
506 raise priorStartDateExp;
507 end if;
508 if P_STARTDATE > sysdate then
509 raise startDateinFurture;
510 end if;
511 if P_ENDDATE > sysdate then
512 raise endDateinFuture;
513 end if;
514 begin
515 TEMP_APPLID := P_AMEAPPLID;
516 select lookup_code
517 into TEMP_REASON
518 from fnd_lookup_values
519 where lookup_type ='AME_DEVIATION_REASON'
520 and language = userenv('LANG')
521 and meaning = P_REASON;
522
523 exception
524 when others then
525 null;
526 end;
527 return true;
528 exception
529 when endDateinFuture then
530 errorCode := -20001;
531 errorMessage :=
532 ame_util.getMessage(applicationShortNameIn =>'PER',
533 messageNameIn => 'AME_400827_DEV_END_DATE_ERR');
534 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
535 routineNameIn => 'purgeDeviationData',
536 exceptionNumberIn => errorCode,
537 exceptionStringIn => errorMessage);
538 FND_FILE.PUT_LINE (FND_FILE.LOG,errorMessage);
539 raise_application_error(errorCode,
540 errorMessage);
541 when startDateinFurture then
542 errorCode := -20001;
543 errorMessage :=
544 ame_util.getMessage(applicationShortNameIn =>'PER',
545 messageNameIn => 'AME_400826_DEV_START_DATE_ERR');
546 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
547 routineNameIn => 'purgeDeviationData',
548 exceptionNumberIn => errorCode,
549 exceptionStringIn => errorMessage);
550 FND_FILE.PUT_LINE (FND_FILE.LOG,errorMessage);
551 raise_application_error(errorCode,
552 errorMessage);
553 when priorStartDateExp then
554 errorCode := -20001;
555 errorMessage :=
556 ame_util.getMessage(applicationShortNameIn =>'PER',
557 messageNameIn => 'AME_400828_DEV_DATE_MISMATCH');
558 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
559 routineNameIn => 'purgeDeviationData',
560 exceptionNumberIn => errorCode,
561 exceptionStringIn => errorMessage);
562 FND_FILE.PUT_LINE (FND_FILE.LOG,errorMessage);
563 raise_application_error(errorCode,
564 errorMessage);
565 end validateDate;
566 function getApplicationName return varchar2 as
567 tempName varchar2(300);
568 begin
569 select application_name
570 into tempName
571 from fnd_application_vl
572 where application_id = to_number(P_APPLICATION);
573 return tempName;
574 exception
575 when others then
576 return null;
577 end getApplicationName;
578 function gettxntype return varchar2 as
579 tempapplName varchar2(720);
580 begin
581 select application_name
582 into tempapplName
583 from ame_calling_apps_vl
584 where application_id = P_AMEAPPLID
585 and sysdate between start_date and nvl(end_date,sysdate);
586 return tempapplName;
587 exception
588 when others then
589 return null;
590 end gettxntype;
591 function getStartDateParam return varchar2 as
592 begin
593 return fnd_date.date_to_displayDate(P_STARTDATE);
594 exception
595 when others then
596 return to_char(P_STARTDATE);
597 end getStartDateParam;
598
599 function getEndDateParam return varchar2 as
600 begin
601 return fnd_date.date_to_displayDate(P_ENDDATE);
602 exception
603 when others then
604 return to_char(P_ENDDATE);
605 end getEndDateParam;
606 end ame_approver_deviation_pkg;
[Home] [Help]