Release 12.1
Part Number E13452-04
Contents
Previous
Next
| Oracle Advanced Collections Implementation Guide Release 12.1 Part Number E13452-04 | Contents | Previous | Next |
This appendix provides letter samples and the queries used to produce the preconfigured correspondence templates.
This appendix covers the following topics:
Oracle Advanced Collections provides preconfigured templates for correspondence generated by Oracle XML Publisher. These letters are used in dunning plans, strategies, and to confirm activities during customer interactions.
This appendix provides samples of each preconfigured letter template, the query that produces the output, and the bind variables for the query. Sample letters are shown at the customer data level. Collections provides for a query for each operational data level.
Preconfigured letter templates include:
The text of the Payment Confirmation Letter Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
This is to confirm your payment by Payment Method in the amount of
$0.00 made on sysdate.
If you prefer in the future, you can easily make payment by either
credit card, electronic funds transfer, or through our self service
portal. Please discuss these options and benefits with us if
that would be preferred.
Your business is important to us.
If you have any questions, call us at Collector Phone 1.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, PAYMENT_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select l.meaning from iex_payments p, iex_lookups_v l
where l.lookup_type = 'IEX_PAYMENT_TYPES' and p.payment_method = l.lookup_code
and p.payment_id = :PAYMENT_ID ) payment_method,
(SELECT r.amount from iex_payments p, iex_pay_receipt_xref xpr , ar_cash_receipts_all r
WHERE p.payment_id = :PAYMENT_ID and xpr.payment_id = p.payment_id
and p.payment_id = xpr.payment_id and r.cash_receipt_id = xpr.cash_receipt_id) last_payment_amount,
(select to_char(p.creation_date, 'MM/DD/YYYY') from iex_payments p where p.payment_id = :PAYMENT_ID ) payment_date,
(select rs.source_phone from jtf_rs_resource_extns rs , iex_payments p
where rs.resource_id = p.resource_id and p.payment_id = :PAYMENT_ID ) collector_phone1,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs, iex_payments p
where rs.resource_id = p.resource_id and p.payment_id = :PAYMENT_ID ) collector_name,
(select rs.source_job_title from jtf_rs_resource_extns rs , iex_payments p
where rs.resource_id = p.resource_id and p.payment_id = :PAYMENT_ID ) collector_title,
(select rs.source_phone from jtf_rs_resource_extns rs , iex_payments p
where rs.resource_id = p.resource_id and p.payment_id = :PAYMENT_ID ) collector_phone
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'COLLECTIONS'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Dispute Confirmation Letter Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
This is to confirm your dispute in the amount of $0.00
submittted sysdate, reference number Credit Memo Number, against
invoice number Invoice Number.
Thank you in advance for prompt payment of the balance of the
invoice, $0.00.
If you have any questions, call us at Collector Phone 1.
Sincerely yours,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, DISPUTE_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
to_char(sysdate, 'MM/DD/YYYY') sysdate1,
(select dv.dispute_amount from iex_disputes_v dv, iex_disputes d
where d.rowid = dv.row_id and d.dispute_id = :DISPUTE_ID ) last_dispute_amount,
(select dv.invoice_number from iex_disputes_v dv, iex_disputes d where d.rowid = dv.row_id and d.dispute_id = :DISPUTE_ID ) invoice_number,
(select cm_request_id from iex_disputes d where d.dispute_id = :DISPUTE_ID ) credit_memo_number,
(select amount_due_remaining from
(select aps.amount_due_remaining
from iex_disputes_v dv, iex_disputes d, ra_customer_trx ct, ar_payment_schedules aps
where d.rowid = dv.row_id and ct.trx_number = dv.invoice_number and aps.customer_trx_id = ct.customer_trx_id
and d.dispute_id = :DISPUTE_ID order by aps.creation_date desc)
where rownum = 1) invoice_amount_due,
(select rs.source_phone from jtf_rs_resource_extns rs, iex_disputes d
where rs.user_id = d.created_by and d.dispute_id = :DISPUTE_ID ) collector_phone1,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs, iex_disputes d
where rs.user_id = d.created_by and d.dispute_id = :DISPUTE_ID ) collector_name,
(select rs.source_job_title from jtf_rs_resource_extns rs, iex_disputes d
where rs.user_id = d.created_by and d.dispute_id = :DISPUTE_ID ) collector_title,
(select rs.source_phone from jtf_rs_resource_extns rs, iex_disputes d
where rs.user_id = d.created_by and d.dispute_id = :DISPUTE_ID ) collector_phone
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'COLLECTIONS'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Promise Confirmation Letter Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
This is to confirm your Promise to Pay in the amount of $0.00
logged sysdate.
Invoice Number Promise Amount Promise Due Date
Begin Group 10000 $0.00 sysdateEnd Group
Thank you in advance for prompt payment in the amount of $0.00.
If you have any questions, call us at Collector Phone 1.
Sincerely yours,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PROMISE_DETAIL_ID, LAST_UPDATE_DATE, PARTY_ID, CUST_ACCOUNT_ID, RESOURCE_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
to_char(sysdate, 'MM/DD/YYYY') sysdate1,
(select sum(pv.promise_amount) from iex_promises_v pv
where pv.cust_account_id= :CUST_ACCOUNT_ID and trunc(sysdate) = trunc(pv.creation_date)) ptp_amount,
(select sum(pv.promise_amount) from iex_promises_v pv
where pv.cust_account_id= :CUST_ACCOUNT_ID and trunc(sysdate) = trunc(pv.creation_date)) ptp_amount1,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone1,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select pv.trx_number invoice_number, to_char(pv.promise_date, 'MM/DD/YYYY') ptp_due_date, pv.promise_amount ptp_amount2
from iex_promises_v pv where pv.cust_account_id= :CUST_ACCOUNT_ID and trunc(sysdate) = trunc(pv.creation_date)
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'COLLECTIONS'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Adjustment Confirmation Letter Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
This is to confirm the adjustment in the amount of $0.00
submitted today, sysdate, against invoice number Invoice Number.
Thank you in advance for prompt payment in the outstanding amount
of $0.00.
If you have any questions, call us at Collector Phone 1.
Sincerely yours,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, ADJUSTMENT_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
to_char(sysdate, 'MM/DD/YYYY') sysdate1,
(select adj.total_amount from iex_app_adj_v adj where adj.adjustment_id = :ADJUSTMENT_ID ) total_amount,
(select adj.trx_number from iex_app_adj_v adj where adj.adjustment_id = :ADJUSTMENT_ID )invoice_number,
(select amount_due_original from
(select aps.amount_due_original
from iex_app_adj_v adj, ra_customer_trx ct, ar_payment_schedules aps
where ct.trx_number = adj.trx_number and aps.customer_trx_id = ct.customer_trx_id
and adj.adjustment_id = :ADJUSTMENT_ID order by aps.creation_date desc)
where rownum = 1) invoice_amount,
(select rs.source_phone from jtf_rs_resource_extns rs, iex_app_adj_v adj
where rs.user_id = adj.created_by and adj.adjustment_id = :ADJUSTMENT_ID ) collector_phone1,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs, iex_app_adj_v adj
where rs.user_id = adj.created_by and adj.adjustment_id = :ADJUSTMENT_ID ) collector_name,
(select rs.source_job_title from jtf_rs_resource_extns rs, iex_app_adj_v adj
where rs.user_id = adj.created_by and adj.adjustment_id = :ADJUSTMENT_ID ) collector_title,
(select rs.source_phone from jtf_rs_resource_extns rs, iex_app_adj_v adj
where rs.user_id = adj.created_by and adj.adjustment_id = :ADJUSTMENT_ID ) collector_phone
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'COLLECTIONS'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Payment Reversal Confirmation Letter Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: Confirming Payment Reversal
This is to confirm the payment reversal in the amount of
$0.00 submittted REVERSAL DATE, reference number RECEIPT NUMBER.
If you have any questions, call us at Collector Phone 1.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, RECEIPT_ID, RESOURCE_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select currency_code from ar_cash_receipts where cash_receipt_id = :RECEIPT_ID ) currency_code,
(select amount from ar_cash_receipts where cash_receipt_id = :RECEIPT_ID) amount,
(select to_char(reversal_date, 'MM/DD/YYYY') from ar_cash_receipts where cash_receipt_id = :RECEIPT_ID ) reversal_date,
(select receipt_number from ar_cash_receipts where cash_receipt_id = :RECEIPT_ID ) receipt_number,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone1,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'COLLECTIONS'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Consolidated Invoice Confirmation Letter Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: Invoice copy
Here is a copy of the invoice 1001 you requested. Please let me
know if I can be of further assistance.
The due date is sysdate with amount due remaining $0.00.
Your business is important to us.
If you have any questions, call us at Collector Phone 1.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | PARTY_ID, CONSOLIDATED_INVOICE_ID, RESOURCE_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select consolidated_invoice_number from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) consolidated_invoice_number,
(select to_char(invoice_due_date, 'MM/DD/YYYY') from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) invoice_due_date,
(select currency_code from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) currency_code,
(select amount_due_remaining from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) amount_due_remaining,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone1,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'COLLECTIONS'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Invoice Letter Template is as follows:
Re: INVOICE COPY
rac bill to customer_name rac ship to customer_name
raa bill to address1 raa ship to address1
raa bill to address2 raa ship to address2
raa bill to address3 raa ship to address3
raa bill to address4 raa ship to address4
raa remit to address1
raa remit to address2
raa remit to address3
raa remit to address4
Invoice: trx_number
Billing Date: trx_date
Shipping Date: ship date actual
Purchase Order Number: purchase order
Bill to Customer Number: rac bill to customer num
Terms: term name Due Date: term due date
Item Num Description Quantity Unit Extended Amount
Shipped Price
Begin Group description 1 $0.00 $0.00End Group
line number
Sub Total: $0.00
Tax: $0.00
Total: $0.00
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | INVOICE_ID |
select to_char(a.trx_date, 'MM/DD/YYYY') trx_date, a.trx_number trx_number,
to_char(a.term_due_date, 'MM/DD/YYYY') term_due_date, b.name term_name,
a.purchase_order purchase_order, a.ship_date_actual ship_date_actual, a.rac_bill_to_customer_name rac_bill_to_customer_name,
a.rac_bill_to_customer_num rac_bill_to_customer_num, a.raa_bill_to_address1 raa_bill_to_address1,
a.raa_bill_to_address2 raa_bill_to_address2, a.raa_bill_to_address3_db raa_bill_to_address3,
a.raa_bill_to_city || ', ' || a.raa_bill_to_state || ' ' || a.raa_bill_to_postal_code raa_bill_to_address4,
a.rac_ship_to_customer_name rac_ship_to_customer_name, a.raa_ship_to_address1 raa_ship_to_address1,
a.raa_ship_to_address2 raa_ship_to_address2, a.raa_ship_to_address3_db raa_ship_to_address3,
a.raa_ship_to_city || ', ' || a.raa_ship_to_state || ' ' || a.raa_ship_to_postal_code raa_ship_to_address4,
a.raa_remit_to_address1 raa_remit_to_address1,
a.raa_remit_to_address2 raa_remit_to_address2, a.raa_remit_to_address3_db raa_remit_to_address3,
a.raa_remit_to_city || ', ' || a.raa_remit_to_state || ' ' || a.raa_remit_to_postal_code raa_remit_to_address4,
(select sum(d.extended_amount) from ra_customer_trx_lines_v d
where d.customer_trx_id = :INVOICE_ID and d.line_type = 'TAX' group by d.customer_trx_id) tax,
(select sum(d.extended_amount) from ra_customer_trx_lines_v d
where d.customer_trx_id = :INVOICE_ID and d.line_type <> 'TAX' group by d.customer_trx_id) sub_total,
(select sum(d.extended_amount) from ra_customer_trx_lines_v d
where d.customer_trx_id = :INVOICE_ID group by d.customer_trx_id) sum_extended_amount,
cursor (
select decode(c.line_type, 'LINE', c.line_number, null) line_number,
decode (line_type, 'TAX', initcap(c.line_type) || ' ' || c.tax_code || ' @ ' || c.tax_rate,
'LINE', c.description, 'CB', c.description, initcap(c.line_type)) description,
c.quantity quantity, c.unit_selling_price unit_selling_price, c.extended_amount extended_amount
from ra_customer_trx_lines_v c
where c.customer_trx_id = :INVOICE_ID order by c.customer_trx_line_id
) as payment_history
from ra_customer_trx_partial_v a, ra_terms_vl b
where a.customer_trx_id = :INVOICE_ID and a.term_id = b.term_id(+)
The text of the Pre-delinquent Letter Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: Courtesy Reminder
This is a friendly reminder that you have upcoming payments due.
Invoice Number Amount Due Due Date
Begin Group 10000 $0.00 sysdateEnd Group
To take advantage of our Terms, you must pay $0.00 by the due
date.
If you have already sent your payment, we thank you. If you have
any questions, please don't hesitate to contact us.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | RESOURCE_ID, PARTY_ID |
| Account | RESOURCE_ID, PARTY_ID, ACCOUNT_ID |
| Bill To | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID |
| Delinquency | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
group by dd.party_cust_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'PREDELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'PREDELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'PREDELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and
d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'PREDELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Soft Dunning Letter 1 Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: Overdue Payments
This is a friendly reminder advising you that the following
payments are overdue.
Invoice Number Amount Due Due Date
Begin Group 10000 $0.00 sysdateEnd Group
The total amount due is $0.00.
If you have already sent your payment, we thank you. If you have any questions, please don't hesitate to contact us.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | RESOURCE_ID, PARTY_ID |
| Account | RESOURCE_ID, PARTY_ID, ACCOUNT_ID |
| Bill To | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID |
| Delinquency | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
group by dd.party_cust_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and
d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Soft Dunning Letter 2 Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: Second Notice Overdue Payments
We are writing this letter to call your attention to the below
referenced overdue items with us. We are still awaiting your
payment.
Invoice Number Amount Due Due Date
Begin Group 10000 $0.00 sysdateEnd Group
Please ensure that payment of $0.00 reaches us, at the latest,
by sysdate.
Thanking you for your business and anticipating a prompt response.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | RESOURCE_ID, PARTY_ID |
| Account | RESOURCE_ID, PARTY_ID, ACCOUNT_ID |
| Bill To | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID |
| Delinquency | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
group by dd.party_cust_id) total_amount_due_remaining,
to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining,
to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining,
to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and
d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining,
to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and
d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Moderate Dunning Letter 1 Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: Overdue Notice
We are writing this letter to call your attention to the overdue
invoices listed below. We are awaiting your payment.
Invoice Number Amount Due Due Date
Begin Group 10000 $0.00 sysdateEnd Group
Please ensure that payment of $0.00 reaches us by sysdate.
To expedite payment, please call at the number below.
Thanking you for your business and anticipating a prompt response.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | RESOURCE_ID, PARTY_ID |
| Account | RESOURCE_ID, PARTY_ID, ACCOUNT_ID |
| Bill To | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID |
| Delinquency | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
group by dd.party_cust_id) total_amount_due_remaining,
to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining,
to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by
dd.party_cust_id , dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining,
to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining,
to_char(sysdate+14, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and
d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Moderate Dunning Letter 2 Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: SECOND NOTICE Overdue Payments
We are writing this letter to call your attention to the overdue
invoices listed below. We are still awaiting your payment.
Invoice Number Amount Due Due Date
Begin Group 10000 $0.00 sysdateEnd Group
Please ensure that payment of $0.00 reaches us by sysdate. To
expedite payment, and avoid escalation, please call at the
number below.
Thanking you for your business and anticipating a prompt response.
Regards,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | RESOURCE_ID, PARTY_ID |
| Account | RESOURCE_ID, PARTY_ID, ACCOUNT_ID |
| Bill To | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID |
| Delinquency | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
group by dd.party_cust_id) total_amount_due_remaining,
to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining,
to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining,
to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining,
to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and
d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Hard Dunning Letter 1 Template is as follows:
sysdatea
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
We are writing this letter to call your attention to the overdue
invoices listed below. We are awaiting your payment.
Invoice Number Amount Due Due Date
Begin Group 10000 0.00 sysdateEnd Group
Please ensure that payment of $ $0.00 reaches us by sysdate.
If you wish to discuss any issues please call the undersigned.
Regards,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | RESOURCE_ID, PARTY_ID |
| Account | RESOURCE_ID, PARTY_ID, ACCOUNT_ID |
| Bill To | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID |
| Delinquency | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
group by dd.party_cust_id) total_amount_due_remaining,
to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining,
to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining,
to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining,
to_char(sysdate+7, 'MM/DD/YYYY') required_pay_date,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor,
cursor (select ct.trx_number invoice_number, to_char(ar.due_date, 'MM/DD/YYYY') due_date, ar.amount_due_remaining amount_due_remaining
from iex_delinquencies d, ar_payment_schedules ar, ra_customer_trx ct
where d.party_cust_id = h.party_id
and d.cust_account_id = :ACCOUNT_ID and d.customer_site_use_id = :CUSTOMER_SITE_USE_ID and d.delinquency_id = :DELINQUENCY_ID and
d.payment_schedule_id = ar.payment_schedule_id
and d.status = 'DELINQUENT'
and ar.customer_trx_id = ct.customer_trx_id
) as payment_history
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Hard Dunning Letter 2 Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: SECOND NOTICE Overdue Payment
Recently your attention was called upon regarding the above
referenced account via our calls and the previous letter. The
amount of $0.00 is now considerably past due.
We must receive payment immediately to keep your credit in good
standing with us.
Please courier your payment today or contact us to pay by credit
card or electronic transfer of funds.
We sincerely hope and expect that your payment be on its way so
that there is no disruption to our business partnership.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | RESOURCE_ID, PARTY_ID |
| Account | RESOURCE_ID, PARTY_ID, ACCOUNT_ID |
| Bill To | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID |
| Delinquency | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
group by dd.party_cust_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
The text of the Hard Dunning Letter 3 Template is as follows:
sysdate
First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code
Re: Final NOTICE Overdue Payment
Recently your attention was called upon regarding your account
via our calls and the previous letter. The amount of $0.00 is now
immediately due.
We must receive payment immediately to keep your credit in good
standing with us.
Please courier your payment today or contact us to pay by credit
card or electronic transfer of funds.
We sincerely hope and expect that your payment be on its way so
that there is no disruption to our business partnership. Unless
we receive payment within 48 hours, your account will be
forwarded to our legal department.
Sincerely,
Collector Name
Collector Title
Collector Phone
This query uses the bind variables shown in the following table:
| Query Level | Bind Variables |
|---|---|
| Customer | RESOURCE_ID, PARTY_ID |
| Account | RESOURCE_ID, PARTY_ID, ACCOUNT_ID |
| Bill To | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID |
| Delinquency | RESOURCE_ID, PARTY_ID, ACCOUNT_ID, CUSTOMER_SITE_USE_ID, DELINQUENCY_ID |
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
group by dd.party_cust_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID group by dd.party_cust_id, dd.cust_account_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name,
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select sum(amount_due_remaining) from iex_delinquencies dd, ar_payment_schedules aps
where dd.payment_schedule_id = aps.payment_schedule_id and dd.party_cust_id = h.party_id
and dd.cust_account_id = :ACCOUNT_ID and dd.customer_site_use_id = :CUSTOMER_SITE_USE_ID and dd.delinquency_id = :DELINQUENCY_ID group by
dd.party_cust_id, dd.cust_account_id, dd.customer_site_use_id, dd.delinquency_id) total_amount_due_remaining,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone,
(select source_mgr_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_supervisor
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'DUNNING'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'
Copyright © 2001, 2010, Oracle and/or its affiliates. All rights reserved.