
create or replace
function FN_WAVE_DESCRIPTION(VI_REF_SER IN CHAR,VI_REF_ID IN CHAR, VI_PTCN IN VARCHAR)
RETURN VARCHAR2 IS TEMP_DESCR VARCHAR2(200);
CNT1 NUMBER;
--TEMP_DESCR VARCHAR2(200);
TEMP_HAZARDOUS CHAR;
TEMP_ITEM_CODE CHAR(10);
TEMP_REF_ID CHAR(10);
TEMP_CASE VARCHAR2(20);
TEMP_ACTIVE VARCHAR2(20);
TEMP_REF_SER CHAR(6);
ISTACKDONE  CHAR(1);

BEGIN
  BEGIN
  --ISTACKDONE := 'N';
  IF(VI_REF_SER = 'R-DSO') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'R-PND') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'S-DSP') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'R-CPA') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'R-TASK') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'RD-DSA') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'RN-DSA') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'RP-ACT') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'RP-PND') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'RP-PND') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'RR-DSA') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'RS-DSO') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'S-DOC') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'M-PACK') THEN
    SELECT DESCR INTO TEMP_DESCR FROM REFSER WHERE REF_SER = VI_REF_SER ;
    RETURN TEMP_DESCR;
  END IF;
 
    if( VI_REF_SER = 'C-PICK') then 
        TEMP_HAZARDOUS := '';
        select wtd.ref_id,WTD.REF_SER, (CASE WHEN I.hazardous IS NULL THEN 'N' ELSE I.hazardous END) AS HAZ into TEMP_REF_ID,TEMP_REF_SER, TEMP_HAZARDOUS
        from wave_task_det WTD, ITEM I , PICK_ORD_DET POD
        where WTD.ptcn= VI_PTCN and WTD.ref_ser ='C-PICK' and WTD.REF_ID = VI_REF_ID 
        AND WTD.REF_ID = POD.PICK_ORDER AND I.ITEM_CODE = POD.ITEM_CODE AND ROWNUM = 1;
        IF (TEMP_REF_SER = 'C-PICK' AND TEMP_HAZARDOUS = 'Y' ) THEN          
          TEMP_CASE := 'HAZ_CASE_CASE';
          ISTACKDONE := 'Y';
        ELSIF (TEMP_REF_SER = 'C-PICK' AND TEMP_HAZARDOUS = 'N' ) THEN
           TEMP_CASE := 'CASE_CASE';
           ISTACKDONE := 'Y';       
        END IF;
    END IF;
    IF ( VI_REF_SER = 'M-PICK') then         
       select wtd.ref_id,WTD.REF_SER, (CASE WHEN I.hazardous IS NULL THEN 'N' ELSE I.hazardous END) AS HAZ into TEMP_REF_ID,TEMP_REF_SER, TEMP_HAZARDOUS
       from wave_task_det WTD, ITEM I , PICK_ORD_DET POD
       where WTD.ptcn= VI_PTCN and WTD.ref_ser ='M-PICK' and WTD.REF_ID  = VI_REF_ID
       AND WTD.REF_ID = POD.PICK_ORDER AND I.ITEM_CODE = POD.ITEM_CODE AND ROWNUM = 1;
       IF (TEMP_REF_SER = 'M-PICK' AND TEMP_HAZARDOUS = 'Y' ) THEN
         TEMP_CASE := 'HAZ_MASTER_CASE';
         --RETURN TEMP_CASE;
         ISTACKDONE := 'Y';
       ELSIF (TEMP_REF_SER = 'M-PICK' AND TEMP_HAZARDOUS = 'N' ) THEN
         TEMP_CASE := 'MASTER_CASE';
         ISTACKDONE := 'Y';        
       END IF;
    ELSIF ( VI_REF_SER = 'P-PICK' ) then
       TEMP_HAZARDOUS := '';
       select wtd.ref_id,WTD.REF_SER, (CASE WHEN I.hazardous IS NULL THEN 'N' ELSE I.hazardous END) AS HAZ into TEMP_REF_ID,TEMP_REF_SER, TEMP_HAZARDOUS
       from wave_task_det WTD, ITEM I , PICK_ORD_DET POD
       where WTD.ptcn= VI_PTCN and WTD.ref_ser ='P-PICK' and WTD.REF_ID  = VI_REF_ID
       AND WTD.REF_ID = POD.PICK_ORDER AND I.ITEM_CODE = POD.ITEM_CODE AND ROWNUM = 1;
       IF (TEMP_REF_SER = 'P-PICK' AND TEMP_HAZARDOUS = 'Y' ) THEN
         TEMP_CASE := 'HAZ_PARCEL_CASE';
         ISTACKDONE := 'Y';
       ELSIF (TEMP_REF_SER = 'P-PICK' AND TEMP_HAZARDOUS = 'N' ) THEN
         TEMP_CASE := 'PARCEL_CASE';
         ISTACKDONE := 'Y';
       END IF;
     ELSIF ( VI_REF_SER = 'A-PICK' ) then
        select count(*) as cont into CNT1 from wave_task_det WTD where WTD.ptcn = VI_PTCN and WTD.ref_ser ='C-PICK';      
       IF (CNT1 > 0 ) then
          TEMP_CASE := 'CASE_CASE';        
      end if;
      select count(*) as cont into CNT1 from wave_task_det WTD where WTD.ptcn = VI_PTCN and WTD.ref_ser ='M-PICK';       
       IF (CNT1 > 0 ) then
          TEMP_CASE := 'MASTER_CASE';        
      end if;
      select count(*) as cont into CNT1 from wave_task_det WTD where WTD.ptcn = VI_PTCN and WTD.ref_ser ='P-PICK';       
       IF (CNT1 > 0 ) then
          TEMP_CASE := 'PARCEL_CASE';        
      end if;
    end if;   
   
  IF(VI_REF_SER = 'C-PICK' AND TEMP_CASE = 'HAZ_CASE_CASE' ) THEN
    SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'HAZ_CASE_CASE' and MOD_NAME = 'WAVE_TASK' ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'C-PICK' AND TEMP_CASE = 'CASE_CASE' ) THEN
    SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'CASE_CASE' and MOD_NAME = 'WAVE_TASK' ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'M-PICK' AND TEMP_CASE = 'HAZ_MASTER_CASE' ) THEN ---// for normal MASTER pick ------- start
    SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'HAZ_MASTER_CASE' and MOD_NAME = 'WAVE_TASK' ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'M-PICK' AND TEMP_CASE = 'MASTER_CASE' ) THEN
    SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'MASTER_CASE' and MOD_NAME = 'WAVE_TASK' ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'P-PICK' AND TEMP_CASE = 'HAZ_PARCEL_CASE' ) THEN ---// for normal MASTER pick ------- start
    SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'HAZ_PARCEL_CASE' and MOD_NAME = 'WAVE_TASK' ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'P-PICK' AND TEMP_CASE = 'PARCEL_CASE' ) THEN
    SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'PARCEL_CASE' and MOD_NAME = 'WAVE_TASK' ;
    RETURN TEMP_DESCR;
  ELSIF (VI_REF_SER = 'A-PICK') THEN
  
       IF ( (TEMP_CASE <> 'PARCEL_CASE' AND TEMP_CASE <> 'MASTER_CASE') or TEMP_CASE is null ) THEN
           SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'CASE_ACTIVE' and MOD_NAME = 'WAVE_TASK' ;
            RETURN TEMP_DESCR;
        ELSIF ( TEMP_CASE = 'MASTER_CASE') THEN
            SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'MASTER_ACTIVE' and MOD_NAME = 'WAVE_TASK' ;
            RETURN TEMP_DESCR;
        ELSIF ( TEMP_CASE = 'PARCEL_CASE') THEN
           SELECT DESCR INTO TEMP_DESCR FROM GENcodes WHERE FLD_NAME = 'PARCEL_ACTIVE' and MOD_NAME = 'WAVE_TASK' ;
           RETURN TEMP_DESCR;
       
        END IF;
  END IF; 
 
  EXCEPTION
  WHEN NO_DATA_FOUND  THEN
   TEMP_DESCR :='';
  END;
  RETURN TEMP_DESCR;
END;