ALTER TABLE AB03B10T00 ADD (EXP_ARCHIVADO_010 NUMBER(8,0) default 0);
COMMENT ON COLUMN AB03B.AB03B10T00.EXP_ARCHIVADO_010 IS '0:Sin marcar, 1:Marcada para archivar, 2:Archivado, 3:Error al archivar';

CREATE OR REPLACE VIEW AB03B10V00ARCHIVO (
IDEXPEDIENTE, ID010, IDANO010, IDPROV010, NIFTITULAR010, NOMBRETITULAR010, APE1TITULAR010, APE2TITULAR010, ID047EMPRESA, NIFEMPRESA047, NOMBREEMPRESA047,
ESTADOEXP010, EXPCERRADO010, OIDDOCVISITA010, OIDDOCSANCION010, EXPARCHIVADO010) AS
SELECT 
T10.ID_PROV_010||'/'||T10.ID_ANO_010||'/'||LPAD(T10.ID_010,5,'0'),
T10.ID_010,
T10.ID_ANO_010,
T10.ID_PROV_010,
T10.NIF_TITULAR_010,
T10.NOMBRE_TITULAR_010,
T10.APE1_TITULAR_010,
T10.APE2_TITULAR_010,
T10.ID_047_EMPRESA,
T47.NIF_EMPRESA_047,
T47.NOMBRE_EMPRESA_047,
T10.ESTADO_EXP_010,
T10.EXP_CERRADO_010,
T10.OID_DOC_VISITA_010,
T10.OID_DOC_SANCION_010,
T10.EXP_ARCHIVADO_010
FROM AB03B10T00 T10
INNER JOIN AB03B47T00 T47 ON T47.ID_047 = T10.ID_047_EMPRESA
WHERE T10.EXP_CERRADO_010 = 'S' AND T10.EXP_ARCHIVADO_010=0;


CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B15V00ARCHIVO" ("ID", "IDEXPEDIENTE", "IDPROV", "IDANO", "IDNUMEXP", "ORIGEN", "DESCDOCEXP", 
"DESEDOCEXP", "OID", "IDPIF", "ORGDOCENTREGADO", "FECDOCENTREGADO", "NOMDOCENTREGADO", "IDDOCENTRADA", "IDTPDOCDOKUSI", "TIPODOCDOKUSI", "DESCTIPODOCDOKUSI",
"MOTIVORECHAZO") AS 
  (select t15.id_015, T15.ID_PROV_010||'/'||T15.ID_ANO_010||'/'||T15.ID_010_NUM_EXP, T15.ID_PROV_010, T15.ID_ANO_010, T15.ID_010_NUM_EXP,
'CIUDADANO' origen, T11.DESC_ES_011, T11.DESC_EU_011, T15.OID_015, T15.ID_PIF_015, T15.ORG_DOC_ENTREGADO_015,
T15.FEC_DOC_ENTREGADO_015, T15.NOM_DOC_ENTREGADO_015, T15.ID_011_DOCENTRADA, 
T11.ID_007_TP_DOC_DOKUSI, T07.DESC_ES_007, T07.DESC_EU_007, T15.MOTIVO_RECHAZO_015
FROM AB03B15T00 t15
INNER JOIN AB03B11T00 t11  ON T15.ID_011_DOCENTRADA = t11.id_011
INNER JOIN AB03B07T00 T07 ON T07.ID_007 = T11.ID_007_TP_DOC_DOKUSI
WHERE (T15.MOTIVO_RECHAZO_015 IS NULL OR T15.MOTIVO_RECHAZO_015 != '')
UNION
select t15.id_015, T15.ID_PROV_010||'/'||T15.ID_ANO_010||'/'||T15.ID_010_NUM_EXP, T15.ID_PROV_010, T15.ID_ANO_010, T15.ID_010_NUM_EXP,
'ADMINISTRACION' origen, T13.DESC_ES_013, T13.DESC_EU_013, T15.OID_015, T15.ID_PIF_015, T15.ORG_DOC_ENTREGADO_015,
T15.FEC_DOC_ENTREGADO_015, T15.NOM_DOC_ENTREGADO_015, T15.ID_011_DOCENTRADA,
T13.ID_007_TP_DOC_DOKUSI, T07.DESC_ES_007, T07.DESC_EU_007, T15.MOTIVO_RECHAZO_015
FROM AB03B15T00 t15
INNER JOIN AB03B13T00 t13  ON T15.ID_013_TIPO_DOC_G= t13.id_013
INNER JOIN AB03B07T00 T07 ON T07.ID_007 = T13.ID_007_TP_DOC_DOKUSI
WHERE (T15.MOTIVO_RECHAZO_015 IS NULL OR T15.MOTIVO_RECHAZO_015 != ''));
