HCM Extract / Fast Formulas scribblings

HCM Extracts -

Add Parent Data Group
- Select the main UE

To add a Child data group -> Right click on the Parent group area and 'Add Child Data Group'
- Define the Child Data group (UE, Filters etc)
- Connect the Data groups

To add a Record -> Righ Click and 'Add Record'
- Define the details, Save and Add Attributes

To add a fast formula column add the columnd as Rule

DEFAULT FOR PER_ASG_REL_DATE_START IS '4712/12/31 00:00:00' (date)

l_rel_date_start = PER_ASG_REL_DATE_START

l_effective_date = GET_PARAMETER_VALUE_DATE('EFFECTIVE_DATE')

--RULE_VALUE is default return for Extract Rule FF Type
return RULE_VALUE

Cloud HCM Use Valueset in Fast Formula

You can use valueset(Ex-ORG_LEVEL1) to use the query table functionality in Fast Formulas. Below is sample how you pass parameters(P_ORG_ID) and invoke valuesets to FFs. The Fast Formula will be of type Extract Rule.

Sample FF Code :
DEFAULT FOR PER_ASG_DEPARTMENT_ID IS 0

l_organization_id=to_char(PER_ASG_DEPARTMENT_ID)
l_level1_org=GET_VALUE_SET('ORG_LEVEL1','|=P_ORG_ID='''||l_organization_id||'''')

RULE_VALUE=l_level1_org

return RULE_VALUE


In you Valueset where clause the parameter is used as below :
and level1.organization_id=:{PARAMETER.P_ORG_ID}
Make sure the ID and Value columns are populated in Valuset otherwise empty values are returned

HCM GET ORG AT PARTICULAR DEPTH

Query to get a org at a particular depth -

SELECT haou.name, haou.organization_id
, ANCESTOR_PK1_VALUE, DISTANCE
,haou1.name LEVEL1_ORG
FROM per_org_tree_node_rf prf, hr_all_organization_units haou
, hr_all_organization_units haou1
WHERE 1=1
AND pk1_value = haou.organization_id
AND ANCESTOR_PK1_VALUE = haou1.organization_id
AND DISTANCE = (CASE WHEN haou.name LIKE '%IDEN1%' THEN 1
WHEN haou.name LIKE '%IDEN2%' THEN 2
WHEN haou.name LIKE '%IDEN3%' THEN 3
ELSE 1 END
)

Instead of the hard coded distance calculation you could use the current node depth and get the node at a given level above.

Oracle Worker HDL Post Migration Processes

Oracle has documented the processes hence don't want to duplicate the content -
Oracle Worker HDL Post Processes

All HDL Articles -
Oracle HDL Blogs

HDL Keys - HDL Keys Blog

Oracle Receivables

Oracle AR builds on top of customer and customer transactions (receivables/incoming money)

Receipts are inbound payments, which can be of different types.
1. Unidentified receipts - When customer is not identified based on the receipt record in inbound file.
2. Standard receipts - When customer is identified based on the receipt record in inbound file.
3. Applied receipts - When customer is identified based on the receipt record in inbound file and amount is applied onto installments. When the receipt amount is more than customer balance we can Apply On Account as well.

Default values in BI reports

Oracle provides some functions for default values for date fields. Below are some supported functions. The below details are from Oracle support note.
Enter one of the following functions using the syntax shown to calculate the appropriate date at the scheduled runtime for the report:

{$SYSDATE()$} - current date (the system date of the server on which BI Publisher is running)

{$FIRST_DAY_OF_MONTH()$} - first day of the current month

{$LAST_DAY_OF_MONTH()$} - last day of the current month

{$FIRST_DAY_OF_YEAR)$} - first day of the current year

{$LAST_DAY_OF_YEAR)$} - last day of the current year

The date function calls in the parameter values are not evaluated until the report is executed by the Scheduler.

You can also enter expressions using the plus sign "+" and minus sign "-" to add or subtract days as follows:

{$SYSDATE()+1$}

{$SYSDATE()-7$}

For our example, to capture data from the previous week, each time the schedule runs, enter the following in the report's date parameter fields:

Date From: {$SYSDATE()-7$}

Date To: {$SYSDATE()-1$}

Last run date parameter BI Report

Having the last run date is quite useful in reports particularly if you are interested in incremental reports. There is no straightforward way for doing this Oracle Cloud BI.


Below are the steps I have done this -

1. Define list of values SQL based.
Name last_run_time_lov
Query (Only one value is returned)
SELECT MAX(processend-30) last_end_time
FROM fusion.ess_request_history erh, fusion.ess_request_property erp
WHERE 1 = 1 AND erh.product = 'BI Publisher'
AND erh.requestid = erp.requestid
AND erp.name = 'report_url'
AND erp.VALUE LIKE '/Custom/Test/AP Rejections.xdo'
AND erh.state IN (4,12)

2. Create a menu based parameter and assign the LOV
3. Uncheck Select All and now the only value is defaulted when you run the report.