Query for fetching claim from ChRM/Trade Management

Below is a basic query to fetch claims, claim types and status. Can add more columns if needed.

SELECT      (SELECT vendor_name
                        FROM apps.ap_suppliers aps
                          where oca.vendor_id = aps.vendor_id) vendor_name,
       oca.claim_id,
       oca.claim_number,
       oca.creation_date,
       oct.name,
       claim_date,
       oca.amount,
       aus.name user_status,
  FROM
       apps.ozf_claims_all oca,
       apps.ozf_claim_types_all_tl oct,
       apps.ams_user_statuses_vl aus
 WHERE     oca.claim_type_id = oct.claim_type_id
       AND aus.user_status_id = oca.user_status_id;