AR Late Charges

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Late Charges Report
Short Name: ARLCRPT
DB package: AR_LATE_CHARGES_REPORT_PVT
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
Parameter NameSQL textValidation
Batch Name
 
LOV Oracle