regist payment for each month of the year using oracle data

Question

I have the following tables:

• Employees with level (rank or wtv you may call it): income_value,id, etc.
• INCOME that stores the rank vs. the income value (very tinny and static)
• REGIST_INCOME table with the following columns:
  • ID_REG;
  • ID_EMPLOYEE;
  • MONTH_and_Year_OF_PAYMENT DATE (I want to format to yy.mm);
  • DATE_OF_PAYMENT DATE (format yy.mm.dd);
  • INCOME; /*which equals the income registered in INCOME */.

I want to create a function that generates a registry/line in a x day of the month.
Something like this: On each 8th of the year, add a line to the table REGIST_INCOME for each employee that is still active (aka still works):

ID_REG | ID_EMPLOYEE  | MONTH_and_Year_OF_PAYMENT | PAYMENT DATE
--------------------------------------------------------------------
1      | 121          | 10.01                     | NULL
2      | 20345        | 10.01                     | NULL 

... and so on...

I want something simple, because in Java I want to click on a button that fetches the current date and puts it into PAYMENT DATE. Basically, I want to generate the registrations "automatically" and then confirm just the payment date.

I would appreciate an example with focus on oracle's date convertions and date functions.

Answer 1

I've created or tried to create a regularly scheduled job like this:

declare
  lv_job number;
  lv_proc varchar2(1000);
  begin_date   DATE := to_date('10.01.08');
  begin
  lv_proc:='begin regIncomePerMonth; end;';
  dbms_job.submit(lv_job,
  lv_proc,
  begin_date,
  'begin_date+30'
  );
  dbms_output.put_line('the job created with job number: '|| lv_job);
  exception
  when others then
  dbms_output.put_line('error'||sqlcode||sqlerrm);
end;

( see http://oracle.ittoolbox.com/documents/regularly-scheduled-plsql-procedure-14568) but it can't be like this, because it would be always 8Jan+30, and I want to begin in 8Jan and go on in a 30 days step until December.

and my procedure is: create or replace procedure regIncomePerMonth

cursor activeemps is select f.id_pessoa, f.escalao, s.salario_actual from funcionario f , salario s where f.removed != 1 and f.escalao = s.escalao;

begin

for emp in activeemps loop
    INSERT INTO registo_salarial values(S_REGISTO_SALARIAL.nextval,emp.id_pessoa,
    trunc(sysdate,'MM'),emp.salario_actual,NULL);
end loop;

end;