[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_allSelect VARCHAR2(1000);
l_allSelect := ' MINISITE_ID,ID,';
/* type is sequential. Depending upon the Period Type selected the */
/* Value of TIME_PERIOD and the whereclause would change */
/*************************************************************************/
/*************************************************************************/
/* VIEWBY : Period Name */
/* IBE_ATTR1 : Period Name */
/* IBE_VAL1 : Previous Total */
/* IBE_VAL2 : Current Total */
/* IBE_VAL3 : Previous Assisted */
/* IBE_VAL4 : Current Assisted */
/* IBE_VAL5 : Previous Unassisted */
/* IBE_VAL6 : Current Unassisted */
/*************************************************************************/
l_custom_sql :='SELECT FACT.NAME VIEWBY,FACT.NAME IBE_ATTR1,'||
' SUM(NVL(AMOUNT_TOTAL,0)) IBE_VAL2,'||
' SUM(NVL(AMOUNT_ASSIST,0)) IBE_VAL4,'||
' SUM(NVL(AMOUNT_UNASSIST,0)) IBE_VAL6,'||
' NULL IBE_VAL1,'||
' NULL IBE_VAL3,'||
' NULL IBE_VAL5'||
' FROM ('||
' SELECT TIME.NAME,'||l_allSelect ||
' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSIST,'||
' REPORT_DATE'||
' FROM'||
' (SELECT' ||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID'||
' FROM'||
' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) ' ||l_whereclause|| ') TIME,'||
l_tableList||
' WHERE'||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter||') FACT'||
' WHERE FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
' GROUP BY FACT.NAME, REPORT_DATE ORDER BY REPORT_DATE';
l_custom_sql := 'SELECT MAX(NAME) VIEWBY,MAX(NAME) IBE_ATTR1,'||
' SUM(NVL(CT_AMOUNT,0)) IBE_VAL2,'||
' SUM(NVL(CA_AMOUNT,0)) IBE_VAL4,'||
' SUM(NVL(CU_AMOUNT,0)) IBE_VAL6,'||
' SUM(NVL(PT_AMOUNT,0)) IBE_VAL1,'||
' SUM(NVL(PA_AMOUNT,0)) IBE_VAL3,'||
' SUM(NVL(PU_AMOUNT,0)) IBE_VAL5'||
' FROM ('||
' SELECT'|| l_allSelect ||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN null else fact.name end) NAME,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN fact.year445_id+1 else fact.year445_id end) ENT_YEAR_ID,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.AMOUNT_TOTAL else 0 end) PT_AMOUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.AMOUNT_TOTAL else 0 end) CT_AMOUNT,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.AMOUNT_ASSISTED else 0 end) PA_AMOUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.AMOUNT_ASSISTED else 0 end) CA_AMOUNT,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.AMOUNT_UNASSISTED else 0 end) PU_AMOUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.AMOUNT_UNASSISTED else 0 end) CU_AMOUNT,'||
' SEQUENCE'||
' FROM ('||
' SELECT'|| l_allSelect ||
' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSISTED,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSISTED,'||
' TIME.PERIOD_TYPE_ID,'||
' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
' TIME.YEAR445_ID,TIME.NAME'||
' FROM'||
' ( SELECT ' ||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID, '||
' TIME_PERIOD.SEQUENCE,'||
' TIME_PERIOD.START_DATE,'||
' TIME.year445_id'||
' FROM '||
' FII_TIME_RPT_STRUCT_V CAL,'||
' FII_TIME_WEEK TIME_PERIOD,'||
' FII_TIME_p445 TIME'||
' WHERE '||
' BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE)'||
' AND (TIME_PERIOD.start_date between :l_pprev_start and :l_pcur_start OR'||
' TIME_PERIOD.start_date between :l_prev_start and :l_cur_start)'||
' AND TIME.period445_id = TIME_PERIOD.period445_id ) TIME,'||
l_tableList||
' WHERE '||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter||' ) FACT'||
' WHERE'||
' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
' ORDER BY ENT_YEAR_ID,SEQUENCE';
l_custom_sql :='SELECT MAX(NAME) VIEWBY,MAX(NAME) IBE_ATTR1,'||
' SUM(NVL(CT_AMOUNT,0)) IBE_VAL2,'||
' SUM(NVL(CA_AMOUNT,0)) IBE_VAL4,'||
' SUM(NVL(CU_AMOUNT,0)) IBE_VAL6,'||
' SUM(NVL(PT_AMOUNT,0)) IBE_VAL1,'||
' SUM(NVL(PA_AMOUNT,0)) IBE_VAL3,'||
' SUM(NVL(PU_AMOUNT,0)) IBE_VAL5'||
' FROM ('||
' SELECT'||l_allSelect ||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN null else fact.name end) NAME,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN fact.ent_year_id+1 else fact.ent_year_id end) ENT_YEAR_ID,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.AMOUNT_TOTAL else 0 end) PT_AMOUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.AMOUNT_TOTAL else 0 end) CT_AMOUNT,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.AMOUNT_ASSISTED else 0 end) PA_AMOUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.AMOUNT_ASSISTED else 0 end) CA_AMOUNT,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.AMOUNT_UNASSISTED else 0 end) PU_AMOUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.AMOUNT_UNASSISTED else 0 end) CU_AMOUNT,'||
' SEQUENCE'||
' FROM ('||
' SELECT'|| l_allSelect ||
' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSISTED,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSISTED,'||
' TIME.PERIOD_TYPE_ID,'||
' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
' TIME.ENT_YEAR_ID,TIME.NAME'||
' FROM'||
' (SELECT' ||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID,'||
' TIME_PERIOD.SEQUENCE,'||
' TIME_PERIOD.START_DATE,'||
' TIME_PERIOD.ENT_YEAR_ID'||
' FROM'||
' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) '|| l_whereclause|| ') TIME,'||
l_tableList||
' WHERE'||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID (+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter ||') FACT'||
' WHERE '||
' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'||l_allWhere ||
' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
' ORDER BY ENT_YEAR_ID, SEQUENCE';
l_allSelect VARCHAR2(1000);
l_allSelect := ' MINISITE_ID,ID,';
/* type is sequential. Depending upon the Period Type selected the */
/* Value of TIME_PERIOD and the whereclause would change */
/*************************************************************************/
/*************************************************************************/
/* VIEWBY : Period Name */
/* IBE_ATTR1 : Period Name */
/* IBE_VAL1 : Previous Total */
/* IBE_VAL2 : Current Total */
/* IBE_VAL3 : Previous Assisted */
/* IBE_VAL4 : Current Assisted */
/* IBE_VAL5 : Previous Unassisted */
/* IBE_VAL6 : Current Unassisted */
/*************************************************************************/
l_custom_sql :='SELECT FACT.NAME VIEWBY,FACT.NAME IBE_ATTR1,'||
' decode(SUM(NVL(TOTAL_CNT,0)),0,0,(SUM(NVL(AMOUNT_TOTAL,0))/SUM(NVL(TOTAL_CNT,0)))) IBE_VAL2,'||
' decode(SUM(NVL(ASSIST_CNT,0)),0,0,(SUM(NVL(AMOUNT_ASSIST,0))/SUM(NVL(ASSIST_CNT,0)))) IBE_VAL4,'||
' decode(SUM(NVL(UNASSIST_CNT,0)),0,0,(SUM(NVL(AMOUNT_UNASSIST,0))/SUM(NVL(UNASSIST_CNT,0)))) IBE_VAL6,'||
' NULL IBE_VAL1,'||
' NULL IBE_VAL3,'||
' NULL IBE_VAL5'||
' FROM ('||
' SELECT '||l_allSelect ||
' TIME.NAME, NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' TOT_COUNT ,0) TOTAL_CNT,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' TOT_COUNT ,0) ASSIST_CNT,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' TOT_COUNT ,0) UNASSIST_CNT,'||
' TIME.PERIOD_TYPE_ID,TIME.TIME_ID,TIME.REPORT_DATE'||
' FROM'||
' (SELECT'||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID'||
' FROM'||
' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) ' ||l_whereclause|| ') TIME,'||
l_tableList||
' WHERE'||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter||') FACT'||
' WHERE FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
' GROUP BY FACT.NAME, REPORT_DATE ORDER BY REPORT_DATE';
l_custom_sql :='SELECT MAX(NAME) VIEWBY, MAX(NAME) IBE_ATTR1,'||
' decode(SUM(NVL(CT_COUNT,0)),0,0,(SUM(NVL(CT_AMOUNT,0))/SUM(CT_COUNT))) IBE_VAL2,'||
' decode(SUM(NVL(CA_COUNT,0)),0,0,(SUM(NVL(CA_AMOUNT,0))/SUM(CA_COUNT))) IBE_VAL4,'||
' decode(SUM(NVL(CU_COUNT,0)),0,0,(SUM(NVL(CU_AMOUNT,0))/SUM(CU_COUNT))) IBE_VAL6,'||
' decode(SUM(NVL(PT_COUNT,0)),0,0,(SUM(NVL(PT_AMOUNT,0))/SUM(PT_COUNT))) IBE_VAL1,'||
' decode(SUM(NVL(PA_COUNT,0)),0,0,(SUM(NVL(PA_AMOUNT,0))/SUM(PA_COUNT))) IBE_VAL3,'||
' decode(SUM(NVL(PU_COUNT,0)),0,0,(SUM(NVL(PU_AMOUNT,0))/SUM(PU_COUNT))) IBE_VAL5'||
' FROM ('||
' SELECT'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN null else fact.name end) NAME,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN fact.year445_id+1 else fact.year445_id end) ENT_YEAR_ID,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.AMOUNT_TOTAL else 0 end) PT_AMOUNT,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.TOTAL_CNT else 0 end) PT_COUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.AMOUNT_TOTAL else 0 end) CT_AMOUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.TOTAL_CNT else 0 end) CT_COUNT,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.AMOUNT_ASSIST else 0 end) PA_AMOUNT,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.ASSIST_CNT else 0 end) PA_COUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.AMOUNT_ASSIST else 0 end) CA_AMOUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.ASSIST_CNT else 0 end) CA_COUNT,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.AMOUNT_UNASSIST else 0 end) PU_AMOUNT,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.UNASSIST_CNT else 0 end) PU_COUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.AMOUNT_UNASSIST else 0 end) CU_AMOUNT,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.UNASSIST_CNT else 0 end) CU_COUNT,'||
' REPORT_DATE,'||
' SEQUENCE'||
' FROM ('||
' SELECT'|| l_allSelect ||
' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' TOT_COUNT,0) TOTAL_CNT,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' TOT_COUNT,0) ASSIST_CNT,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' TOT_COUNT,0) UNASSIST_CNT,'||
' TIME.PERIOD_TYPE_ID,'||
' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
' TIME.YEAR445_ID,TIME.NAME,TIME.REPORT_DATE'||
' FROM '||
' ( SELECT '||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID, '||
' TIME_PERIOD.SEQUENCE,'||
' TIME_PERIOD.START_DATE,'||
' TIME.year445_id'||
' FROM '||
' FII_TIME_RPT_STRUCT_V CAL,'||
' FII_TIME_WEEK TIME_PERIOD,'||
' FII_TIME_p445 TIME'||
' WHERE '||
' BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE)'||
' AND (TIME_PERIOD.start_date between :l_pprev_start and :l_pcur_start OR'||
' TIME_PERIOD.start_date between :l_prev_start and :l_cur_start)'||
' AND TIME.period445_id = TIME_PERIOD.period445_id ) TIME,'||
l_tableList||
' WHERE '||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter||' ) FACT'||
' WHERE'||
' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
' ORDER BY ENT_YEAR_ID,SEQUENCE';
l_custom_sql :='SELECT MAX(NAME) VIEWBY,MAX(NAME) IBE_ATTR1,'||
' decode(SUM(NVL(CT_COUNT,0)),0,0,(SUM(NVL(CT_AMOUNT,0))/SUM(CT_COUNT))) IBE_VAL2,'||
' decode(SUM(NVL(CA_COUNT,0)),0,0,(SUM(NVL(CA_AMOUNT,0))/SUM(CA_COUNT))) IBE_VAL4,'||
' decode(SUM(NVL(CU_COUNT,0)),0,0,(SUM(NVL(CU_AMOUNT,0))/SUM(CU_COUNT))) IBE_VAL6,'||
' decode(SUM(NVL(PT_COUNT,0)),0,0,(SUM(NVL(PT_AMOUNT,0))/SUM(PT_COUNT))) IBE_VAL1,'||
' decode(SUM(NVL(PA_COUNT,0)),0,0,(SUM(NVL(PA_AMOUNT,0))/SUM(PA_COUNT))) IBE_VAL3,'||
' decode(SUM(NVL(PU_COUNT,0)),0,0,(SUM(NVL(PU_AMOUNT,0))/SUM(PU_COUNT))) IBE_VAL5'||
' FROM ('||
' SELECT'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN null else fact.name end) NAME,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN fact.ent_year_id+1 else fact.ent_year_id end) ENT_YEAR_ID,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.AMOUNT_TOTAL else 0 end) PT_AMOUNT,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.TOTAL_CNT else 0 end) PT_COUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.AMOUNT_TOTAL else 0 end) CT_AMOUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.TOTAL_CNT else 0 end) CT_COUNT,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.AMOUNT_ASSIST else 0 end) PA_AMOUNT,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.ASSIST_CNT else 0 end) PA_COUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.AMOUNT_ASSIST else 0 end) CA_AMOUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.ASSIST_CNT else 0 end) CA_COUNT,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.AMOUNT_UNASSIST else 0 end) PU_AMOUNT,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.UNASSIST_CNT else 0 end) PU_COUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.AMOUNT_UNASSIST else 0 end) CU_AMOUNT,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.UNASSIST_CNT else 0 end) CU_COUNT,'||
' fact.REPORT_DATE,'||
' fact.SEQUENCE'||
' FROM ('||
' SELECT'|| l_allSelect||
' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' TOT_COUNT,0) TOTAL_CNT,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' TOT_COUNT,0) ASSIST_CNT,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' TOT_COUNT,0) UNASSIST_CNT,'||
' TIME.PERIOD_TYPE_ID,'||
' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
' TIME.ENT_YEAR_ID,TIME.NAME,TIME.REPORT_DATE'||
' FROM'||
' (SELECT'||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID,'||
' TIME_PERIOD.SEQUENCE,'||
' TIME_PERIOD.START_DATE,'||
' TIME_PERIOD.ENT_YEAR_ID'||
' FROM '||
' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) '|| l_whereclause|| ') TIME,'||
l_tableList||
' WHERE'||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID (+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter ||') FACT'||
' WHERE '||
' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'||l_allWhere ||
' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
' ORDER BY ENT_YEAR_ID, SEQUENCE';
l_allSelect VARCHAR2(1000);
l_allSelect := ' MINISITE_ID,ID,';
/* type is sequential. Depending upon the Period Type selected the */
/* Value of TIME_PERIOD and the whereclause would change */
/*************************************************************************/
/*************************************************************************/
/* VIEWBY : Period Name */
/* IBE_ATTR1 : Period Name */
/* IBE_VAL1 : Previous Total */
/* IBE_VAL2 : Current Total */
/* IBE_VAL3 : Previous Assisted */
/* IBE_VAL4 : Current Assisted */
/* IBE_VAL5 : Previous Unassisted */
/* IBE_VAL6 : Current Unassisted */
/*************************************************************************/
l_custom_sql :='SELECT FACT.NAME VIEWBY,FACT.NAME IBE_ATTR1,'||
' decode(SUM(NVL(LIST_TOTAL,0)),0,0,(SUM(NVL(DISC_TOTAL,0))/SUM(LIST_TOTAL)))*100 IBE_VAL2,'||
' decode(SUM(NVL(LIST_ASSIST,0)),0,0,(SUM(NVL(DISC_ASSIST,0))/SUM(LIST_ASSIST)))*100 IBE_VAL4,'||
' decode(SUM(NVL(LIST_UNASSIST,0)),0,0,(SUM(NVL(DISC_UNASSIST,0))/SUM(LIST_UNASSIST)))*100 IBE_VAL6,'||
' NULL IBE_VAL1,'||
' NULL IBE_VAL3,'||
' NULL IBE_VAL5'||
' FROM ('||
' SELECT '||l_allSelect ||
' TIME.NAME, NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_UNASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_UNASSIST,'||
' TIME.PERIOD_TYPE_ID,TIME.TIME_ID, TIME.REPORT_DATE'||
' FROM'||
' (SELECT'||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID'||
' FROM'||
' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) ' ||l_whereclause|| ') TIME,'||
l_tableList||
' WHERE'||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter||') FACT'||
' WHERE FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
' GROUP BY FACT.NAME, REPORT_DATE ORDER BY REPORT_DATE';
l_custom_sql := 'SELECT MAX(NAME) VIEWBY, MAX(NAME) IBE_ATTR1,'||
' decode(SUM(NVL(CT_LIST,0)),0,0,(SUM(NVL(CT_DISC,0))/SUM(CT_LIST)))*100 IBE_VAL2,'||
' decode(SUM(NVL(CA_LIST,0)),0,0,(SUM(NVL(CA_DISC,0))/SUM(CA_LIST)))*100 IBE_VAL4,'||
' decode(SUM(NVL(CU_LIST,0)),0,0,(SUM(NVL(CU_DISC,0))/SUM(CU_LIST)))*100 IBE_VAL6,'||
' decode(SUM(NVL(PT_LIST,0)),0,0,(SUM(NVL(PT_DISC,0))/SUM(PT_LIST)))*100 IBE_VAL1,'||
' decode(SUM(NVL(PA_LIST,0)),0,0,(SUM(NVL(PA_DISC,0))/SUM(PA_LIST)))*100 IBE_VAL3,'||
' decode(SUM(NVL(PU_LIST,0)),0,0,(SUM(NVL(PU_DISC,0))/SUM(PU_LIST)))*100 IBE_VAL5'||
' FROM ('||
' SELECT'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN null else fact.name end) NAME,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN fact.year445_id+1 else fact.year445_id end) ENT_YEAR_ID,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.DISC_TOTAL else 0 end) PT_DISC,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.LIST_TOTAL else 0 end) PT_LIST,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.DISC_TOTAL else 0 end) CT_DISC,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.LIST_TOTAL else 0 end) CT_LIST,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.DISC_ASSIST else 0 end) PA_DISC,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.LIST_ASSIST else 0 end) PA_LIST,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.DISC_ASSIST else 0 end) CA_DISC,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.LIST_ASSIST else 0 end) CA_LIST,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.DISC_UNASSIST else 0 end) PU_DISC,'||
' (CASE WHEN fact.start_date < :l_mid_start'||
' THEN FACT.LIST_UNASSIST else 0 end) PU_LIST,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.DISC_UNASSIST else 0 end) CU_DISC,'||
' (CASE WHEN fact.start_date >= :l_mid_start'||
' THEN FACT.LIST_UNASSIST else 0 end) CU_LIST,'||
' FACT.REPORT_DATE,'||
' FACT.SEQUENCE'||
' FROM ('||
' SELECT'||l_allSelect ||
' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_UNASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_UNASSIST,'||
' TIME.PERIOD_TYPE_ID,'||
' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
' TIME.YEAR445_ID,TIME.NAME,TIME.REPORT_DATE'||
' FROM'||
' ( SELECT '||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID, '||
' TIME_PERIOD.SEQUENCE,'||
' TIME_PERIOD.START_DATE,'||
' TIME.year445_id'||
' FROM '||
' FII_TIME_RPT_STRUCT_V CAL,'||
' FII_TIME_WEEK TIME_PERIOD,'||
' FII_TIME_p445 TIME'||
' WHERE '||
' BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE)'||
' AND (TIME_PERIOD.start_date between :l_pprev_start and :l_pcur_start OR'||
' TIME_PERIOD.start_date between :l_prev_start and :l_cur_start)'||
' AND TIME.period445_id = TIME_PERIOD.period445_id ) TIME,'||
l_tableList||
' WHERE '||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter||' ) FACT'||
' WHERE'||
' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'||l_allWhere ||
' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
' ORDER BY ENT_YEAR_ID,SEQUENCE';
l_custom_sql := 'SELECT MAX(NAME) VIEWBY, MAX(NAME) IBE_ATTR1,'||
' decode(SUM(NVL(CT_LIST,0)),0,0,(SUM(NVL(CT_DISC,0))/SUM(CT_LIST)))*100 IBE_VAL2,'||
' decode(SUM(NVL(CA_LIST,0)),0,0,(SUM(NVL(CA_DISC,0))/SUM(CA_LIST)))*100 IBE_VAL4,'||
' decode(SUM(NVL(CU_LIST,0)),0,0,(SUM(NVL(CU_DISC,0))/SUM(CU_LIST)))*100 IBE_VAL6,'||
' decode(SUM(NVL(PT_LIST,0)),0,0,(SUM(NVL(PT_DISC,0))/SUM(PT_LIST)))*100 IBE_VAL1,'||
' decode(SUM(NVL(PA_LIST,0)),0,0,(SUM(NVL(PA_DISC,0))/SUM(PA_LIST)))*100 IBE_VAL3,'||
' decode(SUM(NVL(PU_LIST,0)),0,0,(SUM(NVL(PU_DISC,0))/SUM(PU_LIST)))*100 IBE_VAL5'||
' FROM ('||
' SELECT'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN null else fact.name end) NAME,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN fact.ent_year_id+1 else fact.ent_year_id end) ENT_YEAR_ID,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.DISC_TOTAL else 0 end) PT_DISC,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.LIST_TOTAL else 0 end) PT_LIST,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.DISC_TOTAL else 0 end) CT_DISC,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.LIST_TOTAL else 0 end) CT_LIST,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.DISC_ASSIST else 0 end) PA_DISC,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.LIST_ASSIST else 0 end) PA_LIST,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.DISC_ASSIST else 0 end) CA_DISC,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.LIST_ASSIST else 0 end) CA_LIST,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.DISC_UNASSIST else 0 end) PU_DISC,'||
' (CASE WHEN fact.start_date <= :l_mid_start'||
' THEN FACT.LIST_UNASSIST else 0 end) PU_LIST,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.DISC_UNASSIST else 0 end) CU_DISC,'||
' (CASE WHEN fact.start_date > :l_mid_start'||
' THEN FACT.LIST_UNASSIST else 0 end) CU_LIST,'||
' fact.REPORT_DATE,'||
' fact.SEQUENCE'||
' FROM ('||
' SELECT'|| l_allSelect ||
' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,currency_cd_f,DISC_AMOUNT_F),0) DISC_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,currency_cd_f,LIST_AMOUNT_F),0) LIST_TOTAL,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,currency_cd_f,DISC_AMOUNT_F),0) DISC_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,currency_cd_f,LIST_AMOUNT_F),0) LIST_ASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_c_d,:l_g_p,list_amount_g,:l_g_s,LIST_AMOUNT_G1,currency_cd_f,LIST_AMOUNT_F),0) LIST_UNASSIST,'||
' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,currency_cd_f,DISC_AMOUNT_F),0) DISC_UNASSIST,'||
' TIME.PERIOD_TYPE_ID,'||
' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
' TIME.ENT_YEAR_ID,TIME.NAME,TIME.REPORT_DATE'||
' FROM'||
' (SELECT'||
' TIME_PERIOD.NAME,'||
' CAL.REPORT_DATE,'||
' CAL.PERIOD_TYPE_ID,'||
' CAL.TIME_ID,'||
' TIME_PERIOD.SEQUENCE,'||
' TIME_PERIOD.START_DATE,'||
' TIME_PERIOD.ENT_YEAR_ID'||
' FROM'||
' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) '|| l_whereclause|| ') TIME,'||
l_tableList||
' WHERE'||
' FACT.TIME_ID (+)= TIME.TIME_ID'||
' AND FACT.PERIOD_TYPE_ID (+)= TIME.PERIOD_TYPE_ID'||
l_msiteFilter ||') FACT'||
' WHERE '||
' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'||l_allWhere ||
' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
' ORDER BY ENT_YEAR_ID, SEQUENCE';