c++

来源：互联网发布：sql是什么意思编辑：程序博客网时间：2024/04/28 12:46

/******************************************************************************//* *//* Procedure Name : getspdn.cxx *//* *//* Description : To provide specific lot information. *//* Mail : GETSPDN HDR= LOT= OPERATOR= *//* *//* Change History : *//* Author Date Contents *//* ------- -------- ------------------------------------------ *//* Chris Lin 02-20-01 Initial *//* Ellison Liu 2004-08-12 Add Lot Mask Data Comparison function *//* Elaine Chen 2006-10-30 Add WAC check Data function if non-PROD *//* *//******************************************************************************/

#include "getspdn.h"#include "spdndef.h"

extern "C"{ extern int get_SpdnGrade(char *,char *,char *,char *,char *); extern int CompLotMaskData(char* LotId); /* Ellison at 2004-08-12 */ extern int CheckPndnData(char* LotId, char* ReturnResult); /* Ellison 2005-03-08 */ extern int SpdnConstraint(char* LotId, char* ReturnResult); /* Elaine 2006-08-14 */ extern int CheckWacData(char *, char *); /* Elaine 2006-10-30 */}

/******************************************************************************** Constructor*******************************************************************************/GETSPDN ::GETSPDN (char *theName, char *thePrototype, int theFlags, char *theDestination) : ASMail(theName, thePrototype, theFlags, theDestination){ int i = 0; AddData(m_dataLOT = new ASData("LOT", "%32s", "", AS_REQUIRED)); AddData(m_dataOP = new ASData("OP", "%1s", "", AS_REQUIRED)); AddData(m_dataOPERATOR = new ASData("OPERATOR", "%32s", ""));

m_waferlist[0] = 0; m_gradelist[0]=0; m_retgrade[0] = 0;}

intGETSPDN ::ParseHeader(){ return(ASMail::ParseHeader());}

intGETSPDN ::ParseMessage(){ char LogMsg_Buff[MAXBUFFLEN/4];

sprintf(LogMsg_Buff, "[%s]Received:[%s %s]", myContainer->Name(), Name(), myMessage); LogPrintf(5, SEV_RECEIVE, LogMsg_Buff);

return(ASMail::ParseMessage());}

intGETSPDN ::Processing(){ int aReturnCode; int aErrorCode; char aErrorMsg[ERRORMSGLEN+1]; char aHoldErrorMsg[ERRORMSGLEN+1]; char compidlist[3000]; char gradelist[5000]; char aOP[2]; char retmsg[120]; char LogMsg_Buff[MAXBUFFLEN/4];

aReturnCode=0; aErrorCode=0; aErrorMsg[0]=0x00; aHoldErrorMsg[0]=0x00; compidlist[0]=0x00; gradelist[0]=0x00; retmsg[0]=0x00; LogMsg_Buff[0]=0x00; m_ErrorMessage=LogError("");

if (strlen((char *)*m_dataOP)==0) strcpy(aOP,"0"); else strcpy(aOP,(char *)*m_dataOP);

/* --- Ellison : CompLotMaskData() ---*/

aReturnCode = CompLotMaskData((char *)*m_dataLOT);

if (aReturnCode == 0) { printf("Ellison TEST Retult : CompLotMaskData Check Passed !!/n"); } else { if (aReturnCode == -100) { printf("Ellison TEST Retult : Lot has been checked & fail./n"); strcpy(aErrorMsg,"LOT-MASK Check Retult:Lot has been performed LOT-MASK check & fail. Please Call PC #5317"); } else if (aReturnCode == -811) { printf("Ellison TEST Retult : SQL Cursor Fail./n"); strcpy(aErrorMsg,"LOT-MASK Check Retult : ORACLE SQL Cursor Fail. Please Call PC #5317"); } else { printf("Ellison TEST Retult : Check Fail./n"); strcpy(aErrorMsg,"LOT-MASK Check Retult : LOT-MASK check & fail. Please Call PC #5317"); } m_ErrorMessage = LogError( aErrorMsg ); m_ErrorCode = aReturnCode; return(aReturnCode); }

/* -----End of CompLotMaskData() ----*/

/* --- Elaine 2006-08-14 check Constraint before package P --- */ aReturnCode = SpdnConstraint((char *)*m_dataLOT , aErrorMsg); if ( aReturnCode != 0 ) { m_ErrorMessage = LogError(aErrorMsg); m_ErrorCode = aReturnCode; return(m_ErrorCode); } printf("SpdnConstraint Pass !!/n");

/*---------- Elaine 2006-10-30 to check WAC data --------------*/ m_ErrorCode = CheckWacData((char *)*m_dataLOT, aErrorMsg);

/*------ if (m_ErrorCode == 9050) { SpdnHold(aHoldErrorMsg); // HOLD LOT because fail rate over. m_ErrorMessage = LogError(aHoldErrorMsg); return(m_ErrorCode); } else ------------------------------------*/

if(m_ErrorCode != SUCCESS) { m_ErrorMessage = LogError(aErrorMsg); return(m_ErrorCode); } sprintf(LogMsg_Buff, "LOT[%s] CheckWacData Passed!! ", (char *)*m_dataLOT); LogPrintf(7, SEV_CHECK, LogMsg_Buff);

/* ----------------------------------------------------------*/ /* --- Ellison 2005-03-08 to check PNDN-WaferGrade Data --- */ aReturnCode = CheckPndnData((char *)*m_dataLOT , aErrorMsg); if ( aReturnCode == -1 ) { /* DB Error */ m_ErrorMessage = LogError(aErrorMsg); m_ErrorCode = aReturnCode; return(-1); } if ( aReturnCode > 0 ) { /* some pndn without wafer-grade data */ char tmpMessage[1024]; tmpMessage[0] = 0x00;

sprintf(tmpMessage, "PNDN [%s] WITHOUT WAFER_GRADE DATA! PLEASE UPDATE PNDN DATA !", aErrorMsg); m_ErrorMessage = LogError(tmpMessage); m_ErrorCode = aReturnCode; return(-111); } printf("CheckPndnData Pass !!/n"); /* ----------------------------------------------------------*/

printf("aOP=%s/n",aOP); m_DCHost = new HOST(GroupId(), TPsrvQ(), RPMsrvQ()); m_ErrorCode = m_DCHost->RequestLOTINFO((char *)*m_dataOPERATOR, //userid "NONE", //password (char *)*m_dataLOT ); //lotid

sprintf(LogMsg_Buff, "RequestLOTINFO retcode=[%d],compidlist=%s^", m_ErrorCode,(char *)m_DCHost->ComponentIdList()); LogPrintf(7, SEV_CHECK, LogMsg_Buff);

printf("RequestLOTINFO retcode=%d,compidlist=%s/n",m_ErrorCode,(char *)m_DCHost->ComponentIdList());

if(m_ErrorCode!= SUCCESS) { printf("RequestLOTINFO retcode=%d is FAIL!!/n",m_ErrorCode); m_ErrorMessage = (char *)m_DCHost->ErrorMsg(); // get error message return(m_ErrorCode); } sprintf(compidlist,"%s", (char *)m_DCHost->ComponentIdList()); printf("compidlist=%s^/n",compidlist); if (strlen(compidlist)==0) { m_ErrorMessage = LogError("LOT NOT FOUND"); m_ErrorCode=-2; return(m_ErrorCode); } m_gradelist[0]=0x00; strcpy(retmsg,"NULL"); printf("OP=%s/n",(char *)*m_dataOP); //m_ErrorCode= get_SpdnGrade((char *)*m_dataLOT,compidlist,m_gradelist,retmsg);

m_ErrorCode= get_SpdnGrade((char *)*m_dataLOT,aOP,compidlist,m_gradelist,retmsg); m_ErrorMessage=LogError(retmsg);

sprintf(aErrorMsg,"%s",retmsg); /* m_ErrorCode=aReturnCode; m_ErrorMessage=aErrorMsg;*/

printf("m_ErrorCode=%d,m_ErrorMessage=%s^/n",m_ErrorCode,m_ErrorMessage);

sprintf(LogMsg_Buff, "get_SpdnGrade retcode=[%d],retmsg=%s,compidlist=%s,m_gradelist=%s^", m_ErrorCode,retmsg,(char *)m_DCHost->ComponentIdList(),m_gradelist); LogPrintf(7, SEV_CHECK, LogMsg_Buff);

if (m_ErrorCode>=0) { strcpy(m_waferlist,compidlist); strcpy(m_retgrade,m_gradelist); m_retgrade[strlen(m_retgrade)-1] = 0; return(SUCCESS); } else { return(m_ErrorCode); } }

intGETSPDN ::Reply(){ char LogMsg_Buff[MAXBUFFLEN/4];

char cmdbuf[MAXBUFFLEN]; // Elaine 2007-03-15 for system command char aMailBuf[MAXBUFFLEN]; // Elaine 2007-03-15 for system command cmdbuf[0] = aMailBuf[0] = 0x00;

if (m_DCHost != NULL) { delete m_DCHost; // free memory m_DCHost = NULL; }

if (m_ErrorCode != SUCCESS) { sprintf(LogMsg_Buff, "In Reply->m_ErrorCode=%d,m_ErrorMessage=%s",m_ErrorCode,m_ErrorMessage); LogPrintf(7, SEV_CHECK, LogMsg_Buff); sprintf(m_Buffer, "GETSPDNR HDR=%s RCD=%d LOT=%s ERR=/"%s/"", Header(), m_ErrorCode, (char *)*m_dataLOT,m_ErrorMessage);

/* Elaine 2007-03-15 to add Device mail if fail rate > 50 */ if(m_ErrorCode == 9050) { sprintf(aMailBuf,"Error:/n%s/n/n/nIf the above fail rate was acceptable, please give a comment in PNDN./nOtherwise please correct the PNDN test file table./n",m_ErrorMessage); sprintf(cmdbuf,"echo /"%s/n/" | mailx -s /"%s WAC fail rate not passed./" A636@promos.com.tw,A571@promos.com.tw",aMailBuf,(char *)*m_dataLOT); system(cmdbuf); } } else { printf("m_retgrade=%s^/n",m_retgrade); sprintf(m_Buffer, "GETSPDNR HDR=%s RCD=0 LOT=%s STATUS=OK GRADELIST=%s", Header(), (char *)*m_dataLOT, m_retgrade); }

sprintf(LogMsg_Buff, "[%s]Send to [%s]: [%s]", myContainer->Name(), (char *)ReplyBox(), m_Buffer); LogPrintf(5, SEV_SEND,LogMsg_Buff);

Send((char *)ReplyBox(), m_Buffer);

return(0);}

/* ---------------- Elaine 2006-11-06 Hold lot when WAC data fail rate > 50 ---------------------*/voidGETSPDN ::SpdnHold(char *theErrorMsg){ char aBoxName[MAILBOXLEN+1]; char aRcvMsg[ERRORMSGLEN + 1]; char aReplyMsg[EAPREPLYLEN]; char aErrorMsg[ERRORMSGLEN + 1]; char LogMsg_Buff[MAXBUFFLEN/4]; int rc = 0;

aBoxName[0] = aRcvMsg[0] = aReplyMsg[0] = aErrorMsg[0] = LogMsg_Buff[0] = 0x00; sprintf(aBoxName, "%s%d", myContainer->Name(),getpid()); sprintf(m_Buffer,"HOLD HDR=LOT_SRV,%s,%s OPERATOR=DREAMS LOT=%s PASSWD= CODE= REASON=/"WAT-SPDN CHECK WAC FAILURE RATE NO PASSED./" CMT=/"/"",aBoxName,aBoxName, (char *)*m_dataLOT); printf("SPDN Send to HOLD [%s]/n",m_Buffer); rc = myContainer->SendWait("LOT_SRV", aBoxName, m_Buffer, aRcvMsg, sizeof(aRcvMsg), 30); sprintf(LogMsg_Buff, "SPDN Received From HOLD[%s]", aRcvMsg); LogPrintf(1, SEV_RECEIVE, LogMsg_Buff);

if (rc == 0) { sprintf(aErrorMsg, "FAILED!! Hold Lot no reply."); } if(DreamsParse(aRcvMsg, "RCD", aReplyMsg) > 0) { if (atoi(aReplyMsg) == 0) { sprintf(aErrorMsg, "Hold Lot Because WAC Failure Rate no passed."); } if(DreamsParse(aRcvMsg, "ERR", aReplyMsg) > 0 ) { sprintf(aErrorMsg, "WAC Failure Rate no passed But Hold Lot Failed. %c%c%s", 0x0d,0x0a,aReplyMsg); } } strcpy(theErrorMsg, aErrorMsg);}

/*-------------------------------------------------spdn.pc--------------------------------------------*/

#include <stdio.h>#include <stdlib.h>#include <string.h>#include <time.h>#include <sys/stat.h> /* Added by Ellison for WriteLog*/

#include "spdndef.h"

/*-- for WriteLog --*/#define _MAX_LOG 10#define _MAX_LOG_SIZE 1024*1024

#define WAFER_QTY 25#define LOTIDLEN 12#define MAXCOMPONENTS 25#define DB_OP_SUCCESS 0#define DB_ERROR -1

#define DB_REC_NOTFOUND 1#define DB_NO_NEED_TO_QUERY -99#define DBIO_NULL_VALUE_ERR_CODE -1405#define REC_NOTFOUND_CODE 1403

/*----------------------------------------------------------------------+ | PROTOTYPE DEFINITION SECTION | +----------------------------------------------------------------------*/int get_SpdnGrade(char *, char *,char *,char *,char *);int CompLotMaskData(char* LotId); /* Ellison at 2004-08-10 */void handle_error(char *str1,char *str2);int CheckPndnData(char* LotId, char* ReturnResult); /* Ellison 2005-03-08 */int SpdnConstraint(char* LotId, char* ReturnResult); /* Elaine 2006-08-14 */int CheckWacData(char* LotId, char* ReturnResult); /* Elaine 2006-10-30 */int saveSPDNSPECOriGrade(char *,char *,char *,int); /* Michael 2007-05-11 */

void WriteLog(char *log_str); /* Ellison 2004-08-12 */void getsystime(char *timestr);

extern void util_strip_blank(char *);extern void geterrormsg(char *);extern int instr(char *, char *); /*Elaine 2006-11-03 */

EXEC SQL INCLUDE sqlca.h;

int get_SpdnGrade(char *Lotid,char *OP,char *compidlist, char *m_gradelist,char *result){ int i; int op; int ret; char tmpstr[20]; char gradestr[20]; char reasonstr[20]; char evalstr[20]; char wid[20];/* FILE *fp; char rc[60];*/ typedef char vc2_arr[13]; typedef char vc2_arr1[30]; EXEC SQL BEGIN DECLARE SECTION; EXEC SQL TYPE vc2_arr IS VARCHAR2(13) REFERENCE; EXEC SQL TYPE vc2_arr1 IS VARCHAR2(30) REFERENCE; char lot[LOTIDLEN+1]; vc2_arr waferlist[WAFER_QTY]; vc2_arr1 gradelist[WAFER_QTY]; char rc[60];

EXEC SQL END DECLARE SECTION;

rc[0]=0x00; tmpstr[0]=0x00; wid[0]=0x00; gradestr[0] = reasonstr[0] = evalstr[0] = lot[0] = 0x00; /* 200506, var init */ i = 0; op = 0; ret = 0; /* if(!(fp = fopen("/mnt/projap/promos/dreams_log/getspdn.log", "a"))) { printf("open getspdn.log fail!!"); }

if(!(fp = fopen("/mnt/prodrmap/ProMOS/dreams_log/getspdn.log", "a"))) { printf("open getspdn.log fail!!"); exit(1); }*/ strcpy(lot,Lotid); op=atoi(OP);

printf("lotid=%s/n",Lotid); printf("comp=%s/n",compidlist); /* printf("wafer_qty=%d/n",WAFER_QTY);*/ for(i=0;i<WAFER_QTY;i++) { gradelist[i][0]=0x00; waferlist[i][0]=0x00; strcpy(waferlist[i]," "); strcpy(waferlist[i],"NONE"); strcpy(gradelist[i]," "); }

for(i=0;i<WAFER_QTY;i++) { tmpstr[0]=0x00; ret=GetNthItem(compidlist, i+1, ",", tmpstr); if (strlen(tmpstr)!=0) { ret=GetNthItem(tmpstr,2,".",wid); strcpy(waferlist[atoi(wid)-1],tmpstr); } }

for(i=0;i<WAFER_QTY;i++) printf("0307 waferlist[%d]=%s^/n",i,waferlist[i]);/* fprintf(fp,"START get_SpdnGrade lot=%s,compidlist=%s,m_gradelist=%s,result=%s^/n",Lotid,compidlist,m_gradelist,result);*//* rc=0;*/ rc[0]=0x00;/* fprintf(fp,"before execute P.SPDN->lotid=%s,rc=%s^/n",lot,rc);*/ printf("-->start to executr P.SPDN (Lot=%s, Mode=%d /n", lot,op);

EXEC SQL WHENEVER SQLERROR DO handle_error(lot,rc); EXEC SQL EXECUTE BEGIN P.SPDN (:lot,/* BEGIN Q.SPDN (:lot,*/ :op, :waferlist, :gradelist, :rc); END; END-EXEC;/* fprintf(fp,"after execute P.SPDN->lotid=%s,rc=%s^,gradelist=%s^/n",lot,rc,gradelist); EXEC SQL INSERT INTO TMP12(LOTID,R_STR,EVTIME) VALUES ( :lot, :rc, SYSDATE);*/

if (sqlca.sqlcode) { EXEC SQL ROLLBACK WORK;/* return(sqlca.sqlcode);*/ } EXEC SQL COMMIT WORK;/* fprintf(fp,"after INSERT TMP12->lotid=%s,rc=%s^,sqlca.sqlcode=%d^/n",lot,rc,sqlca.sqlcode);

fclose(fp);*/ printf("RC=%s^/n",rc); util_strip_blank(rc); printf("trim(RC)=%s^/n",rc); printf("sqlca.sqlcode=%d/n",sqlca.sqlcode);

strcpy(result,rc);

if (strlen(rc) !=0 ) { if (strlen(rc)>=7) { if (strncmp(rc,"SUCCESS",7)==0) { for(i=0;i<WAFER_QTY;i++) { tmpstr[0]=0x00; ret=GetNthItem(gradelist[i], 1, " ", gradelist[i]); ret=GetNthItem(gradelist[i], 1, ":", gradestr); ret=GetNthItem(gradelist[i], 2, ":", reasonstr); ret=GetNthItem(gradelist[i], 3, ":", evalstr); if (strcmp(gradestr,"NOWAFER")!=0) sprintf(m_gradelist,"%s%d:%s:%s:%s,",m_gradelist,i+1,gradestr,reasonstr,evalstr); printf("gradelist[%d]=%s^/n",i,gradelist[i]); } printf("sqlca.sqlcode=%d/n",sqlca.sqlcode); return(0); } else { printf("sqlca.sqlcode=%d/n",sqlca.sqlcode); return(-2); } } else { printf("sqlca.sqlcode=%d/n",sqlca.sqlcode); return(-3); } } else { printf("sqlca.sqlcode=%d,rc=%s/n",sqlca.sqlcode,rc); return(-4); }}

void handle_error(char *str1,char *str2){ printf("SQLERROR CODE=%d,lot=%s,rc=%s^",sqlca.sqlcode,str1,str2);}

/* Added by Ellison at 2004-08-10 for Lot Mask Data Comparison */int CompLotMaskData(char* LotId){ LotMaskData *start; LotMaskData *ptr;

EXEC SQL BEGIN DECLARE SECTION;

char Prod[4]; char aLotId[LOTIDLEN+1]; char mainLotId[LOTIDLEN+1]; char tmpLotId[LOTIDLEN+1]; char sLotId[4]; char SqlStr[1024]; char terrmsg[1024]; char Process[6]; char ordernum[24]; char LogBuff[1024];

char lot[LOTIDLEN+1]; char rc[60];

EXEC SQL END DECLARE SECTION; int recordCount = 0; int sId = 0; /* sLotId */ char aLayer[5]; char aRunMask[32]; char aAssignMask[5];

rc[0] = lot[0] = 0x00; strcpy(lot,LotId); Prod[0] = SqlStr[0] = aLotId[0] = terrmsg[0] = mainLotId[0] = sLotId[0] = tmpLotId[0] = 0x00; start = ptr = NULL; Process[0] = LogBuff[0] = ordernum[0] = 0x00;

/* 200506, var init */ aLayer[0] = aRunMask[0] = aAssignMask[0] = 0x00; sprintf(LogBuff,"SPDN.PC:fn[CompLotMaskData] Start to Compare Lot-Run-Mask Data for [%s]",LotId); WriteLog(LogBuff);

/* -- Check if this LotId has been checked -- */ EXEC SQL SELECT COUNT(*) INTO :recordCount FROM FOUNDRY_CUSTOMER_MISMATCH WHERE RELEASE='Y' AND LOTID=:LotId; if (recordCount > 0 ) return(0); /* if RELEASE='Y' bypass Mask Check */

EXEC SQL SELECT COUNT(*) INTO :recordCount FROM FOUNDRY_CUSTOMER_MISMATCH WHERE RELEASE='N' AND LOTID=:LotId; if (recordCount > 0 ) return(-100); /* if RELEASE='N' , Lot has been chekced. */

EXEC SQL SELECT ORDERNUM INTO :ordernum FROM PROM_ACTL WHERE LOTID=:LotId; util_strip_blank(ordernum); printf("Get OrderNumber=[%s]/n", ordernum); if( strlen(ordernum) < 5 || ordernum[4] != 'P') return(0);

recordCount = 0;

strncpy(Prod, LotId, 3); Prod[3] = 0x00; sprintf(LogBuff,"SPDN.PC:fn[CompLotMaskData] Get ProdType:[%s]", Prod); WriteLog(LogBuff); strcpy(aLotId, LotId); aLotId[9] = 0x00; strcpy(sLotId, strstr(LotId,".")+1); sId = atoi(sLotId); printf("SPDN.PC:fn[CompLotMaskData] Get sLotId=[%d]/n", sId);

/* Ellison 2004-11-22 : check FOUNDRY_BAUNO_ASSIGN setting by prod */ recordCount = 0; EXEC SQL SELECT COUNT(*) INTO :recordCount FROM FOUNDRY_BAUNO_ASSIGN WHERE LOTCODE=:Prod; if(recordCount == 0) return (0); /* setting in FOUNDRY_ABUNO_ASSIGN not found */ if(sId > 1 ) { /* Get Mother LotId */ EXEC SQL SELECT ORIGINALLOTID INTO :mainLotId FROM LOT@L_DREAMS WHERE LOTID=:LotId; if ((sqlca.sqlcode) && (sqlca.sqlcode != DBIO_NULL_VALUE_ERR_CODE )) { geterrormsg(terrmsg); sprintf(LogBuff,"/nDREAMS <[E]< SPDN.PC(CompLotMaskData) GET MONTHER LOT :: [%s]/n", terrmsg); WriteLog(LogBuff); return(sqlca.sqlcode); } util_strip_blank(mainLotId); } else { strcpy(mainLotId, LotId); } sprintf(LogBuff,"SPDN.PC:fn[CompLotMaskData] Get Mother Lot = [%s]", mainLotId); WriteLog(LogBuff); EXEC SQL COMMIT WORK;

/* Get BAUNO --> Process */ EXEC SQL SELECT PROCESS INTO :Process FROM LOT_BAUNUM WHERE LOTID=:LotId; if ((sqlca.sqlcode) && (sqlca.sqlcode != DBIO_NULL_VALUE_ERR_CODE )) { geterrormsg(terrmsg); sprintf(LogBuff,"/nDREAMS <[E]< SPDN.PC(CompLotMaskData) Get BAUNO-PROCESS :: [%s]/n", terrmsg); WriteLog(LogBuff); return(sqlca.sqlcode); } util_strip_blank(Process); sprintf(LogBuff,"SPDN.PC(CompLotMaskData) Get BAUNO-PROCESS :: [%s] OK.",Process); WriteLog(LogBuff);

/* Get Layer-Mask Setting Data */ sprintf(SqlStr,"SELECT LAYER,VALUE FROM FOUNDRY_BAUNO_ASSIGN WHERE LOTCODE='%s' AND CHKTYPE='MASK' AND OPTIONCODE='%s' ORDER BY LAYER", Prod,Process); sprintf(LogBuff,"SPDN.PC(CompLotMaskData) Layer-Mask Assign SQLstr=[%s]",SqlStr); WriteLog(LogBuff);

EXEC SQL PREPARE S FROM :SqlStr; EXEC SQL DECLARE mask_cursor_1 CURSOR FOR S; EXEC SQL OPEN mask_cursor_1; if (sqlca.sqlcode) { geterrormsg(terrmsg); printf("/nDREAMS <[E]< SPDN.PC(CompLotMaskData) :: [%s]/n", terrmsg); recordCount = -1; return(recordCount); } recordCount = 0; while(1) { aLayer[0] = aAssignMask[0] = 0x00; /* 200507, var init */

EXEC SQL FETCH mask_cursor_1 INTO :aLayer, :aAssignMask; if ((sqlca.sqlcode) && (sqlca.sqlcode != DBIO_NULL_VALUE_ERR_CODE)) break; util_strip_blank(aLayer); util_strip_blank(aAssignMask); sprintf(LogBuff,"SPDN.PC(CompLotMaskData) Mask Assign Data: Layer=%s AssignMask=%s",aLayer,aAssignMask); WriteLog(LogBuff);

if(ptr == NULL) { ptr = (void *)(malloc (sizeof (LotMaskData))); start = ptr; } else { ptr->next = (void *)(malloc (sizeof (LotMaskData))); ptr = ptr->next; } if (ptr == NULL) { sprintf(LogBuff,"/nDREAMS <[E]> SPDN.PC(CompLotMaskData) mask_cursor_1 :: [Memory allocation error]/n"); WriteLog(LogBuff); /* before return, should check link list & release memory */ if(start != NULL) { while(start) { ptr = start; start = start->next; free(ptr); } } return (-811); } else /* memory allocate OK */ { ptr->Layer[0] = 0x00; ptr->RunMask[0] = 0x00; ptr->AssignMask[0] = 0x00; ptr->LotId[0] = 0x00; ptr->next = NULL;

strcpy(ptr->Layer, aLayer); strcpy(ptr->AssignMask, aAssignMask);

recordCount++; } } /* end of while */ EXEC SQL CLOSE mask_cursor_1; if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(LogBuff,"/nDREAMS <[E]< SPDN.PC(CompLotMaskData) CLOSE mask_cursor_1 Error :: [%s]", terrmsg); WriteLog(LogBuff); /* before return, should check link list & release memory */ if(start != NULL) { while(start) { ptr = start; start = start->next; free(ptr); } } return(sqlca.sqlcode); }

/* Get Lot Run-Mask Data */ WriteLog("Start to Get Lot Run-Mask Data..."); ptr = start; while(ptr) { SqlStr[0] = 0x00; sprintf(SqlStr,"SELECT LOTID,MASK FROM LOT_MASK_HIST WHERE LAYER='%s' AND LOTID LIKE '%s%%' ORDER BY LOTID DESC",ptr->Layer,aLotId); sprintf(LogBuff,"SPDN.PC(CompLotMaskData) Run-Mask SQLstr=[%s]",SqlStr); WriteLog(LogBuff);

EXEC SQL PREPARE S FROM :SqlStr; EXEC SQL DECLARE mask_cursor CURSOR FOR S; EXEC SQL OPEN mask_cursor;

if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(LogBuff,"/nDREAMS <[E]< SPDN.PC(CompLotMaskData) Lot Run-Mask Data :: [%s]/n", terrmsg); WriteLog(LogBuff); recordCount = -3; while(start) /* free memory */ { ptr = start; start = start->next; free(ptr); } return(recordCount); } while(1) { EXEC SQL FETCH mask_cursor INTO :tmpLotId,:aRunMask; if ((sqlca.sqlcode) && (sqlca.sqlcode != DBIO_NULL_VALUE_ERR_CODE)) { strcpy(ptr->LotId, LotId); break; }

util_strip_blank(aRunMask); util_strip_blank(tmpLotId); printf("SPDN.PC(CompLotMaskData) Get Run-Mask Data: RunMask=%s LotId=%s/n",aRunMask,tmpLotId); if(strlen(ptr->LotId)==0){ strcpy(ptr->LotId,tmpLotId); strcpy(ptr->RunMask,aRunMask); } else { if(strcmp(tmpLotId, ptr->LotId) != 0) { if(strcmp(ptr->LotId, LotId) != 0) { if((strcmp(tmpLotId, LotId) != 0) && (strcmp(tmpLotId, mainLotId) != 0)) { strcpy(ptr->LotId, tmpLotId); } else { strcpy(ptr->LotId, tmpLotId); strcpy(ptr->RunMask, aRunMask); } } } else { if(strlen(ptr->RunMask) == 0) { strcpy(ptr->RunMask, aRunMask); } } } } /* end of while(1) */

EXEC SQL CLOSE mask_cursor; if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(LogBuff,"/nDREAMS <[E]< SPDN.PC(CompLotMaskData) CLOSE mask_cursor :: [%s]/n", terrmsg); WriteLog(LogBuff); /* before return, should check link list & release memory */ if(start != NULL) { while(start) { ptr = start; start = start->next; free(ptr); } } return(sqlca.sqlcode); } ptr = ptr->next; } /* end of while(ptr) */

/* Start to compare Mask Data */ sprintf(LogBuff,"SPDN.PC(CompLotMaskData) :: Total Records = [%d] need to be compared.", recordCount); WriteLog(LogBuff);

recordCount = 0; ptr = start; while(ptr) { sprintf(LogBuff,"SPDN.PC(CompLotMaskData) Data :: LotId=[%s] Layer=[%s] AssignMask=[%s] RunMask=[%s]", ptr->LotId, ptr->Layer, ptr->AssignMask, ptr->RunMask); WriteLog(LogBuff); /* only compare 3rd code form 'ptr->RunMask's left side. */ if(ptr->AssignMask[0] != ptr->RunMask[strlen(ptr->RunMask)-3]) recordCount++; ptr = ptr->next; }

if(start != NULL) { while(start) { ptr = start; start = start->next; free(ptr); } } sprintf(LogBuff,"End of Compare Data for [%s], Toatl %d records error./n",LotId, recordCount); WriteLog(LogBuff);

if(recordCount>0) { EXEC SQL INSERT INTO FOUNDRY_CUSTOMER_MISMATCH(LOTID,INDATE,RELEASE) VALUES(:LotId, SYSDATE,'N'); if (sqlca.sqlcode) { EXEC SQL ROLLBACK WORK; return(sqlca.sqlcode); } EXEC SQL COMMIT WORK; } return(recordCount); } /*end of function */

void WriteLog(char *log_str){ FILE *fp; char sys_time[24]; char LogFile[50]; struct stat aStat; char inStr[1024], *result, *start_point; int i; inStr[0] = 0x00; i = 1; /* 200506, var init */ sys_time[0] = LogFile[0] = 0x00; fp = NULL; result = start_point = NULL; getsystime(sys_time); /* Get LogFile */ for(i = 1;i <= _MAX_LOG;i++) { sprintf(LogFile,"../dreams_log/SPDN%03d.Log",i); if (access(LogFile, 0) != 0) /* file not exists */ break;

if (lstat(LogFile, &aStat) == 0) /* looking for file information */ { if (aStat.st_size <= _MAX_LOG_SIZE) /* not execeeds maximum file size */ break;

if (i == _MAX_LOG) { /* if all log files exist and filesize == _MAX_LOG_SIZE */ system("ls -ltr ../dreams_log/SPDN*.Log > SPDN_loglist"); fp = fopen("SPDN_loglist","r"); result = fgets(inStr,512,fp); fclose(fp); remove("SPDN_loglist"); start_point = strstr(result,"SPDN"); strncpy(LogFile, start_point,11); LogFile[11] = 0x00; sprintf(LogFile,"../dreams_log/%s", LogFile); printf("Get LotFile:[%s]/n",LogFile); remove(LogFile); break; } } } fp = fopen(LogFile,"a"); fputs("[",fp); fputs(sys_time,fp); fputs("]",fp); fputs(log_str,fp); fputs("/n",fp); fclose(fp); printf("[%s]:[SPDNsrv]:%s/n",sys_time,log_str); return;}

void getsystime(char *timestr){ struct tm *gm; time_t myclock;

myclock = time(0); gm = localtime((const time_t *)&myclock); sprintf(timestr, "%04d/%02d/%02d %02d:%02d:%02d" , gm->tm_year+1900, gm->tm_mon+1, gm->tm_mday, gm->tm_hour, gm->tm_min, gm->tm_sec);}

/* Ellison 2005-03-08 : to check PNDN -Wafer Grade Data when SPDN perfromed */int CheckPndnData(char* LotId, char* ReturnResult){ char tmpResult[1024]; char sqlstr[1024]; char tmpPNDN[20]; int count; char lot[LOTIDLEN+1], mlot[LOTIDLEN+1]; char rc[60]; char terrmsg[1024];

count = 0; tmpResult[0] = sqlstr[0] = tmpPNDN[0] = rc[0] = lot[0] = terrmsg[0] = 0x00; /* 200506, var init */ mlot[0] = 0x00;

strcpy(lot, LotId); lot[9] = 0x00; /* lot id as P6B23123.1 */

/* sprintf(sqlstr,"SELECT A.PNDN_NO FROM PNDN_IMPACT_LOT A WHERE A.LOTID='%s' " " AND A.PNDN_NO NOT LIKE 'R%%' " " AND A.PNDN_NO NOT IN ( SELECT B.PNDN_NO" " FROM WAFER_GRADE B WHERE B.LOTID='%s' )", lot,lot );*//* to take child-lot into consider, modified the SQL at 2005-03-09 */ sprintf(sqlstr,"SELECT A.PNDN_NO FROM PNDN_IMPACT_LOT A WHERE A.LOTID LIKE '%s%%' " " AND A.PNDN_NO NOT LIKE 'R%%' " " AND A.PNDN_NO NOT IN ( SELECT B.PNDN_NO" " FROM WAFER_GRADE B WHERE B.LOTID LIKE '%s%%' )", lot,lot );

printf("Check PNDN-WaferGrade Data SQL:[%s]/n", sqlstr);

EXEC SQL PREPARE S FROM :sqlstr ;

EXEC SQL DECLARE pndn_wafergrade_cursor CURSOR FOR S;

EXEC SQL OPEN pndn_wafergrade_cursor ;

if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(tmpResult, "DREAMS <[Error OPEN eqptype_cursor ]< ORACLE :: [%s]", terrmsg); printf("/n%s/n", tmpResult); strcpy(ReturnResult, tmpResult); EXEC SQL COMMIT WORK; return(-1); }

while(1) { tmpPNDN[0]=0x00; /* 200507, var init */ EXEC SQL FETCH pndn_wafergrade_cursor INTO :tmpPNDN;

if ((sqlca.sqlcode) && (sqlca.sqlcode != DBIO_NULL_VALUE_ERR_CODE)) break;

util_strip_blank(tmpPNDN);

count++; if(strlen(tmpResult) == 0) strcpy(tmpResult, tmpPNDN); else sprintf(tmpResult+strlen(tmpResult), ",%s", tmpPNDN); } /* end of while */

EXEC SQL CLOSE pndn_wafergrade_cursor; EXEC SQL COMMIT WORK;

printf("SQL-RESULT: COUNT=%d DATA=%s/n", count, tmpResult);

strcpy(ReturnResult, tmpResult); return(count);}

/* Elaine 2006-08-14 : to check SPDN general constraint *//* Elaine 2006-08-14 : add PMRF status check by OP QA *//* Elaine 2006-08-14 : add AB Lot PNDN status check */int SpdnConstraint(char* LotId, char* ReturnResult){ char tmpResult[1024]; int cnt1 = 0; int cnt2 = 0; char lot[LOTIDLEN+1]; char terrmsg[1024]; char rc[60];

tmpResult[0] = lot[0] = terrmsg[0] = rc[0] = 0x00;

strcpy(lot, LotId); /* lot[9] = 0x00; */ /* lot id as P6B23123.1 */

printf("SpdnConstraint: check PMRF status LOT=[%s]/n",lot); EXEC SQL SELECT COUNT(*) into :cnt1 FROM PMRF_STATUS WHERE LOTID = :lot AND STATUS NOT IN ('Status1','Status9','StatusR');

if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(tmpResult, "DREAMS <[Error PMRF status check ]< ORACLE :: [%s]", terrmsg); printf("/n%s/n", tmpResult); strcpy(ReturnResult, tmpResult); return(sqlca.sqlcode); } if(cnt1 > 0) /* PMRF not completed. */ { sprintf(ReturnResult, "Lot[%s] PMRF not completed. Plz. call OP QA #3647", lot); return(1); }

printf("SpdnConstraint: check AB Lot PNDN ,LOT=[%s]/n",lot); EXEC SQL SELECT COUNT(*) into :cnt2 FROM CHG2AB_STATUS WHERE LOTID = :lot AND STATUS NOT IN ('Status1','Status9','StatusR');

if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(tmpResult, "DREAMS <[Error AB Lot PNDN check ]< ORACLE :: [%s]", terrmsg); printf("/n%s/n", tmpResult); strcpy(ReturnResult, tmpResult); return(sqlca.sqlcode); } if(cnt2 > 0) /* AB Lot PNDN not completed. */ { sprintf(ReturnResult, "AB Lot[%s] PNDN not completed.", lot); return(1); }

EXEC SQL COMMIT WORK; sprintf(ReturnResult,"SpdnConstraint passed. LOT[%s]/n", lot); return(0);}/* Elaine 2006-10-30 : to check WAC Data if non-PROD */int CheckWacData(char *LotId, char *ReturnResult){ char sqlstr[1024]; char LogBuff[1024]; int i = 0; int cnt1 = 0; int cnt2 = 0; int pos = 0; int aRc, aRc2 = 0; int aCode = 0; /* Oracle Procedure PROC_WAC_CHECK return Code */ char aMessage[200+1]; /* Oracle Procedure PROC_WAC_CHECK return Message */ char lot[LOTIDLEN+1]; char aLotNoDot[LOTIDLEN+1]; char aCompIdList[MAXCOMPONENTS*(2+1)+1]; char aWaferId[30+1];

char aCat2[20+1]; char aTestFile[30+1]; char aWaferList[MAXCOMPONENTS*(30+1)+1]; char aSkipPos[3+1]; char aCmt[150+1];

/* oracle procedure input parameters */ char aTestFileParm[(30+1)*MAXCOMPONENTS+1]; char aWaferParm[MAXCOMPONENTS * (2+1)+1];

char terrmsg[1024]; char rc[60];

LogBuff[0] = terrmsg[0] = rc[0] = sqlstr[0] = lot[0] = aMessage[0] = 0x00;

aCompIdList[0] = aCat2[0] = aWaferId[0] = aLotNoDot[0] = 0x00; aTestFile[0] = aWaferList[0] = aSkipPos[0] = aCmt[0] = 0x00; aTestFileParm[0] = aWaferParm[0] = 0x00;

strcpy(lot, LotId);

EXEC SQL SELECT waferids, cat2 into :aCompIdList, :aCat2 FROM PROM_ACTL A, PROD B WHERE a.PARTID = b.PARTID AND a.LOTID = :lot; /* SELECT waferids, cat2 into :aCompIdList, :aCat2 FROM PROM_ACTL A, PROD B WHERE a.PARTID = b.PARTID AND a.LOTID = pc001.44; ===>waferids=11;cat2=FOUNDRY */ if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(LogBuff, "GET PROM_ACTL :: %s", terrmsg); WriteLog(LogBuff); strcpy(ReturnResult, terrmsg); return(sqlca.sqlcode); }

util_strip_blank(aCat2); util_strip_blank(aCompIdList); printf("CheckWacData: check WAC Data LOT=[%s] Cat2[%s] CompIdList[%s]/n", lot, aCat2, aCompIdList); if(strcmp(aCat2,"PROD") == 0) { sprintf(ReturnResult, "Passed cause Lot[%s] is PROD.", lot); return(0); }

if((pos = instr(lot,".")) == 0) strcpy(aLotNoDot, lot); else { strncpy(aLotNoDot, lot, pos-1); aLotNoDot[pos-1] = 0x00; }

i = 1; while (GetNthItem(aCompIdList, i++, ";", aWaferId) == 0) { if (strlen(aWaferId)==0) continue; aTestFile[0] = aWaferList[0] = aSkipPos[0] = aCmt[0] = 0x00; aRc = 0;

/* get WAC testfile from PNDN by each waferid */ EXEC SQL SELECT FILENAME, WAFERID, instr((','||WAIVELST||','||SCRAPLST||','),(','||:aWaferId||',')), NVL(CMT,' ') INTO :aTestFile, :aWaferList, :aSkipPos, :aCmt FROM (SELECT * FROM PNDN_WATFILES WHERE substr(LOTID,1,instr(LOTID,'.')-1) = :aLotNoDot AND instr((','||WAFERID||','||WAIVELST||','||SCRAPLST||','),(','||:aWaferId||',')) > 0 ORDER BY PNDNCREATETIME DESC) WHERE ROWNUM=1;

aRc = sqlca.sqlcode; if((aRc) && (aRc != REC_NOTFOUND_CODE) && (aRc != DBIO_NULL_VALUE_ERR_CODE)) { geterrormsg(terrmsg); sprintf(LogBuff, "GET PNDN WATFILE ERROR :: [%s]", terrmsg); WriteLog(LogBuff); printf("%s/n",LogBuff); if(aRc==-2112) { sprintf(terrmsg,"Wafer[%s.%s] has more than one WAT TEST FILE in PNDN.", aLotNoDot, aWaferId); } strcpy(ReturnResult, terrmsg); EXEC SQL COMMIT WORK; return (aRc); } util_strip_blank(aTestFile); util_strip_blank(aWaferList); util_strip_blank(aSkipPos); util_strip_blank(aCmt);

printf("TestFile=%s, WaferId=%s, comment=%s/n",aTestFile,aWaferId,aCmt); /* Skip or Pass to next WaferId */ if(atoi(aSkipPos) > 0) continue; /* Skip */ /*--- Elaine markded at 2007-03-26, judge and pass in PROC_WAC_CHECK if(strlen(aCmt) > 0) continue; -----------------*/

/* NO_REC_FOUND or NULL, TestFile using Lot.1 */ if(strlen(aTestFile) == 0) { strcpy(aTestFile, aLotNoDot); strcat(aTestFile,".1"); }

/* making oracle procedure input parameters */ if(strlen(aTestFileParm)>0) strcat(aTestFileParm, ";"); sprintf(aTestFileParm+strlen(aTestFileParm), "%s", aTestFile); if(strlen(aWaferParm)>0) strcat(aWaferParm, ";"); sprintf(aWaferParm+strlen(aWaferParm), "%s", aWaferId);

} /* while waferid*/ printf("/nBegin to close PNDN_RWK DB_LINK../n"); EXEC SQL EXECUTE BEGIN COMMIT; DBMS_SESSION.CLOSE_DATABASE_LINK('PNDN_RWK.PROMOS.COM.TW'); END; END-EXEC; printf("/nEnd to close PNDN_RWK DB_LINK../n");

printf("/nWaferListParm[%s],TestFileParm[%s]/n",aWaferParm,aTestFileParm);

/* Elaine 2006-01-24 , if no wafer to check, bypass it */ if(strlen(aWaferParm)==0) { EXEC SQL COMMIT WORK; sprintf(ReturnResult,"Pass. No wafer to be checked./n"); return(0); } EXEC SQL EXECUTE BEGIN PROC_WAC_CHECK(:aWaferParm, :aTestFileParm, '', :aCode, :aMessage); COMMIT; END; END-EXEC;

if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(ReturnResult,"CALL PROC_WAC_CHECK failed.%c%cLOT[%s] RC[%d] MESSAGE[%s]/n", 0x0d,0x0a, lot, sqlca.sqlcode, terrmsg); EXEC SQL ROLLBACK WORK; return (-1); }

printf("/nBegin to close EIM DB_LINK../n"); EXEC SQL EXECUTE BEGIN COMMIT; DBMS_SESSION.CLOSE_DATABASE_LINK('EIM.PROMOS.COM.TW'); END; END-EXEC; printf("/nEnd to close EIM DB_LINK..SQLCODE=[%d]/n",sqlca.sqlcode);

if(aCode != 0) { sprintf(ReturnResult,"CheckWacData failed.%c%c%s",0x0d,0x0a,aMessage); EXEC SQL ROLLBACK WORK; return (aCode); } EXEC SQL COMMIT WORK; sprintf(ReturnResult,"CheckWacData passed. LOT[%s]/n", lot); return(0);}

/* Michael added on 2007-05-11 */int saveSPDNSPECOriGrade(char *LotId, char *WaferId, char *Grade, int Qty){ char terrmsg[1024]; char rc[60]; char LogBuff[1024]; char lot[LOTIDLEN+1]; LogBuff[0] = terrmsg[0] = rc[0] = lot[0] = 0x00; EXEC SQL INSERT INTO SPDN_SPEC_LOT_ORI ( LOTID, WAFER_MEMBER, GRADE, QTY) VALUES (:LotId, :WaferId, :Grade, :Qty); if (sqlca.sqlcode) { geterrormsg(terrmsg); sprintf(LogBuff, "INSERT INTO SPDN_SPEC_LOT_ORI FAIL :: %s", terrmsg); WriteLog(LogBuff); EXEC SQL ROLLBACK WORK; return(sqlca.sqlcode); }

EXEC SQL COMMIT WORK;

return(0);}

/*---------------------------------------------proc_wac_check----------------------------------------*/

CREATE OR REPLACE PROCEDURE Proc_Wac_Check(a_WAFERLIST IN VARCHAR2, a_lot IN VARCHAR2, a_SKIPWAFERLIST IN VARCHAR2, a_code OUT NUMBER, a_message OUT VARCHAR2)IS TYPE WAFERLIST_TABLE IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; TYPE TESTFILE_TABLE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; I INTEGER := 1; J INTEGER := 1; pos INTEGER; posA INTEGER := 1; waferid VARCHAR2(100); tmp2waferlist VARCHAR2(200); tmpwaferlist VARCHAR2(200); tmplot VARCHAR2(1000); tmpskipwaferlist VARCHAR2(200); ret_fail_rate INTEGER; WAFERLIST WAFERLIST_TABLE; TESTFILE TESTFILE_TABLE; v_testfile VARCHAR2(30); tmptestfile VARCHAR2(30); vDate DATE; PAR_CNT INTEGER; ins_sql VARCHAR2(1000); del_sql VARCHAR2(1000); min_test_date DATE; briefwaferid VARCHAR2(2); cmtcnt INTEGER := 0;BEGIN

a_code := 0; --Default value a_message := 'SUCCESS'; tmpskipwaferlist := ',' || a_SKIPWAFERLIST || ','; /* input parameter format : There are 2 kind of delimiter --> 1.comma 2.semicolon a_waferlist : 1,2,3,4,5;6,7,8 a_lot (testfile) : P4D000123,1;P4D000123.1X a_SKIPWAFERLIST : 21,22,23 */ -- Parsing WAFERLIST input parameter pos := 1; I := 1; tmpwaferlist := a_WAFERLIST; WHILE (pos > 0) LOOP pos := INSTR(tmpwaferList,';'); IF ( pos > 0 ) THEN WAFERLIST(I) := TRIM(SUBSTR(tmpwaferlist,1,pos-1)); tmpwaferlist := SUBSTR(tmpwaferlist,pos+1); ELSE WAFERLIST(I) := TRIM(tmpwaferlist); END IF; I := I + 1; END LOOP;

-- Parsing lot (test files) input parameter pos := 1; tmplot := a_lot; WHILE (pos > 0) LOOP pos := INSTR(tmplot,';'); IF ( pos > 0 ) THEN TESTFILE(J) := TRIM(SUBSTR(tmplot,1,pos-1)); tmplot := SUBSTR(tmplot,pos+1); ELSE TESTFILE(J) := TRIM(tmplot); END IF; J := J + 1; END LOOP; IF (I <> J) THEN a_code := 99; a_message := 'PARAMETER : WAFERLIST vs LOT count are inconsistent.'; END IF; IF (LENGTH(TESTFILE(1)) < 8) THEN a_code := 98; a_message := 'PARAMETER : INVALID TESTFILE.'; END IF;

-- Elaine 2007-07-24 ITM200707-586 P6S -- Elaine 2007-07-31 ITM200707-720 S2 --IF (a_code = 0 AND SUBSTR(TESTFILE(1),1,3)<>'P6S' AND SUBSTR(TESTFILE(1),1,2)<>'S2') THEN -- add virtual lot PC0 IF (a_code = 0 AND SUBSTR(TESTFILE(1),1,3)<>'P6S' AND SUBSTR(TESTFILE(1),1,2)<>'S2' AND SUBSTR(TESTFILE(1),1,3)<>'PC0') THEN --IF (a_code = 0) THEN -- Elaine 2007-01-15 to insert into a temp table INSERT INTO WAC_FAILRATE_TMP SELECT LOT,LIMIT_FILE,TEST_DATE,WAFER,KEY_FLAG,PAR,FAIL_RATE FROM WAT_FAIL_RATE@myeda WHERE LIMIT_FILE LIKE SUBSTR(TESTFILE(1),1,3)||'_TV%' AND lot LIKE SUBSTR(TESTFILE(1),1,8)||'%' AND KEY_FLAG='02'; --FROM wac_failrate_bak WHERE LIMIT_FILE LIKE SUBSTR(TESTFILE(1),1,3)||'%' AND lot LIKE SUBSTR(TESTFILE(1),1,8)||'%' AND KEY_FLAG='02'; I := 0; WHILE (I < TESTFILE.COUNT AND a_code=0) LOOP I := I + 1; IF( TESTFILE.EXISTS(I) AND TESTFILE.EXISTS(I)) THEN NULL; ELSE a_code := 9999; END IF; --DBMS_OUTPUT.PUT_LINE('WAFERLIST('||I||')'||' = '||WAFERLIST(I)); posA := 1; tmp2waferlist := WAFERLIST(I); v_testfile := TESTFILE(I); WHILE (posA > 0 AND a_code = 0) LOOP posA := INSTR(tmp2waferlist,','); IF (posA > 0) THEN waferid := SUBSTR(tmp2waferlist,1, posA-1); tmp2waferlist := SUBSTR(tmp2waferlist,posA+1); ELSE waferid := tmp2waferlist; END IF;

IF (INSTR(tmpskipwaferlist,','||waferid||',') > 0) THEN -- the wafer should be skipped. --DBMS_OUTPUT.PUT_LINE('SKIPPED WAFERID : ' ||waferid); NULL; ELSE IF (LENGTH(waferid)=1) THEN waferid := '0'||waferid; END IF; --DBMS_OUTPUT.PUT_LINE('WAFERID'||' = '||waferid||', TESTFILE = '|| v_testfile); BEGIN -- if no PNDN, tset filename could be '.1' or its sub-lotid, choose the earliest one. IF (v_testfile LIKE '%.1') THEN SELECT MIN(lot) INTO tmptestfile FROM WAC_FAILRATE_TMP WHERE lot LIKE SUBSTR(v_testfile,1,8)||'%' AND wafer = waferid AND LIMIT_FILE LIKE '%_TV%' AND KEY_FLAG='02'; IF (tmptestfile IS NOT NULL) THEN v_testfile := tmptestfile; END IF; END IF; -- If there are multi EDA testfile, they might belong to TV.LIM or TV20.LIM, choose the earliest one. SELECT MIN(test_date) INTO min_test_date FROM WAC_FAILRATE_TMP WHERE lot = v_testfile AND wafer = waferid AND LIMIT_FILE LIKE '%_TV%' AND KEY_FLAG='02'; DELETE FROM WAC_EDA WHERE TESTFILE LIKE SUBSTR(v_testfile,1,8)||'%' AND wafer = waferid; INSERT INTO WAC_EDA (TESTFILE,LIMIT_FILE,TEST_DATE,KEY_FLAG,WAFER,PAR,FAIL_RATE, MESLOTID) SELECT LOT, LIMIT_FILE, TEST_DATE, KEY_FLAG,WAFER, PAR, FAIL_RATE, SUBSTR(LOT,1,8) FROM WAC_FAILRATE_TMP WHERE LOT = v_testfile AND LIMIT_FILE LIKE '%_TV%' AND WAFER = waferid AND KEY_FLAG='02' AND TEST_DATE = min_test_date; --COMMIT; -- canot commit here cause temp table used wac_failrate_tmp SELECT COUNT(*) INTO PAR_CNT FROM (SELECT testfile, wafer, par, COUNT(FAIL_RATE) FROM WAC_EDA WHERE TESTFILE = v_testfile AND WAFER = waferid HAVING COUNT(*) >1 GROUP BY testfile, wafer, par); IF (PAR_CNT>0) THEN a_code := 7777; a_message := 'More than one EDA fail_rate found. !! WAFER['||waferid||'] TEST_FILE['||TESTFILE(I)||']'; END IF; IF (SUBSTR(waferid,1,1)='0') THEN briefwaferid := SUBSTR(waferid,2,1); ELSE briefwaferid := waferid; END IF; DBMS_OUTPUT.PUT_LINE('testfile='||v_testfile||', wafer='||waferid); SELECT MAX(FAIL_RATE) INTO ret_fail_rate FROM WAC_EDA WHERE TESTFILE = v_testfile AND WAFER = waferid; --DBMS_OUTPUT.PUT_LINE('fail_rate : ' ||ret_fail_rate); IF (ret_fail_rate IS NULL) THEN a_code := 1403; a_message := 'EDA TEST FILE NOT FOUND !! PLEASE TRY LATER!! WAFER['||waferid||'] TEST_FILE['||TESTFILE(I)||']'; END IF; IF (ret_fail_rate > 50) THEN -- at least one parameter >= 50 SELECT COUNT(*) INTO cmtcnt FROM PNDN_WATFILES WHERE FILENAME = v_testfile AND INSTR(','||waferid||',',','||briefwaferid||',')>0 AND cmt IS NOT NULL; IF(cmtcnt = 0) THEN -- FAIL_RATE > 50 with no PNDN COMMENT a_code := 9050; a_message := 'WAC FAIL RATE NO PASSED!! WAFER['||waferid||'] TEST_FILE['||TESTFILE(I)||'] FAIL RATE['||ret_fail_rate||']'; END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN a_code := 1403; a_message := 'EDA TEST FILE NOT FOUND !! PLEASE TRY LATER !! WAFER['||waferid||'] TEST_FILE['||TESTFILE(I)||']'; ROLLBACK; WHEN OTHERS THEN a_code := SQLCODE; a_message := SUBSTR(SQLERRM, 1, 150); IF a_code = -2068 OR a_code = -6502 THEN -- Already Disconnect to EDA a_message := 'Unable to connect EIM DB. Please try later.'|| a_message; END IF; --insert into maillog@rtd(sender,sendto,contenttype,subject,body) values ('HOUSE_DB','mes1','text','PROC_WAC_CHECK ERROR. '||a_code ,a_code||' '||a_message); ROLLBACK; END; END IF; -- end skipped wafer END LOOP; -- wafer END LOOP; -- waferlist END IF; -- a_code =0 COMMIT; EXCEPTION WHEN OTHERS THEN a_code := SQLCODE; a_message := SUBSTR(SQLERRM, 1, 150); --insert into maillog@rtd(sender,sendto,contenttype,subject,body) values ('HOUSE_DB','mes1','text','PROC_WAC_CHECK ERROR. '||a_code ,a_code||' '||a_message); ROLLBACK; END;/