[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select l.line_id
, l.line_number
, l.shipment_number
, l.header_id
, l.ordered_quantity
, l.ordered_quantity2
, l.request_date
into l_line_ids_rec.line_id
, l_line_ids_rec.line_number
, l_line_ids_rec.shipment_number
, l_line_ids_rec.header_id
, l_line_ids_rec.ordered_quantity
, l_line_ids_rec.ordered_quantity2
, l_line_ids_rec.request_date
from oe_order_headers_all h
, oe_order_lines_all l
-- OE_Order_Header_All table is used in this query to use
-- the OE_Order_Headers_N7 index for performance reasons
where h.header_id = l.header_id
and h.source_document_id = p_internal_req_header_id
and l.source_document_line_id = p_internal_req_line_id
and h.source_document_type_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
and h.open_flag = 'Y'
and l.open_flag = 'Y'
and nvl(l.cancelled_flag,'N') = 'N'
and nvl(l.fulfilled_flag,'N') = 'N'
and nvl(l.shipped_quantity,0) = 0
and nvl(l.fulfilled_quantity,0) = 0
and l.actual_shipment_date is null
and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = l.line_id
and w.source_header_id = h.header_id
and w.source_code = 'OE'
and w.released_status = 'C')
order by l.shipment_number;
Function Update_Allowed -- Body definition
( P_line_id IN NUMBER
, P_Attribute IN VARCHAR2
) RETURN BOOLEAN
IS
--
l_line_rec OE_Order_Pub.Line_Rec_Type;
l_attr_update_allowed BOOLEAN := FALSE;
l_entity_update_allowed BOOLEAN := FALSE;
oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Update_Allowed', 1) ;
oe_debug_pub.add( ' Checking if update of Request Date is allowed', 5);
( p_operation => OE_PC_GLOBALS.UPDATE_OP
-- , p_column_name => 'REQUEST_DATE'
, p_record => l_line_rowtype_rec
, x_on_operation_action => l_action );
oe_debug_pub.add( ' Update of Request Date is allowed. Action'||l_action,1);
l_attr_update_allowed := TRUE;
oe_debug_pub.add( ' Update of Request Date is not allowed.',1);
l_attr_Update_Allowed := FALSE;
IF (NOT l_attr_update_allowed AND P_Attribute IS NULL)
OR P_Attribute in ('ORDERED_QUANTITY', 'ALL') THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' Checking if update of Ordered Quantity is allowed',5);
( p_operation => OE_PC_GLOBALS.UPDATE_OP
-- , p_column_name => 'ORDERED_QUANTITY'
, p_record => l_line_rowtype_rec
, x_on_operation_action => l_action );
oe_debug_pub.add( 'Update of Ordered Quantity is allowed. Action'||l_action,1);
l_attr_Update_Allowed := TRUE;
oe_debug_pub.add( ' Update of Ordered Quantity is not allowed.',1);
l_attr_Update_Allowed := FALSE;
oe_debug_pub.add( ' Checking if Update operation is allowed for a record',5);
IF ( NOT l_entity_update_allowed ) AND ( l_attr_update_allowed ) THEN
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'Update is allowed for Entity. Action'||l_action,1);
l_entity_Update_Allowed := TRUE;
oe_debug_pub.add( ' Entity Update is not allowed.',1);
l_entity_Update_Allowed := FALSE;
END IF; -- l_entity_update_allowed
IF l_entity_update_allowed AND l_attr_update_allowed THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' Order Line is allowed to UPDATE.',1);
oe_debug_pub.add( ' Order Line is NOT allowed to UPDATE.',1);
l_entity_update_allowed := FALSE;
l_attr_update_allowed := FALSE;
oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Update_Allowed', 1 ) ;
oe_debug_pub.add( ' When Others of OE_Internal_Requisition_Pvt.Update_Allowed '||sqlerrm,1);
OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Allowed');
End Update_Allowed;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
( p_operation => OE_PC_GLOBALS.UPDATE_OP
-- , p_column_name => 'ORDERED_QUANTITY'
, p_record => l_rowtype_rec
, x_on_operation_action => l_action );
select requisition_header_id
into l_req_hdr_id
from po_requisition_lines_all
where requisition_line_id = P_internal_req_line_id;
select requisition_header_id
into l_req_hdr_id
from po_requisition_lines_all
where requisition_line_id = P_internal_req_line_id;
select l.line_id, l.header_id
into l_line_id, l_header_id
from oe_order_headers_all h
, oe_order_lines_all l
, oe_order_holds_all oh
, oe_hold_sources_all hs
-- OE_Order_Header_All table is used in this query to use
-- the OE_Order_Headers_N7 index for performance reasons
where h.header_id = l.header_id
and h.header_id = oh.header_id
and l.line_id = oh.line_id
and oh.hold_source_id = hs.hold_source_id
and hs.hold_id = 17
and oh.hold_release_id is null
and h.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
and h.source_document_id = l_req_hdr_id
and l.source_document_line_id = P_internal_req_line_id;
, X_Update_Allowed OUT NOCOPY BOOLEAN
, X_Cancel_Allowed OUT NOCOPY BOOLEAN
, X_msg_count OUT NOCOPY NUMBER
, X_msg_data OUT NOCOPY VARCHAR2
, X_return_status OUT NOCOPY VARCHAR2
) IS
--
CURSOR All_Order_Lines (v_order_header_id NUMBER) IS
select l.line_id
from oe_order_lines_all l
where l.header_id = v_order_header_id
and nvl(l.cancelled_flag,'N') = 'N';
l_update_allowed BOOLEAN := FALSE;
X_Update_Allowed := FALSE;
oe_debug_pub.add('Requisition Line is NULL. We cannot check if Requisition Line is allowed to Update',1);
X_UPDATE_Allowed := FALSE;
select h.header_id
into l_header_id
from oe_order_headers_all h
where h.source_document_id = p_internal_req_header_id
and h.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
and h.open_flag = 'Y';
select requisition_header_id
into l_req_hdr_id
from po_requisition_lines_all
where requisition_line_id = P_internal_req_line_id;
IF NOT X_Update_Allowed THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' Checking if Update is allowed',5);
l_Update_Allowed := Update_Allowed(P_Line_id => l_line_ids_rec.line_id);
IF l_Update_Allowed THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' Update is Allowed',5);
X_Update_Allowed := TRUE;
oe_debug_pub.add(' Update is Not Allowed for this requisition line',1);
IF X_Update_Allowed THEN
oe_debug_pub.add( ' Record is allowed to Update',5);
X_Update_Allowed := FALSE;
X_Update_Allowed := FALSE;
X_Update_Allowed := FALSE;
select l.line_id, l.header_id, l.ordered_quantity2
from oe_order_lines_all l
-- , oe_order_headers_all h
where nvl(l.shipped_quantity,0) = 0
-- and h.orig_sys_document_ref = p_internal_req_header_id
-- and h.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
-- and h.header_id = v_order_header_id
-- and h.header_id = l.header_id
and l.source_document_id = p_internal_req_header_id
and l.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
and l.header_id = v_order_header_id
-- and h.open_flag = 'Y'
and nvl(cancelled_flag,'N') = 'N'
and l.open_flag = 'Y'
and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = l.line_id
and w.source_code = 'OE'
and released_status = 'C')
order by l.line_id;
l_lin_update NUMBER := 0;
select requisition_header_id
into l_req_hdr_id
from po_requisition_lines_all
where requisition_line_id = P_internal_req_line_id;
G_Update_ISO_From_Req := TRUE; -- Confirming IR initiated change
l_lin_update := 0;
select header_id
into l_order_header_id
from oe_order_headers_all h
where h.source_document_id = l_req_hdr_id
and h.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
and h.open_flag = 'Y';
l_line_tbl(l_cancel_eligble_lin).operation := OE_GLOBALS.G_OPR_UPDATE;
select count(line_id)
into l_count_of_lines
from oe_order_lines_all
where header_id = l_order_header_id
and nvl(cancelled_flag,'N') = 'N';
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(l_lin_cancel).operation := OE_GLOBALS.G_OPR_UPDATE;
AND p_Delta_Ordered_Qty <> 0) THEN -- This is an update request
IF l_debug_level > 0 THEN
oe_debug_pub.add(' This is an Update request',5);
oe_debug_pub.add(' Check if update is allowed for line_id '||l_line_ids_rec.line_id,5);
Update_Allowed( p_Line_id => l_line_ids_rec.line_id
, P_Attribute => 'REQUEST_DATE') THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Request Date is different w.r.t. sales order line ',5);
l_lin_update := l_lin_update + 1;
l_line_tbl(l_lin_update) := l_line_orig_rec;
l_line_tbl(l_lin_update).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(l_lin_update).request_date := P_New_Request_Date;
l_line_tbl(l_lin_update).change_reason := 'IR_ISO_CMS_CHG'; --'Internal requisition initiated change';
oe_debug_pub.add(' Update is not allowed for this line. Setting the status to Error',5);
oe_debug_pub.add(' Check for update of Line_id '||l_line_ids_rec.line_id,5);
IF Update_Allowed( p_Line_id => l_line_ids_rec.line_id
, P_Attribute => 'ORDERED_QUANTITY') THEN
l_lin_update := l_lin_update + 1;
l_line_tbl(l_lin_update) := l_line_orig_rec;
l_line_tbl(l_lin_update).ordered_quantity := l_line_ids_rec.ordered_quantity + P_Delta_Ordered_Qty;
l_line_tbl(l_lin_update).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(l_lin_update).change_reason := 'IR_ISO_CMS_CHG'; --'Internal requisition initiated change';
oe_debug_pub.add(' Update is not allowed for this line. Setting the status to Error',5);
oe_debug_pub.add(' Check for update of Line_id '||l_line_ids_rec.line_id,5);
Update_Allowed( p_Line_id => l_line_ids_rec.line_id
, P_Attribute => 'ALL') THEN
l_lin_update := l_lin_update + 1;
l_line_tbl(l_lin_update) := l_line_orig_rec;
l_line_tbl(l_lin_update).ordered_quantity := l_line_ids_rec.ordered_quantity + P_Delta_Ordered_Qty;
l_line_tbl(l_lin_update).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(l_lin_update).request_date := P_New_Request_Date;
l_line_tbl(l_lin_update).change_reason := 'IR_ISO_CMS_CHG'; --'Internal requisition initiated change';
oe_debug_pub.add(' Update is not allowed for this line. Setting the status to Error',5);
END IF; -- Update/Cancel Request
G_Update_ISO_From_Req := FALSE; -- Confirming IR initiated change
G_Update_ISO_From_Req := FALSE; -- Confirming IR initiated change
G_Update_ISO_From_Req := FALSE; -- Confirming IR initiated change
G_Update_ISO_From_Req := FALSE; -- Confirming IR initiated change