USE [AJK]
GO
/****** Object:  UserDefinedFunction [dbo].[func_custport_list_claim]    Script Date: 18/07/2018 11:12:56 ******/
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.*,
		cms.description 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
				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
		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
	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','COM') OR COALESCE(@pi_tipe_data, 0) <> 1)

)
