This appendix provides letter samples and the queries used to produce the preconfigured correspondence templates.
This appendix covers the following topics:
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:
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, PAYMENT_ID |
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, DISPUTE_ID |
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PROMISE_DETAIL_ID, LAST_UPDATE_DATE, PARTY_ID, CUST_ACCOUNT_ID, RESOURCE_ID |
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, ADJUSTMENT_ID |
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, RECEIPT_ID, RESOURCE_ID |
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, CONSOLIDATED_INVOICE_ID, RESOURCE_ID |
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | INVOICE_ID |
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(+)
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| 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 |
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'
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'
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'
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| 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 |
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'
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'
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'
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| 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 |
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'
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'
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'
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| 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 |
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'
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'
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'
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| 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 |
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'
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'
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'
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| 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 |
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'
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'
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'
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| 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 |
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'
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'
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'
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'
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
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| 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 |
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'
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'
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'
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'