[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
into lcount
from dual
where exists (select 1
from oe_line_sets
where set_id = p_set_id
and line_id = p_line_id);
SELECT Enforce_Ship_Set_And_Smc
INTO l_shipset_enforce
FROM Wsh_Shipping_Parameters
WHERE Organization_Id = p_line_rec.ship_from_org_Id;
SELECT count(*)
INTO l_ship_set
FROM Wsh_Delivery_Details
WHERE Ship_Set_Id = p_line_rec.ship_set_id
AND Source_Code = 'OE'
AND Source_Header_Id = p_line_rec.header_id
AND Released_Status In ('S','Y','C')
AND ROWNUM = 1; -- 3229707 Removed 'B' from Released_Status check
SELECT count(*)
INTO l_ship_set
FROM wsh_delivery_details wdd
WHERE wdd.ship_set_id = p_line_rec.ship_set_id
AND wdd.source_code = 'OE'
AND wdd.source_header_id = p_line_rec.header_id
AND ((wdd.released_status = 'C')
OR EXISTS (select wda.delivery_detail_id
FROM wsh_delivery_assignments wda, wsh_new_deliveries wnd
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.status_code in ('CO', 'IT', 'CL', 'SA')))
AND rownum = 1;
SELECT SET_NAME
INTO l_set_name
FROM OE_SETS
WHERE set_id = p_line_rec.ship_set_id;
Select header_id
Into l_header_id
From oe_order_lines_all
Where line_id = p_line_rec.service_reference_line_id;
Select line_id from
oe_order_lines_all where
top_model_line_id = p_line_id and
--item_type_code = 'INCLUDED' and
nvl(cancelled_flag,'N') <> 'Y' and
nvl(model_remnant_flag,'N') <> 'Y' and
line_id not in
(select line_id from oe_line_sets where
set_id = p_set_id );
SELECT set_status
INTO l_set_s
FROM oe_sets
WHERE set_id = p_set_id
FOR UPDATE NOWAIT;
Insert into oe_line_sets(
Line_id,
Set_id,
SYSTEM_REQUIRED_FLAG )
Values
(p_line_id,
p_set_id,
'Y');
Insert into oe_line_sets(Line_id,
Set_id,
SYSTEM_REQUIRED_FLAG )
Values (c1rec.line_id,
p_set_id,
'Y');
Procedure Delete_Fulfillment_Set(p_line_id NUMBER,
p_set_id NUMBER) IS
l_return_status varchar2(30);
oe_debug_pub.add( 'DELETE FULLFILLMENT SET' ) ;
/* The next check is redundant and harmful if a child line is being deleted from
set. The check has already been done in process_sets(). Commented for 2525203.
-- See if this is already fulfilled . if fulfilled raise error
oe_line_util.query_row(p_line_id => p_line_id,
x_line_rec => l_line_rec);
/*Delete oe_line_sets
Where line_id = p_line_id
and set_id = p_set_id;*/
Delete oe_line_sets
Where line_id = p_line_id
and set_id = p_set_id;
oe_debug_pub.add( 'EXIT - DELETE FULLFILLMENT SET' ) ;
'Delete Fulfillment Set'
);
End Delete_Fulfillment_Set;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Into_Set';
Select Ordered_quantity,
header_id,
Line_id
from
oe_order_lines_all where
top_model_line_id = l_line_id and
--and line_id <> l_line_id and
nvl(cancelled_flag,'N') <> 'Y' and
nvl(model_remnant_flag,'N') <> 'Y'
ORDER BY arrival_set_id,ship_set_id,line_number,shipment_number,nvl(option_number,-1);
l_line_tbl(l_count).operation := oe_globals.g_opr_update;
l_old_line_tbl.delete;
g_old_line_tbl.delete;
l_line_tbl.delete;
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' and
nvl(model_remnant_flag,'N') <> 'Y'
order by line_id;
p_x_line_tbl(l_count).operation := oe_globals.g_opr_update;
Procedure Insert_Into_arrival_Set
(p_Set_request_tbl oe_order_pub.Request_Tbl_Type,
p_Push_Set_Date IN VARCHAR2 := FND_API.G_FALSE,
X_Return_Status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_line_rec OE_ORDER_PUB.line_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Into_Set';
Select set_id from
oe_sets where
set_type = 'SHIP_SET'
and header_id = l_header_id
and ship_from_org_id = l_ship_from_org_id
and ship_to_org_id = l_ship_to_org_id
and trunc(schedule_ship_date) = trunc(l_Schedule_Ship_Date)
and nvl(set_status,'X') <> 'C'
;
Select set_id from
oe_sets where
set_type = 'ARRIVAL_SET' and
header_id = l_header_id
and ship_to_org_id = l_ship_to_org_id
and trunc(schedule_arrival_date) = trunc(l_Schedule_arrival_Date)
and nvl(set_status,'X') <> 'C';
select Max(to_number(set_name)) from
oe_sets
where
set_status = 'T'
and header_id = l_header_id and
set_type = l_set_type;
Select Schedule_ship_date,
Ship_from_org_id,
ship_to_org_id,
schedule_arrival_date,
ship_set_id,
arrival_set_id
from
oe_order_lines_all
where
line_id = l_top_model_line_id ;
select Max(to_number(set_name)) from
oe_sets
where
header_id = l_header_id and
set_type = l_set_type and
set_name = lsettempname;
oe_debug_pub.add( 'INSERT_INTO ARRIVAL SETSS' , 1 ) ;
l_temp_line_tbl.delete;
g_old_line_tbl.delete;
l_sch_line_tbl.delete(l_sch_count) ;
g_old_line_tbl.delete;
l_sch_line_tbl.delete;
l_temp_line_tbl.delete;
l_sch_line_tbl.delete;
l_temp_line_tbl(K).operation := oe_globals.g_opr_update;
l_line_tbl.delete;
g_auto_set_tbl.delete;
oe_debug_pub.add( ' EXIT INSERT INTO SETS' , 1 ) ;
g_auto_set_tbl.delete;
, 'Insert_Into_Set'
);
End Insert_Into_arrival_Set;
( p_selected_line_tbl IN OE_GLOBALS.Selected_Record_Tbl, --(R12.MOAC)
p_record_count IN NUMBER,
p_set_name IN VARCHAR2,
p_set_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_operation IN VARCHAR2,
p_header_id IN VARCHAR2 := FND_API.G_MISS_CHAR,
x_Set_Id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_num_of_records NUMBER := p_record_count;
FOR I IN 1..p_selected_line_tbl.COUNT LOOP
l_line_id := p_selected_line_tbl(I).id1;
l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT set_id
INTO x_set_id
FROM OE_SETS
where set_name = p_set_name
and set_type = p_set_type and
header_id = p_header_id ;
SELECT 'Y'
INTO row_exists
FROM OE_SETS
WHERE set_id = p_set_id
and header_id = nvl(l_header_id,header_id);
SELECT OE_SETS_S.NEXTVAL
INTO l_set_id
FROM DUAL;
INSERT INTO OE_SETS(
SET_ID
, SET_NAME
, SET_TYPE
, Header_Id
, Ship_from_org_id
, Ship_to_org_id
, Schedule_Ship_Date
, Schedule_Arrival_Date
, Freight_Carrier_Code
, Shipping_Method_Code
, Shipment_priority_code
, Set_Status
, CREATED_BY
,CREATION_DATE
,UPDATE_DATE
,UPDAtED_BY
)
VALUES(
l_set_id
, p_set_name
, p_Set_type
, p_Header_Id
, p_Ship_from_org_id
, p_Ship_to_org_id
, p_Schedule_Ship_Date
, p_Schedule_Arrival_Date
, p_Freight_Carrier_Code
, p_Shipping_Method_Code
,p_shipment_priority_code
, lcustpref
,1
,sysdate
,sysdate
,1001
);
oe_debug_pub.add( 'AFTERINSERT ' , 1 ) ;
Procedure Insert_Into_Set
(p_Set_request_tbl oe_order_pub.Request_Tbl_Type,
p_Push_Set_Date IN VARCHAR2 := FND_API.G_FALSE,
X_Return_Status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_line_rec OE_ORDER_PUB.line_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Into_Set';
Select set_id from
oe_sets where
set_type = 'SHIP_SET'
and header_id = l_header_id
and ship_from_org_id = l_ship_from_org_id
and ship_to_org_id = l_ship_to_org_id
and trunc(schedule_ship_date) = trunc(l_Schedule_Ship_Date)
and nvl(set_status,'X') <> 'C'
;
Select set_id from
oe_sets where
set_type = 'ARRIVAL_SET' and
header_id = l_header_id
and ship_to_org_id = l_ship_to_org_id
and trunc(schedule_arrival_date) = trunc(l_Schedule_arrival_Date)
and nvl(set_status,'X') <> 'C';
select Max(to_number(set_name)) from
oe_sets
where
set_status = 'T'
and header_id = l_header_id and
set_type = l_set_type;
Select Schedule_ship_date,
Ship_from_org_id,
ship_to_org_id,
schedule_arrival_date
from
oe_order_lines_all
where
line_id = l_top_model_line_id ;
select Max(to_number(set_name)) from
oe_sets
where
header_id = l_header_id and
set_type = l_set_type and
set_name = lsettempname;
select set_id,
Schedule_ship_date,
Ship_from_org_id,
ship_to_org_id
from
oe_sets
where
header_id = l_header_id and
set_type = 'SHIP_SET' and
set_status = 'T';
oe_debug_pub.add( 'INSERT_INTO SETSS' , 1 ) ;
Insert_Into_arrival_Set
(p_Set_request_tbl => p_Set_request_tbl ,
X_Return_Status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_temp_line_tbl.delete;
g_old_line_tbl.delete;
l_sch_line_tbl.delete(l_sch_count) ;
g_old_line_tbl.delete;
l_temp_line_tbl.delete;
l_temp_line_tbl(K).operation := oe_globals.g_opr_update;
l_line_tbl.delete;
g_auto_set_tbl.delete;
oe_debug_pub.add( ' EXIT INSERT INTO SETS' , 1 ) ;
g_auto_set_tbl.delete;
, 'Insert_Into_Set'
);
End Insert_Into_Set;
Procedure Update_Set
(p_Set_Id IN NUMBER,
p_Ship_From_Org_Id IN NUMBER := FND_API.G_MISS_NUM,
p_Ship_To_Org_Id IN NUMBER := FND_API.G_MISS_NUM,
p_Schedule_Ship_Date IN DATE := FND_API.G_MISS_DATE,
p_Schedule_Arrival_Date IN DATE := FND_API.G_MISS_DATE,
p_Freight_Carrier_Code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_Shipping_Method_Code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_shipment_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
X_Return_Status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_set_rec OE_ORDER_CACHE.set_rec_type;
UPDATE OE_SETS SET
ship_from_org_id = l_ship_from_org_id,
ship_to_org_id = l_ship_to_org_id,
Schedule_Arrival_Date = l_Schedule_Arrival_Date,
Schedule_Ship_Date = l_Schedule_Ship_Date,
Freight_Carrier_Code = l_Freight_Carrier_Code ,
Shipping_Method_Code = l_Shipping_Method_Code,
shipment_priority_code = l_shipment_priority_code
WHERE SET_ID = P_SET_ID;
, 'Update_set'
);
End Update_Set;
( p_selected_line_tbl IN OE_GLOBALS.Selected_Record_Tbl, -- R12.MOAC
p_record_count IN NUMBER,
p_set_name IN VARCHAR2,
p_set_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_operation IN VARCHAR2,
p_header_id IN VARCHAR2 := FND_API.G_MISS_CHAR,
x_Set_Id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_num_of_records NUMBER := p_record_count;
(p_selected_line_tbl => p_selected_line_tbl,
p_record_count => p_record_count,
p_set_name => p_set_name,
p_set_type => p_set_type,
p_operation => p_operation,
p_header_id => p_header_id ,
x_Set_Id => x_set_id ,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
FOR K IN 1..p_selected_line_tbl.count LOOP
l_line_id := p_selected_line_tbl(K).id1;
l_line_opt_tbl.delete;
l_line_opt_tbl.delete;
Delete_Fulfillment_Set
(p_line_id => l_line_opt_tbl(optrec).line_id,
p_set_id => l_Set_id);
l_line_opt_tbl.delete;
Delete_Fulfillment_Set
(p_line_id => l_line_id,
p_set_id => l_Set_id);
l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_opt_temp_tbl.delete;
g_old_line_tbl.delete;
g_old_line_tbl.delete;
l_line_opt_tbl.delete;
SELECT arrival_set_id INTO
x_arrival_set_id
FROM OE_ORDER_LINES_ALL WHERE
header_id = p_header_id AND
ship_set_id = p_ship_set_id AND
arrival_set_id IS NOT NULL AND
rownum = 1;
Procedure Update_Options(p_ato_line_id IN NUMBER := FND_API.G_MISS_NUM,
p_config_line_id IN NUMBER := FND_API.G_MISS_NUM,
p_set_id IN NUMBER,
p_set_type IN VARCHAR2 )IS
stmt_str varchar2(2000);
stmt_str := 'UPDATE OE_ORDER_LINES_ALL ' ||
'SET ' || column1 || ' = ' || to_char(p_set_id) ||
'WHERE ' || column2 || ' = ' ||to_char(parent_line_id);
END Update_Options;
ELSIF p_x_line_rec.operation = oe_globals.g_opr_update THEN
g_set_tbl(I).line_id := p_x_line_rec.line_id;
ELSIF p_x_line_rec.operation = oe_globals.g_opr_update THEN
g_set_tbl(I).line_id := p_x_line_rec.line_id;
ELSIF p_x_line_rec.operation = oe_globals.g_opr_update THEN
g_set_tbl(I).line_id := p_x_line_rec.line_id;
ELSIF p_x_line_rec.operation = oe_globals.g_opr_update THEN
g_set_tbl(I).line_id := p_x_line_rec.line_id;
SELECT line_id
FROM oe_order_lines
WHERE header_id = l_header_id
AND (ship_set_id = p_set_id
OR arrival_set_id = p_set_id)
ORDER BY line_number,shipment_number,nvl(option_number,-1);
Select header_id
Into l_header_id
From Oe_sets
Where set_id = p_set_id;
SELECT OE_SETS_S.NEXTVAL
INTO l_set_id
FROM DUAL;
INSERT INTO OE_SETS(
SET_ID
, SET_NAME
, SET_TYPE
, Header_Id
, inventory_item_id
, ordered_quantity_uom
, line_type_id
, Ship_tolerance_above
, ship_tolerance_below
, CREATED_BY
,CREATION_DATE
,UPDATE_DATE
,UPDATED_BY
)
VALUES(
l_set_id
, to_char(p_x_line_rec.line_id)
, 'SPLIT'
, p_x_line_rec.Header_Id
, p_x_line_rec.inventory_item_id
, p_x_line_rec.order_quantity_uom
, p_x_line_rec.line_type_id
, p_x_line_rec.Ship_tolerance_above
, p_x_line_rec.ship_tolerance_below
,1
,sysdate
,sysdate
,1001
);
oe_debug_pub.add( 'AFTERINSERT'||TO_CHAR ( L_SET_ID ) , 1 ) ;
oe_debug_pub.add( 'AFTERINSERT ' , 1 ) ;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Into_Set';
--g_old_line_tbl.delete;
Update oe_order_lines_all
set ship_set_id = l_set_id where
top_model_line_id = l_line_tbl(K).line_id and
item_type_code = 'INCLUDED';
Update oe_order_lines_all
set arrival_set_id = l_set_id where
top_model_line_id = l_line_tbl(K).line_id and
item_type_code = 'INCLUDED';
l_line_tbl(K).operation := oe_globals.g_opr_update;
l_line_tbl.delete;
g_old_line_tbl.delete;
g_set_tbl.delete;
g_set_opt_tbl.delete;
Select os.set_name from
oe_sets os,
oe_line_sets ls
where
ls.line_id = p_line_id and
os.set_id = ls.set_id;
Delete oe_line_sets
where line_id = p_line_id;
select Max(to_number(set_name))
from oe_sets
where set_status = 'T'
and header_id = p_x_line_rec.header_id and
set_type = l_set_pref_type;
select Max(to_number(set_name))
from oe_sets
where header_id = p_x_line_rec.header_id
and set_type = l_set_pref_type
and set_name = lsettempname;
select set_id
from oe_sets
where header_id = p_x_line_rec.header_id
and set_type = l_set_pref_type
and set_status = 'T';
select set_id
from oe_sets, oe_order_lines_all
where line_id = p_x_line_rec.line_id
and set_type = l_set_pref_type
and set_status = 'T'
AND set_id = Nvl(ship_set_id, arrival_set_id);
select set_id
from oe_sets
where header_id = p_x_line_rec.header_id
and set_type = 'SHIP_SET'
and set_status = 'T'
AND Nvl(ship_from_org_id,p_x_line_rec.ship_from_org_id) = p_x_line_rec.ship_from_org_id;
Select line_id, shipping_interfaced_flag
from oe_order_lines_all
where top_model_line_id = p_x_line_rec.line_id
and open_flag = 'Y';
select set_name
from oe_sets
where header_id = p_x_line_rec.header_id
and set_type = 'FULFILLMENT_SET'
and set_status = 'T';
select set_name
from oe_sets
where header_id = p_x_line_rec.header_id
and set_type = l_set_pref_type;
select set_id
from oe_sets
where header_id = p_x_line_rec.header_id
and set_type = l_set_pref_type
and set_status = 'T'
and set_name = nvl(p_x_line_rec.arrival_set,p_x_line_rec.ship_set);
select line_id,ship_set_id,arrival_set_id
from oe_order_lines_all
where top_model_line_id=p_x_line_rec.line_id
and line_id<>p_x_line_rec.line_id
and open_flag='Y';
SELECT inventory_item_id, ship_from_org_id, item_type_code,
line_id,top_model_line_id,source_document_type_id, line_number,
shipment_number, option_number, component_number, service_number,
line_category_code --4241385
FROM oe_order_lines_all
WHERE ship_set_id = p_x_line_rec.ship_set_id;
select set_id
from oe_sets, oe_order_lines_all
where line_id = p_x_line_rec.line_id
and set_status = 'A'
AND set_id = nvl(ship_set_id,arrival_set_id); --Bug 13691678
p_x_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE) or
(p_x_line_rec.operation = OE_GLOBALS.G_OPR_CREATE and
p_x_line_rec.split_from_line_id is NOT NULL)) then
IF ((p_x_line_rec.arrival_set is not null AND
p_x_line_rec.arrival_set <> FND_API.G_MISS_CHAR) OR
(p_x_line_rec.arrival_set_id is not null AND
p_x_line_rec.arrival_set_id <> FND_API.G_MISS_NUM))
AND ((p_x_line_rec.ship_set is not null AND
p_x_line_rec.ship_set <> FND_API.G_MISS_CHAR) OR
(p_x_line_rec.ship_set_id is not null AND
p_x_line_rec.ship_set_id <> FND_API.G_MISS_NUM))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID SER OPR' , 2 ) ;
OR (p_x_line_rec.operation = oe_globals.g_opr_update --2843738
AND NOT OE_Globals.Equal(p_x_line_rec.ship_from_org_id, p_old_line_rec.ship_from_org_id)) -- 13706641 and 13788287
/*OR (OE_Quote_Util.G_COMPLETE_NEG = 'Y'
AND NOT OE_GLOBALS.EQUAL(p_x_line_rec.transaction_phase_code
,p_old_line_rec.transaction_phase_code)
)-- Comented for 2843738 */
THEN
OE_Order_Cache.Load_Order_Header(p_x_line_rec.header_id);
select Nvl(ship_set_id,arrival_set_id)
into l_set_id
from oe_order_lines_all
where line_id= p_x_line_rec.top_model_line_id;
select Min(set_id) INTO l_set_id from oe_sets
where header_id = p_x_line_rec.header_id
AND set_status = 'T'
AND not exists (select line_id FROM oe_order_lines_all
WHERE ship_set_id = set_id);
SELECT ol1.ship_set_id INTO l_set_id
FROM oe_order_lines_all ol1
WHERE ol1.line_id = p_x_line_rec.line_id
AND NOT EXISTS ( SELECT 1 FROM oe_order_lines_all ol2
WHERE ol2.ship_set_id = ol1.ship_set_id
AND Nvl(ol2.top_model_line_id,ol2.line_id) <> ol1.line_id);
-- Create or Update based on the flags set above
/* First we will create the set if l_create_flag is set to 'Y' */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CREATE FLAG IS - ' || L_CREATE_SET , 1 ) ;
SELECT ship_from_org_id , schedule_ship_date ,shipping_method_code
, set_name
INTO l_ship_from_org_id , l_schedule_ship_Date,l_shipping_method_code
, l_set_name_dsp
FROM oe_sets
WHERE set_id=p_x_line_rec.ship_set_id;
SELECT ship_from_org_id , schedule_ship_date, set_id ,shipping_method_code
INTO l_ship_from_org_id , l_schedule_ship_Date , l_ship_set_id ,
l_shipping_method_code
FROM oe_sets
WHERE set_name = p_x_line_rec.ship_set
and set_type = 'SHIP_SET'
and header_id = p_x_line_rec.header_id ;
SELECT 1
INTO l_validate_combinition
FROM mtl_system_items_b msi,
org_organization_definitions org
WHERE msi.inventory_item_id= i.inventory_item_id
AND org.organization_id=msi.organization_id
AND sysdate<=nvl(org.disable_date,sysdate)
AND org.organization_id=p_x_line_rec.ship_from_org_id
AND rownum=1 ;
/* if the new warehouse is valid on all the lines in the set, then update
the new warehouse on all the lines and also update the set with the new
warehouse information*/
UPDATE oe_order_lines_all
SET ship_from_org_id=p_x_line_rec.ship_from_org_id
WHERE ship_set_id= p_x_line_rec.ship_set_id
AND open_flag='Y';
Update_Set
(p_Set_Id => p_x_line_rec.ship_set_id,
p_Ship_From_Org_Id => p_x_line_rec.ship_from_org_id,
p_Ship_To_Org_Id => p_x_line_rec.Ship_To_Org_Id,
p_Schedule_Ship_Date => p_x_line_rec.Schedule_Ship_Date,
p_Schedule_Arrival_Date => p_x_line_rec.Schedule_Arrival_Date,
p_Freight_Carrier_Code => p_x_line_rec.Freight_Carrier_Code,
p_Shipping_Method_Code => p_x_line_rec.Shipping_Method_Code,
p_shipment_priority_code => p_x_line_rec.shipment_priority_code,
X_Return_Status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT 1
INTO l_validate_combinition
FROM OE_SHIP_METHODS_V
WHERE lookup_code = p_x_line_rec.shipping_method_code
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
AND ROWNUM = 1;
/* if the new warehouse is valid on all the lines in the set, then update
the new warehouse on all the lines and also update the set with the new
warehouse information*/
UPDATE oe_order_lines_all
SET shipping_method_code=p_x_line_rec.shipping_method_code
WHERE ship_set_id= p_x_line_rec.ship_set_id
AND open_flag='Y';
Update_Set
(p_Set_Id => p_x_line_rec.ship_set_id,
p_Ship_From_Org_Id => p_x_line_rec.ship_from_org_id,
p_Ship_To_Org_Id => p_x_line_rec.Ship_To_Org_Id,
p_Schedule_Ship_Date => p_x_line_rec.Schedule_Ship_Date,
p_Schedule_Arrival_Date => p_x_line_rec.Schedule_Arrival_Date,
p_Freight_Carrier_Code => p_x_line_rec.Freight_Carrier_Code,
p_Shipping_Method_Code => p_x_line_rec.Shipping_Method_Code,
p_shipment_priority_code => p_x_line_rec.shipment_priority_code,
X_Return_Status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
/* Update set id on the children of the top model */
/*Changes for bug 6719457 start*/
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'Cascade flag is yes',1);
select ol.arrival_set_id
into l_old_set_id
from oe_order_lines_all ol
where ol.top_model_line_id = p_x_line_rec.line_id
and ol.line_id<>p_x_line_rec.line_id
and rownum=1;
select distinct(ol.ship_set_id)
into l_old_set_id
from oe_order_lines_all ol
where ol.top_model_line_id = p_x_line_rec.line_id
and ol.line_id<>p_x_line_rec.line_id
and rownum=1;
oe_debug_pub.add( 'After selecting, old set id is:'||l_old_set_id,1);
Update oe_order_lines_all l
set
arrival_set_id =
decode(l_Set_type , 'ARRIVAL_SET', l_set_id,l.arrival_set_id),
ship_set_id =
decode(l_Set_type , 'SHIP_SET', l_set_id,l.ship_set_id),
ship_from_org_id = p_x_line_rec.ship_from_org_id -- 2843738
WHERE
top_model_line_id = p_x_line_rec.line_id
AND open_flag = 'Y'
and l.item_type_code<>'SERVICE'; -- added for bug 8369694
oe_debug_pub.add( 'UPDATE SHIPPING : CHILDREN OF MODEL FOR SETS ' || TO_CHAR ( OPTIONREC.LINE_ID ) , 1 ) ;
p_request_type => OE_GLOBALS.G_UPDATE_SHIPPING,
p_request_unique_key1 => OE_GLOBALS.G_OPR_UPDATE,
p_param1 => FND_API.G_TRUE,
x_return_status => l_return_status);
end;-- Begin of the update
PROCEDURE: Delete_Set
DESCRIPTION:This api will delete set record from oe_sets table if
the set is not associated with any line.
*/
Procedure Delete_Set(p_request_rec IN OE_ORDER_PUB.request_rec_type,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_dummy NUMBER;
oe_debug_pub.add( 'ENTERING DELETE SET' , 3 ) ;
SELECT 1
INTO l_dummy
FROM oe_order_lines_all
WHERE header_id=p_request_rec.param2
AND ship_set_id =p_request_rec.param1
AND ROWNUM =1;
SELECT 1
INTO l_dummy
FROM oe_order_lines_all
WHERE header_id=p_request_rec.param2
AND arrival_set_id =p_request_rec.param1
AND ROWNUM =1;
DELETE FROM OE_SETS
WHERE set_id = p_request_rec.param1;
oe_debug_pub.add( 'IN OTHERS EXCEPTION OF DELETE SET ' , 3 ) ;
oe_debug_pub.add( 'EXITING DELETE SET' , 3 ) ;
, 'Delete_Set'
);
END Delete_Set;
SELECT line_type_id INTO l_line_type_id
FROM OE_ORDER_LINES_ALL
WHERE header_id = p_header_id
AND ship_set_id = p_ship_set_id
AND line_id <> p_line_id
AND rownum=1;
SELECT line_type_id INTO l_line_type_id
FROM OE_ORDER_LINES_ALL
WHERE header_id = p_header_id
AND arrival_set_id = p_arrival_set_id
AND line_id <> p_line_id
AND rownum=1;