[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1 into x_future_date from dual where p_date <= sysdate ;
select inventory_item_id into p_item_id
from mtl_system_items_b_kfv
where
concatenated_segments = p_item
and organization_id = p_org_id;
select sum(transaction_quantity) into l_mti_qty
from mtl_transactions_interface
where transaction_source_id = p_trx_src_id
and transaction_type_id = 43
and organization_id = p_org_id
and inventory_item_id = p_item_id
and process_flag = 1; --1(ready). Do not pick up 3(errored) or 2(not ready)
select quantity_issued into l_qty_issued
from wip_requirement_operations
where wip_entity_id = p_trx_src_id
and inventory_item_id = p_item_id;
select secondary_inventory_name into p_subinv_code
from mtl_secondary_inventories
where
secondary_inventory_name = p_subinv_code
and organization_id = p_org_id
and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
and inv_material_status_grp.is_status_applicable(NULL,NULL,p_trx_type_id,NULL,NULL,
p_org_id,p_item_id,secondary_inventory_name,NULL,NULL,NULL,'Z') = 'Y' ;
select secondary_inventory_name into p_subinv_code
from mtl_secondary_inventories
where
secondary_inventory_name = p_subinv_code
and organization_id = p_org_id
and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
and inv_material_status_grp.is_status_applicable(NULL,NULL,p_trx_type_id,NULL,NULL,
p_org_id,p_item_id,secondary_inventory_name,NULL,NULL,NULL,'Z') = 'Y'
and EXISTS (select secondary_inventory from mtl_item_sub_inventories
where secondary_inventory = secondary_inventory_name
and inventory_item_id = p_item_id
and organization_id = p_org_id);
select Inventory_Location_ID into p_locator_id
from mtl_item_locations_kfv where
concatenated_segments = p_locator
and subinventory_code = p_subinv_code
and organization_id = p_org_id;
select Inventory_Location_ID into p_locator_id
from mtl_item_locations_kfv where
concatenated_segments = p_locator
and subinventory_code = p_subinv_code
and organization_id = p_org_id
and EXISTS (select '1' from mtl_secondary_locators
where inventory_item_id = p_item_id
and organization_id = p_org_id
and secondary_locator = inventory_location_id) ;
select reason_id into p_reason_id
from mtl_transaction_reasons
where
reason_name = p_reason
and nvl(disable_date,sysdate+1) > sysdate ;
select
mtl_material_transactions_s.nextval into x_header_id
from dual;
select mtl_material_transactions_s.nextval into INV_TRANSACTIONS.G_Serial_ID
from dual;
'Inserting into MTI with Tx Header ID=' || x_header_id);
/* Calling the Inventory Transaction API to insert data into MTI table .*/
INV_TRANSACTIONS.Line_Interface_Insert(
p_item_id,
p_revision,
p_org_id,
p_trx_src_id,
p_trx_action_id,
p_subinv_code,
p_tosubinv_code,
p_locator_id,
p_tolocator_id,
null,/* Added for bug# 5896548*/
/*p_org_id,Commented for bug# 5896548*/
p_trx_type_id,
p_trx_src_type_id,
p_trx_qty,
p_uom,
p_date,
p_reason_id,
p_user_id,
x_error_mssg,
x_status_flag
);
UPDATE MTL_TRANSACTIONS_INTERFACE SET
WIP_ENTITY_TYPE = p_wip_entity_type,
OPERATION_SEQ_NUM = p_operation_seq_num,
TRANSACTION_REFERENCE = p_trx_reference,
NEGATIVE_REQ_FLAG = negative_req_flag,
TRANSACTION_SOURCE_ID = p_trx_src_id,
TRANSACTION_MODE = l_tx_mode_mti --else background transactions wont be picked up
where
TRANSACTION_HEADER_ID = x_header_id ;
INV_TRANSACTIONS.LOT_INTERFACE_INSERT(
p_Transaction_Quantity => p_trx_qty,
p_Lot_Number => p_lot_num,
p_User_Id => p_user_id,
p_serial_number_control_code => p_serial_ctrl_code
);
INV_TRANSACTIONS.SERIAL_INTERFACE_INSERT(
p_From_Serial => p_from_ser_number,
p_To_Serial => p_to_ser_number,
p_User_Id => p_user_id,
p_lot_control_code => p_lot_ctrl_code
);
SELECT
negative_inv_receipt_code,stock_locator_control_code into
x_neg_flag,x_org_ctrl FROM MTL_PARAMETERS
WHERE
organization_id = p_org;
SELECT
locator_type into x_sub_ctrl
FROM MTL_SECONDARY_INVENTORIES
WHERE
organization_id = p_org and
secondary_inventory_name = p_subinv ;
SELECT
location_control_code,restrict_locators_code into
x_item_ctrl,x_restrict_flag
FROM MTL_SYSTEM_ITEMS
WHERE
inventory_item_id = p_item_id and
organization_id = p_org;
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
l_eam_direct_items_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
PROCEDURE insert_into_wro(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_wip_entity_id IN NUMBER
,p_organization_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_description IN VARCHAR2
,p_operation_seq_num IN NUMBER
,p_supply IN NUMBER
,p_mode IN VARCHAR2 := 'INSERT'
,p_required_date IN DATE
,p_quantity IN NUMBER
,p_comments IN VARCHAR2
,p_supply_subinventory IN VARCHAR2
,p_locator IN VARCHAR2
,p_mrp_net_flag IN VARCHAR2
,p_material_release IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wro';
invalid_update_operation NUMBER := 0;
invalid_update_department NUMBER := 0;
invalid_update_description NUMBER := 0;
l_update_status NUMBER := 0;
SAVEPOINT get_insert_into_wro_pvt;
select department_id
into l_department_id
from wip_operations
where wip_entity_id = l_wip_entity_id
and operation_seq_num = p_operation_seq_num
and organization_id = p_organization_id;
select inventory_location_id
into l_locator
from mtl_item_locations_kfv
where organization_id = p_organization_id
and concatenated_segments = p_locator
and subinventory_code = p_supply_subinventory ;
if(p_mode='INSERT') then
-- entry into WIP_REQUIREMENT_OPERATIONS
l_eam_mat_req_rec.batch_id := 1;
, p_debug_filename => 'insertwro.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
, p_debug_filename => 'updatewro.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
END insert_into_wro;
PROCEDURE insert_into_wdi(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_wip_entity_id IN NUMBER
,p_organization_id IN NUMBER
,p_direct_item_seq_id IN NUMBER := NULL
,p_description IN VARCHAR2
,p_operation_seq_num IN NUMBER
,p_mode IN VARCHAR2 := 'INSERT'
,p_direct_item_type IN VARCHAR2 :='1'
,p_purchasing_category_id NUMBER :=null
,p_suggested_vendor_id NUMBER :=null
,p_suggested_vendor_name VARCHAR2 :=null
,p_suggested_vendor_site VARCHAR2 :=null
,p_suggested_vendor_contact VARCHAR2 :=null
,p_suggested_vendor_phone VARCHAR2 :=null,
p_suggested_vendor_item_num VARCHAR2 :=null,
p_unit_price NUMBER :=null,
p_auto_request_material VARCHAR2 :=null,
p_required_quantity NUMBER :=null,
p_uom VARCHAR2 :=null,
p_need_by_date DATE :=null
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wro';
invalid_update_operation NUMBER := 0;
invalid_update_department NUMBER := 0;
invalid_update_description NUMBER := 0;
l_update_status NUMBER := 0;
SAVEPOINT get_insert_into_wro_pvt;
select department_id
into l_department_id
from wip_operations
where wip_entity_id = l_wip_entity_id
and operation_seq_num = p_operation_seq_num
and organization_id = p_organization_id;
if(p_mode='INSERT') then
l_eam_di_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
l_eam_di_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
, p_debug_filename => 'insertwdi.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
if(p_mode='INSERT') then
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
, p_debug_filename => 'insertwdi.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
END insert_into_wdi;