Technical‎ > ‎MSCA‎ > ‎

Oracle MSCA - Bar code preprocessing using CustomScanManager


Oracle MSCA - Bar code preprocessing using CustomScanManager

0digg
I was recently involved in a project needed a translation of the bar code into an oracle defined value. i.e The barcode represented a cross reference and this had to be translated into an Oracle part number. This required a pre-processing of the barcode scan.

Log into your Apps and go to the Profile ? System ? Find System Profile Values: MWA% and modify the two attributes below.

        MWA: Debug Trace = Yes

        MWA: Pre-process scanned input = Yes

        The above values need to be altered regardless of you using PL/SQL or JAVA.

    PL/SQL Pre-Processing Scan

You need to decide if you’re going to use PL/SQL or JAVA. The pre-processing scan will try to call your JAVA class first. If it finds it then it will use that one and ignore your PL/SQL package.

    
In order to do any type of pre-process scanning you must have the above Pre-process scanned input set to Yes as above.

APPS.INV_PREPROCESS_SCAN.process_scan is the Oracle supplied package/procedure that is called before anything is done on the form. I will dissect my code later on in this article.

The only drawback to using the PL/SQL callout is that it does not support out of order scanning. This will be accomplished with the Java based custom scan manager.

NOTE  Your barcode gun must be setup to prefix the code that matches what is in your mfg.cfg. The default is CTRL+\ so you’ll need to check with your barcode gun manufacturer to add this. Your debug code will show you the below line if your code is being called:

INV_PREPROCESS_SCAN

This PL/SQL procedure just basically does a legacy barcode cross-reference scan. It’s pretty straight forward.

CREATE OR REPLACE PACKAGE BODY apps.inv_preprocess_scan AS

  PROCEDURE process_scan(x_return_status OUT NOCOPY VARCHAR2,
                        
                         x_processed_value OUT NOCOPY VARCHAR2,
                        
                         p_current_page_name IN VARCHAR2,
                        
                         p_scanned_value IN VARCHAR2) IS
 
    c_master_org NUMBER := 132; -- 84 is the master org
 
  BEGIN
 
    IF (p_current_page_name = 'MAIN_PICK_PAGE')
    THEN
      -- Check for the new scanned barcode first
      SELECT DISTINCT msi.concatenated_segments
        INTO x_processed_value
        FROM mtl_system_items_vl  msi,
             mtl_cross_references mcr
       WHERE msi.organization_id = c_master_org
       NUMBER
         AND ((mcr.cross_reference_type =
             fnd_profile.value('INV:CROSS_REFERENCE_TYPE') AND
             mcr.cross_reference LIKE p_scanned_value))
         AND ((mcr.org_independent_flag = 'Y') OR
             (mcr.org_independent_flag = 'N' AND
             mcr.organization_id = msi.organization_id))
         AND mcr.inventory_item_id = msi.inventory_item_id;
   
    END IF;
 
  EXCEPTION
 
    WHEN no_data_found THEN
   
      -- Check the cross reference table
   
      RETURN p_scanned_value;
   
  END process_scan;

END inv_preprocess_scan;

/
Java Based Custom Scan Manager

    

You can use any IDE to create your CustomScanManager.class, but I used JDeveloper. In order for it to be called the pre-processing scanned input profile must be set to “Yes”. The .class file also needs to be placed in the $CUSTOM_DIRECTORY as specified by your DBA. Most people create a directory in the root of $JAVA_TOP (ex: $JAVA_TOP/xxx/custom). You must have a $CUSTOM_DIRECTORY set up in order for oracle to know where to look for your .class file.
You can create the directory under $XX_TOP/java/xxx/custom. If you do this the entry / change must be made to AF_CLASSPATH. Oracle MSCA relies on AF_CLASSPATH and not on CLASSPATH.

NOTE – Your barcode gun must be setup to prefix the code that matches what is in your mfg.cfg. The default is CTRL+\ so you’ll need to check with your barcode gun manufacturer to add this.
For the british keyboard I noticed that ctrl+~ would work and not ctrl+\. 

I am not sure why this is the case but I discovered this purely by chance.
Your debug code will show you the below line if your code is being called:


CustomScanManager

package xxx.custom;

import oracle.apps.inv.utilities.server.UtilFns;

import oracle.apps.mwa.beans.*;

import oracle.apps.mwa.container.FileLogger;
import oracle.apps.mwa.container.Session;

import oracle.jdbc.*;

import oracle.jdbc.driver.OracleConnection;

import java.math.BigDecimal;

import java.sql.*;

import java.util.Locale;
import java.util.Vector;


/**
 * Class to manage the scans. This class will pre-process scans.
 * CustomScanManager is the customizable dallout to pre-process a scanned
 * barcode. it has reference to the current MWA Session, and the page the scan
 * was performed on. Once can access the database session using the MWA
 * session passed in. Please refer to the MWA API documetnation or the WMS
 * customization document for more information on ways to customize the class.
 */
public class CustomScanManager
{

    /**
     * The query string for querying cross references for a given item if it
     * exists.
     */
    private static final String CROSS_REF_SQL_STR =
        "SELECT DISTINCT mcr.cross_reference,\n"
        + "                msi.inventory_item_id,                                            \n"
        + "                msi.CONCATENATED_SEGMENTS                                         \n"
        + "           FROM mtl_system_items_vl msi,                                          \n"
        + "                mtl_cross_references mcr                                          \n"
        + "          WHERE msi.organization_id = :1                                          \n"
        + "            AND ((mcr.cross_reference_type = fnd_profile.value('INV:CROSS_REFERENCE_TYPE') \n"
        + "AND mcr.cross_reference LIKE :2                                                   \n"
        + "                 )                                                                \n"
        + "                )                                                                 \n"
        + "            AND (   (mcr.org_independent_flag = 'Y')                              \n"
        + "                 OR (mcr.org_independent_flag = 'N'                               \n"
        + "                     AND mcr.organization_id = msi.organization_id                \n"
        + "                    )                                                             \n"
        + "                )                                                                 \n"
        + "            AND mcr.inventory_item_id = msi.inventory_item_id                     \n";

    /** DOCUMENT ME! */
    private static OracleConnection con;

    static
    {

        try
        {
            Class.forName("oracle.jdbc.OracleDriver");
            Locale.setDefault(Locale.US);
            con = (OracleConnection) DriverManager.getConnection("jdbc:oracle:thin:apps/appsdev@fcebsdbfrm01.na.gilead.com:1551:erpdev");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }


    private static final String MAIN_PICK_PAGE =
        "oracle.apps.wms.td.server.XxgilCPMainPickPage";
    private static final String MAIN_PICK_PAGE_INIT =
        "oracle.apps.wms.td.server.XxgilIPMainPickPage";

    /** DOCUMENT ME! */
    private static final String ITEM_CONFIRM_BEAN = "MAIN.ITEM_CONFIRM";

    /**
     * Creates a new CustomScanManager object.
     */
    public CustomScanManager()
    {
    }

    /**
     * DOCUMENT ME!
     *
     * @param f DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public static boolean isInputableField(FieldBean f)
    {

        return (f instanceof InputableFieldBean)
                   && (((InputableFieldBean) f).isEditable());
    }


    /**
     * DOCUMENT ME!
     *
     * @param args DOCUMENT ME!
     */
    public static void main(String[] args)
    {
        getItemNumberFromCrossReference(con, "5 3585001 037593 00", "132");
        getItemNumberFromCrossReference(con, "5 3656563 053729 00", "132");
    }


    /**
     * DOCUMENT ME!
     * This method is the main method that gets called by apps when the scan
     * takes place
     * @param curtSession DOCUMENT ME!
     * @param curtPage DOCUMENT ME!
     * @param val DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public static String processScan(Session curtSession,
                                     PageBean curtPage,
                                     String val)
    {

        Session session = curtSession;
        con = (OracleConnection) session.getConnection();

        String lModName  = "processScan : ";
        String lProgress = "000";
        debug(lModName + lProgress + " Start ");
        debug(lModName + lProgress + " val " + val);

        FieldBean curtBean = curtPage.getCurrentFieldBean();
        lProgress = "010";
        debug(lModName + lProgress + " curtPage.getName() "
              + curtPage.getName());
        debug(lModName + lProgress + " curtBean.getName() "
              + curtBean.getName());
        debug(lModName + lProgress + " val " + val);
        lProgress = "020";

        String scannedValue = val;

        if((MAIN_PICK_PAGE.equals(curtPage.getName())||MAIN_PICK_PAGE_INIT.equals(curtPage.getName()))
               && ITEM_CONFIRM_BEAN.equals(curtBean.getName()))
        {

            String[] curtFieldDFIs = null;
            int      itemDFILength = 0;
            lProgress = "030";

            boolean isInputable = isInputableField(curtBean);
            debug(lModName + lProgress + " isInputableField(curtBean) "
                  + isInputable);
            lProgress = "40";
            String orgId = (String) session.getObject("ORGID");
            debug(lModName + lProgress + " orgId " + orgId);
            lProgress = "50";           
            scannedValue = getItemNumberFromCrossReference(con, val, orgId);
            lProgress = "60";           
            debug(lModName + lProgress + " scannedValue " + scannedValue);
            debug(lModName + lProgress + " End ");
        }

        return scannedValue;

    }


    /**
     * This method is used for debugging
     *
     * @param str Writes to the debug fiels.
     */
    private static void debug(String str)
    {
        //System.out.println("xxx.custom.CustomScanManager."+str);

        if(true)//UtilFns.isTraceOn)
        {
            UtilFns.trace("xxx.custom.CustomScanManager." + str);
        }
    }


    /**
     * This method returns the item cross
     *
     * @param con The Oracle dtabase connection!
     * @param pCrossReference The actual value entered by the user!
     * @param pOrgId The organizationId!
     *
     * @return The item number if the cross reference is found or else return
     *         the main value itself because it must have been the itemNumber!
     */
    private static String getItemNumberFromCrossReference(OracleConnection con,
                                                          String pCrossReference,
                                                          String pOrgId)
    {

        String lModName  = "getItemNumberFromCrossReference : ";
        String lProgress = "000";
        debug(lModName + lProgress + " Start ");
        debug(lModName + lProgress + " pCrossReference " + pCrossReference);
        debug(lModName + lProgress + " pOrgId " + pOrgId);

        OracleConnection oracleconnection = con;
        //con.setClientData("NLS_LANGUAGE", "AMERICAN");

        PreparedStatement stmt       = null;
        String            itemNumber = pCrossReference;
        lProgress = "010";
        debug(lModName + lProgress);

        try
        {
            lProgress = "020";
            debug(lModName + lProgress);

            stmt =
                oracleconnection.prepareStatement(CROSS_REF_SQL_STR,
                                                  ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                  ResultSet.CONCUR_READ_ONLY);
            lProgress = "030";
            debug(lModName + lProgress);

            stmt.setBigDecimal(1, new BigDecimal(pOrgId));
            lProgress = "040";
            debug(lModName + lProgress);

            stmt.setString(2, pCrossReference);

            lProgress = "050";
            debug(lModName + lProgress);
            debug(lModName + lProgress + " CROSS_REF_SQL_STR "
                  + CROSS_REF_SQL_STR);

            ResultSet rs = stmt.executeQuery();
            lProgress = "060";
            debug(lModName + lProgress);

            rs.beforeFirst();
            lProgress = "070";
            debug(lModName + lProgress);

            while(rs.next())
            {
                lProgress = "080";
                debug(lModName + lProgress);

                itemNumber     = rs.getString(3);
                lProgress      = "090";
                debug(lModName + lProgress + " itemNumber " + itemNumber);

                break;
            }

            //            itemNumber     = rs.getString(1);
            lProgress = "100";
            debug(lModName + lProgress);
            rs.close();
            lProgress = "110";
            debug(lModName + lProgress);
        }
        catch(Exception e)
        {
            itemNumber = pCrossReference;
            debug(lModName + lProgress+"");
            //e.printStackTrace();

        }
        finally
        {
            lProgress = "140";
            debug(lModName + lProgress + " itemNumber " + itemNumber);

            try
            {
                lProgress = "150";
                debug(lModName + lProgress);

                if(stmt != null)
                {
                    lProgress = "160";
                    debug(lModName + lProgress);
                    stmt.close();
                }

                lProgress = "170";
                debug(lModName + lProgress);
            }
            catch(Exception e)
            {
                itemNumber = pCrossReference;
                debug(lModName + lProgress);
            }
        }

        return itemNumber;
    } 
}


Free Hit Counters
Comments