OKL Generate Reconciliation

âžœ Back to Library
Description
Categories: BI Publisher
Application: Lease and Finance Management
Source: Generate Reconciliation Report
Short Name: OKLGIRECONRPT
DB package: OKL_REPORT_GENERATOR_PVT
Run OKL Generate Reconciliation and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
 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