[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_updated_quantity NUMBER;
g_line_updated_date DATE;
g_updated_quantity2 NUMBER; --Bug 14211120
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);
, 'UPDATED_QTY2'
, g_updated_quantity2);
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);
, 'UPDATED_QTY2'
, g_updated_quantity2);
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_Quantity2_Change IN NUMBER --Bug 14211120
, 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;
select source_document_line_id
from oe_order_lines_all
where header_id = p_header_id
and open_flag = 'N'
and nvl(cancelled_flag, 'N') = 'Y'; -- Cursor added for 8583903
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 => 4.0 -- Bug12970870, 14211120
, p_req_line_id_tbl => l_req_line_id_tbl
--, p_req_can_qty_tbl => l_req_can_qty_tbl -- Bug12970870
, p_req_can_prim_qty_tbl => l_req_can_qty_tbl -- Bug 14211120
, p_req_can_sec_qty_tbl => l_req_can_qty2_tbl -- Bug 14211120
, p_req_can_all => TRUE -- Bug 12970870
-- , p_req_line_id => NULL -- Header Level Cancellation -- Api Modified for 8583903
-- , 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/power(10,length(shipment_number))) as line_num
-- Added for bug #8974535
, 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
, prl.secondary_quantity --Bug 14211120
into g_inventory_item_name
, g_requisition_line_number
, g_requested_quantity
, g_requested_quantity2 --Bug 14211120
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 => 4.0 --Bug12970870 14211120
, p_req_line_id_tbl => l_req_line_id_tbl
--, p_req_can_qty_tbl => l_req_can_qty_tbl -- Bug12970870
, p_req_can_prim_qty_tbl => l_req_can_qty_tbl -- Bug 14211120
, p_req_can_sec_qty_tbl => l_req_can_qty2_tbl -- Bug 14211120
, p_req_can_all => FALSE --Bug12970870
-- , p_req_line_id => P_Req_Line_id
-- , p_req_hdr_id => P_Req_Header_id -- Api modified for 8583903
, 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/power(10,length(shipment_number))) as line_num
-- Added for bug 8974535
-- , 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.secondary_quantity --Bug 14211120
, prl.need_by_date
, prl.need_by_date
into g_inventory_item_name
, g_requisition_line_number
, g_requested_quantity
, g_requested_quantity2 --Bug 14211120
, 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 + NVL(P_Quantity_Change,0);
g_updated_quantity2 := g_requested_quantity2 + NVL(P_Quantity2_Change,0); --Bug 14211120
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 => 3.0 --Bug 14211120
, p_req_line_id => P_Req_Line_id
--, p_delta_quantity => P_Quantity_Change --Bug 14211120
, p_delta_quantity_prim => P_Quantity_Change --Bug 14211120
, p_delta_quantity_sec => P_Quantity2_Change --Bug 14211120
, 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;
g_updated_quantity2 := NULL; --Bug 14211120
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;