[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT doc_id batch_id
FROM gme_resource_txns_gtmp
ORDER BY batch_id;
delete from gme_resource_txns_gtmp
where doc_id = header_row.batch_id;
SELECT inventory_item_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_vrule_id;
SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = v_item_no;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM mtl_units_of_measure
WHERE uom_code = v_uom_code);
# update_actual_rsrc_usage
# DESCRIPTION
#
# HISTORY :
# 10-MAR-2005 Punit Kumar Convergence changes
#########################################################################*/
PROCEDURE update_actual_rsrc_usage (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_instance_no IN NUMBER := NULL
,p_reason_name IN VARCHAR2 := NULL
,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,p_validate_flexfields IN VARCHAR2 := fnd_api.g_false
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ACTUAL_RSRC_USAGE';
SELECT r.*
FROM gme_resource_txns_gtmp t, gme_resource_txns r
WHERE action_code = 'NONE' AND t.line_id = v_line_id
AND t.poc_trans_id = r.poc_trans_id;
update_rsrc_usage EXCEPTION;
SAVEPOINT update_actual_rsrc_usage;
gme_debug.log_initialize ('UpdateResource');
,'update_actual_resource_usage'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
||'BEFORE CALLING gme_resource_engine_pvt.update_actual_resource_usagep ' );
gme_resource_engine_pvt.update_actual_resource_usage
(p_org_code => p_org_code
,p_batch_no => p_batch_no
,p_batchstep_no => p_batchstep_no
,p_activity => p_activity
,p_resource => p_resource
,p_instance_no => p_instance_no
,p_reason_name => p_reason_name
,p_rsrc_txn_rec => p_rsrc_txn_rec
,x_rsrc_txn_rec => x_rsrc_txn_rec
,x_return_status => x_return_status);
RAISE update_rsrc_usage;
ROLLBACK TO SAVEPOINT update_actual_rsrc_usage;
WHEN update_rsrc_usage THEN
ROLLBACK TO SAVEPOINT update_actual_rsrc_usage;
ROLLBACK TO SAVEPOINT update_actual_rsrc_usage;
END update_actual_rsrc_usage;
# insert_incr_actual_rsrc_txn
# DESCRIPTION
# HISTORY
# 10-MAR-2005 Punit Kumar
# Convergence changes
#########################################################################*/
PROCEDURE insert_incr_actual_rsrc_txn (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_instance_no IN NUMBER := NULL
,p_reason_name IN VARCHAR2 := NULL
,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,p_validate_flexfields IN VARCHAR2 := fnd_api.g_false
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_INCR_ACTUAL_RSRC_TXN';
SAVEPOINT insert_incr_actual_rsrc_txn;
gme_debug.log_initialize ('InsertIncr');
,'insert_incr_actual_rsrc_txn'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
||'BEFORE CALLING gme_resource_engine_pvt.insert_incr_actual_rsrc_txnp ' );
gme_resource_engine_pvt.insert_incr_actual_rsrc_txn
(p_org_code => p_org_code
,p_batch_no => p_batch_no
,p_batchstep_no => p_batchstep_no
,p_activity => p_activity
,p_resource => p_resource
,p_instance_no => p_instance_no
,p_reason_name => p_reason_name
,p_rsrc_txn_rec => p_rsrc_txn_rec
,x_rsrc_txn_rec => x_rsrc_txn_rec
,x_return_status => x_return_status);
gme_debug.put_line ( 'Insert Incr Actual rsrc usage at '
|| TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
ROLLBACK TO SAVEPOINT insert_incr_actual_rsrc_txn;
ROLLBACK TO SAVEPOINT insert_incr_actual_rsrc_txn;
END insert_incr_actual_rsrc_txn;
# insert_timed_actual_rsrc_txn
# DESCRIPTION
# This procedure calculates the resource usage based on
# difference of p_end_date and p_start_date converted in resource UOM.
# HISTORY
# 10-MAR-2005 Punit Kumar
# Convergence changes
#########################################################################*/
PROCEDURE insert_timed_actual_rsrc_txn (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_instance_no IN NUMBER := NULL
,p_reason_name IN VARCHAR2 := NULL
,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,p_validate_flexfields IN VARCHAR2 := fnd_api.g_false
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_TIMED_ACTUAL_RSRC_TXN';
SAVEPOINT insert_timed_actual_rsrc_txn;
gme_debug.log_initialize ('InsertTimed');
,'insert_timed_actual_rsrc_txn'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
||'BEFORE CALLING gme_resource_engine_pvt.insert_timed_actual_rsrc_txnp ');
gme_resource_engine_pvt.insert_timed_actual_rsrc_txn
(p_org_code => p_org_code
,p_batch_no => p_batch_no
,p_batchstep_no => p_batchstep_no
,p_activity => p_activity
,p_resource => p_resource
,p_instance_no => p_instance_no
,p_reason_name => p_reason_name
,p_rsrc_txn_rec => p_rsrc_txn_rec
,x_rsrc_txn_rec => x_rsrc_txn_rec
,x_return_status => x_return_status);
gme_debug.put_line ( 'Insert timed Actual rsrc usage at '
|| TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
ROLLBACK TO SAVEPOINT insert_timed_actual_rsrc_txn;
ROLLBACK TO SAVEPOINT insert_timed_actual_rsrc_txn;
END insert_timed_actual_rsrc_txn;
# At a later time this transaction is updated with the usage
# by calling gme_api_pub.end_cmplt_actual_rsrc_txn (It passes
# the end date and the usage is calculated as diferrence between
# end_date and start_date)
# HISTORY
# 10-MAR-2005 Punit Kumar
# Convergence changes
# Pawan kumar corrected check for organization_id and various parameters
#########################################################################*/
PROCEDURE start_cmplt_actual_rsrc_txn (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_instance_no IN NUMBER
,p_reason_name IN VARCHAR2 := NULL
,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,p_validate_flexfields IN VARCHAR2 := fnd_api.g_false
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'START_CMPLT_ACTUAL_RSRC_TXN';
gme_debug.put_line ( 'Insert Incr Actual rsrc usage at '
|| TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
,'insert_batchstep_resource'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
,'insert_batchstep_resource'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
/* Verify that update_inventory is permitted for this batch */
IF l_batch_header_rec.update_inventory_ind <> 'Y' THEN
gme_common_pvt.log_message ('GME_INVENTORY_UPDATE_BLOCKED');
PROCEDURE insert_process_parameter (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_batch_no IN VARCHAR2
,p_org_code IN VARCHAR2
,p_validate_flexfields IN VARCHAR2
,p_batchstep_no IN NUMBER
,p_activity IN VARCHAR2
,p_parameter IN VARCHAR2
,p_process_param_rec IN gme_process_parameters%ROWTYPE
,x_process_param_rec OUT NOCOPY gme_process_parameters%ROWTYPE)
IS
insert_parameter_failed EXCEPTION;
l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_PROCESS_PARAMETER';
SAVEPOINT insert_process_parameter;
gme_debug.log_initialize ('InsertProcessParameters');
,'Insert_process_parameter'
,g_pkg_name) THEN
x_return_status := fnd_api.g_ret_sts_error;
gme_debug.put_line ('Calling Insert process parameters');
gme_process_parameters_pvt.insert_process_parameter
(p_batch_no => p_batch_no
,p_org_code => p_org_code
,p_validate_flexfields => p_validate_flexfields
,p_batchstep_no => p_batchstep_no
,p_activity => p_activity
,p_parameter => p_parameter
,p_process_param_rec => p_process_param_rec
,x_process_param_rec => x_process_param_rec
,x_return_status => x_return_status);
( 'Came back from Pvt insert process parameter with status '
|| x_return_status);
RAISE insert_parameter_failed;
ROLLBACK TO SAVEPOINT insert_process_parameter;
WHEN insert_parameter_failed THEN
ROLLBACK TO SAVEPOINT insert_process_parameter;
ROLLBACK TO SAVEPOINT insert_process_parameter;
END insert_process_parameter;
PROCEDURE update_process_parameter (
p_api_version IN NUMBER
,p_validation_level IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_batch_no IN VARCHAR2
,p_org_code IN VARCHAR2
,p_validate_flexfields IN VARCHAR2
,p_batchstep_no IN NUMBER
,p_activity IN VARCHAR2
,p_parameter IN VARCHAR2
,p_process_param_rec IN gme_process_parameters%ROWTYPE
,x_process_param_rec OUT NOCOPY gme_process_parameters%ROWTYPE)
IS
update_parameter_failed EXCEPTION;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_PROCESS_PARAMETER';
SAVEPOINT update_process_parameter;
gme_debug.log_initialize ('UpdateProcessParameter');
,'Insert_process_parameter'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_process_parameters_pvt.update_process_parameter
(p_batch_no => p_batch_no
,p_org_code => p_org_code
,p_validate_flexfields => p_validate_flexfields
,p_batchstep_no => p_batchstep_no
,p_activity => p_activity
,p_parameter => p_parameter
,p_process_param_rec => p_process_param_rec
,x_process_param_rec => x_process_param_rec
,x_return_status => x_return_status);
( 'Came back from Pvt update process parameter with status '
|| x_return_status);
RAISE update_parameter_failed;
ROLLBACK TO SAVEPOINT update_process_parameter;
WHEN update_parameter_failed THEN
ROLLBACK TO SAVEPOINT update_process_parameter;
ROLLBACK TO SAVEPOINT update_process_parameter;
END update_process_parameter;
PROCEDURE delete_process_parameter (
p_api_version IN NUMBER
,p_validation_level IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_batch_no IN VARCHAR2
,p_org_code IN VARCHAR2
,p_batchstep_no IN NUMBER
,p_activity IN VARCHAR2
,p_parameter IN VARCHAR2
,p_process_param_rec IN gme_process_parameters%ROWTYPE)
IS
delete_parameter_failed EXCEPTION;
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_PROCESS_PARAMETER';
SAVEPOINT delete_process_parameter;
gme_debug.log_initialize ('DeleteProcessParameter');
,'Insert_process_parameter'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_debug.put_line ('Calling Insert process parameters');
gme_process_parameters_pvt.delete_process_parameter
(p_batch_no => p_batch_no
,p_org_code => p_org_code
,p_batchstep_no => p_batchstep_no
,p_activity => p_activity
,p_parameter => p_parameter
,p_process_param_rec => p_process_param_rec
,x_return_status => x_return_status);
( 'Came back from Pvt delete process parameter with status '
|| x_return_status);
RAISE delete_parameter_failed;
ROLLBACK TO SAVEPOINT delete_process_parameter;
WHEN delete_parameter_failed THEN
ROLLBACK TO SAVEPOINT delete_process_parameter;
ROLLBACK TO SAVEPOINT delete_process_parameter;
END delete_process_parameter;
PROCEDURE delete_step (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2
,p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_batch_step_rec IN gme_batch_steps%ROWTYPE)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_STEP';
delete_step_failed EXCEPTION;
SAVEPOINT delete_step;
,'delete_step'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_api_main.delete_step (p_validation_level => p_validation_level
,p_init_msg_list => fnd_api.g_false
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => x_return_status
,p_batch_header_rec => l_batch_header_rec
,p_batch_step_rec => p_batch_step_rec);
RAISE delete_step_failed;
ROLLBACK TO SAVEPOINT delete_step;
WHEN delete_step_failed THEN
ROLLBACK TO SAVEPOINT delete_step;
ROLLBACK TO SAVEPOINT delete_step;
END delete_step;
* insert_step
* History
* Punit Kumar 07-Apr-2005
* Convergence Changes
* SivakumarG 16-NOV-2005 FPBug#4395561
* Added new argument p_validate_flexfields
/*************************************************************************/
PROCEDURE insert_step (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2
,p_org_code IN VARCHAR2
,p_validate_flexfields IN VARCHAR2 := fnd_api.g_false
,p_oprn_no IN VARCHAR2
,p_oprn_vers IN NUMBER
,p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_batch_step_rec IN gme_batch_steps%ROWTYPE
,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_STEP';
insert_step_failed EXCEPTION;
SELECT oprn_no, oprn_vers
FROM gmd_operations_b
WHERE oprn_id = l_oprn_id;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM gmd_operations_b
WHERE oprn_no = l_oprn_no
AND oprn_vers = l_oprn_vers
AND owner_organization_id = l_org_id);
SAVEPOINT insert_step;
,'insert_step'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
/* Bug 2766460 Added check not to allow insert step if batch is completed/closed or cancelled */
IF (l_batch_header_rec.batch_status IN (gme_common_pvt.g_batch_cancelled,
gme_common_pvt.g_batch_completed,
gme_common_pvt.g_batch_closed) ) THEN
gme_common_pvt.log_message ('GME_INV_STATUS_INSERT_STEP');
gme_common_pvt.log_message ('GME_INV_STATUS_INSERT_STEP');
RAISE insert_step_failed;
RAISE insert_step_failed;
RAISE insert_step_failed;
gme_api_main.insert_step (p_validation_level => p_validation_level
,p_init_msg_list => fnd_api.g_false
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => x_return_status
,p_batch_header_rec => l_batch_header_rec
,p_batch_step_rec => p_batch_step_rec
,x_batch_step => x_batch_step_rec);
RAISE insert_step_failed;
ROLLBACK TO SAVEPOINT insert_step;
WHEN insert_step_failed THEN
ROLLBACK TO SAVEPOINT insert_step;
ROLLBACK TO SAVEPOINT insert_step;
END insert_step;
insert_material_line
Description
This procedure is used to insert a material line
Parameters
p_batch_header_rec (R) batch for which material line has to be updated
p_material_detail_rec (R) material line details that has to be inserted
p_locator_code (O) Default Consumption/Yield Locator Code
p_org_code (O) organization code
p_batchstep_no(O) batch step no
p_validate_flexfields (O) Whether to validate the flexfields
x_material_detail_rec inserted material detail record
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
HISTORY
SivakumarG Bug#5078853 02-MAR-2006
Procedure Created
G. Muratore 12-JUN-2009 Bug 7562848
Allow users to insert material lines on phantom batches.
Added parameter p_check_phantom when calling validate_batch.
================================================================================*/
PROCEDURE insert_material_line (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
,p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
,p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_material_detail_rec IN gme_material_details%ROWTYPE
,p_locator_code IN VARCHAR2
,p_org_code IN VARCHAR2
,p_batchstep_no IN NUMBER := NULL
,p_validate_flexfields IN VARCHAR2 DEFAULT fnd_api.g_false
,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 )
IS
/* get the locator id */
CURSOR c_get_locator(v_org_id NUMBER,v_sub_inv VARCHAR2) IS
SELECT inventory_location_id locator_id
FROM mtl_item_locations_kfv
WHERE organization_id = v_org_id
AND subinventory_code = v_sub_inv
AND concatenated_segments = p_locator_code;
l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_MATERIAL_LINE';
gme_debug.log_initialize ('INSERT_MATERIAL_LINE');
SAVEPOINT insert_material_line;
,'insert_material_line'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_api_main.insert_material_line (
p_validation_level => p_validation_level
,p_init_msg_list => p_init_msg_list
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => x_return_status
,p_batch_header_rec => l_batch_header_rec
,p_material_detail_rec => l_material_detail_rec
,p_batch_step_rec => l_batch_step_rec
,p_trans_id => NULL
,x_transacted => x_transacted
,x_material_detail_rec => x_material_detail_rec);
ROLLBACK TO SAVEPOINT insert_material_line;
ROLLBACK TO SAVEPOINT insert_material_line;
END insert_material_line;
update_material_line
Description
This procedure is used to update a material line
Parameters
p_batch_header_rec (R) batch for which material line has to be updated
p_material_detail_rec (R) material line that has to be updated
p_locator_code (O) Default Consumption/Yield Locator Code
p_org_code (O) organization code
p_scale_phantom (O) to scale phantom product only or total batch
p_validate_flexfields (O) Whether to validate the flexfields
x_material_detail_rec updated material detail record
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
HISTORY
SivakumarG Bug#5078853 02-MAR-2006
Procedure Created
================================================================================*/
PROCEDURE update_material_line (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
,p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
,p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_material_detail_rec IN gme_material_details%ROWTYPE
,p_locator_code IN VARCHAR2
,p_org_code IN VARCHAR2
,p_scale_phantom IN VARCHAR2 DEFAULT fnd_api.g_false
,p_validate_flexfields IN VARCHAR2 DEFAULT fnd_api.g_false
,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 )
IS
/* get the locator id */
CURSOR c_get_locator(v_org_id NUMBER,v_sub_inv VARCHAR2,v_locator VARCHAR2) IS
SELECT inventory_location_id locator_id
FROM mtl_item_locations_kfv
WHERE organization_id = v_org_id
AND subinventory_code = v_sub_inv
AND concatenated_segments = v_locator;
SELECT batchstep_id
FROM gme_batch_step_items
WHERE material_detail_id = v_mat_id;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_LINE';
SAVEPOINT update_material_line;
,'update_material_line'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_api_main.update_material_line (
p_validation_level => p_validation_level
,p_init_msg_list => p_init_msg_list
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => x_return_status
,p_batch_header_rec => l_batch_header_rec
,p_material_detail_rec => l_material_detail_rec
,p_stored_material_detail_rec => l_stored_material_detail_rec
,p_batch_step_rec => l_batch_step_rec
,p_scale_phantom => p_scale_phantom
,p_trans_id => NULL
,x_transacted => x_transacted
,x_material_detail_rec => x_material_detail_rec);
ROLLBACK TO SAVEPOINT update_material_line;
ROLLBACK TO SAVEPOINT update_material_line;
END update_material_line;
delete_material_line
Description
This procedure is used to delete a material line
Parameters
p_batch_header_rec (R) batch for which material line has to be updated
p_material_detail_rec (R) material line that has to be updated
p_org_code (O) organization code
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
HISTORY
SivakumarG Bug#5078853 02-MAR-2006
Procedure Created
================================================================================*/
PROCEDURE delete_material_line (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
,p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
,p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_material_detail_rec IN gme_material_details%ROWTYPE
,p_org_code IN VARCHAR2
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 )
IS
CURSOR c_get_step_id(v_mat_id NUMBER) IS
SELECT batchstep_id
FROM gme_batch_step_items
WHERE material_detail_id = v_mat_id;
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_MATERIAL_LINE';
gme_debug.log_initialize ('DELETE_MATERIAL_LINE');
SAVEPOINT delete_material_line;
,'delete_material_line'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_api_main.delete_material_line (
p_validation_level => p_validation_level
,p_init_msg_list => p_init_msg_list
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => x_return_status
,p_batch_header_rec => l_batch_header_rec
,p_material_detail_rec => l_material_detail_rec
,p_batch_step_rec => l_batch_step_rec
,x_transacted => x_transacted );
ROLLBACK TO SAVEPOINT delete_material_line;
ROLLBACK TO SAVEPOINT delete_material_line;
END delete_material_line;
SELECT COUNT(1)
FROM mtl_material_transactions mmt, gme_material_details mtl
WHERE NVL(transfer_lpn_id,0) > 0
AND TRANSACTION_SOURCE_ID = p_batch_id
AND transaction_action_id = 31
AND transaction_type_id IN (44, 1002)
AND mmt.transaction_source_id = mtl.batch_id
AND mmt.inventory_item_id = mtl.inventory_item_id
AND mmt.transaction_id NOT IN (
SELECT transaction_id1
FROM gme_transaction_pairs
WHERE batch_id = p_batch_id
AND pair_type = 1);
,p_delete_pending IN VARCHAR2 := fnd_api.g_false
,p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2
,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'CLOSE_STEP';
,p_delete_pending => p_delete_pending
,x_batch_step_rec => x_batch_step_rec);
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
SELECT *
FROM mtl_material_transactions
WHERE transaction_set_id = v_header_id;
PROCEDURE update_material_txn (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_transaction_id IN NUMBER
,p_mmti_rec IN mtl_transactions_interface%ROWTYPE
,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
,p_create_lot IN VARCHAR2 := NULL
,p_generate_lot IN VARCHAR2 := NULL
,p_generate_parent_lot IN VARCHAR2 := NULL
,x_mmt_rec OUT NOCOPY mtl_material_transactions%ROWTYPE
,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl)
IS
CURSOR cur_get_item_rec (v_item_id NUMBER, v_org_id NUMBER)
IS
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
SELECT *
FROM mtl_material_transactions
WHERE transaction_set_id = v_header_id;
SELECT organization_id, transaction_source_id, trx_source_line_id, transaction_type_id
FROM mtl_material_transactions
WHERE transaction_id = v_transaction_id;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN';
update_txn_fail EXCEPTION;
update_txn_mismatch EXCEPTION;
gme_debug.log_initialize ('UpdateTxn');
SAVEPOINT update_transaction;
,'update_material_txn'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
RAISE update_txn_mismatch;
gme_transactions_pvt.update_material_txn
(p_transaction_id => p_transaction_id
,p_mmti_rec => l_mmti_rec
,p_mmli_tbl => l_mmli_tbl
,x_return_status => l_return_status);
RAISE update_txn_fail;
WHEN update_txn_mismatch THEN
ROLLBACK TO SAVEPOINT update_transaction;
gme_common_pvt.log_message('GME_TXN_UPDATE_MISMATCH');
WHEN update_txn_fail THEN
ROLLBACK TO SAVEPOINT update_transaction;
ROLLBACK TO SAVEPOINT update_transaction;
END update_material_txn;
PROCEDURE delete_material_txn (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_transaction_id IN NUMBER)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_MATERIAL_TXN';
delete_txn_fail EXCEPTION;
SELECT organization_id
FROM mtl_material_transactions
WHERE transaction_id = v_transaction_id;
gme_debug.log_initialize ('DeleteTxn');
SAVEPOINT delete_transaction;
,'delete_material_txn'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_transactions_pvt.delete_material_txn
(p_transaction_id => l_transaction_id
,x_return_status => x_return_status);
RAISE delete_txn_fail;
WHEN delete_txn_fail THEN
ROLLBACK TO SAVEPOINT delete_transaction;
ROLLBACK TO SAVEPOINT delete_transaction;
END delete_material_txn;
SELECT 1
FROM mtl_transaction_reasons
WHERE NVL (disable_date, SYSDATE + 1) > SYSDATE
AND reason_id = v_reason_id;
SELECT reason_id
FROM mtl_transaction_reasons
WHERE NVL (disable_date, SYSDATE + 1) > SYSDATE
AND reason_name = v_reason_name;
SELECT *
FROM mtl_reservations
WHERE reservation_id = v_reservation_id;
PROCEDURE insert_batchstep_resource (
p_api_version IN NUMBER
,p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
,p_org_code IN VARCHAR2 := NULL
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_ignore_qty_below_cap IN VARCHAR2 := fnd_api.g_false
,p_validate_flexfields IN VARCHAR2 := fnd_api.g_false
,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'INSERT_BATCHSTEP_RESOURCE';
l_delete_mark NUMBER;
insert_rsrc_failed EXCEPTION;
SELECT batchstep_id, batch_id
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_activity_id;
SELECT 1
FROM gme_batch_header a, gme_batch_step_activities b
WHERE a.batch_id = b.batch_id
AND b.batchstep_activity_id = v_activity_id
AND a.batch_type = 10;
SELECT min_capacity, max_capacity, capacity_constraint, capacity_um
,usage_uom, delete_mark, capacity_tolerance
FROM cr_rsrc_dtl
WHERE resources = v_resources
AND organization_id = v_organization_id;
SELECT min_capacity, max_capacity, capacity_constraint, capacity_um
,std_usage_uom, delete_mark, capacity_tolerance
FROM cr_rsrc_mst
WHERE resources = v_resources;
SELECT step_status
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
gme_debug.log_initialize ('InsertBatchstepResource');
SAVEPOINT insert_batchstep_rsrc;
,'insert_batchstep_resource'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
,l_capacity_um, l_usage_uom, l_delete_mark, l_capacity_tolerance;
,l_capacity_um, l_usage_uom, l_delete_mark
,l_capacity_tolerance;
IF cur_get_rsrc_hdr%NOTFOUND OR l_delete_mark = 1 THEN
CLOSE cur_get_rsrc_dtl;
,p_action => 'INSERT'
,x_batchstep_resource_rec => l_batchstep_resource_rec_out
,x_step_status => l_step_status
,x_return_status => l_return_status);
|| ' invoke private layer insert_batchstep_rsrc');
gme_batchstep_rsrc_pvt.insert_batchstep_rsrc
(p_batchstep_resource_rec => l_batchstep_resource_rec
,x_batchstep_resource_rec => x_batchstep_resource_rec
,x_return_status => x_return_status);
insert_batchstep_rsrc returns '|| x_return_status);
RAISE insert_rsrc_failed;
WHEN insert_rsrc_failed THEN
ROLLBACK TO SAVEPOINT insert_batchstep_rsrc;
ROLLBACK TO SAVEPOINT insert_batchstep_rsrc;
ROLLBACK TO SAVEPOINT insert_batchstep_rsrc;
END insert_batchstep_resource;
PROCEDURE update_batchstep_resource (
p_api_version IN NUMBER
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
,p_org_code IN VARCHAR2 := NULL
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_ignore_qty_below_cap IN VARCHAR2 := fnd_api.g_false
,p_validate_flexfields IN VARCHAR2 := fnd_api.g_false
,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'UPDATE_BATCHSTEP_RESOURCE';
SELECT step_status
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id AND batch_id = v_batch_id;
update_rsrc_failed EXCEPTION;
SAVEPOINT update_batchstep_rsrc;
gme_debug.log_initialize ('UpdateBatchstepResource');
,'update_batchstep_resource'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
/* Retrieve the row to be updated */
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| 'Invoke get_batchstep_rsrc');
/* Don't overwrite any other input data which carries the updates */
l_batchstep_resource_rec.organization_id :=
l_batchstep_resource_rec_out.organization_id;
,p_action => 'UPDATE'
,x_batchstep_resource_rec => l_batchstep_resource_rec_out
,x_step_status => l_step_status
,x_return_status => l_return_status);
|| ' Invoke gme_batchstep_rsrc_pvt.update_batchstep_rsrc');
gme_batchstep_rsrc_pvt.update_batchstep_rsrc
(p_batchstep_resource_rec => l_batchstep_resource_rec
,x_batchstep_resource_rec => x_batchstep_resource_rec
,x_return_status => x_return_status);
|| ' update_batchstep_rsrc returns '
|| x_return_status);
RAISE update_rsrc_failed;
WHEN update_rsrc_failed THEN
ROLLBACK TO SAVEPOINT update_batchstep_rsrc;
ROLLBACK TO SAVEPOINT update_batchstep_rsrc;
ROLLBACK TO SAVEPOINT update_batchstep_rsrc;
END update_batchstep_resource;
PROCEDURE delete_batchstep_resource (
p_api_version IN NUMBER := 2.0
,p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_batchstep_resource_id IN NUMBER := NULL
,p_org_code IN VARCHAR2 := NULL
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_BATCHSTEP_RESOURCE';
SELECT c.organization_id, a.step_status, a.batch_id, a.batchstep_id
,b.batchstep_activity_id, c.batch_status
FROM gme_batch_steps a
,gme_batch_step_resources b
,gme_batch_header c
WHERE a.batch_id = b.batch_id
AND a.batchstep_id = b.batchstep_id
AND b.batchstep_resource_id = v_batchstep_rsrc_id
AND a.batch_id = c.batch_id;
SELECT 1
FROM gme_batch_header a, gme_batch_step_resources b
WHERE a.batch_id = b.batch_id
AND b.batchstep_resource_id = v_rsrc_id
AND a.batch_type = 10;
delete_rsrc_failed EXCEPTION;
SAVEPOINT delete_batchstep_rsrc;
gme_debug.log_initialize ('DeleteBatchstepResource');
,'delete_batchstep_resource'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
' invoke gme_batchstep_rsrc_pvt.delete_batchstep_rsrc ');
gme_batchstep_rsrc_pvt.delete_batchstep_rsrc
(p_batchstep_resource_rec => l_batchstep_resource_rec
,x_return_status => x_return_status);
status from delete is ' || x_return_status);
RAISE delete_rsrc_failed;
WHEN delete_rsrc_failed THEN
ROLLBACK TO SAVEPOINT delete_batchstep_rsrc;
ROLLBACK TO SAVEPOINT delete_batchstep_rsrc;
ROLLBACK TO SAVEPOINT delete_batchstep_rsrc;
END delete_batchstep_resource;
SELECT *
FROM mtl_reservations
WHERE reservation_id = v_reservation_id;
/* Verify that update_inventory is permitted for this batch */
IF l_batch_header_rec.update_inventory_ind <> 'Y' THEN
gme_common_pvt.log_message ('GME_INVENTORY_UPDATE_BLOCKED');
insert_batchstep_activity
Description
This procedure is used to insert an activity for a step
Parameters
p_batchstep_activity_rec (R) activity to be inserted for a step
p_batchstep_resource_tbl (R) one or more resources to be inserted for the activity
p_org_code (O) organization code
p_batch_no (O) batch number
p_batchstep_no(O) batch step number
p_ignore_qty_below_cap (O) controls - allow rsrc_qty going below rsrc capacity
p_validate_flexfield (O) Whether to validate the flexfields
x_batchstep_activity_rec newly inserted activity row, can be used for update etc
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
HISTORY
Sivakumar.G FPBug#4395561 16-NOV-2005
Code changes made to set global flex field validate flag
13-MAY-2010 G. Muratore Bug 13072622
Conditionalize call to save batch using p_commit value.
================================================================================*/
PROCEDURE insert_batchstep_activity (
p_api_version IN NUMBER ,
p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors,
p_init_msg_list IN VARCHAR2
DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2
DEFAULT fnd_api.g_false,
p_org_code IN VARCHAR2,
p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
p_batchstep_resource_tbl IN gme_create_step_pvt.resources_tab,
p_batch_no IN VARCHAR2 := NULL,
p_batchstep_no IN NUMBER := NULL,
p_ignore_qty_below_cap IN VARCHAR2
DEFAULT fnd_api.g_false,
p_validate_flexfield IN VARCHAR2
DEFAULT fnd_api.g_false,
x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
x_message_count OUT NOCOPY NUMBER,
x_message_list OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_BATCHSTEP_ACTIVITY';
insert_activity_failed EXCEPTION;
SAVEPOINT insert_activity_pub;
gme_debug.log_initialize ('InsertBatchstepActivity');
gme_batchstep_act_pvt.insert_batchstep_activity (p_batchstep_activity_rec => p_batchstep_activity_rec,
p_batchstep_resource_tbl => p_batchstep_resource_tbl,
p_org_code => p_org_code,
p_batch_no => p_batch_no,
p_batchstep_no => p_batchstep_no,
p_ignore_qty_below_cap => p_ignore_qty_below_cap,
p_validate_flexfield => p_validate_flexfield,
x_batchstep_activity_rec => x_batchstep_activity_rec,
x_return_status => x_return_status
);
RAISE insert_activity_failed;
WHEN insert_activity_failed THEN
ROLLBACK TO SAVEPOINT insert_activity_pub;
ROLLBACK TO SAVEPOINT insert_activity_pub;
ROLLBACK TO SAVEPOINT insert_activity_pub;
END insert_batchstep_activity;
update_batchstep_activity
Description
This procedure is used to update an activity for a step
Parameters
p_batchstep_activity (R) activity to be updated
p_org_code (O) organization code
p_batch_no (O) batch number
p_batchstep_no(O) batch step number
p_validate_flexfield (O) Whether to validate the flexfields
x_batchstep_activity updated activity row
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
HISTORY
Sivakumar.G FPBug#4395561 16-NOV-2005
Code changes made to set global flex field validate flag
================================================================================*/
PROCEDURE update_batchstep_activity (
p_api_version IN NUMBER ,
p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors,
p_init_msg_list IN VARCHAR2
DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2
DEFAULT fnd_api.g_false,
p_org_code IN VARCHAR2,
p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
p_batch_no IN VARCHAR2 := NULL,
p_batchstep_no IN NUMBER := NULL,
p_validate_flexfield IN VARCHAR2
DEFAULT fnd_api.g_false,
x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
x_message_count OUT NOCOPY NUMBER,
x_message_list OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_BATCHSTEP_ACTIVITY';
update_activity_failed EXCEPTION;
SAVEPOINT update_activity_pub;
gme_debug.log_initialize ('UpdateBatchstepActivity');
gme_batchstep_act_pvt.update_batchstep_activity (p_batchstep_activity_rec => p_batchstep_activity_rec,
p_org_code => p_org_code,
p_batch_no => p_batch_no,
p_batchstep_no => p_batchstep_no,
p_validate_flexfield => p_validate_flexfield,
x_batchstep_activity_rec => x_batchstep_activity_rec,
x_return_status => x_return_status
);
RAISE update_activity_failed;
WHEN update_activity_failed THEN
ROLLBACK TO SAVEPOINT update_activity_pub;
ROLLBACK TO SAVEPOINT update_activity_pub;
ROLLBACK TO SAVEPOINT update_activity_pub;
END update_batchstep_activity;
delete_batchstep_activity
Description
This procedure is used to delete an activity from a step. Note that either the
activity_id must be provided or the combination of organization_code, batch_no, batchstep_no,
and activity in order to uniquely identify an activity to be deleted.
Parameters
p_batchstep_activity_id (O) activity_id to be deleted
p_org_code (O) organization code
p_batch_no (O) batch number
p_batchstep_no(O) batch step number
p_activity(O) activity
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
================================================================================*/
PROCEDURE delete_batchstep_activity (
p_api_version IN NUMBER := 2.0,
p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors,
p_init_msg_list IN VARCHAR2
DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2
DEFAULT fnd_api.g_false,
p_org_code IN VARCHAR2,
p_batchstep_activity_id IN NUMBER := NULL,
p_batch_no IN VARCHAR2 := NULL,
p_batchstep_no IN NUMBER := NULL,
p_activity IN VARCHAR2 := NULL,
x_message_count OUT NOCOPY NUMBER,
x_message_list OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_BATCHSTEP_ACTIVITY';
delete_activity_failed EXCEPTION;
SAVEPOINT delete_activity_pub;
gme_batchstep_act_pvt.delete_batchstep_activity (p_batchstep_activity_id => p_batchstep_activity_id,
p_org_code => p_org_code,
p_batch_no => p_batch_no,
p_batchstep_no => p_batchstep_no,
p_activity => p_activity,
x_return_status => x_return_status
);
RAISE delete_activity_failed;
WHEN delete_activity_failed THEN
ROLLBACK TO SAVEPOINT delete_activity_pub;
ROLLBACK TO SAVEPOINT delete_activity_pub;
ROLLBACK TO SAVEPOINT delete_activity_pub;
END delete_batchstep_activity;
update_pending_product_lot
Description
This procedure is used to update a pending product lot record for a specified
pending product lot record
Following key sequences can be specified:
pending_product_lot_id OR
batch_id, line_no, line_type, sequence OR
batch_no, org_code, line_no, line_type, sequence (batch_type is assumed to be Batch)
Parameters
p_batch_header_rec (O) batch header record
p_org_code (O) organization code
p_material_detail_rec (O) material detail record
p_pending_product_lots_rec (R) pending product lots record
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
================================================================================*/
PROCEDURE update_pending_product_lot
(p_api_version IN NUMBER
,p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_org_code IN VARCHAR2
,p_material_detail_rec IN gme_material_details%ROWTYPE
,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_PENDING_PRODUCT_LOT';
error_update_pp_lot EXCEPTION;
gme_debug.log_initialize ('UpdatePendingProdLot');
SAVEPOINT update_pending_product_lot;
,'update_pending_prod_lot'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_pending_product_lots_pvt.validate_material_for_update
(p_batch_header_rec => l_batch_header_rec
,p_material_detail_rec => l_material_detail_rec
,x_return_status => x_return_status);
gme_pending_product_lots_pvt.validate_record_for_update
(p_material_detail_rec => l_material_detail_rec
,p_db_pending_product_lots_rec => l_db_pending_product_lots_rec
,p_pending_product_lots_rec => p_pending_product_lots_rec
,x_pending_product_lots_rec => l_pending_product_lots_rec
,x_return_status => x_return_status);
gme_api_main.update_pending_product_lot
(p_validation_level => p_validation_level
,p_init_msg_list => fnd_api.g_false
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => x_return_status
,p_org_id => l_batch_header_rec.organization_id
,p_pending_product_lots_rec => l_pending_product_lots_rec
,x_pending_product_lots_rec => x_pending_product_lots_rec);
|| ' Return status from gme_api_main.update_pending_product_lot is '
|| x_return_status);
RAISE error_update_pp_lot;
WHEN error_update_pp_lot THEN
ROLLBACK TO SAVEPOINT update_pending_product_lot;
ROLLBACK TO SAVEPOINT update_pending_product_lot;
ROLLBACK TO SAVEPOINT update_pending_product_lot;
END update_pending_product_lot;
Delete_pending_product_lot
Description
This procedure is used to delete a pending product lot record for a specified
pending product lot record
Following key sequences can be specified:
pending_product_lot_id OR
batch_id, line_no, line_type, sequence OR
batch_no, org_code, line_no, line_type, sequence (batch_type is assumed to be Batch)
Parameters
p_batch_header_rec (O) batch header record
p_org_code (O) organization code
p_material_detail_rec (O) material detail record
p_pending_product_lots_rec (R) pending product lots record
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
================================================================================*/
PROCEDURE delete_pending_product_lot
(p_api_version IN NUMBER
,p_validation_level IN NUMBER
:= gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_org_code IN VARCHAR2
,p_material_detail_rec IN gme_material_details%ROWTYPE
,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_PENDING_PRODUCT_LOT';
error_delete_pp_lot EXCEPTION;
gme_debug.log_initialize ('DeletePendingProdLot');
SAVEPOINT delete_pending_product_lot;
,'delete_pending_prod_lot'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
gme_pending_product_lots_pvt.validate_material_for_delete
(p_batch_header_rec => l_batch_header_rec
,p_material_detail_rec => l_material_detail_rec
,x_return_status => x_return_status);
gme_pending_product_lots_pvt.validate_record_for_delete
(p_material_detail_rec => l_material_detail_rec
,p_db_pending_product_lots_rec => l_db_pending_product_lots_rec
,p_pending_product_lots_rec => p_pending_product_lots_rec
,x_pending_product_lots_rec => l_pending_product_lots_rec
,x_return_status => x_return_status);
gme_api_main.delete_pending_product_lot
(p_validation_level => p_validation_level
,p_init_msg_list => fnd_api.g_false
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => x_return_status
,p_org_id => l_batch_header_rec.organization_id
,p_pending_product_lots_rec => l_pending_product_lots_rec);
|| ' Return status from gme_api_main.delete_pending_product_lot is '
|| x_return_status);
RAISE error_delete_pp_lot;
WHEN error_delete_pp_lot THEN
ROLLBACK TO SAVEPOINT delete_pending_product_lot;
ROLLBACK TO SAVEPOINT delete_pending_product_lot;
ROLLBACK TO SAVEPOINT delete_pending_product_lot;
END delete_pending_product_lot;
SELECT COUNT(1)
FROM mtl_material_transactions mmt, gme_material_details mtl
WHERE NVL(transfer_lpn_id,0) > 0
AND TRANSACTION_SOURCE_ID = p_batch_id
AND transaction_action_id = 31
AND transaction_type_id IN (44, 1002)
AND mmt.transaction_source_id = mtl.batch_id
AND mmt.inventory_item_id = mtl.inventory_item_id
AND mmt.transaction_id NOT IN (
SELECT transaction_id1
FROM gme_transaction_pairs
WHERE batch_id = p_batch_id
AND pair_type = 1);
,p_delete_invis_mo => 'F'
,p_delete_reservations => 'T'
,p_delete_trans_pairs => 'F'
,p_recursive => 'R'
,x_return_status => x_return_status);
SELECT organization_id
FROM mtl_parameters
WHERE organization_code = p_org_code;
SELECT group_id
FROM gme_batch_groups_b
WHERE organization_id = l_org_id AND group_name = l_group_name;
SELECT lookup_code FROM gem_lookups
WHERE lookup_type = 'GME_BATCH_GROUP_ACTION'
AND lookup_code = l_lookup_code
AND lookup_code NOT IN (1,2);
SELECT batch_id
FROM gme_batch_groups_association
WHERE group_id = l_group_id;
SELECT batch_status
FROM gme_batch_header
WHERE batch_id = l_batch_id
and organization_id = l_org_id;
update_batchstep_qty
Description
This api procedure is used only for updating actual_step_qty.
Charges will also be recalculated.
One of the following key sequences must be specified:
batchstep_id
batch_id, batchstep_no OR
batch_no, org_code, batchstep_no batch_type assumed to be batch
Parameters
p_batch step_rec (O) batch step to identify the step to update.
p_org_code (O) organization code to identify the step is conjunction with batch_no in p_batch_header_rec
p_batch_no (O) batch_no to identify the step
p_add (O) indicates whether to add to the actual step qty for incremental
recording or overwrite. Default is 'N' Meaning overwrite.
x_batch_step_rec Output batch step record after update
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected Error
X - Batch Exception
History:
G. Muratore 15-JUN-2009 Bug 7447757
Introduced new api for update actual_step_qty
PROCEDURE update_batchstep_qty. Charges will also be recalculated.
================================================================================*/
PROCEDURE update_batchstep_qty (
p_api_version IN NUMBER := 1.0
,p_validation_level IN NUMBER := gme_common_pvt.g_max_errors
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2
,p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_add IN VARCHAR2 := 'N'
,p_batch_step_rec IN gme_batch_steps%ROWTYPE
,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'UPDATE_STEP';
update_step_failed EXCEPTION;
SAVEPOINT update_batchstep_qty;
gme_debug.log_initialize ('UpdateBatchstep');
,'update_batchstep'
,g_pkg_name) THEN
gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
/* Retrieve the row to be updated */
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('actual qty passed in is '||p_batch_step_rec.actual_step_qty);
gme_update_step_qty_pvt.recalculate_charges(p_batchstep_rec => l_batch_step_rec
,p_cal_type => 'P'
,x_batchstep_rec => l_batch_step_rec2
,x_return_status => x_return_status );
RAISE update_step_failed;
,p_message_code => 'IEM_NOTHING_TO_UPDATE');
WHEN l_invalid_step_status OR negative_qty_error OR update_step_failed THEN
ROLLBACK TO SAVEPOINT update_batchstep_qty;
ROLLBACK TO SAVEPOINT update_batchstep_qty;
ROLLBACK TO SAVEPOINT update_batchstep_qty;
END update_batchstep_qty;