AnsweredAssumed Answered

pmo accelerator CA PPM 13.2 and 15.3

Question asked by RnFernandez on Dec 12, 2017
Latest reply on Dec 13, 2017 by Suman Pramanik

When we run> admin content csk... the following error appears:
Is there a field depreciation or some field that changed the type of data?

 

ERROR 2017-12-11 12:18:35,220 [http-bio-443-exec-2159] dal.NSQLClient (clarity:XXXXXXX:25726153__03A75451-808F-4B63-85F7-4A590003D4A5:npt.overview) Unable to retrieve NSQL cube.  NSQLException was thrown.
com.niku.union.persistence.nsql.NSQLException: com.niku.union.persistence.PersistenceException:
SQL error code: 1722
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01722: invalid number

Executed:
select * from (select row_number() over ( order by codigo  asc) row_num, count(*) over () num_rows, q.* from ( SELECT
t.inv_id||t.obs_id id,
inv.id inv_id,
inv.code codigo,
inv.name nombre,

gestor.full_name manager_n,
gestor.user_id manager_id,
tim.full_name tim_n,
tim.user_id tim_id,
RM.full_name rm_n,
rm.id rm_id,

INV3.sb_tipo_proyecto tipo_proyecto_id,
L1.name tipo_proyecto,
inv.created_date fecha_alta,
inv4.sb_req_procesos req_procesos_id,
case when inv4.sb_req_procesos IS NULL THEN 'Sin Def' when
inv4.sb_req_procesos = 1 then 'Si' else 'No' end req_procesos,

case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then
'Cancelado' else ( case when clv8.name='Cerrado' then clv8.name else ( case when inv3.sb_suspendido = 1 then 'Suspendido' else
clv8.name end) end) end estado_sb,
case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then 7 else (
case when clv8.name='Cerrado' then to_number(inv.stage_code) else (case when inv3.sb_suspendido = 1 then 8 else
to_number(inv.stage_code) end ) end ) end estado_sb_id,
AUD.cerrado_date cerrado_date,

t.obs_id obs_id,
t.link_proy link_proy,
t.link_app link_app,
t.obs_name||t.obs_desc obs_desc

from (
  /******************************************** Buscar observaciones  a nivel de proyecto************************************************ */
 
 
select 1 obs_id, 'FALTA DATO: ' obs_name,
      invx.id inv_id, invx.code,  
      'Ver' link_proy, '' link_app,
      case when INVx.GOAL_CODE             is null then '- Objetivo'    else '' end ||
      case when (inv3x.sb_rm is null and  inv3x.sb_tipo_proyecto != 'tipo05'
             and invx.code != 'RESERVA_REGULATORIO' and inv4x.sb_req_procesos = 0   )
             then ' - RM '        else '' end ||
      case when (INVx.manager_id is null and invx.code != 'RESERVA_REGULATORIO' )
             then ' - Gestor '        else '' end ||
      case when inv3x.sb_tipo_proyecto   is null then ' - Tipo Proy ' else '' end    obs_desc
   from INV_INVESTMENTS invx
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id
    where  invx.is_active = 1  
     and    INVx.stage_code  != 6                   /* No Cerrado*/
     and   (INVx.GOAL_CODE is null
           or (inv3x.sb_rm  is null   and  inv3x.sb_tipo_proyecto != 'tipo05'   /* RM no soporte*/
               and invx.code != 'RESERVA_REGULATORIO' and inv4x.sb_req_procesos = 0)  
           or (INVx.manager_id  is null  and invx.code != 'RESERVA_REGULATORIO')   
           or inv3x.sb_tipo_proyecto is null )
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select  2 obs_id, 'DT Cerrada con Tareas NO finalizadas (AJUSQA)' obs_name,
         invx.id inv_id,  invx.code,
      'Ver' link_proy, '' link_app,
         ' - Cant. Tareas: ' ||tx.k_no_finaliz     obs_desc
    from INV_INVESTMENTS INVx  
          left outer join    (
              select tsk1.prprojectid inv_id,  count(*)  k_no_finaliz
                from PRTASK TSK1     where TSK1.prStatus !=  2  and   TSK1.prName = 'Horas Ajuste QA'           /* No Finalizado    */
                 group by tsk1.prprojectid
                     )  tx on invx.id = tx.inv_id   
      where  invx.is_active = 1
         and  INVx.stage_code  = 6                                             /* Cerrado*/
         and  tx.k_no_finaliz > 0                                                   /* tareas no Finalizadas > 0    */
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select  3 obs_id, 'Centro Costo Proy NO SUMA 100' obs_name,
         invx.id inv_id,  invx.code,
      'Ver' link_proy, '' link_app,
         ' - Suma: ' ||NVL(CC_2.sum_cc,0)     obs_desc
    from INV_INVESTMENTS INVx  
        left outer join
         (select odf_parent_id,
                 round(SUM(NVL(sb_prcentaje,0)),0) sum_cc
          from
          (select id, odf_parent_id, sb_prcentaje, sb_cencosto
           from ODF_CA_SB_CENCOSTO_PAGADOR4  /*Proyecto*/
           ) CC
          group by odf_parent_id) CC_2
          on INVx.id = CC_2.odf_parent_id
      where  invx.is_active = 1
        and  INVx.odf_object_code = 'project'
        and  INVx.is_open_for_te = 1
        and  NVL(CC_2.sum_cc,0)  != 100         /* SUMA DISTINTO 100    */
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select 4 obs_id,  'Centro Costo App NO SUMA 100' obs_name,
         invx.id inv_id,  invx.code,
      '' link_proy, 'Ver' link_app,
         ' - Suma: ' ||NVL(CC_2.sum_cc,0)     obs_desc
    from INV_INVESTMENTS INVx  
        left outer join
         (select odf_parent_id,
                 round(SUM(NVL(sb_prcentaje,0)),0) sum_cc
          from
          (select id, odf_parent_id, sb_prcentaje, sb_cencosto
           from ODF_CA_SB_CENCOSTO_PAGADOR3  /*Aplicacion*/
           ) CC
          group by odf_parent_id) CC_2
          on INVx.id = CC_2.odf_parent_id
      where  invx.is_active = 1
        and  INVx.odf_object_code = 'application'
        and  INVx.is_open_for_te = 1
        and  NVL(CC_2.sum_cc,0)  != 100         /* SUMA DISTINTO 100    */
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select 5 obs_id, 'RM inactivo' obs_name,
      invx.id inv_id, invx.code,
      'Ver' link_proy, '' link_app,
       RMx.full_name  obs_desc
   from INV_INVESTMENTS invx
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id
   left outer join ODF_CA_PROJECT INV4x     on INVx.id = INV4x.id
   left outer join SRM_RESOURCES RMx    on INV3x.sb_rm = RMx.id
    where  invx.is_active = 1
     and   RMx.is_active = 0                 /*RM inactivo */  
     and   INVx.stage_code  != 6                /* No Cerrado*/
     and   (inv3x.sb_rm  is null   and  inv3x.sb_tipo_proyecto != 'tipo05'   /* RM no soporte*/
              and invx.code != 'RESERVA_REGULATORIO' and inv4x.sb_req_procesos = 0)   
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select 6 obs_id, 'Obligatorio NO Proyecto' obs_name,
      invx.id inv_id, invx.code,
      'Ver' link_proy, '' link_app,
        ''     obs_desc
   from INV_INVESTMENTS invx
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id
    where  invx.is_active = 1    and  INVx.odf_object_code = 'project'
     and   invx.IS_REQUIRED = 1                 /* Obligatorio  */
     and   inv3x.sb_tipo_proyecto != 'tipo01'   /* No Proyecto*/
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select 7 obs_id, 'Gestor Inactivo - ' obs_name,
      invx.id inv_id, invx.code,
      'Ver' link_proy, '' link_app,
       GESTORx.full_name  obs_desc
   from INV_INVESTMENTS invx
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id
   left outer join ODF_CA_PROJECT INV4x     on INVx.id = INV4x.id
   left outer join SRM_RESOURCES GESTORx    on INVx.manager_id = GESTORx.user_id
    where  invx.is_active = 1
     and   GESTORx.is_active = 0                 /*GESTOR inactivo */  
     and   INVx.stage_code  != 6                /* No Cerrado*/
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select 8 obs_id, 'Marca Watchlist vs Categoria inconsistente - ' obs_name,
      invx.id inv_id, invx.code,
      'Ver' link_proy, '' link_app,
        inv4x.sb_cat_watchlist  obs_desc
   from INV_INVESTMENTS invx
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'
     and   invx.IS_REQUIRED = 1                         /* Obligatorio  */
     and   INVx.stage_code  != 6 and INVx.stage_code  != 5              /* No Cerrado - No post-implem*/
     and  (  
       (inv4x.sb_es_watchlist = 0                    /* no está en la watchlist*/
           and  inv4x.sb_cat_watchlist is not null)      /* tiene categ watchlist*/
    or (inv4x.sb_es_watchlist = 1                    /* está en la watchlist*/
           and  inv4x.sb_cat_watchlist is null)   )   /* no tiene categ watchlist*/  
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select 9 obs_id, 'Falta MARCA Watchlist: ' obs_name,
      invx.id inv_id, invx.code,
      'Ver' link_proy, '' link_app,
        case when inv3x.sb_cat_proy = 'SMP-DR' then 'DR-1 ' else '' end ||
        case when inv3x.sb_clasific_proy = 'A-EX' or inv3x.sb_clasific_proy = 'B-ES' then 'Estrateg  ' else '' end ||
        case when INVx.GOAL_CODE = 'REGULATORY' then 'REG' else '' end ||
        case when inv3x.presup_inv_total >= 1500  then 'Presup > 1,5K' else '' end
             obs_desc
   from INV_INVESTMENTS invx
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'
     and   invx.IS_REQUIRED = 1                         /* Obligatorio  */
     and   inv3x.sb_suspendido = 0                                                         /* no está suspendido*/
     and   INVx.stage_code  != 6 and INVx.stage_code  != 5                   /* No Cerrado - No post-implem*/
     and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
     and   (inv4x.sb_es_watchlist = 0                    /* no está en la watchlist*/
           or  inv4x.sb_cat_watchlist is null     )       /* no tiene categ watchlist*/  
     and   (
                 inv3x.sb_cat_proy = 'SMP-DR'         /*categorias a subir a la watchlist*/

              or inv3x.sb_clasific_proy = 'A-EX'       /*clasificacion icbc EXCO */
              or inv3x.sb_clasific_proy = 'B-ES'       /*clasificacion icbc ESTREGICO */
              or INVx.GOAL_CODE = 'REGULATORY'        /*REGULATORIO a subir a la watchlist*/
              or inv3x.presup_inv_total >= 1500      /* controla presupuesto total*/  
             )
)
/* CONTROLA PARA HACER PQA: Susp-Cerrado-Post_Impl-Canc QUE TENGAN CATEGORIA WATCHLIST */
UNION ( select 10 obs_id, 'Falta CATEGORIA Watchlist: ' obs_name,
      invx.id inv_id, invx.code,
      'Ver' link_proy, '' link_app,
        case when inv3x.sb_cat_proy = 'SMP-DR' then 'DR-1  ' else '' end ||
        case when inv3x.sb_clasific_proy = 'A-EX' or inv3x.sb_clasific_proy = 'B-ES' then 'Estrateg  ' else '' end ||
        case when INVx.GOAL_CODE = 'REGULATORY' then 'REG'           else '' end ||
        case when inv3x.presup_inv_total >= 1500  then 'Presup > 1,5K'    else '' end
             obs_desc
   from INV_INVESTMENTS invx
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'
     and   invx.IS_REQUIRED = 1                         /* Obligatorio  */
     and   (
              inv3x.sb_suspendido = 1                                                           /* está suspendido*/
              or   INVx.stage_code  = 6     or  INVx.stage_code  = 5              /* Cerrado - post-implem*/
              or   NVL(INVx.status,0)  = 3  or  NVL(INVx.status,0)  = 4           /* Canc - Cancel (o Rechazada)*/
             )
     and   inv4x.sb_cat_watchlist is null                    /* no tiene categ watchlist*/  
     and   (
                 inv3x.sb_cat_proy = 'SMP-DR'                   /*categorias a subir a la watchlist*/
              or inv3x.sb_clasific_proy = 'A-EX'                 /*clasificacion icbc EXCO */
              or inv3x.sb_clasific_proy = 'B-ES'                 /*clasificacion icbc ESTREGICO */
              or INVx.GOAL_CODE = 'REGULATORY'        /*REGULATORIO a subir a la watchlist*/
              or   inv3x.presup_inv_total >= 1500               /* controla presupuesto total*/
             )
)

UNION ( select 11 obs_id, 'Categoría Watchlist No corresponde ' obs_name,
      invx.id inv_id, invx.code,
      'Ver' link_proy, '' link_app
        case when inv4x.sb_cat_watchlist = 6 then 'DR-1'   else '' end ||
        case when inv4x.sb_cat_watchlist = 7 then 'REG'      else '' end ||
        case when inv4x.sb_cat_watchlist = 8 then 'Estrateg' else '' end
              obs_desc

   from INV_INVESTMENTS invx
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'
     and   INVx.stage_code  != 6 and INVx.stage_code  != 5              /* No Cerrado - No post-implem*/
     and (  
             (inv4x.sb_cat_watchlist = 6  and  NVL(inv3x.sb_cat_proy,' ') != 'SMP-DR')   /* categ watchlist DR-1*/

         or (inv4x.sb_cat_watchlist = 8   and  

             ( NVL(inv3x.sb_clasific_proy,' ') != 'A-EX' and  NVL(inv3x.sb_clasific_proy,' ') != 'B-ES'))  /* categ watchlist STRATEG*/

         or (inv4x.sb_cat_watchlist = 7   and  NVL(INVx.GOAL_CODE,' ') != 'REGULATORY')   /* categ watchlist REGU*/
         )
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/
)

UNION ( select 12 obs_id, 'Completar Año Plan Anual Cerrado / Canc' obs_name,
      invx.id inv_id, invx.code,
      'Ver' link_proy, '' link_app,
        ''     obs_desc
   from INV_INVESTMENTS invx
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'
     and   ( INVx.stage_code  = 6 or INVx.status=3 or INVx.status=4 )             /* Cerrado - o cancelado*/
     and   NVL(invx.IS_REQUIRED,0) = 1             /* es Obligatorio, está en Plan */
     and   NVL(INV3x.sb_ano_plananual,0) = 0     /* Falta completar año*/

)



) t
 left outer join INV_INVESTMENTS INV     on t.inv_id = INV.id
 left outer join INV_PROJECTS INV2       on INV.id = INV2.prid   
 left outer join ODF_CA_INV INV3         on INV.id = INV3.id
 left outer join ODF_CA_PROJECT INV4     on INV.id = INV4.id

/* Tabla con Manager, Gestor - TIM -RM */
   left outer join SRM_RESOURCES GESTOR       on INV.manager_id = gestor.user_id
   left outer join SRM_RESOURCES TIM          on inv3.tim_id = TIM.user_id
   left outer join SRM_RESOURCES RM           on INV3.sb_rm = RM.id

/* Tabla con Etapa - Estado */
       left outer join cmn_lookups_v CLV8 ON (CLV8.language_code = ?
           and INV.stage_code = CLV8.lookup_code       and CLV8.lookup_type = 'INV_STAGE_TYPE')
       left outer join cmn_lookups_v CLV6 ON (CLV6.language_code = ?
           and INV.status = CLV6.lookup_enum                and CLV6.lookup_type = 'INVESTMENT_OBJ_STATUS')

/* Tabla con etiquetas de Tipo Proyecto */
          left outer join CMN_LOOKUPS_V L1     on ( L1.language_code = ?
              and INV3.sb_tipo_proyecto = L1.lookup_code       and L1.lookup_type = 'SB_TIPO_PROYECTO')


/* Fecha de Cerrado o Cancelado*/
        LEFT OUTER JOIN
          (select distinct  audx.OBJECT_ID,   MAX(audx.LAST_UPDATED_DATE)  cerrado_date
            from CMN_AUDITS audx
            where audx.OBJECT_CODE = 'project'
             and   audx.TABLE_NAME  = 'inv_investments'
             and   (
                   (audx.COLUMN_NAME = 'STATUS'      and   audx.RAW_VALUE_AFTER  = 3)
                or (audx.COLUMN_NAME = 'STATUS'      and   audx.RAW_VALUE_AFTER  = 4)
                or (audx.COLUMN_NAME = 'STAGE_CODE'   and   audx.RAW_VALUE_AFTER  = 6)  
            ) GROUP BY audx.OBJECT_ID )  AUD  on INV.id=AUD.OBJECT_ID


WHERE  NVL(inv2.is_template,0) = 0 and NVL(inv2.is_program,0) = 0
      and    1=? and 1=1 ) q) q where q.row_num between ? and ? order by q.row_num
Derived from statement:
<?xml version="1.0" encoding="UTF-8"?>
<statement id="sb_valida_dt_pyc:5088000" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="map" xmlns="http://schemas.niku.com/2002/pmd">
  <sql dbVendor="all">
    <text>SELECT&#xd;
t.inv_id||t.obs_id id,&#xd;
inv.id inv_id,&#xd;
inv.code codigo,&#xd;
inv.name nombre,&#xd;
&#xd;
gestor.full_name manager_n,&#xd;
gestor.user_id manager_id,&#xd;
tim.full_name tim_n,&#xd;
tim.user_id tim_id,&#xd;
RM.full_name rm_n,&#xd;
rm.id rm_id,&#xd;
&#xd;
INV3.sb_tipo_proyecto tipo_proyecto_id,&#xd;
L1.name tipo_proyecto,&#xd;
inv.created_date fecha_alta,&#xd;
inv4.sb_req_procesos req_procesos_id,&#xd;
case when inv4.sb_req_procesos IS NULL THEN 'Sin Def' when &#xd;
inv4.sb_req_procesos = 1 then 'Si' else 'No' end req_procesos,&#xd;
&#xd;
case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then &#xd;
'Cancelado' else ( case when clv8.name='Cerrado' then clv8.name else ( case when inv3.sb_suspendido = 1 then 'Suspendido' else &#xd;
clv8.name end) end) end estado_sb,&#xd;
case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then 7 else ( &#xd;
case when clv8.name='Cerrado' then to_number(inv.stage_code) else (case when inv3.sb_suspendido = 1 then 8 else &#xd;
to_number(inv.stage_code) end ) end ) end estado_sb_id,&#xd;
AUD.cerrado_date cerrado_date,&#xd;
&#xd;
t.obs_id obs_id,&#xd;
t.link_proy link_proy,&#xd;
t.link_app link_app,&#xd;
t.obs_name||t.obs_desc obs_desc&#xd;
&#xd;
from (&#xd;
  /******************************************** Buscar observaciones  a nivel de proyecto************************************************ */&#xd;
 &#xd;
 &#xd;
(  select 1 obs_id, 'FALTA DATO: ' obs_name,&#xd;
      invx.id inv_id, invx.code,  &#xd;
      'Ver' link_proy, '' link_app,&#xd;
      case when INVx.GOAL_CODE             is null then '- Objetivo'    else '' end ||&#xd;
      case when (inv3x.sb_rm is null and  inv3x.sb_tipo_proyecto != 'tipo05' &#xd;
             and invx.code != 'RESERVA_REGULATORIO' and inv4x.sb_req_procesos = 0   )&#xd;
             then ' - RM '        else '' end ||&#xd;
      case when (INVx.manager_id is null and invx.code != 'RESERVA_REGULATORIO' )&#xd;
             then ' - Gestor '        else '' end ||&#xd;
      case when inv3x.sb_tipo_proyecto   is null then ' - Tipo Proy ' else '' end    obs_desc&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id&#xd;
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id&#xd;
    where  invx.is_active = 1  &#xd;
     and    INVx.stage_code  != 6                   /* No Cerrado*/ &#xd;
     and   (INVx.GOAL_CODE is null&#xd;
           or (inv3x.sb_rm  is null   and  inv3x.sb_tipo_proyecto != 'tipo05'   /* RM no soporte*/ &#xd;
               and invx.code != 'RESERVA_REGULATORIO' and inv4x.sb_req_procesos = 0)  &#xd;
           or (INVx.manager_id  is null  and invx.code != 'RESERVA_REGULATORIO')   &#xd;
           or inv3x.sb_tipo_proyecto is null )&#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select  2 obs_id, 'DT Cerrada con Tareas NO finalizadas (AJUSQA)' obs_name,&#xd;
         invx.id inv_id,  invx.code,&#xd;
      'Ver' link_proy, '' link_app,&#xd;
         ' - Cant. Tareas: ' ||tx.k_no_finaliz     obs_desc&#xd;
    from INV_INVESTMENTS INVx  &#xd;
          left outer join    (&#xd;
              select tsk1.prprojectid inv_id,  count(*)  k_no_finaliz&#xd;
                from PRTASK TSK1     where TSK1.prStatus !=  2  and   TSK1.prName = 'Horas Ajuste QA'           /* No Finalizado    */&#xd;
                 group by tsk1.prprojectid&#xd;
                     )  tx on invx.id = tx.inv_id   &#xd;
      where  invx.is_active = 1 &#xd;
         and  INVx.stage_code  = 6                                             /* Cerrado*/ &#xd;
         and  tx.k_no_finaliz &gt; 0                                                   /* tareas no Finalizadas &gt; 0    */&#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select  3 obs_id, 'Centro Costo Proy NO SUMA 100' obs_name,&#xd;
         invx.id inv_id,  invx.code,&#xd;
      'Ver' link_proy, '' link_app,&#xd;
         ' - Suma: ' ||NVL(CC_2.sum_cc,0)     obs_desc&#xd;
    from INV_INVESTMENTS INVx  &#xd;
        left outer join &#xd;
         (select odf_parent_id, &#xd;
                 round(SUM(NVL(sb_prcentaje,0)),0) sum_cc&#xd;
          from &#xd;
          (select id, odf_parent_id, sb_prcentaje, sb_cencosto&#xd;
           from ODF_CA_SB_CENCOSTO_PAGADOR4  /*Proyecto*/&#xd;
           ) CC&#xd;
          group by odf_parent_id) CC_2&#xd;
          on INVx.id = CC_2.odf_parent_id&#xd;
      where  invx.is_active = 1 &#xd;
        and  INVx.odf_object_code = 'project'&#xd;
        and  INVx.is_open_for_te = 1&#xd;
        and  NVL(CC_2.sum_cc,0)  != 100         /* SUMA DISTINTO 100    */&#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select 4 obs_id,  'Centro Costo App NO SUMA 100' obs_name,&#xd;
         invx.id inv_id,  invx.code,&#xd;
      '' link_proy, 'Ver' link_app,&#xd;
         ' - Suma: ' ||NVL(CC_2.sum_cc,0)     obs_desc&#xd;
    from INV_INVESTMENTS INVx  &#xd;
        left outer join &#xd;
         (select odf_parent_id, &#xd;
                 round(SUM(NVL(sb_prcentaje,0)),0) sum_cc&#xd;
          from &#xd;
          (select id, odf_parent_id, sb_prcentaje, sb_cencosto&#xd;
           from ODF_CA_SB_CENCOSTO_PAGADOR3  /*Aplicacion*/&#xd;
           ) CC&#xd;
          group by odf_parent_id) CC_2&#xd;
          on INVx.id = CC_2.odf_parent_id&#xd;
      where  invx.is_active = 1 &#xd;
        and  INVx.odf_object_code = 'application'&#xd;
        and  INVx.is_open_for_te = 1&#xd;
        and  NVL(CC_2.sum_cc,0)  != 100         /* SUMA DISTINTO 100    */&#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select 5 obs_id, 'RM inactivo' obs_name,&#xd;
      invx.id inv_id, invx.code, &#xd;
      'Ver' link_proy, '' link_app,&#xd;
       RMx.full_name  obs_desc&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id&#xd;
   left outer join ODF_CA_PROJECT INV4x     on INVx.id = INV4x.id &#xd;
   left outer join SRM_RESOURCES RMx    on INV3x.sb_rm = RMx.id&#xd;
    where  invx.is_active = 1 &#xd;
     and   RMx.is_active = 0                 /*RM inactivo */  &#xd;
     and   INVx.stage_code  != 6                /* No Cerrado*/ &#xd;
     and   (inv3x.sb_rm  is null   and  inv3x.sb_tipo_proyecto != 'tipo05'   /* RM no soporte*/ &#xd;
              and invx.code != 'RESERVA_REGULATORIO' and inv4x.sb_req_procesos = 0)   &#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select 6 obs_id, 'Obligatorio NO Proyecto' obs_name,&#xd;
      invx.id inv_id, invx.code, &#xd;
      'Ver' link_proy, '' link_app,&#xd;
        ''     obs_desc&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id&#xd;
    where  invx.is_active = 1    and  INVx.odf_object_code = 'project'&#xd;
     and   invx.IS_REQUIRED = 1                 /* Obligatorio  */ &#xd;
     and   inv3x.sb_tipo_proyecto != 'tipo01'   /* No Proyecto*/ &#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select 7 obs_id, 'Gestor Inactivo - ' obs_name,&#xd;
      invx.id inv_id, invx.code, &#xd;
      'Ver' link_proy, '' link_app,&#xd;
       GESTORx.full_name  obs_desc&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id&#xd;
   left outer join ODF_CA_PROJECT INV4x     on INVx.id = INV4x.id &#xd;
   left outer join SRM_RESOURCES GESTORx    on INVx.manager_id = GESTORx.user_id&#xd;
    where  invx.is_active = 1 &#xd;
     and   GESTORx.is_active = 0                 /*GESTOR inactivo */  &#xd;
     and   INVx.stage_code  != 6                /* No Cerrado*/ &#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select 8 obs_id, 'Marca Watchlist vs Categoria inconsistente - ' obs_name,&#xd;
      invx.id inv_id, invx.code, &#xd;
      'Ver' link_proy, '' link_app,&#xd;
        inv4x.sb_cat_watchlist  obs_desc&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id&#xd;
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'&#xd;
     and   invx.IS_REQUIRED = 1                         /* Obligatorio  */ &#xd;
     and   INVx.stage_code  != 6 and INVx.stage_code  != 5              /* No Cerrado - No post-implem*/&#xd;
     and  (  &#xd;
       (inv4x.sb_es_watchlist = 0                    /* no está en la watchlist*/ &#xd;
           and  inv4x.sb_cat_watchlist is not null)      /* tiene categ watchlist*/&#xd;
    or (inv4x.sb_es_watchlist = 1                    /* está en la watchlist*/ &#xd;
           and  inv4x.sb_cat_watchlist is null)   )   /* no tiene categ watchlist*/  &#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select 9 obs_id, 'Falta MARCA Watchlist: ' obs_name,&#xd;
      invx.id inv_id, invx.code, &#xd;
      'Ver' link_proy, '' link_app,&#xd;
        case when inv3x.sb_cat_proy = 'SMP-DR' then 'DR-1 ' else '' end ||&#xd;
        case when inv3x.sb_clasific_proy = 'A-EX' or inv3x.sb_clasific_proy = 'B-ES' then 'Estrateg  ' else '' end ||&#xd;
        case when INVx.GOAL_CODE = 'REGULATORY' then 'REG' else '' end ||&#xd;
        case when inv3x.presup_inv_total &gt;= 1500  then 'Presup &gt; 1,5K' else '' end&#xd;
             obs_desc&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id&#xd;
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id&#xd;
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'&#xd;
     and   invx.IS_REQUIRED = 1                         /* Obligatorio  */ &#xd;
     and   inv3x.sb_suspendido = 0                                                         /* no está suspendido*/&#xd;
     and   INVx.stage_code  != 6 and INVx.stage_code  != 5                   /* No Cerrado - No post-implem*/&#xd;
     and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
     and   (inv4x.sb_es_watchlist = 0                    /* no está en la watchlist*/ &#xd;
           or  inv4x.sb_cat_watchlist is null     )       /* no tiene categ watchlist*/  &#xd;
     and   ( &#xd;
                 inv3x.sb_cat_proy = 'SMP-DR'         /*categorias a subir a la watchlist*/ &#xd;
&#xd;
              or inv3x.sb_clasific_proy = 'A-EX'       /*clasificacion icbc EXCO */&#xd;
              or inv3x.sb_clasific_proy = 'B-ES'       /*clasificacion icbc ESTREGICO */&#xd;
              or INVx.GOAL_CODE = 'REGULATORY'        /*REGULATORIO a subir a la watchlist*/&#xd;
              or inv3x.presup_inv_total &gt;= 1500      /* controla presupuesto total*/  &#xd;
             )&#xd;
)&#xd;
/* CONTROLA PARA HACER PQA: Susp-Cerrado-Post_Impl-Canc QUE TENGAN CATEGORIA WATCHLIST */&#xd;
UNION ( select 10 obs_id, 'Falta CATEGORIA Watchlist: ' obs_name,&#xd;
      invx.id inv_id, invx.code, &#xd;
      'Ver' link_proy, '' link_app,&#xd;
        case when inv3x.sb_cat_proy = 'SMP-DR' then 'DR-1  ' else '' end ||&#xd;
        case when inv3x.sb_clasific_proy = 'A-EX' or inv3x.sb_clasific_proy = 'B-ES' then 'Estrateg  ' else '' end ||&#xd;
        case when INVx.GOAL_CODE = 'REGULATORY' then 'REG'           else '' end ||&#xd;
        case when inv3x.presup_inv_total &gt;= 1500  then 'Presup &gt; 1,5K'    else '' end &#xd;
             obs_desc&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id&#xd;
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id&#xd;
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'&#xd;
     and   invx.IS_REQUIRED = 1                         /* Obligatorio  */ &#xd;
     and   (&#xd;
              inv3x.sb_suspendido = 1                                                           /* está suspendido*/&#xd;
              or   INVx.stage_code  = 6     or  INVx.stage_code  = 5              /* Cerrado - post-implem*/&#xd;
              or   NVL(INVx.status,0)  = 3  or  NVL(INVx.status,0)  = 4           /* Canc - Cancel (o Rechazada)*/&#xd;
             )&#xd;
     and   inv4x.sb_cat_watchlist is null                    /* no tiene categ watchlist*/  &#xd;
     and   ( &#xd;
                 inv3x.sb_cat_proy = 'SMP-DR'                   /*categorias a subir a la watchlist*/ &#xd;
              or inv3x.sb_clasific_proy = 'A-EX'                 /*clasificacion icbc EXCO */&#xd;
              or inv3x.sb_clasific_proy = 'B-ES'                 /*clasificacion icbc ESTREGICO */&#xd;
              or INVx.GOAL_CODE = 'REGULATORY'        /*REGULATORIO a subir a la watchlist*/ &#xd;
              or   inv3x.presup_inv_total &gt;= 1500               /* controla presupuesto total*/ &#xd;
             )&#xd;
)&#xd;
&#xd;
UNION ( select 11 obs_id, 'Categoría Watchlist No corresponde ' obs_name,&#xd;
      invx.id inv_id, invx.code, &#xd;
      'Ver' link_proy, '' link_app,&#xd;
        case when inv4x.sb_cat_watchlist = 6 then 'DR-1'   else '' end ||&#xd;
        case when inv4x.sb_cat_watchlist = 7 then 'REG'      else '' end ||&#xd;
        case when inv4x.sb_cat_watchlist = 8 then 'Estrateg' else '' end &#xd;
              obs_desc&#xd;
&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id&#xd;
   left outer join ODF_CA_PROJECT INV4x  on INVx.id = INV4x.id&#xd;
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'&#xd;
     and   INVx.stage_code  != 6 and INVx.stage_code  != 5              /* No Cerrado - No post-implem*/&#xd;
     and (  &#xd;
             (inv4x.sb_cat_watchlist = 6  and  NVL(inv3x.sb_cat_proy,' ') != 'SMP-DR')   /* categ watchlist DR-1*/&#xd;
&#xd;
         or (inv4x.sb_cat_watchlist = 8   and  &#xd;
&#xd;
             ( NVL(inv3x.sb_clasific_proy,' ') != 'A-EX' and  NVL(inv3x.sb_clasific_proy,' ') != 'B-ES'))  /* categ watchlist STRATEG*/&#xd;
&#xd;
         or (inv4x.sb_cat_watchlist = 7   and  NVL(INVx.GOAL_CODE,' ') != 'REGULATORY')   /* categ watchlist REGU*/ &#xd;
         )&#xd;
    and   NVL(INVx.status,0)  != 3    and    NVL(INVx.status,0)  != 4        /* NO: Canc - Cancel (o Rechazada)*/&#xd;
)&#xd;
&#xd;
UNION ( select 12 obs_id, 'Completar Año Plan Anual Cerrado / Canc' obs_name,&#xd;
      invx.id inv_id, invx.code, &#xd;
      'Ver' link_proy, '' link_app,&#xd;
        ''     obs_desc&#xd;
   from INV_INVESTMENTS invx&#xd;
   left outer join ODF_CA_INV INV3x     on INVx.id = INV3x.id&#xd;
    where  invx.is_active = 1    and            INVx.odf_object_code = 'project'&#xd;
     and   ( INVx.stage_code  = 6 or INVx.status=3 or INVx.status=4 )             /* Cerrado - o cancelado*/&#xd;
     and   NVL(invx.IS_REQUIRED,0) = 1             /* es Obligatorio, está en Plan */&#xd;
     and   NVL(INV3x.sb_ano_plananual,0) = 0     /* Falta completar año*/&#xd;
&#xd;
)&#xd;
&#xd;
&#xd;
&#xd;
) t&#xd;
 left outer join INV_INVESTMENTS INV     on t.inv_id = INV.id&#xd;
 left outer join INV_PROJECTS INV2       on INV.id = INV2.prid   &#xd;
 left outer join ODF_CA_INV INV3         on INV.id = INV3.id&#xd;
 left outer join ODF_CA_PROJECT INV4     on INV.id = INV4.id &#xd;
&#xd;
/* Tabla con Manager, Gestor - TIM -RM */&#xd;
   left outer join SRM_RESOURCES GESTOR       on INV.manager_id = gestor.user_id&#xd;
   left outer join SRM_RESOURCES TIM          on inv3.tim_id = TIM.user_id&#xd;
   left outer join SRM_RESOURCES RM           on INV3.sb_rm = RM.id&#xd;
&#xd;
/* Tabla con Etapa - Estado */&#xd;
       left outer join cmn_lookups_v CLV8 ON (CLV8.language_code = ? &#xd;
           and INV.stage_code = CLV8.lookup_code       and CLV8.lookup_type = 'INV_STAGE_TYPE')&#xd;
       left outer join cmn_lookups_v CLV6 ON (CLV6.language_code = ? &#xd;
           and INV.status = CLV6.lookup_enum                and CLV6.lookup_type = 'INVESTMENT_OBJ_STATUS')&#xd;
&#xd;
/* Tabla con etiquetas de Tipo Proyecto */&#xd;
          left outer join CMN_LOOKUPS_V L1     on ( L1.language_code = ?&#xd;
              and INV3.sb_tipo_proyecto = L1.lookup_code       and L1.lookup_type = 'SB_TIPO_PROYECTO')&#xd;
&#xd;
&#xd;
/* Fecha de Cerrado o Cancelado*/&#xd;
        LEFT OUTER JOIN &#xd;
          (select distinct  audx.OBJECT_ID,   MAX(audx.LAST_UPDATED_DATE)  cerrado_date&#xd;
            from CMN_AUDITS audx&#xd;
            where audx.OBJECT_CODE = 'project'&#xd;
             and   audx.TABLE_NAME  = 'inv_investments'&#xd;
             and   (&#xd;
                   (audx.COLUMN_NAME = 'STATUS'      and   audx.RAW_VALUE_AFTER  = 3)&#xd;
                or (audx.COLUMN_NAME = 'STATUS'      and   audx.RAW_VALUE_AFTER  = 4)&#xd;
                or (audx.COLUMN_NAME = 'STAGE_CODE'   and   audx.RAW_VALUE_AFTER  = 6)  &#xd;
            ) GROUP BY audx.OBJECT_ID )  AUD  on INV.id=AUD.OBJECT_ID&#xd;
&#xd;
&#xd;
WHERE  NVL(inv2.is_template,0) = 0 and NVL(inv2.is_program,0) = 0&#xd;
      and    1=? and ~FILTER_EXPRESSION~</text>
    <param replace="language" direction="IN" expressionListDelimiter=","/>
    <param replace="language" direction="IN" expressionListDelimiter=","/>
    <param replace="language" direction="IN" expressionListDelimiter=","/>
    <param name="nsql.execFlag" type="int" direction="IN" expressionListDelimiter=","/>
    <param name="id_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(t.inv_id||t.obs_id) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="id_list" type="string" direction="IN" expressionListTag="@id_list@" expressionListDelimiter=",">
      <variation text="AND t.inv_id||t.obs_id IN (@id_list@)"/>
    </param>
    <param name="id_in" type="string" direction="IN" expressionListTag="@id_in@" expressionListDelimiter=",">
      <variation text="AND t.inv_id||t.obs_id IN (@id_in@)"/>
    </param>
    <param name="id" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.inv_id||t.obs_id = ?"/>
    </param>
    <param name="id_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.inv_id||t.obs_id >= ?"/>
    </param>
    <param name="id_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.inv_id||t.obs_id &lt;= ?"/>
    </param>
    <param name="inv_id_list" type="bigDecimal" direction="IN" expressionListTag="@inv_id_list@" expressionListDelimiter=",">
      <variation text="AND inv.id IN (@inv_id_list@)"/>
    </param>
    <param name="inv_id_in" type="bigDecimal" direction="IN" expressionListTag="@inv_id_in@" expressionListDelimiter=",">
      <variation text="AND inv.id IN (@inv_id_in@)"/>
    </param>
    <param name="inv_id" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.id = ?"/>
    </param>
    <param name="inv_id_from" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.id >= ?"/>
    </param>
    <param name="inv_id_to" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.id &lt;= ?"/>
    </param>
    <param name="codigo_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(inv.code) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="codigo_list" type="string" direction="IN" expressionListTag="@codigo_list@" expressionListDelimiter=",">
      <variation text="AND inv.code IN (@codigo_list@)"/>
    </param>
    <param name="codigo_in" type="string" direction="IN" expressionListTag="@codigo_in@" expressionListDelimiter=",">
      <variation text="AND inv.code IN (@codigo_in@)"/>
    </param>
    <param name="codigo" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.code = ?"/>
    </param>
    <param name="codigo_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.code >= ?"/>
    </param>
    <param name="codigo_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.code &lt;= ?"/>
    </param>
    <param name="nombre_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(inv.name) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="nombre_list" type="string" direction="IN" expressionListTag="@nombre_list@" expressionListDelimiter=",">
      <variation text="AND inv.name IN (@nombre_list@)"/>
    </param>
    <param name="nombre_in" type="string" direction="IN" expressionListTag="@nombre_in@" expressionListDelimiter=",">
      <variation text="AND inv.name IN (@nombre_in@)"/>
    </param>
    <param name="nombre" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.name = ?"/>
    </param>
    <param name="nombre_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.name >= ?"/>
    </param>
    <param name="nombre_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.name &lt;= ?"/>
    </param>
    <param name="manager_n_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(gestor.full_name) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="manager_n_list" type="string" direction="IN" expressionListTag="@manager_n_list@" expressionListDelimiter=",">
      <variation text="AND gestor.full_name IN (@manager_n_list@)"/>
    </param>
    <param name="manager_n_in" type="string" direction="IN" expressionListTag="@manager_n_in@" expressionListDelimiter=",">
      <variation text="AND gestor.full_name IN (@manager_n_in@)"/>
    </param>
    <param name="manager_n" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND gestor.full_name = ?"/>
    </param>
    <param name="manager_n_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND gestor.full_name >= ?"/>
    </param>
    <param name="manager_n_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND gestor.full_name &lt;= ?"/>
    </param>
    <param name="manager_id_list" type="bigDecimal" direction="IN" expressionListTag="@manager_id_list@" expressionListDelimiter=",">
      <variation text="AND gestor.user_id IN (@manager_id_list@)"/>
    </param>
    <param name="manager_id_in" type="bigDecimal" direction="IN" expressionListTag="@manager_id_in@" expressionListDelimiter=",">
      <variation text="AND gestor.user_id IN (@manager_id_in@)"/>
    </param>
    <param name="manager_id" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND gestor.user_id = ?"/>
    </param>
    <param name="manager_id_from" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND gestor.user_id >= ?"/>
    </param>
    <param name="manager_id_to" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND gestor.user_id &lt;= ?"/>
    </param>
    <param name="tim_n_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(tim.full_name) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="tim_n_list" type="string" direction="IN" expressionListTag="@tim_n_list@" expressionListDelimiter=",">
      <variation text="AND tim.full_name IN (@tim_n_list@)"/>
    </param>
    <param name="tim_n_in" type="string" direction="IN" expressionListTag="@tim_n_in@" expressionListDelimiter=",">
      <variation text="AND tim.full_name IN (@tim_n_in@)"/>
    </param>
    <param name="tim_n" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND tim.full_name = ?"/>
    </param>
    <param name="tim_n_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND tim.full_name >= ?"/>
    </param>
    <param name="tim_n_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND tim.full_name &lt;= ?"/>
    </param>
    <param name="tim_id_list" type="bigDecimal" direction="IN" expressionListTag="@tim_id_list@" expressionListDelimiter=",">
      <variation text="AND tim.user_id IN (@tim_id_list@)"/>
    </param>
    <param name="tim_id_in" type="bigDecimal" direction="IN" expressionListTag="@tim_id_in@" expressionListDelimiter=",">
      <variation text="AND tim.user_id IN (@tim_id_in@)"/>
    </param>
    <param name="tim_id" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND tim.user_id = ?"/>
    </param>
    <param name="tim_id_from" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND tim.user_id >= ?"/>
    </param>
    <param name="tim_id_to" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND tim.user_id &lt;= ?"/>
    </param>
    <param name="rm_n_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(RM.full_name) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="rm_n_list" type="string" direction="IN" expressionListTag="@rm_n_list@" expressionListDelimiter=",">
      <variation text="AND RM.full_name IN (@rm_n_list@)"/>
    </param>
    <param name="rm_n_in" type="string" direction="IN" expressionListTag="@rm_n_in@" expressionListDelimiter=",">
      <variation text="AND RM.full_name IN (@rm_n_in@)"/>
    </param>
    <param name="rm_n" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND RM.full_name = ?"/>
    </param>
    <param name="rm_n_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND RM.full_name >= ?"/>
    </param>
    <param name="rm_n_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND RM.full_name &lt;= ?"/>
    </param>
    <param name="rm_id_list" type="bigDecimal" direction="IN" expressionListTag="@rm_id_list@" expressionListDelimiter=",">
      <variation text="AND rm.id IN (@rm_id_list@)"/>
    </param>
    <param name="rm_id_in" type="bigDecimal" direction="IN" expressionListTag="@rm_id_in@" expressionListDelimiter=",">
      <variation text="AND rm.id IN (@rm_id_in@)"/>
    </param>
    <param name="rm_id" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND rm.id = ?"/>
    </param>
    <param name="rm_id_from" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND rm.id >= ?"/>
    </param>
    <param name="rm_id_to" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND rm.id &lt;= ?"/>
    </param>
    <param name="tipo_proyecto_id_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(INV3.sb_tipo_proyecto) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="tipo_proyecto_id_list" type="string" direction="IN" expressionListTag="@tipo_proyecto_id_list@" expressionListDelimiter=",">
      <variation text="AND INV3.sb_tipo_proyecto IN (@tipo_proyecto_id_list@)"/>
    </param>
    <param name="tipo_proyecto_id_in" type="string" direction="IN" expressionListTag="@tipo_proyecto_id_in@" expressionListDelimiter=",">
      <variation text="AND INV3.sb_tipo_proyecto IN (@tipo_proyecto_id_in@)"/>
    </param>
    <param name="tipo_proyecto_id" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND INV3.sb_tipo_proyecto = ?"/>
    </param>
    <param name="tipo_proyecto_id_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND INV3.sb_tipo_proyecto >= ?"/>
    </param>
    <param name="tipo_proyecto_id_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND INV3.sb_tipo_proyecto &lt;= ?"/>
    </param>
    <param name="tipo_proyecto_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(L1.name) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="tipo_proyecto_list" type="string" direction="IN" expressionListTag="@tipo_proyecto_list@" expressionListDelimiter=",">
      <variation text="AND L1.name IN (@tipo_proyecto_list@)"/>
    </param>
    <param name="tipo_proyecto_in" type="string" direction="IN" expressionListTag="@tipo_proyecto_in@" expressionListDelimiter=",">
      <variation text="AND L1.name IN (@tipo_proyecto_in@)"/>
    </param>
    <param name="tipo_proyecto" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND L1.name = ?"/>
    </param>
    <param name="tipo_proyecto_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND L1.name >= ?"/>
    </param>
    <param name="tipo_proyecto_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND L1.name &lt;= ?"/>
    </param>
    <param name="fecha_alta_list" type="date" direction="IN" expressionListTag="@fecha_alta_list@" expressionListDelimiter=",">
      <variation text="AND inv.created_date IN (@fecha_alta_list@)"/>
    </param>
    <param name="fecha_alta_in" type="date" direction="IN" expressionListTag="@fecha_alta_in@" expressionListDelimiter=",">
      <variation text="AND inv.created_date IN (@fecha_alta_in@)"/>
    </param>
    <param name="fecha_alta" type="date" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.created_date = ?"/>
    </param>
    <param name="fecha_alta_from" type="date" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.created_date >= ?"/>
    </param>
    <param name="fecha_alta_to" type="date" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv.created_date &lt;= ?"/>
    </param>
    <param name="req_procesos_id_list" type="bigDecimal" direction="IN" expressionListTag="@req_procesos_id_list@" expressionListDelimiter=",">
      <variation text="AND inv4.sb_req_procesos IN (@req_procesos_id_list@)"/>
    </param>
    <param name="req_procesos_id_in" type="bigDecimal" direction="IN" expressionListTag="@req_procesos_id_in@" expressionListDelimiter=",">
      <variation text="AND inv4.sb_req_procesos IN (@req_procesos_id_in@)"/>
    </param>
    <param name="req_procesos_id" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv4.sb_req_procesos = ?"/>
    </param>
    <param name="req_procesos_id_from" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv4.sb_req_procesos >= ?"/>
    </param>
    <param name="req_procesos_id_to" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND inv4.sb_req_procesos &lt;= ?"/>
    </param>
    <param name="req_procesos_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(case when inv4.sb_req_procesos IS NULL THEN 'Sin Def' when &#xd;&#xa;inv4.sb_req_procesos = 1 then 'Si' else 'No' end) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="req_procesos_list" type="string" direction="IN" expressionListTag="@req_procesos_list@" expressionListDelimiter=",">
      <variation text="AND case when inv4.sb_req_procesos IS NULL THEN 'Sin Def' when &#xd;&#xa;inv4.sb_req_procesos = 1 then 'Si' else 'No' end IN (@req_procesos_list@)"/>
    </param>
    <param name="req_procesos_in" type="string" direction="IN" expressionListTag="@req_procesos_in@" expressionListDelimiter=",">
      <variation text="AND case when inv4.sb_req_procesos IS NULL THEN 'Sin Def' when &#xd;&#xa;inv4.sb_req_procesos = 1 then 'Si' else 'No' end IN (@req_procesos_in@)"/>
    </param>
    <param name="req_procesos" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when inv4.sb_req_procesos IS NULL THEN 'Sin Def' when &#xd;&#xa;inv4.sb_req_procesos = 1 then 'Si' else 'No' end = ?"/>
    </param>
    <param name="req_procesos_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when inv4.sb_req_procesos IS NULL THEN 'Sin Def' when &#xd;&#xa;inv4.sb_req_procesos = 1 then 'Si' else 'No' end >= ?"/>
    </param>
    <param name="req_procesos_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when inv4.sb_req_procesos IS NULL THEN 'Sin Def' when &#xd;&#xa;inv4.sb_req_procesos = 1 then 'Si' else 'No' end &lt;= ?"/>
    </param>
    <param name="estado_sb_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then &#xd;&#xa;'Cancelado' else ( case when clv8.name='Cerrado' then clv8.name else ( case when inv3.sb_suspendido = 1 then 'Suspendido' else &#xd;&#xa;clv8.name end) end) end) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="estado_sb_list" type="string" direction="IN" expressionListTag="@estado_sb_list@" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then &#xd;&#xa;'Cancelado' else ( case when clv8.name='Cerrado' then clv8.name else ( case when inv3.sb_suspendido = 1 then 'Suspendido' else &#xd;&#xa;clv8.name end) end) end IN (@estado_sb_list@)"/>
    </param>
    <param name="estado_sb_in" type="string" direction="IN" expressionListTag="@estado_sb_in@" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then &#xd;&#xa;'Cancelado' else ( case when clv8.name='Cerrado' then clv8.name else ( case when inv3.sb_suspendido = 1 then 'Suspendido' else &#xd;&#xa;clv8.name end) end) end IN (@estado_sb_in@)"/>
    </param>
    <param name="estado_sb" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then &#xd;&#xa;'Cancelado' else ( case when clv8.name='Cerrado' then clv8.name else ( case when inv3.sb_suspendido = 1 then 'Suspendido' else &#xd;&#xa;clv8.name end) end) end = ?"/>
    </param>
    <param name="estado_sb_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then &#xd;&#xa;'Cancelado' else ( case when clv8.name='Cerrado' then clv8.name else ( case when inv3.sb_suspendido = 1 then 'Suspendido' else &#xd;&#xa;clv8.name end) end) end >= ?"/>
    </param>
    <param name="estado_sb_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then &#xd;&#xa;'Cancelado' else ( case when clv8.name='Cerrado' then clv8.name else ( case when inv3.sb_suspendido = 1 then 'Suspendido' else &#xd;&#xa;clv8.name end) end) end &lt;= ?"/>
    </param>
    <param name="estado_sb_id_list" type="bigDecimal" direction="IN" expressionListTag="@estado_sb_id_list@" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then 7 else ( &#xd;&#xa;case when clv8.name='Cerrado' then to_number(inv.stage_code) else (case when inv3.sb_suspendido = 1 then 8 else &#xd;&#xa;to_number(inv.stage_code) end ) end ) end IN (@estado_sb_id_list@)"/>
    </param>
    <param name="estado_sb_id_in" type="bigDecimal" direction="IN" expressionListTag="@estado_sb_id_in@" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then 7 else ( &#xd;&#xa;case when clv8.name='Cerrado' then to_number(inv.stage_code) else (case when inv3.sb_suspendido = 1 then 8 else &#xd;&#xa;to_number(inv.stage_code) end ) end ) end IN (@estado_sb_id_in@)"/>
    </param>
    <param name="estado_sb_id" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then 7 else ( &#xd;&#xa;case when clv8.name='Cerrado' then to_number(inv.stage_code) else (case when inv3.sb_suspendido = 1 then 8 else &#xd;&#xa;to_number(inv.stage_code) end ) end ) end = ?"/>
    </param>
    <param name="estado_sb_id_from" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then 7 else ( &#xd;&#xa;case when clv8.name='Cerrado' then to_number(inv.stage_code) else (case when inv3.sb_suspendido = 1 then 8 else &#xd;&#xa;to_number(inv.stage_code) end ) end ) end >= ?"/>
    </param>
    <param name="estado_sb_id_to" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND case when clv6.lookup_enum=3 or clv6.lookup_enum=4 then 7 else ( &#xd;&#xa;case when clv8.name='Cerrado' then to_number(inv.stage_code) else (case when inv3.sb_suspendido = 1 then 8 else &#xd;&#xa;to_number(inv.stage_code) end ) end ) end &lt;= ?"/>
    </param>
    <param name="cerrado_date_list" type="date" direction="IN" expressionListTag="@cerrado_date_list@" expressionListDelimiter=",">
      <variation text="AND AUD.cerrado_date IN (@cerrado_date_list@)"/>
    </param>
    <param name="cerrado_date_in" type="date" direction="IN" expressionListTag="@cerrado_date_in@" expressionListDelimiter=",">
      <variation text="AND AUD.cerrado_date IN (@cerrado_date_in@)"/>
    </param>
    <param name="cerrado_date" type="date" direction="IN" expressionListDelimiter=",">
      <variation text="AND AUD.cerrado_date = ?"/>
    </param>
    <param name="cerrado_date_from" type="date" direction="IN" expressionListDelimiter=",">
      <variation text="AND AUD.cerrado_date >= ?"/>
    </param>
    <param name="cerrado_date_to" type="date" direction="IN" expressionListDelimiter=",">
      <variation text="AND AUD.cerrado_date &lt;= ?"/>
    </param>
    <param name="obs_id_list" type="bigDecimal" direction="IN" expressionListTag="@obs_id_list@" expressionListDelimiter=",">
      <variation text="AND t.obs_id IN (@obs_id_list@)"/>
    </param>
    <param name="obs_id_in" type="bigDecimal" direction="IN" expressionListTag="@obs_id_in@" expressionListDelimiter=",">
      <variation text="AND t.obs_id IN (@obs_id_in@)"/>
    </param>
    <param name="obs_id" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.obs_id = ?"/>
    </param>
    <param name="obs_id_from" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.obs_id >= ?"/>
    </param>
    <param name="obs_id_to" type="bigDecimal" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.obs_id &lt;= ?"/>
    </param>
    <param name="link_proy_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(t.link_proy) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="link_proy_list" type="string" direction="IN" expressionListTag="@link_proy_list@" expressionListDelimiter=",">
      <variation text="AND t.link_proy IN (@link_proy_list@)"/>
    </param>
    <param name="link_proy_in" type="string" direction="IN" expressionListTag="@link_proy_in@" expressionListDelimiter=",">
      <variation text="AND t.link_proy IN (@link_proy_in@)"/>
    </param>
    <param name="link_proy" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.link_proy = ?"/>
    </param>
    <param name="link_proy_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.link_proy >= ?"/>
    </param>
    <param name="link_proy_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.link_proy &lt;= ?"/>
    </param>
    <param name="link_app_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(t.link_app) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="link_app_list" type="string" direction="IN" expressionListTag="@link_app_list@" expressionListDelimiter=",">
      <variation text="AND t.link_app IN (@link_app_list@)"/>
    </param>
    <param name="link_app_in" type="string" direction="IN" expressionListTag="@link_app_in@" expressionListDelimiter=",">
      <variation text="AND t.link_app IN (@link_app_in@)"/>
    </param>
    <param name="link_app" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.link_app = ?"/>
    </param>
    <param name="link_app_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.link_app >= ?"/>
    </param>
    <param name="link_app_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.link_app &lt;= ?"/>
    </param>
    <param name="obs_desc_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">
      <variation text="AND NLS_UPPER(t.obs_name||t.obs_desc) LIKE NLS_UPPER(?) ESCAPE '\'"/>
    </param>
    <param name="obs_desc_list" type="string" direction="IN" expressionListTag="@obs_desc_list@" expressionListDelimiter=",">
      <variation text="AND t.obs_name||t.obs_desc IN (@obs_desc_list@)"/>
    </param>
    <param name="obs_desc_in" type="string" direction="IN" expressionListTag="@obs_desc_in@" expressionListDelimiter=",">
      <variation text="AND t.obs_name||t.obs_desc IN (@obs_desc_in@)"/>
    </param>
    <param name="obs_desc" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.obs_name||t.obs_desc = ?"/>
    </param>
    <param name="obs_desc_from" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.obs_name||t.obs_desc >= ?"/>
    </param>
    <param name="obs_desc_to" type="string" direction="IN" expressionListDelimiter=",">
      <variation text="AND t.obs_name||t.obs_desc &lt;= ?"/>
    </param>
    <dynamicDefault tag="~FILTER_EXPRESSION~">
      <text>1=1</text>
    </dynamicDefault>
  </sql>
</statement>

Referenced by:
<?xml version="1.0" encoding="UTF-8"?>
<statementRef id="nsql_internal_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" xmlns="http://schemas.niku.com/2002/pmd"/>

Using input:
{nsql.execFlag=1}

Outcomes