eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.INV_MWB_TREE dependencies on DBMS_SQL

Line 1367: query_hdl := DBMS_SQL.open_cursor;

1363: -- Enable this during debugging
1364: inv_trx_util_pub.trace(query_str, 'Add-Orgs - Material Workbench', 9);
1365: --trace1(query_str, 'add_orgs', 9);
1366:
1367: query_hdl := DBMS_SQL.open_cursor;
1368: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
1369:
1370: IF p_organization_id IS NOT NULL THEN
1371: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 1368: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

1364: inv_trx_util_pub.trace(query_str, 'Add-Orgs - Material Workbench', 9);
1365: --trace1(query_str, 'add_orgs', 9);
1366:
1367: query_hdl := DBMS_SQL.open_cursor;
1368: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
1369:
1370: IF p_organization_id IS NOT NULL THEN
1371: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
1372: END IF;

Line 1371: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

1367: query_hdl := DBMS_SQL.open_cursor;
1368: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
1369:
1370: IF p_organization_id IS NOT NULL THEN
1371: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
1372: END IF;
1373:
1374: IF p_subinventory_code IS NOT NULL THEN
1375: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 1375: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

1371: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
1372: END IF;
1373:
1374: IF p_subinventory_code IS NOT NULL THEN
1375: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
1376: END IF;
1377:
1378: IF p_locator_id IS NOT NULL THEN
1379: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 1379: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

1375: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
1376: END IF;
1377:
1378: IF p_locator_id IS NOT NULL THEN
1379: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
1380: END IF;
1381:
1382: IF p_inventory_item_id IS NOT NULL THEN
1383: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 1383: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

1379: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
1380: END IF;
1381:
1382: IF p_inventory_item_id IS NOT NULL THEN
1383: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
1384: END IF;
1385:
1386: IF p_revision IS NOT NULL THEN
1387: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 1387: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

1383: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
1384: END IF;
1385:
1386: IF p_revision IS NOT NULL THEN
1387: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
1388: END IF;
1389:
1390: IF p_cost_group_id IS NOT NULL THEN
1391: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 1391: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

1387: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
1388: END IF;
1389:
1390: IF p_cost_group_id IS NOT NULL THEN
1391: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
1392: END IF;
1393:
1394: IF p_lot_number_from IS NOT NULL THEN
1395: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 1395: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

1391: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
1392: END IF;
1393:
1394: IF p_lot_number_from IS NOT NULL THEN
1395: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
1396: END IF;
1397:
1398: IF p_lot_number_to IS NOT NULL THEN
1399: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 1399: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

1395: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
1396: END IF;
1397:
1398: IF p_lot_number_to IS NOT NULL THEN
1399: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
1400: END IF;
1401:
1402: -- NSRIVAST, INVCONV, Start
1403: IF p_grade_from IS NOT NULL THEN

Line 1404: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

1400: END IF;
1401:
1402: -- NSRIVAST, INVCONV, Start
1403: IF p_grade_from IS NOT NULL THEN
1404: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
1405: END IF;
1406: IF p_grade_code IS NOT NULL THEN
1407: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
1408: END IF;

Line 1407: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

1403: IF p_grade_from IS NOT NULL THEN
1404: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
1405: END IF;
1406: IF p_grade_code IS NOT NULL THEN
1407: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
1408: END IF;
1409: -- NSRIVAST, INVCONV, End
1410: IF p_serial_number_from IS NOT NULL THEN
1411: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

Line 1411: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

1407: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
1408: END IF;
1409: -- NSRIVAST, INVCONV, End
1410: IF p_serial_number_from IS NOT NULL THEN
1411: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
1412: END IF;
1413:
1414: IF p_serial_number_to IS NOT NULL THEN
1415: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 1415: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

1411: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
1412: END IF;
1413:
1414: IF p_serial_number_to IS NOT NULL THEN
1415: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
1416: END IF;
1417:
1418: --bugfix#3646484
1419: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 1422: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

1418: --bugfix#3646484
1419: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
1420: --User is querying for single LPN so converted the range query to equality query
1421: --So it is enought to bind the from lpn alone
1422: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
1423: ELSE
1424: IF p_lpn_from IS NOT NULL THEN
1425: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
1426: END IF;

Line 1425: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

1421: --So it is enought to bind the from lpn alone
1422: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
1423: ELSE
1424: IF p_lpn_from IS NOT NULL THEN
1425: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
1426: END IF;
1427:
1428: IF p_lpn_to IS NOT NULL THEN
1429: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 1429: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

1425: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
1426: END IF;
1427:
1428: IF p_lpn_to IS NOT NULL THEN
1429: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
1430: END IF;
1431: END IF;
1432:
1433: IF p_status_id IS NOT NULL THEN

Line 1434: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

1430: END IF;
1431: END IF;
1432:
1433: IF p_status_id IS NOT NULL THEN
1434: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
1435: END IF;
1436:
1437: IF p_prepacked <> 1
1438: AND p_prepacked <> 999

Line 1440: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

1436:
1437: IF p_prepacked <> 1
1438: AND p_prepacked <> 999
1439: AND p_prepacked IS NOT NULL THEN
1440: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
1441: END IF;
1442:
1443: IF p_mln_context_code IS NOT NULL THEN
1444: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 1444: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

1440: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
1441: END IF;
1442:
1443: IF p_mln_context_code IS NOT NULL THEN
1444: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
1445: END IF;
1446:
1447: IF p_project_id IS NOT NULL THEN
1448: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 1448: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

1444: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
1445: END IF;
1446:
1447: IF p_project_id IS NOT NULL THEN
1448: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
1449: END IF;
1450:
1451: IF p_task_id IS NOT NULL THEN
1452: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 1452: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

1448: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
1449: END IF;
1450:
1451: IF p_task_id IS NOT NULL THEN
1452: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
1453: END IF;
1454:
1455: IF p_unit_number IS NOT NULL THEN
1456: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 1456: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

1452: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
1453: END IF;
1454:
1455: IF p_unit_number IS NOT NULL THEN
1456: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
1457: END IF;
1458:
1459: IF (p_owning_qry_mode = 4)
1460: OR(p_owning_qry_mode = 3) THEN

Line 1461: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

1457: END IF;
1458:
1459: IF (p_owning_qry_mode = 4)
1460: OR(p_owning_qry_mode = 3) THEN
1461: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
1462: END IF;
1463:
1464: IF (p_planning_query_mode = 4)
1465: OR(p_planning_query_mode = 3) THEN

Line 1466: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

1462: END IF;
1463:
1464: IF (p_planning_query_mode = 4)
1465: OR(p_planning_query_mode = 3) THEN
1466: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
1467: END IF;
1468:
1469: --ER(3338592) Changes
1470: IF p_item_description IS NOT NULL THEN

Line 1471: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

1467: END IF;
1468:
1469: --ER(3338592) Changes
1470: IF p_item_description IS NOT NULL THEN
1471: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
1472: END IF;
1473:
1474: IF p_qty_from IS NOT NULL THEN
1475: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);

Line 1475: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);

1471: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
1472: END IF;
1473:
1474: IF p_qty_from IS NOT NULL THEN
1475: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);
1476: END IF;
1477:
1478: IF p_qty_to IS NOT NULL THEN
1479: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);

Line 1479: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);

1475: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);
1476: END IF;
1477:
1478: IF p_qty_to IS NOT NULL THEN
1479: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);
1480: END IF;
1481: --End of ER Changes
1482:
1483: --Bug #3411938

Line 1486: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );

1482:
1483: --Bug #3411938
1484: IF p_organization_id IS NULL THEN
1485: IF p_responsibility_id IS NOT NULL THEN
1486: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
1487: END IF;
1488:
1489: IF p_resp_application_id IS NOT NULL THEN
1490: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

Line 1490: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

1486: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
1487: END IF;
1488:
1489: IF p_resp_application_id IS NOT NULL THEN
1490: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
1491: END IF;
1492: END IF;
1493:
1494: DBMS_SQL.define_column(query_hdl, 1, org_id);

Line 1494: DBMS_SQL.define_column(query_hdl, 1, org_id);

1490: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
1491: END IF;
1492: END IF;
1493:
1494: DBMS_SQL.define_column(query_hdl, 1, org_id);
1495: DBMS_SQL.define_column(query_hdl, 2, org_code, 3);
1496: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
1497:
1498: LOOP

Line 1495: DBMS_SQL.define_column(query_hdl, 2, org_code, 3);

1491: END IF;
1492: END IF;
1493:
1494: DBMS_SQL.define_column(query_hdl, 1, org_id);
1495: DBMS_SQL.define_column(query_hdl, 2, org_code, 3);
1496: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
1497:
1498: LOOP
1499: -- fetch a row

Line 1496: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

1492: END IF;
1493:
1494: DBMS_SQL.define_column(query_hdl, 1, org_id);
1495: DBMS_SQL.define_column(query_hdl, 2, org_code, 3);
1496: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
1497:
1498: LOOP
1499: -- fetch a row
1500: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 1500: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

1496: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
1497:
1498: LOOP
1499: -- fetch a row
1500: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
1501: -- fetch columns from the row
1502: DBMS_SQL.column_value(query_hdl, 1, org_id);
1503: DBMS_SQL.column_value(query_hdl, 2, org_code);
1504:

Line 1502: DBMS_SQL.column_value(query_hdl, 1, org_id);

1498: LOOP
1499: -- fetch a row
1500: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
1501: -- fetch columns from the row
1502: DBMS_SQL.column_value(query_hdl, 1, org_id);
1503: DBMS_SQL.column_value(query_hdl, 2, org_code);
1504:
1505: IF j >= p_node_low_value THEN
1506: x_node_tbl(i).state := p_node_state;

Line 1503: DBMS_SQL.column_value(query_hdl, 2, org_code);

1499: -- fetch a row
1500: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
1501: -- fetch columns from the row
1502: DBMS_SQL.column_value(query_hdl, 1, org_id);
1503: DBMS_SQL.column_value(query_hdl, 2, org_code);
1504:
1505: IF j >= p_node_low_value THEN
1506: x_node_tbl(i).state := p_node_state;
1507: x_node_tbl(i).DEPTH := 1;

Line 1522: DBMS_SQL.close_cursor(query_hdl); -- close cursor

1518: EXIT;
1519: END IF;
1520: END LOOP;
1521:
1522: DBMS_SQL.close_cursor(query_hdl); -- close cursor
1523: x_node_value := j;
1524: x_tbl_index := i;
1525: EXCEPTION
1526: WHEN NO_DATA_FOUND THEN

Line 2465: query_hdl := DBMS_SQL.open_cursor;

2461: -- Enable this during debugging
2462: inv_trx_util_pub.trace(query_str, 'Add-Status Material Workbench', 9);
2463: --trace1('Add_Statuses - ' || query_str);
2464:
2465: query_hdl := DBMS_SQL.open_cursor;
2466: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
2467:
2468: IF p_organization_id IS NOT NULL THEN
2469: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 2466: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

2462: inv_trx_util_pub.trace(query_str, 'Add-Status Material Workbench', 9);
2463: --trace1('Add_Statuses - ' || query_str);
2464:
2465: query_hdl := DBMS_SQL.open_cursor;
2466: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
2467:
2468: IF p_organization_id IS NOT NULL THEN
2469: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
2470: END IF;

Line 2469: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

2465: query_hdl := DBMS_SQL.open_cursor;
2466: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
2467:
2468: IF p_organization_id IS NOT NULL THEN
2469: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
2470: END IF;
2471:
2472: IF p_subinventory_code IS NOT NULL THEN
2473: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 2473: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

2469: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
2470: END IF;
2471:
2472: IF p_subinventory_code IS NOT NULL THEN
2473: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
2474: END IF;
2475:
2476: IF p_locator_id IS NOT NULL THEN
2477: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 2477: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

2473: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
2474: END IF;
2475:
2476: IF p_locator_id IS NOT NULL THEN
2477: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
2478: END IF;
2479:
2480: IF p_inventory_item_id IS NOT NULL THEN
2481: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 2481: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

2477: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
2478: END IF;
2479:
2480: IF p_inventory_item_id IS NOT NULL THEN
2481: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
2482: END IF;
2483:
2484: IF p_revision IS NOT NULL THEN
2485: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 2485: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

2481: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
2482: END IF;
2483:
2484: IF p_revision IS NOT NULL THEN
2485: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
2486: END IF;
2487:
2488: IF p_cost_group_id IS NOT NULL THEN
2489: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 2489: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

2485: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
2486: END IF;
2487:
2488: IF p_cost_group_id IS NOT NULL THEN
2489: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
2490: END IF;
2491:
2492: IF p_lot_number_from IS NOT NULL THEN
2493: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 2493: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

2489: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
2490: END IF;
2491:
2492: IF p_lot_number_from IS NOT NULL THEN
2493: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
2494: END IF;
2495:
2496: IF p_lot_number_to IS NOT NULL THEN
2497: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 2497: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

2493: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
2494: END IF;
2495:
2496: IF p_lot_number_to IS NOT NULL THEN
2497: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
2498: END IF;
2499:
2500: -- NSRIVAST, INVCONV, Start
2501: IF p_grade_from IS NOT NULL THEN

Line 2502: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

2498: END IF;
2499:
2500: -- NSRIVAST, INVCONV, Start
2501: IF p_grade_from IS NOT NULL THEN
2502: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
2503: END IF;
2504: IF p_grade_code IS NOT NULL THEN
2505: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
2506: END IF;

Line 2505: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

2501: IF p_grade_from IS NOT NULL THEN
2502: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
2503: END IF;
2504: IF p_grade_code IS NOT NULL THEN
2505: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
2506: END IF;
2507: -- NSRIVAST, INVCONV, End
2508:
2509: IF p_serial_number_from IS NOT NULL THEN

Line 2510: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

2506: END IF;
2507: -- NSRIVAST, INVCONV, End
2508:
2509: IF p_serial_number_from IS NOT NULL THEN
2510: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
2511: END IF;
2512:
2513: IF p_serial_number_to IS NOT NULL THEN
2514: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 2514: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

2510: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
2511: END IF;
2512:
2513: IF p_serial_number_to IS NOT NULL THEN
2514: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
2515: END IF;
2516:
2517: --bugfix#3646484
2518: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 2521: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

2517: --bugfix#3646484
2518: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
2519: --User is querying for single LPN so converted the range query to equality query
2520: --So it is enought to bind the from lpn alone
2521: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
2522: ELSE
2523: IF p_lpn_from IS NOT NULL THEN
2524: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
2525: END IF;

Line 2524: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

2520: --So it is enought to bind the from lpn alone
2521: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
2522: ELSE
2523: IF p_lpn_from IS NOT NULL THEN
2524: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
2525: END IF;
2526:
2527: IF p_lpn_to IS NOT NULL THEN
2528: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 2528: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

2524: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
2525: END IF;
2526:
2527: IF p_lpn_to IS NOT NULL THEN
2528: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
2529: END IF;
2530: END IF;
2531:
2532: IF p_status_id IS NOT NULL THEN

Line 2533: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

2529: END IF;
2530: END IF;
2531:
2532: IF p_status_id IS NOT NULL THEN
2533: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
2534: END IF;
2535:
2536: IF p_mln_context_code IS NOT NULL THEN
2537: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 2537: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

2533: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
2534: END IF;
2535:
2536: IF p_mln_context_code IS NOT NULL THEN
2537: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
2538: END IF;
2539:
2540: IF p_project_id IS NOT NULL THEN
2541: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 2541: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

2537: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
2538: END IF;
2539:
2540: IF p_project_id IS NOT NULL THEN
2541: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
2542: END IF;
2543:
2544: IF p_task_id IS NOT NULL THEN
2545: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 2545: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

2541: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
2542: END IF;
2543:
2544: IF p_task_id IS NOT NULL THEN
2545: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
2546: END IF;
2547:
2548: IF p_unit_number IS NOT NULL THEN
2549: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 2549: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

2545: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
2546: END IF;
2547:
2548: IF p_unit_number IS NOT NULL THEN
2549: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
2550: END IF;
2551:
2552: /*IF p_site_id IS NOT NULL THEN
2553: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 2553: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

2549: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
2550: END IF;
2551:
2552: /*IF p_site_id IS NOT NULL THEN
2553: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
2554: ELSIF p_vendor_id is NOT NULL THEN
2555: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
2556: END IF;*/
2557: IF (p_owning_qry_mode = 4)

Line 2555: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

2551:
2552: /*IF p_site_id IS NOT NULL THEN
2553: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
2554: ELSIF p_vendor_id is NOT NULL THEN
2555: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
2556: END IF;*/
2557: IF (p_owning_qry_mode = 4)
2558: OR(p_owning_qry_mode = 3) THEN
2559: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 2559: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

2555: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
2556: END IF;*/
2557: IF (p_owning_qry_mode = 4)
2558: OR(p_owning_qry_mode = 3) THEN
2559: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
2560: END IF;
2561:
2562: IF (p_planning_query_mode = 4)
2563: OR(p_planning_query_mode = 3) THEN

Line 2564: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

2560: END IF;
2561:
2562: IF (p_planning_query_mode = 4)
2563: OR(p_planning_query_mode = 3) THEN
2564: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
2565: END IF;
2566:
2567: --ER(3338592) Changes
2568: IF p_item_description IS NOT NULL THEN

Line 2569: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

2565: END IF;
2566:
2567: --ER(3338592) Changes
2568: IF p_item_description IS NOT NULL THEN
2569: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
2570: END IF;
2571:
2572: IF p_qty_from IS NOT NULL THEN
2573: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);

Line 2573: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);

2569: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
2570: END IF;
2571:
2572: IF p_qty_from IS NOT NULL THEN
2573: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);
2574: END IF;
2575:
2576: IF p_qty_to IS NOT NULL THEN
2577: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);

Line 2577: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);

2573: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);
2574: END IF;
2575:
2576: IF p_qty_to IS NOT NULL THEN
2577: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);
2578: END IF;
2579: --End of ER Changes
2580:
2581: -- Bug # 3411938

Line 2584: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );

2580:
2581: -- Bug # 3411938
2582: IF p_organization_id IS NULL THEN
2583: IF p_responsibility_id IS NOT NULL THEN
2584: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
2585: END IF;
2586:
2587: IF p_resp_application_id IS NOT NULL THEN
2588: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

Line 2588: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

2584: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
2585: END IF;
2586:
2587: IF p_resp_application_id IS NOT NULL THEN
2588: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
2589: END IF;
2590: END IF;
2591:
2592: DBMS_SQL.define_column(query_hdl, 1, status_id);

Line 2592: DBMS_SQL.define_column(query_hdl, 1, status_id);

2588: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
2589: END IF;
2590: END IF;
2591:
2592: DBMS_SQL.define_column(query_hdl, 1, status_id);
2593: DBMS_SQL.define_column(query_hdl, 2, status_code, 80);
2594: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
2595:
2596: LOOP

Line 2593: DBMS_SQL.define_column(query_hdl, 2, status_code, 80);

2589: END IF;
2590: END IF;
2591:
2592: DBMS_SQL.define_column(query_hdl, 1, status_id);
2593: DBMS_SQL.define_column(query_hdl, 2, status_code, 80);
2594: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
2595:
2596: LOOP
2597: -- fetch a row

Line 2594: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

2590: END IF;
2591:
2592: DBMS_SQL.define_column(query_hdl, 1, status_id);
2593: DBMS_SQL.define_column(query_hdl, 2, status_code, 80);
2594: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
2595:
2596: LOOP
2597: -- fetch a row
2598: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 2598: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

2594: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
2595:
2596: LOOP
2597: -- fetch a row
2598: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
2599: -- fetch columns from the row
2600: DBMS_SQL.column_value(query_hdl, 1, status_id);
2601: DBMS_SQL.column_value(query_hdl, 2, status_code);
2602:

Line 2600: DBMS_SQL.column_value(query_hdl, 1, status_id);

2596: LOOP
2597: -- fetch a row
2598: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
2599: -- fetch columns from the row
2600: DBMS_SQL.column_value(query_hdl, 1, status_id);
2601: DBMS_SQL.column_value(query_hdl, 2, status_code);
2602:
2603: IF j >= p_node_low_value THEN
2604: x_node_tbl(i).state := p_node_state;

Line 2601: DBMS_SQL.column_value(query_hdl, 2, status_code);

2597: -- fetch a row
2598: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
2599: -- fetch columns from the row
2600: DBMS_SQL.column_value(query_hdl, 1, status_id);
2601: DBMS_SQL.column_value(query_hdl, 2, status_code);
2602:
2603: IF j >= p_node_low_value THEN
2604: x_node_tbl(i).state := p_node_state;
2605: x_node_tbl(i).DEPTH := 1;

Line 2620: DBMS_SQL.close_cursor(query_hdl); -- close cursor

2616: EXIT;
2617: END IF;
2618: END LOOP;
2619:
2620: DBMS_SQL.close_cursor(query_hdl); -- close cursor
2621: x_node_value := j;
2622: x_tbl_index := i;
2623: EXCEPTION
2624: WHEN NO_DATA_FOUND THEN

Line 3502: query_hdl := DBMS_SQL.open_cursor;

3498: END IF;
3499: END IF;
3500: inv_trx_util_pub.trace( query_str, 'Add_subs Material Workbench', 9);
3501: --trace1(query_str, 'add_subs', 9);
3502: query_hdl := DBMS_SQL.open_cursor;
3503: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
3504:
3505: IF p_organization_id IS NOT NULL THEN
3506: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 3503: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

3499: END IF;
3500: inv_trx_util_pub.trace( query_str, 'Add_subs Material Workbench', 9);
3501: --trace1(query_str, 'add_subs', 9);
3502: query_hdl := DBMS_SQL.open_cursor;
3503: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
3504:
3505: IF p_organization_id IS NOT NULL THEN
3506: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
3507: END IF;

Line 3506: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

3502: query_hdl := DBMS_SQL.open_cursor;
3503: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
3504:
3505: IF p_organization_id IS NOT NULL THEN
3506: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
3507: END IF;
3508:
3509: IF p_subinventory_code IS NOT NULL THEN
3510: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 3510: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

3506: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
3507: END IF;
3508:
3509: IF p_subinventory_code IS NOT NULL THEN
3510: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
3511: END IF;
3512:
3513: IF p_locator_id IS NOT NULL THEN
3514: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 3514: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

3510: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
3511: END IF;
3512:
3513: IF p_locator_id IS NOT NULL THEN
3514: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
3515: END IF;
3516:
3517: IF p_inventory_item_id IS NOT NULL THEN
3518: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 3518: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

3514: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
3515: END IF;
3516:
3517: IF p_inventory_item_id IS NOT NULL THEN
3518: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
3519: END IF;
3520:
3521: IF p_revision IS NOT NULL THEN
3522: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 3522: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

3518: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
3519: END IF;
3520:
3521: IF p_revision IS NOT NULL THEN
3522: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
3523: END IF;
3524:
3525: IF p_cost_group_id IS NOT NULL THEN
3526: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 3526: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

3522: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
3523: END IF;
3524:
3525: IF p_cost_group_id IS NOT NULL THEN
3526: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
3527: END IF;
3528:
3529: IF p_lot_number_from IS NOT NULL THEN
3530: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 3530: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

3526: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
3527: END IF;
3528:
3529: IF p_lot_number_from IS NOT NULL THEN
3530: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
3531: END IF;
3532:
3533: IF p_lot_number_to IS NOT NULL THEN
3534: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 3534: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

3530: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
3531: END IF;
3532:
3533: IF p_lot_number_to IS NOT NULL THEN
3534: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
3535: END IF;
3536:
3537: -- NSRIVAST, INVCONV, Start
3538: IF p_grade_from IS NOT NULL THEN

Line 3539: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

3535: END IF;
3536:
3537: -- NSRIVAST, INVCONV, Start
3538: IF p_grade_from IS NOT NULL THEN
3539: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
3540: END IF;
3541: IF p_grade_code IS NOT NULL THEN
3542: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
3543: END IF;

Line 3542: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

3538: IF p_grade_from IS NOT NULL THEN
3539: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
3540: END IF;
3541: IF p_grade_code IS NOT NULL THEN
3542: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
3543: END IF;
3544: -- NSRIVAST, INVCONV, End
3545:
3546: IF p_serial_number_from IS NOT NULL THEN

Line 3547: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

3543: END IF;
3544: -- NSRIVAST, INVCONV, End
3545:
3546: IF p_serial_number_from IS NOT NULL THEN
3547: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
3548: END IF;
3549:
3550: IF p_serial_number_to IS NOT NULL THEN
3551: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 3551: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

3547: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
3548: END IF;
3549:
3550: IF p_serial_number_to IS NOT NULL THEN
3551: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
3552: END IF;
3553:
3554: --bugfix#3646484
3555: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 3558: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

3554: --bugfix#3646484
3555: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
3556: --User is querying for single LPN so converted the range query to equality query
3557: --So it is enought to bind the from lpn alone
3558: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
3559: ELSE
3560: IF p_lpn_from IS NOT NULL THEN
3561: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
3562: END IF;

Line 3561: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

3557: --So it is enought to bind the from lpn alone
3558: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
3559: ELSE
3560: IF p_lpn_from IS NOT NULL THEN
3561: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
3562: END IF;
3563:
3564: IF p_lpn_to IS NOT NULL THEN
3565: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 3565: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

3561: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
3562: END IF;
3563:
3564: IF p_lpn_to IS NOT NULL THEN
3565: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
3566: END IF;
3567: END IF;
3568:
3569: IF p_status_id IS NOT NULL THEN

Line 3570: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

3566: END IF;
3567: END IF;
3568:
3569: IF p_status_id IS NOT NULL THEN
3570: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
3571: END IF;
3572:
3573: IF p_mln_context_code IS NOT NULL THEN
3574: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 3574: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

3570: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
3571: END IF;
3572:
3573: IF p_mln_context_code IS NOT NULL THEN
3574: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
3575: END IF;
3576:
3577: IF p_project_id IS NOT NULL THEN
3578: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 3578: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

3574: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
3575: END IF;
3576:
3577: IF p_project_id IS NOT NULL THEN
3578: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
3579: END IF;
3580:
3581: IF p_task_id IS NOT NULL THEN
3582: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 3582: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

3578: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
3579: END IF;
3580:
3581: IF p_task_id IS NOT NULL THEN
3582: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
3583: END IF;
3584:
3585: IF p_unit_number IS NOT NULL THEN
3586: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 3586: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

3582: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
3583: END IF;
3584:
3585: IF p_unit_number IS NOT NULL THEN
3586: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
3587: END IF;
3588:
3589: /*IF p_site_id IS NOT NULL THEN
3590: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 3590: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

3586: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
3587: END IF;
3588:
3589: /*IF p_site_id IS NOT NULL THEN
3590: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
3591: ELSIF p_vendor_id is NOT NULL THEN
3592: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
3593: END IF;*/
3594: IF (p_owning_qry_mode = 4)

Line 3592: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

3588:
3589: /*IF p_site_id IS NOT NULL THEN
3590: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
3591: ELSIF p_vendor_id is NOT NULL THEN
3592: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
3593: END IF;*/
3594: IF (p_owning_qry_mode = 4)
3595: OR(p_owning_qry_mode = 3) THEN
3596: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 3596: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

3592: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
3593: END IF;*/
3594: IF (p_owning_qry_mode = 4)
3595: OR(p_owning_qry_mode = 3) THEN
3596: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
3597: END IF;
3598:
3599: IF (p_planning_query_mode = 4)
3600: OR(p_planning_query_mode = 3) THEN

Line 3601: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

3597: END IF;
3598:
3599: IF (p_planning_query_mode = 4)
3600: OR(p_planning_query_mode = 3) THEN
3601: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
3602: END IF;
3603:
3604: --ER(3338592) Changes
3605: IF p_item_description IS NOT NULL THEN

Line 3606: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

3602: END IF;
3603:
3604: --ER(3338592) Changes
3605: IF p_item_description IS NOT NULL THEN
3606: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
3607: END IF;
3608:
3609: DBMS_SQL.define_column(query_hdl, 1, sub_code, 10);
3610: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

Line 3609: DBMS_SQL.define_column(query_hdl, 1, sub_code, 10);

3605: IF p_item_description IS NOT NULL THEN
3606: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
3607: END IF;
3608:
3609: DBMS_SQL.define_column(query_hdl, 1, sub_code, 10);
3610: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
3611:
3612: LOOP
3613: -- fetch a row

Line 3610: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

3606: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
3607: END IF;
3608:
3609: DBMS_SQL.define_column(query_hdl, 1, sub_code, 10);
3610: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
3611:
3612: LOOP
3613: -- fetch a row
3614: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 3614: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

3610: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
3611:
3612: LOOP
3613: -- fetch a row
3614: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
3615: -- fetch columns from the row
3616: DBMS_SQL.column_value(query_hdl, 1, sub_code);
3617:
3618: IF j >= p_node_low_value

Line 3616: DBMS_SQL.column_value(query_hdl, 1, sub_code);

3612: LOOP
3613: -- fetch a row
3614: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
3615: -- fetch columns from the row
3616: DBMS_SQL.column_value(query_hdl, 1, sub_code);
3617:
3618: IF j >= p_node_low_value
3619: AND sub_code IS NOT NULL THEN
3620: x_node_tbl(i).state := p_node_state;

Line 3636: DBMS_SQL.close_cursor(query_hdl); -- close cursor

3632: EXIT;
3633: END IF;
3634: END LOOP;
3635:
3636: DBMS_SQL.close_cursor(query_hdl); -- close cursor
3637: x_node_value := j;
3638: x_tbl_index := i;
3639: EXCEPTION
3640: WHEN NO_DATA_FOUND THEN

Line 4498: query_hdl := DBMS_SQL.open_cursor;

4494:
4495: inv_trx_util_pub.trace(query_str, 'Add Loc :- Material Workbench', 9);
4496: --trace1(query_str, 'add_locs', 9);
4497:
4498: query_hdl := DBMS_SQL.open_cursor;
4499: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
4500:
4501: IF p_organization_id IS NOT NULL THEN
4502: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 4499: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

4495: inv_trx_util_pub.trace(query_str, 'Add Loc :- Material Workbench', 9);
4496: --trace1(query_str, 'add_locs', 9);
4497:
4498: query_hdl := DBMS_SQL.open_cursor;
4499: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
4500:
4501: IF p_organization_id IS NOT NULL THEN
4502: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
4503: END IF;

Line 4502: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

4498: query_hdl := DBMS_SQL.open_cursor;
4499: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
4500:
4501: IF p_organization_id IS NOT NULL THEN
4502: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
4503: END IF;
4504:
4505: IF p_subinventory_code IS NOT NULL THEN
4506: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 4506: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

4502: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
4503: END IF;
4504:
4505: IF p_subinventory_code IS NOT NULL THEN
4506: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
4507: END IF;
4508:
4509: IF p_locator_id IS NOT NULL THEN
4510: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 4510: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

4506: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
4507: END IF;
4508:
4509: IF p_locator_id IS NOT NULL THEN
4510: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
4511: END IF;
4512:
4513: IF p_inventory_item_id IS NOT NULL THEN
4514: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 4514: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

4510: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
4511: END IF;
4512:
4513: IF p_inventory_item_id IS NOT NULL THEN
4514: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
4515: END IF;
4516:
4517: IF p_revision IS NOT NULL THEN
4518: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 4518: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

4514: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
4515: END IF;
4516:
4517: IF p_revision IS NOT NULL THEN
4518: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
4519: END IF;
4520:
4521: IF p_cost_group_id IS NOT NULL THEN
4522: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 4522: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

4518: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
4519: END IF;
4520:
4521: IF p_cost_group_id IS NOT NULL THEN
4522: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
4523: END IF;
4524:
4525: IF p_lot_number_from IS NOT NULL THEN
4526: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 4526: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

4522: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
4523: END IF;
4524:
4525: IF p_lot_number_from IS NOT NULL THEN
4526: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
4527: END IF;
4528:
4529: IF p_lot_number_to IS NOT NULL THEN
4530: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 4530: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

4526: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
4527: END IF;
4528:
4529: IF p_lot_number_to IS NOT NULL THEN
4530: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
4531: END IF;
4532:
4533: -- NSRIVAST, INVCONV, Start
4534: IF p_grade_from IS NOT NULL THEN

Line 4535: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

4531: END IF;
4532:
4533: -- NSRIVAST, INVCONV, Start
4534: IF p_grade_from IS NOT NULL THEN
4535: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
4536: END IF;
4537: IF p_grade_code IS NOT NULL THEN
4538: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
4539: END IF;

Line 4538: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

4534: IF p_grade_from IS NOT NULL THEN
4535: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
4536: END IF;
4537: IF p_grade_code IS NOT NULL THEN
4538: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
4539: END IF;
4540: -- NSRIVAST, INVCONV, End
4541:
4542: IF p_serial_number_from IS NOT NULL THEN

Line 4543: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

4539: END IF;
4540: -- NSRIVAST, INVCONV, End
4541:
4542: IF p_serial_number_from IS NOT NULL THEN
4543: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
4544: END IF;
4545:
4546: IF p_serial_number_to IS NOT NULL THEN
4547: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 4547: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

4543: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
4544: END IF;
4545:
4546: IF p_serial_number_to IS NOT NULL THEN
4547: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
4548: END IF;
4549:
4550: --bugfix#3646484
4551: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 4554: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

4550: --bugfix#3646484
4551: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
4552: --User is querying for single LPN so converted the range query to equality query
4553: --So it is enought to bind the from lpn alone
4554: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
4555: ELSE
4556: IF p_lpn_from IS NOT NULL THEN
4557: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
4558: END IF;

Line 4557: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

4553: --So it is enought to bind the from lpn alone
4554: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
4555: ELSE
4556: IF p_lpn_from IS NOT NULL THEN
4557: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
4558: END IF;
4559:
4560: IF p_lpn_to IS NOT NULL THEN
4561: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 4561: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

4557: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
4558: END IF;
4559:
4560: IF p_lpn_to IS NOT NULL THEN
4561: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
4562: END IF;
4563: END IF;
4564:
4565: IF p_status_id IS NOT NULL THEN

Line 4566: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

4562: END IF;
4563: END IF;
4564:
4565: IF p_status_id IS NOT NULL THEN
4566: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
4567: END IF;
4568:
4569: IF p_project_id IS NOT NULL THEN
4570: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 4570: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

4566: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
4567: END IF;
4568:
4569: IF p_project_id IS NOT NULL THEN
4570: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
4571: END IF;
4572:
4573: IF p_task_id IS NOT NULL THEN
4574: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 4574: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

4570: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
4571: END IF;
4572:
4573: IF p_task_id IS NOT NULL THEN
4574: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
4575: END IF;
4576:
4577: IF p_unit_number IS NOT NULL THEN
4578: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 4578: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

4574: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
4575: END IF;
4576:
4577: IF p_unit_number IS NOT NULL THEN
4578: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
4579: END IF;
4580:
4581: /*IF p_site_id IS NOT NULL THEN
4582: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 4582: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

4578: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
4579: END IF;
4580:
4581: /*IF p_site_id IS NOT NULL THEN
4582: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
4583: ELSIF p_vendor_id is NOT NULL THEN
4584: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
4585: END IF;*/
4586: IF (p_owning_qry_mode = 4)

Line 4584: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

4580:
4581: /*IF p_site_id IS NOT NULL THEN
4582: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
4583: ELSIF p_vendor_id is NOT NULL THEN
4584: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
4585: END IF;*/
4586: IF (p_owning_qry_mode = 4)
4587: OR(p_owning_qry_mode = 3) THEN
4588: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 4588: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

4584: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
4585: END IF;*/
4586: IF (p_owning_qry_mode = 4)
4587: OR(p_owning_qry_mode = 3) THEN
4588: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
4589: END IF;
4590:
4591: IF (p_planning_query_mode = 4)
4592: OR(p_planning_query_mode = 3) THEN

Line 4593: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

4589: END IF;
4590:
4591: IF (p_planning_query_mode = 4)
4592: OR(p_planning_query_mode = 3) THEN
4593: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
4594: END IF;
4595:
4596: IF p_mln_context_code IS NOT NULL THEN
4597: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 4597: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

4593: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
4594: END IF;
4595:
4596: IF p_mln_context_code IS NOT NULL THEN
4597: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
4598: END IF;
4599:
4600: --ER(3338592) Changes
4601: IF p_item_description IS NOT NULL THEN

Line 4602: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

4598: END IF;
4599:
4600: --ER(3338592) Changes
4601: IF p_item_description IS NOT NULL THEN
4602: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
4603: END IF;
4604:
4605: DBMS_SQL.define_column(query_hdl, 1, loc_id);
4606: DBMS_SQL.define_column(query_hdl, 2, loc_code, 204);

Line 4605: DBMS_SQL.define_column(query_hdl, 1, loc_id);

4601: IF p_item_description IS NOT NULL THEN
4602: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
4603: END IF;
4604:
4605: DBMS_SQL.define_column(query_hdl, 1, loc_id);
4606: DBMS_SQL.define_column(query_hdl, 2, loc_code, 204);
4607: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
4608:
4609: LOOP

Line 4606: DBMS_SQL.define_column(query_hdl, 2, loc_code, 204);

4602: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
4603: END IF;
4604:
4605: DBMS_SQL.define_column(query_hdl, 1, loc_id);
4606: DBMS_SQL.define_column(query_hdl, 2, loc_code, 204);
4607: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
4608:
4609: LOOP
4610: -- fetch a row

Line 4607: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

4603: END IF;
4604:
4605: DBMS_SQL.define_column(query_hdl, 1, loc_id);
4606: DBMS_SQL.define_column(query_hdl, 2, loc_code, 204);
4607: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
4608:
4609: LOOP
4610: -- fetch a row
4611: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 4611: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

4607: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
4608:
4609: LOOP
4610: -- fetch a row
4611: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
4612: -- fetch columns from the row
4613: DBMS_SQL.column_value(query_hdl, 1, loc_id);
4614: DBMS_SQL.column_value(query_hdl, 2, loc_code);
4615:

Line 4613: DBMS_SQL.column_value(query_hdl, 1, loc_id);

4609: LOOP
4610: -- fetch a row
4611: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
4612: -- fetch columns from the row
4613: DBMS_SQL.column_value(query_hdl, 1, loc_id);
4614: DBMS_SQL.column_value(query_hdl, 2, loc_code);
4615:
4616: IF j >= p_node_low_value
4617: AND loc_code IS NOT NULL THEN

Line 4614: DBMS_SQL.column_value(query_hdl, 2, loc_code);

4610: -- fetch a row
4611: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
4612: -- fetch columns from the row
4613: DBMS_SQL.column_value(query_hdl, 1, loc_id);
4614: DBMS_SQL.column_value(query_hdl, 2, loc_code);
4615:
4616: IF j >= p_node_low_value
4617: AND loc_code IS NOT NULL THEN
4618: x_node_tbl(i).state := p_node_state;

Line 4634: DBMS_SQL.close_cursor(query_hdl); -- close cursor

4630: EXIT;
4631: END IF;
4632: END LOOP;
4633:
4634: DBMS_SQL.close_cursor(query_hdl); -- close cursor
4635: x_node_value := j;
4636: x_tbl_index := i;
4637: EXCEPTION
4638: WHEN NO_DATA_FOUND THEN

Line 5709: query_hdl := DBMS_SQL.open_cursor;

5705: -- Enable this during debugging
5706: inv_trx_util_pub.trace(query_str, 'Add- Cgs Material Workbench', 9);
5707: --trace1(query_str, 'add_cgs', 9);
5708:
5709: query_hdl := DBMS_SQL.open_cursor;
5710: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
5711:
5712: IF p_organization_id IS NOT NULL THEN
5713: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 5710: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

5706: inv_trx_util_pub.trace(query_str, 'Add- Cgs Material Workbench', 9);
5707: --trace1(query_str, 'add_cgs', 9);
5708:
5709: query_hdl := DBMS_SQL.open_cursor;
5710: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
5711:
5712: IF p_organization_id IS NOT NULL THEN
5713: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
5714: END IF;

Line 5713: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

5709: query_hdl := DBMS_SQL.open_cursor;
5710: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
5711:
5712: IF p_organization_id IS NOT NULL THEN
5713: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
5714: END IF;
5715:
5716: IF p_subinventory_code IS NOT NULL THEN
5717: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 5717: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

5713: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
5714: END IF;
5715:
5716: IF p_subinventory_code IS NOT NULL THEN
5717: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
5718: END IF;
5719:
5720: IF p_locator_id IS NOT NULL THEN
5721: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 5721: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

5717: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
5718: END IF;
5719:
5720: IF p_locator_id IS NOT NULL THEN
5721: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
5722: END IF;
5723:
5724: IF p_inventory_item_id IS NOT NULL THEN
5725: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 5725: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

5721: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
5722: END IF;
5723:
5724: IF p_inventory_item_id IS NOT NULL THEN
5725: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
5726: END IF;
5727:
5728: IF p_revision IS NOT NULL THEN
5729: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 5729: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

5725: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
5726: END IF;
5727:
5728: IF p_revision IS NOT NULL THEN
5729: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
5730: END IF;
5731:
5732: IF p_cost_group_id IS NOT NULL THEN
5733: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 5733: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

5729: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
5730: END IF;
5731:
5732: IF p_cost_group_id IS NOT NULL THEN
5733: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
5734: END IF;
5735:
5736: IF p_lot_number_from IS NOT NULL THEN
5737: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 5737: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

5733: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
5734: END IF;
5735:
5736: IF p_lot_number_from IS NOT NULL THEN
5737: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
5738: END IF;
5739:
5740: IF p_lot_number_to IS NOT NULL THEN
5741: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 5741: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

5737: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
5738: END IF;
5739:
5740: IF p_lot_number_to IS NOT NULL THEN
5741: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
5742: END IF;
5743:
5744: -- NSRIVAST, INVCONV, Start
5745: IF p_grade_from IS NOT NULL THEN

Line 5746: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

5742: END IF;
5743:
5744: -- NSRIVAST, INVCONV, Start
5745: IF p_grade_from IS NOT NULL THEN
5746: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
5747: END IF;
5748: IF p_grade_code IS NOT NULL THEN
5749: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
5750: END IF;

Line 5749: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

5745: IF p_grade_from IS NOT NULL THEN
5746: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
5747: END IF;
5748: IF p_grade_code IS NOT NULL THEN
5749: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
5750: END IF;
5751: -- NSRIVAST, INVCONV, End
5752:
5753: IF p_serial_number_from IS NOT NULL THEN

Line 5754: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

5750: END IF;
5751: -- NSRIVAST, INVCONV, End
5752:
5753: IF p_serial_number_from IS NOT NULL THEN
5754: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
5755: END IF;
5756:
5757: IF p_serial_number_to IS NOT NULL THEN
5758: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 5758: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

5754: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
5755: END IF;
5756:
5757: IF p_serial_number_to IS NOT NULL THEN
5758: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
5759: END IF;
5760:
5761: --bugfix#3646484
5762: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 5765: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

5761: --bugfix#3646484
5762: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
5763: --User is querying for single LPN so converted the range query to equality query
5764: --So it is enought to bind the from lpn alone
5765: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
5766: ELSE
5767: IF p_lpn_from IS NOT NULL THEN
5768: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
5769: END IF;

Line 5768: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

5764: --So it is enought to bind the from lpn alone
5765: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
5766: ELSE
5767: IF p_lpn_from IS NOT NULL THEN
5768: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
5769: END IF;
5770:
5771: IF p_lpn_to IS NOT NULL THEN
5772: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 5772: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

5768: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
5769: END IF;
5770:
5771: IF p_lpn_to IS NOT NULL THEN
5772: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
5773: END IF;
5774: END IF;
5775:
5776: IF p_status_id IS NOT NULL THEN

Line 5777: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

5773: END IF;
5774: END IF;
5775:
5776: IF p_status_id IS NOT NULL THEN
5777: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
5778: END IF;
5779:
5780: IF p_mln_context_code IS NOT NULL THEN
5781: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 5781: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

5777: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
5778: END IF;
5779:
5780: IF p_mln_context_code IS NOT NULL THEN
5781: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
5782: END IF;
5783:
5784: IF p_project_id IS NOT NULL THEN
5785: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 5785: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

5781: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
5782: END IF;
5783:
5784: IF p_project_id IS NOT NULL THEN
5785: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
5786: END IF;
5787:
5788: IF p_task_id IS NOT NULL THEN
5789: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 5789: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

5785: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
5786: END IF;
5787:
5788: IF p_task_id IS NOT NULL THEN
5789: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
5790: END IF;
5791:
5792: IF p_unit_number IS NOT NULL THEN
5793: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 5793: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

5789: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
5790: END IF;
5791:
5792: IF p_unit_number IS NOT NULL THEN
5793: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
5794: END IF;
5795:
5796: /*IF p_site_id IS NOT NULL THEN
5797: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 5797: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

5793: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
5794: END IF;
5795:
5796: /*IF p_site_id IS NOT NULL THEN
5797: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
5798: ELSIF p_vendor_id is NOT NULL THEN
5799: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
5800: END IF;*/
5801: IF (p_owning_qry_mode = 4)

Line 5799: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

5795:
5796: /*IF p_site_id IS NOT NULL THEN
5797: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
5798: ELSIF p_vendor_id is NOT NULL THEN
5799: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
5800: END IF;*/
5801: IF (p_owning_qry_mode = 4)
5802: OR(p_owning_qry_mode = 3) THEN
5803: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 5803: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

5799: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
5800: END IF;*/
5801: IF (p_owning_qry_mode = 4)
5802: OR(p_owning_qry_mode = 3) THEN
5803: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
5804: END IF;
5805:
5806: IF (p_planning_query_mode = 4)
5807: OR(p_planning_query_mode = 3) THEN

Line 5808: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

5804: END IF;
5805:
5806: IF (p_planning_query_mode = 4)
5807: OR(p_planning_query_mode = 3) THEN
5808: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
5809: END IF;
5810:
5811: --ER(3338592) Changes
5812: IF p_item_description IS NOT NULL THEN

Line 5813: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

5809: END IF;
5810:
5811: --ER(3338592) Changes
5812: IF p_item_description IS NOT NULL THEN
5813: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
5814: END IF;
5815:
5816: IF p_qty_from IS NOT NULL THEN
5817: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);

Line 5817: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);

5813: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
5814: END IF;
5815:
5816: IF p_qty_from IS NOT NULL THEN
5817: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);
5818: END IF;
5819:
5820: IF p_qty_to IS NOT NULL THEN
5821: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);

Line 5821: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);

5817: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);
5818: END IF;
5819:
5820: IF p_qty_to IS NOT NULL THEN
5821: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);
5822: END IF;
5823: --End of ER(3338592) Changes
5824:
5825: --Bug #3411938

Line 5828: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );

5824:
5825: --Bug #3411938
5826: IF p_organization_id IS NULL THEN
5827: IF p_responsibility_id IS NOT NULL THEN
5828: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
5829: END IF;
5830:
5831: IF p_resp_application_id IS NOT NULL THEN
5832: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

Line 5832: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

5828: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
5829: END IF;
5830:
5831: IF p_resp_application_id IS NOT NULL THEN
5832: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
5833: END IF;
5834: END IF;
5835:
5836:

Line 5837: DBMS_SQL.define_column(query_hdl, 1, cg_id);

5833: END IF;
5834: END IF;
5835:
5836:
5837: DBMS_SQL.define_column(query_hdl, 1, cg_id);
5838: DBMS_SQL.define_column(query_hdl, 2, cg, 10);
5839: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
5840:
5841: LOOP

Line 5838: DBMS_SQL.define_column(query_hdl, 2, cg, 10);

5834: END IF;
5835:
5836:
5837: DBMS_SQL.define_column(query_hdl, 1, cg_id);
5838: DBMS_SQL.define_column(query_hdl, 2, cg, 10);
5839: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
5840:
5841: LOOP
5842: -- fetch a row

Line 5839: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

5835:
5836:
5837: DBMS_SQL.define_column(query_hdl, 1, cg_id);
5838: DBMS_SQL.define_column(query_hdl, 2, cg, 10);
5839: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
5840:
5841: LOOP
5842: -- fetch a row
5843: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 5843: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

5839: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
5840:
5841: LOOP
5842: -- fetch a row
5843: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
5844: -- fetch columns from the row
5845: DBMS_SQL.column_value(query_hdl, 1, cg_id);
5846: DBMS_SQL.column_value(query_hdl, 2, cg);
5847:

Line 5845: DBMS_SQL.column_value(query_hdl, 1, cg_id);

5841: LOOP
5842: -- fetch a row
5843: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
5844: -- fetch columns from the row
5845: DBMS_SQL.column_value(query_hdl, 1, cg_id);
5846: DBMS_SQL.column_value(query_hdl, 2, cg);
5847:
5848: IF j >= p_node_low_value THEN
5849: x_node_tbl(i).state := p_node_state;

Line 5846: DBMS_SQL.column_value(query_hdl, 2, cg);

5842: -- fetch a row
5843: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
5844: -- fetch columns from the row
5845: DBMS_SQL.column_value(query_hdl, 1, cg_id);
5846: DBMS_SQL.column_value(query_hdl, 2, cg);
5847:
5848: IF j >= p_node_low_value THEN
5849: x_node_tbl(i).state := p_node_state;
5850: x_node_tbl(i).DEPTH := 1;

Line 5865: DBMS_SQL.close_cursor(query_hdl); -- close cursor

5861: EXIT;
5862: END IF;
5863: END LOOP;
5864:
5865: DBMS_SQL.close_cursor(query_hdl); -- close cursor
5866: x_node_value := j;
5867: x_tbl_index := i;
5868: EXCEPTION
5869: WHEN NO_DATA_FOUND THEN

Line 6461: query_hdl := DBMS_SQL.open_cursor;

6457:
6458: inv_trx_util_pub.trace(query_str, 'Material Workbench :- ADD LPNs', 9);
6459: --trace1('QUERY STR ' || query_str, 'add_lpns', 9);
6460:
6461: query_hdl := DBMS_SQL.open_cursor;
6462: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
6463: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
6464:
6465: IF p_prepacked <> 1

Line 6462: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

6458: inv_trx_util_pub.trace(query_str, 'Material Workbench :- ADD LPNs', 9);
6459: --trace1('QUERY STR ' || query_str, 'add_lpns', 9);
6460:
6461: query_hdl := DBMS_SQL.open_cursor;
6462: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
6463: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
6464:
6465: IF p_prepacked <> 1
6466: AND p_prepacked <> 999

Line 6463: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);

6459: --trace1('QUERY STR ' || query_str, 'add_lpns', 9);
6460:
6461: query_hdl := DBMS_SQL.open_cursor;
6462: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
6463: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
6464:
6465: IF p_prepacked <> 1
6466: AND p_prepacked <> 999
6467: AND p_prepacked IS NOT NULL THEN

Line 6468: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

6464:
6465: IF p_prepacked <> 1
6466: AND p_prepacked <> 999
6467: AND p_prepacked IS NOT NULL THEN
6468: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
6469: END IF;
6470:
6471: is_bind_required := FALSE;
6472: END IF;

Line 6480: query_hdl := DBMS_SQL.open_cursor;

6476: -- Enable this during debugging
6477: inv_trx_util_pub.trace(query_str, 'ADD LPNs Material Workbench :', 9);
6478: --trace1(query_str, 'add_lpns', 9);
6479:
6480: query_hdl := DBMS_SQL.open_cursor;
6481: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
6482:
6483: IF p_organization_id IS NOT NULL THEN
6484: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 6481: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

6477: inv_trx_util_pub.trace(query_str, 'ADD LPNs Material Workbench :', 9);
6478: --trace1(query_str, 'add_lpns', 9);
6479:
6480: query_hdl := DBMS_SQL.open_cursor;
6481: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
6482:
6483: IF p_organization_id IS NOT NULL THEN
6484: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
6485: END IF;

Line 6484: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

6480: query_hdl := DBMS_SQL.open_cursor;
6481: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
6482:
6483: IF p_organization_id IS NOT NULL THEN
6484: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
6485: END IF;
6486:
6487: IF p_subinventory_code IS NOT NULL THEN
6488: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 6488: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

6484: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
6485: END IF;
6486:
6487: IF p_subinventory_code IS NOT NULL THEN
6488: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
6489: END IF;
6490:
6491: IF p_locator_id IS NOT NULL THEN
6492: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 6492: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

6488: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
6489: END IF;
6490:
6491: IF p_locator_id IS NOT NULL THEN
6492: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
6493: END IF;
6494:
6495: IF p_inventory_item_id IS NOT NULL THEN
6496: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 6496: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

6492: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
6493: END IF;
6494:
6495: IF p_inventory_item_id IS NOT NULL THEN
6496: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
6497: END IF;
6498:
6499: IF p_revision IS NOT NULL THEN
6500: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 6500: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

6496: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
6497: END IF;
6498:
6499: IF p_revision IS NOT NULL THEN
6500: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
6501: END IF;
6502:
6503: IF p_cost_group_id IS NOT NULL THEN
6504: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 6504: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

6500: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
6501: END IF;
6502:
6503: IF p_cost_group_id IS NOT NULL THEN
6504: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
6505: END IF;
6506:
6507: IF p_lot_number_from IS NOT NULL THEN
6508: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 6508: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

6504: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
6505: END IF;
6506:
6507: IF p_lot_number_from IS NOT NULL THEN
6508: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
6509: END IF;
6510:
6511: IF p_lot_number_to IS NOT NULL THEN
6512: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 6512: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

6508: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
6509: END IF;
6510:
6511: IF p_lot_number_to IS NOT NULL THEN
6512: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
6513: END IF;
6514:
6515: -- NSRIVAST, INVCONV, Start
6516: IF p_grade_from IS NOT NULL THEN

Line 6517: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

6513: END IF;
6514:
6515: -- NSRIVAST, INVCONV, Start
6516: IF p_grade_from IS NOT NULL THEN
6517: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
6518: END IF;
6519: IF p_grade_code IS NOT NULL THEN
6520: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
6521: END IF;

Line 6520: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

6516: IF p_grade_from IS NOT NULL THEN
6517: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
6518: END IF;
6519: IF p_grade_code IS NOT NULL THEN
6520: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
6521: END IF;
6522: -- NSRIVAST, INVCONV, End
6523:
6524: IF p_serial_number_from IS NOT NULL THEN

Line 6525: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

6521: END IF;
6522: -- NSRIVAST, INVCONV, End
6523:
6524: IF p_serial_number_from IS NOT NULL THEN
6525: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
6526: END IF;
6527:
6528: IF p_serial_number_to IS NOT NULL THEN
6529: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 6529: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

6525: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
6526: END IF;
6527:
6528: IF p_serial_number_to IS NOT NULL THEN
6529: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
6530: END IF;
6531:
6532: IF p_parent_lpn_id IS NOT NULL THEN
6533: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);

Line 6533: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);

6529: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
6530: END IF;
6531:
6532: IF p_parent_lpn_id IS NOT NULL THEN
6533: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
6534: END IF;
6535:
6536: --bugfix#3646484
6537: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 6540: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

6536: --bugfix#3646484
6537: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
6538: --User is querying for single LPN so converted the range query to equality query
6539: --So it is enought to bind the from lpn alone
6540: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
6541: ELSE
6542: IF p_lpn_from IS NOT NULL THEN
6543: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
6544: END IF;

Line 6543: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

6539: --So it is enought to bind the from lpn alone
6540: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
6541: ELSE
6542: IF p_lpn_from IS NOT NULL THEN
6543: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
6544: END IF;
6545:
6546: IF p_lpn_to IS NOT NULL THEN
6547: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 6547: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

6543: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
6544: END IF;
6545:
6546: IF p_lpn_to IS NOT NULL THEN
6547: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
6548: END IF;
6549: END IF;
6550:
6551: IF p_status_id IS NOT NULL THEN

Line 6552: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

6548: END IF;
6549: END IF;
6550:
6551: IF p_status_id IS NOT NULL THEN
6552: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
6553: END IF;
6554:
6555: IF p_prepacked <> 1
6556: AND p_prepacked <> 999

Line 6558: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

6554:
6555: IF p_prepacked <> 1
6556: AND p_prepacked <> 999
6557: AND p_prepacked IS NOT NULL THEN
6558: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
6559: END IF;
6560:
6561: IF p_mln_context_code IS NOT NULL THEN
6562: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 6562: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

6558: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
6559: END IF;
6560:
6561: IF p_mln_context_code IS NOT NULL THEN
6562: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
6563: END IF;
6564:
6565: IF p_project_id IS NOT NULL THEN
6566: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 6566: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

6562: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
6563: END IF;
6564:
6565: IF p_project_id IS NOT NULL THEN
6566: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
6567: END IF;
6568:
6569: IF p_task_id IS NOT NULL THEN
6570: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 6570: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

6566: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
6567: END IF;
6568:
6569: IF p_task_id IS NOT NULL THEN
6570: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
6571: END IF;
6572:
6573: IF p_unit_number IS NOT NULL THEN
6574: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 6574: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

6570: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
6571: END IF;
6572:
6573: IF p_unit_number IS NOT NULL THEN
6574: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
6575: END IF;
6576:
6577: /*IF p_site_id IS NOT NULL THEN
6578: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 6578: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

6574: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
6575: END IF;
6576:
6577: /*IF p_site_id IS NOT NULL THEN
6578: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
6579: ELSIF p_vendor_id is NOT NULL THEN
6580: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
6581: END IF;*/
6582: IF (p_owning_qry_mode = 4)

Line 6580: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

6576:
6577: /*IF p_site_id IS NOT NULL THEN
6578: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
6579: ELSIF p_vendor_id is NOT NULL THEN
6580: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
6581: END IF;*/
6582: IF (p_owning_qry_mode = 4)
6583: OR(p_owning_qry_mode = 3) THEN
6584: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 6584: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

6580: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
6581: END IF;*/
6582: IF (p_owning_qry_mode = 4)
6583: OR(p_owning_qry_mode = 3) THEN
6584: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
6585: END IF;
6586:
6587: IF (p_planning_query_mode = 4)
6588: OR(p_planning_query_mode = 3) THEN

Line 6589: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

6585: END IF;
6586:
6587: IF (p_planning_query_mode = 4)
6588: OR(p_planning_query_mode = 3) THEN
6589: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
6590: END IF;
6591:
6592: --ER(3338592) Changes
6593: IF p_item_description IS NOT NULL THEN

Line 6594: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

6590: END IF;
6591:
6592: --ER(3338592) Changes
6593: IF p_item_description IS NOT NULL THEN
6594: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
6595: END IF;
6596:
6597: END IF;
6598:

Line 6599: DBMS_SQL.define_column(query_hdl, 1, lpn, 30);

6595: END IF;
6596:
6597: END IF;
6598:
6599: DBMS_SQL.define_column(query_hdl, 1, lpn, 30);
6600: DBMS_SQL.define_column(query_hdl, 2, lpn_id);
6601: DBMS_SQL.define_column(query_hdl, 3, item_id);
6602: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
6603:

Line 6600: DBMS_SQL.define_column(query_hdl, 2, lpn_id);

6596:
6597: END IF;
6598:
6599: DBMS_SQL.define_column(query_hdl, 1, lpn, 30);
6600: DBMS_SQL.define_column(query_hdl, 2, lpn_id);
6601: DBMS_SQL.define_column(query_hdl, 3, item_id);
6602: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
6603:
6604: LOOP

Line 6601: DBMS_SQL.define_column(query_hdl, 3, item_id);

6597: END IF;
6598:
6599: DBMS_SQL.define_column(query_hdl, 1, lpn, 30);
6600: DBMS_SQL.define_column(query_hdl, 2, lpn_id);
6601: DBMS_SQL.define_column(query_hdl, 3, item_id);
6602: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
6603:
6604: LOOP
6605: -- fetch a row

Line 6602: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

6598:
6599: DBMS_SQL.define_column(query_hdl, 1, lpn, 30);
6600: DBMS_SQL.define_column(query_hdl, 2, lpn_id);
6601: DBMS_SQL.define_column(query_hdl, 3, item_id);
6602: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
6603:
6604: LOOP
6605: -- fetch a row
6606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 6606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

6602: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
6603:
6604: LOOP
6605: -- fetch a row
6606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
6607: -- fetch columns from the row
6608: DBMS_SQL.column_value(query_hdl, 1, lpn);
6609: DBMS_SQL.column_value(query_hdl, 2, lpn_id);
6610: DBMS_SQL.column_value(query_hdl, 3, item_id);

Line 6608: DBMS_SQL.column_value(query_hdl, 1, lpn);

6604: LOOP
6605: -- fetch a row
6606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
6607: -- fetch columns from the row
6608: DBMS_SQL.column_value(query_hdl, 1, lpn);
6609: DBMS_SQL.column_value(query_hdl, 2, lpn_id);
6610: DBMS_SQL.column_value(query_hdl, 3, item_id);
6611:
6612: IF item_id IS NOT NULL

Line 6609: DBMS_SQL.column_value(query_hdl, 2, lpn_id);

6605: -- fetch a row
6606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
6607: -- fetch columns from the row
6608: DBMS_SQL.column_value(query_hdl, 1, lpn);
6609: DBMS_SQL.column_value(query_hdl, 2, lpn_id);
6610: DBMS_SQL.column_value(query_hdl, 3, item_id);
6611:
6612: IF item_id IS NOT NULL
6613: AND item_id <> 0 THEN

Line 6610: DBMS_SQL.column_value(query_hdl, 3, item_id);

6606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
6607: -- fetch columns from the row
6608: DBMS_SQL.column_value(query_hdl, 1, lpn);
6609: DBMS_SQL.column_value(query_hdl, 2, lpn_id);
6610: DBMS_SQL.column_value(query_hdl, 3, item_id);
6611:
6612: IF item_id IS NOT NULL
6613: AND item_id <> 0 THEN
6614: SELECT concatenated_segments

Line 6642: DBMS_SQL.close_cursor(query_hdl); -- close cursor

6638: EXIT;
6639: END IF;
6640: END LOOP;
6641:
6642: DBMS_SQL.close_cursor(query_hdl); -- close cursor
6643: x_node_value := j;
6644: x_tbl_index := i;
6645: EXCEPTION
6646: WHEN NO_DATA_FOUND THEN

Line 7944: query_hdl := DBMS_SQL.open_cursor;

7940: -- Enable this during debugging
7941: inv_trx_util_pub.trace(query_str, 'Material Workbench - Add Items : ', 9);
7942: --trace1(query_str, 'add_items', 9);
7943:
7944: query_hdl := DBMS_SQL.open_cursor;
7945: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
7946:
7947: IF p_organization_id IS NOT NULL THEN
7948: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 7945: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

7941: inv_trx_util_pub.trace(query_str, 'Material Workbench - Add Items : ', 9);
7942: --trace1(query_str, 'add_items', 9);
7943:
7944: query_hdl := DBMS_SQL.open_cursor;
7945: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
7946:
7947: IF p_organization_id IS NOT NULL THEN
7948: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
7949: END IF;

Line 7948: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

7944: query_hdl := DBMS_SQL.open_cursor;
7945: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
7946:
7947: IF p_organization_id IS NOT NULL THEN
7948: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
7949: END IF;
7950:
7951: IF p_subinventory_code IS NOT NULL THEN
7952: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 7952: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

7948: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
7949: END IF;
7950:
7951: IF p_subinventory_code IS NOT NULL THEN
7952: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
7953: END IF;
7954:
7955: IF p_locator_id IS NOT NULL THEN
7956: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 7956: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

7952: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
7953: END IF;
7954:
7955: IF p_locator_id IS NOT NULL THEN
7956: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
7957: END IF;
7958:
7959: IF p_inventory_item_id IS NOT NULL THEN
7960: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 7960: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

7956: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
7957: END IF;
7958:
7959: IF p_inventory_item_id IS NOT NULL THEN
7960: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
7961: END IF;
7962:
7963: IF p_revision IS NOT NULL THEN
7964: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 7964: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

7960: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
7961: END IF;
7962:
7963: IF p_revision IS NOT NULL THEN
7964: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
7965: END IF;
7966:
7967: IF p_cost_group_id IS NOT NULL THEN
7968: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 7968: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

7964: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
7965: END IF;
7966:
7967: IF p_cost_group_id IS NOT NULL THEN
7968: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
7969: END IF;
7970:
7971: IF p_lot_number_from IS NOT NULL THEN
7972: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 7972: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

7968: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
7969: END IF;
7970:
7971: IF p_lot_number_from IS NOT NULL THEN
7972: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
7973: END IF;
7974:
7975: IF p_lot_number_to IS NOT NULL THEN
7976: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 7976: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

7972: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
7973: END IF;
7974:
7975: IF p_lot_number_to IS NOT NULL THEN
7976: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
7977: END IF;
7978:
7979: IF p_lot_number IS NOT NULL THEN
7980: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);

Line 7980: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);

7976: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
7977: END IF;
7978:
7979: IF p_lot_number IS NOT NULL THEN
7980: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);
7981: END IF;
7982:
7983: -- NSRIVAST, INVCONV, Start
7984: IF p_grade_from IS NOT NULL THEN

Line 7985: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

7981: END IF;
7982:
7983: -- NSRIVAST, INVCONV, Start
7984: IF p_grade_from IS NOT NULL THEN
7985: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
7986: END IF;
7987: IF p_grade_code IS NOT NULL THEN
7988: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
7989: END IF;

Line 7988: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

7984: IF p_grade_from IS NOT NULL THEN
7985: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
7986: END IF;
7987: IF p_grade_code IS NOT NULL THEN
7988: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
7989: END IF;
7990: -- NSRIVAST, INVCONV, End
7991:
7992: IF p_serial_number_from IS NOT NULL THEN

Line 7993: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

7989: END IF;
7990: -- NSRIVAST, INVCONV, End
7991:
7992: IF p_serial_number_from IS NOT NULL THEN
7993: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
7994: END IF;
7995:
7996: IF p_serial_number_to IS NOT NULL THEN
7997: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 7997: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

7993: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
7994: END IF;
7995:
7996: IF p_serial_number_to IS NOT NULL THEN
7997: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
7998: END IF;
7999:
8000: IF p_serial_number IS NOT NULL THEN
8001: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);

Line 8001: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);

7997: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
7998: END IF;
7999:
8000: IF p_serial_number IS NOT NULL THEN
8001: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);
8002: END IF;
8003:
8004: --bugfix#3646484
8005: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 8008: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

8004: --bugfix#3646484
8005: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
8006: --User is querying for single LPN so converted the range query to equality query
8007: --So it is enought to bind the from lpn alone
8008: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
8009: ELSE
8010: IF p_lpn_from IS NOT NULL THEN
8011: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
8012: END IF;

Line 8011: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

8007: --So it is enought to bind the from lpn alone
8008: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
8009: ELSE
8010: IF p_lpn_from IS NOT NULL THEN
8011: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
8012: END IF;
8013:
8014: IF p_lpn_to IS NOT NULL THEN
8015: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 8015: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

8011: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
8012: END IF;
8013:
8014: IF p_lpn_to IS NOT NULL THEN
8015: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
8016: END IF;
8017: END IF;
8018:
8019: IF p_parent_lpn_id IS NOT NULL THEN

Line 8020: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);

8016: END IF;
8017: END IF;
8018:
8019: IF p_parent_lpn_id IS NOT NULL THEN
8020: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
8021: END IF;
8022:
8023: IF p_status_id IS NOT NULL THEN
8024: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

Line 8024: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

8020: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
8021: END IF;
8022:
8023: IF p_status_id IS NOT NULL THEN
8024: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
8025: END IF;
8026:
8027: IF p_prepacked <> 1
8028: AND p_prepacked <> 999

Line 8030: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

8026:
8027: IF p_prepacked <> 1
8028: AND p_prepacked <> 999
8029: AND p_prepacked IS NOT NULL THEN
8030: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
8031: END IF;
8032:
8033: IF p_mln_context_code IS NOT NULL THEN
8034: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 8034: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

8030: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
8031: END IF;
8032:
8033: IF p_mln_context_code IS NOT NULL THEN
8034: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
8035: END IF;
8036:
8037: IF p_project_id IS NOT NULL THEN
8038: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 8038: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

8034: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
8035: END IF;
8036:
8037: IF p_project_id IS NOT NULL THEN
8038: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
8039: END IF;
8040:
8041: IF p_task_id IS NOT NULL THEN
8042: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 8042: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

8038: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
8039: END IF;
8040:
8041: IF p_task_id IS NOT NULL THEN
8042: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
8043: END IF;
8044:
8045: IF p_unit_number IS NOT NULL THEN
8046: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 8046: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

8042: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
8043: END IF;
8044:
8045: IF p_unit_number IS NOT NULL THEN
8046: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
8047: END IF;
8048:
8049: /*IF p_site_id IS NOT NULL THEN
8050: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 8050: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

8046: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
8047: END IF;
8048:
8049: /*IF p_site_id IS NOT NULL THEN
8050: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
8051: ELSIF p_vendor_id is NOT NULL THEN
8052: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
8053: END IF;*/
8054: IF (p_owning_qry_mode = 4)

Line 8052: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

8048:
8049: /*IF p_site_id IS NOT NULL THEN
8050: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
8051: ELSIF p_vendor_id is NOT NULL THEN
8052: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
8053: END IF;*/
8054: IF (p_owning_qry_mode = 4)
8055: OR(p_owning_qry_mode = 3) THEN
8056: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 8056: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

8052: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
8053: END IF;*/
8054: IF (p_owning_qry_mode = 4)
8055: OR(p_owning_qry_mode = 3) THEN
8056: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
8057: END IF;
8058:
8059: IF (p_planning_query_mode = 4)
8060: OR(p_planning_query_mode = 3) THEN

Line 8061: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

8057: END IF;
8058:
8059: IF (p_planning_query_mode = 4)
8060: OR(p_planning_query_mode = 3) THEN
8061: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
8062: END IF;
8063:
8064: --ER(3338592) Changes
8065: IF p_item_description IS NOT NULL THEN

Line 8066: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

8062: END IF;
8063:
8064: --ER(3338592) Changes
8065: IF p_item_description IS NOT NULL THEN
8066: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
8067: END IF;
8068:
8069: --Bug #3411938
8070: IF p_organization_id IS NULL THEN

Line 8072: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );

8068:
8069: --Bug #3411938
8070: IF p_organization_id IS NULL THEN
8071: IF p_responsibility_id IS NOT NULL THEN
8072: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
8073: END IF;
8074:
8075: IF p_resp_application_id IS NOT NULL THEN
8076: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

Line 8076: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

8072: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
8073: END IF;
8074:
8075: IF p_resp_application_id IS NOT NULL THEN
8076: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
8077: END IF;
8078: END IF;
8079:
8080: --Bug # 3539766

Line 8082: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);

8078: END IF;
8079:
8080: --Bug # 3539766
8081: IF p_qty_from IS NOT NULL THEN
8082: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);
8083: END IF;
8084:
8085: IF p_qty_to IS NOT NULL THEN
8086: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);

Line 8086: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);

8082: dbms_sql.bind_variable(query_hdl, 'qty_from', p_qty_from);
8083: END IF;
8084:
8085: IF p_qty_to IS NOT NULL THEN
8086: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);
8087: END IF;
8088:
8089:
8090: DBMS_SQL.define_column(query_hdl, 1, item_id);

Line 8090: DBMS_SQL.define_column(query_hdl, 1, item_id);

8086: dbms_sql.bind_variable(query_hdl, 'qty_to', p_qty_to);
8087: END IF;
8088:
8089:
8090: DBMS_SQL.define_column(query_hdl, 1, item_id);
8091: DBMS_SQL.define_column(query_hdl, 2, item, 40);
8092: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
8093:
8094: LOOP

Line 8091: DBMS_SQL.define_column(query_hdl, 2, item, 40);

8087: END IF;
8088:
8089:
8090: DBMS_SQL.define_column(query_hdl, 1, item_id);
8091: DBMS_SQL.define_column(query_hdl, 2, item, 40);
8092: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
8093:
8094: LOOP
8095: -- fetch a row

Line 8092: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

8088:
8089:
8090: DBMS_SQL.define_column(query_hdl, 1, item_id);
8091: DBMS_SQL.define_column(query_hdl, 2, item, 40);
8092: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
8093:
8094: LOOP
8095: -- fetch a row
8096: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 8096: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

8092: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
8093:
8094: LOOP
8095: -- fetch a row
8096: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
8097: -- fetch columns from the row
8098: DBMS_SQL.column_value(query_hdl, 1, item_id);
8099: DBMS_SQL.column_value(query_hdl, 2, item);
8100:

Line 8098: DBMS_SQL.column_value(query_hdl, 1, item_id);

8094: LOOP
8095: -- fetch a row
8096: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
8097: -- fetch columns from the row
8098: DBMS_SQL.column_value(query_hdl, 1, item_id);
8099: DBMS_SQL.column_value(query_hdl, 2, item);
8100:
8101: IF j >= p_node_low_value THEN
8102: x_node_tbl(i).state := p_node_state;

Line 8099: DBMS_SQL.column_value(query_hdl, 2, item);

8095: -- fetch a row
8096: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
8097: -- fetch columns from the row
8098: DBMS_SQL.column_value(query_hdl, 1, item_id);
8099: DBMS_SQL.column_value(query_hdl, 2, item);
8100:
8101: IF j >= p_node_low_value THEN
8102: x_node_tbl(i).state := p_node_state;
8103: x_node_tbl(i).DEPTH := 1;

Line 8118: DBMS_SQL.close_cursor(query_hdl); -- close cursor

8114: EXIT;
8115: END IF;
8116: END LOOP;
8117:
8118: DBMS_SQL.close_cursor(query_hdl); -- close cursor
8119: x_node_value := j;
8120: x_tbl_index := i;
8121: EXCEPTION
8122: WHEN NO_DATA_FOUND THEN

Line 9102: query_hdl := DBMS_SQL.open_cursor;

9098: END IF;
9099: inv_trx_util_pub.trace(query_str, 'Add_revs :- Material Workbench', 9);
9100: --trace1(query_str, 'add_revs', 9);
9101:
9102: query_hdl := DBMS_SQL.open_cursor;
9103: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
9104:
9105: IF p_organization_id IS NOT NULL THEN
9106: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 9103: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

9099: inv_trx_util_pub.trace(query_str, 'Add_revs :- Material Workbench', 9);
9100: --trace1(query_str, 'add_revs', 9);
9101:
9102: query_hdl := DBMS_SQL.open_cursor;
9103: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
9104:
9105: IF p_organization_id IS NOT NULL THEN
9106: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
9107: END IF;

Line 9106: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

9102: query_hdl := DBMS_SQL.open_cursor;
9103: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
9104:
9105: IF p_organization_id IS NOT NULL THEN
9106: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
9107: END IF;
9108:
9109: IF p_subinventory_code IS NOT NULL THEN
9110: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 9110: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

9106: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
9107: END IF;
9108:
9109: IF p_subinventory_code IS NOT NULL THEN
9110: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
9111: END IF;
9112:
9113: IF p_locator_id IS NOT NULL THEN
9114: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 9114: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

9110: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
9111: END IF;
9112:
9113: IF p_locator_id IS NOT NULL THEN
9114: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
9115: END IF;
9116:
9117: IF p_inventory_item_id IS NOT NULL THEN
9118: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 9118: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

9114: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
9115: END IF;
9116:
9117: IF p_inventory_item_id IS NOT NULL THEN
9118: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
9119: END IF;
9120:
9121: IF p_revision IS NOT NULL THEN
9122: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 9122: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

9118: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
9119: END IF;
9120:
9121: IF p_revision IS NOT NULL THEN
9122: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
9123: END IF;
9124:
9125: IF p_cost_group_id IS NOT NULL THEN
9126: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 9126: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

9122: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
9123: END IF;
9124:
9125: IF p_cost_group_id IS NOT NULL THEN
9126: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
9127: END IF;
9128:
9129: IF p_lot_number_from IS NOT NULL THEN
9130: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 9130: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

9126: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
9127: END IF;
9128:
9129: IF p_lot_number_from IS NOT NULL THEN
9130: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
9131: END IF;
9132:
9133: IF p_lot_number_to IS NOT NULL THEN
9134: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 9134: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

9130: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
9131: END IF;
9132:
9133: IF p_lot_number_to IS NOT NULL THEN
9134: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
9135: END IF;
9136:
9137: -- NSRIVAST, INVCONV, Start
9138: IF p_grade_from IS NOT NULL THEN

Line 9139: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

9135: END IF;
9136:
9137: -- NSRIVAST, INVCONV, Start
9138: IF p_grade_from IS NOT NULL THEN
9139: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
9140: END IF;
9141: IF p_grade_code IS NOT NULL THEN
9142: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
9143: END IF;

Line 9142: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

9138: IF p_grade_from IS NOT NULL THEN
9139: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
9140: END IF;
9141: IF p_grade_code IS NOT NULL THEN
9142: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
9143: END IF;
9144: -- NSRIVAST, INVCONV, End
9145:
9146: IF p_lot_number IS NOT NULL THEN

Line 9147: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);

9143: END IF;
9144: -- NSRIVAST, INVCONV, End
9145:
9146: IF p_lot_number IS NOT NULL THEN
9147: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);
9148: END IF;
9149:
9150: IF p_serial_number_from IS NOT NULL THEN
9151: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

Line 9151: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

9147: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);
9148: END IF;
9149:
9150: IF p_serial_number_from IS NOT NULL THEN
9151: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
9152: END IF;
9153:
9154: IF p_serial_number_to IS NOT NULL THEN
9155: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 9155: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

9151: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
9152: END IF;
9153:
9154: IF p_serial_number_to IS NOT NULL THEN
9155: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
9156: END IF;
9157:
9158: IF p_serial_number IS NOT NULL THEN
9159: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);

Line 9159: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);

9155: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
9156: END IF;
9157:
9158: IF p_serial_number IS NOT NULL THEN
9159: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);
9160: END IF;
9161:
9162: --bugfix#3646484
9163: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 9166: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

9162: --bugfix#3646484
9163: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
9164: --User is querying for single LPN so converted the range query to equality query
9165: --So it is enought to bind the from lpn alone
9166: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
9167: ELSE
9168: IF p_lpn_from IS NOT NULL THEN
9169: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
9170: END IF;

Line 9169: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

9165: --So it is enought to bind the from lpn alone
9166: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
9167: ELSE
9168: IF p_lpn_from IS NOT NULL THEN
9169: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
9170: END IF;
9171:
9172: IF p_lpn_to IS NOT NULL THEN
9173: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 9173: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

9169: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
9170: END IF;
9171:
9172: IF p_lpn_to IS NOT NULL THEN
9173: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
9174: END IF;
9175: END IF;
9176:
9177: IF p_parent_lpn_id IS NOT NULL THEN

Line 9178: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);

9174: END IF;
9175: END IF;
9176:
9177: IF p_parent_lpn_id IS NOT NULL THEN
9178: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
9179: END IF;
9180:
9181: IF p_status_id IS NOT NULL THEN
9182: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

Line 9182: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

9178: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
9179: END IF;
9180:
9181: IF p_status_id IS NOT NULL THEN
9182: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
9183: END IF;
9184:
9185: IF p_prepacked <> 1
9186: AND p_prepacked <> 999

Line 9188: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

9184:
9185: IF p_prepacked <> 1
9186: AND p_prepacked <> 999
9187: AND p_prepacked IS NOT NULL THEN
9188: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
9189: END IF;
9190:
9191: IF p_mln_context_code IS NOT NULL THEN
9192: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 9192: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

9188: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
9189: END IF;
9190:
9191: IF p_mln_context_code IS NOT NULL THEN
9192: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
9193: END IF;
9194:
9195: IF p_project_id IS NOT NULL THEN
9196: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 9196: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

9192: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
9193: END IF;
9194:
9195: IF p_project_id IS NOT NULL THEN
9196: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
9197: END IF;
9198:
9199: IF p_task_id IS NOT NULL THEN
9200: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 9200: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

9196: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
9197: END IF;
9198:
9199: IF p_task_id IS NOT NULL THEN
9200: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
9201: END IF;
9202:
9203: IF p_unit_number IS NOT NULL THEN
9204: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 9204: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

9200: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
9201: END IF;
9202:
9203: IF p_unit_number IS NOT NULL THEN
9204: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
9205: END IF;
9206:
9207: /*IF p_site_id IS NOT NULL THEN
9208: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 9208: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

9204: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
9205: END IF;
9206:
9207: /*IF p_site_id IS NOT NULL THEN
9208: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
9209: ELSIF p_vendor_id is NOT NULL THEN
9210: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
9211: END IF;*/
9212: IF (p_owning_qry_mode = 4)

Line 9210: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

9206:
9207: /*IF p_site_id IS NOT NULL THEN
9208: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
9209: ELSIF p_vendor_id is NOT NULL THEN
9210: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
9211: END IF;*/
9212: IF (p_owning_qry_mode = 4)
9213: OR(p_owning_qry_mode = 3) THEN
9214: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 9214: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

9210: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
9211: END IF;*/
9212: IF (p_owning_qry_mode = 4)
9213: OR(p_owning_qry_mode = 3) THEN
9214: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
9215: END IF;
9216:
9217: IF (p_planning_query_mode = 4)
9218: OR(p_planning_query_mode = 3) THEN

Line 9219: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

9215: END IF;
9216:
9217: IF (p_planning_query_mode = 4)
9218: OR(p_planning_query_mode = 3) THEN
9219: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
9220: END IF;
9221:
9222: --ER(3338592) Changes
9223: IF p_item_description IS NOT NULL THEN

Line 9228: DBMS_SQL.bind_variable(query_hdl,'item_description',p_item_description);

9224: /* Fix for bug #3457285
9225: Following line has been removed.
9226: query_str := query_str || ' AND item_description LIKE :item_description ';
9227: Added the following line to bind the item_description value to WHERE clause the query*/
9228: DBMS_SQL.bind_variable(query_hdl,'item_description',p_item_description);
9229: /*End of fix 3457285 */
9230:
9231: END IF;
9232: --ER(3338592) Changes

Line 9234: DBMS_SQL.define_column(query_hdl, 1, item, 40);

9230:
9231: END IF;
9232: --ER(3338592) Changes
9233:
9234: DBMS_SQL.define_column(query_hdl, 1, item, 40);
9235: DBMS_SQL.define_column(query_hdl, 2, rev, 3);
9236: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
9237:
9238: LOOP

Line 9235: DBMS_SQL.define_column(query_hdl, 2, rev, 3);

9231: END IF;
9232: --ER(3338592) Changes
9233:
9234: DBMS_SQL.define_column(query_hdl, 1, item, 40);
9235: DBMS_SQL.define_column(query_hdl, 2, rev, 3);
9236: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
9237:
9238: LOOP
9239: -- fetch a row

Line 9236: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

9232: --ER(3338592) Changes
9233:
9234: DBMS_SQL.define_column(query_hdl, 1, item, 40);
9235: DBMS_SQL.define_column(query_hdl, 2, rev, 3);
9236: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
9237:
9238: LOOP
9239: -- fetch a row
9240: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 9240: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

9236: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
9237:
9238: LOOP
9239: -- fetch a row
9240: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
9241: -- fetch columns from the row
9242: DBMS_SQL.column_value(query_hdl, 1, item);
9243: DBMS_SQL.column_value(query_hdl, 2, rev);
9244:

Line 9242: DBMS_SQL.column_value(query_hdl, 1, item);

9238: LOOP
9239: -- fetch a row
9240: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
9241: -- fetch columns from the row
9242: DBMS_SQL.column_value(query_hdl, 1, item);
9243: DBMS_SQL.column_value(query_hdl, 2, rev);
9244:
9245: IF j >= p_node_low_value AND
9246: rev IS NOT NULL THEN

Line 9243: DBMS_SQL.column_value(query_hdl, 2, rev);

9239: -- fetch a row
9240: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
9241: -- fetch columns from the row
9242: DBMS_SQL.column_value(query_hdl, 1, item);
9243: DBMS_SQL.column_value(query_hdl, 2, rev);
9244:
9245: IF j >= p_node_low_value AND
9246: rev IS NOT NULL THEN
9247: x_node_tbl(i).state := p_node_state;

Line 9263: DBMS_SQL.close_cursor(query_hdl); -- close cursor

9259: EXIT;
9260: END IF;
9261: END LOOP;
9262:
9263: DBMS_SQL.close_cursor(query_hdl); -- close cursor
9264: x_node_value := j;
9265: x_tbl_index := i;
9266: EXCEPTION
9267: WHEN NO_DATA_FOUND THEN

Line 10252: query_hdl := DBMS_SQL.open_cursor;

10248: -- Enable this during debugging
10249: inv_trx_util_pub.trace(query_str, 'Add_lot :- Material Workbench', 9);
10250: --trace1(query_str, 'add_lots', 9);
10251:
10252: query_hdl := DBMS_SQL.open_cursor;
10253: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
10254:
10255: IF p_organization_id IS NOT NULL THEN
10256: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 10253: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

10249: inv_trx_util_pub.trace(query_str, 'Add_lot :- Material Workbench', 9);
10250: --trace1(query_str, 'add_lots', 9);
10251:
10252: query_hdl := DBMS_SQL.open_cursor;
10253: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
10254:
10255: IF p_organization_id IS NOT NULL THEN
10256: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
10257: END IF;

Line 10256: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

10252: query_hdl := DBMS_SQL.open_cursor;
10253: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
10254:
10255: IF p_organization_id IS NOT NULL THEN
10256: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
10257: END IF;
10258:
10259: IF p_subinventory_code IS NOT NULL THEN
10260: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 10260: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

10256: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
10257: END IF;
10258:
10259: IF p_subinventory_code IS NOT NULL THEN
10260: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
10261: END IF;
10262:
10263: IF p_locator_id IS NOT NULL THEN
10264: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 10264: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

10260: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
10261: END IF;
10262:
10263: IF p_locator_id IS NOT NULL THEN
10264: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
10265: END IF;
10266:
10267: IF p_inventory_item_id IS NOT NULL THEN
10268: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 10268: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

10264: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
10265: END IF;
10266:
10267: IF p_inventory_item_id IS NOT NULL THEN
10268: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
10269: END IF;
10270:
10271: IF p_revision IS NOT NULL THEN
10272: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 10272: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

10268: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
10269: END IF;
10270:
10271: IF p_revision IS NOT NULL THEN
10272: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
10273: END IF;
10274:
10275: IF p_cost_group_id IS NOT NULL THEN
10276: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 10276: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

10272: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
10273: END IF;
10274:
10275: IF p_cost_group_id IS NOT NULL THEN
10276: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
10277: END IF;
10278:
10279: IF p_lot_number_from IS NOT NULL THEN
10280: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 10280: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

10276: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
10277: END IF;
10278:
10279: IF p_lot_number_from IS NOT NULL THEN
10280: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
10281: END IF;
10282:
10283: IF p_lot_number_to IS NOT NULL THEN
10284: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 10284: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

10280: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
10281: END IF;
10282:
10283: IF p_lot_number_to IS NOT NULL THEN
10284: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
10285: END IF;
10286:
10287: -- NSRIVAST, INVCONV, Start
10288: IF p_grade_from IS NOT NULL THEN

Line 10289: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

10285: END IF;
10286:
10287: -- NSRIVAST, INVCONV, Start
10288: IF p_grade_from IS NOT NULL THEN
10289: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
10290: END IF;
10291: IF p_grade_code IS NOT NULL THEN
10292: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
10293: END IF;

Line 10292: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

10288: IF p_grade_from IS NOT NULL THEN
10289: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
10290: END IF;
10291: IF p_grade_code IS NOT NULL THEN
10292: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
10293: END IF;
10294: -- NSRIVAST, INVCONV, End
10295:
10296: IF p_serial_number_from IS NOT NULL THEN

Line 10297: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

10293: END IF;
10294: -- NSRIVAST, INVCONV, End
10295:
10296: IF p_serial_number_from IS NOT NULL THEN
10297: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
10298: END IF;
10299:
10300: IF p_serial_number_to IS NOT NULL THEN
10301: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 10301: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

10297: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
10298: END IF;
10299:
10300: IF p_serial_number_to IS NOT NULL THEN
10301: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
10302: END IF;
10303:
10304: IF p_serial_number IS NOT NULL THEN
10305: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);

Line 10305: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);

10301: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
10302: END IF;
10303:
10304: IF p_serial_number IS NOT NULL THEN
10305: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);
10306: END IF;
10307:
10308: --bugfix#3646484
10309: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 10312: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

10308: --bugfix#3646484
10309: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
10310: --User is querying for single LPN so converted the range query to equality query
10311: --So it is enought to bind the from lpn alone
10312: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
10313: ELSE
10314: IF p_lpn_from IS NOT NULL THEN
10315: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
10316: END IF;

Line 10315: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

10311: --So it is enought to bind the from lpn alone
10312: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
10313: ELSE
10314: IF p_lpn_from IS NOT NULL THEN
10315: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
10316: END IF;
10317:
10318: IF p_lpn_to IS NOT NULL THEN
10319: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 10319: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

10315: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
10316: END IF;
10317:
10318: IF p_lpn_to IS NOT NULL THEN
10319: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
10320: END IF;
10321: END IF;
10322:
10323: IF p_parent_lpn_id IS NOT NULL THEN

Line 10324: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);

10320: END IF;
10321: END IF;
10322:
10323: IF p_parent_lpn_id IS NOT NULL THEN
10324: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
10325: END IF;
10326:
10327: IF p_status_id IS NOT NULL THEN
10328: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

Line 10328: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

10324: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
10325: END IF;
10326:
10327: IF p_status_id IS NOT NULL THEN
10328: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
10329: END IF;
10330:
10331: IF p_prepacked <> 1
10332: AND p_prepacked <> 999

Line 10334: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

10330:
10331: IF p_prepacked <> 1
10332: AND p_prepacked <> 999
10333: AND p_prepacked IS NOT NULL THEN
10334: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
10335: END IF;
10336:
10337: IF p_mln_context_code IS NOT NULL THEN
10338: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 10338: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

10334: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
10335: END IF;
10336:
10337: IF p_mln_context_code IS NOT NULL THEN
10338: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
10339: END IF;
10340:
10341: IF p_project_id IS NOT NULL THEN
10342: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 10342: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

10338: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
10339: END IF;
10340:
10341: IF p_project_id IS NOT NULL THEN
10342: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
10343: END IF;
10344:
10345: IF p_task_id IS NOT NULL THEN
10346: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 10346: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

10342: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
10343: END IF;
10344:
10345: IF p_task_id IS NOT NULL THEN
10346: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
10347: END IF;
10348:
10349: IF p_unit_number IS NOT NULL THEN
10350: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 10350: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

10346: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
10347: END IF;
10348:
10349: IF p_unit_number IS NOT NULL THEN
10350: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
10351: END IF;
10352:
10353: /*IF p_site_id IS NOT NULL THEN
10354: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 10354: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

10350: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
10351: END IF;
10352:
10353: /*IF p_site_id IS NOT NULL THEN
10354: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
10355: ELSIF p_vendor_id is NOT NULL THEN
10356: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
10357: END IF;*/
10358: IF (p_owning_qry_mode = 4)

Line 10356: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

10352:
10353: /*IF p_site_id IS NOT NULL THEN
10354: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
10355: ELSIF p_vendor_id is NOT NULL THEN
10356: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
10357: END IF;*/
10358: IF (p_owning_qry_mode = 4)
10359: OR(p_owning_qry_mode = 3) THEN
10360: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 10360: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

10356: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
10357: END IF;*/
10358: IF (p_owning_qry_mode = 4)
10359: OR(p_owning_qry_mode = 3) THEN
10360: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
10361: END IF;
10362:
10363: IF (p_planning_query_mode = 4)
10364: OR(p_planning_query_mode = 3) THEN

Line 10365: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

10361: END IF;
10362:
10363: IF (p_planning_query_mode = 4)
10364: OR(p_planning_query_mode = 3) THEN
10365: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
10366: END IF;
10367:
10368: --ER(3338592) Changes
10369: IF p_item_description IS NOT NULL THEN

Line 10370: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

10366: END IF;
10367:
10368: --ER(3338592) Changes
10369: IF p_item_description IS NOT NULL THEN
10370: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
10371: END IF;
10372:
10373: DBMS_SQL.define_column(query_hdl, 1, lot, 80);
10374: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

Line 10373: DBMS_SQL.define_column(query_hdl, 1, lot, 80);

10369: IF p_item_description IS NOT NULL THEN
10370: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
10371: END IF;
10372:
10373: DBMS_SQL.define_column(query_hdl, 1, lot, 80);
10374: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
10375:
10376: LOOP
10377: -- fetch a row

Line 10374: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

10370: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
10371: END IF;
10372:
10373: DBMS_SQL.define_column(query_hdl, 1, lot, 80);
10374: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
10375:
10376: LOOP
10377: -- fetch a row
10378: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 10378: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

10374: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
10375:
10376: LOOP
10377: -- fetch a row
10378: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
10379: -- fetch columns from the row
10380: DBMS_SQL.column_value(query_hdl, 1, lot);
10381:
10382: IF j >= p_node_low_value THEN

Line 10380: DBMS_SQL.column_value(query_hdl, 1, lot);

10376: LOOP
10377: -- fetch a row
10378: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
10379: -- fetch columns from the row
10380: DBMS_SQL.column_value(query_hdl, 1, lot);
10381:
10382: IF j >= p_node_low_value THEN
10383: x_node_tbl(i).state := p_node_state;
10384: x_node_tbl(i).DEPTH := 1;

Line 10399: DBMS_SQL.close_cursor(query_hdl); -- close cursor

10395: EXIT;
10396: END IF;
10397: END LOOP;
10398:
10399: DBMS_SQL.close_cursor(query_hdl); -- close cursor
10400: x_node_value := j;
10401: x_tbl_index := i;
10402: EXCEPTION
10403: WHEN NO_DATA_FOUND THEN

Line 10949: query_hdl := DBMS_SQL.open_cursor;

10945: inv_trx_util_pub.trace(query_str, 'Add Serails :- Material Workbench', 9);
10946: --trace1(query_str, 'add_serials', 9);
10947:
10948:
10949: query_hdl := DBMS_SQL.open_cursor;
10950: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
10951:
10952: IF p_organization_id IS NOT NULL THEN
10953: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

Line 10950: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

10946: --trace1(query_str, 'add_serials', 9);
10947:
10948:
10949: query_hdl := DBMS_SQL.open_cursor;
10950: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
10951:
10952: IF p_organization_id IS NOT NULL THEN
10953: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
10954: END IF;

Line 10953: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

10949: query_hdl := DBMS_SQL.open_cursor;
10950: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
10951:
10952: IF p_organization_id IS NOT NULL THEN
10953: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
10954: END IF;
10955:
10956: IF p_subinventory_code IS NOT NULL THEN
10957: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 10957: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

10953: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
10954: END IF;
10955:
10956: IF p_subinventory_code IS NOT NULL THEN
10957: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
10958: END IF;
10959:
10960: IF p_locator_id IS NOT NULL THEN
10961: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

Line 10961: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

10957: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
10958: END IF;
10959:
10960: IF p_locator_id IS NOT NULL THEN
10961: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
10962: END IF;
10963:
10964: IF p_inventory_item_id IS NOT NULL THEN
10965: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

Line 10965: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

10961: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
10962: END IF;
10963:
10964: IF p_inventory_item_id IS NOT NULL THEN
10965: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
10966: END IF;
10967:
10968: IF p_revision IS NOT NULL THEN
10969: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

Line 10969: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

10965: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
10966: END IF;
10967:
10968: IF p_revision IS NOT NULL THEN
10969: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
10970: END IF;
10971:
10972: IF p_cost_group_id IS NOT NULL THEN
10973: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

Line 10973: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

10969: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
10970: END IF;
10971:
10972: IF p_cost_group_id IS NOT NULL THEN
10973: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
10974: END IF;
10975:
10976: IF p_lot_number_from IS NOT NULL THEN
10977: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

Line 10977: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

10973: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
10974: END IF;
10975:
10976: IF p_lot_number_from IS NOT NULL THEN
10977: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
10978: END IF;
10979:
10980: IF p_lot_number_to IS NOT NULL THEN
10981: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

Line 10981: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

10977: DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
10978: END IF;
10979:
10980: IF p_lot_number_to IS NOT NULL THEN
10981: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
10982: END IF;
10983:
10984: IF p_lot_number IS NOT NULL THEN
10985: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);

Line 10985: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);

10981: DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
10982: END IF;
10983:
10984: IF p_lot_number IS NOT NULL THEN
10985: DBMS_SQL.bind_variable(query_hdl, 'lot_n', p_lot_number);
10986: END IF;
10987:
10988: -- NSRIVAST, INVCONV, Start
10989: IF p_grade_from IS NOT NULL THEN

Line 10990: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

10986: END IF;
10987:
10988: -- NSRIVAST, INVCONV, Start
10989: IF p_grade_from IS NOT NULL THEN
10990: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
10991: END IF;
10992: IF p_grade_code IS NOT NULL THEN
10993: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
10994: END IF;

Line 10993: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

10989: IF p_grade_from IS NOT NULL THEN
10990: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
10991: END IF;
10992: IF p_grade_code IS NOT NULL THEN
10993: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
10994: END IF;
10995: -- NSRIVAST, INVCONV, End
10996:
10997: IF p_serial_number_from IS NOT NULL THEN

Line 10998: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

10994: END IF;
10995: -- NSRIVAST, INVCONV, End
10996:
10997: IF p_serial_number_from IS NOT NULL THEN
10998: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
10999: END IF;
11000:
11001: IF p_serial_number_to IS NOT NULL THEN
11002: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

Line 11002: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

10998: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
10999: END IF;
11000:
11001: IF p_serial_number_to IS NOT NULL THEN
11002: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
11003: END IF;
11004:
11005: --bugfix#3646484
11006: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN

Line 11009: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

11005: --bugfix#3646484
11006: IF ((p_lpn_from IS NOT NULL) AND (p_lpn_to IS NOT NULL) AND (p_lpn_from = p_lpn_to)) THEN
11007: --User is querying for single LPN so converted the range query to equality query
11008: --So it is enought to bind the from lpn alone
11009: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
11010: ELSE
11011: IF p_lpn_from IS NOT NULL THEN
11012: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
11013: END IF;

Line 11012: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

11008: --So it is enought to bind the from lpn alone
11009: dbms_sql.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
11010: ELSE
11011: IF p_lpn_from IS NOT NULL THEN
11012: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
11013: END IF;
11014:
11015: IF p_lpn_to IS NOT NULL THEN
11016: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

Line 11016: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

11012: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
11013: END IF;
11014:
11015: IF p_lpn_to IS NOT NULL THEN
11016: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
11017: END IF;
11018: END IF;
11019:
11020: IF p_parent_lpn_id IS NOT NULL THEN

Line 11021: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);

11017: END IF;
11018: END IF;
11019:
11020: IF p_parent_lpn_id IS NOT NULL THEN
11021: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
11022: END IF;
11023:
11024: IF p_status_id IS NOT NULL THEN
11025: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

Line 11025: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

11021: DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
11022: END IF;
11023:
11024: IF p_status_id IS NOT NULL THEN
11025: DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
11026: END IF;
11027:
11028: IF p_prepacked <> 1
11029: AND p_prepacked <> 999

Line 11031: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

11027:
11028: IF p_prepacked <> 1
11029: AND p_prepacked <> 999
11030: AND p_prepacked IS NOT NULL THEN
11031: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
11032: END IF;
11033:
11034: IF p_mln_context_code IS NOT NULL THEN
11035: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

Line 11035: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

11031: DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
11032: END IF;
11033:
11034: IF p_mln_context_code IS NOT NULL THEN
11035: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
11036: END IF;
11037:
11038: IF p_project_id IS NOT NULL THEN
11039: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

Line 11039: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

11035: DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
11036: END IF;
11037:
11038: IF p_project_id IS NOT NULL THEN
11039: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
11040: END IF;
11041:
11042: IF p_task_id IS NOT NULL THEN
11043: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

Line 11043: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

11039: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
11040: END IF;
11041:
11042: IF p_task_id IS NOT NULL THEN
11043: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
11044: END IF;
11045:
11046: IF p_unit_number IS NOT NULL THEN
11047: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

Line 11047: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

11043: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
11044: END IF;
11045:
11046: IF p_unit_number IS NOT NULL THEN
11047: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
11048: END IF;
11049:
11050: /*IF p_site_id IS NOT NULL THEN
11051: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

Line 11051: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);

11047: DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
11048: END IF;
11049:
11050: /*IF p_site_id IS NOT NULL THEN
11051: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
11052: ELSIF p_vendor_id is NOT NULL THEN
11053: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
11054: END IF;*/
11055: IF (p_owning_qry_mode = 4)

Line 11053: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);

11049:
11050: /*IF p_site_id IS NOT NULL THEN
11051: dbms_sql.bind_variable(query_hdl,'site_id', p_site_id);
11052: ELSIF p_vendor_id is NOT NULL THEN
11053: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
11054: END IF;*/
11055: IF (p_owning_qry_mode = 4)
11056: OR(p_owning_qry_mode = 3) THEN
11057: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 11057: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

11053: dbms_sql.bind_variable(query_hdl,'vendor_id', p_vendor_id);
11054: END IF;*/
11055: IF (p_owning_qry_mode = 4)
11056: OR(p_owning_qry_mode = 3) THEN
11057: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
11058: END IF;
11059:
11060: IF (p_planning_query_mode = 4)
11061: OR(p_planning_query_mode = 3) THEN

Line 11062: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

11058: END IF;
11059:
11060: IF (p_planning_query_mode = 4)
11061: OR(p_planning_query_mode = 3) THEN
11062: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
11063: END IF;
11064:
11065: --ER(3338592) Changes
11066: IF p_item_description IS NOT NULL THEN

Line 11067: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

11063: END IF;
11064:
11065: --ER(3338592) Changes
11066: IF p_item_description IS NOT NULL THEN
11067: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
11068: END IF;
11069:
11070: DBMS_SQL.define_column(query_hdl, 1, serial, 30);
11071: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

Line 11070: DBMS_SQL.define_column(query_hdl, 1, serial, 30);

11066: IF p_item_description IS NOT NULL THEN
11067: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
11068: END IF;
11069:
11070: DBMS_SQL.define_column(query_hdl, 1, serial, 30);
11071: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
11072:
11073: LOOP
11074: -- fetch a row

Line 11071: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

11067: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
11068: END IF;
11069:
11070: DBMS_SQL.define_column(query_hdl, 1, serial, 30);
11071: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
11072:
11073: LOOP
11074: -- fetch a row
11075: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

Line 11075: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

11071: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
11072:
11073: LOOP
11074: -- fetch a row
11075: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
11076: -- fetch columns from the row
11077: DBMS_SQL.column_value(query_hdl, 1, serial);
11078:
11079: IF j >= p_node_low_value THEN

Line 11077: DBMS_SQL.column_value(query_hdl, 1, serial);

11073: LOOP
11074: -- fetch a row
11075: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
11076: -- fetch columns from the row
11077: DBMS_SQL.column_value(query_hdl, 1, serial);
11078:
11079: IF j >= p_node_low_value THEN
11080: x_node_tbl(i).state := p_node_state;
11081: x_node_tbl(i).DEPTH := 1;

Line 11096: DBMS_SQL.close_cursor(query_hdl); -- close cursor

11092: EXIT;
11093: END IF;
11094: END LOOP;
11095:
11096: DBMS_SQL.close_cursor(query_hdl); -- close cursor
11097: x_node_value := j;
11098: x_tbl_index := i;
11099: EXCEPTION
11100: WHEN NO_DATA_FOUND THEN

Line 11508: query_hdl := DBMS_SQL.open_cursor;

11504: END IF ;
11505:
11506: inv_trx_util_pub.trace( query_str, 'Add-Grades Material Workbench', 9);
11507: -- execute the query and populate the node table
11508: query_hdl := DBMS_SQL.open_cursor;
11509: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
11510:
11511: IF p_grade_from IS NOT NULL THEN
11512: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

Line 11509: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);

11505:
11506: inv_trx_util_pub.trace( query_str, 'Add-Grades Material Workbench', 9);
11507: -- execute the query and populate the node table
11508: query_hdl := DBMS_SQL.open_cursor;
11509: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
11510:
11511: IF p_grade_from IS NOT NULL THEN
11512: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
11513: END IF;

Line 11512: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );

11508: query_hdl := DBMS_SQL.open_cursor;
11509: DBMS_SQL.parse(query_hdl, query_str, DBMS_SQL.native);
11510:
11511: IF p_grade_from IS NOT NULL THEN
11512: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
11513: END IF;
11514:
11515: IF p_grade_code IS NOT NULL THEN
11516: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

Line 11516: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);

11512: DBMS_SQL.bind_variable(query_hdl, 'grade_f',p_grade_from );
11513: END IF;
11514:
11515: IF p_grade_code IS NOT NULL THEN
11516: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
11517: END IF;
11518: IF p_organization_id IS NOT NULL THEN
11519: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
11520: END IF;

Line 11519: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);

11515: IF p_grade_code IS NOT NULL THEN
11516: DBMS_SQL.bind_variable(query_hdl, 'grade_c', p_grade_code);
11517: END IF;
11518: IF p_organization_id IS NOT NULL THEN
11519: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
11520: END IF;
11521: IF p_organization_id IS NULL THEN
11522: IF p_responsibility_id IS NOT NULL THEN
11523: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );

Line 11523: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );

11519: DBMS_SQL.bind_variable(query_hdl, 'org_id', p_organization_id);
11520: END IF;
11521: IF p_organization_id IS NULL THEN
11522: IF p_responsibility_id IS NOT NULL THEN
11523: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
11524: END IF;
11525: IF p_resp_application_id IS NOT NULL THEN
11526: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
11527: END IF;

Line 11526: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );

11522: IF p_responsibility_id IS NOT NULL THEN
11523: dbms_sql.bind_variable(query_hdl, 'responsibility_id', p_responsibility_id );
11524: END IF;
11525: IF p_resp_application_id IS NOT NULL THEN
11526: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
11527: END IF;
11528: END IF;
11529: IF p_subinventory_code IS NOT NULL THEN
11530: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

Line 11530: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);

11526: dbms_sql.bind_variable(query_hdl, 'resp_application_id', p_resp_application_id );
11527: END IF;
11528: END IF;
11529: IF p_subinventory_code IS NOT NULL THEN
11530: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
11531: END IF;
11532: IF p_locator_id IS NOT NULL THEN
11533: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
11534: END IF;

Line 11533: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);

11529: IF p_subinventory_code IS NOT NULL THEN
11530: DBMS_SQL.bind_variable(query_hdl, 'sub', p_subinventory_code);
11531: END IF;
11532: IF p_locator_id IS NOT NULL THEN
11533: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
11534: END IF;
11535: IF p_inventory_item_id IS NOT NULL THEN
11536: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
11537: END IF;

Line 11536: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);

11532: IF p_locator_id IS NOT NULL THEN
11533: DBMS_SQL.bind_variable(query_hdl, 'loc_id', p_locator_id);
11534: END IF;
11535: IF p_inventory_item_id IS NOT NULL THEN
11536: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
11537: END IF;
11538: IF p_revision IS NOT NULL THEN
11539: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
11540: END IF;

Line 11539: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);

11535: IF p_inventory_item_id IS NOT NULL THEN
11536: DBMS_SQL.bind_variable(query_hdl, 'item_id', p_inventory_item_id);
11537: END IF;
11538: IF p_revision IS NOT NULL THEN
11539: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
11540: END IF;
11541: IF p_cost_group_id IS NOT NULL THEN
11542: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
11543: END IF;

Line 11542: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);

11538: IF p_revision IS NOT NULL THEN
11539: DBMS_SQL.bind_variable(query_hdl, 'rev', p_revision);
11540: END IF;
11541: IF p_cost_group_id IS NOT NULL THEN
11542: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
11543: END IF;
11544: -- IF p_lot_number_from IS NOT NULL THEN
11545: -- DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
11546: -- END IF;

Line 11545: -- DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);

11541: IF p_cost_group_id IS NOT NULL THEN
11542: DBMS_SQL.bind_variable(query_hdl, 'cg_id', p_cost_group_id);
11543: END IF;
11544: -- IF p_lot_number_from IS NOT NULL THEN
11545: -- DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
11546: -- END IF;
11547: -- IF p_lot_number_to IS NOT NULL THEN
11548: -- DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
11549: -- END IF;

Line 11548: -- DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);

11544: -- IF p_lot_number_from IS NOT NULL THEN
11545: -- DBMS_SQL.bind_variable(query_hdl, 'lot_f', p_lot_number_from);
11546: -- END IF;
11547: -- IF p_lot_number_to IS NOT NULL THEN
11548: -- DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
11549: -- END IF;
11550: IF p_serial_number_from IS NOT NULL THEN
11551: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
11552: END IF;

Line 11551: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);

11547: -- IF p_lot_number_to IS NOT NULL THEN
11548: -- DBMS_SQL.bind_variable(query_hdl, 'lot_t', p_lot_number_to);
11549: -- END IF;
11550: IF p_serial_number_from IS NOT NULL THEN
11551: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
11552: END IF;
11553: IF p_serial_number_to IS NOT NULL THEN
11554: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
11555: END IF;

Line 11554: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);

11550: IF p_serial_number_from IS NOT NULL THEN
11551: DBMS_SQL.bind_variable(query_hdl, 'serial_f', p_serial_number_from);
11552: END IF;
11553: IF p_serial_number_to IS NOT NULL THEN
11554: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
11555: END IF;
11556: IF p_serial_number IS NOT NULL THEN
11557: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);
11558: END IF;

Line 11557: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);

11553: IF p_serial_number_to IS NOT NULL THEN
11554: DBMS_SQL.bind_variable(query_hdl, 'serial_t', p_serial_number_to);
11555: END IF;
11556: IF p_serial_number IS NOT NULL THEN
11557: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);
11558: END IF;
11559: IF p_lpn_from IS NOT NULL THEN
11560: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
11561: END IF;

Line 11560: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);

11556: IF p_serial_number IS NOT NULL THEN
11557: DBMS_SQL.bind_variable(query_hdl, 'serial_n', p_serial_number);
11558: END IF;
11559: IF p_lpn_from IS NOT NULL THEN
11560: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
11561: END IF;
11562: IF p_lpn_to IS NOT NULL THEN
11563: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
11564: END IF;

Line 11563: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);

11559: IF p_lpn_from IS NOT NULL THEN
11560: DBMS_SQL.bind_variable(query_hdl, 'lpn_f', p_lpn_from);
11561: END IF;
11562: IF p_lpn_to IS NOT NULL THEN
11563: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
11564: END IF;
11565: -- IF p_parent_lpn_id IS NOT NULL THEN
11566: -- DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
11567: -- END IF;

Line 11566: -- DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);

11562: IF p_lpn_to IS NOT NULL THEN
11563: DBMS_SQL.bind_variable(query_hdl, 'lpn_t', p_lpn_to);
11564: END IF;
11565: -- IF p_parent_lpn_id IS NOT NULL THEN
11566: -- DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
11567: -- END IF;
11568: -- IF p_status_id IS NOT NULL THEN
11569: -- DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
11570: -- END IF;

Line 11569: -- DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);

11565: -- IF p_parent_lpn_id IS NOT NULL THEN
11566: -- DBMS_SQL.bind_variable(query_hdl, 'plpn_id', p_parent_lpn_id);
11567: -- END IF;
11568: -- IF p_status_id IS NOT NULL THEN
11569: -- DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
11570: -- END IF;
11571: -- IF p_prepacked <> 1
11572: -- AND p_prepacked <> 999 THEN
11573: -- DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

Line 11573: -- DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);

11569: -- DBMS_SQL.bind_variable(query_hdl, 'st_id', p_status_id);
11570: -- END IF;
11571: -- IF p_prepacked <> 1
11572: -- AND p_prepacked <> 999 THEN
11573: -- DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
11574: -- END IF;
11575: -- IF p_mln_context_code IS NOT NULL THEN
11576: -- DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
11577: -- END IF;

Line 11576: -- DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);

11572: -- AND p_prepacked <> 999 THEN
11573: -- DBMS_SQL.bind_variable(query_hdl, 'prepacked', p_prepacked);
11574: -- END IF;
11575: -- IF p_mln_context_code IS NOT NULL THEN
11576: -- DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
11577: -- END IF;
11578: IF p_project_id IS NOT NULL THEN
11579: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
11580: END IF;

Line 11579: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);

11575: -- IF p_mln_context_code IS NOT NULL THEN
11576: -- DBMS_SQL.bind_variable(query_hdl, 'mln_context', p_mln_context_code);
11577: -- END IF;
11578: IF p_project_id IS NOT NULL THEN
11579: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
11580: END IF;
11581: IF p_task_id IS NOT NULL THEN
11582: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
11583: END IF;

Line 11582: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);

11578: IF p_project_id IS NOT NULL THEN
11579: DBMS_SQL.bind_variable(query_hdl, 'pr_id', p_project_id);
11580: END IF;
11581: IF p_task_id IS NOT NULL THEN
11582: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
11583: END IF;
11584: -- IF p_unit_number IS NOT NULL THEN
11585: -- DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
11586: -- END IF;

Line 11585: -- DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);

11581: IF p_task_id IS NOT NULL THEN
11582: DBMS_SQL.bind_variable(query_hdl, 'ta_id', p_task_id);
11583: END IF;
11584: -- IF p_unit_number IS NOT NULL THEN
11585: -- DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
11586: -- END IF;
11587: IF (p_owning_qry_mode = 4)
11588: OR(p_owning_qry_mode = 3) THEN
11589: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

Line 11589: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);

11585: -- DBMS_SQL.bind_variable(query_hdl, 'un_id', p_unit_number);
11586: -- END IF;
11587: IF (p_owning_qry_mode = 4)
11588: OR(p_owning_qry_mode = 3) THEN
11589: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
11590: END IF;
11591: IF (p_planning_query_mode = 4)
11592: OR(p_planning_query_mode = 3) THEN
11593: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

Line 11593: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);

11589: DBMS_SQL.bind_variable(query_hdl, 'own_org', p_owning_org);
11590: END IF;
11591: IF (p_planning_query_mode = 4)
11592: OR(p_planning_query_mode = 3) THEN
11593: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
11594: END IF;
11595: --ER(3338592) Changes
11596: IF p_item_description IS NOT NULL THEN
11597: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

Line 11597: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);

11593: DBMS_SQL.bind_variable(query_hdl, 'plan_org', p_planning_org);
11594: END IF;
11595: --ER(3338592) Changes
11596: IF p_item_description IS NOT NULL THEN
11597: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
11598: END IF;
11599: DBMS_SQL.define_column(query_hdl, 1, grade, 150);
11600: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
11601:

Line 11599: DBMS_SQL.define_column(query_hdl, 1, grade, 150);

11595: --ER(3338592) Changes
11596: IF p_item_description IS NOT NULL THEN
11597: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
11598: END IF;
11599: DBMS_SQL.define_column(query_hdl, 1, grade, 150);
11600: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
11601:
11602: --inv_trx_util_pub.trace( 'Material Workbench rows processed ' || rows_processed, 'Material Workbench', 9);
11603:

Line 11600: rows_processed := DBMS_SQL.EXECUTE(query_hdl);

11596: IF p_item_description IS NOT NULL THEN
11597: dbms_sql.bind_variable(query_hdl, 'item_description', p_item_description);
11598: END IF;
11599: DBMS_SQL.define_column(query_hdl, 1, grade, 150);
11600: rows_processed := DBMS_SQL.EXECUTE(query_hdl);
11601:
11602: --inv_trx_util_pub.trace( 'Material Workbench rows processed ' || rows_processed, 'Material Workbench', 9);
11603:
11604: LOOP

Line 11606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN

11602: --inv_trx_util_pub.trace( 'Material Workbench rows processed ' || rows_processed, 'Material Workbench', 9);
11603:
11604: LOOP
11605: -- fetch a row
11606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
11607: -- fetch columns from the row
11608: DBMS_SQL.column_value(query_hdl, 1, grade);
11609: IF j >= p_node_low_value THEN
11610: x_node_tbl(i).state := p_node_state;

Line 11608: DBMS_SQL.column_value(query_hdl, 1, grade);

11604: LOOP
11605: -- fetch a row
11606: IF DBMS_SQL.fetch_rows(query_hdl) > 0 THEN
11607: -- fetch columns from the row
11608: DBMS_SQL.column_value(query_hdl, 1, grade);
11609: IF j >= p_node_low_value THEN
11610: x_node_tbl(i).state := p_node_state;
11611: x_node_tbl(i).DEPTH := 1;
11612: x_node_tbl(i).label := substr(grade,1,80);

Line 11624: DBMS_SQL.close_cursor(query_hdl); -- close cursor

11620: ELSE
11621: EXIT;
11622: END IF;
11623: END LOOP;
11624: DBMS_SQL.close_cursor(query_hdl); -- close cursor
11625: x_node_value := j;
11626: x_tbl_index := i;
11627: EXCEPTION
11628: WHEN NO_DATA_FOUND THEN