package com.cim.idm.constants;

public interface SqlStatement {

	//WareHouse
	public static final String	INSERT_WAREHOUSE = "INSERT INTO BSWAREHOUSE (WAREHOUSENO, DESCRIPTION, WAREHOUSETYPE,FACTORYNAME) VALUES(:WAREHOUSENO, :DESCRIPTION, :WAREHOUSETYPE,:FACTORYNAME)";
	public static final String	DELETE_WAREHOUSE = "DELETE FROM BSWAREHOUSE WHERE WAREHOUSENO = :WAREHOUSENO";
	public static final String	INSERT_WAREHOUSESHELF = "INSERT INTO BSWAREHOUSESHELF (WAREHOUSENO, DESCRIPTION, SHELFNO,FACTORYNAME,SHELFTYPE,CAPACITY) VALUES(:WAREHOUSENO, :DESCRIPTION, :SHELFNO,:FACTORYNAME,:SHELFTYPE,:CAPACITY)";
	public static final String	DELETE_WAREHOUSESHELF = "DELETE FROM BSWAREHOUSESHELF WHERE WAREHOUSENO = :WAREHOUSENO AND SHELFNO = :SHELFNO";
	public static final String	INSERT_WAREHOUSESTORAGE = "INSERT INTO BSWAREHOUSESTORAGE (WAREHOUSENO, DESCRIPTION, SHELFNO,STORAGENO) VALUES(:WAREHOUSENO, :DESCRIPTION, :SHELFNO,:STORAGENO)";
	public static final String	DELETE_WAREHOUSESTORAGE = "DELETE FROM BSWAREHOUSESTORAGE WHERE WAREHOUSENO = :WAREHOUSENO AND SHELFNO = :SHELFNO AND STORAGENO = :STORAGENO";
	
	//Lot
	public static final String	CHECK_LOT_PRODUCTREQUEST = "SELECT DISTINCT PRODUCTREQUESTNAME FROM LOT WHERE LOTNAME IN (:LOTNAMELIST) OR MODULENAME IN (:LOTNAMELIST)";
	public static final String	CHECK_LOT_CONVERT_DATA = "SELECT PANELNAME,QPANELNAME FROM BSCUTTINGPANELCONVERT WHERE PANELNAME IN (:LOTNAMELIST)";
	public static final String	CHECK_LOT_CONVERT_AS_SAME = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE,PROCESSOPERATIONNAME,PRODUCTREQUESTNAME,LOTHOLDSTATE FROM LOT WHERE LOTNAME IN (:LOTNAMELIST)";
	public static final String	CHECK_LOT_EXIST = "SELECT LOTNAME FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	SELECT_CHECK_LOT_TRACKIN = "SELECT DISTINCT PRODUCTIONTYPE ,FACTORYNAME ,PRODUCTSPECNAME ,PROCESSFLOWNAME ,PROCESSOPERATIONNAME ,LOTSTATE ,LOTPROCESSSTATE ,LOTHOLDSTATE,LOTGRADE FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	SELECT_CHECK_LOT_TRACKOUT = "SELECT DISTINCT PRODUCTIONTYPE ,FACTORYNAME ,PRODUCTSPECNAME ,PROCESSFLOWNAME ,PROCESSOPERATIONNAME ,LOTSTATE ,LOTPROCESSSTATE ,LOTHOLDSTATE FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	SELECT_CHECK_VIRTUALLOT_CREATE = "SELECT DISTINCT PRODUCTIONTYPE,PRODUCTSPECNAME,LOTSTATE,PROCESSOPERATIONNAME,PROCESSFLOWNAME,LOTGRADE, LOTHOLDSTATE,LOTPROCESSSTATE,PROCESSGROUPNAME FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	SELECT_CHECK_CUT_VIRTUALLOT_CREATE = "SELECT DISTINCT PRODUCTIONTYPE,PRODUCTSPECNAME,LOTSTATE,PROCESSOPERATIONNAME,PROCESSFLOWNAME,LOTGRADE, LOTHOLDSTATE,LOTPROCESSSTATE,PROCESSGROUPNAME FROM LOT WHERE READPANELNAME IN (:LOTNAME)";
	public static final String	SELECT_CHECK_VIRTUALLOT_CHANGE = "SELECT DISTINCT PRODUCTIONTYPE,PRODUCTSPECNAME,LOTSTATE,PROCESSOPERATIONNAME,PROCESSFLOWNAME,LOTGRADE, LOTHOLDSTATE,LOTPROCESSSTATE,PROCESSGROUPNAME FROM LOT WHERE PROCESSGROUPNAME IN (:PROCESSGROUPNAME)";
	public static final String	SELECT_CHECK_OQALOT = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE, LOTHOLDSTATE,PROCESSOPERATIONNAME,PROCESSFLOWNAME FROM LOT WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE SUPERPROCESSGROUPNAME = :PROCESSGROUPNAME)";
//	public static final String	CHECK_INNER_PACKING = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE,PROCESSOPERATIONNAME,PROCESSFLOWNAME,LOTGRADE,LOTDETAILGRADE,REWORKSTATE,REVISIONCODE FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	CHECK_INNER_PACKING = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE,PROCESSOPERATIONNAME,PROCESSFLOWNAME,LOTGRADE,LOTDETAILGRADE,REWORKSTATE FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	CHECK_REVISIONCODE = "SELECT DISTINCT REVISIONCODE FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	CHECK_RMA_PACKING = "SELECT DISTINCT PRODUCTSPECNAME,LOTGRADE,LOTDETAILGRADE,RMANUMBER,RMAJUDGE,CTNO  FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	CHECK_INNER_PACKING_WO = "SELECT DISTINCT PRODUCTREQUESTNAME FROM LOT WHERE LOTNAME IN (:LOTNAME)";
	public static final String	SELECT_GROUP_BY_LOTNAME = "SELECT DISTINCT PROCESSGROUPNAME FROM LOT WHERE LOTNAME IN (:LOTNAME)";
//	public static final String	CHECK_OUTER_PACKING = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE , LOTHOLDSTATE , LOTPROCESSSTATE ,PROCESSFLOWNAME ,PROCESSOPERATIONNAME ,LOTDETAILGRADE ,REWORKSTATE,REVISIONCODE  FROM lot WHERE PROCESSGROUPNAME IN (:PROCESSGROUPNAME)";
	public static final String	CHECK_OUTER_PACKING = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE , LOTHOLDSTATE , LOTPROCESSSTATE ,PROCESSFLOWNAME ,PROCESSOPERATIONNAME ,LOTDETAILGRADE ,REWORKSTATE  FROM lot WHERE PROCESSGROUPNAME IN (:PROCESSGROUPNAME)";
	public static final String	CHECK_OUTER_PACKING_WO = "SELECT DISTINCT PRODUCTREQUESTNAME FROM lot WHERE PROCESSGROUPNAME IN (:PROCESSGROUPNAME)";
	public static final String	CHECK_PALLET_PACKING = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE , LOTHOLDSTATE , LOTPROCESSSTATE ,PROCESSFLOWNAME ,PROCESSOPERATIONNAME ,LOTDETAILGRADE ,REWORKSTATE,REVISIONCODE  FROM lot WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE SUPERPROCESSGROUPNAME IN(:PROCESSGROUPNAME))";
//	public static final String	CHECK_PALLET_PACKINGNEW = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE , LOTHOLDSTATE , LOTPROCESSSTATE ,PROCESSFLOWNAME ,PROCESSOPERATIONNAME ,LOTDETAILGRADE ,REWORKSTATE,REVISIONCODE  FROM lot WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP START WITH  PROCESSGROUPNAME IN( :PROCESSGROUPNAME) CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME )";
	public static final String	CHECK_PALLET_PACKINGNEW = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE , LOTHOLDSTATE , LOTPROCESSSTATE ,PROCESSFLOWNAME ,PROCESSOPERATIONNAME ,LOTDETAILGRADE ,REWORKSTATE  FROM lot WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP START WITH  PROCESSGROUPNAME IN( :PROCESSGROUPNAME) CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME )";
	public static final String	CHECK_PALLET_PACKING_WONEW = "SELECT DISTINCT PRODUCTREQUESTNAME FROM lot WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP START WITH  PROCESSGROUPNAME IN( :PROCESSGROUPNAME) CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME )";
	public static final String	CHECK_PALLET_PACKING_WO = "SELECT DISTINCT PRODUCTREQUESTNAME FROM lot WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE SUPERPROCESSGROUPNAME IN(:PROCESSGROUPNAME))";
	public static final String	SELECT_LOTNAME_BY_BOXNAME = "SELECT LOTNAME FROM LOT WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP START WITH  PROCESSGROUPNAME = :BOXNAME CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME )";
	public static final String	SELECT_LOTNAME_BY_GROUP = "SELECT LOTNAME FROM LOT WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP START WITH  PROCESSGROUPNAME = ? CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME )";
	public static final String	SELECT_PRODUCTREQUESTNAME_BY_BOXNAME = "SELECT DISTINCT PRODUCTREQUESTNAME FROM LOT WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP START WITH  PROCESSGROUPNAME = :BOXNAME CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME )";
	public static final String	SELECT_LOTOPER_BY_BOXNAME = "SELECT DISTINCT PROCESSOPERATIONNAME FROM LOT WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP START WITH  PROCESSGROUPNAME = :BOXNAME CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME )";
	public static final String	SELECT_LOT_CHECK_AS_SAME_BY_LOTLIST = "SELECT DISTINCT L.PROCESSOPERATIONNAME, L.PROCESSFLOWNAME, L.PRODUCTSPECNAME, L.PRODUCTIONTYPE, l.LOTGRADE, L.LOTHOLDSTATE, L.LOTSTATE, L.LOTPROCESSSTATE, L.PRODUCTREQUESTNAME  FROM LOT L WHERE L.LOTNAME IN (:LOTNAMELIST)";
	public static final String	SELECT_LOTNAME_BY_MODULENAME = "SELECT LOTNAME  FROM LOT  WHERE MODULENAME = ?";
	public static final String	SELECT_LOTNAME_BY_MATERIALNAME = "SELECT LOTNAME  FROM BSLOTCONSUMABLEASSY  WHERE CONSUMABLENAME = ?";
	
	public static final String	UPDATE_LOT_CLEAR_OQAINFO = "UPDATE LOT SET OQAJUDGE = '',OQARETURNFLAG = '' WHERE PROCESSGROUPNAME = ?";
	
	public static final String	CHECK_WO_TYPE_BY_NAMELIST = "SELECT DISTINCT PRODUCTREQUESTTYPE FROM PRODUCTREQUEST WHERE PRODUCTREQUESTNAME IN (:PRODUCTREQUESTNAME)";
	
	public static final String SELECT_AGING_GROUP = "SELECT PROCESSOPERATIONNAME  FROM PROCESSOPERATIONSPEC p WHERE DETAILPROCESSOPERATIONTYPE = ?";
	public static final String CHECK_CHECKOUT_TIME = "SELECT LOTNAME ,OLDPROCESSOPERATIONNAME ,EVENTNAME ,TIMEKEY FROM LOTHISTORY l WHERE LOTNAME =? AND OLDPROCESSOPERATIONNAME =? AND EVENTNAME ='TrackOut'";
	public static final String CHECK_CONTROL_TIME = "SELECT FROMOPERATION ,TOOPERATION ,CONTROLTIME ,CONTROLTYPE  FROM OPERATIONCONTROL o WHERE FACTORYNAME ='BADT' AND PRODUCTSPECNAME =? AND PROCESSFLOWNAME =? AND TOOPERATION =?";
	
	// WorkOrder
	public static final String	CHECK_LINE_EXIST_ACTIVE_WO = "SELECT PRODUCTREQUESTNAME,LINENAME,STATE FROM BSWORKORDERLINE WHERE LINENAME = ? AND STATE = ?";
	public static final String	CHECK_LINE_EXIST_WO = "SELECT DISTINCT LINENAME,PRODUCTSPECNAME FROM BSWORKORDERLINE WHERE LINENAME = ?";
	
	// Panel bind material check
	public static final String	CHECK_PANEL_BIND_FPC = "SELECT LOTNAME,CONSUMABLENAME FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ? AND CONSUMABLETYPE = 'FPC'";
	public static final String	CHECK_PANEL_BIND_MATERIAL = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE  FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ? AND CONSUMABLENAME = ?";
	public static final String	CHECK_MATERIAL_IS_BIND = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE  FROM BSLOTCONSUMABLEASSY WHERE CONSUMABLENAME = ?";
	public static final String	CHECK_PANEL_BIND_BLU = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE  FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ? AND CONSUMABLETYPE = 'BLU' ";
	public static final String	CHECK_PANEL_BIND_PCB = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE  FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ? AND CONSUMABLETYPE = 'PCB' ";
	public static final String	CHECK_PANEL_BIND_MOT = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE  FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ? AND CONSUMABLETYPE = 'MOT' ";
	public static final String	CHECK_PANEL_BIND_MDL_2nd = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE  FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ? AND CONSUMABLETYPE = 'MDL_2nd' ";
	public static final String	CHECK_PANEL_BIND_S_FPC = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE  FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ? AND CONSUMABLETYPE = 'S-FPC' ";
	public static final String	CHECK_PANEL_BIND_PCB_FPC = "SELECT LOTNAME,CONSUMABLENAME FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ? AND CONSUMABLETYPE IN ( 'FPC','PCB') ";
	
	
	// check panel tarckOut info
	public static final String	CHECK_PANEL_TRACKOUT_NEED_TRACKIN = "SELECT SERVICENAME,PROCESSOPERATIONNAME,NEEDTRACKIN FROM BSTRACKOUTINFO WHERE SERVICENAME = ? AND PROCESSOPERATIONNAME = ? AND NEEDTRACKIN = ? ";
	public static final String	CHECK_PANEL_MUST_OPERATION = "SELECT P.ISKEYPROCESS FROM POSMUSTOPERATION P,TPPOLICY T WHERE T.CONDITIONID = P.CONDITIONID AND T.PRODUCTSPECNAME = ? AND P.PROCESSOPERATIONNAME = ?";
	public static final String	CHECK_PANEL_MUST_OPERATION_BY_OPERATION = "SELECT PROCESSOPERATIONNAME, DECODE(ISKEYPROCESS,'X','Y','N') AS ISKEYPROCESS FROM PROCESSOPERATIONSPEC WHERE PROCESSOPERATIONNAME = ?";
	public static final String	CHECK_PANEL_MUST_OPERATION_BY_TP = "SELECT PM.PROCESSOPERATIONNAME,PM.ISKEYPROCESS FROM TPPOLICY TP,POSMUSTOPERATION PM WHERE PM.CONDITIONID = TP.CONDITIONID AND TP.PRODUCTSPECNAME = ? AND PM.PROCESSOPERATIONNAME = ?";
	public static final String	CHECK_PANEL_MUST_OPERATION_BY_TPF = "SELECT PM.PROCESSOPERATIONNAME,PM.ISKEYPROCESS FROM TPFPOLICY TPF,POSMUSTOPERATION PM WHERE PM.CONDITIONID = TPF.CONDITIONID AND TPF.PRODUCTSPECNAME = ? AND TPF.PROCESSFLOWNAME =? AND PM.PROCESSOPERATIONNAME = ?";
	public static final String	CHECK_PANEL_MUST_OPERATION_NEW = "WITH OPERATION_TEMP AS (SELECT PROCESSOPERATIONNAME FROM PROCESSOPERATIONSPEC WHERE PROCESSOPERATIONNAME = ?)\r\n" + 
			"SELECT DISTINCT OP.PROCESSOPERATIONNAME,\r\n" + 
			"	DECODE(OP.ISKEYPROCESS,'X','Y','N') AS ISKEYPROCESS \r\n" + 
			"FROM PROCESSOPERATIONSPEC OP,OPERATION_TEMP TEMP\r\n" + 
			"WHERE OP.PROCESSOPERATIONNAME = TEMP.PROCESSOPERATIONNAME\r\n" + 
			"UNION ALL\r\n" + 
			"SELECT DISTINCT PM.PROCESSOPERATIONNAME,\r\n" + 
			"	PM.ISKEYPROCESS \r\n" + 
			"FROM TPFPOLICY TPF,TPPOLICY TP,POSMUSTOPERATION PM,OPERATION_TEMP TEMP\r\n" + 
			"WHERE PM.PROCESSOPERATIONNAME = TEMP.PROCESSOPERATIONNAME\r\n" + 
			"	AND (PM.CONDITIONID = TPF.CONDITIONID OR PM.CONDITIONID = TP.CONDITIONID )";
	
	// ProcessGroup
	public static final String	SELECT_GROUP_BY_CREATEUSER_FGCODE = "SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE PROCESSGROUPTYPE = 'OQALot' AND OQAINSPECTSTATE = 'On' AND CREATEUSER = :CREATEUSER AND PRODUCTSPECNAME = :PRODUCTSPECNAME ORDER BY CREATETIME DESC";
	public static final String	SELECT_GROUP_BY_CREATEUSER_FGCODE_FLOW_OPERATION = "SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE PROCESSGROUPTYPE = 'OQALot' AND OQAINSPECTSTATE = 'On' AND CREATEUSER = :CREATEUSER AND PRODUCTSPECNAME = :PRODUCTSPECNAME AND PROCESSFLOWNAME = :PROCESSFLOWNAME AND PROCESSOPERATIONNAME = :PROCESSOPERATIONNAME ORDER BY CREATETIME DESC FOR UPDATE";
	public static final String	CHECK_BOX_EXIST = "SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE PROCESSGROUPNAME = ?";
	public static final String	CHECK_DEGRADE_PALLET_PACKING = "SELECT DISTINCT PRODUCTSPECNAME ,ROOTFGCODE,PROCESSFLOWNAME ,PROCESSOPERATIONNAME,LOTGRADE,REVISIONCODE  FROM PROCESSGROUP WHERE PROCESSGROUPNAME IN ( :PROCESSGROUPNAME)";
	
	// Consumable
	public static final String	SELECT_SPEC_QTY_BY_CONSUMABLELIST = "SELECT RAWTOHEX(SYS_GUID()) SEQ, SUM(QUANTITY) GAMNG, CONSUMABLESPECNAME MATNR, UNITNAME GMEIN, MATERIALLOCATIONNAME LGORT, CHARG\r\n" + 
			"FROM CONSUMABLE\r\n" + 
			"WHERE CONSUMABLENAME IN (:CONSUMABLENAME)\r\n" + 
			"GROUP BY CONSUMABLESPECNAME, UNITNAME, MATERIALLOCATIONNAME, CHARG";
	public static final String	SELECT_SPEC_QTY_BY_CONSUMABLELIST_NEW = "SELECT RAWTOHEX(SYS_GUID()) SEQ, SUM(QUANTITY) GAMNG, CONSUMABLESPECNAME MATNR, UNITNAME GMEIN, CHARG\r\n" + 
			"FROM CONSUMABLE\r\n" + 
			"WHERE CONSUMABLENAME IN (:CONSUMABLENAME)\r\n" + 
			"GROUP BY CONSUMABLESPECNAME, UNITNAME, CHARG";
	public static final String	SELECT_REQUEST_SPEC_QTY_BY_REQUESTNAME = "SELECT CONSUMABLESPECNAME ,REQUESTQTY  FROM BSCONSUMABLEREQUESTLIST  WHERE REQUESTNAME = :REQUESTNAME ORDER BY CONSUMABLESPECNAME";
	public static final String	SELECT_SPEC_QTY_BY_LIST = "SELECT SUM(QUANTITY) AS QUANTITY,CONSUMABLESPECNAME FROM CONSUMABLE WHERE CONSUMABLENAME IN (:CONSUMABLENAME) GROUP BY CONSUMABLESPECNAME ORDER BY CONSUMABLESPECNAME";
	public static final String	CHECK_MATERIAL_SPEC = "SELECT DISTINCT CONSUMABLESPECNAME FROM CONSUMABLE WHERE CONSUMABLENAME IN (:CONSUMABLENAME)";
	public static final String	CHECK_MATERIAL_SPECS = "SELECT CONSUMABLESPECNAME,CONSUMABLENAME FROM CONSUMABLE WHERE CONSUMABLENAME IN (:CONSUMABLENAME)";
	public static final String	CHECK_MATERIAL_ORDER = "SELECT REQUESTORDER,CONSUMABLENAME FROM CONSUMABLE WHERE CONSUMABLENAME IN (:CONSUMABLENAME)";
	public static final String	GET_MATERIAL_REQUEST_BY_LINE_SPEC = "SELECT BL.REQUESTNAME , \r\n" + 
			"	BL.CONSUMABLESPECNAME , \r\n" + 
			"	BL.REQUESTQTY , \r\n" + 
			"	BL.SENDQTY  \r\n" + 
			"FROM BSCONSUMABLEREQUEST B,BSCONSUMABLEREQUESTLIST BL \r\n" + 
			"WHERE B.REQUESTNAME = BL.REQUESTNAME  \r\n" + 
			"	AND B.LINENAME = :LINENAME  \r\n" + 
			"	AND B.REQUESTTYPE = 'Call'\r\n" + 
			"	AND B.REQUESTSTATE IN ('Requested', 'Sending') \r\n" + 
			"	AND BL.CONSUMABLESPECNAME = :CONSUMABLESPECNAME\r\n" + 
			"ORDER BY BL.REQUESTNAME ";
	
	// Enum
	public static final String	SELECT_ACTION_BY_NAME_VALUE = "SELECT ENUMVALUE,ACTION,ACTIONVALUE  FROM ENUMDEFVALUE WHERE ENUMNAME = :ENUMNAME AND ENUMVALUE = :ENUMVALUE ORDER BY ACTIONVALUE";
	public static final String	SELECT_VALUE_BY_NAME = "SELECT ENUMVALUE FROM ENUMDEFVALUE WHERE ENUMNAME = ?";
	public static final String	UPDATE_ACTION_BY_NAME_VALUE = "UPDATE ENUMDEFVALUE SET ACTION = :ACTION, ACTIONVALUE = :ACTIONVALUE,DESCRIPTION = :DESCRIPTION WHERE ENUMNAME = :ENUMNAME AND ENUMVALUE = :ENUMVALUE";
	public static final String	UPDATE_PACKINGFALG_ACTION_BY_NAME_VALUE = "UPDATE ENUMDEFVALUE SET ACTION = :ACTION WHERE ENUMNAME = :ENUMNAME AND ENUMVALUE = :ENUMVALUE";
	public static final String	INSERT_ACTION_BY_NAME_VALUE = "INSERT INTO ENUMDEFVALUE(ENUMNAME, ENUMVALUE, ACTION, ACTIONVALUE,DESCRIPTION) VALUES(:ENUMNAME, :ENUMVALUE, :ACTION, :ACTIONVALUE,:DESCRIPTION)";
	public static final String	INSERT_ENUMVALUE = "INSERT INTO ENUMDEFVALUE(ENUMNAME, ENUMVALUE, DESCRIPTION, ACTIONVALUE) VALUES(:ENUMNAME, :ENUMVALUE, :DESCRIPTION, :ACTIONVALUE)";
	public static final String	DELETE_ENUMVALUE = "DELETE FROM ENUMDEFVALUE WHERE ENUMNAME = :ENUMNAME AND ENUMVALUE = :ENUMVALUE";
	public static final String	SELECT_CHECK_REVISIONCODE = "SELECT ENUMVALUE,ACTION  FROM ENUMDEFVALUE WHERE ENUMNAME = ?";
	
	// BSLOTCONSUMABLEASSY
	public static final String INSERT_BSLOTCONSUMABLEASSY = "INSERT INTO BSLOTCONSUMABLEASSY (LOTNAME, CONSUMABLENAME, PROCESSOPERATIONNAME, CONSUMABLESPECNAME, CONSUMABLETYPE, FACTORYNAME, ATTACHQUANTITY, EVENTTIME, EVENTNAME, EVENTUSER, EVENTTIMEKEY, EVENTCOMMENT, MACHINENAME) VALUES(:LOTNAME, :CONSUMABLENAME, :PROCESSOPERATIONNAME, :CONSUMABLESPECNAME, :CONSUMABLETYPE, :FACTORYNAME, :ATTACHQUANTITY, :EVENTTIME, :EVENTNAME, :EVENTUSER, :EVENTTIMEKEY, :EVENTCOMMENT, :MACHINENAME)";
	public static final String SELECT_FPC_PCB_LOT_BY_CONSUMABLENAME = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE FROM BSLOTCONSUMABLEASSY WHERE CONSUMABLENAME =:CONSUMABLENAME AND CONSUMABLETYPE IN ('FPC','PCB','IC','BLU','MOT')";
	public static final String SELECT_LOT_BY_MODULENAME = "SELECT MODULENAME,LOTNAME FROM LOT WHERE MODULENAME =:MODULENAME";
	public static final String DELETE_BSLOTCONSUMABLEASSY_BY_KEY = "DELETE FROM BSLOTCONSUMABLEASSY WHERE CONSUMABLENAME =:CONSUMABLENAME AND LOTNAME = :LOTNAME";
	public static final String SELECT_MATERIAL_LOT_BY_CONSUMABLENAME = "SELECT LOTNAME FROM BSLOTCONSUMABLEASSY WHERE CONSUMABLENAME =:CONSUMABLENAME";
	public static final String SELECT_MATERIALNAME_BY_LOTNAME = "SELECT LOTNAME,CONSUMABLENAME,CONSUMABLETYPE FROM BSLOTCONSUMABLEASSY WHERE LOTNAME = ?";
	public static final String UPDATE_MATERIAL_MOVE = "UPDATE BSLOTCONSUMABLEASSY SET OLDLOTNAME = LOTNAME,LOTNAME = ? WHERE LOTNAME = ? ";
	public static final String UPDATE_MATERIAL_ROLLBACK = "UPDATE BSLOTCONSUMABLEASSY SET LOTNAME = OLDLOTNAME WHERE LOTNAME = ? ";
	
	// BSFGMSGROUP
	public static final String SELECT_RECEIVEFLAG_BY_NAME = "SELECT RECEIVEFLAG FROM BSFGMSPROCESSGROUP WHERE PROCESSGROUPNAME = ?";
	
	// TPFOM
	public static final String SELECT_OPERATION_BY_TPFM = "SELECT T.PROCESSOPERATIONNAME FROM TPFOMPOLICY T, POSMACHINERECIPE P WHERE T.CONDITIONID = P.CONDITIONID AND T.FACTORYNAME = :FACTORYNAME AND T.PRODUCTSPECNAME = :PRODUCTSPECNAME AND T.PROCESSFLOWNAME = :PROCESSFLOWNAME AND T.MACHINENAME = :MACHINENAME";
	public static final String SELECT_OPERATION_BY_EQ = "SELECT DISTINCT T.PROCESSOPERATIONNAME FROM TPFOMPOLICY T ,POSMACHINERECIPE P WHERE T.CONDITIONID = P.CONDITIONID AND T.MACHINENAME = ?";
	public static final String SELECT_CONSUMABLETYPE_BY_OPER = "SELECT DISTINCT P.MATERIALTYPE FROM TOPOLICY T ,POSMATERIALTYPE P WHERE T.CONDITIONID = P.CONDITIONID AND T.PROCESSOPERATIONNAME = ?";
	public static final String SELECT_OQARETURNOPER_BY_CURRENTOPER = "SELECT P.RETURNOPERATIONNAME FROM TOPOLICY T,POSOQARETURNOPERATION P WHERE T.CONDITIONID = P.CONDITIONID AND T.PROCESSOPERATIONNAME = ?";
	
	// BSLABEL
	public static final String CHECK_BSLABEL_EXIST = "SELECT LABELID FROM BSLABEL WHERE LABELID = :LABELID";
	public static final String INSERT_BSLABEL_ALL = "INSERT INTO BSLABEL (LABELID, LABELTYPE, LABELGROUP, LABELDRIVE, LABELNAME, LABELADDRESS, DESCRIPTION) VALUES(:LABELID, :LABELTYPE, :LABELGROUP, :LABELDRIVE, :LABELNAME, :LABELADDRESS, :DESCRIPTION)";
	public static final String INSERT_BSLABELASSIGNFGCODE_ALL = "INSERT INTO BSLABELASSIGNFGCODE (PRODUCTSPECNAME, LABELID, PRINTERNAME, PRINTQTY, PROCESSOPERATIONNAME,FACTORYNAME) VALUES(:PRODUCTSPECNAME, :LABELID, :PRINTERNAME, :PRINTQTY, :PROCESSOPERATIONNAME,:FACTORYNAME)";
	public static final String UPDATE_BSLABEL_BY_ID = "UPDATE BSLABEL SET LABELTYPE=:LABELTYPE, LABELGROUP=:LABELGROUP, LABELDRIVE=:LABELDRIVE, LABELNAME=:LABELNAME, LABELADDRESS=:LABELADDRESS, DESCRIPTION=:DESCRIPTION WHERE LABELID=:LABELID";
	public static final String DELETE_BSLABEL_BY_ID = "DELETE FROM BSLABEL WHERE LABELID=:LABELID";
	public static final String DELETE_BSLABELASSIGNFGCODE_BY_ID = "DELETE FROM BSLABELASSIGNFGCODE WHERE LABELID=:LABELID AND PRODUCTSPECNAME=:PRODUCTSPECNAME AND FACTORYNAME = :FACTORYNAME ";
	public static final String INSERT_LABEL_PARAM = "INSERT INTO BSLABELASSIGNPARAMETER (LABELID, PARAMETERNAME, PARAMETERDESCRIPTION, LASTEVENTUSER, LASTEVENTNAME, LASTEVENTCOMMENT, LASTEVENTTIMEKEY, LASTEVENTTIME) VALUES(?,?,?,?,?,?,?,?)";
	public static final String INSERT_LABEL_PARAM_HIS = "INSERT INTO BSLABELASSIGNPARAMETERHISTORY (TIMEKEY, LABELID, PARAMETERNAME, PARAMETERDESCRIPTION, EVENTUSER, EVENTNAME, EVENTCOMMENT, EVENTTIME) VALUES(?,?,?,?,?,?,?,?)";
	public static final String DELETE_LABEL_PARAM = "DELETE FROM BSLABELASSIGNPARAMETER WHERE LABELID = :LABELID";
	public static final String SELECT_LABEL_PARAM = "SELECT PARAMETERNAME FROM BSLABELASSIGNPARAMETER WHERE LABELID = ?";
	
	// Task
	public static final String SELECT_ACTIVE_WORKBINDING_BY_LINENAME = "SELECT PRODUCTSPECNAME,PRODUCTREQUESTNAME,PROCESSFLOWNAME,QFGCODE FROM BSWORKORDERLINE WHERE LINENAME = :LINENAME AND STATE = 'Active' ";
	public static final String SELECT_RESERVE_WORKBINDING_BY_LINENAME = "SELECT PRODUCTSPECNAME,PRODUCTREQUESTNAME,PROCESSFLOWNAME,QFGCODE FROM BSWORKORDERLINE WHERE LINENAME = :LINENAME AND STATE = 'Reserve' ORDER BY LASTEVENTTIMEKEY ";
	public static final String SELECT_MAINSPEC_BY_FGCODE = "SELECT IDNRK FROM SAPBOMDETAIL WHERE MATNR = :PRODUCTSPECNAME AND DUMPS = 'x'";
	public static final String SELECT_OPERATION_BY_MACHINE = "SELECT T.PROCESSOPERATIONNAME FROM TPFOMPOLICY T,POSMACHINERECIPE P WHERE T.CONDITIONID = P.CONDITIONID AND T.PRODUCTSPECNAME = :PRODUCTSPECNAME AND T.MACHINENAME = :MACHINENAME ORDER BY T.PROCESSOPERATIONNAME ";
	public static final String SELECT_AGVOPERATION_BY_MACHINE = "SELECT AGVOPERATION FROM TASKOPERATION WHERE MACHINENAME = :MACHINENAME AND PORTNAME = :PORTNAME ";
	public static final String SELECT_OPERATION_BY_PFM = "SELECT T.PROCESSOPERATIONNAME FROM TPFOMPOLICY T,POSMACHINERECIPE P WHERE T.CONDITIONID = P.CONDITIONID AND T.PRODUCTSPECNAME = :PRODUCTSPECNAME AND T.MACHINENAME = :MACHINENAME AND T.PROCESSFLOWNAME = :PROCESSFLOWNAME ORDER BY T.PROCESSOPERATIONNAME ";
	
	public static final String SELECT_PRODUCTSPECTYPE = "SELECT PRODUCTSPECNAME ,PRODUCTIONTYPE FROM PRODUCTSPEC p WHERE FACTORYNAME ='BADT' AND PRODUCTSPECNAME =:PRODUCTSPECNAME";
	
	// Machine 
	public static final String CHECK_MACHINE_ASSIGN_WO = "SELECT PRODUCTREQUESTNAME FROM BSWORKORDERLINE WHERE PRODUCTREQUESTNAME = :PRODUCTREQUESTNAME AND LINENAME = :LINENAME";
	public static final String CHECK_MACHINE_FIRST = "SELECT T.MACHINENAME FROM PROCESSFLOWSEQ P,TPFOMPOLICY T WHERE P.FACTORYNAME=:FACTORYNAME AND P.PROCESSFLOWNAME=:PROCESSFLOWNAME AND P.POSITIONGROUP='0' AND P.POSITION='1' AND P.FACTORYNAME=T.FACTORYNAME AND T.PROCESSFLOWNAME=P.PROCESSFLOWNAME AND T.PROCESSOPERATIONNAME=P.PROCESSOPERATIONNAME AND T.PRODUCTSPECNAME=:PRODUCTSPECNAME AND T.MACHINENAME=:MACHINENAME";
	public static final String WORK_ORDER_BIND_LINE = "MERGE INTO BSWORKORDERLINE wol\r\n" + 
			"USING (SELECT :PRODUCTREQUESTNAME PRODUCTREQUESTNAME\r\n" + 
			"       FROM DUAL) a\r\n" + 
			"ON (a.PRODUCTREQUESTNAME = wol.PRODUCTREQUESTNAME)\r\n" + 
			"WHEN MATCHED THEN\r\n" + 
			"UPDATE\r\n" + 
			"    SET wol.LINENAME = :LINENAME,\r\n" + 
			"    UPDATETIME   = SYSDATE,\r\n" + 
			"    UPDATEUSER   = :CREATEUSER\r\n" + 
			"    WHEN NOT MATCHED THEN\r\n" + 
			"INSERT (PRODUCTREQUESTNAME, LINENAME, PRODUCTSPECNAME, CREATEUSER, CREATETIME, PRODUCTREQUESTTYPE,\r\n" + 
			"    PROCESSFLOWNAME)\r\n" + 
			"    VALUES (:PRODUCTREQUESTNAME, :LINENAME, :PRODUCTSPECNAME, :CREATEUSER, SYSDATE, :PRODUCTREQUESTTYPE,\r\n" + 
			"               :PROCESSFLOWNAME)";
	public static final String WORK_ORDER_BIND_LINE_HISTORY = "INSERT INTO BSWORKORDERLINEHISTORY(PRODUCTREQUESTNAME, LINENAME, PRODUCTSPECNAME, CREATEUSER, CREATETIME, OPERATIONTYPE, PRODUCTREQUESTTYPE, PROCESSFLOWNAME) VALUES (:PRODUCTREQUESTNAME, :LINENAME, :PRODUCTSPECNAME, :CREATEUSER, SYSDATE, :OPERATIONTYPE, :PRODUCTREQUESTTYPE, :PROCESSFLOWNAME)";
	public static final String WORK_ORDER_UN_BIND_LINE = "UPDATE BSWORKORDERLINE SET LINENAME = NULL, UPDATETIME = SYSDATE , UPDATEUSER = :CREATEUSER WHERE PRODUCTREQUESTNAME = :PRODUCTREQUESTNAME";
	public static final String SELECT_WOBINDING_BY_LINENAME = "SELECT PRODUCTREQUESTNAME,LINENAME ,PRODUCTSPECNAME, PROCESSFLOWNAME  FROM BSWORKORDERLINE WHERE LINENAME = :LINENAME";
	public static final String SELECT_RECIPE_BY_LINENAME = "SELECT DISTINCT T.MACHINENAME ,POS.MACHINERECIPENAME \r\n" + 
														"FROM TPFOMPOLICY T,POSMACHINERECIPE POS \r\n" + 
														"WHERE T.CONDITIONID = POS.CONDITIONID \r\n" + 
														"	AND T.PRODUCTSPECNAME = :PRODUCTSPECNAME \r\n" + 
														"	AND T.PROCESSFLOWNAME = :PROCESSFLOWNAME \r\n" + 
														"	AND T.MACHINENAME IN (SELECT MACHINENAME FROM MACHINE WHERE LINENAME = :LINENAME)";
	
	// Future Action
	public static final String DELETE_LOTFUTUREACTION_HOLD_BY_OPER_LOTLIST = "DELETE FROM LOTFUTUREACTION WHERE ACTIONNAME = 'Hold' AND PROCESSOPERATIONNAME = :PROCESSOPERATIONNAME AND LOTNAME IN (:LOTLIST)";
	public static final String DELETE_LOTFUTUREACTION_HOLD_BY_OPER_LOTNAME = "DELETE FROM LOTFUTUREACTION WHERE ACTIONNAME = 'Hold' AND PROCESSOPERATIONNAME = :PROCESSOPERATIONNAME AND LOTNAME = :LOTNAME";
	public static final String DELETE_LOTFUTUREACTION_HOLD_BY_LOTNAME = "DELETE FROM LOTFUTUREACTION WHERE ACTIONNAME = 'Hold' AND LOTNAME = :LOTNAME";
	public static final String INSERT_LOTFUTUREACTION_ALL = "INSERT INTO LOTFUTUREACTION (LOTNAME, FACTORYNAME, PROCESSFLOWNAME, PROCESSOPERATIONNAME, ACTIONNAME, ACTIONTYPE, EVENTNAME, EVENTUSER, EVENTCOMMENT, REASONCODETYPE, REASONCODE) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
	public static final String SELECT_LOTFUTUREACTION = "SELECT LOTNAME, PROCESSOPERATIONNAME FROM LOTFUTUREACTION WHERE LOTNAME = :LOTNAME";
	public static final String SELECTSINGLE_LOTFUTUREACTION = " LOTNAME = :LOTNAME ";
	public static final String SELECT_LOTPROCESS = "SELECT t.FACTORYNAME ,\r\n" + 
												   "	lot.LOTNAME ,\r\n" + 
												   "	lot.PROCESSOPERATIONNAME AS CURRENTPROCESSOPERATIONNAME,\r\n" + 
												   "	t.PRODUCTSPECNAME ,\r\n" + 
												   "	t.CONDITIONID ,\r\n" + 
												   "	p.PROCESSFLOWNAME ,\r\n" + 
												   "	q.PROCESSOPERATIONNAME ,\r\n" + 
												   "	q.POSITIONGROUP ,\r\n" + 
												   "	q.POSITION\r\n" + 
												   "FROM LOT lot, TPPOLICY t , POSPOSSIBLEFLOW p , PROCESSFLOWSEQ q\r\n" + 
												   "WHERE lot.PRODUCTSPECNAME = t.PRODUCTSPECNAME AND t.CONDITIONID = p.CONDITIONID AND \r\n" + 
												   "      p.PROCESSFLOWNAME = q.PROCESSFLOWNAME(+) AND lot.LOTNAME = :LOTNAME \r\n" + 
												   "ORDER BY q.POSITIONGROUP ASC , q.POSITION ASC ";
	
	// BSMACHINEMATERIAL
	public static final String INSERT_BSMACHINEMATERIAL_ALL = "INSERT INTO BSMACHINEMATERIAL (MACHINENAME, UNITNAME, MATERIALPOSITION, MATERIALNAME, MATERIALTYPE, MATERIALSTATE, MATERIALTQTY, EVENTTIMEKEY, EVENTNAME, EVENTUSER) VALUES(?,?,?,?,?,?,?,?,?,?)";
	public static final String UPDATE_BSMACHINEMATERIAL = "UPDATE BSMACHINEMATERIAL SET MATERIALSTATE = :MATERIALSTATE, MATERIALPOSITION = :MATERIALPOSITION, EVENTNAME = :EVENTNAME, EVENTTIMEKEY = :EVENTTIMEKEY, EVENTUSER = :EVENTUSER  WHERE MATERIALNAME = :MATERIALNAME AND MACHINENAME = :MACHINENAME ";
	public static final String CHECK_MATERIAL_STATE = "SELECT MATERIALNAME FROM BSMACHINEMATERIAL  WHERE MATERIALNAME = :MATERIALNAME AND MACHINENAME = :MACHINENAME AND MATERIALSTATE = :MATERIALSTATE ";
	//IQCCONSUMABLE
	public static final String INSERT_BSCONSUMABLESPEC_NEW = "INSERT INTO CONSUMABLESPEC (FACTORYNAME,CONSUMABLESPECNAME,DESCRIPTION,CHECKSTATE,CREATETIME, CREATEUSER, LASTEVENTTIME, LASTEVENTUSER, CONSUMABLETYPE, CONSUMEUNIT,THICKNESS) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
	public static final String DELETE_BSCONSUMABLESPEC_NEW="DELETE FROM CONSUMABLESPEC WHERE CONSUMABLESPECNAME=:CONSUMABLESPECNAME";
	public static final String INSERT_BSIQCMATERIALNGINFO_NEW = "INSERT INTO BSIQCMATERIALNGINFO (CONSUMABLENAME,CREATEQUANTITY,LASTEVENTTIMEKEY,LASTEVENTNAME,LASTEVENTTIME,LASTEVENTUSER,LASTEVENTCOMMENT,SUPPLIERID,CONSUMABLETYPE,USERCOMMENT,CONSUMABLESPECNAME,BOXID) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String DELETE_BSIQCMATERIALNGINFO_NEW="DELETE FROM BSIQCMATERIALNGINFO WHERE CONSUMABLENAME=:CONSUMABLENAME";
	// NameRule
	public static final String CHECK_NAMERULE_EXIST = "SELECT RULENAME FROM NAMERULEDEF WHERE RULENAME = :RULENAME";
	public static final String INSERT_NAMERULEDEF = "INSERT INTO NAMERULEDEF (RULENAME, TOTALLENGTH, DESCRIPTION, RULETYPE, RULEGROUP) VALUES(:RULENAME,:TOTALLENGTH,:DESCRIPTION,:RULETYPE,:RULEGROUP)";
	public static final String INSERT_NAMERULEATTRDEF = "INSERT INTO NAMERULEATTRDEF (RULENAME, SECTIONNAME, POSITION, SECTIONCATEGORY, SECTIONTYPE, SECTIONLENGTH, SECTIONVALUE, EXCEPTIONALCHARACTER, MINNUMBER, MAXNUMBER) VALUES(?,?,?,?,?,?,?,?,?,?)";
	public static final String UPDATE_NAMERULEDEF = "UPDATE NAMERULEDEF SET DESCRIPTION = :DESCRIPTION ,RULETYPE = :RULETYPE , RULEGROUP = :RULEGROUP ,TOTALLENGTH = :TOTALLENGTH WHERE RULENAME = :RULENAME";
	public static final String DELETE_NAMERULEATTYDEF = "DELETE FROM NAMERULEATTRDEF WHERE RULENAME = :RULENAME";
	
	// SOP File
	public static final String UPDATE_SOPFILE = "UPDATE BSSOPFILE SET PRODUCTSPECNAME=?, FILEADDRESS=?, SOPTYPE=?, CREATEUSER=?, CREATETIME=? WHERE OPERATIONNAME=? AND FILENAME=?";
	public static final String INSERT_SOPFILE = "INSERT INTO BSSOPFILE (OPERATIONNAME, PRODUCTSPECNAME, FILENAME, FILEADDRESS, SOPTYPE, CREATEUSER, CREATETIME) VALUES(?, ?, ?, ?, ?, ?, ?)";
	public static final String DELETE_SOPFILE = "DELETE FROM BSSOPFILE WHERE OPERATIONNAME=? AND FILENAME=?";
	
	
	//SAP
	public static final String INSER_BSERPINF_LG03 = "INSERT INTO BSERPINF_LG03 (SEQ, MATNR, FABID, AUFNR, DLVST, GAMNG, GSTRP, GSUZP, GLTRP, GLUZP, AUART, STRING, ERNAM) VALUES  (:SEQ, :MATNR, :FABID, :AUFNR, :DLVST, :GAMNG, :GSTRP, :GSUZP, :GLTRP, :GLUZP, :AUART, :STRING, :ERNAM)";
	public static final String INSER_BSERPINF_LG03NEW = "INSERT INTO BSERPINF_LG03 (SEQ, MATNR, FABID, AUFNR, DLVST, GAMNG, GSTRP, GSUZP, GLTRP, GLUZP, AUART, STRING, ERNAM) VALUES  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	public static final String FIND_SPEC = "SELECT PRODUCTSPECNAME FROM PRODUCTSPEC WHERE PRODUCTSPECNAME IN (:PRODUCTSPECNAME)";
	
	public static final String FIND_WORK_ORDER = "SELECT PRODUCTREQUESTNAME FROM PRODUCTREQUEST WHERE PRODUCTREQUESTNAME IN (:PRODUCTREQUESTNAME)"; 
	
	public static final String FINISHE_PROCESS_OPERATION = "SELECT RAWTOHEX(SYS_GUID()) SEQ, a.*\r\n" + 
			"FROM (SELECT DISTINCT OLDPROCESSOPERATIONNAME                                                                 MESGX,\r\n" + 
			"                      PRODUCTREQUESTNAME                                                                      AUFNR,\r\n" + 
			"                      PRODUCTSPECNAME                                                                         MATNR,\r\n" + 
			"                      'EA'                                                                                   GMEIN,\r\n" + 
			"                      '1'                                                                                     STATUS,\r\n" + 
			"                      TO_CHAR(:NOWDATE, 'yyyy-MM-dd')                                                          CDATE,\r\n" + 
			"                      TO_CHAR(:NOWDATE, 'HH24:MI:SS')                                                          CTIME,\r\n" + 
			"                      DECODE(FACTORYNAME,'BADT','V151',FACTORYNAME)                                             WERKS,\r\n" + 
			"                      'MODULE'                                                                                FABID,\r\n" + 
			"                      COUNT(DISTINCT LOTNAME) OVER (PARTITION BY OLDPROCESSOPERATIONNAME, PRODUCTREQUESTNAME) GAMNG\r\n" + 
			"      FROM LOTHISTORY\r\n" + 
			"      WHERE PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"        AND PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"        AND EVENTNAME = 'TrackOut'\r\n" + 
			"        AND :STARTTIME <= TIMEKEY\r\n" + 
			"        AND TIMEKEY < :ENDTIME) a";
	
	public static final String NEW_FINISHE_PROCESS_OPERATION = "SELECT RAWTOHEX(SYS_GUID()) SEQ, a.*\r\n" + 
			"FROM (SELECT DISTINCT l.PRODUCTREQUESTNAME                                               AUFNR,\r\n" + 
			"                      s.VALUE                                                            MATNR,\r\n" + 
			"                      DECODE(l.FACTORYNAME, 'BADT', 'V151', l.FACTORYNAME)               WERKS,\r\n" + 
			"                      l.ROOTMATERIALBOX                                                  IDNRK,\r\n" + 
			"                      COUNT(DISTINCT l.LOTNAME) OVER (PARTITION BY l.PRODUCTREQUESTNAME) GAMNG,\r\n" + 
			"                      l.ROOTMATERIALLOT                                                  CHARG,\r\n" + 
			"                      pos.LGORT                                                          LGORT,\r\n" + 
			"                      '1'                                                                STATUS,\r\n" + 
			"                      TO_CHAR(:NOWDATE, 'yyyy-MM-dd')                                    CDATE,\r\n" + 
			"                      TO_CHAR(:NOWDATE, 'HH24:MI:SS')                                    CTIME\r\n" + 
			"      FROM LOTHISTORY l\r\n" + 
			"               LEFT JOIN BSPRODUCTSPECATTRIBUTE s\r\n" + 
			"                         ON l.PRODUCTSPECNAME = s.PRODUCTSPECNAME AND s.ATTRIBUTEKEY = 'PanelMaterialSpec'\r\n" + 
			"               LEFT JOIN PROCESSOPERATIONSPEC pos ON pos.PROCESSOPERATIONNAME = l.PROCESSOPERATIONNAME\r\n" + 
			"      WHERE l.PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"        AND l.PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"        AND l.EVENTNAME = 'CreateLot'\r\n" + 
			"        AND :STARTTIME <= l.TIMEKEY\r\n" + 
			"        AND l.TIMEKEY < :ENDTIME) a";
	
	public static final String INSER_SAP_VIRTUAL_LOT_LOG = "INSERT INTO SAPVIRTUALLOTLOG (SEQ, SAPLOTNAME, PROCESSOPERATIONNAME, PRODUCTREQUESTNAME, STARTTIMEKEY, ENDTTIMEKEY,\r\n" + 
			"                              PRODUCTNUM, CREATTIME, PRODUCTSPECNAME, UNIT, STATUS, EVENTNAME, FACTORYNAME, FABID)\r\n" + 
			"VALUES (:SEQ, :LOTID, :MESGX, :AUFNR, :STARTTIMEKEY, :ENDTTIMEKEY, :GAMNG, :CREATTIME, :MATNR, :GMEIN, :STATUS,\r\n" + 
			"        :EVENTNAME, :WERKS, :FABID)";
	
	public static final String INSER_NEW_FINISH_SAP_VIRTUAL_LOT_LOG = "INSERT INTO SAPVIRTUALLOTLOG (SEQ, SAPLOTNAME, PRODUCTREQUESTNAME, STARTTIMEKEY, ENDTTIMEKEY,\r\n" + 
			"                              PRODUCTNUM, CREATTIME, PRODUCTSPECNAME, STATUS, EVENTNAME, FACTORYNAME, LOCATION, IDNRK,\r\n" + 
			"                              CHARG)\r\n" + 
			"VALUES (:SEQ, :LOTID, :AUFNR, :STARTTIMEKEY, :ENDTTIMEKEY, :GAMNG, :CREATTIME, :MATNR, :STATUS,\r\n" + 
			"        :EVENTNAME, :WERKS, :LGORT, :IDNRK, :CHARG)";
	
	public static final String FIND_SPECNAME_FLOW_OPREATION = "SELECT 'Z' || LPAD(ROWNUM,3,'0')  AS MESGXXH,A.* FROM \r\n" + 
			"(SELECT pfs.PROCESSFLOWNAME      FLOWMC,\r\n" + 
			"       flow.PRODUCTSPECNAME     MATNR,\r\n" + 
			"       pfs.PROCESSOPERATIONNAME MESGX,\r\n" + 
			"       pos.DESCRIPTION          MESGXMC,\r\n" + 
			"       pos.WORKCENTER           ARBPL,\r\n" + 
			"       pos.SAPKEYPROCESS         GJGX,\r\n" + 
			"       'MODULE'                 FABID,\r\n" + 
			"       DECODE(pfs.FACTORYNAME,'BADT','V151',pfs.FACTORYNAME)           WERKS\r\n" + 
			"FROM PROCESSFLOWSEQ pfs\r\n" + 
			"    LEFT JOIN PROCESSOPERATIONSPEC pos ON pfs.PROCESSOPERATIONNAME = pos.PROCESSOPERATIONNAME\r\n" + 
			"    INNER JOIN (SELECT DISTINCT tp.PRODUCTSPECNAME, ppf.PROCESSFLOWNAME\r\n" + 
			"    FROM TPPOLICY tp\r\n" + 
			"    INNER JOIN POSPOSSIBLEFLOW ppf ON tp.CONDITIONID = ppf.CONDITIONID\r\n" + 
			"    INNER JOIN PRODUCTSPEC ps ON tp.PRODUCTSPECNAME = ps.PRODUCTSPECNAME\r\n" + 
			"    INNER JOIN PROCESSFLOW pl ON pl.PROCESSFLOWNAME = ppf.PROCESSFLOWNAME\r\n" + 
			"    WHERE tp.FACTORYNAME = 'BADT'\r\n" + 
			"    AND ppf.SAPUPLOADFLAG IS NULL) flow\r\n" + 
			"    ON pfs.PROCESSFLOWNAME = flow.PROCESSFLOWNAME\r\n" + 
			"ORDER BY pfs.POSITIONGROUP,pfs.POSITION) A";
	

	public static final String INSER_PRODUCT_REQUEST = "MERGE INTO PRODUCTREQUEST pr\r\n" + 
			"USING (SELECT :AUFNR PRODUCTREQUESTNAME\r\n" + 
			"       FROM DUAL) pr1\r\n" + 
			"ON (pr.PRODUCTREQUESTNAME = pr1.PRODUCTREQUESTNAME)\r\n" + 
			"WHEN MATCHED THEN\r\n" + 
			"UPDATE\r\n" + 
			"    SET pr.PLANQUANTITY = :GAMNG,\r\n" + 
			"    pr.PRODUCTREQUESTSTATE = :PRODUCTREQUESTSTATE\r\n" + 
			"    WHEN NOT MATCHED THEN\r\n" + 
			"INSERT (PRODUCTREQUESTNAME, PRODUCTREQUESTTYPE, FACTORYNAME, PRODUCTSPECNAME, PLANRELEASEDTIME, PLANFINISHEDTIME,\r\n" + 
			"    PRODUCTREQUESTSTATE, PLANQUANTITY, CREATEUSER, DESCRIPTION, CREATETIME)\r\n" + 
			"    VALUES (:AUFNR, :AUART, DECODE(:FABID,'MODULE','BADT',:FABID), :MATNR,\r\n" + 
			"               TO_DATE(CONCAT(:GSTRP, CASE WHEN :GSUZP = '24:00:00' THEN '00:00:00' ELSE :GSUZP END),\r\n" + 
			"               'yyyy-MM-ddHH24:MI:SS'),\r\n" + 
			"               TO_DATE(CONCAT(:GLTRP, CASE WHEN :GLUZP = '24:00:00' THEN '00:00:00' ELSE :GLUZP END),\r\n" + 
			"               'yyyy-MM-ddHH24:MI:SS'), :PRODUCTREQUESTSTATE, :GAMNG, :ERNAM, :STRING, SYSDATE)";
	
	
	public static final String INSER_PRODUCT_REQUEST_HISTOR = "INSERT INTO PRODUCTREQUESTHISTORY(PRODUCTREQUESTNAME, PRODUCTREQUESTTYPE, FACTORYNAME, PRODUCTSPECNAME,\r\n" + 
			"                                  PLANRELEASEDTIME,\r\n" + 
			"                                  PLANFINISHEDTIME,\r\n" + 
			"                                  PRODUCTREQUESTSTATE, PLANQUANTITY, CREATEUSER, DESCRIPTION, CREATETIME, TIMEKEY,\r\n" + 
			"                                  EVENTUSER, EVENTTIME, EVENTNAME)\r\n" + 
			"VALUES (:AUFNR, :AUART, DECODE(:FABID,'MODULE','BADT',:FABID), :MATNR,\r\n" + 
			"        TO_DATE(CONCAT(:GSTRP, CASE WHEN :GSUZP = '24:00:00' THEN '00:00:00' ELSE :GSUZP END),\r\n" + 
			"                'yyyy-MM-ddHH24:MI:SS'),\r\n" + 
			"        TO_DATE(CONCAT(:GLTRP, CASE WHEN :GLUZP = '24:00:00' THEN '00:00:00' ELSE :GLUZP END),\r\n" + 
			"                'yyyy-MM-ddHH24:MI:SS'), :PRODUCTREQUESTSTATE, :GAMNG, :ERNAM, :STRING, SYSDATE, :timeKey, :EVENTUSER,\r\n" + 
			"        SYSDATE, :EVENTNAME)";
	
	
	public static final String PRODUCT_LOTNAME_NUM = "SELECT RAWTOHEX(SYS_GUID()) SEQ, :BOXNAME PALLETID, PRODUCTREQUESTNAME AUFNR, COUNT(LOTNAME) GAMNG\r\n" + 
			"FROM LOT\r\n" + 
			"WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME\r\n" + 
			"                           FROM PROCESSGROUP\r\n" + 
			"                           START WITH PROCESSGROUPNAME = :BOXNAME\r\n" + 
			"                           CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME)\r\n" + 
			"GROUP BY PRODUCTREQUESTNAME";
	
	
	public static final String PRODUCT_SHIPMENT_NUM = "SELECT RAWTOHEX(SYS_GUID()) SEQ, COUNT(a.LOTNAME) GAMNG\r\n" + 
			"FROM (\r\n" + 
			"         SELECT LOTNAME\r\n" + 
			"         FROM LOT\r\n" + 
			"         WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME\r\n" + 
			"                                    FROM PROCESSGROUP\r\n" + 
			"                                    START WITH PROCESSGROUPNAME = :BOXNAME\r\n" + 
			"                                    CONNECT BY PRIOR PROCESSGROUPNAME = SUPERPROCESSGROUPNAME)) a\r\n" + 
			"GROUP BY a.LOTNAME";
	
	
	
	public static final String SCRAP_PROCESS_OPERATION = "SELECT RAWTOHEX(SYS_GUID()) SEQ, a.*\r\n" + 
			"FROM (\r\n" + 
			"SELECT DISTINCT pfS.PROCESSOPERATIONNAME AS MESGX,\r\n" + 
			"				lh.PRODUCTREQUESTNAME AS AUFNR,\r\n" + 
			"				lh.PRODUCTSPECNAME AS MATNR,\r\n" + 
			"				'EA' AS GMEIN,\r\n" + 
			"				'1' AS STATUS,\r\n" + 
			"				TO_CHAR(:NOWDATE, 'yyyy-MM-dd') AS CDATE,\r\n" + 
			"				TO_CHAR(:NOWDATE, 'HH24:MI:SS') AS CTIME,\r\n" + 
			"				COUNT(DISTINCT lh.LOTNAME) \r\n" + 
			"					OVER (PARTITION BY pfS.PROCESSOPERATIONNAME, lh.PRODUCTREQUESTNAME) AS  GAMNG,\r\n" + 
			"				DECODE(lh.FACTORYNAME, 'BADT', 'V151', lh.FACTORYNAME) AS WERKS,\r\n" + 
			"				'MODULE' AS FABID,\r\n" + 
			"				'SM' AS DATATYPE\r\n" + 
			" FROM LOT lh LEFT JOIN PROCESSFLOWSEQ pfS ON pfS.SEQUENCEID = lh.BEFORESEQUENCEID\r\n" + 
			" WHERE (((lh.LOTSTATE = 'Scrapped' OR ETCSTATE = 'OUT') AND\r\n" + 
			"              :STARTTIME <= lh.LASTEVENTTIMEKEY AND lh.LASTEVENTTIMEKEY < :ENDTIME) OR\r\n" + 
			"             (DEGRADEFLAG = 'Y' AND :STARTTIME <= lh.DEGRADETIMEKEY AND lh.DEGRADETIMEKEY < :ENDTIME))\r\n" + 
			"	AND pfS.PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"	AND lh.PRODUCTREQUESTNAME IS NOT NULL	 \r\n" + 
			") a";
	
	public static final String BACKSTOCK_PROCESS_OPERATION = "SELECT RAWTOHEX(SYS_GUID()) SEQ, a.*\r\n" + 
			"FROM (SELECT DISTINCT pfS.PROCESSOPERATIONNAME                                                  MESGX,\r\n" + 
			"                      L.PRODUCTREQUESTNAME                                                      AUFNR,\r\n" + 
			"                      L.PRODUCTSPECNAME                                                         MATNR,\r\n" + 
			"                      'EA'                                                                      GMEIN,\r\n" + 
			"                      '1'                                                                       STATUS,\r\n" + 
			"                      TO_CHAR(:NOWDATE, 'yyyy-MM-dd')                                           CDATE,\r\n" + 
			"                      TO_CHAR(:NOWDATE, 'HH24:MI:SS')                                           CTIME,\r\n" + 
			"                      COUNT(DISTINCT L.LOTNAME)\r\n" + 
			"                            OVER (PARTITION BY pfS.PROCESSOPERATIONNAME, L.PRODUCTREQUESTNAME)  GAMNG,\r\n" + 
			"                      DECODE(L.FACTORYNAME, 'BADT', 'V151', L.FACTORYNAME)                      WERKS,\r\n" + 
			"                      'MODULE'                                                                  FABID,\r\n" + 
			"                      'SP'                                                                      DATATYPE\r\n" + 
			"      FROM LOT L LEFT JOIN PROCESSFLOWSEQ pfS ON pfS.SEQUENCEID = L.BEFORESEQUENCEID\r\n" + 
			"      WHERE pfS.PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"        AND L.PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"        AND L.LOTSTATE = 'Withdrew' \r\n" + 
			"        AND L.LOTNAME IN (:LOTNAMELIST)\r\n" + 
			"     ) a";
	
	public static final String SCRAP_LOTNAME_OPERATION = "SELECT DISTINCT pfS.PROCESSOPERATIONNAME OLDPROCESSOPERATIONNAME, lh.PRODUCTREQUESTNAME, lh.LOTNAME\r\n" + 
			"FROM LOT lh LEFT JOIN PROCESSFLOWSEQ pfS ON pfS.SEQUENCEID = lh.BEFORESEQUENCEID\r\n" + 
			"WHERE  (((lh.LOTSTATE = 'Scrapped' OR ETCSTATE = 'OUT') AND\r\n" + 
			"        :STARTTIME <= lh.LASTEVENTTIMEKEY AND lh.LASTEVENTTIMEKEY < :ENDTIME) OR\r\n" + 
			"       (DEGRADEFLAG = 'Y' AND :STARTTIME <= lh.DEGRADETIMEKEY AND lh.DEGRADETIMEKEY < :ENDTIME))\r\n" + 
			"	AND pfS.PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"	AND lh.PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"GROUP BY pfS.PROCESSOPERATIONNAME, lh.PRODUCTREQUESTNAME, lh.LOTNAME";
	public static final String BACKSTOCK_LOTNAME_OPERATION = "SELECT DISTINCT pfS.PROCESSOPERATIONNAME OLDPROCESSOPERATIONNAME, L.PRODUCTREQUESTNAME, L.LOTNAME\r\n" + 
			"FROM LOT L LEFT JOIN PROCESSFLOWSEQ pfS ON pfS.SEQUENCEID = L.BEFORESEQUENCEID\r\n" + 
			"WHERE pfS.PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"  AND L.PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"  AND L.LOTSTATE = 'Withdrew'\r\n" + 
			"  AND L.LOTNAME IN (:LOTNAMELIST)";
	
	
	public static final String INSER_SAP_LOTNAME = "INSERT INTO SAPLOTNAMELOG(SAPLOTNAME, LOTNAME) VALUES (:SAPLOTNAME, :LOTNAME)";
	
	public static final String FINISH_LOTNAME_OPERATION = "SELECT DISTINCT OLDPROCESSOPERATIONNAME, PRODUCTREQUESTNAME , LOTNAME\r\n" + 
			"FROM LOTHISTORY\r\n" + 
			"WHERE PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"  AND PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"  AND EVENTNAME = 'TrackOut'\r\n" + 
			"   AND :STARTTIME <= TIMEKEY AND TIMEKEY < :ENDTIME\r\n" + 
			"GROUP BY OLDPROCESSOPERATIONNAME, PRODUCTREQUESTNAME , LOTNAME\r\n" + 
			"";
	
	public static final String FIND_OPREATION = "SELECT RAWTOHEX(SYS_GUID()) SEQ, PROCESSOPERATIONNAME MESGX, LOTNAME LOTID, DECODE(FACTORYNAME,'BADT','V151',FACTORYNAME) WERKS, 'MODULE' FABID, LOTNAME SEQ\r\n" + 
			"FROM LOT\r\n" + 
			"WHERE LOTNAME IN (:LOTNAME)";
	public static final String FIND_OPREATION_REWORK = "SELECT RAWTOHEX(SYS_GUID()) SEQ, \r\n" + 
			"	P.PROCESSOPERATIONNAME MESGX, \r\n" + 
			"	L.LOTNAME LOTID,\r\n" + 
			"	DECODE(L.FACTORYNAME,'BADT','V151',L.FACTORYNAME) WERKS,\r\n" + 
			"	'MODULE' FABID, \r\n" + 
			"	L.LOTNAME SEQ\r\n" + 
			"FROM LOT L,PROCESSFLOWSEQ P\r\n" + 
			"WHERE L.BEFORESEQUENCEID = P.SEQUENCEID(+)\r\n" + 
			"	AND L.LOTNAME IN (:LOTNAME)";
	
	public static final String INMAKING_PROCESS_OPERATION = "SELECT RAWTOHEX(SYS_GUID()) SEQ, a.*\r\n" + 
			"FROM (SELECT DISTINCT PROCESSOPERATIONNAME                                                                 MESGX,\r\n" + 
			"                      PRODUCTREQUESTNAME                                                                   AUFNR,\r\n" + 
			"                      PRODUCTSPECNAME                                                                      MATNR,\r\n" + 
			"                      'EA'                                                                                GMEIN,\r\n" + 
			"                      '1'                                                                                  STATUS,\r\n" + 
			"                      TO_CHAR(:NOWDATE, 'yyyy-MM-dd')                                                      CDATE,\r\n" + 
			"                      TO_CHAR(:NOWDATE, 'HH24:MI:SS')                                                      CTIME,\r\n" + 
			"                      COUNT(DISTINCT LOTNAME) OVER (PARTITION BY PROCESSOPERATIONNAME, PRODUCTREQUESTNAME) GAMNG,\r\n" + 
			"                      DECODE(FACTORYNAME,'BADT','V151',FACTORYNAME)                                                                          WERKS,\r\n" + 
			"                      'MODULE'                                                                             FABID\r\n" + 
			"      FROM LOT\r\n" + 
			"      WHERE PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"        AND PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"        AND LOTSTATE = 'Released') a";
	
	public static final String INMAKING_LOTNAME_OPERATION = "SELECT DISTINCT PROCESSOPERATIONNAME OLDPROCESSOPERATIONNAME, PRODUCTREQUESTNAME, LOTNAME\r\n" + 
			"FROM LOT\r\n" + 
			"WHERE PROCESSOPERATIONNAME IS NOT NULL\r\n" + 
			"  AND PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"  AND LOTSTATE NOT IN ('Completed', 'Scrapped')\r\n" + 
			"GROUP BY PROCESSOPERATIONNAME, PRODUCTREQUESTNAME, LOTNAME";
	
	public static final String UPDATE_LG03_RESULTCODE = "UPDATE BSERPINF_LG03 SET RESULTCODE = :RESULTCODE WHERE SEQ = :SEQ";
	
	public static final String UPDATE_PROCESSFLOW_WHETHERTOUPLOAD = "UPDATE POSPOSSIBLEFLOW SET SAPUPLOADFLAG = 'Y' WHERE PROCESSFLOWNAME = :FLOWMC AND CONDITIONID = 'BADT_' || :MATNR";

	public static final String UPDATE_PRODUCT_REQUEST = "UPDATE PRODUCTREQUEST\r\n" + 
			"SET PLANQUANTITY        = :GAMNG,\r\n" + 
			"    PRODUCTREQUESTSTATE = :PRODUCTREQUESTSTATE\r\n" + 
			"    WHERE PRODUCTREQUESTNAME = :AUFNR";
	
	public static final String UPDATE_ENUMDEFVALUE_BY_ENUMNAME = "UPDATE ENUMDEFVALUE SET ENUMVALUE = :ENUMVALUE WHERE ENUMNAME = :ENUMNAME";
	public static final String UPDATE_ENUMDEFVALUE_BY_ENUMNAME_P = "UPDATE ENUMDEFVALUE SET ENUMVALUE = ? WHERE ENUMNAME = ?";
	
	public static final String UPDATE_SAPVIRTUALLOTLOG_STATE = "UPDATE SAPVIRTUALLOTLOG SET STATE = :STATE WHERE SEQ IN :SEQ";
	
	public static final String WORK_ORDER_MODIFY_LOG = "INSERT INTO SAPVIRTUALLOTLOG\r\n" + 
			"(SEQ, SAPLOTNAME, PROCESSOPERATIONNAME, PRODUCTREQUESTNAME, STARTTIMEKEY, ENDTTIMEKEY, PRODUCTNUM, CREATTIME,\r\n" + 
			" PRODUCTSPECNAME,\r\n" + 
			" UNIT, EVENTNAME, OLDPRODUCTSPECNAME, OLDPRODUCTREQUESTNAME, FACTORYNAME, FABID)\r\n" + 
			"VALUES (:SEQ, :LOTID, :MESGX, :NAUFNR, :STARTTIMEKEY, :ENDTTIMEKEY, :GAMNG, :CREATTIME, :NMATNR,\r\n" + 
			"        :GMEIN, :EVENTNAME, :OMATNR, :OAUFNR, :WERKS, :FABID)";
	
	public static final String PRODUCT_RECEIVING_LOG = "INSERT INTO SAPVIRTUALLOTLOG(SEQ, SAPLOTNAME, PRODUCTREQUESTNAME, STARTTIMEKEY, ENDTTIMEKEY,\r\n" + 
			"                             PRODUCTNUM, CREATTIME, PRODUCTSPECNAME, UNIT, STATUS, EVENTNAME, FACTORYNAME, LOTGRADE,\r\n" + 
			"                             LOCATION)\r\n" + 
			"VALUES (:SEQ, :PALLETID, :AUFNR, :STARTTIMEKEY, :ENDTTIMEKEY, :GAMNG,\r\n" + 
			"        :CREATTIME, :MATNR,\r\n" + 
			"        :GMEIN, :STATUS, :EVENTNAME, :WERKS, :CHARG,\r\n" + 
			"        :LGORT)";
	
	public static final String PRODUCT_SHIPMENT_LOG = "INSERT INTO SAPVIRTUALLOTLOG(SEQ, SAPLOTNAME, STARTTIMEKEY, ENDTTIMEKEY,\r\n" + 
			"                             PRODUCTNUM, CREATTIME, PRODUCTSPECNAME, UNIT, STATUS, EVENTNAME, FACTORYNAME, LOTGRADE,\r\n" + 
			"                             LOCATION, NEWLOCATION)\r\n" + 
			"VALUES (:SEQ, :PALLETID, :STARTTIMEKEY, :ENDTTIMEKEY, :GAMNG,\r\n" + 
			"        :CREATTIME, :MATNR,\r\n" + 
			"        :GMEIN, :STATUS, :EVENTNAME, :WERKS, :CHARG,\r\n" + 
			"        :LGORT, :LGORT1)";
	
	
	public static final String MATERIALS_SHIPMENT_LOG = "INSERT INTO SAPVIRTUALLOTLOG(SEQ, STARTTIMEKEY, ENDTTIMEKEY,\r\n" + 
			"                                                     PRODUCTNUM, CREATTIME, PRODUCTSPECNAME, UNIT, STATUS, EVENTNAME, FACTORYNAME, LOTGRADE,\r\n" + 
			"                                                     LOCATION, NEWLOCATION)\r\n" + 
			"VALUES (:SEQ, :STARTTIMEKEY, :ENDTTIMEKEY, :GAMNG,\r\n" + 
			"    :CREATTIME, :MATNR,\r\n" + 
			"    :GMEIN, :STATUS, :EVENTNAME, :WERKS, :CHARG,\r\n" + 
			"    :LGORT, :LGORT1)";
	
	public static final String WORKORDER_BIND_LINE_LOG = "INSERT INTO SAPVIRTUALLOTLOG(SEQ, PRODUCTREQUESTNAME, PROCESSFLOWNAME, EVENTNAME, CREATTIME)\r\n" + 
			"VALUES (:SEQ, :AUFNR, :FLOWMC, :EVENTNAME, SYSDATE)";
	
	public static final String FIND_FINISH_LOG = "SELECT SEQ,\r\n" + 
			"       SAPLOTNAME                     LOTID,\r\n" + 
			"       PROCESSOPERATIONNAME           MESGX,\r\n" + 
			"       PRODUCTREQUESTNAME             AUFNR,\r\n" + 
			"       PRODUCTNUM                     GAMNG,\r\n" + 
			"       PRODUCTSPECNAME                MATNR,\r\n" + 
			"       UNIT                           GMEIN,\r\n" + 
			"       STATUS,\r\n" + 
			"       FACTORYNAME                    WERKS,\r\n" + 
			"       FABID,\r\n" + 
			"       TO_CHAR(:NOWDATE, 'yyyy-MM-dd') CDATE,\r\n" + 
			"       TO_CHAR(:NOWDATE, 'HH24:MI:SS') CTIME\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = 'FINISH'\r\n" + 
			"  AND STATE IS NULL";
	
	public static final String NEW_FIND_FINISH_LOG = "SELECT SEQ,\r\n" + 
			"       SAPLOTNAME                      LOTID,\r\n" + 
			"       PRODUCTREQUESTNAME              AUFNR,\r\n" + 
			"       PRODUCTNUM                      GAMNG,\r\n" + 
			"       PRODUCTSPECNAME                 MATNR,\r\n" + 
			"       STATUS,\r\n" + 
			"       FACTORYNAME                     WERKS,\r\n" + 
			"       IDNRK                           IDNRK,\r\n" + 
			"       CHARG                           CHARG,\r\n" + 
			"       LOCATION                        LGORT,\r\n" + 
			"       TO_CHAR(:NOWDATE, 'yyyy-MM-dd') CDATE,\r\n" + 
			"       TO_CHAR(:NOWDATE, 'HH24:MI:SS') CTIME\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = 'NEWFINISH'\r\n" + 
			"  AND STATE IS NULL";
	
	public static final String FIND_SCRAP_INMAKING_LOG = "SELECT SEQ,\r\n" + 
			"       SAPLOTNAME                      LOTID,\r\n" + 
			"       PROCESSOPERATIONNAME            MESGX,\r\n" + 
			"       PRODUCTREQUESTNAME              AUFNR,\r\n" + 
			"       PRODUCTNUM                      GAMNG,\r\n" + 
			"       PRODUCTSPECNAME                 MATNR,\r\n" + 
			"       UNIT                            GMEIN,\r\n" + 
			"       STATUS,\r\n" + 
			"       FACTORYNAME                     WERKS,\r\n" + 
			"       FABID,\r\n" + 
			"       :DATATYPE                       DATATYPE,\r\n" + 
			"       TO_CHAR(:NOWDATE, 'yyyy-MM-dd') CDATE,\r\n" + 
			"       TO_CHAR(:NOWDATE, 'HH24:MI:SS') CTIME\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = :EVENTNAME\r\n" + 
			"  AND STATE IS NULL";
	
	public static final String FIND_RECEIVING_LOG = "SELECT SEQ,\r\n" + 
			"       SAPLOTNAME         PALLETID,\r\n" + 
			"       PRODUCTREQUESTNAME AUFNR,\r\n" + 
			"       PRODUCTNUM         GAMNG,\r\n" + 
			"       PRODUCTSPECNAME    MATNR,\r\n" + 
			"       UNIT               GMEIN,\r\n" + 
			"       STATUS,\r\n" + 
			"       FACTORYNAME        WERKS,\r\n" + 
			"       LOCATION           LGORT,\r\n" + 
			"       LOTGRADE           CHARG,\r\n" + 
			"       :CDATE             CDATE,\r\n" + 
			"       :CTIME             CTIME\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = 'RECEIVING'\r\n" + 
			"  AND STATE IS NULL";
	
	
	public static final String FIND_PRODUCT_SHIPMENT_LOG = "SELECT SEQ,\r\n" + 
			"       SAPLOTNAME      PALLETID,\r\n" + 
			"       PRODUCTNUM      GAMNG,\r\n" + 
			"       PRODUCTSPECNAME MATNR,\r\n" + 
			"       UNIT            GMEIN,\r\n" + 
			"       NEWLOCATION     LGORT1,\r\n" + 
			"       STATUS,\r\n" + 
			"       FACTORYNAME     WERKS,\r\n" + 
			"       LOCATION        LGORT,\r\n" + 
			"       LOTGRADE        CHARG,\r\n" + 
			"       :CDATE          CDATE,\r\n" + 
			"       :CTIME          CTIME\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = 'PRODUCTSHIPMENT'\r\n" + 
			"  AND STATE IS NULL";
	
	public static final String FIND_MATERIALS_SHIPMENT_LOG = "SELECT SEQ,\r\n" + 
			"       PRODUCTNUM      GAMNG,\r\n" + 
			"       PRODUCTSPECNAME MATNR,\r\n" + 
			"       UNIT            GMEIN,\r\n" + 
			"       NEWLOCATION     LGORT1,\r\n" + 
			"       STATUS,\r\n" + 
			"       FACTORYNAME     WERKS,\r\n" + 
			"       LOCATION        LGORT,\r\n" + 
			"       LOTGRADE        CHARG,\r\n" + 
			"       :CDATE          CDATE,\r\n" + 
			"       :CTIME          CTIME\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = 'MATERIALSSHIPMENT'\r\n" + 
			"  AND STATE IS NULL";
	
	
	public static final String FIND_MODIFY_LOG = "SELECT SEQ,\r\n" + 
			"       FACTORYNAME           WERKS,\r\n" + 
			"       SAPLOTNAME            LOTID,\r\n" + 
			"       PRODUCTREQUESTNAME    NAUFNR,\r\n" + 
			"       PRODUCTSPECNAME       NMATNR,\r\n" + 
			"       OLDPRODUCTREQUESTNAME OAUFNR,\r\n" + 
			"       OLDPRODUCTSPECNAME    OMATNR,\r\n" + 
			"       FABID,\r\n" + 
			"       PROCESSOPERATIONNAME  MESGX,\r\n" + 
			"       PRODUCTNUM            GAMNG,\r\n" + 
			"       UNIT                  GMEIN,\r\n" + 
			"       :CDATE                CDATE,\r\n" + 
			"       :CTIME                CTIME\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = 'MODIFY'\r\n" + 
			"  AND STATE IS NULL";
	
	public static final String FIND_WORKORDER_BIND_LINE_LOG = "SELECT SEQ, PRODUCTREQUESTNAME AUFNR,\r\n" + 
			"       PROCESSFLOWNAME    FLOWMC\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = 'WORKORDERBINDLINE'\r\n" + 
			"  AND STATE IS NULL";
	
	public static final String FIND_MATERIAL_DETAILS = "SELECT CONSUMABLENAME,\r\n" + 
			"       CONSUMABLETYPE,\r\n" + 
			"       CONSUMABLESPECNAME,\r\n" + 
			"       MATERIALLOCATIONNAME,\r\n" + 
			"       CREATEQUANTITY,\r\n" + 
			"       QUANTITY,\r\n" + 
			"       FACTORYNAME,\r\n" + 
			"       AREANAME,\r\n" + 
			"       CONSUMABLESTATE,\r\n" + 
			"       VERSION\r\n" + 
			"FROM CONSUMABLE\r\n" + 
			"WHERE CONSUMABLENAME = :CONSUMABLENAME";
	
	public static final String FIND_MATERIAL_DETAILS_COUNT = "SELECT nvl(count(CONSUMABLENAME), 0) FROM CONSUMABLE WHERE CONSUMABLENAME = :CONSUMABLENAME";
	
	public static final String FIND_BOMS = "SELECT MATNR,\r\n" + 
			"       STLNR,\r\n" + 
			"       STLAN,\r\n" + 
			"       STLAL,\r\n" + 
			"       WERKS,\r\n" + 
			"       BMENG,\r\n" + 
			"       BMEIN,\r\n" + 
			"       IDNRK,\r\n" + 
			"       MENGE,\r\n" + 
			"       MEINS,\r\n" + 
			"       ALPGR,\r\n" + 
			"       EWAHR,\r\n" + 
			"       ERNAM,\r\n" + 
			"       DUMPS,\r\n" + 
			"       LASTEVENTTIMEKEY,\r\n" + 
			"       LASTEVENTTIME,\r\n" + 
			"       LASTEVENTNAME,\r\n" + 
			"       LASTEVENTUSER,\r\n" + 
			"       LASTEVENTCOMMENT\r\n" + 
			"FROM SAPBOMDETAIL\r\n" + 
			"WHERE MATNR = :MATNR\r\n" + 
			"  AND STLNR = :STLNR";
	
	public static final String INSERT_BOM = "INSERT INTO SAPBOMDETAIL(MATNR, STLNR, STLAN, STLAL, WERKS, BMENG, BMEIN, IDNRK, MATKL, MENGE, MEINS, ALPGR, EWAHR, ERNAM,\r\n" + 
			"                         DUMPS, LASTEVENTTIMEKEY, LASTEVENTTIME, LASTEVENTNAME, LASTEVENTUSER, LASTEVENTCOMMENT,MAKTX,OJTXP)\r\n" + 
			"VALUES (:MATNR, :STLNR, :STLAN, :STLAL, :WERKS, :BMENG, :BMEIN, :IDNRK, :MATKL, :MENGE, :MEINS, :ALPGR, :EWAHR, :ERNAM, :DUMPS,\r\n" + 
			"        :LASTEVENTTIMEKEY, :LASTEVENTTIME, :LASTEVENTNAME, :LASTEVENTUSER, :LASTEVENTCOMMENT,:MAKTX,:OJTXP)";
	
	public static final String INSERT_BOM_HISTORY = "INSERT INTO SAPBOMDETAILHISTORY(MATNR, STLNR, STLAN, STLAL, WERKS, BMENG, BMEIN, IDNRK, MATKL, MENGE, MEINS, ALPGR, EWAHR, ERNAM,\r\n" + 
			"                         DUMPS, TIMEKEY, EVENTTIME, EVENTNAME, EVENTUSER, EVENTCOMMENT,MAKTX,OJTXP)\r\n" + 
			"VALUES (:MATNR, :STLNR, :STLAN, :STLAL, :WERKS, :BMENG, :BMEIN, :IDNRK, :MATKL, :MENGE, :MEINS, :ALPGR, :EWAHR, :ERNAM, :DUMPS,\r\n" + 
			"        :LASTEVENTTIMEKEY, :LASTEVENTTIME, :LASTEVENTNAME, :LASTEVENTUSER, :LASTEVENTCOMMENT,:MAKTX,:OJTXP)";
	
	public static final String DEL_BOMS = "DELETE FROM SAPBOMDETAIL WHERE MATNR IN (:MATNR)";
	
	
	// FGCodeAttribute
	public static final String SELECT_ATTRIBUTE_VALUE_BY_SPEC_KEY = "SELECT VALUE FROM BSPRODUCTSPECATTRIBUTE WHERE PRODUCTSPECNAME = ? AND ATTRIBUTEKEY = ?";
	public static final String SELECT_ATTRIBUTE_VALUE_LIKE_SPEC_KEY = "SELECT VALUE FROM BSPRODUCTSPECATTRIBUTE WHERE PRODUCTSPECNAME = ? AND ATTRIBUTEKEY LIKE ?";
	
	// Data Collect
	public static final String INSERT_BSMACHINEENERGYDATA = "INSERT INTO BSMACHINEENERGYDATA(MACHINENAME, TIMEKEY, DATANAME, DATAVALUE) VALUES(?, ?, ?, ?)";
	public static final String CHECK_LOTINBSMACHINEENERGYDATA = "SELECT LOTNAME FROM BSPRODUCTPROCESSDATA WHERE LOTNAME = ? AND PROCESSOPERATIONNAME = 'T9999'";

	// Name Rule
	public static final String CHECK_RULE_HAS_ARGUMENT = "SELECT RULENAME ,SECTIONNAME FROM NAMERULEATTRDEF WHERE RULENAME = ? AND SECTIONTYPE = 'Argument'";
	
	public static final String SELECT_MACHINE_ENERGY_DATA = "SELECT MACHINENAME,TIMEKEY,DATANAME,DATAVALUE FROM BSMACHINEENERGYDATA WHERE TIMEKEY >= ? AND TIMEKEY < ?";
	
	// select recently 4 hours' alarm msg
	public static final String SELECT_ALARM_MSG = "SELECT TIMEKEY, USERID, CAPTION, MESSAGE FROM BSOICTIPS WHERE STATUS=0 AND TO_DATE(SUBSTR(TIMEKEY,1,14), 'yyyyMMddHH24miss') >  (SYSDATE + NUMTODSINTERVAL(-4, 'hour'))";
	public static final String SET_ALARM_MSG_SEND = "UPDATE BSOICTIPS SET STATUS=1 WHERE STATUS=0 AND TO_DATE(SUBSTR(TIMEKEY,1,14), 'yyyyMMddHH24miss') >  (SYSDATE + NUMTODSINTERVAL(-4, 'hour'))";
	
	//get black/white list	
	public static final String SELECT_BLACK_OR_WHITE_LIST = "SELECT LOTNAME, PRODUCTSPECNAME, LISTTYPE, PRODUCTREQUESTNAME, PACKINGFLAG FROM LOTBLACKWHITELIST WHERE FACTORYNAME = 'BADT' AND LOTNAME = ?";
	public static final String SELECT_RISK_LIST = "SELECT LOTNAME, PRODUCTSPECNAME, LISTTYPE, PROCESSOPERATIONNAME, ACTION FROM LOTBLACKWHITELIST WHERE FACTORYNAME = 'BADT' AND LISTTYPE = 'R' AND LOTNAME = ? AND PRODUCTSPECNAME = ?";
	
	//get special list
	public static final String SELECT_SPECIAL_LIST = "SELECT PRODUCTREQUESTNAME, PRODUCTSPECNAME, PACKINGFLAG FROM BSPRBLACKLIST b WHERE PRODUCTREQUESTNAME = ? AND PRODUCTSPECNAME = ?";
	
	//query SDParameter/SDData list
	public static final String SELECT_SDPARAMETER_LIST = "SELECT PRODUCTSPECNAME ,PROCESSOPERATIONNAME  FROM SDPARAMETER s WHERE PRODUCTSPECNAME =:PRODUCTSPECNAME AND PROCESSOPERATIONNAME =:PROCESSOPERATIONNAME";
	public static final String SELECT_SDDATA = "SELECT *FROM BSPRODUCTPROCESSDATA b WHERE TIMEKEY = ?";
	public static final String SELECT_SDDATA_ITEM = "SELECT *FROM BSPRODUCTPROCESSDATA b WHERE TIMEKEY = ?";

	public static final String SELECT_ABNORMAL_LOT_LIST = "SELECT DISTINCT \r\n" + 
			"	PRODUCTREQUESTNAME AS AUFNR,\r\n" + 
			"	PROCESSOPERATIONNAME AS MESGX,\r\n" + 
			"	DECODE(LASTEVENTNAME,'PanelLineOut','LM','TrackOut','TP','HD') AS STATU,\r\n" + 
			"	TO_CHAR(LASTEVENTTIME , 'yyyy-MM-dd') AS AUDAT,\r\n" + 
			"	TO_CHAR(SYSDATE , 'yyyy-MM-dd') AS CDATE,\r\n" + 
			"	TO_CHAR(SYSDATE , 'HH24:MI:SS') AS CTIME,\r\n" + 
			"	COUNT(DISTINCT LOTNAME) OVER (PARTITION BY PROCESSOPERATIONNAME, PRODUCTREQUESTNAME,LASTEVENTNAME,TO_CHAR(LASTEVENTTIME , 'yyyy-MM-dd')) AS ZWQTY\r\n" + 
			"FROM\r\n" + 
			"	LOT l\r\n" + 
			"WHERE\r\n" + 
			"	LOTSTATE = 'Released'\r\n" + 
			"	AND LOTPROCESSSTATE = 'WAIT'\r\n" + 
			"	AND LASTEVENTNAME IN ('Hold','ReserveAutoHold','PanelLineOut','TrackOut')\r\n" + 
			"	AND LASTEVENTTIME < SYSDATE - INTERVAL '7' DAY";
	
	public static final String CUTTING_BACKSTOCK_PROCESS_OPERATION = "SELECT RAWTOHEX(SYS_GUID()) SEQ, a.*\r\n" + 
			"FROM (SELECT DISTINCT L.PROCESSOPERATIONNAME                                                  MESGX,\r\n" + 
			"                      L.PRODUCTREQUESTNAME                                                      AUFNR,\r\n" + 
			"                      L.PRODUCTSPECNAME                                                         MATNR,\r\n" + 
			"                      'EA'                                                                      GMEIN,\r\n" + 
			"                      '1'                                                                       STATUS,\r\n" + 
			"                      TO_CHAR(SYSDATE, 'yyyy-MM-dd')                                           	CDATE,\r\n" + 
			"                      TO_CHAR(SYSDATE, 'HH24:MI:SS')                                           	CTIME,\r\n" + 
			"                      COUNT(DISTINCT L.LOTNAME)\r\n" + 
			"                            OVER (PARTITION BY L.PROCESSOPERATIONNAME, L.PRODUCTREQUESTNAME)  GAMNG,\r\n" + 
			"                      'V151'																	WERKS,\r\n" + 
			"                      DECODE(L.FACTORYNAME, 'BADT', 'MODULE', L.FACTORYNAME)                   	FABID,\r\n" + 
			"                      'SP'                                                                      DATATYPE\r\n" + 
			"      FROM LOT L \r\n" + 
			"      WHERE L.PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"        AND L.LOTSTATE = 'Withdrew' \r\n" + 
			"        AND L.PRODUCTTYPE = 'QPanel'\r\n" + 
			"        AND L.LOTNAME = :LOTNAME\r\n" + 
			"     ) a";
	
	public static final String CUTTING_BACKSTOCK_LOTNAME_OPERATION = "SELECT DISTINCT L.PROCESSOPERATIONNAME OLDPROCESSOPERATIONNAME, L.PRODUCTREQUESTNAME, L.LOTNAME\r\n" + 
			"			FROM LOT L\r\n" + 
			"			WHERE L.PRODUCTREQUESTNAME IS NOT NULL\r\n" + 
			"			  AND L.LOTSTATE = 'Withdrew'\r\n" + 
			"			  AND L.PRODUCTTYPE = 'QPanel'\r\n" + 
			"			  AND L.LOTNAME = :LOTNAME";
	
	// Cutting
	public static final String	CHECK_OUT_OF_STORAGE = "SELECT DISTINCT PRODUCTSPECNAME,LOTSTATE,PROCESSOPERATIONNAME,PROCESSFLOWNAME,LOTGRADE,LOTDETAILGRADE,PRODUCTREQUESTNAME FROM LOT WHERE LOTNAME IN (:LOTNAME)";

	//MaterialState
	public static final String CHECK_MATERIAL_EXIST = "SELECT *FROM CONSUMABLESTATE c WHERE CONSUMABLENAME = :CONSUMABLENAME";
	public static final String CHECK_MATERIAL_BIND = "SELECT *FROM BSLOTCONSUMABLEASSY b, LOT l WHERE b.CONSUMABLENAME =:CONSUMABLENAME OR l.LOTNAME =:CONSUMABLENAME OR l.MODULENAME =:CONSUMABLENAME";
	public static final String CHECK_MATERIAL_RECYCLE = "SELECT CONSUMABLENAME ,CONSUMABLETYPE ,RECYCLESTATE FROM BSCONSUMABLERECYCLEINFO b WHERE CONSUMABLENAME =:CONSUMABLENAME";
	public static final String INSERT_CONSUMABLESTATEHISTORY = "INSERT INTO CONSUMABLESTATEHISTORY (CONSUMABLENAME, CONSUMABLETYPE, FACTORYNAME, LOTNAME, PROCESSOPERATIONNAME, CONSUMABLESPECNAME, MACHINENAME, EVENTNAME, EVENTUSER, TIMEKEY, EVENTCOMMENT, MATERIALSTATE, MATERIALJUDGE, MATERIALDECS, REMARK1, EVENTTIME, CREATETIMES, REINPUTTIMES, THROWINPUTTIMES) VALUES(:CONSUMABLENAME, :CONSUMABLETYPE, :FACTORYNAME, :LOTNAME, :PROCESSOPERATIONNAME, :CONSUMABLESPECNAME, :MACHINENAME, :EVENTNAME, :EVENTUSER, :TIMEKEY, :EVENTCOMMENT, :MATERIALSTATE, :MATERIALJUDGE, :MATERIALDECS, :REMARK1, :EVENTTIME, :CREATETIMES, :REINPUTTIMES, :THROWINPUTTIMES)";
	
	public static final String SELECT_ASSIGN_HISTORY = "SELECT LOTNAME ,BLUNAME FROM LOTHISTORY l WHERE LOTNAME =? AND BLUNAME IS NOT NULL";
	
	//operationControl
	public static final String CHECK_OPERATION_CONTROL_EXIST = "SELECT\r\n" + 
			"	PRODUCTSPECNAME ,\r\n" + 
			"	PROCESSFLOWNAME ,\r\n" + 
			"	FROMOPERATION ,\r\n" + 
			"	TOOPERATION ,\r\n" + 
			"	CONTROLTIME ,\r\n" + 
			"	CONTROLTYPE \r\n" + 
			"	FROM OPERATIONCONTROL o\r\n" + 
			"WHERE\r\n" + 
			"	FACTORYNAME =:FACTORYNAME\r\n" + 
			"	AND PRODUCTSPECNAME =:PRODUCTSPECNAME\r\n" + 
			"	AND PROCESSFLOWNAME =:PROCESSFLOWNAME\r\n" + 
			"	AND FROMOPERATION =:FROMOPERATION\r\n" + 
			"	AND TOOPERATION =:TOOPERATION";
	
	// sap : upload bind material 
	public static final String SELECT_SAP_BIND_MATERIAL = "SELECT RAWTOHEX(SYS_GUID()) SEQ, a.*\r\n" + 
			"FROM (SELECT DISTINCT l.PRODUCTREQUESTNAME                                               AUFNR,\r\n" + 
			"                      l.PRODUCTSPECNAME                                               MATNR,\r\n" + 
			"                      DECODE(l.FACTORYNAME, 'BADT', 'V151', l.FACTORYNAME)               WERKS,\r\n" + 
			"                      c.CONSUMABLESPECNAME                                               IDNRK,\r\n" + 
			"                      c.CONSUMABLENAME		                                             LOTID,\r\n" + 
			"                      COUNT(DISTINCT l.LOTNAME) OVER (PARTITION BY l.PRODUCTREQUESTNAME,\r\n" + 
			"                      b.CONSUMABLENAME) 												 GAMNG,\r\n" + 
			"                      c.CHARG 			                                                 CHARG,\r\n" + 
			"                      p.LGORT                                                            LGORT,\r\n" + 
			"                      '1'                                                                STATUS,\r\n" + 
			"                      TO_CHAR(SYSDATE , 'yyyy-MM-dd')                                    CDATE,\r\n" + 
			"                      TO_CHAR(SYSDATE, 'HH24:MI:SS')                                     CTIME\r\n" + 
			"      FROM (SELECT B.LOTNAME,B.CONSUMABLENAME,B.PROCESSOPERATIONNAME \r\n" + 
			"			FROM BSLOTCONSUMABLEASSY B \r\n" + 
			"			WHERE B.EVENTTIMEKEY >= :STARTTIME\r\n" + 
			"				AND B.EVENTTIMEKEY < :ENDTIME) b \r\n" + 
			"		  , LOT l  \r\n" + 
			"		  ,CONSUMABLE c   \r\n" + 
			"		  ,PROCESSOPERATIONSPEC p  \r\n" + 
			"      WHERE b.LOTNAME = l.LOTNAME\r\n" + 
			"      	AND b.CONSUMABLENAME = c.CONSUMABLENAME\r\n" + 
			"      	AND b.PROCESSOPERATIONNAME = p.PROCESSOPERATIONNAME) a";
	
	public static final String SELECT_SAP_BIND_MATERIAL_NOTSEND = "SELECT SEQ,\r\n" + 
			"       SAPLOTNAME                      LOTID,\r\n" + 
			"       PRODUCTREQUESTNAME              AUFNR,\r\n" + 
			"       PRODUCTNUM                      GAMNG,\r\n" + 
			"       PRODUCTSPECNAME                 MATNR,\r\n" + 
			"       STATUS,\r\n" + 
			"       FACTORYNAME                     WERKS,\r\n" + 
			"       IDNRK                           IDNRK,\r\n" + 
			"       CHARG                           CHARG,\r\n" + 
			"       LOCATION                        LGORT,\r\n" + 
			"       TO_CHAR(SYSDATE , 'yyyy-MM-dd') CDATE,\r\n" + 
			"       TO_CHAR(SYSDATE , 'HH24:MI:SS') CTIME\r\n" + 
			"FROM SAPVIRTUALLOTLOG\r\n" + 
			"WHERE EVENTNAME = 'BindMaterial'\r\n" + 
			"  AND STATE IS NULL";
	
	public static final String SELECT_Q_CONVERT_QUANTITY = "SELECT ACTIONVALUE FROM ENUMDEFVALUE WHERE ENUMNAME = 'QCellConvertQuantity' AND ENUMVALUE = ? AND ACTION = ? ";
	
	public static final String SELECT_SAP_BOM_BY_FGCODE = "SELECT :PRODUCTSPECNAME AS FGCode, IDNRK,BMENG,MENGE  FROM SAPBOMDETAIL START WITH MATNR = :PRODUCTSPECNAME CONNECT BY PRIOR IDNRK = MATNR ORDER BY IDNRK ";
	public static final String SELECT_SAP_BOM_BY_FGCODE_TYPE = "SELECT :PRODUCTSPECNAME AS FGCode, IDNRK,BMENG,MENGE,MATKL,DECODE(WERKS,'V151','BADT',WERKS) AS FACTORYNAME  FROM SAPBOMDETAIL WHERE MATKL = :MATERIALTYPE START WITH MATNR = :PRODUCTSPECNAME CONNECT BY PRIOR IDNRK = MATNR ORDER BY IDNRK ";
	public static final String SELECT_MACHINE_MATERIAL_DATA = "SELECT MACHINENAME ,MATERIALNAME,MATERIALTYPE,MATERIALSTATE FROM BSMACHINEMATERIAL WHERE MACHINENAME = ? AND MATERIALNAME = ?";

	public static final String SELECT_REASON_CODE = "SELECT *FROM REASONCODE r WHERE r.REASONCODE = ? ";
	
	// Reason Code
	public static final String check_code_exist = "SELECT REASONCODE FROM REASONCODE WHERE FACTORYNAME=? AND REASONCODETYPE=? AND REASONCODE=?";
	public static final String delete_code = "DELETE FROM REASONCODE WHERE FACTORYNAME=? AND REASONCODETYPE=? AND REASONCODE=?";
	public static final String insert_code_history = "INSERT INTO REASONCODEHISTORY (FACTORYNAME, REASONCODETYPE, REASONCODE, TIMEKEY, EVENTTIME, EVENTNAME, EVENTUSER, EVENTCOMMENT, DESCRIPTION, SUPERREASONCODE, LEVELNO, AVAILABILITY, YIELDFLAG, REPAIRFLAG, CHECKOUTTYPE) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String insert_code = "INSERT INTO REASONCODE (FACTORYNAME, REASONCODETYPE, REASONCODE, DESCRIPTION, SUPERREASONCODE, LEVELNO, AVAILABILITY, YIELDFLAG, REPAIRFLAG, CREATEUSER, CREATETIME, CHECKOUTTYPE) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String update_code = "UPDATE REASONCODE SET DESCRIPTION=?, SUPERREASONCODE=?, LEVELNO=?, AVAILABILITY=?, YIELDFLAG=?, REPAIRFLAG=?, UPDATEUSER=?, UPDATETIME=?, CHECKOUTTYPE=? WHERE FACTORYNAME=? AND REASONCODETYPE=? AND REASONCODE=?";
	
	// BSLotFutureAction
	public static final String SELECT_PANEL_PREOPERATION = "WITH A AS (\r\n" + 
			"SELECT FACTORYNAME,PROCESSFLOWNAME,PROCESSOPERATIONNAME, POSITIONGROUP ,POSITION \r\n" + 
			"FROM PROCESSFLOWSEQ \r\n" + 
			"WHERE FACTORYNAME = :FACTORYNAME \r\n" + 
			"	AND PROCESSFLOWNAME = :PROCESSFLOWNAME \r\n" + 
			"	AND PROCESSOPERATIONNAME = :PROCESSOPERATIONNAME \r\n" + 
			"	AND ROWNUM = 1)\r\n" + 
			"SELECT :LOTNAME AS LOTNAME, P.PROCESSOPERATIONNAME \r\n" + 
			"FROM PROCESSFLOWSEQ P INNER JOIN A \r\n" + 
			"	ON P.FACTORYNAME = A.FACTORYNAME\r\n" + 
			"	AND P.PROCESSFLOWNAME = A.PROCESSFLOWNAME\r\n" + 
			"WHERE P.POSITIONGROUP <= A.POSITIONGROUP \r\n" + 
			"	AND P.POSITION <= A.POSITION\r\n" + 
			"	ORDER BY P.POSITIONGROUP ,P.POSITION ";
	
	//AttributeHistory
	public static final String Insert_Attribute_History ="INSERT INTO PRODUCTSPECATTRIBUTEHISTORY\r\n" + 
			"(PRODUCTSPECNAME, ATTRIBUTEKEY, VALUE, DESCRIPTION, TIMEKEY, EVENTUSER, EVENTNAME, EVENTTIME, EVENTCOMMENT)\r\n" + 
			"VALUES(:PRODUCTSPECNAME, :ATTRIBUTEKEY, :VALUE, :DESCRIPTION, :TIMEKEY, :EVENTUSER, :EVENTNAME, :EVENTTIME, NULL)";
}

