Bug #3270
Updated by Muhammad Bintar about 2 months ago
dear tim developer mohon supportnya untuk perbaikan pada report travell all dimana untuk cost type kode 7000 hanya muncul 2 data , padahal di master data ada 3 data, jadi 1 data lagi yang belum tampil. contoh employeeid 00000122 !clipboard-202412061459-z3mkk.png! remote.minovais.com:31130 database sql server 2014 remote.minovais.com, 1442 Bank Kalteng Dev sp nya seperti ini ya== --USE [MinovaES_BankKalteng_Prod] --GO --/****** Object: StoredProcedure [dbo].[PRPTTVTRAVELALL] Script Date: 06/12/2024 14.32.04 ******/ --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO ----USE [MinovaES_Bank_Kalteng_Dev] ----GO ----/****** Object: StoredProcedure [dbo].[PRPTTVTRAVELALL] Script Date: 24/09/2024 10.07.42 ******/ ----SET ANSI_NULLS ON ----GO ----SET QUOTED_IDENTIFIER ON ----GO --ALTER PROCEDURE [dbo].[PRPTTVTRAVELALL] -- @PeriodFrom VARCHAR(8), -- @PeriodTo VARCHAR(8), -- @EmployeeIDFrom VARCHAR(8), -- @EmployeeIDTo VARCHAR(8), -- @TravelStatus VARCHAR(8), -- @TravelType VARCHAR(8), -- @CompanyID VARCHAR(10), -- @UserID VARCHAR(100) , -- @MenuID VARCHAR(10) --AS DECLARE @PeriodFrom VARCHAR (8) = '20241118' DECLARE @PeriodTo VARCHAR (8) = '20241121' DECLARE @EmployeeIDFrom VARCHAR (8) = '00000122' DECLARE @EmployeeIDTo VARCHAR (8) = '00000122' DECLARE @TravelStatus VARCHAR (8) = '02' DECLARE @TravelType VARCHAR (8) = '' DECLARE @CompanyID VARCHAR (10) = '1000' DECLARE @UserID VARCHAR (100) = 'minovais' DECLARE @MenuID VARCHAR (10) = 'TVR0002' ------------------ // End Get Otorisasi //--------------- IF ( @EmployeeIDFrom <> '' ) BEGIN IF ( @EmployeeIDTo = '' ) BEGIN SET @EmployeeIDTo = @EmployeeIDFrom END END DECLARE @EMP TABLE ( [emp_id] VARCHAR(8) , [full_name] VARCHAR(255) , [pos] VARCHAR(255) , [org] VARCHAR(255) , [grade] VARCHAR(255) ) INSERT INTO @EMP SELECT DISTINCT m1.[EmployeeID] , m1.[FullName] , m2.position , m2.organization , m3.PayrollGrade FROM dbo.PHRPA0001 m1 LEFT OUTER JOIN dbo.PHRPA0002 m2 ON m1.EmployeeID = m2.EmployeeID AND m2.StartDate <= @PeriodFrom AND m2.EndDate >= @PeriodTo LEFT OUTER JOIN dbo.PHRPA0003 m3 ON m1.EmployeeID = m3.EmployeeID AND m3.StartDate <= @PeriodFrom AND m3.EndDate >= @PeriodTo DECLARE @EMP_temp TABLE ( [emp_id] VARCHAR(8) , [full_name] VARCHAR(255) , [pos] VARCHAR(255) , [org] VARCHAR(255) , [grade] VARCHAR(255) ) IF ( @EmployeeIDTo <> '' ) BEGIN INSERT INTO @EMP_temp SELECT * FROM @EMP WHERE emp_id <= @EmployeeIDTo DELETE FROM @EMP INSERT INTO @EMP SELECT * FROM @EMP_temp DELETE FROM @EMP_temp END IF ( @EmployeeIDFrom <> '' ) BEGIN INSERT INTO @EMP_temp SELECT * FROM @EMP WHERE emp_id >= @EmployeeIDFrom DELETE FROM @EMP INSERT INTO @EMP SELECT * FROM @EMP_temp DELETE FROM @EMP_temp END INSERT INTO @EMP_temp SELECT DISTINCT m1.[emp_id] , m1.[full_name] , pos.[ObjectDescription] , org.[ObjectDescription] , gr.[PayrollGradeDescription] FROM @EMP m1 LEFT OUTER JOIN dbo.PHROM0001 pos ON m1.pos = pos.[ObjectID] AND pos.StartDate <= @PeriodFrom AND pos.EndDate >= @PeriodTo AND pos.ObjectClass = 'P' LEFT OUTER JOIN dbo.PHROM0001 org ON m1.org = org.[ObjectID] AND org.StartDate <= @PeriodFrom AND org.EndDate >= @PeriodTo AND org.ObjectClass = 'O' LEFT OUTER JOIN PHRPYPGRAD gr ON m1.grade = gr.PayrollGrade DELETE FROM @EMP INSERT INTO @EMP SELECT * FROM @EMP_temp DELETE FROM @EMP_temp DECLARE @DATA TABLE ( --[landscape] VARCHAR(3) , [emp_id] VARCHAR(MAX) , [start_date] VARCHAR(MAX) , [end_date] VARCHAR(MAX) , [doc_status] VARCHAR(MAX) , [type] VARCHAR(MAX) , [reason] VARCHAR(MAX) , [depature] VARCHAR(MAX) , [destination] VARCHAR(MAX) , [full_name] VARCHAR(MAX) , [pos] VARCHAR(MAX) , [org] VARCHAR(MAX) , [grade] VARCHAR(MAX) , [doc_num] VARCHAR(MAX) --[number] VARCHAR(255) ) INSERT INTO @DATA SELECT DISTINCT --m62_h.landscape, m1.[emp_id] , m62_h.[StartDate] , m62_h.[EndDate] , m62_h.[TravelStatus] , m62_h.[TravelType] , m62_h.[TravelReason] , m62_h.[Departure] , m62_h.[Destination] , m1.[full_name] , m1.[pos] , m1.[org] , m1.[grade] , m62_h.[HeaderSequence] FROM @EMP m1 INNER JOIN dbo.PHRPA0062 m62_h ON m1.emp_id = m62_h.EmployeeID WHERE ( m62_h.TravelType = @TravelType OR @TravelType = '' ) AND ( m62_h.TravelStatus = @TravelStatus OR @TravelStatus = '' ) DECLARE @DATA_Temp TABLE ( --[landscape] VARCHAR(3) , [emp_id] VARCHAR(MAX) , [start_date] VARCHAR(MAX) , [end_date] VARCHAR(MAX) , [doc_status] VARCHAR(MAX) , [type] VARCHAR(MAX) , [reason] VARCHAR(MAX) , [depature] VARCHAR(MAX) , [destination] VARCHAR(MAX) , [full_name] VARCHAR(MAX) , [pos] VARCHAR(MAX) , [org] VARCHAR(MAX) , [grade] VARCHAR(MAX) , [doc_num] VARCHAR(MAX) --[number] VARCHAR(255) ) IF ( @PeriodFrom <> '' ) BEGIN INSERT INTO @DATA_Temp SELECT * FROM @DATA WHERE start_date >= @PeriodFrom AND start_date <= @PeriodTo DELETE FROM @DATA INSERT INTO @DATA SELECT * FROM @DATA_Temp DELETE FROM @DATA_Temp END IF ( @PeriodTo <> '' ) BEGIN INSERT INTO @DATA_Temp SELECT * FROM @DATA WHERE start_date >= @PeriodFrom AND start_date <= @PeriodTo --WHERE end_date <= @PeriodTo DELETE FROM @DATA INSERT INTO @DATA SELECT * FROM @DATA_Temp DELETE FROM @DATA_Temp END IF ( @TravelStatus <> '' ) BEGIN INSERT INTO @DATA_Temp SELECT * FROM @DATA WHERE [doc_status] = @TravelStatus DELETE FROM @DATA INSERT INTO @DATA SELECT * FROM @DATA_Temp DELETE FROM @DATA_Temp END IF ( @TravelType <> '' ) BEGIN INSERT INTO @DATA_Temp SELECT * FROM @DATA WHERE type = @TravelType DELETE FROM @DATA INSERT INTO @DATA SELECT * FROM @DATA_Temp DELETE FROM @DATA_Temp END SELECT DISTINCT --cost.*, head.emp_id , head.full_name , md2.Position , pos.ObjectDescription pos , md2.Organization , org.ObjectDescription org , md3.PayrollGrade , gradedesc.PayrollGradeDescription gradedesc , head.type , trtypedesc.TravelTypeDescription trtypedesc , head.doc_status , trstatdesc.TravelStatusDescription trstatdesc , head.[doc_num] doc_number , head.start_date AS start_date, head.end_date AS end_date, dbo.fn_formatdatetime(head.start_date, 'dd/mm/yyyy') StartDate , dbo.fn_formatdatetime(head.end_date, 'dd/mm/yyyy') EndDate , head.destination , head.reason , cost.CostType , costdesc.CostTypeDesc costdesc , cost.Currency , cost.AmountPlan , cost.AmountSettlement , cost.AmountApproval , CONVERT(DECIMAL, cost.AmountPlan) - CONVERT(DECIMAL, cost.AmountApproval) balanced , curr.Currency AS curr_desc , curr.CurrencyDescription AS curr_name, md2.CompanyID FROM @DATA head LEFT JOIN dbo.PHRPA0065 cost ON head.emp_id=cost.EmployeeID AND head.[doc_num] = cost.HeaderSequence LEFT JOIN dbo.PHRPA0064 accom ON head.emp_id=accom.EmployeeID AND head.[doc_num] = accom.HeaderSequence LEFT JOIN dbo.PHRPA0063 route ON head.emp_id=route.EmployeeID AND head.[doc_num] = route.HeaderSequence --AND head.landscape = cost.landscape --JOIN hr_md_emp_md0001 md1 --ON head.emp_id = md1.emp_id --AND head.landscape = md1.landscape --AND md1.start_date <= head.end_date --AND md1.end_date >= head.end_date LEFT JOIN PHRTVCOSTYP costdesc ON cost.CostType = costdesc.CostType LEFT JOIN PHRTVTRAVTYP trtypedesc ON head.type = trtypedesc.TravelType LEFT JOIN PHRTVTRAVSTAT trstatdesc ON head.doc_status = trstatdesc.TravelStatus JOIN dbo.PHRPA0002 md2 ON head.emp_id = md2.EmployeeID --AND head.landscape = md2.landscape AND md2.StartDate <= head.end_date AND md2.EndDate >= head.end_date JOIN dbo.PHRPA0003 md3 ON head.emp_id = md3.EmployeeID --AND head.landscape = md3.landscape AND md3.StartDate <= head.end_date AND md3.EndDate >= head.end_date LEFT JOIN PHRPYPGRAD gradedesc ON md3.PayrollGrade = gradedesc.PayrollGrade LEFT JOIN dbo.PHROM0001 pos ON md2.Position = pos.ObjectID AND pos.ObjectClass = 'P' AND pos.StartDate <= head.end_date AND pos.EndDate >= head.end_date LEFT JOIN dbo.PHROM0001 org ON md2.Organization = org.ObjectID AND org.ObjectClass = 'O' AND org.StartDate <= head.end_date AND org.EndDate >= head.end_date LEFT JOIN dbo.PHRPYCURR AS curr ON curr.Currency = cost.Currency WHERE ( md2.CompanyID = @CompanyID OR @CompanyID = '' ) tinggal execute aja ke database kalteng dev sini