DROP VIEW apps.xx_media_request_info_v;


CREATE OR REPLACE FORCE VIEW apps.xx_media_request_info_v (

         license_number
        ,license_id 
        ,upgrade_input_id
        ,inventory_item_id
        ,licensed_products_id
        ,source
        ,status
        ,requested_date
        ,in_progress_date
        ,completed_canceled_date
        ,batch_number
        ,ship_method
        ,ship_channel
        ,country
                                                             )
AS


SELECT 
distinct 
xui.license_number
,xui.license_id
,xuid.upgrade_input_id
,xuid.inventory_item_id
,xuid.licensed_products_id
,xui.source
,xuid.status
,xuid.s1_date requested_date
,xuid.s3_date in_progress_date
,xuid.s6_date completed_canceled_date
,xuid.ship_batch_id batch_number
,xusm.ship_method
,xusm.ship_channel
,raa_ru.country
     
FROM 
          
          xxcus.xx_upgrade_shipping_methods xusm,
          ar.ra_addresses_all raa_ru,
          xxcus.xx_upgrade_input_details xuid,
          xxcus.xx_upgrade_inputs xui
          
WHERE 
xui.action = 'SHIP'
AND xui.status IN ('IN_PROGRESS','REQUESTED','CANCELED')
AND xuid.upgrade_input_id = xui.upgrade_input_id
AND raa_ru.address_id = xuid.ship_to_address_id
AND xusm.country_code(+) = raa_ru.country;