/* ------- ƒJƒeƒSƒŠ -------- */

CREATE OR REPLACE VIEW V_KATE_S AS
SELECT
	 ‘å•Š—Þš°ÄÞ		"‘å•Š—Þš°ÄÞ"
	,SUBSTR(ķÚÞØš°ÄÞ,1,2)	"Ž•Š—Þš°ÄÞ"
	,SUBSTR(ķÚÞØš°ÄÞ,3,2)	"Ũ•Š—Þš°ÄÞ"
	,–žĖ			"Ũ•Š—Þ–ž"
FROM
    MM_KATE
WHERE
    Ží•Ę = 'Ũ•Š—Þ'
;

/* -------- •”‰Û -------- */


CREATE OR REPLACE VIEW V_BUKA AS
SELECT
	 SUBSTR(–žĖ·°2,1,3)	"•”"
	,SUBSTR(–žĖ·°2,4,2)	"‰Û"
	,–žĖ			"•”‰Û–ž"
FROM
    MM_MEI
WHERE
    –žĖ·°1 = '‰Û'
;

/* -------- ÅV’S“–ŽŌ(“ūˆÓæƒ}ƒXƒ^‚Đ‚į) -------- */

CREATE OR REPLACE VIEW V_TANTO AS
select
 A.“ūˆÓæš°ÄÞ “ūˆÓæš°ÄÞ
,case 
	when (A.‰c‹Æ’S“–ŠJŽn“ú•t2>=A.‰c‹Æ’S“–ŠJŽn“ú•t1 AND B.”„ãŽd“ü“–“ú“ú•t>=A.‰c‹Æ’S“–ŠJŽn“ú•t2 
		OR A.‰c‹Æ’S“–ŠJŽn“ú•t2<A.‰c‹Æ’S“–ŠJŽn“ú•t1 AND B.”„ãŽd“ü“–“ú“ú•t<A.‰c‹Æ’S“–ŠJŽn“ú•t1) AND A.‰c‹Æ’S“–š°ÄÞ2<>'' 
	then A.‰c‹Æ’S“–š°ÄÞ2
 	else A.‰c‹Æ’S“–š°ÄÞ1 end ‰c‹Æ’S“–š°ÄÞ
from
 MM_TOK A
,FE_TODAY B
where
 B.‚j‚d‚x = '‹Æ–ą“ú•tƒ}ƒXƒ^'
;

/* -------- ŒŸŽûˆę——•\ -------- */

CREATE OR REPLACE VIEW V_KENSYU
AS
SELECT
	T1.“`•[”ԍ† 			“`•[”ԍ†
	,T1.s”ԍ†			s”ԍ†
	,T1.“`•[sŽ}”Ô			sŽ}”Ô
	,T1.“`•[“ú•t			“`•[“ú•t
	,T1.ŒŸŽû“ú•t			ŒŸŽû“ú•t
	,T1.’ ‡æš°ÄÞ	 		Žx•Ĩæš°ÄÞ
	,T1.Žæˆøæš°ÄÞ	 		Žd“üæš°ÄÞ
	,T1.Žæˆøæ—Š–ž	 		Žd“üæ—Š–ž
	,T1.‹āŠz 			ŒŸŽû‹āŠz
	,T1.“`•[‡ŒvÁ”ïÅŠz		Á”ïÅ
	,T1.‹āŠz+T1.“`•[‡ŒvÁ”ïÅŠz	ŒŸŽû‹āŠz‡Œv
	,T1.‘Ώێx•Ĩ’ũ“ú		Žx•Ĩ—\’č“ú
FROM 
	FB_RUISEKUK T1
WHERE 
	T1.ŽæˆøŽí•Ę='02'
	AND T1.ŒŸŽû‹æ•Š='1'

UNION ALL

SELECT
	T2.“ü‰Ũ”ԍ†
	,T2.“ü‰Ũs
	,T2.“ü‰ŨsŽ}”Ô
	,T2.“ü‰Ũ“ú
	,T2.ŒŸŽû“ú•t
	,T2.’ ‡æš°ÄÞ
	,T2.Žd“üæš°ÄÞ
	,T2.Žd“üæ—Š–ž
	,T2.Žd“ü‹āŠz
	,T2.Žd“ü“`•[Á”ïÅŠz
	,T2.Žd“ü‹āŠz+T2.Žd“ü“`•[Á”ïÅŠz
	,T2.Žx•Ĩˆ—“ú•t
FROM 
	FE_NYUKA T2
WHERE
	T2.ŒŸŽû‹æ•Š='1'


/* -------- Ī•iŽó•Ĩ–ūŨˆę——•\ -------- */

CREATE OR REPLACE VIEW V_UKEBARAI
AS
SELECT
	T1.Ī•iš°ÄÞ	Ī•iš°ÄÞ
	,T1.Ī•i–ž	Ī•i–ž
	,T1.‹KŠi–ž	‹KŠi
	,T1.Ą–@	Ą–@
	,T1.“`•[“ú•t	“`•[“ú•t
	,T1.Žæˆø‹æ•Š	Žæˆø‹æ•Š
	,L1.ž―ÃŅ–žĖ	Žæˆø‹æ•Š–ž
	,T1.“`•[”ԍ†	“`•[”ԍ†
	,T1.Žæˆøæš°ÄÞ	Žæˆøæš°ÄÞ
	,T1.Žæˆøæ—Š–ž	Žæˆøæ—Š–ž
	,T1.”[“üæš°ÄÞ	”[“üæš°ÄÞ
	,T1.”[“üæ—Š–ž	”[“üæ—Š–ž
	,T1.‘qŒÉš°ÄÞ	‘qŒÉš°ÄÞ
	,L2.‘qŒÉ—Š–ž	‘qŒÉ—Š–ž
	,CASE WHEN T1.ŽæˆøŽí•Ę='02' THEN T1.ĘÞŨ”—Ę ELSE 0 END	Žd“ü”—Ę
	,CASE WHEN T1.ŽæˆøŽí•Ę='02' THEN T1.’P‰ŋ ELSE 0 END	Žd“ü’P‰ŋ
	,CASE WHEN T1.ŽæˆøŽí•Ę='02' THEN T1.‹āŠz ELSE 0 END	Žd“ü‹āŠz
	,CASE WHEN T1.ŽæˆøŽí•Ę='01' THEN T1.ĘÞŨ”—Ę ELSE 0 END	”„ã”—Ę
	,CASE WHEN T1.ŽæˆøŽí•Ę='01' THEN T1.’P‰ŋ ELSE 0 END	”„ã’P‰ŋ
	,CASE WHEN T1.ŽæˆøŽí•Ę='01' THEN T1.‹āŠz ELSE 0 END	”„ã‹āŠz
	,CASE WHEN T1.ŽæˆøŽí•Ę='05' THEN T1.ĘÞŨ”—Ę ELSE 0 END	“üoŒÉ”—Ę
	,CASE WHEN T1.ŽæˆøŽí•Ę='05' THEN T1.Œī‰ŋ’P‰ŋ ELSE 0 END	Žó•Ĩ’P‰ŋ
	,T1.•”š°ÄÞ || T1.‰Ûš°ÄÞ	•”‰Ûš°ÄÞ
	,L3.•”‰Û–ž	•”‰Û–žĖ
	,T1.’S“–ŽŌš°ÄÞ	’S“–ŽŌš°ÄÞ
	,L4.’S“–ŽŌ–ž	’S“–ŽŌ–ž
FROM 
	FB_RUISEKUK T1
	LEFT JOIN ST_SYSMEI L1 ON L1.ž―ÃŅ–žĖ·°1='Žæˆø‹æ•Š' AND T1.Žæˆø‹æ•Š=L1.ž―ÃŅ–žĖ·°2
	LEFT JOIN MM_SOUKO L2 ON T1.‘qŒÉš°ÄÞ=L2.‘qŒÉš°ÄÞ
	LEFT JOIN V_BUKA L3 ON T1.•”š°ÄÞ=L3.•” AND T1.‰Ûš°ÄÞ=L3.‰Û
	LEFT JOIN MM_TAN L4 ON T1.’S“–ŽŌš°ÄÞ=L4.’S“–ŽŌš°ÄÞ
	LEFT JOIN ST_TORIHIKI L5 ON T1.Žæˆø‹æ•Š=L5.Žæˆø‹æ•Š
WHERE
	(T1.ŽæˆøŽí•Ę='01' OR T1.ŽæˆøŽí•Ę='02' OR T1.ŽæˆøŽí•Ę='05')
	AND L5.Žæˆø‹æ•Š IS NOT NULL