SQLize | PHPize | SQLtest

-------------------------------------------------------- -- DDL for Procedure SP_ASIA_BOC_SUMMARY_QUARTERLY_TEST -------------------------------------------------------- set define off; CREATE OR REPLACE EDITIONABLE PROCEDURE BERT.SP_ASIA_BOC_SUMMARY_QUARTERLY_TEST ( p_bfy in varchar2, p_fy in number, p_fm in number, p_fund in varchar2, p_fund_center in varchar2, p_appropriation in varchar2, p_program in varchar2, p_is_obligations in number, p_auth_fund_centers in varchar2, resultset out sys_refcursor ) AS p_sql varchar2(32767) := ''; p_quarter varchar2(32767) := ''; BEGIN if p_is_obligations = 1 then p_quarter := 'Obs Q' || to_char( ceil( p_fm/3 ) ); else p_quarter := 'Coms Q' || to_char( ceil( p_fm/3 ) ); end if; p_sql := 'FUND_CENTER,FUND , VALUE, CARRYOVER, VALUE_QUARTER, CARRYOVER_QUARTER from ( '; p_sql := p_sql || ' select FY, FM, APPN, d.FUND_CENTER AS FUND_CENTER, d.FUND AS FUND, FUND_SOURCE_YEAR, FUND_ACCOUNT, UNIQUE_FUND_ID, d.PROGRAM, d.BOC '; p_sql := p_sql || ' , fc.TITLE as FUND_CENTER_TITLE, f.TITLE as FUND_TITLE, p.TITLE as PROGRAM_TITLE, b.TITLE as BOC_TITLE '; p_sql := p_sql || ' , substr(BOC_GROUP, 1, 2) as BOC_GROUP_ORDER, substr(BOC_GROUP, 4) as BOC_GROUP '; p_sql := p_sql || ' , substr(BOC_CATEGORY, 1, 2) as BOC_CATEGORY_ORDER, substr(BOC_CATEGORY, 4) as BOC_CATEGORY , VALUE, CARRYOVER '; p_sql := p_sql || ' , case when ' || to_char(p_fm) || ' between 1 and 3 and FM between 1 and 3 then VALUE '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 4 and 6 and FM between 4 and 6 then VALUE '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 7 and 9 and FM between 7 and 9 then VALUE '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 10 and 12 and FM between 10 and 12 then VALUE '; p_sql := p_sql || ' else 0 end as VALUE_QUARTER '; p_sql := p_sql || ' , case when ' || to_char(p_fm) || ' between 1 and 3 and FM between 1 and 3 then CARRYOVER '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 4 and 6 and FM between 4 and 6 then CARRYOVER '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 7 and 9 and FM between 7 and 9 then CARRYOVER '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 10 and 12 and FM between 10 and 12 then CARRYOVER '; p_sql := p_sql || ' else 0 end as CARRYOVER_QUARTER from ( '; p_sql := replace(replace(p_sql, ' ', ' '), ' ,', ','); p_sql := p_sql || ' select FISCAL_YEAR as FY, FISCAL_MONTH as FM, BEGIN_FISCAL_YEAR as BFY '; p_sql := p_sql || ' , APPROPRIATION as APPN, DIVISION as FUND_CENTER, FUND, substr(FUND, 1, 3) as FUND_SOURCE_YEAR '; p_sql := p_sql || ' , substr(FUND, 5, 4) as FUND_ACCOUNT, FUND_CLASS as UNIQUE_FUND_ID, PROGRAM, BOC '; p_sql := p_sql || ' , case when substr(BOC, 1, 2) in (''11'', ''12'', ''13'') then ''01-Pay'' else ''02-Non-Pay'' end BOC_GROUP '; p_sql := p_sql || ' , case when substr(BOC, 1, 1) in (''1'') then ''01-Pay (BOC 1*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''21'') then ''02-Travel (BOC 21*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''22'') then ''03-Transportation of Things (BOC 22*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 3) in (''231'', ''232'') then ''04-Rent (BOC 231* and 232*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 3) in (''233'') then ''05-Communications/Utilities (BOC 233*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''24'') then ''06-Printing (BOC 24*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''25'') then ''07-Other Contr. (BOC 25*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''26'') then ''08-Supplies (BOC 26*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 1) in (''3'') then ''09-Equipment (BOC 3*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 1) in (''4'') then ''10-Grants and Fixed Charges (BOC 4*)'' '; p_sql := p_sql || ' else ''11-Other'' end BOC_CATEGORY '; p_sql := p_sql || ' , case when to_char(FISCAL_YEAR) = BEGIN_FISCAL_YEAR or BEGIN_FISCAL_YEAR = ''XXX'' then case when ' || to_char(p_is_obligations) || ' = 1 then OBLIGATED else COMMITMENTS end '; p_sql := p_sql || ' else 0 '; p_sql := p_sql || ' end as VALUE '; p_sql := p_sql || ' , case when to_char(FISCAL_YEAR) = BEGIN_FISCAL_YEAR or BEGIN_FISCAL_YEAR = ''XXX'' then 0 '; p_sql := p_sql || ' else case when ' || to_char(p_is_obligations) || ' = 1 then OBLIGATED else COMMITMENTS end '; p_sql := p_sql || ' end as CARRYOVER '; p_sql := p_sql || ' from MV_ASIA_RESOURCE_FINAL r where ((' || to_char(p_is_obligations) || ' = 1 and OBLIGATED <> 0) or (' || to_char(p_is_obligations) || ' = 0 and COMMITMENTS <> 0)) '; p_sql := p_sql || ' and r.FISCAL_YEAR BETWEEN ' || to_char(p_fy - 3) || ' AND ' || to_char(p_fy) || ' AND (r.FISCAL_MONTH <= (ceil(' || to_char(p_fm) || '/3) * 3)) '; -- p_sql := p_sql || ' and ((''' || p_from_inception || ''' = ''0'' AND r.FISCAL_YEAR BETWEEN ' || to_char(p_fy - 3) || ' AND ' || to_char(p_fy) || ' AND (r.FISCAL_MONTH <= (ceil(' || to_char(p_fm) || '/3) * 3))) '; -- p_sql := p_sql || ' OR (''' || p_from_inception || ''' != ''0'' AND r.FISCAL_MONTH > 0 AND (r.FISCAL_YEAR < ' || to_char(p_fy) || ' OR (r.FISCAL_YEAR = ' || to_char(p_fy) || ' AND r.FISCAL_MONTH <= (ceil(' || to_char(p_fm) || '/3) * 3))))) '; p_sql := p_sql || ' AND (TRIM(''' || p_bfy || ''') IS NULL '; p_sql := p_sql || ' OR r.BEGIN_FISCAL_YEAR IN '; p_sql := p_sql || ' (SELECT REGEXP_SUBSTR(''' || p_bfy || ''', ''[^,]+'',1,ROWNUM) '; p_sql := p_sql || ' FROM dual CONNECT BY REGEXP_SUBSTR(''' || p_bfy || ''', ''[^,]+'', 1, LEVEL) IS NOT NULL)) '; p_sql := p_sql || ' AND ((r.TERM = 0) OR (r.TERM != 0 AND TO_NUMBER(r.BEGIN_FISCAL_YEAR) + (r.TERM - 1) + 5 >= TO_NUMBER(''' || p_fy || '''))) '; p_sql := p_sql || ' AND (''' || p_fund || ''' IS NULL OR r.FUND_CLASS = ''' || p_fund || ''') '; p_sql := p_sql || ' AND ((''' || p_appropriation || ''' IS NULL OR r.APPROPRIATION = ''' || p_appropriation || ''') OR (''' || p_appropriation || ''' IS NULL OR r.APPROPRIATION_GROUP = ''' || p_appropriation || ''')) '; p_sql := p_sql || ' AND (''' || p_fund_center || ''' IS NULL OR r.FUND_CENTER LIKE ''' || p_fund_center || ''' || ''%'') '; p_sql := p_sql || ' AND (''' || p_program || ''' is null or substr(program, 0, 7) in '; p_sql := p_sql || ' (select regexp_substr(''' || p_program || ''',''[^,]+'', 1, level) from dual connect by regexp_substr(''' || p_program || ''', ''[^,]+'', 1, level) is not null)) '; p_sql := p_sql || ' AND (''' || p_auth_fund_centers || ''' IS NULL OR EXISTS '; p_sql := p_sql || ' (SELECT 1 FROM (SELECT REGEXP_SUBSTR(''' || p_auth_fund_centers || ''',''[^,]+'', 1, LEVEL) AS fund_center '; p_sql := p_sql || ' FROM DUAL CONNECT BY REGEXP_SUBSTR(''' || p_auth_fund_centers || ''', ''[^,]+'', 1, LEVEL) IS NOT NULL) d '; p_sql := p_sql || ' WHERE r.fund_center LIKE d.fund_center || ''%'')) ) d '; p_sql := p_sql || ' left join MV_ASIA_FUND_CENTER fc on fc.FUND_CENTER = trim(d.FUND_CENTER) '; p_sql := p_sql || ' left join MV_ASIA_FUND f on f.FUND = trim(d.FUND) '; p_sql := p_sql || ' left join MV_ASIA_PROGRAM p on p.PROGRAM = trim(d.PROGRAM) '; p_sql := p_sql || ' left join MV_ASIA_BOC b on b.FISCAL_YEAR = d.FY and b.BOC = trim(d.BOC) and b.BOC_ROLLUP = least(4, length(trim(d.BOC))) '; p_sql := p_sql || ' ) d ) '; p_sql := replace(replace(p_sql, ' ', ' '), ' ,', ','); p_sql := p_sql || ' select decode(CATEGORY, '''', ''Total'', CATEGORY) as "Category",FUND as FUND ,FUND_CENTER as FUND_CENTER , sum(FY1_ACT_CY + FY1_ACT_CAR) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Actuals Total" '; p_sql := p_sql || ' , sum(FY1_ACT_CY) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Actuals CY" '; p_sql := p_sql || ' , sum(FY1_ACT_CAR) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Actuals Carryover" '; p_sql := p_sql || ' , sum(FY1_QUA_CY + FY1_QUA_CAR) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Only Total" '; p_sql := p_sql || ' , sum(FY1_QUA_CY) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Only CY" '; p_sql := p_sql || ' , sum(FY1_QUA_CAR) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Only Carryover" '; p_sql := p_sql || ' , sum(FY2_ACT_CY + FY2_ACT_CAR) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Actuals Total" '; p_sql := p_sql || ' , sum(FY2_ACT_CY) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Actuals CY" '; p_sql := p_sql || ' , sum(FY2_ACT_CAR) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Actuals Carryover" '; p_sql := p_sql || ' , sum(FY2_QUA_CY + FY2_QUA_CAR) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Only Total" '; p_sql := p_sql || ' , sum(FY2_QUA_CY) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Only CY" '; p_sql := p_sql || ' , sum(FY2_QUA_CAR) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Only Carryover" '; p_sql := p_sql || ' , sum(FY3_ACT_CY + FY3_ACT_CAR) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Actuals Total" '; p_sql := p_sql || ' , sum(FY3_ACT_CY) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Actuals CY" '; p_sql := p_sql || ' , sum(FY3_ACT_CAR) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Actuals Carryover" '; p_sql := p_sql || ' , sum(FY3_QUA_CY + FY3_QUA_CAR) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Only Total" '; p_sql := p_sql || ' , sum(FY3_QUA_CY) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Only CY" '; p_sql := p_sql || ' , sum(FY3_QUA_CAR) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Only Carryover" '; p_sql := p_sql || ' , sum(FY4_ACT_CY + FY4_ACT_CAR) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Actuals Total" '; p_sql := p_sql || ' , sum(FY4_ACT_CY) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Actuals CY" '; p_sql := p_sql || ' , sum(FY4_ACT_CAR) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Actuals Carryover" '; p_sql := p_sql || ' , sum(FY4_QUA_CY + FY4_QUA_CAR) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Only Total" '; p_sql := p_sql || ' , sum(FY4_QUA_CY) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Only CY" '; p_sql := p_sql || ' , sum(FY4_QUA_CAR) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Only Carryover" from ( '; p_sql := replace(replace(p_sql, ' ', ' '), ' ,', ','); p_sql := p_sql || ' select CATEGORY_ORDER, CATEGORY,FUND,FUND_CENTER '; p_sql := p_sql || ' , VALUE as FY1_ACT_CY, CARRYOVER as FY1_ACT_CAR, VALUE_QUARTER as FY1_QUA_CY, CARRYOVER_QUARTER as FY1_QUA_CAR '; p_sql := p_sql || ' , 0 as FY2_ACT_CY, 0 as FY2_ACT_CAR, 0 as FY2_QUA_CY, 0 as FY2_QUA_CAR '; p_sql := p_sql || ' , 0 as FY3_ACT_CY, 0 as FY3_ACT_CAR, 0 as FY3_QUA_CY, 0 as FY3_QUA_CAR '; p_sql := p_sql || ' , 0 as FY4_ACT_CY, 0 as FY4_ACT_CAR, 0 as FY4_QUA_CY, 0 as FY4_QUA_CAR '; p_sql := p_sql || ' from base where FY = (' || to_char(p_fy - 0) || ') '; p_sql := p_sql || ' union all select CATEGORY_ORDER, CATEGORY,FUND,FUND_CENTER '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , VALUE, CARRYOVER, VALUE_QUARTER, CARRYOVER_QUARTER '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' from base where FY = (' || to_char(p_fy - 1) || ') '; p_sql := p_sql || ' union all select CATEGORY_ORDER, CATEGORY ,FUND,FUND_CENTER'; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , VALUE, CARRYOVER, VALUE_QUARTER, CARRYOVER_QUARTER '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' from base where FY = (' || to_char(p_fy - 2) || ') '; p_sql := p_sql || ' union all select CATEGORY_ORDER, CATEGORY ,FUND,FUND_CENTER'; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , VALUE, CARRYOVER, VALUE_QUARTER, CARRYOVER_QUARTER '; p_sql := p_sql || ' from base where FY = (' || to_char(p_fy - 3) || ') ) d '; -- p_sql := replace(replace(p_sql, ' ', ' '), ' ,', ','); -- OPEN resultset for ' with base as ( select FY, BOC_GROUP_ORDER as CATEGORY_ORDER, BOC_GROUP as CATEGORY ' || p_sql || ' group by ROLLUP( CATEGORY ) order by substr(CATEGORY, 2, 2) '; p_sql := replace(p_sql, 'decode(CATEGORY, '''', ''Total'', CATEGORY) as "Category"', 'CATEGORY as "Category"'); OPEN resultset for ' with base as ( select FY, BOC_CATEGORY_ORDER as CATEGORY_ORDER, BOC_CATEGORY as CATEGORY, FUND,FUND_CENTER, ' || p_sql || ' group by CATEGORY_ORDER, CATEGORY order by CATEGORY_ORDER, CATEGORY '; -- p_sql := replace(p_sql, 'CATEGORY as "Category"', 'CATEGORY as "BOC", TITLE as "Title"'); -- p_sql := replace(p_sql, 'CATEGORY_ORDER, CATEGORY', 'CATEGORY_ORDER, CATEGORY, TITLE'); -- OPEN resultset3 for ' with base as ( select FY, 1 as CATEGORY_ORDER, BOC as CATEGORY, BOC_TITLE as TITLE ' || p_sql || ' group by CATEGORY_ORDER, CATEGORY, TITLE order by CATEGORY_ORDER, CATEGORY, TITLE '; END; / GRANT EXECUTE ON BERT.SP_ASIA_BOC_SUMMARY_QUARTERLY_TEST TO BERT_VIEW;