[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_login number := 0;
select parameter_value
from pay_action_parameters
where parameter_name = 'HR_GL_SYNC_DEBUG';
l_stmt := 'select ccid, org_id from '||p_tablename;
l_stmt := 'select ccid, org_id, chart_of_accounts_id,
company, company_vs, cost_center, cc_vs,
business_group_id from '||p_tablename;
SELECT name INTO l_bg_name
from PER_BUSINESS_GROUPS
WHERE business_group_id = p_bgid;
open c_bg_list for 'select distinct rep.business_group_id,
per.name
from '||p_hr_cc_reporting_temp ||' rep,
per_business_groups per
where rep.business_group_id=per.business_group_id';
'select distinct reptemp.company_value_set,
comp.flex_value_set_name,
reptemp.cc_value_set,
cc.flex_value_set_name,
reptemp.company,
reptemp.cost_center,
reptemp.org_name,
compname.description,
ccname.description,
reptemp.org_start_date
from fnd_flex_value_sets comp,
fnd_flex_value_sets cc,
fnd_flex_values_vl compname,
fnd_flex_values_vl ccname,
(select distinct company_value_set,
cc_value_set,
company,
cost_center,
org_name ,
org_start_date
from '||p_hr_cc_reporting_temp|| ') reptemp
where reptemp.company_value_set = comp.flex_value_set_id
and reptemp.cc_value_set = cc.flex_value_set_id
and comp.flex_value_set_id = compname.flex_value_set_id
and cc.flex_value_set_id = ccname.flex_value_set_id
and reptemp.company = compname.flex_value
and reptemp.cost_center = ccname.flex_value ';
open c_list for 'select vs.flex_value_set_id,
vs.flex_value_set_name
from fnd_id_flex_segments_vl seg
, fnd_flex_value_sets vs
where upper(seg.id_flex_code) = ''GL#''
and seg.application_id = 101
and seg.flex_value_set_id = vs.flex_value_set_id
and seg.enabled_flag = ''Y''
and id_flex_num = :1
and application_column_name = :2'
using p_chart_of_accounts_id, p_segment;
select organization_id
from hr_all_organization_units
where name = l_org_name
and business_group_id = p_bg_id;
select org_information2, org_information_id, object_version_number
from hr_organization_information ori
where organization_id = p_organization_id
and org_information_context = 'CLASS'
and org_information1 = p_classification;
select org_information2,
org_information3,
org_information4,
org_information5
from hr_organization_information ori
where organization_id = p_organization_id
and org_information_context = p_info_type;
select summary_flag
into l_summary_flag
from gl_code_combinations
where code_combination_id = p_ccid;
open c_list for 'select units.organization_id, units.name
from hr_all_organization_units_tl units,
hr_organization_information class,
hr_organization_information cc
where units.organization_id = class.organization_id
and class.org_information_context=''CLASS''
and class.org_information1=''CC''
and class.organization_id = cc.organization_id
and cc.org_information_context = ''Company Cost Center''
and cc.org_information2 = :1
and cc.org_information4 = :2
and cc.org_information3 = :3
and cc.org_information5 = :4 '
using to_char(p_company_vs), to_char(p_cc_vs), p_company,
p_cost_center;
open c_list for 'select description ,start_date_active
from fnd_flex_values_vl
where flex_value_set_id = :1
and flex_value = :2 '
using p_company_vs, p_company;
open c_list for 'select description ,start_date_active
from fnd_flex_values_vl
where flex_value_set_id = :1
and flex_value = :2'
using p_cc_vs, p_cost_center;
select substrb(
REPLACE(
REPLACE(
REPLACE(
REPLACE(l_org_name_format,'$COC', p_company),
'$CCC', p_cost_center),
'$CON', l_company_desc),
'$CCN', l_cc_desc), 1, g_org_name_length)
into l_org_name
from dual;
** Update the info type and link the org to the current cost center.
** if the org info type is present with data then (D)
** Derive an unique org name and create a new org with classification as required.
** if we don't have an org classification then (E)
** Create the Company Cost Center Org classification and info type.
** Add the HR_ORG classification if the profile indicates it's required and the
** organization does not have it already.
*/
hr_utility.set_location(l_proc,180);
select distinct units.business_group_id
from hr_all_organization_units units,
hr_organization_information class,
hr_organization_information cc
where units.organization_id = class.organization_id
and class.org_information_context='CLASS'
and class.org_information1='CC'
and class.organization_id = cc.organization_id
and cc.org_information_context = 'Company Cost Center'
and cc.org_information3 = p_company
and cc.org_information2 = to_char(p_companyvs)
and cc.org_information4 is null
and cc.org_information5 is null;
select distinct chart_of_accounts_id
from gl_sets_of_books --Bug 3264485
where chart_of_accounts_id = p_coa
or p_coa is null ; -- Fix for Bug 2875915
select description, start_date_active
from fnd_flex_values_vl
where flex_value_set_id= p_vs
and p_segment = flex_value;
open c_list2 for 'SELECT /*+ORDERED USE_NL(gcc)*/
distinct gcc.'||l_company_segment||' company,
gcc.'||l_cc_segment ||' cost_center
FROM gl_code_combinations gcc
WHERE gcc.company_cost_center_org_id is null
AND gcc.summary_flag = ''N''
AND gcc.chart_of_accounts_id = '||l_coas.chart_of_accounts_id||
'AND gcc.'||l_company_segment||' is not null
AND gcc.'||l_cc_segment ||' is not null';
l_stmt := 'INSERT INTO '||p_hr_cc_reporting_temp ||'
(cc_value_set,
company_value_set,
chart_of_accounts_id,
company,
cost_center,
business_group_id,
company_name,
cost_center_name,
org_start_date)
VALUES (:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
greatest(nvl(:9 ,to_date(''01/01/1900'',''DD/MM/RRRR'')),
nvl(:10 ,to_date(''01/01/1900'',''DD/MM/RRRR''))))';
open c_list for 'select count(*) from '||p_hr_cc_reporting_temp;
** Delete records from the working table which are for companies which do
** not exist in the business group. Because GL CC table spans multiple
** business groups you need to define the companies in the appropriate BG
** first(a manual setup step) and then run sync orgs. This enables you to
** run the sync orgs process for a business group and only pull in those
** company cost centers which relate to this business group (i.e those
** which are for companies already defined in this business group.
**
** A possible enhancement here - instead of inserting all the data and then
** deleting some, merge this into a single insert which only inserts cost
** centers for companies in this business group. We probably should not
** implement this enhancement as it would prevent us from determining if the
** temporary table was empty because there are no GL cost centers to process
** or because the setup is incorrect and no companies have been defined in
** the current BG.
*/
l_stmt := 'delete from '||p_hr_cc_reporting_temp||' rep
where (company,company_value_set) not in
( select distinct cc.org_information3,
cc.org_information2
from hr_all_organization_units units,
hr_all_organization_units_tl unitstl,
hr_organization_information class,
hr_organization_information cc
where units.organization_id = class.organization_id
and units.organization_id = unitstl.organization_id
and class.org_information_context=''CLASS''
and class.org_information1=''CC''
and class.organization_id = cc.organization_id
and cc.org_information_context = ''Company Cost Center''
and units.business_group_id = :1
and cc.org_information4 is null
and cc.org_information5 is null)';
** We have populated the temp table and deleted records from it
** if no company organization has been defined in the business
** group. Now check the rows in the temp table. If we don't
** have any tell the user.
*/
open c_list for 'select count(*) from '||p_hr_cc_reporting_temp;
l_stmt := 'UPDATE '||p_hr_cc_reporting_temp||' rep set (org_name) =
(select substrb(REPLACE(
REPLACE(
REPLACE(
REPLACE(:l_org_name_format,''$COC'',
rep.company),
''$CCC'', rep.cost_center),
''$CON'', rep.company_name),
''$CCN'', rep.cost_center_name), 1,
:g_org_name_length)
from dual)';
l_stmt := 'update '||p_hr_cc_reporting_temp||' rep
set org_name = substrb(org_name, 1, '||
to_char(g_org_name_length)||'-length(''-''||
company_value_set||
''-''||cc_value_set))
||''-''||company_value_set ||''-''||cc_value_set
where rep.org_name in
(select distinct a.org_name
from '||p_hr_cc_reporting_temp||' a, '||p_hr_cc_reporting_temp||' b
where a.org_name = b.org_name and
(a.cc_value_set <> b.cc_value_set
or a.company_value_set<>b.company_value_set))';
** If the start date is '01/01/1900' then update it to default
** date 01/01/1990
*/
execute immediate 'UPDATE '||p_hr_cc_reporting_temp||' rep
set (org_start_date) = to_date(''01/01/1990'',''DD/MM/RRRR'')
Where to_char(org_start_date,''DD/MM/RRRR'') = (''01/01/1900'')';
select distinct chart_of_accounts_id
from gl_sets_of_books --Bug 3264485
where chart_of_accounts_id = p_coa
or p_coa is null ; -- Fix for Bug 2875915
** to be inserted into the temp table. i.e. if l_company holds the string
** 'SEGMENT1' then the value from GL_CODE_COMBINATIONS.SEGMENT1 is
** inserted into the tmp table.
*/
execute immediate 'insert into '||p_hr_sync_temp ||'
(ccid,
chart_of_accounts_id,
company,
company_vs,
cost_center,
cc_vs)
select code_combination_id,
chart_of_accounts_id,'||
l_company ||', '||l_company_vs||', '||
l_cost_center||', '||l_cc_vs||'
from gl_code_combinations
where company_cost_center_org_id is null
and summary_flag = ''N''
and chart_of_accounts_id = :1'
using l_coas.chart_of_accounts_id;
writelog('Inserted '||sql%rowcount||' records for COA = '||
l_coas.chart_of_accounts_id,'D');
l_stmt := 'delete from '||p_hr_sync_temp||' rep
where (company,company_vs) not in
( select distinct cc.org_information3,
cc.org_information2
from hr_all_organization_units units,
hr_all_organization_units_tl unitstl,
hr_organization_information class,
hr_organization_information cc
where units.organization_id = class.organization_id
and units.organization_id = unitstl.organization_id
and class.org_information_context=''CLASS''
and class.org_information1=''CC''
and class.organization_id = cc.organization_id
and cc.org_information_context = ''Company Cost Center''
and units.business_group_id = :1
and cc.org_information4 is null
and cc.org_information5 is null)';
open c_list for 'select count(*) from '||p_hr_sync_temp;
(select sync.ccid,
units.organization_id
from '||p_hr_sync_temp ||' sync ,
hr_all_organization_units units,
hr_organization_information class,
hr_organization_information cc
where units.organization_id = class.organization_id
and units.business_group_id = '||p_bgid||'
and class.org_information_context=''CLASS''
and class.org_information1=''CC''
and class.organization_id = cc.organization_id
and cc.org_information_context = ''Company Cost Center''
and cc.org_information2 = to_char(sync.company_vs)
and cc.org_information3 = sync.company
and cc.org_information4 = to_char(sync.cc_vs)
and cc.org_information5 = sync.cost_center)';
For the last two cases update the GCC table with this org_id.
---------------------------------------------------------------------*/
function synchronizeMode(p_mode in varchar2,
p_hr_sync_temp in varchar2,
p_hr_sync_ccid in varchar2,
p_start_date in date,
p_bgid in number,
p_schema in varchar2,
p_coa in number)
return NUMBER is
l_proc VARCHAR2(50) := g_package||'.synchronizeMode';
l_last_update_login number := 0;
** Update the GL code combinations table and set the org ID FK
** to the corresponding company cost center org.
*/
l_stmt := 'update /*+ORDERED USE_NL(gcc)*/ gl_code_combinations gcc
set (company_cost_center_org_id,
last_update_date,
last_updated_by ) =
(select sync.org_id,
sysdate, '||
l_last_update_login||
' from '||p_hr_sync_ccid ||' sync
where gcc.code_combination_id = sync.ccid)
where gcc.company_cost_center_org_id is null
and gcc.code_combination_id in
(select ccid from '||p_hr_sync_ccid ||')';
writelog('Updated '||sql%rowcount||' rows in GL_CODE_COMBINATIONS','D');
execute immediate 'delete from '||p_hr_sync_temp ||'
where ccid in
(select code_combination_id
from gl_code_combinations
where company_cost_center_org_id is not null
and last_update_date >= :1)'
using p_start_date;
writelog('deleted '||sql%rowcount||' rows from '||p_hr_sync_temp||
' where org_id is not null','D');
open c_list for 'select count(*) from '||p_hr_sync_temp;
open c_list for 'select distinct company,
cost_center,
company_vs,
cc_vs
from '||p_hr_sync_temp;
execute immediate 'update '||p_hr_sync_temp ||'
set (org_id) = :1
where company =:2 and cost_center=:3
and company_vs = :4 and cc_vs = :5'
using l_org_id, l_company, l_cost_center,
l_temp_compvs, l_temp_ccvs;
** now update GL_CODE_COMBINATIONS and set the org ID FK.
*/
l_stmt := 'update gl_code_combinations gcc
set (company_cost_center_org_id,
last_update_date,
last_updated_by) =
(select org_id,
sysdate, '||
fnd_global.user_id||
' from '||p_hr_sync_temp||' sync
where sync.ccid = gcc.code_combination_id
and sync.org_id <> -1
and sync.org_id is not null)
where gcc.code_combination_id in
(select ccid
from '||p_hr_sync_temp||'
where org_id is not null
and org_id <> -1)';
writelog('Updated '||sql%rowcount||
' rows in GL Code combinations with newly created Org ids','D');
select chart_of_accounts_id
into l_chart_of_accounts_id
from gl_code_combinations
where code_combination_id = p_ccid;
open c_list for 'select '||l_company_segment||', '||
l_cc_segment ||'
from gl_code_combinations
where code_combination_id =:1'
using p_ccid;
open c_list for 'select units.organization_id
from hr_all_organization_units units,
hr_organization_information class,
hr_organization_information cc
where units.organization_id = class.organization_id
and class.org_information_context=''CLASS''
and class.org_information1=''CC''
and class.organization_id = cc.organization_id
and cc.org_information_context = ''Company Cost Center''
and cc.org_information2 = :1
and cc.org_information4 = :2
and cc.org_information3 = :2
and cc.org_information5 = :4 '
using to_char(l_company_vs), to_char(l_cc_vs),
l_company, l_cost_center;
execute immediate 'update gl_code_combinations
set company_cost_center_org_id=:1,
last_update_date = sysdate,
last_updated_by = :2
where code_combination_id =:3'
using l_org_id, fnd_global.user_id, p_ccid;
writelog('Updated GL_CODE_COMBINATIONS. Set Company_cost_center_org_id = '||
l_org_id ||' where code_Combination_id = '||p_ccid,'D');
select 1
from user_triggers
where trigger_name='HR_ALL_ORGANIZATION_UNITS_UTF8';