DBCC TRACEON(460)
GO

Begin Transaction

DECLARE @f_user						varchar(20)
DECLARE @f_site_cd 					varchar(4)
DECLARE @f_supplier_fetch			numeric(12,0)
DECLARE @f_supplier_RowID			numeric(21,0)
DECLARE @f_vendor_code				varchar(25)
DECLARE @f_vendor_name				varchar(255)
DECLARE @f_address					varchar(80)
DECLARE @f_post_code				varchar(25)
DECLARE @f_city						varchar(50)
DECLARE @f_state					varchar(25)
DECLARE @f_phone_no					varchar(25)
DECLARE @f_mobile_phone				varchar(25)
DECLARE @f_contact					varchar(80)
DECLARE @f_email					varchar(60)
DECLARE @f_work_grp					varchar(4)

SELECT @f_site_cd = 'H10'
SELECT @f_user = 'Admin'
SELECT @f_work_grp = NULL
select @f_address = NULL
select @f_post_code = NULL
select @f_city = NULL
select @f_state = NULL
select @f_phone_no = NULL
select @f_mobile_phone = NULL
select @f_contact = NULL
select @f_email = NULL

Declare supplier_cur cursor for	
	SELECT [Code], [Desc]
	FROM [Vendor Cheno]
	ORDER BY 1

Open supplier_cur

SELECT @f_supplier_fetch = 0

WHILE (@f_supplier_fetch <> -1)
BEGIN

FETCH NEXT FROM supplier_cur INTO @f_vendor_code, @f_vendor_name

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

		INSERT INTO sup_mst (
							site_cd,
							sup_mst_supplier_cd,
							sup_mst_status,
							sup_mst_rating,
							sup_mst_curr_code,
							sup_mst_desc,
							sup_mst_smallbu,
							sup_mst_on_bid_lst,
							sup_mst_insurance,
							sup_mst_hub,
							sup_mst_iso,
							sup_mst_blanketpo,
							sup_mst_services,
							audit_user,
							audit_date,
							sup_mst_create_by,
							sup_mst_create_date )
					VALUES(
							@f_site_cd, --site_cd
							@f_vendor_code, --sup_mst_supplier_cd
							'ACT', --sup_mst_status
							'1.0000', --sup_mst_rating
							'RM', --sup_mst_curr_code
							@f_vendor_name, --sup_mst_desc
							'0', --sup_mst_smallbu
							'0', --sup_mst_on_bid_lst
							'0', --sup_mst_insurance
							'0', --sup_mst_hub
							'0', --sup_mst_is,
							'0', --sup_mst_blanketpo
							@f_work_grp, --sup_mst_services
							@f_user, --audit_user
							getdate(), --audit_date
							@f_user, --sup_mst_create_by
							getdate() --sup_mst_create_date
							)
			
		IF @@ERROR <> 0 
		BEGIN
			DEALLOCATE supplier_cur
			Rollback Transaction
			RAISERROR('Could Not Insert Supplier Master Record (sup_mst).',16,-1 )
			RETURN
		END

		SELECT 		@f_supplier_RowID = RowID
		FROM		sup_mst
		WHERE		site_cd = @f_site_cd
		AND			sup_mst_supplier_cd = @f_vendor_code

		INSERT INTO sup_det (
						site_cd,
						mst_RowID,
						sup_det_contact1,
						sup_det_contact2,
						sup_det_address1,
						sup_det_address2,
						sup_det_city,
						sup_det_state,
						sup_det_province,
						sup_det_country,
						sup_det_postal_code,
						sup_det_phone,
						sup_det_fax_phone,
						sup_det_mobile_phone,
						sup_det_email_id,
						sup_det_rate_dt,
						sup_det_rate_dc,
						sup_det_rate_qa,
						sup_det_rate_odt,
						sup_det_rate_odc,
						sup_det_rate_oqa,
						audit_user,
						audit_date )
				VALUES (
						@f_site_cd, --site_cd
						@f_supplier_RowID, --mst_RowID
						@f_contact, --sup_det_contact1
						NULL, --sup_det_contact2
						@f_address, --sup_det_address1
						NULL, --sup_det_address2
						@f_city, --sup_det_city
						@f_state, --sup_det_state
						NULL, --sup_det_province
						NULL, --sup_det_country
						@f_post_code, --sup_det_postal_code
						@f_phone_no, --sup_det_phone
						NULL, --sup_det_fax_phone
						@f_mobile_phone, --sup_det_mobile_phone
						@f_email, --sup_det_email_id
						'0.0000', --sup_det_rate_dt
						'0.0000', --sup_det_rate_dc
						'0.0000', --sup_det_rate_qa
						'0.0000', --sup_det_rate_odt
						'0.0000', --sup_det_rate_odc
						'0.0000', --sup_det_rate_oqa
						@f_user, --audit_user
						getdate() --audit_date
						)

		IF @@ERROR <> 0 
		BEGIN
			DEALLOCATE supplier_cur
			rollback transaction
			RAISERROR('Could Not Insert Supplier Detail Record (sup_det).',16,-1 )
			RETURN
		END 		
    END     
END

DEALLOCATE supplier_cur
-- Rollback Transaction
 Commit Transaction
Return
GO