SHARE
TWEET

Untitled

a guest Oct 2nd, 2019 84 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. FUNCTION GET_AEFS_RATING_FY(p_project_id IN NUMBER)
  2.     RETURN NUMBER
  3. IS
  4.     aefs_rating_pct NUMBER := 0;
  5.     timeliness_rating NUMBER := 0;
  6.     nature_opinion_rating NUMBER := 0;
  7.     fiscal_yr NUMBER;
  8.     agencies_due_count NUMBER := 0;
  9.     months_delayed NUMBER := 0;
  10.     auditors_opinion VARCHAR2(50);
  11.     rating_timeliness_const NUMBER;
  12.     rating_nature_opinion_uq_const NUMBER;
  13.     rating_nature_opinion_ql_const NUMBER;
  14.     due_date DATE;
  15.     acceptable_flag VARCHAR(10);
  16.     months_due_after NUMBER := 0;
  17.     due_month_with_added NUMBER := 0;
  18.     due_yr_with_added NUMBER := 0;
  19.     due_date_with_added DATE;
  20.     effectivity_date DATE;
  21. BEGIN
  22.  
  23.     SELECT attribute1
  24.     INTO rating_timeliness_const
  25.     FROM adbpa_lookup_values
  26.     WHERE code = 'AEFS_RATING_TIMELINESS'
  27.     AND TYPE = 'AEFS_RATING';
  28.  
  29.     SELECT attribute1
  30.     INTO rating_nature_opinion_uq_const
  31.     FROM adbpa_lookup_values
  32.     WHERE code = 'AEFS_RATING_NATURE_OPINION_UQ'
  33.     AND TYPE = 'AEFS_RATING';
  34.  
  35.     SELECT attribute1
  36.     INTO rating_nature_opinion_ql_const
  37.     FROM adbpa_lookup_values
  38.     WHERE code = 'AEFS_RATING_NATURE_OPINION_QL'
  39.     AND TYPE = 'AEFS_RATING';
  40.  
  41.  
  42.     FOR agencies IN (SELECT DISTINCT(ca.agency_cd)
  43.                     FROM adbpa_cov_agency_setup setup, adbpa_covenant_accounts ca, adbpa_dmf_headers dmf
  44.                     WHERE setup.project_id = p_project_id
  45.                     AND setup.submit_aefs = 'Y'
  46.                     AND setup.agency_cd = ca.agency_cd
  47.                     AND ca.dmf_id = dmf.dmf_id
  48.                     AND setup.project_id = dmf.project_id
  49.                     AND SYSDATE BETWEEN setup.effective_start_date AND setup.effective_end_date
  50.                     AND SYSDATE BETWEEN ca.effective_start_date AND ca.effective_end_date
  51.                     AND SYSDATE BETWEEN dmf.effective_start_date AND dmf.effective_end_date)
  52.     LOOP
  53.         SELECT EXTRACT(YEAR FROM SYSDATE) INTO fiscal_yr FROM dual;
  54.  
  55.         /** Check if there is an exsting Fiscal year for the current year and if it is Accepted already **/
  56.         BEGIN
  57.             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
  58.             INTO due_date, acceptable_flag
  59.             FROM adbpa_covenant_accounts ca, adbpa_dmf_headers dmf
  60.             WHERE ca.dmf_id = dmf.dmf_id
  61.             AND dmf.project_id = p_project_id
  62.             AND cov_type_cd = 'COV-AFS'
  63.             AND agency_cd = agencies.agency_cd
  64.             AND fy = fiscal_yr
  65.             AND rejection_date IS NULL
  66.             AND SYSDATE BETWEEN ca.effective_start_date AND ca.effective_end_date
  67.             AND SYSDATE BETWEEN dmf.effective_start_date AND dmf.effective_end_date;
  68.         EXCEPTION
  69.             WHEN NO_DATA_FOUND THEN
  70.                 fiscal_yr := fiscal_yr - 1;
  71.         END;
  72.  
  73.         IF EXTRACT(YEAR FROM due_date) > fiscal_yr THEN
  74.             fiscal_yr := fiscal_yr - 1;
  75.         ELSIF (TO_DATE(due_date,'DD-MON-YYYY') > TO_DATE(SYSDATE,'DD-MON-YYYY')) AND (acceptable_flag = 'N') THEN
  76.             --do not compute rating if due date is future date and no yet Acceptable, rule raised duting UAT
  77.             fiscal_yr := fiscal_yr - 1;            
  78.         ELSIF (TO_DATE(due_date,'DD-MON-YYYY') > TO_DATE(SYSDATE,'DD-MON-YYYY')) AND (acceptable_flag = 'Y') THEN
  79.             --compute rating even if due date is still future date but already Accepted, rule raised duting UAT
  80.             fiscal_yr := fiscal_yr;
  81.         END IF;
  82.         agencies_due_count := agencies_due_count+1;
  83.         --check due date again using new FY
  84.         BEGIN
  85.             SELECT due_date, acceptable_flag
  86.             INTO due_date, acceptable_flag
  87.         FROM (
  88.             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
  89.                 , 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
  90.  
  91.             FROM adbpa_covenant_accounts ca, adbpa_dmf_headers dmf
  92.             WHERE ca.dmf_id = dmf.dmf_id
  93.             AND dmf.project_id = p_project_id
  94.             AND cov_type_cd = 'COV-AFS'
  95.             AND agency_cd = agencies.agency_cd
  96.             AND fy = fiscal_yr
  97.             AND rejection_date IS NULL
  98.             AND SYSDATE BETWEEN ca.effective_start_date AND ca.effective_end_date
  99.             AND SYSDATE BETWEEN dmf.effective_start_date AND dmf.effective_end_date
  100.             ) WHERE ranks = 1
  101.             ;
  102.         EXCEPTION
  103.             WHEN NO_DATA_FOUND THEN
  104.                 continue;
  105.         END;
  106.        
  107.         IF (TO_DATE(due_date,'DD-MON-YYYY') > TO_DATE(SYSDATE,'DD-MON-YYYY')) AND (acceptable_flag = 'N') THEN
  108.             EXIT;
  109.         END IF;
  110.  
  111.         --New rule add 1 qtr to due date or end fiscal yr
  112.         due_date := ADD_MONTHS(TRUNC(due_date), 3 );
  113.         SELECT EXTRACT(MONTH FROM due_date) INTO due_month_with_added FROM dual;
  114.         SELECT EXTRACT(YEAR FROM due_date) INTO due_yr_with_added FROM dual;
  115.  
  116.         --future due date but acceptable flag needs to be Y before deducting 1 yr
  117.         IF (due_month_with_added BETWEEN 10 AND 12 ) AND (acceptable_flag = 'N') THEN -- qtr 4
  118.             --deduct 1 again
  119.             fiscal_yr := fiscal_yr - 1;
  120.         ELSIF (TO_DATE(due_date,'DD-MON-YYYY') > TO_DATE(SYSDATE,'DD-MON-YYYY')) AND (acceptable_flag = 'N') THEN
  121.             --do not compute rating if due date is future date and no yet Acceptable, rule raised duting UAT
  122.             fiscal_yr := fiscal_yr - 1;
  123.         END IF;
  124.         --end New rule add 1 qtr to due date or end fiscal yr
  125.  
  126.  
  127.         -- get Nature of opinion, Due Date, Months delayed
  128.         BEGIN
  129.             SELECT auditors_opinion
  130.             , adbpa_covenant_accounts_pkg.Get_due_date(fy_end, fy, months_due_after, agency_cd) AS due_date
  131.             , 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
  132.             , NVL(acceptable_flag, 'N') acceptable_flag
  133.             , months_due_after
  134.             INTO auditors_opinion, due_date, months_delayed, acceptable_flag, months_due_after
  135.             FROM adbpa_covenant_accounts ca, adbpa_dmf_headers dmf
  136.             WHERE ca.dmf_id = dmf.dmf_id
  137.             AND dmf.project_id = p_project_id
  138.             AND cov_type_cd = 'COV-AFS'
  139.             AND agency_cd = agencies.agency_cd
  140.             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')))
  141.             AND rejection_date IS NULL
  142.             AND SYSDATE BETWEEN ca.effective_start_date AND ca.effective_end_date
  143.             AND SYSDATE BETWEEN dmf.effective_start_date AND dmf.effective_end_date;
  144.  
  145.         EXCEPTION
  146.             WHEN NO_DATA_FOUND THEN
  147.                 CONTINUE;
  148.             WHEN OTHERS  THEN
  149.                 CONTINUE;
  150.         END;
  151.  
  152.         --New rule add 1 qtr to due date or end fiscal yr
  153.         IF (acceptable_flag = 'N') THEN
  154.             due_date := ADD_MONTHS(TRUNC(due_date), 3 );
  155.             SELECT EXTRACT(MONTH FROM due_date) INTO due_month_with_added FROM dual;
  156.             SELECT EXTRACT(YEAR FROM due_date) INTO due_yr_with_added FROM dual;
  157.  
  158.             IF (due_month_with_added BETWEEN 1 AND 3 ) THEN -- qtr 1
  159.                 due_date_with_added := TO_DATE('01-APR-'||due_yr_with_added, 'DD-MON-YYYY');
  160.             ELSIF (due_month_with_added BETWEEN 4 AND 6 ) THEN -- qtr 2
  161.                 due_date_with_added := TO_DATE('01-JUL-'||due_yr_with_added, 'DD-MON-YYYY');
  162.             ELSIF (due_month_with_added BETWEEN 7 AND 9 ) THEN -- qtr 3
  163.                 due_date_with_added := TO_DATE('01-OCT-'||due_yr_with_added, 'DD-MON-YYYY');
  164.             ELSIF (due_month_with_added BETWEEN 10 AND 12 ) THEN -- qtr 3
  165.                 due_date_with_added := TO_DATE('01-JAN-'||(due_yr_with_added+1), 'DD-MON-YYYY');
  166.             END IF;
  167.  
  168.         ELSE
  169.             due_date_with_added := due_date;
  170.         END IF;
  171.         --end New rule add 1 qtr to due date or end fiscal yr
  172.  
  173.         --IF (TO_DATE(sysdate,'DD-MON-YYYY') >= TO_DATE(due_date_with_added,'DD-MON-YYYY')) THEN
  174.         IF (TO_DATE(SYSDATE,'DD-MON-YYYY') >= TO_DATE(due_date_with_added,'DD-MON-YYYY')) OR (acceptable_flag = 'Y') THEN
  175.  
  176.             IF (acceptable_flag = 'Y') THEN
  177.                 --compute Timeliness
  178.                 IF (months_delayed = 0) THEN
  179.                       timeliness_rating := timeliness_rating + rating_timeliness_const;
  180.                 ELSE
  181.                     timeliness_rating := timeliness_rating + 0; --adding this just to be clear, because of constant changes in requirements. remove if final and stable
  182.                 END IF;
  183.  
  184.                 --compute Nature of opinion
  185.                 IF (auditors_opinion = 'U') THEN
  186.                     nature_opinion_rating := nature_opinion_rating + rating_nature_opinion_uq_const; -- +10 Unqualified
  187.                 ELSIF (auditors_opinion = 'Q') THEN
  188.                     nature_opinion_rating := nature_opinion_rating + rating_nature_opinion_ql_const; -- +5 Qualified
  189.                 ELSE
  190.                     nature_opinion_rating := nature_opinion_rating + 0;
  191.                 END IF;
  192.             END IF;
  193.         ELSE
  194.             --If not yet due, give perfect score for each category
  195.             timeliness_rating := timeliness_rating + rating_timeliness_const; -- 15
  196.             nature_opinion_rating := nature_opinion_rating + rating_nature_opinion_uq_const; --10
  197.         END IF;
  198.  
  199.  
  200.     END LOOP;
  201.  
  202.     IF agencies_due_count > 0 THEN
  203.         --AEFS RATING = (timeliness total + nature of opinion total) / no. agencies due
  204.         aefs_rating_pct := (timeliness_rating + nature_opinion_rating) / agencies_due_count;
  205.     ELSE
  206.         --no due agency, check if newly effective
  207.         SELECT MIN(effectivity_date) effectivity_date INTO effectivity_date
  208.         FROM  apps.adbpa_financing_Headers_V afh
  209.             , apps.adbpa_financing_details_v afd
  210.         WHERE afh.project_Financing_id = afd.project_Financing_id
  211.         AND  afd.financing_source_Cd IN ('ADB', 'COFINANCING')
  212.         AND  afd.approval_number IS NOT NULL
  213.         AND  afh.project_id = p_project_id
  214.         GROUP BY afh.project_id, afh.project_financing_id;
  215.  
  216.         effectivity_date := ADD_MONTHS(TRUNC(effectivity_date), 15 ); -- plus 1 yr and 1 qtr before rating is computed
  217.  
  218.         IF (TO_DATE(effectivity_date,'DD-MON-YYYY') >= TO_DATE(SYSDATE,'DD-MON-YYYY')) THEN            
  219.             aefs_rating_pct := rating_timeliness_const + rating_nature_opinion_uq_const;
  220.         END IF;
  221.     END IF;
  222.  
  223.     RETURN ROUND(aefs_rating_pct, 2);
  224.  
  225. EXCEPTION
  226.    WHEN NO_DATA_FOUND THEN
  227.       RETURN 0;
  228.    WHEN OTHERS THEN
  229.       RETURN 0;
  230. END GET_AEFS_RATING_FY;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top