SQL ID: 4pu9xkscfkqtg Plan Hash: 1504126373 SELECT /*+ STAR_TRANSFORMATION */ PAA.ASSIGNMENT_ID, DECODE(SIGN(NVL(PPS.ACTUAL_TERMINATION_DATE,TO_DATE('31/12/4712', 'DD/MM/YYYY')) - :B1 ),1, NULL,PPS.ACTUAL_TERMINATION_DATE) ACTUAL_TERMINATION_DATE, PEEV.SCREEN_ENTRY_VALUE TAX_FILE_NUMBER FROM PAY_PAYROLL_ACTIONS PPA ,PER_PEOPLE_F PAP ,PER_ASSIGNMENTS_F PAA , HR_SOFT_CODING_KEYFLEX HSC ,PER_PERIODS_OF_SERVICE PPS , PAY_ELEMENT_ENTRIES_F PEE ,PAY_ELEMENT_LINKS_F PEL ,PAY_ELEMENT_TYPES_F PET ,PAY_INPUT_VALUES_F PIV ,PAY_ELEMENT_ENTRY_VALUES_F PEEV WHERE PPA.PAYROLL_ACTION_ID = :B6 AND PAP.PERSON_ID BETWEEN :B5 AND :B4 AND PAP.PERSON_ID = PAA.PERSON_ID AND PAA.BUSINESS_GROUP_ID = :B3 AND PAP.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID AND PAA.SOFT_CODING_KEYFLEX_ID = HSC.SOFT_CODING_KEYFLEX_ID AND HSC.SEGMENT1 = :B2 AND PPS.PERSON_ID = PAA.PERSON_ID AND PPS.PERIOD_OF_SERVICE_ID = PAA.PERIOD_OF_SERVICE_ID AND PPS.DATE_START= (SELECT MAX(PPS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPS1 WHERE PPS1.PERSON_ID=PPS.PERSON_ID AND PPS1.DATE_START <= :B1 ) AND PAA.EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE) FROM PER_ASSIGNMENTS_F A WHERE A.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND A.EFFECTIVE_START_DATE <= :B1 GROUP BY A.ASSIGNMENT_ID ) AND PAP.EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE) FROM PER_PEOPLE_F P WHERE P.PERSON_ID = PAP.PERSON_ID AND P.EFFECTIVE_START_DATE <= :B1 GROUP BY P.PERSON_ID) AND PET.ELEMENT_NAME = 'Tax Information' AND PEL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PEE.ENTRY_INFORMATION_CATEGORY = 'AU_TAX DEDUCTIONS' AND ((TRUNC(FND_DATE.CANONICAL_TO_DATE(PEE.ENTRY_INFORMATION1)) BETWEEN :B1 -13 AND :B1 ) OR (NVL(PPS.ACTUAL_TERMINATION_DATE,TO_DATE('31/12/4712', 'DD/MM/YYYY')) BETWEEN :B1 - 13 AND :B1 AND PEEV.SCREEN_ENTRY_VALUE = '111 111 111')) AND PIV.NAME = 'Tax File Number' AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PEEV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID AND PEEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND PEE.EFFECTIVE_START_DATE =
(SELECT MAX(PEE1.EFFECTIVE_START_DATE) FROM PAY_ELEMENT_TYPES_F PET1 , PAY_ELEMENT_LINKS_F PEL1 ,PAY_ELEMENT_ENTRIES_F PEE1 WHERE PET1.ELEMENT_NAME = 'Tax Information' AND PET1.ELEMENT_TYPE_ID = PEL1.ELEMENT_TYPE_ID AND PEL1.ELEMENT_LINK_ID = PEE1.ELEMENT_LINK_ID AND PEE1.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PEE1.ENTRY_INFORMATION1 IS NOT NULL AND PEE1.EFFECTIVE_START_DATE <= :B1 AND PEL1.EFFECTIVE_START_DATE BETWEEN PET1.EFFECTIVE_START_DATE AND PET1.EFFECTIVE_END_DATE ) AND PEEV.EFFECTIVE_START_DATE = (SELECT MAX(PEEV1.EFFECTIVE_START_DATE) FROM PAY_ELEMENT_ENTRY_VALUES_F PEEV1 WHERE PEEV1.ELEMENT_ENTRY_VALUE_ID = PEEV.ELEMENT_ENTRY_VALUE_ID AND PEEV1.EFFECTIVE_START_DATE <= :B1 ) AND :B1 BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE AND :B1 BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND :B1 BETWEEN PEL.EFFECTIVE_START_DATE AND PEL.EFFECTIVE_END_DATE AND :B1 BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND NOT EXISTS (SELECT 1 FROM PAY_PAYROLL_ACTIONS PPA, PAY_ASSIGNMENT_ACTIONS PAC WHERE PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID AND FND_DATE.CANONICAL_TO_DATE(PAY_CORE_UTILS.GET_PARAMETER('REPORT_END_DATE', PPA.LEGISLATIVE_PARAMETERS)) BETWEEN :B1 - 13 AND :B1 AND PAC.ACTION_STATUS = 'C' AND PPA.ACTION_TYPE = 'X' AND PPA.REPORT_TYPE = 'AU_TFN_MAGTAPE')
SQL ID: 6fgsk6zhw60uw Plan Hash: 1186147916 SELECT PEE.ASSIGNMENT_ID ,DECODE(EEV0.SCREEN_ENTRY_VALUE,'YS','Y','YI','Y', 'YC','Y','NN','N','YN','Y','Y','Y','N','N',NULL) , EEV1.SCREEN_ENTRY_VALUE , DECODE( EEV3.SCREEN_ENTRY_VALUE, 'N', 'N', 'Y', 'Y', 'NF','N', 'NP','N', 'NC','N', 'YF','Y', 'YP','Y', 'YC','Y', 'N' ) , DECODE( EEV3.SCREEN_ENTRY_VALUE, 'Y', 'X', 'N', 'X', 'NF','F', 'NP','P', 'NC','C', 'YF','F', 'YP','P', 'YC','C', 'X') , DECODE(EEV5.SCREEN_ENTRY_VALUE,'Y','Y', 'N','N','YY','Y','NY','N',NULL) HECS_FLAG , DECODE(EEV5.SCREEN_ENTRY_VALUE, 'YY','Y','NY','Y','N') SFSS_ENTRY_VALUE , TO_CHAR(FND_DATE.CANONICAL_TO_DATE(EEV6.SCREEN_ENTRY_VALUE),'ddmmyyyy') , DECODE(DECODE(EEV8.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N','N') ,'Y','Y',DECODE(EEV4.SCREEN_ENTRY_VALUE,'Y','Y','N')) , EEV13.SCREEN_ENTRY_VALUE , PEE.EFFECTIVE_START_DATE , DECODE(SIGN(NVL(PPS.ACTUAL_TERMINATION_DATE,TO_DATE('31/12/4712', 'DD/MM/YYYY')) - :B1 ), 1,NULL,PPS.ACTUAL_TERMINATION_DATE) ACTUAL_TERMINATION_DATE , DECODE(EEV0.SCREEN_ENTRY_VALUE,'YS','Y','YI','Y', 'YC','Y','NN','N','YN','N','Y','N','N','N',NULL) FROM PER_PEOPLE_F PAP , PER_ALL_ASSIGNMENTS_F PAA, HR_SOFT_CODING_KEYFLEX HSC, PER_PERIODS_OF_SERVICE PPS , PAY_ELEMENT_ENTRIES_F PEE , PAY_ELEMENT_TYPES_F PET , PAY_INPUT_VALUES_F PIV0 , PAY_ELEMENT_ENTRY_VALUES_F EEV0 , PAY_INPUT_VALUES_F PIV1 , PAY_ELEMENT_ENTRY_VALUES_F EEV1 , PAY_INPUT_VALUES_F PIV3 , PAY_ELEMENT_ENTRY_VALUES_F EEV3 , PAY_INPUT_VALUES_F PIV4 , PAY_ELEMENT_ENTRY_VALUES_F EEV4 , PAY_INPUT_VALUES_F PIV5 , PAY_ELEMENT_ENTRY_VALUES_F EEV5 , PAY_INPUT_VALUES_F PIV6 , PAY_ELEMENT_ENTRY_VALUES_F EEV6 , PAY_INPUT_VALUES_F PIV8 , PAY_ELEMENT_ENTRY_VALUES_F EEV8 , PAY_INPUT_VALUES_F PIV13 , PAY_ELEMENT_ENTRY_VALUES_F EEV13 , HR_LOOKUPS HRL0 , HR_LOOKUPS HRL1 , HR_LOOKUPS HRL3 , HR_LOOKUPS HRL4 , HR_LOOKUPS HRL5 , HR_LOOKUPS HRL8 WHERE PAP.BUSINESS_GROUP_ID=:B3 AND PAA.BUSINESS_GROUP_ID = PAP.BUSINESS_GROUP_ID AND PAP.PERSON_ID=PAA.PERSON_ID AND PPS.PERSON_ID=PAA.PERSON_ID AND PAA.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID AND PAA.SOFT_CODING_KEYFLEX_ID = HSC.SOFT_CODING_KEYFLEX_ID AND HSC.SEGMENT1 = :B2 AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PPS.PERSON_ID = PAA.PERSON_ID AND PPS.DATE_START= (SELECT MAX(PPS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPS1 WHERE PPS1.PERSON_ID=PPS.PERSON_ID AND PPS1.DATE_START <= :B1 ) AND ( PEE.ENTRY_INFORMATION_CATEGORY = 'AU_TAX DEDUCTIONS' AND (TRUNC(FND_DATE.CANONICAL_TO_DATE(PEE.ENTRY_INFORMATION1)) BETWEEN :B1 - 13 AND :B1 OR NVL(PPS.ACTUAL_TERMINATION_DATE, TO_DATE('31/12/4712','DD/MM/YYYY')) BETWEEN :B1 - 13 AND :B1 ) ) AND PAA.EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE) FROM PER_ASSIGNMENTS_F A WHERE A.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID) AND PAP.EFFECTIVE_START_DATE=(SELECT MAX(EFFECTIVE_START_DATE) FROM PER_PEOPLE_F P WHERE P.PERSON_ID=PAP.PERSON_ID) AND PEE.EFFECTIVE_START_DATE = (SELECT MAX(PEE1.EFFECTIVE_START_DATE) FROM PAY_ELEMENT_TYPES_F PET1 ,PAY_ELEMENT_LINKS_F PEL1 ,PAY_ELEMENT_ENTRIES_F PEE1 WHERE PET1.ELEMENT_NAME = 'Tax Information' AND PET1.ELEMENT_TYPE_ID =
PEL1.ELEMENT_TYPE_ID AND PEL1.ELEMENT_LINK_ID = PEE1.ELEMENT_LINK_ID AND PEE1.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PEE1.ENTRY_INFORMATION1 IS NOT NULL AND PEE1.EFFECTIVE_START_DATE <= :B1 AND PEL1.EFFECTIVE_START_DATE BETWEEN PET1.EFFECTIVE_START_DATE AND PET1.EFFECTIVE_END_DATE ) AND PET.ELEMENT_NAME= 'Tax Information' AND PET.ELEMENT_TYPE_ID = PIV0.ELEMENT_TYPE_ID AND EEV0.INPUT_VALUE_ID = PIV0.INPUT_VALUE_ID AND EEV0.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND (PIV0.NAME) = 'Australian Resident' AND HRL0.LOOKUP_TYPE (+) = 'AU_AUST_RES_SENR_AUS' AND HRL0.LOOKUP_CODE (+) = EEV0.SCREEN_ENTRY_VALUE AND HRL0.ENABLED_FLAG (+)= 'Y' AND EEV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID AND EEV1.ELEMENT_ENTRY_ID
= PEE.ELEMENT_ENTRY_ID AND PIV1.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND (PIV1.NAME) = 'Tax Free Threshold' AND HRL1.LOOKUP_TYPE (+) = 'YES_NO' AND HRL1.LOOKUP_CODE (+) = EEV1.SCREEN_ENTRY_VALUE AND HRL1.ENABLED_FLAG (+)= 'Y' AND EEV3.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID AND PIV3.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND EEV3.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND (PIV3.NAME) = 'FTA Claim' AND HRL3.LOOKUP_TYPE (+) = 'HR_AU_FTA_PAYMENT_BASIS' AND HRL3.LOOKUP_CODE (+) = EEV3.SCREEN_ENTRY_VALUE AND HRL3.ENABLED_FLAG (+) = 'Y' AND EEV4.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID AND PIV4.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND EEV4.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND (PIV4.NAME) = 'Savings Rebate' AND HRL4.LOOKUP_TYPE(+) = 'YES_NO' AND HRL4.LOOKUP_CODE(+) = EEV4.SCREEN_ENTRY_VALUE AND HRL4.ENABLED_FLAG (+) = 'Y' AND EEV5.INPUT_VALUE_ID = PIV5.INPUT_VALUE_ID AND PIV5.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND EEV5.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND (PIV5.NAME) = 'HECS' AND HRL5.LOOKUP_TYPE(+) = 'AU_HECS_SFSS' AND HRL5.LOOKUP_CODE (+) = EEV5.SCREEN_ENTRY_VALUE AND HRL5.ENABLED_FLAG (+) = 'Y' AND EEV6.INPUT_VALUE_ID = PIV6.INPUT_VALUE_ID AND PIV6.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND EEV6.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND (PIV6.NAME) = 'Date Declaration Signed' AND EEV8.INPUT_VALUE_ID = PIV8.INPUT_VALUE_ID AND PIV8.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND EEV8.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND (PIV8.NAME) = 'Spouse' AND HRL8.LOOKUP_TYPE (+) = 'AU_SPOUSE_MLS' AND HRL8.LOOKUP_CODE (+) = EEV8.SCREEN_ENTRY_VALUE AND HRL8.ENABLED_FLAG (+) = 'Y' AND EEV13.INPUT_VALUE_ID = PIV13.INPUT_VALUE_ID AND PIV13.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND EEV13.ELEMENT_ENTRY_ID= PEE.ELEMENT_ENTRY_ID AND (PIV13.NAME ) = 'Tax File Number' AND PEE.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND EEV0.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND EEV1.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND EEV3.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND EEV4.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND EEV5.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND EEV6.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND EEV8.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND EEV13.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND EEV0.EFFECTIVE_START_DATE BETWEEN PIV0.EFFECTIVE_START_DATE AND PIV0.EFFECTIVE_END_DATE AND EEV1.EFFECTIVE_START_DATE BETWEEN PIV1.EFFECTIVE_START_DATE AND PIV1.EFFECTIVE_END_DATE AND EEV3.EFFECTIVE_START_DATE BETWEEN PIV3.EFFECTIVE_START_DATE AND PIV3.EFFECTIVE_END_DATE AND EEV4.EFFECTIVE_START_DATE BETWEEN PIV4.EFFECTIVE_START_DATE AND PIV4.EFFECTIVE_END_DATE AND EEV5.EFFECTIVE_START_DATE BETWEEN PIV5.EFFECTIVE_START_DATE AND PIV5.EFFECTIVE_END_DATE AND EEV6.EFFECTIVE_START_DATE BETWEEN PIV6.EFFECTIVE_START_DATE AND PIV6.EFFECTIVE_END_DATE AND EEV8.EFFECTIVE_START_DATE BETWEEN PIV8.EFFECTIVE_START_DATE AND PIV8.EFFECTIVE_END_DATE AND EEV13.EFFECTIVE_START_DATE BETWEEN PIV13.EFFECTIVE_START_DATE AND PIV13.EFFECTIVE_END_DATE
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
