1 PACKAGE WSH_XC_UTIL AUTHID CURRENT_USER as
2 /* $Header: WSHXCUTS.pls 120.4.12010000.2 2009/02/09 12:04:22 skanduku 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 --Bug 8205117 : Adding the parameters Custom_Mode and Upload Mode to the API Load_Row
258 p_custom_mode in varchar2 default null,
259 p_upload_mode in varchar2 default null
260
261 );
262
263 -- ---------------------------------------------------------------------
264 -- procedure: Translate_Row
265 -- description: called by the generic loader to translate exception definition
266 -- This procedure should be called only by generic loader
267 -- no one else should call this procedure.
268 -- --------------------------------------------------------------------
269
270 procedure Translate_Row (
271 p_exception_definition_id in number,
272 p_exception_name in VARCHAR2,
273 p_description in VARCHAR2,
274 p_owner in VARCHAR2
275 );
276
277
278
279 -- ---------------------------------------------------------------------
280 -- procedure: delete_xc_def_form
281 -- description: called by the form to delete exception definition
282 -- This procedure should be called only by form WSHFXCDF.fmb,
283 -- no one else should call this procedure.
284 -- ---------------------------------------------------------------------
285 procedure delete_xc_def_form (
286 p_exception_definition_id in NUMBER
287 );
288
289
290
291 -- ---------------------------------------------------------------------
292 -- procedure: add_language
293 -- description: restore data intergrity to a corrupted
294 -- base/translation pair
295 --
296 -- ---------------------------------------------------------------------
297 procedure add_language;
298
299 -- ---------------------------------------------------------------------
300 -- function: Get_Lookup_Meaning
301 -- description: called by the view to populate Lookup Meaning for
302 -- EXCEPTION_BEHAVIOR and LOGGING_ENTITY
303 --
304 -- ---------------------------------------------------------------------
305 function Get_Lookup_Meaning (
306 p_lookup_code in VARCHAR2,
307 p_lookup_type in VARCHAR2
308 ) return VARCHAR2 ;
309
310
311 -- --------------------------------------------------------------------------
312 -- Procedure: Purge
313 -- Description: This procedure will purge the exception data based on the
314 -- given input criteria
315 -- --------------------------------------------------------------------------
316
317 PROCEDURE Purge
318 (p_api_version IN NUMBER,
319 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
320 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
321 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
322 x_return_status OUT NOCOPY VARCHAR2,
323 x_msg_count OUT NOCOPY NUMBER,
324 x_msg_data OUT NOCOPY VARCHAR2,
325 p_request_id IN NUMBER DEFAULT NULL,
326 p_exception_name IN VARCHAR2 DEFAULT NULL,
327 p_logging_entity IN VARCHAR2 DEFAULT NULL,
328 p_exception_location_id IN NUMBER DEFAULT NULL,
329 p_logged_at_location_id IN NUMBER DEFAULT NULL,
330 p_inventory_org_id IN NUMBER DEFAULT NULL ,
331 p_exception_type IN VARCHAR2 DEFAULT NULL,
332 p_severity IN VARCHAR2 DEFAULT NULL,
333 p_status IN VARCHAR2 DEFAULT NULL,
334 p_arrival_date_from IN DATE DEFAULT NULL,
335 p_arrival_date_to IN DATE DEFAULT NULL,
336 p_departure_date_from IN DATE DEFAULT NULL,
337 p_departure_date_to IN DATE DEFAULT NULL,
338 p_creation_date_from IN DATE DEFAULT NULL,
339 p_creation_date_to IN DATE DEFAULT NULL,
340 p_data_older_no_of_days IN NUMBER DEFAULT NULL,
341 x_no_of_recs_purged OUT NOCOPY NUMBER,
342 p_delivery_id IN NUMBER DEFAULT NULL,
343 p_trip_id IN NUMBER DEFAULT NULL,
344 p_trip_stop_id IN NUMBER DEFAULT NULL,
345 p_delivery_detail_id IN NUMBER DEFAULT NULL,
346 p_delivery_contents IN VARCHAR2 DEFAULT 'Y',
347 p_action IN VARCHAR2 DEFAULT NULL
348 );
349
350
351 TYPE XC_REC_TYPE IS RECORD
352 (entity_name VARCHAR2(30),
353 entity_id NUMBER,
354 exception_behavior VARCHAR2(30)
355 );
356
357 TYPE XC_TAB_TYPE IS TABLE OF XC_REC_TYPE INDEX BY BINARY_INTEGER;
358
359 -- -------------------------------------------------------------------------------
360 -- Start of comments
361 -- API name : Check_Exceptions
362 -- Type : Public
363 -- Function : This procedure takes input as Entity Name and Entity Id
364 -- and finds the maximum severity exception logged against it.
365 -- Only Error and Warning Exceptions are considered, Information Only
366 -- are not considered.
367 -- If p_consider_content is set to 'Y', then the API also looks
368 -- at the contents of the Entity and checks for the maximum severity
369 -- against each child entity. This is drilled to lowest child entity.
370 -- The API returns a PL/SQL table of records with Entity Name, Entity ID
371 -- Exception Behavior. The table is populated with the Top Most entity
372 -- followed by its child entities (if exceptions exist against them) in
373 -- a hierarchial tree structure.
374 -- Valid Values for p_logging_entity_name : LINE, CONTAINER, DELIVERY,
375 -- TRIP, STOP
376 -- End of comments
377 -- --------------------------------------------------------------------------------
378
379 PROCEDURE Check_Exceptions (
380 -- Standard parameters
381 p_api_version IN NUMBER,
382 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
383 x_return_status OUT NOCOPY VARCHAR2,
384 x_msg_count OUT NOCOPY NUMBER,
385 x_msg_data OUT NOCOPY VARCHAR2,
386
387 -- program specific parameters
388 p_logging_entity_id IN NUMBER,
389 p_logging_entity_name IN VARCHAR2,
390 p_consider_content IN VARCHAR2 DEFAULT 'Y',
391
392 -- program specific out parameters
393 x_exceptions_tab IN OUT NOCOPY XC_TAB_TYPE,
394 p_caller IN VARCHAR2 DEFAULT NULL
395 );
396
397
398 -- -------------------------------------------------------------------------------
399 -- Start of comments
400 -- API name : Close_Exceptions
401 -- Type : Public
402 -- Function : This procedure takes input as Entity Name and Entity Id
403 -- and closes all exceptions logged against it.
404 -- If p_consider_content is set to 'Y', then the API also looks
405 -- at the contents of the Entity and closes all exceptions for the
406 -- child entities. This is drilled to lowest child entity.
407 -- This API should be called ONLY if Check_Exceptions is called before
408 -- it. This is because this API assumes all Error Exceptions are Resolved
409 -- prior to this API call and closes OPEN/NO_ACTION_REQUIRED exceptions
410 -- unless they are Information Only (FP bug 4370532)
411 -- Valid Values for p_logging_entity_name : LINE, CONTAINER, DELIVERY,
412 -- TRIP, STOP
413 -- End of comments
414 -- --------------------------------------------------------------------------------
415
416 PROCEDURE Close_Exceptions (
417 -- Standard parameters
418 p_api_version IN NUMBER,
419 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
420 x_return_status OUT NOCOPY VARCHAR2,
421 x_msg_count OUT NOCOPY NUMBER,
422 x_msg_data OUT NOCOPY VARCHAR2,
423
424 -- program specific parameters
425 p_logging_entity_id IN NUMBER,
426 p_logging_entity_name IN VARCHAR2,
427 p_consider_content IN VARCHAR2,
428 p_caller IN VARCHAR2 DEFAULT NULL
429 );
430
431 --OTM R12
432 PROCEDURE LOG_OTM_EXCEPTION(
433 p_delivery_info_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
434 p_new_interface_flag_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
435 x_return_status OUT NOCOPY VARCHAR2);
436
437 PROCEDURE GET_OTM_DELIVERY_EXCEPTION (
438 p_delivery_id IN WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE,
439 x_exception_name OUT NOCOPY WSH_EXCEPTIONS.EXCEPTION_NAME%TYPE,
440 x_severity OUT NOCOPY WSH_EXCEPTIONS.SEVERITY%TYPE,
441 x_return_status OUT NOCOPY VARCHAR2);
442 --
443
444 END WSH_XC_UTIL;
445
[Home] [Help]