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;
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;
No comments:
Post a Comment