USE [MinovaES_BankFama_Dev]
GO
/****** Object:  StoredProcedure [dbo].[PDSPYWAGETYPEAUTH]    Script Date: 07/01/2022 10.57.51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PDSPYWAGETYPEAUTH]
(
	 @UserID VARCHAR(50)
	 ,@PayrollType VARCHAR(50)
	 ,@PayrollArea VARCHAR(50)
	 ,@PayrollClass VARCHAR(50)
	 ,@PayrollGrade VARCHAR(50)
)
AS

--DECLARE @UserID VARCHAR(50) = 'ilvia'
--DECLARE @PayrollType VARCHAR(50) = '*'
--DECLARE @PayrollArea VARCHAR(50) = '*'
--DECLARE @PayrollClass VARCHAR(50) = '*'
--DECLARE @PayrollGrade VARCHAR(50) = '*'

DECLARE @CompanyIDAuth VARCHAR(50) 
SELECT DISTINCT @CompanyIDAuth = b.Value 
FROM SUSERPROFILE AS a
LEFT JOIN PCUSTPROFILE AS b
	ON a.ProfileId = b.ProfileID
where a.UserName = @UserID AND b.FieldName = 'CompanyID'


DECLARE @TResult TABLE
(
	TableName VARCHAR(200)
	,WageType VARCHAR(200)
	,WageTypeDesc VARCHAR(200)
	,Valuation VARCHAR(20)
	,Overwrite VARCHAR(20)
	,Flag VARCHAR(20)
	,IsActive VARCHAR(20)
	,WTAuthorizationGroup VARCHAR(200)
	,Amount DECIMAL(22,0)
	,Currency VARCHAR(30)
)

INSERT INTO @TResult
SELECT  *  
FROM    ( SELECT  cs.TableName , 
                   c300.WageTypeDefinition as WageType ,  
                   c300.WTDefinitionDesc as WageTypeDesc ,  
                   c300.Valuation,  
                   c300.Valuation as Flag, 
                   c300.Overwrite, 
                   c300.IsActive,  
                   c300.WTAuthorizationGroup,  
                   CASE WHEN ( c300.Valuation = 'I1' )  
                         THEN ( SELECT  Amount FROM PHRPYCU0303 WITH ( NOLOCK ) WHERE WageType = c300.WageTypeDefinition AND StartDate <= '20210101' AND EndDate >= '20210101' )  
                         WHEN ( c300.Valuation = 'I2' )  
                         THEN ( SELECT  Amount FROM PHRPYCU0304 WITH ( NOLOCK ) WHERE WageType = c300.WageTypeDefinition AND StartDate <= '20210101' AND EndDate >= '20210101'  
                               AND (PayrollType = @PayrollType OR PayrollType = '*')  
	                            AND (PayrollArea = @PayrollArea OR PayrollArea = '*')  
	                            AND (PayrollClass = @PayrollClass OR PayrollClass = '*')  
	                            AND (PayrollGrade = @PayrollGrade OR PayrollGrade = '*') )  
                         WHEN ( c300.Valuation = 'I3' )  
                         THEN 0  
                   END AS Amount,  
                   CASE WHEN ( c300.Valuation = 'I1' )  
                         THEN ( SELECT  Currency FROM PHRPYCU0303 WITH ( NOLOCK ) WHERE WageType = c300.WageTypeDefinition AND StartDate <= '20210101' AND EndDate >= '20210101' )  
                         WHEN ( c300.Valuation = 'I2' )  
                         THEN ( SELECT  Currency FROM PHRPYCU0304 WITH ( NOLOCK ) WHERE WageType = c300.WageTypeDefinition AND StartDate <= '20210101' AND EndDate >= '20210101'   
                               AND (PayrollType = @PayrollType OR PayrollType = '*')  
	                            AND (PayrollArea = @PayrollArea OR PayrollArea = '*')  
	                            AND (PayrollClass = @PayrollClass OR PayrollClass = '*')  
	                            AND (PayrollGrade = @PayrollGrade OR PayrollGrade = '*') )  
                         WHEN ( c300.Valuation = 'D' )  
                         THEN ( SELECT 'IDR' )  
                    END AS Currency  
            FROM    PCMEPMDCONSUB AS cs WITH ( NOLOCK )  
                    INNER JOIN PHRPYCU0300 AS c300 WITH ( NOLOCK ) ON cs.MdSub = c300.WageTypeDefinition  
                    AND cs.TableName = 'PHRPA0004'  
                    AND c300.StartDate <= '20210101' AND c300.EndDate >= '20210101'  
       AND c300.IsActive='1'AND c300.WTAuthorizationGroup = @CompanyIDAuth) AS tbl 

INSERT INTO @TResult
SELECT  *  
FROM    ( SELECT  cs.TableName , 
                   c300.WageTypeDefinition as WageType ,  
                   c300.WTDefinitionDesc as WageTypeDesc ,  
                   c300.Valuation,  
                   c300.Valuation as Flag, 
                   c300.Overwrite, 
                   c300.IsActive,  
                   c300.WTAuthorizationGroup,  
                   CASE WHEN ( c300.Valuation = 'I1' )  
                         THEN ( SELECT  Amount FROM PHRPYCU0303 WITH ( NOLOCK ) WHERE WageType = c300.WageTypeDefinition AND StartDate <= '20210101' AND EndDate >= '20210101' )  
                         WHEN ( c300.Valuation = 'I2' )  
                         THEN ( SELECT  Amount FROM PHRPYCU0304 WITH ( NOLOCK ) WHERE WageType = c300.WageTypeDefinition AND StartDate <= '20210101' AND EndDate >= '20210101'  
                              AND (PayrollType = @PayrollType OR PayrollType = '*')  
	                            AND (PayrollArea = @PayrollArea OR PayrollArea = '*')  
	                            AND (PayrollClass = @PayrollClass OR PayrollClass = '*')  
	                            AND (PayrollGrade = @PayrollGrade OR PayrollGrade = '*') )  
                         WHEN ( c300.Valuation = 'I3' )  
                         THEN 0  
                   END AS Amount,  
                   CASE WHEN ( c300.Valuation = 'I1' )  
                         THEN ( SELECT  Currency FROM PHRPYCU0303 WITH ( NOLOCK ) WHERE WageType = c300.WageTypeDefinition AND StartDate <= '20210101' AND EndDate >= '20210101' )  
                         WHEN ( c300.Valuation = 'I2' )  
                         THEN ( SELECT  Currency FROM PHRPYCU0304 WITH ( NOLOCK ) WHERE WageType = c300.WageTypeDefinition AND StartDate <= '20210101' AND EndDate >= '20210101'   
                               AND (PayrollType = @PayrollType OR PayrollType = '*')  
	                            AND (PayrollArea = @PayrollArea OR PayrollArea = '*')  
	                            AND (PayrollClass = @PayrollClass OR PayrollClass = '*')  
	                            AND (PayrollGrade = @PayrollGrade OR PayrollGrade = '*') )  
                         WHEN ( c300.Valuation = 'D' )  
                         THEN ( SELECT 'IDR' )  
                    END AS Currency  
            FROM    PCMEPMDCONSUB AS cs WITH ( NOLOCK )  
                    INNER JOIN PHRPYCU0300 AS c300 WITH ( NOLOCK ) ON cs.MdSub = c300.WageTypeDefinition  
                    AND cs.TableName = 'PHRPA0004'  
                    AND c300.StartDate <= '20210101' AND c300.EndDate >= '20210101'  
       AND c300.IsActive='1'AND ISNULL(c300.WTAuthorizationGroup,'') = '') AS tbl 
SELECT * FROM @TResult