CREATE FUNCTION [dbo].[GETFILODOCREF] ()
RETURNS TABLE
AS
RETURN
(
 
 SELECT DISTINCT * FROM (SELECT  ref.TransType ,ref.DocType , ref.TransTypeRef ,ref.DocTypeRef ,ref.DocStatusRef ,docdata.DocNo ,docdata.DocDate , docdata.CompanyID, docdata.BusinessUnit, docdata.Description, docdata.DocItemID, docdata.ExternalID, docdata.DocStatus, docdata.CustomerID, docdata.VendorID  FROM PCMBSDOCREF ref 
 
 INNER JOIN ( SELECT 'FIN' AS TransType ,DocType ,finhead.DocNo ,DocDate ,finhead.CompanyID, finhead.BusinessUnit, '' AS Description , '' AS DocItemID, ExternalID, finhead.DocStatus, finhead.CustomerID, finhead.VendorID FROM PTRFINANCEHEAD finhead 
 
 INNER JOIN dbo.PTRFINANCEITEM finitem 
 
 ON finitem.DocNo = finhead.DocNo  WHERE finhead.DocNo IN (SELECT DISTINCT DocNo FROM PTRFINANCEITEM 
 
 WHERE (ItemStatus = '0' OR ItemStatus = '1'))  UNION ALL  SELECT 'SLS' AS TransType , DocType , slsHead.DocNo , DocDate ,CompanyID, BusinessUnit, '' AS Description , '' AS DocItemID, '' AS ExternalID, slsHead.DocStatus, slsHead.CustomerID, '' AS VendorID FROM PTRSALESHEADER slsHead 
 
 UNION ALL  SELECT 'PUR' AS TransType , DocType , purHead.DocNo , DocDate ,CompanyID, BusinessUnit, '' AS Description , '' AS DocItemID, '' AS ExternalID, purHead.DocStatus, '' AS CustomerID, purHead.VendorID AS VendorID FROM PTRPURCHHEAD purHead 
 
 UNION ALL  SELECT 'PRD' AS TransType ,DocType ,DocNo ,DocDate ,CompanyID, '0' AS BusinessUnit, '' AS Description , '' AS DocItemID, '' AS ExternalID, DocStatus, '' AS CustomerID, '' AS VendorID FROM PTRPRODUCTIONHEAD 
 
 UNION ALL  SELECT 'DLVR' AS TransType ,DocType ,DocNo ,DocDate ,'0' AS CompanyID , '0' AS BusinessUnit, '' AS Description , '' AS DocItemID, '' AS ExternalID, DocStatus, '' AS CustomerID, '' AS VendorID FROM PTRDELIVERYHEAD 
 
 UNION ALL  SELECT DISTINCT 'MTR' AS TransType ,DocType ,mtrHead.DocNo ,DocDate ,ReceiverID AS CompanyID , '0' AS BusinessUnit, '' AS Description , '' AS DocItemID, '' AS ExternalID, mtrHead.DocStatus, '' AS CustomerID, '' AS VendorID FROM PTRMATERIALHEAD mtrHead 
 
 INNER JOIN dbo.PTRMATERIALITEM mtrItem 
 
 ON mtrHead.DocNo = mtrItem.DocNo ) AS docdata ON ref.TransTypeRef = docdata.TransType AND ref.DocTypeRef = docdata.DocType AND docdata.DocStatus = ref.DocStatusRef AND docdata.DocStatus <> '' AND ref.DocStatusRef <> '' ) AS tbl 
 )