CREATE OR REPLACE FUNCTION APPS.SGDF_WORK_DAY( START_DATE IN DATE ,END_DATE IN DATE )
RETURN NUMBER IS DAY_COUNT NUMBER :=0 ;
BEGIN
DECLARE V_DAy NUMBER :=0;
v_start_date date ;
begin
v_start_date:=start_date ;
while v_start_date <= end_date loop
SELECT TO_CHAR(v_start_date,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') and (v_day!='7') THEN
DAY_COUNT := DAY_COUNT +1 ;
END IF ;
v_start_date :=v_start_date + 1 ;
end loop;
RETURN(DAY_COUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '0';
end;
END ;
/*, ATTRIBUTE16,ATTRIBUTE13 */
select ORDER_NUMBER
,to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'),TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')
, apps.SGDF_WORK_DAY(to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'), TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')) AS DAYCOUNT
from ont.oe_order_headers_all
where ORDER_NUMBER LIKE '6010017__'
RETURN NUMBER IS DAY_COUNT NUMBER :=0 ;
BEGIN
DECLARE V_DAy NUMBER :=0;
v_start_date date ;
begin
v_start_date:=start_date ;
while v_start_date <= end_date loop
SELECT TO_CHAR(v_start_date,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') and (v_day!='7') THEN
DAY_COUNT := DAY_COUNT +1 ;
END IF ;
v_start_date :=v_start_date + 1 ;
end loop;
RETURN(DAY_COUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '0';
end;
END ;
/*, ATTRIBUTE16,ATTRIBUTE13 */
select ORDER_NUMBER
,to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'),TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')
, apps.SGDF_WORK_DAY(to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'), TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')) AS DAYCOUNT
from ont.oe_order_headers_all
where ORDER_NUMBER LIKE '6010017__'