c++

/******************************************************************************/ /*                                                                            */ /*  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; }

 

int GETSPDN ::ParseHeader() {     return(ASMail::ParseHeader()); }

int GETSPDN ::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()); }

int GETSPDN ::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);  }   }

int GETSPDN ::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 ---------------------*/ void GETSPDN ::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; /