[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select_str varchar2(10000);
query_str := ' SELECT distinct pha.segment1, pha.po_header_id
FROM po_headers_trx_v pha
, po_lines_trx_v pla
, mtl_supply ms
, mtl_system_items_kfv msik
WHERE pha.po_header_id = ms.po_header_id
AND pla.po_line_id = ms.po_line_id
AND pha.authorization_status = ''APPROVED''
AND ms.destination_type_code = ''INVENTORY''
AND ms.item_id = msik.inventory_item_id
AND ms.to_organization_id = msik.organization_id
-- AND ms.supply_type_code IN (''PO'') --15991963
AND pha.segment1 IS NOT NULL';
query_str := query_str || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_org_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
select_str := ' SELECT distinct rsh.shipment_num, rsh.shipment_header_id FROM mtl_supply ms
, rcv_shipment_lines rsl
, rcv_shipment_headers rsh ';
select_str := select_str || ' , wms_license_plate_numbers wlpn ';
where_str := where_str || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
select_str := select_str || ', mtl_system_items_kfv msik ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_org_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
select_str := select_str || ', rcv_serials_supply rss ';
select_str := select_str || ', rcv_lots_supply rls ';
select_str := select_str || ', mtl_lot_numbers mln1 ';
select_str := select_str || ', rcv_lots_supply rls ';
query_str := select_str || where_str;
select_str := ' SELECT distinct rsh.shipment_num, rsh.shipment_header_id FROM mtl_supply ms
, rcv_shipment_lines rsl
, rcv_shipment_headers rsh ';
select_str := select_str || ' , wms_license_plate_numbers wlpn ';
where_str := where_str || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
select_str := select_str || ', mtl_system_items_kfv msik ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_org_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
select_str := select_str || ', rcv_serials_supply rss ';
select_str := select_str || ', rcv_lots_supply rls ';
select_str := select_str || ', mtl_lot_numbers mln1 ';
select_str := select_str || ', rcv_lots_supply rls ';
query_str := select_str || where_str;
/* delete from rtest2;
insert into rtest2 values (query_str);
select_str := ' SELECT distinct prha.segment1, prha.requisition_header_id
FROM mtl_supply ms
, po_req_headers_trx_v prha
, po_req_lines_trx_v prla ';
where_str := where_str || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
select_str := select_str || ', mtl_system_items_kfv msik ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_org_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
select_str := select_str || ', rcv_serials_supply rss ';
select_str := select_str || ', rcv_lots_supply rls ';
select_str := select_str || ', mtl_lot_numbers mln1 ';
select_str := select_str || ', rcv_lots_supply rls ';
query_str := select_str || where_str;
/* delete from rtest2;
insert into rtest2 values (query_str);
inb_select_clause VARCHAR2(500) := ' SELECT DISTINCT mp.organization_code , ms.to_organization_id ';
SELECT sum(inbound)
,sum(onhand)
,sum(receiving)
INTO l_inbound_qty
,l_onhand_qty
,l_rcv_qty
FROM mtl_mwb_gtmp;
inb_where_clause := inb_where_clause || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_organization_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
inb_where_clause := inb_where_clause || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_organization_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
inb_where_clause := inb_where_clause || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_organization_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
inb_query_str := inb_select_clause || inb_from_clause || inb_where_clause || inb_orderby_clause;
/* delete from rtest2;
insert into rtest2 values (inb_query_str);
END IF; -- Inbound checkbox selected
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 v.organization_id from ' || table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' --BUG 7556505 -- Bug 8396954
|| inv_mwb_globals.g_tree_lot_attr_query
|| ' ) mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id))' ;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || ' SELECT v.organization_id from ' || table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' --BUG 7556505 -- Bug 8396954
|| inv_mwb_globals.g_tree_lot_attr_query
|| ' ) mln, '
|| table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' --BUG 7556505 -- Bug 8396954
|| inv_mwb_globals.g_tree_lot_attr_query
|| ' ) mln, '
|| ' (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ' ) msn, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 v.organization_id from ' || table_required;
|| 'SELECT v.organization_id from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' --BUG 7556505, BUG 13970892
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT v.organization_id from ' || table_required;
|| 'SELECT v.organization_id from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id))' ;
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id))' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
SELECT mp.organization_id, mp.organization_code
INTO org_id,org_code
FROM mtl_parameters mp
WHERE EXISTS (SELECT organization_id
FROM mtl_onhand_total_mwb_v
WHERE 1 = 1 AND organization_id = mp.organization_id)
AND mp.organization_id = inv_mwb_globals.g_organization_id
ORDER BY organization_code;
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 v.organization_id from ' || table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ' ) mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || ' SELECT v.organization_id from ' || table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' --BUG 7556505
|| inv_mwb_globals.g_tree_lot_attr_query
|| ' ) mln, '
|| table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' --BUG 7556505
|| inv_mwb_globals.g_tree_lot_attr_query
|| ' ) mln, '
|| ' (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ' ) msn, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 v.organization_id from ' || table_required;
|| ' SELECT v.organization_id from'
|| ' (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' --BUG 7556505
|| inv_mwb_globals.g_tree_lot_attr_query
|| ' ) mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT v.organization_id from ' || table_required;
|| 'SELECT v.organization_id from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
SELECT mp.organization_id, mp.organization_code
INTO org_id,org_code
FROM mtl_parameters mp
WHERE EXISTS (SELECT organization_id
FROM mtl_onhand_total_mwb_v
WHERE 1 = 1 AND organization_id = mp.organization_id)
AND mp.organization_id = inv_mwb_globals.g_organization_id
ORDER BY organization_code;
query_str := 'SELECT mms.status_id, mms.status_code ';
SELECT serial_number_control_code
INTO serial_control
FROM mtl_system_items
WHERE organization_id = inv_mwb_globals.g_organization_id
AND inventory_item_id = inv_mwb_globals.g_inventory_item_id;
query_str := query_str || 'SELECT v.organization_id from mtl_onhand_serial_v v ';
|| 'SELECT v.organization_id from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, mtl_onhand_serial_v v ';
|| 'SELECT v.organization_id from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, mtl_onhand_serial_v v ';
|| 'SELECT v.organization_id from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, mtl_onhand_serial_v v ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 v.organization_id from mtl_onhand_total_v v ';
|| 'SELECT v.organization_id from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, mtl_onhand_total_v v ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 v.organization_id from mtl_onhand_serial_v v ';
|| 'SELECT v.organization_id from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, mtl_onhand_serial_v v ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := 'select wlpn.subinventory_code ';
query_str := 'SELECT msi.secondary_inventory_name subinventory_code FROM mtl_secondary_inventories msi ';
query_str := query_str || ' SELECT v.subinventory_code from ' || table_required;
|| ' SELECT v.subinventory_code from '
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || 'SELECT v.subinventory_code from ' || table_required;
|| 'SELECT v.subinventory_code from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT v.subinventory_code from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT v.subinventory_code from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || 'SELECT v.subinventory_code from ' || table_required;
|| 'SELECT v.subinventory_code from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || 'SELECT v.subinventory_code from ' || table_required;
|| 'SELECT v.subinventory_code from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
SELECT stock_locator_control_code
INTO stock_loc_code
FROM mtl_parameters
WHERE organization_id = NVL(inv_mwb_globals.g_tree_organization_id ,
inv_mwb_globals.g_organization_id);
SELECT locator_type
INTO loc_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = NVL(inv_mwb_globals.g_tree_subinventory_code,
inv_mwb_globals.g_subinventory_code)
AND organization_id = NVL(inv_mwb_globals.g_tree_organization_id,
inv_mwb_globals.g_organization_id);
query_str := 'SELECT wlpn.locator_id, mil.concatenated_segments ';
query_str := query_str || 'SELECT v.locator_id, v.locator from ' || table_required;
|| 'SELECT v.locator_id, v.locator from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || 'SELECT v.locator_id, v.locator from ' || table_required;
|| 'SELECT v.locator_id, v.locator from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT v.locator_id, v.locator from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT v.locator_id, v.locator from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := 'SELECT locator_id, locator from (';
query_str := query_str || 'SELECT v.locator_id locator_id, v.locator locator from ' || table_required;
|| 'SELECT v.locator_id locator_id, v.locator locator from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || 'SELECT v.locator_id locator_id , v.locator locator from ' || table_required;
|| 'SELECT v.locator_id locator_id, v.locator locator from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := 'SELECT DISTINCT v.cost_group_id , ccg.cost_group ';
|| 'SELECT v.cost_group_id , ccg.cost_group from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, cst_cost_groups ccg, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 v.cost_group_id, ccg.cost_group ';
|| 'SELECT DISTINCT v.cost_group_id, ccg.cost_group from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, cst_cost_groups ccg, '
|| table_required;
|| 'SELECT DISTINCT v.cost_group_id, ccg.cost_group from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, cst_cost_groups ccg, '
|| table_required;
|| 'SELECT DISTINCT v.cost_group_id, ccg.cost_group from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, cst_cost_groups ccg, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 v.cost_group_id cost_group_id, ccg.cost_group cost_group ';
|| 'SELECT v.cost_group_id cost_group_id, ccg.cost_group cost_group FROM '
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, cst_cost_groups ccg, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT v.cost_group_id, ccg.cost_group ';
|| 'SELECT v.cost_group_id, ccg.cost_group from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, cst_cost_groups ccg, mtl_onhand_serial_v v ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
/* , inv_mwb_globals.g_inserted_under_org IN VARCHAR2 DEFAULT 'N'*/
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
) IS
query_str VARCHAR2(8000);
select_from_str VARCHAR2(4000);
inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Entered'||inv_mwb_globals.g_inserted_under_org);
select_from_str :=
' SELECT DISTINCT wlpn.license_plate_number
, wlpn.lpn_id
FROM rcv_shipment_lines rsl
, mtl_system_items_kfv msik
, wms_license_plate_numbers wlpn ';
select_from_str := select_from_str || ' , rcv_serials_supply rss ';
select_from_str := select_from_str || ' , rcv_lots_supply rls ';
where_str := where_str || ' AND msik.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
select_from_str := select_from_str || ', mtl_lot_numbers mln1 ';
select_from_str := select_from_str || ' , rcv_lots_supply rls ';
query_str := select_from_str || where_str;
query_str := 'SELECT license_plate_number lpn, lpn_id, inventory_item_id ';
query_str := query_str || ' (select outermost_lpn_id ';
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
query_str := query_str || ' AND mol.subinventory_code is null AND mol.locator_id is null ';
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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 inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
query_str := query_str || ' AND mol.subinventory_code is null AND mol.locator_id is null ';
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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 inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
query_str := query_str || ' AND wlpn.subinventory_code is null AND wlpn.locator_id is null ';
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
query_str := query_str || ' AND subinventory_code is null AND locator_id is null ';
query_str := query_str || ' AND mol.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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 inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
query_str := query_str || ' AND subinventory_code is null AND locator_id is null ';
/*, inv_mwb_globals.g_inserted_under_org IN VARCHAR2 DEFAULT 'N'*/
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
) IS
query_str VARCHAR2(10000);
select_str VARCHAR2(10000) ;
inv_mwb_globals.g_inserted_under_org := 1;
query_str := ' SELECT distinct msik.concatenated_segments
, msik.inventory_item_id
FROM po_headers_trx_v pha
, po_lines_trx_v pla
, mtl_supply ms
, mtl_system_items_kfv msik
WHERE pha.po_header_id(+) = ms.po_header_id
AND pla.po_line_id(+) = ms.po_line_id
AND ms.destination_type_code = ''INVENTORY''
AND ms.item_id = msik.inventory_item_id
AND ms.to_organization_id = msik.organization_id
AND ms.supply_type_code IN (''PO'',''SHIPMENT'')
AND pha.segment1 IS NOT NULL';
query_str := query_str || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_organization_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
select_str := ' SELECT distinct msik.concatenated_segments
, msik.inventory_item_id
FROM mtl_supply ms
, rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, mtl_system_items_kfv msik ';
where_str := where_str || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_organization_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
select_str := select_str || ', rcv_serials_supply rss ';
select_str := select_str || ', rcv_lots_supply rss ';
select_str := select_str || ', mtl_lot_numbers mln1 ';
select_str := select_str || ' , rcv_lots_supply rls1 ';
query_str := select_str || where_str;
/* delete from rtest2;
insert into rtest2 values (query_str);
select_str := ' SELECT distinct msik.concatenated_segments
, msik.inventory_item_id
FROM mtl_supply ms
, rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, mtl_system_items_kfv msik ';
where_str := where_str || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_organization_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
select_str := select_str || ', rcv_serials_supply rss ';
select_str := select_str || ', rcv_lots_supply rls ';
select_str := select_str || ', mtl_lot_numbers mln1 ';
select_str := select_str || ' , rcv_lots_supply rls ';
query_str := select_str || where_str;
/* delete from rtest2;
insert into rtest2 values (query_str);
select_str := ' SELECT distinct msik.concatenated_segments
, msik.inventory_item_id
FROM mtl_supply ms
, po_req_headers_trx_v prha
, po_req_lines_trx_v prla
, mtl_system_items_kfv msik ';
where_str := where_str || ' AND ms.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :inb_client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :inb_to_organization_id '
|| ' and category_set_id = :inb_category_set_id '
|| ' and category_id = nvl(:inb_category_id, category_id)) ' ;
select_str := select_str || ', rcv_serials_supply rss ';
select_str := select_str || ', rcv_lots_supply rls ';
select_str := select_str || ', mtl_lot_numbers mln1 ';
select_str := select_str || ' , rcv_lots_supply rls ';
query_str := select_str || where_str;
/* delete from rtest2;
insert into rtest2 values (query_str);
query_str := query_str || 'SELECT DISTINCT v.inventory_item_id, v.item from ' || table_required;
|| 'SELECT DISTINCT v.inventory_item_id, v.item from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
query_str := query_str || ' AND v.subinventory_code is null AND v.locator_id is null ';
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || 'SELECT DISTINCT v.inventory_item_id, v.item from ' || table_required;
|| 'SELECT DISTINCT v.inventory_item_id, v.item from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT DISTINCT v.inventory_item_id, v.item from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT DISTINCT v.inventory_item_id, v.item from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
--don't add the below locator id not null check
NULL;
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_inserted_under_org = 'y' THEN
query_str := query_str || ' AND v.subinventory_code is null AND v.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 v.inventory_item_id inventory_item_id, v.item item from ' || table_required;
|| 'SELECT v.inventory_item_id inventory_item_id, v.item item from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, mtl_onhand_total_v v ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
|| ' ( select null from mtl_system_items msi WHERE ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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 v.inventory_item_id inventory_item_id, v.item item from mtl_onhand_serial_v v ';
|| 'SELECT v.inventory_item_id inventory_item_id, v.item item from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, mtl_onhand_serial_v v ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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 inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
IF inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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 inv_mwb_globals.g_inserted_under_org = 'Y' OR inv_mwb_globals.g_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_from_str VARCHAR2(1000);
SELECT revision_qty_control_code
INTO rev_control
FROM mtl_system_items
WHERE organization_id = NVL(inv_mwb_globals.g_tree_organization_id
, inv_mwb_globals.g_organization_id)
AND inventory_item_id = NVL(inv_mwb_globals.g_tree_item_id
, inv_mwb_globals.g_inventory_item_id);
select_from_str :=
' SELECT DISTINCT rsl.item_revision
, msik.concatenated_segments
, msik.inventory_item_id -- Bug 6350236
FROM rcv_shipment_lines rsl
, mtl_system_items_kfv msik ';
where_str := where_str || ' AND rsl.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
select_from_str := select_from_str || ' , rcv_serials_supply rss ';
select_from_str := select_from_str || ' , rcv_lots_supply rls ';
select_from_str := select_from_str || ', mtl_lot_numbers mln1 ';
select_from_str := select_from_str || ' , rcv_lots_supply rls1 ';
query_str := select_from_str || where_str;
SELECT revision_qty_control_code
INTO rev_control
FROM mtl_system_items
WHERE organization_id = inv_mwb_globals.g_tree_organization_id
AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
query_str := query_str || 'SELECT v.item, v.inventory_item_id, v.revision from ' || table_required; -- Bug 6350236
|| 'SELECT v.item, v.inventory_item_id, v.revision from' -- Bug 6350236
|| '(SELECT lot_number lot_num, parent_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT v.item, v.inventory_item_id, v.revision from ' || table_required; -- Bug 6350236
|| 'SELECT v.item, v.inventory_item_id, v.revision from' -- Bug 6350236
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT v.item, v.inventory_item_id, v.revision from' -- Bug 6350236
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT v.item, v.inventory_item_id, v.revision from' -- Bug 6350236
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := 'SELECT item, inventory_item_id, revision from( '; -- Bug 6350236
query_str := query_str || 'SELECT v.item item, v.inventory_item_id inventory_item_id, v.revision revision from ' || table_required; -- Bug 6350236
|| 'SELECT v.item item, v.inventory_item_id inventory_item_id, v.revision revision from' -- Bug 6350236
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || 'SELECT v.item item, v.inventory_item_id inventory_item_id, v.revision revision from ' || table_required; -- Bug 6350236
|| 'SELECT v.item item, v.inventory_item_id inventory_item_id, v.revision revision from' -- Bug 6350236
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := 'SELECT item, inventory_item_id, revision '; -- Bug 6350236
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
select_from_str VARCHAR2(2000);
SELECT lot_control_code
, serial_number_control_code
INTO lot_control
, serial_control
FROM mtl_system_items
WHERE organization_id = NVL(inv_mwb_globals.g_tree_organization_id
, inv_mwb_globals.g_organization_id)
AND inventory_item_id = NVL(inv_mwb_globals.g_inventory_item_id
, inv_mwb_globals.g_tree_item_id);
select_from_str :=
' SELECT DISTINCT rls.lot_num
FROM rcv_shipment_lines rsl
, mtl_supply ms ';
where_str := where_str || ' AND rsl.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
select_from_str := select_from_str || ' , rcv_serials_supply rls ';
select_from_str := select_from_str || ' , rcv_lots_supply rls ';
select_from_str := select_from_str || ', mtl_lot_numbers mln1 ';
select_from_str := select_from_str || ' , rcv_lots_supply rls1 ';
query_str := select_from_str || where_str;
SELECT lot_control_code
INTO lot_control
FROM mtl_system_items
WHERE organization_id = NVL(inv_mwb_globals.g_tree_organization_id,
inv_mwb_globals.g_organization_id)
AND inventory_item_id = NVL(inv_mwb_globals.g_tree_item_id,
inv_mwb_globals.g_inventory_item_id);
query_str := query_str || 'SELECT v.lot_number from ' || table_required;
|| 'SELECT v.lot_number from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || 'SELECT v.lot_number from ' || table_required;
|| 'SELECT v.lot_number from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT v.lot_number from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT v.lot_number from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := 'SELECT lot_number from( ';
query_str := query_str || 'SELECT v.lot_number lot_number from ' || table_required;
|| 'SELECT v.lot_number lot_number from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_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 '
|| ' v.organization_id = msi.organization_id and '
|| ' v.inventory_item_id = msi.inventory_item_id and '
|| ' item_serial_control in (1,6) ) ';
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || 'SELECT v.lot_number from ' || table_required;
|| 'SELECT v.lot_number from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_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) ) ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
select_from_str VARCHAR2(2000);
SELECT serial_number_control_code
INTO serial_control
FROM mtl_system_items
WHERE organization_id = NVL(inv_mwb_globals.g_tree_organization_id,
inv_mwb_globals.g_organization_id)
AND inventory_item_id = NVL(inv_mwb_globals.g_inventory_item_id,
inv_mwb_globals.g_tree_item_id);
select_from_str :=
' SELECT DISTINCT rss.serial_num
FROM rcv_shipment_lines rsl
, mtl_supply ms
, rcv_serials_supply rss';
where_str := where_str || ' AND rsl.item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
select_from_str := select_from_str || ', mtl_lot_numbers mln1 ';
query_str := select_from_str || where_str;
SELECT serial_number_control_code
INTO serial_control
FROM mtl_system_items
WHERE organization_id = NVL(inv_mwb_globals.g_tree_organization_id,
inv_mwb_globals.g_organization_id)
AND inventory_item_id = NVL(inv_mwb_globals.g_inventory_item_id,
inv_mwb_globals.g_tree_item_id);
query_str := query_str || 'SELECT v.serial_number serial_number from ' || table_required;
|| 'SELECT v.serial_number serial_number from'
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
|| 'SELECT v.serial_number serial_number from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| table_required;
|| 'SELECT v.serial_number serial_number from'
|| '(SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln, '
|| '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn, '
|| table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 ' || inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 ' || inv_mwb_globals.g_tree_lot_attr_query || ') mln ';
|| ', (SELECT lot_number lot_num, parent_lot_number, supplier_lot_number FROM mtl_lot_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_lot_attr_query
|| ') mln '
|| ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
|| inv_mwb_globals.g_tree_serial_attr_query
|| ') msn ';
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 = NVL(inv_mwb_globals.g_tree_item_id,
inv_mwb_globals.g_inventory_item_id)
AND organization_id = NVL(inv_mwb_globals.g_tree_organization_id,
inv_mwb_globals.g_organization_id);
query_str := query_str || ' SELECT v.grade_code grade_code from ' || table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || ' SELECT v.grade_code grade_code from ' || table_required;
query_str := query_str || ' AND v.inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
query_str := query_str || ' and EXISTS ( SELECT 1 ' ;
query_str := query_str || ' SELECT grade_code from ' || table_required;
query_str := query_str || ' AND inventory_item_id in (select DISTINCT inventory_item_id from mtl_system_items_b where wms_deploy.get_client_code(inventory_item_id) = :client_code) ';
|| ' (select DISTINCT inventory_item_id from mtl_item_categories '
|| ' where organization_id = :organization_id '
|| ' and category_set_id = :category_set_id '
|| ' and category_id = nvl(:category_id, category_id)) ' ;
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 = inv_mwb_globals.g_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 = inv_mwb_globals.g_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 = inv_mwb_globals.g_inventory_item_id
AND organization_id = inv_mwb_globals.g_organization_id
AND lot_number = inv_mwb_globals.g_tree_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 = inv_mwb_globals.g_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 = inv_mwb_globals.g_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 = inv_mwb_globals.g_inventory_item_id
AND current_organization_id = inv_mwb_globals.g_organization_id
AND serial_number = inv_mwb_globals.g_tree_serial_number;
SELECT organization_id into l_org_id
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND rownum = 1;