[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_updated_quantity NUMBER;
g_line_updated_date DATE;
oe_debug_pub.add( ' RETURN. No notification will be send as nothing has updated on the requisition', 5);
select to_char(oe_wf_key_s.nextval) into l_wf_item_key
from dual;
oe_debug_pub.add( 'Sending notification for requisition line quantity update', 5);
l_process_name := 'ISO_QTY_UPDATE';
, 'UPDATED_QTY'
, g_updated_quantity);
, 'LINE_UPDATE_DATE'
, g_line_updated_date);
oe_debug_pub.add( 'Sending notification for requisition line date update', 5);
l_process_name := 'ISO_SCH_DATE_UPDATE';
, 'LINE_UPDATE_DATE'
, g_line_updated_date);
oe_debug_pub.add( 'Sending notification for requisition line quantity and date update', 5);
l_process_name := 'ISO_QTY_SCH_DATE_UPDATE';
, 'UPDATED_QTY'
, g_updated_quantity);
, 'LINE_UPDATE_DATE'
, g_line_updated_date);
select user_name
into l_user_name
from fnd_user
where user_id = FND_GLOBAL.USER_ID;
Procedure Update_Internal_Requisition -- Body definition
( P_Header_id IN NUMBER
, P_Line_id IN NUMBER
, P_Line_ids IN VARCHAR2
, p_num_records IN NUMBER
, P_Req_Header_id IN NUMBER
, P_Req_Line_id IN NUMBER
, P_Quantity_Change IN NUMBER
, P_New_Schedule_Ship_Date IN DATE
, P_Cancel_Order IN BOOLEAN
, P_Cancel_Line IN BOOLEAN
, X_msg_count OUT NOCOPY NUMBER
, X_msg_data OUT NOCOPY VARCHAR2
, X_return_status OUT NOCOPY VARCHAR2
) IS
--
-- TYPE Line_id_tbl_TYPE is TABLE OF NUMBER;
l_call_po_api_for_update BOOLEAN := FALSE;
oe_debug_pub.add( 'ENTERING OE_Process_Requisition_Pvt.Update_Internal_Requisition', 1 ) ;
oe_debug_pub.add( ' Nothing to update on Requisition', 5 ) ;
SELECT org_id, segment1
INTO l_target_org_id, g_requisition_number
FROM po_requisition_Headers_all
WHERE requisition_header_id = p_req_Header_id;
oe_debug_pub.add( ' Calling PO_RCO_Validation_GRP.Update_ReqCancel_from_SO', 1 ) ;
select order_number, last_update_date
into g_sales_order_number, g_order_cancellation_date
from oe_order_headers_all
where header_id = p_header_id;
PO_RCO_Validation_GRP.Update_ReqCancel_from_SO
( p_api_version => 1.0
, p_req_line_id => NULL -- Header Level Cancellation
, p_req_hdr_id => P_Req_Header_id
, x_return_status => l_return_status
);
oe_debug_pub.add( ' After PO_RCO_Validation_GRP.Update_ReqCancel_from_SO '||l_return_status, 1 ) ;
oe_debug_pub.add( ' Calling PO_RCO_Validation_GRP.Update_ReqCancel_from_SO', 1 ) ;
select order_number
into g_sales_order_number
from oe_order_headers_all
where header_id = p_header_id;
select line_number||'.'||shipment_number
, nvl(cancelled_quantity,0)
, last_update_date
into g_sales_ord_line_num
, g_ISO_cancelled_quantity
, g_line_cancellation_date
from oe_order_lines_all
where line_id = p_line_id;
select items.concatenated_segments
, prl.line_num
, prl.quantity
into g_inventory_item_name
, g_requisition_line_number
, g_requested_quantity
from mtl_system_items_b_kfv items
, po_requisition_lines_all prl
, financials_system_params_all fsp
where fsp.org_id = prl.org_id
and fsp.inventory_organization_id = items.organization_id
and prl.item_id = items.inventory_item_id
and prl.requisition_line_id = P_Req_Line_id;
PO_RCO_Validation_GRP.Update_ReqCancel_from_SO
( p_api_version => 1.0
, p_req_line_id => P_Req_Line_id
, p_req_hdr_id => P_Req_Header_id
, x_return_status => l_return_status
);
oe_debug_pub.add( ' After PO_RCO_Validation_GRP.Update_ReqCancel_from_SO '||l_return_status, 5 ) ;
oe_debug_pub.add( ' Quantity OR Date changed. Req Line has to be updated', 5 ) ;
select order_number
into g_sales_order_number
from oe_order_headers_all
where header_id = p_header_id;
select line_number||'.'||shipment_number
-- , ordered_quantity
, schedule_ship_date
, schedule_arrival_date
, last_update_date
into g_sales_ord_line_num
-- , g_updated_quantity
, g_schedule_ship_date
, g_schedule_arrival_date
, g_line_updated_date
from oe_order_lines_all
where line_id = p_line_id;
select items.concatenated_segments
, prl.line_num
, prl.quantity
, prl.need_by_date
, prl.need_by_date
into g_inventory_item_name
, g_requisition_line_number
, g_requested_quantity
, g_need_by_date
, l_Req_Line_NeedByDate
from mtl_system_items_b_kfv items
, po_requisition_lines_all prl
, financials_system_params_all fsp
where fsp.org_id = prl.org_id
and fsp.inventory_organization_id = items.organization_id
and prl.item_id = items.inventory_item_id
and prl.requisition_line_id = P_Req_Line_id;
g_updated_quantity := g_requested_quantity + P_Quantity_Change;
oe_debug_pub.add( ' Ordered Quantity is changed. Update Req', 5 ) ;
l_call_po_api_for_update := TRUE;
oe_debug_pub.add( ' Ordered Quantity and Schedule Ship/Arrival Date are changed. Update Req', 5 ) ;
l_call_po_api_for_update := TRUE;
oe_debug_pub.add( ' Ordered Quantity is changed. Update Req', 5 ) ;
l_call_po_api_for_update := TRUE;
g_updated_quantity := NULL;
l_call_po_api_for_update := FALSE;
oe_debug_pub.add( ' Schedule Ship/Arrival Date is changed. Update Req', 5 ) ;
g_updated_quantity := NULL;
l_call_po_api_for_update := TRUE;
IF l_call_po_api_for_update THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' Calling PO_RCO_Validation_GRP.Update_ReqChange_from_SO', 1 ) ;
PO_RCO_Validation_GRP.Update_ReqChange_from_SO
( p_api_version => 1.0
, p_req_line_id => P_Req_Line_id
, p_delta_quantity => P_Quantity_Change
, p_new_need_by_date => l_New_Schedule_Arrival_Date -- l_New_Schedule_Ship_Date
, x_return_status => l_return_status
);
oe_debug_pub.add( ' After PO_RCO_Validation_GRP.Update_ReqChange_from_SO '||l_return_status, 1);
oe_debug_pub.add( ' Update_ReqChange_from_SO is not called as there is no valid change to process', 1);
IF OE_Schedule_GRP.G_ISO_Planning_Update THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add(' The caller for this change is Planning. Hence notification will not be send',5);
g_updated_quantity := NULL;
g_line_updated_date := NULL;
oe_debug_pub.add( 'EXITING OE_Process_Requisition_Pvt.Update_Internal_Requisition', 1 ) ;
oe_debug_pub.add( ' When Others of OE_Process_Requisition_Pvt.Update_Internal_Requisition '||sqlerrm,1);
OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Internal_Requisition');
End Update_Internal_Requisition;