CREATE OR REPLACE PACKAGE mip_select_xml IS

  -- Author  : HARDYA
  -- Created : 25/03/2008 09:55:57
  -- Purpose : Produce select lists in XML format

  /** 
  Return an selection list suitable for use within AJAX Select_XML call
  
  %param p_select_list identifies the select list required
  %param p_select_list_key1 key used to condition list returned
  %param p_select_list_key2 optional second key used to condition list returned
  */
  PROCEDURE select_xml(p_select_list      IN VARCHAR2
                      ,p_select_list_key1 IN VARCHAR2
                      ,p_select_list_key2 IN VARCHAR2 DEFAULT NULL);

END mip_select_xml;
/
CREATE OR REPLACE PACKAGE BODY mip_select_xml IS

  PROCEDURE pl(p_in   VARCHAR2
              ,p_line IN NUMBER DEFAULT NULL) IS
  BEGIN
    NULL;
    /*  $IF mip_debug_constants.debugging OR mip_debug_constants.select_xml 
    $THEN*/
    mip_debug.pl(p_unit => $$PLSQL_UNIT
                ,p_line => p_line
                ,p_in   => p_in);
    /*  $END*/
  END pl;

  PROCEDURE select_xml(p_select_list      IN VARCHAR2
                      ,p_select_list_key1 IN VARCHAR2
                      ,p_select_list_key2 IN VARCHAR2 DEFAULT NULL) IS
  BEGIN
    pl('select_xml:entry:' || p_select_list || ':' || p_select_list_key1 || ':' ||
       p_select_list_key2);
    owa_util.mime_header('text/xml'
                        ,FALSE);
    htp.p('Cache-Control: no-cache');
    htp.p('Pragma: no-cache');
    owa_util.http_header_close;
    htp.prn('<select>');
  
    IF p_select_list = 'MANU_METR' THEN
      FOR l_rec IN (SELECT '- OTHER -' AS NAME
                          ,'OTHER' AS id
                      FROM dual
                    UNION
                    SELECT code AS NAME
                          ,code AS id
                      FROM meters
                     WHERE prty_id = decode(p_select_list_key1
                                           ,'OTHER'
                                           ,NULL
                                           ,'%null%'
                                           ,NULL
                                           ,p_select_list_key1)
                       AND mety_code = p_select_list_key2
                     ORDER BY 1) LOOP
        htp.prn('<option value="' || l_rec.id || '">' || l_rec.NAME ||
                '</option>');
      END LOOP;
    ELSIF p_select_list = 'METY_MANU' THEN
      FOR l_rec IN (SELECT DISTINCT prty.NAME AS NAME
                                   ,to_char(prty.id) AS id
                      FROM parties prty
                          ,meters  metr
                     WHERE metr.mety_code = p_select_list_key1
                       AND metr.prty_id = prty.id
                       AND prty.prty_type = 'MANU'
                     ORDER BY 1) LOOP
        htp.prn('<option value="' || l_rec.id || '">' || l_rec.NAME ||
                '</option>');
      END LOOP;
    END IF;
  
    htp.prn('</select>');
    pl('select_xml:exit');
  EXCEPTION
    WHEN OTHERS THEN
      pl('select_xml:exception:' || SQLERRM);
      RAISE;
  END select_xml;

BEGIN
  -- Initialization
  NULL;
END mip_select_xml;
/
