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