USE [PWC_ADDON]
GO
/****** Object: Trigger [dbo].[upload_info_trigger] Script Date: 9/8/2021 3:48:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[upload_info_trigger]
on [dbo].[upload_excel_info]
after insert
as
begin
EXEC xp_cmdshell ‘D:\pwc_interface\job-bat\Notification\uploadExcel\execUploadExcel.bat’
end
USE [PWC_ADDON]
GO
/****** Object: StoredProcedure [dbo].[Notification_RowsToColumn] Script Date: 9/8/2021 3:53:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Notification_RowsToColumn]
(@IF_TYPE nvarchar (20),
@IF_STATUS int
)
AS
BEGIN
DECLARE @COUNT int
SET @COUNT = (SELECT COUNT(1) AS COUN FROM A_XTM021 where TYPE = @IF_TYPE and STATUS = @IF_STATUS)
IF @COUNT > 0
BEGIN
INSERT INTO A_XTM022(LIST,TYPE,STATUS)
SELECT STUFF((
SELECT char(10)+left(PRTY_CD+’ ‘,12)+’ ‘+left(PRTY_CTGR+’ ‘,10)+’ ‘+
left(CREATOR+’ ‘,17)+’ ‘+CASE WHEN status=0 THEN left(CONVERT(varchar(100),cast(CREATE_DATE as datetime),120)+’ ‘,19)
ELSE left(CONVERT(varchar(100), dateadd(s, cast(CREATE_DATE as bigint)/1000 ,‘1970-1-1 08:00:00’), 120)+’ ‘,19)
END
+’ ‘+left(UPDATED_BY +’ ‘,22)+’ ‘+left(CONVERT(varchar(100), dateadd(s, cast(LAST_UPDATE_DATE as bigint)/1000 ,‘1970-1-1 08:00:00’), 120)+’ ‘,19)+’ ‘+left(PRTY_NM+’ ‘,30)
FROM A_XTM021 where TYPE=@IF_TYPE and STATUS=@IF_STATUS FOR XML PATH(’’)),1,1,’’) AS CodeList,@IF_TYPE,@IF_STATUS
END
END