/*  SQL Query to get the details of Requirements and RISK results in HP ALM */
/*  Sep/13: Canvi en presentació cobertura */
SELECT REQ.RQ_REQ_ID AS "Id.",
       folder_path AS "Ruta",
       REQ.RQ_REQ_NAME AS "Nom",
       NouOCanviat AS "és Nou/Canviat",
            CASE WHEN (REQ.RQ_RBT_EFFECTIVE_BSNS_IMPACT='A') THEN 'A-Crítica'
       ELSE CASE WHEN (REQ.RQ_RBT_EFFECTIVE_BSNS_IMPACT='B') THEN 'B-Important'
       ELSE CASE WHEN (REQ.RQ_RBT_EFFECTIVE_BSNS_IMPACT='C') THEN 'C-Baixa'
       END END END AS "Crit.Neg.",
            CASE WHEN (REQ.RQ_RBT_EFFECTIVE_FAIL_PROB='1') THEN '1-Alta'
       ELSE CASE WHEN (REQ.RQ_RBT_EFFECTIVE_FAIL_PROB='2') THEN '2-Mitja'
       ELSE CASE WHEN (REQ.RQ_RBT_EFFECTIVE_FAIL_PROB='3') THEN '3-Baixa'
       END END END AS "Prob.Fall.",
            CASE WHEN (REQ.RQ_RBT_RISK='A') THEN 'A-Alt'
       ELSE CASE WHEN (REQ.RQ_RBT_RISK='B') THEN 'B-Mig'
       ELSE CASE WHEN (REQ.RQ_RBT_RISK='C') THEN 'C-Baix'
       END END END AS "Risc",
            CASE WHEN (REQ.RQ_RBT_EFFECTIVE_FUNC_CMPLX='1') THEN '1-Alta'
       ELSE CASE WHEN (REQ.RQ_RBT_EFFECTIVE_FUNC_CMPLX='2') THEN '2-Mitja'
       ELSE CASE WHEN (REQ.RQ_RBT_EFFECTIVE_FUNC_CMPLX='3') THEN '3-Baixa'
       END END END AS "Complx.",
       REQ.RQ_REQ_PRIORITY AS "Prioritat",
       CASE WHEN casos_prova IS NULL THEN 'No' ELSE 'Si' END AS "Cob.",
       casos_prova AS "Casos Prova de la Versió"
FROM REQ,

  /* Construcció de la ruta del requisit */
  ( SELECT RQ_REQ_ID as folder_id,
    SYS_CONNECT_BY_PATH(REQ.RQ_REQ_NAME, ' / ') as folder_path
    FROM REQ
    START WITH RQ_FATHER_ID = 0
    CONNECT BY NOCYCLE PRIOR RQ_REQ_ID = RQ_FATHER_ID),

  /* Construcció de la llista de casos de prova del requisit */
  ( SELECT REQ.RQ_REQ_ID as prova_req_id, LISTAGG(TS_NAME, '; ') WITHIN GROUP (ORDER BY TS_NAME) AS casos_prova
    FROM   REQ
    LEFT JOIN (
       SELECT DISTINCT REQ_COVER.RC_REQ_ID, TEST.TS_NAME
       FROM REQ_COVER
       INNER JOIN TEST on REQ_COVER.RC_ENTITY_ID=TEST.TS_TEST_ID
                    AND REQ_COVER.RC_ENTITY_TYPE = 'TEST'
       INNER JOIN testcycl on tc_test_id=ts_test_id
       INNER JOIN cycle on cy_cycle_id=tc_cycle_id
       INNER JOIN release_cycles on rcyc_id= cy_assign_rcyc
       INNER JOIN releases on rel_id = rcyc_parent_id
                       AND RELEASES.REL_NAME = @Versio@
    ) on RC_REQ_ID=REQ.RQ_REQ_ID
    GROUP BY REQ.RQ_REQ_ID ),

  /* Determinar si el requisit és Nou/Canviat a la Versió */
  ( SELECT REQ.RQ_REQ_ID as release_req_id, 'Si' AS NouOCanviat
    FROM REQ
    INNER JOIN REQ_RELEASES on REQ.RQ_REQ_ID = REQ_RELEASES.RQRL_REQ_ID
    INNER JOIN RELEASES on REQ_RELEASES.RQRL_RELEASE_ID = RELEASES.REL_ID
    WHERE RELEASES.REL_NAME = @Versio@
   UNION
    SELECT REQ.RQ_REQ_ID as release_req_id, 'No' AS NouOCanviat
    FROM REQ
    WHERE REQ.RQ_REQ_ID NOT IN (
     SELECT REQ.RQ_REQ_ID
     FROM REQ
     INNER JOIN REQ_RELEASES on REQ.RQ_REQ_ID = REQ_RELEASES.RQRL_REQ_ID
     INNER JOIN RELEASES on REQ_RELEASES.RQRL_RELEASE_ID = RELEASES.REL_ID
     WHERE RELEASES.REL_NAME = @Versio@) )


WHERE REQ.RQ_TYPE_ID > 2 AND folder_id=REQ.RQ_FATHER_ID AND prova_req_id=REQ.RQ_REQ_ID AND release_req_id = REQ.RQ_REQ_ID

ORDER BY folder_path,REQ.RQ_REQ_ID