[Home] [Help]
PACKAGE BODY: APPS.WIP_JSI_UTILS
Source
1 package body WIP_JSI_Utils as
2 /* $Header: wipjsiub.pls 115.16 2002/11/29 10:29:34 rmahidha ship $ */
3
4
5 procedure
6 begin_processing_request (
7 p_interface_id in number,
8 p_validation_level in number
9 )
10 is
11 begin
12
13 validation_level := p_validation_level ;
14 current_interface_id := p_interface_id;
15 any_current_request := false ;
16 any_nonwarning_errors := false ;
17
18 savepoint wip_jsi_request_start ;
19
20 open matching_request(p_interface_id) ;
21 fetch matching_request into current_rowid ;
22 if (not matching_request%found) then
23 abort_request ;
24 end if ;
25
26 any_current_request := true ;
27
28 end begin_processing_request ;
29
30
31
32 procedure
33 end_processing_request
34 is
35
36 n_errors binary_integer ;
37 error_no binary_integer ;
38 x_final_process_status number ;
39 x_group_id number;
40 x_header_id number;
41
42 begin
43
44 if (not any_current_request) then
45 close matching_request ;
46 return ;
47 end if ;
48
49 if (any_nonwarning_errors) then
50 rollback to savepoint wip_jsi_request_start ;
51 x_final_process_status := WIP_CONSTANTS.ERROR ;
52
53 select group_id,header_id
54 into x_group_id, x_header_id
55 from wip_job_schedule_interface
56 where rowid = current_rowid;
57
58 -- Change process_status of detail records to error.
59
60 WIP_JDI_Utils.Change_Status_Error(x_group_id,x_header_id,null,null);
61
62 else
63 x_final_process_status := WIP_CONSTANTS.COMPLETED ;
64 end if ;
65
66 -- Use new utilities to load errors into interface errors table.
67
68 WIP_INTERFACE_ERR_Utils.load_errors;
69
70 update wip_job_schedule_interface
71 set
72 process_phase = current_process_phase,
73 process_status = x_final_process_status,
74 last_update_date = sysdate
75 where current of matching_request ;
76
77 close matching_request ;
78 any_current_request := false ;
79
80 end end_processing_request ;
81
82
83
84 procedure
85 abort_request
86 is
87 begin
88
89 -- rollback to savepoint wip_jsi_request_start ;
90
91 raise_application_error (
92 -20239, -- WIP_JSI_EXCEPTION,
93 'Job/Schedule Interface Request Processing Aborted'
94 ) ;
95
96 end abort_request ;
97
98
99
100 procedure
101 record_error_text (
102 p_text in varchar2,
103 p_warning_only in boolean
104 )
105 is
106
107 error_record WIP_INTERFACE_ERR_Utils.request_error ;
108 error_type number ;
109
110 begin
111
112 if (nvl(p_warning_only,false)) then
113 error_type := 2 ;
114 else
115 error_type := 1 ;
116 any_nonwarning_errors := true ;
117 end if ;
118
119 -- Use new error handler to add error into PL/SQL table.
120
121 WIP_INTERFACE_ERR_Utils.add_error(current_interface_id,p_text, error_type);
122
123 end record_error_text ;
124
125
126
127 procedure
128 record_current_error(p_warning_only in boolean)
129 is
130 begin
131
132 record_error_text(FND_Message.get, nvl(p_warning_only,false)) ;
133
134 end record_current_error ;
135
136
137
138 procedure
139 record_error(p_message_name in varchar2, p_warning_only in boolean)
140 is
141 begin
142
143 FND_Message.set_name('WIP', p_message_name) ;
144 record_current_error(nvl(p_warning_only,false)) ;
145
146 end record_error ;
147
148
149
150 procedure
151 record_invalid_column_error(p_column_name in varchar2)
152 is
153 begin
154
155 FND_Message.set_name('WIP', 'WIP_ML_FIELD_INVALID') ;
156 FND_Message.set_token('COLUMN', p_column_name, false ) ;
157 record_current_error(p_warning_only => false) ;
158
159 end record_invalid_column_error ;
160
161
162
163 procedure
164 record_ignored_column_warning(p_column_name in varchar2)
165 is
166 begin
167
168 FND_Message.set_name('WIP', 'WIP_ML_FIELD_IGNORED') ;
169 FND_Message.set_token('COLUMN', p_column_name, false) ;
170 record_current_error(p_warning_only => true) ;
171
172 end record_ignored_column_warning ;
173
174
175
176 function
177 request_matches_condition (p_where_clause in varchar2) return boolean
178 is
179
180 x_statement varchar2(2000) :=
181 'select 1 from wip_job_schedule_interface WJSI ' ||
182 'where rowid = :x_row_id and ' ||
183 replace(p_where_clause, ' ', ' ') ;
184
185 x_cursor_id integer ;
186 n_rows_fetched integer ;
187
188 begin
189
190 x_cursor_id := dbms_sql.open_cursor ;
191 dbms_sql.parse(x_cursor_id, x_statement, dbms_sql.native) ;
192 dbms_sql.bind_variable_rowid(x_cursor_id, ':x_row_id', current_rowid) ;
193 n_rows_fetched := dbms_sql.execute_and_fetch(x_cursor_id) ;
194 dbms_sql.close_cursor(x_cursor_id) ;
195
196 return (n_rows_fetched > 0) ;
197
198 exception when others then
199
200 record_error_text(sql_error_prefix || x_statement) ;
201 abort_request ;
202 return false ; -- not reached
203
204 end request_matches_condition ;
205
206
207
208 function
209 sql_error_prefix
210 return varchar2
211 is
212 begin
213 return 'WIP_JSI_Utils : ORA-' || -sqlcode || ' : ' ;
214 end sql_error_prefix ;
215
216
217
218 procedure
219 nonfatal_error_if(p_condition in varchar2, p_message in varchar2)
220 is
221 begin
222 if (request_matches_condition(p_condition)) then
223 record_error(p_message) ;
224 end if ;
225 end nonfatal_error_if ;
226
227
228
229 procedure
230 fatal_error_if(p_condition in varchar2, p_message in varchar2)
231 is
232 begin
233 if (request_matches_condition(p_condition)) then
234 record_error(p_message) ;
235 abort_request ;
236 end if ;
237 end fatal_error_if ;
238
239
240
241 -- Sets p_column to the result of p_default_value_expression
242 -- if p_condition is true.
243 procedure
244 default_if_null (
245 p_column in varchar2,
246 p_condition in varchar2,
247 p_default_value_expression in varchar2
248 )
249 is
250 x_cursor_id integer ;
251 x_dummy integer ;
252 x_statement varchar2(2000);
253
254 begin
255
256 x_statement :=
257 'update wip_job_schedule_interface WJSI ' ||
258 'set ' || p_column || ' = ' ||
259 replace(p_default_value_expression, ' ', ' ') || ' ' ||
260 'where rowid = :x_row_id and ' ||
261 p_column || ' is null and ' ||
262 replace(p_condition, ' ', ' ') ;
263
264
265 x_cursor_id := dbms_sql.open_cursor ;
266 dbms_sql.parse(x_cursor_id, x_statement, dbms_sql.native) ;
267 dbms_sql.bind_variable_rowid(x_cursor_id, ':x_row_id', current_rowid) ;
268 x_dummy := dbms_sql.execute(x_cursor_id) ;
269 dbms_sql.close_cursor(x_cursor_id) ;
270
271 exception when others then
272 record_error_text(sql_error_prefix || x_statement) ;
273 abort_request ;
274
275 end default_if_null ;
276
277
278
279 procedure
280 warn_irrelevant_column (p_column in varchar2, p_load_type_list in varchar2)
281 is
282
283 x_condition varchar2(2000) ;
284
285 begin
286
287 x_condition := p_column || ' is not null' ;
288
289 if (p_load_type_list is not null) then
290 x_condition := x_condition || ' and load_type in ' || p_load_type_list ;
291 end if ;
292
293 if (request_matches_condition(x_condition)) then
294 record_ignored_column_warning(p_column) ;
295 end if ;
296
297 end warn_irrelevant_column ;
298
299
300
301 procedure
302 warn_redundant_column (
303 p_column_being_used in varchar2,
304 p_column_being_ignored in varchar2,
305 p_exception_load_type_list in varchar2
306 )
307 is
308
309 x_condition varchar2(2000) ;
310
311 begin
312
313 x_condition :=
314 p_column_being_used || ' is not null and ' ||
315 p_column_being_ignored || ' is not null' ;
316
317 if (p_exception_load_type_list is not null) then
318 x_condition :=
319 x_condition || ' and load_type not in ' ||
320 p_exception_load_type_list ;
321 end if ;
322
323 if(request_matches_condition(x_condition)) then
324 record_ignored_column_warning(p_column_being_ignored) ;
325 end if ;
326
327 end warn_redundant_column ;
328
329
330
331 procedure
332 derive_id_from_code (
333 p_id_column in varchar2,
334 p_code_column in varchar2,
335 p_derived_value_expression in varchar2,
336 p_exception_load_type_list in varchar2,
337 p_required in boolean default NULL
338 )
339 is
340
341 x_condition varchar2(2000) ;
342
343 begin
344
345 -- If both the code column and the ID column are filled in,
346 -- we will ignore the code column.
347 warn_redundant_column(p_id_column, p_code_column,
348 p_exception_load_type_list) ;
349
350 -- If the ID column is blank but the code column is filled in,
351 -- try to fill in the ID column using the derivation expression
352 -- (which presumably involves the code column).
353 x_condition := p_code_column || ' is not null' ;
354 if (p_exception_load_type_list is not null) then
355 x_condition := x_condition ||
356 ' and load_type not in ' || p_exception_load_type_list ;
357 end if ;
358 default_if_null(p_id_column,
359 x_condition,
360 p_derived_value_expression) ;
361
362 -- In the end, we require that the ID column not be null
363 -- if the code column was not null.
364 if(request_matches_condition (p_code_column || ' is not null and ' ||
365 p_id_column || ' is null') AND nvl(p_required,true))
366 then
367 record_invalid_column_error(p_code_column) ;
368 abort_request ;
369 end if ;
370
371 end derive_id_from_code ;
372
373 end WIP_JSI_Utils ;