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;  

ORACLE Quote to Order Process flow

Quote to Sales Order cycle

Amala Silvester M

Oracle Quotes offers limited functionality to create Quotes and Progress it through the Negotiation phase and finally creates Sales Order. Within Order Management we can avail simple functionality like. (Create Quote > Submit for Internal Approval > Take Customer Acceptance).

In company has complex / advance quoting needs then Oracle Sales Quoting needs to be implemented.

One more important point to be noted is data for Quote header/lines got inserted into OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL and same data will be used for order header /lines

Create Quote:
If you want then you can add Expiration date for the quote.
Initially quote remain in Draft status.

Submit for Internal Approval:
Once the draft is done then creator shall submit it for internal approval. This can be done from ‘Action’ button then select ‘Progress Quote’ and submit for approval.

Then the status becomes ‘Pending Internal Approval’


WorkFlow:
Remember the work flow associated to quote header only, there will be no Line level workflow associated in case of with Quote.

WF_ITEMS and WF_ITEM_ACTIVITY_STATUSES  with ITEM_TYPE ='OENH' , but once Quote got approved and converted to Orders , system will end date the workflow with ITEM_TYPE ='OENH' and creates new workflow with ITEM_TYPE ='OEOH'( for order header) and OEOL (for order lines).

Internal Approval:
Now the WF is waiting for internal Approval. We can modify the WF to have some customization like send email notification to concerned person.

After the internal approval from the authorities, WF get moved further and wait for Customer Acceptance.

At this stage status of the quote becomes “Pending Customer Acceptance”.



Customer Acceptance:
After receiving confirmation from your customer you may take the appropriate action.
In our case, let’s say customer sent Acceptance. So go to ‘Actions’ button and select ‘Customer Acceptance’ option.

Here you can add details about who and when accepted the quote.

At this stage quote WF is completed and Sales Order WF starts for header as well as for order lines.



Sales Order get created automatically

Order Header Workflow will be as below


Order Line Workflow will be as below.

From this point sales order cycle starts…