The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into amintemp1 VALUES (b || a);
query_str := 'SELECT mp.organization_id, mp.organization_code ';
query_str := query_str || '(select organization_id ';
query_str := ' SELECT mp.organization_id, mp.organization_code FROM mtl_parameters mp ';
query_str := query_str || 'SELECT organization_id from ' || table_required;
|| ' SELECT organization_id from'
|| ' (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ' ) mln, '
|| table_required;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || ' SELECT organization_id from ' || table_required;
|| ' SELECT organization_id from'
|| ' (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
|| ' SELECT organization_id from'
|| ' (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ' ) mln, '
|| table_required;
|| ' SELECT organization_id from'
|| ' (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ' ) mln, '
|| ' (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ' ) msn, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || 'SELECT organization_id from ' || table_required;
|| 'SELECT organization_id from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT organization_id from ' || table_required;
|| 'SELECT organization_id from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || ' SELECT organization_id from ' || table_required;
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := 'SELECT mms.status_id, mms.status_code ';
SELECT serial_number_control_code
INTO serial_control
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
query_str := query_str || 'SELECT organization_id from mtl_onhand_serial_v mos ';
|| 'SELECT organization_id from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, mtl_onhand_serial_v mos ';
|| 'SELECT organization_id from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, mtl_onhand_serial_v mos ';
|| 'SELECT organization_id from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, mtl_onhand_serial_v mos ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND EXISTS ( SELECT 1 ' ;
query_str := query_str || 'SELECT organization_id from mtl_onhand_total_v mot ';
|| 'SELECT organization_id from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, mtl_onhand_total_v mot ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND EXISTS ( SELECT 1 ' ;
query_str := query_str || 'SELECT organization_id from mtl_onhand_serial_v mos ';
|| 'SELECT organization_id from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, mtl_onhand_serial_v mos ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || 'SELECT organization_id from ' || table_required;
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := 'select subinventory_code ';
query_str := 'SELECT msi.secondary_inventory_name subinventory_code FROM mtl_secondary_inventories msi ';
query_str := query_str || ' SELECT subinventory_code from ' || table_required;
|| ' SELECT subinventory_code from '
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT subinventory_code from ' || table_required;
|| 'SELECT subinventory_code from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT subinventory_code from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT subinventory_code from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT subinventory_code from ' || table_required;
|| 'SELECT subinventory_code from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT subinventory_code from ' || table_required;
|| 'SELECT subinventory_code from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || 'SELECT subinventory_code from ' || table_required;
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
SELECT stock_locator_control_code
INTO stock_loc_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT locator_type
INTO loc_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_organization_id;
query_str := 'SELECT wlpn.locator_id, mil.concatenated_segments ';
query_str := query_str || 'SELECT locator_id, locator from ' || table_required;
|| 'SELECT locator_id, locator from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT locator_id, locator from ' || table_required;
|| 'SELECT locator_id, locator from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT locator_id, locator from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT locator_id, locator from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
query_str := 'SELECT locator_id, locator from (';
query_str := query_str || 'SELECT locator_id, locator from ' || table_required;
|| 'SELECT locator_id, locator from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT locator_id, locator from ' || table_required;
|| 'SELECT locator_id, locator from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := 'SELECT locator_id, locator from ' || table_required;
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := 'SELECT DISTINCT mot.cost_group_id, ccg.cost_group ';
|| 'SELECT mot.cost_group_id, ccg.cost_group from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, cst_cost_groups ccg, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := 'SELECT DISTINCT mos.cost_group_id, ccg.cost_group ';
|| 'SELECT DISTINCT mos.cost_group_id, ccg.cost_group from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, cst_cost_groups ccg, '
|| table_required;
|| 'SELECT DISTINCT mos.cost_group_id, ccg.cost_group from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, cst_cost_groups ccg, '
|| table_required;
|| 'SELECT DISTINCT mos.cost_group_id, ccg.cost_group from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, cst_cost_groups ccg, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := 'SELECT DISTINCT cost_group_id, cost_group from (';
query_str := query_str || 'SELECT mot.cost_group_id, ccg.cost_group ';
|| 'SELECT mot.cost_group_id, ccg.cost_group FROM '
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, cst_cost_groups ccg, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT mos.cost_group_id, ccg.cost_group ';
|| 'SELECT mos.cost_group_id, ccg.cost_group from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, cst_cost_groups ccg, mtl_onhand_serial_v mos ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := 'SELECT DISTINCT mol.cost_group_id, ccg.cost_group ';
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
, p_inserted_under_org IN VARCHAR2 DEFAULT 'N'
--ER(3338592) Changes
, p_item_description IN VARCHAR2 DEFAULT NULL
--ER(3338592) Changes
, x_node_value IN OUT NOCOPY NUMBER
, x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
, x_tbl_index IN OUT NOCOPY NUMBER
-- NSRIVAST, INVCONV, Start
, p_grade_from IN VARCHAR2 DEFAULT NULL
, p_grade_code IN VARCHAR2 DEFAULT NULL
, p_grade_controlled IN NUMBER DEFAULT 0
-- NSRIVAST, INVCONV, End
) IS
query_str VARCHAR2(10000);
query_str := 'SELECT license_plate_number lpn, lpn_id, inventory_item_id ';
query_str := query_str || ' (select outermost_lpn_id ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND mol.subinventory_code is null AND mol.locator_id is null ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
query_str := 'SELECT license_plate_number lpn, lpn_id, inventory_item_id ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND mol.subinventory_code is null AND mol.locator_id is null ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
query_str := 'SELECT license_plate_number lpn, lpn_id, inventory_item_id ';
query_str := query_str || ' (select MOL.outermost_lpn_id ';
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND wlpn.subinventory_code is null AND wlpn.locator_id is null ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND subinventory_code is null AND locator_id is null ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
query_str := query_str || ' select license_plate_number lpn, lpn_id, inventory_item_id from wms_license_plate_numbers ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND subinventory_code is null AND locator_id is null ';
SELECT concatenated_segments
INTO item
FROM mtl_system_items_kfv
WHERE organization_id = p_organization_id
AND inventory_item_id = item_id;
, p_inserted_under_org IN VARCHAR2 DEFAULT 'N'
--ER(3338592) Changes
, p_item_description IN VARCHAR2 DEFAULT NULL
--ER(3338592) Changes
, p_responsibility_id IN NUMBER DEFAULT NULL --Bug # 3411938
, p_resp_application_id IN NUMBER DEFAULT NULL
, p_qty_from IN NUMBER DEFAULT NULL --Bug # 3539766
, p_qty_to IN NUMBER DEFAULT NULL
, x_node_value IN OUT NOCOPY NUMBER
, x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
, x_tbl_index IN OUT NOCOPY NUMBER
-- NSRIVAST, INVCONV, Start
, p_grade_from IN VARCHAR2 DEFAULT NULL
, p_grade_code IN VARCHAR2 DEFAULT NULL
, p_grade_controlled IN NUMBER DEFAULT 0
-- NSRIVAST, INVCONV, End
) IS
query_str VARCHAR2(10000);
query_str := query_str || 'SELECT DISTINCT inventory_item_id, item from ' || table_required;
|| 'SELECT DISTINCT inventory_item_id, item from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND subinventory_code is null AND locator_id is null ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || 'SELECT DISTINCT inventory_item_id, item from ' || table_required;
|| 'SELECT DISTINCT inventory_item_id, item from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT DISTINCT inventory_item_id, item from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT DISTINCT inventory_item_id, item from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND subinventory_code is null AND locator_id is null ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := 'SELECT DISTINCT inventory_item_id, item from( ';
query_str := query_str || 'SELECT inventory_item_id, item from ' || table_required;
|| 'SELECT inventory_item_id, item from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, mtl_onhand_total_v ';
|| ' ( select null from mtl_system_items msi WHERE ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || 'SELECT inventory_item_id, item from mtl_onhand_serial_v mos ';
|| 'SELECT inventory_item_id, item from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, mtl_onhand_serial_v ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := 'SELECT DISTINCT inventory_item_id, item ';
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND wlpn.subinventory_code is null AND wlpn.locator_id is null ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
query_str := query_str || ' AND mol.subinventory_code is null AND mol.locator_id is null ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
SELECT revision_qty_control_code
INTO rev_control
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
query_str := query_str || 'SELECT item, revision from ' || table_required;
|| 'SELECT item, revision from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT item, revision from mtl_onhand_serial_v ';
|| 'SELECT item, revision from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, mtl_onhand_serial_v ';
|| 'SELECT item, revision from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, mtl_onhand_serial_v ';
|| 'SELECT item, revision from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, mtl_onhand_serial_v ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := 'SELECT item, revision from( ';
query_str := query_str || 'SELECT item, revision from ' || table_required;
|| 'SELECT item, revision from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT item, revision from ' || table_required;
|| 'SELECT item, revision from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, mtl_onhand_serial_v ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := 'SELECT item, revision ';
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
SELECT lot_control_code
INTO lot_control
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
query_str := query_str || 'SELECT lot_number from ' || table_required;
|| 'SELECT lot_number from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT lot_number from ' || table_required;
|| 'SELECT lot_number from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT lot_number from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT lot_number from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := 'SELECT lot_number from( ';
query_str := query_str || 'SELECT lot_number from ' || table_required;
|| 'SELECT lot_number from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
|| ' ( select null from mtl_system_items msi WHERE '
|| ' moq.organization_id = msi.organization_id and '
|| ' moq.inventory_item_id = msi.inventory_item_id and '
|| ' item_serial_control in (1,6) ) ';
query_str := query_str || 'SELECT lot_number from ' || table_required;
|| 'SELECT lot_number from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| table_required;
query_str := 'SELECT lot_number ';
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
|| ' ( select null from mtl_system_items msi WHERE '
|| ' mol.organization_id = msi.organization_id and '
|| ' mol.inventory_item_id = msi.inventory_item_id and '
|| ' item_serial_control in (1,6) ) ';
|| ' ( select null from mtl_system_items msi WHERE '
|| ' mol.organization_id = msi.organization_id and '
|| ' mol.inventory_item_id = msi.inventory_item_id and '
|| ' item_serial_control in (2,5) ) ';
SELECT serial_number_control_code
INTO serial_control
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
query_str := query_str || 'SELECT serial_number from ' || table_required;
|| 'SELECT serial_number from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT serial_number from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, mtl_onhand_serial_v ';
|| 'SELECT serial_number from'
|| '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn, mtl_onhand_serial_v ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := 'SELECT serial_number ';
query_str := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
|| p_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| p_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
SELECT DISTINCT grade_control_flag
INTO grade_control
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id;
query_str := query_str || ' SELECT grade_code from ' || table_required;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || ' SELECT grade_code from ' || table_required;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || ' SELECT grade_code from ' || table_required;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
SELECT fdfcu.form_left_prompt
, fdfcu.application_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_application_vl fa
WHERE fdfcu.application_id = fa.application_id
AND fa.application_short_name = 'INV'
AND fdfcu.descriptive_flexfield_name = 'MTL_LOT_NUMBERS'
AND(
fdfcu.descriptive_flex_context_code IN(
SELECT fdfc.descriptive_flex_context_code
FROM fnd_descr_flex_contexts_vl fdfc
WHERE fdfc.global_flag = 'Y'
AND fdfc.descriptive_flexfield_name = 'MTL_LOT_NUMBERS'
AND fdfc.application_id = fa.application_id)
OR fdfcu.descriptive_flex_context_code = p_mln_context_code
)
AND fdfcu.enabled_flag = 'Y'
ORDER BY fdfcu.column_seq_num;
SELECT fdfcu.form_left_prompt
, fdfcu.application_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_application_vl fa
WHERE fdfcu.application_id = fa.application_id
AND fa.application_short_name = 'INV'
AND fdfcu.descriptive_flexfield_name = 'MTL_LOT_NUMBERS'
AND(
fdfcu.descriptive_flex_context_code IN(
SELECT fdfc.descriptive_flex_context_code
FROM fnd_descr_flex_contexts_vl fdfc
WHERE fdfc.global_flag = 'Y'
AND fdfc.descriptive_flexfield_name = 'MTL_LOT_NUMBERS'
AND fdfc.application_id = fa.application_id)
OR fdfcu.descriptive_flex_context_code = p_mln_context_code
)
AND fdfcu.enabled_flag = 'Y'
ORDER BY fdfcu.column_seq_num;
SELECT attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
INTO l_attribute(1)
, l_attribute(2)
, l_attribute(3)
, l_attribute(4)
, l_attribute(5)
, l_attribute(6)
, l_attribute(7)
, l_attribute(8)
, l_attribute(9)
, l_attribute(10)
, l_attribute(11)
, l_attribute(12)
, l_attribute(13)
, l_attribute(14)
, l_attribute(15)
, l_mln_context_code
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT fdfcu.form_left_prompt
, fdfcu.application_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_application_vl fa
WHERE fdfcu.application_id = fa.application_id
AND fa.application_short_name = 'INV'
AND fdfcu.descriptive_flexfield_name = 'MTL_SERIAL_NUMBERS'
AND(
fdfcu.descriptive_flex_context_code IN(
SELECT fdfc.descriptive_flex_context_code
FROM fnd_descr_flex_contexts_vl fdfc
WHERE fdfc.global_flag = 'Y'
AND fdfc.descriptive_flexfield_name = 'MTL_SERIAL_NUMBERS'
AND fdfc.application_id = fa.application_id)
OR fdfcu.descriptive_flex_context_code = p_msn_context_code
)
AND fdfcu.enabled_flag = 'Y'
ORDER BY fdfcu.column_seq_num;
SELECT fdfcu.form_left_prompt
, fdfcu.application_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_application_vl fa
WHERE fdfcu.application_id = fa.application_id
AND fa.application_short_name = 'INV'
AND fdfcu.descriptive_flexfield_name = 'MTL_SERIAL_NUMBERS'
AND(
fdfcu.descriptive_flex_context_code IN(
SELECT fdfc.descriptive_flex_context_code
FROM fnd_descr_flex_contexts_vl fdfc
WHERE fdfc.global_flag = 'Y'
AND fdfc.descriptive_flexfield_name = 'MTL_SERIAL_NUMBERS'
AND fdfc.application_id = fa.application_id)
OR fdfcu.descriptive_flex_context_code = p_msn_context_code
)
AND fdfcu.enabled_flag = 'Y'
ORDER BY fdfcu.column_seq_num;
SELECT attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
INTO l_attribute(1)
, l_attribute(2)
, l_attribute(3)
, l_attribute(4)
, l_attribute(5)
, l_attribute(6)
, l_attribute(7)
, l_attribute(8)
, l_attribute(9)
, l_attribute(10)
, l_attribute(11)
, l_attribute(12)
, l_attribute(13)
, l_attribute(14)
, l_attribute(15)
, l_msn_context_code
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number = p_serial_number;