CREATE OR REPLACE FUNCTION "APPS"."RESOLUTION_TIME" (p_init_num VARCHAR2) RETURN varchar
  IS
  v_date_in  DATE :=NULL;
  v_date_out  DATE :=NULL;
  v_temp_date  DATE;
  v_date_in_hh NUMBER;
  v_date_out_hh NUMBER;
  v_status VARCHAR2(100):=NULL;
  v_requestid VARCHAR2(100):=NULL;
  v_time_hours  NUMBER:=0;
  v_loop_count NUMBER:=0;
  v_queue VARCHAR2(100);
  v_class VARCHAR2(50);
  v_flag NUMBER:=3;
  v_holiday number:=0;
  v_left_time number:=0;
  v_max_date date;
  v_status_flag number :=0;
  v_SlaTimeId VARCHAR2(50);
  v_time_diff number:=0;
  CURSOR c_history  IS   
      SELECT
      XXE_F749_INIT_HISTORY.Request_ID,
      XXE_F749_INIT_HISTORY.Init_Number,
      XXE_F749_INIT_HISTORY.Create_Date,
      XXE_F749_INIT.Class,
      XXE_F749_INIT_HISTORY.Init_Status,
      XXE_F749_INIT_HISTORY.Competence_Queue
      FROM XXE_F749_INIT_HISTORY,XXE_F749_INIT
      WHERE XXE_F749_INIT_HISTORY.Init_Number=XXE_F749_INIT.Init_Number
      AND XXE_F749_INIT_HISTORY.Init_Number = p_init_num
      and XXE_F749_INIT.Class in ('1/A','2/B','3/C','4/D')
      ORDER BY XXE_F749_INIT_HISTORY.create_date_sec; 
      
  CURSOR c_holiday IS
      SELECT DISTINCT start_date_time
      FROM jtf_cal_exceptions_vl
      ORDER BY start_date_time;
  BEGIN
      FOR c_history_rec IN c_history
      LOOP
/*Stop traversing the history records of the InIT if for a InIT resolved and closed status are find consecutive*/
/*      IF(v_status='Resolved' ) THEN
          IF(c_history_rec.Init_Status='Closed') THEN
            EXIT;
          END IF;
        END IF;
        IF(c_history_rec.Init_Status = 'Resolved' and v_status_flag = 0) then
          v_date_out:=c_history_rec.create_date;
          v_requestid:=c_history_rec.request_id;
          v_status_flag:=1;
        END IF;
*/        
        v_queue:=UPPER(trim(c_history_rec.Competence_Queue));
        v_status:=c_history_rec.Init_Status;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        V_CLASS:=c_history_rec.CLASS;
--To get the date and time when InIt entered Alpha Maintenance Queue
        IF ((v_queue='ALPHA MAINT ONSITE' OR v_queue='ALPHA MAINT OFFSHORE') AND v_loop_count=0 AND v_status!='Dormant' and v_status!='Resolved') THEN
          v_date_in:=c_history_rec.create_date;
/*        if(v_date_out is NULL) then
            v_date_out:=c_history_rec.create_date;
          end if;
*/
          v_temp_date:=v_date_in;
          v_date_in_hh:=TO_NUMBER (TO_CHAR (v_date_in,'hh24'));
          v_loop_count:=v_loop_count+1;
        END IF;
--To get the time InIT moved out of the Alpha Maintenance Queue 
        IF (v_loop_count=1 AND ((v_queue NOT IN ('ALPHA MAINT ONSITE','ALPHA MAINT OFFSHORE')) OR v_status = 'Dormant') ) THEN
          v_requestid:=c_history_rec.request_id;
          v_date_out:=c_history_rec.create_date;
          v_date_out_hh:=TO_NUMBER (TO_CHAR (v_date_out,'hh24' ) );
    
    /*To get the total time for which InIT remained in Alpha Maintenance Queue*/

           /*INIT entered and exited from Alpha Maint Queue on the different dates         */
          IF((TO_CHAR(v_date_in,'mm/dd/yyyy')) !=  (TO_CHAR(v_date_out,'mm/dd/yyyy')) ) THEN
            FOR c_holiday_rec in c_holiday
            Loop
              if(to_char(v_date_in,'dd-mon-yyyy')=to_char(c_holiday_rec.start_date_time,'dd-mon-yyyy')) then
                v_holiday:=1;
                v_temp_date:=(TO_DATE (CONCAT (TO_CHAR (v_date_in, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss'));
                exit;
              end if;
            End loop;  
            IF(trim(TO_CHAR(v_date_in,'Day')) IN ('Saturday','Sunday')) THEN
              v_temp_date:=(TO_DATE (CONCAT (TO_CHAR (v_date_in, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss'));
              v_holiday:=1;
            END IF;                          
            IF (v_date_in_hh>=8 AND v_date_in_hh<18 and v_holiday!=1) THEN
              v_time_hours:=v_time_hours+(((TO_DATE(CONCAT(TO_CHAR(v_date_in,'mm/dd/yyyy'),'18:00:00'),'mm/dd/yyyy hh24:mi:ss') )- v_date_in)*24);
              v_temp_date:=(TO_DATE (CONCAT (TO_CHAR (v_date_in, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss'))+ 1;
            END IF;
            IF( v_date_in_hh>=18 and v_holiday!=1)  THEN
              v_temp_date:=( TO_DATE (CONCAT (TO_CHAR (v_date_in,'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss'))+ 1;
            END IF;
            v_holiday:=0;
            WHILE ((TO_DATE (CONCAT (TO_CHAR (v_temp_date, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss')) <(TO_DATE (CONCAT (TO_CHAR (v_date_out, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss')) )
            LOOP
              For c_holiday_rec in c_holiday
              Loop
                If(to_char(v_temp_date,'dd-mon-yyyy')=to_char(c_holiday_rec.start_date_time,'dd-mon-yyyy')) then
                  v_holiday:=1;
                End if;
              End loop;  
              IF(trim(TO_CHAR(v_temp_date,'Day')) NOT IN ('Saturday','Sunday') and v_holiday!=1) THEN
                v_time_hours:=v_time_hours+10;
              END IF;
              v_temp_date:=v_temp_date+1;
              v_holiday:=0;
            END LOOP;
            IF(v_date_out_hh >=18) THEN
              v_time_hours:=v_time_hours+10;
            END IF;
            IF(v_date_out_hh >=8 AND v_date_out_hh<18) THEN
              v_time_hours:=v_time_hours+(v_date_out-(TO_DATE(CONCAT(TO_CHAR(v_date_out,'mm/dd/yyyy'),'08:00:00'),'mm/dd/yyyy hh24:mi:ss')))*24;
            END IF;
          END IF;
          /*INIT entered and exited on the same day from Alpha Maint Queue          */
          IF((TO_CHAR(v_date_in,'mm/dd/yyyy')) = (TO_CHAR(v_date_out,'mm/dd/yyyy')) ) THEN
            For c_holiday_rec in c_holiday
            Loop
              If(to_char(v_date_in,'dd-mon-yyyy')=to_char(c_holiday_rec.start_date_time,'dd-mon-yyyy')) then
                v_holiday:=1;
                v_time_hours:=0;
                exit;
              End if;
            End loop;  
            IF(trim(TO_CHAR(v_date_in,'Day')) IN ('Saturday','Sunday')) THEN
              v_time_hours:=0;
              v_holiday:=1;
            END IF;      
            If(v_holiday!=1) then              
              IF(v_date_out<=(TO_DATE(CONCAT(TO_CHAR(v_date_out,'mm/dd/yyyy'),'18:00:00'),'mm/dd/yyyy hh24:mi:ss')) ) THEN
                v_time_hours:=v_time_hours+((v_date_out-v_date_in)*24);
              ELSE
                v_time_hours:=v_time_hours+((TO_DATE(CONCAT(TO_CHAR(v_date_out,'mm/dd/yyyy'),'18:00:00'),'mm/dd/yyyy hh24:mi:ss'))-v_date_in)*24;
              END IF;
            END IF;
          End If;
          v_loop_count:=0;
--Stop Traversing the InIT history record with the occurance of Closed Status
          IF (c_history_rec.init_status ='Closed') THEN
            EXIT;
          END IF;                         
        END IF;/*Init exits out of Alpha Maint queue */
      END LOOP;/*loop ends with complete traversal of Init history records */
      
      /*To calculate the duration of time till now fro the open INIT's in Alpha Miant Queue */
      If(v_loop_count=1 ) then
        v_holiday:=0;
        select to_number(substr(to_char(systimestamp,'TZH'),2,3)) into v_time_diff from dual;
        if(v_time_diff =2) then
          v_date_out:=(sysdate +3/24+30/(24*60));
        else
          v_date_out:=(sysdate +4/24+30/(24*60));
        end if;   
      --v_date_out:=sysdate + INTERVAL '210' MINUTE;
        v_date_out_hh:=TO_NUMBER (TO_CHAR (v_date_out,'hh24' ) );
--To get the total time for which InIT remained in Alpha Maintenance Queue
        IF((TO_CHAR(v_date_in,'mm/dd/yyyy')) !=  (TO_CHAR(v_date_out,'mm/dd/yyyy')) ) THEN
          For c_holiday_rec in c_holiday
          Loop
            If(to_char(v_date_in,'dd-mon-yyyy')=to_char(c_holiday_rec.start_date_time,'dd-mon-yyyy')) then
              v_holiday:=1;
              v_temp_date:=(TO_DATE (CONCAT (TO_CHAR (v_date_in, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss'));
              exit;
            End if;
          End loop;  
          IF(trim(TO_CHAR(v_date_in,'Day')) IN ('Saturday','Sunday')) THEN
            v_temp_date:=(TO_DATE (CONCAT (TO_CHAR (v_date_in, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss'));
            v_holiday:=1;
          END IF;                          
          IF (v_date_in_hh>=8 AND v_date_in_hh<18 and v_holiday!=1) THEN
            v_time_hours:=v_time_hours+(((TO_DATE(CONCAT(TO_CHAR(v_date_in,'mm/dd/yyyy'),'18:00:00'),'mm/dd/yyyy hh24:mi:ss') )- v_date_in)*24);
            v_temp_date:=(TO_DATE (CONCAT (TO_CHAR (v_date_in, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss'))+ 1;
          END IF;
          IF( v_date_in_hh>=18 and v_holiday!=1)  THEN
            v_temp_date:=( TO_DATE (CONCAT (TO_CHAR (v_date_in,'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss'))+ 1;
          END IF;
          v_holiday:=0;
          WHILE ((TO_DATE (CONCAT (TO_CHAR (v_temp_date, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss')) <(TO_DATE (CONCAT (TO_CHAR (v_date_out, 'mm/dd/yyyy'), '08:00:00'),'mm/dd/yyyy hh24:mi:ss')) )
          LOOP
            For c_holiday_rec in c_holiday
            Loop
              If(to_char(v_temp_date,'dd-mon-yyyy')=to_char(c_holiday_rec.start_date_time,'dd-mon-yyyy')) then
                v_holiday:=1;
              End if;
            End loop;  
            IF(trim(TO_CHAR(v_temp_date,'Day')) NOT IN ('Saturday','Sunday') and v_holiday!=1) THEN
              v_time_hours:=v_time_hours+10;
            END IF;
            v_temp_date:=v_temp_date+1;
            v_holiday:=0;
          END LOOP;
          IF(v_date_out_hh >=18) THEN
            v_time_hours:=v_time_hours+10;
          END IF;
          IF(v_date_out_hh >=8 AND v_date_out_hh<18) THEN
            v_time_hours:=v_time_hours+(v_date_out-(TO_DATE(CONCAT(TO_CHAR(v_date_out,'mm/dd/yyyy'),'08:00:00'),'mm/dd/yyyy hh24:mi:ss')))*24;
          END IF;
        END IF;
        
        IF((TO_CHAR(v_date_in,'mm/dd/yyyy')) = (TO_CHAR(v_date_out,'mm/dd/yyyy')) ) THEN
          For c_holiday_rec in c_holiday
          Loop
            If(to_char(v_date_in,'dd-mon-yyyy')=to_char(c_holiday_rec.start_date_time,'dd-mon-yyyy')) then
              v_holiday:=1;
              v_time_hours:=0;
              Exit;
            End if;
          End loop;  
          IF(trim(TO_CHAR(v_date_in,'Day')) IN ('Saturday','Sunday')) THEN
            v_time_hours:=0;
            v_holiday:=1;
          END IF;      
          If(v_holiday!=1) then              
            IF(v_date_out<=(TO_DATE(CONCAT(TO_CHAR(v_date_out,'mm/dd/yyyy'),'18:00:00'),'mm/dd/yyyy hh24:mi:ss')) ) THEN
              v_time_hours:=v_time_hours+((v_date_out-v_date_in)*24);
            ELSE
              v_time_hours:=v_time_hours+((TO_DATE(CONCAT(TO_CHAR(v_date_out,'mm/dd/yyyy'),'18:00:00'),'mm/dd/yyyy hh24:mi:ss'))-v_date_in)*24;
            END IF;
          END IF;
        End if;
        v_loop_count:=0;
      End if;
--Set flag according to the class of the InIT and the time for which init was in Alpha Maintenance Queue  
--v_flag=0 (For Init's which are within SLA and we have less time left )
--v_flag=1 (For Init's which have gone out of SLA)
--v_flag=2 (For Init's which are within SLA and we have ample of time left)
      IF (v_time_hours>2 AND V_CLASS='1/A') THEN
        v_flag:=1;
      ELSIF(v_time_hours>5 AND V_CLASS='2/B') THEN
        v_flag:=1;
      ELSIF(v_time_hours>50 AND V_CLASS='3/C') THEN
        v_flag:=1;
      ELSIF(v_time_hours<30 AND V_CLASS='3/C') THEN
        v_flag:=2;
      ELSE
        v_flag:=0;
      END IF;

--V_SlaTimeId:=CONCAT(v_flag, (CONCAT((TO_CHAR(v_date_out,'Mon-YYYY')),v_requestid) ));

      v_max_date:=v_date_out;
      If(v_time_hours<=50 and v_class='3/C' and v_flag=0) then
        v_left_time:=round((50-v_time_hours)*60);
 
        While(v_left_time!=0)
        Loop
    --To check for Holiday
    
          For c_holiday_rec in c_holiday
          Loop
            If(to_char(v_max_date,'dd-mon-yyyy')=to_char(c_holiday_rec.start_date_time,'dd-mon-yyyy')) then
              v_max_date:=(TO_DATE(CONCAT(TO_CHAR(v_max_date+1,'mm/dd/yyyy'),'08:00:00'),'mm/dd/yyyy hh24:mi:ss'));
            End if;
          End loop;  
          IF(trim(TO_CHAR(v_max_date,'Day')) IN ('Saturday','Sunday')) THEN
            v_max_date:=(TO_DATE(CONCAT(TO_CHAR(v_max_date+1,'mm/dd/yyyy'),'08:00:00'),'mm/dd/yyyy hh24:mi:ss'));
          END IF;
          
    
          If( (v_max_date between (TO_DATE(CONCAT(TO_CHAR(v_max_date,'mm/dd/yyyy'),'08:00:00'),'mm/dd/yyyy hh24:mi:ss')) and (TO_DATE(CONCAT(TO_CHAR(v_max_date,'mm/dd/yyyy'),'18:00:00'),'mm/dd/yyyy hh24:mi:ss'))) ) then
            v_left_time:=v_left_time-1;
          End if;
          v_max_date:=v_max_date+(1/(24*60));
    
        End loop;

      End if;

      RETURN concat(v_flag,v_max_date);
      --return p_init_num;
    CLOSE c_history;
  END Resolution_Time;
/