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;