[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select c1.conversion_rate / c2.conversion_rate
into l_conversion_factor
from mtl_uom_conversions_view c1,
mtl_uom_conversions_view c2
where c1.organization_id = P_ORGANIZATION_ID
and c2.organization_id = P_ORGANIZATION_ID
and c1.inventory_item_id = P_INVENTORY_ITEM_ID
and c2.inventory_item_id = P_INVENTORY_ITEM_ID
and c1.uom_code = P_OLD_TRANSACTION_UOM_CODE
and c2.uom_code = P_NEW_TRANSACTION_UOM_CODE;
-- the monetary totals. Also update weight values.
--
if p_old_transaction_uom_code <> p_new_transaction_uom_code or
p_old_transaction_quantity <> abs(p_new_transaction_quantity) then
--
select c.minimum_accountable_unit,
c.precision
into l_mau,
l_precision
from fnd_currencies c
where c.currency_code = P_CURRENCY_CODE;
-- and hence we need to update the unit weight. If = 'MU'
-- then the total weight needs to be re-calculated from the
-- unit weight.
--
if p_unit_weight is not null then
if p_weight_method = 'MU' then
p_total_weight := p_unit_weight *
abs(p_new_transaction_quantity);
SELECT
movement_id,
transaction_uom_code,
transaction_quantity,
inventory_item_id,
organization_id,
currency_code,
document_unit_price,
document_line_ext_value,
weight_method,
unit_weight,
total_weight,
stat_adj_percent,
stat_adj_amount,
stat_ext_value,
outside_unit_price,
outside_ext_value
INTO
pseudo_movement_id,
l_old_transaction_uom_code,
l_old_transaction_quantity,
l_inventory_item_id,
l_organization_id,
l_currency_code,
l_document_unit_price,
l_document_line_ext_value,
l_weight_method,
l_unit_weight,
l_total_weight,
l_stat_adj_percent,
l_stat_adj_amount,
l_stat_ext_value,
l_outside_unit_price,
l_outside_ext_value
FROM mtl_movement_statistics
WHERE movement_id = p_movement_id
AND movement_status <> 'F' FOR UPDATE NOWAIT;
UPDATE mtl_movement_statistics
SET movement_status = 'O',
primary_quantity = p_primary_quantity,
transaction_quantity = ABS(p_transaction_quantity),
transaction_uom_code = p_transaction_uom,
transaction_date = p_transaction_date,
item_cost = p_actual_cost,
last_update_date = sysdate,
last_updated_by = user_id,
document_unit_price = l_document_unit_price,
document_line_ext_value = l_document_line_ext_value,
unit_weight = l_unit_weight,
total_weight = l_total_weight,
stat_ext_value = l_stat_ext_value,
outside_unit_price = l_outside_unit_price,
outside_ext_value = l_outside_ext_value
WHERE movement_id = p_movement_id;
UPDATE mtl_movement_statistics
SET item_cost = p_actual_cost,
last_update_date = sysdate,
last_updated_by = user_id,
document_unit_price = l_document_unit_price,
document_line_ext_value = l_document_line_ext_value,
unit_weight = l_unit_weight,
total_weight = l_total_weight,
stat_ext_value = l_stat_ext_value,
outside_unit_price = l_outside_unit_price,
outside_ext_value = l_outside_ext_value
WHERE movement_id = p_movement_id;
SELECT
movement_id,
transaction_uom_code,
transaction_quantity,
inventory_item_id,
organization_id,
currency_code,
document_unit_price,
document_line_ext_value,
weight_method,
unit_weight,
total_weight,
stat_adj_percent,
stat_adj_amount,
stat_ext_value,
outside_unit_price,
outside_ext_value
INTO
pseudo_movement_id,
l_old_transaction_uom_code,
l_old_transaction_quantity,
l_inventory_item_id,
l_organization_id,
l_currency_code,
l_document_unit_price,
l_document_line_ext_value,
l_weight_method,
l_unit_weight,
l_total_weight,
l_stat_adj_percent,
l_stat_adj_amount,
l_stat_ext_value,
l_outside_unit_price,
l_outside_ext_value
FROM mtl_movement_statistics
WHERE movement_id = l_movement_id
FOR UPDATE NOWAIT;
UPDATE mtl_movement_statistics
SET shipment_header_id = p_shipment_header_id,
shipment_line_id = p_shipment_line_id,
transaction_quantity = p_transaction_quantity,
transaction_uom_code = v_uom_code,
last_update_date = sysdate,
last_updated_by = user_id,
movement_status = 'O',
document_unit_price = l_document_unit_price,
document_line_ext_value = l_document_line_ext_value,
unit_weight = l_unit_weight,
total_weight = l_total_weight,
stat_ext_value = l_stat_ext_value,
outside_unit_price = l_outside_unit_price,
outside_ext_value = l_outside_ext_value
WHERE movement_id = p_movement_id;
select mtl_movement_statistics_s.NEXTVAL
into l_new_movement_id
from sys.dual;
INSERT INTO mtl_movement_statistics (
movement_id, organization_id,
entity_org_id, movement_type,
movement_status, transaction_date,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, document_source_type,
creation_method, document_reference,
document_line_reference, document_unit_price,
document_line_ext_value,
vendor_name, vendor_number,
vendor_site, po_header_id,
po_line_id, po_line_location_id,
shipment_header_id, shipment_line_id,
vendor_id, vendor_site_id,
parent_movement_id,
inventory_item_id, item_description,
item_cost, transaction_quantity,
transaction_uom_code, outside_code,
outside_ext_value, outside_unit_price,
currency_code,
category_id,
weight_method, unit_weight,
total_weight, transaction_nature,
delivery_terms, transport_mode,
dispatch_territory_code,
destination_territory_code,
origin_territory_code, area,
port, stat_type, comments,
stat_adj_amount, stat_adj_percent,
stat_ext_value
)
SELECT
l_new_movement_id,
organization_id, entity_org_id,
decode(movement_status, 'O', 'A', 'AA'),
'O', p_transaction_date,
sysdate, user_id,
sysdate, created_by,
last_update_login, document_source_type,
creation_method, document_reference,
document_line_reference, L_DOCUMENT_UNIT_PRICE,
L_DOCUMENT_LINE_EXT_VALUE,
vendor_name, vendor_number,
vendor_site, po_header_id,
po_line_id, po_line_location_id,
P_SHIPMENT_HEADER_ID, P_SHIPMENT_LINE_ID,
vendor_id, vendor_site_id,
P_PARENT_MOVEMENT_ID,
inventory_item_id, item_description,
item_cost, P_TRANSACTION_QUANTITY,
transaction_uom_code, outside_code,
L_OUTSIDE_EXT_VALUE,
L_OUTSIDE_UNIT_PRICE,
currency_code,
category_id,
weight_method, L_UNIT_WEIGHT,
L_TOTAL_WEIGHT,
transaction_nature,
delivery_terms, transport_mode,
dispatch_territory_code,
destination_territory_code,
origin_territory_code, area,
port, stat_type, comments,
stat_adj_amount, stat_adj_percent,
decode(stat_adj_percent, NULL, decode(stat_adj_amount,
NULL, NULL, stat_ext_value), stat_ext_value)
FROM mtl_movement_statistics
WHERE movement_id = p_parent_movement_id;
select movement_type
into l_movement_type
from mtl_movement_statistics
where movement_id = p_parent_movement_id;
update mtl_movement_statistics
set movement_type = 'AA'
where movement_id = l_new_movement_id;
update mtl_movement_statistics
set movement_type = 'DA',
transaction_quantity = (-1) * p_transaction_quantity
where movement_id = l_new_movement_id;
SELECT uom_code INTO v_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_transaction_uom_code;