1 PACKAGE WSH_XC_UTIL as
2 /* $Header: WSHXCUTS.pls 120.2.12000000.3 2007/04/27 01:09:20 rvarghes ship $ */
3
4 -- --------------------------------------------------------------------------
5 -- Procedure: Log_Exception
6 -- Description: This procedure will create a new exception record or allow
7 -- update to an existing exception record. Update is allowed
8 -- only for the fields which are NULL in the Exception record.
9 -- The update function is mostly useful to add exception name to
10 -- an open exception or to add missing attributes.
11 -- BUG#: 1549665 hwahdani - added a new parameter p_request_id
12 -- BUG#: 1900149 HW added opm_parameters to log_excpetion
13 -- p_sublot_number, p_unit_of_measure2 and p_quantity2
14 -- BUG#: 1729516 Added BATCH_ID for P.Release Online Process in log_exception
15 -- --------------------------------------------------------------------------
16
17 PROCEDURE log_exception
18 (p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
20 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
21 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 x_exception_id IN OUT NOCOPY NUMBER,
26
27 -- exception_location_id is a required field, assign it to
28 -- FND_API.G_MISS_NUM for insert, NULL for update
29 p_exception_location_id IN NUMBER,
30
31 -- logged_at_location_id is a required field, assign it to
32 -- FND_API.G_MISS_NUM for insert, NULL for update
33 p_logged_at_location_id IN NUMBER,
34
35 p_logging_entity IN VARCHAR2 DEFAULT NULL,
36 p_logging_entity_id IN NUMBER DEFAULT NULL,
37 p_exception_name IN VARCHAR2 DEFAULT NULL,
38 p_message IN VARCHAR2 DEFAULT NULL,
39 p_severity IN VARCHAR2 DEFAULT NULL,
40 p_manually_logged IN VARCHAR2 DEFAULT NULL,
41 p_exception_handling IN VARCHAR2 DEFAULT NULL,
42 p_trip_id IN NUMBER DEFAULT NULL,
43 p_trip_name IN VARCHAR2 DEFAULT NULL,
44 p_trip_stop_id IN NUMBER DEFAULT NULL,
45 p_delivery_id IN NUMBER DEFAULT NULL,
46 p_delivery_name IN VARCHAR2 DEFAULT NULL,
47 p_delivery_detail_id IN NUMBER DEFAULT NULL,
48 p_delivery_assignment_id IN NUMBER DEFAULT NULL,
49 p_container_name IN VARCHAR2 DEFAULT NULL,
50 p_inventory_item_id IN NUMBER DEFAULT NULL,
51 p_lot_number IN VARCHAR2 DEFAULT NULL,
52 -- HW BUG#:1900149 OPM added p_sublot_number
53 -- HW OPMCONV. No need for sublot anymore
54 -- p_sublot_number IN VARCHAR2 DEFAULT NULL,
55 p_revision IN VARCHAR2 DEFAULT NULL,
56 p_serial_number IN VARCHAR2 DEFAULT NULL,
57 p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
58 p_quantity IN NUMBER DEFAULT NULL,
59 -- HW BUG#:1900149 OPM added unit_of_measure2 and quantity2
60 p_unit_of_measure2 IN VARCHAR2 DEFAULT NULL,
61 p_quantity2 IN NUMBER DEFAULT NULL,
62 p_subinventory IN VARCHAR2 DEFAULT NULL,
63 p_locator_id IN NUMBER DEFAULT NULL,
64 p_arrival_date IN DATE DEFAULT NULL,
65 p_departure_date IN DATE DEFAULT NULL,
66 p_error_message IN VARCHAR2 DEFAULT NULL,
67 p_attribute_category IN VARCHAR2 DEFAULT NULL,
68 p_attribute1 IN VARCHAR2 DEFAULT NULL,
69 p_attribute2 IN VARCHAR2 DEFAULT NULL,
70 p_attribute3 IN VARCHAR2 DEFAULT NULL,
71 p_attribute4 IN VARCHAR2 DEFAULT NULL,
72 p_attribute5 IN VARCHAR2 DEFAULT NULL,
73 p_attribute6 IN VARCHAR2 DEFAULT NULL,
74 p_attribute7 IN VARCHAR2 DEFAULT NULL,
75 p_attribute8 IN VARCHAR2 DEFAULT NULL,
76 p_attribute9 IN VARCHAR2 DEFAULT NULL,
77 p_attribute10 IN VARCHAR2 DEFAULT NULL,
78 p_attribute11 IN VARCHAR2 DEFAULT NULL,
79 p_attribute12 IN VARCHAR2 DEFAULT NULL,
80 p_attribute13 IN VARCHAR2 DEFAULT NULL,
81 p_attribute14 IN VARCHAR2 DEFAULT NULL,
82 p_attribute15 IN VARCHAR2 DEFAULT NULL,
83 p_request_id IN NUMBER DEFAULT NULL,
84 p_batch_id IN NUMBER DEFAULT NULL,
85 p_creation_date IN DATE DEFAULT NULL,
86 p_created_by IN NUMBER DEFAULT NULL,
87 p_last_update_date IN DATE DEFAULT NULL,
88 p_last_updated_by IN NUMBER DEFAULT NULL,
89 p_last_update_login IN NUMBER DEFAULT NULL,
90 p_program_application_id IN NUMBER DEFAULT NULL,
91 p_program_id IN NUMBER DEFAULT NULL,
92 p_program_update_date IN DATE DEFAULT NULL,
93 p_status IN VARCHAR2 DEFAULT NULL,
94 p_action IN VARCHAR2 DEFAULT NULL
95 );
96
97 -- --------------------------------------------------------------------------
98 -- Procedure: Change_ Status
99 -- Description: If the p_old_status matches the current exception status,
100
101 -- --------------------------------------------------------------------------
102 -- Procedure: Change_ Status
103 -- Description: If the p_old_status matches the current exception status,
104 -- this procedure will change the status in two ways:
105 -- 1)if p_set_default_status = FND_API.G_TRUE (i.e. 'T'),
106 -- then it sets the exception to default status
107 -- 2)if p_set_default_status is missing, it sets the
108 -- exception to x_new_status
109 --
110 -- --------------------------------------------------------------------------
111
112
113 PROCEDURE change_status
114 (p_api_version IN NUMBER,
115 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
116 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
117 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_msg_count OUT NOCOPY NUMBER,
120 x_msg_data OUT NOCOPY VARCHAR2,
121 p_exception_id IN NUMBER,
122 p_old_status IN VARCHAR2,
123 p_set_default_status IN VARCHAR2 DEFAULT FND_API.G_FALSE,
124 x_new_status IN OUT NOCOPY VARCHAR2
125 );
126
127
128 -- ---------------------------------------------------------------------
129 -- procedure: insert_xc_def_form
130 -- description: called by the form to insert exception definition
131 -- This procedure should be called only by form WSHFXCDF.fmb,
132 -- no one else should call this procedure.
133 -- ---------------------------------------------------------------------
134
135 procedure insert_xc_def_form (
136 x_exception_definition_id in out NOCOPY NUMBER,
137 p_exception_name in VARCHAR2 DEFAULT null,
138 p_description in VARCHAR2 DEFAULT NULL,
139 p_exception_type in VARCHAR2 DEFAULT NULL,
140 p_default_severity in VARCHAR2 DEFAULT NULL,
141 p_exception_handling in VARCHAR2 DEFAULT NULL,
142 p_workflow_item_type in VARCHAR2 DEFAULT NULL,
143 p_workflow_process in VARCHAR2 DEFAULT NULL,
144 p_initiate_workflow in VARCHAR2 DEFAULT NULL,
145 p_update_allowed in VARCHAR2 DEFAULT NULL,
146 p_enabled in VARCHAR2 DEFAULT 'Y',
147 p_attribute_category in VARCHAR2 DEFAULT NULL,
148 p_attribute1 in VARCHAR2 DEFAULT NULL,
149 p_attribute2 in VARCHAR2 DEFAULT NULL,
150 p_attribute3 in VARCHAR2 DEFAULT NULL,
151 p_attribute4 in VARCHAR2 DEFAULT NULL,
152 p_attribute5 in VARCHAR2 DEFAULT NULL,
153 p_attribute6 in VARCHAR2 DEFAULT NULL,
154 p_attribute7 in VARCHAR2 DEFAULT NULL,
155 p_attribute8 in VARCHAR2 DEFAULT NULL,
156 p_attribute9 in VARCHAR2 DEFAULT NULL,
157 p_attribute10 in VARCHAR2 DEFAULT NULL,
158 p_attribute11 in VARCHAR2 DEFAULT NULL,
159 p_attribute12 in VARCHAR2 DEFAULT NULL,
160 p_attribute13 in VARCHAR2 DEFAULT NULL,
161 p_attribute14 in VARCHAR2 DEFAULT NULL,
162 p_attribute15 in VARCHAR2 DEFAULT NULL,
163 p_creation_date in DATE DEFAULT NULL,
164 p_created_by in NUMBER DEFAULT NULL,
165 p_last_update_date in DATE DEFAULT NULL,
166 p_last_updated_by in NUMBER DEFAULT NULL,
167 p_last_update_login in NUMBER DEFAULT NULL
168 );
169
170
171
172 -- ---------------------------------------------------------------------
173 -- procedure: update_xc_def_form
174 -- description: called by the form to update exception definition
175 -- This procedure should be called only by form WSHFXCDF.fmb,
176 -- no one else should call this procedure.
177 -- ---------------------------------------------------------------------
178
179 procedure update_xc_def_form (
180 p_exception_definition_id in NUMBER,
181 p_exception_name in VARCHAR2,
182 p_description in VARCHAR2 DEFAULT NULL,
183 p_exception_type in VARCHAR2 DEFAULT NULL,
184 p_default_severity in VARCHAR2 DEFAULT NULL,
185 p_exception_handling in VARCHAR2 DEFAULT NULL,
186 p_workflow_item_type in VARCHAR2 DEFAULT NULL,
187 p_workflow_process in VARCHAR2 DEFAULT NULL,
188 p_initiate_workflow in VARCHAR2 DEFAULT NULL,
189 p_update_allowed in VARCHAR2 DEFAULT NULL,
190 p_enabled in VARCHAR2 DEFAULT 'Y',
191 p_attribute_category in VARCHAR2 DEFAULT NULL,
192 p_attribute1 in VARCHAR2 DEFAULT NULL,
193 p_attribute2 in VARCHAR2 DEFAULT NULL,
194 p_attribute3 in VARCHAR2 DEFAULT NULL,
195 p_attribute4 in VARCHAR2 DEFAULT NULL,
196 p_attribute5 in VARCHAR2 DEFAULT NULL,
197 p_attribute6 in VARCHAR2 DEFAULT NULL,
198 p_attribute7 in VARCHAR2 DEFAULT NULL,
199 p_attribute8 in VARCHAR2 DEFAULT NULL,
200 p_attribute9 in VARCHAR2 DEFAULT NULL,
201 p_attribute10 in VARCHAR2 DEFAULT NULL,
202 p_attribute11 in VARCHAR2 DEFAULT NULL,
203 p_attribute12 in VARCHAR2 DEFAULT NULL,
204 p_attribute13 in VARCHAR2 DEFAULT NULL,
205 p_attribute14 in VARCHAR2 DEFAULT NULL,
206 p_attribute15 in VARCHAR2 DEFAULT NULL,
207 p_creation_date in DATE DEFAULT NULL,
208 p_created_by in NUMBER DEFAULT NULL,
209 p_last_update_date in DATE DEFAULT NULL,
210 p_last_updated_by in NUMBER DEFAULT NULL,
211 p_last_update_login in NUMBER DEFAULT NULL,
212 p_caller IN VARCHAR2 DEFAULT NULL --- 5986504
213 );
214
215
216 -- ---------------------------------------------------------------------
217 -- procedure: Load_Row
218 -- description: called by the generic loader to upload exception definition
219 -- This procedure should be called only by generic loader
220 -- no one else should call this procedure.
221 -- ---------------------------------------------------------------------
222 procedure Load_Row (
223 p_language in VARCHAR2,
224 p_source_lang in VARCHAR2,
225 p_exception_definition_id in NUMBER,
226 p_exception_name in VARCHAR2,
227 p_description in VARCHAR2,
228 p_exception_type in VARCHAR2,
229 p_default_severity in VARCHAR2,
230 p_exception_handling in VARCHAR2,
231 p_workflow_item_type in VARCHAR2 DEFAULT NULL,
232 p_workflow_process in VARCHAR2 DEFAULT NULL,
233 p_initiate_workflow in VARCHAR2 DEFAULT NULL,
234 p_update_allowed in VARCHAR2 DEFAULT NULL,
235 p_enabled in VARCHAR2 DEFAULT NULL,
236 p_attribute_category in VARCHAR2 DEFAULT NULL,
237 p_attribute1 in VARCHAR2 DEFAULT NULL,
238 p_attribute2 in VARCHAR2 DEFAULT NULL,
239 p_attribute3 in VARCHAR2 DEFAULT NULL,
240 p_attribute4 in VARCHAR2 DEFAULT NULL,
241 p_attribute5 in VARCHAR2 DEFAULT NULL,
242 p_attribute6 in VARCHAR2 DEFAULT NULL,
243 p_attribute7 in VARCHAR2 DEFAULT NULL,
244 p_attribute8 in VARCHAR2 DEFAULT NULL,
245 p_attribute9 in VARCHAR2 DEFAULT NULL,
246 p_attribute10 in VARCHAR2 DEFAULT NULL,
247 p_attribute11 in VARCHAR2 DEFAULT NULL,
248 p_attribute12 in VARCHAR2 DEFAULT NULL,
249 p_attribute13 in VARCHAR2 DEFAULT NULL,
250 p_attribute14 in VARCHAR2 DEFAULT NULL,
251 p_attribute15 in VARCHAR2 DEFAULT NULL,
252 p_creation_date in DATE DEFAULT NULL,
253 p_created_by in NUMBER DEFAULT NULL,
254 p_last_update_date in DATE DEFAULT NULL,
255 p_last_updated_by in NUMBER DEFAULT NULL,
256 p_last_update_login in NUMBER DEFAULT NULL
257 );
258
259 -- ---------------------------------------------------------------------
260 -- procedure: Translate_Row
261 -- description: called by the generic loader to translate exception definition
262 -- This procedure should be called only by generic loader
263 -- no one else should call this procedure.
264 -- --------------------------------------------------------------------
265
266 procedure Translate_Row (
267 p_exception_definition_id in number,
268 p_exception_name in VARCHAR2,
269 p_description in VARCHAR2,
270 p_owner in VARCHAR2
271 );
272
273
274
275 -- ---------------------------------------------------------------------
276 -- procedure: delete_xc_def_form
277 -- description: called by the form to delete exception definition
278 -- This procedure should be called only by form WSHFXCDF.fmb,
279 -- no one else should call this procedure.
280 -- ---------------------------------------------------------------------
281 procedure delete_xc_def_form (
282 p_exception_definition_id in NUMBER
283 );
284
285
286
287 -- ---------------------------------------------------------------------
288 -- procedure: add_language
289 -- description: restore data intergrity to a corrupted
290 -- base/translation pair
291 --
292 -- ---------------------------------------------------------------------
293 procedure add_language;
294
295 -- ---------------------------------------------------------------------
296 -- function: Get_Lookup_Meaning
297 -- description: called by the view to populate Lookup Meaning for
298 -- EXCEPTION_BEHAVIOR and LOGGING_ENTITY
299 --
300 -- ---------------------------------------------------------------------
301 function Get_Lookup_Meaning (
302 p_lookup_code in VARCHAR2,
303 p_lookup_type in VARCHAR2
304 ) return VARCHAR2 ;
305
306
307 -- --------------------------------------------------------------------------
308 -- Procedure: Purge
309 -- Description: This procedure will purge the exception data based on the
310 -- given input criteria
311 -- --------------------------------------------------------------------------
312
313 PROCEDURE Purge
314 (p_api_version IN NUMBER,
315 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
316 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
317 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2,
321 p_request_id IN NUMBER DEFAULT NULL,
322 p_exception_name IN VARCHAR2 DEFAULT NULL,
323 p_logging_entity IN VARCHAR2 DEFAULT NULL,
324 p_exception_location_id IN NUMBER DEFAULT NULL,
325 p_logged_at_location_id IN NUMBER DEFAULT NULL,
326 p_inventory_org_id IN NUMBER DEFAULT NULL ,
327 p_exception_type IN VARCHAR2 DEFAULT NULL,
328 p_severity IN VARCHAR2 DEFAULT NULL,
329 p_status IN VARCHAR2 DEFAULT NULL,
330 p_arrival_date_from IN DATE DEFAULT NULL,
331 p_arrival_date_to IN DATE DEFAULT NULL,
332 p_departure_date_from IN DATE DEFAULT NULL,
333 p_departure_date_to IN DATE DEFAULT NULL,
334 p_creation_date_from IN DATE DEFAULT NULL,
335 p_creation_date_to IN DATE DEFAULT NULL,
336 p_data_older_no_of_days IN NUMBER DEFAULT NULL,
337 x_no_of_recs_purged OUT NOCOPY NUMBER,
338 p_delivery_id IN NUMBER DEFAULT NULL,
339 p_trip_id IN NUMBER DEFAULT NULL,
340 p_trip_stop_id IN NUMBER DEFAULT NULL,
341 p_delivery_detail_id IN NUMBER DEFAULT NULL,
342 p_delivery_contents IN VARCHAR2 DEFAULT 'Y',
343 p_action IN VARCHAR2 DEFAULT NULL
344 );
345
346
347 TYPE XC_REC_TYPE IS RECORD
348 (entity_name VARCHAR2(30),
349 entity_id NUMBER,
350 exception_behavior VARCHAR2(30)
351 );
352
353 TYPE XC_TAB_TYPE IS TABLE OF XC_REC_TYPE INDEX BY BINARY_INTEGER;
354
355 -- -------------------------------------------------------------------------------
356 -- Start of comments
357 -- API name : Check_Exceptions
358 -- Type : Public
359 -- Function : This procedure takes input as Entity Name and Entity Id
360 -- and finds the maximum severity exception logged against it.
361 -- Only Error and Warning Exceptions are considered, Information Only
362 -- are not considered.
363 -- If p_consider_content is set to 'Y', then the API also looks
364 -- at the contents of the Entity and checks for the maximum severity
365 -- against each child entity. This is drilled to lowest child entity.
366 -- The API returns a PL/SQL table of records with Entity Name, Entity ID
367 -- Exception Behavior. The table is populated with the Top Most entity
368 -- followed by its child entities (if exceptions exist against them) in
369 -- a hierarchial tree structure.
370 -- Valid Values for p_logging_entity_name : LINE, CONTAINER, DELIVERY,
371 -- TRIP, STOP
372 -- End of comments
373 -- --------------------------------------------------------------------------------
374
375 PROCEDURE Check_Exceptions (
376 -- Standard parameters
377 p_api_version IN NUMBER,
378 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2,
382
383 -- program specific parameters
384 p_logging_entity_id IN NUMBER,
385 p_logging_entity_name IN VARCHAR2,
386 p_consider_content IN VARCHAR2 DEFAULT 'Y',
387
388 -- program specific out parameters
389 x_exceptions_tab IN OUT NOCOPY XC_TAB_TYPE,
390 p_caller IN VARCHAR2 DEFAULT NULL
391 );
392
393
394 -- -------------------------------------------------------------------------------
395 -- Start of comments
396 -- API name : Close_Exceptions
397 -- Type : Public
398 -- Function : This procedure takes input as Entity Name and Entity Id
399 -- and closes all exceptions logged against it.
400 -- If p_consider_content is set to 'Y', then the API also looks
401 -- at the contents of the Entity and closes all exceptions for the
402 -- child entities. This is drilled to lowest child entity.
403 -- This API should be called ONLY if Check_Exceptions is called before
404 -- it. This is because this API assumes all Error Exceptions are Resolved
405 -- prior to this API call and closes OPEN/NO_ACTION_REQUIRED exceptions
406 -- unless they are Information Only (FP bug 4370532)
407 -- Valid Values for p_logging_entity_name : LINE, CONTAINER, DELIVERY,
408 -- TRIP, STOP
409 -- End of comments
410 -- --------------------------------------------------------------------------------
411
412 PROCEDURE Close_Exceptions (
413 -- Standard parameters
414 p_api_version IN NUMBER,
415 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
416 x_return_status OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER,
418 x_msg_data OUT NOCOPY VARCHAR2,
419
420 -- program specific parameters
421 p_logging_entity_id IN NUMBER,
422 p_logging_entity_name IN VARCHAR2,
423 p_consider_content IN VARCHAR2,
424 p_caller IN VARCHAR2 DEFAULT NULL
425 );
426
427 --OTM R12
428 PROCEDURE LOG_OTM_EXCEPTION(
429 p_delivery_info_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
430 p_new_interface_flag_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
431 x_return_status OUT NOCOPY VARCHAR2);
432
433 PROCEDURE GET_OTM_DELIVERY_EXCEPTION (
434 p_delivery_id IN WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE,
435 x_exception_name OUT NOCOPY WSH_EXCEPTIONS.EXCEPTION_NAME%TYPE,
436 x_severity OUT NOCOPY WSH_EXCEPTIONS.SEVERITY%TYPE,
437 x_return_status OUT NOCOPY VARCHAR2);
438 --
439
440 END WSH_XC_UTIL;
441
[Home] [Help]