Fusion

Query to Find AP Reimbursable Expenses in Oracle Fusion

WITH GET_PERIOD_AVG_RATE        AS (  SELECT gper.avg_rate,                     gsob.CURRENCY_CODE AS functional_currency,                     gper.period_name,                     gper.to_currency_code                FROM GL_LOOKUPS lk,                     GL_TRANSLATION_RATES gper,                     gl_sets_of_books gsob               WHERE     lk.lookup_type = ‘TRANSLATION_BAL_TYPE’                     AND gper.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID                     AND lk.lookup_code = gper.actual_flag           …

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 Projects Not Accruing Revenue in Oracle Fusion

–**** Project Not Accruing Revenue Due to Expenditure Exception ****– SELECT PE.accounting_period AS GL_PERIOD      ,OKH.contract_number      ,OKL.line_number      ,PPA.segment1 AS project_number      ,PPA.name AS project_name      ,PT.task_number      ,PT.task_name      ,PEI.expenditure_item_id trx_number      ,PEC.expenditure_category_name      ,NVL(PPN.full_name, PS.vendor_name) AS…

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

HCKT Hard Limit Reached Events in Fusion

Purpose of the report is to display project hours and hard-limit amount with interactive mode. with XXANSR_PA_EVENTS_cte as (SELECT evt.ROWID as “ROW_ID”,           evt.event_id as “EVENT_ID”,…

Read More

REVENUE TRANSFER REPORT (FUSION)

Purpose of this report is to derive all projects expenditure and event details with cost information. SELECT   revenue_type, TO_CHAR (customer_id) customer_id, customer_name,          TO_CHAR (project_id) project_id, project_number,…

Read More

Building Dynamic Regions in Oracle APEX using APEX API

Modifying region queries/source dynamically can be done easily, whereas building regions dynamically can be done only using APEX API. Using the below code, a static region and a chart region…

Read More

Integrating With Fusion Application Using Services (APEX)

Fusion Applications provides web services that allow external systems to integrate with Fusion Applications. There are two types of services: ADF services and composite services. ADF services are created for…

Read More