[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from dual
where ((exists (select 'Y'
from pa_resource_list_members
where spread_curve_id = p_spread_curve_id))
OR (exists (select 'Y'
from pa_plan_res_defaults
where spread_curve_id = p_spread_curve_id)));
select 'Y'
from pa_resource_list_members
where non_labor_resource = p_non_labor_resource
and migration_code = 'N';
SELECT typ.res_type_code
INTO l_res_type_code
FROM pa_res_types_b typ,
pa_resource_list_members rlm,
pa_res_formats_b fmt
WHERE rlm.resource_list_member_id = p_resource_list_member_id
AND rlm.res_format_id = fmt.res_format_id
AND fmt.res_type_enabled_flag = 'Y'
AND fmt.res_type_id = typ.res_type_id;
Select DECODE(incurred_by_res_flag, 'Y','',
DECODE(l_res_type_code,
'NAMED_PERSON',to_char(person_id),
'PERSON_TYPE', person_type_code,
'JOB', to_char(job_id),
'BOM_LABOR', to_char(bom_resource_id),
'RESOURCE_CLASS',resource_class_code,
'NON_LABOR_RESOURCE',non_labor_resource,
'BOM_EQUIPMENT',to_char(bom_resource_id),
'INVENTORY_ITEM',to_char(inventory_item_id),
'ITEM_CATEGORY',to_char(item_category_id)))
INTO l_resource_code
FROM pa_resource_list_members
WHERE resource_list_member_id = p_resource_list_member_id;
SELECT DECODE(fc_res_type_code,
'EXPENDITURE_TYPE',expenditure_type,
'EXPENDITURE_CATEGORY',expenditure_category,
'EVENT_TYPE',event_type,
'REVENUE_CATEGORY',revenue_category)
INTO l_fin_category_code
FROM pa_resource_list_members
WHERE RESOURCE_LIST_MEMBER_ID = p_resource_list_member_id;
SELECT DECODE(incurred_by_res_flag,'N','',
nvl(to_char(person_id),nvl(to_char(job_id),
nvl(person_type_code,
nvl(to_char(incur_by_role_id),
nvl(incur_by_res_class_code, 'ERROR'))))))
INTO l_incur_by_res_code
FROM pa_resource_list_members
WHERE resource_list_member_id = p_resource_list_member_id;
SELECT b.res_type_code
INTO l_res_type_code
FROM pa_res_formats_b a, pa_res_types_b b
WHERE a.RES_TYPE_ID = b.RES_TYPE_ID
AND a.RES_TYPE_ENABLED_FLAG = 'Y'
AND a.res_format_id = p_res_format_id;
Select DECODE(incurred_by_res_flag, 'Y','',
DECODE(res_type_code,
'NAMED_PERSON',to_char(person_id),
'PERSON_TYPE', person_type_code,
'JOB', to_char(job_id),
'BOM_LABOR', to_char(bom_resource_id),
'RESOURCE_CLASS',resource_class_code,
'NON_LABOR_RESOURCE',non_labor_resource,
'BOM_EQUIPMENT',to_char(bom_resource_id),
'INVENTORY_ITEM',to_char(inventory_item_id),
'ITEM_CATEGORY',to_char(item_category_id)))
INTO l_resource_code
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT DECODE(incurred_by_res_flag,'N','',
nvl(to_char(person_id),nvl(to_char(job_id), nvl(person_type_code,
nvl(to_char(incur_by_role_id),
nvl(incur_by_res_class_code, NULL))))))
INTO l_incur_by_res_code
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT DECODE(fc_res_type_code,
'EXPENDITURE_TYPE',expenditure_type,
'EXPENDITURE_CATEGORY',expenditure_category,
'EVENT_TYPE',event_type,
'REVENUE_CATEGORY',revenue_category_code)
INTO l_fin_category_code
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT distinct ORGANIZATION_ID
INTO x_organization_id
FROM pa_all_organizations
WHERE INACTIVE_DATE IS NULL
AND PA_ORG_USE_TYPE in ('EXPENDITURES', 'PROJECTS')
AND ORGANIZATION_ID = l_organization_id;
SELECT vendor_id
FROM po_vendors
WHERE vendor_name = p_supplier_name;
SELECT vendor_id
INTO x_supplier_id
FROM PO_Vendors
WHERE vendor_id = p_supplier_id;
SELECT vendor_id
INTO x_supplier_id
FROM po_vendors
WHERE vendor_name = p_supplier_name;
SELECT 'Y'
INTO l_cwk_flag
--FROM per_people_x
FROM per_all_people_f
WHERE person_id = p_person_id
AND current_npw_flag = 'Y'
AND trunc(sysdate) between trunc(effective_start_date) AND
trunc(effective_end_date);
* And selecting from per_people_x to keep it
* consistent with the other selects.
***************************************************/
Cursor c_ids IS
SELECT person_id
--FROM per_all_people_f
FROM per_people_x
WHERE full_name = p_person_name;
SELECT person_id
INTO l_person_id
FROM per_people_x
WHERE person_id = p_person_id;
SELECT person_id
INTO l_person_id
FROM per_people_x
WHERE full_name = p_person_name;
* This Select has been moved from the Main Body to this
* Procedure. As any other Prog's calling the Check_
* PersonName_or_id check would need this cond to be present.
* *****************************************************/
/******************************************************
* Bug - 3523947
* Desc - Modified the below select. We no longer need the
* check where sysdate between effective start and
* end date.
*****************************************************/
BEGIN
SELECT person_id
INTO x_person_id
FROM per_all_people_f per
WHERE
--sysdate BETWEEN effective_start_date AND effective_end_date
(current_employee_flag = 'Y' or CURRENT_NPW_FLAG = 'Y')
AND ((PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'Y') OR
((PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N') AND
fnd_profile.value('PER_BUSINESS_GROUP_ID') =
BUSINESS_GROUP_ID))
AND person_id = l_person_id
and rownum = 1;
SELECT job_id
FROM per_jobs
WHERE name = p_job_name
AND job_group_id = p_job_group_id;
SELECT job_id
INTO x_job_id
FROM per_jobs
WHERE job_id = p_job_id
AND job_group_id = p_job_group_id;
SELECT job_id
INTO x_job_id
FROM per_jobs
WHERE name = p_job_name
AND job_group_id = p_job_group_id;
SELECT b.resource_id
FROM bom_resources b
WHERE b.resource_type = p_res_type_code
and NVL(b.disable_date,SYSDATE) >= SYSDATE
and b.expenditure_type is NOT NULL
and b.resource_code = p_bom_eqlabor_name;
SELECT b.resource_id
INTO x_bom_resource_id
FROM bom_resources b
WHERE b.resource_type = p_res_type_code
AND nvl(b.disable_date,SYSDATE) >= SYSDATE
AND b.expenditure_type IS NOT NULL
AND b.resource_id = p_bom_eqlabor_id;
SELECT b.resource_id
INTO x_bom_resource_id
FROM bom_resources b
WHERE b.resource_type = p_res_type_code --2
AND NVL(b.disable_date,SYSDATE) >= SYSDATE
AND b.expenditure_type IS NOT NULL
AND b.resource_code = p_bom_eqlabor_name;
SELECT c.category_id
FROM mtl_categories_b c, mtl_category_set_valid_cats I
WHERE i.category_set_id = p_item_category_set_id
AND i.category_id = c.category_id
AND nvl(c.disable_date,sysdate) >= sysdate
AND fnd_Flex_ext.GET_SEGS('INV', 'MCAT', c.structure_id, c.category_id) =
p_item_cat_name;
SELECT c.category_id
INTO x_item_category_id
FROM mtl_categories_b c, mtl_category_set_valid_cats I
WHERE i.category_set_id = p_item_category_set_id
AND i.category_id = c.category_id
AND NVL(c.disable_date,sysdate) >= sysdate
AND c.category_id = p_item_cat_id;
SELECT c.category_id
INTO x_item_category_id
FROM mtl_categories_b c, mtl_category_set_valid_cats I
WHERE i.category_set_id = p_item_category_set_id
AND i.category_id = c.category_id
AND nvl(c.disable_date,sysdate) >= sysdate
AND fnd_Flex_ext.GET_SEGS('INV', 'MCAT', c.structure_id, c.category_id) = p_item_cat_name;
* Desc - Modified the cursor to select based on
* the segment1 field instead of the
* description field. Also we dont need a join
* with the MTL_SYSTEM_ITEMS_tl table.
*******************************************/
CURSOR c_ids IS
SELECT b.INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_b b
WHERE b.organization_id = p_item_master_id
AND b.ENABLED_FLAG = 'Y'
--AND b.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID
--AND b.organization_id = t.organization_id
--AND t.language = userenv('LANG')
AND b.segment1 = p_item_name;
* Desc - Modified the below select. We dont need
* a join to the mtl_system_items_tl
* table.
*******************************************/
SELECT b.INVENTORY_ITEM_ID
INTO x_item_id
FROM MTL_SYSTEM_ITEMS_b b
WHERE b.organization_id = p_item_master_id
AND b.ENABLED_FLAG = 'Y'
--AND b.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID
--AND b.organization_id = t.organization_id
--AND t.language = userenv('LANG')
AND b.inventory_item_id = p_item_id;
* Desc - Modified the below select to be based on
* the segment1 field instead of the
* description field. Also we dont need a join
* with the MTL_SYSTEM_ITEMS_tl table.
*******************************************/
SELECT b.INVENTORY_ITEM_ID
INTO x_item_id
FROM MTL_SYSTEM_ITEMS_b b
WHERE b.organization_id = p_item_master_id
AND b.ENABLED_FLAG = 'Y'
--AND b.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID
--AND b.organization_id = t.organization_id
--AND t.language = userenv('LANG')
AND b.segment1 = p_item_name;
SELECT lookup_code
INTO x_person_type_code
FROM pa_lookups
WHERE lookup_type = 'PA_PERSON_TYPE'
AND lookup_code = p_resource_code;
SELECT non_labor_resource
INTO x_non_labor_resource
FROM pa_non_labor_resources
WHERE non_labor_resource = p_resource_name;
SELECT def.item_category_set_id
INTO l_item_category_set_id
FROM pa_plan_res_defaults def
-- , pa_resource_classes_b cl
WHERE def.resource_class_id = 3
AND def.object_type ='CLASS';
SELECT def.item_master_id
INTO l_item_master_id
FROM pa_plan_res_defaults def
-- , pa_resource_classes_b cl
WHERE def.resource_class_id = 3
AND def.object_type ='CLASS';
* res_temp.expenditure_type and the final update is not
* required while updating the expenditure_type value.
* Also in the where clause we are checking for
* res_temp.expenditure_type IS NULL
**********************************************************/
PROCEDURE Default_Expenditure_Type
IS
BEGIN
/*************************************************
* Update to use when the expenditure_type is null
* and bom_resource_id is not null. We use the below
* Update to set the value for expenditure_type
* ************************************************/
--Bug 3615477
--Also updating the value of fc_res_type_code.
--Bug 3628429
--Join to the pa_expenditure_types table.
-- Fixed bug 3962699 - Removed the setting of fc_res_type_code from
-- all the below selects. No need to set fc res type code - only
-- need to set Exp Type.
UPDATE pa_res_members_temp res_temp
SET res_temp.expenditure_type =
(SELECT exp.expenditure_type
FROM bom_resources bom, pa_expenditure_types exp
WHERE bom.resource_id = res_temp.bom_resource_id
AND exp.expenditure_type = bom.expenditure_type
AND exp.UNIT_OF_MEASURE = 'HOURS'
AND ROWNUM = 1)
WHERE res_temp.expenditure_type IS NULL
AND res_temp.bom_resource_id IS NOT NULL;
* Update to use when the expenditure_type is null
* and non_labor_resource is not null. We use the below
* Update to set the value for expenditure_type
*************************************************/
--Bug 3615477
--Also updating the value of fc_res_type_code.
UPDATE pa_res_members_temp res_temp
SET res_temp.expenditure_type =
(SELECT n.expenditure_type
FROM pa_non_labor_resources n
WHERE n.non_labor_resource =
res_temp.non_labor_resource
AND ROWNUM = 1)
WHERE res_temp.expenditure_type IS NULL
AND res_temp.non_labor_resource IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET res_temp.expenditure_type =
PJM_COMMITMENT_UTILS.MTL_EXPENDITURE_TYPE(
res_temp.organization_id, res_temp.inventory_item_id)
WHERE res_temp.organization_id IS NOT NULL
AND res_temp.inventory_item_id IS NOT NULL
AND res_temp.expenditure_type IS NULL;
* res_temp.rate_expenditure_type and the final update is
* required while updating the rate_expenditure_type value.
* Also in the where condition we are checking for
* res_temp.rate_expenditure_type IS NULL
**********************************************************/
PROCEDURE Default_Rate_Expenditure_Type
IS
l_resource_class_flag PA_RESOURCE_LISTS_ALL_BG.resource_class_flag%TYPE := 'Y'; --CBS
* Update to use when the rate_expenditure_type is null
* and bom_resource_id is not null. We use the below
* Update to set the value for rate_expenditure_type
* ************************************************/
--Bug 3628429
--Join to the pa_expenditure_types table.
UPDATE pa_res_members_temp res_temp
SET res_temp.rate_expenditure_type =
(SELECT exp.expenditure_type
FROM bom_resources bom,pa_expenditure_types exp
WHERE bom.resource_id = res_temp.bom_resource_id
AND exp.expenditure_type = bom.expenditure_type
AND exp.UNIT_OF_MEASURE = 'HOURS'
AND ROWNUM = 1)
WHERE res_temp.rate_expenditure_type IS NULL
AND res_temp.bom_resource_id IS NOT NULL;
* Update to use when the rate_expenditure_type is null
* and non_labor_resource is not null. We use the below
* Update to set the value for rate_expenditure_type
*************************************************/
UPDATE pa_res_members_temp res_temp
SET res_temp.rate_expenditure_type =
(SELECT n.expenditure_type
FROM pa_non_labor_resources n
WHERE n.non_labor_resource =
res_temp.non_labor_resource
AND ROWNUM = 1)
WHERE res_temp.rate_expenditure_type IS NULL
AND res_temp.non_labor_resource IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET res_temp.rate_expenditure_type =
PJM_COMMITMENT_UTILS.MTL_EXPENDITURE_TYPE(
res_temp.organization_id, res_temp.inventory_item_id)
WHERE res_temp.organization_id IS NOT NULL
AND res_temp.inventory_item_id IS NOT NULL
AND res_temp.rate_expenditure_type IS NULL;
* Final Update which will default the value of
* rate_expenditure_type based on the resource class id
* **************************************************/
--CBS Changes Start here
begin
select nvl(prlab.resource_class_flag,'Y') into l_resource_class_flag
from PA_RESOURCE_LISTS_ALL_BG prlab,
PA_RESOURCE_LIST_MEMBERS prlm,
pa_res_members_temp prmt
where prlab.resource_list_id = prlm.resource_list_id and
prlm.resource_list_member_id = prmt.resource_list_member_id and
rownum = 1;
UPDATE pa_res_members_temp res_temp
SET res_temp.rate_expenditure_type = (select expenditure_type_2
from PA_RESOURCE_LIST_MEMBERS where
resource_list_member_id = res_temp.resource_list_member_id)
WHERE (res_temp.rate_expenditure_type IS NULL OR
(res_temp.rate_expenditure_type = 'NO Val'));
UPDATE pa_res_members_temp res_temp
SET res_temp.rate_expenditure_type =
(SELECT expenditure_type
FROM pa_plan_res_defaults
WHERE resource_class_id =
res_temp.resource_class_id
AND ROWNUM = 1
AND object_type = 'CLASS')
WHERE (res_temp.rate_expenditure_type IS NULL OR
(res_temp.rate_expenditure_type = 'NO Val'))
AND res_temp.resource_class_id IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET org_id = (SELECT to_number(org_information1)
FROM hr_organization_information
WHERE org_information_context = 'Exp Organization Defaults'
AND organization_id = res_temp.organization_id
AND rownum = 1)
WHERE res_temp.organization_id IS NOT NULL
AND res_temp.org_id IS NULL;
SELECT org_id
INTO l_proj_ou
FROM pa_projects_all
WHERE project_id = p_project_id
AND rownum = 1;
UPDATE pa_res_members_temp res_temp
SET org_id = l_proj_ou
WHERE res_temp.org_id IS NULL;
* resource_class_code. Update the UOM in the table with the appr
* value.
* ******************************************************/
PROCEDURE Default_UOM
IS
BEGIN
/*********************************************************
* If the resource_class code in the table is 'PEOPLE' or EQUIPMENT
* Then by default the Unit of measure should only be 'HOURS'
*************************************************************/
UPDATE pa_res_members_temp res_temp
SET unit_of_measure = 'HOURS'
WHERE res_temp.resource_class_code IN ('PEOPLE', 'EQUIPMENT')
AND res_temp.unit_of_measure IS NULL;
UPDATE pa_res_members_temp res_temp
SET unit_of_measure = (SELECT primary_uom_code
FROM mtl_system_items_b items
WHERE items.inventory_item_id =
res_temp.inventory_item_id
AND items.organization_id =
res_temp.organization_id
AND ROWNUM = 1)
WHERE res_temp.resource_class_code = 'MATERIAL_ITEMS'
AND res_temp.unit_of_measure IS NULL
AND res_temp.organization_id IS NOT NULL
AND res_temp.inventory_item_id IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET unit_of_measure = (SELECT primary_uom_code
FROM mtl_system_items_b items
WHERE items.inventory_item_id =
res_temp.inventory_item_id
AND items.organization_id =
(SELECT def.item_master_id
FROM pa_resource_classes_b cls,
pa_plan_res_defaults def
WHERE cls.resource_class_code = 'MATERIAL_ITEMS'
AND cls.resource_class_id = def.resource_class_id
AND def.object_type = 'CLASS')
AND ROWNUM = 1)
WHERE res_temp.resource_class_code = 'MATERIAL_ITEMS'
AND res_temp.unit_of_measure IS NULL
AND res_temp.inventory_item_id IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET res_temp.unit_of_measure = (SELECT unit_of_measure
FROM pa_expenditure_types et
WHERE et.expenditure_type = res_temp.expenditure_type
AND ROWNUM = 1)
WHERE res_temp.resource_class_code IN
('MATERIAL_ITEMS', 'FINANCIAL_ELEMENTS')
AND res_temp.inventory_item_id IS NULL
AND res_temp.unit_of_measure IS NULL
AND res_temp.expenditure_type IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET res_temp.unit_of_measure = 'DOLLARS'
WHERE res_temp.unit_of_measure IS NULL;
UPDATE pa_res_members_temp res_temp
SET res_temp.vendor_id =
(SELECT asgn.vendor_id
FROM per_all_assignments_f asgn
WHERE asgn.person_id = res_temp.person_id
AND asgn.primary_flag = 'Y'
AND asgn.assignment_type = 'C'
AND trunc(sysdate) BETWEEN
asgn.effective_start_date AND
asgn.effective_end_date
AND ROWNUM = 1)
WHERE res_temp.vendor_id IS NULL
-- Fix for bug 3940856 - get supplier for all cwk
-- AND res_temp.incurred_by_res_flag = 'N'
AND res_temp.person_id IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET rate_func_curr_code = (
SELECT FC.Currency_Code
FROM FND_CURRENCIES FC,
GL_SETS_OF_BOOKS GB,
PA_IMPLEMENTATIONS_all IMP
-- Bug 4656920 - removed nvl on imp.org_id for R12 MOAC
WHERE imp.org_id = nvl(res_temp.org_id, -99)
AND IMP.Set_Of_Books_ID = GB.Set_Of_Books_ID
AND FC.Currency_Code =
DECODE(IMP.Set_Of_Books_ID, NULL,NULL,GB.CURRENCY_CODE));
UPDATE pa_res_members_temp res_temp
SET rate_func_curr_code = unit_of_measure
WHERE inventory_item_id IS NOT NULL
AND rate_based_flag = 'N';
UPDATE pa_res_members_temp res_temp
SET unit_of_measure = 'DOLLARS'
WHERE inventory_item_id IS NOT NULL
AND rate_based_flag = 'N';
UPDATE pa_res_members_temp res_temp
SET job_id = (SELECT job_id
FROM per_all_assignments_f assn
WHERE assn.person_id = res_temp.person_id
AND SYSDATE BETWEEN assn.effective_start_date
AND assn.effective_end_date
AND assn.assignment_type in ('C','E')
AND assn.primary_flag = 'Y'
AND ROWNUM = 1)
WHERE res_temp.job_id IS NULL
AND res_temp.person_id IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET job_id = (SELECT default_job_id
FROM pa_project_role_types_vl role
WHERE role.project_role_id = res_temp.project_role_id
AND ROWNUM = 1)
WHERE res_temp.job_id IS NULL
AND res_temp.project_role_id IS NOT NULL;
UPDATE pa_res_members_temp res_temp
SET person_type_code = (SELECT
decode(peo.current_employee_flag, 'Y', 'EMP', 'CWK')
FROM per_all_people_f peo
WHERE peo.person_id = res_temp.person_id
AND SYSDATE BETWEEN peo.effective_start_date
AND peo.effective_end_date
AND ROWNUM = 1)
WHERE res_temp.person_type_code IS NULL
-- AND res_temp.incurred_by_res_flag = 'N' -- Bug 3827566
AND res_temp.person_id IS NOT NULL;
* All of the below updates will only fire for
* organization_id being null in the temp table.
* If it is not null....then dont do anything.
* ****************************************************/
/**************************************************
* This Update will fire when the incurred_by_res_flag is Yes or No
* and person_id IS NOT NULL.
* We are updating the value for organization_id = derived value
* based on person's HR org.
* ***************************************************/
UPDATE pa_res_members_temp res_temp
SET organization_id = (SELECT a.organization_id
FROM per_assignments_x a,
pa_all_organizations org
WHERE a.person_id = res_temp.person_id
AND a.organization_id = org.organization_id
AND org.inactive_date is null
AND org.pa_org_use_type = 'EXPENDITURES'
AND a.assignment_type in ('C','E')
AND a.primary_flag = 'Y'
AND ROWNUM = 1)
WHERE res_temp.person_id IS NOT NULL
AND res_temp.organization_id IS NULL;
* This Update will fire when the incurred_by_res_flag = 'N'
* and bom_resource_id IS NOT NULL and organization_id IS NULL.
* We are deriving the value for organization_id
* ***************************************************/
UPDATE pa_res_members_temp res_temp
SET organization_id = (SELECT b.organization_id
FROM bom_resources b
WHERE b.resource_id = res_temp.bom_resource_id
AND ROWNUM = 1)
WHERE res_temp.incurred_by_res_flag = 'N'
AND res_temp.bom_resource_id IS NOT NULL
AND res_temp.organization_id IS NULL;
* This Update will fire when the incurred_by_res_flag = 'N'
* and inventory_item_id IS NOT NULL and organization_id IS NULL.
* We are deriving the value for organization_id
* ***************************************************/
UPDATE pa_res_members_temp res_temp
SET organization_id = (SELECT i.organization_id
FROM mtl_system_items_b i
WHERE i.inventory_item_id = res_temp.inventory_item_id
AND i.organization_id =
(SELECT def.item_master_id
FROM pa_resource_classes_b cls,
pa_plan_res_defaults def
WHERE cls.resource_class_code = 'MATERIAL_ITEMS'
AND cls.resource_class_id = def.resource_class_id
AND def.object_type = 'CLASS')
AND ROWNUM = 1)
WHERE res_temp.incurred_by_res_flag = 'N'
AND res_temp.inventory_item_id IS NOT NULL
AND res_temp.organization_id IS NULL;
* This Update will fire for all resources if the organization_id is null
* We are deriving the value for organization_id from the project
* ***************************************************/
BEGIN
SELECT proj.carrying_out_organization_id
INTO l_organization_id
FROM pa_projects_all proj,
pa_all_organizations org
WHERE proj.project_id = p_project_id
AND proj.carrying_out_organization_id = org.organization_id
AND org.inactive_date is null
AND org.pa_org_use_type = 'EXPENDITURES'
AND ROWNUM = 1;
UPDATE pa_res_members_temp res_temp
SET organization_id = l_organization_id
WHERE res_temp.organization_id IS NULL;
* First Update all the rows no 'N' as by
* default it should be N and not null.
******************************************/
UPDATE pa_res_members_temp res_temp
SET rate_based_flag = 'N';
* Update it to 'Y' for resource_class_code
* in 'PEOPLE' or 'EQUIPMENT'
***********************************************/
UPDATE pa_res_members_temp res_temp
SET rate_based_flag = 'Y'
WHERE res_temp.resource_class_code in ('PEOPLE','EQUIPMENT');
UPDATE pa_res_members_temp res_temp
SET rate_based_flag = 'Y'
WHERE res_temp.resource_class_code
= 'MATERIAL_ITEMS'
AND res_temp.inventory_item_id IS NOT NULL
AND NOT EXISTS (select 'Y'
from mtl_system_items_b item,
mtl_units_of_measure meas
where item.inventory_item_id =
res_temp.inventory_item_id
and item.organization_id = res_temp.organization_id -- bug 10121923
and item.primary_uom_code = meas.uom_code
and meas.uom_class = 'Currency');
* Modified the existing update statement to include the foll:
* - 'MATERIAL_ITEMS' in res_class_code.
* - Added an nvl condition to derive based on the
* rate expenditure type if the expenditure type is null
* - added an extra condition to check that either one of
* expenditure type or rate expenditure type should not be null.
* ********************************************************/
UPDATE pa_res_members_temp res_temp
SET rate_based_flag =
(SELECT c.cost_rate_flag
FROM pa_expenditure_types c
WHERE c.expenditure_type = res_temp.expenditure_type)
-- nvl(res_temp.expenditure_type, Bug 3586021
-- res_temp.rate_expenditure_type))
WHERE res_temp.resource_class_code
in ('MATERIAL_ITEMS', 'FINANCIAL_ELEMENTS')
--Added the below cond so that it does not override prev upd.
AND res_temp.inventory_item_id IS NULL
AND res_temp.expenditure_type IS NOT NULL;
DELETE FROM pa_res_members_temp;
DELETE FROM pa_res_member_id_temp;
* First insert into the pa_res_member_id_temp table just the
* resource_list_member_id which have been passed as IN param's
**************************************************************/
FOR i in p_resource_list_members.first..p_resource_list_members.last
LOOP
INSERT INTO pa_res_member_id_temp
(resource_list_member_id,
order_id)
VALUES(p_resource_list_members(i),
i);
* pa_res_members_temp table. And in the below insert
* we are populating value into it, same as the order
* id in pa_res_members_id_temp.
*********************************************************/
INSERT INTO pa_res_members_temp
(resource_list_member_id,
order_id,
resource_class_flag ,
resource_class_code ,
resource_class_id ,
--Added column newly
res_type_code ,
person_id ,
job_id ,
person_type_code ,
named_role ,
bom_resource_id ,
non_labor_resource ,
inventory_item_id ,
item_category_id ,
project_role_id ,
organization_id ,
fc_res_type_code ,
expenditure_type ,
expenditure_category ,
Event_type ,
revenue_category ,
vendor_id ,
spread_curve_id ,
etc_method_code ,
mfc_cost_type_id ,
incurred_by_res_flag ,
incur_by_res_class_code ,
incur_by_role_id ,
unit_of_measure ,
org_id ,
rate_based_flag ,
rate_expenditure_type ,
rate_func_curr_code ,
rate_incurred_by_org_id )
SELECT /*+ ORDERED */
a.resource_list_member_id ,
b.order_id,
a.resource_class_flag ,
a.resource_class_code ,
a.resource_class_id ,
typ.res_type_code ,
a.person_id ,
a.job_id ,
a.person_type_code ,
a.team_role ,
a.bom_resource_id ,
a.non_labor_resource ,
a.inventory_item_id ,
a.item_category_id ,
a.project_role_id ,
a.organization_id ,
a.fc_res_type_code ,
a.expenditure_type ,
a.expenditure_category ,
a.Event_type ,
a.revenue_category ,
a.vendor_id ,
a.spread_curve_id ,
a.etc_method_code ,
a.mfc_cost_type_id ,
a.incurred_by_res_flag ,
a.incur_by_res_class_code ,
a.incur_by_role_id ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL
FROM pa_res_member_id_temp b,
pa_resource_list_members a,
pa_res_formats_b fmt,
pa_res_types_b typ
WHERE a.resource_list_member_id = b.resource_list_member_id
AND a.res_format_id = fmt.res_format_id
AND fmt.res_type_id = typ.res_type_id(+);
* as Null. We have however fixed the issue with the below Update.
* This Update will fire when if the res_type_code is still null
* and the incurred_by_res_flag='Y'
****************************************************************/
UPDATE pa_res_members_temp res_temp
SET res_type_code = DECODE(res_temp.person_id, NULL,
DECODE(res_temp.job_id, NULL,
DECODE(res_temp.person_type_code, NULL,
DECODE(res_temp.incur_by_role_id, NULL,
DECODE(res_temp.incur_by_res_class_code, NULL,
NULL, 'RESOURCE_CLASS'),
'ROLE'),
'PERSON_TYPE'),
'JOB'),
'NAMED_PERSON')
WHERE incurred_by_res_flag = 'Y'
AND res_type_code IS NULL;
* Call the Procedure Default_job which will Update the Job ID
* in the temp table pa_res_members_temp with the correct
* value.
* *****************************************************************/
pa_planning_resource_utils.default_job;
* Call the Procedure default_person_type which will Update the
* person_type_code in the temp table pa_res_members_temp with the correct
* value.
* *****************************************************************/
pa_planning_resource_utils.default_person_type;
* Update the organization_id and rate_incurred_by_org_id
* in the temp table pa_res_members_temp with the correct
* values.
* *****************************************************************/
pa_planning_resource_utils.default_organization(
p_project_id => p_project_id);
* Update the expenditure_type
* in the temp table pa_res_members_temp with the correct
* values.
* *****************************************************************/
pa_planning_resource_utils.default_expenditure_type;
* Update the rate_expenditure_type
* in the temp table pa_res_members_temp with the correct
* values.
* *****************************************************************/
pa_planning_resource_utils.default_rate_expenditure_type;
* Update the vendor_id
* in the temp table pa_res_members_temp with the correct
* values.
* *****************************************************************/
pa_planning_resource_utils.default_supplier;
* Update the Rate_based_flag
* in the temp table pa_res_members_temp with the correct
* values.
* *****************************************************************/
pa_planning_resource_utils.default_rate_based;
* Update the OU
* in the temp table pa_res_members_temp with the correct
* values.
*****************************************************************/
pa_planning_resource_utils.default_ou(p_project_id);
* Update the Unit_of_measure
* in the temp table pa_res_members_temp with the correct
* values.
*****************************************************************/
pa_planning_resource_utils.default_uom;
* Update the Rate_Func_Curr_Code
* in the temp table pa_res_members_temp with the correct
* values.
*****************************************************************/
pa_planning_resource_utils.default_currency_code;
* table into the out var's. Select it from the Temp table
* pa_res_members_temp and Bulk collect it into the OUT var's.
*********************************************************************/
/**********************************************************
* Added an extra order ID join between the pa_res_members_temp
* and pa_res_member_id_temp table to keep in sync.
* Bug - 3473425
**********************************************************/
/*********************************************************
* Bug - 3473324
* Desc - If the incurred_by res_flag was 'N' or Null then we would
* populate the res_type_code into the x_res_type_code parameter.
* If the incur_by_res_flag was 'Y' then we would populate the
* res_type_code into the x_incur_by_res_type parameter.
********************************************************/
SELECT
a.RESOURCE_CLASS_FLAG,
a.RESOURCE_CLASS_CODE,
a.RESOURCE_CLASS_ID,
decode(a.incurred_by_res_flag,'Y',Null,a.RES_TYPE_CODE),
decode(a.incurred_by_res_flag,'Y',a.RES_TYPE_CODE,Null),
a.PERSON_ID,
a.JOB_ID,
a.PERSON_TYPE_CODE,
a.NAMED_ROLE ,
a.BOM_RESOURCE_ID,
a.NON_LABOR_RESOURCE,
a.INVENTORY_ITEM_ID,
a.ITEM_CATEGORY_ID,
a.PROJECT_ROLE_ID,
a.ORGANIZATION_ID,
a.FC_RES_TYPE_CODE,
a.EXPENDITURE_TYPE,
a.EXPENDITURE_CATEGORY,
a.EVENT_TYPE,
a.REVENUE_CATEGORY,
a.VENDOR_ID,
a.SPREAD_CURVE_ID,
a.ETC_METHOD_CODE,
a.MFC_COST_TYPE_ID,
a.INCURRED_BY_RES_FLAG,
a.INCUR_BY_RES_CLASS_CODE,
a.INCUR_BY_ROLE_ID,
a.UNIT_OF_MEASURE,
a.ORG_ID,
a.RATE_BASED_FLAG,
a.RATE_EXPENDITURE_TYPE,
a.RATE_FUNC_CURR_CODE
--a.RATE_INCURRED_BY_ORG_ID
BULK COLLECT INTO
x_resource_class_flag ,
x_resource_class_code ,
x_resource_class_id ,
x_res_type_code ,
x_incur_by_res_type ,
x_person_id ,
x_job_id ,
x_person_type_code ,
x_named_role ,
x_bom_resource_id ,
x_non_labor_resource ,
x_inventory_item_id ,
x_item_category_id ,
x_project_role_id ,
x_organization_id ,
x_fc_res_type_code ,
x_expenditure_type ,
x_expenditure_category ,
x_event_type ,
x_revenue_category_code ,
x_supplier_id ,
x_spread_curve_id ,
x_etc_method_code ,
x_mfc_cost_type_id ,
x_incurred_by_res_flag ,
x_incur_by_res_class_code ,
x_incur_by_role_id ,
x_unit_of_measure ,
x_org_id ,
x_rate_based_flag ,
x_rate_expenditure_type ,
x_rate_func_curr_code
-- x_rate_incurred_by_org_id
FROM pa_res_members_temp a,
pa_res_member_id_temp b
WHERE a.resource_list_member_id = b.resource_list_member_id
AND a.order_id = b.order_id
ORDER BY b.order_id;
DELETE FROM pa_res_members_temp;
DELETE FROM pa_res_member_id_temp;
* Null then select the full_name.
*************************************************/
Cursor c_People (P_Person_Id IN Number) is
Select
--nvl(known_as,Full_Name)
--Bug 3485392
Full_Name
From
Per_People_X
Where
Person_Id = P_Person_Id
And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Business_Group_Id)
OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
Select
Name
From
Per_Jobs
Where
Job_Id = P_Job_Id
And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Per_Jobs.Business_Group_Id )
OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
Select
Meaning
From
pa_lookups
Where
Lookup_Type = 'PA_PERSON_TYPE'
And Lookup_Code = P_Person_Type;
Select
Resource_code
From
Bom_Resources
Where
Resource_Id = P_BOM_Res_Id;
Select fnd_Flex_ext.GET_SEGS('INV', 'MCAT', c.structure_id, c.category_id)
From mtl_categories_v c
Where c.Category_Id = P_Item_Cat_Id;
* Desc - Instead of the description we are now selecting the
* segment1 field from Mtl_System_Items_b table as the
* resource name.
**********************************************************/
Cursor c_Inven_Item (P_Inven_Item_Id IN Number ) Is
Select
segment1
From
Mtl_System_Items_b
Where
--Language = USERENV('LANG')
Inventory_Item_Id = P_Inven_Item_Id
and 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');
Select
Name
From
Pa_Resource_Classes_Vl
Where
Resource_Class_Id = P_Res_Class_Id;
Select
Meaning
From
Pa_Project_Role_Types_vl
Where
Project_Role_Id = P_Prj_Role_Id;
Select
a.Res_Type_Code Res_Type_Code,
a.Person_Id Person_Id,
a.Job_Id Job_Id,
a.Bom_Resource_Id Bom_Resource_Id,
a.Inventory_Item_Id Inventory_Item_Id,
a.Item_Category_Id Item_Category_Id,
a.Person_Type_Code Person_Type_Code,
a.Non_Labor_Resource Non_Labor_Resource,
a.incurred_by_res_flag incurred_by_res_flag,
c.Resource_Class_Id
From
Pa_Resource_Assignments a
,Pa_Resource_Classes_b c
Where
c.Resource_Class_Code = a.Resource_Class_Code
And a.Resource_Assignment_Id = P_Res_Assignment_Id;
Select
Fc_Res_Type_Code,
Expenditure_Type,
Expenditure_Category,
Event_Type,
Revenue_Category_Code
From
Pa_Resource_Assignments
Where
Resource_Assignment_Id = P_Res_Assignment_Id;
SELECT lk.Meaning
INTO l_Fin_Cat_Displayed
FROM PA_LOOKUPS lk
WHERE lk.Lookup_Type = 'REVENUE CATEGORY'
and lk.lookup_code = l_revenue_category_code;
Select
Vendor_Name
From
Po_Vendors
Where
Vendor_id = P_Vendor_Id;
Select
Meaning
From
Pa_Project_Role_Types_vl
Where
Project_Role_Id = P_Prj_Role_Id;
Select
Full_Name
From
Per_People_X
Where
Person_Id = P_Person_Id
And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Business_Group_Id)
OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
Select
Name
From
Per_Jobs
Where
Job_Id = P_Job_Id
And ( (Pa_Cross_Business_Grp.IsCrossBGProfile = 'N' AND
Fnd_Profile.Value('PER_BUSINESS_GROUP_ID') = Business_Group_Id )
OR Pa_Cross_Business_Grp.IsCrossBGProfile = 'Y');
Select
Meaning
From
hr_lookups
Where
Lookup_Type = 'PERSON_TYPE'
And Lookup_Code = P_Person_Type;
Select
Name
From
Pa_Resource_Classes_Vl
Where
Resource_Class_Code = P_Resource_Class_Code;
Select
Meaning
From
Pa_Project_Role_Types_vl
Where
Project_Role_Id = P_Prj_Role_Id;
Select
Person_Id,
Job_Id,
Incur_By_Role_Id,
Person_Type_Code,
Incur_By_Res_Class_Code
From
Pa_Resource_Assignments
Where
Resource_Assignment_Id = P_Res_Assignment_Id;
Select
Incurred_By_Res_Flag
From
Pa_Resource_Assignments
Where
Resource_Assignment_Id = P_Res_Assignment_Id;
Select
f.Res_Type_Enabled_Flag,
f.Orgn_Enabled_Flag,
f.Fin_Cat_Enabled_Flag,
f.Incurred_By_Enabled_Flag,
f.Supplier_Enabled_Flag,
f.Role_Enabled_Flag,
f.Resource_Class_Flag,
f.Res_Type_Id,
f.Resource_Type_Disp_Chars,
f.Orgn_Disp_Chars,
f.Fin_Cat_Disp_Chars,
f.Incurred_By_Disp_Chars,
f.Supplier_Disp_Chars,
f.Role_Disp_Chars,
t.Res_Type_Code
From
Pa_Res_Formats_B f,
Pa_Res_Types_B t
Where
f.Res_Type_Id = t.Res_Type_Id(+)
And f.Res_Format_Id = P_Res_Format_Id;
Select
Res_Format_Id,
Person_Id,
Job_Id,
Organization_Id,
Vendor_Id,
Expenditure_Type,
Event_Type,
Non_Labor_Resource,
Expenditure_Category,
Revenue_Category,
Non_Labor_Resource_Org_Id,
Project_Role_Id,
Resource_Format_Id,
Resource_Class_Id,
Mfc_Cost_Type_Id,
Resource_Class_Flag,
Fc_Res_Type_Code,
Bom_Resource_Id,
Inventory_Item_Id,
Item_Category_Id,
Person_Type_Code,
team_role ,
Incurred_By_Res_Flag,
Incur_By_Res_Class_Code,
Incur_By_Role_Id
From
Pa_Resource_List_Members
Where
Resource_List_Member_Id = P_Res_List_Member_Id;
Select Full_Name
INTO l_Res_Type_Displayed
From Per_People_X
Where Person_Id = Res_List_Member_Rec.Person_Id;
SELECT nvl(known_as,Full_Name)
INTO l_Res_Incur_By_alias
FROM Per_People_X
WHERE Person_Id = Res_List_Member_Rec.Person_Id;
select nvl(prlab.resource_class_flag,'Y') INTO l_resource_class_flag from
PA_RESOURCE_LIST_MEMBERS prlm, PA_RESOURCE_LISTS_ALL_BG prlab
where prlm.resource_list_member_id = P_Resource_List_Member_Id and
prlm.resource_list_id = prlab.resource_list_id;
Select
'Y'
From
Pa_Event_Types
Where
Event_Type = P_Fin_Cat_Name
And Event_Type_Classification IN ('AUTOMATIC','MANUAL','WRITE OFF','WRITE ON')
And Decode(Pa_Get_Resource.Include_Inactive_Resources,
'Y',Start_Date_Active,
Trunc(SysDate)) Between Start_Date_Active
And Nvl(End_Date_Active,Trunc(SysDate));
Select
'Y'
From
Pa_Expenditure_Types
Where
lower(Expenditure_Type) = lower(P_Fin_Cat_Name)
And Decode(Pa_Get_Resource.Include_Inactive_Resources,
'Y',Start_Date_Active,
Trunc(SysDate)) Between Start_Date_Active
And Nvl(End_Date_Active,Trunc(SysDate));
Select
'Y'
From
Pa_Expenditure_Categories
Where
lower(Expenditure_Category) = lower(P_Fin_Cat_Name)
And Decode(Pa_Get_Resource.Include_Inactive_Resources,
'Y',Start_Date_Active,
Trunc(SysDate)) Between Start_Date_Active
And Nvl(End_Date_Active,Trunc(SysDate));
Select
Meaning
From
Pa_Lookups
Where
Lookup_Code = P_Fin_Cat_Code
And Lookup_Type = 'REVENUE CATEGORY';
Select
lookup_code
From
Pa_Lookups
Where
Meaning = P_Fin_Cat_Meaning
And Lookup_Type = 'REVENUE CATEGORY';
Select
'Y'
From
Pa_Expenditure_Types
Where
Unit_Of_Measure = 'HOURS'
And Expenditure_Type = P_Fin_Cat_Name;
SELECT 'Y'
FROM pa_projects_all
WHERE project_id = c_project_id
AND template_flag = 'Y';
SELECT budget_version_id
INTO l_budget_version_id
FROM pa_budget_versions
WHERE project_structure_version_id = l_wp_vers_id
AND wp_version_flag = 'Y';
SELECT MAX(bv.budget_version_id)
INTO l_budget_version_id
FROM pa_budget_versions bv
WHERE bv.fin_plan_type_id = p_fin_plan_type_id
AND bv.project_id = p_project_id
AND NVL(wp_version_flag,'N') = 'N'
AND NVL(bv.budget_status_code,'W') IN ('W','S');
Select
Resource_List_Member_Id,
Res_Format_Id
From
Pa_Resource_List_Members
Where (P_Plan_Res_List_Mem_Id is Not Null and
Resource_List_Member_Id = P_Plan_Res_List_Mem_Id) ;
Select migration_code
From Pa_Resource_List_Members
Where Resource_List_Member_Id = P_Res_List_Mem_Id;
Select
Res_Type_Id,
Res_Type_Enabled_Flag,
Orgn_Enabled_Flag,
Fin_Cat_Enabled_Flag,
Incurred_By_Enabled_Flag,
Supplier_Enabled_Flag,
Role_Enabled_flag,
Resource_Class_Flag
From
Pa_Res_Formats_B
Where
Res_Format_Id = P_Res_Format_Id;
select job_group_id
from pa_resource_lists_all_bg
where resource_list_id = p_res_list_id;
select 'Y'
from per_jobs
where job_group_id = p_job_group_id
and Job_ID = p_Job_ID;
select resource_list_id
from pa_resource_list_members
where resource_list_member_id = p_res_member_id;
select NVL(resource_class_flag,'Y') resource_class_flag
from pa_resource_lists_all_bg
where resource_list_id = x_p_resource_list_id;
/* bug 3436074 with the change update top this code is not needed.
Else
If P_Planning_Resource_Alias is Not Null Then
Open c_Res_List_Mem (P_Plan_Res_Alias => P_Planning_Resource_Alias);
SELECT 'Y'
INTO l_bom_combo_exists
FROM bom_resources b
WHERE b.resource_id = X_Bom_Resource_Id
AND b.organization_id = X_Organization_Id
AND ROWNUM = 1;
* (6a) Insert into Pa_res_list_map_tmp1 and then call
* Api pa_resource_mapping.map_resource_list.
* which will put the correct resource_list_member_id
* in pa_res_list_map_tmp4.
**************************************************************/
Function Derive_Resource_List_Member
(p_project_id IN NUMBER,
p_res_format_id IN NUMBER,
p_person_id IN NUMBER DEFAULT NULL,
p_job_id IN NUMBER DEFAULT NULL,
p_organization_id IN NUMBER DEFAULT NULL,
p_expenditure_type IN Varchar2 DEFAULT NULL,
p_expenditure_category IN Varchar2 DEFAULT NULL,
p_project_role_id IN Number DEFAULT NULL,
p_person_type_code IN Varchar2 DEFAULT NULL,
p_named_role IN Varchar2 DEFAULT NULL)
RETURN NUMBER
IS
CURSOR get_fmt_details (p_res_format_id NUMBER) IS
SELECT f.Res_Type_Enabled_Flag,
f.Orgn_Enabled_Flag,
f.Fin_Cat_Enabled_Flag,
f.Role_Enabled_Flag,
f.incurred_by_enabled_flag,
f.supplier_enabled_flag,
f.Res_Type_Id,
t.Res_Type_Code
FROM Pa_Res_Formats_B f,
Pa_Res_Types_B t
WHERE f.Res_Type_Id = t.Res_Type_Id(+)
AND f.Res_Format_Id = p_res_format_id;
SELECT control_flag
INTO l_central_control
FROM pa_resource_lists_all_bg
WHERE resource_list_id = l_resource_list_id;
* Constructing the Select Statement based on the values passed
* as parameters to this procedure.
***********************************************************/
l_stmt := 'SELECT resource_list_member_id '||
' FROM pa_resource_list_members ' ||
' WHERE resource_list_id = :resource_list_id' ||
' AND enabled_flag = ''Y'' ' ||
' AND res_format_id = :res_format_id';
* Parse the Select Stmt
************************************/
dbms_sql.parse(l_cursor,l_stmt,dbms_sql.native);
* If the select returns some Value then assign it to the
* l_resource_list_member_id.
* ********************************************************/
IF l_exists = 'Y' THEN
-- dbms_output.put_line('l_exists is Y');
DELETE FROM pa_res_list_map_tmp1;
DELETE FROM pa_res_list_map_tmp4;
INSERT INTO pa_res_list_map_tmp1
(person_id,
job_id,
organization_id,
expenditure_type,
expenditure_category,
fc_res_type_code,
project_role_id,
resource_class_id,
resource_class_code,
res_format_id,
person_type_code,
named_role)
VALUES
(p_person_id,
p_job_id,
p_organization_id,
p_expenditure_type,
p_expenditure_category,
l_fc_res_type_code,
p_project_role_id,
1,
'PEOPLE',
p_res_format_id,
p_person_type_code,
p_named_role);
* resource_list_member_id and insert it into the
* table pa_res_list_map_tmp4.
**************************************************/
-- dbms_output.put_line('Pa_Resource_Mapping.map_resource_list');
SELECT RESOURCE_LIST_MEMBER_ID
INTO l_res_list_member_id
FROM pa_res_list_map_tmp4
WHERE rownum = 1;
SELECT resource_class_id
INTO l_resource_class_id
FROM pa_res_formats_b
WHERE res_format_id = p_res_format_id;
SELECT job_group_id
INTO l_job_group_id
FROM pa_resource_lists_all_bg
WHERE resource_list_id = l_resource_list_id;
SELECT 'Y'
INTO l_valid_job
FROM per_jobs
WHERE job_id = l_job_id
AND job_group_id = l_job_group_id;
select a.res_format_id
from pa_res_formats_b a,pa_res_types_b b, pa_plan_rl_formats c
where a.resource_class_id = 1
and a.res_type_id = b.res_type_id (+)
and c.res_format_id = a.res_format_id
and c.resource_list_id = p_resource_list_id
and nvl(b.res_type_code, 'DUMMY') <> 'BOM_LABOR';
select a.res_format_id
from pa_res_formats_b a,pa_res_types_b b, pa_plan_rl_formats c
where a.resource_class_id = 1
and a.res_type_id = b.res_type_id (+)
and c.res_format_id = a.res_format_id
and c.resource_list_id = p_resource_list_id
and nvl(b.res_type_code, 'DUMMY') NOT IN
('NAMED_PERSON','BOM_LABOR','PERSON_TYPE');
Insert into pa_resource_lists_tl (
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
RESOURCE_LIST_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
fnd_global.login_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
lst.resource_list_id,
lst.name,
lst.description,
l.language_code,
userenv('LANG')
from pa_resource_lists_all_bg lst,
fnd_languages l
where l.Installed_Flag in ('I', 'B')
and lst.resource_list_id = p_resource_list_id
and not exists (select 'Y'
from pa_resource_lists_tl T
where t.resource_list_id = lst.resource_list_id);
* Procedure : Delete_proj_specific_resource
* Desc : This API is used to delete the project specific resources
* once the project is deleted.
*******************************************************************/
PROCEDURE Delete_Proj_Specific_Resource(
p_project_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER)
IS
l_res_list_id pa_resource_lists_all_bg.resource_list_id%TYPE;
SELECT resource_list_id FROM pa_resource_lists_all_bg
WHERE migration_code is not null;
DELETE FROM pa_resource_list_members
WHERE resource_list_id = l_res_list_id
AND object_type = 'PROJECT'
AND object_id = p_project_id;
'Pa_Planning_Resource_Utils.Delete_proj_specific_resource'
,p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_Proj_Specific_Resource;
SELECT nvl(control_flag, 'N')
INTO l_central_control
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT resource_list_member_id
INTO l_resource_list_member_id
FROM pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND resource_class_flag = 'Y'
AND resource_class_code = p_resource_class_code;
SELECT resource_list_member_id
INTO l_resource_list_member_id
FROM pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND resource_class_flag = 'Y'
AND resource_class_code = p_resource_class_code
AND object_type = 'PROJECT'
AND object_id = p_project_id;
SELECT resource_class_code, inventory_item_id, expenditure_type
FROM pa_resource_list_members
WHERE resource_list_member_id = p_resource_list_member_id;
SELECT 'Y'
INTO l_rate_based_flag
FROM dual
WHERE NOT EXISTS (select 'Y'
from mtl_system_items_b item,
mtl_units_of_measure meas
where item.inventory_item_id =
l_res_details.inventory_item_id
and item.primary_uom_code = meas.uom_code
and meas.uom_class = 'Currency');
SELECT c.cost_rate_flag
INTO l_rate_based_flag
FROM pa_expenditure_types c
WHERE c.expenditure_type = l_res_details.expenditure_type;
SELECT res_format_id, resource_list_id, expenditure_type,
expenditure_category, revenue_category, event_type, object_type,
object_id, enabled_flag
INTO l_res_format_id, l_res_list_id, l_expenditure_type,
l_expenditure_category, l_revenue_category, l_event_type, l_object_type,
l_object_id, l_enabled_flag
FROM pa_resource_list_members
WHERE resource_list_member_id = p_resource_list_member_id;
SELECT 'N'
INTO l_allowed
FROM pa_resource_list_members
WHERE resource_list_id = l_res_list_id
AND res_format_id = l_res_format_id
AND enabled_flag = 'Y'
AND resource_list_member_id <> p_resource_list_member_id
AND object_type = l_object_type
AND object_id = l_object_id
AND nvl(expenditure_type, 'DUMMY') = nvl(l_expenditure_type, 'DUMMY')
AND nvl(expenditure_category, 'DUMMY') =
nvl(l_expenditure_category,'DUMMY')
AND nvl(revenue_category, 'DUMMY') = nvl(l_revenue_category, 'DUMMY')
AND nvl(event_type, 'DUMMY') = nvl(l_event_type, 'DUMMY');
SELECT control_flag
INTO l_central_control
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT resource_list_member_id
INTO x_resource_list_member_id
FROM pa_resource_list_members
WHERE alias = p_alias
AND resource_list_id = p_resource_list_id
AND object_type = l_object_type
AND object_id = l_object_id;
SELECT 'Y'
INTO x_valid_member_flag
FROM pa_resource_list_members
WHERE resource_list_member_id = x_resource_list_member_id
AND resource_list_id = p_resource_list_id
AND object_type = l_object_type
AND object_id = l_object_id;
SELECT 'Y'
INTO x_valid_member_flag
FROM pa_resource_list_members
WHERE resource_list_member_id = x_resource_list_member_id
AND enabled_flag = 'Y';
SELECT f.Res_Type_Enabled_Flag,
f.Orgn_Enabled_Flag,
f.Fin_Cat_Enabled_Flag,
f.Role_Enabled_Flag,
f.Res_Type_Id,
t.Res_Type_Code
FROM Pa_Res_Formats_B f,
Pa_Res_Types_B t
WHERE f.Res_Type_Id = t.Res_Type_Id(+)
AND f.Res_Format_Id = p_res_format_id;
SELECT a.organization_id, orgvl.name
INTO X_organization_id, X_organization_name
FROM per_assignments_x a,
pa_all_organizations org,
hr_all_organization_units_vl orgvl
WHERE a.person_id = l_person_id
AND a.organization_id = orgvl.organization_id
AND a.organization_id = org.organization_id
AND org.inactive_date is null
AND org.pa_org_use_type = 'EXPENDITURES'
AND a.assignment_type in ('C','E')
AND a.primary_flag = 'Y'
AND ROWNUM = 1;
SELECT b.organization_id, orgvl.name
INTO X_organization_id, X_organization_name
FROM bom_resources b,
hr_all_organization_units_vl orgvl
WHERE b.resource_id = l_bom_resource_id
AND b.organization_id = orgvl.organization_id
AND ROWNUM = 1;
SELECT n.expenditure_type
INTO x_expenditure_type
FROM pa_non_labor_resources n
WHERE n.non_labor_resource = p_non_labor_resource
AND ROWNUM = 1;