COPY OF: APL: AR Transaction Register

➜ Back to Library
Description
Application: Receivables
Source: Transaction Register
Short Name: ARRXINVR

The report has now been enhanced to allow multiple accessible Ledgers or Operating Units to be selected in the Reporting Context parameter. Additionally, the Reporting Context parameter has been made optional. Leaving it null will allow the report to be run across all accessible Ledgers or Operating Units.
select
rx.organization_name                   ledger,
haouv.name                             operating_unit,
rx.rec_postable_flag                   postable,
rx.rec_balance                         "&bal_segment_p",
rx.rec_balance_desc                    "&bal_segment_d",
rx.trx_currency                        currency,
arpt_sql_func_util.get_lookup_meaning('INV/CM',trx_types.type) class,
rx.trx_number                          invoice_number,
rx.doc_sequence_value                  document_number,
trx_types.name                         "Type",
substrb(bill_to_party.party_name,1,50) customer_name,
bill_to.account_number                 customer_number,
bill_to_site.location                  customer_site,
rx.trx_date                            invoice_date,
rx.receivables_gl_date                 gl_date,
rx.trx_amount                          entered_amount,
rx.trx_acctd_amount                    functional_amount,
rx.trx_amount-arpt_sql_func_util.get_sum_of_trx_lines(rx.customer_trx_id,'TAX') total_line_amount,
arpt_sql_func_util.get_sum_of_trx_lines(rx.customer_trx_id,'FREIGHT') total_freight_amount,
arpt_sql_func_util.get_sum_of_trx_lines(rx.customer_trx_id,'TAX') total_tax_amount,
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='IGST' and
jtlv.tax_type_name not like 'IGST%RFD' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION')
else (select sum(jtlv.rounded_tax_amt_fun_curr) 
from 
jai_tax_lines_v jtlv, 
jai_reporting_associations_v jrav 
where 
jtlv.trx_id=rx.customer_trx_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='IGST' and
jtlv.tax_type_name not like 'IGST%RFD' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end) "IGST",
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='CGST' and
jtlv.tax_type_name not like 'CGST%RFD' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION')
else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav 
where 
jtlv.trx_id=rx.customer_trx_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='CGST' and
jtlv.tax_type_name not like 'CGST%RFD' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end) "CGST",
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='SGST' and
jtlv.tax_type_name not like 'SGST%RFD' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION')
else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav 
where 
jtlv.trx_id=rx.customer_trx_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='SGST' and
jtlv.tax_type_name not like 'SGST%RFD' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end ) "SGST",
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='CESS' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION')
else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav 
where 
jtlv.trx_id=rx.customer_trx_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='CESS' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end) "Compensation CESS",
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='CUSTOMS' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION')
else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav 
where 
jtlv.trx_id=rx.customer_trx_id and
jrav.entity_id=jtlv.tax_type_id and
jrav.reporting_code='CUSTOMS' and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jrav.entity_code='TAX_TYPE' and
jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end)  custom,
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jtlv.tax_type_code like 'SGST%RFD')
else (
select sum(jtlv.rounded_tax_amt_fun_curr) 
from 
jai_tax_lines_v jtlv
where 
rx.customer_trx_id=jtlv.trx_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jtlv.tax_type_code like 'SGST%RFD') end) "SGST Refund on Deemed Exports",
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jtlv.tax_type_code like 'CGST%RFD')
else (
select sum(jtlv.rounded_tax_amt_fun_curr) 
from 
jai_tax_lines_v jtlv
where 
rx.customer_trx_id=jtlv.trx_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jtlv.tax_type_code like 'CGST%RFD') end) "CGST Refund on Deemed Exports",
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jtlv.tax_type_code like 'TCS%')
else (
select sum(jtlv.rounded_tax_amt_fun_curr) 
from 
jai_tax_lines_v jtlv
where 
rx.customer_trx_id=jtlv.trx_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jtlv.tax_type_code like 'TCS%') end) "Tax Collected Source (TCS)",
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jtlv.tax_type_code='SAL_OTHER_CHARGES_BCD')
else (
select sum(jtlv.rounded_tax_amt_fun_curr) 
from 
jai_tax_lines_v jtlv
where 
rx.customer_trx_id=jtlv.trx_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jtlv.tax_type_code='SAL_OTHER_CHARGES_BCD') end) "BCD",
(case when trx_types.type='CM' then -1*(
select
sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr)
from 
ra_customer_trx_lines_all rctl,
jai_tax_lines_v jtlv
where
rx.customer_trx_id=rctl.customer_trx_id and
rctl.line_type='LINE' and
nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and
nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and
jtlv.application_id=222 and
jtlv.tax_type_code like 'IGST%RFD')
else (
select sum(jtlv.rounded_tax_amt_fun_curr) 
from 
jai_tax_lines_v jtlv
where 
rx.customer_trx_id=jtlv.trx_id and
jtlv.entity_code='TRANSACTIONS' and
jtlv.event_class_code='INVOICE' and
jtlv.application_id=222 and
jtlv.tax_type_code like 'IGST%RFD') end) "IGST Export Refund",
rx.rec_natacct                         receivables_account,
rx.rec_natacct_desc                    receivables_account_desc,
rx.rec_account                         receivables_account_full,
rx.rec_account_desc                    receivables_account_full_desc,
rx.functional_currency_code            functional_currency,
rx.exchange_type                       exchange_rate_type,
rx.exchange_date                       exchange_rate_date,
rx.exchange_rate                       exchange_rate,
gdct.user_conversion_type exchange_rate_type,
terms.name                             payment_terms,
rx.trx_due_date                        invoice_due_date,
methods.name                           payment_method,
arpt_sql_func_util.get_lookup_meaning('YES/NO', nvl(bill_to_site.tax_header_level_flag, nvl(bill_to.tax_header_level_flag, rx.tax_header_level_flag))) tax_calculation_level,
bas.name                               batch_source,
ba.name                                batch_name,
rx.cons_bill_number                    consolidated_bill_number,
doc_seq.name                           document_sequence_name,
substrb(ship_to_party.party_name,1,50) ship_to_customer_name,
ship_to.account_number                 ship_to_customer_number,
ship_to_site.location                  ship_to_customer_site,
(select
jprl_.registration_number
from
ra_customer_trx_lines_all rctla,
jai_party_regs jpr_,
jai_party_reg_lines jprl_
where
rctla.customer_trx_Id=rx.customer_trx_id and
rctla.line_type='LINE' and
jpr_.party_id=rctla.warehouse_id and
jpr_.party_reg_id=jprl_.party_reg_id and
jpr_.party_type_code='IO' and
jprl_.registration_type_code='GST' and
jprl_.effective_to is null and
rownum=1
) warehouse_registration_number,
(select 
jprl_.registration_number
from
jai_party_regs jpr_,
jai_party_reg_lines jprl_,
hz_cust_accounts hca,
ra_customer_trx_all rcta
where
jpr_.party_reg_id=jprl_.party_reg_id and
jpr_.party_type_code='THIRD_PARTY_SITE' and
jprl_.registration_type_code='GST' and
jprl_.effective_to is null and
rcta.customer_trx_id=rx.customer_trx_id and
rcta.sold_to_customer_id=hca.cust_account_id and
to_char(jpr_.party_id)=hca.orig_system_reference and
jpr_.customer_flag='Y' and
jpr_.party_site_id in (select cust_acct_site_id from hz_cust_site_uses_all hcsu where hcsu.site_use_id=rcta.bill_to_site_use_id and
hcsu.status='A') and
rownum=1
) customer_registration_number
from
ar_transactions_rep_itf rx,
ra_terms terms,
gl_daily_conversion_types gdct,
fnd_document_sequences doc_seq,
hz_cust_accounts ship_to,
hz_parties ship_to_party,
hz_cust_accounts bill_to,
hz_parties bill_to_party,
hz_cust_site_uses_all ship_to_site,
hz_cust_site_uses_all bill_to_site,
ra_cust_trx_types_all trx_types,
ar_receipt_methods methods,
ra_batches_all ba,
ra_batch_sources_all bas,
hr_all_organization_units_vl haouv
where
1=1 and
rx.ship_to_customer_id=ship_to.cust_account_id(+) and
rx.exchange_type=gdct.conversion_type(+) and
ship_to.party_id=ship_to_party.party_id(+) and
rx.ship_to_site_use_id=ship_to_site.site_use_id(+) and
rx.bill_to_customer_id=bill_to.cust_account_id and
bill_to.party_id=bill_to_party.party_id and
rx.bill_to_site_use_id=bill_to_site.site_use_id and
rx.cust_trx_type_id=trx_types.cust_trx_type_id and
rx.term_id=terms.term_id(+) and
rx.doc_sequence_id=doc_seq.doc_sequence_id(+) and
rx.receipt_method_id=methods.receipt_method_id(+) and
nvl(rx.org_id, -99)=nvl(trx_types.org_id, -99) and
rx.batch_id=ba.batch_id(+) and
rx.batch_source_id=bas.batch_source_id(+) and
nvl(rx.org_id, -99)=nvl(bas.org_id, -99) and
rx.request_id=fnd_global.conc_request_id and
rx.org_id=haouv.organization_id (+)
order by
rx.organization_name,
rx.rec_balance,
rx.rec_postable_flag,
rx.trx_currency,
arpt_sql_func_util.get_lookup_meaning('INV/CM',trx_types.type),
rx.trx_number,
rx.doc_sequence_value
Parameter NameSQL textValidation
Reporting Level
 
LOV Oracle
Reporting Context
decode(:p_reporting_level,'1000',rx.organization_name,'3000',haouv.name,null) = :p_reporting_entity_name
LOV
Company Segment Low
 
LOV
Company Segment High
 
LOV
GL Date From
 
Date
GL Date To
 
Date
Receivables Account Low
 
LOV
Receivables Account High
 
LOV
Entered Currency Low
 
LOV Oracle
Entered Currency High
 
LOV Oracle
Transaction Date Low
 
Date
Transaction Date High
 
Date
Batch Source Name
 
LOV
Transaction Type Low
 
LOV
Transaction Type High
 
LOV
Transaction Class
 
LOV Oracle
Document Sequence Name
 
LOV
Document Sequence Number From
 
LOV
Document Sequence Number To
 
LOV
Download
Blitz Report™