ALTER PROCEDURE [dbo].[Rpt_Mega_HR_PY_BankTransferEmp]
(
	@landscape VARCHAR(10)
	,@companycode VARCHAR(10)
	,@paygroup VARCHAR(10)
	,@payperiod VARCHAR(10)
	,@emp_id VARCHAR(10)
	,@trans_date VARCHAR(20)
	,@bank_id VARCHAR(10)
	,@payment_date VARCHAR(20)
)
AS

--DECLARE @landscape VARCHAR(10) = '100'
--DECLARE @companycode VARCHAR(10) = ''
--DECLARE @paygroup VARCHAR(10) = '01'
--DECLARE @payperiod VARCHAR(10) = '202401'
--DECLARE @emp_id VARCHAR(10) = ''
--DECLARE @trans_date VARCHAR(20) = '20250101'
--DECLARE @bank_id VARCHAR(10) = '01'
--DECLARE @payment_date VARCHAR(20) = ''


DECLARE @paymonth VARCHAR(10) = RIGHT(@payperiod, 2)
DECLARE @payyear VARCHAR(10) = LEFT(@payperiod, 4)
DECLARE @enddate VARCHAR(10) = CONVERT(NVARCHAR(8), DATEADD(d, -1,
                                                            DATEADD(m, 1,
                                                              ( @payperiod
                                                              + '01' ))), 112)

DECLARE @t_emp TABLE
    (
      landscape VARCHAR(10) ,
      emp_id VARCHAR(20) ,
      emp_name VARCHAR(250) ,
      bank_acc VARCHAR(50) ,
      bank_acc_name VARCHAR(250) ,
      bank_acc_non VARCHAR(50) ,
      bank_id VARCHAR(20) ,
      bank_name VARCHAR(250) ,
      bank_non VARCHAR(20) ,
      bank_non_name VARCHAR(250) ,
      emp_office VARCHAR(20) ,
      size VARCHAR(50) ,
      type VARCHAR(50) ,
      branch_existing VARCHAR(50),
	  job_level VARCHAR (MAX),
	  job_title VARCHAR (MAX),
	  org_group VARCHAR (MAX),
	  org_group_name VARCHAR (MAX)
    )	

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)
    )

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 DECIMAL(18, 0) ,
      rate DECIMAL(18, 0) ,
      flg_retro VARCHAR(3)
    )
	 
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)
    )

DECLARE @t_result TABLE
    (
      branch VARCHAR(50) ,
	  employeeid VARCHAR (50),
	  employeename VARCHAR(250) ,
	  paymentdate VARCHAR (50),
      trans_date VARCHAR(50) ,
      bank_acc VARCHAR(50) ,
      amount  VARCHAR(50) ,
      flag VARCHAR(50) ,
      ket VARCHAR(250) ,
      sal VARCHAR(50),
      size VARCHAR(50),
      type VARCHAR(50),
      branch_exsting VARCHAR(50),
	  job_level VARCHAR (MAX),
	  job_title VARCHAR (MAX),
	  org_group VARCHAR (MAX),
	  org_group_name VARCHAR (MAX)
    )

INSERT  INTO @t_emp
        SELECT DISTINCT
                tr300.landscape ,
                md1.emp_id ,
                md1.full_name ,
                md3.bank_account ,
                md3.bank_account_name ,
                md3.bank_account_non ,
                md3.bank_id ,
                bank.description AS bank_desc ,
                md3.bank_id_non ,
                banknon.description AS bank_non_desc ,
                md2.employee_office,
                office.size,
                office.type,
                office.branch_existing,
				joblevel.description,
				om.description,
				oo.org_group,
				org_group.description
        FROM    dbo.hr_tr0300_new AS tr300
                LEFT JOIN dbo.hr_md_emp_md0001 AS md1 ON md1.landscape = tr300.landscape
                                                         AND md1.emp_id = tr300.employee_id
                                                         AND md1.start_date <= @enddate
                                                         AND md1.end_date >= @enddate
                LEFT JOIN dbo.hr_md_emp_md0003 AS md3 ON md3.landscape = tr300.landscape
                                                         AND md3.emp_id = tr300.employee_id
                                                         AND md3.start_date <= @enddate
                                                         AND md3.end_date >= @enddate
                LEFT JOIN dbo.hr_md_emp_md0002 AS md2 ON md2.landscape = tr300.landscape
                                                         AND md2.emp_id = tr300.employee_id
                                                         AND md2.start_date <= @enddate
                                                         AND md2.end_date >= @enddate
				LEFT JOIN dbo.hr_md_emp_md0002 AS pos ON pos.emp_id=tr300.employee_id
														 --AND pos.end_date = '99991231'
														 AND pos.start_date <= @enddate
                                                         AND pos.end_date >= @enddate
				LEFT join hr_md_orm_object om on pos.position=om.object  
														 AND om.class='P'
				LEFT join hr_md_orm_object org on pos.organization=org.object  
														 AND org.class='O'
				LEFT JOIN hr_md_orm_o_o oo on  org.object= oo.object
				LEFT JOIN hr_md_orm_object obj on pos.job=obj.object
														AND obj.class='J'
				LEFT JOIN hr_md_orm_o_j oj on obj.object=oj.object
				LEFT JOIN base_cust_ref_joblevel joblevel on oj.job_level=joblevel.joblevel
                LEFT JOIN dbo.base_cust_ref_bankid AS bank ON bank.landscape = md3.landscape
                                                              AND bank.code = md3.bank_id
                LEFT JOIN dbo.base_cust_ref_bankid AS banknon ON banknon.landscape = md3.landscape
                                                              AND banknon.code = md3.bank_id_non
                LEFT JOIN base_cust_ref_emp_office AS office ON md2.landscape = office.landscape
                                                              AND md2.employee_office = office.emp_subarea
				LEFT JOIN base_cust_ref_OrgGroup AS org_group on oo.org_group=org_group.code 
        WHERE   tr300.landscape = @landscape
                AND ( tr300.company_id = @companycode
                      OR @companycode = ''
                    )
                AND ( tr300.payroll_group = @paygroup
                      OR @paygroup = ''
                    )
                AND ( tr300.employee_id = @emp_id
                      OR @emp_id = ''
                    )
                AND tr300.run_period_month = tr300.pay_period_month
                AND tr300.run_period_year = tr300.pay_period_year
                AND tr300.run_period_month = @paymonth
                AND tr300.run_period_year = @payyear
                AND ( md3.bank_id = @bank_id
                      OR @bank_id = ''
                    )

					--select * from @t_emp
	
--//-- Insert data payroll original
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'
        FROM    hr_tr0301_new pr WITH ( NOLOCK )
                INNER JOIN @t_emp emp ON pr.employee_id = emp.emp_id
        WHERE   pr.run_period_year = @payyear
                AND pr.run_period_month = @paymonth
                AND pr.pay_period_year = @payyear
                AND pr.pay_period_month = @paymonth
                AND ( pr.employee_id = @emp_id
                      OR @emp_id = ''
                    )
                AND ( pr.wage_type = 'THP' )
--SELECT * from @tbl_tr_enc

--//-- Insert data payroll retro
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'
        FROM    hr_tr0301_retro_new pr WITH ( NOLOCK )
                INNER JOIN @t_emp emp ON pr.employee_id = emp.emp_id
        WHERE   pr.run_period_year = @payyear
                AND pr.run_period_month = @paymonth
                AND pr.pay_period_year = @payyear
                AND pr.pay_period_month = @paymonth
                AND ( pr.employee_id = @emp_id
                      OR @emp_id = ''
                    )
                AND ( pr.wage_type = 'THP' )

--//-- Decrypt data
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
        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
        FROM    @tbl_tr_dec AS tbl_tr_dec
        GROUP BY tbl_tr_dec.landscape ,
                employee_id ,
                payPeriodMonth ,
                payPeriodYear ,
                runPeriodMonth ,
                runPeriodYear ,
                wage_type

INSERT  @t_result
        SELECT DISTINCT
                '' ,
				'Employee ID',
				'Employee Name',
				'Payment Date',
                'Source Date' ,
                'Norek Kredit' ,
                'Amount',
                'Db Cr' ,
                'Keterangan' ,
                'Refferensi',
                '',
                '',
                'Source Branch',
				'Job Level',
				'Job Title',
				'Org Group',
				'Org Group Name'
        FROM    @t_emp AS temp
                LEFT JOIN @tbl_tr_dec_sum AS decsum ON temp.emp_id = decsum.employee_id

INSERT  @t_result
        SELECT DISTINCT
                'TOTAL' ,
				'TOTAL',
				'',
				'',
                '' ,
                CONVERT(VARCHAR(50),COUNT(temp.emp_id)) ,
                CONVERT(VARCHAR(50),SUM(decsum.amount)),
                '' ,
                '' ,
                '',
                '',
                '',
                'TOTAL',
				'',
				'',
				'',
				''
        FROM    @t_emp AS temp
                LEFT JOIN @tbl_tr_dec_sum AS decsum ON temp.emp_id = decsum.employee_id



INSERT  @t_result
        SELECT DISTINCT
                RIGHT(temp.emp_office, 3) AS branch ,
				temp.emp_id,
				temp.emp_name,
                case when @payment_date='' then '' else dbo.fn_formatdatetime_indonesia(@payment_date, 'dd mmmm yyyy') end AS trans_date ,
				dbo.fn_formatdatetime_indonesia(@trans_date, 'dd mmmm yyyy') AS trans_date ,
                temp.bank_acc ,
                CONVERT(VARCHAR(50),decsum.amount) ,
                'C' ,
                'SAL' + dbo.fn_formatdatetime_indonesia(@enddate, 'mmyy') AS ket ,
                'SAL',
				temp.size,
                temp.type,
                temp.branch_existing,
				temp.job_level,
				temp.job_title,
				temp.org_group,
				temp.org_group_name
        FROM    @t_emp AS temp
                LEFT JOIN @tbl_tr_dec_sum AS decsum ON temp.emp_id = decsum.employee_id

SELECT  *
FROM    @t_result
		 
--SELECT DISTINCT dbo.fn_formatdatetime_indonesia(@trans_date, 'ddmmyy') AS trans_date
--				,temp.*
--				,RIGHT(temp.emp_office,3) AS branch
--				,'SAL' + dbo.fn_formatdatetime_indonesia(@enddate, 'mmyy') AS ket
--				,decsum.amount
--				,decsum.rate
--FROM @t_emp AS temp
--LEFT JOIN @tbl_tr_dec_sum AS decsum
--	ON temp.emp_id = decsum.employee_id