CREATE OR REPLACE FUNCTION Get_Eff_Alloc_Percent_170709(EMPID VARCHAR2, ORCODE VARCHAR2, PROJECTID VARCHAR2, SITE VARCHAR2, BILLABLE VARCHAR2, FMDT DATE, TODT DATE)
RETURN NUMBER IS
 LV_EFF_ALLOC_PCNT NUMBER;
 LV_EMPID VARCHAR2(6);
 LV_FMDT DATE;
 LV_TODT DATE;
 LV_ORCODE VARCHAR2(12);
 LV_PROJECTCODE VARCHAR2(40);
 LV_PROJECTID VARCHAR2(40);
 LV_SITE VARCHAR2(40);
 LV_BILLABLE VARCHAR2(1);
 LV_TEMP VARCHAR2(4000);
BEGIN

	 LV_FMDT := FMDT;
	 LV_TODT := TODT;
	 LV_EMPID := UPPER(EMPID);
	 LV_ORCODE := ORCODE;
	 LV_PROJECTID := PROJECTID;
	 LV_SITE := SITE;
	 LV_BILLABLE := BILLABLE;

	SELECT (CASE WHEN LV_ORCODE <> '' THEN PRJ.ORDERID
		   		WHEN LV_PROJECTID <> '' THEN ALLOC.PROJECTID
		    END) PROJECT,
		   SUM(ROUND(
	       		 	 (
	   	 		  	  TO_DATE(LEAST(LV_TODT,NVL(LEAST(LV_TODT,NVL(CASE
  	  WHEN (TCOL5 = 'Available') AND EXISTS (SELECT 1 FROM TBLRESMGMT WHERE PREVIDKEY = ALLOC.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking')) THEN
	  	   (SELECT TO_CHAR(ACTUALSTARTDATE - 1,'DD-MON-YYYY') FROM TBLRESMGMT WHERE PREVIDKEY = ALLOC.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking'))
	  ELSE
	  	   TO_CHAR(ALLOC.ACTUALENDDATE,'DD-MON-YYYY')
  END,LV_TODT)),LV_TODT)))+1 -
	     		  	  TO_DATE(GREATEST(LV_FMDT,ALLOC.ACTUALSTARTDATE))
	   			 	 )/DAYS.TOTAL_DAYS
	    		 	 * NVL(ALLOC.PERCENTALLOCATION,100)
	   				,6)
			  )
			  INTO LV_PROJECTCODE, LV_EFF_ALLOC_PCNT
	FROM TBLRESMGMT ALLOC,
		 TBLPROJECTMASTER PRJ,
	 	 (
	 	 SELECT RES.CODE,
	   	 		SUM(
	   				 (
	   	 			  TO_DATE(LEAST(LV_TODT,NVL(LEAST(LV_TODT,NVL(CASE
  	  WHEN (TCOL5 = 'Available') AND EXISTS (SELECT 1 FROM TBLRESMGMT WHERE PREVIDKEY = res.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking')) THEN
	  	   (SELECT TO_CHAR(ACTUALSTARTDATE - 1,'DD-MON-YYYY') FROM TBLRESMGMT WHERE PREVIDKEY = res.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking'))
	  ELSE
	  	   TO_CHAR(RES.ACTUALENDDATE,'DD-MON-YYYY')
  END,LV_TODT)),LV_TODT)))+1 -
	     			  TO_DATE(GREATEST(LV_FMDT,RES.ACTUALSTARTDATE))
	   				 ) * NVL(RES.PERCENTALLOCATION,100)/100
	      		   ) TOTAL_DAYS
         FROM TBLRESMGMT RES
	 	 WHERE RES.CODE = LV_EMPID
		 AND RES.TYPE = 'EMPLOYEE'
		 AND UPPER(RES.TCOL5) IN ('ALLOCATED','AVAILABLE','EARMARKED', 'REMOVED EARMARKING','AVAILABLE EARMARKED')
	 	 AND TO_DATE(LV_FMDT) <= TO_DATE(NVL(RES.ACTUALENDDATE,LV_TODT))
	 	 AND TO_DATE(LV_TODT) >= RES.ACTUALSTARTDATE
and (TO_DATE(LEAST(to_date(LV_TODT),NVL(least(to_date(LV_TODT),NVL(CASE
  	  WHEN (TCOL5 = 'Available') AND EXISTS (SELECT 1 FROM TBLRESMGMT WHERE PREVIDKEY = res.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking')) THEN
	  	   (SELECT TO_CHAR(ACTUALSTARTDATE - 1,'DD-MON-YYYY') FROM TBLRESMGMT WHERE PREVIDKEY = res.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking'))
	  ELSE
	  	   TO_CHAR(res.ACTUALENDDATE,'DD-MON-YYYY')
  END,to_date(LV_TODT))),to_date(LV_TODT))))) >= to_date(LV_FMDT)
	 	 GROUP BY RES.CODE
	    ) DAYS
	WHERE ALLOC.CODE = DAYS.CODE
	AND ALLOC.PROJECTID = PRJ.PROJECTID
	AND DECODE(LV_PROJECTID,'',PRJ.ORDERID,ALLOC.PROJECTID) = DECODE(LV_ORCODE,'',LV_PROJECTID,LV_ORCODE)
	AND ALLOC.TYPE = 'EMPLOYEE'
	AND UPPER(ALLOC.TCOL5) IN ('ALLOCATED','AVAILABLE','EARMARKED', 'REMOVED EARMARKING','AVAILABLE EARMARKED')
	AND TO_DATE(LV_FMDT) <= TO_DATE(NVL(ACTUALENDDATE,LV_TODT))
	AND TO_DATE(LV_TODT) >= ACTUALSTARTDATE
	AND DECODE(LV_SITE,'',NVL(ALLOC.TCOL3,'-'),LV_SITE) = NVL(ALLOC.TCOL3,'-')
	AND DECODE(LV_BILLABLE,'',NVL(ALLOC.FLAG1,'-'),LV_BILLABLE) = NVL(ALLOC.FLAG1,'-')
and (TO_DATE(LEAST(to_date(LV_TODT),NVL(least(to_date(LV_TODT),NVL(CASE
  	  WHEN (TCOL5 = 'Available') AND EXISTS (SELECT 1 FROM TBLRESMGMT WHERE PREVIDKEY = alloc.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking')) THEN
	  	   (SELECT TO_CHAR(ACTUALSTARTDATE - 1,'DD-MON-YYYY') FROM TBLRESMGMT WHERE PREVIDKEY = alloc.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking'))
	  ELSE
	  	   TO_CHAR(alloc.ACTUALENDDATE,'DD-MON-YYYY')
  END,to_date(LV_TODT))),to_date(LV_TODT))))) >= to_date(LV_FMDT)
	GROUP BY (CASE WHEN LV_ORCODE <> '' THEN PRJ.ORDERID
		   		  WHEN LV_PROJECTID <> '' THEN ALLOC.PROJECTID
		   	  END)
	;

RETURN(LV_EFF_ALLOC_PCNT);

EXCEPTION WHEN OTHERS THEN
	RETURN 0;
END Get_Eff_Alloc_Percent_170709;
/