CLIENT SUPPORT » BANK KALTENG Bugs

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

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,
-- @PeriodTo VARCHAR,
-- @EmployeeIDFrom VARCHAR,
-- @EmployeeIDTo VARCHAR,
-- @TravelStatus VARCHAR,
-- @TravelType VARCHAR,
-- @CompanyID VARCHAR,
-- @UserID VARCHAR ,
-- @MenuID VARCHAR
--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 ,
[full_name] VARCHAR ,
[pos] VARCHAR ,
[org] VARCHAR ,
[grade] VARCHAR
)

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 ,
[full_name] VARCHAR ,
[pos] VARCHAR ,
[org] VARCHAR ,
[grade] VARCHAR
)

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 ,
[emp_id] VARCHAR ,
[start_date] VARCHAR ,
[end_date] VARCHAR ,
[doc_status] VARCHAR ,
[type] VARCHAR ,
[reason] VARCHAR ,
[depature] VARCHAR ,
[destination] VARCHAR ,
[full_name] VARCHAR ,
[pos] VARCHAR ,
[org] VARCHAR ,
[grade] VARCHAR ,
[doc_num] VARCHAR
--[number] VARCHAR
)

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 ,
[emp_id] VARCHAR ,
[start_date] VARCHAR ,
[end_date] VARCHAR ,
[doc_status] VARCHAR ,
[type] VARCHAR ,
[reason] VARCHAR ,
[depature] VARCHAR ,
[destination] VARCHAR ,
[full_name] VARCHAR ,
[pos] VARCHAR ,
[org] VARCHAR ,
[grade] VARCHAR ,
[doc_num] VARCHAR
--[number] VARCHAR
)

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
- CONVERT 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