PA Project Billing Status

Description
Categories: BI Publisher
Application: Projects
Source: MGT: Project Billing Status (XML)
Short Name: PAXMGPBS_XML
DB package: PA_PAXMGPBS_XMLP_PKG
Run PA Project Billing Status and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Start Organization
 
LOV Oracle
Project Member
 
LOV Oracle
Project Role Type
 
LOV Oracle
Project Number
 
LOV Oracle
Days Since Last Billing
 
Number
Only Report Never Billed
 
LOV Oracle
Billing Method
 
LOV Oracle
Thru Next Bill Date
 
Date
Blitz Report™