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
Thursday, 25 February 2021
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.
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.
Friday, 19 February 2021
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 Worker HDL Post Processes
All HDL Articles -
Oracle HDL Blogs
HDL Keys - HDL Keys Blog
Friday, 13 March 2020
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.
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.
Monday, 23 September 2019
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$}
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.
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.
UCM/Content Server/FTP/Email Bursting Sample Query
For most outbound interfaces bursting to content server and then picking the file from UCM is the best approach for large extracts.
Default URL - https://POD.oraclecloud.com/cs
Also a cool feature I noticed is that bursting works to different targets (like the same file can be sent to email & FTP at the same time)
Below is the samplequery for busting to UCM or webcenter content server.
Sample Bursting Query for UCM Bursting
This is a sample query for bursting reports to a specific folder in the content server.
select distinct
product_status as "KEY", -- Split Key from data model SQL
'BurstTemp' TEMPLATE, -- Report template name
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT, -- Output type
'WCC' DEL_CHANNEL, -- To send to content server
'FA_UCM_PROVISIONED' PARAMETER1, -- Content Server name
'FAFusionImportExport' PARAMETER2, -- Security group
'intergration_user' PARAMETER3, -- Author of the file to be shown in content server
'' PARAMETER4, -- Account (Optional)
'Test_Burst' PARAMETER5, -- Title
Product_Status|| TestFile.pdf'PARAMETER6, -- Output File name
Product_Status PARAMETER8, -- Content ID (Optional)
'false' PARAMETER9, -- Custom metadata (Optional)
'/Contribution Folders/Inbound' PARAMETER10 -- Folder path to deliver files
from products
'FA_UCM_PROVISIONED' is the default account for BI reports 'FAFusionImportExport' can be used as it setup by default.
Sample Bursting query for Email with output file name from BI Publisher-
SELECT 'Split_KEY' KEY, -- Split Key from data model SQL
'AP Rejections' TEMPLATE, --BI Report template
'en-US' LOCALE,
'xlsx' OUTPUT_FORMAT,
'EMAIL' DEL_CHANNEL,
'from@email.com' parameter1,
'cc@email.com' parameter2, --CC
'to@email.com' parameter3,
'Bursting Email TEST' parameter4,
'Please find the reported AP Invoice Rejections during the interface load.
Please review the errors and take appropriate actions.
Regards
Interface Team ' parameter5,
'true' parameter6,
'replyto@nowhere.com' parameter7,
'Rejections Output'|| to_char(sysdate,'dd_mm_yyyy') output_name FROM dual
Sample Bursting query for FTP with output file name from BI Publisher-
SELECT SEQ KEY
, 'TEST_REPORT' TEMPLATE -- Report template name
, 'en-US' LOCALE
, 'PDF' OUTPUT_FORMAT
, 'FTP' DEL_CHANNEL
, 'FTP Name Defined in BI Pub' PARAMETER1
, 'accountname' PARAMETER2
, 'password' PARAMETER3
, 'folder' PARAMETER4
, 'TEST_FTP_'||TO_CHAR(SYSDATE, 'DDMMYYHH24MISS')||'.pdf' PARAMETER5
, 'TRUE' PARAMETER6
, null parameter7
from dual
Default URL - https://POD.oraclecloud.com/cs
Also a cool feature I noticed is that bursting works to different targets (like the same file can be sent to email & FTP at the same time)
Below is the samplequery for busting to UCM or webcenter content server.
Sample Bursting Query for UCM Bursting
This is a sample query for bursting reports to a specific folder in the content server.
select distinct
product_status as "KEY", -- Split Key from data model SQL
'BurstTemp' TEMPLATE, -- Report template name
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT, -- Output type
'WCC' DEL_CHANNEL, -- To send to content server
'FA_UCM_PROVISIONED' PARAMETER1, -- Content Server name
'FAFusionImportExport' PARAMETER2, -- Security group
'intergration_user' PARAMETER3, -- Author of the file to be shown in content server
'' PARAMETER4, -- Account (Optional)
'Test_Burst' PARAMETER5, -- Title
Product_Status|| TestFile.pdf'PARAMETER6, -- Output File name
Product_Status PARAMETER8, -- Content ID (Optional)
'false' PARAMETER9, -- Custom metadata (Optional)
'/Contribution Folders/Inbound' PARAMETER10 -- Folder path to deliver files
from products
'FA_UCM_PROVISIONED' is the default account for BI reports 'FAFusionImportExport' can be used as it setup by default.
Sample Bursting query for Email with output file name from BI Publisher-
SELECT 'Split_KEY' KEY, -- Split Key from data model SQL
'AP Rejections' TEMPLATE, --BI Report template
'en-US' LOCALE,
'xlsx' OUTPUT_FORMAT,
'EMAIL' DEL_CHANNEL,
'from@email.com' parameter1,
'cc@email.com' parameter2, --CC
'to@email.com' parameter3,
'Bursting Email TEST' parameter4,
'Please find the reported AP Invoice Rejections during the interface load.
Please review the errors and take appropriate actions.
Regards
Interface Team ' parameter5,
'true' parameter6,
'replyto@nowhere.com' parameter7,
'Rejections Output'|| to_char(sysdate,'dd_mm_yyyy') output_name FROM dual
Sample Bursting query for FTP with output file name from BI Publisher-
SELECT SEQ KEY
, 'TEST_REPORT' TEMPLATE -- Report template name
, 'en-US' LOCALE
, 'PDF' OUTPUT_FORMAT
, 'FTP' DEL_CHANNEL
, 'FTP Name Defined in BI Pub' PARAMETER1
, 'accountname' PARAMETER2
, 'password' PARAMETER3
, 'folder' PARAMETER4
, 'TEST_FTP_'||TO_CHAR(SYSDATE, 'DDMMYYHH24MISS')||'.pdf' PARAMETER5
, 'TRUE' PARAMETER6
, null parameter7
from dual
Subscribe to:
Posts (Atom)
Integrations Lead - Lessons learnt
Integrations have been my passion for a while but like anything tech there is no credit given when things go right but always heaps of pres...
-
For most outbound interfaces bursting to content server and then picking the file from UCM is the best approach for large extracts. Default...
-
While developing Cloud integrations I could not find a single place for all details like InterfaceID or jobdefinitionname or job name etc. ...
-
Order to Cash flow starts with putting in an order in Order Management. 1. Enter an Sales order. A customer puts in a order for an Item. S...