Oracle SYS.AUD$ performance issues, Apache Kafka come to rescue!

OK, we’ll continue about Oracle Database and Apache Kafka integration.
In which We describe very straight usage of Oracle Database as Apache Kafka producer.
Here we discuss how Apache Kafka can help in improving some pains while running
Oracle database.

Just to google for SYS.AUD$:

4470000 results and most of them are related to

592000 results about performance issues with SYS.AUD$!

Apache Kafka come to rescue!

Starting from Oracle Database  11g default options are:

audit_trail = db

audit_sys_operations = true

This produce lot of records in SYS.AUD$ table, fortunately starting from Oracle Database 12c this table
is in SYSAUX table space, Unified Auditing is introduced too. But this still generate contention in the same
database instance! We can disable audit_trail, but what to do if we must audit some actions or data
changes according to industry (PCI DSS for example) or government standards?

Solution is in setting audit destination to file system, watch this file system for changes and then transfer
audit files to Apache Kafka broker. This will consume CPU on database server only for file system
watching and transferring files to Kafka.
Source code and installation instructions are available at https://github.com/averemee-si/oraaud-kafka.

Using Kafka Streams or Kafka Consumers you can perform any analysis of audit information without

“eating” CPU and disk I/O of datab

Oracle GL based reporting an reconciliation tool

When it comes to period end processing, EBS users faces lack of handy tools and tons of tedious repetetive work.

• Scattered information in standard reports
• FSG is awesome idea but techically more tan 20 years old with ugly drill and export realisation.
• GL to subledger drilling realisation is ugly...
• Your name it :)

 Builded for

• Instant acess to GL balances

• Build FSG alike reportst online

• Drill to GL journals and subledger details

• When drill into detais of selected GL balance or accounting flexfield range.

• You get all entries comprises GL balance/accounting flexfield range, which is subledger entries or in case of direct manual or uploaded GL journals - jounal lines.
  
• Subledger enries have a lot of useful for reconciliation process details.
• Source module - AP, AR, FA, etc.
• Parties/Sites - Supplier, Customer.
• Subledger classification - event types.
• Descriptions, etc.
• In a future releases more details will be added - PO's, Inventory Items, Projects-Tasks, Assets...
• From subledger entries you can go directly to respective GL journals with all details.

• Export to Ecxel

• Everything Excel exportable in one click.

• And more...


It is JavaEE application can be placed close to the database even withing EBS itself and retrieve data fast.
When drill-down to big underlying data it naturally provides lazy loading by getting just data that can be displayed.
No "smart" cashing server solutions needed.

See how it works. Set captions on (CC) to see detailed descriptions.

Try it HERE

Then try it on you mobile device :)

AP invoice sweep program by OU

-->

Despite many requests, Oracle still not enabled period closing in modules (particularly AR/AP) by operating unint, it is only can be achieved by standard functionality if OU's assigned to different set of books. If you OU's assigned to the same SOB, period can be closed only at once for all OU's which is quite inconvinient.
AP invoice sweep program and report about period exceptions in ap behave the same way. In this post I'l show you how to tweak system to enable AP invoice sweep program and report about period exceptions in ap work by particular OU.
Both mentioned programs uses ap_org_attributes_gt temporary table to store OU's being processed. I decided to impose policy on this table to restrict access current user to specific OU.
I defined two custom profile option's assigned to current user (only at user level!) one  XXAP_ENABLED_OU links current user to particular OU and another one XXAP_SUPER_USER if assigned an has Y value allows current user to access all OU's preserving standard functionality, just in case.

 create or replace function policy_ap_org_attributes_gt (p_schema varchar2, p_object varchar2)
return varchar2 is
begin
  if fnd_profile.VALUE_SPECIFIC('XXAP_SUPER_USER')='Y' then
    return '1=1';
  else 
    return 'ORG_ID = fnd_profile.VALUE_SPECIFIC(''XXAP_ENABLED_OU'')';
  end if; 
end;

then eneble policy

Begin
DBMS_RLS.ADD_POLICY (
   object_schema => 'AP',
   object_name    => 'ap_org_attributes_gt',
   policy_name    => 'xx_ap_org_attributes_gt',
   policy_function    => 'policy_ap_org_attributes_gt',
   statement_types => 'select, insert, update, delete',
   update_check    => false
);
End;

thats all about, benefit :)

AP invoice accounting statuses

-->

select hh.name as OU, ap_invoices_pkg.get_posting_status(ai.invoice_id)as Status,
ai.invoice_num , ai.invoice_amount , ss.vendor_name , ss.segment1 as Vendor_num, u.user_name as created_by
from ap.ap_invoices_all ai, ap.ap_suppliers ss, hr.hr_all_organization_units hh, fnd_user u
where ap_invoices_pkg.get_posting_status(ai.invoice_id) in ('N','P') -- status you need
and ai.vendor_id=ss.vendor_id
and ai.org_id=hh.organization_id
and ai.created_by=u.user_id
and ai.gl_date>to_date('31.03.2012','dd.mm.yyyy')
and ai.gl_date<to_date('01.05.2012','dd.mm.yyyy')
order by hh.name

Session should be initialized first

AP invoice statuses

-->

select hh.name as OU, ap_invoices_pkg.get_approval_status( ai.invoice_id, ai.invoice_amount, ai.payment_status_flag, ai.invoice_type_lookup_code)as Status, ai.invoice_num as Invoice_num, ai.invoice_amount Amount, ss.vendor_name as Supplier, ss.segment1 as Supplier_number, u.user_name as created_by
from ap.ap_invoices_all ai, ap.ap_suppliers ss, hr.hr_all_organization_units hh, fnd_user u
where ap_invoices_pkg.get_approval_status( ai.invoice_id, ai.invoice_amount, ai.payment_status_flag, ai.invoice_type_lookup_code)
not in ('APPROVED','AVAILABLE','FULL','CANCELLED','UNPAID') -- statuses you need
and ai.vendor_id=ss.vendor_id
and ai.org_id=hh.organization_id
and ai.created_by=u.user_id
and ai.gl_date>to_date('31.03.2012','dd.mm.yyyy')
and ai.gl_date<to_date('01.05.2012','dd.mm.yyyy')
order by hh.name


Environment should be initialized before inquery.

Script to register custom table

-->

DECLARE
  x_appl_short_name VARCHAR2(30) :=  'you application short name eg SQLAP for payables';
  x_appl_table_name VARCHAR2(30) := 'you custom table name';
BEGIN
  ad_dd.register_table(x_appl_short_name, x_appl_table_name, 'T');
  FOR cur_sh IN ( SELECT * FROM dba_tab_columns t
                  WHERE t.OWNER = x_appl_short_name
                        AND t.TABLE_NAME = x_appl_table_name
                ) LOOP
      AD_DD.delete_column(x_appl_short_name, x_appl_table_name, cur_sh.column_name);
      AD_DD.REGISTER_COLUMN ( x_appl_short_name,
                              x_appl_table_name,
                              cur_sh.column_name,
                              cur_sh.column_id,
                              cur_sh.data_type,
                              cur_sh.data_length,
                              cur_sh.nullable,
                              'N' );
  END LOOP;
END;



Then go to Application Developer responsibility, menu->Application->Database->Table, see you custom table registered.

Hide diagnostics menu

-->

In this menu it is very handy to do lot of things like look at hidden field values like invoice_id's etc, yet it possible to change values wich is not allowed by standard form behavior, so it makes you instance potentialy vulnerable when used by curious users.
By defaul it looks like this

 Then disable it by changing profile

An finally diagnostics menu disabled