/*Create by Richard.Liu on 2012-06-12 for XXXX Company */
/*FOR DEPRN in XDL:
source_distribution_id_num_1 as asset_id,
source_distribution_id_num_2 as period_counter,
source_distribution_id_num_5 as distribution_id in fa_distribution_accounts */
SELECT UNIQUE xte.source_id_char_1 book_type_code,
xah.period_name,
fab.tag_number,
gcc.segment4 account,
xal.ae_line_num,
xal.entered_dr,
xal.entered_cr,
xal.accounted_dr,
xal.accounted_cr,
xal.description,
xte.entity_code,
xah.event_type_code
FROM xla_transaction_entities xte,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
fa_books fb,
fa_additions_b fab,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xdl.application_id = xah.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl.event_id
AND xah.application_id = xte.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND xah.entity_id = xte.entity_id
AND xe.application_id = 140
AND xah.Ledger_Id = '&Ledger_Id'
AND xah.event_type_code = 'DEPRECIATION'
AND fb.book_type_code = xte.source_id_char_1
AND fb.book_type_code = UPPER('&book_type_code')
AND xte.source_id_int_1 = fb.asset_id
AND fb.asset_id = fab.asset_id
AND fab.tag_number = '&tag_number'
UNION ALL
/*For Transaction IN XDL:
source_distribution_id_num_1 as transaction_id in FA_TRANSACTION_HEADERS*/
SELECT UNIQUE xte.source_id_char_1 book_type_code,
xah.period_name,
fab.tag_number,
gcc.segment4,
xal.ae_line_num,
xal.entered_dr,
xal.entered_cr,
xal.accounted_dr,
xal.accounted_cr,
xal.description,
xte.entity_code,
xah.event_type_code
FROM xla_transaction_entities xte,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
fa_books fb,
fa_additions_b fab,
fa_transaction_headers fth,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xdl.application_id = xah.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xte.application_id
AND xah.entity_id = xte.entity_id
AND xal.code_combination_id = gcc.code_combination_id
AND xe.application_id = 140
AND xah.Ledger_Id = '&Ledger_Id'
AND xah.event_type_code != 'DEPRECIATION'
AND fb.book_type_code = xte.source_id_char_1
AND fb.book_type_code = UPPER('&book_type_code')
AND fb.book_type_code = fth.book_type_code
AND fab.asset_id = fth.asset_id
AND xte.source_id_int_1 = fth.transaction_header_id
AND fb.asset_id = fab.asset_id
AND fab.tag_number = '&tag_number'
ORDER BY 12
/////////////////////////////////////////////////////////////////////////////////////
/*Create by Richard.Liu on 2012-06-12 for XXXX Company */
__***********************************************************************************
--*********** All reverse line has been removal**************************************
--***********************************************************************************
/*FOR DEPRN in XDL:
source_distribution_id_num_1 as asset_id,
source_distribution_id_num_2 as period_counter,
source_distribution_id_num_5 as distribution_id in fa_distribution_accounts
For DEPRN IN XDL:
source_distribution_type='DEPRN' */
SELECT UNIQUE xte.source_id_char_1 book_type_code,
xah.period_name,
fab.tag_number,
gcc.segment4 account,
xal.ae_line_num,
xal.entered_dr,
xal.entered_cr,
xal.accounted_dr,
xal.accounted_cr,
xal.description,
xte.entity_code,
xah.event_type_code
FROM xla_transaction_entities xte,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
fa_books fb,
fa_additions_b fab,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xdl.application_id = xah.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl.event_id
AND xah.application_id = xte.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND xah.entity_id = xte.entity_id
AND xe.application_id = 140
AND xah.Ledger_Id = '&Ledger_Id'
AND xah.event_type_code IN ('DEPRECIATION', 'ROLLBACK_DEPRECIATION')
AND fb.book_type_code = xte.source_id_char_1
AND fb.book_type_code = UPPER('&book_type_code')
AND xte.source_id_int_1 = fb.asset_id
AND fb.asset_id = fab.asset_id
AND fab.tag_number = '&tag_number'
AND NOT EXISTS
(SELECT 'T'
FROM xla_transaction_entities xte,
xla_distribution_links xdl2,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
fa_books fb,
fa_additions_b fab,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xdl2.application_id = xah.application_id
AND xdl2.ae_header_id = xah.ae_header_id
AND xdl2.ae_line_num = xal.ae_line_num
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl2.event_id
AND xah.application_id = xte.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND xah.entity_id = xte.entity_id
AND xe.application_id = 140
AND xah.Ledger_Id = '&Ledger_Id'
AND xah.event_type_code IN
('DEPRECIATION', 'ROLLBACK_DEPRECIATION')
AND fb.book_type_code = xte.source_id_char_1
AND fb.book_type_code = UPPER('&book_type_code')
AND xte.source_id_int_1 = fb.asset_id
AND fb.asset_id = fab.asset_id
AND fab.tag_number = '&tag_number'
AND XDL.EVENT_ID = XDL2.REF_EVENT_ID --****
)
INTERSECT
SELECT UNIQUE xte.source_id_char_1 book_type_code,
xah.period_name,
fab.tag_number,
gcc.segment4 account,
xal.ae_line_num,
xal.entered_dr,
xal.entered_cr,
xal.accounted_dr,
xal.accounted_cr,
xal.description,
xte.entity_code,
xah.event_type_code
FROM xla_transaction_entities xte,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
fa_books fb,
fa_additions_b fab,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xdl.application_id = xah.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl.event_id
AND xah.application_id = xte.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND xah.entity_id = xte.entity_id
AND xe.application_id = 140
AND xah.Ledger_Id = '&Ledger_Id'
AND xah.event_type_code IN ('DEPRECIATION', 'ROLLBACK_DEPRECIATION')
AND fb.book_type_code = xte.source_id_char_1
AND fb.book_type_code = UPPER('&book_type_code')
AND xte.source_id_int_1 = fb.asset_id
AND fb.asset_id = fab.asset_id
AND fab.tag_number = '&tag_number'
AND NOT EXISTS
(SELECT 'T'
FROM xla_transaction_entities xte,
xla_distribution_links xdl2,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
fa_books fb,
fa_additions_b fab,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xdl2.application_id = xah.application_id
AND xdl2.ae_header_id = xah.ae_header_id
AND xdl2.ae_line_num = xal.ae_line_num
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl2.event_id
AND xah.application_id = xte.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND xah.entity_id = xte.entity_id
AND xe.application_id = 140
AND xah.Ledger_Id = '&Ledger_Id'
AND xah.event_type_code IN
('DEPRECIATION', 'ROLLBACK_DEPRECIATION')
AND fb.book_type_code = xte.source_id_char_1
AND fb.book_type_code = UPPER('&book_type_code')
AND xte.source_id_int_1 = fb.asset_id
AND fb.asset_id = fab.asset_id
AND fab.tag_number = '&tag_number'
AND XDL2.EVENT_ID = XDL.REF_EVENT_ID --****
)
UNION ALL
/*For Transaction IN XDL:
source_distribution_id_num_1 as transaction_id in FA_TRANSACTION_HEADERS
For Transaction IN XDL:
source_distribution_type='TRX'*/
SELECT UNIQUE xte.source_id_char_1 book_type_code,
xah.period_name,
fab.tag_number,
gcc.segment4,
xal.ae_line_num,
xal.entered_dr,
xal.entered_cr,
xal.accounted_dr,
xal.accounted_cr,
xal.description,
xte.entity_code,
xah.event_type_code
FROM xla_transaction_entities xte,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
fa_books fb,
fa_additions_b fab,
fa_transaction_headers fth,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xdl.application_id = xah.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xte.application_id
AND xah.entity_id = xte.entity_id
AND xal.code_combination_id = gcc.code_combination_id
AND xe.application_id = 140
AND xah.Ledger_Id = '&Ledger_Id'
AND xah.event_type_code NOT IN ('DEPRECIATION', 'ROLLBACK_DEPRECIATION')
AND fb.book_type_code = xte.source_id_char_1
AND fb.book_type_code = UPPER('&book_type_code')
AND fb.book_type_code = fth.book_type_code
AND fab.asset_id = fth.asset_id
AND xte.source_id_int_1 = fth.transaction_header_id
AND fb.asset_id = fab.asset_id
AND fab.tag_number = '&tag_number'
ORDER BY 12
