Improve performance on query with many OR operations

I have the below query where I need to Select the MAX(COST_DATE) per C.SUBINVENTORY_CODE, but I don't want to Group On A.REC_TRXN_ID, B.TRANSACTION_ID, A.QUANTITY_ONHAND, or B.TRANSACTION_COST.

(SELECT * FROM 
 (SELECT A.INVENTORY_ITEM_ID
        ,C.SUBINVENTORY_CODE,
        A.REC_TRXN_ID,
        B.TRANSACTION_ID,
        B.COST_DATE,
        A.QUANTITY_ONHAND,
        B.TRANSACTION_COST
        FROM CST_ONHAND_V A,
             CST_ITEM_COST_HISTORY_V B,
             CST_TXN_LAYER_DTLS_V C
        WHERE 1=1
            AND A.INVENTORY_ITEM_ID= :p_inv_num
            AND A.REC_TRXN_ID=B.TRANSACTION_ID
            AND A.COST_ORG_ID = B.COST_ORG_ID
            AND A.COST_BOOK_ID = B.COST_BOOK_ID
            AND A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID                           
            AND A.REC_TRXN_ID=C.REC_TRXN_ID                             
    ORDER BY B.COST_DATE DESC NULLS LAST
    )
    --WHERE ROWNUM = 1
)

Current Results:

INVENTORY_ITEM_ID   SUBINVENTORY_CODE   REC_TRXN_ID TRANSACTION_ID  COST_DATE  QUANTITY_ONHAND  TRANSACTION_COST
100000030624440     11XDC               10727447    10727447        2024-01-23  3        17.4375
100000030624440     41XDC               9532105     9532105         2023-11-05  6        17.43
100000030624440     41XDC               9532105     9532105         2023-11-05  6        17.43
100000030624440     41XDC               9532105     9532105         2023-11-04  6        17.43
100000030624440     11XDC               8790246     8790246         2023-09-18  5      17.4375
100000030624440     11XDC               8659489     8659489         2023-09-08  4      17.4375
100000030624440     11XDC               8659489     8659489         2023-09-08  4      17.4375

Desired Results:

INVENTORY_ITEM_ID   SUBINVENTORY_CODE   REC_TRXN_ID TRANSACTION_ID  COST_DATE  QUANTITY_ONHAND  TRANSACTION_COST
100000030624440     11XDC               10727447    10727447        2024-01-23  3        17.4375
100000030624440     41XDC               9532105     9532105         2023-11-05  6        17.43

Because I have to Group on the fields A.REC_TRXN_ID, B.TRANSACTION_ID, A.QUANTITY_ONHAND B.TRANSACTION_COST that is preventing me from getting just the MAX Cost Date by only Subinventory. How can I achieve this? Thanks in advance.

Solution

with ROW_NUMBER() we can assign a row number to each row within each SUBINVENTORY_CODE partition, ordered by COST_DATE in descending order and then, filter out only the rows with row number 1, which corresponds to the maximum COST_DATE for each SUBINVENTORY_CODE:

SELECT INVENTORY_ITEM_ID,
       SUBINVENTORY_CODE,
       REC_TRXN_ID,
       TRANSACTION_ID,
       COST_DATE,
       QUANTITY_ONHAND,
       TRANSACTION_COST
FROM (
    SELECT A.INVENTORY_ITEM_ID,
           C.SUBINVENTORY_CODE,
           A.REC_TRXN_ID,
           B.TRANSACTION_ID,
           B.COST_DATE,
           A.QUANTITY_ONHAND,
           B.TRANSACTION_COST,
           ROW_NUMBER() OVER (PARTITION BY C.SUBINVENTORY_CODE ORDER BY B.COST_DATE DESC) AS rn
    FROM CST_ONHAND_V A
    JOIN CST_ITEM_COST_HISTORY_V B ON A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID AND A.REC_TRXN_ID = B.TRANSACTION_ID
    JOIN CST_TXN_LAYER_DTLS_V C ON A.REC_TRXN_ID = C.REC_TRXN_ID
    WHERE A.INVENTORY_ITEM_ID = :p_inv_num
    ORDER BY B.COST_DATE DESC NULLS LAST
)
WHERE rn = 1;