[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* insert_resource_dtl */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the insert into resources */
/* header (cr_rsrc_mst ) table is successfully. */
/* */
/* History : */
/* Sridhar 09-SEP-2002 Initial implementation */
/* =================================================================== */
PROCEDURE insert_resource_dtl
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_resources IN cr_rsrc_dtl%ROWTYPE
, p_rsrc_instances IN resource_instances_tbl
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status IN OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RESOURCE_DTL';
SELECT BOM_RESOURCES_S.nextval
FROM sys.dual;
SELECT GMP_RESOURCE_INSTANCES_S.nextval
FROM sys.DUAL;
SELECT STD_USAGE_UOM,CAPACITY_UM,MIN_CAPACITY,MAX_CAPACITY
FROM cr_rsrc_mst
WHERE resources = l_resources_rec.resources
AND delete_mark = 0;
,'INSERT_RESOURCE_DTL'
,GMP_RESOURCE_DTL_PUB.m_pkg_name) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
/* After Validating the data , Insert the Resource Detail rows
only if the Return Status is 'S' else,. error Out
*/
IF l_return_status = 'E' THEN /* resource return value */
RAISE resource_dtl_creation_failure;
ELSE /* Insert the Resource Data now */
OPEN Cur_resource_id;
insert_detail_rows
(
l_resources_rec.organization_id, /* B4724360 - INVCONV */
l_resources_rec.resources,
l_resources_rec.group_resource,
l_resources_rec.assigned_qty,
l_resources_rec.daily_avail_use,
v_std_usage_uom,
l_resources_rec.nominal_cost,
l_resources_rec.inactive_ind,
sysdate,
FND_GLOBAL.user_id, /* Bug 6412180 */
sysdate,
FND_GLOBAL.user_id, /* Bug 6412180 */
FND_GLOBAL.user_id, /* Bug 6412180 */
l_resources_rec.trans_cnt,
0,
l_resources_rec.text_code,
l_resources_rec.ideal_capacity,
v_min_capacity,
v_max_capacity,
v_capacity_uom,
v_resource_id,
l_resources_rec.capacity_constraint,
l_resources_rec.capacity_tolerance,
l_resources_rec.schedule_ind,
l_resources_rec.utilization,
l_resources_rec.efficiency,
l_resources_rec.planning_exception_set, /* Bug # 6413873 */
l_resources_rec.calendar_code, /* B4724360 - INVCONV */
l_resources_rec.sds_window,
nvl(l_resources_rec.batchable_flag,0), /* BUG 4157063 */
l_resources_rec.batch_window /* BUG 4157063 */
);
ELSE /* Insert the Resource Instance row */
insert_resource_instance
(
v_resource_id
,v_instance_id
,j /* p_instance_number */
,l_rsrc_instances(j).vendor_id
,l_rsrc_instances(j).model_number
,l_rsrc_instances(j).serial_number
,l_rsrc_instances(j).tracking_number
,l_rsrc_instances(j).eff_start_date
,l_rsrc_instances(j).eff_end_date
,l_rsrc_instances(j).last_maintenance_date
,l_rsrc_instances(j).maintenance_interval
,l_rsrc_instances(j).inactive_ind
,l_rsrc_instances(j).calibration_frequency
,l_rsrc_instances(j).calibration_period
,l_rsrc_instances(j).calibration_item_id
,l_rsrc_instances(j).last_calibration_date
,l_rsrc_instances(j).next_calibration_date
,l_rsrc_instances(j).last_certification_date
,l_rsrc_instances(j).certified_by
,sysdate
,FND_GLOBAL.user_id /* Bug 6412180 */
,sysdate
,FND_GLOBAL.user_id /* Bug 6412180 */
,FND_GLOBAL.user_id /* Bug 6412180 */
) ;
END IF; /* Insert Resource Instance only if Schedule Ind = 2 */
END insert_resource_dtl;
/* The following Procedure checks the Record and then Inserts */
/* the row into cr_rsrc_mst table and Returns S code if inserted */
/* Successfully */
/* */
/* History : */
/* Sgidugu 09/10/2002 Initial implementation */
/* Rajesh 11/28/2007 BUG 4157063 Resource batching */
/* =============================================================== */
PROCEDURE check_data
(
p_organization_id IN NUMBER, /* B4724360 - INVCONV */
p_resources IN VARCHAR2,
p_resource_id IN NUMBER, /* B4724360 - INVCONV */
p_group_resource IN VARCHAR2,
p_assigned_qty IN integer,
p_daily_avl_use IN NUMBER,
p_usage_um IN VARCHAR2,
p_nominal_cost IN NUMBER,
p_inactive_ind IN NUMBER,
p_ideal_capacity IN NUMBER,
p_min_capacity IN NUMBER,
p_max_capacity IN NUMBER,
p_capacity_uom IN VARCHAR2,
p_capacity_constraint IN NUMBER,
p_capacity_tolerance IN NUMBER,
p_schedule_ind IN NUMBER,
p_utilization IN NUMBER,
p_efficiency IN NUMBER,
p_calendar_code IN VARCHAR2, /* B4724360 - INVCONV */
p_batchable_flag IN NUMBER, /* BUG 4157063 */
p_batch_window IN NUMBER, /* BUG 4157063 */
x_message_count OUT NOCOPY NUMBER,
x_message_list OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
/* B4724360 Rajesh Patangya -Changed the source for organizations*/
CURSOR Cur_orgn_code IS
SELECT COUNT(1)
FROM mtl_parameters
where organization_id = p_organization_id
and process_enabled_flag = 'Y';
SELECT COUNT(1)
FROM cr_rsrc_mst
where resources = p_resources
and delete_mark = 0;
SELECT COUNT(1)
FROM cr_rsrc_dtl
WHERE organization_id = p_organization_id
AND resource_id = p_resource_id;
SELECT COUNT(1)
FROM cr_rsrc_dtl
WHERE organization_id = p_organization_id
AND resources = p_resources;
SELECT COUNT(1)
FROM mtl_units_of_measure
WHERE uom_code = p_usage_um;
SELECT COUNT(1)
FROM mtl_units_of_measure
WHERE uom_code = p_capacity_uom;
SELECT COUNT(1)
FROM bom_calendars
WHERE calendar_code = p_calendar_code;
IF v_update_flag <> 'Y' THEN
OPEN Cur_check_dup;
IF (p_schedule_ind = 2) AND (v_update_flag = 'Y') THEN
NULL;
/* insert_detail_rows */
/* */
/* DESCRIPTION: */
/* */
/* The following Procedure checks the Record and then Inserts */
/* the row into cr_rsrc_dtl table and Returns S code if inserted */
/* Successfully */
/* */
/* History : */
/* Sgidugu 09/10/2002 Initial implementation */
/* Kaushek B 13/09/07 B6413873 Added parameter */
/* planning_exception_set and insert value of planning_exception_set*/
/* =============================================================== */
PROCEDURE insert_detail_rows
(
p_organization_id IN NUMBER, /* B4724360 - INVCONV */
p_resources IN varchar2,
p_group_resource IN VARCHAR2,
p_assigned_qty IN NUMBER,
p_daily_avail_use IN NUMBER,
p_usage_um IN VARCHAR2,
p_nominal_cost IN NUMBER,
p_inactive_ind IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_trans_cnt IN NUMBER,
p_delete_mark IN NUMBER,
p_text_code IN NUMBER,
p_ideal_capacity IN NUMBER,
p_min_capacity IN NUMBER,
p_max_capacity IN NUMBER,
p_capacity_uom IN VARCHAR2,
p_resource_id IN NUMBER,
p_capacity_constraint IN NUMBER,
p_capacity_tolerance IN NUMBER,
p_schedule_ind IN NUMBER,
p_utilization IN NUMBER,
p_efficiency IN NUMBER,
p_planning_exception_set IN VARCHAR2, /* Bug # 6413873 */
p_calendar_code IN VARCHAR2, /* B4724360 - INVCONV */
p_sds_window IN NUMBER, /* B7637373 - VPEDARLA */
p_batchable_flag IN NUMBER, /* BUG 4157063 */
p_batch_window IN NUMBER /* BUG 4157063 */
) IS
BEGIN
INSERT INTO CR_RSRC_DTL(
ORGANIZATION_ID /* B4724360 - INVCONV */
,RESOURCES
,GROUP_RESOURCE
,ASSIGNED_QTY
,DAILY_AVAIL_USE
,USAGE_UOM /* B4724360 - INVCONV */
,NOMINAL_COST
,INACTIVE_IND
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,TRANS_CNT
,DELETE_MARK
,TEXT_CODE
,IDEAL_CAPACITY
,MIN_CAPACITY
,MAX_CAPACITY
,CAPACITY_UM /* B4724360 - INVCONV */
,RESOURCE_ID
,CAPACITY_CONSTRAINT
,CAPACITY_TOLERANCE
,SCHEDULE_IND
,UTILIZATION
,EFFICIENCY
,PLANNING_EXCEPTION_SET /* Bug # 6413873 */
,CALENDAR_CODE /* B4724360 - INVCONV */
,BATCHABLE_FLAG /* BUG 4157063 */
,BATCH_WINDOW /* BUG 4157063 */
,SDS_WINDOW /* B7637373 - VPEDARLA */
)
values (
p_organization_id , /* B4724360 - INVCONV */
p_resources ,
nvl(p_group_resource,p_resources),
p_assigned_qty ,
p_daily_avail_use,
p_usage_um ,
p_nominal_cost ,
p_inactive_ind ,
p_creation_date ,
p_created_by ,
p_last_update_date ,
p_last_updated_by ,
p_last_update_login ,
p_trans_cnt ,
p_delete_mark ,
p_text_code ,
p_ideal_capacity ,
p_min_capacity ,
p_max_capacity ,
p_capacity_uom ,
p_resource_id ,
p_capacity_constraint ,
p_capacity_tolerance ,
p_schedule_ind ,
p_utilization ,
p_efficiency ,
p_planning_exception_set, /* Bug # 6413873 */
p_calendar_code ,/* B4724360 - INVCONV */
p_batchable_flag , /* BUG 4157063 */
p_batch_window , /* BUG 4157063 */
p_sds_window /* B7637373 - VPEDARLA */
);
END insert_detail_rows;
/* insert_resource_instance */
/* */
/* DESCRIPTION: */
/* */
/* The following Procedure checks the Record and then Inserts */
/* the row into cr_rsrc_dtl table and Returns S code if inserted */
/* Successfully */
/* */
/* History : */
/* Sgidugu 09/10/2002 Initial implementation */
/* =============================================================== */
PROCEDURE insert_resource_instance (
p_resource_id IN NUMBER
,p_instance_id IN NUMBER
,p_instance_number IN NUMBER
,p_vendor_id IN NUMBER
,p_model_number IN VARCHAR2
,p_serial_number IN VARCHAR2
,p_tracking_number IN VARCHAR2
,p_eff_start_date IN DATE
,p_eff_end_date IN DATE
,p_last_maintenance_date IN DATE
,p_maintenance_interval IN NUMBER
,p_inactive_ind IN NUMBER
,p_calibration_frequency IN NUMBER
,p_calibration_period IN VARCHAR2
,p_calibration_item_id IN NUMBER
,p_last_calibration_date IN DATE
,p_next_calibration_date IN DATE
,p_last_certification_date IN DATE
,p_certified_by IN VARCHAR2
,p_creation_date IN DATE
,p_created_by IN NUMBER
,p_last_update_date IN DATE
,p_last_updated_by IN NUMBER
,p_last_update_login IN NUMBER ) IS
BEGIN
INSERT INTO gmp_resource_instances
(
RESOURCE_ID
,INSTANCE_ID
,INSTANCE_NUMBER
,VENDOR_ID
,MODEL_NUMBER
,SERIAL_NUMBER
,TRACKING_NUMBER
,EFF_START_DATE
,EFF_END_DATE
,LAST_MAINTENANCE_DATE
,MAINTENANCE_INTERVAL
,INACTIVE_IND
,CALIBRATION_FREQUENCY
,CALIBRATION_PERIOD
,CALIBRATION_ITEM_ID
,LAST_CALIBRATION_DATE
,NEXT_CALIBRATION_DATE
,LAST_CERTIFICATION_DATE
,CERTIFIED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
values (
p_resource_id
,p_instance_id
,p_instance_number
,p_vendor_id
,p_model_number
,p_serial_number
,p_tracking_number
,p_eff_start_date
,p_eff_end_date
,p_last_maintenance_date
,p_maintenance_interval
,p_inactive_ind
,p_calibration_frequency
,p_calibration_period
,p_calibration_item_id
,p_last_calibration_date
,p_next_calibration_date
,p_last_certification_date
,p_certified_by
,p_creation_date
,p_created_by
,p_last_update_date
,p_last_updated_by
,p_last_update_login
) ;
END insert_resource_instance ;
/* update_resource_dtl */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the update into Generic */
/* Resource Table */
/* */
/* History : */
/* Sgidugu 09/04/2002 Initial implementation */
/* =============================================================== */
PROCEDURE update_resource_dtl
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_resources IN cr_rsrc_dtl%ROWTYPE
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_DTL';
resource_update_failure EXCEPTION;
SAVEPOINT update_resource_dtl;
fnd_file.put_line(fnd_file.log,'UpdateResourcePub');
/* Set the Update Flag to Yes */
v_update_flag := 'Y';
RAISE resource_update_failure;
/* Update the Resource Data now */
update_detail_rows(
p_resources.organization_id, /* B4724360 - INVCONV */
p_resources.resources,
p_resources.group_resource,
p_resources.assigned_qty,
p_resources.daily_avail_use,
p_resources.usage_uom, /* B4724360 - INVCONV */
p_resources.nominal_cost,
p_resources.inactive_ind,
sysdate,
FND_GLOBAL.user_id, /* Bug 6412180 */
sysdate,
FND_GLOBAL.user_id, /* Bug 6412180 */
FND_GLOBAL.user_id, /* Bug 6412180 */
p_resources.trans_cnt,
p_resources.delete_mark,
p_resources.text_code,
p_resources.ideal_capacity,
p_resources.min_capacity,
p_resources.max_capacity,
p_resources.capacity_um, /* B4724360 - INVCONV */
p_resources.resource_id,
p_resources.capacity_constraint,
p_resources.capacity_tolerance,
p_resources.schedule_ind,
p_resources.utilization,
p_resources.efficiency,
p_resources.sds_window, /* B7637373 - VPEDARLA */
p_resources.planning_exception_set, /* Bug # 6413873 */
p_resources.calendar_code, /* B4724360 - INVCONV */
nvl(p_resources.batchable_flag,0), /* BUG 4157063 */
p_resources.batch_window, /* BUG 4157063 */
g_return_status
);
/* set the Update flag back to 'No' */
v_update_flag := 'N';
RAISE resource_update_failure;
fnd_file.put_line(fnd_file.log,'Resource was Updated successfullly');
WHEN resource_update_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT update_resource_dtl;
ROLLBACK TO SAVEPOINT update_resource_dtl;
END update_resource_dtl;
/* update_detail_rows */
/* */
/* DESCRIPTION: */
/* */
/* The following Procedure checks the Record and then Inserts */
/* the row into cr_rsrc_mst table and Returns S code if inserted */
/* Successfully */
/* */
/* History : */
/* Sgidugu 09/10/2002 Initial implementation */
/* Sgidugu 04/12/2002 Count is not Updated if Schedule Ind = 2 */
/* Kaushek B 13/09/07 B6413873 Added parameter planning_exception_set */
/* =============================================================== */
PROCEDURE update_detail_rows
(
p_organization_id IN NUMBER, /* B4724360 - INVCONV */
p_resources IN VARCHAR2,
p_group_resource IN VARCHAR2,
p_assigned_qty IN NUMBER,
p_daily_avail_use IN NUMBER,
p_usage_um IN VARCHAR2,
p_nominal_cost IN NUMBER,
p_inactive_ind IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_trans_cnt IN NUMBER,
p_delete_mark IN NUMBER,
p_text_code IN NUMBER,
p_ideal_capacity IN NUMBER,
p_min_capacity IN NUMBER,
p_max_capacity IN NUMBER,
p_capacity_uom IN VARCHAR2,
p_resource_id IN NUMBER,
p_capacity_constraint IN NUMBER,
p_capacity_tolerance IN NUMBER,
p_schedule_ind IN NUMBER,
p_utilization IN NUMBER,
p_efficiency IN NUMBER,
p_sds_window IN NUMBER, /* B7637373 - VPEDARLA */
p_planning_exception_set IN VARCHAR2, /* Bug # 6413873 */
p_calendar_code IN VARCHAR2, /* B4724360 - INVCONV */
p_batchable_flag IN NUMBER, /* BUG 4157063 */
p_batch_window IN NUMBER, /* BUG 4157063 */
x_return_status OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_schedule_ind = 2 THEN
UPDATE cr_rsrc_dtl
SET group_resource = p_group_resource,
daily_avail_use = p_daily_avail_use,
usage_uom = p_usage_um, /* B4724360 - INVCONV */
nominal_cost = p_nominal_cost,
inactive_ind = p_inactive_ind,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
trans_cnt = p_trans_cnt,
delete_mark = p_delete_mark,
text_code = p_text_code,
ideal_capacity = p_ideal_capacity,
min_capacity = p_min_capacity,
max_capacity = p_max_capacity,
capacity_um = p_capacity_uom, /* B4724360 - INVCONV */
capacity_constraint = p_capacity_constraint,
capacity_tolerance = p_capacity_tolerance,
schedule_ind = p_schedule_ind,
utilization = p_utilization,
efficiency = p_efficiency,
sds_window = p_sds_window, /* B7637373 - VPEDARLA */
planning_exception_set = p_planning_exception_set, /* Bug # 6413873 */
calendar_code = p_calendar_code , /* B4724360 - INVCONV */
batchable_flag = nvl(p_batchable_flag,0) , /* BUG 4157063 */
batch_window = p_batch_window /* BUG 4157063 */
WHERE resource_id = p_resource_id
AND organization_id = p_organization_id;
UPDATE cr_rsrc_dtl
SET group_resource = p_group_resource,
assigned_qty = p_assigned_qty,
daily_avail_use = p_daily_avail_use,
usage_uom = p_usage_um, /* B4724360 - INVCONV */
nominal_cost = p_nominal_cost,
inactive_ind = p_inactive_ind,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
trans_cnt = p_trans_cnt,
delete_mark = p_delete_mark,
text_code = p_text_code,
ideal_capacity = p_ideal_capacity,
min_capacity = p_min_capacity,
max_capacity = p_max_capacity,
capacity_uom = p_capacity_uom, /* B4724360 - INVCONV */
capacity_constraint = p_capacity_constraint,
capacity_tolerance = p_capacity_tolerance,
schedule_ind = p_schedule_ind,
utilization = p_utilization,
efficiency = p_efficiency,
sds_window = p_sds_window, /* B7637373 - VPEDARLA */
planning_exception_set = p_planning_exception_set, /* Bug # 6413873 */
calendar_code = p_calendar_code , /* B4724360 - INVCONV */
batchable_flag = nvl(p_batchable_flag,0) , /* BUG 4157063 */
batch_window = p_batch_window /* BUG 4157063 */
WHERE resource_id = p_resource_id
AND organization_id = p_organization_id;
ROLLBACK TO SAVEPOINT update_instances;
ROLLBACK TO SAVEPOINT update_resource_dtl;
END update_detail_rows;
/* update_instances */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the update into Generic */
/* Resource Table */
/* */
/* History : */
/* Rajesh Patangya 11/27/2002 Initial implementation */
/* =============================================================== */
PROCEDURE update_instances
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_instances IN gmp_resource_instances%ROWTYPE
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INSTANCES';
instance_update_failure EXCEPTION;
SAVEPOINT update_instances;
fnd_file.put_line(fnd_file.log,'UpdateInstancePub');
/* Set the Update Flag to Yes */
v_update_flag := 'Y';
RAISE instance_update_failure;
/* Update the Instance Data now */
update_instance_row(
p_instances.resource_id
,p_instances.instance_id
,p_instances.instance_number
,p_instances.vendor_id
,p_instances.model_number
,p_instances.serial_number
,p_instances.tracking_number
,p_instances.eff_start_date
,p_instances.eff_end_date
,p_instances.last_maintenance_date
,p_instances.maintenance_interval
,p_instances.inactive_ind
,p_instances.calibration_frequency
,p_instances.calibration_period
,p_instances.calibration_item_id
,p_instances.last_calibration_date
,p_instances.next_calibration_date
,p_instances.last_certification_date
,p_instances.certified_by
,sysdate
,FND_GLOBAL.user_id /* Bug 6412180 */
,sysdate
,FND_GLOBAL.user_id /* Bug 6412180 */
,FND_GLOBAL.user_id /* Bug 6412180 */
,g_return_status
);
/* set the Update flag back to 'No' */
v_update_flag := 'N';
RAISE instance_update_failure;
fnd_file.put_line(fnd_file.log,'Resource was Updated successfullly');
WHEN instance_update_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT update_instances;
ROLLBACK TO SAVEPOINT update_instances;
END update_instances;
/* update_instance_row */
/* */
/* DESCRIPTION: */
/* */
/* The following Procedure Updates the information in */
/* gmp_resource_instances table and Returns S code if updated */
/* Successfully */
/* */
/* History : */
/* Sgidugu 09/10/2002 Initial implementation */
/* =============================================================== */
PROCEDURE update_instance_row
(
p_resource_id IN NUMBER
,p_instance_id IN NUMBER
,p_instance_number IN NUMBER
,p_vendor_id IN NUMBER
,p_model_number IN VARCHAR2
,p_serial_number IN VARCHAR2
,p_tracking_number IN VARCHAR2
,p_eff_start_date IN DATE
,p_eff_end_date IN DATE
,p_last_maintenance_date IN DATE
,p_maintenance_interval IN NUMBER
,p_inactive_ind IN NUMBER
,p_calibration_frequency IN NUMBER
,p_calibration_period IN VARCHAR2
,p_calibration_item_id IN NUMBER
,p_last_calibration_date IN DATE
,p_next_calibration_date IN DATE
,p_last_certification_date IN DATE
,p_certified_by IN VARCHAR2
,p_creation_date IN DATE
,p_created_by IN NUMBER
,p_last_update_date IN DATE
,p_last_updated_by IN NUMBER
,p_last_update_login IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
) IS
BEGIN
UPDATE gmp_resource_instances set
vendor_id = p_vendor_id
,model_number = p_model_number
,serial_number = p_serial_number
,tracking_number = p_tracking_number
,eff_start_date = p_eff_start_date
,eff_end_date = p_eff_end_date
,last_maintenance_date = p_last_maintenance_date
,maintenance_interval = p_maintenance_interval
,inactive_ind = p_inactive_ind
,calibration_frequency = p_calibration_frequency
,calibration_period = p_calibration_period
,calibration_item_id = p_calibration_item_id
,last_calibration_date = p_last_calibration_date
,next_calibration_date = p_next_calibration_date
,last_certification_date = p_last_certification_date
,certified_by = p_certified_by
,creation_date = p_creation_date
,created_by = p_created_by
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
WHERE resource_id = p_resource_id
AND instance_id = p_instance_id ;
ROLLBACK TO SAVEPOINT update_instances;
ROLLBACK TO SAVEPOINT update_instances;
END update_instance_row ;
/* delete_resources */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the delete Resources */
/* was Successful */
/* */
/* History : */
/* Sgidugu 09/04/2002 Initial implementation */
/* =============================================================== */
PROCEDURE delete_resources
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_organization_id IN cr_rsrc_dtl.organization_id%TYPE
, p_resources IN cr_rsrc_dtl.resources%TYPE
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_counter number ;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCES';
resource_delete_failure EXCEPTION;
SELECT resource_id
FROM cr_rsrc_dtl
WHERE organization_id = p_organization_id
AND resources = p_resources;
SAVEPOINT delete_resources;
fnd_file.put_line(fnd_file.log,'DeleteResourcePub');
GMD_DEBUG.PUT_LINE('Resource to be deleted Does Not Exist ');
RAISE resource_delete_failure;
delete from gmp_rsrc_excp_asnmt
WHERE resource_id = v_resource_id ;
delete from gmp_rsrc_unavail_man
WHERE resource_id = v_resource_id ;
delete from gmp_resource_instances
where resource_id = v_resource_id;
delete from gmp_resource_avail
where resource_id = v_resource_id
and organization_id = p_organization_id
and RESOURCE_INSTANCE_ID IS NOT NULL ;
delete from cr_rsrc_dtl
where resource_id = v_resource_id
and organization_id = p_organization_id;
fnd_file.put_line(fnd_file.log,'Resource was deleted successfully');
WHEN resource_delete_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT delete_resources;
ROLLBACK TO SAVEPOINT delete_resources;
END delete_resources;
/* The following Procedure checks the Record and then Inserts */
/* the row into cr_rsrc_mst table and Returns S code if inserted */
/* Successfully */
/* */
/* History : */
/* Sgidugu 09/10/2002 Initial implementation */
/* =============================================================== */
PROCEDURE check_instance_data
(
p_resource_id IN NUMBER
,p_instance_id IN NUMBER
,p_vendor_id IN NUMBER
,p_eff_start_date IN DATE
,p_eff_end_date IN DATE
,p_maintenance_interval IN NUMBER
,p_inactive_ind IN NUMBER
,p_calibration_frequency IN NUMBER
,p_calibration_item_id IN NUMBER
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR Cur_check_dup IS
SELECT COUNT(1)
FROM gmp_resource_instances
where instance_id = p_instance_id
and resource_id = p_resource_id ;
SELECT COUNT(1)
FROM po_vendors
where vendor_id = p_vendor_id
and enabled_flag = 'Y' ;
SELECT COUNT(1)
FROM ic_item_mst
where item_id = p_calibration_item_id
and delete_mark = 0 ;
IF v_update_flag <> 'Y' THEN
OPEN Cur_check_dup;
SELECT to_date((FND_PROFILE.VALUE('SY$MAX_DATE')),'YYYY/MM/DD')
INTO l_sy_date FROM SYS.DUAL ;