VIEW: APPS.OZF_CLAIM_OFFER_UTIL_V

Source

View Text - Preformatted

SELECT off.qp_list_header_id qp_list_header_id , to_char(null) qualifier_type , f.cust_account_id qualifier_id, off.offer_type offer_type, off.offer_code offer_code, off.status_code status_code , to_char(null) plan_type , to_number(null) plan_id , to_number(null) activity_product_id , to_char(null) product_level_type , to_number(null) product_id , to_char(null) currency_code , to_number(null) amount , to_number(null) acctd_amount , to_number(null) amount_remaining , to_number(null) acctd_amount_remaining , to_number(null) channel_id , to_char(null) adjustment_flag , to_number(null) scan_value , to_char(null) uom_code , to_number(null) quantity , to_number(null) scan_unit , to_number(null) scan_unit_remaining, 'Y' header_flag, off.confidential_flag, off.user_status_id, f.year_id from ozf_offers off, ozf_funds_utilized_all_b f where off.offer_type = 'SCAN_DATA' and f.utilization_type in ('ADJUSTMENT', 'ACCRUAL') and off.qp_list_header_id = f.plan_id and f.plan_type = 'OFFR' and f.org_id = mo_global.get_current_org_id() group by off.qp_list_header_id, f.cust_account_id , off.offer_code, off.offer_type, off.status_code, off.confidential_flag,off.user_status_id,f.year_id union all (select fu.activity_product_id qp_list_header_id ,to_char(null) qualifier_type , fu.cust_account_id qualifier_id , ao.offer_type offer_type , to_char(null) offer_code , to_char(null) status_code , fu.plan_type plan_type , fu.plan_id plan_id , fu.activity_product_id activity_product_id ,fu.product_level_type product_level_type , fu.product_id product_id ,fu.currency_code currency_code , sum(fu.amount) amount , sum(fu.acctd_amount) acctd_amount , sum(fu.amount_remaining) amount_remaining , sum(fu.acctd_amount_remaining) acctd_amount_remaining , ap.channel_id channel_id, ap.adjustment_flag adjustment_flag , ap.scan_value scan_value , ap.uom_code uom_code , ap.quantity quantity , ap.scan_unit_forecast scan_unit, sum(fu.scan_unit_remaining) scan_unit_remaining , 'N' header_flag, ao.confidential_flag, ao.user_status_id, fu.year_id from ozf_funds_utilized_all_b fu, ams_act_products ap, ozf_offers ao where fu.utilization_type in ('ADJUSTMENT', 'ACCRUAL') and fu.plan_id = ao.qp_list_header_id and ao.offer_type = 'SCAN_DATA' and fu.plan_type = 'OFFR' and fu.activity_product_id = ap.activity_product_id (+) and fu.org_id = mo_global.get_current_org_id() group by fu.plan_type, fu.plan_id, fu.activity_product_id , fu.product_level_type, fu.product_id,fu.currency_code , ap.channel_id, ap.adjustment_flag, ap.scan_value, ap.uom_code ,ap.quantity,ap.scan_unit_forecast,ao.offer_type, fu.cust_account_id, ao.confidential_flag,ao.user_status_id,fu.year_id)

View Text - HTML Formatted

SELECT OFF.QP_LIST_HEADER_ID QP_LIST_HEADER_ID
, TO_CHAR(NULL) QUALIFIER_TYPE
, F.CUST_ACCOUNT_ID QUALIFIER_ID
, OFF.OFFER_TYPE OFFER_TYPE
, OFF.OFFER_CODE OFFER_CODE
, OFF.STATUS_CODE STATUS_CODE
, TO_CHAR(NULL) PLAN_TYPE
, TO_NUMBER(NULL) PLAN_ID
, TO_NUMBER(NULL) ACTIVITY_PRODUCT_ID
, TO_CHAR(NULL) PRODUCT_LEVEL_TYPE
, TO_NUMBER(NULL) PRODUCT_ID
, TO_CHAR(NULL) CURRENCY_CODE
, TO_NUMBER(NULL) AMOUNT
, TO_NUMBER(NULL) ACCTD_AMOUNT
, TO_NUMBER(NULL) AMOUNT_REMAINING
, TO_NUMBER(NULL) ACCTD_AMOUNT_REMAINING
, TO_NUMBER(NULL) CHANNEL_ID
, TO_CHAR(NULL) ADJUSTMENT_FLAG
, TO_NUMBER(NULL) SCAN_VALUE
, TO_CHAR(NULL) UOM_CODE
, TO_NUMBER(NULL) QUANTITY
, TO_NUMBER(NULL) SCAN_UNIT
, TO_NUMBER(NULL) SCAN_UNIT_REMAINING
, 'Y' HEADER_FLAG
, OFF.CONFIDENTIAL_FLAG
, OFF.USER_STATUS_ID
, F.YEAR_ID
FROM OZF_OFFERS OFF
, OZF_FUNDS_UTILIZED_ALL_B F
WHERE OFF.OFFER_TYPE = 'SCAN_DATA'
AND F.UTILIZATION_TYPE IN ('ADJUSTMENT'
, 'ACCRUAL')
AND OFF.QP_LIST_HEADER_ID = F.PLAN_ID
AND F.PLAN_TYPE = 'OFFR'
AND F.ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID() GROUP BY OFF.QP_LIST_HEADER_ID
, F.CUST_ACCOUNT_ID
, OFF.OFFER_CODE
, OFF.OFFER_TYPE
, OFF.STATUS_CODE
, OFF.CONFIDENTIAL_FLAG
, OFF.USER_STATUS_ID
, F.YEAR_ID UNION ALL (SELECT FU.ACTIVITY_PRODUCT_ID QP_LIST_HEADER_ID
, TO_CHAR(NULL) QUALIFIER_TYPE
, FU.CUST_ACCOUNT_ID QUALIFIER_ID
, AO.OFFER_TYPE OFFER_TYPE
, TO_CHAR(NULL) OFFER_CODE
, TO_CHAR(NULL) STATUS_CODE
, FU.PLAN_TYPE PLAN_TYPE
, FU.PLAN_ID PLAN_ID
, FU.ACTIVITY_PRODUCT_ID ACTIVITY_PRODUCT_ID
, FU.PRODUCT_LEVEL_TYPE PRODUCT_LEVEL_TYPE
, FU.PRODUCT_ID PRODUCT_ID
, FU.CURRENCY_CODE CURRENCY_CODE
, SUM(FU.AMOUNT) AMOUNT
, SUM(FU.ACCTD_AMOUNT) ACCTD_AMOUNT
, SUM(FU.AMOUNT_REMAINING) AMOUNT_REMAINING
, SUM(FU.ACCTD_AMOUNT_REMAINING) ACCTD_AMOUNT_REMAINING
, AP.CHANNEL_ID CHANNEL_ID
, AP.ADJUSTMENT_FLAG ADJUSTMENT_FLAG
, AP.SCAN_VALUE SCAN_VALUE
, AP.UOM_CODE UOM_CODE
, AP.QUANTITY QUANTITY
, AP.SCAN_UNIT_FORECAST SCAN_UNIT
, SUM(FU.SCAN_UNIT_REMAINING) SCAN_UNIT_REMAINING
, 'N' HEADER_FLAG
, AO.CONFIDENTIAL_FLAG
, AO.USER_STATUS_ID
, FU.YEAR_ID
FROM OZF_FUNDS_UTILIZED_ALL_B FU
, AMS_ACT_PRODUCTS AP
, OZF_OFFERS AO
WHERE FU.UTILIZATION_TYPE IN ('ADJUSTMENT'
, 'ACCRUAL')
AND FU.PLAN_ID = AO.QP_LIST_HEADER_ID
AND AO.OFFER_TYPE = 'SCAN_DATA'
AND FU.PLAN_TYPE = 'OFFR'
AND FU.ACTIVITY_PRODUCT_ID = AP.ACTIVITY_PRODUCT_ID (+)
AND FU.ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID() GROUP BY FU.PLAN_TYPE
, FU.PLAN_ID
, FU.ACTIVITY_PRODUCT_ID
, FU.PRODUCT_LEVEL_TYPE
, FU.PRODUCT_ID
, FU.CURRENCY_CODE
, AP.CHANNEL_ID
, AP.ADJUSTMENT_FLAG
, AP.SCAN_VALUE
, AP.UOM_CODE
, AP.QUANTITY
, AP.SCAN_UNIT_FORECAST
, AO.OFFER_TYPE
, FU.CUST_ACCOUNT_ID
, AO.CONFIDENTIAL_FLAG
, AO.USER_STATUS_ID
, FU.YEAR_ID)