USE [GrpInsurance]
GO

SELECT
	*
FROM
	dbo.custport_provider
	
DELETE FROM [GrpInsurance].[dbo].[custport_provider] WHERE is_aai = 0 -- AND COALESCE(IDI,'')<>''
INSERT INTO dbo.custport_provider
        (no_provider,
         provider_name,
         address,
         city,
         province,
         country,
         op,
         ip,
         phone,
         notes,
         local_provider,
         is_aai,
         is_admedika,
         is_eli,
         status,
         create_by,
         create_date,
         change_by,
         change_date,
         IDI)
SELECT
	NULL AS no_provider,
	p.Nama,
	p.Alamat,
	p.Kota,
	w.Nama AS province,
	'Indonesia' AS country,
	0,
	0,
	'+'+Telp1_kode_negara+' '+RIGHT(Telp1_kode_area,LEN(Telp1_kode_area)-1)+' '+Telp1_nomor AS phone,
	NULL AS notes,
	1 AS local_provider,
	0, 
	COALESCE(p.Provider_Adm,0),
	1,
	'I',
	'SYS',
	GETDATE(),
	NULL,
	NULL,
	IDI
FROM
	dbo.Provider AS p LEFT JOIN
	dbo.Wilayah AS w ON p.Kode_Wilayah = w.Kode
WHERE
  p.Status = 'I'

--SELECT
--  '2016.'+CAST(id_provider AS VARCHAR(6)),
--	*
--FROM
--	dbo.custport_provider
--WHERE
--  no_provider IS NULL

--UPDATE
--	dbo.custport_provider
--SET
--  no_provider = '2016.'+CAST(id_provider AS VARCHAR(6))
--WHERE
--  no_provider IS NULL
