DECLARE @SPPDNumber VARCHAR(500) ='DHC.03/SPH-0322/VI-24'
DECLARE @CostCenter VARCHAR(8)='00000089'
DECLARE @NumberReport VARCHAR(500) = '758403'
DECLARE @ReportDate VARCHAR(8) = '20240302'
DECLARE @paymentDate VARCHAR(8) = '20240302'
DECLARE @City VARCHAR(500) = 'Tangerang'
DECLARE @TravelStatus VARCHAR(8) = '02'
DECLARE @now VARCHAR(8) 
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

DECLARE @Header TABLE
(	
	 EmployeeID VARCHAR(8)
	 ,StartDate VARCHAR(8)
	 ,EndDate VARCHAR(8)
	,SPPDNumber VARCHAR(60)
	,TravelReason VARCHAR(500)
	,TravelStatus VARCHAR(4)
	,Departure VARCHAR(50)
	,Destination VARCHAR(50)
	,Notes VARCHAR(500)
	,RegID VARCHAR(8)
	,HeaderSequence int
)
INSERT INTO @Header 
SELECT 
	pa62.EmployeeID
	,pa62.StartDate
	,pa62.EndDate
	,pa62.SPPDNumber
	,pa62.TravelReason
	,pa62.TravelStatus
	,pa62.Departure
	,pa62.Destination
	,pa62.Notes
	,pa62.RegID
	,pa62.HeaderSequence
FROM PHRPA0062 pa62
WHERE pa62.SPPDNumber = @SPPDNumber and pa62.TravelStatus = @TravelStatus

DECLARE @RouteTrans TABLE
(	
	 EmployeeID VARCHAR(8)
	,TransportType VARCHAR(8)
	,RegID VARCHAR(8)
	,HeaderSequence int
)
INSERT INTO @RouteTrans 
SELECT TOP 1
	pa63.EmployeeID
	,pa63.TransportType
	,pa63.RegID
	,pa63.HeaderSequence
FROM phrpa0063 pa63
	LEFT JOIN @Header as header ON pa63.EmployeeID = header.EmployeeID
			AND pa63.HeaderSequence = header.HeaderSequence


DECLARE @CostSumAmount TABLE
(	
	 EmployeeID VARCHAR(8)
	,AmountPlan decimal(18,2)
	,AmountApproval decimal(18,2)
	,CostCenter VARCHAR(8)
	,HeaderSequence int
)
INSERT INTO @CostSumAmount
SELECT
	header.EmployeeID
	,SUM(AmountPlan) AS AmountPlan
	,SUM(AmountApproval) AS AmountApprov
	,pa65.CostCenter
	,pa65.HeaderSequence
FROM phrpa0065 pa65
 INNER JOIN @Header as header ON pa65.EmployeeID = header.EmployeeID
			AND pa65.HeaderSequence = header.HeaderSequence
WHERE pa65.CostCenter = @CostCenter
GROUP BY header.EmployeeID
		,pa65.CostCenter
		,pa65.HeaderSequence

DECLARE @CostSumAmountFIX TABLE
(	
	
	AmountPlanFIX decimal(18,2)
	,AmountApprovalFIX decimal(18,2)
	,CostCenter VARCHAR(8)
)
INSERT INTO @CostSumAmountFIX
SELECT
	SUM(AmountPlan) AS AmountPlan
	,SUM(AmountApproval) AS AmountApprov
	,CostCenter
FROM @CostSumAmount 
Group by CostCenter

SELECT 
 header.*
 , Rou.TransportType
 , tptyp.TransportTypeDescription
 , costSum.AmountPlan
 , costSum.AmountApproval
 , amountFix.AmountPlanFIX 
 , amountFix.AmountApprovalFIX
 ,@NumberReport as NumberReport
  ,dbo.fn_formatdatetime_indonesia(@ReportDate, 'DD MMMM YYYY') as ReportDate
  ,@City as City
 ,dbo.fn_formatdatetime_indonesia(@paymentDate, 'DD MMM YYYY') as payDate
 ,pa01.FullName
 --,header.StartDate
 --,header.EndDate
 ,DATEDIFF(DAY, header.StartDate, header.EndDate) + 1  as countDay
 ,chom1_K.NomorAkun as acountK
 ,chom1_K.DeskripsiAkun as descK
 ,Case when header.TravelStatus = '01' then chom1_D.NomorAkun
	when header.TravelStatus= '02' then chom1_Dtyp2.NomorAkun end as Account
 ,Case when header.TravelStatus = '01' then chom1_D.DeskripsiAkun
	when header.TravelStatus= '02' then chom1_Dtyp2.DeskripsiAkun end as DescAccount
,CASE WHEN header.TravelStatus = '01' then amountFix.AmountPlanFIX 
	when header.TravelStatus= '02' then amountFix.AmountApprovalFIX end as Amount
,pa3.BankAccount
	,pa3.BankAccountName
	,amountFix.CostCenter
	,dtlSppd.TempatDikeluarkan
	, dbo.fn_formatdatetime_indonesia(dtlSppd.TanggalSPPD, 'DD MMMM YYYY') as TanggalSPPD
	, Case when header.TravelStatus = '01' then costSum.AmountPlan
	when header.TravelStatus= '02' then costSum.AmountApproval end as AmountKreditDetail
 	 ,dbo.fn_formatdatetime_indonesia(header.StartDate, 'DD/MM/YYYY') AS StartDateFor
	,dbo.fn_formatdatetime_indonesia(header.EndDate, 'DD/MM/YYYY') AS EndDateFor
FROM @Header header
LEFT JOIN @RouteTrans Rou ON header.EmployeeID = Rou.EmployeeID
				AND header.HeaderSequence = Rou.HeaderSequence
LEFT JOIN @CostSumAmount costSum ON header.EmployeeID = costSum.EmployeeID
LEFT JOIN @CostSumAmountFIX amountFix on costSum.CostCenter = amountFix.CostCenter
LEFT JOIN PHRPA0001 pa01 ON header.EmployeeID = pa01.EmployeeID
		AND pa01.EndDate >= @now
LEFT JOIN PHRPA0002 pa02 ON header.EmployeeID = pa02.EmployeeID
		AND pa02.EndDate >= @now
LEFT JOIN CHROM0001 chom1_D ON amountFix.CostCenter = chom1_D.ObjectID
		AND chom1_D.DebitKredit = 'D'
		AND chom1_D.ModuleType = '1'
		AND chom1_D.Rencana = '1'
LEFT JOIN CHROM0001 chom1_Dtyp2 ON amountFix.CostCenter = chom1_Dtyp2.ObjectID
		AND chom1_Dtyp2.DebitKredit = 'D'
		AND chom1_Dtyp2.ModuleType = '1'
		AND chom1_Dtyp2.Rencana = '2'
LEFT JOIN CHROM0001 chom1_K ON amountFix.CostCenter = chom1_K.ObjectID
		AND chom1_K.DebitKredit = 'K'
		AND chom1_K.ModuleType = '1'
LEFT JOIN PHRPA0003 pa3 ON header.EmployeeID = pa3.EmployeeID
			AND pa3.StartDate <= @now
			AND pa3.EndDate >= @now
LEFT JOIN PHRTVTPTYP tptyp ON Rou.TransportType = tptyp.TransportType
LEFT JOIN CTRSPPDNUMBER dtlSppd ON header.SPPDNumber = dtlSppd.SPPDNumber
where costSum.CostCenter = @CostCenter