USE GrpInsuranceUAT

DECLARE
  @pi_no_polis VARCHAR(11) = '2016.0196.0'

--------------------------------------------------------------------

UPDATE
  GrpInsuranceUAT.dbo.custport_user_login_client
SET
  status = 'G'
WHERE
  id_user IN (68,69,70,71,72,73,74,75,76,77)

------

DECLARE @v_no_proposal VARCHAR(9)
SELECT
	@v_no_proposal = No_Proposal
FROM
	dbo.Polis_Asuransi
WHERE
  No_Polis = @pi_no_polis
  AND Status = 'I'


---- hapus semua data di customer portal ----
DELETE FROM GrpInsuranceUAT.dbo.custport_klaim  
DELETE FROM GrpInsuranceUAT.dbo.custport_klaim_aai
DELETE FROM GrpInsuranceUAT.dbo.custport_klaim_aai_history
DELETE FROM GrpInsuranceUAT.dbo.custport_klaim_detail
DELETE FROM GrpInsuranceUAT.dbo.custport_klaim_dokumen
DELETE FROM GrpInsuranceUAT.dbo.custport_klaim_email
DELETE FROM GrpInsuranceUAT.dbo.custport_klaim_history
DELETE FROM GrpInsuranceUAT.dbo.custport_payment_email
DELETE FROM GrpInsuranceUAT.dbo.custport_rekening_peserta
DELETE FROM GrpInsuranceUAT.dbo.custport_from_aai_header
DELETE FROM GrpInsuranceUAT.dbo.custport_from_aai_detail
DELETE FROM GrpInsuranceUAT.dbo.custport_svc_user_session_client WHERE session_expire> GETDATE()

---- hapus data di GroupInsurance ----
DELETE FROM GrpInsuranceUAT.dbo.Note_Lunas_Master WHERE No_Ref IN (SELECT No_Ref FROM GrpInsuranceUAT.dbo.Klaim_Ref_Detail WHERE No_Klaim IN (SELECT No_Klaim FROM GrpInsuranceUAT.dbo.Klaim_Peserta WHERE No_Proposal=@v_no_proposal))
DELETE FROM GrpInsuranceUAT.dbo.Note_Lunas_Detail WHERE No_Ref IN (SELECT No_Ref FROM GrpInsuranceUAT.dbo.Klaim_Ref_Detail WHERE No_Klaim IN (SELECT No_Klaim FROM GrpInsuranceUAT.dbo.Klaim_Peserta WHERE No_Proposal=@v_no_proposal))

--DELETE FROM GrpInsuranceUAT.dbo.Note_Terbit_Detail WHERE No_Ref IN (SELECT No_Ref FROM GrpInsuranceUAT.dbo.Klaim_Ref_Detail WHERE No_Klaim IN (SELECT No_Klaim FROM GrpInsuranceUAT.dbo.Klaim_Peserta WHERE No_Proposal=@v_no_proposal))
--DELETE FROM GrpInsuranceUAT.dbo.Note_Terbit_Master WHERE No_Ref IN (SELECT No_Ref FROM GrpInsuranceUAT.dbo.Klaim_Ref_Detail WHERE No_Klaim IN (SELECT No_Klaim FROM GrpInsuranceUAT.dbo.Klaim_Peserta WHERE No_Proposal=@v_no_proposal))

DELETE FROM GrpInsuranceUAT.dbo.Note_Terbit_Detail WHERE No_Ref IN (SELECT No_Ref FROM GrpInsuranceUAT.dbo.Note_Terbit_Master WHERE No_Proposal=@v_no_proposal AND Jenis=20)
DELETE FROM GrpInsuranceUAT.dbo.Note_Terbit_Master WHERE No_Proposal=@v_no_proposal AND Jenis=20

DELETE krm FROM GrpInsuranceUAT.dbo.Klaim_Ref_Master AS krm INNER JOIN GrpInsuranceUAT.dbo.Klaim_Ref_Detail AS krd ON krm.No_Ref = krd.No_Ref AND krm.No_Klaim = krd.No_Klaim WHERE krd.No_Klaim IN (SELECT No_Klaim FROM dbo.Klaim_Peserta WHERE No_Proposal=@v_no_proposal)
DELETE FROM GrpInsuranceUAT.dbo.Klaim_Ref_Detail WHERE No_Klaim IN (SELECT No_Klaim FROM GrpInsuranceUAT.dbo.Klaim_Peserta WHERE No_Proposal=@v_no_proposal)
DELETE FROM GrpInsuranceUAT.dbo.Klaim_Detail WHERE No_Klaim IN (SELECT No_Klaim FROM GrpInsuranceUAT.dbo.Klaim_Peserta WHERE No_Proposal=@v_no_proposal)
DELETE FROM GrpInsuranceUAT.dbo.Klaim_Peserta WHERE No_Proposal=@v_no_proposal
