Tuesday, September 14, 2010

Sql Script to find Projects which are funded by multiple Sales Order (Agreement)

SELECT paa.agreement_num sales_order, ppa.segment1 project_number,
paps.project_status_name project_status,
pspf.total_baselined_amount funding_amount,
TRUNC (pspf.last_update_date) last_funding_date, '-Budget-',
pbv.revenue,PA.FULL_NAME PROJECT_MANAGER
FROM pa_projects_all ppa,
(SELECT pspf.project_id
FROM pa_summary_project_fundings pspf
WHERE 1 = 1
GROUP BY pspf.project_id
HAVING COUNT (*) > 1) multi,
(SELECT *
FROM pa_budget_versions
WHERE budget_type_code = 'Revenue Budget'
AND budget_status_code = 'B'
AND current_flag = 'Y') pbv,
pa_summary_project_fundings pspf,
pa_project_classes ppct,
pa_agreements_all paa,
pa_employees pa,
pabv_key_members pkm
WHERE multi.project_id = ppa.project_id
AND pspf.project_id = ppa.project_id
AND paa.agreement_id = pspf.agreement_id
AND paps.project_status_code = ppa.project_status_code
AND ppa.project_id = pbv.project_id(+)
AND pa.person_id = pkm.person_id
AND pkm.project_id = ppa.project_id
AND pkm.project_role_type = 'PROJECT MANAGER'
and NVL (PKM.END_EFFECTIVE_DATE, TRUNC (sysdate)) >= TRUNC (sysdate)
and NVL (PA.EFFECTIVE_END_DATE, TRUNC (sysdate)) >= TRUNC (sysdate)
AND current_flag = 'Y'
AND paps.project_status_name <> 'Not Acquired'
AND ppa.template_flag = 'N'

No comments: