USE [AJK]
GO
/****** Object:  UserDefinedFunction [dbo].[func_custport_list_claim]    Script Date: 14/09/2018 14:42:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2018-07-10
-- catatan: jika ada penambahan/hapus (alter) kolom tabel yg dipake di function ini, query dibawah harus dijalankan (alter) lagi
-- karena ada bugs di sql server, dia enggak me-refresh secara otomatis kolom-kolom di function ini (jika pake bintang)
-- https://www.mssqltips.com/sqlservertip/1427/table-changes-not-automatically-reflected-in-a-sql-server-view/
-- https://stackoverflow.com/questions/43131121/table-valued-function-refresh
ALTER FUNCTION [dbo].[func_custport_list_claim] (
  @pi_username VARCHAR(15),
  @pi_period_from VARCHAR(20),
  @pi_period_to VARCHAR(20),
  @pi_customer_name VARCHAR(100),
  @pi_contract_no VARCHAR(20),
  @pi_register_no VARCHAR(50),
  @pi_status VARCHAR(5),
  @pi_tipe_data TINYINT = 0	-- 0: all/inquiry
							-- 1: os, status not in (cancel, complete)
)
RETURNS TABLE
AS
RETURN ( 
	SELECT DISTINCT
		--cc.*,
		cc.claim_id, 
		cc.claim_no, 
		cc.no_polis, 
		cc.id_peserta, 
		cc.certificate_no, 
		cc.contract_no, 
		cc.customer_name, 
		cc.register_date, 
		cc.event_date, 
		cc.claim_type_code, 
		cc.currency_code, 
		cc.claim_amount, 
		cc.reference_no, 
		cc.status_code, 
		cc.flow_stage_code, 
		cc.reason_code, 
		cc.notes, 
		cc.alt_phone, 
		cc.alt_email, 
		cc.account_no, 
		cc.account_beneficiary, 
		cc.account_currency_code, 
		cc.bank_id, 
		cc.bank_name, 
		cc.bank_code, 
		cc.bank_branch, 
		cc.company_code, 
		cc.company_region_code, 
		cc.tmp_guid, 
		cc.id_klaim_core, 
		cc.complete_doc_date, 
		cc.created_by, 
		cc.created_date, 
		cc.modified_by, 
		cc.modified_date, 
		cc.is_active,

		--cms.description AS status,
		CASE
			WHEN (lunas.id_note_lunas IS NOT NULL) AND (k.id_status_klaim <> '06') THEN 'Paid'
			ELSE
				CASE k.id_status_klaim
					WHEN '05' THEN 'Approve'
					WHEN '06' THEN 'Reject'
					ELSE cms.description
				END 
		END AS status,

		cmct.description AS claim_type
	FROM
		dbo.custport_claim AS cc INNER JOIN
		--(
		--	SELECT
		--		cmu.username,
		--		cmuc.company_code,
		--		cmuc.company_region_code
		--	FROM
		--		dbo.custport_m_user AS cmu INNER JOIN
		--		dbo.custport_m_user_company AS cmuc ON cmu.username = cmuc.username
		--	WHERE
		--		cmu.is_active = 1
		--		AND cmuc.is_active = 1
		--) AS company_region_create ON cc.created_by = company_region_create.username INNER JOIN
		(
			SELECT
				cmuc.company_code,
				cmuc.company_region_code
			FROM
				dbo.custport_m_user AS cmu INNER JOIN
				dbo.custport_m_user_company AS cmuc ON cmu.username = cmuc.username
			WHERE
				(cmu.username = @pi_username OR COALESCE(@pi_tipe_data, 0) <> 1)
				AND cmu.is_active = 1
				AND cmuc.is_active = 1
		) AS company_region_user ON --company_region_create.company_code = company_region_user.company_code
									--AND company_region_create.company_region_code = company_region_user.company_region_code INNER JOIN
									cc.company_code = company_region_user.company_code
									AND cc.company_region_code = company_region_user.company_region_code INNER JOIN
		dbo.custport_m_status AS cms ON cc.status_code = cms.status_code LEFT JOIN
		dbo.custport_m_claim_type AS cmct ON cc.claim_type_code = cmct.claim_type_code LEFT JOIN
		dbo.klaim AS k ON cc.id_klaim_core = k.id_klaim LEFT JOIN
		(
			SELECT
				k.id_klaim,
				nl.id_note_lunas
			FROM
				klaim AS k INNER JOIN
				note_terbit_peserta AS ntp ON k.id_klaim = ntp.id_klaim INNER JOIN
				note_terbit AS nt ON ntp.id_note_terbit = nt.id_note_terbit INNER JOIN
				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
	WHERE
		(cc.is_active = 1)
		AND (cms.is_active = 1)
		AND (cc.register_date >= CAST(@pi_period_from AS DATE) OR ISNULL(@pi_period_from, '') = '')
		AND (cc.register_date <= CAST(@pi_period_to AS DATE) OR ISNULL(@pi_period_to, '') = '')
		AND (cc.customer_name LIKE '%'+@pi_customer_name+'%' OR ISNULL(@pi_customer_name, '') = '')
		AND (cc.contract_no = @pi_contract_no OR ISNULL(@pi_contract_no, '') = '')
		AND (cc.claim_no = @pi_register_no OR ISNULL(@pi_register_no, '') = '')
		AND (cc.status_code = @pi_status OR ISNULL(@pi_status, '') = '')
		AND (cc.status_code NOT IN ('CNC','DRP','RJT') OR COALESCE(@pi_tipe_data, 0) <> 1)

)

--CNC	Canceled
--DRP	Claim Dropped
--RJT	Claim Rejected
