Label Printing with WMS

Wednesday, November 21, 2012 by Ajay Atre


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.


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.
 This program prints a label as


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
 System administrator Navigation:- Install/Printer/types
 System administrator Navigation:- Install/Printer/style
 System administrator Navigation:- Install/Printer/Driver


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






Filed under , having 45 comments