SELECT
seq_level, --1
trx_level, --2
currency_code, --3
trx_detail_type_code, --4
trx_detail_type_value, --5
operating_unit, --6
application, --7
account, --8
gl_period, --9
event, --10
transaction_type, --11
stream_type, --12
fin_product, --13
contract_number, --14
transaction_number, --15
transaction_date, --16
gl_date, --17
cr_amount, --18
dr_amount, --19
amount, --20
stream_period --21
,asset_number --22
,stream_date -- 23
,accrual_date -- 24
FROM (
SELECT
'111' seq_level,
'GL_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
trx.value4_text application,
trx.value13_text account,
NULL gl_period,
value18_text event,
NULL transaction_type,
NULL stream_type,
NULL fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'GL_ACC_OTHER_APP'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value13_text,
trx.value4_text,
trx.value18_text
UNION ALL
SELECT
'112' seq_level,
'GL_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
trx.value13_text account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'GL_ACC_NON_SPEC_CRITERIA'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value13_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
seq_level,
'GL_2' trx_level,
currency_code,
trx_detail_type_code,
NULL trx_detail_type_value,
operating_unit,
application,
account,
NULL gl_period,
event,
transaction_type,
stream_type,
fin_product,
contract_number,
transaction_number,
asset_number,
stream_period,
stream_date,
transaction_date,
gl_date,
cr_amount,
dr_amount,
accrual_date,
amount
FROM
(SELECT
'211' seq_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
trx.value14_text operating_unit,
trx.value4_text application,
trx.value13_text account,
trx.value18_text event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
trx.value1_date transaction_date,
trx.value2_date gl_date,
nvl(trx.value2_num,0) cr_amount,
nvl(trx.value1_num,0) dr_amount,
NULL accrual_date,
trx.value3_num amount,
decode(trx.value1_text, 'GL_ACC_OTHER_APPS', 1, 'GL_ACC_NON_SPEC_CRITERIA', 2, 4) trx_sequence
FROM OKL_G_REPORTS_GT trx
WHERE :p_drill2_yn ='Y'
and trx.value1_text in ('GL_ACC_OTHER_APPS','GL_ACC_NON_SPEC_CRITERIA')
ORDER BY 22)
UNION ALL
SELECT
'113' seq_level,
'STR_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
NULL transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'STRMS_WITHOUT_TRX'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value9_text
UNION ALL
SELECT
'213' seq_level,
'STR_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
NULL transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
NULL transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE
trx.value1_text = 'STRMS_WITHOUT_TRX'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'114' seq_level,
'STR_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'NON_SPEC_STRMS_NASE'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'214' seq_level,
'STR_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
NULL transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'NON_SPEC_STRMS_NASE'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'115' seq_level,
'STR_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'NON_SPEC_STRMS_ASE'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'215' seq_level,
'STR_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
NULL transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'NON_SPEC_STRMS_ASE'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'116' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_SPEC_STRMS_ASE'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'216' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
NULL stream_date,
trx.value1_date transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_SPEC_STRMS_ASE'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'117' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_SPEC_STRMS_NASE'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'217' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
trx.value1_date transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_SPEC_STRMS_NASE'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'118' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_NON_SPEC_STRMS'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'218' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
trx.value1_date transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE
trx.value1_text = 'TRX_NON_SPEC_STRMS'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'119' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_ADD_ACC_EVENTS'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'219' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
trx.value1_date transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE
trx.value1_text = 'TRX_ADD_ACC_EVENTS'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'120' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_UNACCOUNTED'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'220' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
trx.value1_date transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE
trx.value1_text = 'TRX_UNACCOUNTED'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'121' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_UNPOSTED'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'221' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
trx.value1_date transaction_date,
NULL gl_date,
nvl(trx.value2_num,0) cr_amount,
nvl(trx.value1_num,0) dr_amount,
NULL accrual_date,
nvl(trx.value3_num,0) amount
FROM OKL_G_REPORTS_GT trx
WHERE
trx.value1_text = 'TRX_UNPOSTED'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'122' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
trx.value1_date transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
trx.value4_date accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'NON_SPEC_PERIOD_TRX_SPEC_PERIOD'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value1_date,
trx.value4_date,
trx.value9_text
UNION ALL
SELECT
'222' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
trx.value1_date transaction_date,
NULL gl_date,
nvl(trx.value2_num,0) cr_amount,
nvl(trx.value1_num,0) dr_amount,
trx.value4_date accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
WHERE
trx.value1_text = 'NON_SPEC_PERIOD_TRX_SPEC_PERIOD'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'123' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
trx.value1_date transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
trx.value4_date accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_POST_NON_SPEC_PERIOD'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value1_date,
trx.value4_date,
trx.value9_text
UNION ALL
SELECT
'223' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
trx.value1_date transaction_date,
NULL gl_date,
nvl(trx.value2_num,0) cr_amount,
nvl(trx.value1_num,0) dr_amount,
trx.value4_date accrual_date,
trx.value3_num amount
FROM OKL_G_REPORTS_GT trx
where trx.value1_text = 'TRX_POST_NON_SPEC_PERIOD'
AND :p_drill2_yn ='Y'
UNION ALL
SELECT
'124' seq_level,
'TRX_1' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
NULL contract_number,
NULL transaction_number,
NULL asset_number,
NULL stream_period,
NULL stream_date,
NULL transaction_date,
NULL gl_date,
NULL cr_amount,
NULL dr_amount,
NULL accrual_date,
SUM(trx.value3_num) amount
FROM OKL_G_REPORTS_GT trx
WHERE trx.value1_text = 'TRX_POST_NON_SPEC_ACCOUNTS'
AND :p_drill1_yn ='Y'
GROUP BY trx.value11_text,
trx.value1_text,
trx.value14_text,
trx.value8_text,
trx.value3_text,
trx.value9_text
UNION ALL
SELECT
'224' seq_level,
'TRX_2' trx_level,
trx.value11_text currency_code,
trx.value1_text trx_detail_type_code,
NULL trx_detail_type_value,
trx.value14_text operating_unit,
NULL application,
NULL account,
NULL gl_period,
NULL event,
trx.value3_text transaction_type,
trx.value9_text stream_type,
trx.value8_text fin_product,
trx.value5_text contract_number,
trx.value2_text transaction_number,
trx.value6_text asset_number,
NULL stream_period,
trx.value3_date stream_date,
trx.value1_date transaction_date,
NULL gl_date,
nvl(trx.value2_num,0) cr_amount,
nvl(trx.value1_num,0) dr_amount,
NULL accrual_date,
nvl(trx.value3_num,0) amount
FROM OKL_G_REPORTS_GT trx
WHERE
trx.value1_text = 'TRX_POST_NON_SPEC_ACCOUNTS'
AND :p_drill2_yn ='Y'
) main
WHERE :P_REPORT_TYPE_CODE = 'INCOME'
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23 |
