AR Transactions and Lines

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github
AR transaction report.
Can be run at Header, Line and/or Distribution Level
Optionally include special columns for service contracts (OKS) and lease contracts (OKL) data
Run AR Transactions and Lines and other Oracle EBS reports with Blitz Report™ on our demo environment
select
gl.name ledger,
haouv.name operating_unit,
acia.cons_billing_number invoice_number,
rcta.trx_number,
rcta.trx_date,
to_number(to_char(rcta.trx_date,'MM')) trx_month,
xxen_util.meaning(apsa.class,'INV/CM/ADJ',222) class,
rctta.name type,
rbsa.name batch_source,
rba.name batch_name,
rcta.ct_reference reference,
rcta.purchase_order,
(
select
nvl2(acia0.cons_billing_number,acia0.cons_billing_number||' - ',null)||rcta0.trx_number credited_invoice
from
ra_customer_trx_all rcta0,
ar_cons_inv_trx_all acita0,
ar_cons_inv_all acia0
where
rcta.previous_customer_trx_id=rcta0.customer_trx_id and
rcta0.customer_trx_id=acita0.customer_trx_id(+) and
acita0.cons_inv_id=acia0.cons_inv_id(+)
) credited_invoice,
(select rctla0.line_number from ra_customer_trx_lines_all rctla0 where rctla.previous_customer_trx_line_id=rctla0.customer_trx_line_id) credited_invoice_line,
hp.party_name,
hca.account_number,
hcsua.location bill_to_location,
hz_format_pub.format_address(hps.location_id,null,null,' , ') bill_to_address,
hz_format_pub.format_address(nvl(hps2.location_id,hps3.location_id),null,null,' , ') ship_to_address_invoice,
hz_format_pub.format_address(hps4.location_id,null,null,' , ') ship_to_address_order,
hp.jgzz_fiscal_code taxpayer_id,
rcta.invoice_currency_code currency,
apsa.number_of_due_dates,
apsa.terms_sequence_number,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_due_original end total_due_original,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.tax_original end tax_amount,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_applied end total_payment_applied,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_adjusted end total_adjustment,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_credited end total_credit,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_due_remaining end total_due_remaining,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then case when rctta.accounting_affect_flag='Y' and apsa.amount_in_dispute<>0 then apsa.amount_in_dispute end end dispute_amount,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_due_original*nvl(apsa.exchange_rate,1) end accounted_total_due_original,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.tax_original*nvl(apsa.exchange_rate,1) end accounted_tax_amount,
case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.acctd_amount_due_remaining end accounted_total_due_remaining,
xxen_util.meaning(apsa.status,'PAYMENT_SCHEDULE_STATUS',222) status,
apsa.due_date,
case when apsa.class in ('INV','DM') and apsa.status='OP' then greatest(trunc(sysdate)-apsa.due_date,0) end overdue_days,
decode(rcta.invoicing_rule_id,-3,'Arrears','Advance') invoicing_rule,
rtt.name payment_term,
rcta.ship_date_actual ship_date,
arm.name receipt_method,
decode(apsa.status,'OP',to_date(null),apsa.actual_date_closed) actual_date_closed,
apsa.gl_date payment_sched_gl_date,
decode(apsa.status,'OP',to_date(null),apsa.gl_date_closed) payment_sched_gl_date_closed,
ifpct.payment_channel_name payment_method,
decode(ipiua.instrument_type,'BANKACCOUNT',ieba.masked_bank_account_num,'CREDITCARD',ic.masked_cc_number) instrument_number,
xxen_util.meaning(rcta.printing_option,'INVOICE_PRINT_OPTIONS',222) print_option,
rcta.printing_original_date first_printed_date,
rcta.customer_reference,
rcta.comments,
jrrev.resource_name salesperson,
rtk.concatenated_segments sales_region,
rtk.name sales_region_name,
xxen_util.concatenated_segments(rctlgda0.code_combination_id) receivables_account,
xxen_util.segments_description(rctlgda0.code_combination_id) receivables_account_desc,
rctlgda0.gl_date receivables_account_gl_date,
&line_columns
&distribution_columns
&segment_columns
&contracts_columns
xxen_util.user_name(rcta.created_by) trx_created_by,
xxen_util.client_time(rcta.creation_date) trx_creation_date,
xxen_util.user_name(rcta.last_updated_by) trx_last_updated_by,
xxen_util.client_time(rcta.last_update_date) trx_last_update_date,
&trx_audit_columns
rcta.customer_trx_id,
apsa.payment_schedule_id
from
gl_ledgers gl,
hr_all_organization_units_vl haouv,
ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
(
select
decode(rctla.customer_trx_line_id,min(rctla.customer_trx_line_id) keep (dense_rank first order by decode(rctla.line_type,'LINE',1,2), rctla.line_number) over (partition by rctla.customer_trx_id),null,'Y') not_first_line,
case when rctla.interface_line_context in ('OKL_CONTRACTS','OKL_INVESTOR') then rctla.interface_line_attribute1 end okl_contract_number,
case when rctla.interface_line_context in ('OKS_CONTRACTS') then to_date(rctla.interface_line_attribute4,'YYYY/MM/DD HH24:MI:SS') end oks_billed_from,
case when rctla.interface_line_context in ('OKS_CONTRACTS') then to_date(rctla.interface_line_attribute5,'YYYY/MM/DD HH24:MI:SS') end oks_billed_to,
rctla.*
from
ra_customer_trx_lines_all rctla
where
'&enable_rctla'='Y' and
rctla.line_type in ('LINE','FREIGHT','CB')
) rctla,
(
select distinct
rctla2.link_to_cust_trx_line_id,
sum(rctla2.extended_amount) over (partition by rctla2.link_to_cust_trx_line_id) extended_amount,
listagg(rctla2.tax_rate,', ') within group (order by rctla2.tax_rate) over (partition by rctla2.link_to_cust_trx_line_id) tax_rates
from
ra_customer_trx_lines_all rctla2
where
'&enable_rctla'='Y' and
rctla2.line_type='TAX'
) rctla2,
ra_cust_trx_line_gl_dist_all rctlgda0,
(
select
decode(rctlgda.cust_trx_line_gl_dist_id,min(rctlgda.cust_trx_line_gl_dist_id) keep (dense_rank first order by decode(rctlgda.account_class,'TAX',2,1)) over (partition by rctla3.link_to_cust_trx_line_id),null,'Y') not_first_line,
rctla3.link_to_cust_trx_line_id,
rctla3.tax_rate,
rctlgda.*
from
(
select rctla.link_to_cust_trx_line_id, rctla.customer_trx_line_id, rctla.tax_rate from ra_customer_trx_lines_all rctla where rctla.link_to_cust_trx_line_id is not null union all
select rctla.customer_trx_line_id, rctla.customer_trx_line_id, rctla.tax_rate from ra_customer_trx_lines_all rctla where rctla.link_to_cust_trx_line_id is null
) rctla3,
ra_cust_trx_line_gl_dist_all rctlgda
where
2=2 and
&gl_distribution_where_clause2
'&enable_rctlgda'='Y' and
rctla3.customer_trx_line_id=rctlgda.customer_trx_line_id and
rctlgda.account_set_flag='N'
) rctlgda,
gl_code_combinations_kfv gcck,
ra_batch_sources_all rbsa,
ra_batches_all rba,
ra_cust_trx_types_all rctta,
ra_terms_tl rtt,
ar_cons_inv_all acia,
oe_sys_parameters_all ospa,
hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses_all hcsua,
ra_territories_kfv rtk,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
-- inv line, header and order ship to
hz_cust_site_uses_all hcsua2,
hz_cust_acct_sites_all hcasa2,
hz_party_sites hps2,
hz_cust_site_uses_all hcsua3,
hz_cust_acct_sites_all hcasa3,
hz_party_sites hps3,
hz_cust_site_uses_all hcsua4,
hz_cust_acct_sites_all hcasa4,
hz_party_sites hps4,
--
jtf_rs_salesreps jrs,
jtf_rs_resource_extns_vl jrrev,
oe_order_lines_all oola,
oe_order_headers_all ooha,
jtf_rs_salesreps jrs2,
jtf_rs_resource_extns_vl jrrev2,
ar_receipt_methods arm,
iby_fndcpt_pmt_chnnls_tl ifpct,
iby_fndcpt_tx_extensions ifte,
iby_pmt_instr_uses_all ipiua,
iby_creditcard ic,
iby_ext_bank_accounts ieba
&contracts_query
&contracts_tables
where
1=1 and
&gl_distribution_where_clause
gl.ledger_id=rcta.set_of_books_id and
haouv.organization_id=rcta.org_id and
rcta.customer_trx_id=apsa.customer_trx_id and
decode(apsa.terms_sequence_number,1,apsa.customer_trx_id)=rctla.customer_trx_id(+) and
rctla.customer_trx_line_id=rctla2.link_to_cust_trx_line_id(+) and
rcta.customer_trx_id=rctlgda0.customer_trx_id and
rctlgda0.account_class='REC' and
rctlgda0.latest_rec_flag='Y' and
rctla.customer_trx_line_id=rctlgda.link_to_cust_trx_line_id(+) and
rctlgda.code_combination_id=gcck.code_combination_id(+) and
rcta.batch_source_id=rbsa.batch_source_id(+) and
rcta.org_id=rbsa.org_id(+) and
rcta.batch_id=rba.batch_id(+) and
rcta.org_id=rba.org_id(+) and
rcta.cust_trx_type_id=rctta.cust_trx_type_id and
rcta.org_id=rctta.org_id and
apsa.term_id=rtt.term_id(+) and
rtt.language(+)=userenv('lang') and
apsa.cons_inv_id=acia.cons_inv_id(+) and
rcta.org_id=ospa.org_id(+) and
ospa.parameter_code(+)='MASTER_ORGANIZATION_ID' and
apsa.customer_id=hca.cust_account_id and
hca.party_id=hp.party_id and
apsa.customer_site_use_id=hcsua.site_use_id and
hcsua.territory_id=rtk.territory_id(+) and
hcsua.cust_acct_site_id=hcasa.cust_acct_site_id and
hcasa.party_site_id=hps.party_site_id and
rcta.primary_salesrep_id=jrs.salesrep_id(+) and
rcta.org_id=jrs.org_id(+) and
jrs.resource_id=jrrev.resource_id(+) and
case when rctla.interface_line_context in ('INTERCOMPANY','ORDER ENTRY') then rctla.interface_line_attribute6 end=oola.line_id(+) and
oola.header_id=ooha.header_id(+) and
-- inv line, header, oe ship to
rctla.ship_to_site_use_id=hcsua2.site_use_id(+) and
hcsua2.cust_acct_site_id=hcasa2.cust_acct_site_id(+) and
hcasa2.party_site_id=hps2.party_site_id(+) and
rcta.ship_to_site_use_id=hcsua3.site_use_id(+) and
hcsua3.cust_acct_site_id=hcasa3.cust_acct_site_id(+) and
hcasa3.party_site_id=hps3.party_site_id(+) and
oola.ship_to_org_id=hcsua4.site_use_id(+) and
hcsua4.cust_acct_site_id=hcasa4.cust_acct_site_id(+) and
hcasa4.party_site_id=hps4.party_site_id(+) and
--
ooha.salesrep_id=jrs2.salesrep_id(+) and
ooha.org_id=jrs2.org_id(+) and
jrs2.resource_id=jrrev2.resource_id(+) and
rcta.receipt_method_id=arm.receipt_method_id(+) and
arm.payment_channel_code=ifpct.payment_channel_code(+) and
ifpct.language(+)=userenv('lang') and
rcta.payment_trxn_extension_id=ifte.trxn_extension_id(+) and
ifte.instr_assignment_id=ipiua.instrument_payment_use_id(+) and
decode(ipiua.instrument_type,'CREDITCARD',ipiua.instrument_id)=ic.instrid(+) and
decode(ipiua.instrument_type,'BANKACCOUNT',ipiua.instrument_id)=ieba.ext_bank_account_id(+)
&contracts_joins
order by
operating_unit,
party_name,
account_number,
trx_date,
payment_sched_gl_date,
invoice_number,
trx_number,
apsa.terms_sequence_number
&order_by_line
&order_by_distribution
Parameter Name SQL text Validation
Ledger
gl.name=:ledger and
haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual)
LOV
Operating Unit
haouv.name=:operating_unit
LOV
Display Level
Y
LOV
Customer Name like
upper(hp.party_name) like upper(:customer_name)
LOV
Account Number
hca.account_number=:account_number
LOV
Consolidated Invoice Number
acia.cons_billing_number=:cons_billing_number
Char
Transaction Number
rcta.trx_number=:trx_number
Char
Inv. Date Period
rcta.trx_date>=(select
gps.start_date
from
gl_period_statuses gps
where
  gps.period_name=:period_name and
gps.set_of_books_id=gl.ledger_id and
gps.application_id=222) and
rcta.trx_date<(select
gps.end_date+1
from
gl_period_statuses gps
where
gps.period_name=:period_name and
gps.set_of_books_id=gl.ledger_id and
gps.application_id=222)
LOV
Inv. Date From
rcta.trx_date>=:trx_date_from
Date
Inv. Date To
rcta.trx_date<:trx_date_to+1
Date
GL Period
:gl_period=:gl_period
LOV
GL Date From
:gl_date_from=:gl_date_from
Date
GL Date To
:gl_date_to=:gl_date_to
Date
Inv. Creation Date From
rcta.creation_date>=:date_from
Date
Inv. Creation Date To
rcta.creation_date<:date_to-1
Date
Status
apsa.status=xxen_util.lookup_code(:status,'PAYMENT_SCHEDULE_STATUS',222)
LOV
Overdue for more than x Days
apsa.class in ('INV','DM') and apsa.status='OP' and greatest(trunc(sysdate)-apsa.due_date,0)>:overdue_days
Number
Salesperson
jrrev.resource_name=:salesperson
LOV
Transaction Class
apsa.class=xxen_util.lookup_code(:trx_class,'INV/CM/ADJ',222)
LOV
Transaction Type
rctta.name=:trx_type
LOV
Batch Source
rbsa.name=:batch_source
LOV
Batch Name
rba.name=:batch_name
LOV
Interface Category
rcta.interface_header_context=:category and nvl2(rctla.customer_trx_line_id,rctla.interface_line_context,:category)=:category
LOV
Sales Order
rctla.interface_line_context in ('INTERCOMPANY','ORDER ENTRY') and
rctla.interface_line_attribute6 in (select to_char(oola.line_id) from oe_order_headers_all ooha, oe_order_lines_all oola where ooha.order_number=:order_number and ooha.header_id=oola.header_id)
LOV
Distribution Class
:dist_class=:dist_class
LOV
Distribution Account From
select
'gcck.'||lower(x.application_column_name)||'>='''||x.segment_value||''''||decode(x.segment_num,x.max_segment_num,null,' and') text
from
(
select
fifs.application_column_name,
substr(:concatenated_segments_from,sum(fifs.display_size+1) over (order by fifs.segment_num rows between unbounded preceding and current row)-fifs.display_size,fifs.display_size) segment_value,
fifs.segment_num,
max(fifs.segment_num) over () max_segment_num
from
fnd_id_flex_segments fifs
where
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(
select
gl.chart_of_accounts_id
from
gl_ledgers gl
where
(:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and
gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and
rownum<=1
) and
fifs.enabled_flag='Y'
order by
fifs.segment_num
) x
LOV
Distribution Account To
select
'gcck.'||lower(x.application_column_name)||'<='''||x.segment_value||''''||decode(x.segment_num,x.max_segment_num,null,' and') text
from
(
select
fifs.application_column_name,
substr(:concatenated_segments_to,sum(fifs.display_size+1) over (order by fifs.segment_num rows between unbounded preceding and current row)-fifs.display_size,fifs.display_size) segment_value,
fifs.segment_num,
max(fifs.segment_num) over () max_segment_num
from
fnd_id_flex_segments fifs
where
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(
select
gl.chart_of_accounts_id
from
gl_ledgers gl
where
(:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and
gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and
rownum<=1
) and
fifs.enabled_flag='Y'
order by
fifs.segment_num
) x
LOV
Print Date from
rcta.printing_original_date>=:print_date_from
DateTime
Print Date to
rcta.printing_original_date<=:print_date_to
DateTime
Display Contracts Details
,(
select
rctla.customer_trx_line_id,
-- oks --
obtl.bill_instance_number, 
oklb1.line_number||nvl2(oklb2.line_number,'.',null)||oklb2.line_number contract_line,
decode(okslb1.usage_type,'VRT',' Actual','FRT',' Fixed','NPR',' Negotiated') usage_type,
obsl.date_billed_from,
obsl.date_billed_to,
ccv.counter_reading end_read,
ccv.value_timestamp end_read_date,
obsld.actual,
decode(oklb2.lse_id,13,obsld.result) result,
nvl(oklb2.date_terminated,oklb.date_terminated) date_terminated,
msiv2.concatenated_segments||nvl2(msiv2.description,' - '||msiv2.description,null) covered_item,
cc.name installed_counter,
-- okl --
ocasb.id ocasb_id, 
nvl(round(months_between(obsl.date_billed_to+1,obsl.date_billed_from),2),ocasb.frequency) frequency,
ostb.code stream_type,
ose.stream_element_date,
nvl(decode(oki2.jtot_object1_code,'OKX_CUSTPROD',oki2.object1_id1,'OKX_COUNTER',ccg.source_object_id),
    (select
     oki3.object1_id1
     from
     okc_k_lines_b oklb2,
     okc_k_lines_b oklb3,
     okc_k_items oki3
     where
     case when oklb.lse_id in (49,53) then (select to_number(oki2.object1_id1) from okc_k_items oki2 where ocasb.kle_id=oki2.cle_id and oki2.jtot_object1_code='OKX_COVASST' and rownum=1) else ocasb.kle_id end=oklb2.cle_id and
     oklb2.lse_id=43 and
     oklb3.lse_id=45 and
     oklb2.id=oklb3.cle_id and
     oklb3.id=oki3.cle_id and
     oki3.jtot_object1_code='OKX_IB_ITEM' and
     rownum=1
    )
) instance_id,
nvl(oklb2.dnz_chr_id,ocasb.khr_id) dnz_chr_id,
case when oklb.lse_id in (49,53) then oklb.cle_id else ocasb.kle_id end oklb1_id,
nvl(oklb2.lse_id,oklb.lse_id) lse_id
from
ra_customer_trx_lines_all rctla,
-- oks --
oks_bill_txn_lines obtl,
oks_bill_cont_lines obcl,
oks_bill_sub_lines obsl,
oks_bill_sub_line_dtls obsld,
cs_counter_values ccv,
(select oki2.* from okc_k_items oki2 where oki2.jtot_object1_code in ('OKX_COVITEM','OKX_CUSTPROD','OKX_COUNTER')) oki2,
okc_k_lines_b oklb2,
okc_k_lines_b oklb1,
oks_k_lines_b okslb1,
cs_counters cc,
cs_counter_groups ccg,
mtl_system_items_vl msiv2,
-- okl --
( select 
    ocasb.*,
    (select decode(orb.object1_id1,'A',12,'S',6,'Q',3,'M',1) 
     from okc_rules_b orb 
     where 
      orgb.id=orb.rgp_id and 
      orb.rule_information7 is null and 
      orb.rule_information_category='LASLL' and 
      rownum=1
    ) frequency
  from
    okl_cnsld_ar_strms_b ocasb,
    (select * from okc_rule_groups_b orgb where orgb.rgd_code='LALEVL') orgb
  where
    ocasb.kle_id=orgb.cle_id(+)
) ocasb,
okl_strm_type_b ostb,
okl_strm_elements ose,
okc_k_lines_b oklb  
where   
 decode(rctla.interface_line_context,'OKS CONTRACTS',nvl2(translate(rctla.interface_line_attribute3,'A0123456789','A'),null,rctla.interface_line_attribute3)) = obtl.bill_instance_number (+) and
 decode(rctla.interface_line_context,'OKL_CONTRACTS',nvl2(translate(rctla.interface_line_attribute10||rctla.interface_line_attribute11,'A0123456789','A'),null,rctla.interface_line_attribute10||rctla.interface_line_attribute11)) = ocasb.id(+) and
 -- oks --
 obtl.bsl_id=obsl.id(+) and
 obsl.bcl_id=obcl.id(+) and
 obtl.bsl_id=obsld.bsl_id(+) and
 obsld.ccr_id=ccv.counter_value_id(+) and
 obsl.cle_id=oki2.cle_id(+) and
 decode(oki2.jtot_object1_code,'OKX_COVITEM',oki2.object1_id1)=msiv2.inventory_item_id(+) and
 decode(oki2.jtot_object1_code,'OKX_COVITEM',oki2.object1_id2)=msiv2.organization_id(+) and
 decode(oki2.jtot_object1_code,'OKX_COUNTER',oki2.object1_id1)=cc.counter_id(+) and
 cc.counter_group_id=ccg.counter_group_id(+) and
 ccg.source_object_code(+)='CP' and
 obsl.cle_id=oklb2.id(+) and
 oklb2.cle_id=oklb1.id(+) and
 oklb2.cle_id=okslb1.cle_id(+) and
 -- okl --
 ocasb.sty_id=ostb.id(+) and
 ocasb.sel_id=ose.id(+) and
 ocasb.kle_id=oklb.id(+)
) okc,
okc_line_styles_b olsb,
csi_item_instances cii,
okc_k_headers_all_b okhab,
okc_k_lines_tl oklt1
LOV
Blitz Report™