[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_get_hash_for_insert NUMBER := 1;
IF p_get_hash_mode = g_get_hash_for_insert THEN
g_field_elements_table(l_return_hash_value).column_name_with_count := p_input_string;
SELECT wlf.column_name,
wlf.sql_stmt,
wlfv.field_variable_name
FROM wms_label_field_variables wlfv,
wms_label_fields_vl wlf
WHERE wlfv.label_format_id = p_label_format_id
AND wlfv.label_field_id = wlf.label_field_id
ORDER BY wlf.column_name, wlfv.field_variable_name;
g_field_elements_table.delete;
g_field_elements_table(get_field_hash_value(l_label_field_var.column_name||l_column_count, g_get_hash_for_insert)).variable_name := l_label_field_var.field_variable_name;
g_field_elements_table(get_field_hash_value(l_label_field_var.column_name||l_column_count, g_get_hash_for_insert)).sql_stmt := l_label_field_var.sql_stmt;
SELECT distinct wda.delivery_id
FROM wsh_delivery_assignments wda
, wsh_delivery_details wdd
, mtl_material_transactions_temp mmtt
WHERE mmtt.cartonization_id = v_cartonization_id
AND mmtt.move_order_line_id = wdd.move_order_line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id;
g_carton_tb.delete;
SELECT lbfl.column_name
FROM wms_label_field_variables lbvar,
wms_label_fields_vl lbfl
WHERE lbfl.label_field_id = lbvar.label_field_id
AND lbvar.label_format_id = v_label_format_id
GROUP BY lbfl.column_name;
SELECT
nvl(wdd.requested_quantity, mmtt.transaction_quantity) requested_quantity
, mmtt.transaction_quantity shipped_quantity
, mmtt.secondary_transaction_quantity shipped_quantity2
, mmtt.transaction_uom uom
, mmtt.revision revision
, mmtt.lot_number lot_number
, wdd.cancelled_quantity
, wdd.delivered_quantity
, wdd.carrier_id
, wdd.cust_po_number customer_purchase_order
, wdd.customer_id
, wdd.ship_method_code
, NULL oe_ship_method_code
, mmtt.organization_id
, mmtt.subinventory_code from_subinventory
, mmtt.locator_id from_locator_id
, milk.concatenated_segments from_locator
, mmtt.transfer_subinventory to_subinventory
, mmtt.transfer_to_location to_locator_id
, milk2.concatenated_segments to_locator
--Standalone
,decode(l_deploy_mode,
'I', wdd.source_header_number,
'D', wdd.reference_number,
'L', wdd.reference_number) source_header_number
,decode(l_deploy_mode,
'I', wdd.source_line_number,
'D', wdd.reference_line_number,
'L', wdd.reference_line_number) source_line_number
--, wdd.source_header_number
--, wdd.source_line_number
, wdd.tracking_number
, wdd.fob_code FOB
, mmtt.inventory_item_id
, wdd.customer_item_id
, wdd.project_id
, wdd.task_id
, wda.delivery_id
, wdd.ship_from_location_id
, wdd.ship_to_location_id
, wdd.ship_to_site_use_id
, wdd.ship_to_contact_id
, wdd.sold_to_contact_id
, wdd.deliver_to_location_id
, wdd.deliver_to_contact_id
, wdd.deliver_to_site_use_id
, oeol.header_id source_header_id
, wdd.source_line_id
, wdd.attribute_category
, wdd.attribute1
, wdd.attribute2
, wdd.attribute3
, wdd.attribute4
, wdd.attribute5
, wdd.attribute6
, wdd.attribute7
, wdd.attribute8
, wdd.attribute9
, wdd.attribute10
, wdd.attribute11
, wdd.attribute12
, wdd.attribute13
, wdd.attribute14
, wdd.attribute15
, wdd.tp_attribute_category
, wdd.tp_attribute1
, wdd.tp_attribute2
, wdd.tp_attribute3
, wdd.tp_attribute4
, wdd.tp_attribute5
, wdd.tp_attribute6
, wdd.tp_attribute7
, wdd.tp_attribute8
, wdd.tp_attribute9
, wdd.tp_attribute10
, wdd.tp_attribute11
, wdd.tp_attribute12
, wdd.tp_attribute13
, wdd.tp_attribute14
, wdd.tp_attribute15
-- , Nvl(mmtt.transfer_lpn_id, cartonization_id) outer_lpn_id
, Nvl(Nvl(mmtt.content_lpn_id, mmtt.transfer_lpn_id), cartonization_id) outer_lpn_id --Changes for 13419924
, NULL number_of_total
, NULL delivery_number -- Place holder, get later with c_delivery
, NULL waybill -- Place holder, get later with c_delivery
, NULL airbill -- Place holder, get later with c_delivery
, NULL bill_of_lading -- Place holder, get later with c_delivery
, NULL trip_number -- Place holder, get later with c_delivery
, NULL wnd_carrier_id -- Place holder, get later with c_delivery
, NULL wnd_ship_method_code -- Place holder, get later with c_delivery
, NULL intmed_ship_to_location_id -- Place holder, get later with c_delivery
, wdd.intmed_ship_to_contact_id
, wdd.delivery_detail_id --Bug9261874
FROM
(SELECT mmtt1.inventory_item_id,
mmtt1.organization_id,
mmtt1.subinventory_code,
mmtt1.locator_id,
mmtt1.transfer_organization,
mmtt1.transfer_to_location,
mmtt1.transfer_subinventory,
mmtt1.move_order_line_id,
mmtt1.content_lpn_id,
mmtt1.transfer_lpn_id,
mmtt1.cartonization_id,
mmtt1.transaction_temp_id,
mmtt1.revision,
mmtt1.transaction_uom,
nvl(mtlt1.transaction_quantity, mmtt1.transaction_quantity) transaction_quantity,
nvl(mtlt1.secondary_quantity, mmtt1.secondary_transaction_quantity) secondary_transaction_quantity, --Bug# 3596990
mtlt1.lot_number
FROM
mtl_material_transactions_temp mmtt1,
mtl_transaction_lots_temp mtlt1
WHERE mmtt1.transaction_temp_id = mtlt1.transaction_temp_id(+)
) mmtt, -- mmtt with lot number information
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations_kfv milk,
mtl_item_locations_kfv milk2,
oe_order_lines_all oeol
WHERE ((mmtt.transaction_temp_id = p_transaction_temp_id AND
p_transaction_temp_id IS NOT NULL) OR
(mmtt.cartonization_id = p_cartonization_id AND
p_cartonization_id IS NOT NULL))
AND mmtt.move_order_line_id = wdd.move_order_line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.released_status = 'S'
AND mmtt.organization_id = milk.organization_id (+)
AND mmtt.locator_id = milk.inventory_location_id(+)
AND mmtt.transfer_organization = milk2.organization_id (+)
AND mmtt.transfer_to_location = milk2.inventory_location_id(+)
AND wdd.source_line_id = oeol.line_id(+)
ORDER BY mmtt.inventory_item_id, mmtt.lot_number;
SELECT acct_site.cust_acct_site_id cust_site_id
from wsh_delivery_details wdd
, wsh_delivery_assignments wda
, hz_cust_site_uses_all hcsua
, hz_party_sites party_site
--, hz_loc_assignments loc_assign --13683119
, hz_locations loc
, hz_cust_acct_sites_all acct_site
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'N'
AND wda.delivery_id=p_delivery_detail_id
and hcsua.site_use_id = wdd.ship_to_site_use_id
and acct_site.cust_acct_site_id = hcsua.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
--AND loc.location_id = loc_assign.location_id --13683119
--AND NVL ( acct_site.org_id, -99 ) = NVL ( loc_assign.org_id, -99 ) --13683119
--start of 13683119
AND DECODE ( NVL(
(SELECT 1
FROM hz_geo_struct_map map
WHERE map.country_code = loc.country
AND map.loc_tbl_name = 'HZ_LOCATIONS'
AND rownum = 1
), 0), 1, NVL(
(SELECT 2
FROM hz_geo_name_reference_log log1
WHERE 2 = 2
AND log1.location_id = loc.location_id
AND log1.location_table_name = 'HZ_LOCATIONS'
AND log1.usage_code = 'TAX'
AND log1.map_status = 'S'
AND ROWNUM = 1
), 0), NVL(
(SELECT 2
FROM hz_geographies g
WHERE 3 = 3
AND g.country_code = loc.country
AND g.geography_type = 'COUNTRY'
AND ROWNUM = 1
), 0) ) = 2
--end of 13683119
AND ROWNUM=1;
SELECT wdd_item.requested_quantity
,wdd_item.shipped_quantity
,wdd_item.shipped_quantity2
,wdd_item.requested_quantity_uom uom
,wdd_item.revision
,wdd_item.lot_number
,wdd_item.cancelled_quantity
,wdd_item.delivered_quantity
,wdd_item.carrier_id
,wdd_item.cust_po_number customer_purchase_order
,wdd_item.customer_id
,wdd_item.ship_method_code
,to_char(NULL) oe_ship_method_code
,wdd_item.organization_id
,to_char(NULL) from_subinventory
,to_number(NULL) from_locator_id
,to_char(NULL) from_locator
,to_char(NULL) to_subinventory -- get it later from LPN
,to_number(NULL) to_locator_id -- get it later from LPN
,to_char(NULL) to_locator -- get it later from LPN
--Standalone
,decode(l_deploy_mode,
'I', wdd_item.source_header_number,
'D', wdd_item.reference_number,
'L', wdd_item.reference_number) source_header_number
,decode(l_deploy_mode,
'I', wdd_item.source_line_number,
'D', wdd_item.reference_line_number,
'L', wdd_item.reference_line_number) source_line_number
--,wdd_item.source_header_number
--,wdd_item.source_line_number
,wdd_item.tracking_number
,wdd_item.fob_code FOB
,wdd_item.inventory_item_id
,wdd_item.customer_item_id
,wdd_item.project_id
,wdd_item.task_id
,wda.delivery_id
,wdd_item.ship_from_location_id
,wdd_item.ship_to_location_id
,wdd_item.ship_to_site_use_id
,wdd_item.ship_to_contact_id
,wdd_item.sold_to_contact_id
,wdd_item.deliver_to_location_id
,wdd_item.deliver_to_contact_id
,wdd_item.deliver_to_site_use_id
,oeol.header_id source_header_id
,wdd_item.source_line_id
,wdd_item.attribute_category
,wdd_item.attribute1
,wdd_item.attribute2
,wdd_item.attribute3
,wdd_item.attribute4
,wdd_item.attribute5
,wdd_item.attribute6
,wdd_item.attribute7
,wdd_item.attribute8
,wdd_item.attribute9
,wdd_item.attribute10
,wdd_item.attribute11
,wdd_item.attribute12
,wdd_item.attribute13
,wdd_item.attribute14
,wdd_item.attribute15
,wdd_item.tp_attribute_category
,wdd_item.tp_attribute1
,wdd_item.tp_attribute2
,wdd_item.tp_attribute3
,wdd_item.tp_attribute4
,wdd_item.tp_attribute5
,wdd_item.tp_attribute6
,wdd_item.tp_attribute7
,wdd_item.tp_attribute8
,wdd_item.tp_attribute9
,wdd_item.tp_attribute10
,wdd_item.tp_attribute11
,wdd_item.tp_attribute12
,wdd_item.tp_attribute13
,wdd_item.tp_attribute14
,wdd_item.tp_attribute15
,wlpn.outermost_lpn_id outer_lpn_id
,NULL number_of_total
,NULL delivery_number -- Place holder, get later with c_delivery
,NULL waybill -- Place holder, get later with c_delivery
,NULL airbill -- Place holder, get later with c_delivery
,NULL bill_of_lading -- Place holder, get later with c_delivery
,NULL trip_number -- Place holder, get later with c_delivery
,NULL wnd_carrier_id -- Place holder, get later with c_delivery
,NULL wnd_ship_method_code -- Place holder, get later with c_delivery
,NULL intmed_ship_to_location_id -- Place holder, get later with c_delivery
,wdd_item.intmed_ship_to_contact_id
,wdd_item.delivery_detail_id --Bug9261874
FROM wsh_delivery_details wdd_item -- records with item info
, wsh_delivery_details wdd_lpn -- records of the immediate lpn
, wsh_delivery_assignments_v wda
, oe_order_lines_all oeol
, wms_license_plate_numbers wlpn
WHERE wda.delivery_detail_id = wdd_item.delivery_detail_id
AND wda.parent_delivery_detail_id = wdd_lpn.delivery_detail_id
AND (wdd_item.inventory_item_id IS NOT NULL AND
wdd_item.lpn_id IS NULL)
AND wdd_lpn.lpn_id IN
(SELECT wlpn2.lpn_id
FROM wms_license_plate_numbers wlpn2
WHERE wlpn2.outermost_lpn_id = wlpn.outermost_lpn_id)
AND wlpn.lpn_id = p_lpn_id
AND wdd_item.source_line_id = oeol.line_id(+)
AND wdd_item.organization_id = wlpn.organization_id /*9180228*/
ORDER BY wdd_item.inventory_item_id, wdd_item.lot_number;
SELECT wdd_item.requested_quantity
,wdd_item.shipped_quantity
,wdd_item.shipped_quantity2
,wdd_item.requested_quantity_uom uom
,wdd_item.revision
,wdd_item.lot_number
,wdd_item.cancelled_quantity --Added bug3952110 -- about LPN contains multiple splitted line from del det. sum qty
,wdd_item.delivered_quantity
,wdd_item.carrier_id
,wdd_item.cust_po_number customer_purchase_order
,wdd_item.customer_id
,wdd_item.ship_method_code
,NULL oe_ship_method_code
,wdd_item.organization_id
,NULL from_subinventory
,NULL from_locator_id
,NULL from_locator
,NULL to_subinventory -- get it later from LPN
,NULL to_locator_id -- get it later from LPN
,NULL to_locator -- get it later from LPN
--Standalone
,decode(l_deploy_mode,
'I', wdd_item.source_header_number,
'D', wdd_item.reference_number,
'L', wdd_item.reference_number) source_header_number
,decode(l_deploy_mode,
'I', wdd_item.source_line_number,
'D', wdd_item.reference_line_number,
'L', wdd_item.reference_line_number) source_line_number
--,wdd_item.source_header_number
--,wdd_item.source_line_number
,wdd_item.tracking_number
,wdd_item.fob_code FOB
,wdd_item.inventory_item_id
,wdd_item.customer_item_id
,wdd_item.project_id
,wdd_item.task_id
,wda.delivery_id
,wdd_item.ship_from_location_id
,wdd_item.ship_to_location_id
,wdd_item.ship_to_site_use_id
,wdd_item.ship_to_contact_id
,wdd_item.deliver_to_location_id
,wdd_item.deliver_to_contact_id
,wdd_item.deliver_to_site_use_id
,wdd_item.sold_to_contact_id
,oeol.header_id source_header_id
,wdd_item.source_line_id
,wdd_item.attribute_category
,wdd_item.attribute1
,wdd_item.attribute2
,wdd_item.attribute3
,wdd_item.attribute4
,wdd_item.attribute5
,wdd_item.attribute6
,wdd_item.attribute7
,wdd_item.attribute8
,wdd_item.attribute9
,wdd_item.attribute10
,wdd_item.attribute11
,wdd_item.attribute12
,wdd_item.attribute13
,wdd_item.attribute14
,wdd_item.attribute15
,wdd_item.tp_attribute_category
,wdd_item.tp_attribute1
,wdd_item.tp_attribute2
,wdd_item.tp_attribute3
,wdd_item.tp_attribute4
,wdd_item.tp_attribute5
,wdd_item.tp_attribute6
,wdd_item.tp_attribute7
,wdd_item.tp_attribute8
,wdd_item.tp_attribute9
,wdd_item.tp_attribute10
,wdd_item.tp_attribute11
,wdd_item.tp_attribute12
,wdd_item.tp_attribute13
,wdd_item.tp_attribute14
,wdd_item.tp_attribute15
,wlpn.outermost_lpn_id outer_lpn_id
,NULL number_of_total
,NULL delivery_number -- Place holder, get later with c_delivery
,NULL waybill -- Place holder, get later with c_delivery
,NULL airbill -- Place holder, get later with c_delivery
,NULL bill_of_lading -- Place holder, get later with c_delivery
,NULL trip_number -- Place holder, get later with c_delivery
,NULL wnd_carrier_id -- Place holder, get later with c_delivery
,NULL wnd_ship_method_code -- Place holder, get later with c_delivery
,NULL intmed_ship_to_location_id -- Place holder, get later with c_delivery
,wdd_item.intmed_ship_to_contact_id
,wdd_item.delivery_detail_id --Bug9261874
FROM wsh_delivery_details wdd_item -- records with item info
, wsh_delivery_details wdd_lpn -- records of the immediate lpn
, wms_license_plate_numbers wlpn
, wsh_delivery_assignments_v wda
, oe_order_lines_all oeol
WHERE wda.delivery_detail_id = wdd_item.delivery_detail_id
AND (wdd_item.inventory_item_id IS NOT NULL AND
wdd_item.lpn_id IS NULL)
AND wda.parent_delivery_detail_id = wdd_lpn.delivery_detail_id
AND wdd_lpn.lpn_id = wlpn.lpn_id
AND wda.delivery_id = p_delivery_id
AND wdd_item.source_line_id = oeol.line_id(+)
ORDER BY wlpn.outermost_lpn_id, wdd_item.inventory_item_id, wdd_item.lot_number;
SELECT wdd_item.requested_quantity
,wdd_item.shipped_quantity
,wdd_item.shipped_quantity2
,wdd_item.requested_quantity_uom uom
,wdd_item.revision
,wdd_item.lot_number
,wdd_item.cancelled_quantity --Added bug3952110 -- about LPN contains multiple splitted line from del det. sum qty
,wdd_item.delivered_quantity
,wdd_item.carrier_id
,wdd_item.cust_po_number customer_purchase_order
,wdd_item.customer_id
,wdd_item.ship_method_code
,NULL oe_ship_method_code
,wdd_item.organization_id
,NULL from_subinventory
,NULL from_locator_id
,NULL from_locator
,wdd_item.subinventory to_subinventory
,wdd_item.locator_id to_locator_id
,milk.concatenated_segments to_locator
--Standalone
,decode(l_deploy_mode,
'I', wdd_item.source_header_number,
'D', wdd_item.reference_number,
'L', wdd_item.reference_number) source_header_number
,decode(l_deploy_mode,
'I', wdd_item.source_line_number,
'D', wdd_item.reference_line_number,
'L', wdd_item.reference_line_number) source_line_number
--,wdd_item.source_header_number
--,wdd_item.source_line_number
,wdd_item.tracking_number
,wdd_item.fob_code FOB
,wdd_item.inventory_item_id
,wdd_item.customer_item_id
,wdd_item.project_id
,wdd_item.task_id
,wda.delivery_id
,wdd_item.ship_from_location_id
,wdd_item.ship_to_location_id
,wdd_item.ship_to_site_use_id
,wdd_item.ship_to_contact_id
,wdd_item.sold_to_contact_id
,wdd_item.deliver_to_location_id
,wdd_item.deliver_to_contact_id
,wdd_item.deliver_to_site_use_id
,oeol.header_id source_header_id
,wdd_item.source_line_id
,wdd_item.attribute_category
,wdd_item.attribute1
,wdd_item.attribute2
,wdd_item.attribute3
,wdd_item.attribute4
,wdd_item.attribute5
,wdd_item.attribute6
,wdd_item.attribute7
,wdd_item.attribute8
,wdd_item.attribute9
,wdd_item.attribute10
,wdd_item.attribute11
,wdd_item.attribute12
,wdd_item.attribute13
,wdd_item.attribute14
,wdd_item.attribute15
,wdd_item.tp_attribute_category
,wdd_item.tp_attribute1
,wdd_item.tp_attribute2
,wdd_item.tp_attribute3
,wdd_item.tp_attribute4
,wdd_item.tp_attribute5
,wdd_item.tp_attribute6
,wdd_item.tp_attribute7
,wdd_item.tp_attribute8
,wdd_item.tp_attribute9
,wdd_item.tp_attribute10
,wdd_item.tp_attribute11
,wdd_item.tp_attribute12
,wdd_item.tp_attribute13
,wdd_item.tp_attribute14
,wdd_item.tp_attribute15
,NULL outer_lpn_id
,NULL number_of_total
,NULL delivery_number -- Place holder, get later with c_delivery
,NULL waybill -- Place holder, get later with c_delivery
,NULL airbill -- Place holder, get later with c_delivery
,NULL bill_of_lading -- Place holder, get later with c_delivery
,NULL trip_number -- Place holder, get later with c_delivery
,NULL wnd_carrier_id -- Place holder, get later with c_delivery
,NULL wnd_ship_method_code -- Place holder, get later with c_delivery
,NULL intmed_ship_to_location_id -- Place holder, get later with c_delivery
,wdd_item.intmed_ship_to_contact_id
,wdd_item.delivery_detail_id --Bug9261874
FROM wsh_delivery_details wdd_item -- records with item info
, wsh_delivery_assignments_v wda
, mtl_item_locations_kfv milk
, oe_order_lines_all oeol
WHERE wda.delivery_detail_id = wdd_item.delivery_detail_id
AND (wdd_item.inventory_item_id IS NOT NULL AND
wdd_item.lpn_id IS NULL)
AND wda.delivery_id = p_delivery_id
AND wdd_item.organization_id = milk.organization_id (+)
AND wdd_item.locator_id = milk.inventory_location_id(+)
AND wdd_item.source_line_id = oeol.line_id(+)
ORDER BY wdd_item.inventory_item_id, wdd_item.lot_number;
SELECT wnd.name delivery_number
, wnd.waybill waybill
, wnd.waybill airbill
, wdi.sequence_number bill_of_lading
, wt.name trip_number
-- Bug 5121507, Get carrier in the order of Trip->Delivery->Delivery Detail
--, nvl(wnd.carrier_id, wt.carrier_id) wnd_carrier_id
, nvl(wt.carrier_id, wnd.carrier_id) wnd_carrier_id
, wnd.ship_method_code wnd_ship_method_code
, wnd.intmed_ship_to_location_id
FROM wsh_new_deliveries wnd
, wsh_delivery_legs wdl
, wsh_document_instances wdi
, wsh_trip_stops wts
, wsh_trips wt
WHERE wnd.delivery_id = wdl.delivery_id(+)
AND wdi.entity_name (+) = 'WSH_DELIVERY_LEGS'
AND wdl.delivery_leg_id = wdi.entity_id (+)
AND wdl.pick_up_stop_id = wts.stop_id (+)
AND wts.trip_id = wt.trip_id (+)
AND wnd.delivery_id = p_delivery_id;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT hou.name organization_name
, loc.telephone_number_1 org_tel_num
, loc.telephone_number_2 org_fax_num
FROM hr_organization_units hou
, hr_locations_all_v loc
WHERE hou.organization_id = p_organization_id
AND hou.location_id = loc.location_id (+);
SELECT wdd.load_seq_number
, wdd.net_weight
, wdd.gross_weight
, wdd.tracking_number
, wdd.gross_weight
, wdd.weight_uom_code
, (wdd.gross_weight - wdd.net_weight) tare_weight
, wdd.weight_uom_code tare_weight_uom
, wdd.volume
, wdd.volume_uom_code
FROM wsh_delivery_details wdd
WHERE lpn_id = p_lpn_id;
SELECT msik.concatenated_segments
,WMS_DEPLOY.GET_CLIENT_ITEM(p_organization_id,p_inventory_item_id) -- Added for LSP Project, bug 9087971
,msik.description
,msik.secondary_uom_code
,msik.attribute_category
,msik.attribute1
,msik.attribute2
,msik.attribute3
,msik.attribute4
,msik.attribute5
,msik.attribute6
,msik.attribute7
,msik.attribute8
,msik.attribute9
,msik.attribute10
,msik.attribute11
,msik.attribute12
,msik.attribute13
,msik.attribute14
,msik.attribute15
,poh.hazard_class
FROM mtl_system_items_kfv msik
,po_hazard_classes poh
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = p_inventory_item_id
AND msik.hazard_class_id = poh.hazard_class_id(+);
SELECT
mci.customer_item_number
, mci.attribute_category
, mci.attribute1
, mci.attribute2
, mci.attribute3
, mci.attribute4
, mci.attribute5
, mci.attribute6
, mci.attribute7
, mci.attribute8
, mci.attribute9
, mci.attribute10
, mci.attribute11
, mci.attribute12
, mci.attribute13
, mci.attribute14
, mci.attribute15
FROM mtl_customer_items mci
WHERE mci.customer_item_id = p_customer_item_id;
SELECT
mmst.status_code lot_number_status
, to_char(mln.expiration_date, G_DATE_FORMAT_MASK) lot_expiration_date
, 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
, to_char(mln.D_ATTRIBUTE2, G_DATE_FORMAT_MASK) lot_d_attribute2
, to_char(mln.D_ATTRIBUTE3, G_DATE_FORMAT_MASK) lot_d_attribute3
, to_char(mln.D_ATTRIBUTE4, G_DATE_FORMAT_MASK) lot_d_attribute4
, to_char(mln.D_ATTRIBUTE5, G_DATE_FORMAT_MASK) lot_d_attribute5
, to_char(mln.D_ATTRIBUTE6, G_DATE_FORMAT_MASK) lot_d_attribute6
, to_char(mln.D_ATTRIBUTE7, G_DATE_FORMAT_MASK) lot_d_attribute7
, to_char(mln.D_ATTRIBUTE8, G_DATE_FORMAT_MASK) lot_d_attribute8
, to_char(mln.D_ATTRIBUTE9, G_DATE_FORMAT_MASK) lot_d_attribute9
, to_char(mln.D_ATTRIBUTE10, G_DATE_FORMAT_MASK) lot_d_attribute10
, 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
, mln.DATE_CODE lot_date_code
, to_char(mln.CHANGE_DATE, G_DATE_FORMAT_MASK) lot_change_date
, mln.AGE lot_age
, to_char(mln.RETEST_DATE, G_DATE_FORMAT_MASK) lot_retest_date
, to_char(mln.MATURITY_DATE, G_DATE_FORMAT_MASK) lot_maturity_date
, 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
, 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
, mln.parent_lot_number parent_lot_number
, mln.expiration_action_date expiration_action_date
, ml.meaning origination_type
, mln.hold_date hold_date
, mln.expiration_action_code expiration_action_code
, mln.supplier_lot_number supplier_lot_number
FROM mtl_lot_numbers mln,
mtl_material_statuses_b mmsb,
mtl_material_statuses_tl mmst,
mfg_lookups ml
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.status_id = mmsb.status_id(+)
AND mmsb.status_id = mmst.status_id(+)
AND mmst.language(+) = USERENV('LANG')
AND ml.lookup_type(+) = 'MTL_LOT_ORIGINATION_TYPE'
AND ml.lookup_code(+) = mln.origination_type;
SELECT substrb(party.party_name,1,50) customer_name,
cust_acct.account_number customer_number
FROM hz_parties party
, hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id
AND cust_acct.cust_account_id = p_customer_id;
SELECT name
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT task_name
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT wlpn.license_plate_number
, wlpn.content_volume
, wlpn.content_volume_uom_code
, wlpn.gross_weight
, wlpn.gross_weight_uom_code
, wlpn.tare_weight
, wlpn.tare_weight_uom_code
, wlpn.subinventory_code
, wlpn.locator_id
, milk.concatenated_segments locator
, wlpn.attribute_category
, wlpn.attribute1
, wlpn.attribute2
, wlpn.attribute3
, wlpn.attribute4
, wlpn.attribute5
, wlpn.attribute6
, wlpn.attribute7
, wlpn.attribute8
, wlpn.attribute9
, wlpn.attribute10
, wlpn.attribute11
, wlpn.attribute12
, wlpn.attribute13
, wlpn.attribute14
, wlpn.attribute15
, msik.concatenated_segments lpn_container_item
FROM wms_license_plate_numbers wlpn
, mtl_system_items_kfv msik
, mtl_item_locations_kfv milk
WHERE wlpn.lpn_id = p_lpn_id
AND msik.organization_id(+) = wlpn.organization_id
AND msik.inventory_item_id(+) = wlpn.inventory_item_id
AND milk.organization_id(+) = wlpn.organization_id
AND milk.inventory_location_id(+) = wlpn.locator_id;
SELECT carrier_name
FROM wsh_carriers_v
WHERE carrier_id = p_carrier_id;
SELECT meaning
FROM fnd_common_lookups
WHERE lookup_type='SHIP_METHOD'
AND lookup_code = p_ship_method_code
AND ROWNUM<2;
SELECT hr.address_line_1
, hr.address_line_2
, hr.address_line_3
, hr.address_line_4
, hr.city
, hr.postal_code
, hr.state
, hr.county
, hr.country
, hr.province
, hr.location_code
, hr.location_description
FROM (SELECT loc.location_id location_id,loc.address_line_1 address_line_1
,loc.address_line_2 address_line_2,loc.address_line_3 address_line_3
,loc.loc_information13 address_line_4,loc.town_or_city city
,loc.postal_code postal_code,loc.region_2 state,loc.region_1 county
,loc.country country,loc.region_3 province, loc.location_code location_code
,loc.description location_description
FROM hr_locations_all loc
UNION ALL
SELECT hz.location_id location_id,hz.address1 address_line_1
,hz.address2 address_line_2,hz.address3 address_line_3
,hz.address4 address_line_4,hz.city city,hz.postal_code postal_code
,hz.state state,hz.county county,hz.country country,hz.province province
,hz.description location_code, hz.description location_description
FROM hz_locations hz) hr
WHERE hr.location_id = p_location_id;
SELECT location
FROM hz_cust_site_uses_all
WHERE site_use_id = p_site_use_id;
SELECT hcp.phone_country_code||decode(hcp.phone_country_code, NULL, '',' ')||
decode(hcp.phone_area_code,NULL,'','(')||hcp.phone_area_code||decode(hcp.phone_area_code,NULL,'',')')||
hcp.phone_number customer_site_tel_number
FROM hz_party_sites hps, hz_locations hl, hz_contact_points hcp
WHERE hps.location_id = hl.location_id
AND hcp.owner_table_name = 'HZ_PARTY_SITES'
AND hcp.owner_table_id = hps.party_site_id
AND (((hcp.phone_line_type IN ('PHONE','GEN')) AND (p_type = 'PHONE')) OR
((hcp.phone_line_type IN ('FAX')) AND (p_type = 'FAX')))
AND hps.location_id = p_location_id;
SELECT ra_cont.last_name || decode(ra_cont.last_name, NULL, NULL, ', ')
|| ra_cont.first_name contact_name
FROM ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) ra_cont
WHERE ra_cont.contact_id = p_contact_id;
SELECT
to_char(oeol.SCHEDULE_SHIP_DATE, G_DATE_FORMAT_MASK)
, to_char(oeol.REQUEST_DATE, G_DATE_FORMAT_MASK)
, to_char(oeol.PROMISE_DATE, G_DATE_FORMAT_MASK)
, oeol.SHIPMENT_PRIORITY_CODE
, oeol.shipping_method_code
, oeol.FREIGHT_CARRIER_CODE
, to_char(oeol.SCHEDULE_ARRIVAL_DATE, G_DATE_FORMAT_MASK)
, to_char(oeol.ACTUAL_SHIPMENT_DATE, G_DATE_FORMAT_MASK)
, oeol.SHIPPING_INSTRUCTIONS
, oeol.PACKING_INSTRUCTIONS
, oeol.attribute1
, oeol.attribute2
, oeol.attribute3
, oeol.attribute4
, oeol.attribute5
, oeol.attribute6
, oeol.attribute7
, oeol.attribute8
, oeol.attribute9
, oeol.attribute10
, oeol.attribute11
, oeol.attribute12
, oeol.attribute13
, oeol.attribute14
, oeol.attribute15
, oeol.global_attribute1
, oeol.global_attribute2
, oeol.global_attribute3
, oeol.global_attribute4
, oeol.global_attribute5
, oeol.global_attribute6
, oeol.global_attribute7
, oeol.global_attribute8
, oeol.global_attribute9
, oeol.global_attribute10
, oeol.global_attribute11
, oeol.global_attribute12
, oeol.global_attribute13
, oeol.global_attribute14
, oeol.global_attribute15
, oeol.global_attribute16
, oeol.global_attribute17
, oeol.global_attribute18
, oeol.global_attribute19
, oeol.global_attribute20
, oeol.pricing_attribute1
, oeol.pricing_attribute2
, oeol.pricing_attribute3
, oeol.pricing_attribute4
, oeol.pricing_attribute5
, oeol.pricing_attribute6
, oeol.pricing_attribute7
, oeol.pricing_attribute8
, oeol.pricing_attribute9
, oeol.pricing_attribute10
, oeol.industry_attribute1
, oeol.industry_attribute2
, oeol.industry_attribute3
, oeol.industry_attribute4
, oeol.industry_attribute5
, oeol.industry_attribute6
, oeol.industry_attribute7
, oeol.industry_attribute8
, oeol.industry_attribute9
, oeol.industry_attribute10
, oeol.industry_attribute11
, oeol.industry_attribute13
, oeol.industry_attribute12
, oeol.industry_attribute14
, oeol.industry_attribute15
, oeol.industry_attribute16
, oeol.industry_attribute17
, oeol.industry_attribute18
, oeol.industry_attribute19
, oeol.industry_attribute20
, oeol.industry_attribute21
, oeol.industry_attribute22
, oeol.industry_attribute23
, oeol.industry_attribute24
, oeol.industry_attribute25
, oeol.industry_attribute26
, oeol.industry_attribute27
, oeol.industry_attribute28
, oeol.industry_attribute29
, oeol.industry_attribute30
, oeol.return_attribute1
, oeol.return_attribute2
, oeol.return_attribute3
, oeol.return_attribute4
, oeol.return_attribute5
, oeol.return_attribute6
, oeol.return_attribute7
, oeol.return_attribute8
, oeol.return_attribute9
, oeol.return_attribute10
, oeol.return_attribute11
, oeol.return_attribute12
, oeol.return_attribute13
, oeol.return_attribute14
, oeol.return_attribute15
, oeol.tp_attribute1
, oeol.tp_attribute2
, oeol.tp_attribute3
, oeol.tp_attribute4
, oeol.tp_attribute5
, oeol.tp_attribute6
, oeol.tp_attribute7
, oeol.tp_attribute8
, oeol.tp_attribute9
, oeol.tp_attribute10
, oeol.tp_attribute11
, oeol.tp_attribute12
, oeol.tp_attribute13
, oeol.tp_attribute14
, oeol.tp_attribute15
, Nvl(oeol.ordered_item,
Decode(oeol.item_identifier_type,
'CUST', mci_oi.customer_item_number,
'INT', msik_oi.concatenated_segments,
msik_oi.concatenated_segments)) ordered_item
FROM oe_order_lines_all oeol
, mtl_customer_items mci_oi
, mtl_system_items_kfv msik_oi
WHERE oeol.line_id = p_line_id
AND oeol.ordered_item_id = mci_oi.customer_item_id (+)
AND oeol.ordered_item_id = msik_oi.inventory_item_id (+)
AND oeol.org_id = msik_oi.organization_id (+);
SELECT
oeoh.attribute1
, oeoh.attribute2
, oeoh.attribute3
, oeoh.attribute4
, oeoh.attribute5
, oeoh.attribute6
, oeoh.attribute7
, oeoh.attribute8
, oeoh.attribute9
, oeoh.attribute10
, oeoh.attribute11
, oeoh.attribute12
, oeoh.attribute13
, oeoh.attribute14
, oeoh.attribute15
, oeoh.global_attribute1
, oeoh.global_attribute2
, oeoh.global_attribute3
, oeoh.global_attribute4
, oeoh.global_attribute5
, oeoh.global_attribute6
, oeoh.global_attribute7
, oeoh.global_attribute8
, oeoh.global_attribute9
, oeoh.global_attribute10
, oeoh.global_attribute11
, oeoh.global_attribute12
, oeoh.global_attribute13
, oeoh.global_attribute14
, oeoh.global_attribute15
, oeoh.global_attribute16
, oeoh.global_attribute17
, oeoh.global_attribute18
, oeoh.global_attribute19
, oeoh.global_attribute20
, oeoh.tp_attribute1
, oeoh.tp_attribute2
, oeoh.tp_attribute3
, oeoh.tp_attribute4
, oeoh.tp_attribute5
, oeoh.tp_attribute6
, oeoh.tp_attribute7
, oeoh.tp_attribute8
, oeoh.tp_attribute9
, oeoh.tp_attribute10
, oeoh.tp_attribute11
, oeoh.tp_attribute12
, oeoh.tp_attribute13
, oeoh.tp_attribute14
, oeoh.tp_attribute15
, oeoh.sales_channel_code
, oeoh.shipping_instructions
, oeoh.packing_instructions
FROM oe_order_headers_all oeoh
WHERE oeoh.header_id = p_header_id;
SELECT FM_SERIAL_NUMBER
, TO_SERIAL_NUMBER
FROM wsh_serial_numbers
WHERE delivery_detail_id = p_delivery_detail_id
UNION
SELECT msnt.FM_SERIAL_NUMBER
, msnt.TO_SERIAL_NUMBER
FROM mtl_serial_numbers_temp msnt
, wsh_delivery_details wdd
WHERE msnt.transaction_temp_id = wdd.transaction_temp_id
AND wdd.delivery_detail_id = p_delivery_detail_id
UNION
SELECT serial_number FM_SERIAL_NUMBER
, serial_number TO_SERIAL_NUMBER
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id
AND serial_number IS NOT NULL ;
l_selected_fields INV_LABEL.label_field_variable_tbl_type;
l_selected_fields_count NUMBER;
l_wdd_tb.delete;
SELECT lpn_id INTO l_outer_lpn_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_transaction_id;
SELECT organization_id
INTO l_organization_id
FROM wsh_new_deliveries
WHERE delivery_id = l_delivery_id;
l_wdd_tb.delete;
SELECT Count(*)
INTO l_serial_check
FROM wms_label_field_variables lbvar
, wms_label_fields_vl lbfl
WHERE lbfl.label_field_id = lbvar.label_field_id
AND lbvar.label_format_id = p_label_type_info.default_format_id
AND column_name = 'serial_number';
SELECT wdd.delivery_detail_id
bulk collect INTO l_wdd_id_list
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda2,
(SELECT wsh1.inventory_item_id item,
wsh1.lot_number lot,
wsh1.revision rev,
wsh1.lpn_id lpn_id
FROM wsh_delivery_details wsh1, wsh_delivery_assignments wda
WHERE EXISTS
(SELECT 'Y' FROM wsh_delivery_details wsh2,wsh_delivery_assignments wda1
WHERE wsh1.inventory_item_id = wsh2.inventory_item_id
AND Nvl(wsh1.lot_number,'@@@')=Nvl(wsh2.lot_number,'@@@')
AND Nvl(wsh1.revision,'@@@')=Nvl(wsh2.revision,'@@@')
AND Nvl(wsh1.lpn_id,-999)=Nvl(wsh2.lpn_id,-999)
AND wsh1.ROWID <> wsh2.ROWID
AND wda1.delivery_detail_id = wsh2.delivery_detail_id
AND wda1.delivery_id = l_delivery_id)
AND wda.delivery_detail_id = wsh1.delivery_detail_id
AND wda.delivery_id = l_delivery_id
AND wsh1.delivery_detail_id = l_cur_wdd.delivery_detail_id) wsh_filter
WHERE wdd.inventory_item_id = wsh_filter.item
AND Nvl(wdd.lot_number,'@@@') = Nvl(wsh_filter.lot,'@@@')
AND Nvl(wdd.revision,'@@@') = Nvl(wsh_filter.rev,'@@@')
AND Nvl(wdd.lpn_id,-999)=Nvl(wsh_filter.lpn_id,-999)
AND wdd.delivery_detail_id = wda2.delivery_detail_id
AND wda2.delivery_id = l_delivery_id;
SELECT wdd.delivery_detail_id
bulk collect INTO l_wdd_id_list
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda2,
wsh_delivery_details wdd1,
(SELECT wsh1.inventory_item_id item,
wsh1.lot_number lot,
wsh1.revision rev,
wlpn2.lpn_id lpn_id
FROM wsh_delivery_details wsh1, wsh_delivery_assignments wda, wsh_delivery_details wsh2 ,
wms_license_plate_numbers wlpn,wms_license_plate_numbers wlpn2
WHERE wlpn.lpn_id = wsh2.lpn_id
AND wlpn.outermost_lpn_id=wlpn2.outermost_lpn_id
AND wda.delivery_detail_id = wsh1.delivery_detail_id
AND wda.parent_delivery_detail_id = wsh2.delivery_detail_id
AND wda.delivery_id = l_delivery_id
AND wsh1.delivery_detail_id = l_cur_wdd.delivery_detail_id
and exists(select 1 from wsh_delivery_details wdd4, wsh_delivery_assignments wda3
where wdd4.lpn_id=wlpn2.lpn_id
and wda3.delivery_detail_id = wdd4.delivery_detail_id
and wda3.delivery_id=l_delivery_id)) wsh_filter
WHERE wdd.inventory_item_id = wsh_filter.item
AND Nvl(wdd.lot_number,'@@@') = Nvl(wsh_filter.lot,'@@@')
AND Nvl(wdd.revision,'@@@') = Nvl(wsh_filter.rev,'@@@')
AND Nvl(wdd1.lpn_id,-999)=Nvl(wsh_filter.lpn_id,-999)
AND wdd.delivery_detail_id = wda2.delivery_detail_id
AND wdd1.delivery_detail_id=wda2.parent_delivery_detail_id
AND wda2.delivery_id =l_delivery_id;
SELECT count(lpn_id)
INTO g_column_elements_table(get_column_hash_value('box_count')).column_content
FROM wms_license_plate_numbers
WHERE parent_lpn_id IS NOT NULL
AND parent_lpn_id = l_cur_wdd.outer_lpn_id
AND outermost_lpn_id = l_cur_wdd.outer_lpn_id;
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_business_flow_code => p_label_type_info.business_flow_code,
p_customer_item_id => l_cur_wdd.customer_item_id,
p_sales_order_header_id => l_cur_wdd.source_header_id,
p_sales_order_line_id => l_cur_wdd.source_line_id,
p_use_rule_engine => 'Y',
x_return_status =>l_return_status,
x_label_format_id =>l_label_format_id,
x_label_format =>l_label_format,
x_label_request_id =>l_label_request_id);
trace(' Getting selected fields ');
x_variables => l_selected_fields
, x_variables_count => l_selected_fields_count
, p_format_id => l_label_format_id);
IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
IF (l_debug = 1) THEN
trace('no fields defined for this format: ' || l_label_format_id || ',' || l_label_format);
SELECT min(a.count)
INTO no_of_rows_per_label
FROM (SELECT wlfv.label_field_id,
count(*) count
FROM wms_label_field_variables wlfv
WHERE wlfv.label_format_id = l_label_format_id
GROUP BY wlfv.label_field_id
HAVING count(*) > 1) a;
SELECT max(a.count)
INTO max_no_of_rows_defined
FROM (SELECT wlfv.label_field_id,
count(*) count
FROM wms_label_field_variables wlfv
WHERE wlfv.label_format_id = l_label_format_id
GROUP BY wlfv.label_field_id
HAVING count(*) > 1) a;
trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
SELECT count(1) INTO l_multi_format
FROM DUAL
WHERE EXISTS (SELECT wlfv.label_field_id, count(*) count
FROM wms_label_field_variables wlfv
WHERE wlfv.label_format_id = l_label_format_id
GROUP BY wlfv.label_field_id
HAVING count(*) > 1) ;
SELECT COUNT(1) INTO l_wdd_already_printed
FROM WMS_LABEL_REQUESTS WLR
WHERE WLR.ORGANIZATION_ID = l_cur_wdd.organization_id
AND WLR.LPN_ID = l_cur_wdd.outer_lpn_id
AND WLR.DELIVERY_ID = l_cur_wdd.delivery_id
AND WLR.INVENTORY_ITEM_ID = l_cur_wdd.inventory_item_id
AND WLR.BUSINESS_FLOW_CODE = p_label_type_info.business_flow_code
AND WLR.LABEL_FORMAT_ID = l_label_format_id
AND WLR.DOCUMENT_ID = 8
AND WLR.LABEL_REQUEST_ID <> l_label_request_id; --exclude the one inserted for the current request
DELETE FROM WMS_LABEL_REQUESTS WLR WHERE WLR.LABEL_REQUEST_ID = l_label_request_id;
UPDATE wms_label_requests
SET printer_name = l_printer
WHERE label_request_id = l_label_request_id;
/* Loop for each selected fields, find the columns and write into the XML_content*/
OPEN c_fields_for_format (l_label_format_id);
FOR j IN 1..l_selected_fields.count LOOP
IF l_selected_fields(j).column_name <>'sql_stmt' AND -- Added for Bug#9790692
(j=1 OR l_selected_fields(j).column_name <> l_selected_fields(j-1).column_name) THEN
l_variable_name := get_variable_name(l_selected_fields(j).column_name,
i-1, l_label_format_id);
FOR j IN 1..l_selected_fields.count LOOP
IF l_selected_fields(j).column_name <>'sql_stmt' AND -- Added for Bug#9790692
(j=1 OR l_selected_fields(j).column_name <> l_selected_fields(j-1).column_name) THEN
l_variable_name := get_variable_name(l_selected_fields(j).column_name,
i-1, l_label_format_id);