Oracle executing stored procedure not returning all the columns and result

Question

I have this stored procedure in Oracle:

create or replace PROCEDURE "SP_ATTENDANCE_DETAILS_SELECT" 
(
  employeeid IN tblemployees.empid%type,
  parmonth IN integer,
  paryear IN integer,
  p_rec  OUT SYS_REFCURSOR
)
AS 
BEGIN
---------------------------------------------------------------------
--  Revisions:
--  20150623      cschua       Added AWOL when checking leavetype
---------------------------------------------------------------------
  OPEN p_rec
       FOR
SELECT emp.EmpID,to_char(att.AttendanceDate, 'YYYY-MM-DD') AS AttendanceDate,
              CASE WHEN (upper(att.leavetype) = 'LEAVE W/O PAY' OR upper(att.leavetype) = 'HL' OR upper(att.leavetype) = 'AWOL')
                  THEN att.LeaveType
                  WHEN att.tardiness != 0
                  THEN 'Late'
                   END AS Description
    FROM tblemployees emp
 LEFT JOIN tblattendancedtl att
        ON emp.EmpID = att.EmpID
         AND (att.Month = parmonth OR parmonth = 0)
         AND (att.Year = parYear  OR parYear = 0)
         --modified by kim ivan 2020-11-18
         --AND (upper(att.leavetype) IN ('LEAVE W/O PAY', 'SICK LEAVE', 'AWOL')
         AND (upper(att.leavetype) IN ('NPL', 'HL', 'AWOL')
              OR att.tardiness != 0)
        WHERE emp.EmpID = employeeid
      ORDER BY att.AttendanceDate ASC;
        
END SP_ATTENDANCE_DETAILS_SELECT;

The problem is if I execute only the select inside of stored procedure, it is working as expected:

SELECT 
    emp.EmpID,
    to_char(att.AttendanceDate, 'YYYY-MM-DD') AS AttendanceDate,
    --modified by kim ivan 2020-11-18
    -- (CASE WHEN (upper(att.leavetype) = 'LEAVE W/O PAY' OR upper(att.leavetype) = 'SICK LEAVE' OR upper(att.leavetype) = 'AWOL')
    (CASE WHEN (upper(att.leavetype) = 'LEAVE W/O PAY' OR upper(att.leavetype) = 'HL' OR upper(att.leavetype) = 'AWOL')
                  THEN att.LeaveType
                  WHEN att.tardiness != 0
                  THEN 'Late'
                   END) AS Description
FROM 
    tblemployees emp
LEFT JOIN 
    tblattendancedtl att ON emp.EmpID = '13001495'
                         AND (att.Month = 7 OR 7 = 0 )
                         AND (att.Year = 2020 OR 2020=0 )
                         --modified by kim ivan 2020-11-18
                         --AND (upper(att.leavetype) IN ('LEAVE W/O PAY', 'SICK LEAVE', 'AWOL')
                         AND (upper(att.leavetype) IN ('NPL', 'HL', 'AWOL')
                              OR att.tardiness != 0)
WHERE 
    emp.EmpID = '13001495'
ORDER BY 
    att.AttendanceDate ASC;
      

with the result of this

EmpID     AttendanceDate   Description
--------------------------------------
13001495    2020-07-04      Late
13001495    2020-07-13      Late
13001495    2020-07-13      Late

But when I try to execute the whole procedure using this

var r refcursor;
exec SP_ATTENDANCE_DETAILS_SELECT('13001495',7,2020,:r);
print r;

it only returns 1 row with employee id only

EmpID     AttendanceDate   Description
---------------------------------------
13001495    

What is wrong? It seems correct and fine, I'm stuck at this point.

I hope someone can help me out of this.

Thank you

Answer 1

I can see a clear difference in your procedure and in your query in the JOIN condition here:

In Procedure:

ON emp.EmpID = att.EmpID

In Query:

ON emp.EmpID = '13001495' --> It should be ON emp.EmpID = att.EmpID

Change the same in the query and see what result it gives to you.