

ACCEPT beg_date PROMPT 'Enter the first DATE in your range (format: MM/DD/YYYY)'
ACCEPT end_date PROMPT 'Enter the last DATE in your range (format: MM/DD/YYYY)'
ACCEPT budget_purpose PROMPT 'Enter the BUDGET PURPOSE (format: ######)'
ACCEPT obj1 PROMPT 'Enter the first OBJECT in your range (format: ####)'
ACCEPT obj2 PROMPT 'Enter the last OBJECT in your range (format: #####)'



/***********************************************************************************************/
/***********************************************************************************************/
/***********************************************************************************************/
/*	Date Created:	07/23/2010
	Created by:	Loren Cook
	Description:	Selects expenditure information for a given Budget Purpose and Date Range
				based on specific segment criteria.  Similar to a FUNDS AVAILABLE REPORT OF
				TRANSACTIONS.  Enter Budget Purpose and the Date Range needed when prompted.*/
/***********************************************************************************************/
/***********************************************************************************************/
/***********************************************************************************************/




select
  gl.gl_code_combinations.segment1 as "Fund",
  gl.gl_code_combinations.segment2 as "Unit",
  gl.gl_code_combinations.segment3 as "Budget Purpose",
  gl.gl_code_combinations.segment4 as "Dept Activity 1",
  gl.gl_code_combinations.segment5 as "Dept Activity 2",
  gl.gl_code_combinations.segment6 as "Function",
  gl.gl_code_combinations.segment7 as "Natural Account",
  gl.gl_code_combinations.segment8 as "Object",
  gl.gl_code_combinations.segment9 as "Source of Funds",
  gl.gl_code_combinations.segment10 as "Funding FY",
  gl_je_lines.description||'   -   '||gl_je_lines.reference_1 as "Description",
  gl_je_headers.name as "Headers Name",
  TO_CHAR(GL_JE_LINES.EFFECTIVE_DATE,'DD-Mon-YY') as "Ledger Date",
  nvl(accounted_dr,0) as "Debit Amount",
  nvl(accounted_cr,0) as "Credit Amount",
  gl_je_batches.name as "Batches Reference",
  gl_je_headers.je_source as "Headers Source",
  gl_je_headers.je_category as "Headers Category",
  gl_je_headers.period_name as "Period Name",
  gl_je_batches.status as "Batches Status",
  gl_je_headers.actual_flag as "Headers Actual Flag",
  gl_je_lines.reference_2 as "Lines Reference 2",
  gl_je_lines.reference_3 as "Lines Reference 3",
  gl_je_lines.reference_4 as "Lines Reference 4",
  gl_je_lines.reference_5 as "Lines Reference 5",
  gl_je_lines.reference_6 as "Lines Reference 6",
  gl_je_lines.reference_7 as "Lines Reference 7",
  gl_je_lines.reference_8 as "Lines Reference 8",
  gl_je_lines.reference_9 as "Lines Reference 9",
  gl_je_lines.reference_10 as "Lines Reference 10"

from
	gl.gl_je_batches, gl.gl_je_headers, gl.gl_je_lines, gl.gl_code_combinations

where
-- ****TABLE LINKING SECTION DO NOT CHANGE ****
	gl_je_headers.je_header_id = gl_je_lines.je_header_id
	and  gl_code_combinations.code_combination_id = gl_je_lines.code_combination_id
	and  gl_je_batches.je_batch_id = gl_je_headers.je_batch_id

-- PERIOD DATE
	--  ****Replace the MON-YY with the period or periods needed.
	--and gl_je_headers.period_name = 'MON-YY'

-- CREATION DATE
	-- ****Replace the MM/DD/YYYY below with the creation date range needed.
	--and  GL_JE_LINES.CREATION_DATE between 'MM/DD/YYYY 00:00:00' and 'MM/DD/YYYY 23:59:59'

-- ENCUMBRANCE TYPE
	-- ****This flag is used to pull a specific Encumbrance type (A=Actuals)
	and  gl_je_headers.actual_flag = 'A'

--  FUNDING FISCAL YEAR
	-- ****Replace the YY below with the Funding Fiscal Year
	--and  gl_code_combinations.segment10 = 'YY'

--  UNIT
	-- ****Replace the ##### below with the Unit or range of Units needed.
	--and  gl_code_combinations.segment2 = '#####'
	--and  gl_code_combinations.segment2 between '#####' and '#####'
    --and  gl_code_combinations.segment2 in ('#####','#####','#####')

--  FUND
	-- ****Replace the ####### below with the Fund or range of Funds needed.
	--and  gl_code_combinations.segment1 = '#######'
	--and  gl_code_combinations.segment1 between '#######' and '#######'
	--and  gl_code_combinations.segment in ('#######','#######','#######')

--  DEBIT/CREDIT
	-- ****Replace the ####.## below with the Account Debit or Credit needed.
	--and	accounted_dr = '####.##'
	--and	accounted_cr = '####.##'

--  OBJECT
	-- ****Replace the #### below with the range of Objects needed.
	and  gl_code_combinations.segment8 between '&obj1' and '&obj2'
    --and  gl_code_combinations.segment8 in ('####','####','####')
    --and  gl_code_combinations.segment8 = '####'


--  NATURAL ACCOUNT
	-- ****Replace the ##### below with the Natural Account or range of Natural Accounts needed.
	--and  gl_code_combinations.segment7 = '#####'
	and  gl_code_combinations.segment7 between '50000' and '60000'

--  BUDGET PURPOSE
	-- ****Replace the ###### below with the Budget Purpose or range of Budget Purposes needed.
	and  gl_code_combinations.segment3 = '&budget_purpose'
	--and  gl_code_combinations.segment3 between '######' and '######'
    --and  gl_code_combinations.segment3 in ('######','######','######')

-- EFFECTIVE DATE
	-- ****Replace the MM/DD/YYYY below with the effective date range needed.
	and  GL_JE_LINES.EFFECTIVE_DATE between '&beg_date 00:00:00' and '&end_date 23:59:59'

--	SET OF BOOKS
	-- ****This limits your selection to the SIU Set Of Books.
	and  gl.gl_je_headers.ledger_id = '1'

	order by GL_JE_LINES.EFFECTIVE_DATE
