Notes

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:k.FAF_ID:FAF_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.IDEA_CODE:IDEA_CODE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.IDEA_NAME:IDEA_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.IDEA_ID:IDEA_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.FAF_NAME:FAF_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.fase:fase@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.STATO:STATO@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.link_sec:link_sec@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.link_bsn:link_bsn@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.link_it:link_it@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.giornilav:giornilav@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.delta:delta@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.data_richiesta_ku:data_richiesta_ku@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.spec_ut:spec_ut@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:k.sistema:sistema@
FROM (
SELECT IDEA.CODE AS IDEA_CODE
, oi.gpa_breve_desc_int AS IDEA_NAME
, IDEA.ID AS IDEA_ID
, FAF.id AS FAF_ID
, FAF.NAME AS FAF_NAME
, FAF_STATO.NAME AS STATO
,'Analisi Iniziata' as fase
, 'Lista Funzioni Analisi Fast' AS LINK_SEC
, 'Lista Analisi Fast BSN' AS LINK_BSN
, 'Lista Analisi Fast IT' AS LINK_IT
,(DATEDIFF(dd, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE), getdate()))
-(DATEDIFF(wk, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE), getdate()) * 2)
-(CASE WHEN DATENAME(dw, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE)) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END) giornilav
,(DATEDIFF(dd, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE), getdate()))
-(DATEDIFF(wk, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE), getdate()) * 2)
-(CASE WHEN DATENAME(dw, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE)) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END) - (select gpa_i_comp_af from odf_ca_gpa_gestione_kpi where code='00000001') delta
,u.data_richiesta AS data_richiesta_ku
,oi.gpa_data_compl_utent as spec_ut
, serv.name AS sistema
FROM ODF_CA_GPA_FUNZ_ANALISI_FAS FAF
JOIN INV_INVESTMENTS IDEA ON IDEA.ID = FAF.ODF_PARENT_ID
join odf_ca_idea oi on oi.id=idea.id
left outer join (select ide.id id,max(jour.created_date) data_richiesta
from odf_ca_idea ide
join odf_ca_gpa_incident_journal jour
on jour.odf_parent_id = ide.id
where jour.gpa_incident_azione = 'gpa_approva_kubsn'
group by ide.id)u on u.id = oi.id
left outer join (select idea.id, service.name
from odf_ca_idea idea
left outer join odf_ca_service s on idea.gpa_sist_prim_im_evo = s.id
join inv_investments service on service.id = s.id) serv on serv.id = oi.id
JOIN CMN_LOOKUPS_V FAF_STATO ON (FAF_STATO.LANGUAGE_CODE = 'it'
AND FAF_STATO.LOOKUP_TYPE = 'GPA_WFE_AF_STATO'
AND FAF_STATO.LOOKUP_CODE = FAF.gpa_stato
)
WHERE FAF.gpa_stato != 'gpa_completato' and FAF.gpa_stato != '0001'
AND (

(FAF.gpa_stato not in ('gpa_af_rac_req','gpa_rac_req_ck', 'gpa_rich_sup_kubsn') AND FAF.gpa_ku_it = @WHERE:PARAM:USER_ID@ )
or (FAF.gpa_stato in ('gpa_af_rac_req','gpa_rac_req_ck') AND FAF.gpa_ku_bsn= @WHERE:PARAM:USER_ID@)
)
and oi.gpa_tipo_req='0001'
union
SELECT IDEA.CODE AS IDEA_CODE
, oi.gpa_breve_desc_int AS IDEA_NAME
, IDEA.ID AS IDEA_ID
, FAF.id AS FAF_ID
, FAF.NAME AS FAF_NAME
, FAF_STATO.NAME AS STATO
,'Analisi Iniziata' as fase
, 'Lista Funzioni Analisi Fast' AS LINK_SEC
, 'Lista Analisi Fast BSN' AS LINK_BSN
, 'Lista Analisi Fast IT' AS LINK_IT
,(DATEDIFF(dd, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE), getdate()))
-(DATEDIFF(wk, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE), getdate()) * 2)
-(CASE WHEN DATENAME(dw, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE)) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END) giornilav
,(DATEDIFF(dd, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE), getdate()))
-(DATEDIFF(wk, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE), getdate()) * 2)
-(CASE WHEN DATENAME(dw, isnull(oi.gpa_data_compl_utent,FAF.CREATED_DATE)) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END) - (select gpa_i_comp_af from odf_ca_gpa_gestione_kpi where code='00000001') delta
,u.data_richiesta AS data_richiesta_ku
,oi.gpa_data_compl_utent as spec_ut
, serv.name AS sistema
FROM ODF_CA_GPA_FUNZ_ANALISI_FAS FAF
JOIN INV_INVESTMENTS IDEA ON IDEA.ID = FAF.ODF_PARENT_ID
join odf_ca_idea oi on oi.id=idea.id
left outer join (select ide.id id,max(jour.created_date) data_richiesta
from odf_ca_idea ide
join odf_ca_gpa_incident_journal jour
on jour.odf_parent_id = ide.id
where jour.gpa_incident_azione = 'gpa_approva_kubsn'
group by ide.id)u on u.id = oi.id
left outer join (select idea.id, service.name
from odf_ca_idea idea
left outer join odf_ca_service s on idea.gpa_sist_prim_im_evo = s.id
join inv_investments service on service.id = s.id) serv on serv.id = oi.id
JOIN CMN_LOOKUPS_V FAF_STATO ON (FAF_STATO.LANGUAGE_CODE = 'it'
AND FAF_STATO.LOOKUP_TYPE = 'GPA_WFE_AF_STATO'
AND FAF_STATO.LOOKUP_CODE = FAF.gpa_stato
)
WHERE FAF.gpa_stato != 'gpa_completato' and FAF.gpa_stato != '0001'
AND (
(FAF.gpa_stato in ('gpa_af_rac_req','gpa_rac_req_ck') AND FAF.gpa_ku_it = @WHERE:PARAM:USER_ID@ )
)
and oi.gpa_tipo_req='gpa_prod_linea_fast'
) K
WHERE @FILTER@