select
jb.name BATCH_NAME,
jc.user_je_category_name CATEGORY,
&C_Acct_Flex C_FLEXDATA,
aj.code_combination_id CCID,
jl1.je_line_num LINE,
ad.asset_number||' - '||ad.description ASSET,
sum(decode(decode(aj.debit_credit_flag,
'DR', 1, 'CR', -1, 0) *
sign(aj.adjustment_amount),
1, abs(aj.adjustment_amount),
null)) DR_AMT,
sum(decode(decode(aj.debit_credit_flag,
'DR', 1, 'CR', -1, 0) *
sign(aj.adjustment_amount),
-1,abs(aj.adjustment_amount),
null)) CR_AMT,
aj.transaction_header_id THID,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_display', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_Flex_Display
from JL_CO_FA_ADJUSTMENTS aj,
GL_JE_LINES jl1,
GL_JE_HEADERS jh,
GL_JE_CATEGORIES jc,
GL_JE_BATCHES jb,
FA_ADDITIONS ad,
GL_CODE_COMBINATIONS cc
where aj.book_type_code = :P_BOOK
and aj.period_counter_created = :PERIOD1_PC
and aj.adjustment_amount <> 0
and jb.description like :p_book||'/'||:p_period1||'%'
and jl1.je_header_id = jh.je_header_id
and RTRIM(NVL(jl1.reference_2, ' ')) = to_char(nvl(aj.sequence_line,0))
and aj.posting_flag = 'C'
and jc.je_category_name = jh.je_category
and jb.je_batch_id = jh.je_batch_id
and ad.asset_id = aj.asset_id
and cc.code_combination_id = aj.code_combination_id
&c_dynamic_where_clause
group by
&C_Acct_Flex,
aj.code_combination_id,
jb.name,
jc.user_je_category_name,
jl1.je_line_num,
ad.asset_number,
ad.description,
aj.transaction_header_id,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_display', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
order by
1, 2, 3, 4, 5, 6 |