Automatic Partition Management for Oracle 10g

If you have a moving window partitioning scheme then you should know that in Oracle 11g you now have a brand new way of creating new partitions. Oracle 11g creates the partitions in an automatic fashion, so that when the row arrives at the table, if the partition does not exist, Oracle will create it. This is awsome, but imagine two things:

* You can’t migrate to Oracle 11g on the fly. You’re stuck in 10g
* You also need to drop the oldest partition, because you have lack of storage issues

Then if you are in this situation we have the solution for you. It’s bullet-proof tested and proven at production sites.

Every night you need to add another partition and remove the oldest. Pure moving window stuff. The format of the partition name is: PXXXX_YYYY_MM_DD, where XXXX is a sequential number that increments every day. This is daily partitioning and should only have a job running this code everynight:

declare
x varchar2(90);
s varchar2(900);
begin
-- Fetchs the name of the oldest partition
select partition_name
into x
from sys.dba_tab_partitions
where partition_position = 1
and table_name = 'MYTABLE'
and table_owner = 'MYOWNER';
-- Builds the name-string
s := 'ALTER TABLE MYOWNER.MYTABLE DROP PARTITION '||x||' UPDATE INDEXES';
-- Uses a customized report and sends it by email
-- so you can see the partitions state before and after
MYOWNER.my_pkg.sends_email('REPORT6');
-- Drops the Partition
execute immediate s;
-- And now adds another
MYOWNER.add_partition;
MYOWNER.my_pkg.sends_email('REPORT6');
--dbms_output.put_line(s);
end;

The procedure “add_partition” is another piece of automatic code that you must create prior to the previous PL/SQL block:

CREATE OR REPLACE procedure MYOWNER.add_partition
is
next_part varchar2(40);
less_than_char varchar2(20);
comando_add varchar2(1000);
BEGIN

-- Generates the name of the partition
select 'P'||to_char(to_number(substr(partition_name,2,
         instr(partition_name,'_',1)-2))+1)||'_'||
         to_char(to_date(substr(partition_name,
         instr(partition_name,'_',1)+1),'yyyy_mm_dd')+1,'yyyy_mm_dd'),
         replace(to_char(to_date(substr(partition_name,
        instr(partition_name,'_',1)+1),'yyyy_mm_dd')+2,'yyyy_mm_dd'),'_','-')
into next_part,less_than_char
from dba_tab_partitions
where table_owner = 'MYOWNER'
and table_name = 'MYTABLE'
and partition_position = (select max(partition_position)
from dba_tab_partitions where table_owner = 'MYOWNER'
and table_name = 'MYTABLE');
-- Builds the statement string
comando_add :=              'ALTER TABLE MYOWNER.MYTABLE ADD PARTITION '||next_part;
comando_add := comando_add||' VALUES LESS THAN (to_date('||chr(39)||less_than_char;
comando_add := comando_add||chr(39)||','||chr(39)||
'yyyy-mm-dd'||chr(39)||')) TABLESPACE DATA_PARTITIONED';
-- Executes the statement
execute immediate(comando_add);
--dbms_output.put_line(comando_add);
end;
/

You will have permission issues that you can resolve reading this.

Source: http://ocpdba.wordpress.com/2009/10/12/automatic-partition-management-for-oracle-10g/

R12 Subledger Accounting Process Events

You can run accounting in two ways, one is Online other is batch mode. Online accounting is ideal when you want to generate accounting event for one transaction. Batch mode accounting process will be initiated by submitting accounting concurrent request (Create Accounting, Submit accounting)
There is no difference if you run online accounting or batch accounting in terms of the event generation. Only different is in detailed reporting
Tables that are involved in this process are
XLA_TRANSACTION_ENTITIES
XLA_EVENTS
XLA_AE_HEADERS
XLA_AE_LINES
GL_IMPORT_REFERENCES
GL_JE_LINES
GL_JE_HEADERS
GL_BALANCES
Links Between these tables

GL_JE_LINES (JE_HEADER_ID, JE_LINE_NUM)	GL_IMPORT_REFERENCES (JE_HEADER_ID, JE_LINE_NUM)
GL_IMPORT_REFERENCES (GL_SL_LINK_TABLE, GL_SL_LINK_ID	XLA_AE_LINES (GL_SL_LINK_TABLE, GL_SL_LINK_ID)
XLA_AE_LINES (APPLICAITON_ID, AE_HEADER_ID)	XLA_AE_HEADERS (APPLICATION_ID, AE_HEADER_ID)
XLA_AE_HEADERS (APPLICATION_ID, EVENT_ID)	XLA_EVENTS (APPLICATION_ID, EVENT_ID)
XLA_EVENTS (APPLICATION_ID, ENTITY_ID)	XLA_TRANSACTION_ENTITIES (APPLICATION_ID, ENTITY_ID)

Draft : Draft will create journal entries, which are not final, which means they are not ready to be transferred to GL.
You can create accounting on this transaction again and again, which will delete the old journal entries and create new ones. You can’t transfer these journal entries to GL.
xla_events.process_status_code = D
xla_events.event_status_code = U
xla_ae_headers.accounting_entry_status_code = D
Final : Final will create journal entries, which can be transferred to GL. Once it is finally accounted you can’t run create accounting on the particular transaction (specifically on that event).
Run Transfer Journal Entries to GL program
xla_events.process_status_code = P
xla_events.event_status_code = P
xla_ae_headers.accounting_entry_status_code = F
Final Post: Final Post will create journal entries in final mode, transfer them to GL and post them.
xla_ae_headers.accounting_entry_status_code = F
xla_ae_headers.transfer_status_code = Y
xla_events.process_status_code = P
xla_events.event_status_code = P

Source: http://appssupport.com/2010/05/21/r12-subledger-accounting-process-events/