[Home] [Help]
2050: serial_number,
2051: quantity,
2052: unit_of_measure,
2053: lot_number --jeli 12/01/05
2054: FROM csi_item_instances
2055: WHERE instance_id = p_instance_id;
2056:
2057: --yazhou 06-Jun-2006 starts
2058: --bug fix#5304095
2144:
2145: -- Cursor to check if an instance is independent (not an installed component)
2146: CURSOR ahl_csi_unit_test_csr (p_csi_ii_id IN NUMBER) IS
2147: SELECT 1
2148: FROM CSI_ITEM_INSTANCES CII
2149: WHERE CII.INSTANCE_ID = p_csi_ii_id AND
2150: NOT EXISTS
2151: (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
2152: WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID AND
2364: from ahl_osp_order_lines ospl,
2365: oe_order_lines_all oel,
2366: csi_t_transaction_lines tl,
2367: csi_t_txn_line_details tld,
2368: csi_item_instances csi
2369: where ospl.osp_order_id = p_osp_order_id
2370: AND (NVL(oe_ship_line_id, -9) = NVL(p_line_id, -8)
2371: OR NVL(oe_return_line_id, -9) = NVL(p_line_id, -8))
2372: AND oel.line_id = p_line_id
2403: csii.lot_number
2404: FROM AHL_WORKORDERS wo,
2405: ahl_visits_b vst,
2406: ahl_visit_tasks_b vts,
2407: csi_item_instances csii
2408: WHERE wo.workorder_id = p_workorder_id
2409: AND wo.visit_task_id = vts.visit_task_id(+)
2410: AND vts.visit_id = vst.visit_id(+)
2411: AND vts.instance_id = csii.instance_id(+)
2418: CURSOR get_inv_inst_csr(p_inv_item_id IN NUMBER,
2419: p_inv_org_id IN NUMBER,
2420: p_serial_number IN VARCHAR2) IS
2421: SELECT INSTANCE_ID
2422: FROM csi_item_instances csi
2423: WHERE p_inv_item_id = csi.inventory_item_id
2424: and p_inv_org_id = csi.last_vld_organization_id
2425: and p_serial_number = csi.serial_number;
2426: */
2426: */
2427: CURSOR get_inv_inst_csr(p_inv_item_id IN NUMBER,
2428: p_serial_number IN VARCHAR2) IS
2429: SELECT INSTANCE_ID
2430: FROM csi_item_instances csi
2431: WHERE p_inv_item_id = csi.inventory_item_id
2432: and p_serial_number = csi.serial_number;
2433:
2434: CURSOR get_inv_inst_from_lot_csr(p_inv_item_id IN NUMBER,
2433:
2434: CURSOR get_inv_inst_from_lot_csr(p_inv_item_id IN NUMBER,
2435: p_lot_number IN VARCHAR2) IS
2436: SELECT INSTANCE_ID
2437: FROM csi_item_instances csi
2438: WHERE csi.inventory_item_id = p_inv_item_id
2439: and csi.lot_number = p_lot_number;
2440:
2441: --Modified by mpothuku on 06-Mar-06 to fix the Perf Bug #4919255
2467: csi.lot_number,
2468: csi.quantity,
2469: csi.unit_of_measure,
2470: csi.last_vld_organization_id
2471: FROM ahl_osp_order_lines ospl, csi_item_instances csi
2472: WHERE ospl.osp_order_line_id = p_osp_order_line_id and
2473: csi.instance_id = ospl.exchange_instance_id;
2474:
2475: l_exchange_inst_dtls exchange_instance_dtls_csr%rowtype;
4568: a.PO_REQ_LINE_ID
4569: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4570: FROM AHL_OSP_ORDER_LINES a,
4571: ahl_workorders wo,
4572: csi_item_instances csii,
4573: ahl_visit_tasks_b vts
4574: WHERE a.osp_order_id = p_osp_id
4575: AND wo.workorder_id(+) = a.workorder_id
4576: AND wo.visit_task_id = vts.visit_task_id(+)
4674: -- Check if the instance is a valid IB instance
4675: -- Also not part of relationship
4676:
4677: CURSOR val_exg_instance_id_csr(p_instance_id IN NUMBER) IS
4678: SELECT 'x' FROM csi_item_instances csi
4679: WHERE instance_id = p_instance_id
4680: AND nvl(csi.active_end_date, sysdate + 1) > sysdate
4681: AND NOT EXISTS (select subject_id from csi_ii_relationships where
4682: subject_id = p_instance_id and
4943: FROM oe_order_lines_all
4944: WHERE line_id = p_oe_line_id;
4945:
4946: -- get instance details.
4947: CURSOR csi_item_instances_csr (p_instance_id IN NUMBER) IS
4948: SELECT serial_number, inventory_revision, lot_number, mfg_serial_number_flag
4949: FROM csi_item_instances
4950: WHERE instance_id = p_instance_id;
4951:
4945:
4946: -- get instance details.
4947: CURSOR csi_item_instances_csr (p_instance_id IN NUMBER) IS
4948: SELECT serial_number, inventory_revision, lot_number, mfg_serial_number_flag
4949: FROM csi_item_instances
4950: WHERE instance_id = p_instance_id;
4951:
4952: -- get value for return by date.
4953: CURSOR ahl_osp_order_lines_csr (p_oe_line_id IN NUMBER) IS
5275: we are creating the IB transactions even if the instance is not present on the order line for Exchange Return Lines
5276: we do not need to any clauses for exchange orders below.
5277: */
5278: IF(p_csi_instance_id is not null) THEN
5279: OPEN csi_item_instances_csr (p_csi_instance_id);
5280: FETCH csi_item_instances_csr INTO l_txn_line_dtl_tbl(1).SERIAL_NUMBER,
5281: l_txn_line_dtl_tbl(1).INVENTORY_REVISION,
5282: l_txn_line_dtl_tbl(1).LOT_NUMBER,
5283: l_txn_line_dtl_tbl(1).MFG_SERIAL_NUMBER_FLAG;
5276: we do not need to any clauses for exchange orders below.
5277: */
5278: IF(p_csi_instance_id is not null) THEN
5279: OPEN csi_item_instances_csr (p_csi_instance_id);
5280: FETCH csi_item_instances_csr INTO l_txn_line_dtl_tbl(1).SERIAL_NUMBER,
5281: l_txn_line_dtl_tbl(1).INVENTORY_REVISION,
5282: l_txn_line_dtl_tbl(1).LOT_NUMBER,
5283: l_txn_line_dtl_tbl(1).MFG_SERIAL_NUMBER_FLAG;
5284: CLOSE csi_item_instances_csr;
5280: FETCH csi_item_instances_csr INTO l_txn_line_dtl_tbl(1).SERIAL_NUMBER,
5281: l_txn_line_dtl_tbl(1).INVENTORY_REVISION,
5282: l_txn_line_dtl_tbl(1).LOT_NUMBER,
5283: l_txn_line_dtl_tbl(1).MFG_SERIAL_NUMBER_FLAG;
5284: CLOSE csi_item_instances_csr;
5285: ELSE
5286: l_txn_line_dtl_tbl(1).SERIAL_NUMBER := NULL;
5287: l_txn_line_dtl_tbl(1).INVENTORY_REVISION := NULL;
5288: l_txn_line_dtl_tbl(1).LOT_NUMBER := NULL;
5717: from ahl_osp_order_lines OSPL,
5718: oe_order_lines_all OE,
5719: ahl_workorders wo,
5720: ahl_visit_tasks_b vts,
5721: csi_item_instances csii
5722: where OSPL.osp_order_id = p_osp_order_id
5723: AND OSPL.OE_SHIP_LINE_ID IS NOT NULL
5724: AND OE.line_id = OSPL.OE_SHIP_LINE_ID
5725: AND wo.workorder_id(+) = ospl.workorder_id
6013: RETURN VARCHAR2
6014: IS
6015: CURSOR csi_instance_num_csr(p_instance_id IN NUMBER) IS
6016: SELECT INSTANCE_NUMBER
6017: FROM CSI_ITEM_INSTANCES
6018: WHERE INSTANCE_ID = p_instance_id;
6019:
6020: p_instance_num CSI_ITEM_INSTANCES.INSTANCE_NUMBER%TYPE;
6021: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.GET_INSTANCE_NUM_FROM_ID';
6016: SELECT INSTANCE_NUMBER
6017: FROM CSI_ITEM_INSTANCES
6018: WHERE INSTANCE_ID = p_instance_id;
6019:
6020: p_instance_num CSI_ITEM_INSTANCES.INSTANCE_NUMBER%TYPE;
6021: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.GET_INSTANCE_NUM_FROM_ID';
6022:
6023: BEGIN
6024: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6372: csi.last_vld_organization_id
6373: FROM csi_t_transaction_lines tl,
6374: csi_t_txn_line_details tld,
6375: ahl_osp_order_lines ospl,
6376: csi_item_instances csi
6377: WHERE tl.source_transaction_id = ospl.oe_ship_line_id
6378: AND tl.source_transaction_table = G_TRANSACTION_TABLE
6379: AND tl.transaction_line_id = tld.transaction_line_id
6380: AND ospl.osp_order_line_id = c_osp_order_line_id
6618: AND tl.source_transaction_table = G_TRANSACTION_TABLE
6619: AND tl.transaction_line_id = tld.transaction_line_id
6620: AND ospl.osp_order_line_id = c_osp_order_line_id;
6621:
6622: -- Get the current record from csi item instances
6623: CURSOR get_instance_details (c_instance_id IN NUMBER) IS
6624: SELECT instance_number,
6625: instance_id,
6626: object_version_number,
6627: inventory_item_id,
6628: serial_number,
6629: lot_number,
6630: inventory_revision
6631: FROM csi_item_instances
6632: WHERE instance_id = c_instance_id;
6633:
6634: CURSOR get_item_information(c_inv_item_id IN NUMBER, c_inv_org_id IN NUMBER) IS
6635: SELECT serial_number_control_code,