OKL Generate Reconciliation

Description
Categories: BI Publisher
Application: Lease and Finance Management
Source: Generate Reconciliation Report
Short Name: OKLGIRECONRPT
DB package: OKL_REPORT_GENERATOR_PVT
 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
Parameter Name SQL text Validation
Report Type
 
LOV Oracle
Report Definition Name
 
LOV Oracle
Ledger
 
LOV Oracle
Period From
 
LOV Oracle
Period To
 
LOV Oracle
Display Summary
 
LOV Oracle
Display Drilldown 1
 
LOV Oracle
Display Drilldown 2
 
LOV Oracle
Download
Blitz Report In Action