[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE_FLAG VARCHAR2(1) := FND_API.G_TRUE;
Procedure Update_PO
(p_schedule_ship_date IN DATE,
p_source_document_id IN VARCHAR2,
p_source_document_line_id IN VARCHAR2);
IF p_line_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
RETURN FALSE;
IF (p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'IT IS A UPDATE ACTION ON THE LINE' , 1 ) ;
IF p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
p_line_rec.schedule_status_code IS NOT NULL AND
(NOT OE_GLOBALS.Equal(p_line_rec.inventory_item_id,
p_old_line_rec.inventory_item_id))
THEN
RETURN TRUE;
Procedure Name : Update_Group_Sch_Results
Description :
----------------------------------------------------------------------- */
Procedure Update_Group_Sch_Results
(p_x_line_rec IN OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.line_rec_type,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_line_rec OE_ORDER_PUB.line_rec_type;
END Update_Group_Sch_Results;
3. The operation on the line is DELETE.
If will explode the included items (by calling
process_included_items) for the following cases
1. Explosion date is null on the line and operation is
UPDATE on the line.
----------------------------------------------------------- */
PROCEDURE Build_Included_Items
(p_line_rec IN OE_ORDER_PUB.line_rec_type,
x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type)
IS
--l_line_tbl OE_ORDER_PUB.line_tbl_type;
IF p_line_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
l_explode := FALSE;
IF p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'RR:I1' ) ;
ELSIF p_x_line_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
l_schedule_action_code := OESCH_ACT_UNSCHEDULE;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
g_update_flag = FND_API.G_TRUE
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CALLING UPDATE_LINE_RECORD WITH NEW TABLE ' , 1 ) ;
Update_line_record(p_line_tbl => l_top_old_tbl,
p_x_new_line_tbl => l_new_line_tbl,
p_write_to_db => p_write_to_db,
p_recursive_call => p_recursive_call,
x_return_status => l_return_status);
oe_debug_pub.add( 'AFTER CALLING UPDATE_LINE_RECORD :' || L_RETURN_STATUS , 1 ) ;
oe_debug_pub.add( 'CALLING UPDATE_LINE_RECORD WITH INC TABLE '||L_INC_UPD_INDEX||'/'||L_INC_OLD_INDEX , 2 ) ;
Update_line_record(p_line_tbl => l_inc_old_tbl,
p_x_new_line_tbl => l_inc_upd_tbl,
p_write_to_db => FND_API.G_TRUE,
p_recursive_call => p_recursive_call,
x_return_status => l_return_status);
oe_debug_pub.add( 'AFTER CALLING UPDATE_LINE_RECORD :' || L_RETURN_STATUS , 1 ) ;
4. Sch_Multi_Selected_Lines (in OEXVGRPB.pls)
-------------------------------------------------------------------------- */
Procedure Schedule_line( p_old_line_rec IN OE_ORDER_PUB.line_rec_type,
p_write_to_db IN VARCHAR2,
p_update_flag IN VARCHAR2 := FND_API.G_TRUE,
p_recursive_call IN VARCHAR2 := FND_API.G_TRUE,
p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type,
x_atp_tbl OUT NOCOPY /* file.sql.39 change */ OE_ATP.atp_tbl_type,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
l_schedule_line_rec request_rec_type;
g_update_flag := p_update_flag;
l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
(NOT OE_GLOBALS.Equal(p_x_line_rec.inventory_item_id,
p_old_line_rec.inventory_item_id)))
THEN
-- If the scheduling is happening due to inventory item change.
-- We should call MRP twice. First time we should call MRP with
-- Undemand for old item. Second call would be redemand.
IF (l_old_line_rec.reserved_quantity is not null AND
l_old_line_rec.reserved_quantity <> FND_API.G_MISS_NUM)
THEN
-- Call INV API to delete the reservations on the line.
Unreserve_Line
( p_line_rec => l_old_line_rec
, p_quantity_to_unreserve => l_old_line_rec.reserved_quantity
, p_quantity2_to_unreserve => l_old_line_rec.reserved_quantity2 -- INVCONV
, x_return_status => l_return_status);
ELSIF l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
((NOT OE_GLOBALS.Equal(p_x_line_rec.ship_set_id,
p_old_line_rec.ship_set_id)) OR
(NOT OE_GLOBALS.Equal(p_x_line_rec.arrival_set_id,
p_old_line_rec.arrival_set_id)))
THEN
-- Line is either being moved from one set to another,
-- or is being added to a new set.
IF l_line_rec.schedule_status_code is null THEN
-- New line which is being added to the set is not
-- scheduled.
l_request_type := OE_GLOBALS.G_SCHEDULE_LINE;
oe_debug_pub.add( 'CALLING UPDATE_GROUP_SCH_RESULTS' , 1 ) ;
Update_Group_Sch_Results(p_x_line_rec => l_out_line_rec,
x_return_status => l_return_status);
oe_debug_pub.add( 'AFTER CALLING UPDATE_GROUP_SCH_RESULTS' , 1 ) ;
g_update_flag = FND_API.G_TRUE
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CALLING UPDATE_LINE_RECORD ' , 1 ) ;
Update_line_record(p_line_tbl => l_line_tbl,
p_x_new_line_tbl => l_new_line_tbl,
p_write_to_db => p_write_to_db,
p_recursive_call => p_recursive_call,
x_return_status => l_return_status);
oe_debug_pub.add( 'AFTER CALLING UPDATE_LINE_RECORD :' || L_RETURN_STATUS , 1 ) ;
IF p_x_line_rec.operation = oe_globals.g_opr_update AND
p_x_line_rec.source_document_type_id = 10 THEN
/* Changing schedule ship date to schedule arrival date for
bug 2024748 */
IF NOT OE_GLOBALS.EQUAL(l_out_line_rec.schedule_arrival_date,
p_old_line_rec.schedule_arrival_date) THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'PASSING SCHEDULE_ARRIVAL_DATE TO PO ' , 3 ) ;
Update_PO(l_out_line_rec.schedule_arrival_date,
l_out_line_rec.source_document_id,
l_out_line_rec.source_document_line_id);
Procedure Name : Update_line_record
Description : This process is called after scheduling is performed
on the line and the result needs to be verified and/or
updated to the database.
--------------------------------------------------------------------- */
Procedure Update_line_record
( p_line_tbl IN OE_ORDER_PUB.line_tbl_type
, p_x_new_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
, p_write_to_db IN VARCHAR2
, p_recursive_call IN VARCHAR2
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_schedule_line_rec request_rec_type;
oe_debug_pub.add( 'ENTERING UPDATE_LINE_RECORD' , 1 ) ;
/** Instead call process_requests_and_notify after this update_line_rec **/
/** procedure is called. **/
/*
-- Do not process delayed requests if this was a recursive
-- call (e.g. from oe_line_util.pre_write_process)
IF p_recursive_call = FND_API.G_TRUE THEN
l_process_requests := FALSE;
oe_debug_pub.add( 'EXITING UPDATE LINE RECORD' , 1 ) ;
END Update_line_record;
p_line_rec.operation = OE_GLOBALS.G_OPR_DELETE)
THEN
RETURN FALSE;
(p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE)
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'THIS LINE IS BEING SPLIT' , 1 ) ;
(p_line_rec.operation = OE_GLOBALS.G_OPR_DELETE)
THEN
RETURN TRUE;
select order_date_type_code into l_order_date_type_code
from oe_order_headers_all
where header_id = p_line_rec.header_id;
SELECT name, scheduling_level_code
INTO l_line_type,l_scheduling_level_code
FROM oe_transaction_types
WHERE transaction_type_id = p_line_type_id AND
transaction_type_code = 'LINE';
SELECT name, scheduling_level_code
INTO l_order_type,l_scheduling_level_code
FROM oe_order_types_v ot, oe_order_headers h
WHERE h.header_id = p_header_id AND
h.order_type_id = ot.order_type_id;
SELECT planning_make_buy_code
INTO l_make_buy
FROM mtl_system_items
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND ORGANIZATION_ID = nvl(p_line_rec.ship_from_org_id,
l_org_id);
SELECT BILL_SEQUENCE_ID
INTO l_bill_seq_id
FROM BOM_BILL_OF_MATERIALS
WHERE ORGANIZATION_ID = nvl(p_line_rec.ship_from_org_id,l_org_id)
AND ASSEMBLY_ITEM_ID = p_line_rec.inventory_item_id
AND ALTERNATE_BOM_DESIGNATOR IS NULL;
SELECT msi.lot_control_code
INTO l_lot_control_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = nvl(p_org_id,l_org_id);
SELECT RESERVABLE_TYPE
INTO l_reservable_type
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_line_rec.inventory_item_id
AND ORGANIZATION_ID = l_line_rec.ship_from_org_id;
SELECT revision_qty_control_code, lot_control_code,
serial_number_control_code
INTO l_revision_code, l_lot_code, l_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_line_rec.ship_from_org_id;
IF (l_line_rec.operation = OE_GLOBALS.G_OPR_DELETE) OR
((l_line_rec.source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL) AND
(l_line_rec.schedule_status_code is not null)) THEN
-- If the line is deleted, we need to unschedule it.
-- If the line's source type is being changed from INTERNAL to
-- EXTERNAL, and the old line was scheduled, we need to unschedule it.
l_line_rec.schedule_action_code := OESCH_ACT_UNSCHEDULE;
END IF; /* If operation on line was DELETE */
oe_debug_pub.add( 'RSCH: CALLING INVS UPDATE RESERVATION ' , 1 ) ;
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_tbl(k)
, p_to_rsv_rec => l_reservation_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
);
oe_debug_pub.add( 'AFTER CALLING INVS UPDATE_RESERVATION: ' || L_RETURN_STATUS , 1 ) ;
SELECT RESERVABLE_TYPE
INTO l_reservable_type
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_line_rec.inventory_item_id
AND ORGANIZATION_ID = l_line_rec.ship_from_org_id;
SELECT revision_qty_control_code, lot_control_code,
serial_number_control_code
INTO l_revision_code, l_lot_code, l_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_line_rec.ship_from_org_id;
g_update_flag := FND_API.G_FALSE;
g_update_flag := FND_API.G_FALSE;
l_atp_rec.Insert_Flag.extend(l_count);
l_atp_rec.ato_delete_flag.extend(l_count);
SELECT NVL ( MSI.FULL_LEAD_TIME , 0 )
, NVL ( MSI.FIXED_LEAD_TIME , 0 )
, NVL ( MSI.VARIABLE_LEAD_TIME , 0 )
, MSI.PRIMARY_UOM_CODE
, NVL ( OL.INVENTORY_ITEM_ID , 0 )
, OL.order_quantity_uom
, OL.ordered_quantity
INTO db_full_lead_time
, db_fixed_lead_time
, db_variable_lead_time
, db_primary_uom_code
, db_model_item_id
, db_line_unit_code
, primary_model_qty
FROM MTL_SYSTEM_ITEMS MSI
, OE_ORDER_LINES OL
WHERE MSI.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = p_ship_from_org_id
AND OL.LINE_ID = p_ato_line_id ;
SELECT order_date_type_code
INTO l_order_date_type_code
FROM oe_order_headers
WHERE header_id = p_header_id;
select order_number
into l_order_number
from oe_order_headers
where header_id = p_header_id;
l_insert_flag NUMBER;
l_insert_flag := 1;
l_insert_flag := 0;
oe_debug_pub.add( 'INSERT FLAG : '||L_INSERT_FLAG , 3 ) ;
l_atp_rec.Insert_Flag(I) := l_insert_flag;
oe_debug_pub.add( 'INSERT FLAG IN ATP_REC : '||L_ATP_REC.INSERT_FLAG ( I ) , 3 ) ;
Update oe_order_lines_all
Set ordered_quantity = 0,
ordered_quantity2 = 0
Where line_id=l_old_line_rec.line_id;
l_model_rec.Insert_Flag := MRP_ATP_PUB.number_arr
(l_atp_rec.Insert_Flag(I));
--oe_debug_pub.add('Insert flag in model_rec : '||l_model_rec.insert_flag,3);
l_atp_rec.Insert_Flag(I) :=
l_smc_rec.Insert_Flag(J);
oe_debug_pub.add( 'INSERT FLAG IN SMC_REC : '||L_SMC_REC.INSERT_FLAG ( J ) , 3 ) ;
oe_debug_pub.add( 'INSERT FLAG IN ATP_REC : '||L_ATP_REC.INSERT_FLAG ( I ) , 3 ) ;
delete_reservations until the p_quantity_to_unreserve
is satisfied.
--------------------------------------------------------------------- */
Procedure Unreserve_Line
( p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
, p_quantity_to_unreserve IN NUMBER
, p_quantity2_to_unreserve IN NUMBER -- INVCONV
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_line_rec OE_ORDER_PUB.line_rec_type;
oe_debug_pub.add( 'CALLING INVS DELETE_RESERVATION' , 3 ) ;
inv_reservation_pub.delete_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_rec
, p_serial_number => l_dummy_sn
);
oe_debug_pub.add( 'AFTER CALLING INVS DELETE_RESERVATION: ' || L_RETURN_STATUS , 1 ) ;
oe_debug_pub.add( 'CALLING INVS UPDATE_RESERVATION: ' , 3 ) ;
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_rec
, p_to_rsv_rec => l_rsv_new_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
);
oe_debug_pub.add( 'AFTER CALLING INVS UPDATE_RESERVATION: ' || L_RETURN_STATUS , 1 ) ;
Select 'Y'
Into l_config_exists
From oe_order_lines_all
Where header_id = p_x_line_tbl(I).header_id
And ato_line_id = p_x_line_tbl(I).ato_line_id
And item_type_code = OE_GLOBALS.G_ITEM_CONFIG;
select meaning
into l_explanation
from mfg_lookups where
lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = p_atp_table.error_code(J) ;
SELECT SET_NAME
INTO l_ship_set_name
FROM OE_SETS
WHERE set_id = l_line_rec.ship_set_id;
SELECT SET_NAME
INTO l_arrival_set_name
FROM OE_SETS
WHERE set_id = l_line_rec.arrival_set_id;
select meaning
into l_explanation
from mfg_lookups where
lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = p_atp_table.error_code(J) ;
SELECT SET_NAME
INTO l_ship_set_name
FROM OE_SETS
WHERE set_id = l_line_rec.ship_set_id;
SELECT SET_NAME
INTO l_arrival_set_name
FROM OE_SETS
WHERE set_id = l_line_rec.arrival_set_id;
Procedure Name : Insert_Into_Mtl_Sales_Orders
Description : This API creates a record in MTL_SALES_ORDERS for a given
order header.
Every header in oe_order_headers_all will have a record
in MTL_SALES_ORDERS. The unique key to get the sales_order_id
from mtl_sales_orders is
Order_Number
Order_Type (in base language)
OM:Source Code profile option (stored as ont_source_code).
The above values are stored in a flex in MTL_SALES_ORDERS.
SEGMENT1 : stores the order number
SEGMENT2 : stores the order type
SEGMENT3 : stores the ont_source_code value
-------------------------------------------------------------------------- */
Procedure Insert_Into_Mtl_Sales_Orders
( p_header_rec IN OE_ORDER_PUB.header_rec_type)
IS
l_order_type_name VARCHAR2(80);
oe_debug_pub.add( 'ENTERING INSERT_INTO_MTL_SALES_ORDERS' , 1 ) ;
SELECT NAME
INTO l_order_type_name
FROM OE_TRANSACTION_TYPES_TL
WHERE TRANSACTION_TYPE_ID = p_header_rec.order_type_id
AND language = (select language_code
from fnd_languages
where installed_flag = 'B');
oe_debug_pub.add( 'EXITING INSERT_INTO_MTL_SALES_ORDERS' , 1 ) ;
, 'Insert_Into_Mtl_Sales_Orders'
);
END Insert_Into_Mtl_Sales_Orders;
l_local_line_tbl.delete;
l_local_line_tbl.delete;
l_local_line_tbl.delete;
Select ato_line_id
Into l_ato_line_id
From oe_order_lines_all
Where line_id = p_x_line_tbl(I).line_id;
l_local_line_tbl.delete;
There is an update line (the line which is getting split)
and multiple insert lines (new lines created due to the split).
We need to do the following:
For scheduling
-------------
On the updated line: Reschedule the line.
On the inserted lines: Schedule the lines.
For reservation
---------------
If the split is due to shipping, we need to update the
reservations (whichever exist) to the new line which
got created.
If the split is due to the user splitting, there could be
multiple records created due to the split. We should update
the old reservation to reflect the change in qty for the
original line, and create new reservations for the new lines
which got created.
---------------------------------------------------------------------- */
Procedure SPLIT_SCHEDULING
( p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
IF p_x_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE AND
p_x_line_tbl(I).schedule_status_code is not null AND
p_x_line_tbl(I).split_action_code = 'SPLIT' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SPLITTING SCHEDULING' , 1 ) ;
oe_debug_pub.add( '11. CALLING INVS UPDATE RSV: ' , 1 ) ;
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_rec
, p_to_rsv_rec => l_rsv_new_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
oe_debug_pub.add( '1. CALLING INVS UPDATE RSV: ' , 1 ) ;
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_rec
, p_to_rsv_rec => l_rsv_new_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
oe_debug_pub.add( '1 AFTER CALLING INVS UPDATE_RESERVATION: ' || L_RETURN_STATUS , 1 ) ;
l_rsv_tbl.delete(K);
END IF; /* If operation on the line was UPDATE */
IS select header_id,
line_id ,
ordered_quantity,
shipped_quantity
from oe_order_lines where
split_from_line_id = p_reserved_line_id;
SELECT header_id, ship_from_org_id
into l_p_header_id, l_ship_from_org_id
from oe_order_lines
where line_id = p_reserved_line_id;
oe_debug_pub.add( 'CALLING INVS UPDATE_RESERVATION: ' , 1 ) ;
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_rec
, p_to_rsv_rec => l_rsv_new_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
);
oe_debug_pub.add( 'AFTER CALLING INVS UPDATE_RESERVATION: ' || L_RETURN_STATUS , 1 ) ;
Procedure Name : Update_Results_from_backlog_wb
Description : This procedure is called from the backlog's scheduler's
workbenck and the Supply Chain ATP form, after the user
has performed some scheduling in their form. They call
this API to update the results of scheduling on the order
lines table.
For the purpose of this call, we have created a new table type
mrp_line_tbl_type, which is table of mrp_line_rec_type.
This record is created with only those fields whose values
we can get back from MRP's form. We take the field values
from this record and update the lines information in
oe_order_lines table.
-------------------------------------------------------------------------- */
Procedure Update_Results_from_backlog_wb
( p_mrp_line_tbl IN mrp_line_tbl_type
, x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
, x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_schedule_line_rec request_rec_type;
OE_SCHEDULE_UTIL.Update_Results_from_backlog_wb
(p_mrp_line_tbl => l_mrp_line_tbl,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
oe_debug_pub.add( 'EXITING UPDATE_RESULTS_FROM_BACKLOG_WB' , 1 ) ;
END Update_Results_from_backlog_wb;
SELECT mrp_atp_schedule_temp_s.nextval
INTO MRP_SESSION_ID
from dual;
Procedure Name : Insert_Mandatory_Components
Description : This procedure is called from the form side, when the user
clicks on global availability button and the item to check
global availability is an ATO Model. We insert the mandatory
components in MRP_ATP_SCHEDULE_TEMP for global availability.
--------------------------------------------------------------------------*/
Procedure Insert_Mandatory_Components
(p_order_number IN NUMBER,
p_ato_line_id IN NUMBER,
p_customer_name IN VARCHAR2,
p_customer_location IN VARCHAR2,
p_arrival_set_name IN VARCHAR2,
p_ship_set_name IN VARCHAR2,
p_ship_set_id IN NUMBER,
p_requested_ship_date IN DATE,
p_requested_arrival_date IN DATE,
p_session_id IN NUMBER,
p_instance_id IN NUMBER,
p_insert_code IN NUMBER,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
l_model_line_rec OE_ORDER_PUB.line_rec_type;
SAVEPOINT insert_mand_comp;
l_model_rec.Insert_Flag := MRP_ATP_PUB.number_arr
(null);
SELECT concatenated_segments
INTO l_ordered_item
FROM mtl_system_items_vl
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_ship_from_org_id;
INSERT INTO MRP_ATP_SCHEDULE_TEMP
(INVENTORY_ITEM_ID,
SR_INSTANCE_ID,
SOURCE_ORGANIZATION_ID,
CUSTOMER_ID,
CUSTOMER_SITE_ID,
DESTINATION_TIME_ZONE,
QUANTITY_ORDERED,
UOM_CODE,
REQUESTED_SHIP_DATE,
REQUESTED_ARRIVAL_DATE,
LATEST_ACCEPTABLE_DATE,
DELIVERY_LEAD_TIME,
FREIGHT_CARRIER,
INSERT_FLAG,
SHIP_METHOD,
DEMAND_CLASS,
SHIP_SET_NAME,
SHIP_SET_ID,
ARRIVAL_SET_NAME,
ARRIVAL_SET_ID,
ATP_LEAD_TIME,
OVERRIDE_FLAG,
SESSION_ID,
ORDER_HEADER_ID,
ORDER_LINE_ID,
INVENTORY_ITEM_NAME,
SOURCE_ORGANIZATION_CODE,
ORDER_LINE_NUMBER,
SHIPMENT_NUMBER,
OPTION_NUMBER,
PROMISE_DATE,
CUSTOMER_NAME,
CUSTOMER_LOCATION,
OLD_LINE_SCHEDULE_DATE,
OLD_SOURCE_ORGANIZATION_CODE,
CALLING_MODULE,
ACTION,
STATUS_FLAG,
SCENARIO_ID,
ORDER_NUMBER,
OLD_SOURCE_ORGANIZATION_ID,
OLD_DEMAND_CLASS,
PROJECT_ID,
TASK_ID,
PROJECT_NUMBER,
TASK_NUMBER,
SHIP_METHOD_TEXT
)
VALUES
(l_inventory_item_id,
p_instance_id,
null,
l_sold_to_org_id, -- CUSTOMER_ID
l_ship_to_org_id, -- CUSTOMER_SITE_ID
null, -- DESTINATION_TIME_ZONE
l_quantity_ordered,
l_uom_code,
p_requested_ship_date,
p_requested_arrival_date,
l_latest_acceptable_date,
l_delivery_lead_time,
null, -- FREIGHT_CARRIER,
p_insert_code,
l_ship_method,
l_demand_class,
p_ship_set_name,
p_ship_set_id,
p_arrival_set_name,
l_arrival_set_id,
l_st_atp_lead_time,
null, -- OVERRIDE_FLAG
p_session_id,
l_header_id,
l_line_id,
l_ordered_item, -- l_INVENTORY_ITEM_NAME,
null, -- l_SOURCE_ORGANIZATION_CODE,
l_line_number,
l_shipment_number,
l_option_number,
l_promise_date,
p_customer_name,
p_customer_location,
null, -- l_OLD_LINE_SCHEDULE_DATE,
null, -- l_OLD_SOURCE_ORGANIZATION_CODE,
null, -- l_CALLING_MODULE,
100,
4, -- l_STATUS_FLAG,
l_scenario_id,
p_order_number,
l_ship_from_org_id,
l_demand_class,
l_project_id,
l_task_id,
l_project_number,
l_task_number,
l_ship_method_text
);
ROLLBACK TO SAVEPOINT insert_mand_comp;
END Insert_Mandatory_Components;
Procedure Name : Update_PO
Description : This procedure is called whenever there is a change to
schedule_ship_date on an internal order. PO has a callback
we need to call to notify them of this change.
--------------------------------------------------------------------------*/
Procedure Update_PO(p_schedule_ship_date IN DATE,
p_source_document_id IN VARCHAR2,
p_source_document_line_id IN VARCHAR2)
IS
po_result BOOLEAN;
oe_debug_pub.add( 'ENTERING OE_ORDER_SCH_UTIL.UPDATE_PO' , 2 ) ;
p_action => 'Update_Req_Line_Date',
p_recreate_flag => FALSE,
p_qty => null,
p_receipt_date => p_schedule_ship_date);
oe_debug_pub.add( 'EXITING OE_ORDER_SCH_UTIL.UPDATE_PO' , 1 ) ;
oe_debug_pub.add( 'EXCEPTION IN UPDATE_PO' , 2 ) ;
END Update_PO;
Procedure Delete_Row(p_line_id IN NUMBER)
IS
l_line_rec OE_ORDER_PUB.line_rec_type;
oe_debug_pub.add( 'ENTERING OE_ORDER_SCH_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'EXITING OE_ORDER_SCH_UTIL.DELETE_ROW' , 1 ) ;
, 'Delete_row'
);
END Delete_Row;