MINOVA DEMO

??CREATE FUNCTION [dbo].[GETFILODOCREFPROD] () ------//------ tanpa validasi docstatus

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 --WHERE DocStatus IN ('2','4')

 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 ) AS tbl 

 )