[Home] [Help]
PACKAGE BODY: APPS.FA_RX_REPORTS_PKG
Source
1 PACKAGE BODY FA_RX_REPORTS_PKG as
2 /* $Header: faxrxdmb.pls 120.7 2006/05/30 12:08:59 rravunny ship $ */
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
7 X_Report_Id NUMBER default null,
8 X_Application_Id NUMBER default null,
9 X_Responsibility_Id NUMBER default null,
10 X_Concurrent_Program_Id NUMBER default null,
11 X_Concurrent_Program_Name VARCHAR2 default null,
12 X_Interface_Table VARCHAR2 default null,
13 X_Concurrent_Program_Flag VARCHAR2 default null,
14 X_Select_Program_Name VARCHAR2 default null,
15 X_Last_Update_Date DATE default null,
16 X_Last_Updated_By NUMBER default null,
17 X_Created_By NUMBER default null,
18 X_Creation_Date DATE default null,
19 X_Last_Update_Login NUMBER default null,
20 X_Where_Clause_API VARCHAR2 default null,
21 X_Purge_API VARCHAR2 default null,
22 X_Calling_Fn VARCHAR2
23 ) IS
24 temp_var varchar2(1);
25 ver_num number := 1; /* default value */
26 x_max_report_id number;
27 x_curr_report_id_s number;
28 dummy number;
29
30 CURSOR C IS SELECT rowid FROM fa_rx_reports
31 WHERE report_id = X_Report_Id;
32
33 BEGIN
34
35 INSERT INTO fa_rx_reports(
36 report_id,
37 application_id,
38 responsibility_id,
39 concurrent_program_id,
40 concurrent_program_name,
41 concurrent_program_flag,
42 interface_table,
43 select_program_name,
44 last_update_date,
45 last_updated_by,
46 created_by,
47 creation_date,
48 last_update_login,
49 where_clause_api,
50 purge_api,
51 version_number
52 ) VALUES (
53 X_report_id,
54 X_application_id,
55 X_responsibility_id,
56 X_concurrent_program_id,
57 X_concurrent_program_name,
58 X_concurrent_program_flag,
59 X_interface_table,
60 decode(X_concurrent_program_flag,'N',X_select_program_name,null),
61 X_Last_Update_Date,
62 X_Last_Updated_By,
63 X_Created_By,
64 X_Creation_Date,
65 X_Last_Update_Login,
66 X_Where_Clause_API,
67 X_Purge_API,
68 ver_num
69 );
70
71 OPEN C;
72 FETCH C INTO X_Rowid;
73 if (C%NOTFOUND) then
74 CLOSE C;
75 Raise NO_DATA_FOUND;
76 end if;
77 CLOSE C;
78
79 select max(report_id) into x_max_report_id from fa_rx_reports;
80 declare
81 seq_no_curr exception;
82 pragma exception_init(seq_no_curr, -8002);
83 begin
84 select fa_rx_reports_s.currval into x_curr_report_id_s from dual;
85 exception
86 when seq_no_curr then
87 select fa_rx_reports_s.nextval into x_curr_report_id_s from dual;
88 end;
89 for i in x_curr_report_id_s .. x_max_report_id loop
90 select fa_rx_reports_s.nextval into dummy from dual;
91 end loop;
92
93 -- EXCEPTION
94 -- WHEN Others THEN
95 -- FA_STANDARD_PKG.RAISE_ERROR
96 -- (Called_Fn => 'FA_RX_REPORTS_PKG.Insert_Row',
97 -- Calling_Fn => X_Calling_Fn);
98 END Insert_Row;
99 --
100 PROCEDURE Lock_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
101 X_Report_Id NUMBER default null,
102 X_Application_Id NUMBER default null,
103 X_Responsibility_Id NUMBER default null,
104 X_Concurrent_Program_Id NUMBER default null,
105 X_Concurrent_Program_Flag VARCHAR2 default null,
106 X_Select_Program_Name VARCHAR2 default null,
107 X_Interface_Table VARCHAR2 default null,
108 X_Where_Clause_API VARCHAR2 default null,
109 X_Purge_API VARCHAR2 default null,
110 X_Calling_Fn VARCHAR2
111 ) IS
112 cursor c_reports is
113 SELECT *
114 FROM FA_RX_REPORTS
115 WHERE ROWID = X_ROWID
116 FOR UPDATE OF REPORT_ID NOWAIT;
117 Recinfo c_reports%rowtype;
118
119 Begin
120 Open c_reports;
121 Fetch c_reports into recinfo;
122 IF (c_reports%notfound) then
123 close c_reports;
124 fnd_message.set_name('FND','FORM_RECORD_DELETED');
125 app_exception.raise_exception;
126
127 End if;
128 Close c_reports;
129 --
130 if (
131 (recinfo.report_id = X_report_id)
132 AND (recinfo.application_id = X_application_id)
133 AND (nvl(recinfo.concurrent_program_id,nvl(X_concurrent_program_id,-9999))
134 = nvl(X_concurrent_program_id,-9999))
135 AND (recinfo.interface_table = X_interface_table)
136 AND (recinfo.concurrent_program_flag = X_concurrent_program_flag)
137 AND (nvl(recinfo.select_program_name,
138 nvl(X_select_program_name,'-9999'))
139 = nvl(X_select_program_name,'-9999'))
140 AND (nvl(recinfo.responsibility_id,nvl(X_responsibility_id,-9999))
141 = nvl(X_responsibility_id,-9999))
142 AND (nvl(recinfo.where_clause_api,
143 nvl(X_Where_Clause_API,'-9999'))
144 = nvl(X_Where_Clause_API,'-9999'))
145 AND (nvl(recinfo.purge_api,
146 nvl(X_Purge_API,'-9999'))
147 = nvl(X_Purge_API,'-9999'))
148 ) then
149 return;
150 else
151 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
152 APP_EXCEPTION.Raise_Exception;
153 end if;
154 END Lock_Row;
155
156 PROCEDURE Update_Row(X_Rowid VARCHAR2,
157 X_Report_Id NUMBER default null,
158 X_Application_Id NUMBER default null,
159 X_Responsibility_Id NUMBER default null,
160 X_Concurrent_Program_Id NUMBER default null,
161 X_Concurrent_Program_Name VARCHAR2 default null,
162 X_Interface_Table VARCHAR2 default null,
163 X_Concurrent_Program_Flag VARCHAR2 default null,
164 X_Select_Program_Name VARCHAR2 default null,
165 X_Last_Update_Date DATE default null,
166 X_Last_Updated_By NUMBER default null,
167 X_Last_Update_Login NUMBER default null,
168 X_Where_Clause_API VARCHAR2 default null,
169 X_Purge_API VARCHAR2 default null,
170 X_Calling_Fn VARCHAR2
171 ) IS
172
173 h_sel_program_name varchar2(240);
174 BEGIN
175
176
177 if (X_concurrent_program_flag = 'N') then
178 h_sel_program_name := X_select_program_name;
179 else h_sel_program_name := null;
180 end if;
181
182
183 if X_Rowid is not null then
184
185 UPDATE fa_rx_reports
186 SET
187 Application_Id = X_Application_Id,
188 Responsibility_Id = X_Responsibility_Id,
189 Concurrent_Program_Id = X_Concurrent_Program_Id,
190 Concurrent_Program_Name = X_Concurrent_Program_Name,
191 Interface_Table = X_Interface_Table ,
192 Concurrent_Program_Flag = X_Concurrent_Program_Flag,
193 Select_Program_Name = h_sel_program_name,
194 Last_Update_Date = X_Last_Update_Date ,
195 Last_Updated_By = X_Last_Updated_By,
196 Last_Update_Login = X_Last_Update_Login,
197 Where_Clause_API = X_Where_Clause_API,
198 Purge_API = X_Purge_API
199 WHERE rowid = X_Rowid;
200 else
201
202 UPDATE fa_rx_reports
203 SET
204 Application_Id = X_Application_Id,
205 Responsibility_Id = X_Responsibility_Id,
206 Concurrent_Program_Id = X_Concurrent_Program_Id,
207 Concurrent_Program_Name = X_Concurrent_Program_Name,
208 Interface_Table = X_Interface_Table ,
209 Concurrent_Program_Flag = X_Concurrent_Program_Flag,
210 Select_Program_Name = h_sel_program_name,
211 Last_Update_Date = X_Last_Update_Date ,
212 Last_Updated_By = X_Last_Updated_By,
213 Last_Update_Login = X_Last_Update_Login,
214 Where_Clause_API = X_Where_Clause_API,
215 Purge_API = X_Purge_API
216 WHERE report_id = x_report_id;
217 end if;
218 if (SQL%NOTFOUND) then
219 Raise NO_DATA_FOUND;
220 end if;
221 -- EXCEPTION
222 -- WHEN Others THEN
223 -- FA_STANDARD_PKG.RAISE_ERROR
224 -- (Called_Fn => 'FA_RX_REPORTS_PKG.Update_Row',
225 -- Calling_Fn => X_Calling_Fn);
226 END Update_Row;
227 --
228 PROCEDURE Delete_Row(X_Rowid VARCHAR2 default null,
229 X_Report_id NUMBER,
230 X_Calling_Fn VARCHAR2) IS
231 BEGIN
232 if X_Rowid is not null then
233 DELETE FROM fa_rx_reports
234 WHERE rowid = X_Rowid;
235 elsif X_Report_Id is not null then
236 DELETE FROM fa_rx_reports
237 WHERE report_id = X_report_id;
238 else
239 -- error
240 null;
241 end if;
242 if (SQL%NOTFOUND) then
243 Raise NO_DATA_FOUND;
244 end if;
245 EXCEPTION
246 WHEN Others THEN
247 FA_STANDARD_PKG.RAISE_ERROR
248 (Called_Fn => 'FA_RX_REPORTS_PKG.Delete_Row',
249 Calling_Fn => X_Calling_Fn);
250 END Delete_Row;
251
252 PROCEDURE Load_Row(
253 X_Report_Id NUMBER default null,
254 X_Application_Name VARCHAR2 default null,
255 X_Responsibility_Id NUMBER default null,
256 X_Concurrent_Program_Name VARCHAR2 default null,
257 X_Concurrent_Program_Flag VARCHAR2 default null,
258 X_Select_Program_Name VARCHAR2 default null,
259 X_Interface_Table VARCHAR2 default null,
260 X_Where_Clause_API VARCHAR2 default null,
261 X_Purge_API VARCHAR2 default null,
262 X_Owner VARCHAR2 default 'SEED')
263 is
264 Begin
265 Load_Row(X_Report_Id ,
266 X_Application_Name ,
267 X_Responsibility_Id ,
268 X_Concurrent_Program_Name ,
269 X_Concurrent_Program_Flag,
270 X_Select_Program_Name ,
271 X_Interface_Table ,
272 X_Where_Clause_API ,
273 X_Purge_API ,
274 X_Owner ,
275 Null,
276 Null);
277 End;
278
279 PROCEDURE Load_Row(
280 X_Report_Id NUMBER default null,
281 X_Application_Name VARCHAR2 default null,
282 X_Responsibility_Id NUMBER default null,
283 X_Concurrent_Program_Name VARCHAR2 default null,
284 X_Concurrent_Program_Flag VARCHAR2 default null,
285 X_Select_Program_Name VARCHAR2 default null,
286 X_Interface_Table VARCHAR2 default null,
287 X_Where_Clause_API VARCHAR2 default null,
288 X_Purge_API VARCHAR2 default null,
289 X_Owner VARCHAR2 default 'SEED',
290 X_Last_Update_Date VARCHAR2,
291 X_CUSTOM_MODE in VARCHAR2
292 )
293 Is
294 x_userid number;
295 x_rowid varchar2(64);
296 x_concurrent_program_id number;
297 x_application_id number;
298
299 --* Bug#5102292, rravunny
300 --* Begin
301 --*
302 f_luby number; -- entity owner in file
303 f_ludate date; -- entity update date in file
304 db_luby number; -- entity owner in db
305 db_ludate date; -- entity update date in db
306 --* End
307 --*
308
309 Begin
310 select application_id into x_application_id
311 from fnd_application
312 where application_short_name = X_Application_Name;
313
314 if x_concurrent_program_flag = 'N' then
315 x_concurrent_program_id := null;
316 else
317 begin
318 select concurrent_program_id
319 into x_concurrent_program_id
320 from fnd_concurrent_programs
321 where application_id = x_application_id
322 and concurrent_program_name = x_concurrent_program_name;
323 exception
324 when no_data_found then
325 x_concurrent_program_id := NULL;
326 end;
327 end if;
328
329 --* Bug#5102292, rravunny
330 --* Begin
331 --*
332 f_luby := fnd_load_util.owner_id(X_Owner);
333
334 -- Translate char last_update_date to date
335 f_ludate := nvl(to_date(X_Last_Update_Date, 'YYYY/MM/DD HH24:MI:SS'), sysdate);
336
337 select LAST_UPDATED_BY, LAST_UPDATE_DATE
338 into db_luby, db_ludate
339 from fa_rx_reports
340 where report_id = X_Report_Id;
341 --* End
342 --*
343
344 x_userid := f_luby;
345
346 If (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE)) Then
347 update_row(
348 X_Rowid => null,
349 X_Report_Id => X_Report_Id,
350 X_Application_Id => X_Application_Id,
351 X_Responsibility_Id => X_Responsibility_Id,
352 X_Concurrent_Program_Id => X_Concurrent_Program_Id,
353 X_Concurrent_Program_Name => X_Concurrent_Program_Name,
354 X_Interface_Table => X_Interface_Table,
355 X_Concurrent_Program_Flag => X_Concurrent_Program_Flag,
356 X_Select_Program_Name => X_Select_Program_Name,
357 X_Last_Update_Date => f_ludate,
358 X_Last_Updated_By => f_luby,
359 X_Last_Update_Login => 0,
360 X_Where_Clause_API => X_Where_Clause_API,
361 X_Purge_API => X_Purge_API,
362 X_Calling_Fn => 'Load_Row');
363 End If;
364 exception
365 when NO_DATA_FOUND then
366 insert_row(
367 X_Rowid => X_Rowid,
368 X_Report_Id => X_Report_Id,
369 X_Application_Id => X_Application_Id,
370 X_Responsibility_Id => X_Responsibility_Id,
371 X_Concurrent_Program_Id => X_Concurrent_Program_Id,
372 X_Concurrent_Program_Name => X_Concurrent_Program_Name,
373 X_Interface_Table => X_Interface_Table,
374 X_Concurrent_Program_Flag => X_Concurrent_Program_Flag,
375 X_Select_Program_Name => X_Select_Program_Name,
376 X_Last_Update_Date => f_ludate,
377 X_Last_Updated_By => f_luby,
378 X_Created_By => f_luby,
379 X_Creation_Date => f_ludate,
380 X_Last_Update_Login => 0,
381 X_Where_Clause_API => X_Where_Clause_API,
382 X_Purge_API => X_Purge_API,
383 X_Calling_Fn => 'Load_Row');
384
385 end Load_Row;
386
387 FUNCTION validate_plsql_block(p_plsql IN VARCHAR2) return BOOLEAN is
388 l_cursor integer;
389 dummy varchar2(80);
390 plsql_block varchar2(200);
391
392 Begin
393 --
394 if (g_print_debug) then
395 arp_util_tax.debug('fa_rx_reports_pkg.validate_plsql_block(+)');
396 end if;
397 --
398 IF p_plsql IS not null THEN
399 if (g_print_debug) then
400 arp_util_tax.debug(p_plsql);
401 end if;
402
403 BEGIN
404 plsql_block := 'BEGIN :dummy := ' || p_plsql ||'(0); end;';
405 execute immediate plsql_block using out dummy;
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 if (g_print_debug) then
410 arp_util_tax.debug('Invalid Where Clause API/Purge API.');
411 end if;
412
413 return FALSE;
414 END;
415 end if;
416
417 if (g_print_debug) then
418 arp_util_tax.debug('fa_rx_reports_pkg.validate_plsql_block(-)');
419 end if;
420
421 return TRUE;
422 End;
423
424 END FA_RX_REPORTS_PKG;