TEST/GW-renewal
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
GW-renewal/DB Scheme
JAE SIK CHO f8427ee1d0 share job
2026-04-09 11:12:12 +09:00

6517 lines
223 KiB
Plaintext
Raw Permalink Blame History

USE [master]
GO
/****** Object: Database [logins_test] Script Date: 2026-03-30 오후 1:12:18 ******/
CREATE DATABASE [logins_test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'gw', FILENAME = N'D:\LOGINS\Database\Groupware\logins_test.mdf' , SIZE = 94720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'gw_log', FILENAME = N'D:\LOGINS\Database\Groupware\logins_test_log.ldf' , SIZE = 3136KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [logins_test] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [logins_test].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [logins_test] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [logins_test] SET ANSI_NULLS OFF
GO
ALTER DATABASE [logins_test] SET ANSI_PADDING OFF
GO
ALTER DATABASE [logins_test] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [logins_test] SET ARITHABORT OFF
GO
ALTER DATABASE [logins_test] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [logins_test] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [logins_test] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [logins_test] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [logins_test] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [logins_test] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [logins_test] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [logins_test] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [logins_test] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [logins_test] SET DISABLE_BROKER
GO
ALTER DATABASE [logins_test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [logins_test] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [logins_test] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [logins_test] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [logins_test] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [logins_test] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [logins_test] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [logins_test] SET RECOVERY FULL
GO
ALTER DATABASE [logins_test] SET MULTI_USER
GO
ALTER DATABASE [logins_test] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [logins_test] SET DB_CHAINING OFF
GO
ALTER DATABASE [logins_test] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [logins_test] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [logins_test] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'logins_test', N'ON'
GO
USE [logins_test]
GO
/****** Object: User [logins] Script Date: 2026-03-30 오후 1:12:19 ******/
CREATE USER [logins] FOR LOGIN [logins] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [logins]
GO
/****** Object: UserDefinedTableType [dbo].[ApprovalParamTableType] Script Date: 2026-03-30 오후 1:12:19 ******/
CREATE TYPE [dbo].[ApprovalParamTableType] AS TABLE(
[CORP_NO] [varchar](15) NULL,
[APRVL_DOC_ID] [int] NULL,
[USR_ID] [varchar](10) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[TableType] Script Date: 2026-03-30 오후 1:12:19 ******/
CREATE TYPE [dbo].[TableType] AS TABLE(
[CORP_NO] [varchar](15) NULL,
[APRVL_DOC_ID] [int] NULL,
[USR_ID] [varchar](10) NULL
)
GO
/****** Object: Table [dbo].[bfile] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bfile](
[ID] [int] NOT NULL,
[b_id] [int] NULL,
[filename] [nvarchar](50) NULL,
[filesize] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[bment] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bment](
[ID] [int] NOT NULL,
[b_id] [int] NULL,
[m_name] [nvarchar](50) NULL,
[m_ment] [ntext] NULL,
[m_date] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[board] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[board](
[ID] [int] NOT NULL,
[title] [nvarchar](250) NULL,
[content] [ntext] NULL,
[date] [nvarchar](50) NULL,
[name] [nvarchar](50) NULL,
[count] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[bphoto] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bphoto](
[ID] [int] NOT NULL,
[b_id] [int] NULL,
[filename] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[confile] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[confile](
[ID] [int] NOT NULL,
[n_id] [int] NULL,
[filename] [nvarchar](50) NULL,
[filesize] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[conotice] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[conotice](
[ID] [int] NOT NULL,
[title] [nvarchar](250) NULL,
[content] [ntext] NULL,
[date] [nvarchar](50) NULL,
[name] [nvarchar](50) NULL,
[count] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[dummy] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dummy](
[idx] [int] IDENTITY(1,1) NOT NULL,
[NM] [varchar](50) NULL,
[VAL] [varchar](50) NULL,
[CD] [varchar](50) NULL,
CONSTRAINT [PK_dummy] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[errorMng] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[errorMng](
[sq] [bigint] IDENTITY(1,1) NOT NULL,
[ie_gbn] [varchar](1) NULL,
[s_code] [varchar](5) NULL,
[s_year] [varchar](2) NULL,
[s_jechl] [varchar](7) NULL,
[com_nm] [varchar](28) NULL,
[j_singo_dt] [varchar](8) NULL,
[s_singo_dt] [varchar](8) NULL,
[j_cd_cd] [int] NULL,
[j_title_cd] [int] NULL,
[j_contents] [varchar](200) NULL,
[j_gwi_cd] [int] NULL,
[j_gwi_user_id1] [varchar](50) NULL,
[j_gwi_user_id2] [varchar](50) NULL,
[reg_user_id] [varchar](50) NULL,
[f_st_cd] [int] NULL,
[f_jj_cd] [int] NULL,
[f_jj_contents] [text] NULL,
[f_jj_nm] [varchar](10) NULL,
[f_jj_reg_dt] [varchar](8) NULL,
[f_jj_reg_gbn] [varchar](1) NULL,
[u_title] [varchar](50) NULL,
[u_res] [varchar](400) NULL,
[u_reg_dt] [varchar](8) NULL,
[ATTACH_NO] [varchar](50) NULL,
[work_dt] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[exco] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[exco](
[ID] [int] NOT NULL,
[singono] [nvarchar](20) NULL,
[importer] [nvarchar](50) NULL,
[singodate] [nvarchar](10) NULL,
[sclass] [nvarchar](50) NULL,
[content] [ntext] NULL,
[name] [nvarchar](50) NULL,
[date] [nvarchar](50) NULL,
[stats] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[f_jj_gbn] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[f_jj_gbn](
[f_jj_cd] [bigint] IDENTITY(1,1) NOT NULL,
[f_jj_des1] [varchar](2) NULL,
[f_jj_des2] [varchar](50) NULL,
[f_jj_des3] [varchar](100) NULL,
CONSTRAINT [PK_f_jj_gbn] PRIMARY KEY CLUSTERED
(
[f_jj_cd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[f_jj_st] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[f_jj_st](
[f_st_cd] [int] IDENTITY(1,1) NOT NULL,
[f_st_des] [varchar](10) NULL,
CONSTRAINT [PK_f_jj_st] PRIMARY KEY CLUSTERED
(
[f_st_cd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[gwi_gbn] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[gwi_gbn](
[j_gwi_cd] [int] IDENTITY(1,1) NOT NULL,
[j_gwi_des] [varchar](10) NULL,
CONSTRAINT [PK_f_gwi_gbn] PRIMARY KEY CLUSTERED
(
[j_gwi_cd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[holiday] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[holiday](
[ID] [int] NOT NULL,
[날짜] [nvarchar](50) NULL,
[설명] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[inco] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[inco](
[ID] [int] NOT NULL,
[singono] [nvarchar](20) NULL,
[importer] [nvarchar](50) NULL,
[singodate] [nvarchar](10) NULL,
[sclass] [nvarchar](50) NULL,
[scause] [nvarchar](50) NULL,
[content] [ntext] NULL,
[name] [nvarchar](50) NULL,
[date] [nvarchar](50) NULL,
[stats] [nvarchar](50) NULL,
[statsdate] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[incolog] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[incolog](
[ID] [int] NOT NULL,
[i_id] [int] NULL,
[ltitle] [nvarchar](50) NULL,
[ltime] [nvarchar](50) NULL,
[lment] [nvarchar](200) NULL,
[lwriter] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[incoment] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[incoment](
[ID] [int] NOT NULL,
[i_id] [int] NULL,
[m_name] [nvarchar](50) NULL,
[m_ment] [ntext] NULL,
[m_date] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[jj_cd] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jj_cd](
[j_cd_cd] [int] IDENTITY(1,1) NOT NULL,
[j_cd_des] [varchar](10) NULL,
CONSTRAINT [PK_f_jj_cd] PRIMARY KEY CLUSTERED
(
[j_cd_cd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[jj_contents] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jj_contents](
[j_title_cd] [int] IDENTITY(1,1) NOT NULL,
[j_title_des] [varchar](20) NULL,
CONSTRAINT [PK_f_jj_contents] PRIMARY KEY CLUSTERED
(
[j_title_cd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[kfile] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kfile](
[ID] [int] NOT NULL,
[b_id] [int] NULL,
[filename] [nvarchar](50) NULL,
[filesize] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[kment] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kment](
[ID] [int] NOT NULL,
[b_id] [int] NULL,
[m_name] [nvarchar](50) NULL,
[m_ment] [ntext] NULL,
[m_date] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[know] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[know](
[ID] [int] NOT NULL,
[title] [nvarchar](250) NULL,
[content] [ntext] NULL,
[date] [nvarchar](50) NULL,
[name] [nvarchar](50) NULL,
[count] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[kphoto] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kphoto](
[ID] [int] NOT NULL,
[b_id] [int] NULL,
[filename] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[latereport] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[latereport](
[ID] [int] NOT NULL,
[name] [nvarchar](255) NULL,
[reason] [ntext] NULL,
[timein] [nvarchar](255) NULL,
[latetime] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[lawfile] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[lawfile](
[ID] [int] NOT NULL,
[법] [nvarchar](50) NULL,
[이름] [nvarchar](50) NULL,
[파일명] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[nfile] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[nfile](
[ID] [int] NOT NULL,
[n_id] [int] NULL,
[filename] [nvarchar](50) NULL,
[filesize] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[nment] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[nment](
[ID] [int] NOT NULL,
[n_id] [int] NULL,
[m_name] [nvarchar](50) NULL,
[m_ment] [ntext] NULL,
[m_date] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[notice] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[notice](
[ID] [int] NOT NULL,
[title] [nvarchar](250) NULL,
[content] [ntext] NULL,
[date] [nvarchar](50) NULL,
[name] [nvarchar](50) NULL,
[count] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[notwork] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[notwork](
[ID] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[date] [nvarchar](10) NULL,
[reason] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[overtime] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[overtime](
[ID] [int] NOT NULL,
[이름] [nvarchar](50) NULL,
[신청일] [nchar](10) NULL,
[구분] [nvarchar](10) NULL,
[전후] [nvarchar](10) NULL,
[내용] [ntext] NULL,
[팀장] [nvarchar](10) NULL,
[팀장승인] [nvarchar](50) NULL,
[본부장] [nvarchar](10) NULL,
[본부장승인] [nvarchar](50) NULL,
[작성일] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[shortwork] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[shortwork](
[ID] [int] NOT NULL,
[name] [nvarchar](255) NULL,
[reason] [ntext] NULL,
[timeout] [nvarchar](255) NULL,
[shorttime] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[start] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[start](
[ID] [int] NOT NULL,
[구분] [nvarchar](50) NULL,
[참조] [nvarchar](200) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[startsat] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[startsat](
[ID] [int] NOT NULL,
[구분] [nvarchar](50) NULL,
[참조] [nvarchar](200) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[suri_count] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[suri_count](
[ss_year] [varchar](4) NOT NULL,
[ss_month] [varchar](2) NOT NULL,
[ss_i_cnt] [int] NULL,
[ss_e_cnt] [int] NULL,
CONSTRAINT [PK_f_suri_count] PRIMARY KEY CLUSTERED
(
[ss_year] ASC,
[ss_month] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0010] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0010](
[CORP_NO] [varchar](15) NOT NULL,
[CORP_NM] [varchar](100) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_CO0010_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0030] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0030](
[CORP_NO] [varchar](15) NOT NULL,
[COMM_CL_CD] [varchar](5) NOT NULL,
[COMM_CL_CD_NM] [varchar](100) NULL,
[COMM_CL_CD_USE_YN] [varchar](1) NOT NULL,
[COMM_CL_CD_DSCRPT] [varchar](500) NULL,
[DSPLY_ORDR] [int] NULL,
[PROP_YN] [varchar](1) NOT NULL,
[PROP_CL1_CD] [varchar](5) NULL,
[PROP_CL2_CD] [varchar](5) NULL,
[PROP_CL3_CD] [varchar](5) NULL,
[PROP_CL4_CD] [varchar](5) NULL,
[PROP_CL5_CD] [varchar](5) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_CO0030_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[COMM_CL_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0040] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0040](
[CORP_NO] [varchar](15) NOT NULL,
[COMM_CL_CD] [varchar](5) NOT NULL,
[COMM_CD] [varchar](4) NOT NULL,
[COMM_CD_NM] [varchar](100) NULL,
[COMM_CD_USE_YN] [varchar](1) NOT NULL,
[COMM_CD_DSCRPT] [varchar](500) NULL,
[COMM_CD_DSPLY_ORDR] [int] NULL,
[COMM_CD_TYPE_VAL] [varchar](10) NULL,
[PROP2_CD] [varchar](4) NULL,
[PROP1_CD] [varchar](4) NULL,
[PROP3_CD] [varchar](4) NULL,
[PROP4_CD] [varchar](4) NULL,
[PROP5_CD] [varchar](4) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_CO0040_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[COMM_CL_CD] ASC,
[COMM_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0050] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0050](
[ATCHFILE_NO] [varchar](50) NOT NULL,
[ATCH_NO] [varchar](50) NULL,
[ATCH_FILE_PATH_NM] [varchar](200) NULL,
[ATCH_FILE_NM] [varchar](200) NULL,
[ATCH_TYPE_NM] [varchar](100) NULL,
[ORGNL_FILE_NM] [varchar](200) NULL,
[ATCH_FILE_MG] [int] NULL,
[TBL_COL_NM] [varchar](100) NULL,
[DEL_DT] [datetime] NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_CO0050_PK] PRIMARY KEY CLUSTERED
(
[ATCHFILE_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0060] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0060](
[SEQUENCE_NM] [varchar](100) NOT NULL,
[SEQUENCE_VAL] [int] NULL,
CONSTRAINT [SX_CO0060_PK] PRIMARY KEY CLUSTERED
(
[SEQUENCE_NM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0070] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0070](
[CORP_NO] [varchar](15) NOT NULL,
[WORK_CD] [varchar](10) NOT NULL,
[WORK_CD_TITLE_NM] [varchar](200) NULL,
[GOTOWORK_TM_NM] [varchar](10) NULL,
[GETOFFWORK_TM_NM] [varchar](10) NULL,
[PAY_DIV_NM] [varchar](100) NULL,
[REGULAR_WORK_TM] [decimal](5, 2) NULL,
[REST_TM] [decimal](5, 2) NULL,
[INCLU_WORK_NM] [varchar](100) NULL,
[INCLU_WORK_START_TM] [varchar](100) NULL,
[INCLU_WORK_END_TM] [varchar](100) NULL,
[INCLU_WORK_TM] [decimal](5, 2) NULL,
[CALC_LOCA_NM] [varchar](10) NULL,
[OT_START_TM] [varchar](100) NULL,
[NGT_ALLW_NM] [varchar](100) NULL,
[NGT_START_TM] [varchar](10) NULL,
[NGT_END_TM] [varchar](10) NULL,
[WORK_ALLW_NM] [varchar](200) NULL,
[WORK_CD_USE_YN] [varchar](1) NOT NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[WORK_CN] [varchar](4000) NULL,
[OT_BSCRT] [decimal](5, 2) NULL,
[NGT_BSCRT] [decimal](5, 2) NULL,
[APLY_WORK_DAY] [decimal](5, 2) NULL,
[YYCT_DEDU_DAY] [decimal](5, 2) NULL,
[MEAL_START_TM] [varchar](10) NULL,
[MEAL_END_TM] [varchar](10) NULL,
[HOLIDAY_YN] [varchar](10) NULL,
CONSTRAINT [SX_CO0070_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[WORK_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0080] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0080](
[CORP_NO] [varchar](15) NOT NULL,
[MENU_NO] [int] NOT NULL,
[UPPER_MENU_NO] [int] NULL,
[MENU_NM] [varchar](200) NULL,
[URL] [varchar](300) NULL,
[RM] [varchar](1000) NULL,
[MENU_PROP] [varchar](1000) NULL,
[MENU_ORDR] [int] NULL,
[MENU_USE_YN] [varchar](1) NOT NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[CONTROLLER] [varchar](100) NULL,
[MENU_LVL] [int] NULL,
CONSTRAINT [SX_CO0080_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[MENU_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0090] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0090](
[CORP_NO] [varchar](15) NOT NULL,
[MENU_NO] [int] NOT NULL,
[MENU_AUTH_CD] [varchar](4) NOT NULL,
[AUTH_MENU_PROP] [varchar](1000) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[FUNC_AUTH_CN] [varchar](100) NULL,
CONSTRAINT [SX_CO0090_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[MENU_NO] ASC,
[MENU_AUTH_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0100] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0100](
[SEQ] [numeric](38, 0) IDENTITY(1,1) NOT NULL,
[WORK_DT] [varchar](8) NOT NULL,
[WORK_ID] [nvarchar](200) NULL,
[WORK_NM] [nvarchar](100) NULL,
[WORK_TYPE] [int] NULL,
CONSTRAINT [PK_SX_CO0100] PRIMARY KEY CLUSTERED
(
[SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0110] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0110](
[SEQ] [bigint] IDENTITY(1,1) NOT NULL,
[WORK_NM] [nvarchar](100) NULL,
CONSTRAINT [PK_SX_CO0110] PRIMARY KEY CLUSTERED
(
[SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0120] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0120](
[SEQ] [int] IDENTITY(1,1) NOT NULL,
[B_NM] [nvarchar](30) NULL,
[B_TERM] [int] NULL,
[B_CUT] [datetime] NULL,
[B_NEXT] [datetime] NULL,
CONSTRAINT [PK_SX_CO0120] PRIMARY KEY CLUSTERED
(
[SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_CO0130] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_CO0130](
[SEQ] [int] IDENTITY(1,1) NOT NULL,
[B_NM] [nvarchar](30) NULL,
[B_DAY] [int] NULL,
[B_DAY_VIEW] [nvarchar](10) NULL,
[B_TIME] [nvarchar](5) NULL,
CONSTRAINT [PK_SX_CO0130] PRIMARY KEY CLUSTERED
(
[SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0010] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0010](
[CORP_NO] [varchar](15) NOT NULL,
[USR_ID] [varchar](10) NOT NULL,
[USR_SORT_ORDR] [int] NULL,
[USR_NM] [varchar](100) NOT NULL,
[PW] [varchar](100) NOT NULL,
[DUTY_CD] [varchar](4) NULL,
[BRTHDY_DATE] [varchar](8) NULL,
[USR_TELNO] [varchar](15) NULL,
[MTEL_NO] [varchar](15) NULL,
[EMAIL] [varchar](100) NULL,
[BASE_ADRS] [varchar](100) NULL,
[GUSO_ADRS] [varchar](100) NULL,
[GUSO_TELNO] [varchar](15) NULL,
[JOINCP_DATE] [varchar](8) NULL,
[SALARY] [int] NULL,
[BZ_CD] [varchar](4) NULL,
[WKDAY_WORK_CD] [varchar](4) NULL,
[WKEND_WORK_CD] [varchar](4) NULL,
[OT_AMT] [int] NULL,
[OT_CD] [varchar](4) NULL,
[FD_EXPENSES_CD] [varchar](4) NULL,
[SPOUSE_YN] [varchar](1) NOT NULL,
[SPT_FAMILY_CNT] [int] NULL,
[TEAM_CD] [varchar](4) NULL,
[MDCHIT_NM] [varchar](100) NULL,
[FINAL_SCHSP_NM] [varchar](100) NULL,
[DISML_DATE] [varchar](8) NULL,
[RETIREMENT_DATE] [varchar](8) NULL,
[RETIREMENT_RSN_CN] [varchar](500) NULL,
[MNYVALUA_ADJ_CN] [varchar](500) NULL,
[CRR_CN] [varchar](max) NULL,
[QUAL_CN] [varchar](max) NULL,
[CNTRCT_CNDT_CN] [varchar](max) NULL,
[SPCLT_ARTC_CN] [varchar](max) NULL,
[PHOTO_ATCHFILE_NO] [varchar](50) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[SPMT_EMAIL] [varchar](100) NULL,
[SPMT_MTEL_NO] [varchar](15) NULL,
[RRNO] [varchar](7) NULL,
[LOGIN_ID] [varchar](100) NULL,
[APPR_YN] [varchar](1) NULL,
CONSTRAINT [SX_GW0010_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[USR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [LOGIN_ID_UNIQE] UNIQUE NONCLUSTERED
(
[LOGIN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0020] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0020](
[CORP_NO] [varchar](15) NOT NULL,
[UNTY_BBS_CD] [varchar](4) NOT NULL,
[UNTY_BBS_SNO] [int] NOT NULL,
[BBS_TITLE_NM] [varchar](200) NULL,
[BBS_CN] [text] NULL,
[INQR_CNT] [int] NULL,
[CTUSR_ID] [varchar](10) NULL,
[ETC_ATCH_NO] [varchar](50) NULL,
[PHOTO_ATCH_NO] [varchar](50) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[CMMT_CNT] [int] NULL,
CONSTRAINT [SX_GW0020_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[UNTY_BBS_CD] ASC,
[UNTY_BBS_SNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0030] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0030](
[CORP_NO] [varchar](15) NOT NULL,
[UNTY_BBS_CD] [varchar](4) NOT NULL,
[UNTY_BBS_SNO] [int] NOT NULL,
[CMMT_SNO] [int] NOT NULL,
[CMMT_CN] [varchar](max) NULL,
[CMMT_CTUSR_ID] [varchar](10) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_GW0030_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[UNTY_BBS_CD] ASC,
[UNTY_BBS_SNO] ASC,
[CMMT_SNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0050] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0050](
[CORP_NO] [varchar](15) NOT NULL,
[USR_ID] [varchar](10) NOT NULL,
[WORK_PLAN_YYMMDD] [varchar](8) NOT NULL,
[PLAN_WORK_CD] [varchar](10) NULL,
[WORK_CD] [varchar](10) NULL,
[WORK_START_DT] [datetime] NULL,
[WORK_END_DT] [datetime] NULL,
[TOT_WORK_MIN] [int] NULL,
[LATE_MIN] [int] NULL,
[ABTI_YN] [varchar](1) NOT NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[INCLU_WORK_OT_MIN] [int] NULL,
[OT_WORK_MIN] [int] NULL,
[NGT_OT_MIN] [int] NULL,
[SKIPOFF_REMN_BZ_MIN] [int] NULL,
[OT_RCTN_YN] [varchar](1) NULL,
[SORT_ODR] [int] NULL,
[OUTING_MIN] [int] NULL,
[OUTING_CNT] [int] NULL,
[REAL_START_DT] [datetime] NULL,
[REAL_END_DT] [datetime] NULL,
[EL_RCTN_YN] [varchar](1) NULL,
[HOLIDAY_LATE_MIN] [int] NULL,
CONSTRAINT [SX_GW0050_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[USR_ID] ASC,
[WORK_PLAN_YYMMDD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0060] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0060](
[CORP_NO] [varchar](15) NOT NULL,
[USR_ID] [varchar](10) NOT NULL,
[YYVCT_YY] [varchar](4) NOT NULL,
[YYVCT_CNT] [float] NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[SORT_ODR] [int] NULL,
[REFCN] [varchar](2000) NULL,
CONSTRAINT [SX_GW0060_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[USR_ID] ASC,
[YYVCT_YY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0080] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0080](
[CORP_NO] [varchar](15) NOT NULL,
[APRVL_DOC_ID] [int] NOT NULL,
[LATE_SKIPOFF_ABTI_OT_CD] [varchar](4) NULL,
[LATE_SKIPOFF_ABTI_OT_DATE] [varchar](8) NULL,
[LATE_SKIPOFF_OT_DT] [datetime] NULL,
[LATE_SKIPOFF_OT_DT2] [datetime] NULL,
[LATE_SKIPOFF_OT_MIN] [int] NULL,
[NML_WORK_RCNTN_YN] [varchar](1) NOT NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[REQUEST_DT] [datetime] NULL,
[REQUEST_DT2] [datetime] NULL,
CONSTRAINT [SX_GW0080_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[APRVL_DOC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0090] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0090](
[CORP_NO] [varchar](15) NOT NULL,
[APRVL_DOC_ID] [int] NOT NULL,
[APRVL_STUS_CD] [varchar](4) NULL,
[APRVL_KIND_CD] [varchar](4) NULL,
[APLNT_ID] [varchar](10) NULL,
[OFFER_DT] [datetime] NULL,
[CMPL_DT] [datetime] NULL,
[APLNT_CN] [varchar](1000) NULL,
[BZ_CN] [varchar](1000) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[ATCH_NO] [varchar](50) NULL,
[OFFER_CN] [varchar](4000) NULL,
[APRVL_CMPL_SNO] [int] NULL,
[FINAL_APRVL_SNO] [int] NULL,
[BZ_DEPUTY_ID] [varchar](10) NULL,
[DOC_FLAG] [varchar](2) NULL,
CONSTRAINT [SX_GW0090_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[APRVL_DOC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0100] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0100](
[CORP_NO] [varchar](15) NOT NULL,
[APRVL_DOC_ID] [int] NOT NULL,
[APRVL_SNO] [int] NOT NULL,
[APPR_ID] [varchar](10) NULL,
[APPR_STUS_CD] [varchar](4) NULL,
[APRVL_DT] [datetime] NULL,
[APPR_CN] [varchar](1000) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
[DOC_FLAG] [varchar](2) NULL,
CONSTRAINT [SX_GW0100_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[APRVL_DOC_ID] ASC,
[APRVL_SNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0110] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0110](
[CORP_NO] [varchar](15) NOT NULL,
[APRVL_DOC_ID] [int] NOT NULL,
[UPD_DATE] [varchar](8) NOT NULL,
[UPD_BF_WORK_CD] [varchar](4) NULL,
[UPD_WORK_CD] [varchar](4) NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_GW0110_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[APRVL_DOC_ID] ASC,
[UPD_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0120] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0120](
[CORP_NO] [varchar](15) NOT NULL,
[APRVL_DOC_ID] [int] NOT NULL,
[YYVCT_DATE] [varchar](8) NULL,
[BZ_DEPUTY_ID] [varchar](10) NOT NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_GW0120_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[APRVL_DOC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0130] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0130](
[CORP_NO] [varchar](15) NOT NULL,
[USR_ID] [varchar](10) NOT NULL,
[MENU_AUTH_CD] [varchar](4) NOT NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_GW0130_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[USR_ID] ASC,
[MENU_AUTH_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SX_GW0140] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SX_GW0140](
[CORP_NO] [varchar](15) NOT NULL,
[USR_ID] [varchar](10) NOT NULL,
[LV_1_APPR_YN] [varchar](1) NOT NULL,
[LV_2_APPR_YN] [varchar](1) NOT NULL,
[LV_3_APPR_YN] [varchar](1) NOT NULL,
[LV_4_APPR_YN] [varchar](1) NOT NULL,
[LV_5_APPR_YN] [varchar](1) NOT NULL,
[RGSTR_ID] [varchar](10) NOT NULL,
[RGST_DT] [datetime] NOT NULL,
[MODID] [varchar](10) NULL,
[UPD_DT] [datetime] NOT NULL,
CONSTRAINT [SX_GW0140_PK] PRIMARY KEY CLUSTERED
(
[CORP_NO] ASC,
[USR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[team] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[team](
[ID] [int] NOT NULL,
[구분] [nvarchar](50) NULL,
[참조] [nvarchar](200) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[timecode] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[timecode](
[ID] [int] NOT NULL,
[구분] [nvarchar](255) NULL,
[출근] [nvarchar](255) NULL,
[퇴근] [nvarchar](255) NULL,
[휴일] [int] NULL,
[제목] [nvarchar](50) NULL,
[참조] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[users] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
[ID] [int] NOT NULL,
[순서] [int] NULL,
[u_name] [nvarchar](50) NULL,
[u_pass] [nvarchar](50) NULL,
[직책] [nvarchar](50) NULL,
[u_power] [int] NULL,
[생일] [nvarchar](50) NULL,
[집전화] [nvarchar](50) NULL,
[휴대폰] [nvarchar](50) NULL,
[이메일] [nvarchar](50) NULL,
[주소] [nvarchar](100) NULL,
[거소] [nvarchar](100) NULL,
[거소전화] [nvarchar](50) NULL,
[입사일] [nvarchar](50) NULL,
[연봉] [int] NULL,
[연가] [int] NULL,
[업무] [nvarchar](50) NULL,
[출근] [nvarchar](50) NULL,
[주말] [nvarchar](50) NULL,
[OT] [int] NULL,
[OT구분] [nvarchar](50) NULL,
[식비] [nvarchar](50) NULL,
[배우자] [nvarchar](50) NULL,
[부양가족] [int] NULL,
[팀] [nvarchar](50) NULL,
[병력] [nvarchar](50) NULL,
[학력] [nvarchar](50) NULL,
[해고일] [nvarchar](50) NULL,
[퇴직일] [nvarchar](50) NULL,
[퇴직사유] [nvarchar](250) NULL,
[금품청산] [nvarchar](250) NULL,
[경력] [ntext] NULL,
[자격] [ntext] NULL,
[계약조건] [ntext] NULL,
[참조] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[vacation] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[vacation](
[ID] [int] NOT NULL,
[구분] [nvarchar](20) NULL,
[예정일] [ntext] NULL,
[일수] [real] NULL,
[내용] [ntext] NULL,
[이름] [nvarchar](10) NULL,
[업무] [ntext] NULL,
[작성일] [nvarchar](50) NULL,
[대행자] [nvarchar](10) NULL,
[대행승인] [nvarchar](50) NULL,
[결재자] [nvarchar](50) NULL,
[결재] [nvarchar](50) NULL,
[결재일] [nvarchar](50) NULL,
[차감] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[vacationt] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[vacationt](
[ID] [int] NOT NULL,
[년도] [nvarchar](50) NULL,
[이름] [nvarchar](50) NULL,
[연가] [int] NULL,
[참조] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[workchange] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[workchange](
[ID] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[date1] [nvarchar](50) NULL,
[date2] [nvarchar](50) NULL,
[workplan] [nvarchar](50) NULL,
[workchange] [nvarchar](50) NULL,
[사유] [ntext] NULL,
[업무] [ntext] NULL,
[작성일] [nvarchar](50) NULL,
[대행자] [nvarchar](50) NULL,
[대행승인] [nvarchar](50) NULL,
[승인] [nvarchar](50) NULL,
[승인시각] [nvarchar](50) NULL,
[승인자] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[workplan] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[workplan](
[ID] [int] NOT NULL,
[u_id] [int] NULL,
[name] [nvarchar](50) NULL,
[date] [nvarchar](50) NULL,
[주말] [nvarchar](50) NULL,
[업무] [nvarchar](50) NULL,
[d1] [nvarchar](50) NULL,
[d2] [nvarchar](50) NULL,
[d3] [nvarchar](50) NULL,
[d4] [nvarchar](50) NULL,
[d5] [nvarchar](50) NULL,
[d6] [nvarchar](50) NULL,
[d7] [nvarchar](50) NULL,
[d8] [nvarchar](50) NULL,
[d9] [nvarchar](50) NULL,
[d10] [nvarchar](50) NULL,
[d11] [nvarchar](50) NULL,
[d12] [nvarchar](50) NULL,
[d13] [nvarchar](50) NULL,
[d14] [nvarchar](50) NULL,
[d15] [nvarchar](50) NULL,
[d16] [nvarchar](50) NULL,
[d17] [nvarchar](50) NULL,
[d18] [nvarchar](50) NULL,
[d19] [nvarchar](50) NULL,
[d20] [nvarchar](50) NULL,
[d21] [nvarchar](50) NULL,
[d22] [nvarchar](50) NULL,
[d23] [nvarchar](50) NULL,
[d24] [nvarchar](50) NULL,
[d25] [nvarchar](50) NULL,
[d26] [nvarchar](50) NULL,
[d27] [nvarchar](50) NULL,
[d28] [nvarchar](50) NULL,
[d29] [nvarchar](50) NULL,
[d30] [nvarchar](50) NULL,
[d31] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[works] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[works](
[ID] [int] NOT NULL,
[구분] [nvarchar](50) NULL,
[참조] [nvarchar](200) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[worktime] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[worktime](
[ID] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[date] [nvarchar](50) NULL,
[timein] [nvarchar](50) NULL,
[timeout] [nvarchar](50) NULL,
[worktime] [real] NULL,
[ottime0] [real] NULL,
[ottime1] [real] NULL,
[ottime2] [real] NULL,
[ottime3] [real] NULL,
[ottime4] [real] NULL,
[tottime] [real] NULL,
[food] [real] NULL,
[otdetail] [nvarchar](200) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[환경설정] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[환경설정](
[ID] [int] NOT NULL,
[ot] [real] NULL,
[nt] [real] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX01_SX_GW0010] Script Date: 2026-03-30 오후 1:12:19 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX01_SX_GW0010] ON [dbo].[SX_GW0010]
(
[CORP_NO] ASC,
[USR_NM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[errorMng] ADD CONSTRAINT [DF_errorMng_work_dt] DEFAULT (getdate()) FOR [work_dt]
GO
ALTER TABLE [dbo].[SX_CO0030] ADD CONSTRAINT [DF_SX_CO0030_PROP_YN] DEFAULT ('N') FOR [PROP_YN]
GO
ALTER TABLE [dbo].[SX_CO0040] ADD CONSTRAINT [DF_SX_CO0040_COMM_CD_USE_YN] DEFAULT ('N') FOR [COMM_CD_USE_YN]
GO
ALTER TABLE [dbo].[SX_CO0070] ADD CONSTRAINT [DF_SX_CO0070_WORK_CD_USE_YN] DEFAULT ('N') FOR [WORK_CD_USE_YN]
GO
ALTER TABLE [dbo].[SX_CO0070] ADD CONSTRAINT [DF_SX_CO0070_HOLIDAY_YN] DEFAULT ('N') FOR [HOLIDAY_YN]
GO
ALTER TABLE [dbo].[SX_CO0100] ADD CONSTRAINT [DF_SX_CO0100_WORK_DT] DEFAULT (CONVERT([varchar],getdate(),(112))) FOR [WORK_DT]
GO
ALTER TABLE [dbo].[SX_GW0010] ADD CONSTRAINT [DF_SX_GW0010_SPOUSE_YN] DEFAULT ('N') FOR [SPOUSE_YN]
GO
ALTER TABLE [dbo].[SX_GW0010] ADD CONSTRAINT [DF_SX_GW0010_APPR_YN] DEFAULT ('N') FOR [APPR_YN]
GO
ALTER TABLE [dbo].[SX_GW0050] ADD CONSTRAINT [DF_SX_GW0050_ABTI_YN] DEFAULT ('N') FOR [ABTI_YN]
GO
ALTER TABLE [dbo].[SX_GW0080] ADD CONSTRAINT [DF_SX_GW0080_NML_WORK_RCNTN_YN] DEFAULT ('N') FOR [NML_WORK_RCNTN_YN]
GO
ALTER TABLE [dbo].[SX_GW0140] ADD CONSTRAINT [DF_SX_GW0140_LV_1_APPR_YN] DEFAULT ('N') FOR [LV_1_APPR_YN]
GO
ALTER TABLE [dbo].[SX_GW0140] ADD CONSTRAINT [DF_SX_GW0140_LV_2_APPR_YN] DEFAULT ('N') FOR [LV_2_APPR_YN]
GO
ALTER TABLE [dbo].[SX_GW0140] ADD CONSTRAINT [DF_SX_GW0140_LV_3_APPR_YN] DEFAULT ('N') FOR [LV_3_APPR_YN]
GO
ALTER TABLE [dbo].[SX_GW0140] ADD CONSTRAINT [DF_SX_GW0140_LV_4_APPR_YN] DEFAULT ('N') FOR [LV_4_APPR_YN]
GO
ALTER TABLE [dbo].[SX_GW0140] ADD CONSTRAINT [DF_SX_GW0140_LV_5_APPR_YN] DEFAULT ('N') FOR [LV_5_APPR_YN]
GO
ALTER TABLE [dbo].[SX_CO0080] WITH CHECK ADD CONSTRAINT [SX_CO0080_SX_CO0080_FK01] FOREIGN KEY([CORP_NO], [UPPER_MENU_NO])
REFERENCES [dbo].[SX_CO0080] ([CORP_NO], [MENU_NO])
GO
ALTER TABLE [dbo].[SX_CO0080] CHECK CONSTRAINT [SX_CO0080_SX_CO0080_FK01]
GO
ALTER TABLE [dbo].[SX_CO0090] WITH CHECK ADD CONSTRAINT [SX_CO0090_SX_CO0080_FK01] FOREIGN KEY([CORP_NO], [MENU_NO])
REFERENCES [dbo].[SX_CO0080] ([CORP_NO], [MENU_NO])
GO
ALTER TABLE [dbo].[SX_CO0090] CHECK CONSTRAINT [SX_CO0090_SX_CO0080_FK01]
GO
ALTER TABLE [dbo].[SX_GW0010] WITH CHECK ADD CONSTRAINT [SX_GW0010_SX_CO0010_FK01] FOREIGN KEY([CORP_NO])
REFERENCES [dbo].[SX_CO0010] ([CORP_NO])
GO
ALTER TABLE [dbo].[SX_GW0010] CHECK CONSTRAINT [SX_GW0010_SX_CO0010_FK01]
GO
ALTER TABLE [dbo].[SX_GW0020] WITH CHECK ADD CONSTRAINT [SX_GW0020_SX_CO0010_FK01] FOREIGN KEY([CORP_NO])
REFERENCES [dbo].[SX_CO0010] ([CORP_NO])
GO
ALTER TABLE [dbo].[SX_GW0020] CHECK CONSTRAINT [SX_GW0020_SX_CO0010_FK01]
GO
ALTER TABLE [dbo].[SX_GW0030] WITH CHECK ADD CONSTRAINT [SX_GW0030_SX_GW0020_FK01] FOREIGN KEY([CORP_NO], [UNTY_BBS_CD], [UNTY_BBS_SNO])
REFERENCES [dbo].[SX_GW0020] ([CORP_NO], [UNTY_BBS_CD], [UNTY_BBS_SNO])
GO
ALTER TABLE [dbo].[SX_GW0030] CHECK CONSTRAINT [SX_GW0030_SX_GW0020_FK01]
GO
ALTER TABLE [dbo].[SX_GW0050] WITH CHECK ADD CONSTRAINT [SX_GW0050_SX_GW0010_FK01] FOREIGN KEY([CORP_NO], [USR_ID])
REFERENCES [dbo].[SX_GW0010] ([CORP_NO], [USR_ID])
GO
ALTER TABLE [dbo].[SX_GW0050] CHECK CONSTRAINT [SX_GW0050_SX_GW0010_FK01]
GO
ALTER TABLE [dbo].[SX_GW0110] WITH CHECK ADD CONSTRAINT [SX_GW0110_SX_GW0090_FK01] FOREIGN KEY([CORP_NO], [APRVL_DOC_ID])
REFERENCES [dbo].[SX_GW0090] ([CORP_NO], [APRVL_DOC_ID])
GO
ALTER TABLE [dbo].[SX_GW0110] CHECK CONSTRAINT [SX_GW0110_SX_GW0090_FK01]
GO
ALTER TABLE [dbo].[SX_GW0120] WITH CHECK ADD CONSTRAINT [SX_GW0120_SX_GW0090_FK01] FOREIGN KEY([CORP_NO], [APRVL_DOC_ID])
REFERENCES [dbo].[SX_GW0090] ([CORP_NO], [APRVL_DOC_ID])
GO
ALTER TABLE [dbo].[SX_GW0120] CHECK CONSTRAINT [SX_GW0120_SX_GW0090_FK01]
GO
ALTER TABLE [dbo].[SX_GW0130] WITH CHECK ADD CONSTRAINT [SX_GW0130_SX_GW0010_FK01] FOREIGN KEY([CORP_NO], [USR_ID])
REFERENCES [dbo].[SX_GW0010] ([CORP_NO], [USR_ID])
GO
ALTER TABLE [dbo].[SX_GW0130] CHECK CONSTRAINT [SX_GW0130_SX_GW0010_FK01]
GO
ALTER TABLE [dbo].[SX_GW0140] WITH CHECK ADD CONSTRAINT [SX_GW0140_SX_GW0010_FK01] FOREIGN KEY([CORP_NO], [USR_ID])
REFERENCES [dbo].[SX_GW0010] ([CORP_NO], [USR_ID])
GO
ALTER TABLE [dbo].[SX_GW0140] CHECK CONSTRAINT [SX_GW0140_SX_GW0010_FK01]
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_delete] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-02-02
-- Create Description : 스카이브릿지 그룹웨어 결재문서 삭제
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_approval_delete '1111',0, '00001'
-- exec pr_skybridge_gw_approval_delete '0000',0, '00001'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_delete]
@CORP_NO VARCHAR(15) --회사번호CORP_NO
, @APRVL_DOC_ID INT --결재문서ID
, @USR_ID VARCHAR(10) --사용자ID
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @BIZM_CNT AS INT
SET @BIZM_CNT = 0
-- 업무관리자 권한이 있는지 체크
SELECT @BIZM_CNT = COUNT('A')
FROM SX_GW0130 A
WHERE 1=1
AND CORP_NO = @CORP_NO
AND USR_ID = @USR_ID
AND MENU_AUTH_CD = 'BIZM' --업무관리자
--만약 0 이면 업무관리자 권한이 없어서 처리 불가
IF @BIZM_CNT = 0
BEGIN
RAISERROR('해당 ID는 업무관리자가 아닙니다. 확인해 주세요.', 16, 1)
RETURN
END
BEGIN TRAN
--근무변경 삭제 (SX_GW0110)
DELETE
FROM SX_GW0110
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APRVL_DOC_ID = @APRVL_DOC_ID
--지각조퇴기록 삭제 (SX_GW0080)
DELETE
FROM SX_GW0080
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APRVL_DOC_ID = @APRVL_DOC_ID
--결재완료 삭제 (SX_GW0100)
DELETE
FROM SX_GW0100
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APRVL_DOC_ID = @APRVL_DOC_ID
--결재신청 삭제 (SX_GW0090)
DELETE
FROM SX_GW0090
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APRVL_DOC_ID = @APRVL_DOC_ID
COMMIT;
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_delete_myDoc] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-02-02
-- Create Description : 스카이브릿지 그룹웨어 결재문서 삭제 (본인이 작성한 문서일 경우에만)
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_approval_delete '1111',0, '00001'
-- exec pr_skybridge_gw_approval_delete '0000',0, '00001'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_delete_myDoc]
@CORP_NO VARCHAR(15) --회사번호CORP_NO
, @APRVL_DOC_ID INT --결재문서ID
, @USR_ID VARCHAR(10) --사용자ID
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @BIZM_CNT AS INT
SET @BIZM_CNT = 0
-- 업무관리자 권한이 있는지 체크
SELECT @BIZM_CNT = COUNT('A')
FROM SX_GW0090 A
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APLNT_ID = @USR_ID
AND APRVL_DOC_ID = @APRVL_DOC_ID
AND APRVL_CMPL_SNO IS NULL
--만약 0 이면 업무관리자 권한이 없어서 처리 불가
IF @BIZM_CNT = 0
BEGIN
RAISERROR('결재가 진행/완료 되었거나, 삭제 권한이 없습니다. 확인해 주세요.', 16, 1)
RETURN
END
BEGIN TRAN
--근무변경 삭제 (SX_GW0110)
DELETE
FROM SX_GW0110
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APRVL_DOC_ID = @APRVL_DOC_ID
--지각조퇴기록 삭제 (SX_GW0080)
DELETE
FROM SX_GW0080
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APRVL_DOC_ID = @APRVL_DOC_ID
--결재완료 삭제 (SX_GW0100)
DELETE
FROM SX_GW0100
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APRVL_DOC_ID = @APRVL_DOC_ID
--결재신청 삭제 (SX_GW0090)
DELETE
FROM SX_GW0090
WHERE 1=1
AND CORP_NO = @CORP_NO
AND APRVL_DOC_ID = @APRVL_DOC_ID
COMMIT;
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_document_group_count] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 내가 올린 결재 문서 개수
-- Update date :
-- Update Description :
--exec pr_skybridge_gw_approval_document_group_count '1111', '00001'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_document_group_count]
@CORP_NO VARCHAR(15) --회사코드
, @USR_ID VARCHAR(20) --사용자
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
1 AS SORT
,'☆내가 올린 결재 문서' AS STUS_NM
,'' AS DOC_CNT
UNION ALL
SELECT
2 AS SORT
, ' - 작성중' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0090
WHERE 1=1
AND APRVL_STUS_CD IS NOT NULL
AND DATEPART(YYYY, RGST_DT) = DATEPART(YYYY, GETDATE())
AND CORP_NO = @CORP_NO
AND APLNT_ID = @USR_ID
AND APRVL_STUS_CD = '0001'
UNION ALL
SELECT
3 AS SORT
, ' - 미결재' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0090
WHERE 1=1
AND APRVL_STUS_CD IS NOT NULL
AND DATEPART(YYYY, RGST_DT) = DATEPART(YYYY, GETDATE())
AND CORP_NO = @CORP_NO
AND APLNT_ID = @USR_ID
AND APRVL_STUS_CD = '0002'
UNION ALL
SELECT
4 AS SORT
, ' - 승인' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0090
WHERE 1=1
AND APRVL_STUS_CD IS NOT NULL
AND DATEPART(YYYY, RGST_DT) = DATEPART(YYYY, GETDATE())
AND CORP_NO = @CORP_NO
AND APLNT_ID = @USR_ID
AND APRVL_STUS_CD = '0003'
AND ISNULL(DOC_FLAG, '') != 'A'
/*
UNION ALL
SELECT
5 AS SORT
, ' - 반려' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0090 A
LEFT OUTER JOIN SX_GW0100 B
ON A.CORP_NO = B.CORP_NO AND A.APRVL_DOC_ID = B.APRVL_DOC_ID AND APRVL_SNO = 1
WHERE 1=1
AND A.APRVL_STUS_CD IS NOT NULL
AND DATEPART(YYYY, A.RGST_DT) = DATEPART(YYYY, GETDATE())
AND A.CORP_NO = @CORP_NO
AND A.APLNT_ID = @USR_ID
AND A.APRVL_STUS_CD = '0004'
AND ISNULL(B.DOC_FLAG, '') = 'A'
AND ISNULL(A.DOC_FLAG, '') != 'A'
*/
UNION ALL
SELECT
5 AS SORT
, ' - 반려' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0090 A
LEFT OUTER JOIN SX_GW0100 B
ON A.CORP_NO = B.CORP_NO AND A.APRVL_DOC_ID = B.APRVL_DOC_ID AND APRVL_SNO = 1
WHERE 1=1
AND A.APRVL_STUS_CD IS NOT NULL
AND DATEPART(YYYY, A.RGST_DT) = DATEPART(YYYY, GETDATE())
AND A.CORP_NO = @CORP_NO
AND A.APLNT_ID = @USR_ID
AND A.APRVL_STUS_CD = '0004'
AND (B.APPR_STUS_CD = '0004' OR (B.APPR_STUS_CD != '0004' AND ISNULL(B.DOC_FLAG, '') = 'A'))
AND ISNULL(A.DOC_FLAG, '') != 'A'
/*
AND B.APPR_STUS_CD = '0004'
AND ISNULL(A.DOC_FLAG, '') != 'A'
*/
UNION ALL
SELECT
6 AS SORT
,'☆내가 받은 결재 문서' AS STUS_NM
,'' AS DOC_CNT
UNION ALL
SELECT
7 AS SORT
, ' - 미결재' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0100 A
LEFT OUTER JOIN SX_GW0090 B
ON A.CORP_NO = B.CORP_NO AND A.APRVL_DOC_ID = B.APRVL_DOC_ID
WHERE 1=1
AND B.APRVL_DOC_ID IS NOT NULL
AND DATEPART(YYYY, A.RGST_DT) = DATEPART(YYYY, GETDATE())
AND A.CORP_NO = @CORP_NO
AND A.APPR_ID = @USR_ID
AND ISNULL(A.APPR_STUS_CD, '') = '0002'
UNION ALL
SELECT
8 AS SORT
, ' - 미확인' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0100 A
LEFT OUTER JOIN SX_GW0090 B
ON A.CORP_NO = B.CORP_NO AND A.APRVL_DOC_ID = B.APRVL_DOC_ID
WHERE 1=1
AND B.APRVL_DOC_ID IS NOT NULL
AND DATEPART(YYYY, A.RGST_DT) = DATEPART(YYYY, GETDATE())
AND A.CORP_NO = @CORP_NO
AND A.APPR_ID = @USR_ID
AND A.APRVL_SNO = 1
AND ISNULL(A.APPR_STUS_CD, '') = '0003'
AND ISNULL(B.APRVL_STUS_CD, '') = '0004'
AND ISNULL(A.DOC_FLAG, '') != 'A'
UNION ALL
SELECT
9 AS SORT
, ' - 승인' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0100 A
LEFT OUTER JOIN SX_GW0090 B
ON A.CORP_NO = B.CORP_NO AND A.APRVL_DOC_ID = B.APRVL_DOC_ID
WHERE 1=1
AND B.APRVL_DOC_ID IS NOT NULL
AND DATEPART(YYYY, A.RGST_DT) = DATEPART(YYYY, GETDATE())
AND A.CORP_NO = @CORP_NO
AND A.APPR_ID = @USR_ID
AND A.APRVL_SNO = 1
AND ISNULL(A.APPR_STUS_CD, '') = '0003'
AND ISNULL(B.APRVL_STUS_CD, '') = '0002'
UNION ALL
SELECT
10 AS SORT
, ' - 반려' AS STUS_NM
, '('+ CONVERT(VARCHAR, COUNT('A')) +')' AS DOC_CNT
FROM SX_GW0100 A
LEFT OUTER JOIN SX_GW0090 B
ON A.CORP_NO = B.CORP_NO AND A.APRVL_DOC_ID = B.APRVL_DOC_ID
LEFT OUTER JOIN SX_GW0100 C
ON A.CORP_NO = C.CORP_NO AND A.APRVL_DOC_ID = C.APRVL_DOC_ID AND A.APRVL_SNO != C.APRVL_SNO
WHERE 1=1
AND B.APRVL_DOC_ID IS NOT NULL
AND DATEPART(YYYY, A.RGST_DT) = DATEPART(YYYY, GETDATE())
AND A.CORP_NO = @CORP_NO
AND A.APPR_ID = @USR_ID
AND ISNULL(B.APRVL_STUS_CD, '') = '0004'
AND(
(A.APRVL_SNO = 1 AND ISNULL(B.DOC_FLAG, '') != 'A' AND A.DOC_FLAG = 'A')
OR
(A.APRVL_SNO = 2 AND ISNULL(C.DOC_FLAG, '') != 'A' AND ISNULL(A.APPR_STUS_CD, '') = '0004')
)
ORDER BY SORT ASC
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_document_list_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 결재문서 결재 리스트 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_approval_document_list_select '1111','00001','','','','','','',1,20000,20000, '20070101', '20171231', '' --1페이지
-- exec pr_skybridge_gw_approval_document_list_select '1111','00001','','','','','','',2,20,20, '20070101', '20171231', '' --2페이지
-- exec pr_skybridge_gw_approval_document_list_select '1111','10212','','','','','','',1,20000,20000,'20070101', '20171231', '' --1페이지
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_document_list_select]
@P0 VARCHAR(15) --CORP_NO
, @P1 VARCHAR(10) --APPR_ID
, @P2 VARCHAR(20) --
, @P3 VARCHAR(4) --APPR_STUS_CD
, @P4 VARCHAR(20) --
, @P5 VARCHAR(4) --APRVL_STUS_CD
, @P6 VARCHAR(20) --
, @P7 VARCHAR(4) --APRVL_KIND_CD
, @P8 INT -- 조회 페이지 번호
, @P9 INT -- 한행에 조회되는 행수
, @P10 INT -- 한행에 조회되는 행수
, @P11 VARCHAR(20) -- 검색 시작일
, @P12 VARCHAR(20) -- 검색 종료일
, @P13 VARCHAR(4) -- 진행상태
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 신규 작성
SELECT
A.APRVL_SNO,
CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
A.APPR_STUS_CD,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
SUBSTRING(CONVERT(VARCHAR, A.OFFER_DT, 120),1,10) AS OFFER_DT,
CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
ISNULL(A.APRVL_CMPL_SNO,0) AS APRVL_CMPL_SNO,
ISNULL(A.FINAL_APRVL_SNO,0) AS FINAL_APRVL_SNO,
-- ■ 스카이브릿지 결재문서 결재 상세 내역 정리
--- 1.시간외 : 신청내용, 업무내용, OT일자
--- 2.지각 : 신청내용, 업무내용, 정상근무인정 요청, 지각일자(X), 지각일시
--- 3.조퇴 : 신청내용, 업무내용, 정상근무인정 요청, 조퇴일자(X), 조퇴일시
--- 4.출퇴근미기록 : 신청내용, 업무내용, 정상근무인정 요청, 결근일자
--- 5.연차등 : 신청내용, 업무내용, 대행자, 연차 (날자, 연차등)
--- 6.근무변경 : 신청내용, 업무내용, 대행자, 근무변경(근무일, 변경전, 변경후)
--- 7.기안 : 신청내용, 업무내용
A.APLNT_CN , --신청자 내용
A.BZ_CN, --업무내용
--근무코드 : 변경후 근무코드 조회 없으면 변경전 근무코드 조회
CASE WHEN APRVL_KIND_CD = '0001' THEN ( SELECT CASE WHEN ISNULL(WORK_CD, '') = '' THEN PLAN_WORK_CD ELSE WORK_CD END AS WORK_CD
FROM SX_GW0050
WHERE CORP_NO = @P0
AND USR_ID = APLNT_ID
AND WORK_PLAN_YYMMDD = REPLACE(LATE_SKIPOFF_ABTI_OT_DATE, '-', '')
)
WHEN APRVL_KIND_CD = '0002' THEN ( SELECT CASE WHEN ISNULL(WORK_CD, '') = '' THEN PLAN_WORK_CD ELSE WORK_CD END AS WORK_CD
FROM SX_GW0050
WHERE CORP_NO = @P0
AND USR_ID = APLNT_ID
AND WORK_PLAN_YYMMDD = REPLACE(SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120),1,10), '-', '')
)
WHEN APRVL_KIND_CD = '0003' THEN ( SELECT CASE WHEN ISNULL(WORK_CD, '') = '' THEN PLAN_WORK_CD ELSE WORK_CD END AS WORK_CD
FROM SX_GW0050
WHERE CORP_NO = @P0
AND USR_ID = APLNT_ID
AND WORK_PLAN_YYMMDD = REPLACE(SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120),1,10), '-', '')
)
WHEN APRVL_KIND_CD = '0004' THEN ( SELECT CASE WHEN ISNULL(WORK_CD, '') = '' THEN PLAN_WORK_CD ELSE WORK_CD END AS WORK_CD
FROM SX_GW0050
WHERE CORP_NO = @P0
AND USR_ID = APLNT_ID
AND WORK_PLAN_YYMMDD = REPLACE(SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_ABTI_OT_DATE, 120),1,10), '-', '')
)
WHEN APRVL_KIND_CD = '0005' THEN ( SELECT CASE WHEN ISNULL(WORK_CD, '') = '' THEN PLAN_WORK_CD ELSE WORK_CD END AS WORK_CD
FROM SX_GW0050
WHERE CORP_NO = @P0
AND USR_ID = APLNT_ID
AND WORK_PLAN_YYMMDD = REPLACE(SUBSTRING(A.UPD_DATE_YC,1,10), '-', '')
)
WHEN APRVL_KIND_CD = '0006' THEN UPD_BF_WORK_CD
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS WORK_CD ,
--상세1
CASE WHEN APRVL_KIND_CD = '0001' THEN 'OT : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0002' THEN '실제 : ' + SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120),1,16)
WHEN APRVL_KIND_CD = '0003' THEN CASE WHEN A.NML_WORK_RCNTN_YN = 'Y'THEN '조기퇴근' ELSE '' END -- '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0004' THEN '출퇴근미기록 : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0005' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0006' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_01 , --상세내역 01
--상세2
CASE WHEN APRVL_KIND_CD = '0001' THEN ( SELECT CASE WHEN ISNULL(WORK_START_DT, '') = ''
THEN SUBSTRING(CONVERT(VARCHAR, REAL_START_DT, 120),12,5)
ELSE SUBSTRING(CONVERT(VARCHAR, WORK_START_DT, 120),12,5) END
+ ' ~ ' +
CASE WHEN ISNULL(WORK_END_DT, '') = ''
THEN SUBSTRING(CONVERT(VARCHAR, REAL_END_DT, 120),12,5)
ELSE SUBSTRING(CONVERT(VARCHAR, WORK_END_DT, 120),12,5) END
FROM SX_GW0050
WHERE CORP_NO = @P0
AND USR_ID = APLNT_ID
AND WORK_PLAN_YYMMDD = REPLACE(LATE_SKIPOFF_ABTI_OT_DATE, '-', '')
)
WHEN APRVL_KIND_CD = '0002' THEN '승인 : ' + SUBSTRING(CONVERT(VARCHAR, A.REQUEST_DT, 120),1,16)
WHEN APRVL_KIND_CD = '0003' THEN '조퇴 : ' + SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120),1,16)
WHEN APRVL_KIND_CD = '0004' THEN SUBSTRING(CONVERT(VARCHAR, REQUEST_DT, 120),12,5) + ' ~ ' + SUBSTRING(CONVERT(VARCHAR, REQUEST_DT2, 120),12,5)
WHEN APRVL_KIND_CD = '0005' THEN '연차 : ' + UPD_DATE_YC + ' ' + FREE_CNT
WHEN APRVL_KIND_CD = '0006' THEN '변경 : ' + UPD_DATE_CHANGE + '(' + UPD_BF_WORK_CD + '-->' + UPD_WORK_CD + ')'
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_02 , -- 상세내역02
--상세3
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN ''
WHEN APRVL_KIND_CD = '0003' THEN ''
WHEN APRVL_KIND_CD = '0004' THEN ''
WHEN APRVL_KIND_CD = '0005' THEN ''
WHEN APRVL_KIND_CD = '0006' THEN ''
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_03 , --상세내역 03
--A.OFFER_CN, --신청내용
--A.UPD_DT, --변경일시
A.APLNT_ID,
A.APLNT_NM,
A.LATE_SKIPOFF_ABTI_OT_DATE
FROM (
SELECT A.CORP_NO,
A.APRVL_SNO,
A.APRVL_DT,
A.APPR_STUS_CD,
B.APRVL_DOC_ID,
B.APRVL_STUS_CD,
B.APRVL_KIND_CD,
B.OFFER_DT,
B.CMPL_DT,
B.APRVL_CMPL_SNO,
B.FINAL_APRVL_SNO,
B.APLNT_ID,
U1.USR_NM AS APLNT_NM,
U2.LATE_SKIPOFF_ABTI_OT_DATE,
U2.NML_WORK_RCNTN_YN,
U2.LATE_SKIPOFF_OT_DT,
U2.REQUEST_DT, --승인출근요청일시
U2.REQUEST_DT2, --승인퇴근요청일시
U3.UPD_DATE_YC,
U3.FREE_CNT,
U4.USR_NM AS BZ_DEPUTY_NM,
U3.UPD_DATE_CHANGE,
U3.UPD_BF_WORK_CD,
U3.UPD_WORK_CD,
B.APLNT_CN ,
B.BZ_CN,
B.OFFER_CN,
B.UPD_DT
--FROM SX_GW0090 B
-- LEFT OUTER JOIN
-- ( SELECT * FROM SX_GW0100
-- WHERE 1=1
-- AND CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + CONVERT(VARCHAR, APRVL_SNO) IN
-- (SELECT CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + MAX(CONVERT(VARCHAR, APRVL_SNO))
-- FROM SX_GW0100
-- WHERE CORP_NO = @P0
-- GROUP BY CORP_NO, APRVL_DOC_ID
--)
FROM SX_GW0100 A LEFT JOIN
SX_GW0090 B
ON (1=1
AND B.CORP_NO = A.CORP_NO
AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = B.CORP_NO
AND U1.USR_ID = B.APLNT_ID
)
LEFT JOIN SX_GW0080 U2 --SX_지각조퇴기록
ON(1=1
AND U2.CORP_NO = A.CORP_NO
AND U2.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN (SELECT TA.CORP_NO
, APRVL_DOC_ID
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
, CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)) + '일' AS FREE_CNT --연차 일수
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23)) AS UPD_DATE_CHANGE --근무변경
, MIN(UPD_BF_WORK_CD) AS UPD_BF_WORK_CD --변경전 근무 코드
, MIN(UPD_WORK_CD) AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110 TA LEFT JOIN SX_CO0070 TB ON TB.CORP_NO = TA.CORP_NO AND TB.WORK_CD = TA.UPD_WORK_CD
GROUP BY TA.CORP_NO, TA.APRVL_DOC_ID
) U3
ON(1=1
AND U3.CORP_NO = A.CORP_NO
AND U3.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = B.BZ_DEPUTY_ID )
WHERE 1=1
AND B.APRVL_DOC_ID IS NOT NULL
AND A.CORP_NO = @P0
AND (A.APPR_ID = @P1)
AND (NULLIF(@P2, '') IS NULL OR A.APPR_STUS_CD = @P3)
AND (NULLIF(@P4, '') IS NULL OR B.APRVL_STUS_CD = @P5)
AND (NULLIF(@P6, '') IS NULL OR B.APRVL_KIND_CD = @P7)
AND (CONVERT(VARCHAR, B.UPD_DT, 112) BETWEEN REPLACE(@P11, '-', '') AND REPLACE(@P12, '-', ''))
/* 추가 조건 시작 */
AND (
(NULLIF(@P13, '') IS NULL)
OR (NULLIF(@P13, '0001') IS NULL AND ISNULL(A.APPR_STUS_CD, '') = '0002') -- 미결재
OR (NULLIF(@P13, '0002') IS NULL AND (A.APRVL_SNO = '1' AND B.APRVL_STUS_CD = '0004' AND A.APPR_STUS_CD = '0003' AND ISNULL(A.DOC_FLAG, '') != 'A')) -- 미확인
OR (NULLIF(@P13, '0003') IS NULL AND (A.APRVL_SNO = '1' AND B.APRVL_STUS_CD = '0002' AND A.APPR_STUS_CD = '0003')) -- 승인
OR (NULLIF(@P13, '0004') IS NULL AND B.APRVL_STUS_CD = '0004' AND (
(A.APPR_ID = @P1 AND A.APRVL_SNO = '2' AND ISNULL(A.APPR_STUS_CD, '') = '0004' AND ISNULL((SELECT DOC_FLAG FROM SX_GW0100 WHERE CORP_NO = A.CORP_NO AND APRVL_SNO = '1' AND APRVL_DOC_ID = A.APRVL_DOC_ID), '') != 'A' )
OR
(A.APPR_ID = @P1 AND A.APRVL_SNO = '1' AND ISNULL(A.DOC_FLAG, '') != 'A')
)
) --반려
)
/* 추가 조건 끝 */
ORDER BY
B.OFFER_DT DESC,
APRVL_SNO DESC
OFFSET ((@P8 - 1) * @P9) ROWS
FETCH NEXT @P10 ROWS ONLY
) A
--기존에 사용한 문장 BACKUP
--SELECT
-- A.APRVL_SNO,
-- CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
-- A.APPR_STUS_CD,
-- A.APRVL_DOC_ID,
-- A.APRVL_STUS_CD,
-- A.APRVL_KIND_CD,
-- CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
-- CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
-- A.APRVL_CMPL_SNO,
-- A.FINAL_APRVL_SNO,
-- A.APLNT_ID,
-- A.APLNT_NM
-- FROM (
--SELECT
-- A.APRVL_SNO,
-- A.APRVL_DT,
-- A.APPR_STUS_CD,
-- B.APRVL_DOC_ID,
-- B.APRVL_STUS_CD,
-- B.APRVL_KIND_CD,
-- B.OFFER_DT,
-- B.CMPL_DT,
-- B.APRVL_CMPL_SNO,
-- B.FINAL_APRVL_SNO,
-- B.APLNT_ID,
-- U1.USR_NM AS APLNT_NM
-- FROM SX_GW0100 A
-- JOIN SX_GW0090 B
-- ON(1=1
-- AND B.CORP_NO = A.CORP_NO
-- AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
-- )
-- LEFT JOIN SX_GW0010 U1
-- ON(1=1
-- AND U1.CORP_NO = B.CORP_NO
-- AND U1.USR_ID = B.APLNT_ID
-- )
-- WHERE 1=1
-- AND A.CORP_NO = @P0
-- AND A.APPR_ID = @P1
-- AND (NULLIF(@P2, '''') IS NULL OR A.APPR_STUS_CD = @P3)
-- AND (NULLIF(@P4, '''') IS NULL OR B.APRVL_STUS_CD = @P5)
-- AND (NULLIF(@P6, '''') IS NULL OR B.APRVL_KIND_CD = @P7)
-- ORDER BY
-- B.OFFER_DT DESC,
-- APRVL_SNO DESC
--OFFSET ((@P8 - 1) * @P9) ROWS
--FETCH NEXT @P10 ROWS ONLY
-- ) A
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_document_list_select_20200109_bak] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 결재문서 결재 리스트 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_approval_document_list_select '1111','00001','','','','','','',1,20000,20000 --1페이지
-- exec pr_skybridge_gw_approval_document_list_select '1111','00001','','','','','','',2,20,20 --2페이지
-- exec pr_skybridge_gw_approval_document_list_select '1111','10212','','','','','','',1,20000,20000 --1페이지
-- EXEC pr_skybridge_gw_approval_document_list_select '1111', '10305', '', '', '', '', '', '', 1, 10000, 10000 --김은중
-- =======================================================================================
create PROCEDURE [dbo].[pr_skybridge_gw_approval_document_list_select_20200109_bak]
@P0 VARCHAR(15) --CORP_NO
, @P1 VARCHAR(10) --APPR_ID
, @P2 VARCHAR(20) --
, @P3 VARCHAR(4) --APPR_STUS_CD
, @P4 VARCHAR(20) --
, @P5 VARCHAR(4) --APRVL_STUS_CD
, @P6 VARCHAR(20) --
, @P7 VARCHAR(4) --APRVL_KIND_CD
, @P8 INT -- 조회 페이지 번호
, @P9 INT -- 한행에 조회되는 행수
, @P10 INT -- 한행에 조회되는 행수
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 신규 작성
SELECT
A.APRVL_SNO,
CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
A.APPR_STUS_CD,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
ISNULL(A.APRVL_CMPL_SNO,0) AS APRVL_CMPL_SNO,
ISNULL(A.FINAL_APRVL_SNO,0) AS FINAL_APRVL_SNO,
-- ■ 스카이브릿지 결재문서 결재 상세 내역 정리
--- 1.시간외 : 신청내용, 업무내용, OT일자
--- 2.지각 : 신청내용, 업무내용, 정상근무인정 요청, 지각일자(X), 지각일시
--- 3.조퇴 : 신청내용, 업무내용, 정상근무인정 요청, 조퇴일자(X), 조퇴일시
--- 4.결근 : 신청내용, 업무내용, 정상근무인정 요청, 결근일자
--- 5.연차등 : 신청내용, 업무내용, 대행자, 연차 (날자, 연차등)
--- 6.근무변경 : 신청내용, 업무내용, 대행자, 근무변경(근무일, 변경전, 변경후)
--- 7.기안 : 신청내용, 업무내용
A.APLNT_CN , --신청자 내용
A.BZ_CN, --업무내용
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0003' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0004' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0005' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0006' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0007' THEN ''
WHEN APRVL_KIND_CD = '0008' THEN ''
ELSE ''
END AS DETAIL_01 , --상세내역 01
CASE WHEN APRVL_KIND_CD = '0001' THEN 'OT : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0002' THEN '지각 : ' + (CASE WHEN ISNULL(A.REQUEST_DT,'') = '' THEN SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120), 1, 16) ELSE SUBSTRING(CONVERT(VARCHAR, A.REQUEST_DT, 120), 1, 16) END)
WHEN APRVL_KIND_CD = '0003' THEN '조퇴 : ' + (CASE WHEN ISNULL(A.REQUEST_DT2,'') = '' THEN SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120), 1, 16) ELSE SUBSTRING(CONVERT(VARCHAR, A.REQUEST_DT2, 120), 1, 16) END)
WHEN APRVL_KIND_CD = '0004' THEN '결근 : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23) + ' ' + ISNULL(SUBSTRING(CONVERT(VARCHAR, REQUEST_DT, 108), 1, 5), '') + '~' + ISNULL(SUBSTRING(CONVERT(VARCHAR, REQUEST_DT2, 108), 1, 5), '')
WHEN APRVL_KIND_CD = '0005' THEN '연차 : ' + UPD_DATE_YC + ' ' + FREE_CNT
WHEN APRVL_KIND_CD = '0006' THEN '변경 : ' + UPD_DATE_CHANGE + '(' + UPD_BF_WORK_CD + '-->' + UPD_WORK_CD + ')'
WHEN APRVL_KIND_CD = '0007' THEN ''
WHEN APRVL_KIND_CD = '0008' THEN '외출 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 23) + ' (' + SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 108),1,5) + '~' + SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT2, 108),1,5) + ')'
ELSE ''
END AS DETAIL_02 , -- 상세내역02
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN ''
WHEN APRVL_KIND_CD = '0003' THEN ''
WHEN APRVL_KIND_CD = '0004' THEN ''
WHEN APRVL_KIND_CD = '0005' THEN ''
WHEN APRVL_KIND_CD = '0006' THEN ''
WHEN APRVL_KIND_CD = '0007' THEN ''
WHEN APRVL_KIND_CD = '0008' THEN ''
ELSE ''
END AS DETAIL_03 , --상세내역 03
--A.OFFER_CN, --신청내용
--A.UPD_DT, --변경일시
A.APLNT_ID,
A.APLNT_NM
FROM (
SELECT A.CORP_NO,
A.APRVL_SNO,
A.APRVL_DT,
A.APPR_STUS_CD,
B.APRVL_DOC_ID,
B.APRVL_STUS_CD,
B.APRVL_KIND_CD,
B.OFFER_DT,
B.CMPL_DT,
B.APRVL_CMPL_SNO,
B.FINAL_APRVL_SNO,
B.APLNT_ID,
U1.USR_NM AS APLNT_NM,
U2.LATE_SKIPOFF_ABTI_OT_DATE,
U2.NML_WORK_RCNTN_YN,
U2.LATE_SKIPOFF_OT_DT,
U2.LATE_SKIPOFF_OT_DT2,
U2.REQUEST_DT, --승인출근시각
U2.REQUEST_DT2, --승인퇴근시각
U3.UPD_DATE_YC,
U3.FREE_CNT,
U4.USR_NM AS BZ_DEPUTY_NM,
U3.UPD_DATE_CHANGE,
U3.UPD_BF_WORK_CD,
U3.UPD_WORK_CD,
B.APLNT_CN ,
B.BZ_CN,
B.OFFER_CN,
B.UPD_DT
--FROM SX_GW0090 B
-- LEFT OUTER JOIN
-- ( SELECT * FROM SX_GW0100
-- WHERE 1=1
-- AND CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + CONVERT(VARCHAR, APRVL_SNO) IN
-- (SELECT CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + MAX(CONVERT(VARCHAR, APRVL_SNO))
-- FROM SX_GW0100
-- WHERE CORP_NO = @P0
-- GROUP BY CORP_NO, APRVL_DOC_ID
--)
FROM SX_GW0100 A LEFT JOIN
SX_GW0090 B
ON (1=1
AND B.CORP_NO = A.CORP_NO
AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = B.CORP_NO
AND U1.USR_ID = B.APLNT_ID
)
LEFT JOIN SX_GW0080 U2 --SX_지각조퇴기록
ON(1=1
AND U2.CORP_NO = A.CORP_NO
AND U2.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN (SELECT TA.CORP_NO
, APRVL_DOC_ID
-- , MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
--, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + TC.PLAN_WORK_CD + '-->' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) +
--CASE WHEN ISNULL(TC.PLAN_WORK_CD, '') = '' THEN '( --> )'
CASE WHEN ISNULL(TC.PLAN_WORK_CD, '') = '' THEN '( -->' + UPD_WORK_CD + ')'
ELSE '(' + TC.PLAN_WORK_CD + '-->' + UPD_WORK_CD + ')'
END ) AS UPD_DATE_YC --연차
--, CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)) + '일' AS FREE_CNT --연차 일수
, SUBSTRING(ISNULL(CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)), '0.00'),1,3) + '일' AS FREE_CNT --연차 일수
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23)) AS UPD_DATE_CHANGE --근무변경
, MIN(UPD_BF_WORK_CD) AS UPD_BF_WORK_CD --변경전 근무 코드
, MIN(UPD_WORK_CD) AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110 TA LEFT JOIN SX_CO0070 TB ON TB.CORP_NO = TA.CORP_NO AND TB.WORK_CD = TA.UPD_WORK_CD
LEFT JOIN SX_GW0050 TC ON TC.CORP_NO = TA.CORP_NO AND TC.USR_ID = TA.RGSTR_ID AND TC.WORK_PLAN_YYMMDD = TA.UPD_DATE
GROUP BY TA.CORP_NO, TA.APRVL_DOC_ID
) U3
ON(1=1
AND U3.CORP_NO = A.CORP_NO
AND U3.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = B.BZ_DEPUTY_ID )
WHERE 1=1
AND A.CORP_NO = @P0
AND (A.APPR_ID = @P1 OR A.RGSTR_ID = @P1)
AND (NULLIF(@P2, '') IS NULL OR A.APPR_STUS_CD = @P3)
AND (NULLIF(@P4, '') IS NULL OR B.APRVL_STUS_CD = @P5)
AND (NULLIF(@P6, '') IS NULL OR B.APRVL_KIND_CD = @P7)
ORDER BY
B.OFFER_DT DESC,
APRVL_SNO DESC
OFFSET ((@P8 - 1) * @P9) ROWS
FETCH NEXT @P10 ROWS ONLY
) A
--기존에 사용한 문장 BACKUP
--SELECT
-- A.APRVL_SNO,
-- CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
-- A.APPR_STUS_CD,
-- A.APRVL_DOC_ID,
-- A.APRVL_STUS_CD,
-- A.APRVL_KIND_CD,
-- CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
-- CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
-- A.APRVL_CMPL_SNO,
-- A.FINAL_APRVL_SNO,
-- A.APLNT_ID,
-- A.APLNT_NM
-- FROM (
--SELECT
-- A.APRVL_SNO,
-- A.APRVL_DT,
-- A.APPR_STUS_CD,
-- B.APRVL_DOC_ID,
-- B.APRVL_STUS_CD,
-- B.APRVL_KIND_CD,
-- B.OFFER_DT,
-- B.CMPL_DT,
-- B.APRVL_CMPL_SNO,
-- B.FINAL_APRVL_SNO,
-- B.APLNT_ID,
-- U1.USR_NM AS APLNT_NM
-- FROM SX_GW0100 A
-- JOIN SX_GW0090 B
-- ON(1=1
-- AND B.CORP_NO = A.CORP_NO
-- AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
-- )
-- LEFT JOIN SX_GW0010 U1
-- ON(1=1
-- AND U1.CORP_NO = B.CORP_NO
-- AND U1.USR_ID = B.APLNT_ID
-- )
-- WHERE 1=1
-- AND A.CORP_NO = @P0
-- AND A.APPR_ID = @P1
-- AND (NULLIF(@P2, '''') IS NULL OR A.APPR_STUS_CD = @P3)
-- AND (NULLIF(@P4, '''') IS NULL OR B.APRVL_STUS_CD = @P5)
-- AND (NULLIF(@P6, '''') IS NULL OR B.APRVL_KIND_CD = @P7)
-- ORDER BY
-- B.OFFER_DT DESC,
-- APRVL_SNO DESC
--OFFSET ((@P8 - 1) * @P9) ROWS
--FETCH NEXT @P10 ROWS ONLY
-- ) A
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_document_list_select_count] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 결재문서 결재 리스트 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_approval_document_list_select '1111','00001','','','','','','',1,20000,20000, '20070101', '20171231', '' --1페이지
-- exec pr_skybridge_gw_approval_document_list_select '1111','00001','','','','','','',2,20,20, '20070101', '20171231', '' --2페이지
-- exec pr_skybridge_gw_approval_document_list_select '1111','10212','','','','','','',1,20000,20000,'20070101', '20171231', '' --1페이지
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_document_list_select_count]
@P0 VARCHAR(15) --CORP_NO
, @P1 VARCHAR(10) --APPR_ID
, @P2 VARCHAR(20) --
, @P3 VARCHAR(4) --APPR_STUS_CD
, @P4 VARCHAR(20) --
, @P5 VARCHAR(4) --APRVL_STUS_CD
, @P6 VARCHAR(20) --
, @P7 VARCHAR(4) --APRVL_KIND_CD
, @P8 INT -- 조회 페이지 번호
, @P9 INT -- 한행에 조회되는 행수
, @P10 INT -- 한행에 조회되는 행수
, @P11 VARCHAR(20) -- 검색 시작일
, @P12 VARCHAR(20) -- 검색 종료일
, @P13 VARCHAR(4) -- 진행상태
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 신규 작성
SELECT
COUNT('A') AS TOT_CNT
FROM (
SELECT A.CORP_NO,
A.APRVL_SNO,
A.APRVL_DT,
A.APPR_STUS_CD,
B.APRVL_DOC_ID,
B.APRVL_STUS_CD,
B.APRVL_KIND_CD,
B.OFFER_DT,
B.CMPL_DT,
B.APRVL_CMPL_SNO,
B.FINAL_APRVL_SNO,
B.APLNT_ID,
U1.USR_NM AS APLNT_NM,
U2.LATE_SKIPOFF_ABTI_OT_DATE,
U2.NML_WORK_RCNTN_YN,
U2.LATE_SKIPOFF_OT_DT,
U2.REQUEST_DT, --승인출근요청일시
U2.REQUEST_DT2, --승인퇴근요청일시
U3.UPD_DATE_YC,
U3.FREE_CNT,
U4.USR_NM AS BZ_DEPUTY_NM,
U3.UPD_DATE_CHANGE,
U3.UPD_BF_WORK_CD,
U3.UPD_WORK_CD,
B.APLNT_CN ,
B.BZ_CN,
B.OFFER_CN,
B.UPD_DT
FROM SX_GW0100 A LEFT JOIN
SX_GW0090 B
ON (1=1
AND B.CORP_NO = A.CORP_NO
AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = B.CORP_NO
AND U1.USR_ID = B.APLNT_ID
)
LEFT JOIN SX_GW0080 U2 --SX_지각조퇴기록
ON(1=1
AND U2.CORP_NO = A.CORP_NO
AND U2.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN (SELECT TA.CORP_NO
, APRVL_DOC_ID
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
, CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)) + '일' AS FREE_CNT --연차 일수
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23)) AS UPD_DATE_CHANGE --근무변경
, MIN(UPD_BF_WORK_CD) AS UPD_BF_WORK_CD --변경전 근무 코드
, MIN(UPD_WORK_CD) AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110 TA LEFT JOIN SX_CO0070 TB ON TB.CORP_NO = TA.CORP_NO AND TB.WORK_CD = TA.UPD_WORK_CD
GROUP BY TA.CORP_NO, TA.APRVL_DOC_ID
) U3
ON(1=1
AND U3.CORP_NO = A.CORP_NO
AND U3.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = B.BZ_DEPUTY_ID )
WHERE 1=1
AND B.APRVL_DOC_ID IS NOT NULL
AND A.CORP_NO = @P0
AND A.APPR_ID = @P1
AND (NULLIF(@P2, '') IS NULL OR A.APPR_STUS_CD = @P3)
AND (NULLIF(@P4, '') IS NULL OR B.APRVL_STUS_CD = @P5)
AND (NULLIF(@P6, '') IS NULL OR B.APRVL_KIND_CD = @P7)
AND (CONVERT(VARCHAR, B.UPD_DT, 112) BETWEEN @P11 AND @P12)
/* 추가 조건 시작 */
AND (
(NULLIF(@P13, '') IS NULL)
OR (NULLIF(@P13, '0001') IS NULL AND ISNULL(A.APPR_STUS_CD, '') = '0002') -- 미결재
OR (NULLIF(@P13, '0002') IS NULL AND (A.APRVL_SNO = '1' AND B.APRVL_STUS_CD = '0004' AND A.APPR_STUS_CD = '0003' AND ISNULL(A.DOC_FLAG, '') != 'A')) -- 미확인
OR (NULLIF(@P13, '0003') IS NULL AND (A.APRVL_SNO = '1' AND B.APRVL_STUS_CD = '0002' AND A.APPR_STUS_CD = '0003')) -- 승인
OR (NULLIF(@P13, '0004') IS NULL AND B.APRVL_STUS_CD = '0004' AND (
(A.APRVL_SNO = '2' AND ISNULL(A.APPR_STUS_CD, '') = '0004' AND ISNULL((SELECT DOC_FLAG FROM SX_GW0100 WHERE CORP_NO = A.CORP_NO AND APRVL_SNO = '1' AND APRVL_DOC_ID = A.APRVL_DOC_ID), '') != 'A' )
OR
(A.APRVL_SNO = '1' AND A.DOC_FLAG = 'A' AND ISNULL(B.DOC_FLAG, '') = '')
)
) --반려
)
/* 추가 조건 끝 */
) A
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_manager_list_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 결재문서 결재 리스트 조회(관리자용)
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_approval_manager_list_select '','','','','','',NULL,'1111','0003','0003','','','20161215','20170215',1,20000,20000
-- exec pr_skybridge_gw_approval_manager_list_select '','','','','','',NULL,'1111','','','','','20161215','20170215',1,20000,20000
-- exec pr_skybridge_gw_approval_manager_list_select '10429','10429','','','','',NULL,'1111','0003','0003','','','20161217','20170217',1,2000,2000
-- exec pr_skybridge_gw_approval_manager_list_select '지희','지희','','','','',NULL,'1111','0003','0003','','','20161217','20170217',1,2000,2000
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_manager_list_select]
@P0 nvarchar(4000)
, @P1 nvarchar(4000)
, @P2 nvarchar(4000)
, @P3 nvarchar(4000)
, @P4 nvarchar(4000)
, @P5 nvarchar(4000)
, @P6 varchar(8000)
, @P7 nvarchar(4000)
, @P8 nvarchar(4000)
, @P9 nvarchar(4000)
, @P10 nvarchar(4000)
, @P11 nvarchar(4000)
, @P12 nvarchar(4000)
, @P13 nvarchar(4000)
, @P14 int
, @P15 int
, @P16 int
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
---- 신규 작성
SELECT
A.APRVL_SNO,
CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
A.APPR_STUS_CD,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
ISNULL(A.APRVL_CMPL_SNO,0) AS APRVL_CMPL_SNO,
ISNULL(A.FINAL_APRVL_SNO,0) AS FINAL_APRVL_SNO,
-- ■ 스카이브릿지 결재문서 결재 상세 내역 정리
--- 1.시간외 : 신청내용, 업무내용, OT일자
--- 2.지각 : 신청내용, 업무내용, 정상근무인정 요청, 지각일자(X), 지각일시
--- 3.조퇴 : 신청내용, 업무내용, 정상근무인정 요청, 조퇴일자(X), 조퇴일시
--- 4.결근 : 신청내용, 업무내용, 정상근무인정 요청, 결근일자
--- 5.연차등 : 신청내용, 업무내용, 대행자, 연차 (날자, 연차등)
--- 6.근무변경 : 신청내용, 업무내용, 대행자, 근무변경(근무일, 변경전, 변경후)
--- 7.기안 : 신청내용, 업무내용
A.APLNT_CN , --신청자 내용
A.BZ_CN, --업무내용
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0003' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0004' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0005' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0006' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_01 , --상세내역 01
CASE WHEN APRVL_KIND_CD = '0001' THEN 'OT : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
--WHEN APRVL_KIND_CD = '0002' THEN '지각 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
--WHEN APRVL_KIND_CD = '0003' THEN '조퇴 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
--WHEN APRVL_KIND_CD = '0004' THEN '결근 : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0002' THEN '지각 : ' + (CASE WHEN ISNULL(A.REQUEST_DT,'') = '' THEN SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120), 1, 16) ELSE SUBSTRING(CONVERT(VARCHAR, A.REQUEST_DT, 120), 1, 16) END)
WHEN APRVL_KIND_CD = '0003' THEN '조퇴 : ' + (CASE WHEN ISNULL(A.REQUEST_DT2,'') = '' THEN SUBSTRING(CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120), 1, 16) ELSE SUBSTRING(CONVERT(VARCHAR, A.REQUEST_DT2, 120), 1, 16) END)
WHEN APRVL_KIND_CD = '0004' THEN '결근 : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23) + ' ' + ISNULL(SUBSTRING(CONVERT(VARCHAR, REQUEST_DT, 108), 1, 5), '') + '~' + ISNULL(SUBSTRING(CONVERT(VARCHAR, REQUEST_DT2, 108), 1, 5), '')
WHEN APRVL_KIND_CD = '0005' THEN '연차 : ' + UPD_DATE_YC + ' ' + FREE_CNT
WHEN APRVL_KIND_CD = '0006' THEN '변경 : ' + UPD_DATE_CHANGE + '(' + UPD_BF_WORK_CD + '-->' + UPD_WORK_CD + ')'
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_02 , -- 상세내역02
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN ''
WHEN APRVL_KIND_CD = '0003' THEN ''
WHEN APRVL_KIND_CD = '0004' THEN ''
WHEN APRVL_KIND_CD = '0005' THEN ''
WHEN APRVL_KIND_CD = '0006' THEN ''
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_03 , --상세내역 03
--A.OFFER_CN, --신청내용
--A.UPD_DT, --변경일시
A.APLNT_ID,
A.APLNT_NM,
A.APLNT_TEAM_CD,
A.APLNT_DUTY_CD
FROM (
SELECT A.CORP_NO,
A.APRVL_SNO,
A.APRVL_DT,
A.APPR_STUS_CD,
B.APRVL_DOC_ID,
B.APRVL_STUS_CD,
B.APRVL_KIND_CD,
B.OFFER_DT,
B.CMPL_DT,
B.APRVL_CMPL_SNO,
B.FINAL_APRVL_SNO,
B.APLNT_ID,
U1.USR_NM AS APLNT_NM,
U1.TEAM_CD AS APLNT_TEAM_CD,
U1.DUTY_CD AS APLNT_DUTY_CD,
U2.LATE_SKIPOFF_ABTI_OT_DATE,
U2.NML_WORK_RCNTN_YN,
U2.LATE_SKIPOFF_OT_DT,
U2.REQUEST_DT, --승인출근시각
U2.REQUEST_DT2, --승인퇴근시각
U3.UPD_DATE_YC,
U3.FREE_CNT,
U4.USR_NM AS BZ_DEPUTY_NM,
U3.UPD_DATE_CHANGE,
U3.UPD_BF_WORK_CD,
U3.UPD_WORK_CD,
B.APLNT_CN ,
B.BZ_CN,
B.OFFER_CN,
B.UPD_DT
FROM SX_GW0090 B LEFT JOIN
( SELECT * FROM SX_GW0100
WHERE 1=1
AND CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + CONVERT(VARCHAR, APRVL_SNO) IN
(SELECT CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + MAX(CONVERT(VARCHAR, APRVL_SNO))
FROM SX_GW0100
WHERE CORP_NO = @P7
GROUP BY CORP_NO, APRVL_DOC_ID )
) A
ON (1=1
AND B.CORP_NO = A.CORP_NO
AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
)
JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = B.CORP_NO
AND U1.USR_ID = B.APLNT_ID
--AND (NULLIF(@P0, '') IS NULL OR U1.USR_ID LIKE '%' + @P1 + '%') --사용자ID로 조회
AND (NULLIF(@P0, '') IS NULL OR U1.USR_NM LIKE '%' + @P1 + '%') --사용자명으로 조회
AND (NULLIF(@P2, '') IS NULL OR U1.TEAM_CD = @P3)
AND (NULLIF(@P4, '') IS NULL OR U1.DUTY_CD = @P5)
AND (@P6 = 'Y' OR NULLIF(U1.RETIREMENT_DATE, '') IS NULL)
)
LEFT JOIN SX_GW0080 U2 --SX_지각조퇴기록
ON(1=1
AND U2.CORP_NO = B.CORP_NO
AND U2.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN (SELECT TA.CORP_NO
, APRVL_DOC_ID
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
, CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)) + '일' AS FREE_CNT --연차 일수
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23)) AS UPD_DATE_CHANGE --근무변경
, MIN(UPD_BF_WORK_CD) AS UPD_BF_WORK_CD --변경전 근무 코드
, MIN(UPD_WORK_CD) AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110 TA LEFT JOIN SX_CO0070 TB ON TB.CORP_NO = TA.CORP_NO AND TB.WORK_CD = TA.UPD_WORK_CD
GROUP BY TA.CORP_NO, TA.APRVL_DOC_ID
) U3
ON(1=1
AND U3.CORP_NO = B.CORP_NO
AND U3.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = B.BZ_DEPUTY_ID )
WHERE 1=1
AND B.CORP_NO = @P7
AND (NULLIF(@P8, '') IS NULL OR B.APRVL_STUS_CD = @P9)
AND (NULLIF(@P10, '') IS NULL OR B.APRVL_KIND_CD = @P11)
AND CONVERT(VARCHAR, ISNULL(B.OFFER_DT, B.RGST_DT), 112) BETWEEN @P12 AND @P13
ORDER BY
ISNULL(B.OFFER_DT, B.RGST_DT) DESC
OFFSET ((@P14 - 1) * @P15) ROWS
FETCH NEXT @P16 ROWS ONLY
) A
--기존에 사용한 문장 BACKUP
--SELECT
-- A.APRVL_DOC_ID,
-- A.APRVL_STUS_CD,
-- A.APRVL_KIND_CD,
-- CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
-- CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
-- A.APRVL_CMPL_SNO,
-- A.FINAL_APRVL_SNO,
-- A.APLNT_ID,
-- A.APLNT_NM,
-- A.APLNT_TEAM_CD,
-- A.APLNT_DUTY_CD
-- FROM (
-- SELECT
-- A.APRVL_DOC_ID,
-- A.APRVL_STUS_CD,
-- A.APRVL_KIND_CD,
-- A.OFFER_DT,
-- A.CMPL_DT,
-- A.APRVL_CMPL_SNO,
-- A.FINAL_APRVL_SNO,
-- A.APLNT_ID,
-- U.USR_NM AS APLNT_NM,
-- U.TEAM_CD AS APLNT_TEAM_CD,
-- U.DUTY_CD AS APLNT_DUTY_CD
-- FROM SX_GW0090 A
-- JOIN SX_GW0010 U
-- ON(1=1
-- AND U.CORP_NO = A.CORP_NO
-- AND U.USR_ID = A.APLNT_ID
-- AND (NULLIF(@P0, '') IS NULL OR U.USR_ID = @P1)
-- AND (NULLIF(@P2, '') IS NULL OR U.TEAM_CD = @P3)
-- AND (NULLIF(@P4, '') IS NULL OR U.DUTY_CD = @P5)
-- AND (@P6 = 'Y' OR NULLIF(U.RETIREMENT_DATE, '') IS NULL)
-- )
-- WHERE 1=1
-- AND A.CORP_NO = @P7
-- AND (NULLIF(@P8, '') IS NULL OR A.APRVL_STUS_CD = @P9)
-- AND (NULLIF(@P10, '') IS NULL OR A.APRVL_KIND_CD = @P11)
-- AND CONVERT(VARCHAR, ISNULL(A.OFFER_DT, A.RGST_DT), 112) BETWEEN @P12 AND @P13
-- ORDER BY
-- ISNULL(A.OFFER_DT, A.RGST_DT) DESC
-- OFFSET ((@P14 - 1) * @P15) ROWS
-- FETCH NEXT @P16 ROWS ONLY
-- ) A
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_multi_delete] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-02-02
-- Create Description : 스카이브릿지 그룹웨어 결재문서 삭제- 다수건 삭제
-- Update date :
-- Update Description :
/*
DECLARE @ParamTable ApprovalParamTableType
insert into @ParamTable values ('1111', 338, '00001')
insert into @ParamTable values ('1111', 341, '00001')
insert into @ParamTable values ('1111', 349, '00001')
exec pr_skybridge_gw_approval_multi_delete @ParamTable
*/
-- =======================================================================================
/* Create a table type. -- 테이블 타입 선언
CREATE TYPE ApprovalParamTableType AS TABLE
( CORP_NO VARCHAR(15) --회사번호CORP_NO
, APRVL_DOC_ID INT --결재문서ID
, USR_ID VARCHAR(10) --사용자ID
);
GO
*/
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_multi_delete]
@ParamTable_1 ApprovalParamTableType READONLY
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--PARAMETER로 넘김 DATA를 FETCH하면서 담을 변수 지정
DECLARE @CORP_NO VARCHAR(15) --회사번호CORP_NO
DECLARE @APRVL_DOC_ID INT --결재문서ID
DECLARE @USR_ID VARCHAR(10) --사용자ID
DECLARE Cursor_1 CURSOR FOR
SELECT CORP_NO
, APRVL_DOC_ID
, USR_ID
FROM @ParamTable_1
OPEN Cursor_1;
FETCH NEXT FROM Cursor_1 INTO @CORP_NO, @APRVL_DOC_ID, @USR_ID;
WHILE @@FETCH_STATUS = 0 --FETCH가 성공하면
BEGIN
exec pr_skybridge_gw_approval_delete @CORP_NO, @APRVL_DOC_ID, @USR_ID
FETCH NEXT FROM Cursor_1 INTO @CORP_NO, @APRVL_DOC_ID, @USR_ID;
END;
CLOSE Cursor_1;
DEALLOCATE Cursor_1;
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_request_list_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 결재 신청 리스트 조회 (개별)
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_approval_request_list_select '1111','00001','','','','','20161215','20170215',1,20000,20000 --1페이지
-- exec pr_skybridge_gw_approval_request_list_select '1111','00001','','','','','20160101','20170215',1,20000,20000 --1페이지
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_request_list_select]
@P0 VARCHAR(4000) --CORP_NO
, @P1 VARCHAR(4000) --APPR_ID
, @P2 VARCHAR(4000) --
, @P3 VARCHAR(4000) --
, @P4 VARCHAR(4000) --
, @P5 VARCHAR(4000) --
, @P6 VARCHAR(4000) --
, @P7 VARCHAR(4000) --APRVL_KIND_CD
, @P8 INT -- 조회 페이지 번호
, @P9 INT -- 한행에 조회되는 행수
, @P10 INT -- 한행에 조회되는 행수
, @P11 VARCHAR(4000) --
, @P12 VARCHAR(4000) --
, @P13 VARCHAR(4000) --
, @P14 VARCHAR(4000) --APRVL_KIND_CD
, @P15 VARCHAR(10) -- PROCESS_CD
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
/* 0001 : 작성중 */ --AND (B.APRVL_STUS_CD = '0001')
/* 0002 : 미결재 */ --AND (B.APRVL_STUS_CD = '0002')
/* 0003 : 승인 */ --AND (B.APRVL_STUS_CD = '0003' AND ISNULL(B.DOC_FLAG, '') != 'A')
/* 0004 : 반려 */ --AND (B.APRVL_STUS_CD = '0004' AND ISNULL(B.DOC_FLAG, '') != 'A' AND ISNULL(CH1.APPR_STUS_CD, '') = '0004' )
-- 2017.07.12
SELECT
A.APRVL_SNO,
CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
A.APPR_STUS_CD,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
ISNULL(A.APRVL_CMPL_SNO,0) AS APRVL_CMPL_SNO,
ISNULL(A.FINAL_APRVL_SNO,0) AS FINAL_APRVL_SNO,
-- ■ 스카이브릿지 결재문서 결재 상세 내역 정리
--- 1.시간외 : 신청내용, 업무내용, OT일자
--- 2.지각 : 신청내용, 업무내용, 정상근무인정 요청, 지각일자(X), 지각일시
--- 3.조퇴 : 신청내용, 업무내용, 정상근무인정 요청, 조퇴일자(X), 조퇴일시
--- 4.결근 : 신청내용, 업무내용, 정상근무인정 요청, 결근일자
--- 5.연차등 : 신청내용, 업무내용, 대행자, 연차 (날자, 연차등)
--- 6.근무변경 : 신청내용, 업무내용, 대행자, 근무변경(근무일, 변경전, 변경후)
--- 7.기안 : 신청내용, 업무내용
A.APLNT_CN , --신청자 내용
A.BZ_CN, --업무내용
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN '' --'인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0003' THEN CASE WHEN A.NML_WORK_RCNTN_YN = 'Y'THEN '조기퇴근' ELSE '' END -- '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0004' THEN '' --'인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0005' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0006' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_01 , --상세내역 01
CASE WHEN APRVL_KIND_CD = '0001' THEN 'OT : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0002' THEN '지각 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
WHEN APRVL_KIND_CD = '0003' THEN '조퇴 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
WHEN APRVL_KIND_CD = '0004' THEN '결근 : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0005' THEN '연차 : ' + UPD_DATE_YC + ' ' + FREE_CNT
WHEN APRVL_KIND_CD = '0006' THEN '변경 : ' + UPD_DATE_CHANGE + '(' + UPD_BF_WORK_CD + '-->' + UPD_WORK_CD + ')'
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_02 , -- 상세내역02
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN ''
WHEN APRVL_KIND_CD = '0003' THEN ''
WHEN APRVL_KIND_CD = '0004' THEN ''
WHEN APRVL_KIND_CD = '0005' THEN ''
WHEN APRVL_KIND_CD = '0006' THEN ''
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_03 , --상세내역 03
--A.OFFER_CN, --신청내용
--A.UPD_DT, --변경일시
A.APLNT_ID,
A.APLNT_NM,
PROCESS_CD
FROM (
SELECT A.CORP_NO
, A.APRVL_SNO
, A.APRVL_DT
, A.APPR_STUS_CD
, A.RGST_DT
, B.APRVL_DOC_ID
, B.APRVL_STUS_CD
, B.APRVL_KIND_CD
, B.OFFER_DT
, B.CMPL_DT
, B.APRVL_CMPL_SNO
, B.FINAL_APRVL_SNO
, B.APLNT_ID
, U1.USR_NM AS APLNT_NM
, U2.LATE_SKIPOFF_ABTI_OT_DATE
, U2.NML_WORK_RCNTN_YN
, U2.LATE_SKIPOFF_OT_DT
, U3.UPD_DATE_YC
, U3.FREE_CNT
, U4.USR_NM AS BZ_DEPUTY_NM
, U3.UPD_DATE_CHANGE
, U3.UPD_BF_WORK_CD
, U3.UPD_WORK_CD
, B.APLNT_CN
, B.BZ_CN
, B.OFFER_CN
, B.UPD_DT
, CASE WHEN B.APRVL_STUS_CD = '0001' THEN '0001'
WHEN B.APRVL_STUS_CD = '0002' THEN '0002'
WHEN B.APRVL_STUS_CD = '0003' AND ISNULL(B.DOC_FLAG, '') != 'A' THEN '0003'
--WHEN B.APRVL_STUS_CD = '0004' AND ISNULL(B.DOC_FLAG, '') != 'A' AND ISNULL(CH1.APPR_STUS_CD, '') = '0004' THEN '0004'
WHEN B.APRVL_STUS_CD = '0004' AND ISNULL(B.DOC_FLAG, '') != 'A' AND (ISNULL(CH1.APPR_STUS_CD, '') = '0004' OR ISNULL(CH1.DOC_FLAG, '') = 'A') THEN '0004'
ELSE ''
END AS PROCESS_CD
FROM SX_GW0090 B
LEFT OUTER JOIN
( SELECT * FROM SX_GW0100
WHERE 1=1
AND CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + CONVERT(VARCHAR, APRVL_SNO) IN
(SELECT CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + MAX(CONVERT(VARCHAR, APRVL_SNO))
FROM SX_GW0100
WHERE CORP_NO = @P0
GROUP BY CORP_NO, APRVL_DOC_ID )
) A
ON (1=1
AND B.CORP_NO = A.CORP_NO
AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = B.CORP_NO
AND U1.USR_ID = B.APLNT_ID
)
LEFT JOIN SX_GW0080 U2 --SX_지각조퇴기록
ON(1=1
AND U2.CORP_NO = B.CORP_NO
AND U2.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN (SELECT TA.CORP_NO
, APRVL_DOC_ID
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
, CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)) + '일' AS FREE_CNT --연차 일수
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23)) AS UPD_DATE_CHANGE --근무변경
, MIN(UPD_BF_WORK_CD) AS UPD_BF_WORK_CD --변경전 근무 코드
, MIN(UPD_WORK_CD) AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110 TA LEFT JOIN SX_CO0070 TB ON TB.CORP_NO = TA.CORP_NO AND TB.WORK_CD = TA.UPD_WORK_CD
GROUP BY TA.CORP_NO, TA.APRVL_DOC_ID
) U3
ON(1=1
AND U3.CORP_NO = B.CORP_NO
AND U3.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = B.BZ_DEPUTY_ID )
LEFT OUTER JOIN SX_GW0100 CH1
ON B.CORP_NO = CH1.CORP_NO AND B.APRVL_DOC_ID = CH1.APRVL_DOC_ID AND CH1.APRVL_SNO = 1
WHERE 1=1
AND B.CORP_NO = @P0
AND B.APLNT_ID = @P1
AND (NULLIF(@P2, '') IS NULL OR B.APRVL_STUS_CD = @P3)
AND
(
(NULLIF(@P4, '') IS NULL OR (LEN(@P11) = 4 AND B.APRVL_KIND_CD = @P5 ) )
OR
(NULLIF(@P12, '') IS NULL OR (LEN(@P13) > 4 AND B.APRVL_KIND_CD IN(/*'0001', */'0002', '0003', '0008', '0004') ) )
)
AND CONVERT(VARCHAR, ISNULL(B.OFFER_DT, B.RGST_DT), 112) BETWEEN @P6 AND @P7
/*
ORDER BY
ISNULL(B.OFFER_DT, A.RGST_DT) DESC
OFFSET ((@P8 - 1) * @P9) ROWS
FETCH NEXT @P10 ROWS ONLY
*/
) A
WHERE 1=1
AND (NULLIF(@P15, '') IS NULL OR A.PROCESS_CD = @P15)
ORDER BY
ISNULL(A.OFFER_DT, A.RGST_DT) DESC
OFFSET ((@P8 - 1) * @P9) ROWS
FETCH NEXT @P10 ROWS ONLY
/* 백업_v2
SELECT
A.APRVL_SNO,
CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
A.APPR_STUS_CD,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
ISNULL(A.APRVL_CMPL_SNO,0) AS APRVL_CMPL_SNO,
ISNULL(A.FINAL_APRVL_SNO,0) AS FINAL_APRVL_SNO,
-- ■ 스카이브릿지 결재문서 결재 상세 내역 정리
--- 1.시간외 : 신청내용, 업무내용, OT일자
--- 2.지각 : 신청내용, 업무내용, 정상근무인정 요청, 지각일자(X), 지각일시
--- 3.조퇴 : 신청내용, 업무내용, 정상근무인정 요청, 조퇴일자(X), 조퇴일시
--- 4.결근 : 신청내용, 업무내용, 정상근무인정 요청, 결근일자
--- 5.연차등 : 신청내용, 업무내용, 대행자, 연차 (날자, 연차등)
--- 6.근무변경 : 신청내용, 업무내용, 대행자, 근무변경(근무일, 변경전, 변경후)
--- 7.기안 : 신청내용, 업무내용
A.APLNT_CN , --신청자 내용
A.BZ_CN, --업무내용
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0003' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0004' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0005' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0006' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_01 , --상세내역 01
CASE WHEN APRVL_KIND_CD = '0001' THEN 'OT : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0002' THEN '지각 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
WHEN APRVL_KIND_CD = '0003' THEN '조퇴 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
WHEN APRVL_KIND_CD = '0004' THEN '결근 : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0005' THEN '연차 : ' + UPD_DATE_YC + ' ' + FREE_CNT
WHEN APRVL_KIND_CD = '0006' THEN '변경 : ' + UPD_DATE_CHANGE + '(' + UPD_BF_WORK_CD + '-->' + UPD_WORK_CD + ')'
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_02 , -- 상세내역02
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN ''
WHEN APRVL_KIND_CD = '0003' THEN ''
WHEN APRVL_KIND_CD = '0004' THEN ''
WHEN APRVL_KIND_CD = '0005' THEN ''
WHEN APRVL_KIND_CD = '0006' THEN ''
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_03 , --상세내역 03
--A.OFFER_CN, --신청내용
--A.UPD_DT, --변경일시
A.APLNT_ID,
A.APLNT_NM
FROM (
SELECT A.CORP_NO,
A.APRVL_SNO,
A.APRVL_DT,
A.APPR_STUS_CD,
B.APRVL_DOC_ID,
B.APRVL_STUS_CD,
B.APRVL_KIND_CD,
B.OFFER_DT,
B.CMPL_DT,
B.APRVL_CMPL_SNO,
B.FINAL_APRVL_SNO,
B.APLNT_ID,
U1.USR_NM AS APLNT_NM,
U2.LATE_SKIPOFF_ABTI_OT_DATE,
U2.NML_WORK_RCNTN_YN,
U2.LATE_SKIPOFF_OT_DT,
U3.UPD_DATE_YC,
U3.FREE_CNT,
U4.USR_NM AS BZ_DEPUTY_NM,
U3.UPD_DATE_CHANGE,
U3.UPD_BF_WORK_CD,
U3.UPD_WORK_CD,
B.APLNT_CN ,
B.BZ_CN,
B.OFFER_CN,
B.UPD_DT
FROM SX_GW0090 B
LEFT OUTER JOIN
( SELECT * FROM SX_GW0100
WHERE 1=1
AND CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + CONVERT(VARCHAR, APRVL_SNO) IN
(SELECT CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + MAX(CONVERT(VARCHAR, APRVL_SNO))
FROM SX_GW0100
WHERE CORP_NO = @P0
GROUP BY CORP_NO, APRVL_DOC_ID )
) A
ON (1=1
AND B.CORP_NO = A.CORP_NO
AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = B.CORP_NO
AND U1.USR_ID = B.APLNT_ID
)
LEFT JOIN SX_GW0080 U2 --SX_지각조퇴기록
ON(1=1
AND U2.CORP_NO = B.CORP_NO
AND U2.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN (SELECT TA.CORP_NO
, APRVL_DOC_ID
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
, CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)) + '일' AS FREE_CNT --연차 일수
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23)) AS UPD_DATE_CHANGE --근무변경
, MIN(UPD_BF_WORK_CD) AS UPD_BF_WORK_CD --변경전 근무 코드
, MIN(UPD_WORK_CD) AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110 TA LEFT JOIN SX_CO0070 TB ON TB.CORP_NO = TA.CORP_NO AND TB.WORK_CD = TA.UPD_WORK_CD
GROUP BY TA.CORP_NO, TA.APRVL_DOC_ID
) U3
ON(1=1
AND U3.CORP_NO = B.CORP_NO
AND U3.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = B.BZ_DEPUTY_ID )
WHERE 1=1
AND B.CORP_NO = @P0
AND B.APLNT_ID = @P1
AND (NULLIF(@P2, '') IS NULL OR B.APRVL_STUS_CD = @P3)
AND
(
(NULLIF(@P4, '') IS NULL OR (LEN(@P11) = 4 AND B.APRVL_KIND_CD = @P5 ) )
OR
(NULLIF(@P12, '') IS NULL OR (LEN(@P13) > 4 AND B.APRVL_KIND_CD IN('0002', '0003', '0008', '0004') ) )
)
AND CONVERT(VARCHAR, ISNULL(B.OFFER_DT, B.RGST_DT), 112) BETWEEN @P6 AND @P7
ORDER BY
ISNULL(B.OFFER_DT, A.RGST_DT) DESC
OFFSET ((@P8 - 1) * @P9) ROWS
FETCH NEXT @P10 ROWS ONLY
) A
*/
--기존에 사용한 문장 BACKUP
--declare @p1 int
--set @p1=1492
--exec sp_prepexec @p1 output
--@P0 nvarchar(4000)
--,@P1 nvarchar(4000)
--,@P2 nvarchar(4000)
--,@P3 nvarchar(4000)
--,@P4 nvarchar(4000)
--,@P5 nvarchar(4000)
--,@P6 nvarchar(4000)
--,@P7 nvarchar(4000)
--,@P8 int
--,@P9 int
--,@P10 int
--SELECT
-- A.APRVL_DOC_ID,
-- A.APRVL_STUS_CD,
-- A.APRVL_KIND_CD,
-- CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
-- CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
-- A.APRVL_CMPL_SNO,
-- A.FINAL_APRVL_SNO,
-- (SELECT TOP 1 USR_NM FROM SX_GW0010 WHERE USR_ID = A.APLNT_ID) AS APLNT_NM
-- FROM (
-- SELECT
-- A.APRVL_DOC_ID,
-- A.APRVL_STUS_CD,
-- A.APRVL_KIND_CD,
-- A.OFFER_DT,
-- A.CMPL_DT,
-- A.APRVL_CMPL_SNO,
-- A.FINAL_APRVL_SNO,
-- A.APLNT_ID
-- FROM SX_GW0090 A
-- WHERE 1=1
-- AND A.CORP_NO = @P0
-- AND A.APLNT_ID = @P1
-- AND (NULLIF(@P2, '') IS NULL OR A.APRVL_STUS_CD = @P3)
-- AND (NULLIF(@P4, '') IS NULL OR A.APRVL_KIND_CD = @P5)
-- AND CONVERT(VARCHAR, ISNULL(A.OFFER_DT, A.RGST_DT), 112) BETWEEN @P6 AND @P7
-- ORDER BY
-- ISNULL(A.OFFER_DT, A.RGST_DT) DESC
--OFFSET ((@P8 - 1) * @P9) ROWS
--FETCH NEXT @P10 ROWS ONLY
-- ) A
--'1111','00001','','','','','20161215','20170215',1,20,20
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_approval_request_list_select_count] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : Byun
-- Create date : 2017-07-12
-- Create Description : 스카이브릿지 그룹웨어 결재 신청 리스트 개수 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_approval_request_list_select '1111','00001','','','','','20161215','20170215',1,20000,20000 --1페이지
-- exec pr_skybridge_gw_approval_request_list_select '1111','00001','','','','','20160101','20170215',1,20000,20000 --1페이지
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_approval_request_list_select_count]
@P0 VARCHAR(4000) --CORP_NO
, @P1 VARCHAR(4000) --APPR_ID
, @P2 VARCHAR(4000) --
, @P3 VARCHAR(4000) --
, @P4 VARCHAR(4000) --
, @P5 VARCHAR(4000) --
, @P6 VARCHAR(4000) --
, @P7 VARCHAR(4000) --APRVL_KIND_CD
, @P8 INT -- 조회 페이지 번호
, @P9 INT -- 한행에 조회되는 행수
, @P10 INT -- 한행에 조회되는 행수
, @P11 VARCHAR(4000) --
, @P12 VARCHAR(4000) --
, @P13 VARCHAR(4000) --
, @P14 VARCHAR(4000) --APRVL_KIND_CD
, @P15 VARCHAR(10) -- PROCESS_CD
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
/* 0001 : 작성중 */ --AND (B.APRVL_STUS_CD = '0001')
/* 0002 : 미결재 */ --AND (B.APRVL_STUS_CD = '0002')
/* 0003 : 승인 */ --AND (B.APRVL_STUS_CD = '0003' AND ISNULL(B.DOC_FLAG, '') != 'A')
/* 0004 : 반려 */ --AND (B.APRVL_STUS_CD = '0004' AND ISNULL(B.DOC_FLAG, '') != 'A' AND ISNULL(CH1.DOC_FLAG, '') = 'A' )
-- 2017.07.12
SELECT COUNT('A') AS TOT_CNT
FROM(
SELECT
A.APRVL_SNO,
CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
A.APPR_STUS_CD,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
ISNULL(A.APRVL_CMPL_SNO,0) AS APRVL_CMPL_SNO,
ISNULL(A.FINAL_APRVL_SNO,0) AS FINAL_APRVL_SNO,
-- ■ 스카이브릿지 결재문서 결재 상세 내역 정리
--- 1.시간외 : 신청내용, 업무내용, OT일자
--- 2.지각 : 신청내용, 업무내용, 정상근무인정 요청, 지각일자(X), 지각일시
--- 3.조퇴 : 신청내용, 업무내용, 정상근무인정 요청, 조퇴일자(X), 조퇴일시
--- 4.결근 : 신청내용, 업무내용, 정상근무인정 요청, 결근일자
--- 5.연차등 : 신청내용, 업무내용, 대행자, 연차 (날자, 연차등)
--- 6.근무변경 : 신청내용, 업무내용, 대행자, 근무변경(근무일, 변경전, 변경후)
--- 7.기안 : 신청내용, 업무내용
A.APLNT_CN , --신청자 내용
A.BZ_CN, --업무내용
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0003' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0004' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0005' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0006' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_01 , --상세내역 01
CASE WHEN APRVL_KIND_CD = '0001' THEN 'OT : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0002' THEN '지각 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
WHEN APRVL_KIND_CD = '0003' THEN '조퇴 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
WHEN APRVL_KIND_CD = '0004' THEN '결근 : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0005' THEN '연차 : ' + UPD_DATE_YC + ' ' + FREE_CNT
WHEN APRVL_KIND_CD = '0006' THEN '변경 : ' + UPD_DATE_CHANGE + '(' + UPD_BF_WORK_CD + '-->' + UPD_WORK_CD + ')'
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_02 , -- 상세내역02
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN ''
WHEN APRVL_KIND_CD = '0003' THEN ''
WHEN APRVL_KIND_CD = '0004' THEN ''
WHEN APRVL_KIND_CD = '0005' THEN ''
WHEN APRVL_KIND_CD = '0006' THEN ''
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_03 , --상세내역 03
--A.OFFER_CN, --신청내용
--A.UPD_DT, --변경일시
A.APLNT_ID,
A.APLNT_NM,
PROCESS_CD
FROM (
SELECT A.CORP_NO
, A.APRVL_SNO
, A.APRVL_DT
, A.APPR_STUS_CD
, B.APRVL_DOC_ID
, B.APRVL_STUS_CD
, B.APRVL_KIND_CD
, B.OFFER_DT
, B.CMPL_DT
, B.APRVL_CMPL_SNO
, B.FINAL_APRVL_SNO
, B.APLNT_ID
, U1.USR_NM AS APLNT_NM
, U2.LATE_SKIPOFF_ABTI_OT_DATE
, U2.NML_WORK_RCNTN_YN
, U2.LATE_SKIPOFF_OT_DT
, U3.UPD_DATE_YC
, U3.FREE_CNT
, U4.USR_NM AS BZ_DEPUTY_NM
, U3.UPD_DATE_CHANGE
, U3.UPD_BF_WORK_CD
, U3.UPD_WORK_CD
, B.APLNT_CN
, B.BZ_CN
, B.OFFER_CN
, B.UPD_DT
, CASE WHEN B.APRVL_STUS_CD = '0001' THEN '0001'
WHEN B.APRVL_STUS_CD = '0002' THEN '0002'
WHEN B.APRVL_STUS_CD = '0003' AND ISNULL(B.DOC_FLAG, '') != 'A' THEN '0003'
WHEN B.APRVL_STUS_CD = '0004' AND ISNULL(B.DOC_FLAG, '') != 'A' AND ISNULL(CH1.DOC_FLAG, '') = 'A' THEN '0004'
ELSE ''
END AS PROCESS_CD
FROM SX_GW0090 B
LEFT OUTER JOIN
( SELECT * FROM SX_GW0100
WHERE 1=1
AND CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + CONVERT(VARCHAR, APRVL_SNO) IN
(SELECT CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + MAX(CONVERT(VARCHAR, APRVL_SNO))
FROM SX_GW0100
WHERE CORP_NO = @P0
GROUP BY CORP_NO, APRVL_DOC_ID )
) A
ON (1=1
AND B.CORP_NO = A.CORP_NO
AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = B.CORP_NO
AND U1.USR_ID = B.APLNT_ID
)
LEFT JOIN SX_GW0080 U2 --SX_지각조퇴기록
ON(1=1
AND U2.CORP_NO = B.CORP_NO
AND U2.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN (SELECT TA.CORP_NO
, APRVL_DOC_ID
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
, CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)) + '일' AS FREE_CNT --연차 일수
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23)) AS UPD_DATE_CHANGE --근무변경
, MIN(UPD_BF_WORK_CD) AS UPD_BF_WORK_CD --변경전 근무 코드
, MIN(UPD_WORK_CD) AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110 TA LEFT JOIN SX_CO0070 TB ON TB.CORP_NO = TA.CORP_NO AND TB.WORK_CD = TA.UPD_WORK_CD
GROUP BY TA.CORP_NO, TA.APRVL_DOC_ID
) U3
ON(1=1
AND U3.CORP_NO = B.CORP_NO
AND U3.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = B.BZ_DEPUTY_ID )
LEFT OUTER JOIN SX_GW0100 CH1
ON B.CORP_NO = CH1.CORP_NO AND B.APRVL_DOC_ID = CH1.APRVL_DOC_ID AND CH1.APRVL_SNO = 1
WHERE 1=1
AND B.CORP_NO = @P0
AND B.APLNT_ID = @P1
AND (NULLIF(@P2, '') IS NULL OR B.APRVL_STUS_CD = @P3)
AND
(
(NULLIF(@P4, '') IS NULL OR (LEN(@P11) = 4 AND B.APRVL_KIND_CD = @P5 ) )
OR
(NULLIF(@P12, '') IS NULL OR (LEN(@P13) > 4 AND B.APRVL_KIND_CD IN(/*'0001', */'0002', '0003', '0008', '0004') ) )
)
AND CONVERT(VARCHAR, ISNULL(B.OFFER_DT, B.RGST_DT), 112) BETWEEN @P6 AND @P7
) A
WHERE 1=1
AND (NULLIF(@P15, '') IS NULL OR A.PROCESS_CD = @P15)
) AS TOT
/* 백업_v2
SELECT
A.APRVL_SNO,
CONVERT(VARCHAR, A.APRVL_DT, 120) AS APRVL_DT,
A.APPR_STUS_CD,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
ISNULL(A.APRVL_CMPL_SNO,0) AS APRVL_CMPL_SNO,
ISNULL(A.FINAL_APRVL_SNO,0) AS FINAL_APRVL_SNO,
-- ■ 스카이브릿지 결재문서 결재 상세 내역 정리
--- 1.시간외 : 신청내용, 업무내용, OT일자
--- 2.지각 : 신청내용, 업무내용, 정상근무인정 요청, 지각일자(X), 지각일시
--- 3.조퇴 : 신청내용, 업무내용, 정상근무인정 요청, 조퇴일자(X), 조퇴일시
--- 4.결근 : 신청내용, 업무내용, 정상근무인정 요청, 결근일자
--- 5.연차등 : 신청내용, 업무내용, 대행자, 연차 (날자, 연차등)
--- 6.근무변경 : 신청내용, 업무내용, 대행자, 근무변경(근무일, 변경전, 변경후)
--- 7.기안 : 신청내용, 업무내용
A.APLNT_CN , --신청자 내용
A.BZ_CN, --업무내용
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0003' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0004' THEN '인정 : ' + A.NML_WORK_RCNTN_YN
WHEN APRVL_KIND_CD = '0005' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0006' THEN '대행자 : ' + A.BZ_DEPUTY_NM
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_01 , --상세내역 01
CASE WHEN APRVL_KIND_CD = '0001' THEN 'OT : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0002' THEN '지각 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
WHEN APRVL_KIND_CD = '0003' THEN '조퇴 : ' + CONVERT(VARCHAR, A.LATE_SKIPOFF_OT_DT, 120)
WHEN APRVL_KIND_CD = '0004' THEN '결근 : ' + CONVERT(VARCHAR, CONVERT(DATETIME, A.LATE_SKIPOFF_ABTI_OT_DATE), 23)
WHEN APRVL_KIND_CD = '0005' THEN '연차 : ' + UPD_DATE_YC + ' ' + FREE_CNT
WHEN APRVL_KIND_CD = '0006' THEN '변경 : ' + UPD_DATE_CHANGE + '(' + UPD_BF_WORK_CD + '-->' + UPD_WORK_CD + ')'
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_02 , -- 상세내역02
CASE WHEN APRVL_KIND_CD = '0001' THEN ''
WHEN APRVL_KIND_CD = '0002' THEN ''
WHEN APRVL_KIND_CD = '0003' THEN ''
WHEN APRVL_KIND_CD = '0004' THEN ''
WHEN APRVL_KIND_CD = '0005' THEN ''
WHEN APRVL_KIND_CD = '0006' THEN ''
WHEN APRVL_KIND_CD = '0007' THEN ''
ELSE ''
END AS DETAIL_03 , --상세내역 03
--A.OFFER_CN, --신청내용
--A.UPD_DT, --변경일시
A.APLNT_ID,
A.APLNT_NM
FROM (
SELECT A.CORP_NO,
A.APRVL_SNO,
A.APRVL_DT,
A.APPR_STUS_CD,
B.APRVL_DOC_ID,
B.APRVL_STUS_CD,
B.APRVL_KIND_CD,
B.OFFER_DT,
B.CMPL_DT,
B.APRVL_CMPL_SNO,
B.FINAL_APRVL_SNO,
B.APLNT_ID,
U1.USR_NM AS APLNT_NM,
U2.LATE_SKIPOFF_ABTI_OT_DATE,
U2.NML_WORK_RCNTN_YN,
U2.LATE_SKIPOFF_OT_DT,
U3.UPD_DATE_YC,
U3.FREE_CNT,
U4.USR_NM AS BZ_DEPUTY_NM,
U3.UPD_DATE_CHANGE,
U3.UPD_BF_WORK_CD,
U3.UPD_WORK_CD,
B.APLNT_CN ,
B.BZ_CN,
B.OFFER_CN,
B.UPD_DT
FROM SX_GW0090 B
LEFT OUTER JOIN
( SELECT * FROM SX_GW0100
WHERE 1=1
AND CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + CONVERT(VARCHAR, APRVL_SNO) IN
(SELECT CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) + '|' + MAX(CONVERT(VARCHAR, APRVL_SNO))
FROM SX_GW0100
WHERE CORP_NO = @P0
GROUP BY CORP_NO, APRVL_DOC_ID )
) A
ON (1=1
AND B.CORP_NO = A.CORP_NO
AND B.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = B.CORP_NO
AND U1.USR_ID = B.APLNT_ID
)
LEFT JOIN SX_GW0080 U2 --SX_지각조퇴기록
ON(1=1
AND U2.CORP_NO = B.CORP_NO
AND U2.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN (SELECT TA.CORP_NO
, APRVL_DOC_ID
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23) + '(' + UPD_WORK_CD + ')') AS UPD_DATE_YC --연차
, CONVERT (VARCHAR, SUM(TB.YYCT_DEDU_DAY)) + '일' AS FREE_CNT --연차 일수
, MIN(CONVERT(VARCHAR, CONVERT(DATETIME, UPD_DATE), 23)) AS UPD_DATE_CHANGE --근무변경
, MIN(UPD_BF_WORK_CD) AS UPD_BF_WORK_CD --변경전 근무 코드
, MIN(UPD_WORK_CD) AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110 TA LEFT JOIN SX_CO0070 TB ON TB.CORP_NO = TA.CORP_NO AND TB.WORK_CD = TA.UPD_WORK_CD
GROUP BY TA.CORP_NO, TA.APRVL_DOC_ID
) U3
ON(1=1
AND U3.CORP_NO = B.CORP_NO
AND U3.APRVL_DOC_ID = B.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = B.BZ_DEPUTY_ID )
WHERE 1=1
AND B.CORP_NO = @P0
AND B.APLNT_ID = @P1
AND (NULLIF(@P2, '') IS NULL OR B.APRVL_STUS_CD = @P3)
AND
(
(NULLIF(@P4, '') IS NULL OR (LEN(@P11) = 4 AND B.APRVL_KIND_CD = @P5 ) )
OR
(NULLIF(@P12, '') IS NULL OR (LEN(@P13) > 4 AND B.APRVL_KIND_CD IN('0002', '0003', '0008', '0004') ) )
)
AND CONVERT(VARCHAR, ISNULL(B.OFFER_DT, B.RGST_DT), 112) BETWEEN @P6 AND @P7
ORDER BY
ISNULL(B.OFFER_DT, A.RGST_DT) DESC
OFFSET ((@P8 - 1) * @P9) ROWS
FETCH NEXT @P10 ROWS ONLY
) A
*/
--기존에 사용한 문장 BACKUP
--declare @p1 int
--set @p1=1492
--exec sp_prepexec @p1 output
--@P0 nvarchar(4000)
--,@P1 nvarchar(4000)
--,@P2 nvarchar(4000)
--,@P3 nvarchar(4000)
--,@P4 nvarchar(4000)
--,@P5 nvarchar(4000)
--,@P6 nvarchar(4000)
--,@P7 nvarchar(4000)
--,@P8 int
--,@P9 int
--,@P10 int
--SELECT
-- A.APRVL_DOC_ID,
-- A.APRVL_STUS_CD,
-- A.APRVL_KIND_CD,
-- CONVERT(VARCHAR, A.OFFER_DT, 120) AS OFFER_DT,
-- CONVERT(VARCHAR, A.CMPL_DT, 120) AS CMPL_DT,
-- A.APRVL_CMPL_SNO,
-- A.FINAL_APRVL_SNO,
-- (SELECT TOP 1 USR_NM FROM SX_GW0010 WHERE USR_ID = A.APLNT_ID) AS APLNT_NM
-- FROM (
-- SELECT
-- A.APRVL_DOC_ID,
-- A.APRVL_STUS_CD,
-- A.APRVL_KIND_CD,
-- A.OFFER_DT,
-- A.CMPL_DT,
-- A.APRVL_CMPL_SNO,
-- A.FINAL_APRVL_SNO,
-- A.APLNT_ID
-- FROM SX_GW0090 A
-- WHERE 1=1
-- AND A.CORP_NO = @P0
-- AND A.APLNT_ID = @P1
-- AND (NULLIF(@P2, '') IS NULL OR A.APRVL_STUS_CD = @P3)
-- AND (NULLIF(@P4, '') IS NULL OR A.APRVL_KIND_CD = @P5)
-- AND CONVERT(VARCHAR, ISNULL(A.OFFER_DT, A.RGST_DT), 112) BETWEEN @P6 AND @P7
-- ORDER BY
-- ISNULL(A.OFFER_DT, A.RGST_DT) DESC
--OFFSET ((@P8 - 1) * @P9) ROWS
--FETCH NEXT @P10 ROWS ONLY
-- ) A
--'1111','00001','','','','','20161215','20170215',1,20,20
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_holiday_detail_list_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-08-11
-- Create Description : 스카이브릿지 그룹웨어 연차등상세내역 리스트 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '', '', '', ''
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '강충우', '', '', ''
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '', '16', '', ''
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '', '', 'Y', ''
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '', '', '', 'USR_NM' --정렬순서 : (이름), 직책, 업무별, 팀별, 업로드순
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '', '', '', 'DUTY_CD' --정렬순서 : 이름, (직책), 업무별, 팀별, 업로드순
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '', '', '', 'BZ_CD' --정렬순서 : 이름, 직책, (업무별), 팀별, 업로드순
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '', '', '', 'TEAM_CD' --정렬순서 : 이름, 직책, 업무별, (팀별), 업로드순
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2017', '', '', '', 'SORT_ODR' --정렬순서 : 이름, 직책, 업무별, 팀별, (업로드순)
-- exec pr_skybridge_gw_holiday_detail_list_select '1111', '2018', '정진구', '', '', 'SORT_ODR' --정렬순서 : 이름, 직책, 업무별, 팀별, (업로드순)
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_holiday_detail_list_select]
@CORP_NO VARCHAR(15) --회사코드
, @YYVCT_YY VARCHAR(20) --조회년도
, @USR_NM VARCHAR(50) --이름
, @TEAM_CD VARCHAR(50) --팀코드
, @RETIRE_YN VARCHAR(10) --퇴사여부
, @ORDER_TYPE VARCHAR(10) --정렬방법
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT A.USR_ID,
A.USR_ID AS ORDER_2,
A.USR_NM,
A.BZ_CD,
A.TEAM_CD,
A.DUTY_CD,
@YYVCT_YY AS YYVCT_YY,
ISNULL(B.YYVCT_CNT,0) AS YYVCT_CNT, --(A)총연차
ISNULL(COM_CNT,0) AS COM_CNT, --(B)승인연차
ISNULL(NONCOM_CNT,0) AS NONCOM_CNT, --(C)비승인연차
ISNULL(ABSENT_CNT,0) AS ABSENT_CNT, --(D)출퇴근미기록
ISNULL(B.YYVCT_CNT,0) - ISNULL(COM_CNT,0)
- ISNULL(NONCOM_CNT,0)
- ISNULL(ABSENT_CNT,0) AS REMAIN_CNT, --(S)잔여연차 S=A-B-C-D
B.SORT_ODR,
0 AS ORI
FROM SX_GW0010 A
LEFT JOIN SX_GW0060 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.USR_ID = A.USR_ID
AND B.YYVCT_YY = @YYVCT_YY )
--승인 연차 일수
LEFT OUTER JOIN (
SELECT A.CORP_NO,
A.USR_ID,
SUM(B.YYCT_DEDU_DAY) AS COM_CNT --YYCT_DEDU_DCNT, --연차사용일수
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(A.WORK_CD, A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
--AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND A.WORK_PLAN_YYMMDD IN ( SELECT T2.UPD_DATE
FROM SX_GW0090 T1
INNER JOIN SX_GW0110 T2 ON (T1.CORP_NO = T2.CORP_NO
AND T1.APRVL_DOC_ID = T2.APRVL_DOC_ID )
WHERE T1.APRVL_STUS_CD = '0003' --결재완료
AND T1.CORP_NO = A.CORP_NO
AND T1.APLNT_ID = A.USR_ID
)
GROUP BY
A.CORP_NO,
A.USR_ID
) T2
ON( 1=1
AND T2.CORP_NO = A.CORP_NO
AND T2.USR_ID = A.USR_ID
)
--비승인 연차 일수
LEFT OUTER JOIN (
SELECT A.CORP_NO,
A.USR_ID,
SUM(B.YYCT_DEDU_DAY) AS NONCOM_CNT --비승인연차일수
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
--AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND A.WORK_PLAN_YYMMDD NOT IN ( SELECT T2.UPD_DATE
FROM SX_GW0090 T1
INNER JOIN SX_GW0110 T2 ON (T1.CORP_NO = T2.CORP_NO
AND T1.APRVL_DOC_ID = T2.APRVL_DOC_ID )
WHERE T1.APRVL_STUS_CD = '0003' --결재완료
AND T1.CORP_NO = A.CORP_NO
AND T1.APLNT_ID = A.USR_ID
)
GROUP BY
A.CORP_NO,
A.USR_ID
) T31
ON( 1=1
AND T31.CORP_NO = A.CORP_NO
AND T31.USR_ID = A.USR_ID
)
--출퇴근 미기록 일수
--출퇴근 미기록은 연차(V), '병', 특별휴무(X), 예비군훈련(Y)은 제외한 일자만 표시 한다.
LEFT OUTER JOIN (
SELECT CORP_NO
, USR_ID
, COUNT('A') ABSENT_CNT
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
AND PLAN_WORK_CD >= 'A'
AND PLAN_WORK_CD <= 'T'
AND WORK_START_DT IS NULL
AND WORK_END_DT IS NULL
AND ISNULL(WORK_CD, PLAN_WORK_CD) NOT IN ('W', 'V', '병', 'X', 'Y', 'U') --정기휴일, 연차, 병가, 특별휴무, 예비군훈련은 제외한다.(20170425추가)
GROUP BY CORP_NO, USR_ID
) T6
ON (1=1
AND T6.CORP_NO = A.CORP_NO
AND T6.USR_ID = A.USR_ID
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND (NULLIF(@USR_NM, '') IS NULL OR A.USR_NM LIKE CONCAT('%', @USR_NM, '%'))
AND (NULLIF(@TEAM_CD, '') IS NULL OR A.TEAM_CD = @TEAM_CD)
AND (@RETIRE_YN = 'Y' OR NULLIF(A.RETIREMENT_DATE, '') IS NULL)
--ordering을 parameter를 넘겨서 처리 가능
ORDER BY CASE WHEN @ORDER_TYPE = 'USR_NM' THEN ISNULL(USR_NM, '')
WHEN @ORDER_TYPE = 'DUTY_CD' THEN ISNULL(DUTY_CD, '')
WHEN @ORDER_TYPE = 'BZ_CD' THEN ISNULL(BZ_CD, '')
WHEN @ORDER_TYPE = 'TEAM_CD' THEN ISNULL(TEAM_CD, '')
--WHEN @ORDER_TYPE = 'SORT_ODR' THEN ISNULL(SORT_ODR, 0) --
ELSE ''
END
, B.SORT_ODR, ORDER_2
--ORDER BY
-- <if test='user_order_by == "SORT_ODR"'>
-- B.${user_order_by},
-- </if>
-- <if test='user_order_by != "SORT_ODR"'>
-- A.${user_order_by},
-- </if>
-- ORDER_2
-- </select>
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_holiday_management_detail_DocID_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-20
-- Create Description : 스카이브릿지 연차등관리 상세 화면 조회-문서ID 추가
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_holiday_management_detail_select '1111','00001' , '2017'
-- exec pr_skybridge_gw_holiday_management_detail_select '1111','10004' , '2017'
-- exec pr_skybridge_gw_holiday_management_detail_select '1111','10216' , '2017'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_holiday_management_detail_DocID_select]
@CORP_NO VARCHAR(15) --회사번호
, @USR_ID VARCHAR(10) --사용자ID
, @YYVCT_YY VARCHAR(4) --연차년도
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 신규 작성
SELECT T1.CORP_NO --회사번호
, T1.USR_ID --사용자ID
, T1.USR_NM -- 이름
-- , ISNULL(T2.YYVCT_CNT, 0) AS YYVCT_CNT -- 총연차일수
--, ISNULL(COM_CNT,0) AS COM_CNT --승인연차일수
, ISNULL(COM_UPD_DATE,0) AS COM_UPD_DATE --승인연차일
--, ISNULL(NONCOM_CNT,0) AS NONCOM_CNT --비승인연차일수
, NONCOM_UPD_DATE AS NONCOM_UPD_DATE --비승인연차일자
--, ISNULL(ABSENT_CNT, 0) AS ABSENT_CNT --출퇴근미기록일수
--, ISNULL(ABSENT_DATE,0) AS ABSENT_DATE --출퇴근미기록일자
--, ISNULL(TG_CNT,0) AS TG_CNT --퇴근 미기록 일수
--, TG_DATE AS TG_DATE --퇴근미기록일자
--, ISNULL(SPECIAL_CNT,0) AS SPECIAL_CNT --병가특별휴가일수
--, ISNULL(SPECIAL_DATE,0) AS SPECIAL_DATE --병가특별휴가일자
-- , CONVERT(VARCHAR,ISNULL(T2.YYVCT_CNT, 0) - (ISNULL(COM_CNT,0) + ISNULL(NONCOM_CNT,0) + ISNULL(ABSENT_CNT, 0))) + ' = ' +
-- CONVERT(VARCHAR,ISNULL(T2.YYVCT_CNT, 0)) + ' - ( ' +
--CONVERT(VARCHAR,ISNULL(COM_CNT,0)) + ' + ' +
--CONVERT(VARCHAR,ISNULL(NONCOM_CNT,0))+ ' + ' +
--CONVERT(VARCHAR,ISNULL(ABSENT_CNT,0)) + ')' AS REAMIN_HOLIDAY --잔여휴가 = 총연차일 - (승인연차 + 비승인연차 + 출퇴근미기록)
FROM (SELECT *
FROM SX_GW0010 --사용자
WHERE 1=1
AND CORP_NO = @CORP_NO
AND USR_ID = @USR_ID
) T1
LEFT OUTER JOIN SX_GW0060 T2 --연차관리
ON ( 1=1
AND T2.CORP_NO = T1.CORP_NO
AND T2.USR_ID = T1.USR_ID
AND T2.YYVCT_YY = @YYVCT_YY
)
LEFT OUTER JOIN (
SELECT A.CORP_NO
, A.APLNT_ID AS USR_ID
-- , SUM(CASE WHEN UPD_WORK_CD IN ('V') THEN 1 --연차면 1일,
-- WHEN UPD_WORK_CD IN ('H', 'I', 'J') THEN 0.5 --반차면 0.5일을 뺀다
--ELSE 0 END) AS COM_CNT --승인연차일수
FROM SX_GW0090 A
LEFT OUTER JOIN SX_GW0110 C
ON( 1=1
AND C.CORP_NO = A.CORP_NO
AND C.APRVL_DOC_ID = A.APRVL_DOC_ID
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND SUBSTRING(C.UPD_DATE,1,4) = @YYVCT_YY --해당년도의 연차만 조회
AND A.APRVL_KIND_CD = '0005' --연차등만 조회
AND A.CORP_NO + '!' + CONVERT(VARCHAR,A.APRVL_DOC_ID) IN (SELECT CORP_NO + '!' + CONVERT(VARCHAR,APRVL_DOC_ID)
FROM SX_GW0100
WHERE APPR_STUS_CD = '0003' --승인 완료
AND RGSTR_ID = @USR_ID )
GROUP BY A.CORP_NO, A.APLNT_ID
) T3
ON (1=1
AND T3.CORP_NO = T1.CORP_NO
AND T3.USR_ID = T1.USR_ID
)
LEFT OUTER JOIN (
SELECT A.CORP_NO
, A.APLNT_ID AS USR_ID
, SUM(CASE WHEN UPD_WORK_CD IN ('V') THEN 1 --연차면 1일,
WHEN UPD_WORK_CD IN ('H', 'I', 'J') THEN 0.5 --반차면 0.5일을 뺀다
ELSE 0 END) AS NONCOM_CNT --비승인연차일수
FROM SX_GW0090 A
LEFT OUTER JOIN SX_GW0110 C
ON( 1=1
AND C.CORP_NO = A.CORP_NO
AND C.APRVL_DOC_ID = A.APRVL_DOC_ID
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND SUBSTRING(C.UPD_DATE,1,4) = @YYVCT_YY --해당년도의 연차만 조회
AND A.APRVL_KIND_CD = '0005' --연차등만 조회
AND A.CORP_NO + '!' + CONVERT(VARCHAR,A.APRVL_DOC_ID) IN (SELECT CORP_NO + '!' + CONVERT(VARCHAR,APRVL_DOC_ID)
FROM SX_GW0100
WHERE APPR_STUS_CD = '0002' --결재중
AND RGSTR_ID = @USR_ID )
GROUP BY A.CORP_NO, A.APLNT_ID
) T31
ON (1=1
AND T31.CORP_NO = T1.CORP_NO
AND T31.USR_ID = T1.USR_ID
)
--승인완료 연차 일자 : 일1, 월2, 화3, 수4, 목5, 금6, 토7
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,UPD_DATE), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 7 THEN '토요일'
END
FROM SX_GW0110 AS TA INNER JOIN SX_GW0090 AS TB
ON TB.CORP_NO = TA.CORP_NO
AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.UPD_WORK_CD IN ('V', 'H', 'I', 'J')
AND SUBSTRING(TA.UPD_DATE,1,4) = @YYVCT_YY --당해년도
AND TB.APRVL_KIND_CD = '0005' --결재문서가 '연차등'인 경우
AND TA.CORP_NO + '!' + CONVERT(VARCHAR,TA.APRVL_DOC_ID) IN (SELECT CORP_NO + '!' + CONVERT(VARCHAR,APRVL_DOC_ID)
FROM SX_GW0100
WHERE APPR_STUS_CD = '0003' --승인 완료
AND RGSTR_ID = @USR_ID )
ORDER BY UPD_DATE
FOR XML PATH('')
),1,10,'') AS COM_UPD_DATE
FROM SX_GW0110 AS P
) T4
ON( 1=1
AND T4.CORP_NO = T1.CORP_NO
)
--비승인 연차 일자
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
, STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,UPD_DATE), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 7 THEN '토요일'
END
FROM SX_GW0110 AS TA INNER JOIN SX_GW0090 AS TB
ON TB.CORP_NO = TA.CORP_NO
AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.UPD_WORK_CD IN ('V', 'H', 'I', 'J')
AND SUBSTRING(TA.UPD_DATE,1,4) = @YYVCT_YY --당해년도
AND TB.APRVL_KIND_CD = '0005' --결재문서가 '연차등'인 경우
AND TA.CORP_NO + '!' + CONVERT(VARCHAR,TA.APRVL_DOC_ID) IN (SELECT CORP_NO + '!' + CONVERT(VARCHAR,APRVL_DOC_ID)
FROM SX_GW0100
WHERE APPR_STUS_CD = '0002' --결재중
AND RGSTR_ID = @USR_ID )
ORDER BY UPD_DATE
FOR XML PATH('')
),1,10,'') AS NONCOM_UPD_DATE
FROM SX_GW0110
) T5
ON( 1=1
AND T5.CORP_NO = T1.CORP_NO
)
--출퇴근 미기록 일수
LEFT OUTER JOIN (
SELECT CORP_NO
, USR_ID
, COUNT('A') ABSENT_CNT
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
AND PLAN_WORK_CD >= 'A'
AND PLAN_WORK_CD <= 'T'
AND WORK_START_DT IS NULL
AND WORK_END_DT IS NULL
GROUP BY CORP_NO, USR_ID
) T6
ON (1=1
AND T6.CORP_NO = T1.CORP_NO
AND T6.USR_ID = T1.USR_ID
)
--출퇴근 미기록 일자
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + PLAN_WORK_CD + ')'
FROM SX_GW0050
WHERE CORP_NO + '!' + USR_ID + '!' + WORK_PLAN_YYMMDD IN
( SELECT CORP_NO + '!' + USR_ID + '!' + WORK_PLAN_YYMMDD
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
AND PLAN_WORK_CD >= 'A'
AND PLAN_WORK_CD <= 'T'
AND WORK_START_DT IS NULL
AND WORK_END_DT IS NULL
AND USR_ID = @USR_ID
)
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS ABSENT_DATE
FROM SX_GW0050 AS P
) T61
ON( 1=1
AND T61.CORP_NO = T1.CORP_NO
)
--퇴근 미기록 일수
LEFT OUTER JOIN (
SELECT CORP_NO
, USR_ID
, COUNT('A') TG_CNT
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND PLAN_WORK_CD >= 'A'
AND PLAN_WORK_CD <= 'T'
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
AND WORK_START_DT IS NOT NULL
AND WORK_END_DT IS NULL
GROUP BY CORP_NO, USR_ID
) T7
ON (1=1
AND T7.CORP_NO = T1.CORP_NO
AND T7.USR_ID = T1.USR_ID
)
--퇴근 미기록 일자
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + PLAN_WORK_CD + ')'
FROM SX_GW0050
WHERE CORP_NO + '!' + USR_ID+ '!' + WORK_PLAN_YYMMDD IN
( SELECT CORP_NO + '!' + USR_ID+ '!' + WORK_PLAN_YYMMDD
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND PLAN_WORK_CD >= 'A'
AND PLAN_WORK_CD <= 'T'
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
AND WORK_START_DT IS NOT NULL
AND WORK_END_DT IS NULL
AND USR_ID = @USR_ID
)
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS TG_DATE
FROM SX_GW0050 AS P
) T71
ON (1=1
AND T71.CORP_NO = T1.CORP_NO
)
--병가, 특별휴가 일수
LEFT OUTER JOIN (
SELECT CORP_NO
, RGSTR_ID AS USR_ID
, COUNT('A') SPECIAL_CNT
FROM SX_GW0110
WHERE SUBSTRING(UPD_DATE,1,4) = @YYVCT_YY
AND UPD_WORK_CD IN ('병', 'X', 'Y', 'Z')
AND CORP_NO + '!' + CONVERT(VARCHAR, APRVL_DOC_ID) IN (SELECT CORP_NO + '!' + CONVERT(VARCHAR,APRVL_DOC_ID)
FROM SX_GW0100
WHERE APPR_STUS_CD = '0003' --승인 완료
AND RGSTR_ID = @USR_ID )
GROUP BY CORP_NO, RGSTR_ID
) T8
ON (1=1
AND T8.CORP_NO = T1.CORP_NO
AND T8.USR_ID = T1.USR_ID
)
--병가, 특별휴가 일자
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,UPD_DATE), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 7 THEN '토요일'
END + ' (' + UPD_WORK_CD + ')'
FROM SX_GW0110
WHERE CORP_NO + '!' + RGSTR_ID + '!' + UPD_DATE IN
( SELECT CORP_NO + '!' + RGSTR_ID + '!' + UPD_DATE
FROM SX_GW0110
WHERE SUBSTRING(UPD_DATE,1,4) = @YYVCT_YY
AND UPD_WORK_CD IN ('병', 'X', 'Y', 'Z')
AND RGSTR_ID = @USR_ID
)
AND CORP_NO + '!' + CONVERT(VARCHAR, APRVL_DOC_ID) IN (SELECT CORP_NO + '!' + CONVERT(VARCHAR,APRVL_DOC_ID)
FROM SX_GW0100
WHERE APPR_STUS_CD = '0003' --승인 완료
AND RGSTR_ID = @USR_ID )
ORDER BY UPD_DATE
FOR XML PATH('')
),1,10,'') AS SPECIAL_DATE
FROM SX_GW0110 AS P
) T81
ON (1=1
AND T81.CORP_NO = T1.CORP_NO
)
WHERE 1=1
AND T1.CORP_NO = @CORP_NO
AND T1.USR_ID = @USR_ID
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_holiday_management_detail_select_id] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-20
-- Create Description : 스카이브릿지 연차등관리에서 일자를 가지고 결재문서 ID 조회하기
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_holiday_management_detail_select_id '1111','00001' , '20170301', '승인연차'
-- exec pr_skybridge_gw_holiday_management_detail_select_id '1111','10004' , '20170301', '비승인연차'
-- exec pr_skybridge_gw_holiday_management_detail_select_id '1111','10216' , '20170301',
-- exec pr_skybridge_gw_holiday_management_detail_select_id '1111','10027' , '20170301',
-- exec pr_skybridge_gw_holiday_management_detail_select_id '1111','00025' , '20170301',
-- select * from SX_GW0010 where USR_NM like '안' + '%'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_holiday_management_detail_select_id]
@CORP_NO VARCHAR(15) --회사번호
, @USR_ID VARCHAR(10) --사용자ID
, @APRVL_DATE VARCHAR(8) --결재일자
, @GUBUN VARCHAR(50) --구분 : 승인연차, 비승인연차, 지각, 조퇴, 외출
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @GUBUN IN ('승인연차')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0110 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
AND TB.APRVL_STUS_CD = '0003' --결재완료
AND TB.APRVL_KIND_CD = '0005' --결재종류 : 연차등
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.UPD_DATE = @APRVL_DATE
ELSE IF @GUBUN IN ('비승인연차')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0110 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
AND TB.APRVL_STUS_CD = '0002' --결재중
AND TB.APRVL_KIND_CD = '0005' --결재종류 : 연차등
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.UPD_DATE = @APRVL_DATE
ELSE IF @GUBUN IN ('병가/특별휴가')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0110 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
AND TB.APRVL_STUS_CD = '0003' --결재완료
AND TB.APRVL_KIND_CD = '0005' --결재종류 : 연차등
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.UPD_DATE = @APRVL_DATE
ELSE IF @GUBUN IN ('지각A')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0110 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
AND TB.APRVL_STUS_CD = '0003' --결재완료
AND TB.APRVL_KIND_CD = '0002' --결재종류 : 지각
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.UPD_DATE = @APRVL_DATE
ELSE IF @GUBUN IN ('조퇴A')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0110 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
AND TB.APRVL_STUS_CD = '0003' --결재완료
AND TB.APRVL_KIND_CD = '0003' --결재종류 : 조퇴
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.UPD_DATE = @APRVL_DATE
ELSE IF @GUBUN IN ('외출A')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0110 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
AND TB.APRVL_STUS_CD = '0003' --결재완료
AND TB.APRVL_KIND_CD = '0008' --결재종류 : 외출
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.UPD_DATE = @APRVL_DATE
ELSE IF @GUBUN IN ('지각')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0050 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APLNT_ID = TA.RGSTR_ID
AND TB.APRVL_STUS_CD IN ('0001', '0002', '0003')
AND TB.APRVL_KIND_CD = '0002' --결재종류 : 지각
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.WORK_PLAN_YYMMDD = @APRVL_DATE
ELSE IF @GUBUN IN ('조퇴')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0050 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APLNT_ID = TA.RGSTR_ID
AND TB.APRVL_STUS_CD IN ('0001', '0002', '0003')
AND TB.APRVL_KIND_CD = '0003' --결재종류 : 조퇴
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.WORK_PLAN_YYMMDD = @APRVL_DATE
ELSE IF @GUBUN IN ('외출')
SELECT MAX(TB.APRVL_DOC_ID) AS APRVL_DOC_ID
FROM SX_GW0050 TA
INNER JOIN SX_GW0090 TB
ON (1=1
AND TB.CORP_NO = TA.CORP_NO
AND TB.APLNT_ID = TA.RGSTR_ID
AND TB.APRVL_STUS_CD IN ('0001', '0002', '0003')
AND TB.APRVL_KIND_CD = '0008' --결재종류 : 외출
AND TB.APLNT_ID = @USR_ID
)
WHERE 1=1
AND TA.CORP_NO = @CORP_NO
AND TA.RGSTR_ID = @USR_ID
AND TA.WORK_PLAN_YYMMDD = @APRVL_DATE
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_holiday_management_detail_select_new] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-20
-- Create Description : 스카이브릿지 연차등관리 상세 화면 조회- 수정
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_holiday_management_detail_select_new '1111','00001' , '2017'
-- exec pr_skybridge_gw_holiday_management_detail_select_new '1111','10004' , '2017'
-- exec pr_skybridge_gw_holiday_management_detail_select_new '1111','10216' , '2017'
-- exec pr_skybridge_gw_holiday_management_detail_select_new '1111','10027' , '2017'
-- exec pr_skybridge_gw_holiday_management_detail_select_new '1111','00025' , '2017'
--exec pr_skybridge_gw_holiday_management_detail_select_new '1111','00038' , '2020'
--select * from SX_GW0010 where USR_NM = '조유진'
-- select * from SX_GW0010 where USR_NM like '안' + '%'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_holiday_management_detail_select_new]
@CORP_NO VARCHAR(15) --회사번호
, @USR_ID VARCHAR(10) --사용자ID
, @YYVCT_YY VARCHAR(4) --연차년도
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 신규 작성
SELECT T1.CORP_NO --회사번호
, T1.USR_ID --사용자ID
, T1.USR_NM -- 이름
, ISNULL(T11.YYVCT_CNT, 0) AS YYVCT_CNT -- 총연차일수
, ISNULL(COM_CNT,0) AS COM_CNT --승인연차일수
, ISNULL(COM_UPD_DATE,0) AS COM_UPD_DATE --승인연차일
, ISNULL(NONCOM_CNT,0) AS NONCOM_CNT --비승인연차일수
, NONCOM_UPD_DATE AS NONCOM_UPD_DATE --비승인연차일자
, ISNULL(ABSENT_CNT, 0) AS ABSENT_CNT --출퇴근미기록일수
, ISNULL(ABSENT_DATE,0) AS ABSENT_DATE --출퇴근미기록일자
, ISNULL(TG_CNT,0) AS TG_CNT --퇴근 미기록 일수
, TG_DATE AS TG_DATE --퇴근미기록일자
, ISNULL(T2.SPECIAL_CNT,0) AS SPECIAL_CNT --병가특별휴가일수
, ISNULL(T81.SPECIAL_DATE,0) AS SPECIAL_DATE --병가특별휴가일자
, T2.LATE_DCNT AS LATE_DCNT --지각횟수
, LATE_DATE --지각일자
, T2.SKIPOFF_DCNT AS SKIPOFF_DCNT --조퇴횟수
, SKIPOFF_DATE--조퇴일자
, T2.OUTING_CNT AS OUTING_CNT --외출횟수
, OUTING_DATE--외출일자
, CONVERT(VARCHAR,ISNULL(T11.YYVCT_CNT, 0) - (ISNULL(COM_CNT,0) + ISNULL(NONCOM_CNT,0) + ISNULL(ABSENT_CNT, 0))) + ' = ' +
CONVERT(VARCHAR,ISNULL(T11.YYVCT_CNT, 0)) + ' - ( ' +
CONVERT(VARCHAR,ISNULL(COM_CNT,0)) + ' + ' +
CONVERT(VARCHAR,ISNULL(NONCOM_CNT,0))+ ' + ' +
CONVERT(VARCHAR,ISNULL(ABSENT_CNT,0)) + ')' AS REAMIN_HOLIDAY --잔여휴가 = 총연차일 - (승인연차 + 비승인연차 + 출퇴근미기록)
FROM (SELECT *
FROM SX_GW0010 --사용자
WHERE 1=1
AND CORP_NO = @CORP_NO
AND USR_ID = @USR_ID
) T1
LEFT OUTER JOIN
--INNER JOIN
( SELECT *
FROM SX_GW0060 --총연차
WHERE 1=1
AND CORP_NO = @CORP_NO
AND USR_ID = @USR_ID
AND YYVCT_YY = @YYVCT_YY
) T11
ON (1=1
AND T11.CORP_NO = T1.CORP_NO
AND T11.USR_ID = T1.USR_ID
)
--승인 연차
LEFT OUTER JOIN (
--INNER JOIN (
SELECT A.CORP_NO,
A.USR_ID,
--SUM(CASE WHEN ISNULL(NULLIF(A.ABTI_YN, ''), 'N') != 'Y' THEN B.APLY_WORK_DAY END) AS WORK_DCNT, --근무
SUM(B.YYCT_DEDU_DAY) AS COM_CNT, --YYCT_DEDU_DCNT, --연차사용일수
--COUNT(CASE WHEN ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) = '결' OR A.ABTI_YN = 'Y' THEN 1 END) AS ABTI_DCNT, --결근
COUNT(CASE WHEN A.LATE_MIN > 0 THEN 1 END) AS LATE_DCNT, --지각
--조퇴는 오후반차(H), 야근반차(J) 인경우 제외한다(20170425)
COUNT(CASE WHEN A.SKIPOFF_REMN_BZ_MIN > 0 AND ISNULL(A.WORK_CD, A.PLAN_WORK_CD) NOT IN ('H', 'J') THEN 1 END) AS SKIPOFF_DCNT, --조퇴
SUM(A.OUTING_CNT) AS OUTING_CNT, --외출횟수
SUM(A.OUTING_MIN) AS OUTING_MIN, --외출시간(분)
COUNT(CASE WHEN ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) IN ('병', 'X', 'Y') THEN 1 END) AS SPECIAL_CNT --SICKLEAVE_DCNT --병가, 특별휴가 일수
--SUM(A.TOT_WORK_MIN) AS TOT_WORK_MIN,
--SUM(A.LATE_MIN) AS LATE_MIN,
--SUM(A.SKIPOFF_REMN_BZ_MIN) AS SKIPOFF_REMN_BZ_MIN,
--SUM(A.INCLU_WORK_OT_MIN) AS INCLU_WORK_OT_MIN,
--SUM(A.OT_WORK_MIN) AS OT_WORK_MIN,
--SUM(A.NGT_OT_MIN) AS NGT_OT_MIN
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND A.WORK_PLAN_YYMMDD IN ( SELECT T2.UPD_DATE
FROM SX_GW0090 T1
INNER JOIN SX_GW0110 T2 ON (T1.CORP_NO = T2.CORP_NO
AND T1.APRVL_DOC_ID = T2.APRVL_DOC_ID )
WHERE T1.APRVL_STUS_CD = '0003' --결재완료
AND T1.CORP_NO = @CORP_NO
AND T1.APLNT_ID = @USR_ID
)
GROUP BY
A.CORP_NO,
A.USR_ID
) T2
ON( 1=1
AND T2.CORP_NO = T1.CORP_NO
AND T2.USR_ID = T1.USR_ID
)
--승인완료 연차 일자 : 일1, 월2, 화3, 수4, 목5, 금6, 토7
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) + ')'
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
--AND B.WORK_CD IN ('V', 'H', 'I', 'J') --연차, 반차
AND B.WORK_CD IN(SELECT WORK_CD FROM SX_CO0070 WHERE ISNULL(YYCT_DEDU_DAY, 0) > 0)
AND A.WORK_PLAN_YYMMDD IN ( SELECT T2.UPD_DATE
FROM SX_GW0090 T1
INNER JOIN SX_GW0110 T2 ON (T1.CORP_NO = T2.CORP_NO
AND T1.APRVL_DOC_ID = T2.APRVL_DOC_ID )
WHERE T1.APRVL_STUS_CD = '0003' --결재완료
AND T1.CORP_NO = @CORP_NO
AND T1.APLNT_ID = @USR_ID
)
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS COM_UPD_DATE
FROM SX_CO0070 AS P
) T4
ON( 1=1
AND T4.CORP_NO = T1.CORP_NO
)
--비승인 연차
LEFT OUTER JOIN (
--INNER JOIN (
SELECT A.CORP_NO,
A.USR_ID,
--SUM(CASE WHEN ISNULL(NULLIF(A.ABTI_YN, ''), 'N') != 'Y' THEN B.APLY_WORK_DAY END) AS WORK_DCNT, --근무
SUM(B.YYCT_DEDU_DAY) AS NONCOM_CNT , --비승인연차일수
--COUNT(CASE WHEN ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) = '결' OR A.ABTI_YN = 'Y' THEN 1 END) AS ABTI_DCNT, --결근
COUNT(CASE WHEN A.LATE_MIN > 0 THEN 1 END) AS LATE_DCNT, --지각
--조퇴는 오후반차(H), 야근반차(J) 인경우 제외한다(20170425)
COUNT(CASE WHEN A.SKIPOFF_REMN_BZ_MIN > 0 AND ISNULL(A.WORK_CD, A.PLAN_WORK_CD) NOT IN ('H', 'J') THEN 1 END) AS SKIPOFF_DCNT, --조퇴
SUM(A.OUTING_CNT) AS OUTING_CNT, --외출횟수
SUM(A.OUTING_MIN) AS OUTING_MIN, --외출시간(분)
COUNT(CASE WHEN ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) IN ('병', 'X', 'Y') THEN 1 END) AS SPECIAL_CNT --SICKLEAVE_DCNT --병가, 특별휴가 일수
--SUM(A.TOT_WORK_MIN) AS TOT_WORK_MIN,
--SUM(A.LATE_MIN) AS LATE_MIN,
--SUM(A.SKIPOFF_REMN_BZ_MIN) AS SKIPOFF_REMN_BZ_MIN,
--SUM(A.INCLU_WORK_OT_MIN) AS INCLU_WORK_OT_MIN,
--SUM(A.OT_WORK_MIN) AS OT_WORK_MIN,
--SUM(A.NGT_OT_MIN) AS NGT_OT_MIN
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND A.WORK_PLAN_YYMMDD NOT IN ( SELECT T2.UPD_DATE
FROM SX_GW0090 T1
INNER JOIN SX_GW0110 T2 ON (T1.CORP_NO = T2.CORP_NO
AND T1.APRVL_DOC_ID = T2.APRVL_DOC_ID )
WHERE T1.APRVL_STUS_CD = '0003' --결재완료
AND T1.CORP_NO = @CORP_NO
AND T1.APLNT_ID = @USR_ID
)
GROUP BY
A.CORP_NO,
A.USR_ID
) T31
ON( 1=1
AND T31.CORP_NO = T1.CORP_NO
AND T31.USR_ID = T1.USR_ID
)
--비승인연차 일자 : 일1, 월2, 화3, 수4, 목5, 금6, 토7
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) + ')'
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
--AND B.WORK_CD IN ('V', 'H', 'I', 'J') --연차, 반차
AND B.WORK_CD IN(SELECT WORK_CD FROM SX_CO0070 WHERE ISNULL(YYCT_DEDU_DAY, 0) > 0)
AND A.WORK_PLAN_YYMMDD NOT IN ( SELECT T2.UPD_DATE
FROM SX_GW0090 T1
INNER JOIN SX_GW0110 T2 ON (T1.CORP_NO = T2.CORP_NO
AND T1.APRVL_DOC_ID = T2.APRVL_DOC_ID )
WHERE T1.APRVL_STUS_CD = '0003' --결재완료
AND T1.CORP_NO = @CORP_NO
AND T1.APLNT_ID = @USR_ID
)
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS NONCOM_UPD_DATE
FROM SX_CO0070 AS P
) T5
ON( 1=1
AND T4.CORP_NO = T1.CORP_NO
)
-- --비승인 연차 일수 - OLD
-- LEFT OUTER JOIN (
-- SELECT A.CORP_NO
-- , A.APLNT_ID AS USR_ID
-- , SUM(CASE WHEN UPD_WORK_CD IN ('V') THEN 1 --연차면 1일,
-- WHEN UPD_WORK_CD IN ('H', 'I', 'J') THEN 0.5 --반차면 0.5일을 뺀다
-- ELSE 0 END) AS NONCOM_CNT --비승인연차일수
-- FROM SX_GW0090 A
-- LEFT OUTER JOIN SX_GW0110 C
-- ON( 1=1
-- AND C.CORP_NO = A.CORP_NO
-- AND C.APRVL_DOC_ID = A.APRVL_DOC_ID
-- )
-- WHERE 1=1
-- AND A.CORP_NO = @CORP_NO
-- AND A.APLNT_ID = @USR_ID
-- AND SUBSTRING(C.UPD_DATE,1,4) = @YYVCT_YY --해당년도의 연차만 조회
-- AND A.APRVL_KIND_CD = '0005' --연차등만 조회
-- AND A.APRVL_STUS_CD = '0002' --결재중
-- GROUP BY A.CORP_NO, A.APLNT_ID
-- ) T31
--ON (1=1
-- AND T31.CORP_NO = T1.CORP_NO
-- AND T31.USR_ID = T1.USR_ID
-- )
-- --비승인 연차 일자
-- LEFT OUTER JOIN (
-- SELECT DISTINCT CORP_NO
-- , STUFF((
-- SELECT '<br>' +
-- CONVERT(VARCHAR, CONVERT(DATETIME,UPD_DATE), 23) + ' ' +
-- CASE WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 1 THEN '일요일'
-- WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 2 THEN '월요일'
-- WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 3 THEN '화요일'
-- WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 4 THEN '수요일'
-- WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 5 THEN '목요일'
-- WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 6 THEN '금요일'
-- WHEN DATEPART (DW, CONVERT(DATETIME,UPD_DATE)) = 7 THEN '토요일'
-- END + ' (' + ISNULL(NULLIF(UPD_WORK_CD, ''), UPD_BF_WORK_CD) + ')'
-- FROM SX_GW0110 AS TA INNER JOIN SX_GW0090 AS TB
-- ON TB.CORP_NO = TA.CORP_NO
-- AND TB.APRVL_DOC_ID = TA.APRVL_DOC_ID
-- WHERE 1=1
-- AND TA.CORP_NO = @CORP_NO
-- AND TB.APLNT_ID = @USR_ID
-- AND TA.UPD_WORK_CD IN ('V', 'H', 'I', 'J')
-- AND SUBSTRING(TA.UPD_DATE,1,4) = @YYVCT_YY --당해년도
-- AND TB.APRVL_KIND_CD = '0005' --결재문서가 '연차등'인 경우
-- AND TB.APRVL_STUS_CD = '0002' --결재중
-- ORDER BY UPD_DATE
-- FOR XML PATH('')
-- ),1,10,'') AS NONCOM_UPD_DATE
-- FROM SX_GW0110
-- ) T5
-- ON( 1=1
-- AND T5.CORP_NO = T1.CORP_NO
-- )
--출퇴근 미기록 일수
--출퇴근 미기록은 연차(V), '병', 특별휴무(X), 예비군훈련(Y)은 제외한 일자만 표시 한다.
LEFT OUTER JOIN (
SELECT CORP_NO
, USR_ID
, COUNT('A') ABSENT_CNT
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
/*
AND PLAN_WORK_CD >= 'A'
AND PLAN_WORK_CD <= 'T'
*/
AND ISNULL(WORK_CD, PLAN_WORK_CD) >= 'A'
AND ISNULL(WORK_CD, PLAN_WORK_CD) <= 'T'
AND WORK_START_DT IS NULL
AND WORK_END_DT IS NULL
AND ISNULL(WORK_CD, PLAN_WORK_CD) NOT IN ('V', '병', 'X', 'Y') --연차, 병가, 특별휴무, 예비군훈련은 제외한다.(20170425추가)
GROUP BY CORP_NO, USR_ID
) T6
ON (1=1
AND T6.CORP_NO = T1.CORP_NO
AND T6.USR_ID = T1.USR_ID
)
--출퇴근 미기록 일자
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' +
CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + PLAN_WORK_CD + ')'
FROM SX_GW0050
WHERE CORP_NO + '!' + USR_ID + '!' + WORK_PLAN_YYMMDD IN
( SELECT CORP_NO + '!' + USR_ID + '!' + WORK_PLAN_YYMMDD
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
AND ISNULL(WORK_CD, PLAN_WORK_CD) >= 'A'
AND ISNULL(WORK_CD, PLAN_WORK_CD) <= 'T'
AND WORK_START_DT IS NULL
AND WORK_END_DT IS NULL
AND USR_ID = @USR_ID
AND ISNULL(WORK_CD, PLAN_WORK_CD) NOT IN ('V', '병', 'X', 'Y') --연차, 병가, 특별휴무, 예비군훈련은 제외한다.(20170425추가)
)
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS ABSENT_DATE
FROM SX_GW0050 AS P
) T61
ON( 1=1
AND T61.CORP_NO = T1.CORP_NO
)
--퇴근 미기록 일수
LEFT OUTER JOIN (
SELECT CORP_NO
, USR_ID
, COUNT('A') TG_CNT
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND PLAN_WORK_CD >= 'A'
AND PLAN_WORK_CD <= 'T'
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
AND WORK_START_DT IS NOT NULL
AND WORK_END_DT IS NULL
GROUP BY CORP_NO, USR_ID
) T7
ON (1=1
AND T7.CORP_NO = T1.CORP_NO
AND T7.USR_ID = T1.USR_ID
)
--퇴근 미기록 일자
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + PLAN_WORK_CD + ')'
FROM SX_GW0050
WHERE CORP_NO + '!' + USR_ID+ '!' + WORK_PLAN_YYMMDD IN
( SELECT CORP_NO + '!' + USR_ID+ '!' + WORK_PLAN_YYMMDD
FROM SX_GW0050
WHERE SUBSTRING(WORK_PLAN_YYMMDD,1,4) = @YYVCT_YY
AND PLAN_WORK_CD >= 'A'
AND PLAN_WORK_CD <= 'T'
AND WORK_PLAN_YYMMDD < CONVERT(VARCHAR, GETDATE(), 112)--오늘일자 보다 하루 적은 날자로 가져온다.
AND WORK_START_DT IS NOT NULL
AND WORK_END_DT IS NULL
AND USR_ID = @USR_ID
)
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS TG_DATE
FROM SX_GW0050 AS P
) T71
ON (1=1
AND T71.CORP_NO = T1.CORP_NO
)
--병가, 특별휴가 일자
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) + ')'
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND B.WORK_CD IN ('병', 'X', 'Y', 'Z')
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS SPECIAL_DATE
FROM SX_CO0070 AS P
) T81
ON (1=1
AND T81.CORP_NO = T1.CORP_NO
)
--지각 일자 : 일1, 월2, 화3, 수4, 목5, 금6, 토7
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) + ')'
FROM SX_GW0050 A
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND A.LATE_MIN > 0
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS LATE_DATE
FROM SX_GW0050 AS P
) T91
ON( 1=1
AND T91.CORP_NO = T1.CORP_NO
)
--조퇴 일자 : 일1, 월2, 화3, 수4, 목5, 금6, 토7
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) + ')'
FROM SX_GW0050 A
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND A.SKIPOFF_REMN_BZ_MIN > 0
AND ISNULL(A.WORK_CD, A.PLAN_WORK_CD) NOT IN ('H', 'J') --조퇴는 오후반차(H), 야근반차(J) 인경우 제외한다(20170425)
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS SKIPOFF_DATE
FROM SX_GW0050 AS P
) T92
ON( 1=1
AND T92.CORP_NO = T1.CORP_NO
)
--외출 일자 : 일1, 월2, 화3, 수4, 목5, 금6, 토7
LEFT OUTER JOIN (
SELECT DISTINCT CORP_NO
,STUFF((
SELECT '<br>' + CONVERT(VARCHAR, CONVERT(DATETIME,WORK_PLAN_YYMMDD), 23) + ' ' +
CASE WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 1 THEN '일요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 2 THEN '월요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 3 THEN '화요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 4 THEN '수요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 5 THEN '목요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 6 THEN '금요일'
WHEN DATEPART (DW, CONVERT(DATETIME,WORK_PLAN_YYMMDD)) = 7 THEN '토요일'
END + ' (' + ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) + ')'
FROM SX_GW0050 A
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND A.OUTING_CNT >= 1
ORDER BY WORK_PLAN_YYMMDD
FOR XML PATH('')
),1,10,'') AS OUTING_DATE
FROM SX_GW0050 AS P
) T93
ON( 1=1
AND T93.CORP_NO = T1.CORP_NO
)
WHERE 1=1
AND T1.CORP_NO = @CORP_NO
AND T1.USR_ID = @USR_ID
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_holiday_request_detail_select_new] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 연차등신청 상세 화면 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_holiday_request_detail_select_new '1111','10027' , '2017' --안순회씨
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_holiday_request_detail_select_new]
@CORP_NO VARCHAR(15) --회사번호
, @USR_ID VARCHAR(10) --사용자ID
, @YYVCT_YY VARCHAR(4) --연차년도
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
--T1.* ,
--T2.* ,
T1.CORP_NO
, T1.USR_ID
, T1.WORK_CD
, T1.FREE_CNT
, T1.WORK_PLAN_YYMMDD
, T2.APRVL_STUS_DS
, T2.APRVL_DOC_ID
, T2.APRVL_STUS_CD
, T2.APRVL_KIND_CD
, T2.OFFER_DT
, T2.CMPL_DT
, T2.APRVL_CMPL_SNO
, T2.FINAL_APRVL_SNO
--, T2.USER_ID
, T2.APLNT_NM
, T2.BZ_DEPUTY_NM
, T2.UPD_BF_WORK_CD
, ISNULL(T2.UPD_WORK_CD, WORK_CD) AS UPD_WORK_CD
, ISNULL(T2.APLNT_CN, '<<< 결재 필요 >>>') AS APLNT_CN
, T2.BZ_CN
, T2.OFFER_CN
, T2.UPD_DT
, T2.COMM_CD_NM
, ISNULL (T2.UPD_DATE,WORK_PLAN_YYMMDD) AS UPD_DATE
FROM (
SELECT A.CORP_NO,
A.USR_ID,
ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) AS WORK_CD,
ISNULL(B.YYCT_DEDU_DAY,0) AS FREE_CNT, --연차사용일수
WORK_PLAN_YYMMDD --연차일자
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) IN ('H', 'I', 'J', 'V', '병','U', 'X', 'Y')
) T1
left outer join
--inner JOIN
(
SELECT A.COMM_CD_NM AS APRVL_STUS_DS --결재
, A.*
FROM (
SELECT A.CORP_NO,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
A.OFFER_DT,
A.CMPL_DT,
A.APRVL_CMPL_SNO,
A.FINAL_APRVL_SNO,
A.APLNT_ID AS USR_ID,
U1.USR_NM AS APLNT_NM,
U4.USR_NM AS BZ_DEPUTY_NM,
U3.UPD_BF_WORK_CD,
U3.UPD_WORK_CD,
A.APLNT_CN ,
A.BZ_CN,
A.OFFER_CN,
A.UPD_DT,
COMM_CD_NM,
UPD_DATE
FROM (SELECT *
FROM SX_GW0090
WHERE CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) IN ( SELECT CORP_NO + '|' + CONVERT(VARCHAR, MAX(APRVL_DOC_ID))
FROM SX_GW0110
WHERE CORP_NO = @CORP_NO
AND RGSTR_ID = @USR_ID
GROUP BY CORP_NO, UPD_DATE
)
) A --같은날 중복으로 결재신청이 될수 있으므로 마지막으로 신청한 것만 사용
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = A.CORP_NO
AND U1.USR_ID = A.APLNT_ID
)
LEFT JOIN (SELECT CORP_NO
, APRVL_DOC_ID
, UPD_DATE AS UPD_DATE
, UPD_BF_WORK_CD AS UPD_BF_WORK_CD --변경전 근무 코드
, UPD_WORK_CD AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110
) U3
ON(1=1
AND U3.CORP_NO = A.CORP_NO
AND U3.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = A.BZ_DEPUTY_ID )
LEFT JOIN SX_CO0040 COMMON
ON (1=1
AND COMMON.CORP_NO = A.CORP_NO
AND COMMON.COMM_CL_CD = 'SX011' --결재상태코드
AND COMMON.COMM_CD = APRVL_STUS_CD )
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.APLNT_ID = @USR_ID
AND SUBSTRING(UPD_DATE,1,4) = @YYVCT_YY
--AND APRVL_STUS_CD IN ('0003') --결재완료
AND UPD_WORK_CD IN ('H', 'I', 'J', 'V', '병','U', 'X', 'Y')
) A
) T2 ON (1=1
AND T1.CORP_NO = T2.CORP_NO
AND T1.USR_ID = T2.USR_ID
AND T1.WORK_PLAN_YYMMDD = T2.UPD_DATE
)
ORDER BY T1.WORK_PLAN_YYMMDD
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_holiday_request_detail_select_total_new] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 연차등신청 상세 화면 합계 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_holiday_request_detail_select_total_new '1111','00001' , '2017'
-- exec pr_skybridge_gw_holiday_request_detail_select_total_new '1111','10004' , '2017'
-- exec pr_skybridge_gw_holiday_request_detail_select_total_new '1111','10027' , '2017'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_holiday_request_detail_select_total_new]
@CORP_NO VARCHAR(15) --회사번호
, @USR_ID VARCHAR(10) --사용자ID
, @YYVCT_YY VARCHAR(4) --연차년도
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 20170601 신규 작성 (pr_skybridge_gw_holiday_request_detail_select_new 과 같은 쿼리 사용
SELECT '총연차일 : ' + CONVERT(VARCHAR, ISNULL(YYVCT_CNT * 1.00,0)) + '일' AS YYVCT_CNT --총연차일
, '신청일수 : ' + CONVERT(VARCHAR, ISNULL(FREE_CNT,0)) + '일' AS SINCHUNG_CNT --신청일수
, '사용일수 : ' + CONVERT(VARCHAR, ISNULL(FREE_CNT,0)) + '일' AS USD_CNT --사용일수
, '특휴·병가등일수 : ' + CONVERT(VARCHAR, ISNULL(SPECIAL_CNT*1.00,0)) + '일' AS SPECIAL_CNT --특휴병가등일수
, '잔여연차 : ' + CONVERT(VARCHAR, ISNULL(YYVCT_CNT,0) - ISNULL(FREE_CNT,0)) + '일' AS REMAIN_CNT --잔여연차
FROM (
--pr_skybridge_gw_holiday_request_detail_select_new 과 같은 쿼리
SELECT
MAX(T1.CORP_NO) AS CORP_NO
, SUM(T1.FREE_CNT) AS FREE_CNT
, SUM(T1.SPECIAL_CNT) AS SPECIAL_CNT
FROM (
SELECT A.CORP_NO,
A.USR_ID,
ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) AS WORK_CD,
ISNULL(B.YYCT_DEDU_DAY,0) AS FREE_CNT, --연차사용일수
CASE WHEN ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) IN ('병', 'X', 'Y') THEN 1 ELSE 0 END AS SPECIAL_CNT,
WORK_PLAN_YYMMDD --연차일자
FROM SX_GW0050 A
LEFT JOIN SX_CO0070 B
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.USR_ID = @USR_ID
AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
AND ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) IN ('H', 'I', 'J', 'V', '병','U', 'X', 'Y')
) T1
left outer JOIN
(
SELECT A.COMM_CD_NM AS APRVL_STUS_DS --결재
, A.*
FROM (
SELECT A.CORP_NO,
A.APRVL_DOC_ID,
A.APRVL_STUS_CD,
A.APRVL_KIND_CD,
A.OFFER_DT,
A.CMPL_DT,
A.APRVL_CMPL_SNO,
A.FINAL_APRVL_SNO,
A.APLNT_ID AS USR_ID,
U1.USR_NM AS APLNT_NM,
U4.USR_NM AS BZ_DEPUTY_NM,
U3.UPD_BF_WORK_CD,
U3.UPD_WORK_CD,
A.APLNT_CN ,
A.BZ_CN,
A.OFFER_CN,
A.UPD_DT,
COMM_CD_NM,
UPD_DATE
FROM (SELECT *
FROM SX_GW0090
WHERE CORP_NO + '|' + CONVERT(VARCHAR, APRVL_DOC_ID) IN ( SELECT CORP_NO + '|' + CONVERT(VARCHAR, MAX(APRVL_DOC_ID))
FROM SX_GW0110
WHERE CORP_NO = @CORP_NO
AND RGSTR_ID = @USR_ID
GROUP BY CORP_NO, UPD_DATE
)
) A --같은날 중복으로 결재신청이 될수 있으므로 마지막으로 신청한 것만 사용
LEFT JOIN SX_GW0010 U1
ON(1=1
AND U1.CORP_NO = A.CORP_NO
AND U1.USR_ID = A.APLNT_ID
)
LEFT JOIN (SELECT CORP_NO
, APRVL_DOC_ID
, UPD_DATE AS UPD_DATE
, UPD_BF_WORK_CD AS UPD_BF_WORK_CD --변경전 근무 코드
, UPD_WORK_CD AS UPD_WORK_CD --변경후 근무코드
FROM SX_GW0110
) U3
ON(1=1
AND U3.CORP_NO = A.CORP_NO
AND U3.APRVL_DOC_ID = A.APRVL_DOC_ID
)
LEFT JOIN SX_GW0010 U4
ON(1=1
AND U4.USR_ID = A.BZ_DEPUTY_ID )
LEFT JOIN SX_CO0040 COMMON
ON (1=1
AND COMMON.CORP_NO = A.CORP_NO
AND COMMON.COMM_CL_CD = 'SX011' --결재상태코드
AND COMMON.COMM_CD = APRVL_STUS_CD )
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.APLNT_ID = @USR_ID
AND SUBSTRING(UPD_DATE,1,4) = @YYVCT_YY
--AND APRVL_STUS_CD IN ('0003') --결재완료
AND UPD_WORK_CD IN ('H', 'I', 'J', 'V', '병','U', 'X', 'Y')
) A
) T2 ON (1=1
AND T1.CORP_NO = T2.CORP_NO
AND T1.USR_ID = T2.USR_ID
AND T1.WORK_PLAN_YYMMDD = T2.UPD_DATE
)
) TX LEFT OUTER JOIN
( SELECT *
FROM SX_GW0060
WHERE CORP_NO = @CORP_NO
AND USR_ID = @USR_ID
AND YYVCT_YY = @YYVCT_YY ) TY ON (1=1)
--**************************************************************************************************************************************
-- 2017601 이전 버젼
--SELECT '총연차일 : ' + CONVERT(VARCHAR, ISNULL(YYVCT_CNT * 1.00,0)) + '일' AS YYVCT_CNT --총연차일
-- , '신청일수 : ' + CONVERT(VARCHAR, ISNULL(COM_CNT,0)) + '일' AS SINCHUNG_CNT --신청일수
-- , '사용일수 : ' + CONVERT(VARCHAR, ISNULL(COM_CNT,0)) + '일' AS USD_CNT --사용일수
-- , '특휴·병가등일수 : ' + CONVERT(VARCHAR, ISNULL(SPECIAL_CNT*1.00,0)) + '일' AS SPECIAL_CNT --특휴병가등일수
-- , '잔여연차 : ' + CONVERT(VARCHAR, ISNULL(YYVCT_CNT,0) - ISNULL(COM_CNT,0)) + '일' AS REMAIN_CNT --잔여연차
-- FROM
-- ( SELECT *
-- FROM SX_GW0060
-- WHERE CORP_NO = @CORP_NO
-- AND USR_ID = @USR_ID
-- AND YYVCT_YY = @YYVCT_YY ) A
-- inner JOIN
-- ( SELECT
-- SUM(B.YYCT_DEDU_DAY) AS COM_CNT, --YYCT_DEDU_DCNT, --연차사용일수
-- COUNT(CASE WHEN A.LATE_MIN > 0 THEN 1 END) AS LATE_DCNT, --지각
-- COUNT(CASE WHEN A.SKIPOFF_REMN_BZ_MIN > 0 THEN 1 END) AS SKIPOFF_DCNT, --조퇴
-- SUM(A.OUTING_CNT) AS OUTING_CNT, --외출횟수
-- SUM(A.OUTING_MIN) AS OUTING_MIN, --외출시간(분)
-- COUNT(CASE WHEN ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) IN ('병', 'X', 'Y') THEN 1 END) AS SPECIAL_CNT --SICKLEAVE_DCNT --병가, 특별휴가 일수
-- FROM SX_GW0050 A
-- LEFT JOIN SX_CO0070 B
-- ON(1=1
-- AND B.CORP_NO = A.CORP_NO
-- AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
-- )
-- WHERE 1=1
-- AND A.CORP_NO = @CORP_NO
-- AND A.USR_ID = @USR_ID
-- AND A.WORK_PLAN_YYMMDD LIKE @YYVCT_YY + '%'
-- ) B ON (1=1)
-- WHERE 1=1
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_monthly_working_time_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-02-02
-- Create Description : 스카이브릿지 그룹웨어 월별근무시간 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_monthly_working_time_select '1111',NULL,NULL,'','','','','','',NULL,'20170201','20170228','20170201'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_monthly_working_time_select]
@P0 nvarchar(4000)
,@P1 varchar(8000)
,@P2 varchar(8000)
,@P3 nvarchar(4000)
,@P4 nvarchar(4000)
,@P5 nvarchar(4000)
,@P6 nvarchar(4000)
,@P7 nvarchar(4000)
,@P8 nvarchar(4000)
,@P9 varchar(8000)
,@P10 nvarchar(4000)
,@P11 nvarchar(4000)
,@P12 nvarchar(4000)
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
;
WITH USER1 AS
(
SELECT U.CORP_NO,
U.USR_ID,
U.USR_NM,
U.TEAM_CD,
U.DUTY_CD
FROM SX_GW0010 U
WHERE 1=1
AND U.CORP_NO = @P0
AND (NULLIF(@P1, '') IS NULL OR U.USR_ID = @P2)
AND (NULLIF(@P3, '') IS NULL OR U.USR_NM LIKE CONCAT('%', @P4, '%'))
AND (NULLIF(@P5, '') IS NULL OR U.TEAM_CD = @P6)
AND (NULLIF(@P7, '') IS NULL OR U.DUTY_CD = @P8)
AND (@P9 = 'Y' OR NULLIF(U.RETIREMENT_DATE, '') IS NULL)
)
SELECT U.USR_ID,
U.USR_NM,
U.TEAM_CD,
U.DUTY_CD,
A.WORK_DCNT, --근무
B.YYVCT_CNT, --연차(조회가 없음)
A.YYCT_DEDU_DCNT, --연차사용
A.ABTI_DCNT, --결근
A.LATE_DCNT, --지각
A.SKIPOFF_DCNT, --조퇴
A.SICKLEAVE_DCNT, --병가
A.TOT_WORK_MIN,
A.LATE_MIN,
A.SKIPOFF_REMN_BZ_MIN,
A.INCLU_WORK_OT_MIN,
A.OT_WORK_MIN,
A.NGT_OT_MIN
FROM USER1 U
LEFT JOIN (
SELECT A.CORP_NO,
A.USR_ID,
--일수
SUM(CASE WHEN ISNULL(NULLIF(A.ABTI_YN, ''), 'N') != 'Y' THEN B.APLY_WORK_DAY END) AS WORK_DCNT, --근무
--연차(조회가 없음)
SUM(B.YYCT_DEDU_DAY) AS YYCT_DEDU_DCNT, --연차사용
COUNT(CASE WHEN ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) = '결' OR A.ABTI_YN = 'Y' THEN 1 END) AS ABTI_DCNT, --결근
COUNT(CASE WHEN A.LATE_MIN > 0 THEN 1 END) AS LATE_DCNT, --지각
COUNT(CASE WHEN A.SKIPOFF_REMN_BZ_MIN > 0 THEN 1 END) AS SKIPOFF_DCNT, --조퇴
COUNT(CASE WHEN ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD) = '병' THEN 1 END) AS SICKLEAVE_DCNT, --병가
--(분) : 시간은 화면에서 계속하는것 같음
SUM(A.TOT_WORK_MIN) AS TOT_WORK_MIN, --총근무
SUM(A.LATE_MIN) AS LATE_MIN, --지각
SUM(A.SKIPOFF_REMN_BZ_MIN) AS SKIPOFF_REMN_BZ_MIN, --조퇴
SUM(A.INCLU_WORK_OT_MIN) AS INCLU_WORK_OT_MIN, --포괄OT
SUM(A.OT_WORK_MIN) AS OT_WORK_MIN, --OT
SUM(A.NGT_OT_MIN) AS NGT_OT_MIN --야간
FROM USER1 U
JOIN SX_GW0050 A
ON(1=1
AND A.CORP_NO = U.CORP_NO
AND A.USR_ID = U.USR_ID
AND A.WORK_PLAN_YYMMDD BETWEEN @P10 AND @P11
)
LEFT JOIN SX_CO0070 B --공통코드
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
)
WHERE 1=1
GROUP BY
A.CORP_NO,
A.USR_ID
) A
ON(1=1
AND A.CORP_NO = U.CORP_NO
AND A.USR_ID = U.USR_ID
)
LEFT JOIN SX_GW0060 B
ON(1=1
AND B.CORP_NO = U.CORP_NO
AND B.USR_ID = U.USR_ID
AND B.YYVCT_YY = SUBSTRING(@P12, 1, 4)
)
WHERE 1=1
ORDER BY
USR_NM,
USR_ID
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_today_early_leave_YN_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 퇴근 클릭시 조퇴인지를 체크
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_today_early_leave_YN_select '1111', '20170215' , '10454'
-- exec pr_skybridge_gw_today_early_leave_YN_select '1111', '20170216' , '10454'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_today_early_leave_YN_select]
@CORP_NO VARCHAR(15) --회사코드
, @WORK_PLAN_YYMMDD VARCHAR(8) --근무일자(계획)
, @USR_ID VARCHAR(20) --사용자
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT COUNT('A') AS EARLY_LEAVE_YN --1이면 조퇴
FROM (
SELECT EARLY_LEAVE_MIN
, USR_NM
, TEAM_CD
, DUTY_CD
, WORK_END_DT
, USR_ID
FROM (
SELECT DATEDIFF(
MINUTE,
CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GETOFFWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')),
ISNULL(A.WORK_END_DT, GETDATE())
) AS EARLY_LEAVE_MIN,
U.USR_NM,
U.TEAM_CD,
U.DUTY_CD,
A.WORK_END_DT,
U.USR_ID
FROM SX_GW0050 A --일별근무계획
JOIN SX_GW0010 U --사용자
ON(1=1
AND U.CORP_NO = A.CORP_NO
AND U.USR_ID = A.USR_ID
)
JOIN SX_CO0070 B --공통근무코드
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
AND NULLIF(B.GETOFFWORK_TM_NM, '') IS NOT NULL
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.WORK_PLAN_YYMMDD = @WORK_PLAN_YYMMDD
AND ISNULL(A.WORK_CD, A.PLAN_WORK_CD) NOT IN('N', 'Z')
AND (
(
1=1
AND SUBSTRING(B.GETOFFWORK_TM_NM, 1, 1) != 'T'
AND CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GETOFFWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '))
> ISNULL(A.WORK_END_DT, GETDATE())
)
)
) AS TA
WHERE 1=1
) TA
WHERE USR_ID = @USR_ID
AND EARLY_LEAVE_MIN < 0
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_today_late_person_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 메인화면의 오늘 지각한사람(분) 조회
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_today_late_person_select '20160113', '20160113', '1111', '20170117'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_today_late_person_select]
@DISML_DATE VARCHAR(8) --해고일자
, @RETIREMENT_DATE VARCHAR(8) --퇴직일자 일자
, @CORP_NO VARCHAR(15) --회사코드
, @WORK_PLAN_YYMMDD VARCHAR(8) --근무일자(계획)
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 신규 작성 : 출근시각이 있는 사람에 한해서만 지각자를 DISPLAY 한다.
SELECT LATE_MIN
, USR_NM
, TEAM_CD
, DUTY_CD
, WORK_START_DT
FROM (
SELECT DATEDIFF(
MINUTE,
CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')),
ISNULL(A.WORK_START_DT, GETDATE())
) AS LATE_MIN,
U.USR_NM,
U.TEAM_CD,
U.DUTY_CD,
A.WORK_START_DT
FROM SX_GW0050 A --일별근무계획
JOIN SX_GW0010 U --사용자
ON(1=1
AND U.CORP_NO = A.CORP_NO
AND U.USR_ID = A.USR_ID
AND (NULLIF(U.DISML_DATE, '') IS NULL OR U.DISML_DATE >= @DISML_DATE) --해고일자
AND (NULLIF(U.RETIREMENT_DATE, '') IS NULL OR U.RETIREMENT_DATE >= @RETIREMENT_DATE) --퇴직일자
)
JOIN SX_CO0070 B --공통근무코드
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
AND NULLIF(B.GOTOWORK_TM_NM, '') IS NOT NULL
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.WORK_PLAN_YYMMDD = @WORK_PLAN_YYMMDD
AND (
(
1=1
AND SUBSTRING(B.GOTOWORK_TM_NM, 1, 1) != 'T'
AND CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '))
< ISNULL(A.WORK_START_DT, GETDATE())
)
)
) AS TA
WHERE 1=1
AND TA.LATE_MIN > 0
--AND TA.WORK_START_DT IS NOT NULL
ORDER BY
LATE_MIN DESC,
USR_NM
-- --기존에 사용한 문장 BACKUP
--SELECT DATEDIFF(
-- MINUTE,
-- CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')),
-- ISNULL(A.WORK_START_DT, GETDATE())
-- ) AS LATE_MIN,
-- U.USR_NM,
-- U.TEAM_CD,
-- U.DUTY_CD,
-- A.WORK_START_DT
-- FROM SX_GW0050 A --일별근무계획
-- JOIN SX_GW0010 U --사용자
-- ON(1=1
-- AND U.CORP_NO = A.CORP_NO
-- AND U.USR_ID = A.USR_ID
-- AND (NULLIF(U.DISML_DATE, '') IS NULL OR U.DISML_DATE >= @DISML_DATE) --해고일자
-- AND (NULLIF(U.RETIREMENT_DATE, '') IS NULL OR U.RETIREMENT_DATE >= @RETIREMENT_DATE) --퇴직일자
-- )
-- JOIN SX_CO0070 B --공통근무코드
-- ON(1=1
-- AND B.CORP_NO = A.CORP_NO
-- AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
-- AND NULLIF(B.GOTOWORK_TM_NM, '') IS NOT NULL
-- )
-- WHERE 1=1
-- AND A.CORP_NO = @CORP_NO
-- AND A.WORK_PLAN_YYMMDD = @WORK_PLAN_YYMMDD
-- AND (
-- (
-- 1=1
-- AND SUBSTRING(B.GOTOWORK_TM_NM, 1, 1) != 'T'
-- AND CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '))
-- < ISNULL(A.WORK_START_DT, GETDATE())
-- )
-- )
-- ORDER BY
-- LATE_MIN DESC,
-- USR_NM
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_today_late_YN_select] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 출근체크 클릭시 지작인지를 체크
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_today_late_YN_select '1111', '20170307' , '10352'
-- exec pr_skybridge_gw_today_late_YN_select '1111', '20170307' , '00026'
-- exec pr_skybridge_gw_today_late_YN_select '1111', '20170307' , '00001'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_today_late_YN_select]
@CORP_NO VARCHAR(15) --회사코드
, @WORK_PLAN_YYMMDD VARCHAR(8) --근무일자(계획)
, @USR_ID VARCHAR(20) --사용자
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT COUNT('A') AS LATE_YN --1이면 지각
FROM (
SELECT LATE_MIN
, USR_NM
, TEAM_CD
, DUTY_CD
, WORK_START_DT
, USR_ID
FROM (
SELECT DATEDIFF(
MINUTE,
CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, (CASE WHEN ISNULL(A.WORK_CD, A.PLAN_WORK_CD) IN('N', 'Z') THEN B.GETOFFWORK_TM_NM ELSE B.GOTOWORK_TM_NM END), '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')),
ISNULL(A.WORK_START_DT, GETDATE())
) AS LATE_MIN,
U.USR_NM,
U.TEAM_CD,
U.DUTY_CD,
A.WORK_START_DT,
U.USR_ID
FROM SX_GW0050 A --일별근무계획
JOIN SX_GW0010 U --사용자
ON(1=1
AND U.CORP_NO = A.CORP_NO
AND U.USR_ID = A.USR_ID
)
JOIN SX_CO0070 B --공통근무코드
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
AND NULLIF(B.GOTOWORK_TM_NM, '') IS NOT NULL
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.WORK_PLAN_YYMMDD = @WORK_PLAN_YYMMDD
--AND ISNULL(A.WORK_CD, A.PLAN_WORK_CD) NOT IN('N', 'Z') --2017.06.15 지각계 작성 관련 작업
AND (
(
1=1
--AND DATEPART(DW, @WORK_PLAN_YYMMDD) NOT IN('1', '7') --2017.06.15 지각계 작성 관련 작업
AND SUBSTRING(B.GOTOWORK_TM_NM, 1, 1) != 'T'
AND CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '))
< ISNULL(A.WORK_START_DT, GETDATE())
)
)
) AS TA
WHERE 1=1
--AND TA.WORK_START_DT IS NOT NULL
) TA
WHERE USR_ID = @USR_ID
AND LATE_MIN >= 1
-- --기존에 사용한 문장 BACKUP
--SELECT DATEDIFF(
-- MINUTE,
-- CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')),
-- ISNULL(A.WORK_START_DT, GETDATE())
-- ) AS LATE_MIN,
-- U.USR_NM,
-- U.TEAM_CD,
-- U.DUTY_CD,
-- A.WORK_START_DT
-- FROM SX_GW0050 A --일별근무계획
-- JOIN SX_GW0010 U --사용자
-- ON(1=1
-- AND U.CORP_NO = A.CORP_NO
-- AND U.USR_ID = A.USR_ID
-- AND (NULLIF(U.DISML_DATE, '') IS NULL OR U.DISML_DATE >= @DISML_DATE) --해고일자
-- AND (NULLIF(U.RETIREMENT_DATE, '') IS NULL OR U.RETIREMENT_DATE >= @RETIREMENT_DATE) --퇴직일자
-- )
-- JOIN SX_CO0070 B --공통근무코드
-- ON(1=1
-- AND B.CORP_NO = A.CORP_NO
-- AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
-- AND NULLIF(B.GOTOWORK_TM_NM, '') IS NOT NULL
-- )
-- WHERE 1=1
-- AND A.CORP_NO = @CORP_NO
-- AND A.WORK_PLAN_YYMMDD = @WORK_PLAN_YYMMDD
-- AND (
-- (
-- 1=1
-- AND SUBSTRING(B.GOTOWORK_TM_NM, 1, 1) != 'T'
-- AND CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '))
-- < ISNULL(A.WORK_START_DT, GETDATE())
-- )
-- )
-- ORDER BY
-- LATE_MIN DESC,
-- USR_NM
END --PROCEDURE END
GO
/****** Object: StoredProcedure [dbo].[pr_skybridge_gw_today_late_YN_select_ORI] Script Date: 2026-03-30 오후 1:12:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author : 박종응
-- Create date : 2017-01-17
-- Create Description : 스카이브릿지 그룹웨어 출근체크 클릭시 지작인지를 체크
-- Update date :
-- Update Description :
-- exec pr_skybridge_gw_today_late_YN_select '1111', '20170307' , '10352'
-- exec pr_skybridge_gw_today_late_YN_select '1111', '20170307' , '00026'
-- exec pr_skybridge_gw_today_late_YN_select '1111', '20170307' , '00001'
-- =======================================================================================
CREATE PROCEDURE [dbo].[pr_skybridge_gw_today_late_YN_select_ORI]
@CORP_NO VARCHAR(15) --회사코드
, @WORK_PLAN_YYMMDD VARCHAR(8) --근무일자(계획)
, @USR_ID VARCHAR(20) --사용자
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT COUNT('A') AS LATE_YN --1이면 지각
FROM (
SELECT LATE_MIN
, USR_NM
, TEAM_CD
, DUTY_CD
, WORK_START_DT
, USR_ID
FROM (
SELECT DATEDIFF(
MINUTE,
CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')),
ISNULL(A.WORK_START_DT, GETDATE())
) AS LATE_MIN,
U.USR_NM,
U.TEAM_CD,
U.DUTY_CD,
A.WORK_START_DT,
U.USR_ID
FROM SX_GW0050 A --일별근무계획
JOIN SX_GW0010 U --사용자
ON(1=1
AND U.CORP_NO = A.CORP_NO
AND U.USR_ID = A.USR_ID
)
JOIN SX_CO0070 B --공통근무코드
ON(1=1
AND B.CORP_NO = A.CORP_NO
AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
AND NULLIF(B.GOTOWORK_TM_NM, '') IS NOT NULL
)
WHERE 1=1
AND A.CORP_NO = @CORP_NO
AND A.WORK_PLAN_YYMMDD = @WORK_PLAN_YYMMDD
AND ISNULL(A.WORK_CD, A.PLAN_WORK_CD) NOT IN('N', 'Z')
AND (
(
1=1
AND DATEPART(DW, @WORK_PLAN_YYMMDD) NOT IN('1', '7')
AND SUBSTRING(B.GOTOWORK_TM_NM, 1, 1) != 'T'
AND CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '))
< ISNULL(A.WORK_START_DT, GETDATE())
)
)
) AS TA
WHERE 1=1
--AND TA.WORK_START_DT IS NOT NULL
) TA
WHERE USR_ID = @USR_ID
AND LATE_MIN >= 1
-- --기존에 사용한 문장 BACKUP
--SELECT DATEDIFF(
-- MINUTE,
-- CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')),
-- ISNULL(A.WORK_START_DT, GETDATE())
-- ) AS LATE_MIN,
-- U.USR_NM,
-- U.TEAM_CD,
-- U.DUTY_CD,
-- A.WORK_START_DT
-- FROM SX_GW0050 A --일별근무계획
-- JOIN SX_GW0010 U --사용자
-- ON(1=1
-- AND U.CORP_NO = A.CORP_NO
-- AND U.USR_ID = A.USR_ID
-- AND (NULLIF(U.DISML_DATE, '') IS NULL OR U.DISML_DATE >= @DISML_DATE) --해고일자
-- AND (NULLIF(U.RETIREMENT_DATE, '') IS NULL OR U.RETIREMENT_DATE >= @RETIREMENT_DATE) --퇴직일자
-- )
-- JOIN SX_CO0070 B --공통근무코드
-- ON(1=1
-- AND B.CORP_NO = A.CORP_NO
-- AND B.WORK_CD = ISNULL(NULLIF(A.WORK_CD, ''), A.PLAN_WORK_CD)
-- AND NULLIF(B.GOTOWORK_TM_NM, '') IS NOT NULL
-- )
-- WHERE 1=1
-- AND A.CORP_NO = @CORP_NO
-- AND A.WORK_PLAN_YYMMDD = @WORK_PLAN_YYMMDD
-- AND (
-- (
-- 1=1
-- AND SUBSTRING(B.GOTOWORK_TM_NM, 1, 1) != 'T'
-- AND CONVERT(DATETIME, STUFF(STUFF(STUFF(CONCAT(A.WORK_PLAN_YYMMDD, B.GOTOWORK_TM_NM, '00'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '))
-- < ISNULL(A.WORK_START_DT, GETDATE())
-- )
-- )
-- ORDER BY
-- LATE_MIN DESC,
-- USR_NM
END --PROCEDURE END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'정렬순서' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SX_GW0050', @level2type=N'COLUMN',@level2name=N'SORT_ODR'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'정렬순서' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SX_GW0060', @level2type=N'COLUMN',@level2name=N'SORT_ODR'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'회사번호' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SX_GW0100', @level2type=N'COLUMN',@level2name=N'CORP_NO'
GO
USE [master]
GO
ALTER DATABASE [logins_test] SET READ_WRITE
GO
Reference in New Issue View Git Blame Copy Permalink