[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (max(a1.end_date)+1) available_from
FROM pa_res_availability a1,
pa_res_availability a2
WHERE a1.resource_id = p_resource_id
and a1.percent < l_avl_profile
and a1.record_type = 'C'
and a1.start_date < sysdate
and a1.resource_id = a2.resource_id
and a2.record_type ='C'
and a2.percent >= l_avl_profile
and sysdate between a2.start_date and a2.end_date
UNION
SELECT min(a1.start_date) available_from
FROM pa_res_availability a1,
pa_res_availability a2
WHERE a1.resource_id = p_resource_id
and a1.percent >= l_avl_profile
and a1.record_type = 'C'
and a1.start_date >= sysdate
and a1.resource_id = a2.resource_id
and a2.record_type ='C'
and a2.percent < l_avl_profile
and sysdate between a2.start_date and a2.end_date
UNION
SELECT min(a1.start_date) available_from
FROM pa_res_availability a1
WHERE a1.record_type = 'C'
and a1.resource_id = p_resource_id
and a1.percent >= l_avl_profile
and sysdate < (select min(resource_effective_start_date)
from pa_resources_denorm res
where a1.resource_id = res.resource_id);
SELECT min(item_date)
INTO l_available_date
FROM pa_forecast_items
WHERE resource_id = p_resource_id
AND forecast_item_type = 'U'
AND delete_flag = 'N'
AND availability_flag = 'Y'
AND overcommitment_flag = 'N'
AND item_date >= trunc(sysdate);
select min(start_date)
into l_end_date
from pa_project_assignments
where resource_id = p_resource_id
and start_date > l_start_date
and status_code in (select project_status_code
from pa_project_statuses
where project_system_status_code =
'STAFFED_ASGMT_CONF');
SELECT
RSCH.START_DATE
, RSCH.END_DATE
, RSCH.SCALE_TYPE
INTO
l_start_date1,
l_end_date1,
l_scale_type
FROM PA_RES_SCH_TIME_CHART RSCH
WHERE RSCH.ROW_LABEL_ID = p_ROW_LABEL_ID
AND END_DATE >= to_date('2000-10-23', 'YYYY-MM-DD')
AND START_DATE <= to_date('2000-11-26', 'YYYY-MM-DD')
AND SCALE_TYPE = 'MONTH';*/
PROCEDURE Insert_resource_denorm
( p_resource_denorm_rec IN Resource_denorm_Rec_type,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
l_resource_denorm_rec PA_RESOURCE_PVT.Resource_denorm_Rec_type;
PA_DEBUG.init_err_stack('PA_RESOURCE_PVT.Insert_resource_denorm');
savepoint RES_PVT_INSERT_RES_DENORM;
SELECT 'Y'
INTO l_rows_exists
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_resources_denorm
WHERE l_resource_denorm_rec.person_id = person_id
AND l_resource_denorm_rec.job_id = job_id);
INSERT into pa_resources_denorm
(
PERSON_ID,
RESOURCE_ID,
RESOURCE_NAME,
RESOURCE_TYPE,
RESOURCE_ORGANIZATION_ID,
RESOURCE_COUNTRY_CODE,
RESOURCE_COUNTRY,
RESOURCE_REGION,
RESOURCE_CITY,
JOB_ID,
RESOURCE_JOB_LEVEL,
RESOURCE_EFFECTIVE_START_DATE,
RESOURCE_EFFECTIVE_END_DATE,
EMPLOYEE_FLAG,
MANAGER_ID,
MANAGER_NAME,
BILLABLE_FLAG,
UTILIZATION_FLAG,
SCHEDULABLE_FLAG,
RESOURCE_ORG_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
-- ,RESOURCE_PERSON_TYPE
)
VALUES
(
l_resource_denorm_rec.person_id,
l_resource_id,
l_resource_denorm_rec.resource_name,
l_resource_denorm_rec.resource_type,
l_resource_denorm_rec.resource_organization_id,
DECODE(l_resource_denorm_rec.resource_country_code, FND_API.G_MISS_CHAR,
NULL, l_resource_denorm_rec.resource_country_code),
DECODE(l_resource_denorm_rec.resource_country, FND_API.G_MISS_CHAR,
NULL, l_resource_denorm_rec.RESOURCE_COUNTRY),
DECODE(l_resource_denorm_rec.resource_region, FND_API.G_MISS_CHAR,
NULL, l_resource_denorm_rec.resource_region),
DECODE(l_resource_denorm_rec.resource_city, FND_API.G_MISS_CHAR,
NULL, l_resource_denorm_rec.resource_city),
DECODE(l_resource_denorm_rec.job_id, FND_API.G_MISS_NUM,
NULL, l_resource_denorm_rec.job_id),
DECODE(l_resource_denorm_rec.resource_job_level, FND_API.G_MISS_NUM,
NULL, l_resource_denorm_rec.resource_job_level),
l_resource_denorm_rec.resource_effective_start_date,
DECODE(l_resource_denorm_rec.resource_effective_end_date, FND_API.G_MISS_DATE,
NULL, l_resource_denorm_rec.resource_effective_end_date),
l_resource_denorm_rec.employee_flag,
DECODE(l_resource_denorm_rec.manager_id, FND_API.G_MISS_NUM,
NULL, l_resource_denorm_rec.manager_id),
DECODE(l_resource_denorm_rec.manager_name, FND_API.G_MISS_CHAR,
NULL, l_resource_denorm_rec.manager_name),
DECODE(l_resource_denorm_rec.billable_flag, FND_API.G_MISS_CHAR,
NULL, l_resource_denorm_rec.billable_flag),
DECODE(l_resource_denorm_rec.utilization_flag, FND_API.G_MISS_CHAR,
NULL, l_resource_denorm_rec.utilization_flag),
DECODE(l_resource_denorm_rec.schedulable_flag, FND_API.G_MISS_CHAR,
NULL, l_resource_denorm_rec.schedulable_flag),
DECODE(l_resource_denorm_rec.resource_org_id, FND_API.G_MISS_NUM,
NULL,l_resource_denorm_rec.resource_org_id),
null, -- REQUEST_ID
null, -- PROGRAM_APPLICATION_ID
null, -- PROGRAM_ID
null, -- PROGRAM_UPDATE_DATE
sysdate, -- CREATION_DATE
fnd_global.user_id, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
fnd_global.user_id, -- LAST_UPDATED_BY
fnd_global.login_id -- LAST_UPDATE_LOGIN
-- ,l_resource_person_type
);
ROLLBACK TO RES_PVT_INSERT_RES_DENORM;
ROLLBACK TO RES_PVT_INSERT_RES_DENORM;
(p_pkg_name => 'PA_RESOURCE_PVT.Insert_resource_denorm'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Insert_resource_denorm;
PROCEDURE Update_resource_denorm (
p_resource_denorm_old_rec IN Resource_denorm_Rec_type,
p_resource_denorm_new_rec IN Resource_denorm_Rec_type,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
IS
l_resource_denorm_new_rec PA_RESOURCE_PVT.Resource_denorm_Rec_type;
PA_DEBUG.init_err_stack('PA_RESOURCE_PVT.Update_resource_denorm');
savepoint RES_PVT_UPDATE_RES_DENORM;
update_single_res_denorm_rec (
p_resource_denorm_rec => l_resource_denorm_new_rec,
x_return_status => l_return_status,
x_err_msg_code => l_err_msg_code );
-- Update person related multiple records
update_person_res_denorm_recs (
p_resource_denorm_rec => l_resource_denorm_new_rec,
x_return_status => l_return_status,
x_err_msg_code => l_err_msg_code );
-- If job_id has been passed but not person_id, usually update attribute
-- job_level or billable_flag on multiple records.
ELSIF l_resource_denorm_new_rec.person_id = FND_API.G_MISS_NUM
AND l_resource_denorm_new_rec.job_id <> FND_API.G_MISS_NUM
AND l_resource_denorm_new_rec.job_id is not null THEN
-- Update job related multiple records.
update_job_res_denorm_recs (
p_resource_denorm_rec => l_resource_denorm_new_rec,
x_return_status => l_return_status,
x_err_msg_code => l_err_msg_code );
ROLLBACK TO RES_PVT_UPDATE_RES_DENORM;
ROLLBACK TO RES_PVT_UPDATE_RES_DENORM;
(p_pkg_name => 'PA_RESOURCE_PVT.Update_resource_denorm'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_resource_denorm;
PROCEDURE update_single_res_denorm_rec(
p_resource_denorm_rec IN resource_denorm_rec_type,
x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
x_err_msg_code OUT NOCOPY VARCHAR) --File.Sql.39 bug 4440895
IS
num_of_rec NUMBER;
UPDATE pa_resources_denorm
SET
resource_type = DECODE(p_resource_denorm_rec.resource_type, FND_API.G_MISS_CHAR,
resource_type,p_resource_denorm_rec.resource_type),
resource_organization_id = DECODE(p_resource_denorm_rec.resource_organization_ID,
FND_API.G_MISS_NUM, resource_organization_id,
p_resource_denorm_rec.resource_organization_id),
resource_country_code = DECODE(p_resource_denorm_rec.resource_country_code, FND_API.G_MISS_CHAR,
resource_country_code,p_resource_denorm_rec.resource_country_code),
resource_country = DECODE(p_resource_denorm_rec.resource_country, FND_API.G_MISS_CHAR,
resource_country,p_resource_denorm_rec.resource_country),
resource_region = DECODE(p_resource_denorm_rec.resource_region, FND_API.G_MISS_CHAR,
resource_region,p_resource_denorm_rec.resource_region),
resource_city = DECODE(p_resource_denorm_rec.resource_city, FND_API.G_MISS_CHAR,
resource_city,p_resource_denorm_rec.resource_city),
job_id = DECODE(p_resource_denorm_rec.job_id, FND_API.G_MISS_NUM,
job_id,p_resource_denorm_rec.job_id),
resource_job_level = DECODE(p_resource_denorm_rec.resource_job_level, FND_API.G_MISS_NUM,
resource_job_level,p_resource_denorm_rec.resource_job_level),
resource_effective_end_date = DECODE(p_resource_denorm_rec.resource_effective_end_date,
FND_API.G_MISS_DATE, resource_effective_end_date,
p_resource_denorm_rec. resource_effective_end_date),
employee_flag = DECODE(p_resource_denorm_rec.employee_flag, FND_API.G_MISS_CHAR,
employee_flag,p_resource_denorm_rec.employee_flag),
manager_id = DECODE(p_resource_denorm_rec.manager_id, FND_API.G_MISS_NUM,
manager_id,p_resource_denorm_rec.manager_id),
manager_name = DECODE(p_resource_denorm_rec.manager_name, FND_API.G_MISS_CHAR,
manager_name,p_resource_denorm_rec.manager_name),
billable_flag = DECODE(p_resource_denorm_rec.billable_flag, FND_API.G_MISS_CHAR,
billable_flag,p_resource_denorm_rec.billable_flag),
utilization_flag = DECODE(p_resource_denorm_rec.utilization_flag, FND_API.G_MISS_CHAR,
utilization_flag,p_resource_denorm_rec.utilization_flag),
schedulable_flag = DECODE(p_resource_denorm_rec.schedulable_flag, FND_API.G_MISS_CHAR,
schedulable_flag,p_resource_denorm_rec.schedulable_flag),
resource_org_id = DECODE(p_resource_denorm_rec.resource_org_id, FND_API.G_MISS_NUM,
resource_org_id,p_resource_denorm_rec.resource_org_id),
LAST_UPDATE_date = sysdate,
LAST_UPDATED_by = fnd_global.user_id,
LAST_UPDATE_login = fnd_global.login_id
WHERE person_id = p_resource_denorm_rec.person_id
AND resource_effective_start_date = p_resource_denorm_rec.resource_effective_start_date;
(p_pkg_name => 'PA_RESOURCE_PVT.update_person_res_denorm_recs',
p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_single_res_denorm_rec;
PROCEDURE update_person_res_denorm_recs
( p_resource_denorm_rec IN resource_denorm_rec_type,
x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
x_err_msg_code OUT NOCOPY VARCHAR) --File.Sql.39 bug 4440895
IS
num_of_rec NUMBER;
UPDATE pa_resources_denorm
SET
resource_name = DECODE(p_resource_denorm_rec.resource_name, FND_API.G_MISS_CHAR,
resource_name,p_resource_denorm_rec.resource_name),
resource_country_code = DECODE(p_resource_denorm_rec.resource_country_code, FND_API.G_MISS_CHAR,
resource_country_code,p_resource_denorm_rec.resource_country_code),
resource_country = DECODE(p_resource_denorm_rec.resource_country, fnd_API.G_MISS_CHAR,
resource_country,p_resource_denorm_rec.resource_country),
resource_region = DECODE(p_resource_denorm_rec.resource_region, FND_API.G_MISS_CHAR,
resource_region,p_resource_denorm_rec.resource_region),
resource_city = DECODE(p_resource_denorm_rec.resource_city, FND_API.G_MISS_CHAR,
resource_city,p_resource_denorm_rec.resource_city),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE p_resource_denorm_rec.person_id = person_id;
(p_pkg_name => 'PA_RESOURCE_PVT.update_person_res_denorm_recs'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_person_res_denorm_recs;
SELECT 'Y' INTO l_name_changed
FROM dual
WHERE exists
(SELECT 'Y'
FROM pa_resources_denorm
WHERE person_id = p_new_resource_denorm_rec.person_id
AND resource_name <> l_new_resource_name) ;
UPDATE pa_resources_denorm
SET manager_name = l_new_resource_name
WHERE manager_id = p_new_resource_denorm_rec.person_id;
(p_pkg_name => 'PA_RESOURCE_PVT.update_person_res_denorm_recs',
p_procedure_name => 'PA_RESOURCE_PVT.syncronize_manager_name'); --PA_DEBUG.G_Err_Stack ); 5336386
PROCEDURE update_job_res_denorm_recs
(p_resource_denorm_rec IN resource_denorm_rec_type,
p_start_rowid IN rowid default NULL,
p_end_rowid IN rowid default NULL,
x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
x_err_msg_code OUT NOCOPY VARCHAR ) --File.Sql.39 bug 4440895
IS
BEGIN
-- Initialize the return status to success
x_return_status := FND_API.G_RET_STS_SUCCESS;
UPDATE pa_resources_denorm
SET
resource_job_level = DECODE(p_resource_denorm_rec.resource_job_level, FND_API.G_MISS_NUM,
resource_job_level,p_resource_denorm_rec.resource_job_level),
employee_flag = DECODE(p_resource_denorm_rec.employee_flag, FND_API.G_MISS_CHAR,
employee_flag, p_resource_denorm_rec.employee_flag),
billable_flag = DECODE(p_resource_denorm_rec.billable_flag, FND_API.G_MISS_CHAR,
billable_flag, p_resource_denorm_rec.billable_flag),
utilization_flag = DECODE(p_resource_denorm_rec.utilization_flag, FND_API.G_MISS_CHAR,
utilization_flag, p_resource_denorm_rec.utilization_flag),
schedulable_flag = DECODE(p_resource_denorm_rec.schedulable_flag, FND_API.G_MISS_CHAR,
schedulable_flag, p_resource_denorm_rec.schedulable_flag),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE p_resource_denorm_rec.job_id = job_id
AND rowid between nvl(p_start_rowid, rowid)
and nvl(p_end_rowid, rowid);
(p_pkg_name => 'PA_RESOURCE_PVT.update_person_res_denorm_recs'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_job_res_denorm_recs;
PROCEDURE delete_resource_denorm
( p_person_id IN pa_resources_denorm.person_id%type,
p_res_effective_start_date IN pa_resources_denorm.resource_effective_start_date%type,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
IS
-- l_manager_id pa_resources_denorm.manager_id%TYPE;
DELETE FROM PA_RESOURCES_DENORM
WHERE person_id = p_person_id
AND resource_effective_start_date = p_res_effective_start_date
AND resource_effective_end_date > trunc(sysdate);
( p_pkg_name => 'PA_RESOURCE_PVT.delete_resource_denorm'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END delete_resource_denorm;
SELECT jei_information2, jei_information3,jei_information6
INTO l_billable_flag, l_utilization_flag, l_schedulable_flag
FROM per_job_extra_info
WHERE job_id = p_job_id
AND information_type = l_job_info_type;
SELECT date_from,
date_to
FROM per_addresses
WHERE person_id = l_person_id
AND date_from <= l_asgn_end_date
AND nvl(date_to, l_asgn_end_date) >= l_asgn_start_date
AND primary_flag = 'Y'
ORDER BY date_from
;
DELETE FROM pa_resources_denorm
WHERE person_id = p_resource_source_id
AND resource_effective_start_date <= p_assignment_end_date
AND resource_effective_end_date >= p_assignment_start_date
;
l_resource_job_level := PA_HR_UPDATE_API.Get_Job_Level(
p_job_id => p_resource_job_id,
p_job_group_id => p_resource_job_group_id);
l_billable_flag := PA_HR_UPDATE_API.check_job_billability(
p_job_id => p_resource_job_id,
p_person_id => p_resource_source_id,
p_date => p_assignment_start_date);
l_utilization_flag := PA_HR_UPDATE_API.check_job_utilization(
p_job_id => p_resource_job_id,
p_person_id => p_resource_source_id,
p_date => p_assignment_start_date);
l_schedulable_flag := PA_HR_UPDATE_API.check_job_schedulable
(p_job_id => p_resource_job_id);
INSERT INTO PA_RESOURCES_DENORM ( person_id
, resource_id
, resource_name
, resource_type
, resource_org_id
, resource_organization_id
, resource_country_code
, resource_country
, resource_region
, resource_city
, resource_job_level
, resource_effective_start_date
, resource_effective_end_date
, employee_flag
, manager_id
, manager_name
, billable_flag
, job_id
, utilization_flag
, schedulable_flag
, request_id
, program_application_id
, program_id
, program_update_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, resource_person_type
)
SELECT p_resource_source_id
, p_resource_id
, p_resource_name
, p_resource_type
, p_resource_org_id
, p_resource_organization_id
, l_resource_country_code
, l_resource_country
, l_resource_region
, l_resource_city
, l_resource_job_level
, l_rec_start_date
, l_rec_end_date
, l_employee_flag
, p_manager_id
, p_manager_name
, l_billable_flag
, p_resource_job_id
, l_utilization_flag
, l_schedulable_flag
, p_request_id
, p_program_application_id
, p_program_id
, sysdate
, sysdate
/*----- Bug 1992257 commented
, TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
, sysdate
, TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
, TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
End of Comment, added next 4 lines */
, TO_NUMBER(FND_GLOBAL.USER_ID)
, sysdate
, TO_NUMBER(FND_GLOBAL.USER_ID)
, TO_NUMBER(FND_GLOBAL.LOGIN_ID)
, p_person_type
FROM dual;
proceeding on inserting into pa_resources_denorm. This will show the
error as an exception in the report output. */
IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
IF l_error_msg_code IS NOT NULL THEN
pa_utils.add_message
( p_app_short_name => 'PA',
p_msg_name => l_error_msg_code);
INSERT INTO PA_RESOURCES_DENORM ( person_id
, resource_id
, resource_name
, resource_type
, resource_org_id
, resource_organization_id
, resource_country_code
, resource_country
, resource_region
, resource_city
, resource_job_level
, resource_effective_start_date
, resource_effective_end_date
, employee_flag
, manager_id
, manager_name
, billable_flag
, job_id
, utilization_flag
, schedulable_flag
, request_id
, program_application_id
, program_id
, program_update_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, resource_person_type
)
SELECT p_resource_source_id
, p_resource_id
, p_resource_name
, p_resource_type
, p_resource_org_id
, p_resource_organization_id
, l_resource_country_code
, l_resource_country
, l_resource_region
, l_resource_city
, l_resource_job_level
, l_rec_start_date
, l_rec_end_date
, l_employee_flag
, p_manager_id
, p_manager_name
, l_billable_flag
, p_resource_job_id
, l_utilization_flag
, l_schedulable_flag
, p_request_id
, p_program_application_id
, p_program_id
, sysdate
, sysdate
/*----- Bug 1992257 commented
, TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
, sysdate
, TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
, TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
End of Comment, added next 4 lines */
, TO_NUMBER(FND_GLOBAL.USER_ID)
, sysdate
, TO_NUMBER(FND_GLOBAL.USER_ID)
, TO_NUMBER(FND_GLOBAL.LOGIN_ID)
, p_person_type
FROM dual;
SELECT parties.resource_source_id
INTO l_party_id
from pa_project_parties parties
where parties.project_role_id = 1
AND parties.project_id = p_project_id
AND trunc(sysdate) between parties.start_date_active AND NVL(parties.end_date_active,trunc(sysdate) + 1)--;
select resource_name
into g_manager_name
from pa_resources_denorm
where person_id = l_party_id
and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
select resource_id
into g_manager_resource_id
from pa_resources_denorm
where person_id = l_party_id
and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
select min(start_date)-1
into l_avl_to_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
and start_date > p_avl_from_date
-- Commented for 4725606 and percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE'));
select min(start_date)-1
into l_ovc_to_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
and start_date > p_ovc_from_date
-- Commented for 4725606 and -1 * percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_OVERCOMMITMENT_PERCENTAGE'));
select sum(hours) * -1
into l_ovc_hours
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
and start_date >= p_ovc_from_date
and end_date <= p_ovc_to_date;
SELECT sum(nvl(capacity_quantity,0) - nvl(confirmed_qty,0))
INTO l_conf_availability
FROM pa_forecast_items
WHERE resource_id = p_resource_id
AND delete_flag = 'N'
AND forecast_item_type = 'U'
AND item_date between p_start_date
and p_end_date;
SELECT sum(nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))
INTO l_prov_conf_availability
FROM pa_forecast_items
WHERE resource_id = p_resource_id
AND delete_flag = 'N'
AND forecast_item_type = 'U'
AND item_date between p_start_date
and p_end_date;
l_last_updated_by number;
l_last_update_login number;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
'insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
hours,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login
)
select item_date,
(lead(item_date) over(order by item_date)) - 1,
c * 100 ,
' || '''C''' ||
' , resource_id,
pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date,' || '''' || p_cstart_date || ''''|| '),
nvl((lead(item_date) over(order by item_date)),' || '''' || p_cend_date || ''''|| ') - 1),
sysdate,
sysdate,
' || l_created_by || ',
' || l_last_updated_by || ',
' || l_last_update_login || '
from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0) a,
lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0),1,1000) -- bug 3229929
over ( order by item_date) as b,
(nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/capacity_quantity c,
item_date,
resource_id
from pa_forecast_items
where forecast_item_type = ' || '''U''' ||
' and resource_id = ' || p_resource_id || '
and capacity_quantity > 0
and delete_flag = ' || '''N''' || '
and item_date between ' || '''' || p_cstart_date || '''' || ' and ' ||
'''' || p_cend_date || ''''|| ')
where nvl(a,0) - nvl(b,0) <> 0';
'insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
hours,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login
)
select item_date,
(lead(item_date) over(order by item_date)) - 1,
c * 100 ,
:c,
resource_id,
pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date, :cstart_date ),
nvl((lead(item_date) over(order by item_date)), :cend_date ) - 1),
sysdate,
sysdate,
:created_by,
:last_updated_by,
:last_update_login
from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0) a,
lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/nvl(capacity_quantity,0),1,1000) -- bug 3229929
over ( order by item_date) as b,
(nvl(capacity_quantity,0) - nvl(confirmed_qty,0))/capacity_quantity c,
item_date,
resource_id
from pa_forecast_items
where forecast_item_type = :u
and resource_id = :resource_id
and capacity_quantity > 0
and delete_flag = :n
and item_date between :cstart_date and :cend_date )
where nvl(a,0) - nvl(b,0) <> 0' using 'C', p_cstart_date, p_cend_date, l_created_by,
l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_cstart_date, p_cend_date;
'insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
hours,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login
)
select item_date,
(lead(item_date) over(order by item_date)) - 1,
c * 100 ,
' || '''B''' ||
' , resource_id,
pa_resource_pvt.get_res_prov_conf_availability(resource_id, nvl(item_date,' || '''' || p_bstart_date || '''' || '), nvl((lead(item_date) over(order by item_date)),' || '''' || p_bend_date || ''''|| ') - 1),
sysdate,
sysdate,
' || l_created_by || ' ,
' || l_last_updated_by || ' ,
' || l_last_update_login || '
from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0) a,
lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0))
over ( order by item_date) as b,
(nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/capacity_quantity c,
item_date,
resource_id
from pa_forecast_items
where forecast_item_type = ' || '''U''' ||
' and resource_id = ' || p_resource_id || '
and capacity_quantity > 0
and delete_flag = ' || '''N''' || '
and item_date between ' || '''' || p_bstart_date || '''' || ' and ' ||
'''' || p_bend_date || ''''|| ')
where nvl(a,0) - nvl(b,0) <> 0';
'insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
hours,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login
)
select item_date,
(lead(item_date) over(order by item_date)) - 1,
c * 100 ,
:b,
resource_id,
pa_resource_pvt.get_res_prov_conf_availability(resource_id, nvl(item_date, :bstart_date ), nvl((lead(item_date) over(order by item_date)), :bend_date ) - 1),
sysdate,
sysdate,
:created_by,
:last_updated_by,
:last_update_login
from ( select (nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0) a,
lag((nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/nvl(capacity_quantity,0))
over ( order by item_date) as b,
(nvl(capacity_quantity,0) - nvl(confirmed_qty,0) - nvl(provisional_qty,0))/capacity_quantity c,
item_date,
resource_id
from pa_forecast_items
where forecast_item_type = :u
and resource_id = :resource_id
and capacity_quantity > 0
and delete_flag = :n
and item_date between :bstart_date and :bend_date )
where nvl(a,0) - nvl(b,0) <> 0' using 'B', p_bstart_date, p_bend_date, l_created_by,
l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_bstart_date, p_bend_date;
update pa_res_availability avl
set start_date = p_cstart_date
where resource_id = p_resource_id
and record_type = 'C'
and start_date is null;
update pa_res_availability avl
set end_date = p_cend_date
where resource_id = p_resource_id
and record_type = 'C'
and end_date is null;
select min(start_date)
into l_start_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C';
select percent
into l_percent
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
and start_date = l_start_date;
insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
select min(start_date) - 1,
min(start_date) - 1,
0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
group by 0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login;
select max(end_date)
into l_end_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C';
select percent
into l_percent
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
and end_date = l_end_date;
insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
select max(end_date) + 1,
max(end_date) + 1,
0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
group by 0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login;
update pa_res_availability avl
set start_date = p_bstart_date
where resource_id = p_resource_id
and record_type = 'B'
and start_date is null;
update pa_res_availability avl
set end_date = p_bend_date
where resource_id = p_resource_id
and record_type = 'B'
and end_date is null;
select min(start_date)
into l_start_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B';
select percent
into l_percent
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B'
and start_date = l_start_date;
insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
select min(start_date) - 1,
min(start_date) - 1,
0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B'
group by 0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login;
select max(end_date)
into l_end_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B';
select percent
into l_percent
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B'
and end_date = l_end_date;
insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
select max(end_date) + 1,
max(end_date) + 1,
0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B'
group by 0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login;
'insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
hours,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login
)
select item_date,
(lead(item_date) over(order by item_date)) - 1,
c * 100 ,
' || '''C''' ||
' , resource_id,
pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date,' || '''' || p_cstart_date || '''' || '), nvl((lead(item_date) over(order by item_date)),' || '''' || p_cend_date || ''''|| ') - 1),
sysdate,
sysdate,
' || l_created_by || ',
' || l_last_updated_by || ',
' || l_last_update_login || '
from (
select (nvl(a1,0) - nvl(a2,0))/nvl(a1,1) a,
lag((nvl(a1,0) - nvl(a2,0))/nvl(a1,1))
over ( order by d) as b,
(nvl(a1,0) - nvl(a2,0))/nvl(a1,1) c,
d item_date,
resource_id
from (
select (global_exp_period_end_date - 6) d,
resource_id,
sum(capacity_quantity) a1,
sum(confirmed_qty) a2
from pa_forecast_items
where forecast_item_type = ' || '''U''' ||
'and resource_id = ' || p_resource_id || '
and capacity_quantity > 0
and delete_flag = ' || '''N''' || '
and item_date between ' || '''' || p_cstart_date || '''' || ' and ' ||
'''' || p_cend_date || ''''||
'group by global_exp_period_end_date - 6,
resource_id))
where nvl(a,0) - nvl(b,0) <> 0 ';
'insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
hours,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login
)
select item_date,
(lead(item_date) over(order by item_date)) - 1,
c * 100 ,
:c,
resource_id,
pa_resource_pvt.get_res_conf_availability(resource_id, nvl(item_date, :cstart_date), nvl((lead(item_date) over(order by item_date)), :cend_date ) - 1),
sysdate,
sysdate,
:created_by,
:last_updated_by,
:last_update_login
from (
select (nvl(a1,0) - nvl(a2,0))/nvl(a1,1) a,
lag((nvl(a1,0) - nvl(a2,0))/nvl(a1,1))
over ( order by d) as b,
(nvl(a1,0) - nvl(a2,0))/nvl(a1,1) c,
d item_date,
resource_id
from (
select (global_exp_period_end_date - 6) d,
resource_id,
sum(capacity_quantity) a1,
sum(confirmed_qty) a2
from pa_forecast_items
where forecast_item_type = :u
and resource_id = :resource_id
and capacity_quantity > 0
and delete_flag = :n
and item_date between :cstart_date and :cend_date
group by global_exp_period_end_date - 6,
resource_id))
where nvl(a,0) - nvl(b,0) <> 0 ' using 'C', p_cstart_date, p_cend_date, l_created_by,
l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_cstart_date, p_cend_date;
'insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
hours,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login
)
select item_date,
(lead(item_date) over(order by item_date)) - 1,
c * 100 ,
' || '''B''' ||
' , resource_id,
pa_resource_pvt.get_res_prov_conf_availability(resource_id, nvl(item_date,' || '''' || p_bstart_date || '''' || '), nvl((lead(item_date) over(order by item_date)),' || '''' || p_bend_date || ''''|| ') - 1),
sysdate,
sysdate,
' || l_created_by || ',
' || l_last_updated_by ||',
' || l_last_update_login || '
from (
select (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) a,
lag((nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1))
over ( order by d) as b,
(nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) c,
d item_date,
resource_id
from (
select (global_exp_period_end_date - 6) d,
resource_id,
sum(capacity_quantity) a1,
sum(confirmed_qty) a2,
sum(provisional_qty) a3
from pa_forecast_items
where forecast_item_type = ' || '''U''' ||
'and resource_id = ' || p_resource_id || '
and capacity_quantity > 0
and delete_flag = ' || '''N''' || '
and item_date between ' || '''' || p_bstart_date || '''' || ' and ' ||
'''' || p_bend_date || ''''||
'group by global_exp_period_end_date - 6,
resource_id))
where nvl(a,0) - nvl(b,0) <> 0 ';
'insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
hours,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login
)
select item_date,
(lead(item_date) over(order by item_date)) - 1,
c * 100 ,
:b,
resource_id,
pa_resource_pvt.get_res_prov_conf_availability(resource_id, nvl(item_date, :bstart_date ), nvl((lead(item_date) over(order by item_date)), :bend_date ) - 1),
sysdate,
sysdate,
:created_by,
:last_updated_by,
:last_update_login
from (
select (nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) a,
lag((nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1))
over ( order by d) as b,
(nvl(a1,0) - nvl(a2,0) - nvl(a3,0))/nvl(a1,1) c,
d item_date,
resource_id
from (
select (global_exp_period_end_date - 6) d,
resource_id,
sum(capacity_quantity) a1,
sum(confirmed_qty) a2,
sum(provisional_qty) a3
from pa_forecast_items
where forecast_item_type = :u
and resource_id = :resource_id
and capacity_quantity > 0
and delete_flag = :n
and item_date between :bstart_date and :bend_date
group by global_exp_period_end_date - 6,
resource_id))
where nvl(a,0) - nvl(b,0) <> 0 ' using 'B', p_bstart_date, p_bend_date, l_created_by,
l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_bstart_date, p_bend_date;
update pa_res_availability avl
set start_date = p_cstart_date
where resource_id = p_resource_id
and record_type = 'C'
and start_date is null;
update pa_res_availability avl
set end_date = p_cend_date
where resource_id = p_resource_id
and record_type = 'C'
and end_date is null;
select min(start_date)
into l_start_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C';
select percent
into l_percent
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
and start_date = l_start_date
and percent = 0;
insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
select min(start_date) - 1,
min(start_date) - 1,
0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
group by 0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login;
select max(end_date)
into l_end_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C';
select percent
into l_percent
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
and end_date = l_end_date
and percent = 0;
insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
select max(end_date) + 1,
max(end_date) + 1,
0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'C'
group by 0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login;
update pa_res_availability avl
set start_date = p_bstart_date
where resource_id = p_resource_id
and record_type = 'B'
and start_date is null;
update pa_res_availability avl
set end_date = p_bend_date
where resource_id = p_resource_id
and record_type = 'B'
and end_date is null;
select min(start_date)
into l_start_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B';
select percent
into l_percent
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B'
and start_date = l_start_date
and percent = 0;
insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
select min(start_date) - 1,
min(start_date) - 1,
0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B'
group by 0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login;
select max(end_date)
into l_end_date
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B';
select percent
into l_percent
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B'
and end_date = l_end_date
and percent = 0;
insert into PA_RES_AVAILABILITY
( start_date,
end_date,
percent,
record_type,
resource_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
select max(end_date) + 1,
max(end_date) + 1,
0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login
from pa_res_availability
where resource_id = p_resource_id
and record_type = 'B'
group by 0,
record_type,
resource_id,
sysdate,
sysdate,
l_created_by,
l_last_updated_by,
l_last_update_login;
select min(RESOURCE_EFFECTIVE_START_DATE)
into l_earliest_start_date
from pa_resources_denorm
where RESOURCE_ID = p_resource_id;
update pa_res_availability
set start_date = l_earliest_start_date
where resource_id = p_resource_id
and start_date < l_earliest_start_date
and percent > 0;
select count(*)
into l_rec_count
from pa_res_availability
where resource_id = p_resource_id
and start_date < l_earliest_start_date
and record_type = 'B'
and percent = 0;
delete
from pa_res_availability
where resource_id = p_resource_id
and start_date < l_earliest_start_date - 1
and record_type = 'B'
and percent = 0;
select count(*)
into l_rec_count
from pa_res_availability
where resource_id = p_resource_id
and start_date < l_earliest_start_date
and record_type = 'C'
and percent = 0;
delete
from pa_res_availability
where resource_id = p_resource_id
and start_date < l_earliest_start_date - 1
and record_type = 'C'
and percent = 0;
update pa_res_availability
set start_date = l_earliest_start_date -1,
end_date = l_earliest_start_date -1
where resource_id = p_resource_id
and start_date < l_earliest_start_date
and percent = 0;
PROCEDURE update_res_availability (
p_resource_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
IS
l_msg_index_out number;
PA_DEBUG.init_err_stack('PA_RESOURCE_PVT.Update_Res_Availability');
select min(resource_effective_start_date)
into l_start_date
from pa_resources_denorm
where resource_id = p_resource_id;
select max(resource_effective_end_date)
into l_end_date
from pa_resources_denorm
where resource_id = p_resource_id;
delete
from pa_res_availability
where resource_id = p_resource_id;
(p_pkg_name => 'PA_RESOURCE_PVT.Update_Res_Availability'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Res_Availability;
select resource_id,
min(resource_effective_start_date) start_date,
max(resource_effective_end_date) end_date
from pa_resources_denorm
group by resource_id;
select resource_id
from pa_res_availability
where resource_id not in (Select resource_id from pa_resources_denorm)
and resource_id <> -1;
delete
from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id;
delete
from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id; --Added for bug 4928773
delete
from PA_RES_AVAILABILITY where RESOURCE_ID = -1; --Added for bug 4928773
INSERT
INTO PA_RES_AVAILABILITY
(
RESOURCE_ID,
START_DATE,
RECORD_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
VALUES
(
-1,
sysdate,
decode(l_profile, 'DAILY', 'D', 'W'),
sysdate,
-1,
sysdate,
-1
);