Doyen Domains

Blog Archives

Follow us on

Total Page Views

1,197 views

Query to Find Project Estimation To Complete in Oracle Fusion

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 / (CASE                                                   WHEN A.BUDGETED_REV_PROJ_CURR = 0 THEN 0.1                                              ...[ read more ]

  • March 4, 2019 | 18 views

Fusion Query to find Revenue Exceptions details for Expenditures

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,          x.ei_project_currency_code, x.ei_expenditure_org_name,          x.ei_prvdr_org_name, x.project_owning_org,          x.billed_flag, x.task_number, x.rejection_code,          transaction_number, billable_flag, invoice_status, revenue_status,          employee_job, unit_of_measure,...[ read more ]

  • March 4, 2019 | 24 views

Query to Find Projects Not Accruing Revenue in Oracle Fusion

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 full_name           ,NVL(PPF.person_number, PS.segment1) AS person_number           ,DECODE(PEI.incurred_by_person_id, NULL, 'Supplier'                                       ,(SELECT FL.meaning FROM fnd_lookups FL                                          WHERE FL.lookup_type = 'PJC_PERSON_TYPE'                                            AND FL.lookup_code = PEI.person_type)              ) AS PERSON_TYPE      ,TRUNC(PEI.expenditure_item_date) expenditure_item_date      ,DECODE(PEC.expenditure_category_name, 'Expenses',...[ read more ]

  • March 4, 2019 | 22 views

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

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   NUMBER,      p_convert_flag              IN   VARCHAR2,      p_due_date                  IN   DATE,      p_return_position           IN   NUMBER   )      RETURN NUMBER   IS      l_inv_type              CHAR (4);      l_amount_applied_late   NUMBER (24, 2)            := 0;      l_adjustment_amount     NUMBER (24, 2);      l_amt_due_remaining     NUMBER (24,...[ read more ]

  • March 4, 2019 | 19 views

Fusion Query to get Employee Allocated and Utilization hours

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             FROM fnd_flex_values_vl fv,                  fnd_flex_value_sets fvs            WHERE 1 = 1          ...[ read more ]

  • March 4, 2019 | 24 views

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

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           ,hl.address2           ,hl.address3           ,hl.address4           ,hl.city           ,hl.postal_code          ...[ read more ]

  • March 4, 2019 | 18 views

Fusion Query to find Default company values assigned in Legal Entity

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                ,gl_ledger_le_v gll WHERE gleb.legal_entity_id = gll.legal_entity_id      AND ledger_category_code = 'PRIMARY'      AND gleb.flex_segment_value = :p_company    or 2.  Default Company...[ read more ]

  • March 4, 2019 | 18 views

Project wise PO details Reports

Project wise PO details Reports

Project wise PO details Reports SELECT ipd.project_number PROJECT_NUMBER,        ipda.asset_tag_id TAG_NUMBER,        translate(ipd.description,'ä','a') Project_Desc,        Translate(ipda.asset_desc,'ä','a') Asset_Desc,       -- prh.segment1 req_number,        ph.segment1 po_number ,        ph.CREATION_DATE CREATION_DATE,        (select Translate(full_name,'éÖ','eO') from per_all_people_f ppf1 where pd.deliver_to_person_id = ppf1.person_id(+) and sysdate between effective_start_Date and effective_end_Date)Requestor,    ...[ read more ]

  • March 4, 2019 | 24 views

How to add expense items type in expense template

How to add expense items type in expense template

Add Expense type in Expense template Navigation AP setup user à Setup à Invoice à Expense Report Templates Name Expense Category Tax Code GL Account Projects Expenditure Type Policy Schedule Data Capture Rule Data Capture Rule End date Prompt Justification Required Requre receipt for Cash Require Receipt Above Number of duplicates Allowed Viewable from all templates Calculate Amount Apply Receipt...[ read more ]

  • March 4, 2019 | 15 views

AR Invoice PDF email (Alternative of BPA Process)

AR Invoice PDF email (Alternative of BPA Process)

<?xml version="1.0"?> <dataTemplate name="XX__AR_INVPRINT" description="TW Invoice Printout for United States" defaultPackage="XX_AR_INVPRINT_PKG" version="1.0">     <parameters>         <parameter name="P_Report_Level"            dataType = "character"></parameter>         <parameter name="P_Report_Context"          dataType = "character"></parameter>         <parameter name="P_TRANSACTION_SOURCE"      dataType = "character"></parameter>         <parameter name="P_TRANSACTION_TYPE"   ...[ read more ]

  • February 25, 2019 | 13 views