PA Project Billing Status

select  to_number(null),  
           substr(p.organization_name,1,60) project_organization, 
            p.member_name manager_name,
            p.project_role,
            p.project_number,
            p.project_name name,
            b.billing_method,
            b.last_released_invoice_num,
            TO_CHAR(TO_DATE(b.last_released_invoice_date,'DD-MON-YY'),'DD-MON-YY') last_released_invoice_date,
            TO_CHAR(TO_DATE(b.last_released_bill_thru_date,'DD-MON-YY'),'DD-MON-YY') last_released_bill_thru_date,
            b.days_since_last_release,
              TO_CHAR(TO_DATE(b.next_billing_date,'DD-MON-YY'),'DD-MON-YY') next_billing_date,
            b.next_event_amount,
            b.pending_invoice_amount,
            b.revenue_amount,
            b.invoice_amount,
            greatest(b.unbilled_receivable_amount,0,0)   unbilled_receivable_amount,
            b.next_event_projfunc_amount,
            b.pending_projfunc_inv_amount,
            b.projfunc_revenue_amount,
            b.projfunc_invoice_amount,
            greatest(b.projfunc_ubr_amount,0,0) projfunc_ubr_amount,
            projfunc_unbilled_retention, 
            b.enable_top_task_inv_mth_flag, 
	PA_PAXMGPBS_XMLP_PKG.cf_1formula(b.enable_top_task_inv_mth_flag) CF_flag_meaning, 
	PA_PAXMGPBS_XMLP_PKG.cf_ubrformula(greatest ( b.projfunc_ubr_amount , 0 , 0 )) CF_UBR
from   pa_proj_members_view p,
        pa_proj_billing_status_view b,
        pa_projects proj
where    p.member_person_id =           
         nvl(:project_member,p.member_person_id)
and   p.project_role_type = 
       nvl(:PROJECT_ROLE_TYPE,'PROJECT MANAGER')
and proj.template_flag = 'N' 
and   p.project_id = b.project_id   
and   p.project_id = proj.project_id
and (pa_project_stus_utils.is_project_status_closed(proj.project_status_code) != 'Y' OR 
        (pa_project_stus_utils.is_project_status_closed(proj.project_status_code) = 'Y' AND
         proj.unbilled_receivable_dr != 0)
      )
and   b.project_id = :project
and   nvl(b.days_since_last_release,0) >= 
        nvl(:days_since,-99999)
and  decode(:never_billed,'Y',b.last_released_invoice_num,null)
        is null
and  nvl(b.billing_method ,'1') =   
       nvl(:billing_method,nvl(b.billing_method,'1'))
and  nvl(b.last_released_bill_thru_date ,to_date('19000101','YYYYMMDD')) <= 
        nvl(to_char(:bill_thru_date),
              nvl(b.last_released_bill_thru_date,sysdate))
and  :project is not null
UNION ALL
select  org.organization_id,
            substr(p.organization_name,1,60) project_organization, 
            p.member_name manager_name,
            p.project_role,
            p.project_number,
            p.project_name name,
            b.billing_method,
            b.last_released_invoice_num,
            TO_CHAR(TO_DATE(b.last_released_invoice_date,'DD-MON-YY'),'DD-MON-YY'),
           TO_CHAR(TO_DATE(b.last_released_bill_thru_date,'DD-MON-YY'),'DD-MON-YY'),
            b.days_since_last_release,
            TO_CHAR(TO_DATE(b.next_billing_date,'DD-MON-YY'),'DD-MON-YY'),
            b.next_event_amount,
            b.pending_invoice_amount,
            b.revenue_amount,
            b.invoice_amount,
            greatest(b.unbilled_receivable_amount,0,0)   unbilled_receivable_amount,
            b.next_event_projfunc_amount,
            b.pending_projfunc_inv_amount,
            b.projfunc_revenue_amount,
            b.projfunc_invoice_amount,
            greatest(b.projfunc_ubr_amount,0,0) projfunc_ubr_amount,
            projfunc_unbilled_retention, 
            b.enable_top_task_inv_mth_flag, 
	PA_PAXMGPBS_XMLP_PKG.cf_1formula(b.enable_top_task_inv_mth_flag) CF_flag_meaning, 
	PA_PAXMGPBS_XMLP_PKG.cf_ubrformula(greatest ( b.projfunc_ubr_amount , 0 , 0 )) CF_UBR
from pa_org_reporting_view org, 
        pa_proj_members_view p,
        pa_proj_billing_status_view b,
        pa_projects proj
where 
         decode(:project,null,org.organization_id,p.organization_id)
         = p.organization_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  proj.template_flag = 'N' 
and   p.project_id = b.project_id
and   p.project_id = proj.project_id
and (pa_project_stus_utils.is_project_status_closed(proj.project_status_code) != 'Y' OR 
        (pa_project_stus_utils.is_project_status_closed(proj.project_status_code)= 'Y' AND
         proj.unbilled_receivable_dr != 0)
      )
and   nvl(b.days_since_last_release,0) >= 
        nvl(:days_since,-99999)
and  decode(:never_billed,'Y',b.last_released_invoice_num,null)
        is null
and  nvl(b.billing_method ,'1') =   
       nvl(:billing_method,nvl(b.billing_method,'1'))
and  nvl(b.last_released_bill_thru_date ,to_date('19000101', 'YYYYMMDD')) <= 
        nvl(to_char(:bill_thru_date),
              nvl(b.last_released_bill_thru_date,sysdate))
and  :project is null
ORDER BY
     2, 3, 7, 5, 6