存储过程代码.docx
- 文档编号:8998803
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:21
- 大小:19.13KB
存储过程代码.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 代码