[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT description
, start_date_active
, end_date_active
FROM fnd_flex_values_vl
WHERE flex_value_set_id = cp_vs_id
AND flex_value = cp_code;
SELECT flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = cp_vs_id;
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_coa_id, p_segment;
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_information2 = cp_co_vs_id
AND CC.org_information3 = cp_co
AND CC.org_information4 IS NULL
AND CC.org_information5 IS NULL;
SELECT chart_of_accounts_id
FROM gl_code_combinations
WHERE code_combination_id = cp_ccid;
SELECT org_information2
, org_information3
, org_information4
, org_information5
FROM hr_organization_information
WHERE organization_id = p_org_id
AND org_information_context = 'Company Cost Center';
SELECT 1
FROM hr_organization_information
WHERE organization_id = p_org_id
--AND org_information_context = 'HR_ORG';
SELECT org_information2
,org_information_id
,object_version_number
FROM hr_organization_information
WHERE organization_id = p_org_id
AND org_information_context = 'CLASS'
AND org_information1 = 'CC';
SELECT organization_id
FROM hr_all_organization_units
WHERE name = p_org_name
AND business_group_id = p_bg_id;
SELECT SUBSTRB(REPLACE(REPLACE(REPLACE(REPLACE(g_org_name_format
,'$COC'
,p_co
)
,'$CCC'
,p_cc
)
,'$CON'
,p_co_desc
)
,'$CCN'
,p_cc_desc
)
,1
,g_org_name_max_length
)
INTO p_org_name
FROM dual;
OPEN c_list FOR 'SELECT coa_id, co, co_vs_id, cc, cc_vs_id FROM '||g_temp_table_name;
EXECUTE IMMEDIATE 'UPDATE '||g_temp_table_name||
' SET co_desc = :1' ||
', cc_desc = :2' ||
', co_vs_name = :3' ||
', cc_vs_name = :4' ||
', org_name = :5' ||
', org_start_date = :6'||
' WHERE co = :7' ||
' AND co_vs_id = :8' ||
' AND cc = :9' ||
' AND cc_vs_id = :10'
USING l_co_desc
, l_cc_desc
, l_co_vs_name
, l_cc_vs_name
, l_org_name
, l_org_start_date
, l_co
, l_co_vs_id
, l_cc
, l_cc_vs_id;
function update_org_name_date(
p_mode in varchar2
,p_sync_org_name in varchar2 default 'N'
,p_sync_org_dates in varchar2 default 'N'
) return number is
--
-- Local variables
l_rowid rowid;
l_proc varchar2(72) := g_package||'.update_org_name_date';
open c_ref for ' select units.rowid, units.name, units.organization_id, units.date_from, units.date_to, '||
' t.co_vs_id, t.co, t.cc_vs_id, t.cc'||
' from '|| g_temp_table_name ||' t , '||
' hr_organization_information cc,'||
' hr_organization_information class,'||
' hr_all_organization_units units'||
' where to_char(t.co_vs_id) = cc.org_information2 '||
' and t.co = cc.org_information3 '||
' and to_char(t.cc_vs_id) = cc.org_information4 '||
' and t.cc = cc.org_information5 '||
' and 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'''||
' for update of units.name nowait ';
update hr_all_organization_units units
set units.name = l_org_name,
units.date_from = l_org_start_date,
units.date_to = l_org_end_date
where units.rowid = l_rowid;
update hr_all_organization_units_tl
set name= l_org_name
where organization_id = l_org_id
and language in (language, source_lang);
update hr_all_organization_units units
set units.name = l_org_name
where units.rowid = l_rowid;
update hr_all_organization_units_tl
set name=l_org_name
where organization_id = l_org_id
and language in (language, source_lang);
update hr_all_organization_units units
set units.date_from = l_org_start_date,
units.date_to = l_org_end_date
where units.rowid = l_rowid;
writelog('Updated '||l_updrowcount||' organization details.','N');
end update_org_name_date;
select distinct cc.org_information3 company_code
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 is not null
and cc.org_information3 is not null
and cc.org_information3 = nvl(cp_co, cc.org_information3)
and cc.org_information4 is null
and cc.org_information5 is null
and units.business_group_id = cp_business_group_id
and cc.org_information3 in (
select flex_value
from fnd_flex_values_vl
where flex_value_set_id = cp_co_vs_id);
select ffv.flex_value cost_center_code
from fnd_flex_values_vl ffv
where ffv.flex_value_set_id = cp_cc_vs_id
order by ffv.flex_value;
execute immediate 'insert into '||g_temp_table_name||
' ( coa_id'||
' , co'||
' , co_vs_id'||
' , cc'||
' , cc_vs_id'||
' ) values'||
' ( :1, :2, :3, :4, :5 )'
using p_coa_id
,cr_company.company_code
,l_co_vs_id
,cr_cost_center.cost_center_code
,l_cc_vs_id;
writelog('Inserted '||l_rowcount||' Company-CostCenter records into temp','Y');
execute immediate 'insert into '||g_temp_table_name||
' ( coa_id'||
' , co'||
' , co_vs_id'||
' , cc'||
' , cc_vs_id )'||
' select distinct'||
' '||p_coa_id||
', '||l_co_seg||
', '||l_co_vs_id||
', '||l_cc_seg||
', '||l_cc_vs_id||
' from gl_code_combinations'||
' where summary_flag = ''N'''||
' and chart_of_accounts_id = :1'||
' and '||l_co_seg||' = nvl(:2,'||l_co_seg||')'
using p_coa_id, p_co;
writelog('Inserted '||l_rowcount||' Company-CostCenter records from GLCC into temp','Y');
execute immediate 'delete from '||g_temp_table_name||
' where (co,to_char(co_vs_id)) not in ('||
' select distinct cc.org_information3, cc.org_information2'||
' 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 units.business_group_id = :1 '||
' and cc.org_information4 is null'||
' and cc.org_information5 is null )'
using p_business_group_id;
writelog('Deleted '||l_delrowcount||' missing Company-ORG records from temp','Y');
function delete_sync_orgs_from_temp(
p_mode in varchar2
,p_business_group_id in number
,p_coa_id in number
,p_rowcount in number -- Added for bug4346785.
) return number is
--
-- Local variables
--
l_retcode number := 0;
l_proc varchar2(50) := g_package||'.delete_sync_orgs_from_temp';
EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
' WHERE (co,cc) IN ('||
' SELECT DISTINCT '||l_co_seg||','||l_cc_seg||' FROM gl_code_combinations'||
' WHERE chart_of_accounts_id = :1 AND company_cost_center_org_id IS NOT NULL)'
USING p_coa_id;
writelog('Deleted '||l_delrowcount||' already synchronized records from temp table','Y');
EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
' WHERE (TO_CHAR(co_vs_id), co'||
', TO_CHAR(cc_vs_id), cc) IN ('||
' SELECT CC.org_information2, CC.org_information3,'||
' CC.org_information4, CC.org_information5'||
' 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 UNITS.business_group_id = :1 )'
USING p_business_group_id;
writelog('Deleted '||l_del1rowcount||' ORG already existing records from temp table','Y');
END delete_sync_orgs_from_temp;
l_delete_synced_orgs_flag BOOLEAN := FALSE;
SELECT DISTINCT CC.org_information3 company_code
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 IS NOT NULL
AND CC.org_information3 IS NOT NULL
AND CC.org_information3 = NVL(cp_co, CC.org_information3)
AND CC.org_information4 IS NULL
AND CC.org_information5 IS NULL
AND UNITS.business_group_id = cp_business_group_id
AND CC.org_information3 IN ( SELECT flex_value
FROM fnd_flex_values_vl
WHERE flex_value_set_id = cp_co_vs_id
);
SELECT FFV.flex_value cost_center_code
FROM fnd_flex_values_vl FFV
WHERE FFV.flex_value_set_id = cp_cc_vs_id
ORDER BY FFV.flex_value;
EXECUTE IMMEDIATE 'INSERT INTO '||g_temp_table_name||
' ( coa_id'||
' , co'||
' , co_vs_id'||
' , cc'||
' , cc_vs_id'||
' ) VALUES'||
' ( :1, :2, :3, :4, :5 )'
USING p_coa_id
, cr_company.company_code
, l_co_vs_id
, cr_cost_center.cost_center_code
, l_cc_vs_id;
writelog('Inserted '||l_rowcount||' Company-CostCenter records into temp','Y');
l_delete_synced_orgs_flag := true;
EXECUTE IMMEDIATE 'INSERT INTO '||g_temp_table_name||
' ( coa_id'||
' , co'||
' , co_vs_id'||
' , cc'||
' , cc_vs_id )'||
' SELECT DISTINCT'||
' '||p_coa_id||
', '||l_co_seg||
', '||l_co_vs_id||
', '||l_cc_seg||
', '||l_cc_vs_id||
' FROM gl_code_combinations'||
' WHERE company_cost_center_org_id IS NULL'||
' AND summary_flag = ''N'''||
' AND chart_of_accounts_id = :1'||
' AND '||l_co_seg||' = NVL(:2,'||l_co_seg||')'
USING p_coa_id, p_co;
writelog('Inserted '||l_rowcount||' Company-CostCenter records from GLCC into temp','Y');
EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
' WHERE (co,TO_CHAR(co_vs_id)) NOT IN ('||
' SELECT DISTINCT CC.org_information3, CC.org_information2'||
' 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 UNITS.business_group_id = :1 '||
' AND CC.org_information4 IS NULL'||
' AND CC.org_information5 IS NULL )'
USING p_business_group_id;
writelog('Deleted '||l_delrowcount||' missing Company-ORG records from temp','Y');
OPEN c_ref FOR ' SELECT UNITS.ROWID, UNITS.name, UNITS.date_from, UNITS.date_to, '||
' t.CO_VS_ID, t.CO, t.CC_VS_ID, t.CC'||
' FROM '|| g_temp_table_name ||' t , '||
' hr_organization_information CC,'||
' hr_organization_information CLASS,'||
' hr_all_organization_units UNITS'||
' WHERE TO_CHAR(t.co_vs_id) = CC.org_information2 '||
' AND t.co = CC.org_information3 '||
' AND TO_CHAR(t.cc_vs_id) = CC.org_information4 '||
' AND t.cc = CC.org_information5 '||
' AND 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'''||
' FOR UPDATE OF UNITS.name NOWAIT ';
UPDATE hr_all_organization_units UNITS
SET UNITS.name = l_org_name,
UNITS.date_from = l_org_start_date,
UNITS.date_to = l_org_end_date
WHERE UNITS.rowid = l_rowid;
UPDATE hr_all_organization_units UNITS
SET UNITS.name = l_org_name
WHERE UNITS.rowid = l_rowid;
UPDATE hr_all_organization_units UNITS
SET UNITS.date_from = l_org_start_date,
UNITS.date_to = l_org_end_date
WHERE UNITS.rowid = l_rowid;
writelog('Updated '||l_updrowcount||' organization details.','N');
writelog('No organization details were updated.','N');
END IF; -- Phase 2a update org name and dates
IF l_retcode = 0 and l_delete_synced_orgs_flag THEN
hr_utility.set_location(l_proc, 50);
EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
' WHERE (co,cc) IN ('||
' SELECT DISTINCT '||l_co_seg||','||l_cc_seg||' FROM gl_code_combinations'||
' WHERE chart_of_accounts_id = :1 AND company_cost_center_org_id IS NOT NULL)'
USING p_coa_id;
writelog('Deleted '||l_delrowcount||' already synchronized records from temp table','Y');
EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
' WHERE (TO_CHAR(co_vs_id), co'||
', TO_CHAR(cc_vs_id), cc) IN ('||
' SELECT CC.org_information2, CC.org_information3,'||
' CC.org_information4, CC.org_information5'||
' 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 UNITS.business_group_id = :1 )'
USING p_business_group_id;
writelog('Deleted '||l_del1rowcount||' ORG already existing records from temp table','Y');
EXECUTE IMMEDIATE 'INSERT INTO '||g_temp_table_name||
' ( coa_id'||
' , co'||
' , co_vs_id'||
' , cc'||
' , cc_vs_id'||
' , ccid'||
' , org_id )'||
' SELECT chart_of_accounts_id'||
', '||l_co_seg||
', '||l_co_vs_id||
', '||l_cc_seg||
', '||l_cc_vs_id||
', code_combination_id'||
', NULL'||
' FROM gl_code_combinations'||
' WHERE company_cost_center_org_id IS NULL'||
' AND summary_flag = ''N'''||
' AND chart_of_accounts_id = :1'
USING p_coa_id;
writelog('Inserted '||l_rowcount||' records to synchronize into temp table','Y');
EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
' WHERE (TO_CHAR(co_vs_id), co'||
', TO_CHAR(cc_vs_id), cc) NOT IN'||
' (SELECT DISTINCT CC.org_information2'||
', CC.org_information3'||
', CC.org_information4'||
', CC.org_information5'||
' 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 IS NOT NULL'||
' AND CC.org_information3 IS NOT NULL'||
' AND CC.org_information4 IS NOT NULL'||
' AND CC.org_information5 IS NOT NULL'||
' AND UNITS.business_group_id = :1)'
USING p_business_group_id;
writelog('Deleted '||l_delrowcount||' missing Company Cost Center ORG records from temp table','Y');
EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
' WHERE (TO_CHAR(co_vs_id), co'||
', TO_CHAR(cc_vs_id), cc) IN'||
' (SELECT CC.org_information2'||
', CC.org_information3'||
', CC.org_information4'||
', CC.org_information5'||
' 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 IS NOT NULL'||
' AND CC.org_information3 IS NOT NULL'||
' AND CC.org_information4 IS NOT NULL'||
' AND CC.org_information5 IS NOT NULL'||
' AND UNITS.business_group_id = :1'||
' GROUP BY CC.org_information2'||
', CC.org_information3'||
', CC.org_information4'||
', CC.org_information5'||
' HAVING COUNT(*) > 1)'
USING p_business_group_id;
writelog('Deleted '||l_del1rowcount||' duplicate ORG records from temp table','Y');
OPEN c_list FOR 'SELECT org_name' ||
', cc' ||
', cc_desc' ||
', co' ||
', co_desc' ||
', cc_vs_name' ||
', co_vs_name' ||
', org_start_date' ||
' FROM '||g_temp_table_name||
' ORDER BY cc,co';
OPEN c_list FOR 'SELECT '||l_co_seg||', '||l_cc_seg||
' 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_information3 = :2'||
' AND CC.org_information4 = :3'||
' AND CC.org_information5 = :4'
USING TO_CHAR(l_co_vs_id), l_co, TO_CHAR(l_cc_vs_id), l_cc;
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 with Org Id:'||l_org_id||
' for CCID:'||p_ccid, 'Y');
hr_utility.set_location('Updated GL Code Combinations with Org Id:'||
l_org_id||' for CCID:'||p_ccid, 150);
l_updated_by NUMBER := 0;
EXECUTE IMMEDIATE 'UPDATE '||g_temp_table_name||' TEMP'||
' SET TEMP.org_id ='||
' (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 = TO_CHAR(TEMP.co_vs_id)'||
' AND CC.org_information3 = TEMP.co'||
' AND CC.org_information4 = TO_CHAR(TEMP.cc_vs_id)'||
' AND CC.org_information5 = TEMP.cc'||
' AND UNITS.business_group_id = :1)'
USING p_business_group_id;
writelog('Updated '||SQL%ROWCOUNT||' ORGs to temp table','Y');
EXECUTE IMMEDIATE 'UPDATE gl_code_combinations GCC'||
' SET ( GCC.company_cost_center_org_id'||
', GCC.last_update_date'||
', GCC.last_updated_by) ='||
' (SELECT SYNC.org_id'||
', TRUNC(SYSDATE)'||
','||l_updated_by||
' FROM '||g_temp_table_name||' SYNC'||
' WHERE SYNC.ccid = GCC.code_combination_id'||
' AND SYNC.org_id IS NOT NULL)'||
' WHERE GCC.company_cost_center_org_id IS NULL'||
' AND GCC.code_combination_id IN (SELECT ccid'||
' FROM '||g_temp_table_name||
' WHERE org_id IS NOT NULL)';
writelog('Updated '||SQL%ROWCOUNT||' ORGs to GL Code Combinations','Y');
OPEN c_list FOR 'SELECT ccid, co, cc, org_id FROM '||g_temp_table_name;
l_retcode := update_org_name_date(
p_mode
,p_sync_org_name
,p_sync_org_dates
);
l_retcode := delete_sync_orgs_from_temp(
p_mode
,p_business_group_id
,p_coa_id
,l_rowcount -- Added for bug4346785.
);
OPEN c_list FOR 'SELECT coa_id, co, co_vs_id, cc, cc_vs_id FROM '||g_temp_table_name;
l_retcode := update_org_name_date(
p_mode
,p_sync_org_name
,p_sync_org_dates
);
SELECT 1
FROM user_triggers
WHERE trigger_name = 'HR_ALL_ORGANIZATION_UNITS_UTF8';
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'HR_GL_SYNC_DEBUG';
SELECT name
FROM hr_all_organization_units
WHERE organization_id = p_business_group_id;