BANK FAMA

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