SQL Queries

SQL queries are used to retrieve, analyze, filter, combine, and manipulate data stored in relational databases. A few examples are listed below.

Fetch Legal Entities along with Business Unit

SELECT xep.name LEGAL_ENTITY_NAME, xep.LEGAL_ENTITY_ID, ffbuv.PRIMARY_LEDGER_ID, ffbuv.BU_NAME, ffbuv.SHORT_CODE, ffbuv.BU_ID, ffbuv.LOCATION_ID, ffbuv.DATE_FROM, ffbuv.DATE_TO
FROM fun_all_business_units_v ffbuv,xle_entity_profiles xep
WHERE ffbuv.legal_entity_id = xep.legal_entity_id
AND TRUNC (SYSDATE) BETWEEN ffbuv.DATE_FROM AND ffbuv.DATE_TO

Fetch Departments along Sets

SELECT hauft.ORGANIZATION_ID, hauft.NAME, hauft.TITLE, houcf.SET_ID, SET_NAME, SET_CODE
FROM HR_ORGANIZATION_UNITS_F_TL hauft, HR_ORG_UNIT_CLASSIFICATIONS_F houcf, fnd_setid_sets fss
WHERE hauft.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND houcf.set_id = fss.set_id
AND hauft.LANGUAGE = fss.LANGUAGE
AND hauft.LANGUAGE = 'US'
AND SYSDATE BETWEEN hauft.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'

List of Positions by Business Unit

SELECT hapf.POSITION_CODE, hapft.name, hapf.ATTRIBUTE1 arabic_name_dff, hapf.POSITION_ID, hapf.BUSINESS_UNIT_ID, ffbuv.BU_NAME, hapf.ORGANIZATION_ID, hapf.LOCATION_ID, hapf.JOB_ID, hapf.ACTIVE_STATUS, hapf.HIRING_STATUS, hapf.POSITION_TYPE, hapf.FULL_PART_TIME, hapf.FTE, hapf.MAX_PERSONS
FROM hr_all_positions_f_tl hapft, hr_all_positions_f hapf, fun_all_business_units_v ffbuv
where hapft.position_id = hapf.position_id
and hapf.BUSINESS_UNIT_ID = ffbuv.BU_ID
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(hapft.effective_start_date, sysdate) ) AND  TRUNC(NVL(hapft.effective_end_date,sysdate) )
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(hapf.effective_start_date, sysdate) ) AND  TRUNC(NVL(hapf.effective_end_date,sysdate) )
AND LANGUAGE = 'US'

Bank detail by employee

SELECT 
	pba.bank_name bank_name,
	pba.bank_branch_name ,
	pba.bank_account_num clear_bank_account_number,
	pba.iban_number clear_iban,
	cbv.bank_number,
	papf.person_id,
	papf.person_number,
	popf.BASE_ORG_PAY_METHOD_NAME
FROM 
	pay_bank_accounts pba,
	pay_person_pay_methods_f pppmf,
	pay_org_pay_methods_f popf,
	(select * 
	   from pay_pay_relationships_dn a 
	  where a.payroll_relationship_id = 
			(select max (payroll_relationship_id) 
			   from pay_pay_relationships_dn b 
			  where a.person_id = b.person_id)) ppr,
	per_all_people_f papf,
	ce_banks_v cbv
 WHERE     pppmf.bank_account_id = pba.bank_account_id(+)
	   AND pppmf.org_payment_method_id = popf.org_payment_method_id
	   AND ppr.payroll_relationship_id = pppmf.payroll_relationship_id
	   AND papf.person_id = ppr.person_id
	   AND (papf.person_number IN (:p_person_number) OR LEAST (:p_person_number) IS NULL)
	   AND cbv.bank_name(+) = pba.bank_name
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (pppmf.effective_start_date,sysdate)) AND TRUNC (NVL (pppmf.effective_end_date,sysdate))
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (papf.effective_start_date,sysdate)) AND TRUNC (NVL (papf.effective_end_date,sysdate))
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (popf.effective_start_date,sysdate)) AND TRUNC (NVL (popf.effective_end_date,sysdate))
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (pba.start_date,sysdate)) AND TRUNC (NVL (pba.end_date,sysdate))
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (ppr.start_date,sysdate)) AND TRUNC (NVL (ppr.end_date,sysdate))

Detail of People Group by employee

SELECT  papf.person_number,
		papf.person_id per_id,
		paam.assignment_id,
		ppnf.display_name,
		ppg.SEGMENT1 FIELD1,
		ppg.SEGMENT2 FIELD2,
		ppg.SEGMENT3 FIELD3,
		ppg.SEGMENT4 FIELD4,
		ppg.SEGMENT5 FIELD5,
		ppg.segment6 FIELD6,
		ppg.segment7 FIELD7,
		ppg.segment8 FIELD8,
		ppg.SEGMENT9 FIELD9,
		ppg.SEGMENT10 FIELD10,
		ppg.SEGMENT11 FIELD11,
		ppg.SEGMENT12 FIELD12,
		paam.assignment_status_type
FROM     per_person_secured_list_v papf
		,per_all_assignments_f paam
		,per_persons pp
		,per_people_groups ppg
		,per_person_names_f ppnf
WHERE   papf.person_id = paam.person_id
and    	papf.person_id = ppnf.person_id(+) --name may be missing
AND 	papf.person_id = pp.person_id(+) --name may be missing
AND     ppg.people_group_id(+) = paam.people_group_id --- people group segments may be missing--
AND     paam.assignment_type = 'E'         -- Default Conditions
AND     paam.effective_latest_change = 'Y' -- Default Conditions
AND     paam.primary_flag = 'Y'           ---primary assignment
AND     UPPER (paam.assignment_status_type) = 'ACTIVE'
AND     ppnf.name_type = 'GLOBAL'
AND     TRUNC (paam.effective_start_date) BETWEEN TRUNC ( ppnf.effective_start_date) AND      TRUNC ( ppnf.effective_end_date)
AND     TRUNC (paam.effective_start_date) BETWEEN TRUNC ( papf.effective_start_date) AND      TRUNC ( papf.effective_end_date)
AND     NVL(:p_start_date,TRUNC(sysdate)) BETWEEN TRUNC ( paam.effective_start_date) AND      TRUNC (paam.effective_end_date)
AND 	(papf.person_number IN (:p_person_number) OR LEAST (:p_person_number) IS NULL)

Detail of Managers by Employee

SELECT ppnf.FIRST_NAME||' '||ppnf.NAM_INFORMATION15||' '||ppnf.NAM_INFORMATION16||' '||ppnf.LAST_NAME manager_name
FROM per_person_names_f            ppnf,
   per_assignment_supervisors_f  sup,
   per_person_secured_list_v     papf1,
   per_person_secured_list_v     mgr_papf
WHERE     ppnf.person_id = sup.manager_id
   AND papf1.person_id = sup.person_id
   AND TRUNC(sysdate) BETWEEN TRUNC (sup.effective_start_date)
						  AND TRUNC (sup.effective_end_date)
   AND TRUNC(sysdate) BETWEEN TRUNC (papf1.effective_start_date)
						  AND TRUNC (papf1.effective_end_date)
   AND TRUNC(sysdate) BETWEEN TRUNC (ppnf.effective_start_date)
						  AND TRUNC (ppnf.effective_end_date)
   AND UPPER (ppnf.name_type) = 'GLOBAL' --should fetch these details only
   AND sup.manager_type = 'LINE_MANAGER' --- to fetch technical manager detail
   AND mgr_papf.person_id = sup.manager_id
   AND papf1.person_id = pasf.manager_id
   AND TRUNC(sysdate) BETWEEN TRUNC (mgr_papf.effective_start_date)
						  AND TRUNC (mgr_papf.effective_end_date)

List of Nationalities

SELECT Max(fl.meaning)
  FROM fnd_lookup_VALUEs_tl fl
 WHERE UPPER(TRIM(fl.lookup_type)) = 'NATIONALITY' --to get nationality
   AND fl.lookup_code (+) = pcz.legislation_code    --to get all records though legislation  details not included for all
   AND fl.language = userenv('LANG')

Legislative by country

SELECT * 
  FROM per_people_legislative_f 
 WHERE LEGISLATION_CODE = 'KW'

Employee passports detail

select person_id, PASSPORT_NUMBER, PASSPORT_TYPE, ISSUING_AUTHORITY, ISSUING_COUNTRY, ISSUE_DATE, ISSUING_LOCATION, EXPIRATION_DATE
from per_passports where LEGISLATION_CODE = 'KW'