The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_ADJUSTMENTS purges the the unapplied adjustments from oe table
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
PROCEDURE DELETE_ADJUSTMENTS (
p_header_id IN NUMBER DEFAULT NULL
,p_line_id IN NUMBER DEFAULT NULL
)
IS
l_adjustment_id NUMBER;
SELECT price_adjustment_id
FROM oe_price_adjustments
WHERE header_id = p_header_id
AND list_line_type_code<>'TAX'
AND automatic_flag = 'N'
AND applied_flag = 'N'
AND retrobill_request_id IS NULL; --bug4099565
SELECT price_adjustment_id
FROM oe_price_adjustments adj
WHERE line_id = p_line_id
AND list_line_type_code<>'TAX'
AND automatic_flag = 'N'
AND applied_flag = 'N'
AND retrobill_request_id IS NULL --bug4099565
AND not exists( select 1 from oe_price_adj_assocs
where rltd_price_adj_id = adj.price_adjustment_id);
oe_debug_pub.add('ENTERING DELETE_ADJUSTMENTS');
DELETE FROM oe_price_adj_assocs
WHERE price_adjustment_id = l_adjustment_id;
DELETE FROM oe_price_adj_attribs
WHERE price_adjustment_id = l_adjustment_id;
DELETE FROM oe_price_adjustments
WHERE price_adjustment_id = l_adjustment_id;
DELETE FROM oe_price_adj_assocs
WHERE price_adjustment_id = l_adjustment_id;
DELETE FROM oe_price_adj_attribs
WHERE price_adjustment_id = l_adjustment_id;
DELETE FROM oe_price_adjustments
WHERE price_adjustment_id = l_adjustment_id;
oe_debug_pub.add( 'ERROR: BOTH PARAMETERS ARE NULL IN DELETE_ADJUSTMENTS ' , 1 ) ;
oe_debug_pub.add('EXITING DELETE_ADJUSTMENTS');
END DELETE_ADJUSTMENTS;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'HEADER'
,p_entity_id => l_old_header_rec.header_id
,p_header_id => l_old_header_rec.header_id
,p_line_id => null
,p_orig_sys_document_ref => l_old_header_rec.orig_sys_document_ref
,p_orig_sys_document_line_ref => null
,p_change_sequence => l_old_header_rec.change_sequence
,p_source_document_id => l_old_header_rec.source_document_id
,p_source_document_line_id => null
,p_order_source_id => l_old_header_rec.order_source_id
,p_source_document_type_id => l_old_header_rec.source_document_type_id);
l_header_rec.last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID); -- 3169637;
l_header_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
l_header_rec.last_update_date := SYSDATE;
UPDATE oe_order_headers
SET open_flag = l_header_rec.open_flag
, flow_status_code = l_header_rec.flow_status_code
, last_updated_by = l_header_rec.last_updated_by
, last_update_login = l_header_rec.last_update_login
, last_update_date = l_header_rec.last_update_date
, lock_control = l_header_rec.lock_control
WHERE header_id = p_header_id;
DELETE_ADJUSTMENTS(p_header_id => p_header_id);
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_header_rec => l_header_rec
, p_old_header_rec => l_old_header_rec
, p_header_id => l_header_rec.header_id
, x_index => l_notify_index
, x_return_status => x_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || X_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_header_rec.last_updated_by:=l_header_rec.last_updated_by;
OE_ORDER_UTIL.g_header_rec.last_update_login:=l_header_rec.last_update_login;
OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
* the update_flow_status is getting called directly. So *
* we need to call synch_header_line for 28 *
***********************************************************/
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' GENESIS : -CLOSE ORDER- header rec order source'||l_header_rec.order_source_id);
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey_sso
from dual;
SELECT cancelled_flag,
lock_control,
line_id,
header_id,
order_source_id,
orig_sys_document_ref,
orig_sys_line_ref,
orig_sys_shipment_ref,
change_sequence,
source_document_type_id,
source_document_id,
source_document_line_id
INTO l_old_line_tbl(1).cancelled_flag,
l_old_line_tbl(1).lock_control,
l_old_line_tbl(1).line_id,
l_old_line_tbl(1).header_id,
l_old_line_tbl(1).order_source_id,
l_old_line_tbl(1).orig_sys_document_ref,
l_old_line_tbl(1).orig_sys_line_ref,
l_old_line_tbl(1).orig_sys_shipment_ref,
l_old_line_tbl(1).change_sequence,
l_old_line_tbl(1).source_document_type_id,
l_old_line_tbl(1).source_document_id,
l_old_line_tbl(1).source_document_line_id
FROM oe_order_lines_all
WHERE line_id = p_line_id
FOR UPDATE NOWAIT;
OE_MSG_PUB.update_msg_context
( p_entity_code => 'LINE'
,p_entity_id => l_old_line_tbl(1).line_id
,p_header_id => l_old_line_tbl(1).header_id
,p_line_id => l_old_line_tbl(1).line_id
,p_orig_sys_document_ref => l_old_line_tbl(1).orig_sys_document_ref
,p_orig_sys_document_line_ref => l_old_line_tbl(1).orig_sys_line_ref
,p_orig_sys_shipment_ref => l_old_line_tbl(1).orig_sys_shipment_ref
,p_change_sequence => l_old_line_tbl(1).change_sequence
,p_source_document_id => l_old_line_tbl(1).source_document_id
,p_source_document_line_id => l_old_line_tbl(1).source_document_line_id
,p_order_source_id => l_old_line_tbl(1).order_source_id
,p_source_document_type_id => l_old_line_tbl(1).source_document_type_id);
OE_MSG_PUB.update_msg_context
( p_entity_code => 'LINE'
,p_entity_id => l_old_line_tbl(1).line_id
,p_header_id => l_old_line_tbl(1).header_id
,p_line_id => l_old_line_tbl(1).line_id
,p_orig_sys_document_ref => l_old_line_tbl(1).orig_sys_document_ref
,p_orig_sys_document_line_ref => l_old_line_tbl(1).orig_sys_line_ref
,p_orig_sys_shipment_ref => l_old_line_tbl(1).orig_sys_shipment_ref
,p_change_sequence => l_old_line_tbl(1).change_sequence
,p_source_document_id => l_old_line_tbl(1).source_document_id
,p_source_document_line_id => l_old_line_tbl(1).source_document_line_id
,p_order_source_id => l_old_line_tbl(1).order_source_id
,p_source_document_type_id => l_old_line_tbl(1).source_document_type_id);
SELECT inventory_item_id, org_id
INTO l_inventory_item_id, l_org_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
UPDATE oe_order_lines_all
SET flow_status_code='NOTIFY_COSTING_ERROR'
WHERE line_id = p_line_id;
UPDATE oe_order_lines
SET flow_status_code = 'POST-BILLING_ACCEPTANCE'
, last_updated_by = NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID)
, last_update_login = FND_GLOBAL.LOGIN_ID
, last_update_date = SYSDATE
, lock_control = l_old_line_tbl(1).lock_control + 1
WHERE line_id = p_line_id;
UPDATE oe_order_lines
SET open_flag = 'N'
, calculate_price_flag = 'N'
, flow_status_code = DECODE(l_old_line_tbl(1).cancelled_flag,'Y',
'CANCELLED','CLOSED')
, last_updated_by = NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID) -- 3169637
, last_update_login = FND_GLOBAL.LOGIN_ID
, last_update_date = SYSDATE
, lock_control = l_old_line_tbl(1).lock_control + 1
WHERE line_id = p_line_id;
l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);
l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
l_line_tbl(1).last_update_date := SYSDATE;
l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);
l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
l_line_tbl(1).last_update_date := SYSDATE;
UPDATE oe_order_lines
SET open_flag = l_line_tbl(1).open_flag
, calculate_price_flag = l_line_tbl(1).calculate_price_flag
, flow_status_code = l_line_tbl(1).flow_status_code
, last_updated_by = l_line_tbl(1).last_updated_by
, last_update_login = l_line_tbl(1).last_update_login
, last_update_date = l_line_tbl(1).last_update_date
, lock_control = l_line_tbl(1).lock_control
WHERE line_id = p_line_id;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_line_rec => l_line_tbl(1)
, p_old_line_rec => l_old_line_tbl(1)
, p_line_id => l_line_tbl(1).line_id
, x_index => l_notify_index
, x_return_status => x_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RET_STATUS FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || X_RETURN_STATUS , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL INDEX FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by:= l_line_tbl(1).last_updated_by;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login:=l_line_tbl(1).last_update_login;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date:=l_line_tbl(1).last_update_date;
END IF; -- we should notify after we update
DELETE_ADJUSTMENTS(p_line_id => p_line_id);
* the update_flow_status is getting called directly. So *
* we need to call synch_header_line for 28 *
***********************************************************/
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' GENESIS : CLOSE LINE - header rec order source'||p_line_id);
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey_sso
from dual;
OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(1),
p_change_type => 'LINE_STATUS',
p_req_id => l_itemkey_sso,
X_RETURN_STATUS => L_RETURN_STATUS_GEN);