-- Create table
create table adam.fco_pi_pens_rt2
(
    compania char(4) NOT NULL,
    anio number NOT NULL,
    mes number NOT NULL,
    sucursal varchar2(10) NOT NULL,
    trabajador char(10) NOT NULL,
    -- Variables de novedades generales
    secuencia char(7) NOT NULL,
    -- Datos del Pensionado
    pens_ape1 varchar2(20) NOT NULL,
    pens_ape2 varchar2(30) NOT NULL,
    pens_nom1 varchar2(20) NOT NULL,
    pens_nom2 varchar2(30) NOT NULL,
    pens_tdoc char(2) NOT NULL,
    pens_id varchar2(16) NOT NULL,
    tipo_pension number NOT NULL,
    pension_compartida char(1) NOT NULL,
    -- Datos del Causante
    caus_ape1 varchar2(20) NOT NULL,
    caus_ape2 varchar2(30) NOT NULL,
    caus_nom1 varchar2(20) NOT NULL,
    caus_nom2 varchar2(30) NOT NULL,
    caus_tdoc char(2) NOT NULL,
    caus_id varchar2(16) NOT NULL,
    tipo_pensionado char(1) NOT NULL,
    residente_en_exterior char(1) NOT NULL,
    departamento char(2) NOT NULL,
    municipio char(3) NOT NULL,    
    situa_en_exterior char(1) NOT NULL,
    -- (Novedades)
    nov_ING char(1) NOT NULL,
    nov_RET char(1) NOT NULL,
    nov_TDE char(1) NOT NULL,
    nov_TAE char(1) NOT NULL,
    nov_TDP char(1) NOT NULL,
    nov_TAP char(1) NOT NULL,
    nov_VSP char(1) NOT NULL,
    nov_SUS char(1) NOT NULL,
    -- (Fin Novedades)
    cod_AFP char(10) NOT NULL,
    cod_AFP_Tras varchar2(6) NOT NULL,
    cod_EPS char(10) NOT NULL,
    cod_EPS_Tras varchar2(6) NOT NULL,
    cod_CCF char(10) NOT NULL,
    dias_AFP number NOT NULL,
    dias_FSP number NOT NULL,
    dias_EPS number NOT NULL,
    dias_CCF number NOT NULL,
    mesada number(9) NOT NULL,
    -- Sistema General de Pensiones
    tarifa_AFP number(6,5) NOT NULL,
    aporte_AFP number(9) NOT NULL,
    voluntario_AFP number(9) NOT NULL,
    aporte_total_AFP number(9),
    valor_no_retenido number(9) NOT NULL,
    -- Fondo de Solidaridad Pensional
    aporte_FSP_sol number(9) NOT NULL,
    aporte_FSP_sub number(9) NOT NULL,
    tarifa_FSP number(6,5) NOT NULL,
    -- Sistema General de EPS
    tarifa_EPS number(6,5) NOT NULL,
    aporte_EPS number(9) NOT NULL,
    UPC_adicional number(9) NOT NULL,
    -- Cajas de Compensación Familiar
    tarifa_CCF number(6,5) NOT NULL,
    aporte_CCF number(9) NOT NULL,
    origen number NOT NULL, -- 1 Normal, 2 Fantasma, 3 Editada manualmente
    eliminada number(1) DEFAULT 0 NOT NULL 
)
tablespace TS_ADAMDATOS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table fco_pi_pens_rt2
  add constraint PK_fco_pi_pens_rt2 primary key (compania, anio, mes, sucursal, trabajador)
  using index 
  tablespace TS_ADAMINDICES
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
