SELECT intb.org_id org_id,
intb.interest_batch_id interest_batch_id,
intb.batch_name batch_name,
ou.name ou_name,
-- start for bug 26500596 by sunagesh
--intb.calculate_interest_to_date charge_as_of_date,
FND_DATE.DATE_TO_CHARDATE(intb.calculate_interest_to_date) charge_as_of_date,
-- end for bug 26500596 by sunagesh
pty.party_id party_id,
pty.party_name party_name,
ac.account_number account_number,
inth.customer_id account_id,
inth.customer_site_use_id bill_to_id,
loc.address1 address1,
loc.address2 address2,
decode(acs.cust_acct_site_id, NULL, NULL, arh_addr_pkg.format_last_address_line(loc.address_style, loc.address3, loc.address4, loc.city, loc.county,
loc.state, loc.province, terr.territory_short_name, loc.postal_code)) address3,
inth.currency_code currency_code,
inth.interest_header_id interest_header_id,
inth.header_type header_type,
trx.trx_number invoice_number,
FND_DATE.DATE_TO_CHARDATE(trx.trx_date) invoice_date,
-inth.interest_header_id interest_line_id
FROM ar_interest_batches intb,
ar_interest_headers inth,
hz_parties pty,
hz_cust_accounts ac,
hz_cust_acct_sites acs,
hz_cust_site_uses_all su,
hz_locations loc,
hz_party_sites ps,
fnd_territories_vl terr,
ra_customer_trx trx,
hr_all_organization_units_tl ou
WHERE intb.interest_batch_id = inth.interest_batch_id
AND pty.party_id = ac.party_id
AND inth.customer_id = ac.cust_account_id
AND inth.customer_site_use_id = su.site_use_id
AND su.cust_acct_site_id = acs.cust_acct_site_id
AND acs.party_site_id = ps.party_site_id
AND ps.location_id = loc.location_id
AND loc.country = terr.territory_code(+)
AND ou.organization_id = intb.org_id
AND ou.LANGUAGE = userenv('LANG')
AND inth.display_flag = 'Y'
ANd intb.batch_status in ( 'F','D')
ANd intb.transferred_status = decode(intb.batch_status ,'F','S',intb.transferred_status)
ANd trx.interest_header_id(+) = inth.interest_header_id
AND inth.interest_header_id = :int_header_id
AND inth.header_type IN ( 'INV','DM')
&P_QUERY_WHERE
UNION ALL
SELECT intb.org_id org_id,
intb.interest_batch_id interest_batch_id,
intb.batch_name batch_name,
ou.name ou_name,
-- start for bug 26500596 by sunagesh
--intb.calculate_interest_to_date charge_as_of_date,
FND_DATE.DATE_TO_CHARDATE(intb.calculate_interest_to_date) charge_as_of_date,
-- end for bug 26500596 by sunagesh
pty.party_id party_id,
pty.party_name party_name,
ac.account_number account_number,
inth.customer_id account_id,
inth.customer_site_use_id bill_to_id,
loc.address1 address1,
loc.address2 address2,
decode(acs.cust_acct_site_id, NULL, NULL, arh_addr_pkg.format_last_address_line(loc.address_style, loc.address3, loc.address4, loc.city, loc.county, loc.state,
loc.province, terr.territory_short_name, loc.postal_code)) address3,
inth.currency_code currency_code,
inth.interest_header_id interest_header_id,
inth.header_type header_type,
adj.adjustment_number invoice_number,
FND_DATE.DATE_TO_CHARDATE(adj.apply_date) invoice_date,
intl.interest_line_id interest_line_id
FROM ar_interest_batches intb,
ar_interest_lines intl,
ar_interest_headers inth,
hz_parties pty,
hz_cust_accounts ac,
hz_cust_acct_sites acs,
hz_cust_site_uses_all su,
hz_locations loc,
hz_party_sites ps,
fnd_territories_vl terr,
ar_adjustments adj,
hr_all_organization_units_tl ou
WHERE intb.interest_batch_id = inth.interest_batch_id
AND pty.party_id = ac.party_id
AND inth.customer_id = ac.cust_account_id
AND inth.customer_site_use_id = su.site_use_id
AND su.cust_acct_site_id = acs.cust_acct_site_id
AND acs.party_site_id = ps.party_site_id
AND ps.location_id = loc.location_id
AND loc.country = terr.territory_code(+)
AND ou.organization_id = intb.org_id
AND ou.LANGUAGE = userenv('LANG')
AND inth.display_flag = 'Y'
ANd intb.batch_status in ('F','D')
AND inth.interest_header_id=intl.interest_header_id
ANd intb.transferred_status = decode(intb.batch_status ,'F','S',intb.transferred_status)
ANd adj.interest_line_id(+) = intl.interest_line_id
AND inth.interest_header_id = :int_header_id
AND inth.header_type IN ( 'ADJ')
&P_QUERY_WHERE |
