set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


--******************************************************************************
--M?c dích: Chuy?n d? li?u t? các b?ng t?m vào b?ng g?c (Dung cho cap Huyen)
--Th?c hi?n: + Chuy? d? li?u theo th? t? sau
--		Tmp_FieldWorker 	--> FieldWorker
--		Tmp_Address		--> Address
--		Tmp_HouseHold	--> HouseHold
--		Tmp_Personal		--> Personal
--		Tmp_FamilyPlanning	--> FamilyPlanning
--Ngu?i vi?t: Nguy?n Bá Hùng
--Ngày vi?t : 20/06/2005
--******************************************************************************
ALTER PROCEDURE [dbo].[dn_MoveDataDistrict] 
	@sCommuneID varchar(10),--Mã xa phuong
	@sDelOldData varchar(10)--Có xóa s? li?u cu không
AS
------------------------Khai báo bi?n c?c b?---------------------------------------
--***********Khai báo các bi?n luu Identity
DECLARE @iAddressID int, @iHouseHoldID int, @iPersonalID int
--***********Khái báo thông tin c?ng tác viên
DECLARE @iFW_ID int, @sFW_FirstName nvarchar(40), @sFW_LastName nvarchar(40), @daFW_HireDate datetime, @sFW_SexID char(1), @bFW_IsCadre bit, @sFW_RegionID varchar(10), @daFW_DateUpdate datetime, @iFW_UserID int
--***********Khai báo thông tin KHHGÐ
DECLARE @sFP_ContraDate varchar(10), @sFP_ContraCode varchar(5), @sFP_RegionID varchar(10), @daFP_DateUpdate datetime, @iFP_UserID int
--***********Khai báo thông tin l?ch s? KHHGÐ
DECLARE @sFPH_ContraDate varchar(10), @sFPH_ContraCode varchar(5), @sFPH_RegionID varchar(10), @daFPH_DateUpdate datetime, @iFPH_UserID int
--***********Khai báo thông tin d?a ch? 
DECLARE @iAdd_ID int, @sAdd_Name nvarchar(255), @sAdd_Levels char(1), @sAdd_Parent varchar(10), @sAdd_Notes nvarchar(255) , @sAdd_FullAddress nvarchar(255), 
	@sAdd_RegionID varchar(10), @daAdd_DateUpdate datetime, @iAdd_UserID int
--***********Khai báo thông tin h?
DECLARE @iHH_ID int, @sHH_Code varchar(10), @sHH_Number nvarchar(100), @iHH_AddressID int, @sHH_RegionID varchar(10), @daHH_StartDate datetime, @daHH_EndDate datetime, 
	@iHH_UserID int, @iIsBigHH int, @sHH_Status char(1), @sHH_Notes nvarchar(300)
--***********Khai báo thông tin bi?n d?ng
DECLARE @sC_RegionID varchar(10), @iC_UserID int, @daC_DateUpdate datetime, @daC_ChangeDate datetime, @sC_ChangeType varchar(10), @sC_Source varchar(15), @sC_Dest varchar(15),
	@sC_Status nvarchar(10), @sC_Notes varchar(10)
--***********Khai báo thông tin cá nhân
DECLARE @iPer_ID int, @sPer_FirstName nvarchar(40), @sPer_LastName nvarchar(50), @sPer_DOB varchar(10),  @sPer_POB varchar(10), @sPer_NatCode char(3), @sPer_RelCode varchar(10), 
	@sPer_EthCode varchar(10), @sPer_ResCode varchar(10), @sPer_EduCode varchar(10), @sPer_TecCode varchar(10), @sPer_MarCode varchar(10), @sPer_InvCode varchar(10),
	@sPer_SexID char(1), @sPer_EduLevel nvarchar(20), @iPer_HouseHoldID int, @sPer_PersonStatus varchar(10), @daPer_DateUpdate datetime, @iPer_UserID int, 
	@daPer_StartDate datetime, @daPer_ChangeDate datetime, @daPer_EndDate datetime, @sPer_Notes nvarchar(300)
--***********Khai bao Thong ke hoat dong
DECLARE @sORegionID varchar(15), @sYear varchar(10), @sQuarter varchar(10), @sMonth varchar(10), @iPropagate_Number varchar(10), @iPano_Number varchar(10), @iBrochure_Number varchar(10),
	@iBook_Number varchar(10), @iCattsette_Number varchar(10), @iTwoChildren varchar(10), @iThreeChildren varchar(10), @iDistrict_Cadre varchar(10), 
	@iFemale_District_Cadre varchar(10), @iContract_District_Cadre varchar(10), @iFieldworker_Cadre varchar(10), @iFemale_Fieldworker1 varchar(10),
	@iMoney_ChildProtection varchar(10), @iExpense_Carrer_Commune varchar(10), @iExpense_Carrer_District varchar(10), @iCadre_Province varchar(10),
	@iExpense_Carrer_Superior varchar(10), @iSpend_Population varchar(10), @iSpend_Child varchar(10), @iSpend_Family varchar(10), @iSpend_Another varchar(10)
--Khai báo thông tin SKSS
--DECLARE @sGen_Date datetime, @iGenerate_Code int, @iBirth_Number int, @iWeight1 int, @iWeight2 int, @iWeight3 int, @iWeight4 int, @sPlaceOfBirth varchar(15),
	--@iPregnant_Check_Number int, @bPregnant_Abort_No_Use bit, @sGH_RegionID varchar(15), @daGH_DateUpdate datetime, @iGH_UserID int 
DECLARE @iGenerate_Code int, @sGen_Date datetime,@bExport_Status bit,@iBirth_Number int, @iWeight1 int,@iWeight2 int,@iWeight3 int,@iWeight4 int,@sPlaceOfBirth varchar(15),@iPregnant_Check_Number int,@bPregnant_Abort_No_Use bit,@sGH_RegionID varchar(15)
	,@iGH_UserID int,@sDate_Update datetime,@sCreatedDate datetime,@sDeliver varchar(6),@sDate_SLSS datetime,@sResult_SLSS varchar(2),@sDate_SLTS1 datetime,@sResult_SLTS1 varchar(2),@sDate_SLTS2 datetime,@sResult_SLTS2 varchar(2)
--------------------------------------------------------------------------------------------------
BEGIN TRAN
--Xóa h?t s? li?u c?a xa phuong
IF(@sDelOldData = 'True')
BEGIN
	DELETE FamilyPlanning 	WHERE Region_ID = @sCommuneID
	DELETE GenerateHealth	WHERE Region_ID = @sCommuneID
	DELETE Change		WHERE Region_ID = @sCommuneID
	DELETE FamilyPlanningHistory	WHERE Region_ID = @sCommuneID
	DELETE Personal 		WHERE Region_ID = @sCommuneID
	DELETE HouseHold 		WHERE Region_ID = @sCommuneID
	DELETE Address 		WHERE Region_ID = @sCommuneID
	DELETE Fieldworker 		WHERE Region_ID = @sCommuneID
	DELETE OperationStatisticInfor	WHERE Region_ID = @sCommuneID
END
--******************Khai bao Cursor th?ng kê ho?t d?ng
DECLARE Operation_Cur CURSOR
	FOR	SELECT [Region_ID], [RptQuarter], [RptYear], [RptMonth],[Total_Propagate_Number], [Total_Pano_Number], [Total_Brochure_Number], [Total_Book_Number], 
		[Total_Cattsette_Number], [Two_Children_Pair], [MoreThanThreeChildren_Pair], [District_Cadre], [Female_District_Cadre], [Contract_District_Cadre], 
		[Fieldworker_Cadre], [Female_Fieldworker1], [Money_ChildProtection], [Expense_Carrer_Commnue], [Expense_Carrer_District], [Cadre_Province], [Expense_Carrer_Superior],
		[Spend_Population], [Spend_Child], [Spend_Family], [Spend_Another]
	FROM [Tmp_OperationStatisticInfor] WHERE [Region_ID] = @sCommuneID
OPEN Operation_Cur
BEGIN
	FETCH NEXT FROM Operation_Cur INTO @sORegionID, @sQuarter, @sYear, @sMonth, @iPropagate_Number, @iPano_Number, @iBrochure_Number, @iBook_Number, @iCattsette_Number, 
	@iTwoChildren, @iThreeChildren, @iDistrict_Cadre, @iFemale_District_Cadre, @iContract_District_Cadre, @iFieldworker_Cadre, @iFemale_Fieldworker1, @iMoney_ChildProtection,
	@iExpense_Carrer_Commune, @iExpense_Carrer_District, @iCadre_Province, @iExpense_Carrer_Superior, @iSpend_Population, @iSpend_Child, @iSpend_Family, @iSpend_Another
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		INSERT INTO [OperationStatisticInfor] ([Region_ID], [RptQuarter], [RptYear], [RptMonth], [Total_Propagate_Number], [Total_Pano_Number], [Total_Brochure_Number], [Total_Book_Number], 
		[Total_Cattsette_Number], [Two_Children_Pair], [MoreThanThreeChildren_Pair], [District_Cadre], [Female_District_Cadre], [Contract_District_Cadre], [Fieldworker_Cadre],
		[Female_Fieldworker1], [Money_ChildProtection], [Expense_Carrer_Commnue], [Expense_Carrer_District], [Cadre_Province], [Expense_Carrer_Superior], [Spend_Population], 
		[Spend_Child], [Spend_Family], [Spend_Another])
		VALUES (@sCommuneID , @sQuarter , @sYear, @sMonth, @iPropagate_Number, @iPano_Number , @iBrochure_Number , @iBook_Number , @iCattsette_Number , @iTwoChildren ,
			@iThreeChildren , @iDistrict_Cadre , @iFemale_District_Cadre, @iContract_District_Cadre, @iFieldworker_Cadre , @iFemale_Fieldworker1 , @iMoney_ChildProtection ,
			@iExpense_Carrer_Commune , @iExpense_Carrer_District , @iCadre_Province , @iExpense_Carrer_Superior , @iSpend_Population , @iSpend_Child , @iSpend_Family , 
			@iSpend_Another)	 
		FETCH NEXT FROM Operation_Cur INTO @sORegionID, @sQuarter, @sYear, @sMonth, @iPropagate_Number, @iPano_Number, @iBrochure_Number, @iBook_Number, @iCattsette_Number, 
		@iTwoChildren, @iThreeChildren, @iDistrict_Cadre, @iFemale_District_Cadre, @iContract_District_Cadre, @iFieldworker_Cadre, @iFemale_Fieldworker1, @iMoney_ChildProtection,
		@iExpense_Carrer_Commune, @iExpense_Carrer_District, @iCadre_Province, @iExpense_Carrer_Superior, @iSpend_Population, @iSpend_Child, @iSpend_Family, @iSpend_Another
	END
END
DEALLOCATE Operation_Cur
--****************** Ket thuc thong ke hoat dong
--******************Khai báo Cursor luu thông tin c?ng tác viên
DECLARE FW_Cur CURSOR
	FOR	SELECT [FieldWorker_ID], [First_Name], [Last_Name], [Hire_Date],  [Sex_ID], [IsCadre], [Date_Update], [User_ID]  
		FROM [Tmp_FieldWorker] WHERE [Region_ID] like @sCommuneID
OPEN FW_Cur
BEGIN
	--DECLARE @iHouseHoldID int, @iPersonalID int
	FETCH NEXT FROM FW_Cur INTO @iFW_ID, @sFW_FirstName, @sFW_LastName, @daFW_HireDate,  @sFW_SexID, @bFW_IsCadre, @daFW_DateUpdate, @iFW_UserID
	WHILE (@@FETCH_STATUS=0)
	BEGIN	
		--******************Chuy?n thông tin c?ng tác viên
		INSERT INTO [FieldWorker]([FieldWorker_ID], [First_Name], [Last_Name], [Hire_Date], [Region_ID], [Sex_ID], [IsCadre], [Export_Status], [Date_Update], [User_ID])
		VALUES(@iFW_ID, @sFW_FirstName, @sFW_LastName, @daFW_HireDate, @sCommuneID, @sFW_SexID, @bFW_IsCadre, 0, @daFW_DateUpdate, @iFW_UserID)
		--Khai báo Cursor luu thông tin d?a ch?
		DECLARE Add_Cur CURSOR
			FOR  	SELECT [Address_ID], [Address_Name], [Levels], [Parent], [Notes], [Full_Address], [Date_Update], [User_ID]  FROM [Tmp_Address]
				WHERE [Region_ID] like @sCommuneID and [FieldWorker_ID] = @iFW_ID
		OPEN Add_Cur
		BEGIN
			FETCH NEXT FROM Add_Cur INTO @iAdd_ID, @sAdd_Name, @sAdd_Levels, @sAdd_Parent, @sAdd_Notes, @sAdd_FullAddress, @daAdd_DateUpdate, @iAdd_UserID
			WHILE (@@FETCH_STATUS=0)
			BEGIN
				--******************Chuy?n thông tin d?a ch?
				INSERT INTO [Address]([FieldWorker_ID], [Address_Name], [Levels], [Parent], [Region_ID], [Notes], [Full_Address], [Export_Status], [Date_Update], [User_ID])
				VALUES(@iFW_ID, @sAdd_Name, @sAdd_Levels, @sAdd_Parent, @sCommuneID, @sAdd_Notes, @sAdd_FullAddress, 0,@daAdd_DateUpdate, @iAdd_UserID)
				SET @iAddressID = @@IDENTITY
				--******************Khai báo Cursor luu thông tin H?
				DECLARE Ho_Cur CURSOR
					FOR	SELECT [HouseHold_ID], [HouseHold_Code], [HouseHold_Number], [Start_Date], [End_Date], [HouseHold_Status], [IsBigHouseHold], [Notes], [User_ID] FROM  [Tmp_HouseHold] 
						WHERE [Region_ID] = @sCommuneID and [Address_ID] = @iAdd_ID
				OPEN Ho_Cur
				BEGIN
					FETCH NEXT FROM Ho_Cur INTO @iHH_ID, @sHH_Code, @sHH_Number, @daHH_StartDate, @daHH_EndDate,  @sHH_Status, @iIsBigHH, @sHH_Notes, @iHH_UserID
					WHILE (@@FETCH_STATUS=0)
					BEGIN
						--******************Chuy?n thông tin h?
						INSERT INTO [HouseHold]([HouseHold_Code], [Address_ID], [Region_ID], [HouseHold_Number], [Export_Status], [HouseHold_Status], [Start_Date], [End_Date], [IsBigHouseHold], [Notes], [User_ID])
						VALUES(@sHH_Code, @iAddressID, @sCommuneID, @sHH_Number, 0, @sHH_Status, @daHH_StartDate, @daHH_EndDate, @iIsBigHH, @sHH_Notes, @iHH_UserID)
						SET @iHouseHoldID = @@IDENTITY
						--******************Khai báo Cursor luu thông tin cá nhân
						DECLARE Person_Cur CURSOR
						FOR	SELECT [Personal_ID],[First_Name], [Last_Name], [DateOfBirth], [PlaceOfBirth], [Nationality_Code], [Relation_Code], 
								[Ethnic_Code], [Residence_Code], [Education_Code],[Technical_Code], [Marital_Code], [Sex_ID],
								 [Education_Level], [Person_Status], [Invalid_Code], [Start_Date], [Change_Date], [End_Date], [Notes], [User_ID]
							FROM [Tmp_Personal] 
							WHERE [Region_ID] = @sCommuneID and [HouseHold_ID] = @iHH_ID
						OPEN Person_Cur
						BEGIN
							FETCH NEXT FROM Person_Cur INTO @iPer_ID, @sPer_FirstName, @sPer_LastName, @sPer_DOB, 
							@sPer_POB, @sPer_NatCode, @sPer_RelCode, @sPer_EthCode, @sPer_ResCode, @sPer_EduCode, @sPer_TecCode, 
							@sPer_MarCode, @sPer_SexID, @sPer_EduLevel, @sPer_PersonStatus, @sPer_InvCode, @daPer_StartDate, @daPer_ChangeDate,
							@daPer_EndDate, @sPer_Notes, @iPer_UserID
							WHILE (@@FETCH_STATUS = 0)
							BEGIN
								--******************Chuy?n thông tin cá nhân
								INSERT [Personal]( [First_Name], [Last_Name], [DateOfBirth], [PlaceOfBirth], 
									[Nationality_Code], [Relation_Code], [Ethnic_Code], [Residence_Code], [Education_Code],
									[Technical_Code], [Marital_Code], [Sex_ID], [Region_ID], [HouseHold_ID], [Person_Status], 
									[Education_Level], [Invalid_Code], [Start_Date], [Change_Date], [End_Date], [Notes], [User_ID])
								VALUES (@sPer_FirstName, @sPer_LastName, @sPer_DOB, @sPer_POB, 
									@sPer_NatCode, @sPer_RelCode, @sPer_EthCode, @sPer_ResCode, @sPer_EduCode,
									@sPer_TecCode, @sPer_MarCode, @sPer_SexID, @sCommuneID, @iHouseHoldID, @sPer_PersonStatus,
									@sPer_EduLevel, @sPer_InvCode, @daPer_StartDate, @daPer_ChangeDate,
									@daPer_EndDate, @sPer_Notes, @iPer_UserID)
								SET @iPersonalID = @@IDENTITY
								
								--******************Khai bao Cursor bi?n d?ng
								DECLARE Change_Cur CURSOR
									FOR	SELECT [Region_ID], [Date_Update], [Change_Date], [ChangeType_Code], [Source], [Destination], [Status], [Notes], [User_ID]
									FROM [Tmp_Change] WHERE [Region_ID] = @sCommuneID AND [Personal_ID] = @iPer_ID
								OPEN Change_Cur
								BEGIN
									FETCH NEXT FROM Change_Cur INTO @sC_RegionID, @daC_DateUpdate, @daC_ChangeDate, @sC_ChangeType, @sC_Source, @sC_Dest,
										@sC_Status, @sC_Notes, @iC_UserID
									WHILE (@@FETCH_STATUS = 0)
									BEGIN
										INSERT INTO [Change]  ([Personal_ID], [Region_ID], [Date_Update], [Change_Date], [ChangeType_Code], 
											[Source], [Destination], [Status], [Notes], [User_ID], [Export_Status])
										VALUES (@iPersonalID, @sC_RegionID, @daC_DateUpdate, @daC_ChangeDate, @sC_ChangeType, @sC_Source, @sC_Dest,
											@sC_Status, @sC_Notes, @iC_UserID, 0)
										FETCH NEXT FROM Change_Cur INTO @sC_RegionID, @daC_DateUpdate, @daC_ChangeDate, @sC_ChangeType, @sC_Source, @sC_Dest,
										@sC_Status, @sC_Notes, @iC_UserID
									END
								END
								DEALLOCATE Change_Cur
								--******************K?t thúc thông tin SKSS
								--******************Khai bao Cursor S?c kh?e SS
								DECLARE GH_Cur CURSOR
									--FOR	SELECT [Generate_Code], [Gen_Date], [Birth_Number], [Weight1], [Weight2], [Weight3], [Weight4], [PlaceOfBirth],
									--	[Pregnant_Check_Number], [Pregnant_Abort_No_Use], [Region_ID], [Date_Update], [User_ID]
									--DECLARE @iGenerate_Code int, @sGen_Date datetime,@bExport_Status bit,@iBirth_Number int, @iWeight1 int,@iWeight2 int,@iWeight3 int,@iWeight4 int,@sPlaceOfBirth varchar(15)
									--,@iPregnant_Check_Number int,@bPregnant_Abort_No_Use bit,@sGH_RegionID varchar(15)
									--,@iGH_UserID int,@sDate_Update datetime,@sCreatedDate datetime,@sDeliver varchar(6),@sDate_SLSS datetime,@sResult_SLSS varchar(2),@sDate_SLTS1 datetime,@sResult_SLTS1 varchar(2),@sDate_SLTS2 datetime,@sResult_SLTS2 varchar(2)
									FOR SELECT [Generate_Code],[Gen_Date],[Birth_Number],[Weight1],[Weight2],[Weight3],[Weight4],[PlaceOfBirth]
										,[Pregnant_Check_Number],[Pregnant_Abort_No_Use],[Region_ID]
										,[User_ID],[Date_Update],[CreatedDate],[Deliver],[Date_SLSS],[Result_SLSS],[Date_SLTS1],[Result_SLTS1],[Date_SLTS2],[Result_SLTS2]	
									FROM [Tmp_GenerateHealth] WHERE [Region_ID] = @sCommuneID AND [Personal_ID] = @iPer_ID
								OPEN GH_Cur
								BEGIN
									FETCH NEXT FROM GH_Cur INTO @iGenerate_Code, @sGen_Date, @iBirth_Number, @iWeight1, @iWeight2, @iWeight3, @iWeight4, @sPlaceOfBirth,
										@iPregnant_Check_Number ,@bPregnant_Abort_No_Use, @sGH_RegionID, 
										@iGH_UserID,@sDate_Update, @sCreatedDate,@sDeliver, @sDate_SLSS, @sResult_SLSS, @sDate_SLTS1, @sResult_SLTS1, @sDate_SLTS2,@sResult_SLTS2
									WHILE (@@FETCH_STATUS = 0)
									BEGIN
										--INSERT INTO [GenerateHealth]  ([Personal_ID], [Generate_Code], [Gen_Date], [Export_Status], [Birth_Number], [Weight1], [Weight2], [Weight3], [Weight4], 
										--				[PlaceOfBirth], [Pregnant_Check_Number], [Pregnant_Abort_No_Use], [Region_ID], [Date_Update], [User_ID])
										INSERT INTO [GenerateHealth]([Personal_ID], [Generate_Code],[Gen_Date],[Export_Status],[Birth_Number],[Weight1],[Weight2],[Weight3],[Weight4],[PlaceOfBirth]
										,[Pregnant_Check_Number],[Pregnant_Abort_No_Use],[Region_ID]
										,[User_ID],[Date_Update],[CreatedDate],[Deliver],[Date_SLSS],[Result_SLSS],[Date_SLTS1],[Result_SLTS1],[Date_SLTS2],[Result_SLTS2])
										VALUES (@iPersonalID, @iGenerate_Code, @sGen_Date, 0, @iBirth_Number, @iWeight1, @iWeight2, @iWeight3, @iWeight4, @sPlaceOfBirth,
											@iPregnant_Check_Number ,@bPregnant_Abort_No_Use, @sGH_RegionID, 
											@iGH_UserID, @sDate_Update,@sCreatedDate,@sDeliver, @sDate_SLSS, @sResult_SLSS, @sDate_SLTS1, @sResult_SLTS1, @sDate_SLTS2,@sResult_SLTS2)
										FETCH NEXT FROM GH_Cur INTO @iGenerate_Code, @sGen_Date, @iBirth_Number, @iWeight1, @iWeight2, @iWeight3, @iWeight4, @sPlaceOfBirth,
											@iPregnant_Check_Number ,@bPregnant_Abort_No_Use, @sGH_RegionID, 
											@iGH_UserID,@sDate_Update, @sCreatedDate,@sDeliver, @sDate_SLSS, @sResult_SLSS, @sDate_SLTS1, @sResult_SLTS1, @sDate_SLTS2,@sResult_SLTS2
									END
								END
								DEALLOCATE GH_Cur
								--******************K?t thúc thông tin SKSS
								--******************Khai báo Currsor luu thông tin KHHGÐ
								DECLARE FP_Cur CURSOR
									FOR	SELECT [Contra_Date],[Contraceptive_Code],[Date_Update], [User_ID]
										FROM [Tmp_FamilyPlanning] 
										WHERE [Region_ID] = @sCommuneID AND [Personal_ID] = @iPer_ID
								OPEN FP_Cur
								BEGIN
									FETCH NEXT FROM FP_Cur INTO @sFP_ContraDate, @sFP_ContraCode, @daFP_DateUpdate, @iFP_UserID
									WHILE (@@FETCH_STATUS = 0)	
									BEGIN	--******************Chuy?n thông tin KHHGÐ
										INSERT INTO [FamilyPlanning]([Personal_ID], [Contra_Date],[Contraceptive_Code],[Export_Status], [Region_ID], [Date_Update], [User_ID])
										VALUES(@iPersonalID, @sFP_ContraDate, @sFP_ContraCode, 0, @sCommuneID, @daFP_DateUpdate, @iFP_UserID)
										FETCH NEXT FROM FP_Cur INTO @sFP_ContraDate, @sFP_ContraCode, @daFP_DateUpdate, @iFP_UserID
									END
								END
								DEALLOCATE FP_Cur
								--******************Khai báo Currsor luu thông tin l?ch s? KHHGÐ
								DECLARE FPH_Cur CURSOR
									FOR	SELECT [Contra_Date],[Contraceptive_Code], [Date_Update], [User_ID]
										FROM [Tmp_FamilyPlanningHistory] 
										WHERE [Region_ID] = @sCommuneID AND [Personal_ID] = @iPer_ID
								OPEN FPH_Cur
								BEGIN
									FETCH NEXT FROM FPH_Cur INTO @sFPH_ContraDate, @sFPH_ContraCode, @daFPH_DateUpdate, @iFPH_UserID
									WHILE (@@FETCH_STATUS = 0)	
									BEGIN	--******************Chuy?n thông tin  l?ch s? KHHGÐ
										INSERT INTO [FamilyPlanningHistory]([Personal_ID], [Contra_Date],[Contraceptive_Code],[Export_Status], [Region_ID], [Date_Update], [User_ID])
										VALUES(@iPersonalID, @sFPH_ContraDate, @sFPH_ContraCode, 0, @sCommuneID, @daFPH_DateUpdate, @iFPH_UserID)
										FETCH NEXT FROM FPH_Cur INTO @sFPH_ContraDate, @sFPH_ContraCode, @daFPH_DateUpdate, @iFPH_UserID
									END
								END
								DEALLOCATE FPH_Cur
								--********************Chuy?n b?n ghi cá nhân ti?p theo
								FETCH NEXT FROM Person_Cur INTO @iPer_ID, @sPer_FirstName, @sPer_LastName, @sPer_DOB, 
								@sPer_POB, @sPer_NatCode, @sPer_RelCode, @sPer_EthCode, @sPer_ResCode, @sPer_EduCode, @sPer_TecCode, 
								@sPer_MarCode, @sPer_SexID, @sPer_EduLevel, @sPer_PersonStatus, @sPer_InvCode, @daPer_StartDate, @daPer_ChangeDate,
								@daPer_EndDate, @sPer_Notes, @iPer_UserID
							END
						END
						DEALLOCATE Person_Cur
						--
						FETCH NEXT FROM Ho_Cur INTO @iHH_ID, @sHH_Code, @sHH_Number, @daHH_StartDate, @daHH_EndDate,  @sHH_Status, @iIsBigHH, @sHH_Notes, @iHH_UserID
					END

				END
				DEALLOCATE Ho_Cur
				FETCH NEXT FROM Add_Cur INTO @iAdd_ID, @sAdd_Name, @sAdd_Levels, @sAdd_Parent, @sAdd_Notes, @sAdd_FullAddress, @daAdd_DateUpdate, @iAdd_UserID
			END
		END
		FETCH NEXT FROM FW_Cur INTO @iFW_ID, @sFW_FirstName, @sFW_LastName, @daFW_HireDate,  @sFW_SexID, @bFW_IsCadre, @daFW_DateUpdate, @iFW_UserID
		DEALLOCATE Add_Cur
		--******************K?t thúc d?a ch? chuy?n sang c?ng tác viên khác	
END
	END

DEALLOCATE FW_Cur
IF (@@ERROR > 0)
	ROLLBACK TRAN
ELSE
	COMMIT TRAN