[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
MO allocations are deleted,Only Reservations deleted,Only MO allocations deleted respectively
Pawan Kumar -- bug 5138929 2nd may 2006
Added procedure check_close_period
Susruth D. Bug#5311713 Rounded the wip_plan_qty and plan_qty to 5 decimal places.
Namit S -- bug 5176319 20 Jun 2006
Added procedure reset_txn_hdr_tbl
Sunitha ch. Bug#5336007 checked the parameter p_validate_plan_dates_ind.If it is 1 then
validate planned start date only.If it is 0 then validate both
SivakumarG Bug#Bug#5394232 26-Jun-2006
Added new procedure fetch_trans_date that can give us the default transaction date by
considering release types batch actions and all
Sunitha ch. Bug 5404329 Changed the condition fron AND to OR
Sunitha ch. bug 5581523 removed the default value of the l_qty variable
SivakumarG Bug#5618961 07-Oct-2006
Modified the get_process_loss procedure to consider the qty correctly for getting correct
theoretical process loss from routing class table
Swapna K Bug#7157383 16-JUL-08
Modified validate_material_detail procedure to comment the validation for
the phantom batch
Swapna K Bug#7346653 16-JUL-08
Commented the procedure reservation_fully_specified in the get_open_qty
procedure.
Swapna K Bug#7385805 13-OCT-2008
Commented the code to divide the leadtime by 24 as the leadtime from Item
master is already defined in days.
G. Muratore 01-DEC-2008 Bug 7562848
Added parameter p_check_phantom to allow calling routine to bypass phantom check.
Many api's call validate_batch, but not all the api's are invalid for phantom batches.
This can be used in the future by other api's when it is determined that they are valid
for phantoms. Procedure: Validate_batch
Aditi Sharma 21-May-2009 Bug 7496141
Added two condition in the query(i.e TRANSACTION_SOURCE_ID NOT IN (l_mtl_dtl_rec.batch_id) AND
TRX_SOURCE_LINE_ID NOT IN (l_mtl_dtl_rec.material_detail_id)) which fetches the total qty
allocated to find out org level shortages.
A.Mishra 01-JUL-2009 Bug -- Bug 8582412
Moved cursor outside of if - we need unusable for both calculations of ATR and ATT.
Basically we are now disregarding qty's for lots that have a status which is not useable for production.
Prior to fix it was doing it only for ATT but now it is doing for ATR also.
G. Muratore 11-MAR-2010 Bug 9442109
Pass in material_requirement_date as p_lot_expiration_date parameter when making the call to query
quantities. The INV function will use this value to exclude lots which aill be expired by this date.
Procedure: get_batch_shortages
G. Muratore 13-MAY-2010 Bug 9666304
Insert att variable value into exception table using NVL.
Procedure: insert_exceptions
Kishore 22-Jul-2010 Bug No.9924437
Added code in get_open_qty when p_called_by is 'Z'. As WMS is not considering allocations
while doing reservations, added code to exclude allocations from planned qty before sending to WMS.
G. Muratore 03-AUG-2010 Bug 9938452
Renamed procedure set_move_to_temp_off to set_move_to_temp_toggle.
Also, added parameter to allow toggling of the global variable g_move_to_temp.
PROCEDURE: set_move_to_temp_toggle
G. Muratore 22-Jun-2011 Bug 12613813
Pass in true for new parameter when calling get_reserved_qty for picking only.
This will direct get_open_qty to consider locator as well when summing the reservations.
PROCEDURE: get_open_qty
G. Muratore 14-NOV-2011 Bug 13356510
Commented out validation code that was incorrectly checking for phantom batch.
PROCEDURE: Validate_batch_step
G. Muratore 20-MAR-2012 Bug 13811289
Make sure validity rule is valid for the current organization.
PROCEDURE: Validate_validity_rule
G. Muratore 28-MAR-2012 Bug 13611486
Backout calculation introduced in 8582412. There is missing functionality when calling
INV to get att/atr. It is not considering the transaction type in tandem with material status.
This fix will be included in patch 13795581 and bug 13611486 will be transferred to INV to
see if they can correct the functionality.
PROCEDURE: get_batch_shortages
G. Muratore 03-APR-2012 Bug 13881792
During IB, return the user entered transaction date when exceptions appear.
Procedure: fetch_trans_date and reset_txn_header_id.
G. Muratore 16-APR-2012 Bug 13946067
Introduce new table and joins to make use of existing index.
Procedure: resource_qty_below_capacity.
G. Muratore 19-APR-2012 Bug 13721430
Calculate leadtime properly by considering lot size.
Procedure: calc_date_from_prod_rule.
G. Muratore 01-OCT-2012 Bug 14488987
Backout calculation introduced in 7462542 for release 12.1 and above.
INV code already considers Move orders in the newer versions of the code.
PROCEDURE: get_batch_shortages
A. Mundhe 15-NOV-2012 Bug 15847423
Modified function setup to return true only if the org_id passed is same as the g_organization_id.
***********************************************************************************************/
FUNCTION get_txn_header_id
RETURN NUMBER
IS
BEGIN
RETURN gme_common_pvt.g_transaction_header_id;
gme_common_pvt.g_mat_txn_hdr_tbl.DELETE;
SELECT *
FROM gme_parameters
WHERE organization_id = v_org_id;
SELECT organization_code, calendar_code, negative_inv_receipt_code
,stock_locator_control_code, organization_id
FROM mtl_parameters
WHERE (organization_id = v_org_id)
OR (v_org_id IS NULL AND organization_code = v_org_code);
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM fnd_user
WHERE user_id = v_user_id);
gme_common_pvt.g_delete_material_ind :=
l_cur_get_org_params.delete_material_ind;
SELECT NVL (i.process_supply_subinventory
,g.supply_subinventory)
,NVL (i.process_supply_locator_id
,g.supply_locator_id)
,i.restrict_subinventories_code, NVL(i.location_control_code,1)
,i.restrict_locators_code
FROM mtl_system_items_b i, gme_parameters g
WHERE i.organization_id = v_org_id
AND i.inventory_item_id = v_inventory_item_id
AND g.organization_id = v_org_id;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE organization_id = v_org_id
AND secondary_inventory_name = v_subinventory;
SELECT NVL (i.process_yield_subinventory
,g.yield_subinventory)
,NVL (i.process_yield_locator_id
,g.yield_locator_id)
,i.restrict_subinventories_code, NVL(i.location_control_code,1)
,i.restrict_locators_code
FROM mtl_system_items_b i, gme_parameters g
WHERE i.organization_id = v_org_id
AND i.inventory_item_id = v_inventory_item_id
AND g.organization_id = v_org_id;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE organization_id = v_org_id
AND secondary_inventory_name = v_subinventory;
SELECT b.recipe_validity_rule_id, a.recipe_id, b.organization_id
,b.routing_id, b.formula_id
FROM gme_batch_header b, gmd_recipe_validity_rules a
WHERE b.batch_id = v_batch_id
AND b.recipe_validity_rule_id = a.recipe_validity_rule_id;
SELECT r.recipe_id, r.routing_id, r.formula_id
FROM gmd_recipes_b r, gmd_recipe_validity_rules v
WHERE v.recipe_validity_rule_id = v_validity_rule_id
AND v.recipe_id = r.recipe_id;
/*Bug#5618961 cursor modified to select routing class uom instead of
selecting routing uom*/
CURSOR cur_get_rtclass (v_routing_id NUMBER)
IS
SELECT a.routing_class,routing_uom, b.routing_class_uom, --item_um
process_loss, routing_qty
FROM fm_rout_hdr a, gmd_routing_class_b b
WHERE a.routing_id = v_routing_id
AND a.routing_class = b.routing_class (+);
SELECT process_loss
FROM gmd_recipe_process_loss
WHERE recipe_id = v_recipe_id AND organization_id = v_org_id;
SELECT planned_process_loss
FROM gmd_recipes
WHERE recipe_id = v_recipe_id;
/*Bug#5618961 cursor modified to select routing qty and uom */
CURSOR routing_process_loss_cursor (v_routing_id NUMBER)
IS
SELECT process_loss, routing_qty, routing_uom
FROM fm_rout_hdr
WHERE routing_id = v_routing_id;
SELECT process_loss
FROM gmd_process_loss
WHERE v_qty <= NVL (max_quantity, v_qty)
AND routing_class = v_routing_class
ORDER BY max_quantity;
SELECT planned_process_loss, std_qty, detail_uom, inventory_item_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = NVL (v_recipe_validity_rule_id, -1);
SELECT batch_doc_numbering, batch_no_last_assigned + 1
INTO l_assignment_type, l_document_no
FROM gme_parameters
WHERE organization_id = x_batch_header_rec.organization_id
FOR UPDATE OF batch_no_last_assigned NOWAIT;
SELECT fpo_doc_numbering, fpo_no_last_assigned + 1
INTO l_assignment_type, l_document_no
FROM gme_parameters
WHERE organization_id = x_batch_header_rec.organization_id
FOR UPDATE OF batch_no_last_assigned NOWAIT;
UPDATE gme_parameters
SET batch_no_last_assigned = batch_no_last_assigned + 1
WHERE organization_id = p_batch_header_rec.organization_id;
UPDATE gme_parameters
SET fpo_no_last_assigned = fpo_no_last_assigned + 1
WHERE organization_id = p_batch_header_rec.organization_id;
SELECT std_lot_size, fixed_lead_time, variable_lead_time
FROM mtl_system_items_b
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
SELECT batchstep_id
FROM gme_batch_step_items
WHERE material_detail_id = v_material_detail_id;
SELECT plan_start_date, plan_cmplt_date
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id;
SELECT plan_start_date, plan_cmplt_date
FROM gme_batch_header
WHERE batch_id = v_batch_id;
SELECT b.uom_class, a.conversion_rate
FROM mtl_uom_conversions a, mtl_units_of_measure b
WHERE a.uom_code = b.uom_code
AND a.inventory_item_id = 0
AND b.uom_code = v_uom_code;
SELECT plan_activity_factor, actual_activity_factor
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batch_id = p_batch_id;
SELECT batchstep_resource_id
FROM gme_batch_step_resources r, gme_batch_steps s
WHERE r.batch_id = p_batch_id
AND s.batch_id = p_batch_id
AND s.batchstep_id = r.batchstep_id;
SELECT negative_inv_receipt_code, stock_locator_control_code
FROM mtl_parameters
WHERE organization_id = v_org_id;
SELECT NVL (locator_type, 1)
FROM mtl_secondary_inventories
WHERE organization_id = v_org_id
AND secondary_inventory_name = v_subinventory;
SELECT NVL(location_control_code,1), restrict_locators_code
FROM mtl_system_items_b
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
SELECT restrict_subinventories_code
FROM mtl_system_items_b
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT *
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_validity_rule_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
IF l_validity_rec.delete_mark = 1 THEN
gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
SELECT d.*
FROM gme_material_details d, mtl_system_items_b i
WHERE d.batch_id IN (SELECT DISTINCT batch_id
FROM gme_material_details
START WITH batch_id = p_batch_id
CONNECT BY batch_id = PRIOR phantom_id)
AND d.line_type = -1
AND d.phantom_type = 0
AND d.actual_qty < NVL (d.wip_plan_qty, d.plan_qty)
AND (p_invoke_mode = 'O' OR (p_invoke_mode = 'S' AND d.subinventory IS NOT NULL))
AND i.organization_id = d.organization_id
AND i.inventory_item_id = d.inventory_item_id
AND i.stock_enabled_flag = 'Y'
AND i.mtl_transactions_enabled_flag = 'Y'
ORDER BY d.inventory_item_id, d.batch_id, d.revision, d.line_no;
SELECT concatenated_segments, NVL(location_control_code,1) location_control_code,
restrict_locators_code, primary_uom_code,
reservable_type
FROM mtl_system_items_kfv
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
SELECT NVL (SUM (primary_transaction_quantity), 0) onhand
FROM mtl_onhand_quantities_detail
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id
AND (v_revision IS NULL OR revision = v_revision)
AND (p_invoke_mode = 'O' OR (subinventory_code = v_sub_code) )
AND (inv_material_status_grp.is_status_applicable
(NULL
,NULL
,gme_common_pvt.g_ing_issue
,NULL
,NULL
,v_org_id
,inventory_item_id
,subinventory_code
,locator_id
,lot_number
,NULL
,'A') = 'N');
SELECT 1
FROM fnd_product_installations
WHERE application_id = 553 -- GME product
AND patch_level like 'R12.GME.A.%'; -- This indicates that installed GME product level is 12.0
DELETE FROM gme_exceptions_gtmp;
SELECT SUM(l.primary_quantity) into l_allocated_qty
FROM mtl_material_transactions_temp t,
mtl_txn_request_lines l,
mtl_txn_request_headers h
WHERE t.move_order_line_id = l.line_id
AND t.move_order_header_id = h.header_id
AND t.organization_id = l_mtl_dtl_rec.organization_id
AND t.inventory_item_id = l_mtl_dtl_rec.inventory_item_id
AND t.reservation_id not in (select reservation_id from mtl_reservations
where demand_source_header_id = l_mtl_dtl_rec.batch_id and
demand_source_line_id = l_mtl_dtl_rec.material_detail_id and
demand_Source_type_id = gme_common_pvt.g_txn_source_type)
AND TRANSACTION_SOURCE_ID NOT IN (l_mtl_dtl_rec.batch_id) --bug 7496141
AND TRX_SOURCE_LINE_ID NOT IN (l_mtl_dtl_rec.material_detail_id) --bug 7496141
AND h.move_order_type = gme_common_pvt.g_txn_source_type
AND l.line_status NOT IN (5, 6)
AND h.header_id = l.header_id
AND h.move_order_type NOT IN
(gme_common_pvt.g_invis_move_order_type
,inv_globals.g_move_order_put_away);
IF NOT (insert_exceptions (l_exception_rec) ) THEN
RAISE exception_ins_err;
SELECT NVL(location_control_code,1), restrict_locators_code
FROM mtl_system_items_b
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
FUNCTION insert_exceptions (p_exception_rec IN gme_exceptions_gtmp%ROWTYPE)
RETURN BOOLEAN
IS
l_api_name CONSTANT VARCHAR2 (30) := 'insert_exceptions';
INSERT INTO gme_exceptions_gtmp
(organization_id
,pending_move_order_ind
,pending_reservations_ind
,onhand_qty, att
,atr, material_detail_id
,transacted_qty
,exception_qty, batch_id)
VALUES (p_exception_rec.organization_id
,p_exception_rec.pending_move_order_ind
,p_exception_rec.pending_reservations_ind
,p_exception_rec.onhand_qty, NVL(p_exception_rec.att, 0)
,p_exception_rec.atr, p_exception_rec.material_detail_id
,p_exception_rec.transacted_qty
,p_exception_rec.exception_qty, p_exception_rec.batch_id);
END insert_exceptions;
IF NOT (insert_exceptions (p_exception_tbl (i) ) ) THEN
RETURN FALSE;
SELECT COUNT (1)
INTO l_assoc_count
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gme_batch_step_items
WHERE material_detail_id = p_material_detail_id);
SELECT a.batchstep_id, s.step_status
FROM gme_batch_step_items a, gme_batch_steps s
WHERE a.material_detail_id = v_material_detail_id
AND a.batchstep_id = s.batchstep_id
AND a.batch_id = s.batch_id;
SELECT organization_id
FROM mtl_parameters
WHERE organization_code = p_org_code;
IF NOT (gme_batch_history_dbl.insert_row (l_ins_history, l_ins_history) ) THEN
RETURN FALSE;
SELECT organization_id
FROM mtl_parameters
WHERE organization_code = p_org_code;
SELECT batch_id
FROM gme_batch_header
WHERE organization_id = l_org_id AND batch_no = l_batch_no;
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = l_batch_id AND batchstep_no = l_batchstep_no;
SELECT gbsr.batchstep_resource_id
FROM gme_batch_step_resources gbsr
,gme_batch_step_activities gbsa
WHERE gbsr.organization_id = l_organization_id
AND gbsr.batch_id = l_batch_id
AND gbsr.batchstep_id = l_batchstep_id
AND gbsa.batch_id = l_batch_id
AND gbsa.batchstep_id = l_batchstep_id
AND gbsa.activity = l_activity
AND gbsr.batchstep_activity_id = gbsa.batchstep_activity_id
AND gbsr.resources = l_resource;
SELECT *
FROM gme_batch_step_resources
WHERE batchstep_resource_id = l_batchstep_resource_id;
SELECT *
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_validity_rule_id;
SELECT status_type
FROM gmd_status gs, gmd_recipe_validity_rules grvr
WHERE grvr.recipe_validity_rule_id = v_validity_rule_id
AND status_code = grvr.validity_rule_status;
IF l_validity_rec.delete_mark = 1 THEN
gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
/*Navin: Added new procedure to update the material required date*/
PROCEDURE material_date_change (
p_material_detail_id IN NUMBER
,p_material_date IN DATE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'MATERIAL_DATE_CHANGE';
delete_allocations_failed EXCEPTION;
/* Bug#5590100 selecting only required columns and added join condition to match batch_id to avoid
FTS and for better performance */
CURSOR cur_get_mo_lines
IS
SELECT mtrl.line_id, mtrl.inventory_item_id
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, gme_material_details d
WHERE d.material_detail_id = p_material_detail_id
AND mtrl.organization_id = d.organization_id
AND mtrh.organization_id = d.organization_id
AND mtrl.inventory_item_id = d.inventory_item_id
AND mtrl.txn_source_id = d.batch_id
AND mtrl.header_id = mtrh.header_id
AND mtrl.txn_source_line_id = p_material_detail_id
AND mtrh.move_order_type = gme_common_pvt.g_move_order_type;
SELECT mtlt.*
FROM mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = p_line_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id;
/*SELECT mtlt.*
FROM mtl_txn_request_lines mtrl
,mtl_transaction_lots_temp mtlt
,mtl_material_transactions_temp mmtt
WHERE mtrl.line_id = p_line_id
AND mtrl.txn_source_line_id = p_material_detail_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id; */
SELECT lots.expiration_date
FROM mtl_lot_numbers lots
WHERE lots.inventory_item_id = p_item_id
AND lots.lot_number = p_lot_number;
l_reservations_deleted NUMBER := 0;
l_mo_deleted NUMBER := 0;
l_mo_alloc_deleted NUMBER := 0;
( 'Update existing Material Required Date : '
|| TO_CHAR (l_material_detail_rec.material_requirement_date
,'MM/DD/YYYY HH24:MI:SS')
|| ' in Gme_material_details with new Material Required Date : '
|| TO_CHAR (p_material_date, 'MM/DD/YYYY HH24:MI:SS') );
IF NOT gme_material_details_dbl.update_row
(p_material_detail => l_material_detail_rec) THEN
RAISE fnd_api.g_exc_error;
gme_debug.put_line ('Came back from update of gme_material_details');
* 1. batch_header_rec.update_inventory_ind is not set.
* 2. if line_type is not Ingredient.
*/
IF l_batch_header_rec.update_inventory_ind <> 'Y'
OR l_material_detail_rec.line_type <>
gme_common_pvt.g_line_type_ing /* -1 */ THEN
IF (NVL (g_debug, 0) IN
(gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
gme_debug.put_line
( 'Returning no further processing of reservations and Move Orders are required. '
|| 'Because update_inventory_ind : '
|| l_batch_header_rec.update_inventory_ind
|| ' line_type : '
|| l_material_detail_rec.line_type);
l_reservations_deleted := 1;
/* Delete the reservation by calling
* The parameters that will be assigned to identity the reservations
* to be deleted are the same as used for querying the reservations*/
IF (NVL (g_debug, 0) IN
(gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
gme_debug.put_line ('Calling Delete Reservation.');
gme_reservations_pvt.delete_reservation
(p_reservation_id => l_rsv_array (j).reservation_id
,x_return_status => l_return_status);
( 'Came back from Delete Reservation with status '
|| l_return_status);
/* For remaining reserved lots, update the material required
date with the l_material_date. Assign the new required date to
the corresponding parameter of p_to_rsv_rec */
IF (NVL (g_debug, 0) IN
(gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
gme_debug.put_line
( 'Calling Update Reservation for reservation_id: '
|| l_rsv_array (j).reservation_id
|| ' requirement_date: '
|| TO_CHAR (l_material_date, 'MM/DD/YYYY HH24:MI:SS') );
gme_reservations_pvt.update_reservation
(p_reservation_id => l_rsv_array (j).reservation_id
,p_revision => l_rsv_array (j).revision
,p_subinventory => l_rsv_array (j).subinventory_code
,p_locator_id => l_rsv_array (j).locator_id
,p_lot_number => l_rsv_array (j).lot_number
,p_new_qty => l_rsv_array (j).reservation_quantity
,p_new_uom => l_rsv_array (j).reservation_uom_code
,p_new_date => l_material_date
,x_return_status => l_return_status);
( 'Came back from Update Reservation with status '
|| l_return_status);
gme_move_orders_pvt.update_move_order_lines
(p_batch_id => l_material_detail_rec.batch_id
,p_material_detail_id => l_material_detail_rec.material_detail_id
,p_new_qty => NULL
,p_new_date => l_material_date
,p_invis_move_line_id => NULL
,x_return_status => l_return_status);
l_mo_deleted := 1;
( 'Delete mo alloacations for mo_line_id: '
|| l_mo_line_allocations_tbl (j).transaction_temp_id);
inv_mo_line_detail_util.delete_allocations
(x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,p_mo_line_id => l_mo_lines_tbl (k).line_id
,p_transaction_temp_id => l_mo_line_allocations_tbl (j).transaction_temp_id);
( 'Came back from Delete Allocations with status '
|| l_return_status);
RAISE delete_allocations_failed;
IF l_reservations_deleted = 1 AND l_mo_deleted = 1 THEN
RAISE reserv_mo_err;
ELSIF l_reservations_deleted = 1 AND l_mo_deleted = 0 THEN
RAISE reserv_err;
ELSIF l_reservations_deleted = 0 AND l_mo_deleted = 1 THEN
RAISE mo_err;
/* GME_EXPIRED_RESERV_MO_DELETED: Due to requirement date change,
some reserved lots and move orders allocations expired and are hence deleted.
*/
--correct message will be set in the calling proc.
--FPBug#4585491 changed status to B from W3
x_return_status := 'B';
WHEN reservation_api_failed OR process_move_order_failed OR delete_allocations_failed THEN
x_return_status := l_return_status;
SELECT release_type
INTO l_release_type
FROM gme_material_details
WHERE material_detail_id = p_line_id;
SELECT COUNT (1)
INTO l_dep_count
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gme_batch_step_items
WHERE material_detail_id = p_line_id);
SELECT batchstep_id
FROM gme_batch_step_items
WHERE material_detail_id = v_material_detail_id;
SELECT actual_start_date, actual_cmplt_date
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id;
* inserting
* -- the step.
* --
* -- SYNOPSIS:
* -- bug 6408612
* -- Swapna K created for bug 6408612 to check operation effectivity dates */
--===================================================================== */
FUNCTION check_oprn_effectivity_dates (
p_oprn_id IN NUMBER
,p_start_date IN DATE
,p_cmplt_date IN DATE
)
RETURN BOOLEAN IS
/* Cusror definitions */
Cursor get_oprn_effectivity (v_oprn_id NUMBER) IS
SELECT *
FROM gmd_operations
WHERE oprn_id = v_oprn_id;