• Post category:BI Publisher
  • Post comments:2 Comments
  • Post last modified:September 23, 2021
  • Reading time:3 mins read
You are currently viewing SQL Query to fetch Fast Formulas used in HCM Extract
SQL Query to fetch Fast Formulas used in HCM Extract

In this article, we will figure out a way to fetch the fast formulas used for attributes in an HCM Extract.

We use the Extract Rule Fast Formulas to write calculation logic, complex logic which cannot be handled at template level, logic based on dependent attributes, etc.

While migrating the HCM Extract, we first need to migrate the fast formulas followed by HCM Extract, else the attributes which have the fast formulas will vanish after the HCM Extract import as those fast formulas will not be present in the target instance.

Getting the list of fast formulas used is a tedious process if we go through the HCM Extract manually by going through all data groups, records, etc. If we have multiple data groups and records, it would be a nightmare. The below SQL Query will come to our rescue to help us get the complete list of all fast formulas used in HCM Extract at attribute level.

SQL Query to get the list of Extract Rule Fast Formulas

SELECT pedv.definition_name HCM_EXTRACT_NAME,
prb.base_block_name DATA_GROUP,
prrf.flex_structure_code RECORD,
FROM per_ext_data_elements_vl pedev,
pay_report_records_f prrf,
pay_report_blocks prb,
per_ext_definitions_vl pedv,
ff_formulas_vl ffv
WHERE pedev.report_record_id = prrf.report_record_id
AND prrf.report_block_id = prb.report_block_id
AND prb.ext_definition_id = pedv.ext_definition_id
--and pedv.definition_name LIKE '%Extract_Name%'
AND pedv.last_updated_by NOT LIKE 'SEED%'
AND ffv.formula_id = pedev.rule_id

Sample Output will look like:

image 9 1024x569 - SQL Query to fetch Fast Formulas used in HCM Extract
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.

  • Hello, This is great website for learning and practical testing too. Can you update the query to include the database item used in HCM extract as attributes just like fast formula query in this section (Query to get the list of Extract Rule Fast Formulas) . Thanks in advance.

  • In visible box by plugintheme