Back to Back orders in OM

Monday, December 10, 2012 by Ajay Atre



Back to Back orders in OM

Back to back order
Customer places order to you, you place the order to your supplier. Your supplier ships the goods to you and in turn you ship the goods to customer.
You can use the scenario when
·         You do not have stock or inventory
·         You do not manufacture
Cycle-
Create a Sales Order with line where the line source is Internal
Book the order status will be supply eligible, progress the order status will be external req requested
In purchasing run the requisition import with import source as CTO
Auto create PO, approve PO. Order line status will be PO open
Receive against the PO order status will be awaiting shipping.
Then pick release and ship the sales order

Drop Shipment is a process where the customer places a purchase order on a company and the company instructs its supplier to directly ship the items to the customer.
Drop Shipment Process Steps
Create a Sales Order with line where the line source is External
Book and Schedule the Sales Order
Run workflow background Process
Now the line status will be in Awaiting Receipt
Login to the Receiving Organization (Purchasing) who has been setup as an Approver
Run Requisition Import Process
Auto create to Create Purchase Order
Approve the PO
Receive against the purchase Order
Run Auto Invoice
Verify Invoice in Sales Order
Oracle Order Management uses purchase orders to outside suppliers that are automatically generated from sales orders for goods supplied directly from the supplier. The external supplier ships the goods directly to the 3rd Party customer and confirms the shipment through the use of an Advanced Shipment Notice (ASN).
What are the different statuses for a drop ship order?
Entered àBooked àAwaiting Receipt à Closed

Back to back order Vs Drop Ship in OM

Difference between drop shipment and back to back order 
In B2B the source will be internal but the item would be procured after the order is created 
In Drop Ship the source will be external
In Drop Ship orders, material is directly shipped to the customer from the supplier. Thus, inventory is not affected. In this case, only logical receiving is done. But in the case of Back-to-Back orders, material is taken from inventory.



Back to Back order Flow
Step#1 Item attributes
Inventory super user- master items
In the Order Management tab, check the Assemble to Order flag (ATO).
In the Purchasing tab, enter a value in the List Price field.
In the Work in Process tab, make sure the Build in WIP flag is checked.
In the General Planning tab, set the Make or Buy flag to Buy.
Save, and assign to Organization M1.




Step#2 Enter the Sales Order
The responsibility to be used is Order Management Super User, Vision Operations (USA)



Book the Order; note the order line status as ‘Supply eligible’ immediately after booking the order.

Progress the order line- right click on the line and select progress order. And then select activity as “create supply order- eligible”


Once clicked ok note the line status as “External Req Requested”


From backend you can see the requisition is created into requisition interface for the order line id as 407416

select * from po_requisitions_interface_all
where interface_source_code = 'CTO'
and trunc(sysdate) = trunc(creation_date);



Step#3 Requisition import
Responsibility- Order Management Super User, Vision Operations (USA)
Orders, Returns menu - choose 'Requisition Import'
 - Import Source will be CTO and Initiating Approval is not required as the record was inserted into the po_requisitions_interface_all table having a status of 'Approved'.  The status will always be Approved.
 - Submit the Request and then close the Requests Form


Step#4 Auto create the PO
Navigate to Purchasing/Requisitions Summary Find the requisition by item number



Auto create the PO based on the requisition above. Click on auto create and enter requisition number
Navigate Purchasing/Autocreate

Select the line and click automatic




A Supplier should default, click the Create Button. If a Supplier and Site did not default, choose any Supplier - Allied Manufacturing/LOS ANGELES for example.

A Purchase Order is then created.

Once the PO is approved you can see the order status as “PO OPEN”


Step#5- Create the receipt
Navigate to Purchasing/Receiving/Receipts  and select M1 as the inventory organization - as this was the inventory organization used on the Purchase Order Shipment 

Select the line enter the subinventory and locator (if needed) and save the record.


View the receiving transactions of the receipt done above
Navigate to Purchasing/Receiving/Receiving Transactions Summary and then enter the Receipt Number or PO number and Choose Find




Once received come back on the order and note the line status as awaiting shipping.


You can then ship the order from here.


This explains back to back order flow  in Oracle R12

Thanks,
Ajay

Filed under , , , , having 9 comments

Modifiers in OM

Friday, December 7, 2012 by Ajay Atre



Modifiers in OM
Requirement-
Apply a header level fix freight charge, apply the discount and surcharge.
1.   Freight and Special charge List
Creating the fixed price charge with lumpsum value of $11.
Oracle Pricing manager Navigation:- Modifiers- Modifiers
Select type as Freight and Special charge List
Enter name and number; I am entering both of them as ORA Fixed Freight
On the modifier summary tab enter the modifier number or use the auto generated number
Select level, modifier type and select pricing phrase (like where you want to apply the charge at order header level or line level).


On the discounts tab,
Enter charge name – for example Freight Costs
Also select application method – which is Lumpsum here
Enter how much value charge should be. I am entering $11 here.

Save the work.

Qualifiers –
We can restrict the modifier by entering the qualifier as customer name.
But before we do so we need to setup qualifiers
Navigate to Qualifier setup- Create group and enter the attribute


Once done come back on the modifier click on the line qualifiers


Once done save the work and run below request.


2.   Create Discount modifier
Select type as Discount list and then enter name and number (which is ORA Low Price Discount)
Enter below details under modifier summary tab
Clicking automatic check box will automatically apply the modifier.




Line qualifiers


3.   Surcharge modifier using pricing formula
Create Formula- I am creating calculation as NVL(10,20) where 10 &20 are steps.
The logic is for the for item ORATEST_ORDER_ITEM I need surcharge of .3 so adding the same under factor list. And for rest of the items the value will be 0.




Save the formula and run below request.


Once done create modifier of surcharge type


Under discounts/charges specify the formula you have created.
Please note I am not entering any value here as the value will be applied from formula itself.


View the modifiers
Save the work and come back to order management and create a order you shall see the modifier and header level charge is being applied.



You can look the header level charge by actions and select charges




Similarly click on the order line, click on actions and select View Adjustment.
It will show the discount modifier being applied.




Note the list line which is the actual modifier number.

This explains Modifiers in Oracle R12

Thanks,
Ajay

Filed under , , having 6 comments

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 47 comments

Subscribe to: Posts (Atom)

Search This Blog

Labels

About Me

Ajay Atre
View my complete profile

Total Pageviews

Followers