–Changed the query to new syntax hence posting as new—–
I have created a query to retrieve employee information. There may be employees that do not have information in pay_payroll_actions table. For that, I have used the left join on the pay_payroll_actions table but it is not giving me output with employees that do not have info in this table.
Can someone pls help me with this query as to how can this be corrected?
select * from (SELECT DISTINCT PAPF.PERSON_NUMBER, NAME.FIRST_NAME, NAME.LAST_NAME, PAAM.ASSIGNMENT_NUMBER, PY.PAYROLL_NAME, PPA.ACTION_TYPE ACTION_TYPE_code, ( SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'ACTION_TYPE' AND LOOKUP_CODE = PPA.ACTION_TYPE AND LANGUAGE = 'US' AND ENABLED_FLAG = 'Y' ) ACTION_TYPE, TO_CHAR(PPA.EFFECTIVE_DATE, 'DD-MON-YYYY') EFFECTIVE_DATE, PPA.PAYROLL_ACTION_ID ACTION_NUMBER, PCS.CONSOLIDATION_SET_NAME, TO_CHAR(FINC, 'DD-MON-YYYY') FINC, PASV.USER_STATUS FROM PAY_ASSIGNED_PAYROLLS_DN PAPD JOIN PAY_PAYROLL_TERMS PT ON ( PAPD.PAYROLL_TERM_ID = PT.PAYROLL_TERM_ID ) JOIN PAY_PAY_RELATIONSHIPS_DN PR ON ( PT.PAYROLL_RELATIONSHIP_ID = PR.PAYROLL_RELATIONSHIP_ID ) JOIN PAY_ALL_PAYROLLS_F PY ON ( PAPD.PAYROLL_ID = PY.PAYROLL_ID ) JOIN PER_ALL_PEOPLE_F PAPF ON ( PR.PERSON_ID = PAPF.PERSON_ID ) JOIN PAY_CONSOLIDATION_SETS PCS ON ( PCS.CONSOLIDATION_SET_ID = PY.CONSOLIDATION_SET_ID ) JOIN PER_PERSON_NAMES_F NAME ON ( PAPF.PERSON_ID = NAME.PERSON_ID ) JOIN PAY_REL_GROUPS_DN PAYREL ON ( PAYREL.PARENT_REL_GROUP_ID = PAPD.PAYROLL_TERM_ID ) JOIN PER_ALL_ASSIGNMENTS_M PAAM ON ( PAYREL.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID ) JOIN PAY_PAYROLL_REL_ACTIONS PPRA ON ( PPRA.PAYROLL_RELATIONSHIP_ID = PR.PAYROLL_RELATIONSHIP_ID ) JOIN PER_ASSIGNMENT_STATUS_TYPES_VL PASV ON ( PASV.ASSIGNMENT_STATUS_TYPE_ID = PAAM.ASSIGNMENT_STATUS_TYPE_ID ) JOIN PER_LEGISLATIVE_DATA_GROUPS_VL LDG ON ( LDG.LEGISLATION_CODE = PAAM.LEGISLATION_CODE ) LEFT JOIN PAY_PAYROLL_ACTIONS PPA ON ( PPA.PAYROLL_ID = PY.PAYROLL_ID AND PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID -- TEJASH : SUGGESTION-1 : ADDED FOLLOWING CONDITIONS IN JOIN CONDITIONS INSTEAD OF WHERE CLAUSE -- AND PPA.ACTION_TYPE IS NULL -- CHANGED FOLLOWING CONDITION TO USE DATES RATHER THAN CHAR AND NVL(PPA.EFFECTIVE_DATE, DATE '2019-09-01') BETWEEN DATE '2019-09-01' AND DATE '2019-12-01' AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID AND NVL(PPA.EFFECTIVE_DATE,trunc(sysdate)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE AND NVL(PPA.EFFECTIVE_DATE,trunc(sysdate)) BETWEEN PAPD.START_DATE AND PAPD.END_DATE ) -- TEJASH : SUGGESTION-2 : USE LEFT JOIN HERE LEFT JOIN PAY_TIME_PERIODS PTP ON ( PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID AND PTP.PAYROLL_ID = PY.PAYROLL_ID ) WHERE 1 = 1 AND PAAM.ASSIGNMENT_TYPE = 'E' --ADDED condition--- AND PASV.USER_STATUS LIKE '%Payroll Eligible' AND ( FINC > PTP.START_DATE OR FINC IS NULL ) -- COMMENTED BY TEJASH -- AND ( PPA.ACTION_TYPE IS NULL -- OR ppa.action_type IN ( 'Q', 'R' ) -- ) -- AND ldg.name = Nvl(:P_LEGISLATIVE_GROUP, ldg.name) --AND pasv.user_status = Nvl(:P_ASSIGNMENT_STATUS, -- pasv.user_status) -- COMMENTED BY TEJASH --AND TO_CHAR(NVL(PPA.EFFECTIVE_DATE, '2019-09-01'), 'YYYY-MM-DD') BETWEEN ( '2019-09-01' ) AND ( '2019-12-01' ) ------- --AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID AND PAYREL.GROUP_TYPE = 'A' AND NAME.NAME_TYPE = 'GLOBAL' --AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE --AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAPD.START_DATE AND PAPD.END_DATE AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN PY.EFFECTIVE_START_DATE AND PY.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN NAME.EFFECTIVE_START_DATE AND NAME.EFFECTIVE_END_DATE ) where (ACTION_TYPE_code is null or ACTION_TYPE_code in ('Q','R'))
Advertisement
Answer
There are few issues which I have listed down as following
You have used this as
INNER JOIN
as following butON
condition consists of the column from a table:PAY_PAYROLL_ACTIONS
which will make the condition false if there is no data of employee inPAY_PAYROLL_ACTIONS
table.JOIN PAY_TIME_PERIODS PTP ON ( PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID )
You have used following 5 WHERE condition.
PPA.ACTION_TYPE IS NULL
TO_CHAR(NVL(PPA.EFFECTIVE_DATE, '2019-09-01'), 'YYYY-MM-DD') BETWEEN ( '2019-09-01' ) AND ( '2019-12-01' )
PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID
NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE
NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAPD.START_DATE AND PAPD.END_DATE
These conditions also affect the final result based on the presence of the data in table PAY_PAYROLL_ACTIONS
.
I am trying to give you the solution(see inline comments for a description of change) according to my knowledge of these tables as follows:
SELECT DISTINCT PAPF.PERSON_NUMBER, NAME.FIRST_NAME, NAME.LAST_NAME, PAAM.ASSIGNMENT_NUMBER, PY.PAYROLL_NAME, ( SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'ACTION_TYPE' AND LOOKUP_CODE = PPA.ACTION_TYPE AND LANGUAGE = 'US' AND ENABLED_FLAG = 'Y' ) ACTION_TYPE, TO_CHAR(PPA.EFFECTIVE_DATE, 'DD-MON-YYYY') EFFECTIVE_DATE, PPA.PAYROLL_ACTION_ID ACTION_NUMBER, PCS.CONSOLIDATION_SET_NAME, TO_CHAR(FINC, 'DD-MON-YYYY') FINC, PASV.USER_STATUS FROM PAY_ASSIGNED_PAYROLLS_DN PAPD JOIN PAY_PAYROLL_TERMS PT ON ( PAPD.PAYROLL_TERM_ID = PT.PAYROLL_TERM_ID ) JOIN PAY_PAY_RELATIONSHIPS_DN PR ON ( PT.PAYROLL_RELATIONSHIP_ID = PR.PAYROLL_RELATIONSHIP_ID ) JOIN PAY_ALL_PAYROLLS_F PY ON ( PAPD.PAYROLL_ID = PY.PAYROLL_ID ) JOIN PER_ALL_PEOPLE_F PAPF ON ( PR.PERSON_ID = PAPF.PERSON_ID ) JOIN PAY_CONSOLIDATION_SETS PCS ON ( PCS.CONSOLIDATION_SET_ID = PY.CONSOLIDATION_SET_ID ) JOIN PER_PERSON_NAMES_F NAME ON ( PAPF.PERSON_ID = NAME.PERSON_ID ) JOIN PAY_REL_GROUPS_DN PAYREL ON ( PAYREL.PARENT_REL_GROUP_ID = PAPD.PAYROLL_TERM_ID ) JOIN PER_ALL_ASSIGNMENTS_M PAAM ON ( PAYREL.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID ) JOIN PAY_PAYROLL_REL_ACTIONS PPRA ON ( PPRA.PAYROLL_RELATIONSHIP_ID = PR.PAYROLL_RELATIONSHIP_ID ) JOIN PER_ASSIGNMENT_STATUS_TYPES_VL PASV ON ( PASV.ASSIGNMENT_STATUS_TYPE_ID = PAAM.ASSIGNMENT_STATUS_TYPE_ID ) JOIN PER_LEGISLATIVE_DATA_GROUPS_VL LDG ON ( LDG.LEGISLATION_CODE = PAAM.LEGISLATION_CODE ) LEFT JOIN PAY_PAYROLL_ACTIONS PPA ON ( PPA.PAYROLL_ID = PY.PAYROLL_ID AND PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID -- TEJASH : SUGGESTION-1 : ADDED FOLLOWING CONDITIONS IN JOIN CONDITIONS INSTEAD OF WHERE CLAUSE AND PPA.ACTION_TYPE IS NULL -- CHANGED FOLLOWING CONDITION TO USE DATES RATHER THAN CHAR AND NVL(PPA.EFFECTIVE_DATE, DATE '2019-09-01') BETWEEN DATE '2019-09-01' AND DATE '2019-12-01' AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAPD.START_DATE AND PAPD.END_DATE ) -- TEJASH : SUGGESTION-2 : USE LEFT JOIN HERE LEFT JOIN PAY_TIME_PERIODS PTP ON ( PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID ) WHERE 1 = 1 AND PTP.PAYROLL_ID = PY.PAYROLL_ID AND PAAM.ASSIGNMENT_TYPE = 'E' --ADDED condition--- AND PASV.USER_STATUS LIKE '%Payroll Eligible' AND ( FINC > PTP.START_DATE OR FINC IS NULL ) -- COMMENTED BY TEJASH -- AND ( PPA.ACTION_TYPE IS NULL -- OR ppa.action_type IN ( 'Q', 'R' ) -- ) -- AND ldg.name = Nvl(:P_LEGISLATIVE_GROUP, ldg.name) --AND pasv.user_status = Nvl(:P_ASSIGNMENT_STATUS, -- pasv.user_status) -- COMMENTED BY TEJASH --AND TO_CHAR(NVL(PPA.EFFECTIVE_DATE, '2019-09-01'), 'YYYY-MM-DD') BETWEEN ( '2019-09-01' ) AND ( '2019-12-01' ) ------- --AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID AND PAYREL.GROUP_TYPE = 'A' AND NAME.NAME_TYPE = 'GLOBAL' --AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE --AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAPD.START_DATE AND PAPD.END_DATE AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN PY.EFFECTIVE_START_DATE AND PY.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN NAME.EFFECTIVE_START_DATE AND NAME.EFFECTIVE_END_DATE
Cheers!!