[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cac_sr_object_capacity_s.nextval
FROM dual;
, p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
, p_Start_Date IN DATE -- start date and time of period of interest
, p_End_Date IN DATE -- end date and time of period of interest
, p_Populate_ID IN VARCHAR2 DEFAULT 'F' -- Populate the object_capacity_id of the record?
-- Should be set to 'T' if the ids are needed for foreign key reference
-- 'F' means the ids will be genrated while inserting these records in the table
, p_Fetch_Tasks IN VARCHAR2 DEFAULT 'F' -- Fetch tasks for the time period too?
, x_Object_Capacity OUT NOCOPY OBJECT_CAPACITY_TBL_TYPE
-- return table of object capacity records
, x_Object_Tasks OUT NOCOPY OBJECT_TASKS_TBL_TYPE
-- return table of object task records
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack
select jta.task_assignment_id
, jta.object_version_number asn_ovn
, jta.assignment_status_id
, jta.free_busy_type
, jtb.task_id
, jtb.object_version_number task_ovn
, jtb.task_status_id
, jtb.task_type_id
, jtb.scheduled_start_date
, jtb.scheduled_end_date
, jtb.planned_start_date
, jtb.planned_end_date
, jtb.address_id
, jtb.customer_id
from jtf_task_statuses_b jtsb
, jtf_task_assignments jta
, jtf_tasks_b jtb
where nvl(jtsb.closed_flag, 'N') = 'N'
and nvl(jtsb.completed_flag, 'N') = 'N'
and nvl(jtsb.cancelled_flag, 'N') = 'N'
and jtsb.task_status_id = jta.assignment_status_id
and jta.resource_type_code = b_resource_type
and jta.resource_id = b_resource_id
and jta.task_id = jtb.task_id
and nvl(jtb.deleted_flag, 'N') = 'N'
and nvl(jtb.open_flag, 'Y') = 'Y'
and jtb.scheduled_start_date BETWEEN b_date_min AND b_date_max
and jtb.scheduled_end_date BETWEEN b_date_min AND b_date_max
order by jtb.scheduled_start_date;
PROCEDURE insert_object_capacity
/*******************************************************************************
** insert_object_capacity
**
** This API calls table handler to insert data into cac_sr_object_capacity
** using pl/sql table passed. It populates object_capacity_id for each record
** if with the sequence cac_sr_object_capacity_s value if it is NULL in the
** record.
** It updates the task assignment with the corresponding object_capacity_id
** if the p_Update_Tasks parameter is set to 'T'.
**
*******************************************************************************/
( p_api_version IN NUMBER -- API version you coded against
, p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
, p_Object_Capacity IN OUT NOCOPY OBJECT_CAPACITY_TBL_TYPE
-- table of object capacity records which should be inserte
, p_Update_Tasks IN VARCHAR2 DEFAULT 'F' -- Update task assignments too?
, p_Object_Tasks IN OBJECT_TASKS_TBL_TYPE
-- table of object task records to be updated
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack
l_api_name constant varchar2(30) := 'INSERT_OBJECT_CAPACITY';
CAC_SR_OBJECT_CAPACITY_PKG.INSERT_ROW
(
X_ROWID => l_rowid,
X_OBJECT_CAPACITY_ID => p_Object_Capacity(l_idx).OBJECT_CAPACITY_ID,
X_OBJECT_VERSION_NUMBER => p_Object_Capacity(l_idx).OBJECT_VERSION_NUMBER,
X_OBJECT_TYPE => p_Object_Capacity(l_idx).OBJECT_TYPE,
X_OBJECT_ID => p_Object_Capacity(l_idx).OBJECT_ID,
X_START_DATE_TIME => p_Object_Capacity(l_idx).START_DATE_TIME,
X_END_DATE_TIME => p_Object_Capacity(l_idx).END_DATE_TIME,
X_AVAILABLE_HOURS => p_Object_Capacity(l_idx).AVAILABLE_HOURS,
X_AVAILABLE_HOURS_BEFORE => p_Object_Capacity(l_idx).AVAILABLE_HOURS_BEFORE,
X_AVAILABLE_HOURS_AFTER => p_Object_Capacity(l_idx).AVAILABLE_HOURS_AFTER,
X_SCHEDULE_DETAIL_ID => p_Object_Capacity(l_idx).SCHEDULE_DETAIL_ID,
X_STATUS => p_Object_Capacity(l_idx).STATUS,
X_CREATION_DATE => l_current_date,
X_CREATED_BY => l_user,
X_LAST_UPDATE_DATE => l_current_date,
X_LAST_UPDATED_BY => l_user,
X_LAST_UPDATE_LOGIN => l_login,
X_AVAILABILITY_TYPE => p_Object_Capacity(l_idx).AVAILABILITY_TYPE,
X_SOURCE_TYPE => p_Object_Capacity(l_idx).SOURCE_TYPE
);
IF fnd_api.to_boolean(p_Update_Tasks)
THEN
l_idx := p_Object_Tasks.FIRST;
JTF_TASK_ASSIGNMENTS_PUB.UPDATE_TASK_ASSIGNMENT
(
p_api_version => 1.0,
p_commit => fnd_api.G_FALSE,
p_object_version_number => l_ovn,
p_task_assignment_id => p_Object_Tasks(l_idx).TASK_ASSIGNMENT_ID,
p_enable_workflow => NULL,
p_abort_workflow => NULL,
p_object_capacity_id => p_Object_Capacity(p_Object_Tasks(l_idx).OBJECT_CAPACITY_TBL_IDX).OBJECT_CAPACITY_ID,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END insert_object_capacity;
PROCEDURE update_object_capacity
/*******************************************************************************
** update_object_capacity New version
**
** This API calls table handler to update data into cac_sr_object_capacity.
** Only the available hours fields and status can be updated.
** This version include updation of shift type also.
*******************************************************************************/
( p_api_version IN NUMBER -- API version you coded against
, p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
, p_object_capacity_id IN NUMBER -- Primary Key ID of the row to be updated
, p_object_version_number IN NUMBER -- Object Version of the row to be updated
-- If this doesn't match the database value then that means someone else has updated the same row
, p_available_hours IN NUMBER DEFAULT NULL -- The new value of available hours
-- If it is NULL then no change is done to the existing data
, p_available_hours_before IN NUMBER DEFAULT NULL -- The new value of available before hours.
-- If it is NULL then no change is done to the existing data
-- If it is FND_API.G_MISS_NUM then the value will be set to NULL in the database
, p_available_hours_after IN NUMBER DEFAULT NULL -- The new value of available before hours.
-- If it is NULL then no change is done to the existing data
-- If it is FND_API.G_MISS_NUM then the value will be set to NULL in the database
, p_status IN NUMBER DEFAULT NULL -- The new value of the status
-- If it is NULL then no change is done to the existing data
, p_availability_type IN VARCHAR2 DEFAULT NULL -- The new value of availability_type
--If it is NULL then no change is done to the existing data
, p_start_date_time IN DATE DEFAULT NULL -- New value for start date time
-- if this value is NULL then no change is done to the existing
-- value
, p_end_date_time IN DATE DEFAULT NULL -- New value for end date time
-- if this value is NULL then no change is done to the existing
-- value
, p_source_type IN VARCHAR2 DEFAULT NULL -- new value for source type of trip
-- If NULL is passed for this value then no change is done
-- to exsiting value
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack
) IS SELECT
OBJECT_VERSION_NUMBER,
OBJECT_TYPE,
OBJECT_ID,
START_DATE_TIME,
END_DATE_TIME,
AVAILABLE_HOURS,
AVAILABLE_HOURS_BEFORE,
AVAILABLE_HOURS_AFTER,
SCHEDULE_DETAIL_ID,
STATUS,
AVAILABILITY_TYPE,
SOURCE_TYPE
FROM CAC_SR_OBJECT_CAPACITY
WHERE OBJECT_CAPACITY_ID = b_object_capacity_id
FOR UPDATE OF OBJECT_CAPACITY_ID NOWAIT;
l_api_name constant varchar2(30) := 'UPDATE_OBJECT_CAPACITY';
fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
CAC_SR_OBJECT_CAPACITY_PKG.UPDATE_ROW
(
X_OBJECT_CAPACITY_ID => l_Object_Capacity.OBJECT_CAPACITY_ID,
X_OBJECT_VERSION_NUMBER => l_Object_Capacity.OBJECT_VERSION_NUMBER,
X_OBJECT_TYPE => l_Object_Capacity.OBJECT_TYPE,
X_OBJECT_ID => l_Object_Capacity.OBJECT_ID,
X_START_DATE_TIME => l_Object_Capacity.START_DATE_TIME,
X_END_DATE_TIME => l_Object_Capacity.END_DATE_TIME,
X_AVAILABLE_HOURS => l_Object_Capacity.AVAILABLE_HOURS,
X_AVAILABLE_HOURS_BEFORE => l_Object_Capacity.AVAILABLE_HOURS_BEFORE,
X_AVAILABLE_HOURS_AFTER => l_Object_Capacity.AVAILABLE_HOURS_AFTER,
X_SCHEDULE_DETAIL_ID => l_Object_Capacity.SCHEDULE_DETAIL_ID,
X_STATUS => l_Object_Capacity.STATUS,
X_AVAILABILITY_TYPE => l_Object_Capacity.AVAILABILITY_TYPE,
X_SOURCE_TYPE => l_Object_Capacity.SOURCE_TYPE,
X_CREATION_DATE => l_current_date,
X_CREATED_BY => l_user,
X_LAST_UPDATE_DATE => l_current_date,
X_LAST_UPDATED_BY => l_user,
X_LAST_UPDATE_LOGIN => l_login
);
END update_object_capacity;
PROCEDURE delete_object_capacity
/*******************************************************************************
** delete_object_capacity
**
** This API calls table handler to delete data from cac_sr_object_capacity.
** It will also update the task assignments and remove the object capacity id
** if the p_update_tasks is true
**
*******************************************************************************/
( p_api_version IN NUMBER -- API version you coded against
, p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
, p_object_capacity_id IN NUMBER -- Primary Key ID of the row to be updated
, p_object_version_number IN NUMBER -- Object Version of the row to be updated
-- If this doesn't match the database value then that means someone else has updated the same row
, p_Update_Tasks IN VARCHAR2 DEFAULT 'T' -- Update task assignments too?
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack
) IS SELECT
OBJECT_VERSION_NUMBER
FROM CAC_SR_OBJECT_CAPACITY
WHERE OBJECT_CAPACITY_ID = b_object_capacity_id
FOR UPDATE OF OBJECT_CAPACITY_ID NOWAIT;
select jta.task_assignment_id
, jta.object_version_number
from jtf_task_statuses_b jtsb
, jtf_task_assignments jta
, jtf_tasks_b jtb
where jtsb.task_status_id = jta.assignment_status_id
and jta.object_capacity_id = b_object_capacity_id
and jta.task_id = jtb.task_id
and nvl(jtb.deleted_flag, 'N') = 'N'
order by jtb.scheduled_start_date;
l_api_name constant varchar2(30) := 'DELETE_OBJECT_CAPACITY';
fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
IF fnd_api.to_boolean(p_Update_Tasks)
THEN
-- First get all the open task assignments and remove the object capacity
FOR ref_tasks IN c_tasks(p_object_capacity_id)
LOOP
-- Call assignments api to update object capacity id
JTF_TASK_ASSIGNMENTS_PUB.UPDATE_TASK_ASSIGNMENT
(
p_api_version => 1.0,
p_commit => fnd_api.G_FALSE,
p_object_version_number => ref_tasks.OBJECT_VERSION_NUMBER,
p_task_assignment_id => ref_tasks.TASK_ASSIGNMENT_ID,
p_enable_workflow => NULL,
p_abort_workflow => NULL,
p_object_capacity_id => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
CAC_SR_OBJECT_CAPACITY_PKG.DELETE_ROW
(
X_OBJECT_CAPACITY_ID => p_object_capacity_id
);
END delete_object_capacity;
** period and then calls insert_object_capacity to insert data.
**
*******************************************************************************/
( p_api_version IN NUMBER -- API version you coded against
, p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
, p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
, p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
, p_Start_Date_Time IN DATE -- start date and time of period of interest
, p_End_Date_Time IN DATE -- end date and time of period of interest
, p_Build_Mode IN VARCHAR2 -- operation mode of the build
-- 'ADD' - New object capacity records are generated and inserted
-- 'REPLACE' - Existing object capacity records are deleted and new ones are inserted
-- 'DELETE' - Existing object capacity records are deleted
, p_Update_Tasks IN VARCHAR2 DEFAULT 'F' -- Should the existing task assignments be updated with the object capacity ids?
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack