[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure UpdateLine
(p_line_id In Number
,p_ordered_quantity In Number
,p_change_reason In Varchar2
,p_change_comments In Varchar2
,x_return_status out nocopy Varchar2
,x_msg_count out nocopy Number
,x_msg_data out nocopy Varchar2)
Is
l_api_name VARCHAR2(30) := 'UPDATELINE';
oe_debug_pub.add( 'ENTERING OE_SALES_CAN_UTIL.UPDATE_LINE' ) ;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.ADD('Update Line Process Order return UNEXP_ERROR');
oe_debug_pub.ADD('Update Line Process Order return RET_STS_ERROR');
oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.UPDATE_LINE' ) ;
END UpdateLine;
SELECT PRL.REQUISITION_LINE_ID
FROM PO_REQUISITION_LINES PRL,
PO_REQUISITION_HEADERS PRH
WHERE PRH.SEGMENT1 = p_line_rec.source_document_id
AND PRL.LINE_NUM = p_line_rec.source_document_line_id
AND PRL.REQUISITION_HEADER_ID =PRH.REQUISITION_HEADER_ID;*/
lhisttypecode := 'QUANTITY UPDATE';
lhisttypecode := 'QUANTITY UPDATE';
lhisttypecode := 'QUANTITY UPDATE';
lhisttypecode := 'QUANTITY UPDATE';
** update service not called when order is being cancelled.
if NOT(g_ord_lvl_can) then
*/
/*
** Fix Bug # 2157850
** Store the g_require_reason global before calling update
** service and restore after that.
*/
l_require_reason := oe_sales_can_util.g_require_reason;
oe_debug_pub.add('Before calling update_service()',1) ;
update_service(p_line_rec, p_old_line_rec, x_return_status);
oe_debug_pub.add('After calling update_service , return status : '||x_return_status,1) ;
SELECT quantity
INTO l_mtl_supply_quantity
FROM mtl_supply
WHERE req_line_id = p_line_rec.source_document_line_id
AND supply_type_code = 'REQ';
fnd_message.set_name('ONT','OE_CAN_UPDATE_SUPPLY');
fnd_message.set_name('ONT','OE_CAN_UPDATE_SUPPLY');
p_action => 'Update_Req_Line_Qty',
p_recreate_flag => FALSE,
p_qty => p_line_rec.ordered_quantity,
p_receipt_date => null,
p_reservation_action=>'UPDATE_SO_QUANTITY',
p_ordered_uom => l_order_quantity_uom);
p_request_type => OE_GLOBALS.G_DELETE_CHARGES,
x_return_status => l_return_status);
/* --sol_ord_er #16014165, Update qty check not required for Subscription Service */
NVL(p_x_line_rec.subscription_enable_flag,'N')='N' AND
NOT (g_ser_cascade) AND
(p_x_line_rec.Ordered_Quantity <> 0) then
if (p_x_line_rec.Ordered_Quantity <>
p_old_line_rec.Ordered_Quantity) THEN
fnd_message.set_name('ONT', 'OE_CAN_SERV_AMT_NOT_ALLOWED');
** Following columns are updated when WF Handle error is called.
p_x_line_rec.open_flag := 'N';
PROCEDURE update_service
( p_line_rec IN OE_Order_PUB.Line_Rec_Type
, p_old_line_rec IN OE_Order_PUB.Line_Rec_Type := OE_Order_PUB.G_MISS_LINE_REC
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR2(30):= 'Update_Service';
SELECT LINE_ID, ORDERED_QUANTITY
FROM oe_order_lines
WHERE service_reference_line_id = p_line_rec.line_id
AND service_reference_type_code = 'ORDER' -- these two conditions added for bug 2946327
AND nvl(cancelled_flag, 'N') <> 'Y';
oe_debug_pub.add( 'ENTERING OE_SALES_CAN_UTIL.UPDATE_SERVICE' ) ;
oe_debug_pub.add( 'BEFORE CALLING UPDATELINE FOR LINE ID: '|| L_LINE_ID ) ;
/* Call the UpdateLine only if ordered_quantity is greater than zero */
IF( l_service_quantity <> 0 )then
Updateline(l_line_id,
l_ordered_quantity,
l_change_reason,
l_change_comments,
l_return_status,
x_msg_count,
x_msg_data);
oe_debug_pub.add( 'AFTER CALLING UPDATELINE , RETURN STATUS: '|| L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.UPDATE_SERVICE' ) ;
end update_service;
select nvl(ordered_quantity, 0)
, nvl(cancelled_quantity, 0)
into l_old_ord_quantity
, l_old_can_quantity
from oe_order_lines
where line_id = p_line_rec.line_id;
** G_CANCELLATION_ACTION will be set to 'CR' from oe_config_pvt.handle_updates
*/
IF G_CANCELLATION_ACTION = 'CR'
AND nvl(oe_globals.g_pricing_recursion, 'N') = 'N' THEN
l_new_can_quantity := l_old_ord_quantity - p_line_rec.ordered_quantity
+ l_old_can_quantity;
** order right after quantity on the lines was updated to 0.
** Need to send the Cancelled Qty as Zero in such instances.
*/
IF p_line_rec.ordered_quantity = 0 AND
p_old_line_rec.ordered_quantity = 0 AND
OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can THEN
RETURN 0;
OE_Header_Adj_Util.Delete_Header_Charges( p_header_id => p_header_rec.header_id );
oe_debug_pub.add( 'LINE SELECTED FOR CANCELLATION , ID: '|| L_LINE_OLD_TBL ( J ) .LINE_ID , 1 ) ;
select 'Y'
into l_service_parent_exists
from oe_order_lines
where header_id = l_line_old_tbl(j).header_id
and line_id = l_line_old_tbl(j).service_reference_line_id;
select count(*)
into l_prg_line_count
from oe_price_adjustments opa1,
oe_price_adjustments opa2,
oe_price_adj_assocs opaa
where opa1.list_line_type_code = 'PRG'
and opa1.price_adjustment_id = opaa.price_adjustment_id
and opa2.price_adjustment_id = opaa.rltd_price_adj_id
and opa2.line_id = l_line_old_tbl(j).line_id;
l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'UPDATE LINE PROCESS ORDER RETURN UNEXP_ERROR' ) ;
oe_debug_pub.add( 'UPDATE LINE PROCESS ORDER RETURN RET_STS_ERROR' ) ;
SELECT DISTINCT OS.SET_ID
FROM oe_order_lines ol, oe_sets OS, oe_line_sets ols
WHERE ol.header_id = p_header_id
and OS.HEADER_ID = ol.header_id
and ol.line_id = ols.line_id
and ols.set_id = OS.set_id
and OS.SET_TYPE = 'FULFILLMENT_SET'
and OS.SET_STATUS in ('A','T')
and ol.cancelled_flag <> 'Y'
and ol.open_flag = 'Y';
SELECT count(1) INTO l_lines_in_set
FROM oe_order_lines ol, oe_line_sets ols
WHERE ol.header_id = p_header_id
AND ols.set_id = C_Set.set_id
AND ol.line_id = ols.line_id
AND ol.cancelled_flag <> 'Y'
AND ol.open_flag = 'Y';
SELECT count(1) INTO l_lines_awaiting
FROM oe_order_lines ol, oe_line_sets ols,
wf_item_activity_statuses WIAS,
wf_process_activities WPA
WHERE ol.header_id = p_header_id
AND ols.set_id = C_Set.set_id
AND ols.line_id = ol.line_id
AND WPA.activity_name = 'FULFILL_LINE'
AND WIAS.item_type = 'OEOL'
AND WIAS.item_key = to_char(ol.line_id)
AND WIAS.activity_status = 'NOTIFIED'
AND WIAS.Process_Activity = WPA.instance_id;
SELECT WIAS.Process_Activity, ol.line_id
INTO l_activity_id, l_line_id
FROM oe_order_lines ol, oe_line_sets ols,
wf_item_activity_statuses WIAS,
wf_process_activities WPA
WHERE ol.header_id = p_header_id
AND ols.set_id = C_Set.set_id
AND ols.line_id = ol.line_id
AND WPA.activity_name = 'FULFILL_LINE'
AND WIAS.item_type = 'OEOL'
AND WIAS.item_key = to_char(ol.line_id)
AND WIAS.activity_status = 'NOTIFIED'
AND WIAS.Process_Activity = WPA.instance_id
AND ROWNUM = 1;
oe_debug_pub.add( 'LINE SELECTED FOR CANCELLATION , ID: '|| L_LINE_OLD_TBL ( I ) .LINE_ID , 1 ) ;
select 'Y'
into l_service_parent_exists
from oe_order_lines
where header_id = l_line_old_tbl(i).header_id
and line_id = l_line_old_tbl(i).service_reference_line_id;
select count(*)
into l_prg_line_count
from oe_price_adjustments opa1,
oe_price_adjustments opa2,
oe_price_adj_assocs opaa,
oe_order_lines_all ol --bug 4156493
where opa1.list_line_type_code = 'PRG'
and opa1.price_adjustment_id = opaa.price_adjustment_id
and opa2.price_adjustment_id = opaa.rltd_price_adj_id
and opa2.line_id = l_line_old_tbl(i).line_id
--bug 4156493
and opa1.line_id = ol.line_id
and ol.shipped_quantity is null
AND Nvl(ol.open_flag,'N') = 'Y'; -- bug 13739573
l_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
** Following column will be updated in Check_Constraints()
** procedure if line reaches the cancellation point.
l_line_tbl(j).cancelled_flag := 'Y';
** Following columns are updated when WF Handle error is called.
*/
--l_line_tbl(j).flow_status_code := 'CANCELLED';
select ordered_quantity from
oe_order_lines_all
where line_id = x_request_rec.entity_id;
select ordered_quantity from
oe_order_lines_all
where header_id = x_request_rec.entity_id
and ordered_quantity <> 0;
OE_DEBUG_PUB.ADD('Before select ');
OE_DEBUG_PUB.ADD('before select Cursoer C1 ');
Update oe_order_lines_all
set cancelled_flag = 'Y' where
line_id = x_request_rec.entity_id;
select nvl(ordered_quantity2, 0)
, nvl(cancelled_quantity2, 0)
into l_old_ord_quantity2
, l_old_can_quantity2
from oe_order_lines
where line_id = p_line_rec.line_id;
** order right after quantity on the lines was updated to 0.
** Need to send the Cancelled Qty as Zero in such instances.
*/
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'in Cal_Cancelled_Qty2 2 ') ;