if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#table') and type='U')
DROP TABLE #table
CREATE TABLE #table
(
[RECNUM] [decimal](28, 0) IDENTITY(1, 1) NOT NULL ,
ModuleCode NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
FunctionCode NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
FuncDescription NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Process_Code NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Menu_Type NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Menu_Code NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO #table(ModuleCode, FunctionCode , FuncDescription,Process_Code,Menu_Type,Menu_Code)
SELECT 'PRSF', 'PRSFRT' , 'Matrial Reservation Transfer','M6_ENT','ENTRY','100'
UNION ALL
SELECT 'PRSF', 'PRSFRC' ,'Routing Process Completion','M6_ENT','ENTRY','110'
UNION ALL
SELECT 'PRPE', 'PRPEMJ' ,'Mass ECN','M4_ENT','ENTRY','60'
UNION ALL
SELECT 'PRPM', 'PRPMMJ' ,'Mass ECN','M4_ENT','ENTRY','70'
UNION ALL
SELECT 'SAMF', 'SAMFRP' ,'Routing Process','MAST01','MAST','110'
UNION ALL
SELECT 'SAMF', 'SAMFRP' ,'Bom Routing Process Settings','MAST01','MAST','120'
UNION ALL
SELECT 'SAMF', 'SAMFCL' ,'Collection Master','MAST01','MAST','130'
UNION ALL
SELECT 'SAMF', 'SAMFSY' ,'Style Master','MAST01','MAST','140'
UNION ALL
SELECT 'SAMF','SAMFCO' ,'Color Master','MAST01','MAST','150'
UNION ALL
SELECT 'PRPM', 'PRMJU' , 'Job BOM Update','M4_ENT','ENTRY','80'
DECLARE @SeriesCode nvarchar(30)
DECLARE @ModuleCode nvarchar(30)
DECLARE @FunctionCode nvarchar(30)
DECLARE @FunctionDesc nvarchar(30)
DECLARE @RECNUM INT
DECLARE @Process_Code nvarchar(30)
DECLARE @Menu_Type nvarchar(30)
DECLARE @Menu_Code nvarchar(30)
DECLARE @LastLineNo INT
SET @SeriesCode=''
DECLARE function_cursor CURSOR FOR SELECT RECNUM FROM #table
OPEN function_cursor
FETCH NEXT FROM function_cursor INTO @RECNUM
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ModuleCode=ModuleCode,@FunctionCode=FunctionCode, @FunctionDesc=FuncDescription,@Process_Code=Process_Code,
@Menu_Type=Menu_Type,@Menu_Code=Menu_Code FROM #table WHERE RECNUM=@RECNUM
-- Check for Function
if not exists ( select * from [ADFUNC] where Module_Code = @ModuleCode and Function_Code = @FunctionCode)
begin
Update [ADMODU] Set [Last_Line_No] = [Last_Line_No] + 1
Where [Module_Code] = @ModuleCode
Select @LastLineNo = Last_Line_No from ADMODU Where Module_Code = @ModuleCode
if @LastLineNo is not null
BEGIN
Insert [ADFUNC]
([Module_Code], [Function_No], [Function_Code], [Description],
[Suspended], [Series_Option], [Series_Code],
[Created_Date], [Created_By], [Revised_Date], [Revised_By],
[OWNER_BRANCH],[SOURCE_BRANCH],[Icon])
Values
(@ModuleCode, @LastLineNo, @FunctionCode, @FunctionDesc, N'N', N'Y', @SeriesCode,
GetDate(), 'MIS', GetDate(), 'MIS', N'', N'','16')
-- coding for EMPower
--if '%SYS%'='EMP' or '%SYS%'='EMPT' begin
Insert [ADAUTD]
([User_Group], [Module_Code], [Function_No], [Function_Code], [Description],
[Suspended], [Allow_Read], [Allow_Create], [Allow_Update], [Allow_Delete], [Allow_Print],
[Allow_Post], [Allow_All_Tran])
Values
('SYSADM', @ModuleCode, @LastLineNo, @FunctionCode, @FunctionDesc,
'N' ,'Y', 'Y', 'Y', 'Y', 'Y',
'Y', 'Y')
Insert [ADMNUD]
([User_Group], [Process_Code], [Function_Code], [Description], [Menu_Type], [Menu_Code],
[Response_Type], [Suspended])
Values
('SYSADM', @Process_Code, @FunctionCode, @FunctionDesc, @Menu_Type, @Menu_Code,
'STDFUNC', 'N')
--end
-- coding for EPN
--else if '%SYS%'='EPN' begin
-- Insert [ADAUTD]
-- ([User_Group], [Module_Code], [Function_No], [Function_Code], [Description],
-- [Suspended], [Allow_Read], [Allow_Create], [Allow_Update], [Allow_Delete], [Allow_Print],
-- [Allow_Post], [Allow_All_Tran])
-- Values
-- ('EPN', @ModuleCode, @LastLineNo, @FunctionCode, @FunctionDesc,
-- 'N' ,'Y', 'Y', 'Y', 'Y', 'Y',
-- 'Y', 'Y')
-- Insert [ADMNUD]
-- ([User_Group], [Process_Code], [Function_Code], [Description], [Menu_Type], [Menu_Code],
-- [Response_Type], [Suspended])
-- Values
-- ('EPN', @Process_Code, @FunctionCode, @FunctionDesc, @Menu_Type, @Menu_Code,
-- 'STDFUNC', 'N')
--end
END
end
FETCH NEXT FROM function_cursor INTO @RECNUM
END
CLOSE function_cursor;
DEALLOCATE function_cursor;
DROP TABLE #table