CREATE TABLE [dbo].[PaymentBatchPayableLines_as_EvoOnline_PaymentBatchLine](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[AccountNumberAccountId] [nvarcharlong](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 

	   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;