ALTER PROCEDURE [dbo].[c]
    @CompanyID VARCHAR(4) = NULL ,
    @Year VARCHAR(4) ,
    @TaxOffice VARCHAR(10) ,
    @EmployeeStatus VARCHAR(2) = NULL ,
    @PayrollGroup VARCHAR(2) = NULL ,
    @EmployeeIDFrom VARCHAR(8) = NULL ,
    @EmployeeIDTo VARCHAR(8) = NULL
AS 
    SET NOCOUNT ON;

--DECLARE @CompanyID varchar(4) = ''
--DECLARE @Year varchar(4) = '2024'
--DECLARE @TaxOffice varchar(8) = 'JKT'
--DECLARE @EmployeeStatus varchar(2) = null
--DECLARE @PayrollGroup varchar(2) = ''
--DECLARE @EmployeeIDFrom varchar(8) = '00000017'--'00000300'
--DECLARE @EmployeeIDTo varchar(8) = NULL


    DECLARE @begda INT
    DECLARE @endda INT
    DECLARE @employee_status_beg VARCHAR(5)
    DECLARE @employee_status_end VARCHAR(5)
    DECLARE @payroll_group_beg VARCHAR(5)
    DECLARE @payroll_group_END VARCHAR(5)

--//Set begin & end of period
    SET @begda = @Year + '0101'
    SET @endda = @Year + '1231'

--//Set employee status
    IF @EmployeeStatus = ''
        OR @EmployeeStatus IS NULL 
        BEGIN
            SET @employee_status_beg = '00000'
            SET @employee_status_end = '99999'
        END
    ELSE 
        BEGIN
            SET @employee_status_beg = @EmployeeStatus
            SET @employee_status_end = @EmployeeStatus
        END

--//Set employee group
    IF @PayrollGroup = ''
        OR @PayrollGroup IS NULL 
        BEGIN
            SET @payroll_group_beg = '00000'
            SET @payroll_group_END = '99999'
        END
    ELSE 
        BEGIN
            SET @payroll_group_beg = @PayrollGroup
            SET @payroll_group_END = @PayrollGroup
        END


--//Set employee id start
    IF @EmployeeIDFrom = ''
        OR @EmployeeIDFrom IS NULL 
        BEGIN
            SET @EmployeeIDFrom = '00000000'
            SET @EmployeeIDTo = '99999999'
        END

    IF @EmployeeIDTo = ''
        OR @EmployeeIDTo IS NULL 
        SET @EmployeeIDTo = @EmployeeIDFrom 

--//Get system parameter
    DECLARE @is_encrypted VARCHAR
    DECLARE @npwp_address_subtype VARCHAR(4)

    SELECT  @is_encrypted = Value1
    FROM    dbo.PCMEPGENPARAM
    WHERE   Parameter = 'HR_PY_ENCRYPTED'
            AND StartDate <= @endda
            AND EndDate >= @endda

    SELECT  @npwp_address_subtype = Value1
    FROM    dbo.PCMEPGENPARAM
    WHERE   Parameter = 'HR_PY_NPWP_ADDRESS_SUBTYPE'
            AND StartDate <= @endda
            AND EndDate >= @endda

-----// Get employee tax office list
    DECLARE @emp_office TABLE
        (
          [StartDate] [varchar](8) NOT NULL ,
          [EndDate] [varchar](8) NOT NULL ,
          [EmployeeOffice] [varchar](4) NOT NULL ,
          [EmployeeOfficeDesc] [varchar](50) NULL ,
          [EmployeeArea] [varchar](4) NOT NULL ,
          [TaxOffice] [varchar](4) NULL ,
          [NPWP] [varchar](50) NULL ,
          [Address] [varchar](500) NULL ,
          [EmployeeOfficeGroup] [varchar](5) NULL ,
          [MinimumWageRegional] [varchar](4) NULL ,
          [Notes] [varchar](500) NULL ,
          [CreateBy] [varchar](18) NULL ,
          [CreateDate] [varchar](14) NULL ,
          [ChangeBy] [varchar](18) NULL ,
          [ChangeDate] [varchar](14) NULL
        )

    INSERT  INTO @emp_office
            SELECT  [StartDate] ,
                    [EndDate] ,
                    [EmployeeOffice] ,
                    [EmployeeOfficeDesc] ,
                    [EmployeeArea] ,
                    [TaxOffice] ,
                    [NPWP] ,
                    [Address] ,
                    [EmployeeOfficeGroup] ,
                    [MinimumWageRegional] ,
                    [Notes] ,
                    [CreateBy] ,
                    [CreateDate] ,
                    [ChangeBy] ,
                    [ChangeDate]
            FROM    dbo.PCMEPEMPOFF
            WHERE   TaxOffice = @TaxOffice
	
--Get Employee Tobe Processed
    DECLARE @employee_tobe_process2 TABLE
        (
          emp_id VARCHAR(8) ,
          start_date INT ,
          end_date INT ,
          employee_status VARCHAR(2)
        )

    DECLARE @employee_tobe_process TABLE
        (
          emp_id VARCHAR(8) ,
          start_date INT ,
          end_date INT
        )

    DECLARE @payroll_header TABLE
        (
          employee_id VARCHAR(8) ,
          start_date VARCHAR(8) ,
          end_date VARCHAR(8) ,
          employee_office VARCHAR(4) ,
          run_period_month VARCHAR(2)
        )
    DECLARE @payroll_header_tmp TABLE
        (
          employee_id VARCHAR(8) ,
          start_date VARCHAR(8) ,
          end_date VARCHAR(8) ,
          employee_office VARCHAR(4) ,
          run_period_month VARCHAR(2)
        )
    DECLARE @payroll_header0 TABLE
        (
          employee_id VARCHAR(8) ,
          start_date VARCHAR(8) ,
          end_date VARCHAR(8) ,
          employee_office VARCHAR(4) ,
          run_period_month VARCHAR(2)
        )

    INSERT  INTO @payroll_header_tmp
            SELECT DISTINCT
                    md2.EmployeeID ,
                    ph.StartDate ,
                    ph.EndDate ,
                    md2.EmployeeOffice ,
                    RunPeriodMonth
            FROM    dbo.PHRPYTR0300 ph
                    INNER JOIN dbo.PHRPA0002 md2 ON md2.EmployeeID = ph.EmployeeID
            WHERE   ph.StartDate >= @begda
                    AND ph.EndDate <= @endda
                    AND md2.StartDate <= ph.EndDate
                    AND md2.EndDate >= ph.EndDate
                    AND md2.EmployeeID BETWEEN @EmployeeIDFrom
                                       AND     @EmployeeIDTo
				
--//get the latest run data
    INSERT  INTO @payroll_header0
            SELECT  py_head.*
            FROM    @payroll_header_tmp AS py_head
                    INNER JOIN ( SELECT employee_id ,
                                        start_date ,
                                        end_date ,
                                        MAX(run_period_month) AS run_period_month
                                 FROM   @payroll_header_tmp
                                 GROUP BY employee_id ,
                                        start_date ,
                                        end_date
                               ) a ON py_head.employee_id = a.employee_id
                                      AND py_head.start_date = a.start_date
                                      AND py_head.end_date = a.end_date
                                      AND py_head.run_period_month = a.run_period_month
			
    INSERT  INTO @payroll_header
            SELECT DISTINCT
                    employee_id ,
                    start_date ,
                    end_date ,
                    employee_office ,
                    run_period_month
            FROM    @payroll_header0 ph0
                    INNER JOIN @emp_office eo ON ph0.employee_office = eo.EmployeeOffice
					
----//get list spliiter running payroll for others TAX Office.
    SELECT DISTINCT
            employee_id ,
            start_date ,
            end_date ,
            employee_office ,
            run_period_month
    INTO    #tmp0
    FROM    @payroll_header0 ph0
    WHERE   NOT EXISTS ( SELECT *
                         FROM   @payroll_header ph
                         WHERE  ph0.employee_id = ph.employee_id
                                AND ph0.start_date = ph.start_date
                                AND ph0.end_date = ph.end_date
                                AND ph0.employee_office = ph.employee_office
                                AND ph0.run_period_month = ph.run_period_month )
		
-- Get list date range splitter.
    SELECT  a.employee_id ,
            a.start_date mindt0 ,
            b.start_date maxdt0
    INTO    #tmp
    FROM    @payroll_header a ,
            @payroll_header b
    WHERE   a.employee_id = b.employee_id
            AND b.start_date > a.start_date
            AND ( CONVERT(INT, SUBSTRING(b.start_date, 5, 2))
                  - CONVERT(INT, SUBSTRING(a.start_date, 5, 2)) ) > 1
            AND ( SELECT    COUNT(*)
                  FROM      @payroll_header c
                  WHERE     ( CONVERT(INT, SUBSTRING(a.start_date, 5, 2)) < CONVERT(INT, SUBSTRING(c.start_date,
                                                              5, 2)) )
                            AND ( CONVERT(INT, SUBSTRING(b.start_date, 5, 2)) > CONVERT(INT, SUBSTRING(c.end_date,
                                                              5, 2)) )
                            AND c.employee_id = a.employee_id
                ) = 0
            AND ( SELECT    COUNT(*)
                  FROM      #tmp0 d
                  WHERE     d.employee_id = a.employee_id
                            AND a.start_date < d.start_date
                            AND b.start_date > d.end_date
                ) <> 0
	
    DROP TABLE #tmp0


    DECLARE @n_empty_range INT
	  
    SELECT  @n_empty_range = COUNT(*)
    FROM    #tmp	  

    IF ( @n_empty_range <> 0 ) 
        BEGIN
            INSERT  INTO @employee_tobe_process
                    SELECT  ddd.employee_id ,
                            ddd.mindt ,
                            ddd.maxdt
                    FROM    ( SELECT    dd.employee_id ,
                                        MIN(dd.start_date) mindt ,
                                        MAX(dd.end_date) maxdt
                              FROM      ( SELECT    x.employee_id ,
                                                    x.start_date ,
                                                    x.end_date ,
                                                    SUM(ISNULL(d.flag, 0)) flag
                                          FROM      @payroll_header x
                                                    LEFT OUTER JOIN ( SELECT
                                                              a.* ,
                                                              1 'flag'
                                                              FROM
                                                              @payroll_header a
                                                              LEFT OUTER JOIN #tmp b ON a.employee_id = b.employee_id
                                                              WHERE
                                                              a.start_date <= b.mindt0
                                                              AND a.end_date < b.maxdt0
                                                              ) d ON x.employee_id = d.employee_id
                                                              AND x.start_date = d.start_date
                                                              AND x.end_date = d.end_date
                                          GROUP BY  x.employee_id ,
                                                    x.start_date ,
                                                    x.end_date
                                        ) dd
                              GROUP BY  dd.employee_id ,
                                        dd.flag
                            ) ddd
                    ORDER BY ddd.employee_id ,
                            ddd.mindt	
        END
    ELSE 
        BEGIN
            INSERT  INTO @employee_tobe_process
                    SELECT  main.employee_id ,
                            mindate.mindt ,
                            maxdate.maxdt
                    FROM    ( SELECT DISTINCT
                                        employee_id
                              FROM      @payroll_header
                            ) main
                            INNER JOIN ( SELECT ph.employee_id ,
                                                MIN(ph.start_date) mindt
                                         FROM   @payroll_header ph
                                         GROUP BY employee_id
                                       ) mindate ON main.employee_id = mindate.employee_id
                            INNER JOIN ( SELECT employee_id ,
                                                MAX(end_date) maxdt
                                         FROM   @payroll_header ph
                                         GROUP BY employee_id
                                       ) maxdate ON main.employee_id = maxdate.employee_id
        END
				
    DROP TABLE #tmp           
                
    DECLARE @kode_form VARCHAR(7)
    DECLARE @tahun_pajak VARCHAR(4)
    DECLARE @pembetulan INT
    DECLARE @nomor_urut VARCHAR(7)
    DECLARE @npwp_pegawai VARCHAR(15)
    DECLARE @nama_pegawai VARCHAR(50)
    DECLARE @alamat_pegawai VARCHAR(255)
    DECLARE @jabatan_pegawai VARCHAR(30)
    DECLARE @jenis_kelamin VARCHAR(50)
    DECLARE @status_pegawai VARCHAR(1)
    DECLARE @status_kawin VARCHAR(1)
    DECLARE @flag_asing VARCHAR(1)
    DECLARE @status_ptkp VARCHAR(2)
    DECLARE @jumlah_tanggungan VARCHAR(1)
    DECLARE @masa_perolehan_1 VARCHAR(2)
    DECLARE @masa_perolehan_2 VARCHAR(2)
    DECLARE @flag_status VARCHAR(1)
    DECLARE @a1 DECIMAL
    DECLARE @flg_a2 VARCHAR(1)
    DECLARE @a2 DECIMAL
    DECLARE @a3 DECIMAL
    DECLARE @a4 DECIMAL
    DECLARE @a5 DECIMAL
    DECLARE @a6 DECIMAL
    DECLARE @a7 DECIMAL
    DECLARE @a8 DECIMAL
    DECLARE @a9 DECIMAL
    DECLARE @a10 DECIMAL
    DECLARE @a11 DECIMAL
    DECLARE @a12 DECIMAL
    DECLARE @a13 DECIMAL
    DECLARE @a14 DECIMAL
    DECLARE @a15 DECIMAL
    DECLARE @a16 DECIMAL
    DECLARE @a16_flag DECIMAL
    DECLARE @a17 DECIMAL
    DECLARE @a18 DECIMAL
    DECLARE @a19 DECIMAL
    DECLARE @a19b DECIMAL
    DECLARE @a20 DECIMAL
    DECLARE @a21 DECIMAL
    DECLARE @a22 DECIMAL
    DECLARE @a22a DECIMAL
    DECLARE @a22b DECIMAL
    DECLARE @a23 DECIMAL
    DECLARE @a24 DECIMAL
    DECLARE @flg_a24 VARCHAR(1)
    DECLARE @bln_a24 VARCHAR(6)
    DECLARE @masa_pajak VARCHAR(10)
    DECLARE @kode_pajak VARCHAR(30)
    DECLARE @npwp_pemotong VARCHAR(15)
    DECLARE @nama_pemotong VARCHAR(200)
    DECLARE @company_name VARCHAR(200)
    DECLARE @a19murni DECIMAL
    DECLARE @a17_att DECIMAL
    DECLARE @ktp VARCHAR(50)

    DECLARE @jml_bulan_perolehan INT
    DECLARE @tot_biaya_jabatan DECIMAL

    DECLARE @result TABLE
        (
          kode_form VARCHAR(7) ,
          tahun_pajak VARCHAR(4) ,
          pembetulan INT ,
          nomor_urut CHAR(7) ,
          npwp_pegawai VARCHAR(30) ,
          nama_pegawai VARCHAR(50) ,
          alamat_pegawai VARCHAR(255) ,
          jabatan_pegawai VARCHAR(30) ,
          jenis_kelamin VARCHAR(50) ,
          status_pegawai VARCHAR(5) ,
          status_kawin VARCHAR(5) ,
          flag_asing VARCHAR(1) ,
          status_ptkp VARCHAR(5) ,
          jumlah_tanggungan VARCHAR(1) ,
          masa_perolehan_1 VARCHAR(2) ,
          masa_perolehan_2 VARCHAR(2) ,
          flag_status VARCHAR(1) ,
          a1 DECIMAL ,
          flg_a2 VARCHAR(1) ,
          a2 DECIMAL ,
          a3 DECIMAL ,
          a4 DECIMAL ,
          a5 DECIMAL ,
          a6 DECIMAL ,
          a7 DECIMAL ,
          a8 DECIMAL ,
          a9 DECIMAL ,
          a10 DECIMAL ,
          a11 DECIMAL ,
          a12 DECIMAL ,
          a13 DECIMAL ,
          a14 DECIMAL ,
          a15 DECIMAL ,
          a16 DECIMAL ,
          a17 DECIMAL ,
          a18 DECIMAL ,
          a19 DECIMAL ,
          a20 DECIMAL ,
          a21 DECIMAL ,
          a22 DECIMAL ,
          a22a DECIMAL ,
          a22b DECIMAL ,
          a23 DECIMAL ,
          a24 DECIMAL ,
          flg_a24 VARCHAR(1) ,
          bln_a24 VARCHAR(6)
	--,emp_id VARCHAR(20)
          ,
          KTP VARCHAR(50) ,
          nama_pemotong VARCHAR(200) ,
          npwp_pemotong VARCHAR(30) ,
          kode_pajak VARCHAR(30)
	--,company_name VARCHAR(200)
        )                   
 
    DECLARE @cu0300 TABLE
        (
          [code] [nVARCHAR](4) NOT NULL ,
          [start_date] [nVARCHAR](8) NOT NULL ,
          [end_date] [nVARCHAR](8) NOT NULL ,
          [spt_no] [INT] NULL
        )

    DECLARE @cu0300_tmp TABLE
        (
          [code] [nVARCHAR](4) NOT NULL ,
          [start_date] [nVARCHAR](8) NOT NULL ,
          [end_date] [nVARCHAR](8) NOT NULL ,
          [spt_no] [INT] NULL
        )
	
    DECLARE @tbl_tr_prev_ori TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount DECIMAL ,
          spt_no INT ,
          run_period_month VARCHAR(2)
        )

    DECLARE @tbl_tr_prev_ori_tmp TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount DECIMAL ,
          spt_no INT ,
          run_period_month VARCHAR(2)
        )
 
    DECLARE @tbl_tr_prev_cum TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount DECIMAL ,
          spt_no INT ,
          run_period_month VARCHAR(2)
        )

    DECLARE @tbl_tr_prev_cum_tmp TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount DECIMAL ,
          spt_no INT ,
          run_period_month VARCHAR(2)
        )

    DECLARE @tbl_tr_dec_ori TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount DECIMAL ,
          spt_no INT ,
          run_period_month VARCHAR(2)
        )

    DECLARE @tbl_tr_dec_ori_tmp TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount DECIMAL ,
          spt_no INT ,
          run_period_month VARCHAR(2)
        )
 
    DECLARE @tbl_tr_dec TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount DECIMAL ,
          spt_no INT ,
          run_period_month VARCHAR(2)
        )

    DECLARE @tbl_tr_dec_tmp TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount DECIMAL ,
          spt_no INT ,
          run_period_month VARCHAR(2)
        )

    DECLARE @tbl_tr_dec_total TABLE
        (
          employee_id VARCHAR(8) ,
          amount DECIMAL ,
          spt_no INT
        )
	 
    DECLARE @tbl_tr_enc TABLE
        (
          employee_id VARCHAR(8) ,
          wage_type VARCHAR(4) ,
          amount VARCHAR(250) ,
          spt_no INT
        )
                   
    SET @kode_form = 'D113248'
    SET @tahun_pajak = @Year
    SET @pembetulan = 0
 
    DECLARE @c_landscape VARCHAR(3)
    DECLARE @c_emp_id VARCHAR(8)
    DECLARE @c_start_date INT
    DECLARE @c_end_date INT

    DECLARE @last_period_payroll INT
    DECLARE @previous_period_payroll INT
    DECLARE @previous_period_payroll0 INT
    DECLARE @previous_period_payroll_varchar VARCHAR(2)

----//Run Cursor
    DECLARE cur_employee CURSOR
    FOR
        SELECT  *
        FROM    @employee_tobe_process
    OPEN cur_employee
    FETCH cur_employee INTO @c_emp_id, @c_start_date, @c_end_date
    WHILE @@fetch_status = 0 
        BEGIN  
            SET @last_period_payroll = @c_end_date
            SET @nomor_urut = CONVERT(INTEGER, RIGHT(@c_emp_id, 7))

            SELECT  @npwp_pegawai = REPLACE(REPLACE(ISNULL(NPWP,
                                                           '000000000000000'),
                                                    '.', ''), '-', '') ,
                    @status_kawin = CASE LEFT(TaxStatus, 1)
                                      WHEN 'T' THEN '1'
                                      ELSE '2'
                                    END ,
                    @status_ptkp = a.TaxStatus ,
                    @jumlah_tanggungan = CASE RIGHT(TaxStatus, 1)
                                           WHEN 'K' THEN '0'
                                           ELSE RIGHT(TaxStatus, 1)
                                         END
            FROM    dbo.PHRPA0003 AS a
            WHERE   EmployeeID = @c_emp_id
                    AND StartDate <= @c_end_date
                    AND EndDate >= @c_end_date
	
            SELECT  DISTINCT
                    @a17 = PTKP
            FROM    dbo.PHRPYCU0302
            WHERE   StartDate <= @c_end_date
                    AND EndDate >= @c_end_date
                    AND TaxStatus = ( SELECT DISTINCT
                                                TaxStatus
                                      FROM      dbo.PHRPA0003
                                      WHERE     EmployeeID = @c_emp_id
                                                AND StartDate <= @c_end_date
                                                AND EndDate >= @c_end_date
                                    )

            SELECT  @nama_pegawai = FullName ,
                    @jenis_kelamin = b.GenderDescription ,
                    @ktp = c.IDDescription
            FROM    dbo.PHRPA0001 AS a
                    LEFT JOIN dbo.PHRPAGENDER AS b ON a.Gender = b.Gender
                                                      AND b.StartDate <= @c_end_date
                                                      AND b.EndDate >= @c_end_date
                    LEFT JOIN dbo.PHRPA0013 AS c ON a.EmployeeID = c.EmployeeID
                                                    AND c.StartDate <= @c_end_date
                                                    AND c.EndDate >= @c_end_date
            WHERE   a.EmployeeID = @c_emp_id
                    AND a.StartDate <= @c_end_date
                    AND a.EndDate >= @c_end_date
					AND c.IDType = '01' 

			IF(@ktp IS NULL) ------ tidak punya master data id / ktp add by Tri nwh 20240723
			BEGIN
				 SELECT  @nama_pegawai = FullName ,
                    @jenis_kelamin = b.GenderDescription ,
                    @ktp = a.IDCard
				 FROM    dbo.PHRPA0001 AS a
                 LEFT JOIN dbo.PHRPAGENDER AS b ON a.Gender = b.Gender
                                                      AND b.StartDate <= @c_end_date
                                                      AND b.EndDate >= @c_end_date
					WHERE   a.EmployeeID = @c_emp_id
                    AND a.StartDate <= @c_end_date
                    AND a.EndDate >= @c_end_date
			END
			
            SELECT  @nama_pemotong = t.TaxOfficeDescription ,
                    @npwp_pemotong = o.NPWP
            FROM    dbo.PHRPA0002 AS a
                    LEFT JOIN dbo.PCMEPEMPOFF AS o ON a.EmployeeOffice = o.EmployeeOffice
                                                      AND o.StartDate <= @c_end_date
                                                      AND o.EndDate >= @c_end_date
                    LEFT JOIN dbo.PHRPYTAXOFF AS t ON t.TaxOffice = o.TaxOffice
                                                      AND t.StartDate <= @c_end_date
                                                      AND t.EndDate >= @c_end_date
            WHERE   a.StartDate <= @c_end_date
                    AND a.EndDate >= @c_end_date
                    AND a.EmployeeID = @c_emp_id
			
            SELECT  @kode_pajak = e.TaxCode
            FROM    dbo.PHRPA0002 AS a
                    LEFT JOIN dbo.PCMEPEMPTYP AS e ON a.EmployeeType = e.EmployeeType
            WHERE   a.StartDate <= @c_end_date
                    AND a.EndDate >= @c_end_date
                    AND a.EmployeeID = @c_emp_id
			
	--//alamat & flag status belakangan
            SELECT  @alamat_pegawai = REPLACE(md06.Address, ',', ' ')
            FROM    dbo.PHRPA0006 AS md06
            WHERE   EmployeeID = @c_emp_id
                    AND StartDate <= @c_end_date
                    AND EndDate >= @c_end_date
                    AND AddressType = @npwp_address_subtype

            SELECT  @jabatan_pegawai = mdobj.ObjectDescription
            FROM    dbo.PHRPA0002 AS md02
                    INNER JOIN dbo.PHROM0001 AS mdobj ON md02.Position = mdobj.ObjectID
                                                         AND mdobj.ObjectClass = 'P'
                                                         AND mdobj.StartDate <= @c_end_date
                                                         AND mdobj.EndDate >= @c_end_date
            WHERE   md02.EmployeeID = @c_emp_id
                    AND md02.StartDate <= @c_end_date
                    AND md02.EndDate >= @c_end_date
	
            SELECT  @status_pegawai = CASE EmployeeStatus
                                        WHEN '01' THEN '1'
                                        WHEN '03' THEN '2'
                                        ELSE '1'
                                      END ,
                    @flag_asing = CASE EmployeeType
                                    WHEN '05' THEN '1'
                                    ELSE '0'
                                  END
            FROM    dbo.PHRPA0002 AS md02
            WHERE   EmployeeID = @c_emp_id
                    AND StartDate <= @c_end_date
                    AND EndDate >= @c_end_date

            SET @masa_perolehan_1 = CONVERT(INTEGER, SUBSTRING(CONVERT(VARCHAR, @c_start_date),
                                                              5, 2))
            SET @masa_perolehan_2 = CONVERT(INTEGER, SUBSTRING(CONVERT(VARCHAR, @c_end_date),
                                                              5, 2))
            SET @jml_bulan_perolehan = ( CONVERT(INT, @masa_perolehan_2)
                                         - CONVERT(INT, @masa_perolehan_1) + 1 )

	--//#region getwtconfiguration
            DELETE  @cu0300_tmp

            INSERT  INTO @cu0300_tmp
                    SELECT  WageTypeDefinition ,
                            StartDate ,
                            EndDate ,
                            SPTNo
                    FROM    dbo.PHRPYCU0300
                    WHERE   StartDate <= @c_end_date
                            AND EndDate >= @c_start_date
                            AND SPTNo > 0

            UPDATE  @cu0300_tmp
            SET     start_date = @begda
            WHERE   start_date < @c_start_date
	  
            UPDATE  @cu0300_tmp
            SET     end_date = @endda
            WHERE   end_date > @c_end_date

--/*
            DELETE  @cu0300

            INSERT  INTO @cu0300
                    SELECT  cu0300.*
                    FROM    @cu0300_tmp AS cu0300
                            INNER JOIN ( SELECT code ,
                                                MIN(start_date) AS start_date ,
                                                MAX(end_date) AS end_date
                                         FROM   @cu0300_tmp
                                         GROUP BY code
                                       ) a ON cu0300.code = a.code
                                              AND cu0300.end_date = a.end_date

            DELETE  @tbl_tr_dec
            DELETE  @tbl_tr_dec_ori

            DELETE  @tbl_tr_dec_tmp
            DELETE  @tbl_tr_dec_ori_tmp

            DECLARE @cc_landscape VARCHAR(3)
            DECLARE @cc_code VARCHAR(4)
            DECLARE @cc_start_date INT
            DECLARE @cc_end_date INT
            DECLARE @cc_spt_no INT

            INSERT  INTO @tbl_tr_dec_tmp
                    SELECT  tr301.EmployeeID ,
                            tr301.WageType ,
                            CAST(REPLACE(ISNULL(dbo.GetPEN(tr301.Amount), '0'),
                                         ',', '.') AS DECIMAL(22, 0)) ,
                            cu300.spt_no ,
                            tr301.RunPeriodMonth
                    FROM    dbo.PHRPYTR0301CUM AS tr301
                            INNER JOIN @cu0300 AS cu300 ON cu300.code = tr301.WageType
                    WHERE   tr301.EmployeeID = @c_emp_id
                            AND tr301.PayPeriodMonth = SUBSTRING(CONVERT(VARCHAR, @last_period_payroll),
                                                              5, 2)
                            AND tr301.PayPeriodYear = tr301.RunPeriodYear
                            AND tr301.RunPeriodYear = LEFT(@last_period_payroll,
                                                           4)
		  
	--//get the latest run data
            INSERT  INTO @tbl_tr_dec
                    SELECT  tr_dec.*
                    FROM    @tbl_tr_dec_tmp AS tr_dec
                            INNER JOIN ( SELECT employee_id ,
                                                wage_type ,
                                                MAX(run_period_month) AS run_period_month
                                         FROM   @tbl_tr_dec_tmp
                                         GROUP BY employee_id ,
                                                wage_type
                                       ) a ON tr_dec.employee_id = a.employee_id
                                              AND tr_dec.wage_type = a.wage_type
                                              AND tr_dec.run_period_month = a.run_period_month


            INSERT  INTO @tbl_tr_dec_ori_tmp
                    SELECT  tr301.EmployeeID ,
                            tr301.WageType ,
                            CAST(REPLACE(ISNULL(dbo.GetPEN(tr301.Amount), '0'),
                                         ',', '.') AS DECIMAL(22, 0)) ,
                            cu300.spt_no ,
                            tr301.RunPeriodMonth
                    FROM    dbo.PHRPYTR0301 AS tr301
                            INNER JOIN @cu0300 AS cu300 ON cu300.code = tr301.WageType
                    WHERE   tr301.EmployeeID = @c_emp_id
                            AND tr301.PayPeriodMonth = SUBSTRING(CONVERT(VARCHAR, @last_period_payroll),
                                                              5, 2)
                            AND tr301.PayPeriodYear = tr301.RunPeriodYear
                            AND tr301.RunPeriodYear = LEFT(@last_period_payroll,
                                                           4)
		  
	--//get the latest run data
            INSERT  INTO @tbl_tr_dec_ori
                    SELECT  tr_dec_ori.*
                    FROM    @tbl_tr_dec_ori_tmp AS tr_dec_ori
                            INNER JOIN ( SELECT employee_id ,
                                                wage_type ,
                                                MAX(run_period_month) AS run_period_month
                                         FROM   @tbl_tr_dec_ori_tmp
                                         GROUP BY employee_id ,
                                                wage_type
                                       ) a ON tr_dec_ori.employee_id = a.employee_id
                                              AND tr_dec_ori.wage_type = a.wage_type
                                              AND tr_dec_ori.run_period_month = a.run_period_month
		
            IF ( @masa_perolehan_1 > '1'
                 OR @masa_perolehan_1 > '01'
               ) 
                BEGIN
                    SET @previous_period_payroll = CONVERT(INT, @masa_perolehan_1)
                        - 1
                    IF ( @previous_period_payroll < 10 ) 
                        SET @previous_period_payroll_varchar = '0'
                            + CONVERT(VARCHAR(1), @previous_period_payroll)
                    ELSE 
                        SET @previous_period_payroll_varchar = CONVERT(VARCHAR(2), @previous_period_payroll)
			
		--//get the latest pay period if previous period no pay period.
                    SET @previous_period_payroll0 = @previous_period_payroll
                    SELECT TOP ( 1 )
                            @previous_period_payroll0 = CONVERT(INT, PayPeriodMonth)
                    FROM    dbo.PHRPYTR0300
                    WHERE   EmployeeID = @c_emp_id
                            AND PayPeriodMonth <= @previous_period_payroll_varchar
                            AND PayPeriodYear = LEFT(@last_period_payroll, 4)
                            AND RunPeriodYear = LEFT(@last_period_payroll, 4)
                    ORDER BY PayPeriodMonth DESC
			
                    IF ( @previous_period_payroll0 < 10 ) 
                        SET @previous_period_payroll_varchar = '0'
                            + CONVERT(VARCHAR(1), @previous_period_payroll0)
                    ELSE 
                        SET @previous_period_payroll_varchar = CONVERT(VARCHAR(2), @previous_period_payroll0)
					
                    DELETE  FROM @tbl_tr_prev_ori
                    DELETE  FROM @tbl_tr_prev_cum
		
                    DELETE  FROM @tbl_tr_prev_ori_tmp
                    DELETE  FROM @tbl_tr_prev_cum_tmp

                    INSERT  INTO @tbl_tr_prev_ori_tmp
                            SELECT  tr301.EmployeeID ,
                                    tr301.WageType ,
                                    CAST(REPLACE(ISNULL(dbo.GetPEN(tr301.Amount),
                                                        '0'), ',', '.') AS DECIMAL(22,
                                                              0)) ,
                                    cu300.spt_no ,
                                    tr301.RunPeriodMonth
                            FROM    dbo.PHRPYTR0301 AS tr301
                                    INNER JOIN @cu0300 AS cu300 ON cu300.code = tr301.WageType
                            WHERE   tr301.EmployeeID = @c_emp_id
                                    AND tr301.PayPeriodMonth = @previous_period_payroll_varchar
                                    AND tr301.PayPeriodYear = tr301.RunPeriodYear
                                    AND tr301.RunPeriodMonth = LEFT(@last_period_payroll,
                                                              4)

		--//get the latest run data
                    INSERT  INTO @tbl_tr_prev_ori
                            SELECT  tr_prev_ori.*
                            FROM    @tbl_tr_prev_ori_tmp AS tr_prev_ori
                                    INNER JOIN ( SELECT employee_id ,
                                                        wage_type ,
                                                        MAX(run_period_month) AS run_period_month
                                                 FROM   @tbl_tr_prev_ori_tmp
                                                 GROUP BY employee_id ,
                                                        wage_type
                                               ) a ON tr_prev_ori.employee_id = a.employee_id
                                                      AND tr_prev_ori.wage_type = a.wage_type
                                                      AND tr_prev_ori.run_period_month = a.run_period_month

                    INSERT  INTO @tbl_tr_prev_cum_tmp
                            SELECT  tr301.EmployeeID ,
                                    tr301.WageType ,
                                    CAST(REPLACE(ISNULL(dbo.GetPEN(tr301.Amount),
                                                        '0'), ',', '.') AS DECIMAL(22,
                                                              0)) ,
                                    cu300.spt_no ,
                                    tr301.RunPeriodMonth
                            FROM    dbo.PHRPYTR0301CUM AS tr301
                                    INNER JOIN @cu0300 AS cu300 ON cu300.code = tr301.WageType
                            WHERE   tr301.EmployeeID = @c_emp_id
                                    AND tr301.PayPeriodMonth = @previous_period_payroll_varchar
                                    AND tr301.PayPeriodYear = tr301.RunPeriodYear
                                    AND tr301.RunPeriodYear = LEFT(@last_period_payroll,
                                                              4)

		--//get the latest run data
                    INSERT  INTO @tbl_tr_prev_cum
                            SELECT  tr_prev_cum.*
                            FROM    @tbl_tr_prev_cum_tmp AS tr_prev_cum
                                    INNER JOIN ( SELECT employee_id ,
                                                        wage_type ,
                                                        MAX(run_period_month) AS run_period_month
                                                 FROM   @tbl_tr_prev_cum_tmp
                                                 GROUP BY employee_id ,
                                                        wage_type
                                               ) a ON tr_prev_cum.employee_id = a.employee_id
                                                      AND tr_prev_cum.wage_type = a.wage_type
                                                      AND tr_prev_cum.run_period_month = a.run_period_month	
                END 

            DECLARE @emp_status VARCHAR(2) ,
                @emp_type VARCHAR(2) ,
                @movement_type VARCHAR(2) ,
                @movement_reason VARCHAR(2) ,
                @emp_type_payroll VARCHAR(20) ,
                @movement_reason_payroll VARCHAR(20) ,
                @is_pegawai_baru VARCHAR(1)
		
            DECLARE @death_mov_reason VARCHAR(5)
            SELECT TOP ( 1 )
                    @death_mov_reason = Value1
            FROM    dbo.PCMEPGENPARAM
            WHERE   Parameter = 'HR_ESPT_MOV_REASON_DEATH'
                    AND StartDate <= @endda
                    AND EndDate >= @endda
		
            SELECT  @emp_status = md02.EmployeeStatus ,
                    @emp_type = md02.EmployeeType ,
                    @movement_type = md02.MovementType ,
                    @movement_reason = md02.MovementReason ,
                    @emp_type_payroll = ret.PayrollProcess ,
                    @movement_reason_payroll = rmr.PayrollProcess
            FROM    dbo.PHRPA0002 md02
                    INNER JOIN dbo.PCMEPEMPTYP ret ON md02.EmployeeType = ret.EmployeeType
                    INNER JOIN dbo.PCMEPMOVR rmr ON md02.MovementReason = rmr.MovementReason
            WHERE   EmployeeID = @c_emp_id
                    AND md02.StartDate <= @endda
                    AND md02.EndDate >= @endda
		
	
            SET @is_pegawai_baru = '0'
            IF @c_start_date > @begda 
                BEGIN
                    SELECT  @is_pegawai_baru = CASE WHEN HiringDate >= @c_start_date
                                                    THEN '1'
                                                    ELSE '0'
                                               END
                    FROM    dbo.PHRPA0015
                    WHERE   EmployeeID = @c_emp_id
                END

	--//Setting flag status
            IF @emp_status = '01'
                AND @masa_perolehan_1 = '1'
                AND @masa_perolehan_2 = '12'
                AND ISNULL(@emp_type_payroll, '') <> 'EXP' 
                SET @flag_status = '0'
            ELSE 
                IF @emp_status = '01'
                    AND @masa_perolehan_1 = '1'
                    AND @masa_perolehan_2 = '12'
                    AND ISNULL(@emp_type_payroll, '') = 'EXP' 
                    SET @flag_status = '3'
                ELSE 
                    IF @emp_status = '01'
                        AND @masa_perolehan_2 <> '12' 
                        SET @flag_status = '1'
                    ELSE 
                        IF @is_pegawai_baru = '1' 
                            SET @flag_status = '4'
                        ELSE 
                            IF @emp_status = '01'
                                AND @masa_perolehan_2 <> '1'
                                AND @masa_perolehan_2 = '12' 
                                SET @flag_status = '5'
                            ELSE 
                                IF ( @movement_type = '17'
                                     OR @emp_status <> '01'
                                   ) 
                                    BEGIN
                                        IF @movement_reason = '12' 
                                            SET @flag_status = '2'
                                        ELSE 
                                            IF @movement_reason = @death_mov_reason
                                                OR @emp_type_payroll = 'EXP' 
                                                SET @flag_status = '3'
                                            ELSE 
                                                IF @masa_perolehan_1 = '1'
                                                    AND @masa_perolehan_2 = '12' 
                                                    SET @flag_status = '0'
                                                ELSE 
                                                    SET @flag_status = '2'
                                    END

            SELECT  @a1 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 1
            GROUP BY spt_no

            IF ( @masa_perolehan_1 <> '1' ) 
                SELECT  @a1 = @a1 - ISNULL(SUM(amount), 0)
                FROM    @tbl_tr_prev_cum
                WHERE   spt_no = 1
                GROUP BY spt_no

            SET @a1 = ISNULL(@a1, 0)
            SELECT  @a2 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 2
            GROUP BY spt_no

            IF ( @masa_perolehan_1 <> '1' ) 
                SELECT  @a2 = @a2 - ISNULL(SUM(amount), 0)
                FROM    @tbl_tr_prev_cum
                WHERE   spt_no = 2
                GROUP BY spt_no
				  
				
            SET @a2 = ISNULL(@a2, 0)
	
            SELECT  @a3 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 3
            GROUP BY spt_no
	  
            IF ( @masa_perolehan_1 <> '1' ) 
                SELECT  @a3 = @a3 - ISNULL(SUM(amount), 0)
                FROM    @tbl_tr_prev_cum
                WHERE   spt_no = 3
                GROUP BY spt_no
				  
            SET @a3 = ISNULL(@a3, 0)
	
            SELECT  @a4 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 4
            GROUP BY spt_no
	
            SET @a4 = ISNULL(@a4, 0)
	
            SELECT  @a5 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 5
            GROUP BY spt_no
					  
            IF ( @masa_perolehan_1 <> '1' ) 
                SELECT  @a5 = @a5 - ISNULL(SUM(amount), 0)
                FROM    @tbl_tr_prev_cum
                WHERE   spt_no = 5
                GROUP BY spt_no
					  
            SET @a5 = ISNULL(@a5, 0)
            SELECT  @a6 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 6
            GROUP BY spt_no
	
            SET @a6 = ISNULL(@a6, 0)

            SET @a7 = ISNULL(@a1 + @a2 + @a3 + @a4 + @a5 + @a6, 0)

            SELECT  @a8 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 8
            GROUP BY spt_no
					  
            IF ( @masa_perolehan_1 <> '1' ) 
                SELECT  @a8 = @a8 - ISNULL(SUM(amount), 0)
                FROM    @tbl_tr_prev_cum
                WHERE   spt_no = 8
                GROUP BY spt_no

            SET @a8 = ISNULL(@a8, 0)

            SET @a9 = ISNULL(@a7 + @a8, 0)

            SELECT  @a10 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 10
            GROUP BY spt_no

            IF ( @masa_perolehan_1 <> '1' ) 
                SELECT  @a10 = @a10 - ISNULL(SUM(amount), 0)
                FROM    @tbl_tr_prev_cum
                WHERE   spt_no = 10
                GROUP BY spt_no

            SET @a10 = ISNULL(@a10, 0)

            SET @tot_biaya_jabatan = 500000 * @jml_bulan_perolehan
            IF ( @a10 > @tot_biaya_jabatan ) 
                BEGIN
                    SET @a10 = @tot_biaya_jabatan
                    SET @a11 = 0
                END

            SELECT  @a11 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 11
            GROUP BY spt_no
					  
            IF ( @masa_perolehan_1 <> '1' ) 
                SELECT  @a11 = @a11 - ISNULL(SUM(amount), 0)
                FROM    @tbl_tr_prev_cum
                WHERE   spt_no = 11
                GROUP BY spt_no
					  
            SET @a11 = ISNULL(@a11, 0)

            IF ( ( @a10 + @a11 ) > @tot_biaya_jabatan ) 
                BEGIN 
                    SET @a11 = @tot_biaya_jabatan - @a10
                END

            SELECT  @a12 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 12
            GROUP BY spt_no

            IF ( @masa_perolehan_1 <> '1' ) 
                SELECT  @a12 = @a12 - ISNULL(SUM(amount), 0)
                FROM    @tbl_tr_prev_cum
                WHERE   spt_no = 12
                GROUP BY spt_no

            SET @a12 = ISNULL(@a12, 0)
	
            SET @a13 = ISNULL(@a10 + @a11 + @a12, 0)
	
            SET @a14 = ISNULL(@a9 - @a13, 0)

            SELECT  @a15 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec_ori
            WHERE   spt_no = 15
            GROUP BY spt_no
            SET @a15 = ISNULL(@a15, 0)

            SET @a16 = ISNULL(@a14 + @a15, 0)

	--//spt_no 16 case khusus flag_status khusus
            SELECT  @a16_flag = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec_ori
            WHERE   spt_no = 16
            GROUP BY spt_no
            SET @a16_flag = ISNULL(@a16_flag, 0)

            IF @movement_reason = @death_mov_reason
                OR ISNULL(@emp_type_payroll, '') = 'EXP' 
                BEGIN
                    SELECT  @a16 = CASE WHEN ISNULL(@emp_type_payroll, '') = 'EXP'
                                        THEN @a16
                                        ELSE @a16 * 12
                                             / ( CONVERT(INT, @masa_perolehan_2)
                                                 - CONVERT(INT, @masa_perolehan_1)
                                                 + 1 )
                                   END
                END
	
            IF @flag_status = '1'
                OR @flag_status = '3' 
                BEGIN
                    SET @a16 = @a16_flag
                END

            SET @a18 = @a16 - @a17
	
            IF ( RIGHT(RTRIM(CAST(@a18 AS VARCHAR(19))), 3) <> '000' ) 
                BEGIN
                    DECLARE @Pembulatan1000 AS VARCHAR(19)
                    SET @Pembulatan1000 = CAST(@a18 AS VARCHAR(19)); 		
                    SET @a18 = SUBSTRING(@Pembulatan1000, 1,
                                         LEN(RTRIM(@Pembulatan1000)) - 3)
                        + REPLICATE('0', 3); 		
                END

	--//ambil mt murni tanpa kondisi
            SELECT  @a19murni = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 19
            GROUP BY spt_no

	--//ambil att murni tanpa kondisi
            SELECT  @a17_att = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec_ori
            WHERE   spt_no = 99
            GROUP BY spt_no

	--//cek status employee apakah sudah resign
            SELECT  @a19 = ISNULL(SUM(amount), 0)
            FROM    @tbl_tr_dec
            WHERE   spt_no = 19
            GROUP BY spt_no

            IF ISNULL(@emp_type_payroll, '') = 'EXP' 
                BEGIN
                    SELECT  @a19b = ISNULL(SUM(amount), 0)
                    FROM    @tbl_tr_prev_cum
                    WHERE   spt_no = 19
                    GROUP BY spt_no
                    SET @a19 = @a19 * 12 / ( CONVERT(INT, @masa_perolehan_2)
                                             - CONVERT(INT, @masa_perolehan_1)
                                             + 1 )			
                END
            ELSE 
                IF ( @movement_type = 17
                     OR @emp_status <> '01'
                   ) 
                    BEGIN
                        IF @movement_reason = '12' 
                            BEGIN
                                SET @a19 = ISNULL(@a19murni, 0)
                            END
                    END
                ELSE 
                    BEGIN
                        IF ( ( @masa_perolehan_1 = '1' )
                             AND ( @masa_perolehan_2 <> '12' )
                           ) 
                            BEGIN
                                SET @a19 = @a19 * 12
                                    / CONVERT(INT, @masa_perolehan_2)
                            END
                    END

            SET @a19 = ISNULL(@a19, 0)

            SELECT  @a20 = CASE WHEN CONVERT(INT, @masa_perolehan_1) > 1
                                THEN ISNULL(SUM(amount), 0)
                                ELSE 0
                           END
            FROM    @tbl_tr_dec_ori
            WHERE   spt_no = 20
            GROUP BY spt_no

            SET @a20 = ISNULL(@a20, 0)
			
	------------------ // cek apa sdh pernah running di period sebelum, untuk kebutuhan init paycum, mengganggap a20 lama atau a18 baru menjadi 0 // -----------------
            DECLARE @flag_running VARCHAR(200)
            SELECT  @flag_running = CASE WHEN COUNT(tr.EmployeeID) <= 0
                                         THEN 'no_data'
                                         ELSE 'yes_data'
                                    END
            FROM    dbo.PHRPYTR0300 AS tr
            WHERE   tr.RunPeriodMonth = tr.PayPeriodMonth
                    AND tr.RunPeriodYear = tr.PayPeriodYear
                    AND tr.RunPeriodMonth = @previous_period_payroll_varchar
                    AND tr.EmployeeID = @c_emp_id
	------------------ // pengecekan running selesai // ------------------ 

            IF ( @emp_status = '01'
                 AND NOT ( @masa_perolehan_1 = '1'
                           AND @masa_perolehan_2 = '12'
                         )
               )
                OR ISNULL(@emp_type_payroll, '') = 'EXP'
                OR @movement_reason = @death_mov_reason 
                BEGIN	
                    IF @flag_running = 'no_data'  -----// by Tri 20151229 untuk perubahan case upload init paycum
                        BEGIN
                            SET @a21 = ISNULL(@a19murni - 0, 0)
                        END 
                    ELSE 
                        BEGIN
                            SET @a21 = ISNULL(@a19murni - @a20, 0)
                        END --@a2--@a19 								
                END

--		ELSE
            SET @a21 = ISNULL(@a19 - @a20, 0)

		----// sementara di hard-code dulu, untuk handling case mks.

            SET @a22 = @a21 + @a20
            SET @a22a = 0
            SET @a22b = @a21
            SET @a23 = ISNULL(@a21 - @a22, 0)
            SET @a24 = ISNULL(@a23, 0)

            SET @bln_a24 = SUBSTRING(CONVERT(VARCHAR, @c_end_date), 5, 2)
                + LEFT(CONVERT(VARCHAR, @c_end_date), 4) 

            IF ( @a21 - @a22 = 0 ) 
                BEGIN
                    SET @flg_a24 = '0'
                    SET @bln_a24 = ''
                END

            IF ( @a21 - @a22 > 0 ) 
                BEGIN
                    SET @flg_a24 = '1'
                END

            IF ( @a21 - @a22 < 0 ) 
                BEGIN
                    SET @flg_a24 = '2'
                END

            SET @flg_a2 = '0'
		
		--//Pegawai yang dipindahkan
            SET @flg_a2 = '1'

            DELETE  @tbl_tr_dec
		
            INSERT  INTO @result
                    SELECT  @kode_form ,
                            @tahun_pajak ,
                            @pembetulan ,
                            CONVERT(CHAR, RIGHT('0000000'
                                                + CONVERT(VARCHAR(7), @nomor_urut),
                                                7)) AS nomor_urut ,
                            @npwp_pegawai ,
                            @nama_pegawai ,
                            @alamat_pegawai ,
                            @jabatan_pegawai ,
                            @jenis_kelamin ,
                            @status_pegawai ,
                            @status_kawin ,
                            @flag_asing ,
                            @status_ptkp ,
                            @jumlah_tanggungan ,
                            @masa_perolehan_1 ,
                            @masa_perolehan_2 ,
                            @flag_status ,
                            @a1 ,
                            @flg_a2 ,
                            @a2 ,
                            @a3 ,
                            @a4 ,
                            @a5 ,
                            @a6 ,
                            @a7 ,
                            @a8 ,
                            @a9 ,
                            @a10 ,
                            @a11 ,
                            @a12 ,
                            @a13 ,
                            @a14 ,
                            @a15 ,
                            @a16 ,
                            @a17 ,
                            @a18 ,
                            @a17_att ,
                            @a20 ,
                            @a21 ,
                            @a22 ,
                            @a22a ,
                            @a22b ,
                            @a23 ,
                            @a24 ,
                            @flg_a24 ,
                            @bln_a24 ,
                            @ktp
		--, @c_emp_id
                            ,
                            @nama_pemotong ,
                            @npwp_pemotong ,
                            @kode_pajak
		--, @company_name
					

            SET @nomor_urut = NULL
            SET @npwp_pegawai = NULL
            SET @nama_pegawai = NULL
            SET @alamat_pegawai = NULL
            SET @jabatan_pegawai = NULL
            SET @jenis_kelamin = NULL
            SET @status_pegawai = NULL
            SET @status_kawin = NULL
            SET @flag_asing = NULL
            SET @status_ptkp = NULL
            SET @jumlah_tanggungan = NULL
            SET @masa_perolehan_1 = NULL
            SET @masa_perolehan_2 = NULL
            SET @flag_status = NULL
            SET @a1 = NULL
            SET @flg_a2 = NULL
            SET @a2 = NULL
            SET @a3 = NULL
            SET @a4 = NULL
            SET @a5 = NULL
            SET @a6 = NULL
            SET @a7 = NULL
            SET @a8 = NULL
            SET @a9 = NULL
            SET @a10 = NULL
            SET @a11 = NULL
            SET @a12 = NULL
            SET @a13 = NULL
            SET @a14 = NULL
            SET @a15 = NULL
            SET @a16 = NULL
            SET @a16_flag = NULL
            SET @a17 = NULL
            SET @a18 = NULL
            SET @a19 = NULL
            SET @a20 = NULL
            SET @a21 = NULL
            SET @a22 = NULL
            SET @a22a = NULL
            SET @a22b = NULL
            SET @a23 = NULL
            SET @a24 = NULL
            SET @flg_a24 = NULL
            SET @bln_a24 = NULL

            FETCH cur_employee INTO @c_emp_id, @c_start_date, @c_end_date   
        END
    CLOSE cur_employee
    DEALLOCATE cur_employee

    UPDATE  @result
    SET     npwp_pegawai = '000000000000000'
    WHERE   LEN(npwp_pegawai) < 15

    UPDATE  @result
    SET     alamat_pegawai = '-'
    WHERE   alamat_pegawai = ''
            OR alamat_pegawai IS NULL
		
    SELECT  masa_perolehan_2 AS TaxPeriod ,
            tahun_pajak AS TaxableYear,
            pembetulan AS TaxCorrection ,
            '1.1-' + CONVERT(CHAR(2), RIGHT('00'
                                            + CONVERT(VARCHAR(2), masa_perolehan_2),
                                            2)) + '.' + RIGHT(@Year, 2) + '-'
            + nomor_urut AS WithholdingTax ,
            --CONVERT(CHAR(2), RIGHT('00' + CONVERT(VARCHAR(2), masa_perolehan_2),
            --                       2)) AS bulan_bukti_potong ,
            --RIGHT(@Year, 2) AS tahun_bukti_potong ,
            --nomor_urut ,
            masa_perolehan_1 AS AcquisitionPeriod ,
            masa_perolehan_2 AS AcquisitionPeriodFinal ,
            npwp_pegawai AS NPWP ,
            --RIGHT(npwp_pegawai, 3) AS tiga_akhir_npwp_pegawai ,
            --SUBSTRING(npwp_pegawai, 10, 3) AS enam_akhir_npwp_pegawai ,
            --LEFT(npwp_pegawai, 9) AS awal_npwp_pegawai ,
            --SUBSTRING(npwp_pegawai, 1, 2) + '.' + SUBSTRING(npwp_pegawai, 3, 3)
            --+ '.' + SUBSTRING(npwp_pegawai, 6, 3) + '.'
            --+ SUBSTRING(npwp_pegawai, 9, 1) AS awal_titik_npwp_pegawai ,
            ktp AS EmployeeID ,
            nama_pegawai AS Name,
            alamat_pegawai AS Address,
            CASE WHEN jenis_kelamin = 'W'
                      OR jenis_kelamin = 'Wanita'
                      OR jenis_kelamin = 'Female'
                      OR jenis_kelamin = 'F' OR jenis_kelamin = '02' OR jenis_kelamin = 'Perempuan' 
					  THEN 'F'
                 WHEN jenis_kelamin = 'P'
                      OR jenis_kelamin = 'Pria'
                      OR jenis_kelamin = 'Male'
                      OR jenis_kelamin = 'M' OR jenis_kelamin = '01' OR jenis_kelamin = 'Laki-laki' 
					  THEN 'M'
            END AS Gender ,
            CASE WHEN status_ptkp = 'TK' THEN 'TK'
                 WHEN status_ptkp = 'K1'
                      OR status_ptkp = 'K2'
                      OR status_ptkp = 'K3' THEN 'K'
                 WHEN status_ptkp = 'T1'
                      OR status_ptkp = 'T2'
                      OR status_ptkp = 'T3' THEN 'HB'
            END AS PTKPStatus ,
            CASE WHEN status_ptkp = 'TK' THEN '0'
                 ELSE RIGHT(status_ptkp, 1)
            END AS NumberOfChildren --,jumlah_tanggungan
            ,
            jabatan_pegawai AS JobDescription ,
            CASE WHEN flag_asing = 0 THEN 'N'
                 ELSE 'Y'
            END AS NonResidentTaxpayer ,
            '' AS CountryCode ,
            kode_pajak AS TaxCode ,
            a1 AS Amount1 ,
            a2 AS Amount2 ,
            a3 AS Amount3 ,
            a4 AS Amount4 ,
            a5 AS Amount5 ,
            a6 AS Amount6 ,
            a8 AS Amount7 ,
            a9 AS Amount8 ,
            a10 + a11 AS Amount9 ,
            a12 AS Amount10 ,
            a13 AS Amount11 ,
            a14 AS Amount12 ,
            a15 AS Amount13 ,
            CASE WHEN a16 < 0 THEN 0
                 ELSE a16
            END AS Amount14 ,
            a17 AS Amount15 ,
            CASE WHEN a18 < 0 THEN 0
                 ELSE a18
            END AS Amount16 ,
            a19 AS Amount17 --@a17_att AS a17 
            ,
            CASE WHEN a20 < 0 THEN 0
                 ELSE a20
            END AS Amount18 ,
            a21 AS Amount19 ,
            a22 AS Amount20 ,
            '' AS MovementStatus ,
            npwp_pemotong AS NPWPPemotong ,
            --RIGHT(npwp_pemotong, 3) AS tiga_akhir_npwp_pemotong ,
            --SUBSTRING(npwp_pemotong, 10, 3) AS enam_akhir_npwp_pemotong ,
            --LEFT(npwp_pemotong, 9) AS awal_npwp_pemotong ,
            --SUBSTRING(npwp_pemotong, 1, 2) + '.' + SUBSTRING(npwp_pemotong, 3,
            --                                                 3) + '.'
            --+ SUBSTRING(npwp_pemotong, 6, 3) + '.' + SUBSTRING(npwp_pemotong,
            --                                                  9, 1) AS awal_titik_npwp_pemotong ,
            nama_pemotong AS NamaPemotong ,
            dbo.fn_formatdatetime(GETDATE(), 'dd/mm/yyyy') AS TanggalBuktiPotong 
            --dbo.fn_formatdatetime(GETDATE(), 'dd') AS day_tgl_bukti_potong ,
            --dbo.fn_formatdatetime(GETDATE(), 'mm') AS month_tgl_bukti_potong ,
            --dbo.fn_formatdatetime(GETDATE(), 'yyyy') AS year_tgl_bukti_potong
    FROM    @result