1 package body pa_purge_extn as
2 /* $Header: PAXAPPXB.pls 120.1 2005/08/19 17:08:20 mwasowic noship $ */
3 -- forward declarations
4
5 l_commit_size NUMBER ;
6
7 -- Start of comments
8 -- API name : pa_purge_client_extn
9 -- Type : Public
10 -- Pre-reqs : None
11 -- Function : This procedure is the client extension that is called
12 -- from the main purge procedure. Custom code can be added
13 -- to this procedure to purge custom tables.
14 --
15 -- Parameters : p_purge_batch_id IN NUMBER
16 -- The purge batch id for which rows have
17 -- to be purged/archived.
18 -- p_project_Id IN NUMBER,
19 -- The project id for which records have
20 -- to be purged/archived.
21 -- p_Purge_Release IN OUT VARCHAR2,
22 -- The version of the application on which the
23 -- purge process is run.
24 -- p_Txn_Through_Date IN DATE,
25 -- If the purging is being done on projects
26 -- that are active then this parameter
27 -- determines the date through which the
28 -- transactions need to be purged.
29 -- p_Archive_Flag IN OUT VARCHAR2,
30 -- This flag determines if the records need to
31 -- be archived before they are purged. When the
32 -- main procedure calls the client extension,
33 -- this flag is passed a value of 'Y' if actuals
34 -- are being archived, 'N' if actuals are not
35 -- being archived
36 -- p_Calling_Place IN OUT VARCHAR2,
37 -- This parameter will have a value of BEFORE_PURGE
38 -- when the client extension is called at the start
39 -- of the purge process and AFTER_PURGE when the
40 -- client extension is called at the end of the
41 -- purge process
42 -- p_Commit_Size IN NUMBER,
43 -- The number of records that can be allowed to
44 -- remain uncommitted. If the number of records
45 -- goes beyond this number then the process is
46 -- commited.
47 -- X_Err_Stack IN OUT VARCHAR2,
48 -- Error stack
49 -- X_Err_Stage IN OUT VARCHAR2,
50 -- Stage in the procedure where error occurred
51 -- X_Err_Code IN OUT NUMBER
52 -- Error code returned from the procedure
53 --
54 -- Note : The parameter p_txn_through_date includes transactions through
55 -- a given date. However, the archive/purge code and tables refer
56 -- to this parameter as txn_to_date
57 --
58 --
59 -- End of comments
60
61 procedure pa_purge_client_extn ( p_purge_batch_id in NUMBER,
62 p_project_id in NUMBER,
63 p_purge_release in VARCHAR2,
64 p_txn_through_date in DATE,
65 p_archive_flag in VARCHAR2,
66 p_calling_place in VARCHAR2,
67 p_commit_size in NUMBER,
68 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
69 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
70 x_err_code in OUT NOCOPY NUMBER ) is --File.Sql.39 bug 4440895
71
72 l_old_err_stack VARCHAR2(2000);
73 l_err_stage VARCHAR2(500);
74 l_err_stack VARCHAR2(500);
75 l_no_records_del NUMBER ;
76 l_no_records_ins NUMBER ;
77
78 BEGIN
79
80 -- l_old_err_stack := x_err_stack;
81 --
82 -- x_err_stack := x_err_stack || ' ->Before call to purge the data ';
83 -- -- Call the procedure to delete custom table data
84 --
85 if p_calling_place = 'BEFORE_PURGE' then
86
87 -- Call the procedure to purge the respective tables one after the
88 -- other. This section of the code is called before any of the main
89 -- tables are purged in the current run.
90 --
91 -- Note : The p_archive_purge flag determines if the records need to
92 -- be archived before they are purged. This flag is passed a default
93 -- value of 'Y' by the calling procedure if actuals are being
94 -- archived, 'N' if actuals are not being archived
95 -- The value can be changed before calling the <CUST_PROCEDURE>
96 --
97 --
98 -- pa_debug.debug('*-> About to purge Extn data ') ;
99 -- pa_purge_extn.<CUST_PROCEDURE>(p_purge_batch_id => p_purge_batch_id,
100 -- p_project_id => p_project_id,
101 -- p_txn_through_date => p_txn_through_date,
102 -- p_purge_release => p_purge_release,
103 -- p_archive_flag => p_archive_flag,
104 -- p_commit_size => p_commit_size,
105 -- x_err_code => x_err_code,
106 -- x_err_stack => x_err_stack,
107 -- x_err_stage => x_err_stage
108 -- ) ;
109 --
110 NULL ;
111 else
112 -- Call the procedure to purge the respective tables one after the
113 -- other. This section of the code is called after all the main
114 -- tables are purged in the current run.
115 --
116 -- pa_debug.debug('*-> About to purge extn data ') ;
117 -- pa_purge_extn.<CUST_PROCEDURE>(p_purge_batch_id => p_purge_batch_id,
118 -- p_project_id => p_project_id,
119 -- p_txn_through_date => p_txn_through_date,
120 -- p_purge_release => p_purge_release,
121 -- p_archive_flag => p_archive_flag,
122 -- p_commit_size => p_commit_size,
123 -- x_err_code => x_err_code,
124 -- x_err_stack => x_err_stack,
125 -- x_err_stage => x_err_stage
126 -- ) ;
127 --
128 NULL ;
129
130 end if;
131
132 NULL ;
133
134 EXCEPTION
135 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
136 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
137
138 WHEN OTHERS THEN
139 x_err_stage := l_err_stage ;
140 pa_debug.debug('Error Procedure Name := PA_PURGE_EXTN.PA_PURGE_CLIENT_EXTN' );
141 pa_debug.debug('Error stage is '||l_err_stage );
142 pa_debug.debug('Error stack is '||l_err_stack );
143 pa_debug.debug(SQLERRM);
144 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
145
146 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
147
148
149 END pa_purge_client_extn ;
150
151 -- Start of comments
152 -- API name : <CUST_PROCEDURE>
153 -- Type : Public
154 -- Pre-reqs : None
155 -- Function : This procedure purges all the records from <CUST_TABLE>
156 --
157 -- Parameters : Refer to the comments of the previous procedure
158 --
159 -- End of comments
160
161 -- procedure <CUST_PROCEDURE> ( p_purge_batch_id IN NUMBER,
162 -- p_project_id IN NUMBER,
163 -- p_txn_through_date IN DATE,
164 -- p_purge_release IN VARCHAR2,
165 -- p_archive_flag IN VARCHAR2,
166 -- p_commit_size IN NUMBER,
167 -- x_err_code IN OUT NUMBER,
168 -- x_err_stack IN OUT VARCHAR2,
169 -- x_err_stage IN OUT VARCHAR2
170 -- ) is
171 --
172 -- l_old_err_stage VARCHAR2(2000);
173 -- l_old_err_stack VARCHAR2(2000);
174 -- l_NoOfRecordsIns NUMBER;
175 -- l_NoOfRecordsDel NUMBER;
176 -- l_NoOfRecsPrced NUMBER;
177 -- begin
178 --
179 -- l_old_err_stack := x_err_stack;
180 --
181 -- x_err_stack := x_err_stack || ' ->Before insert into <CUST_TABLE_AR>' ;
182 -- LOOP
183 -- if p_archive_flag = 'Y' then
184 -- -- If archive option is selected then the records are
185 -- -- inserted into the archived into the archive tables
186 -- -- before being purged. The where condition is such that
187 -- -- it inserts half the no. of records specified
188 -- -- in the commit size.
189 --
190 -- l_commit_size := p_commit_size / 2 ;
191 -- insert into <CUSTOM_TABLE>_AR
192 -- (
193 -- expenditure_item_id,
194 -- line_number,
195 -- last_update_date,
196 -- last_updated_by,
197 -- creation_date,
198 -- created_by,
199 -- last_update_login,
200 -- request_id,
201 -- program_id,
202 -- program_application_id,
203 -- program_update_date ,
204 -- purge_batch_id,
205 -- purge_release,
206 -- purge_project_id
207 -- )
208 -- select eX.expenditure_item_id,
209 -- eX.line_number
210 -- eX.last_update_date,
211 -- eX.last_updated_by,
212 -- eX.creation_date,
213 -- eX.created_by,
214 -- eX.last_update_login,
215 -- eX.request_id,
216 -- eX.program_id,
217 -- eX.program_application_id,
218 -- eX.program_update_date,
219 -- p_purge_batch_id,
220 -- p_purge_release,
221 -- p_project_id
222 -- from <CUSTOM_TABLE> eX
223 -- where ( eX.rowid )
224 -- in ( select eX1.rowid
225 -- from pa_tasks t,
226 -- pa_expenditure_items_all ei,
227 -- <CUSTOM_TABLE> eX1
228 -- where ei.expenditure_item_id = eX1.expenditure_item_id
229 -- and (p_txn_through_date is null
230 -- or trunc(ei.expenditure_item_date) <= trunc(p_txn_through_date ))
231 -- and ei.task_id = t.task_id
232 -- and t.project_id = p_project_id
233 -- and rownum <= p_commit_size
234 -- ) ;
235 --
236 -- -- Make sure that the custom tables are indexed on
237 -- -- expenditure_item_id
238 --
239 -- l_NoOfRecordsIns := SQL%ROWCOUNT ;
240 -- l_NoOfRecsPrced := SQL%ROWCOUNT ;
241 --
242 --
243 -- if l_NoOfRecsPrced > 0 then
244 -- -- We have a separate delete statement if the archive option is
245 -- -- selected because if archive option is selected the the records
246 -- -- being purged will be those records which are already archived.
247 --
248 --
249 -- delete from <CUST_TABLE> eX
250 -- where ( eX.rowid )
251 -- in ( select eX1.rowid
252 -- from <CUST_TABLE> eX1,
253 -- <CUST_TABLE>_ar eX2
254 -- where eX2.expenditure_item_id = eX1.expenditure_item_id
255 -- and eX2.line_number = eX1.line_number
256 -- and eX2.purge_project_id = p_project_id
257 -- ) ;
258 -- -- The archive tables should be indexed on the combination
259 -- -- of the original primary key and purge_project_id. This
260 -- -- index will improve the performance while purging the
261 -- -- archived records.
262 --
263 -- l_NoOfRecordsDel := SQL%ROWCOUNT ;
264 -- l_NoOfRecsPrced := SQL%ROWCOUNT ;
265 --
266 -- end if;
267 -- else
268 --
269 -- l_commit_size := p_commit_size ;
270 --
271 -- -- If the archive option is not selected then the delete will
272 -- -- be based on the commit size.
273 --
274 -- delete from <CUST_TABLE> eX
275 -- where ( eX.rowid )
276 -- in ( select eX1.rowid
277 -- from pa_tasks t,
278 -- pa_expenditure_items_all ei,
279 -- <CUST_TABLE> eX1
280 -- where ei.expenditure_item_id = eX1.expenditure_item_id
281 -- and (p_txn_through_date is null
282 -- or trunc(ei.expenditure_item_date) <= trunc(p_txn_through_date ))
283 -- and ei.task_id = t.task_id
284 -- and t.project_id = p_project_id
285 -- and rownum <= p_commit_size
286 -- ) ;
287 --
288 -- l_NoOfRecordsDel := SQL%ROWCOUNT ;
289 -- l_NoOfRecsPrced := SQL%ROWCOUNT ;
290 --
291 -- end if ;
292 --
293 -- if l_NoOfRecsPrced = 0 then
294 --
295 -- -- Once the SqlCount becomes 0, which means that there are
296 -- -- no more records to be purged then we exit the loop.
297 --
298 -- exit ;
299 --
300 -- else
301 -- -- After "deleting" or "deleting and inserting" a set of records
302 -- -- the transaction is commited. This also creates a record in the
303 -- -- Pa_Purge_Project_details which will show the number of records
304 -- -- that are purged from each table.
305 --
306 -- pa_purge.CommitProcess(p_purge_batch_id,
307 -- p_project_id,
308 -- '<CUST_TABLE>',
309 -- l_NoOfRecordsIns,
310 -- l_NoOfRecordsDel,
311 -- x_err_code,
312 -- x_err_stack,
313 -- x_err_stage
314 -- ) ;
315 --
316 -- end if ;
317 -- END LOOP ;
318 --
319 -- x_err_stack := l_old_err_stack ;
320 --
321 -- EXCEPTION
322 -- WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
323 -- RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
324 --
325 -- WHEN OTHERS THEN
326 -- x_err_stage := l_err_stage ;
327 -- pa_debug.debug('Error Procedure Name := PA_PURGE_EXTN.<CUST_PROCEDURE>' );
328 -- pa_debug.debug('Error stage is '||l_err_stage );
329 -- pa_debug.debug('Error stack is '||l_err_stack );
330 -- pa_debug.debug(SQLERRM);
331 -- PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
332 --
333 -- RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
334 --
335 --
336 -- end <CUST_PROCEDURE> ;
337
338 END pa_purge_extn;
[Home] [Help]