[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_I_MKT_CPB_MV
Source
SELECT
(CASE
WHEN GROUPING(b.report_date_julian)=0 Then b.report_date_julian
WHEN GROUPING(b.week_id)=0 Then b.week_id
WHEN GROUPING(b.ent_period_id)=0 Then b.ent_period_id
WHEN GROUPING(b.ent_qtr_id)=0 Then b.ent_qtr_id
ELSE b.ent_year_id
END
) time_id,
(CASE
WHEN GROUPING(b.report_date_julian)=0 Then 1
WHEN GROUPING(b.week_id)=0 Then 16
WHEN GROUPING(b.ent_period_id)=0 Then 32
WHEN GROUPING(b.ent_qtr_id)=0 Then 64
ELSE 128
END
) period_type_id,
1 umarker,
a.object_id,
a.object_type,
a.object_country,
a.category_id,
a.object_region,
a.CHILD_OBJECT_ID,
a.CHILD_OBJECT_type,
a.source_code_id ,
0 total_cost,
0 total_cost_fr,
0 booked_amt,
0 booked_amt_fr,
0 invoiced_amt,
0 invoiced_amt_fr,
0 won_opportunity_amt,
0 won_opportunity_amt_fr,
0 customers_targeted,
0 customers_targeted_fr,
0 responses_forecasted,
0 responses_forecasted_fr,
0 responses_positive,
0 responses_positive_fr,
a.activity_id activity_id,
SUM
(
budget_approved/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)
) budget_approved,
COUNT
(
budget_approved/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)
) budget_approved_fr,
SUM
(
cost_actual/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)
) actual_cost,
COUNT
(
cost_actual/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)
) actual_cost_fr,
0 leads,
0 total_leads_fr,
0 total_cost_s,
0 total_cost_s_fr,
0 booked_amt_s,
0 booked_amt_s_fr,
0 invoiced_amt_s,
0 invoiced_amt_s_fr,
0 won_opportunity_amt_s,
0 won_opportunity_amt_s_fr,
SUM( budget_approved_s/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)) budget_approved_s,
COUNT
( budget_approved_s/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)) budget_approved_s_fr,
SUM( cost_actual_s/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
) ) actual_cost_s,
COUNT (
cost_actual_s/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
))actual_cost_s_fr,
SUM
(
budget_approved_Sch/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)
) budget_approved_sch,
COUNT
(
budget_approved_sch/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)
) budget_approved_sch_fr,
SUM
(
budget_approved_Sch_s/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)
) budget_approved_sch_s,
COUNT
(
budget_approved_sch_s/
to_number ( a.end_date -
GREATEST(transaction_create_date, a.start_date) + 1
)
) budget_approved_sch_s_fr,
COUNT(*) all_count_fr,
b.report_date_julian dummy1_fr,
b.week_id dummy2_fr,
b.ent_period_id dummy3_fr,
b.ent_qtr_id dummy4_fr,
b.ent_year_id dummy5_fr,
GROUPING_ID(b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id,a.object_id, a.object_type,a.object_country,a.category_id,
a.activity_id,a.object_region,
a.CHILD_OBJECT_ID,
a.CHILD_OBJECT_type,
a.source_code_id)
dummy6_fr
FROM bim_i_mkt_fct_mv a,
FII.FII_TIME_DAY b
WHERE
(a.budget_approved <> 0 or a.cost_actual <> 0 or budget_approved_sch <> 0) and
b.report_date between a.start_date and a.end_date and
b.report_date >= a.transaction_create_date and
a.end_date >= a.transaction_create_date
GROUP BY
GROUPING SETS(b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id),
a.object_id, a.object_type,a.object_country,a.category_id,a.activity_id,a.object_region,
a.CHILD_OBJECT_ID,
a.CHILD_OBJECT_type,
a.source_code_id
union all
SELECT
(CASE
WHEN GROUPING(b.report_date_julian)=0 Then b.report_date_julian
WHEN GROUPING(b.week_id)=0 Then b.week_id
WHEN GROUPING(b.ent_period_id)=0 Then b.ent_period_id
WHEN GROUPING(b.ent_qtr_id)=0 Then b.ent_qtr_id
ELSE b.ent_year_id
END
) time_id,
(CASE
WHEN GROUPING(b.report_date_julian)=0 Then 1
WHEN GROUPING(b.week_id)=0 Then 16
WHEN GROUPING(b.ent_period_id)=0 Then 32
WHEN GROUPING(b.ent_qtr_id)=0 Then 64
ELSE 128
END
) period_type_id,
2 umarker,
a.object_id,
a.object_type,
a.object_country,
a.category_id,
a.object_region,
a.CHILD_OBJECT_ID,
a.CHILD_OBJECT_type,
a.source_code_id ,
SUM
(
cost_actual
) total_cost,
COUNT
(
cost_actual
) total_cost_fr,
SUM
(
orders_booked_amt
) booked_amt,
COUNT
(
orders_booked_amt
) booked_amt_fr,
SUM
(
orders_invoiced_amt
) invoiced_amt,
COUNT
(
orders_invoiced_amt
) invoiced_amt_fr,
SUM
(
won_opportunity_amt
) won_opportunity_amt,
COUNT
(
won_opportunity_amt
) won_opportunity_amt_fr,
SUM ( customers_targeted) customers_targeted,
count(customers_targeted) customers_targeted_fr,
SUM ( responses_forecasted) responses_forecasted,
count(responses_forecasted) responses_forecasted_fr,
SUM ( responses_positive) responses_positive,
count(responses_positive) responses_positive_fr,
a.activity_id activity_id,
0 budget_approved,
0 budget_approved_fr,
0 actual_cost,
0 actual_cost_fr,
sum(a.leads) leads,
count(a.leads) total_leads_fr,
SUM
(
cost_actual_s
) total_cost_s,
COUNT
(
cost_actual_s
) total_cost_s_fr,
SUM
(
booked_order_amount_s
) booked_amt_s,
COUNT
(
booked_order_amount_s
) booked_amt_s_fr,
SUM
(
invoiced_order_amount_s
) invoiced_amt_s,
COUNT
(
invoiced_order_amount_s
) invoiced_amt_s_fr,
SUM
(
won_opportunity_amt_s
) won_opportunity_amt_s,
COUNT
(
won_opportunity_amt_s
) won_opportunity_amt_s_fr,
0 budget_approved_s,
0 budget_approved_s_fr,
0 actual_cost_s,
0 actual_cost_s_fr,
0 budget_approved_sch,
0 budget_approved_sch_fr,
0 budget_approved_sch_s,
0 budget_approved_sch_s_fr,
COUNT(*) all_count_fr,
b.report_date_julian dummy1_fr,
b.week_id dummy2_fr,
b.ent_period_id dummy3_fr,
b.ent_qtr_id dummy4_fr,
b.ent_year_id dummy5_fr,
GROUPING_ID(b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id,a.object_id, a.object_type,a.object_country,a.category_id,
a.activity_id,a.object_region,
a.CHILD_OBJECT_ID,
a.CHILD_OBJECT_type,
a.source_code_id )
dummy6_fr
FROM bim_i_mkt_fct_mv a,
FII.FII_TIME_DAY b
WHERE b.report_date =a.transaction_create_date
GROUP BY
GROUPING SETS
(b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id),
a.object_id, a.object_type,a.object_country,a.category_id,a.activity_id,a.object_region,
a.CHILD_OBJECT_ID,
a.CHILD_OBJECT_type,
a.source_code_id