ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F2B23869E053B46B63588994Y SELECT substr(o.name,1,60) organization, per.full_name project_manager, p.segment1 project_number, p.name project_name, nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0) bucket1, nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0) bucket2, nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0) bucket3, nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0) bucket4, nvl(tu.event_inv_amount,0) event_invoiced_amount, nvl(tu.cost_work_amount,0) cost_work_amount, nvl(tu.invoice_reduction,0) invoice_reduction, nvl(tu.retention,0) retention, p.project_currency_code, PA_PAXMGURA_XMLP_PKG.C_COLHEAD1_p C_COLHEAD1, PA_PAXMGURA_XMLP_PKG.C_COLHEAD2_p C_COLHEAD2, PA_PAXMGURA_XMLP_PKG.C_COLHEAD3_p C_COLHEAD3, PA_PAXMGURA_XMLP_PKG.C_COLHEAD4_p C_COLHEAD4, PA_PAXMGURA_XMLP_PKG.final_bucket1formula('BUCKET1',nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) FINAL_BUCKET1, PA_PAXMGURA_XMLP_PKG.final_bucket2formula('BUCKET2',nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) FINAL_BUCKET2, PA_PAXMGURA_XMLP_PKG.final_bucket3formula('BUCKET3',nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) FINAL_BUCKET3, PA_PAXMGURA_XMLP_PKG.final_bucket4formula('BUCKET4',nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) FINAL_BUCKET4, PA_PAXMGURA_XMLP_PKG.totalformula(nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) TOTAL FROM hr_organization_units o, per_people_f per, pa_project_players pl, pa_projects p, pa_project_types pr_type, /*Added for bug 7115658*/ pa_unbilled_rec_reporting tu WHERE p.carrying_out_organization_id between nvl(:org_id,0) and nvl(:org_id,999999999999999) and o.organization_id = p.carrying_out_organization_id and nvl(:date_from,sysdate) between pl.start_date_active and nvl(pl.end_date_active,nvl(:date_from,sysdate+1)) and per.person_id between nvl(:project_manager_id,0) and nvl(:project_manager_id,999999999999999) and per.person_id = pl.person_id and per.employee_number is not null and trunc(sysdate) between per.effective_start_date and per.effective_end_date and pl.project_role_type = 'PROJECT MANAGER' /*and p.project_id between nvl(:proj,0) and nvl(:proj, 999999999999999) Commented the above and added below conditions for bug 7115658*/ and ((:proj is not null and p.project_id = :proj) or (:proj is null /* 20027987 and p.segment1 between :from_project_number and :to_project_number */ and p.segment1 between :from_pn and :to_pn /*20027987*/ and ((nvl(:project_status,'ALL') = 'ALL_EXCEPT_CLOSED' and p.project_status_code <> 'CLOSED' ) OR (nvl(:project_status,'ALL') <> 'ALL_EXCEPT_CLOSED' and p.project_status_code = decode(nvl(:project_status,'ALL'), 'ALL',p.project_status_code, 'ALL_EXCEPT_CLOSED', p.project_status_code, nvl(:project_status,'ALL')) ) ) and ((nvl(:project_status,'ALL') not in ('ALL', 'CLOSED') and p.closed_date is null ) OR (nvl(:project_status,'ALL') in ('ALL','CLOSED') and nvl(p.closed_date,sysdate) > nvl(:project_closed_after,sysdate-365) ) ) ) /*Close of or proj is null*/ ) and p.project_type = pr_type.project_type and pr_type.project_type_class_code = 'CONTRACT' and pl.project_id = p.project_id /*end of code added for bug 7115658*/ and p.project_id = tu.project_id and nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0)+ nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0)+ nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0)+ nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0)+ nvl(tu.event_inv_amount,0)+ nvl(tu.cost_work_amount,0)+ nvl(tu.invoice_reduction,0)+ nvl(tu.retention,0) <> 0 ORDER BY 1 ASC,2 ASC , o.name , per.full_name , p.segment1 PA PAXMGURA_XML AR PA تقادم المقبوضات بدون فاتورة (بتنسيق XML) Application: المشروعات Source: MGT: تقادم المقبوضات بدون فاتورة (بتنسيق XML) Short Name: PAXMGURA_XML DB package: PA_PAXMGURA_XMLP_PKG D PA Nicht fakturierte Forderungen/Fälligkeit Application: Projects Source: PA: Nicht fakturierte Forderungen/Fälligkeit (XML) Short Name: PAXMGURA_XML DB package: PA_PAXMGURA_XMLP_PKG F PA Balance âgée des factures à établir Application: Projects Source: MGT : Balance âgée des factures à établir (XML) Short Name: PAXMGURA_XML DB package: PA_PAXMGURA_XMLP_PKG US PA Unbilled Receivables Aging Application: Projects Source: MGT: Unbilled Receivables Aging (XML) Short Name: PAXMGURA_XML DB package: PA_PAXMGURA_XMLP_PKG ZHS PA MGT：未开单应收款帐龄 Application: 项目 Source: MGT：未开单应收款帐龄 (XML) Short Name: PAXMGURA_XML DB package: PA_PAXMGURA_XMLP_PKG BI Publisher :age :bucket_size1 :bucket_size2 :bucket_size3 :c_colhead1 :c_colhead2 :c_colhead3 :c_colhead4 :c_company_name_header :c_no_data_found :date_from :from_pn :from_project_number :operating_unit :org_id :p_conc_request_id :p_debug_mode :p_org_id :p_rule_optimizer :proj :project_closed_after :project_manager_id :project_status :to_pn :to_project_number 1 10 :date_from Date AR تاريخ الفعالية D Gültig ab F Date de validité US Effective Date ZHS 有效日期 2 20 :org_id LOV Oracle PA_SRS_PROJECT_ORGANIZATION Y Y select organization_id id, name value, null description from pa_organizations_project_v where active_flag='Y' order by value,description AR تنظيم المشروع D Projektorganisation F Organisation du projet US Project Organization ZHS 项目组织 3 30 :project_manager_id LOV Oracle PA_SRS_PROJECT_MANAGERS Y select person_id id, full_name value, employee_number description from pa_project_managers_v order by value,description AR مدير المشروع D Projektmanager F Chef de projet US Project Manager ZHS 项目经理 4 40 :proj LOV Oracle PA_SRS_PROJECT_ID_CONTRACT Y Y select project_id id, project_number value, project_name description from pa_projects_basic_v where template_flag!='Y' and project_type_class_code='CONTRACT' and cc_prvdr_flag='N' order by project_number_sort_order AR رقم المشروع D Projektnummer F Numéro de projet US Project Number ZHS 项目编号 5 50 :from_project_number Char AR من رقم المشروع D Von Projektnummer F Du numéro de projet US From Project Number ZHS 自项目编号 6 60 :to_project_number Char AR إلى رقم المشروع D Bis Projektnummer F Au numéro de projet US To Project Number ZHS 至项目编号 7 70 :project_status LOV Oracle PA_SRS_PROJ_STATUSES Y select project_status_code id, project_status_name value, null description from pa_proj_statuses_v order by value,description select project_status_name from pa_proj_statuses_v where project_status_code = 'ALL_EXCEPT_CLOSED' AR موقف المشروع D Projektstatus F Statut du projet US Project Status ZHS 项目状态 8 80 :project_closed_after Date select sysdate-365 from dual AR المشروع المقفل بعد D Projekt geschlossen nach F Projet fermé après US Project Closed After ZHS 项目关闭起始日期 9 90 :age LOV Oracle PA_SRS_AGE_UR_FROM Y select lookup_code id, meaning value, null description from pa_lookups where lookup_type='AGE UNBILLED RECEIVABLES FROM' order by meaning select meaning from pa_lookups where lookup_type = 'AGE UNBILLED RECEIVABLES FROM' and lookup_code = 'EXPENDITURE_ITEM_DATE' Y AR مقبوضات العمر من D Fällige Forderungen von F Factures âgées depuis US Age Receivables From ZHS 应收款管理系统的帐龄分析起始日期 10 100 :bucket_size1 Number 30 AR عدد الأيام في رزمة 1 D Anzahl von Tagen in Zeitraum 1 F Nombre de jours dans la catégorie 1 US Number of days in Bucket 1 ZHS 第 1 个时段内的天数 11 110 :bucket_size2 Number 30 AR عدد الأيام في رزمة 2 D Anzahl von Tagen in Zeitraum 2 F Nombre de jours dans la catégorie 2 US Number of days in Bucket 2 ZHS 第 2 个时段内的天数 12 120 :bucket_size3 Number 30 AR عدد الأيام في رزمة 3 D Anzahl von Tagen in Zeitraum 3 F Nombre de jours dans la catégorie 3 US Number of days in Bucket 3 ZHS 第 3 个时段内的天数