Project

General

Profile

Download (148 KB)

Feature #185 » Feature_185_SP.sql

楷翊 張, 07/31/2026 08:16 AM

 
??/*
Kaid : CREATE PROCEDURE AND ALTER PROCEDURE TэN!k
*/
-------------------------------------------------------------------CREATE--------------------------------------------------------------------------------------------------
/****** Object: StoredProcedure [dbo].[sp_app_equip_maintain_colse] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: APP ?}?O?U close
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260625 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_app_equip_maintain_colse]
@mt_event_id BIGINT,
@mtsid INT,
@fs_arr NVARCHAR(MAX) = NULL,
@remark NVARCHAR(50) = NULL,
@update_uid SMALLINT
AS
DECLARE @eqpid INT
DECLARE @event_log_id BIGINT = -1
DECLARE @now DATETIME = GETDATE()
SELECT @eqpid = eqpid FROM dbo.maintenance_event_log WHERE mt_event_id = @mt_event_id
UPDATE dbo.maintenance_event
SET
mtsid = @mtsid,
end_dt = @now,
end_uid = @update_uid
WHERE
mt_event_id = @mt_event_id
INSERT INTO dbo.maintenance_event_log
(mt_event_id, mtsid, eqpid, remark, mt_dt, create_dt, create_uid)
VALUES
(@mt_event_id, @mtsid, @eqpid, @remark, @now, @now, @update_uid)
SELECT @event_log_id = SCOPE_IDENTITY()
IF(@fs_arr IS NOT NULL)
BEGIN
INSERT INTO dbo.maintenance_fs
SELECT
@event_log_id, value
FROM
string_split(@fs_arr, ',')
END
UPDATE dbo.eqp
SET
eqpsid = 5,
update_dt = GETDATE(),
update_uid = @update_uid
WHERE
eqpid = @eqpid
SELECT @event_log_id AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_app_mold_bind_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?f?e-??P
N NR?rKa
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260618 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_app_mold_bind_update]
@moldid INT,
@eqpid INT,
@moldsid TINYINT,
@moldbindsid TINYINT,
@opid INT = NULL,
@usage_count INT = NULL,
@fsid NVARCHAR(50) = NULL,
@remark NVARCHAR(50) = NULL,
@change_dt DATETIME,
@update_uid SMALLINT
AS
DECLARE @state INT = -99
DECLARE @check_mold INT = -1
DECLARE @check_eqp INT = -1
DECLARE @is_on BIT
DECLARE @check_op INT = -1
DECLARE @moldtid TINYINT = 0
SELECT @moldtid = moldtid FROM dbo.mold WHERE moldid = @moldid
SELECT TOP(1) @is_on = is_on FROM dbo.mold_bind_log WHERE eqpid = @eqpid ORDER BY create_dt DESC
SELECT @check_mold = moldid, @state = moldbindsid FROM dbo.mold WHERE moldid = @moldid AND is_deleted = 0
SELECT @check_eqp = eqpid FROM dbo.eqp WHERE @eqpid = @eqpid AND is_deleted = 0
SELECT @check_op = opid FROM dbo.map_op_eqp WHERE eqpid = @eqpid AND (opid = @opid OR @opid is NULL)
IF EXISTS (SELECT * FROM dbo.mold WHERE moldtid = @moldtid AND moldid IN (SELECT moldid FROM dbo.map_op_mold WHERE eqpid = @eqpid))
BEGIN
SET @state = -1 -- _j?S?]?} T^??WRwQ
END
ELSE IF (@check_mold < 0 )
BEGIN
SET @state = -2 -- ~b
N0R!jwQ
END
ELSE IF (@check_eqp < 0)
BEGIN
SET @state = -3 -- ~b
N0R_j?S
END
ELSE IF (@state <> 1 AND @moldbindsid = 2)
BEGIN
SET @state = -4 -- !jwQ?rKa
N???}?[
END
ELSE IF(@check_op <= 0)
BEGIN
SET @state = -5 -- _j?S*g?}?zޞ
END
ELSE
BEGIN
UPDATE dbo.mold
SET
moldsid = @moldsid,
moldbindsid = @moldbindsid,
update_dt = GETDATE(),
update_uid = @update_uid
WHERE
moldid = @moldid
IF(@moldsid = 4 AND @moldbindsid = 2) --
NR
BEGIN
INSERT INTO dbo.mold_bind_log
(eqpid, moldid, usage_count, moldsid, moldtid, is_on, fsid, remark, [uid], change_dt, create_dt, create_uid)
VALUES
(@eqpid, @moldid, @usage_count, @moldsid, @moldtid, 1, @fsid, @remark, @update_uid, GETDATE(), GETDATE(), @update_uid)
INSERT INTO dbo.map_op_mold
(moldid, opid, eqpid)
VALUES
(@moldid, @check_op, @eqpid)
SET @state = 1
END
ELSE -- NR
d?c0??O...???{ NR
BEGIN
IF(@usage_count IS NULL OR @usage_count = 0)
BEGIN
SELECT TOP(1) @usage_count = usage_count FROM dbo.mold_bind_log WHERE is_on = 1 AND eqpid = @eqpid AND moldid = @moldid ORDER BY create_dt DESC
END
INSERT INTO dbo.mold_bind_log
(eqpid, moldid, usage_count, moldsid, moldtid, is_on, fsid, remark, [uid], change_dt, create_dt, create_uid)
VALUES
(@eqpid, @moldid, @usage_count, @moldsid, @moldtid, 0, @fsid, @remark, @update_uid, @change_dt, GETDATE(), @update_uid)
DELETE FROM dbo.map_op_mold
WHERE moldid = @moldid AND eqpid = @eqpid
SET @state = 1
END
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_app_mpoeqp_list_BTOX] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?b?S(u_j?S
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260525 Kaid CREATE
*/
CREATE PROCEDURE [dbo].[sp_app_mpoeqp_list_BTOX]
@oactid INT,
@apteid SMALLINT
AS
SELECT DISTINCT
dbo.mpoeqp.eqpid, eqpno, eqpname, IIF(dbo.iot.iot_id IS NULL , 'False', 'True') AS is_binded
FROM
dbo.mpoeqp INNER JOIN
dbo.eqp ON dbo.eqp.eqpid = dbo.mpoeqp.eqpid LEFT JOIN
dbo.iot ON dbo.iot.eqpid = dbo.eqp.eqpid
WHERE
mpoid IN (SELECT mpoid FROM dbo.mpo WHERE oactid = @oactid AND apteid = @apteid) AND dbo.eqp.eqpsid IN (1, 2, 5)
GO
/****** Object: StoredProcedure [dbo].[sp_app_sys_role_funcode_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK:
kP?҉r?
kP?n?U
OODA_RESULT: RECORDSET
*/
CREATE PROCEDURE [dbo].[sp_app_sys_role_funcode_list]
@rid SMALLINT = -1
AS
DECLARE @roleFuncode TABLE
(
funcode_id SMALLINT,
is_auth BIT,
has_write BIT
)
IF @rid = -1
BEGIN
SELECT
fncid, fncno AS fncname, isapp, isweb, 'FALSE' AS is_auth, 'FALSE' AS has_write
FROM
dbo.cfnc
WHERE
isapp <> 0 OR isweb <> 0
END
ELSE
BEGIN
INSERT INTO @roleFuncode
SELECT fcid, 1, has_write FROM dbo.sysrfc WHERE rid = @rid
SELECT
fncid, fncno AS fncname, isapp, isweb, ISNULL(roleTb.is_auth, 'FALSE') AS is_auth, has_write
FROM
dbo.cfnc LEFT JOIN
@roleFuncode AS roleTb ON roleTb.funcode_id = dbo.cfnc.fncid
WHERE
isapp <> 0 OR isweb <> 0
ORDER BY isapp, funcode_id
END
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_erp_info_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_erp_info_create]
@runcard_no NVARCHAR(50) = null
,@customer NVARCHAR(50) = null
,@material_no NVARCHAR(50) = null
,@color NVARCHAR(50) = null
,@plasma NVARCHAR(50) = null
,@reich NVARCHAR(50) = null
,@remark NVARCHAR(50) = null
,@reich2 NVARCHAR(50) = null
,@lot_no NVARCHAR(50) = null
,@lens_specifications_bc FLOAT = NULL--b?T BC
,@lens_specifications_bcx FLOAT = NULL--b?T BCX
,@lens_specifications_bcy FLOAT = NULL--b?T BCY
,@lens_specifications_ct FLOAT = NULL--b?T CT
,@lens_specificatons_tc FLOAT = NULL--b?T TC
,@lens_specifications_dia FLOAT = NULL--b?T DIA
,@lens_specifications_power FLOAT = NULL--b?T POWER
,@lens_specifications_powerx FLOAT = NULL--b?T POWERX
,@lens_specifications_powery FLOAT = NULL--b?T POWERY
,@lens_semi_specifications_bc FLOAT = NULL--JSb?T BC
,@lens_semi_specifications_bcx FLOAT = NULL--JSb?T BCX
,@lens_semi_specifications_bcy FLOAT = NULL--JSb?T BCY
,@lens_semi_specifications_ct FLOAT = NULL--JSb?T CT
,@lens_semi_specifications_tc FLOAT = NULL--JSb?T TC
,@user_id INT = NULL
,@runcard_id BIGINT
AS
INSERT INTO btox.erp_info
(
runcard_id
,runcard_no
,customer
,material_no
,color-- N?bx??U
,plasma
,reich
,remark
,reich2
,lot_no
,lens_specifications_bc--b?T BC
,lens_specifications_bcx--b?T BCX
,lens_specifications_bcy--b?T BCY
,lens_specifications_ct--b?T CT
,lens_specificatons_tc--b?T TC
,lens_specifications_dia--b?T DIA
,lens_specifications_power--b?T POWER
,lens_specifications_powerx--b?T POWERX
,lens_specifications_powery--b?T POWERY
,lens_semi_specifications_bc--JSb?T BC
,lens_semi_specifications_bcx--JSb?T BCX
,lens_semi_specifications_bcy--JSb?T BCY
,lens_semi_specifications_ct--JSb?T CT
,lens_semi_specifications_tc--JSb?T TC
,create_dt
,update_dt
,[user_id]
)
VALUES
(
@runcard_id
,@runcard_no
,@customer
,@material_no
,@color-- N?bx??U
,@plasma
,@reich
,@remark
,@reich2
,@lot_no
,@lens_specifications_bc--b?T BC
,@lens_specifications_bcx--b?T BCX
,@lens_specifications_bcy--b?T BCY
,@lens_specifications_ct--b?T CT
,@lens_specificatons_tc--b?T TC
,@lens_specifications_dia--b?T DIA
,@lens_specifications_power--b?T POWER
,@lens_specifications_powerx--b?T POWERX
,@lens_specifications_powery--b?T POWERY
,@lens_semi_specifications_bc--JSb?T BC
,@lens_semi_specifications_bcx--JSb?T BCX
,@lens_semi_specifications_bcy--JSb?T BCY
,@lens_semi_specifications_ct--JSb?T CT
,@lens_semi_specifications_tc--JSb?T TC
,GETDATE()
,GETDATE()
,@user_id
)
SELECT SCOPE_IDENTITY () AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_erp_info_get] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_erp_info_get]
@rec_id BIGINT
AS
SELECT
[rec_id]
,[runcard_id]
,[runcard_no]
,[customer]
,[material_no]
,[color]-- N?bx??U
,[plasma]
,[reich]
,[remark]
,[reich2]
,[lot_no]
,[lens_specifications_bc]--b?T BC
,[lens_specifications_bcx]--b?T BCX
,[lens_specifications_bcy]--b?T BCY
,[lens_specifications_ct]--b?T CT
,[lens_specificatons_tc]--b?T TC
,[lens_specifications_dia]--b?T DIA
,[lens_specifications_power]--b?T POWER
,[lens_specifications_powerx]--b?T POWERX
,[lens_specifications_powery]--b?T POWERY
,[lens_semi_specifications_bc]--JSb?T BC
,[lens_semi_specifications_bcx]--JSb?T BCX
,[lens_semi_specifications_bcy]--JSb?T BCY
,[lens_semi_specifications_ct]--JSb?T CT
,[lens_semi_specifications_tc]--JSb?T TC
,[create_dt]
,[update_dt]
FROM
(
SELECT
rcid
FROM
dbo.rc
WHERE
rcsteid <> 6
) AS rcTb INNER JOIN
(
SELECT
[rec_id]
,[runcard_id]
,[runcard_no]
,[customer]
,[material_no]
,[color]-- N?bx??U
,[plasma]
,[reich]
,[remark]
,[reich2]
,[lot_no]
,[lens_specifications_bc]--b?T BC
,[lens_specifications_bcx]--b?T BCX
,[lens_specifications_bcy]--b?T BCY
,[lens_specifications_ct]--b?T CT
,[lens_specificatons_tc]--b?T TC
,[lens_specifications_dia]--b?T DIA
,[lens_specifications_power]--b?T POWER
,[lens_specifications_powerx]--b?T POWERX
,[lens_specifications_powery]--b?T POWERY
,[lens_semi_specifications_bc]--JSb?T BC
,[lens_semi_specifications_bcx]--JSb?T BCX
,[lens_semi_specifications_bcy]--JSb?T BCY
,[lens_semi_specifications_ct]--JSb?T CT
,[lens_semi_specifications_tc]--JSb?T TC
,[create_dt]
,[update_dt]
FROM
btox.erp_info
WHERE
rec_id = @rec_id
) AS erpTb ON erpTb.runcard_id = rcTb.rcid
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_erp_info_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_erp_info_list]
@erp_order_no NVArCHAR(50) = NULL
AS
IF (@erp_order_no IS NULL) SET @erp_order_no = ''
SELECT
[rec_id]
,[runcard_id]
,[runcard_no]
,[customer]
,[material_no]
,[color]-- N?bx??U
,[plasma]
,[reich]
,erpTb.[remark]
,[reich2]
,[lot_no]
,[lens_specifications_bc]--b?T BC
,[lens_specifications_bcx]--b?T BCX
,[lens_specifications_bcy]--b?T BCY
,[lens_specifications_ct]--b?T CT
,[lens_specificatons_tc]--b?T TC
,[lens_specifications_dia]--b?T DIA
,[lens_specifications_power]--b?T POWER
,[lens_specifications_powerx]--b?T POWERX
,[lens_specifications_powery]--b?T POWERY
,[lens_semi_specifications_bc]--JSb?T BC
,[lens_semi_specifications_bcx]--JSb?T BCX
,[lens_semi_specifications_bcy]--JSb?T BCY
,[lens_semi_specifications_ct]--JSb?T CT
,[lens_semi_specifications_tc]--JSb?T TC
,[create_dt]
,erpTb.[update_dt]
,ptid
,dbo.p.pid
FROM
(
SELECT
rcid
FROM
dbo.rc
WHERE
rcsteid <> 6
) AS rcTb INNER JOIN
(
SELECT
[rec_id]
,[runcard_id]
,[runcard_no]
,[customer]
,[material_no]
,[color]-- N?bx??U
,[plasma]
,[reich]
,[remark]
,[reich2]
,[lot_no]
,[lens_specifications_bc]--b?T BC
,[lens_specifications_bcx]--b?T BCX
,[lens_specifications_bcy]--b?T BCY
,[lens_specifications_ct]--b?T CT
,[lens_specificatons_tc]--b?T TC
,[lens_specifications_dia]--b?T DIA
,[lens_specifications_power]--b?T POWER
,[lens_specifications_powerx]--b?T POWERX
,[lens_specifications_powery]--b?T POWERY
,[lens_semi_specifications_bc]--JSb?T BC
,[lens_semi_specifications_bcx]--JSb?T BCX
,[lens_semi_specifications_bcy]--JSb?T BCY
,[lens_semi_specifications_ct]--JSb?T CT
,[lens_semi_specifications_tc]--JSb?T TC
,[create_dt]
,[update_dt]
FROM
btox.erp_info
WHERE
runcard_no LIKE '%' + @erp_order_no + '%'
) AS erpTb ON erpTb.runcard_id = rcTb.rcid INNER JOIN
(
SELECT
rcid, pid
FROM
dbo.mpc
WHERE
is_processing = 1 AND is_deleted = 0
) AS mpcTb ON mpcTb.rcid = rcTb.rcid INNER JOIN
dbo.p ON dbo.p.pid = mpcTb.pid
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_erp_info_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: VOID
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_erp_info_update]
@rec_id BIGINT,
@runcard_no NVARCHAR(50) = null
,@customer NVARCHAR(50) = null
,@material_no NVARCHAR(50) = null
,@color NVARCHAR(50) = null
,@plasma NVARCHAR(50) = null
,@reich NVARCHAR(50) = null
,@remark NVARCHAR(50) = null
,@reich2 NVARCHAR(50) = null
,@lot_no NVARCHAR(50) = null
,@lens_specifications_bc FLOAT = NULL--b?T BC
,@lens_specifications_bcx FLOAT = NULL--b?T BCX
,@lens_specifications_bcy FLOAT = NULL--b?T BCY
,@lens_specifications_ct FLOAT = NULL--b?T CT
,@lens_specificatons_tc FLOAT = NULL--b?T TC
,@lens_specifications_dia FLOAT = NULL--b?T DIA
,@lens_specifications_power FLOAT = NULL--b?T POWER
,@lens_specifications_powerx FLOAT = NULL--b?T POWERX
,@lens_specifications_powery FLOAT = NULL--b?T POWERY
,@lens_semi_specifications_bc FLOAT = NULL--JSb?T BC
,@lens_semi_specifications_bcx FLOAT = NULL--JSb?T BCX
,@lens_semi_specifications_bcy FLOAT = NULL--JSb?T BCY
,@lens_semi_specifications_ct FLOAT = NULL--JSb?T CT
,@lens_semi_specifications_tc FLOAT = NULL--JSb?T TC
,@user_id INT = NULL
AS
UPDATE btox.erp_info
SET
runcard_no = @runcard_no
,customer = @customer
,material_no = @material_no
,color = @color-- N?bx??U
,plasma = @plasma
,reich = @reich
,remark = @remark
,reich2 = @reich2
,lot_no = @lot_no
,lens_specifications_bc = @lens_specifications_bc--b?T BC
,lens_specifications_bcx = @lens_specifications_bcx --b?T BCX
,lens_specifications_bcy = @lens_specifications_bcy--b?T BCY
,lens_specifications_ct = @lens_specifications_ct--b?T CT
,lens_specificatons_tc = @lens_specificatons_tc--b?T TC
,lens_specifications_dia = @lens_specifications_dia--b?T DIA
,lens_specifications_power = @lens_specifications_power--b?T POWER
,lens_specifications_powerx = @lens_specifications_powerx--b?T POWERX
,lens_specifications_powery = @lens_specifications_powery--b?T POWERY
,lens_semi_specifications_bc = @lens_semi_specifications_bc--JSb?T BC
,lens_semi_specifications_bcx = @lens_semi_specifications_bcx--JSb?T BCX
,lens_semi_specifications_bcy = @lens_semi_specifications_bcy--JSb?T BCY
,lens_semi_specifications_ct = @lens_semi_specifications_ct--JSb?T CT
,lens_semi_specifications_tc = @lens_semi_specifications_tc--JSb?T TC
,update_dt = GETDATE()
,[user_id] = @user_id
WHERE
rec_id = @rec_id
SELECT SCOPE_IDENTITY () AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_material_info_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_material_info_list]
AS
SELECT
color
FROM
btox.material_info
GO
/****** Object: StoredProcedure [dbo].[sp_mes_runtask_checkout] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: Runtask check-out
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260526 Kaid CREATE
*/
CREATE PROCEDURE [dbo].[sp_mes_runtask_checkout]
@rcid BIGINT,
@mpoid BIGINT,
@checkout_qty DECIMAL(19,4),
@scrap_qty DECIMAL(19,4),
@update_dt DATETIME,
@update_uid SMALLINT
AS
DECLARE @state BIGINT = -1
DECLARE @mpcid BIGINT = -1
DECLARE @oseq INT = -1
DECLARE @eqpid INT = NULL
DECLARE @opid BIGINT
SELECT @mpcid = mpcid, @oseq = oseq, @opid = oactid FROM dbo.mpo WHERE mpoid = @mpoid AND rtsteid = 51
SELECT @state = rtid FROM dbo.rt WHERE mpoid = @mpoid AND rtsteid = 51
SELECT @eqpid = eqpid FROM dbo.rteqp WHERE rtid = @state
IF(@state > 0)
BEGIN
UPDATE dbo.mpo
SET
rtsteid = 57,
update_uid = @update_uid,
update_dt = @update_dt
WHERE
mpoid = @mpoid
UPDATE dbo.rt
SET
rtsteid = 57,
checkout_qty = @checkout_qty,
scrap_qty = @scrap_qty,
oenduid = @update_uid,
oenddt = @update_dt,
update_uid = @update_uid,
update_dt = @update_dt
WHERE
rtid = @state
-- Kaid : g?_N?z?v?c?]?UP}Hh
IF NOT EXISTS(SELECT COUNT(*) FROM dbo.mpo WHERE mpcid = @mpcid AND oseq > @oseq)
BEGIN
UPDATE dbo.rc
SET
rcsteid = 6,
prod_end_dt = @update_dt,
update_uid = @update_uid,
update_dt = @update_dt
WHERE
rcid = @rcid
END
IF EXISTS (SELECT COUNT(*) FROM dbo.rteqp WHERE rtid = @state)
BEGIN
INSERT INTO dbo.eqpstlg
SELECT TOP(1)
eqpid, 5, @rcid, @state, 'sysAction', @update_uid, @update_dt
FROM
dbo.rteqp
WHERE
rtid = @state
UPDATE dbo.eqp
SET
eqpsid = 5,
update_dt = @update_dt,
update_uid = @update_uid
WHERE
eqpid = @eqpid
END
DECLARE @is_on BIT
DECLARE @mold_log_id BIGINT
DECLARE @cur_moldid INT
DECLARE mdo CURSOR FOR
SELECT
moldid
FROM
dbo.map_op_mold INNER JOIN
dbo.map_op_eqp ON dbo.map_op_eqp.eqpid = dbo.map_op_mold.eqpid AND dbo.map_op_eqp.opid = dbo.map_op_mold.opid
WHERE
dbo.map_op_eqp.eqpid = @eqpid AND dbo.map_op_mold.opid = @opid
OPEN mdo
FETCH NEXT FROM mdo INTO @cur_moldid
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT TOP(1) @mold_log_id = recid, @is_on = is_on FROM dbo.mold_bind_log WHERE eqpid = @eqpid AND moldid = @cur_moldid ORDER BY create_dt DESC
IF(@is_on = 'True')
BEGIN
UPDATE dbo.mold_bind_log
SET
usage_count = usage_count + 1
WHERE
recid = @mold_log_id
END
FETCH NEXT FROM mdo INTO @cur_moldid
END
CLOSE mdo
DEALLOCATE mdo
END
SELECT @state AS result
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: create control_chart (return id)
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20250106 Jade Create
*/
CREATE PROCEDURE [dbo].[sp_web_department_create]
@department_no nvarchar(50),
@department_name nvarchar(50),
@update_by smallint
AS
INSERT INTO dbo.dept
(deptno,deptname, pltid, is_delete, update_dt, update_uid)
VALUES
(@department_no,@department_name, NULL, 0, GETDATE(), @update_by)
SELECT @@IDENTITY AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_delete] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: create control_chart (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_department_delete]
@deptid smallint,
@update_by smallint
AS
UPDATE dbo.dept
SET
is_delete = 1,
update_dt = GETDATE(),
update_uid = @update_by
WHERE
deptid = @deptid
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_info_by_id] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK:
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2025/01/06 Jade CREATE
*/
CREATE PROCEDURE [dbo].[sp_web_department_info_by_id]
@deptid SMALLINT
AS
SELECT
deptid, deptno, deptname
FROM
dbo.dept
WHERE
dbo.dept.deptid = @deptid
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: GET department list
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20250106 Jade Create
*/
CREATE PROCEDURE [dbo].[sp_web_department_list]
AS
SELECT
dbo.dept.deptid,
deptno,
deptname,
dbo.dept.update_dt,
username
FROM
dbo.dept INNER JOIN
dbo.[user] ON dbo.[user].[uid] = dbo.dept.update_uid
WHERE
dbo.dept.is_delete = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_option_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: sp_web_department_all_list
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
060428 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_web_department_option_list]
AS
SELECT
deptid, deptno, deptname
FROM
dbo.dept
WHERE
is_delete = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: create control_chart (return id)
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20250106 Jade Create
*/
CREATE PROCEDURE [dbo].[sp_web_department_update]
@deptid smallint,
@department_name nvarchar(50),
@update_by smallint
AS
UPDATE dbo.dept
SET
deptname = @department_name,
update_dt = GETDATE(),
update_uid = @update_by
WHERE
deptid = @deptid
GO
/****** Object: StoredProcedure [dbo].[sp_web_equip_option_list_by_state] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: WEB-??P;N?jList (option)
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260507 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_web_equip_option_list_by_state]
@eqpsid SMALLINT = NULL
AS
SELECT
eqpid, eqpno, eqpname
FROM
dbo.eqp
WHERE
is_deleted = 0 AND (dbo.eqp.eqpsid = @eqpsid OR @eqpsid IS NULL)
GO
/****** Object: StoredProcedure [dbo].[sp_web_equip_state_log_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: -??P???f}? (MR5F{)
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260508 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_web_equip_state_log_list]
@eqpid INT,
@data_count INT = 5
AS
SELECT
dbo.ceqps.eqpsno, dbo.ceqps.eqpsname, logTb.remark, username, logTb.create_dt, IIF(btox.erp_info.runcard_no IS NULL, '', btox.erp_info.runcard_no) AS erp_no
FROM
(
SELECT TOP(@data_count)
eqpid, eqpsid, rcid, rtid, remark, create_by, create_dt
FROM
dbo.eqpstlg
WHERE
eqpid = @eqpid
ORDER BY create_dt DESC
) AS logTb INNER JOIN
dbo.ceqps ON dbo.ceqps.eqpsid = logTb.eqpsid INNER JOIN
dbo.[user] ON dbo.[user].[uid] = logTb.create_by LEFT JOIN
dbo.rc ON dbo.rc.rcid = logTb.rcid LEFT JOIN
btox.erp_info ON btox.erp_info.runcard_id = dbo.rc.rcid
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?e?X?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_create]
@alarmno NVARCHAR(50),
@alarmname NVARCHAR(60)
AS
DECLARE @state BIGINT = -1
SELECT @state = alarmid FROM dbo.calarm WHERE alarmno = @alarmno
IF @state < 0
BEGIN
INSERT INTO dbo.calarm
(alarmno, alarmname)
VALUES
(@alarmno, @alarmname)
SELECT @state = SCOPE_IDENTITY()
END
ELSE
BEGIN
SET @state = -1
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_delete] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: *Rd??}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_delete]
@alarmid INT,
@update_uid smallint
AS
DECLARE @state BIGINT = -1
SELECT @state = alarmid FROM dbo.calarm WHERE alarmid = @alarmid
IF @state > 0
BEGIN
UPDATE dbo.calarm
SET
is_deleted = 1
WHERE
alarmid = @alarmid
END
SELECT @state AS result
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_get] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_get]
@alarmid INT
AS
SELECT
alarmid, alarmno, alarmname
FROM
dbo.calarm
WHERE
alarmid = @alarmid
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_list]
AS
SELECT
alarmid, alarmno, alarmname, is_system
FROM
dbo.calarm
WHERE
is_deleted = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?f?e?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_update]
@alarmid INT,
@alarmname nvarchar(50)
AS
DECLARE @state BIGINT = -1
SELECT @state = alarmid FROM dbo.calarm WHERE alarmid = @alarmid
IF @state > 0
BEGIN
UPDATE dbo.calarm
SET
alarmname = @alarmname
WHERE
alarmid = @alarmid
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?e?X?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_area_create]
@mtano NVARCHAR(50),
@mtaname NVARCHAR(60),
@remark NVARCHAR(MAX) = NULL,
@update_uid SMALLINT
AS
DECLARE @state BIGINT = -1
SELECT @state = mtaid FROM dbo.cmta WHERE mtano = @mtano
IF @state < 0
BEGIN
INSERT INTO dbo.cmta
(mtano, mtaname, remark)
VALUES
(@mtano, @mtaname, @remark)
SELECT @state = SCOPE_IDENTITY()
END
ELSE
BEGIN
SET @state = -1
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_delete] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: *Rd??}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_area_delete]
@mtaid INT,
@update_uid smallint
AS
DECLARE @state BIGINT = -1
SELECT @state = mtaid FROM dbo.cmta WHERE mtaid = @mtaid
IF @state > 0
BEGIN
UPDATE dbo.cmta
SET
is_deleted = 1
WHERE
mtaid = @mtaid
END
SELECT @state AS result
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_get] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_area_get]
@mtaid INT
AS
SELECT
mtaid, mtano, mtaname
FROM
dbo.cmta
WHERE
mtaid = @mtaid
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_area_list]
AS
SELECT
mtaid, mtano, mtaname
FROM
dbo.cmta
WHERE
is_deleted = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?f?e?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_area_update]
@mtaid INT,
@mtaname nvarchar(50),
@remark NVARCHAR(MAX) = NULL
AS
DECLARE @state BIGINT = -1
SELECT @state = mtaid FROM dbo.cmta WHERE mtaid = @mtaid
IF @state > 0
BEGIN
UPDATE dbo.cmta
SET
mtaname = @mtaname,
remark = @remark
WHERE
mtaid = @mtaid
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?e?X?}?O?S?V (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_reason_create]
@mtresno NVARCHAR(50),
@mtresname NVARCHAR(60)
AS
DECLARE @state BIGINT = -1
SELECT @state = mtresid FROM dbo.cmtres WHERE mtresno = @mtresno
IF @state < 0
BEGIN
INSERT INTO dbo.cmtres
(mtresno, mtresname)
VALUES
(@mtresno, @mtresname)
SELECT @state = SCOPE_IDENTITY()
END
ELSE
BEGIN
SET @state = -1
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_delete] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: *Rd??}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_reason_delete]
@mtresid INT,
@update_uid smallint
AS
DECLARE @state BIGINT = -1
SELECT @state = mtresid FROM dbo.cmtres WHERE mtresid = @mtresid
IF @state > 0
BEGIN
UPDATE dbo.cmtres
SET
is_deleted = 1
WHERE
mtresid = @mtresid
END
SELECT @state AS result
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_get] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_reason_get]
@mtresid INT
AS
SELECT
mtresid, mtresno, mtresname
FROM
dbo.cmtres
WHERE
mtresid = @mtresid
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_reason_list]
AS
SELECT
mtresid, mtresno, mtresname
FROM
dbo.cmtres
WHERE
is_deleted = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?f?e?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_reason_update]
@mtresid INT,
@mtresname nvarchar(50),
@remark NVARCHAR(MAX) = NULL
AS
DECLARE @state BIGINT = -1
SELECT @state = mtresid FROM dbo.cmtres WHERE mtresid = @mtresid
IF @state > 0
BEGIN
UPDATE dbo.cmtres
SET
mtresname = @mtresname
WHERE
mtresid = @mtresid
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_mold_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: !jwQ?e?X
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260507 Kaid Create
*/
CREATE PROCEDURE [dbo].[sp_web_mold_list]
@fctid TINYINT = NULL,
@moldtid TINYINT = NULL
AS
SELECT DISTINCT
dbo.mold.moldid, dbo.cmoldt.moldtid, dbo.cfct.fctid, dbo.cmolds.moldsid, dbo.cmoldbinds.moldbindsid,
dbo.cfct.fctname, dbo.cmoldt.moldtname, dbo.mold.moldno, dbo.mold.moldname,
dbo.cmolds.mdsname, dbo.cmoldbinds.moldbindsname, dbo.mold.remark, dbo.mold.update_dt, dbo.[user].username,
dbo.eqp.eqpid, eqpno, eqpname
FROM
dbo.mold INNER JOIN
dbo.cmoldbinds ON dbo.mold.moldbindsid = dbo.cmoldbinds.moldbindsid INNER JOIN
dbo.cmolds ON dbo.cmolds.moldsid = dbo.mold.moldsid INNER JOIN
dbo.cfct ON dbo.cfct.fctid = dbo.mold.fctid INNER JOIN
dbo.cmoldt ON dbo.cmoldt.moldtid = dbo.mold.moldtid INNER JOIN
dbo.[user] ON dbo.[user].[uid] = dbo.mold.update_uid LEFT JOIN
dbo.map_op_mold ON dbo.map_op_mold.moldid = dbo.mold.moldid LEFT JOIN
dbo.map_op_eqp ON dbo.map_op_eqp.eqpid = dbo.map_op_mold.eqpid LEFT JOIN
dbo.eqp ON dbo.eqp.eqpid = dbo.map_op_eqp.eqpid
WHERE
(dbo.mold.fctid = @fctid OR @fctid IS NULL) AND (dbo.mold.moldtid = @moldtid OR @moldtid IS NULL)
AND mold.is_deleted = 0
GO
-------------------------------------------------------------------ALTER--------------------------------------------------------------------------------------------------
/****** Object: StoredProcedure [dbo].[sp_app_equip_maintain_colse] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: APP ?}?O?U close
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260625 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_app_equip_maintain_colse]
@mt_event_id BIGINT,
@mtsid INT,
@fs_arr NVARCHAR(MAX) = NULL,
@remark NVARCHAR(50) = NULL,
@update_uid SMALLINT
AS
DECLARE @eqpid INT
DECLARE @event_log_id BIGINT = -1
DECLARE @now DATETIME = GETDATE()
SELECT @eqpid = eqpid FROM dbo.maintenance_event_log WHERE mt_event_id = @mt_event_id
UPDATE dbo.maintenance_event
SET
mtsid = @mtsid,
end_dt = @now,
end_uid = @update_uid
WHERE
mt_event_id = @mt_event_id
INSERT INTO dbo.maintenance_event_log
(mt_event_id, mtsid, eqpid, remark, mt_dt, create_dt, create_uid)
VALUES
(@mt_event_id, @mtsid, @eqpid, @remark, @now, @now, @update_uid)
SELECT @event_log_id = SCOPE_IDENTITY()
IF(@fs_arr IS NOT NULL)
BEGIN
INSERT INTO dbo.maintenance_fs
SELECT
@event_log_id, value
FROM
string_split(@fs_arr, ',')
END
UPDATE dbo.eqp
SET
eqpsid = 5,
update_dt = GETDATE(),
update_uid = @update_uid
WHERE
eqpid = @eqpid
SELECT @event_log_id AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_app_mold_bind_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?f?e-??P
N NR?rKa
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260618 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_app_mold_bind_update]
@moldid INT,
@eqpid INT,
@moldsid TINYINT,
@moldbindsid TINYINT,
@opid INT = NULL,
@usage_count INT = NULL,
@fsid NVARCHAR(50) = NULL,
@remark NVARCHAR(50) = NULL,
@change_dt DATETIME,
@update_uid SMALLINT
AS
DECLARE @state INT = -99
DECLARE @check_mold INT = -1
DECLARE @check_eqp INT = -1
DECLARE @is_on BIT
DECLARE @check_op INT = -1
DECLARE @moldtid TINYINT = 0
SELECT @moldtid = moldtid FROM dbo.mold WHERE moldid = @moldid
SELECT TOP(1) @is_on = is_on FROM dbo.mold_bind_log WHERE eqpid = @eqpid ORDER BY create_dt DESC
SELECT @check_mold = moldid, @state = moldbindsid FROM dbo.mold WHERE moldid = @moldid AND is_deleted = 0
SELECT @check_eqp = eqpid FROM dbo.eqp WHERE @eqpid = @eqpid AND is_deleted = 0
SELECT @check_op = opid FROM dbo.map_op_eqp WHERE eqpid = @eqpid AND (opid = @opid OR @opid is NULL)
IF EXISTS (SELECT * FROM dbo.mold WHERE moldtid = @moldtid AND moldid IN (SELECT moldid FROM dbo.map_op_mold WHERE eqpid = @eqpid))
BEGIN
SET @state = -1 -- _j?S?]?} T^??WRwQ
END
ELSE IF (@check_mold < 0 )
BEGIN
SET @state = -2 -- ~b
N0R!jwQ
END
ELSE IF (@check_eqp < 0)
BEGIN
SET @state = -3 -- ~b
N0R_j?S
END
ELSE IF (@state <> 1 AND @moldbindsid = 2)
BEGIN
SET @state = -4 -- !jwQ?rKa
N???}?[
END
ELSE IF(@check_op <= 0)
BEGIN
SET @state = -5 -- _j?S*g?}?zޞ
END
ELSE
BEGIN
UPDATE dbo.mold
SET
moldsid = @moldsid,
moldbindsid = @moldbindsid,
update_dt = GETDATE(),
update_uid = @update_uid
WHERE
moldid = @moldid
IF(@moldsid = 4 AND @moldbindsid = 2) --
NR
BEGIN
INSERT INTO dbo.mold_bind_log
(eqpid, moldid, usage_count, moldsid, moldtid, is_on, fsid, remark, [uid], change_dt, create_dt, create_uid)
VALUES
(@eqpid, @moldid, @usage_count, @moldsid, @moldtid, 1, @fsid, @remark, @update_uid, GETDATE(), GETDATE(), @update_uid)
INSERT INTO dbo.map_op_mold
(moldid, opid, eqpid)
VALUES
(@moldid, @check_op, @eqpid)
SET @state = 1
END
ELSE -- NR
d?c0??O...???{ NR
BEGIN
IF(@usage_count IS NULL OR @usage_count = 0)
BEGIN
SELECT TOP(1) @usage_count = usage_count FROM dbo.mold_bind_log WHERE is_on = 1 AND eqpid = @eqpid AND moldid = @moldid ORDER BY create_dt DESC
END
INSERT INTO dbo.mold_bind_log
(eqpid, moldid, usage_count, moldsid, moldtid, is_on, fsid, remark, [uid], change_dt, create_dt, create_uid)
VALUES
(@eqpid, @moldid, @usage_count, @moldsid, @moldtid, 0, @fsid, @remark, @update_uid, @change_dt, GETDATE(), @update_uid)
DELETE FROM dbo.map_op_mold
WHERE moldid = @moldid AND eqpid = @eqpid
SET @state = 1
END
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_app_mpoeqp_list_BTOX] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?b?S(u_j?S
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260525 Kaid CREATE
*/
ALTER PROCEDURE [dbo].[sp_app_mpoeqp_list_BTOX]
@oactid INT,
@apteid SMALLINT
AS
SELECT DISTINCT
dbo.mpoeqp.eqpid, eqpno, eqpname, IIF(dbo.iot.iot_id IS NULL , 'False', 'True') AS is_binded
FROM
dbo.mpoeqp INNER JOIN
dbo.eqp ON dbo.eqp.eqpid = dbo.mpoeqp.eqpid LEFT JOIN
dbo.iot ON dbo.iot.eqpid = dbo.eqp.eqpid
WHERE
mpoid IN (SELECT mpoid FROM dbo.mpo WHERE oactid = @oactid AND apteid = @apteid) AND dbo.eqp.eqpsid IN (1, 2, 5)
GO
/****** Object: StoredProcedure [dbo].[sp_app_sys_role_funcode_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK:
kP?҉r?
kP?n?U
OODA_RESULT: RECORDSET
*/
ALTER PROCEDURE [dbo].[sp_app_sys_role_funcode_list]
@rid SMALLINT = -1
AS
DECLARE @roleFuncode TABLE
(
funcode_id SMALLINT,
is_auth BIT,
has_write BIT
)
IF @rid = -1
BEGIN
SELECT
fncid, fncno AS fncname, isapp, isweb, 'FALSE' AS is_auth, 'FALSE' AS has_write
FROM
dbo.cfnc
WHERE
isapp <> 0 OR isweb <> 0
END
ELSE
BEGIN
INSERT INTO @roleFuncode
SELECT fcid, 1, has_write FROM dbo.sysrfc WHERE rid = @rid
SELECT
fncid, fncno AS fncname, isapp, isweb, ISNULL(roleTb.is_auth, 'FALSE') AS is_auth, has_write
FROM
dbo.cfnc LEFT JOIN
@roleFuncode AS roleTb ON roleTb.funcode_id = dbo.cfnc.fncid
WHERE
isapp <> 0 OR isweb <> 0
ORDER BY isapp, funcode_id
END
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_erp_info_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_erp_info_create]
@runcard_no NVARCHAR(50) = null
,@customer NVARCHAR(50) = null
,@material_no NVARCHAR(50) = null
,@color NVARCHAR(50) = null
,@plasma NVARCHAR(50) = null
,@reich NVARCHAR(50) = null
,@remark NVARCHAR(50) = null
,@reich2 NVARCHAR(50) = null
,@lot_no NVARCHAR(50) = null
,@lens_specifications_bc FLOAT = NULL--b?T BC
,@lens_specifications_bcx FLOAT = NULL--b?T BCX
,@lens_specifications_bcy FLOAT = NULL--b?T BCY
,@lens_specifications_ct FLOAT = NULL--b?T CT
,@lens_specificatons_tc FLOAT = NULL--b?T TC
,@lens_specifications_dia FLOAT = NULL--b?T DIA
,@lens_specifications_power FLOAT = NULL--b?T POWER
,@lens_specifications_powerx FLOAT = NULL--b?T POWERX
,@lens_specifications_powery FLOAT = NULL--b?T POWERY
,@lens_semi_specifications_bc FLOAT = NULL--JSb?T BC
,@lens_semi_specifications_bcx FLOAT = NULL--JSb?T BCX
,@lens_semi_specifications_bcy FLOAT = NULL--JSb?T BCY
,@lens_semi_specifications_ct FLOAT = NULL--JSb?T CT
,@lens_semi_specifications_tc FLOAT = NULL--JSb?T TC
,@user_id INT = NULL
,@runcard_id BIGINT
AS
INSERT INTO btox.erp_info
(
runcard_id
,runcard_no
,customer
,material_no
,color-- N?bx??U
,plasma
,reich
,remark
,reich2
,lot_no
,lens_specifications_bc--b?T BC
,lens_specifications_bcx--b?T BCX
,lens_specifications_bcy--b?T BCY
,lens_specifications_ct--b?T CT
,lens_specificatons_tc--b?T TC
,lens_specifications_dia--b?T DIA
,lens_specifications_power--b?T POWER
,lens_specifications_powerx--b?T POWERX
,lens_specifications_powery--b?T POWERY
,lens_semi_specifications_bc--JSb?T BC
,lens_semi_specifications_bcx--JSb?T BCX
,lens_semi_specifications_bcy--JSb?T BCY
,lens_semi_specifications_ct--JSb?T CT
,lens_semi_specifications_tc--JSb?T TC
,create_dt
,update_dt
,[user_id]
)
VALUES
(
@runcard_id
,@runcard_no
,@customer
,@material_no
,@color-- N?bx??U
,@plasma
,@reich
,@remark
,@reich2
,@lot_no
,@lens_specifications_bc--b?T BC
,@lens_specifications_bcx--b?T BCX
,@lens_specifications_bcy--b?T BCY
,@lens_specifications_ct--b?T CT
,@lens_specificatons_tc--b?T TC
,@lens_specifications_dia--b?T DIA
,@lens_specifications_power--b?T POWER
,@lens_specifications_powerx--b?T POWERX
,@lens_specifications_powery--b?T POWERY
,@lens_semi_specifications_bc--JSb?T BC
,@lens_semi_specifications_bcx--JSb?T BCX
,@lens_semi_specifications_bcy--JSb?T BCY
,@lens_semi_specifications_ct--JSb?T CT
,@lens_semi_specifications_tc--JSb?T TC
,GETDATE()
,GETDATE()
,@user_id
)
SELECT SCOPE_IDENTITY () AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_erp_info_get] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_erp_info_get]
@rec_id BIGINT
AS
SELECT
[rec_id]
,[runcard_id]
,[runcard_no]
,[customer]
,[material_no]
,[color]-- N?bx??U
,[plasma]
,[reich]
,[remark]
,[reich2]
,[lot_no]
,[lens_specifications_bc]--b?T BC
,[lens_specifications_bcx]--b?T BCX
,[lens_specifications_bcy]--b?T BCY
,[lens_specifications_ct]--b?T CT
,[lens_specificatons_tc]--b?T TC
,[lens_specifications_dia]--b?T DIA
,[lens_specifications_power]--b?T POWER
,[lens_specifications_powerx]--b?T POWERX
,[lens_specifications_powery]--b?T POWERY
,[lens_semi_specifications_bc]--JSb?T BC
,[lens_semi_specifications_bcx]--JSb?T BCX
,[lens_semi_specifications_bcy]--JSb?T BCY
,[lens_semi_specifications_ct]--JSb?T CT
,[lens_semi_specifications_tc]--JSb?T TC
,[create_dt]
,[update_dt]
FROM
(
SELECT
rcid
FROM
dbo.rc
WHERE
rcsteid <> 6
) AS rcTb INNER JOIN
(
SELECT
[rec_id]
,[runcard_id]
,[runcard_no]
,[customer]
,[material_no]
,[color]-- N?bx??U
,[plasma]
,[reich]
,[remark]
,[reich2]
,[lot_no]
,[lens_specifications_bc]--b?T BC
,[lens_specifications_bcx]--b?T BCX
,[lens_specifications_bcy]--b?T BCY
,[lens_specifications_ct]--b?T CT
,[lens_specificatons_tc]--b?T TC
,[lens_specifications_dia]--b?T DIA
,[lens_specifications_power]--b?T POWER
,[lens_specifications_powerx]--b?T POWERX
,[lens_specifications_powery]--b?T POWERY
,[lens_semi_specifications_bc]--JSb?T BC
,[lens_semi_specifications_bcx]--JSb?T BCX
,[lens_semi_specifications_bcy]--JSb?T BCY
,[lens_semi_specifications_ct]--JSb?T CT
,[lens_semi_specifications_tc]--JSb?T TC
,[create_dt]
,[update_dt]
FROM
btox.erp_info
WHERE
rec_id = @rec_id
) AS erpTb ON erpTb.runcard_id = rcTb.rcid
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_erp_info_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_erp_info_list]
@erp_order_no NVArCHAR(50) = NULL
AS
IF (@erp_order_no IS NULL) SET @erp_order_no = ''
SELECT
[rec_id]
,[runcard_id]
,[runcard_no]
,[customer]
,[material_no]
,[color]-- N?bx??U
,[plasma]
,[reich]
,erpTb.[remark]
,[reich2]
,[lot_no]
,[lens_specifications_bc]--b?T BC
,[lens_specifications_bcx]--b?T BCX
,[lens_specifications_bcy]--b?T BCY
,[lens_specifications_ct]--b?T CT
,[lens_specificatons_tc]--b?T TC
,[lens_specifications_dia]--b?T DIA
,[lens_specifications_power]--b?T POWER
,[lens_specifications_powerx]--b?T POWERX
,[lens_specifications_powery]--b?T POWERY
,[lens_semi_specifications_bc]--JSb?T BC
,[lens_semi_specifications_bcx]--JSb?T BCX
,[lens_semi_specifications_bcy]--JSb?T BCY
,[lens_semi_specifications_ct]--JSb?T CT
,[lens_semi_specifications_tc]--JSb?T TC
,[create_dt]
,erpTb.[update_dt]
,ptid
,dbo.p.pid
FROM
(
SELECT
rcid
FROM
dbo.rc
WHERE
rcsteid <> 6
) AS rcTb INNER JOIN
(
SELECT
[rec_id]
,[runcard_id]
,[runcard_no]
,[customer]
,[material_no]
,[color]-- N?bx??U
,[plasma]
,[reich]
,[remark]
,[reich2]
,[lot_no]
,[lens_specifications_bc]--b?T BC
,[lens_specifications_bcx]--b?T BCX
,[lens_specifications_bcy]--b?T BCY
,[lens_specifications_ct]--b?T CT
,[lens_specificatons_tc]--b?T TC
,[lens_specifications_dia]--b?T DIA
,[lens_specifications_power]--b?T POWER
,[lens_specifications_powerx]--b?T POWERX
,[lens_specifications_powery]--b?T POWERY
,[lens_semi_specifications_bc]--JSb?T BC
,[lens_semi_specifications_bcx]--JSb?T BCX
,[lens_semi_specifications_bcy]--JSb?T BCY
,[lens_semi_specifications_ct]--JSb?T CT
,[lens_semi_specifications_tc]--JSb?T TC
,[create_dt]
,[update_dt]
FROM
btox.erp_info
WHERE
runcard_no LIKE '%' + @erp_order_no + '%'
) AS erpTb ON erpTb.runcard_id = rcTb.rcid INNER JOIN
(
SELECT
rcid, pid
FROM
dbo.mpc
WHERE
is_processing = 1 AND is_deleted = 0
) AS mpcTb ON mpcTb.rcid = rcTb.rcid INNER JOIN
dbo.p ON dbo.p.pid = mpcTb.pid
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_erp_info_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: VOID
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_erp_info_update]
@rec_id BIGINT,
@runcard_no NVARCHAR(50) = null
,@customer NVARCHAR(50) = null
,@material_no NVARCHAR(50) = null
,@color NVARCHAR(50) = null
,@plasma NVARCHAR(50) = null
,@reich NVARCHAR(50) = null
,@remark NVARCHAR(50) = null
,@reich2 NVARCHAR(50) = null
,@lot_no NVARCHAR(50) = null
,@lens_specifications_bc FLOAT = NULL--b?T BC
,@lens_specifications_bcx FLOAT = NULL--b?T BCX
,@lens_specifications_bcy FLOAT = NULL--b?T BCY
,@lens_specifications_ct FLOAT = NULL--b?T CT
,@lens_specificatons_tc FLOAT = NULL--b?T TC
,@lens_specifications_dia FLOAT = NULL--b?T DIA
,@lens_specifications_power FLOAT = NULL--b?T POWER
,@lens_specifications_powerx FLOAT = NULL--b?T POWERX
,@lens_specifications_powery FLOAT = NULL--b?T POWERY
,@lens_semi_specifications_bc FLOAT = NULL--JSb?T BC
,@lens_semi_specifications_bcx FLOAT = NULL--JSb?T BCX
,@lens_semi_specifications_bcy FLOAT = NULL--JSb?T BCY
,@lens_semi_specifications_ct FLOAT = NULL--JSb?T CT
,@lens_semi_specifications_tc FLOAT = NULL--JSb?T TC
,@user_id INT = NULL
AS
UPDATE btox.erp_info
SET
runcard_no = @runcard_no
,customer = @customer
,material_no = @material_no
,color = @color-- N?bx??U
,plasma = @plasma
,reich = @reich
,remark = @remark
,reich2 = @reich2
,lot_no = @lot_no
,lens_specifications_bc = @lens_specifications_bc--b?T BC
,lens_specifications_bcx = @lens_specifications_bcx --b?T BCX
,lens_specifications_bcy = @lens_specifications_bcy--b?T BCY
,lens_specifications_ct = @lens_specifications_ct--b?T CT
,lens_specificatons_tc = @lens_specificatons_tc--b?T TC
,lens_specifications_dia = @lens_specifications_dia--b?T DIA
,lens_specifications_power = @lens_specifications_power--b?T POWER
,lens_specifications_powerx = @lens_specifications_powerx--b?T POWERX
,lens_specifications_powery = @lens_specifications_powery--b?T POWERY
,lens_semi_specifications_bc = @lens_semi_specifications_bc--JSb?T BC
,lens_semi_specifications_bcx = @lens_semi_specifications_bcx--JSb?T BCX
,lens_semi_specifications_bcy = @lens_semi_specifications_bcy--JSb?T BCY
,lens_semi_specifications_ct = @lens_semi_specifications_ct--JSb?T CT
,lens_semi_specifications_tc = @lens_semi_specifications_tc--JSb?T TC
,update_dt = GETDATE()
,[user_id] = @user_id
WHERE
rec_id = @rec_id
SELECT SCOPE_IDENTITY () AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_BTOX_material_info_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: BTOX O?r?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20727 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_material_info_list]
AS
SELECT
color
FROM
btox.material_info
GO
/****** Object: StoredProcedure [dbo].[sp_mes_runtask_checkout] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: Runtask check-out
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260526 Kaid CREATE
*/
ALTER PROCEDURE [dbo].[sp_mes_runtask_checkout]
@rcid BIGINT,
@mpoid BIGINT,
@checkout_qty DECIMAL(19,4),
@scrap_qty DECIMAL(19,4),
@update_dt DATETIME,
@update_uid SMALLINT
AS
DECLARE @state BIGINT = -1
DECLARE @mpcid BIGINT = -1
DECLARE @oseq INT = -1
DECLARE @eqpid INT = NULL
DECLARE @opid BIGINT
SELECT @mpcid = mpcid, @oseq = oseq, @opid = oactid FROM dbo.mpo WHERE mpoid = @mpoid AND rtsteid = 51
SELECT @state = rtid FROM dbo.rt WHERE mpoid = @mpoid AND rtsteid = 51
SELECT @eqpid = eqpid FROM dbo.rteqp WHERE rtid = @state
IF(@state > 0)
BEGIN
UPDATE dbo.mpo
SET
rtsteid = 57,
update_uid = @update_uid,
update_dt = @update_dt
WHERE
mpoid = @mpoid
UPDATE dbo.rt
SET
rtsteid = 57,
checkout_qty = @checkout_qty,
scrap_qty = @scrap_qty,
oenduid = @update_uid,
oenddt = @update_dt,
update_uid = @update_uid,
update_dt = @update_dt
WHERE
rtid = @state
-- Kaid : g?_N?z?v?c?]?UP}Hh
IF NOT EXISTS(SELECT COUNT(*) FROM dbo.mpo WHERE mpcid = @mpcid AND oseq > @oseq)
BEGIN
UPDATE dbo.rc
SET
rcsteid = 6,
prod_end_dt = @update_dt,
update_uid = @update_uid,
update_dt = @update_dt
WHERE
rcid = @rcid
END
IF EXISTS (SELECT COUNT(*) FROM dbo.rteqp WHERE rtid = @state)
BEGIN
INSERT INTO dbo.eqpstlg
SELECT TOP(1)
eqpid, 5, @rcid, @state, 'sysAction', @update_uid, @update_dt
FROM
dbo.rteqp
WHERE
rtid = @state
UPDATE dbo.eqp
SET
eqpsid = 5,
update_dt = @update_dt,
update_uid = @update_uid
WHERE
eqpid = @eqpid
END
DECLARE @is_on BIT
DECLARE @mold_log_id BIGINT
DECLARE @cur_moldid INT
DECLARE mdo CURSOR FOR
SELECT
moldid
FROM
dbo.map_op_mold INNER JOIN
dbo.map_op_eqp ON dbo.map_op_eqp.eqpid = dbo.map_op_mold.eqpid AND dbo.map_op_eqp.opid = dbo.map_op_mold.opid
WHERE
dbo.map_op_eqp.eqpid = @eqpid AND dbo.map_op_mold.opid = @opid
OPEN mdo
FETCH NEXT FROM mdo INTO @cur_moldid
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT TOP(1) @mold_log_id = recid, @is_on = is_on FROM dbo.mold_bind_log WHERE eqpid = @eqpid AND moldid = @cur_moldid ORDER BY create_dt DESC
IF(@is_on = 'True')
BEGIN
UPDATE dbo.mold_bind_log
SET
usage_count = usage_count + 1
WHERE
recid = @mold_log_id
END
FETCH NEXT FROM mdo INTO @cur_moldid
END
CLOSE mdo
DEALLOCATE mdo
END
SELECT @state AS result
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: create control_chart (return id)
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20250106 Jade Create
*/
ALTER PROCEDURE [dbo].[sp_web_department_create]
@department_no nvarchar(50),
@department_name nvarchar(50),
@update_by smallint
AS
INSERT INTO dbo.dept
(deptno,deptname, pltid, is_delete, update_dt, update_uid)
VALUES
(@department_no,@department_name, NULL, 0, GETDATE(), @update_by)
SELECT @@IDENTITY AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_delete] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: create control_chart (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_department_delete]
@deptid smallint,
@update_by smallint
AS
UPDATE dbo.dept
SET
is_delete = 1,
update_dt = GETDATE(),
update_uid = @update_by
WHERE
deptid = @deptid
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_info_by_id] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK:
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2025/01/06 Jade CREATE
*/
ALTER PROCEDURE [dbo].[sp_web_department_info_by_id]
@deptid SMALLINT
AS
SELECT
deptid, deptno, deptname
FROM
dbo.dept
WHERE
dbo.dept.deptid = @deptid
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: GET department list
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20250106 Jade Create
*/
ALTER PROCEDURE [dbo].[sp_web_department_list]
AS
SELECT
dbo.dept.deptid,
deptno,
deptname,
dbo.dept.update_dt,
username
FROM
dbo.dept INNER JOIN
dbo.[user] ON dbo.[user].[uid] = dbo.dept.update_uid
WHERE
dbo.dept.is_delete = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_option_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: sp_web_department_all_list
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
060428 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_web_department_option_list]
AS
SELECT
deptid, deptno, deptname
FROM
dbo.dept
WHERE
is_delete = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_department_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: create control_chart (return id)
OODA_RESULT: SCALAR
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
20250106 Jade Create
*/
ALTER PROCEDURE [dbo].[sp_web_department_update]
@deptid smallint,
@department_name nvarchar(50),
@update_by smallint
AS
UPDATE dbo.dept
SET
deptname = @department_name,
update_dt = GETDATE(),
update_uid = @update_by
WHERE
deptid = @deptid
GO
/****** Object: StoredProcedure [dbo].[sp_web_equip_option_list_by_state] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: WEB-??P;N?jList (option)
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260507 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_web_equip_option_list_by_state]
@eqpsid SMALLINT = NULL
AS
SELECT
eqpid, eqpno, eqpname
FROM
dbo.eqp
WHERE
is_deleted = 0 AND (dbo.eqp.eqpsid = @eqpsid OR @eqpsid IS NULL)
GO
/****** Object: StoredProcedure [dbo].[sp_web_equip_state_log_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: -??P???f}? (MR5F{)
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260508 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_web_equip_state_log_list]
@eqpid INT,
@data_count INT = 5
AS
SELECT
dbo.ceqps.eqpsno, dbo.ceqps.eqpsname, logTb.remark, username, logTb.create_dt, IIF(btox.erp_info.runcard_no IS NULL, '', btox.erp_info.runcard_no) AS erp_no
FROM
(
SELECT TOP(@data_count)
eqpid, eqpsid, rcid, rtid, remark, create_by, create_dt
FROM
dbo.eqpstlg
WHERE
eqpid = @eqpid
ORDER BY create_dt DESC
) AS logTb INNER JOIN
dbo.ceqps ON dbo.ceqps.eqpsid = logTb.eqpsid INNER JOIN
dbo.[user] ON dbo.[user].[uid] = logTb.create_by LEFT JOIN
dbo.rc ON dbo.rc.rcid = logTb.rcid LEFT JOIN
btox.erp_info ON btox.erp_info.runcard_id = dbo.rc.rcid
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?e?X?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_create]
@alarmno NVARCHAR(50),
@alarmname NVARCHAR(60)
AS
DECLARE @state BIGINT = -1
SELECT @state = alarmid FROM dbo.calarm WHERE alarmno = @alarmno
IF @state < 0
BEGIN
INSERT INTO dbo.calarm
(alarmno, alarmname)
VALUES
(@alarmno, @alarmname)
SELECT @state = SCOPE_IDENTITY()
END
ELSE
BEGIN
SET @state = -1
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_delete] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: *Rd??}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_delete]
@alarmid INT,
@update_uid smallint
AS
DECLARE @state BIGINT = -1
SELECT @state = alarmid FROM dbo.calarm WHERE alarmid = @alarmid
IF @state > 0
BEGIN
UPDATE dbo.calarm
SET
is_deleted = 1
WHERE
alarmid = @alarmid
END
SELECT @state AS result
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_get] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_get]
@alarmid INT
AS
SELECT
alarmid, alarmno, alarmname
FROM
dbo.calarm
WHERE
alarmid = @alarmid
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_list]
AS
SELECT
alarmid, alarmno, alarmname, is_system
FROM
dbo.calarm
WHERE
is_deleted = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_alarm_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?f?e?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_update]
@alarmid INT,
@alarmname nvarchar(50)
AS
DECLARE @state BIGINT = -1
SELECT @state = alarmid FROM dbo.calarm WHERE alarmid = @alarmid
IF @state > 0
BEGIN
UPDATE dbo.calarm
SET
alarmname = @alarmname
WHERE
alarmid = @alarmid
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?e?X?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_area_create]
@mtano NVARCHAR(50),
@mtaname NVARCHAR(60),
@remark NVARCHAR(MAX) = NULL,
@update_uid SMALLINT
AS
DECLARE @state BIGINT = -1
SELECT @state = mtaid FROM dbo.cmta WHERE mtano = @mtano
IF @state < 0
BEGIN
INSERT INTO dbo.cmta
(mtano, mtaname, remark)
VALUES
(@mtano, @mtaname, @remark)
SELECT @state = SCOPE_IDENTITY()
END
ELSE
BEGIN
SET @state = -1
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_delete] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: *Rd??}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_area_delete]
@mtaid INT,
@update_uid smallint
AS
DECLARE @state BIGINT = -1
SELECT @state = mtaid FROM dbo.cmta WHERE mtaid = @mtaid
IF @state > 0
BEGIN
UPDATE dbo.cmta
SET
is_deleted = 1
WHERE
mtaid = @mtaid
END
SELECT @state AS result
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_get] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_area_get]
@mtaid INT
AS
SELECT
mtaid, mtano, mtaname
FROM
dbo.cmta
WHERE
mtaid = @mtaid
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_area_list]
AS
SELECT
mtaid, mtano, mtaname
FROM
dbo.cmta
WHERE
is_deleted = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_area_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?f?e?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_area_update]
@mtaid INT,
@mtaname nvarchar(50),
@remark NVARCHAR(MAX) = NULL
AS
DECLARE @state BIGINT = -1
SELECT @state = mtaid FROM dbo.cmta WHERE mtaid = @mtaid
IF @state > 0
BEGIN
UPDATE dbo.cmta
SET
mtaname = @mtaname,
remark = @remark
WHERE
mtaid = @mtaid
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_create] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?e?X?}?O?S?V (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_reason_create]
@mtresno NVARCHAR(50),
@mtresname NVARCHAR(60)
AS
DECLARE @state BIGINT = -1
SELECT @state = mtresid FROM dbo.cmtres WHERE mtresno = @mtresno
IF @state < 0
BEGIN
INSERT INTO dbo.cmtres
(mtresno, mtresname)
VALUES
(@mtresno, @mtresname)
SELECT @state = SCOPE_IDENTITY()
END
ELSE
BEGIN
SET @state = -1
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_delete] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: *Rd??}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_reason_delete]
@mtresid INT,
@update_uid smallint
AS
DECLARE @state BIGINT = -1
SELECT @state = mtresid FROM dbo.cmtres WHERE mtresid = @mtresid
IF @state > 0
BEGIN
UPDATE dbo.cmtres
SET
is_deleted = 1
WHERE
mtresid = @mtresid
END
SELECT @state AS result
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_get] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_reason_get]
@mtresid INT
AS
SELECT
mtresid, mtresno, mtresname
FROM
dbo.cmtres
WHERE
mtresid = @mtresid
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: web part list "u?T;N?jRh?
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
2026/04/29 Kaid CREATE
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_reason_list]
AS
SELECT
mtresid, mtresno, mtresname
FROM
dbo.cmtres
WHERE
is_deleted = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_maintain_reason_update] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?f?e?}?O@S?W (return id)
OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_reason_update]
@mtresid INT,
@mtresname nvarchar(50),
@remark NVARCHAR(MAX) = NULL
AS
DECLARE @state BIGINT = -1
SELECT @state = mtresid FROM dbo.cmtres WHERE mtresid = @mtresid
IF @state > 0
BEGIN
UPDATE dbo.cmtres
SET
mtresname = @mtresname
WHERE
mtresid = @mtresid
END
SELECT @state AS RESULT
GO
/****** Object: StoredProcedure [dbo].[sp_web_mold_list] Script Date: 2026/7/29 NHS 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: !jwQ?e?X
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260507 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_web_mold_list]
@fctid TINYINT = NULL,
@moldtid TINYINT = NULL
AS
SELECT DISTINCT
dbo.mold.moldid, dbo.cmoldt.moldtid, dbo.cfct.fctid, dbo.cmolds.moldsid, dbo.cmoldbinds.moldbindsid,
dbo.cfct.fctname, dbo.cmoldt.moldtname, dbo.mold.moldno, dbo.mold.moldname,
dbo.cmolds.mdsname, dbo.cmoldbinds.moldbindsname, dbo.mold.remark, dbo.mold.update_dt, dbo.[user].username,
dbo.eqp.eqpid, eqpno, eqpname
FROM
dbo.mold INNER JOIN
dbo.cmoldbinds ON dbo.mold.moldbindsid = dbo.cmoldbinds.moldbindsid INNER JOIN
dbo.cmolds ON dbo.cmolds.moldsid = dbo.mold.moldsid INNER JOIN
dbo.cfct ON dbo.cfct.fctid = dbo.mold.fctid INNER JOIN
dbo.cmoldt ON dbo.cmoldt.moldtid = dbo.mold.moldtid INNER JOIN
dbo.[user] ON dbo.[user].[uid] = dbo.mold.update_uid LEFT JOIN
dbo.map_op_mold ON dbo.map_op_mold.moldid = dbo.mold.moldid LEFT JOIN
dbo.map_op_eqp ON dbo.map_op_eqp.eqpid = dbo.map_op_mold.eqpid LEFT JOIN
dbo.eqp ON dbo.eqp.eqpid = dbo.map_op_eqp.eqpid
WHERE
(dbo.mold.fctid = @fctid OR @fctid IS NULL) AND (dbo.mold.moldtid = @moldtid OR @moldtid IS NULL)
AND mold.is_deleted = 0
GO
/****** Object: StoredProcedure [dbo].[sp_web_map_op_eqp_list] Script Date: 2026/7/31 NHS 04:08:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
OODA_REMARK: ?zޞ_j?S?}?[ܕ?O
OODA_RESULT: RECORDSET
*/
/*
?O9ewk z
Bf?? ?Ock?N Mark
260701 Kaid Create
*/
ALTER PROCEDURE [dbo].[sp_web_map_op_eqp_list]
AS
DECLARE @resultTb TABLE
(
eqpid INT,
eqpno NVARCHAR(50),
eqpname NVARCHAR(50),
actmsgid INT,
opname NVARCHAR(50),
fctid INT,
fctno NVARCHAR(50),
fctname NVARCHAR(50),
moldid INT,
moldno NVARCHAR(50),
moldname NVARCHAR(50),
moldtname NVARCHAR(50)
)
DECLARE @opid INT,
@eqpid INT
DECLARE mdo CURSOR FOR
SELECT
opid, eqpid
FROM
dbo.map_op_eqp
OPEN mdo
FETCH NEXT FROM mdo INTO @opid, @eqpid
WHILE(@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @resultTb
SELECT
dbo.eqp.eqpid, dbo.eqp.eqpno, dbo.eqp.eqpname,
@opid, opnameTb.displaymsg,
dbo.cfct.fctid, dbo.cfct.fctno, dbo.cfct.fctname,
IIF(dbo.mold.moldid IS NOT NULL, dbo.mold.moldid, NULL),
IIF(dbo.mold.moldno IS NOT NULL, dbo.mold.moldno, ''),
IIF(dbo.mold.moldname IS NOT NULL, dbo.mold.moldname, ''),
IIF(dbo.cmoldt.moldtname IS NOT NULL, dbo.cmoldt.moldtname, '')
FROM
(
SELECT
opid, eqpid
FROM
dbo.map_op_eqp
WHERE
opid = @opid AND eqpid = @eqpid
) AS mapTb INNER JOIN
(
SELECT
actmsgid, displaymsg
FROM
dbo.actmsg
WHERE
actmsgid = @opid AND lcid = 1
) AS opnameTb ON opnameTb.actmsgid = mapTb.opid INNER JOIN
dbo.eqp ON dbo.eqp.eqpid = mapTb.eqpid INNER JOIN
dbo.cfct ON dbo.cfct.fctid = dbo.eqp.fctid LEFT JOIN
dbo.map_op_mold ON dbo.map_op_mold.eqpid = mapTb.eqpid AND dbo.map_op_mold.opid = mapTb.opid LEFT JOIN
dbo.mold ON dbo.mold.moldid = dbo.map_op_mold.moldid LEFT JOIN
dbo.cmoldbinds ON dbo.cmoldbinds.moldbindsid = dbo.mold.moldbindsid LEFT JOIN
dbo.cmoldt ON dbo.cmoldt.moldtid = dbo.mold.moldtid
FETCH NEXT FROM mdo INTO @opid, @eqpid
END
CLOSE mdo
DEALLOCATE mdo
SELECT * FROM @resultTb
(3-3/5)