[Home] [Help]
23: l_rev_control_code NUMBER;
24:
25: CURSOR c IS
26: SELECT revision_qty_control_code
27: FROM mtl_system_items
28: WHERE inventory_item_id = p_item_id
29: AND organization_id = p_org_id;
30: BEGIN
31:
51: l_lot_control_code NUMBER;
52:
53: CURSOR c IS
54: SELECT lot_control_code
55: FROM mtl_system_items
56: WHERE inventory_item_id = p_item_id
57: AND organization_id = p_org_id;
58: BEGIN
59:
458: BEGIN
459:
460:
461: /*x_lov_sql := 'SELECT to_char(sl.line_number), msik.concatenated_segments
462: FROM mtl_system_items_kfv msik, so_lines sl
463: WHERE sl.inventory_item_id = msik.inventory_item_id'; */
464:
465: x_lov_sql := NULL;
466:
775: ORDER BY revision';
776: */
777: x_lov_sql := 'SELECT revision, QLTDATE.date_to_user(effectivity_date)
778: FROM mtl_item_revisions mir,
779: mtl_system_items_kfv msi
780: WHERE mir.inventory_item_id = msi.inventory_item_id
781: AND mir.organization_id = msi.organization_id
782: AND msi.revision_qty_control_code = 2
783: AND mir.inventory_item_id = :1
1163:
1164: x_lov_sql :=
1165: 'SELECT cii.instance_number, cii.instance_description
1166: FROM
1167: csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
1168: WHERE
1169: msib.organization_id = mp.organization_id and
1170: msib.organization_id = cii.last_vld_organization_id and
1171: msib.inventory_item_id = cii.inventory_item_id and
1231: --dgupta: Start R12 EAM Integration. Bug 4345492
1232: x_lov_sql := 'SELECT
1233: distinct msn.serial_number, msn.descriptive_text
1234: FROM
1235: mtl_serial_numbers msn, csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
1236: WHERE
1237: msib.organization_id = mp.organization_id and
1238: msib.organization_id = cii.last_vld_organization_id and
1239: msib.inventory_item_id = cii.inventory_item_id and
1554:
1555: BEGIN
1556:
1557: x_lov_sql := 'SELECT concatenated_segments, description
1558: FROM mtl_system_items_kfv
1559: WHERE organization_id = :1
1560: ORDER BY concatenated_segments';
1561:
1562: END get_item_lov;
1582:
1583: -- l_item_id := qa_flex_util.get_item_id(x_org_id, p_item);
1584:
1585: x_lov_sql := 'SELECT concatenated_segments, description
1586: FROM mtl_system_items_kfv
1587: WHERE organization_id = :1 ' ||
1588: ' and inventory_item_id in (
1589: SELECT bic.component_item_id
1590: FROM bom_inventory_components bic,
1598: else
1599:
1600: -- show an empty list if parent item is not passed
1601: x_lov_sql := 'SELECT concatenated_segments, description
1602: FROM mtl_system_items_kfv
1603: WHERE 1 = 2
1604: ORDER BY concatenated_segments';
1605:
1606: end if;
1633: BEGIN
1634:
1635: --dgupta: Start R12 EAM Integration. Bug 4345492
1636: x_lov_sql := 'select distinct msikfv.concatenated_segments, msikfv.description
1637: from mtl_system_items_b_kfv msikfv, mtl_parameters mp
1638: where msikfv.organization_id = mp.organization_id
1639: and msikfv.eam_item_type in (1,3)
1640: and mp.maint_organization_id = :1
1641: order by msikfv.concatenated_segments';
1673: -- show all activities asssociated to the asset group
1674: -- If no match found or if asset group passed in is null, lov is empty
1675: /*
1676: x_lov_sql := 'SELECT meaav.activity, meaav.activity_description
1677: FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
1678: WHERE msib.organization_id = :1
1679: and meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
1680: and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
1681: and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
1687: x_lov_sql := 'SELECT
1688: msib.concatenated_segments activity ,
1689: msib.description activity_description
1690: FROM mtl_eam_asset_activities meaav,
1691: mtl_system_items_b_kfv msib
1692: WHERE msib.organization_id = :1
1693: AND meaav.maintenance_object_id = :2 --pass asset group inventory_item_id
1694: AND (meaav.end_date_active is null
1695: OR meaav.end_date_active > sysdate)
1702: -- show all activities associated to asset group and asset number
1703: -- if exact match not found, lov is empty.
1704: /*
1705: x_lov_sql := 'SELECT meaav.activity, meaav.activity_description
1706: FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
1707: WHERE msib.organization_id = :1
1708: and meaav.maintenance_object_id = :2 --pass asset instance_id
1709: and meaav.maintenance_object_type = 3 --serialized item
1710: and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
1716: x_lov_sql := 'SELECT
1717: msi.concatenated_segments activity ,
1718: msi.description activity_description
1719: FROM mtl_eam_asset_activities meaa,
1720: mtl_system_items_b_kfv msi
1721: WHERE msi.organization_id = :1
1722: AND meaa.maintenance_object_id = :2 --pass asset instance_id
1723: AND meaa.maintenance_object_type = 3 --serialized item
1724: AND (meaa.end_date_active is null
1883:
1884: BEGIN
1885:
1886: x_lov_sql := 'SELECT cii.instance_number, cii.serial_number
1887: FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
1888: WHERE cii.inventory_item_id = msik.inventory_item_id
1889: AND cii.last_vld_organization_id = msik.organization_id
1890: ORDER BY 1';
1891:
2420:
2421: BEGIN
2422:
2423: x_lov_sql := 'SELECT concatenated_segments, description
2424: FROM mtl_system_items_kfv
2425: WHERE organization_id = :1
2426: ORDER BY concatenated_segments';
2427:
2428: END get_bill_reference_lov;
2432:
2433: BEGIN
2434:
2435: x_lov_sql := 'SELECT concatenated_segments, description
2436: FROM mtl_system_items_kfv
2437: WHERE organization_id = :1
2438: ORDER BY concatenated_segments';
2439:
2440: END get_routing_reference_lov;