[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 );
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 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';
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 ) ;
-- 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 ) ;
/* 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)
WHERE
top_model_line_id = p_x_line_rec.line_id
AND open_flag = 'Y';
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;