Register Table and its column

Register Table and its column:

create or replace procedure XXIMR_REGISTER_TABLE(APPL_SHORT_NAME varchar2,
                                                 TABLE_OWNER     VARCHAR2,
                                                 TABLE_NAME      VARCHAR2) is

  cursor tbl_column(p_owner varchar2, p_Table varchar2) is
    select clmn.COLUMN_NAME,
           clmn.DATA_TYPE,
           clmn.DATA_LENGTH,
           clmn.DATA_PRECISION,
           clmn.DATA_SCALE,
           clmn.NULLABLE,
           clmn.COLUMN_ID
      from ALL_TAB_COLUMNS clmn
     where clmn.OWNER = p_owner
       and clmn.TABLE_NAME = p_Table;

begin

  ad_dd.register_table(p_appl_short_name => APPL_SHORT_NAME, p_tab_name => TABLE_NAME, p_tab_type => 'T');

  Commit;
  for i in tbl_column(TABLE_OWNER, TABLE_NAME) loop
    ad_dd.register_column(p_appl_short_name => APPL_SHORT_NAME,
                          p_tab_name        => TABLE_NAME,
                          p_col_name        => i.column_name,
                          p_col_seq         => i.column_id,
                          p_col_type        => i.data_type,
                          p_col_width       => i.data_length,
                          p_nullable        => i.nullable,
                          p_translate       => 'N',
                          p_precision       => i.data_precision,
                          p_scale           => i.data_scale);
  end loop;

  commit;
end;

Supplier's Contact Query of R12's TCA Architecture:

select hzr.object_id        party_id_for_vendor,
       hp.party_id          party_id_for_person,
       hp.person_first_name,
       hp.person_last_name,
       hcpp.phone_number    primary_phone_number,
       hcpe.email_address,
       hcpp.PHONE_AREA_CODE,
       hcpp.PHONE_NUMBER,
       hcpp.PHONE_EXTENSION,
       hp.party_name,
       fu.user_name
  from hz_parties               hp,
       fnd_user                 fu,
       hz_relationships         hzr,
       hz_parties               hzr_hp,
       hz_party_usg_assignments hpua,
       HZ_CONTACT_POINTS        hcpp,
       HZ_CONTACT_POINTS        hcpe
 where hp.party_id = hzr.subject_id
   and hzr.object_id = 431710 -- Supplier's party_id
   and hzr.relationship_type = 'CONTACT'
   and hzr.relationship_code = 'CONTACT_OF'
   and hzr.subject_type = 'PERSON'
   and hzr.object_type = 'ORGANIZATION'
   and hzr_hp.party_id = hzr.party_id
   and fu.person_party_id(+) = hp.party_id
   and hpua.party_id = hp.party_id
   and hpua.status_flag = 'A'
   and hpua.party_usage_code = 'SUPPLIER_CONTACT'
   And hcpp.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
   And hcpp.OWNER_TABLE_ID(+) = hzr.PARTY_ID
   And hcpp.PHONE_LINE_TYPE(+) = 'GEN'
   And hcpp.CONTACT_POINT_TYPE(+) = 'PHONE'
   And hcpe.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
   and hcpe.OWNER_TABLE_ID(+) = hzr.PARTY_ID
   And hcpe.CONTACT_POINT_TYPE(+) = 'EMAIL'
   And hcpe.status(+) = 'A'
   And hcpp.status(+) = 'A'