GL Revaluation Accounts Query – FUSION
SELECT DISTINCT o101368.book_currency AS c_1, (o101368.converted_journal_total_dr * o101368.currency_conversion_rate ) AS c_2, o101368.converted_journal_total_cr…
Read MoreEAM Asset Failure code,Cause,Resolution creation
API used for EAM asset maintenance during work order failure. This API used to create failure code and its cause and its resolution for an asset in an asset group.…
Read MoreProject Unbilled Balances Query – FUSION
WITHFUNCTION get_rev_todate_amt ( p_contract_id IN NUMBER, p_contract_line_id IN NUMBER, P_AS_OF_DATE IN DATE )RETURN NUMBERIS ln_amount NUMBER;BEGIN SELECT SUM(PRD.cont_curr_revenue_amt) cont_curr_revenue_amt INTO ln_amount FROM pjb_rev_distributions PRD WHERE PRD.gl_date <= P_AS_OF_DATE …
Read MoreProject Details – Auditor Query – FUSION
SELECT DISTINCT PPA.project_status_code ,HOU.name organization_name ,PPA.description ,OCT.name contract_type ,PPT.project_type ,PPA.segment1 project_number …
Read MorePeriod and Daily Exchange Rates Query – FUSION
SELECT gper.set_of_books_id ,gl_set_of_books.name || gper.period_name || gper.to_currency_code set_of_books_name ,gper.period_name ,gper.to_currency_code to_currency ,lk.meaning…
Read MorePA Invoice Detail Query – FUSION
SELECT PEI.expenditure_item_id ,PPA.segment1 project_number ,PPA.name project_name ,PPA.segment1||’-‘||pih.invoice_num invoice_number ,TO_CHAR(PIH.INVOICE_DATE, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) INVOICE_DATE…
Read MoreProject Accounting Labor Revenue query – FUSION
SELECT prd.org_id employee_cost_org_id, prd.gl_period_name gl_period, NVL (pei.override_to_organization_id, pei.incurred_by_organization_id ) expenditure_org_id, trx_org.NAME expenditure_org, fnbu.bu_name,…
Read MoreAR Aging 4 Bucket with project details – FUSION
WITH FUNCTION get_outstanding_amount ( p_payment_schedule_id IN NUMBER, p_as_of_date IN DATE, p_invoice_type …
Read MoreScript to get Normalized Exp Amount from Property Manager
CREATE OR REPLACE FUNCTION APPS.PN_NORM_EXP(p_payment_item_id number, p_ccid number) RETURN NUMBER IS l_accrued_amount number; l_cash_amount number; l_norm_exp number; l_max_item_id number; l_ps_id …
Read MoreScript to get Cash from Property Manager
CREATE OR REPLACE FUNCTION APPS.PN_CASH(p_payment_item_id number, p_ccid number) RETURN NUMBER IS l_cash_amount number; l_max_item_id number; l_ps_id number; BEGIN — select payment_schedule_id…
Read More