--Types
CREATE OR REPLACE TYPE msa_audit.t_bus_audit_rec_data AS OBJECT
(
   business_attribute VARCHAR2(100),
   created_date       DATE,
   created_by         VARCHAR2(30),
   action             VARCHAR2(1), --I, U, D, A
   bus_old_value      VARCHAR2(4000),
   bus_new_value      VARCHAR2(4000)
)
;

CREATE TYPE msa_audit.t_bus_audit_data_table_nt AS TABLE OF t_bus_audit_rec_data;


--Function
CREATE OR REPLACE FUNCTION msa_audit.fn_business_audit_record(p_bus_object IN VARCHAR2,
                                                              p_record_id  IN VARCHAR2,
                                                              p_action     IN VARCHAR2,
                                                              p_attribute  IN VARCHAR2,
                                                              p_date_from  IN DATE,
                                                              p_date_to    IN DATE)

 RETURN t_bus_audit_data_table_nt

 IS

   CURSOR c_attributes(p_obj_id IN NUMBER) IS
      SELECT business_attr_no,
             business_obj_no,
             business_attr,
             schema_name,
             table_name,
             column_name,
             description_statement,
             pk_statement,
             date_created,
             date_updated,
             created_by,
             updated_by
        FROM msa_core.business_attr
       WHERE business_obj_no = p_obj_id;
   r_attributes c_attributes%ROWTYPE;

   l_business_obj_no NUMBER(13);
   l_err_msg         VARCHAR2(4000);
   l_bus_audit_data  t_bus_audit_data_table_nt := t_bus_audit_data_table_nt();
   l_record_id       NUMBER(13);
   l_date_from       DATE;
   l_date_to         DATE;
   l_action          VARCHAR2(1);
   l_cnt_attrib      NUMBER(13);
   l_sql             VARCHAR2(4000);
   l_test            VARCHAR2(100);
   l_sql_err         VARCHAR2(1000);
   l_primary_key     VARCHAR2(20);

BEGIN

   l_record_id := p_record_id;
   l_date_from := p_date_from;
   l_date_to   := p_date_to;
   l_action    := p_action;

   --Get the business object id
   -- ** Note: this table only contains 1 row
   SELECT business_obj_no
     INTO l_business_obj_no
     FROM msa_core.business_obj
    WHERE business_obj = TRIM(p_bus_object);


   BEGIN
   
      l_bus_audit_data.DELETE;
      l_cnt_attrib := 0;
   
      FOR r_attributes IN c_attributes(l_business_obj_no) LOOP
      
         l_cnt_attrib := l_cnt_attrib + 1;

         --get THE primary key
         l_sql := REPLACE(r_attributes.pk_statement,
                          '<record_id>',
                          l_record_id);
      
         EXECUTE IMMEDIATE l_sql
            INTO l_primary_key;
      
         l_bus_audit_data(1).business_attribute := 'test attrib no = ' || to_char(1);
      
         NULL;
      END LOOP;
   
      RETURN l_bus_audit_data;
   
   END;

EXCEPTION
   WHEN OTHERS THEN
      l_sql_err := SQLERRM;
      raise_application_error(-20020,
                              'Function FN_BUSINESS_AUDIT_RECORD ' || ' : Error occured: ' ||
                              l_err_msg || ' - ' || l_sql_err);
   
END;

--Block that calls the function
DECLARE

   var1 t_bus_audit_data_table_nt;

BEGIN

   var1 := msa_audit.fn_business_audit_record('BOND APPLICATION',
                                              '1',
                                              'U',
                                              NULL,
                                              SYSDATE,
                                              SYSDATE);
END;[/FONT]