Many a times we get errors when we run HCM Extract and one of the common errors is "A system error occurred during processing. Contact your help desk" and it does not show up any other clue on why the extract is failing. If we dig into details and add the GMFZT logging at system level and run the extract…
Do you know the most frequently used Date, Number and String functions in SQL query? Read on to know more details
Below are the list of SQLs that are most frequently used to get the list of values for business objects. Legislative Data Group: Select name from per_legislative_data_groups_vl Secured Persons List: SELECT DISTINCT PERSON_NUMBER FROM PER_PERSON_SECURED_LIST_V Secured Departments List: SELECT DISTINCT SUBSTR(DEPT.NAME,1,INSTR(DEPT.NAME,’-‘,1)-1) DEPCODE FROM PER_DEPARTMENT_SECURED_LIST_V SEC_DEPT, PER_DEPARTMENTS DEPT WHERE DEPT.ORGANIZATION_ID=SEC_DEPT.ORGANIZATION_ID Payroll Name: select distinct payroll_name from pay_all_payrolls_f where TRUNC(SYSDATE) BETWEEN effective_start_date…
Below are the SQL's to extract Fast Formula related data from underlying tables:Fast Formula Functions and their underlying Packages: SELECT f.BASE_FUNCTION_NAME ,f.description ,f.DEFINITION || '(' || listagg(fp.name || ' ' || decode(fp.class, 'I', 'IN', 'O', 'OUT') || ' ' || decode(fp.data_type, 'N', 'NUMBER', 'T', 'VARCHAR2', 'D', 'DATE'), ',') WITHIN GROUP (ORDER BY fp.sequence_number) || ') RETURN ' || decode(f.data_type,'N','NUMBER','T','VARCHAR2','D','DATE') AS…
While creating a BI Publisher Report, we develop templates using RTF template as it provides multiple output formats like Interactive Mode, PDF, XLSX, XML, CSV etc which are not possible with any other templates like XLS, E-TEXT, etc. One problem most of the people face is when they try to download the excel output using RTF template, the excel output contains merged…
One of the missing piece in BI Publisher earlier was the password protection for Excel 2007 Output and it has been introduced in 18c update. Earlier password protection was possible for PDF output only and there have been a number of bugs and enhancements pending around the feasibility for password protection for Excel Output. And finally Oracle has worked on…
In this article we will look into getting the Country, State, County, City and Zipcode information from backend tables. These queries will be handy when there is a requirement to get the Country Name, State Names etc apart from the Country Code and State Code which are present in most of the address tables. Countries:SELECT TERRITORY_CODE ,TERRITORY_SHORT_NAME ,CURRENCY_CODE ,DESCRIPTIONFROM FND_TERRITORIES_VLWHERE…
For most of the BI Reports, we have the input parameter as "AsOfDate" alias sysdate or current date... Sometimes we also have a date range "From Date" and "To Date" to fetch transactions between those dates. And these dates need to have a default value when we open the report/schedule the report. If the scheduled report includes date parameters, when…
Do you know how to create the supervisor update HDL file using SQL Query? Within a few minutes you will be able to update supervisor for a bunch of employees. Read on to know more details.
When we configure the flex fields we check the BI Enabled flag for the flex field to be used in OTBI analysis. Setting Extensible Flexfields as BI-Enabled: Procedure Extensible flexfields are BI enabled if at least one segment in a context is BI enabled. Setting Extensible Flexfields as BI-Enabled To set extensible flexfields as BI-enabled: Navigate to Setup and Maintenance.Navigate to Manage…
