select
y.vote_code,
y.vote_name,
count(case when y.kpi_code='GL01' then 1 end) gl01_excess_expenditure,
count(case when y.kpi_code='GL02' then 1 end) gl02_negative_encumbrance,
count(case when y.kpi_code='GL03' then 1 end) gl03_negative_actuals,
count(case when y.kpi_code='GL04' then 1 end) gl04_unposted_journals,
count(case when y.kpi_code='GL05' then 1 end) gl05_journals_in_errors,
count(case when y.kpi_code='AR01' then 1 end) ar01_unposted_items,
count(case when y.kpi_code='AR02' then 1 end) ar02_unapplied_receipts,
count(case when y.kpi_code='AR03' then 1 end) ar03_unreconciled_receipts,
count(case when y.kpi_code='CM01' then 1 end) cm01_recon_transactions,
count(case when y.kpi_code='CM02' then 1 end) cm02_bank_statement_summary,
count(case when y.kpi_code='CM03' then 1 end) cm03_gl_recon_differences,
count(case when y.kpi_code='AP01' then 1 end) ap01_never_validated,
count(case when y.kpi_code='AP02' then 1 end) ap02_invoices_on_hold,
count(case when y.kpi_code='AP03' then 1 end) ap03_validated_not_required,
count(case when y.kpi_code='AP04' then 1 end) ap04_validated_required,
count(case when y.kpi_code='AP05' then 1 end) ap05_validated_initiated,
count(case when y.kpi_code='AP06' then 1 end) ap06_rejected,
count(case when y.kpi_code='AP07' then 1 end) ap07_stopped,
count(case when y.kpi_code='AP08' then 1 end) ap08_unaccounted,
count(case when y.kpi_code='AP09' then 1 end) ap09_unretired_prepayments,
count(case when y.kpi_code='PO01' then 1 end) po01_uninvoiced_receipts,
count(case when y.kpi_code='PO02' then 1 end) po02_expected_receipts,
count(case when y.kpi_code='PO03' then 1 end) po03_overdue_reqs,
count(case when y.kpi_code='PO04' then 1 end) po04_overdue_pos,
count(case when y.kpi_code='PO05' then 1 end) po05_incomplete_reqs,
count(case when y.kpi_code='PO06' then 1 end) po06_rejected_reqs,
count(case when y.kpi_code='PO07' then 1 end) po07_incomplete_pos,
count(case when y.kpi_code='PO08' then 1 end) po08_rejected_pos,
count(case when y.kpi_code='PO09' then 1 end) po09_reapproval_pos,
count(case when y.kpi_code='PO10' then 1 end) po10_open_pos,
count(case when y.kpi_code='BU01' then 1 end) bu01_dossiers_creating,
count(case when y.kpi_code='BU02' then 1 end) bu02_dossiers_rejected,
count(case when y.kpi_code='BU03' then 1 end) bu03_dossiers_in_process,
count(case when y.kpi_code='BU04' then 1 end) bu04_unwarranted_cash_limits,
count(case when y.kpi_code='FA01' then 1 end) fa01_new_mass_additions,
count(case when y.kpi_code='FA02' then 1 end) fa02_on_hold_additions,
count(case when y.kpi_code='FA03' then 1 end) fa03_ready_to_post,
count(*) total_exceptions
from
(
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.kpi_code
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
) y
group by
y.vote_code,
y.vote_name
order by y.vote_code |
