[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_count
from wf_item_activity_statuses wias,
wf_process_activities wpa
where wias.item_type = 'OEOL'
and wias.item_key = to_char(p_line_id)
and wias.ACTIVITY_STATUS = 'NOTIFIED'
and wias.process_activity = wpa.instance_id
and wpa.activity_name = 'SHIP_LINE';
updated or a non-shippable line reaches the SHIP_LINE activity.
*/
PROCEDURE Complete_Ship_Line_Activity
( p_api_version_number IN NUMBER
, p_line_id IN NUMBER
, p_result_code IN VARCHAR2
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
, x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
, x_update_tolerance_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, x_ship_tolerance OUT NOCOPY /* file.sql.39 change */ NUMBER
, x_ship_beyond_tolerance OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, x_shipped_within_tolerance OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, x_config_broken OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
, x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
x_update_tolerance_flag := FND_API.G_FALSE;
-- Tolerance needs to be updated so that ship confirm can result
-- in split.
l_temp_line_tbl := l_line_tbl;
x_update_tolerance_flag := FND_API.G_TRUE;
SELECT count(*)
INTO l_count_unshipped
FROM oe_order_lines
WHERE line_set_id = l_line_rec.line_set_id
AND shipped_quantity IS NULL
AND line_id <> l_line_rec.line_id ;
SELECT nvl(sum(shipped_quantity),0)
INTO l_del_shipping_quantity
FROM wsh_delivery_details
where source_line_id in (SELECT line_id
FROM oe_order_lines
WHERE line_set_id = l_line_rec.line_set_id
AND shipped_quantity IS NULL
AND line_id <> l_line_rec.line_id)
and source_code = 'OE'
and released_status = 'C'
group by requested_quantity_uom;
-- Tolerance needs to be updated so that ship confirm can result
-- in split.
l_shipped_quantity := 0;
x_update_tolerance_flag := FND_API.G_TRUE;
SELECT SUM(ordered_quantity)
, SUM(shipped_quantity)
, SUM(shipping_quantity)
INTO l_x_ordered_quantity
, l_x_shipped_quantity
, l_x_shipping_quantity
FROM oe_order_lines
WHERE line_set_id = p_line_set_id;
PROCEDURE Update_Shipping_Interface
(
p_api_version_number IN NUMBER
, p_line_id IN NUMBER
, p_shipping_interfaced_flag IN VARCHAR2
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
, x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
l_line_tbl OE_Order_PUB.Line_Tbl_Type;
oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE' , 1 ) ;
SAVEPOINT Update_Shipping_Interface;
update oe_order_lines
set shipping_interfaced_flag = p_shipping_interfaced_flag,
firm_demand_flag = NVL(l_firm_flag,firm_demand_flag),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
lock_control = lock_control + 1
where line_id = p_line_id;
l_line_tbl(1).last_update_date := SYSDATE;
l_line_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
oe_debug_pub.add('Do not update flow status ');
oe_debug_pub.add('Update flow status ');
Update oe_order_lines_all
Set firm_demand_flag = 'Y'
Where top_model_line_id = l_line_tbl(1).top_model_line_id;
Update oe_order_lines_all
Set firm_demand_flag = 'Y'
Where ato_line_id = l_line_tbl(1).ato_line_id;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_header_id =>l_line_tbl(1).header_id,
p_old_line_rec => l_old_line_tbl(1),
p_line_rec =>l_line_tbl(1),
p_line_id => p_line_id,
x_index => l_index,
x_return_status => x_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_SHIPPING_INTEGRATION_PUB IS:' || X_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date := SYSDATE;
OE_ORDER_UTIL.g_line_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login := FND_GLOBAL.LOGIN_ID;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login := FND_GLOBAL.LOGIN_ID;
Select ato_line_id ,
item_type_code,
ship_model_complete_flag,
top_model_line_id
Into l_ato_line_id,
l_item_type_code,
l_smc_flag,
l_top_model_line_id
From oe_order_lines_all
Where line_id = p_line_id;
Update oe_order_lines_all
Set firm_demand_flag = 'Y'
Where top_model_line_id = l_top_model_line_id;
Update oe_order_lines_all
Set firm_demand_flag = 'Y'
Where ato_line_id = l_ato_line_id;
oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE' , 1 ) ;
ROLLBACK TO Update_Shipping_Interface;
ROLLBACK TO Update_Shipping_Interface;
ROLLBACK TO Update_Shipping_Interface;
, 'Update_Shipping_Interface'
);
END Update_Shipping_Interface;
SELECT oel.line_id
INTO l_line_id
FROM oe_order_lines_all oel,
mtl_system_items msi
WHERE oel.header_id = p_header_id
AND (oel.ship_set_id = p_ship_set_id OR
(oel.top_model_line_id = p_top_model_line_id AND
oel.ship_model_complete_flag = 'Y'))
AND oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
AND oel.ordered_quantity > 0
and ((p_transactable_flag = 'N') OR
(p_transactable_flag = 'Y' AND
msi.mtl_transactions_enabled_flag = 'Y'))
AND oel.shipping_interfaced_flag = 'N'
AND (oel.shippable_flag = 'Y' OR
(EXISTS (SELECT 'Y'
FROM oe_order_lines_all oel1
WHERE oel1.header_id = p_header_id
AND (oel1.ship_set_id = p_ship_set_id OR
(oel1.top_model_line_id = p_top_model_line_id AND
oel1.ship_model_complete_flag = 'Y'))
AND oel1.ato_line_id = oel1.line_id
AND oel1.item_type_code in ('MODEL','CLASS')
AND oel1.ordered_quantity > 0
AND NOT EXISTS (SELECT 'Y'
FROM oe_order_lines_all oel2
WHERE oel2.top_model_line_id
= oel1.top_model_line_id
AND oel2.ato_line_id
= oel1.ato_line_id
AND oel2.item_type_code = 'CONFIG'))))
AND ROWNUM = 1;
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT line_id
INTO l_config_line_id
FROM oe_order_lines
WHERE header_id = OE_LINE_SECURITY.g_record.header_id
AND ato_line_id = OE_LINE_SECURITY.g_record.line_id
AND item_type_code = 'CONFIG';
SELECT 1
INTO config_line_ship_notified
FROM wf_item_activity_statuses wias, wf_process_activities wpa
WHERE wias.item_type='OEOL'
AND wias.item_key=l_config_line_id
AND wpa.activity_name='SHIP_LINE'
AND wias.activity_status='NOTIFIED'
AND wias.PROCESS_ACTIVITY = wpa.INSTANCE_ID;
SELECT oel.line_id
INTO l_line_id
FROM oe_order_lines_all oel
WHERE oel.header_id = l_header_id
AND (oel.ship_set_id = l_ship_set_id OR
oel.top_model_line_id = l_top_model_line_id)
AND oel.shipping_interfaced_flag = 'N'
AND (oel.shippable_flag = 'Y' OR
(EXISTS (SELECT 'Y'
FROM oe_order_lines_all oel1
WHERE oel1.header_id = l_header_id
AND (oel1.ship_set_id = l_ship_set_id OR
oel1.top_model_line_id = l_top_model_line_id)
AND oel1.ato_line_id = oel1.line_id
AND oel1.item_type_code in ('MODEL','CLASS')
AND NOT EXISTS (SELECT 'Y'
FROM oe_order_lines_all oel2
WHERE oel2.top_model_line_id
= oel1.top_model_line_id
AND oel2.ato_line_id
= oel1.ato_line_id
AND oel2.item_type_code = 'CONFIG'))))
AND ROWNUM = 1;