AP Key Indicators Report- Not Supported: Reserved For Future Use

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Key Indicators Report
Application: Payables
Source: Key Indicators Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXKIRKI_XML
DB package: AP_APXKIRKI_XMLP_PKG
Run AP Key Indicators Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
select decode (ai.source, 'SelfService',
                          decode (ai.last_updated_by, -1, :C_SYSTEM_USER_NAME,
                                                       5, :C_SYSTEM_USER_NAME,
                          fnd_user_ap_pkg.get_user_name(ai.last_updated_by)),
                          'XpenseXpress',
                          decode (ai.created_by, -1, :C_SYSTEM_USER_NAME,
                                                       5, :C_SYSTEM_USER_NAME,
                          fnd_user_ap_pkg.get_user_name(ai.last_updated_by)),
                          'Confirm PaymentBatch', :C_SYSTEM_USER_NAME,
                          decode (ai.created_by, -1, :C_SYSTEM_USER_NAME,
                                                  5, :C_SYSTEM_USER_NAME,
                          fnd_user_ap_pkg.get_user_name(ai.created_by)))  C_USER_NAME,
        ai.invoice_currency_code C_INVOICE_CURRENCY_CODE,
        ai.source C_INVOICE_SOURCE,
        sum(case when trunc(ai.creation_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then 1 else 0 end) C_CURRENT_NUM_OF_INVOICES,
        DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(ai.creation_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then 1 else 0 end)) C_PRIOR_NUM_OF_INVOICES,
        sum(case when trunc(ai.creation_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then ai.invoice_amount else 0 end) C_CURRENT_INVOICE_AMOUNT,
        DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(ai.creation_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then ai.invoice_amount else 0 end)) C_PRIOR_INVOICE_AMOUNT,
        sum(case when trunc(ai.creation_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then nvl(ai.base_amount,ai.invoice_amount)
                                else 0 end) C_CURRENT_FUNCTIONAL_AMOUNT,
        DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(ai.creation_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then nvl(ai.base_amount,ai.invoice_amount)
                                else 0 end)) C_PRIOR_FUNCTIONAL_AMOUNT,
        sum(case when trunc(ai.creation_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then ap_invoices_utility_pkg.get_dist_count(ai.invoice_id)
                                else 0 end) C_CURRENT_DIST_COUNT,
        DECODE (:C_PRIOR_START_DATE, '', 0,
                        sum(case when trunc(ai.creation_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then ap_invoices_utility_pkg.get_dist_count(ai.invoice_id)
                                else 0 end)) C_PRIOR_DIST_COUNT,
        sum(case when trunc(ai.creation_date) <= trunc(:C_END_DATE)
                                then ap_invoices_utility_pkg.get_dist_count(ai.invoice_id)
                                else 0 end) C_TOTAL_DIST_COUNT, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_inv_userformula(:CS_SUM_PRIOR_INV_NUM, :CS_SUM_CURR_INV_NUM) CF_PERCENT_INV_USER, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_func_amt_userformul(:CS_SUM_PRIOR_FUNC_AMT, :CS_SUM_CURR_FUNC_AMT) CF_PERCENT_FUNC_AMT_USEr, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_distformula0006(:CS_PRIOR_DIST, :CS_CURR_DIST) CF_PERCENT_DIST, 
	AP_APXKIRKI_XMLP_PKG.cf_per_change_at_curformula(:CS_CURR_INV_NUM, :CS_PRIOR_INV_NUM) CF_PER_CHANGE_AT_CUR_NUM, 
	AP_APXKIRKI_XMLP_PKG.cf_per_change_at_cur_amtformul(:CS_CURR_INV_AMT, :CS_PRIOR_INV_AMT) CF_PER_CHANGE_AT_CUR_AMT, 
	AP_APXKIRKI_XMLP_PKG.cf_display_sourceformula(ai.source) CF_DISPLAY_SOURCE, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_change_numformula(DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(ai.creation_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then 1 else 0 end)), sum(case when trunc(ai.creation_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then 1 else 0 end)) CF_PERCENT_CHANGE_NUM, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_change_amountformul(DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(ai.creation_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then ai.invoice_amount else 0 end)), sum(case when trunc(ai.creation_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then ai.invoice_amount else 0 end)) CF_PERCENT_CHANGE_AMOUNT
from ap_invoices ai
WHERE trunc(ai.creation_date) between
                                     decode (:C_PRIOR_START_DATE,'',
                                             trunc(:C_START_DATE),
                                             trunc(:C_PRIOR_START_DATE))
                                     and  trunc(:C_END_DATE)
AND   ap_invoices_utility_pkg.Get_Expense_Type(ai.source, ai.invoice_id) = 'I'
&P_WHERE_CREATED_BY
group by decode (ai.source, 'SelfService',
                          decode (ai.last_updated_by, -1, :C_SYSTEM_USER_NAME,
                                                       5, :C_SYSTEM_USER_NAME,
                          fnd_user_ap_pkg.get_user_name(ai.last_updated_by)),
                          'XpenseXpress',
                          decode (ai.created_by, -1, :C_SYSTEM_USER_NAME,
                                                       5, :C_SYSTEM_USER_NAME,
                          fnd_user_ap_pkg.get_user_name(ai.last_updated_by)),
                          'Confirm PaymentBatch', :C_SYSTEM_USER_NAME,
                          decode (ai.created_by, -1, :C_SYSTEM_USER_NAME,
                                                  5, :C_SYSTEM_USER_NAME,
                          fnd_user_ap_pkg.get_user_name(ai.created_by))),
        ai.invoice_currency_code,
        ai.source
UNION
select decode (aerh.last_updated_by, -1, :C_SYSTEM_USER_NAME,
                                      5, :C_SYSTEM_USER_NAME,
       fnd_user_ap_pkg.get_user_name(aerh.last_updated_by)) C_USER_NAME,
       aerh.default_currency_code C_INVOICE_CURRENCY_CODE,
       aerh.source C_INVOICE_SOURCE,
        sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then 1 else 0 end) C_CURRENT_NUM_OF_INVOICES,
        DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then 1 else 0 end)) C_PRIOR_NUM_OF_INVOICES,
        sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then aerh.total else 0 end) C_CURRENT_INVOICE_AMOUNT,
        DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then aerh.total else 0 end)) C_PRIOR_INVOICE_AMOUNT,
        sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then DECODE (:C_BASE_CURRENCY_CODE,
                                     aerh.default_currency_code,
                                     aerh.total,
                                     DECODE(fc.minimum_accountable_unit,NULL,
                                     ROUND(aerh.total*aerh.default_exchange_rate, fc.precision),
                                     ROUND(aerh.total*aerh.default_exchange_rate
                                           / fc.minimum_accountable_unit) * fc.minimum_accountable_unit))
                                else 0 end) C_CURRENT_FUNCTIONAL_AMOUNT,
        DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then DECODE (:C_BASE_CURRENCY_CODE,
                                     aerh.default_currency_code,
                                     aerh.total,
                                     DECODE(fc.minimum_accountable_unit,NULL,
                                     ROUND(aerh.total*aerh.default_exchange_rate, fc.precision),
                                     ROUND(aerh.total*aerh.default_exchange_rate
                                           / fc.minimum_accountable_unit) * fc.minimum_accountable_unit))
                                else 0 end)) C_PRIOR_FUNCTIONAL_AMOUNT,
        sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then ap_invoices_utility_pkg.get_explines_count(aerh.report_header_id)
                                else 0 end) C_CURRENT_DIST_COUNT,
        DECODE (:C_PRIOR_START_DATE, '', 0,
                        sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then ap_invoices_utility_pkg.get_explines_count(aerh.report_header_id)
                                else 0 end)) C_PRIOR_DIST_COUNT,
        sum(case when trunc(aerh.last_update_date) <= trunc(:C_END_DATE)
                                then ap_invoices_utility_pkg.get_explines_count(aerh.report_header_id)
                                else 0 end) C_TOTAL_DIST_COUNT, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_inv_userformula(:CS_SUM_PRIOR_INV_NUM, :CS_SUM_CURR_INV_NUM) CF_PERCENT_INV_USER, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_func_amt_userformul(:CS_SUM_PRIOR_FUNC_AMT, :CS_SUM_CURR_FUNC_AMT) CF_PERCENT_FUNC_AMT_USEr, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_distformula0006(:CS_PRIOR_DIST, :CS_CURR_DIST) CF_PERCENT_DIST, 
	AP_APXKIRKI_XMLP_PKG.cf_per_change_at_curformula(:CS_CURR_INV_NUM, :CS_PRIOR_INV_NUM) CF_PER_CHANGE_AT_CUR_NUM, 
	AP_APXKIRKI_XMLP_PKG.cf_per_change_at_cur_amtformul(:CS_CURR_INV_AMT, :CS_PRIOR_INV_AMT) CF_PER_CHANGE_AT_CUR_AMT, 
	AP_APXKIRKI_XMLP_PKG.cf_display_sourceformula(aerh.source) CF_DISPLAY_SOURCE, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_change_numformula(DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then 1 else 0 end)), sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then 1 else 0 end)) CF_PERCENT_CHANGE_NUM, 
	AP_APXKIRKI_XMLP_PKG.cf_percent_change_amountformul(DECODE (:C_PRIOR_START_DATE, '', 0,
                                sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_PRIOR_START_DATE)
                                and trunc(:C_PRIOR_END_DATE)
                                then aerh.total else 0 end)), sum(case when trunc(aerh.last_update_date)
                                between trunc(:C_START_DATE)
                                and trunc(:C_END_DATE)
                                then aerh.total else 0 end)) CF_PERCENT_CHANGE_AMOUNT
from ap_expense_report_headers aerh,
     fnd_currencies fc
WHERE trunc(aerh.last_update_date) between
                                     decode (:C_PRIOR_START_DATE,'',
                                             trunc(:C_START_DATE),
                                             trunc(:C_PRIOR_START_DATE))
                                     and  trunc(:C_END_DATE)
AND aerh.source IN ('SelfService', 'XpenseXpress')
AND aerh.vouchno <> 0
AND fc.currency_code = :C_BASE_CURRENCY_CODE
&P_WHERE_CREATED_BY_AERH
group by decode (aerh.last_updated_by, -1, :C_SYSTEM_USER_NAME,
                                      5, :C_SYSTEM_USER_NAME,
       fnd_user_ap_pkg.get_user_name(aerh.last_updated_by))