USE [MinovaES_Pertalife_Prod]
GO
/****** Object:  StoredProcedure [dbo].[PDSDOCREF]    Script Date: 23/05/2023 10.21.00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PDSDOCREF]
(
	@DocType VARCHAR(50)
)
AS

--DECLARE @DocType VARCHAR(50) = ',DocType[=]PO'
SET @DocType = REPLACE(REPLACE(@DocType, 'DocType[=]', ''),',','')

SELECT 
  * 
FROM 
  (
    SELECT 
      ref.TransType, 
      ref.DocType, 
      ref.TransTypeRef, 
      ref.DocTypeRef, 
      docdata.DocNo, 
      docdata.DocDate, 
      docdata.CompanyID, 
      docdata.BusinessUnit, 
      docdata.Description, 
      docdata.DocItemID, 
      docdata.ExternalID 
    FROM 
      PCMBSDOCREF ref WITH (Nolock) 
      INNER JOIN (
        SELECT 
          'FIN' AS TransType, 
          DocType, 
          DocNo, 
          DocDate, 
          CompanyID, 
          BusinessUnit, 
          '' AS Description, 
          '' AS DocItemID, 
          ExternalID 
        FROM 
          PTRFINANCEHEAD WITH (Nolock) 
        WHERE 
          DocNo IN (
            SELECT 
              DISTINCT DocNo 
            FROM 
              PTRFINANCEITEM WITH (Nolock) 
            WHERE 
              (
                ItemStatus = '0' 
                OR ItemStatus = '1'
              )
          ) 
        UNION ALL 
        SELECT 
          'SLS' AS TransType, 
          DocType, 
          slsHead.DocNo, 
          DocDate, 
          CompanyID, 
          BusinessUnit, 
          slsPay.Description AS Description, 
          slsPay.DocItemID AS DocItemID, 
          ExternalID 
        FROM 
          PTRSALESHEADER slsHead WITH (Nolock) 
          INNER JOIN dbo.PTRSALESPAYTERM slsPay WITH (Nolock) ON slsHead.DocNo = slsPay.DocNo 
          AND slsPay.Status = '0' 
        UNION ALL 
        SELECT 
          'PUR' AS TransType, 
          DocType, 
          purHead.DocNo, 
          DocDate, 
          CompanyID, 
          BusinessUnit, 
          '' AS Description, --purPay.Description AS Description, 
          '' AS DocItemID, --purPay.DocItemID AS DocItemID, 
          ExternalID 
        FROM 
          PTRPURCHHEAD purHead WITH (Nolock) 
          --INNER JOIN dbo.PTRSALESPAYTERMPURCH purPay WITH (Nolock) ON purHead.DocNo = purPay.DocNo 
          --AND purPay.Status = '0' 
        UNION ALL 
        SELECT 
          'PRD' AS TransType, 
          DocType, 
          DocNo, 
          DocDate, 
          CompanyID, 
          '0' AS BusinessUnit, 
          '' AS Description, 
          '' AS DocItemID, 
          ExternalID 
        FROM 
          PTRPRODUCTIONHEAD WITH (Nolock) 
        UNION ALL 
        SELECT 
          'DLVR' AS TransType, 
          DocType, 
          DocNo, 
          DocDate, 
          '0' AS CompanyID, 
          '0' AS BusinessUnit, 
          '' AS Description, 
          '' AS DocItemID, 
          ExternalID 
        FROM 
          PTRDELIVERYHEAD WITH (Nolock) 
        UNION ALL 
        SELECT 
          DISTINCT 'MTR' AS TransType, 
          DocType, 
          mtrHead.DocNo, 
          DocDate, 
          ReceiverID AS CompanyID, 
          '0' AS BusinessUnit, 
          '' AS Description, 
          '' AS DocItemID, 
          ExternalID 
        FROM 
          PTRMATERIALHEAD mtrHead WITH (Nolock) 
          INNER JOIN dbo.PTRMATERIALITEM mtrItem WITH (Nolock) ON mtrHead.DocNo = mtrItem.DocNo 
        WHERE 
          mtrHead.DocStatus = '3'
		UNION ALL 
        SELECT 
          DISTINCT 'ASTM' AS TransType, 
          DocType, 
          astmhead.DocNo, 
          DocDate, 
          astmhead.CompanyID AS CompanyID, 
          astmhead.BusinessUnit AS BusinessUnit, 
          astmhead.Description AS Description, 
          astmItem.DocItemID AS DocItemID, 
          '' AS ExternalID 
        FROM 
          PTRASTMAINHEAD astmhead WITH (Nolock) 
          INNER JOIN PTRASTMAINITEM astmItem WITH (Nolock) ON astmhead.DocNo = astmItem.DocNo 
		UNION ALL 
        SELECT 
          DISTINCT 'ASTU' AS TransType, 
          DocType, 
          astuhead.DocNo, 
          DocDate, 
          astuhead.CompanyID AS CompanyID, 
          astuhead.BusinessUnit AS BusinessUnit, 
          astuhead.Description AS Description, 
          astuItem.DocItemID AS DocItemID, 
          '' AS ExternalID 
        FROM 
          PTRASTUTILHEAD astuhead WITH (Nolock) 
          INNER JOIN PTRASTUTILITEM astuItem WITH (Nolock) ON astuhead.DocNo = astuItem.DocNo 
      ) AS docdata ON ref.TransTypeRef = docdata.TransType 
      AND ref.DocTypeRef = docdata.DocType
  ) AS tbl
where DocType = @DocType