CLIENT SUPPORT » BANK KALTENG Bugs

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