The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT uom_code
INTO l_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND unit_of_measure = p_unit_of_measure;
SELECT SECONDARY_UOM_CODE
INTO l_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT meaning
into l_origination_type
FROM mfg_lookups
WHERE lookup_type = 'MTL_LOT_ORIGINATION_TYPE'
AND lookup_code = p_origination_type;
SELECT rti.item_id inventory_item_id
, rti.to_organization_id organization_id
, wlc.lot_number lot_number
, rti.cost_group_id cost_group_id
, pol.project_id project_id
, pol.task_id task_id
-- Added by joabraha bug 3472150
, rsh.receipt_num
--
, NVL(wlc.quantity, rti.quantity) quantity
, -- Bug 2743097, For OSP or onetime expense item, they will not be packed into LPN
-- even in WMS org. So the UOM code need to be retrieved from RTI
NVL(
wlc.uom_code
, get_uom_code(
rti.to_organization_id
, rti.item_id
, rti.unit_of_measure
)
) uom
, rti.item_revision revision
, rti.lpn_id
, pha.segment1
, 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
, rrh.routing_name routing_name
, rti.item_description item_description
, rti.subinventory
, rti.locator_id
, WOJV.WIP_ENTITY_NAME
, WOJV.DESCRIPTION
, RTI.WIP_OPERATION_SEQ_NUM
, rti.DEPARTMENT_CODE
, rti.BOM_RESOURCE_ID
FROM rcv_transactions_interface rti
, wms_lpn_contents wlc
, po_lines_all pol
, po_headers_all pha
, rcv_shipment_headers rsh
, po_line_locations_all pll
, po_vendors pov
, hr_locations_all hrl1
, hr_locations_all hrl2
, po_vendor_sites_all pvs
, per_people_f ppf
, rcv_routing_headers rrh
, wip_osp_jobs_val_v wojv
WHERE wlc.parent_lpn_id(+) = rti.lpn_id
AND wlc.inventory_item_id(+) = rti.item_id -- bug 2372669
AND pol.po_line_id(+) = rti.po_line_id
AND pha.po_header_id(+) = rti.po_header_id
AND rsh.shipment_header_id(+) = rti.shipment_header_id
AND pll.line_location_id(+) = rti.po_line_location_id
AND pov.vendor_id(+) = rti.vendor_id
-- AND pvs.vendor_id(+) = rti.vendor_id This line is uneccessary 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
AND rrh.routing_header_id(+) = rti.routing_header_id
AND wlc.source_header_id(+) = rti.GROUP_ID --- Added for Bug 2699098.
AND rti.interface_transaction_id = p_transaction_id
AND wojv.wip_entity_id = rti.wip_entity_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+); -- for bug#5889715
SELECT rti.item_id inventory_item_id
, rti.to_organization_id organization_id
, wlc.lot_number lot_number
, rti.cost_group_id cost_group_id
, pol.project_id project_id
, pol.task_id task_id
-- Added by joabraha bug 3472150
, rsh.receipt_num
--
, NVL(wlc.quantity, rti.quantity) quantity
, -- Bug 2743097, For OSP or onetime expense item, they will not be packed into LPN
-- even in WMS org. So the UOM code need to be retrieved from RTI
NVL(
wlc.uom_code
, get_uom_code(
rti.to_organization_id
, rti.item_id
, rti.unit_of_measure
)
) uom
, rti.item_revision revision
, rti.lpn_id
, pha.segment1
, 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
, rrh.routing_name routing_name
, rti.item_description item_description
, rti.subinventory
, rti.locator_id
, WOJV.WIP_ENTITY_NAME
, WOJV.DESCRIPTION
, RTI.WIP_OPERATION_SEQ_NUM
, rti.DEPARTMENT_CODE
, rti.BOM_RESOURCE_ID
FROM rcv_transactions_interface rti
, wms_lpn_contents wlc
, po_lines_all pol
, po_headers_all pha
, rcv_shipment_headers rsh
, po_line_locations_all pll
, po_vendors pov
, hr_locations_all hrl1
, hr_locations_all hrl2
, po_vendor_sites_all pvs
, per_people_f ppf
, rcv_routing_headers rrh
, wip_osp_jobs_val_v wojv
WHERE wlc.parent_lpn_id(+) = rti.transfer_lpn_id
AND wlc.inventory_item_id(+) = rti.item_id -- bug 2372669
AND pol.po_line_id(+) = rti.po_line_id
AND pha.po_header_id(+) = rti.po_header_id
AND rsh.shipment_header_id(+) = rti.shipment_header_id
AND pll.line_location_id(+) = rti.po_line_location_id
AND pov.vendor_id(+) = rti.vendor_id
-- AND pvs.vendor_id(+) = rti.vendor_id This line is uneccessary 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
AND rrh.routing_header_id(+) = rti.routing_header_id
AND wlc.source_header_id(+) = rti.GROUP_ID --- Added for Bug 2699098.
AND rti.interface_transaction_id = p_transaction_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+); -- for bug#5889715
SELECT rti.item_id
, rti.item_revision
, mtlt.lot_number
, rti2.organization_id
, rti2.cost_group_id
, rti2.project_id
, rti2.task_id
, SUM(NVL(mtlt.transaction_quantity, rti.quantity)) quantity
, rti2.uom
, rti2.segment1
, rti2.po_line_number
, rti2.quantity_ordered
, rti2.supplier_part_number
, rti2.vendor_id
, rti2.supplier_name
, rti2.vendor_site_id
, rti2.supplier_site
, rti2.requestor
, rti2.deliver_to_location
, rti2.location
, rti2.note_to_receiver
, rti2.routing_name
, rti2.item_description
, rti2.subinventory
, rti2.locator_id
, WOJV.WIP_ENTITY_NAME
, WOJV.DESCRIPTION
, RTI.WIP_OPERATION_SEQ_NUM
, rti.DEPARTMENT_CODE
, rti.BOM_RESOURCE_ID
FROM rcv_transactions_interface rti
, wip_osp_jobs_val_v wojv
, mtl_transaction_lots_temp mtlt
, /***************************************/
(SELECT rti.GROUP_ID
, rti.interface_transaction_id
, rti.to_organization_id organization_id
, rti.cost_group_id cost_group_id
, pol.project_id project_id
, pol.task_id task_id
, inv_label_pvt1.get_uom_code(
rti.to_organization_id
, rti.item_id
, rti.unit_of_measure
) uom
, pha.segment1 segment1
, 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
, hre.full_name requestor
, hrl1.location_code deliver_to_location
, hrl2.location_code location
, pll.note_to_receiver note_to_receiver
, rrh.routing_name routing_name
, rti.item_description item_description
, rti.subinventory subinventory
, rti.locator_id locator_id
FROM rcv_transactions_interface rti
, po_lines_all pol
, po_headers_all pha
, rcv_shipment_headers rsh
, po_line_locations_all pll
, po_vendors pov
, hr_locations hrl1
, hr_locations hrl2
-- MOAC changed po_vendor_sites to po_vendor_sites_all
, po_vendor_sites_all pvs
, hr_employees hre
, rcv_routing_headers rrh
, wip_osp_jobs_val_v wojv
WHERE rti.GROUP_ID IN (SELECT GROUP_ID
FROM rcv_transactions_interface
WHERE interface_transaction_id =
p_transaction_id)
AND pol.po_line_id(+) = rti.po_line_id
AND pha.po_header_id(+) = rti.po_header_id
AND rsh.shipment_header_id(+) = rti.shipment_header_id
AND pll.line_location_id(+) = rti.po_line_location_id
AND pov.vendor_id(+) = rti.vendor_id
-- corrected following line to be pvs.vendor_site_id and not pvs.vendor_id dherring
AND pvs.vendor_site_id(+) = rti.vendor_site_id
-- AND pvs.vendor_id(+) = rti.vendor_id Uneccessary line dherring 8/2/05
AND hre.employee_id(+) = rti.deliver_to_person_id
AND hrl1.location_id(+) = rti.deliver_to_location_id
AND rrh.routing_header_id(+) = rti.routing_header_id
AND hrl2.location_id(+) = rti.location_id) rti2
/***************************************/
WHERE inv_label_pvt1.check_rti_id(
rti2.interface_transaction_id
, mtlt.lot_number
, rti.item_revision
) = 'N'
AND mtlt.transaction_temp_id(+) = rti.interface_transaction_id
AND rti.interface_transaction_id = rti2.interface_transaction_id
AND rti.GROUP_ID = rti2.GROUP_ID
AND rti.wip_entity_id = wojv.wip_entity_id
GROUP BY rti.item_id
, rti.item_revision
, mtlt.lot_number
, rti2.organization_id
, rti2.cost_group_id
, rti2.project_id
, rti2.task_id
, rti2.uom
, rti2.segment1
, rti2.po_line_number
, rti2.quantity_ordered
, rti2.supplier_part_number
/* Bug# 3329195 - Added rti2.vendor_id and rti2.vendor_site_id to the group by clause */
, rti2.vendor_id
, rti2.supplier_name
, rti2.vendor_site_id
, rti2.supplier_site
, rti2.requestor
, rti2.deliver_to_location
, rti2.location
, rti2.note_to_receiver
, rti2.routing_name
, rti2.item_description
, rti2.subinventory
, rti2.locator_id
, WOJV.WIP_ENTITY_NAME
, WOJV.DESCRIPTION
, RTI.WIP_OPERATION_SEQ_NUM
, rti.DEPARTMENT_CODE
, rti.BOM_RESOURCE_ID;
SELECT rsl.item_id inventory_item_id
, rt.organization_id organization_id
, rls.lot_num lot_number -- Reverted to original code as part of Bug#4516067
-- , rsl.cost_group_id cost_group_id /* Modified for the Bug # 4770558 */
, mmt.cost_group_id cost_group_id
--Bug# 3586116 - Get project and task id from rt
, rt.project_id
, rt.task_id
-- , pod.project_id project_id --Commented as part of Bug# 3586116
-- , pod.task_id task_id --Commented as part of Bug# 3586116
-- Added by joabraha bug 3472150
, rsh.receipt_num
, SUM(NVL(rls.quantity, rt.quantity)) quantity -- Reverted to original code as part of Bug#4516067
-- Commented as part of the Bug#4516067 and added the code to fetch secondary_quantity from rls instead of mtln
-- , SUM(NVL(mtln.SECONDARY_TRANSACTION_QUANTITY, rt.SECONDARY_QUANTITY)) secondary_quantity -- fabdi 4373856
, SUM(NVL(rls.SECONDARY_QUANTITY, rt.SECONDARY_QUANTITY)) secondary_quantity -- fabdi 4373856
, (inv_label_pvt1.get_uom_code(
rt.organization_id
, rsl.item_id
, rsl.unit_of_measure
)
) uom
, (inv_label_pvt1.get_uom2_code(
rt.organization_id
, rsl.item_id
)
) secondary_uom -- bug 4373856
, rsl.item_revision revision
, pha.segment1
, rsh.shipment_num
, pol.line_num po_line_number
, pll.quantity quantity_ordered
, rsl.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
, rrh.routing_name routing_name
--Bug 6504959-Reverted fix made which was fetching item desc from msiv.
, rsl.item_description item_description
, rt.subinventory
, rt.locator_id
-- Bug 4516067, to improve performance, query the base table directly
--, WOJV.WIP_ENTITY_NAME wip_entity_name
--, WOJV.DESCRIPTION wip_description
, we.wip_entity_name wip_entity_name -- Added for Bug#4516067
, wdj.description wip_description -- Added for Bug#4516067
, RT.WIP_OPERATION_SEQ_NUM wip_op_seq_num
, rt.DEPARTMENT_CODE wip_department_code
, rt.BOM_RESOURCE_ID wip_bom_resource_id
, wlpn.lpn_context
, wlpn.lpn_id
, rt.routing_header_id routing_header_id --bug 4916450
FROM rcv_transactions rt
, rcv_lots_supply rls -- Reverted to original code as part of Bug#4516067
-- , rcv_lot_transactions rls -- Replaced rcv_lot_transactions by mtl_transaction_lot_numbers to fetch the LOT details as part of Bug# 4186856
-- Added rt2 as part of Bug# 4186856
-- rt2, mtln commented for Bug#4516067 to revert the changes done for Bug#4186856
/* , (select transaction_id
* from rcv_transactions rt_deliver
* where rt_deliver.group_id = p_transaction_id
* and rt_deliver.transaction_type = 'DELIVER') rt2
* , mtl_transaction_lot_numbers mtln -- Added as part of Bug# 4186856
*/
, rcv_shipment_lines rsl
, po_lines_all pol
-- , po_distributions_all pod --Commented as part of Bug# 3586116
, po_headers_all pha
, rcv_shipment_headers rsh
, po_line_locations_all pll
, po_vendors pov
, hr_locations_all hrl1
, hr_locations_all hrl2
, po_vendor_sites_all pvs
, per_people_f ppf
, rcv_routing_headers rrh
-- Bug 4516067, to improve performance, query the base table directly
--, wip_osp_jobs_val_v wojv
, wip_entities we -- Added for Bug#4516067
, wip_discrete_jobs wdj -- Added for Bug#4516067
, wms_license_plate_numbers wlpn -- Bug 3836623
, (SELECT cost_group_id, rcv_transaction_id
FROM mtl_material_transactions mmt1
WHERE mmt1.rcv_transaction_id = p_transaction_id
AND nvl(mmt1.logical_transaction, -999) <> 1) mmt -- Modified for bug# 5515979
--, mtl_material_transactions mmt -- Added for the Bug # 4770558
WHERE rls.transaction_id(+) = rt.transaction_id -- Reverted to original code as part of Bug#4516067
--mtln.product_transaction_id(+) = rt.transaction_id -- Commented as part of Bug#4186856
/* Reverted to original code as part of Bug#4516067
* mtln.product_code = 'RCV' -- Added as part of Bug#4186856
* AND mtln.product_transaction_id = rt2.transaction_id -- Added as part of Bug#4186856
* AND mtln.inventory_item_id = pol.item_id -- Added as part of Bug#4186856
*/
AND pol.po_line_id(+) = rt.po_line_id
AND pha.po_header_id(+) = rt.po_header_id
AND rsh.shipment_header_id(+) = rt.shipment_header_id
AND pll.line_location_id(+) = rt.po_line_location_id
-- AND pod.po_distribution_id(+) = rt.po_distribution_id --Commented as part of Bug# 3586116
AND pov.vendor_id(+) = rt.vendor_id
-- AND pvs.vendor_id(+) = rt.vendor_id Uneccessary line dherring 8/2/05
AND pvs.vendor_site_id(+) = rt.vendor_site_id
AND ppf.person_id(+) = rt.deliver_to_person_id
AND hrl1.location_id(+) = rt.deliver_to_location_id
AND hrl2.location_id(+) = rt.location_id
AND rrh.routing_header_id(+) = rt.routing_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.GROUP_ID = p_transaction_id
-- Bug 4516067, to improve performance, query the base table directly
--AND rt.wip_entity_id = wojv.wip_entity_id (+)
AND rt.wip_entity_id = we.wip_entity_id (+)
AND rt.wip_entity_id = wdj.wip_entity_id (+)
AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
AND p_label_type_info.business_flow_code = 2) -- label flow code 'Inspect'
OR (rt.transaction_type = 'RECEIVE'
AND p_label_type_info.business_flow_code = 1 -- label flow code 'Receive'
-- Commented following condition for bug 4142656
-- Reverted back the changes done for Bug#4142656 as part of Bug#4516067
AND rt.routing_header_id <> 3
)
)
-- Bug 3836623
-- Add check for LPN context
-- When cross docking happens, label printing are called for both cross docking and putaway
-- To prevent duplicate labels
-- For putaway business flow, only print if LPN Context is not Picked (11)
AND wlpn.lpn_id(+) = rt.lpn_id
AND ((rt.lpn_id IS NULL) OR
(p_label_type_info.business_flow_code <> 4) OR
(p_label_type_info.business_flow_code = 4 AND
wlpn.lpn_context <> 11))
AND rt.transaction_id = mmt.rcv_transaction_id(+) /* Added for the Bug # 4770558 */
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+) -- for bug#5889715
-- The outer join has been added in the above condition for solving the bug # 4863161
GROUP BY rsl.item_id
, rt.organization_id
, rls.lot_num -- Modified as part of Bug# 4516067
--, rsl.cost_group_id /* Modified for the Bug # 4770558 */
, mmt.cost_group_id
, rt.project_id
, rt.task_id
-- , pod.project_id --Commented as part of Bug# 3586116
-- , pod.task_id --Commented as part of Bug# 3586116
-- Added by joabraha bug 3472150
, rsh.receipt_num
--
, inv_label_pvt1.get_uom_code(
rt.organization_id
, rsl.item_id
, rsl.unit_of_measure
)
, (inv_label_pvt1.get_uom2_code(
rt.organization_id
, rsl.item_id
) -- bug 4373856
)
, (inv_label_pvt1.get_uom2_code(
rt.organization_id
, rsl.item_id
)
) -- bug 4373856
, rsl.item_revision
, pha.segment1
, rsh.shipment_num
, pol.line_num
, pll.quantity
, rsl.vendor_item_num
, pov.vendor_id
, pov.vendor_name
, pvs.vendor_site_id
, pvs.vendor_site_code
, ppf.full_name
, hrl1.location_code
, hrl2.location_code
, pll.note_to_receiver
, rrh.routing_name
--Bug 6504959-Reverted fix made which was fetching item desc from msiv.
, rsl.item_description
, rt.subinventory
, rt.locator_id
-- Bug 4516067, to improve performance, query the base table directly
--, WOJV.WIP_ENTITY_NAME
--, WOJV.DESCRIPTION
, we.wip_entity_name
, wdj.description
, RT.WIP_OPERATION_SEQ_NUM
, rt.DEPARTMENT_CODE
, rt.BOM_RESOURCE_ID
, wlpn.lpn_context
, wlpn.lpn_id
, rt.routing_header_id --bug 4916450
UNION ALL -- Removed the cursor Added as part of 4186856 to segregate the Lot Controlled items and non Lot Controlled items.
-- Added a new cursor to pick the records for label during Direct Routing for Bug# 4516067
SELECT rsl.item_id inventory_item_id
, rt.organization_id organization_id
, mtln.lot_number lot_number
-- , rsl.cost_group_id cost_group_id /* Modified for the Bug # 4770558 */
, mmt.cost_group_id cost_group_id
--Bug# 3586116 - Get project and task id from rt
, rt.project_id
, rt.task_id
-- , pod.project_id project_id
--, pod.task_id task_id
-- Added by joabraha bug 3472150
, rsh.receipt_num
--
, SUM(NVL(mtln.transaction_quantity, rt.quantity)) quantity
, SUM(NVL(mtln.SECONDARY_TRANSACTION_QUANTITY, rt.SECONDARY_QUANTITY)) secondary_quantity -- fabdi 4373856
, (inv_label_pvt1.get_uom_code(
rt.organization_id
, rsl.item_id
, rsl.unit_of_measure
)
) uom
, (inv_label_pvt1.get_uom2_code(
rt.organization_id
, rsl.item_id
)
) secondary_uom -- bug 4373856
, rsl.item_revision revision
, pha.segment1
, rsh.shipment_num
, pol.line_num po_line_number
, pll.quantity quantity_ordered
, rsl.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
, rrh.routing_name routing_name
--Bug 6504959-Reverted fix made which was fetching item desc from msiv.
, rsl.item_description item_description
, rt.subinventory
, rt.locator_id
-- Bug 4516067, to improve performance, query the base table directly
--, WOJV.WIP_ENTITY_NAME wip_entity_name
--, WOJV.DESCRIPTION wip_description
, we.wip_entity_name wip_entity_name
, wdj.description wip_description
, RT.WIP_OPERATION_SEQ_NUM
, rt.DEPARTMENT_CODE
, rt.BOM_RESOURCE_ID
, wlpn.lpn_context
, wlpn.lpn_id
, rt.routing_header_id routing_header_id --bug 4916450
FROM rcv_transactions rt
, mtl_transaction_lot_numbers mtln
, rcv_shipment_lines rsl
, po_lines_all pol
-- , po_distributions_all pod --Commented as part of Bug# 3586116
, po_headers_all pha
, rcv_shipment_headers rsh
, po_line_locations pll
, po_vendors pov
, hr_locations_all hrl1
, hr_locations_all hrl2
, po_vendor_sites_all pvs
, per_people_f ppf
, rcv_routing_headers rrh
-- Bug 4516067, to improve performance, query the base table directly
--, wip_osp_jobs_val_v wojv
, wip_entities we
, wip_discrete_jobs wdj
, wms_license_plate_numbers wlpn -- Bug 3836623
, (SELECT cost_group_id, rcv_transaction_id
FROM mtl_material_transactions mmt1
WHERE mmt1.rcv_transaction_id = p_transaction_id
AND nvl(mmt1.logical_transaction, -999) <> 1) mmt -- Modified for bug# 5515979
--, mtl_material_transactions mmt -- Added for the Bug # 4770558
WHERE mtln.product_transaction_id(+) = rt.transaction_id
AND mtln.product_code(+) = 'RCV'
AND pol.po_line_id(+) = rt.po_line_id
AND pha.po_header_id(+) = rt.po_header_id
AND rsh.shipment_header_id(+) = rt.shipment_header_id
AND pll.line_location_id(+) = rt.po_line_location_id
-- AND pod.po_distribution_id(+) = rt.po_distribution_id --Commented as part of Bug# 3586116
AND pov.vendor_id(+) = rt.vendor_id
-- AND pvs.vendor_id(+) = rt.vendor_id
AND pvs.vendor_site_id(+) = rt.vendor_site_id
AND ppf.person_id(+) = rt.deliver_to_person_id
AND hrl1.location_id(+) = rt.deliver_to_location_id
AND hrl2.location_id(+) = rt.location_id
AND rrh.routing_header_id(+) = rt.routing_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.GROUP_ID = p_transaction_id
AND rt.transaction_type = 'DELIVER'
AND rt.routing_header_id = 3 -- Added as part of Bug# 4516067
AND p_label_type_info.business_flow_code in (1) -- Only pick for label flow code of 'deliver' or 'putaway'
-- Bug 4516067, to improve performance, query the base table directly
--AND rt.wip_entity_id = wojv.wip_entity_id (+)
AND rt.wip_entity_id = we.wip_entity_id (+)
AND rt.wip_entity_id = wdj.wip_entity_id (+)
-- Bug 3836623
-- Add check for LPN context
-- When cross docking happens, label printing are called for both cross docking and putaway
-- To prevent duplicate labels
-- For putaway business flow, only print if LPN Context is not Picked (11)
AND wlpn.lpn_id(+) = rt.lpn_id
AND ((rt.lpn_id IS NULL) OR
(p_label_type_info.business_flow_code <> 4) OR
(p_label_type_info.business_flow_code = 4 AND
wlpn.lpn_context <> 11))
AND rt.transaction_id = mmt.rcv_transaction_id(+) /* Added for the Bug # 4770558 */
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+) -- for bug#5889715
-- The outer join has been added in the above condition for solving the bug # 4863161
GROUP BY rsl.item_id
, rt.organization_id
, mtln.lot_number
--, rsl.cost_group_id /* Modified for the Bug # 4770558 */
, mmt.cost_group_id
, rt.project_id
, rt.task_id
-- , pod.project_id --Commented as part of Bug# 3586116
--, pod.task_id --Commented as part of Bug# 3586116
-- Added by joabraha bug 3472150
, rsh.receipt_num
--
, (inv_label_pvt1.get_uom_code(
rt.organization_id
, rsl.item_id
, rsl.unit_of_measure
)
)
, (inv_label_pvt1.get_uom2_code(
rt.organization_id
, rsl.item_id
)
) -- bug 4373856
, rsl.item_revision
, pha.segment1
, rsh.shipment_num
, pol.line_num
, pll.quantity
, rsl.vendor_item_num
, pov.vendor_id
, pov.vendor_name
, pvs.vendor_site_id
, pvs.vendor_site_code
, ppf.full_name
, hrl1.location_code
, hrl2.location_code
, pll.note_to_receiver
, rrh.routing_name
--Bug 6504959-Reverted fix made which was fetching item desc from msiv.
, rsl.item_description
, rt.subinventory
, rt.locator_id
-- Bug 4516067, to improve performance, query the base table directly
--, WOJV.WIP_ENTITY_NAME
--, WOJV.DESCRIPTION
, we.wip_entity_name
, wdj.description
, RT.WIP_OPERATION_SEQ_NUM
, rt.DEPARTMENT_CODE
, rt.BOM_RESOURCE_ID
, wlpn.lpn_context
, wlpn.lpn_id
, rt.routing_header_id; --bug 4916450
SELECT rsl.item_id inventory_item_id
, rt.organization_id organization_id
, mtln.lot_number lot_number
, rsl.cost_group_id cost_group_id
--Bug# 3586116 - Get project and task id from rt
, rt.project_id
, rt.task_id
-- , pod.project_id project_id
--, pod.task_id task_id
-- Added by joabraha bug 3472150
, rsh.receipt_num
--
, SUM(NVL(mtln.transaction_quantity, rt.quantity)) quantity
, SUM(NVL(mtln.SECONDARY_TRANSACTION_QUANTITY, rt.SECONDARY_QUANTITY)) secondary_quantity
, (inv_label_pvt1.get_uom_code(
rt.organization_id
, rsl.item_id
, rsl.unit_of_measure
)
) uom
, (inv_label_pvt1.get_uom2_code(
rt.organization_id
, rsl.item_id
)
) secondary_uom -- bug 4373856
, rsl.item_revision revision
, pha.segment1
, rsh.shipment_num
, pol.line_num po_line_number
, pll.quantity quantity_ordered
, rsl.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
, rrh.routing_name routing_name
, rsl.item_description item_description
, rt.subinventory
, rt.locator_id
-- Bug 4516067, to improve performance, query the base table directly
--, WOJV.WIP_ENTITY_NAME wip_entity_name
--, WOJV.DESCRIPTION wip_description
, we.wip_entity_name wip_entity_name
, wdj.description wip_description
, RT.WIP_OPERATION_SEQ_NUM
, rt.DEPARTMENT_CODE
, rt.BOM_RESOURCE_ID
, wlpn.lpn_context
, wlpn.lpn_id
, rt.routing_header_id --bug 4916450
FROM rcv_transactions rt
, mtl_transaction_lot_numbers mtln
, rcv_shipment_lines rsl
, po_lines_all pol
-- , po_distributions_all pod --Commented as part of Bug# 3586116
, po_headers_all pha
, rcv_shipment_headers rsh
, po_line_locations pll
, po_vendors pov
, hr_locations_all hrl1
, hr_locations_all hrl2
, po_vendor_sites_all pvs
, per_people_f ppf
, rcv_routing_headers rrh
-- Bug 4516067, to improve performance, query the base table directly
--, wip_osp_jobs_val_v wojv
, wip_entities we
, wip_discrete_jobs wdj
, wms_license_plate_numbers wlpn -- Bug 3836623
WHERE mtln.product_transaction_id(+) = rt.transaction_id
AND mtln.product_code(+) = 'RCV'
AND pol.po_line_id(+) = rt.po_line_id
AND pha.po_header_id(+) = rt.po_header_id
AND rsh.shipment_header_id(+) = rt.shipment_header_id
AND pll.line_location_id(+) = rt.po_line_location_id
-- AND pod.po_distribution_id(+) = rt.po_distribution_id --Commented as part of Bug# 3586116
AND pov.vendor_id(+) = rt.vendor_id
-- AND pvs.vendor_id(+) = rt.vendor_id uneccessary line dherring 8/2/05
AND pvs.vendor_site_id(+) = rt.vendor_site_id
AND ppf.person_id(+) = rt.deliver_to_person_id
AND hrl1.location_id(+) = rt.deliver_to_location_id
AND hrl2.location_id(+) = rt.location_id
AND rrh.routing_header_id(+) = rt.routing_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.GROUP_ID = p_transaction_id
AND rt.transaction_type = 'DELIVER'
-- Bug 4516067, because created this new cursor for putaway and deliver
-- no need to restrict business flow code here
-- AND p_label_type_info.business_flow_code in (3,4) -- Only pick for label flow code of 'deliver' or 'putaway'
-- Bug 4516067, to improve performance, query the base table directly
-- AND rt.wip_entity_id = wojv.wip_entity_id (+)
AND rt.wip_entity_id = we.wip_entity_id (+)
AND rt.wip_entity_id = wdj.wip_entity_id (+)
-- Bug 3836623
-- Add check for LPN context
-- When cross docking happens, label printing are called for both cross docking and putaway
-- To prevent duplicate labels
-- For putaway business flow, only print if LPN Context is not Picked (11)
AND wlpn.lpn_id(+) = rt.lpn_id
AND ((rt.lpn_id IS NULL) OR
(p_label_type_info.business_flow_code <> 4) OR
(p_label_type_info.business_flow_code = 4 AND
wlpn.lpn_context <> 11))
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+) -- for bug#5889715
GROUP BY rsl.item_id
, rt.organization_id
, mtln.lot_number
, rsl.cost_group_id
, rt.project_id
, rt.task_id
-- , pod.project_id --Commented as part of Bug# 3586116
--, pod.task_id --Commented as part of Bug# 3586116
-- Added by joabraha bug 3472150
, rsh.receipt_num
--
, (inv_label_pvt1.get_uom_code(
rt.organization_id
, rsl.item_id
, rsl.unit_of_measure
)
)
, (inv_label_pvt1.get_uom2_code(
rt.organization_id
, rsl.item_id
)
) -- bug 4373856
, rsl.item_revision
, pha.segment1
, rsh.shipment_num
, pol.line_num
, pll.quantity
, rsl.vendor_item_num
, pov.vendor_id
, pov.vendor_name
, pvs.vendor_site_id
, pvs.vendor_site_code
, ppf.full_name
, hrl1.location_code
, hrl2.location_code
, pll.note_to_receiver
, rrh.routing_name
, rsl.item_description
, rt.subinventory
, rt.locator_id
-- Bug 4516067, to improve performance, query the base table directly
--, WOJV.WIP_ENTITY_NAME
--, WOJV.DESCRIPTION
, we.wip_entity_name
, wdj.description
, RT.WIP_OPERATION_SEQ_NUM
, rt.DEPARTMENT_CODE
, rt.BOM_RESOURCE_ID
, wlpn.lpn_context
, wlpn.lpn_id
, rt.routing_header_id; --bug 4916450
SELECT br.resource_code
,bd.department_code
FROM bom_resources br
,bom_department_resources bdr
,bom_departments bd
WHERE br.resource_id = p_resource_id
AND bdr.resource_id = p_resource_id
AND bd.department_id = bdr.department_id
GROUP BY br.resource_code
,bd.department_code;
SELECT mmtt.inventory_item_id
, mmtt.organization_id
, mtlt.lot_number
, mmtt.cost_group_id
, mmtt.project_id
, mmtt.task_id
, ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
, mmtt.transaction_uom
, ABS(NVL(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)) secondary_quantity -- invconv changes
, mmtt.secondary_uom_code -- invconv changes
, mmtt.revision
, -- Added for Bug 2308273
mtlt.lot_attribute_category
, mtlt.c_attribute1
, mtlt.c_attribute2
, mtlt.c_attribute3
, mtlt.c_attribute4
, mtlt.c_attribute5
, mtlt.c_attribute6
, mtlt.c_attribute7
, mtlt.c_attribute8
, mtlt.c_attribute9
, mtlt.c_attribute10
, mtlt.c_attribute11
, mtlt.c_attribute12
, mtlt.c_attribute13
, mtlt.c_attribute14
, mtlt.c_attribute15
, mtlt.c_attribute16
, mtlt.c_attribute17
, mtlt.c_attribute18
, mtlt.c_attribute19
, mtlt.c_attribute20
, mtlt.d_attribute1
, mtlt.d_attribute2
, mtlt.d_attribute3
, mtlt.d_attribute4
, mtlt.d_attribute5
, mtlt.d_attribute6
, mtlt.d_attribute7
, mtlt.d_attribute8
, mtlt.d_attribute9
, mtlt.d_attribute10
, mtlt.n_attribute1
, mtlt.n_attribute2
, mtlt.n_attribute3
, mtlt.n_attribute4
, mtlt.n_attribute5
, mtlt.n_attribute6
, mtlt.n_attribute7
, mtlt.n_attribute8
, mtlt.n_attribute9
, mtlt.n_attribute10
, mtlt.territory_code
, mtlt.grade_code
, mtlt.origination_date
, mtlt.date_code
, mtlt.change_date
, mtlt.age
, mtlt.retest_date
, mtlt.maturity_date
, mtlt.item_size
, mtlt.color
, mtlt.volume
, mtlt.volume_uom
, mtlt.place_of_origin
, mtlt.best_by_date
, mtlt.LENGTH
, mtlt.length_uom
, mtlt.recycled_content
, mtlt.thickness
, mtlt.thickness_uom
, mtlt.width
, mtlt.width_uom
, mtlt.curl_wrinkle_fold
, mtlt.vendor_name
-- End Bug 2308273
, mmtt.subinventory_code
, mmtt.locator_id
, we.wip_entity_name -- Fix For Bug: 4907062
, we.description -- Fix For Bug: 4907062
, mtlt.parent_lot_number -- added for inconv fabdi start
, mtlt.expiration_action_date
, mtlt.origination_type
, mtlt.hold_date
, mtlt.expiration_action_code
, mtlt.supplier_lot_number -- invconv end
FROM mtl_material_transactions_temp mmtt
,mtl_transaction_lots_temp mtlt
,wip_entities we -- Fix For Bug: 4907062
WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND mmtt.transaction_temp_id = p_transaction_id
AND we.wip_entity_id(+) = mmtt.transaction_source_id; -- Fix For Bug: 4907062
SELECT mmtt.inventory_item_id inventory_item_id
, mmtt.organization_id organization_id
, mtlt.lot_number lot_number
, mmtt.cost_group_id cost_group_id
, mmtt.transfer_cost_group_id xfr_cost_group_id /* Added for the bug # 4686024 */
, mmtt.project_id project_id
, mmtt.task_id task_id
, ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
, mmtt.transaction_uom uom
, mmtt.revision revision
, mmtt.subinventory_code
, mmtt.transfer_subinventory
, mmtt.locator_id
, mmtt.transfer_to_location
, mmtt.secondary_uom_code -- added for invconv
, ABS(NVL(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)) -- added for invconv
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
AND mmtt.content_lpn_id IS NULL
UNION ALL
SELECT wlc.inventory_item_id inventory_item_id
, wlc.organization_id organization_id
, wlc.lot_number lot_number
, wlc.cost_group_id cost_group_id
, mmtt.transfer_cost_group_id xfr_cost_group_id /* Added for the bug # 4686024 */
, mmtt.project_id project_id
, mmtt.task_id task_id
, wlc.quantity quantity
, wlc.uom_code uom
, wlc.revision revision
, mmtt.subinventory_code
, mmtt.transfer_subinventory
, mmtt.locator_id
, mmtt.transfer_to_location
, wlc.secondary_uom_code -- added for invconv
, wlc.secondary_quantity -- added for invconv
FROM wms_lpn_contents wlc, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_id
AND mmtt.content_lpn_id IS NOT NULL
AND mmtt.content_lpn_id = wlc.parent_lpn_id;
SELECT mmtt.inventory_item_id inventory_item_id
, mmtt.organization_id organization_id
, mtlt.lot_number lot_number
, mmtt.cost_group_id cost_group_id
, mmtt.project_id project_id
, mmtt.task_id task_id
, ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
, ABS(NVL(mtlt.SECONDARY_QUANTITY, mmtt.SECONDARY_TRANSACTION_QUANTITY)) secondary_quantity -- fabdi bug 4387144
, mmtt.transaction_uom uom
, mmtt.SECONDARY_UOM_CODE secondary_uom -- fabdi bug 4387144
, mmtt.revision revision
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
FROM mtl_material_transactions_temp mmtt
, mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt_orgin
WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND mmtt.transaction_header_id = mmtt_orgin.transaction_header_id
AND mmtt.transaction_temp_id <> mmtt_orgin.transaction_temp_id
AND mmtt_orgin.content_lpn_id IS NOT NULL
AND mmtt_orgin.transaction_temp_id = p_transaction_id
UNION ALL
SELECT mmtt.inventory_item_id inventory_item_id
, mmtt.organization_id organization_id
, mtlt.lot_number lot_number
, mmtt.cost_group_id cost_group_id
, mmtt.project_id project_id
, mmtt.task_id task_id
, ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
, ABS(NVL(mtlt.SECONDARY_QUANTITY , mmtt.SECONDARY_TRANSACTION_QUANTITY)) secondary_quantity -- fabdi bug 4387144
, mmtt.transaction_uom uom
, mmtt.SECONDARY_UOM_CODE secondary_uom -- fabdi bug 4387144
, mmtt.revision revision
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND mmtt.content_lpn_id IS NULL
AND mmtt.transaction_temp_id = p_transaction_id;
SELECT mmtt.inventory_item_id inventory_item_id
, mmtt.organization_id organization_id
, mtlt.lot_number lot_number
, mmtt.cost_group_id cost_group_id
, mmtt.project_id project_id
, mmtt.task_id task_id
, ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
, mmtt.transaction_uom uom
, mmtt.revision revision
, mmtt.subinventory_code
, mmtt.transfer_subinventory
, mmtt.locator_id
, mmtt.transfer_to_location
, ABS(NVL(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)) secondary_quantity -- invconv 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.transfer_lpn_id IS NULL
AND mmtt.content_lpn_id IS NULL
AND mmtt.transaction_temp_id = p_transaction_id
UNION ALL
-- This part of the cursor returns the content_lpn_id unpacked from an LPN.
SELECT wlc.inventory_item_id inventory_item_id
, wlc.organization_id organization_id
, wlc.lot_number lot_number
, wlc.cost_group_id cost_group_id
, mmtt.project_id project_id
, mmtt.task_id task_id
, wlc.quantity quantity
, wlc.uom_code uom
, wlc.revision revision
, mmtt.subinventory_code
, mmtt.transfer_subinventory
, mmtt.locator_id
, mmtt.transfer_to_location
, wlc.secondary_quantity -- added for invconv
, wlc.secondary_uom_code -- added for invconv
FROM wms_lpn_contents wlc, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_id
AND mmtt.content_lpn_id = wlc.parent_lpn_id
UNION ALL
-- This part of the cursor is for 2 cases. Items unpacked from an LPN and packed into another LPN AND
-- for loose Items packed into an existing or loose LPN.
SELECT wlc.inventory_item_id inventory_item_id
, wlc.organization_id organization_id
, wlc.lot_number lot_number
, wlc.cost_group_id cost_group_id
, mmtt.project_id project_id
, mmtt.task_id task_id
, wlc.quantity quantity
, wlc.uom_code uom
, wlc.revision revision
, mmtt.subinventory_code
, mmtt.transfer_subinventory
, mmtt.locator_id
, mmtt.transfer_to_location
, wlc.secondary_quantity -- added for invconv
, wlc.secondary_uom_code -- added for invconv
FROM wms_lpn_contents wlc, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_id
AND mmtt.transfer_lpn_id = wlc.parent_lpn_id
UNION ALL
-- This part of the cursor is for all cases except loose items being packed into an existing/new LPN.
SELECT wlc.inventory_item_id inventory_item_id
, wlc.organization_id organization_id
, wlc.lot_number lot_number
, wlc.cost_group_id cost_group_id
, mmtt.project_id project_id
, mmtt.task_id task_id
, wlc.quantity quantity
, wlc.uom_code uom
, wlc.revision revision
, mmtt.subinventory_code
, mmtt.transfer_subinventory
, mmtt.locator_id
, mmtt.transfer_to_location
, wlc.secondary_quantity -- added for invconv
, wlc.secondary_uom_code -- added for invconv
FROM wms_lpn_contents wlc, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_id
AND mmtt.lpn_id = wlc.parent_lpn_id;
SELECT wlc.organization_id
, wlc.inventory_item_id
, wlc.revision
, wlc.lot_number
, SUM(wlc.quantity)
FROM wms_lpn_contents wlc, WMS_LICENSE_PLATE_NUMBERS wlpn
WHERE wlpn.OUTERMOST_LPN_ID = p_transaction_id
and wlc.parent_lpn_id = wlpn.lpn_id
GROUP BY wlc.organization_id
, wlc.inventory_item_id
, wlc.revision
, wlc.lot_number
/* Union Clause added to fetch the details from mmtt for pick release transactions for cartonization flow
as a part of Bug#4305501*/
UNION
SELECT mmtt.organization_id
, mmtt.inventory_item_id
, mmtt.revision
, mtlt.lot_number
, SUM(mmtt.primary_quantity) quantity
FROM mtl_material_transactions_temp mmtt
, mtl_transaction_lots_temp mtlt
WHERE mmtt.cartonization_id = p_transaction_id
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
GROUP BY mmtt.organization_id
, mmtt.inventory_item_id
, mmtt.revision
, mtlt.lot_number;
SELECT wdd1.inventory_item_id inventory_item_id
, wdd1.organization_id organization_id
, wdd1.lot_number lot_number
, NVL(wlpn.cost_group_id, 0) cost_group_id
, NVL(wdd1.project_id, 0) project_id
, NVL(wdd1.task_id, 0) task_id
, wdd1.requested_quantity quantity
, wdd1.requested_quantity_uom uom
, wdd1.revision revision
, wdd1.subinventory
, wdd1.locator_id
FROM wsh_delivery_details wdd1
, wsh_delivery_details wdd2
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers wlpn
WHERE wdd1.delivery_detail_id = wda.delivery_detail_id
AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id
AND wlpn.lpn_id(+) = wdd2.lpn_id
AND wdd2.delivery_detail_id = p_transaction_id;
SELECT wdd1.inventory_item_id inventory_item_id
, wdd1.organization_id organization_id
, wdd1.lot_number lot_number
, NVL(wlpn.cost_group_id, 0) cost_group_id
, NVL(wdd1.project_id, 0) project_id
, NVL(wdd1.task_id, 0) task_id
-- Bug - 4193950, requested_quantity is replaced with shipped_quantity.
, wdd1.shipped_quantity quantity --, wdd1.requested_quantity quantity
, wdd1.requested_quantity_uom uom
, wdd1.revision revision
, wdd1.subinventory
, wdd1.locator_id
FROM wsh_delivery_details wdd1
, wsh_delivery_assignments_v wda
, wsh_new_deliveries wnd
, wms_license_plate_numbers wlpn
, wsh_delivery_details wdd2
WHERE wda.delivery_id = wnd.delivery_id
AND wdd1.delivery_detail_id = wda.delivery_detail_id
AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd1.inventory_item_id IS NOT NULL
AND wlpn.lpn_id(+) = wdd2.lpn_id
AND wnd.delivery_id = p_transaction_id;
SELECT mmtt.inventory_item_id
, mmtt.organization_id
, mtlt.lot_number
, mmtt.cost_group_id
, mmtt.project_id
, mmtt.task_id
, ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity))
, mmtt.transaction_uom
, mmtt.revision
, mmtt.subinventory_code
, mmtt.locator_id
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 mmtt.inventory_item_id
, mmtt.organization_id
, mtlt.lot_number
, mmtt.cost_group_id
, mmtt.project_id
, mmtt.task_id
, ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity))
, mmtt.transaction_uom
, mmtt.revision
, mmtt.subinventory_code
, mmtt.locator_id
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 mmtt.inventory_item_id inventory_item_id
, mmtt.organization_id organization_id
, mtlt.lot_number lot_number
, mmtt.cost_group_id cost_group_id
, mmtt.project_id project_id
, mmtt.task_id task_id
, NVL(mtlt.transaction_quantity, mmtt.transaction_quantity) quantity
, mmtt.transaction_uom uom
, mmtt.revision revision
, mmtt.subinventory_code
, mmtt.locator_id
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mmtt.transaction_temp_id = p_transaction_id;
SELECT mti.inventory_item_id inventory_item_id
, mti.organization_id organization_id
, mtil.lot_number lot_number
, mti.cost_group_id cost_group_id
, mti.project_id project_id
, mti.task_id task_id
, NVL(mtil.transaction_quantity, mti.transaction_quantity) quantity
, mti.transaction_uom uom
, mti.revision revision
, mti.subinventory_code
, mti.locator_id
FROM mtl_transactions_interface mti, mtl_transaction_lots_interface mtil
WHERE mti.transaction_interface_id = mtil.transaction_interface_id(+)
AND mti.transaction_interface_id = p_transaction_id;
SELECT mtrl.inventory_item_id inventory_id
, mtrl.organization_id organization_id
, mtrl.lot_number lot_number
, mtrl.from_cost_group_id cost_group_id
, mtrl.project_id project_id
, mtrl.task_id task_id
, mtrl.quantity quantity
, mtrl.uom_code uom
, mtrl.revision revision
, mtrl.from_subinventory_code
, mtrl.from_locator_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = p_transaction_id;
SELECT to_organization_id
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_transaction_id;
SELECT organization_id
FROM rcv_transactions rt
WHERE rt.group_id = p_transaction_id
and ( (p_label_type_info.business_flow_code = 1 AND rt.transaction_type = 'RECEIVE')
OR (p_label_type_info.business_flow_code = 2 AND rt.transaction_type in ('ACCEPT', 'REJECT') )
OR (p_label_type_info.business_flow_code = 3 AND rt.transaction_type = 'DELIVER')
OR (p_label_type_info.business_flow_code = 4 AND rt.transaction_type = 'DELIVER')
);
SELECT cost_group
FROM cst_cost_groups
WHERE cost_group_id = l_cost_group_id; */
SELECT cost_group
FROM cst_cost_groups
WHERE cost_group_id = p_cost_group_id;
SELECT project_name, project_number
FROM pjm_projects_mtll_v --pa_projects
WHERE project_id = l_project_id;
SELECT task_name, task_number
FROM pa_tasks
WHERE task_id = l_task_id;
SELECT msik.concatenated_segments item
, NVL(msik.description, l_item_description) item_description
, mp.organization_code ORGANIZATION
, l_revision revision
, l_quantity quantity
, l_uom uom
, mln.lot_number lot_number
, NVL(l_parent_lot_number , mln.parent_lot_number) parent_lot_number -- invconv changes
, TO_CHAR(NVL(l_expiration_action_date, mln.expiration_action_date), g_date_format_mask) expiration_action_date
, NVL(l_expiration_action_code , mln.expiration_action_code) expiration_action_code
, l_secondary_transaction_qty secondary_quantity
, l_secondary_uom_code secondary_uom
, TO_CHAR(NVL(l_hold_date, mln.hold_date), g_date_format_mask) hold_date
, NVL(l_origination_type , mln.origination_type) origination_type
, NVL(l_supplier_lot_number, mln.supplier_lot_number) supplier_lot_number -- invconv changes
, mmsvl.status_code lot_status
, TO_CHAR(mln.expiration_date, g_date_format_mask) lot_expiration_date
, -- Added for Bug 2795525,
NVL(l_attribute_category, mln.attribute_category) lot_attribute_category
, NVL(l_c_attribute1, mln.c_attribute1) lot_c_attribute1
, NVL(l_c_attribute2, mln.c_attribute2) lot_c_attribute2
, NVL(l_c_attribute3, mln.c_attribute3) lot_c_attribute3
, NVL(l_c_attribute4, mln.c_attribute4) lot_c_attribute4
, NVL(l_c_attribute5, mln.c_attribute5) lot_c_attribute5
, NVL(l_c_attribute6, mln.c_attribute6) lot_c_attribute6
, NVL(l_c_attribute7, mln.c_attribute7) lot_c_attribute7
, NVL(l_c_attribute8, mln.c_attribute8) lot_c_attribute8
, NVL(l_c_attribute9, mln.c_attribute9) lot_c_attribute9
, NVL(l_c_attribute10, mln.c_attribute10) lot_c_attribute10
, NVL(l_c_attribute11, mln.c_attribute11) lot_c_attribute11
, NVL(l_c_attribute12, mln.c_attribute12) lot_c_attribute12
, NVL(l_c_attribute13, mln.c_attribute13) lot_c_attribute13
, NVL(l_c_attribute14, mln.c_attribute14) lot_c_attribute14
, NVL(l_c_attribute15, mln.c_attribute15) lot_c_attribute15
, NVL(l_c_attribute16, mln.c_attribute16) lot_c_attribute16
, NVL(l_c_attribute17, mln.c_attribute17) lot_c_attribute17
, NVL(l_c_attribute18, mln.c_attribute18) lot_c_attribute18
, NVL(l_c_attribute19, mln.c_attribute19) lot_c_attribute19
, NVL(l_c_attribute20, mln.c_attribute20) lot_c_attribute20
, TO_CHAR(NVL(l_d_attribute1, mln.d_attribute1), g_date_format_mask) lot_d_attribute1
, -- Added for Bug 2795525,
TO_CHAR(NVL(l_d_attribute2, mln.d_attribute2), g_date_format_mask) lot_d_attribute2
, -- Added for Bug 2795525,
TO_CHAR(NVL(l_d_attribute3, mln.d_attribute3), g_date_format_mask) lot_d_attribute3
, -- Added for Bug 2795525,
TO_CHAR(NVL(l_d_attribute4, mln.d_attribute4), g_date_format_mask) lot_d_attribute4
, -- Added for Bug 2795525,
TO_CHAR(NVL(l_d_attribute5, mln.d_attribute5), g_date_format_mask) lot_d_attribute5
, -- Added for Bug 2795525,
TO_CHAR(NVL(l_d_attribute6, mln.d_attribute6), g_date_format_mask) lot_d_attribute6
, -- Added for Bug 2795525,
TO_CHAR(NVL(l_d_attribute7, mln.d_attribute7), g_date_format_mask) lot_d_attribute7
, -- Added for Bug 2795525,
TO_CHAR(NVL(l_d_attribute8, mln.d_attribute8), g_date_format_mask) lot_d_attribute8
, -- Added for Bug 2795525,
TO_CHAR(NVL(l_d_attribute9, mln.d_attribute9), g_date_format_mask) lot_d_attribute9
, -- Added for Bug 2795525,
TO_CHAR(
NVL(l_d_attribute10, mln.d_attribute10)
, g_date_format_mask
) lot_d_attribute10
, -- Added for Bug 2795525,
NVL(l_n_attribute1, mln.n_attribute1) lot_n_attribute1
, NVL(l_n_attribute2, mln.n_attribute2) lot_n_attribute2
, NVL(l_n_attribute3, mln.n_attribute3) lot_n_attribute3
, NVL(l_n_attribute4, mln.n_attribute4) lot_n_attribute4
, NVL(l_n_attribute5, mln.n_attribute5) lot_n_attribute5
, NVL(l_n_attribute6, mln.n_attribute6) lot_n_attribute6
, NVL(l_n_attribute7, mln.n_attribute7) lot_n_attribute7
, NVL(l_n_attribute8, mln.n_attribute8) lot_n_attribute8
, NVL(l_n_attribute9, mln.n_attribute9) lot_n_attribute9
, NVL(l_n_attribute10, mln.n_attribute10) lot_n_attribute10
, NVL(l_territory_code, mln.territory_code) lot_country_of_origin
, NVL(l_grade_code, mln.grade_code) lot_grade_code
, TO_CHAR(
NVL(l_origination_date, mln.origination_date)
, g_date_format_mask
) lot_origination_date
, -- Added for Bug 2795525,
NVL(l_date_code, mln.date_code) lot_date_code
, TO_CHAR(NVL(l_change_date, mln.change_date), g_date_format_mask) lot_change_date
, -- Added for Bug 2795525,
NVL(l_age, mln.age) lot_age
, TO_CHAR(NVL(l_retest_date, mln.retest_date), g_date_format_mask) lot_retest_date
, -- Added for Bug 2795525,
TO_CHAR(
NVL(l_maturity_date, mln.maturity_date)
, g_date_format_mask
) lot_maturity_date
, -- Added for Bug 2795525,
NVL(l_item_size, mln.item_size) lot_item_size
, NVL(l_color, mln.color) lot_color
, NVL(l_volume, mln.volume) lot_volume
, NVL(l_volume_uom, mln.volume_uom) lot_volume_uom
, NVL(l_place_of_origin, mln.place_of_origin) lot_place_of_origin
, TO_CHAR(NVL(l_best_by_date, mln.best_by_date), g_date_format_mask) lot_best_by_date
, -- Added for Bug 2795525,
NVL(l_length, mln.LENGTH) lot_length
, NVL(l_length_uom, mln.length_uom) lot_length_uom
, NVL(l_recycled_content, mln.recycled_content) lot_recycled_cont
, NVL(l_thickness, mln.thickness) lot_thickness
, NVL(l_thickness_uom, mln.thickness_uom) lot_thickness_uom
, NVL(l_width, mln.width) lot_width
, NVL(l_width_uom, mln.width_uom) lot_width_uom
, NVL(l_curl_wrinkle_fold, mln.curl_wrinkle_fold) lot_curl
, NVL(l_vendor_name, mln.vendor_name) lot_vendor
, l_cost_group cost_group
, poh.hazard_class item_hazard_class
, 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
, l_project_number project_number -- Fix For Bug: 4907062
, l_project_name project
, l_task_number task_number -- Fix For Bug: 4907062
, l_task_name task
, l_subinventory_code subinventory_code
, wilk.concatenated_segments LOCATOR
-- milk.concatenated_segments LOCATOR -- Modified for bug # 5015415
FROM mtl_system_items_vl msik --Bug 5302715 changed from kfv to vl
, mtl_lot_numbers mln
, mtl_material_statuses_vl mmsvl
, po_hazard_classes poh
, mtl_parameters mp
/*Commented for bug# 6334460 start
, DUAL d
Commented for bug# 6334460 end */
, wms_item_locations_kfv wilk
--, mtl_item_locations_kfv milk -- Modified for bug # 5015415
/*Commented for bug# 6334460 start
WHERE d.dummy = 'X'
AND msik.concatenated_segments(+) <> NVL('@@@', d.dummy)
Commented for bug# 6334460 End */
WHERE msik.inventory_item_id(+) = p_inventory_item_id
AND msik.organization_id(+) = p_organization_id
AND mp.organization_id = p_organization_id
AND mln.organization_id(+) = msik.organization_id
AND mln.inventory_item_id(+) = msik.inventory_item_id
AND mln.lot_number(+) = p_lot_number
AND mmsvl.status_id(+) = mln.status_id
AND poh.hazard_class_id(+) = msik.hazard_class_id
AND wilk.organization_id(+) = msik.organization_id
AND wilk.subinventory_code(+) = l_subinventory_code
AND wilk.inventory_location_id(+) = l_locator_id;
SELECT DISTINCT pod.project_id, pod.task_id
FROM po_distributions_all pod,
rcv_transactions rt
WHERE pod.po_header_id = rt.po_header_id
AND pod.po_line_id = rt.po_line_id
AND pod.line_location_id = rt.po_line_location_id
AND pod.po_distribution_id = nvl(rt.po_distribution_id, pod.po_distribution_id)
AND rt.group_id = p_transaction_id;
SELECT pop.project_reference_enabled
FROM pjm_org_parameters pop
WHERE pop.organization_id = p_organization_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 = 1
UNION --FOR FORMAT
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 = 1 ;
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,
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 transaction_type_id into l_transaction_type
from mtl_material_transactions_temp
where transaction_temp_id = p_transaction_id; --bug 6646793
select nvl (mmtt.transaction_quantity,mtlt.transaction_quantity) into l_mmtt_quantity
from mtl_material_transactions_temp mmtt , mtl_transaction_lots_temp mtlt
where mmtt.transaction_temp_id = p_transaction_id
and mtlt.transaction_temp_id = mmtt.transaction_temp_id;
SELECT Nvl(Sum(primary_transaction_quantity),0) INTO l_moqd_quantity
FROM mtl_onhand_quantities_detail moqd , mtl_material_transactions_temp mmtt , mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = p_transaction_id
and mtlt.transaction_temp_id = mmtt.transaction_temp_id
and moqd.lot_number = mtlt.lot_number
and nvl(mmtt.lpn_id , -999) = nvl(moqd.lpn_id , -999)
and moqd.inventory_item_id = mmtt.inventory_item_id
and moqd.organization_id = mmtt.organization_id
and moqd.subinventory_code = mmtt.subinventory_code
and NVL(moqd.locator_id , -999 ) = NVL(mmtt.locator_id ,-999);
/* R12 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 => l_organization_id
, p_inventory_item_id => l_inventory_item_id
, p_lot_number => l_lot_number
, p_revision => l_revision
, p_subinventory_code => l_subinventory_code
, p_locator_id => l_locator_id
, p_business_flow_code => p_label_type_info.business_flow_code
--, p_printer_name => l_printer --not used post R12
, p_last_update_date => SYSDATE
, p_last_updated_by => fnd_global.user_id
, p_creation_date => SYSDATE
, p_created_by => fnd_global.user_id
, -- Added for Bug 2748297 Start
p_supplier_id => l_vendor_id
, p_supplier_site_id => l_vendor_site_id
, -- End
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 => l_organization_id
, p_inventory_item_id => l_inventory_item_id
, p_lot_number => l_lot_number
, p_revision => l_revision
, p_subinventory_code => l_subinventory_code
, p_locator_id => l_locator_id
, p_business_flow_code => p_label_type_info.business_flow_code
--, p_printer_name => l_printer --not used post R12
, p_last_update_date => SYSDATE
, p_last_updated_by => fnd_global.user_id
, p_creation_date => SYSDATE
, p_created_by => fnd_global.user_id
, -- Added for Bug 2748297 Start
p_supplier_id => l_vendor_id
, p_supplier_site_id => l_vendor_site_id -- End
, p_use_rule_engine => 'N' --------------------------Rules ENgine will NOT get called
, 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
);
SELECT FLOOR(l_quantity)
INTO l_quantity_floor
FROM DUAL;
, p_last_update_date => SYSDATE
, p_last_updated_by => fnd_global.user_id
, p_creation_date => SYSDATE
, p_created_by => fnd_global.user_id
, -- Added for Bug 2748297 Start
p_supplier_id => l_vendor_id
, p_supplier_site_id => l_vendor_site_id -- End
, p_use_rule_engine => 'N' ------Rules Engine will NOT get called
, 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 --A NEW label request id
);
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 [INVLAP1B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
trace('Custom Labels Trace [INVLAP1B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
trace('Custom Labels Trace [INVLAP1B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
trace('Custom Labels Trace [INVLAP1B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
trace('Custom Labels Trace [INVLAP1B.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_material_data := l_material_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_material_data := l_material_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_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| inv_label.g_user
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_description' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| nvl(v_material_cur.item_description,l_item_description) /* Modified for the bug # 4708752*/
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'revision' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.revision
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_number' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_lot_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_status' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_status
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_expiration_date' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_expiration_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'quantity' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_quantity
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'uom' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'cost_group' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.cost_group
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'customer_purchase_order' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_purchase_order
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_attribute_category' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_attribute_category
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute1' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute1
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute2' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute2
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute3' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute3
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute4' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute4
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute5' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute5
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute6' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute6
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute7' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute7
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute8' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute8
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute9' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute9
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute10' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute10
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute11' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute11
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute12' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute12
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute13' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute13
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute14' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute14
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute15' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute15
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute16' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute16
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute17' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute17
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute18' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute18
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute19' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute19
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute20' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_c_attribute20
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute1' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute1
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute2' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute2
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute3' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute3
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute4' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute4
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute5' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute5
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute6' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute6
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute7' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute7
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute8' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute8
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute9' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute9
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute10' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_d_attribute10
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute1' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute1
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute2' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute2
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute3' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute3
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute4' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute4
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute5' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute5
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute6' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute6
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute7' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute7
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute8' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute8
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute9' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute9
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute10' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_n_attribute10
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) =
'lot_country_of_origin' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_country_of_origin
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_grade_code' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_grade_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_origination_date' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_origination_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_date_code' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_date_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_change_date' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_change_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_age' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_age
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_retest_date' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_retest_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_maturity_date' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_maturity_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_item_size' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_item_size
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_color' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_color
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_volume
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume_uom' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_volume_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_place_of_origin' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_place_of_origin
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_best_by_date' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_best_by_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_length
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length_uom' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_length_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_recycled_cont' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_recycled_cont
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_thickness
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness_uom' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_thickness_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_width
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width_uom' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_width_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_curl' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_curl
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_vendor' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.lot_vendor
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_hazard_class' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_hazard_class
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) =
'item_attribute_category' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute_category
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute1' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute1
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute2' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute2
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute3' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute3
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute4' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute4
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute5' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute5
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute6' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute6
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute7' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute7
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute8' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute8
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute9' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute9
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute10' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute10
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute11' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute11
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute12' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute12
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute13' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute13
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute14' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute14
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute15' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.item_attribute15
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'project_number' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.project_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'task_number' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.task_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'project' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.project
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'task' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.task
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'receipt_num' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_receipt_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'po_line_num' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_po_line_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'quan_ordered' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_quantity_ordered
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_part_num' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_supplier_part_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_name' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_supplier_name
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_site' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_supplier_site
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'requestor' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_requestor
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'deliver_to_loc' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_deliver_to_location
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'loc_id' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_location_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'note_to_receiver' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_note_to_receiver
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'rec_routing' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_routing_name
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'po_num' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_purchase_order
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.subinventory_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'locator' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.LOCATOR
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'organization' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| nvl(v_material_cur.ORGANIZATION,l_organization_id) /* Modified for the bug # 4708752*/
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'cycle_count_name' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">' || l_cycle_count_name
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'gtin' THEN
l_material_data := l_material_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_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_gtin_desc
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'wip_entity_name' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_wip_entity_name
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'wip_description' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_wip_description
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'wip_operation_seq_num' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_wip_op_seq_num
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'osp_department_code' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_osp_dept_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'bom_resource' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_bom_resource_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lot_number' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.parent_lot_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'expiration_action_date' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.expiration_action_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'origination_type' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| get_origination_type (v_material_cur.origination_type)
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'hold_date' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.hold_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'secondary_transaction_quantity' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.secondary_quantity
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'secondary_uom_code' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.secondary_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'expiration_action_code' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.expiration_action_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'supplier_lot_number' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| v_material_cur.supplier_lot_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'epc' THEN
l_material_data := l_material_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_epc
|| variable_e;