[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
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 mtl_material_transactions_s.nextval
FROM dual;
SELECT acct_period_id
FROM org_acct_periods
WHERE trunc(period_start_date) <= trunc(p_transaction_date)
AND trunc(schedule_close_date) >= trunc(p_transaction_date)
AND organization_id = p_organization_id
AND period_close_date is null
AND nvl(open_flag,'Y') = 'Y';
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 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
, p_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;