ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5BE1B4033DED472599930541F1B78A35Y select &lp_columns x.supplier_name, x.supplier_number, x.type, &period_columns sum(x.invoice_amount) total, x.currency from ( select gl.name ledger, hou.name operating_unit, aps.vendor_name supplier_name, aps.segment1 supplier_number, xxen_util.meaning(aps.vendor_type_lookup_code,'VENDOR TYPE',201) type, trunc(gp.start_date+8,'mm') period_month, aia.invoice_amount, aia.invoice_currency_code currency from gl_ledgers gl, hr_operating_units hou, ap_invoices_all aia, ap_suppliers aps, gl_periods gp where 1=1 and gl.ledger_id=hou.set_of_books_id and hou.organization_id=aia.org_id and aia.vendor_id=aps.vendor_id and gl.period_set_name=gp.period_set_name and gl.accounted_period_type=gp.period_type and gp.adjustment_period_flag='N' and aia.gl_date>=gp.start_date and aia.gl_date<gp.end_date+1 ) x group by &lp_columns x.supplier_name, x.supplier_number, x.type, x.currency order by &lp_columns x.supplier_name, x.currency Initial version #,##0.00;[Red]-#,##0.00 (:Ledger is not null or :Operating_Unit is not null) US AP Suppliers Revenue by Period AP invoice amount per supplier and invoice currency, with one column per GL period from Period From to Period To. An invoice is counted in the period of its invoice GL date. Periods are taken from the ledger's accounting calendar, excluding adjustment periods, and each column is named after the month the period falls in. Please enter a Ledger or Operating Unit Enginatics &lp_columns &period_columns 1=1 1 10 1=1 gl.name=:ledger and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='LEDGER') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) US Ledger 2 20 1=1 hou.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name US Operating Unit 3 30 1=1 aps.vendor_name=:supplier_name LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US Supplier 4 40 1=1 aps.segment1=:supplier_number LOV AP Supplier Number 8E2FF36EDF1479D2E0530100007F1FF2 Y select aps.segment1 value, aps.vendor_name description from ap_suppliers aps where :$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id) order by aps.segment1, aps.vendor_id US Supplier Number 5 50 1=1 aia.gl_date>=(select gp0.start_date from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gl.accounted_period_type=gp0.period_type and gp0.period_name=:period_from) LOV GL Period (past) 8E2FF36EDED479D2E0530100007F1FF2 Y select distinct gp.period_name value, ( select xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description from gl_period_statuses gps where gp.period_name=gps.period_name and gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger) and gps.application_id=101 ) description, min(gp.start_date) over (partition by gp.period_name) min_start_date from gl_periods gp where gp.start_date<=sysdate+400 and (gp.period_set_name,gp.period_type) in ( select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or gl.chart_of_accounts_id in (select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y')) ) order by min_start_date desc, gp.period_name Y US Period From 6 &period_columns select 'sum(case when x.period_month=to_date('''||to_char(y.period_month,'YYYYMM')||''',''YYYYMM'') then x.invoice_amount end) '||to_char(y.period_month,'mon_yyyy','nls_date_language=american')||',' from ( select distinct trunc(gp.start_date+8,'mm') period_month from gl_ledgers gl, gl_periods gp_from, gl_periods gp_to, gl_periods gp where (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and (:operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:operating_unit,hou.name)='Y')) and gl.period_set_name=gp_from.period_set_name and gl.accounted_period_type=gp_from.period_type and gp_from.period_name=:period_from and gl.period_set_name=gp_to.period_set_name and gl.accounted_period_type=gp_to.period_type and gp_to.period_name=:period_to and gl.period_set_name=gp.period_set_name and gl.accounted_period_type=gp.period_type and gp.adjustment_period_flag='N' and gp.start_date>=gp_from.start_date and gp.end_date<=gp_to.end_date order by period_month ) y US Period From 7 60 1=1 aia.gl_date<(select gp0.end_date+1 from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gl.accounted_period_type=gp0.period_type and gp0.period_name=:period_to) LOV GL Period (past) 8E2FF36EDED479D2E0530100007F1FF2 Y select distinct gp.period_name value, ( select xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description from gl_period_statuses gps where gp.period_name=gps.period_name and gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger) and gps.application_id=101 ) description, min(gp.start_date) over (partition by gp.period_name) min_start_date from gl_periods gp where gp.start_date<=sysdate+400 and (gp.period_set_name,gp.period_type) in ( select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or gl.chart_of_accounts_id in (select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y')) ) order by min_start_date desc, gp.period_name :$flex$.period_from Y US Period To 8 70 &lp_columns x.ledger, LOV custom Y select 'Global' value, null description from dual union all select 'Ledger' value, null description from dual union all select 'Operating Unit' value, null description from dual Ledger Global Y US Summary Level 9 &lp_columns x.ledger, x.operating_unit, Operating Unit US Summary Level