Calculating Employee Service years

Below script return Number of years, Months and days of an employee

Select ppos.person_id,
trunc(months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start )/12) ||' Years '||
       TO_CHAR (FLOOR(MOD (months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start ), 12)) ) ||' Months '||
       (To_Date (coalesce(ppos.projected_termination_date,sysdate))-Add_Months (ppos.date_start,Trunc (Months_Between (coalesce(ppos.projected_termination_date,sysdate), ppos.date_start)) ) )      ||' Days ' yearOfService
      
From   per_periods_of_service ppos

Flashback version Between query in oracle

Flashback version between query used to track a table row in a specified time period

Syntax: VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound];

eg:
SELECT * FROM wf_notifications
VERSIONS  BETWEEN TIMESTAMP TO_TIMESTAMP('15-AUG-2012 08:13:07')
                                      AND TO_TIMESTAMP('15-AUG-2012 12:01:17')

        Where message_type = 'XXXX'

                                 

HR: Create Person Extra infromation using API

Following HR (11i) api can be used for creating person extra information, below example show you how to insert a employee passport details into extra information tables using api.

Procedure create_pp_details(p_pp_country In Varchar2 -- passport issuing country
                            ,p_pp_number  In Varchar2 -- passport number
                            ,p_pp_issue_date In date -- passport issue date
                            ,p_pp_expiry_date In Date -- passport expiry date
                            ,p_pp_issue_place In Varchar2 -- passport issue place
                            ,p_person_id In Number
                            )

Is
  l_person_extra_info_id  Number;
   l_object_version_number Number;
 

Begin
 

 hr_person_extra_info_api.create_person_extra_info
    (p_validate                    => false
    ,p_person_id                   => p_person_id     -- employee person id
    ,p_information_type            => 'PASSPORT_DETAILS' -- this is same as your extra person information flex filed code.
    ,p_pei_attribute_category      => null
    ,p_pei_information_category    => 'PASSPORT_DETAILS' -- this is same as your extra person information flex filed code.
    ,p_pei_information1            =>  p_pp_country
    ,p_pei_information2            => p_pp_number
    ,p_pei_information3            => to_char(p_pp_issue_date,'RRRR/MM/DD HH:MI:SS')
    ,p_pei_information4            => to_char(p_pp_expiry_date,'RRRR/MM/DD HH:MI:SS')
    ,p_pei_information5            => p_pp_issue_place
    ,p_person_extra_info_id        => l_person_extra_info_id  -- out values
    ,p_object_version_number       => l_object_version_number -- out values

    );
 commit;
end;

ORA-01927: cannot REVOKE privileges you did not grant

Means you didn't grant to the user.

use the follwing dba table to find out details of grant privleges

dba_tab_privs

Insert %rowtype variable directly into a table

Can I insert a %rowtype variable directly into table without specifying each column?
Ans: Yes

 Normally we are putting open and closed brackets after values. Remove these brackets in the case %rowtype variable.

Syntax:
Insert into << values >><<%rowtype variable>>

eg: 

Procedure create_emp(p_emp_rec In emp%rowtype)
Is
Begin
insert into emp values p_emp_rec;
End create_emp;