The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select *
Into l_res_rec
From gml_batch_so_reservations
Where so_line_id = p_so_line_rec.so_line_id
and batch_line_id = p_batch_line_rec.batch_line_id;
Select *
Into l_res_rec
From gml_batch_so_reservations
Where so_line_id = p_so_line_rec.so_line_id;
Select *
Into l_res_rec
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_rec.batch_line_id;
Select *
Into l_res_rec
From gml_batch_so_reservations
Where batch_res_id = p_gme_om_reservation_rec.batch_res_id;
PROCEDURE insert_reservation
(
P_Gme_om_reservation_rec IN GML_BATCH_OM_UTIL.gme_om_reservation_rec
, X_return_status OUT NOCOPY VARCHAR2
, X_msg_cont OUT NOCOPY NUMBER
, X_msg_data OUT NOCOPY VARCHAR2
) IS
l_batch_res_id NUMBER;
GMI_RESERVATION_UTIL.Println(' In insert reservations');
/* insert gme_om_reservation_rec into gml_batch_so_reservations table */
select gml_so_reservation_s.nextval
into l_batch_res_id
from dual;
Insert Into gml_batch_so_reservations
(
batch_res_id
, batch_id
, batch_line_id
, so_line_id
, order_id
, rule_id
, delivery_detail_id
, mo_line_id
, item_id
, reserved_qty
, reserved_qty2
, qty_uom
, qty2_uom
, whse_code
, organization_id
, allocated_ind
, batch_type
, delete_mark
, created_by
, creation_date
, last_updated_by
, last_update_date
)
Values
(
l_batch_res_id
, p_gme_om_reservation_rec.batch_id
, p_gme_om_reservation_rec.batch_line_id
, p_gme_om_reservation_rec.so_line_id
, p_gme_om_reservation_rec.order_id
, p_gme_om_reservation_rec.rule_id
, p_gme_om_reservation_rec.delivery_detail_id
, p_gme_om_reservation_rec.mo_line_id
, p_gme_om_reservation_rec.item_id
, p_gme_om_reservation_rec.reserved_qty
, p_gme_om_reservation_rec.reserved_qty2
, p_gme_om_reservation_rec.uom1
, p_gme_om_reservation_rec.uom2
, p_gme_om_reservation_rec.whse_code
, p_gme_om_reservation_rec.organization_id
, 0
, p_gme_om_reservation_rec.batch_type
, 0
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, sysdate
);
GMI_RESERVATION_UTIL.Println(' In insert reservations, new batch_res_id '||l_batch_res_id);
END insert_reservation;
PROCEDURE update_reservation
(
P_Gme_om_reservation_rec IN GML_BATCH_OM_UTIL.gme_om_reservation_rec
, X_return_status OUT NOCOPY VARCHAR2
, X_msg_cont OUT NOCOPY NUMBER
, X_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
update gml_batch_so_reservations
Set
batch_id = p_gme_om_reservation_rec.batch_id
, batch_line_id = p_gme_om_reservation_rec.batch_line_id
, so_line_id = p_gme_om_reservation_rec.so_line_id
, order_id = p_gme_om_reservation_rec.order_id
, delivery_detail_id = p_gme_om_reservation_rec.delivery_detail_id
, mo_line_id = p_gme_om_reservation_rec.mo_line_id
, reserved_qty = p_gme_om_reservation_rec.reserved_qty
, reserved_qty2 = p_gme_om_reservation_rec.reserved_qty2
, qty_uom = p_gme_om_reservation_rec.uom1
, qty2_uom = p_gme_om_reservation_rec.uom2
, whse_code = p_gme_om_reservation_rec.whse_code
, organization_id = p_gme_om_reservation_rec.organization_id
, batch_type = p_gme_om_reservation_rec.batch_type
, delete_mark = p_gme_om_reservation_rec.delete_mark
, last_updated_by = fnd_global.user_id
, last_update_date = sysdate
Where batch_res_id = p_gme_om_reservation_rec.batch_res_id;
END update_reservation;
PROCEDURE delete_reservation
(
P_Batch_res_id IN NUMBER default null
, P_Batch_line_id IN NUMBER default null
, P_Batch_id IN NUMBER default null
, X_return_status OUT NOCOPY VARCHAR2
, X_msg_cont OUT NOCOPY NUMBER
, X_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
update gml_batch_so_reservations
set delete_mark = 1
Where batch_res_id = p_batch_res_id;
update gml_batch_so_reservations
set delete_mark = 1
Where batch_line_id = p_batch_line_id;
update gml_batch_so_reservations
set delete_mark = 1
Where batch_id = p_batch_id;
END delete_reservation;
Select *
Into l_history_row
from gml_batch_so_alloc_history
Where alloc_rec_id = p_alloc_history_rec.alloc_rec_id;
PROCEDURE insert_alloc_history
(
P_alloc_history_rec IN GML_BATCH_OM_UTIL.alloc_history_rec
, X_return_status OUT NOCOPY VARCHAR2
, X_msg_cont OUT NOCOPY NUMBER
, X_msg_data OUT NOCOPY VARCHAR2
) IS
l_alloc_rec_id NUMBER;
/* insert gme_om_reservation_rec into gml_batch_so_reservations table */
GMI_RESERVATION_UTIL.println('inserting alloc history');
select gml_so_alloc_history_s.nextval
into l_alloc_rec_id
from dual;
Insert Into gml_batch_so_alloc_history
( Alloc_rec_id
, Batch_res_id
, Batch_id
, Trans_id
, line_id
, Lot_id
, Location
, Whse_code
, Rule_id
, Failure_reason
, batch_trans_id
, batch_line_id
, delete_mark
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_DATE
, LAST_UPDATED_BY
)
Values
(
l_alloc_rec_id
, p_alloc_history_rec.batch_res_id
, p_alloc_history_rec.batch_id
, p_alloc_history_rec.trans_id
, p_alloc_history_rec.so_line_id
, p_alloc_history_rec.lot_id
, p_alloc_history_rec.location
, p_alloc_history_rec.whse_code
, p_alloc_history_rec.rule_id
, p_alloc_history_rec.failure_reason
, p_alloc_history_rec.batch_trans_id
, p_alloc_history_rec.batch_line_id
, 0
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
);
END insert_alloc_history;
Select count(*)
Into l_exist
From gml_batch_so_reservations
Where batch_id = p_batch_id
and delete_mark = 0
and (reserved_qty > 0 or allocated_ind = 1)
;
Select count(*)
Into l_exist
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_id
and delete_mark = 0
and (reserved_qty > 0 or allocated_ind = 1)
;
Select count(*)
Into l_exist
From gml_batch_so_reservations
Where so_line_id = p_so_line_id
and delete_mark = 0
and (reserved_qty > 0 or allocated_ind = 1)
;
Select count(*)
Into l_exist
From gml_batch_so_reservations
Where delivery_detail_id = p_delivery_detail_id
and delete_mark = 0
and (reserved_qty > 0 or allocated_ind = 1)
;
Select decode(site_use_id, null, 0, site_use_id) site_use_id
, decode(customer_id, null, 0, customer_id) customer_id
, decode(item_id, null, 0, item_id) item_id
, decode(allocation_class, null, ' ', allocation_class) allocation_class
, rule_assign_id
, rule_id
From gml_batch_so_rule_assignments
Where whse_code = p_gme_om_config_assign.whse_code
and (item_id = p_gme_om_config_assign.item_id
or item_id is null )
and (allocation_class = p_gme_om_config_assign.allocation_class
or allocation_class is null)
and (customer_id = p_gme_om_config_assign.customer_id
or customer_id is null)
and (site_use_id = p_gme_om_config_assign.site_use_id
or site_use_id is null)
and delete_mark = 0
Order by
1 desc
, 2 desc
, 3 desc
, 4 desc
;
/*Select rule_id
, rule_assign_id
Into l_rule_id
, l_rule_assign_id
, nvl(item_id, 0)
, nvl(allocation_class, '0')
, nvl(customer_id, 0)
, nvl(site_use_id, 0)
From gml_batch_so_rule_assignments
Where whse_code = p_gme_om_config_assign.whse_code
and (item_id = nvl(p_gme_om_config_assign.item_id,0)
or item_id is null )
and (allocation_class = nvl(p_gme_om_config_assign.allocation_class,'0')
or allocation_class is null)
and (customer_id = nvl(p_gme_om_config_assign.customer_id,0)
or customer_id is null)
and (site_use_id = nvl(p_gme_om_config_assign.site_use_id,0)
or site_use_id is null)
and delete_mark = 0
Order by
site_use_id desc
, customer_id desc
, item_id desc
, allocation_class desc
;*/
/*Select item_id
, allocation_class
, customer_id
, site_use_id
Into l_rule_assign_rec.item_id
, l_rule_assign_rec.allocation_class
, l_rule_assign_rec.customer_id
, l_rule_assign_rec.site_use_id
From gml_batch_so_rule_assignments
Where rule_assign_id = l_rule_assign_id;
l_where_clause := 'delete_mark = 0 and whse_code = ';
'SELECT count(*) FROM gml_batch_so_rule_assignments WHERE '
|| l_where_clause ;
Select distinct so_line_id
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_id;
Select sold_to_org_id
, ship_to_org_id
, inventory_item_id
, ship_from_org_id
From oe_order_lines_all
Where line_id = p_so_line_id;
Select ic.alloc_class
, ic.item_id
From ic_item_mst ic
, mtl_system_items mtl
Where ic.item_no = mtl.segment1
and mtl.inventory_item_id = p_inv_item_id
and mtl.organization_id = p_org_id;
Select whse_code
From ic_whse_mst
Where mtl_organization_id = p_org_id;
l_cust_site.delete;
Select *
Into l_rule_rec
From gml_batch_so_rules
Where rule_id = l_rule_id;