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.

VIEW: APPS.OZF_CUST_FUND_SUMMARY_MV#

Source

View Text - Preformatted


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

View Text - HTML Formatted

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