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_EARNING_SUMMARY_MV#

Source

View Text - Preformatted


SELECT fu.cust_account_id cust_account_id
      ,fu.product_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(fu.product_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
     , 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 = 'FAMILY'
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         fu.product_id,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.month_id 

UNION ALL
/*Query for the Product category and and Time Period is : Quarter*/
SELECT fu.cust_account_id cust_account_id
      ,fu.product_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(fu.product_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
     , 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 = 'FAMILY'
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         fu.product_id, 
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.ent_qtr_id 

UNION ALL
/*Query for the Product category and and Time Period is : Year*/
SELECT fu.cust_account_id cust_account_id
      ,fu.product_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(fu.product_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
     , 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 = 'FAMILY'
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         fu.product_id, 
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
     , td.ent_year_id

UNION ALL

/*Query for the Product category and and Time Period is : -1 */
SELECT fu.cust_account_id cust_account_id1
      ,fu.product_id item_category_id1
     , 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(fu.product_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
     , 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 = 'FAMILY'
AND    td.report_date = trunc(fu.gl_date)
AND    fu.gl_posted_flag = 'Y'
GROUP BY
       CUBE(
         fu.cust_account_id,
         fu.product_id, 
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id

UNION ALL

/*Query for the Product and Time Period is : Month */

 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_v mic --mtl_item_categories_v
     , 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
AND    mic.organization_id = (select profile_option_value 
                                from fnd_profile_option_values
				where level_id = 10001 
				and (profile_option_id, application_id) = (select profile_option_id, application_id 
				                                             from fnd_profile_options
									    where profile_option_name = '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'
AND  ( mic.MULT_ITEM_CAT_ASSIGN_FLAG = 'N' OR mic.category_id = (SELECT min(category_id) FROM mtl_item_categories icat WHERE icat.category_id = fu.product_id AND icat.category_set_id = mic.category_set_id))
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

/*Query for the Product and Time Period is : 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_v 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
AND    mic.organization_id = (select profile_option_value 
                                from fnd_profile_option_values
				where level_id = 10001 
				and (profile_option_id, application_id) = (select profile_option_id, application_id 
				                                             from fnd_profile_options
									    where profile_option_name = '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'
AND  ( mic.MULT_ITEM_CAT_ASSIGN_FLAG = 'N' OR mic.category_id = (SELECT min(category_id) FROM mtl_item_categories_v icat WHERE icat.category_id = fu.product_id AND icat.category_set_id = mic.category_set_id))
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

/*Query for the Product and Time Period is : Year */
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_v 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
AND    mic.organization_id = (select profile_option_value 
                                from fnd_profile_option_values
				where level_id = 10001 
				and (profile_option_id, application_id) = (select profile_option_id, application_id 
				                                             from fnd_profile_options
									    where profile_option_name = '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'
AND  ( mic.MULT_ITEM_CAT_ASSIGN_FLAG = 'N' OR mic.category_id = (SELECT min(category_id) FROM mtl_item_categories_v icat WHERE icat.category_id = fu.product_id AND icat.category_set_id = mic.category_set_id))
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

/*Query for the Product and Time Period is : -1 */
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_v 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
AND    mic.organization_id = (select profile_option_value 
                                from fnd_profile_option_values
				where level_id = 10001 
				and (profile_option_id, application_id) = (select profile_option_id, application_id 
				                                             from fnd_profile_options
									    where profile_option_name = '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'
AND  ( mic.MULT_ITEM_CAT_ASSIGN_FLAG = 'N' OR mic.category_id = (SELECT min(category_id) FROM mtl_item_categories_v icat WHERE icat.category_id = fu.product_id AND icat.category_set_id = mic.category_set_id))
GROUP BY
       CUBE(
         fu.cust_account_id,
         mic.category_id,
         offer.offer_type,
         fu.object_type, offer.activity_media_id
       )
     , fu.org_id
--End of the Product Category and Product Queries.

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

View Text - HTML Formatted

SELECT FU.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, FU.PRODUCT_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(FU.PRODUCT_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
, 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 = 'FAMILY' AND TD.REPORT_DATE = TRUNC(FU.GL_DATE) AND FU.GL_POSTED_FLAG = 'Y' GROUP BY CUBE( FU.CUST_ACCOUNT_ID
, FU.PRODUCT_ID
, OFFER.OFFER_TYPE
, FU.OBJECT_TYPE
, OFFER.ACTIVITY_MEDIA_ID )
, FU.ORG_ID
, TD.MONTH_ID UNION ALL /*QUERY FOR THE PRODUCT CATEGORY
AND AND TIME PERIOD IS : QUARTER*/ SELECT FU.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, FU.PRODUCT_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(FU.PRODUCT_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
, 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 = 'FAMILY' AND TD.REPORT_DATE = TRUNC(FU.GL_DATE) AND FU.GL_POSTED_FLAG = 'Y' GROUP BY CUBE( FU.CUST_ACCOUNT_ID
, FU.PRODUCT_ID
, OFFER.OFFER_TYPE
, FU.OBJECT_TYPE
, OFFER.ACTIVITY_MEDIA_ID )
, FU.ORG_ID
, TD.ENT_QTR_ID UNION ALL /*QUERY FOR THE PRODUCT CATEGORY
AND AND TIME PERIOD IS : YEAR*/ SELECT FU.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, FU.PRODUCT_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(FU.PRODUCT_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
, 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 = 'FAMILY' AND TD.REPORT_DATE = TRUNC(FU.GL_DATE) AND FU.GL_POSTED_FLAG = 'Y' GROUP BY CUBE( FU.CUST_ACCOUNT_ID
, FU.PRODUCT_ID
, OFFER.OFFER_TYPE
, FU.OBJECT_TYPE
, OFFER.ACTIVITY_MEDIA_ID )
, FU.ORG_ID
, TD.ENT_YEAR_ID UNION ALL /*QUERY FOR THE PRODUCT CATEGORY
AND AND TIME PERIOD IS : -1 */ SELECT FU.CUST_ACCOUNT_ID CUST_ACCOUNT_ID1
, FU.PRODUCT_ID ITEM_CATEGORY_ID1
, 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(FU.PRODUCT_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
, 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 = 'FAMILY' AND TD.REPORT_DATE = TRUNC(FU.GL_DATE) AND FU.GL_POSTED_FLAG = 'Y' GROUP BY CUBE( FU.CUST_ACCOUNT_ID
, FU.PRODUCT_ID
, OFFER.OFFER_TYPE
, FU.OBJECT_TYPE
, OFFER.ACTIVITY_MEDIA_ID )
, FU.ORG_ID UNION ALL /*QUERY FOR THE PRODUCT
AND TIME PERIOD IS : MONTH */ 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_V MIC --MTL_ITEM_CATEGORIES_V
, 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 AND MIC.ORGANIZATION_ID = (SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES WHERE LEVEL_ID = 10001 AND (PROFILE_OPTION_ID
, APPLICATION_ID) = (SELECT PROFILE_OPTION_ID
, APPLICATION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = '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' AND ( MIC.MULT_ITEM_CAT_ASSIGN_FLAG = 'N' OR MIC.CATEGORY_ID = (SELECT MIN(CATEGORY_ID)
FROM MTL_ITEM_CATEGORIES ICAT
WHERE ICAT.CATEGORY_ID = FU.PRODUCT_ID
AND ICAT.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID)) 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 /*QUERY FOR THE PRODUCT
AND TIME PERIOD IS : 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_V 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 AND MIC.ORGANIZATION_ID = (SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES WHERE LEVEL_ID = 10001 AND (PROFILE_OPTION_ID
, APPLICATION_ID) = (SELECT PROFILE_OPTION_ID
, APPLICATION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = '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' AND ( MIC.MULT_ITEM_CAT_ASSIGN_FLAG = 'N' OR MIC.CATEGORY_ID = (SELECT MIN(CATEGORY_ID)
FROM MTL_ITEM_CATEGORIES_V ICAT
WHERE ICAT.CATEGORY_ID = FU.PRODUCT_ID
AND ICAT.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID)) 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 /*QUERY FOR THE PRODUCT
AND TIME PERIOD IS : YEAR */ 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_V 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 AND MIC.ORGANIZATION_ID = (SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES WHERE LEVEL_ID = 10001 AND (PROFILE_OPTION_ID
, APPLICATION_ID) = (SELECT PROFILE_OPTION_ID
, APPLICATION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = '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' AND ( MIC.MULT_ITEM_CAT_ASSIGN_FLAG = 'N' OR MIC.CATEGORY_ID = (SELECT MIN(CATEGORY_ID)
FROM MTL_ITEM_CATEGORIES_V ICAT
WHERE ICAT.CATEGORY_ID = FU.PRODUCT_ID
AND ICAT.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID)) 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 /*QUERY FOR THE PRODUCT
AND TIME PERIOD IS : -1 */ 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_V 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 AND MIC.ORGANIZATION_ID = (SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES WHERE LEVEL_ID = 10001 AND (PROFILE_OPTION_ID
, APPLICATION_ID) = (SELECT PROFILE_OPTION_ID
, APPLICATION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = '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' AND ( MIC.MULT_ITEM_CAT_ASSIGN_FLAG = 'N' OR MIC.CATEGORY_ID = (SELECT MIN(CATEGORY_ID)
FROM MTL_ITEM_CATEGORIES_V ICAT
WHERE ICAT.CATEGORY_ID = FU.PRODUCT_ID
AND ICAT.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID)) GROUP BY CUBE( FU.CUST_ACCOUNT_ID
, MIC.CATEGORY_ID
, OFFER.OFFER_TYPE
, FU.OBJECT_TYPE
, OFFER.ACTIVITY_MEDIA_ID )
, FU.ORG_ID --END OF THE PRODUCT CATEGORY
AND PRODUCT QUERIES. 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