[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.
/*************************************************************************************************/
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 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 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');
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 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, p_exception_rec.att
,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;