SELECT CARTNUMBER, PARTY_ID CUSTOMER, MINISITE_ID,CURRENCY_CD_F,BOOKED_AMT_F,BOOKED_AMT_G,BOOKED_AMT_G1, LINES, QUOTE_EXPIRATION_DATE, CREATION_DATE, RESOURCE_FLAG, TIME_ID, PERIOD_TYPE_ID, RANK FROM ( SELECT QUOTE_NUMBER CARTNUMBER, PARTY_ID, MINISITE_ID,CURRENCY_CD_F,BOOKED_AMT_F,BOOKED_AMT_G,BOOKED_AMT_G1, LINES, RESOURCE_FLAG, CREATION_DATE, QUOTE_EXPIRATION_DATE, TIME_ID, PERIOD_TYPE_ID, RANK() OVER (PARTITION BY MINISITE_ID,TIME_ID,PERIOD_TYPE_ID ORDER BY BOOKED_AMT_G DESC NULLS LAST) RANK FROM ( SELECT MV.QUOTE_NUMBER, MV.CUST_PARTY_ID PARTY_ID, MV.QUOTE_EXPIRATION_DATE, trunc(MV.CREATION_DATE) CREATION_DATE, (CASE WHEN GROUPING (TIME.report_date_julian) = 0 THEN TIME.report_date_julian WHEN GROUPING (TIME.week_id)=0 THEN TIME.week_id WHEN GROUPING (TIME.ent_period_id)=0 THEN TIME.ent_period_id WHEN GROUPING (TIME.ent_qtr_id)=0 THEN TIME.ent_qtr_id Else TIME.ent_year_id End) Time_Id, (CASE WHEN GROUPING (TIME.report_date_julian) = 0 THEN 1 WHEN GROUPING (TIME.week_id)=0 THEN 16 WHEN GROUPING (TIME.ent_period_id)=0 THEN 32 WHEN GROUPING (TIME.ent_qtr_id)=0 THEN 64 Else 128 END) Period_Type_id, RESOURCE_FLAG, CURRENCY_CD_F, SUM(AMOUNT_G) BOOKED_AMT_G, SUM(AMOUNT_G1) BOOKED_AMT_G1, SUM(AMOUNT_F) BOOKED_AMT_F, MV.MINISITE_ID, SUM(MV.QUOTE_LINES) LINES FROM APPS.IBE_BI_CARTS_MV MV, FII.FII_TIME_DAY TIME WHERE trunc(MV.CREATION_DATE) = TIME.REPORT_DATE AND MV.PUBLISHED_QUOTE_FLAG= 'N' AND MV.QUOTE_STATUS_ID <> 5 AND ORDER_ID IS NULL GROUP BY MV.QUOTE_NUMBER, MV.CUST_PARTY_ID, MV.QUOTE_EXPIRATION_DATE, trunc(MV.CREATION_DATE), RESOURCE_FLAG, CURRENCY_CD_F, MV.MINISITE_ID, TIME.ENT_YEAR_ID, ROLLUP(TIME.ENT_QTR_ID,TIME.ENT_PERIOD_ID,TIME.WEEK_ID,TIME.REPORT_DATE_JULIAN) )) WHERE RANK <= NVL(FND_PROFILE.VALUE('IBE_BI_TOP_ACT_NO_ROWS'),25)
[Home] [Help]