select
distinct
substr(p.organization_name,1,60) project_organization,
p.manager_name project_manager,
p.project_id project_id7,
p.project_type,
p.project_number,
p.project_name,
NVL(pr.retn_accounting_flag,'N') retn_accounting_flag,
iv.total_revenue_amount,
iv.total_invoiced_amount,
f.meaning costs_burdened,
pr.projfunc_currency_code,
iv.write_off_invoice_amount,
iv.pfc_invoiced_amount,
iv.pfc_total_invoice_amount,
iv.pfc_write_off_invoice_amount,
iv.unbilled_retention,
PA_PAXMGPSD_XMLP_PKG.unbilled_receivablesformula(NVL ( pr.retn_accounting_flag , 'N' ), iv.total_revenue_amount, iv.pfc_total_invoice_amount, iv.unbilled_retention) Unbilled_Receivables,
PA_PAXMGPSD_XMLP_PKG.unearned_revenueformula(NVL ( pr.retn_accounting_flag , 'N' ), iv.pfc_total_invoice_amount, iv.total_revenue_amount, iv.unbilled_retention) Unearned_Revenue,
PA_PAXMGPSD_XMLP_PKG.unbilled_retnformula(NVL ( pr.retn_accounting_flag , 'N' ), iv.unbilled_retention) unbilled_retn
from
pa_project_types pt,
pa_proj_invoice_summary_view iv,
pa_proj_info_view p,
pa_projects pr,
fnd_lookups f
where
pr.template_flag != 'Y'
and pr.project_id = p.project_id
and p.project_id = iv.project_id (+)
and pt.burden_cost_flag = f.lookup_code
and f.lookup_type = 'YES_NO'
and pt.project_type = p.project_type
&p_project_type
&p_project
&p_project_manager
and nvl(p.organization_id,-999) =
nvl(:org_id,nvl(p.organization_id,-999))
and pr.project_id in (select P.project_id from pa_projects P, pa_project_status_controls S
where P.project_status_code = S.project_status_code and
S.status_type = 'PROJECT' and
S.action_code = 'STATUS_REPORTING' and
S.enabled_flag='Y')
order by
project_organization, p.manager_name, p.project_number |