eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.OZF_CUST_FUND_SUMMARY_MV

Source


select
(CASE
WHEN GROUPING(td.month_id)=0 THEN td.month_id
WHEN GROUPING(td.ent_qtr_id)=0 THEN td.ent_qtr_id
WHEN GROUPING(td.ent_year_id)=0 THEN td.ent_year_id
ELSE -1
END
) time_id
,(CASE
WHEN GROUPING(td.month_id)=0 THEN 32
WHEN GROUPING(td.ent_qtr_id)=0 THEN 64
WHEN GROUPING(td.ent_year_id)=0 THEN 128
ELSE 256
END
) period_type_id
,hca.party_id, util.billto_cust_account_id cust_account_id, util.bill_to_site_use_id, util.ship_to_site_use_id
,util.product_level_type, util.product_id
,util.plan_type, util.plan_id
,fun.fund_id, fun.currency_code_tc currency_code
,fun.status_code, fun.owner owner
,fun.category_id, util.org_id
,sum(amount) utilized_amt
,TO_NUMBER(null) earned_amt
,TO_NUMBER(null) paid_amt
,sum(acctd_amount) func_curr_utilized_amt
,TO_NUMBER(null) func_curr_earned_amt
,TO_NUMBER(null) func_curr_paid_amt
,sum(plan_curr_amount) plan_curr_utilized_amt
,TO_NUMBER(null) plan_curr_earned_amt
,TO_NUMBER(null) plan_curr_paid_amt
,sum(univ_curr_amount) univ_curr_utilized_amt
,TO_NUMBER(null) univ_curr_earned_amt
,TO_NUMBER(null) univ_curr_paid_amt
from
ozf_funds_utilized_all_b util, hz_cust_accounts hca
,hz_parties hp, ozf_funds_all_b fun
,ozf_time_day td
where
util.billto_cust_account_id = hca.cust_account_id
and hca.party_id = hp.party_id
and fun.fund_id = util.fund_id
and util.utilization_type NOT IN ('REQUEST', 'TRANSFER')
and nvl(util.adjustment_type, 'NULL') NOT IN ('DECREASE_COMMITTED', 'DECREASE_PAID', 'INCREASE_PAID')
and nvl(util.gl_posted_flag, 'Y') NOT IN ('X') -- EXCLUDE CASE 'X'
AND td.report_date = trunc(adjustment_date)
group by hca.party_id, util.billto_cust_account_id, util.bill_to_site_use_id,fun.fund_id
,fun.status_code, fun.owner, fun.category_id, util.org_id, util.ship_to_site_use_id
, fun.currency_code_tc
,util.plan_id, util.plan_type
,util.product_id, util.product_level_type
,GROUPING SETS (td.month_id,td.ent_qtr_id,td.ent_year_id, 1)
union all
select
(CASE
WHEN GROUPING(td.month_id)=0 THEN td.month_id
WHEN GROUPING(td.ent_qtr_id)=0 THEN td.ent_qtr_id
WHEN GROUPING(td.ent_year_id)=0 THEN td.ent_year_id
ELSE -1
END
) time_id
,(CASE
WHEN GROUPING(td.month_id)=0 THEN 32
WHEN GROUPING(td.ent_qtr_id)=0 THEN 64
WHEN GROUPING(td.ent_year_id)=0 THEN 128
ELSE 256
END
) period_type_id
,hca.party_id, util.billto_cust_account_id cust_account_id, util.bill_to_site_use_id, util.ship_to_site_use_id
,util.product_level_type, util.product_id
,util.plan_type, util.plan_id
,fun.fund_id, fun.currency_code_tc currency_code
,fun.status_code, fun.owner
,fun.category_id, util.org_id
,TO_NUMBER(null) utilized_amt
,sum(amount) earned_amt
,TO_NUMBER(null) paid_amt
,TO_NUMBER(null) func_curr_utilized_amt
,sum(acctd_amount) func_curr_earned_amt
,TO_NUMBER(null) func_curr_paid_amt
,TO_NUMBER(null) plan_curr_utilized_amt
,sum(plan_curr_amount) plan_curr_earned_amt
,TO_NUMBER(null) plan_curr_paid_amt
,TO_NUMBER(null) univ_curr_utilized_amt
,sum(univ_curr_amount) univ_curr_earned_amt
,TO_NUMBER(null) univ_curr_paid_amt
from
ozf_funds_utilized_all_b util, hz_cust_accounts hca
,hz_parties hp, ozf_funds_all_b fun
,ozf_time_day td
where
util.billto_cust_account_id = hca.cust_account_id
and hca.party_id = hp.party_id
and fun.fund_id = util.fund_id
and util.utilization_type NOT IN ('REQUEST', 'TRANSFER')
and NVL(util.adjustment_type, 'NULL') NOT IN ('DECREASE_COMMITTED', 'DECREASE_PAID', 'INCREASE_PAID')
and nvl(gl_posted_flag,'Y') = 'Y'
AND td.report_date = trunc(gl_date)
group by hca.party_id, util.billto_cust_account_id, util.bill_to_site_use_id,fun.fund_id
,fun.status_code, fun.owner, fun.category_id, util.org_id
,util.ship_to_site_use_id
,fun.currency_code_tc
,util.plan_id, util.plan_type
,util.product_id, util.product_level_type
,GROUPING SETS (td.month_id,td.ent_qtr_id,td.ent_year_id, 1)
union all
select
td.month_id time_id, 32 period_type_id
,hca.party_id, util.billto_cust_account_id cust_account_id, util.bill_to_site_use_id, util.ship_to_site_use_id
,util.product_level_type, util.product_id
,util.plan_type, util.plan_id
,fun.fund_id, fun.currency_code_tc currency_code
,fun.status_code, fun.owner
,fun.category_id, util.org_id
,TO_NUMBER(null) utilized_amt
,TO_NUMBER(null) earned_amt
,sum(pay.amount_in_budget) paid_amt
,TO_NUMBER(null) func_curr_utilized_amt
,TO_NUMBER(null) func_curr_earned_amt
,sum(pay.acctd_amount) func_curr_paid_amt
,TO_NUMBER(null) plan_curr_utilized_amt
,TO_NUMBER(null) plan_curr_earned_amt
,sum(pay.amount) plan_curr_paid_amt
,TO_NUMBER(null) univ_curr_utilized_amt
,TO_NUMBER(null) univ_curr_earned_amt
,sum(pay.univ_curr_amount) univ_curr_paid_amt
from
ozf_funds_utilized_all_b util, hz_cust_accounts hca
,hz_parties hp, ozf_funds_all_b fun
,ozf_time_day td
,ozf_funds_paid_v pay
where
util.billto_cust_account_id = hca.cust_account_id
and hca.party_id = hp.party_id
and fun.fund_id = util.fund_id
and util.utilization_id = pay.utilization_id
and td.report_date = trunc(pay.claim_date)
group by hca.party_id, util.billto_cust_account_id, util.bill_to_site_use_id,fun.fund_id
,fun.status_code, fun.owner, fun.category_id, util.org_id
,util.ship_to_site_use_id
,td.month_id, fun.currency_code_tc
,util.plan_id, util.plan_type
,util.product_id, util.product_level_type
union all
select
td.ent_qtr_id time_id, 64 period_type_id
,hca.party_id, util.billto_cust_account_id cust_account_id, util.bill_to_site_use_id, util.ship_to_site_use_id
,util.product_level_type, util.product_id
,util.plan_type, util.plan_id
,fun.fund_id, fun.currency_code_tc currency_code
,fun.status_code, fun.owner
,fun.category_id, util.org_id
,TO_NUMBER(null) utilized_amt
,TO_NUMBER(null) earned_amt
,sum(pay.amount_in_budget) paid_amt
,TO_NUMBER(null) func_curr_utilized_amt
,TO_NUMBER(null) func_curr_earned_amt
,sum(pay.acctd_amount) func_curr_paid_amt
,TO_NUMBER(null) plan_curr_utilized_amt
,TO_NUMBER(null) plan_curr_earned_amt
,sum(pay.amount) plan_curr_paid_amt
,TO_NUMBER(null) univ_curr_utilized_amt
,TO_NUMBER(null) univ_curr_earned_amt
,sum(pay.univ_curr_amount) univ_curr_paid_amt
from
ozf_funds_utilized_all_b util, hz_cust_accounts hca
,hz_parties hp, ozf_funds_all_b fun
,ozf_time_day td
,ozf_funds_paid_v pay
where
util.billto_cust_account_id = hca.cust_account_id
and hca.party_id = hp.party_id
and fun.fund_id = util.fund_id
and util.utilization_id = pay.utilization_id
AND td.report_date = trunc(pay.claim_date)
group by hca.party_id, util.billto_cust_account_id, util.bill_to_site_use_id,fun.fund_id
,fun.status_code, fun.owner, fun.category_id, util.org_id
,util.ship_to_site_use_id
,td.ent_qtr_id, fun.currency_code_tc
,util.plan_id, util.plan_type
,util.product_id, util.product_level_type
union all
select
td.ent_year_id time_id, 128 period_type_id
,hca.party_id, util.billto_cust_account_id cust_account_id, util.bill_to_site_use_id, util.ship_to_site_use_id
,util.product_level_type, util.product_id
,util.plan_type, util.plan_id
,fun.fund_id, fun.currency_code_tc currency_code
,fun.status_code, fun.owner
,fun.category_id, util.org_id
,TO_NUMBER(null) utilized_amt
,TO_NUMBER(null) earned_amt
,sum(pay.amount_in_budget) paid_amt
,TO_NUMBER(null) func_curr_utilized_amt
,TO_NUMBER(null) func_curr_earned_amt
,sum(pay.acctd_amount) func_curr_paid_amt
,TO_NUMBER(null) plan_curr_utilized_amt
,TO_NUMBER(null) plan_curr_earned_amt
,sum(pay.amount) plan_curr_paid_amt
,TO_NUMBER(null) univ_curr_utilized_amt
,TO_NUMBER(null) univ_curr_earned_amt
,sum(pay.univ_curr_amount) univ_curr_paid_amt
from
ozf_funds_utilized_all_b util, hz_cust_accounts hca
,hz_parties hp, ozf_funds_all_b fun
,ozf_time_day td
,ozf_funds_paid_v pay
where
util.billto_cust_account_id = hca.cust_account_id
and hca.party_id = hp.party_id
and fun.fund_id = util.fund_id
and util.utilization_id = pay.utilization_id
AND td.report_date = trunc(pay.claim_date)
group by hca.party_id, util.billto_cust_account_id, util.bill_to_site_use_id,fun.fund_id
,fun.status_code, fun.owner, fun.category_id, util.org_id
,util.ship_to_site_use_id
,td.ent_year_id, fun.currency_code_tc
,util.plan_id, util.plan_type
,util.product_id, util.product_level_type
union all
select
-1 time_id,256 period_type_id
,hca.party_id, util.billto_cust_account_id cust_account_id, util.bill_to_site_use_id, util.ship_to_site_use_id
,util.product_level_type, util.product_id
,util.plan_type, util.plan_id
,fun.fund_id, fun.currency_code_tc currency_code
,fun.status_code, fun.owner
,fun.category_id, util.org_id
,TO_NUMBER(null) utilized_amt
,TO_NUMBER(null) earned_amt
,sum(pay.amount_in_budget) paid_amt
,TO_NUMBER(null) func_curr_utilized_amt
,TO_NUMBER(null) func_curr_earned_amt
,sum(pay.acctd_amount) func_curr_paid_amt
,TO_NUMBER(null) plan_curr_utilized_amt
,TO_NUMBER(null) plan_curr_earned_amt
,sum(pay.amount) plan_curr_paid_amt
,TO_NUMBER(null) univ_curr_utilized_amt
,TO_NUMBER(null) univ_curr_earned_amt
,sum(pay.univ_curr_amount) univ_curr_paid_amt
from
ozf_funds_utilized_all_b util, hz_cust_accounts hca
,hz_parties hp, ozf_funds_all_b fun
,ozf_time_day td
,ozf_funds_paid_v pay
where
util.billto_cust_account_id = hca.cust_account_id
and hca.party_id = hp.party_id
and fun.fund_id = util.fund_id
and util.utilization_id = pay.utilization_id
AND td.report_date = trunc(pay.claim_date)
group by hca.party_id, util.billto_cust_account_id, util.bill_to_site_use_id,fun.fund_id
,fun.status_code, fun.owner, fun.category_id, util.org_id
,util.ship_to_site_use_id
, fun.currency_code_tc
,util.plan_id, util.plan_type
,util.product_id, util.product_level_type