PA Project Billing Status

Description
Categories: BI Publisher, Financials
Application: Projects
Source: MGT: Project Billing Status (XML)
Short Name: PAXMGPBS_XML
DB package: PA_PAXMGPBS_XMLP_PKG
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
Parameter Name SQL text Validation
Thru Next Bill Date
 
Date
Billing Method
 
LOV Oracle
Only Report Never Billed
 
LOV Oracle
Days Since Last Billing
 
Number
Project Number
 
LOV Oracle
Project Role Type
 
LOV Oracle
Project Member
 
LOV Oracle
Start Organization
 
LOV Oracle