[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_BATCHES_PKG
Source
1 package body pa_purge_batches_pkg as
2 /* $Header: PAXARPBB.pls 120.2 2005/08/05 00:42:45 rgandhi noship $ */
3 procedure insert_row(x_rowid in out NOCOPY VARCHAR2,/*File.sql.39*/
4 x_purge_batch_id in out NOCOPY NUMBER, /*File.sql.39*/
5 x_batch_name in VARCHAR2,
6 x_description in VARCHAR2,
7 x_batch_status_code in VARCHAR2,
8 x_active_closed_flag in VARCHAR2,
9 x_purge_summary_flag in VARCHAR2,
10 x_archive_summary_flag in VARCHAR2,
11 x_purge_budgets_flag in VARCHAR2,
12 x_archive_budgets_flag in VARCHAR2,
13 x_purge_capital_flag in VARCHAR2,
14 x_archive_capital_flag in VARCHAR2,
15 x_purge_actuals_flag in VARCHAR2,
16 x_archive_actuals_flag in VARCHAR2,
17 x_admin_proj_flag in VARCHAR2,
18 x_txn_to_date in DATE,
19 x_next_pp_project_status_code in VARCHAR2,
20 x_next_p_project_status_code in VARCHAR2,
21 x_purged_date in DATE,
22 x_purge_release in VARCHAR2,
23 x_user_id in NUMBER,
24 x_org_id in NUMBER) is
25
26 cursor c is select rowid from pa_purge_batches
27 where purge_batch_id = x_purge_batch_id;
28 cursor get_id is select pa_purge_batches_s.nextval from dual ;
29 x_err_code NUMBER;
30 x_return_status VARCHAR2(630);
31 l_purge_batch_id NUMBER := x_purge_batch_id;
32
33 BEGIN
34
35 if x_purge_batch_id is null then
36 open get_id;
37 fetch get_id into x_purge_batch_id ;
38 close get_id ;
39 end if;
40
41 insert into pa_purge_batches(purge_batch_id,
42 batch_name,
43 description,
44 batch_status_code,
45 active_closed_flag,
46 archive_summary_flag,
47 purge_summary_flag,
48 archive_budgets_flag,
49 purge_budgets_flag,
50 archive_capital_flag,
51 purge_capital_flag,
52 archive_actuals_flag,
53 purge_actuals_flag,
54 admin_proj_flag,
55 txn_to_date ,
56 next_pp_project_status_code,
57 next_p_project_status_code,
58 purged_date,
59 purge_release,
60 last_update_date,
61 last_updated_by ,
62 last_update_login,
63 creation_date,
64 created_by,
65 org_id)
66 values ( x_purge_batch_id,
67 x_batch_name,
68 x_description,
69 x_batch_status_code,
70 x_active_closed_flag,
71 x_archive_summary_flag,
72 x_purge_summary_flag,
73 x_archive_budgets_flag,
74 x_purge_budgets_flag,
75 x_archive_capital_flag,
76 x_purge_capital_flag,
77 x_archive_actuals_flag,
78 x_purge_actuals_flag,
79 x_admin_proj_flag,
80 x_txn_to_date ,
81 x_next_pp_project_status_code,
82 x_next_p_project_status_code,
83 x_purged_date,
84 x_purge_release,
85 sysdate,
86 x_user_id,
87 x_user_id,
88 sysdate,
89 x_user_id,
90 x_org_id);
91
92 open c;
93 fetch c into x_rowid;
94 if (c%notfound) then
95 raise NO_DATA_FOUND;
96 end if;
97 close c;
98
99 exception
100 when others then
101 x_purge_batch_id := l_purge_batch_id;
102 raise ;
103 END insert_row;
104
105 procedure update_row (x_rowid in VARCHAR2,
106 x_purge_batch_id in out NOCOPY NUMBER,/*File.sql.39*/
107 x_batch_name in VARCHAR2,
108 x_description in VARCHAR2,
109 x_batch_status_code in VARCHAR2,
110 x_active_closed_flag in VARCHAR2,
111 x_purge_summary_flag in VARCHAR2,
112 x_archive_summary_flag in VARCHAR2,
113 x_purge_budgets_flag in VARCHAR2,
114 x_archive_budgets_flag in VARCHAR2,
115 x_purge_capital_flag in VARCHAR2,
116 x_archive_capital_flag in VARCHAR2,
117 x_purge_actuals_flag in VARCHAR2,
118 x_archive_actuals_flag in VARCHAR2,
119 x_admin_proj_flag in VARCHAR2,
120 x_txn_to_date in DATE,
121 x_next_pp_project_status_code in VARCHAR2,
122 x_next_p_project_status_code in VARCHAR2,
123 x_purged_date in DATE,
124 x_purge_release in VARCHAR2,
125 x_user_id in NUMBER) is
126
127 BEGIN
128
129 update pa_purge_batches
130 set purge_batch_id = x_purge_batch_id,
131 batch_name = x_batch_name,
132 description = x_description,
133 batch_status_code = x_batch_status_code,
134 active_closed_flag = x_active_closed_flag,
135 purge_summary_flag = x_purge_summary_flag,
136 archive_summary_flag = x_archive_summary_flag,
137 purge_budgets_flag = x_purge_budgets_flag,
138 archive_budgets_flag = x_archive_budgets_flag,
139 purge_capital_flag = x_purge_capital_flag,
140 archive_capital_flag = x_archive_capital_flag,
141 purge_actuals_flag = x_purge_actuals_flag,
142 archive_actuals_flag = x_archive_actuals_flag,
143 admin_proj_flag = x_admin_proj_flag,
144 txn_to_date = x_txn_to_date ,
145 next_pp_project_status_code = x_next_pp_project_status_code,
146 next_p_project_status_code = x_next_p_project_status_code,
147 purged_date = x_purged_date,
148 purge_release = x_purge_release,
149 last_update_date = sysdate,
150 last_updated_by = x_user_id,
151 last_update_login = x_user_id
152 where rowid = x_rowid;
153
154 exception
155 when others then
156 raise ;
157 END update_row;
158
159 -- The delete_row table handler cascades the delete to the
160 -- pa_purge_projects table by calling the pa_purge_projects delete_row
161 -- table handler.
162
163 procedure delete_row (x_rowid in VARCHAR2) is
164 cursor get_batch is select batch_status_code,
165 purge_batch_id
166 from pa_purge_batches
167 where rowid = x_rowid;
168 batches_rec get_batch%rowtype;
169
170 BEGIN
171 open get_batch;
172 fetch get_batch into batches_rec;
173 -- check notfound?
174
175 if (batches_rec.batch_status_code <> 'W') then
176 fnd_message.set_name ('PA', 'PA_ARPUR_ONLY_DEL_WORK');
177 app_exception.raise_exception;
178 end if;
179
180 -- cascade delete to purge projects in the batch.
181 DECLARE
182 cursor purge_projects is
183 select rowid, project_id, last_project_status_code
184 from pa_purge_projects
185 where purge_batch_id = batches_rec.purge_batch_id
186 for update of project_id, purge_batch_id nowait;
187
188 projects_rec purge_projects%rowtype;
189
190 cursor project is
191 select project_id
192 from pa_projects
193 where project_id = projects_rec.project_id
194 for update of project_status_code nowait ;
195
196 BEGIN
197 open purge_projects;
198 LOOP
199 fetch purge_projects into projects_rec;
200 open project ;
201 if (purge_projects%notfound) then
202 exit;
203 else
204 pa_purge_projects_pkg.delete_row ( projects_rec.rowid);
205 end if;
206 close project ;
207 END LOOP;
208
209 close purge_projects ;
210 EXCEPTION
211 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
212 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
213 app_exception.raise_exception;
214 END;
215
216 delete from pa_purge_batches
217 where rowid = x_rowid;
218
219
220 END delete_row;
221
222
223 -- Locks the given row in the database. Does not check if
224 -- values have changed (currently not in use).
225
226 procedure lock_row (x_rowid in VARCHAR2,
227 x_purge_batch_id in out NOCOPY NUMBER,/*file.sql.39*/
228 x_batch_name in VARCHAR2,
229 x_description in VARCHAR2,
230 x_batch_status_code in VARCHAR2,
231 x_active_closed_flag in VARCHAR2,
232 x_purge_summary_flag in VARCHAR2,
233 x_archive_summary_flag in VARCHAR2,
234 x_purge_budgets_flag in VARCHAR2,
235 x_archive_budgets_flag in VARCHAR2,
236 x_purge_capital_flag in VARCHAR2,
237 x_archive_capital_flag in VARCHAR2,
238 x_purge_actuals_flag in VARCHAR2,
239 x_archive_actuals_flag in VARCHAR2,
240 x_admin_proj_flag in VARCHAR2,
241 x_txn_to_date in DATE,
242 x_next_pp_project_status_code in VARCHAR2,
243 x_next_p_project_status_code in VARCHAR2,
244 x_purge_release in VARCHAR2,
245 x_purged_date in DATE) is
246
247 dummy NUMBER;
248
249 CURSOR C is
250 select * from pa_purge_batches
251 where rowid = x_rowid
252 for update of purge_batch_id ;
253
254 recinfo C%ROWTYPE ;
255
256 BEGIN
257 open C;
258 fetch C into recinfo ;
259
260 if C%NOTFOUND then
261 close C ;
262 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
263 APP_EXCEPTION.RAISE_EXCEPTION ;
264 end if ;
265 close C ;
266
267 if ( recinfo.purge_batch_id = x_purge_batch_id and
268 recinfo.batch_name = x_batch_name and
269 recinfo.description = x_description and
270 recinfo.batch_status_code = x_batch_status_code and
271 recinfo.active_closed_flag = x_active_closed_flag and
272 recinfo.purge_summary_flag = x_purge_summary_flag and
273 recinfo.archive_summary_flag = x_archive_summary_flag and
274 recinfo.purge_budgets_flag = x_purge_budgets_flag and
275 recinfo.archive_budgets_flag = x_archive_budgets_flag and
276 recinfo.purge_capital_flag = x_purge_capital_flag and
277 recinfo.archive_capital_flag = x_archive_capital_flag and
278 recinfo.purge_actuals_flag = x_purge_actuals_flag and
279 recinfo.archive_actuals_flag = x_archive_actuals_flag and
280 recinfo.admin_proj_flag = x_admin_proj_flag and
281 ( (recinfo.txn_to_date = x_txn_to_date ) or
282 (recinfo.txn_to_date is null and
283 x_txn_to_date is null) ) and
284 ( (recinfo.next_pp_project_status_code = x_next_pp_project_status_code ) or
285 (recinfo.next_pp_project_status_code is null and
286 x_next_pp_project_status_code is null) ) and
287 ( (recinfo.next_p_project_status_code = x_next_p_project_status_code ) or
288 (recinfo.next_p_project_status_code is null and
289 x_next_pp_project_status_code is null) ) and
290 ( (recinfo.purged_date = x_purged_date ) or
291 (recinfo.purged_date is null and
292 x_purged_date is null) ) and
293 ( (recinfo.purge_release = x_purge_release ) or
294 (recinfo.purge_release is null and
295 x_purge_release is null) )
296
297 ) then
298 return ;
299
300 else
301
302 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
303 app_exception.raise_exception ;
304
305
306 end if ;
307
308 END lock_row;
309
310 ---------------------------------------------------------------------------
311
312
313 END pa_purge_batches_pkg;