SELECT trx_id,
trx_type,
trx_cur_code,
exchange_rate,
exchange_date,
trx_date,
trx_number,
trx_doc_type,
trx_class,
trx_lang,
func_cur_code ,
batch_name,
seq_name,
period_set
FROM (SELECT trx.customer_trx_id trx_id,
trx_types.type trx_type,
trx.invoice_currency_code trx_cur_code,
decode(sob.currency_code,'EUR', trx.exchange_rate,rates.conversion_rate) exchange_rate,
to_char(trx.exchange_date,'YYYY-MM-DD') exchange_date,
to_char(trx.trx_date,'YYYY-MM-DD') trx_date,
trx.trx_number trx_number,
NVL(trx.global_attribute17,'FC') trx_doc_type, -- trx_doc_type
:p_gen_option||trx.global_attribute1 trx_class, -- trx_type (original,corrective,consolidated)
LOWER(NVL(trx.global_attribute18,'ES')) trx_lang , -- language
sob.currency_code func_cur_code,
batch.name batch_name,
seqs.name seq_name,
sob.period_set_name period_set
FROM ra_customer_trx trx,
ra_cust_trx_types trx_types,
gl_sets_of_books sob,
gl_daily_rates rates,
ra_batches batch,
fnd_document_sequences seqs
WHERE trx.legal_entity_id = :P_LEGAL_ENTITY_ID
AND trx.bill_to_customer_id = :P_CUST_ACCOUNT_ID
AND trx.bill_to_site_use_id = :P_BILL_TO_SITE_USE_ID
AND trx.cust_trx_type_id = trx_types.cust_trx_type_id
AND trx_types.type in ('INV','CM')
AND trx.org_id = trx_types.org_id
AND trx.complete_flag = 'Y'
AND trx.customer_trx_id = NVL(:p_trx_id,trx.customer_trx_id)
AND trx.invoice_currency_code = :P_TRX_CUR
AND trx.trx_date between :P_TRX_DATE_FROM and :P_TRX_DATE_TO
AND ((:p_gen_option='O' and (trx.global_attribute16 is null OR trx.global_attribute16 = 'TBR'))
OR (:p_gen_option = 'C' and trx.global_attribute16 = 'ACC'))
AND ((trx_types.type ='INV')
OR (trx_types.type='CM' AND (trx.previous_customer_trx_id is not null
OR (( (select count(distinct ara.applied_customer_trx_id)
from ar_Receivable_applications ara
where ara.customer_trx_id = trx.customer_trx_id) = 1 )
AND not exists (select 1 from ar_payment_schedules
where customer_trx_id = trx.customer_trx_id
and amount_due_remaining <> 0)))
AND trx.global_attribute19 is not null and trx.global_attribute20 is not null))
AND exists (select 1
from ra_customer_trx_lines_all trx_lines
where trx_lines.customer_trx_id = trx.customer_trx_id
and trx_lines.line_type = 'TAX' )
AND trx.set_of_books_id = sob.set_of_books_id
AND rates.from_currency(+) = trx.invoice_currency_code
AND rates.to_currency(+) ='EUR'
AND rates.conversion_type(+) = trx.exchange_rate_type
AND rates.conversion_date(+) = trx.trx_date
AND trx.batch_id = batch.batch_id(+)
AND trx.doc_sequence_id = seqs.doc_sequence_id(+)
ORDER BY trx.trx_number)
WHERE ROWNUM <= :p_max_no_of_invs |
