Query to find Subscription details in oracle fusion

 SELECT 
os.subscription_number,
os.extn_attribute_char019 subscription_business_source,
null account_name,
hca_bill.attribute2 account_site_id,
hca_bill.account_number account_number,
hca_bill.orig_system_reference account_crm_account_id,
os.extn_attribute_char036 ds_account_id,
hca_bill.status customer_account_status,
null account_partner_referring_account2,
hca_bill.attribute18 renewal_manager_name,
hca_bill.attribute16 account_owner_name,
null account_balance,
os.status subscription_status,
(SELECT MAX(cp.email_address)
   FROM hz_contact_points cp,
        hz_cust_account_roles acct_role
  WHERE acct_role.cust_account_role_id = os.bill_to_contact_id
    AND acct_role.relationship_id      = cp.relationship_id
    AND cp.owner_table_name            = 'HZ_PARTIES'
    AND cp.contact_point_type          = 'EMAIL'
    AND cp.status                      = 'A'
    AND cp.primary_flag                = 'Y'
) bill_to_work_email, 
(SELECT MAX(cp.email_address)
   FROM hz_contact_points cp,
        hz_cust_account_roles acct_role
  WHERE acct_role.cust_account_role_id = os.ship_to_contact_id
    AND acct_role.relationship_id      = cp.relationship_id
    AND cp.owner_table_name            = 'HZ_PARTIES'
    AND cp.contact_point_type          = 'EMAIL'
    AND cp.status                      = 'A'
    AND cp.primary_flag                = 'Y'
) ship_to_work_email,   
hl_ship.country ship_to_country,        
--rt.name term_type,
decode(os.end_date,null,'EVERGREEN','TERMED') term_type,
os.start_date,
os.creation_date,
os.end_date,
os.extn_attribute_char018 subscription_opportunity_id,
os.source_number subscription_order_id,
null subscription_order_type,
os.currency,
'CyberSource' payment_gateway_name
FROM oss_subscriptions os,
--ra_terms rt,
hz_cust_accounts hca_bill,
hz_parties hp_bill,
hz_cust_site_uses_all hcsua_bill,
hz_cust_acct_sites_all hcasa_bill,
hz_party_sites hps_bill,
hz_locations hl_bill,
--hz_cust_accounts hca_ship,
--hz_parties hp_ship,
--hz_cust_site_uses_all hcsua_ship,
hz_cust_acct_sites_all hcasa_ship,
hz_party_sites hps_ship,
hz_locations hl_ship
WHERE 1=1
--AND os.payment_term_id = rt.term_id(+)
AND os.bill_to_acct_id = hca_bill.cust_account_id
AND hca_bill.party_id = hp_bill.party_id
AND os.bill_to_site_use_id = hcsua_bill.site_use_id
AND hcsua_bill.cust_acct_site_id = hcasa_bill.cust_acct_site_id
AND hcasa_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.location_id = hl_bill.location_id
--AND os.ship_to_acct_id = hca_bill.cust_account_id
--AND hca_ship.party_id = hp_ship.party_id
--AND os.bill_to_site_use_id = hcsua_ship.site_use_id
--AND hcsua_ship.cust_acct_site_id = hcasa_ship.cust_acct_site_id
AND os.ship_to_party_site_id = hcasa_ship.party_site_id(+)
AND hcasa_ship.party_site_id = hps_ship.party_site_id(+)
AND hps_ship.location_id = hl_ship.location_id(+)
/*AND os.end_date =
(CASE :p_subscription_type
WHEN 'EVERGREEN'
THEN  is null
WHEN 'TERMED'
THEN  is not null
END)*/
AND os.status = nvl(:p_subscription_status,os.status)
AND (:p_subscription_type = 'EVERGREEN' AND os.end_date IS NULL
OR :p_subscription_type = 'TERMED' AND os.end_date IS NOT NULL
OR :p_subscription_type is null and 1=1)
AND trunc(os.creation_date) >= nvl(:p_from_date,trunc(os.creation_date))
AND trunc(os.creation_date) <= nvl(:p_to_date,trunc(os.creation_date))
AND os.subscription_number ='A-S01375171'