[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rti.lpn_id lpn_id, rti.to_organization_id to_oragnization_id,
pha.segment1 purchase_order, rti.subinventory, rti.locator_id, l_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_all pha,
-- MOAC : po_line_locations changed to po_line_locations_all
po_lines_all pol, rcv_shipment_headers rsh, po_line_locations_all pll,
po_vendors pov, hr_locations_all hrl1, hr_locations_all hrl2,
-- MOAC : po_vendor_sites changed 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 uneccessary line dherring 8/2/05
AND pvs.vendor_site_id(+) = rti.vendor_site_id
AND ppf.person_id(+) = rti.deliver_to_person_id
AND ppf.EFFECTIVE_END_DATE(+) >= trunc(sysdate) --bug 6501344
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,
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_all pha,
-- MOAC : po_line_locations changed to po_line_locations_all
po_lines_all pol, rcv_shipment_headers rsh, po_line_locations_all pll,
po_vendors pov, hr_locations_all hrl1, hr_locations_all hrl2,
-- MOAC : po_vendor_sites changed 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 uneccessary 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
, pol.item_id item_id
, all_lpn.quantity quantity
, wlpn.license_plate_number /*5758070*/
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
, rt.quantity
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
, rt.quantity
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 rt.group_id = p_transaction_id
and lpn.parent_lpn_id is not null -- parentLPN could be null for single-level LPN
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
, rt.quantity
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 deliver_to_location_id
, location_id location_id
, rt.quantity
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
, rt.quantity
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 <> lpn.lpn_id
and lpn.parent_lpn_id is not null -- parentLPN could be null for single-level LPN
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
, rt.quantity
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_all pha
, po_lines_all pol
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
-- MOAC : po_line_locations changed to po_line_locations_all
, po_line_locations_all pll
, po_vendors pov
-- , hr_locations_all hrl1
-- , hr_locations_all hrl2
-- MOAC : po_vendor_sites changed to po_vendor_sites_all
, po_vendor_sites_all pvs
, per_people_f ppf
, wms_license_plate_numbers wlpn
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 uneccessary 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 ppf.EFFECTIVE_END_DATE(+) >= trunc(sysdate) --6501344
-- Bug 3826298, 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 -- Bug 3836623, add this missing where clause for bug 3826298 fix
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 wlpn.license_plate_number /* 5758070*/
;
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
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_id;
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 /*from sub, to select printer*/
, abs(nvl(mtlt.secondary_quantity, -- invconv fabdi
mmtt.secondary_transaction_quantity)) secondary_quantity
, mmtt.secondary_uom_code -- invconv fabdi
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, subinventory_code, locator_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_id;
SELECT cartonization_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_id;
SELECT lpn_id, from_subinventory_code subinventory_code
FROM mtl_txn_request_lines
WHERE line_id=p_transaction_id;
SELECT lpn_id, subinventory_code
FROM mtl_transactions_interface
WHERE transaction_interface_id = p_transaction_id;
SELECT distinct(wph.parent_package_id), wph.lpn_id, wph1.content_volume_uom_code, wph1.content_volume,
wph1.gross_weight_uom_code, wph1.gross_weight, wph.inventory_item_id, wph1.parent_package_id,
wph1.pack_level, wph.header_id,wph.packaging_mode, wph1.tare_weight, wph1.tare_weight_uom_code,
msik.concatenated_segments container_item, lpn.license_plate_number, wph2.pack_level
FROM wms_packaging_hist wph, wms_packaging_hist wph1, wms_packaging_hist wph2, mtl_system_items_kfv msik,
WMS_LICENSE_PLATE_NUMBERS lpn
WHERE wph.rowid in (select rowid
from wms_packaging_hist
where pack_level = 0
START WITH parent_lpn_id = p_transaction_id
CONNECT BY PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
AND msik.inventory_item_id (+) = wph.parent_item_id
AND msik.organization_id (+) = wph.organization_id
AND wph.parent_package_id = wph1.package_id (+)
AND lpn.lpn_id(+) = wph1.parent_lpn_id
AND wph2.parent_lpn_id = p_transaction_id;
SELECT distinct(wph.parent_package_id), wph.lpn_id, wph1.content_volume_uom_code, wph1.content_volume,
wph1.gross_weight_uom_code, wph1.gross_weight, wph.inventory_item_id, wph1.parent_package_id,
wph1.pack_level, wph.header_id,wph.packaging_mode, wph1.tare_weight, wph1.tare_weight_uom_code,
msik.concatenated_segments container_item,lpn.license_plate_number
FROM wms_packaging_hist wph, wms_packaging_hist wph1, mtl_system_items_kfv
msik, WMS_LICENSE_PLATE_NUMBERS lpn
WHERE wph.rowid in (select rowid
from wms_packaging_hist
where pack_level = 0
START WITH parent_lpn_id = p_transaction_id
CONNECT BY PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
AND msik.inventory_item_id (+) = wph.parent_item_id
AND msik.organization_id (+) = wph.organization_id
AND wph.parent_package_id = wph1.package_id (+)
AND lpn.lpn_id(+) = wph1.parent_lpn_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 fabdi
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
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 transfer_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_id;*/
SELECT DISTINCT wdd2.lpn_id
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 lpn_id
FROM wms_license_plate_numbers
WHERE parent_lpn_id = p_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(l_content_volume, lpn.CONTENT_VOLUME) volume
, nvl(l_content_volume_uom_code, lpn.CONTENT_VOLUME_UOM_CODE) volume_uom
, nvl(l_gross_weight, lpn.GROSS_WEIGHT) gross_weight
, nvl(l_gross_weight_uom_code, lpn.GROSS_WEIGHT_UOM_CODE) gross_weight_uom
, nvl(l_tare_weight, lpn.TARE_WEIGHT) tare_weight
, nvl(l_tare_weight_uom_code, lpn.TARE_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_package_id) parent_package --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
, DUAL d
WHERE d.dummy = 'X'
AND lpn.license_plate_number (+) <> NVL('@@@',d.dummy)
AND 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 (+) = lpn.inventory_item_id;
SELECT mp.organization_code organization
, msik.concatenated_segments item
, 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 -- invconv fabdi start
, mln.expiration_action_date
, mln.origination_type
, mln.hold_date
, mln.expiration_action_code
, mln.supplier_lot_number -- invconv fabdi 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_transaction_qty,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
-- Bug 4137707, Do not need to include this where clause,
-- This will be controlled when opening this cursor
--WHERE cartonization_flag = 0 -- non Cartonization Flow
WHERE --wlc.parent_lpn_id = p_lpn_id /* Modified for the bug # 4771610*/
wlc.parent_lpn_id IN (SELECT lpn_id FROM wms_license_plate_numbers plpn
WHERE 1 = 1
start with lpn_id = p_lpn_id
connect by parent_lpn_id = prior lpn_id)
AND plpn.lpn_id (+) = wlc.parent_lpn_id
AND milkfv.organization_id (+) = NVL(p_organization_id, plpn.organization_id)
-- Bug 4137707
--AND milkfv.subinventory_code(+) = DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.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,
-- 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)
AND nvl(p_inventory_item_id, wlc.inventory_item_id) IS NOT NULL -- Added for Bug 2857568
AND wlc.inventory_item_id = nvl(p_item_id,wlc.inventory_item_id)
-- Added the following condition for bug 4387168
AND nvl(wlc.lot_number,-1) = nvl(p_lot_number,nvl(wlc.lot_number,-1))
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 -- invconv fabdi
/*Fix for the bug 3693953. Added the following Query to this cursor*/
UNION ALL
SELECT nvl(p_organization_id, plpn.organization_id) organization_id
, nvl(p_inventory_item_id, mmtt.inventory_item_id) inventory_item_id
, nvl(p_revision, mmtt.revision) revision
, nvl(p_lot_number,mmtt.lot_number) lot_number
, sum(nvl(p_qty, mmtt.primary_quantity)) quantity
, nvl(p_uom, mmtt.item_primary_uom_code) uom
, nvl(p_cost_group_id, 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
, sum(nvl(l_secondary_transaction_qty,mmtt.secondary_transaction_quantity)) secondary_quantity -- invconv fabdi
, mmtt.secondary_uom_code secondary_uom -- invconv fabdi
FROM wms_license_plate_numbers plpn
, cst_cost_groups ccg
, mtl_item_locations milkfv
, mtl_material_transactions_temp mmtt
-- Bug 4137707, Do not need to include this where clause,
-- This will be controlled when opening this cursor
--WHERE cartonization_flag = 0 -- non Cartonization Flow
WHERE plpn.lpn_id (+) = p_lpn_id
AND p_label_type_info.business_flow_code NOT IN (19) /* Modified for bug# 5168330*/
AND milkfv.organization_id (+) = NVL(p_organization_id, plpn.organization_id)
-- Bug 4137707
--AND milkfv.subinventory_code(+) = DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.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,
-- 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, mmtt.cost_group_id)
AND mmtt.transaction_temp_id = p_transaction_id
AND NOT EXISTS (SELECT 1 from wms_lpn_contents wlc where wlc.parent_lpn_id=p_lpn_id)
GROUP BY
nvl(p_organization_id, plpn.organization_id)
, nvl(p_inventory_item_id, mmtt.inventory_item_id)
, nvl(p_revision, mmtt.revision)
, nvl(p_lot_number,mmtt.lot_number)
, nvl(p_uom, mmtt.item_primary_uom_code)
, nvl(p_cost_group_id, 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)
, mmtt.secondary_uom_code; -- invconv fabdi
SELECT
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_transaction_qty secondary_quantity -- invconv fabdi
, l_secondary_uom_code 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 rowid from wms_packaging_hist
where pack_level = 0
AND header_id = l_header_id
AND packaging_mode = l_packaging_mode
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)
AND mmtt.transaction_temp_id (+) = wpc.reference_id
AND msi.inventory_item_id (+) = mmtt.inventory_item_id
AND msi.organization_id (+) = mmtt.organization_id
AND milkfv.organization_id (+) = mmtt.organization_id
AND milkfv.subinventory_code(+) = DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL,
mmtt.subinventory_code)
AND milkfv.inventory_location_id(+) = DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.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 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_all pha
, po_lines_all pol
, rcv_shipment_headers rsh
-- MOAC : po_line_locations changed to po_line_locations_all
, po_line_locations_all pll
, po_vendors pov
, hr_locations_all hrl1
, hr_locations_all hrl2
-- MOAC : po_vendor_sites changed 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 uneccessary 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;
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 oeol.header_id ,
oeol.line_id
FROM mtl_material_transactions_temp mmtt,
oe_order_lines_all oeol
WHERE oeol.line_id = mmtt.trx_source_line_id
AND mmtt.transaction_temp_id = p_transaction_id ;
select wlfs.format_id label_format_id, wlf.label_entity_type --FOR SETS
from wms_label_set_formats wlfs , wms_label_formats wlf
where WLFS.SET_ID = p_format_set_id
and wlfs.set_id = wlf.label_format_id
and wlf.label_entity_type = 1
AND WLF.DOCUMENT_ID = 4
UNION --FOR FORMATS
select label_format_id,nvl(wlf.label_entity_type,0)
from wms_label_formats wlf
where wlf.label_format_id = p_format_set_id
and nvl(wlf.label_entity_type,0) = 0--for label formats only validation
AND WLF.DOCUMENT_ID = 4 ;
SELECT NVL(p_organization_id, plpn.organization_id) organization_id
, NVL(p_inventory_item_id, mcce.inventory_item_id) inventory_item_id
, NVL(p_revision, mcce.revision) revision
, NVL(p_lot_number, mcce.lot_number) lot_number
, SUM(NVL(p_qty, mcce.count_quantity_current)) quantity
, NVL(p_uom, mcce.count_uom_current) uom
, NVL(p_cost_group_id, mcce.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_transaction_qty,mcce.count_quantity_current)) secondary_quantity
, mcce.count_uom_current secondary_uom
FROM wms_license_plate_numbers plpn, cst_cost_groups ccg, mtl_item_locations milkfv,
mtl_cycle_count_entries mcce
WHERE cartonization_flag = 0 -- non Cartonization Flow
AND plpn.lpn_id(+) = p_lpn_id
AND milkfv.organization_id(+) = NVL(p_organization_id, plpn.organization_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, mcce.cost_group_id)
AND mcce.cycle_count_entry_id = p_transaction_id
GROUP BY NVL(p_organization_id, plpn.organization_id)
, NVL(p_inventory_item_id, mcce.inventory_item_id)
, NVL(p_revision, mcce.revision)
, NVL(p_lot_number, mcce.lot_number)
, NVL(p_uom, mcce.count_uom_current)
, NVL(p_cost_group_id, mcce.cost_group_id)
, ccg.cost_group
, milkfv.subinventory_code
, milkfv.inventory_location_id
, inv_project.get_locsegs(milkfv.inventory_location_id, milkfv.organization_id)
, mcce.count_uom_current; /* Added for the bug # 5215799 */
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 ;
l_selected_fields INV_LABEL.label_field_variable_tbl_type;
l_selected_fields_count NUMBER;
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;
SELECT DISTINCT header_id, packaging_mode, pack_level
INTO l_header_id, l_packaging_mode, l_max_pack_level
FROM WMS_PACKAGING_HIST
WHERE parent_lpn_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;
SELECT inventory_item_id INTO l_cur_item_id
FROM mtl_material_transactions_temp WHERE lpn_id = l_lpn_id AND transaction_temp_id = p_transaction_id;
select lpn_context into l_lpn_context_id
from wms_license_plate_numbers
where lpn_id = l_lpn_id;
SELECT mmtt.transaction_action_id , mmtt.subinventory_code ,
mmtt.locator_id ,NVL(mmtt.lpn_id,mmtt.content_lpn_id) ,mmtt.organization_id
INTO l_transaction_action_id , l_src_subinventory_code ,
l_src_locator_id ,l_src_lpn_id , l_src_organization_id
FROM mtl_material_transactions_temp mmtt
WHERE transaction_temp_id = p_transaction_id;
SELECT moqd.status_id into l_src_status_id
FROM mtl_onhand_quantities_detail moqd
WHERE inventory_item_id = v_lpn_content.inventory_item_id
AND organization_id = l_src_organization_id
AND subinventory_code = l_src_subinventory_code
AND NVL(lpn_id ,-999) = NVL(l_src_lpn_id,-999)
AND nvl( locator_id, -9999) =nvl( l_src_locator_id, -9999)
AND nvl(lot_number, '@@@@') = nvl(v_lpn_content.lot_number, '@@@@')
AND ROWNUM =1;
select count(1) into l_count
from wms_lpn_contents
where parent_lpn_id = l_lpn_id;
SELECT status_code INTO l_material_status_code
FROM mtl_material_statuses_vl
WHERE status_id = l_return_status_id;
/* insert a record into wms_label_requests entity to
call the label rules engine to get appropriate label
In this call if this happens to be for the label-set, the record
from wms_label_request will be deleted inside following API*/
INV_LABEL.GET_FORMAT_WITH_RULE
( p_document_id =>p_label_type_info.label_type_id,
P_LABEL_FORMAT_ID =>p_label_type_info.manual_format_id,
p_organization_id =>v_lpn_content.organization_id,
p_inventory_item_id =>v_lpn_content.inventory_item_id,
p_subinventory_code =>v_lpn_content.subinventory_code,
p_locator_id =>v_lpn_content.locator_id,
p_lpn_id =>l_lpn_id,
P_LOT_NUMBER =>v_lpn_content.lot_number,
p_package_id =>l_package_id,
P_REVISION =>v_lpn_content.revision,
P_BUSINESS_FLOW_CODE =>p_label_type_info.business_flow_code,
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_PRINTER_NAME =>l_printer, Removed in R12
-- Added for Bug 2748297 Start
P_SUPPLIER_ID => l_vendor_id,
P_SUPPLIER_SITE_ID => l_vendor_site_id,
-- End
p_sales_order_header_id => l_sales_order_header_id,-- bug 5006693
p_sales_order_line_id => l_sales_order_line_id, -- bug 5006693
x_return_status =>l_return_status,
x_label_format_id =>l_label_format_set_id,
x_label_format =>l_label_format,
x_label_request_id =>l_label_request_id);
/* insert a record into wms_label_requests entity */
INV_LABEL.GET_FORMAT_WITH_RULE
( p_document_id =>p_label_type_info.label_type_id,
P_LABEL_FORMAT_ID =>l_label_formats_in_set.label_format_id, --considers manual printer also
p_organization_id =>v_lpn_content.organization_id,
p_inventory_item_id =>v_lpn_content.inventory_item_id,
p_subinventory_code =>v_lpn_content.subinventory_code,
p_locator_id =>v_lpn_content.locator_id,
p_lpn_id =>l_lpn_id,
P_LOT_NUMBER =>v_lpn_content.lot_number,
p_package_id =>l_package_id,
P_REVISION =>v_lpn_content.revision,
P_BUSINESS_FLOW_CODE =>p_label_type_info.business_flow_code,
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', -----Rules ENgine will NOT get called
-- Added for Bug 2748297 Start
P_SUPPLIER_ID => l_vendor_id,
P_SUPPLIER_SITE_ID => l_vendor_site_id, -- End
p_sales_order_header_id => l_sales_order_header_id,-- bug 5006693
p_sales_order_line_id => l_sales_order_line_id, -- bug 5006693
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);
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|| ',' ||l_label_format_id);
trace(' Found selected_fields for format ' || l_label_format ||', num='|| l_selected_fields_count);
/* Loop for each selected fields, find the columns and write into the XML_content*/
FOR i IN 1..l_selected_fields.count LOOP
IF (l_debug = 1) THEN
l_column_name_list := l_column_name_list || ',' ||l_selected_fields(i).column_name;
IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
IF (l_debug = 1) THEN
trace('Custom Labels Trace [INVLAP4B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
trace('Custom Labels Trace [INVLAP4B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
trace('Custom Labels Trace [INVLAP4B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
trace('Custom Labels Trace [INVLAP4B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
trace('Custom Labels Trace [INVLAP4B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
l_sql_stmt := l_selected_fields(i).sql_stmt;
|| l_selected_fields(i).variable_name
|| '">'
|| l_sql_stmt_result
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || INV_LABEL.G_DATE || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || INV_LABEL.G_TIME || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || INV_LABEL.G_USER || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lpn' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_parent_lpn || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'volume' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.volume || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'volume_uom' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.volume_uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'gross_weight' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.gross_weight || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'gross_weight_uom' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.gross_weight_uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'tare_weight' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.tare_weight || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'tare_weight_uom' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.tare_weight_uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'po_num' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
trace('po_num ' || l_rcv_lpn_table(l_lpn_table_index).purchase_order);
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).purchase_order || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_purchase_order || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'organization' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.organization || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || v_lpn_content.subinventory_code || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'locator' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || v_lpn_content.locator || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_description' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_description || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'revision' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || v_lpn_content.revision || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || v_lpn_content.lot_number || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_status' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_number_status || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_expiration_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_expiration_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'quantity' THEN
if ( l_rlpn_ndx <> 0 ) then
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).quantity || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || v_lpn_content.quantity || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'uom' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || v_lpn_content.uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'cost_group' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || v_lpn_content.cost_group || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_hazard_class' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_hazard_class || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute_category' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute_category || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute1' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute1 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute2' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute2 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute3' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute3 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute4' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute4 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute5' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute5 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute6' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute6 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute7' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute7 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute8' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute8 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute9' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute9 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute10' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute10 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute11' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute11 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute12' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute12 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute13' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute13 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute14' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute14 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute15' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.item_attribute15 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute_category' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute_category || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute1' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute1 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute2' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute2 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute3' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute3 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute4' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute4 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute5' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute5 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute6' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute6 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute7' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute7 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute8' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute8 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute9' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute9 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute10' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute10 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute11' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute11 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute12' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute12 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute13' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute13 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute14' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute14 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute15' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.lpn_attribute15 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_attribute_category' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_attribute_category || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute1' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute1 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute2' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute2 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute3' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute3 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute4' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute4 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute5' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute5 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute6' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute6 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute7' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute7 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute8' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute8 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute9' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute9 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute10' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute10 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute11' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute11 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute12' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute12 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute13' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute13 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute14' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute14 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute15' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute15 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute16' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute16 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute17' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute17 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute18' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute18 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute19' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute19 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute20' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_c_attribute20 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute1' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute1 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute2' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute2 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute3' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute3 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute4' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute4 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute5' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute5 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute6' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute6 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute7' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute7 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute8' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute8 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute9' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute9 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute10' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_d_attribute10 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute1' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute1 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute2' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute2 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute3' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute3 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute4' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute4 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute5' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute5 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute6' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute6 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute7' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute7 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute8' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute8 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute9' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute9 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute10' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_n_attribute10 || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_country_of_origin' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_country_of_origin || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_grade_code' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_grade_code || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_origination_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_origination_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_date_code' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_date_code || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_change_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_change_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_age' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_age || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_retest_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_retest_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_maturity_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_maturity_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_item_size' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_item_size || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_color' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_color || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_volume || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume_uom' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_volume_uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_place_of_origin' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_place_of_origin || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_best_by_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_best_by_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_length || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length_uom' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_length_uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_recycled_cont' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_recycled_cont || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_thickness || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness_uom' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_thickness_uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_width || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width_uom' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_width_uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_curl' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_curl || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_vendor' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.lot_vendor || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'receipt_num' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).receipt_num || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_receipt_number || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'po_line_num' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).po_line_num || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_po_line_number || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'quan_ordered' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).quantity_ordered || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_quantity_ordered || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_part_num' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).supplier_part_number || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_supplier_part_number || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_name' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).supplier_name || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_supplier_name || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_site' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).supplier_site || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_supplier_site || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'requestor' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).requestor || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_requestor || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'deliver_to_loc' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).deliver_to_location || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_deliver_to_location || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'loc_id' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).location || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_location_code || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'note_to_receiver' THEN
if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).note_to_receiver || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_note_to_receiver || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'package_id' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_package_id || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_package_id' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_parent_package_id || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'container_item' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_container_item || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'pack_level' THEN
IF(l_lpn_id = p_transaction_id) THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_outermost_pack_level || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || l_pack_level || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'outermost_lpn' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_lpn_info.outermost_lpn || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'gtin' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_gtin || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'gtin_description' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_gtin_desc || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'comments_line' THEN
/* Modified for bug 4080297 -start */
if ( l_rlpn_ndx <> 0 ) then
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).comments || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'packing_slip_line' THEN
/* Modified for bug 4080297 -start */
if ( l_rlpn_ndx <> 0 ) then
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).packing_slip || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_due_date' THEN
/* Modified for bug 4080297 -start */
if ( l_rlpn_ndx <> 0 ) then
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).due_date || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'truck_number' THEN
/* Modified for bug 4080297 -start */
if ( l_rlpn_ndx <> 0 ) then
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).truck_num || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'country_of_origin' THEN
/* Modified for bug 4080297 -start */
if ( l_rlpn_ndx <> 0 ) then
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).country_of_origin || VARIABLE_E;
l_selected_fields(i).variable_name || '">' || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'asn_number' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.asn_num || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.shipment_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'expct_rcpt_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.expected_receipt_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'freight_terms' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.freight_terms || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'freight_carrier' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.freight_carrier || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'num_of_containers' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.num_of_containers || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'bill_of_lading' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.bill_of_lading || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'waybill_airbill_num' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.waybill_airbill_num || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'packing_slip_header' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.packing_slip || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'comments_header' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.comments || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'packaging_code' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.packaging_code || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'special_handling_code' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_rcv_isp_header.special_handling_code || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'cycle_count_name' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_cycle_count_name || variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'epc' THEN
l_content_item_data := l_content_item_data || variable_b ||
l_selected_fields(i).variable_name || '">' || l_epc || variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lot_number' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.parent_lot_number || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'hold_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.hold_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'expiration_action_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.expiration_action_date || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'expiration_action_code' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.expiration_action_code || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'origination_type' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.origination_type || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'secondary_transaction_quantity' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || v_lpn_content.secondary_quantity || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'secondary_uom_code' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || v_lpn_content.secondary_uom || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'supplier_lot_number' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_item_info.supplier_lot_number || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'material_status' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name || '">' || l_material_status_code || variable_e;
SELECT meaning INTO l_label_type_child_lpn.label_type
FROM mfg_lookups WHERE lookup_type = 'WMS_LABEL_TYPE'
AND lookup_code = 5;
l_child_lpn_summary.delete;