[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM WF_ITEM_ACTIVITY_STATUSES WIAS
, WF_PROCESS_ACTIVITIES WPA
WHERE WIAS.item_type = 'OEOH'
AND WIAS.item_key = p_itemkey
AND WIAS.activity_status = 'DEFERRED'
AND WPA.activity_name = 'BOOK_DEFER'
AND WPA.instance_id = WIAS.process_activity;
PROCEDURE Update_Booked_Flag
(p_header_id IN NUMBER
,x_validate_cfg OUT NOCOPY BOOLEAN
,x_freeze_inc_items OUT NOCOPY BOOLEAN
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_index NUMBER := 1;
SELECT line_id
, booked_flag
, sold_to_org_id
, invoice_to_org_id
, ship_to_org_id
, tax_exempt_flag
, inventory_item_id
, order_quantity_uom
, ordered_quantity
, line_category_code
, item_type_code
, price_list_id
, unit_list_price
, unit_selling_price
, payment_term_id
, ship_from_org_id
, request_date
, line_type_id
, tax_date
, tax_code
, service_duration
, reference_line_id
, cancelled_flag
, orig_sys_document_ref
, orig_sys_line_ref
, source_document_id
, source_document_line_id
, service_coterminate_flag
, service_reference_type_code
, service_start_date
, service_end_date
, service_period
, header_id /* renga */
, org_id
, return_context
, reference_customer_trx_line_id /* end renga */
, order_firmed_date /* Key Transaction Dates */
FROM OE_ORDER_LINES
WHERE HEADER_ID = p_header_id;
oe_debug_pub.add( 'ENTERING UPDATE_BOOKED_FLAG', 0.5) ;
-- NOTE: Process order is called twice, once to update the booked flag
-- on the order header and the second time to update the booked flag
-- on all lines on the order. This cannot be done in one call as the
-- process order does not process lines(or any line level validation)
-- if the header validation fails. In order to give user complete
-- feedback i.e. validation errors for lines also if any, process order
-- is called for lines separately even if header update returns with
-- a status of ERROR.
-- Set up the header record
OE_Header_Util.Query_Row
(p_header_id => p_header_id
,x_header_rec => l_old_header_rec
);
l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_header_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
l_header_rec.last_update_date := SYSDATE;
SELECT TAX_CALCULATION_EVENT_CODE
into l_tax_calculation_event_code
from oe_transaction_types_all
where transaction_type_id = l_header_rec.order_type_id;
oe_debug_pub.add( 'REN: FAILED WHILE TRYING TO QUERY UP TAX_CALCUALTION_EVENT FOR ORDER_TYPE_ID IN UPDATE_BOOKED_FLAG' ) ;
SELECT count(*)
INTO l_active_phase_count
FROM QP_EVENT_PHASES
WHERE pricing_event_code = 'BOOK'
AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
trunc(nvl(end_date_active, sysdate));
l_line_tbl(l_index).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
l_line_tbl(l_index).last_update_login := FND_GLOBAL.LOGIN_ID;
l_line_tbl(l_index).last_update_date := SYSDATE;
-- delete the cancelled lines from the lines table, these do
-- not need to be sent to notify_oc
ELSE
l_old_line_tbl.DELETE(l_index);
l_line_tbl.DELETE(l_index);
SELECT Count(1)
INTO l_hdr_chg_count
FROM oe_price_adjustments
WHERE header_id= l_header_rec.header_id
AND list_line_type_code='FREIGHT_CHARGE'
AND line_id IS NULL;
UPDATE oe_order_headers_all
SET booked_flag = 'Y'
, booked_date = sysdate
, flow_status_code = 'BOOKED'
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.LOGIN_ID
, last_update_date = SYSDATE
, lock_control = lock_control + 1
WHERE header_id = p_header_id;
UPDATE oe_order_lines_all
SET booked_flag = 'Y'
, flow_status_code = 'BOOKED'
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.LOGIN_ID
, last_update_date = SYSDATE
, lock_control = lock_control + 1
WHERE header_id = p_header_id
AND nvl(cancelled_flag,'N') <> 'Y'; -- nvl added for bug 4486781
oe_debug_pub.add( 'BEFORE ENTERING UPDATE_GLOBAL_PICTURE IN BOOKING' ) ;
oe_debug_pub.add( 'BEFORE UPDATE , HEADER VALUE' || OE_ORDER_UTIL.G_HEADER_REC.HEADER_ID ) ;
oe_debug_pub.add( 'BEFORE UPDATE , OLD HEADER VALUE' || OE_ORDER_UTIL.G_OLD_HEADER_REC.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 => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || L_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;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_line_rec => l_line_tbl(l_loop_index)
, p_line_id => l_line_tbl(l_loop_index).line_id
, x_index => l_notify_index
, x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS IN OE_ORDER_BOOK_UTIL FOR LINE_ID '||L_LINE_TBL ( L_LOOP_INDEX ) .LINE_ID ||' IS: ' || L_RETURN_STATUS , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL INDEX IN OE_ORDER_BOOK_UTIL FOR LINE_ID '||L_LINE_TBL ( L_LOOP_INDEX ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by:= l_line_tbl(l_loop_index).last_updated_by;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login:=l_line_tbl(l_loop_index).last_update_login;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date:=l_line_tbl(l_loop_index).last_update_date;
oe_debug_pub.add( 'IN BOOKING , AFTER UPDATE LINE GLOBAL PICTURE' ) ;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_line_adj_id => l_line_adj_tbl(l_in_loop_index).price_adjustment_id
, x_index => l_notify_index
, x_return_status => l_return_status);
oe_debug_pub.add('in OEXUBOKB after insert into global table, line_id is ' || OE_ORDER_UTIL.g_line_adj_tbl(l_notify_index).line_id);
oe_debug_pub.add('in OEXUBOKB after insert into global table, operation is ' || OE_ORDER_UTIL.g_line_adj_tbl(l_notify_index).operation);
END LOOP outer; -- over each line to update global picture
oe_debug_pub.add( 'EXITING UPDATE_BOOKED_FLAG', 0.5) ;
, 'Update_Booked_Flag'
);
END Update_Booked_Flag;
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id;
SELECT
'EXISTS' INTO l_dummy
FROM oe_order_lines_all
WHERE header_id = p_header_id and
ROWNUM = 1;
-- Call process order to update the booked_flag on header and
-- on all the order lines. This will also check for all the fields
-- that are required on the order and the lines at booking
Update_Booked_Flag(p_header_id => p_header_id
, x_validate_cfg => l_validate_cfg
, x_freeze_inc_items => l_freeze_inc_items
, x_return_status => x_return_status
);
oe_debug_pub.add( 'RETURN STATUS AFTER UPDATE BOOKED:'||X_RETURN_STATUS ) ;
SELECT 'Y'
FROM WF_ITEM_ACTIVITY_STATUSES WIAS
, WF_PROCESS_ACTIVITIES WPA
WHERE WIAS.item_type = 'OEOH'
AND WIAS.item_key = l_itemkey
AND WIAS.activity_status = 'NOTIFIED'
AND WPA.activity_name = 'BOOK_ELIGIBLE'
AND WPA.instance_id = WIAS.process_activity;
SELECT 'Y'
FROM WF_ITEM_ACTIVITY_STATUSES WIAS
, WF_PROCESS_ACTIVITIES WPA
WHERE WIAS.item_type = 'OEOH'
AND WIAS.item_key = l_itemkey
AND WIAS.activity_status = 'ERROR'
AND WPA.activity_name = 'BOOK_ORDER'
AND WPA.instance_id = WIAS.process_activity;
SELECT order_source_id, orig_sys_document_ref, change_sequence, source_document_type_id, source_document_id,flow_status_code
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence, l_source_document_type_id, l_source_document_id,l_flow_status_code
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = p_header_id;
-- been updated.
SELECT booked_flag
INTO l_booked_flag
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = p_header_id;
p_event_name => 'oracle.apps.ont.oip.processfailure.update',
p_nameVal_tbl => l_nameVal_tbl);
,p_header_id_list IN OE_GLOBALS.Selected_Record_Tbl
,p_header_count IN NUMBER
,x_error_count OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'BOOK_MULTIPLE_ORDERS';
SELECT h.Transaction_Phase_Code
INTO l_Transaction_Phase_Code
FROM oe_order_headers_all h
WHERE l_header_id=h.header_id;
TYPE delete_header_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_delete_header_id_tbl delete_header_id_tbl_type;
l_header_id_tbl.DELETE;
l_delete_header_id_tbl.DELETE;
l_delete_header_id_tbl(J) := I; --keep track of which header id to delete later
l_delete_header_id_tbl(J) := I; --keep track of which header id to delete later
oe_debug_pub.add('l_delete_header_id_tbl.COUNT: ' || l_delete_header_id_tbl.COUNT);
FOR J IN 1..l_delete_header_id_tbl.COUNT LOOP
l_header_id_tbl.DELETE(l_delete_header_id_tbl(J));
oe_debug_pub.add('Deleted element with index ' || J || ' from l_header_id_tbl');