Rpt_IMFI_HR_PY_BankTransfer_New.sql - IMFI - Redmine

Feature #4143 » Rpt_IMFI_HR_PY_BankTransfer_New.sql

Tri Rizqiaty, 01/15/2026 04:43 PM

    
      ??ALTER PROCEDURE [dbo].[Rpt_IMFI_HR_PY_BankTransfer_New]

          @landscape AS VARCHAR(3) ,

          @companycode AS VARCHAR(5) ,

          @paygroup AS VARCHAR(50) ,

          @payperiod AS VARCHAR(6) ,

          @bank_id AS VARCHAR(2) ,

          @tgltrans AS VARCHAR(8) ,

          @key1 AS NVARCHAR(MAX) ,

          @key2 AS NVARCHAR(MAX)

      AS 

      --DECLARE @landscape AS VARCHAR(3) 

      --DECLARE @companycode AS VARCHAR(5) 

      --DECLARE @paygroup AS VARCHAR(50) 

      --DECLARE @payperiod AS VARCHAR(6) 

      --DECLARE @bank_id AS VARCHAR(2) 

      --DECLARE @tgltrans AS VARCHAR(8) 

      --SET @landscape = '100'

      --SET @companycode = '1000' 

      --SET @paygroup = '13'

      --SET @payperiod = '202501'

      --SET @bank_id = '02' 

      --SET @tgltrans = '20250101'

      DECLARE @companyname AS NVARCHAR(50)

      DECLARE @companylogo AS VARBINARY(MAX)

      DECLARE @payrollgroup AS NVARCHAR(50)

      DECLARE @paymonth AS NVARCHAR(2)

      DECLARE @payyear AS NVARCHAR(4)

      DECLARE @begda AS NVARCHAR(8)

      DECLARE @endda AS NVARCHAR(8)

      DECLARE @enddate AS VARCHAR(8)

      SET @paymonth = RIGHT(@payperiod, 2)

      SET @payyear = LEFT(@payperiod, 4)

      SET @begda = @payperiod + '01'

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

      DECLARE @last_date AS VARCHAR(12) 

      DECLARE @last_year AS VARCHAR(8)

      DECLARE @last_month AS VARCHAR(8)

      SET @last_date = CONVERT(VARCHAR(6), DATEADD(d, 0, DATEADD(m, -1, @begda)), 112)

      SET @last_year = LEFT(@last_date, 4)

      SET @last_month = RIGHT(@last_date, 2)

      -----//----- tabel-tabel internal

      DECLARE @tbl_emp TABLE

      (

          emp_id VARCHAR(8) ,

          emp_name VARCHAR(250) ,

          [bank_id] [varchar](2) ,

          [bank_account] [varchar](50) ,

          [pay_grade] [varchar](2) ,

          [tax_status] [varchar](2) ,

          [npwp] [varchar](50) ,

          [jamsostek_type] [varchar](4) ,

          [jamsostek] [varchar](50) ,

          [jamsostek_level] [varchar](5) ,

          [cost_center] [varchar](8) ,

          [employee_area] [varchar](4) ,

          [employee_office] [varchar](4) ,

          [employee_status] [varchar](2) ,

          [employee_type] [varchar](2) ,

          [employee_subtype] [varchar](2) ,

          [bank_account_name] [varchar](250) ,

          [payroll_group] [varchar](2) ,

          [organization] [varchar](8) ,

          [position] [varchar](8) ,

          [job] [varchar](8) ,

          bank_name VARCHAR(250)

      	,CustomerReff VARCHAR(250)

      	,NoRow DECIMAL(18,0)

      )

      DECLARE @tbl_tr_enc TABLE

      (

          landscape VARCHAR(3) ,

          employee_id VARCHAR(8) ,

          payPeriodMonth VARCHAR(2) ,

          payPeriodYear VARCHAR(4) ,

          runPeriodMonth VARCHAR(2) ,

          runPeriodYear VARCHAR(4) ,

          wage_type VARCHAR(4) ,

          amount VARCHAR(250) ,

          rate VARCHAR(250) ,

          flg_retro VARCHAR(3) ,

          flg_period VARCHAR(20)

      )

      DECLARE @tbl_tr_dec TABLE

      (

          landscape VARCHAR(3) ,

          employee_id VARCHAR(8) ,

          payPeriodMonth VARCHAR(2) ,

          payPeriodYear VARCHAR(4) ,

          runPeriodMonth VARCHAR(2) ,

          runPeriodYear VARCHAR(4) ,

          wage_type VARCHAR(4) ,

          amount INT ,

          rate INT ,

          flg_retro VARCHAR(3) ,

          flg_period VARCHAR(20)

      )

      DECLARE @tbl_tr_dec_sum TABLE

      (

          landscape VARCHAR(3) ,

          employee_id VARCHAR(8) ,

          payPeriodMonth VARCHAR(2) ,

          payPeriodYear VARCHAR(4) ,

          runPeriodMonth VARCHAR(2) ,

          runPeriodYear VARCHAR(4) ,

          wage_type VARCHAR(4) ,

          amount DECIMAL(22,0) ,

          rate DECIMAL(22,0) ,

          flg_retro VARCHAR(1) ,

          flg_period VARCHAR(20)

      )

      DECLARE @tbl_sum TABLE

      (

          landscape VARCHAR(3) ,

          runPeriodMonth VARCHAR(2) ,

          runPeriodYear VARCHAR(4) ,

      	SumAmount DECIMAL(22,0) ,

      	SumRate DECIMAL(22,0) ,

      	SumpEmpID DECIMAL(22,0) ,

      	Cek VARCHAR(250)

      )

      DECLARE @t_paygroup TABLE ( paygroup VARCHAR(20) )

      SELECT  @companyname = description ,

      		@companylogo = logo

      FROM base_cust_ref_companycode

      WHERE (landscape = @landscape) AND (companycode = @companycode)

      SELECT @payrollgroup = description

      FROM base_cust_ref_payroll_group

      WHERE (landscape = @landscape) AND (payroll_group = @paygroup)

      -----//----- pengambilan data employee

      INSERT  INTO @tbl_emp

      SELECT  tr300.employee_id ,

              md1.full_name ,

              md3.[bank_id] ,

              md3.[bank_account] ,

              md3.[pay_grade] ,

              md3.[tax_status] ,

              md3.[npwp] ,

              md3.[jamsostek_type] ,

              md3.[jamsostek] ,

              md3.[jamsostek_level] ,

              tr300.[cost_center] ,

              tr300.[employee_area] ,

              tr300.[employee_office] ,

              tr300.[employee_status] ,

              tr300.[employee_type] ,

              tr300.[employee_subtype] ,

              md3.[bank_account_name] ,

              tr300.[payroll_group] ,

              tr300.[organization] ,

              tr300.[position] ,

              tr300.[job] ,

              bank.description 

      		,'A' + tr300.payroll_group + @payyear + @paymonth + tr300.employee_id AS CustomerReff

      		,ROW_NUMBER() OVER(ORDER BY tr300.employee_id ASC) AS NoRow

      FROM    hr_tr0300 AS tr300

      LEFT JOIN hr_md_emp_md0001 AS md1 ON tr300.landscape = md1.landscape

      			AND tr300.employee_id = md1.emp_id

      			AND md1.start_date <= @endda

      			AND md1.end_date >= @endda

      LEFT JOIN hr_md_emp_md0003 AS md3 ON tr300.landscape = md3.landscape

                  AND tr300.employee_id = md3.emp_id

                  AND md3.start_date <= @endda

                  AND md3.end_date >= @endda

      LEFT JOIN base_cust_ref_bankid AS bank ON bank.code = md3.bank_id

      			AND bank.landscape = md3.landscape

      WHERE   md3.bank_id = @bank_id

              AND tr300.landscape = @landscape

      		AND tr300.payroll_group = @paygroup 

              AND tr300.pay_period_month = @paymonth

              AND tr300.pay_period_year = @payyear

              AND tr300.run_period_month = @paymonth

              AND tr300.run_period_year = @payyear 

      --------------- get data payroll --------------

      INSERT  INTO @tbl_tr_enc

      SELECT  pr.landscape ,

              pr.employee_id ,

              pr.pay_period_month ,

              pr.pay_period_year ,

              pr.run_period_month ,

              pr.run_period_year ,

              pr.wage_type ,

              pr.amount ,

              pr.rate ,

              'N' ,

              'now'

      FROM    hr_tr0301 pr WITH ( NOLOCK )

      INNER JOIN @tbl_emp emp ON pr.employee_id = emp.emp_id

      WHERE   pr.pay_period_month = @paymonth

              AND pr.pay_period_year = @payyear

              AND pr.run_period_month = @paymonth

              AND pr.run_period_year = @payyear

              AND pr.wage_type IN ( 'THP' )

      INSERT  INTO @tbl_tr_enc

      SELECT  pr.landscape ,

              pr.employee_id ,

              pr.pay_period_month ,

              pr.pay_period_year ,

              pr.run_period_month ,

              pr.run_period_year ,

              pr.wage_type ,

              pr.amount ,

              pr.rate ,

              'R' ,

              'now'

      FROM    hr_tr0301_retro pr WITH ( NOLOCK )

      INNER JOIN @tbl_emp emp ON pr.employee_id = emp.emp_id

      WHERE   pr.pay_period_month = @paymonth

              AND pr.pay_period_year = @payyear

              AND pr.run_period_month = @paymonth

              AND pr.run_period_year = @payyear

              AND pr.wage_type IN ( 'THP' )

      INSERT  INTO @tbl_tr_dec

      SELECT  pr.landscape ,

              pr.employee_id ,

              pr.payPeriodMonth ,

              pr.payPeriodYear ,

              pr.runPeriodMonth ,

              pr.runPeriodYear ,

              pr.wage_type ,

              CONVERT(DECIMAL(18, 0), dbo.SDE(pr.amount, 'M!N0V@2010')) ,

              CONVERT(DECIMAL(18, 0), dbo.SDE(pr.rate, 'M!N0V@2010')) ,

              pr.flg_retro ,

              pr.flg_period

      FROM    @tbl_tr_enc pr

      INSERT  INTO @tbl_tr_dec_sum

      SELECT  tbl_tr_dec.landscape ,

              employee_id ,

              payPeriodMonth ,

              payPeriodYear ,

              runPeriodMonth ,

              runPeriodYear ,

              wage_type ,

              SUM(amount) AS amount ,

              SUM(rate) AS rate ,

              MIN(flg_retro) AS flg_retro ,

              flg_period

      FROM    @tbl_tr_dec AS tbl_tr_dec

      WHERE   tbl_tr_dec.amount <> '0'

      GROUP BY tbl_tr_dec.landscape ,

              employee_id ,

              payPeriodMonth ,

              payPeriodYear ,

              runPeriodMonth ,

              runPeriodYear ,

              wage_type ,

              flg_period

      INSERT INTO @tbl_sum

      SELECT landscape ,runPeriodMonth ,runPeriodYear

      		,SUM(amount) AS SumAmount ,SUM(rate) AS SumRate ,COUNT(employee_id) AS SumEmpID

      		,CONVERT(VARCHAR(250),(COUNT(employee_id))) + CONVERT(VARCHAR(250),(SUM(amount))) AS Cek

      FROM @tbl_tr_dec_sum

      GROUP BY landscape, runPeriodMonth, runPeriodYear

      SELECT DISTINCT emp.NoRow

      				,emp.emp_id

      				,emp.emp_name

      				,emp.bank_account

      				,emp.bank_account_name

      				,emp.bank_id

      				,emp.bank_name

      				,emp.CustomerReff

      				,decsum.amount

      				,tblsum.SumAmount

      				,tblsum.SumpEmpID

      				,tblsum.Cek

      				,dbo.fn_formatdatetime(@tgltrans,'ddmmyyyy') AS ValueDate --,dbo.fn_formatdatetime(GETDATE(),'ddmmyyyy') AS ValueDate

      				,'020101999999301' AS DebitAccount

      FROM @tbl_emp AS emp

      LEFT JOIN @tbl_tr_dec_sum AS decsum

      	ON emp.emp_id = decsum.employee_id

      LEFT JOIN @tbl_sum AS tblsum

      	ON tblsum.landscape = decsum.landscape

      	AND tblsum.runPeriodMonth = decsum.runPeriodMonth

      	AND tblsum.runPeriodYear = decsum.runPeriodYear

(5-5/6)

Project

General

Profile

IMFI

Feature #4143 » Rpt_IMFI_HR_PY_BankTransfer_New.sql