[Home] [Help]
PACKAGE BODY: APPS.EAM_INT_UTILS
Source
1 PACKAGE BODY eam_int_utils AS
2 /* $Header: EAMINTUB.pls 120.0 2005/05/25 15:42:55 appldev noship $ */
3
4 procedure add_error(p_interface_id number,
5 p_text varchar2,
6 p_error_type number) IS
7
8 error_record request_error;
9 error_type number;
10 begin
11
12 error_record.interface_id := p_interface_id;
13 error_record.error_type := p_error_type;
14 error_record.error := substr(p_text,1,500);
15
16 current_errors(current_errors.count+1) := error_record;
17
18 end add_error;
19
20
21 procedure load_errors(p_source_interface_table in varchar2) is
22 n_errors number;
23 error_no number := 1;
24 x_statement varchar2(2000);
25 x_cursor_id integer;
26 x_dummy integer;
27 begin
28 x_statement :=
29 ' insert into wip_interface_errors ( ' ||
30 ' interface_id, error_type, error, ' ||
31 ' last_update_date, creation_date, created_by, ' ||
32 ' last_update_login, last_updated_by ' ||
33 ' ) ' ||
34 ' select ' ||
35 ' :interface_id_1, :error_type, :error, ' ||
36 ' sysdate, sysdate, created_by, ' ||
37 ' last_update_login, last_updated_by ' ||
38 --4247057 begin
39 ' from :l_source_table where interface_id = :interface_id_2 ';
40 --4247057 End
41
42 x_cursor_id := dbms_sql.open_cursor;
43
44 n_errors := current_errors.count;
45
46 dbms_sql.parse(x_cursor_id, x_statement, dbms_sql.native);
47 WHILE (error_no <= n_errors) LOOP
48 dbms_sql.bind_variable(x_cursor_id, ':interface_id_1',
49 current_errors(error_no).interface_id);
50 dbms_sql.bind_variable(x_cursor_id, ':error_type',
51 current_errors(error_no).error_type);
52 dbms_sql.bind_variable(x_cursor_id, ':error',
53 current_errors(error_no).error);
54 dbms_sql.bind_variable(x_cursor_id, ':interface_id_2',
55 current_errors(error_no).interface_id);
56 --4247057 Begin
57 dbms_sql.bind_variable(x_cursor_id, ':l_source_table', p_source_interface_table);
58 --4247057 End
59
60 x_dummy := dbms_sql.execute(x_cursor_id);
61 error_no := error_no + 1;
62
63 END LOOP;
64 dbms_sql.close_cursor(x_cursor_id);
65 current_errors.delete;
66
67 end load_errors;
68
69
70 function has_errors return boolean is
71 n_errors number;
72 error_no number := 1;
73 error_happened boolean := false;
74 begin
75 n_errors := current_errors.count;
76 WHILE (error_no <= n_errors AND error_happened = false) LOOP
77 if ( current_errors(error_no).error_type = 1 ) then
78 error_happened := true;
79 end if;
80 error_no := error_no + 1;
81 END LOOP;
82
83 return error_happened;
84 end has_errors;
85
86
87 procedure abort_request is
88 begin
89 raise_application_error(
90 -20240,
91 'EAM Interface Request Processing Aborted');
92 end abort_request;
93
94
95 procedure warn_irrelevant_column(p_current_rowid in rowid,
96 p_current_interface_id in number,
97 p_table_name in varchar2,
98 p_column in varchar2,
99 p_condition in varchar2) is
100 x_condition varchar2(2000);
101 begin
102 x_condition := p_column || ' is not null';
103 if ( p_condition is not null ) then
104 x_condition := x_condition || ' and ' || p_condition;
105 end if;
106
107 if (request_matches_condition(p_current_rowid,
108 p_current_interface_id,
109 p_table_name,
110 x_condition)) then
111 record_ignored_column_warning(p_current_interface_id,
112 p_column);
113 end if ;
114 end warn_irrelevant_column;
115
116
117 procedure warn_redundant_column(p_current_rowid in rowid,
118 p_current_interface_id in number,
119 p_table_name in varchar2,
120 p_column_being_used in varchar2,
121 p_column_being_ignored in varchar2,
122 p_condition in varchar2 default null) is
123 x_condition varchar2(2000);
124 x_interface_id number;
125 begin
126 x_condition :=
127 p_column_being_used || ' is not null and ' ||
128 p_column_being_ignored || ' is not null';
129 if ( p_condition is not null ) then
130 x_condition := x_condition || ' and ' || p_condition;
131 end if;
132
133 if ( request_matches_condition(p_current_rowid,
134 p_current_interface_id,
135 p_table_name,
136 x_condition) ) then
137 record_ignored_column_warning(p_current_interface_id, p_column_being_ignored);
138 end if;
139
140 end warn_redundant_column;
141
142
143 procedure derive_id_from_code(p_current_rowid in rowid,
144 p_current_interface_id in number,
145 p_table_name in varchar2,
146 p_id_column in varchar2,
147 p_code_column in varchar2,
148 p_derived_value_expression in varchar2,
149 p_id_required in boolean default true) is
150 x_condition varchar2(2000);
151 begin
152 -- if both the code and id filled in, we ignore the code column.
153 warn_redundant_column(p_current_rowid,
154 p_current_interface_id,
155 p_table_name,
156 p_id_column,
157 p_code_column);
158
159 x_condition := p_code_column || ' is not null';
160
161
162 default_if_null(p_current_rowid,
163 p_current_interface_id,
164 p_table_name,
165 p_id_column,
166 x_condition,
167 p_derived_value_expression);
168
169 -- In the end, we require that the ID column not be null
170 -- if the code column was not null.
171 if( p_id_required AND request_matches_condition (p_current_rowid,
172 p_current_interface_id,
173 p_table_name,
174 p_code_column || ' is not null and ' ||
175 p_id_column || ' is null')) then
176 record_invalid_column_error(p_current_interface_id, p_code_column);
177 end if ;
178
179 end derive_id_from_code;
180
181 procedure derive_code_from_id(p_current_rowid in rowid,
182 p_current_interface_id in number,
183 p_table_name in varchar2,
184 p_id_column in varchar2,
185 p_code_column in varchar2,
186 p_derived_value_expression in varchar2,
187 p_id_required in boolean default true) is
188 x_condition varchar2(2000);
189 begin
190 /* not required
191 -- if both the code and id filled in, we ignore the code column.
192 warn_redundant_column(p_current_rowid,
193 p_current_interface_id,
194 p_table_name,
195 p_id_column,
196 p_code_column);
197 */
198
199 x_condition := p_id_column || ' is not null';
200
201
202 default_if_null(p_current_rowid,
203 p_current_interface_id,
204 p_table_name,
205 p_code_column,
206 x_condition,
207 p_derived_value_expression);
208 /* not required
209 -- In the end, we require that the ID column not be null
210 -- if the code column was not null.
211 if( p_id_required AND request_matches_condition (p_current_rowid,
212 p_current_interface_id,
213 p_table_name,
214 p_code_column || ' is not null and ' ||
215 p_id_column || ' is null')) then
216 record_invalid_column_error(p_current_interface_id, p_code_column);
217 end if ;
218 */
219
220 end derive_code_from_id;
221
222 procedure default_if_null(p_current_rowid in rowid,
223 p_interface_id in number,
224 p_table_name in varchar2,
225 p_column in varchar2,
226 p_condition in varchar2,
227 p_default_value_expression in varchar2) is
228 x_cursor_id integer ;
229 x_dummy integer ;
230 x_statement varchar2(2000);
231 begin
232 -- Bug 4247057 begin
233
234 x_statement := 'update :l_table set :l_column = :l_default where rowid = :x_row_id and :l_column is null and :l_condition' ;
235
236
237 -- Bug 4247057 end
238 x_cursor_id := dbms_sql.open_cursor ;
239 dbms_sql.parse(x_cursor_id, x_statement, dbms_sql.native) ;
240 dbms_sql.bind_variable_rowid(x_cursor_id, ':x_row_id', p_current_rowid) ;
241 -- Bug 4247057 begin
242 dbms_sql.bind_variable(x_cursor_id, ':l_table', p_table_name);
243 dbms_sql.bind_variable(x_cursor_id, ':l_column', p_column);
244 dbms_sql.bind_variable(x_cursor_id, ':l_default', replace(p_default_value_expression, ' ', ' '));
245 dbms_sql.bind_variable(x_cursor_id, ':l_condition', replace(p_condition, ' ', ' '));
246 -- Bug 4247057 end
247
248 x_dummy := dbms_sql.execute(x_cursor_id) ;
249 dbms_sql.close_cursor(x_cursor_id) ;
250
251 exception when others then
252 record_error(p_interface_id,
253 'EAM_INT_UTILS: ORA-' || -sqlcode || ' : ' || x_statement,
254 FALSE);
255 abort_request;
256 end default_if_null;
257
258
259 function request_matches_condition(p_current_rowid in rowid,
260 p_interface_id in number,
261 p_table_name in varchar2,
262 p_where_clause in varchar2)
263 return boolean is
264 --4247057 begin
265 x_statement varchar2(2000) := 'select 1 from :l_table where rowid = :x_row_id and :l_where';
266 --4247057 end
267
268 x_cursor_id integer;
269 n_rows_fetched integer;
270 begin
271 x_cursor_id := dbms_sql.open_cursor;
272 dbms_sql.parse(x_cursor_id, x_statement, dbms_sql.native);
273 dbms_sql.bind_variable_rowid(x_cursor_id, ':x_row_id', p_current_rowid);
274
275 --4247057 begin
276 dbms_sql.bind_variable(x_cursor_id, ':l_table', p_table_name);
277 dbms_sql.bind_variable(x_cursor_id, ':l_where', replace(p_where_clause, ' ', ' '));
278 --4247057 end
279
280 n_rows_fetched := dbms_sql.execute_and_fetch(x_cursor_id);
281 dbms_sql.close_cursor(x_cursor_id);
282
283 return (n_rows_fetched > 0);
284
285 exception when others then
286 record_error(p_interface_id,
287 'EAM_INT_UTILS: ORA-' || -sqlcode || ' : ' || x_statement,
288 FALSE);
289 abort_request;
290 return false; -- not reached here
291 end request_matches_condition;
292
293
294 procedure record_ignored_column_warning(p_interface_id in number,
295 p_column_name in varchar2) is
296 begin
297 FND_Message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
298 FND_Message.set_token('COLUMN', p_column_name, false);
299 record_error(p_interface_id,
300 FND_Message.get,
301 true);
302 end record_ignored_column_warning;
303
304
305 procedure record_invalid_column_error(p_interface_id in number,
306 p_column_name in varchar2) is
307 begin
308 FND_Message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
309 FND_Message.set_token('COLUMN', p_column_name, false);
310 record_error(p_interface_id,
311 FND_Message.get,
312 FALSE);
313 end record_invalid_column_error;
314
315
316 procedure record_error(p_interface_id in number,
317 p_text in varchar2,
318 p_warning_only in boolean) is
319 error_type number;
320 begin
321 error_type := 1;
322 if ( p_warning_only ) then
323 error_type := 2;
324 end if;
325
326 add_error(p_interface_id, p_text, error_type);
327 end record_error;
328
329
330 END eam_int_utils;