R12 SQL Query to differentiate Customer and Suppliers Data

select * from (
select hp.party_number,hp.party_name,hp.status,decode(nvl(hpu.party_usage_code,hp.party_type),'ORGANIZATION','CUSTOMER',nvl(hpu.party_usage_code,hp.party_type)) party_type 
from  apps.HZ_PARTY_USG_ASSIGNMENTS hpu, hz_parties hp
where hp.party_id = hpu.party_id(+))
order by party_type

Read More»

Important HRMS Tables

• per_all_people_f 

This table is used to store employee related data.

• per_all_assignments_f

This table store employee assignment related information and connected to per_all_people_f with person_id and assignment number.

• per_addresses

This table store  information related to employee address form and  connected to per_all_people_f with person_id.

• per_pay_proposals

This table will store information related to employee salary form.

• per_person_types_tl

This table store type of the employee information and connected to per_all_people_f with the person_type_id.

• per_jobs_tl

This table is for JOBS in Oracle and can be linked to per_all_assignments_f.

• per_grades_tl

This table stores various types of GRADES and can be linked to per_all_assignments_f.

• hr_locations_all

Location table

• pay_all_payrolls_f

PAYROLLS Table and can be linked to per_all_assignments_f table

• per_pay_bases

Contain the PAY BASES can be linked to  per_all_assignments_f table.

• per_assignment_status_types_tl

Contain the assignment types and linked to per_all_assignments_f table

• per_person_type_usages_f

This table store the person type of the employee. 

Read More»

Responsibilites and their associated Menus

SELECT DISTINCT frt.responsibility_name, fmt.user_menu_name
  FROM apps.fnd_responsibility_tl frt,
       apps.fnd_responsibility fr,
       apps.fnd_menus_tl fmt,
       apps.fnd_menus fm,
       apps.fnd_application_tl fat,
       apps.fnd_application fa
 WHERE     frt.responsibility_id(+) = fr.responsibility_id
       AND fr.menu_id = fmt.menu_id
       AND fr.menu_id = fm.menu_id
       AND fat.application_id = fa.application_id
       AND fa.application_id = fr.application_id
       AND frt.LANGUAGE = 'US';

Read More»

Request Group assigned to Responsibilty

SELECT    responsibility_name responsibility,request_group_name,frg.description 
FROM       fnd_request_groups frg, fnd_responsibility_vl frv 
WHERE     frv.request_group_id = frg.request_group_id 
ORDER BY responsibility_name; 

Read More»

Responsibility Assigned to Users

SELECT  DISTINCT 
            u.user_id ,SUBSTR (u.user_name, 1, 30)  user_name 
           ,SUBSTR (r.responsibility_name, 1, 60)     responsibility 
           ,SUBSTR (a.application_name, 1, 50)       application 
FROM    fnd_user u, 
           fnd_user_resp_groups g, 
           fnd_application_tl a, 
           fnd_responsibility_tl r 
WHERE  g.user_id(+)                         =   u.user_id 
AND      g.responsibility_application_id  =   a.application_id 
AND      a.application_id                     =   r.application_id 
AND      g.responsibility_id                  =   r.responsibility_id 
ORDER BY SUBSTR (user_name, 1, 30), 
SUBSTR (a.application_name, 1, 50), 
SUBSTR (r.responsibility_name, 1, 60); 

Read More»

List of Responsibilities in an Instance

SELECT (SELECT application_short_name
FROM   fnd_application fa
WHERE fa.application_id = frt.application_id) application,
          frt.responsibility_id, frt.responsibility_name
FROM  apps.fnd_responsibility_tl frt;

Read More»

How to determine Oracle Database And OS version

You can use following two queries to find out the Apps/Database/OS version

select * from v$version;

select * from product_component_version;

Thanks
Mandeep

Read More»