USE [MinovaES_TPI_Prod]
GO
/****** Object:  StoredProcedure [dbo].[CRPTTUGUPYPOSTTOGL]    Script Date: 23/03/2022 10.34.59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CRPTTUGUPYPOSTTOGL] 
	-- exec Rpt_HR_PY_PostToGL_BNP  '100', '1000', '01', '', '', '', '201102', 'scriberion', 'triadblank'
	-- Add the parameters for the stored procedure here
	@CompanyID VARCHAR(4),
	@PayGroup VARCHAR(5),
	@EmployeeArea VARCHAR(8), 
	@CostCenter VARCHAR(8),
	@EmployeeID VARCHAR(8),
	@RunPeriod varchar(6),
	@key1 AS NVARCHAR(max),
	@key2 AS NVARCHAR(max),
	@EmployeeType VARCHAR(8),
	@MenuID VARCHAR(250),
	@UserID VARCHAR(250)
AS

--DECLARE @CompanyID VARCHAR(4) = '1000'
--DECLARE	@PayGroup VARCHAR(5) = '01'
--DECLARE	@EmployeeArea VARCHAR(8) = ''
--DECLARE	@CostCenter VARCHAR(8) = ''
--DECLARE	@EmployeeID VARCHAR(8) = ''--'00000582'
--DECLARE	@RunPeriod varchar(6) = '202001'
--DECLARE	@key1 AS NVARCHAR(max) = 'scriberion'
--DECLARE	@key2 AS NVARCHAR(max) = 'triadblank'
--DECLARE @EmployeeType VARCHAR(8) = ''
--DECLARE @MenuID VARCHAR(250) = 'woty'
--DECLARE @UserID VARCHAR(250) = 'PYAR40'

--//-- Variable Declaration
DECLARE @payPeriodMonth varchar(2)
DECLARE @payPeriodYear varchar(4)
DECLARE @payPeriodStartDate varchar(8)
DECLARE @payPeriodEndDate varchar(8)
DECLARE @payPeriodEndDateDt datetime

--//-- Table Declaration
DECLARE @t_final TABLE
	(EmployeeID varchar(8),
	 PayPeriodMonth varchar(2),
	 PayPeriodYear varchar(4),
	 RunPeriodMonth varchar(2),
	 RunPeriodYear varchar(4),
	 WageType varchar(20), 
	 WageTypeDesc varchar(250),
	 CostElementDesc VARCHAR (250),
	 CostElement VARCHAR (50),
	 GLAccount VARCHAR (150),
     DEBET DECIMAL(18,2),
     CostCenterAbbr varchar(250),
	 EmployeeArea varchar (12),
	 EmployeeAreaDesc varchar (250),
	 CostCenter varchar(12),
	 CostCenterDesc varchar(250),
	 CostElementGroup VARCHAR(10),
     CostElementGroupDesc VARCHAR(250),
	 EmployeeType VARCHAR(10),
	 EmployeeTypeDesc VARCHAR(250),
	 PeriodMonth VARCHAR(50),
	 CostElementTotal DECIMAL(18,2),
	 CostElementAbbr VARCHAR(250)
	 )

DECLARE @tbl_result TABLE
	(EmployeeID varchar(8),
	 PayPeriodMonth varchar(2),
	 PayPeriodYear varchar(4),
	 RunPeriodMonth varchar(2),
	 RunPeriodYear varchar(4),
	 WageType varchar(20), 
	 WageTypeDesc varchar(250),
	 CostElementDesc VARCHAR (250),
	 CostElement VARCHAR (50),
	 GLAccount VARCHAR (150),
     DEBET DECIMAL(18,2),
     CostCenterAbbr varchar(250),
	 EmployeeArea varchar (12),
	 EmployeeAreaDesc varchar (250),
	 CostCenter varchar(12),
	 CostCenterDesc varchar(250),
	 CostElementGroup VARCHAR(10),
     CostElementGroupDesc VARCHAR(250),
	 EmployeeType VARCHAR(10),
	 EmployeeTypeDesc VARCHAR(250),
	 PeriodMonth VARCHAR(50),
	 CostElementTotal DECIMAL(18,2),
	 CostElementAbbr VARCHAR(250)
	 )
	 
DECLARE @tbl_employee TABLE
	(
	 EmployeeID varchar(8),
	 CostCenter varchar(12),
	 EmployeeArea varchar (12),
	 EmployeeAreaDesc varchar (250),
	 CostCenterDesc varchar(250),
	 CostCenterAbbr varchar(250),
	 EmployeeType VARCHAR(10),
	 EmployeeTypeDesc VARCHAR(250))



DECLARE @tbl_tr_enc TABLE
	(EmployeeID varchar(8),
	 PayPeriodMonth varchar(2),
	 PayPeriodYear varchar(4),
	 RunPeriodMonth varchar(2),
	 RunPeriodYear varchar(4),
	 WageTyoe varchar(4),
	 Amount varchar(250),
	 Rate varchar(250),
	 FlagRetro varchar(3))

DECLARE @tbl_tr_dec TABLE
	(EmployeeID varchar(8),
	 PayPeriodMonth varchar(2),
	 PayPeriodYear varchar(4),
	 RunPeriodMonth varchar(2),
	 RunPeriodYear varchar(4),
	 WageTyoe varchar(4),
	 Amount DECIMAL(22,2),
	 Rate DECIMAL(22,2),
	 FlagRetro varchar(3))
	 
DECLARE @tbl_cu0300 TABLE
	(
	WageType VARCHAR(20) 
    ,StartDate VARCHAR(20)
    ,EndDate VARCHAR(20) 
    ,WageTypeDesc VARCHAR(250) 
    ,CostElement VARCHAR(20) 
    ,CostElementDesc VARCHAR(250) 
    ,GLAccount VARCHAR(250) 
    ,DebitAcc VARCHAR(50) 
	,CreditAcc VARCHAR(50)
    ,CostElementGroup VARCHAR(20)
    ,CostElementGroupDesc VARCHAR(250) 
    ,CostElementAbbr VARCHAR(250)
	)
	
DECLARE @tbl_tr_dec_final TABLE
	(EmployeeID varchar(8),
	 PayPeriodMonth varchar(2),
	 PayPeriodYear varchar(4),
	 RunPeriodMonth varchar(2),
	 RunPeriodYear varchar(4),
	 WageTyoe varchar(4),
	 Amount DECIMAL(22,2),
	 Aate DECIMAL(22,2),
	 FlagRetro varchar(3))
	 
DECLARE @t_ce_area TABLE
(
	CostElement VARCHAR(10)
	,EmployeeArea VARCHAR(10)
)


--//-- Setting variable
SET @payPeriodMonth = SUBSTRING(@RunPeriod, 5, 2)
SET @payPeriodYear = SUBSTRING(@RunPeriod, 1, 4)
SET @payPeriodStartDate = @RunPeriod + '01'
SET @payPeriodEndDateDt = DATEADD(dd, -1, DATEADD(mm, 1, CONVERT(datetime, @payPeriodStartDate)))
SET @payPeriodEndDate = @RunPeriod + CONVERT(varchar(2), DATEPART(dd, @payPeriodEndDateDt))

--//-- Select Employee
INSERT INTO @tbl_employee
SELECT  tr0300.EmployeeID, 
		tr0300.CostCenter, 
		tr0300.EmployeeArea, 
		area.EmployeeAreaDescription,
	    cost_center.ObjectDescription AS CCDesc, 
		cost_center.ObjectDescription AS CCAbbr ----cost_center.abbreviation
	    ,tr0300.EmployeeType, 
		emptype.EmployeeTypeDescription
FROM dbo.PHRPYTR0300 AS tr0300
LEFT JOIN dbo.PHROM0001 AS cost_center
		ON tr0300.CostCenter= cost_center.ObjectID
		AND cost_center.ObjectClass = 'CC'
		AND cost_center.StartDate <= @payPeriodEndDate
		AND cost_center.EndDate >= @payPeriodEndDate
LEFT JOIN dbo.PCMEPEMPAREA AS area
		ON tr0300.EmployeeArea = area.EmployeeArea 
LEFT JOIN dbo.PCMEPEMPTYP AS emptype
		ON emptype.EmployeeType = tr0300.EmployeeType
WHERE (tr0300.EmployeeID = @EmployeeID OR @EmployeeID = '')
	  AND tr0300.PayPeriodMonth = @payPeriodMonth
	  AND tr0300.PayPeriodYear = @payPeriodYear 
	  AND tr0300.RunPeriodMonth = @payPeriodMonth
	  AND tr0300.RunPeriodYear = @payPeriodYear 
	  AND (tr0300.CostCenter = @CostCenter OR @CostCenter = '')
	  AND (tr0300.EmployeeType = @EmployeeType OR @EmployeeType = '')
	  AND (tr0300.PayrollGroup = @PayGroup OR @PayGroup = '')

--//-- Table cu_0300
INSERT INTO @tbl_cu0300
SELECT cu_0300.WageTypeDefinition 
      ,cu_0300.StartDate
      ,cu_0300.EndDate
      ,cu_0300.WTDefinitionDesc  
      ,CE.ObjectID AS CE
      ,CE_Desc.ObjectDescription AS CEDesc
      ,CE.GLAccount
      ,cu_0300.DebitAcc	  
	  ,cu_0300.CreditAcc
      ,eg.ObjectID AS EG
      ,eg.ObjectDescription AS EGDesc
      ,CE_Desc.Abbreviation AS CEAbbr
FROM dbo.PHRPYCU0300 AS cu_0300
LEFT JOIN PHROM0017 AS CE
	  ON CE.StartDate <= @payPeriodEndDate
	  AND CE.EndDate >= @payPeriodEndDate
	  AND (CE.ObjectID = cu_0300.DebitAcc Or CE.ObjectID = cu_0300.CreditAcc)	  
LEFT JOIN dbo.PHROM0001 AS CE_Desc
	  ON CE_Desc.StartDate <= @payPeriodEndDate
	  AND CE_Desc.EndDate >= @payPeriodEndDate
	  AND CE_Desc.ObjectID = CE.ObjectID
	  AND CE_Desc.ObjectClass = 'CE'
LEFT JOIN PHROM0002 AS ce_eg
	  ON CE_Desc.ObjectID = ce_eg.ObjectID
	  AND ce_eg.ObjectClass = 'CE' AND ce_eg.RelationshipClass = 'EG'
	  AND ce_eg.StartDate <= @payPeriodEndDate 
	  AND ce_eg.EndDate >= @payPeriodEndDate
LEFT JOIN PHROM0001 AS eg
	  ON eg.ObjectID = ce_eg.RelationshipObject
	  AND eg.ObjectClass = 'EG'
	  AND eg.StartDate <= @payPeriodEndDate 
	  AND eg.EndDate >= @payPeriodEndDate
WHERE cu_0300.StartDate <= @payPeriodEndDate
      AND cu_0300.EndDate >= @payPeriodEndDate
      AND cu_0300.IsActive = '1'
      AND (cu_0300.DebitAcc <> '' or cu_0300.CreditAcc <> '')
----SELECT * FROM @tbl_cu0300

------------------- Get data payroll --------------------
INSERT INTO @tbl_tr_enc
SELECT  pr.EmployeeID, 
		pr.PayPeriodMonth, pr.PayPeriodYear, 
	    pr.RunPeriodMonth, pr.RunPeriodYear, 
	    pr.WageType, pr.amount, pr.rate, 'N'
FROM dbo.PHRPYTR0301 pr  WITH (NOLOCK)
INNER JOIN @tbl_employee emp
		ON pr.EmployeeID = emp.EmployeeID
WHERE pr.RunPeriodYear = @payPeriodYear
	  AND pr.RunPeriodMonth = @payPeriodMonth
	  AND pr.PayPeriodYear = @payPeriodYear
      AND pr.PayPeriodMonth = @payPeriodMonth
      AND (pr.EmployeeID = @EmployeeID OR @EmployeeID = '')
      AND pr.WageType IN ( SELECT DISTINCT WageType FROM @tbl_cu0300)

INSERT INTO @tbl_tr_enc
SELECT  pr.EmployeeID, 
		pr.PayPeriodMonth, pr.PayPeriodYear, 
	    pr.RunPeriodMonth, pr.RunPeriodYear, 
	    pr.WageType, pr.amount, pr.rate, 'R'
FROM dbo.PHRPYTR0301RET pr  WITH (NOLOCK)
INNER JOIN @tbl_employee emp
		ON pr.EmployeeID = emp.EmployeeID
WHERE pr.RunPeriodYear = @payPeriodYear
	  AND pr.RunPeriodMonth = @payPeriodMonth
	  AND pr.PayPeriodYear = @payPeriodYear
      AND pr.PayPeriodMonth = @payPeriodMonth
      AND (pr.EmployeeID = @EmployeeID OR @EmployeeID = '')
      AND pr.WageType IN ( SELECT DISTINCT WageType FROM @tbl_cu0300)

INSERT INTO @tbl_tr_dec
SELECT pr.EmployeeID, 
	   pr.PayPeriodMonth, pr.PayPeriodYear, 
	   pr.RunPeriodMonth, pr.RunPeriodYear, pr.WageTyoe,
	   CONVERT(decimal(18,0), dbo.SDE(pr.amount, 'M!N0V@2010')),
	   CONVERT(decimal(18,0), dbo.SDE(pr.rate, 'M!N0V@2010')),  pr.FlagRetro
FROM @tbl_tr_enc pr
--select * from @tbl_tr_dec

INSERT INTO @tbl_tr_dec_final
SELECT tbl_tr_dec.EmployeeID,
	   PayPeriodMonth, PayPeriodYear,
	   RunPeriodMonth, RunPeriodYear, tbl_tr_dec.WageTyoe,
	   SUM(tbl_tr_dec.Amount) AS amount, SUM(tbl_tr_dec.Rate) AS rate, 
	   min(tbl_tr_dec.FlagRetro) AS flg_retro
FROM @tbl_tr_dec AS tbl_tr_dec
GROUP BY tbl_tr_dec.EmployeeID, 
		PayPeriodMonth, PayPeriodYear,
		RunPeriodMonth, RunPeriodYear, WageTyoe
----select * from @tbl_tr_dec_final	

INSERT INTO @t_ce_area
SELECT Value1, Value2 FROM dbo.PCMEPGENPARAM WHERE Parameter = 'TUGU_GL_COSTELEMENT' --AND val1 = '00000044'


INSERT INTO @tbl_result
SELECT DISTINCT  
	   tbl_employee.EmployeeID ,
	   tbl_tr_dec_final.PayPeriodMonth ,
	   tbl_tr_dec_final.PayPeriodYear ,
	   tbl_tr_dec_final.RunPeriodMonth ,
	   tbl_tr_dec_final.RunPeriodYear , 
	   tbl_tr_dec_final.WageTyoe , 
	   tbl_cu0300.WageTypeDesc ,
	   --tbl_cu0300.cost_element_name, 
	   --tbl_cu0300.cost_element,
	   CASE 
			WHEN tbl_cu0300.GLAccount <> '' THEN tbl_cu0300.CostElementDesc
			ELSE tbl_employee.EmployeeTypeDesc
	   END, 
	   CASE 
			WHEN tbl_cu0300.GLAccount <> '' THEN tbl_cu0300.CostElement
			ELSE tbl_employee.EmployeeTypeDesc
	   END,
	   CASE 
			WHEN tbl_cu0300.GLAccount <> '' THEN tbl_cu0300.GLAccount
			ELSE ''
	   END,  
	   tbl_tr_dec_final.amount AS DEBET ,
	   tbl_employee.CostCenterAbbr , 
	   tbl_employee.EmployeeArea ,
	   tbl_employee.EmployeeAreaDesc ,
	   tbl_employee.CostCenter , 
	   tbl_employee.CostCenterDesc ,
	   tbl_cu0300.CostElementGroup ,
	   tbl_cu0300.CostElementGroupDesc ,
	   tbl_employee.EmployeeType ,
	   tbl_employee.EmployeeTypeDesc
	   ,dbo.fn_formatdatetime(@RunPeriod + '01', 'mmmm') AS period_month
	   ,CASE WHEN tbl_cu0300.CostElementGroup = '00000003' THEN tbl_tr_dec_final.Amount ELSE tbl_tr_dec_final.Amount * -1 END AS CETotal
	   ,tbl_cu0300.CostElementAbbr
FROM @tbl_tr_dec_final AS tbl_tr_dec_final
LEFT JOIN @tbl_cu0300 AS tbl_cu0300
	   ON tbl_cu0300.WageType  = tbl_tr_dec_final.WageTyoe
LEFT JOIN @tbl_employee AS tbl_employee
	ON tbl_tr_dec_final.EmployeeID = tbl_employee.EmployeeID
WHERE tbl_tr_dec_final.Amount <> 0

------SELECT * FROM @t_ce_area
------SELECT * FROM @tbl_result 

INSERT INTO @t_final
SELECT tr.* 
FROM @tbl_result AS tr
WHERE tr.CostElement NOT IN ( SELECT CostElement FROM @t_ce_area)

DECLARE @c_ce VARCHAR(10)
DECLARE @c_area VARCHAR(10)
DECLARE cur_ce CURSOR FOR SELECT DISTINCT EmployeeArea, CostElement  FROM @t_ce_area 
OPEN cur_ce
FETCH cur_ce INTO @c_area, @c_ce
WHILE @@Fetch_Status = 0
	BEGIN
	
		INSERT INTO @t_final
		SELECT DISTINCT 
				 EmployeeID,
				 PayPeriodMonth,
				 PayPeriodYear,
				 RunPeriodMonth,
				 RunPeriodYear,
				 WageType, 
				 WageTypeDesc,
				 CostElementDesc + ' - ' + CostCenterDesc,
				 CostElement,
				 GLAccount,
				 DEBET,
				 CostCenterAbbr,
				 EmployeeArea,
				 EmployeeAreaDesc,
				 CostCenter,
				 CostCenterDesc,
				 CostElementGroup,
				 CostElementGroupDesc,
				 EmployeeType,
				 EmployeeTypeDesc,
				 PeriodMonth,
				 CostElementTotal,
				 CostElementAbbr
		FROM @tbl_result WHERE CostCenter = @c_area AND CostElement = @c_ce
	
		INSERT INTO @t_final
		SELECT DISTINCT a.* FROM @tbl_result AS a
		WHERE a.CostCenter <> @c_area 
			AND a.CostElement = @c_ce
			AND a.CostCenter NOT IN ( SELECT DISTINCT EmployeeArea FROM @t_ce_area)

			
		--	--AND a.employee_id NOT IN (SELECT DISTINCT employee_id FROM @tbl_result)
		
	FETCH cur_ce INTO @c_area, @c_ce   
	END
CLOSE cur_ce
DEALLOCATE cur_ce

SELECT DISTINCT * FROM @t_final
WHERE CostElementGroupDesc <> 'C. Take Home Pay'		------//------ Add by Tri nwh 20220322 untuk perubahan template


----SELECT tr.* 
----	,dbo.fn_formatdatetime(@run_period + '01', 'mmmm') AS period_month
----	,CASE WHEN cost_element_group_id = '00000003' THEN tr.DEBET ELSE tr.DEBET * -1 END AS ce_total
----FROM @tbl_result AS tr