Hello Everyone,
I have a SQL View where I join some tables in each block and then, using UNION ALL, I combine all blocks and return a list. In Acumatica by using PXProjection, I should be able to replicate the result of each block as a flat DAC but I am not sure how then I can aggregate these Projections to get a unionized result of all DACs. As a very simple example, how I can replicate the below SQL example in Acumatica?
CREATE VIEW [MyUnionView] AS
SELECT 'AP' AS [DocType], [RefNbr] AS [RefNbr] FROM [APInvoice]
UNION ALL
SELECT 'PO' AS [DocType], [OrderNbr] AS [RefNbr] FROM [POOrder]
UNION ALL
SELECT 'PM' AS [DocType], [ContractCD] AS [RefNbr] FROM [Contract]
GO
SELECT [DocType], [RefNbr] FROM [MyUnionView]
GO