/*SQL Query to get the details of Test Cases and Requirements from a RELEASE in HP ALM  */
SELECT
  B.RCYC_NAME AS "Build",
  B.TS_USER_01 AS "Nivell Prova",
  B.CY_CYCLE as "Escenari",
  B.TS_TEST_ID AS "ID.",
  B.TS_NAME AS "Nom Prova",
  A.TS_DESCRIPTION AS "Descripció",
  B.TS_USER_02 AS "Tipus Prova",
  B.requisits AS "Requisits",
  CASE WHEN  (B.TS_USER_02 = 'Rendiment') THEN 'Automàtica'
    ELSE CASE WHEN (B.TS_USER_02 <> 'Rendiment' and A.TS_TYPE = 'QUICKTEST-TEST') THEN 'Automàtica'
    ELSE  CASE WHEN (A.TS_TYPE <> 'QUICKTEST-TEST') THEN 'Manual'
  END END END AS  "Tipus Exec"
    FROM
  TEST A,
(
SELECT DISTINCT
  TEST.TS_TEST_ID,
  TEST.TS_NAME,
  RELEASE_CYCLES.RCYC_NAME,
  CYCLE.CY_CYCLE,
  TEST.TS_USER_02,
  TEST.TS_USER_01,
  TEST.TS_STATUS,
  requisits,
       CASE WHEN (TEST.TS_USER_01='Unitàries') THEN 1
       ELSE CASE WHEN (TEST.TS_USER_01='Integració') THEN 2
       ELSE CASE WHEN (TEST.TS_USER_01='Qualificació') THEN 3
       ELSE CASE WHEN (TEST.TS_USER_01='Acceptació') THEN 4
       ELSE CASE WHEN (TEST.TS_USER_01='Revisió') THEN 5
       END END END END END AS NrNP
  FROM TEST
  LEFT JOIN testcycl on testcycl.tc_test_id=test.ts_test_id
  LEFT JOIN cycle on cycle.cy_cycle_id=testcycl.tc_cycle_id
  LEFT JOIN release_cycles on release_cycles.rcyc_id= cycle.cy_assign_rcyc
  LEFT JOIN releases on releases.rel_id = release_cycles.rcyc_parent_id,

  (SELECT TEST.TS_TEST_ID as req_prova_id, LISTAGG(REQ.RQ_REQ_ID, '; ') WITHIN GROUP (ORDER BY REQ.RQ_REQ_ID) AS requisits
   FROM TEST
   LEFT JOIN REQ_COVER on REQ_COVER.RC_ENTITY_ID=TEST.TS_TEST_ID
   LEFT JOIN REQ on REQ_COVER.RC_REQ_ID=REQ.RQ_REQ_ID
   GROUP BY TEST.TS_TEST_ID )
  WHERE rel_name=@Versio@ AND req_prova_id=TEST.TS_TEST_ID

) B
WHERE A.TS_TEST_ID = B.TS_TEST_ID
ORDER BY B.RCYC_NAME, B.NrNP, B.TS_USER_02