-
procedure HELP
WORKING ON 9.1 OS:- WIN 2000
how do i write a procedure to calculate the approval date where
p_date date,p_num number (nos of days) are the parameters to be passed IN
for approval.(SHOULD BE ONLY WORKING DAYS)
CONDITIONS:-
1)Excluding Thursdays & Fridays
2)Holidays should be excluded, that is taken from a table called holiday_schedule
which comprises of from_dt,to_dt columns.
3)if the approval_date again encounters a public holiday from holiday_schedule or a Thursday/Friday
then add up number of days to the next approval_day.
could finish off with the first case
CREATE OR REPLACE PROCEDURE proc_appr_day
(P_CREATED_DT IN DATE,
P_APPR_DAYS IN NUMBER)
IS
V_APPR_DT DATE := TO_CHAR(P_CREATED_DT);
V_COUNT NUMBER(3) := 0 ;
v_abc DATE;
BEGIN
V_APPR_DT := P_CREATED_DT +1;
FOR i IN 1..P_APPR_DAYS LOOP
IF TO_CHAR(V_APPR_DT,'DY') = 'THU' THEN
V_COUNT:= V_COUNT+1;
ELSE IF TO_CHAR(V_APPR_DT,'DY') = 'FRI' THEN
V_COUNT := V_COUNT +1;
END IF;
END IF;
V_APPR_DT := V_APPR_DT +1;
END LOOP;
V_ABC :=V_APPR_DT + V_COUNT;
DBMS_OUTPUT.PUT_LINE(V_ABC);
END;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|