Posts by Doyensys

Project 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 More

Project 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 More

Period 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 More

PA 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 More

Project 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 More

AR 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 More

Script 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 More

Script 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

Script to get dependents for Employee

CREATE OR REPLACE FUNCTION APPS.HR_GET_DEPENDENT(p_element_entry_id IN NUMBER,     p_contact_number IN NUMBER, p_data_field IN VARCHAR2) RETURN VARCHAR2 IS l_contact_name VARCHAR2(300); CURSOR C1 IS SELECT DEP.Full_Name Contact_Full_Name,        DEP.National_Identifier,…

Read More

Script to get Aging Bucket Due for Customer

CREATE OR REPLACE FUNCTION APPS.Aging_Bucket_Due       (P_CUST_ACCOUNT_ID IN NUMBER,        P_AGING_BUCKET       IN NUMBER)   RETURN NUMBER     IS   l_bucket_balance NUMBER(10,2);   BEGIN…

Read More