Posts by Doyensys

Fusion Query to get Revenue and billable hours of a Employee

SELECT pcdl.org_id employee_cost_org_id, pcdl.prvdr_gl_period_name gl_period,        pcdl.prvdr_pa_period_name,        NVL (pei.override_to_organization_id,             pei.incurred_by_organization_id            ) expenditure_org_id,    …

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

Fusion Query to find Revenue Exceptions details for Expenditures

SELECT   x.ei_latest_gl_period_name, x.r_company,          x.ei_revenue_distributed_flag, x.ei_project_name,          x.ei_project_number, x.ei_expenditure_type,          x.ei_expenditure_category, x.ei_employee_name,          x.ei_expenditure_item_date, x.ei_receipt_currency_code,      …

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

Fusion Query to get Employee Allocated and Utilization hours

select Entity,Business_unit,reporting_organization,EMPLOYEE_NUMBER,EMPLOYEE_NAME,EFFECTIVE_START_DATE,TOTAL_HOURS,BILLABLE_HOURS,CHARGEABLE_HOURS,EXPENDITURE_ENDING_DATE,YEAR_START_DATE, round((BILLABLE_HOURS/TOTAL_HOURS)*100,2) BILLABLE_PCT,round((CHARGEABLE_HOURS/TOTAL_HOURS)*100,2) CHARGEABLE_PCT ,normal_hours,effective_end_date from ( SELECT Entity,Business_unit,             EMPLOYEE_NUMBER,EMPLOYEE_NAME,EFFECTIVE_START_DATE,TOTAL_HOURS,BILLABLE_HOURS,CHARGEABLE_HOURS,EXPENDITURE_ENDING_DATE, YEAR_START_DATE, round((BILLABLE_HOURS/TOTAL_HOURS)*100,2) BILLABLE_PCT,round((CHARGEABLE_HOURS/TOTAL_HOURS)*100,2) CHARGEABLE_PCT ,normal_hours,effective_end_date  from (  select  (SELECT fv.description      …

Read More

Fusion–> Query to find registered address of a Legal Entity

SELECT   xep.NAME,   — Legal Entity Name           xjv.registration_code_le registration_code,           xr.registration_number,           hl.address1          …

Read More

Fusion Query to find Default company values assigned in Legal Entity

1. Link Between Legal Entity & Ledger along with Default Company Assigned in Legal Entity  SELECT flex_segment_value                 ,gll.*    FROM gl_legal_entities_bsvs gleb…

Read More

Restricting access to su for group(dba) of users.

Providing su access to group of users in Oracle Cloud. Add below line in /etc/sudoers.d/90-cloud-init-users %dba ALL=(ALL) NOPASSWD: /bin/su – oracloud And then restart ssh daemon services #/sbin/service sshd restart…

Read More