Hi Gopal,
Thank you for your response. Here is the sql.
SELECT
o586765.mcc_country AS e1081917,
o586772.mcc_product_line_desc AS e1081924,
o583784.accounting_date AS e1127909,
o583784.bank_location AS e1127923,
o583784.cancelled_date AS e1127935,
o583784.description AS e1127968,
o583784.distribution_description AS e1127972,
o583784.gl_period AS e1127987,
o583784.invoice_line_amount AS invoice_line_amount,
o583784.invoice_number AS invoice_number,
o583784.manually_inserted_due_date AS e1128032,
o583784.payment_date AS payment_date,
o583784.po_number AS po_number,
o583784.segment2 AS fund,
o586765.mcc_country AS country,
o583784.segment5 AS project,
o583784.segment9 AS activity,
o583784.status AS e1128197,
o583784.supplier_site AS e1128207,
o583784.payment_status_flag AS e1833969,
o583784.prepay_amount_remaining AS e1879948,
as1139650_1127987_old AS as1139650_1127987_old
FROM
(
SELECT DISTINCT
v.invoice_id,
v.po_distribution_id,
v.prepay_distribution_id,
v.invoice_distribution_id,
v.gl_period,
v.accounting_date,
v.payment_posted_flag posted_flag,
v.terms_code,
v.invoice_date,
v.invoice_number,
v.source,
v.voucher_num,
v.description,
v.date_received_by_nbc,
v.manually_inserted_due_date,
v.bank_location,
v.grant_po_number,
v.payment_date,
v.check_number,
v.treasury_pay_date,
v.treasury_pay_number,
v.pay_method,
v.pay_group,
v.status,
v.po_number,
v.po_line_type,
v.item_description,
v.buyer_name,
v.invoice_type,
v.invoice_line_number,
v.r12_invoice_line_number,
v.r12_distribution_line_number,
v.vendor_name,
v.vendor_site_code supplier_site,
v.distribution_description,
v.line_type,
v.ussgl_transaction_code,
v.invoice_line_amount,
v.r12_invoice_line_amount,
v.r12_distribution_line_amount,
v.invoice_amount,
v.payment_amount,
v.due_date,
v.cancelled_date,
v.segment1,
v.segment2,
v.segment3,
v.segment4,
v.segment5,
v.segment6,
v.segment7,
v.segment8,
v.segment9,
v.segment10,
v.distribution_account,
v.org_id,
v.org_name,
v.creation_date,
v.last_update_date,
v.created_by,
v.last_updated_by,
v.payment_status_flag,
ap.prepay_amount_remaining
FROM
cus01.xxibc_ap_bot_dist_v v,
xxibc_ap_invoice_dist_v ap
WHERE
v.invoice_distribution_id = ap.invoice_distribution_id
) o583784,
(
SELECT DISTINCT
ffvv.flex_value mcc_country,
ffvv.description mcc_country_desc,
ffvv.enabled_flag enabled_flag,
ffvv.end_date_active end_date
FROM
apps.fnd_flex_values_vl ffvv,
applsys.fnd_flex_value_sets ffvs
WHERE
ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvs.flex_value_set_name = 'MCC_PROD_PROGRAM' --VNickols added to accommodate new MCC LOA
AND ffvv.summary_flag = 'N'
ORDER BY
ffvv.flex_value
) o586765,
(
SELECT DISTINCT
ffvv.flex_value mcc_product_line,
ffvv.description mcc_product_line_desc,
ffvv.enabled_flag enabled_flag,
ffvv.end_date_active end_date
FROM
apps.fnd_flex_values_vl ffvv,
applsys.fnd_flex_value_sets ffvs
WHERE
ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvs.flex_value_set_name = 'MCC_PROD_ACTIVITY' --VNickols added to accommodate new MCC LOA
AND ffvv.summary_flag = 'N'
ORDER BY
ffvv.flex_value
) o586772,
(
SELECT
o1139611.period_name AS as1139650_1127987_old_2,
MAX(o1139611.effective_period_num) AS as1139650_1127987_old
FROM
gl.gl_period_statuses o1139611
WHERE
(
o1139611.application_id = 101
AND o1139611.set_of_books_id = 2
AND o1139611.closing_status IN (
'C',
'O'
)
)
GROUP BY
o1139611.period_name
)
WHERE
(
( o586765.mcc_country = o583784.segment3 )
AND ( o586772.mcc_product_line = o583784.segment9 )
AND ( o583784.gl_period = as1139650_1127987_old_2 (+) )
)
AND ( o586765.mcc_country = 'NER' )
AND ( o583784.accounting_date < '01-MAR-2020' )
AND (
(
o583784.status IS NULL
AND o583784.payment_status_flag = 'Y'
AND o583784.payment_date IS NULL
OR o583784.status <> 'VOIDED'
AND o583784.payment_status_flag = 'Y'
AND o583784.payment_date < '01-MAR-2020'
OR o583784.status = 'VOIDED'
AND o583784.cancelled_date > '01-MAR-2020'
AND o583784.payment_status_flag = 'N'
AND o583784.payment_date < '01-MAR-2020'
)
)
AND ( o583784.segment2 LIKE '%COM%' )
AND o583784.prepay_amount_remaining > 0
ORDER BY
o583784.bank_location ASC,
o583784.payment_date ASC
------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------
