[Home] [Help]
2085: serial_number,
2086: quantity,
2087: unit_of_measure,
2088: lot_number --jeli 12/01/05
2089: FROM csi_item_instances
2090: WHERE instance_id = p_instance_id;
2091:
2092: --yazhou 06-Jun-2006 starts
2093: --bug fix#5304095
2179:
2180: -- Cursor to check if an instance is independent (not an installed component)
2181: CURSOR ahl_csi_unit_test_csr (p_csi_ii_id IN NUMBER) IS
2182: SELECT 1
2183: FROM CSI_ITEM_INSTANCES CII
2184: WHERE CII.INSTANCE_ID = p_csi_ii_id AND
2185: NOT EXISTS
2186: (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
2187: WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID AND
2399: from ahl_osp_order_lines ospl,
2400: oe_order_lines_all oel,
2401: csi_t_transaction_lines tl,
2402: csi_t_txn_line_details tld,
2403: csi_item_instances csi
2404: where ospl.osp_order_id = p_osp_order_id
2405: AND (NVL(oe_ship_line_id, -9) = NVL(p_line_id, -8)
2406: OR NVL(oe_return_line_id, -9) = NVL(p_line_id, -8))
2407: AND oel.line_id = p_line_id
2438: csii.lot_number
2439: FROM AHL_WORKORDERS wo,
2440: ahl_visits_b vst,
2441: ahl_visit_tasks_b vts,
2442: csi_item_instances csii
2443: WHERE wo.workorder_id = p_workorder_id
2444: AND wo.visit_task_id = vts.visit_task_id(+)
2445: AND vts.visit_id = vst.visit_id(+)
2446: AND vts.instance_id = csii.instance_id(+)
2453: CURSOR get_inv_inst_csr(p_inv_item_id IN NUMBER,
2454: p_inv_org_id IN NUMBER,
2455: p_serial_number IN VARCHAR2) IS
2456: SELECT INSTANCE_ID
2457: FROM csi_item_instances csi
2458: WHERE p_inv_item_id = csi.inventory_item_id
2459: and p_inv_org_id = csi.last_vld_organization_id
2460: and p_serial_number = csi.serial_number;
2461: */
2461: */
2462: CURSOR get_inv_inst_csr(p_inv_item_id IN NUMBER,
2463: p_serial_number IN VARCHAR2) IS
2464: SELECT INSTANCE_ID
2465: FROM csi_item_instances csi
2466: WHERE p_inv_item_id = csi.inventory_item_id
2467: and p_serial_number = csi.serial_number;
2468:
2469: CURSOR get_inv_inst_from_lot_csr(p_inv_item_id IN NUMBER,
2468:
2469: CURSOR get_inv_inst_from_lot_csr(p_inv_item_id IN NUMBER,
2470: p_lot_number IN VARCHAR2) IS
2471: SELECT INSTANCE_ID
2472: FROM csi_item_instances csi
2473: WHERE csi.inventory_item_id = p_inv_item_id
2474: and csi.lot_number = p_lot_number;
2475:
2476: --Modified by mpothuku on 06-Mar-06 to fix the Perf Bug #4919255
2502: csi.lot_number,
2503: csi.quantity,
2504: csi.unit_of_measure,
2505: csi.last_vld_organization_id
2506: FROM ahl_osp_order_lines ospl, csi_item_instances csi
2507: WHERE ospl.osp_order_line_id = p_osp_order_line_id and
2508: csi.instance_id = ospl.exchange_instance_id;
2509:
2510: l_exchange_inst_dtls exchange_instance_dtls_csr%rowtype;
4552: a.PO_REQ_LINE_ID
4553: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4554: FROM AHL_OSP_ORDER_LINES a,
4555: ahl_workorders wo,
4556: csi_item_instances csii,
4557: ahl_visit_tasks_b vts
4558: WHERE a.osp_order_id = p_osp_id
4559: AND wo.workorder_id(+) = a.workorder_id
4560: AND wo.visit_task_id = vts.visit_task_id(+)
4612: a.PO_REQ_LINE_ID
4613: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4614: FROM AHL_OSP_ORDER_LINES a,
4615: ahl_workorders wo,
4616: csi_item_instances csii,
4617: ahl_visit_tasks_b vts
4618: WHERE a.osp_order_id = p_osp_id
4619: AND wo.workorder_id(+) = a.workorder_id
4620: AND wo.visit_task_id = vts.visit_task_id(+)
4719: -- Check if the instance is a valid IB instance
4720: -- Also not part of relationship
4721:
4722: CURSOR val_exg_instance_id_csr(p_instance_id IN NUMBER) IS
4723: SELECT 'x' FROM csi_item_instances csi
4724: WHERE instance_id = p_instance_id
4725: AND nvl(csi.active_end_date, sysdate + 1) > sysdate
4726: AND NOT EXISTS (select subject_id from csi_ii_relationships where
4727: subject_id = p_instance_id and
4988: FROM oe_order_lines_all
4989: WHERE line_id = p_oe_line_id;
4990:
4991: -- get instance details.
4992: CURSOR csi_item_instances_csr (p_instance_id IN NUMBER) IS
4993: SELECT serial_number, inventory_revision, lot_number, mfg_serial_number_flag
4994: FROM csi_item_instances
4995: WHERE instance_id = p_instance_id;
4996:
4990:
4991: -- get instance details.
4992: CURSOR csi_item_instances_csr (p_instance_id IN NUMBER) IS
4993: SELECT serial_number, inventory_revision, lot_number, mfg_serial_number_flag
4994: FROM csi_item_instances
4995: WHERE instance_id = p_instance_id;
4996:
4997: -- get value for return by date.
4998: CURSOR ahl_osp_order_lines_csr (p_oe_line_id IN NUMBER) IS
5320: we are creating the IB transactions even if the instance is not present on the order line for Exchange Return Lines
5321: we do not need to any clauses for exchange orders below.
5322: */
5323: IF(p_csi_instance_id is not null) THEN
5324: OPEN csi_item_instances_csr (p_csi_instance_id);
5325: FETCH csi_item_instances_csr INTO l_txn_line_dtl_tbl(1).SERIAL_NUMBER,
5326: l_txn_line_dtl_tbl(1).INVENTORY_REVISION,
5327: l_txn_line_dtl_tbl(1).LOT_NUMBER,
5328: l_txn_line_dtl_tbl(1).MFG_SERIAL_NUMBER_FLAG;
5321: we do not need to any clauses for exchange orders below.
5322: */
5323: IF(p_csi_instance_id is not null) THEN
5324: OPEN csi_item_instances_csr (p_csi_instance_id);
5325: FETCH csi_item_instances_csr INTO l_txn_line_dtl_tbl(1).SERIAL_NUMBER,
5326: l_txn_line_dtl_tbl(1).INVENTORY_REVISION,
5327: l_txn_line_dtl_tbl(1).LOT_NUMBER,
5328: l_txn_line_dtl_tbl(1).MFG_SERIAL_NUMBER_FLAG;
5329: CLOSE csi_item_instances_csr;
5325: FETCH csi_item_instances_csr INTO l_txn_line_dtl_tbl(1).SERIAL_NUMBER,
5326: l_txn_line_dtl_tbl(1).INVENTORY_REVISION,
5327: l_txn_line_dtl_tbl(1).LOT_NUMBER,
5328: l_txn_line_dtl_tbl(1).MFG_SERIAL_NUMBER_FLAG;
5329: CLOSE csi_item_instances_csr;
5330: ELSE
5331: l_txn_line_dtl_tbl(1).SERIAL_NUMBER := NULL;
5332: l_txn_line_dtl_tbl(1).INVENTORY_REVISION := NULL;
5333: l_txn_line_dtl_tbl(1).LOT_NUMBER := NULL;
5762: from ahl_osp_order_lines OSPL,
5763: oe_order_lines_all OE,
5764: ahl_workorders wo,
5765: ahl_visit_tasks_b vts,
5766: csi_item_instances csii
5767: where OSPL.osp_order_id = p_osp_order_id
5768: AND OSPL.OE_SHIP_LINE_ID IS NOT NULL
5769: AND OE.line_id = OSPL.OE_SHIP_LINE_ID
5770: AND wo.workorder_id(+) = ospl.workorder_id
6058: RETURN VARCHAR2
6059: IS
6060: CURSOR csi_instance_num_csr(p_instance_id IN NUMBER) IS
6061: SELECT INSTANCE_NUMBER
6062: FROM CSI_ITEM_INSTANCES
6063: WHERE INSTANCE_ID = p_instance_id;
6064:
6065: p_instance_num CSI_ITEM_INSTANCES.INSTANCE_NUMBER%TYPE;
6066: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.GET_INSTANCE_NUM_FROM_ID';
6061: SELECT INSTANCE_NUMBER
6062: FROM CSI_ITEM_INSTANCES
6063: WHERE INSTANCE_ID = p_instance_id;
6064:
6065: p_instance_num CSI_ITEM_INSTANCES.INSTANCE_NUMBER%TYPE;
6066: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.GET_INSTANCE_NUM_FROM_ID';
6067:
6068: BEGIN
6069: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6417: csi.last_vld_organization_id
6418: FROM csi_t_transaction_lines tl,
6419: csi_t_txn_line_details tld,
6420: ahl_osp_order_lines ospl,
6421: csi_item_instances csi
6422: WHERE tl.source_transaction_id = ospl.oe_ship_line_id
6423: AND tl.source_transaction_table = G_TRANSACTION_TABLE
6424: AND tl.transaction_line_id = tld.transaction_line_id
6425: AND ospl.osp_order_line_id = c_osp_order_line_id
6663: AND tl.source_transaction_table = G_TRANSACTION_TABLE
6664: AND tl.transaction_line_id = tld.transaction_line_id
6665: AND ospl.osp_order_line_id = c_osp_order_line_id;
6666:
6667: -- Get the current record from csi item instances
6668: CURSOR get_instance_details (c_instance_id IN NUMBER) IS
6669: SELECT instance_number,
6670: instance_id,
6671: object_version_number,
6672: inventory_item_id,
6673: serial_number,
6674: lot_number,
6675: inventory_revision
6676: FROM csi_item_instances
6677: WHERE instance_id = c_instance_id;
6678:
6679: CURSOR get_item_information(c_inv_item_id IN NUMBER, c_inv_org_id IN NUMBER) IS
6680: SELECT serial_number_control_code,