[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_operation_seq_num NUMBER) IS select transaction_temp_id
from mtl_material_transactions_temp
where transaction_source_id= c_transaction_source_id
and organization_id=c_organization_id
and inventory_item_id=c_inventory_item_id
and operation_seq_num=c_operation_seq_num;
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 milk.Inventory_Location_ID into p_locator_id
from mtl_item_locations_kfv milk where
inv_project.Get_locator(milk.inventory_location_id, milk.organization_id) = p_locator
and milk.subinventory_code = p_subinv_code
and milk.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 milk.Inventory_Location_ID into p_locator_id
from mtl_item_locations_kfv milk where
inv_project.Get_locator(milk.inventory_location_id, milk.organization_id) = p_locator
and milk.subinventory_code = p_subinv_code
and milk.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
);
select department_id
into l_department_id
from wip_requirement_operations
where wip_entity_id = p_trx_src_id
and operation_seq_num = p_operation_seq_num
and organization_id = p_org_id
and inventory_item_id=p_item_id;
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
department_id = l_department_id -- fix for 8669096
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
);
select count(*)
into l_lotcount
From mtl_lot_numbers
where organization_id = p_org_id
and inventory_item_id = p_item_id
and lot_number = p_lot_num;
SELECT
supplier_lot_number,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
lot_attribute_category,
c_attribute1,
c_attribute2,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute20,
fnd_date.date_to_canonical ( d_attribute1 ),
fnd_date.date_to_canonical ( d_attribute2 ),
fnd_date.date_to_canonical ( d_attribute3 ),
fnd_date.date_to_canonical ( d_attribute4 ),
fnd_date.date_to_canonical ( d_attribute5 ),
fnd_date.date_to_canonical ( d_attribute6 ),
fnd_date.date_to_canonical ( d_attribute7 ),
fnd_date.date_to_canonical ( d_attribute8 ),
fnd_date.date_to_canonical ( d_attribute9 ),
fnd_date.date_to_canonical ( d_attribute10 ),
TO_CHAR ( n_attribute1 ),
TO_CHAR ( n_attribute2 ),
TO_CHAR ( n_attribute3 ),
TO_CHAR ( n_attribute4 ),
TO_CHAR ( n_attribute5 ),
TO_CHAR ( n_attribute6 ),
TO_CHAR ( n_attribute7 ),
TO_CHAR ( n_attribute8 ),
TO_CHAR ( n_attribute9 ),
TO_CHAR ( n_attribute10 )
Into
l_supplier_lot_num,
l_attribute_category ,
l_attribute1 ,
l_attribute2 ,
l_attribute3 ,
l_attribute4 ,
l_attribute5 ,
l_attribute6 ,
l_attribute7 ,
l_attribute8 ,
l_attribute9 ,
l_attribute10 ,
l_attribute11 ,
l_attribute12 ,
l_attribute13 ,
l_attribute14 ,
l_attribute15 ,
l_lot_attribute_category,
l_c_attribute1,
l_c_attribute2,
l_c_attribute3,
l_c_attribute4,
l_c_attribute5,
l_c_attribute6,
l_c_attribute7,
l_c_attribute8,
l_c_attribute9,
l_c_attribute10,
l_c_attribute11,
l_c_attribute12,
l_c_attribute13,
l_c_attribute14,
l_c_attribute15,
l_c_attribute16,
l_c_attribute17,
l_c_attribute18,
l_c_attribute19,
l_c_attribute20,
l_d_attribute1,
l_d_attribute2 ,
l_d_attribute3 ,
l_d_attribute4 ,
l_d_attribute5 ,
l_d_attribute6 ,
l_d_attribute7 ,
l_d_attribute8 ,
l_d_attribute9 ,
l_d_attribute10 ,
l_n_attribute1 ,
l_n_attribute2 ,
l_n_attribute3 ,
l_n_attribute4 ,
l_n_attribute5 ,
l_n_attribute6 ,
l_n_attribute7 ,
l_n_attribute8 ,
l_n_attribute9 ,
l_n_attribute10
FROM mtl_lot_numbers
WHERE lot_number = p_lot_num
AND inventory_item_id = p_item_id
AND organization_id = p_org_id ;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE
SET
supplier_lot_number = l_supplier_lot_num,
attribute_category = l_attribute_category ,
attribute1 = l_attribute1 ,
attribute2 = l_attribute2 ,
attribute3 = l_attribute3 ,
attribute4 = l_attribute4 ,
attribute5 = l_attribute5 ,
attribute6 = l_attribute6 ,
attribute7 = l_attribute7 ,
attribute8 = l_attribute8 ,
attribute9 = l_attribute9 ,
attribute10 = l_attribute10 ,
attribute11 = l_attribute11 ,
attribute12 = l_attribute12 ,
attribute13 = l_attribute13 ,
attribute14 = l_attribute14 ,
attribute15 = l_attribute15 ,
LOT_ATTRIBUTE_CATEGORY = l_lot_attribute_category,
C_ATTRIBUTE1= l_c_attribute1,
C_ATTRIBUTE2= l_c_attribute2,
C_ATTRIBUTE3= l_c_attribute3,
C_ATTRIBUTE4= l_c_attribute4,
C_ATTRIBUTE5= l_c_attribute5,
C_ATTRIBUTE6= l_c_attribute6,
C_ATTRIBUTE7= l_c_attribute7,
C_ATTRIBUTE8= l_c_attribute8,
C_ATTRIBUTE9= l_c_attribute9,
C_ATTRIBUTE10= l_c_attribute10,
C_ATTRIBUTE11= l_c_attribute11,
C_ATTRIBUTE12= l_c_attribute12,
C_ATTRIBUTE13= l_c_attribute13,
C_ATTRIBUTE14= l_c_attribute14,
C_ATTRIBUTE15= l_c_attribute15,
C_ATTRIBUTE16= l_c_attribute16,
C_ATTRIBUTE17= l_c_attribute17,
C_ATTRIBUTE18= l_c_attribute18,
C_ATTRIBUTE19= l_c_attribute19,
C_ATTRIBUTE20= l_c_attribute20,
D_ATTRIBUTE1= l_d_attribute1,
D_ATTRIBUTE2= l_d_attribute2 ,
D_ATTRIBUTE3= l_d_attribute3 ,
D_ATTRIBUTE4= l_d_attribute4 ,
D_ATTRIBUTE5= l_d_attribute5 ,
D_ATTRIBUTE6= l_d_attribute6 ,
D_ATTRIBUTE7= l_d_attribute7 ,
D_ATTRIBUTE8= l_d_attribute8 ,
D_ATTRIBUTE9= l_d_attribute9 ,
D_ATTRIBUTE10= l_d_attribute10 ,
N_ATTRIBUTE1= l_n_attribute1 ,
N_ATTRIBUTE2= l_n_attribute2 ,
N_ATTRIBUTE3= l_n_attribute3 ,
N_ATTRIBUTE4= l_n_attribute4 ,
N_ATTRIBUTE5= l_n_attribute5 ,
N_ATTRIBUTE6= l_n_attribute6 ,
N_ATTRIBUTE7= l_n_attribute7 ,
N_ATTRIBUTE8= l_n_attribute8 ,
N_ATTRIBUTE9= l_n_attribute9 ,
N_ATTRIBUTE10= l_n_attribute10
WHERE
transaction_interface_id = INV_TRANSACTIONS.G_Header_Id ;
end if ; --end of if block for selecting and updating the values
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
);
eam_material_allocations_pvt.delete_allocation
( p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_transaction_temp_id => l_txn_temp_id,
x_return_status => x_return_status,
x_msg_count => x_mssg_count,
x_msg_data => x_error_mssg
);
UPDATE wip_requirement_operations
SET required_quantity = quantity_issued
WHERE organization_id = p_org_id
AND wip_entity_id = p_trx_src_id
AND operation_seq_num = p_operation_seq_num
AND inventory_item_id = p_item_id;
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 milk.inventory_location_id
into l_locator
from mtl_item_locations_kfv milk
where milk.organization_id = p_organization_id
and concatenated_segments = p_locator
and subinventory_code = p_supply_subinventory ;*/
select milk.inventory_location_id
into l_locator
from mtl_item_locations_kfv milk
where milk.organization_id = p_organization_id
and inv_project.Get_locator(milk.inventory_location_id, milk.organization_id) = p_locator
and milk.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;