[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_update_step_qty_pvt';
update_step_qty
Description
This particular procedure call changes the current step qty and propogates it.
Parameters
p_batch_step_rec The batch step row to identify the step.
x_message_count The number of messages in the message stack
x_message_list message stack where the api writes its messages
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE update_step_qty (
p_batch_step_rec IN gme_batch_steps%ROWTYPE
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
,p_routing_scale_factor IN NUMBER DEFAULT NULL
,p_backflush_factor IN NUMBER DEFAULT NULL
,p_dependency_type IN NUMBER DEFAULT NULL
,p_material_step_id IN NUMBER DEFAULT NULL)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_step_qty';
SELECT d.batchstep_id
FROM gme_batch_step_dependencies d
WHERE d.batch_id = v_batch_id
START WITH ( (d.batch_id = v_batch_id)
AND ( (v_batchstep_id IS NULL)
OR (dep_step_id = v_batchstep_id) ) )
CONNECT BY d.batch_id = PRIOR d.batch_id
AND d.dep_step_id = PRIOR d.batchstep_id
GROUP BY d.batchstep_id
--Bug# 5606246 Start
--ORDER BY MAX (LEVEL) ASC;
SELECT p_material_step_id from dual;
SAVEPOINT update_step_qty;
/* If step status is not in pending then actual quantities have to be updated. */
IF x_batch_step_rec.step_status > gme_common_pvt.g_step_pending THEN
x_batch_step_rec.actual_step_qty := l_step_tbl (l_rec).step_qty;
/* be updated */
IF l_batch_step.step_status > gme_common_pvt.g_step_pending THEN
l_batch_step.actual_step_qty :=
l_step_tbl (l_rec).step_qty;
ROLLBACK TO SAVEPOINT update_step_qty;
ROLLBACK TO SAVEPOINT update_step_qty;
ROLLBACK TO SAVEPOINT update_step_qty;
ROLLBACK TO SAVEPOINT update_step_qty;
ROLLBACK TO SAVEPOINT update_step_qty;
ROLLBACK TO SAVEPOINT update_step_qty;
END update_step_qty;
SELECT uom_class, conversion_rate
FROM mtl_uom_conversions
WHERE uom_code = p_batch_step_rec.step_qty_um
AND inventory_item_id = 0;
/* be updated */
IF p_batch_step_rec.step_status > gme_common_pvt.g_step_pending THEN
p_batch_step_rec.actual_mass_qty :=
NVL (p_batch_step_rec.actual_step_qty * l_std_factor, 0);
/* be updated */
IF p_batch_step_rec.step_status > gme_common_pvt.g_step_pending THEN
p_batch_step_rec.actual_volume_qty :=
NVL (p_batch_step_rec.actual_step_qty * l_std_factor, 0);
update_activities (p_batch_hdr_rec => p_batch_hdr_rec
,p_batch_step_rec => p_batch_step_rec
,x_return_status => l_return_status
,p_routing_scale_factor => p_routing_scale_factor
,p_backflush_factor => p_backflush_factor
,p_charge_diff => l_charge_diff
,p_dependency_type => p_dependency_type);
/* Now update the batch step to the database */
IF NOT (gme_batch_steps_dbl.update_row (p_batch_step_rec) ) THEN
RAISE batch_step_upd_err;
SELECT max_step_capacity, max_step_capacity_um, batchstep_no
,plan_step_qty, step_qty_um
FROM gme_batch_steps
WHERE batchstep_id = p_step_id;
SELECT uom_class, conversion_rate
FROM mtl_uom_conversions
WHERE uom_code = v_um_code AND inventory_item_id = 0;
SELECT NVL (MIN (gbsr.capacity_tolerance), 0)
FROM gme_batch_steps gbs, gme_batch_step_resources gbsr
WHERE gbs.batchstep_id = gbsr.batchstep_id
AND gbsr.calculate_charges = 1
AND gbsr.batchstep_id = p_step_id;
SELECT plan_start_date, plan_cmplt_date
FROM gme_batch_step_resources
WHERE batchstep_id = v_batchstep_id AND resources = v_resources;
SELECT batch_id
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM gme_batch_step_resources
WHERE batchstep_id = p_step_id
AND resources = v_resources
AND scale_type = 2);
error_charge_insert EXCEPTION;
RAISE error_charge_insert;
WHEN error_charge_insert THEN
gme_common_pvt.log_message ('GME_INSERT_CHARGE_ERROR'
,'STEP_NO'
,x_step_no);
update_activities
Description
This particular procedure is used to update the activities associated with a batch step
Parameters
p_batch_hdr_rec Batch Header Record
p_batch_step_rec Batch Step Line
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE update_activities (
p_batch_hdr_rec IN gme_batch_header%ROWTYPE
,p_batch_step_rec IN gme_batch_steps%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2
,p_routing_scale_factor IN NUMBER DEFAULT NULL
,p_backflush_factor IN NUMBER DEFAULT NULL
,p_charge_diff IN NUMBER
,p_dependency_type IN NUMBER DEFAULT NULL)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_activities';
SELECT batchstep_activity_id
FROM gme_batch_step_activities
WHERE batch_id = p_batch_step_rec.batch_id
AND batchstep_id = p_batch_step_rec.batchstep_id
ORDER BY batchstep_id;
/* Let us update all the resources attached to the activity */
gme_update_step_qty_pvt.update_resources
(p_batch_hdr_rec => p_batch_hdr_rec
,p_batch_step_rec => p_batch_step_rec
,p_batchstep_activities_rec => l_gme_batchstep_activities
,x_return_status => l_return_status
,p_routing_scale_factor => p_routing_scale_factor
,p_backflush_factor => p_backflush_factor
,p_charge_diff => p_charge_diff
,p_dependency_type => p_dependency_type);
/* Save the updated batch step activities row to the database */
IF NOT (gme_batch_step_activities_dbl.update_row
(l_gme_batchstep_activities) ) THEN
RAISE step_activity_upd_err;
/* SELECT user_profile_option_name
INTO l_user_profile_option_name
FROM fnd_profile_options_vl
WHERE application_id = 553
AND profile_option_name = 'GME_CALC_INT_RSRC_USAGE';*/
fnd_msg_pub.add_exc_msg ('gme_update_step_qty_pvt'
,'UPDATE_ACTIVITIES');
END update_activities;
update_resources
Description
This particular procedure is used to update the resources associated with a activity
Parameters
p_batch_hdr_rec Batch Header Record
p_batch_step_rec Batch Step Line
p_batchstep_activities_rec Batch Step Activity Line
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
History
=============================================================================================*/
PROCEDURE update_resources (
p_batch_hdr_rec IN gme_batch_header%ROWTYPE
,p_batch_step_rec IN gme_batch_steps%ROWTYPE
,p_batchstep_activities_rec IN gme_batch_step_activities%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2
,p_routing_scale_factor IN NUMBER DEFAULT NULL
,p_backflush_factor IN NUMBER DEFAULT NULL
,p_charge_diff IN NUMBER DEFAULT NULL
,p_dependency_type IN NUMBER DEFAULT NULL)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_resources';
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT SUM (resource_usage)
FROM gme_resource_txns
WHERE line_id = v_batchstep_resource_id
AND doc_type = v_doc_type
AND doc_id = v_doc_id
AND completed_ind = 1
AND overrided_protected_ind = 'Y';
SELECT plan_activity_factor, actual_activity_factor
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT plan_charges
FROM gme_batch_steps
WHERE batchstep_id = p_batch_step_rec.batchstep_id;
error_txns_update EXCEPTION;
/* If the step status is pending then we have to update the plan quantities */
ELSIF p_batch_step_rec.step_status = gme_common_pvt.g_step_pending THEN
l_gme_batchstep_resources.plan_rsrc_qty :=
p_batch_step_rec.plan_step_qty
* p_batchstep_activities_rec.plan_activity_factor;
/* Only if the update inventory ind is set to 'Y' on the batch header */
/* then only we will have resource transactions */
IF p_batch_hdr_rec.update_inventory_ind = 'Y' THEN
IF (p_batch_step_rec.step_status >
gme_common_pvt.g_step_pending)
AND (NVL (l_gme_batchstep_resources.actual_rsrc_usage, -1) >= 0) THEN
IF p_batch_hdr_rec.automatic_step_calculation = 1 THEN
OPEN cur_sum_override_resource
(l_gme_batchstep_resources.batchstep_resource_id
,l_doc_type
,p_batch_hdr_rec.batch_id);
/* we have to delete the actual transactions and create pending resource transactions */
ELSIF ( p_batch_step_rec.step_status =
gme_common_pvt.g_step_pending
OR ( p_batch_hdr_rec.automatic_step_calculation = 1
AND p_batch_step_rec.step_status =
gme_common_pvt.g_step_wip) ) THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line (' invoking pending usage');
END IF; /* IF p_batch_header.update_inventory_ind = 'Y' */
/* Save the updated batch step resources row to the database */
IF NOT (gme_batch_step_resources_dbl.update_row
(l_gme_batchstep_resources) ) THEN
RAISE step_resource_upd_err;
WHEN error_txns_update THEN
x_return_status := fnd_api.g_ret_sts_error;
END update_resources;
Build_Insert_Resource_Txn
Description
This particular procedure is used to build a resource transaction row based on the resource
line row and the usage passed in and insert it.
Parameters
p_batch_hdr_rec Batch Header Row
p_batchstep_resources Batch Step Resource Line
p_usage Usage to be created.
p_completed Build a completed or pending transaction.
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
HISTORY
G. Muratore 19-MAR-2010 Bug 8751983
Stamp resource transaction with the trans_date if new p_trans_date parameter passed in.
=============================================================================================*/
PROCEDURE build_insert_resource_txn (
p_batch_hdr_rec IN gme_batch_header%ROWTYPE
,p_batchstep_resource IN gme_batch_step_resources%ROWTYPE
,p_usage IN NUMBER
,p_completed IN NUMBER DEFAULT 1
,p_trans_date IN DATE DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2)
IS
/* Local Variables */
l_api_name CONSTANT VARCHAR2 (30) := 'Build_Insert_Resource_Txn';
SELECT gem5_poc_trans_id_s.NEXTVAL
FROM SYS.DUAL;
l_ins_resource_row.delete_mark := 0;
IF NOT (gme_resource_txns_gtmp_dbl.insert_row (l_ins_resource_row
,l_ins_resource_row) ) THEN
RAISE resource_trans_ins_err;
END build_insert_resource_txn;
SELECT NVL (SUM (resource_usage), 0), COUNT (1)
FROM gme_resource_txns_gtmp
WHERE line_id = v_batchstep_resource_id AND completed_ind = 0;
/* Deletes all resource transactions for the current resource */
gme_delete_batch_step_pvt.delete_resource_transactions
(p_batch_step_resources_rec => p_batch_step_resources_rec
,x_return_status => l_return_status);
build_insert_resource_txn
(p_batch_hdr_rec => l_batch_hdr
,p_batchstep_resource => p_batch_step_resources_rec
,p_usage => l_alloc_usage
,p_completed => 0
,x_return_status => l_return_status);
Step 1 : If their are any pending transactions then we have to either delete them or adjust
them based on the step status.
Step 2 : Check for the total completed usage transactions if it equals to the resource
line actual usage then we need not do any adjustment we can return.
Step 3 : If we are here then their is some adjustment we have to make. If the total usage is
less than the actual resource usage then we perform step 4 else step 5.
Step 4 : If the total usage is less than the actual usage then, find the difference between
actual resource usage and the total usage.
Divide the difference amount with the actual resource count and post one transactions for
the divided amount for each actual resource count.
Step 5 : If the actual usage is less than the total usage then, delete all the completed transactions
which are not override protected and then subtract total override protected usage from the
actual resource usage and then divide the difference amount with the actual resource count
and post one transactions for the divided amount for each actual resource count.
Parameters
p_batch_step_resources_rec Batch Step Resource Line
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
HISTORY:
G. Muratore 19-MAR-2010 Bug 8751983
Fetch resource transactions in reverse trans order just in case this is being called
by negative IB. Also, rework logic so that all the existing resource transactions are
not blindly removed. Reverse out only what is necessary. Also, Pass in a trans date
for any new resource transaction generated by negative IB logic.
=============================================================================================*/
PROCEDURE adjust_actual_usage (
p_batch_step_resources_rec IN gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'adjust_actual_usage';
SELECT NVL (SUM (resource_usage), 0)
FROM gme_resource_txns_gtmp
WHERE line_id = v_batchstep_resource_id
AND completed_ind = 1
AND action_code <> 'DEL';
SELECT COUNT (1)
FROM gme_resource_txns_gtmp
WHERE line_id = v_batchstep_resource_id
AND completed_ind = 1
AND NVL (overrided_protected_ind, 'N') <> 'Y'
AND action_code <> 'DEL';
gme_update_step_qty_pvt.reduce_pending_usage
(p_batch_step_resources_rec => p_batch_step_resources_rec
,x_return_status => l_return_status);
build_insert_resource_txn
(p_batch_hdr_rec => l_batch_hdr
,p_batchstep_resource => p_batch_step_resources_rec
,p_usage => l_actual_usage
,p_completed => 1
,x_return_status => l_return_status);
/* Delete all the existing completed transactions */ -- This is the original commment before 8751983.
FOR i IN 1 .. l_resource_tab.COUNT LOOP
-- Bug 8751983 - Let's not delete/reverse everything unless we have to.
IF (l_resource_tab (i).overrided_protected_ind <> 'Y')
AND l_sum_comp_usage_loop > 0
AND (l_resource_tab (i).completed_ind = 1) THEN
l_resource_txns := l_resource_tab (i);
gme_resource_engine_pvt.delete_resource_trans
(p_tran_rec => l_resource_txns
,x_return_status => l_return_status);
build_insert_resource_txn
(p_batch_hdr_rec => l_batch_hdr
,p_batchstep_resource => p_batch_step_resources_rec
,p_usage => l_actual_usage
,p_completed => 1
,p_trans_date => l_resource_tab(l_hold_trans_index).trans_date
,x_return_status => l_return_status);
SELECT step_status
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id;
SELECT actual_rsrc_usage, plan_start_date
FROM gme_batch_step_resources
WHERE batchstep_resource_id = v_batchstep_resource_id;
SELECT COUNT (1)
FROM gme_resource_txns_gtmp
WHERE line_id = v_batchstep_resource_id AND completed_ind = 0;
gme_resource_engine_pvt.update_resource_trans
(p_tran_rec => l_resource_txns
,x_return_status => l_return_status);
gme_resource_engine_pvt.delete_resource_trans
(p_tran_rec => l_resource_txns
,x_return_status => l_return_status);
p_cal_type 'R' means change is due to Resource Add/Update/Delete
'P' means change is due to operation
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
HISTORY
SivakumarG Bug#5231180
Procedure Created
=============================================================================================*/
PROCEDURE recalculate_charges( p_batchstep_rec IN gme_batch_steps%ROWTYPE
,p_cal_type IN VARCHAR2
,x_batchstep_rec OUT NOCOPY gme_batch_steps%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2 )
IS
l_api_name VARCHAR2(30) := 'RECALCULATE_CHARGES';
SELECT batchstep_id, step_status, plan_step_qty, actual_step_qty,
max_step_capacity, max_step_capacity_um, step_qty_um
FROM gme_batch_steps
WHERE batchstep_id = v_step_id;
SELECT resources
FROM gme_batch_step_resources
WHERE batchstep_id = v_step_id;
error_in_update_step EXCEPTION;
/* if the cal type P that means step update is triggering recalculate charges*/
IF NVL(l_step_qty,0) <> NVL(l_in_step_qty,0) THEN
l_calc_step_qty := TRUE;
gme_insert_step_pvt.calc_max_capacity (
p_recipe_rout_resc => l_gmd_resources
,p_step_qty_uom => l_step_rec.step_qty_um
,p_capacity_uom => l_uom
,p_max_capacity => x_batchstep_rec.max_step_capacity
,x_resource => l_resource
,x_return_status => l_return_status);
/*DELETE FROM gme_batch_step_charges
WHERE batchstep_id = l_step_rec.batchstep_id; */
gme_update_step_qty_pvt.calculate_mass_vol_qty(
p_batch_step_rec => x_batchstep_rec
);
gme_update_step_qty_pvt.calc_charge (
p_step_id => l_step_rec.batchstep_id
,p_resources => l_resource
,p_mass_qty => l_mass_qty
,p_vol_qty => l_vol_qty
,p_step_qty => l_in_step_qty
,p_max_capacity => x_batchstep_rec.max_step_capacity
,x_charge => l_charge
,x_return_status => l_return_status);
/* update the batch step record */
IF NOT gme_batch_steps_dbl.update_row(p_batch_step => x_batchstep_rec) THEN
RAISE error_in_update_step;