XMLTYPE Example

PROCEDURE aud_s023_s020_a023(lt_in_data_orig    IN CLOB,
                               lt_in_data         IN OUT CLOB,
                               lt_out_data        IN OUT CLOB,
                               pc_process_code    IN VARCHAR2,
                               lc_from_stage      IN VARCHAR2,
                               lc_to_stage        IN VARCHAR2,
                               xn_error_handle_id IN OUT NUMBER,
                               xn_error_seq       IN OUT NUMBER,
                               xn_error_code      OUT VARCHAR2) IS
    lc_audit_id           ms_aud_audit.audit_id%TYPE;
    lc_doc_id             ms_aud_audit_doc.do_id%TYPE;
    lc_contact_id         ms_aud_audit_con.co_contact_id%TYPE;
    lc_initiator          ms_aud_audit.initiator%TYPE;
    lc_action             ms_aud_audit.audit_action%TYPE;
    lc_status             ms_aud_audit.status%TYPE;
    lc_error_message      varchar2(4000);
    lc_object_type        ms_aud_audit.dd_object_type%type;
    lc_pid                ms_aud_audit.dd_process_instance_id%type;
    lc_submit_status      ms_aud_audit.submit_status%type;
    lc_milestone_template ms_aud_audit.milestone_template%type;
    lc_modulename         VARCHAR2(100) := '[ ms_aud_f003_helper.aud_s023_s020_a023 ] ';
    lc_out_xml            XMLTYPE;
    O_Next_Id      Varchar2(4000);
    Process_Inst_Id Number;
    O_Error_Code   Number;
    O_Error_Message Varchar2(4000);
  BEGIN
    lc_out_xml := XMLTYPE(lt_in_data);

    ---- Deleting Blank Record
    lc_out_xml := ms_aud_utilities.deletenode(lc_out_xml,
                                              'con',
                                              'co_user_in_system',
                                              gc_data_namespace);
    lc_out_xml := ms_aud_utilities.deletenode(lc_out_xml,
                                              'doc',
                                              'do_type',
                                              gc_data_namespace);
    lc_out_xml := ms_aud_utilities.deletenode(lc_out_xml,
                                              'kms',
                                              'km_milestones',
                                              gc_data_namespace);
    lc_out_xml := ms_aud_utilities.update_instance(lc_out_xml,
                                                   gc_data_namespace);

    ms_apps_mdf_errors_pkg.insert_log(pn_error_handle_id => xn_error_handle_id,
                                      pn_error_sequence  => xn_error_seq,
                                      pc_error_type      => 'LOG',
                                      pc_error_text      => lc_modulename ||
                                                            'Inside ms_aud_f003_helper.aud_s020_s022_a002 before data manipulation');

    IF UPPER(lc_from_stage) = 'INITIATE_AUDIT' AND
       UPPER(lc_to_stage) = 'CREATE_AUDIT' THEN
      SELECT UPDATEXML(lc_out_xml,
                       'datapacket/data/nonmultirow/previous_stage/text()',
                       '23',
                       gc_data_namespace)
        INTO lc_out_xml
        FROM DUAL;
 
      SELECT UPDATEXML(lc_out_xml,
                       'datapacket/data/nonmultirow/dd_current_stage/text()',
                       '20',
                       gc_data_namespace)
        INTO lc_out_xml
        FROM DUAL;
 
      ---- Audit ID Generation
      select lc_out_xml.extract('datapacket/data/nonmultirow/audit_id/text()', gc_data_namespace)
             .getstringval(),
             lc_out_xml.extract('datapacket/data/nonmultirow/dd_object_type/text()', gc_data_namespace)
             .getstringval(),
             lc_out_xml.extract('datapacket/data/nonmultirow/process_instance_id/text()', gc_data_namespace)
             .getstringval(),
             lc_out_xml.extract('datapacket/data/nonmultirow/submit_status/text()', gc_data_namespace)
             .getstringval(),
             lc_out_xml.extract('datapacket/data/nonmultirow/milestone_template/text()', gc_data_namespace)
             .getstringval()
        INTO lc_audit_id,
             lc_object_type,
             lc_pid,
             lc_submit_status,
             lc_milestone_template
        FROM DUAL;
 
      IF lc_audit_id = '0' OR lc_audit_id IS NULL THEN
   
        /* SELECT ms_aud_audit_id_s.NEXTVAL
        INTO lc_audit_id
        FROM DUAL;*/
   
        lc_audit_id := ms_aud_components.ms_aud_generate_id(lc_object_type,
                                                            'AUDIT_ID',
                                                            lc_pid);
   
        SELECT UPDATEXML(lc_out_xml,
                         'datapacket/data/nonmultirow/audit_id/text()',
                         lc_audit_id,
                         gc_data_namespace)
          INTO lc_out_xml
          FROM DUAL;
   
      END IF;
      ---- End of Audit ID Generation
 
      ---- Contact multirow block
      FOR rec_con IN (SELECT CO_CONTACT_ID, INSTANCE_REC_NUM
                        FROM XMLTABLE(XMLNAMESPACES('http://www.metricstream.com/appstudio/msa' as
                                                    "msa"),
                                      'msa:datapacket/msa:data/msa:con/msa:row'
                                      PASSING lc_out_xml COLUMNS
                                      CO_CONTACT_ID NUMBER PATH
                                      'msa:co_contact_id',
                                      INSTANCE_REC_NUM NUMBER PATH
                                      'msa:instance_rec_num')) LOOP
        ---- Contact ID Generation
        IF rec_con.co_contact_id = 0 OR rec_con.co_contact_id IS NULL THEN
          SELECT ms_aud_audit_contact_id_s.NEXTVAL
            INTO lc_contact_id
            FROM DUAL;
     
          SELECT UPDATEXML(lc_out_xml,
                           'datapacket/data/con/row[instance_rec_num="' ||
                           rec_con.instance_rec_num ||
                           '"]/co_contact_id/text()',
                           lc_contact_id,
                           gc_data_namespace)
            INTO lc_out_xml
            FROM DUAL;
        END IF;
      END LOOP;
      ---- End Contact multirow block
 
      ---- Doc multirow block
      FOR rec_doc IN (SELECT DO_ID, INSTANCE_REC_NUM
                        FROM XMLTABLE(XMLNAMESPACES('http://www.metricstream.com/appstudio/msa' as
                                                    "msa"),
                                      'msa:datapacket/msa:data/msa:doc/msa:row'
                                      PASSING lc_out_xml COLUMNS DO_ID
                                      VARCHAR2(50) PATH 'msa:do_id',
                                      INSTANCE_REC_NUM NUMBER PATH
                                      'msa:instance_rec_num')) LOOP
        ---- Doc ID Generation
        IF rec_doc.do_id = '0' OR rec_doc.do_id IS NULL THEN
          SELECT ms_aud_doc_id_s.NEXTVAL INTO lc_doc_id FROM DUAL;
     
          SELECT UPDATEXML(lc_out_xml,
                           'datapacket/data/doc/row[instance_rec_num="' ||
                           rec_doc.instance_rec_num || '"]/do_id/text()',
                           lc_doc_id,
                           gc_data_namespace)
            INTO lc_out_xml
            FROM DUAL;
        END IF;
      END LOOP;
      ---- End Doc multirow block
 
   ---- pl_log multirow block
       FOR rec_pl_log IN (SELECT PL_ID, INSTANCE_REC_NUM
                        FROM XMLTABLE(XMLNAMESPACES('http://www.metricstream.com/appstudio/msa' as
                                                    "msa"),
                                      'msa:datapacket/msa:data/msa:pro/msa:row'
                                      PASSING lc_out_xml COLUMNS PL_ID
                                      VARCHAR2(50) PATH 'msa:pl_id',
                                      INSTANCE_REC_NUM NUMBER PATH
                                      'msa:instance_rec_num')) LOOP
        ---- Proceeding log ID Generation
        IF rec_pl_log.pl_id = '0' or rec_pl_log.pl_id IS NULL THEN
     
        Select Xmltype(Lt_In_Data_Orig).Extract('datapacket/data/nonmultirow/instance_id/text()', 'xmlns="http://www.metricstream.com/appstudio/msa"').getNumberVal() Into Process_Inst_Id From Dual;
     
        Ms_Apps_Utilities.Get_Next_Id('AUD','MS_AUD_PL_LOG',' ',Process_Inst_Id,O_Next_Id,O_Error_Code,O_Error_Message);
             
                cboe_error_log(Process_Inst_Id,'MS_AUD_PL_LOG',O_Next_Id,Process_Inst_Id);
             
          SELECT UPDATEXML(lc_out_xml,
                           'datapacket/data/pro/row[instance_rec_num="' ||
                           rec_pl_log.instance_rec_num || '"]/pl_id',
                           xmltype('<msa:pl_id xmlns:msa="http://www.metricstream.com/appstudio/msa">' ||
                                   O_Next_Id || '</msa:pl_id>'),
                           gc_data_namespace)
            INTO LC_OUT_XML
            FROM DUAL;
        END IF;
      END LOOP;
      ---- End pl_log multirow block  
 
      ---- Assigning the initiator Start
      lc_initiator := ms_aud_utilities.fn_get_xml_value('initiator',
                                                        lc_out_xml,
                                                        gc_data_namespace);
 
      IF lc_initiator = '0' THEN
        lc_initiator := ms_aud_utilities.fn_get_xml_value('dd_current_user_name',
                                                          lc_out_xml,
                                                          gc_data_namespace);
        SELECT UPDATEXML(lc_out_xml,
                         'datapacket/data/nonmultirow/initiator/text()',
                         lc_initiator,
                         gc_data_namespace)
          INTO lc_out_xml
          FROM DUAL;
      END IF;
      ---- Assigning the initiator End
 
      IF ((lc_submit_status = '0' OR lc_submit_status IS NULL)) THEN
        SELECT UPDATEXML(lc_out_xml,
                         'datapacket/data/nonmultirow/submit_status',
                         xmltype('<msa:submit_status xmlns:msa="http://www.metricstream.com/appstudio/msa">' || '1' ||
                                 '</msa:submit_status>'),
                         gc_data_namespace)
          INTO lc_out_xml
          FROM DUAL;
      END IF;
 
      IF ((lc_submit_status != '0' OR lc_submit_status IS NOT NULL) AND
         lc_milestone_template IS NOT NULL) THEN
        SELECT UPDATEXML(lc_out_xml,
                         'datapacket/data/nonmultirow/submit_status',
                         xmltype('<msa:submit_status xmlns:msa="http://www.metricstream.com/appstudio/msa">' || '2' ||
                                 '</msa:submit_status>'),
                         gc_data_namespace)
          INTO lc_out_xml
          FROM DUAL;
      END IF;
 
      ---- Populate Status information Start        
      SELECT lc_out_xml.EXTRACT('datapacket/data/nonmultirow/audit_action/text()', gc_data_namespace)
             .getstringval()
        INTO lc_action
        FROM DUAL;
 
      lc_status := ms_aud_utilities.fn_get_status(20, 23, lc_action);
 
      SELECT UPDATEXML(lc_out_xml,
                       'datapacket/data/nonmultirow/status/text()',
                       lc_status,
                       gc_data_namespace)
        INTO lc_out_xml
        FROM DUAL;
      ---- Populate Status information End
 
      ms_aud_utilities.process_comments_history(i_key_col_value => ms_aud_utilities.fn_get_xml_value('audit_id',
                                                                                                     lc_out_xml,
                                                                                                     gc_data_namespace),
                                                i_comments      => ms_aud_utilities.fn_get_xml_value('audit_comments',
                                                                                                     lc_out_xml,
                                                                                                     gc_data_namespace),
                                                i_process_step  => ms_aud_utilities.fn_get_xml_value('dd_current_stage',
                                                                                                     lc_out_xml,
                                                                                                     gc_data_namespace),
                                                i_user_name     => ms_aud_utilities.fn_get_xml_value('dd_current_user_name',
                                                                                                     lc_out_xml,
                                                                                                     gc_data_namespace),
                                                i_status        => ms_aud_utilities.fn_get_xml_value('status',
                                                                                                     lc_out_xml,
                                                                                                     gc_data_namespace),
                                                i_action        => ms_aud_utilities.fn_get_xml_value('audit_action',
                                                                                                     lc_out_xml,
                                                                                                     gc_data_namespace),
                                                i_key_col_name  => 'AUDIT_ID',
                                                i_rtf_attach    => NULL,
                                                i_form_name     => 'MANAGE AUDIT',
                                                o_error_code    => xn_error_code,
                                                o_error_message => lc_error_message);
 
      /*SELECT UPDATEXML(lc_out_xml,
                       'datapacket/data/nonmultirow/audit_comments/text()',
                       ' ',
                       gc_data_namespace)
        INTO lc_out_xml
        FROM DUAL;*/
    END IF;

    lt_out_data := lc_out_xml.getclobval();
    lt_in_data  := lc_out_xml.getclobval();

    ms_apps_mdf_errors_pkg.insert_log(pn_error_handle_id => xn_error_handle_id,
                                      pn_error_sequence  => xn_error_seq,
                                      pc_error_type      => 'LOG',
                                      pc_error_text      => lc_modulename ||
                                                            'End of ms_aud_f003_helper.aud_s023_s023_a023');
  END aud_s023_s020_a023;