[Home] [Help]
247: lot_control_code,
248: concatenated_segments,
249: revision_qty_control_code,
250: comms_nl_trackable_flag
251: FROM mtl_system_items_kfv
252: WHERE inventory_item_id = c_inventory_id
253: AND organization_id = c_organization_id;
254: l_serial_number_control NUMBER;
255: l_lot_control_code NUMBER;
253: AND organization_id = c_organization_id;
254: l_serial_number_control NUMBER;
255: l_lot_control_code NUMBER;
256: -- Changed by jaramana on 16-APR-2008 for bug 6977832
257: -- Changed from 40 to mtl_system_items_kfv.concatenated_segments%TYPE in order
258: -- to accommodate longer item names.
259: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
260: l_revision_qty_control_code NUMBER;
261: l_comms_nl_trackable_flag VARCHAR2(1);
255: l_lot_control_code NUMBER;
256: -- Changed by jaramana on 16-APR-2008 for bug 6977832
257: -- Changed from 40 to mtl_system_items_kfv.concatenated_segments%TYPE in order
258: -- to accommodate longer item names.
259: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
260: l_revision_qty_control_code NUMBER;
261: l_comms_nl_trackable_flag VARCHAR2(1);
262: BEGIN
263: IF (p_inventory_id IS NULL) OR (p_inventory_id = FND_API.G_MISS_NUM)
332: -- SATHAPLI::Bug#4912576 fix::SQL ID 14401150 --
333: /*
334: SELECT o.operating_unit
335: FROM org_organization_definitions o,
336: mtl_system_items_kfv m,
337: csi_item_instances c
338: WHERE c.instance_id = p_instance_id
339: AND c.inventory_item_id = m.inventory_item_id
340: AND c.inv_master_organization_id = m.organization_id
340: AND c.inv_master_organization_id = m.organization_id
341: AND m.organization_id = o.organization_id;
342: */
343: SELECT i.operating_unit
344: FROM inv_organization_info_v i, mtl_system_items_kfv m,
345: csi_item_instances c
346: WHERE c.instance_id = p_instance_id AND
347: c.inventory_item_id = m.inventory_item_id AND
348: c.inv_master_organization_id = m.organization_id AND
1315: l_sn_tag_rec_found VARCHAR2(1) DEFAULT 'Y';
1316: l_serial_number_control NUMBER;
1317: l_mfg_date csi_iea_values.attribute_value%TYPE;
1318: l_mfg_date_rec_found VARCHAR2(1) DEFAULT 'Y';
1319: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
1320: l_lookup_code fnd_lookups.lookup_code%TYPE;
1321: l_item_assoc_id NUMBER;
1322: l_end_date DATE;
1323:
2271: l_attribute_value_id NUMBER;
2272: l_attribute_value csi_iea_values.attribute_value%TYPE;
2273: l_attribute_id NUMBER;
2274: l_subscript NUMBER DEFAULT 0;
2275: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
2276: l_item_assoc_id NUMBER;
2277: l_new_instance_id NUMBER;
2278: l_new_csi_instance_ovn NUMBER;
2279: l_parent_uc_header_id NUMBER;
3334: l_dummy_char VARCHAR2(1);
3335: l_subunit BOOLEAN;
3336: i NUMBER := 0;
3337: l_subscript NUMBER DEFAULT 0;
3338: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
3339: l_item_assoc_id NUMBER;
3340: l_meaning fnd_lookups.meaning%TYPE;
3341: l_parent_uc_header_id NUMBER;
3342: l_parent_instance_id NUMBER;
4666: C.inv_subinventory_name,
4667: C.inv_locator_id,
4668: to_number(NULL) uc_header_id
4669: FROM csi_item_instances C,
4670: mtl_system_items_kfv M,
4671: ahl_mc_relationships R,
4672: ahl_item_associations_b A
4673: WHERE C.inventory_item_id = M.inventory_item_id
4674: AND C.inv_master_organization_id = M.organization_id
4804: WHERE parent_uc_header_id IS NULL
4805: AND unit_config_status_code IN ('COMPLETE', 'INCOMPLETE')
4806: )U,
4807: csi_item_instances C,
4808: mtl_system_items_kfv M
4809: WHERE U.csi_instance_id = C.instance_id
4810: AND C.inventory_item_id = M.inventory_item_id
4811: AND C.inv_master_organization_id = M.organization_id
4812: AND trunc(nvl(U.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
5451: l_inventory_revision VARCHAR2(3);
5452: l_uom_code VARCHAR2(3);
5453: l_item_assoc_id NUMBER;
5454: l_priority NUMBER;
5455: l_item_number mtl_system_items_kfv.concatenated_segments%TYPE :=upper(nvl(p_item_number, '%'));
5456: l_serial_number csi_item_instances.serial_number%TYPE := upper(nvl(p_serial_number, '%'));
5457: l_uc_header_id NUMBER;
5458: i NUMBER;
5459: j NUMBER;
5493: C.inventory_revision,
5494: C.unit_of_measure uom_code,
5495: to_number(NULL) uc_header_id
5496: FROM csi_item_instances C,
5497: mtl_system_items_kfv M,
5498: ahl_mc_relationships R,
5499: ahl_item_associations_b A
5500: WHERE C.inventory_item_id = M.inventory_item_id
5501: AND C.inv_master_organization_id = M.organization_id
5609: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
5610: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)
5611: ) U,
5612: csi_item_instances C,
5613: mtl_system_items_kfv M
5614: WHERE U.csi_instance_id = C.instance_id
5615: AND C.inventory_item_id = M.inventory_item_id
5616: AND C.inv_master_organization_id = M.organization_id
5617: -- SATHAPLI::Bug 9022080, 05-Nov-2009, filter out sub UCs in INVENTORY, i.e. with root instance in INVENTORY
6254: i NUMBER := 0;
6255: l_transaction_type_id NUMBER;
6256: l_return_val BOOLEAN;
6257: l_attribute_id NUMBER;
6258: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
6259: l_new_instance_id NUMBER;
6260: l_new_csi_instance_ovn NUMBER;
6261: l_end_date DATE;
6262:
6718: l_id_ref_map_Tbl Ref_Tbl_Type;
6719:
6720: -- Validate the p_inventory_item_id parameter
6721: CURSOR check_inv_item_id IS
6722: SELECT 'x' from mtl_system_items_kfv
6723: WHERE inventory_item_id = p_inventory_item_id;
6724:
6725: -- Cursor to pick up all instances that match the item association setup. But units can only
6726: -- be installed in MC leaf node which has associated sub-MC, in other words, any units
6741: CII.unit_of_measure uom_code,
6742: to_number(NULL) uc_header_id,
6743: CII.wip_job_id
6744: FROM csi_item_instances CII,
6745: mtl_system_items_kfv MSIK,
6746: ahl_mc_relationships AMR,
6747: ahl_item_associations_b AIA,
6748: hr_all_organization_units_tl HAOUT
6749: WHERE CII.inventory_item_id = NVL(c_inventory_item_id, CII.inventory_item_id)
6810: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
6811: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)
6812: ) U,
6813: csi_item_instances CII,
6814: mtl_system_items_kfv MSIK,
6815: hr_all_organization_units_tl HAOUT
6816: WHERE CII.inventory_item_id = NVL(c_inventory_item_id, CII.inventory_item_id)
6817: AND U.csi_instance_id = CII.instance_id
6818: AND CII.inventory_item_id = MSIK.inventory_item_id
7115:
7116: CURSOR is_instance_valid(c_instance_id IN NUMBER, c_relationship_id IN NUMBER) IS
7117: SELECT CII.inventory_item_id
7118: FROM csi_item_instances CII,
7119: mtl_system_items_kfv MSIK,
7120: ahl_mc_relationships AMR,
7121: ahl_item_associations_b AIA
7122: WHERE CII.instance_id = c_instance_id
7123: AND MSIK.inventory_item_id = CII.inventory_item_id
7161: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
7162: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)
7163: ) U,
7164: csi_item_instances CII,
7165: mtl_system_items_kfv MSIK
7166: WHERE CII.instance_id = c_instance_id
7167: AND U.csi_instance_id = CII.instance_id
7168: AND CII.inventory_item_id = MSIK.inventory_item_id
7169: AND CII.inv_master_organization_id = MSIK.organization_id