DECLARE QUERY VARCHAR2(32767); FIN_QUERY VARCHAR2(32767); CURSOR C IS (select distinct PORTFOLIO from MPE_TEST1); CURSOR C2 IS SELECT DISTINCT PORTFOLIO_LEVEL, PORTFOLIO FROM MPE_TEST1 ORDER BY PORTFOLIO_LEVEL; TYPE ARRAY IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; PORTFOLIO_LEVEL ARRAY; IDX NUMBER :=1; BEGIN QUERY := 'SELECT '; for R IN C LOOP IDX := 1; FOR R2 IN C2 LOOP IF(R2.PORTFOLIO = R.PORTFOLIO) THEN PORTFOLIO_LEVEL(IDX) := R2.PORTFOLIO_LEVEL; IDX := IDX + 1; END IF; END LOOP; QUERY := QUERY || 'CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(1) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_ASS_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(1) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALES_ASS_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(2) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_MST_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(2) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALE_MST_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(3) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_PROF_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(3) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALE_PROF_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(4) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_ASS_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(4) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_ASS_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(5) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_MST_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(5) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_MST_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(6) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_PROF_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(6) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_PROF_EXP_DT,'; END LOOP; QUERY := SUBSTRING(QUERY,0,LENGTH(QUERY)-1); QUERY := QUERY || ' FROM MPE_TEST1 F '; --DBMS_OUTPUT.PUT_LINE('QUERY--'||QUERY); FIN_QUERY := 'select '; FOR R IN C LOOP FIN_QUERY := FIN_QUERY || '(case when '||R.PORTFOLIO||'_TECH_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_TA_LEVEL, (case when '||R.PORTFOLIO||'_SALE_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_SA_LEVEL, (case when '||R.PORTFOLIO||'_TECH_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_TM_LEVEL, (case when '||R.PORTFOLIO||'_SALE_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_SM_LEVEL, (case when '||R.PORTFOLIO||'_TECH_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_TP_LEVEL, (case when '||R.PORTFOLIO||'_SALE_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_SP_LEVEL, '; END LOOP; FIN_QUERY := FIN_QUERY || ' i.* from (' || QUERY; FIN_QUERY := FIN_QUERY || ') I '; DBMS_OUTPUT.PUT_LINE(FIN_QUERY); EXECUTE IMMEDIATE FIN_QUERY; END;
DECLARE QUERY VARCHAR2(32767); QUERY2 CLOB; FIN_QUERY CLOB; CURSOR C IS (select distinct PORTFOLIO from MPE_TEST1); CURSOR C2 IS SELECT DISTINCT PORTFOLIO_LEVEL, PORTFOLIO FROM MPE_TEST1 ORDER BY PORTFOLIO_LEVEL; TYPE ARRAY IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; PORTFOLIO_LEVEL ARRAY; IDX NUMBER :=1; BEGIN QUERY := 'SELECT DISTINCT ACCOUNT_ID,'; for R IN C LOOP IDX := 1; FOR R2 IN C2 LOOP IF(R2.PORTFOLIO = R.PORTFOLIO) THEN PORTFOLIO_LEVEL(IDX) := R2.PORTFOLIO_LEVEL; IDX := IDX + 1; END IF; END LOOP; QUERY := QUERY || 'CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(1) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_ASS_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(1) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALES_ASS_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(2) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_MST_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(2) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALE_MST_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(3) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_PROF_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(3) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALE_PROF_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(4) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_ASS_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(4) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_ASS_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(5) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_MST_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(5) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_MST_EXP_DT, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(6) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_PROF_C, CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(6) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_PROF_EXP_DT,'; END LOOP; QUERY := SUBSTRING(QUERY,0,LENGTH(QUERY)-1); QUERY := QUERY || ' FROM MPE_TEST1 F '; --DBMS_OUTPUT.PUT_LINE('QUERY--'||QUERY); FIN_QUERY := 'select i.ACCOUNT_ID, '; FOR R IN C LOOP FIN_QUERY := FIN_QUERY || '(case when '||R.PORTFOLIO||'_TECH_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_TA_LEVEL, (case when '||R.PORTFOLIO||'_SALE_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_ASS_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_SA_LEVEL, (case when '||R.PORTFOLIO||'_TECH_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_TM_LEVEL, (case when '||R.PORTFOLIO||'_SALE_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_MST_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_SM_LEVEL, (case when '||R.PORTFOLIO||'_TECH_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_TECH_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_TP_LEVEL, (case when '||R.PORTFOLIO||'_SALE_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'') then ''Elite Specialist, Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'') then ''Specialist, Authorized'' when '||R.PORTFOLIO||'_SALE_PROF_C >= (select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'') then ''Authorized'' else ''Authorized'' end) '||R.PORTFOLIO||'_SP_LEVEL, '; END LOOP; FIN_QUERY := FIN_QUERY || ' i.* from (' || QUERY; FIN_QUERY := FIN_QUERY || ') I '; --DBMS_OUTPUT.PUT_LINE(FIN_QUERY); QUERY2 := 'SELECT '; FOR R IN C LOOP QUERY2 := QUERY2 || 'MAX('||R.PORTFOLIO||'_TA_LEVEL) '||R.PORTFOLIO||'_TA_LEVEL, MAX('||R.PORTFOLIO||'_SA_LEVEL) '||R.PORTFOLIO||'_SA_LEVEL, MAX('||R.PORTFOLIO||'_TM_LEVEL) '||R.PORTFOLIO||'_TM_LEVEL, MAX('||R.PORTFOLIO||'_SM_LEVEL) '||R.PORTFOLIO||'_SM_LEVEL, MAX('||R.PORTFOLIO||'_TP_LEVEL) '||R.PORTFOLIO||'_TP_LEVEL, MAX('||R.PORTFOLIO||'_SP_LEVEL) '||R.PORTFOLIO||'_SP_LEVEL, SUM('||R.PORTFOLIO||'_SALE_ASS_C ) '||R.PORTFOLIO||'_SALE_ASS, MIN('||R.PORTFOLIO||'_SALES_ASS_EXP_DT) '||R.PORTFOLIO||'_SALES_ASS_EXP_D, SUM('||R.PORTFOLIO||'_TECH_ASS_C) '||R.PORTFOLIO||'_TECH_ASS, MIN('||R.PORTFOLIO||'_TECH_ASS_EXP_DT) '||R.PORTFOLIO||'_TECH_ASS_EXP_D, SUM('||R.PORTFOLIO||'_SALE_PROF_C) '||R.PORTFOLIO||'_SALE_PROF, MIN('||R.PORTFOLIO||'_SALE_PROF_EXP_DT) '||R.PORTFOLIO||'_SALE_PROF_EXP_D, SUM('||R.PORTFOLIO||'_TECH_PROF_C) '||R.PORTFOLIO||'_TECH_PROF, MIN('||R.PORTFOLIO||'_TECH_PROF_EXP_DT) '||R.PORTFOLIO||'_TECH_PROF_EXP_D, SUM('||R.PORTFOLIO||'_SALE_MST_C) '||R.PORTFOLIO||'_SALE_MST, MIN('||R.PORTFOLIO||'_SALE_MST_EXP_DT) '||R.PORTFOLIO||'_SALE_MST_EXP_D, SUM('||R.PORTFOLIO||'_TECH_MST_C) '||R.PORTFOLIO||'_TECH_MST, MIN('||R.PORTFOLIO||'_TECH_MST_EXP_DT) '||R.PORTFOLIO||'_TECH_MST_EXP_D,'; END LOOP; QUERY2 := SUBSTR(QUERY2,0,LENGTH(QUERY2)-1); QUERY2 := QUERY2 || ' FROM ( ' || FIN_QUERY || ') '; DBMS_OUTPUT.PUT_LINE('FINAL--'||QUERY2); END;
Ads