1 package body pa_purge_projects_pkg as
2 /* $Header: PAXARPPB.pls 120.1 2005/08/05 00:47:38 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_project_id in NUMBER,
6 x_last_project_status_code in VARCHAR2,
7 x_purge_summary_flag in VARCHAR2,
8 x_archive_summary_flag in VARCHAR2,
9 x_purge_budgets_flag in VARCHAR2,
10 x_archive_budgets_flag in VARCHAR2,
11 x_purge_capital_flag in VARCHAR2,
12 x_archive_capital_flag in VARCHAR2,
13 x_purge_actuals_flag in VARCHAR2,
14 x_archive_actuals_flag in VARCHAR2,
15 x_txn_to_date in DATE,
16 x_purge_project_status_code in VARCHAR2,
17 x_next_pp_project_status_code in VARCHAR2,
18 x_next_p_project_status_code in VARCHAR2,
19 x_purged_date in DATE,
20 x_user_id in NUMBER ) is
21
22 cursor c is select rowid from pa_purge_projects
23 where purge_batch_id = x_purge_batch_id
24 and project_id = x_project_id;
25
26 x_err_code NUMBER;
27 x_return_status VARCHAR2(630);
28
29 BEGIN
30
31 insert into pa_purge_projects(purge_batch_id,
32 project_id,
33 last_project_status_code,
34 purge_summary_flag,
35 archive_summary_flag,
36 purge_budgets_flag,
37 archive_budgets_flag,
38 purge_capital_flag,
39 archive_capital_flag,
40 purge_actuals_flag,
41 archive_actuals_flag,
42 purge_project_status_code,
43 next_pp_project_status_code,
44 next_p_project_status_code,
45 txn_to_date ,
46 creation_date,
47 created_by,
48 last_update_date,
49 last_updated_by )
50 values (x_purge_batch_id,
51 x_project_id,
52 x_last_project_status_code,
53 x_purge_summary_flag,
54 x_archive_summary_flag,
55 x_purge_budgets_flag,
56 x_archive_budgets_flag,
57 x_purge_capital_flag,
58 x_archive_capital_flag,
59 x_purge_actuals_flag,
60 x_archive_actuals_flag,
61 x_purge_project_status_code,
62 x_next_pp_project_status_code,
63 x_next_p_project_status_code,
64 x_txn_to_date ,
65 sysdate,
66 x_user_id,
67 sysdate,
68 x_user_id) ;
69
70 open c;
71 fetch c into x_rowid;
72 if (c%notfound) then
73 raise NO_DATA_FOUND;
74 end if;
75 close c;
76
77 exception
78 when others then
79 raise ;
80 END insert_row;
81
82 procedure update_row (x_rowid in VARCHAR2,
83 x_purge_batch_id in NUMBER,
84 x_project_id in NUMBER,
85 x_last_project_status_code in VARCHAR2,
86 x_purge_summary_flag in VARCHAR2,
87 x_archive_summary_flag in VARCHAR2,
88 x_purge_budgets_flag in VARCHAR2,
89 x_archive_budgets_flag in VARCHAR2,
90 x_purge_capital_flag in VARCHAR2,
91 x_archive_capital_flag in VARCHAR2,
92 x_purge_actuals_flag in VARCHAR2,
93 x_archive_actuals_flag in VARCHAR2,
94 x_txn_to_date in DATE,
95 x_purge_project_status_code in VARCHAR2,
96 x_next_pp_project_status_code in VARCHAR2,
97 x_next_p_project_status_code in VARCHAR2,
98 x_purged_date in DATE,
99 x_user_id in NUMBER ) is
100
101
102 BEGIN
103
104 update pa_purge_projects
105 set purge_batch_id = x_purge_batch_id,
106 project_id = x_project_id,
107 last_project_status_code = x_last_project_status_code,
108 purge_summary_flag = x_purge_summary_flag ,
109 archive_summary_flag = x_archive_summary_flag,
110 purge_budgets_flag = x_purge_budgets_flag ,
111 archive_budgets_flag = x_archive_budgets_flag,
112 purge_capital_flag = x_purge_capital_flag ,
113 archive_capital_flag = x_archive_capital_flag,
114 purge_actuals_flag = x_purge_actuals_flag ,
115 archive_actuals_flag = x_archive_actuals_flag,
116 purge_project_status_code = x_purge_project_status_code,
117 next_pp_project_status_code = x_next_pp_project_status_code,
118 next_p_project_status_code = x_next_p_project_status_code,
119 txn_to_date = x_txn_to_date ,
120 last_update_date = sysdate,
121 last_updated_by = x_user_id,
122 last_update_login = x_user_id
123 where rowid = x_rowid;
124
125 exception
126 when others then
127 raise ;
128 END update_row;
129
130 -- The delete_row table handler cascades the delete to the
131 -- expenditures table by calling the expenditures delete_row
132 -- table handler.
133
134 procedure delete_row (x_rowid in VARCHAR2) is
135
136 cursor purge_projects is
137 select purge_batch_id, project_id, last_project_status_code
138 from pa_purge_projects
139 where rowid = x_rowid
140 for update of project_id, purge_batch_id nowait;
141
142 projects_rec purge_projects%rowtype;
143 cursor project is
144 select project_id
145 from pa_projects
146 where project_id = projects_rec.project_id
147 for update of project_status_code nowait ;
148
149 BEGIN
150 open purge_projects;
151 fetch purge_projects into projects_rec;
152 open project ;
153 if (purge_projects%notfound) then
154 null;
155 else
156 update pa_projects
157 set project_status_code = projects_rec.last_project_status_code
158 where project_id = projects_rec.project_id ;
159
160 delete from pa_purge_project_errors
161 where project_id = projects_rec.project_id
162 and purge_batch_id = projects_rec.purge_batch_id ;
163
164 delete from pa_purge_projects
165 where rowid = x_rowid;
166 end if;
167 close project ;
168
169 close purge_projects ;
170
171 EXCEPTION
172 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
173 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
174 app_exception.raise_exception;
175
176 END delete_row;
177
178
179 -- Locks the given row in the database. Does not check if
180 -- values have changed (currently not in use).
181
182 procedure lock_row (x_rowid in VARCHAR2,
183 x_purge_batch_id in NUMBER,
184 x_project_id in NUMBER,
185 x_last_project_status_code in VARCHAR2,
186 x_purge_summary_flag in VARCHAR2,
187 x_archive_summary_flag in VARCHAR2,
188 x_purge_budgets_flag in VARCHAR2,
189 x_archive_budgets_flag in VARCHAR2,
190 x_purge_capital_flag in VARCHAR2,
191 x_archive_capital_flag in VARCHAR2,
192 x_purge_actuals_flag in VARCHAR2,
193 x_archive_actuals_flag in VARCHAR2,
194 x_txn_to_date in DATE,
195 x_purge_project_status_code in VARCHAR2,
196 x_next_pp_project_status_code in VARCHAR2,
197 x_next_p_project_status_code in VARCHAR2,
198 x_purged_date in DATE) is
199
200 dummy NUMBER;
201
202 CURSOR C is
203 select * from pa_purge_projects
204 where rowid = x_rowid
205 for update of project_id ;
206
207 recinfo C%ROWTYPE ;
208
209 BEGIN
210 open C;
211 fetch C into recinfo ;
212
213 if C%NOTFOUND then
214 close C ;
215 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
216 APP_EXCEPTION.RAISE_EXCEPTION ;
217 end if ;
218 close C ;
219
220 if ( recinfo.purge_batch_id = x_purge_batch_id and
221 recinfo.project_id = x_project_id and
222 recinfo.last_project_status_code = x_last_project_status_code and
223 recinfo.purge_summary_flag = x_purge_summary_flag and
224 recinfo.archive_summary_flag = x_archive_summary_flag and
225 recinfo.purge_budgets_flag = x_purge_budgets_flag and
226 recinfo.archive_budgets_flag = x_archive_budgets_flag and
227 recinfo.purge_capital_flag = x_purge_capital_flag and
228 recinfo.archive_capital_flag = x_archive_capital_flag and
229 recinfo.purge_actuals_flag = x_purge_actuals_flag and
230 recinfo.archive_actuals_flag = x_archive_actuals_flag and
231 recinfo.purge_project_status_code = x_purge_project_status_code and
232 ( (recinfo.txn_to_date = x_txn_to_date ) or
233 (recinfo.txn_to_date is null and
234 x_txn_to_date is null) ) and
235 ( (recinfo.next_pp_project_status_code = x_next_pp_project_status_code ) or
236 (recinfo.next_pp_project_status_code is null and
237 x_next_pp_project_status_code is null) ) and
238 ( (recinfo.next_p_project_status_code = x_next_p_project_status_code ) or
239 (recinfo.next_p_project_status_code is null and
240 x_next_pp_project_status_code is null) ) and
241 ( (recinfo.purged_date = x_purged_date ) or
242 (recinfo.purged_date is null and
243 x_purged_date is null) )
244
245 ) then
246 return ;
247
248 else
249
250 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
251 app_exception.raise_exception ;
252
253
254 end if ;
255
256 END lock_row;
257
258 ---------------------------------------------------------------------------
259
260
261 END pa_purge_projects_pkg;
[Home] [Help]