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_EARNING_SUMMARY_MV

Source


SELECT fu.cust_account_id cust_account_id
     , mic.category_id item_category_id
     , offer.offer_type offer_type
     , fu.object_type object_type
     , offer.activity_media_id activity_type
     , fu.org_id org_id
     , sum(acctd_amount) amount_earned
     , count(acctd_amount) amount_earned_cnt
     , sum(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid
     , count(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid_cnt
     , sum(fu.acctd_amount_remaining) amount_balance
     , count(fu.acctd_amount_remaining) amount_balance_cnt
     , count(*) all_cnt
     , grouping(fu.cust_account_id) grp_cust_account_id
     , grouping(mic.category_id) grp_item_category_id
     , grouping(offer.offer_type) grp_offer_type
     , grouping(fu.object_type) grp_object_type
     , grouping(offer.activity_media_id) grp_activity_type
     , 32 period_type_id
     , td.month_id time_id
FROM   ozf_funds_utilized_all_b fu
     , mtl_item_categories mic
     , mtl_default_category_sets cat
     , ozf_offers offer
     , ozf_time_day td
WHERE  fu.utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND    fu.plan_type = 'OFFR'
AND    fu.plan_id = offer.qp_list_header_id
AND    ((fu.product_level_type = 'PRODUCT' AND fu.product_id = mic.inventory_item_id)
        OR (fu.product_level_type = 'FAMILY' AND fu.product_id = mic.category_id)
       )
AND    mic.organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
AND    mic.category_set_id = cat.category_set_id
AND    cat.functional_area_id = 11
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         mic.category_id,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.month_id
UNION ALL
-- select for offers; period: quarter
SELECT fu.cust_account_id cust_account_id
     , mic.category_id item_category_id
     , offer.offer_type offer_type
     , fu.object_type object_type
     , offer.activity_media_id activity_type
     , fu.org_id org_id
     , sum(acctd_amount) amount_earned
     , count(acctd_amount) amount_earned_cnt
     , sum(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid
     , count(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid_cnt
     , sum(fu.acctd_amount_remaining) amount_balance
     , count(fu.acctd_amount_remaining) amount_balance_cnt
     , count(*) all_cnt
     , grouping(fu.cust_account_id) grp_cust_account_id
     , grouping(mic.category_id) grp_item_category_id
     , grouping(offer.offer_type) grp_offer_type
     , grouping(fu.object_type) grp_object_type
     , grouping(offer.activity_media_id) grp_activity_type
     , 64 period_type_id
     , td.ent_qtr_id time_id
FROM   ozf_funds_utilized_all_b fu
     , mtl_item_categories mic
     , mtl_default_category_sets cat
     , ozf_offers offer
     , ozf_time_day td
WHERE  fu.utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND    fu.plan_type = 'OFFR'
AND    fu.plan_id = offer.qp_list_header_id
AND    ((fu.product_level_type = 'PRODUCT' AND fu.product_id = mic.inventory_item_id)
        OR (fu.product_level_type = 'FAMILY' AND fu.product_id = mic.category_id)
       )
AND    mic.organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
AND    mic.category_set_id = cat.category_set_id
AND    cat.functional_area_id = 11
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         mic.category_id,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.ent_qtr_id
UNION ALL
SELECT fu.cust_account_id cust_account_id
     , mic.category_id item_category_id
     , offer.offer_type offer_type
     , fu.object_type object_type
     , offer.activity_media_id activity_type
     , fu.org_id org_id
     , sum(acctd_amount) amount_earned
     , count(acctd_amount) amount_earned_cnt
     , sum(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid
     , count(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid_cnt
     , sum(fu.acctd_amount_remaining) amount_balance
     , count(fu.acctd_amount_remaining) amount_balance_cnt
     , count(*) all_cnt
     , grouping(fu.cust_account_id) grp_cust_account_id
     , grouping(mic.category_id) grp_item_category_id
     , grouping(offer.offer_type) grp_offer_type
     , grouping(fu.object_type) grp_object_type
     , grouping(offer.activity_media_id) grp_activity_type
     , 128 period_type_id
     , td.ent_year_id time_id
FROM   ozf_funds_utilized_all_b fu
     , mtl_item_categories mic
     , mtl_default_category_sets cat
     , ozf_offers offer
     , ozf_time_day td
WHERE  fu.utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND    fu.plan_type = 'OFFR'
AND    fu.plan_id = offer.qp_list_header_id
AND    ((fu.product_level_type = 'PRODUCT' AND fu.product_id = mic.inventory_item_id)
        OR (fu.product_level_type = 'FAMILY' AND fu.product_id = mic.category_id)
       )
AND    mic.organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
AND    mic.category_set_id = cat.category_set_id
AND    cat.functional_area_id = 11
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         mic.category_id,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.ent_year_id
UNION ALL
SELECT fu.cust_account_id cust_account_id
     , mic.category_id item_category_id
     , offer.offer_type offer_type
     , fu.object_type object_type
     , offer.activity_media_id activity_type
     , fu.org_id org_id
     , sum(acctd_amount) amount_earned
     , count(acctd_amount) amount_earned_cnt
     , sum(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid
     , count(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid_cnt
     , sum(fu.acctd_amount_remaining) amount_balance
     , count(fu.acctd_amount_remaining) amount_balance_cnt
     , count(*) all_cnt
     , grouping(fu.cust_account_id) grp_cust_account_id
     , grouping(mic.category_id) grp_item_category_id
     , grouping(offer.offer_type) grp_offer_type
     , grouping(fu.object_type) grp_object_type
     , grouping(offer.activity_media_id) grp_activity_type
     , 256 period_type_id
     , -1 time_id
FROM   ozf_funds_utilized_all_b fu
     , mtl_item_categories mic
     , mtl_default_category_sets cat
     , ozf_offers offer
WHERE  fu.utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND    fu.plan_type = 'OFFR'
AND    fu.plan_id = offer.qp_list_header_id
AND    ((fu.product_level_type = 'PRODUCT' AND fu.product_id = mic.inventory_item_id)
        OR (fu.product_level_type = 'FAMILY' AND fu.product_id = mic.category_id)
       )
AND    mic.organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
AND    mic.category_set_id = cat.category_set_id
AND    cat.functional_area_id = 11
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         mic.category_id,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
UNION ALL
-- select for offers; period: month; product: null or media
SELECT fu.cust_account_id cust_account_id
     , to_number(null) item_category_id
     , offer.offer_type offer_type
     , fu.object_type object_type
     , offer.activity_media_id activity_type
     , fu.org_id org_id
     , sum(acctd_amount) amount_earned
     , count(acctd_amount) amount_earned_cnt
     , sum(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid
     , count(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid_cnt
     , sum(fu.acctd_amount_remaining) amount_balance
     , count(fu.acctd_amount_remaining) amount_balance_cnt
     , count(*) all_cnt
     , grouping(fu.cust_account_id) grp_cust_account_id
     , grouping(null) grp_item_category_id
     , grouping(offer.offer_type) grp_offer_type
     , grouping(fu.object_type) grp_object_type
     , grouping(offer.activity_media_id) grp_activity_type
     , 32 period_type_id
     , td.month_id time_id
FROM   ozf_funds_utilized_all_b fu
     , ozf_offers offer
     , ozf_time_day td
WHERE  fu.utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND    fu.plan_type = 'OFFR'
AND    fu.plan_id = offer.qp_list_header_id
AND    (fu.product_level_type = 'MEDIA' OR fu.product_id IS NULL)
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         null,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.month_id
UNION ALL
-- select for offers; period: quarter; product: media or null
SELECT fu.cust_account_id cust_account_id
     , to_number(null) item_category_id
     , offer.offer_type offer_type
     , fu.object_type object_type
     , offer.activity_media_id activity_type
     , fu.org_id org_id
     , sum(acctd_amount) amount_earned
     , count(acctd_amount) amount_earned_cnt
     , sum(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid
     , count(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid_cnt
     , sum(fu.acctd_amount_remaining) amount_balance
     , count(fu.acctd_amount_remaining) amount_balance_cnt
     , count(*) all_cnt
     , grouping(fu.cust_account_id) grp_cust_account_id
     , grouping(null) grp_item_category_id
     , grouping(offer.offer_type) grp_offer_type
     , grouping(fu.object_type) grp_object_type
     , grouping(offer.activity_media_id) grp_activity_type
     , 64 period_type_id
     , td.ent_qtr_id time_id
FROM   ozf_funds_utilized_all_b fu
     , ozf_offers offer
     , ozf_time_day td
WHERE  fu.utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND    fu.plan_type = 'OFFR'
AND    fu.plan_id = offer.qp_list_header_id
AND    (fu.product_level_type = 'MEDIA' OR fu.product_id IS NULL)
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         null,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.ent_qtr_id
UNION ALL
SELECT fu.cust_account_id cust_account_id
     , to_number(null) item_category_id
     , offer.offer_type offer_type
     , fu.object_type object_type
     , offer.activity_media_id activity_type
     , fu.org_id org_id
     , sum(acctd_amount) amount_earned
     , count(acctd_amount) amount_earned_cnt
     , sum(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid
     , count(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid_cnt
     , sum(fu.acctd_amount_remaining) amount_balance
     , count(fu.acctd_amount_remaining) amount_balance_cnt
     , count(*) all_cnt
     , grouping(fu.cust_account_id) grp_cust_account_id
     , grouping(null) grp_item_category_id
     , grouping(offer.offer_type) grp_offer_type
     , grouping(fu.object_type) grp_object_type
     , grouping(offer.activity_media_id) grp_activity_type
     , 128 period_type_id
     , td.ent_year_id time_id
FROM   ozf_funds_utilized_all_b fu
     , ozf_offers offer
     , ozf_time_day td
WHERE  fu.utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND    fu.plan_type = 'OFFR'
AND    fu.plan_id = offer.qp_list_header_id
AND    (fu.product_level_type = 'MEDIA' OR fu.product_id IS NULL)
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         null,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.ent_year_id
UNION ALL
SELECT fu.cust_account_id cust_account_id
     , to_number(null) item_category_id
     , offer.offer_type offer_type
     , fu.object_type object_type
     , offer.activity_media_id activity_type
     , fu.org_id org_id
     , sum(acctd_amount) amount_earned
     , count(acctd_amount) amount_earned_cnt
     , sum(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid
     , count(fu.acctd_amount - fu.acctd_amount_remaining) amount_paid_cnt
     , sum(fu.acctd_amount_remaining) amount_balance
     , count(fu.acctd_amount_remaining) amount_balance_cnt
     , count(*) all_cnt
     , grouping(fu.cust_account_id) grp_cust_account_id
     , grouping(null) grp_item_category_id
     , grouping(offer.offer_type) grp_offer_type
     , grouping(fu.object_type) grp_object_type
     , grouping(offer.activity_media_id) grp_activity_type
     , 256 period_type_id
     , -1 time_id
FROM   ozf_funds_utilized_all_b fu
     , ozf_offers offer
WHERE  fu.utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND    fu.plan_type = 'OFFR'
AND    fu.plan_id = offer.qp_list_header_id
AND    (fu.product_level_type = 'MEDIA' OR fu.product_id IS NULL)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         null,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id