1 package body ame_trans_data_purge as
2 /* $Header: amepurge.pkb 120.10 2008/04/16 13:50:38 prasashe noship $ */
3 procedure purgeTransData(errbuf out nocopy varchar2, --needed by concurrent manager.
4 retcode out nocopy number, --needed by concurrent manager.
5 applicationIdIn in number,
6 purgeTypeIn in varchar2) as
7 /* IN argument purgeTypeIn has the following allowed values
8 Y - Purge all complete or incomplete transactions
9 A - Purge all completed transactions which have been Approved
10 P - Purge all Partially approved /rejected transactions and all completely approved transactions too.
11 */
12 lastDateToSave date;
13 cursor attributeUsageCursor (applicationIdIn integer) is
14 select attribute_id
15 from ame_attribute_usages
16 where
17 application_id = applicationIdIn and
18 sysdate between start_date and
19 nvl(end_date - ame_util.oneSecond, sysdate);
20 cursor tempTransaction is
21 select transaction_id
22 from ame_temp_transactions
23 where application_id = applicationIdIn and
24 row_timestamp < lastDateToSave;
25 cursor oldApproverCursor(applicationIdIn in integer,
26 transactionIdIn in varchar2) is
27 select
28 name,
29 item_class,
30 item_id,
31 approver_category,
32 api_insertion,
33 authority,
34 approval_status,
35 action_type_id,
36 group_or_chain_id,
37 occurrence
38 from ame_temp_old_approver_lists
39 where
40 application_id = applicationIdIn and
41 transaction_id = transactionIdIn
42 order by order_number;
43 tempIndex integer;
44 attributeId integer;
45 transactionId ame_temp_transactions.transaction_id%type;
46 transactionIds ame_util.stringList;
47 bulkFetchRowLimit number := 1000;
48 approverNames ame_util.longStringList;
49 approverItemClasses ame_util.stringList;
50 approverItemIds ame_util.stringList;
51 approverCategories ame_util.charList;
52 approverApiInsertions ame_util.StringList;
53 approverAuthorities ame_util.StringList;
54 approverStatuses ame_util.StringList;
55 approverActionTypeIds ame_util.idList;
56 approverGroupOrChainIds ame_util.idList;
57 approverOccurrences ame_util.idList;
58 applicationName ame_calling_apps.application_name%type;
59 begin
60 /* Get last Date to save based on the purge frquency set in the config variables */
61 lastDateToSave := sysdate - to_number(ame_util.getConfigVar(variableNameIn => 'purgeFrequency',
62 applicationIdIn => applicationIdIn));
63 begin
64 select application_name
65 into applicationName
66 from ame_calling_apps
67 where application_id = applicationIdIn
68 and sysdate between start_date and nvl(end_date - 1/86400,sysdate);
69 exception
70 when no_data_found then
71 FND_FILE.PUT_LINE(FND_FILE.LOG,applicationIdIn || ' Application ID does not exist' );
72 end;
73 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Purge run for Transaction Type: ' || applicationName);
74 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,3);
75 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'The following Transaction IDs will be purged');
76 /* Recalculate the attribute use count */
77 for tempAttrUsage in attributeUsageCursor(applicationIdIn) loop
78 attributeId := tempAttrUsage.attribute_id;
79 ame_attribute_pkg.updateUseCount(attributeIdIn => attributeId,
80 applicationIdIn => applicationIdIn);
81 end loop;
82 /* Fetch all transactions which potentially need to be purged */
83 open tempTransaction;
84 loop
85 fetch tempTransaction bulk collect into
86 transactionIds limit bulkFetchRowLimit;
87 if transactionIds.count = 0 then
88 close tempTransaction;
89 exit;
90 end if;
91 /* Check whether every transaction retrieved is complete or not */
92 for i in 1 .. transactionIds.count
93 loop
94 if purgeTypeIn <> 'Y' then
95 /* For each transaction retrieve the approver list from ame_temp_old_approver_lists */
96 open oldApproverCursor(applicationIdIn=> applicationIdIn
97 ,transactionIdIn=> transactionIds(i));
98 fetch oldApproverCursor bulk collect into
99 approverNames
100 ,approverItemClasses
101 ,approverItemIds
102 ,approverCategories
103 ,approverApiInsertions
104 ,approverAuthorities
105 ,approverStatuses
106 ,approverActionTypeIds
107 ,approverGroupOrChainIds
108 ,approverOccurrences ;
109 close oldApproverCursor;
110 /* Check to see if the transaction is complete */
111 /* If there is any approver with a null status or a notified status with approver category = 'A'
112 then transaction is incomplete so do not purge */
113 /* If there is any approver with a status of Rejected then this is a rejected transaction or a
114 partially approved transaction */
115 if approverNames.count >= 0 then
116 for j in 1..approverNames.count
117 loop
118 if (approverStatuses(j) is null or
119 (approverStatuses(j) = ame_util.notifiedStatus and
120 approverStatuses(j) = ame_util.approvalApproverCategory)) then
121 /* Transaction is incomplete so do not purge */
122 transactionIds.delete(i);
123 exit;
124 elsif (approverStatuses(j) = ame_util.rejectStatus and
125 purgeTypeIn <> 'P') then
126 /* Transaction is rejected, and purge type is not 'P' so do not purge */
127 transactionIds.delete(i);
128 exit;
129 else
130 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'1: ' ||transactionIds(i));
131 end if;
132 end loop;
133 else
134 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'2: '||transactionIds(i)); -- P
135 end if;
136 else
137 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'3: ' ||transactionIds(i));
138 end if;
139 end loop;
140 /* compact ID List */
141 ame_util.compactStringList(stringListInOut => transactionIds);
142 /* Do bulk deletes */
143 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'No.of transaction ids to be purged: '||transactionIds.count);
144 if(transactionIds.count > 0) then
145 /* ame_temp_old_approver_lists */
146 forall ct in transactionIds.first..transactionIds.last
147 delete from ame_temp_old_approver_lists
148 where application_id = applicationIdIn and
149 transaction_id = transactionIds(ct);
150 /* ame_temp_insertions */
151 forall ct in transactionIds.first..transactionIds.last
152 delete from ame_temp_insertions
153 where application_id = applicationIdIn and
154 transaction_id = transactionIds(ct);
155 /* ame_temp_deletions */
156 forall ct in transactionIds.first..transactionIds.last
157 delete from ame_temp_deletions
158 where application_id = applicationIdIn and
159 transaction_id = transactionIds(ct);
160 /* ame_trans_approval_history */
161 forall ct in transactionIds.first..transactionIds.last
162 delete from ame_trans_approval_history
163 where application_id = applicationIdIn and
164 transaction_id = transactionIds(ct);
165 /* ame_temp_transactions */
166 forall ct in transactionIds.first..transactionIds.last
167 delete from ame_temp_transactions
168 where application_id = applicationIdIn
169 and transaction_id = transactionIds(ct)
170 and trans_deviation_flag is null or trans_deviation_flag = 'D';
171 end if;
172 end loop;
173 delete from ame_temp_trans_locks
174 where row_timestamp < sysdate - 1;
175 exception
176 when others then
177 FND_FILE.PUT_LINE(FND_FILE.LOG,sqlerrm);
178 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
179 routineNameIn => 'purgeTransData',
180 exceptionNumberIn => sqlcode,
181 exceptionStringIn => sqlerrm);
182 raise;
183 end purgeTransData;
184
185 procedure purgeDeviationData(errbuf out nocopy varchar2,
186 retcode out nocopy number,
187 applicationIdIn in number default null,
188 ameApplicationId in number default null,
189 endDateIn in varchar2 default null) as
190 l_profileDate date;
191 defaultValue date;
192 dateToDelete date;
193 errormsg varchar2(500);
194 errorCode integer;
195 tempTxnId varchar2(100);
196 tempFndapplicationId number;
197 errorMessage ame_util.longStringType;
198 applicationIdList ame_util.idList;
199 tempApplication number;
200 l_endDate date;
201 l_error_date varchar2(12);
202 cursor getApplicationList(applictionIdIn in number) is
203 select application_id
204 from ame_calling_apps
205 where fnd_application_id = applictionIdIn
206 and sysdate between start_date and nvl(end_Date-(1/86400),sysdate);
207 profileNotsetExc exception;
208 invalidInputDateExc exception;
209 deleteTodayRecordExc exception;
210 begin
211 tempFndapplicationId := applicationIdIn;
212 l_profileDate := to_date(FND_PROFILE.VALUE('AME_DEVITION_PURGE_DATE'),'dd/mm/yyyy');
213 l_endDate := fnd_date.canonical_to_date(endDateIn);
214 dateToDelete := l_endDate;
215 FND_FILE.PUT_LINE (FND_FILE.LOG,'Staring AME deviation data purge process');
216 FND_FILE.PUT_LINE (FND_FILE.LOG,'Application id:'||applicationIdIn);
217 FND_FILE.PUT_LINE (FND_FILE.LOG,'AME internal application id:'||ameApplicationId);
218 FND_FILE.PUT_LINE (FND_FILE.LOG,'Date upto record deltion allowed:'||l_endDate);
219 FND_FILE.PUT_LINE (FND_FILE.LOG,'Profile date:'||l_profileDate);
220 if l_endDate > l_profileDate then
221 raise invalidInputDateExc;
222 elsif l_endDate > sysdate then
223 dateToDelete := sysdate;
224 elsif l_endDate <= l_profileDate then
225 dateToDelete := l_endDate;
226 end if;
227 if dateToDelete > trunc(sysdate) or dateToDelete = trunc(sysdate) then
228 raise deleteTodayRecordExc;
229 end if;
230 if applicationIdIn is not null and ameApplicationId is not null then
231 tempApplication := ameApplicationId;
232 begin
233 delete from ame_txn_approvers
234 where temp_transactions_id in
235 (select temp_transactions_id
236 from ame_temp_transactions
237 where trunc(row_timestamp) <= dateToDelete
238 and application_id = tempApplication);
239 FND_FILE.PUT_LINE (FND_FILE.LOG,'number of rows deleted:'||sql%rowcount);
240 update ame_temp_transactions
241 set trans_deviation_flag = 'D'
242 where trunc(row_timestamp) <= dateToDelete
243 and application_id = tempApplication;
244 exception
245 when others then
246 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
247 routineNameIn => 'purgeDeviationData',
248 exceptionNumberIn => sqlcode,
249 exceptionStringIn => sqlerrm);
250 FND_FILE.PUT_LINE (FND_FILE.LOG,sqlerrm);
251 end;
252 elsif ameApplicationId is null and tempFndapplicationId is not null then
253 open getApplicationList(tempFndapplicationId);
254 fetch getApplicationList bulk collect into applicationIdList;
255 close getApplicationList;
256 begin
257 for i in 1..applicationIdList.count loop
258 delete from ame_txn_approvers
259 where temp_transactions_id in
260 (select temp_transactions_id
261 from ame_temp_transactions
262 where trunc(row_timestamp) <= dateToDelete
263 and application_id = applicationIdList(i));
264 FND_FILE.PUT_LINE (FND_FILE.LOG,'AME internal application id:'||applicationIdList(i));
265 FND_FILE.PUT_LINE (FND_FILE.LOG,'number of rows deleted:'||sql%rowcount);
266 update ame_temp_transactions
267 set trans_deviation_flag = 'D'
268 where trunc(row_timestamp) <= dateToDelete
269 and application_id = applicationIdList(i);
270 end loop;
271 exception
272 when others then
273 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
274 routineNameIn => 'purgeDeviationData',
275 exceptionNumberIn => sqlcode,
276 exceptionStringIn => sqlerrm);
277 FND_FILE.PUT_LINE (FND_FILE.LOG,sqlerrm);
278 end;
279 elsif tempTxnId is null and tempFndapplicationId is null then
280 begin
281 delete from ame_txn_approvers
282 where temp_transactions_id in
283 (select temp_transactions_id
284 from ame_temp_transactions
285 where trunc(row_timestamp) <= dateToDelete);
286 FND_FILE.PUT_LINE (FND_FILE.LOG,'number of rows deleted:'||sql%rowcount);
287 update ame_temp_transactions
288 set trans_deviation_flag = 'D'
289 where trunc(row_timestamp) <= dateToDelete;
290 exception
291 when others then
292 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
293 routineNameIn => 'purgeDeviationData',
294 exceptionNumberIn => sqlcode,
295 exceptionStringIn => sqlerrm);
296 FND_FILE.PUT_LINE (FND_FILE.LOG,sqlerrm);
297 end;
298 end if;
299 FND_FILE.PUT_LINE (FND_FILE.LOG,'Completed AME deviation data purge process');
300 exception
301 when profileNotsetExc then
302 errorCode := -20001;
303 errorMessage :=
304 ame_util.getMessage(applicationShortNameIn =>'PER',
305 messageNameIn => 'AME_400828_DEV_PRF_NOTSET');
306 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
307 routineNameIn => 'purgeDeviationData',
308 exceptionNumberIn => errorCode,
309 exceptionStringIn => errorMessage);
310 FND_FILE.PUT_LINE (FND_FILE.LOG,errorMessage);
311 raise_application_error(errorCode,
312 errorMessage);
313 when deleteTodayRecordExc then
314 errorCode := -20001;
315 errorMessage :=
316 ame_util.getMessage(applicationShortNameIn =>'PER',
317 messageNameIn => 'AME_400832_INV_PURGE_UP_TODATE');
318 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
319 routineNameIn => 'purgeDeviationData',
320 exceptionNumberIn => errorCode,
321 exceptionStringIn => errorMessage);
322 FND_FILE.PUT_LINE (FND_FILE.LOG,errorMessage);
323 raise_application_error(errorCode,
324 errorMessage);
325 when invalidInputDateExc then
326 l_error_date := FND_PROFILE.VALUE('AME_DEVITION_PURGE_DATE');
327 errorCode := -20001;
328 errorMessage :=
329 ame_util.getMessage(applicationShortNameIn =>'PER',
330 messageNameIn => 'AME_400830_INVALID_PURGE_DATE',
331 tokenNameOneIn => 'PROFILE_DATE',
332 tokenValueOneIn => l_error_date );
333 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
334 routineNameIn => 'purgeDeviationData',
335 exceptionNumberIn => errorCode,
336 exceptionStringIn => errorMessage);
337 FND_FILE.PUT_LINE (FND_FILE.LOG,errorMessage);
338 raise_application_error(errorCode,
339 errorMessage);
340 when others then
341 ame_util.runtimeException(packageNameIn => 'ame_trans_data_purge',
342 routineNameIn => 'purgeDeviationData',
343 exceptionNumberIn => sqlcode,
344 exceptionStringIn => sqlerrm);
345 FND_FILE.PUT_LINE (FND_FILE.LOG,sqlerrm);
346 raise_application_error(sqlcode,
347 sqlerrm);
348 end purgeDeviationData;
349 end ame_trans_data_purge;
[Home] [Help]