DECLARE @now VARCHAR(8)
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

								
DECLARE @t_emp TABLE
(
Rowse INT,
    EmployeeID VARCHAR(80),
    ExternalID VARCHAR(80),
    FullName VARCHAR(MAX),
    JoinDate VARCHAR(80),
    Level VARCHAR(MAX),
    Division VARCHAR(MAX),
    LOB VARCHAR(MAX),	
    LOBc VARCHAR(MAX),
	Divisionc VARCHAR(MAX),
	EmpSubType VARCHAR(MAX),
	Levelc VARCHAR(MAX)

)



DECLARE @t_emp_ABS TABLE
(
    EmployeeID VARCHAR(80),
    ExternalID VARCHAR(80),
    FullName VARCHAR(MAX),
    JoinDate VARCHAR(80),
    Level VARCHAR(MAX),
    Division VARCHAR(MAX),
    LOB VARCHAR(MAX),	
    LOBc VARCHAR(MAX),
	Divisionc VARCHAR(MAX),
	EmpSubType VARCHAR(MAX),
	Levelc VARCHAR(MAX),
	AbsenceType VARCHAR(MAX),
	AbstypeDesc VARCHAR(MAX),
	AbsQuota VARCHAR(MAX),
	QuotaDeduction VARCHAR(MAX)
)


DECLARE @t_emp_ABS_rows TABLE
(
	Rowse INT,
	notes VARCHAR(80),
    EmployeeID VARCHAR(80),
    ExternalID VARCHAR(80),
    FullName VARCHAR(MAX),
    JoinDate VARCHAR(80),
    Level VARCHAR(MAX),
    Division VARCHAR(MAX),
    LOB VARCHAR(MAX),	
    LOBc VARCHAR(MAX),
	Divisionc VARCHAR(MAX),
	EmpSubType VARCHAR(MAX),
	Levelc VARCHAR(MAX),
	AbsenceType VARCHAR(MAX),
	AbstypeDesc VARCHAR(MAX),
	AbsQuota VARCHAR(MAX),
	QuotaDeduction VARCHAR(MAX)
)













INSERT INTO @t_emp
SELECT DISTINCT 
ROW_NUMBER() OVER(ORDER BY pa2.EmployeeID ASC) AS rw,
       pa2.EmployeeID,
       pa1.ExternalID,
       pa1.FullName,
       dbo.fn_formatdatetime(pa15.HiringDate, 'dd-mmm-yyyy') AS JoinDate,
       pyg.PayrollGradeDescription AS LEVEL,
	   coalesce(div.DivDescription,'-') Division,
       lob.LobDescription LOB,
	   pa2.LOB as LOBc,
	   pa2.Division Divisionc,
	   pa2.EmployeeSubType,
	   pa3.payrollgrade Levelc
FROM dbo.PHRPA0001 pa1
    LEFT JOIN dbo.PHRPA0002 pa2
        ON pa2.EmployeeID = pa1.EmployeeID
           AND pa2.StartDate <= @now
           AND pa2.EndDate >= @now

    LEFT JOIN PHRPA0015 pa15
        ON pa1.EmployeeID = pa15.EmployeeID
   
    LEFT JOIN dbo.PHRPA0003 pa3
        ON pa1.EmployeeID = pa3.EmployeeID
           AND pa3.StartDate <= @now
           AND pa3.EndDate >= @now
    LEFT JOIN CHRPACOMBODIV div
        ON pa2.Division = div.Div
    LEFT JOIN CHRPACOMBOLOB lob
        ON pa2.LOB = lob.Lob
    LEFT JOIN PHRPYPGRAD pyg
        ON pa3.PayrollGrade = pyg.PayrollGrade

		ORDER BY EmployeeID AsC
								
	DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= (SELECT MAX(Rowse) FROM @t_emp))
BEGIN
    


	INSERT INTO @t_emp_ABS

	SELECT  (SELECT EmployeeID FROM @t_emp Where Rowse=@Counter),
	(SELECT ExternalID FROM @t_emp Where Rowse=@Counter),
	(SELECT FullName FROM @t_emp Where Rowse=@Counter),
	(SELECT JoinDate FROM @t_emp Where Rowse=@Counter),
	(SELECT Level FROM @t_emp Where Rowse=@Counter),
	(SELECT Division FROM @t_emp Where Rowse=@Counter),
	(SELECT LOB FROM @t_emp Where Rowse=@Counter),
	(SELECT LOBc FROM @t_emp Where Rowse=@Counter),
	(SELECT Divisionc FROM @t_emp Where Rowse=@Counter),
	(SELECT EmpSubType FROM @t_emp Where Rowse=@Counter),
	(SELECT Levelc FROM @t_emp Where Rowse=@Counter),
	tabsence.AbsenceType ,  
                                             tabsence.AbsenceTypeDesc ,  
                                             tquota.AbsenceQuotaType ,  
                                             tabsence.QuotaDeduction  
                                             FROM    dbo.PHRTMABSTYP AS tabsence  
                                             LEFT JOIN dbo.PHRTMQUOTYP AS tquota ON tquota.AbsenceQuotaType = tabsence.AbsenceQuotaType  
                                             WHERE   tabsence.QuotaDeduction = 'N'  
                                             UNION  
                                             SELECT  (SELECT EmployeeID FROM @t_emp Where Rowse=@Counter),
	(SELECT ExternalID FROM @t_emp Where Rowse=@Counter),
	(SELECT FullName FROM @t_emp Where Rowse=@Counter),
	(SELECT JoinDate FROM @t_emp Where Rowse=@Counter),
	(SELECT Level FROM @t_emp Where Rowse=@Counter),
	(SELECT Division FROM @t_emp Where Rowse=@Counter),
	(SELECT LOB FROM @t_emp Where Rowse=@Counter),
	(SELECT LOBc FROM @t_emp Where Rowse=@Counter),
	(SELECT Divisionc FROM @t_emp Where Rowse=@Counter),
	(SELECT EmpSubType FROM @t_emp Where Rowse=@Counter),
	(SELECT Levelc FROM @t_emp Where Rowse=@Counter),
  tabsence.AbsenceType ,  
                                             tabsence.AbsenceTypeDesc ,  
                                             tquota.AbsenceQuotaType ,  
                                             tabsence.QuotaDeduction  
                                             FROM    dbo.PHRTMABSTYP AS tabsence  
                                             LEFT JOIN dbo.PHRTMQUOTYP AS tquota ON tquota.AbsenceQuotaType = tabsence.AbsenceQuotaType  
                                             RIGHT JOIN dbo.PHRPA0017 AS md17 ON md17.AbsenceQuotaType = tabsence.AbsenceQuotaType  
                                             AND md17.EmployeeID = (SELECT EmployeeID FROM @t_emp Where Rowse=@Counter)
                                             AND md17.StartDeduction <= @now
                                             AND md17.EndDeduction >= @now
											     SET @Counter  = @Counter  + 1

END


 
 INSERT INTO @t_emp_ABS_rows

SELECT ROW_NUMBER() OVER(ORDER BY tempabs.EmployeeID ASC) AS rw,'' as notes,* FROM @t_emp_ABS  tempabs

	DECLARE @Counter2 INT 
SET @Counter2=1
WHILE ( @Counter2 <= (SELECT MAX(Rowse) FROM @t_emp_ABS_rows))
BEGIN
IF 
(SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpLvStart =(SELECT Levelc FROM @t_emp_ABS_rows Where Rowse=@Counter2)) IS  NULL  OR (SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpLvStart =(SELECT Levelc FROM @t_emp_ABS_rows Where Rowse=@Counter2)) < 0 OR (SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpLvStart =(SELECT Levelc FROM @t_emp_ABS_rows Where Rowse=@Counter2)) =''
BEGIN 
UPDATE @t_emp_ABS_rows 
SET notes = 'Tidak Ada Tipe Cuti ' + (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) + ' Untuk grade ' + (SELECT Levelc FROM @t_emp_ABS_rows Where Rowse=@Counter2)
WHERE Rowse = @Counter2
END
IF 


(SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpSubType =(SELECT EmpSubType FROM @t_emp_ABS_rows Where Rowse=@Counter2)) IS  NULL OR (SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpSubType =(SELECT EmpSubType FROM @t_emp_ABS_rows Where Rowse=@Counter2)) < 0 OR (SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpSubType =(SELECT EmpSubType FROM @t_emp_ABS_rows Where Rowse=@Counter2)) =''

BEGIN 


UPDATE @t_emp_ABS_rows 

SET notes = 'Tidak Ada Tipe Cuti ' + (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) + ' Untuk EmployeeSubType' + (SELECT EmpSubType FROM @t_emp_ABS_rows Where Rowse=@Counter2)


WHERE Rowse = @Counter2

END 
SET @Counter2  = @Counter2  + 1
END

	
SELECT * FROM @t_emp_ABS_rows	WHERE notes <>''