1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help needed interpreting explain plan and suggestions tuning the query

Discussion in 'SQL PL/SQL' started by james shallow, Apr 15, 2017.

  1. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Thanks, Sergey. As this is run annually, I guess the safest would be one year window. add_months(date, -12)

    I am still getting the error message
    ORA-00920: invalid relational operator
    00920. 00000 - "invalid relational operator"
    *Cause:
    *Action:
    Error at Line: 27 Column: 25

    Sergey, I don't understand line 6, can you please explain this to me.

    Code (SQL):
    WHERE sev2.THEN start_date < DATE('01-Aug-2016','DD-mon-YYYY')
     
    Last edited: Apr 17, 2017
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    I don't see such conditions.There's such a simple line : where sev.start_date >= tst.start_date;
     
  3. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Thanks, Sergey, I removed the funny characters "then" and it is working now.
     
  4. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Hi Sergey, Please I know you have been so helpful to me today. I just need the last favour from you if possible. If you look at my code, where I am getting the salary and the fte_salary, I am passing the start_date from qry1 as the current salary and current_fte_salary. Now I need to add the code to get the old_salary and the old_fte_salary. This is just a matter of passing a date before the current run_date. Please see the code. The query works but soon as I add this bit, it slows down and takes a long time to retrieve the data. This is because I have to join to the view "BRSV_SALARY_EVENTS SEV_PREV". All I need to get is the maximum date before the run date. Is there a way I can tweak the query without joining to this problem view again? Thanks for your help. Please see code in italics
    Code (SQL):
    SELECT * FROM (
    WITH qry1 AS (
    SELECT appointment_code, sev.start_date start_date FROM BRSV_SALARY_EVENTS sev
    WHERE sev.start_date >= to_date('01-AUG-2016','DD-MON-YYYY')
      OR sev.start_date =
          (SELECT MAX(sev2.start_date)
           FROM brsv_salary_events sev2
           WHERE sev2.start_date < to_date('01-AUG-2016','DD-MON-YYYY') ))
    SELECT
    brsf_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code) FAM,
    decode('2','1',org.team_area,NULL) AREA,
    org.body_position body_pos,
    org.full_name,
    peo.surname||', '||peo.initials name,
    peo.payroll_number,
    decode(BRSF_SES_IND(peo.person_code),
    NULL,  BRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')),
    BRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')),  BRSF_SES_IND(peo.person_code),
    BRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')) ||
    ' at revision date, ' || BRSF_SES_IND(peo.person_code) || ' now') SES,
    app.pos_post_number post_number,
    app.appointment_code,
    qry1.start_date salev_date,
    org.team_area,
    BRSF_APPT_GRADE(app.appointment_code,qry1.start_date) grade_name,
    BRSF_APPT_HOURS(app.appointment_code, qry1.start_date) hours_per_week,
    BRSF_APPT_SALARY(app.appointment_code, qry1.start_date) salary,
    BRSF_APPT_FTE_SALARY(app.appointment_code, qry1.start_date) fte_salary,

    [I]--BRSF_APPT_SALARY(app.appointment_code, sev_prev.start_date) old_salary,
    --BRSF_APPT_FTE_SALARY(app.appointment_code, sev_prev.start_date) old_fte_salary,[/I]

    decode(BRSF_APPT_SPINAL_IND(app.appointment_code, qry1.start_date),
    'N','NON-SPINAL',
    'Y','Spinal') spinal_ind,
    gra.psp_category||gra.psp_group staffcat
    FROM
    hes_people peo,
    hes_periods_of_service pos,
    hes_appointments app,
    brs_post_org_histories poh,
    hes_organisation_units org,
    --brsv_salary_events sev,
    [I]brsv_salary_events sev_prev,[/I]
    hes_app_grade_histories agh,
    hes_grades gra,
    qry1
    WHERE peo.person_code = pos.per_person_code
    AND to_date('01-AUG-2016','DD-MON-YYYY') BETWEEN pos.start_date AND pos.end_date
    AND pos.period_of_ser_code = app.pes_period_of_ser_code
    AND app.pos_post_number = poh.post_number
    AND poh.start_date= (SELECT MAX(poh2.start_date)
                         FROM brs_post_org_histories poh2
                         WHERE poh2.post_number=poh.post_number
                         AND poh2.start_date<=qry1.start_date)
    AND poh.organisation_code = org.organisation_code
    AND agh.gra_grade_code = gra.grade_code
    AND qry1.start_date BETWEEN gra.start_date AND gra.end_date
    AND app.appointment_code = agh.app_appointment_code
    AND qry1.start_date BETWEEN agh.effective_start_date AND agh.effective_end_date
    AND qry1.start_date BETWEEN app.start_date AND app.end_date
    AND app.end_date >= to_date('01-AUG-2016','DD-MON-YYYY')
    AND app.pos_post_number != 'Z1'
    AND qry1.appointment_code = app.appointment_code
    -------
    [I]/*and app.appointment_code=sev_prev.appointment_code
      and sev_prev.start_date=
          (select max(subsev.start_date)
           from brsv_salary_events subsev
           where subsev.appointment_code=sev_prev.appointment_code
           and subsev.start_date<to_date('01-AUG-2016','DD-MON-YYYY'))*/
    [/I]
    UNION ALL
    (
    SELECT
    BRSF_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code) FAM,
    decode('2','1',org.team_area,NULL) AREA,
    org.body_position org_body_pos,
    org.full_name,
    peo.surname||', '||peo.initials name,
    peo.payroll_number,
    decode(BRSF_SES_IND(peo.person_code),
    NULL, BRSF_SES_IND(peo.person_code,pos.start_date),
    BRSF_SES_IND(peo.person_code,pos.start_date),  BRSF_SES_IND(peo.person_code),
    BRSF_SES_IND(peo.person_code,pos.start_date) ||
    ' at start date, ' || BRSF_SES_IND(peo.person_code) || ' now') SES,
    app.pos_post_number post_number,
    app.appointment_code,
    pos.start_date service_start_date,
    org.team_area,
    BRSF_APPT_GRADE(app.appointment_code, pos.start_date) grade_name,
    BRSF_APPT_HOURS(app.appointment_code, pos.start_date) hours_per_week,
    BRSF_APPT_SALARY(app.appointment_code, pos.start_date) salary,
    BRSF_APPT_FTE_SALARY(app.appointment_code, pos.start_date) fte_salary,
    decode(BRSF_APPT_SPINAL_IND(app.appointment_code, pos.start_date),
    'N','NON-SPINAL',
    'Y','Spinal') spinal_ind,
    gra.psp_category||gra.psp_group staffcat
    FROM
    hes_people peo,
    hes_periods_of_service pos,
    hes_appointments app,
    brs_post_org_histories poh,
    hes_organisation_units org,
    hes_app_grade_histories agh,
    hes_grades gra
    WHERE  peo.person_code = pos.per_person_code
    AND pos.start_date > to_date('01-AUG-2016', 'DD-MON-YYYY')
    AND (BRSF_JOB_FAMILY(app.appointment_code) IN ('R')  OR 'null' IN (''))
    AND pos.period_of_ser_code = app.pes_period_of_ser_code
    AND pos.start_date BETWEEN app.start_date AND app.end_date
    AND app.pos_post_number != 'Z1'
    AND app.pos_post_number = poh.post_number
    AND pos.start_date BETWEEN poh.start_date AND poh.end_date
    AND poh.organisation_code = org.organisation_code
    AND app.appointment_code = agh.app_appointment_code
    AND pos.start_date BETWEEN agh.effective_start_date AND agh.effective_end_date
    AND agh.gra_grade_code = gra.grade_code
    AND pos.start_date BETWEEN gra.start_date AND gra.end_date)
    ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 DESC)
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi,James.

    Your query is desirable to rewrite.
    You know the data distribution and the amount of data in your tables.
    On the basis of this information need to rewrite joins your tables so that at each step reduces the amount of unnecessary data.
    This way you get more correct join order.

    A few questions :
    1)
    How many records returns this block :
    Code (Text):

    WITH qry1 AS (
    SELECT appointment_code, sev.start_date start_date FROM BRSV_SALARY_EVENTS sev
    WHERE sev.start_date >= to_date('01-AUG-2016','DD-MON-YYYY')
      OR sev.start_date =
          (SELECT MAX(sev2.start_date)
           FROM brsv_salary_events sev2
           WHERE sev2.start_date < to_date('01-AUG-2016','DD-MON-YYYY') ))
    select count(*) from qry1;
     
    2)
    Why is block
    Code (Text):

    (/*and app.appointment_code=sev_prev.appointment_code
      and sev_prev.start_date=
          (select max(subsev.start_date)
           from brsv_salary_events subsev
           where subsev.appointment_code=sev_prev.appointment_code
           and subsev.start_date<to_date('01-AUG-2016','DD-MON-YYYY'))*/
     
    not used QRY1 .Here you need to use qry1.
    3)
    What is function BRSF_APPT_FTE_SALARY?
    Can you provide code your function ?
     
    james shallow likes this.
  6. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Code (SQL):
    WITH qry1 AS (
    SELECT appointment_code, sev.start_date start_date FROM BRSV_SALARY_EVENTS sev
    WHERE sev.start_date >= to_date('01-AUG-2016','DD-MON-YYYY')
      OR sev.start_date =
          (SELECT MAX(sev2.start_date)
           FROM brsv_salary_events sev2
           WHERE sev2.start_date < to_date('01-AUG-2016','DD-MON-YYYY') ))
    SELECT COUNT(*) FROM qry1;

    COUNT(*) = 13994 - This took about 3 minutes TO run
    Code (SQL):
    This IS part OF qry1 WHERE I GET ALL the appointment code AND start_date AND WHEN joined WITH the main query FOR this appointment_code '643886', FOR dates greater than '01-AUG-2016'
    RETURNS the below DATA (This shows SOME duplicates) - These dates are used FOR salary ON this DATE '01-AUG-2016' AND future dates, (i.e. pay increament)
    SELECT sev.start_date start_date
    FROM BRSV_SALARY_EVENTS sev
    WHERE sev.appointment_code = '643886'
    AND  sev.start_date >= to_date('01-AUG-2016','DD-MON-YYYY')
      OR sev.start_date =
          (SELECT MAX(sev2.start_date)
           FROM brsv_salary_events sev2
           WHERE sev2.start_date < to_date('01-AUG-2016','DD-MON-YYYY') )

    31-JUL-16
    31-JUL-16
    31-JUL-16
    31-JUL-16
    01-AUG-16
    24-MAR-17
    31-JUL-16
    31-JUL-16
    31-JUL-16
    31-JUL-16
    31-JUL-16
    31-JUL-16
    31-JUL-16
    31-JUL-16
    31-JUL-16
    31-JUL-16

    SELECT BRSF_APPT_SALARY('643886','01-AUG-16') new_salary FROM dual

    new_salary = SELECT BRSF_APPT_SALARY('343886','01-AUG-16') new_salary FROM dual

    This bit OF query IS meant TO RETURN salary BEFORE 01-AUG-2016' as old salary when '24-MAR-16' is passed to it.
          select max(subsev.start_date)
           from brsv_salary_events subsev
           where subsev.appointment_code = '
    643886'
           and subsev.start_date<to_date('
    01-AUG-2016','DD-MON-YYYY')

    24-MAR-16

    select BRSF_APPT_SALARY('
    643886','24-MAR-16') old_salary from dual

    old_salary = 52219

    ----------------------------------------------------------------------------
    [code=sql]create or replace function BRSF_APPT_FTE_SALARY
    (v_appointment_code number,
    v_date             date := trunc(sysdate))
    return number
    --
    -- Function to return the FTE salary (rounded to the nearest pound)
    -- for a given appointment as at today or a specified date
    --
    -- Parameters:
    -- 1 - appointment code - numeric value - required
    -- 2 - date - optional - will assume today if not supplied
    --
    is
    v_hrs          hes_agh_hrs_wks_histories.hours_per_week%type;
    v_wks          hes_agh_hrs_wks_histories.weeks_per_year%type;
    v_std_hrs      hes_psp_std_hrs_histories.hours_per_week%type;
    v_std_wks      hes_psp_std_hrs_histories.weeks_per_year%type;
    v_payment_type hes_spinal_scales.payment_type%type;
    v_agh_code     hes_app_grade_histories.app_grade_hist_code%type;
    v_fte_amount   number;
    v_fte_salary   number;
    --
    cursor c1 is
    select agh.app_grade_hist_code,
           ss.payment_type,
           hwh.hours_per_week,
           hwh.weeks_per_year,
           shh.hours_per_week,
           shh.weeks_per_year
    from hes_app_grade_histories agh,
         hes_agh_hrs_wks_histories hwh,
         hes_grades g,
         hes_psp_std_hrs_histories shh,
         hes_spinal_scales ss
    where agh.app_appointment_code=v_appointment_code
      and trunc(v_date) between agh.effective_start_date
                            and agh.effective_end_date
      and agh.app_grade_hist_code=hwh.agh_app_grade_hist_code
      and trunc(v_date) between hwh.effective_start_date
                            and hwh.effective_end_date
      and agh.gra_grade_code=g.grade_code
      and trunc(v_date) between g.start_date
                            and g.end_date
      and g.psp_category=shh.psp_category
      and g.psp_group=shh.psp_group
      and trunc(v_date) between shh.effective_start_date
                            and shh.effective_end_date
      and g.psp_spc_spine_name=ss.spine_name
      and trunc(v_date) between ss.start_date
                            and ss.end_date;
    --
    cursor c_spinal is
    select
    --Red-circled salaries do not extend beyond 4 years after Reward date 01-AUG-2007
    DECODE(SIGN(trunc(v_date)-TO_DATE('
    01-AUG-2011','DD-MON-YYYY')),
      -1,GREATEST(NVL(PRH.SALARY_AMOUNT,0),NVL(AGIH.RED_CIRCLED_FTE_SALARY,0)),
         PRH.SALARY_AMOUNT)
    from hes_app_grade_incr_histories agih,
         hes_spinal_increment_maps sim,
         hes_point_rate_histories prh
    where agih.agh_app_grade_hist_code=v_agh_code
      and trunc(v_date) between agih.effective_start_date
                            and agih.effective_end_date
      and agih.sim_gra_grade_code=sim.gra_grade_code
      and agih.sim_increment_point=sim.increment_point
      and trunc(v_date) between sim.effective_start_date
                            and sim.effective_end_date
      and sim.spp_point=prh.spp_point
      and sim.spp_spc_spine_name=prh.spp_spc_spine_name
      and trunc(v_date) between prh.effective_start_date
                            and prh.effective_end_date;
    --
    cursor c_non_spinal is
    select sh.salary
    from hes_salary_histories sh
    where sh.agh_app_grade_hist_code=v_agh_code
      and trunc(v_date) between sh.effective_start_date
                            and sh.effective_end_date;
    --
    begin
    open c1;
    fetch c1
    into v_agh_code,
         v_payment_type,
         v_hrs,
         v_wks,
         v_std_hrs,
         v_std_wks;
    --
    open c_spinal;
    fetch c_spinal
    into v_fte_amount;
    if c_spinal%notfound then
      open c_non_spinal;
      fetch c_non_spinal
      into v_fte_amount;
      close c_non_spinal;
    end if;
    close c_spinal;
    --
    if v_payment_type='
    ANNUAL SALARY' then
      v_fte_salary:=v_fte_amount;
    else
      select
    --   round(v_fte_amount * v_std_hrs*v_std_wks)
    --   CEIL(v_fte_amount * v_std_hrs*decode(v_std_wks,52,52.2,v_std_wks))
       ROUND(v_fte_amount * v_std_hrs*decode(v_std_wks,52,52.2,v_std_wks))
      into v_fte_salary
      from dual;
    end if;
    --
    close c1;
    --
    return(v_fte_salary);
    exception
    when others then
    return(null);
    end;
    The help I want is if it is possible to retrieve these two dates (future and past) from qry1 or how can I do this without having join to the VIEW again[/code]
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Replace this condition from "and trunc(v_date) between ss.start_date
    and ss.end_date;"

    to " and v_date between ss.start_date
    and ss.end_date;",because might not used index...

    Try this variant of function:
    Code (Text):

    create or replace function BRSF_APPT_FTE_SALARY
    (v_appointment_code number,
     v_date             date := trunc(sysdate))
    return number
    --
    -- Function to return the FTE salary (rounded to the nearest pound)
    -- for a given appointment as at today or a specified date
    --
    -- Parameters:
    -- 1 - appointment code - numeric value - required
    -- 2 - date - optional - will assume today if not supplied
    --
    is
    v_hrs          hes_agh_hrs_wks_histories.hours_per_week%type;
    v_wks          hes_agh_hrs_wks_histories.weeks_per_year%type;
    v_std_hrs      hes_psp_std_hrs_histories.hours_per_week%type;
    v_std_wks      hes_psp_std_hrs_histories.weeks_per_year%type;
    v_payment_type hes_spinal_scales.payment_type%type;
    v_agh_code     hes_app_grade_histories.app_grade_hist_code%type;
    v_fte_amount   number;

    PRAGMA udf;
    --
    --
    BEGIN
      --c1
      select agh.app_grade_hist_code,
             ss.payment_type,
             hwh.hours_per_week,
             hwh.weeks_per_year,
             shh.hours_per_week,
             shh.weeks_per_year
      INTO      
             v_agh_code,
             v_payment_type,
             v_hrs,
             v_wks,
             v_std_hrs,
             v_std_wks
      from hes_app_grade_histories agh,
           hes_agh_hrs_wks_histories hwh,
           hes_grades g,
           hes_psp_std_hrs_histories shh,
           hes_spinal_scales ss
      where agh.app_appointment_code=v_appointment_code
        and v_date between agh.effective_start_date
                              and agh.effective_end_date
        and agh.app_grade_hist_code=hwh.agh_app_grade_hist_code
        and v_date between hwh.effective_start_date
                              and hwh.effective_end_date
        and agh.gra_grade_code=g.grade_code
        AND v_date between g.start_date
                              and g.end_date
        and g.psp_category=shh.psp_category
        and g.psp_group=shh.psp_group
        and v_date  between shh.effective_start_date
                              and shh.effective_end_date
        and g.psp_spc_spine_name=ss.spine_name
        and v_date between ss.start_date
                              and ss.end_date;
    --
      BEGIN
        --spin
        SELECT
          COALESCE(
                --1
                --Red-circled salaries do not extend beyond 4 years after Reward date 01-AUG-2007
                DECODE(SIGN(trunc(v_date)-TO_DATE('01-AUG-2011','DD-MON-YYYY')),
                  -1,GREATEST(NVL(PRH.SALARY_AMOUNT,0),NVL(AGIH.RED_CIRCLED_FTE_SALARY,0)),
                     PRH.SALARY_AMOUNT),
                 --2  
                 ( select max(sh.salary)
                  from hes_salary_histories sh
                  where sh.agh_app_grade_hist_code=v_agh_code
                    and v_date between sh.effective_start_date
                                          and sh.effective_end_date)
                  )                      
        INTO v_fte_amount  
        from hes_app_grade_incr_histories agih,
             hes_spinal_increment_maps sim,
             hes_point_rate_histories prh
        where agih.agh_app_grade_hist_code=v_agh_code
          and v_date between agih.effective_start_date
                                and agih.effective_end_date
          and agih.sim_gra_grade_code=sim.gra_grade_code
          and agih.sim_increment_point=sim.increment_point
          and v_date between sim.effective_start_date
                                and sim.effective_end_date
          and sim.spp_point=prh.spp_point
          and sim.spp_spc_spine_name=prh.spp_spc_spine_name
          and v_date between prh.effective_start_date
                                and prh.effective_end_date;
                           
      --result
      return  case WHEN v_payment_type='ANNUAL SALARY' THEN v_fte_amount
                      ELSE
                           ROUND(v_fte_amount * v_std_hrs*CASE WHEN v_std_wks = 52 THEN 52.2 ELSE v_std_wks END )
                       END;  

    exception
    when others then
    return(null);

    end;
     
     
    Last edited: Apr 18, 2017
    james shallow likes this.
  8. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Hi Sergey, I cannot thank you enough, I am learning new stuff from you. Thanks for re-writing this function. I got compilation error about the DECODE but besides, I just need to ask you about getting the date parameter for old_salary and the old_fte_salary to pass to the function. That seems to be the real problem(i.e. how to get future and past date parameter from BRSV_SALARY_EVENTS). Thanks for your help sir.
    Code (SQL):
    LINE/COL ERROR
    -------- -------------------------------------------------------------------------------------
    97/3     PL/SQL: Statement ignored
    99/55    PLS-00204: FUNCTION OR pseudo-COLUMN 'DECODE' may be used inside a SQL statement ONLY
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Decode - this sql fucntion. It need replace to CASE ...

    Try this...
    Code (Text):

    create or replace function BRSF_APPT_FTE_SALARY
    (v_appointment_code number,
     v_date             date := trunc(sysdate))
    return number
    --
    -- Function to return the FTE salary (rounded to the nearest pound)
    -- for a given appointment as at today or a specified date
    --
    -- Parameters:
    -- 1 - appointment code - numeric value - required
    -- 2 - date - optional - will assume today if not supplied
    --
    is
    v_hrs          hes_agh_hrs_wks_histories.hours_per_week%type;
    v_wks          hes_agh_hrs_wks_histories.weeks_per_year%type;
    v_std_hrs      hes_psp_std_hrs_histories.hours_per_week%type;
    v_std_wks      hes_psp_std_hrs_histories.weeks_per_year%type;
    v_payment_type hes_spinal_scales.payment_type%type;
    v_agh_code     hes_app_grade_histories.app_grade_hist_code%type;
    v_fte_amount   number;

    PRAGMA udf;
    --
    --
    BEGIN
      --c1
      select agh.app_grade_hist_code,
             ss.payment_type,
             hwh.hours_per_week,
             hwh.weeks_per_year,
             shh.hours_per_week,
             shh.weeks_per_year
      INTO    
             v_agh_code,
             v_payment_type,
             v_hrs,
             v_wks,
             v_std_hrs,
             v_std_wks
      from hes_app_grade_histories agh,
           hes_agh_hrs_wks_histories hwh,
           hes_grades g,
           hes_psp_std_hrs_histories shh,
           hes_spinal_scales ss
      where agh.app_appointment_code=v_appointment_code
        and v_date between agh.effective_start_date
                              and agh.effective_end_date
        and agh.app_grade_hist_code=hwh.agh_app_grade_hist_code
        and v_date between hwh.effective_start_date
                              and hwh.effective_end_date
        and agh.gra_grade_code=g.grade_code
        AND v_date between g.start_date
                              and g.end_date
        and g.psp_category=shh.psp_category
        and g.psp_group=shh.psp_group
        and v_date  between shh.effective_start_date
                              and shh.effective_end_date
        and g.psp_spc_spine_name=ss.spine_name
        and v_date between ss.start_date
                              and ss.end_date;
    --
      BEGIN
        --spin
        SELECT
          COALESCE(
                --1
                --Red-circled salaries do not extend beyond 4 years after Reward date 01-AUG-2007
                DECODE(SIGN(trunc(v_date)-TO_DATE('01-AUG-2011','DD-MON-YYYY')),
                  -1,GREATEST(NVL(PRH.SALARY_AMOUNT,0),NVL(AGIH.RED_CIRCLED_FTE_SALARY,0)),
                     PRH.SALARY_AMOUNT),
                 --2
                 ( select max(sh.salary)
                  from hes_salary_histories sh
                  where sh.agh_app_grade_hist_code=v_agh_code
                    and v_date between sh.effective_start_date
                                          and sh.effective_end_date)
                  )                    
        INTO v_fte_amount
        from hes_app_grade_incr_histories agih,
             hes_spinal_increment_maps sim,
             hes_point_rate_histories prh
        where agih.agh_app_grade_hist_code=v_agh_code
          and v_date between agih.effective_start_date
                                and agih.effective_end_date
          and agih.sim_gra_grade_code=sim.gra_grade_code
          and agih.sim_increment_point=sim.increment_point
          and v_date between sim.effective_start_date
                                and sim.effective_end_date
          and sim.spp_point=prh.spp_point
          and sim.spp_spc_spine_name=prh.spp_spc_spine_name
          and v_date between prh.effective_start_date
                                and prh.effective_end_date;
                         
      --result
      return  case WHEN v_payment_type='ANNUAL SALARY' THEN v_fte_amount
                      ELSE
                           ROUND(v_fte_amount * v_std_hrs*CASE WHEN v_std_wks = 52 THEN 52.2 ELSE v_std_wks END )
                       END;

    exception
    when others then
    return(null);

    end;
     
     
  10. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Many Thanks Sergey, the function is created and pretty fast. Thanks, pls when you get a chance later about the dates. I will get in touch later on in the day. Many thanks sir.