Skip Headers

Oracle Advanced Collections Implementation Guide
Release 12.1
Part Number E13452-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

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:

Query Level Bind Variables
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:

Query Level Bind Variables
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:

Query Level Bind Variables
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:

Query Level Bind Variables
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:

Query Level Bind Variables
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:

Query Level Bind Variables
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 Level Bind Variables
Customer INVOICE_ID

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:

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

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:

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

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:

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

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:

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

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:

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

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:

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

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:

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

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:

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

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

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'