[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select quantity_delivered
from mtl_txn_request_lines
where line_id = p_line_id;
select header_id
into l_mo_header_id
from MTL_TXN_REQUEST_lines
where line_id = p_line_id;
select count(line_id )
into l_other_line_id
from MTL_TXN_REQUEST_lines
where header_id = l_mo_header_id
and line_id <> p_line_id;
select header_id
from mtl_txn_request_lines
where line_id = p_line_id;
l_trolin_tbl(1).operation := inv_globals.g_opr_update;
SELECT header_id
INTO l_header_id
FROM mtl_txn_request_headers
WHERE header_id = px_header_id
AND organization_id = p_organization_id;
l_mohdr_rec.last_updated_by := nvl(fnd_global.user_id,1);
l_mohdr_rec.last_update_date := sysdate;
l_mohdr_rec.last_update_login := nvl(fnd_global.login_id,-1);
/* call table handlers for inserting into csp_move_order_header table*/
csp_to_form_moheaders.Validate_and_Write(
P_Api_Version_Number => 1.0
,P_Init_Msg_List => p_init_msg_list
,P_Commit => l_commit
,p_validation_level => null
,p_action_code => 0 -- 0 = insert, 1 = update, 2 = delete
,p_header_id => l_mohdr_rec.header_id
,p_created_by => nvl(fnd_global.user_id,1)
,p_creation_date => sysdate
,p_last_updated_by => nvl(fnd_global.user_id,1)
,p_last_update_date => sysdate
,p_last_update_login => nvl(fnd_global.login_id,-1)
,p_carrier => p_freight_carrier
,p_shipment_method => p_shipment_method
,p_autoreceipt_flag => p_autoreceipt_flag
,p_attribute_category => null
,p_attribute1 => null
,p_attribute2 => null
,p_attribute3 => null
,p_attribute4 => null
,p_attribute5 => null
,p_attribute6 => null
,p_attribute7 => null
,p_attribute8 => null
,p_attribute9 => null
,p_attribute10 => null
,p_attribute11 => null
,p_attribute12 => null
,p_attribute13 => null
,p_attribute14 => null
,p_attribute15 => null
,p_location_id => null
/*,p_address1 => p_address1
,p_address2 => p_address2
,p_address3 => p_address3
,p_address4 => p_address4
,p_city => p_city
,p_postal_code => p_postal_code
,p_state => p_state
,p_province => p_province
,p_country => p_country */
,X_Return_Status => l_return_status
,X_Msg_Count => l_msg_count
,X_Msg_Data => l_msg_data
);
SELECT line_id
INTO l_line_id
FROM mtl_txn_request_lines
WHERE line_id = px_line_id
AND organization_id = p_organization_id;
select nvl(max(line_number), 0)
into l_line_num
from mtl_txn_request_lines
where header_id = p_header_id;
l_trolin_tbl(l_order_count).last_updated_by := nvl(FND_GLOBAL.USER_ID,1);
l_trolin_tbl(l_order_count).last_update_date := sysdate;
l_trolin_tbl(l_order_count).last_update_login := nvl(FND_GLOBAL.LOGIN_ID, -1);
/* call table handlers for inserting into csp_move_order_lines table*/
csp_to_form_molines.Validate_and_Write(
P_Api_Version_Number => 1.0
,P_Init_Msg_List => p_init_msg_list
,P_Commit => l_commit
,p_validation_level => null
,p_action_code => 0
,P_line_id => l_trolin_tbl(l_order_count).line_id
,p_CREATED_BY => nvl(fnd_global.user_id,1)
,p_CREATION_DATE => sysdate
,p_LAST_UPDATED_BY => nvl(fnd_global.user_id,1)
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATED_LOGIN => nvl(fnd_global.login_id,-1)
,p_HEADER_ID => p_header_id
,p_CUSTOMER_PO => p_customer_po
,p_INCIDENT_ID => p_service_request
,p_TASK_ID => p_task_id
,p_TASK_ASSIGNMENT_ID => p_task_assignment_id
,p_COMMENTS => p_comments
,p_attribute_category => null
,p_attribute1 => null
,p_attribute2 => null
,p_attribute3 => null
,p_attribute4 => null
,p_attribute5 => null
,p_attribute6 => null
,p_attribute7 => null
,p_attribute8 => null
,p_attribute9 => null
,p_attribute10 => null
,p_attribute11 => null
,p_attribute12 => null
,p_attribute13 => null
,p_attribute14 => null
,p_attribute15 => null
,X_Return_Status => l_return_status
,X_Msg_Count => l_msg_count
,X_Msg_Data => l_msg_data
);
SELECT owner_resource_type, owner_resource_id
FROM csp_sec_inventories WHERE
organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT owner_resource_type, owner_resource_id
FROM csp_sec_inventories
WHERE organization_id = p_transfer_to_organization
AND secondary_inventory_name = p_transfer_to_subinventory;
SELECT user_name, source_name FROM jtf_rs_resource_extns
WHERE resource_id = l_src_rsc_id and category = l_src_rsc_type_converted;
SELECT user_name, source_name FROM jtf_rs_resource_extns
WHERE resource_id = l_dest_rsc_id and category = l_dest_rsc_type_converted;
SELECT concatenated_segments FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE inventory_item_id=p_inventory_item_id and organization_id=p_organization_id;
SELECT organization_code FROM mtl_organizations
WHERE Organization_id = p_organization_id;
SELECT organization_code FROM mtl_organizations
WHERE Organization_id = p_transfer_to_organization;
SELECT description FROM mtl_item_uoms_view
WHERE Organization_id = p_organization_id
AND Inventory_item_id = p_inventory_item_id AND uom_code = p_uom;
SELECT mtl_material_transactions_s.nextval
FROM dual;
SELECT acct_period_id,
least(sysdate,
decode(sign(trunc(period_start_date)-(trunc(p_transaction_date))),
1,sysdate,p_transaction_date)) transaction_date
FROM org_acct_periods
WHERE (trunc(p_transaction_date)
between trunc(period_start_date)
and trunc(schedule_close_date)
OR trunc(sysdate)
between trunc(period_start_date)
and trunc(schedule_close_date))
AND organization_id = p_organization_id
AND period_close_date is null
AND nvl(open_flag,'Y') = 'Y'
ORDER BY period_start_date asc;
SELECT application_id,
responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = 'SPARES_MANAGEMENT';
SELECT cost_of_sales_account,inventory_asset_flag
FROM mtl_system_items_b
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT asset_inventory
FROM mtl_secondary_inventories
WHERE organization_id = p_org_id
AND secondary_inventory_name = p_subinv;
select transaction_id
from mtl_material_transactions
where transaction_set_id = l_transaction_header_id;
select inventory_item_id into l_check_existence
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
INTO p_org_id
from dual;
SElECT gcc.code_combination_id
INTO l_code_comb_id
FROM hr_operating_units hou,gl_sets_of_books gsob,
gl_code_combinations gcc
-- WHERE hou.organization_id = p_organization_id
WHERE hou.organization_id = p_org_id
AND hou.set_of_books_id = gsob.set_of_books_id
AND gsob.chart_of_accounts_id = gcc.chart_of_accounts_id
AND gcc.code_combination_id = p_account_id;
SELECT SHIPMENT_NUMBER INTO l_ship_number
FROM MTL_MATERIAL_TRANSACTIONS_TEMP M
WHERE M.SHIPMENT_NUMBER = p_shipment_number AND ROWNUM = 1;
SELECT SHIPMENT_NUM INTO l_ship_number
FROM RCV_SHIPMENT_HEADERS M
WHERE M.SHIPMENT_NUM = p_shipment_number AND ROWNUM = 1;
SELECT SHIPMENT_NUMBER INTO l_ship_number
FROM MTL_TRANSACTIONS_INTERFACE M
WHERE M.SHIPMENT_NUMBER = p_shipment_number AND ROWNUM = 1;
INSERT INTO mtl_transactions_interface
( source_code
, source_header_id
, source_line_id
, process_flag
, transaction_mode
, transaction_header_id
, transaction_interface_id
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, transaction_uom
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_source_name
, transaction_type_id
, transfer_subinventory
, transfer_locator
, transfer_organization
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, lock_flag --always set to 2 so that the transaction_manager will pick the record and assign it to the transaction_worker.
, transaction_source_id
, trx_source_line_id
, waybill_airbill
, shipment_number
, freight_code
, reason_id
, transaction_reference
, expected_arrival_date
, WIP_ENTITY_TYPE
, FINAL_COMPLETION_FLAG
)
VALUES
( nvl(p_source_id,'CSP')
, 100 -- source header id
, nvl(p_source_line_id,1)
, 1 --process_flag yes
, 2 --transaction_mode online
, l_transaction_header_id
, l_transaction_interface_id
, p_inventory_item_id
, p_revision
, p_organization_id
, p_subinventory_code
, p_locator_id
, l_quantity
, p_uom
, l_transaction_date --transaction_date
, l_acct_period_id
, l_account_id
, p_transaction_source_name
, p_transaction_type_id
, p_transfer_to_subinventory
, p_transfer_to_locator
, p_transfer_to_organization
, sysdate --last_update_date
, nvl(fnd_global.user_id,1) --last_updated_by
, sysdate --creation_date
, nvl(fnd_global.user_id,1) --created_by
, nvl(fnd_global.login_id,-1)
, 2
, decode(sign(p_transaction_source_id-1000000000000),-1,
p_transaction_source_id,null)
, decode(sign(greatest(p_transaction_source_id,p_trx_source_line_id)
-1000000000000),-1,p_trx_source_line_id,null)
, p_waybill_airbill
, p_shipment_number
, p_freight_code
, p_reason_id
, p_transaction_reference
, p_expected_delivery_date
, l_WIP_ENTITY_TYPE
, l_FINAL_COMPLETION_FLAG
);
select process_flag,transaction_source_id,wip_entity_type
into l_process_flag,l_transaction_source_id,l_wip_entity_type
from mtl_transactions_interface
where transaction_header_id = l_transaction_header_id
and transaction_interface_id = l_transaction_interface_id;
select count(*)
into ln_count
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and serial_number_control_code <> 1
and lot_control_code = 2
and organization_id = p_organization_id;
Select lot_number
into l_lot_number_val
From MTL_SERIAL_NUMBERS_VAL_V
Where current_organization_id = p_organization_id
and current_subinventory_code = p_subinventory_code
and inventory_item_id = p_inventory_item_id
and serial_number = p_serial_number
and lot_number is not null;
INSERT INTO mtl_transaction_lots_interface
( transaction_interface_id
, lot_number
, lot_expiration_date
, transaction_quantity
, serial_transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
)
VALUES
( l_transaction_interface_id
, p_lot_number_temp
, p_lot_expiration_date
, p_quantity
, l_transaction_interface_id -- We will only have 1 serial number at a time
, sysdate
, nvl(fnd_global.user_id,-1)
, sysdate
, nvl(fnd_global.user_id,-1)
, nvl(fnd_global.login_id,-1)
);
INSERT INTO mtl_serial_numbers_interface
( transaction_interface_id
, fm_serial_number
, to_serial_number
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login)
VALUES
( l_transaction_interface_id
, p_serial_number
, nvl(p_to_serial_number, p_serial_number)
, sysdate --last_update_date
, nvl(fnd_global.user_id,1) --last_updated_by
, sysdate --creation_date
, nvl(fnd_global.user_id,1) --created_by
, nvl(fnd_global.login_id,-1) --last_update_login
);
select error_code, error_explanation
into l_error_code, l_error_explanation
from mtl_transactions_interface
where transaction_header_id = l_transaction_header_id
and rownum = 1;
delete from mtl_transactions_interface where transaction_header_id = l_transaction_header_id;
select
transaction_temp_id ,
transaction_source_id ,
lot_number ,
lot_expiration_date ,
transaction_quantity ,
move_order_line_id ,
item_lot_control_code ,
item_serial_control_code ,
inventory_item_id ,
organization_id ,
subinventory_code ,
locator_id ,
revision ,
TRANSACTION_UOM ,
SOURCE_CODE ,
source_line_id ,
TRANSACTION_TYPE_ID ,
distribution_account_id ,
transfer_subinventory ,
transfer_to_location ,
transfer_organization ,
trx_source_line_id ,
expected_arrival_date
from mtl_material_transactions_temp
where transaction_temp_id = p_transaction_temp_id;
select transaction_temp_id, serial_transaction_temp_id,
lot_number, lot_expiration_date, transaction_quantity
from mtl_transaction_lots_temp
where transaction_temp_id = p_transaction_temp_id;
select transaction_temp_id, fm_serial_number, to_serial_number, serial_prefix from mtl_serial_numbers_temp
where transaction_temp_id = l_transaction_temp_id;
SELECT mtl_material_transactions_s.nextval
FROM dual;
select distinct header_id from csp_moveorder_lines
where line_id = l_line_id;
select transaction_temp_id into l_check_existence
from mtl_material_transactions_temp
where transaction_temp_id = p_transaction_temp_id;
SELECT REQUEST_NUMBER
FROM MTL_TXN_REQUEST_HEADERS
WHERE HEADER_ID = v_move_order_id;
select h.task_id, l.requirement_line_id
from
csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d
where d.req_line_detail_id = p_req_line_detail_id
and d.requirement_line_id = l.requirement_line_id
and l.requirement_header_id = h.requirement_header_id
and h.task_id is not null;
select
rsh.shipment_header_id,
rsl.shipment_line_id,
'INTERNAL' SOURCE_TYPE_CODE,
rsh.receipt_source_code,
rsh.shipment_num,
rsh.ship_to_org_id,
rsh.bill_of_lading,
rsh.packing_slip,
rsh.shipped_date,
rsh.freight_carrier_code,
rsh.expected_receipt_date,
rsh.waybill_airbill_num,
rsh.RECEIPT_NUM
from
RCV_SHIPMENT_LINES rsl,
RCV_SHIPMENT_HEADERS rsh
where
rsl.shipment_line_id = l_rcv_ship_line_id
and rsh.shipment_header_id = rsl.shipment_header_id
and rsh.receipt_source_code = 'INTERNAL ORDER';
select
'INTERNAL' SOURCE_TYPE_CODE,
order_type_code,
item_id,
item_revision,
item_category_id,
item_description,
from_organization_id,
ordered_qty,
ordered_uom,
decode(SERIAL_NUMBER_CONTROL_CODE, 1, TRANSACTION_QTY, 1),
REQ_LINE_ID,
receipt_source_code,
lot_num,
PRIMARY_UOM,
PRIMARY_UOM_CLASS,
SERIAL_NUM,
TO_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
DESTINATION_TYPE_CODE,
ROUTING_ID,
SHIP_TO_LOCATION_ID,
ENFORCE_SHIP_TO_LOCATION_CODE,
SET_OF_BOOKS_ID_SOB,
CURRENCY_CODE_SOB,
SERIAL_NUMBER_CONTROL_CODE,
LOT_CONTROL_CODE,
LOT_QUANTITY,
item_revision
from
CSP_RECEIVE_lines_V
where
rcv_shipment_header_id = l_rcv_ship_header_id
and rcv_shipment_line_id = l_rcv_ship_line_id
and nvl(SERIAL_NUM, -999) = nvl(p_trans_record.SERIAL_NUMBER, -999);
SELECT h.destination_organization_id,
h.destination_subinventory
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d
WHERE d.req_line_detail_id = p_req_line_detail_id
AND d.requirement_line_id = l.requirement_line_id
AND l.requirement_header_id = h.requirement_header_id;
'Before calling csp_req_line_details_pkg.delete_row...');
csp_req_line_details_pkg.delete_row(p_req_line_detail_id);
'Before calling csp_req_line_details_pkg.Update_Row...');
csp_req_line_details_pkg.Update_Row(px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
p_REQUIREMENT_LINE_ID => l_requirement_line_id,
p_CREATED_BY => FND_GLOBAL.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
p_SOURCE_TYPE => 'RES',
p_SOURCE_ID => l_new_reservation_id);
'Before calling csp_req_line_details_pkg.Insert_Row...');
csp_req_line_details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
p_REQUIREMENT_LINE_ID => l_requirement_line_id,
p_CREATED_BY => FND_GLOBAL.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
p_SOURCE_TYPE => 'RES',
p_SOURCE_ID => l_new_reservation_id);
SELECT NVL(h.need_by_date, sysdate)
INTO l_hdr_need_by
FROM csp_requirement_headers h,
csp_requirement_lines l
WHERE l.requirement_line_id = l_requirement_line_id
AND l.requirement_header_id = h.requirement_header_id;
select reservation_quantity into l_total_reserved_qty
from mtl_reservations where reservation_id = l_new_reservation_id;
SELECT nvl(SUM(mr.reservation_quantity), 0)
INTO l_already_res_qty
FROM mtl_reservations mr,
csp_req_line_details cd
WHERE mr.reservation_id = cd.source_id
AND cd.source_type = 'RES'
AND cd.requirement_line_id = l_requirement_line_id
AND cd.source_id <> l_new_reservation_id;
SELECT req.quantity
INTO l_total_req_qty
FROM po_requisition_lines_all req,
oe_order_lines_all ord,
csp_req_line_details csp
WHERE req.requisition_line_id = ord.source_document_line_id
AND ord.line_id = csp.source_id
AND csp.source_type = 'IO'
AND csp.req_line_detail_id = l_req_line_detail_id;
SELECT COUNT(*)
INTO l_res_exists
FROM csp_req_line_details
WHERE requirement_line_id = l_requirement_line_id
AND source_type = 'RES'
AND source_id = l_new_reservation_id;
'Before calling csp_req_line_details_pkg.Insert_Row...');
csp_req_line_details_pkg.Insert_Row(px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
p_REQUIREMENT_LINE_ID => l_requirement_line_id,
p_CREATED_BY => FND_GLOBAL.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
p_SOURCE_TYPE => 'RES',
p_SOURCE_ID => l_new_reservation_id);
'Before calling csp_req_line_details_pkg.Insert_Row...');
csp_req_line_details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
p_REQUIREMENT_LINE_ID => l_requirement_line_id,
p_CREATED_BY => FND_GLOBAL.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
p_SOURCE_TYPE => 'RES',
p_SOURCE_ID => l_new_reservation_id);
select autoreceipt_flag
from csp_sec_inventories
where organization_id = cp_org_id
and secondary_inventory_name = cp_subinv;
select nvl(stocking_site_type, 'TECHNICIAN')
from csp_stocking_site_details_v
where organization_id = cp_org_id
and nvl(subinventory_code, 'NULL') = nvl(cp_subinv, 'NULL');
select internal_order_required_flag,
INTRANSIT_TYPE
from MTL_SHIPPING_NETWORK_VIEW
where from_organization_id = cp_s_org_id
and to_organization_id = cp_d_org_id;
SELECT rsl.to_organization_id AS organization_id,
rsl.to_subinventory AS subinv_code,
rsl.item_id,
rsl.item_revision AS revision,
rsl.quantity_received AS rcv_qty,
(SELECT quantity
FROM po_requisition_lines_all
WHERE requisition_line_id = oola.source_document_line_id
) AS ord_qty,
mmt.transaction_uom AS uom,
NVL(crh.need_by_date, sysdate) AS need_by_date,
crld.req_line_detail_id,
crl.requirement_line_id
FROM csp_requirement_headers crh,
csp_requirement_lines crl,
csp_req_line_details crld,
rcv_shipment_lines rsl,
oe_order_lines_all oola,
mtl_material_transactions mmt
WHERE rsl.SHIPMENT_HEADER_ID = l_shipment_header_id
AND rsl.ROUTING_HEADER_ID = -1 -- only direct shipment receive
AND rsl.mmt_transaction_id = mmt.transaction_id
AND rsl.requisition_line_id = oola.source_document_line_id
AND oola.line_id = crld.source_id
AND crld.source_type = 'IO'
AND crld.requirement_line_id = crl.requirement_line_id
AND crl.requirement_header_id = crh.requirement_header_id
AND crh.task_id IS NOT NULL;
SELECT COUNT(*)
INTO l_res_exists
FROM csp_req_line_details
WHERE requirement_line_id = grd.requirement_line_id
AND source_type = 'RES'
AND source_id = l_reservation_id;
csp_req_line_details_pkg.insert_row(px_req_line_detail_id => l_req_line_detali_id
,p_requirement_line_id => grd.requirement_line_id
,p_created_by => FND_GLOBAL.user_id
,p_creation_date => sysdate
,p_last_updated_by => FND_GLOBAL.user_id
,p_last_update_date => sysdate
,p_last_update_login => FND_GLOBAL.login_id
,p_source_type => 'RES'
,p_source_id => l_reservation_id );
select reservation_quantity into l_total_reserved_qty
from mtl_reservations where reservation_id = l_reservation_id;
SELECT nvl(SUM(mr.reservation_quantity), 0)
INTO l_already_res_qty
FROM mtl_reservations mr,
csp_requirement_lines cl,
csp_req_line_details cd
WHERE mr.reservation_id = cd.source_id
AND cd.source_type = 'RES'
AND cl.requirement_line_id = grd.requirement_line_id
AND cd.requirement_line_id = cl.requirement_line_id
AND cd.source_id <> l_reservation_id;
csp_req_line_details_pkg.delete_row(grd.req_line_detail_id);