1 PACKAGE WIP_UTILITIES AS
2 /* $Header: wiputils.pls 120.5.12010000.2 2008/10/06 16:57:09 hliew ship $ */
3
4 /*=====================================================================+
5 | PROCEDURE
6 | DO_SQL
7 |
8 | PURPOSE
9 | Executes a dynamic SQL statement
10 |
11 | ARGUMENTS
12 | p_sql_stmt String holding sql statement. May be up to 8K long.
13 |
14 | EXCEPTIONS
15 |
16 | NOTES
17 |
18 +=====================================================================*/
19 PROCEDURE DO_SQL(p_sql_stmt in varchar2);
20
21 /*=====================================================================+
22 | Function
23 | is_status_applicable
24 |
25 | Description
26 | Wrapper function for INV_MATERIAL_STATUS_GRP.is_status_applicable, checks if WMS
27 | is installed, before calling the INV code.
28 |
29 | Input Paramters
30 |
31 | p_trx_status_enabled Indicate if the transaction type is status control
32 | Enabled or not
33 | passing 1 for enabled, 2 for disabled
34 | this is optional, passing this value can increase the
35 | the processing speed
36 | p_trx_type_id transaction type id
37 | p_lot_status_enabled Indicate if the item is lot status control
38 | Enabled or not
39 | passing 'Y' for enabled, 'N' for disabled
40 | this is optional, passing this value can increase the
41 | the processing speed
42 | p_serial_status_enabled Indicate if the item is serial status control
43 | Enabled or not
44 | passing 'Y' for enabled, 'N' for disabled
45 | this is optional, passing this value can increase the
46 | the processing speed
47 | p_organization_id organization id the item resides in
48 | p_inventory_item_id given item id we query for
49 | p_sub_code subinventory code
50 | p_locator_id locator id
51 | p_lot_number lot number
52 | p_serial_number serial number
53 | p_object_type this parameter is for performance purpose
54 | must be specified to get the proper function
55 | 'Z' checking zone (subinventory)
56 | 'L' checking locator
57 | 'O' checking lot
58 | 'S' checking serial
59 | 'A' checking all including sub, locator, lot, serial
60 |
61 |
62 |
63 | Return:
64 | 'Y' the given object's status allow the given transaction type or any error occurred
65 | 'N' the given object's status disallow the given transaction type
66 |
67 | Usage:
68 | TO check any object (sub, locator, lot or serial) is applicable or not,
69 | p_trx_type_id, p_organization_id, p_object_type must be specified.
70 | Additionally,
71 | to check subinventory, p_sub_code must be specified;
72 | to check locator, p_locator_id must be specified;
73 | to check lot,p_inventory_item_id, p_lot_number must be specified
74 | to check serial, p_inventory_item_id, p_serial_number must be specified
75 |
76 | p_trx_status_enabled is optional for all checkings
77 | p_lot_status_enabledled is optional for checking lot status,
78 | p_serial_status_enabled is optional for checking serial status
79 | The default value is NULL for all input parameters except p_wms_installed
80 +=====================================================================*/
81
82 Function is_status_applicable( p_trx_status_enabled IN NUMBER:=NULL,
83 p_trx_type_id IN NUMBER:=NULL,
84 p_lot_status_enabled IN VARCHAR2:=NULL,
85 p_serial_status_enabled IN VARCHAR2:=NULL,
86 p_organization_id IN NUMBER:=NULL,
87 p_inventory_item_id IN NUMBER:=NULL,
88 p_sub_code IN VARCHAR2:=NULL,
89 p_locator_id IN NUMBER:=NULL,
90 p_lot_number IN VARCHAR2:=NULL,
91 p_serial_number IN VARCHAR2:=NULL,
92 p_object_type IN VARCHAR2:=NULL)
93 return varchar2;
94
95 /*=====================================================================+
96 | PROCEDURE
97 | print_label_java
98 |
99 | PURPOSE
100 | A wrapper to call the Label Printing capability introduced in H.
101 | This procedure should be used instead of print_label if called from
102 | java. The p_err_msg basically concatenates all error messages on
103 | the stack if an error is returned from WMS.
104 |
105 | ARGUMENTS
106 | p_txn_id an id to either MTI.transaction_header_id or to
107 | MMTT.transaction_header_id depending on p_table_type
108 | p_table_type either 1 (MTI) or 2 (MMTT)
109 | p_ret_status return status returned from INV_LABEL.print_label_wrap
110 | p_err_msg actual error messages from the msg stack
111 | p_business_flow_code business flow code, either 26 (comp) or 33 (flow)
112 |
113 | EXCEPTIONS
114 |
115 | NOTES
116 |
117 +=====================================================================*/
118 procedure print_label_java(p_txn_id IN NUMBER,
119 p_table_type IN NUMBER, -- 1 MTI, 2 MMTT
120 p_ret_status OUT NOCOPY VARCHAR2,
121 p_err_msg OUT NOCOPY VARCHAR2,
122 p_business_flow_code IN NUMBER);
123
124 /*=====================================================================+
125 | PROCEDURE
126 | print_label
127 |
128 | PURPOSE
129 | A wrapper to call the Label Printing capability introduced in H.
130 |
131 | ARGUMENTS
132 | p_txn_id an id to either MTI.transaction_header_id or to
133 | MMTT.transaction_header_id depending on p_table_type
134 | p_table_type either 1 (MTI) or 2 (MMTT)
135 | p_ret_status return status returned from INV_LABEL.print_label_wrap
136 | p_msg_count number of error messages from INV_LABEL.print_label_wrap
137 | p_msg_data actual error messages from INV_LABEL.print_label_wrap
138 | p_label_status returned from INV_LABEL.print_label_wrap
139 | p_business_flow_code business flow code, either 26 (comp) or 33 (flow)
140 |
141 | EXCEPTIONS
142 |
143 | NOTES
144 |
145 +=====================================================================*/
146 procedure print_label(p_txn_id IN NUMBER,
147 p_table_type IN NUMBER, -- 1 MTI 2 MMTT
148 p_ret_status OUT NOCOPY VARCHAR2,
149 p_msg_count OUT NOCOPY NUMBER,
150 p_msg_data OUT NOCOPY VARCHAR2,
151 p_label_status OUT NOCOPY VARCHAR2,
152 p_business_flow_code IN NUMBER);
153
154
155 /*=====================================================================+
156 | PROCEDURE
157 | print_label
158 |
159 | PURPOSE
160 | A wrapper to call the Label Printing capability introduced in H.
161 |
162 | ARGUMENTS
163 | <in arguments> these are passed directly to the inventory call.
164 | p_ret_status return status returned from INV_LABEL.print_label_manual_wrap
165 | p_msg_count number of error messages from INV_LABEL.print_label_manual_wrap
166 | p_msg_data actual error messages from INV_LABEL.print_label_manual_wrap
167 | p_label_status returned from INV_LABEL.print_label_manual_wrap
168 | p_business_flow_code business flow code, either 26 (comp) or 33 (flow)
169 |
170 | EXCEPTIONS
171 |
172 | NOTES
173 | This version is a generic wrapper to the INV_LABEL.print_label_manual_wrap. This
174 | version should be used when printing labels not associated with a transaction in
175 | the inventory interface tables, e.g. from the associate serial numbers form.
176 +=====================================================================*/
177 procedure print_label(p_business_flow_code IN NUMBER DEFAULT NULL,
178 p_label_type_id IN NUMBER DEFAULT NULL,
179 p_organization_id IN NUMBER DEFAULT NULL,
180 p_inventory_item_id IN NUMBER DEFAULT NULL,
181 p_revision IN VARCHAR2 DEFAULT NULL,
182 p_lot_number IN VARCHAR2 DEFAULT NULL,
183 p_fm_serial_number IN VARCHAR2 DEFAULT NULL,
184 p_to_serial_number IN VARCHAR2 DEFAULT NULL,
185 p_lpn_id IN NUMBER DEFAULT NULL,
186 p_subinventory_code IN VARCHAR2 DEFAULT NULL,
187 p_locator_id IN NUMBER DEFAULT NULL,
188 p_delivery_id IN NUMBER DEFAULT NULL,
189 p_quantity IN NUMBER DEFAULT NULL,
190 p_uom IN VARCHAR2 DEFAULT NULL,
191 p_no_of_copies IN NUMBER DEFAULT NULL,
192 p_ret_status OUT NOCOPY VARCHAR2,
193 p_msg_count OUT NOCOPY NUMBER,
194 p_msg_data OUT NOCOPY VARCHAR2,
195 p_label_status OUT NOCOPY VARCHAR2);
196
197 /*=====================================================================+
198 | PROCEDURE
199 | get_message_stack
200 |
201 | PURPOSE
202 | Flattens out the message stack into one string. The messages are separated
203 | by a single space. This procedure should only be used for mobile applications
204 | or to print messages to a log file. Forms should loop through the message stack
205 | and display the messages individually.
206 |
207 | ARGUMENTS
208 | p_msg -- The flattened stack.
209 |
210 | EXCEPTIONS
211 |
212 | NOTES
213 |
214 +=====================================================================*/
215 procedure get_message_stack(p_delete_stack in varchar2 := null,
216 p_separator in varchar2 := null,
217 p_msg OUT NOCOPY VARCHAR2);
218
219 /*=====================================================================+
220 | PROCEDURE
221 | delete_temp_records
222 |
223 | PURPOSE
224 | Deletes all records for the given transaction in the mtl temp tables.
225 | This method is used by both discrete and flow LPN completions, which
226 | insert records into the temp tables for processing but need to remove
227 | them afterwards.
228 |
229 | ARGUMENTS
230 | p_header_id (the transaction_header_id for this transaction)
231 |
232 | EXCEPTIONS
233 |
234 | NOTES
235 |
236 +=====================================================================*/
237 procedure delete_temp_records(p_header_id IN NUMBER);
238 procedure delete_temp_records(p_temp_id IN NUMBER);
239
240
241 /*=====================================================================+
242 | PROCEDURE
243 | update_serial
244 |
245 | PURPOSE
246 | Updates the wip_entity_id, operation_seq_num, and intraoperation_step_type
247 | of the serial number. Currently all three will be updated with the passed
248 | value since the caller generally knows all the values
249 |
250 | ARGUMENTS
251 | p_header_id (the transaction_header_id for this transaction)
252 |
253 | EXCEPTIONS
254 |
255 | NOTES
256 |
257 +=====================================================================*/
258 procedure update_serial(p_serial_number in VARCHAR2,
259 p_inventory_item_id in number,
260 p_organization_id in number,
261 p_wip_entity_id in number,
262 p_line_mark_id in number := null,
263 p_operation_seq_num in number,
264 p_intraoperation_step_type in number,
265 x_return_status OUT NOCOPY VARCHAR2);
266
267
268
269 /*=====================================================================+
270 | PROCEDURE
271 | generate_serials
272 |
273 | PURPOSE
274 | Generate serial numbers.
275 | ARGUMENTS
276 | p_wip_entity_id The value to be stamped on the MSN column wip_entity_id
277 | x_start_serial The first serial generated (alphabetically)
278 | x_end_serial The last serial generated (alphabetically)
279 |
280 | EXCEPTIONS
281 |
282 | NOTES
283 | If there is an existing serial number in between the x_start_serial and
284 | x_end_serial, that number is skipped. For this purpose, the caller should
285 | always query by current_status, group_mark_id, line_mark_id, and if
286 | applicable wip_entity_id. Also, the inventory item attributes to do with
287 | serial number generation should be defined for the item for this procedure
288 | to return successfully.
289 +=====================================================================*/
290
291 procedure generate_serials(p_org_id in NUMBER,
292 p_item_id in NUMBER,
293 p_qty IN NUMBER,
294 p_wip_entity_id IN NUMBER,
295 p_revision in VARCHAR2,
296 p_lot in varchar2,
297 x_start_serial IN OUT NOCOPY VARCHAR2,
298 x_end_serial OUT NOCOPY VARCHAR2,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_err_msg OUT NOCOPY VARCHAR2);
301
302 /*=====================================================================+
303 | FUNCTION
304 | require_lot_attributes
305 |
306 | PURPOSE
307 | To check whether completion lot number provided required lot attributes or
308 | not. This function will be called for EZ Completion transaction because
309 | we cannot gather lot attributes for EZ Completion transaction.
310 |
311 | ARGUMENTS
312 | p_org_id organization_id in MTL_LOT_NUMBERS
313 | p_item_id inventory_item_id in MTL_LOT_NUMBERS
314 | p_lot_number lot_number in MTL_LOT_NUMBERS
315 |
316 | RETURN
317 | WIP_CONSTANTS.YES if require lot attributes
318 | WIP_CONSTANTS.NO if not require lot attributes
319 |
320 | EXCEPTIONS
321 | If there is an unexpected error occur, also return WIP_CONSTANTS.NO.
322 +=====================================================================*/
323 FUNCTION require_lot_attributes(p_org_id IN NUMBER,
324 p_item_id IN NUMBER,
325 p_lot_number IN VARCHAR2)
326 RETURN NUMBER;
327
328 /*=====================================================================+
329 | FUNCTION
330 | get_locator
331 |
332 | PURPOSE
333 | Returns the concatenated locator number (with project/task names).
334 | Basically a wrapper around inv_project.get_locator, but as a
335 | procedure.
336 |
337 | ARGUMENTS
338 | p_locator_id
339 | p_org_id
340 | p_locator
341 |
342 | EXCEPTIONS
343 | This procedure is mainly used for e-records so there's no way to
344 | return a status.
345 +=====================================================================*/
346 PROCEDURE get_locator(p_locator_id IN NUMBER,
347 p_org_id IN NUMBER,
348 p_locator OUT NOCOPY VARCHAR2);
349
350 /*=====================================================================+
351 | FUNCTION
352 | is_user_defined_lot_exp
353 |
354 | PURPOSE
355 | To check whether lot expiration date was set to user-defined or not.
356 | This function will be called for EZ Completion transaction because
357 | we cannot gather lot expiration date for EZ Completion transaction.
358 | This check only matter for the new lot. For existing lot, we can
359 | EZ complete because we will use existing lot expiration date instead of
360 | gathering the new expiration date.
361 |
362 | ARGUMENTS
363 | p_org_id organization_id
364 | p_item_id inventory_item_id
365 | p_lot_number lot_number
366 |
367 | RETURN
368 | WIP_CONSTANTS.YES if lot expiration date is user-defined
369 | WIP_CONSTANTS.NO if lot expiration date is no control or shelf life days
370 |
371 | EXCEPTIONS
372 | If there is an unexpected error occur, also return WIP_CONSTANTS.NO.
373 +=====================================================================*/
374 FUNCTION is_user_defined_lot_exp(p_org_id IN NUMBER,
375 p_item_id IN NUMBER,
376 p_lot_number IN VARCHAR2)
377 RETURN NUMBER;
378
379 /**************************************************************************
380 * This function can be used to check whether descriptive flex field is
381 * required or not. This function will return either fnd_api.g_true or
382 * fnd_api.g_false.
383 *************************************************************************/
384 FUNCTION is_dff_required(p_application_id IN NUMBER,
385 p_dff_name IN VARCHAR2)
386 RETURN VARCHAR2;
387
388 /**************************************************************************
389 * This function can be used to check whether descriptive flex field is
390 * setup or not. This function will return either fnd_api.g_true or
391 * fnd_api.g_false.
392 *************************************************************************/
393 FUNCTION is_dff_setup(p_application_id IN NUMBER,
394 p_dff_name IN VARCHAR2)
395 RETURN VARCHAR2;
396
397 /*=====================================================================+
398 | FUNCTION
399 | validate_scrap_account_id
400 | Added this function for bug 7138983(FP 7028072).
401 | PURPOSE
402 | To check whether the scrap account id is valid for the specified
403 | structure_id, in the current responsibility. It looks into security
404 | rules while validating.This function will be called for validating
405 | Scrap account id from WIPTXSFM.pld
406 |
407 | ARGUMENTS
408 | scrap_account_id
409 | chart_of_accounts_id
410 |
411 | RETURN
412 | 'Y' if the combination is valid.
413 | 'N' if the combination is invalid.
414 |
415 | EXCEPTIONS
416 | If there is an unexpected error returns 'N'
417 +=====================================================================*/
418 FUNCTION validate_scrap_account_id ( scrap_account_id IN NUMBER ,
419 chart_of_accounts_id IN NUMBER )
420 RETURN VARCHAR2 ;
421
422 --VJ: Label Printing - Start
423
424 PROCEDURE print_job_labels(p_wip_entity_id IN NUMBER,
425 -- p_op_seq_num IN NUMBER,
426 x_status IN OUT NOCOPY VARCHAR2,
427 x_msg_count IN OUT NOCOPY NUMBER,
428 x_msg IN OUT NOCOPY VARCHAR2
429 );
430
431 PROCEDURE print_serial_label(p_org_id IN NUMBER,
432 p_serial_number IN VARCHAR2,
433 p_item_id IN NUMBER,
434 x_status IN OUT NOCOPY VARCHAR2,
435 x_msg_count IN OUT NOCOPY NUMBER,
436 x_msg IN OUT NOCOPY VARCHAR2
437 );
438
439 PROCEDURE print_move_txn_label(p_txn_id IN NUMBER,
440 x_status IN OUT NOCOPY VARCHAR2,
441 x_msg_count IN OUT NOCOPY NUMBER,
442 x_msg IN OUT NOCOPY VARCHAR2
443 );
444
445 --VJ: Label Printing - End
446
447 END WIP_UTILITIES;
[Home] [Help]