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') DAY,
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;
--I am getting error here in below code
Update A
set A.ShiftName = case when to_char(TO_DATE(a.Attendancedate) ,'DAY') in ('SATURDAY') and 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 = '' and extract(year from attendancedate) = extract(year from Sysdate) and extract(month from attendancedate) = extract(month from
Sysdate()) and nvl(shiftname,'') = ''
) A
END IF
END Proc_Dashboard;
END PKG_ATTENDANCE_DASHBOARD;
Code you (initially) posted is only partially Oracle. When fixed:
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
);
I don't know whether it will work or not (I don't have your tables nor data), but - at least - it should compile.
Similarly, for the whole procedure: you should terminate statements with a semi-colon; use a proper subquery in that update:
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;
--I am getting error here in below code
UPDATE a
SET a.shiftname =
(SELECT CASE
WHEN TO_CHAR (TO_DATE (a.attendancedate), 'DAY') IN
('SATURDAY')
AND 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 = ''
AND EXTRACT (YEAR FROM attendancedate) =
EXTRACT (YEAR FROM SYSDATE)
AND EXTRACT (MONTH FROM attendancedate) =
EXTRACT (MONTH FROM SYSDATE ())
AND NVL (shiftname, '') = '') a);
END IF;
END proc_dashboard;
END pkg_attendance_dashboard;