USE [MinovaES_Malaka_Dev]
GO
/****** Object:  StoredProcedure [dbo].[PRPTFIPO_LAMP]    Script Date: 10/14/2021 2:40:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PRPTFIPO_LAMP]
    (
      @CompanyID VARCHAR(50) ,
      @DocNo VARCHAR(50)
    )
AS


 --DECLARE @CompanyID VARCHAR(50) = '1000'
 --DECLARE @DocNo VARCHAR(50)= '00000021'

 SELECT DISTINCT
        h.ExternalID AS lampiran ,
        i.Material ,
        i.ItemType ,
		ds.Description as descitem,
        CASE WHEN i.ItemType = '1' THEN mat1.Description
             WHEN i.ItemType = '2' THEN srv.Description
        END AS Uraian,
		i.MaterialUnit,
		um.Description AS Satuan,
		i.MaterialQuantity AS Volume,
		i.PriceAmount AS HargaSatuan,
		i.MaterialQuantity * i.PriceAmount AS Jumlah,
		i.NetAmount AS AfterDisc,
		i.tax AS ppn,
		i.NetAmount + i.tax AS Grand_Total
 FROM   PTRPURCHHEAD h
        LEFT JOIN PTRPURCHITEM i ON h.DocNo = i.DocNo
		LEFT JOIN PCMFILOMATTYPE ds ON i.ItemType = ds.MaterialType
        LEFT JOIN dbo.PMDMAT0001 mat1 ON i.Material = mat1.MaterialID
        LEFT JOIN dbo.PMDSRV0001 srv ON i.Material = srv.ServiceID
		LEFT JOIN PCMFILOUNITMEASURE um ON i.MaterialUnit = um.UnitMeasurement
 WHERE  ( h.CompanyID = @CompanyID
          OR @CompanyID = ''
        )
        AND ( h.DocNo = @DocNo
              OR @DocNo = ''
            )