About Configuring E-Business Suite Accrual Extract Mode

Optional or Mandatory

This is a mandatory step. The default value of HR_ACCRUAL_EXTRACT_MODE must be reviewed and its impact must be understood before setting a value or continuing with the default value, which is NOPARALLEL.

Applies to

This applies to all extracts done for Accrual Module from E-Business Suite 11.1.10 and R12.x.x.

Task description

The E-Business Suite Accrual uses fast formula calls for extracting accrual data for assignments. Fast formula function calls are inherently slow and might cause performance problems. However if the number of assignments are less and/or number of periods of history being collected is small, the time taken to call the fast formulas for the various metrics should be reviewed.

There are 3 modes that can be used:

• NOPARALLEL: This value is used when Accruals Extraction runs in single a thread mode. This ensures that Accrual extraction is done as a part of the Accrual Load Plan. Permissions in E-Business Suite source schemas to create pl/sql package is needed by ODI for this mode to work. Can be used when data load is less, for example, In Incremental Load or when HR_ACCRUAL_EXTRACT_DATE parameter is set to a smaller value. This is also the DEFAULT extraction mode by default.

• PARALLEL: This value is used when Accruals Extraction runs in Parallel threads. This will improve loading speed. To configure this mode we must assign a value to HR_ACCRUAL_THREADS_TOTAL variable. The numerical value in this variable decides the number of parallel threads spawned by accrual source extract program. A default value of 8 is assigned to this variable, implying that 8 parallel threads will be spawned. But provision is available to extend it till 10 threads, in which case the parallel loads plan steps for thread 9 and thread 10 have to be enabled (by default, eight parallel steps are enabled).

• STANDALONE: This value is used when the Accrual Extraction process is not part of Accrual Load plan and is executed independently in a standalone manner before the Accrual Load Plan is executed. This might be done to prevent holding up the Accrual Load plan from spending too much time on the Accrual Extract interfaces. Standalone mode can be used when extraction volume is high in full load and takes a long time to complete. Here also PLSQL based wrapper approach is used. Permissions in E-Business Suite source schemas to create pl/sql package is needed by ODI for this mode to work.

Dependency

The extraction of incremental load depends on the value set for HR_ACCRUAL_EXTRACT_DATE. Hence for a high value of this variable which fetches a bigger dataset STANDALONE mode is best.

Additional Information

This SQL can be used to estimate the Accrual Extraction Time:

SELECT PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID ,PER_TIME_PERIODS.END_DATE,
PER_UTILITY_FUNCTIONS.GET_NET_ACCRUAL(PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID,PER_ALL_ASSIGNMENTS_F.PAYROLL_ID,
PER_ALL_ASSIGNMENTS_F.BUSINESS_GROUP_ID,-1,PER_TIME_PERIODS.END_DATE,PAY_ACCRUAL_PLANS.ACCRUAL_PLAN_ID,PER_TIME_PERIODS.START_DATE,NULL),
PER_ACCRUAL_CALC_FUNCTIONS.GET_CARRY_OVER(PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID,
PAY_ACCRUAL_PLANS.ACCRUAL_PLAN_ID,PER_TIME_PERIODS.END_DATE,PER_TIME_PERIODS.START_DATE ),
PER_ACCRUAL_CALC_FUNCTIONS.GET_ABSENCE(PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID,
PAY_ACCRUAL_PLANS.ACCRUAL_PLAN_ID,PER_TIME_PERIODS.END_DATE,
PER_TIME_PERIODS.START_DATE ),
PER_ACCRUAL_CALC_FUNCTIONS.GET_OTHER_NET_CONTRIBUTION(PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID,
PAY_ACCRUAL_PLANS.ACCRUAL_PLAN_ID,PER_TIME_PERIODS.END_DATE,PER_TIME_PERIODS.START_DATE)
FROM   APPS.PAY_ELEMENT_ENTRIES_F   PAY_ELEMENT_ENTRIES_F,
       APPS.PAY_ELEMENT_LINKS_F     PAY_ELEMENT_LINKS_F,
       APPS.PAY_ELEMENT_TYPES_F     PAY_ELEMENT_TYPES_F,
       APPS.PER_ALL_ASSIGNMENTS_F   PER_ALL_ASSIGNMENTS_F,
       APPS.PER_TIME_PERIODS        PER_TIME_PERIODS,
       APPS.PAY_ACCRUAL_PLANS       PAY_ACCRUAL_PLANS
WHERE  (1=1)
       AND (PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_TYPE IN ('E','C'))
       AND (PER_TIME_PERIODS.END_DATE < SYSDATE)
             AND (PER_TIME_PERIODS.END_DATE > #HR_ACCRUAL_EXTRACT_DATE)—Set the start date
       AND (PER_ALL_ASSIGNMENTS_F.PAYROLL_ID IS NOT NULL )
       AND (PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID=PAY_ELEMENT_ENTRIES_F.ASSIGNMENT_ID)
       AND (PER_ALL_ASSIGNMENTS_F.PAYROLL_ID=PER_TIME_PERIODS.PAYROLL_ID)
       AND (PAY_ELEMENT_ENTRIES_F.ELEMENT_LINK_ID=PAY_ELEMENT_LINKS_F.ELEMENT_LINK_ID)
       AND (PAY_ELEMENT_LINKS_F.ELEMENT_TYPE_ID=PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID)
       AND (PAY_ELEMENT_LINKS_F.ELEMENT_TYPE_ID=PAY_ACCRUAL_PLANS.ACCRUAL_PLAN_ELEMENT_TYPE_ID)
       AND (PER_TIME_PERIODS.END_DATE BETWEEN PAY_ELEMENT_ENTRIES_F.EFFECTIVE_START_DATE AND PAY_ELEMENT_ENTRIES_F.EFFECTIVE_END_DATE)
       AND (PER_TIME_PERIODS.END_DATE BETWEEN PAY_ELEMENT_LINKS_F.EFFECTIVE_START_DATE AND PAY_ELEMENT_LINKS_F.EFFECTIVE_END_DATE)
       AND (PER_TIME_PERIODS.END_DATE BETWEEN PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE AND PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE)
       AND (PER_TIME_PERIODS.END_DATE BETWEEN PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE AND PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE);