JL Colombian Fixed Assets Inflation Adjusted Drill Down - draft

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