[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(1)
INTO l_lotcount
FROM MTL_LOT_NUMBERS
WHERE INVENTORY_ITEM_ID = l_transaction_tbl(i).inventory_item_id
AND ORGANIZATION_ID = l_transaction_tbl(i).organization_id
AND LOT_NUMBER = l_transaction_tbl(i).lot_number;
INV_LOT_API_PUB.InsertLot(
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'F',
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_inventory_item_id => l_transaction_tbl(i).inventory_item_id,
p_organization_id => l_transaction_tbl(i).organization_id,
p_lot_number => l_transaction_tbl(i).lot_number,
p_expiration_date => l_transaction_tbl(i).lot_expiration_date,
p_transaction_temp_id => l_transaction_tbl(i).transaction_temp_id,
p_transaction_Action_id => WIP_CONSTANTS.CPLASSY_ACTION,
p_transfer_organization_id => NULL,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
SELECT count(1)
INTO l_lotcount
FROM MTL_LOT_NUMBERS
WHERE INVENTORY_ITEM_ID = l_transaction_tbl(i).inventory_item_id
AND ORGANIZATION_ID = l_transaction_tbl(i).organization_id
AND LOT_NUMBER = l_transaction_tbl(i).lot_number;
INV_LOT_API_PUB.InsertLot(
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'F',
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_inventory_item_id => l_transaction_tbl(i).inventory_item_id,
p_organization_id => l_transaction_tbl(i).organization_id,
p_lot_number => l_transaction_tbl(i).lot_number,
p_expiration_date => l_transaction_tbl(i).lot_expiration_date,
p_transaction_temp_id => l_transaction_tbl(i).transaction_temp_id,
p_transaction_Action_id => WIP_CONSTANTS.CPLASSY_ACTION,
p_transfer_organization_id => NULL,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
select we.entity_type
into l_wip_entity_type
from wip_entities we
where we.wip_entity_id = p_wip_entity_id ;
SELECT line_id
INTO l_line_id
FROM oe_order_lines_all
WHERE line_id = p_transaction_tbl(i).demand_source_line_id
FOR UPDATE;
SELECT requirement_date,
primary_uom_code,
primary_open_quantity
INTO l_requirement_date,
l_primary_uom_code,
l_primary_open_quantity
FROM wip_open_demands_v
WHERE organization_id = p_transaction_tbl(i).organization_id
AND inventory_item_id = p_transaction_tbl(i).inventory_item_id
AND demand_source_header_id = p_transaction_tbl(i).demand_source_header_id
AND demand_source_line_id = p_transaction_tbl(i).demand_source_line_id
AND primary_open_quantity > 0;
SELECT source_document_type_id
INTO l_so_type
FROM oe_order_headers_all
WHERE header_id = l_oe_header_id;
SELECT count(1)
INTO l_lotcount
FROM MTL_LOT_NUMBERS
WHERE INVENTORY_ITEM_ID = l_reservation_rec.inventory_item_id
AND ORGANIZATION_ID = l_reservation_rec.organization_id
AND LOT_NUMBER = l_reservation_rec.lot_number;
wip_logger.log('inserting log', l_return_status);
INV_LOT_API_PUB.InsertLot(p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'F',
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_inventory_item_id => l_reservation_rec.inventory_item_id,
p_organization_id => l_reservation_rec.organization_id,
p_lot_number => l_reservation_rec.lot_number,
p_expiration_date => l_expiration_date,
p_transaction_temp_id => p_table_line_id,
p_transaction_Action_id => wip_constants.cplassy_action,
p_transfer_organization_id => NULL,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
select completion_transaction_id,
transaction_action_id,
transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = p_transaction_header_id
and transaction_temp_id = nvl(p_transaction_temp_id,transaction_temp_id);
UPDATE WIP_FLOW_SCHEDULES
SET demand_source_line = to_char(p_new_demand_source_line_id)
WHERE to_number(demand_source_line) = p_old_demand_source_line_id;
SELECT mmtt.demand_source_header_id,
mmtt.demand_source_line,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision,
mmtt.subinventory_code,
mmtt.locator_id,
msi.lot_control_code ,
mtlt.lot_number,
mmtt.transaction_source_id,
mmtt.transaction_uom,
mmtt.transaction_date,
sum(decode(msi.lot_control_code,
2 /*WIP_CONSTANTS.LOT*/, mtlt.primary_quantity*sign(mmtt.primary_quantity), /* ER 4163405 */
mmtt.primary_quantity)),
sum(decode(msi.lot_control_code,
2 /*WIP_CONSTANTS.LOT*/, mtlt.transaction_quantity*sign(mmtt.transaction_quantity),
mmtt.transaction_quantity)),
mmtt.demand_class,
mtlt.lot_expiration_date,
mmtt.transaction_temp_id,
null --never an lpn associated with a non-lpn completion
FROM MTL_SYSTEM_ITEMS MSI,
MTL_TRANSACTION_LOTS_TEMP MTLT,
MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE mmtt.completion_transaction_id = cp_transaction_header_id
AND mmtt.transaction_action_id = cp_transaction_action_id
AND mmtt.transaction_source_type_id = 5 /* Job or Schedule */
AND mtlt.transaction_temp_id (+) = mmtt.transaction_temp_id
AND MMTT.inventory_item_id = msi.inventory_item_id
AND MMTT.organization_id = msi.organization_id
AND (p_txn_temp_id IS NULL OR
mmtt.transaction_temp_id = p_txn_temp_id)
GROUP BY
mmtt.demand_source_header_id,
mmtt.demand_source_line,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision,
mmtt.subinventory_code,
mmtt.locator_id,
msi.lot_control_code,
mtlt.lot_number,
mmtt.transaction_source_id,
mmtt.transaction_uom,
mmtt.transaction_date,
mmtt.demand_class,
mtlt.lot_expiration_date,
mmtt.transaction_temp_id
ORDER BY
mmtt.transaction_temp_id,
mmtt.demand_source_header_id,
mmtt.demand_source_line;
SELECT wlc.demand_source_header_id, --to_number(null),
wlc.demand_source_line,--null,
wlc.organization_id,
wlc.inventory_item_id,
decode(msi.revision_qty_control_code,2,wlc.bom_revision,null),
wlc.subinventory_code,
wlc.locator_id,
msi.lot_control_code ,
wlcl.lot_number,
wlc.wip_entity_id,
wlc.transaction_uom,
wlc.transaction_date,
p_primary_quantity,
p_primary_quantity,
null,
WLCL.lot_expiration_date,
null,
p_lpn_id
FROM MTL_SYSTEM_ITEMS MSI,
WIP_LPN_COMPLETIONS_LOTS WLCL,
WIP_LPN_COMPLETIONS WLC
WHERE wlc.header_id = cp_header_id
AND wlc.transaction_source_type_id = 5 /* Job or Schedule */
AND wlcl.header_id (+) = wlc.header_id
AND wlcl.lot_number (+) = p_lot_number
AND wlc.transaction_action_id = cp_transaction_action_id
AND wlc.inventory_item_id = msi.inventory_item_id
AND wlc.organization_id = msi.organization_id
GROUP BY
wlc.demand_source_header_id,
wlc.demand_source_line,
wlc.organization_id,
wlc.inventory_item_id,
decode(msi.revision_qty_control_code,2,wlc.bom_revision,null),
wlc.subinventory_code,
wlc.locator_id,
msi.lot_control_code,
wlcl.lot_number,
wlc.wip_entity_id,
wlc.transaction_uom,
wlc.primary_quantity,
wlc.transaction_date,
WLCL.lot_expiration_date;
SELECT source_document_type_id
INTO l_so_type
FROM oe_order_headers_all
WHERE header_id = l_oe_header_id;
SELECT line_id
INTO l_line_id
FROM oe_order_lines_all
WHERE line_id = p_transaction_rec.demand_source_line_id ;
SELECT requirement_date,
primary_uom_code,
primary_open_quantity
INTO l_requirement_date,
l_primary_uom_code,
l_primary_open_quantity
FROM wip_open_demands_v
WHERE organization_id = p_transaction_rec.organization_id
AND inventory_item_id = p_transaction_rec.inventory_item_id
AND demand_source_header_id
= p_transaction_rec.demand_source_header_id
AND demand_source_line_id
= p_transaction_rec.demand_source_line_id;
INV_Reservation_PUB.Update_Reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => p_reservation_rec
, p_to_rsv_rec => l_reservation_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
select msi.replenish_to_order_flag into IS_ATO_ITEM
from mtl_system_items msi
where msi.organization_id = p_organization_id
and msi.inventory_item_id = p_inventory_item_id;
select count(*) into cnt
from mtl_reservations
where demand_source_line_id = p_order_line_id;
select count(*) into cnt
from mtl_reservations
where demand_source_line_id = p_order_line_id;
CTO_WIP_WORKFLOW_API_PK.last_wo_reservation_deleted(
p_order_line_id,
x_return_status,
x_msg_count,
x_msg_data);
SELECT wip_entity_id
FROM wip_discrete_jobs wdj
WHERE wdj.organization_id = p_org_id
AND wdj.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG)
AND wdj.wip_entity_id IN (SELECT wip_entity_id
FROM wip_reservations_v
WHERE demand_source_line_id = p_line_id
AND demand_source_header_id = p_header_id
AND organization_id = p_org_id);
SELECT wip_entity_id
FROM wip_discrete_jobs wdj
WHERE wdj.organization_id = p_org_id
AND wdj.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG)
AND wdj.wip_entity_id IN (SELECT wip_entity_id
FROM wip_reservations_v
WHERE demand_source_line_id = p_line_id
AND demand_source_header_id = p_header_id
AND organization_id = p_org_id)
AND NOT EXISTS (SELECT 1
FROM wip_reservations_v wrv1
WHERE wrv1.demand_source_line_id = p_line_id
AND wrv1.demand_source_header_id = p_header_id
AND ((wrv1.organization_id <> p_org_id)
OR (wrv1.wip_entity_id <> wdj.wip_entity_id)))
AND NOT EXISTS (SELECT 1
FROM wip_reservations_v wrv2
WHERE wrv2.wip_entity_id = wdj.wip_entity_id
AND wrv2.organization_id = wdj.organization_id
AND ((wrv2.demand_source_header_id <> p_header_id)
OR (wrv2.demand_source_line_id <> p_line_id)));
SELECT wdj.wip_entity_id
FROM wip_discrete_jobs wdj, wip_reservations_v wrv
WHERE wdj.organization_id = p_org_id
AND wdj.organization_id = wrv.organization_id
AND wdj.status_type IN (WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG)
AND wdj.wip_entity_id = wrv.wip_entity_id
AND wrv.demand_source_line_id = p_line_id
AND wrv.demand_source_header_id = p_header_id
AND NOT EXISTS (SELECT 1
FROM wip_reservations_v wrv1
WHERE (wrv1.demand_source_line_id = p_line_id AND
wrv1.demand_source_header_id = p_header_id AND
(wrv1.organization_id <> p_org_id OR
wrv1.wip_entity_id <> wdj.wip_entity_id))
OR
(wrv1.wip_entity_id = wdj.wip_entity_id AND
wrv1.organization_id = wdj.organization_id AND
(wrv1.demand_source_header_id <> p_header_id OR
wrv1.demand_source_line_id <> p_line_id)));
SELECT so_change_response_type
INTO l_response_code
FROM wip_parameters
WHERE organization_id = p_org_id;
UPDATE wip_job_schedule_interface
SET status_type = WIP_CONSTANTS.HOLD,
last_update_date = SYSDATE
WHERE organization_id = p_org_id
AND source_code = 'WICDOL'
AND source_line_id = p_line_id;
select wip_entity_id,
transaction_id,
transaction_type,
organization_id,
primary_item_id,
fm_operation_seq_num,
fm_intraoperation_step_type,
to_operation_seq_num,
to_intraoperation_step_type,
primary_quantity,
primary_uom,
entity_type,
repetitive_schedule_id,
transaction_date
from wip_move_txn_interface wmti
where wmti.group_id = p_group_id
and wmti.process_phase = 2
and wmti.process_status = 2
and wmti.wip_entity_id = p_wip_entity_id
order by transaction_id;
select distinct wdj.wip_entity_id,
wdj.organization_id,
wdj.primary_item_id,
wdj.start_quantity,
wdj.quantity_completed,
wdj.quantity_scrapped
from wip_move_txn_interface wmti, wip_discrete_jobs wdj
where wmti.group_id = p_group_id
and wmti.wip_entity_id = wdj.wip_entity_id
and wmti.organization_id = wdj.organization_id
and wmti.process_phase = 2
and wmti.process_status = 2
and nvl(wmti.entity_type,1) <> 5
order by wdj.wip_entity_id;
select nvl(sum(primary_quantity),0)
into l_job_reservation_quantity
from wip_reservations_v
where wip_entity_id = l_wip_entity_id;
inv_reservation_pub.delete_reservation(
p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rsv_rec => l_reservation_tbl(j),
p_serial_number => l_dummy_sn);
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_original_rsv_rec => l_reservation_tbl(j),
p_to_rsv_rec => l_to_reservation_rec,
p_original_serial_number => l_dummy_sn,
p_to_serial_number => l_dummy_sn,
p_validation_flag => fnd_api.g_true);
PROCEDURE update_row(p_item_revision IN VARCHAR2,
p_reservation_id IN NUMBER,
p_requirement_date IN DATE,
p_demand_source_header_id IN NUMBER,
p_demand_source_line_id IN NUMBER,
p_primary_quantity IN NUMBER,
p_wip_entity_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_msg_count NUMBER;
inv_reservation_form_pkg.update_reservation(
p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
p_from_reservation_id => p_reservation_id,
p_from_requirement_date => NULL,
p_from_organization_id => NULL,
p_from_inventory_item_id => NULL,
p_from_demand_type_id => NULL,
p_from_demand_name => NULL,
p_from_demand_header_id => NULL,
p_from_demand_line_id => NULL,
p_from_primary_uom_code => NULL,
p_from_primary_uom_id => NULL,
p_from_reservation_uom_code => NULL,
p_from_reservation_uom_id => NULL,
p_from_reservation_quantity => NULL,
p_from_primary_rsv_quantity => NULL,
p_from_autodetail_group_id => NULL,
p_from_external_source_code => NULL,
p_from_external_source_line => NULL,
p_from_supply_type_id => NULL,
p_from_supply_header_id => NULL,
p_from_supply_line_id => NULL,
p_from_supply_name => NULL,
p_from_supply_line_detail => NULL,
p_from_revision => NULL,
p_from_subinventory_code => NULL,
p_from_subinventory_id => NULL,
p_from_locator_id => NULL,
p_from_lot_number => NULL,
p_from_lot_number_id => NULL,
p_from_pick_slip_number => NULL,
p_from_lpn_id => NULL,
p_from_ship_ready_flag => NULL,
p_to_requirement_Date => p_requirement_date,
p_to_demand_type_id => l_rsv_array(1).demand_source_type_id,
p_to_demand_name => l_rsv_array(1).demand_source_name,
p_to_demand_header_id => p_demand_source_header_id,
p_to_demand_line_id => p_demand_source_line_id,
p_to_demand_delivery_id => l_rsv_array(1).demand_source_delivery,
p_to_reservation_uom_code => l_rsv_array(1).reservation_uom_code,
p_to_reservation_uom_id => l_rsv_array(1).reservation_uom_id,
p_to_reservation_quantity => l_rsv_array(1).reservation_quantity,
p_to_primary_rsv_quantity => p_primary_quantity,
p_to_autodetail_group_id => l_rsv_array(1).autodetail_group_id,
p_to_external_source_code => l_rsv_array(1).external_source_code,
p_to_external_source_line => l_rsv_array(1).external_source_line_id,
p_to_supply_type_id => l_rsv_array(1).supply_source_type_id,
p_to_supply_header_id => p_wip_entity_id,
p_to_supply_line_id => l_rsv_array(1).supply_source_line_id,
p_to_supply_name => l_rsv_array(1).supply_source_name,
p_to_supply_line_detail => l_rsv_array(1).supply_source_line_detail,
p_to_revision => p_item_revision,
p_to_subinventory_code => l_rsv_array(1).subinventory_code,
p_to_subinventory_id => l_rsv_array(1).subinventory_id,
p_to_locator_id => l_rsv_array(1).locator_id,
p_to_lot_number => l_rsv_array(1).lot_number,
p_to_lot_number_id => l_rsv_array(1).lot_number_id,
p_to_pick_slip_number => l_rsv_array(1).pick_slip_number,
p_to_lpn_id => l_rsv_array(1).lpn_id,
p_to_ship_ready_flag => l_rsv_array(1).ship_ready_flag,
p_to_attribute_category => l_rsv_array(1).attribute_category,
p_to_attribute1 => l_rsv_array(1).attribute1,
p_to_attribute2 => l_rsv_array(1).attribute2,
p_to_attribute3 => l_rsv_array(1).attribute3,
p_to_attribute4 => l_rsv_array(1).attribute4,
p_to_attribute5 => l_rsv_array(1).attribute5,
p_to_attribute6 => l_rsv_array(1).attribute6,
p_to_attribute7 => l_rsv_array(1).attribute7,
p_to_attribute8 => l_rsv_array(1).attribute8,
p_to_attribute9 => l_rsv_array(1).attribute9,
p_to_attribute10 => l_rsv_array(1).attribute10,
p_to_attribute11 => l_rsv_array(1).attribute11,
p_to_attribute12 => l_rsv_array(1).attribute12,
p_to_attribute13 => l_rsv_array(1).attribute13,
p_to_attribute14 => l_rsv_array(1).attribute14,
p_to_attribute15 => l_rsv_array(1).attribute15,
p_validation_flag => fnd_api.g_true,
/* Changes for Inventory */
p_from_serial_number_tbl => l_dummy_sn,
p_from_crossDock_flag => NULL,
p_to_serial_number_tbl => l_dummy_sn,
p_to_crossDock_flag => NULL,
/* End of Changes */
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END update_row;