[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SOURCE_CODE,
SOURCE_LINE_ID
FROM WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = p_detail_id;
PROCEDURE Insert_PR_Header_Holds (
p_header_id IN NUMBER,
p_status IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO WSH_PR_HEADER_HOLDS (
batch_id,
header_id,
status )
VALUES (
WSH_PICK_LIST.G_BATCH_ID,
p_header_id,
p_status );
END Insert_PR_Header_Holds;
SELECT nvl(destination_type_code,'@'),source_organization_id,
destination_organization_id
INTO l_Dest_Type,l_From_Org,l_To_Org
FROM po_requisition_lines_all pl,
oe_order_lines_all ol
WHERE pl.line_num = to_number(ol.orig_sys_line_ref)
AND pl.requisition_header_id = ol.source_document_id
AND pl.requisition_line_id = ol.source_document_line_id
AND ol.line_id = p_source_line_id;
select serial_number_control_code
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = p_org_id;
g_bad_header_ids.delete;
g_good_header_ids.delete;
g_passed_crd_Tab.delete;
g_failed_crd_Tab.delete;
select da.parent_delivery_detail_id , da.delivery_id ,
dd.container_name , dd.container_flag
from wsh_delivery_assignments da,
wsh_delivery_Details dd
where da.delivery_detail_id = detail_id
and nvl(da.type,'S') in ('S', 'C')
and da.parent_Delivery_Detail_id = dd.delivery_Detail_id (+);
select delivery_id
from wsh_delivery_assignments_v
where delivery_detail_id = detail_id
and delivery_id IS NOT NULL;
SELECT source_header_id, source_line_id,ship_from_location_id,
source_header_number, source_line_number, org_id, container_flag, source_code -- RTV Changes
FROM wsh_delivery_details
WHERE delivery_detail_id = p_detail_id;
SELECT ship_from_location_id,source_header_number, source_line_number, org_id, container_flag
FROM wsh_delivery_details
WHERE source_header_id = p_source_header_id
AND source_code = p_source_code
AND source_line_id = p_source_line_id;
SELECT status
FROM wsh_pr_header_holds
WHERE header_id = p_source_header_id
AND batch_id = WSH_PICK_LIST.G_BATCH_ID;
g_bad_header_ids.delete;
g_good_header_ids.delete;
g_passed_crd_Tab.delete;
g_failed_crd_Tab.delete;
SELECT ORG_ID
INTO l_org_id
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = l_header_id;
Insert_PR_Header_Holds (p_header_id => l_header_id, p_status => 'F');
Insert_PR_Header_Holds (p_header_id => l_header_id, p_status => 'F');
Insert_PR_Header_Holds (p_header_id => l_header_id, p_status => 'P');
SELECT inventory_item_id,
organization_id,
requested_quantity_uom,
requested_quantity_uom2,
ship_tolerance_above,
ship_tolerance_below, -- 2181132 added following fields
source_header_id,
source_line_set_id,
source_code
FROM wsh_delivery_details
WHERE source_line_id = x_source_line_id
AND source_code = 'OE' -- pick confirm supports only OE lines
AND container_flag = 'N'
AND released_status <> 'D'
AND rownum = 1;
SELECT NVL(SUM(requested_quantity), 0) net_requested_qty,
NVL(SUM(decode (released_status,'C',nvl(shipped_quantity,0),
NVL(picked_quantity, requested_quantity))
), 0) net_staged_qty,
--NVL(SUM(NVL(picked_quantity, requested_quantity)), 0) net_staged_qty,
NVL(SUM(NVL(requested_quantity2,0)), 0) net_requested_qty2,
NVL(SUM(NVL(picked_quantity2, requested_quantity2)), 0) net_staged_qty2
FROM wsh_delivery_details
WHERE source_line_id = x_source_line_id
AND source_code = 'OE'
AND container_flag = 'N'
AND released_status IN ('X', 'Y', 'C');
SELECT WSH_WV_UTILS.CONVERT_UOM(order_quantity_uom,
x_primary_uom,
ordered_quantity,
x_item_id) quantity ,
order_quantity_uom,
ordered_quantity2,
ordered_quantity_uom2
FROM oe_order_lines_all
WHERE line_id = x_source_line_id;
select sum(wdd.requested_quantity), wdd.source_code, wdd.source_line_id,
decode(wdd.requested_quantity_uom2,null,'P',WSH_UTIL_CORE.Get_Line_Fulfillment_Base(wdd.source_code,wdd.source_line_id)) fulfill_base
from wsh_delivery_details wdd, wsh_delivery_assignments_v wda
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id is not null
and wda.delivery_id = del_id
and wdd.released_status <> 'D'
group by source_line_id, source_code, decode(wdd.requested_quantity_uom2,null,'P',WSH_UTIL_CORE.Get_Line_Fulfillment_Base(wdd.source_code, wdd.source_line_id))
having sum(decode( decode(wdd.requested_quantity_uom2,null,'P',WSH_UTIL_CORE.Get_Line_Fulfillment_Base(wdd.source_code,wdd.source_line_id)),'P',wdd.requested_quantity,'S',wdd.requested_quantity2)) = 0;
select distinct wdd.source_line_id, wdd.source_code,
decode(wdd.requested_quantity_uom2,null,'P',WSH_UTIL_CORE.Get_Line_Fulfillment_Base(wdd.source_code,wdd.source_line_id)) fulfill_base
from wsh_delivery_details wdd, wsh_delivery_assignments_v wda
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.released_status <> 'D'
and wda.delivery_id is not null
and wda.delivery_id = p_delivery_id;
select sum(wdd.requested_quantity)
into l_req_qty
from wsh_delivery_details wdd, wsh_delivery_assignments_v wda, wsh_new_deliveries wnd
where wdd.delivery_detail_id = wda.delivery_detail_id
and wnd.delivery_id(+) = wda.delivery_id
and (wda.delivery_id <> p_delivery_id or wda.delivery_id is NULL)
and wdd.released_status not in ('C', 'D')
and NVL(wnd.status_code,'OP') <> 'CO'
and wdd.source_line_id = source_rec.source_line_id
and wdd.source_code = source_rec.source_code
having sum(wdd.requested_quantity) = 0;
select sum(wdd.requested_quantity2)
into l_req_qty
from wsh_delivery_details wdd, wsh_delivery_assignments_v wda, wsh_new_deliveries wnd
where wdd.delivery_detail_id = wda.delivery_detail_id
and wnd.delivery_id(+) = wda.delivery_id
and (wda.delivery_id <> p_delivery_id or wda.delivery_id is NULL)
and wdd.released_status not in ('C', 'D')
and NVL(wnd.status_code,'OP') <> 'CO'
and wdd.source_line_id = source_rec.source_line_id
and wdd.source_code = source_rec.source_code
having sum(wdd.requested_quantity2) = 0;
SELECT status_code
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT requested_quantity_uom2,
nvl(inspection_flag,'N'),
released_status,
picked_quantity,
container_flag,
organization_id,
inventory_item_id,
pickable_flag,
subinventory,
source_code,
inventory_item_id,
nvl(line_direction,'O') line_direction -- J-IB-NPARIKH
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
x_disabled_list.delete;
-- can update some fields if the delivery line
-- is released to warehouse and is not pending overpick.
i:=i+1; x_disabled_list(i) := 'TP_FLEXFIELD';
-- Nothing is updateable
WSH_DEBUG_SV.pop(l_module_name,'e_all_disabled');
SELECT SUM(requested_quantity),
SUM(NVL(requested_quantity2,0))
FROM wsh_delivery_details
WHERE source_line_set_id = x_line_set_id
AND source_code = p_in_attributes.source_code
AND container_flag = 'N'
AND released_status <> 'D'
AND source_header_id = x_header_id;
SELECT nvl(SUM(GREATEST(nvl(shipped_quantity,0),
nvl(picked_quantity,requested_quantity))),0),
nvl(SUM(GREATEST(nvl(shipped_quantity2,0),
nvl(picked_quantity2,requested_quantity2))),0)
FROM wsh_delivery_details
WHERE source_line_set_id = x_line_set_id
AND released_status <> 'D'
AND source_line_id <> p_in_attributes.line_id
AND source_code = p_in_attributes.source_code
AND container_flag = 'N'
AND source_header_id = x_header_id;
SELECT NVL(SUM(shipped_quantity), 0),
NVL(SUM(shipped_quantity2), 0) --Bug#9437761
FROM wsh_delivery_details
WHERE source_line_id = p_in_attributes.line_id
AND released_status = 'C'
AND source_code = p_in_attributes.source_code
AND container_flag = 'N';
SELECT NVL(SUM(shipped_quantity), 0),
NVL(SUM(shipped_quantity2), 0) --Bug#9437761
FROM wsh_delivery_details
WHERE source_code = p_in_attributes.source_code
AND released_status = 'C'
AND (
source_line_id = p_in_attributes.line_id
OR oe_interfaced_flag = 'Y'
)
AND container_flag = 'N'
AND source_header_id = x_header_id
AND source_line_set_id = x_line_set_id;
SELECT NVL(SUM(shipped_quantity), 0),
NVL(SUM(shipped_quantity2), 0) --Bug#9437761
FROM wsh_delivery_details
WHERE source_code = p_in_attributes.source_code
AND released_status = 'C'
AND container_flag = 'N'
AND source_header_id = x_header_id
AND source_line_set_id = x_line_set_id;
SELECT ship_tolerance_below,
ship_tolerance_above,
source_line_set_id,
source_header_id,
src_requested_quantity_uom,
inventory_item_id,
requested_quantity_uom
FROM wsh_delivery_details
WHERE source_line_id = p_in_attributes.line_id
AND container_flag = 'N'
AND released_status <> 'D'
AND source_code = p_in_attributes.source_code
AND rownum = 1;
SELECT SUM(requested_quantity),
SUM(NVL(requested_quantity2,0))
FROM wsh_delivery_details
WHERE source_line_id =p_in_attributes.line_id
AND source_code = p_in_attributes.source_code
AND container_flag = 'N'
AND released_status <> 'D'
AND source_header_id = x_header_id;
SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE source_line_set_id = x_line_set_id
AND source_code = p_in_attributes.source_code
AND source_header_id = x_header_id
FOR UPDATE;
SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE source_line_id = p_in_attributes.line_id
FOR UPDATE;
select wnd.organization_id,
wnd.status_code,
wnd.planned_flag,
wnd.delivery_id,
nvl(wnd.shipment_direction, 'O'), -- J inbound logistics jckwok
nvl(wnd.ignore_for_planning, 'N'), -- OTM R12 : WSHDEVLS record
nvl(wnd.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) -- OTM R12 : WSHDEVLS record
from wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda
where wnd.delivery_id = wda.delivery_id
and wda.delivery_detail_id = p_del_det_id;
SELECT 1 FROM DUAL
WHERE EXISTS
( SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id IN
(SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
START WITH parent_delivery_detail_id = p_container_id
CONNECT BY prior delivery_detail_id = parent_delivery_detail_id)
AND wdd.container_flag = 'N'
AND wdd.released_status = 'Y' );
SELECT 'Y'
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id=p_deliveryid
AND wnd.planned_flag='F';
SELECT 'Y'
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id=p_deliveryid
AND (nvl(wnd.ignore_for_planning, 'N') <>
(select nvl(ignore_for_planning,'N') from wsh_delivery_details where delivery_detail_id=p_detid)
);
select shipment_batch_id
from wsh_delivery_details
where delivery_detail_id = c_del_det_id;
SELECT uom.uom_code
FROM mtl_uom_conversions conv, mtl_units_of_measure_vl uom
WHERE uom.unit_of_measure = conv.unit_of_measure
AND uom.uom_code = p_uom_code
AND conv.inventory_item_id = 0
AND NVL(uom.disable_date, sysdate+1) > sysdate
AND NVL(conv.disable_date, sysdate+1) > sysdate ;
IF p_in_rec.action_code = 'UPDATE' THEN
IF p_delivery_detail_rec.weight_uom_code <> FND_API.G_MISS_CHAR
AND p_delivery_detail_rec.weight_uom_code IS NOT NULL
AND x_delivery_detail_rec.weight_uom_code IS NULL THEN
OPEN c_isvalidUOM_Code(p_delivery_detail_rec.weight_uom_code);
SELECT msi.container_type_code,msi.maximum_load_weight,msi.primary_uom_code
FROM mtl_system_items msi,wsh_delivery_details wdd
WHERE msi.inventory_item_id (+) = p_inventory_item_id
AND msi.organization_id (+) = wdd.organization_id
AND wdd.container_flag = 'Y'
AND wdd.delivery_detail_id = p_delivery_detail_id;
SELECT status_code
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT delivery_detail_id
,source_code
,source_header_id
,source_line_id
,customer_id
,sold_to_contact_id
,inventory_item_id
,item_description
,hazard_class_id
,country_of_origin
,classification
,ship_from_location_id
,ship_to_location_id
,ship_to_contact_id
,ship_to_site_use_id
,deliver_to_location_id
,deliver_to_contact_id
,deliver_to_site_use_id
,intmed_ship_to_location_id
,intmed_ship_to_contact_id
,hold_code
,ship_tolerance_above
,ship_tolerance_below
,requested_quantity
,shipped_quantity
,delivered_quantity
,requested_quantity_uom
,subinventory
,revision
,lot_number
,customer_requested_lot_flag
,serial_number
,locator_id
,date_requested
,date_scheduled
,master_container_item_id
,detail_container_item_id
,load_seq_number
,ship_method_code
,carrier_id
,freight_terms_code
,shipment_priority_code
,fob_code
,customer_item_id
,dep_plan_required_flag
,customer_prod_seq
,customer_dock_code
,cust_model_serial_number
,customer_job
,customer_production_line
,net_weight
,weight_uom_code
,volume
,volume_uom_code
,tp_attribute_category
,tp_attribute1
,tp_attribute2
,tp_attribute3
,tp_attribute4
,tp_attribute5
,tp_attribute6
,tp_attribute7
,tp_attribute8
,tp_attribute9
,tp_attribute10
,tp_attribute11
,tp_attribute12
,tp_attribute13
,tp_attribute14
,tp_attribute15
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,sysdate
,FND_GLOBAL.LOGIN_ID
,FND_GLOBAL.USER_ID
,program_application_id
,program_id
,program_update_date
,request_id
,mvt_stat_status
,p_delivery_detail_rec.released_flag
,organization_id
,transaction_temp_id
,ship_set_id
,arrival_set_id
,ship_model_complete_flag
,top_model_line_id
,source_header_number
,source_header_type_id
,source_header_type_name
,cust_po_number
,ato_line_id
,src_requested_quantity
,src_requested_quantity_uom
,move_order_line_id
,cancelled_quantity
,quality_control_quantity
,cycle_count_quantity
,tracking_number
,movement_id
,shipping_instructions
,packing_instructions
,project_id
,task_id
,org_id
,oe_interfaced_flag
,split_from_delivery_detail_id
,inv_interfaced_flag
,source_line_number
,inspection_flag
,released_status
,container_flag
,container_type_code
,container_name
,fill_percent
,gross_weight
,master_serial_number
,maximum_load_weight
,maximum_volume
,minimum_fill_percent
,seal_code
,unit_number
,unit_price
,currency_code
,freight_class_cat_id
,commodity_code_cat_id
,preferred_grade
,src_requested_quantity2
,src_requested_quantity_uom2
,requested_quantity2
,shipped_quantity2
,delivered_quantity2
,cancelled_quantity2
,quality_control_quantity2
,cycle_count_quantity2
,requested_quantity_uom2
-- HW OPMCONV - No need for sublot_number
-- ,sublot_number
,lpn_id
,pickable_flag
,original_subinventory
,to_serial_number
,picked_quantity
,picked_quantity2
,received_quantity
,received_quantity2
,source_line_set_id
,batch_id
,p_delivery_detail_rec.ROWID
,transaction_id
/*J Inbound Logistics new columns. jckwok*/
,vendor_id
,ship_from_site_id
,nvl(line_direction, 'O')
,party_id
,routing_req_id
,shipping_control
,source_blanket_reference_id
,source_blanket_reference_num
,po_shipment_line_id
,po_shipment_line_number
,returned_quantity
,returned_quantity2
,rcv_shipment_line_id
,source_line_type_code
,supplier_item_number
/* J TP Release : New columns ttrichy*/
,nvl(IGNORE_FOR_PLANNING, 'N') ignore_for_planning
,EARLIEST_PICKUP_DATE
,LATEST_PICKUP_DATE
,EARLIEST_DROPOFF_DATE
,LATEST_DROPOFF_DATE
,REQUEST_DATE_TYPE_CODE
,tp_delivery_detail_id
,source_document_type_id
-- J: W/V Changes
,unit_weight
,unit_volume
,filled_volume
,wv_frozen_flag
,mode_of_transport
,service_level
/*J IB: asutar*/
,po_revision_number
,release_revision_number
,replenishment_status --bug# 6689448 (replenishment project)
-- Standalone Project Start
,original_lot_number
,reference_number
,reference_line_number
,reference_line_quantity
,reference_line_quantity_uom
,original_revision
,original_locator_id
-- Standalone Project End
-- TPW - Distributed Organization Changes - Start
,shipment_batch_id
,shipment_line_number
,reference_line_id
-- TPW - Distributed Organization Changes - End
,client_id -- LSP PROJECT : Added just for compatibility (not used anywhere)
,consignee_flag --RTV changes
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_rec.delivery_detail_id;
ELSIF p_in_rec.action_code = 'UPDATE' THEN
--
l_caller := p_in_rec.caller;
l_disabled_list.delete;
wsh_debug_sv.log(l_module_name, 'WMS Caller to update Released status and MOL', p_in_rec.caller);
END IF; /* if action = 'UPDATE' */
-- Nothing is updateable
WSH_DEBUG_SV.pop(l_module_name,'e_all_disabled');
'PACK', 'UNASSIGN','UNPACK', 'DELETE',
'PICK-SHIP', 'PICK-PACK-SHIP',
'CYCLE-COUNT', 'SPLIT-LINE',
'INCLUDE_PLAN', 'IGNORE_PLAN')
THEN
i := i+1;
'PICK-RELEASE-UI', 'UNASSIGN','UNPACK','WT-VOL', 'DELETE',
'RESOLVE-EXCEPTIONS-UI') THEN
-- Fixed as part of bug fix 2864546
-- Resolve Exceptions should be allowed for cancelled lines
--Bug 7025876
--UNASSIGN action should be allowed for cancelled delivery details assigned to planned deliveries
IF p_action NOT IN ('RESOLVE-EXCEPTIONS-UI','UNASSIGN') THEN
i := i+1;
IF p_action IN ('AUTOCREATE-TRIP','PICK-RELEASE','PICK-RELEASE-UI','DELETE','RESOLVE-EXCEPTIONS-UI') THEN
i := i+1;
SELECT SOURCE_CODE,
SOURCE_LINE_ID
FROM WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = p_detail_id;
l_id_tab.DELETE;
SELECT organization_id, inventory_item_id,
lot_number, nvl(line_direction,'O') line_direction,
requested_quantity_uom,
requested_quantity_uom2
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT lot_number
FROM MTL_LOT_NUMBERS
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number ;
FND_MESSAGE.SET_NAME('wsh','WSH_UPDATE_CANNOT_SPLIT');
AND (NVL(p_in_rec.action_code,'CREATE') = 'UPDATE') THEN
NULL;
AND (NVL(p_in_rec.action_code,'CREATE') = 'UPDATE') THEN
NULL;
IF p_user_in_rec.LAST_UPDATE_DATE <> FND_API.G_MISS_DATE
AND NVL(p_user_in_rec.LAST_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.LAST_UPDATE_DATE,TO_DATE('2','j'))
THEN
l_attributes := l_attributes || 'LAST_UPDATE_DATE, ';
IF p_user_in_rec.LAST_UPDATE_LOGIN <> FND_API.G_MISS_NUM
AND NVL(p_user_in_rec.LAST_UPDATE_LOGIN,-99) <> NVL(p_out_rec.LAST_UPDATE_LOGIN,-99)
THEN
l_attributes := l_attributes || 'LAST_UPDATE_LOGIN, ';
IF p_user_in_rec.LAST_UPDATED_BY <> FND_API.G_MISS_NUM
AND NVL(p_user_in_rec.LAST_UPDATED_BY,-99) <> NVL(p_out_rec.LAST_UPDATED_BY,-99)
THEN
l_attributes := l_attributes || 'LAST_UPDATED_BY, ';
IF p_user_in_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
AND NVL(p_user_in_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j'))
THEN
l_attributes := l_attributes || 'PROGRAM_UPDATE_DATE, ';
Select 1
From mtl_reservations
Where demand_source_type_id = 2
and demand_source_line_id = l_source_line_id
and rownum = 1
;
SELECT source_line_id
from wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;