1 PACKAGE INV_LABEL AUTHID CURRENT_USER AS
2 /* $Header: INVLABPS.pls 120.7.12020000.2 2013/02/26 08:49:51 srsomasu ship $ */
3 /*#
4 * This procedure initiates a label print request for Oracle Warehouse
5 * Management or Mobile Supply Chain Applications
6 * @rep:scope public
7 * @rep:product INV
8 * @rep:lifecycle active
9 * @rep:displayname Label Printing request for WMS/MSCA
10 * @rep:category BUSINESS_ENTITY WMS_LABEL
11 */
12
13 G_PKG_NAME CONSTANT VARCHAR2(50) := 'INV_LABEL';
14
15 -- Table type definition for an array of transaction_id reocrds and input parameters
16 TYPE transaction_id_rec_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
17
18 TYPE input_parameter_rec_type is TABLE OF MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
19 INDEX BY BINARY_INTEGER;
20
21 txn_id_null_rec transaction_id_rec_type;
22 input_param_null_rec input_parameter_rec_type;
23
24 -- Table type definition of label type header information
25 TYPE label_type_rec is RECORD
26 (
27 business_flow_code NUMBER
28 , label_type_id NUMBER -- Label Type ID
29 , label_type VARCHAR2(200) -- Label Type Desc
30 , default_format_id NUMBER
31 , default_format_name VARCHAR2(200)
32 , default_printer VARCHAR2(200)
33 , default_no_of_copies NUMBER
34 , manual_format_id NUMBER -- Added for Add format/printer for manual request
35 , manual_format_name VARCHAR2(200) -- Added for Add format/printer for manual request
36 , manual_printer VARCHAR2(200) -- Added for Add format/printer for manual request
37 );
38 TYPE label_type_tbl_type IS TABLE OF label_type_rec INDEX BY BINARY_INTEGER;
39
40
41 ---------------------------------------------------------------------------------------------
42 -- Project: 'Custom Labels' (A 11i10+ Project) |
43 -- Author: Dinesh ([email protected]) |
44 -- Change Description: |
45 -- Included sql_Stmt to label_field_var_rec to hold the 'Custom Query' supplied by the user |
46 ---------------------------------------------------------------------------------------------
47 -- Record type and table of record type defintion for the selected fields for a format
48 TYPE label_field_variable_rec IS RECORD
49 (
50 label_field_id NUMBER
51 , variable_name VARCHAR2(100)
52 , column_name VARCHAR2(100)
53 , sql_stmt VARCHAR2(4000) -- This field is newly added for the Custom SQL project.
54 );
55 ------------------------End of this change for Custom Labels project code--------------------
56
57 TYPE label_field_variable_tbl_type IS TABLE OF label_field_variable_rec INDEX BY BINARY_INTEGER;
58
59 TYPE lpn_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
60
61 -- Record type and table of record type definition to
62 -- keep lot and serial number data for material label
63 -- for receipt, inspection and put away
64 TYPE material_label_input_rec IS RECORD
65 (
66 item_id NUMBER
67 , lot_number VARCHAR2(240)
68 , lot_quantity NUMBER
69 , serial_number VARCHAR2(240)
70 );
71 TYPE material_label_input_tbl IS TABLE OF material_label_input_rec INDEX BY BINARY_INTEGER;
72
73 TYPE serial_tab_type IS TABLE OF VARCHAR(100) INDEX BY BINARY_INTEGER;
74
75 -- Table of record type to store label data.
76 -- Each record is a LONG variable to store one piece of label data
77 -- <LABEL> ... </LABEL>
78 --TYPE label_tbl_type IS TABLE OF LONG INDEX BY BINARY_INTEGER;
79
80 -- Sept.10: Patchset I change for label history and reprint
81 -- In addtion to the LONG variable for label data,
82 -- added information of Label format, Printer, and RequestID
83 TYPE label_rec_type IS RECORD
84 (
85 label_request_id NUMBER
86 , label_content LONG
87 , label_status VARCHAR2(1)
88 , error_message VARCHAR2(1000)
89 );
90 TYPE label_tbl_type IS TABLE OF label_rec_type INDEX BY BINARY_INTEGER;
91
92 TYPE t_genref IS REF CURSOR;
93
94 /* Bug 3417450. This table contains the request_id - label_type_id mapping which is
95 * used to fetch the correct label format for the LPN Summary labels, if they are printed
96 * from the LPN Content label print call
97 */
98 TYPE label_request_rec IS RECORD
99 (
100 label_request_id NUMBER
101 , label_type_id NUMBER
102 );
103
104 TYPE label_request_tbl IS TABLE OF label_request_rec INDEX BY BINARY_INTEGER;
105
106 TYPE l_cust_entity_table_type IS TABLE OF VARCHAR2(1000) INDEX BY LONG; -- Added for Conf Label ER
107
108
109
110 /*******************************************
111 ** -- Global Variables
112 *******************************************/
113 g_xml_content LONG;
114 g_xml_header LONG;
115 g_material_label_input material_label_input_tbl;
116 g_label_request_tbl label_request_tbl;
117
118 -- Transaction Identifier denotes the source of Transaction. This is used
119 -- in conjunction with p_transaction_id to retrive the details. Possible values :
120 /* MANUAL: Manual mode. source is from p_input_param.p_transaction_id */
121 /* MMTT : transaction is MTL_MATERIAL_TRANSACTIONS_TEMP.transaction_temp_id */
122 /* MTI : transaction is MTL_TRANSACTION_INTERFACE.transaction_interface_id */
123 /* MTRL : transaction is MTL_TXN_REQUEST_LINES.line_id */
124 /* WFS : transaction is WIP_FLOW_SCHEDULES.wip_entity_id */
125 /* RT : transaction is RCV_TRANSACTION.lpn_group_id */
126 /* RSH : transaction is RCV_SHIPMENT_HEADERS.shipment_header_id */
127
128 -- bug #6417575, Label Printing Support for WIP Move Transactions (12.1)
129 /* WMT : transaction is WIP_MOVE_TRANSACTIONS.transaction_id */
130 --START Conf Label ER
131 /* RTI : transaction is RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID */
132 /* WDD : transaction is WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID */
133 /* WND : transaction is wsh_new_deliveries.delivery_id */
134 /* MCCE : transaction is mtl_cycle_count_entries.cycle_count_entry_id */
135
136 --END Conf Label ER
137
138
139 TRX_ID_MANUAL CONSTANT NUMBER := 0;
140 TRX_ID_MMTT CONSTANT NUMBER := 1;
141 TRX_ID_MTI CONSTANT NUMBER := 2;
142 TRX_ID_MTRL CONSTANT NUMBER := 3;
143 TRX_ID_WFS CONSTANT NUMBER := 4;
144 TRX_ID_RT CONSTANT NUMBER := 5;
145 TRX_ID_RSH CONSTANT NUMBER := 6;
146 TRX_ID_DIS CONSTANT NUMBER := 7; -- fabdi, added for gmo
147 TRX_ID_UNDIS CONSTANT NUMBER := 8; -- fabdi, added for gmo
148 TRX_ID_WMT CONSTANT NUMBER := 9; -- hjogleka, added for Label Printing Support for WIP Move Transactions (12.1)
149
150 --START Conf Label ER
151 TRX_ID_C_MMTT CONSTANT VARCHAR2(40) := 'MMTT.TRANSACTION_TEMP_ID';
152 TRX_ID_C_MTI CONSTANT VARCHAR2(40) := 'MTI.TRANSACTION_INTERFACE_ID';
153 TRX_ID_C_MTRL CONSTANT VARCHAR2(40) := 'MTRL.LINE_ID';
154 TRX_ID_C_RT CONSTANT VARCHAR2(40) := 'RT.GROUP_ID';
155 TRX_ID_C_RSH CONSTANT VARCHAR2(40) := 'RSH.SHIPMENT_HEADER_ID';
156 TRX_ID_C_WDD CONSTANT VARCHAR2(40) := 'WDD.DELIVERY_DETAIL_ID';
157 TRX_ID_C_WND CONSTANT VARCHAR2(40) := 'WND.DELIVERY_ID';
158 TRX_ID_C_MCCE CONSTANT VARCHAR2(40) := 'MCCE.CYCLE_COUNT_ENTRY_ID';
159 TRX_ID_C_LPN CONSTANT VARCHAR2(40):= 'LPN_ID';
160 TRX_ID_C_NULL CONSTANT VARCHAR2(40):= 'NULL_TRX_ID';
161 --END Conf Label ER
162
163
164 -- Global Variable for RFID
165 -- Added for 11.5.10+ RFID Compliance project as lpn_group_id
166 -- Modified in R12 RFID project as EPC_GROUP_ID
167 -- This is used to retrieve EPC for LPN/Material/Serial
168 -- For each label printing request, there will be a new LPN Group ID from WMS_EPC_S2
169 -- At the end of the label printing request, the value will be set to null
170 epc_group_id NUMBER;
171
172 /*************************************************************************
173 * Print Label
174 * This can be called from transaction process or manual
175 * p_print_mode: 1 => Transaction Driven
176 * 2 => Manual print
177 * If it is transaction driven, business flow code and transaction are required
178 * If it is manual print, label type and input record are required
179 * LABEL Types
180 * 1 Material
181 * 2 Serial
182 * 3 LPN
183 * 4 LPN Content
184 * 5 LPN Summary
185 * 6 Location
186 * 7 Shipping
187 * 8 Shipping Contents
188 * 9 WIP Content
189 * 10 WIP Flow
190 * 16 WIP Move Contents
191 ***************************************************************************/
192 PROCEDURE PRINT_LABEL
193 (
194 x_return_status OUT NOCOPY VARCHAR2
195 , x_msg_count OUT NOCOPY NUMBER
196 , x_msg_data OUT NOCOPY VARCHAR2
197 , x_label_status OUT NOCOPY VARCHAR2
198 , p_api_version IN NUMBER
199 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
200 , p_commit IN VARCHAR2 := fnd_api.g_false
201 , p_print_mode IN NUMBER
202 , p_business_flow_code IN NUMBER DEFAULT NULL
203 , p_transaction_id IN transaction_id_rec_type default txn_id_null_rec
204 , p_input_param_rec IN input_parameter_rec_type default input_param_null_rec
205 , p_label_type_id IN NUMBER DEFAULT NULL
206 , p_no_of_copies IN NUMBER := 1
207 , p_transaction_identifier IN NUMBER DEFAULT NULL
208 , p_format_id IN NUMBER DEFAULT NULL -- Added for the Add Printer and Format Project.
209 , p_printer_name IN VARCHAR2 DEFAULT NULL -- Added for the Add Printer and Format Project.
210 ) ;
211
212 /*************************************************************************
213 * Print Label
214 * New Overloaded procedure add by GMO fabdi
215 *
216 * This can be called from transaction process or manual
217 * p_print_mode: 1 => Transaction Driven
218 * 2 => Manual print
219 * If it is transaction driven, business flow code and transaction are required
220 * If it is manual print, label type and input record are required
221 * LABEL Types
222 * 1 Material
223 * 2 Serial
224 * 3 LPN
225 * 4 LPN Content
226 * 5 LPN Summary
227 * 6 Location
228 * 7 Shipping
229 * 8 Shipping Contents
230 * 9 WIP Content
231 * 10 WIP Flow
232 * 11 Process material
233 * 12 Dispense material
234 * 13 dispense cage
235 * 14 process product
236 * 15 process sample
237 *
238 ***************************************************************************/
239 PROCEDURE PRINT_LABEL
240 (
241 x_return_status OUT NOCOPY VARCHAR2
242 , x_msg_count OUT NOCOPY NUMBER
243 , x_msg_data OUT NOCOPY VARCHAR2
244 , x_label_status OUT NOCOPY VARCHAR2
245 , x_label_request_id OUT NOCOPY NUMBER -- fabdi, new para
246 , p_api_version IN NUMBER
247 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
248 , p_commit IN VARCHAR2 := fnd_api.g_false
249 , p_print_mode IN NUMBER
250 , p_business_flow_code IN NUMBER DEFAULT NULL
251 , p_transaction_id IN transaction_id_rec_type default txn_id_null_rec
252 , p_input_param_rec IN input_parameter_rec_type default input_param_null_rec
253 , p_label_type_id IN NUMBER DEFAULT NULL
254 , p_no_of_copies IN NUMBER := 1
255 , p_transaction_identifier IN NUMBER DEFAULT NULL
256 , p_format_id IN NUMBER DEFAULT NULL -- Added for the Add Printer and Format Project.
257 , p_printer_name IN VARCHAR2 DEFAULT NULL -- Added for the Add Printer and Format Project.
258 ) ;
259
260 /********************************************
261 * Wrapper API for calling printing from Java
262 * This wrapper is for giving transaction ID
263 *******************************************/
264 PROCEDURE PRINT_LABEL_WRAP
265 (
266 x_return_status OUT NOCOPY VARCHAR2
267 , x_msg_count OUT NOCOPY NUMBER
268 , x_msg_data OUT NOCOPY VARCHAR2
269 , x_label_status OUT NOCOPY VARCHAR2
270 , p_business_flow_code IN NUMBER DEFAULT NULL
274
271 , p_transaction_id IN NUMBER
272 , p_transaction_identifier IN NUMBER DEFAULT NULL
273 ) ;
275 /********************************************
276 * Wrapper API for calling printing from Java
277 * This wrapper is for Manual Mode
278 *******************************************/
279 /*#
280 * These procedure initiates manual label print request based on the provided
281 * information. Note that the label will be generated with the given information
282 * provided by the input parameters, and will not be based on any transaction
283 * records such as MTL_MATERIAL_TRANSACTIONS_TEMP records. However, a
284 * business flow code can be provided for those transactions that do not have
285 * transaction records, such as Serial Generation, Cost Group Update, etc.
286 *
287 * @param x_return_status Return status of the procedure. If the procedure
288 * succeeds, the value will be fnd_api.g_ret_sts_success; if there is an expected * error, the value will be fnd_api.g_ret_sts_error; if there is an unexpected
289 * error, the value will be fnd_api.g_ret_sts_unexp_error;
290 * @ paraminfo {@rep:required}
291 * @ param x_msg_count if there is one or more errors, the number of error
292 * messages in the buffer
293 * @ paraminfo {@rep:required}
294 * @ param x_msg_data if there is one and only one error, the error message is x_msg_data, otherwise, get the messages from the message stack
295 * @ paraminfo {@rep:required}
296 * @ param x_label_status status message from printer. Currently not used
297 * @ paraminfo {@rep:required}
298 * @ param p_business_flow_code the business flow code that initiate this printing request. It is null for a manual print request. Valid values are specified in lookup WMS_BUSINESS_FLOW. Default value is NULL
299 * @ paraminfo {@rep:optional}
300 * @ param p_label_type Type of label that is requested. Valid values are specified in lookup WMS_LABEL_TYPE. Default value is NULL
301 * @ paraminfo {@rep:optional}
302 * @ param p_organization_id Organization ID. It is required for Material and Serial label. Default value is NULL
303 * @ paraminfo {@rep:optional}
304 * @ param p_inventory_item_id Inventory Item ID. It is required for Material and Serial label. Default value is NULL
305 * @ paraminfo {@rep:optional}
306 * @ param p_revision Item Revision.It is required for Material and Serial label, if applicable. Default value is NULL
307 * @ paraminfo {@rep:optional}
308 * @ param p_lot_number Lot Number.It is required for Material and Serial label, if applicable. Default value is NULL
309 * @ paraminfo {@rep:optional}
310 * @ param p_fm_serial_number From Serial Number. It is required for Serial label. Default value is NULL
311 * @ paraminfo {@rep:optional}
312 * @ param p_to_serial_number To Serial Number. It is required for Material and Serial label, when printing for a range of serial numbers, if applicable.Default value is NULL
313 * @ paraminfo {@rep:optional}
314 * @ param p_lpn_id License Plate Number ID. It is required for LPN, LPN Content, LPN Summary, and Shipping Content label. Default value is NULL
315 * @ paraminfo {@rep:optional}
316 * @ param p_subinventory_code Subinventory Code. It may be required for Location label. Default value is NULL
317 * @ paraminfo {@rep:optional}
318 * @ param p_locator_id Locator ID, It may be required for Location label. Default value is NULL
319 * @ paraminfo {@rep:optional}
320 * @ param p_delivery_id Delivery ID. It is required for Shipping and Shipping Content label. Default value is NULL
321 * @ paraminfo {@rep:optional}
322 * @ param p_quantity Quantity. It is required for Material label. Default value is NULL
323 * @ paraminfo {@rep:optional}
324 * @ param p_uom Unit of Measure code. It is required for Material label. Default value is NULL
325 * @ paraminfo {@rep:optional}
326 * @ param p_wip_entity_id WIP Entity Id. It is required for WIP Move Contents Label, Default value is NULL
327 * @ paraminfo {@rep:optional}
328 * @ param p_no_of_copies Number of copies of the label. Default value is NULL
329 * @ paraminfo {@rep:optional}
330 * @ param p_fm_schedule_number From Schedule Number. Default value is NULL. It is not used currently
331 * @ paraminfo {@rep:optional}
332 * @ param p_to_schedule_number To Schedule Number. Default value is NULL. It is not used currently. Default value is NULL
333 * @ paraminfo {@rep:optional}
334 * @ param p_format_id Label Format ID. If provided, this specific label format will be used to generate labels. Otherwise, the label format will be decided with WMS Rules Engine or default label format in a Inventory only appplication. Default is NULL
335 * @ paraminfo {@rep:optional}
336 * @ param p_printer_name Printer Name. If it is provided, this specific printer will be used to print a label. Otherwise, the printer will be decided with printer setup. Default value is NULL
337 * @ paraminfo {@rep:optional}
338 * @rep:scope public
339 * @rep:lifecycle active
340 * @rep:displayname API to initiate a manual label printing request
341 * @rep:businessevent print_label_manual_wrap
342 */
343 PROCEDURE PRINT_LABEL_MANUAL_WRAP
344 (
345 x_return_status OUT NOCOPY VARCHAR2
346 , x_msg_count OUT NOCOPY NUMBER
347 , x_msg_data OUT NOCOPY VARCHAR2
348 , x_label_status OUT NOCOPY VARCHAR2
349 , p_business_flow_code IN NUMBER DEFAULT NULL
350 , p_label_type IN NUMBER DEFAULT NULL
351 , p_organization_id IN NUMBER DEFAULT NULL
352 , p_inventory_item_id IN NUMBER DEFAULT NULL
353 , p_revision IN VARCHAR2 DEFAULT NULL
354 , p_lot_number IN VARCHAR2 DEFAULT NULL
355 , p_fm_serial_number IN VARCHAR2 DEFAULT NULL
356 , p_to_serial_number IN VARCHAR2 DEFAULT NULL
357 , p_lpn_id IN NUMBER DEFAULT NULL
358 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
359 , p_locator_id IN NUMBER DEFAULT NULL
363 , p_wip_entity_id IN NUMBER DEFAULT NULL
360 , p_delivery_id IN NUMBER DEFAULT NULL
361 , p_quantity IN NUMBER DEFAULT NULL
362 , p_uom IN VARCHAR2 DEFAULT NULL
364 , p_no_of_copies IN NUMBER DEFAULT NULL
365 , p_fm_schedule_number IN VARCHAR2 DEFAULT NULL
366 , p_to_schedule_number IN VARCHAR2 DEFAULT NULL
367 , p_format_id IN NUMBER DEFAULT NULL
368 , p_printer_name IN VARCHAR2 DEFAULT NULL
369 );
370
371 /*****************************************************
372 * API to get the label fields defined for a specific
373 * format. This get called from the individual label
374 * API's often.
375 *****************************************************/
376 PROCEDURE GET_VARIABLES_FOR_FORMAT(
377 x_variables OUT NOCOPY label_field_variable_tbl_type
378 , x_variables_count OUT NOCOPY NUMBER
379 , p_format_id IN NUMBER
380 );
381 /******************************************************
382 * Overloaded procedure GET_VARIABLES_FOR_FORMAT
383 * Added in 11.5.10+
384 * Also it can check whether a given variable is included
385 * in the given format
386 * p_exist_variable_name has the name of the variable
387 * that will be checked for existence
388 * x_is_variable_exist returns whether the given variable exists
389 * possible value is 'Y' or 'N'
390 *******************************************************/
391 PROCEDURE GET_VARIABLES_FOR_FORMAT(
392 x_variables OUT NOCOPY label_field_variable_tbl_type
393 , x_variables_count OUT NOCOPY NUMBER
394 , x_is_variable_exist OUT NOCOPY VARCHAR2
395 , p_format_id IN NUMBER
396 , p_exist_variable_name IN VARCHAR2 DEFAULT NULL
397 );
398
399 /*****************************************************
400 * API to get default formatfor a pabel type passed in
401 *****************************************************/
402 PROCEDURE GET_DEFAULT_FORMAT
403 (p_label_type_id IN number,
404 p_label_format OUT NOCOPY VARCHAR2,
405 p_label_format_id OUT NOCOPY NUMBER
406 );
407
408 /**********************************************************
409 * Rules Engine call from within the individual label API's
410 **********************************************************/
411 PROCEDURE GET_FORMAT_WITH_RULE
412 (
413 P_DOCUMENT_ID IN NUMBER ,
414 P_LABEL_FORMAT_ID IN NUMBER default null,
415 P_ORGANIZATION_ID IN NUMBER default null,
416 P_INVENTORY_ITEM_ID IN NUMBER default null,
417 P_SUBINVENTORY_CODE IN VARCHAR2 default null,
418 P_LOCATOR_ID IN NUMBER default null,
419 P_LOT_NUMBER IN VARCHAR2 default null,
420 P_REVISION IN VARCHAR2 default null,
421 P_SERIAL_NUMBER IN VARCHAR2 default null,
422 P_LPN_ID IN NUMBER default null,
423 P_SUPPLIER_ID IN NUMBER default null,
424 P_SUPPLIER_SITE_ID IN NUMBER default null,
425 P_SUPPLIER_ITEM_ID IN NUMBER default null,
426 P_CUSTOMER_ID IN NUMBER default null,
427 P_CUSTOMER_SITE_ID IN NUMBER default null,
428 P_CUSTOMER_ITEM_ID IN NUMBER default null,
429 P_CUSTOMER_CONTACT_ID IN NUMBER default null,
430 P_FREIGHT_CODE IN VARCHAR2 default null,
431 P_LAST_UPDATE_DATE IN DATE ,
432 P_LAST_UPDATED_BY IN NUMBER ,
433 P_CREATION_DATE IN DATE ,
434 P_CREATED_BY IN NUMBER ,
435 P_LAST_UPDATE_LOGIN IN NUMBER default null,
436 P_REQUEST_ID IN NUMBER default null,
437 P_PROGRAM_APPLICATION_ID IN NUMBER default null,
438 P_PROGRAM_ID IN NUMBER default null,
439 P_PROGRAM_UPDATE_DATE IN DATE default null,
440 P_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
441 P_ATTRIBUTE1 IN VARCHAR2 default null,
442 P_ATTRIBUTE2 IN VARCHAR2 default null,
443 P_ATTRIBUTE3 IN VARCHAR2 default null,
444 P_ATTRIBUTE4 IN VARCHAR2 default null,
445 P_ATTRIBUTE5 IN VARCHAR2 default null,
446 P_ATTRIBUTE6 IN VARCHAR2 default null,
447 P_ATTRIBUTE7 IN VARCHAR2 default null,
448 P_ATTRIBUTE8 IN VARCHAR2 default null,
449 P_ATTRIBUTE9 IN VARCHAR2 default null,
450 P_ATTRIBUTE10 IN VARCHAR2 default null,
451 P_ATTRIBUTE11 IN VARCHAR2 default null,
452 P_ATTRIBUTE12 IN VARCHAR2 default null,
453 P_ATTRIBUTE13 IN VARCHAR2 default null,
454 P_ATTRIBUTE14 IN VARCHAR2 default null,
455 P_ATTRIBUTE15 IN VARCHAR2 default null,
456 P_PRINTER_NAME IN VARCHAR2 default null,
457 P_DELIVERY_ID IN NUMBER default null,
458 P_BUSINESS_FLOW_CODE IN NUMBER default null,
459 P_PACKAGE_ID IN NUMBER default null,
463 p_use_rule_engine IN VARCHAR2 default null, -- For label history of multi-rec label types
460 p_sales_order_header_id IN NUMBER default null, -- bug 2326102
461 p_sales_order_line_id IN NUMBER default null, -- bug 2326102
462 p_delivery_detail_id IN NUMBER default null, -- bug 2326102
464 x_return_status OUT NOCOPY VARCHAR2 ,
465 x_label_format_id OUT NOCOPY NUMBER ,
466 x_label_format OUT NOCOPY VARCHAR2 ,
467 x_label_request_id OUT NOCOPY NUMBER -- For label history
468 );
469
470 PROCEDURE trace(p_message IN VARCHAR2,
471 p_prompt IN VARCHAR2 ,
472 p_level IN NUMBER DEFAULT 12);
473
474
475 /***************************************
476 * Get numbers between a specified range
477 ***************************************/
478 PROCEDURE GET_NUMBER_BETWEEN_RANGE(
479 fm_x_number IN VARCHAR2
480 , to_x_number IN VARCHAR2
481 , x_return_status OUT NOCOPY VARCHAR2
482 , x_number_table OUT NOCOPY serial_tab_type );
483
484 /***************************************
485 * Update history record
486 ***************************************/
487 PROCEDURE update_history_record(
488 p_label_request_id IN NUMBER
489 , p_status_flag IN VARCHAR2 DEFAULT NULL
490 , p_job_status IN VARCHAR2 DEFAULT NULL
491 , p_printer_status IN VARCHAR2 DEFAULT NULL
492 , p_status_type IN VARCHAR2 DEFAULT NULL
493 , p_outfile_name IN VARCHAR2 DEFAULT NULL
494 , p_outfile_directory IN VARCHAR2 DEFAULT NULL
495 , p_error_message IN VARCHAR2 DEFAULT NULL
496 );
497
498 /**************************************
499 * Reprint a previous label to a specified
500 * printer or no of copies
501 * The input parameters are
502 * p_hist_label_request_id :
503 is the label request ID of the original
504 label printing history record
505 * p_printer_name: new printer name
506 * p_no_of_copies: new number of copies
507 **************************************/
508 PROCEDURE RESUBMIT_LABEL_REQUEST(
509 x_return_status OUT NOCOPY VARCHAR2
510 , x_msg_count OUT NOCOPY NUMBER
511 , x_msg_data OUT NOCOPY VARCHAR2
512 , p_hist_label_request_id IN NUMBER
513 , p_printer_name IN VARCHAR2 DEFAULT NULL
514 , p_no_of_copy IN NUMBER DEFAULT NULL
515 );
516
517 /*************************************
518 * Obtain Label Request Print Hist
519 *************************************/
520 PROCEDURE INV_LABEL_REQUESTS_REPRINT (
521 x_label_rep_hist_inqs OUT NOCOPY t_genref,
522 p_printer_Name IN VARCHAR2,
523 p_bus_flow_Code IN NUMBER,
524 p_label_type_Id IN NUMBER,
525 p_lpn_Id IN NUMBER,
526 p_Requests IN NUMBER,
527 p_created_By IN NUMBER,
528 x_Status OUT NOCOPY VARCHAR2,
529 x_Message OUT NOCOPY VARCHAR2
530 );
531
532 -- Bug #3067059
533 /**************************************
534 * Checks if there is a GTIN defined for the
535 * Item + UOM + Rev combination.
536 * Also fetches GTIN and GTIN Desc. if it is
537 * defined for the given Org, Item, UOM, Rev
538 **************************************/
539 PROCEDURE IS_ITEM_GTIN_ENABLED(
540 x_return_status OUT NOCOPY VARCHAR2
541 , x_gtin_enabled OUT NOCOPY BOOLEAN
542 , x_gtin OUT NOCOPY VARCHAR2
543 , x_gtin_desc OUT NOCOPY VARCHAR2
544 , p_organization_id IN NUMBER
545 , p_inventory_item_id IN NUMBER
546 , p_unit_of_measure IN OUT NOCOPY VARCHAR2 --bug11820532
547 , p_revision IN VARCHAR2
548 );
549
550 /*****************************************
551 * Global variables for business flow code
552 *****************************************/
553 WMS_BF_RECEIPT CONSTANT NUMBER := 1;
554 WMS_BF_INSPECTION CONSTANT NUMBER := 2;
555 WMS_BF_DELIVERY CONSTANT NUMBER := 3;
556 WMS_BF_PUTAWAY_DROP CONSTANT NUMBER := 4;
557 WMS_BF_LPN_CORRECTION CONSTANT NUMBER := 5;
558 WMS_BF_CROSSDOCK CONSTANT NUMBER := 6;
559 WMS_BF_REPLENISHMENT_DROP CONSTANT NUMBER := 7;
560 WMS_BF_CYCLE_COUNT CONSTANT NUMBER := 8;
561 WMS_BF_PHYSICAL_COUNT CONSTANT NUMBER := 9;
562 WMS_BF_MAT_STATUS_UPD CONSTANT NUMBER := 10;
563 WMS_BF_COST_GROUP_UPD CONSTANT NUMBER := 11;
564 WMS_BF_LOT_SPLIT_MERGE CONSTANT NUMBER := 12;
565 WMS_BF_MISC_RECEIPT CONSTANT NUMBER := 13;
566 WMS_BF_ORG_TRANSFER CONSTANT NUMBER := 14;
567 WMS_BF_SUB_TRANSFER CONSTANT NUMBER := 15;
568 WMS_BF_LPN_GENERATION CONSTANT NUMBER := 16;
569 WMS_BF_SN_GENERATION CONSTANT NUMBER := 17;
570 WMS_BF_PICK_LOAD CONSTANT NUMBER := 18;
571 WMS_BF_PICK_DROP CONSTANT NUMBER := 19;
572 WMS_BF_PACK_LPN CONSTANT NUMBER := 20;
573 WMS_BF_SHIP_CONFIRM CONSTANT NUMBER := 21;
574 WMS_BF_CARTONIZATION CONSTANT NUMBER := 22;
575 WMS_BF_MISC_ISSUE CONSTANT NUMBER := 23;
576 WMS_BF_DYNAMIC_LOCATOR CONSTANT NUMBER := 24;
577 WMS_BF_IMPORT_ASN CONSTANT NUMBER := 25;
578 WMS_BF_WIP_COMPLETION CONSTANT NUMBER := 26;
579 WMS_BF_PUTAWAY_PREGEN CONSTANT NUMBER := 27;
580 WMS_BF_WIP_PICK_LOAD CONSTANT NUMBER := 28;
581 WMS_BF_WIP_PICK_DROP CONSTANT NUMBER := 29;
582 WMS_BF_INV_PUTAWAY CONSTANT NUMBER := 30;
586 WMS_BF_REPLENISHMENT_LOAD CONSTANT NUMBER := 34;
583 WMS_BF_FLOW_LINE_START CONSTANT NUMBER := 31;
584 WMS_BF_FLOW_LINE_OPERATION CONSTANT NUMBER := 32;
585 WMS_BF_FLOW_WORK_ASSEMBLY CONSTANT NUMBER := 33;
587 WMS_BF_WIP_FLOW_PUTAWAY CONSTANT NUMBER := 35;
588
589
590 /*******************************************
591 * Global variables for Date, Time, User
592 *******************************************/
593 G_DATE VARCHAR2(20);
594 G_TIME VARCHAR2(20);
595 G_USER VARCHAR2(100);
596 G_DATE_FORMAT_MASK VARCHAR2(100);
597
598 /*******************************************
599 * Global variable for Character Set and
600 * XML encoding
601 *******************************************/
602 G_CHARACTER_SET VARCHAR2(50):= NULL;
603 G_XML_ENCODING VARCHAR2(50):= NULL;
604 G_DEFAULT_XML_ENCODING CONSTANT VARCHAR2(50) := 'UTF-8';
605
606 /************************************
607 * Global variable for Profile values
608 ************************************/
609 G_PROFILE_PRINT_MODE NUMBER;
610 G_PROFILE_PREFIX VARCHAR2(100);
611 G_PROFILE_OUT_DIR VARCHAR2(200);
612 -- Bug #3067059
613 G_PROFILE_GTIN VARCHAR2(100) := NULL;
614 /***********************************
615 * Global variable for debug profile
616 ***********************************/
617 L_DEBUG NUMBER := 0;
618
619
620 /***********************************
621 * Global variable for label status
622 ***********************************/
623 G_SUCCESS VARCHAR2(1) := 'S';
624 G_ERROR VARCHAR2(1) := 'E';
625 G_WARNING VARCHAR2(1) := 'W';
626 --added for lpn status project to get the status of the lpn after the transaction has been commited
627 FUNCTION get_txn_lpn_status
628 (p_lpn_id IN NUMBER,
629 p_transaction_id IN NUMBER,
630 p_organization_id IN NUMBER,
631 p_business_flow IN NUMBER)
632 RETURN VARCHAR2 ;
633 --end of lpn status project
634
635 END INV_LABEL;