FUNCTION GET_AEFS_RATING_FY(p_project_id in NUMBER)
    RETURN NUMBER
IS
    aefs_rating_pct NUMBER := 0;
    timeliness_rating NUMBER := 0;
    nature_opinion_rating NUMBER := 0;
    fiscal_yr NUMBER;
    agencies_due_count NUMBER := 0;
    months_delayed NUMBER := 0;
    auditors_opinion VARCHAR2(50);
    rating_timeliness_const NUMBER;
    rating_nature_opinion_uq_const NUMBER;
    rating_nature_opinion_ql_const NUMBER;
    due_date DATE;
    acceptable_flag VARCHAR(10);
    months_due_after NUMBER := 0;
    due_month_with_added NUMBER := 0;
    due_yr_with_added NUMBER := 0;
    due_date_with_added DATE;
    effectivity_date DATE;
BEGIN

    select attribute1
    into rating_timeliness_const
    from adbpa_lookup_values
    where code = 'AEFS_RATING_TIMELINESS'
    and type = 'AEFS_RATING';

    select attribute1
    into rating_nature_opinion_uq_const
    from adbpa_lookup_values
    where code = 'AEFS_RATING_NATURE_OPINION_UQ'
    and type = 'AEFS_RATING';

    select attribute1
    into rating_nature_opinion_ql_const
    from adbpa_lookup_values
    where code = 'AEFS_RATING_NATURE_OPINION_QL'
    and type = 'AEFS_RATING';


    for agencies in (select distinct(ca.agency_cd)
                    from adbpa_cov_agency_setup setup, adbpa_covenant_accounts ca, adbpa_dmf_headers dmf
                    where setup.project_id = p_project_id
                    and setup.submit_aefs = 'Y'
                    and setup.agency_cd = ca.agency_cd
                    and ca.dmf_id = dmf.dmf_id
                    and setup.project_id = dmf.project_id
                    and sysdate BETWEEN setup.effective_start_date AND setup.effective_end_date
                    and sysdate BETWEEN ca.effective_start_date AND ca.effective_end_date
                    and sysdate BETWEEN dmf.effective_start_date AND dmf.effective_end_date)
    loop
        select extract(year from sysdate) into fiscal_yr from dual;

        /** Check if there is an exsting Fiscal year for the current year and if it is Accepted already **/
        begin
            select adbpa_covenant_accounts_pkg.Get_due_date(fy_end, fy, months_due_after, agency_cd) AS due_date, nvl(acceptable_flag, 'N') acceptable_flag
            into due_date, acceptable_flag
            from adbpa_covenant_accounts ca, adbpa_dmf_headers dmf
            where ca.dmf_id = dmf.dmf_id
            and dmf.project_id = p_project_id
            and cov_type_cd = 'COV-AFS'
            and agency_cd = agencies.agency_cd
            and fy = fiscal_yr
            and rejection_date is null
            and sysdate BETWEEN ca.effective_start_date AND ca.effective_end_date
            and sysdate BETWEEN dmf.effective_start_date AND dmf.effective_end_date;
        exception
            when no_data_found then
                fiscal_yr := fiscal_yr - 1;
        end;

        if extract(year from due_date) > fiscal_yr then
            fiscal_yr := fiscal_yr - 1;
        elsif (TO_DATE(due_date,'DD-MON-YYYY') > TO_DATE(sysdate,'DD-MON-YYYY')) AND (acceptable_flag = 'N') then
            --do not compute rating if due date is future date and no yet Acceptable, rule raised duting UAT
            fiscal_yr := fiscal_yr - 1;            
        elsif (TO_DATE(due_date,'DD-MON-YYYY') > TO_DATE(sysdate,'DD-MON-YYYY')) AND (acceptable_flag = 'Y') then
            --compute rating even if due date is still future date but already Accepted, rule raised duting UAT
            fiscal_yr := fiscal_yr;
        end if;
        agencies_due_count := agencies_due_count+1;
        --check due date again using new FY
        begin
            select due_date, acceptable_flag
            into due_date, acceptable_flag
        from (
            select adbpa_covenant_accounts_pkg.Get_due_date(fy_end, fy, months_due_after, agency_cd) AS due_date, nvl(acceptable_flag, 'N') acceptable_flag
                , row_number() over (partition by dmf.dmf_id, ca.cov_type_Cd, ca.fy, ca.agency_cd order by submission_date desc nulls last) ranks

            from adbpa_covenant_accounts ca, adbpa_dmf_headers dmf
            where ca.dmf_id = dmf.dmf_id
            and dmf.project_id = p_project_id
            and cov_type_cd = 'COV-AFS'
            and agency_cd = agencies.agency_cd
            and fy = fiscal_yr
            and rejection_date is null
            and sysdate BETWEEN ca.effective_start_date AND ca.effective_end_date
            and sysdate BETWEEN dmf.effective_start_date AND dmf.effective_end_date
            ) where ranks = 1
            ;
        exception
            when no_data_found then
                continue;
        end;
        
        if (TO_DATE(due_date,'DD-MON-YYYY') > TO_DATE(sysdate,'DD-MON-YYYY')) AND (acceptable_flag = 'N') then
            exit;
        end if;

        --New rule add 1 qtr to due date or end fiscal yr
        due_date := ADD_MONTHS(trunc(due_date), 3 );
        select extract(month from due_date) into due_month_with_added from dual;
        select extract(year from due_date) into due_yr_with_added from dual;

        --future due date but acceptable flag needs to be Y before deducting 1 yr
        if (due_month_with_added between 10 and 12 ) AND (acceptable_flag = 'N') then -- qtr 4
            --deduct 1 again
            fiscal_yr := fiscal_yr - 1;
        elsif (TO_DATE(due_date,'DD-MON-YYYY') > TO_DATE(sysdate,'DD-MON-YYYY')) AND (acceptable_flag = 'N') then
            --do not compute rating if due date is future date and no yet Acceptable, rule raised duting UAT
            fiscal_yr := fiscal_yr - 1;
        end if;
        --end New rule add 1 qtr to due date or end fiscal yr


        -- get Nature of opinion, Due Date, Months delayed
        BEGIN
            select auditors_opinion
            , adbpa_covenant_accounts_pkg.Get_due_date(fy_end, fy, months_due_after, agency_cd) AS due_date
            , nvl(adbpa_performance_utils_pkg.Get_months_delayed(acceptable_flag, available_flag, submission_date, adbpa_covenant_accounts_pkg.Get_due_date(fy_end, fy, months_due_after ,agency_cd)), 0) AS months_delayed
            , nvl(acceptable_flag, 'N') acceptable_flag
            , months_due_after
            into auditors_opinion, due_date, months_delayed, acceptable_flag, months_due_after
            from adbpa_covenant_accounts ca, adbpa_dmf_headers dmf
            where ca.dmf_id = dmf.dmf_id
            and dmf.project_id = p_project_id
            and cov_type_cd = 'COV-AFS'
            and agency_cd = agencies.agency_cd
            and ((fy = fiscal_yr and TO_DATE(sysdate,'DD-MON-YYYY') >= TO_DATE(adbpa_covenant_accounts_pkg.Get_due_date(fy_end, fy, months_due_after, agency_cd),'DD-MON-YYYY')) or (fy = fiscal_yr and TO_DATE(sysdate,'DD-MON-YYYY') < TO_DATE(adbpa_covenant_accounts_pkg.Get_due_date(fy_end, fy, months_due_after, agency_cd),'DD-MON-YYYY')))
            and rejection_date is null
            and sysdate BETWEEN ca.effective_start_date AND ca.effective_end_date
            and sysdate BETWEEN dmf.effective_start_date AND dmf.effective_end_date;

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                CONTINUE;
            WHEN OTHERS  THEN
                CONTINUE;
        END;

        --New rule add 1 qtr to due date or end fiscal yr
        if (acceptable_flag = 'N') then
            due_date := ADD_MONTHS(trunc(due_date), 3 );
            select extract(month from due_date) into due_month_with_added from dual;
            select extract(year from due_date) into due_yr_with_added from dual;

            if (due_month_with_added between 1 and 3 ) then -- qtr 1
                due_date_with_added := TO_DATE('01-APR-'||due_yr_with_added, 'DD-MON-YYYY');
            elsif (due_month_with_added between 4 and 6 ) then -- qtr 2
                due_date_with_added := TO_DATE('01-JUL-'||due_yr_with_added, 'DD-MON-YYYY');
            elsif (due_month_with_added between 7 and 9 ) then -- qtr 3
                due_date_with_added := TO_DATE('01-OCT-'||due_yr_with_added, 'DD-MON-YYYY');
            elsif (due_month_with_added between 10 and 12 ) then -- qtr 3
                due_date_with_added := TO_DATE('01-JAN-'||(due_yr_with_added+1), 'DD-MON-YYYY');
            end if;

        else
            due_date_with_added := due_date;
        end if;
        --end New rule add 1 qtr to due date or end fiscal yr

        --IF (TO_DATE(sysdate,'DD-MON-YYYY') >= TO_DATE(due_date_with_added,'DD-MON-YYYY')) THEN
        IF (TO_DATE(sysdate,'DD-MON-YYYY') >= TO_DATE(due_date_with_added,'DD-MON-YYYY')) OR (acceptable_flag = 'Y') THEN

            if (acceptable_flag = 'Y') then
                --compute Timeliness
                if (months_delayed = 0) then
                      timeliness_rating := timeliness_rating + rating_timeliness_const;
                else
                    timeliness_rating := timeliness_rating + 0; --adding this just to be clear, because of constant changes in requirements. remove if final and stable
                end if;

                --compute Nature of opinion
                if (auditors_opinion = 'U') then
                    nature_opinion_rating := nature_opinion_rating + rating_nature_opinion_uq_const; -- +10 Unqualified
                elsif (auditors_opinion = 'Q') then
                    nature_opinion_rating := nature_opinion_rating + rating_nature_opinion_ql_const; -- +5 Qualified
                else
                    nature_opinion_rating := nature_opinion_rating + 0;
                end if;
            end if;
        ELSE
            --If not yet due, give perfect score for each category
            timeliness_rating := timeliness_rating + rating_timeliness_const; -- 15
            nature_opinion_rating := nature_opinion_rating + rating_nature_opinion_uq_const; --10
        END IF;


    end loop;

    if agencies_due_count > 0 then
        --AEFS RATING = (timeliness total + nature of opinion total) / no. agencies due
        aefs_rating_pct := (timeliness_rating + nature_opinion_rating) / agencies_due_count;
    else
        --no due agency, check if newly effective
        select min(effectivity_date) effectivity_date into effectivity_date
        from  apps.adbpa_financing_Headers_V afh
            , apps.adbpa_financing_details_v afd
        where afh.project_Financing_id = afd.project_Financing_id
        and  afd.financing_source_Cd in ('ADB', 'COFINANCING')
        and  afd.approval_number is not null
        and  afh.project_id = p_project_id
        group by afh.project_id, afh.project_financing_id;

        effectivity_date := ADD_MONTHS(trunc(effectivity_date), 15 ); -- plus 1 yr and 1 qtr before rating is computed

        if (TO_DATE(effectivity_date,'DD-MON-YYYY') >= TO_DATE(sysdate,'DD-MON-YYYY')) then            
            aefs_rating_pct := rating_timeliness_const + rating_nature_opinion_uq_const;
        end if;
    end if;

    return round(aefs_rating_pct, 2);

EXCEPTION
   WHEN no_data_found THEN
      RETURN 0;
   WHEN others THEN
      RETURN 0;
END GET_AEFS_RATING_FY;