批量打开库存会计期SQL代码,OracleEBS代码库,德仔网

代码语言

代码分类

【OracleEBS】批量打开库存会计期SQL

作者:Dezai.CN / 发布于2013/4/8/ 2948

由于系统界面中只能一次性打开一个库存组织下的一个会计期，如果需要打开大量的库存会计期时费时又费力，应一个业务顾问朋友的要求，写了一段SQL脚本来实现将账簿下所有OU下的所有库存组织的会计期都打开：


	DECLARE
	 l_last_scheduled_close_date DATE;
	 l_le_sysdate DATE;
	 &mdash;
	 l_prior_period_open BOOLEAN;
	 l_new_acct_period_id NUMBER;
	 l_duplicate_open_period BOOLEAN;
	 l_commit_completed BOOLEAN;
	 l_return_status VARCHAR2(1);
	BEGIN
	 &mdash; operation units
	 FOR ou IN (SELECT organization_id org_id
	 FROM hr_operating_units
	 WHERE set_of_books_id = fnd_profile.VALUE(&lsquo;GL_SET_OF_BKS_ID&rsquo;) &mdash; 62
	 ) LOOP
	 l_le_sysdate := inv_le_timezone_pub.get_le_sysdate_for_ou(ou.org_id);
	 &mdash; inventory organization
	 FOR org IN (SELECT organization_id
	 FROM org_organization_definitions
	 WHERE operating_unit = ou.org_id
	 &ndash;AND organization_id = 458
	 ORDER BY organization_id) LOOP
	 SELECT nvl(MAX(schedule_close_date), l_le_sysdate)
	 INTO l_last_scheduled_close_date
	 FROM org_acct_periods
	 WHERE organization_id = org.organization_id;
	 FOR ps IN (SELECT sob.period_set_name,
	 sob.accounted_period_type,
	 ps.period_name,
	 ps.period_year,
	 ps.period_num period_number,
	 ps.end_date
	 FROM gl_sets_of_books sob, gl_period_statuses ps
	 WHERE sob.set_of_books_id = fnd_profile.VALUE(&lsquo;GL_SET_OF_BKS_ID&rsquo;) &mdash; 62
	 AND sob.set_of_books_id = ps.set_of_books_id
	 AND ps.application_id = 401
	 AND ps.adjustment_period_flag = &lsquo;N&rsquo;
	 AND (ps.period_name, ps.period_year) NOT IN
	 (SELECT period_name, period_year
	 FROM org_acct_periods
	 WHERE organization_id = org.organization_id)
	 AND ps.created_by <> 0
	 ORDER BY ps.period_year, ps.effective_period_num) LOOP
	 &mdash; open the period
	 cst_accountingperiod_pub.open_period(p_api_version => 1.0,
	 p_org_id => org.organization_id,
	 p_user_id => fnd_global.user_id,
	 p_login_id => fnd_global.login_id,
	 p_acct_period_type => ps.accounted_period_type,
	 p_org_period_set_name => ps.period_set_name,
	 p_open_period_name => ps.period_name,
	 p_open_period_year => ps.period_year,
	 p_open_period_num => ps.period_number,
	 x_last_scheduled_close_date => l_last_scheduled_close_date,
	 p_period_end_date => ps.end_date,
	 x_prior_period_open => l_prior_period_open,
	 x_new_acct_period_id => l_new_acct_period_id,
	 x_duplicate_open_period => l_duplicate_open_period,
	 x_commit_complete => l_commit_completed,
	 x_return_status => l_return_status);
	 IF l_commit_completed THEN
	 dbms_output.put_line(&lsquo;Period: (&lsquo; || ps.period_name || &lsquo;) is success&rsquo;);
	 ELSE
	 dbms_output.put_line(&lsquo;Period: (&lsquo; || ps.period_name || &lsquo;) is failed&rsquo;);
	 GOTO stop_open;
	 END IF;
	 END LOOP;
	 END LOOP;
	 END LOOP;
	 <<stop_open>> NULL;
	END;

	

	运行这段脚本，需要在同一个Session中进行Apps的初始化，否则无法取到环境信息

试试其它关键字

　打开库存会计期　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)