Label Printing with WMS
Together with MSCA (Mobile supply chain application) and WMS (warehouse management system) have capabilities built into to produce labels using the various points of the business process.
Oracle WMS produces label XML files as a part of transaction or an event such as a Purchase order receipt, Pick confirmation, work order completion or licenace plate transaction such as consolidate, generate etc.
To print oracle generated label XML, you will need actual label printer, label design application to define the graphical layout of the label.
Oracle supports two kinds of label printing configurations
1. Printing using third party label software and a print server
2. Printing using XML enabled direct label printers
The printing strategy depends upon factors like ROI, real time latency, volume of label, network infrastructure etc.
Printer server solution
In this strategy it requires to have a configuration of Oracle WMS, third party label printing software, printer server and network printers.
The LDA (Label design application) allows you to define different label printing formats. The printer server manages the print queue. Finally the label printer prints the label.
The label printer and LDA can come from same vendor or you can have different vendors for it.
This approach is recommended when you have high volume of labels to print form each printer.
Oracle WMS allows you to have two choices of integration with third party software.
This can be done by setting the profile WMS Label Print Mode- Asynchronous
1. Asynchronous mode
2. Synchronous mode
In Asynchronous mode of architecture oracle WMS generated the label XML and drops in it a specified directory. Third party label software monitors this directory for any new XML file and processes them.
Profile WMS: Label output directory – specify where the XML file needs to stored
WMS: Label file prefix- Label prefix.
Disadvantages of asynchronous label printing
Include latency security issues to FTP files across severs and no possibility for response/
Synchronous mode-
Set profile as WMS Label Print Mode- Synchronous – Generic
In synchronous mode of integration, Oracle WMS generates a label XML and sends it directly to the partner via one of two synchronous methods. In the first method, the integration is via an API implemented by the third party application that replaces an empty stub provided by Oracle (INV_TXNSTUB_PUB.POSTTRANSACTION).
Optionally, you can set ‘WMS Label Print Mode’ to ‘Synchronous – TCP/IP’ and define the IP address and port on which the print server is listening.
Synchronous - TCP/IP
Profile setup as 'WMS: Label Print Mode' to Synchronous - TCP/IP. Profile setup as 'WMS: Label Print Mode' to Synchronous - TCP/IP. Here you do not need to create any concurrent program for XML generation rather Oracle will create it as seeded. Additionally if you need to get labels printed distinctly and not want then getting overlapped (rare scenario wherein you fire multiple prints at given time and actual prints gets mingled) while printing its advised to set the profile either at responsibility or site level 'WMS: Synchronous TCP/IP label request delay' to 100. For TCP/IP this is a required step and found not much documentation about it. Still facing issues then please check printer/style/drivers as driver has Unix printing LP command into SRW driver.
XML DIRECT PRINTER
In this strategy Oracle WMS sends the XML directly to the printer. The label printer is capable of receiving XML that meets Oracle’s label specification and merges with pre-defined label formats to print a label.
XML PUBLISHER
Using XML publisher, you can configure your label layout in MS Word and map the data content to Oracle XML. You can print these labels on normal printers.
However, you cannot deploy Oracle XML publisher as a printing solution because it does not have printer drivers for industrial label printers like Zebra, Intermec etc.
Transaction Manager Stub (INV_TXNSTUB_PUB.POSTTRANSACTION)
The transaction manager calls an empty stub when each transaction processes, regardless of processing mode. It can include any additional business logic to perform for each record in the transaction interface tables.
This program prints a label as
System administrator Navigation:- Install/Printer/types
System administrator Navigation:- Install/Printer/style
System administrator Navigation:- Install/Printer/Driver
Trigger code (Tigger on table ora_label_print_tab)
CREATE OR REPLACE TRIGGER ora_label_print_tab_trg
BEFORE INSERT
ON ora_label_print_tab
FOR EACH ROW
DECLARE
-- +------------------------------------------------------------+
-- + Variable Declaration +
-- +------------------------------------------------------------+
g_debug_point VARCHAR2 (50);
l_resp_id NUMBER := NULL;
l_resp_appl_id NUMBER := NULL;
l_request_id NUMBER := NULL;
l_result BOOLEAN;
l_result1 BOOLEAN;
e_user_null EXCEPTION;
BEGIN
g_debug_point := 'TRG_1000';
-- IF :NEW.created_by is null then
-- raise e_user_null;
-- END IF;
-- +------------------------------------------------------------+
-- + Get the Resp ID for 'Warehouse Manager', aka the 'WMS' key
-- +------------------------------------------------------------+
BEGIN
g_debug_point := 'TRG_1020';
l_resp_id := NULL;
l_resp_appl_id := NULL;
SELECT responsibility_id
,application_id
INTO l_resp_id
,l_resp_appl_id
FROM apps.fnd_responsibility
WHERE responsibility_key = 'WMS'
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_resp_id := 0;
l_resp_appl_id := 0;
END;
-- +------------------------------------------------------------+
-- + Initialize APPS Environment +
-- +------------------------------------------------------------+
g_debug_point := 'TRG_1030';
/*
BEGIN
fnd_global.apps_initialize (:NEW.created_by, l_resp_id, l_resp_appl_id);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
*/
IF l_resp_id IS NOT NULL
AND l_resp_appl_id IS NOT NULL
THEN
--fnd_global.apps_initialize (:NEW.created_by, l_resp_id, l_resp_appl_id);
NULL;
ELSE
:NEW.status := 'E';
:NEW.error_message :=
'Error at (' || g_debug_point || '):' || ' Error getting the responsibility id for responsibility key- WMS';
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END IF;
-- +------------------------------------------------------------+
-- + Launch the printing program
-- +------------------------------------------------------------+
g_debug_point := 'TRG_1100';
l_result := fnd_request.set_mode (TRUE);
l_result1 := fnd_request.set_print_options (printer => :NEW.printer_name
,copies => :NEW.print_copies);
g_debug_point := 'TRG_1110';
l_request_id :=
fnd_request.submit_request ('WMS' -- application_short_name
,'ORA_LABEL_PRINT' -- concurrent program name
,NULL -- description
,NULL -- start time
,FALSE -- sub_request
,:NEW.tag_print_id
,CHR (0)
);
--COMMIT;
IF NVL (l_request_id, -1) > 0
THEN
g_debug_point := 'TRG_1120';
:NEW.request_id := l_request_id;
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
ELSE
:NEW.status := 'E';
:NEW.error_message :=
SUBSTR ( 'Error at ('
|| g_debug_point
|| '):'
|| '1Error submitting conc program- ORA_LABEL_PRINT printer '
|| :NEW.printer_name
|| ' SQLMSG: '
|| SQLERRM
,1
,2000
);
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END IF;
EXCEPTION
WHEN e_user_null
THEN
NULL;
WHEN OTHERS
THEN
:NEW.status := 'E';
:NEW.error_message :=
SUBSTR ('Error at (' || g_debug_point || '):' || '2Error submitting conc program- ORA_LABEL_PRINT ' || 'SQLMSG: ' || SQLERRM
,1
,2000
);
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END ora_label_print_tab_trg;
/
Label print test case
I am explaining a test case based on the synchronous mode
Utilizing
INV stub - INV_TXNSTUB_PUB.POSTTRANSACTION
And WMS synchronous stub - INV_SYNC_PRINT_REQUEST.SYNC_PRINT_REQUEST
Where
INV_TXNSTUB_PUB.POSTTRANSACTION is getting called at the end of inventory transactions like PO receipt, ship confirm etc.
INV_SYNC_PRINT_REQUEST.SYNC_PRINT_REQUEST is getting called when you set profile WMS Label Print Mode- as “Synchronous – Generic”
Having custom label re-print program by which users can reprint the labels if needed.
Prerequisites-
Oracle printer setups – setting up the Intermec printer in oracle and on network.
Oracle WMS setup - for label format and assigning to a particular business flows.
Demonstration-
1- PO receipt label printing
Purchasing Navigation:- Purchase order
Create the PO and approve it.
Receive the PO by MSCA
Note – As we are calling ora_label_print_pkg.inv_trx_label_print inside INV_TXNSTUB_PUB.POSTTRANSACTION, the custom procedure calls collects the data and inserts a record into custom table (ORA_LABEL_PRINT_TAB) on which we have trigger which fires custom label print program.
2- LPN Transactions – lpn consolidate label print
Prerequisite
Set the WMS label processing mode profile (WMS: Label Print Mode) to synchronous
Label format
Warehouse manager Navigation:- Setup/Warehouse Configuration/Define label format
You can define you own format or use existing format once defined make it default
For example I have defined custom format under LPN content label type as “Custom LPN Content”
The label format contents minimum fields which I wanted.
Assign label types to Business flows
Warehouse manager Navigation:- Setup/Warehouse Configuration/Assign label types to Business flows
Here I am assigning label type to business flow. Business flow is actually transactions like ship confirm, po receipt or pack/unpack lpns.
Complete the lpn consolidate transaction from MSCA as below
Once success check the label history
You will see the xml generated under LPN Content which is essentially same passed to package INV_SYNC_PRINT_REQUEST.SYNC_PRINT_REQUEST parameter p_xml_content
Label content
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE labels SYSTEM "label.dtd">
<labels _FORMAT="Custom LPN Content" _QUANTITY="1" _PRINTERNAME="LabelPDF" _JOBNAME="LBL17603">
<label>
<variable name= "ITEM">ORATEST_WMS_ITEM</variable>
<variable name= "LOT">04MAR11</variable>
<variable name= "LPN">LPN402A</variable>
<variable name= "WHS">OWH</variable>
<variable name= "QTY">1</variable>
<variable name= "REQUEST_USER">ORATEST</variable>
</label>
</labels>
Note: - The custom package ora_label_print_pkg.lpnsplit_consolidate_data (p_xml_content)
Processes the received xml data with parameter p_xml_content and loads into custom label print table which then fires the label print program.
Label generated as
1- Label Reprint
Considering we have done the plumbing work to get the labels generated during various part of process, we will still need the label re-print mechanism.
Using the reprinting mechanism user can re-print the label whenever needs to be.
Re-printing is needed in case where original label is lost or any other reason.
I have defined a plsql concurrent program based procedure ora_label_print_pkg.reprint_label_data.
The idea behind the re-print label program is show all onhand lpn’s available (you can have lot number as well) and let user select which lpn it needs to be reprinted.
To do this I have created a table value set based on below view
CREATE OR REPLACE FORCE VIEW ORA_GET_LPN_ONHNAD
as
SELECT ROWIDTOCHAR (moqd.ROWID) row_id,
wlpn.license_plate_number lpn,
msib.segment1 item_number,
moqd.lot_number,
mp.organization_code whs,
moqd.subinventory_code,
(SELECT mil.segment1 LOCATION
FROM mtl_item_locations mil
WHERE moqd.locator_id = mil.inventory_location_id
AND moqd.organization_id = mil.organization_id)
LOCATOR,
moqd.transaction_quantity qty,
moqd.inventory_item_id,
moqd.organization_id,
moqd.locator_id,
moqd.lpn_id
FROM mtl_onhand_quantities_detail moqd,
mtl_parameters mp,
mtl_system_items_b msib,
wms_license_plate_numbers wlpn
WHERE moqd.organization_id = mp.organization_id
AND moqd.inventory_item_id = msib.inventory_item_id
AND moqd.organization_id = msib.organization_id
AND moqd.lpn_id = wlpn.lpn_id
The value set lets user to select the lpn number and then passes the rowed of onhand quantities table.
The reprint program then picks required columns from onhand table and then inserts record into custom label print program which eventually files the actual label print.
Label reprinted as
Printer Setups
System administrator Navigation:- Install/Printer/Register
Technical insight-
Explained as shown below.
Concurrent programs created
ORA Label Print Program
Make sure the label print program has the style selected as Intermec.
Concurrent programs created
ORA Label Print Program
Read the XML and place the values in oracle table
I have defined a procedure which reads the XML data and inserts the values into table
For example see below
Table data
DROP SEQUENCE ORA_LABEL_PRINT_TAB_s;
CREATE SEQUENCE ora_label_print_tab_s
START WITH 1
MAXVALUE 9999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
ORDER;
DROP TABLE ora_label_print_tab;
CREATE TABLE ora_label_print_tab
(
TAG_PRINT_ID NUMBER,
TRANSACTION_ID NUMBER,
TRANSACTION_DATE DATE,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_TYPE_NAME VARCHAR2(100 BYTE),
INVENTORY_ITEM_ID NUMBER,
ITEM_NUMBER VARCHAR2(100 BYTE),
CASE_UPC_CODE VARCHAR2(100 BYTE),
ITEM_DESCRIPTION VARCHAR2(200 BYTE),
LOT_NUMBER VARCHAR2(100 BYTE),
LPN_ID NUMBER,
TRANSFER_LPN_ID NUMBER,
INNER_LPN VARCHAR2(100 BYTE),
OUTER_LPN VARCHAR2(100 BYTE),
TRANSACTION_QUANTITY NUMBER,
TRANSACTION_UOM VARCHAR2(100 BYTE),
ORGANIZATION_ID NUMBER,
ORGANIZATION_CODE VARCHAR2(100 BYTE),
SUBINVENTORY_CODE VARCHAR2(100 BYTE),
LOCATOR_ID NUMBER,
LOCATOR VARCHAR2(100 BYTE),
LOT_EXPIRATION_DATE DATE,
TRANSACTION_SOURCE_ID NUMBER,
BATCH_NO VARCHAR2(100 BYTE),
BATCH_MACHINE VARCHAR2(100 BYTE),
BATCH_LINE VARCHAR2(100 BYTE),
BATCH_SHIFT VARCHAR2(100 BYTE),
PALLET_ID VARCHAR2(100 BYTE),
ITEM_TAG_TYPE VARCHAR2(100 BYTE),
REQUEST_USER VARCHAR2(2000 BYTE),
PRINTER_NAME VARCHAR2(100 BYTE),
PRINT_COPIES VARCHAR2(100 BYTE),
REPRINT_REQUEST_ID NUMBER,
REQUEST_ID NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
ORIG_SYS_REFERENCE VARCHAR2(200 BYTE),
STATUS VARCHAR2(10 BYTE),
ERROR_MESSAGE VARCHAR2(2000 BYTE)
)
Trigger code-
CREATE OR REPLACE TRIGGER ora_label_print_tab_trg
BEFORE INSERT
ON ora_label_print_tab
FOR EACH ROW
DECLARE
-- +------------------------------------------------------------+
-- + Variable Declaration +
-- +------------------------------------------------------------+
g_debug_point VARCHAR2 (50);
l_resp_id NUMBER := NULL;
l_resp_appl_id NUMBER := NULL;
l_request_id NUMBER := NULL;
l_result BOOLEAN;
l_result1 BOOLEAN;
e_user_null EXCEPTION;
BEGIN
g_debug_point := 'TRG_1000';
-- IF :NEW.created_by is null then
-- raise e_user_null;
-- END IF;
-- +------------------------------------------------------------+
-- + Get the Resp ID for 'Warehouse Manager', aka the 'WMS' key
-- +------------------------------------------------------------+
BEGIN
g_debug_point := 'TRG_1020';
l_resp_id := NULL;
l_resp_appl_id := NULL;
SELECT responsibility_id
,application_id
INTO l_resp_id
,l_resp_appl_id
FROM apps.fnd_responsibility
WHERE responsibility_key = 'WMS'
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_resp_id := 0;
l_resp_appl_id := 0;
END;
-- +------------------------------------------------------------+
-- + Initialize APPS Environment +
-- +------------------------------------------------------------+
g_debug_point := 'TRG_1030';
/*
BEGIN
fnd_global.apps_initialize (:NEW.created_by, l_resp_id, l_resp_appl_id);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
*/
IF l_resp_id IS NOT NULL
AND l_resp_appl_id IS NOT NULL
THEN
--fnd_global.apps_initialize (:NEW.created_by, l_resp_id, l_resp_appl_id);
NULL;
ELSE
:NEW.status := 'E';
:NEW.error_message :=
'Error at (' || g_debug_point || '):' || ' Error getting the responsibility id for responsibility key- WMS';
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END IF;
-- +------------------------------------------------------------+
-- + Launch the printing program
-- +------------------------------------------------------------+
g_debug_point := 'TRG_1100';
l_result := fnd_request.set_mode (TRUE);
l_result1 := fnd_request.set_print_options (printer => :NEW.printer_name
,copies => :NEW.print_copies);
g_debug_point := 'TRG_1110';
l_request_id :=
fnd_request.submit_request ('WMS' -- application_short_name
,'ORA_LABEL_PRINT' -- concurrent program name
,NULL -- description
,NULL -- start time
,FALSE -- sub_request
,:NEW.tag_print_id
,CHR (0)
);
--COMMIT;
IF NVL (l_request_id, -1) > 0
THEN
g_debug_point := 'TRG_1120';
:NEW.request_id := l_request_id;
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
ELSE
:NEW.status := 'E';
:NEW.error_message :=
SUBSTR ( 'Error at ('
|| g_debug_point
|| '):'
|| '1Error submitting conc program- ORA_LABEL_PRINT printer '
|| :NEW.printer_name
|| ' SQLMSG: '
|| SQLERRM
,1
,2000
);
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END IF;
EXCEPTION
WHEN e_user_null
THEN
NULL;
WHEN OTHERS
THEN
:NEW.status := 'E';
:NEW.error_message :=
SUBSTR ('Error at (' || g_debug_point || '):' || '2Error submitting conc program- ORA_LABEL_PRINT ' || 'SQLMSG: ' || SQLERRM
,1
,2000
);
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END ora_label_print_tab_trg;
/
Package code-
CREATE OR REPLACE PACKAGE apps.ora_label_print_pkg AUTHID CURRENT_USER
AS
-- +------------------------------------------------------------+
-- Reprinting the lable procedure called by reprint conc program
-- +------------------------------------------------------------+
PROCEDURE reprint_label_data (
p_errbuf OUT VARCHAR2
,p_retcode OUT NUMBER
,p_onhand_qty_rec_rowid IN VARCHAR2
);
-- +------------------------------------------------------------+
-- called inside INV_TXNSTUB_PUB.POSTTRANSACTION for label prints like po receipt WOL completion
-- +------------------------------------------------------------+
PROCEDURE inv_trx_label_print (
p_transaction_id IN NUMBER
);
-- +------------------------------------------------------------+
-- called inside INV_SYNC_PRINT_REQUEST.SYNC_PRINT_REQUEST for lpn transactions label print
-- +------------------------------------------------------------+
PROCEDURE lpnsplit_consolidate_data (
p_xml_content IN LONG
);
PROCEDURE print_label (
p_errbuf OUT VARCHAR2
,p_retcode OUT NUMBER
,p_tag_print_id IN NUMBER
);
END ora_label_print_pkg;
/
CREATE OR REPLACE PACKAGE BODY apps.ora_label_print_pkg
AS
g_printer_name CONSTANT VARCHAR2 (50) := 'D41';
PROCEDURE write_line (
p_buffer_line IN VARCHAR2
)
IS
BEGIN
-- +------------------------------------------------------------+
-- Write the buffer.
-- +------------------------------------------------------------+
-- Write buffer to the Concurrent Manager output.
fnd_file.put_line (fnd_file.output, p_buffer_line);
DBMS_OUTPUT.put_line (p_buffer_line);
END write_line;
FUNCTION get_xml_variable (
p_xml IN LONG
,p_variable_name IN VARCHAR
)
RETURN VARCHAR2
IS
x_xml_value VARCHAR2 (100) := NULL;
l_session VARCHAR2 (1000) := NULL;
BEGIN
BEGIN
x_xml_value := NULL;
-- +------------------------------------------------------------+
-- +
-- +------------------------------------------------------------+
l_session := 'alter session set events =''31156 trace name context forever, level 2''';
EXECUTE IMMEDIATE l_session;
SELECT TRIM (EXTRACTVALUE (VALUE (x), '//variable'))
INTO x_xml_value
FROM (SELECT XMLTYPE (p_xml) xml
FROM DUAL), TABLE (XMLSEQUENCE (EXTRACT (xml, 'labels/label/variable'))) x
WHERE UPPER (EXTRACTVALUE (VALUE (x), '//@name')) = UPPER (p_variable_name);
EXCEPTION
WHEN OTHERS
THEN
x_xml_value := NULL;
END;
RETURN x_xml_value;
END get_xml_variable;
-- +------------------------------------------------------------+
-- | get the extracted values from xml_content
-- +------------------------------------------------------------+
PROCEDURE lpnsplit_consolidate_data (
p_xml_content IN LONG
)
IS
r_ora_label_print_tab ora_label_print_tab%ROWTYPE;
BEGIN
fnd_file.put_line (fnd_file.LOG, '+----------------------------------------------+');
fnd_file.put_line
(fnd_file.LOG
,'Uses LPNSPLIT_CONSOLIDATE_DATA- Tags printing from INV_SYNC_PRINT_REQUEST.SYNC_PRINT_REQUEST (WMS LABEL PRINTING) for trx types LPN split(89) and LPN pack(87)');
r_ora_label_print_tab.lot_number := get_xml_variable (p_xml_content, 'LOT');
r_ora_label_print_tab.request_user := get_xml_variable (p_xml_content, 'REQUEST_USER');
r_ora_label_print_tab.inner_lpn := get_xml_variable (p_xml_content, 'LPN');
r_ora_label_print_tab.item_number := get_xml_variable (p_xml_content, 'ITEM');
r_ora_label_print_tab.organization_code := get_xml_variable (p_xml_content, 'WHS');
r_ora_label_print_tab.transaction_quantity := get_xml_variable (p_xml_content, 'QTY');
INSERT INTO ORA_LABEL_PRINT_TAB
(tag_print_id
,item_number
,lot_number
,inner_lpn
,printer_name
,print_copies
,created_by
)
VALUES (ora_label_print_tab_s.nextval
,r_ora_label_print_tab.item_number
,r_ora_label_print_tab.lot_number
,r_ora_label_print_tab.inner_lpn
,g_printer_name
,1
,6895
);
END;
-- +------------------------------------------------------------+
-- | reprint_label_data
-- +------------------------------------------------------------+
PROCEDURE reprint_label_data (
p_errbuf OUT VARCHAR2
,p_retcode OUT NUMBER
,p_onhand_qty_rec_rowid IN VARCHAR2
)
IS
CURSOR c_get_onhand_details
IS
SELECT msi.segment1 item_number
, (SELECT wlpn.license_plate_number
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = moqd.lpn_id) inner_lpn
, (SELECT mil.segment1 LOCATION
FROM mtl_item_locations mil
WHERE moqd.locator_id = mil.inventory_location_id
AND moqd.organization_id = mil.organization_id) LOCATOR
,moqd.*
FROM mtl_onhand_quantities_detail moqd, mtl_system_items msi
WHERE moqd.inventory_item_id = msi.inventory_item_id
AND moqd.organization_id = msi.organization_id
AND moqd.ROWID = p_onhand_qty_rec_rowid;
BEGIN
fnd_file.put_line (fnd_file.LOG, '+----------------------------------------------+');
fnd_file.put_line (fnd_file.LOG, 'Reprinting the label');
FOR r_get_onhand_details IN c_get_onhand_details
LOOP
INSERT INTO ora_label_print_tab
(tag_print_id
,item_number
,lot_number
,inner_lpn
,printer_name
,print_copies
,created_by
)
VALUES (ora_label_print_tab_s.nextval
,r_get_onhand_details.item_number
,r_get_onhand_details.lot_number
,r_get_onhand_details.inner_lpn
,g_printer_name
,1
,6895
);
END LOOP;
END reprint_label_data;
-- +------------------------------------------------------------+
-- | INVSTUB procedure
-- +------------------------------------------------------------+
PROCEDURE inv_trx_label_print (
p_transaction_id IN NUMBER
)
IS
CURSOR c_get_onhand_details
IS
SELECT mmt.transaction_id
,mmt.creation_date transaction_date
,mtt.transaction_type_id
,mtt.transaction_type_name
,msi.inventory_item_id
,msi.segment1 item_number
,mtln.lot_number
,ABS (mmt.transaction_quantity) transaction_quantity
,mmt.transaction_uom
,mmt.organization_id
,mp.organization_code
,mmt.subinventory_code
, (SELECT wlpn.license_plate_number
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = NVL (NVL (mmt.lpn_id, mmt.transfer_lpn_id), mmt.content_lpn_id)) inner_lpn
, (SELECT mil.segment1 LOCATION
FROM mtl_item_locations mil
WHERE mmt.locator_id = mil.inventory_location_id
AND mmt.organization_id = mil.organization_id) LOCATOR
,1 print_copies
,fu.user_name request_user
,mmt.created_by
FROM mtl_system_items msi
,mtl_material_transactions mmt
,mtl_transaction_types mtt
,mtl_parameters mp
,fnd_user fu
,mtl_transaction_lot_numbers mtln
WHERE msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.organization_id = mp.organization_id
AND fu.user_id = mmt.created_by
AND mmt.inventory_item_id = mtln.inventory_item_id
AND mmt.organization_id = mtln.organization_id
AND mmt.transaction_id = mtln.transaction_id
AND mmt.transaction_id = p_transaction_id;
BEGIN
fnd_file.put_line (fnd_file.LOG, '+----------------------------------------------+');
fnd_file.put_line (fnd_file.LOG
,'Tags printing from INV_TXNSTUB_PUB.POSTTRANSACTION for trx types like PO Receipt(18) and WIP Completion(44)');
FOR r_get_onhand_details IN c_get_onhand_details
LOOP
INSERT INTO ora_label_print_tab
(tag_print_id
,item_number
,lot_number
,inner_lpn
,printer_name
,print_copies
,created_by
)
VALUES (ora_label_print_tab_s.nextval
,r_get_onhand_details.item_number
,r_get_onhand_details.lot_number
,r_get_onhand_details.inner_lpn
,g_printer_name
,1
,6895
);
END LOOP;
END inv_trx_label_print;
PROCEDURE print_label (
p_errbuf OUT VARCHAR2
,p_retcode OUT NUMBER
,p_tag_print_id IN NUMBER
)
IS
CURSOR c_label_data
IS
SELECT *
FROM ora_label_print_tab
WHERE tag_print_id = p_tag_print_id;
BEGIN
FOR r_label_data IN c_label_data
LOOP
fnd_file.put_line (fnd_file.LOG, '+----------------------------------------------+');
fnd_file.put_line (fnd_file.LOG, ' TAG PRINTING STARTS');
fnd_file.put_line (fnd_file.LOG, '+----------------------------------------------+');
write_line ('<STX><ESC>c<ETX>'); -- Set 86xx emulation mode.
write_line ('<STX><ESC>P<ETX>'); -- Enter program mode.
write_line ('<STX>E4;F4;<ETX>'); -- Erase previous format #4.
write_line ('<STX>H1;o580,20;f3;c20;h1;w1;d3, Custom Lable Print - ORATEST;<ETX>');
write_line ('<STX>B2;o550,30;f3;c6,0,1;h100;w2.85;i0;d3,' || r_label_data.inner_lpn || ';p@;<ETX>');
-- Print LPN barcode
write_line ('<STX>H3;o450,20;f3;c20;h1;w1;d3,' || 'LPN Number:' || r_label_data.inner_lpn || ';<ETX>');
write_line ('<STX>L4;o425,10;f3;l440;w2;<ETX>'); -- Line below barcode and item number
write_line ('<STX>B5;o400,30;f3;c6,0,1;h100;w2.85;i0;d3,' || r_label_data.item_number || ';p@;<ETX>');
-- Print item barcode
write_line ('<STX>H6;o300,20;f3;c20;h1;w1;d3,' || 'Item Number:' || r_label_data.item_number || ';<ETX>');
write_line ('<STX>B7;o250,30;f3;c6,0,1;h100;w2.85;i0;d3,' || r_label_data.lot_number || ';p@;<ETX>');
-- Print lot barcode
write_line ('<STX>H8;o150,20;f3;c20;h1;w1;d3,' || 'LOT Number:' || r_label_data.lot_number || ';<ETX>');
write_line ('<STX>H16;o53,20;f3;c30;h1;w1;d3,' || r_label_data.inner_lpn || ';<ETX>');
write_line ('<STX>B17;o35,20;f3;c6,0,1;h30;w1;i0;d3,' || r_label_data.inner_lpn || ';p@;<ETX>');
write_line ('<STX>H18;o38,225;f3;c30;h2;w1;d3,' || TO_CHAR (SYSDATE, 'MON/DD/RR HH24:MI:SS') || ';<ETX>');
write_line ('<STX>R<ETX>'); -- Enter data mode
write_line ('<STX><ESC>E4<CAN><ETX>'); -- Access format #4/clear data
write_line ('<STX><ETB><ETX>'); -- Print the label
END LOOP;
END print_label;
END ora_label_print_pkg;
/
Whereas code below is an actual code which gives printing instructions to Intermec printer.
fnd_file.put_line (fnd_file.LOG, '+----------------------------------------------+');
fnd_file.put_line (fnd_file.LOG, ' TAG PRINTING STARTS');
fnd_file.put_line (fnd_file.LOG, '+----------------------------------------------+');
write_line ('<STX><ESC>c<ETX>'); -- Set 86xx emulation mode.
write_line ('<STX><ESC>P<ETX>'); -- Enter program mode.
write_line ('<STX>E4;F4;<ETX>'); -- Erase previous format #4.
write_line ('<STX>H1;o580,20;f3;c20;h1;w1;d3, Custom Lable Print - ORATEST;<ETX>');
write_line ('<STX>B2;o550,30;f3;c6,0,1;h100;w2.85;i0;d3,' || r_label_data.inner_lpn || ';p@;<ETX>');
-- Print LPN barcode
write_line ('<STX>H3;o450,20;f3;c20;h1;w1;d3,' || 'LPN Number:' || r_label_data.inner_lpn || ';<ETX>');
write_line ('<STX>L4;o425,10;f3;l440;w2;<ETX>'); -- Line below barcode and item number
write_line ('<STX>B5;o400,30;f3;c6,0,1;h100;w2.85;i0;d3,' || r_label_data.item_number || ';p@;<ETX>');
-- Print item barcode
write_line ('<STX>H6;o300,20;f3;c20;h1;w1;d3,' || 'Item Number:' || r_label_data.item_number || ';<ETX>');
write_line ('<STX>B7;o250,30;f3;c6,0,1;h100;w2.85;i0;d3,' || r_label_data.lot_number || ';p@;<ETX>');
-- Print lot barcode
write_line ('<STX>H8;o150,20;f3;c20;h1;w1;d3,' || 'LOT Number:' || r_label_data.lot_number || ';<ETX>');
write_line ('<STX>H16;o53,20;f3;c30;h1;w1;d3,' || r_label_data.inner_lpn || ';<ETX>');
write_line ('<STX>B17;o35,20;f3;c6,0,1;h30;w1;i0;d3,' || r_label_data.inner_lpn || ';p@;<ETX>');
write_line ('<STX>H18;o38,225;f3;c30;h2;w1;d3,' || TO_CHAR (SYSDATE, 'MON/DD/RR HH24:MI:SS') || ';<ETX>');
write_line ('<STX>R<ETX>'); -- Enter data mode
write_line ('<STX><ESC>E4<CAN><ETX>'); -- Access format #4/clear data
write_line ('<STX><ETB><ETX>'); -- Print the label
Trigger code (Tigger on table ora_label_print_tab)
CREATE OR REPLACE TRIGGER ora_label_print_tab_trg
BEFORE INSERT
ON ora_label_print_tab
FOR EACH ROW
DECLARE
-- +------------------------------------------------------------+
-- + Variable Declaration +
-- +------------------------------------------------------------+
g_debug_point VARCHAR2 (50);
l_resp_id NUMBER := NULL;
l_resp_appl_id NUMBER := NULL;
l_request_id NUMBER := NULL;
l_result BOOLEAN;
l_result1 BOOLEAN;
e_user_null EXCEPTION;
BEGIN
g_debug_point := 'TRG_1000';
-- IF :NEW.created_by is null then
-- raise e_user_null;
-- END IF;
-- +------------------------------------------------------------+
-- + Get the Resp ID for 'Warehouse Manager', aka the 'WMS' key
-- +------------------------------------------------------------+
BEGIN
g_debug_point := 'TRG_1020';
l_resp_id := NULL;
l_resp_appl_id := NULL;
SELECT responsibility_id
,application_id
INTO l_resp_id
,l_resp_appl_id
FROM apps.fnd_responsibility
WHERE responsibility_key = 'WMS'
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_resp_id := 0;
l_resp_appl_id := 0;
END;
-- +------------------------------------------------------------+
-- + Initialize APPS Environment +
-- +------------------------------------------------------------+
g_debug_point := 'TRG_1030';
/*
BEGIN
fnd_global.apps_initialize (:NEW.created_by, l_resp_id, l_resp_appl_id);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
*/
IF l_resp_id IS NOT NULL
AND l_resp_appl_id IS NOT NULL
THEN
--fnd_global.apps_initialize (:NEW.created_by, l_resp_id, l_resp_appl_id);
NULL;
ELSE
:NEW.status := 'E';
:NEW.error_message :=
'Error at (' || g_debug_point || '):' || ' Error getting the responsibility id for responsibility key- WMS';
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END IF;
-- +------------------------------------------------------------+
-- + Launch the printing program
-- +------------------------------------------------------------+
g_debug_point := 'TRG_1100';
l_result := fnd_request.set_mode (TRUE);
l_result1 := fnd_request.set_print_options (printer => :NEW.printer_name
,copies => :NEW.print_copies);
g_debug_point := 'TRG_1110';
l_request_id :=
fnd_request.submit_request ('WMS' -- application_short_name
,'ORA_LABEL_PRINT' -- concurrent program name
,NULL -- description
,NULL -- start time
,FALSE -- sub_request
,:NEW.tag_print_id
,CHR (0)
);
--COMMIT;
IF NVL (l_request_id, -1) > 0
THEN
g_debug_point := 'TRG_1120';
:NEW.request_id := l_request_id;
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
ELSE
:NEW.status := 'E';
:NEW.error_message :=
SUBSTR ( 'Error at ('
|| g_debug_point
|| '):'
|| '1Error submitting conc program- ORA_LABEL_PRINT printer '
|| :NEW.printer_name
|| ' SQLMSG: '
|| SQLERRM
,1
,2000
);
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END IF;
EXCEPTION
WHEN e_user_null
THEN
NULL;
WHEN OTHERS
THEN
:NEW.status := 'E';
:NEW.error_message :=
SUBSTR ('Error at (' || g_debug_point || '):' || '2Error submitting conc program- ORA_LABEL_PRINT ' || 'SQLMSG: ' || SQLERRM
,1
,2000
);
:NEW.last_update_date := SYSDATE;
:NEW.last_updated_by := :NEW.created_by;
END ora_label_print_tab_trg;
/
This explains WMS Label print in Oracle R12
Thanks,
Ajay