SELECT
DISTINCT NTLL.foreignamount as lineinvoicepaidamount,
NT.foreignpaymentamountunused as amountunapplied,
NT.ID AS upstreamid,
NT.TranDate,
NT.Type,
NT.otherrefnum AS checkNumber,
transaction.id AS lineTransactionId,
transaction.tranid AS invoicenumber,
NT.tranid AS paymentReferenceNumber,
BUILTIN.DF(NT.paymentmethod.name) AS paymentMethod,
‘dd/MM/yyyy’ AS dateFormat,
customer.entitytitle AS businessPartnerId,
customer.companyname AS companyId,
BUILTIN.DF(NT.currency) AS currency,
CASE WHEN (NT.Type = ‘CustPymt’) THEN (NT.foreigntotal) ELSE ABS(NT.foreigntotal) END AS receiptamount,
NT.exchangerate,
NT.trandate AS transactiondate,
BUILTIN.DF(NT.status) AS paymentstatus,
transaction.id AS upstreamTxnDomainId,
transaction.foreignamountpaid AS totalinvoicepaidamount
FROM
NextTransactionLineLink AS NTLL
INNER JOIN TRANSACTION AS NT ON (NT.ID = NTLL.NextDoc)
JOIN TRANSACTION TRANSACTION ON transaction.id = NTLL.PreviousDoc
JOIN customer ON customer.id = NT.entity
WHERE
(NT.Type = ‘CustCred’)
and customer.entitytitle = ’46 Ram Mehra’
and NTLL.foreignamount IS NOT NULL