select OPE_SLP_CONCEPTO_FACT.CODEMP, OPE_SLP_CONCEPTO_FACT.CODLOC, OPE_SLP_CONCEPTO_FACT.ANIO,
       pub_tipo_slp.nom_slp, right('00000'||OPE_SLP_CONCEPTO_FACT.NRO_SLP,5) as nro_slp, OPE_SLP_CONCEPTO_FACT.ITEM,
       OPE_SLP_CONCEPTO_FACT.ID_PERSONA, PUB_PERSONAS.RAZON_SOC, OPE_SLP_CONCEPTO_FACT.PUNTO_VENTA,
       OPE_SLP_CONCEPTO_FACT.PRO_CODIGO, OPE_SLP_CONCEPTO_FACT.MONEDA, OPE_SLP_CONCEPTO_FACT.CANTIDAD,
       OPE_SLP_CONCEPTO_FACT.COSTO_UNITARIO, OPE_SLP_CONCEPTO_FACT.COSTO_REAL, OPE_SLP_CONCEPTO_FACT.MONTO_FACTURA,
       OPE_SLP_CONCEPTO_FACT.AFECTO_A_IGV, OPE_SLP_CONCEPTO_FACT.ID_GRUPO, OPE_SLP_CONCEPTO_FACT.ESTADO,
       OPE_SLP_CONCEPTO_FACT.COD_UNIDAD, OPE_SLP_CONCEPTO_FACT.FLAG_PLANTA, OPE_SLP_CONCEPTO_FACT.TIPO_DOC,
       OPE_SLP_CONCEPTO_FACT.FLAG_COBRANZA, OPE_SLP_CONCEPTO_FACT.TIPDOC_REF, OPE_SLP_CONCEPTO_FACT.SERDOC_REF,
       OPE_SLP_CONCEPTO_FACT.NUMDOC_REF, OPE_SLP_CONCEPTO_FACT.FECDOC_REF, OPE_SLP_CONCEPTO_FACT.TIPCAM_REF,
       OPE_SLP_CONCEPTO_FACT.OBSERVACION,
       (case OPE_SLP_CONCEPTO_FACT.FLAG_PASE
       when 0 then 'Prefacturado'
       when 1 then 'C&P'
       when 2 then 'Facturado' end) as estado_facturacion
       ,
        OPE_SLP_CONCEPTO_FACT.FEC_REG as fecha_registro,
       OPE_SLP_CONCEPTO_FACT.USU_REG as usuario_registro, OPE_SLP_CONCEPTO_FACT.FEC_MOD, OPE_SLP_CONCEPTO_FACT.USU_MOD,
       OPE_SLP_CONCEPTO_FACT.SERIE_DOC, OPE_SLP_CONCEPTO_FACT.NUMERO_DOC, OPE_SLP_CONCEPTO_FACT.FECHA_DOC as fecha_facturacion,
       OPE_SLP_CONCEPTO_FACT.ENDOSADO_BL, OPE_SLP_CONCEPTO_FACT.ID_CENCOS, OPE_SLP_CONCEPTO_FACT.ORDEN_PEDIDO,
       OPE_SLP_CONCEPTO_FACT.CONFORMIDAD, OPE_SLP_CONCEPTO_FACT.TIPCAMB,ope_slp.fec_zarpe,
(case ope_slp.tipo_servicio_comercial
       when '1' then 'LOGISTICO'
       when '2' then 'PRODUCCION'
       when '3' then 'FLETE'
       else 'OTROS' end ) tipo_servicio
from OPE_SLP_CONCEPTO_FACT
left join PUB_PERSONAS on (PUB_PERSONAS.CODEMP = OPE_SLP_CONCEPTO_FACT.CODEMP) and (PUB_PERSONAS.ID_PERSONA = OPE_SLP_CONCEPTO_FACT.ID_PERSONA)
left join ope_slp on (ope_slp.codemp=OPE_SLP_CONCEPTO_FACT.CODEMP and
ope_slp.codlocal=OPE_SLP_CONCEPTO_FACT.CODLOC and
ope_slp.anio=OPE_SLP_CONCEPTO_FACT.anio and
ope_slp.tipo_slp= OPE_SLP_CONCEPTO_FACT.tipo_slp and
ope_slp.nro_slp=OPE_SLP_CONCEPTO_FACT.nro_slp)
left join pub_tipo_slp on (pub_tipo_slp.tipo_slp=OPE_SLP_CONCEPTO_FACT.tipo_slp)
where (OPE_SLP_CONCEPTO_FACT.CODEMP = '08') and
      (OPE_SLP_CONCEPTO_FACT.CODLOC in ('01','02')) and
      (OPE_SLP_CONCEPTO_FACT.ANIO IN (2017,2018))