PL/SQL - How to pull data from 3 tables based on latest created date

I'm hoping someone can help me as I've been stuck on this problem for a few days now. Basically I'm trying to pull data from 3 tables in Oracle: 1) Orders Table 2) Vendor Table and 3) Master Data Table.

Here's what the 3 tables look like:

Table 1: BIZ_DOC2 (Orders table)

OBJECTID (Unique key)

UNIQUE_DOC_NAME (Document Name i.e. ORD-005)

CREATED_AT (Date the order was created)

Table 2: UDEF_VENDOR (Vendors table):

PARENT_OBJECT_ID (This matches up to the OBJECTID in the Orders table)

VENDOR_OBJECT_NAME (This is the na开发者_JAVA技巧me of the vendor i.e. Acme)

Table 3: BIZ_UNIT (Master Data table)

PARENT_OBJECT_ID (This matches up to the OBJECTID in the Orders table)

BIZ_UNIT_OBJECT_NAME (This is the name of the business unit i.e. widget A, widget B)

Note: The Vendors Table and Master Data do not have a link between them except through the Orders table.

I can join all of the data from the tables and it looks something like this:

Before selecting latest order date:

ORD-005 | Widget A | Acme | 3/14/10

ORD-005 | Widget B | Acme | 3/14/10

ORD-004 | Widget C | Acme | 3/10/10

Ideally I'd like to return the latest order for each vendor. However, each order may contain multiple business units (e.g. types of widgets) so if a Vendor's latest record is ORD-005 and the order contains 2 business units, here's what the result set should look like by the following columns: UNIQUE_DOC_NAME, BIZ_UNIT_OBJECT_NAME, VENDOR_OBJECT_NAME, CREATED_AT

After selecting by latest order date:

ORD-005 | Widget A | Acme | 3/14/10

ORD-005 | Widget B | Acme | 3/14/10

I tried using Select Max and several variations of sub-queries but I just can't seem to get it working. Any help would be hugely appreciated!

SELECT DISTINCT
       FIRST_VALUE(d.unique_doc_name)
       OVER (PARTITION BY v.vendor_object_name
             ORDER BY d.created_at DESC)
       AS unique_doc_name
      ,FIRST_VALUE(u.biz_unit_object_name)
       OVER (PARTITION BY v.vendor_object_name
             ORDER BY d.created_at DESC)
       AS biz_unit_object_name
      ,FIRST_VALUE(v.vendor_object_name)
       OVER (PARTITION BY v.vendor_object_name
             ORDER BY d.created_at DESC)
       AS vendor_object_name
      ,FIRST_VALUE(d.created_at)
       OVER (PARTITION BY v.vendor_object_name
             ORDER BY d.created_at DESC)
       AS created_at
FROM   biz_doc2 d, udef_vendor v, biz_unit u
WHERE  d.objectid = v.objectid
AND    d.objectid = u.parent_object_id;

SELECT
  O.UNIQUE_DOC_NAME,
  U.BIZ_UNIT_OBJECT_NAME,
  V.VENDOR_OBJECT_NAME,
  O.CREATED_AT
 FROM
  ( SELECT
      V.VENDOR_OBJECT_NAME, MAX(O.CREATED_AT) AS CREATED_AT
     FROM
      UDEF_VENDOR AS V
     INNER JOIN
      BIZ_DOC2 AS O
     ON
      V.PARENT_OBJECT_ID=O.OBJECTID
     GROUP BY
      V.VENDOR_OBJECT_NAME
  ) AS VO   -- most recent order date per vendor
 INNER JOIN
  UDEF_VENDOR AS V
 ON
  V.VENDOR_OBJECT_NAME=VO.VENDOR_OBJECT_NAME
 INNER JOIN
  BIZ_DOC2 AS O
 ON         -- re-match vendors to orders for latest date
  O.OBJECTID=V.PARENT_OBJECT_ID AND
  O.CREATED_AT=VO.CREATED_AT
 INNER JOIN
  BIZ_UNIT AS U
 ON
  U.PARENT_OBJECT_ID=O.OBJECTID

Here's another solution using analytic functions. Instead of getting the first value for the max date, it ranks the dates and gets all values for the latest date.

select unique_doc_name, biz_unit_object_name, vendor_object_name, created_at
from (select unique_doc_name, biz_unit_object_name,
             vendor_object_name, created_at,
             rank () over (order by created_date desc) rnk
      from biz_doc2 d, udef_vendor v, biz_unit u
      where d.object_id = v.parent_object_id
      and d.objectid = u.parent_object_id)
where rnk = 1;