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

Description

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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

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)),
        aerh.default_currency_code,
        aerh.source
order by 1,2,3

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)), aerh.default_currency_code, aerh.source order by 1,2,3

Parameter Name

SQL text

Validation

Operating Unit

LOV

Period

LOV Oracle

Include Invoice Detail

LOV Oracle

Invoices Entered By

LOV Oracle

Download

Related Blitz Reports

PA Supplier Cost Audit CAC Invoice Price Variance GL Account Analysis (Distributions) AP Expenses AP Turnover AP Invoices and Lines FA Cost Adjustments GL Account Analysis (Distributions) 11g AP Withholding Tax by Tax Authority - draft

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Find out more

Features

Download

FAQ