DROP TABLE TMP_ENDPARTSPED
/

CREATE GLOBAL TEMPORARY TABLE TMP_ENDPARTSPED
(
   NRSEQENDPARTSPED  NUMBER        NOT NULL,
   INSCRICAO         VARCHAR2(20)  NOT NULL,
   PAIS              VARCHAR2(4)   NULL,
   ESTADO            VARCHAR2(2)   NULL,
   MUNICIPIO         VARCHAR2(4)   NULL,
   ENDERECO          VARCHAR2(100) NULL,
   NUMERO            VARCHAR2(10)  NULL,
   COMPLEMENTO       VARCHAR2(200) NULL,
   BAIRRO            VARCHAR2(50)  NULL,
   PARTICIPANTE      CLOB          NOT NULL,
   CONSTRAINT PK_TMP_ENDPARTSPED PRIMARY KEY(NRSEQENDPARTSPED)) ON COMMIT PRESERVE ROWS
/

CREATE OR REPLACE PROCEDURE GRAVA_ENDPART_SPED_ORCLANT
AS
   V_DSCODPART        VARCHAR2(400);
   V_PARTICIPANTE     CLOB;
   V_NRSEQENDPARTSPED NUMBER;
BEGIN
   DELETE FROM TMP_ENDPARTSPED;
   V_DSCODPART        := NULL;
   V_PARTICIPANTE     := NULL;
   V_NRSEQENDPARTSPED := NULL;
   FOR C_DADOS IN (SELECT PARTICIPANTE, INSCRICAO, PAIS, ESTADO, MUNICIPIO, ENDERECO, NUMERO, COMPLEMENTO, BAIRRO,
                          INSCRICAO||PAIS||ESTADO||MUNICIPIO||ENDERECO||NUMERO||COMPLEMENTO||BAIRRO AS DSCODPART
                     FROM (SELECT 'C'||CL.CDCLIENTE AS PARTICIPANTE, CL.NRINSJURCLIE AS INSCRICAO, EC.CDPAIS AS PAIS, EC.SGESTADO AS ESTADO, EC.CDMUNICIPIO AS MUNICIPIO,
                                  NVL(EC.DSENDECLIESEP,EC.DSENDECLIE) AS ENDERECO, EC.NRENDECLIESEP AS NUMERO,
                                  EC.DSCOMPENDCLI AS COMPLEMENTO, EC.NMBAIRCLIE AS BAIRRO
                             FROM CLIENTE CL, ENDECLIE EC
                            WHERE CL.CDCLIENTE = EC.CDCLIENTE
                              AND CL.IDENDEMISCLI = EC.IDTPENDECLIE
                            UNION ALL
                           SELECT 'F'||FO.CDFORNECED AS PARTICIPANTE, FO.NRINSJURFORN AS INSCRICAO, EF.CDPAIS AS PAIS, EF.SGESTADO AS ESTADO, EF.CDMUNICIPIO AS MUNICIPIO,
                                  NVL(EF.DSENDEFORNSEP,EF.DSENDEFORN) AS ENDERECO, EF.NRENDEFORNSEP AS NUMERO,
                                  EF.DSCOMPENDFOR AS COMPLEMENTO, EF.NMBAIRFORN AS BAIRRO
                             FROM FORNECEDOR FO, ENDEFORN EF
                            WHERE FO.CDFORNECED = EF.CDFORNECED
                              AND FO.IDENDEMISFOR = EF.IDTPENDEFORN
                            UNION ALL
                           SELECT 'L'||FI.CDFILIAL AS PARTICIPANTE, FI.NRINSJURFILI, DECODE(FI.IDENDEMISFIL,'I',I.CDPAIS,EF.CDPAIS) AS CDPAIS,
                                  DECODE(FI.IDENDEMISFIL,'I',I.SGESTADO,EF.SGESTADO) AS ESTADO,
                                  DECODE(FI.IDENDEMISFIL,'I',I.CDMUNICIPIO,EF.CDMUNICIPIO) AS MUNICIPIO,
                                  DECODE(FI.IDENDEMISFIL,'I',NVL(I.DSENDEIESTSEP,I.DSENDEIEST),NVL(EF.DSENDEFILISEP,EF.DSENDEFILI)) AS ENDERECO,
                                  DECODE(FI.IDENDEMISFIL,'I',I.NRENDEIESTSEP,EF.NRENDEFILISEP) AS NUMERO,
                                  DECODE(FI.IDENDEMISFIL,'I',I.DSCOMPENDIES,EF.DSCOMPENDFIL) AS COMPLEMENTO,
                                  DECODE(FI.IDENDEMISFIL,'I',I.NMBAIRIEST,EF.NMBAIRFILI) AS BAIRRO
                             FROM FILIAL FI, ENDEFILI EF, INSCRESTAD I
                            WHERE EF.CDFILIAL        = FI.CDFILIAL
                              AND ((EF.IDTPENDEFILI  = NVL(FI.IDENDEMISFIL,'P')) OR (FI.IDENDEMISFIL = 'I' AND EF.IDTPENDEFILI = 'P'))
                              AND FI.CDINSCESTA      = I.CDINSCESTA
                              AND FI.CDEMPRESA       = I.CDEMPRESA)
                    ORDER BY INSCRICAO, PAIS, ESTADO, MUNICIPIO, ENDERECO, NUMERO, COMPLEMENTO, BAIRRO) LOOP

	   IF (V_DSCODPART IS NULL) OR (V_DSCODPART <> C_DADOS.DSCODPART) THEN
	      NOVO_CODIGO_NT('ENDPARTSPED',V_NRSEQENDPARTSPED);
	      INSERT INTO TMP_ENDPARTSPED
		     (NRSEQENDPARTSPED,INSCRICAO, PAIS, ESTADO, MUNICIPIO, ENDERECO, NUMERO, COMPLEMENTO, BAIRRO, PARTICIPANTE)
		  VALUES
		     (TO_NUMBER(V_NRSEQENDPARTSPED),C_DADOS.INSCRICAO, C_DADOS.PAIS, C_DADOS.ESTADO, C_DADOS.MUNICIPIO, C_DADOS.ENDERECO,
			  C_DADOS.NUMERO, C_DADOS.COMPLEMENTO, C_DADOS.BAIRRO, C_DADOS.PARTICIPANTE);
	      V_PARTICIPANTE := C_DADOS.PARTICIPANTE;
	      V_DSCODPART    := C_DADOS.DSCODPART;
	   ELSE
	      UPDATE TMP_ENDPARTSPED
		     SET PARTICIPANTE = PARTICIPANTE || '_' || C_DADOS.PARTICIPANTE
		   WHERE NRSEQENDPARTSPED = TO_NUMBER(V_NRSEQENDPARTSPED);
		  V_PARTICIPANTE := V_PARTICIPANTE || '_' || C_DADOS.PARTICIPANTE;
	   END IF;

   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('GRAVA_ENDPART_SPED_ORCLANT','0.00.000'); END;
/