Introduction
This Post illustrates the steps required to extracting the late fee data for customers in Oracle EBS R12.
Script to SQL Query for extracting the late fee data for customers in R12 Oracle Apps
SELECT aps.trx_number, aps.trx_date, cust_det.interest_rate,
ROUND
( cust_det.interest_rate
* ( amount_due_remaining
– (SELECT NVL (SUM (amount), 0)
FROM apps.ar_adjustments_all aaa,
ar_receivables_trx_all art
WHERE 1 = 1
AND aaa.receivables_trx_id =
art.receivables_trx_id
AND NAME = ‘DM Late Fee’
–RECEIVABLES_TRX_ID=44076
AND aaa.status = ‘A’
AND aaa.customer_trx_id = aps.customer_trx_id)
)
/ 100,
2
) late_fee_charge,
aps.gl_date, aps.due_date, aps.status, aps.CLASS,
aps.amount_due_original, aps.amount_due_remaining,
(SELECT NVL (SUM (amount), 0)
FROM apps.ar_adjustments_all aaa,
ar_receivables_trx_all art
WHERE 1 = 1
AND aaa.receivables_trx_id = art.receivables_trx_id
AND NAME = ‘DM Late Fee’
AND aaa.status = ‘A’
AND aaa.customer_trx_id = aps.customer_trx_id)
late_fee_amount,
( amount_due_remaining
– (SELECT NVL (SUM (amount), 0)
FROM apps.ar_adjustments_all aaa,
ar_receivables_trx_all art
WHERE 1 = 1
AND aaa.receivables_trx_id = art.receivables_trx_id
AND NAME = ‘DM Late Fee’
–RECEIVABLES_TRX_ID=44076
AND aaa.status = ‘A’
AND aaa.customer_trx_id = aps.customer_trx_id)
) amount_without_late_fee,
aps.customer_id,
ROUND (TO_DATE (pn_late_fee) – due_date) days_late,
aps.customer_trx_id, aps.customer_site_use_id,
cust_det.charge_begin_date, cust_det.account_number,
cust_det.party_name, cust_det.LOCATION,
cust_det.interest_charges, cust_det.interest_period_days,
cust_det.late_charge_type, cust_det.party_site_number,
cust_det.site_use_id, rct.ship_to_site_use_id,
rct.cust_trx_type_id, rct.batch_source_id,
‘Late Fee for ‘
|| ROUND (TO_DATE (pn_late_fee) – due_date)
|| ‘ Days’
|| ‘ Invoice # ‘
|| rct.trx_number
|| ‘ Invoice Amount ‘
|| aps.amount_due_original
|| ‘ Due Amount ‘
|| aps.amount_due_remaining
|| ‘ Due Date ‘
|| aps.due_date “REMARKS”
FROM apps.ar_payment_schedules_all aps,
(SELECT hcaa.account_number, hcaa.account_name, hp.party_name,
hca.attribute1, hcsua.site_use_code, hcsua.LOCATION,
hcaa.party_id, hcaa.attribute3,
hcp.cust_account_id cust_account_id,
hcp.cust_account_profile_id cust_account_profile_id,
hcpa.currency_code currency_code,
hcpa.cust_acct_profile_amt_id
cust_acct_profile_amt_id,
hcpa.exchange_rate_type exchange_rate_type,
hcp.object_version_number object_version_number,
hcpa.object_version_number amt_obn,
hcp.cust_account_profile_id, interest_charges,
late_charge_calculation_trx, credit_items_flag,
disputed_transactions_flag, payment_grace_days,
interest_period_days, late_charge_type,
interest_calculation_period,
hold_charged_invoices_flag,
multiple_interest_rates_flag, charge_begin_date,
charge_on_finance_charge_flag,
cust_acct_profile_amt_id, currency_code,
exchange_rate_type, min_fc_invoice_overdue_type,
min_fc_balance_overdue_type, min_fc_balance_amount,
min_fc_invoice_amount, min_interest_charge,
max_interest_charge, interest_type, interest_rate,
hps.party_site_number, hl.address1, hl.address2,
hl.address3, hl.city, hl.postal_code, hl.state,
hcpa.site_use_id
FROM apps.hz_cust_accounts_all hcaa,
apps.hz_parties hp,
apps.hz_cust_acct_sites_all hca,
apps.hz_cust_site_uses_all hcsua,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_customer_profiles hcp,
apps.hz_cust_profile_amts hcpa
WHERE hp.party_id = hcaa.party_id
AND hca.cust_account_id = hcaa.cust_account_id
AND hp.party_id = hps.party_id
AND hca.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsua.cust_acct_site_id = hca.cust_acct_site_id
AND hcp.cust_account_profile_id =
hcpa.cust_account_profile_id
AND hcaa.cust_account_id = hcp.cust_account_id
AND hcp.site_use_id = hcsua.site_use_id
AND hcsua.site_use_code = ‘BILL_TO’
AND hcsua.org_id = 82
AND hcp.interest_charges = ‘Y’) cust_det,
apps.ra_customer_trx_all rct
WHERE 1 = 1
AND aps.customer_id = cust_det.cust_account_id
AND aps.customer_site_use_id = cust_det.site_use_id
AND aps.customer_trx_id = rct.customer_trx_id
AND aps.trx_date >= ’01-JAN-2018′
— AND aps.due_date >= ’01-NOV-2017′
AND aps.status = ‘OP’
AND aps.CLASS = ‘INV’
AND rct.batch_source_id = 3009
AND aps.due_date >= cust_det.charge_begin_date
AND ROUND (TO_DATE (pn_late_fee) – due_date) > 0
AND cust_det.charge_begin_date IS NOT NULL;
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
