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