HOW AND WHEN COGS ACCOUNT HIT FOR TRANSACTIONS

Inventory Interface generates the cost of goods sold account for transactions when passing the transactions to Oracle Inventory. Receivables Interface generates a receivable account, a revenue account, a tax account, a freight account, and others, but not the cost of goods sold account. Ship confirm and pick release do not generate any accounts.
___________________________________________________________________
Oracle Application R12 COGS 

In Oracle Application R12 COGS process has been changed. Reason for that are aggressive revenue
recognition practices as well as the guidelines from various governing bodies.

Till R11 Cost of goods sold has been recognized as soon as the Order line has shipped, as shown in below
steps

After ship confirm, user run the interface trip stop (ITS).
ITS in turns run the OM Interface and Inventory Interface.
Inventory Interface calls Inventory transaction manager which in turns call COGS WF.
But as per new practices COGS should be recognized along with the revenue.

In R12 used need to define deferred cogs account. These deferred cogs account can be defined at each
inventory org level.

During shipping process Inventory tables will hold the deferred COGS accounts. Only after invoicing has
done in AR, AR will notify the Costing and Costing in turns call the COGS account generator to get the
cogs account .In that way COGS and revenue will be recognized in the same period.

There are few exceptions like how to get the COGS for
1. Ship only line (No Invoice will be created). 
To handle above cases Close-line activity of the order line workflow has modified to call the costing API
to get the cogs value 
What is the Deferred COGS account in R12
The deferred COGS of goods account is the new feature introduced in Release 12. The basic
fundamental behind the enhancement is that the COGS is now directly matched to the Revenue. The
same was not possible till now.

Prior to this enhancement, the value of goods shipped from inventory were expensed to COGS upon
ship confirm, despite the fact that revenue may not yet have been earned on that shipment. With this
enhancement, the value of goods shipped from inventory will be put in a Deferred COGS account. As
percentages of Revenue are recognized, a matching percentage of the value of goods shipped from
inventory will be moved from the Deferred COGS account to the COGS account, thus synchronizing
the recognition of revenue and COGS in accordance with the recommendations of generally accepted
accounting principles.

The Matching Principle is a fundamental accounting directive that mandates that revenue and its
associated cost of goods sold must be recognized in the same accounting period. This enhancement will
automate the matching of Cost of Goods Sold (COGS) for a sales order line to the revenue that is billed
for that sales order line.

The deferral of COGS applies to sales orders of both non-configurable and configurable items (Pick-To Order and Assemble-To-Order). It applies to sales orders from the customer facing operating units in the
case of drop shipments when the new accounting flow introduced in 11.5.10 is used. And finally, it also
applies to RMAs that references a sales order whose COGS was deferred. Such RMAs will be accounted
using the original sales order cost in such a way that it will maintain the latest known COGS recognition percentage.
To set the deferrred COGS account.
 Inventory –Setup–Organization–Parameters–Other Accounts

A new account is added which is referred as the Deffered COGS accounts.

NEW ACCOUNTING:

Release 12 :
 
When a Sales order is shipped the following accounting takes place:

Inventory Valuation Account : Credit. 
Deferred COGS account : Debit

Once the revenue is recognised, you would need to decide the percentage you wish to recognize the
Revenue. A COGS recognition transaction will be created to reflect a change in the revenue recognition
percentage for a sales order line.

The steps to generate such transactions are as follows:
1. Run the Collect Revenue Recognition Information program. This program will collect the change in
revenue recognition percentage based on AR events within the user specified date range.
2. Run the Generate COGS Recognition Events. This program will create the COGS recognition
transaction for each sales order line where there is a mismatch between the latest revenue recognition
percentage and the current COGS recognition percentage.

Note that users can choose how often they want to create the COGS Recognition Events. 

Navigation to run the COGS recognition request :
– Cost > COGS Recognition > Collect Revenue Recognition Information
– Cost > COGS Recognition > Generate COGS Recognition Events
– Cost > View Transactions > Material Transactions

The distribution for the COGS Recognition transaction associated with the Sales Order transaction now
would be as follows:

Deffered COGS : Debit y revenue percentage
COGS : Credit (Actual revenue percentage )

Thus, essentially the recognized COGS balance is to move the value from Deferred COGS to COGS.

This particular COGS recognition transaction actually correspond to a revenue recognition percentage
change.

You can view the transactions as :
Navigation:
– Cost > View Transactions > Material Transactions > Distributions

A new COGS Revenue Matching Report shows the revenue and COGS information of sales order that fall
within the user specified date range by sales order line

SIMPLER TERMS ( Table level details ) :

Once the whole cycle is complete we will have 2 transactions lines in mtl_material_transactions.

1. Sales Order
2. COGS Recognition transaction

Accounting will be in mtl_transaction_accounts and the Subledger accounting tables as follows: 

Transaction 1:
Inventory Valuation Account : Credit. (item_cost)
Deferred COGS account : Debit (item_cost)

Transaction 2:
Deffered COGS : Credit (Actual revenue percentage)
COGS : Debit (Actual revenue percentage )

COGS (Cost of Goods Sold) in Oracle E-Business Suite Release 12
Deferred COGS is a new feature introduced in Oracle E-Business Suite Release 12. The basic
fundamentals behind the enhancement are that the COGS are now directly matched to the
Revenue.
 
Prior to this enhancement, the value of goods shipped from inventory were expensed to COGS
upon ship confirm, despite the fact that revenue may not yet have been earned on that shipment.
With this enhancement, the value of goods shipped from inventory will be put in a Deferred
COGS account. As percentages of Revenue are recognized, a matching percentage of the value
of goods shipped from inventory will be moved from the Deferred COGS account to the COGS
account, thus synchronizing the recognition of revenue and COGS in accordance with the
recommendations of generally accepted accounting principles.
 
While this helps solve some key accounting issues, there are some key issues one needs to be
aware of:
   
• Currently Deferred COGS accounting cannot be turned off in Oracle EBS Release 12.
• The activity of recording COGS recognition is now a multi-step process
• Run AR Revenue Recognition, and Submit Accounting Processes
• Run a set of concurrent processes in Cost Manager to record Sales Order and revenue
recognition transactions and to create and cost COGS recognition transactions. These
COGS recognition transactions adjust deferred and earned COGS in an amount that
synchronizes the % of earned COGS to earned revenue on Sales Order shipment lines.
• Record Order Management Transactions: records new sales order transaction activity
such as shipments and RMA returns in Oracle Order Management.
• Collect Revenue Recognition Information: determines the percentage of recognized or
earned revenue related to invoiced sales order shipment lines in Oracle Receivables.
• Generate COGS Recognition Events: creates and costs COGS recognition events for
new sales order shipments/returns and changes in revenue recognition and credits for
invoiced sales order shipment lines.
The end result of these activities is a series of COGS Recognition Material Distributions.
However these distributions will not be visible on the Material Transaction screen, unless the
‘Include Logical Transaction’ checkbox is checked.

R12 Order Management: Revenue-COGS Matching Part I
It is a relief to see this much awaited functionality. We heard our accounting departments
complaining about the period mismatches in for our COGS and Revenue accounting for one
order. We ship an order on the last day of the month and COGS gets posted to this month, but if
the invoice is created with next period’s GL date as the current AR period is closed by the time
the order is invoiced. Now all that is changed. Revenue-COGS matching is a standard
functionality now. In simple terms, this means, COGS for an order line will be recognized only if
the revenue is recognized for that line making sure that the revenue and COGS are posted in the
same month.
All of us have spent a lot of time working on COGS accounting workflow to achieve what we
want for our clients/companies. In some cases we even customized Revenue accounting
generation (avoiding auto accounting logic) by using ra_interface, distributions_all table. We had
a handle on accounts generation in this process but not on the actual events of accounting
recognition.
We all know this.
When we ship the order and run the Interface Trip Stops program, inventory gets reduced and
orders get updated to move forward in the workflow to the next activity. Interface Trip Stops
program calls the OE_FLEX_COGS_PUB to generate the COGS account as per design. This
gets passed on to the mtl_material_transactions table in the distribution_account column. When
Cost Manager runs, distribution_account from mtl_material_transactions is picked up to generate
accounting as shown.
                                Cr Inventory Material account $100
                                            Dr COGS Account $100
The role of COGS
workflow is not changed. It is still the same which generates the account of our choice per
workflow design. It still passes the generated account to the mtl_material_transactions table into
the distribution_account column. But what changed in R12 is accounting. In order to match
Revenue with COGS accounting in terms of timing, COGS account cannot be used at the time
shipping. Instead revenue recognition process of the invoice for that order line should generate
COGS accounting.
To achieve this, a new account called Deferred COGS account is introduced at the inventory
organization parameters level. So when the order shipped instead of the above entries the entry
will be
                       Cr Inventory Material account             $100
                                    Dr Deferred COGS Account    $100
When you invoice is this order line, if you have no revenue recognition policies or specialized
accounting rules, revenue should be instantly recognized (upon running revenue recognition
program).
After revenue is recognized, the following programs need to be run to relieve deferred COGS
value and debit actual COGS account.
Record Order Management Transactions: This program collects all the transactions that
belong to transaction types Sales order issue and Logical Sales Order Issue which are not costed
and the order line is invoiceable. The source table is mtl_material_transactions. This program
inserts rows into two tables: cst_cogs_events and cst_revenue_cogs_match_lines. This program
is not necessary to run. When not run, Cost Manager will insert rows into these tables. So from
implementation considerations, this program is not required to be run.
Collect Revenue Recognition Information: This program collects invoice line information of
the order line after the revenue is recognized. The source tables are ra_customer_trx_lines_all
and ra_cust_trx_line_gl_dist_all. It will check the percentage of the revenue recognized (we can
recognize revenue partially for a specific order line based on accounting rule or contingency
rules) and inserts that information into this table: cst_revenue_recognition_lines. Also the table
cst_revenue_cogs_control table is updated with the latest run date with high date of this
parameter, which is used in the next run of the same program.
Generate COGS
Recognition Events: The role of this program is to record a logical material transaction, which
is used to create final COGS entry. This program takes information from the above tables and
creates one logical inventory transaction in mtl_material_transactions with a new transaction
type called COGS Recognition. In the same program these transactions will be costed (not by the
cost manager) creating the following accounting entries. The COGS account in this entry is taken
from the distribution_account in mtl_material_transactions table (which was generated earlier by
COGS workflow).
                                Cr Deferred account                     $100
                                              Dr COGS Account             $100
This is the concept in simple terms. There are different cases (well documented in the Cost
Management User Guide) in this same flow which, I will take one at a time to discuss in the
coming posts.
SQL statements that help understand the data model are below,
SELECT header_id
  FROM oe_order_headers_all
 WHERE order_number = &your_order_number;

SELECT line_id
  FROM oe_order_lines_all
 WHERE header_id = (SELECT header_id
                      FROM oe_order_headers_all
                     WHERE order_number = &your_order_number);

SELECT *
  FROM mtl_material_transactions
 WHERE trx_source_line_id IN (SELECT line_id
                                FROM oe_order_lines_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number =
&your_order_number))
       AND transaction_type_id IN (33, 10008);

SELECT *
  FROM mtl_transaction_accounts
 WHERE transaction_id IN (
          SELECT transaction_id
            FROM mtl_material_transactions
           WHERE trx_source_line_id IN (SELECT line_id
                                          FROM oe_order_lines_all
                                         WHERE header_id = (SELECT header_id
                                                              FROM
oe_order_headers_all
                                                             WHERE
order_number = &your_order_number))
             AND transaction_type_id IN (33, 10008));

SELECT *
  FROM cst_revenue_cogs_match_lines
 WHERE cogs_om_line_id IN (SELECT line_id
                             FROM oe_order_lines_all
                            WHERE header_id = (SELECT header_id
                                                 FROM oe_order_headers_all
                                                WHERE order_number =
&your_order_number));

SELECT *
  FROM cst_cogs_events
 WHERE cogs_om_line_id IN (SELECT line_id
                             FROM oe_order_lines_all
                            WHERE header_id = (SELECT header_id
                                                 FROM oe_order_headers_all
                                                WHERE order_number =
&your_order_number));

SELECT *
  FROM cst_revenue_cogs_control;

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE interface_line_context = ‘ORDER ENTRY’
   AND interface_line_attribute6 IN (SELECT line_id
                                       FROM oe_order_lines_all
                                      WHERE header_id = (SELECT header_id
                                                           FROM
oe_order_headers_all
                                                          WHERE order_number
= &your_order_number));

SELECT *
  FROM ra_cust_trx_line_gl_dist_all
 WHERE customer_trx_line_id IN (
          SELECT customer_trx_line_id
            FROM ra_customer_trx_lines_all
           WHERE interface_line_context = ‘ORDER ENTRY’
             AND interface_line_attribute6 IN (SELECT line_id
                                                 FROM oe_order_lines_all
                                                WHERE header_id = (SELECT
header_id
                                                                     FROM
oe_order_headers_all
                                                                    WHERE
order_number = &your_order_number))
             AND account_set_flag = ‘N’
             AND account_class = ‘REV’);

SELECT *
  FROM cst_revenue_recognition_lines
 WHERE revenue_om_line_id IN (SELECT line_id
                                FROM oe_order_lines_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number =
&your_order_number));

SELECT *
  FROM mtl_transaction_accounts
 WHERE transaction_id IN (
          SELECT transaction_id
            FROM mtl_material_transactions
           WHERE trx_source_line_id IN (SELECT line_id
                                          FROM oe_order_lines_all
                                         WHERE header_id = (SELECT header_id
                                                              FROM
oe_order_headers_all
                                                             WHERE
order_number = &your_order_number))
             AND transaction_type_id IN (33, 10008));

Finding password of a User in Oracle Apps(11i\R12)?

FINDING USER PASSWORDS IN ORACLE APPLICATIONS - 11i & R12

Step 1. Create a package as below

Create or replace package xxa_get_user_pwd

As Function decrypt ( key in varchar2 ,value in varchar2 ) RETURN VARCHAR2;

END xxa_get_user_pwd;

/

CREATE OR REPLACE PACKAGE BODY xxa_get_user_pwd

AS FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 ) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

(java.lang.String,java.lang.String) return java.lang.String';                                                                    

END xxa_get_user_pwd;

/

Step 2. Query to get password for apps user

Select (Select XXA_Get_User_Pwd.Decrypt (Upper ((Select Upper (Fnd_Profile.Value ('Guest_User_Pwd'))

       From Dual)), Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password

  From Fnd_User Usertable

 Where Usertable.User_Name Like Upper ((Select Substr (Fnd_Profile.Value ('Guest_User_Pwd') ,1 ,

       Instr (Fnd_Profile.Value ('Guest_User_Pwd'), '/') - 1 ) From Dual));

above query works only in 11i since Oracle removed ‘Guest User Password’ profile option from Release 12 so please use below query for R12

/-------------------------------------------------------------------------------------------------/

/*          QUERY TO GET APPS USER PASSWORD FOR BACK END          */

/-------------------------------------------------------------------------------------------------/

ALTER SESSION SET current_schema = apps;

Select (Select XXA_Get_User_Pwd.Decrypt (Fnd_Web_Sec.Get_Guest_Username_Pwd, Usertable.Encrypted_Foundation_Password)

From Dual) As Apps_Password

From Fnd_User Usertable

Where Usertable.User_Name Like (Select Substr (Fnd_Web_Sec.Get_Guest_Username_Pwd

,1, Instr (Fnd_Web_Sec.Get_Guest_Username_Pwd, '/') - 1) From Dual);

Step 3. Query to get password for application user

Select Usertable.User_Name , (Select XX_Get_User_Pwd.Decrypt (Upper ((Select

       (Select XXA_Get_User_Pwd.Decrypt (Upper ((Select Upper (Fnd_Profile.Value ('Guest_User_Pwd'))

       From Dual)), Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password

  From Fnd_User Usertable

 Where Usertable.User_Name Like Upper ((Select Substr (Fnd_Profile.Value ('Guest_User_Pwd') ,

       1 , Instr (Fnd_Profile.Value ('Guest_User_Pwd'), '/') - 1 ) From Dual)))) ,Usertable.Encrypted_User_Password)

       From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('&Username');

above query works only in 11i since Oracle removed ‘Guest User Password’ profile option from Release 12 so please use below query for R12

/-----------------------------------------------------------------------------------------------------------------/

/*          QUERY TO GET THE PASSWORD FOR THE FRONTEND USER LOGIN          */

/----------------------------------------------------------------------------------------------------------------/

ALTER SESSION SET current_schema = apps;

Select Usr.User_Name, Usr.Description,

       XXA_Get_User_Pwd.Decrypt((Select (Select XXA_Get_User_Pwd.Decrypt

       (Fnd_Web_Sec.Get_Guest_Username_Pwd, Usertable.Encrypted_Foundation_Password)From Dual) As Apps_Password

       From Fnd_User Usertable  Where Usertable.User_Name = (Select Substr(Fnd_Web_Sec.Get_Guest_Username_Pwd,1,

       Instr(Fnd_Web_Sec.Get_Guest_Username_Pwd,'/') - 1) From Dual)), Usr.Encrypted_User_Password) Password

  From Fnd_User Usr

 Where Usr.User_Name = '&User_Name';

Autoincrement Primary Key for Oracle

A lot of databases has a column autoincrement attribute or an autoincrement data type. They are used to create unique identifiers for a column. In Oracle, you must use a Sequence in combination with a Trigger to get an autoincrement column. DeZign for Databases will automate the creation of an autoincrement column for you.

To make an Oracle autoincrement column in DeZign for Databases:

1. Select Dictionary | Sequences from the application menu.
2. In the Sequences dialog, add a new sequence (let's say Sequence_1). Press OK to save your changes.
3. Go to the Entity dialog of the entity which owns the Attribute(column) you want to make autoincrement.
4. Select the Attribute in the list of attributes.
5. Select a numeric data type for the Attribute (NUMBER, INTEGER).
6. Go to tab "Advanced" in the property editor at the bottom of the list of attributes.
7. Select "Sequence_1" in the Apply sequence on attribute field.
8. A "autoincrement" trigger will be created now automatically. This trigger will use the selected Sequence. Go to tab Triggers to see the generated trigger.

The generated trigger code:

CREATE OR REPLACE TRIGGER %triggername%
BEFORE INSERT ON %tablename% FOR EACH ROW
BEGIN
  SELECT %seqname%.NEXTVAL
  INTO :NEW.%columnname%
  FROM DUAL;
END;

%seqname% will be replaced with the name of the sequence when you generate the database.

%triggername% will be replaced with the name of the trigger when you generate the database.

%columnname% will be replaced with the name of the associated column when you generate the database.

Note that the sequence can be reused. You can use the same sequence on multiple columns if you want.

The generated trigger code is generated based on the template settings in the oracle definition file (oracle7.def, oracle8.def, oracle9.def, oracle10.def). The definition files are located in the Definitions subdirectory of DeZign for Databases' installation directory. 

Register Custom Table in ORACLE

DECLARE
   vc_appl_short_name   CONSTANT VARCHAR2 (40) := 'C_APPS';
   vc_tab_name          CONSTANT VARCHAR2 (32) := 'C_FND_INTERFACES';
   vc_tab_type          CONSTANT VARCHAR2 (50) := 'T';
   vc_next_extent       CONSTANT NUMBER        := 512;
   vc_pct_free          CONSTANT NUMBER        := 10;
   vc_pct_used          CONSTANT NUMBER        := 70;
BEGIN   -- Start Register Custom Table
   -- Get the table details in cursor
   FOR table_detail IN (SELECT table_name, tablespace_name, pct_free, pct_used,
                              ini_trans, max_trans, initial_extent,
                              next_extent
                          FROM dba_tables
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register table
      ad_dd.register_table (p_appl_short_name => vc_appl_short_name,
                            p_tab_name        => table_detail.table_name,
                            p_tab_type        => vc_tab_type,
                            p_next_extent     => NVL(table_detail.next_extent, vc_next_extent),
                            p_pct_free        => NVL(table_detail.pct_free, vc_pct_free),
                            p_pct_used        => NVL(table_detail.pct_used, vc_pct_used)
                           );
   END LOOP; -- End Register Custom Table

   -- Start Register Columns
   -- Get the column details of the table in cursor
   FOR table_columns IN (SELECT column_name, column_id, data_type, data_length,
                               nullable
                          FROM all_tab_columns
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register column
      ad_dd.register_column (p_appl_short_name      => vc_appl_short_name,
                             p_tab_name             => vc_tab_name,
                             p_col_name             => table_columns.column_name,
                             p_col_seq              => table_columns.column_id,
                             p_col_type             => table_columns.data_type,
                             p_col_width            => table_columns.data_length,
                             p_nullable             => table_columns.nullable,
                             p_translate            => 'N',
                             p_precision            => NULL,
                             p_scale                => NULL
                            );
   END LOOP;   -- End Register Columns
   -- Start Register Primary Key
   -- Get the primary key detail of the table in cursor
   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P' AND table_name = vc_tab_name)
   LOOP
      -- Call the API to register primary_key
      ad_dd.register_primary_key (p_appl_short_name      => vc_appl_short_name,
                                  p_key_name             => all_keys.constraint_name,
                                  p_tab_name             => all_keys.table_name,
                                  p_description          => 'Register primary key',
                                  p_key_type             => 'S',
                                  p_audit_flag           => 'N',
                                  p_enabled_flag         => 'Y'
                                 );
      -- Start Register Primary Key Column
      -- Get the primary key column detial in cursor
      FOR all_columns IN (SELECT column_name, POSITION
                            FROM dba_cons_columns
                           WHERE table_name = all_keys.table_name
                             AND constraint_name = all_keys.constraint_name)
      LOOP
         -- Call the API to register primary_key_column
         ad_dd.register_primary_key_column
                                     (p_appl_short_name      => vc_appl_short_name,
                                      p_key_name             => all_keys.constraint_name,
                                      p_tab_name             => all_keys.table_name,
                                      p_col_name             => all_columns.column_name,
                                      p_col_sequence         => all_columns.POSITION
                                     );
      END LOOP; -- End Register Primary Key Column
   END LOOP;    -- End Register Primary Key

   COMMIT;
END;

Let’s go and validate in application.
Navigate to Application Developer –> Application –> Database –> Table
F11 –> Enter your table_name ‘C_FND_INTERFACES’ –> Ctrl + F11.
 

DBMS_SCHEDULER examples

DBMS_SCHEDULER examples

May be interesting to some of you which haven't had the time testing and working with the DBMS_SCHEDULER. I collected some examples to show what is possible with that amazing tool.

DBMS_SCHEDULER is an internal Oracle package (since Version 10g) which provides database driven jobs.
It's divided into 3 parts:

• Time schedule part - dbms_scheduler.create_schedule
• Program declaration part - dbms_scheduler.create_program
• Job (conflation) part -dbms_scheduler.create_job

Examples of the dbms_scheduler.create_schedule part:

view plainprint?

1. begin  
2. -- daily from Monday to Sunday at 22:00 (10:00 p.m.)  
3. dbms_scheduler.create_schedule  
4. (schedule_name => 'INTERVAL_DAILY_2200',  
5.  start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)  
6.  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',  
7.  comments=>'Runtime: Every day (Mon-Sun) at 22:00 o'clock');  
8.   
9. -- run every hour, every day  
10. dbms_scheduler.create_schedule(    
11. schedule_name  => 'INTERVAL_EVERY_HOUR',    
12.   start_date    => trunc(sysdate)+18/24,    
13.   repeat_interval => 'freq=HOURLY;interval=1',    
14.   comments     => 'Runtime: Every day every hour');    
15.   
16. -- run every 5 minute, every day  
17. dbms_scheduler.create_schedule(  
18. schedule_name  => 'INTERVAL_EVERY_5_MINUTES',  
19.   start_date    => trunc(sysdate)+18/24,  
20.   repeat_interval => 'freq=MINUTELY;interval=5',  
21.   comments     => 'Runtime: Every day all 5 minutes');  
22.   
23. -- run every minute, every day  
24. dbms_scheduler.create_schedule(  
25. schedule_name  => 'INTERVAL_EVERY_MINUTE',  
26.   start_date    => trunc(sysdate)+18/24,  
27.   repeat_interval => 'freq=MINUTELY;interval=1',  
28.   comments     => 'Runtime: Every day every minute');  
29.   
30. -- run every Sunday at 18:00 (06:00 p.m.)  
31. dbms_scheduler.create_schedule  
32. (schedule_name => 'INTERVAL_EVERY_SUN_1800',  
33.  start_date=> trunc(sysdate)+18/24,  
34.  repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',  
35.  comments=>'Runtime: Run at 6pm every Sunday');  
36. end;  

Example of the dbms_scheduler.create_program part:

view plainprint?

1. begin  
2. -- Call a procedure of a database package  
3. dbms_scheduler.create_program  
4. (program_name=> 'PROG_COLLECT_SESS_DATA',  
5.  program_type=> 'STORED_PROCEDURE',  
6.  program_action=> 'pkg_collect_data.prc_session_data',  
7.  enabled=>true,  
8.  comments=>'Procedure to collect session information'  
9.  );  
10. end;  

Example of the dbms_scheduler.create_job part:

view plainprint?

1. begin  
2. -- Connect both dbms_scheduler parts by creating the final job  
3. dbms_scheduler.create_job  
4.  (job_name => 'JOB_COLLECT_SESS_DATA',  
5.   program_name=> 'PROG_COLLECT_SESS_DATA',  
6.   schedule_name=>'INTERVAL_EVERY_5_MINUTES',  
7.   enabled=>true,  
8.   auto_drop=>false,  
9.   comments=>'Job to collect data about session values every 5 minutes');  
10. end;  

Examples to change dbms_scheduler settings:

view plainprint?

1. begin  
2. -- change start time  
3. DBMS_SCHEDULER.SET_ATTRIBUTE(  
4.    name => 'INTERVAL_EVERY_5_MINUTES',  
5.    attribute => 'start_date',  
6.    value => to_date('22.06.2009 12:15','dd.mm.yyyy hh24:mi')  
7. );  
8.   
9. -- change repeat interval  
10. DBMS_SCHEDULER.SET_ATTRIBUTE(  
11.    name => 'INTERVAL_EVERY_MINUTE',  
12.    attribute => 'repeat_interval',  
13.    value => 'freq=MINUTELY;interval=2'  
14. );   
15. end;  

Example to run job immediate:

view plainprint?

1. begin  
2. dbms_scheduler.run_job('JOB_COLLECT_SESS_DATA',TRUE);  
3. end;  

Example to restart job:

view plainprint?

1. begin  
2. dbms_scheduler.disable('JOB_COLLECT_INST_INFO');  
3. dbms_scheduler.enable('JOB_COLLECT_INST_INFO');  
4. end;  

Select job status:

view plainprint?

1. -- All jobs  
2. select * from user_scheduler_jobs;  
3.   
4. -- Get information to job  
5. select * from user_scheduler_job_log order by log_date desc;  
6.   
7. -- Show details on job run  
8. select * from user_scheduler_job_run_details;