[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mrp_atp_schedule_temp_s.nextval
INTO MRP_SESSION_ID
from dual;
SELECT count(*)
INTO l_count
FROM wsh_carrier_services wsh,
wsh_org_carrier_services wsh_org
WHERE wsh_org.organization_id = p_ship_from_org_id
AND wsh.carrier_service_id = wsh_org.carrier_service_id
AND wsh.ship_method_code = p_new_ship_method
AND wsh_org.enabled_flag = 'Y';
SELECT count(*)
INTO l_count
FROM wsh_carrier_ship_methods
WHERE ship_method_code = p_new_ship_method
AND organization_id = p_ship_from_org_id;
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 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_SCHEDULE_UTIL.UPDATE_PO' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
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_SCHEDULE_UTIL.UPDATE_PO' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
oe_debug_pub.add( 'EXCEPTION IN UPDATE_PO' , 2 ) ;
END Update_PO;
SELECT 'Y'
INTO l_overridden
FROM oe_order_lines_all
WHERE top_model_line_id = p_top_model_line_id
AND open_flag = 'Y'
AND override_atp_date_code = 'Y'
AND rownum < 2;
SELECT 'Y'
INTO l_overridden
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND (ship_set_id = p_ship_set_id
OR arrival_set_id = p_arrival_set_id)
AND override_atp_date_code = 'Y'
AND rownum < 2;
,p_rsv_update IN BOOLEAN DEFAULT FALSE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_msg_count NUMBER;
SELECT RESERVABLE_TYPE
INTO l_reservable_type
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = p_line_rec.inventory_item_id
AND ORGANIZATION_ID = p_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 = p_line_rec.inventory_item_id
AND organization_id = p_line_rec.ship_from_org_id;
, p_partial_rsv_exists => p_rsv_update
, p_substitute_flag => l_substitute_flag -- Added for ER 6110708
);
delete_reservations until the p_quantity_to_unreserve
is satisfied.
--------------------------------------------------------------------- */
Procedure Unreserve_Line
( p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
,p_old_ship_from_org_id IN NUMBER DEFAULT NULL -- 6628134
,p_quantity_to_unreserve IN NUMBER
,p_quantity2_to_unreserve IN NUMBER DEFAULT NULL -- INVCONV
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
SELECT count(a.line_id)
INTO l_count
FROM oe_order_lines_all a,
oe_order_lines_all b
WHERE a.top_model_line_id = p_top_model_line_id
AND a.top_model_line_id = b.top_model_line_id
AND a.override_atp_date_code = 'Y'
AND b.override_atp_date_code = 'Y'
AND a.schedule_ship_date <> b.schedule_ship_date;
SELECT count(a.line_id)
INTO l_count
FROM oe_order_lines_all a,
oe_order_lines_all b
WHERE a.top_model_line_id = p_top_model_line_id
AND a.top_model_line_id = b.top_model_line_id
AND a.override_atp_date_code = 'Y'
AND b.override_atp_date_code = 'Y'
AND a.schedule_arrival_date <> b.schedule_arrival_date;
Procedure Log_Delete_Set_Request
Set_id will be deleted
1. When the line gets removed from the set.
2. When the line gets deleted, which is part of the set.
3. When line gets cancelled.
-------------------------------------------------------------------------*/
Procedure Log_Delete_Set_Request
(p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_set_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_set_type VARCHAR2(30);
oe_debug_pub.add( 'ENTERING LOG DELETE SET REQUEST' || P_SET_ID , 1 );
SELECT set_type
INTO l_set_type
FROM oe_sets
WHERE set_id = p_set_id;
p_request_type => OE_GLOBALS.G_DELETE_SET,
p_param1 => p_set_id,
p_param2 => p_header_id,
p_param3 => l_set_type,
x_return_status => x_return_status);
oe_debug_pub.add( 'EXITING LOG DELETE SET REQUEST '||x_return_status , 1 ) ;
'Log_Delete_Set_Request');
END Log_Delete_Set_Request;
/* Removed ship_method_code from the following select , to fix the bug 2916814 */
Select schedule_ship_date,ship_to_org_id,ship_from_org_id,
schedule_arrival_date,shipping_method_code,
set_status, set_type
Into l_schedule_ship_date,l_ship_to_org_id,l_ship_from_org_id,
l_schedule_arrival_date,l_shipping_method_code,
l_set_status, l_set_type
From oe_sets
Where set_id = nvl(p_line_rec.ship_set_id,p_line_rec.arrival_set_id);
Update oe_order_lines_all
Set shipping_method_code = l_shipping_method_code
where header_id = p_line_rec.header_id
and line_id = p_line_rec.line_id;
IF NOT OE_Internal_Requisition_Pvt.G_Update_ISO_From_Req
AND NOT OE_SALES_CAN_UTIL.G_IR_ISO_HDR_CANCEL THEN
IF FND_PROFILE.VALUE('POR_SYNC_NEEDBYDATE_OM') = 'YES' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Logging G_UPDATE_REQUISITION delayed request for date change');
, p_request_type => OE_GLOBALS.G_UPDATE_REQUISITION
, x_return_status => l_return_status
);
IF NOT OE_Schedule_GRP.G_ISO_Planning_Update THEN
IF l_debug_level > 0 THEN
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_rec.schedule_arrival_date,
p_x_line_rec.source_document_id,
p_x_line_rec.source_document_line_id);
p_x_atp_rec.Insert_Flag.extend(l_count);
p_x_atp_rec.ato_delete_flag.extend(l_count);
l_update_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
select line_id , schedule_action_code
from oe_schedule_lines_temp
where line_id = c_line_id;
l_split_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_update_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_update_line_tbl(1).line_id := l_split_line_tbl(2).line_id;
l_update_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_update_line_tbl(1).change_reason := 'SYSTEM';
l_update_line_tbl(1).ship_from_org_id := Nvl(p_x_line_rec.ship_from_org_id, p_atp_rec.source_organization_id(J));
l_update_line_tbl(1).schedule_action_code := OE_Schedule_Util.OESCH_ACT_SCHEDULE;
, p_x_line_tbl => l_update_line_tbl
, p_control_rec => l_control_rec
, p_x_header_rec => l_header_out_rec
, p_x_header_adj_tbl => l_header_adj_out_tbl
, p_x_header_scredit_tbl => l_header_scredit_out_tbl
, p_x_header_payment_tbl => l_header_payment_out_tbl
, p_x_line_adj_tbl => l_line_adj_out_tbl
, p_x_line_scredit_tbl => l_line_scredit_out_tbl
, p_x_line_payment_tbl => l_line_payment_out_tbl
, p_x_action_request_tbl => l_action_request_out_tbl
, p_x_lot_serial_tbl => l_lot_serial_tbl
, p_x_header_price_att_tbl => l_header_price_att_tbl
, p_x_header_adj_att_tbl => l_header_adj_att_tbl
, p_x_header_adj_assoc_tbl => l_header_adj_assoc_tbl
, p_x_line_price_att_tbl => l_line_price_att_tbl
, p_x_line_adj_att_tbl => l_line_adj_att_tbl
, p_x_line_adj_assoc_tbl => l_line_adj_assoc_tbl
);
oe_debug_pub.add( 'check_split_needed() l_update_line_tbl(1) : SSD ' || l_update_line_tbl(1).schedule_ship_date , 1 ) ;
oe_debug_pub.add( 'check_split_needed() l_update_line_tbl(1) : SAD ' || l_update_line_tbl(1).schedule_arrival_date , 1 ) ;
Select 'EXISTS' into
l_service_exists
from oe_order_lines_all
where service_reference_line_id = p_x_line_rec.line_id;
l_split_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
--update the order line so that in the DB it is scheduled
--doing the update later so that the default_attributes above
--takes the item_id from the original line
update oe_order_lines_all
SET schedule_ship_date = p_atp_rec.ship_date(J),
schedule_arrival_date = p_atp_rec.arrival_date(J),
schedule_status_code = 'SCHEDULED',
ship_from_org_id = p_atp_rec.Source_Organization_Id(J),
visible_demand_flag = p_x_line_rec.visible_demand_flag,
delivery_lead_time = p_atp_rec.delivery_lead_time(J),
mfg_lead_time = p_atp_rec.atp_lead_time(J),
shipping_method_code = nvl(p_atp_rec.ship_method(J),shipping_method_code),
Original_Inventory_Item_Id = p_x_line_rec.Inventory_Item_id,
Original_item_identifier_Type = p_x_line_rec.item_identifier_type,
Original_ordered_item_id = p_x_line_rec.ordered_item_id ,
Original_ordered_item = p_x_line_rec.ordered_item,
inventory_item_id = p_atp_rec.inventory_item_id(J),
item_identifier_type = 'INT'
WHERE line_id = p_x_line_rec.line_id;
--Call PO API to update newly created line with new inventory item id
l_update_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_update_line_tbl(1).line_id := l_split_line_tbl(2).line_id;
l_update_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_update_line_tbl(1).change_reason := 'SYSTEM';
l_update_line_tbl(1).ship_from_org_id := Nvl(p_x_line_rec.ship_from_org_id, p_atp_rec.source_organization_id(J));
l_update_line_tbl(1).inventory_item_id := p_atp_rec.inventory_item_id(J) ; --substitute item
l_update_line_tbl(1).schedule_ship_date := p_atp_rec.requested_ship_date(J);
l_update_line_tbl(1).schedule_arrival_date := p_atp_rec.requested_arrival_date(J);
, p_x_line_tbl => l_update_line_tbl
, p_control_rec => l_control_rec
, p_x_header_rec => l_header_out_rec
, p_x_header_adj_tbl => l_header_adj_out_tbl
, p_x_header_scredit_tbl => l_header_scredit_out_tbl
, p_x_header_payment_tbl => l_header_payment_out_tbl
, p_x_line_adj_tbl => l_line_adj_out_tbl
, p_x_line_scredit_tbl => l_line_scredit_out_tbl
, p_x_line_payment_tbl => l_line_payment_out_tbl
, p_x_action_request_tbl => l_action_request_out_tbl
, p_x_lot_serial_tbl => l_lot_serial_tbl
, p_x_header_price_att_tbl => l_header_price_att_tbl
, p_x_header_adj_att_tbl => l_header_adj_att_tbl
, p_x_header_adj_assoc_tbl => l_header_adj_assoc_tbl
, p_x_line_price_att_tbl => l_line_price_att_tbl
, p_x_line_adj_att_tbl => l_line_adj_att_tbl
, p_x_line_adj_assoc_tbl => l_line_adj_assoc_tbl
);
oe_debug_pub.add( 'check_split_needed() substitute item : returned from process order to update to substitute item ' || l_return_status , 1 ) ;
for l_rec in (select * from oe_schedule_lines_temp)
LOOP
oe_debug_pub.add('check_split_needed() GLOBAL TEMP TABLE : ' || l_rec.line_id || ' : ' || l_rec.schedule_action_code,1);
l_insert_flag NUMBER;
l_insert_flag := 1;
l_insert_flag := 0;
IF (p_line_rec.operation = OE_GLOBALS.G_OPR_DELETE)
AND (p_line_rec.ato_line_id IS NOT NULL AND
NOT (p_line_rec.ato_line_id = p_line_rec.line_id AND
p_line_rec.item_type_code IN (OE_GLOBALS.G_ITEM_STANDARD,
OE_GLOBALS.G_ITEM_OPTION,
OE_GLOBALS.G_ITEM_INCLUDED))) THEN --9775352
x_atp_rec.ato_delete_flag(I) := 'Y';
oe_debug_pub.add( 'ATO DELETE FLAG : '
|| x_atp_rec.ato_delete_flag(I) , 1 ) ;
x_atp_rec.Insert_Flag(I) := l_insert_flag;
oe_debug_pub.add( 'INSERT FLAG IN ATP_REC : '||X_ATP_REC.INSERT_FLAG ( I ) , 3 ) ;
/* IF INV_GMI_RSV_BRANCH.Process_Branch -- INVCONV - delete this
(p_organization_id => p_old_line_rec.ship_from_org_id)
THEN
Update oe_order_lines_all
Set ordered_quantity = 0,
ordered_quantity2 = 0
Where line_id=p_old_line_rec.line_id;
OE_Item_Tbl.DELETE(l_line_id_mod); --7827737
l_model_rec.Insert_Flag := MRP_ATP_PUB.number_arr
(x_atp_rec.Insert_Flag(I));
x_atp_rec.Insert_Flag(I) :=
l_smc_rec.Insert_Flag(J);
Select line_id
Into l_config_line_id
From oe_order_lines_all
Where ato_line_id = p_line_tbl(cnt).ato_line_id
And item_type_code = 'CONFIG';
SELECT SET_NAME
INTO l_ship_set_name
FROM OE_SETS
WHERE set_id = p_x_line_rec.ship_set_id;
SELECT SET_NAME
INTO l_arrival_set_name
FROM OE_SETS
WHERE set_id = p_x_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_rec.error_code(J) ;
select meaning
into l_explanation
from mfg_lookups where
lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = p_atp_rec.error_code(J) ;
SELECT SET_NAME
INTO l_ship_set_name
FROM OE_SETS
WHERE set_id = p_x_line_rec.ship_set_id;
SELECT SET_NAME
INTO l_arrival_set_name
FROM OE_SETS
WHERE set_id = p_x_line_rec.arrival_set_id;
oe_debug_pub.add( '6378240 : inserted the line_id into temp table '||p_x_line_rec.line_id ||' ' ||p_x_line_rec.schedule_action_code);
insert into oe_schedule_lines_temp
(LINE_ID ,
SCHEDULE_ACTION_CODE)
values(p_x_line_rec.line_id,p_x_line_rec.schedule_action_code);
oe_debug_pub.add( '6378240 : INSERT ERROR ');
IF NOT OE_Internal_Requisition_Pvt.G_Update_ISO_From_Req
AND NOT OE_SALES_CAN_UTIL.G_IR_ISO_HDR_CANCEL THEN
IF FND_PROFILE.VALUE('POR_SYNC_NEEDBYDATE_OM') = 'YES' THEN
-- Modified for IR ISO Tracking bug 7667702
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Logging G_UPDATE_REQUISITION delayed request for date change',5);
, p_request_type => OE_GLOBALS.G_UPDATE_REQUISITION
, x_return_status => l_return_status
);
IF NOT OE_Schedule_GRP.G_ISO_Planning_Update THEN
IF l_debug_level > 0 THEN
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_rec.schedule_arrival_date,
p_x_line_rec.source_document_id,
p_x_line_rec.source_document_line_id);
g_atp_tbl.delete;
OE_inactive_demand_tbl.DELETE(l_line_id_mod); --7827737
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;
IF NOT OE_Internal_Requisition_Pvt.G_Update_ISO_From_Req
AND NOT OE_SALES_CAN_UTIL.G_IR_ISO_HDR_CANCEL THEN
-- AND FND_PROFILE.VALUE('POR_SYNC_NEEDBYDATE_OM') = 'YES' THEN
IF FND_PROFILE.VALUE('POR_SYNC_NEEDBYDATE_OM') = 'YES' THEN
-- Modified for IR ISO Tracking bug 7667702
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Logging G_UPDATE_REQUISITION delayed request for date change');
, p_request_type => OE_GLOBALS.G_UPDATE_REQUISITION
, x_return_status => l_return_status
);
IF NOT OE_Schedule_GRP.G_ISO_Planning_Update THEN
IF l_debug_level > 0 THEN
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);
G_ITEM_ORG_TABLE.DELETE;
select order_source_id, orig_sys_document_ref, orig_sys_line_ref,
orig_sys_shipment_ref, change_sequence, source_document_id,
source_document_line_id, source_document_type_id, header_id
into l_order_source_id, l_orig_sys_document_ref, l_orig_sys_line_ref,
l_orig_sys_shipment_ref, l_change_sequence, l_source_document_id,
l_source_document_line_id, l_source_document_type_id, l_header_id
from oe_order_lines
where line_id = p_line_id;
select meaning
into l_explanation
from mfg_lookups where
lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = p_atp_rec.error_code(J) ;
select meaning
into l_explanation
from mfg_lookups where
lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = p_atp_rec.error_code(J) ;
oe_debug_pub.add( '6378240 : inserted the line_id into temp table '||p_x_line_rec.line_id ||' ' ||p_x_line_rec.schedule_action_code);
insert into oe_schedule_lines_temp
(LINE_ID ,
SCHEDULE_ACTION_CODE)
values(p_x_line_rec.line_id,p_x_line_rec.schedule_action_code);
oe_debug_pub.add( '6378240 : INSERT ERROR ');
UPDATE OE_ORDER_LINES_ALL
SET override_atp_date_code = Null
WHERE header_id = p_line_rec.header_id
AND ato_line_id = p_line_rec.ato_line_id;
UPDATE OE_ORDER_LINES_ALL
SET override_atp_date_code = Null
WHERE header_id = p_line_rec.header_id
AND link_to_line_id = p_line_rec.line_id
AND item_type_code = 'INCLUDED';
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 secondary_uom_code ,primary_uom_code
INTO l_secondary_uom_code ,l_primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = g_atp_tbl(K).inventory_item_id
AND organization_id = g_atp_tbl(K).ship_from_org_id
AND ROWNUM=1;
SELECT secondary_uom_code ,primary_uom_code
INTO l_secondary_uom_code ,l_primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = g_atp_tbl(K).request_item_id
AND organization_id = g_atp_tbl(K).ship_from_org_id
AND ROWNUM=1;
, p_rsv_update => TRUE -- Going to increase reservation
, x_return_Status => x_return_status);
l_rsv_update BOOLEAN :=FALSE;
oe_debug_pub.add( 'RSCH: CALLING INVS UPDATE RESERVATION ' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
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(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
, p_over_reservation_flag => 2 -- 4715544
);
oe_debug_pub.add( 'AFTER CALLING INVS UPDATE_RESERVATION: ' || X_RETURN_STATUS , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
l_rsv_update := TRUE;
, p_rsv_update => l_rsv_update
, x_return_status => x_return_status);
, p_rsv_update => TRUE
, x_return_Status => x_return_status);
l_rsv_update := TRUE;
, p_rsv_update => l_rsv_update
, x_return_status => x_return_status);
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 /* MOAC_SQL_CHANGE*/ name, scheduling_level_code
INTO l_order_type,l_scheduling_level_code
FROM oe_order_types_v ot, oe_order_headers_all h
WHERE h.header_id = p_header_id AND
h.order_type_id = ot.order_type_id;
SELECT line_id
FROM oe_order_lines
WHERE top_model_line_id = p_x_line_rec.top_model_line_id
AND ato_line_id = p_x_line_rec.ato_line_id
AND open_flag = 'Y'
AND source_type_code = 'EXTERNAL';
oe_debug_pub.add( 'UPDATE SHIP DATE , EXTERNAL '|| P_X_LINE_REC.LINE_ID , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'AFTER Inserting data in OE_SETS_HISTORY table ' || x_return_status , 1 ) ;
Log_Delete_Set_Request
(p_header_id => p_x_line_rec.header_id,
p_line_id => p_x_line_rec.line_id,
p_set_id => nvl(l_old_line_rec.ship_set_id,l_old_line_rec.arrival_set_id),
x_return_status => x_return_status);
oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST IN SCHEDULE LINE' || X_RETURN_STATUS , 1 ) ;
AND p_x_line_rec.operation = 'UPDATE' THEN
IF p_x_line_rec.ship_model_complete_flag = 'Y'
THEN
Update oe_order_lines_all
Set firm_demand_flag = p_x_line_rec.firm_demand_flag
Where top_model_line_id = p_x_line_rec.top_model_line_id;
Update oe_order_lines_all
Set firm_demand_flag = p_x_line_rec.firm_demand_flag
Where ato_line_id = p_x_line_rec.ato_line_id;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists => False,
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 => x_return_status);
oe_debug_pub.add( 'After update global pic: UNEXP ERRORED OUT' , 1 ) ;
oe_debug_pub.add( 'After update global pic : ERRORED OUT' , 1 ) ;
Update oe_order_lines_all
Set arrival_set_id = Null,
ship_set_id = Null
Where Line_id = p_x_line_rec.line_id;
AND p_x_line_rec.operation = oe_globals.g_opr_update
AND NVL(oe_sys_parameters.Value('ONT_AUTO_SCH_SETS',p_x_line_rec.org_id),'Y') = 'N' THEN
x_return_status := FND_API.G_RET_STS_SUCCESS;
,p_delete => FND_API.G_TRUE
,x_return_status => x_return_status );
(p_line_rec.operation = OE_GLOBALS.G_OPR_DELETE OR
p_line_rec.operation = OE_GLOBALS.G_OPR_CREATE )
THEN
RETURN FALSE;
oe_debug_pub.add( 'N3 NOT CONFIG WITH DELETE AND CREATE OPR' , 1 ) ;
(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 ) ;
oe_debug_pub.add( 'REQUEST DATE IS ALSO CHANGED , UPDATE SCHEDULE' , 4 ) ;
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 meaning INTO l_explanation
FROM oe_lookups
WHERE lookup_type = 'SCH_FAIL_REASONS'
AND lookup_code = 'EXT_SCH_DOO';
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 ORGANIZATION_CODE
Into l_org_code
From Inv_Organization_Info_v --ORG_ORGANIZATION_DEFINITIONS
Where ORGANIZATION_ID = NVL(p_line_rec.ship_from_org_id,l_org_id);
SELECT line_Id
INTO l_config_id
FROM OE_ORDER_LINES_ALL
WHERE header_id = p_line_rec.header_id
AND (ato_line_id =p_line_rec.ato_line_id OR
(top_model_line_id = p_line_rec.top_model_line_id AND
Ship_model_complete_flag = 'Y'))
AND item_type_code = 'CONFIG';
oe_debug_pub.add( 'E23 USER DOES NOT HAVE AUTHORITY TO UPDATE SCHEDULE ATTRIBUTES' , 1 ) ;
SELECT 'Y' INTO l_found
FROM oe_order_lines
WHERE top_model_line_id = p_line_rec.top_model_line_id
AND override_atp_date_code = 'Y'
AND rownum <2;
SELECT count('x')
INTO l_override
FROM oe_order_lines_all
WHERE header_id = p_line_rec.header_id
AND (ship_set_id = p_line_rec.ship_set_id
OR arrival_set_id = p_line_rec.arrival_set_id)
AND override_atp_date_code = 'Y';
oe_debug_pub.add( 'E25 USER DOES NOT HAVE AUTHORITY TO UPDATE SCHEDULE ATTRIBUTES ON A SET WITH AN OVERRIDDEN LINE' , 1 ) ;
SELECT 'Y'
INTO l_overridden
FROM oe_order_lines_all
WHERE header_id = p_line_rec.header_id
AND line_id <> p_line_rec.line_id
AND ship_set_id = p_line_rec.ship_set_id
AND override_atp_date_code = 'Y'
AND schedule_ship_date <> p_line_rec.schedule_ship_date
AND rownum < 2;
SELECT 'Y'
INTO l_overridden
FROM oe_order_lines_all
WHERE header_id = p_line_rec.header_id
AND line_id <> p_line_rec.line_id
AND arrival_set_id = p_line_rec.arrival_set_id
AND override_atp_date_code = 'Y'
AND schedule_arrival_date <> p_line_rec.schedule_arrival_date
AND rownum < 2;
SELECT 'Y'
INTO l_found
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_LINE_ID = p_line_rec.line_id
AND SOURCE_CODE = 'OE'
AND RELEASED_STATUS IN ('Y', 'C');
l_update_flag VARCHAR2(1):= FND_API.G_TRUE;
l_update_flag := FND_API.G_FALSE;
l_update_flag := FND_API.G_FALSE;
l_update_flag := FND_API.G_FALSE;
l_update_flag := FND_API.G_FALSE;
l_update_flag := FND_API.G_FALSE;
IF l_update_flag = FND_API.G_TRUE
THEN
/*
Call_Process_Order(p_old_line_rec => p_old_line_rec,
p_x_line_rec => p_x_line_rec,
p_write_to_db => p_write_to_db,
p_caller => p_caller,
x_return_status => x_return_status);
SELECT shipping_interfaced_flag,firm_demand_flag,lock_control
INTO l_shipping_interfaced_flag,l_firm_demand_flag,l_lock_control
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=p_x_line_rec.line_id;
END IF; -- update flag
Update_PO(p_x_line_rec.schedule_arrival_date,
p_x_line_rec.source_document_id,
p_x_line_rec.source_document_line_id);
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;
SELECT order_type_id
INTO l_order_type_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT NAME
INTO l_order_type_name
FROM OE_TRANSACTION_TYPES_TL
WHERE TRANSACTION_TYPE_ID = l_order_type_id
AND language = (select language_code
from fnd_languages
where installed_flag = 'B');
SELECT S.SALES_ORDER_ID
INTO l_sales_order_id
FROM MTL_SALES_ORDERS S,
OE_ORDER_HEADERS_ALL H
WHERE S.SEGMENT1 = TO_CHAR(H.ORDER_NUMBER)
AND S.SEGMENT2 = l_order_type_name
AND S.SEGMENT3 = l_source_code
AND H.HEADER_ID = p_header_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' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
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' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
, 'Insert_Into_Mtl_Sales_Orders'
);
END Insert_Into_Mtl_Sales_Orders;
updated to the database.
--------------------------------------------------------------------- */
Procedure call_process_order
( p_x_old_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
, p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
, p_control_rec IN OE_GLOBALS.control_rec_type
, p_caller IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2)
IS
is_set_recursion VARCHAR2(1) := 'Y';
updated to the database.
--------------------------------------------------------------------- */
Procedure Process_Group_of_Lines
( p_x_old_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
, p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
, p_caller IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_process_requests BOOLEAN;
oe_debug_pub.add( 'BEFORE DOING DIRECT UPDATE IN GROUP' , 1 ) ;
p_x_line_tbl(I).last_update_date := SYSDATE;
p_x_line_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
p_x_line_tbl(I).last_update_login := FND_GLOBAL.LOGIN_ID;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_old_line_rec => p_x_old_line_tbl(I),
p_line_rec =>p_x_line_tbl(I),
p_line_id => p_x_line_tbl(I).line_id,
x_index => l_index,
x_return_status => x_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_SCHEDULE_UTIL.PROCESS_GROUP_OF_LINE IS: ' || X_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date := p_x_line_tbl(I).last_update_date;
OE_ORDER_UTIL.g_line_tbl(l_index).last_updated_by := p_x_line_tbl(I).last_updated_by;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login := p_x_line_tbl(I).last_update_login;
UPDATE OE_ORDER_LINES
SET
ship_from_org_id = p_x_line_tbl(I).ship_from_org_id
,schedule_ship_date = p_x_line_tbl(I).schedule_ship_date
,schedule_arrival_date = p_x_line_tbl(I).schedule_arrival_date
,delivery_lead_time = p_x_line_tbl(I).delivery_lead_time
,mfg_lead_time = p_x_line_tbl(I).mfg_lead_time
,shipping_method_code = p_x_line_tbl(I).shipping_method_code
,schedule_status_code = p_x_line_tbl(I).schedule_status_code
,visible_demand_flag = p_x_line_tbl(I).visible_demand_flag
,latest_acceptable_date = p_x_line_tbl(I).latest_acceptable_date
,Original_Inventory_Item_Id
= p_x_line_tbl(I).Original_Inventory_Item_Id
,Original_item_identifier_Type
= p_x_line_tbl(I).Original_item_identifier_Type
,Original_ordered_item_id = p_x_line_tbl(I).Original_ordered_item_id
,Original_ordered_item = p_x_line_tbl(I).Original_ordered_item
,last_update_date = p_x_line_tbl(I).last_update_date
,last_updated_by = p_x_line_tbl(I).last_updated_by
,last_update_login = p_x_line_tbl(I).last_update_login
,lock_control = p_x_line_tbl(I).lock_control
WHERE LINE_ID = p_x_line_tbl(I).line_id;
oe_debug_pub.add( 'AFTER DOING DIRECT UPDATE' , 1 ) ;
updated to the database.
New code : If the warehouse is changed on the line due to scheduling
we will follow older approch. If warehouse is not changed ont
the line then call security based on the operation. Call new API
for clear-dep and defaulting. Based on the output from the new api
either call process order or do a direct update.
p_call_prn is call process_request and notify.
--------------------------------------------------------------------- */
Procedure Process_Line
( p_old_line_rec IN OE_ORDER_PUB.line_rec_type
, p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
, p_caller IN VARCHAR2
, p_call_prn IN BOOLEAN := TRUE
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_process_requests BOOLEAN;
oe_debug_pub.add( 'DIRECT UPDATE FOR A CANCELLED LINE' || P_X_LINE_REC.LINE_ID , 1 ) ;
p_x_line_rec.last_update_date := SYSDATE;
p_x_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
p_x_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
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
,firm_demand_flag = p_x_line_rec.firm_demand_flag
,earliest_ship_date = p_x_line_rec.earliest_ship_date
,last_update_date = p_x_line_rec.last_update_date
,last_updated_by = p_x_line_rec.last_updated_by
,last_update_login = p_x_line_rec.last_update_login
,lock_control = p_x_line_rec.lock_control
WHERE LINE_ID = p_x_line_rec.line_id;
oe_debug_pub.add( 'AFTER DOING DIRECT UPDATE ON A CANCELLED LINE' , 1 ) ;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
IF p_x_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'IT IS UPDATE , CALL SECURITY' ) ;
END IF; -- operation update.
OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := 'N';
oe_debug_pub.add( 'DIRECT/PO ' || OE_GLOBALS.G_ATTR_UPDATED_BY_DEF , 1 ) ;
IF OE_GLOBALS.G_ATTR_UPDATED_BY_DEF = 'Y' THEN
l_control_rec.controlled_operation := TRUE;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'BEFORE DOING DIRECT UPDATE' , 1 ) ;
p_x_line_rec.last_update_date := SYSDATE;
p_x_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
p_x_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
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 = p_x_line_rec.last_update_date
,last_updated_by = p_x_line_rec.last_updated_by
,last_update_login = p_x_line_rec.last_update_login
,lock_control = p_x_line_rec.lock_control
WHERE LINE_ID = p_x_line_rec.line_id; --2806483 Added Fright_carrier_code
oe_debug_pub.add( 'AFTER DOING DIRECT UPDATE' , 1 ) ;
END IF; -- OE_GLOBALS.G_ATTR_UPDATED_BY_DEF.
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>True,--changed for bug 8737932
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 => x_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_SCHEDULE_UTIL.PROCESS_LINE IS: ' || X_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;
g_atp_tbl.Delete;
g_atp_tbl.Delete;
SELECT secondary_uom_code ,primary_uom_code
INTO l_secondary_uom_code ,l_primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = g_atp_tbl(K).inventory_item_id
AND organization_id = g_atp_tbl(K).ship_from_org_id
AND ROWNUM=1;
SELECT Nvl(fulfillment_base,'P')
INTO l_fulfillment_base
FROM oe_order_lines_all
WHERE line_id = g_atp_tbl(K).line_id;
SELECT secondary_uom_code ,primary_uom_code
INTO l_secondary_uom_code ,l_primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = g_atp_tbl(K).request_item_id
AND organization_id = g_atp_tbl(K).ship_from_org_id
AND ROWNUM=1;
Procedure Delete_Row(p_line_id IN NUMBER)
IS
l_line_rec OE_ORDER_PUB.line_rec_type;
oe_debug_pub.add( 'ENTERING OE_SCHEDULE_UTIL.DELETE_ROW' , 1 ) ;
Log_Delete_Set_Request
(p_header_id => l_line_rec.header_id,
p_line_id => l_line_rec.line_id,
p_set_id => nvl(l_line_rec.ship_set_id,l_line_rec.arrival_set_id),
x_return_status => l_return_status);
oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST ' || L_RETURN_STATUS , 1 ) ;
OE_AUDIT_HISTORY_PVT.DELETE_SET_HISTORY( p_line_id => l_line_rec.line_id, x_return_status => l_return_status);
oe_debug_pub.add( 'AFTER DELETE_SET_HISTORY ' || l_return_status , 1 ) ;
Select 'Y'
Into l_ato_exists
From oe_order_lines_all
Where line_id = l_line_rec.ato_line_id;
p_param12 => 'DELETE',
p_param14 => l_line_rec.ship_set_id,
p_param13 => l_line_rec.arrival_set_id,
p_param15 => l_line_rec.ship_model_complete_flag,
p_param25 => l_line_rec.header_id,
x_return_status => l_return_status);
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add( 'EXITING OE_SCHEDULE_UTIL.DELETE_ROW' , 1 ) ;
, 'Delete_row'
);
END Delete_Row;
l_rsv_update BOOLEAN :=FALSE; --8706868
l_line_tbl(K).operation := oe_globals.g_opr_update;
l_local_line_tbl.delete;
l_line_tbl(K).operation := oe_globals.g_opr_update;
l_local_line_tbl.delete;
l_line_tbl(K).operation := oe_globals.g_opr_update;
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_line_tbl(K).operation := oe_globals.g_opr_update;
l_local_line_tbl.delete;
-- AND l_line_tbl(J).operation = oe_globals.g_opr_update THEN
l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(l_line_tbl(J).HEADER_ID);
l_line_tbl(J).operation := oe_globals.g_opr_update;
l_mrp_line_tbl(I).operation := oe_globals.g_opr_update; --14350185:MRP might return different warehouse or date
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 VARCHAR2)
IS
l_rsv_qty NUMBER := 0;
SELECT schedule_ship_date,schedule_arrival_date,ship_from_org_id
INTO p_x_line_tbl(I).schedule_ship_date,p_x_line_tbl(I).schedule_arrival_date,
p_x_line_tbl(I).ship_from_org_id
FROM OE_ORDER_LINES_ALL
WHERE line_id=p_x_line_tbl(I).line_id;
SELECT schedule_ship_date,schedule_arrival_date,ship_from_org_id
INTO p_x_line_tbl(I).schedule_ship_date,p_x_line_tbl(I).schedule_arrival_date,p_x_line_tbl(I).ship_from_org_id
FROM OE_ORDER_LINES_ALL
WHERE line_id=p_x_line_tbl(I).line_id;
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( 'BEFORE DELETE ' || K || '/' || L_RSV_TBL ( K ) .RESERVATION_QUANTITY , 1 ) ;
oe_debug_pub.add( 'BEFORE DELETE Primary ' || K || '/' || L_RSV_TBL ( K ) .PRIMARY_RESERVATION_QUANTITY , 1 ) ;
l_rsv_tbl.delete(K);
END IF; -- If operation on the line was UPDATE
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 NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2)
IS
/*
l_line_rec OE_ORDER_PUB.line_rec_type;
oe_debug_pub.add( 'ENTERING UPDATE_RESULTS_FROM_BACKLOG_WB' , 1 ) ;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
Update_PO(l_line_tbl(I).schedule_arrival_date,
l_line_tbl(I).source_document_id,
l_line_tbl(I).source_document_line_id);
oe_debug_pub.add( 'EXITING UPDATE_RESULTS_FROM_BACKLOG_WB' , 1 ) ;
END Update_Results_from_backlog_wb;
SELECT sum(s.onhand_order_qty), sum(s.COMMITTEDSALES_QTY), sum(s.COMMITTEDPROD_QTY)
FROM ic_summ_inv s
WHERE s.item_id = p_itemid AND
s.whse_code = (Select wh.whse_code
from ic_whse_mst wh
where wh.MTL_ORGANIZATION_ID = p_organisation_id);
SELECT s.onhand_order_qty, s.COMMITTEDSALES_QTY, s.COMMITTEDPROD_QTY
FROM ic_summ_inv s
WHERE s.item_id = p_itemid AND
s.whse_code = (Select wh.whse_code
from ic_whse_mst wh
where wh.MTL_ORGANIZATION_ID = p_organisation_id) AND
s.qc_grade = (SELECT preferred_grade
FROM oe_order_lines
WHERE line_id = p_line_id);
SELECT grade_ctl
FROM ic_item_mst
WHERE item_id = p_item_id;
SELECT preferred_grade
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT '1'
INTO l_Pick_status
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_CODE = 'OE'
AND SOURCE_LINE_ID = p_line_id
AND RELEASED_STATUS IN ('S','Y','C');
gets updated, but reservation is still for the original UOM (non-primary). So unreservation in this case
will be done using primary reservation quantity
*/
IF l_rsv_tbl(1).primary_reservation_quantity > p_quantity_to_unreserve
AND NOT oe_globals.equal(p_line_rec.order_quantity_uom,l_rsv_tbl(1).reservation_uom_code)
THEN
l_qty_to_unreserve := l_rsv_tbl(1).primary_reservation_quantity ;
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 => x_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: ' || X_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
, 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
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
oe_debug_pub.add( 'AFTER INVS UPDATE_RESERVATION: ' || X_RETURN_STATUS , 1 ) ;
SELECT header_id
FROM oe_order_lines_all
WHERE line_id = p_x_line_rec.line_id;
l_insert_code NUMBER;
l_insert_flag VARCHAR2(240);
SELECT line_id,
header_id,
inventory_item_id,
ordered_item,
sold_to_org_id,
ship_to_org_id,
ship_from_org_id,
demand_class_code,
ordered_quantity,
order_quantity_uom,
latest_acceptable_date,
line_number,
shipment_number,
option_number,
delivery_lead_time,
request_date,
promise_date,
project_id,
task_id,
shipping_method_code,
ship_set_id,
arrival_set_id,
link_to_line_id,
ato_line_id,
item_type_code,
top_model_line_id,
component_sequence_id,
component_code
FROM OE_ORDER_LINES_ALL
where header_id = p_header_id AND
(arrival_set_id = p_arrival_set_id OR
ship_set_id = p_ship_set_id OR
top_model_line_id = p_top_model_line_id OR
ato_line_id = p_ato_line_id) AND
line_id <> p_line_id AND
item_type_code <> 'CONFIG';
SELECT header_id,
inventory_item_id,
ordered_item,
sold_to_org_id,
ship_to_org_id,
ship_from_org_id,
demand_class_code,
ordered_quantity,
order_quantity_uom,
latest_acceptable_date,
line_number,
shipment_number,
option_number,
ship_model_complete_flag,
top_model_line_id,
ato_line_id,
delivery_lead_time,
request_date,
promise_date,
project_id,
task_id,
shipping_method_code,
ship_set_id,
arrival_set_id,
item_type_code,
link_to_line_id,
component_code,
component_sequence_id
INTO l_header_id,
l_inventory_item_id,
l_ordered_item,
l_sold_to_org_id,
l_ship_to_org_id,
l_ship_from_org_id,
l_demand_class,
l_quantity_ordered,
l_uom_code,
l_latest_acceptable_date,
l_line_number,
l_shipment_number,
l_option_number,
l_smc_flag,
l_top_model_line_id,
l_ato_line_id,
l_delivery_lead_time,
l_request_date,
l_promise_date,
l_project_id,
l_task_id,
l_ship_method,
l_ship_set_id,
l_arrival_set_id,
l_item_type_code,
l_link_to_line_id,
l_component_code,
l_component_sequence_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT ORDER_NUMBER ,ORDER_DATE_TYPE_CODE
INTO l_order_number,l_order_date_type_code
FROM oe_order_headers
WHERE header_id=l_header_id;
SELECT NAME
INTO l_customer_name
FROM OE_SOLD_TO_ORGS_V
WHERE organization_id = l_sold_to_org_id;
SELECT NAME
INTO l_customer_location
FROM OE_SHIP_TO_ORGS_V
WHERE organization_id = l_ship_to_org_id;
SELECT SET_NAME
INTO l_ship_set_name
FROM OE_SETS
WHERE set_id = l_ship_set_id;
SELECT SET_NAME
INTO l_arrival_set_name
FROM OE_SETS
WHERE set_id = l_arrival_set_id;
Select line_id
Into l_config_line_id
From oe_order_lines_all
Where ato_line_id = l_line_id
And item_type_code = 'CONFIG';
SELECT instance_id
INTO l_instance_id
FROM mrp_ap_apps_instances;
l_insert_flag := fnd_profile.value('MRP_ATP_CALC_SD');
IF nvl(l_insert_flag,'N') = 'Y' THEN
l_insert_code := 1;
l_insert_code := 2;
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,
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,
ATP_LEAD_TIME,
ORDER_NUMBER,
OLD_SOURCE_ORGANIZATION_ID,
OLD_DEMAND_CLASS,
PROJECT_ID,
TASK_ID,
PROJECT_NUMBER,
TASK_NUMBER,
SHIP_METHOD_TEXT,
TOP_MODEL_LINE_ID,
ATO_MODEL_LINE_ID,
PARENT_LINE_ID,
VALIDATION_ORG,
COMPONENT_SEQUENCE_ID,
COMPONENT_CODE,
INCLUDED_ITEM_FLAG,
LINE_NUMBER,
CONFIG_ITEM_LINE_ID
)
VALUES
(l_inventory_item_id,
l_instance_id,
l_ship_from_org_id, --null -- Bug 2913742
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,
l_requested_ship_date,
l_requested_arrival_date,
l_latest_acceptable_date,
l_delivery_lead_time,
l_freight_carrier,
l_insert_code,
l_ship_method,
l_demand_class,
l_ship_set_name,
l_ship_set_id_1,
l_arrival_set_name,
l_arrival_set_id,
null, -- OVERRIDE_FLAG
l_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,
l_customer_name,
l_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,
l_atp_lead_time,
l_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,
l_top_model_line_id,
l_ato_model_line_id,
l_link_to_line_id,
OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'),
l_component_sequence_id,
l_component_code,
1 , --l_included_item_flag
l_conc_line_number,
l_config_line_id
);
SELECT NAME
INTO l_customer_name
FROM OE_SOLD_TO_ORGS_V
WHERE organization_id = l_sold_to_org_id;
SELECT NAME
INTO l_customer_location
FROM OE_SHIP_TO_ORGS_V
WHERE organization_id = l_ship_to_org_id;
SELECT SET_NAME
INTO l_ship_set_name
FROM OE_SETS
WHERE set_id = l_ship_set_id;
SELECT SET_NAME
INTO l_arrival_set_name
FROM OE_SETS
WHERE set_id = l_arrival_set_id;
Select line_id
Into l_config_line_id
From oe_order_lines_all
Where ato_line_id = l_line_id
And item_type_code = 'CONFIG';
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,
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,
ATP_LEAD_TIME,
ORDER_NUMBER,
OLD_SOURCE_ORGANIZATION_ID,
OLD_DEMAND_CLASS,
PROJECT_ID,
TASK_ID,
PROJECT_NUMBER,
TASK_NUMBER,
SHIP_METHOD_TEXT,
TOP_MODEL_LINE_ID,
ATO_MODEL_LINE_ID,
PARENT_LINE_ID,
VALIDATION_ORG,
COMPONENT_SEQUENCE_ID,
COMPONENT_CODE,
INCLUDED_ITEM_FLAG,
LINE_NUMBER,
CONFIG_ITEM_LINE_ID
)
VALUES
(l_inventory_item_id,
l_instance_id,
l_ship_from_org_id, --null -- Bug 2913742
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,
l_requested_ship_date,
l_requested_arrival_date,
l_latest_acceptable_date,
l_delivery_lead_time,
l_freight_carrier,
l_insert_code,
l_ship_method,
l_demand_class,
l_ship_set_name,
l_ship_set_id_1,
l_arrival_set_name,
l_arrival_set_id,
null, -- OVERRIDE_FLAG
l_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,
l_customer_name,
l_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,
l_atp_lead_time,
l_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,
l_top_model_line_id,
l_ato_model_line_id,
l_link_to_line_id,
OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'),
l_component_sequence_id,
l_component_code,
1 , --l_included_item_flag
l_conc_line_number,
l_config_line_id
);
l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
OE_Set_Util.Update_Set
(p_Set_Id => p_request_rec.param2,
p_Shipping_Method_Code => p_request_rec.param3,
X_Return_Status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
select line_id , schedule_action_code
from oe_schedule_lines_temp;
l_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE; --6715950
OE_SCHEDULE_UTIL.OE_Delayed_Schedule_line_tbl.delete;
SELECT PICK_STATUS
FROM WSH_DELIVERY_LINE_STATUS_V
WHERE SOURCE_CODE = 'OE'
AND SOURCE_LINE_ID = OE_LINE_SECURITY.g_record.line_id
AND PICK_STATUS NOT IN ('N', 'R', 'X', 'B'); --Added 'B', (Backordered Status) for bug 8521082
SELECT shippable_item_flag
INTO l_shippable_flag
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = p_new_inventory_item_id
AND ORGANIZATION_ID = p_new_ship_from_org_id;
SELECT schedule_ship_date,
schedule_arrival_date
INTO l_ship_date,
l_arrival_date
FROM oe_sets
WHERE set_id=p_set_id;