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 
submitted 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:

sysdate

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

Dear Customer,

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 \n\
(SELECT TO_CHAR(CORRESPONDENCE_DATE,'MM/DD/YYYY') FROM IEX_DUNNINGS WHERE DUNNING_ID=:DUNNING_ID) CURRSYSDATE, DECODE((PER.PERSON_FIRST_NAME||PER.PERSON_LAST_NAME),NULL,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('RESPONSIBILITY','APS'),
PER.PERSON_FIRST_NAME) FIRST_NAME,
PER.PERSON_LAST_NAME LAST_NAME,
ORG.PARTY_NAME ORG_NAME,
LOC.ADDRESS1 ADDRESS1,
LOC.ADDRESS2 ADDRESS2,
LOC.CITY CITY,
LOC.STATE STATE,
LOC.POSTAL_CODE POSTAL_CODE,
PER.PERSON_FIRST_NAME FIRST_NAME1,
(
(SELECT NVL(SUM(APS.ACCTD_AMOUNT_DUE_REMAINING),0)
        FROM
                AR_PAYMENT_SCHEDULES_ALL APS,
                IEX_DUNNING_TRANSACTIONS IET,
                HZ_CUST_ACCOUNTS ACC
                WHERE
                        APS.AMOUNT_DUE_REMAINING<>0 AND
                        APS.CUSTOMER_ID=ACC.CUST_ACCOUNT_ID AND
                        ( (ACC.PARTY_ID = :PARTY_ID)
                        OR ((FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE') IS NOT NULL) AND ACC.PARTY_ID IN
                                (SELECT REL.SUBJECT_ID
                                        FROM HZ_RELATIONSHIPS REL,
                                                IEX_DELINQUENCIES_ALL DEL
                                                        WHERE REL.OBJECT_ID = DEL.PARTY_CUST_ID
                                                                AND REL.OBJECT_ID = :PARTY_ID
                                                                AND DEL.ORG_ID = :ORG_ID
                                                                AND DEL.STATUS <> 'CURRENT'
                                                                AND REL.STATUS = 'A'
                                                                AND REL.OBJECT_TYPE = 'ORGANIZATION'
                                                                AND REL.SUBJECT_TYPE = 'ORGANIZATION'
                                                                AND REL.RELATIONSHIP_CODE = FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE')
                                                                AND (DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY') ,'31-12-4712',
                                                                                TO_DATE(NULL),REL.END_DATE) IS NULL
                                                                OR DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY') ,'31-12-4712', 
                                                                                TO_DATE(NULL),REL.END_DATE) > TRUNC(SYSDATE))
) )) AND
/* ACC.PARTY_ID=:PARTY_ID AND */
IET.DUNNING_ID=:DUNNING_ID AND
APS.PAYMENT_SCHEDULE_ID=IET.PAYMENT_SCHEDULE_ID AND IET.STAGE_NUMBER = -1)
) INV_TOT,
NVL(
(SELECT NVL(SUM(APS.ACCTD_AMOUNT_DUE_REMAINING),0)
        FROM
                AR_PAYMENT_SCHEDULES_ALL APS,
                IEX_DUNNING_TRANSACTIONS IET,
                HZ_CUST_ACCOUNTS ACC
                WHERE
                        APS.AMOUNT_DUE_REMAINING<>0 AND
                        APS.CUSTOMER_ID=ACC.CUST_ACCOUNT_ID AND
                        ((ACC.PARTY_ID = :PARTY_ID)
                        OR ((FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE') IS NOT NULL) AND ACC.PARTY_ID IN
                        (SELECT REL.SUBJECT_ID
                                FROM    HZ_RELATIONSHIPS REL,
                                IEX_DELINQUENCIES_ALL DEL
                                WHERE REL.OBJECT_ID = DEL.PARTY_CUST_ID
                                        AND REL.OBJECT_ID = :PARTY_ID
                                        AND DEL.ORG_ID = :ORG_ID
                                        AND DEL.STATUS <> 'CURRENT'
                                        AND REL.STATUS = 'A'
                                        AND REL.OBJECT_TYPE = 'ORGANIZATION'
                                        AND REL.SUBJECT_TYPE = 'ORGANIZATION'
                                        AND REL.RELATIONSHIP_CODE = FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE')
                                        AND (DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY') ,'31-12-4712',
                                                        TO_DATE(NULL),REL.END_DATE) IS NULL
                                        OR DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY') ,'31-12-4712', 
                                                        TO_DATE(NULL),REL.END_DATE) > TRUNC(SYSDATE))
) )) AND
/* ACC.PARTY_ID=:PARTY_ID AND*/
IET.DUNNING_ID=:DUNNING_ID AND
APS.PAYMENT_SCHEDULE_ID=IET.PAYMENT_SCHEDULE_ID AND IET.STAGE_NUMBER = 0 AND APS.CLASS IN ('INV','DM','BR')
),0) CINV_TOT,
NVL(
(SELECT NVL(SUM(APS.ACCTD_AMOUNT_DUE_REMAINING),0)
        FROM
                AR_PAYMENT_SCHEDULES_ALL APS,
                IEX_DUNNING_TRANSACTIONS IET,
                HZ_CUST_ACCOUNTS ACC
                WHERE
                        APS.AMOUNT_DUE_REMAINING<>0 AND
                        APS.CUSTOMER_ID=ACC.CUST_ACCOUNT_ID AND
                        ((ACC.PARTY_ID = :PARTY_ID)
                        OR ((FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE') IS NOT NULL) AND ACC.PARTY_ID IN
                        (SELECT REL.SUBJECT_ID
                                FROM HZ_RELATIONSHIPS REL,
                                IEX_DELINQUENCIES_ALL DEL
                                WHERE REL.OBJECT_ID = DEL.PARTY_CUST_ID
                                        AND REL.OBJECT_ID = :PARTY_ID
                                        AND DEL.ORG_ID = :ORG_ID
                                        AND DEL.STATUS <> 'CURRENT'
                                        AND REL.STATUS = 'A'
                                        AND REL.OBJECT_TYPE = 'ORGANIZATION'
                                        AND REL.SUBJECT_TYPE = 'ORGANIZATION'
                                        AND REL.RELATIONSHIP_CODE = FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE')
                                        AND (DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY') ,'31-12-4712',
                                                TO_DATE(NULL),REL.END_DATE)IS NULL
                                        OR DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY') ,'31-12-4712', 
                                                TO_DATE(NULL),REL.END_DATE) > TRUNC(SYSDATE))
) )) AND
/* ACC.PARTY_ID=:PARTY_ID AND */
IET.DUNNING_ID=:DUNNING_ID AND
APS.PAYMENT_SCHEDULE_ID=IET.PAYMENT_SCHEDULE_ID AND APS.CLASS='CM'
),0) CM_TOT,
NVL(
(SELECT NVL(SUM(APS.ACCTD_AMOUNT_DUE_REMAINING),0)
        FROM
                AR_PAYMENT_SCHEDULES_ALL APS,
                IEX_DUNNING_TRANSACTIONS IET,
                HZ_CUST_ACCOUNTS ACC
                WHERE
                        APS.AMOUNT_DUE_REMAINING<>0 AND
                        APS.CUSTOMER_ID=ACC.CUST_ACCOUNT_ID AND
                        ((ACC.PARTY_ID = :PARTY_ID)
                        OR ((FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE') IS NOT NULL) AND ACC.PARTY_ID IN
                        (SELECT REL.SUBJECT_ID
                                FROM HZ_RELATIONSHIPS REL,
                                IEX_DELINQUENCIES_ALL DEL
                                WHERE REL.OBJECT_ID = DEL.PARTY_CUST_ID
                                        AND REL.OBJECT_ID = :PARTY_ID
                                        AND DEL.ORG_ID = :ORG_ID
                                        AND DEL.STATUS <> 'CURRENT'
                                        AND REL.STATUS = 'A'
                                        AND REL.OBJECT_TYPE = 'ORGANIZATION'
                                        AND REL.SUBJECT_TYPE = 'ORGANIZATION'
                                        AND REL.RELATIONSHIP_CODE = FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE')
                                        AND (DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY') ,'31-12-4712',
                                                TO_DATE(NULL),REL.END_DATE) IS NULL
                                        OR DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY') ,'31-12-4712', 
                                                TO_DATE(NULL),REL.END_DATE) > TRUNC(SYSDATE))
) )) AND
/* ACC.PARTY_ID=:PARTY_ID AND */
IET.DUNNING_ID=:DUNNING_ID AND
APS.PAYMENT_SCHEDULE_ID=IET.PAYMENT_SCHEDULE_ID AND APS.CLASS='PMT'
),0) PMT_TOT,
RS.SOURCE_NAME COLLECTOR_NAME,
RS.SOURCE_JOB_TITLE COLLECTOR_TITLE,
RS.SOURCE_PHONE COLLECTOR_PHONE,
CURSOR
(SELECT
CT.TRX_NUMBER TRX_NO,
PARTY.PARTY_NAME PARTY_NAME,
L.MEANING TRX_TYPE,
AR.AMOUNT_DUE_REMAINING AMT_DUE,
CT.INVOICE_CURRENCY_CODE INV_CUR,
AR.ACCTD_AMOUNT_DUE_REMAINING A_AMT_DUE,
GL.CURRENCY_CODE FUN_CUR,
TO_CHAR(AR.DUE_DATE,'MM/DD/YYYY') DUE_DATE
FROM
        AR_PAYMENT_SCHEDULES_ALL AR,
        RA_CUSTOMER_TRX_ALL CT,
        AR_SYSTEM_PARAMETERS_ALL ARS,
        GL_SETS_OF_BOOKS GL,
        AR_LOOKUPS L,
        IEX_DUNNING_TRANSACTIONS IDT,
        HZ_CUST_ACCOUNTS ACC,
        HZ_PARTIES PARTY
        WHERE AR.CUSTOMER_ID=ACC.CUST_ACCOUNT_ID
                AND ACC.PARTY_ID = PARTY.PARTY_ID
                AND ( (ACC.PARTY_ID = :PARTY_ID)
                OR ((FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE') IS NOT NULL) AND ACC.PARTY_ID IN
                (SELECT REL.SUBJECT_ID
                        FROM HZ_RELATIONSHIPS REL,
                        IEX_DELINQUENCIES_ALL DEL
                        WHERE REL.OBJECT_ID = DEL.PARTY_CUST_ID
                                AND REL.OBJECT_ID = :PARTY_ID
                                AND DEL.ORG_ID = :ORG_ID
                                AND DEL.STATUS <> 'CURRENT'
                                AND REL.STATUS = 'A'
                                AND REL.OBJECT_TYPE = 'ORGANIZATION'
                                AND REL.SUBJECT_TYPE = 'ORGANIZATION'
                                AND REL.RELATIONSHIP_CODE = FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE')
                                AND (DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY'),'31-12-4712',
                                        TO_DATE(NULL),REL.END_DATE) IS NULL
                                OR DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY'),'31-12-4712', 
                                        TO_DATE(NULL),REL.END_DATE) > TRUNC(SYSDATE))
) ))
/* AND ACC.PARTY_ID=:PARTY_ID */
AND IDT.DUNNING_ID=:DUNNING_ID
AND AR.PAYMENT_SCHEDULE_ID=IDT.PAYMENT_SCHEDULE_ID
AND AR.CUSTOMER_TRX_ID=CT.CUSTOMER_TRX_ID
AND AR.AMOUNT_DUE_REMAINING<>0
AND ARS.SET_OF_BOOKS_ID=GL.SET_OF_BOOKS_ID
AND ARS.ORG_ID=AR.ORG_ID
AND L.LOOKUP_TYPE='INV/CM'
AND AR.CLASS=L.LOOKUP_CODE
UNION
SELECT
ACR.RECEIPT_NUMBER TRX_NO,
PARTY.PARTY_NAME PARTY_NAME,
L.MEANING TRX_TYPE,
AR.AMOUNT_DUE_REMAINING AMT_DUE,
ACR.CURRENCY_CODE INV_CUR,
AR.ACCTD_AMOUNT_DUE_REMAINING A_AMT_CUR,
GL.CURRENCY_CODE FUN_CUR,
TO_CHAR(AR.DUE_DATE,'MM/DD/YYYY') DUE_DATE
FROM
        AR_PAYMENT_SCHEDULES_ALL AR,
        AR_CASH_RECEIPTS_ALL ACR,
        AR_SYSTEM_PARAMETERS ARS,
        GL_SETS_OF_BOOKS GL,
        HZ_CUST_ACCOUNTS AC,
        AR_LOOKUPS L,
        IEX_DUNNING_TRANSACTIONS IDT,
        HZ_PARTIES PARTY
        WHERE ((AC.PARTY_ID = :PARTY_ID) OR ((FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE') IS NOT NULL) AND AC.PARTY_ID IN
                (SELECT REL.SUBJECT_ID
                        FROM HZ_RELATIONSHIPS REL,
                        IEX_DELINQUENCIES_ALL DEL
                        WHERE REL.OBJECT_ID = DEL.PARTY_CUST_ID
                                AND REL.OBJECT_ID = :PARTY_ID
                                AND DEL.ORG_ID   = :ORG_ID
                                AND DEL.STATUS <> 'CURRENT'
                                AND REL.STATUS = 'A'
                                AND REL.OBJECT_TYPE = 'ORGANIZATION'
                                AND REL.SUBJECT_TYPE = 'ORGANIZATION'
                                AND REL.RELATIONSHIP_CODE = FND_PROFILE.VALUE('IEX_PARTY_GRP_REL_ROLE')
                                AND (DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY'),'31-12-4712',
                                        TO_DATE(NULL),REL.END_DATE) IS NULL
                                OR DECODE(TO_CHAR(REL.END_DATE,'DD-MM-YYYY'),'31-12-4712', 
                                        TO_DATE(NULL),REL.END_DATE) > TRUNC(SYSDATE))
) ))
/* AC.PARTY_ID=:PARTY_ID */
AND AC.CUST_ACCOUNT_ID=AR.CUSTOMER_ID
AND AC.PARTY_ID = PARTY.PARTY_ID
AND IDT.DUNNING_ID=:DUNNING_ID
AND AR.PAYMENT_SCHEDULE_ID=IDT.PAYMENT_SCHEDULE_ID
AND AR.CUSTOMER_ID=AC.CUST_ACCOUNT_ID
AND AR.CASH_RECEIPT_ID=ACR.CASH_RECEIPT_ID
AND AR.AMOUNT_DUE_REMAINING<>0
AND AR.CLASS='PMT'
AND AR.STATUS='OP'
AND ARS.SET_OF_BOOKS_ID=GL.SET_OF_BOOKS_ID
AND ARS.ORG_ID=AR.ORG_ID
AND L.LOOKUP_TYPE='INV/CM'
AND AR.CLASS=L.LOOKUP_CODE
) AS PAYMENT_HISTORY
FROM
HZ_LOCATIONS LOC
,HZ_PARTIES ORG
,HZ_PARTIES PER
,JTF_RS_RESOURCE_EXTNS RS
WHERE
LOC.LOCATION_ID = :LOCATION_ID
AND ORG.PARTY_ID= :PARTY_ID
AND PER.PARTY_ID = NVL(:CONTACT_ID, ORG.PARTY_ID) AND RS.RESOURCE_ID = :RESOURCE_ID

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'