Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @filepath | varchar | 500 | |
| @filename | varchar | 100 | |
| @username | varchar | 50 | |
| @tipe | varchar | 50 | |
| @trx | varchar | 50 | |
| @id | bigint | 8 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spuload_stockopname
(@filepath varchar(500), @filename varchar(100), @username varchar(50), @tipe varchar(50), @trx varchar(50), @id bigint )
AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
if @tipe='STORM'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_rm'' and F3=''qty_stock_opname_rm'' and f4=''satuan_stock_opname_rm'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME RAW MATERIAL TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='STOFG'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''assy_code'' AND F2=''tgl_stock_opname_fg'' and F3=''qty_stock_opname_fg'' and f4=''satuan_stock_opname_fg'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME ASSY/FINISHGOOD TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='STOSS'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_sisa_scrap'' and F3=''qty_stock_opname_sisa_scrap'' and f4=''satuan_stock_opname_sisa_scrap'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME SISA DAN SCRAP TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='STOMPK'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_mpk'' and F3=''qty_stock_opname_mpk'' and f4=''satuan_stock_opname_mpk'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME MESIN DAN PERALATAN KANTOR TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='STOBP'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_bp'' and F3=''qty_stock_opname_bp'' and f4=''satuan_stock_opname_bp'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME BAHAN PENOLONG TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
END
GO
GO
CREATE PROCEDURE dbo.spuload_stockopname
(@filepath varchar(500), @filename varchar(100), @username varchar(50), @tipe varchar(50), @trx varchar(50), @id bigint )
AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
if @tipe='STORM'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_rm'' and F3=''qty_stock_opname_rm'' and f4=''satuan_stock_opname_rm'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME RAW MATERIAL TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='STOFG'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''assy_code'' AND F2=''tgl_stock_opname_fg'' and F3=''qty_stock_opname_fg'' and f4=''satuan_stock_opname_fg'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME ASSY/FINISHGOOD TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='STOSS'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_sisa_scrap'' and F3=''qty_stock_opname_sisa_scrap'' and f4=''satuan_stock_opname_sisa_scrap'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME SISA DAN SCRAP TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='STOMPK'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_mpk'' and F3=''qty_stock_opname_mpk'' and f4=''satuan_stock_opname_mpk'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME MESIN DAN PERALATAN KANTOR TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='STOBP'
begin
if @trx = 'add'
begin
if (select COUNT(*) from [tbl_uploadstockopname] where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_bp'' and F3=''qty_stock_opname_bp'' and f4=''satuan_stock_opname_bp'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME BAHAN PENOLONG TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_uploadstockopname] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES UPLOAD'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_uploadstockopname set status_upload='Deleted' where id_stockopname_upload = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
END
GO
Depends On
1Used By
No items found