Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE AP_INVOICES_INTERFACE (
  INVOICE_ID    INT,
  PO_NUMBER     NVARCHAR2(32)
)
INSERT INTO AP_INVOICES_INTERFACE VALUES (300000136747640 , NULL)
1 rows affected

CREATE TABLE AP_INVOICE_LINES_INTERFACE (
  INVOICE_ID    INT,
  PO_NUMBER     NVARCHAR2(32)
)
INSERT INTO AP_INVOICE_LINES_INTERFACE
SELECT 300000136747640, 'K11004499' FROM DUAL
UNION ALL
SELECT 300000136747640, NULL FROM DUAL
UNION ALL
SELECT 300000136747640, NULL FROM DUAL
UNION ALL
SELECT 300000136747640, 'K11004499' FROM DUAL
4 rows affected

CREATE TABLE PO_HEADERS_ALL (
  SEGMENT1      NVARCHAR2(32),
  REQ_BU_ID     INT
)
INSERT INTO PO_HEADERS_ALL
SELECT 'K11004499', 300000006290049 FROM DUAL
UNION ALL
SELECT 'K11004499', NULL FROM DUAL
2 rows affected

WITH
  PO_LOOKUP
AS
(
  SELECT
    HDR.INVOICE_ID,
    HDR.PO_NUMBER,
    MAX(LN.PO_NUMBER) OVER (PARTITION BY HDR.INVOICE_ID)   AS LN_PO_NUMBER
  FROM
    AP_INVOICES_INTERFACE        HDR
  INNER JOIN
    AP_INVOICE_LINES_INTERFACE   LN
      ON LN.INVOICE_ID = HDR.INVOICE_ID
)
SELECT
  PO_LOOKUP.*,
  (
    SELECT REQ_BU_ID
      FROM PO_HEADERS_ALL
     WHERE SEGMENT1 = PO_LOOKUP.LN_PO_NUMBER
  ORDER BY REQ_BU_ID ASC
     FETCH NEXT 1 ROWS ONLY
  )
    AS REQ_BU_ID
FROM
  PO_LOOKUP
WHERE
  INVOICE_ID = 300000136747640


INVOICE_ID
PO_NUMBER
LN_PO_NUMBER
REQ_BU_ID


300000136747640
null
K11004499
300000006290049

300000136747640
null
K11004499
300000006290049

300000136747640
null
K11004499
300000006290049

300000136747640
null
K11004499
300000006290049


SELECT
  HDR.INVOICE_ID,
  HDR.PO_NUMBER,
  LN.MAX_PO_NUMBER,
  PO.MAX_REQ_BU_ID
FROM
  AP_INVOICES_INTERFACE        HDR
INNER JOIN
(
  SELECT
    AP_INVOICE_LINES_INTERFACE.*,
    MAX(PO_NUMBER) OVER (PARTITION BY INVOICE_ID)  AS MAX_PO_NUMBER
  FROM
    AP_INVOICE_LINES_INTERFACE
)
  LN
    ON LN.INVOICE_ID = HDR.INVOICE_ID
INNER JOIN
(
  SELECT
    SEGMENT1,
    MAX(REQ_BU_ID)   AS MAX_REQ_BU_ID
  FROM
    PO_HEADERS_ALL
  GROUP BY
    SEGMENT1
)
  PO
    ON PO.SEGMENT1 = LN.MAX_PO_NUMBER
WHERE
  HDR.INVOICE_ID = 300000136747640


INVOICE_ID
PO_NUMBER
MAX_PO_NUMBER
MAX_REQ_BU_ID


300000136747640
null
K11004499
300000006290049

300000136747640
null
K11004499
300000006290049

300000136747640
null
K11004499
300000006290049

300000136747640
null
K11004499
300000006290049

INVOICE_ID	PO_NUMBER	LN_PO_NUMBER	REQ_BU_ID
300000136747640	null	K11004499	300000006290049
300000136747640	null	K11004499	300000006290049
300000136747640	null	K11004499	300000006290049
300000136747640	null	K11004499	300000006290049