PA Revaluated Funding Audit

Description
Categories: BI Publisher, Financials
Application: Projects
Source: AUD: Revaluated Funding Audit Report (XML)
Short Name: PAXFRADR_XML
DB package: PA_PAXFRADR_XMLP_PKG
SELECT
  Project_Number, 1 TempFlag,
  Project_Type,
  Task_Number,
   SUBSTR(Agreement_Number,1,20) Agreement_Number,
  Customer_Name ||' ('||Customer_Number||')' Customer_Desc,
  Revaluation_Date,
  Funding_Currency,
  to_char(Funding_Baselined_Funding,FND_CURRENCY.Get_Format_Mask(Funding_Currency,30)) FC_Baselined_Funding,
  to_char(Funding_Amount_Applied,FND_CURRENCY.Get_Format_Mask(Funding_Currency,30)) FC_Amount_Applied,
  to_char(Funding_Amount_Due,FND_CURRENCY.Get_Format_Mask(Funding_Currency,30)) FC_Amount_Due,
  to_char(Funding_Backlog,FND_CURRENCY.Get_Format_Mask(Funding_Currency,30)) FC_Backlog,
  Funding_Revaluation_Factor, 
  to_char(Funding_Backlog_Subj_Reval, FND_CURRENCY.Get_Format_Mask(Funding_Currency,30)) FC_Backlog_Subj_Reval,
  RevProc_Currency,
  substr(PA_Multi_Currency.Get_User_Conversion_Type(RevProc_Rate_Type),1,30) RevProc_Rate_Type,
  RevProc_Rate_Date,
  RevProc_Rate,
  to_char(RevProc_Backlog_Revalued,FND_CURRENCY.Get_Format_Mask(RevProc_Currency,30)) RPC_Backlog_Revalued, 
  to_char(RevProc_Amount_Applied,FND_CURRENCY.Get_Format_Mask(RevProc_Currency,30)) RPC_Amount_Applied,
  to_char(RevProc_Amount_Due_Revaled, FND_CURRENCY.Get_Format_Mask(RevProc_Currency,30)) RPC_Amount_Due_Revaled,
  to_char(RevProc_Realized_Gains, FND_CURRENCY.Get_Format_Mask(RevProc_Currency,30)) RPC_Realized_Gains,
  to_char(RevProc_Realized_Losses,FND_CURRENCY.Get_Format_Mask(RevProc_Currency,30)) RPC_Realized_Losses,
  to_char(RevProc_Baselined_Revalued,FND_CURRENCY.Get_Format_Mask(RevProc_Currency,30)) RPC_Baselined_Revalued,
  to_char(RevProc_Baselined_Bfr_Reval,FND_CURRENCY.Get_Format_Mask(RevProc_Currency,30)) RPC_Baselined_Bfr_Reval,
  to_char(RevProc_Revaluation_Adj,FND_CURRENCY.Get_Format_Mask(RevProc_Currency,30)) RPC_Revaluation_Adj,
to_char(RevProc_Revaluation_Adj,FND_Currency.Get_Format_Mask(RevProc_Currency,30)) ||' '||RevProc_Currency RPC_Reval_Adj_Wth_Curr,
  InvProc_Currency,
  substr(PA_Multi_Currency.Get_User_Conversion_Type(InvProc_Rate_Type),1,30) InvProc_Rate_Type,
  InvProc_Rate_Date, 
  InvProc_Rate,
  to_char(InvProc_Backlog_Ravaled,FND_CURRENCY.Get_Format_Mask(InvProc_Currency,30)) IPC_Backlog_Revaled,
  to_char(InvProc_Baselined_Revaled,FND_CURRENCY.Get_Format_Mask(InvProc_Currency,30)) IPC_Baselined_Revaled,
  to_char(InvProc_Baselined_Bfr_Reval,FND_CURRENCY.Get_Format_Mask(InvProc_Currency,30)) IPC_Baselined_Bfr_Reval,
  to_char(InvProc_Revaluation_Adj,FND_CURRENCY.Get_Format_Mask(InvProc_Currency,30)) IPC_Revaluation_Adj,
to_char(InvProc_Revaluation_Adj,FND_CURRENCY.Get_Format_Mask(InvProc_Currency,30)) ||' '||InvProc_Currency IPC_Reval_Adj_wth_Curr,
  Event_Number,
  Event_Type,
  Event_Classification,
  Event_Currency,
  to_char(nvl(Event_Amount,0), FND_CURRENCY.Get_Format_Mask(Event_Currency,30)) EC_Event_Amount
FROM
pa_fund_reval_audits_v
WHERE
   Project_Number BETWEEN NVL(:LP_From_Project_Number, '0') AND
                          NVL(:LP_To_Project_Number, 'z')
AND UPPER(Project_Type) = UPPER(NVL(:P_Proj_Type, Project_Type))
 AND trunc(Revaluation_Date) BETWEEN 
       NVL(trunc(:P_Reval_From_Date), sysdate-10000) AND
       NVL(trunc(:P_Reval_To_Date), sysdate+10000)
UNION
SELECT p.segment1, 0 TempFlag, NULL, NULL, NULL, NULL,
       to_Date(NULL), NULL, NULL, NULL, NULL, NULL, 
       to_Number(NULL),  
NULL, NULL, NULL,
       To_Date(NULL), To_Number(NULL),  
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
       NULL, NULL, NULL, 
       To_Date(NULL), To_Number(NULL),  
NULL, NULL, NULL, NULL, NULL,
       To_Number(NULL),  
NULL, NULL, NULL, NULL
from   pa_projects p
where  p.segment1 BETWEEN NVL(:LP_From_Project_Number, '0') AND
                          NVL(:LP_To_Project_Number, 'z')
AND    P.Project_Type = NVL(:P_Proj_Type, P.Project_Type)
AND    p.template_flag = 'N'
AND    Revaluate_Funding_Flag = 'Y'
AND    NOT EXISTS (select f.project_id from pa_project_fundings f
                       where  p.project_Id = f.project_id
                      and   Revaluation_Through_Date between
                         NVL(trunc(:P_Reval_From_Date), sysdate-10000) AND 
                         NVL(trunc(:P_Reval_To_Date), sysdate+10000)
                  )
ORDER BY TempFlag, Project_Number, Agreement_Number, Task_Number, Revaluation_Date
Parameter Name SQL text Validation
Ledger Currency
 
LOV Oracle
Mode
 
LOV Oracle
Revaluation To Date
 
Date
Revaluation From Date
 
Date
Project Type
 
LOV Oracle
To Project Number
 
LOV Oracle
From Project Number
 
LOV Oracle