[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,x_direct_update OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
PROCEDURE Handle_Direct_Update
( p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
,p_old_line_rec IN OE_ORDER_PUB.line_rec_type
,p_caller IN VARCHAR2);
6.ato_delete_flag : in case of deletes of ato options.
g)
Make sure that for smc, the l_res_change_flag is not
overwritten by some other rescheduling change.
pto_ato_nonui can not get overwritten by a value other
that Y if once set to Y becasue all records will
satisfy the condition whcih sets it.
e) if the caller is EXTERNAL i.e wf, the action
can be only SCHEDULE.
Changes have been made to pass override flag to schedule_nonsmc and
schedule_ato delayed request
----------------------------------------------------------*/
PROCEDURE Log_Config_Sch_Request
( p_line_rec IN OE_Order_PUB.Line_Rec_Type
,p_old_line_rec IN OE_Order_PUB.Line_Rec_Type
,p_sch_action IN VARCHAR2
,p_caller IN VARCHAR2 := OE_SCHEDULE_UTIL.SCH_INTERNAL
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_sch_action VARCHAR2(30);
SELECT schedule_status_code, ship_from_org_id,
demand_class_code, schedule_ship_date,
schedule_arrival_date, request_date
INTO l_model_sch_status_code, l_model_ship_from,
l_model_demand_class, l_model_ship_date,
l_model_arrival_date, l_model_request_date
FROM oe_order_lines
WHERE line_id = p_line_rec.top_model_line_id;
,p_delete => FND_API.G_TRUE
,x_return_status => x_return_status );
Delete_Attribute_Changes
(p_entity_id => p_line_rec.top_model_line_id);
PROCEDURE Delete_Attribute_Changes
This procedure can be used to clean up the global table
where we store the reservations related informtion.
Use in the exceptions handlers of log_config_sch_requests
and schedule_smc, schedule_nonsmc.
------------------------------------------------------------*/
PROCEDURE Delete_Attribute_Changes
(p_entity_id NUMBER := -1)
IS
I NUMBER;
oe_debug_pub.add( 'ENTERING DELETE_ATTRIBUTE_CHANGES '|| P_ENTITY_ID , 3 ) ;
OE_Reservations_Tbl.DELETE;
OE_Reservations_Tbl.DELETE(I);
oe_debug_pub.add( 'DELETE_ATTRIBUTE_CHANGES ERROR '|| SQLERRM , 1 ) ;
END Delete_Attribute_Changes;
the insert itself can fail if sch fails. If larest_acc
is present, all lines will get pushed. Let us not
complicate the code for not so common case.
the action itself will be made RESCHEDULE in above case.
latest_acceptable_date if null, mrp can not give nay dates
other than passed.
-------------------------------------------------------------*/
PROCEDURE Schedule_SMC
( p_request_rec IN OE_Order_Pub.Request_Rec_Type
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_count NUMBER := 0;
Delete_Attribute_Changes
(p_entity_id => p_request_rec.entity_id);
IF p_request_rec.param12 = 'DELETE' THEN
IF p_request_rec.param13 is not null
OR p_request_rec.param14 is not null THEN
IF Check_For_Request
(p_set_id => nvl(p_request_rec.param13,p_request_rec.param14),
p_ato_line_id => p_request_rec.entity_id)
THEN
l_request := 'NONE';
END IF; -- Delete
Delete_Attribute_Changes
(p_entity_id => p_request_tbl(1).entity_id);
b) Current major callers are schedule_multi_selected_lines
and schedule_order APIs and schedule_smc, schedule_ato,
schedule_nonsmc APIs, schedule_sets.
c) The reserved_quantity column should be populated
on the line records by the caller.
reserve_group API is smart enough to look at the sch level,
time fence, shippable_flag, user enterd values etc.
d) The call to save_sch_attribs will try to save lines call
in some cases,
ex: ordered_qty changes and sch. success
new lines addtion to a scheduled grp.
e) the p_partial and p_partial_set parameters are used for
ui and sets caller.
f) the call to reserve_group is moved after the
process_order call, because of the inv change,
they need correct warehouse on the line.
---------------------------------------------------------*/
PROCEDURE Process_Group
( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
,p_old_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
,p_sch_action IN VARCHAR2
,p_caller IN VARCHAR2 := 'X'
,p_handle_unreserve IN VARCHAR2 := 'N'
,p_partial IN BOOLEAN := FALSE
,p_partial_set IN BOOLEAN := FALSE
,p_part_of_set IN VARCHAR2 DEFAULT 'N' -- 4405004
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_msg_count NUMBER;
Delete_Attribute_Changes
(p_entity_id => nvl(nvl(p_x_line_tbl(1).arrival_set_id,
p_x_line_tbl(1).ship_set_id),
p_x_line_tbl(1).top_model_line_id));
OE_SCHEDULE_UTIL.OE_inactive_demand_tbl.DELETE
(l_line_id_mod) ; --7827737
l_rsv_update BOOLEAN := FALSE;
l_rsv_update := TRUE;
,p_rsv_update => l_rsv_update
,x_return_status => l_return_status);
OE_Reservations_Tbl.DELETE(l_line_id_mod); --7827737
p_x_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
Select override_atp_date_code
Into p_x_line_rec.override_atp_date_code
From oe_order_lines_all
Where header_id = p_x_line_rec.header_id
And ato_line_id = p_x_line_rec.ato_line_id
And override_atp_date_code = 'Y'
AND rownum < 2;
Select override_atp_date_code
Into p_x_line_rec.override_atp_date_code
From oe_order_lines_all
Where header_id = p_x_line_rec.header_id
And line_id = p_x_line_rec.link_to_line_id;
update.
-----------------------------------------------------------*/
PROCEDURE Save_Sch_Attributes
( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
,p_old_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
,p_sch_action IN VARCHAR2
,p_caller IN VARCHAR2
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
I NUMBER;
l_direct_update VARCHAR2(1) := 'N';
l_po_NeedByDate_Update VARCHAR2(10); -- Added for IR ISO CMS project
scheduling dates would have already been updated on line
but reservations need to be called. Bug 2801597 */
/*Uncommented the sql for Bug 3144917 */
-- 4052648 : Do not query for action - UNSCHEDULE
IF p_sch_action <> OE_SCHEDULE_UTIL.OESCH_ACT_UNSCHEDULE THEN
SELECT request_date, schedule_ship_date,
schedule_arrival_date,
ship_from_org_id, ship_to_org_id,
shipping_method_code, demand_class_code,
planning_priority, delivery_lead_time
INTO p_old_line_tbl(I).request_date,
p_old_line_tbl(I).schedule_ship_date,
p_old_line_tbl(I).schedule_arrival_date,
p_old_line_tbl(I).ship_from_org_id,
p_old_line_tbl(I).ship_to_org_id,
p_old_line_tbl(I).shipping_method_code,
p_old_line_tbl(I).demand_class_code,
p_old_line_tbl(I).planning_priority,
p_old_line_tbl(I).delivery_lead_time
FROM oe_order_lines
WHERE line_id = p_x_line_tbl(I).line_id;
goto direct_update;
goto direct_update;
,x_direct_update => l_direct_update);
l_direct_update := 'N';
IF l_direct_update = 'Y' THEN
p_x_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
<>
IF p_x_line_tbl(I).operation = OE_GLOBALS.G_OPR_NONE THEN
Handle_Direct_Update
( p_x_line_rec => p_x_line_tbl(I)
,p_old_line_rec => p_old_line_tbl(I)
,p_caller => p_caller);
oe_debug_pub.add( 'MAY UPDATE RESERN '|| P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
Update_Reservation
( p_line_rec => p_x_line_tbl(I)
,p_old_line_rec => l_orig_old_line_rec --3144917
,x_return_status => x_return_status);
OE_SCHEDULE_UTIL.Update_PO(p_x_line_tbl(I).schedule_arrival_date,
p_x_line_tbl(I).source_document_id,
p_x_line_tbl(I).source_document_line_id);
l_po_NeedByDate_Update := NVL(FND_PROFILE.VALUE('POR_SYNC_NEEDBYDATE_OM'),'NO');
oe_debug_pub.add(' Need By Date update is allowed ? '||l_po_NeedByDate_Update);
IF NOT OE_Internal_Requisition_Pvt.G_Update_ISO_From_Req
AND NOT OE_SALES_CAN_UTIL.G_IR_ISO_HDR_CANCEL THEN -- AND
-- l_po_NeedByDate_Update = 'YES' THEN
IF l_po_NeedByDate_Update = 'YES' THEN -- IR ISO Tracking bug 7667702
-- Log a delayed request to update the change in Schedule Arrival Date to
-- Requisition Line. This request will be logged only if the change is
-- not initiated from Requesting Organization, and it is not a case of
-- Internal Sales Order Full Cancellation. It will even not be logged
-- Purchasing profile option does not allow update of Need By Date when
-- Schedule Ship Date changes on internal sales order line
OE_delayed_requests_Pvt.log_request
( p_entity_code => OE_GLOBALS.G_ENTITY_LINE
, p_entity_id => p_x_line_tbl(I).line_id
, p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE
, p_requesting_entity_id => p_x_line_tbl(I).line_id
, p_request_unique_key1 => p_x_line_tbl(I).header_id -- Order Hdr_id
, p_request_unique_key2 => p_x_line_tbl(I).source_document_id -- Req Hdr_id
, p_request_unique_key3 => p_x_line_tbl(I).source_document_line_id -- Req Line_id
, p_date_param1 => p_x_line_tbl(I).schedule_arrival_date
, p_request_type => OE_GLOBALS.G_UPDATE_REQUISITION
, x_return_status => l_return_status
);
oe_debug_pub.add(' Need By Date is not allowed to update. Updating MTL_Supply only',5);
OE_SCHEDULE_UTIL.Update_PO(p_x_line_tbl(I).schedule_arrival_date,
p_x_line_tbl(I).source_document_id,
p_x_line_tbl(I).source_document_line_id);
oe_debug_pub.add( '------- DONE WITH DIRECT UPDATES IF ANY' , 1 ) ;
,x_direct_update OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_sec_result NUMBER;
x_direct_update := 'N';
OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := 'N';
oe_debug_pub.add( 'DEFAULT ' || OE_GLOBALS.G_ATTR_UPDATED_BY_DEF , 1 ) ;
IF OE_GLOBALS.G_ATTR_UPDATED_BY_DEF = 'N' THEN
x_direct_update := 'Y';
oe_debug_pub.add( '------LEAVING SECURITY/DEF '|| X_DIRECT_UPDATE , 1 ) ;
PROCEDURE Handle_Direct_Update
--------------------------------------------------------------------*/
PROCEDURE Handle_Direct_Update
( p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
,p_old_line_rec IN OE_ORDER_PUB.line_rec_type
,p_caller IN VARCHAR2)
IS
l_order_type_id NUMBER := OE_Order_Cache.g_header_rec.order_type_id;
oe_debug_pub.add( 'ENTERING HANDLE_DIRECT_UPDATE' , 1 ) ;
oe_debug_pub.add( 'BEFORE DOING DIRECT UPDATE' , 1 ) ;
UPDATE OE_ORDER_LINES
SET ship_from_org_id = p_x_line_rec.ship_from_org_id
,schedule_ship_date = p_x_line_rec.schedule_ship_date
,schedule_arrival_date = p_x_line_rec.schedule_arrival_date
,delivery_lead_time = p_x_line_rec.delivery_lead_time
,mfg_lead_time = p_x_line_rec.mfg_lead_time
,shipping_method_code = p_x_line_rec.shipping_method_code
,schedule_status_code = p_x_line_rec.schedule_status_code
,visible_demand_flag = p_x_line_rec.visible_demand_flag
,Original_Inventory_Item_Id = p_x_line_rec.Original_Inventory_Item_Id
,Original_item_identifier_Type
= p_x_line_rec.Original_item_identifier_Type
,Original_ordered_item_id = p_x_line_rec.Original_ordered_item_id
,Original_ordered_item = p_x_line_rec.Original_ordered_item
,latest_acceptable_date = p_x_line_rec.latest_acceptable_date
,override_atp_date_code = p_x_line_rec.override_atp_date_code
,freight_carrier_code = p_x_line_rec.freight_carrier_code
,Firm_Demand_Flag = p_x_line_rec.Firm_Demand_Flag
,earliest_ship_date = p_x_line_rec.Earliest_ship_date
,promise_date = p_x_line_rec.promise_date
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_login = FND_GLOBAL.LOGIN_ID
,lock_control = p_x_line_rec.lock_control + 1
WHERE LINE_ID = p_x_line_rec.line_id; -- 2806483 Added fright_carrier_code
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>True,-- bug 12613382
p_old_line_rec => p_old_line_rec,
p_line_rec =>p_x_line_rec,
p_line_id => p_x_line_rec.line_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_SCHEDULE_UTIL.PROCESS_LINE IS: ' || L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date := p_x_line_rec.last_update_date;
OE_ORDER_UTIL.g_line_tbl(l_index).last_updated_by := p_x_line_rec.last_updated_by;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login := p_x_line_rec.last_update_login;
oe_debug_pub.add( 'LEAVING HANDLE DIRECT UPDATE' , 1 ) ;
oe_debug_pub.add( 'HANDLE_DIRECT_UPDATE ERROR '|| SQLERRM , 1 ) ;
END Handle_Direct_Update;
PROCEDURE Update_Reservation
This procedure should be used to update the reservation record
with new schedule ship date, after a call to RESCHEDULE.
---------------------------------------------------------------*/
PROCEDURE Update_Reservation
( p_line_rec IN OE_Order_Pub.Line_Rec_Type
,p_old_line_rec IN OE_Order_Pub.Line_Rec_Type
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
Print_Time('entering Update_Reservation');
oe_debug_pub.add( 'NO NEED TO UPDATE OR NO RESERVN' , 3 ) ;
oe_debug_pub.add( 'RSCH: CALLING UPDATE RESERVATION '|| I , 1 ) ;
Inv_Reservation_Pub.Update_Reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_tbl(I)
, p_to_rsv_rec => l_rsv_rec
, p_original_serial_number => l_dummy_sn -- no serial contorl
, p_to_serial_number => l_dummy_sn -- no serial control
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 2 ); -- Added this for 4715544
oe_debug_pub.add( 'AFTER UPDATE_RESERVATION: '||X_RETURN_STATUS , 1 ) ;
oe_debug_pub.add( 'UPDATE_RESERVATION ERROR '|| SQLERRM , 1 ) ;
END Update_Reservation;
result in correct selection, ex: if model_line_id and ship_set_id
both are set, all lines selected by 1st part are subset of lines
selected by 2nd part.
---------------------------------------------------------------*/
Procedure Query_Set_Lines
(p_header_id IN NUMBER,
p_ship_set_id IN NUMBER := FND_API.G_MISS_NUM,
p_arrival_set_id IN NUMBER := FND_API.G_MISS_NUM,
p_model_line_id IN NUMBER := FND_API.G_MISS_NUM,
p_link_to_line_id IN NUMBER := FND_API.G_MISS_NUM,
p_sch_action IN VARCHAR2,
p_send_cancel_lines IN VARCHAR2 := 'N',
x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_sales_order_id NUMBER;
SELECT line_id
FROM oe_order_lines
WHERE item_type_code IN ('MODEL', 'CLASS', 'KIT')
AND ato_line_id is NULL
AND explosion_date is NULL
AND header_id = p_header_id
AND ((top_model_line_id = p_model_line_id)
OR (ship_set_id = p_ship_set_id)
OR (arrival_set_id = p_arrival_set_id)
OR (line_id = p_link_to_line_id));
x_line_tbl.DELETE(l_ctr);
x_line_tbl.delete;
SELECT ATO_LINE_ID
,BOOKED_FLAG
,COMPONENT_CODE
,COMPONENT_NUMBER
,COMPONENT_SEQUENCE_ID
,CONFIG_HEADER_ID
,CONFIG_REV_NBR
,CONFIGURATION_ID
,CREATION_DATE
,EXPLOSION_DATE
,HEADER_ID
,ITEM_TYPE_CODE
,LINE_ID
,LINE_NUMBER
,LINK_TO_LINE_ID
,OPEN_FLAG
,OPTION_NUMBER
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,ORDERED_ITEM
,SHIPPABLE_FLAG
,SHIP_MODEL_COMPLETE_FLAG
,SPLIT_FROM_LINE_ID
,MODEL_REMNANT_FLAG
,TOP_MODEL_LINE_ID
,UPGRADED_FLAG
,LOCK_CONTROL
INTO
x_line_rec.ATO_LINE_ID
,x_line_rec.BOOKED_FLAG
,x_line_rec.COMPONENT_CODE
,x_line_rec.COMPONENT_NUMBER
,x_line_rec.COMPONENT_SEQUENCE_ID
,x_line_rec.CONFIG_HEADER_ID
,x_line_rec.CONFIG_REV_NBR
,x_line_rec.CONFIGURATION_ID
,x_line_rec.CREATION_DATE
,x_line_rec.EXPLOSION_DATE
,x_line_rec.HEADER_ID
,x_line_rec.ITEM_TYPE_CODE
,x_line_rec.LINE_ID
,x_line_rec.LINE_NUMBER
,x_line_rec.LINK_TO_LINE_ID
,x_line_rec.OPEN_FLAG
,x_line_rec.OPTION_NUMBER
,x_line_rec.ORDERED_QUANTITY
,x_line_rec.ORDER_QUANTITY_UOM
,x_line_rec.ORDERED_ITEM
,x_line_rec.SHIPPABLE_FLAG
,x_line_rec.SHIP_MODEL_COMPLETE_FLAG
,x_line_rec.SPLIT_FROM_LINE_ID
,x_line_rec.MODEL_REMNANT_FLAG
,x_line_rec.TOP_MODEL_LINE_ID
,x_line_rec.UPGRADED_FLAG
,x_line_rec.LOCK_CONTROL
FROM OE_ORDER_LINES
WHERE LINE_ID = P_LINE_ID;
SELECT ship_from_org_id
INTO l_num_id
FROM oe_order_lines
WHERE line_id = p_line_rec.ato_line_id;