CLIENT SUPPORT » BBG Bugs

DECLARE @now VARCHAR(8) 

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

DECLARE @maskerja AS TABLE (mskerja INT)

INSERT INTO @maskerja VALUES (5),(10),(15),(20),(25),(30),(35),(40),(45),(50),(55),(60)	

DECLARE @empHiring AS TABLE

    (

      EmployeeID VARCHAR(MAX) ,

      HiringDate VARCHAR(MAX),

	  [Year] VARCHAR(MAX),

	  [Month] VARCHAR(MAX)

	  --[day] VARCHAR(MAX)

    )

INSERT  INTO @empHiring

       SELECT EmployeeID,

	    HiringDate,

        DATEDIFF(year, HiringDate, GETDATE()) as tahun,

        DATEDIFF(month, HiringDate, GETDATE()) % 12 as bulan

        --DATEDIFF(day, HiringDate, GETDATE()) % 365 % 30 as hari

    FROM PHRPA0015

Declare @FilterHiring  AS TABLE

    (

	 StartDate VARCHAR(MAX) ,

      EndDate VARCHAR(MAX),

      EmployeeID VARCHAR(MAX) ,

      HiringDate VARCHAR(MAX),

	  [Year] VARCHAR(MAX),

	  [Month] VARCHAR(MAX)

    )

INSERT  INTO @FilterHiring

SELECT CONVERT(VARCHAR,YEAR(GETDATE()))+CONVERT(VARCHAR,SUBSTRING(@now,5,4)) StartDate ,

		CONVERT(VARCHAR,YEAR(GETDATE()) +5)+CONVERT(VARCHAR,SUBSTRING(@now,5,2))+ SUBSTRING(CONVERT(VARCHAR(MAX), CONVERT(DATE, DATEADD(DAY, -1, @now))),9,2) AS EndDate , * from @empHiring where  Month = '0' and SUBSTRING(HiringDate, 7, 2) = SUBSTRING(@now, 7, 2) and Year in (select * from @maskerja )

INSERT  INTO PHRPA0017

SELECT DISTINCT  StartDate AS StartDate ,

        EndDate AS EndDate ,

        EmployeeID ,

        '1111' AS AbsenceQuotaType ,

        '1' AS [Sequence] ,

        StartDate AS StartDeduction ,

        EndDate AS EndDeduction ,

        '5' AS OriginalQuota ,

        '5' AS RemainQuota ,

        '0' AS PreviousQuota ,

        EndDate AS ExpiredQuota ,

        'Generate Leave Quota Cuti Masa Kerja ' AS Notes,

		'' AS Createby,

		'' AS CreateDate,

		'' AS ChangeBy,

		'' AS ChangeDate

FROM    @FilterHiring