Home » SQL & PL/SQL » SQL & PL/SQL » a procedure taking long time while running it
a procedure taking long time while running it [message #256913] Tue, 07 August 2007 02:43 Go to next message
qewani
Messages: 51
Registered: December 2005
Location: uaq
Member

am facing a problem with this overtime procedure which is taking about 47 seconds (long time) when i run it for a specific employee, i think because there are many looping thats why its slow. can any one tell me how to make it faster in run.
procedure l_categorize_ot(
                          P_EMP_CODE                    IN      VARCHAR2  ,
                          M_OT_PROC_START_DT             IN     DATE    ,
                          M_OT_PROC_END_DT		  IN    DATE    ,
                          M_PROC_START_DT                 IN      DATE  ,
                          P_EMP_PAY_TYPE		  VARCHAR2,
P_STATUS     IN OUT            VARCHAR2
           
                         ) 
IS
   V_OT_CODE                PW_FS.CODE%TYPE   ;
   V_OT_RATE1              NUMBER(5,2)       ;
   V_OT_RATE2              NUMBER(5,2)       ; 
   V_OT_EARN_CODE_1         PW_FS.CODE%TYPE   ;   
   V_OT_EARN_CODE_2         PW_FS.CODE%TYPE   ;	
   V_OT_MAX_HRS             PW_FS.HOURS%TYPE  ;
   V_OT_BASIC_LC_AMOUNT     PW_FS.AMOUNT%TYPE ;
   V_OT1_TOT_HRS            PW_FS.HOURS%TYPE  ;
   V_OT2_TOT_HRS            PW_FS.HOURS%TYPE  ;
   --M_OT_PROC_START_DT       DATE ;
   --M_OT_PROC_END_DT         DATE ;
   M_TEMP_ALLW_CODE         VARCHAR2(6);
   M_TEMP_EARN_DEDUC        VARCHAR2(6);
   M_LC_AMOUNT              PW_FS.AMOUNT%TYPE ;
   M_FC_AMOUNT              PW_FS.AMOUNT%TYPE ;

   M_OT_BASIC_LC_AMOUNT     PW_FS.AMOUNT%TYPE ;
   M_OT_BASIC_FC_AMOUNT     PW_FS.AMOUNT%TYPE ;
   M_OT_BASIC_CURR_CODE     PW_FS.CODE%TYPE   ;
   V_STD_WK_HRS NUMBER(5,2) := 240;
   

  V_MAX_HRS  PW_FS.HOURS%TYPE  ;
V_TOT_HRS PW_FS.HOURS%TYPE  ;
V_TOT_OT1_HRS PW_FS.HOURS%TYPE  ;
V_TOT_OT2_HRS PW_FS.HOURS%TYPE  ;
V_excess_HRS PW_FS.HOURS%TYPE  ;
v_excess_ot2_hrs PW_FS.HOURS%TYPE  ;
v_excess_ot1_hrs PW_FS.HOURS%TYPE  ;
V_EXCESS_OT_RATE1 NUMBER;
V_OT_BASIC PW_FS.AMOUNT%TYPE ;
V_DEPT_CODE VARCHAR2(6);

v_ot_hrs PW_FS.HOURS%TYPE  ;
V_OT_MAX_AMOUNT  NUMBER; 
 V_OT_MAX_PERC    NUMBER;
V_STD_WKG_HRS   NUMBER;
V_PROC_MTH NUMBER;
V_OT_MTH NUMBER;

---------------------------------------------------->>new variables start
V_WEEK_STRT_DT DATE;
 
R_OTD_OTH_SYS_ID NUMBER(15);
R_OTD_SYS_ID   NUMBER(15);        
R_OTD_DT   DATE;
R_OTD_EMP_CODE  VARCHAR2(15);          
R_OTD_OT_CODE VARCHAR2(15);
R_OTD_COMP_CODE   VARCHAR2(15);       
R_OTD_DEPT_CODE VARCHAR2(15);
R_TOT_OT1_HRS PW_FS.HOURS%TYPE  ;
R_TOT_OT2_HRS PW_FS.HOURS%TYPE  ;
R_EMP_PERSONAL_GRADE VARCHAR2(15);

R_MINUS_OT NUMBER(15);
R_OT_DIVID NUMBER(15);
R_FISRT_DAY DATE;
R_LAST_DAY DATE;
R_BASIC_SAL NUMBER(14,3);
R_DAY_AMT   NUMBER(14,3);
R_DAY_AMT_X NUMBER(14,3);
V_TOT_MIN   PW_FS.HOURS%TYPE  ;
R_OT_CODE   VARCHAR2(15);
R_OT_RATE   NUMBER(14,2);
R_EXCESS_OT_HRS_AMT NUMBER(14,3);
R_OT_HRS_AMT	NUMBER(14,3);
R_1_OT_HR_COST  NUMBER(14,3);
R_RAMADAN_FR DATE;
R_RAMADAN_TO DATE;
R_MINUS_1 NUMBER(14);
r_ot_yes_no  varchar2(5);
--------------------------------------------------->>end of new variables

--------------------------------------------------->>start of new cursors
 CURSOR R_check_OT_DETAIL IS
          SELECT  'X'
          FROM   PT_OVERTIME_DETAIL        , PT_OVERTIME_HEAD,PM_EMP_KEY
          WHERE  
 OTH_COMP_CODE             = :GLOBAL.M_COMP_CODE AND
                 OTD_OTH_SYS_ID            = OTH_SYS_ID
          AND    OTD_EMP_CODE              = P_EMP_CODE 
          AND    OTD_DT            BETWEEN M_OT_PROC_START_DT AND M_OT_PROC_END_DT
          AND    EMP_CODE                  = P_EMP_CODE
          AND    OTH_APPR_UID IS NOT NULL
          AND    NVL(EMP_OT_PAID_YN, 'N') != 'Y' order by OTD_DT;



CURSOR EMP_GRADE IS SELECT GRADE FROM EMPDATA WHERE EMP_CODE =P_EMP_CODE;

CURSOR C_GET_OT_RATE IS
          SELECT OT_RATE_1 
          FROM   PM_OVERTIME
          WHERE  OT_CODE      = R_OT_CODE;

 CURSOR C_GET_OT_DETAIL_B4 IS --TO GET DAYS B4 FIRST WEEK OF MONTH
          SELECT  OTD_OTH_SYS_ID,OTD_SYS_ID,OTD_DT, OTD_EMP_CODE,
	OTD_OT_CODE               , 
		OTD_COMP_CODE,OTD_DEPT_CODE,  
                NVL(( TRUNC(OTD_OT1_HRS)   + (OTD_OT1_HRS - TRUNC(OTD_OT1_HRS))* 100/60  ) ,0)   D_TOT_OT1_HRS , 
                 NVL((TRUNC(OTD_OT2_HRS)   + (OTD_OT2_HRS - TRUNC(OTD_OT2_HRS))* 100/60  ) ,0)
		     D_TOT_OT2_HRS,EMP_PERSONAL_GRADE
          FROM   PT_OVERTIME_DETAIL        , PT_OVERTIME_HEAD,PM_EMP_KEY
          WHERE  
 OTH_COMP_CODE             = :GLOBAL.M_COMP_CODE AND   
                 OTD_OTH_SYS_ID            = OTH_SYS_ID
          AND    OTD_EMP_CODE              = P_EMP_CODE 
          AND    OTD_DT   < R_FISRT_DAY AND OTD_DT >= M_OT_PROC_START_DT
          AND    EMP_CODE                  = P_EMP_CODE
          AND    OTH_APPR_UID IS NOT NULL
          AND    NVL(EMP_OT_PAID_YN, 'N') != 'Y' order by OTD_DT;

 CURSOR C_GET_OT_DETAIL_AF IS --TO GET DAYS AFTER LAST WEEK OF THE MONTH
          SELECT  OTD_OTH_SYS_ID,OTD_SYS_ID,OTD_DT, OTD_EMP_CODE,
	OTD_OT_CODE               , 
		OTD_COMP_CODE,OTD_DEPT_CODE,  
                NVL(( TRUNC(OTD_OT1_HRS)   + (OTD_OT1_HRS - TRUNC(OTD_OT1_HRS))* 100/60  ) ,0)   D_TOT_OT1_HRS , 
                 NVL((TRUNC(OTD_OT2_HRS)   + (OTD_OT2_HRS - TRUNC(OTD_OT2_HRS))* 100/60  ) ,0)
		     D_TOT_OT2_HRS,EMP_PERSONAL_GRADE
          FROM   PT_OVERTIME_DETAIL        , PT_OVERTIME_HEAD,PM_EMP_KEY
          WHERE  
 OTH_COMP_CODE             = :GLOBAL.M_COMP_CODE AND
                 OTD_OTH_SYS_ID            = OTH_SYS_ID
          AND    OTD_EMP_CODE              = P_EMP_CODE 
          AND    OTD_DT   > R_LAST_DAY AND OTD_DT <= M_OT_PROC_END_DT
          AND    EMP_CODE                  = P_EMP_CODE
          AND    OTH_APPR_UID IS NOT NULL
          AND    NVL(EMP_OT_PAID_YN, 'N') != 'Y' order by OTD_DT;

CURSOR R_GET_RAMADAN_FR IS 
	SELECT to_date(P_VALUE,'dd-mm-yyyy') FROM MENU_PARAMETER WHERE P_ID IN ('RAMADAN_FR');
CURSOR R_GET_RAMADAN_TO IS 
	SELECT to_date(P_VALUE,'dd-mm-yyyy') FROM MENU_PARAMETER WHERE P_ID IN ('RAMADAN_TO');
--------------------------------------------------->>end of new cursors 

 CURSOR C_GET_OT_DETAIL IS
          SELECT  OTD_OTH_SYS_ID,OTD_SYS_ID,OTD_DT, OTD_EMP_CODE,
	OTD_OT_CODE               , 
		OTD_COMP_CODE,OTD_DEPT_CODE,  
                NVL(( TRUNC(OTD_OT1_HRS)   + (OTD_OT1_HRS - TRUNC(OTD_OT1_HRS))* 100/60  ) ,0)   D_TOT_OT1_HRS , 
                 NVL((TRUNC(OTD_OT2_HRS)   + (OTD_OT2_HRS - TRUNC(OTD_OT2_HRS))* 100/60  ) ,0)
		     D_TOT_OT2_HRS,EMP_PERSONAL_GRADE
          FROM   PT_OVERTIME_DETAIL        , PT_OVERTIME_HEAD,PM_EMP_KEY
          WHERE  
 OTH_COMP_CODE             = :GLOBAL.M_COMP_CODE AND
                 OTD_OTH_SYS_ID            = OTH_SYS_ID
          AND    OTD_EMP_CODE              = P_EMP_CODE 
          AND    OTD_DT            BETWEEN R_FISRT_DAY AND R_LAST_DAY
          AND    EMP_CODE                  = P_EMP_CODE
          AND    OTH_APPR_UID IS NOT NULL
          AND    NVL(EMP_OT_PAID_YN, 'N') != 'Y' order by OTD_DT;
  CURSOR C_GET_EXCESS_OT IS
         SELECT EO_EMP_CODE,TO_NUMBER(TO_CHAR(EO_DT,'YYYYMM')) OT_MONTH,EO_OT_CODE, 
           SUM(NVL(EO_EXCESS_OT1_HRS,0)) EXCESS_OT1_HRS, 
           SUM(NVL(EO_EXCESS_OT2_HRS,0)) EXCESS_OT2_HRS
             FROM PT_OT_EXCESS_DUMMY
         WHERE EO_EMP_CODE=P_EMP_CODE
               AND EO_DT BETWEEN M_OT_PROC_START_DT AND M_OT_PROC_END_DT
	GROUP BY EO_EMP_CODE,TO_NUMBER(TO_CHAR(EO_DT,'YYYYMM')),EO_OT_CODE;

  CURSOR C_GET_OT_CODE_DETAILS (M_OT_CODE VARCHAR2) IS --C_GET_OT_CODE_DETAILS to get ot main rules from PM_OVERTIME
          SELECT OT_RATE_1      ,   OT_RATE_2      ,   
                 OT_EARN_CODE_1 ,   OT_EARN_CODE_2 ,
                 OT_MAX_HRS     ,   OT_MAX_AMOUNT  ,   
                 OT_MAX_PERC    
          FROM   PM_OVERTIME
          WHERE  OT_CODE      = M_OT_CODE ;
 CURSOR C_GET_BASIC_SAL_AMT (M_OT_CODE VARCHAR2) IS
          SELECT SUM(NVL(EMPA_LC_AMOUNT, 0))
          FROM   PM_EMP_ALLOWANCE
          WHERE  
  EMPA_COMP_CODE  = :GLOBAL.M_COMP_CODE  AND
                   EMPA_CODE       = P_EMP_CODE
          AND  (
                   ( EMPA_FROM_DT           BETWEEN M_OT_PROC_START_DT  AND  M_OT_PROC_END_DT
                  OR EMPA_UPTO_DT           BETWEEN M_OT_PROC_START_DT  AND  M_OT_PROC_END_DT ) 
               OR  ( M_OT_PROC_START_DT    BETWEEN EMPA_FROM_DT         AND EMPA_UPTO_DT
                  OR  M_OT_PROC_END_DT     BETWEEN EMPA_FROM_DT         AND EMPA_UPTO_DT  )
               ) 
          AND    EXISTS
                 (
                  SELECT 'X'
                  FROM   PM_OVERTIME_ALLOWANCES
                  WHERE  OTA_CODE      = M_OT_CODE
                  AND    OTA_ALLW_CODE = EMPA_ALLW_CODE
                 ) ;
 CURSOR C_GET_DEPT IS
          SELECT EMP_DEPT_CODE FROM PM_EMP_KEY WHERE EMP_CODE=P_EMP_CODE;

CURSOR C_GET_OT_ALLW_CODE(M_OT_CODE VARCHAR2) IS
          SELECT  ALLW_EARN_DEDUC,OTA_ALLW_CODE 
          FROM   PM_OVERTIME_ALLOWANCES, PM_ALLOWANCE
          WHERE  OTA_CODE = M_OT_CODE 
          AND    OTA_ALLW_CODE = ALLW_CODE 
          AND    ALLW_CASH_KIND             = 'C'
          AND    ALLW_MTH_NMTH              = 'M'
          AND    NVL(ALLW_PAYROLL_YN, 'N')  = 'Y' ; 
 CURSOR C_GET_MAX_OT IS
          SELECT TO_NUMBER( PARA_VALUE) from PM_COMP_PARAMETER
where PARA_CODE='MAX_OT_HRS' AND PARA_COMP_CODE='D';

CURSOR  OT_EX IS SELECT  OEH_EMP_CODE from PT_OT_EXCESS_HEAD,
PT_OT_EXCESS_APPROVAL WHERE OEHA_OEH_SYS_ID=
OEH_SYS_ID and OEH_PROC_MTH =TO_CHAR(M_PROC_START_DT,'YYYYMM') AND OEH_EMP_CODE=P_EMP_CODE;

EX VARCHAR2(6);
BEGIN
if R_check_OT_DETAIL%isopen then close R_check_OT_DETAIL; end if;
open R_check_OT_DETAIL; fetch R_check_OT_DETAIL into r_ot_yes_no;
if r_ot_yes_no is not null then 

--DISP_ALERT('STARTED READING L CATEGORIZE OT CODE');
--DELETE FROM PT_OVERTIME_DETAIL_PROC WHERE OTD_EMP_CODE=P_EMP_CODE;
V_PROC_MTH :=TO_NUMBER(TO_CHAR(M_PROC_START_DT,'YYYYMM'));
V_OT_MTH:=TO_NUMBER(TO_CHAR(M_OT_PROC_START_DT,'YYYYMM'));
--DELETE FROM PT_OT_EXCESS_HEAD WHERE OEH_PROC_MTH=V_PROC_MTH AND OEH_EMP_CODE=P_EMP_CODE;
 

-->>>>>>>>>>>
	 ---COMMENTED BY RAJAB ,NO NEED FOR THIS 
	 /******
         OPEN C_GET_MAX_OT;
         FETCH C_GET_MAX_OT INTO V_MAX_HRS;---V_MAX_HRS is ot max amount from the parameter table
         IF C_GET_MAX_OT%NOTFOUND THEN
		CLOSE C_GET_MAX_OT ;
         :M_REASON_FOR_NOT_PROCESSING := 'OT MAX HRSre not available in PM_COMP_PARAMETER...';
         L_INSERT_REC_DURING_FAILURE(P_EMP_CODE);
         END IF;
         CLOSE C_GET_MAX_OT;******/
    OPEN C_GET_DEPT;
         FETCH C_GET_DEPT INTO V_DEPT_CODE;
--DISP_ALERT('EMP IS FROM THIS DEPT '||V_DEPT_CODE);
     CLOSE C_GET_DEPT;
--<<<<<<<<<<

      
  open  OT_EX;
 fetch OT_EX into EX;--EX for approved excess ot       
---+++++++++++++++++++++++++++++++++++++++++++++++++++++++**************NEW CODE 1 ADDED START
/******
THERE IS THREE CONDITIONS :- 
X1 (TO CALCULATE DAYS B4 FIRST WEEK OF THE PROC MONTH),
X2 (TO CALCULATE DAYS INSIDE EACH COMPLETE WEEK OF THE MONTH)<> NOTE THAT EACH WEEK STARTS FROM SUNADY AND ENDS ON SATURDAY,
X3 (TO CALCULATE THE REST DAYS OF THE MONTH)
*******/
--FECTH GRADE
IF EMP_GRADE%ISOPEN THEN CLOSE EMP_GRADE;END IF;OPEN EMP_GRADE; FETCH EMP_GRADE INTO R_EMP_PERSONAL_GRADE;
---new START 
--BY RAJAB TO GET RAMADAN FROM AND TO DT
IF R_GET_RAMADAN_FR%ISOPEN THEN CLOSE R_GET_RAMADAN_FR;END IF;OPEN R_GET_RAMADAN_FR; 
FETCH R_GET_RAMADAN_FR INTO R_RAMADAN_FR;close R_GET_RAMADAN_FR;
IF R_GET_RAMADAN_TO%ISOPEN THEN CLOSE R_GET_RAMADAN_TO;END IF;OPEN R_GET_RAMADAN_TO; 
FETCH R_GET_RAMADAN_TO INTO R_RAMADAN_TO;close R_GET_RAMADAN_TO;
--DISP_ALERT('RAMADAN IS FROM '||TO_CHAR(R_RAMADAN_FR)|| 'TO '||TO_CHAR(R_RAMADAN_TO));
---new END

--FIX WEEK START DT
                 IF RTRIM(LTRIM(to_char(M_OT_PROC_START_DT,'DAY'))) <> 'SUNDAY' then
			--DISP_ALERT('M_OT_PROC_START_DT IS NOT ON SUNDAY, NOW READING B4 FIRST WEEK DAYS CODE');
                     R_FISRT_DAY:=next_day(M_OT_PROC_START_DT,'SUNDAY');
	        	--DISP_ALERT('FISRT SUNDAY OF THIS MONTH IS ON '||TO_CHAR(R_FISRT_DAY));
--X1 START
/******
TO CALCULATE DAYS BEFORE THE START DATE OF FIRST WEEK IN A MONTH IF ANY DAYS EXIST, 
NO NEED TO MINUS 5 EACH OT HR IN THESE DAY WILL BE OT DIRECT UNLESS FOR SPECIFIG CATEGORY WE MINUS 1
AND WE NEED TO STORE THE AMOUNT BEING PAID AND ADD IT TO NEXT MONTH DAYS AMOUNT********/

--DISP_ALERT('X1 LOOP START');
FOR X IN C_GET_OT_DETAIL_B4
	LOOP
--MINUS 1 FOR SPECIFIC CATEGORY ON EACH NEW DAY
IF R_EMP_PERSONAL_GRADE NOT IN ('MM1','MM2') --REPLACE WITH CATEGORY A
AND RTRIM(LTRIM(TO_CHAR(X.OTD_DT,'DAY'))) NOT IN ('FRIDAY','SATURDAY')---ADDED ON 05-08-2007
THEN R_MINUS_1 := 1 ; ELSE R_MINUS_1 := 0;END IF;

--RAMADAN DT SET RATE , EACH DAY HAS DIFFERENT DATE
IF X.OTD_DT BETWEEN R_RAMADAN_FR AND R_RAMADAN_TO THEN R_OT_DIVID:=150;ELSE R_OT_DIVID:=240;END IF;

--TO FIX OT CODE TO GET RATE FROM NEXT CURSOR
R_OT_CODE :=X.OTD_OT_CODE;---USED IN C_GET_BASIC_SAL_AMT

--TO GET OT RATE FOR CALCULATION PURPOSE
IF C_GET_OT_RATE%ISOPEN THEN CLOSE C_GET_OT_RATE;END IF;OPEN C_GET_OT_RATE;FETCH C_GET_OT_RATE INTO R_OT_RATE;

--TO GET EMP' S BASIC SALARY
IF C_GET_BASIC_SAL_AMT%ISOPEN THEN CLOSE C_GET_BASIC_SAL_AMT;END IF;
OPEN C_GET_BASIC_SAL_AMT (R_OT_CODE); FETCH C_GET_BASIC_SAL_AMT INTO R_BASIC_SAL;

--DISP_ALERT('X1 LOOP > STARTED WITH NEW DAY, 
--R_OT_DIVID IS '||TO_CHAR(R_OT_DIVID)||
--'   OT DATE IS '||TO_CHAR(X.OTD_DT)||
--'   R_MINUS_1 = '||to_char(R_MINUS_1)||
--'   R_EMP_PERSONAL_GRADE is '||R_EMP_PERSONAL_GRADE|| 
--'   AND R_OT_CODE IS = '||R_OT_CODE|| 
--'   AND R_OT_RATE IS = '||TO_CHAR(R_OT_RATE)||
--'   AND BASIC SALARY FOR EMP IS '||TO_CHAR(R_BASIC_SAL));

               V_TOT_OT1_HRS:=NVL(X.D_TOT_OT1_HRS,0);
               V_TOT_OT2_HRS:=NVL(X.D_TOT_OT2_HRS,0);
 		
		--DAILY TOT HRS OF OT 
		V_TOT_HRS :=NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);

		--OT TOT WHICH GET INCREASE EACH DAY
	     	--V_TOT_MIN :=NVL(V_TOT_HRS,0)+NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);

		--WE MINUS 1 FOR SPCIFIC GRADES ON DAILY BASIS
		IF NVL(V_TOT_HRS,0)>0 THEN
		V_TOT_HRS :=NVL(V_TOT_HRS,0)-NVL(R_MINUS_1,0);
		ELSE NULL;-- V_TOT_HRS IS 0 SO NO NEED TO MINUS 1 FROM THE OT HR, BY THE WAY IT WILL NEVER BE ZERO
		END IF;
		--DISP_ALERT('V_TOT_HRS IS = '||TO_CHAR(V_TOT_HRS));

--FIX V_TOT_OT1_HRS & V_TOT_OT2_HRS
IF    V_TOT_OT1_HRS = 0 AND V_TOT_OT2_HRS <> 0 THEN V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0);
ELSIF V_TOT_OT2_HRS = 0 AND V_TOT_OT1_HRS <> 0 THEN V_TOT_OT1_HRS :=NVL(V_TOT_HRS,0);
ELSE  V_TOT_OT1_HRS := 0; V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0);--BOTH I THINK WILL NEVER BE ZERO AND BOTH WILL NEVER HAVE HR >> AT LEAST ONE OF THEM MUST BE ZERO HRS I THINK
END IF;
--DISP_ALERT('LAST V_TOT_OT1_HRS IS = '||TO_CHAR(V_TOT_OT1_HRS)||' AND V_TOT_OT2_HRS IS = '||TO_CHAR(V_TOT_OT2_HRS));

		--NO NEED TO MINUS 5 BECUASE IT IS NOT IN A WEEK
		--FOR SPECIFIC CATEGORY WE MINUS 5 FROM THE FIRST DAY OT AND SECOND AND SO... TELL R_MINUS_OT GET 0 AMT
		/****if NVL(V_TOT_MIN,0) >= NVL(R_MINUS_OT,0) then 
		V_TOT_MIN:=NVL(V_TOT_MIN,0)-NVL(R_MINUS_OT,0); R_MINUS_OT:=0;
		ELSE R_MINUS_OT:=NVL(R_MINUS_OT,0)-NVL(V_TOT_MIN,0);V_TOT_MIN:=0;
		END IF;***/
		
--DISP_ALERT(TO_CHAR(NVL(R_BASIC_SAL,0)*NVL(V_TOT_HRS,0)*NVL(R_OT_RATE,0)/R_OT_DIVID)) ;
R_DAY_AMT :=NVL(R_BASIC_SAL,0)*NVL(V_TOT_HRS,0)*NVL(R_OT_RATE,0)/R_OT_DIVID; --DAILY OT AMT

---DAILY WEEK AMOUNT GETTING INCREASE
R_DAY_AMT_X :=NVL(R_DAY_AMT,0) + NVL(R_DAY_AMT_X,0);
--DISP_ALERT('R_DAY_AMT IS = '||TO_CHAR(R_DAY_AMT)|| ' AND R_DAY_AMT_X IS = '||TO_CHAR(R_DAY_AMT_X));
IF NVL(R_DAY_AMT_X,0)<=(NVL(R_BASIC_SAL,0)/2) THEN
	--DISP_ALERT('R_DAY_AMT_X < = HALF OF EMP BASIC ');
IF NVL(V_TOT_HRS,0)>0 THEN ---INSERT ONLY WHEN THERE IS OT HRS
--DISP_ALERT('START INSERTING IN PT_OVERTIME_DETAIL_PROC');
		 INSERT INTO PT_OVERTIME_DETAIL_PROC
			(OTD_OTH_SYS_ID,OTD_SYS_ID ,            
			OTD_DT ,OTD_EMP_CODE ,          
			OTD_OT_CODE,OTD_OT1_HRS ,           
			OTD_OT2_HRS,OTD_COMP_CODE,          
			OTD_DEPT_CODE )
                 VALUES
			(X.OTD_OTH_SYS_ID,X.OTD_SYS_ID ,            
			X.OTD_DT ,X.OTD_EMP_CODE ,          
			X.OTD_OT_CODE,NVL(V_TOT_OT1_HRS,0) ,           
			NVL(V_TOT_OT2_HRS,0),X.OTD_COMP_CODE,          
			X.OTD_DEPT_CODE );
END IF;

ELSE -- AMT IS MORE THAN HALF OF BASIC
--DISP_ALERT('R_DAY_AMT_X  > HALF OF EMP BASIC');
	R_EXCESS_OT_HRS_AMT :=NVL(R_DAY_AMT_X,0)-(NVL(R_BASIC_SAL,0)/2);
--DISP_ALERT('R_EXCESS_OT_HRS_AMT IS '||TO_CHAR(R_DAY_AMT_X)||' - '||TO_CHAR((NVL(R_BASIC_SAL,0)/2))||' = '||TO_CHAR(R_EXCESS_OT_HRS_AMT));
	R_OT_HRS_AMT  :=NVL(R_BASIC_SAL,0)/2;
--DISP_ALERT('R_OT_HRS_AMT IS HALF OF EMP BASIC WHICH IS = '||TO_CHAR(R_OT_HRS_AMT));
--TO GET EACH OF OT HRS AND EXCESS HRS >> WE DIVIDE THE AMOUNT OF EACH ONE ON THE 1 OT HR COST
	R_1_OT_HR_COST :=NVL(R_BASIC_SAL,0)*1*NVL(R_OT_RATE,0)/R_OT_DIVID;--1 OT HR COST IN A SINGLE DAY
--DISP_ALERT('R_1_OT_HR_COST IS = '||TO_CHAR(R_1_OT_HR_COST)); 

--START TO GET EXCESS OT HRS
	IF V_TOT_OT2_HRS = 0 THEN
		V_EXCESS_OT1_HRS :=NVL(R_EXCESS_OT_HRS_AMT,0)/R_1_OT_HR_COST;
		V_EXCESS_OT2_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE EXCESS OT HR IN THIS CASE
	ELSE
		V_EXCESS_OT2_HRS :=NVL(R_EXCESS_OT_HRS_AMT,0)/R_1_OT_HR_COST;
		V_EXCESS_OT1_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE EXCESS OT HR IN THIS CASE
	END IF;
	V_EXCESS_HRS :=NVL(V_EXCESS_OT2_HRS,0)+NVL(V_EXCESS_OT1_HRS,0);
--END TO GET EXCESS OT HRS

	--OT TOT WHICH GET INCREASE EACH DAY
	--V_TOT_MIN :=NVL(V_TOT_MIN,0)-NVL(V_EXCESS_HRS,0);
--DISP_ALERT('V_TOT_MIN IS NOW = '||TO_CHAR(V_TOT_MIN));
--
	IF NVL(V_TOT_HRS,0)>=NVL(V_EXCESS_HRS,0) THEN --IF THERE IS EXCESS HRS SO TOT OT WILL BE ALWAYS > EXCESS
		IF NVL(V_TOT_OT2_HRS,0)=0 THEN
	             V_TOT_OT1_HRS :=NVL(V_TOT_HRS,0)-NVL(V_EXCESS_HRS,0);
	             V_TOT_OT2_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE OT HR IN THIS CASE
		ELSE
		     V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0)-NVL(V_EXCESS_HRS,0);
	             V_TOT_OT1_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE OT HR IN THIS CASE
		END IF;
	ELSE NULL;
	END IF;
        
	--DAILY TOT HRS OF OT 
	V_TOT_HRS :=NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);
--DISP_ALERT('R_1_OT_HR_COST IS = '||TO_CHAR(R_1_OT_HR_COST)||' AND V_TOT_HRS IS '||TO_CHAR(V_TOT_HRS));
--DISP_ALERT('V_EXCESS_HRS IS = '||TO_CHAR(V_EXCESS_HRS));
	--V_TOT_MIN :=NVL(V_TOT_HRS,0) + NVL(V_TOT_MIN,0);
--DISP_ALERT('V_TOT_MIN WHICH GET INCREASE EACH DAY IS = '||TO_CHAR(V_TOT_MIN));
R_DAY_AMT :=NVL(R_OT_HRS_AMT,0); --DAILY OT AMT CHANGED
--DISP_ALERT('R_DAY_AMT IS CHANGED TO HALF OF EMP BASIC WHICH IS = '||TO_CHAR(R_DAY_AMT));

---DAILY WEEK AMOUNT GETTING INCREASE CHANGED
IF NVL(R_DAY_AMT_X,0)>=NVL(R_EXCESS_OT_HRS_AMT,0) THEN --IF THERE IS ANY EXCESS AMT, SO IT MUST BE LESS THAN DAY X AMT, BECAUSE EXCESS AMT IS A PART OF THE DAY X AMT 
R_DAY_AMT_X :=NVL(R_DAY_AMT_X,0)-NVL(R_EXCESS_OT_HRS_AMT,0);
ELSE NULL;
END IF;
--DISP_ALERT('R_DAY_AMT_X IS CHANGED TO BE = '||TO_CHAR(R_DAY_AMT_X));

IF NVL(V_TOT_HRS,0)>0 THEN --INSERT ONLY WHEN THERE IS OT HRS
--DISP_ALERT('NOW INSERTING RECORDS IN OT DETAIL PROC');
		 INSERT INTO PT_OVERTIME_DETAIL_PROC
			(OTD_OTH_SYS_ID,OTD_SYS_ID ,            
			OTD_DT ,OTD_EMP_CODE ,          
			OTD_OT_CODE,OTD_OT1_HRS ,           
			OTD_OT2_HRS,OTD_COMP_CODE,          
			OTD_DEPT_CODE )
                 VALUES
			(X.OTD_OTH_SYS_ID,X.OTD_SYS_ID ,            
			X.OTD_DT ,X.OTD_EMP_CODE ,          
			X.OTD_OT_CODE,NVL(V_TOT_OT1_HRS,0) ,           
			NVL(V_TOT_OT2_HRS,0),X.OTD_COMP_CODE,          
			X.OTD_DEPT_CODE );
END IF;		         
	if EX IS NOT NULL then --EX for approved excess ot    
	null;
	else
		IF V_EXCESS_HRS>0 THEN
--DISP_ALERT(' NOW INSERTING INTO PT_OT_EXCESS_DUMMY');

            INSERT INTO PT_OT_EXCESS_DUMMY
                  (EO_DT,EO_EMP_CODE,EO_OT_CODE ,EO_EXCESS_OT1_HRS,EO_EXCESS_OT2_HRS)
            VALUES (X.OTD_DT,X.OTD_EMP_CODE,X.OTD_OT_CODE,NVL(V_EXCESS_OT1_HRS,0),NVL(V_EXCESS_OT2_HRS,0));
          	END IF;
	end if;

end if;
	--DISP_ALERT('X1 LOOP END');
	END LOOP;
--X1 END

                    ELSE NULL;-- PROC MONTH IS STARTING FROM THE FISRT SUNDAY OF MONTH
		R_FISRT_DAY:=M_OT_PROC_START_DT;
		--DISP_ALERT('M_OT_PROC_START_DT IS ON SUNDAY ,SO NO DAYS B4 FIRST WEEK OF MONTH');
                     END IF;


LOOP--START OF THE WEEK FROM > TO LOOP
R_FISRT_DAY:=R_FISRT_DAY;--FIRST DAY OF WEEK DT
R_LAST_DAY:=R_FISRT_DAY+6;--LAST DAY OF WEEK DT
--DISP_ALERT('WEEK START DATE IS '||TO_CHAR(R_FISRT_DAY)||' AND END DT IS '||TO_CHAR(R_LAST_DAY));

--WEE MINUS 5 FOR SPECIFIC GRADES WEEKLY / VALUE FOR THIS FIELD IS BEING SET ON START OF EVERY NEW WEEK OF THE MONTH
IF R_EMP_PERSONAL_GRADE IN ('MM1','MM2') --REPLACE WITH CATEGORY A
THEN R_MINUS_OT := 0;ELSE R_MINUS_OT := 5;END IF;

--X2 START
	FOR I IN C_GET_OT_DETAIL
	LOOP--START LOOP WEEK DAYS
--RAMADAN DT SET RATE , EACH DAY HAS DIFFERENT DATE
IF I.OTD_DT BETWEEN R_RAMADAN_FR AND R_RAMADAN_TO THEN R_OT_DIVID:=150;ELSE R_OT_DIVID:=240;END IF;

--TO FIX OT CODE TO GET RATE FROM NEXT CURSOR
R_OT_CODE :=I.OTD_OT_CODE;
--TO GET OT RATE FOR CALCULATION PURPOSE
IF C_GET_OT_RATE%ISOPEN THEN CLOSE C_GET_OT_RATE;END IF;OPEN C_GET_OT_RATE;FETCH C_GET_OT_RATE INTO R_OT_RATE;

--TO GET EMP'S BASIC SALARY
IF C_GET_BASIC_SAL_AMT%ISOPEN THEN CLOSE C_GET_BASIC_SAL_AMT;END IF;
OPEN C_GET_BASIC_SAL_AMT (R_OT_CODE); FETCH C_GET_BASIC_SAL_AMT INTO R_BASIC_SAL;

--DISP_ALERT('I.OTD_DT IS = '||TO_CHAR(I.OTD_DT)||
--'   AND R_OT_DIVID IS = '||TO_CHAR(R_OT_DIVID)||
--'   R_MINUS_OT IS '||TO_CHAR(R_MINUS_OT)||
--'   AND V_TOT_MIN IS '||TO_CHAR(V_TOT_MIN)||
--'   AND R_DAY_AMT_X IS '||TO_CHAR(R_DAY_AMT_X)||
--'   R_OT_CODE IS = '||R_OT_CODE||
--'   AND R_OT_RATE IS = '||TO_CHAR(R_OT_RATE)||
--'   AND BASIC SALARY FOR EMP IS '||TO_CHAR(R_BASIC_SAL));

               V_TOT_OT1_HRS:=NVL(I.D_TOT_OT1_HRS,0);
               V_TOT_OT2_HRS:=NVL(I.D_TOT_OT2_HRS,0);
--DISP_ALERT('LAST V_TOT_OT1_HRS IS = '||TO_CHAR(V_TOT_OT1_HRS)||' AND V_TOT_OT2_HRS IS = '||TO_CHAR(V_TOT_OT2_HRS));
		
		--DAILY TOT HRS OF OT 
		V_TOT_HRS :=NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);
--DISP_ALERT('V_TOT_HRS B4 = '||TO_CHAR(V_TOT_HRS));

		--FOR SPECIFIC CATEGORY WE MINUS 5 FROM THE FIRST DAY OT AND SECOND AND SO... TELL R_MINUS_OT GET 0 AMT
		if NVL(V_TOT_HRS,0) >= NVL(R_MINUS_OT,0) then 
		V_TOT_HRS:=NVL(V_TOT_HRS,0)-NVL(R_MINUS_OT,0); R_MINUS_OT:=0;
		ELSE 
		--DISP_ALERT('V_TOT_HRS IS <R_MINUS_OT');
		R_MINUS_OT:=NVL(R_MINUS_OT,0)-NVL(V_TOT_HRS,0);V_TOT_HRS:=0;
		--DISP_ALERT(TO_CHAR(R_MINUS_OT));
		END IF;
--OT TOT WHICH GET INCREASE EACH DAY
	     	V_TOT_MIN :=NVL(V_TOT_HRS,0) + NVL(V_TOT_MIN,0);

--FIX V_TOT_OT1_HRS & V_TOT_OT2_HRS
IF    V_TOT_OT1_HRS = 0 AND V_TOT_OT2_HRS <> 0 THEN V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0);
ELSIF V_TOT_OT2_HRS = 0 AND V_TOT_OT1_HRS <> 0 THEN V_TOT_OT1_HRS :=NVL(V_TOT_HRS,0);
ELSE  V_TOT_OT1_HRS := 0; V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0);
END IF;
--DISP_ALERT('LAST V_TOT_OT1_HRS IS = '||TO_CHAR(V_TOT_OT1_HRS)||' AND V_TOT_OT2_HRS IS = '||TO_CHAR(V_TOT_OT2_HRS));


--DISP_ALERT('R_MINUS_OT IS '||TO_CHAR(R_MINUS_OT)||
--'   AND V_TOT_MIN IS '||TO_CHAR(V_TOT_MIN)||
--'   V_TOT_HRS AF = '||TO_CHAR(V_TOT_HRS));

R_DAY_AMT :=(NVL(R_BASIC_SAL,0)*NVL(V_TOT_HRS,0)*NVL(R_OT_RATE,0)/R_OT_DIVID); --DAILY OT AMT

---DAILY WEEK AMOUNT GETTING INCREASE
R_DAY_AMT_X :=NVL(R_DAY_AMT,0) + NVL(R_DAY_AMT_X,0);

--DISP_ALERT('R_DAY_AMT IS = '||TO_CHAR(R_DAY_AMT)||' AND R_DAY_AMT_X IS = '||TO_CHAR(R_DAY_AMT_X));
--WE GET SUM OF THE PREVIUS R_DAY_AMT_X  

IF NVL(R_DAY_AMT_X,0)<=(NVL(R_BASIC_SAL,0)/2) THEN
--DISP_ALERT('R_DAY_AMT_X IS <= HLF OF THE BASIC');
IF NVL(V_TOT_HRS,0)>0 THEN ---INSERT ONLY IF OT HRS ARE NOT ZERO
--DISP_ALERT('NOW INSERTING IN THE OT DETAIL PROC');
		 INSERT INTO PT_OVERTIME_DETAIL_PROC
			(OTD_OTH_SYS_ID,OTD_SYS_ID ,            
			OTD_DT ,OTD_EMP_CODE ,          
			OTD_OT_CODE,OTD_OT1_HRS ,           
			OTD_OT2_HRS,OTD_COMP_CODE,          
			OTD_DEPT_CODE )
                 VALUES
			(I.OTD_OTH_SYS_ID,I.OTD_SYS_ID ,            
			I.OTD_DT ,I.OTD_EMP_CODE ,          
			I.OTD_OT_CODE,NVL(V_TOT_OT1_HRS,0) ,           
			NVL(V_TOT_OT2_HRS,0),I.OTD_COMP_CODE,          
			I.OTD_DEPT_CODE );
END IF;

ELSE -- AMT IS MORE THAN HALF OF BASIC	
--DISP_ALERT('R_DAY_AMT_X IS > HLF OF THE BASIC');		         
	R_EXCESS_OT_HRS_AMT :=NVL(R_DAY_AMT_X,0)-(NVL(R_BASIC_SAL,0)/2);
--DISP_ALERT('R_EXCESS_OT_HRS_AMT IS ='||TO_CHAR(R_DAY_AMT_X)||' - '||TO_CHAR((NVL(R_BASIC_SAL,0)/2))||' = '||TO_CHAR(R_EXCESS_OT_HRS_AMT));
	R_OT_HRS_AMT  :=NVL(R_BASIC_SAL,0)/2;
--TO GET EACH OF OT HRS AND EXCESS HRS >> WE DIVIDE THE AMOUNT OF EACH ONE ON THE 1 OT HR COST
	R_1_OT_HR_COST :=NVL(R_BASIC_SAL,0)*1*NVL(R_OT_RATE,0)/R_OT_DIVID;--1 OT HR COST IN A SINGLE DAY

--START TO GET EXCESS OT HRS
	IF V_TOT_OT2_HRS = 0 THEN
		V_EXCESS_OT1_HRS :=NVL(R_EXCESS_OT_HRS_AMT,0)/R_1_OT_HR_COST;
		V_EXCESS_OT2_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE EXCESS OT HR IN THIS CASE
	ELSE
		V_EXCESS_OT2_HRS :=NVL(R_EXCESS_OT_HRS_AMT,0)/R_1_OT_HR_COST;
		V_EXCESS_OT1_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE EXCESS OT HR IN THIS CASE
	END IF;
	V_EXCESS_HRS :=NVL(V_EXCESS_OT2_HRS,0)+NVL(V_EXCESS_OT1_HRS,0);
--END TO GET EXCESS OT HRS

	--OT TOT WHICH GET INCREASE EACH DAY
IF NVL(V_TOT_MIN,0)>=NVL(V_EXCESS_HRS,0) THEN
	V_TOT_MIN :=NVL(V_TOT_MIN,0)-NVL(V_EXCESS_HRS,0);
ELSE NULL;
END IF;
--DISP_ALERT('V_TOT_MIN IS NOW = '||TO_CHAR(V_TOT_MIN));
--
	IF NVL(V_TOT_HRS,0)>=NVL(V_EXCESS_HRS,0) THEN --IF THERE IS EXCESS HRS SO TOT OT WILL BE ALWAYS >= EXCESS
		IF NVL(V_TOT_OT2_HRS,0)=0 THEN
	             V_TOT_OT1_HRS :=NVL(V_TOT_HRS,0)-NVL(V_EXCESS_HRS,0);
	             V_TOT_OT2_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE OT HR IN THIS CASE
		ELSE
		     V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0)-NVL(V_EXCESS_HRS,0);
	             V_TOT_OT1_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE OT HR IN THIS CASE
		END IF;
	ELSE NULL;
	END IF;
 
	--DAILY TOT HRS OF OT 
	V_TOT_HRS :=NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);
--DISP_ALERT('R_1_OT_HR_COST IS = '||TO_CHAR(R_1_OT_HR_COST)||
--' AND V_TOT_HRS IS '||TO_CHAR(V_TOT_HRS)||
--' AND V_EXCESS_HRS IS = '||TO_CHAR(V_EXCESS_HRS));

R_DAY_AMT :=NVL(R_OT_HRS_AMT,0); --DAILY OT AMT CHANGED
--DISP_ALERT('R_DAY_AMT IS CHANGED TO HALF OF BASIC WHICH IS = '||TO_CHAR(R_DAY_AMT));

---DAILY WEEK AMOUNT GETTING INCREASE CHANGED
IF NVL(R_DAY_AMT_X,0)>=NVL(R_EXCESS_OT_HRS_AMT,0) THEN --IF THERE IS ANY EXCESS AMT, SO IT MUST BE LESS THAN DAY X AMT, BECAUSE EXCESS AMT IS A PART OF THE DAY X AMT 
R_DAY_AMT_X :=NVL(R_DAY_AMT_X,0)-NVL(R_EXCESS_OT_HRS_AMT,0);
ELSE NULL;
END IF;
--DISP_ALERT('R_DAY_AMT_X IS CHANGED TO BE = '||TO_CHAR(R_DAY_AMT_X));

IF NVL(V_TOT_HRS,0)>0 THEN --INSERT ONLY WHEN THERE IS OT HRS
--DISP_ALERT('INSERTING INTO PT_OVERTIME_DETAIL_PROC');
		 INSERT INTO PT_OVERTIME_DETAIL_PROC
			(OTD_OTH_SYS_ID,OTD_SYS_ID ,            
			OTD_DT ,OTD_EMP_CODE ,          
			OTD_OT_CODE,OTD_OT1_HRS ,           
			OTD_OT2_HRS,OTD_COMP_CODE,          
			OTD_DEPT_CODE )
                 VALUES
			(I.OTD_OTH_SYS_ID,I.OTD_SYS_ID ,            
			I.OTD_DT ,I.OTD_EMP_CODE ,          
			I.OTD_OT_CODE,NVL(V_TOT_OT1_HRS,0) ,           
			NVL(V_TOT_OT2_HRS,0),I.OTD_COMP_CODE,          
			I.OTD_DEPT_CODE );
END IF;	
	         
	if EX IS NOT NULL then --EX for approved excess ot    
	null;
	else
		IF V_EXCESS_HRS>0 THEN
--DISP_ALERT('INSERTING INTO PT_OT_EXCESS_DUMMY TABLE');
            INSERT INTO PT_OT_EXCESS_DUMMY
                  (EO_DT,EO_EMP_CODE,EO_OT_CODE ,EO_EXCESS_OT1_HRS,EO_EXCESS_OT2_HRS)
            VALUES (I.OTD_DT,I.OTD_EMP_CODE,I.OTD_OT_CODE,NVL(V_EXCESS_OT1_HRS,0),NVL(V_EXCESS_OT2_HRS,0));
          	END IF;
	end if;

end if;
	END LOOP;----END LOOP WEEK DAYS
--X2 END

--DISP_ALERT('FINISHED LOOPING THIS WEEK GOING TO NEXT WEEK');
R_FISRT_DAY:=R_LAST_DAY+1;
IF R_LAST_DAY+7 >M_OT_PROC_END_DT THEN

--DISP_ALERT('WEEKS ARE FINSIHED IN THIS MONTH NOW CACULATING OT HRS FOR THE REST MONTH DAYS');

----*************
--X3 START
/*****
TO CALCULATE DAYS AFTER END OF WEEKS IN A MONTH IF ANY EXIST, 
NO NEED TO MINUS 5 EACH OT HR IN THAT DAY WILL BE OT DIRECT
AND WE NEED TO CHECK THE AMOUNT PAID AND ADD IT TO NEXT MONTH DAYS AMOUNT******/
FOR K IN C_GET_OT_DETAIL_AF
	LOOP-- START OF REST MONTH DAYS LOOP
--MINUS 1 FOR SPECIFIC GRADES DAILY BASIS
IF R_EMP_PERSONAL_GRADE NOT IN ('MM1','MM2') --REPLACE WITH CATEGORY A
AND RTRIM(LTRIM(TO_CHAR(K.OTD_DT,'DAY'))) NOT IN ('FRIDAY','SATURDAY')---ADDED ON 05-08-2007
THEN R_MINUS_1 := 1 ; ELSE R_MINUS_1 := 0;END IF;

--RAMADAN DT SET RATE , EACH DAY HAS DIFFERENT DATE
IF K.OTD_DT BETWEEN R_RAMADAN_FR AND R_RAMADAN_TO THEN R_OT_DIVID:=150;ELSE R_OT_DIVID:=240;END IF;

--TO FIX OT CODE TO GET RATE FROM NEXT CURSOR
R_OT_CODE :=K.OTD_OT_CODE;---USED IN C_GET_BASIC_SAL_AMT
--TO GET OT RATE FOR CALCULATION PURPOSE
IF C_GET_OT_RATE%ISOPEN THEN CLOSE C_GET_OT_RATE;END IF;OPEN C_GET_OT_RATE;FETCH C_GET_OT_RATE INTO R_OT_RATE;

--TO GET EMP'S BASIC SALARY
IF C_GET_BASIC_SAL_AMT%ISOPEN THEN CLOSE C_GET_BASIC_SAL_AMT;END IF;
OPEN C_GET_BASIC_SAL_AMT (R_OT_CODE); FETCH C_GET_BASIC_SAL_AMT INTO R_BASIC_SAL;

--DISP_ALERT('REST OF MONTH DAYS DAYS LOOP <> STARTED WITH NEW DAY, 
--R_OT_DIVID IS '||TO_CHAR(R_OT_DIVID)||
--' OT DATE IS '||TO_CHAR(K.OTD_DT)||
--' R_MINUS_1 = '||to_char(R_MINUS_1)||
--' R_EMP_PERSONAL_GRADE is '||R_EMP_PERSONAL_GRADE|| 
--' AND R_OT_CODE IS = '||R_OT_CODE|| 
--' AND R_OT_RATE IS = '||TO_CHAR(R_OT_RATE)||
--' AND BASIC SALARY FOR EMP IS '||TO_CHAR(R_BASIC_SAL));

               V_TOT_OT1_HRS:=NVL(K.D_TOT_OT1_HRS,0);
               V_TOT_OT2_HRS:=NVL(K.D_TOT_OT2_HRS,0);
--DISP_ALERT('LAST V_TOT_OT1_HRS IS = '||TO_CHAR(V_TOT_OT1_HRS)||' AND V_TOT_OT2_HRS IS = '||TO_CHAR(V_TOT_OT2_HRS));
		
		--DAILY TOT HRS OF OT 
		V_TOT_HRS :=NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);
		--WE MINUS 1 FOR SPCIFIC GRADES ON DAILY BASIS
		IF NVL(V_TOT_HRS,0)>0 THEN
		V_TOT_HRS :=NVL(V_TOT_HRS,0)-NVL(R_MINUS_1,0);
		ELSE NULL;-- V_TOT_HRS IS 0 SO NO NEED TO MINUS 1
		END IF;
--DISP_ALERT('V_TOT_HRS IS = '||TO_CHAR(V_TOT_HRS));

--FIX V_TOT_OT1_HRS & V_TOT_OT2_HRS
IF    V_TOT_OT1_HRS = 0 AND V_TOT_OT2_HRS <> 0 THEN V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0);
ELSIF V_TOT_OT2_HRS = 0 AND V_TOT_OT1_HRS <> 0 THEN V_TOT_OT1_HRS :=NVL(V_TOT_HRS,0);
ELSE  V_TOT_OT1_HRS := 0; V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0);
END IF;
--DISP_ALERT('LAST V_TOT_OT1_HRS IS = '||TO_CHAR(V_TOT_OT1_HRS)||' AND V_TOT_OT2_HRS IS = '||TO_CHAR(V_TOT_OT2_HRS));


		--OT TOT WHICH GET INCREASE EACH DAY
	     	--V_TOT_MIN :=NVL(V_TOT_HRS,0)+NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);

		--NO NEED TO MINUS 5 BECUASE IT IS NOT IN A WEEK
		
		/***FOR SPECIFIC CATEGORY WE MINUS 5 FROM THE FIRST DAY OT AND SECOND AND SO... TELL R_MINUS_OT GET 0 AMT
		if NVL(V_TOT_MIN,0) >= NVL(R_MINUS_OT,0) then 
		V_TOT_MIN:=NVL(V_TOT_MIN,0)-NVL(R_MINUS_OT,0); R_MINUS_OT:=0;
		ELSE R_MINUS_OT:=NVL(R_MINUS_OT,0)-NVL(V_TOT_MIN,0);V_TOT_MIN:=0;
		END IF;****/
		

R_DAY_AMT :=NVL(R_BASIC_SAL,0)*NVL(V_TOT_HRS,0)*NVL(R_OT_RATE,0)/R_OT_DIVID; --DAILY OT AMT

---DAILY WEEK AMOUNT GETTING INCREASE
R_DAY_AMT_X :=NVL(R_DAY_AMT,0) + NVL(R_DAY_AMT_X,0);
--DISP_ALERT('R_DAY_AMT IS = '||TO_CHAR(R_DAY_AMT)||' AND R_DAY_AMT_X IS = '||TO_CHAR(R_DAY_AMT_X));

IF NVL(R_DAY_AMT_X,0)<=NVL(R_BASIC_SAL,0)/2 THEN
--DISP_ALERT('R_DAY_AMT_X IS <= HALF OF BASIC'); 
IF NVL(V_TOT_HRS,0)>0 THEN --INSERT ONLY WHEN THERE IS OT HRS
--DISP_ALERT('NOW INSERTING INTO OT DETAIL DETAIL PROC');
		 INSERT INTO PT_OVERTIME_DETAIL_PROC
			(OTD_OTH_SYS_ID,OTD_SYS_ID ,            
			OTD_DT ,OTD_EMP_CODE ,          
			OTD_OT_CODE,OTD_OT1_HRS ,           
			OTD_OT2_HRS,OTD_COMP_CODE,          
			OTD_DEPT_CODE )
                 VALUES
			(K.OTD_OTH_SYS_ID,K.OTD_SYS_ID ,            
			K.OTD_DT ,K.OTD_EMP_CODE ,          
			K.OTD_OT_CODE,NVL(V_TOT_OT1_HRS,0) ,           
			NVL(V_TOT_OT2_HRS,0),K.OTD_COMP_CODE,          
			K.OTD_DEPT_CODE );
END IF;

ELSE -- AMT IS MORE THAN HALF OF BASIC
--DISP_ALERT('R_DAY_AMT_X IS > HALF OF BASIC');

	R_EXCESS_OT_HRS_AMT :=NVL(R_DAY_AMT_X,0)-(NVL(R_BASIC_SAL,0)/2);
--DISP_ALERT('R_EXCESS_OT_HRS_AMT IS = '||TO_CHAR(R_DAY_AMT_X)||' - '||TO_CHAR(R_BASIC_SAL)|| ' = '||TO_CHAR(R_EXCESS_OT_HRS_AMT));

	R_OT_HRS_AMT  :=NVL(R_BASIC_SAL,0)/2;
--DISP_ALERT('R_OT_HRS_AMT IS = '||TO_CHAR(R_OT_HRS_AMT));

--TO GET EACH OF OT HRS AND EXCESS HRS >> WE DIVIDE THE AMOUNT OF EACH ONE ON THE 1 OT HR COST
	R_1_OT_HR_COST :=NVL(R_BASIC_SAL,0)*1*NVL(R_OT_RATE,0)/R_OT_DIVID;--1 OT HR COST IN A SINGLE DAY
--DISP_ALERT('R_1_OT_HR_COST IS = '||TO_CHAR(R_1_OT_HR_COST));

--START TO GET EXCESS OT HRS
	IF V_TOT_OT2_HRS = 0 THEN
		V_EXCESS_OT1_HRS :=NVL(R_EXCESS_OT_HRS_AMT,0)/R_1_OT_HR_COST;
		V_EXCESS_OT2_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE EXCESS OT HR IN THIS CASE
	ELSE
		V_EXCESS_OT2_HRS :=NVL(R_EXCESS_OT_HRS_AMT,0)/R_1_OT_HR_COST;
		V_EXCESS_OT1_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE EXCESS OT HR IN THIS CASE
	END IF;
	V_EXCESS_HRS :=NVL(V_EXCESS_OT2_HRS,0)+NVL(V_EXCESS_OT1_HRS,0);
--END TO GET EXCESS OT HRS

	--OT TOT WHICH GET INCREASE EACH DAY
	--V_TOT_MIN :=NVL(V_TOT_MIN,0)-NVL(V_EXCESS_HRS,0);
--DISP_ALERT('V_TOT_MIN IS NOW = '||TO_CHAR(V_TOT_MIN));
--

	IF NVL(V_TOT_HRS,0)>=NVL(V_EXCESS_HRS,0) THEN --IF THERE IS EXCESS HRS SO TOT OT WILL BE ALWAYS > EXCESS
		IF NVL(V_TOT_OT2_HRS,0)=0 THEN
	             V_TOT_OT1_HRS :=NVL(V_TOT_HRS,0)-NVL(V_EXCESS_HRS,0);
	             V_TOT_OT2_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE OT HR IN THIS CASE
		ELSE
		     V_TOT_OT2_HRS :=NVL(V_TOT_HRS,0)-NVL(V_EXCESS_HRS,0);
	             V_TOT_OT1_HRS :=0;--WHY ZERO? BECUZ WE CAN ONLY SET ONE OT HR IN THIS CASE
		END IF;
	ELSE NULL;
	END IF;

	--DAILY TOT HRS OF OT 
	V_TOT_HRS :=NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);
--DISP_ALERT('R_1_OT_HR_COST IS = '||TO_CHAR(R_1_OT_HR_COST)||
--' AND V_TOT_HRS IS '||TO_CHAR(V_TOT_HRS)||
--' V_EXCESS_HRS IS = '||TO_CHAR(V_EXCESS_HRS));

	--V_TOT_MIN :=NVL(V_TOT_HRS,0)+NVL(V_TOT_MIN,0);
R_DAY_AMT :=NVL(R_OT_HRS_AMT,0); --DAILY OT AMT CHANGED

---DAILY WEEK AMOUNT GETTING INCREASE CHANGED
IF NVL(R_DAY_AMT_X,0)>=NVL(R_EXCESS_OT_HRS_AMT,0) THEN --IF THERE IS ANY EXCESS AMT, SO IT MUST BE LESS THAN DAY X AMT, BECAUSE EXCESS AMT IS A PART OF THE DAY X AMT 
R_DAY_AMT_X :=NVL(R_DAY_AMT_X,0)-NVL(R_EXCESS_OT_HRS_AMT,0);
ELSE NULL;
END IF;
--DISP_ALERT('R_DAY_AMT IS CHANGEG TO HALF BASI WHICH = '||TO_CHAR(R_DAY_AMT)||
--' AND R_DAY_AMT_X IS CHANGED TO = '||TO_CHAR(R_DAY_AMT_X));

IF NVL(V_TOT_HRS,0)>0 THEN --INSERT ONLY WHEN THERE IS OT HRS
--DISP_ALERT('NOW INSERTING INTO OT DETAIL PROC TABLE');
		 INSERT INTO PT_OVERTIME_DETAIL_PROC
			(OTD_OTH_SYS_ID,OTD_SYS_ID ,            
			OTD_DT ,OTD_EMP_CODE ,          
			OTD_OT_CODE,OTD_OT1_HRS ,           
			OTD_OT2_HRS,OTD_COMP_CODE,          
			OTD_DEPT_CODE )
                 VALUES
			(K.OTD_OTH_SYS_ID,K.OTD_SYS_ID ,            
			K.OTD_DT ,K.OTD_EMP_CODE ,          
			K.OTD_OT_CODE,NVL(V_TOT_OT1_HRS,0) ,           
			NVL(V_TOT_OT2_HRS,0),K.OTD_COMP_CODE,          
			K.OTD_DEPT_CODE );
END IF;		         
	if EX IS NOT NULL then --EX for approved excess ot    
	null;
	else
		IF V_EXCESS_HRS>0 THEN
--DISP_ALERT('INSERTING INTO PT_OT_EXCESS_DUMMY TABLE');

            INSERT INTO PT_OT_EXCESS_DUMMY
                  (EO_DT,EO_EMP_CODE,EO_OT_CODE ,EO_EXCESS_OT1_HRS,EO_EXCESS_OT2_HRS)
            VALUES (K.OTD_DT,K.OTD_EMP_CODE,K.OTD_OT_CODE,NVL(V_EXCESS_OT1_HRS,0),NVL(V_EXCESS_OT2_HRS,0));
          	END IF;
	end if;

end if;
	END LOOP;--END OF REST MONTH DAYS LOOP
--X3 END


EXIT;--EXIT FROM THE WEEK FORM > TO LOOP
ELSE NULL;--R_LAST_DAY+7 <=M_OT_PROC_END_DT , SO COMPLETE THE LOOPING
--DISP_ALERT('R_LAST_DAY+7 <=M_OT_PROC_END_DT , SO COMPLETE THE LOOPING');
END IF;
END LOOP;--END OF THE WEEK FORM > TO LOOP
--DISP_ALERT('finsihed x1 and x2 and x3');


---+++++++++++++++++++++++++++++++++++++++++++++++++++++++**************NEW CODE 1 ADDED END


-----------------------------------------------start of comment CHECK ABOVE NEW CODE 1 >> ADDED TO REPLACE THIS ONE
/*
 --V_MAX_HRS :=70;
FOR I IN C_GET_OT_DETAIL
 LOOP-- start of first loop
  --MESSAGE(TO_CHAR(NVL(V_TOT_HRS,0)));PAUSE;
--MESSAGE(TO_CHAR(NVL(I.D_TOT_OT1_HRS,0)));PAUSE;
--MESSAGE(TO_CHAR(NVL(I.D_TOT_OT2_HRS,0)));PAUSE;
	
---->>next code will be not used becuase we will get excess hours from the amount of ot
    IF NVL(V_TOT_HRS,0)<V_MAX_HRS THEN---V_MAX_HRS is ot max amount from the parameter table
	IF NVL(V_TOT_HRS,0) +NVL(I.D_TOT_OT1_HRS,0)+NVL(I.D_TOT_OT2_HRS,0) <=V_MAX_HRS THEN
              V_TOT_OT1_HRS:=NVL(I.D_TOT_OT1_HRS,0);
              V_TOT_OT2_HRS:=NVL(I.D_TOT_OT2_HRS,0);
        ELSIF NVL(V_TOT_HRS,0) +NVL(I.D_TOT_OT1_HRS,0)+NVL(I.D_TOT_OT2_HRS,0) >V_MAX_HRS THEN
              V_EXCESS_HRS :=NVL(V_TOT_HRS,0) +NVL(I.D_TOT_OT1_HRS,0)+NVL(I.D_TOT_OT2_HRS,0)- V_MAX_HRS;
              IF NVL(I.D_TOT_OT2_HRS,0) >= NVL(V_EXCESS_HRS,0) THEN
                V_EXCESS_OT2_HRS:=NVL(V_EXCESS_HRS,0);
                V_EXCESS_OT1_HRS:=NVL(V_EXCESS_HRS,0)-NVL(V_EXCESS_OT2_HRS,0);

               
              ELSIF NVL(I.D_TOT_OT2_HRS,0) < NVL(V_EXCESS_HRS,0) THEN
                V_EXCESS_OT2_HRS:=NVL(I.D_TOT_OT2_HRS,0);
                V_EXCESS_OT1_HRS:=NVL(V_EXCESS_HRS,0)-NVL(V_EXCESS_OT2_HRS,0);
                
		             
	     END IF ;

               V_TOT_OT1_HRS:=NVL(I.D_TOT_OT1_HRS,0)-NVL(V_EXCESS_OT1_HRS,0);
               V_TOT_OT2_HRS:=NVL(I.D_TOT_OT2_HRS,0)-NVL(V_EXCESS_OT2_HRS,0);
               V_EXCESS_HRS:=NVL(V_EXCESS_OT2_HRS,0)+NVL(V_EXCESS_OT1_HRS,0);

        END IF;
		V_TOT_HRS :=NVL(V_TOT_HRS,0) +NVL(V_TOT_OT1_HRS,0) + NVL(V_TOT_OT2_HRS,0);
		 INSERT INTO PT_OVERTIME_DETAIL_PROC
			(OTD_OTH_SYS_ID,OTD_SYS_ID ,            
			OTD_DT ,OTD_EMP_CODE ,          
			OTD_OT_CODE,OTD_OT1_HRS ,           
			OTD_OT2_HRS,OTD_COMP_CODE,          
			OTD_DEPT_CODE )
                 VALUES
			(I.OTD_OTH_SYS_ID,I.OTD_SYS_ID ,            
			I.OTD_DT ,I.OTD_EMP_CODE ,          
			I.OTD_OT_CODE,NVL(V_TOT_OT1_HRS,0) ,           
			NVL(V_TOT_OT2_HRS,0),I.OTD_COMP_CODE,          
			I.OTD_DEPT_CODE );
			         

    ELSE
          V_EXCESS_OT2_HRS :=NVL(I.D_TOT_OT2_HRS,0);
	  V_EXCESS_OT1_HRS :=NVL(I.D_TOT_OT1_HRS,0);
          V_EXCESS_HRS :=NVL(V_EXCESS_OT2_HRS,0)+NVL(V_EXCESS_OT1_HRS,0);
   END IF;
--->>end of change.

 

--->>start of no changes here
if EX IS NOT NULL then --EX for approved excess ot    
null;
else
           IF V_EXCESS_HRS>0 THEN

      


            INSERT INTO PT_OT_EXCESS_DUMMY
                  (EO_DT,EO_EMP_CODE,EO_OT_CODE ,EO_EXCESS_OT1_HRS,EO_EXCESS_OT2_HRS)
            VALUES (I.OTD_DT,I.OTD_EMP_CODE,I.OTD_OT_CODE,NVL(V_EXCESS_OT1_HRS,0),NVL(V_EXCESS_OT2_HRS,0));
          END IF;

end if;
--->>end of no changes here

   END LOOP; --end of first loop  
  */
-----------------------------------------------end of comment

if EX IS NOT NULL then  --EX for approved excess ot   
null;
else 
 --DISP_ALERT('FOR EXCESS_OT IN C_GET_EXCESS_OT');--rajab added this     
  FOR EXCESS_OT IN C_GET_EXCESS_OT 
    LOOP--start of second loop

      ---FIND RATE V_OT_RATES
 V_OT_CODE:=EXCESS_OT.EO_OT_CODE;

                  OPEN  C_GET_OT_CODE_DETAILS(V_OT_CODE);--C_GET_OT_CODE_DETAILS to get ot main rules from PM_OVERTIME
      		FETCH C_GET_OT_CODE_DETAILS 
			INTO V_OT_RATE1      , V_OT_RATE2      ,
                             V_OT_EARN_CODE_1 , V_OT_EARN_CODE_2 ,
                             V_OT_MAX_HRS     , V_OT_MAX_AMOUNT  ,   
                             V_OT_MAX_PERC    ;

      IF C_GET_OT_CODE_DETAILS%NOTFOUND THEN --C_GET_OT_CODE_DETAILS to get ot main rules from PM_OVERTIME
         CLOSE C_GET_OT_CODE_DETAILS ;
         :M_REASON_FOR_NOT_PROCESSING := 'OT details are not available in PM_OVERTIME...';
         L_INSERT_REC_DURING_FAILURE(P_EMP_CODE);
      --*******************************
       --  RAISE UNABLE_TO_PROCESS_FOR_EMPLOYEE ;
      --**************************************
      END IF;
      CLOSE C_GET_OT_CODE_DETAILS ;--C_GET_OT_CODE_DETAILS to get ot main rules from PM_OVERTIME


      ---FIND BASIC TO V_OT_BASIC

  OPEN C_GET_BASIC_SAL_AMT (V_OT_CODE);
       FETCH C_GET_BASIC_SAL_AMT INTO M_OT_BASIC_LC_AMOUNT;
       IF C_GET_BASIC_SAL_AMT%NOTFOUND THEN
       CLOSE C_GET_BASIC_SAL_AMT ;
         :M_REASON_FOR_NOT_PROCESSING := 'Applicable OT Allowances are not defined in OT Allowances....' ;
         L_INSERT_REC_DURING_FAILURE(P_EMP_CODE);
       END IF;
        CLOSE C_GET_BASIC_SAL_AMT ;

------------------**************************************************
--------------------***************************************
          IF P_EMP_PAY_TYPE ='M' THEN
   		V_STD_WK_HRS :=:M_MONTHLY_STD_WKG_HOURS ;
          ELSIF P_EMP_PAY_TYPE ='D' THEN
		V_STD_WK_HRS :=:M_MONTHLY_STD_WKG_HOURS ;
          ELSIF P_EMP_PAY_TYPE ='H' THEN
                V_STD_WKG_HRS:=1;
          END IF;	

--MESSAGE(V_DEPT_CODE);PAUSE;
--MESSAGE(TO_CHAR(V_PROC_MTH));PAUSE;
--MESSAGE(TO_CHAR(V_OT_MTH));PAUSE;
--MESSAGE(TO_CHAR(EXCESS_OT.EXCESS_OT1_HRS));PAUSE;
--MESSAGE(TO_CHAR(EXCESS_OT.EXCESS_OT2_HRS));PAUSE;
--MESSAGE(TO_CHAR(NVL(EXCESS_OT.EXCESS_OT1_HRS,0)+NVL(EXCESS_OT.EXCESS_OT2_HRS,0)));PAUSE;

	INSERT INTO PT_OT_EXCESS_HEAD 
         (OEH_SYS_ID,OEH_DEPT_CODE,OEH_EMP_CODE,OEH_PROC_MTH,OEH_OT_MTH,
          OEH_OT_CODE,OEH_EXCESS_OT1_HRS,OEH_EXCESS_OT2_HRS,OEH_EXCESS_TOT_HRS,
           OEH_EXCESS_OT_RATE1,OEH_EXCESS_OT_RATE2,
	  OEH_OT_BASIC,OEH_EMP_PAY_TYPE ,
	  OEH_STD_WK_HRS,OEH_UNAPPR_OT1_HRS,OEH_UNAPPR_OT2_HRS,OEH_UNAPPR_TOT_HRS,
          OEH_CR_UID,OEH_CR_DT)
         VALUES
           (OEH_SYS_ID.NEXTVAL,V_DEPT_CODE,P_EMP_CODE,V_PROC_MTH,V_OT_MTH,
            EXCESS_OT.EO_OT_CODE,EXCESS_OT.EXCESS_OT1_HRS,EXCESS_OT.EXCESS_OT2_HRS,
		NVL(EXCESS_OT.EXCESS_OT1_HRS,0)+NVL(EXCESS_OT.EXCESS_OT2_HRS,0),
            V_OT_RATE1,V_OT_RATE2,M_OT_BASIC_LC_AMOUNT,P_EMP_PAY_TYPE,
            V_STD_WK_HRS,EXCESS_OT.EXCESS_OT1_HRS,EXCESS_OT.EXCESS_OT2_HRS,
		NVL(EXCESS_OT.EXCESS_OT1_HRS,0)+NVL(EXCESS_OT.EXCESS_OT2_HRS,0),
              'SYSTEM',SYSDATE)           ;
   END LOOP;--end of second loop


end if;
CLOSE OT_EX;
	:M_COMM_DEL := 'C' ;
       COMMIT ;  
       :M_COMM_DEL := '' ;
       --DISP_ALERT('SUCCESS L_CATEGORIZE_OT');---BY RAJAB
else null;
end if; --no ot data
 EXCEPTION
  WHEN OTHERS THEN
	--DISP_ALERT('ERRORS IN L_CATEGORIZE_OT');---BY RAJAB
        NULL;

 END;


                

	
      
 







 
         
Re: a procedure taking long time while running it [message #256925 is a reply to message #256913] Tue, 07 August 2007 03:11 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Throw it away and start from scratch?

If it's just ONE employee that takes a long time, check if something is wrong with the data of that employee.

As to make it faster :

- Replace cursor loops with direct SQLs when possible.
(As far as I see, the EMP_GRADE, R_GET_RAMADAN_FR, R_GET_RAMADAN_TO cursors for example could be scraped and replaced with single select into's)

- Try to reduce the number of cursors / loops.

- Change Cursor loops into bulk operations.

But when I look at the code, I think the best thing would be to dump it and start from scratch. Preferably by someone different than the person who wrote this.

Re: a procedure taking long time while running it [message #257218 is a reply to message #256925] Tue, 07 August 2007 15:47 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Of course the other idea is measure how long each block (you can define it yourself) takes time and tune the one who takes the longest time until you are satisfied with results.

P.S. I didn't look at the code, but I noticed that it is looooooong Smile

Gints Plivna
http://www.gplivna.eu
Re: a procedure taking long time while running it [message #257290 is a reply to message #257218] Wed, 08 August 2007 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not so long for the OP as he added a bunch of empty lines at the end. Laughing

Regards
Michel
Re: a procedure taking long time while running it [message #257328 is a reply to message #257290] Wed, 08 August 2007 01:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
To paraphrese Tom Kyte: the following procedure does the same thing and is faster:
begin
  null;
end;
WHEN OTHERS THEN NULL is a bad, very bad idea. It's the same like saying "I don't care what happens, and I don't care if anything happens at all. I don't want to know about it."

That's pointer number one.

MHE
Re: a procedure taking long time while running it [message #257333 is a reply to message #257328] Wed, 08 August 2007 02:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Pointer number two: It is forms client side PL/SQL. Why don't you do this on the database?

Pointer number three: Have a look at your cursors. Do you get the idea too that some cursors could be combined or parametrized?

MHE

[Updated on: Wed, 08 August 2007 02:09]

Report message to a moderator

Re: a procedure taking long time while running it [message #272954 is a reply to message #257333] Mon, 08 October 2007 06:16 Go to previous messageGo to next message
punithavel
Messages: 21
Registered: May 2007
Location: Chennai
Junior Member

I think you need to go for ONE TIME ONLY PROCEDURE
Re: a procedure taking long time while running it [message #272956 is a reply to message #272954] Mon, 08 October 2007 06:19 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And the reasoning behind this wise advise?
Previous Topic: ORA-02070: database does not support operator PLSFUN in this context
Next Topic: ora_sql_txt function errors
Goto Forum:
  


Current Time: Wed Dec 07 10:19:28 CST 2016

Total time taken to generate the page: 0.09901 seconds