RptSPTahun_20220125.sql - BBG - Redmine

Bug #777 » RptSPTahun_20220125.sql

Tri Rizqiaty, 01/25/2022 03:25 PM

    
      ??USE [MinovaES_BBG_Prod]

      GO

      /****** Object:  StoredProcedure [dbo].[RptSPTahun]    Script Date: 25/01/2022 15.24.15 ******/

      SET ANSI_NULLS ON

      GO

      SET QUOTED_IDENTIFIER ON

      GO

      --USE [MinovaES_BBG_Prod]

      --GO

      --/****** Object:  StoredProcedure [dbo].[RptSPTahun]    Script Date: 08/02/2021 11:13:16 ******/

      --SET ANSI_NULLS ON

      --GO

      --SET QUOTED_IDENTIFIER ON

      --GO

      ALTER PROCEDURE [dbo].[RptSPTahun]

          @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) = '1000'

      --DECLARE @Year varchar(4) = '2021'

      --DECLARE @TaxOffice varchar(8) = 'BMI'

      --DECLARE @EmployeeStatus varchar(2) = null

      --DECLARE @PayrollGroup varchar(2) = '04'

      --DECLARE @EmployeeIDFrom varchar(8) = '00000160'--'00000300'

      --DECLARE @EmployeeIDTo varchar(8) = '00000160'

          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 ,

      					CASE WHEN md2.TaxOffice <> '' THEN md2.TaxOffice ELSE md2.EmployeeOffice END ,   --------- //// Edit By Tri nwh 20220125  -----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

          INSERT  INTO @payroll_header  -------- //// add By Tri nwh 20220125

                  SELECT DISTINCT

                          employee_id ,

                          start_date ,

                          end_date ,

                          employee_office ,

                          run_period_month

                  FROM    @payroll_header0 ph0

                  WHERE ph0.employee_office = @TaxOffice

      ----//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(1)

          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(5) ,

                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 -----  dikomen by Tri

      					@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

                          LEFT JOIN dbo.PHRPA0013 AS c ON a.EmployeeID = c.EmployeeID

                                                          AND c.StartDate <= @c_end_date

                                                          AND c.EndDate >= @c_end_date

      													AND c.IDType = '01' -----  add by Tri

                  WHERE   a.EmployeeID = @c_emp_id

                          AND a.StartDate <= @c_end_date

                          AND a.EndDate >= @c_end_date

                          -----AND c.IDType = '01' -----  dikomen by Tri

                  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)

      			DECLARE @MTPrevious Decimal(22,0)

      			SELECT @MTPrevious = (amount * 12) / @previous_period_payroll

      			FROM @tbl_tr_prev_cum WHERE wage_type = 'MT'

                  /* 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) */ ----- dikomen karena apit * aprt di ES belum tersimpan di table result by Tri

      			SET @a20 = CASE WHEN CONVERT(INT, @masa_perolehan_1) > 1

                                      THEN ISNULL(SUM(@MTPrevious), 0)

                                      ELSE 0

                                 END

      	------------------ // 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)*/   --------------- dikomen karena apit * aprt di ES belum tersimpan di table result by Tri

      			SET @a21 = CASE WHEN CONVERT(INT, @masa_perolehan_1) > 1

                                      THEN ISNULL(@a19,0)

                                      ELSE ISNULL(@a19 - @a20, 0)

                                 END

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

                  /*SET @a22 = @a21 + @a20*/

      			SET @a22 = CASE WHEN CONVERT(INT, @masa_perolehan_1) > 1

                                      THEN ISNULL(@a21,0)

                                      ELSE ISNULL(@a21 + @a20, 0)

                                 END

                  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' THEN 'F'

                       WHEN jenis_kelamin = 'P'

                            OR jenis_kelamin = 'Pria'

                            OR jenis_kelamin = 'Male'

                            OR jenis_kelamin = 'M' 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

(1-1/1)

Project

General

Profile

BBG

Bug #777 » RptSPTahun_20220125.sql