The text of the Consolidated Invoice Confirmation Letter Template is as follows:

sysdate

First Name Last Name
Org Name
1910 Oracle Way
Suite 100
City, State Postal Code


Re: Invoice copy

Here is a copy of the invoice 1001 you requested. Please let me 
know if I can be of further assistance.

The due date is sysdate with amount due remaining $0.00.

Your business is important to us. 
If you have any questions, call us at Collector Phone 1.


Sincerely,


Collector Name
Collector Title
Collector Phone

Bind Variables for Consolidated Invoice Confirmation Letter Query

This query uses the bind variables shown in the following table:

Query for Consolidated Invoice Confirmation Letter Template

select to_char(sysdate, 'MM/DD/YYYY') currsysdate, sub.person_first_name first_name, sub.person_last_name last_name, h.party_name org_name, 
a.address1 address1, a.address2 address2, a.city city, a.state state, a.postal_code postal_code,
sub.person_first_name first_name1,
(select consolidated_invoice_number from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) consolidated_invoice_number, 
(select to_char(invoice_due_date, 'MM/DD/YYYY') from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) invoice_due_date,
(select currency_code from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) currency_code, 
(select amount_due_remaining from iex_leasing_invoices_v where consolidated_invoice_id = :CONSOLIDATED_INVOICE_ID ) amount_due_remaining, 
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone1,
(select rs.source_first_name ||' '||rs.source_last_name from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_name,
(select source_job_title from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_title,
(select source_phone from jtf_rs_resource_extns rs where resource_id = :RESOURCE_ID ) collector_phone
from ast_locations_v a, hz_parties h, hz_relationships rel, hz_parties sub
where rel.object_id = :PARTY_ID
and a.primary_flag = 'Y'
and a.party_id = rel.party_id
and a.status = 'A'
AND rel.relationship_type = 'COLLECTIONS'
AND rel.status = 'A'
AND rel.object_id = h.party_id
AND rel.object_type = 'ORGANIZATION'
AND rel.object_table_name = 'HZ_PARTIES'
AND h.status = 'A'
AND rel.subject_id = sub.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_table_name = 'HZ_PARTIES'
AND sub.status = 'A'