Kertas Nusantara

ALTER PROCEDURE PRPTTVJURNALTRAVEL

(

	@CompanyID VARCHAR(4)

	,@CostCenter VARCHAR(8)

	,@EmployeeID VARCHAR(8) 

	,@EmployeeArea VARCHAR(8)

	,@RunPeriod VARCHAR(6)

)

AS

--DECLARE @CompanyID VARCHAR(4) = '1000'

--DECLARE @CostCenter VARCHAR(8) = ''

--DECLARE @EmployeeID VARCHAR(8) = '10000150'

--DECLARE @EmployeeArea VARCHAR(8) = ''

--DECLARE @RunPeriod VARCHAR(6) = '202601'

DECLARE @StartDate VARCHAR(8) = @RunPeriod + '01'

DECLARE @EndDateDt DATETIME = DATEADD(dd, -1,DATEADD(mm, 1,CONVERT(DATETIME, @StartDate)))

DECLARE @EndDate VARCHAR(8) = @RunPeriod + CONVERT(VARCHAR(2), DATEPART(dd,@EndDateDt))

DECLARE @TblEmp TABLE

(

	EmployeeID VARCHAR(20)

	,HeaderSequence DECIMAL(22,0)

	,CostType VARCHAR(20)

	,CostDesc VARCHAR(250)

	,Rate  VARCHAR(250)

	,AmountPlan DECIMAL(22,2)

	,AmountSettlement DECIMAL(22,2)

	,AmountApproval DECIMAL(22,2)

	,RateConversion DECIMAL(22,2)

	,AmountApprovalConversion DECIMAL(22,2)

	,Currency VARCHAR(20)

	,CostCenter VARCHAR(20)

	,CCDesc VARCHAR(250)

	,CompID VARCHAR(20)

	,CompDesc VARCHAR(250)

)

INSERT INTO @TblEmp

SELECT DISTINCT pa65.EmployeeID

				,pa65.HeaderSequence

				,pa65.CostType

				,pa65.CostDescription

				,pa65.Rate

				,pa65.AmountPlan

				,pa65.AmountSettlement

				,pa65.AmountApproval

				,pa65.RateConversion

				,pa65.AmountApprovalConversion

				,pa65.Currency

				,pa65.CostCenter

				,cc.ObjectDescription AS CCDesc

				,pa2.CompanyID

				,comp.CompanyDescription

FROM PHRPA0062 AS pa62

LEFT JOIN PHRPA0065 AS pa65

	ON pa65.EmployeeID = pa62.EmployeeID

LEFT JOIN PHRPA0002 AS pa2

	ON pa62.EmployeeID = pa2.EmployeeID

	AND pa2.StartDate <= @EndDate AND pa2.EndDate >= @EndDate

LEFT JOIN dbo.PCMEPCOMPID AS comp

	ON pa2.CompanyID = comp.CompanyID

LEFT JOIN dbo.PHROM0001 AS cc

	ON cc.ObjectID = pa2.CostCenter

	AND cc.ObjectClass = 'CC'

	AND cc.StartDate <= @EndDate AND cc.EndDate >= @EndDate

WHERE (pa62.EmployeeID = @EmployeeID OR @EmployeeID = '')

	AND (pa62.TravDocDate >= @StartDate AND pa62.TravDocDate <= @EndDate)

	AND (pa2.CompanyID = @CompanyID OR @CompanyID = '')

	AND (pa2.CostCenter = @CostCenter OR @CostCenter = '')

	AND (pa2.EmployeeArea = @EmployeeArea OR @EmployeeArea = '')

	AND (pa62.HeaderSequence = pa65.HeaderSequence)

DECLARE @TblGLAcc TABLE

(

	CostType VARCHAR(20)

	,CostTypeDesc VARCHAR(250)

	,DebitAcc VARCHAR(20)

	,CreditAcc VARCHAR(20)

	,CE VARCHAR(20)

	,GLAccount VARCHAR(20)

	,CEDesc VARCHAR(250)

)

INSERT INTO @TblGLAcc

SELECT DISTINCT costtype.CostType

				,costtype.CostTypeDesc

				,costtype.DebitAcc

				,'' AS CreditAcc

				,CE.ObjectID AS CE

				,CE.GLAccount

				,CE_Desc.ObjectDescription AS CEDesc

FROM PHRTVCOSTYP AS costtype

LEFT JOIN dbo.PHROM0017 AS CE 

	ON CE.StartDate <= @EndDate

    AND CE.EndDate >= @EndDate

    AND CE.ObjectID = costtype.DebitAcc

LEFT JOIN dbo.PHROM0001 AS CE_Desc 

	ON CE_Desc.ObjectDescription = CE.ObjectID

	AND CE_Desc.ObjectClass = 'CE'

	AND CE_Desc.StartDate <= @EndDate

    AND CE_Desc.EndDate >= @EndDate

WHERE costtype.DebitAcc <> ''

INSERT INTO @TblGLAcc

SELECT DISTINCT costtype.CostType

				,costtype.CostTypeDesc

				,'' AS DebitAcc

				,costtype.CreditAcc AS CreditAcc

				,CE.ObjectID AS CE

				,CE.GLAccount

				,CE_Desc.ObjectDescription AS CEDesc

FROM PHRTVCOSTYP AS costtype

LEFT JOIN dbo.PHROM0017 AS CE 

	ON CE.StartDate <= @EndDate

    AND CE.EndDate >= @EndDate

    AND CE.ObjectID = costtype.DebitAcc

LEFT JOIN dbo.PHROM0001 AS CE_Desc 

	ON CE_Desc.ObjectDescription = CE.ObjectID

	AND CE_Desc.ObjectClass = 'CE'

	AND CE_Desc.StartDate <= @EndDate

    AND CE_Desc.EndDate >= @EndDate

WHERE costtype.CreditAcc <> ''

DECLARE @TblAmount TABLE

(

	CostType VARCHAR(20)

	,CostTypeDesc VARCHAR(250)

	,GLAccount VARCHAR(20)

	,DebitAmount DECIMAL(22,2)

	,CreditAmount DECIMAL(22,2)

	,CostCenter VARCHAR(20)

	,CCDesc VARCHAR(250)

	,CompID VARCHAR(20)

	,CompDesc VARCHAR(250)

)

INSERT INTO @TblAmount

SELECT DISTINCT tblgl.CostType

				,tblgl.CostTypeDesc

				,tblgl.GLAccount

				,tblemp.AmountApproval AS DebitAmount

				,0 AS CreditAmount

				,tblemp.CostCenter

				,tblemp.CCDesc

				,tblemp.CompID

				,tblemp.CompDesc

FROM @TblGLAcc AS tblgl

LEFT JOIN @TblEmp AS tblemp

	ON tblemp.CostType = tblgl.CostType

WHERE tblgl.DebitAcc <> '' AND tblemp.AmountApproval > 0

INSERT INTO @TblAmount

SELECT DISTINCT tblgl.CostType

				,tblgl.CostTypeDesc

				,tblgl.GLAccount

				,0 AS DebitAmount

				,tblemp.AmountApproval AS CreditAmount

				,tblemp.CostCenter

				,tblemp.CCDesc

				,tblemp.CompID

				,tblemp.CompDesc

FROM @TblGLAcc AS tblgl

LEFT JOIN @TblEmp AS tblemp

	ON tblemp.CostType = tblgl.CostType

WHERE tblgl.CreditAcc <> '' AND tblemp.AmountApproval > 0

SELECT DISTINCT * 

				,dbo.fn_formatdatetime(@StartDate,'mmmm yyyy') AS PeriodDesc

				,dbo.fn_formatdatetime(@StartDate,'dd/mmmm/yyyy') AS StartDateDesc

				,dbo.fn_formatdatetime(@EndDate,'dd/mmmm/yyyy') AS EndDateDesc

FROM @TblAmount