SQL Query to fetch Related information based on transaction type from TaxTrans in D365FO

//Query to Fetch Related information based on transaction type and tagged Masters (Customer/Vendor) from 

Posted Sales Tax

1. Type (Customer or vendor or ledger)

2. Identification (Customer or vendor account name or Voucher entries text)

//These columns also be converted to Computed column (T-SQL) in view.

Query:

 CAST

                 ((SELECT TOP (1) CAST((CASE WHEN tt.Voucher =

                                   (SELECT TOP (1) ct.Voucher

                                   FROM    CUSTTRANS AS ct

                                   WHERE ct.VOUCHER = tt.VOUCHER AND ct.TRANSDATE = tt.TRANSDATE AND ct.DATAAREAID = tt.DATAAREAID) THEN 'Customer' ELSE CASE WHEN tt.Voucher =

                                   (SELECT TOP (1) vt.Voucher

                                   FROM    VendTrans AS vt

                                   WHERE vt.VOUCHER = tt.VOUCHER AND vt.TRANSDATE = tt.TRANSDATE AND vt.DATAAREAID = tt.DATAAREAID) THEN 'Vendor' ELSE 'Ledger' END END) AS NVARCHAR(10)) AS Expr1

                  FROM    dbo.TAXTRANS AS tt

                  WHERE (RECID = T1.RECID)) AS NVARCHAR(60)) AS TYPE,

 

CAST

                 ((SELECT TOP (1) dpt.NAME

                  FROM    dbo.CUSTTRANS AS ct INNER JOIN

                               dbo.CUSTTABLE AS ctab ON ctab.ACCOUNTNUM = ct.ACCOUNTNUM AND ct.DATAAREAID = ctab.DATAAREAID INNER JOIN

                               dbo.DIRPARTYTABLE AS dpt ON dpt.RECID = ctab.PARTY

                  WHERE (ct.VOUCHER = T1.VOUCHER) AND (ct.TRANSDATE = T1.TRANSDATE) AND (ct.DATAAREAID = T1.DATAAREAID)

                  UNION ALL

                  SELECT TOP (1) dpt.NAME

                  FROM   dbo.VENDTRANS AS vt INNER JOIN

                               dbo.VENDTABLE AS vtab ON vtab.ACCOUNTNUM = vt.ACCOUNTNUM AND vt.DATAAREAID = vtab.DATAAREAID INNER JOIN

                               dbo.DIRPARTYTABLE AS dpt ON dpt.RECID = vtab.PARTY

                  WHERE (vt.VOUCHER = T1.VOUCHER) AND (vt.TRANSDATE = T1.TRANSDATE) AND (vt.DATAAREAID = T1.DATAAREAID)

                  UNION ALL

                  SELECT TOP (1) gjae.TEXT

                  FROM   dbo.GENERALJOURNALENTRY AS gje INNER JOIN

                               dbo.GENERALJOURNALACCOUNTENTRY AS gjae ON gjae.GENERALJOURNALENTRY = gje.RECID

                  WHERE (NOT EXISTS

                                   (SELECT TOP (1) VOUCHER

                                   FROM    dbo.CUSTTRANS AS ct2

                                   WHERE (VOUCHER = T1.VOUCHER) AND (TRANSDATE = T1.TRANSDATE) AND (DATAAREAID = T1.DATAAREAID))) AND (NOT EXISTS

                                   (SELECT TOP (1) VOUCHER

                                   FROM    dbo.VENDTRANS AS vt2

                                   WHERE (VOUCHER = T1.VOUCHER) AND (TRANSDATE = T1.TRANSDATE) AND (DATAAREAID = T1.DATAAREAID))) AND (gje.SUBLEDGERVOUCHER = T1.VOUCHER) AND (gje.ACCOUNTINGDATE = T1.TRANSDATE) AND 

                               (gje.SUBLEDGERVOUCHERDATAAREAID = T1.DATAAREAID)) AS NVARCHAR(100)) AS IDENTIFICATION 

FROM   dbo.TAXTRANS AS T1