[Home] [Help]
2695: AND POL.PO_HEADER_ID = OO.po_header_id
2696: -- Added by jaramana on January 9, 2008 to fix the Bug 5358438/5967633
2697: AND NVL(POL.CANCEL_FLAG, 'N') <> 'Y'
2698: --the line_id should not have already been associated to the same osp order
2699: AND NOT EXISTS (select 1 from ahl_osp_order_lines
2700: where osp_order_id = p_osp_order_id
2701: and po_line_id = p_po_line_id );
2702: -- jaramana End
2703: l_exist VARCHAR2(1);
2756:
2757: /*
2758: - Changed by jaramana on January 9, 2008 for the Requisition ER 6034236 (We are not using this procedure anymore):
2759: Commented the nullify_exchange_instance procedure and placed it at the end of package. Please note that instead of using this
2760: heavy weight procedure we are directly updating the AHL_OSP_ORDER_LINES table
2761: */
2762:
2763: --------------------------------------------------------------------------------------------------------------
2764: -- Jerry made minor changes to this procedure for ISO in May 27, 2005
2768: PROCEDURE process_order_status_change(
2769: p_x_osp_order_rec IN OUT NOCOPY osp_order_rec_type)
2770: IS
2771: CURSOR val_order_has_lines(p_osp_order_id IN NUMBER)IS
2772: SELECT 'x' FROM ahl_osp_order_lines
2773: WHERE osp_order_id = p_osp_order_id;
2774: l_exist VARCHAR2(1);
2775: CURSOR val_order_has_ship_lines(p_osp_order_id IN NUMBER)IS
2776: SELECT oe_ship_line_id, oe_return_line_id, osp_line_number FROM ahl_osp_order_lines
2772: SELECT 'x' FROM ahl_osp_order_lines
2773: WHERE osp_order_id = p_osp_order_id;
2774: l_exist VARCHAR2(1);
2775: CURSOR val_order_has_ship_lines(p_osp_order_id IN NUMBER)IS
2776: SELECT oe_ship_line_id, oe_return_line_id, osp_line_number FROM ahl_osp_order_lines
2777: WHERE osp_order_id = p_osp_order_id;
2778: l_oe_ship_line_id NUMBER;
2779: l_oe_return_line_id NUMBER;
2780: l_line_number NUMBER;
2788: l_shipment_IDs_Tbl AHL_OSP_SHIPMENT_PUB.Ship_ID_Tbl_Type;
2789: CURSOR val_all_wo_closed(p_osp_order_id IN NUMBER) IS
2790: --Modified by mpothuku on 27-Feb-06 to use ahl_workorders instead of ahl_workorders_osp_v
2791: --to fix the Perf Bug #4919164
2792: SELECT 'x' from ahl_osp_order_lines OL, ahl_workorders WO
2793: WHERE OL.osp_order_id = p_osp_order_id
2794: AND OL.workorder_id = WO.workorder_id
2795: AND OL.status_code IS NULL
2796: AND WO.status_code NOT IN(G_OSP_WO_CANCELLED,G_OSP_WO_CLOSED);
2795: AND OL.status_code IS NULL
2796: AND WO.status_code NOT IN(G_OSP_WO_CANCELLED,G_OSP_WO_CLOSED);
2797: CURSOR val_order_lines_csr(p_osp_order_id IN NUMBER) IS
2798: SELECT osp_line_number, service_item_id, service_item_description, service_item_uom_code, quantity, need_by_date, po_line_type_id
2799: FROM AHL_OSP_ORDER_LINES
2800: WHERE osp_order_id = p_osp_order_id
2801: ORDER BY osp_line_number;
2802: CURSOR get_old_status IS
2803: SELECT status_code, object_version_number
3122: l_return_status VARCHAR2(1);
3123: l_msg_count NUMBER;
3124: l_msg_data VARCHAR2(1000);
3125: CURSOR can_del_ship_line_csr(p_osp_order_id IN NUMBER, p_oe_ship_line_id IN NUMBER) IS
3126: SELECT 'x' FROM ahl_osp_order_lines
3127: WHERE osp_order_id = p_osp_order_id
3128: AND oe_ship_line_id = p_oe_ship_line_id
3129: AND status_code IS NULL;
3130: CURSOR can_del_return_line_csr(p_osp_order_id IN NUMBER, p_oe_return_line_id IN NUMBER) IS
3127: WHERE osp_order_id = p_osp_order_id
3128: AND oe_ship_line_id = p_oe_ship_line_id
3129: AND status_code IS NULL;
3130: CURSOR can_del_return_line_csr(p_osp_order_id IN NUMBER, p_oe_return_line_id IN NUMBER) IS
3131: SELECT 'x' FROM ahl_osp_order_lines
3132: WHERE osp_order_id = p_osp_order_id
3133: AND oe_return_line_id = p_oe_return_line_id
3134: AND status_code IS NULL;
3135: CURSOR line_already_shipped_csr(p_oe_line_id IN NUMBER) IS
3275: --p_new_order_rec IN AHL_OSP_ORDERS_PVT.osp_order_rec_type,
3276: --p_old_order_rec IN AHL_OSP_ORDERS_PVT.osp_order_rec_type
3277: ) RETURN BOOLEAN IS
3278: CURSOR order_has_ship_return_csr(p_osp_order_id IN NUMBER) IS
3279: SELECT 'x' FROM ahl_osp_order_lines ol --, oe_order_lines_all oel
3280: WHERE ol.osp_order_id = p_osp_order_id
3281: AND ol.oe_return_line_id IS NOT NULL; --= oel.line_id;
3282: CURSOR order_header_status_csr(p_osp_order_id IN NUMBER) IS
3283: SELECT status_code FROM ahl_osp_orders_b
3428: l_header_site_id NUMBER;
3429: i NUMBER;
3430: CURSOR get_order_lines(c_osp_order_id NUMBER) IS
3431: SELECT osp_order_line_id
3432: FROM ahl_osp_order_lines
3433: WHERE osp_order_id = c_osp_order_id;
3434: BEGIN
3435: --Initialize API return status to success
3436: x_return_status := FND_API.G_RET_STS_SUCCESS;
3512: --Front end just passes osp_order_line_id and operation_flag
3513: BEGIN
3514: SELECT osp_order_id, oe_ship_line_id, oe_return_line_id
3515: INTO l_osp_order_id, l_oe_ship_line_id, l_oe_return_line_id
3516: FROM ahl_osp_order_lines
3517: WHERE osp_order_line_id = p_x_osp_order_lines_tbl(i).osp_order_line_id;
3518:
3519: SELECT status_code, oe_header_id INTO l_status_code, l_oe_header_id
3520: FROM ahl_osp_orders_b
3536: END IF;
3537:
3538: --Delete OSP line record first (it makes more sense to delete shipment lines first, but
3539: --here just keep it the same as the old logic).
3540: AHL_OSP_ORDER_LINES_PKG.delete_row(p_x_osp_order_lines_tbl(i).osp_order_line_id);
3541:
3542: --Delete or cancel shipment lines
3543: delete_cancel_so(
3544: p_oe_header_id => NULL,
3653: FOR l_get_order_lines IN get_order_lines(p_x_osp_order_rec.osp_order_id) LOOP
3654: BEGIN
3655: SELECT oe_ship_line_id, oe_return_line_id
3656: INTO l_oe_ship_line_id, l_oe_return_line_id
3657: FROM ahl_osp_order_lines
3658: WHERE osp_order_line_id = l_get_order_lines.osp_order_line_id;
3659: EXCEPTION
3660: WHEN NO_DATA_FOUND THEN
3661: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3682: END IF;
3683:
3684: --Delete OSP order lines
3685: FOR l_get_order_lines IN get_order_lines(p_x_osp_order_rec.osp_order_id) LOOP
3686: AHL_OSP_ORDER_LINES_PKG.delete_row(l_get_order_lines.osp_order_line_id);
3687: END LOOP;
3688:
3689: --Delete OSP order header record
3690: AHL_OSP_ORDERS_PKG.delete_row(x_osp_order_id => p_x_osp_order_rec.osp_order_id);
3717: --Front end just passes osp_order_line_id and operation_flag
3718: BEGIN
3719: SELECT osp_order_id, oe_ship_line_id, oe_return_line_id
3720: INTO l_osp_order_id, l_oe_ship_line_id, l_oe_return_line_id
3721: FROM ahl_osp_order_lines
3722: WHERE osp_order_line_id = p_x_osp_order_lines_tbl(i).osp_order_line_id;
3723:
3724: SELECT status_code, oe_header_id INTO l_status_code, l_oe_header_id
3725: FROM ahl_osp_orders_b
3742: END IF;
3743:
3744: --Delete line record first (it makes more sense to delete shipment lines first, but
3745: --here just keep it the same as the old logic.
3746: AHL_OSP_ORDER_LINES_PKG.delete_row(p_x_osp_order_lines_tbl(i).osp_order_line_id);
3747: --Delete or cancel Shipment lines
3748: delete_cancel_so(
3749: p_oe_header_id => NULL,
3750: p_del_cancel_so_lines_tbl => l_del_cancel_so_lines_tbl);
3752: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
3753: FND_MSG_PUB.ADD;
3754: RAISE FND_API.G_EXC_ERROR;
3755: END IF;
3756: --AHL_OSP_ORDER_LINES_PKG.delete_row(p_x_osp_order_lines_tbl(i).osp_order_line_id);
3757: ELSIF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_UPDATE) THEN
3758: --update osp order line
3759: update_osp_order_line(p_x_osp_order_lines_tbl(i));
3760: ELSIF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_CREATE) THEN
4245: l_oe_header_id NUMBER;
4246: CURSOR get_all_lines(c_osp_order_id NUMBER) IS
4247: SELECT osp_order_line_id, inventory_item_id,
4248: inventory_org_id, service_item_id, workorder_id
4249: FROM ahl_osp_order_lines
4250: WHERE osp_order_id = c_osp_order_id;
4251: CURSOR get_old_vendor_attrs(c_osp_order_id NUMBER) IS
4252: SELECT vendor_id, vendor_site_id, oe_header_id
4253: FROM ahl_osp_orders_b
4466:
4467: --yazhou 22-Aug-2006 ends
4468:
4469: --Generate the primary key
4470: SELECT ahl_osp_order_lines_s.NEXTVAL
4471: INTO l_osp_line_id
4472: FROM sys.dual;
4473:
4474: --Generate the line number
4476: --index defined on osp_line_number if two concurrent users try to add osp order line
4477: --to the same OSP Order headet at the same time.
4478: SELECT NVL(MAX(osp_line_number), 0)+1
4479: INTO l_osp_line_number
4480: FROM ahl_osp_order_lines
4481: WHERE osp_order_id = p_x_osp_order_line_rec.osp_order_id;
4482:
4483: /*
4484: Changed by jaramana on January 10, 2008
4622: -- bug fix#5376907
4623: -- Service item qty should be the same as physical item qty at the time of creation
4624:
4625: --Call table handler to insert the line
4626: AHL_OSP_ORDER_LINES_PKG.insert_row(
4627: p_x_osp_order_line_id => l_osp_line_id,
4628: p_object_version_number => 1,
4629: p_created_by => fnd_global.user_id,
4630: p_creation_date => SYSDATE,
4689: p_x_osp_order_line_rec.ship_by_date := SYSDATE;
4690: END IF;
4691:
4692: --Call table handler to insert the line
4693: AHL_OSP_ORDER_LINES_PKG.insert_row(
4694: p_x_osp_order_line_id => l_osp_line_id,
4695: p_object_version_number => 1,
4696: p_created_by => fnd_global.user_id,
4697: p_creation_date => SYSDATE,
4774: c_inventory_org_id NUMBER,
4775: c_serial_number VARCHAR2,
4776: c_osp_order_id NUMBER) IS
4777: SELECT 'X'
4778: FROM ahl_osp_order_lines
4779: WHERE inventory_item_id = c_inventory_item_id
4780: AND inventory_org_id = c_inventory_org_id
4781: AND serial_number = c_serial_number
4782: AND osp_order_id = c_osp_order_id
5020: c_inv_org_id NUMBER,
5021: c_lot_number VARCHAR2,
5022: c_serial_number VARCHAR2) IS
5023: SELECT 'X'
5024: FROM ahl_osp_order_lines
5025: WHERE osp_order_id = c_osp_order_id
5026: AND ((service_item_id = c_service_item_id) OR
5027: (service_item_id IS NULL AND c_service_item_id IS NULL))
5028: AND inventory_item_id = c_inv_item_id
5030: AND ((lot_number IS NULL AND c_lot_number IS NULL) OR (lot_number = c_lot_number))
5031: AND ((serial_number IS NULL AND c_serial_number IS NULL) OR (serial_number = c_serial_number));
5032: CURSOR check_phy_item_unique(c_osp_order_id NUMBER, c_inv_item_id NUMBER) IS
5033: SELECT 'X'
5034: FROM ahl_osp_order_lines
5035: WHERE inventory_item_id = c_inv_item_id
5036: AND osp_order_id = c_osp_order_id;
5037:
5038: --yazhou 22-Aug-2006 starts
5041: /*
5042: CURSOR check_osp_order_unique(c_osp_order_id NUMBER, c_inventory_item_id NUMBER,
5043: c_inventory_org_id NUMBER, c_serial_number VARCHAR2) IS
5044: SELECT 'X'
5045: FROM ahl_osp_order_lines ospl, ahl_osp_orders_b osp
5046: WHERE ospl.osp_order_id = osp.osp_order_id
5047: AND osp.status_code <> 'CLOSED'
5048: AND ospl.status_code is null
5049: AND ospl.inventory_item_id = c_inventory_item_id
5054: -- Added by jaramana on January 10, 2008 for the Bug 5547870/5673279
5055: CURSOR check_osp_order_unique(c_osp_order_id NUMBER, c_inventory_item_id NUMBER,
5056: c_inventory_org_id NUMBER, c_serial_number VARCHAR2) IS
5057: SELECT 'X'
5058: FROM ahl_osp_order_lines ospl, ahl_osp_orders_b osp, oe_order_lines_all oelship
5059: WHERE ospl.osp_order_id = osp.osp_order_id
5060: AND osp.status_code <> 'CLOSED'
5061: AND oelship.LINE_ID = ospl.OE_SHIP_LINE_ID
5062: --The order line should not be closed and should not be cancelled to be considered in the uniqueness check
5155: --If we are creating an osp order line based on PO line that is created from the Purchasing forms, then
5156: --the work order selected for the order Line, should not be associated with any open order lines
5157: --Note that instead of status_code not in, we could have use status_code is null as well.
5158: AND not exists (select 1
5159: from ahl_osp_order_lines
5160: where workorder_id =p_x_osp_order_line_rec.workorder_id
5161: and nvl(status_code, 'X') not in (G_OL_PO_CANCELLED_STATUS, G_OL_PO_DELETED_STATUS, G_OL_REQ_CANCELLED_STATUS, G_OL_REQ_DELETED_STATUS));
5162: -- jaramana End
5163: ELSE
5519: l_service_duration NUMBER;
5520: BEGIN
5521: validate_order_line_update(p_x_osp_order_line_rec);
5522: --call table handler to update the record
5523: AHL_OSP_ORDER_LINES_PKG.update_row(
5524: p_osp_order_line_id => p_x_osp_order_line_rec.osp_order_line_id,
5525: p_object_version_number => p_x_osp_order_line_rec.object_version_number +1,
5526: p_osp_order_id => p_x_osp_order_line_rec.osp_order_id,
5527: p_osp_line_number => p_x_osp_order_line_rec.osp_line_number,
5585: l_item_description VARCHAR2(240);
5586: l_desc_update_flag VARCHAR2(1);
5587: CURSOR osp_order_line_csr IS
5588: SELECT *
5589: FROM ahl_osp_order_lines
5590: WHERE osp_order_line_id = p_x_osp_order_line_rec.osp_order_line_id
5591: AND object_version_number= p_x_osp_order_line_rec.object_version_number;
5592: l_osp_order_line_rec osp_order_line_csr%ROWTYPE;
5593: CURSOR check_phy_ser_item_unique(c_osp_order_id NUMBER,
5596: c_inv_org_id NUMBER,
5597: c_lot_number VARCHAR2,
5598: c_serial_number VARCHAR2) IS
5599: SELECT 'X'
5600: FROM ahl_osp_order_lines
5601: WHERE osp_order_id = c_osp_order_id
5602: AND ((service_item_id = c_service_item_id) OR
5603: (service_item_id IS NULL AND c_service_item_id IS NULL))
5604: AND inventory_item_id = c_inv_item_id
5606: AND ((lot_number IS NULL AND c_lot_number IS NULL) OR (lot_number = c_lot_number))
5607: AND ((serial_number IS NULL AND c_serial_number IS NULL) OR (serial_number = c_serial_number));
5608: CURSOR check_phy_item_unique(c_osp_order_id NUMBER, c_inv_item_id NUMBER) IS
5609: SELECT 'X'
5610: FROM ahl_osp_order_lines
5611: WHERE inventory_item_id = c_inv_item_id
5612: AND osp_order_id = c_osp_order_id;
5613: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.validate_order_line_update';
5614: --Added by mpothuku on 24-Mar-06 for ER: 4544654
5936: --validate sercice_item_uom (just borrowed the old one)
5937: validate_service_item_uom(p_x_osp_order_line_rec.service_item_id, p_x_osp_order_line_rec.service_item_uom_code, p_x_osp_order_line_rec.inventory_org_id);
5938: --validate sercice_item_quantity(quantity)
5939: --Service item quantity could be from the quantity of PO lines and not necessary to be the same as
5940: --as the value in ahl_osp_order_lines when PO has ever been created
5941: --created
5942: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
5943: --Moved the logic below, to above
5944: /*
6182: x_valid_vendors_tbl OUT NOCOPY Vendor_id_tbl_type,
6183: x_any_vendor_flag OUT NOCOPY VARCHAR2) IS
6184:
6185: CURSOR item_service_dtls_csr IS
6186: SELECT INVENTORY_ORG_ID, INVENTORY_ITEM_ID, SERVICE_ITEM_ID FROM ahl_osp_order_lines
6187: WHERE osp_order_id = p_osp_order_id AND
6188: service_item_id IS NOT NULL;
6189:
6190: CURSOR is_vendor_defined_csr (c_inventory_item_id IN NUMBER,
6431: attribute12,
6432: attribute13,
6433: attribute14,
6434: attribute15
6435: FROM ahl_osp_order_lines_v
6436: WHERE osp_order_line_id = p_x_osp_order_line_rec.osp_order_line_id
6437: AND object_version_number= p_x_osp_order_line_rec.object_version_number;
6438: */
6439: SELECT ospl.osp_order_id,
6496: ospl.attribute12,
6497: ospl.attribute13,
6498: ospl.attribute14,
6499: ospl.attribute15
6500: FROM ahl_osp_order_lines ospl,
6501: ahl_workorders wo,
6502: ahl_visits_b vst,
6503: ahl_visit_tasks_b vts,
6504: csi_item_instances csiwo,
6923: AND NVL(outside_operation_flag, G_NO_FLAG) = G_NO_FLAG;
6924: l_po_line_type_id NUMBER;
6925: CURSOR osp_order_line_on_csr(p_osp_order_id IN NUMBER, p_osp_line_number IN NUMBER) IS
6926: SELECT osp_order_line_id
6927: FROM ahl_osp_order_lines
6928: WHERE osp_order_id = p_osp_order_id
6929: and osp_line_number = p_osp_line_number;
6930: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
6931: -- We are not planning to derive the OSP_ORDER_LINE_ID from PO_LINE_ID
6931: -- We are not planning to derive the OSP_ORDER_LINE_ID from PO_LINE_ID
6932: /*
6933: CURSOR osp_order_line_po_csr(p_po_line_id IN NUMBER) IS
6934: SELECT osp_order_line_id
6935: FROM ahl_osp_order_lines
6936: WHERE po_line_id = p_po_line_id;
6937: */
6938: -- jaramana End
6939: l_osp_order_line_id NUMBER;
7269: WHERE osp_order_id = p_osp_order_rec.osp_order_id;
7270: IF l_old_type_code <> p_osp_order_rec.order_type_code THEN
7271: IF(can_convert_order(p_osp_order_rec.osp_order_id, l_old_type_code, p_osp_order_rec.order_type_code)) THEN
7272: IF(p_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_SERVICE) THEN
7273: UPDATE ahl_osp_order_lines
7274: SET exchange_instance_id = NULL,
7275: last_update_date = SYSDATE,
7276: last_updated_by = fnd_global.user_id,
7277: last_update_login = fnd_global.login_id,
7312: osp_order_id, osp_line_number, status_code , po_line_type_id, service_item_id, service_item_description , service_item_uom_code,
7313: need_by_date, ship_by_date, po_line_id, oe_ship_line_id , oe_return_line_id , workorder_id, operation_id,
7314: quantity, exchange_instance_id, attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6,
7315: attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
7316: FROM ahl_osp_order_lines
7317: WHERE osp_order_id = p_osp_order_id;
7318: l_osp_order_lines_rec osp_order_line_rec_type;
7319: CURSOR l_order_line_obj_ver_csr(p_osp_order_line_id IN NUMBER) IS
7320: SELECT object_version_number FROM ahl_osp_order_lines
7316: FROM ahl_osp_order_lines
7317: WHERE osp_order_id = p_osp_order_id;
7318: l_osp_order_lines_rec osp_order_line_rec_type;
7319: CURSOR l_order_line_obj_ver_csr(p_osp_order_line_id IN NUMBER) IS
7320: SELECT object_version_number FROM ahl_osp_order_lines
7321: WHERE osp_order_line_id = p_osp_order_line_id;
7322: i NUMBER; --index for looping
7323: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.nullify_exchange_instance';
7324: BEGIN
7366: l_osp_order_lines_rec.attribute15 ;
7367: IF(l_order_lines_csr %NOTFOUND) THEN
7368: EXIT;
7369: END IF;
7370: AHL_OSP_ORDER_LINES_PKG.update_row(
7371: p_osp_order_line_id => l_osp_order_lines_rec.osp_order_line_id,
7372: p_object_version_number => l_osp_order_lines_rec.object_version_number + 1,
7373: p_last_update_date => l_osp_order_lines_rec.last_update_date,
7374: p_last_updated_by => l_osp_order_lines_rec.last_updated_by,