USE [AJK]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2018-10-31
CREATE FUNCTION dbo.func_custport_get_claim_status
(
	@pi_claim_no VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
	DECLARE @po_status VARCHAR(50)

	SELECT 
		@po_status = CASE
						--WHEN (lunas.id_note_lunas IS NOT NULL) AND (k.id_status_klaim <> '06') THEN 'Paid'	-- jika mengecek ke tabel note_lunas
						WHEN (lunas.id_klaim IS NOT NULL) AND (k.id_status_klaim <> '06') THEN 'Paid'		-- jika tidak mengecek ke tabel note_lunas
						ELSE
							CASE k.id_status_klaim
								WHEN '05' THEN 'Approve'
								WHEN '06' THEN 'Reject'
								ELSE cms.description
							END 
					END
	FROM
		dbo.custport_claim AS cc INNER JOIN
		dbo.custport_m_status AS cms ON cc.status_code = cms.status_code LEFT JOIN
		dbo.klaim AS k ON cc.id_klaim_core = k.id_klaim LEFT JOIN
		---- ini query jika mengecek ke tabel note_lunas:
		--(
		--	SELECT
		--		k.id_klaim,
		--		nl.id_note_lunas
		--	FROM
		--		dbo.klaim AS k INNER JOIN
		--		dbo.note_terbit_peserta AS ntp ON k.id_klaim = ntp.id_klaim INNER JOIN
		--		dbo.note_terbit AS nt ON ntp.id_note_terbit = nt.id_note_terbit INNER JOIN
		--		dbo.note_lunas AS nl ON nt.id_note_terbit = nl.id_note_terbit
		--	WHERE
		--		k.status = 'I'
		--		AND nt.status = 'I'
		--		AND ntp.status = 'I'
		--		AND nl.status = 'I' -- ?
		--) AS lunas ON cc.id_klaim_core = k.id_klaim
		---- ini query yang tidak mengecek tabel note_lunas, tapi mengecek beberapa isi kolom di note_terbit:
		(
			SELECT
				k.id_klaim
			FROM
				dbo.klaim AS k INNER JOIN
				dbo.note_terbit_peserta AS ntp ON k.id_klaim = ntp.id_klaim INNER JOIN
				dbo.note_terbit AS nt ON ntp.id_note_terbit = nt.id_note_terbit
			WHERE
				k.status = 'I'
				AND nt.status = 'I'
				AND ntp.status = 'I'
				AND nt.lunas_ya = 1
				AND COALESCE(nt.no_voucher,'') <> ''
				AND COALESCE(nt.lunas_by,'') <> ''
				AND nt.tgl_input_lunas IS NOT NULL
		) AS lunas ON cc.id_klaim_core = lunas.id_klaim
	WHERE
		(cc.is_active = 1)
		AND (cms.is_active = 1)
		AND cc.claim_no = @pi_claim_no

	RETURN @po_status

END
GO
