After writing my main query here, I defined my parameters by placing them inside the NVL functionSELECT PPNF.FIRST_NAME,
PPNF.LAST_NAME,
PPNF.FULL_NAME,
PAPF.PERSON_NUMBER,
TO_CHAR (PPOS.DATE_START, ‘dd.mm.yyyy’)
AS START_DATE,
HAPTL.NAME
AS POSITION,
HOUFL.NAME
AS DEPARTMAN,
PJFL.NAME
AS JOB,
PL.INTERNAL_LOCATION_CODE,
PLFL.LOCATION_NAME,
PNI.NATIONAL_IDENTIFIER_NUMBER,
CS.SALARY_AMOUNT,
CS.CURRENCY_CODE,
(SELECT flvg.meaning
FROM FND_LOOKUP_VALUES FLVG
WHERE flvg.lookup_type = ‘SEX’
AND flvg.language = ‘TR’
AND flvg.lookup_code = pplf.sex)
AS GENDER,
TO_CHAR (PP.DATE_OF_BIRTH, ‘dd.mm.yyyy’)
AS BIRTH_DATE,
PP.TOWN_OF_BIRTH,
(SELECT FLVA.MEANING
FROM FND_LOOKUP_VALUES FLVA
WHERE FLVA.LOOKUP_CODE = PAAM.EMPLOYMENT_CATEGORY
AND FLVA.LANGUAGE = ‘TR’
AND FLVA.LOOKUP_TYPE = ‘EMP_CAT’)
AS CALISMAS,
(CASE
WHEN PAAM.ASS_ATTRIBUTE2 IS NOT NULL THEN PAAM.ASS_ATTRIBUTE2
WHEN HAPF.ATTRIBUTE1 IS NOT NULL THEN HAPF.ATTRIBUTE1
ELSE HOU.ATTRIBUTE1
END)
AS MASRAF,
(SELECT PEA.EMAIL_ADDRESS
FROM PER_EMAIL_ADDRESSES PEA
WHERE PEA.DATE_FROM <= SYSDATE
AND NVL (PEA.DATE_TO, SYSDATE + 1) > SYSDATE
AND PEA.PERSON_ID = papf.PERSON_ID
AND PEA.EMAIL_TYPE IN (‘W1’, ‘H1’)
AND PEA.EMAIL_ADDRESS_ID =
(SELECT MAX (PEA2.EMAIL_ADDRESS_ID)
FROM PER_EMAIL_ADDRESSES PEA2
WHERE PEA2.DATE_FROM <= SYSDATE
AND NVL (PEA2.DATE_TO, SYSDATE + 1) > SYSDATE
AND PEA2.EMAIL_TYPE IN (‘W1’, ‘H1’)
AND PEA2.PERSON_ID = PEA.PERSON_ID))
AS EMAIL,
(SELECT PPH.SEARCH_PHONE_NUMBER
FROM PER_PHONES PPH
WHERE PPH.DATE_FROM <= SYSDATE
AND NVL (PPH.DATE_TO, SYSDATE + 1) > SYSDATE
AND PPH.PERSON_ID = papf.PERSON_ID
AND PPH.PHONE_TYPE IN (‘W1’, ‘H1’)
AND PPH.PHONE_ID =
(SELECT MAX (PPH2.PHONE_ID)
FROM PER_PHONES PPH2
WHERE PPH2.DATE_FROM <= SYSDATE
AND NVL (PPH2.DATE_TO, SYSDATE + 1) > SYSDATE
AND PPH2.PHONE_TYPE IN (‘W1’, ‘H1’)
AND PPH2.PERSON_ID = PPH.PERSON_ID)) AS PHONE ,
TRUNC(MONTHS_BETWEEN(SYSDATE, PP.DATE_OF_BIRTH) / 12) AS AGE
FROM PER_PERSON_NAMES_F PPNF,
PER_ALL_PEOPLE_F PAPF,
HR_ALL_POSITIONS_F HAPF,
HR_ALL_POSITIONS_F_TL HAPTL,
PER_JOBS_F PJF,
PER_JOBS_F_TL PJFL,
PER_ALL_ASSIGNMENTS_M PAAM,
HR_ORGANIZATION_UNITS HOU,
HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF,
HR_ORGANIZATION_UNITS_F_TL HOUFL,
PER_LOCATIONS PL,
PER_LOCATION_DETAILS_F PLFD,
PER_LOCATION_DETAILS_F_TL PLFL,
PER_PERSONS PP,
PER_NATIONAL_IDENTIFIERS PNI,
(SELECT *
FROM CMP_SALARY a
WHERE A.SALARY_ID = (SELECT MAX (B.SALARY_ID)
FROM CMP_SALARY B
WHERE A.PERSON_ID = B.PERSON_ID)) CS,
PER_PEOPLE_LEGISLATIVE_F PPLF,
(SELECT *
FROM PER_PERIODS_OF_SERVICE a
WHERE a.primarY_flag = ‘Y’
AND A.PERIOD_OF_SERVICE_ID =
(SELECT MAX (B.PERIOD_OF_SERVICE_ID)
FROM PER_PERIODS_OF_SERVICE B
WHERE B.primary_flag = ‘Y’ AND A.PERSON_ID = B.PERSON_ID))
PPOS
WHERE –PAPF.PERSON_NUMBER = ‘Test051223’
TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE
AND PPNF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE
AND HAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN HAPTL.EFFECTIVE_START_DATE
AND HAPTL.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN HOUCF.EFFECTIVE_START_DATE
AND HOUCF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN HOUFL.EFFECTIVE_START_DATE
AND HOUFL.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PLFD.EFFECTIVE_START_DATE
AND PLFD.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PLFL.EFFECTIVE_START_DATE
AND PLFL.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPLF.EFFECTIVE_START_DATE
AND PPLF.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PPOS.PRIMARY_FLAG = ‘Y’
AND PPNF.NAME_TYPE = ‘GLOBAL’
AND HAPF.POSITION_ID = HAPTL.POSITION_ID
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND PAAM.POSITION_ID = HAPF.POSITION_ID
AND PAAM.JOB_ID = PJF.JOB_ID
AND PJF.JOB_ID = PJFL.JOB_ID
AND PL.LOCATION_ID = PAAM.LOCATION_ID
AND PL.LOCATION_ID = PLFD.LOCATION_ID
AND PLFD.LOCATION_ID = PAAM.LOCATION_ID
AND PAPF.PERSON_ID = PP.PERSON_ID
AND PAPF.PERSON_ID = PNI.PERSON_ID(+)
AND PLFD.LOCATION_DETAILS_ID = PLFL.LOCATION_DETAILS_ID
AND PAPF.PERSON_ID = CS.PERSON_ID(+)
AND PAPF.PERSON_ID = PPLF.PERSON_ID (+)
AND PAAM.ASSIGNMENT_TYPE IN (‘E’, ‘C’)
AND PAAM.ASSIGNMENT_STATUS_TYPE = ‘ACTIVE’
AND HAPTL.LANGUAGE = ‘US’
AND PJFL.LANGUAGE = ‘US’
AND HOUCF.CLASSIFICATION_CODE = ‘DEPARTMENT’
AND HOUCF.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = HOUFL.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = PAAM.ORGANIZATION_ID
AND HOUFL.LANGUAGE = ‘US’
AND PLFL.LANGUAGE = ‘US’
AND PPLF.LEGISLATION_CODE (+) = ‘TR’
— AND PAPF.PERSON_NUMBER = ‘18658’
AND PNI.NATIONAL_IDENTIFIER_TYPE(+) = ‘ORA_HRX_TC_KIMLIK_NO’ –Parameters
AND NVL (PAPF.PERSON_NUMBER, ‘1’) =
NVL ( :P_PersonNumber, NVL (PAPF.PERSON_NUMBER, ‘1’))
AND NVL (PLFL.LOCATION_NAME, ‘X’) =
NVL ( :P_LOCATIONNAME, NVL (PLFL.LOCATION_NAME, ‘X’))
Here, I add parameters and click OK
Then, I created two List of Views for these parameters. Here, I am selecting the HCM Data Source.
Next, I wrote the query for the location that I will use.
To avoid repeating the same location names, I use DISTINCT.
After connecting the location tables with each other and checking the validity date, I sorted the location names in ascending order.SELECT DISTINCT (fldtl.location_name)
FROM PER_LOCATION_DETAILS_F FLD, PER_LOCATION_DETAILS_F_TL FLDTl
WHERE FLD.LOCATION_DETAILS_ID = FLDTL.LOCATION_DETAILS_ID(+)
AND TRUNC (SYSDATE) BETWEEN FLDTL.EFFECTIVE_START_DATE(+)
AND FLDTL.EFFECTIVE_END_DATE(+)
AND TRUNC (SYSDATE) BETWEEN fld.effective_start_date
AND fld.effective_end_date
ORDER BY fldtl.location_name ASC
Then, I selected the Personnel number with DISTINCT and added the location tables and my Assignment table because I will connect it with my Location parameter.
After checking their validity dates, I entered the conditions. Then, I input the location name into my parameter (here, it must be exactly the same as its name).SELECT DISTINCT PAPF.PERSON_NUMBER
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_LOCATION_DETAILS_F PLF,
PER_LOCATION_DETAILS_F_TL PLFL
WHERE TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PLF.EFFECTIVE_START_DATE
AND PLF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PLFL.EFFECTIVE_START_DATE
AND PLFL.EFFECTIVE_END_DATE
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND PLF.LOCATION_ID = PAAM.LOCATION_ID
AND PLFL.LOCATION_DETAILS_ID = PLF.LOCATION_DETAILS_ID
AND PLFL.LANGUAGE = ‘US’
AND PAAM.ASSIGNMENT_TYPE IN (‘E’, ‘C’)
AND PLFL.LOCATION_NAME =:P_LOCATIONNAME
Next, I selected the Menu in the Parameter Type and linked the List of Values to the parameters. After that, I click on the Refresh Parameters button to update the parameters with the changes.
When we go from Data to View, we see that Pernr does not appear initially. However, when I select the location (Unfortunately, I need to keep its name confidential), I observe that the personnel numbers are displayed. We can view the employees working at that location number.
No Comments