SELECT
haou.name Organization,
/* (select class_code
from(select project_id,class_code, dense_rank() over(order by class_code desc)r
from pa_project_classes ppc
where class_category = 'Eka Product'
and ppc.project_id = ppa.project_id
) where r=1
) Acc_num,*/
ppa.project_type Project_Type,
papf.full_Name Project_Manager,
ppa.project_id,
ppa.segment1 Project_Number,
ppa.long_name Project_Name,
paa.agreement_num Agrement_Number,
TO_CHAR (ppa.start_date, 'DD-MON-YY') Project_Start_Date,
TO_CHAR (ppa.completion_date, 'DD-MON-YY') Project_End_Date,
pps.project_status_name Status,
NVL ((SELECT SUM (NVL (allocated_amount, 0))
FROM pa_project_fundings
WHERE project_id = ppa.project_id
AND agreement_id = paa.agreement_id),0
) Funding,
SUM (NVL (projfunc_revenue_amount, 0)) Revenue_Recorded_To_Date,
SUM (NVL (projfunc_bill_amount, 0)) Amount_Invoiced_To_Date,
NVL (((SELECT SUM (NVL (allocated_amount, 0))
FROM pa_project_fundings
WHERE project_id = ppa.project_id
AND agreement_id = paa.agreement_id)
- SUM (NVL (projfunc_revenue_amount, 0))),0
) Balance_Rev_To_Record,
NVL (((SELECT SUM (NVL (allocated_amount, 0))
FROM pa_project_fundings
WHERE project_id = ppa.project_id
AND agreement_id = paa.agreement_id)
- SUM (NVL (projfunc_bill_amount, 0))),0
) Amount_Rem_To_Invoice
FROM
pa_project_types_all ppta,
hr_all_organization_units haou,
pa_projects_all ppa,
per_all_people_f papf,
PA_PROJECT_CUSTOMERS pc,
pa_agreements_all paa,
pa_project_statuses pps,
(SELECT DISTINCT project_id, person_id
FROM pa_project_players
WHERE project_role_type = UPPER ('PROJECT MANAGER')) ppp,
(SELECT DISTINCT agreement_id, project_id FROM PA_PROJECT_FUNDINGS) pdia,
pa_events pe
WHERE 1=1
AND ppa.project_type = ppta.project_type
AND ppta.org_id = ppa.org_id
AND ppa.org_id = 286
AND UPPER (ppta.project_type_class_code) = 'CONTRACT'
AND pps.project_status_code = ppa.project_status_code
AND ppa.CARRYING_OUT_ORGANIZATION_ID = haou.organization_id
AND papf.person_id = ppp.person_id
AND ppp.project_id = ppa.project_id
AND ppa.project_id = pdia.project_id(+)
AND pdia.agreement_id = paa.agreement_id(+)
AND pc.project_id = ppa.project_id
AND ppa.project_id = pe.project_id(+)
AND pps.project_status_name <> 'Closed'
-- AND ppa.project_id BETWEEN NVL (:p_from_project_id, ppa.project_id) AND NVL (:p_to_project_id, ppa.project_id)
-- AND UPPER (haou.name) = UPPER (NVL (:p_organization, haou.name))
-- AND UPPER (ppa.project_type) = UPPER (NVL (:p_project_type, ppa.project_type))
-- AND UPPER (papf.full_name) = UPPER (NVL (:p_project_manager, papf.full_name))
-- AND UPPER (pps.project_status_name) = UPPER (NVL (:p_status, pps.project_status_name))
GROUP BY
haou.name,
ppa.project_type,
papf.full_Name,
ppa.segment1,
ppa.long_name,
paa.agreement_num,
ppa.start_date,
ppa.completion_date,
pps.project_status_name,
ppa.project_id,
paa.agreement_id
ORDER BY
haou.name,
ppa.project_type,
ppa.segment1
/
---------------------------------------------------------------1 --------------------------
SELECT
haou.name Organization
/*,(select class_code
from(select project_id,class_code, dense_rank() over(order by class_code desc)r
from pa_project_classes
where class_category = XXAA Account Number'
and project_id = ppa.project_id
) where r=1
) Acc_num*/
,ppa.project_type Project_Type
,papf.full_Name Project_Manager
,ppa.project_id
,ppa.segment1 Project_Number
,ppa.long_name Project_Name
,paa.agreement_num Agrement_Number
,to_char(ppa.start_date,'DD-MON-YY') Project_Start_Date
,to_char(ppa.completion_date,'DD-MON-YY') Project_End_Date
,pps.project_status_name Status
,nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings
where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) Funding
,pe.event_type Event_Type
,pe.event_num Event_Number
,pt.task_name Task_Name
,to_char(pe.completion_date,'DD-MON-YY') Event_Date
,pe.description Description
--,pe.revenue_amount Revenue_Amount
,pe.BILL_TRANS_REV_AMOUNT Revenue_Amount
,pe.revenue_distributed_flag Revenue_Distributed
--,pe.bill_amount Bill_Amt
,pe.BILL_TRANS_BILL_AMOUNT Bill_Amt
,pe.billed_flag Billed
,nvl(pe.projfunc_revenue_amount,0) Revenue_Recorded_To_Date
,nvl(pe.projfunc_bill_amount,0) Amount_Invoiced_To_Date
,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(projfunc_revenue_amount),0) from pa_events where project_id = ppa.project_id)) Balance_of_revenue_to_record
,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) -(select nvl(sum(projfunc_bill_amount),0) from pa_events where project_id = ppa.project_id)) sum_amount_invoiced_to_date
,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(BILL_TRANS_REV_AMOUNT),0) from pa_events where project_id = ppa.project_id))unscheduled_rev_amt
,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(BILL_TRANS_BILL_AMOUNT),0) from pa_events where project_id = ppa.project_id))unscheduled_bill_event
FROM
pa_project_types_all ppta
,hr_all_organization_units haou
,pa_projects_all ppa
,per_all_people_f papf
,PA_PROJECT_CUSTOMERS pc
,pa_agreements_all paa
,pa_project_statuses pps
,(SELECT DISTINCT project_id, person_id FROM pa_project_players WHERE project_role_type = UPPER('PROJECT MANAGER')) ppp
,(SELECT DISTINCT agreement_id,project_id FROM PA_PROJECT_FUNDINGS) pdia
,pa_events pe
,pa_tasks pt
WHERE 1=1
AND ppa.project_type = ppta.project_type
AND ppta.org_id = ppa.org_id
AND ppa.org_id = 286
AND upper(ppta.project_type_class_code) = 'CONTRACT'
AND pps.project_status_code = ppa.project_status_code
AND ppa.CARRYING_OUT_ORGANIZATION_ID=haou.organization_id
AND papf.person_id = ppp.person_id
AND ppp.project_id = ppa.project_id
AND ppa.project_id = pdia.project_id(+)
AND pdia.agreement_id = paa.agreement_id(+)
AND pc.project_id = ppa.project_id
AND ppa.project_id = pe.project_id(+)
AND pe.task_id = pt.task_id(+)
AND pps.project_status_name <> 'Closed'
GROUP BY
haou.name
,ppa.project_type
,papf.full_Name
,ppa.segment1
,ppa.long_name
,paa.agreement_num
,ppa.start_date
,ppa.completion_date
,pps.project_status_name
,pe.event_type
,pe.event_num
,pt.task_name
,pe.completion_date
,pe.description
--,pe.revenue_amount
,pe.BILL_TRANS_REV_AMOUNT
,pe.revenue_distributed_flag
--,pe.bill_amount
,pe.BILL_TRANS_BILL_AMOUNT
,pe.billed_flag
,pe.projfunc_revenue_amount
,pe.projfunc_bill_amount
,ppa.project_id
,paa.agreement_id
ORDER BY
haou.name
,ppa.project_type
,ppa.project_id
,pe.event_num