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 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
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:
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
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 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
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 \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
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'