Electronic Lodgement of TFN Declaration Process Has Poor Performance
(Doc ID 1638404.1)
Last updated on FEBRUARY 24, 2019
Applies to:
Oracle HRMS (Australia) - Version 12.1.3 to 12.2.3 [Release 12.1 to 12.2]Information in this document applies to any platform.
Symptoms
On : 12.1.3 version, Australian Payroll
On:
pyautfnf.pkb 120.5.12010000.7
pyautfn.pkb 120.7.12010000.7
Find there is performance issue with Electronic Lodgement of TFN Declaration process and after the report is completed, it does not show any output.
Problem sqls identified from a run that ran for 1.5 hours
******************************************************************************
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')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------
Parse 1 0.00 0.00 0 0 0 0
Execute 4992 0.69 0.72 0 0 0 0
Fetch 4992 143.05 2649.67 379983 3563920 0 0
------- ------ -------- ---------- ---------- ---------- ---------- -------
total 9985 143.74 2650.39 379983 3563920 0 0
******************************************************************************
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
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.81 3.86 0 0 0 0
Fetch 1 163.18 2403.67 197815 6980340 0 0
------- ------ -------- ---------- ---------- ---------- ---------- -------
total 3 166.99 2407.54 197815 6980340 0 0
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Electronic Lodgement of TFN Declaration
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |