ALTER PROCEDURE [dbo].[rp_trPS00] -- exec rp_trPS00 '100', '201011', '00000324', '0', '', 'scriberion', 'triadblank'
	-- Add the parameters for the stored procedure here
	@landscape VARCHAR(3),
	@payperiod VARCHAR(6),
	@employee_id VARCHAR(8),
	@running VARCHAR(2),
	@paytype VARCHAR(2),
	@key1 AS NVARCHAR(max),
	@key2 AS NVARCHAR(max)
WITH ENCRYPTION
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--DECLARE @landscape VARCHAR(3) = '100'
	--DECLARE @payperiod VARCHAR(6) = '202405'
	--DECLARE @employee_id VARCHAR(8) = '20130636'
	--DECLARE @running VARCHAR(2) = '1'
	--DECLARE @paytype VARCHAR(2) = ''
	--DECLARE @key1 AS NVARCHAR(max) = 'scriberion'
	--DECLARE @key2 AS NVARCHAR(max) = 'triadblank'

    -- Insert statements for procedure here
	DECLARE @companycode AS VARCHAR(50)
	DECLARE @companyname AS NVARCHAR(50)
	DECLARE @companylogo AS VARBINARY(MAX)
	DECLARE @paymonth AS NVARCHAR(2)
	DECLARE @payyear AS NVARCHAR(4)
	DECLARE @begda AS NVARCHAR(8)
	DECLARE @endda AS NVARCHAR(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 @payslipsigned AS NVARCHAR(50)

	SELECT TOP(1) @payslipsigned = [value]
	FROM hr_cu0307
	WHERE  (landscape = @landscape) AND (pay_param = 'PAYSLIPSIGNED') AND
	(start_date <= @endda AND end_date >= @endda)

	IF (@running = '1')
	BEGIN
		SELECT TOP(1) @companycode = company_id
		FROM hr_tr0300
		WHERE  (landscape = @landscape) AND (employee_id = @employee_id) AND
			   (pay_period_month = @paymonth) AND (pay_period_year = @payyear) AND
			   (run_period_month = @paymonth) AND (run_period_year = @payyear)
	END
	ELSE
	BEGIN
		SELECT TOP(1) @companycode = company_id
		FROM hr_tr0300_sim
		WHERE  (landscape = @landscape) AND (employee_id = @employee_id) AND
			   (pay_period_month = @paymonth) AND (pay_period_year = @payyear) AND
			   (run_period_month = @paymonth) AND (run_period_year = @payyear)
	END

	SELECT @companyname = description, @companylogo = logo
	FROM    base_cust_ref_companycode
	WHERE  (landscape = @landscape) AND (companycode= @companycode)

	IF (@running = '1')
	BEGIN   

		DECLARE @TableCA TABLE
		(
			employee_id VARCHAR(20)
			--,wage_type VARCHAR(20)
			--,rate DECIMAL(22,0)
			,amount DECIMAL(22,0)
			,retrotype VARCHAR(20)
		)

		INSERT INTO @TableCA
		SELECT DISTINCT sumca.employee_id, SUM(DISTINCT(sumca.amount)) AS amount, MIN(sumca.retrotype) FROM
		( SELECT DISTINCT ca.employee_id, ca.wage_type,  CAST(dbo.GetPEN(@landscape, ca.rate) AS DECIMAL) 'rate' , CAST(dbo.GetPEN(@landscape, ca.amount) AS DECIMAL) 'amount', ca.retrotype
		FROM (
		SELECT * ,
            'N' retrotype
        FROM   hr_tr0301 WHERE hr_tr0301.wage_type = '3000'
							AND hr_tr0301.employee_id = @employee_id
							AND hr_tr0301.pay_period_month = @paymonth
							AND hr_tr0301.pay_period_year = @payyear
							AND hr_tr0301.run_period_month = @paymonth
							AND hr_tr0301.run_period_year = @payyear
        UNION ALL
        SELECT * ,
            'R' retrotype
        FROM   hr_tr0301_retro WHERE hr_tr0301_retro.wage_type = '3000'
							AND hr_tr0301_retro.employee_id = @employee_id
							AND hr_tr0301_retro.pay_period_month = @paymonth
							AND hr_tr0301_retro.pay_period_year = @payyear
							AND hr_tr0301_retro.run_period_month = @paymonth
							AND hr_tr0301_retro.run_period_year = @payyear
		) ca ) sumca
		GROUP BY sumca.employee_id

		SELECT DISTINCT @payslipsigned payslipsigned, c308.landscape, @companyname companyname, @companylogo logo, c308.reportid, c308.groupby, c308P.description parentdescription, c308.sequence, c308.description, c308.paytype, c308.wage_type, c308.isdisplaywage_type, c308.isdisplaynumber, c308.isdisplayrate, c308.isdisplayamount, 
			  c308.start_date, c308.end_date, c308.isactive, c308.change_by, c308.change_date, c308.created_by, c308.created_date, hr_tr0301.employee_id, 
			  hr_tr0301.pay_period_month, hr_tr0301.pay_period_year, hr_tr0301.run_period_month, hr_tr0301.run_period_year, hr_tr0301.split_indicator, 
			  --hr_tr0301.rate, hr_tr0301.amount, 
			  CAST(dbo.GetPEN(@landscape, hr_tr0301.rate) AS DECIMAL) 'rate',
			  CASE WHEN c308.paytype = 'T' THEN ((ISNULL(CONVERT(INT, c308.[percent]),100) / 100) * CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL)) + ISNULL(tblca.amount,0)
			  ELSE ((ISNULL(CONVERT(INT, c308.[percent]),100) / 100) * CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL))
			  END AS 'amount',
			  hr_tr0301.number, hr_tr0301.currency, hr_tr0301.flag, md001.full_name, md003.tax_status, tax_stat.description tax_status_desc,
			  md001.marital_status, ms.description maritaldescription, md003.pay_industry, md003.pay_area, md003.pay_class, 
			  md003.pay_grade, grd.description AS grd_desc, md002.job, md002.employee_area, area.description areaname, md002.organization, org.description orgname, md002.position, pos.description posname
			  ,md002.employee_office, office.emp_subarea_description AS emp_office_desc
		FROM         hr_cu0308 AS c308 
		LEFT JOIN hr_cu0308 AS c308P ON  c308.landscape = c308P.landscape AND c308.groupby = c308P.reportid 
		INNER JOIN
		(SELECT *, 'N' retrotype
		 FROM hr_tr0301
		 UNION ALL
		 SELECT *, 'R' retrotype
		 FROM hr_tr0301_retro
		 )hr_tr0301 ON c308.landscape = hr_tr0301.landscape AND c308.wage_type = hr_tr0301.wage_type AND c308.retrotype = hr_tr0301.retrotype
			AND (c308.paytype = @paytype OR @paytype = '')
		INNER JOIN
		(
			SELECT m1.* 
			FROM  hr_md_emp_md0001 m1
			WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
		)md001 ON md001.landscape = hr_tr0301.landscape AND md001.emp_id = hr_tr0301.employee_id
		LEFT JOIN @TableCA AS tblca ON hr_tr0301.employee_id = tblca.employee_id AND c308.retrotype = tblca.retrotype
		LEFT JOIN 
		(
			SELECT m1.* 
			FROM  hr_md_emp_md0003 m1
			WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
		)md003 ON md001.landscape = md003.landscape AND md001.emp_id = md003.emp_id
		LEFT JOIN
		(
			SELECT m1.* 
			FROM  hr_md_emp_md0002 m1
			WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
		)md002 ON hr_tr0301.landscape = md002.landscape AND hr_tr0301.employee_id = md002.emp_id 
		LEFT JOIN base_cust_ref_emp_area area ON md002.landscape = area.landscape and md002.employee_area = area.emp_area
		LEFT JOIN base_cust_ref_emp_office office ON office.landscape = md002.landscape AND md002.employee_office = office.emp_subarea
		LEFT JOIN
		(
			SELECT m1.* 
			FROM  hr_md_orm_object m1
			WHERE class = 'O' AND (start_date <= @endda AND end_date >= @endda)
		) org ON md002.landscape = org.landscape and md002.organization = org.object
		LEFT JOIN
		(
			SELECT m1.* 
			FROM  hr_md_orm_object m1
			WHERE class = 'P' AND (start_date <= @endda AND end_date >= @endda)
		) pos ON md002.landscape = pos.landscape and md002.position = pos.object
		LEFT JOIN base_cust_ref_pay_grade grd ON md003.landscape = grd.landscape AND md003.pay_grade = grd.pay_grade
		LEFT JOIN base_cust_ref_marital_status ms ON md001.landscape = ms.landscape and md001.marital_status = ms.code
		LEFT JOIN hr_cu0302 tax_stat ON tax_stat.landscape = md003.landscape AND md003.tax_status = tax_stat.status
								AND tax_stat.start_date <= @endda AND tax_stat.end_date >= @endda
		WHERE c308.landscape = @landscape AND hr_tr0301.employee_id = @employee_id AND 
			  hr_tr0301.pay_period_month = @paymonth AND hr_tr0301.pay_period_year = @payyear AND 
			  hr_tr0301.run_period_month = @paymonth AND hr_tr0301.run_period_year = @payyear
			  --AND hr_tr0301.amount <> 0
			  AND CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL) <> 0
			  AND dbo.CekObjectKey(@landscape, @key1, @key2, 'M!N0V@2010') > 0

		ORDER BY c308.groupby, c308.wage_type
	END
	ELSE
	BEGIN

		SELECT DISTINCT @payslipsigned payslipsigned, c308.landscape, @companyname companyname, @companylogo logo, c308.reportid, c308.groupby, c308P.description parentdescription, c308.sequence, c308.description, c308.paytype, c308.wage_type, c308.isdisplaywage_type, c308.isdisplaynumber, c308.isdisplayrate, c308.isdisplayamount, 
			  c308.start_date, c308.end_date, c308.isactive, c308.change_by, c308.change_date, c308.created_by, c308.created_date, hr_tr0301_sim.employee_id, 
			  hr_tr0301_sim.pay_period_month, hr_tr0301_sim.pay_period_year, hr_tr0301_sim.run_period_month, hr_tr0301_sim.run_period_year, hr_tr0301_sim.split_indicator, 
			  --hr_tr0301_sim.rate, hr_tr0301_sim.amount, 
			  CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.rate) AS DECIMAL) 'rate',
			  CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.amount) AS DECIMAL) 'amount',
			  hr_tr0301_sim.number, hr_tr0301_sim.currency, hr_tr0301_sim.flag, md001.full_name, md003.tax_status, tax_stat.description tax_status_desc,
			  md001.marital_status, ms.description maritaldescription, md003.pay_industry, md003.pay_area, md003.pay_class, 
			  md003.pay_grade, grd.description AS grd_desc, md002.job, md002.employee_area, area.description areaname, md002.organization, org.description orgname, md002.position, pos.description posname
			  ,md002.employee_office, office.emp_subarea_description AS emp_office_desc
		FROM         hr_cu0308 AS c308 
		LEFT JOIN hr_cu0308 AS c308P ON  c308.landscape = c308P.landscape AND c308.groupby = c308P.reportid 
		INNER JOIN 
		(SELECT *, 'N' retrotype
		 FROM hr_tr0301_sim
		 UNION ALL
		 SELECT *, 'R' retrotype
		 FROM hr_tr0301_retro_sim
		 )hr_tr0301_sim ON c308.landscape = hr_tr0301_sim.landscape AND c308.wage_type = hr_tr0301_sim.wage_type AND c308.retrotype = hr_tr0301_sim.retrotype
			AND (c308.paytype = @paytype OR @paytype = '')
		INNER JOIN
		(
			SELECT m1.* 
			FROM  hr_md_emp_md0001 m1
			WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
		)md001 ON md001.landscape = hr_tr0301_sim.landscape AND md001.emp_id = hr_tr0301_sim.employee_id
		LEFT JOIN 
		(
			SELECT m1.* 
			FROM  hr_md_emp_md0003 m1
			WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
		)md003 ON md001.landscape = md003.landscape AND md001.emp_id = md003.emp_id
		LEFT JOIN
		(
			SELECT m1.* 
			FROM  hr_md_emp_md0002 m1
			WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
		)md002 ON hr_tr0301_sim.landscape = md002.landscape AND hr_tr0301_sim.employee_id = md002.emp_id 
		LEFT JOIN base_cust_ref_emp_area area ON md002.landscape = area.landscape and md002.employee_area = area.emp_area
		LEFT JOIN base_cust_ref_emp_office office ON office.landscape = md002.landscape AND md002.employee_office = office.emp_subarea
		LEFT JOIN
		(
			SELECT m1.* 
			FROM  hr_md_orm_object m1
			WHERE class = 'O' AND (start_date <= @endda AND end_date >= @endda)
		) org ON md002.landscape = org.landscape and md002.organization = org.object
		LEFT JOIN
		(
			SELECT m1.* 
			FROM  hr_md_orm_object m1
			WHERE class = 'P' AND (start_date <= @endda AND end_date >= @endda)
		) pos ON md002.landscape = pos.landscape and md002.position = pos.object
		LEFT JOIN base_cust_ref_pay_grade grd ON md003.landscape = grd.landscape AND md003.pay_grade = grd.pay_grade
		LEFT JOIN base_cust_ref_marital_status ms ON md001.landscape = ms.landscape and md001.marital_status = ms.code
		LEFT JOIN hr_cu0302 tax_stat ON tax_stat.landscape = md003.landscape AND md003.tax_status = tax_stat.status
								AND tax_stat.start_date <= @endda AND tax_stat.end_date >= @endda
		WHERE c308.landscape = @landscape AND hr_tr0301_sim.employee_id = @employee_id AND 
			  hr_tr0301_sim.pay_period_month = @paymonth AND hr_tr0301_sim.pay_period_year = @payyear AND 
			  hr_tr0301_sim.run_period_month = @paymonth AND hr_tr0301_sim.run_period_year = @payyear
			  --AND hr_tr0301_sim.amount <> 0
			  AND CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.amount) AS DECIMAL) <> 0
			  AND dbo.CekObjectKey(@landscape, @key1, @key2, 'M!N0V@2010') > 0

		ORDER BY c308.groupby, c308.wage_type
	END
END
GO