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
                                                   AND PA03.StartDate <= @endda
                                                   AND PA03.EndDate >= @endda
                LEFT JOIN dbo.PHRPA0001 AS PA01 ON TR300.EmployeeID = PA01.EmployeeID
                                                   AND PA01.StartDate <= @endda
                                                   AND PA01.EndDate >= @endda
                LEFT JOIN dbo.PHRPYPGRUP AS PYG ON PYG.PayrollGroup = PA02.PayrollGroup
                LEFT JOIN dbo.PCMEPEMPTYP AS EMPTYPE ON EMPTYPE.EmployeeType = PA02.EmployeeType
                LEFT JOIN dbo.PCMEPEMPOFF AS EMPOFF ON PA02.EmployeeOffice = EMPOFF.EmployeeOffice
        WHERE   TR300.PayPeriodMonth = TR300.RunPeriodMonth
                AND TR300.PayPeriodYear = TR300.RunPeriodYear
                AND TR300.RunPeriodMonth = @paymonth
                AND TR300.RunPeriodYear = @payyear
                AND PA02.StartDate <= @endda
                AND PA02.EndDate >= @endda
                AND ( PA02.PayrollGroup = @PayGroup
                      OR @PayGroup = ''
                    )
                AND ( PA02.EmployeeArea = @EmployeeArea
                      OR @EmployeeArea = ''
                    )
                AND ( PA02.EmployeeOffice = @EmployeeOffice
                      OR @EmployeeOffice = ''
                    )

INSERT  INTO @tbl_tr_enc
        SELECT  PR.EmployeeID ,
                PR.PayPeriodMonth ,
                PR.PayPeriodYear ,
                PR.RunPeriodMonth ,
                PR.RunPeriodYear ,
                PR.WageType ,
                PR.Amount ,
                PR.Rate ,
                'N'
        FROM    PHRPYTR0301 PR WITH ( NOLOCK )
                INNER JOIN @t_emp AS t_emp ON PR.EmployeeID = t_emp.emp_id
        WHERE   PR.PayPeriodMonth = @paymonth
                AND PR.PayPeriodYear = @payyear
                AND PR.RunPeriodMonth = @paymonth
                AND PR.RunPeriodYear = @payyear 
		--AND pr.wage_type in ('RN','RG','MT')
                AND PR.WageType IN ( 'TGI', 'MT' )

INSERT  INTO @tbl_tr_enc
        SELECT  PR.EmployeeID ,
                PR.PayPeriodMonth ,
                PR.PayPeriodYear ,
                PR.RunPeriodMonth ,
                PR.RunPeriodYear ,
                PR.WageType ,
                PR.Amount ,
                PR.Rate ,
                'R'
        FROM    PHRPYTR0301RET PR WITH ( NOLOCK )
                INNER JOIN @t_emp AS t_emp ON PR.EmployeeID = t_emp.emp_id
        WHERE   PR.PayPeriodMonth = @paymonth
                AND PR.PayPeriodYear = @payyear
                AND PR.RunPeriodMonth = @paymonth
                AND PR.RunPeriodYear = @payyear 
		--AND pr.wage_type in ('RN','RG','MT')
                AND PR.WageType IN ( 'TGI', 'MT' )

INSERT  INTO @tbl_tr_dec
        SELECT  PR.employee_id ,
                PR.payPeriodMonth ,
                PR.payPeriodYear ,
                PR.runPeriodMonth ,
                PR.runPeriodYear ,
                PR.wage_type ,
                CONVERT(DECIMAL(22, 0), CONVERT(NVARCHAR(22), dbo.GetPEN(PR.Rate))) ,
                CONVERT(DECIMAL(22, 0), dbo.GetPEN(PR.Rate)) ,
                PR.flg_retro
        FROM    @tbl_tr_enc AS PR		

INSERT  INTO @tbl_tr_dec_sum
        SELECT  employee_id ,
                payPeriodMonth ,
                payPeriodYear ,
                runPeriodMonth ,
                runPeriodYear ,
                wage_type ,
                SUM(amount) AS amount ,
                SUM(rate) AS rate ,
                MIN(flg_retro) AS flg_retro
        FROM    @tbl_tr_dec AS tbl_tr_dec
        GROUP BY employee_id ,
                payPeriodMonth ,
                payPeriodYear ,
                runPeriodMonth ,
                runPeriodYear ,
                wage_type

SELECT DISTINCT
        @paymonth AS bulan ,
        @payyear AS tahun ,
        pyg ,
        pyg_desc ,
        emp_id ,
        emp_name ,
        npwp_no ,
        TGI ,
        MT ,
        1 AS emp ,
        kode_pajak ,
        tax_office_m2 ,
        tax_office_off ,
        tax_office
FROM    ( SELECT DISTINCT
                    t_emp.* ,
                    t_dec.wage_type ,
                    t_dec.amount
          FROM      @t_emp AS t_emp
                    INNER JOIN @tbl_tr_dec_sum AS t_dec ON t_emp.emp_id = t_dec.employee_id
        ) AS SourceData PIVOT
( SUM(amount) FOR wage_type IN ( [TGI], [MT] ) ) AS pvt
WHERE   ( tax_office = @TaxOffice
          OR @TaxOffice = ''
        )