[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT uom_code
INTO l_uom_code
FROM mtl_units_of_measure_vl
WHERE unit_of_measure = p_uom_name;
SELECT * FROM mtl_system_items_kfv msib
WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
AND nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
Select count(1) INTO l_temp
from rcv_transactions rt , rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rt.shipment_line_id
and rt.shipment_header_id = rt.shipment_header_id
AND rsl.item_id = recs.inventory_item_id
AND rt.creation_date <= l_source_to_date
AND rt.creation_date > l_last_computation_Date
AND rt.transaction_type = 'RECEIVE'
AND rt.organization_id = recs.organization_id;
SELECT * FROM mtl_system_items_b msib
WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
and nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
Select count(*) INTO l_temp
from mtl_material_transactions mmt
where transaction_source_type_id = 2
AND transaction_type_id = 33
AND transaction_action_id= 1
AND inventory_item_id = recs.inventory_item_id
AND creation_date <= l_source_to_date
AND creation_date > l_last_computation_date
AND organization_id = recs.organization_id;
SELECT * FROM mtl_system_items_b msib
WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
and nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
Select count(*) INTO l_temp
from mtl_material_transactions mmt
where transaction_source_type_id = 2
AND transaction_type_id = 52
AND transaction_action_id = 28
and inventory_item_id = recs.inventory_item_id
AND transaction_quantity > 0
AND creation_date <= l_source_to_date
AND creation_date > l_last_computation_date
AND organization_id = recs.organization_id;
SELECT *
FROM mtl_system_items_b msib
WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
AND nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
Select rt.quantity quantity, NVL(rt.uom_code, get_item_uom_code(rt.unit_of_measure)) from_uom_code , rsl.item_id
from rcv_transactions rt, rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rt.shipment_line_id
and rt.shipment_header_id = rt.shipment_header_id
AND rsl.item_id = p_inventory_item_id
AND rt.creation_date <= p_source_to_date
AND rt.creation_date > p_last_computation_Date
AND rt.transaction_type = 'RECEIVE'
AND rt.organization_id = p_organization_id;
SELECT inv_convert.inv_um_convert(items.item_id,items.from_uom_code,l_billing_uom)
INTO l_conversion
FROM dual;
SELECT * FROM mtl_system_items_b msib
WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
and nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
Select count(*) INTO l_temp
from rcv_transactions rt, rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rt.shipment_line_id
and rt.shipment_header_id = rt.shipment_header_id
AND rsl.item_id = recs.inventory_item_id
AND rt.creation_date <= l_source_to_date
AND rt.creation_date > l_last_computation_Date
AND rt.transaction_type = 'DELIVER'
AND rt.organization_id = recs.organization_id;
SELECT Trunc(greatest(last_receipt_date, Nvl(last_invoiced_date, last_receipt_date))) greater_date
, locator_id, client_code, organization_id, Trunc(transaction_date) txn_date, transaction_action_id ,
transaction_quantity , current_onhand, Trunc(last_receipt_date) last_rcpt_date,
number_of_days, Trunc(last_invoiced_date) last_inv_date
from mtl_3pl_locator_occupancy inv
WHERE client_code = p_client_code
and organization_id in (select organization_id from org_organization_definitions
where operating_unit = p_operating_unit_id)
and (( nvl(last_invoiced_date, p_source_to_date) <= p_source_to_date )
AND ( transaction_date <= p_source_to_date ) )
order by organization_id, locator_id;
SELECT 'Y'
INTO l_lock_record
FROM mtl_3pl_locator_occupancy
WHERE locator_id = locator_occupancy_rec.locator_id
AND organization_id = locator_occupancy_rec.organization_id
AND client_code = locator_occupancy_rec.client_code
FOR UPDATE NOWAIT;
UPDATE mtl_3pl_locator_occupancy
SET number_of_days = l_new_number_of_days,
last_invoiced_date = l_invoice_date,
request_id= l_reqstid,
program_application_id = l_reqstid,
program_id = l_progid,
program_update_date = SYSDATE
WHERE locator_id = locator_occupancy_rec.locator_id
AND organization_id = locator_occupancy_rec.organization_id
AND client_code = locator_occupancy_rec.client_code;
debug(' : Reset Number of days counter, rows updated -> '||sql%rowcount);
SELECT Trunc(greatest(last_receipt_date, Nvl(last_invoiced_date, last_receipt_date))) greater_date
, locator_id, client_code, organization_id, Trunc(transaction_date) txn_date, transaction_action_id ,
transaction_quantity , current_onhand, Trunc(last_receipt_date) last_rcpt_date,
number_of_days, Trunc(last_invoiced_date) last_inv_date
from mtl_3pl_locator_occupancy inv
WHERE client_code = p_client_code
and organization_id in (select organization_id from org_organization_definitions
where operating_unit = p_operating_unit_id)
and (( nvl(last_invoiced_date, p_source_to_date) <= p_source_to_date )
AND ( transaction_date <= p_source_to_date ) )
order by organization_id, locator_id;
SELECT 'Y'
INTO l_lock_record
FROM mtl_3pl_locator_occupancy
WHERE locator_id = locator_occupancy_rec.locator_id
AND organization_id = locator_occupancy_rec.organization_id
AND client_code = locator_occupancy_rec.client_code
FOR UPDATE NOWAIT;
UPDATE mtl_3pl_locator_occupancy
SET number_of_days = l_new_number_of_days,
last_invoiced_date = l_invoice_date,
request_id= l_reqstid,
program_application_id = l_reqstid,
program_id = l_progid,
program_update_date = SYSDATE
WHERE locator_id = locator_occupancy_rec.locator_id
AND organization_id = locator_occupancy_rec.organization_id
AND client_code = locator_occupancy_rec.client_code;
debug(' : Reset Number of days counter, rows updated -> '||sql%rowcount);
SELECT Trunc(greatest(last_receipt_date, Nvl(last_invoiced_date, last_receipt_date))) greater_date
, locator_id, client_code, organization_id, Trunc(transaction_date) txn_date, transaction_action_id ,
transaction_quantity , current_onhand, Trunc(last_receipt_date) last_rcpt_date,
number_of_days, Trunc(last_invoiced_date) last_inv_date
from mtl_3pl_locator_occupancy inv
WHERE client_code = p_client_code
and organization_id in (select organization_id from org_organization_definitions
where operating_unit = p_operating_unit_id)
and (( nvl(last_invoiced_date, p_source_to_date) <= p_source_to_date )
AND ( transaction_date <= p_source_to_date ) )
order by organization_id, locator_id;
SELECT 'Y'
INTO l_lock_record
FROM mtl_3pl_locator_occupancy
WHERE locator_id = locator_occupancy_rec.locator_id
AND organization_id = locator_occupancy_rec.organization_id
AND client_code = locator_occupancy_rec.client_code
FOR UPDATE NOWAIT;
UPDATE mtl_3pl_locator_occupancy
SET number_of_days = l_new_number_of_days,
last_invoiced_date = l_invoice_date,
request_id= l_reqstid,
program_application_id = l_reqstid,
program_id = l_progid,
program_update_date = SYSDATE
WHERE locator_id = locator_occupancy_rec.locator_id
AND organization_id = locator_occupancy_rec.organization_id
AND client_code = locator_occupancy_rec.client_code;
debug(' : Reset Number of days counter, rows updated -> '||sql%rowcount);
SELECT inv_convert.inv_um_convert(0,x_volume_uom_code,p_billing_uom)
INTO l_conversion
FROM dual;
Select width , length
into x_width , x_length
from mtl_item_locations
where inventory_location_id = p_inventory_location_id
and organization_id = p_organization_id;