[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_Attributes',WSH_DEBUG_SV.C_PROC_LEVEL);
Update_Attributes(
p_line_rec => p_line_rec,
p_action_prms => p_action_prms,
p_dd_list => p_dd_list,
p_dd_id_unassigned => l_dd_id_unassigned,
p_wt_vol_dd_id => l_wt_vol_dd_id,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_Quantity',WSH_DEBUG_SV.C_PROC_LEVEL);
Update_quantity(
p_line_rec => p_line_rec,
p_action_prms => p_action_prms,
p_dd_id_unassigned => l_dd_id_unassigned,
p_wt_vol_dd_id => l_wt_vol_dd_id,
x_return_status => l_return_status);
l_dd_id_unassigned.delete;
l_wt_vol_dd_id.delete;
Procedure Update_Attributes(
p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
p_action_prms IN WSH_BULK_TYPES_GRP.action_parameters_rectype,
p_dd_list IN dd_list_type,
p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type,
p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_version CONSTANT NUMBER := 1.0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ATTRIBUTES';
SAVEPOINT Update_Attributes_PVT;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_dd_attributes',WSH_DEBUG_SV.C_PROC_LEVEL);
Update_dd_attributes(
p_line_rec => p_line_rec,
p_action_prms => p_action_prms,
p_dd_id => null,
p_dd_id_unassigned => p_dd_id_unassigned,
p_wt_vol_dd_id => p_wt_vol_dd_id,
p_line_rec_index => i,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_dd_attributes',WSH_DEBUG_SV.C_PROC_LEVEL);
Update_dd_attributes(
p_line_rec => p_line_rec,
p_action_prms => p_action_prms,
p_dd_id => p_dd_list.delivery_detail_id(i),
p_dd_id_unassigned => p_dd_id_unassigned,
p_wt_vol_dd_id => p_wt_vol_dd_id,
p_line_rec_index => p_line_rec.header_id.COUNT,
x_return_status => l_return_status);
ROLLBACK TO Update_Attributes_PVT;
ROLLBACK TO Update_Attributes_PVT;
WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_CMG_PVT.Update_Attributes',l_module_name);
END Update_Attributes;
Procedure Update_dd_attributes(
p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
p_action_prms IN WSH_BULK_TYPES_GRP.action_parameters_rectype,
p_dd_id IN NUMBER,
p_line_rec_index IN NUMBER,
p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type,
p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
x_return_status OUT NOCOPY VARCHAR2) IS
-- Cursor to fetch the delivery grouping attributes and other critical attributes
-- like shipping control, freight term,fob,ultimate_drop_off_date of delivery.
Cursor C_dd_attr(p_line_location_id NUMBER, p_delivery_detail_id NUMBER,
p_header_id NUMBER,p_line_id NUMBER)
IS
SELECT
WDD.delivery_detail_id,
WDD.ship_to_location_id,
WDD.organization_id,
WDD.ship_from_location_id,
WDD.customer_id,
WDD.date_requested,
WDD.date_scheduled,
WDD.fob_code dd_fob,
WDD.freight_terms_code dd_fgt,
WND.routing_response_id,
WDD.shipping_control,
WND.delivery_id,
WND.freight_terms_code del_fgt,
WND.ultimate_dropoff_date del_date,
WND.ultimate_dropoff_location_id del_location,
/*J-IB-ANJ*/
WDD.earliest_dropoff_date edd,
WDD.latest_dropoff_date ldd,
WDD.last_update_Date
FROM
WSH_DELIVERY_DETAILS WDD,
wsh_delivery_assignments_v WDA,
WSH_NEW_DELIVERIES WND
WHERE
WDD.Delivery_Detail_id = WDA.Delivery_Detail_id AND
WDA.Delivery_id = WND.Delivery_id(+) AND
WDD.released_status = 'X' AND
WDD.PO_SHIPMENT_LINE_ID = p_line_location_id AND
WDD.source_code = 'PO' AND
WDD.source_header_id = nvl(p_header_id,WDD.source_header_id) AND
WDD.source_line_id = p_line_id AND
WDD.delivery_detail_id = NVL(p_delivery_detail_id, WDD.DELIVERY_DETAIL_ID);
SELECT
inventory_item_id,
delivery_detail_id,
source_header_id,
source_blanket_reference_id,
source_line_id,
ship_from_site_id,
customer_item_id,
source_line_type_code,
sold_to_contact_id,
vendor_id,
item_description,
hazard_class_id,
country_of_origin,
ship_to_location_id,
ship_to_contact_id,
deliver_to_location_id,
deliver_to_contact_id,
intmed_ship_to_location_id,
intmed_ship_to_contact_id,
hold_code,
ship_tolerance_above,
ship_tolerance_below,
revision,
date_requested,
date_scheduled,
ship_method_code,
carrier_id,
freight_terms_code,
fob_code,
supplier_item_number,
customer_prod_seq,
customer_dock_code,
cust_model_serial_number,
customer_job,
customer_production_line,
organization_id,
ship_model_complete_flag,
top_model_line_id,
source_header_number,
source_header_type_name,
cust_po_number,
ato_line_id,
shipping_instructions,
packing_instructions,
org_id,
source_line_number,
unit_price,
currency_code,
preferred_grade,
po_shipment_line_number,
source_blanket_reference_num,
po_revision_number,
release_revision_number,
earliest_dropoff_date,
latest_dropoff_date
FROM wsh_delivery_details
WHERE source_code = 'PO'
AND po_shipment_line_id = p_line_location_id AND
source_header_id = p_header_id AND
source_line_id = p_line_id AND
delivery_detail_id = NVL(p_delivery_detail_id, DELIVERY_DETAIL_ID);
SELECT
WTS.STOP_ID,
WTS.planned_arrival_date Stop_date ,
WTS.STOP_LOCATION_ID
FROM wsh_delivery_assignments_v WDA,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WDG,
WSH_TRIP_STOPS WTS
WHERE wda.delivery_detail_id = p_dd_id AND
WDA.DELIVERY_ID = WND.DELIVERY_ID AND
WDG.DELIVERY_ID = WND.DELIVERY_ID AND
WTS.STOP_ID = WDG.DROP_OFF_STOP_ID AND
WTS.STOP_LOCATION_ID = WND.ULTIMATE_DROPOFF_LOCATION_ID;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DD_ATTRIBUTES';
SAVEPOINT Update_dd_Attributes_PVT;
WSH_DEBUG_SV.log(l_module_name,'v_dd_attr last_update_date is ',v_dd_attr.last_update_date);
update wsh_delivery_details
set shipping_control = p_line_rec.shipping_control(p_line_rec_index),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE po_shipment_line_id = p_line_rec.po_shipment_line_id(p_line_rec_index)
AND source_header_id = p_line_rec.header_id(p_line_rec_index) AND
source_line_id = p_line_rec.line_id(p_line_rec_index) AND
source_code = 'PO' AND
((p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NULL AND
source_blanket_reference_id IS NULL) OR
(p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NOT NULL
AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(p_line_rec_index))) AND
delivery_detail_id = v_dd_attr.delivery_detail_id AND
released_status = 'X' AND
container_flag = 'N' AND
last_update_date = v_dd_attr.last_update_date;
-- it is directly updated to wsh_delivery_details without checking for any contions
-- other than line status which should be 'X'(OPEN).
update wsh_delivery_details
set shipping_control = p_line_rec.shipping_control(p_line_rec_index),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE po_shipment_line_id = p_line_rec.po_shipment_line_id(p_line_rec_index)
AND source_header_id = p_line_rec.header_id(p_line_rec_index) AND
source_line_id = p_line_rec.line_id(p_line_rec_index) AND
source_code = 'PO' AND
((p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NULL AND
source_blanket_reference_id IS NULL) OR
(p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NOT NULL
AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(p_line_rec_index))) AND
delivery_detail_id = v_dd_attr.delivery_detail_id AND
released_status = 'X' AND
container_flag = 'N' AND
last_update_date = v_dd_attr.last_update_date;
UPDATE WSH_DELIVERY_DETAILS
SET
source_header_id = p_line_rec.header_id(p_line_rec_index),
source_blanket_reference_id=p_line_rec.source_blanket_reference_id(p_line_rec_index),
source_line_id=p_line_rec.line_id(p_line_rec_index),
ship_from_site_id = p_line_rec.ship_from_site_id(p_line_rec_index),
customer_item_id = p_line_rec.customer_item_id(p_line_rec_index),
source_line_type_code = p_line_rec.source_line_type_code(p_line_rec_index),
sold_to_contact_id=p_line_rec.sold_to_contact_id(p_line_rec_index),
vendor_id=p_line_rec.vendor_id(p_line_rec_index),
inventory_item_id=p_line_rec.inventory_item_id(p_line_rec_index),
item_description=p_line_rec.item_description(p_line_rec_index),
hazard_class_id=p_line_rec.hazard_class_id(p_line_rec_index),
country_of_origin=p_line_rec.country_of_origin(p_line_rec_index),
ship_to_location_id=p_line_rec.ship_to_location_id(p_line_rec_index),
ship_to_contact_id=p_line_rec.ship_to_contact_id(p_line_rec_index),
ship_to_site_use_id=p_line_rec.ship_to_org_id(p_line_rec_index),
deliver_to_location_id=p_line_rec.deliver_to_location_id(p_line_rec_index),
deliver_to_contact_id=p_line_rec.deliver_to_contact_id(p_line_rec_index),
deliver_to_site_use_id=p_line_rec.deliver_to_org_id(p_line_rec_index),
intmed_ship_to_location_id=p_line_rec.intmed_ship_to_location_id(p_line_rec_index),
intmed_ship_to_contact_id=p_line_rec.intermed_ship_to_contact_id(p_line_rec_index),
hold_code=p_line_rec.hold_code(p_line_rec_index),
ship_tolerance_above=p_line_rec.ship_tolerance_above(p_line_rec_index),
ship_tolerance_below=p_line_rec.ship_tolerance_below(p_line_rec_index),
revision=p_line_rec.revision(p_line_rec_index),
date_requested = p_line_rec.request_date(p_line_rec_index),
date_scheduled= p_line_rec.schedule_ship_date(p_line_rec_index),
/*J-IB-ANJ*/
-- Start - Changed for Bug#: 3145863
/* earliest_dropoff_date = l_del_edd,
latest_dropoff_date = l_del_ldd, */
-- End - Changed for Bug#: 3145863
--J-IB-ANJ
earliest_dropoff_date = l_Earliest_delivery_Date,
latest_dropoff_date = l_latest_delivery_date,
ship_method_code=p_line_rec.shipping_method_code(p_line_rec_index),
carrier_id=p_line_rec.carrier_id(p_line_rec_index),
freight_terms_code=p_line_rec.freight_terms_code(p_line_rec_index),
fob_code=p_line_rec.fob_point_code(p_line_rec_index),
supplier_item_number=p_line_rec.supplier_item_num(p_line_rec_index),
customer_prod_seq=p_line_rec.cust_production_seq_num(p_line_rec_index),
customer_dock_code=p_line_rec.customer_dock_code(p_line_rec_index),
cust_model_serial_number=p_line_rec.cust_model_serial_number(p_line_rec_index),
customer_job=p_line_rec.customer_job(p_line_rec_index),
customer_production_line=p_line_rec.customer_production_line(p_line_rec_index),
/*net_weight=p_line_rec.net_weight(p_line_rec_index),
weight_uom_code=p_line_rec.weight_uom_code(p_line_rec_index),
volume=p_line_rec.volume(p_line_rec_index),
volume_uom_code=p_line_rec.volume_uom_code(p_line_rec_index),*/
organization_id=p_line_rec.organization_id(p_line_rec_index),
ship_model_complete_flag=p_line_rec.ship_model_complete_flag(p_line_rec_index),
top_model_line_id=p_line_rec.top_model_line_id(p_line_rec_index),
source_header_number=p_line_rec.source_header_number(p_line_rec_index),
source_header_type_name=p_line_rec.source_header_type_name(p_line_rec_index),
cust_po_number=p_line_rec.cust_po_number(p_line_rec_index),
ato_line_id=p_line_rec.ato_line_id(p_line_rec_index),
-- tracking_number=p_line_rec.tracking_number(p_line_rec_index),
shipping_instructions=p_line_rec.shipping_instructions(p_line_rec_index),
packing_instructions=p_line_rec.packing_instructions(p_line_rec_index),
org_id=p_line_rec.org_id(p_line_rec_index),
source_line_number=p_line_rec.source_line_number(p_line_rec_index),
unit_price=p_line_rec.unit_list_price(p_line_rec_index),
currency_code=p_line_rec.currency_code(p_line_rec_index),
preferred_grade=p_line_rec.preferred_grade(p_line_rec_index),
po_shipment_line_id=p_line_rec.po_shipment_line_id(p_line_rec_index),
po_revision_number=p_line_rec.po_revision(p_line_rec_index),
release_revision_number=p_line_rec.release_revision(p_line_rec_index),
po_shipment_line_number=p_line_rec.po_shipment_line_number(p_line_rec_index),
source_blanket_reference_num=p_line_rec.source_blanket_reference_num(p_line_rec_index),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE po_shipment_line_id = p_line_rec.po_shipment_line_id(p_line_rec_index) AND
delivery_detail_id = nvl(p_dd_id,delivery_detail_id)
AND source_header_id = p_line_rec.header_id(p_line_rec_index) AND
source_line_id = p_line_rec.line_id(p_line_rec_index) ANd
source_code = 'PO' AND
((p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NULL AND
source_blanket_reference_id IS NULL) OR
(p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NOT NULL
AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(p_line_rec_index))) AND
released_status = 'X' AND
container_flag = 'N';
l_tp_del_id.delete;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS. update_freight_terms',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERY_ACTIONS. update_freight_terms
( p_delivery_id => l_del_fgt_terms_tbl(i),
p_action_code => 'ASSIGN',
x_freight_terms_Code => l_fgt_terms_code,
x_return_status => l_return_status);
l_del_fgt_terms_tbl.delete;
ROLLBACK TO Update_dd_Attributes_PVT;
ROLLBACK TO Update_dd_Attributes_PVT;
ROLLBACK TO Update_dd_Attributes_PVT;
WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_CMG_PVT.Update_dd_Attributes',l_module_name);
END Update_dd_Attributes;
PROCEDURE Update_Quantity(
p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
p_action_prms IN OUT NOCOPY WSH_BULK_TYPES_GRP.action_parameters_rectype,
p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type,
p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
x_return_status OUT NOCOPY VARCHAR2) IS
-- Cursor to get the source requested quantity corresponding to the
-- po_line_location_id.
Cursor c_src_qty(p_line_location_id NUMBER,p_header_id NUMBER,p_line_id NUMBER)
is
select src_requested_quantity,
src_requested_quantity_uom,
src_requested_quantity2,
--HACMS {
requested_quantity_uom,
inventory_item_id
--HACMS }
from WSH_DELIVERY_DETAILS
where po_shipment_line_id = p_line_location_id and
source_code = 'PO' AND
source_header_id = p_header_id AND
source_line_id = p_line_id AND
rownum = 1;
SELECT count(*)
FROM wsh_delivery_details
WHERE released_status = 'X' and
source_code = 'PO' AND
source_header_id = p_header_id AND
source_line_id = p_line_id AND
po_shipment_line_id = p_line_location_id and
routing_req_id is null;
select
delivery_detail_id,
requested_quantity,
--HACMS {
requested_quantity_uom ,
inventory_item_id,
--HACMS }
last_update_date
from WSH_DELIVERY_DETAILS
where released_status = 'X' and
source_code = 'PO' AND
source_header_id = p_header_id AND
source_line_id = p_line_id AND
po_shipment_line_id = p_line_location_id and
routing_req_id IS NULL
and rownum = 1;
select
delivery_detail_id,
requested_quantity qty,
picked_quantity pick_qty,
requested_quantity_uom qty_uom,
inventory_item_id,
last_update_date
from
wsh_delivery_details
where released_status = 'X' and
source_code = 'PO' AND
source_header_id = p_header_id AND
source_line_id = p_line_id AND
po_shipment_line_id = p_line_location_id;
select
delivery_detail_id,
requested_quantity qty,
--HACMS {
inventory_item_id,
requested_quantity_uom qty_uom,
last_update_date
--HACMS }
from
wsh_delivery_details
where released_status = 'X' and
source_code = 'PO' AND
source_header_id = p_header_id AND
source_line_id = p_line_id AND
po_shipment_line_id = p_line_location_id and
routing_req_id is null
order by qty asc;
select
wdd.requested_quantity qty,
wdd.delivery_detail_id,
wnd.delivery_id,
wdd.ship_from_location_id,
wdd.routing_req_id,
wnd.planned_flag,
--wdd.ignore_for_planning,
nvl(wdd.ignore_for_planning, 'N') ignore_for_planning,
--HACMS {
wdd.inventory_item_id,
wdd.picked_quantity pick_qty,
wdd.requested_quantity_uom qty_uom,
wdd.ship_to_location_id,
wnd.ultimate_dropoff_location_id del_location,
--HACMS }
wdd.last_update_date
from
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
where wdd.released_status = 'X' and
WDD.source_code = 'PO' AND
WDD.source_header_id = p_header_id AND
WDD.source_line_id = p_line_id AND
wdd.po_shipment_line_id = p_line_location_id and
wda.delivery_detail_id = wdd.delivery_detail_id and
wnd.delivery_id(+) = wda.delivery_id and
wdd.routing_req_id is not null
order by wdd.earliest_pickup_date desc;
SELECT count(distinct(ship_from_location_id))
FROM wsh_delivery_details
WHERE released_status = 'X' and
source_code = 'PO' AND
source_header_id = p_header_id AND
source_line_id = p_line_id AND
po_shipment_line_id = p_line_location_id and
routing_req_id is not null;
l_insert_new_dd VARCHAR2(1);
l_last_update_date DATE;
l_dff_update_flag VARCHAR2(1);
l_update_flag BOOLEAN := FALSE;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_QUANTITY';
SAVEPOINT Update_Quantity_PVT;
l_insert_new_dd := NULL;
ELSE--insert case
-- No delivery detail is present for the corresponding po shipment line.
-- This is an insert case.
l_insert_new_dd := 'Y';
WSH_DEBUG_SV.log(l_module_name,'l_insert_new_dd', l_insert_new_dd);
WSH_DEBUG_SV.log(l_module_name,' v_lines_without_rr.last_update_date', v_lines_without_rr.last_update_date);
-- the open delivery detail is updated with the new qty.
l_requested_qty := l_requested_quantity - l_qty_to_change; --HACMS
-- the delivery detail is updated to 'D'( Cancelled').
-- The new qyt that needs to be updated is determined and this is
-- updated once again on to the next available open delivery detail.
temp_qty := l_qty_to_change - l_requested_quantity; --HACMS
Update wsh_delivery_details
set
requested_quantity = l_requested_qty,
requested_quantity_uom = decode(l_new_req_qty_uom,NULL,requested_quantity_uom,l_new_req_qty_uom), --HACMS
cancelled_quantity = nvl(cancelled_quantity,0) + nvl(l_cancelled_qty,0), --HACMS
released_status = l_released_status,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
delivery_detail_id = v_lines_without_rr.delivery_detail_id AND
po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
source_header_id = p_line_rec.header_id(i) AND
source_line_id = p_line_rec.line_id(i) ANd
source_code = 'PO' AND
((p_line_rec.source_blanket_reference_id(i) IS NULL AND
source_blanket_reference_id IS NULL) OR
(p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND
source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) AND
released_status = 'X' AND
last_update_date = v_lines_without_rr.last_update_date;
WSH_DEBUG_SV.log(l_module_name,'Line Updated in wsh_delivery_details',sql%rowcount);
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
WSH_DEBUG_SV.log(l_module_name,' v_lines_with_rr.last_update_date', v_lines_with_rr.last_update_date);
-- the open delivery detail is updated with the new qty.
l_requested_qty := l_requested_quantity - l_qty_to_change; --HACMS
-- the delivery detail is updated to 'D'( Cancelled').
-- The new qyt that needs to be updated is determined and this is
-- updated once again on to the next available open delivery detail.
temp_qty := l_qty_to_change - l_requested_quantity;
Update wsh_delivery_details
set
requested_quantity = l_requested_qty,
--HACMS {
requested_quantity_uom = decode(l_new_req_qty_uom,NULL,requested_quantity_uom,l_new_req_qty_uom),
cancelled_quantity = nvl(cancelled_quantity,0)+nvl(l_cancelled_qty,0),
picked_quantity = decode(l_pick_qty,NULL,picked_quantity,l_pick_qty),
--HACMS }
released_status = l_released_status,
ignore_for_planning = l_ignore_for_planning,
ship_from_location_id = l_ship_from_location_id,
routing_req_id = l_routing_request_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
delivery_detail_id = v_lines_with_rr.delivery_detail_id AND
po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
source_header_id = p_line_rec.header_id(i) AND
source_line_id = p_line_rec.line_id(i) AND
source_code = 'PO' AND
((p_line_rec.source_blanket_reference_id(i) IS NULL AND
source_blanket_reference_id IS NULL) OR
(p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND
source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) AND
released_status = 'X' AND
last_update_date = v_lines_with_rr.last_update_date;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
-- If no such line is present, then a new record is inserted into wdd
-- for the additional qty.
--{
OPEN c_inc_qty(p_line_rec.po_shipment_line_id(i),
p_line_rec.header_id(i),p_line_rec.line_id(i));
l_requested_quantity_uom, l_inventory_item_id, l_last_update_date; --HACMS
WSH_DEBUG_SV.log(l_module_name,'l_last_update_date',l_last_update_date);
UPDATE WSH_DELIVERY_DETAILS
SET
requested_quantity = l_requested_qty + l_qty_to_change,
requested_quantity_uom = decode(l_new_req_qty_uom,NULL,requested_quantity_uom,l_new_req_qty_uom), --HACMS
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_delivery_detail_id AND
po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
source_header_id = p_line_rec.header_id(i) AND
source_line_id = p_line_rec.line_id(i) ANd
source_code = 'PO' AND
((p_line_rec.source_blanket_reference_id(i) IS NULL AND
source_blanket_reference_id IS NULL) OR
(p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND
source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) AND
released_status = 'X' AND
last_update_date = l_last_update_date;
l_update_flag := TRUE;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
-- insert a new record for additional qty.
IF p_action_prms.action_code = 'REAPPROVE_PO' THEN
l_caller := 'WSH-PO-INT';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_BULK_PROCESS_PVT.BULK_INSERT_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_BULK_PROCESS_PVT.bulk_insert_details (
P_line_rec => P_line_rec,
p_index => i,
p_action_prms => l_action_prms,
p_additional_line_info_rec => l_additional_line_info_rec,
X_return_status => l_return_status
);
IF l_insert_new_dd = 'Y' THEN
--{
-- No delivery detail is present for the corresponding po shipment line id.
-- Need to insert a new record for the line location id.
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'p_line_rec.requested_quantity(i)', p_line_rec.requested_quantity(i));
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_BULK_PROCESS_PVT.BULK_INSERT_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
-- Call WSH_BULK_PROCESS_PVT.bulk_insert_details() to insert the record into wsh_delivery_details.
WSH_BULK_PROCESS_PVT.bulk_insert_details (
P_line_rec => P_line_rec,
p_index => i,
p_action_prms => l_action_prms,
p_additional_line_info_rec => l_additional_line_info_rec,
X_return_status => l_return_status
);
Update wsh_delivery_details
set requested_quantity = l_qty_to_change,
requested_quantity_uom = p_line_rec.requested_quantity_uom(i),
picked_quantity = decode(l_pick_qty,null,picked_quantity,l_pick_qty) ,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = v_lines.delivery_detail_id AND
po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
source_header_id = p_line_rec.header_id(i) AND
source_line_id = p_line_rec.line_id(i) ANd
source_code = 'PO' AND
((p_line_rec.source_blanket_reference_id(i) IS NULL AND source_blanket_reference_id IS NULL) OR
(p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) AND
released_status = 'X' AND
last_update_date = v_lines.last_update_date;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
OR l_update_flag --- increment from asn/receipt
THEN
FORALL i in 1..p_line_rec.po_shipment_line_id.COUNT
Update wsh_delivery_details
Set src_requested_quantity = nvl(p_line_rec.ordered_quantity(i),src_requested_quantity),
src_requested_quantity_uom = nvl(p_line_rec.order_quantity_uom(i),src_requested_quantity_uom),
--HACMS
-- HW OPMCONV - No need to use OPM precision. Use current INV which is 5
requested_quantity2 = decode(released_status,'X',ROUND(l_ratio_tbl(i) * requested_quantity ,WSH_UTIL_CORE.C_MAX_DECIMAL_DIGITS_INV),requested_quantity2),
cancelled_quantity2 = decode(released_status,'X',ROUND(l_ratio_tbl(i) * cancelled_quantity ,WSH_UTIL_CORE.C_MAX_DECIMAL_DIGITS_INV),cancelled_quantity2),
picked_quantity2 = decode(released_status,'X',ROUND(l_ratio_tbl(i) * picked_quantity ,WSH_UTIL_CORE.C_MAX_DECIMAL_DIGITS_INV),picked_quantity2),
src_requested_quantity2 = nvl(p_line_rec.ordered_quantity2(i),src_requested_quantity2),
src_requested_quantity_uom2 = nvl(p_line_rec.ordered_quantity_uom2(i),src_requested_quantity_uom2),
--HACMS
source_line_number = p_line_rec.source_line_number(i),
po_shipment_line_number = p_line_rec.po_shipment_line_number(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where source_code = 'PO'
AND po_shipment_line_id = p_line_rec.po_shipment_line_id(i)
AND source_header_id = p_line_rec.header_id(i)
AND source_line_id = p_line_rec.line_id(i)
AND source_code = 'PO' AND
((p_line_rec.source_blanket_reference_id(i) IS NULL AND source_blanket_reference_id IS NULL) OR
(p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) ;
p_wt_vol_dd_id.delete;
ROLLBACK TO Update_quantity_PVT;
ROLLBACK TO Update_quantity_PVT;
ROLLBACK TO Update_quantity_PVT;
ROLLBACK TO Update_quantity_PVT;
WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_CMG_PVT.Update_quantity',l_module_name);
END Update_Quantity;
SELECT
wdd.delivery_detail_id,
wdd.requested_quantity,
wdd.requested_quantity2,
wda.delivery_id,
wdd.last_update_date
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE
wdd.source_header_id = NVL(p_header_id,wdd.source_header_id) AND
wdd.delivery_detail_id = wda.delivery_detail_id AND
wdd.source_line_id = p_line_id AND
wdd.po_shipment_line_id = p_line_location_id AND
( (p_release_id IS NULL AND wdd.source_blanket_reference_id IS NULL)
OR
(p_release_id IS NOT NULL AND wdd.source_blanket_reference_id = p_release_id )
) AND
wdd.source_code = 'PO' AND
wdd.released_status = 'X'
for update of wdd.released_status NOWAIT;
last_update_date_tab WSH_UTIL_CORE.date_tab_type;
last_update_date_tab(last_update_date_tab.COUNT+1) := v_dd_ids.last_update_date;
UPDATE
wsh_delivery_details
SET
requested_quantity = 0,
requested_quantity2 = 0,
cancelled_quantity = req_qty_tab(i),
cancelled_quantity2 = req_qty2_tab(i),
released_status = 'D',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
delivery_detail_id = dd_ids_tab(i) AND
released_status = 'X'
RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
UPDATE wsh_delivery_details
SET released_status = 'L',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = dd_ids_tab(i) AND
released_status = 'X'
RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
UPDATE wsh_delivery_details
SET released_status = 'X'
WHERE
released_status = 'L' AND
received_quantity IS NULL AND
source_code = 'PO' AND
source_header_id = NVL(p_line_rec.header_id(i),source_header_id) AND
source_line_id = p_line_rec.line_id(i) AND
po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
((p_line_rec.source_blanket_reference_id(i) IS NULL AND source_blanket_reference_id IS NULL)
OR
(p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i) )
)
RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
SELECT '1'
FROM
RCV_FTE_LINES_V RFLV,
WSH_INBOUND_TXN_HISTORY WTH
WHERE RFLV.PO_HEADER_ID = p_header_id AND
RFLV.SHIPMENT_HEADER_ID = WTH.SHIPMENT_HEADER_ID AND
RFLV.PO_LINE_ID = NVL(p_line_id,RFLV.PO_LINE_ID) AND
RFLV.PO_LINE_LOCATION_ID= NVL(p_line_location_id,RFLV.PO_LINE_LOCATION_ID) AND
(
p_release_id IS NULL AND RFLV.po_release_id IS NULL
OR
RFLV.PO_RELEASE_ID = p_release_id
)
AND WTH.STATUS NOT IN ('MATCHED','CANCELLED')
AND WTH.TRANSACTION_TYPE NOT IN ('ROUTING_REQUEST','ROUTING_RESPONSE');
l_dff_update_flag VARCHAR2(1);
p_update_flag => l_dff_update_flag,
x_return_status => l_return_status);
IF (l_dff_update_flag = 'Y') THEN
--{
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ACTIONS_LEVELS.G_VALIDATION_LEVEL_TAB',WSH_DEBUG_SV.C_PROC_LEVEL);