DBCC TRACEON(460)
GO

Begin Transaction

DECLARE @f_user						varchar(20)
DECLARE @f_site_cd 					varchar(4)
DECLARE @f_asset_fetch				numeric(12,0)
DECLARE @f_asset_rowid				numeric(21,0)

DECLARE @f_asset_no					varchar(80)
DECLARE @f_description				varchar(80)
DECLARE @f_level					varchar(80)
DECLARE @f_location					varchar(80)
DECLARE @f_serial_no				varchar(80)
DECLARE @f_model					varchar(80)
DECLARE @f_supplier					varchar(80)
DECLARE @f_work_grp					varchar(25)
DECLARE @f_block					varchar(12)
DECLARE @f_asset_cost				numeric(12,2)				
DECLARE @f_ast_det_depr_term		varchar(4)
									
DECLARE @f_trade					varchar(1)
DECLARE @f_ppm_flag					varchar(1)
DECLARE @f_perm_id					varchar(30)
DECLARE @f_ppm_frequency			varchar(25)
DECLARE @f_warranty_terms			varchar(25)
DECLARE @f_warranty_start			datetime
DECLARE @f_warranty_end				datetime
DECLARE @f_criticality				varchar(1)
DECLARE @f_manufacturer_brand		varchar(80)
DECLARE @f_service_vendor			varchar(80)
DECLARE @f_po_loa					varchar(80)
DECLARE @f_manufacturer_country		varchar(80)
DECLARE @f_origin					varchar(80)
DECLARE @f_eq_flag					varchar(2)
DECLARE @f_user_area				varchar(25)
DECLARE @f_long_description			varchar(255)
DECLARE	@f_purchase_date			datetime
DECLARE @f_warranty_type			varchar(80)
DECLARE @f_umdns					varchar(30)
DECLARE @f_comm_date				datetime
DECLARE @f_mda						varchar(80)
DECLARE @f_asset_owner				varchar(25)
DECLARE @f_ast_mst_asset_code		varchar(12)
DECLARE @f_ast_mst_asset_status		varchar(3)
DECLARE @f_code_no					varchar(80)
DECLARE @f_po_date					datetime

SELECT @f_site_cd = 'H30'
SELECT @f_user = 'Admin'
SELECT @f_block = @f_site_cd
SELECT @f_origin = NULL
SELECT @f_warranty_type = NULL

Declare asset_cur cursor for

SELECT	
	LTRIM(RTRIM([Asset No.])),
	'B',
	1,
	LTRIM(RTRIM([Code Asset])),
	LTRIM(RTRIM([EQUIPMENT])),
	[Asset Type]+' asset, DO Number: '+[DO NO]+', Invoice Number: '+[INVOICE],
	LTRIM(RTRIM([LOCATION])),
	LTRIM(RTRIM([Floor level])),
	LTRIM(RTRIM([SERVICES])),
	substring([MANUFACTURER],  0, 26),
	LTRIM(RTRIM([MODEL])),
	LTRIM(RTRIM([SERIAL NO])),
	LTRIM(RTRIM([SUPPLIER])),
	NULL,
	NULL, 
	[PURCHASE PRICE (RM)],
	[PO NUMBER],
	'HOSPITAL',
	[WARRANTY END],
	[YEAR PURCHASE (Refer PO)],
	0,
	ISNULL([WARRANTY START], getdate()),
	[Finance No.],
	'-',
	'ACT',
	'N',
	LTRIM(RTRIM([BEMS/ FEMS])),
	LTRIM(RTRIM([Block Building])),
	[T&C Date],
	[MDA Cert No],
	[WARRANTY],
	[PO DATE]
FROM [00 Asset Syarifah BDO]
WHERE [Asset No.] is not null

Open asset_cur

SELECT @f_asset_fetch = 0

WHILE (@f_asset_fetch <> -1)
BEGIN

FETCH NEXT FROM asset_cur INTO

		@f_asset_no,
		@f_trade,
		@f_ppm_flag,
		@f_umdns,
		@f_description,
		@f_long_description,
		@f_location,
		@f_level,
		@f_user_area,
		@f_manufacturer_brand,
		@f_model,
		@f_serial_no,
		@f_supplier,
		@f_manufacturer_country,
		@f_service_vendor,
		@f_asset_cost,
		@f_po_loa,
		@f_asset_owner,
		@f_warranty_end,
		@f_purchase_date,
		@f_ast_det_depr_term,
		@f_warranty_start,
		@f_perm_id,
		@f_ast_mst_asset_code,
		@f_ast_mst_asset_status,
		@f_criticality,
		@f_work_grp,
		@f_block,
		@f_comm_date,
		@f_mda,
		@f_warranty_terms,
		@f_po_date

	SELECT @f_asset_fetch = @@FETCH_STATUS
	IF (@f_asset_fetch <> - 1)
	BEGIN

	select @f_eq_flag = case when @f_ppm_flag = 1 then 1 else 0 end

		INSERT INTO ast_mst (
							site_cd,
							ast_mst_asset_no,
							ast_mst_asset_type,
							ast_mst_asset_grpcode,
							ast_mst_asset_status,
							ast_mst_work_area,
							ast_mst_cri_factor,
							ast_mst_cost_center,
							ast_mst_asset_locn,
							ast_mst_asset_shortdesc,
							ast_mst_perm_id,
							ast_mst_asset_code,
							audit_user,
							audit_date,
							ast_mst_parent_flag,
							ast_mst_auto_no,
							ast_mst_create_by,
							ast_mst_create_date,
							ast_mst_wrk_grp,
							ast_mst_print_count,
							ast_mst_ast_lvl,
							ast_mst_service,
							ast_mst_equipment_flag,
							ast_mst_trade,
							ast_mst_ppm_flag,
							ast_mst_asset_longdesc,
							ast_mst_safety_rqmts)
					VALUES (
							@f_site_cd,
							@f_asset_no,
							@f_trade,
							@f_umdns,
							@f_ast_mst_asset_status,
							@f_block,
							@f_criticality,
							@f_site_cd,
							@f_location,
							@f_description,
							@f_perm_id,
							'-',
							@f_user,
							getdate(),
							'0',
							'1',
							@f_user,
							getdate(),
							@f_work_grp,
							'0',
							@f_level,
							@f_work_grp,
							@f_eq_flag,
							@f_trade,
							@f_ppm_flag,
							@f_long_description +', Code No: '+@f_code_no,
							@f_asset_owner)
		IF @@ERROR <> 0 
		BEGIN
			DEALLOCATE asset_cur
			Rollback Transaction
			RAISERROR('Could Not Insert Asset Master Record (ast_mst).',16,-1 )
			RETURN
		END

		SELECT 		@f_asset_rowid = RowID
		FROM		ast_mst
		WHERE		site_cd = @f_site_cd
		AND			ast_mst_asset_no = @f_asset_no

		INSERT INTO ast_det (
						site_cd,
						mst_RowID,
						ast_det_asset_cost,
						ast_det_mtdlabcost,
						ast_det_mtdmtlcost,
						ast_det_mtdconcost,
						ast_det_ytdlabcost,
						ast_det_ytdmtlcost,
						ast_det_ytdconcost,
						ast_det_ltdlabcost,
						ast_det_ltdmtlcost,
						ast_det_ltdconcost,
						ast_det_warranty_date,
						ast_det_repl_cost,
						ast_det_taxable,		--1
						ast_det_serial,			--2
						ast_det_varchar9,		--3
						ast_det_varchar10,		--4
						ast_det_varchar11,		--5
						ast_det_varchar12,		--6
						ast_det_varchar13,		--7
						ast_det_varchar14,		--7
						ast_det_varchar15,		--7
						ast_det_varchar16,		--8
						ast_det_varchar18,		--9
						ast_det_varchar19,		--10
						ast_det_varchar20,		--11
						ast_det_varchar21,		--12
						ast_det_varchar22,		--13
						ast_det_varchar27,		--14
						audit_user,
						audit_date,
						ast_det_purchase_date,
						ast_det_acc_depr_cost,
						ast_det_net_book_value,
						ast_det_depr_method,
						ast_det_modelno,
						ast_det_mfg_cd,
						ast_det_datetime6,
						ast_det_varchar5,
						ast_det_depr_term,
						ast_det_varchar17,
						ast_det_datetime10,
						ast_det_varchar23,
						ast_det_numeric2,
						ast_det_datetime9)
				VALUES (
						@f_site_cd,
						@f_asset_rowid,
						@f_asset_cost,
						'0.0000',
						'0.0000',
						'0.0000',
						'0.0000',
						'0.0000',
						'0.0000',
						'0.0000',
						'0.0000',
						'0.0000',
						@f_warranty_end,
						'0.0000',
						'N',					--1
						@f_serial_no,			--2
						'0',					--3
						'0',					--4
						NULL,					--5
						@f_supplier,			--6
						@f_po_loa,				--7
						@f_manufacturer_country,--7
						@f_origin,				--7
						@f_service_vendor,		--8
						@f_warranty_terms,		--9
						'0',					--10
						'0',					--11
						'0',					--12
						'0',					--13
						@f_ppm_frequency,		--14
						@f_user,
						getdate(),
						@f_purchase_date, --ast_Det_purchase_date
						'0.0000',
						'0.0000',
						'SL',
						@f_model,
						@f_manufacturer_brand,
						@f_warranty_start,
						@f_user_area,
						@f_ast_det_depr_term,
						@f_warranty_type,
						@f_comm_date,
						@f_mda,
						NULL,
						@f_po_date)
		IF @@ERROR <> 0 
		BEGIN
			DEALLOCATE asset_cur
			rollback transaction
			RAISERROR('Could Not Insert Asset Master Record (ast_det).',16,-1 )
			RETURN
		END   
		
		INSERT ast_aud (
			site_cd,
			mst_RowID,
			ast_aud_asset_no,
			ast_aud_status,
			ast_aud_originator,
			ast_aud_start_date,
			ast_aud_end_date,
			ast_aud_duration,
			audit_user,
			audit_date
			)
		VALUES (
			@f_site_cd,
			@f_asset_rowid,
			@f_asset_no,
			'ACT',
			@f_user,
			getdate(),
			NULL,
			NULL,
			@f_user,
			getdate()
			)
		IF @@ERROR <> 0 
		BEGIN
			DEALLOCATE asset_cur
			Rollback Transaction
			RAISERROR('Could Not Insert Asset Audit Record (ast_aud).',16,-1 )
			RETURN
		END
		
    END     
END

DEALLOCATE asset_cur
-- Rollback Transaction
 Commit Transaction
Return
GO