select decode (rpt.batch_status, 'DRAFT', '--------'
, rpt.bill_number) bill_number,
substrb(party.party_name,1,50) drawee_name,
rac.account_number drawee_number,
substrb(party.party_name,1,50)||rac.account_number drawee_concat,
rpt.maturity_date,
rab.special_instructions,
rpt.br_amount,
substrb(con_party.person_first_name,1,40)||' '||substrb(con_party.person_last_name,1,50) contact,
typ.name transaction_type,
trx.trx_number,
trx.trx_date,
rpt.amount_assigned,
null bank_name,
rab.batch_id,
trx.exchange_rate,
rpt.br_currency,
rpt.receipt_method_name payment_method,
arl.meaning grouping_rule,
nvl(to_char(rpt.br_max_acctd_amount), 'None') br_max_acctd_amount,
nvl(to_char(rpt.br_min_acctd_amount), 'None') br_min_acctd_amount,
rpt.lead_days,
AR_ARBRATBR_XMLP_PKG.cf_maturity_dateformula(rpt.maturity_date) CF_MATURITY_DATE,
AR_ARBRATBR_XMLP_PKG.cf_trx_dateformula(trx.trx_date) CF_TRX_DATE,
AR_ARBRATBR_XMLP_PKG.cf_func_amt_assignedformula(trx.exchange_rate, rpt.amount_assigned) CF_FUNC_AMT_ASSIGNED
from ar_br_trx_batch_rpt rpt,
hz_cust_accounts rac,
hz_parties party,
ra_batches rab,
hz_cust_account_roles con,
hz_parties con_party,
hz_relationships rel,
ra_customer_trx trx,
ra_cust_trx_types typ,
ar_lookups arl
WHERE rac.cust_account_id = rpt.drawee_id
AND rac.party_id = party.party_id
AND rab.batch_id = rpt.batch_id
AND rpt.drawee_contact_id = con.cust_account_role_id(+)
AND con.party_id = rel.party_id(+)
AND con.role_type(+) = 'CONTACT'
AND rel.subject_id = con_party.party_id(+)
AND rel.subject_table_name(+) = 'HZ_PARTIES'
AND rel.object_table_name(+) = 'HZ_PARTIES'
AND rel.directional_flag(+) = 'F'
AND rpt.transaction_id = trx.customer_trx_id
AND typ.cust_trx_type_id = trx.cust_trx_type_id
AND rpt.request_id = :CP_REQUEST_ID
AND rpt.receipt_creation_rule_code = arl.lookup_code(+)
AND arl.lookup_type(+) = 'AUTO_RECEIPT_CREATION_RULES'
order by drawee_name, payment_method, br_currency, bill_number, trx_number |