存储过程代码.docx
《存储过程代码.docx》由会员分享，可在线阅读，更多相关《存储过程代码.docx（21页珍藏版）》请在冰豆网上搜索。
存储过程代码
CREATEORREPLACEPROCEDURESP_CASSETTE_START（
RET_CODEOUTNUMBER,
RET_DESCOUTVARCHAR2,
I_FAC_IDINVARCHAR2,
I_CST_IDINVARCHAR2,
I_WO_NBRINVARCHAR2,
I_WO_TYPEINVARCHAR2,---'P':
PROD,'E':
ENG,'R':
RWK,'M':
RMA
I_OPERATORINVARCHAR2,
I_COMMENTSINVARCHAR2,
I_FLOORINVARCHAR2,
I_HOSTNAMEINVARCHAR2
）
AS
---SQLCODENUMBERDEFAULT0;
---VariablesforPanelStatus---------------------
p_PNL_IDWP_PNLSTAT.PNL_ID%TYPEDEFAULT'';
p_SERIAL_NOWP_PNLSTAT.SERIAL_NO%TYPEDEFAULT'';
p_CST_IDWP_PNLSTAT.CST_ID%TYPEDEFAULT'';
p_PROD_NBRWP_PNLSTAT.PROD_NBR%TYPEDEFAULT'';
p_VERWP_PNLSTAT.VER%TYPEDEFAULT'';
p_LCD_PROD_NBRWP_PNLSTAT.LCD_PROD_NBR%TYPEDEFAULT'';
p_RTG_CODEWP_PNLSTAT.RTG_CODE%TYPEDEFAULT'';
p_STATUSWP_PNLSTAT.STATUS%TYPEDEFAULT'';
p_ISSUE_DATEWP_PNLSTAT.ISSUE_DATE%TYPEDEFAULT'1900-01-0100.00.00.000000';
p_WO_TYPEWP_PNLSTAT.WO_TYPE%TYPEDEFAULT'';
p_STOCK_DATEWP_PNLSTAT.STOCK_DATE%TYPEDEFAULT'1900-01-0100.00.00.000000';
p_OPERWP_PNLSTAT.OPER%TYPEDEFAULT0;
p_QA_NBRWP_PNLSTAT.QA_NBR%TYPEDEFAULT'';
p_NEXT_OPERWP_PNLSTAT.next_OPER%TYPEDEFAULT0;
p_MVIN_FLAGWP_PNLSTAT.MVIN_FLAG%TYPEDEFAULT'';
p_HOLD_FLAGWP_PNLSTAT.HOLD_FLAG%TYPEDEFAULT'';
p_TRANS_NBRWP_PNLSTAT.TRANS_NBR%TYPEDEFAULT0;
p_WO_NBRWP_PNLSTAT.WO_NBR%TYPEDEFAULT'';
p_SCRAP_FLAGWP_PNLSTAT.SCRAP_FLAG%TYPEDEFAULT'';
p_SCRAP_DATEWP_PNLSTAT.SCRAP_DATE%TYPEDEFAULT'1900-01-0100.00.00.000000';
p_TRANS_NBR2WP_PNLSTAT.TRANS_NBR2%TYPEDEFAULT0;
p_LEND_FLAGWP_PNLSTAT.LEND_FLAG%TYPEDEFAULT'';
p_LEND_TIMEWP_PNLSTAT.LEND_TIME%TYPEDEFAULT0.0;
p_PALLET_IDWP_PNLSTAT.PALLET_ID%TYPEDEFAULT'';
p_CART_IDWP_PNLSTAT.CART_ID%TYPEDEFAULT'';
p_CART_ID_CUSTWP_PNLSTAT.CART_ID_CUST%TYPEDEFAULT'';
p_RW_COUNTWP_PNLSTAT.RW_COUNT%TYPEDEFAULT0;
p_GRADE_GRPWP_PNLSTAT.GRADE_GRP%TYPEDEFAULT'';
p_GRADE_GRP1WP_PNLSTAT.GRADE_GRP1%TYPEDEFAULT'';------------
p_LCM_OWNERWP_PNLSTAT.LCM_OWNER%TYPEDEFAULT'';
p_LCM_GRADEWP_PNLSTAT.LCM_GRADE%TYPEDEFAULT'';
p_LCM_ABNWP_PNLSTAT.LCM_ABN%TYPEDEFAULT'';
p_LCD_GRADEWP_PNLSTAT.LCD_GRADE%TYPEDEFAULT'';
p_LCD_OWNERWP_PNLSTAT.LCD_OWNER%TYPEDEFAULT'';
p_LCD_ABN_CFWP_PNLSTAT.LCD_ABN_CF%TYPEDEFAULT'';
p_LCD_ABN_TFTWP_PNLSTAT.LCD_ABN_TFT%TYPEDEFAULT'';
p_LCD_ABN_LCDWP_PNLSTAT.LCD_ABN_LCD%TYPEDEFAULT'';
p_MAIN_OPERWP_PNLSTAT.MAIN_OPER%TYPEDEFAULT0;
p_SO_NBRWP_PNLSTAT.SO_NBR%TYPEDEFAULT'';
p_PROJ_CODEWP_PNLSTAT.PROJ_CODE%TYPEDEFAULT'';
p_ENGINEERWP_PNLSTAT.ENGINEER%TYPEDEFAULT'';
p_SCRAP_CODEWP_PNLSTAT.SCRAP_CODE%TYPEDEFAULT'';
p_DFCT_REASONWP_PNLSTAT.DFCT_REASON%TYPEDEFAULT'';
p_HOLD_CODEWP_PNLSTAT.HOLD_CODE%TYPEDEFAULT'';
p_HOLD_OPERWP_PNLSTAT.HOLD_OPER%TYPEDEFAULT0;
p_OQC_GRADEWP_PNLSTAT.OQC_GRADE%TYPEDEFAULT'';
p_ABN_FLAGWP_PNLSTAT.ABN_FLAG%TYPEDEFAULT'';
p_SHIPPING_NBRWP_PNLSTAT.SHIPPING_NBR%TYPEDEFAULT'';
p_FG_STORE_ACCTWP_PNLSTAT.FG_STORE_ACCT%TYPEDEFAULT'';
p_INSPECTOR_1WP_PNLSTAT.INSPECTOR_1%TYPEDEFAULT'';
p_INSPECTOR_2WP_PNLSTAT.INSPECTOR_2%TYPEDEFAULT'';
p_SG_STATUSWP_PNLSTAT.SG_STATUS%TYPEDEFAULT'';
p_SERIAL_NO_CUSTWP_PNLSTAT.SERIAL_NO_CUST%TYPEDEFAULT'';
p_LAST_TRANS_DTWP_PNLSTAT.LAST_TRANS_DT%TYPEDEFAULT'1900-01-0100.00.00.000000';
p_LAST_TRANS_CODEWP_PNLSTAT.LAST_TRANS_CODE%TYPEDEFAULT'';
p_COMMENTWP_PNLSTAT.COMMENTS%TYPEDEFAULT'';
p_RESERVED_1WP_PNLSTAT.RESERVED_1%TYPEDEFAULT'';
p_RESERVED_2WP_PNLSTAT.RESERVED_2%TYPEDEFAULT'';
p_RESERVED_3WP_PNLSTAT.RESERVED_3%TYPEDEFAULT'';
p_RESERVED_4WP_PNLSTAT.RESERVED_4%TYPEDEFAULT'';
p_RESERVED_5WP_PNLSTAT.RESERVED_5%TYPEDEFAULT'';
p_RESERVED_6WP_PNLSTAT.RESERVED_6%TYPEDEFAULT'';
p_RESERVED_7WP_PNLSTAT.RESERVED_7%TYPEDEFAULT'';
p_RESERVED_8WP_PNLSTAT.RESERVED_8%TYPEDEFAULT'';
p_RESERVED_9WP_PNLSTAT.RESERVED_9%TYPEDEFAULT'';
p_RESERVED_10WP_PNLSTAT.RESERVED_10%TYPEDEFAULT'';
p_LCD_SHIP_DATEWP_PNLSTAT.LCD_SHIP_DATE%TYPEDEFAULT'1900-01-0100.00.00.000000';
p_V_PNL_IDWP_PNLSTAT.V_PNL_ID%TYPEDEFAULT'';
p_HOLD_COMMENTWP_PNLSTAT.HOLD_COMMENT%TYPEDEFAULT'';
p_SOURCE_FABWP_PNLSTAT.SOURCE_FAB%TYPEDEFAULT'';
WO_PROD_NBRVARCHAR2（16）DEFAULT'';
WO_VERVARCHAR2（4）DEFAULT'';
WO_LCM_OWNERVARCHAR2（4）DEFAULT'';
WO_RTG_CODEVARCHAR2（7）DEFAULT'';
WO_STATUSVARCHAR2
（1）DEFAULT'';
WO_HOLD_FLAGVARCHAR2
（1）DEFAULT'';
WO_HOLD_OPERNUMBERDEFAULT0;
WO_HOLD_CODEVARCHAR2（4）DEFAULT'';
WO_HOLD_COMMENTVARCHAR2（80）DEFAULT'';
WO_TARGET_QTYNUMBERDEFAULT0;
WO_ACTUAL_QTYNUMBERDEFAULT0;
WO_FLOORVARCHAR2（4）DEFAULT'';
WO_OPERNUMBERDEFAULT0;
PROD_HOLD_OPERNUMBERDEFAULT0;
PROD_HOLD_CODEVARCHAR2（4）DEFAULT'';
PROD_HOLD_COMMENTVARCHAR2（80）DEFAULT'';
tmpSQLCODENUMBERDEFAULT0;
tmpDESCVARCHAR2（200）DEFAULT'';
tmpWORKctrNUMBERDEFAULT0;
tmpFIRSToperNUMBERDEFAULT0;
tmpMVINflagVARCHAR2
（1）DEFAULT'';
tmpPLANTholdVARCHAR2
（1）DEFAULT'';
tmpWIPholdVARCHAR2
（1）DEFAULT'';
tmpTRANStypeVARCHAR2（4）DEFAULT'';
tmpTRANSnbrNUMBERDEFAULT0;
tmpSHIFTidVARCHAR2
（1）DEFAULT'';
tmpCOUNTNUMBERDEFAULT0;
tmpCSTstatusVARCHAR2
（1）DEFAULT'';
tmpTIMEVARCHAR2（30）DEFAULT'1900-01-0100.00.00.000000';
tmpNEXToperNUMBERDEFAULT0;
tmpHOLDwcNUMBERDEFAULT0;
tmpHOLDcodeVARCHAR2（4）DEFAULT'';
tmpHOLDflagVARCHAR2
（1）DEFAULT'';
tmpHOLDcommentVARCHAR2（80）DEFAULT'';
tmpVALUENUMBERDEFAULT0;
tmpLCMabnVARCHAR2（20）DEFAULT'';
tmpPartialNUMBERDEFAULT0;
tmpOVERNUMBERDEFAULT0;
/*Addbynhbyk06/11/15*/
tmFAC_IDVARCHAR（20）DEFAULT0;
tmWO_TYPEVARCHAR（20）DEFAULT0;
tmWO_NBRVARCHAR（20）DEFAULT0;
tmpSHIFTVARCHAR2
（1）DEFAULT'';
ERROR_HANDLEEXCEPTION;
/*Addbynhbyk06/11/15*/
tmpEQUIP_NBRVARCHAR2（10）DEFAULT'';/*--2006/06/08-senceadded:
Forupdatewp_cststatuse--*/
CURSORcrWP_PNLSTATIS///////////////////////定义游标
SELECTPNL_ID,LCD_PROD_NBR,OPER,HOLD_FLAG,TRANS_NBR,LCM_ABN,LCD_GRADE,LCD_OWNER,
LCD_ABN_CF,LCD_ABN_TFT,LCD_ABN_LCD,SO_NBR,PROJ_CODE,ENGINEER,RESERVED_1,RESERVED_2,
RESERVED_3,RESERVED_4,RESERVED_5,RESERVED_6,RESERVED_7,RESERVED_8,RESERVED_9,RESERVED_10,V_PNL_ID,
HOLD_COMMENT,SOURCE_FAB
FROMWP_PNLSTAT
WHEREFAC_ID=I_FAC_IDANDCST_ID=I_CST_ID;
-----------------------------------------------------------------------------------
--/*Author:
KantTsai*/
--/**/
--/*Description（Purpose）:
WhenpanelwillbestartedwithPCSintheWIP*/
--/*table,clientapplicationwillpasssomeparameters*/
--/*intothisfunction.Thisfunctionwillaccordpanel*/
--/*IDtogetitsstatusandcheckstatus.Ifpanel*/
--/*createsucessful,functionreturn0value;return*/
--/*valueis1meansthistransactionisfail.*/
--/*FunctionwillusedtoResortingandRMAworkorder.*/
--/*Usage:
InputparametersincludethatFactoryID,PanelID,WorkOrder,*/
--/*WorkOrderType,Operator,comments*/
--/*ChangeLog:
2002-10-09Initial*/
--/*2003-10-20RevisedforCrossFABCassettedeletewhen*/
--cassettehadbestarted.*/
--2004/03/26[WP_CSTSTAT]TRANS_DATElogthestartdatetime*/
--2006/06/08UpdateFloor,EQUIP_NBRandOperatorinWP_CSTSTAT*/
-----------------------------------------------------------------------------------
BEGIN
tmFAC_ID:
=I_FAC_ID;/*Addbynhbyk06/11/15*/
tmWO_TYPE:
=I_WO_TYPE;
tmWO_NBR:
=I_WO_NBR;
------------------------------------
RET_CODE:
=0;
RET_DESC:
='PROGRAMSTART!
';
IFLTRIM（RTRIM（I_CST_ID））ISNULLORLTRIM（RTRIM（I_WO_NBR））ISNULLTHEN
RET_DESC:
='CASSETTE['||I_CST_ID||'],WORKORDER['||I_WO_NBR||']ISEMPTY!
';
RAISEERROR_HANDLE;
ENDIF;
/*CHECKWIPCHECKINGHOLD*/
SP_QUERY_PLANT_WIP_HOLD（RET_CODE,RET_DESC,tmFAC_ID,tmpWIPhold,tmpPLANThold）;
IFRET_CODE<>0THEN
RAISEERROR_HANDLE;
ENDIF;
IFtmpWIPhold='Y'ORtmpPLANThold='Y'THEN
RET_DESC:
='THEREISFABORWIPHOLD,PANELTRANSSTOP!
';
RAISEERROR_HANDLE;
ENDIF;
/*ENDWIPCHECKINGHOLD*/
/*--CHECKWHETHERANYPANELNOTATSTARTBANKORBEHELDORBELENTINTHECASSETTE--*/
SELECTCOUNT（*）INTOtmpCOUNTFROMWP_PNLSTATWHEREFAC_ID=I_FAC_IDANDCST_ID=I_CST_IDANDOPER<>1000;
IFtmpCOUNT>0THEN
RET_DESC:
='PANELLOCATIONISNOTSTARTBANK!
';
RAISEERROR_HANDLE;
ENDIF;
SELECTCOUNT（*）INTOtmpCOUNTFROMWP_PNLSTATWHEREFAC_ID=I_FAC_IDANDCST_ID=I_CST_IDANDHOLD_FLAG='Y';
IFtmpCOUNT>0THEN
RET_DESC:
='PANELINTHECASSETTEHADBEHELD!
QTY-'||LTRIM（RTRIM（TO_CHAR（tmpCOUNT）））;
RAISEERROR_HANDLE;
ENDIF;
SELECTCOUNT（*）INTOtmpCOUNTFROMWP_PNLSTATWHEREFAC_ID=I_FAC_IDANDCST_ID=I_CST_IDANDLEND_FLAG='Y';
IFtmpCOUNT>0THEN
RET_DESC:
='PANELINTHECASSETTEHADBELENT!
QTY-'||LTRIM（RTRIM（TO_CHAR（tmpCOUNT）））;
RAISEERROR_HANDLE;
ENDIF;
/*--ENDCHECKWHETHERANYPANELBEHELDORBELENTINTHECASSETTE--*/
/*CheckCassettewhetherexist*/
SELECTSTATUSINTOtmpCSTstatusFROMWP_CSTSTATWHEREFAC_ID=I_FAC_IDANDCST_ID=I_CST_ID;
IFtmpCSTstatus<>'1'THEN
RET_DESC:
='CASSETTE['||I_CST_ID||']STATUSERROR!
';
RAISEERROR_HANDLE;
ENDIF;
SELECTCOUNT（*）INTOtmpCOUNTFROMWP_PNLSTATWHEREFAC_ID=I_FAC_IDANDCST_ID=I_CST_ID;
IFtmpCOUNT=0ORtmpCOUNTISNULLTHEN
RET_DESC:
='CASSETTE['||I_CST_ID||']NOTEXITSorCASSETTEQTYISZERO!
';
RAISEERROR_HANDLE;
ENDIF;
/*EndcassetteSTATUSchecking*/
/*GETWORKORDERSTATUS*/
SP_QUERY_WORK_ORDER_STATUS（RET_CODE,RET_DESC,tmFAC_ID,tmWO_NBR,tmWO_TYPE,WO_RTG_CODE,WO_STATUS,WO_TARGET_QTY,
WO_ACTUAL_QTY,WO_PROD_NBR,WO_VER,WO_LCM_OWNER,WO_OPER,WO_FLOOR）;
/*===2005/10/17-sencemarked:
for虫ぃ跋だ加糷===*/
--IFWO_FLOOR<>I_FLOORTHEN
--RET_DESC:
='WORKORDERNOTALLOWSTARTON
升级会员 