[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR cur_dev IS SELECT * FROM wms_device_requests where device_id = p_device_id;
INSERT INTO wms_device_requests_hist
(request_id,
business_event_id,
organization_id,
lpn_id,
device_id,
subinventory_code,
locator_id,
status_code,
status_msg,
task_summary,
requested_by,
responsibility_application_id,
responsibility_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
REQUEST_DATE
)VALUES(
l_rec.request_id,
l_rec.business_event_id,
l_rec.organization_id,
l_rec.lpn_id,
l_rec.device_id,
l_rec.subinventory_code,
l_rec.locator_id,
Nvl(l_rec.status_code,'S'),
l_rec.status_msg,
'Y',
fnd_global.user_id,
FND_GLOBAL.RESP_APPL_ID,
FND_GLOBAL.RESP_ID,
l_rec.last_update_date,
fnd_global.user_id,
l_rec.last_update_date,
fnd_global.user_id,
l_rec.last_update_date);
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
INSERT INTO wms_device_requests (request_id,
business_event_id,
organization_id,
lpn_id,
device_id,
subinventory_code,
locator_id,
status_code,
status_msg,
last_update_date,
last_updated_by
) VALUES
(l_request_id,
p_business_event_id,
p_organization_id,
p_lpn_id,
p_device_id,
p_subinventory_code,
p_locator_id,
p_status_code,
l_msg_data,
p_event_date,
fnd_global.user_id);
trace('generate_xml_csv_api:Inserted record into WDR');
UPDATE wms_device_requests
SET status_code = l_xml_stat,
status_msg = l_msg_data
WHERE device_id = p_device_id;
UPDATE wms_device_requests
SET status_code = l_return_status,
status_msg = l_msg_data
WHERE device_id = p_device_id;
select distinct wda.delivery_id INTO l_delivery_id
from wsh_delivery_details wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
wsh_delivery_details wdd2
where lpn.outermost_lpn_id = p_lpn_id
and wdd2.lpn_id = lpn.lpn_id
and wdd2.lpn_id is not null
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id;
select wts.trip_id
into l_trip_id
from wsh_delivery_legs wdl, wsh_trip_stops wts
where wdl.delivery_id = l_delivery_id
and wdl.pick_up_stop_id = wts.stop_id;
SELECT -1 INTO x_is_last_lpn FROM dual WHERE exists
( select wlpn.license_plate_number --distinct wlpn.license_plate_number
from wsh_delivery_details wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
wsh_delivery_details wdd2,wms_license_plate_numbers wlpn
where wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd2.lpn_id is not null
and wdd2.lpn_id = lpn.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
AND wlpn.lpn_id <> P_LPN_ID
and wlpn.lpn_context <> 9
and lpn.organization_id = wdd2.organization_id
and nvl(wdd.released_status,'N') = 'Y'
and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
and wda.delivery_id = l_delivery_id
);
SELECT -1 INTO x_is_last_lpn FROM dual WHERE exists
(
select wlpn.license_plate_number --distinct wlpn.license_plate_number
from
wms_license_plate_numbers lpn,
wms_license_plate_numbers wlpn,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd2,
wsh_trip_stops pickup_stop,
wsh_trip_stops dropoff_stop
where pickup_stop.trip_id = l_trip_id
and wdl.pick_up_stop_id = pickup_stop.stop_id
and wdl.drop_off_stop_id = dropoff_stop.stop_id
and pickup_stop.trip_id = dropoff_stop.trip_id
and wdl.delivery_id = wnd.delivery_id
and wnd.status_code in ('OP', 'PA')
and wnd.delivery_id = wda.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
and wdd2.lpn_id is not null
and wdd2.lpn_id = lpn.lpn_id
and lpn.outermost_lpn_id = wlpn.lpn_id
AND wlpn.lpn_id <> P_LPN_ID
and wlpn.lpn_context <> 9
and wdd.released_status = 'Y'
and (wdd.inv_interfaced_flag <> 'Y' )
);
SELECT business_event_id,verification_required INTO x_out_business_event_id,x_verif_req FROM wms_bus_event_devices
WHERE device_id = p_device_id
AND ENABLED_FLAG = 'Y'
AND organization_id = p_organization_id
AND (business_event_id = wms_device_integration_pvt.wms_be_truck_load
OR business_event_id = wms_device_integration_pvt.wms_be_truck_load_ship);
SELECT business_event_id,verification_required INTO x_out_business_event_id,X_VERIF_REQ FROM wms_bus_event_devices
WHERE device_id = p_device_id
AND ENABLED_FLAG = 'Y'
AND organization_id = p_organization_id
AND business_event_id IN
(wms_device_integration_pvt.wms_be_std_insp_receipt,wms_device_integration_pvt.wms_be_direct_receipt);
select distinct delivery_id
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and trip_id = p_trip_id;
select distinct delivery_id
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and dock_appoint_flag = 'N';
select 1
into temp_val
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and trip_id = p_trip_id
and rownum = 1
for update NOWAIT;
select 1
into temp_val
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and dock_appoint_flag = 'N'
and rownum = 1
for update NOWAIT;
select count(distinct delivery_id)
into x_deli_count
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and trip_id = p_trip_id;
select count(distinct delivery_id)
into x_deli_count
from WMS_SHIPPING_TRANSACTION_TEMP
where organization_id = p_organization_id
and dock_door_id = p_dock_door_id
and dock_appoint_flag = 'N';
SELECT 1 INTO l_serial_at_issue FROM dual WHERE exists
(SELECT wlpn.lpn_id
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn, mtl_system_items msi
WHERE wlpn.lpn_id = wlc.parent_lpn_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = wlc.organization_id
and wlc.organization_id = p_org_id
AND MSI.SERIAL_NUMBER_CONTROL_CODE = 6
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
);
SELECT 1 INTO l_serial_at_issue FROM dual WHERE exists
(SELECT wlpn.lpn_id
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn, mtl_system_items msi
WHERE wlpn.lpn_id = wlc.parent_lpn_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = wlc.organization_id
and wlc.organization_id = p_org_id
AND MSI.SERIAL_NUMBER_CONTROL_CODE = 6 --Serials at SO issue
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
);
fnd_msg_pub.delete_msg;
--do not need to update the msg here, it is already in the stack
--l_msg_data is returned as nulll
END IF;
trace('process_rfid_receiving_txn: Standard routing/inspection,insert into interface TABLE');
trace('process_rfid_receiving_txn: Direct routing, insert into interface table');
trace('process_rfid_receiving_txn: Calling rcv_insert_update_header');
SELECT shipment_num,vendor_id
INTO l_shipment_num,l_vendor_id
FROM rcv_shipment_headers
WHERE shipment_header_id = l_shipment_header_id;
INV_RCV_STD_RCPT_APIS.rcv_insert_update_header
(p_organization_id => p_dest_org_id, --destination Org ,
p_shipment_header_id => l_shipment_hdr_id_dummy, --IN OUT parameter
p_source_type => l_source_type,
p_receipt_num => l_receipt_num,
p_vendor_id => l_vendor_id,
p_vendor_site_id => null,
p_shipment_num => l_shipment_num,
p_ship_to_location_id => null,
p_bill_of_lading => null,
p_packing_slip => null,
p_shipped_date => null,
p_freight_carrier_code => null,
p_expected_receipt_date => null,
p_num_of_containers => null,
p_waybill_airbill_num => null,
p_comments => null,
p_ussgl_transaction_code => null,
p_government_context => null,
p_request_id => null,
p_program_application_id => null,
p_program_id => null,
p_program_update_date => null,
p_customer_id => null,
p_customer_site_id => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
trace('process_rfid_receiving_txn: Error: rcv_insert_update_header failed');
end if;--INSERT UPDATE HEADR
SELECT wlpn.lpn_id,wlpn.lpn_context,outermost_lpn_id INTO
x_pallet_lpn_id,x_pallet_lpn_context,l_outermost_lpn_id
FROM wms_license_plate_numbers wlpn,
wms_epc we
WHERE we.lpn_id = wlpn.lpn_id
AND we.cross_ref_type =1 --LPN-EPC type
AND we.epc = x_tag_info(1).tag_id
AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND wlpn.parent_lpn_id IS NOT null) OR
( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
SELECT wlpn.lpn_id,wlpn.lpn_context,wlpn.outermost_lpn_id INTO
x_pallet_lpn_id,x_pallet_lpn_context,l_outermost_lpn_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.license_plate_number = x_tag_info(1).tag_id
AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND wlpn.parent_lpn_id IS NOT null) OR
( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
SELECT cross_ref_type , lpn_id , inventory_item_id, serial_number, gtin, gtin_serial
INTO l_cross_ref_type , l_lpn_id , l_item_id, l_serial_number, l_gtin, l_gtin_serial
FROM wms_epc we
WHERE we.epc = x_tag_info(i).tag_id;
SELECT wlpn.parent_lpn_id, wlpn.LPN_CONTEXT INTO
l_parent_lpn_id,l_PALLET_LPN_CONTEXT
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = L_LPN_ID
AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND
wlpn.parent_lpn_id IS NOT null)
OR
( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
select MSN.LPN_ID,WLPN.outermost_LPN_ID
INTO l_lpn_id, l_serial_pallet_id
from mtl_serial_numbers MSN,
WMS_LICENSE_PLATE_NUMBERS wlpn
WHERE MSN.inventory_item_id = l_item_id
AND MSN.serial_number = l_SERIAL_NUMBER
AND MSN.lpn_id = WLPN.LPN_ID;
UPDATE wms_epc
SET status = substr(fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F'),1,240),
status_code = 'E'
WHERE EPC = x_tag_info(j).TAG_id;
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
SELECT SUM(WLC.primary_quantity),wlc.inventory_item_id
INTO l_total_lpn_qty, l_lpn_item_id
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_pallet_lpn_id
AND wlpn.lpn_id = wlc.parent_lpn_id
--AND wlc.organization_id = p_org_id
AND wlc.organization_id = wlpn.organization_id
GROUP BY WLC.inventory_item_id;
select uom_code INTO l_uom_code FROM mtl_cross_references mcr
WHERE mcr.inventory_item_id = L_LPN_ITEM_ID
AND mcr.CROSS_REFERENCE = To_char(p_tag_info(i).gtin)
AND mcr.CROSS_REFERENCE_TYPE = G_PROFILE_GTIN
AND (( mcr.org_independent_flag = 'Y' AND mcr.organization_id IS NULL) OR
(mcr.org_independent_flag = 'N' AND mcr.organization_id = p_org_id ));
SELECT conversion_rate INTO l_total_temp_qty FROM mtl_uom_conversions_view mucv
where organization_id = p_org_id
and uom_code = l_uom_code
AND INVENTORY_ITEM_ID = l_lpn_item_id;
SELECT Nvl(rfid_verif_pcnt_threshold,0) INTO l_load_verify_threshold
FROM mtl_parameters WHERE organization_id = p_org_id;
SELECT Nvl(rfid_verif_pcnt_threshold,0) INTO l_load_verify_threshold
FROM mtl_parameters WHERE organization_id = p_org_id;
SELECT COUNT(1) INTO l_expected_case_cnt
FROM wms_license_plate_numbers wlpn
WHERE parent_lpn_id = p_pallet_lpn_id
AND wlpn.parent_lpn_id = wlpn.outermost_lpn_id
AND wlpn.parent_lpn_id IS NOT NULL;
SELECT COUNT(1) INTO l_expected_ser_cnt
FROM mtl_serial_numbers msn, wms_license_plate_numbers wlpn
WHERE msn.lpn_id = wlpn.lpn_id
and wlpn.outermost_lpn_id = p_pallet_lpn_id;
--verification SUCCEEDED....update associated case records amd
--CURRENT pallet with STATUS='VALID'
--update all the CASE + PALLET set recods as VALID, including
--the unread ones
IF (l_debug = 1) THEN
trace(' sucessful validation');
/* we can not use the group_id to update wms_epc table in one shot
here as all records of the group_id might not be read. I have to
update only those records which have been read by the reader.
*/
IF l_error_code = 1 THEN --Extraneous read
FOR j IN 1..p_tag_info.COUNT LOOP
UPDATE wms_epc
SET status = l_msg_data,
status_code = x_return_status
WHERE EPC = p_tag_info(j).tag_id;--Since no LPN
INSERT INTO wms_device_requests (request_id,
business_event_id,
organization_id,
lpn_id,
device_id,
subinventory_code,
locator_id,
status_code,
status_msg,
last_update_date,
last_updated_by
) VALUES
(l_request_id,
p_bus_event_id ,
p_org_id,
p_tag_info(j).LPN_id,
p_device_id,
p_subinventory_code,
p_locator_id,
x_return_status,
l_msg_data,
p_event_date,
fnd_global.user_id);
UPDATE wms_epc
SET status = l_msg_data,
status_code = x_return_status
WHERE lpn_id = p_tag_info(j).lpn_id;
INSERT INTO wms_device_requests (request_id,
business_event_id,
organization_id,
lpn_id,
device_id,
subinventory_code,
locator_id,
status_code,
status_msg,
last_update_date,
last_updated_by
) VALUES
(l_request_id,
p_bus_event_id ,
p_org_id,
p_tag_info(j).LPN_id,
p_device_id,
p_subinventory_code,
p_locator_id,
x_return_status,
l_msg_data,
p_event_date,
fnd_global.user_id);
UPDATE wms_epc
SET status = l_msg_data,
status_code = x_return_status
WHERE EPC = p_tag_info(j).tag_id;
select APPLICATION_ID INTO l_resp_appl_id from fnd_application where
APPLICATION_SHORT_NAME = 'WMS' AND ROWNUM <2;
SELECT
device_id,enabled_flag,organization_id,subinventory_code,locator_id,output_method_id
INTO l_device_id,l_device_enabled,l_organization_id,l_subinventory_code,l_locator_id,l_output_method_id
FROM wms_devices_vl
WHERE name = p_portalid; --Device Name
--Insert record into the WMS_DEVICE_REQUESTS table
--Generate xml/call API for truck_load business event
--populate in the history table
ROLLBACK TO wms_rfid_sp1;
--Insert record into the WMS_DEVICE_REQUESTS table
--Generate xml/call API for truck_load business event
--populate in the history table
ROLLBACK TO wms_rfid_sp1;
--Insert record into the WMS_DEVICE_REQUESTS table
--Generate xml/call API for truck_load business event
--populate in the history table
ROLLBACK TO wms_rfid_sp1;
--Insert record into the WMS_DEVICE_REQUESTS table
--Generate xml/call API for truck_load business event
--populate in the history table
ROLLBACK TO wms_rfid_sp1;
select rsh.shipment_header_id into l_shipment_header_id from
rcv_shipment_headers rsh ,wms_license_plate_numbers wlpn where wlpn.lpn_id = l_lpn_id
and wlpn.lpn_context IN (6,7) --for ASN 7, blocked for ASN in patch set J
and (rsh.shipment_num = Nvl(wlpn.source_name,'@#$@')
or rsh.shipment_header_id = Nvl(wlpn.source_header_id, -1));
trace('process_rfid_txn:Delete requested rows from WDR');
delete from wms_device_requests;--since temp table is session specific
trace('process_rfid_txn:throwing Exception:end_processing, Delete requested rows from WDR');
delete from wms_device_requests;--since temp table is session specific