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