[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_get_hash_for_insert NUMBER := 1;
IF p_get_hash_mode = g_get_hash_for_insert THEN
g_field_elements_table(l_return_hash_value).column_name_with_count := p_input_string;
SELECT wlf.column_name,
wlf.sql_stmt,
wlfv.field_variable_name
FROM wms_label_field_variables wlfv,
wms_label_fields_vl wlf
WHERE wlfv.label_format_id = p_label_format_id
AND wlfv.label_field_id = wlf.label_field_id
ORDER BY wlf.column_name, wlfv.field_variable_name;
g_field_elements_table.DELETE(nvl(g_field_elements_table.first,0),nvl(g_field_elements_table.last,0));
g_field_elements_table(get_field_hash_value(l_label_field_var.column_name||l_column_count, g_get_hash_for_insert)).variable_name := l_label_field_var.field_variable_name;
g_field_elements_table(get_field_hash_value(l_label_field_var.column_name||l_column_count, g_get_hash_for_insert)).sql_stmt := l_label_field_var.sql_stmt;
SELECT rti.lpn_id, rti.to_organization_id, pha.segment1 purchase_order,
rti.subinventory, rti.locator_id,
l_receipt_number receipt_number, pol.line_num po_line_number,
pll.quantity quantity_ordered, rti.vendor_item_num supplier_part_number,
pov.vendor_id vendor_id, pov.vendor_name supplier_name,
pvs.vendor_site_id vendor_site_id, pvs.vendor_site_code supplier_site,
ppf.full_name requestor, hrl1.location_code deliver_to_location,
hrl2.location_code location, pll.note_to_receiver note_to_receiver
FROM rcv_transactions_interface rti, po_headers_trx_v pha,--CLM Changes,using CLM views instead of base tables
-- MOAC : changed po_line_locations to po_line_locations_all
po_lines_trx_v pol, rcv_shipment_headers rsh, po_line_locations_trx_v pll,
po_vendors pov, hr_locations_all hrl1, hr_locations_all hrl2,
-- MOAC : changed po_vendor_sites to po_vendor_sites_all
po_vendor_sites_all pvs, per_people_f ppf
where rti.interface_transaction_id = p_transaction_id
AND rti.po_header_id = pha.po_header_id(+)
AND rsh.shipment_header_id(+) = rti.shipment_header_id
AND pol.po_line_id(+) = rti.po_line_id --Added outer join, bug 4918726
AND pol.po_header_id(+) = rti.po_header_id --Added outer join, bug 4918726
--AND pll.po_line_id(+) = pol.po_line_id -- bug 2372669
AND pll.line_location_id(+) = rti.po_line_location_id -- bug 2372669
AND pov.vendor_id(+) = rti.vendor_id
-- AND pvs.vendor_id(+) = rti.vendor_id -- Unesseccary line dherring 8/2/05
AND pvs.vendor_site_id(+) = rti.vendor_site_id
AND ppf.person_id(+) = rti.deliver_to_person_id
AND hrl1.location_id(+) = rti.deliver_to_location_id
AND hrl2.location_id(+) = rti.location_id;
SELECT rti.transfer_lpn_id transfer_lpn_id, rti.to_organization_id to_oragnization_id,
pha.segment1 purchase_order , rti.subinventory, rti.locator_id,
l_receipt_number receipt_number, pol.line_num po_line_number, pll.quantity
quantity_ordered, rti.vendor_item_num supplier_part_number,
pov.vendor_id vendor_id, pov.vendor_name supplier_name,
pvs.vendor_site_id vendor_site_id,
pvs.vendor_site_code supplier_site, ppf.full_name requestor,
hrl1.location_code deliver_to_location, hrl2.location_code location,
pll.note_to_receiver note_to_receiver
FROM rcv_transactions_interface rti, po_headers_trx_v pha,--CLM Changes,using CLM views instead of base tables
-- MOAC : changed po_line_locations to po_line_locations_all
po_lines_trx_v pol, rcv_shipment_headers rsh, po_line_locations_trx_v pll,
po_vendors pov, hr_locations_all hrl1, hr_locations_all hrl2,
-- MOAC : changed po_vendor_sites to po_vendor_sites_all
po_vendor_sites_all pvs, per_people_f ppf
where rti.interface_transaction_id = p_transaction_id
AND rti.po_header_id = pha.po_header_id(+)
AND rsh.shipment_header_id(+) = rti.shipment_header_id
AND pol.po_line_id (+) = rti.po_line_id
AND pol.po_header_id (+) = rti.po_header_id
--AND pll.po_line_id(+) = pol.po_line_id -- bug 2372669
AND pll.line_location_id(+) = rti.po_line_location_id -- bug 2372669
AND pov.vendor_id(+) = rti.vendor_id
-- AND pvs.vendor_id(+) = rti.vendor_id -- Unesseccary line dherring 8/2/05
AND pvs.vendor_site_id(+) = rti.vendor_site_id
AND ppf.person_id(+) = rti.deliver_to_person_id
AND hrl1.location_id(+) = rti.deliver_to_location_id
AND hrl2.location_id(+) = rti.location_id;
SELECT distinct all_lpn.lpn_id
, pha.segment1 purchase_order
, all_lpn.subinventory
, all_lpn.locator_id
, rsh.receipt_num
, pol.line_num po_line_number
, pll.quantity quantity_ordered
, rsl.vendor_item_num supplier_part_number
, pov.vendor_id vendor_id
, pvs.vendor_site_id vendor_site_id
, pov.vendor_name supplier_name
, pvs.vendor_site_code supplier_site
, ppf.full_name requestor
-- , hrl1.location_code deliver_to_location
-- , hrl2.location_code location
, pll.note_to_receiver note_to_receiver
, all_lpn.deliver_to_location_id
, all_lpn.location_id
-- Added for bug 3581021 by joabraha
, pol.item_id item_id
--
FROM(
-- LPN_ID
select lpn_id
, po_header_id, po_line_id
, subinventory, locator_id
, shipment_header_id, po_line_location_id
, vendor_id, vendor_site_id
, deliver_to_person_id, deliver_to_location_id
, location_id
from rcv_transactions rt
where rt.lpn_id is not null
and rt.group_id = p_transaction_id
AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
AND p_label_type_info.business_flow_code = 2)
OR (rt.transaction_type = 'DELIVER'
AND p_label_type_info.business_flow_code in (3,4))
OR (rt.transaction_type = 'RECEIVE'
--AND rt.routing_header_id <> 3 Modified for Bug: 4312020
AND p_label_type_info.business_flow_code = 1
)
)
UNION ALL
-- PARENT LPN of LPN_ID
select lpn.parent_lpn_id
, rt.po_header_id, rt.po_line_id
, rt.subinventory, rt.locator_id
, rt.shipment_header_id, rt.po_line_location_id
, rt.vendor_id, rt.vendor_site_id
, rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
, rt.location_id location_id
from wms_license_plate_numbers lpn,
rcv_transactions rt
where lpn.lpn_id = rt.lpn_id
and lpn.parent_lpn_id <> rt.lpn_id
and lpn.parent_lpn_id is not null
and rt.group_id = p_transaction_id
AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
AND p_label_type_info.business_flow_code = 2)
OR (rt.transaction_type = 'DELIVER'
AND p_label_type_info.business_flow_code in (3,4))
OR (rt.transaction_type = 'RECEIVE'
--AND rt.routing_header_id <> 3 Modified for Bug: 4312020
AND p_label_type_info.business_flow_code = 1
)
)
UNION ALL
-- OUTERMOSE LPN of LPN_ID, and different than the LPN and parent LPN
select lpn.outermost_lpn_id
, rt.po_header_id, rt.po_line_id
, rt.subinventory, rt.locator_id
, rt.shipment_header_id, rt.po_line_location_id
, rt.vendor_id, rt.vendor_site_id
, rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
, rt.location_id location_id
from wms_license_plate_numbers lpn, rcv_transactions rt
where lpn.lpn_id = rt.lpn_id
and lpn.outermost_lpn_id <> lpn.lpn_id
and lpn.outermost_lpn_id <> lpn.parent_lpn_id
and rt.group_id = p_transaction_id
AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
AND p_label_type_info.business_flow_code = 2)
OR (rt.transaction_type = 'DELIVER'
AND p_label_type_info.business_flow_code in (3,4))
OR (rt.transaction_type = 'RECEIVE'
--AND rt.routing_header_id <> 3 Modified for Bug: 4312020
AND p_label_type_info.business_flow_code = 1
)
)
UNION all
-- Transfer LPN (different than LPN)
select transfer_lpn_id lpn_id
, po_header_id, po_line_id
, subinventory, locator_id
, shipment_header_id, po_line_location_id
, vendor_id, vendor_site_id
, deliver_to_person_id, deliver_to_location_id
, location_id
from rcv_transactions rt
where nvl(transfer_lpn_id,-999) <> nvl(lpn_id,-999)
and group_id = p_transaction_id
AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
AND p_label_type_info.business_flow_code = 2)
OR (rt.transaction_type = 'DELIVER'
AND p_label_type_info.business_flow_code in (3,4))
OR (rt.transaction_type = 'RECEIVE'
--AND rt.routing_header_id <> 3 Modified for Bug: 4312020
AND p_label_type_info.business_flow_code = 1
)
)
UNION all
-- Parent LPN of Transfer LPN
select lpn.parent_lpn_id
, rt.po_header_id, rt.po_line_id
, rt.subinventory, rt.locator_id
, rt.shipment_header_id, rt.po_line_location_id
, rt.vendor_id, rt.vendor_site_id
, rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
, rt.location_id location_id
from wms_license_plate_numbers lpn, rcv_transactions rt
where lpn.lpn_id = rt.transfer_lpn_id
and rt.transfer_lpn_id <> rt.lpn_id
and lpn.parent_lpn_id is not null
and lpn.parent_lpn_id <> lpn.lpn_id
and rt.group_id = p_transaction_id
AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
AND p_label_type_info.business_flow_code = 2)
OR (rt.transaction_type = 'DELIVER'
AND p_label_type_info.business_flow_code in (3,4))
OR (rt.transaction_type = 'RECEIVE'
--AND rt.routing_header_id <> 3 Modified for Bug: 4312020
AND p_label_type_info.business_flow_code = 1
)
)
UNION ALL
-- Outermost LPN of Transfer LPN
select lpn.outermost_lpn_id
, rt.po_header_id, rt.po_line_id
, rt.subinventory, rt.locator_id
, rt.shipment_header_id, rt.po_line_location_id
, rt.vendor_id, rt.vendor_site_id
, rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
, rt.location_id location_id
from wms_license_plate_numbers lpn, rcv_transactions rt
where lpn.lpn_id = rt.transfer_lpn_id
and rt.transfer_lpn_id <> rt.lpn_id
and lpn.outermost_lpn_id <> lpn.lpn_id
and lpn.outermost_lpn_id <> lpn.parent_lpn_id
and rt.group_id = p_transaction_id
AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
AND p_label_type_info.business_flow_code = 2)
OR (rt.transaction_type = 'DELIVER'
AND p_label_type_info.business_flow_code in (3,4))
OR (rt.transaction_type = 'RECEIVE'
--AND rt.routing_header_id <> 3 Modified for Bug: 4312020
AND p_label_type_info.business_flow_code = 1
)
)
) all_lpn
, po_headers_trx_v pha--CLM Changes, using CLM views instead of base tables
, po_lines_trx_v pol
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
-- MOAC : changed po_line_locations to po_line_locations_all
, po_line_locations_trx_v pll
, po_vendors pov
-- , hr_locations_all hrl1
-- , hr_locations_all hrl2
-- MOAC : changed po_vendor_sites to po_vendor_sites_all
, po_vendor_sites_all pvs
, per_people_f ppf
, wms_license_plate_numbers wlpn -- Bug 3836623
WHERE pha.po_header_id(+) = all_lpn.po_header_id
AND rsh.shipment_header_id(+) = all_lpn.shipment_header_id
AND rsh.shipment_header_id = rsl.shipment_header_id
/* Bug 5241400, Add where clause for rsl and appl_lpn location_id */
/* Bug 5336350, also need to consider case when po_line_location_id is null, Intransit Shipment or RMA txns */
AND ((rsl.po_line_location_id IS NULL and all_lpn.po_line_location_id IS NULL) OR
rsl.po_line_location_id = all_lpn.po_line_location_id)
AND pol.po_line_id (+) = all_lpn.po_line_id
AND pol.po_header_id (+) = all_lpn.po_header_id
AND pll.line_location_id(+) = all_lpn.po_line_location_id
AND pov.vendor_id(+) = all_lpn.vendor_id
-- AND pvs.vendor_id(+) = all_lpn.vendor_id -- Unesseccary line dherring 8/2/05
AND pvs.vendor_site_id(+) = all_lpn.vendor_site_id
AND ppf.person_id(+) = all_lpn.deliver_to_person_id
-- Bug 3836623, for receiving putaway, do not print if the
-- LPN is picked (11), which will be doing cross docking
-- label will be printed during cross docking business flow
AND wlpn.lpn_id = all_lpn.lpn_id
AND (p_label_type_info.business_flow_code <> 4 OR
(p_label_type_info.business_flow_code = 4 AND
wlpn.lpn_context <> 11))
-- AND hrl1.location_id(+) = all_lpn.deliver_to_location_id
-- AND hrl2.location_id(+) = all_lpn.location_id
ORDER BY all_lpn.lpn_id, pol.item_id -- added bug # 10042570
;
Select
decode(l_deliver_to_location_id,null,null,hrl1.location_code)
deliver_to_location
, decode(l_location_id,null,null,hrl2.location_code) location
from hr_locations_all hrl1
, hr_locations_all hrl2
where hrl1.location_id = decode(l_deliver_to_location_id,null,hrl1.location_id,l_deliver_to_location_id)
AND hrl2.location_id = decode(l_location_id,null,hrl2.location_id,l_location_id)
and hrl1.location_id = hrl2.location_id;
SELECT mmtt.lpn_id,
mmtt.content_lpn_id,
mmtt.transfer_lpn_id,
mmtt.transfer_subinventory,
mmtt.transfer_to_location,
mmtt.transaction_type_id,
mmtt.transaction_action_id,
mmtt.transaction_uom, --Bug# 3739739
-- Bug 2515486: Added transaction_type_id, transaction_action_id, inventory_item_id
--Conf Label ER - Adding organization_id,inventory_item_id,revision,locator_id
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision,
mmtt.subinventory_code,
mmtt.locator_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_id
AND rownum<2;
SELECT nvl(mmtt.transfer_lpn_id, mmtt.content_lpn_id), mmtt.organization_id, mmtt.inventory_item_id,
mtlt.lot_number, mmtt.revision,
abs(nvl(mtlt.transaction_quantity,
mmtt.transaction_quantity)) quantity,
mmtt.transaction_uom,
--mmtt.transfer_subinventory, mmtt.transfer_to_location
mmtt.subinventory_code, mmtt.locator_id --Bug 8528146
, mmtt.subinventory_code /*from sub, to select printer*/
, abs(nvl(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)) secondary_quantity, -- invocnv changes
mmtt.secondary_uom_code -- invconv changes
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND mmtt.transaction_temp_id = p_transaction_id;
SELECT lpn_id, package_id, content_volume_uom_code, content_volume, gross_weight_uom_code,
gross_weight, inventory_item_id, parent_package_id, pack_level, parent_lpn_id,
header_id, packaging_mode
FROM wms_packaging_hist
WHERE lpn_id is not null
OR package_id is not null
START WITH parent_lpn_id = p_transaction_id
CONNECT BY PARENT_PACKAGE_ID = PRIOR PACKAGE_ID;
SELECT transfer_lpn_id, organization_id, inventory_item_id,
lot_number, revision, abs(transaction_quantity), transaction_uom,
transfer_subinventory, transfer_to_location,
abs(secondary_transaction_quantity), secondary_uom_code, -- invconv changes
transaction_source_id -- Conf Label ER
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_id;
SELECT lpn_id, subinventory_code, locator_id, abs(transaction_quantity) quantity,--Conf Label ER - Adding organization_id,inventory_item_id,revision
organization_id,inventory_item_id,revision
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_id;
SELECT wdd2.lpn_id, nvl(wdd2.organization_id, wdd1.organization_id)
, wdd1.subinventory
FROM wsh_delivery_details wdd1, wsh_delivery_details wdd2
, wsh_delivery_assignments_v wda
WHERE wdd2.delivery_detail_id = p_transaction_id
AND wdd1.delivery_detail_id(+) = wda.delivery_detail_id
AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id;
SELECT wdd.lpn_id, wlpn.organization_id, wlpn.subinventory_code, wlpn.locator_id-- Added locator for Conf Label ER
FROM wsh_delivery_details wdd, wms_license_plate_numbers wlpn
WHERE wdd.delivery_detail_id = p_transaction_id
AND wdd.lpn_id = wlpn.lpn_id;
SELECT DISTINCT wdd2.lpn_id, wdd1.organization_id /*8736862-added distinct*/
FROM wsh_new_deliveries wnd, wsh_delivery_assignments_v wda
, wsh_delivery_details wdd1, wsh_delivery_details wdd2
WHERE wnd.delivery_id = p_transaction_id
AND wnd.delivery_id = wda.delivery_id
AND wdd1.delivery_detail_id = wda.delivery_detail_id
AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id;
SELECT transfer_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_id;*/
SELECT lpn_id,
--Added below columns from org id to txn_source_line_id for Conf Label ER
organization_id
,inventory_item_id
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,revision
,TXN_SOURCE_ID
,TXN_SOURCE_LINE_ID
FROM mtl_txn_request_lines
WHERE line_id=p_transaction_id;
SELECT lpn_id
--Added below columns from org id to revision for Conf Label ER
,organization_id
,source_header_id
,source_line_id
,subinventory_code
,locator_id
,inventory_item_id
,revision
FROM mtl_transactions_interface
WHERE transaction_interface_id = p_transaction_id;
SELECT distinct
all_lpn.lpn_id
, pha.segment1 purchase_order
, all_lpn.subinventory_code
, all_lpn.locator_id
, nvl(pll.promised_date, pll.need_by_date) due_date
, all_lpn.packing_slip
, all_lpn.truck_num
, all_lpn.country_of_origin_code
, all_lpn.comments
, pol.line_num po_line_number
, pll.quantity quantity_ordered
, all_lpn.vendor_item_num supplier_part_number
, pov.vendor_id vendor_id
, pvs.vendor_site_id vendor_site_id
, pov.vendor_name supplier_name
, pvs.vendor_site_code supplier_site
, ppf.full_name requestor
, hrl1.location_code deliver_to_location
, hrl2.location_code location
, pll.note_to_receiver note_to_receiver
FROM(
select lpn.lpn_id
, rsl.po_header_id, rsl.po_line_id
, lpn.subinventory_code, lpn.locator_id
, rsh.shipment_header_id, rsl.po_line_location_id
, rsh.vendor_id, rsh.vendor_site_id
, rsl.deliver_to_person_id, rsl.deliver_to_location_id
, '' location_id
, rsh.packing_slip
, rsl.truck_num
, rsl.COUNTRY_OF_ORIGIN_CODE
, rsl.comments
, rsl.vendor_item_num
from wms_license_plate_numbers lpn,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where lpn.source_name = rsh.shipment_num
AND lpn.lpn_context = 7
AND rsl.shipment_header_id = rsh.shipment_header_id
and rsh.shipment_header_id = p_transaction_id
and rsl.asn_lpn_id = lpn.lpn_id
AND rsh.asn_type = 'ASN'
UNION
select lpn.parent_lpn_id
, rsl.po_header_id, rsl.po_line_id
, lpn.subinventory_code, lpn.locator_id
, rsh.shipment_header_id, rsl.po_line_location_id
, rsh.vendor_id, rsh.vendor_site_id
, rsl.deliver_to_person_id, rsl.deliver_to_location_id
, '' location_id
, rsh.packing_slip
, rsl.truck_num
, rsl.COUNTRY_OF_ORIGIN_CODE
, rsl.comments
, rsl.vendor_item_num
from wms_license_plate_numbers lpn,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where lpn.source_name = rsh.shipment_num
AND lpn.lpn_context = 7
AND rsl.shipment_header_id = rsh.shipment_header_id
and rsl.asn_lpn_id = lpn.lpn_id
and rsh.shipment_header_id = p_transaction_id
AND rsh.asn_type = 'ASN'
UNION
select lpn.outermost_lpn_id
, rsl.po_header_id, rsl.po_line_id
, lpn.subinventory_code, lpn.locator_id
, rsh.shipment_header_id, rsl.po_line_location_id
, rsh.vendor_id, rsh.vendor_site_id
, rsl.deliver_to_person_id, rsl.deliver_to_location_id
, '' location_id
, rsh.packing_slip
, rsl.truck_num
, rsl.COUNTRY_OF_ORIGIN_CODE
, rsl.comments
, rsl.vendor_item_num
from wms_license_plate_numbers lpn,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where lpn.source_name = rsh.shipment_num
AND lpn.lpn_context = 7
AND rsl.shipment_header_id = rsh.shipment_header_id
and rsh.shipment_header_id = p_transaction_id
and rsl.asn_lpn_id = lpn.lpn_id
AND rsh.asn_type = 'ASN'
) all_lpn
, po_headers_trx_v pha--CLM Changes, using CLM views instead of base tables
, po_lines_trx_v pol
, rcv_shipment_headers rsh
-- MOAC : changed po_line_locations to po_line_locations_all
, po_line_locations_trx_v pll
, po_vendors pov
, hr_locations_all hrl1
, hr_locations_all hrl2
-- MOAC : changed po_vendor_sites to po_vendor_sites_all
, po_vendor_sites_all pvs
, per_people_f ppf
WHERE pha.po_header_id(+) = all_lpn.po_header_id
AND rsh.shipment_header_id(+) = all_lpn.shipment_header_id
AND pol.po_line_id (+) = all_lpn.po_line_id
AND pol.po_header_id (+) = all_lpn.po_header_id
AND pll.line_location_id(+) = all_lpn.po_line_location_id
AND pov.vendor_id(+) = all_lpn.vendor_id
-- AND pvs.vendor_id(+) = all_lpn.vendor_id -- Unesseccary line dherring 8/2/05
AND pvs.vendor_site_id(+) = all_lpn.vendor_site_id
AND ppf.person_id(+) = all_lpn.deliver_to_person_id
AND hrl1.location_id(+) = all_lpn.deliver_to_location_id
AND hrl2.location_id(+) = all_lpn.location_id
AND all_lpn.lpn_id = nvl(p_lpn_id, all_lpn.lpn_id);
SELECT lpn.LICENSE_PLATE_NUMBER lpn
, plpn.lpn_id parent_lpn_id
, plpn.license_plate_number parent_lpn
, olpn.license_plate_number outermost_lpn
, msik.INVENTORY_ITEM_ID container_item_id
, msik.concatenated_segments container_item
, nvl(lpn.CONTENT_VOLUME, l_content_volume) volume
, nvl(lpn.CONTENT_VOLUME_UOM_CODE, l_content_volume_uom_code) volume_uom
, nvl(lpn.GROSS_WEIGHT, l_gross_weight) gross_weight
, nvl(lpn.GROSS_WEIGHT_UOM_CODE, l_gross_weight_uom_code) gross_weight_uom
, nvl(lpn.TARE_WEIGHT, msik.unit_weight) tare_weight
, nvl(lpn.TARE_WEIGHT_UOM_CODE, msik.weight_uom_code) tare_weight_uom
, lpn.attribute_category lpn_attribute_category
, lpn.attribute1 lpn_attribute1
, lpn.attribute2 lpn_attribute2
, lpn.attribute3 lpn_attribute3
, lpn.attribute4 lpn_attribute4
, lpn.attribute5 lpn_attribute5
, lpn.attribute6 lpn_attribute6
, lpn.attribute7 lpn_attribute7
, lpn.attribute8 lpn_attribute8
, lpn.attribute9 lpn_attribute9
, lpn.attribute10 lpn_attribute10
, lpn.attribute11 lpn_attribute11
, lpn.attribute12 lpn_attribute12
, lpn.attribute13 lpn_attribute13
, lpn.attribute14 lpn_attribute14
, lpn.attribute15 lpn_attribute15
, nvl(wph.parent_package_id, l_parent_package_id) parent_package
, nvl(wph.pack_level, l_pack_level) pack_level
FROM WMS_LICENSE_PLATE_NUMBERS lpn
, WMS_PACKAGING_HIST wph
, WMS_LICENSE_PLATE_NUMBERS plpn
, WMS_LICENSE_PLATE_NUMBERS olpn
, MTL_SYSTEM_ITEMS_KFV msik
/*Commented for bug# 6334460 start
, DUAL d
WHERE d.dummy = 'X'
AND lpn.license_plate_number (+) <> NVL('@@@',d.dummy)
Commented for bug# 6334460 end */
WHERE lpn.lpn_id (+) = p_lpn_id
AND wph.lpn_id (+) = lpn.lpn_id
AND plpn.lpn_id (+) = NVL(lpn.parent_lpn_id, l_parent_lpn_id)
AND olpn.lpn_id (+) = NVL(lpn.outermost_lpn_id, l_outermost_lpn_id)
AND msik.organization_id (+) = p_org_id
AND msik.inventory_item_id (+) = NVL(lpn.inventory_item_id, l_inventory_item_id);
SELECT mp.organization_code organization
, msik.concatenated_segments item
, WMS_DEPLOY.GET_CLIENT_ITEM(p_org_id, msik.inventory_item_id) client_item -- Added for LSP Project, bug 9087971
, msik.description item_description
, msik.attribute_category item_attribute_category
, msik.attribute1 item_attribute1
, msik.attribute2 item_attribute2
, msik.attribute3 item_attribute3
, msik.attribute4 item_attribute4
, msik.attribute5 item_attribute5
, msik.attribute6 item_attribute6
, msik.attribute7 item_attribute7
, msik.attribute8 item_attribute8
, msik.attribute9 item_attribute9
, msik.attribute10 item_attribute10
, msik.attribute11 item_attribute11
, msik.attribute12 item_attribute12
, msik.attribute13 item_attribute13
, msik.attribute14 item_attribute14
, msik.attribute15 item_attribute15
, to_char(mtlt.lot_expiration_date, G_DATE_FORMAT_MASK) lot_expiration_date
, poh.hazard_class item_hazard_class
, mtlt.lot_attribute_category lot_attribute_category
, mtlt.c_attribute1 lot_c_attribute1
, mtlt.c_attribute2 lot_c_attribute2
, mtlt.c_attribute3 lot_c_attribute3
, mtlt.c_attribute4 lot_c_attribute4
, mtlt.c_attribute5 lot_c_attribute5
, mtlt.c_attribute6 lot_c_attribute6
, mtlt.c_attribute7 lot_c_attribute7
, mtlt.c_attribute8 lot_c_attribute8
, mtlt.c_attribute9 lot_c_attribute9
, mtlt.c_attribute10 lot_c_attribute10
, mtlt.c_attribute11 lot_c_attribute11
, mtlt.c_attribute12 lot_c_attribute12
, mtlt.c_attribute13 lot_c_attribute13
, mtlt.c_attribute14 lot_c_attribute14
, mtlt.c_attribute15 lot_c_attribute15
, mtlt.c_attribute16 lot_c_attribute16
, mtlt.c_attribute17 lot_c_attribute17
, mtlt.c_attribute18 lot_c_attribute18
, mtlt.c_attribute19 lot_c_attribute19
, mtlt.c_attribute20 lot_c_attribute20
, to_char(mtlt.D_ATTRIBUTE1, G_DATE_FORMAT_MASK) lot_d_attribute1
, to_char(mtlt.D_ATTRIBUTE2, G_DATE_FORMAT_MASK) lot_d_attribute2
, to_char(mtlt.D_ATTRIBUTE3, G_DATE_FORMAT_MASK) lot_d_attribute3
, to_char(mtlt.D_ATTRIBUTE4, G_DATE_FORMAT_MASK) lot_d_attribute4
, to_char(mtlt.D_ATTRIBUTE5, G_DATE_FORMAT_MASK) lot_d_attribute5
, to_char(mtlt.D_ATTRIBUTE6, G_DATE_FORMAT_MASK) lot_d_attribute6
, to_char(mtlt.D_ATTRIBUTE7, G_DATE_FORMAT_MASK) lot_d_attribute7
, to_char(mtlt.D_ATTRIBUTE8, G_DATE_FORMAT_MASK) lot_d_attribute8
, to_char(mtlt.D_ATTRIBUTE9, G_DATE_FORMAT_MASK) lot_d_attribute9
, to_char(mtlt.D_ATTRIBUTE10, G_DATE_FORMAT_MASK) lot_d_attribute10
, mtlt.n_attribute1 lot_n_attribute1
, mtlt.n_attribute2 lot_n_attribute2
, mtlt.n_attribute3 lot_n_attribute3
, mtlt.n_attribute4 lot_n_attribute4
, mtlt.n_attribute5 lot_n_attribute5
, mtlt.n_attribute6 lot_n_attribute6
, mtlt.n_attribute7 lot_n_attribute7
, mtlt.n_attribute8 lot_n_attribute8
, mtlt.n_attribute9 lot_n_attribute9
, mtlt.n_attribute10 lot_n_attribute10
, mtlt.TERRITORY_CODE lot_country_of_origin
, mtlt.grade_code lot_grade_code
, to_char(mtlt.ORIGINATION_DATE, G_DATE_FORMAT_MASK) lot_origination_date
, mtlt.DATE_CODE lot_date_code
, to_char(mtlt.CHANGE_DATE, G_DATE_FORMAT_MASK) lot_change_date
, mtlt.AGE lot_age
, to_char(mtlt.RETEST_DATE, G_DATE_FORMAT_MASK) lot_retest_date
, to_char(mtlt.MATURITY_DATE, G_DATE_FORMAT_MASK) lot_maturity_date
, mtlt.ITEM_SIZE lot_item_size
, mtlt.COLOR lot_color
, mtlt.VOLUME lot_volume
, mtlt.VOLUME_UOM lot_volume_uom
, mtlt.PLACE_OF_ORIGIN lot_place_of_origin
, to_char(mtlt.BEST_BY_DATE, G_DATE_FORMAT_MASK) lot_best_by_date
, mtlt.length lot_length
, mtlt.length_uom lot_length_uom
, mtlt.recycled_content lot_recycled_cont
, mtlt.thickness lot_thickness
, mtlt.thickness_uom lot_thickness_uom
, mtlt.width lot_width
, mtlt.width_uom lot_width_uom
, mtlt.curl_wrinkle_fold lot_curl
, mtlt.vendor_name lot_vendor
, mmsv.status_code lot_number_status
, mtlt.parent_lot_number
, mtlt.expiration_action_date
, mtlt.origination_type
, mtlt. hold_date
, mtlt.expiration_action_code
, mtlt.supplier_lot_number
FROM mtl_parameters mp
, mtl_system_items_kfv msik
, mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt
, po_hazard_classes poh
, mtl_material_statuses_vl mmsv
WHERE msik.inventory_item_id = p_item_id
AND msik.organization_id = p_org_id
AND mp.organization_id = msik.organization_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND poh.hazard_class_id (+) = msik.hazard_class_id
AND mtlt.lot_number (+) = p_lot_number
AND mmsv.status_id (+) = mtlt.status_id;
SELECT mp.organization_code organization
, msik.concatenated_segments item
, WMS_DEPLOY.GET_CLIENT_ITEM(p_org_id, msik.inventory_item_id) client_item -- Added for LSP Project, bug 9087971
, msik.description item_description
, msik.attribute_category item_attribute_category
, msik.attribute1 item_attribute1
, msik.attribute2 item_attribute2
, msik.attribute3 item_attribute3
, msik.attribute4 item_attribute4
, msik.attribute5 item_attribute5
, msik.attribute6 item_attribute6
, msik.attribute7 item_attribute7
, msik.attribute8 item_attribute8
, msik.attribute9 item_attribute9
, msik.attribute10 item_attribute10
, msik.attribute11 item_attribute11
, msik.attribute12 item_attribute12
, msik.attribute13 item_attribute13
, msik.attribute14 item_attribute14
, msik.attribute15 item_attribute15
, to_char(mln.expiration_date, G_DATE_FORMAT_MASK) lot_expiration_date -- Added for Bug 2795525,
, poh.hazard_class item_hazard_class
, mln.lot_attribute_category lot_attribute_category
, mln.c_attribute1 lot_c_attribute1
, mln.c_attribute2 lot_c_attribute2
, mln.c_attribute3 lot_c_attribute3
, mln.c_attribute4 lot_c_attribute4
, mln.c_attribute5 lot_c_attribute5
, mln.c_attribute6 lot_c_attribute6
, mln.c_attribute7 lot_c_attribute7
, mln.c_attribute8 lot_c_attribute8
, mln.c_attribute9 lot_c_attribute9
, mln.c_attribute10 lot_c_attribute10
, mln.c_attribute11 lot_c_attribute11
, mln.c_attribute12 lot_c_attribute12
, mln.c_attribute13 lot_c_attribute13
, mln.c_attribute14 lot_c_attribute14
, mln.c_attribute15 lot_c_attribute15
, mln.c_attribute16 lot_c_attribute16
, mln.c_attribute17 lot_c_attribute17
, mln.c_attribute18 lot_c_attribute18
, mln.c_attribute19 lot_c_attribute19
, mln.c_attribute20 lot_c_attribute20
, to_char(mln.D_ATTRIBUTE1, G_DATE_FORMAT_MASK) lot_d_attribute1 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE2, G_DATE_FORMAT_MASK) lot_d_attribute2 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE3, G_DATE_FORMAT_MASK) lot_d_attribute3 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE4, G_DATE_FORMAT_MASK) lot_d_attribute4 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE5, G_DATE_FORMAT_MASK) lot_d_attribute5 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE6, G_DATE_FORMAT_MASK) lot_d_attribute6 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE7, G_DATE_FORMAT_MASK) lot_d_attribute7 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE8, G_DATE_FORMAT_MASK) lot_d_attribute8 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE9, G_DATE_FORMAT_MASK) lot_d_attribute9 -- Added for Bug 2795525,
, to_char(mln.D_ATTRIBUTE10, G_DATE_FORMAT_MASK) lot_d_attribute10 -- Added for Bug 2795525,
, mln.n_attribute1 lot_n_attribute1
, mln.n_attribute2 lot_n_attribute2
, mln.n_attribute3 lot_n_attribute3
, mln.n_attribute4 lot_n_attribute4
, mln.n_attribute5 lot_n_attribute5
, mln.n_attribute6 lot_n_attribute6
, mln.n_attribute7 lot_n_attribute7
, mln.n_attribute8 lot_n_attribute8
, mln.n_attribute9 lot_n_attribute9
, mln.n_attribute10 lot_n_attribute10
, mln.TERRITORY_CODE lot_country_of_origin
, mln.grade_code lot_grade_code
, to_char(mln.ORIGINATION_DATE, G_DATE_FORMAT_MASK) lot_origination_date -- Added for Bug 2795525,
, mln.DATE_CODE lot_date_code
, to_char(mln.CHANGE_DATE, G_DATE_FORMAT_MASK) lot_change_date -- Added for Bug 2795525,
, mln.AGE lot_age
, to_char(mln.RETEST_DATE, G_DATE_FORMAT_MASK) lot_retest_date -- Added for Bug 2795525,
, to_char(mln.MATURITY_DATE, G_DATE_FORMAT_MASK) lot_maturity_date -- Added for Bug 2795525,
, mln.ITEM_SIZE lot_item_size
, mln.COLOR lot_color
, mln.VOLUME lot_volume
, mln.VOLUME_UOM lot_volume_uom
, mln.PLACE_OF_ORIGIN lot_place_of_origin
, to_char(mln.BEST_BY_DATE, G_DATE_FORMAT_MASK) lot_best_by_date -- Added for Bug 2795525,
, mln.length lot_length
, mln.length_uom lot_length_uom
, mln.recycled_content lot_recycled_cont
, mln.thickness lot_thickness
, mln.thickness_uom lot_thickness_uom
, mln.width lot_width
, mln.width_uom lot_width_uom
, mln.curl_wrinkle_fold lot_curl
, mln.vendor_name lot_vendor
, mmsv.status_code lot_number_status
, mln.parent_lot_number parent_lot_number -- invconv changes start
, mln.expiration_action_date expiration_action_date
, mln.origination_type origination_type
, mln.hold_date hold_date
, mln.expiration_action_code expiration_action_code
, mln.supplier_lot_number supplier_lot_number -- invconv changes end
FROM mtl_parameters mp
,mtl_system_items_kfv msik
, mtl_lot_numbers mln
, po_hazard_classes poh
, mtl_material_statuses_vl mmsv
WHERE msik.inventory_item_id = p_item_id
AND msik.organization_id = p_org_id
AND mp.organization_id = msik.organization_id
AND mln.organization_id (+) = msik.organization_id
AND mln.inventory_item_id (+) = msik.inventory_item_id
AND poh.hazard_class_id (+) = msik.hazard_class_id
AND mln.lot_number (+) = p_lot_number
AND mmsv.status_id (+) = mln.status_id;
SELECT
nvl(p_organization_id, plpn.organization_id) organization_id
, nvl(p_inventory_item_id, wlc.inventory_item_id) inventory_item_id
, nvl(p_revision, wlc.revision) revision
, nvl(p_lot_number,wlc.lot_number) lot_number
, sum(nvl(p_qty, wlc.quantity)) quantity
, nvl(p_uom, wlc.uom_code) uom
, nvl(p_cost_group_id, wlc.cost_group_id) cost_group_id
, ccg.cost_group cost_group
, milkfv.subinventory_code subinventory_code
, milkfv.inventory_location_id locator_id
, INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
, sum(nvl(l_secondary_quantity,wlc.secondary_quantity)) secondary_quantity -- invconv fabdi
, wlc.secondary_uom_code secondary_uom -- invconv fabdi
FROM wms_lpn_contents wlc
, wms_license_plate_numbers plpn
, cst_cost_groups ccg
, mtl_item_locations milkfv
WHERE plpn.lpn_id in (select /*+ cardinality(1) */ lpn_id from wms_license_plate_numbers
where 1=1
-- Bug 4137707
--start with lpn_id in (select nvl(p_lpn_id, -99) from dual
--union all
--select lpn_id from wms_packaging_hist
--where pack_level = 0
--and lpn_id IS not null
--start with parent_package_id = p_package_id
--connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
start with lpn_id = p_lpn_id
connect by parent_lpn_id = prior lpn_id)
AND wlc.parent_lpn_id(+) = plpn.lpn_id
AND milkfv.organization_id (+) = NVL(p_organization_id, plpn.organization_id)
-- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074.
-- Bug 4137707
--AND milkfv.subinventory_code(+) =
-- DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
-- nvl(l_subinventory_code,plpn.subinventory_code))
--AND milkfv.inventory_location_id(+) =
-- DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
-- nvl(l_locator_id, plpn.locator_id))
AND milkfv.subinventory_code(+) = nvl(l_subinventory_code,plpn.subinventory_code)
AND milkfv.inventory_location_id(+) = nvl(l_locator_id, plpn.locator_id)
AND ccg.cost_group_id (+) = nvl(p_cost_group_id, wlc.cost_group_id)
-- Added the AND for fix to Bug 2764074..
--Bug 6523723 Added IS NULL condition.
AND (nvl(p_inventory_item_id, wlc.inventory_item_id) IS NOT NULL
OR (nvl(p_inventory_item_id, wlc.inventory_item_id) IS NULL AND
p_label_type_info.business_flow_code IS NULL AND plpn.lpn_context=5)) --bug 14796348
-- Added for Bug 3581021 by joabraha
-- AND wlc.inventory_item_id = nvl(p_item_id,wlc.inventory_item_id)
-- Bug 4280265, Pick Load
-- The above where clause caused a regression problem for pick load txn
-- where lpn content is not packed to wlc yet.
-- changed to the following
AND nvl(wlc.inventory_item_id,-999) = nvl(p_item_id,nvl(wlc.inventory_item_id,-999))
-- Added the following condition for bug 4387168
-- AND nvl(wlc.lot_number,-1) = nvl(p_lot_number,nvl(wlc.lot_number,-1)) --Bug 8393799
GROUP BY
nvl(p_organization_id, plpn.organization_id)
, nvl(p_inventory_item_id, wlc.inventory_item_id)
, nvl(p_revision, wlc.revision)
, nvl(p_lot_number,wlc.lot_number)
, nvl(p_uom, wlc.uom_code)
, nvl(p_cost_group_id, wlc.cost_group_id)
, ccg.cost_group
, milkfv.subinventory_code
, milkfv.inventory_location_id
, INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id)
, wlc.secondary_uom_code;
SELECT mcce.inventory_item_id
, mcce.organization_id
, mcce.lot_number
, mcce.cost_group_id
, mcce.count_quantity_current
, mcce.count_uom_current
, mcce.revision
, mcce.subinventory
, mcce.locator_id
, mcce.parent_lpn_id
, mcch.cycle_count_header_name
, ppf.full_name requestor
FROM mtl_cycle_count_headers mcch
, mtl_cycle_count_entries mcce
, per_people_f ppf
WHERE mcce.cycle_count_entry_id = p_transaction_Id
AND ppf.person_id(+) = mcce.counted_by_employee_id_current
AND mcce.cycle_count_header_id=mcch.cycle_count_header_id;
SELECT mcch.cycle_count_header_name
, ppf.full_name requestor
FROM mtl_cycle_count_headers mcch
, mtl_cycle_count_entries mcce
, per_people_f ppf
, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id= p_transaction_id
AND mmtt.cycle_count_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND ppf.person_id(+) = mcce.counted_by_employee_id_current ;
SELECT /*+ ORDERED index(PLPN WMS_LICENSE_PLATE_NUMBERS_U1) use_nl(WLC MILKFV CCG) */
plpn.organization_id organization_id
, wlc.inventory_item_id inventory_item_id
, wlc.revision revision
, wlc.lot_number lot_number
, sum(wlc.quantity) quantity
, wlc.uom_code uom
, wlc.cost_group_id cost_group_id
, ccg.cost_group cost_group
, milkfv.subinventory_code subinventory_code
, milkfv.inventory_location_id locator_id
, INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
, sum(nvl(l_secondary_quantity,wlc.secondary_quantity)) secondary_quantity -- invconv fabdi
, wlc.secondary_uom_code secondary_uom -- invconv fabdi
FROM wms_lpn_contents wlc
, wms_license_plate_numbers plpn
, cst_cost_groups ccg
, mtl_item_locations milkfv
WHERE plpn.lpn_id in (
select /*+ cardinality(1) */ id from
((select lpn_id id from wms_license_plate_numbers
where 1=1
start with lpn_id in (select nvl(p_lpn_id, -99) from dual
union all
select /*+ cardinality(1) */ lpn_id from wms_packaging_hist
where pack_level = 0
and lpn_id IS not null
start with parent_package_id = p_package_id
connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
connect by parent_lpn_id = prior lpn_id) ) t )
AND wlc.parent_lpn_id(+) = plpn.lpn_id
AND milkfv.organization_id (+) = plpn.organization_id
-- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074.
AND milkfv.subinventory_code(+) =
DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
plpn.subinventory_code)
AND milkfv.inventory_location_id(+) =
DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
plpn.locator_id)
AND ccg.cost_group_id (+) = wlc.cost_group_id
-- Added the AND for fix to Bug 2764074..
-- Bug 4137707
-- Do not need the where clause about p_item_id
--AND nvl(p_inventory_item_id, wlc.inventory_item_id) IS NOT NULL
-- Added for Bug 3581021 by joabraha
-- AND wlc.inventory_item_id = nvl(p_item_id,wlc.inventory_item_id)
-- Bug 4280265, Pick Load
-- The above where clause caused a regression problem for pick load txn
-- where lpn content is not packed to wlc yet.
-- changed to the following
--AND nvl(wlc.inventory_item_id,-999) = nvl(p_item_id,nvl(wlc.inventory_item_id,-999))
GROUP BY
plpn.organization_id
, wlc.inventory_item_id
, wlc.revision
, wlc.lot_number
, wlc.uom_code
, wlc.cost_group_id
, ccg.cost_group
, milkfv.subinventory_code
, milkfv.inventory_location_id
, INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id)
, wlc.secondary_uom_code
UNION ALL
-- The Subinventory and location information is not required for the Outbound Stuff like Pick Release
-- and Pick Confirm. Hence the decode for the sub and the loc in the where clause of this cursor.
SELECT /*+ ORDERED index(MMTT MTL_MATERIAL_TRANS_TEMP_U1) rowid(WPC) use_nl(WPC MMTT MSI CSG MILKFV) index(MSI MTL_SYSTEM_ITEMS_B_U1)*/
wpc.organization_id organization_id
, wpc.inventory_item_id inventory_item_id
, wpc.revision revision
, wpc.lot_number lot_number
, sum(wpc.primary_quantity) quantity
, msi.primary_uom_code uom
, mmtt.cost_group_id cost_group_id
, ccg.cost_group cost_group
, milkfv.subinventory_code subinventory_code
, milkfv.inventory_location_id locator_id
, INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
, l_secondary_quantity secondary_quantity -- invconv fabdi
, l_secondary_uom secondary_uom -- invconv fabdi
FROM wms_packaging_hist wpc
, mtl_material_transactions_temp mmtt
, mtl_system_items msi
, cst_cost_groups ccg
, mtl_item_locations milkfv
-- Bug 4137707, Do not need to include this where clause,
-- This will be controlled when opening this cursor
-- WHERE cartonization_flag = 1 --Cartonization Flow
WHERE wpc.rowid in ( select /*+ cardinality(1) */ id from ((select rowid id from wms_packaging_hist
where pack_level = 0
AND header_id = l_header_id
AND l_packaging_mode in (WMS_CARTNZN_WRAP.PR_PKG_MODE, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode)
-- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074..
AND lpn_id is null
start with parent_lpn_id = p_lpn_id
connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID
union all
select rowid from wms_packaging_hist
where pack_level = 0
AND lpn_id is null
start with parent_package_id = p_package_id
connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID) ) t )
AND mmtt.transaction_temp_id (+) = wpc.reference_id
AND msi.inventory_item_id (+) = wpc.inventory_item_id
AND msi.organization_id (+) = wpc.organization_id
AND milkfv.organization_id (+) = mmtt.organization_id
-- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074..
AND milkfv.subinventory_code(+) =
DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
mmtt.subinventory_code)
AND milkfv.inventory_location_id(+) =
DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
mmtt.locator_id)
AND ccg.cost_group_id (+) = mmtt.cost_group_id
GROUP BY
wpc.organization_id
, wpc.inventory_item_id
, wpc.revision
, wpc.lot_number
, msi.primary_uom_code
, mmtt.cost_group_id
, ccg.cost_group
, milkfv.subinventory_code
, milkfv.inventory_location_id
, INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id);
SELECT msn.cost_group_id
, ccg.cost_group
FROM mtl_serial_numbers msn
, cst_cost_groups ccg
WHERE msn.lpn_id = p_lpn_id
AND msn.inventory_item_id = p_inventory_item_id
AND msn.lot_number = p_lot_number
AND msn.cost_group_id = ccg.cost_group_id;
SELECT meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_LOT_ORIGINATION_TYPE'
AND lookup_code = p_origination_type;
l_selected_fields INV_LABEL.label_field_variable_tbl_type;
l_selected_fields_count NUMBER;
SELECT lpn_id
, license_plate_number
, parent_lpn_id
, outermost_lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_parent_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id;
SELECT lpn_id
, license_plate_number
, parent_lpn_id
, outermost_lpn_id
FROM wms_license_plate_numbers
START WITH parent_lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT DISTINCT header_id, packaging_mode , pack_level
INTO l_header_id, l_packaging_mode,l_pack_level
FROM WMS_PACKAGING_HIST
WHERE parent_lpn_id = p_transaction_id;
SELECT shipment_num asn_num, shipped_date shipment_date,
expected_receipt_date,freight_terms,
freight_carrier_code, num_of_containers,
bill_of_lading, waybill_airbill_num,
packing_slip,
packaging_code, special_handling_code,
receipt_num, comments
INTO l_rcv_isp_header.asn_num, l_rcv_isp_header.shipment_date,
l_rcv_isp_header.expected_receipt_date, l_rcv_isp_header.freight_terms,
l_rcv_isp_header.freight_carrier, l_rcv_isp_header.num_of_containers,
l_rcv_isp_header.bill_of_lading, l_rcv_isp_header.waybill_airbill_num,
l_rcv_isp_header.packing_slip,
l_rcv_isp_header.packaging_code, l_rcv_isp_header.special_handling_code,
l_rcv_isp_header.receipt_num, l_rcv_isp_header.comments
FROM rcv_shipment_headers
WHERE shipment_header_id = p_transaction_id
-- OR shipment_header_id in --Bug 5051210. Performance fix. Removing OR and adding UNION
UNION
SELECT shipment_num asn_num, shipped_date shipment_date,
expected_receipt_date,freight_terms,
freight_carrier_code, num_of_containers,
bill_of_lading, waybill_airbill_num,
packing_slip,
packaging_code, special_handling_code,
receipt_num, comments
FROM rcv_shipment_headers
WHERE shipment_header_id IN
(select shipment_header_id from rcv_shipment_lines
where asn_lpn_id = p_lpn_id);
SELECT LOT_NUMBER
INTO p_lot_number
FROM MTL_TRANSACTION_LOTS_TEMP
WHERE TRANSACTION_TEMP_ID = p_transaction_id;
For Cost Group Update Bussiness Flow (11), only one label has to be generated with
the updated cost group. Hence the following code (incrementing i, which controls the
loop iteration) will be executed only if the business flow code is not 11
i.e. Cost Group Update Business flow */
IF (p_label_type_info.business_flow_code <> 11) THEN
IF (l_transfer_lpn_id IS NOT NULL)
AND(NVL(l_transfer_lpn_id, -999) <> NVL(l_content_lpn_id, -999)) THEN
l_lpn_table(i) := l_transfer_lpn_id;
l_cust_entity_table.DELETE;
l_cust_entity_table_copy.DELETE;
l_cust_entity_table.DELETE(cust_index);
l_cust_entity_table_copy.DELETE; --Cleanup the table
l_rcv_lpn_table.DELETE;
l_cust_entity_table.DELETE;
l_cust_entity_table_copy.DELETE; --Cleanup the table
l_rcv_lpn_table.DELETE;
l_rcv_lpn_tmp_table.DELETE;--cleanup the table
l_lpn_table.DELETE;
l_lpn_table.DELETE;
trace(' Getting selected fields ');
x_variables => l_selected_fields
, x_variables_count => l_selected_fields_count
, x_is_variable_exist => l_is_epc_exist
, p_format_id => p_label_type_info.default_format_id
, p_exist_variable_name => 'EPC');
IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
IF (l_debug = 1) THEN
trace('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' ||p_label_type_info.default_format_name);
trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
select min(table_a.c) into no_of_rows_per_label
from (select wlfv.label_field_id,
wlf.column_name, count(*) c
from wms_label_field_variables wlfv, wms_label_fields_vl wlf
where wlfv.label_field_id = wlf.label_field_id
and wlfv.label_format_id = p_label_type_info.default_format_id
group by wlfv.label_field_id, wlf.column_name
having count(*)>1 ) table_a;
P_LAST_UPDATE_DATE =>sysdate,
P_LAST_UPDATED_BY =>FND_GLOBAL.user_id,
P_CREATION_DATE =>sysdate,
P_CREATED_BY =>FND_GLOBAL.user_id,
x_return_status =>l_return_status,
x_label_format_id =>l_label_format_id,
x_label_format =>l_label_format,
x_label_request_id =>l_label_request_id);
trace(' Getting selected fields for label_format_id :'||l_label_format_id);
x_variables => l_selected_fields
, x_variables_count => l_selected_fields_count
, x_is_variable_exist => l_is_epc_exist
, p_format_id => l_label_format_id
, p_exist_variable_name => 'EPC');
IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
IF (l_debug = 1) THEN
trace('no fields defined for this format: ' || l_label_format_id || ',' ||l_label_format);
trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
select min(table_a.c) into no_of_rows_per_label
from (select wlfv.label_field_id,
wlf.column_name, count(*) c
from wms_label_field_variables wlfv, wms_label_fields_vl wlf
where wlfv.label_field_id = wlf.label_field_id
and wlfv.label_format_id = l_label_format_id
group by wlfv.label_field_id, wlf.column_name
having count(*)>1 ) table_a;
P_LAST_UPDATE_DATE =>sysdate,
P_LAST_UPDATED_BY =>FND_GLOBAL.user_id,
P_CREATION_DATE =>sysdate,
P_CREATED_BY =>FND_GLOBAL.user_id,
p_use_rule_engine => 'N',
x_return_status =>l_return_status,
x_label_format_id =>l_label_format_id,
x_label_format =>l_label_format,
x_label_request_id =>l_label_request_id);
/* Loop for each selected fields, find the columns and write into the XML_content*/
---------------------------------------------------------------------------------------------
-- Project: 'Custom Labels' (A 11i10+ Project) |
-- Author: Dinesh ([email protected]) |
-- Change Description: |
-- For the column name 'sql_stmt', if the variable name is not null implies that the field |
-- is a Custom SQL. For this variable name, get the corresponding SQL statement using the |
-- function get_sql_for_variable(). Handle the sql appropriately. |
---------------------------------------------------------------------------------------------
IF l_counter_var = 1 THEN -- 14481635
l_counter_var := l_counter_var + 1; -- 14481635
select max(table_a.c) into max_no_of_rows_defined
from (select wlfv.label_field_id,
wlf.column_name, count(*) c
from wms_label_field_variables wlfv, wms_label_fields_vl wlf
where wlfv.label_field_id = wlf.label_field_id
and wlfv.label_format_id = l_label_format_id
group by wlfv.label_field_id, wlf.column_name) table_a;
FOR j IN 1..l_selected_fields.count LOOP
IF l_selected_fields(j).column_name <>'sql_stmt' AND -- Added for Bug#14609151
(j=1 OR l_selected_fields(j).column_name <> l_selected_fields(j-1).column_name) THEN
l_variable_name := get_variable_name(l_selected_fields(j).column_name,
i-1, l_label_format_id);
select max(table_a.c) into max_no_of_rows_defined
from (select wlfv.label_field_id,
wlf.column_name, count(*) c
from wms_label_field_variables wlfv, wms_label_fields_vl wlf
where wlfv.label_field_id = wlf.label_field_id
and wlfv.label_format_id = l_label_format_id
group by wlfv.label_field_id, wlf.column_name) table_a;
FOR j IN 1..l_selected_fields.count LOOP
IF l_selected_fields(j).column_name <>'sql_stmt' AND -- Added for Bug#14609151
(j=1 OR l_selected_fields(j).column_name <> l_selected_fields(j-1).column_name) THEN
l_variable_name := get_variable_name(l_selected_fields(j).column_name,
i-1, l_label_format_id);