Requirements: 

Phase1:

  1. Loading supplier transactions which are not allocated and not paid yet. 
  2. User can filter the transactions by 
    1. Supplier range 
    2. Due date
    3. on hold suppliers 
    4. Remittance cheque EFT
    5. transaction on hold
    6. Action date
  3. Can see the total 
  4. Can allocate the transactions individually or as a batch 
  5. Can view the supplier information of the transaction 
    1. the user with permission should be able to modify the supplier bank information.
  6. Can view attachments linked to that invoice
  7. Due dates are color-coded (red, yellow, green) to indicate urgency.
  8. Download the report of the transactions 
  9. Can download the bank file. 
  10. Can configure the bank file. (settings)
  11. Every action is permission based
  12. Email remittenace advise to the supplier, how much we are paying which invoice  
  13. Batch should be validated against the bank file structure. 
    1. supplier bank information should be validated.
    2. should be validated if the line has been already paid or not.


Phase2: 

  1. Link the payments to the bank and directly pay them 
  2. Saving the batches as Draft and ability to load them later on
  3. Can create a batch from transactions and send it through approval


Different Flows: 

  1. a batch is created and going though the workflow (similar to the existing one in EPO)
  2. upon receiving an invoice we can select an option and and the payment which is already linked to a bank would be generated (and paid? )
  3. We set a flag on the invoice and the invoices with this flag would be already in a batch generated automatically (with option to add more)
  4. having the supplier lines aggrigated to one or we can pay suppliers line by line 

Restrictions 

  1. We can't add lines that are already in the batch 
  2. Making sure not to overpay a supplier
  3. We can't send a negative value (total)



Design: 

Filter: 

  1. Supplier filter: 
    1. supplier area
    2. supplier class
    3. supplier group 
    4. supplier on hold
    5. supplier range (from -to)
  2. Transaction filter: 
    1. transaction on hold
    2. Due date
    3. action date


Settings:

  1. Bank account 
  2. Payment line aggregation (summing up all the lines of the supplier into one group) 


UI/UX Design 


Data structure 


Tables: 

CREATE TABLE [dbo].[PaymentBatchAccounts](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[AccountLink] [int] NOT NULL,
	[iAllowICPurchases] [bit] NOT NULL,
	[Master_Sub_Account] [nvarchar](max) NULL,
	[Account_Type] [nvarchar](max) NULL,
	[Description] [nvarchar](max) NULL,
	[iAccountType] [int] NOT NULL,
	[AcLookup] [nvarchar](max) NULL,
	[iForeignBankCurrencyID] [int] NOT NULL,
	[CurrencyCode] [nvarchar](max) NULL,
	[CurrencyId] [int] NOT NULL,
	[BranchId] [bigint] NULL,
 CONSTRAINT [PK_PaymentBatchAccounts] PRIMARY KEY CLUSTERED 
CREATE TABLE [dbo].[PaymentBatchPayableLines](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[AccountNumber] [nvarchar](max) NULL,
	[AccountName] [nvarchar](max) NULL,
	[Bic_Swift] [nvarchar](max) NULL,
	[IBAN] [nvarchar](max) NULL,
	[IsBankAccountValid] [bit] NOT NULL,
	[ExtOrderNum] [nvarchar](max) NULL,
	[InvoiceDate] [datetime2](7) NULL,
	[TxDescription] [nvarchar](max) NULL,
	[Reference] [nvarchar](max) NULL,
	[Order_No] [nvarchar](max) NULL,
	[UserName] [nvarchar](max) NULL,
	[cReference2] [nvarchar](max) NULL,
	[cAuditNumber] [nvarchar](max) NULL,
	[TrCodeDesc] [nvarchar](max) NULL,
	[Branch] [nvarchar](max) NULL,
	[Balance] [float] NOT NULL,
	[DocTotal] [float] NOT NULL,
	[UBD_DueDate] [datetime2](7) NULL,
	[UBD_Terms] [datetime2](7) NULL,
	[DCBalance] [float] NOT NULL,
	[fForeignBalance] [float] NOT NULL,
	[fForeignOutstanding] [float] NOT NULL,
	[OutstandingHome] [float] NOT NULL,
	[Module] [nvarchar](max) NULL,
	[Debit] [float] NOT NULL,
	[Credit] [float] NOT NULL,
	[DCLink] [int] NOT NULL,
	[Account] [nvarchar](max) NULL,
	[Name] [nvarchar](max) NULL,
	[Tax_Number] [nvarchar](max) NULL,
	[PurchaseOrderID] [bigint] NULL,
	[VendorID] [int] NOT NULL,
	[CurrencyID] [int] NOT NULL,
	[RemittanceChequeEFTs] [bit] NOT NULL,
	[TxOnHold] [bit] NOT NULL,
	[OnHold] [bit] NOT NULL,
	[BranchId] [bigint] NULL,
 CONSTRAINT [PK_PaymentBatchPayableLines] PRIMARY KEY CLUSTERED


Selection payments query 

	   SELECT
						PostAP.AutoIdx AS id,
						BankAccNum AS AccountNumber,
						   --Master Data

						   Vendor.Account + ' - ' + Vendor.Name AS AccountName
						   ,BranchCode as Bic_Swift, cBankRefNr as IBAN
							,CAST(
								CASE 
								   WHEN (ISNULL(BranchCode, '') = '' OR ISNULL(cBankRefNr, '') = '') THEN 0 
								   ELSE 1 
								END AS bit
							) AS IsBankAccountValid
							, COALESCE(InvNum.ExtOrderNum, '') as ExtOrderNum, InvNum.InvDate as InvoiceDate,
						   --TxData
						   postap.Description as TxDescription, Reference, Order_No,
							  UserName, cReference2, cAuditNumber, TrCodes.Description as TrCodeDesc,
						   --Branching
							 (SELECT cBranchCode
							   FROM _etblBranch
							   WHERE idBranch = COALESCE(InvNum.InvNum_iBranchID, 0)) AS Branch,
						   --Amounts
							--Foreign handling:
							  CASE
							 WHEN COALESCE(Vendor.iCurrencyID,0) = 0 THEN DCBalance
							 WHEN Vendor.iCurrencyID > 0 THEN fForeignBalance
							 ELSE 0 END AS Balance,
							  CASE
							 WHEN COALESCE(Vendor.iCurrencyID,0) = 0 THEN (Credit-Debit)
							 WHEN Vendor.iCurrencyID > 0 THEN (fForeignCredit-fForeignDebit)
							 ELSE 0 END AS DocTotal,
						   --DUE DATE SECTIONS
						   CASE
							   WHEN Outstanding < 0 THEN TxDate
							   WHEN Outstanding > 0
							  AND PostAP.id = 'OGrv' THEN InvNum.DueDate
							   WHEN Outstanding > 0
							  AND PostAP.id <> 'OGrv'
							  AND Vendor.AccountTerms = 0 THEN TxDate + 0
							   WHEN Outstanding > 0
							  AND PostAP.id <> 'OGrv'
							  AND Vendor.AccountTerms = 1 THEN TxDate + COALESCE(iInterval1Days,0)
							   WHEN Outstanding > 0
							  AND PostAP.id <> 'OGrv'
							  AND Vendor.AccountTerms = 2 THEN TxDate + COALESCE(iInterval2Days,0)
							   WHEN Outstanding > 0
							  AND PostAP.id <> 'OGrv'
							  AND Vendor.AccountTerms = 3 THEN TxDate + COALESCE(iInterval3Days,0)
							   WHEN Outstanding > 0
							  AND PostAP.id <> 'OGrv'
							  AND Vendor.AccountTerms = 4 THEN TxDate + COALESCE(iInterval4Days,0)
							   WHEN Outstanding > 0
							  AND PostAP.id <> 'OGrv'
							  AND Vendor.AccountTerms = 5 THEN TxDate + COALESCE(iInterval5Days,0)
							   WHEN Outstanding > 0
							  AND PostAP.id <> 'OGrv'
							  AND Vendor.AccountTerms = 6 THEN TxDate + COALESCE(iInterval6Days,0)
							  END AS UBD_DueDate,
							  CASE
							   WHEN Outstanding < 0 THEN TxDate
							   WHEN Outstanding > 0
							  AND Vendor.AccountTerms = 0 THEN TxDate + 0
							   WHEN Outstanding > 0
							  AND Vendor.AccountTerms = 1 THEN TxDate + COALESCE(iInterval1Days,0)
							   WHEN Outstanding > 0
							  AND Vendor.AccountTerms = 2 THEN TxDate + COALESCE(iInterval2Days,0)
							   WHEN Outstanding > 0
							  AND Vendor.AccountTerms = 3 THEN TxDate + COALESCE(iInterval3Days,0)
							   WHEN Outstanding > 0
							  AND Vendor.AccountTerms = 4 THEN TxDate + COALESCE(iInterval4Days,0)
							   WHEN Outstanding > 0
							  AND Vendor.AccountTerms = 5 THEN TxDate + COALESCE(iInterval5Days,0)
							   WHEN Outstanding > 0
							  AND Vendor.AccountTerms = 6 THEN TxDate + COALESCE(iInterval6Days,0)
							  END AS UBD_Terms,

							  --not default displayed:
							  DCBalance,fForeignBalance, fForeignOutstanding, Outstanding as OutstandingHome, PostAp.ID AS Module
, Debit, Credit, DCLink, Account, Name, Tax_Number, InvNum.AutoIndex as PurchaseOrderID
, Vendor.DCLink as VendorID
, Vendor.iCurrencyID as CurrencyID
, vendor.bRemittanceChequeEFTS as RemittanceChequeEFTs
, postap.bTxOnHold as TxOnHold
, vendor.on_hold as OnHold
		, CAST(COALESCE(PostAP_iBranchID, 0) AS BIGINT) BranchId

						FROM PostAP
					/*payment batch lines exclusion which are in process of being paid:*/

					LEFT JOIN Vendor ON PostAp.Accountlink = Vendor.DCLink
					LEFT JOIN Currency C ON Vendor.iCurrencyID = C.CurrencyLink
					LEFT JOIN InvNum ON PostAp.InvNumKey = InvNum.AutoIndex
					LEFT JOIN TrCodes ON TrCodeID = idTrCodes
					LEFT JOIN _etblTerms ON _etblTerms.iModule = 1 AND _etblTerms.iTermID = Vendor.iAgeingTermID
					LEFT JOIN VenClass ON Vendor.iClassID = idVenClass
					WHERE round(CASE
						   WHEN COALESCE(Vendor.iCurrencyID,0) = 0 THEN Outstanding
						   WHEN Vendor.iCurrencyID > 0 THEN fForeignOutstanding
						   ELSE 0
						  END,2) <> 0;