Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement