ALTER PROCEDURE [dbo].[PRPTFIJURNALRECAP]
(
	@StartDate VARCHAR(20)
	,@EndDate VARCHAR(20)
	,@UserID VARCHAR(150)
	,@DocStatus VARCHAR(20)
	,@CompanyID VARCHAR(20)
	,@BusinessUnit VARCHAR(20)
	,@DocType VARCHAR(20)
	,@CreateBy VARCHAR(20)
	,@VendorID VARCHAR(20)
	,@CustomerID VARCHAR(20)
	,@CostCenter VARCHAR(20)
)
AS

--DECLARE @StartDate VARCHAR(20) = '20240101'
--DECLARE @EndDate VARCHAR(20) = '20240430'
--DECLARE @UserID VARCHAR(150) = ''
--DECLARE @DocStatus VARCHAR(20) = ''
--DECLARE @CompanyID VARCHAR(20) = '1000'
--DECLARE @BusinessUnit VARCHAR(20) = ''
--DECLARE @DocType VARCHAR(20) = ''
--DECLARE @CreateBy VARCHAR (20) = ''
--DECLARE @VendorID VARCHAR (20) = '00000002'
--DECLARE @CustomerID VARCHAR (20) = '' ----------CUS0003
--DECLARE @CostCenter VARCHAR (20) = ''


DECLARE @TblHead TABLE
    (
      [DocNo] [VARCHAR](20) NOT NULL ,
      [DocType] [VARCHAR](20) NULL ,
      DocTypeTo VARCHAR(50) ,
      [DocDate] [VARCHAR](20) NULL ,
      [DocStatus] [VARCHAR](20) NULL ,
      DocDesc [VARCHAR](500) NULL ,
      [FiscalYear] [VARCHAR](20) NULL ,
      [FiscalPeriod] [VARCHAR](20) NULL ,
      [CompanyID] [VARCHAR](20) NULL ,
      [IntDocRef] [VARCHAR](100) NULL ,
      [ExtDocRef] [VARCHAR](50) NULL ,
      [BusinessUnit] [VARCHAR](50) NULL ,
      [PostingDate] [VARCHAR](20) NULL ,
      [Curency] [VARCHAR](20) NULL ,
      [CreateBy] [VARCHAR](50) NULL ,
      [CreateDate] [VARCHAR](14) NULL ,
      [ChangeBy] [VARCHAR](50) NULL ,
      [ChangeDate] [VARCHAR](14) NULL ,
      [CustomerID] [VARCHAR](50) NULL ,
      [VendorID] [VARCHAR](50) NULL ,
      [InvoiceNumber] [VARCHAR](50) NULL ,
      [FakturNumber] [VARCHAR](50) NULL ,
      [VendorInvoiceNumber] [VARCHAR](50) NULL ,
      [IntDocTrType] [VARCHAR](50) NULL ,
      [Regular] [VARCHAR](50) NULL ,
      [CashPlan] [VARCHAR](50) NULL ,
      [StartDate] [VARCHAR](8) NULL ,
      [EndDate] [VARCHAR](8) NULL ,
      [RegularDate] [VARCHAR](10) NULL ,
      [TransTypeRef] [VARCHAR](20) NULL ,
      [DocTypeRef] [VARCHAR](20) NULL ,
      [DocIDRef] [VARCHAR](20) NULL ,
      [DocItemRef] [VARCHAR](20) NULL ,
      [ProcessID] [VARCHAR](20) NULL ,
      [ExternalID] [VARCHAR](20) NULL ,
      [CostCenterz] [VARCHAR](20) NULL ,
      REfType VARCHAR(200) ,
      RefStatus VARCHAR(200) ,
      DocStatusDesc VARCHAR(20) ,
      CompanyDescription VARCHAR(250) ,
      BusUnitDesc VARCHAR(250) ,
      CurrencyDescription VARCHAR(250) ,
      CustomerDesc VARCHAR(250) ,
      VendorDesc VARCHAR(250)
    )

INSERT  INTO @TblHead
        SELECT DISTINCT
                 head.DocNo ,
                head.DocType ,
                head.DocTypeTo ,
                head.DocDate ,
                head.DocStatus ,
                head.Description ,
                head.FiscalYear ,
                head.FiscalPeriod ,
                head.CompanyID ,
                head.IntDocRef ,
                head.ExtDocRef ,
                head.BusinessUnit ,
                head.PostingDate ,
                head.Curency ,
                head.CreateBy ,
                head.CreateDate ,
                head.ChangeBy ,
                head.ChangeDate ,
                head.CustomerID ,
                head.VendorID ,
                head.InvoiceNumber ,
                head.FakturNumber ,
                head.VendorInvoiceNumber ,
                head.IntDocTrType ,
                head.Regular ,
                head.CashPlan ,
                head.StartDate ,
                head.EndDate ,
                head.RegularDate ,
                head.RefType ,
                head.RefStatus ,
                head.TransTypeRef ,
                head.DocTypeRef ,
                head.DocIDRef ,
                head.DocItemRef ,
                head.ProcessID ,
                head.ExternalID ,
                head.CostCenter ,
                doc.Description AS DocStatusDesc ,
                comp.CompanyDescription ,
                bus.Description AS BusUnitDesc ,
                curr.CurrencyDescription ,
                '' AS CustomerDesc, --customer.Description AS CustomerDesc ,
                vendor.Description AS VendorDesc
        FROM    dbo.PTRFINANCEHEAD AS head
                LEFT JOIN dbo.PCMBSDOCSTATUS AS doc ON doc.DocStatus = head.DocStatus
                LEFT JOIN dbo.PCMEPCOMPID AS comp ON head.CompanyID = comp.CompanyID
                LEFT JOIN dbo.PCMFILOBUSUNIT AS bus ON bus.BuCode = head.BusinessUnit
                LEFT JOIN dbo.PHRPYCURR AS curr ON curr.Currency = head.Curency
                LEFT JOIN dbo.PMDCUS0001 AS customer ON customer.CustomerID = head.CustomerID
                LEFT JOIN dbo.PMDVEN0001 AS vendor ON vendor.VendorID = head.VendorID
        WHERE   head.DocDate >= @StartDate
                AND head.DocDate <= @EndDate
                AND ( head.DocStatus = @DocStatus
                      OR @DocStatus = ''
                    )
                AND ( Head.CreateBy = @UserID
                      OR @UserID = ''
                    )
                AND ( head.DocType = @DocType
                      OR @DocType = ''
                    )
                AND ( head.CompanyID = @CompanyID
                      OR @CompanyID = ''
                    )
                AND ( head.BusinessUnit = @BusinessUnit
                      OR @BusinessUnit = ''
                    )
					 AND ( head.CreateBy = @CreateBy
                      OR @CreateBy = ''
                    )
					
					

SELECT DISTINCT
        TblHead.* ,
        FiItem.DKFlag ,
        FiItem.AccountGroup ,
        FiItem.GLAccountID ,
        FiItem.SubLedgerType ,
        --FiItem.SubLedgerID ,
		sldesc.Description as SubLedgerID,
        --FiItem.CostCenter as cc ,
        FiItem.ProductID ,
        FiItem.AmountDebet ,
        FiItem.AmountCredit ,
        FiItem.AmountDebetCompCurr ,
        FiItem.AmountCreditCompCurr ,
        FiItem.CompanyCurrency ,
        FiItem.ItemStatus ,
        FiItem.DueDate ,
        FiItem.RefDocTr ,
        FiItem.RefDocTy ,
        FiItem.RefDocID ,
        FiItem.Description AS FiItemDesc ,
        sl.Description AS SLDesc ,
        product.Description AS ProductDesc ,
        compcurr.CurrencyDescription AS CompCurrDesc ,
        CASE WHEN FiItem.ItemStatus = '0' THEN 'Open'
             WHEN FiItem.ItemStatus = '1' THEN 'Partial'
             WHEN FiItem.ItemStatus = '2' THEN 'Closed'
        END AS ItemStatusDesc  --0=Open||1=Partial||2=Closed
        ,
        cc.ObjectDescription AS CostcenterDesc ,
        gl.Description AS GLAccountDesc ,
        dbo.fn_formatdatetime_indonesia(TblHead.PostingDate, 'dd/mm/yyyy') AS PostingDateDesc ,
        dbo.fn_formatdatetime_indonesia(TblHead.DocDate, 'dd/mm/yyyy') AS DocDateDesc,
		hh.CostCenter as ccc
FROM    @TblHead AS TblHead
        LEFT JOIN dbo.PTRFINANCEITEM AS FiItem ON TblHead.DocNo = FiItem.DocNo
        LEFT JOIN dbo.PCMFILOSLTYPE AS sl ON sl.SubLedgerType = FiItem.SubLedgerType
        LEFT JOIN dbo.PMDPRD0001 AS product ON product.ProductID = FiItem.ProductID
        LEFT JOIN dbo.PHRPYCURR AS compcurr ON compcurr.Currency = FiItem.CompanyCurrency
        LEFT JOIN dbo.PHROM0001 AS cc ON cc.ObjectClass = 'CC'
                                         AND cc.StartDate <= dbo.fn_formatdatetime(GETDATE(),
                                                              'yyyymmdd')
                                         AND cc.EndDate >= dbo.fn_formatdatetime(GETDATE(),
                                                              'yyyymmdd')
                                         AND cc.ObjectID = FiItem.CostCenter
        LEFT JOIN dbo.PCMFILOCOAGL AS gl ON gl.GlAccount = FiItem.GLAccountID
		LEFT JOIN dbo.PTRFINANCEHEAD AS hh on FiItem.docno = hh.docno
		LEFT JOIN PMDSL0001 sldesc on FiItem.SubLedgerID=sldesc.SubLedgerID
		and hh.DocDate >= @StartDate
                AND hh.DocDate <= @EndDate

	 WHERE  ( hh.CostCenter = @CostCenter
                      OR @CostCenter = ''
                    )
					AND
					(FiItem.SubLedgerID=@VendorID or @VendorID='')
