Requirement: Find the item cost from last PO of given date

CREATE OR replace FUNCTION xx_get_max_rcv_trx_id(

p_organization_id NUMBER,
p_item_id         NUMBER,
p_date            DATE)
RETURN NUMBER
IS
  l_max_trx_id NUMBER;
BEGIN
    SELECT Max(X.transaction_id) max_trx_id
    INTO   l_max_trx_id
    FROM   po.rcv_transactions X,
           po.po_lines_all Y,
           mtl_system_items Z
    WHERE  X.po_line_id = Y.po_line_id
           AND X.organization_id = Z.organization_id
           AND Y.item_id = Z.inventory_item_id
           AND Z.planning_make_buy_code = 2
           AND x.source_document_code = 'PO'
           AND Y.item_id = p_item_id
           AND X.transaction_type = 'DELIVER'
           AND x.transaction_date <= p_date --to_date('28-FEB-2021','DD-MON-YYYY')
           AND lpn_group_id IS NULL
           AND X.organization_id = p_organization_id
    GROUP  BY x.organization_id,
              y.item_id;
    RETURN l_max_trx_id;
EXCEPTION
  WHEN OTHERS THEN
             RETURN NULL;
END;

CREATE OR replace FUNCTION xx_get_latest_po_price (p_inventory_item_id NUMBER,
                                                   p_organization_id   NUMBER)
RETURN NUMBER
IS
  l_price NUMBER;
BEGIN
    SELECT Nvl(D.price_override, C.unit_price) * Nvl(A.currency_conversion_rate, 1)
                 + Nvl((
                        SELECT SUM(X.unround_tax_amt_fun_curr)
                        FROM   ja.jai_tax_lines X
                        WHERE  X.trx_id = A.shipment_header_id
                               AND X.trx_line_id = A.shipment_line_id
                               AND X.trx_loc_line_id = A.transaction_id
                               AND X.application_id = 707
                               AND X.recoverable_flag = 'N'), 0) / A.quantity *
                                Nvl(CASE
                                      WHEN E.uom_code != F.uom_code THEN 1 /
                                      Decode(
                                      inv_convert.Inv_um_convert(
                                      item_id => c.item_id, PRECISION => NULL
                                      ,
                                      from_quantity => 1,
                                      from_unit => F.uom_code,
                                          to_unit
                                                 => E.uom_code,
                                        from_name => C.unit_meas_lookup_code,
                                        to_name => A.primary_unit_of_measure
                                    ),
                                    0, 1,
                                    NULL, 1, apps.inv_convert.Inv_um_convert(
                                             item_id => c.item_id,
                                    PRECISION => NULL
                                    ,
                                             from_quantity => 1,
                                    from_unit => F.uom_code,
                                    to_unit => E.uom_code,
                                    from_name => C.unit_meas_lookup_code,
                                    to_name => A.primary_unit_of_measure))
                                      ELSE 1
                                    END, 1) UNIT_PRICE
    INTO   l_price
    FROM   po.rcv_transactions A,
           po_lines_all C,
           po_line_locations_all D,
           mtl_units_of_measure E,
           mtl_units_of_measure F
    WHERE  1 = 1
           AND A.transaction_id = Xx_get_max_rcv_trx_id(
                                  p_organization_id => p_organization_id,
                                   p_item_id => p_inventory_item_id)
           AND A.po_line_id = C.po_line_id
           AND A.po_line_location_id = D.line_location_id
           AND C.po_line_id = D.po_line_id
           AND C.org_id = D.org_id
           AND A.primary_unit_of_measure = E.unit_of_measure
           AND C.unit_meas_lookup_code = F.unit_of_measure
           --AND A.TRANSACTION_TYPE='DELIVER'
           --AND A.SOURCE_DOCUMENT_CODE='PO'
           --AND A.TRANSACTION_DATE <= to_date('28-FEB-2021','DD-MON-YYYY')
           AND A.organization_id = p_organization_id
           AND c.item_id = p_inventory_item_id;

    RETURN l_price;
EXCEPTION
  WHEN OTHERS THEN
             RETURN NULL;
END;

/