--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;


--PL/SQL block calling function-------------------------------------------------------
DECLARE

   var1 t_bus_audit_data_table_nt := t_bus_audit_data_table_nt();

BEGIN

   var1 := msa_audit.fn_business_audit_record('BOND APPLICATION',
                                              '1',
                                              'U',
                                              NULL,
                                              SYSDATE,
                                              SYSDATE);

   dbms_output.put_line('var1(1) = ' || var1(1).business_attribute);

END;


--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;
   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
   l_err_msg := 'Getting business_obj_no for business_obj : ' || p_bus_object;
   SELECT business_obj_no
     INTO l_business_obj_no
     FROM msa_core.business_obj
    WHERE business_obj = TRIM(p_bus_object);

   --Get all the attributes for this object (id)
   l_err_msg := 'Before loop through all the attributes: ' || l_business_obj_no;

   BEGIN
   
      l_bus_audit_data := t_bus_audit_data_table_nt();
      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_err_msg := 'Looping: busy with attribute ' || r_attributes.business_attr_no;
      
         -- call function to get audit data ......
      
         --just for testing      
         dbms_output.put_line(l_cnt_attrib);
         l_bus_audit_data.EXTEND;
         l_bus_audit_data(l_cnt_attrib).business_attribute := 'test attrib no = ';
         l_bus_audit_data(l_cnt_attrib).created_date := SYSDATE;
         l_bus_audit_data(l_cnt_attrib).created_by := 'test creator';
         l_bus_audit_data(l_cnt_attrib).action := 'A';
         l_bus_audit_data(l_cnt_attrib).bus_old_value := 'old val';
         l_bus_audit_data(l_cnt_attrib).bus_new_value := 'new val';
      
         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;