Paste code

CREATE MULTISET TABLE .....................T1_VPC_SUNAT_RENIEC_HIST AS
(

							    SELECT DISTINCT A.FECCARGA, A.NUMRUC, RAZONSOCIAL ,TIPCONTRIBUYENTE,CIIU,UBIGEO,CONDICIONDOMICILIO,ESTADOCONTRIBUYENTE, FECALTA,FECBAJA,CONTABILIDAD,TIPPERSONA,FACTURACION,
								COMERCIOEXTERIOR,A.CODDOC,ESTADO,TIENEDIR,TIENETELEF,RANGOVENTAS, TIPDOCRELE,ESTADORELE,ESTADOTRIBUTO , 
								MONTODEUDATRIBUTO,
								NUMTRABAJADORES,
								  CODDOCRELE, B.FEC_NACIMIENTO,B.EST_CIVIL,
				                   CASE WHEN EST_CIVIL = &#039;D&#039; THEN 1 ELSE 0 END EST_CIVIL_DIVORCIADO,
								   CASE WHEN EST_CIVIL = &#039;S&#039; THEN 1 ELSE 0 END EST_CIVIL_SOLTERO,
								   CASE WHEN EST_CIVIL =&#039;C&#039; THEN 1 ELSE 0 END EST_CIVIL_CASADO,
								   CASE WHEN EST_CIVIL = &#039;V&#039; THEN 1 ELSE 0 END EST_CIVIL_VIUDO,
				                  GENERO ,
								  CASE WHEN GENERO = &#039;F&#039; THEN 1 ELSE 0 END EST_CIVIL_FEMENINO,
								  CASE WHEN GENERO = &#039;M&#039; THEN 1 ELSE 0 END EST_CIVIL_MASCULINO,
								  CASE WHEN DON_ORGANOS = &#039;SI&#039; THEN 1 ELSE 0 END DON_ORGANOS,
								  CASE WHEN DEP_UBI_DOMICILIO IN ( &#039;LIMA&#039;  , &#039;CALLAO&#039;  ) THEN &#039;LIMA y CALLAO&#039;        
								              WHEN DEP_UBI_DOMICILIO IN ( &#039;AMAZONAS&#039;, &#039;ANCASH&#039; ,&#039;APURIMAC&#039; , &#039;AREQUIPA&#039;, &#039;AYACUCHO&#039; , &#039;CAJAMARCA&#039;, &#039;CUSCO&#039;,  &#039;HUANCAVELICA&#039; , &#039;HUANUCO&#039; , &#039;ICA&#039;,&#039;JUNIN&#039;, 
											                                                            &#039;LA LIBERTAD&#039; , &#039;LAMBAYEQUE&#039;, &#039;LORETO&#039; , &#039;MADRE DE DIOS&#039;, &#039;MOQUEGUA&#039;, &#039;PASCO&#039; , &#039;PIURA&#039;, &#039;PUNO&#039;, &#039;SAN MARTIN&#039;, &#039;TACNA&#039;, &#039;TUMBES&#039; , &#039;UCAYALI&#039;)  THEN &#039;PROVINCIA&#039; 
																										ELSE &#039;EXTRANJERO&#039; END LUGAR_RESIDENCIA,
								  CASE WHEN DEP_UBI_DOMICILIO IN ( &#039;LIMA&#039;  , &#039;CALLAO&#039;  ) THEN 1 ELSE 0 END RESIDENCIA_LIMA_CALLAO,
								  CASE WHEN DEP_UBI_DOMICILIO IN ( &#039;AMAZONAS&#039;, &#039;ANCASH&#039; ,&#039;APURIMAC&#039; , &#039;AREQUIPA&#039;, &#039;AYACUCHO&#039; , &#039;CAJAMARCA&#039;, &#039;CUSCO&#039;,  &#039;HUANCAVELICA&#039; , &#039;HUANUCO&#039; , &#039;ICA&#039;,&#039;JUNIN&#039;, 
											                                                            &#039;LA LIBERTAD&#039; , &#039;LAMBAYEQUE&#039;, &#039;LORETO&#039; , &#039;MADRE DE DIOS&#039;, &#039;MOQUEGUA&#039;, &#039;PASCO&#039; , &#039;PIURA&#039;, &#039;PUNO&#039;, &#039;SAN MARTIN&#039;, &#039;TACNA&#039;, &#039;TUMBES&#039; , &#039;UCAYALI&#039;)  THEN 1 ELSE 0 END RESIDENCIA_PROVICNCIA,
								   CASE WHEN DEP_UBI_DOMICILIO NOT IN (&#039;LIMA&#039;  , &#039;CALLAO&#039;, &#039;AMAZONAS&#039;, &#039;ANCASH&#039; ,&#039;APURIMAC&#039; , &#039;AREQUIPA&#039;, &#039;AYACUCHO&#039; , &#039;CAJAMARCA&#039;, &#039;CUSCO&#039;,  &#039;HUANCAVELICA&#039; , &#039;HUANUCO&#039; , &#039;ICA&#039;,&#039;JUNIN&#039;, 
											                                                            &#039;LA LIBERTAD&#039; , &#039;LAMBAYEQUE&#039;, &#039;LORETO&#039; , &#039;MADRE DE DIOS&#039;, &#039;MOQUEGUA&#039;, &#039;PASCO&#039; , &#039;PIURA&#039;, &#039;PUNO&#039;, &#039;SAN MARTIN&#039;, &#039;TACNA&#039;, &#039;TUMBES&#039; , &#039;UCAYALI&#039;)  THEN 1 ELSE 0 END RESIDENCIA_EXTRANJERA,
								 ( CURRENT_DATE - B.FEC_NACIMIENTO) /360  EDAD_RRLL
							   FROM ...............V_CONSOLIDADO_SUNAT_HIST A
								LEFT JOIN  ...............V_MAESTRA_RENIEC  B  ON ( CASE WHEN A.NUMRUC LIKE &#039;10%&#039;   THEN A.CODDOC ELSE A.CODDOCRELE END) = B.CODDOC
							    WHERE 1 = 1
								AND ESTADORELE = 0
								AND (
								      NUMRUC in ( SELECT Nro_Documento from ...............V_VPC_SEGMENTACION_MERC WHERE BANCA_FIN_VAL IN (&#039;BPE&#039;, &#039;BE&#039;, &#039;BI&#039;, &#039;INMB&#039;, &#039;BC&#039;))
							          OR
							          A.CODDOC  in (SELECT Nro_Documento from ...............V_VPC_SEGMENTACION_MERC WHERE BANCA_FIN_VAL IN (&#039;BPE&#039;, &#039;BE&#039;, &#039;BI&#039;, &#039;INMB&#039;, &#039;BC&#039;))
							    )
								AND FECCARGA = MES_A_COPIAR


) WITH DATA AND STATS;