USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_view_pending_klaim]    Script Date: 28/06/2018 8:48:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tri Mulyo Suanto
-- Last modified by Pribadi Pamungkas. 2018-07-04. Ticket RQ2018060393
-- Create date: 19 Maret 2018
-- Description:	View Pending Klaim
-- EXEC proc_view_pending_klaim '2017.0001', 'Tri Ms'
-- =============================================
ALTER PROCEDURE [dbo].[proc_view_pending_klaim] 
	-- Add the parameters for the stored procedure here
	@pi_No_Proposal varchar(9), 
	@po_message varchar(255) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	--SELECT No_Ref 
	--FROM dbo.Note_Terbit_Master 
	--WHERE 
	--	--No_Proposal = '2017.0001' AND 
	--	No_Proposal = @pi_No_Proposal AND 
	--	No_Ref NOT IN
	--			(
	--				SELECT No_Ref 
	--				FROM dbo.Note_Lunas_Master
	--				WHERE 
	--					No_Ref IN
	--						(
	--							SELECT No_Ref 
	--							FROM dbo.Note_Terbit_Master 
	--							--WHERE No_Proposal = '2017.0001'
	--							WHERE No_Proposal = @pi_No_Proposal
	--						)
	--			)

	
	SET @po_message = 'No Referensi Klaim Yang Masih Pending : '

	DECLARE @No_Ref varchar(12)

	DECLARE cur_view_pending_klaim CURSOR FOR
	SELECT No_Ref 
	FROM dbo.Note_Terbit_Master 
	WHERE 
		--No_Proposal = '2017.0001' AND 
		No_Proposal = @pi_No_Proposal AND 
		Status = 'I' AND  -- add by Pribadi Pamungkas. 2018-06-28. Ticket RQ2018060393
		Jenis IN (21, 22, 28, 29, 41, 42, 46) AND	-- Hanya CN.CL saja. Add by Pribadi Pamungkas. 2018-07-03. Permintaan tambahan utk ticket RQ2018060393
		(	-- Tampilkan yg ada nominalnya saja. Add by Pribadi Pamungkas. 2018-07-03. Permintaan tambahan utk ticket RQ2018060393
			(
				Mata_Uang='01' 
				AND CAST(ABS(Total) AS NUMERIC(18,2)) >= 1000	-- untuk rupiah, yg nominalnya Rp 1000 keatas
			)
			OR
			(
				Mata_Uang='02' 
				AND CAST(ABS(Total) AS NUMERIC(18,2)) >= 0.1	-- untuk dollar, yg nominalnya $ 0.1 keatas
			)
		) AND
		No_Ref NOT IN
				(
					SELECT No_Ref 
					FROM dbo.Note_Lunas_Master
					WHERE 
						No_Ref IN
							(
								SELECT No_Ref 
								FROM dbo.Note_Terbit_Master 
								--WHERE No_Proposal = '2017.0001'
								WHERE No_Proposal = @pi_No_Proposal
									 AND Status = 'I'  -- add by Pribadi Pamungkas. 2018-06-28. Ticket RQ2018060393
									 AND Jenis IN (21, 22, 28, 29, 41, 42, 46)	-- Hanya CN.CL saja. Add by Pribadi Pamungkas. 2018-07-03. Permintaan tambahan utk ticket RQ2018060393
									 AND	-- Tampilkan yg ada nominalnya saja. Add by Pribadi Pamungkas. 2018-07-03. Permintaan tambahan utk ticket RQ2018060393
										(
											(
												Mata_Uang='01' 
												AND CAST(ABS(Total) AS NUMERIC(18,2)) >= 1000	-- untuk rupiah, yg nominalnya Rp 1000 keatas
											)
											OR
											(
												Mata_Uang='02' 
												AND CAST(ABS(Total) AS NUMERIC(18,2)) >= 0.1	-- untuk dollar, yg nominalnya $ 0.1 keatas
											)
										)
							)
				)
	OPEN cur_view_pending_klaim
	FETCH NEXT FROM cur_view_pending_klaim INTO @No_Ref
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @po_message = @po_message + @No_Ref

		FETCH NEXT FROM cur_view_pending_klaim INTO @No_Ref

		IF @@FETCH_STATUS = 0 
			SET @po_message = @po_message + ', '
	END
	CLOSE cur_view_pending_klaim
	DEALLOCATE cur_view_pending_klaim

	--PRINT @po_message
END
