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.

Answer 2

If you used a decent editor, code formatter would reveal some errors, such as

• you commented opening ref cursor statement and left columns alone
• missing end if

When fixed, code looks like this, but I have no idea whether it'll compile or not as we don't have your tables.

CREATE OR REPLACE PACKAGE BODY PKG_ATTENDANCE_DASHBOARD
AS
   PROCEDURE Proc_Dashboard (V_CommandType          VARCHAR2,
                             V_LoginId              VARCHAR2,
                             V_SignType             VARCHAR2,
                             V_AttendanceDate       DATE,
                             V_Year                 VARCHAR2,
                             V_Month                VARCHAR2,
                             V_IPAddress            VARCHAR2,
                             V_LogonUserName        VARCHAR2,
                             V_WorkLocation         VARCHAR2,
                             V_WorkLocationType     VARCHAR2,
                             V_Company              VARCHAR2,
                             V_UserType             VARCHAR2,
                             V_ShiftName            VARCHAR2,
                             V_Self_Covid_Status    VARCHAR2,
                             V_Self_Covid_Remark    VARCHAR2,
                             V_Family_Covid_Status  VARCHAR2,
                             V_Family_Covid_Remark  VARCHAR2,
                             V_UpdatedBy            VARCHAR2 /*  curGetCalendar OUT SYS_REFCURSOR,
                                                             curGetCovidStatus OUT SYS_REFCURSOR,
                                                             curListCompanyName OUT SYS_REFCURSOR,
                                                             curListTowerName OUT SYS_REFCURSOR,
                                                             curAttendanceListByDate OUT SYS_REFCURSOR*/
                                                            )
   AS
      V_Date  DATE := TO_DATE (V_Year || '-' || V_Month || '-01', 'yyyy-mm-dd');
   BEGIN
      IF V_CommandType = 'getCalendar'
      THEN
         WITH
            DaysInMonth (dates)
            AS
               (SELECT V_Date AS dates FROM DUAL
                UNION ALL
                SELECT INTERVAL '1' DAY (5) + dates
                  FROM DaysInMonth
                 WHERE EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM V_Date))
         SELECT dates
           FROM DaysInMonth;

         INSERT INTO T_ATTENDANCE_ATTENDANCE (LoginId,
                                              AttendanceDate,
                                              ShiftName,
                                              ShiftStartTime,
                                              ShiftEndTime,
                                              Active,
                                              AttendanceStatus)
            SELECT u.LoginID,
                   d.dates,
                   '',
                   NULL,
                   NULL,
                   1,
                   'Approved'
              FROM DaysInMonth d CROSS JOIN T_ATTENDANCE_USER_MASTER u
             WHERE     EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM V_Date)
                   AND LoginId = V_LoginId
                   AND NOT EXISTS
                          (SELECT LoginId
                             FROM T_ATTENDANCE_ATTENDANCE
                            WHERE     LoginId = V_LoginId
                                  AND EXTRACT (YEAR FROM ATTENDANCEDATE) =
                                      EXTRACT (YEAR FROM V_Date)
                                  AND EXTRACT (MONTH FROM AttendanceDate) =
                                      EXTRACT (MONTH FROM V_Date)
                                  AND ACTIVE = 1);



         V_Date := INTERVAL '1' MONTH + V_Date;


         WITH
            DaysInMonth (dates)
            AS
               (SELECT V_Date AS dates FROM DUAL
                UNION ALL
                SELECT INTERVAL '1' DAY (5) + dates
                  FROM DaysInMonth
                 WHERE EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM V_Date))
         SELECT dates
           FROM DaysInMonth;

         INSERT INTO T_ATTENDANCE_ATTENDANCE (LoginId,
                                              AttendanceDate,
                                              ShiftName,
                                              ShiftStartTime,
                                              ShiftEndTime,
                                              Active,
                                              AttendanceStatus)
            SELECT u.LoginID,
                   d.dates,
                   '',
                   NULL,
                   NULL,
                   1,
                   'Approved'
              FROM DaysInMonth d CROSS JOIN T_ATTENDANCE_USER_MASTER u
             WHERE     EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM V_Date)
                   AND LoginId = V_LoginId
                   AND NOT EXISTS
                          (SELECT LoginId
                             FROM T_ATTENDANCE_ATTENDANCE
                            WHERE     LoginId = V_LoginId
                                  AND EXTRACT (YEAR FROM ATTENDANCEDATE) =
                                      EXTRACT (YEAR FROM V_date)
                                  AND EXTRACT (MONTH FROM AttendanceDate) =
                                      EXTRACT (MONTH FROM V_date)
                                  AND ACTIVE = 1);

         OPEN curGetCalendar FOR
              SELECT AttendanceDate,
                     ShiftName,
                     TO_CHAR (AttendanceDate, 'ddd') cDAY,
                     V_Year,
                     V_Month,
                     TO_CHAR (SignIn, 'yyyy-MM-dd hh:mm:ss tt') SignIn,
                     TO_CHAR (SignOut, 'yyyy-MM-dd hh:mm:ss tt') SignOut
                FROM T_ATTENDANCE_ATTENDANCE
               WHERE     LoginId = V_LoginId
                     AND EXTRACT (YEAR FROM ATTENDANCEDATE) = V_Year
                     AND EXTRACT (MONTH FROM AttendanceDate) = V_Month
                     AND ACTIVE = 1
            ORDER BY AttendanceDate ASC;


         UPDATE a
            SET a.shiftname =
                   (SELECT CASE
                              WHEN     TO_CHAR (TO_DATE (a.attendancedate), 'DAY') IN
                                          ('SATURDAY')
                                   AND a.week IN (2, 4)
                              THEN
                                 'WEEKLYOFF'
                              WHEN TO_CHAR (TO_DATE (a.attendancedate), 'WW') IN
                                      ('SUNDAY')
                              THEN
                                 'WEEKLYOFF'
                              ELSE
                                 'GENERAL1'
                           END
                      FROM (SELECT attendancedate,
                                   shiftname,
                                   ROW_NUMBER ()
                                      OVER (
                                         PARTITION BY TO_CHAR (
                                                         TO_DATE (attendancedate, 'DAY'))
                                         ORDER BY attendancedate) AS week
                              FROM T_ATTENDANCE_ATTENDANCE  a1
                                   LEFT JOIN T_ATTENDANCE_USER_MASTER u
                                      ON u.loginid = a1.loginid
                                   LEFT JOIN T_ATTENDANCE_EMPLOYEE_MASTER e
                                      ON e.empid = u.empid
                             WHERE     a1.loginid = V_loginid
                                   AND e.company IS NULL
                                   AND EXTRACT (YEAR FROM attendancedate) =
                                       EXTRACT (YEAR FROM SYSDATE)
                                   AND EXTRACT (MONTH FROM attendancedate) =
                                       EXTRACT (MONTH FROM SYSDATE)
                                   AND NVL (shiftname, 'x') = 'x') a);
      ELSE
         IF V_CommandType = 'UpdateAttendance'
         THEN
            SELECT CASE
                      WHEN SignOut IS NULL THEN INTERVAL '-1' DAY (5) + p_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
                            SYSTIMESTAMP
                         ELSE
                            SignIn
                      END,
                   SignOut =
                      CASE
                         WHEN     V_SignType = 'SIGNOUT'
                              AND signout IS NULL
                         THEN
                            SYSTIMESTAMP
                         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 IF;

         IF V_CommandType = 'GetCovidStatus'
         THEN
            OPEN cur2 FOR
               SELECT E1.FirstName || ' ' || E1.LastName UpdatedBy,
                      C.*,
                      E.FirstName || ' ' || E.LastName FullName
                 FROM T_ATTENDANCE_USER_COVID_STATUS  C
                      LEFT JOIN T_ATTENDANCE_USER_MASTER U
                         ON     U.LoginID = C.LoginId
                            AND U.IsActive = 'Y'
                      LEFT JOIN T_ATTENDANCE_EMPLOYEE_MASTER E
                         ON     E.EmpID = U.EmpID
                            AND E.IsActive = 'Y'
                      LEFT JOIN T_ATTENDANCE_USER_MASTER U1
                         ON     U1.LoginID = C.UpdatedBy
                            AND U1.IsActive = 'Y'
                      LEFT JOIN T_ATTENDANCE_EMPLOYEE_MASTER E1
                         ON     E1.EmpId = U1.EmpId
                            AND E1.IsActive = 'Y'
                WHERE     C.LoginId = V_LoginId
                      AND C.AttendanceDate = SUBSTR (TO_CHAR (SYSTIMESTAMP, 'yyyy-mm-dd
    hh24:mi:ss.ff3'), 1, 10);
         END IF;


         IF V_CommandType = 'UpdateCovidStatus'
         THEN
            IF NOT EXISTS
                  (SELECT 1
                     FROM T_ATTENDANCE_USER_COVID_STATUS
                    WHERE     AttendanceDate = V_AttendanceDate
                          AND LoginId = V_LoginId)
            THEN
               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_UpdatedBy)
                    FROM DUAL;
            ELSE
               UPDATE T_ATTENDANCE_USER_COVID_STATUS
                  SET self_Covid_Status = V_Self_Covid_Status,
                      Self_Covid_Remark = V_Self_Covid_Remark,
                      Family_Covid_Status = V_Family_Covid_Status,
                      Family_Covid_Remark = V_Family_Covid_Remark,
                      UpdatedOn = SYSTIMESTAMP,
                      UpdatedBy = V_UpdatedBy
                WHERE     AttendanceDate = V_AttendanceDate
                      AND LoginId = V_LoginId;
            END IF;
         END IF;



         IF V_CommandType = 'GetCompany'
         THEN
            OPEN cur3 FOR
               SELECT 1 AS sr, UPPER (CompanyName) CompanyName FROM T_ATTENDANCE_COMPANYS
               UNION
               SELECT 0, 'All Companies' FROM DUAL
               ORDER BY sr ASC;
         END IF;


         IF V_CommandType = 'TeamAvailability'
         THEN
            OPEN cur4 FOR SELECT TowerPk, RTRIM (LTRIM (TowerName)) TowerName
                            FROM T_ATTENDANCE_TOWER_MASTER
                           WHERE IsActive = 'Y'
                          UNION
                          SELECT 0, 'Admin' FROM DUAL
                          ORDER BY TowerName ASC;

            OPEN cur5 FOR
                 SELECT E.FirstName || ' ' || E.LastName AS Name,
                        E.ContactNo,
                        r.RoleName AS Role,
                        A.ShiftName,
                        A.SignIn,
                        A.SignOut,
                        NVL (T.TowerName, 'Admin') TowerName,
                        UPPER (E.Company) AS Company,
                        WorkLocation,
                        WorkLocationType,
                        Covid.Self_Covid_Status,
                        Covid.Family_Covid_Status
                   FROM T_ATTENDANCE_ATTENDANCE A
                        JOIN T_ATTENDANCE_USER_MASTER U
                           ON     A.LoginId = U.LoginID
                              AND U.IsActive = 'Y'
                        JOIN T_ATTENDANCE_EMPLOYEE_MASTER E
                           ON     U.EmpID = E.EmpID
                              AND E.IsActive = 'Y'
                        LEFT JOIN T_ATTENDANCE_USER_ATTENDANCE_TEAM uat
                           ON     uat.LoginId = u.LoginID
                              AND uat.isActive = 'Y'
                        LEFT JOIN T_ATTENDANCE_TOWER_MASTER T ON T.TowerPk = uat.TowerPk
                        JOIN T_ATTENDANCE_ROLE_MASTER R ON R.RolePk = u.RolePk
                        LEFT JOIN T_ATTENDANCE_USER_COVID_STATUS Covid
                           ON     Covid.LoginId = A.LoginId
                              AND Covid.AttendanceDate = a.AttendanceDate
                  WHERE     a.AttendanceDate =
                            CASE
                               WHEN TO_NUMBER (TO_CHAR (SYSTIMESTAMP, 'HOUR')) < 7
                               THEN
                                  INTERVAL '-1' DAY (5) + V_AttendanceDate
                               ELSE
                                  V_AttendanceDate
                            END
                        AND a.Active = 1
                        AND E.Company =
                            CASE V_Company
                               WHEN 'All Companies' THEN E.Company
                               ELSE V_Company
                            END
                        AND (   (    NVL (V_UserType, 'Consultant') = 'Consultant'
                                 AND E.Company NOT IN (''))
                             OR (    NVL (p_UserType, 'Employee') = 'Employee'
                                 AND E.Company = ''))
               ORDER BY T.TowerName, A.SignIn DESC;
         END IF;
      END IF;
   END Proc_Dashboard;
END PKG_ATTENDANCE_DASHBOARD;