PA Invoice Review

Description
Categories: BI Publisher, Financials
Application: Projects
Source: MGT: Invoice Review (XML)
Short Name: PAXINGEN_XML
DB package: PA_PAXINGEN_XMLP_PKG
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
Parameter Name SQL text Validation
Display Unbilled Items
 
LOV Oracle
Display Details
 
LOV Oracle
Invoice Status
 
LOV Oracle
Project Role Type
 
LOV Oracle
Project Member
 
LOV Oracle
Start Organization
 
LOV Oracle
Draft Invoice Number
 
Project Closed After
 
Date
Project Status
 
LOV Oracle
To Project Number
 
From Project Number
 
Project Number
 
LOV Oracle
Ask a question