Electronic Lodgement of TFN Declaration Process Has Poor Performance

Last updated on MARCH 08, 2017

Applies to:

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