Oracle code is not compiling kindly help me

Question

PROCEDURE UpdateAttendance (
V_AttendanceDate DATE, V_LoginId varchar2, V_SignType varchar2, V_IpAddress varchar2, V_LogonUserName VARCHAR2, V_ShiftName VARCHAR2, V_WorkLocationType VARCHAR2, V_WorkLocation VARCHAR2, V_Self_Covid_Status VARCHAR2, V_Self_Covid_Remark VARCHAR2, V_Family_Covid_Status VARCHAR2, V_Family_Covid_Remark VARCHAR2, curAttendanceDate OUT T_CURSOR

) AS BEGIN

- In this section I am getting error SELECT CASE WHEN SignOut IS NULL THEN INTERVAL '-1' DAY (5) + V_AttendanceDate ELSE V_AttendanceDate END

INTO V_AttendanceDate FROM T_ATTENDANCE_ATTENDANCE

         WHERE     UPPER (ShiftName) = 'NIGHT'
               AND LoginId = V_LoginId
               AND AttendanceDate = INTERVAL '-1' DAY (5) + V_AttendanceDate
               AND V_SignType = 'SIGNOUT'
               AND Active = 1;

        
        
        
        UPDATE T_ATTENDANCE_ATTENDANCE
           SET SignIn =
                  CASE
                     WHEN     V_SignType = 'SIGNIN'
                          AND SignIn IS NULL
                     THEN
                        SYSDATE
                     ELSE
                        SignIn
                  END,
               SignOut =
                  CASE
                     WHEN     V_SignType = 'SIGNOUT'
                          AND signout IS NULL
                     THEN
                        SYSDATE
                     ELSE
                        SignOut
                  END,
               IpAddress =
                  CASE
                     WHEN NVL (IpAddress, '') = '' THEN V_IPAddress
                     ELSE IpAddress || ',' || V_IPAddress
                  END,
               LogonUserName =
                  CASE
                     WHEN NVL (LogonUserName, '') = '' THEN V_LogonUserName
                     ELSE LogonUserName || ',' || V_LogonUserName
                  END,
               WorkLocation =
                  CASE
                     WHEN V_SignType = 'SIGNIN' THEN V_WorkLocation
                     ELSE WorkLocation
                  END,
               WorkLocationType =
                  CASE
                     WHEN V_SignType = 'SIGNIN' THEN V_WorkLocationType
                     ELSE WorkLocationType
                  END,
               ShiftName =
                  CASE
                     WHEN     V_SignType = 'SIGNIN'
                          AND NVL (ShiftName, '') = ''
                     THEN
                        V_ShiftName
                     ELSE
                        ShiftName
                  END
         WHERE     AttendanceDate = V_AttendanceDate
               AND LoginId = V_LoginId
               AND Active = 1;



        UPDATE T_ATTENDANCE_ATTENDANCE
           SET ShiftName = 'LEAVE'
         WHERE     AttendanceDate < SYSTIMESTAMP - 2
               AND SignIn IS NULL
               AND SignOut IS NULL
               AND ShiftName IN ('MORNING',
                                 'AFTERNOON1',
                                 'AFTERNOON2',
                                 'GENERAL1',
                                 'GENERAL2',
                                 'NIGHT');



        INSERT INTO T_ATTENDANCE_USER_COVID_STATUS (LoginId,
                                                    AttendanceDate,
                                                    Self_Covid_Status,
                                                    Self_Covid_Remark,
                                                    Family_Covid_Status,
                                                    Family_Covid_Remark,
                                                    UpdatedOn,
                                                    UpdatedBy)
           SELECT V_LoginId,
                  V_AttendanceDate,
                  V_Self_Covid_Status,
                  V_Self_Covid_Remark,
                  V_Family_Covid_Status,
                  V_Family_Covid_Remark,
                  SYSTIMESTAMP,
                  UPPER (V_LoginId)
             FROM DUAL
            WHERE V_SignType = 'SIGNIN';

END UpdateAttendance;

Answer 1

Among other errors, you cannot use a SELECT statement on its own in PL/SQL. You need to SELECT ... [BULK COLLECT] INTO .... However, you don't even want to do that and instead should combine your calendar generation into the INSERT statement:

INSERT INTO T_ATTENDANCE_ATTENDANCE(
  LoginId,
  AttendanceDate,
  ShiftName,
  ShiftStartTime,
  ShiftEndTime,
  Active,
  AttendanceStatus
)
WITH DaysInMonth (dates) AS (
  SELECT V_Date
  FROM   DUAL
UNION ALL
  SELECT dates + INTERVAL '1' DAY
  FROM   DaysInMonth
  WHERE  dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(v_date, 'MONTH'), 1)
)
SELECT u.LoginID,
       d.dates,
       NULL,
       NULL,
       NULL,
       1,
       'Approved'
FROM   DaysInMonth d
       CROSS JOIN T_ATTENDANCE_USER_MASTER u
WHERE  LoginId  = V_LoginId
AND    NOT EXISTS(
         SELECT LoginId
         FROM   T_ATTENDANCE_ATTENDANCE
         WHERE  LoginId = V_LoginId
         AND    ATTENDANCEDATE >= TRUNC(v_date, 'MONTH')
         AND    ATTENDANCEDATE <  ADD_MONTHS(TRUNC(v_date, 'MONTH'), 1)
         AND    ACTIVE         = 1
       );

Additionally, E.Company NOT IN ('') and E.Company = '' will never be true as, in Oracle, '' is identical to NULL and Oracle uses trinary logic so E.Company NOT IN (NULL) and E.Company = NULL do not evaluate to TRUE or FALSE but, instead, both evaluate to NULL and since NULL is not TRUE then neither branch of your logic will match.

What you want is to use E.Company IS NOT NULL and E.company IS NULL to match non-empty and empty strings.