     ) dervx on dervx.AccountId = mainUpdate.AccountId and dervx.PeriodEnd = mainUpdate.PeriodEnd and dervx.Ukprn = mainUpdate.Ukprn
 GO
 
+CREATE PROCEDURE #createPaymentsForMonth
+(    	
+    @accountId BIGINT,
+    @accountName NVARCHAR(100),
+	@createDate DATETIME,
+	@totalPaymentAmount DECIMAL(18,5),
+	@numberOfPayments INT
+)  
+AS  
+BEGIN  
 BEGIN TRANSACTION
+
+--todo: use numberOfPayments
+
+    DECLARE @periodEndDate DATETIME = DATEADD(month, -1, @createDate)
+
+	--SELECT dbo.CalendarPeriodYear(@periodEndDate)
+	--SELECT dbo.CalendarPeriodMonth(@periodEndDate)
+	--SELECT dbo.CollectionPeriodYear(@periodEndDate)
+	--SELECT dbo.CollectionPeriodMonth(@periodEndDate)
+	--SELECT dbo.PeriodEndYear(@periodEndDate)
+	--SELECT dbo.PeriodEndMonth(@periodEndDate)
+
+	DECLARE @periodEndId VARCHAR(8) = dbo.PeriodEnd(@periodEndDate)
+	--SELECT @periodEndId
+
+	-- Add period end if its not already there
+	IF NOT EXISTS
+	(
+		select 1 FROM [employer_financial].[periodend] 
+		WHERE periodendid = @periodEndId
+	)
+	BEGIN        
+		insert into employer_financial.periodend (periodendid, calendarperiodmonth, calendarperiodyear, accountdatavalidat, commitmentdatavalidat, completiondatetime, paymentsforperiod)
+		values
+		(@periodEndId,dbo.CalendarPeriodMonth(@periodEndDate),dbo.CalendarPeriodYear(@periodEndDate),'2018-05-04 00:00:00.000','2018-05-04 09:07:34.457','2018-05-04 10:50:27.760','https://pp-payments.apprenticeships.sfa.bis.gov.uk/api/payments?periodId=1617-R10')
+	END
+
+    EXEC #createAccountPayments @accountId, @accountName, 'CHESTERFIELD COLLEGE', 10001378, 'Accounting', @periodEndDate, @totalPaymentAmount	
+        
+    exec #ProcessPaymentDataTransactionsGenerateDataEdition @accountId, @createDate
+
+COMMIT TRANSACTION
+END
+GO
 
 	-- ________                                                                         ____                                                                                  ___    __                    ___               
 	-- `MMMMMMMb.                                                                      6MMMMb/                                                   68b                          `MM    d'  @                   MM              

	--                    (8),P                                                                                                                                                                                              
	--                     YMM                                                                                                                                                                                               

    DECLARE @accountId BIGINT                 = 0
    DECLARE @accountName NVARCHAR(100)        = 'XXX'	
    DECLARE @payeScheme NVARCHAR(16)          = 'XXX'
    
    DECLARE @createDate DATETIME              = GETDATE()
    DECLARE @periodEndDate DATETIME           = DATEADD(month, -1, @createDate)
    DECLARE @totalPaymentAmount DECIMAL(18,5) = 10000
    
	--  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____  _____ 
	-- [_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____][_____]

	--SELECT dbo.CalendarPeriodYear(@periodEndDate)
	--SELECT dbo.CalendarPeriodMonth(@periodEndDate)
	--SELECT dbo.CollectionPeriodYear(@periodEndDate)
	--SELECT dbo.CollectionPeriodMonth(@periodEndDate)
	--SELECT dbo.PeriodEndYear(@periodEndDate)
	--SELECT dbo.PeriodEndMonth(@periodEndDate)

	DECLARE @periodEndId VARCHAR(8) = dbo.PeriodEnd(@periodEndDate)
	--SELECT @periodEndId

	-- Add period end if its not already there
	IF NOT EXISTS
	(
		select 1 FROM [employer_financial].[periodend] 
		WHERE periodendid = @periodEndId
	)
	BEGIN        
		insert into employer_financial.periodend (periodendid, calendarperiodmonth, calendarperiodyear, accountdatavalidat, commitmentdatavalidat, completiondatetime, paymentsforperiod)
		values
		(@periodEndId,dbo.CalendarPeriodMonth(@periodEndDate),dbo.CalendarPeriodYear(@periodEndDate),'2018-05-04 00:00:00.000','2018-05-04 09:07:34.457','2018-05-04 10:50:27.760','https://pp-payments.apprenticeships.sfa.bis.gov.uk/api/payments?periodId=1617-R10')
	END

    EXEC #createAccountPayments @accountId, @accountName, 'CHESTERFIELD COLLEGE', 10001378, 'Accounting', @periodEndDate, @totalPaymentAmount	
        
    exec #ProcessPaymentDataTransactionsGenerateDataEdition @accountId, @createDate

COMMIT TRANSACTION
GO

drop function CalendarPeriodYear
go

IF OBJECT_ID('tempdb..#ProcessPaymentDataTransactionsGenerateDataEdition') IS NOT NULL
BEGIN
    DROP PROC #ProcessPaymentDataTransactionsGenerateDataEdition
END
GO

IF OBJECT_ID('tempdb..#createPaymentsForMonth') IS NOT NULL
BEGIN
    DROP PROC #createPaymentsForMonth
END
GO

