IF EXISTS (SELECT *
           FROM   sys.views
           WHERE  object_id = Object_id(N'[DBO].[V_EST_NF_SAI_CPL]'))
  DROP VIEW [dbo].[V_EST_NF_SAI_CPL]

go

/*select top 10 * from V_EST_NF_SAI_CPL*/
CREATE VIEW [dbo].[V_EST_NF_SAI_CPL]
AS
  SELECT dbo.est_nf_sai.cd_emp,
         dbo.est_nf_sai.cd_filial,
         dbo.est_nf_sai.cd_nf,
         dbo.est_nf_sai.cd_nf_serie,
         dbo.est_nf_sai.nf_nf,
         dbo.est_nf_sai.cd_cli     AS CD_DEST,
         CASE
           WHEN dest_orig = 0 THEN (SELECT rz_cli
                                    FROM   rc_cli
                                    WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
                                           AND rc_cli.cd_cli =
                                   est_nf_sai.cd_cli)
           WHEN dest_orig = 1 THEN (SELECT rz_forn
                                    FROM   pg_forn
                                    WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
                                           AND pg_forn.cd_forn =
                                   est_nf_sai.cd_cli)
           WHEN dest_orig = 2 THEN (SELECT rz_filial
                                    FROM   prc_filial
                                    WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
                                           AND prc_filial.cd_filial =
                                               est_nf_sai.cd_cli)
           ELSE
		'DESTINATRIO SEM CLASSIFICAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS NM_FANT,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT cgc_cpf
								   FROM   rc_cli
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli)
		  WHEN dest_orig = 1 THEN (SELECT cgc_cpf
								   FROM   pg_forn
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli)
		  WHEN dest_orig = 2 THEN (SELECT cgc
								   FROM   prc_filial
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS CGC_CPF,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT insc_est
								   FROM   rc_cli
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli)
		  WHEN dest_orig = 1 THEN (SELECT insc_est
								   FROM   pg_forn
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli)
		  WHEN dest_orig = 2 THEN (SELECT insc_est
								   FROM   prc_filial
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS INSC_EST,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT end_cli
								   FROM   rc_cli
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli)
		  WHEN dest_orig = 1 THEN (SELECT end_forn
								   FROM   pg_forn
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli)
		  WHEN dest_orig = 2 THEN (SELECT end_fililal
								   FROM   prc_filial
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS ENDE,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT bairro
								   FROM   rc_cli
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli)
		  WHEN dest_orig = 1 THEN (SELECT bairro_forn
								   FROM   pg_forn
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli)
		  WHEN dest_orig = 2 THEN (SELECT bairro
								   FROM   prc_filial
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS BAIRRO,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT cep
								   FROM   rc_cli
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli)
		  WHEN dest_orig = 1 THEN (SELECT cep
								   FROM   pg_forn
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli)
		  WHEN dest_orig = 2 THEN (SELECT cep
								   FROM   prc_filial
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS CEP,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT tel
								   FROM   rc_cli
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli)
		  WHEN dest_orig = 1 THEN (SELECT tel
								   FROM   pg_forn
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli)
		  WHEN dest_orig = 2 THEN (SELECT tel
								   FROM   prc_filial
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS TEL,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT ds_cid
								   FROM   rc_cli,
										  glb_cid
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli
										  AND rc_cli.cd_cid = glb_cid.cd_cid)
		  WHEN dest_orig = 1 THEN (SELECT ds_cid
								   FROM   pg_forn,
										  glb_cid
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli
										  AND pg_forn.cd_cid = glb_cid.cd_cid)
		  WHEN dest_orig = 2 THEN (SELECT ds_cid
								   FROM   prc_filial,
										  glb_cid
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli
										  AND prc_filial.cd_cid = glb_cid.cd_cid)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO,SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS DS_CID,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT rc_cli.cd_cid
								   FROM   rc_cli,
										  glb_cid
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli
										  AND rc_cli.cd_cid = glb_cid.cd_cid)
		  WHEN dest_orig = 1 THEN (SELECT pg_forn.cd_cid
								   FROM   pg_forn,
										  glb_cid
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli
										  AND pg_forn.cd_cid = glb_cid.cd_cid)
		  WHEN dest_orig = 2 THEN (SELECT prc_filial.cd_cid
								   FROM   prc_filial,
										  glb_cid
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli
										  AND prc_filial.cd_cid = glb_cid.cd_cid)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO,SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS CD_CID,
		CASE
		  WHEN dest_orig = 0 THEN (SELECT uf
								   FROM   rc_cli,
										  glb_cid
								   WHERE  rc_cli.cd_emp = est_nf_sai.cd_emp
										  AND rc_cli.cd_cli = est_nf_sai.cd_cli
										  AND rc_cli.cd_cid = glb_cid.cd_cid)
		  WHEN dest_orig = 1 THEN (SELECT uf
								   FROM   pg_forn,
										  glb_cid
								   WHERE  pg_forn.cd_emp = est_nf_sai.cd_emp
										  AND pg_forn.cd_forn = est_nf_sai.cd_cli
										  AND pg_forn.cd_cid = glb_cid.cd_cid)
		  WHEN dest_orig = 2 THEN (SELECT uf
								   FROM   prc_filial,
										  glb_cid
								   WHERE  prc_filial.cd_emp = est_nf_sai.cd_emp
										  AND prc_filial.cd_filial = est_nf_sai.cd_cli
										  AND prc_filial.cd_cid = glb_cid.cd_cid)
		  ELSE
		'DESTINATRIO SEM CLASSIFICAO,  SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICAO DO MESMO'
		END                       AS UF,
		dbo.est_nf_sai.dt_emi_nf,
		dbo.est_nf_sai.hr_emi_nf,
		dbo.est_nf_sai.cd_nt_mov,
		dbo.est_nf_sai_glb_nt_op.cd_nt_op,
		dbo.est_nf_sai.vlr_nf,
		dbo.est_nf_sai.vlr_tot_prod,
		dbo.est_nf_sai.vlr_base_icms,
		dbo.est_nf_sai.vlr_icms,
		dbo.est_nf_sai.vlr_base_icms_subs,
		dbo.est_nf_sai.vlr_icms_subs,
		dbo.est_nf_sai.vlr_ipi,
		dbo.est_nf_sai.vlr_frete,
		dbo.est_nf_sai.sts_nf,
		dbo.est_nf_sai.cd_usu,
		dbo.est_nf_sai.dt_cad,
		dbo.est_nf_sai.dt_ult_alt,
		dbo.glb_mov_op.tp_mov_op_transa,
		dbo.glb_mov_op.tp_mov,
		dbo.glb_nt_op.cd_nt_op_fc AS CFOP,
		dbo.glb_mov_op.ds_mov_op,
		dbo.est_nf_sai.obs1,
		dbo.est_nf_sai.obs2,
		dbo.est_nf_sai.obs3,
		dbo.est_nf_sai.obs4,
		dbo.est_nf_sai.obs5,
		dbo.est_nf_sai.obs6,
		dbo.est_nf_sai.obs7,
		dbo.est_nf_sai.obs8,
		dbo.est_nf_sai.obs9,
		dbo.est_nf_sai.obs10,
		dbo.est_nf_sai.obs11,
		dbo.glb_mov_op.dest_orig,
		dbo.est_nf_sai.nr_selo_saida,
		glb_usu.nm_usu,
		ISNULL(est_nf_sai.VLR_ICMS_SUBSTITUTO_CAB,0) AS VLR_ICMS_SUBSTITUTO_CAB
FROM   dbo.est_nf_sai
       INNER JOIN dbo.est_nf_sai_glb_nt_op
               ON dbo.est_nf_sai.cd_emp = dbo.est_nf_sai_glb_nt_op.cd_emp
                  AND dbo.est_nf_sai.cd_filial =
                      dbo.est_nf_sai_glb_nt_op.cd_filial
                  AND dbo.est_nf_sai.cd_nf = dbo.est_nf_sai_glb_nt_op.cd_nf
       INNER JOIN dbo.glb_mov_op
               ON dbo.est_nf_sai.cd_nt_mov = dbo.glb_mov_op.cd_mov_op
       INNER JOIN dbo.glb_nt_op
               ON dbo.glb_nt_op.cd_nt_op = dbo.est_nf_sai_glb_nt_op.cd_nt_op
       INNER JOIN glb_usu
               ON est_nf_sai.cd_usu = glb_usu.cd_usu

go 