[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(MAX(unit_of_measure), 'notfound')
INTO x_unit_of_measure
FROM mtl_units_of_measure
WHERE unit_of_measure = x_uom_record.unit_of_measure;
SELECT primary_unit_of_measure
INTO x_primary_unit_of_measure
FROM mtl_system_items_kfv
WHERE inventory_item_id = x_uom_record.item_id
AND organization_id = NVL(x_uom_record.to_organization_id, organization_id); -- Raj added as org_id is part of uk
SELECT NVL(MAX(unit_meas_lookup_code), 'notfound')
INTO x_unit_meas_lookup_code_lines
FROM po_lines
WHERE po_line_id = x_uom_record.po_line_id;
SELECT NVL(MAX(enable_cum_flag), 'F')
INTO x_cum_enabled
FROM chv_org_options
WHERE organization_id = NVL(x_uom_record.to_organization_id, organization_id);
SELECT NVL(MAX(supply_agreement_flag), 'N')
INTO x_supply_agreement_flag
FROM po_headers
WHERE po_header_id = x_uom_record.po_header_id
AND type_lookup_code = 'BLANKET'
AND supply_agreement_flag = 'Y';
SELECT NVL(MAX(NULL), 'notfound') -- purchasing_unit_of_measure doesn't exist!!
INTO x_asl_uom
FROM chv_cum_period_items
WHERE organization_id = NVL(x_uom_record.to_organization_id, organization_id);
SELECT NVL(MAX(inventory_item_id), -9999)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_item_id_record.item_id;
SELECT NVL(MAX(inventory_item_id), -9999)
INTO x_inventory_item
FROM mtl_system_items
WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
AND inventory_item_id = x_item_id_record.item_id;
* check below. Similarly changed the select statement and the
* check for nvl(max(item_id),0).
*/
SELECT NVL(MAX(organization_id), -9999)
INTO x_organization_id
FROM mtl_system_items
WHERE inventory_item_id = x_item_id_record.item_id
AND organization_id = NVL(x_item_id_record.to_organization_id, organization_id);
SELECT NVL(MAX(item_id), -9999)
INTO x_item_id_po
FROM po_lines
WHERE po_line_id = x_item_id_record.po_line_id
AND item_id = x_item_id_record.item_id;
SELECT NVL(MAX(item_id), -9999)
INTO x_item_id_po
FROM po_lines
WHERE po_line_id = x_item_id_record.po_line_id
AND item_id = x_item_id_record.item_id;
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_sub_item_id_record.substitute_item_id
AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id);
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_system_items
WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
AND inventory_item_id = x_sub_item_id_record.substitute_item_id
AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id);
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_sub_item_id_record.substitute_item_id
AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id);
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_sub_item_id_record.substitute_item_id
AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id)
AND purchasing_item_flag = 'Y';
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_sub_item_id_record.substitute_item_id
AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id)
AND purchasing_enabled_flag = 'Y';
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_related_items
WHERE inventory_item_id = (SELECT item_id
FROM po_lines
WHERE po_line_id = x_sub_item_id_record.po_line_id)
AND related_item_id = x_sub_item_id_record.substitute_item_id
AND relationship_type_id = 2; -- substitute items
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_related_items
WHERE related_item_id = (SELECT item_id
FROM po_lines
WHERE po_line_id = x_sub_item_id_record.po_line_id)
AND inventory_item_id = x_sub_item_id_record.substitute_item_id
AND reciprocal_flag = 'Y'
AND relationship_type_id = 2;
SELECT DECODE(msi.revision_qty_control_code,
1, 'N',
2, 'Y',
'N'
)
INTO x_revision_control_flag
FROM mtl_system_items msi
WHERE inventory_item_id = x_item_revision_record.item_id
AND organization_id = x_item_revision_record.to_organization_id;
SELECT NVL(MAX(po_line_id), 0)
INTO x_inventory_item
FROM po_lines
WHERE po_line_id = x_item_revision_record.po_line_id
AND NVL(item_revision, x_item_revision_record.item_revision) = x_item_revision_record.item_revision;
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_item_revisions
WHERE inventory_item_id = x_item_revision_record.item_id
AND organization_id = NVL(x_item_revision_record.to_organization_id, organization_id)
AND revision = x_item_revision_record.item_revision;
SELECT NVL(MAX(inventory_item_id), 0) -- does this accurately check for active revisions??
INTO x_inventory_item
FROM mtl_item_revisions_org_val_v mir
WHERE mir.inventory_item_id = x_item_revision_record.item_id
AND mir.organization_id = NVL(x_item_revision_record.to_organization_id, mir.organization_id)
AND mir.revision = x_item_revision_record.item_revision;
SELECT NVL(MAX(po_line_id), 0)
INTO x_inventory_item
FROM po_lines
WHERE po_line_id = x_item_revision_record.po_line_id
AND NVL(item_revision, x_item_revision_record.item_revision) = x_item_revision_record.item_revision;
SELECT NVL(MAX(po_line_id), 0)
INTO x_po_line_id
FROM po_lines
WHERE po_line_id = x_ref_integrity_rec.po_line_id
AND vendor_product_num = x_ref_integrity_rec.vendor_item_num;
SELECT NVL(MAX(vendor_id), 0)
INTO x_po_vendor_id
FROM po_headers
WHERE po_header_id = x_ref_integrity_rec.po_header_id
AND vendor_id = x_ref_integrity_rec.vendor_id;
SELECT NVL(MAX(vendor_site_id), 0)
INTO x_po_vendor_site_id
FROM po_headers
WHERE po_header_id = x_ref_integrity_rec.po_header_id
AND vendor_site_id = x_ref_integrity_rec.vendor_site_id;
SELECT NVL(MAX(vendor_site_id), 0)
INTO x_po_vendor_site_id
FROM po_headers
WHERE po_header_id = x_ref_integrity_rec.po_header_id
AND revision_num = x_ref_integrity_rec.po_revision_num;
SELECT NVL(MAX(freight_code), 'notfound')
INTO x_freight_code
FROM org_freight_code_val_v
WHERE freight_code = x_freight_carrier_record.freight_carrier_code
AND organization_id = NVL(x_freight_carrier_record.to_organization_id, organization_id);
SELECT NVL(MAX(freight_code), 'notfound')
INTO x_freight_code
FROM org_freight
WHERE organization_id = NVL(x_freight_carrier_record.to_organization_id, organization_id)
AND freight_code = x_freight_carrier_record.freight_carrier_code
AND NVL(disable_date, SYSDATE + 1) > SYSDATE;
SELECT NVL(MAX(NAME), 'notfound')
INTO x_name
FROM ap_tax_codes
WHERE NAME = x_tax_name_record.tax_name;
SELECT NVL(MAX(NAME), 'notfound')
INTO x_name
FROM ap_tax_codes
WHERE NAME = x_tax_name_record.tax_name
AND NVL(inactive_date, SYSDATE + 1) > SYSDATE;
SELECT NVL(MAX(supply_agreement_flag), 'N')
INTO x_supply_agreement_flag
FROM po_headers
WHERE po_header_id = x_asl_record.po_header_id
AND type_lookup_code = 'BLANKET'
AND supply_agreement_flag = 'Y';
SELECT NVL(MAX('found'), 'notfound')
INTO x_success
FROM po_approved_supplier_lis_val_v
WHERE vendor_id = x_asl_record.vendor_id
AND vendor_site_id = x_asl_record.vendor_site_id
AND item_id = x_asl_record.item_id
AND ( using_organization_id = NVL(x_asl_record.to_organization_id, using_organization_id)
OR using_organization_id = -1); -- per discussion with cindy
x_rtv_update_cum_flag chv_org_options.rtv_update_cum_flag%TYPE;
SELECT NVL(MAX(supply_agreement_flag), 'N')
INTO x_supply_agreement_flag
FROM po_headers
WHERE po_header_id = x_cum_quantity_record.po_header_id
AND type_lookup_code = 'BLANKET'
AND supply_agreement_flag = 'Y';
SELECT MAX(enable_cum_flag)
INTO x_success
FROM chv_org_options
WHERE organization_id = NVL(x_cum_quantity_record.to_organization_id, organization_id);
SELECT NVL(MAX(rtv_update_cum_flag), 'N')
INTO x_rtv_update_cum_flag
FROM chv_org_options
WHERE organization_id = x_cum_quantity_record.to_organization_id;
asn_debug.put_line('RTV update cum flag ' || x_rtv_update_cum_flag);
IF (x_rtv_update_cum_flag = 'Y') THEN
BEGIN
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Org Id ' || TO_CHAR(x_cum_quantity_record.to_organization_id));
SELECT cum_period_start_date,
cum_period_end_date
INTO x_cum_period_start_date,
x_cum_period_end_date
FROM chv_cum_periods
WHERE organization_id = x_cum_quantity_record.to_organization_id
AND x_cum_quantity_record.transaction_date BETWEEN cum_period_start_date AND cum_period_end_date;
x_rtv_update_cum_flag,
x_cum_period_start_date,
x_cum_period_end_date,
x_cum_quantity_record.primary_unit_of_measure,
x_qty_received_primary,
x_qty_received_purchasing
);
SELECT NVL(MAX(pol.lookup_code), 'notfound')
INTO x_lookup_code
FROM po_lookup_codes pol
WHERE pol.lookup_code = x_po_lookup_code_record.lookup_code
AND pol.lookup_type = x_po_lookup_code_record.lookup_type;
SELECT NVL(MAX(secondary_inventory_name), 'notfound')
INTO x_subinventory
FROM mtl_secondary_inventories msub,
mtl_system_items msi
WHERE msub.secondary_inventory_name = x_subinventory_record.subinventory
AND msub.organization_id = x_subinventory_record.to_organization_id
AND x_subinventory_record.transaction_date < NVL(msub.disable_date, x_subinventory_record.transaction_date + 1)
AND msi.inventory_item_id = x_subinventory_record.item_id
AND msi.organization_id = x_subinventory_record.to_organization_id
AND ( msi.restrict_subinventories_code = 2
OR ( msi.restrict_subinventories_code = 1
AND EXISTS(SELECT NULL
FROM mtl_item_sub_inventories mis
WHERE mis.organization_id = x_subinventory_record.to_organization_id
AND mis.inventory_item_id = x_subinventory_record.item_id
AND mis.secondary_inventory = x_subinventory_record.subinventory)
)
);
* exists in hr_locations. Now select from hr_locations_all
*/
BEGIN
SELECT location_id
INTO x_location
FROM hr_locations_all hrl --1942696
WHERE ( hrl.inventory_organization_id = x_location_record.to_organization_id
OR NVL(hrl.inventory_organization_id, 0) = 0)
AND ( hrl.inactive_date IS NULL
OR hrl.inactive_date > SYSDATE)
AND (hrl.location_id = x_location_record.location_id);
SELECT location_id
INTO x_location
FROM hz_locations hz
WHERE ( hz.address_expiration_date IS NULL
OR hz.address_expiration_date > SYSDATE)
AND (hz.location_id = x_location_record.location_id);
SELECT NVL(MAX(hre.full_name), 'notfound')
INTO x_full_name
FROM hr_employees_current_v hre
WHERE ( hre.inactive_date IS NULL
OR hre.inactive_date > SYSDATE)
AND hre.employee_id = x_employee_record.employee_id;
SELECT NVL(MAX(ml.concatenated_segments), 'notfound')
INTO x_locator
FROM mtl_item_locations_kfv ml
WHERE ml.inventory_location_id = x_locator_record.locator_id
AND ( ml.disable_date > SYSDATE
OR ml.disable_date IS NULL)
AND NVL(ml.subinventory_code, 'z') = NVL(x_locator_record.subinventory, 'z')
AND ml.organization_id = x_locator_record.to_organization_id;
SELECT NVL(MAX(ml.concatenated_segments), 'notfound')
INTO x_locator
FROM mtl_item_locations_kfv ml
WHERE ml.inventory_location_id = x_locator_record.locator_id
AND ( ml.disable_date > SYSDATE
OR ml.disable_date IS NULL)
AND NVL(ml.subinventory_code, 'z') = NVL(x_locator_record.subinventory, 'z')
AND ml.inventory_location_id IN(SELECT secondary_locator
FROM mtl_secondary_locators msl
WHERE msl.inventory_item_id = x_locator_record.item_id
AND msl.organization_id = x_locator_record.to_organization_id
AND msl.subinventory_code = x_locator_record.subinventory);
SELECT NVL(PROJECT_REFERENCE_ENABLED, 0)
INTO V_PROJECT_ENABLED
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = X_LOCATOR_RECORD.TO_ORGANIZATION_ID;
SELECT PROJECT_ID, TASK_ID, ORG_ID -- BUG 13709880
INTO X_PROJECT_ID, X_TASK_ID, L_PJM_VALIDATION_OU_ID -- BUG 13709880
FROM PO_DISTRIBUTIONS_ALL
WHERE PO_DISTRIBUTION_ID = X_LOCATOR_RECORD.PO_DISTRIBUTION_ID;
SELECT NVL(MAX(territory_code), 'FF')
INTO x_code
FROM fnd_territories_vl
WHERE territory_code = x_country_of_origin_record.country_of_origin_code;
SELECT consigned_flag
INTO l_consigned_po_flag
FROM po_line_locations
WHERE line_location_id = x_consigned_po_rec.po_line_location_id;
SELECT consigned_consumption_flag
INTO l_consumption_po_flag
FROM po_headers
WHERE po_header_id = x_consumption_po_rec.po_header_id;
SELECT consigned_consumption_flag
INTO l_consumption_release_flag
FROM po_releases
WHERE po_release_id = x_consumption_release_rec.po_release_id;
SELECT LOT_NUMBER FROM mtl_transaction_lots_interface WHERE product_transaction_id=l_transaction_id;
SELECT transaction_quantity, LOT_NUMBER ,SECONDARY_TRANSACTION_QUANTITY FROM mtl_transaction_lots_interface WHERE product_transaction_id=l_transaction_id;
SELECT tracking_quantity_ind , secondary_default_ind
INTO l_TRACKING_QUANTITY_IND ,l_secondary_default_ind
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = x_att_rec.inventory_item_id
AND ORGANIZATION_ID = x_att_rec.to_organization_id;
SELECT SECONDARY_UOM_CODE
INTO l_secondary_uom_code
FROM MTL_SYSTEM_ITEMS_B
WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = x_att_rec.inventory_item_id
AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = x_att_rec.to_organization_id;
Select UNIT_OF_MEASURE
INTO x_att_rec.secondary_unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = x_att_rec.secondary_uom_code;
SELECT SECONDARY_UOM_CODE
INTO x_att_rec.secondary_uom_code
FROM MTL_SYSTEM_ITEMS_B
WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
SELECT UNIT_OF_MEASURE
INTO l_secondary_unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = x_att_rec.secondary_uom_code;
SELECT SECONDARY_UOM_CODE
INTO x_att_rec.secondary_uom_code
FROM MTL_SYSTEM_ITEMS_B
WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
Select UNIT_OF_MEASURE
INTO x_att_rec.secondary_unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = x_att_rec.secondary_uom_code;
SELECT SECONDARY_UOM_CODE
INTO l_secondary_uom_code
FROM MTL_SYSTEM_ITEMS_B
WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
SELECT UNIT_OF_MEASURE
INTO l_secondary_unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = x_att_rec.secondary_uom_code;
SELECT 1 INTO l_conv_exist
FROM mtl_lot_uom_class_conversions
WHERE organization_id = x_att_rec.to_organization_id
AND lot_number = lot_rec1.LOT_NUMBER
AND inventory_item_id = x_att_rec.inventory_item_id
AND FROM_UNIT_OF_MEASURE=X_ATT_REC.transaction_unit_of_measure
AND TO_UNIT_OF_MEASURE=x_att_rec.Secondary_UNIT_OF_MEASURE;