Skip to Main Content

Cloud Platform

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Use Seniority Date for Absence Plan Accruals

User_94LX0Jul 13 2020 — edited Aug 13 2020

I have setup the Seniority Date in the Derived Factors (Length Of Service) under 'Period Start Date Rule'.

And am trying to have the Absence Plan accruals consider the Seniority date instead of Hire Date (which was setup originally)

I have done this setup, yet the accruals are considering the Hire Date.

How can I configure this? And help would be appreciated

Thanks

Comments

Zeeshan BaiG
well u can use Dynamic SQL in PL/SQL for this and REF Cursors refer Oracle documentation for REF CURSORS and DYNAMIC SQL

plz mark it helpful if it is
BluShadow
Answer
Are you wanting to update individual rows on each of those tables, or the whole table of rows where there is any row where std != 1?
Marked as Answer by Vicky007 · Sep 27 2020
Vicky007
select table_name from user_tables where PCT_FREE NOT equal to 10 and table_name like 'GenAgile%' or table_name like 'GenMicro%'


PCT_FREE is field of user_tables


if PCT_FREE not equal to 10 and table_name like GenAgile%' or 'GenMicro%' update PCT_FREE of user_tables to <input_number>
commit;


can any1 help me now ?

Edited by: user8713254 on Nov 30, 2009 8:24 AM
fsitja
Maybe something like this, not tested. I'm assuming you meant you want to update the individual rows with pct_free <> 10 for each of those tables located in all_tables.
create or replace procedure t_proc(p_numval in number) is
  cursor cur_tab is 
    select table_name
      from all_tables t
     where t.table_name like 'GENAGILE%'
           or table_name like 'GENMICRO%';    
begin
  for tab in cur_tab
  loop
    execute immediate 'update ' || tab.table_name || ' t
                          set t.pct_free = :1
                        where t.pct_free <> 10' using p_numval;
  end loop;
  commit;
exception
when others then
  rollback;
  raise;
end;
Vicky007
no its wrong i just want to update PCT_FREE column of "user_tables" table for those selected tables by INPUT
fsitja
umm just a second... do you want to update THE user_tables itself? Because that's not possible, you're dealing with a data dictionary view here, that describes your real table in the database. It contains metadata and you can't change it directly, in any other way besides changing the table described by it. It would be like telling a lie to your database about those tables, and Oracle relies and depends on that information inside USER_TABLES to be able to operate correctly.

Now if that's the case you could maybe explain to us what is it you need to accomplish please.

Edited by: fsitja on Nov 30, 2009 2:31 PM for clarification
Vicky007
Its required !
cant we do it ?
fsitja
You can't. Why do you need it? Let's not look into the microscope for a second now.

What's the requirement behind this idea, that moved you down this path in first place?
728534
Hi welcome,
Please go through the forum link mentioned below before you try this out.
3867075
As far as syntax is concerned
-- Created on 11/30/2009 by USER 
declare 
  -- Local variables here
  i INTEGER :=20; /* variable you will pass to the procedure */
begin
  -- Test statements here
  FOR x IN (SELECT table_name FROM user_tables WHERE pct_free !=10 AND (table_name LIKE 'GENAGILE%' OR table_name LIKE 'GENMICCRO%')) LOOP
  
 dbms_output.put_line (x.table_name); /* for debugging */
  EXECUTE IMMEDIATE 'ALTER table '||x.table_name||'  pctfree ' || i;
  END LOOP;
end;
Cheers!!!
Bhushan
1 - 9

Post Details

Added on Jul 13 2020
8 comments
572 views