[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT interface_transaction_id
INTO l_interface_transaction_id
FROM rcv_transactions
WHERE transaction_id = p_rcv_transaction_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_interface_transaction_id;
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_interface_transaction_id;
PROCEDURE update_csi_data(
p_rcv_attributes_rec IN cse_datastructures_pub.rcv_attributes_rec_type,
x_rcv_txn_tbl OUT NOCOPY cse_datastructures_pub.rcv_txn_tbl_type,
x_return_status OUT NOCOPY varchar2,
x_error_message OUT NOCOPY varchar2)
IS
l_rcv_transaction_id NUMBER;
SELECT Instance_Status_Id
FROM CSI_Instance_Statuses
WHERE UPPER(NAME)='EXPIRED';
debug('Inside API cse_po_receipt_into_project.update_csi_data');
l_party_tbl.DELETE;
debug('Calling API csi_item_instance_pub.update_item_instance - nsrl destination update');
csi_item_instance_pub.update_item_instance(
p_api_version => l_api_version,
p_commit => l_commit,
p_validation_level => l_Validation_Level,
p_init_msg_list => l_init_msg_list,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_txn_rec => l_txn_rec,
p_asset_assignment_tbl => l_asset_assignment_tbl,
x_instance_id_lst => l_instance_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
l_instance_rec := cse_util_pkg.init_instance_update_rec;
l_party_tbl.DELETE;
debug('Calling API csi_item_instance_pub.update_item_instance - srl destination update');
csi_item_instance_pub.update_item_instance(
p_api_version => l_api_version,
p_commit => l_commit,
p_validation_level => l_Validation_Level,
p_init_msg_list => l_init_msg_list,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_txn_rec => l_txn_rec,
p_asset_assignment_tbl => l_asset_assignment_tbl,
x_instance_id_lst => l_instance_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
UPDATE mtl_serial_numbers
SET current_status = 4,
last_txn_source_name = 'CSE_PO_RECEIPT',
last_txn_source_id = x_rcv_txn_tbl(i).rcv_transaction_id
WHERE inventory_item_id = x_rcv_txn_tbl(i).inventory_item_id
AND serial_number = x_rcv_txn_tbl(i).serial_number;
debug('update_csi_data successful. rcv_transaction_id : '||p_rcv_attributes_rec.rcv_transaction_id);
END update_csi_data;
SELECT rt.transaction_id transaction_id,
rt.transaction_date transaction_date,
rt.transaction_type transaction_type,
rt.destination_type_code destination_type_code,
rt.employee_id transacted_by,
rt.organization_id organization_id,
rt.quantity quantity,
rt.po_header_id po_header_id,
rt.po_line_id po_line_id,
rt.po_distribution_id po_distribution_id,
rt.uom_code txn_uom_code,
rt.vendor_id po_vendor_id,
rt.shipment_header_id shipment_header_id,
rt.shipment_line_id shipment_line_id,
rt.interface_transaction_id interface_transaction_id,
pda.project_id project_id,
pda.task_id task_id,
pda.rate rate,
pda.org_id org_id,
plla.price_override price_override,
pla.item_id item_id,
pla.item_revision item_revision,
to_char(pla.line_num) po_line_number,
pha.segment1 po_number
FROM rcv_transactions rt,
po_distributions_all pda,
po_line_locations_all plla,
po_lines_all pla,
po_headers_all pha
WHERE rt.transaction_id = p_rcv_txn_id
AND rt.po_distribution_id = pda.po_distribution_id
AND rt.po_line_location_id = plla.line_location_id
AND rt.po_line_id = pla.po_line_id
AND rt.po_header_id = pha.po_header_id;
SELECT mtlt.lot_number lot_number,
msn.serial_number serial_number,
decode(mtlt.serial_transaction_temp_id,null,nvl(mtlt.transaction_quantity,p_quantity),1) quantity
FROM mtl_transaction_lots_temp mtlt,
mtl_serial_numbers msn
WHERE mtlt.transaction_temp_id = p_interface_transaction_id
AND msn.line_mark_id(+) = mtlt.serial_transaction_temp_id;
SELECT msn.serial_number serial_number
FROM mtl_serial_numbers msn
WHERE EXISTS (
SELECT 'x' FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_interface_transaction_id
AND msnt.transaction_temp_id = msn.line_mark_id) ;
SELECT primary_uom_code,
serial_number_control_code,
lot_control_code
INTO l_rcv_txn_rec.uom,
l_serial_code,
l_lot_code
FROM mtl_system_items
WHERE inventory_item_id = rcvtxn_rec.item_id
AND organization_id = rcvtxn_rec.organization_id;
l_sql_stmt := 'UPDATE rcv_receiving_sub_ledger '||
'SET pa_addition_flag = ''Y'''||
'WHERE rcv_transaction_id = :rcv_txn_id ';
UPDATE rcv_transactions
SET pa_addition_flag = 'Y'
WHERE transaction_id = p_rcv_transaction_id;
UPDATE rcv_transactions
SET pa_addition_flag = 'Y'
WHERE transaction_id = p_rcv_transaction_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT segment1
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT pt.task_number
FROM pa_tasks pt
WHERE pt.task_id = p_task_id
AND pt.project_id = p_project_id;
SELECT pv.vendor_id, pv.segment1
FROM po_vendors pv, po_headers_all ph
WHERE ph.po_header_id = p_po_header_id
AND pv.vendor_id = ph.vendor_id;
SELECT pod.Org_Id Org_ID,
SYSDATE Expenditure_Item_Date,
pod.expenditure_type Expenditure_Type,
pod.expenditure_organization_id Expenditure_Org_Id,
pod.code_combination_id Dr_CC_Id,
hr.Name Expenditure_Organization_Name
FROM po_distributions_all pod,
hr_organization_units hr
WHERE pod.po_distribution_id = p_po_distribution_id
AND hr.organization_id = pod.Expenditure_Organization_Id;
SELECT accts_pay_code_combination_id
FROM ap_system_parameters_all
WHERE org_id = p_org_id;
l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;