By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE T_ATTENDANCE_USER_MASTER (
"LOGINID" VARCHAR2(200 BYTE) NOT NULL ENABLE
)
CREATE TABLE /*"PROMETHEAN".*/"T_ATTENDANCE_ATTENDANCE" (
"ATTENDANCEID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"LOGINID" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"ATTENDANCEDATE" TIMESTAMP (3),
"SHIFTNAME" VARCHAR2(50 BYTE),
"SHIFTSTARTTIME" TIMESTAMP (3),
"SHIFTENDTIME" TIMESTAMP (3),
"SIGNIN" TIMESTAMP (3),
"SIGNOUT" TIMESTAMP (3),
"OLDATTENDANCEID" NUMBER(10,0),
"ACTIVE" NUMBER(10,0),
"REMARK" VARCHAR2(255 BYTE),
"ATTENDANCESTATUS" VARCHAR2(20 BYTE),
"L1APPROVEDDBY" VARCHAR2(20 BYTE),
"L1APPROVEDON" TIMESTAMP (3),
"L2APPROVEDBY" VARCHAR2(20 BYTE),
"L2APPROVALON" TIMESTAMP (3),
"CREATEDBY" VARCHAR2(20 BYTE),
"CREATEDON" TIMESTAMP (3),
"MODIFIEDBY" VARCHAR2(20 BYTE),
"MODIFIEDON" TIMESTAMP (3),
"IPADDRESS" VARCHAR2(100 BYTE),
"LOGONUSERNAME" VARCHAR2(100 BYTE),
"COMPOFFDATE" DATE,
"WORKLOCATIONTYPE" VARCHAR2(10 BYTE),
"WORKLOCATION" VARCHAR2(50 BYTE),
PRIMARY KEY ("ATTENDANCEID")
)
CREATE PACKAGE package_name AS
TYPE t_cursor IS REF CURSOR;
PROCEDURE getCalendar (
V_Year VARCHAR2,
V_Month VARCHAR2,
V_LoginId varchar2,
curAttendanceDate OUT T_CURSOR
);
END;
/
CREATE PACKAGE BODY package_name AS
PROCEDURE getCalendar (
V_Year VARCHAR2,
V_Month VARCHAR2,
V_LoginId varchar2,
curAttendanceDate OUT T_CURSOR
)
AS
V_Date DATE := TO_DATE (V_Year || '-' || V_Month || '-01', 'yyyy-mm-dd');
BEGIN
MERGE INTO T_ATTENDANCE_ATTENDANCE dst
USING (
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'), 2)
)
SELECT u.LoginID,
d.dates,
(TRUNC(d.dates, 'IW') - a.start_date) / 7 AS week
FROM daysinmonth d
CROSS JOIN T_ATTENDANCE_USER_MASTER U
CROSS JOIN LATERAL (
SELECT TRUNC(COALESCE(MIN(attendancedate), v_date), 'IW') AS start_date
FROM T_ATTENDANCE_ATTENDANCE a
WHERE u.loginid = a.loginid
) a
WHERE loginid = V_LoginId
) src
ON (dst.loginid = src.loginid AND dst.attendancedate = src.dates)
WHEN NOT MATCHED THEN
INSERT (
SELECT * FROM USER_ERRORS;