CREATE OR REPLACE FUNCTION get_chofer2(p_batchnumber IN NUMBER, p_terminalserialnumber IN NUMBER, p_placeidnumber IN NUMBER, p_trxdate IN DATE) RETURN VARCHAR2 
IS
   l_chofer NUMBER := 0;
BEGIN
    select q2.workerid into l_chofer
    from 
    (
    select max(aux.trxdate) trxdate
    from vw_chofer_aux1 aux
    where aux.BATCHNUMBER = p_batchnumber
          AND aux.TERMINALSERIALNUMBER = p_terminalserialnumber
          AND aux.PLACEIDNUMBER = p_placeidnumber
          and aux.trxdate < p_trxdate
    ) Q1 
    inner join 
    (
    SELECT distinct aux.trxdate, aux.WORKERID
    from vw_chofer_aux1 aux
    where aux.BATCHNUMBER = p_batchnumber
          AND aux.TERMINALSERIALNUMBER = p_terminalserialnumber
          AND aux.PLACEIDNUMBER = p_placeidnumber
          and aux.trxdate < p_trxdate
    )q2
    on q1.trxdate = q2.trxdate;     
   
   RETURN l_chofer
   ;
END;
/

SELECT t.trxdate, t.batchnumber, t.terminalserialnumber, t.placeidnumber, t.trxdate,
       get_chofer2 (t.batchnumber,
       t.terminalserialnumber,
       t.placeidnumber,
       t.trxdate) workerid
FROM TRANSACTION t;