Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2011
    Posts
    38

    Unanswered: how to call function in the procedure with out parameter...

    hi team....
    i have a function findmonday...
    the code of this function is given below...

    Start of function(findmonday)...

    create or replace function findmonday
    (in_LVTYPCODE IN varchar2,in_FRDATE date,in_EMPCODE varchar2)
    Return number
    IS
    monnum number;
    FRDATE date;
    vis date;
    ddate varchar2(20);
    ddate1 date;
    ddate2 varchar2(20);
    BEGIN
    FRDATE := TRUNC(in_FRDATE);
    dbms_output.put_line('ssss5date' ||FRDATE);
    vis:=to_date(rpad(to_char(FRDATE,'YYYY/mm/dd'),10,''),'YYYY/mm/dd');
    dbms_output.put_line('ssss5vis' ||vis);
    if in_LVTYPCODE = 'LTM-000001' then
    ddate :=to_char(in_FRDATE,'DAY');
    dbms_output.put_line('ssss1' ||ddate);
    if trim(ddate) = 'MONDAY' then
    BEGIN
    dbms_output.put_line('hello');

    SELECT ELTTODT into ddate1 FROM EMP_LEAVE_TRANS
    WHERE ELTEMCODE = in_EMPCODE
    AND
    ROWID=(SELECT MAX(ROWID) FROM EMP_LEAVE_TRANS WHERE
    ELTEMCODE = in_EMPCODE) order by ELTCODE;

    dbms_output.put_line('ssss2' ||ddate1);

    dbms_output.put_line('ssss3diff' ||(FRDATE-ddate1));

    if FRDATE-ddate1= 3 then
    ddate2 :=to_char(ddate1,'DAY');
    dbms_output.put_line('ssss3' ||ddate2);

    if trim(ddate2) = 'FRIDAY' then
    dbms_output.put_line('ssss4' ||ddate2);
    monnum :=2;
    end if;
    end if;
    END;
    end if;
    end if;
    return monnum;
    END;


    End of function(findmonday)...

    and i have a function SP_leaveapplication and i m calling this function(findmonday) on this procedure(sp_leaveapplication) but the out put of the function is not comming....
    my function and my procedure have no error...
    the code of procedure is.....


    --Start of Procedure code

    CREATE OR REPLACE PROCEDURE SP_LEAVEAPPLICATION(in_ExecutionType IN VARCHAR2 DEFAULT 'APL',
    in_EMPCODE IN VARCHAR2 DEFAULT NULL,
    in_ELTAPPLYEMCODE IN VARCHAR2 DEFAULT NULL,
    in_LCODE IN VARCHAR2 DEFAULT '',
    in_LVTYPCODE IN VARCHAR2 DEFAULT '',
    in_FRDATE IN DATE DEFAULT NULL,
    in_TODATE IN DATE DEFAULT NULL,
    in_LeaveFromDateType IN NUMBER DEFAULT 0,
    in_LeaveToDateType IN NUMBER DEFAULT 0,
    in_REASON IN VARCHAR2 DEFAULT '',
    in_ADDONLEAVE IN VARCHAR2 DEFAULT '',
    in_CTCPERSOFFPH IN VARCHAR2 DEFAULT '',
    TotalDays OUT NUMBER,
    BalanceLeave OUT NUMBER,
    NoRecordsInEmpLvDet OUT NUMBER,
    in_LeaveMaxdays IN NUMBER DEFAULT 0,
    in_Mode_ IN VARCHAR2 DEFAULT 'I',
    in_RSHLDYCHKFLAG IN NUMBER DEFAULT 0,
    in_UPDATEFLAG IN NUMBER DEFAULT 0,
    in_ASSIGNCHARGE IN VARCHAR2 DEFAULT '',
    in_CommuteLV IN VARCHAR2 DEFAULT NULL,
    in_DoctorCertProd VARCHAR2 DEFAULT NULL,
    in_child IN NUMBER DEFAULT 0,
    in_bthof IN DATE DEFAULT NULL
    )AS
    ExecutionType VARCHAR2(10);
    EMPCODE VARCHAR2(20);
    ELTAPPLYEMCODE VARCHAR2(20);
    LCODE VARCHAR2(20);
    LVTYPCODE VARCHAR2(20);
    FRDATE DATE;
    TODATE DATE;
    LeaveFromDateType NUMBER(18, 0);
    LeaveToDateType NUMBER(18, 0);
    REASON VARCHAR2(255);
    ADDONLEAVE VARCHAR2(200);
    CTCPERSOFFPH VARCHAR2(30);
    LeaveMaxdays NUMBER(18, 0);
    Mode_ VARCHAR2(1);
    RSHLDYCHKFLAG NUMBER(10, 0);
    UPDATEFLAG NUMBER(10, 0);
    ASSIGNCHARGE VARCHAR2(30);
    StoO_selcnt INTEGER;
    StoO_error INTEGER;
    StoO_rowcnt INTEGER;
    StoO_crowcnt INTEGER := 0;
    StoO_fetchstatus INTEGER := 0;
    StoO_errmsg VARCHAR2(255);
    StoO_sqlstatus INTEGER;
    REGSTATUS VARCHAR2(20);
    CANCELSTATUS VARCHAR2(20);
    APPRSTATNO VARCHAR2(20);
    APPRSTATYES VARCHAR2(20);
    APPRCANSTATYES VARCHAR2(50);
    CRITAPPL1 NUMBER(1, 0);
    CRITAPPL2 NUMBER(1, 0);
    ULOGID VARCHAR2(20);
    LVEXCLHOLIDAY NUMBER(10, 0);
    FNAME VARCHAR2(25);
    APPRID VARCHAR2(20);
    LKPATHSTR VARCHAR2(50);
    JDATE DATE;
    ANNVDATE DATE;
    BTDATE DATE;
    NO_OF_DAYS NUMBER(10, 0);
    ACTNOOFDAYS NUMBER(10, 2);
    NONWORKINGDAYS NUMBER(3, 2);
    LTMMAXLVDAYS NUMBER(9, 0);
    UNITCODE VARCHAR2(10);
    APPDATE DATE;
    MODID VARCHAR2(10);
    HOLIDAY NUMBER(10, 2);
    WMCODE VARCHAR2(20);
    WMFRDAY NUMBER(10, 0);
    WMTODAY NUMBER(10, 0);
    WMTODAYFLAG NUMBER(10, 0);
    CURDAY NUMBER(10, 0);
    TEMPDATE VARCHAR2(10);
    TEMPADATE DATE;
    BDATE DATE;
    ENDDATE DATE;
    NUMSUN NUMBER(10, 0);
    NUMSAT NUMBER(10, 0);
    FRMHALFDAY NUMBER(10, 0);
    TOHALFDAY NUMBER(10, 0);
    FMTODAYFLAG NUMBER(10, 0);
    mSUNCOUNT NUMBER(10, 0);
    CALBDT DATE;
    CALEDT DATE;
    FRMWEEKEND NUMBER(10, 0);
    TOWEEKEND NUMBER(10, 0);
    EMPLVDET VARCHAR2(20);
    LTMNEGFLAG NUMBER(10, 0);
    WORKFROM NUMBER(10, 0);
    WORKTO NUMBER(10, 0);
    FDATE VARCHAR2(10);
    TDATE VARCHAR2(10);
    currresthldys NUMBER(10, 0);
    usedrhsthldys NUMBER(9, 3);
    tempresthldys NUMBER(9, 3);
    appliedhldys NUMBER(9, 3);
    TEMPDAYS NUMBER(9, 3);
    temp1 NUMBER(10, 2);
    pltaken NUMBER(10, 2);
    elclcheck NUMBER(10, 2);
    clelcheck NUMBER(10, 2);
    checkres NUMBER(10, 2);
    mNOOFDAYS NUMBER(10, 0);
    TempDt NUMBER;
    CMMCODE VARCHAR2(20);
    countsick NUMBER(10, 2);
    repeat NUMBER;
    LLeaveMaxdays NUMBER;
    temptaken Number;
    temptype VARCHAR2(20);
    LTMSRTNAME VARCHAR2(20);
    LTMNAME VARCHAR2(20);
    ltm_name VARCHAR2(20);
    ltmsrt_name VARCHAR2(20);
    location_state VARCHAR2(50);
    LocmCode VARCHAR2(20);
    wfromdate number;
    wtodate number;
    test number;
    test1 number;
    totdays number;
    hcounter number;
    ncount number;
    myvar varchar2(20);
    ltmbaldays_ number;
    indHoliday number;
    CommutLeav VARCHAR2(20);
    commuteLVCode VARCHAR2(20) DEFAULT 'LTM-000009';
    doctCertPro VARCHAR2(2);
    elHoldayFrcount NUMBER;
    elHoldayTocount NUMBER;
    elCountDays NUMBER;
    chkHolFrCount NUMBER;
    chkHolToCount NUMBER;
    startholidaycount NUMBER;
    startsundaycount NUMBER;
    startsataurdaycount NUMBER;
    totalleavedays NUMBER;

    curdate date;
    elnumber NUMBER := 0;
    rhcount NUMBER := 0;
    tempCnt NUMBER := 0;
    daysrhcount NUMBER := 0;
    tabrhdyas NUMBER := 0;
    resrhcount NUMBER := 0;
    child NUMBER := 0;
    bthof date;
    countprnt NUMBER := 0;
    countmaternity NUMBER := 0;
    mondaycount VARCHAR2(20);
    Lvtypecode_in VARCHAR2(20);
    fromdate_in date;
    totalmon NUMBER;



    BEGIN
    dbms_output.put_line('vishwas1'||in_LVTYPCODE);
    dbms_output.put_line('vishwas2'||in_FRDATE);
    dbms_output.put_line('vishwas3'||in_FRDATE);
    select findmonday(Lvtypecode_in,fromdate_in,EMPCODE) into TotalDays from dual;
    dbms_output.put_line('vishwas4'||TotalDays);

    END;


    --End of Procedure code


    can some one tell me what is the problem in the my code...

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You have tables & DDL. We don't
    You have data & DML. We don't.
    You have code. We don't.
    You have error message. We don't.
    You have requirements. We don't.

    You have ALL the information & can't debug the problem.
    Why do you expect others, who have NO details, to solve your mystery?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you SET SERVEROUTPUT ON (whichever tool you might be using)?

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by vishwas View Post
    and i have a function SP_leaveapplication and i m calling this function(findmonday) on this procedure(sp_leaveapplication) but the out put of the function is not comming....
    Commmmmmming where??????? If it they "have no error" (whatever it means), the function at least returns something. The code sends it (with many other debug information) to DBMS_OUTPUT buffer. Did you enable it by setting SERVEROUTPUT ON? If not, you cannot see anything. What about setting it on? If so, what was the output for some given parameter values?
    Quote Originally Posted by vishwas View Post
    can some one tell me what is the problem in the my code...
    From the first look, it is not formatted, declares too many unused variables and depends on NLS_DATE_LANGUAGE setting.

  5. #5
    Join Date
    Jul 2011
    Posts
    38
    Quote Originally Posted by flyboy View Post
    Commmmmmming where??????? If it they "have no error" (whatever it means), the function at least returns something. The code sends it (with many other debug information) to DBMS_OUTPUT buffer. Did you enable it by setting SERVEROUTPUT ON? If not, you cannot see anything. What about setting it on? If so, what was the output for some given parameter values?
    From the first look, it is not formatted, declares too many unused variables and depends on NLS_DATE_LANGUAGE setting.
    sir i want to say that when i compile my function and run it on sql+ console it run properly and show out put according to my condition and when ever i used this function into the my procedure (sp_leaveapplication),this function(findmonday) is not return any value...in the procedure.
    but when ever i create another procedure without out parameter it ,function (findmonday) run properly...
    for example i create another procedure such as vishwas the code is given below...
    ---code

    create or replace procedure vishwas(Lvtypecode_in IN varchar2,
    fromdate_in date,EMPCODE varchar2) AS

    totalmon number;

    BEGIN

    select findmonday(Lvtypecode_in,fromdate_in,EMPCODE) into totalmon from dual;



    dbms_output.put_line('vishwas2223'||totalmon);
    RETURN;
    END;


    --end of code


    and it show all the value in sql+
    out put is....

    SQL> EXEC vishwas('LTM-000001',to_date('2011-07-25 12:00:00','YYYY-mm-dd HH:MIS'),'ICFRE/10101011')
    ssss5date25-JUL-11
    ssss5vis
    ssss1MONDAY
    hello
    ssss222-JUL-11
    ssss3diff3
    ssss3FRIDAY
    ssss4FRIDAY
    vishwas22232



    but when ever i used sp_leaveapplication procedure it not show any value...
    and my all the value of procedure is comming from the application.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by vishwas View Post
    sir i want to say that when i compile my function and run it on sql+ console it run properly and show out put according to my condition and when ever i used this function into the my procedure (sp_leaveapplication),this function(findmonday) is not return any value...in the procedure.
    Does it mean, that the OUT parameter has NULL value after its call? If so, the problem is in the OUT parameter and assigning (or retrieving) its value. Why did you not post its code?
    Quote Originally Posted by vishwas View Post
    but when ever i used sp_leaveapplication procedure it not show any value...
    and my all the value of procedure is comming from the application.
    Or, maybe, if called from different environment (whatever the "application" is), the problem may be in its NLS_DATE_LANGUAGE setting (e.g. in German language, MONDAY is named MONTAG, FRIDAY is named FREITAG etc.).
    Last edited by flyboy; 07-27-11 at 05:18. Reason: Fix of the last quote tag

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Just to illustrate the last point:
    Code:
    SQL> SELECT to_char(SYSDATE,'DAY','nls_date_language=german') today_german
      2       , to_char(SYSDATE,'DAY','nls_date_language=english') today_english
      3       , to_char(SYSDATE,'DAY','nls_date_language=french') today_french
      4       , to_char(SYSDATE,'DAY','nls_date_language=spanish') today_spanish
      5  FROM DUAL;
    
    TODAY_GERMAN                             TODAY_ENGLISH
    ---------------------------------------- ------------------------------------
    TODAY_FRENCH                     TODAY_SPANISH
    -------------------------------- ------------------------------------
    MITTWOCH                                 WEDNESDAY
    MERCREDI                         MIÉRCOLES
    As you use this function in FINDMONDAY without the last parameter, it takes the default session one. So, the value may differ as the output shows.

  8. #8
    Join Date
    Jul 2011
    Posts
    38
    sir,
    there is any technique to debug the procedure line by line.
    i m using oracle 10g.
    and sir my procedure containe 180000 line so it is not able to post i m posting some part of code.

    code-------
    CREATE OR REPLACE PROCEDURE SP_LEAVEAPPLICATION
    BEGIN
    SP_LEAVEAPPLICATION.checkres := 0;
    SP_LEAVEAPPLICATION.clelcheck := 0;
    SP_LEAVEAPPLICATION.elclcheck := 0;
    SP_LEAVEAPPLICATION.currresthldys := 0;
    SP_LEAVEAPPLICATION.usedrhsthldys := 0;
    SP_LEAVEAPPLICATION.tempresthldys := 0;
    SP_LEAVEAPPLICATION.appliedhldys := 0;
    SP_LEAVEAPPLICATION.ExecutionType := SP_LEAVEAPPLICATION.in_ExecutionType;
    SP_LEAVEAPPLICATION.EMPCODE := SP_LEAVEAPPLICATION.in_EMPCODE;
    SP_LEAVEAPPLICATION.ELTAPPLYEMCODE := SP_LEAVEAPPLICATION.in_ELTAPPLYEMCODE;
    SP_LEAVEAPPLICATION.LCODE := SP_LEAVEAPPLICATION.in_LCODE;
    SP_LEAVEAPPLICATION.LVTYPCODE := SP_LEAVEAPPLICATION.in_LVTYPCODE;
    SP_LEAVEAPPLICATION.FRDATE := TRUNC(SP_LEAVEAPPLICATION.in_FRDATE);
    SP_LEAVEAPPLICATION.TODATE := TRUNC(SP_LEAVEAPPLICATION.in_TODATE);
    SP_LEAVEAPPLICATION.LeaveFromDateType := SP_LEAVEAPPLICATION.in_LeaveFromDateType;
    SP_LEAVEAPPLICATION.LeaveToDateType := SP_LEAVEAPPLICATION.in_LeaveToDateType;
    SP_LEAVEAPPLICATION.REASON := SP_LEAVEAPPLICATION.in_REASON;
    SP_LEAVEAPPLICATION.ADDONLEAVE := SP_LEAVEAPPLICATION.in_ADDONLEAVE;
    SP_LEAVEAPPLICATION.CTCPERSOFFPH := SP_LEAVEAPPLICATION.in_CTCPERSOFFPH;
    SP_LEAVEAPPLICATION.LeaveMaxdays := SP_LEAVEAPPLICATION.in_LeaveMaxdays;
    SP_LEAVEAPPLICATION.Mode_ := SP_LEAVEAPPLICATION.in_Mode_;
    SP_LEAVEAPPLICATION.RSHLDYCHKFLAG := SP_LEAVEAPPLICATION.in_RSHLDYCHKFLAG;
    SP_LEAVEAPPLICATION.UPDATEFLAG := SP_LEAVEAPPLICATION.in_UPDATEFLAG;
    SP_LEAVEAPPLICATION.ASSIGNCHARGE := SP_LEAVEAPPLICATION.in_ASSIGNCHARGE;

    BEGIN
    --Flag for frdate or todate falling on holiday
    SP_LEAVEAPPLICATION.FMTODAYFLAG := 0;

    -- Convert passed from date into required format
    SP_LEAVEAPPLICATION.FDATE := TO_DATE(RPAD(TO_CHAR(SP_LEAVEAPPLICATION.FRDATE,
    'yyyy/mm/dd'),
    10,
    ' '),
    'yyyy/mm/dd');

    --Convert passed to date into required format
    SP_LEAVEAPPLICATION.TDATE := TO_DATE(RPAD(TO_CHAR(SP_LEAVEAPPLICATION.TODATE,
    'yyyy/mm/dd'),
    10,
    ' '),
    'yyyy/mm/dd');




    IF SP_LEAVEAPPLICATION.LVTYPCODE = 'LTM-000001' THEN
    --RAISE_APPLICATION_ERROR(-20090,'EARNED BLOCK--');
    SP_LEAVEAPPLICATION.NoRecordsInEmpLvDet := SP_LEAVEAPPLICATION.CURDAY;
    BEGIN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error := 0;
    elHoldayFrcount := 0;
    elHoldayTocount := 0;
    elCountDays := 0;
    chkHolFrCount := 0;
    chkHolToCount := 0;

    SELECT WM.WMFRDAY, WM.WMTODAY, WM.WMTODAYFLAG
    INTO SP_LEAVEAPPLICATION.WMFRDAY,
    SP_LEAVEAPPLICATION.WMTODAY,
    SP_LEAVEAPPLICATION.WMTODAYFLAG
    FROM WORKHOUR_MSTR WM
    WHERE WMCODE = SP_LEAVEAPPLICATION.WMCODE;
    StoO_rowcnt := SQL%ROWCOUNT;

    EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    StoO_rowcnt := 2;
    WHEN NO_DATA_FOUND THEN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    WHEN OTHERS THEN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error := SQLCODE;
    StoO_errmsg := SQLERRM;
    END;

    SP_LEAVEAPPLICATION.BDATE := SP_LEAVEAPPLICATION.FRDATE;
    SP_LEAVEAPPLICATION.ENDDATE := SP_LEAVEAPPLICATION.TODATE;
    SP_LEAVEAPPLICATION.curdate1 := SP_LEAVEAPPLICATION.FRDATE;

    SP_LEAVEAPPLICATION.totalleavedays := (SP_LEAVEAPPLICATION.TODATE -
    SP_LEAVEAPPLICATION.FRDATE) + 1;

    IF SP_LEAVEAPPLICATION.totalleavedays = 1 THEN
    SP_LEAVEAPPLICATION.elnumber := 0;
    -- RAISE_APPLICATION_ERROR(-20090,'Earn BLOCK--'||SP_LEAVEAPPLICATION.elnumber);

    select COUNT(*)
    INTO rhcount
    from holiday_mstr
    WHERE HMDT = SP_LEAVEAPPLICATION.TDATE
    AND hmreshldyflag = 1;
    IF rhcount = 1 THEN
    SP_LEAVEAPPLICATION.elnumber := 0;
    END IF;
    END IF;

    IF SP_LEAVEAPPLICATION.totalleavedays <> 1 THEN


    FOR i in 0 .. SP_LEAVEAPPLICATION.totalleavedays - 1 LOOP
    SP_LEAVEAPPLICATION.startholidaycount := 0;
    SP_LEAVEAPPLICATION.startsundaycount := 0;
    SP_LEAVEAPPLICATION.startsataurdaycount := 0;
    SP_LEAVEAPPLICATION.curdate := SP_LEAVEAPPLICATION.FRDATE + i;

    IF (to_char(SP_LEAVEAPPLICATION.curdate, 'DAY') like
    '%SATURDAY%') THEN
    SP_LEAVEAPPLICATION.startsataurdaycount := 1;
    END IF;

    IF (to_char(SP_LEAVEAPPLICATION.curdate, 'DAY') like
    '%SUNDAY%') THEN
    SP_LEAVEAPPLICATION.startsundaycount := 1;
    END IF;

    select count(*)
    INTO SP_LEAVEAPPLICATION.startholidaycount
    from HOLIDAY_MSTR
    WHERE HMDT = TO_DATE(SP_LEAVEAPPLICATION.curdate)
    AND ((hmlocation = 'ALL') );

    exit when SP_LEAVEAPPLICATION.startholidaycount = 0 and SP_LEAVEAPPLICATION.startsundaycount = 0 and SP_LEAVEAPPLICATION.startsataurdaycount = 0;

    SP_LEAVEAPPLICATION.elnumber := SP_LEAVEAPPLICATION.elnumber + 1;
    END LOOP;

    FOR i in 0 .. SP_LEAVEAPPLICATION.totalleavedays - 1 LOOP
    SP_LEAVEAPPLICATION.startholidaycount := 0;
    SP_LEAVEAPPLICATION.startsundaycount := 0;
    SP_LEAVEAPPLICATION.startsataurdaycount := 0;
    SP_LEAVEAPPLICATION.curdate := SP_LEAVEAPPLICATION.TODATE - i;

    IF (to_char(SP_LEAVEAPPLICATION.curdate, 'DAY') like
    '%SATURDAY%') THEN
    SP_LEAVEAPPLICATION.startsataurdaycount := 1;
    END IF;

    IF (to_char(SP_LEAVEAPPLICATION.curdate, 'DAY') like
    '%SUNDAY%') THEN
    SP_LEAVEAPPLICATION.startsundaycount := 1;
    END IF;

    exit when SP_LEAVEAPPLICATION.startholidaycount = 0 and SP_LEAVEAPPLICATION.startsundaycount = 0 and SP_LEAVEAPPLICATION.startsataurdaycount = 0;

    SP_LEAVEAPPLICATION.elnumber := SP_LEAVEAPPLICATION.elnumber + 1;
    END LOOP;

    select COUNT(*)
    INTO rhcount
    from holiday_mstr
    WHERE HMDT = SP_LEAVEAPPLICATION.TDATE
    AND hmreshldyflag = 1;
    IF rhcount = 1 THEN
    SP_LEAVEAPPLICATION.elnumber := SP_LEAVEAPPLICATION.elnumber - 1;
    END IF;
    END IF;

    END IF;




    END;
    END IF; -- end of 'APL' IF

    select emlocmcode
    into LocmCode
    from emp_mstr
    where emcode = SP_LEAVEAPPLICATION.EMPCODE;
    select locmstate
    into location_state
    from LOCATION_MSTR
    where LOCMCODE = SP_LEAVEAPPLICATION.LocmCode;

    /* Getting the actual no of days to be deducted */
    -- For Casula Leave
    IF SP_LEAVEAPPLICATION.LVTYPCODE = 'LTM-000002' THEN
    SP_LEAVEAPPLICATION.ACTNOOFDAYS := ROUND(TO_NUMBER(SP_LEAVEAPPLICATION.NO_OF_DAYS),
    2) -
    SP_LEAVEAPPLICATION.NUMSAT;
    END IF;
    -- For Restriced Holidays
    IF SP_LEAVEAPPLICATION.LVTYPCODE = 'LTM-000003' THEN

    SP_LEAVEAPPLICATION.ACTNOOFDAYS := ROUND(TO_NUMBER(SP_LEAVEAPPLICATION.NO_OF_DAYS),
    2) -
    SP_LEAVEAPPLICATION.NUMSAT;
    -- RAISE_APPLICATION_ERROR(-20090,'TOTAL ______________DAYS--'||SP_LEAVEAPPLICATION.NUMSAT||'Actual days'||SP_LEAVEAPPLICATION.ACTNOOFDAYS);
    END IF;
    --For Special Casula Leave
    IF SP_LEAVEAPPLICATION.LVTYPCODE = 'LTM-000005' THEN

    SP_LEAVEAPPLICATION.ACTNOOFDAYS := ROUND(TO_NUMBER(SP_LEAVEAPPLICATION.NO_OF_DAYS),
    2) -
    SP_LEAVEAPPLICATION.NUMSAT;
    END IF;

    --For Earned Leave
    IF SP_LEAVEAPPLICATION.LVTYPCODE = 'LTM-000001' THEN
    -- RAISE_APPLICATION_ERROR(-20090,'TOTAL ______________DAYS--'||SP_LEAVEAPPLICATION.elnumber||'Actual days'||SP_LEAVEAPPLICATION.ACTNOOFDAYS);
    SP_LEAVEAPPLICATION.STARTSUNDAYCOUNT:=findmonday(i n_LVTYPCODE,in_FRDATE,in_EMPCODE);
    SP_LEAVEAPPLICATION.ACTNOOFDAYS := SP_LEAVEAPPLICATION.STARTSUNDAYCOUNT1;

    END IF;

    this is the code and it is not show the value of my function which it(findmonday) is return...
    and the procedure run properly in the application but not added my function value when it satisfied the condition.

  9. #9
    Join Date
    Jul 2011
    Posts
    38
    IF SP_LEAVEAPPLICATION.LVTYPCODE = 'LTM-000001' THEN
    -- RAISE_APPLICATION_ERROR(-20090,'TOTAL ______________DAYS--'||SP_LEAVEAPPLICATION.elnumber||'Actual days'||SP_LEAVEAPPLICATION.ACTNOOFDAYS);
    SP_LEAVEAPPLICATION.STARTSUNDAYCOUNT1:=findmonday( in_LVTYPCODE,in_FRDATE,in_EMPCODE);
    SP_LEAVEAPPLICATION.ACTNOOFDAYS := SP_LEAVEAPPLICATION.STARTSUNDAYCOUNT1;

    END IF;

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by vishwas View Post
    IF SP_LEAVEAPPLICATION.LVTYPCODE = 'LTM-000001' THEN
    -- RAISE_APPLICATION_ERROR(-20090,'TOTAL ______________DAYS--'||SP_LEAVEAPPLICATION.elnumber||'Actual days'||SP_LEAVEAPPLICATION.ACTNOOFDAYS);
    SP_LEAVEAPPLICATION.STARTSUNDAYCOUNT1:=findmonday( in_LVTYPCODE,in_FRDATE,in_EMPCODE);
    SP_LEAVEAPPLICATION.ACTNOOFDAYS := SP_LEAVEAPPLICATION.STARTSUNDAYCOUNT1;

    END IF;
    So, after you do this
    Code:
    IF SP_LEAVEAPPLICATION.LVTYPCODE = 'LTM-000001' THEN
          -- RAISE_APPLICATION_ERROR(-20090,'TOTAL ______________DAYS--'||SP_LEAVEAPPLICATION.elnumber||'Actual days'||SP_LEAVEAPPLICATION.ACTNOOFDAYS);
          SP_LEAVEAPPLICATION.STARTSUNDAYCOUNT1:=2;
          SP_LEAVEAPPLICATION.ACTNOOFDAYS := SP_LEAVEAPPLICATION.STARTSUNDAYCOUNT1;
    END IF;
    you will "see" somewhere the correct value (2)? Now you see what? NULL? Zero? Anything else?

    If so, the main problem was posted in my previous post. You have to change all occurrences of this call
    Code:
    to_char(<any_date>,'DAY')
    to
    Code:
    to_char(<any_date>,'DAY','nls_date_language=english')
    in FINDMONDAY function. After doing so, it should work regardless the regional settings.

    Just for confirmation, you may run the original FINDMONDAY function after issuing
    Code:
    alter session set nls_date_language=german;
    and check whether it still "works" in sqlplus where it returned the correct result. If not, the cause and its solution are described above (and in my previous posts).

  11. #11
    Join Date
    Jul 2011
    Posts
    38
    thanks sir,
    with ur help i m able to solve my problem...
    my procedure running properly and given right output.

    sir if u dont my to give me ur gmail id..
    my gmail id is vishwassrivastava25@gmail.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •