--QUERY punto 7

DECLARE
  p_in_num_sx  VARCHAR2(20) := NULL; --'120000001';
  p_in_id_sx   NUMBER := NULL; --100721772;
  p_in_id_end  NUMBER := NULL; --100939726;
  p_in_id_inc  NUMBER := NULL; --101003737;
  p_in_id_pag  NUMBER := 101231665;
  p_in_tipoogg_result NUMBER := 1; -- puoi avere valori fino ad 8, vedi la tabella tb_tipo_oggetto_business
  p_out_n_pol  VARCHAR2(20) := '';
  p_out_n_sx   VARCHAR2(20) := '';
  p_out_id_end VARCHAR2(20) := '';
  p_out_id_inc VARCHAR2(20) := '';
BEGIN

  IF (p_in_num_sx IS NOT NULL OR p_in_id_sx IS NOT NULL) THEN
    --Recupero POL, SX da ID_SX o NUM_SX
    SELECT DISTINCT pz.pol_n_polizza, s.sin_n_sinistro
      INTO p_out_n_pol, p_out_n_sx
      FROM sin_sinistri s
      join pol_polizze pz
        ON pz.pol_sin_sinistro_id = s.sin_sinistro_id
     WHERE 1 = 1
       AND ((p_in_num_sx IS NOT NULL AND s.sin_n_sinistro = p_in_num_sx) OR
           (p_in_id_sx IS NOT NULL AND s.sin_sinistro_id = p_in_id_sx));

  ELSIF (p_in_id_end IS NOT NULL) THEN
    --Recupero POL, SX, END da END_ID
    SELECT DISTINCT pz.pol_n_polizza,
                    s.sin_n_sinistro,
                    TO_CHAR(e.end_en_dan_id)
      INTO p_out_n_pol, p_out_n_sx, p_out_id_end
      FROM sin_sinistri s
      join pol_polizze pz
        ON pz.pol_sin_sinistro_id = s.sin_sinistro_id
      join end_entita_danneggiate e
        ON e.end_sin_sinistro_id = s.sin_sinistro_id
     WHERE e.end_en_dan_id = p_in_id_end;

  ELSIF (p_in_id_inc IS NOT NULL) THEN
    --Recupero POL,SX,END,INC da INC_ID
    SELECT DISTINCT pz.pol_n_polizza,
                    s.sin_n_sinistro,
                    TO_CHAR(e.end_en_dan_id),
                    TO_CHAR(i.per_perizie_id)
      INTO p_out_n_pol, p_out_n_sx, p_out_id_end, p_out_id_inc
      FROM sin_sinistri s
      join pol_polizze pz
        ON pz.pol_sin_sinistro_id = s.sin_sinistro_id
      left outer join end_entita_danneggiate e
        ON e.end_sin_sinistro_id = s.sin_sinistro_id
      left outer join per_perizie i
        ON i.per_end_en_dan_id = e.end_en_dan_id
     WHERE i.per_perizie_id = p_in_id_inc;

  ELSIF (p_in_id_pag IS NOT NULL) THEN
    --Recupero POL,SX,END da PAG_ID
    SELECT DISTINCT pz.pol_n_polizza,
                    s.sin_n_sinistro,
                    TO_CHAR(e.end_en_dan_id)
      INTO p_out_n_pol, p_out_n_sx, p_out_id_end
      FROM sin_sinistri s
      join pol_polizze pz
        ON pz.pol_sin_sinistro_id = s.sin_sinistro_id
      join end_entita_danneggiate e
        ON e.end_sin_sinistro_id = s.sin_sinistro_id
      join pag_pagamenti p
        ON p.pag_end_en_dan_id = e.end_en_dan_id
     WHERE p.pag_pagamento_id = p_in_id_pag;

  END IF;

  --

  DBMS_OUTPUT.put_line(p_out_n_pol);
  DBMS_OUTPUT.put_line(p_out_n_sx);
  DBMS_OUTPUT.put_line(p_out_id_end);
  DBMS_OUTPUT.put_line(p_out_id_inc);

  RETURN 0;

EXCEPTION
  WHEN OTHERS THEN
    RETURN -1;

END;