APPS.PAY_JP_DIMENSION_GENERATOR_PKG SQL Statements

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

Line: 278

	select	route_id
	into	l_route_id
	from	ff_routes
	where	route_name = p_route_name;

Line: 359

		select	pay_balance_dimensions_s.nextval
		into	p_balance_dimension_id
		from	dual;

Line: 363

		insert into pay_balance_dimensions(
			BALANCE_DIMENSION_ID,
			DIMENSION_NAME,
			DATABASE_ITEM_SUFFIX,
			BUSINESS_GROUP_ID,
			LEGISLATION_CODE,
			DESCRIPTION,
			PAYMENTS_FLAG,
			DIMENSION_TYPE,
			EXPIRY_CHECKING_LEVEL,
			EXPIRY_CHECKING_CODE,
			FEED_CHECKING_TYPE,
			FEED_CHECKING_CODE,
			ROUTE_ID,
			DATABASE_ITEM_FUNCTION,
			DIMENSION_LEVEL,
			ASG_ACTION_BALANCE_DIM_ID,
			SAVE_RUN_BALANCE_ENABLED,
			PERIOD_TYPE,
			START_DATE_CODE)
		values(	p_balance_dimension_id,
			p_dimension_name,
			p_database_item_suffix,
			p_business_group_id,
			null,
			l_description,
			'N',
			l_dimension_type,
			l_expiry_checking_level,
			l_expiry_checking_code,
			null,
			null,
			l_route_id,
			'Y',
			'ASG',
			null,
			'N',
			l_period_type,
			l_start_date_code);

Line: 403

		update	pay_balance_dimensions
		set	description			= l_description,
			payments_flag			= 'N',
			dimension_type			= l_dimension_type,
			expiry_checking_level		= l_expiry_checking_level,
			expiry_checking_code		= l_expiry_checking_code,
			feed_checking_type		= null,
			feed_checking_code		= null,
			route_id			= l_route_id,
			database_item_function		= 'Y',
			dimension_level			= 'ASG',
			asg_action_balance_dim_id	= null,
			save_run_balance_enabled	= 'N',
			period_type			= l_period_type,
			start_date_code			= l_start_date_code
		where	balance_dimension_id = p_balance_dimension_id;

Line: 453

	select	route_id,
		optimizer_hint,
		text
	into	l_template_route_id,
		l_optimizer_hint,
		l_text
	from	ff_routes
	where	route_name = l_template_route_name;

Line: 469

	select	ff_routes_s.nextval
	into	p_route_id
	from	dual;

Line: 475

	insert into ff_routes(
		ROUTE_ID,
		ROUTE_NAME,
		USER_DEFINED_FLAG,
		DESCRIPTION,
		OPTIMIZER_HINT,
		TEXT)
	values(	p_route_id,
		l_route_name,
		'Y',
		l_description,
		l_optimizer_hint,
		l_text);

Line: 489

	insert into ff_route_context_usages(
		ROUTE_ID,
		CONTEXT_ID,
		SEQUENCE_NO)
	select	p_route_id,
		context_id,
		sequence_no
	from	ff_route_context_usages
	where	route_id = l_template_route_id;

Line: 499

	insert into ff_route_parameters(
		ROUTE_PARAMETER_ID,
		ROUTE_ID,
		DATA_TYPE,
		PARAMETER_NAME,
		SEQUENCE_NO)
	select	ff_route_parameters_s.nextval,
		p_route_id,
		data_type,
		parameter_name,
		sequence_no
	from	ff_route_parameters
	where	route_id = l_template_route_id;

Line: 547

		select	balance_dimension_id
		into	l_run_dimension_id
		from	pay_balance_dimensions
		where	dimension_name = '_ASG_RUN'
		and	legislation_code = 'JP';

Line: 554

	insert into pay_dimension_routes(
		BALANCE_DIMENSION_ID,
		PRIORITY,
		ROUTE_TYPE,
		ROUTE_ID,
		BALANCE_TYPE_COLUMN,
		RUN_DIMENSION_ID,
		OBJECT_VERSION_NUMBER)
	values(	p_balance_dimension_id,
		p_priority,
		p_route_type,
		l_route_id,
		l_balance_type_column,
		l_run_dimension_id,
		1);

Line: 585

	select	count(*)
	into	l_count
	from	pay_balance_dimensions
	where	replace(upper(dimension_name), ' ', '_') = replace(upper(p_dimension_name), ' ', '_')
	and	nvl(business_group_id, p_business_group_id) = p_business_group_id
	and	nvl(legislation_code, 'JP') = 'JP';

Line: 630

	select	count(*)
	into	l_count
	from	pay_balance_dimensions
	where	replace(upper(database_item_suffix), ' ', '_') = replace(upper(p_database_item_suffix), ' ', '_')
	and	nvl(business_group_id, p_business_group_id) = p_business_group_id
	and	nvl(legislation_code, 'JP') = 'JP';

Line: 768

procedure delete_dbi(
	p_defined_balance_id	in number,
	p_business_group_id	in number)
is
begin
	delete
	from	ff_fdi_usages_f
	where	formula_id in (
			select	distinct
				ff.formula_id
			from	ff_user_entities	u,
				ff_database_items	d,
				ff_fdi_usages_f		fdi,
				ff_formulas_f		ff
			where	u.creator_id = p_defined_balance_id
			and	u.creator_type in ('B', 'RB')
			and	d.user_entity_id = u.user_entity_id
			and	fdi.item_name = d.user_name
			and	fdi.usage = 'D'
			and	ff.formula_id = fdi.formula_id
			and	ff.effective_start_date = fdi.effective_start_date
			and	ff.effective_end_date = fdi.effective_end_date
			and	ff.business_group_id = p_business_group_id);

Line: 792

	delete
	from	ff_compiled_info_f
	where	formula_id in (
			select	distinct
				ff.formula_id
			from	ff_user_entities	u,
				ff_database_items	d,
				ff_fdi_usages_f		fdi,
				ff_formulas_f		ff
			where	u.creator_id = p_defined_balance_id
			and	u.creator_type in ('B', 'RB')
			and	d.user_entity_id = u.user_entity_id
			and	fdi.item_name = d.user_name
			and	fdi.usage = 'D'
			and	ff.formula_id = fdi.formula_id
			and	ff.effective_start_date = fdi.effective_start_date
			and	ff.effective_end_date = fdi.effective_end_date
			and	ff.business_group_id = p_business_group_id);

Line: 811

	delete
	from	ff_user_entities
	where	creator_id = p_defined_balance_id
	and	creator_type in ('B', 'RB');

Line: 815

end delete_dbi;

Line: 819

procedure delete_latest_balances(p_defined_balance_id in number)
is
begin
	delete
	from	pay_balance_context_values
	where	latest_balance_id in (
			select	latest_balance_id
			from	pay_assignment_latest_balances
			where	defined_balance_id = p_defined_balance_id
			union all
			select	latest_balance_id
			from	pay_assignment_latest_balances
			where	defined_balance_id = p_defined_balance_id
			union all
			select	latest_balance_id
			from	pay_latest_balances
			where	defined_balance_id = p_defined_balance_id);

Line: 837

	delete
	from	pay_assignment_latest_balances
	where	defined_balance_id = p_defined_balance_id;

Line: 841

	delete
	from	pay_person_latest_balances
	where	defined_balance_id = p_defined_balance_id;

Line: 845

	delete
	from	pay_latest_balances
	where	defined_balance_id = p_defined_balance_id;

Line: 848

end delete_latest_balances;

Line: 852

procedure update_balance_dimension(
	p_balance_dimension_id	in number,
	p_date_type		in varchar2,
	p_reset_date		in date,
	p_frequency_type	in varchar2,
	p_frequency		in number,
	p_exclude_reversal	in boolean,
	p_rebuild_package	in boolean default true)
is
	l_balance_dimension_id	number := p_balance_dimension_id;

Line: 871

	select	business_group_id,
		dimension_name,
		database_item_suffix
	into	l_business_group_id,
		l_dimension_name,
		l_database_item_suffix
	from	pay_balance_dimensions
	where	balance_dimension_id = p_balance_dimension_id;

Line: 880

	-- Only user defined dimension is allowed to be updated.
	--
	if l_business_group_id is null then
		fnd_message.set_name('PAY', 'PAY_JP_DIM_SEEDUPD_NOT_ALLOWED');

Line: 893

	-- Delete current DBIs
	--
	select	defined_balance_id,
		balance_type_id
	bulk collect
	into	l_defined_balance_ids,
		l_balance_type_ids
	from	pay_defined_balances
	where	balance_dimension_id = p_balance_dimension_id;

Line: 905

		-- Delete compiled info and DBIs.
		--
		delete_dbi(l_defined_balance_ids(i), l_business_group_id);

Line: 909

		-- Delete latest balances.
		-- No need to trash run balances which is not affected
		-- because those are ASG_RUN level balances.
		--
		delete_latest_balances(l_defined_balance_ids(i));

Line: 916

	delete
	from	pay_dimension_routes
	where	balance_dimension_id = p_balance_dimension_id;

Line: 982

end update_balance_dimension;

Line: 984

procedure update_balance_dimension(
	errbuf			out nocopy varchar2,
	retcode			out nocopy varchar2,
	p_balance_dimension_id	in varchar2,
	p_date_type		in varchar2,
	p_reset_date		in varchar2,
	p_frequency_type	in varchar2,
	p_frequency		in varchar2,
	p_exclude_reversal	in varchar2)
is
begin
--
  -- disable in progress of online patch
  if ad_zd.get_edition('PATCH') is not null then
  --
    fnd_message.set_name('FND','AD_ZD_DISABLED_FEATURE');

Line: 1006

	update_balance_dimension(
		p_balance_dimension_id	=> fnd_number.canonical_to_number(p_balance_dimension_id),
		p_date_type		=> p_date_type,
		p_reset_date		=> fnd_date.canonical_to_date(p_reset_date),
		p_frequency_type	=> p_frequency_type,
		p_frequency		=> fnd_number.canonical_to_number(p_frequency),
		p_exclude_reversal	=> (p_exclude_reversal = 'Y'));

Line: 1018

end update_balance_dimension;

Line: 1033

		select	def.defined_balance_id,
			def.business_group_id
		from	per_business_groups_perf	bg,
			pay_balance_dimensions		dim,
			pay_defined_balances		def
		where	bg.legislation_code = 'JP'
		and	dim.business_group_id = bg.business_group_id
		and	pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
		and	pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
		and	pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
		and	pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null
		and	def.balance_dimension_id = dim.balance_dimension_id;

Line: 1047

		select	dim.balance_dimension_id,
			dim.description
		from	per_business_groups_perf	bg,
			pay_balance_dimensions		dim
		where	bg.legislation_code = 'JP'
		and	dim.business_group_id = bg.business_group_id
		and	pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
		and	pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
		and	pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
		and	pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null;

Line: 1064

		select	upper(dim.expiry_checking_code)	expiry_checking_code,
			upper(dim.start_date_code)	start_date_code,
			min(dim.description)		description
		from	per_business_groups_perf	bg,
			pay_balance_dimensions		dim
		where	bg.legislation_code = 'JP'
		and	dim.business_group_id = bg.business_group_id
		and	(dim.expiry_checking_code is not null or dim.start_date_code is not null)
		and	pay_core_utils.get_parameter('DATE_TYPE', dim.description) is not null
		and	pay_core_utils.get_parameter('RESET_DATE', dim.description) is not null
		and	pay_core_utils.get_parameter('FREQUENCY_TYPE', dim.description) is not null
		and	pay_core_utils.get_parameter('FREQUENCY', dim.description) is not null
		group by
			dim.expiry_checking_code,
			dim.start_date_code;

Line: 1183

		-- Delete fdi/compiled/DBI info which references DBI with user defined dimension.
		--
		for l_rec in csr_def loop
			delete_dbi(l_rec.defined_balance_id, l_rec.business_group_id);

Line: 1189

		-- Delete PAY_DIMENSION_ROUTES
		--
		for l_rec in csr_dim loop
			delete
			from	pay_dimension_routes
			where	balance_dimension_id = l_rec.balance_dimension_id;

Line: 1197

		-- Delete FF_ROUTES
		--
		delete
		from	ff_routes
		where	(	route_name like 'JP\_ASG\_DP\_%\_BALANCE_DIMENSION%' escape '\'
			or	route_name like 'JP\_ASG\_DE\_%\_BALANCE_DIMENSION%' escape '\')
		and	user_defined_flag = 'Y';

Line: 1209

			update_balance_dimension(
				p_balance_dimension_id	=> l_rec.balance_dimension_id,
				p_date_type		=> pay_core_utils.get_parameter('DATE_TYPE', l_rec.description),
				p_reset_date		=> fnd_date.canonical_to_date(pay_core_utils.get_parameter('RESET_DATE', l_rec.description)),
				p_frequency_type	=> pay_core_utils.get_parameter('FREQUENCY_TYPE', l_rec.description),
				p_frequency		=> fnd_number.canonical_to_number(pay_core_utils.get_parameter('FREQUENCY', l_rec.description)),
				p_exclude_reversal	=> (pay_core_utils.get_parameter('EXCLUDE_REVERSAL', l_rec.description) = 'Y'),
				p_rebuild_package	=> false);