CREATE OR REPLACE VIEW "C##NU_EMS_DEV".ATTENDANCE_DETAIL_VIEW AS
SELECT
	ROWNUM AS ID,
	FFU.REG_NO AS REG_NO,
	FFU.REG_NO AS REG_CODE,
	SBI.STD_ID AS STD_ID,
	FFU.EXAM_ROLL_NO AS EXAM_ROLL,
	FFU.EXAM_ROLL_NO AS EXAM_ROLL_NO,
	SBI.NAME AS STUDENT_NAME,
	SBI.FATHER_NAME AS FATHER_NAME,
	SBI.STUDENT_TYPE AS STUDENT_TYPE,
	SBI.GENDER AS GENDER,
	SBI.AC_SESSION AS SESSION_YEAR,
	SBI.AC_SESSION AS AC_SESSION,
	SBI.COLLEGE_CODE AS COLLEGE_CODE,
	COL.COLLEGE_NAME AS COLLEGE_NAME,
	SBI.DEGREE_CODE AS DEGREE_CODE,
	DEG.DEGREE_NAME AS DEGREE_NAME,
	SBI.DEGREE_GROUP_CODE AS DEGREE_GROUP_CODE,
	DEGG.DEGREE_GROUP_NAME AS DEGREE_GROUP_NAME,
	SBI.SUBJECT_CODE AS SUBJECT_CODE,
	SUB.SUBJECT_NAME AS SUBJECT_NAME,
	FFUD.PAPER_CODE AS PAPER_CODE,
	PAP.PAPER_NAME AS PAPER_NAME,
	PAP.PAPER_TYPE AS PAPER_TYPE,
	(CASE WHEN IAD.ATTENDANCE_STATUS IS NULL THEN 0 ELSE 1  END) AS ATTENDANCE_STATUS,
	CEN.CENTER_CODE AS CENTER_CODE,
	CEN.CENTER_NAME AS CENTER_NAME,
	IAD.SCRIPT_QR AS SCRIPT_QR,
	IAD.DATETIME AS ATTENDANCE_DATE,
	IAD.USER_ID AS USER_ID,
	INV.ID AS INVIGILATOR_ID,
	INV.NAME AS INVIGILATOR_NAME,
	IAD.ROOM_NO AS ROOM_NO,
	IAD.BROWSER AS BROWSER,
	IAD.LATITUDE AS LATITUDE,
	IAD.LONGITUDE AS LONGITUDE,
	IAD.IMEI AS IMEI,
	IAD.RW_STATUS AS RW_STATUS,
	FFU.EXAM_INITIATE_ID AS EXAM_INITIATE_ID,
	FFU.EXAM_CODE AS EXAM_CODE,
	EXM.EXAM_NAME AS EXAM_NAME,
	FFU.EXAM_YEAR AS YEAR,
	FFU.EXAM_YEAR AS EXAM_YEAR
FROM FF_FORM_FILL_UPS FFU
JOIN CORE_EXAMS EXM ON EXM.EXAM_CODE = FFU.EXAM_CODE
JOIN FF_FORM_FILL_UP_DETAILS FFUD ON FFUD.FORM_FILL_UP_ID = FFU.ID
JOIN REG_STUDENT_BASICS SBI ON SBI.REG_NO = FFU.REG_NO
JOIN COLLEGE_INFOS COL ON COL.COLLEGE_CODE = SBI.COLLEGE_CODE
JOIN CORE_DEGREES DEG ON DEG.DEGREE_CODE = SBI.DEGREE_CODE
JOIN CORE_DEGREE_GROUPS DEGG ON DEGG.DEGREE_GROUP_CODE = SBI.DEGREE_GROUP_CODE
JOIN CORE_SUBJECTS SUB ON (
	SUB.SUBJECT_CODE = SBI.SUBJECT_CODE AND
	SUB.DEGREE_CODE = SBI.DEGREE_CODE AND
	SUB.DEGREE_GROUP_CODE = SBI.DEGREE_GROUP_CODE
)
JOIN CORE_PAPERS PAP ON PAP.PAPER_CODE = FFUD.PAPER_CODE
LEFT JOIN INV_ATTENDANCE_DETAILS IAD ON (
	IAD.REG_NO = FFU.REG_NO AND
	IAD.PAPER_CODE = FFUD.PAPER_CODE AND
	IAD.EXAM_INITIATE_ID = FFU.EXAM_INITIATE_ID AND
	IAD.EXAM_CODE = FFU.EXAM_CODE AND
	IAD.EXAM_YEAR = FFU.EXAM_YEAR
)
LEFT JOIN INVIGILATORS INV ON INV.USER_ID = IAD.USER_ID
LEFT JOIN CENTER_ALLOC_COLLEGES CAC ON (
	CAC.EXAM_INITIATE_ID = FFU.EXAM_INITIATE_ID AND
	CAC.EXAM_CODE = FFU.EXAM_CODE AND
	CAC.EXAM_YEAR = FFU.EXAM_YEAR AND
	CAC.COLLEGE_CODE = SBI.COLLEGE_CODE AND
	CAC.DEGREE_GROUP_CODE = SBI.DEGREE_GROUP_CODE AND
	CAC.SUBJECT_CODE = SBI.SUBJECT_CODE AND
	CAC.SUB_SELECTED = 1 AND
	CAC.DEG_SELECTED = 1 AND
	CAC."TYPE" = 1
)
LEFT JOIN CENTER_LISTS CEN ON CEN.CENTER_CODE = CAC.CENTER_CODE