HRMS (Human Resources useful information)

 

 Purchasing

 If HR is not completely installed. We can define the employees and positions in Oracle purchasing,

However if HR is installed then the apps will force you to define the employees ,supervisors and positions in the HR  module.

In Oracle HR,People are different from the employees. Not all the people defined are employees. Employee information goes into the table "hr_employees" and the people information goes into the "per_all_people_f"  table,but an employee will be in both the hr_employees and per_all_people_f tables. Typically if there are any contacts for an employee ,then they will be entered as the people.

-- The below query can be tried to see if the HR product is installed or not. Interesting all the HR related code is present in the applicaction user PER and not HR

select INIT_FUNCTION_NAME from FND_PRODUCT_INITIALIZATION

where APPLICATION_SHORT_NAME = 'PER'

and exists (select 1 from FND_PRODUCT_INIT_CONDITION C

          where C.APPLICATION_SHORT_NAME = 'PER'

     and C.RE_INIT_CONDITION in ('USER','RESP','APPL','NLS'))

Normally, whenever a purchase order is raised ,say,by an employee. then it will be routed thru the next in the hierarchy.

And there are two types of hierarchies in the Oracle purchasing module. One is position approval hierarchy and another

employee/supervisor hierarchy. This setting can be defined using the path

     setup => Organization => Financials Options screen. => Human Resources tab.

    

If the "Use Approval Hierarchies" check box is checked, then it means that the   position approval hierarchy is used.

If the "Use Approval Hierarchies" check box is unchecked, then it means that the employee/supervisor hierarchy is used.

Let  us briefly dwell on what is the difference between these two hierarchies. In the case of position approval hierarchy, firstly a hierarchy of all the positions in the company are defined. for ex, Manager, Senior Manager, Executive director ,Vice President,etc; all these positions are first defined. So if a manager raises a PO, then that document is routed up the hierarchy i.e it is routed to the senior manager.

In the case of employee/supervisor hierarchy, while we are defining an employee ,say ,Smith, then his manager ,say, David, is also specified. And a document is routed to that manager David.

INcidentally in Oracle purchasing, there are lot of document types defined. That is ,a requisition(Internal or Purchase), planned purchase order, standard purchase order.etc We can see these  types from the path   setup => Purchasing => Document Types.

what are the products that we would be supporting in this suite. outerjoin when the where conditions purchase order total price.

 HR Stuff.

   The profile option MO: Operating Unit will always the store the operating unit id and not the inventory_org id

   The Profile option MO:Top Operating Level stores the values of set of books, legal entity or operating unit.

   If it is SOB, user can run the report with any LE underneath that sob

   if it is LE,  user can run the report with any OU  underneath that LE

   if it is OU,  user can run the report with any OU mentioned in the MO: Operating Unit profile option.

       There are certain predefined oracle app reports which we can run with above mentioned reporting levels.

     Ex : Aging 4 Buckets report.*/

/* Hr_organization_information table stores two classes of information

  If org_information_context = 'CLASS', then it gives information about the org itself.

     i.e  whether it is a Legal Entity,Operating Unit, Inventory Organization.

  Interestingly ,the below query will give you info about everything other than set of books id,which makes sense as sob is accounting stuff.

*/

   select    hoi.organization_id

         ,hoi.org_information_context

   ,haou.name     Name

   from  hr_organization_information hoi

   ,hr_all_organization_units  haou

   where hoi.organization_id =2398

   and   hoi.organization_id  = haou.organization_id

   and   org_information_context = 'CLASS'

 

/*  If org_information_context = 'Accounting Information',it provides information on ow it is related to other Orgs.   

   Given a specific organization id,get a list of parents i.e set of books,legal entity and operating unit, run it.

   If the org_information_context is Accounting Information, the org_information1 will store the set of books id

   and org_information3 will give the inventory org id. Hence given the set of books id we can get all the

   heirarchy in one particular record as below.

  */    

select     org_information1  set_of_books_id

   ,org_information2  legal_entity_id

   ,org_information3  operating_unit_id

   ,hoi.organization_id  Inventory_organization_id

   ,org_information4

   ,haou.name

 from   hr_organization_information hoi

   ,hr_all_organization_units  haou

 where  org_information1= 1  -- this isthe sob id

 and    hoi.organization_id  = haou.organization_id

 and    org_information_context ='Accounting Information'

 select * from gl_sets_of_books where set_of_books_id = 34

  

 select * from hr_organization_units where organization_id = 61

select * from fnd_profile_options

where profile_option_name like 'GL_SET_OF_BKS_NAME'

select * from fnd_profile_options_tl

where user_profile_option_name like 'GL%Set%'

select * from hz_cust_accounts order by creation_date desc

select * from hz_cust_account_roles where cust_account_id = 4193

select partner_id from pv_Partner_profiles

order by creation_date desc

hr_organization_information_v

------------------------

-- Set the environment in the production environment ( fnd_client_info.set_org_context)

BEGIN

  dbms_application_info.set_client_info(485);

END;

SELECT  employee_id FROM fnd_user WHERE user_name LIKE 'PLUI'

SELECT * FROM per_all_people_f WHERE last_name ='ADAMS' and first_name ='JAMES'

SELECT * FROM per_all_people_f WHERE person_id in (37303)

SELECT job_id, person_id, position_id FROM per_all_assignments_f WHERE  person_id in (37303)

SELECT name job_name FROM per_jobs WHERE job_id = 10349

--  This query given a job function,will give the  approval group.

select control_group_name from po_control_groups where control_group_id in

        (select control_group_id  from po_position_controls where end_date is not null and job_id in (

                select job_id from  per_jobs_vl where name like 'E7-G AND A' )  )

-- This query ,given a approval group, will give the  approval limits.

select * from po_control_rules where control_group_id  in

    (select control_group_id from po_control_groups where control_group_name = 'NCE 10/CE 10')

-- This query given a job function name, will give the  approval limits.(Make sure you set the correct org_id before you check the amount limits.

select * from po_control_rules where control_group_id  in

    (select control_group_id from po_control_groups where control_group_id in

        (select control_group_id  from po_position_controls where end_date is not null and job_id in (

                select job_id from  per_jobs where name like 'E7-G AND A' ) 

   )

  )