--15010
--15011
--15012
--16 maret - 16 mar
--.
SELECT 
        p.id_peserta, 
        p.nama, 
        p.tgl_lahir, 
        p.uang_pertanggungan, 
        p.tgl_mulai, 
        AJK.dbo.func_get_tgl_akhir(p.kode_produk,p.tgl_mulai,p.masa_kontrak,(AJK.dbo.func_get_id_tipe_periode(p.no_polis,p.create_date))) AS tgl_akhir, 
        p.masa_kontrak, 
        p.satuan_masa, 
        p.kode_produk, 
        p.bunga, 
        p.premi, 
		p.prosen_extra_premi, 
		p.extra_premi,
        p.umur, 
        p.no_peserta, 
        c.nama_cabang, 
		p.kode_medical,
        p.keterangan_medical,
        p.no_polis,
        prs.nama_perusahaan,
        dn.no_ref,
		--cs.print_code,
		ajk_p.id_peserta AS id_peserta_ajk,
		CASE
		  WHEN ajk_p.status='I' AND dn.no_ref IS NOT NULL THEN 'Approved'
		  WHEN ajk_p.id_peserta IS NULL THEN 'Unknown'
		  ELSE 'Pending'
		END AS status_peserta
    FROM peserta_ajk p LEFT OUTER JOIN 
        AJK.dbo.cabang c ON p.id_cabang=c.id_cabang LEFT JOIN 
        AJK.dbo.polis pls ON p.no_polis=pls.no_polis LEFT JOIN
        AJK.dbo.perusahaan prs ON pls.id_perusahaan=prs.id_perusahaan LEFT JOIN
		AJK.dbo.peserta AS ajk_p ON p.no_peserta = ajk_p.no_peserta AND p.no_polis = ajk_p.no_polis LEFT JOIN 
		(
			SELECT
				nt.no_ref,
				ntp.id_peserta,
				nt.no_polis
			FROM
				AJK.dbo.note_terbit_peserta AS ntp INNER JOIN 
				AJK.dbo.note_terbit AS nt ON ntp.id_note_terbit = nt.id_note_terbit
			WHERE
				nt.status = 'I'
		) AS dn ON ajk_p.id_peserta = dn.id_peserta AND p.no_polis = dn.no_polis --LEFT JOIN
    WHERE p.submitted=1 
        AND p.submitted_date>='2016-06-20 00:00:00' 
        AND p.submitted_date<='2016-06-20 23:59:59' 
        --AND ((p.id_cabang IN (SELECT id_cabang FROM AJK.dbo.func_get_hirarki_cabang('$Login_User_Id_Cabang', 0, 1)) AND coalesce('$Login_User_Id_Cabang', '')<>'') OR (coalesce('$Login_User_Id_Cabang', '')='')) 
		    AND p.status='I'
        AND p.no_polis IN ('15010-01-000', '15011-01-000', '15012-01-000', '16003-01-000')
	ORDER BY p.submitted_date

