Oracle Fusion

Getting HCM Extract Input Values in Fast Formula

Introduction This Post Provides the Detail to get HCM Extract Input Values in Fast Formula   Sample Fast Formula PERSON_NUMBER_PARAM = GET_PARAMETER_VALUE(‘PERSON_NUMBER’) CHANGES_ONLY_PARAM = GET_PARAMETER_VALUE(‘CHANGES_ONLY’) EFFECTIVE_DATE_PARAM = GET_PARAMETER_VALUE(‘EFFECTIVE_DATE’) L_VALUE =…

Read More

Query to get Last Run Parameter Value in BI Report

Introduction This Post Provides the SQL Query to get Last Run Parameter Value in BI Report   Query SELECT TO_DATE (SUBSTR (rpj.VALUE, INSTR (rpj.VALUE, ‘”}’) – 10, 10), ‘MM-DD-YYYY’) eff_date…

Read More

Employee Assignment Details Query

Introduction This Post Provides the SQL Query that returns the Assignment Details of the Employees   Query SELECT peo.person_number employee_number, loc.internal_location_code location_code, loc.location_name LOCATION, per_name.last_name, per_name.first_name, per_name.middle_names middle_name, pa.address_line_1, pa.address_line_2,…

Read More

Query to Find NonBillable Expense in Oracle Fusion

SELECT pcdl.prvdr_gl_period_name gl_period,       trx_org.NAME expenditure_organization,       prb.project_id,       NVL (prb.segment1, ‘N/A’) project_number,       NVL (prl.NAME, ‘N/A’) project_name,       NULL project_type_class_code,       ppn.full_name employee_vendor,       papf.person_number employee_number,       ptv.task_name,       pec.expenditure_category_name expenditure_category,       pet.expenditure_type_name expenditure_type,       pcdl.prvdr_gl_date week_ending,       pei.expenditure_item_date expenditure_date,       pcdl.denom_currency_code…

Read More

Query to Find Project Estimation To Complete in Oracle Fusion

SELECT A.PROJECT_STATUS,       A.OPERATING_UNIT_NAME,       A.AGREEMENT_TYPE,       A.PROJECT_ORGANIZATION_NAME,       A.PROJECT_NUMBER,       A.PROJECT_NAME,       A.START_DATE,       A.COMPLETION_DATE,       A.TASK_NUMBER,       A.TASK_NAME,       A.PROJECT_CURRENCY,       ROUND(A.BUDGETED_REV_PROJ_CURR) BUDGETED_REV_PROJ_CURR,       ROUND(A.ACTUAL_REV_ITD_PRJ_CURR) ACTUAL_REV_ITD_PRJ_CURR,       ROUND(A.BILLABLE_HOURS_ACTUAL_ITD) BILLABLE_HOURS_ACTUAL_ITD,       ROUND(A.TOTAL_HOURS_ACTUAL_ITD) TOTAL_HOURS_ACTUAL_ITD,       A.FUNC_CURRENCY,       ROUND(A.ACTUAL_REV_ITD_FUNC_CURR) ACTUAL_REV_ITD_FUNC_CURR,        (ROUND (     ( (A.ACTUAL_REV_ITD_PRJ_CURR /…

Read More

Query to find AR Open Balance Of a Project in Oracle Fusion

WITH FUNCTION get_outstanding_amount (      p_payment_schedule_id       IN   NUMBER,      p_as_of_date                IN   DATE,      p_invoice_type              IN   VARCHAR2,      p_amt_due_remaining         IN   NUMBER,      p_amount_applied            IN   NUMBER,      p_amount_adjusted           IN   NUMBER,      p_amount_credited           IN   NUMBER,      p_amount_in_dispute         IN   NUMBER,      p_amount_adjusted_pending   IN  …

Read More

PROJECT UNBILLED BALANCES- FUSION

This report will display all project unbilled details. SELECT   ppa.project_id, ppa.segment1 project_number,           NVL ((SELECT pt.project_type                FROM pjf_project_types_tl pt, pjf_projects_all_vl…

Read More