Tuesday, June 21, 2011

Designation of Employee Query In Oracle

SELECT
papf.person_id
,papf.employee_number
,papf.first_name
,papf.full_name
,papf.sex
,papf.date_of_birth
,papf.effective_start_date papf_start_date_active
,papf.effective_end_date papf_end_date_active
,mgr.full_name manager
,mgr.employee_number mgr_emp_no
,mgr.person_id mgr_prson_id
,paaf.assignment_number
,paaf.effective_start_date assingment_start_date
,pj.job_id
,pj.NAME
,usr.user_id
,usr.user_name
,usr.last_logon_date
,jrs.salesrep_id
,jrs.resource_id
,jrs.salesrep_number
,jrs.name salesrep_name
,jrs.org_id sales_org_id
,jrs.start_date_active rep_start_date_active
,jrs.end_date_active rep_end_date_active

FROM
per_all_people_f papf
,per_all_people_f mgr
,per_all_assignments_f paaf
,per_jobs pj
,fnd_user usr
,jtf_rs_salesreps jrs

WHERE 1 = 1
AND papf.person_id = paaf.person_id (+)
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND NVL (papf.effective_end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date (+) AND NVL (paaf.effective_end_date (+), TRUNC (SYSDATE))
AND paaf.job_id = pj.job_id (+)
AND paaf.business_group_id = pj.business_group_id (+)
AND papf.party_id = usr.person_party_id (+)
AND TRUNC (SYSDATE) BETWEEN usr.start_date (+) AND NVL (usr.end_date (+) , TRUNC (SYSDATE))
AND paaf.supervisor_id = mgr.person_id (+)
AND TRUNC (SYSDATE) BETWEEN mgr.effective_start_date (+) AND NVL (mgr.effective_end_date (+), TRUNC (SYSDATE))
AND paaf.assignment_type (+) = 'E'
AND papf.person_id = jrs.person_id (+)
AND papf.full_name LIKE '%Behan, Linda%'
--AND papf.employee_number = '30880'
--AND papf.person_id = 36557
--AND mgr.employee_number = '141968'
--AND usr.user_name = 'AAOFFSHORE'
--AND usr.user_ID = 1234
--AND jrs.salesrep_id = 100009572
--AND jrs.resource_id = 100010609
--AND jrs.salesrep_number = 'XXSR100'
--AND jrs.name like 'Bashaar%'
;

2 comments:

Baraa Sarhan said...

will try , thanks

Rajas Jayawant said...

Hi Raju, need your help.
jtf_rs_resource_extns table not showing any records for the new user created, whereas i had added the 'employee' to System Admin ->Security->User creation page. How shall i link the employee to the user ?
Issue is when i create a new user and log into thee account->click on Sales Admin responsibility it shows the error message as 'Failed to get sales group ID or sales role for the current user. User must have at least one role in a valid sales group.'
what is wrong that i am doing ?

Post a Comment

Best Blogger TipsGet Flower Effect