In this article we will look into how modifying the HCM Extract not to generate output file when data is not fetched by HCM Extract. Normally a blank file will be generated by the HCM Extract when there is no data fetched by the Extract. If you want to know the basics of HCM Extract please Click here Even if there…
In this article we will look into setting up Payroll Process Configuration Groups and SQL Query to extract data. Want to learn Fusion Technical tools? Check out the article We use the Payroll Process Configuration groups while submitting HCM Extracts. These Configuration groups specify the multi threading count, BI output maximum size, GMFZT logging etc. We can navigate to this page…
In this article we will look into cancelling an existing Work Relationship of an employee using HDL process. We can prepare the HDL file with the required user keys to delete the work relationship. Sample HDL to delete Work Relationship(Worker.dat): SET PURGE_FUTURE_CHANGES N METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag DELETE|WorkRelationship|HRC_SQLLOADER|1122334455|123456789_1|Legal Entity Two||2010/01/01|E|Y When we have a requirement to cancel multiple work relationships due to bad…
We will try to extract Valueset and Lookup values using SQL Query. Independent Valueset details: SELECT ffvs.flex_value_set_name ,ffvs.description set_description ,ffv.flex_value ,ffvt.flex_value_meaning ,ffvt.description value_description FROM fnd_flex_value_sets ffvs ,fnd_flex_values ffv ,fnd_flex_values_tl ffvt WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id AND ffv.flex_value_id = ffvt.flex_value_id AND ffvt.LANGUAGE = USERENV('LANG') AND TRUNC(SYSDATE) BETWEEN NVL(ffv.start_date_active, trunc(SYSDATE)) AND NVL(ffv.end_date_active, trunc(SYSDATE + 1)) AND ffvs.flex_value_set_name = 'ABCD_BRANCH_VS' Table based Valueset details:…
We normally run many Scheduled Processes in fusion. some of them to keep the users in sync, updating the search keywords, sending pending ldap requests, refresh manger hierarchy, etc to name a few. If you are really interested in finding out the underlying tables which gets impacted by these processes, you can run the below query immediately after the process…
If the client has implemented Payroll, we have a common requirement to pull the balances in BI Report multiple times as part of third party integrations. Getting the Payroll Balance requires calling the balance package and providing the right inputs. Many of us get it wrong in writing the balances query and find it difficult to achieve it. Here is…
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…
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…
