[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OE_SERVICE_UTIL.CHECK_PROC('ASO_ORDER_FEEDBACK_PUB.UPDATE_NOTICE', l_return_status);
ASO_ORDER_FEEDBACK_PUB.UPDATE_NOTICE(
1.0
, :p_init_msg_list
, :p_commit
, :x_return_status
, :x_msg_count
, :x_msg_data
, OE_SERVICE_UTIL.g_header_rec
, OE_SERVICE_UTIL.g_old_header_rec
, OE_SERVICE_UTIL.g_header_adj_tbl
, OE_SERVICE_UTIL.g_old_header_adj_tbl
, OE_SERVICE_UTIL.g_header_price_att_tbl
, OE_SERVICE_UTIL.g_old_header_price_att_tbl
, OE_SERVICE_UTIL.g_header_adj_att_tbl
, OE_SERVICE_UTIL.g_old_header_adj_att_tbl
, OE_SERVICE_UTIL.g_header_adj_assoc_tbl
, OE_SERVICE_UTIL.g_old_header_adj_assoc_tbl
, OE_SERVICE_UTIL.g_header_scredit_tbl
, OE_SERVICE_UTIL.g_old_header_scredit_tbl
, OE_SERVICE_UTIL.g_line_tbl
, OE_SERVICE_UTIL.g_old_line_tbl
, OE_SERVICE_UTIL.g_line_adj_tbl
, OE_SERVICE_UTIL.g_old_line_adj_tbl
, OE_SERVICE_UTIL.g_line_price_att_tbl
, OE_SERVICE_UTIL.g_old_line_price_att_tbl
, OE_SERVICE_UTIL.g_line_adj_att_tbl
, OE_SERVICE_UTIL.g_old_line_adj_att_tbl
, OE_SERVICE_UTIL.g_line_adj_assoc_tbl
, OE_SERVICE_UTIL.g_old_line_adj_assoc_tbl
, OE_SERVICE_UTIL.g_line_scredit_tbl
, OE_SERVICE_UTIL.g_old_line_scredit_tbl
, OE_SERVICE_UTIL.g_lot_serial_tbl
, OE_SERVICE_UTIL.g_old_lot_serial_tbl
, OE_SERVICE_UTIL.g_action_request_tbl);
oe_debug_pub.add( 'CSS: BEFORE CALLS UPDATE_NOTICE ',0.5 ) ; -- debug level 0.5 added for bug 13435459
ASO_ORDER_FEEDBACK_PUB.UPDATE_NOTICE(
p_api_version => 1.0
, p_init_msg_list => p_init_msg_list
, p_commit => l_commit
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_header_rec => OE_SERVICE_UTIL.g_header_rec
, p_old_header_rec => OE_SERVICE_UTIL.g_old_header_rec
, p_Header_Adj_tbl => OE_SERVICE_UTIL.g_header_adj_tbl
, p_old_Header_Adj_tbl => OE_SERVICE_UTIL.g_old_header_adj_tbl
, p_Header_price_Att_tbl => OE_SERVICE_UTIL.g_header_price_att_tbl
, p_old_Header_Price_Att_tbl => OE_SERVICE_UTIL.g_old_header_price_att_tbl
, p_Header_Adj_Att_tbl => OE_SERVICE_UTIL.g_header_adj_att_tbl
, p_old_Header_Adj_Att_tbl => OE_SERVICE_UTIL.g_old_header_adj_att_tbl
, p_Header_Adj_Assoc_tbl => OE_SERVICE_UTIL.g_header_adj_assoc_tbl
, p_old_Header_Adj_Assoc_tbl => OE_SERVICE_UTIL.g_old_header_adj_assoc_tbl
, p_Header_Scredit_tbl => OE_SERVICE_UTIL.g_header_scredit_tbl
, p_old_Header_Scredit_tbl => OE_SERVICE_UTIL.g_old_header_scredit_tbl
, p_line_tbl => OE_SERVICE_UTIL.g_line_tbl
, p_old_line_tbl => OE_SERVICE_UTIL.g_old_line_tbl
, p_Line_Adj_tbl => OE_SERVICE_UTIL.g_line_adj_tbl
, p_old_Line_Adj_tbl => OE_SERVICE_UTIL.g_old_line_adj_tbl
, p_Line_Price_Att_tbl => OE_SERVICE_UTIL.g_line_price_att_tbl
, p_old_Line_Price_Att_tbl => OE_SERVICE_UTIL.g_old_line_price_att_tbl
, p_Line_Adj_Att_tbl => OE_SERVICE_UTIL.g_line_adj_att_tbl
, p_old_Line_Adj_Att_tbl => OE_SERVICE_UTIL.g_old_line_adj_att_tbl
, p_Line_Adj_Assoc_tbl => OE_SERVICE_UTIL.g_line_adj_assoc_tbl
, p_old_Line_Adj_Assoc_tbl => OE_SERVICE_UTIL.g_old_line_adj_assoc_tbl
, p_Line_Scredit_tbl => OE_SERVICE_UTIL.g_line_scredit_tbl
, p_old_Line_Scredit_tbl => OE_SERVICE_UTIL.g_old_line_scredit_tbl
, p_Lot_Serial_tbl => OE_SERVICE_UTIL.g_lot_serial_tbl
, p_old_Lot_Serial_tbl => OE_SERVICE_UTIL.g_old_lot_serial_tbl
, p_action_request_tbl => OE_SERVICE_UTIL.g_action_request_tbl);
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'SERVICE_REFERENCE_TYPE_CODE');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT /* MOAC_SQL_CHANGE */ l.line_number
, l.shipment_number
, l.option_number
, l.component_number
, l.service_number
, l.ordered_quantity
, l.order_quantity_uom
, l.header_id
, l.line_id
, l.inventory_item_id
, l.blanket_number
, l.cust_po_number
, l.fulfilled_quantity --5699215
INTO l_line_number
, l_shipment_number
, l_option_number
, l_component_number
, l_service_number
, l_service_qty
, l_service_uom
, l_header_id
, l_line_id
, l_inventory_item_id
, l_blanket_number
, l_cust_po_number
, l_fulfilled_quantity --5699215
FROM oe_order_lines_all l, oe_order_headers h
WHERE h.order_number = p_x_line_rec.service_ref_order_number
AND l.line_number = p_x_line_rec.service_ref_line_number
AND l.shipment_number = p_x_line_rec.service_ref_shipment_number
AND NVL(l.option_number,0) = NVL(p_x_line_rec.service_ref_option_number, 0)
AND l.header_id = h.header_id
AND rownum < 2;
SELECT /* MOAC_SQL_CHANGE */ l.line_number
, l.shipment_number
, l.option_number
, l.component_number
, l.service_number
, l.ordered_quantity
, l.order_quantity_uom
, l.header_id
, l.line_id
, l.inventory_item_id
, l.fulfilled_quantity --5699215
INTO l_line_number
, l_shipment_number
, l_option_number
, l_component_number
, l_service_number
, l_service_qty
, l_service_uom
, l_header_id
, l_line_id
, l_inventory_item_id
, l_fulfilled_quantity --5699215
FROM oe_order_lines_all l, oe_order_headers h
WHERE h.order_number = p_x_line_rec.service_ref_order_number
AND l.line_number = p_x_line_rec.service_ref_line_number
AND l.shipment_number = p_x_line_rec.service_ref_shipment_number
AND NVL(l.option_number,0) = NVL(p_x_line_rec.service_ref_option_number, 0)
AND l.header_id = h.header_id
AND rownum < 2;
oe_debug_pub.add( 'IN SELECT USING REF LINEID# WITH BLANKET'
|| TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
SELECT l.line_number
, l.shipment_number
, l.option_number
, l.component_number
, l.service_number
, l.ordered_quantity
, l.order_quantity_uom
, l.header_id
, l.line_id
, l.inventory_item_id
, l.top_model_line_id -- 2331301
, NVL(m.serviceable_product_flag, 'N') -- 2331301
, l.blanket_number
, l.cust_po_number
, l.fulfilled_quantity --5699215
INTO l_line_number
, l_shipment_number
, l_option_number
, l_component_number
, l_service_number
, l_service_qty
, l_service_uom
, l_header_id
, l_line_id
, l_inventory_item_id
, l_top_model_id -- 2331301
, l_serviceable -- 2331301
, l_blanket_number
, l_cust_po_number
, l_fulfilled_quantity --5699215
FROM oe_order_lines l,
mtl_system_items m
WHERE l.line_id = p_x_line_rec.service_reference_line_id
AND l.inventory_item_id = m.inventory_item_id
AND m.organization_id = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'));
SELECT blanket_number into l_blanket_number
FROM oe_order_lines where line_id = l_top_model_id;
oe_debug_pub.add( 'IN SELECT USING REF LINEID# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
SELECT l.line_number
, l.shipment_number
, l.option_number
, l.component_number
, l.service_number
, l.ordered_quantity
, l.order_quantity_uom
, l.header_id
, l.line_id
, l.inventory_item_id
, l.top_model_line_id -- 2331301
, NVL(m.serviceable_product_flag, 'N') -- 2331301
, l.fulfilled_quantity --5699215
INTO l_line_number
, l_shipment_number
, l_option_number
, l_component_number
, l_service_number
, l_service_qty
, l_service_uom
, l_header_id
, l_line_id
, l_inventory_item_id
, l_top_model_id -- 2331301
, l_serviceable -- 2331301
, l_fulfilled_quantity --5699215
FROM oe_order_lines l,
mtl_system_items m
WHERE l.line_id = p_x_line_rec.service_reference_line_id
AND l.inventory_item_id = m.inventory_item_id
AND m.organization_id = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'));
oe_debug_pub.add( 'AFTER SELECT OF ORDER REF' ) ;
OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_ITEM');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
select nvl(max(service_number)+1,1)
into l_service_number
from oe_order_lines
where header_id = l_header_id
and line_number = l_line_number
and shipment_number = l_shipment_number
and nvl(option_number,0) = nvl(l_option_number,0)
and nvl(component_number,0) = nvl(l_component_number,0)
and item_type_code = 'SERVICE';
SELECT quantity, unit_of_measure_code
FROM cs_customer_products_rg_v
WHERE customer_product_id = :b1 AND account_id = :b2 AND rownum < 2 ';
SELECT quantity, unit_of_measure_code
FROM csi_instance_accts_rg_v
WHERE customer_product_id = :b1 AND account_id = :b2 AND rownum < 2 ';
SELECT quantity, unit_of_measure_code
FROM csi_instance_accts_rg_v
WHERE customer_product_id = :b1 ';
l_sql_stat := l_sql_stat ||' SELECT cust_account_id';
OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_ITEM');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_QUANTITY');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'SERVICE_REFERENCE_TYPE_CODE');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT l.line_number
, l.shipment_number
, l.option_number
, l.service_number
, l.ordered_quantity
, l.order_quantity_uom
, l.header_id
, l.line_id
, l.inventory_item_id
, l.top_model_line_id -- 2331301
, NVL(m.serviceable_product_flag, 'N')
, l.fulfilled_quantity --5699215
INTO l_line_number
, l_shipment_number
, l_option_number
, l_service_number
, l_service_qty
, l_service_uom
, l_header_id
, l_line_id
, l_inventory_item_id
, l_top_model_id -- 2331301
, l_serviceable
, l_fulfilled_quantity --5699215
FROM oe_order_lines l,
mtl_system_items m
WHERE l.line_id = p_line_rec.service_reference_line_id
AND l.inventory_item_id = m.inventory_item_id
AND m.organization_id = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'));
OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_ITEM');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_QUANTITY');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
select nvl(max(service_number)+1,1)
into l_service_number
from oe_order_lines
where header_id = l_header_id
and line_number = l_line_number
and shipment_number = l_shipment_number
and option_number = l_option_number
and item_type_code = 'SERVICE';
SELECT NVL(MAX(l.line_number)+1,1)
INTO l_line_number
FROM oe_order_lines l
WHERE l.header_id = p_line_rec.header_id;
SELECT quantity
, unit_of_measure_code
FROM cs_customer_products_rg_v
WHERE customer_product_id = p_line_rec.service_reference_line_id
AND rownum < 2';
SELECT quantity
, unit_of_measure_code
FROM csi_instance_accts_rg_v
WHERE customer_product_id = p_line_rec.service_reference_line_id
AND rownum < 2';
OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_ITEM');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_QUANTITY');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT /* MOAC_SQL_CHANGE */ l.line_id
INTO x_reference_line_id
FROM oe_order_lines_all l, oe_order_headers h
WHERE h.order_number = p_order_number
AND h.header_id = l.header_id
AND l.line_number = p_line_number
AND l.shipment_number = p_shipment_number
AND nvl(l.option_number, 0) = nvl(p_option_number, 0)
AND l.item_type_code <> 'SERVICE';
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CS_SYSTEMS_RG_V'
AND ROWNUM < 2;
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CSI_SYSTEMS_RG_V'
AND ROWNUM < 2;
SELECT system_id
FROM cs_systems_rg_v
WHERE customer_id = :l_customer_id
AND system = :l_system_number';
SELECT system_id
FROM csi_systems_rg_v
WHERE customer_id = :l_customer_id
AND system = :l_system_number';
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CS_SYSTEMS_RG_V'
AND ROWNUM < 2;
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CSI_SYSTEMS_RG_V'
AND ROWNUM < 2;
SELECT NAME
FROM cs_systems
WHERE system_id = :l_system_id'; /*commented for 4731582
SELECT NAME
FROM csi_systems_vl
WHERE system_id = :l_system_id';/*commented for 4731582
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CS_CUSTOMER_PRODUCTS_RG_V'
AND ROWNUM < 2;
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CSI_INSTANCE_ACCTS_RG_V'
AND ROWNUM < 2;
SELECT PRODUCT
FROM cs_customer_products_rg_v
WHERE customer_product_id = :l_customer_product_id';
SELECT PRODUCT
FROM csi_instance_accts_rg_v
WHERE customer_product_id = :l_customer_product_id';
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CS_CUSTOMER_PRODUCTS_RG_V'
AND ROWNUM < 2;
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CSI_INSTANCE_ACCTS_RG_V'
AND ROWNUM < 2;
SELECT ORIGINAL_ORDER_LINE_ID
FROM cs_customer_products_rg_v
WHERE customer_product_id = :l_customer_product_id
AND account_id = :l_customer_id';
SELECT ORIGINAL_ORDER_LINE_ID
FROM csi_instance_accts_rg_v
WHERE customer_product_id = :l_customer_product_id';
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CS_CUSTOMER_PRODUCTS_RG_V'
AND ROWNUM < 2;
SELECT 'Y'
INTO l_exists
FROM user_views
WHERE view_name = 'CSI_INSTANCE_ACCTS_RG_V'
AND ROWNUM < 2;
OE_SERVICE_UTIL.l_srv_cust_prod_tbl.DELETE; -- 2225343 end
'SELECT CUSTOMER_PRODUCT_ID, PRODUCT, PRODUCT_DESCRIPTION, REFERENCE_NUMBER, CURRENT_SERIAL_NUMBER
FROM cs_customer_products_rg_v
WHERE account_id = :l_customer_id' USING p_customer_id;
'SELECT CUSTOMER_PRODUCT_ID, PRODUCT, PRODUCT_DESCRIPTION, REFERENCE_NUMBER, CURRENT_SERIAL_NUMBER
FROM csi_instance_accts_rg_v
WHERE account_id = :l_customer_id' USING p_customer_id;
Select 'EXISTS' into
l_exists
from oe_order_lines_all
where service_reference_line_id = l_line_id;
PROCEDURE Update_Service_Lines
(p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_line_rec OE_Order_PUB.Line_Rec_Type;
SELECT line_id
FROM oe_order_lines
WHERE p_service_reference_line_id = service_reference_line_id
ORDER BY line_id;
p_x_line_tbl(i).operation= OE_GLOBALS.G_OPR_UPDATE) THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'IN OE_SERVICE_UTIL.UPDATE_SERVICE_LINES.ENTERING OE_SERVICE_UTIL.NEW_SERVICE_LINES' , 1 ) ;
OE_LINE_UTIL.Update_Row(p_line_rec => l_new_line_rec);
oe_debug_pub.add( 'EXITING OE_SERVICE_UTIL.UPDATE_SERVICE_LINES' , 1 ) ;
, 'Update_Service_Lines'
);
END Update_Service_Lines;
SELECT l.line_id
FROM oe_order_lines l
WHERE l.top_model_line_id = l_service_reference_line_id
AND l.item_type_code in ('INCLUDED','CLASS','OPTION')
AND exists (select null from mtl_system_items mtl where
mtl.inventory_item_id = l.inventory_item_id and
mtl.serviceable_product_flag = 'Y');
SELECT LINE_ID
INTO l_line_id
FROM OE_ORDER_LINES
WHERE INVENTORY_ITEM_ID = l_inventory_item_id
AND service_reference_line_id = child_line_id
AND item_type_code = 'SERVICE'
-- AND ordered_item IS NULL This AND commented for 2556516
AND service_reference_type_code = 'ORDER'
FOR UPDATE NOWAIT;
l_line_tbl(I).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl.DELETE;
oe_debug_pub.add( 'EXITING UPDATE_SERVICE_FOR_OPTIONS' , 1 ) ;
, 'UPDATE_SERVICE_FOR_OPTIONS'
);
SELECT 'Y'
INTO l_exists
FROM all_tables
where table_name='CSI_INSTALL_PARAMETERS';
SELECT freeze_flag
FROM csi_install_parameters
WHERE rownum = 1';
PROCEDURE Update_Service_Option_Numbers
( p_top_model_line_id IN NUMBER )
IS
CURSOR option_lines IS
SELECT line_id, option_number
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id;
oe_debug_pub.add( 'ENTERING OE_SERVICE_UTIL.UPDATE_SERVICE_OPTION_NUMBERS' , 2 ) ;
UPDATE oe_order_lines_all
SET option_number = l_option_numbers(i)
WHERE service_reference_line_id = l_line_ids(i)
AND service_reference_type_code = l_ref_type_code; -- For Bug 3087370
oe_debug_pub.add( 'LEAVING UPDATE_SERVICE_OPTION_NUMBERS' , 2 ) ;