[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rbs_version_id
into l_version_id
from pa_rbs_versions_b ver1
where ver1.rbs_header_id = p_rbs_header_id
and ver1.current_reporting_flag = 'Y';
SELECT name
INTO l_element_name
FROM Pa_rbs_versions_tl
WHERE rbs_version_id = p_resource_source_id
AND language = userenv('LANG');
SELECT lk.meaning
INTO l_element_name
from hr_lookups lk, per_person_types per
where lk.lookup_type = 'PERSON_TYPE'
and per.system_person_type in ('EMP', 'CWK')
and per.system_person_type = lk.lookup_code
and per.business_group_id = 0
and per.person_type_id = p_resource_source_id;
SELECT non_labor_resource
INTO l_element_name
FROM pa_non_labor_resources
WHERE NON_LABOR_RESOURCE_ID = p_resource_source_id;
SELECT RESOURCE_NAME
INTO l_revenue_category_code
FROM Pa_rbs_element_map
--WHERE RESOURCE_NAME = 'REVENUE_CATEGORY' -- NEED TO REVISIT
WHERE resource_type_id =
(SELECT res_type_id
FROM pa_res_types_b
WHERE res_type_code = 'REVENUE_CATEGORY')
AND RESOURCE_ID = p_resource_source_id;
SELECT lk.Meaning
INTO l_element_name
FROM PA_LOOKUPS lk
WHERE lk.Lookup_Type = 'REVENUE CATEGORY'
and lk.lookup_code = l_revenue_category_code;
SELECT Event_Type
INTO l_element_name
FROM pa_event_types
WHERE event_type_id = p_resource_source_id;
SELECT expenditure_type
INTO l_element_name
FROM pa_expenditure_types
WHERE expenditure_type_id = p_resource_source_id;
SELECT expenditure_category
INTO l_element_name
FROM pa_expenditure_categories
WHERE expenditure_category_id = p_resource_source_id;
SELECT Vendor_Name
INTO l_element_name
FROM po_vendors
WHERE vendor_id = p_resource_source_id;
SELECT resource_name
INTO l_element_name
FROM pa_rbs_element_map
--WHERE RESOURCE_NAME = p_resource_type_code -- NEED TO REVISIT
WHERE resource_type_id =
(SELECT res_type_id
FROM pa_res_types_b
WHERE res_type_code = 'REVENUE_CATEGORY')
AND RESOURCE_ID = p_resource_source_id;
* Procedure : Insert_elements
* Description : This Procedure is used to insert into
* the pa_rbs_element_names_b table
* it does a direct
* Insert from the pa_rbs_elem_in_temp
* table based on the resource_type_id
* passed.
* *****************************************************/
PROCEDURE Insert_elements(p_resource_type_id IN NUMBER,
x_return_status OUT NOCOPY Varchar2)
IS
--l_element_name_id Number;
INSERT INTO Pa_rbs_element_names_b
(RBS_ELEMENT_NAME_ID,
RESOURCE_SOURCE_ID,
RESOURCE_TYPE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
PA_RBS_ELEMENT_NAMES_S.NEXTVAL,
a.resource_source_id,
a.resource_type_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM pa_rbs_elem_in_temp a
WHERE a.resource_type_id = p_resource_type_id
AND NOT EXISTS (select 'Y'
FROM Pa_rbs_element_names_b b
where b.RESOURCE_TYPE_ID = a.resource_type_id
and b.RESOURCE_SOURCE_ID = a.resource_source_id);
END Insert_elements;
* Procedure : Insert_non_tl_names
* Description : This API is used to insert into the
* pa_rbs_element_names_tl table.
* For those res_type_codes for which there
* is no Multi lang support.
* We are going to insert the value based
* on the values inserted into the
* pa_rbs_element_names_b table.
* **************************************************/
PROCEDURE Insert_non_tl_names
(p_resource_type_id IN Number,
p_resource_type_code IN Varchar2,
x_return_status OUT NOCOPY Varchar2)
IS
l_count Number;
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.name,
c.language,
c.source_lang,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Pa_rbs_versions_tl c
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
AND c.rbs_version_id = a.resource_source_id
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = c.language);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.meaning,
c.language,
c.source_lang,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
fnd_lookup_values c
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
AND c.lookup_type = 'RBS_RULE_RESOURCE'
AND c.lookup_code = 'USER_DEFINED_RESOURCE'
AND c.view_application_id = 275
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = c.language);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.full_name,
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
per_all_people_f c,
fnd_languages l
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
AND c.person_Id = a.resource_source_id
and sysdate between c.effective_start_date
and c.effective_end_date
and l.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.name,
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Per_Jobs c,
Fnd_Languages L
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
AND c.Job_Id = a.resource_source_id
and l.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
lk.meaning,
lk.language,
lk.source_lang,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
fnd_lookup_values lk,
/* Changes for Bug 3780201 start*/
pa_rbs_element_map c
WHERE a.resource_source_id = b.resource_source_id
and a.resource_type_id = p_resource_type_id
and b.resource_type_id = p_resource_type_id
and lk.lookup_type = 'PA_PERSON_TYPE'
and c.resource_id = a.resource_source_id
and c.resource_type_id = p_resource_type_id
and lk.lookup_code = c.resource_name
and NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = lk.language);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
--For bug 3602566.
--c.description,
c.resource_code, --End of bug 3602566.
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Bom_Resources c,
Fnd_Languages L
WHERE a.resource_source_id = b.resource_source_id
and c.resource_id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and L.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.non_labor_resource,
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
pa_non_labor_resources c,
Fnd_Languages L
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and c.non_labor_resource_id = a.resource_source_id
and L.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.name,
c.language,
c.source_lang, --USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Pa_Resource_Classes_tl c
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and c.resource_class_id = a.resource_source_id
and NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = c.language);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.resource_name,
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Pa_rbs_element_map c,
Fnd_Languages L
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and c.resource_id = a.resource_source_id
and c.resource_type_id = p_resource_type_id
and L.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
lk.meaning, --c.resource_name,
lk.language,
lk.source_lang,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Pa_rbs_element_map c,
fnd_lookup_values lk
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and c.resource_id = a.resource_source_id
and c.resource_type_id = p_resource_type_id
and lk.lookup_type = 'REVENUE CATEGORY'
and lk.lookup_code = c.resource_name
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = lk.language);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.event_type,
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
pa_event_types c,
Fnd_Languages L
WHERE a.resource_source_id = b.resource_source_id
and c.event_type_id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and L.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.expenditure_type,
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
pa_expenditure_types c,
Fnd_Languages L
WHERE a.resource_source_id = b.resource_source_id
and c.expenditure_type_id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and L.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.expenditure_category,
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
pa_expenditure_categories c,
Fnd_Languages L
WHERE a.resource_source_id = b.resource_source_id
and c.expenditure_category_id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and L.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.vendor_name,
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
po_vendors c,
Fnd_Languages L
WHERE a.resource_source_id = b.resource_source_id
and c.vendor_id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and L.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
END Insert_non_tl_names;
* Procedure : Insert_tl_names
* Description : This API is used to insert into the
* pa_rbs_element_names_tl table.
* For those res_type_codes for which there
* are corr TL tables
* We are going to insert the value based
* on the values inserted into the
* pa_rbs_element_names_b table.
* and do a join with the corr TL
* tables for the res_type_codes.
* **************************************************/
PROCEDURE Insert_tl_names
(p_resource_type_id IN Number,
p_resource_type_code IN Varchar2,
x_return_status OUT NOCOPY Varchar2)
IS
BEGIN
IF p_resource_type_code = 'ORGANIZATION' THEN
--ORGANIZATION
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.name,
c.language,
c.source_lang,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
hr_all_organization_units_tl c
WHERE a.resource_source_id = b.resource_source_id
and c.organization_id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
AND NOT EXISTS (select 'Y'
from pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = c.language);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
c.meaning,
c.language,
c.source_lang,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Pa_Project_Role_Types_tl c
WHERE a.resource_source_id = b.resource_source_id
and c.project_role_id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
AND NOT EXISTS (select 'Y'
from pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = c.language);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
fnd_Flex_ext.GET_SEGS('INV', 'MCAT',
c.structure_id, c.category_id),
l.language_code,
USERENV('LANG'),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Fnd_Languages L,
Mtl_Categories_v c
WHERE a.resource_source_id = b.resource_source_id
and c.category_id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
and L.Installed_Flag in ('I', 'B')
AND NOT EXISTS (select 'Y'
FROM pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = l.language_code);
INSERT INTO Pa_rbs_element_names_tl
(RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
Language,
Source_Lang,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_by,
Last_Update_Login)
SELECT
a.rbs_element_name_id,
--For bug 3602566.
--c.description,
d.segment1,
c.language,
c.source_lang,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM
pa_rbs_element_names_b a,
pa_rbs_elem_in_temp b,
Mtl_System_Items_tl c,
Mtl_System_Items_b d --For bug 3602566
WHERE a.resource_source_id = b.resource_source_id
and c.Inventory_Item_Id = a.resource_source_id
AND a.resource_type_id = p_resource_type_id
AND b.resource_type_id = p_resource_type_id
AND c.inventory_item_id = d.inventory_item_id --For bug 3602566
AND c.organization_id = d.organization_id --For bug 3602566
AND c.organization_id =
(select item_master_id
from pa_resource_classes_b cls,
pa_plan_res_defaults def
where def.resource_class_id = cls.resource_class_id and cls.resource_class_code = 'MATERIAL_ITEMS'
and def.object_type = 'CLASS')
AND NOT EXISTS (select 'Y'
from pa_rbs_element_names_tl tl
where tl.rbs_element_name_id = a.rbs_element_name_id
and tl.language = c.language);
END Insert_tl_names;
* And then insert into the pa_rbs_element_names_b
* and pa_rbs_element_names_tl tables.
* Pass back the element_name_id.
* -> In the 2nd case
* Reads the records from the temp table.
* For each of them derives the element_name
* Inserts into the pa_rbs_element_names_b
* table.
* Inserts into the pa_rbs_element_names_tl
* table.
* Delete the recs in the temp table.
********************************************************/
PROCEDURE Populate_RBS_Element_Name
(p_resource_source_id IN Number Default Null,
p_resource_type_id IN Number Default Null,
x_rbs_element_name_id OUT NOCOPY Number,
x_return_status OUT NOCOPY Varchar2)
IS
/*********************************************
* This cursor is used to get the res_type_id
* and the corr res_type_codes for it from the
* temp table. so that we can insert into the
* table for each res_type_code.
********************************************/
Cursor c_get_res_types
IS
SELECT distinct a.resource_type_id,
decode(a.resource_type_id,-1,'VERSION',-2,'RULE',b.res_type_code)
FROM pa_rbs_elem_in_temp a,pa_res_types_b b
WHERE a.resource_type_id = b.res_type_id(+)
ORDER by resource_type_id;
* Insert into the pa_rbs_elem_in_temp table
* explicitely.
**********************************************/
BEGIN
Insert into pa_rbs_elem_in_temp
(resource_source_id,
resource_type_id)
Values
(p_resource_source_id,
l_resource_type_id);
* and delete it, before proceeding.
* Because if duplicate values are passed for
* combination of resource_source_id and
* resource_type_id, then we cannot anyway
* insert multiple rows into the 2 main tables.
**************************************************/
BEGIN
DELETE FROM pa_rbs_elem_in_temp a WHERE ROWID > (
SELECT min(rowid) FROM pa_rbs_elem_in_temp b
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = b.resource_type_id);
* delete rows from the temp table which already
* have rows in pa_rbs_element_names_b */
DELETE FROM pa_rbs_elem_in_temp a
WHERE EXISTS (SELECT 'Y'
FROM pa_rbs_element_names_b b
WHERE a.resource_source_id = b.resource_source_id
AND a.resource_type_id = b.resource_type_id);
select count(*) into l_temp_count from pa_rbs_elem_in_temp;
SELECT rbs_element_name_id
INTO x_rbs_element_name_id
FROM pa_rbs_element_names_b
WHERE resource_source_id = p_resource_source_id
AND resource_type_id = l_resource_type_id
AND rownum = 1;
* we can roll back the insert into any prev tables.
**************************************************/
Savepoint insert_for_call_flag_AB;
* Insert into the Pa_rbs_element_names_b
* for the res_type passed. it will just do an
* Insert as Select from the pa_rbs_elem_in_temp
* table for the corr res_type.
*****************************************************/
Insert_elements
(p_resource_type_id => l_res_type,
x_return_status => l_return_status);
Rollback to Savepoint insert_for_call_flag_AB;
Insert_non_tl_names
(p_resource_type_id => l_res_type,
p_resource_type_code => l_res_type_code,
x_return_status => l_return_status);
Rollback to Savepoint insert_for_call_flag_AB;
Insert_tl_names
(p_resource_type_id => l_res_type,
p_resource_type_code => l_res_type_code,
x_return_status => l_return_status);
Rollback to Savepoint insert_for_call_flag_AB;
DELETE FROM pa_rbs_elem_in_temp;
SELECT rbs_element_name_id
INTO x_rbs_element_name_id
FROM pa_rbs_element_names_b
WHERE resource_source_id = p_resource_source_id
AND resource_type_id = l_resource_type_id;
SELECT Res_type_id
FROM pa_res_types_b;
DELETE FROM pa_rbs_element_names_tl;
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
lk.meaning,
lk.language,
lk.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM --pa_lookups lk,
fnd_lookup_values lk,
pa_rbs_element_names_b a
WHERE
a.resource_type_id = -2
and a.resource_source_id = -1
AND lk.lookup_code = 'USER_DEFINED_RESOURCE'
AND lk.lookup_type = 'RBS_RULE_RESOURCE');
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
b.rbs_element_name_id,
vertl.name,
vertl.language,
vertl.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_rbs_versions_tl vertl,
pa_rbs_element_names_b b
WHERE b.resource_type_id = -1
AND b.resource_source_id = vertl.rbs_version_id);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
b.resource_code,
--b.description,--For bug 3602566
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM bom_resources b,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_type_id = 1
AND b.resource_id = a.resource_source_id
AND l.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
b.resource_code,
--b.description,--For bug 3602566
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM bom_resources b,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_type_id = 2
AND b.resource_id = a.resource_source_id
and L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
b.rbs_element_name_id,
per.full_name,
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM per_all_people_f per,
pa_rbs_element_names_b b,
Fnd_Languages L
WHERE TRUNC(sysdate) BETWEEN
effective_start_date AND
NVL(effective_end_date,TRUNC(sysdate))
AND b.resource_type_id=3
AND per.person_id=b.resource_source_id
and L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
E.EVENT_TYPE,
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM PA_EVENT_TYPES E,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_type_id=4
AND E.event_type_id=a.resource_source_id
AND L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
ec.EXPENDITURE_CATEGORY,
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_expenditure_categories ec,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_type_id = 5
AND ec.EXPENDITURE_CATEGORY_ID =
a.resource_source_id
AND L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
ec.expenditure_type,
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_expenditure_types ec,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_type_id = 6
AND ec.EXPENDITURE_TYPE_ID =
a.resource_source_id
AND L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
fnd_Flex_ext.GET_SEGS('INV', 'MCAT',
c.structure_id, c.category_id),
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM mtl_categories_v c,
Fnd_Languages L,
pa_rbs_element_names_b a
WHERE a.resource_source_id=c.CATEGORY_ID
AND a.resource_type_id=7
AND L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
b.segment1, --For bug 3602566
t.language,
t.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM MTL_SYSTEM_ITEMS_tl t,
MTL_SYSTEM_ITEMS_b b,
pa_plan_res_defaults p,
pa_rbs_element_names_b a
WHERE b.inventory_item_id=t.inventory_item_id
AND b.organization_id=t.organization_id
AND t.organization_id = p.item_master_id
AND p.resource_class_id = 3
AND a.resource_type_id=8
AND a.resource_source_id=t.inventory_item_id);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
job.name,
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM per_jobs job,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_type_id=9
AND a.resource_source_id=job.job_id
AND L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
distinct a.rbs_element_name_id,
tl.name,
tl.language,
tl.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM hr_all_organization_units_tl tl,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE tl.organization_id = a.resource_source_id
AND a.resource_type_id = 10);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT a.rbs_element_name_id,
lk.meaning,
lk.language,
lk.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_rbs_element_map map,-- For bug 3799582
fnd_lookup_values lk,
pa_rbs_element_names_b a
WHERE map.resource_type_id = a.resource_type_id
AND a.resource_source_id=map.resource_id
AND a.resource_type_id=11
AND lk.lookup_type = 'PA_PERSON_TYPE'
AND lk.lookup_code = map.resource_name);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
r.NON_LABOR_RESOURCE,
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_non_labor_resources r,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_source_id =
r.non_labor_resource_id
AND a.resource_type_id = 12
AND L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
tl.name,
tl.language,
tl.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_resource_classes_tl tl,
pa_rbs_element_names_b a
WHERE a.resource_source_id=tl.resource_class_id
AND a.resource_type_id=13);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
lk.meaning,
lk.language,
lk.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM fnd_lookup_values lk,
pa_rbs_element_names_b a,
pa_rbs_element_map map
WHERE a.resource_source_id=map.resource_id
AND map.resource_name=lk.lookup_code
AND lk.Lookup_Type = 'REVENUE CATEGORY'
AND a.resource_type_id=14);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
tl.meaning,
tl.language,
tl.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_project_role_types_tl tl,
pa_rbs_element_names_b a
WHERE a.resource_type_id = 15
AND a.resource_source_id = tl.project_role_id);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS Changes
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
v.vendor_name,
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM po_vendors v,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_type_id = 16
AND a.resource_source_id = v.vendor_id
AND L.Installed_Flag in ('I', 'B'));
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
--MLS
INSERT INTO pa_rbs_element_names_tl(
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
a.rbs_element_name_id,
map.resource_name,
l.language_code,
USERENV('LANG'),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_rbs_element_map map,
pa_rbs_element_names_b a,
Fnd_Languages L
WHERE a.resource_type_id = 18
AND a.resource_source_id = map.resource_id
AND L.Installed_Flag in ('I', 'B'));
* The Below cursor selects the resource_names for
* the rbs_element_id passed in, using a connect by clause.
* It starts with the deepest node first, and then traverses up.
*********************************************************/
Cursor c_element_name
IS
SELECT tl.resource_name
FROM pa_rbs_elements ele, pa_rbs_element_names_vl tl
WHERE ele.RBS_ELEMENT_NAME_ID = tl.RBS_ELEMENT_NAME_ID
CONNECT BY PRIOR ele.parent_element_id = ele.rbs_element_id
START WITH ele.rbs_element_id = p_rbs_element_id
ORDER BY rbs_level DESC;
SELECT resource_list_id,
start_date_active,
end_date_active,
business_group_id,
job_group_id,
nvl(uncategorized_flag, 'N') uncategorized_flag,
decode(group_resource_type_id,103, 'ORGANIZATION',
decode(group_resource_type_id, 108, 'EXPENDITURE_CATEGORY',
decode(group_resource_type_id, 109, 'REVENUE_CATEGORY',
'NONE'))) group_res_type
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT name,
description,
language,
source_lang
FROM pa_resource_lists_tl
WHERE resource_list_id = p_resource_list_id;
SELECT resource_list_member_id,
organization_id,
expenditure_category,
revenue_category
FROM pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND migration_code = 'M'
AND resource_type_id not in (110, 107)
AND parent_member_id is null;
SELECT child.resource_list_member_id,
child.parent_member_id,
child.resource_type_id,
child.organization_id,
child.person_id,
child.job_id,
child.vendor_id,
child.project_role_id,
child.event_type,
child.expenditure_type,
child.expenditure_category,
child.revenue_category
FROM pa_resource_list_members child
WHERE child.resource_list_id = p_resource_list_id
AND ((child.parent_member_id IS NOT NULL AND
EXISTS (SELECT 'Y' FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id
AND group_resource_type_id in (108, 109, 103)))
OR (child.parent_member_id IS NULL AND
EXISTS (SELECT 'Y' FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id
AND group_resource_type_id not in (108, 109, 103))))
AND migration_code = 'M'
AND resource_type_id not in (110, 107)
ORDER BY child.parent_member_id; -- Added for bug 3745326 so that outline numbers for child elements can be properly derived.
SELECT res_type_id, res_type_code
FROM pa_res_types_b
WHERE res_type_code = decode(p_resource_type_id, 101, 'NAMED_PERSON',
decode(p_resource_type_id, 102, 'JOB',
decode(p_resource_type_id, 103, 'ORGANIZATION',
decode(p_resource_type_id, 104, 'SUPPLIER',
decode(p_resource_type_id, 111, 'ROLE',
decode(p_resource_type_id, 105, 'EXPENDITURE_TYPE',
decode(p_resource_type_id, 106, 'EVENT_TYPE',
decode(p_resource_type_id, 108, 'EXPENDITURE_CATEGORY',
decode(p_resource_type_id, 109, 'REVENUE_CATEGORY',
NULL)))))))));
SELECT rlm.revenue_category,
typ.res_type_id -- resource_type_id
FROM pa_resource_list_members rlm,
(select res_type_id from pa_res_types_b
where res_type_code = 'REVENUE_CATEGORY') typ
WHERE rlm.resource_list_id = p_resource_list_id
AND rlm.resource_type_id = 109;
SELECT asg.project_id
FROM pa_resource_list_assignments asg,
pa_resource_list_uses pru
WHERE asg.resource_list_id = p_resource_list_id
AND asg.resource_list_assignment_id = pru.resource_list_assignment_id
AND pru.use_code = 'ACTUALS_ACCUM';
SELECT PA_RBS_HEADERS_S.nextval
INTO l_rbs_header_id from dual;
INSERT INTO PA_RBS_HEADERS_B
(RBS_HEADER_ID,
EFFECTIVE_FROM_DATE,
EFFECTIVE_TO_DATE,
BUSINESS_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER,
USE_FOR_ALLOC_FLAG)
VALUES
(l_rbs_header_id,
l_list.start_date_active,
l_list.end_date_active,
l_list.business_group_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1,
'Y');
Select Count(*)
Into l_Count
From Pa_Rbs_Headers_tl
Where Name = l_names.name
And language = userenv('LANG');
Select Count(*)
Into l_Count
From Pa_Rbs_Headers_tl
Where Name = l_rbs_header_name
And language = userenv('LANG');
INSERT INTO PA_RBS_HEADERS_TL(
RBS_HEADER_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES(
l_rbs_header_id,
l_rbs_header_name,
l_names.description,
l_names.language,
l_names.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
SELECT PA_RBS_VERSIONS_S.nextval
INTO l_rbs_version_id from dual;
INSERT INTO PA_RBS_VERSIONS_B (
RBS_VERSION_ID,
VERSION_NUMBER,
RBS_HEADER_ID,
VERSION_START_DATE,
VERSION_END_DATE,
JOB_GROUP_ID,
RULE_BASED_FLAG,
VALIDATED_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER,
current_reporting_flag )
VALUES (
l_rbs_version_id,
1,
l_rbs_header_id,
l_list.start_date_active,
NULL,
l_list.job_group_id,
'N',
'Y',
'FROZEN',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1,
'Y');
INSERT INTO PA_RBS_VERSIONS_TL(
RBS_VERSION_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES(
l_rbs_version_id,
l_rbs_header_name,
l_names.description,
l_names.language,
l_names.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
UPDATE pa_resource_lists_all_bg
SET migrated_rbs_version_id = l_rbs_version_id
WHERE resource_list_id = l_list.resource_list_id;
INSERT INTO PA_RBS_ELEM_IN_TEMP
(resource_source_id, resource_type_id)
VALUES (l_rbs_version_id, -1);
INSERT INTO PA_RBS_ELEM_IN_TEMP
(resource_source_id,
resource_type_id)
SELECT decode(typ.res_type_code, 'ORGANIZATION', rlm.organization_id,
decode(typ.res_type_code, 'NAMED_PERSON', rlm.person_id,
decode(typ.res_type_code, 'JOB', rlm.job_id,
decode(typ.res_type_code, 'SUPPLIER', rlm.vendor_id,
decode(typ.res_type_code, 'ROLE', rlm.project_role_id,
NULL))))), -- resource_source_id,
typ.res_type_id -- resource_type_id
FROM pa_resource_list_members rlm,
(select res_type_id, res_type_code from pa_res_types_b) typ
WHERE rlm.resource_list_id = l_list.resource_list_id
AND ((rlm.resource_type_id = 103
AND typ.res_type_code = 'ORGANIZATION') OR -- Org
(rlm.resource_type_id = 101
AND typ.res_type_code = 'NAMED_PERSON') OR -- Emp
(rlm.resource_type_id = 102
AND typ.res_type_code = 'JOB') OR -- Job
(rlm.resource_type_id = 104
AND typ.res_type_code = 'SUPPLIER') OR -- Vendor
(rlm.resource_type_id = 111
AND typ.res_type_code = 'ROLE') -- Role
);
/*INSERT INTO PA_RBS_ELEM_IN_TEMP
(resource_source_id,
resource_type_id)
SELECT source.resource_source_id,
typ.res_type_id -- resource_type_id
FROM pa_resource_list_members rlm,
(select res_type_id, res_type_code from pa_res_types_b) typ,
((select expenditure_type_id resource_source_id,
expenditure_type resource_name, 1 type_number
from pa_expenditure_types)
UNION
(select expenditure_category_id resource_source_id,
expenditure_category resource_name, 2 type_number
from pa_expenditure_categories)
UNION
(select event_type_id resource_source_id,
event_type resource_name, 3 type_number
from pa_event_types)) source
WHERE rlm.resource_list_id = l_list.resource_list_id
AND ((rlm.resource_type_id = 105
AND typ.res_type_code = 'EXPENDITURE_TYPE'
AND source.resource_name = rlm.expenditure_type
AND source.type_number = 1) OR--ExpType
(rlm.resource_type_id = 106
AND typ.res_type_code = 'EVENT_TYPE'
AND source.resource_name = rlm.event_type
AND source.type_number = 3) OR -- Event Type
(rlm.resource_type_id = 108
AND typ.res_type_code = 'EXPENDITURE_CATEGORY'
AND source.resource_name = rlm.expenditure_category
AND source.type_number = 2) --ECat
);*/
INSERT INTO PA_RBS_ELEM_IN_TEMP
(resource_source_id,
resource_type_id)
SELECT resource_source_id,
res_type_id -- resource_type_id
FROM
(
(SELECT source.resource_source_id,
typ.res_type_id -- resource_type_id
FROM
pa_resource_list_members rlm,
(select res_type_id, res_type_code from pa_res_types_b) typ,
((select expenditure_category_id resource_source_id,
expenditure_category resource_name, 2 type_number
from pa_expenditure_categories)
UNION
(select event_type_id resource_source_id,
event_type resource_name, 3 type_number
from pa_event_types)) source
WHERE rlm.resource_list_id = l_list.resource_list_id
AND ((rlm.resource_type_id = 106
AND typ.res_type_code = 'EVENT_TYPE'
AND source.resource_name = rlm.event_type
AND source.type_number = 3) OR -- Event Type
(rlm.resource_type_id = 108
AND typ.res_type_code = 'EXPENDITURE_CATEGORY'
AND source.resource_name = rlm.expenditure_category
AND source.type_number = 2) --ECat
)
)
UNION ALL
(SELECT source.resource_source_id,
typ.res_type_id -- resource_type_id
FROM
pa_resource_list_members rlm,
(select res_type_id, res_type_code from pa_res_types_b) typ,
(select expenditure_type_id resource_source_id,
expenditure_type resource_name, 1 type_number
from pa_expenditure_types) source
WHERE rlm.resource_list_id = l_list.resource_list_id
AND (rlm.resource_type_id = 105
AND typ.res_type_code = 'EXPENDITURE_TYPE'
AND source.resource_name = rlm.expenditure_type
AND source.type_number = 1)--ExpType
)
);
INSERT INTO PA_RBS_ELEM_IN_TEMP
(resource_source_id,
resource_type_id)
VALUES (l_resource_id,
l_rev_cat.res_type_id);
SELECT PA_RBS_ELEMENTS_S.nextval
INTO l_top_node_id from dual;
SELECT PA_RBS_ELEMENT_IDENTIFIER_S.nextval
INTO l_rbs_identifier_id from dual;
SELECT rbs_element_name_id INTO l_element_name_id
FROM pa_rbs_element_names_b
WHERE resource_source_id = l_rbs_version_id
AND resource_type_id = -1;
INSERT INTO PA_RBS_ELEMENTS (
RBS_Element_Id,
Rbs_Element_Name_Id,
RBS_Version_Id,
Outline_Number,
Order_Number,
Resource_Type_Id,
Resource_Source_Id,
Rule_Flag,
Parent_Element_Id,
Rbs_Level,
Element_Identifier,
User_Created_Flag,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Record_Version_Number )
VALUES (
l_top_node_id,
l_element_name_id,
l_rbs_version_id,
0,
0, -- P_Order_Number,
-1,
l_rbs_version_id,
'N',
NULL,
1,
l_rbs_identifier_id,
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1);
SELECT PA_RBS_ELEMENTS_S.nextval
INTO l_rbs_element_id from dual;
SELECT PA_RBS_ELEMENT_IDENTIFIER_S.nextval
INTO l_rbs_identifier_id from dual;
SELECT expenditure_category_id
INTO l_expenditure_category_id
FROM pa_expenditure_categories
WHERE expenditure_category =
l_parents.expenditure_category;
SELECT resource_id
INTO l_revenue_category_id
FROM pa_rbs_element_map
WHERE resource_name = l_parents.revenue_category
AND resource_type_id = (select res_type_id
from pa_res_types_b
where res_type_code = 'REVENUE_CATEGORY');
SELECT res_type_id INTO l_res_type_id
FROM pa_res_types_b
WHERE res_type_code = l_list.group_res_type;
SELECT decode(l_list.group_res_type,
'ORGANIZATION', l_parents.organization_id,
decode(l_list.group_res_type,'EXPENDITURE_CATEGORY',
l_expenditure_category_id,
decode(l_list.group_res_type, 'REVENUE_CATEGORY',
l_revenue_category_id, NULL)))
INTO l_resource_source_id
FROM dual;
SELECT rbs_element_name_id
INTO l_element_name_id
FROM pa_rbs_element_names_b
WHERE resource_source_id = l_resource_source_id
AND resource_type_id = l_res_type_id;
INSERT INTO PA_RBS_ELEMENTS (
RBS_Element_Id,
Rbs_Element_Name_Id,
RBS_Version_Id,
Outline_Number,
Order_Number,
Resource_Type_Id,
Resource_Source_Id,
Organization_Id,
Expenditure_Category_Id,
Revenue_Category_Id,
Rule_Flag,
Parent_Element_Id,
Rbs_Level,
Element_Identifier,
User_Created_Flag,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Record_Version_Number )
Values (
l_rbs_element_id,
l_element_name_id,
l_rbs_version_id,
l_Outline_Number,
null, -- P_Order_Number,
l_res_type_id,
l_resource_source_id,
l_parents.organization_id,
l_expenditure_category_id,
l_revenue_category_id,
'N',
l_top_node_id,
2,
l_rbs_identifier_id,
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1);
UPDATE pa_resource_list_members
SET migrated_rbs_element_id = l_rbs_element_id
WHERE resource_list_member_id =
l_parents.resource_list_member_id;
SELECT PA_RBS_ELEMENTS_S.nextval
INTO l_rbs_element_id from dual;
SELECT PA_RBS_ELEMENT_IDENTIFIER_S.nextval
INTO l_rbs_identifier_id from dual;
SELECT migrated_rbs_element_id
INTO l_parent_element_id
FROM pa_resource_list_members
WHERE resource_list_member_id =
l_members.parent_member_id;
SELECT (rbs_level + 1), expenditure_category_id,
revenue_category_id
INTO l_rbs_level, l_expenditure_category_id,
l_revenue_category_id
FROM pa_rbs_elements
WHERE rbs_element_id = l_parent_element_id;
SELECT outline_number INTO l_parent_outline_number
FROM pa_rbs_elements
WHERE rbs_element_id = l_parent_element_id;
SELECT expenditure_category_id
INTO l_expenditure_category_id
FROM pa_expenditure_categories
WHERE expenditure_category =
l_members.expenditure_category;
SELECT resource_id
INTO l_revenue_category_id
FROM pa_rbs_element_map
WHERE resource_name = l_members.revenue_category
AND resource_type_id = (select res_type_id
from pa_res_types_b
where res_type_code = 'REVENUE_CATEGORY');
SELECT expenditure_type_id
INTO l_expenditure_type_id
FROM pa_expenditure_types
WHERE expenditure_type = l_members.expenditure_type;
SELECT event_type_id
INTO l_event_type_id
FROM pa_event_types
WHERE event_type = l_members.event_type;
SELECT decode(l_res_type_code,
'ORGANIZATION', l_members.organization_id,
decode(l_res_type_code,'EXPENDITURE_CATEGORY',
l_expenditure_category_id,
decode(l_res_type_code, 'REVENUE_CATEGORY',
l_revenue_category_id,
decode(l_res_type_code,'EXPENDITURE_TYPE',
l_expenditure_type_id,
decode(l_res_type_code,'EVENT_TYPE',l_event_type_id,
decode(l_res_type_code,'JOB', l_members.job_id,
decode(l_res_type_code,'NAMED_PERSON',
l_members.person_id,
decode(l_res_type_code,'ROLE',
l_members.project_role_id,
decode(l_res_type_code,'SUPPLIER',
l_members.vendor_id, NULL)))))))))
INTO l_resource_source_id
FROM dual;
SELECT rbs_element_name_id INTO l_element_name_id
FROM pa_rbs_element_names_b
WHERE resource_source_id = l_resource_source_id
AND resource_type_id = l_res_type_id;
INSERT INTO PA_RBS_ELEMENTS (
RBS_Element_Id,
Rbs_Element_Name_Id,
RBS_Version_Id,
Outline_Number,
Order_Number,
Resource_Type_Id,
Resource_Source_Id,
Organization_Id,
person_id,
job_id,
role_id,
supplier_id,
Expenditure_Category_Id,
Revenue_Category_Id,
Expenditure_type_id,
event_type_id,
Rule_Flag,
Parent_Element_Id,
Rbs_Level,
Element_Identifier,
User_Created_Flag,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Record_Version_Number)
Values (
l_rbs_element_id,
l_element_name_id,
l_rbs_version_id,
l_Outline_Number,
null, -- P_Order_Number,
l_res_type_id,
l_resource_source_id,
l_members.organization_id,
l_members.person_id,
l_members.job_id,
l_members.project_role_id,
l_members.vendor_id,
l_expenditure_category_id,
l_revenue_category_id,
l_expenditure_type_id,
l_event_type_id,
'N',
l_parent_element_id,
l_rbs_level,
l_rbs_identifier_id,
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1);
UPDATE pa_resource_list_members
SET migrated_rbs_element_id = l_rbs_element_id
WHERE resource_list_member_id =
l_members.resource_list_member_id;
delete from Pa_Rbs_Elements_Temp;
Insert Into Pa_Rbs_Elements_Temp(
New_Element_Id,
Old_Element_Id,
Old_Parent_Element_Id,
New_Parent_Element_Id )
(Select
Pa_Rbs_Elements_S.NextVal,
Rbs_Element_Id,
Parent_Element_Id,
Null
From
Pa_Rbs_Elements
Where
Rbs_Version_Id = l_rbs_version_id
and user_created_flag = 'N' );
Update Pa_Rbs_Elements_Temp Tmp1
Set New_Parent_Element_Id =
(Select
New_Element_Id
From
Pa_Rbs_Elements_Temp Tmp2
Where
Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
Insert Into Pa_Rbs_Elements
(
RBS_ELEMENT_ID,
RBS_ELEMENT_NAME_ID,
RBS_VERSION_ID,
OUTLINE_NUMBER,
ORDER_NUMBER,
RESOURCE_TYPE_ID,
RESOURCE_SOURCE_ID,
PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
EXPENDITURE_TYPE_ID,
EVENT_TYPE_ID,
EXPENDITURE_CATEGORY_ID,
REVENUE_CATEGORY_ID,
inventory_item_id,
item_category_id,
bom_labor_id,
bom_equipment_id,
non_labor_resource_id,
role_id,
person_type_id,
resource_class_id,
supplier_id,
rule_flag,
PARENT_ELEMENT_ID,
rbs_level,
element_identifier,
user_defined_custom1_id,
user_defined_custom2_id,
user_defined_custom3_id,
user_defined_custom4_id,
user_defined_custom5_id,
USER_CREATED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER)
Select
Tmp.New_Element_Id,
Rbs_Elements.Rbs_Element_Name_Id,
l_rbs_version_id,
Rbs_Elements.Outline_Number,
Rbs_Elements.Order_Number,
Rbs_Elements.Resource_Type_Id,
Rbs_Elements.Resource_Source_Id,
Rbs_Elements.Person_Id,
Rbs_Elements.Job_Id,
Rbs_Elements.Organization_Id,
Rbs_Elements.Expenditure_Type_Id,
Rbs_Elements.Event_Type_Id,
Rbs_Elements.Expenditure_Category_Id,
Rbs_Elements.Revenue_Category_Id,
Rbs_Elements.Inventory_Item_Id,
Rbs_Elements.Item_Category_Id,
Rbs_Elements.Bom_Labor_Id,
Rbs_Elements.Bom_Equipment_Id,
Rbs_Elements.Non_Labor_Resource_Id,
Rbs_Elements.Role_Id,
Rbs_Elements.Person_Type_Id,
Rbs_Elements.Resource_Class_Id,
Rbs_Elements.Supplier_Id,
Rbs_Elements.Rule_Flag,
Tmp.New_Parent_Element_Id,
Rbs_Elements.Rbs_Level,
Rbs_Elements.Element_Identifier,
Rbs_Elements.User_Defined_Custom1_Id,
Rbs_Elements.User_Defined_Custom2_Id,
Rbs_Elements.User_Defined_Custom3_Id,
Rbs_Elements.User_Defined_Custom4_Id,
Rbs_Elements.User_Defined_Custom5_Id,
'Y',
Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
Pa_Rbs_Versions_Pvt.G_Creation_Date,
Pa_Rbs_Versions_Pvt.G_Created_By,
Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
1
From
Pa_Rbs_Elements Rbs_Elements,
Pa_Rbs_Elements_Temp Tmp
Where
Tmp.Old_Element_Id = Rbs_Elements.Rbs_Element_Id;
INSERT INTO pa_rbs_prj_assignments (
RBS_PRJ_ASSIGNMENT_ID ,
PROJECT_ID ,
RBS_VERSION_ID ,
RBS_HEADER_ID ,
REPORTING_USAGE_FLAG ,
WP_USAGE_FLAG ,
FP_USAGE_FLAG ,
PROG_REP_USAGE_FLAG ,
PRIMARY_REPORTING_RBS_FLAG ,
ASSIGNMENT_STATUS ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
RECORD_VERSION_NUMBER )
(SELECT pa_rbs_prj_assignments_s.nextval,
asg.project_id,
l_rbs_version_id,
l_rbs_header_id,
'Y',
'N',
'N',
'N',
decode(rpa.primary_reporting_rbs_flag, 'Y', 'N', pru.default_flag),
'ACTIVE',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1
FROM pa_resource_list_assignments asg,
pa_resource_list_uses pru,
pa_rbs_prj_assignments rpa
WHERE asg.resource_list_id = l_list.resource_list_id
AND asg.resource_list_assignment_id =
pru.resource_list_assignment_id
AND pru.use_code = 'ACTUALS_ACCUM'
AND asg.project_id = rpa.project_id(+)
AND rpa.primary_reporting_rbs_flag(+) = 'Y'
);
SELECT PA_RBS_VERSIONS_S.nextval
INTO l_rbs_version_id from dual;
INSERT INTO PA_RBS_VERSIONS_B (
RBS_VERSION_ID,
VERSION_NUMBER,
RBS_HEADER_ID,
VERSION_START_DATE,
VERSION_END_DATE,
JOB_GROUP_ID,
RULE_BASED_FLAG,
VALIDATED_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER,
current_reporting_flag )
VALUES (
l_rbs_version_id,
2,
l_rbs_header_id,
NULL,
NULL,
l_list.job_group_id,
'N',
'Y',
'WORKING',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1,
'N');
INSERT INTO PA_RBS_VERSIONS_TL(
RBS_VERSION_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES(
l_rbs_version_id,
l_rbs_header_name,
l_names.description,
l_names.language,
l_names.source_lang,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
delete from Pa_Rbs_Elements_Temp;
Insert Into Pa_Rbs_Elements_Temp(
New_Element_Id,
Old_Element_Id,
Old_Parent_Element_Id,
New_Parent_Element_Id )
(Select
Pa_Rbs_Elements_S.NextVal,
Rbs_Element_Id,
Parent_Element_Id,
Null
From
Pa_Rbs_Elements
Where
Rbs_Version_Id = l_Rbs_Version_From_Id
and user_created_flag = 'Y' );
Update Pa_Rbs_Elements_Temp Tmp1
Set New_Parent_Element_Id =
(Select
New_Element_Id
From
Pa_Rbs_Elements_Temp Tmp2
Where
Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
Insert Into Pa_Rbs_Elements
(
RBS_ELEMENT_ID,
RBS_ELEMENT_NAME_ID,
RBS_VERSION_ID,
OUTLINE_NUMBER,
ORDER_NUMBER,
RESOURCE_TYPE_ID,
RESOURCE_SOURCE_ID,
PERSON_ID,
JOB_ID,
ORGANIZATION_ID,
EXPENDITURE_TYPE_ID,
EVENT_TYPE_ID,
EXPENDITURE_CATEGORY_ID,
REVENUE_CATEGORY_ID,
inventory_item_id,
item_category_id,
bom_labor_id,
bom_equipment_id,
non_labor_resource_id,
role_id,
person_type_id,
resource_class_id,
supplier_id,
rule_flag,
PARENT_ELEMENT_ID,
rbs_level,
element_identifier,
user_defined_custom1_id,
user_defined_custom2_id,
user_defined_custom3_id,
user_defined_custom4_id,
user_defined_custom5_id,
USER_CREATED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER)
Select
Tmp.New_Element_Id,
Rbs_Elements.Rbs_Element_Name_Id,
l_rbs_version_id,
Rbs_Elements.Outline_Number,
Rbs_Elements.Order_Number,
Rbs_Elements.Resource_Type_Id,
Rbs_Elements.Resource_Source_Id,
Rbs_Elements.Person_Id,
Rbs_Elements.Job_Id,
Rbs_Elements.Organization_Id,
Rbs_Elements.Expenditure_Type_Id,
Rbs_Elements.Event_Type_Id,
Rbs_Elements.Expenditure_Category_Id,
Rbs_Elements.Revenue_Category_Id,
Rbs_Elements.Inventory_Item_Id,
Rbs_Elements.Item_Category_Id,
Rbs_Elements.Bom_Labor_Id,
Rbs_Elements.Bom_Equipment_Id,
Rbs_Elements.Non_Labor_Resource_Id,
Rbs_Elements.Role_Id,
Rbs_Elements.Person_Type_Id,
Rbs_Elements.Resource_Class_Id,
Rbs_Elements.Supplier_Id,
Rbs_Elements.Rule_Flag,
Tmp.New_Parent_Element_Id,
Rbs_Elements.Rbs_Level,
Rbs_Elements.Element_Identifier,
Rbs_Elements.User_Defined_Custom1_Id,
Rbs_Elements.User_Defined_Custom2_Id,
Rbs_Elements.User_Defined_Custom3_Id,
Rbs_Elements.User_Defined_Custom4_Id,
Rbs_Elements.User_Defined_Custom5_Id,
Rbs_Elements.User_Created_Flag,
Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
Pa_Rbs_Versions_Pvt.G_Creation_Date,
Pa_Rbs_Versions_Pvt.G_Created_By,
Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
1
From
Pa_Rbs_Elements Rbs_Elements,
Pa_Rbs_Elements_Temp Tmp
Where
Tmp.Old_Element_Id = Rbs_Elements.Rbs_Element_Id;
Update Pa_Rbs_Elements
Set Rbs_Element_Name_Id = l_New_Element_Name_Id,
Resource_Source_Id = l_rbs_version_id
Where Rbs_Version_Id = l_rbs_version_id
And Resource_Type_Id = -1
And Rbs_Level = 1;
* Procedure : Delete_proj_specific_RBS
* Desc : This API is used to delete the project specific RBS
* assignment once the project is deleted.
*********************************************************************/
PROCEDURE Delete_Proj_Specific_RBS(
p_project_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
DELETE FROM pa_rbs_prj_assignments
WHERE project_id = p_project_id;
'Pa_RBS_Utils.Delete_Proj_Specific_RBS'
,p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_Proj_Specific_RBS;
delete from pa_rbs_element_names_tl T
where not exists
(select NULL
from pa_rbs_element_names_b B
where B.RBS_ELEMENT_NAME_ID = T.RBS_ELEMENT_NAME_ID
);
update pa_rbs_element_names_tl T set (
RESOURCE_NAME
) = (select
B.RESOURCE_NAME
from pa_rbs_element_names_tl b
where B.RBS_ELEMENT_NAME_ID = T.RBS_ELEMENT_NAME_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.RBS_ELEMENT_NAME_ID,
T.LANGUAGE
) in (select
SUBT.RBS_ELEMENT_NAME_ID,
SUBT.LANGUAGE
from pa_rbs_element_names_tl SUBB, pa_rbs_element_names_tl SUBT
where SUBB.RBS_ELEMENT_NAME_ID = SUBT.RBS_ELEMENT_NAME_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.RESOURCE_NAME <> SUBT.RESOURCE_NAME
));
insert into pa_rbs_element_names_tl (
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
RBS_ELEMENT_NAME_ID,
RESOURCE_NAME,
LANGUAGE,
SOURCE_LANG
) select
B.LAST_UPDATE_LOGIN,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.RBS_ELEMENT_NAME_ID,
B.RESOURCE_NAME,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from pa_rbs_element_names_tl B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from pa_rbs_element_names_tl T
where T.RBS_ELEMENT_NAME_ID = B.RBS_ELEMENT_NAME_ID
and T.LANGUAGE = L.LANGUAGE_CODE);