MinovaEasy

??ALTER PROCEDURE [dbo].[PRPTPYSPTBULANANHEADER]

(

	@CompanyID VARCHAR(4) ,

    @PayGroup VARCHAR(8) ,

    @EmployeeArea VARCHAR(8),

	@EmployeeOffice VARCHAR(8),

	@TaxOffice VARCHAR(8),

    @PayPeriod VARCHAR(6)

)

AS

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

--DECLARE @PayGroup VARCHAR(5) = ''

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

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

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

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

--DECLARE @PayPeriod VARCHAR(6) = '202401'

--DECLARE @Key1 NVARCHAR(MAX) = 'scriberion'

--DECLARE @Key2 NVARCHAR(MAX) = 'triadblank'

DECLARE @paymonth AS NVARCHAR(2)

DECLARE @payyear AS NVARCHAR(4)

DECLARE @begda AS NVARCHAR(8)

DECLARE @endda AS NVARCHAR(8)

SET @paymonth = RIGHT(@PayPeriod, 2)

SET @payyear = LEFT(@PayPeriod, 4)

SET @begda = @PayPeriod + '01'

SET @endda = CONVERT(NVARCHAR(8), DATEADD(d, -1, DATEADD(m, 1, @begda)), 112)

DECLARE @hr_tr0300 TABLE

    (

      [employee_id] [varchar](8) ,

      [pay_period_month] [varchar](2) ,

      [pay_period_year] [varchar](4) ,

      [run_period_month] [varchar](2) ,

      [run_period_year] [varchar](4) ,

      [split_indicator] [varchar](1) ,

      [start_date] [varchar](8) ,

      [end_date] [varchar](8) ,

      [data_status] [varchar](1) ,

      [company_id] [varchar](4) ,

      [cost_center] [varchar](8) ,

      [employee_area] [varchar](4) ,

      [employee_office] [varchar](4) ,

      [employee_status] [varchar](2) ,

      [employee_type] [varchar](2) ,

      [employee_subtype] [varchar](4) ,

      [payroll_group] [varchar](4) ,

      [organization] [varchar](8) ,

      [position] [varchar](8) ,

      [job] [varchar](8) ,

      [pay_type] [varchar](2) ,

      [pay_class] [varchar](2) ,

      [pay_grade] [varchar](4) ,

      [pay_area] [varchar](2)

    )

INSERT  INTO @hr_tr0300

        SELECT  EmployeeID ,

                PayPeriodMonth ,

                PayPeriodYear ,

                RunPeriodMonth ,

                RunPeriodYear ,

                SplitIndicator ,

                StartDate ,

                EndDate ,

                DataStatus ,

                CompanyID ,

                CostCenter ,

                EmployeeArea ,

                EmployeeOffice ,

                EmployeeStatus ,

                EmployeeType ,

                EmployeeSubType ,

                PayrollGroup ,

                Organization ,

                Position ,

                Job ,

                PayrollType ,

                PayrollClass ,

                PayrollGrade ,

                PayrollArea

        FROM    PHRPYTR0300 AS TR300

        WHERE   ( StartDate <= @endda )

                AND ( EndDate >= @endda )

                AND ( PayPeriodMonth = @paymonth )

                AND ( PayPeriodYear = @payyear )

                AND ( RunPeriodMonth = @paymonth )

                AND ( RunPeriodYear = @payyear )

--//-- Get Company Logo & Name

DECLARE @description VARCHAR(50)

DECLARE @logo VARCHAR(50)

SELECT  @description = COMP.CompanyDescription ,

        @logo = LOGO.FileName

FROM    dbo.PCMEPCOMPID AS COMP

        INNER JOIN @hr_tr0300 AS hr_tr0300 ON COMP.CompanyID = hr_tr0300.company_id

        LEFT JOIN dbo.SFILECATALOG LOGO ON COMP.Logo = LOGO.FileName

WHERE   COMP.CompanyID = @CompanyID 

--//-- Get Payroll Group

DECLARE @py_group_desc VARCHAR(50)

DECLARE @py_group VARCHAR(2)

SELECT  @py_group = PYGRUP.PayrollGroup ,

        @py_group_desc = PYGRUP.PayrollGroupDescription

FROM    dbo.PHRPYPGRUP AS PYGRUP

        INNER JOIN @hr_tr0300 AS hr_tr0300 ON PYGRUP.PayrollGroup = hr_tr0300.payroll_group 

--//--Get Employee Area

DECLARE @area_desc VARCHAR(50)

DECLARE @emp_area_id VARCHAR(50)

SELECT  @area_desc = EmployeeAreaDescription ,

        @emp_area_id = EmployeeArea

FROM    dbo.PCMEPEMPAREA AS EMPAREA

        INNER JOIN @hr_tr0300 AS hr_tr0300 ON EMPAREA.CompanyID = hr_tr0300.company_id

                                              AND EMPAREA.EmployeeArea = hr_tr0300.employee_area