SELECT
aeh.accounting_date GL_DATE
,ENT.TRANSACTION_NUMBER TRANSACTION_NUMBER
,fns.name DOCUMENT_SEQUENCE_NAME
,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
,xlk.meaning ACCOUNTING_CLASS_NAME
,ael.description LINE_DESCRIPTION
,gcck.concatenated_segments ACCOUNTING_CODE_COMBINATION
--,xla_report_utility_pkg.get_ccid_desc(gll.chart_of_accounts_id,ael.code_combination_id) CODE_COMBINATION_DESCRIPTION
,aeh.entity_id ENTITY_ID
,aeh.event_id EVENT_ID
,aeh.ae_header_id HEADER_ID
,aeh.event_type_code EVENT_TYPE_CODE
,aeh.gl_transfer_status_code GL_TRANSFER_STATUS
,DECODE(gl_transfer_status_code,'Y',&G_POSTING_FLAG,'') POSTING_STATUS
,aeh.accounting_entry_status_code ACCOUNTING_ENTRY_STATUS
,aeh.balance_type_code BALANCE_TYPE_CODE
,ael.ae_line_num LINE_NUMBER
,ael.code_combination_id CL_CODE_COMBINATION_ID
,ael.accounted_dr ACCOUNTED_DR
,ael.accounted_cr ACCOUNTED_CR
,NVL(ael.accounted_dr,0) M_ACCOUNTED_DR -- for calculating running balace these columns being used.
,NVL(ael.accounted_cr,0) M_ACCOUNTED_CR
,ael.entered_dr ENTERED_DR
,ael.entered_cr ENTERED_CR
,ael.currency_code ENTERED_CURRENCY
,ael.control_balance_flag CONTROL_BALANCE_FLAG
,ael.displayed_line_number DISPLAYED_LINE_NUMBER
,ent.entity_code ENTITY_CODE
,ent.source_id_int_1 SOUCE_ID_INT_1
,ent.source_id_int_2 SOUCE_ID_INT_2
,ent.source_id_int_3 SOUCE_ID_INT_3
,ent.source_id_int_4 SOUCE_ID_INT_4
,&P_ALIAS_BALANCING_SEGMENT BALANCING_SEGMENT
,&P_ALIAS_ACCOUNT_SEGMENT NATURAL_ACCOUNT_SEGMENT
,fvl.description NATURAL_ACCOUNT_SEGMENT_DESC
,CASE aeh.application_id
WHEN 200 THEN
CASE
WHEN ent.entity_code = 'AP_INVOICES' THEN
(select LU.displayed_field
FROM AP_LOOKUP_CODES LU
,AP_INVOICES_ALL I
WHERE LU.lookup_type = 'INVOICE TYPE'
AND LU.lookup_code = I.invoice_type_lookup_code
AND I.invoice_id = ent.source_id_int_1)
WHEN ent.entity_code = 'AP_PAYMENTS' THEN
(SELECT LU.displayed_field
FROM AP_LOOKUP_CODES LU
,AP_CHECKS_ALL C
WHERE LU.lookup_type = 'PAYMENT METHOD'
AND LU.lookup_code = C.payment_method_lookup_code
AND C.check_id = ent.source_id_int_1)
ELSE
(SELECT name
from xla_event_types_vl
WHERE application_id = 200
AND entity_code = 'MANUAL')
END
WHEN 222 THEN
CASE
WHEN ent.entity_code = 'ADJUSTMENTS' THEN
(select ttlkp.meaning
from ar_adjustments_all adj
,fnd_lookups ttlkp
WHERE ttlkp.lookup_type = 'JGZZ_SLR_TRANS_TYPE'
AND ((adj.adjustment_type= 'E' and
ttlkp.lookup_code = 'ENDORSEMENT') or
(adj.adjustment_type <> 'E' and
ttlkp.lookup_code = 'ADJUSTMENT'))
AND adj.adjustment_id = ent.source_id_int_1)
WHEN ent.entity_code = 'TRANSACTIONS' THEN
(select ttlkp.meaning
from ra_customer_trx_all ct
,ra_cust_trx_types_all ctt
,fnd_lookups ttlkp
WHERE ttlkp.lookup_code = ctt.type
AND ttlkp.lookup_type = 'JGZZ_SLR_TRANS_TYPE'
AND ctt.cust_trx_type_id = ct.cust_trx_type_id
AND ct.org_id = ctt.org_id
AND ct.customer_trx_id = ent.source_id_int_1)
WHEN ent.entity_code = 'RECEIPTS' THEN
(select ttlkp.meaning
from ar_cash_receipts_all cr
,fnd_lookups ttlkp
WHERE ttlkp.lookup_code = decode(cr.type, 'MISC', 'MISCELLANEOUS', 'TRADE_RECEIPTS')
AND ttlkp.lookup_type = 'JGZZ_SLR_TRANS_TYPE'
AND cr.cash_receipt_id = ent.source_id_int_1)
WHEN ent.entity_code = 'BILLS_RECEIVABLE' THEN
(select ttlkp.meaning
from ra_customer_trx_all ct
,ra_cust_trx_types_all ctt
,fnd_lookups ttlkp
WHERE ttlkp.lookup_code = ctt.type
AND ttlkp.lookup_type = 'JGZZ_SLR_TRANS_TYPE'
AND ctt.cust_trx_type_id = ct.cust_trx_type_id
AND ct.customer_trx_id = ent.source_id_int_1)
ELSE
(select name
from xla_event_types_vl
WHERE application_id = 200
AND entity_code = 'MANUAL')
END
END transaction_type
FROM
xla_transaction_entities_upg ent
,xla_ae_headers aeh
,xla_ae_lines ael
,fnd_document_sequences fns
,xla_lookups xlk
,gl_ledgers gll
,gl_code_combinations_kfv gcck
,fnd_id_flex_segments fsg
,fnd_flex_values_vl fvl
,fnd_segment_attribute_values fsav2
WHERE ael.application_id = aeh.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND xlk.lookup_type = 'XLA_ACCOUNTING_CLASS'
AND xlk.lookup_code = ael.accounting_class_code
AND ent.application_id = aeh.application_id
AND ent.entity_id = aeh.entity_id
AND fns.application_id(+) = aeh.application_id
AND fns.doc_sequence_id(+) = AEH.DOC_SEQUENCE_ID
AND gll.ledger_id = aeh.ledger_id
AND gcck.code_combination_id = ael.code_combination_id
AND gcck.chart_of_accounts_id = gll.chart_of_accounts_id
AND fsav2.application_id = 101
AND fsav2.id_flex_code = 'GL#'
AND fsav2.id_flex_num = gll.chart_of_accounts_id
AND fsav2.attribute_value = 'Y'
AND fsav2.segment_attribute_type = 'GL_ACCOUNT'
AND fsg.application_id = 101
AND fsg.id_flex_code = 'GL#'
AND fsg.id_flex_num = gll.chart_of_accounts_id
AND fvl.flex_value = &P_ALIAS_ACCOUNT_SEGMENT
AND fsg.application_column_name = fsav2.application_column_name
AND fsg.flex_value_set_id = fvl.flex_value_set_id
AND AEL.LEDGER_ID = AEH.LEDGER_ID
AND AEH.LEDGER_ID = :LEDGER_ID
AND AEH.APPLICATION_ID = :CCID_APPLICATION_ID
AND AEL.PARTY_ID = :PARTY_ID
AND NVL(AEL.PARTY_SITE_ID,-999) = :PARTY_SITE_ID
AND AEL.PARTY_TYPE_CODE = :PARTY_TYPE_CODE
AND AEH.ACCOUNTING_DATE BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND ael.code_combination_id <> :code_combination_id
AND (aeh.gl_transfer_status_code = :P_GL_TRANSFER_STATUS OR :P_GL_TRANSFER_STATUS IS NULL OR :P_GL_TRANSFER_STATUS='A')
AND EXISTS (
SELECT 1
FROM XLA_AE_LINES AEL1
WHERE AEL1.AE_HEADER_ID = AEH.AE_HEADER_ID
AND AEL1.APPLICATION_ID = AEH.APPLICATION_ID
--AND AEL1.CONTROL_BALANCE_FLAG = 'Y'
AND AEL1.CODE_COMBINATION_ID = :CODE_COMBINATION_ID)
AND ( AEH.DOC_SEQUENCE_ID = :P_DOCUMENT_SEQUENCE_NAME OR :P_DOCUMENT_SEQUENCE_NAME IS NULL)
AND NVL(AEH.DOC_SEQUENCE_VALUE,-1) BETWEEN NVL(:P_DOC_SEQUENCE_NUMBER_FROM,NVL(AEH.DOC_SEQUENCE_VALUE,-1)) AND NVL(:P_DOC_SEQUENCE_NUMBER_TO,NVL(AEH.DOC_SEQUENCE_VALUE,-1))
AND ( :P_DIST_DR_CR IS NULL
OR
(( :P_DIST_DR_CR = 'DEBIT' AND AEL.ACCOUNTED_DR BETWEEN
NVL(:P_DIST_AMT_FROM,AEL.ACCOUNTED_DR) AND
NVL(:P_DIST_AMT_TO,AEL.ACCOUNTED_DR))
OR
( :P_DIST_DR_CR = 'CREDIT' AND AEL.ACCOUNTED_CR BETWEEN
NVL(:P_DIST_AMT_FROM,AEL.ACCOUNTED_CR) AND
NVL(:P_DIST_AMT_TO,AEL.ACCOUNTED_CR))
)
)
&P_DIST_ACCOUNT_FF_RANGE_FILTER
&P_OTHER_FILTER
ORDER BY aeh.accounting_date
,aeh.doc_sequence_value
,ent.transaction_number |
