[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_x_line_tbl(I).operation = oe_globals.g_opr_update THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE CREATING SET : ' ||P_X_LINE_TBL ( I ) .LINE_ID ) ;
Procedure Update_Drop_Ship_Source(p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type) IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
UPDATE oe_drop_ship_sources
SET line_id = p_line_tbl(I).line_id
WHERE line_id = p_line_tbl(I).split_from_line_id;
End Update_Drop_Ship_Source;
SELECT ORDERED_QUANTITY
, HEADER_ID
, LINE_ID
FROM OE_ORDER_LINES_ALL
WHERE SERVICE_REFERENCE_LINE_ID = l_line_id
AND ITEM_TYPE_CODE = 'SERVICE'
AND SERVICE_REFERENCE_TYPE_CODE = 'ORDER'
AND OPEN_FLAG <> 'N' -- Bug 7555831 and 7555832
AND NVL(CANCELLED_FLAG,'N') <> 'Y';
OE_DEBUG_PUB.add('#5112495, select service reference type code ORDER only',1);
IF p_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE THEN
l_line_id := p_line_tbl(I).line_id;
IF p_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE THEN
l_ser_line_tbl(l_service_count).split_action_code := 'SPLIT';
SELECT NVL(MAX(SHIPMENT_NUMBER)+1,1)
INTO l_ship_number
FROM OE_ORDER_LINES
WHERE HEADER_ID = p_line_rec.header_id
AND LINE_NUMBER = p_line_rec.line_number;
SELECT shipment_number
INTO l_ship_number
FROM oe_order_lines
WHERE line_id = p_line_rec.top_model_line_id;
SELECT ORDERED_QUANTITY
, HEADER_ID
, LINE_ID
FROM OE_ORDER_LINES_ALL
WHERE TOP_MODEL_LINE_ID = l_line_id
AND LINE_ID <> l_line_id
AND NVL(SHIPPABLE_FLAG,'N')='Y';
SELECT ORDERED_QUANTITY
, HEADER_ID
, LINE_ID
FROM OE_ORDER_LINES_ALL
WHERE SERVICE_REFERENCE_LINE_ID = l_line_id
AND ITEM_TYPE_CODE = 'SERVICE'
AND SERVICE_REFERENCE_TYPE_CODE = 'ORDER'
AND OPEN_FLAG <> 'N' -- Bug 6710212
AND NVL(CANCELLED_FLAG,'N') <> 'Y';
l_ser_line_tbl(l_service_count).operation := oe_globals.g_opr_update;
l_insert_quantity NUMBER := 0;
l_update_quantity NUMBER := 0;
option_updated BOOLEAN := FALSE;
l_option_not_updated BOOLEAN := TRUE;
l_update_line_reqd BOOLEAN := TRUE;
SELECT ORDERED_QUANTITY,
HEADER_ID,
LINE_ID
FROM OE_ORDER_LINES_ALL
WHERE TOP_MODEL_LINE_ID = l_line_id
AND LINE_ID <> l_line_id
AND NVL(CANCELLED_FLAG,'N')<> 'Y'
ORDER BY LINE_ID;
l_parent_line.operation := oe_globals.g_opr_update;
option_updated := FALSE;
x_line_tbl(I).operation := oe_globals.g_opr_update;
option_updated := TRUE;
IF not option_updated THEN
l_option_count := l_option_count + 1;
l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
END IF; -- Option not updated
option_updated := false;
l_parent_line.operation := oe_globals.g_opr_update;
option_updated := FALSE;
l_insert_quantity := 0;
l_insert_quantity := p_line_tbl(I).shipped_quantity;
option_updated := true;
IF l_insert_quantity = 0 AND g_max_model > 0 THEN
l_insert_quantity := g_max_model *l_model_ratio;
l_update_quantity := l_option_line.ordered_quantity - g_min_model * l_model_ratio - l_insert_quantity ;
oe_debug_pub.add('Update Quantity : '||l_update_quantity,5) ;
oe_debug_pub.add('Insert Quantity : ' || l_insert_quantity,5);
IF l_update_quantity > 0 THEN
l_option_count := l_option_count + 1;
IF NOT option_updated AND g_max_model = 0 THEN
l_option_line_tbl(l_option_count) := l_option_line;
l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
END IF; -- Option updated or g max model
l_option_line_tbl(l_option_count).ordered_quantity := l_update_quantity;
END IF; -- Update Quantity
option_updated := false;
l_insert_quantity number := 0;
l_update_quantity number := 0;
option_updated BOOLEAN := FALSE;
l_option_not_updated boolean := TRUE;
l_update_line_reqd boolean := TRUE;
SELECT ORDERED_QUANTITY,
HEADER_ID,
LINE_ID
FROM OE_ORDER_LINES_ALL
WHERE TOP_MODEL_LINE_ID = l_line_id
AND LINE_ID <> l_line_id
AND NVL(CANCELLED_FLAG,'N') <> 'Y'
ORDER BY LINE_ID;
l_parent_line.operation := oe_globals.g_opr_update;
option_updated := FALSE;
x_line_tbl(I).operation := OE_GLOBALS.g_opr_update;
option_updated := TRUE;
IF NOT option_updated THEN
l_option_count := l_option_count + 1;
l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
END IF; -- Option not updated
option_updated := false;
option_updated := FALSE;
l_insert_quantity := 0;
l_insert_quantity := p_line_tbl(I).shipped_quantity;
option_updated := true;
l_insert_quantity := (g_max_model*l_model_ratio);
l_update_quantity := l_option_line.ordered_quantity - g_min_model * l_model_ratio - l_insert_quantity ;
oe_debug_pub.add( 'UPDATE QUANTITY : ' || L_UPDATE_QUANTITY ) ;
oe_debug_pub.add( 'INSERT QUANTITY : ' || L_INSERT_QUANTITY ) ;
IF l_update_quantity > 0 THEN
l_option_count := l_option_count + 1;
IF NOT option_updated AND g_max_model = 0 THEN
l_option_line_tbl(l_option_count) := l_option_line;
l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
END IF; -- Option updated or g max model
l_option_line_tbl(l_option_count).ordered_quantity := l_update_quantity;
END IF; -- Update Quantity
option_updated := false;
SELECT ORDERED_QUANTITY,
HEADER_ID,
LINE_ID
FROM OE_ORDER_LINES_ALL
WHERE ATO_LINE_ID = l_line_id
AND LINE_ID <> l_line_id
AND NVL(CANCELLED_FLAG,'N')<>'Y'
ORDER BY LINE_ID;
SELECT ORDERED_QUANTITY,
HEADER_ID,
LINE_ID
FROM OE_ORDER_LINES_ALL
WHERE TOP_MODEL_LINE_ID = l_line_id
AND LINE_ID <> l_line_id
AND NVL(CANCELLED_FLAG,'N') <> 'Y'
ORDER BY LINE_ID;
l_line_scredit_tbl.delete;
l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
l_split_line_tbl.delete;
l_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE
THEN
/* Added this condition to spuress the spliting of the line with zero quantiy.
Bug: 3318920
By: Srini
*/
IF l_line_tbl(I).ordered_quantity = 0
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'ORDERED QUANTITY IS INVALID with UPDATE OPER => '|| l_line_tbl(I).ordered_quantity ) ;
oe_debug_pub.add( 'SPLIT- NOT ALLOWED TO UPDATE WITH QUANTITY ZERO' ) ;
l_line_scredit_tbl.delete;
END LOOP; -- Loop for Insert on line table
-- Delete split line tbl for this line
g_split_line_tbl.delete;
END IF; -- Operation Update
-- Update Drop Ship Sources
--Update_Drop_Ship_Source(p_line_tbl => X_Line_Tbl);
Cursor C1 is SELECT SET_ID
FROM OE_LINE_SETS
WHERE LINE_ID = p_line_rec.split_from_line_id;
SELECT * FROM oe_line_split_details
WHERE line_id = l_init_line_id
AND request_id = FND_GLOBAL.CONC_REQUEST_ID
FOR UPDATE NOWAIT;
select org_id
into l_org_id
from oe_order_lines_all
where line_id=l_init_line_id;
select count(*) into l_count
from oe_line_split_details where line_id = l_init_line_id
AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT sum(ORDERED_QUANTITY), sum(ORDERED_QUANTITY2)
INTO l_ord_qty_sum, l_ord_qty2_sum
FROM oe_line_split_details
WHERE line_id = l_init_line_id
AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT ordered_quantity, ordered_quantity2
INTO l_orig_ord_qty, l_orig_ord_qty2
FROM oe_order_lines_all
WHERE open_flag = 'Y'
AND line_id = l_init_line_id;
l_x_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl.delete;
l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(l_rec_count).operation := OE_GLOBALS.G_OPR_UPDATE;
l_x_Header_Adj_tbl.DELETE;
l_x_header_price_att_tbl.DELETE;
l_x_Header_Adj_att_tbl.DELETE;
l_x_Header_Adj_Assoc_tbl.DELETE;
l_x_Header_Scredit_tbl.DELETE;
l_x_Line_Adj_tbl.DELETE;
l_x_Line_Price_att_tbl.DELETE;
l_x_Line_Adj_att_tbl.DELETE;
l_x_Line_Adj_Assoc_tbl.DELETE;
l_x_Line_Scredit_tbl.DELETE;
l_x_lot_serial_tbl.DELETE;
DELETE FROM oe_line_split_details
WHERE line_id = l_init_line_id
AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
DELETE FROM oe_line_split_details
WHERE line_id = l_init_line_id
AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
PROCEDURE Bulk_Insert (p_line_conc_tbl IN Split_Line_Tbl_Type) is
begin
oe_debug_pub.add('entering oe_split_util.bulk_insert');
INSERT INTO OE_LINE_SPLIT_DETAILS
VALUES p_line_conc_tbl(j);
oe_debug_pub.add('failed in bulk insert API'||sqlerrm);
end Bulk_Insert;