APPS.IGI_BUD SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 12

	SELECT	'x'
	INTO	l_char
	FROM	sys.dual
	WHERE	TO_CHAR(TO_NUMBER(p_char)) = p_char;

Line: 55

SELECT 	period_ratio
FROM 	igi_bud_profile_periods
WHERE 	profile_code	= p_profile_code
AND 	set_of_books_id	= p_Set_Of_Books_Id
AND 	period_number	BETWEEN
		p_first_period AND p_last_period
AND 	period_ratio	<> 0;

Line: 73

	SELECT 	max(period_number)
     	, 	sum(period_ratio)
  	INTO 	l_Max_Period_Number
     	, 	l_Total_Ratio
  	FROM 	igi_bud_profile_periods
 	WHERE 	profile_code	= p_Profile_Code
   	AND 	set_of_books_id	= p_Set_Of_Books_Id
	AND 	period_number	BETWEEN p_Start_Period
				AND decode(p_Max_Period_Number
					, 0, period_number
					, p_Max_Period_Number
					)
	AND 	period_ratio	<> 0;

Line: 138

	SELECT 	1
  	INTO 	x
	FROM 	igi_bud_profile_codes pc
 	WHERE 	pc.set_of_books_id = p_set_of_books_id
	AND 	pc.profile_code = p_profile_code
	AND 	nvl(pc.start_date_active, sysdate-1) <= sysdate
	AND 	nvl(pc.end_date_active, sysdate+1) > sysdate;

Line: 165

PROCEDURE bud_profile_insert
	( 	p_sob_id		NUMBER
	, 	p_batch_id		NUMBER
	, 	p_header_id		NUMBER
	, 	p_line_number		NUMBER
	, 	p_cc_id			NUMBER
	, 	p_profile_code		VARCHAR2
	, 	p_start_period		VARCHAR2
	, 	p_entered_dr		NUMBER
	, 	p_entered_cr		NUMBER
	, 	p_description		VARCHAR2
	, 	p_reason_code		VARCHAR2
	, 	p_recurring		VARCHAR2
	, 	p_effect		VARCHAR2
	, 	p_next_year_budget	NUMBER
	)
IS
		p_period_amount		NUMBER;

Line: 193

SELECT 	period_number,period_ratio
FROM	IGI_BUD_PROFILE_PERIODS jupp
WHERE	jupp.PROFILE_CODE = p_profile_code
AND	jupp.SET_OF_BOOKS_ID = p_sob_id;

Line: 199

SELECT 	sum(nvl(jupp.period_ratio,0)) total
,       max(nvl(jubjl.period_number,0))
FROM	IGI_BUD_PROFILE_PERIODS jupp
,       IGI_BUD_JOURNAL_PERIODS jubjl
WHERE	jupp.PROFILE_CODE = p_profile_code
AND	jupp.SET_OF_BOOKS_ID = p_sob_id
AND     jubjl.BE_BATCH_ID = p_batch_id
AND     jubjl.BE_HEADER_ID = p_header_id
AND     jubjl.BE_LINE_NUM  = p_line_number
AND     jupp.PERIOD_NUMBER = jubjl.PERIOD_NUMBER;

Line: 211

SELECT 	jubjl.period_number
,       period_ratio
FROM	IGI_BUD_PROFILE_PERIODS jupp
,       IGI_BUD_JOURNAL_PERIODS jubjl
WHERE	jupp.PROFILE_CODE = p_profile_code
AND	jupp.SET_OF_BOOKS_ID = p_sob_id
AND     jupp.PERIOD_NUMBER   = jubjl.PERIOD_NUMBER
AND     jubjl.BE_BATCH_ID = p_batch_id
AND     jubjl.BE_HEADER_ID = p_header_id
AND     jubjl.BE_LINE_NUM  = p_line_number;

Line: 225

 select user_je_source_name
 from gl_je_sources
 where je_source_name = 'IGIGBMJL'
 and language = userenv('LANG');

Line: 240

	SELECT	gp.PERIOD_NUM
	, 	gp.PERIOD_YEAR
	, 	gsob.PERIOD_SET_NAME
	, 	gsob.ACCOUNTED_PERIOD_TYPE
	INTO	p_start_period_number
	, 	p_period_year
	, 	p_period_set_name
	, 	p_period_type
	FROM	GL_PERIODS gp
	, 	GL_SETS_OF_BOOKS gsob
	WHERE	gsob.SET_OF_BOOKS_ID = p_sob_id
	AND	gp.PERIOD_SET_NAME = gsob.PERIOD_SET_NAME
	AND	gp.PERIOD_NAME = p_start_period;

Line: 255

	SELECT	gp.PERIOD_NAME
	INTO	p_period_name
	FROM	GL_PERIODS gp
	WHERE	gp.PERIOD_SET_NAME = p_period_set_name
	AND	gp.PERIOD_YEAR = p_period_year
	AND	gp.PERIOD_TYPE = p_period_type
	AND	gp.PERIOD_NUM = period.period_number;

Line: 283

	INSERT INTO IGI_BUD_JOURNAL_PERIODS
	(	BE_BATCH_ID
	,	BE_HEADER_ID
	,	BE_LINE_NUM
	,	PERIOD_NUMBER
	,	PERIOD_YEAR
	,	PERIOD_NAME
	,	ENTERED_DR
	,	ENTERED_CR
	,	NEXT_YEAR_BUDGET
	)
	VALUES
	(	p_batch_id
        ,	p_header_id
	,	p_line_number
	,	period.period_number
	,	p_period_year
	,	p_period_name
	,	DECODE( SIGN(p_period_amount), '1',ABS(p_period_amount),NULL)
	,	DECODE( SIGN(p_period_amount),'-1',ABS(p_period_amount),NULL)
	,	NULL
	);

Line: 324

		UPDATE 	IGI_BUD_JOURNAL_PERIODS
      		SET    	NEXT_YEAR_BUDGET  = l_nyb_amt
      		WHERE	PERIOD_NUMBER     = nyb.period_number
		AND     BE_BATCH_ID 	  = p_batch_id
		AND     BE_HEADER_ID 	  = p_header_id
		AND     BE_LINE_NUM  	  = p_line_number;

Line: 331

      		UPDATE 	IGI_BUD_JOURNAL_PERIODS
      		SET	NEXT_YEAR_BUDGET  =
	         		p_next_year_budget - l_amount
      		WHERE 	PERIOD_NUMBER      = nyb.period_number
		AND 	BE_BATCH_ID = p_batch_id
		AND 	BE_HEADER_ID = p_header_id
		AND   	BE_LINE_NUM  = p_line_number;

Line: 348

	INSERT INTO GL_INTERFACE
	(	STATUS
	,	CREATED_BY
	,	DATE_CREATED
	,	GROUP_ID
	,	SET_OF_BOOKS_ID
	,	ACTUAL_FLAG
	,	USER_JE_CATEGORY_NAME
	,	USER_JE_SOURCE_NAME
	,	BUDGET_VERSION_ID
	,	CURRENCY_CODE
	,	ACCOUNTING_DATE
	,	CODE_COMBINATION_ID
	,	ENTERED_CR
	,	ENTERED_DR
	,	PERIOD_NAME
	,	REFERENCE1
	,	REFERENCE2
	,	REFERENCE4
	,	REFERENCE5
	,	REFERENCE7
	,	REFERENCE10
	,	REFERENCE21
	,	REFERENCE22
	,	REFERENCE23
	,	REFERENCE24
	,	REFERENCE25
	,	REFERENCE26
	,	REFERENCE27
	,	REFERENCE28
	,	REFERENCE29
	,	REFERENCE30
	)
	SELECT
		'HOLDING'
	,	'-1'
	,	SYSDATE
	,	jubjb.BE_BATCH_ID
	,	jubjb.SET_OF_BOOKS_ID
	,	'B'
	,	gjc.USER_JE_CATEGORY_NAME
	,	l_user_je_source_name
	,	jubjh.BUDGET_VERSION_ID
	,	jubjh.CURRENCY_CODE
	,	SYSDATE
	,	p_cc_id
	,	jubjp.ENTERED_CR
	,	jubjp.ENTERED_DR
	,	jubjp.PERIOD_NAME
	,	jubjb.NAME
	,	jubjb.NAME
	,	jubjh.NAME
	,	jubjh.DESCRIPTION
	,	'N'
	,	p_description
	,	'IGIGBUDPR'
	,	jubjb.BE_BATCH_ID
	,	p_profile_code
	,	p_reason_code
	,	p_start_period
	,	p_recurring
	,	p_effect
	,	jubjp.NEXT_YEAR_BUDGET
	,	jubjh.BE_HEADER_ID
	,	p_line_number
	FROM	IGI_BUD_JOURNAL_BATCHES jubjb
	,	IGI_BUD_JOURNAL_HEADERS jubjh
	,	IGI_BUD_JOURNAL_PERIODS jubjp
	,	GL_JE_CATEGORIES gjc
	WHERE	jubjb.BE_BATCH_ID = p_batch_id
	AND	jubjh.BE_HEADER_ID = p_header_id
	AND	jubjp.BE_HEADER_ID = p_header_id
	AND	jubjp.BE_LINE_NUM = p_line_number
	AND	gjc.JE_CATEGORY_NAME = jubjh.JE_CATEGORY_NAME
        --Start Bug 2885983 extra join to remove mjc
        AND     jubjh.be_header_id = jubjp.be_header_id;

Line: 426

END;	--	bud_profile_insert

Line: 429

	This function returns a select string which produces a comma seperated
	key flexfield for a given key flexfield
	Inputs:	p_appl_short_name	The application short name (eg SQLGL)
		p_id_flex_code		The flex code (eg GL#)
		p_if_flex_num		The flex num (eg 101)
		p_table_alias		Alias for table
	Output:	r_where_list 						     */

FUNCTION flexsql_select
	( 	p_appl_short_name	VARCHAR2
	, 	p_id_flex_code		VARCHAR2
	, 	p_id_flex_num		NUMBER
	, 	p_table_alias		VARCHAR2
	)
RETURN VARCHAR2
IS
	where_list		VARCHAR2(2000)	:=null;

Line: 449

SELECT 	fs.application_column_name
FROM	FND_ID_FLEX_SEGMENTS fs
, 	FND_APPLICATION a
WHERE	a.application_short_name = p_appl_short_name
AND	fs.application_id = a.application_id
AND	fs.ID_FLEX_CODE = p_id_flex_code
AND	fs.ID_FLEX_NUM = p_id_flex_num
AND	fs.ENABLED_FLAG = 'Y'
ORDER BY fs.SEGMENT_NUM;

Line: 461

	SELECT  decode(r_where_list, null, null, ',')||
		decode(p_table_alias,null,null,
			p_table_alias||'.')||
			segment.APPLICATION_COLUMN_NAME
	INTO    where_list
	FROM	dual;

Line: 472

END;		-- Of flexsql_select

Line: 475

	This function returns a select string which produces a concatenated
	key flexfield for a given key flexfield
	Inputs:	p_appl_short_name	The application short name (eg SQLGL)
		p_id_flex_code		The flex code (eg GL#)
		p_if_flex_num		The flex num (eg 101)
		p_table_alias		Alias for table
	Output:	r_where_list 						   */

FUNCTION flexsql_concat
		( p_appl_short_name	VARCHAR2
		, p_id_flex_code	VARCHAR2
		, p_id_flex_num		NUMBER
		, p_table_alias		VARCHAR2
		)
RETURN VARCHAR2
IS
	where_list		VARCHAR2(2000)	:=null;

Line: 495

SELECT 	fs.application_column_name
, 	str.concatenated_segment_delimiter delim
FROM	FND_ID_FLEX_SEGMENTS fs
, 	FND_ID_FLEX_STRUCTURES str
, 	FND_APPLICATION a
WHERE	a.application_short_name = p_appl_short_name
AND	fs.application_id = a.application_id
AND	fs.ID_FLEX_CODE = p_id_flex_code
AND	fs.ID_FLEX_NUM = p_id_flex_num
AND	fs.ENABLED_FLAG = 'Y'
AND     str.application_id = fs.application_id
AND     str.id_flex_code = fs.id_flex_code
AND     str.id_flex_num = fs.id_flex_num
ORDER BY fs.SEGMENT_NUM;

Line: 512

	SELECT  decode(r_where_list, null, null,
                               '||'''||segment.delim||'''||')||
			decode(p_table_alias,null,null,
				p_table_alias||'.')||
			segment.APPLICATION_COLUMN_NAME
	INTO    where_list
	FROM	dual;

Line: 550

SELECT 	fs.application_column_name
FROM	FND_ID_FLEX_SEGMENTS fs
, 	FND_APPLICATION a
WHERE	a.application_short_name = p_appl_short_name
AND	fs.application_id = a.application_id
AND	fs.ID_FLEX_CODE = p_id_flex_code
AND	fs.ID_FLEX_NUM = p_id_flex_num
AND	fs.ENABLED_FLAG = 'Y'
ORDER BY fs.SEGMENT_NUM;

Line: 562

	SELECT  decode(r_where_list, null, null, ' AND ')||
			decode(p_single_table_alias,null,null,
				p_single_table_alias||'.')||
			segment.APPLICATION_COLUMN_NAME||
			' '||p_not_between ||' BETWEEN '||
			decode(p_range_table_alias,null,null,
				p_range_table_alias||'.')||
			segment.APPLICATION_COLUMN_NAME||'_LOW AND '||
			decode(p_range_table_alias,null,null,
				p_range_table_alias||'.')||
			segment.APPLICATION_COLUMN_NAME||'_HIGH'
	INTO    where_list
	FROM	dual;

Line: 582

   This proceedure updates or inserts into igi_bud_profile_defaults
   Parameters:		Valid Code Combination ID
			Valid Set of Books ID
			Valid Profile Code			    */

PROCEDURE bud_profile_default
	( 	p_code_combination_id		NUMBER
	, 	p_set_of_books_id		NUMBER
	, 	p_new_profile_code		VARCHAR2
	)
IS
	err_msg			VARCHAR2(240);

Line: 596

	UPDATE 	igi_bud_profile_defaults
   	SET 	latest_profile_code = p_new_profile_code
 	WHERE 	code_combination_id = p_code_combination_id
   	AND 	set_of_books_id = p_set_of_books_id;

Line: 601

	IF SQL%NOTFOUND THEN		-- No row to update so
		INSERT INTO igi_bud_profile_defaults
		( code_combination_id
		, set_of_books_id
		, primary_profile_code
		, latest_profile_code
		, creation_date
		, created_by
		, last_update_date
		, last_updated_by
		, last_update_login
		)
		VALUES
		( 	p_code_combination_id
		, 	p_set_of_books_id
		, 	p_new_profile_code
		, 	p_new_profile_code
		, 	sysdate
		, 	-1
		, 	sysdate
		, 	-1
		, 	-1
		);

Line: 642

   This proceedure updates or inserts igi_bud_ny_balances
   Parameters:		JE_HEADER_ID of Posted Budget Journal */

PROCEDURE bud_next_year_budget
	( 	p_je_header_id		NUMBER
	,	p_set_of_books_id	NUMBER
	,	p_budget_version_id	NUMBER
	,	p_currency_code		VARCHAR2
	,	p_period_name		VARCHAR2
	)
IS
	p_code_combination_id	NUMBER;

Line: 656

SELECT	JE_LINE_NUM
, 	CODE_COMBINATION_ID
, 	REFERENCE_3
FROM	GL_JE_LINES
WHERE	JE_HEADER_ID = p_je_header_id;

Line: 666

	INSERT INTO IGI_BUD_NY_BALANCES
	( 	SET_OF_BOOKS_ID
	, 	CODE_COMBINATION_ID
	, 	BUDGET_VERSION_ID
	, 	PERIOD_NAME
	, 	CURRENCY_CODE
	, 	NEXT_YEAR_BUDGET)
	SELECT
	 	p_set_of_books_id
	, 	gjl.CODE_COMBINATION_ID
	, 	p_budget_version_id
	, 	p_period_name
	, 	p_currency_code
	, 	NVL(gjl.REFERENCE_8,0)
	FROM   	GL_JE_LINES gjl
	WHERE  	gjl.JE_HEADER_ID = p_je_header_id
	AND    	gjl.JE_LINE_NUM = line.JE_LINE_NUM
	--
	-- 01-NOV-00 EGARRETT Start(1)
	-- replaced translate with is_number function
	AND	is_number(NVL(gjl.reference_8,0)) = 1;

Line: 697

	SELECT	CODE_COMBINATION_ID
	INTO	p_code_combination_id
	FROM	GL_JE_LINES
	WHERE	JE_HEADER_ID = p_je_header_id
	AND	JE_LINE_NUM = line.JE_LINE_NUM;

Line: 703

	UPDATE  IGI_BUD_NY_BALANCES nyb
	SET	NEXT_YEAR_BUDGET =
		(SELECT	nyb.NEXT_YEAR_BUDGET +
			NVL(gjl.REFERENCE_8,0)
		 FROM	GL_JE_LINES gjl
	  	 WHERE	gjl.JE_HEADER_ID = p_je_header_id
		 AND	gjl.JE_LINE_NUM = line.JE_LINE_NUM
		-- 01-NOV-00 EGARRETT Start(2)
		 AND	is_number(NVL(gjl.reference_8,0)) = 1)
                /* AND  	translate(gjl.REFERENCE_8,
      '-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz\|*!"#$%^&*()_+. '
                       ,'-0123456789') = gjl.REFERENCE_8
        	 AND             ( (gjl.REFERENCE_8 like '-%' and
			   instr(gjl.REFERENCE_8,'-') <> 0)
			   OR ( instr(gjl.REFERENCE_8,'-') = 0))) */
		-- 01-NOV-00 EGARRETT End(2)
	WHERE	SET_OF_BOOKS_ID = p_set_of_books_id
	AND	BUDGET_VERSION_ID = p_budget_version_id
	AND	CURRENCY_CODE = p_currency_code
	AND	CODE_COMBINATION_ID = p_code_combination_id
	AND	PERIOD_NAME = p_period_name;

Line: 748

	SELECT NULL
	INTO   l_line_reference_3
	FROM   sys.dual
	WHERE  line.reference_3 <> 'MANUAL'
	AND    line.reference_3 is not null
	AND    line.reference_3 not in (
			SELECT  profile_code
			FROM   	igi_bud_profile_codes
			WHERE  	set_of_books_id = p_set_of_books_id
			AND    	sysdate >= nvl(start_date_Active,sysdate-1)
			AND	sysdate <= nvl(end_date_active,sysdate+1));