ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEB879D2E0530100007F1FF2 GL Ledger 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 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 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 AR دفاتر الأستاذ المقيدة بمجموعة الوصول ، المحددة بواسطة قيمة الملف الشخصي "مجموعة الوصول إلى بيانات GL" لمسؤولية تسجيل الدخول الحالية D Ledger, die durch das Zugriffsset eingeschränkt sind, definiert durch den Profilwert 'GL Data Access Set' der aktuellen Anmeldeverantwortung E Libros restringidos por el conjunto de acceso, definido por el valor del perfil "Conjunto de acceso a datos GL" de la responsabilidad de inicio de sesión actual F Grands livres limités par un ensemble d'accès, défini par la valeur du profil "GL Data Access Set" de la responsabilité de connexion actuelle I Libri mastri limitati dal set di accesso, definito dal valore del profilo 'GL Data Access Set' della responsabilità di login corrente JA 現在のログイン責任者のプロファイル値'GL Data Access Set'によって定義されたアクセスセットによって制限された元帳 KO 현재 로그인 권한의 프로파일 값 'GL 데이터 액세스 세트'로 정의 된 액세스 세트로 제한되는 원장 PTB Ledgers restritos pelo conjunto de acesso, definido pelo valor do perfil 'GL Data Access Set' da atual responsabilidade de login RU Леджеры, ограниченные набором доступа, определяемые значением профиля "GL Data Access Set" текущей ответственности за вход в систему S Ledgers begränsade av åtkomstuppsättning, definierade av profilvärde 'GL Data Access Set' för det aktuella inloggningsansvaret TR Mevcut oturum açma sorumluluğunun profil değeri 'GL Veri Erişim Kümesi' ile tanımlanan erişim kümesiyle kısıtlanmış defterler US Ledgers restricted by access set, defined by profile value 'GL Data Access Set' of the current login responsibility ZHS 由访问集限制的分类账，由当前登录责任的配置文件值 "GL数据访问集 "定义。 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit 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 mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 D7C6B5A4E3F2D1C0B9A8D7E6F5C4B3C2 select x.vote_code, (select ffv.description from fnd_flex_values_vl ffv, fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=gl.chart_of_accounts_id and fifs.application_column_name='SEGMENT4' and ffv.flex_value_set_id=fifs.flex_value_set_id and ffv.flex_value=x.vote_code) vote_name, x.module, x.kpi_code, x.kpi_name, x.trx_ref, x.trx_date, x.batch_name, x.trx_name, x.description, x.amount, x.currency, x.status, x.party_name, x.org_name, x.period_name from ( -- GL01: Excess Expenditure per Budget Line select gb_a.ledger_id, substr(gcck.segment4,1,3) vote_code, 'GL' module, 'GL01' kpi_code, 'Excess Expenditure per Budget Line' kpi_name, gcck.segment1||'.'||gcck.segment2||'.'||gcck.segment3||'.'||gcck.segment4 trx_ref, gps.start_date trx_date, null batch_name, null trx_name, null description, (gb_a.period_net_dr-gb_a.period_net_cr)-(nvl(gb_b.period_net_dr,0)-nvl(gb_b.period_net_cr,0)) amount, gb_a.currency_code currency, null status, null party_name, null org_name, gb_a.period_name from gl_code_combinations gcck, gl_balances gb_a, gl_balances gb_b, gl_period_statuses gps where gcck.code_combination_id=gb_a.code_combination_id and gcck.summary_flag='N' and gcck.account_type='E' and gb_a.actual_flag='A' and gb_a.translated_flag is null and gb_b.code_combination_id(+)=gb_a.code_combination_id and gb_b.ledger_id(+)=gb_a.ledger_id and gb_b.period_name(+)=gb_a.period_name and gb_b.actual_flag(+)='B' and gb_b.currency_code(+)=gb_a.currency_code and gb_b.translated_flag(+) is null and gps.application_id=101 and gps.ledger_id=gb_a.ledger_id and gps.period_name=gb_a.period_name and gps.start_date<=:as_of_date and (gb_a.period_net_dr-gb_a.period_net_cr)>(nvl(gb_b.period_net_dr,0)-nvl(gb_b.period_net_cr,0)) union all -- GL02: Negative Encumbrance select gb.ledger_id, substr(gcck.segment4,1,3) vote_code, 'GL' module, 'GL02' kpi_code, 'Negative Encumbrance' kpi_name, gcck.segment1||'.'||gcck.segment2||'.'||gcck.segment3||'.'||gcck.segment4 trx_ref, gps.start_date trx_date, null batch_name, null trx_name, null description, gb.period_net_dr-gb.period_net_cr amount, gb.currency_code currency, null status, null party_name, null org_name, gb.period_name from gl_code_combinations gcck, gl_balances gb, gl_period_statuses gps where gcck.code_combination_id=gb.code_combination_id and gcck.summary_flag='N' and gb.actual_flag='E' and gb.translated_flag is null and gps.application_id=101 and gps.ledger_id=gb.ledger_id and gps.period_name=gb.period_name and gps.start_date<=:as_of_date and gb.period_net_dr-gb.period_net_cr<0 union all -- GL03: Negative Actuals (expense accounts with credit net balance) select gb.ledger_id, substr(gcck.segment4,1,3) vote_code, 'GL' module, 'GL03' kpi_code, 'Negative Actuals' kpi_name, gcck.segment1||'.'||gcck.segment2||'.'||gcck.segment3||'.'||gcck.segment4 trx_ref, gps.start_date trx_date, null batch_name, null trx_name, null description, gb.period_net_dr-gb.period_net_cr amount, gb.currency_code currency, null status, null party_name, null org_name, gb.period_name from gl_code_combinations gcck, gl_balances gb, gl_period_statuses gps where gcck.code_combination_id=gb.code_combination_id and gcck.summary_flag='N' and gcck.account_type='E' and gb.actual_flag='A' and gb.translated_flag is null and gps.application_id=101 and gps.ledger_id=gb.ledger_id and gps.period_name=gb.period_name and gps.start_date<=:as_of_date and gb.period_net_dr-gb.period_net_cr<0 union all -- GL04/GL05: Unposted and Error Journals select gjh.ledger_id, substr(gcck.segment4,1,3) vote_code, 'GL' module, case gjh.status when 'U' then 'GL04' when 'E' then 'GL05' end kpi_code, case gjh.status when 'U' then 'Unposted Journals' when 'E' then 'Journals in Errors' end kpi_name, to_char(gjh.je_header_id) trx_ref, gjh.default_effective_date trx_date, gjb.name batch_name, gjh.name trx_name, gjh.description, sum(nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0)) amount, gjh.currency_code currency, gjh.status, null party_name, null org_name, gjh.period_name from gl_je_batches gjb, gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations gcck where gjh.default_effective_date<=:as_of_date and gjh.status in ('U','E') and gjb.je_batch_id=gjh.je_batch_id and gjl.je_header_id=gjh.je_header_id and gcck.code_combination_id=gjl.code_combination_id and gcck.summary_flag='N' group by gjh.ledger_id, gcck.segment4, gjh.je_header_id, gjh.default_effective_date, gjb.name, gjh.name, gjh.description, gjh.currency_code, gjh.status, gjh.period_name union all -- AR01: Unposted AR Receipts (not transferred to GL) select ou_s.ledger_id, ou_s.segment2 vote_code, 'AR' module, 'AR01' kpi_code, 'Unposted Items' kpi_name, acra.receipt_number trx_ref, acra.receipt_date trx_date, null batch_name, null trx_name, acra.comments description, acra.amount, acra.currency_code currency, acra.status, hp.party_name, ou_s.org_name, null period_name from ar_cash_receipts_all acra, ar_cash_receipt_history_all arcrh, hz_cust_accounts hca, hz_parties hp, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where acra.receipt_date<=:as_of_date and arcrh.cash_receipt_id=acra.cash_receipt_id and arcrh.current_record_flag='Y' and arcrh.posting_control_id<0 and acra.org_id=ou_s.organization_id and acra.pay_from_customer=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) union all -- AR02: Unapplied Standard Receipts select ou_s.ledger_id, ou_s.segment2 vote_code, 'AR' module, 'AR02' kpi_code, 'Unapplied Standard Receipts' kpi_name, acra.receipt_number trx_ref, acra.receipt_date trx_date, null batch_name, null trx_name, acra.comments description, acra.amount, acra.currency_code currency, acra.status, hp.party_name, ou_s.org_name, null period_name from ar_cash_receipts_all acra, hz_cust_accounts hca, hz_parties hp, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where acra.receipt_date<=:as_of_date and acra.org_id=ou_s.organization_id and acra.status='UNAPP' and acra.pay_from_customer=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) union all -- AR03: Unreconciled Receipts (reversed but not reconciled) select ou_s.ledger_id, ou_s.segment2 vote_code, 'AR' module, 'AR03' kpi_code, 'Unreconciled Receipts' kpi_name, acra.receipt_number trx_ref, acra.receipt_date trx_date, null batch_name, null trx_name, acra.comments description, acra.amount, acra.currency_code currency, acra.status, hp.party_name, ou_s.org_name, null period_name from ar_cash_receipts_all acra, hz_cust_accounts hca, hz_parties hp, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where acra.receipt_date<=:as_of_date and acra.org_id=ou_s.organization_id and acra.status='REV' and acra.pay_from_customer=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) union all -- CM01: Transactions Available for Reconciliation select (select to_number(hou.set_of_books_id) from ce_bank_acct_uses_all cbau, hr_operating_units hou where cbau.bank_account_id=cba.bank_account_id and cbau.org_id=hou.organization_id and cbau.end_date is null and rownum=1) ledger_id, null vote_code, 'CM' module, 'CM01' kpi_code, 'Transactions Available for Reconciliation' kpi_name, csl.bank_trx_number trx_ref, csl.trx_date trx_date, null batch_name, cba.bank_account_name trx_name, nvl(csl.invoice_text,csl.customer_text) description, csl.amount, csl.currency_code currency, csl.status, null party_name, null org_name, to_char(csh.statement_date,'MON-RR') period_name from ce_statement_lines csl, ce_statement_headers csh, ce_bank_accounts cba where csh.statement_date<=:as_of_date and csl.statement_header_id=csh.statement_header_id and csh.bank_account_id=cba.bank_account_id and csl.status='UNRECONCILED' union all -- CM02: Bank Statement Summary - statements not fully reconciled select (select to_number(hou.set_of_books_id) from ce_bank_acct_uses_all cbau, hr_operating_units hou where cbau.bank_account_id=cba.bank_account_id and cbau.org_id=hou.organization_id and cbau.end_date is null and rownum=1) ledger_id, null vote_code, 'CM' module, 'CM02' kpi_code, 'Bank Statement Summary Report per Account' kpi_name, csh.statement_number trx_ref, csh.statement_date trx_date, null batch_name, cba.bank_account_name trx_name, null description, null amount, csh.currency_code currency, decode(csh.statement_complete_flag,'Y','RECONCILED','INCOMPLETE') status, null party_name, null org_name, to_char(csh.statement_date,'MON-RR') period_name from ce_statement_headers csh, ce_bank_accounts cba where csh.statement_date<=:as_of_date and csh.bank_account_id=cba.bank_account_id and csh.statement_complete_flag<>'Y' union all -- CM03: GL Reconciliation Differences (bank accounts with unreconciled lines) select (select to_number(hou.set_of_books_id) from ce_bank_acct_uses_all cbau, hr_operating_units hou where cbau.bank_account_id=cba.bank_account_id and cbau.org_id=hou.organization_id and cbau.end_date is null and rownum=1) ledger_id, null vote_code, 'CM' module, 'CM03' kpi_code, 'GL Reconciliation Differences' kpi_name, cba.bank_account_name trx_ref, csh.statement_date trx_date, null batch_name, cba.bank_account_name trx_name, null description, csh.control_total_dr-csh.control_total_cr amount, csh.currency_code currency, 'UNRECONCILED' status, null party_name, null org_name, to_char(csh.statement_date,'MON-RR') period_name from ce_statement_headers csh, ce_bank_accounts cba where csh.statement_date<=:as_of_date and csh.bank_account_id=cba.bank_account_id and exists ( select 1 from ce_statement_lines csl where csl.statement_header_id=csh.statement_header_id and csl.status='UNRECONCILED' ) union all -- AP01: Never Validated Invoices select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP01' kpi_code, 'Never Validated Invoices' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, 'Unvalidated' status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and aia.wfapproval_status not in ('WFAPPROVED','MANUALLY APPROVED','NOT REQUIRED') and aia.validation_request_id is null and aia.invoice_type_lookup_code<>'PREPAYMENT' and aia.cancelled_date is null union all -- AP02: Invoices on Hold select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP02' kpi_code, 'Invoices on Hold' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, 'On Hold' status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select aph.invoice_id from ap_holds_all aph where aph.release_lookup_code is null group by aph.invoice_id) aph, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and aia.invoice_id=aph.invoice_id and aia.cancelled_date is null union all -- AP03: Validated Invoice - No Payment Required > 1 Week select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP03' kpi_code, 'Invoice Validated Not Required > 1 Week' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, aia.wfapproval_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date-7 and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and aia.wfapproval_status='NOT REQUIRED' and aia.payment_status_flag='N' and aia.cancelled_date is null and aia.invoice_type_lookup_code<>'PREPAYMENT' union all -- AP04: Validated Invoice - Payment Required > 1 Week select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP04' kpi_code, 'Invoice Validated Required > 1 Week' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, aia.wfapproval_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date-7 and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and aia.wfapproval_status in ('WFAPPROVED','MANUALLY APPROVED') and aia.payment_status_flag='N' and aia.cancelled_date is null and aia.invoice_type_lookup_code<>'PREPAYMENT' and (select min(aips.due_date) from ap_payment_schedules_all aips where aips.invoice_id=aia.invoice_id)<=:as_of_date union all -- AP05: Validated Invoice - Payment Initiated > 1 Month (partially paid) select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP05' kpi_code, 'Invoice Validated Initiated > 1 Month' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, aia.payment_status_flag status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date-30 and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and aia.payment_status_flag='P' and aia.cancelled_date is null union all -- AP06: Invoices Rejected by Approver select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP06' kpi_code, 'Invoice with Status Rejected' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, aia.wfapproval_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and aia.wfapproval_status='REJECTED' and aia.cancelled_date is null union all -- AP07: Invoices with Stopped Payment select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP07' kpi_code, 'Invoice with Status Stopped' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, 'Stopped' status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and aia.cancelled_date is null and exists ( select 1 from ap_checks_all aca, ap_invoice_payments_all aipa where aipa.invoice_id=aia.invoice_id and aca.check_id=aipa.check_id and aca.status_lookup_code in ('STOP INITIATED','VOIDED') ) union all -- AP08: Unaccounted Transactions (not posted to GL) select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP08' kpi_code, 'Unaccounted Transactions' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, nvl(aia.posting_status,'Unposted') status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and (aia.posting_status is null or aia.posting_status<>'Available') and aia.cancelled_date is null union all -- AP09: Unretired Prepayments > 2 Months select ou_s.ledger_id, ou_s.segment2 vote_code, 'AP' module, 'AP09' kpi_code, 'Unretired Prepayments > 2 Months' kpi_name, aia.invoice_num trx_ref, aia.invoice_date trx_date, null batch_name, null trx_name, aia.description, aia.invoice_amount amount, aia.invoice_currency_code currency, aia.payment_status_flag status, aps.vendor_name party_name, ou_s.org_name, null period_name from ap_invoices_all aia, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where aia.invoice_date<=:as_of_date-60 and aia.org_id=ou_s.organization_id and aia.vendor_id=aps.vendor_id(+) and aia.invoice_type_lookup_code='PREPAYMENT' and aia.payment_status_flag<>'Y' and aia.cancelled_date is null union all -- PO01: Uninvoiced Receipts (Received not Invoiced) select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO01' kpi_code, 'Uninvoiced Receipts' kpi_name, to_char(rt.transaction_id) trx_ref, rt.transaction_date trx_date, null batch_name, pha.segment1 trx_name, null description, rt.quantity*nvl(rt.po_unit_price,0) amount, pha.currency_code currency, rt.transaction_type status, aps.vendor_name party_name, ou_s.org_name, null period_name from rcv_transactions rt, po_headers_all pha, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where rt.transaction_date<=:as_of_date and rt.transaction_type='RECEIVE' and rt.po_header_id=pha.po_header_id and pha.org_id=ou_s.organization_id and pha.vendor_id=aps.vendor_id(+) and not exists ( select 1 from ap_invoice_distributions_all aida where aida.rcv_transaction_id=rt.transaction_id ) union all -- PO02: Expected Receipts (PO Lines not yet received, past need-by date) select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO02' kpi_code, 'Expected Receipts' kpi_name, pha.segment1 trx_ref, nvl(plla.need_by_date,plla.promised_date) trx_date, null batch_name, null trx_name, pha.comments description, null amount, pha.currency_code currency, pha.authorization_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from po_line_locations_all plla, po_headers_all pha, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where nvl(plla.need_by_date,plla.promised_date)<=:as_of_date and plla.po_header_id=pha.po_header_id and pha.org_id=ou_s.organization_id and pha.vendor_id=aps.vendor_id(+) and plla.quantity>nvl(plla.quantity_received,0) and plla.cancel_flag='N' and nvl(plla.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED') union all -- PO03: Overdue Encumbrance - Requisitions > 2 Months select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO03' kpi_code, 'Overdue Encumbrance - Requisitions > 2 Months' kpi_name, prha.segment1 trx_ref, prha.creation_date trx_date, null batch_name, null trx_name, prha.description, null amount, null currency, prha.authorization_status status, null party_name, ou_s.org_name, null period_name from po_requisition_headers_all prha, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where prha.creation_date<=:as_of_date-60 and prha.org_id=ou_s.organization_id and prha.type_lookup_code='PURCHASE' and prha.authorization_status='APPROVED' union all -- PO04: Overdue Encumbrance - Purchase Orders > 2 Months select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO04' kpi_code, 'Overdue Encumbrance - Purchase Orders > 2 Months' kpi_name, pha.segment1 trx_ref, pha.creation_date trx_date, null batch_name, null trx_name, pha.comments description, null amount, pha.currency_code currency, pha.authorization_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from po_headers_all pha, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where pha.creation_date<=:as_of_date-60 and pha.org_id=ou_s.organization_id and pha.type_lookup_code='STANDARD' and pha.vendor_id=aps.vendor_id(+) and pha.authorization_status='APPROVED' and pha.authorization_status<>'FINALLY CLOSED' union all -- PO05: Incomplete Requisitions select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO05' kpi_code, 'Incomplete Requisitions' kpi_name, prha.segment1 trx_ref, prha.creation_date trx_date, null batch_name, null trx_name, prha.description, null amount, null currency, prha.authorization_status status, null party_name, ou_s.org_name, null period_name from po_requisition_headers_all prha, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where prha.creation_date<=:as_of_date and prha.org_id=ou_s.organization_id and prha.type_lookup_code='PURCHASE' and prha.authorization_status in ('INCOMPLETE','IN PROCESS') union all -- PO06: Rejected Requisitions select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO06' kpi_code, 'Rejected Requisitions' kpi_name, prha.segment1 trx_ref, prha.creation_date trx_date, null batch_name, null trx_name, prha.description, null amount, null currency, prha.authorization_status status, null party_name, ou_s.org_name, null period_name from po_requisition_headers_all prha, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where prha.creation_date<=:as_of_date and prha.org_id=ou_s.organization_id and prha.type_lookup_code='PURCHASE' and prha.authorization_status='REJECTED' union all -- PO07: Incomplete Purchase Orders select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO07' kpi_code, 'Incomplete Purchase Orders' kpi_name, pha.segment1 trx_ref, pha.creation_date trx_date, null batch_name, null trx_name, pha.comments description, null amount, pha.currency_code currency, pha.authorization_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from po_headers_all pha, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where pha.creation_date<=:as_of_date and pha.org_id=ou_s.organization_id and pha.type_lookup_code='STANDARD' and pha.vendor_id=aps.vendor_id(+) and pha.authorization_status in ('INCOMPLETE','IN PROCESS') union all -- PO08: Rejected Purchase Orders select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO08' kpi_code, 'Rejected Purchase Orders' kpi_name, pha.segment1 trx_ref, pha.creation_date trx_date, null batch_name, null trx_name, pha.comments description, null amount, pha.currency_code currency, pha.authorization_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from po_headers_all pha, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where pha.creation_date<=:as_of_date and pha.org_id=ou_s.organization_id and pha.type_lookup_code='STANDARD' and pha.vendor_id=aps.vendor_id(+) and pha.authorization_status='REJECTED' union all -- PO09: Purchase Orders Requiring Re-approval select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO09' kpi_code, 'POs Requiring Re-approval' kpi_name, pha.segment1 trx_ref, pha.creation_date trx_date, null batch_name, null trx_name, pha.comments description, null amount, pha.currency_code currency, pha.authorization_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from po_headers_all pha, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where pha.creation_date<=:as_of_date and pha.org_id=ou_s.organization_id and pha.type_lookup_code='STANDARD' and pha.vendor_id=aps.vendor_id(+) and pha.authorization_status='REQUIRES REAPPROVAL' union all -- PO10: Open Purchase Orders (Approved, not finally closed) select ou_s.ledger_id, ou_s.segment2 vote_code, 'PO' module, 'PO10' kpi_code, 'Open Purchase Orders' kpi_name, pha.segment1 trx_ref, pha.creation_date trx_date, null batch_name, null trx_name, pha.comments description, null amount, pha.currency_code currency, pha.authorization_status status, aps.vendor_name party_name, ou_s.org_name, null period_name from po_headers_all pha, ap_suppliers aps, (select hou.organization_id, to_number(hou.set_of_books_id) ledger_id, hou.name org_name, (select substr(gcck_m.segment4,1,3) from ap_invoice_distributions_all aida_m, gl_code_combinations gcck_m where aida_m.org_id=hou.organization_id and gcck_m.code_combination_id=aida_m.dist_code_combination_id and gcck_m.summary_flag='N' and rownum=1) segment2 from hr_operating_units hou) ou_s where pha.creation_date<=:as_of_date and pha.org_id=ou_s.organization_id and pha.type_lookup_code='STANDARD' and pha.vendor_id=aps.vendor_id(+) and pha.authorization_status='APPROVED' and pha.authorization_status<>'FINALLY CLOSED' union all -- FA01/FA02/FA03: FA Mass Additions select fbc.set_of_books_id ledger_id, substr(gcck.segment4,1,3) vote_code, 'FA' module, case fma.queue_name when 'NEW' then 'FA01' when 'ON HOLD' then 'FA02' when 'POST' then 'FA03' end kpi_code, case fma.queue_name when 'NEW' then 'Assets in Queue New' when 'ON HOLD' then 'Assets in Queue Hold' when 'POST' then 'Assets in Queue Post' end kpi_name, fma.asset_number trx_ref, fma.creation_date trx_date, null batch_name, null trx_name, fma.description, fma.fixed_assets_cost amount, null currency, fma.queue_name status, null party_name, null org_name, null period_name from fa_book_controls fbc, fa_mass_additions fma, gl_code_combinations gcck where fma.creation_date<=:as_of_date and fma.book_type_code=fbc.book_type_code and gcck.code_combination_id=fma.expense_code_combination_id and gcck.summary_flag='N' and fma.queue_name in ('NEW','ON HOLD','POST') union all -- BU01: Dossiers in status Creating > 2 Weeks select idtd.sob_id ledger_id, substr(idtd.segment4,1,3) vote_code, 'BU' module, 'BU01' kpi_code, 'Dossiers Creating > 2 Weeks' kpi_name, idth.trx_number trx_ref, idth.creation_date trx_date, null batch_name, null trx_name, idth.description, sum(idtd.budget_amount) amount, idtd.currency_code currency, idth.trx_status status, null party_name, null org_name, idtd.period_name from igi_dos_trx_headers idth, igi_dos_trx_dest idtd where idth.creation_date<=:as_of_date-14 and idtd.trx_id=idth.trx_id and idtd.sob_id=idth.sob_id and idth.trx_status='Creating' group by idtd.sob_id, idtd.segment4, idth.trx_id, idth.trx_number, idth.creation_date, idth.description, idtd.currency_code, idth.trx_status, idtd.period_name union all -- BU02: Dossiers in status Rejected select idtd.sob_id ledger_id, substr(idtd.segment4,1,3) vote_code, 'BU' module, 'BU02' kpi_code, 'Dossiers Rejected' kpi_name, idth.trx_number trx_ref, idth.creation_date trx_date, null batch_name, null trx_name, idth.description, sum(idtd.budget_amount) amount, idtd.currency_code currency, idth.trx_status status, null party_name, null org_name, idtd.period_name from igi_dos_trx_headers idth, igi_dos_trx_dest idtd where idth.creation_date<=:as_of_date and idtd.trx_id=idth.trx_id and idtd.sob_id=idth.sob_id and idth.trx_status='Rejected' group by idtd.sob_id, idtd.segment4, idth.trx_id, idth.trx_number, idth.creation_date, idth.description, idtd.currency_code, idth.trx_status, idtd.period_name union all -- BU03: Dossiers in status In Process > 1 Month select idtd.sob_id ledger_id, substr(idtd.segment4,1,3) vote_code, 'BU' module, 'BU03' kpi_code, 'Dossiers In Process > 1 Month' kpi_name, idth.trx_number trx_ref, idth.creation_date trx_date, null batch_name, null trx_name, idth.description, sum(idtd.budget_amount) amount, idtd.currency_code currency, idth.trx_status status, null party_name, null org_name, idtd.period_name from igi_dos_trx_headers idth, igi_dos_trx_dest idtd where idth.creation_date<=:as_of_date-30 and idtd.trx_id=idth.trx_id and idtd.sob_id=idth.sob_id and idth.trx_status='In Process' group by idtd.sob_id, idtd.segment4, idth.trx_id, idth.trx_number, idth.creation_date, idth.description, idtd.currency_code, idth.trx_status, idtd.period_name union all -- BU04: Unwarranted Cash Limits (negative destination balance) select idtd.sob_id ledger_id, substr(idtd.segment4,1,3) vote_code, 'BU' module, 'BU04' kpi_code, 'Unwarranted Cash Limits' kpi_name, idth.trx_number trx_ref, idth.creation_date trx_date, null batch_name, null trx_name, idth.description, sum(idtd.new_balance) amount, idtd.currency_code currency, idth.trx_status status, null party_name, null org_name, idtd.period_name from igi_dos_trx_headers idth, igi_dos_trx_dest idtd where idth.creation_date<=:as_of_date and idtd.trx_id=idth.trx_id and idtd.sob_id=idth.sob_id and idtd.new_balance<0 group by idtd.sob_id, idtd.segment4, idth.trx_id, idth.trx_number, idth.creation_date, idth.description, idtd.currency_code, idth.trx_status, idtd.period_name ) x, gl_ledgers gl where 1=1 and x.ledger_id=gl.ledger_id order by x.vote_code, x.module, x.kpi_code, x.trx_date Full KPI alignment with KPIS TEMPLATE: GL01-GL05, AR01-AR03, CM01-CM03, AP01-AP09, PO01-PO10, BU01-BU04, FA01-FA03 (35 codes); add Module and KPI Code required parameters Y US GL MoFU KPI Dashboard Exceptions Ministry of Finance Uganda - KPI exceptions at transaction level across GL, AR, CM, AP, PO, BU, and FA modules by Vote Code. Use Module and KPI Code parameters to drill into a specific exception type. GL 1=1 :as_of_date 1 10 1=1 gl.name=:ledger 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 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 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) Y US Ledger 2 20 1=1 x.org_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 mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 3 30 :as_of_date Date sysdate Y US As of Date 4 40 1=1 x.module=:module LOV custom Y select 'AP' value, 'Payables' description from dual union all select 'AR', 'Receivables' from dual union all select 'BU', 'Budgeting' from dual union all select 'CM', 'Cash Management' from dual union all select 'FA', 'Fixed Assets' from dual union all select 'GL', 'General Ledger' from dual union all select 'PO', 'Purchasing' from dual order by 1 Y US Module 5 50 1=1 x.kpi_code=:kpi_code LOV custom Y select x.value, x.description from ( select 'AP01' value, 'Never Validated Invoices' description from dual union all select 'AP02' value, 'Invoices on Hold' description from dual union all select 'AP03' value, 'Invoice Validated Not Required > 1 Week' description from dual union all select 'AP04' value, 'Invoice Validated Required > 1 Week' description from dual union all select 'AP05' value, 'Invoice Validated Initiated > 1 Month' description from dual union all select 'AP06' value, 'Invoice with Status Rejected' description from dual union all select 'AP07' value, 'Invoice with Status Stopped' description from dual union all select 'AP08' value, 'Unaccounted Transactions' description from dual union all select 'AP09' value, 'Unretired Prepayments > 2 Months' description from dual union all select 'AR01' value, 'Unposted Items' description from dual union all select 'AR02' value, 'Unapplied Standard Receipts' description from dual union all select 'AR03' value, 'Unreconciled Receipts' description from dual union all select 'BU01' value, 'Dossiers Creating > 2 Weeks' description from dual union all select 'BU02' value, 'Dossiers Rejected' description from dual union all select 'BU03' value, 'Dossiers In Process > 1 Month' description from dual union all select 'BU04' value, 'Unwarranted Cash Limits' description from dual union all select 'CM01' value, 'Transactions Available for Reconciliation' description from dual union all select 'CM02' value, 'Bank Statement Summary Report per Account' description from dual union all select 'CM03' value, 'GL Reconciliation Differences' description from dual union all select 'FA01' value, 'Assets in Queue New' description from dual union all select 'FA02' value, 'Assets in Queue Hold' description from dual union all select 'FA03' value, 'Assets in Queue Post' description from dual union all select 'GL01' value, 'Excess Expenditure per Budget Line' description from dual union all select 'GL02' value, 'Negative Encumbrance' description from dual union all select 'GL03' value, 'Negative Actuals' description from dual union all select 'GL04' value, 'Unposted Journals' description from dual union all select 'GL05' value, 'Journals in Errors' description from dual union all select 'PO01' value, 'Uninvoiced Receipts' description from dual union all select 'PO02' value, 'Expected Receipts' description from dual union all select 'PO03' value, 'Overdue Encumbrance - Requisitions > 2 Months' description from dual union all select 'PO04' value, 'Overdue Encumbrance - Purchase Orders > 2 Months' description from dual union all select 'PO05' value, 'Incomplete Requisitions' description from dual union all select 'PO06' value, 'Rejected Requisitions' description from dual union all select 'PO07' value, 'Incomplete Purchase Orders' description from dual union all select 'PO08' value, 'Rejected Purchase Orders' description from dual union all select 'PO09' value, 'POs Requiring Re-approval' description from dual union all select 'PO10' value, 'Open Purchase Orders' description from dual ) x where :$flex$.Module is null or x.value like :$flex$.Module||'%' order by x.value Y US KPI Code :$flex$.Module Module KPI Code :$flex$.ledger Ledger Operating Unit