CRPTTUGUPYPOSTTOGL_20220223.sql - TUGU PRATAMA INDONESIA (TPI) - Redmine

Feature #984 » CRPTTUGUPYPOSTTOGL_20220223.sql

Tri Rizqiaty, 03/23/2022 10:40 AM

    
      ??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

(2-2/3)

Project

General

Profile

TUGU PRATAMA INDONESIA (TPI)

Feature #984 » CRPTTUGUPYPOSTTOGL_20220223.sql