[Home] [Help]
PACKAGE BODY: APPS.FA_MAINT_PURGE_PKG
Source
1 PACKAGE BODY FA_MAINT_PURGE_PKG as
2 /* $Header: FAXMTPRB.pls 120.1 2002/11/12 08:11:47 glchen ship $ */
3
4 h_progname varchar2(30) := 'Asset Maintenance Purge';
5
6 procedure do_purge(
7 errbuf out nocopy varchar2,
8 retcode out nocopy varchar2,
9 argument1 in varchar2, -- book_type_code
10 argument2 in varchar2, -- from asset number
11 argument3 in varchar2, -- to asset number
12 argument4 in varchar2, -- from date
13 argument5 in varchar2, -- to date
14 argument6 in varchar2, -- category_id
15 argument7 in varchar2, -- status
16 argument8 in varchar2 default null,
17 argument9 in varchar2 default null,
18 argument10 in varchar2 default null,
19 argument11 in varchar2 default null,
20 argument12 in varchar2 default null,
21 argument13 in varchar2 default null,
22 argument14 in varchar2 default null,
23 argument15 in varchar2 default null,
24 argument16 in varchar2 default null,
25 argument17 in varchar2 default null,
26 argument18 in varchar2 default null,
27 argument19 in varchar2 default null,
28 argument20 in varchar2 default null,
29 argument21 in varchar2 default null,
30 argument22 in varchar2 default null,
31 argument23 in varchar2 default null,
32 argument24 in varchar2 default null,
33 argument25 in varchar2 default null,
34 argument26 in varchar2 default null,
35 argument27 in varchar2 default null,
36 argument28 in varchar2 default null,
37 argument29 in varchar2 default null,
38 argument30 in varchar2 default null,
39 argument31 in varchar2 default null,
40 argument32 in varchar2 default null,
41 argument33 in varchar2 default null,
42 argument34 in varchar2 default null,
43 argument35 in varchar2 default null,
44 argument36 in varchar2 default null,
45 argument37 in varchar2 default null,
46 argument38 in varchar2 default null,
47 argument39 in varchar2 default null,
48 argument40 in varchar2 default null,
49 argument41 in varchar2 default null,
50 argument42 in varchar2 default null,
51 argument43 in varchar2 default null,
52 argument44 in varchar2 default null,
53 argument45 in varchar2 default null,
54 argument46 in varchar2 default null,
55 argument47 in varchar2 default null,
56 argument48 in varchar2 default null,
57 argument49 in varchar2 default null,
58 argument50 in varchar2 default null,
59 argument51 in varchar2 default null,
60 argument52 in varchar2 default null,
61 argument53 in varchar2 default null,
62 argument54 in varchar2 default null,
63 argument55 in varchar2 default null,
64 argument56 in varchar2 default null,
65 argument57 in varchar2 default null,
66 argument58 in varchar2 default null,
67 argument59 in varchar2 default null,
68 argument60 in varchar2 default null,
69 argument61 in varchar2 default null,
70 argument62 in varchar2 default null,
71 argument63 in varchar2 default null,
72 argument64 in varchar2 default null,
73 argument65 in varchar2 default null,
74 argument66 in varchar2 default null,
75 argument67 in varchar2 default null,
76 argument68 in varchar2 default null,
77 argument69 in varchar2 default null,
78 argument70 in varchar2 default null,
79 argument71 in varchar2 default null,
80 argument72 in varchar2 default null,
81 argument73 in varchar2 default null,
82 argument74 in varchar2 default null,
83 argument75 in varchar2 default null,
84 argument76 in varchar2 default null,
85 argument77 in varchar2 default null,
86 argument78 in varchar2 default null,
87 argument79 in varchar2 default null,
88 argument80 in varchar2 default null,
89 argument81 in varchar2 default null,
90 argument82 in varchar2 default null,
91 argument83 in varchar2 default null,
92 argument84 in varchar2 default null,
93 argument85 in varchar2 default null,
94 argument86 in varchar2 default null,
95 argument87 in varchar2 default null,
96 argument88 in varchar2 default null,
97 argument89 in varchar2 default null,
98 argument90 in varchar2 default null,
99 argument91 in varchar2 default null,
100 argument92 in varchar2 default null,
101 argument93 in varchar2 default null,
102 argument94 in varchar2 default null,
103 argument95 in varchar2 default null,
104 argument96 in varchar2 default null,
105 argument97 in varchar2 default null,
106 argument98 in varchar2 default null,
107 argument99 in varchar2 default null,
108 argument100 in varchar2 default null) is
109
110
111 h_mesg_str varchar2(2000);
112 h_mesg_name varchar2(30);
113 h_book_type_code varchar2(15);
114 h_from_date date;
115 h_to_date date;
116 h_from_asset_number varchar2(15);
117 h_to_asset_number varchar2(15);
118 h_status varchar2(10);
119 h_category_id number;
120 h_cat_struct_id number;
121 h_concat_string varchar2(500);
122 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
123 h_arg_exist boolean:= FALSE;
124 savepoint_set boolean:= FALSE;
125 prog_failed exception;
126
127 /* cursor for assets_to_purge is
128 select me.schedule_id
129 from fa_maint_events me,
130 fa_additions ad
131 where ad.asset_number >= nvl(h_from_asset_number,ad.asset_number) and
132 ad.asset_number <= nvl(h_to_asset_number,ad.asset_number) and
133 ad.category_id = nvl(h_category_id,ad.category_id) and
134 ad.asset_id = me.asset_id and
135 me.schedule_id = nvl(h_schedule_id,me.schedule_id) and
136 me.maintenance_date between nvl(h_from_date,me.maintenance_date) and
137 nvl(h_to_date,me.maintenance_date) and
138 me.status = nvl(h_status,me.status); */
139
140
141 BEGIN
142
143 retcode := 0;
144
145 -- print out arguments
146
147 SELECT category_flex_structure
148 INTO h_cat_struct_id
149 FROM fa_system_controls;
150
151 fnd_message.set_name('OFA','FA_ASSET_MAINT_PURARG_LIST');
152 fnd_message.set_token('SCHID', argument1, FALSE);
153 fnd_message.set_token('FROM_ASSET', argument2, FALSE);
154 fnd_message.set_token('TO_ASSET', argument3,FALSE);
155 fnd_message.set_token('FROM_DATE', fnd_date.date_to_chardate(
156 fnd_date.canonical_to_date(argument4)),FALSE);
157 fnd_message.set_token('TO_DATE', fnd_date.date_to_chardate(
158 fnd_date.canonical_to_date(argument5)),FALSE);
159 -- fa_rx_shared_pkg.concat_category(h_cat_struct_id,to_number(argument6),
160 -- h_concat_string,h_cat_segs);
161 fnd_message.set_token('CAT',argument6,FALSE);
162 fnd_message.set_token('STAT',argument7,FALSE);
163 h_mesg_str := fnd_message.get;
164 fa_rx_conc_mesg_pkg.log(h_mesg_str);
165 fa_rx_conc_mesg_pkg.log('');
166
167
168 -- check if valid arguments are passed into the program
169
170 if (argument1 is not NULL) then
171 h_book_type_code := argument1;
172 h_arg_exist := TRUE;
173 end if;
174
175 if (argument2 is not NULL and
176 argument3 is not NULL) then
177 h_from_asset_number := argument2;
178 h_to_asset_number := argument3;
179 h_arg_exist := TRUE;
180 elsif ((argument2 is NULL and argument3 is not NULL) or
181 (argument2 is not NULL and argument3 is NULL)) then
182 fnd_message.set_name('OFA','FA_ASSET_MAINT_WRONG_PARAM');
183 h_mesg_str := fnd_message.get;
184 fa_rx_conc_mesg_pkg.log(h_mesg_str);
185 fa_rx_conc_mesg_pkg.log('');
186 fa_rx_conc_mesg_pkg.out(h_mesg_str);
187 fa_rx_conc_mesg_pkg.out('');
188 retcode := 2;
189 raise prog_failed;
190 end if;
191
192 if (argument4 is not NULL and
193 argument5 is not NULL) then
194 h_from_date := fnd_date.canonical_to_date(argument4);
195 h_to_date := fnd_date.canonical_to_date(argument5);
196 h_arg_exist := TRUE;
197 elsif ((argument4 is NULL and argument5 is not NULL) or
198 (argument4 is not NULL and argument5 is NULL)) then
199 fnd_message.set_name('OFA','FA_ASSET_MAINT_WRONG_PARAM');
200 h_mesg_str := fnd_message.get;
201 fa_rx_conc_mesg_pkg.log(h_mesg_str);
202 fa_rx_conc_mesg_pkg.log('');
203 fa_rx_conc_mesg_pkg.out(h_mesg_str);
204 fa_rx_conc_mesg_pkg.out('');
205 retcode := 2;
206 raise prog_failed;
207 end if;
208
209 if (argument6 is not NULL) then
210 h_category_id := to_number(argument6);
211 h_arg_exist := TRUE;
212 end if;
213
214 if (argument7 is not NULL) then
215 h_status := argument7;
216 h_arg_exist := TRUE;
217 end if;
218
219 if (NOT h_arg_exist) then
220 fnd_message.set_name('OFA','FA_ASSET_MAINT_WRONG_PARAM');
221 h_mesg_str := fnd_message.get;
222 fa_rx_conc_mesg_pkg.log(h_mesg_str);
223 fa_rx_conc_mesg_pkg.log('');
224 fa_rx_conc_mesg_pkg.out(h_mesg_str);
225 fa_rx_conc_mesg_pkg.out('');
226 retcode := 2;
227 raise prog_failed;
228 end if;
229
230
231 -- delete rows from fa_asset_maint table
232
233 h_mesg_name := 'FA_SHARED_DELETE_FAILED';
234 savepoint asset_maint;
235 savepoint_set := TRUE;
236
237 delete from fa_maint_events me
238 where me.book_type_code = nvl(h_book_type_code,me.book_type_code) and
239 me.status = nvl(h_status, me.status) and
240 me.maintenance_date between nvl(h_from_date,me.maintenance_date) and
241 nvl(h_to_date,me.maintenance_date) and
242 me.asset_id = (select ad.asset_id
243 from fa_additions ad
244 where ad.asset_number >= nvl(h_from_asset_number,ad.asset_number)
245 and ad.asset_number <= nvl(h_to_asset_number,ad.asset_number)
246 and ad.asset_category_id = nvl(h_category_id,ad.asset_category_id)
247 and ad.asset_id = me.asset_id);
248
249
250 fnd_message.set_name('OFA', 'FA_SHARED_END_SUCCESS');
251 fnd_message.set_token('PROGRAM',h_progname,FALSE);
252 h_mesg_str := fnd_message.get;
253 fa_rx_conc_mesg_pkg.log(h_mesg_str);
254 fa_rx_conc_mesg_pkg.log('');
255 fa_rx_conc_mesg_pkg.out(h_mesg_str);
256
257 retcode := 0;
258
259 EXCEPTION
260 when prog_failed then
261 if (savepoint_set) then
262 rollback to savepoint asset_maint;
263 end if;
264 fnd_message.set_name('OFA', 'FA_SHARED_END_WITH_ERROR');
265 fnd_message.set_token('PROGRAM',h_progname,FALSE);
266 h_mesg_str := fnd_message.get;
267 fa_rx_conc_mesg_pkg.log(h_mesg_str);
268 fa_rx_conc_mesg_pkg.out(h_mesg_str);
269 retcode := 2;
270
271 when others then
272 if (savepoint_set) then
273 rollback to savepoint asset_maint;
274 end if;
275 fnd_message.set_name('OFA',h_mesg_name);
276 fnd_message.set_token('TABLE','FA_MAINT_EVENTS',FALSE);
277 h_mesg_str := fnd_message.get;
278 fa_rx_conc_mesg_pkg.log(h_mesg_str);
279 fa_rx_conc_mesg_pkg.log('');
280
281 fnd_message.set_name('OFA', 'FA_SHARED_END_WITH_ERROR');
282 fnd_message.set_token('PROGRAM',h_progname,FALSE);
283 h_mesg_str := fnd_message.get;
284 fa_rx_conc_mesg_pkg.log(h_mesg_str);
285 fa_rx_conc_mesg_pkg.out(h_mesg_str);
286 retcode := 2;
287
288 END do_purge;
289
290
291 END FA_MAINT_PURGE_PKG;