select
t1.project_id,
t1.task_id q_task_task_id,
pt.direct_flag direct,
p.project_name,
p.project_number,
p.project_status,
p.start_date,
p.completion_date,
substr(p.organization_name,1,60) project_organization ,
upper(pt.project_type_class_code) ptcc ,
p.project_currency_code,
pr.multi_currency_billing_flag,
pl.meaning inv_proc_curr_type,
pr.projfunc_currency_code,
pl1.meaning projfunc_bil_rate_date_code,
(select user_conversion_type from pa_conversion_types_v where conversion_type = pr.projfunc_bil_rate_type) projfunc_bil_rate_type,
pr.projfunc_bil_rate_date,
pr.projfunc_bil_exchange_rate,
pl2.meaning project_bil_rate_date_code,
(select user_conversion_type from pa_conversion_types_v where conversion_type = pr.project_bil_rate_type) project_bil_rate_type,
pr.project_bil_rate_date,
pr.project_bil_exchange_rate,
pl3.meaning funding_rate_date_code,
(select user_conversion_type from pa_conversion_types_v where conversion_type = pr.funding_rate_type) funding_rate_type,
pr.funding_rate_date,
pr.funding_exchange_rate,
gl.user_conversion_type projfunc_cost_rate_type,
t1.taskfunc_cost_rate_date projfunc_cost_rate_date,
t1.cc_process_labor_flag,
t1.cc_process_nl_flag,
ltp.name ltp_name,
t1.labor_tp_fixed_date,
nltp.name nltp_name,
t1.nl_tp_fixed_date,
t2.task_number cc_tax_task_number,
pr.baseline_funding_flag,
t1.customer_id,
t1.invoice_method,
t1.revenue_accrual_method,
PA_PAXPRTSK_XMLP_PKG.cf_revenue_accrual_mthformula(t1.revenue_accrual_method, t1.project_id) CF_REVENUE_ACCRUAL_MTH,
PA_PAXPRTSK_XMLP_PKG.cf_inv_mthformula(t1.invoice_method, t1.project_id) CF_INV_MTH,
PA_PAXPRTSK_XMLP_PKG.cf_customer_numberformula(t1.customer_id, t1.project_id) CF_CUSTOMER_NUMBER,
PA_PAXPRTSK_XMLP_PKG.cf_customer_nameformula(t1.customer_id, t1.project_id) CF_CUSTOMER_NAME
from
pa_proj_info_view p,
pa_projects_all pr,
pa_project_types pt,
pa_tasks t1,
pa_tasks t2,
pa_lookups pl,
pa_lookups pl1,
pa_lookups pl2,
pa_lookups pl3,
pa_cc_tp_schedules ltp,
pa_cc_tp_schedules nltp,
GL_DAILY_CONVERSION_TYPES_V gl
where p.project_id = pr.project_id
and p.project_type = pt.project_type
and p.project_id = t1.project_id
and pr.CC_TAX_task_id = t2.task_id(+)
and pr.invproc_currency_type = pl.lookup_code(+)
and nvl(pl.lookup_type,'INVPROCE_CURR_TYPE') = 'INVPROCE_CURR_TYPE'
and pr.projfunc_bil_rate_date_code = pl1.lookup_code(+)
and nvl(pl1.lookup_type,'BILL_RATE_DATE_CODE') = 'BILL_RATE_DATE_CODE'
and pr.project_bil_rate_date_code = pl2.lookup_code(+)
and nvl(pl2.lookup_type,'BILL_RATE_DATE_CODE') = 'BILL_RATE_DATE_CODE'
and pr.funding_rate_date_code = pl3.lookup_code(+)
and nvl(pl3.lookup_type,'BILL_RATE_DATE_CODE') = 'BILL_RATE_DATE_CODE'
and t1.labor_tp_schedule_id = ltp.tp_schedule_id(+)
and t1.nl_tp_schedule_id = nltp.tp_schedule_id(+)
and p.project_id = :proj
and decode(:top_task_id, '', t1.parent_task_id,'All', t1.parent_task_id, null) is null
and decode(:top_task_id, '', t1.task_id,'All', t1.task_id, :top_task_id) = t1.task_id
and t1.taskfunc_cost_rate_type = gl.conversion_type(+)
ORDER BY t1.task_number |