PA Invoice Review

select substr(p.organization_name,1,60) project_organization 
      ,p.member_name Project_member_1
      ,p.project_role role_type
      ,p.project_id project_id2 
      ,p.project_number
      ,p.project_name
      ,p.description
      ,NVL(pr.retn_accounting_flag,'N') Retn_accounting_flag 
      ,decode(pr_type.cc_prvdr_flag,'Y','Yes','No') cc_flag
      ,sum(nvl(p.budget_revenue_amount, 0)) budget_revenue
      ,sum(nvl(p.total_revenue_amount, 0)) total_revenue
      ,sum(nvl(p.write_on_revenue_amount, 0)) write_on
      ,sum(nvl(p.write_off_revenue_amount, 0)) write_off
      ,sum(nvl(p.projfunc_tot_inv_amount, 0)) Total_invoiced
      ,sum(nvl(p.projfunc_tot_retn_amount, 0)) retentions
      ,sum(nvl(p.projfunc_retn_billed_amount,0)) Billed_retention
      ,sum(nvl(p.projfunc_invoice_amount, 0)) invoiced
      ,sum(NVL(p.total_revenue_amount,0))- sum(NVL(p.projfunc_tot_inv_amount,0)) + decode(NVL(pr.retn_accounting_flag,'N'),'Y',0,sum(NVL(p.projfunc_tot_retn_amount,0))-sum(nvl(p.projfunc_retn_billed_amount,0))) unbilled_receivable
      ,decode(NVL(pr.retn_accounting_flag,'N'),'N',0,sum(NVL(p.projfunc_tot_retn_amount,0))-sum(nvl(p.projfunc_retn_billed_amount,0))) unbilled_retention
      ,sum(nvl(p.on_hold_amount, 0)) Amount_on_hold
      ,sum(nvl(p.on_hold_quantity, 0)) items_on_hold
      ,sum(NVL(p.invoice_writeoff,0)) invoice_write_off
      ,PA_PAXINGEN_XMLP_PKG.c_ubr_uerformula(sum ( NVL ( p.total_revenue_amount , 0 ) ) - sum ( NVL ( p.projfunc_tot_inv_amount , 0 ) ) + decode ( NVL ( pr.retn_accounting_flag , 'N' ) , 'Y' , 0 , sum ( NVL ( p.projfunc_tot_retn_amount , 0 ) ) - sum ( nvl ( p.projfunc_retn_billed_amount , 0 ) ) )) C_ubr_uer
      ,PA_PAXINGEN_XMLP_PKG.CF_PROJECT_CURRENCYFormula(p.project_id) CF_PROJECT_CURRENCY
      ,PA_PAXINGEN_XMLP_PKG.C_ubr_uer_labelformula(sum(NVL(p.total_revenue_amount,0))- sum(NVL(p.projfunc_tot_inv_amount,0))+ decode(NVL(pr.retn_accounting_flag,'N'),'Y',0,sum(NVL(p.projfunc_tot_retn_amount,0))-sum(nvl(p.projfunc_retn_billed_amount,0)))) C_ubr_uer_label
from pa_proj_summary_amounts_v p,
--            pa_projects_all pr, pa_project_types_all pr_type
            pa_projects pr, pa_project_types pr_type
 where :project_id is not null
   and p.project_id = :project_id
   and p.member_person_id = nvl(:project_member,p.member_person_id)
   and p.project_role_type = nvl(:Project_role_type,
	'PROJECT MANAGER') 
 and   pr.project_id = :project_id
and  pr.project_type = pr_type.project_type
 and  nvl(pr.org_id, -99) = nvl(pr_type.org_id, -99)            
group by p.organization_name
      ,p.member_name
      ,p.project_role
      ,p.project_id
      ,p.project_number
      ,p.project_name
      ,p.description
     ,decode(pr_type.cc_prvdr_flag,'Y','Yes','No') 
     ,NVL(pr.retn_accounting_flag,'N') 
UNION ALL
  select substr(p.organization_name,1,60) project_organization 
        ,p.member_name Project_member_1
        ,p.project_role role_type
        ,p.project_id project_id2
        ,p.project_number
        ,p.project_name
        ,p.description
        ,NVL(pr.retn_accounting_flag,'N')  
        ,decode(pr_type.cc_prvdr_flag,'Y','Yes','No') cc_flag
        ,sum(nvl(p.budget_revenue_amount, 0)) budget_revenue
        ,sum(nvl(p.total_revenue_amount, 0)) total_revenue
        ,sum(nvl(p.write_on_revenue_amount, 0)) write_on
        ,sum(nvl(p.write_off_revenue_amount, 0)) write_off
        ,sum(nvl(p.projfunc_tot_inv_amount, 0)) Total_invoiced
        ,sum(nvl(p.projfunc_tot_retn_amount, 0)) retentions
        ,sum(nvl(p.projfunc_retn_billed_amount,0)) Billed_retention
        ,sum(nvl(p.projfunc_invoice_amount, 0)) invoiced
        ,sum(NVL(p.total_revenue_amount,0)) - sum(NVL(p.projfunc_tot_inv_amount,0)) + decode(NVL(pr.retn_accounting_flag,'N'),'Y',0,sum(NVL(p.projfunc_tot_retn_amount,0))-sum(nvl(p.projfunc_retn_billed_amount,0))) unbilled_receivable
        ,decode(NVL(pr.retn_accounting_flag,'N'),'N',0,sum(NVL(p.projfunc_tot_retn_amount,0))-sum(nvl(p.projfunc_retn_billed_amount,0))) unbilled_retention      
        ,sum(nvl(p.on_hold_amount, 0)) Amount_on_hold
        ,sum(nvl(p.on_hold_quantity, 0)) items_on_hold
        ,sum(NVL(p.invoice_writeoff,0))
  	,PA_PAXINGEN_XMLP_PKG.c_ubr_uerformula(sum ( NVL ( p.total_revenue_amount , 0 ) ) - sum ( NVL ( p.projfunc_tot_inv_amount , 0 ) ) + decode ( NVL ( pr.retn_accounting_flag , 'N' ) , 'Y' , 0 , sum ( NVL ( p.projfunc_tot_retn_amount , 0 ) ) - sum ( nvl ( p.projfunc_retn_billed_amount , 0 ) ) )) C_ubr_uer
  	,PA_PAXINGEN_XMLP_PKG.CF_PROJECT_CURRENCYFormula(p.project_id) CF_PROJECT_CURRENCY
  	,PA_PAXINGEN_XMLP_PKG.C_ubr_uer_labelformula(sum(NVL(p.total_revenue_amount,0)) - sum(NVL(p.projfunc_tot_inv_amount,0)) + decode(NVL(pr.retn_accounting_flag,'N'),'Y',0,sum(NVL(p.projfunc_tot_retn_amount,0))-sum(nvl(p.projfunc_retn_billed_amount,0))) ) C_ubr_uer_label
  from pa_proj_summary_amounts_v p
  	       , pa_projects pr
       , pa_project_types pr_type
     where :project_id is null
        /*Added for bug 7115649 */
        and pr.segment1 between 
        /* below code change for bug 10405513 */
            decode(:from_project_number,NULL,(select min(pa.segment1) 
              	   FROM pa_projects pa , pa_project_types ppt
                 where pa.project_type = ppt.project_type
                 and ppt.project_type_class_code = 'CONTRACT'),:from_project_number)  
            and 
            decode(:to_project_number,NULL,(select max(pa.segment1)
              	   FROM pa_projects pa , pa_project_types ppt
                 where pa.project_type = ppt.project_type
                 and ppt.project_type_class_code = 'CONTRACT'),:to_project_number) 
        /* Added the above NVL conditions for bug 10405513 */
        and ((:project_status = 'ALL_EXCEPT_CLOSED'
                   and pr.project_status_code <> 'CLOSED'
                )
          OR (:project_status <> 'ALL_EXCEPT_CLOSED'
                and pr.project_status_code = decode(:project_status,
                              'ALL', pr.project_status_code,
                              'ALL_EXCEPT_CLOSED', pr.project_status_code,
                              :project_status)
                )
               ) 
       and ((:project_status not in ('ALL', 'CLOSED') 
                  and pr.closed_date is null
               )
         OR (:project_status in ('ALL','CLOSED') 
                     and  nvl(pr.closed_date,sysdate)  
                                       > nvl(:project_closed_after,sysdate-365)
               )
              )  
   and p.member_person_id =  nvl(:project_member,p.member_person_id)
   and p.project_role_type = nvl(:Project_role_type,'PROJECT MANAGER') 
   and pr.project_id = p.project_id
   and pr.project_type = pr_type.project_type
    and pr_type.project_type_class_code = 'CONTRACT' /*Added for bug 7115649*/
  group by p.organization_name
        ,p.member_name
        ,p.project_role
        ,p.project_id
        ,p.project_number
        ,p.project_name
        ,p.description
        ,decode(pr_type.cc_prvdr_flag,'Y','Yes','No')
        ,NVL(pr.retn_accounting_flag,'N') 
   ORDER BY 1, 2, 5