Project

General

Profile

Bug #979

Fixing Service Auto Generate LQ (Leave Quota)

Added by Muhammad Bintar almost 3 years ago. Updated almost 3 years ago.

Status:
Closed
Priority:
Normal
Start date:
03/17/2022
Due date:
03/21/2022
% Done:

0%

Estimated time:

Description

Dear tim developer mohon support nya untuk perbaikan service AutoGenerateLQ (Leave Quota) dimana pada saat ini service tersebut men generate quota pada employee yang baru saja hiring tetapi sudah mendapat kuota, padahal seharusnya kuota tersebut bisa terambil jika employee sudah bekerja lebih dari 1 tahun

database MinovaHR_ESS_BankMega_Production

database sql server 2014
192.168.3.105\MSSQLSERVER2014


Files

AutoGenerateLeaveQuotaAfter1YearHiring.txt (2.12 KB) AutoGenerateLeaveQuotaAfter1YearHiring.txt Muhammad Bintar, 03/18/2022 03:30 PM
serviceautogenerateabsencequota.txt (2.76 KB) serviceautogenerateabsencequota.txt revisi versi 2 Muhammad Bintar, 03/25/2022 06:34 PM
#1

Updated by Saswanto Tampan almost 3 years ago

  • Status changed from New to Deploy
  • Assignee changed from Saswanto Tampan to Muhammad Bintar

dibuatkan job sql

#2

Updated by Muhammad Bintar almost 3 years ago

update =
buat SP dengan nama AutoGenerateLeaveQuotaAfter1YearHiring

lalu dibuat job dengan schedule setiap 1 hari sekali di malam hari

#3

Updated by Muhammad Bintar almost 3 years ago

USE [MinovaHR_BANK_MEGA_Production]
GO
/****** Object: StoredProcedure [dbo].[AutoGenerateLeaveQuotaAfter1YearHiring] Script Date: 25/03/2022 17:10:03 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE [dbo].[AutoGenerateLeaveQuotaAfter1YearHiring]
-- Add the parameters for the stored procedure here
--@Landscape varchar(3)
AS
BEGIN
INSERT INTO dbo.hr_md_emp_md0017
SELECT DISTINCT CONVERT, '100') AS landscape ,
md02.emp_id ,
CONVERT))+CONVERT) stdate ,
CONVERT) 1)+CONVERT) SUBSTRING, CONVERT(DATE, DATEADD(DAY, 1, hiring_date))),9,2) AS endate ,
CONVERT))+CONVERT) std,
CONVERT) +1)+CONVERT)+SUBSTRING, CONVERT)),9,2) AS etd ,
CONVERT, '12') AS oriquot,
CONVERT, 'System') AS userchange,
format(getdate(),'yyyyMMddHHmmss') AS laschange,
CONVERT, 'System') AS createby,
format(getdate(),'yyyyMMddHHmmss') AS createdate,
CASE WHEN (SELECT TOP CONVERT, CONVERT+1) AS seq FROM dbo.hr_md_emp_md0017 WHERE emp_id= md02.emp_id ORDER BY seq desc)
IS NOT NULL THEN
(SELECT TOP CONVERT, CONVERT+1) AS seq FROM dbo.hr_md_emp_md0017 WHERE emp_id= md02.emp_id ORDER BY seq DESC) ELSE '1'
END AS seq,
CONVERT,'1000') AS absentyp,
CONVERT, '12') AS remainquot
FROM dbo.hr_md_emp_md0002 md02
LEFT OUTER JOIN dbo.hr_md_emp_md0015 md15 ON md02.emp_id = md15.emp_id
WHERE md02.employee_status = '01'
AND md15.hiring_date <= REPLACE) AS DATE),'
', '')
AND md02.end_date = '99991231'
AND md02.emp_id NOT IN (
SELECT DISTINCT md02.emp_id
FROM dbo.hr_md_emp_md0002 md02
LEFT OUTER JOIN dbo.hr_md_emp_md0017 md17 ON md17.emp_id = md02.emp_id
WHERE md02.employee_status = '01'
AND md02.end_date = '99991231'
AND SUBSTRING >= YEAR)
)
--AND md02.emp_id ='03053749'
END

#5

Updated by Muhammad Bintar almost 3 years ago

  • Status changed from Deploy to Closed

Also available in: Atom PDF