[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_untrxd_rec_count
from wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_trip_stops wts
where
wdd.source_code = 'OE'
and wdd.released_status = 'C'
and wdd.inv_interfaced_flag in ('N' ,'P')
and wdd.organization_id = l_organization_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wnd.delivery_id = wda.delivery_id
and wnd.status_code in ('CL','IT')
and wdl.delivery_id = wnd.delivery_id
and wts.pending_interface_flag in ('Y', 'P')
and trunc(wts.actual_departure_date) between l_closing_fm_date and l_closing_to_date
and wdl.pick_up_stop_id = wts.stop_id;
select count(*)
into l_rec_exp_count
from wsh_delivery_details wdd, wsh_delivery_assignments wda,
wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_trip_stops wts,
oe_order_lines_all oel, po_requisition_lines_all pl
where
wdd.source_code = 'OE'
and wdd.released_status = 'C'
and wdd.inv_interfaced_flag in ('N' ,'P')
and wda.delivery_detail_id = wdd.delivery_detail_id
and wnd.delivery_id = wda.delivery_id
and wnd.status_code in ('CL','IT')
and wdl.delivery_id = wnd.delivery_id
and wts.pending_interface_flag in ('Y', 'P')
and trunc(wts.actual_departure_date) between l_closing_fm_date and l_closing_to_date
and wdd.source_line_id = oel.line_id
and wdd.source_document_type_id = 10
and oel.source_document_line_id = pl.requisition_line_id
and pl.destination_organization_id = l_organization_id
and pl.destination_organization_id <> pl.source_organization_id
and pl.destination_type_code = 'EXPENSE'
and wdl.pick_up_stop_id = wts.stop_id
and wts.stop_location_id = wnd.initial_pickup_location_id;
select count(*)
into l_rec_direct_count
from wsh_delivery_details wdd, wsh_delivery_assignments wda,
wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_trip_stops wts,
oe_order_lines_all oel, po_requisition_lines_all pl,
mtl_interorg_parameters mip
where
wdd.source_code = 'OE'
and wdd.released_status = 'C'
and wdd.inv_interfaced_flag in ('N' ,'P')
and wda.delivery_detail_id = wdd.delivery_detail_id
and wnd.delivery_id = wda.delivery_id
and wnd.status_code in ('CL','IT')
and wdl.delivery_id = wnd.delivery_id
and wts.pending_interface_flag in ('Y', 'P')
and trunc(wts.actual_departure_date) between l_closing_fm_date and l_closing_to_date
and wdd.source_line_id = oel.line_id
and wdd.source_document_type_id = 10
and oel.source_document_line_id = pl.requisition_line_id
and pl.destination_organization_id = l_organization_id
and pl.destination_organization_id <> pl.source_organization_id
and pl.destination_organization_id = mip.to_organization_id
and pl.source_organization_id = mip.from_organization_id
and mip.intransit_type = 1
and pl.destination_type_code <> 'EXPENSE'
and wdl.pick_up_stop_id = wts.stop_id
and wts.stop_location_id = wnd.initial_pickup_location_id;
select sum( nvl(shipped_quantity, nvl(picked_quantity, requested_quantity)) )
into l_nonintf_line_qty
from wsh_delivery_details wdd
where
wdd.source_code = 'OE'
and wdd.inv_interfaced_flag in ('N' ,'P')
and wdd.source_line_id = l_line_id;
PROCEDURE update_printer_assignment(
x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, p_application_id IN NUMBER
, p_conc_program_id IN NUMBER
, p_level_type_id IN NUMBER
, p_level_value_id IN NUMBER
, p_organization_id IN NUMBER
, p_printer_name IN VARCHAR2
, p_enabled_flag IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(100) := 'update_printer_assignment';
SELECT enabled_flag, default_printer_flag
FROM WSH_REPORT_PRINTERS
WHERE application_id = v_application_id
AND concurrent_program_id = v_label_type_id
AND level_type_id = p_level_type_id
AND level_value_id = p_level_value_id
AND printer_name = p_printer_name;
SELECT max(lookup_code)
FROM mfg_lookups
WHERE lookup_type = 'WMS_LABEL_TYPE';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_PRINTER_ASSIGNMENT';
-- This SELECT checks to see if there is atleast a single label type enabled for this printer.
-- In case the user is signing off this printer for all label types, the "p_conc_program_id"
-- is null and so the "concurrent_program_id = nvl(p_conc_program_id, concurrent_program_id)"
-- results in a true.
-- The advantage of having this check outside of the loop is that the message pops up only for
-- cases where a printer name is specified.
-- A good test case for this would be to enable a printer for multiple label types and test.
-- In cases of signing off all printers for all label types, this part of the code is not executed
-- and so the messages don't pop up.
IF (p_printer_name IS NOT NULL) AND
(nvl(p_enabled_flag,'N') = 'N') THEN
trace('When signing off a specific printer, check whether this printer has been signed on');
SELECT 1 INTO l_printer_signed_on FROM dual
WHERE EXISTS
(SELECT 1
FROM wsh_report_printers
WHERE enabled_flag ='Y'
AND nvl(default_printer_flag,'N') ='Y'
AND concurrent_program_id = nvl(p_conc_program_id, concurrent_program_id)
AND level_type_id = p_level_type_id
AND level_value_id = p_level_value_id
AND printer_name = p_printer_name);
trace('Inserting new record for the combination');
-- Insert the new relation into the table.
INSERT INTO WSH_REPORT_PRINTERS
( application_id
, concurrent_program_id
, level_type_id
, level_value_id
, printer_name
, description
, enabled_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, label_id
, format_type
, equipment_instance
, organization_id
, subinventory
, default_printer_flag)
VALUES(
l_application_id
, l_label_type_id
, p_level_type_id
, p_level_value_id
, p_printer_name
, null
, p_enabled_flag
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, sysdate
, FND_GLOBAL.user_id
, sysdate
, FND_GLOBAL.user_id
, FND_GLOBAL.user_id
, null
, null
, null
, null
, null
, null
, null
, null
, null
, 'Y');
trace('Record inserted');
UPDATE WSH_REPORT_PRINTERS
SET enabled_flag = 'Y',
default_printer_flag = 'Y'
WHERE application_id = l_application_id
AND concurrent_program_id = l_label_type_id
AND level_type_id = p_level_type_id
AND level_value_id = p_level_value_id
AND printer_name = p_printer_name;
trace('Record updated');
trace('Update printers other than the current one to be not default');
-- In this update, set the default_printer_flag to 'N' so that any printer
-- with the enabled_flag earlier is left untouched. The currently enabled printer
-- is a combination of the "enabled_flag" and the "default_printer_flag" set to 'Y'.
UPDATE WSH_REPORT_PRINTERS
SET enabled_flag = 'N',
default_printer_flag = null
WHERE application_id = l_application_id
AND concurrent_program_id = l_label_type_id
AND level_type_id = p_level_type_id
AND level_value_id = p_level_value_id
AND printer_name <> p_printer_name;
trace('Record updated');
UPDATE WSH_REPORT_PRINTERS
SET enabled_flag = 'N',
default_printer_flag = null
WHERE application_id = l_application_id
AND concurrent_program_id = l_label_type_id
AND level_type_id = p_level_type_id
AND level_value_id = p_level_value_id
AND printer_name = nvl(p_printer_name, printer_name);
trace('Record updated');
END update_printer_assignment;
PROCEDURE DBI_Update_Detail_Log
(p_delivery_detail_id_tab IN WSH_UTIL_CORE.id_tab_type,
p_dml_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_dbi_detail_list ISC_DBI_CHANGE_LOG_PKG.log_tab_type;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DBI_UPDATE_DETAIL_LOG';BEGIN
ISC_DBI_CHANGE_LOG_PKG.Update_Del_Detail_Log
(p_detail_list => l_dbi_detail_list,
p_dml_type => p_dml_type,
x_return_status => x_return_status
);
END DBI_Update_Detail_Log;
PROCEDURE DBI_Update_Trip_Stop_Log
(p_stop_id_tab IN WSH_UTIL_CORE.id_tab_type,
p_dml_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_dbi_stop_list ISC_DBI_CHANGE_LOG_PKG.log_tab_type;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'DBI_UPDATE_TRIP_STOP_LOG';
ISC_DBI_CHANGE_LOG_PKG.Update_Trip_Stop_Log
(p_stop_list => l_dbi_stop_list,
p_dml_type => p_dml_type,
x_return_status => x_return_status
);
END DBI_Update_Trip_Stop_Log;
l_attr_tab.delete;
l_group_tab.delete;
SELECT organization_id INTO l_org_id FROM wsh_delivery_details WHERE delivery_detail_id = l_del_det_id;
select sum(wdd.requested_quantity) , requested_quantity_uom, src_requested_quantity_uom, inventory_item_id
into l_cancel_qty_allowed , l_requested_quantity_uom, l_src_requested_quantity_uom, l_inventory_item_id
from wsh_delivery_details wdd
where
wdd.source_line_id = p_source_line_id
and wdd.source_code = p_source_code
and not exists (select 'x' from
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where
wda.delivery_detail_id = wdd.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wnd.status_code in ('CL','CO', 'IT') )
group by requested_quantity_uom, src_requested_quantity_uom, inventory_item_id;