CLIENT SUPPORT » PERTALIFE Bugs

??USE [MinovaES_Pertalife_Prod]

GO

/****** Object:  StoredProcedure [dbo].[PRPTPYSPTBULANAN]    Script Date: 06/06/2023 11.05.28 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PRPTPYSPTBULANAN]

(

	@CompanyID VARCHAR(4) ,

    @PayGroup VARCHAR(5) ,

    @EmployeeArea VARCHAR(8),

	@EmployeeOffice VARCHAR(8),

	@TaxOffice VARCHAR(8),

    @PayPeriod VARCHAR(6) ,

    @MenuID VARCHAR(10) ,

    @UserID VARCHAR(10)

)

AS

--DECLARE @CompanyID AS VARCHAR(5) 

--DECLARE @PayGroup AS VARCHAR(5) 

--DECLARE @PayPeriod AS VARCHAR(6) 

--DECLARE @pembetulan AS VARCHAR(4) 

--DECLARE @EmployeeArea AS VARCHAR(8)

--DECLARE @EmployeeOffice AS VARCHAR(8)

--DECLARE @TaxOffice AS VARCHAR(8)

--DECLARE @MenuID AS VARCHAR(10) = 'PA01'

--DECLARE @UserID AS VARCHAR(10) = 'minovais'

--SET @CompanyID = '1000' 

--SET @PayGroup = '0006'

--SET @PayPeriod = '202211'

--SET @pembetulan = ''

--SET @EmployeeArea = ''

--SET @EmployeeOffice = ''

--SET @TaxOffice = ''

------------------ // Get Otorisasi //---------------

    DECLARE @TableResult TABLE

	 ( 

		EmployeeID VARCHAR(20) )

INSERT INTO @TableResult

EXEC dbo.GETEMPIDAUTHBYMENU 

	@MenuID = @MenuID, -- varchar(max)

    @UserID = @UserID -- varchar(max)

------------------ // End Get Otorisasi //---------------

DECLARE @t_emp TABLE

    (

      pyg VARCHAR(8) ,

      pyg_desc VARCHAR(125) ,

      emp_id VARCHAR(10) ,

      emp_name VARCHAR(225) ,

      npwp_no VARCHAR(60) ,

      kode_pajak VARCHAR(60) ,

      tax_office_m2 VARCHAR(60) ,

      tax_office_off VARCHAR(60) ,

      tax_office VARCHAR(60)

    )

DECLARE @tbl_tr_enc TABLE

    (

      employee_id VARCHAR(8) ,

      payPeriodMonth VARCHAR(2) ,

      payPeriodYear VARCHAR(4) ,

      runPeriodMonth VARCHAR(2) ,

      runPeriodYear VARCHAR(4) ,

      wage_type VARCHAR(4) ,

      amount VARCHAR(250) ,

      rate VARCHAR(250) ,

      flg_retro VARCHAR(3)

    )

DECLARE @tbl_tr_dec TABLE

    (

      employee_id VARCHAR(8) ,

      payPeriodMonth VARCHAR(2) ,

      payPeriodYear VARCHAR(4) ,

      runPeriodMonth VARCHAR(2) ,

      runPeriodYear VARCHAR(4) ,

      wage_type VARCHAR(4) ,

      amount DECIMAL(22,0) ,

      rate DECIMAL(22,0) ,

      flg_retro VARCHAR(3)

    )

DECLARE @tbl_tr_dec_sum TABLE

    (

      employee_id VARCHAR(8) ,

      payPeriodMonth VARCHAR(2) ,

      payPeriodYear VARCHAR(4) ,

      runPeriodMonth VARCHAR(2) ,

      runPeriodYear VARCHAR(4) ,

      wage_type VARCHAR(4) ,

      amount DECIMAL(22,0) ,

      rate DECIMAL(22,0) ,

      flg_retro VARCHAR(1)

    )

DECLARE @paymonth AS NVARCHAR(2)

DECLARE @payyear AS NVARCHAR(4)

DECLARE @begda AS NVARCHAR(8)

DECLARE @endda AS NVARCHAR(8)

DECLARE @enddate AS VARCHAR(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)

INSERT  INTO @t_emp

        SELECT DISTINCT

				PA02.PayrollGroup,     

                PYG.PayrollGroupDescription ,

                PA02.EmployeeID ,

                PA01.FullName ,

                CASE WHEN PA03.NPWP = '' THEN '000000000'

                     WHEN PA03.NPWP = '-' THEN '000000000'

                     ELSE PA03.NPWP

                END AS NPWP ,

                EMPTYPE.TaxCode ,

                PA02.TaxOffice TaxOffice_PA2 ,

                EMPOFF.TaxOffice TaxOffice_OFF ,

                CASE WHEN PA02.TaxOffice <> '' THEN PA02.TaxOffice

                     ELSE EMPOFF.TaxOffice

                END TaxOffice

        FROM    dbo.PHRPYTR0300 AS TR300

                INNER JOIN dbo.PHRPA0002 AS PA02 ON TR300.EmployeeID = PA02.EmployeeID

                INNER JOIN @TableResult AS TableRef ON TableRef.EmployeeID = PA02.EmployeeID

                LEFT JOIN dbo.PHRPA0003 AS PA03 ON TR300.EmployeeID = PA03.EmployeeID