Wishes and Messages

Letter of adjustment of payment

  1. Home
  2. 1st Anniversary Wishes
  3. Letter of adjustment of payment
Letter of adjustment of payment
March 03, 2019 1st Anniversary Wishes 3 comments

How to write a letter to creditors asking for a payment adjustment. Start the year by asking for a break. January 27, - Author: William Hendrian, Michigan.

11/17

Preconfigured Correspondence Templates

This appendix provides letter samples and the queries used to produce the preconfigured correspondence templates.

This appendix covers the following topics:

Preconfigured Templates for Oracle XML Publisher

Oracle Advanced Collections provides preconfigured templates for correspondence generated by Oracle XML Publisher. These letters are used in dunning plans, strategies, and to confirm activities during customer interactions.

This appendix provides samples of each preconfigured letter template, the query that produces the output, and the bind variables for the query. Sample letters are shown at the customer data level. Collections provides for a query for each operational data level.

Preconfigured letter templates include:

Payment Confirmation Letter Template

The text of the Payment Confirmation Letter Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code This is to confirm your payment by Payment Method in the amount of $0.00 made on sysdate. If you prefer in the future, you can easily make payment by either credit card, electronic funds transfer, or through our self service portal. Please discuss these options and benefits with us if that would be preferred. Your business is important to us. If you have any questions, call us at Collector Phone 1. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Payment Confirmation Letter Query

This query uses the bind variables shown in the following table:

Customer PARTY_ID, PAYMENT_ID

Query for Payment Confirmation Letter

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select l.meaning from iex_payments p, iex_lookups_v l where l.lookup_type = 'IEX_PAYMENT_TYPES' and p.payment_method = l.lookup_code and p.payment_id = :PAYMENT_ID ) payment_method, (SELECT r.amount from iex_payments p, iex_pay_receipt_xref xpr , ar_cash_receipts_all r WHERE p.payment_id = :PAYMENT_ID and xpr.payment_id = p.payment_id and p.payment_id = xpr.payment_id and r.cash_receipt_id = xpr.cash_receipt_id) last_payment_amount, (select to_char(p.creation_date, 'MM/DD/YYYY') from iex_payments p where p.payment_id = :PAYMENT_ID ) payment_date, (select rs.source_phone from jtf_rs_resource_extns rs , iex_payments p where rs.resource_id = p.resource_id and p.payment_id = :PAYMENT_ID ) collector_phone1, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs, iex_payments p where rs.resource_id = p.resource_id and p.payment_id = :PAYMENT_ID ) collector_name, (select rs.source_job_title from jtf_rs_resource_extns rs , iex_payments p where rs.resource_id = p.resource_id and p.payment_id = :PAYMENT_ID ) collector_title, (select rs.source_phone from jtf_rs_resource_extns rs , iex_payments p where rs.resource_id = p.resource_id and p.payment_id = :PAYMENT_ID ) collector_phone from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'COLLECTIONS' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Dispute Confirmation Letter Template

The text of the Dispute Confirmation Letter Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code This is to confirm your dispute in the amount of $0.00 submittted sysdate, reference number Credit Memo Number, against invoice number Invoice Number. Thank you in advance for prompt payment of the balance of the invoice, $0.00. If you have any questions, call us at Collector Phone 1. Sincerely yours, Collector Name Collector Title Collector Phone

Bind Variables for Dispute Confirmation Letter Query

This query uses the bind variables shown in the following table:

Customer PARTY_ID, DISPUTE_ID

Query for Dispute Confirmation Letter

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, to_char(sysdate, 'MM/DD/YYYY') sysdate1, (select dv.dispute_amount from iex_disputes_v dv, iex_disputes d where d.rowid = dv.row_id and d.dispute_id = :DISPUTE_ID ) last_dispute_amount, (select dv.invoice_number from iex_disputes_v dv, iex_disputes d where d.rowid = dv.row_id and d.dispute_id = :DISPUTE_ID ) invoice_number, (select cm_request_id from iex_disputes d where d.dispute_id = :DISPUTE_ID ) credit_memo_number, (select amount_due_remaining from (select aps.amount_due_remaining from iex_disputes_v dv, iex_disputes d, ra_customer_trx ct, ar_payment_schedules aps where d.rowid = dv.row_id and ct.trx_number = dv.invoice_number and aps.customer_trx_id = ct.customer_trx_id and d.dispute_id = :DISPUTE_ID order by aps.creation_date desc) where rownum = 1) invoice_amount_due, (select rs.source_phone from jtf_rs_resource_extns rs, iex_disputes d where rs.user_id = d.created_by and d.dispute_id = :DISPUTE_ID ) collector_phone1, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs, iex_disputes d where rs.user_id = d.created_by and d.dispute_id = :DISPUTE_ID ) collector_name, (select rs.source_job_title from jtf_rs_resource_extns rs, iex_disputes d where rs.user_id = d.created_by and d.dispute_id = :DISPUTE_ID ) collector_title, (select rs.source_phone from jtf_rs_resource_extns rs, iex_disputes d where rs.user_id = d.created_by and d.dispute_id = :DISPUTE_ID ) collector_phone from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'COLLECTIONS' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Promise Confirmation Letter Template

The text of the Promise Confirmation Letter Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code This is to confirm your Promise to Pay in the amount of $0.00 logged sysdate. Invoice Number Promise Amount Promise Due Date Begin Group 10000 $0.00 sysdateEnd Group Thank you in advance for prompt payment in the amount of $0.00. If you have any questions, call us at Collector Phone 1. Sincerely yours, Collector Name Collector Title Collector Phone

Bind Variables for Promise Confirmation Letter Query

This query uses the bind variables shown in the following table:

Customer PROMISE_DETAIL_ID, LAST_UPDATE_DATE, PARTY_ID, CUST_ACCOUNT_ID, RESOURCE_ID

Query for Promise Confirmation Letter Template

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, to_char(sysdate, 'MM/DD/YYYY') sysdate1, (select sum(pv.promise_amount) from iex_promises_v pv where pv.cust_account_id= :CUST_ACCOUNT_ID and trunc(sysdate) = trunc(pv.creation_date)) ptp_amount, (select sum(pv.promise_amount) from iex_promises_v pv where pv.cust_account_id= :CUST_ACCOUNT_ID and trunc(sysdate) = trunc(pv.creation_date)) ptp_amount1, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone1, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select pv.trx_number invoice_number, to_char(pv.promise_date, 'MM/DD/YYYY') ptp_due_date, pv.promise_amount ptp_amount2 from iex_promises_v pv where pv.cust_account_id= :CUST_ACCOUNT_ID and trunc(sysdate) = trunc(pv.creation_date) ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'COLLECTIONS' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Adjustment Confirmation Letter Template

The text of the Adjustment Confirmation Letter Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code This is to confirm the adjustment in the amount of $0.00 submitted today, sysdate, against invoice number Invoice Number. Thank you in advance for prompt payment in the outstanding amount of $0.00. If you have any questions, call us at Collector Phone 1. Sincerely yours, Collector Name Collector Title Collector Phone

Bind Variables for Adjustment Confirmation Letter Query

This query uses the bind variables shown in the following table:

Customer PARTY_ID, ADJUSTMENT_ID

Query for Adjustment Confirmation Letter Template

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, to_char(sysdate, 'MM/DD/YYYY') sysdate1, (select adj.total_amount from iex_app_adj_v adj where adj.adjustment_id = :ADJUSTMENT_ID ) total_amount, (select adj.trx_number from iex_app_adj_v adj where adj.adjustment_id = :ADJUSTMENT_ID )invoice_number, (select amount_due_original from (select aps.amount_due_original from iex_app_adj_v adj, ra_customer_trx ct, ar_payment_schedules aps where ct.trx_number = adj.trx_number and aps.customer_trx_id = ct.customer_trx_id and adj.adjustment_id = :ADJUSTMENT_ID order by aps.creation_date desc) where rownum = 1) invoice_amount, (select rs.source_phone from jtf_rs_resource_extns rs, iex_app_adj_v adj where rs.user_id = adj.created_by and adj.adjustment_id = :ADJUSTMENT_ID ) collector_phone1, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs, iex_app_adj_v adj where rs.user_id = adj.created_by and adj.adjustment_id = :ADJUSTMENT_ID ) collector_name, (select rs.source_job_title from jtf_rs_resource_extns rs, iex_app_adj_v adj where rs.user_id = adj.created_by and adj.adjustment_id = :ADJUSTMENT_ID ) collector_title, (select rs.source_phone from jtf_rs_resource_extns rs, iex_app_adj_v adj where rs.user_id = adj.created_by and adj.adjustment_id = :ADJUSTMENT_ID ) collector_phone from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'COLLECTIONS' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Payment Reversal Confirmation Letter Template

The text of the Payment Reversal Confirmation Letter Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: Confirming Payment Reversal This is to confirm the payment reversal in the amount of $0.00 submittted REVERSAL DATE, reference number RECEIPT NUMBER. If you have any questions, call us at Collector Phone 1. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Payment Reversal Confirmation Letter Query

This query uses the bind variables shown in the following table:

Customer PARTY_ID, RECEIPT_ID, RESOURCE_ID

Query for Payment Reversal Confirmation Letter Template

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select currency_code from ar_cash_receipts where cash_receipt_id = :RECEIPT_ID ) currency_code, (select amount from ar_cash_receipts where cash_receipt_id = :RECEIPT_ID) amount, (select to_char(reversal_date, 'MM/DD/YYYY') from ar_cash_receipts where cash_receipt_id = :RECEIPT_ID ) reversal_date, (select receipt_number from ar_cash_receipts where cash_receipt_id = :RECEIPT_ID ) receipt_number, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone1, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'COLLECTIONS' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Consolidated Invoice Confirmation Letter Template

The text of the Consolidated Invoice Confirmation Letter Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: Invoice copy Here is a copy of the invoice 1001 you requested. Please let me know if I can be of further assistance. The due date is sysdate with amount due remaining $0.00. Your business is important to us. If you have any questions, call us at Collector Phone 1. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Consolidated Invoice Confirmation Letter Query

This query uses the bind variables shown in the following table:

Customer PARTY_ID, CONSOLIDATED_INVOICE_ID, RESOURCE_ID

Query for Consolidated Invoice Confirmation Letter Template

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select consolidated_invoice_number from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) consolidated_invoice_number, (select to_char(invoice_due_date, 'MM/DD/YYYY') from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) invoice_due_date, (select currency_code from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) currency_code, (select amount_due_remaining from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) amount_due_remaining, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone1, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'COLLECTIONS' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Invoice Letter Template

The text of the Invoice Letter Template is as follows:

Re: INVOICE COPY rac bill to customer_name rac ship to customer_name raa bill to address1 raa ship to address1 raa bill to address2 raa ship to address2 raa bill to address3 raa ship to address3 raa bill to address4 raa ship to address4 raa remit to address1 raa remit to address2 raa remit to address3 raa remit to address4 Invoice: trx_number Billing Date: trx_date Shipping Date: ship date actual Purchase Order Number: purchase order Bill to Customer Number: rac bill to customer num Terms: term name Due Date: term due date Item Num Description Quantity Unit Extended Amount Shipped Price Begin Group description 1 $0.00 $0.00End Group line number Sub Total: $0.00 Tax: $0.00 Total: $0.00

Bind Variables for Invoice Letter Query

This query uses the bind variables shown in the following table:

Query for Invoice Letter Template

select to_char(a.trx_date, 'MM/DD/YYYY') trx_date, a.trx_number trx_number, to_char(a.term_due_date, 'MM/DD/YYYY') term_due_date, b.name term_name, a.purchase_order purchase_order, a.ship_date_actual ship_date_actual, a.rac_bill_to_customer_name rac_bill_to_customer_name, a.rac_bill_to_customer_num rac_bill_to_customer_num, a.raa_bill_to_address1 raa_bill_to_address1, a.raa_bill_to_address2 raa_bill_to_address2, a.raa_bill_to_address3_db raa_bill_to_address3, a.raa_bill_to_city || ', ' || a.raa_bill_to_state || ' ' || a.raa_bill_to_postal_code raa_bill_to_address4, a.rac_ship_to_customer_name rac_ship_to_customer_name, a.raa_ship_to_address1 raa_ship_to_address1, a.raa_ship_to_address2 raa_ship_to_address2, a.raa_ship_to_address3_db raa_ship_to_address3, a.raa_ship_to_city || ', ' || a.raa_ship_to_state || ' ' || a.raa_ship_to_postal_code raa_ship_to_address4, a.raa_remit_to_address1 raa_remit_to_address1, a.raa_remit_to_address2 raa_remit_to_address2, a.raa_remit_to_address3_db raa_remit_to_address3, a.raa_remit_to_city || ', ' || a.raa_remit_to_state || ' ' || a.raa_remit_to_postal_code raa_remit_to_address4, (select sum(d.extended_amount) from ra_customer_trx_lines_v d where d.customer_trx_id = :INVOICE_ID and d.line_type = 'TAX' group by d.customer_trx_id) tax, (select sum(d.extended_amount) from ra_customer_trx_lines_v d where d.customer_trx_id = :INVOICE_ID and d.line_type <> 'TAX' group by d.customer_trx_id) sub_total, (select sum(d.extended_amount) from ra_customer_trx_lines_v d where d.customer_trx_id = :INVOICE_ID group by d.customer_trx_id) sum_extended_amount, cursor ( select decode(c.line_type, 'LINE', c.line_number, null) line_number, decode (line_type, 'TAX', initcap(c.line_type) || ' ' || c.tax_code || ' @ ' || c.tax_rate, 'LINE', c.description, 'CB', c.description, initcap(c.line_type)) description, c.quantity quantity, c.unit_selling_price unit_selling_price, c.extended_amount extended_amount from ra_customer_trx_lines_v c where c.customer_trx_id = :INVOICE_ID order by c.customer_trx_line_id ) as payment_history from ra_customer_trx_partial_v a, ra_terms_vl b where a.customer_trx_id = :INVOICE_ID and a.term_id = b.term_id(+)

Pre-delinquent Letter Template

The text of the Pre-delinquent Letter Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: Courtesy Reminder This is a friendly reminder that you have upcoming payments due. Invoice Number Amount Due Due Date Begin Group 10000 $0.00 sysdateEnd Group To take advantage of our Terms, you must pay $0.00 by the due date. If you have already sent your payment, we thank you. If you have any questions, please don't hesitate to contact us. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Pre-delinquent Letter Query

This query uses the bind variables shown in the following table:

Customer RESOURCE_ID, PARTY_ID
Account RESOURCE_ID, PARTY_ID, ACCOUNT_ID
Bill To RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID
Delinquency RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID

Query for Pre-delinquent Letter - Customer Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id group by dd.party_cust_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'PREDELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Pre-delinquent Letter - Account Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'PREDELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Pre-delinquent Letter - Bill To Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'PREDELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Pre-delinquent Letter - Delinquency Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'PREDELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Soft Dunning Letter 1 Template

The text of the Soft Dunning Letter 1 Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: Overdue Payments This is a friendly reminder advising you that the following payments are overdue. Invoice Number Amount Due Due Date Begin Group 10000 $0.00 sysdateEnd Group The total amount due is $0.00. If you have already sent your payment, we thank you. If you have any questions, please don't hesitate to contact us. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Soft Dunning Letter 1 Query

This query uses the bind variables shown in the following table:

Customer RESOURCE_ID, PARTY_ID
Account RESOURCE_ID, PARTY_ID, ACCOUNT_ID
Bill To RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID
Delinquency RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID

Query for Soft Dunning Letter 1 - Customer Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id group by dd.party_cust_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Soft Dunning Letter 1 - Account Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Soft Dunning Letter 1 - Bill To Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Soft Dunning Letter 1 - Delinquency Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Soft Dunning Letter 2 Template

The text of the Soft Dunning Letter 2 Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: Second Notice Overdue Payments We are writing this letter to call your attention to the below referenced overdue items with us. We are still awaiting your payment. Invoice Number Amount Due Due Date Begin Group 10000 $0.00 sysdateEnd Group Please ensure that payment of $0.00 reaches us, at the latest, by sysdate. Thanking you for your business and anticipating a prompt response. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Soft Dunning Letter 2 Query

This query uses the bind variables shown in the following table:

Customer RESOURCE_ID, PARTY_ID
Account RESOURCE_ID, PARTY_ID, ACCOUNT_ID
Bill To RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID
Delinquency RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID

Query for Soft Dunning Letter 2 - Customer Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id group by dd.party_cust_id) total_amount_due_remaining, to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Soft Dunning Letter 2 - Account Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining, to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Soft Dunning Letter 2- Bill To Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining, to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Soft Dunning Letter 2 - Delinquency Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining, to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Moderate Dunning Letter 1 Template

The text of the Moderate Dunning Letter 1 Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: Overdue Notice We are writing this letter to call your attention to the overdue invoices listed below. We are awaiting your payment. Invoice Number Amount Due Due Date Begin Group 10000 $0.00 sysdateEnd Group Please ensure that payment of $0.00 reaches us by sysdate. To expedite payment, please call at the number below. Thanking you for your business and anticipating a prompt response. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Moderate Dunning Letter 1 Query

This query uses the bind variables shown in the following table:

Customer RESOURCE_ID, PARTY_ID
Account RESOURCE_ID, PARTY_ID, ACCOUNT_ID
Bill To RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID
Delinquency RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID

Query for Moderate Dunning Letter 1 - Customer Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id group by dd.party_cust_id) total_amount_due_remaining, to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Moderate Dunning Letter 1 - Account Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining, to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Moderate Dunning Letter 1 - Bill To Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by dd.party_cust_id , dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining, to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Moderate Dunning Letter 1 - Delinquency Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining, to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Moderate Dunning Letter 2 Template

The text of the Moderate Dunning Letter 2 Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: SECOND NOTICE Overdue Payments We are writing this letter to call your attention to the overdue invoices listed below. We are still awaiting your payment. Invoice Number Amount Due Due Date Begin Group 10000 $0.00 sysdateEnd Group Please ensure that payment of $0.00 reaches us by sysdate. To expedite payment, and avoid escalation, please call at the number below. Thanking you for your business and anticipating a prompt response. Regards, Collector Name Collector Title Collector Phone

Bind Variables for Moderate Dunning Letter 2 Query

This query uses the bind variables shown in the following table:

Customer RESOURCE_ID, PARTY_ID
Account RESOURCE_ID, PARTY_ID, ACCOUNT_ID
Bill To RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID
Delinquency RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID

Query for Moderate Dunning Letter 2 - Customer Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id group by dd.party_cust_id) total_amount_due_remaining, to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Moderate Dunning Letter 2 - Account Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining, to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Moderate Dunning Letter 2 - Bill To Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining, to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Moderate Dunning Letter 2 - Delinquency Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining, to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Hard Dunning Letter 1 Template

The text of the Hard Dunning Letter 1 Template is as follows:

sysdatea First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code We are writing this letter to call your attention to the overdue invoices listed below. We are awaiting your payment. Invoice Number Amount Due Due Date Begin Group 10000 0.00 sysdateEnd Group Please ensure that payment of $ $0.00 reaches us by sysdate. If you wish to discuss any issues please call the undersigned. Regards, Collector Name Collector Title Collector Phone

Bind Variables for Hard Dunning Letter 1 Query

This query uses the bind variables shown in the following table:

Customer RESOURCE_ID, PARTY_ID
Account RESOURCE_ID, PARTY_ID, ACCOUNT_ID
Bill To RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID
Delinquency RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID

Query for Hard Dunning Letter 1 - Customer Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id group by dd.party_cust_id) total_amount_due_remaining, to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 1 - Account Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining, to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 1 - Bill To Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining, to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 1 - Delinquency Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining, to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor, cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct where d.party_cust_id = h.party_id and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and d.payment_schedule_id = ar.payment_schedule_id and d.status = 'DELINQUENT' and ar.customer_trx_id = ct.customer_trx_id ) as payment_history from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Hard Dunning Letter 2 Template

The text of the Hard Dunning Letter 2 Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: SECOND NOTICE Overdue Payment Recently your attention was called upon regarding the above referenced account via our calls and the previous letter. The amount of $0.00 is now considerably past due. We must receive payment immediately to keep your credit in good standing with us. Please courier your payment today or contact us to pay by credit card or electronic transfer of funds. We sincerely hope and expect that your payment be on its way so that there is no disruption to our business partnership. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Hard Dunning Letter 2 Query

This query uses the bind variables shown in the following table:

Customer RESOURCE_ID, PARTY_ID
Account RESOURCE_ID, PARTY_ID, ACCOUNT_ID
Bill To RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID
Delinquency RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID

Query for Hard Dunning Letter 2 - Customer Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id group by dd.party_cust_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 2 - Account Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 2 - Bill To Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 2 - Delinquency Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Hard Dunning Letter 3 Template

The text of the Hard Dunning Letter 3 Template is as follows:

sysdate First Name Last Name Org Name 1910 Oracle Way Suite 100 City, State Postal Code Re: Final NOTICE Overdue Payment Recently your attention was called upon regarding your account via our calls and the previous letter. The amount of $0.00 is now immediately due. We must receive payment immediately to keep your credit in good standing with us. Please courier your payment today or contact us to pay by credit card or electronic transfer of funds. We sincerely hope and expect that your payment be on its way so that there is no disruption to our business partnership. Unless we receive payment within 48 hours, your account will be forwarded to our legal department. Sincerely, Collector Name Collector Title Collector Phone

Bind Variables for Hard Dunning Letter 3 Query

This query uses the bind variables shown in the following table:

Customer RESOURCE_ID, PARTY_ID
Account RESOURCE_ID, PARTY_ID, ACCOUNT_ID
Bill To RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID
Delinquency RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID

Query for Hard Dunning Letter 3 - Customer Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id group by dd.party_cust_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 3 - Account Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 3 - Bill To Level

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code, sub.person_first_name first_name1, (select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining, (select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name, (select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title, (select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone, (select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub where rel.object_id = :PARTY_ID and a.primary_flag = 'Y' and a.party_id = rel.party_id and a.status = 'A' AND rel.relationship_type = 'DUNNING' AND rel.status = 'A' AND rel.object_id = h.party_id AND rel.object_type = 'ORGANIZATION' AND rel.object_table_name = 'HZ_PARTIES' AND h.status = 'A' AND rel.subject_id = sub.party_id AND rel.subject_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND sub.status = 'A'

Query for Hard Dunning Letter 3 - Delinquency Level

If you have received a letter from HMRC saying you have underpaid tax and are required to pay more, want to challenge the amount that has been requested, or want to Requesting an adjustment to the underpaid amount.

Bill Adjustment Letter

letter of adjustment of payment

Adjustment Letter Sample – How to Write an Adjustment Letter.

 

An adjustment letter is a letter that conveys the response of an official representative of a business or company to a particular buyer’s claim or complaint. In the field of business, it is an essential type of letter as it serves the purpose of gaining the trust back of a customer who has found your provided goods and services to be lacking. It allows you the opportunity of explaining the situation to your customer and time to make up for it. An adjustment letter is important because it gives both the representative and the customer invaluable experience concerning what customer service satisfaction really entails.

 

Writing letters, reports, notes, among other things, are important skills for business and personal life. Effective letters will yield nothing but good results. Most often than not, people assess others by the quality of their writing, hence it’s essential to write well. Here are some simple tips in writing an adjustment letter to get you started:

 

• Start your adjustment letter with the acknowledgement of the customer’s complaint or claim and proceed to write the purpose of the letter. If ever the complaint was unfounded or incorrect, remember to remain tactful. Your immediate refusal of the complaint could worsen the situation.

 

• Always remember to express concern, sympathy and understanding of your customer’s situation. Additionally, it also best to express gratitude for the sent letter.

 

• Remain polite and courteous all throughout your letter. If you decide to grant the customer’s request, then mention the actions that the company will undergo to achieve it. On the other hand, if you deny the request, it is advised to offer some form of reimbursement for the customer’s troubles.

 

Having a good business letter format or letter template as a guide can be very helpful. Download a free adjustment letter sample, then customize that will suit your needs. It is a great way to get you started in the right direction.

WATCH THE VIDEO ON THEME: HOW TO WRITE FORMAL LETTER
how to thank a team for a job well done
Car complaint letter sample
romantic love letters for her examples
Miss letter to the one you love
beat as one
Words to praise a friend
20 year anniversary sayings
Sample letters free download

Sample Letter for Disputing Billing Errors

letter of adjustment of payment

Often, when money and debt are involved, tensions run high and we forget that ultimately we’re just dealing with people like us.

One of the best ways to improve your credit history is to simply explain your situation and ask for a little goodwill by writing a goodwill letter. How does it work?

Let’s say you have a negative mark on a credit report with an account that has not gone to a collection agency. It might be from a credit card company when you got a couple of months behind on your payments.

If the payment is now up-to-date, you should try simply asking the company to remove the late payment from your credit report.

No one is obligated to remove accurate information from your credit report, but companies are eager to satisfy you, their customer. It’s in their best interest to keep you as a customer.

When to Use a Goodwill Letter

There’s no guarantee that a goodwill letter will get the negative item off your credit report; after all, this is not a dispute where you’re challenging the validity of the item. But there are several situations where you’ll have the best chance of receiving a positive response from your creditor.

For example, if you tried to make your payment on time but encountered a technical error (especially if it happened on the creditor’s end), you’re a good candidate for a goodwill letter. Maybe their website was down or the phone line was busy for an unreasonably long time.

You can also try a goodwill letter if your autopay didn’t work for some reason. Even if your bank account had insufficient funds, you could certainly ask and just explain why your funds were low. Perhaps a check took longer to deposit than you expected or you had an emergency expense pop up.

Finally, even if you simply forgot to make the payment, but are otherwise a good customer making timely payments, you could still successfully request a removal through the goodwill letter.

Ultimately, you’re still a paying customer and many creditors appreciate the fact that they depend on people like you to keep their businesses running.

How to Write a Goodwill Letter

When writing a letter requesting a negative mark be removed, keep a few things in mind.

  1. Be courteous. Put yourself in the company’s position and ask how you would like to be treated. Remember that the company has provided you with a service and thank them for that.
  2. Acknowledge your fault in the late payments and explain the circumstances that prevented you from making those payments.
  3. If there is a specific reason that you need a good credit rating, such as attempting to get a loan for a house or a much-needed car, include it.
  4. Again, be friendly. Thank them for their time and offer to answer any questions they have.

Using goodwill letters, thousands of people have been able to have a negative credit mark removed. You have a strong chance of doing the same if your account is no longer past due and if your debt hasn’t been passed on to a collection agency.

Just remember the above tips or use a form letter, or let a credit repair company do the work for you.

Goodwill Letter Sample

{Date}

{Creditor}
Attn: {Credit Card Company}
{Credit Card Company Address}

Re: {Account Number}

Dear Mr./Mrs. CEO/Pres,

I am writing concerning my experience with {company name} that is both a grateful “thank-you” and a pressing request concerning a tradeline in my credit files that I would like to have revised.

I received my {credit card company} on {date you received card} and took pride in the fact that all my payments were routinely made in a timely manner. Unfortunately, in {whenever late payment was made}, I had a tragic accident and was in the hospital over Christmas. I am the sole provider for our family and was unable to go to work for over a month.

I did use what little vacation and sick time I had to cover our living expenses but needless to say, this left us in an unfavorable position financially. Not only do we have three young children but it was at the hardest time of the year since it was Christmas. Concessions had to be made and regretfully, this led to a late payment on my {credit card company} account.

The loss of income, even though it was a short period of time was a brutal “wake-up” call for me concerning financial responsibility. Since that incident, I have learned the essential organizational and financial management skills I desperately needed at that point and my payment history from {date} reflects that. I wish to thank you for your renewed confidence in me and for giving me a second chance at a positive relationship with {credit card company}, one that I am determined to keep untarnished.

My wife and I are about to begin the process of purchasing a new home, and it has come to my attention that the late notation from {credit card company} may prevent me from taking full advantage of the best interest rates available. Since that notation does not reflect my current status with {credit card company}, I am requesting that you please give me a second chance at a positive credit rating by revising those tradelines. I sincerely hope that there is redemption at {credit card company}, and I plead for such consideration.

If any additional documentation would assist in reaching a positive outcome, please feel free to contact me.

I thank you again for the time you have spent reading this correspondence.

Very hopefully yours,

{Your name}

After You Send the Goodwill Letter

Once your goodwill letter is sent in the mail, there’s nothing left to do but wait. You may hear back from your creditor in as little as one to two weeks, which is an optimal situation. However, it may take longer than that, and some may not ever respond.

You can always call customer service to ask about the status of your request after a few weeks of waiting. You can determine how strong your case is by reviewing a few simple points about your account.

First, the creditor considers how long you’ve been a customer. Obviously, if you’ve had a long relationship and have otherwise generally had a positive payment history, your chances are better than being a new customer or making spotty payments.

You’re also more likely to have the delinquency removed if it happened a while ago. That’s because you’ve had time to prove that it was just a one-time occurrence rather than an ongoing financial issue.

Finally, if you’ve already requested a goodwill adjustment from the same creditor, particularly within the last two years, you’re less likely to have a new item removed.

Note: This sample letter template does not have to be used for a credit card company. It can be used for any negative account on your credit report, but it’s probably best for you to dispute the accounts with the credit reporting agency before attempting a goodwill letter.

In this article, we're going to discuss what a “Goodwill Adjustment Letter” is and how it will help you to negotiate the removal of late payments.

Inform a Customer of an Overpayment

letter of adjustment of payment

Aim:
This page is to help you write letters replying to complaints (also called adjustment letters). On this page are the stages of dealing with complaints, some useful expressions, some example letters, and an exercise.

 

 

 

Useful Expressions

Acknowledging receipt of a complaint letter

  • Thank you for your letter of … regarding / concerning / in connection with …
  • I refer to your letter of … about / relating to …

Apology for the error or fault

  • We must apologise for …
  • We sincerely apologise for …
  • Please accept our apologies for …
  • I would like to apologise for the error made by our company in (verb+ing)

Accepting the Complaint

  • We agree that the usual high standards of our products / services were not met in this instance.

A short explanation of the fault

  • Introductory phrase
    • As a result of our investigation, we found that... (Not: After our investigation...)
  • Causes
    • The error was caused by … / was due to …
    • Apparently, the problem was the result of … / resulted from …
    • The cause of / reason for the mistake was …
  • Effects
    • As a result …
    • This led to …
    • Consequently …
  • Solutions
    • We have modified / changed our ...
    • We have implemented a system to...
    • To prevent re-occurrences we have set up a verification procedure.
  • Assurances
    • We assure you that this will not happen again.

 

Investigation to be made

  • We are currently investigating the cause of ...

  • We will investigate the cause of...

Proposal to settle the difficulty

  • As a gesture of our regret, we are prepared to …/ we are willing to …/ we would like to …
  • To show goodwill, we will …

An offer to take goods back, make a replacement, give a discount etc.

  • We have dispatched the new items by express courier. They should arrive by
  • To show our goodwill, we would like to offer you a 5% discount on your next order with us.

Regret at dissatisfaction

  • While we can understand your frustration, ...
  • We understand how disappointing it can be when your expectations are not met.

Rejecting responsibility for the problem leading to the complaint

  • I regret to inform you that …
  • I am afraid that …
  • Unfortunately, I must point out that …

Reasons for the rejection

  • This is because the guarantee period has expired.
  • This is due to the fact that the guarantee period has expired.

If a third party (another person or organisation) is to blame, direct the complainer to that party

  • We therefore suggest that you contact...

A concluding paragraph aiming at retaining the goodwill of the customer

  • We look forward to receiving your further orders, and assure you that they will be filled correctly / promptly.

 

Example Letters

Everlong Batteries
171 Choi Hung Road
Hung Hon, Hong Kong
Tel/Fax 2235 2449


Mr J Wong
Purchasing Officer
Fortune Goods
317 Orchard Road
Singapore

Dear Mr Wong

Order No. 2639/L

Please accept our apologies for the error made by our company in filling your order no. 2639/L dated

You ordered 12,000 size Ultra super-long-life premium batteries, but our dispatch office sent 1,200. This was due to a typing error.

The balance of 10,800 batteries was dispatched by express courier to your store this morning and will arrive by

Since we value your business, we would like to offer you a 10% discount off your next order with us.

We look forward to receiving your further orders and assure you that they will be filled correctly.

Yours sincerely
   David Choi
David Choi
Distributions Manager
 

 
Everlong Batteries
171 Choi Hung Road
Hung Hon, Hong Kong
Tel/Fax (852) 2235 2449


Mr J Wong
Purchasing Officer
Fortune Goods
317 Orchard Road
Singapore

Dear Mr Wong

Order No. 2639/L

Thank you for your letter of regarding your order no. 2639/L. We understand that this is a difficult situation for you.

We have investigated the situation, and found that you ordered 12,00 size Ultra super-long-life premium batteries. Please see the enclosed copy of your order form. Our dispatch office therefore sent 1,200.

If you need the remaining batteries urgently, the balance of 10,800 batteries can be dispatched today by express courier to your store and would arrive by

Please phone me at the number given above if you would like to order these batteries.

We look forward to receiving your further orders.

Yours sincerely
   David Choi
David Choi
Distributions Manager

Encl:
- Order Form No. 2639/L
 

Exercise

Introduction:
This task is for you to practise matching the types of sentence you need in adjustment letters with suitable sentences.

Instructions:
Match the items in the boxes on the left with the items on the right:

  1. Click in the table cell containing the item you want to move.
  2. Click in the table cell where you want the item to go. The words will swap position.
  3. If an item is in the right position, it will have a green background and a tick.
  4. When all the table cells are green and have ticks, you have finished.

Related pages:
Adjustment letters:

 

Last updated on: Monday, March 26, 2012

how to write a salary adjustment letter, salary adjustment letter, tips for writing a Use this letter to express the reasons you consider a salary raise is fair for you.

letter of adjustment of payment
Written by Arashibar
Write a comment