add

About Me

My photo
Oracle Apps - Techno Functional consultant

Tuesday, June 21

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%'
;

No comments: