[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_OSP_Line_Exch_Instance(
p_osp_order_id IN NUMBER,
p_osp_line_id IN NUMBER,
p_exchange_instance_id IN NUMBER);
PROCEDURE Update_OSP_Order_Lines(
p_osp_order_id IN NUMBER,
p_osp_line_id IN NUMBER,
p_oe_ship_line_id IN NUMBER,
p_oe_return_line_id IN NUMBER);
SELECT oe_header_id
FROM AHL_OSP_ORDERS_B
WHERE osp_order_id = c_osp_order_id;
SELECT 'X'
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = c_oe_header_id
AND booked_flag = 'Y';
SELECT OLA.line_id
FROM OE_ORDER_LINES_ALL OLA, OE_LINE_TYPES_V OLT
WHERE OLA.header_id = c_oe_header_id
AND OLT.line_type_id = OLA.line_type_id
AND OLT.order_category_code = 'RETURN';
SELECT 'X'
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id
AND line_type_id = FND_PROFILE.VALUE('AHL_OSP_OE_RETURN_ID');
SELECT ordered_quantity, shipped_quantity
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id;
SELECT SUM(ordered_quantity), SUM(shipped_quantity)
FROM OE_ORDER_LINES_ALL
WHERE header_id = c_oe_header_id
AND line_number = c_oe_line_number
AND NVL(cancelled_flag, 'X') <> 'Y';
SELECT header_id, line_number
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id;
SELECT AOB.po_header_id,
AOB.po_req_header_id,
AOL.osp_order_id
FROM AHL_OSP_ORDER_LINES AOL, AHL_OSP_ORDERS_B AOB
WHERE AOL.oe_return_line_id = c_oe_return_line_id
AND AOL.osp_order_id = AOB.osp_order_id
AND AOB.status_code IN ('PO_CREATED', 'REQ_CREATED')
AND ROWNUM = 1;
SELECT 'X'
FROM PO_HEADERS_ALL
WHERE NVL(approved_flag, 'N') = 'Y'
AND po_header_id = c_po_header_id;
SELECT 'X'
FROM PO_REQUISITION_HEADERS_ALL
WHERE NVL(authorization_status, 'X') = 'APPROVED'
AND requisition_header_id = c_po_req_header_id;
SELECT AOL.po_line_id,
AOL.po_req_line_id,
POL.quantity,
(SELECT SUM(PLL.quantity_received)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = POL.po_line_id) quantity_received
FROM AHL_OSP_ORDER_LINES AOL, PO_LINES_ALL POL
WHERE AOL.oe_return_line_id = c_oe_return_line_id
AND POL.po_line_id(+) = AOL.po_line_id
AND NVL(AOL.status_code, 'X') <> 'PO_DELETED'
AND NVL(AOL.status_code, 'X') <> 'PO_CANCELLED'
AND NVL(AOL.status_code, 'X') <> 'REQ_DELETED'
AND NVL(AOL.status_code, 'X') <> 'REQ_CANCELLED';
SELECT POL.quantity,
(SELECT SUM(PLL.quantity_received)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = POL.po_line_id) quantity_received
FROM PO_LINES_ALL POL
WHERE POL.po_line_id = c_po_line_id;
SELECT PLL.po_line_id
FROM PO_LINE_LOCATIONS_ALL PLL, PO_REQUISITION_LINES REQ,
PO_HEADERS_ALL POH
WHERE REQ.requisition_line_id = c_po_req_line_id
AND PLL.line_location_id = REQ.line_location_id
AND PLL.po_header_id = POH.po_header_id
AND NVL(POH.approved_flag, 'N') = 'Y';
SELECT 'X'
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id
AND line_type_id = FND_PROFILE.VALUE('AHL_OSP_OE_RETURN_ID');
SELECT OHA.header_id
FROM OE_ORDER_LINES_ALL OLA, OE_ORDER_HEADERS_ALL OHA
WHERE OLA.line_id = c_oe_line_id
AND OHA.header_id = OLA.header_id
AND OHA.booked_flag = 'Y';
SELECT oe_ship_line_id
FROM AHL_OSP_ORDER_LINES
WHERE oe_return_line_id = c_oe_return_line_id
AND ROWNUM = 1;
SELECT ordered_quantity, shipped_quantity
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id;
SELECT 'X'
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id
AND line_type_id = FND_PROFILE.VALUE('AHL_OSP_OE_RETURN_ID');
SELECT OHA.header_id
FROM OE_ORDER_LINES_ALL OLA, OE_ORDER_HEADERS_ALL OHA
WHERE OLA.line_id = c_oe_line_id
AND OHA.header_id = OLA.header_id
AND OHA.booked_flag = 'Y';
SELECT oe_ship_line_id
FROM AHL_OSP_ORDER_LINES
WHERE oe_return_line_id = c_oe_return_line_id
AND ROWNUM = 1;
SELECT ordered_quantity, shipped_quantity
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id;
SELECT INVENTORY_ITEM_ID, SHIP_FROM_ORG_ID
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id;
SELECT AVCV.Warranty_flag
FROM ahl_osp_orders_b OSP, AHL_VENDOR_CERTIFICATIONS_V AVCV
WHERE OSP.VENDOR_ID = AVCV.VENDOR_ID
AND OSP.OSP_ORDER_ID = c_osp_order_id;
SELECT COMMS_NL_TRACKABLE_FLAG
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = c_inventory_item_id
AND ORGANIZATION_ID = c_inventory_org_id;
SELECT AOB.po_header_id,
AOB.po_req_header_id,
AOB.osp_order_number
FROM AHL_OSP_ORDER_LINES AOL, AHL_OSP_ORDERS_B AOB
WHERE AOL.oe_return_line_id = c_oe_return_line_id
AND AOL.osp_order_id = AOB.osp_order_id
AND AOB.status_code IN ('PO_CREATED', 'REQ_CREATED')
AND ROWNUM = 1;
SELECT vendor_id, vendor_site_id
FROM PO_HEADERS_ALL
WHERE NVL(approved_flag, 'N') = 'Y'
AND po_header_id = c_po_header_id;
SELECT POH.vendor_id, POH.vendor_site_id
FROM PO_HEADERS_ALL POH, PO_LINES_ALL POL
WHERE POL.po_line_id = c_po_line_id
AND POH.po_header_id = POL.po_header_id
AND ROWNUM = 1;
SELECT 'X'
FROM PO_REQUISITION_HEADERS_V
WHERE NVL(authorization_status, 'X') = 'APPROVED'
AND requisition_header_id = c_po_req_header_id;
SELECT AOL.po_line_id,
AOL.po_req_line_id,
POL.quantity,
(SELECT SUM(PLL.quantity_received)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = POL.po_line_id) quantity_received
FROM AHL_OSP_ORDER_LINES AOL, PO_LINES_ALL POL
WHERE AOL.oe_return_line_id = c_oe_return_line_id
AND POL.po_line_id(+) = AOL.po_line_id
AND NVL(AOL.status_code, 'X') <> 'PO_DELETED'
AND NVL(AOL.status_code, 'X') <> 'PO_CANCELLED'
AND NVL(AOL.status_code, 'X') <> 'REQ_DELETED'
AND NVL(AOL.status_code, 'X') <> 'REQ_CANCELLED';
SELECT POL.quantity,
(SELECT SUM(PLL.quantity_received)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = POL.po_line_id) quantity_received
FROM PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL
WHERE POL.po_line_id = c_po_line_id;
SELECT PLL.po_line_id
FROM PO_LINE_LOCATIONS_ALL PLL, PO_REQUISITION_LINES REQ,
PO_HEADERS_ALL POH
WHERE REQ.requisition_line_id = c_po_req_line_id
AND PLL.line_location_id = REQ.line_location_id
AND PLL.po_header_id = POH.po_header_id
AND NVL(POH.approved_flag, 'N') = 'Y';
SELECT 'X'
FROM RCV_TRANSACTIONS_INTERFACE
WHERE po_line_id = c_po_line_id
AND processing_status_code = 'PENDING';
SELECT ship_to_organization_id
FROM PO_LINE_LOCATIONS_ALL
WHERE po_line_id = c_po_line_id;
SELECT ship_to_location_id
FROM PO_LINE_LOCATIONS_ALL
WHERE po_line_id = c_po_line_id;
l_hdr_inserted BOOLEAN := FALSE;
IF (NOT l_hdr_inserted) THEN
-- Set the l_hdr_inserted flag.
l_hdr_inserted := TRUE;
INSERT INTO RCV_HEADERS_INTERFACE(
HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
VENDOR_ID,
VENDOR_SITE_ID,
SHIP_TO_ORGANIZATION_ID
) VALUES (
PO.RCV_HEADERS_INTERFACE_S.NEXTVAL,
PO.RCV_INTERFACE_GROUPS_S.NEXTVAL,
'PENDING',
'VENDOR',
'NEW',
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_po_vendor_id,
l_po_vendor_site_id,
l_ship_to_org_id
);
'Transaction header inserted.');
END IF; -- l_hdr_inserted check
INSERT INTO RCV_TRANSACTIONS_INTERFACE(
INTERFACE_TRANSACTION_ID,
HEADER_INTERFACE_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
AUTO_TRANSACT_CODE,
RECEIPT_SOURCE_CODE,
SOURCE_DOCUMENT_CODE,
VALIDATION_FLAG,
PO_HEADER_ID,
PO_LINE_ID,
SHIP_TO_LOCATION_ID
) VALUES (
PO.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
PO.RCV_HEADERS_INTERFACE_S.CURRVAL,
PO.RCV_INTERFACE_GROUPS_S.CURRVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
'RECEIVE',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
l_po_diff_qty,
'RECEIVE',
'VENDOR',
'PO',
'Y',
l_po_header_id,
l_po_line_id,
l_ship_to_loc_id
);
'Transaction record inserted for PO line id: '||l_po_line_id);
SELECT PO.RCV_INTERFACE_GROUPS_S.CURRVAL INTO l_temp FROM DUAL;
SELECT inventory_item_id,
sold_to_org_id customer_id,
ship_to_org_id customer_site_id,
ship_from_org_id organization_id,
subinventory,
header_id oe_order_header_id,
line_id oe_order_line_id
FROM oe_order_lines_all
WHERE line_id = c_oe_line_id;
SELECT serial_number_control_code,
lot_control_code,
nvl(comms_nl_trackable_flag,'N')
FROM mtl_system_items_b
WHERE inventory_item_id = c_inv_item_id
AND organization_id = c_org_id;
SELECT tld.instance_id,
csi.inventory_item_id,
csi.serial_number,
csi.lot_number
FROM csi_t_transaction_lines tl,
csi_t_txn_line_details tld,
csi_item_instances csi
WHERE tl.source_transaction_id = c_oe_line_id
AND tl.source_transaction_table = 'OE_ORDER_LINES_ALL'
AND tl.transaction_line_id = tld.transaction_line_id
AND tld.instance_id = csi.instance_id;
SELECT osp.osp_order_id,
osp.order_type_code,
oel.source_document_line_id osp_line_id,
osp.object_version_number
FROM oe_order_lines_all oel,
ahl_osp_orders_b osp
WHERE oel.header_id = osp.oe_header_id
AND oel.line_id = c_oe_line_id;
SELECT inventory_item_id,
serial_number,
lot_number,
exchange_instance_id
FROM ahl_osp_order_lines
WHERE osp_order_line_id = c_osp_line_id;
SELECT lot_number,
from_serial_number serial_number
FROM oe_lot_serial_numbers
WHERE line_id = c_oe_line_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_inv_item_id
AND rownum = 1;
SELECT oe_return_line_id
FROM ahl_osp_order_lines
WHERE osp_order_line_id = c_osp_line_id;
SELECT instance_id
FROM csi_item_instances
WHERE inventory_item_id = c_inv_item_id
AND serial_number = c_serial_number;
SELECT 'X'
FROM RCV_TRANSACTIONS_INTERFACE
WHERE oe_order_line_id = c_oe_line_id
AND processing_status_code = 'PENDING';
SELECT interface_transaction_id, header_interface_id
FROM RCV_TRANSACTIONS_INTERFACE
WHERE oe_order_line_id = c_oe_line_id
AND processing_status_code = 'ERROR';
SELECT matched_ol.osp_order_line_id
FROM ahl_osp_order_lines matched_ol,
ahl_osp_order_lines passed_ol
WHERE passed_ol.osp_order_line_id = c_osp_order_line_id
AND passed_ol.inventory_item_id = matched_ol.inventory_item_id
AND passed_ol.serial_number = matched_ol.serial_number
--modfication by jrotich 08/22/2012 in order to fix bug 14526440
--modification restricts search to same osp order
AND passed_ol.osp_order_id = matched_ol.osp_order_id;
l_ib_trans_deleted boolean;
l_ib_trans_deleted := false;
AHL_OSP_SHIPMENT_PUB.Delete_IB_Transaction(
p_init_msg_list => FND_API.G_FALSE, --p_init_msg_list,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_oe_line_id => p_rma_receipt_rec.return_line_id);
l_ib_trans_deleted := true;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before calling the Delete_Cancel_Order');
AHL_OSP_SHIPMENT_PUB.Delete_Cancel_Order (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE, -- Don't initialize the Message List
p_commit => FND_API.G_FALSE, -- Don't commit independently
p_oe_header_id => null, -- Not deleting the shipment header: Only the lines
p_oe_lines_tbl => l_del_oe_lines_tbl, -- Lines to be deleted/Cancelled
p_cancel_flag => FND_API.G_FALSE, -- Do Deletes if possible, Cancels if not
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from Delete_Cancel_Order, l_return_status = ' || l_return_status);
IF(l_ib_trans_deleted) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_ib_trans_deleted: true');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_ib_trans_deleted: false');
/* Update the osp_line with the new RMA line id*/
OPEN get_same_phyitem_order_lines(l_osp_line_id);
Update_OSP_Order_Lines(
p_osp_order_id => l_osp_order_id,
p_osp_line_id => l_same_ser_ospline_id,
p_oe_ship_line_id => FND_API.G_MISS_NUM ,
p_oe_return_line_id => x_return_line_id);
IF(l_is_ib_trackable = 'Y' AND (l_rma_line_canceled OR l_ib_trans_deleted)) THEN
/* Update the osp_line with the new exchange instance id*/
OPEN get_same_phyitem_order_lines(l_osp_line_id);
Update_OSP_Line_Exch_Instance(
p_osp_order_id => l_osp_order_id,
p_osp_line_id => l_same_ser_ospline_id,
p_exchange_instance_id => l_derived_instance_id);
END IF;--IF(l_is_ib_trackable = 'Y' AND (l_rma_line_canceled OR l_ib_trans_deleted)) THEN
DELETE FROM PO_INTERFACE_ERRORS
WHERE INTERFACE_LINE_ID = l_err_intf_trans_id
AND INTERFACE_HEADER_ID = l_err_intf_hdr_id;
DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE PRODUCT_TRANSACTION_ID = l_err_intf_trans_id;
DELETE FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE PRODUCT_TRANSACTION_ID = l_err_intf_trans_id;
DELETE FROM RCV_TRANSACTIONS_INTERFACE
WHERE INTERFACE_TRANSACTION_ID = l_err_intf_trans_id;
DELETE FROM RCV_HEADERS_INTERFACE
WHERE HEADER_INTERFACE_ID = l_err_intf_hdr_id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Deleted the pending transactions');
SELECT PO.RCV_HEADERS_INTERFACE_S.NEXTVAL INTO l_intf_hdr_id FROM sys.dual;
SELECT PO.RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_group_id FROM sys.dual;
SELECT PO.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL INTO l_intf_transaction_id from sys.dual;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL INTO l_mtl_transaction_id from sys.dual;
SELECT FND_GLOBAL.USER_ID INTO l_user_id from sys.dual;
SELECT FND_GLOBAL.LOGIN_ID INTO l_login_id from sys.dual;
SELECT FND_GLOBAL.EMPLOYEE_ID INTO l_employee_id from sys.dual;
INSERT INTO RCV_HEADERS_INTERFACE
(
HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
CUSTOMER_ID,
CUSTOMER_SITE_ID,
SHIP_TO_ORGANIZATION_ID,
AUTO_TRANSACT_CODE,
EMPLOYEE_ID
)
VALUES
(
l_intf_hdr_id, --HEADER_INTERFACE_ID,
l_group_id, --GROUP_ID,
'PENDING', --PROCESSING_STATUS_CODE,
'CUSTOMER', --RECEIPT_SOURCE_CODE,
'NEW', --TRANSACTION_TYPE,
SYSDATE, --LAST_UPDATE_DATE,
l_user_id, --LAST_UPDATED_BY,
l_login_id, --LAST_UPDATE_LOGIN,
SYSDATE, --CREATION_DATE,
l_user_id, --CREATED_BY,
l_oe_order_line_rec.customer_id, --CUSTOMER_ID,
l_oe_order_line_rec.customer_site_id, --CUSTOMER_SITE_ID
l_oe_order_line_rec.organization_id, --SHIP_TO_ORGANIZATION_ID
'DELIVER', --AUTO_TRANSACT_CODE
l_employee_id --EMPLOYEE_ID
);
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(
INTERFACE_TRANSACTION_ID,
HEADER_INTERFACE_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
AUTO_TRANSACT_CODE,
RECEIPT_SOURCE_CODE,
SOURCE_DOCUMENT_CODE,
VALIDATION_FLAG,
OE_ORDER_HEADER_ID,
OE_ORDER_LINE_ID,
TO_ORGANIZATION_ID,
SUBINVENTORY,
LOCATOR_ID,
INTERFACE_SOURCE_CODE,
UOM_CODE
)
VALUES
(
l_intf_transaction_id, --INTERFACE_TRANSACTION_ID,
l_intf_hdr_id, --HEADER_INTERFACE_ID,
l_group_id, --GROUP_ID,
SYSDATE, --LAST_UPDATE_DATE,
l_user_id, --LAST_UPDATED_BY,
SYSDATE, --CREATION_DATE,
l_user_id, --CREATED_BY,
l_login_id, --LAST_UPDATE_LOGIN,
'RECEIVE', --TRANSACTION_TYPE,
p_rma_receipt_rec.receipt_date, --TRANSACTION_DATE,
'PENDING', --PROCESSING_STATUS_CODE,
--Modified by mpothuku on 04-Mar-2007 for the Bug 6862891
'BATCH', --PROCESSING_MODE_CODE,
'PENDING', --TRANSACTION_STATUS_CODE,
p_rma_receipt_rec.receipt_quantity, --QUANTITY,
'DELIVER', --AUTO_TRANSACT_CODE: 'DELIVER' is needed to ensure delivery of the receipt
'CUSTOMER', --RECEIPT_SOURCE_CODE,
'RMA', --SOURCE_DOCUMENT_CODE,
'Y', --VALIDATION_FLAG,
l_oe_order_line_rec.oe_order_header_id, --OE_ORDER_HEADER_ID,
l_oe_order_line_rec.oe_order_line_id, --OE_ORDER_LINE_ID,
l_oe_order_line_rec.organization_id, --TO_ORGANIZATION_ID
p_rma_receipt_rec.receiving_subinventory,--SUBINVENTORY
-- SALOGAN commeted old and added local variable instead of IN param for 9496606
--p_rma_receipt_rec.receiving_locator_id, --LOCATOR_ID
l_receiving_locator_id,
'AHL', --INTERFACE_SOURCE_CODE
p_rma_receipt_rec.receipt_uom_code --UOM_CODE
);
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
(
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
VALUES
(
l_mtl_transaction_id, --TRANSACTION_INTERFACE_ID,
SYSDATE, --LAST_UPDATE_DATE,
FND_GLOBAL.USER_ID, --LAST_UPDATED_BY,
SYSDATE, --CREATION_DATE,
FND_GLOBAL.USER_ID, --CREATED_BY,
FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN,
l_trans_lot_number, --LOT_NUMBER,
p_rma_receipt_rec.RECEIPT_QUANTITY, --TRANSACTION_QUANTITY
p_rma_receipt_rec.RECEIPT_QUANTITY, --PRIMARY_QUANTITY
'RCV', --PRODUCT_CODE,
l_intf_transaction_id --PRODUCT_TRANSACTION_ID
);
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
(
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
VALUES
(
l_mtl_transaction_id, --TRANSACTION_INTERFACE_ID,
SYSDATE, --LAST_UPDATE_DATE,
FND_GLOBAL.USER_ID, --LAST_UPDATED_BY,
SYSDATE, --CREATION_DATE,
FND_GLOBAL.USER_ID, --CREATED_BY,
FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN,
l_trans_serial_number, --FM_SERIAL_NUMBER,
l_trans_serial_number, --TO_SERIAL_NUMBER,
'RCV', --PRODUCT_CODE,
l_intf_transaction_id --PRODUCT_TRANSACTION_ID
);
SELECT 'X'
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id
AND line_type_id = FND_PROFILE.VALUE('AHL_OSP_OE_RETURN_ID');
SELECT OHA.header_id
FROM OE_ORDER_LINES_ALL OLA, OE_ORDER_HEADERS_ALL OHA
WHERE OLA.line_id = c_oe_line_id
AND OHA.header_id = OLA.header_id
AND OHA.booked_flag = 'Y';
SELECT oe_ship_line_id
FROM AHL_OSP_ORDER_LINES
WHERE oe_return_line_id = c_oe_return_line_id
AND ROWNUM = 1;
SELECT ordered_quantity, shipped_quantity
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id;
SELECT 'X'
FROM OE_ORDER_LINES_ALL
WHERE line_id = c_oe_line_id
AND NVL(ship_from_org_id, -1) = c_rcv_org_id;
PROCEDURE Update_OSP_Line_Exch_Instance(
p_osp_order_id IN NUMBER,
p_osp_line_id IN NUMBER,
p_exchange_instance_id IN NUMBER
)IS
-- Check if the instance is a valid IB instance
-- Also not part of relationship
CURSOR val_exg_instance_id_csr(p_instance_id IN NUMBER) IS
SELECT 'x' FROM csi_item_instances csi
WHERE instance_id = p_instance_id
AND nvl(csi.active_end_date, sysdate + 1) > sysdate
AND NOT EXISTS
(select subject_id
from csi_ii_relationships
where subject_id = p_instance_id
and relationship_type_code = 'COMPONENT-OF'
and NVL(ACTIVE_START_DATE, SYSDATE - 1) < SYSDATE
AND NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE) ;
SELECT a.OSP_ORDER_LINE_ID,
a.OBJECT_VERSION_NUMBER,
a.LAST_UPDATE_DATE,
a.LAST_UPDATED_BY,
a.LAST_UPDATE_LOGIN,
a.OSP_ORDER_ID,
a.OSP_LINE_NUMBER,
a.STATUS_CODE,
a.PO_LINE_TYPE_ID,
a.SERVICE_ITEM_ID,
a.SERVICE_ITEM_DESCRIPTION,
a.SERVICE_ITEM_UOM_CODE,
a.NEED_BY_DATE,
a.SHIP_BY_DATE,
a.PO_LINE_ID,
a.OE_SHIP_LINE_ID,
a.OE_RETURN_LINE_ID,
a.WORKORDER_ID,
a.OPERATION_ID,
a.EXCHANGE_INSTANCE_ID,
a.INVENTORY_ITEM_ID,
a.INVENTORY_ORG_ID,
a.SERIAL_NUMBER,
a.LOT_NUMBER,
a.INVENTORY_ITEM_UOM,
a.INVENTORY_ITEM_QUANTITY,
a.SUB_INVENTORY,
a.QUANTITY,
a.ATTRIBUTE_CATEGORY,
a.ATTRIBUTE1,
a.ATTRIBUTE2,
a.ATTRIBUTE3,
a.ATTRIBUTE4,
a.ATTRIBUTE5,
a.ATTRIBUTE6,
a.ATTRIBUTE7,
a.ATTRIBUTE8,
a.ATTRIBUTE9,
a.ATTRIBUTE10,
a.ATTRIBUTE11,
a.ATTRIBUTE12,
a.ATTRIBUTE13,
a.ATTRIBUTE14,
a.ATTRIBUTE15,
a.PO_REQ_LINE_ID
FROM AHL_OSP_ORDER_LINES a
WHERE a.osp_order_id = p_osp_id
AND a.osp_order_line_id = p_osp_line_id;
AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,
P_LAST_UPDATE_LOGIN => l_osp_line_rec.LAST_UPDATE_LOGIN,
P_OSP_ORDER_ID => l_osp_line_rec.OSP_ORDER_ID,
P_OSP_LINE_NUMBER => l_osp_line_rec.OSP_LINE_NUMBER,
P_STATUS_CODE => l_osp_line_rec.STATUS_CODE,
P_PO_LINE_TYPE_ID => l_osp_line_rec.PO_LINE_TYPE_ID,
P_SERVICE_ITEM_ID => l_osp_line_rec.SERVICE_ITEM_ID,
P_SERVICE_ITEM_DESCRIPTION => l_osp_line_rec.SERVICE_ITEM_DESCRIPTION,
P_SERVICE_ITEM_UOM_CODE => l_osp_line_rec.SERVICE_ITEM_UOM_CODE,
P_NEED_BY_DATE => l_osp_line_rec.NEED_BY_DATE,
P_SHIP_BY_DATE => l_osp_line_rec.SHIP_BY_DATE,
P_PO_LINE_ID => l_osp_line_rec.PO_LINE_ID,
P_OE_SHIP_LINE_ID => l_osp_line_rec.OE_SHIP_LINE_ID,
P_OE_RETURN_LINE_ID => l_osp_line_rec.OE_RETURN_LINE_ID,
P_WORKORDER_ID => l_osp_line_rec.WORKORDER_ID,
P_OPERATION_ID => l_osp_line_rec.OPERATION_ID,
P_QUANTITY => l_osp_line_rec.QUANTITY,
P_EXCHANGE_INSTANCE_ID => p_exchange_instance_id,
P_INVENTORY_ITEM_ID => l_osp_line_rec.INVENTORY_ITEM_ID,
P_INVENTORY_ORG_ID => l_osp_line_rec.INVENTORY_ORG_ID,
P_INVENTORY_ITEM_UOM => l_osp_line_rec.INVENTORY_ITEM_UOM,
P_INVENTORY_ITEM_QUANTITY => l_osp_line_rec.INVENTORY_ITEM_QUANTITY,
P_SUB_INVENTORY => l_osp_line_rec.SUB_INVENTORY,
P_LOT_NUMBER => l_osp_line_rec.LOT_NUMBER,
P_SERIAL_NUMBER => l_osp_line_rec.SERIAL_NUMBER,
P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
P_ATTRIBUTE_CATEGORY => l_osp_line_rec.ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => l_osp_line_rec.ATTRIBUTE1,
P_ATTRIBUTE2 => l_osp_line_rec.ATTRIBUTE2,
P_ATTRIBUTE3 => l_osp_line_rec.ATTRIBUTE3,
P_ATTRIBUTE4 => l_osp_line_rec.ATTRIBUTE4,
P_ATTRIBUTE5 => l_osp_line_rec.ATTRIBUTE5,
P_ATTRIBUTE6 => l_osp_line_rec.ATTRIBUTE6,
P_ATTRIBUTE7 => l_osp_line_rec.ATTRIBUTE7,
P_ATTRIBUTE8 => l_osp_line_rec.ATTRIBUTE8,
P_ATTRIBUTE9 => l_osp_line_rec.ATTRIBUTE9,
P_ATTRIBUTE10 => l_osp_line_rec.ATTRIBUTE10,
P_ATTRIBUTE11 => l_osp_line_rec.ATTRIBUTE11,
P_ATTRIBUTE12 => l_osp_line_rec.ATTRIBUTE12,
P_ATTRIBUTE13 => l_osp_line_rec.ATTRIBUTE13,
P_ATTRIBUTE14 => l_osp_line_rec.ATTRIBUTE14,
P_ATTRIBUTE15 => l_osp_line_rec.ATTRIBUTE15 );
END Update_OSP_Line_Exch_Instance;
PROCEDURE Update_OSP_Order_Lines(
p_osp_order_id IN NUMBER,
p_osp_line_id IN NUMBER,
p_oe_ship_line_id IN NUMBER,
p_oe_return_line_id IN NUMBER
) IS
--
CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_osp_line_id IN NUMBER) IS
SELECT a.OSP_ORDER_LINE_ID,
a.OBJECT_VERSION_NUMBER,
a.LAST_UPDATE_DATE,
a.LAST_UPDATED_BY,
a.LAST_UPDATE_LOGIN,
a.OSP_ORDER_ID,
a.OSP_LINE_NUMBER,
a.STATUS_CODE,
a.PO_LINE_TYPE_ID,
a.SERVICE_ITEM_ID,
a.SERVICE_ITEM_DESCRIPTION,
a.SERVICE_ITEM_UOM_CODE,
a.NEED_BY_DATE,
a.SHIP_BY_DATE,
a.PO_LINE_ID,
a.OE_SHIP_LINE_ID,
a.OE_RETURN_LINE_ID,
a.WORKORDER_ID,
a.OPERATION_ID,
a.EXCHANGE_INSTANCE_ID,
a.INVENTORY_ITEM_ID,
a.INVENTORY_ORG_ID,
a.SERIAL_NUMBER,
a.LOT_NUMBER,
a.INVENTORY_ITEM_UOM,
a.INVENTORY_ITEM_QUANTITY,
a.SUB_INVENTORY,
a.QUANTITY,
a.ATTRIBUTE_CATEGORY,
a.ATTRIBUTE1,
a.ATTRIBUTE2,
a.ATTRIBUTE3,
a.ATTRIBUTE4,
a.ATTRIBUTE5,
a.ATTRIBUTE6,
a.ATTRIBUTE7,
a.ATTRIBUTE8,
a.ATTRIBUTE9,
a.ATTRIBUTE10,
a.ATTRIBUTE11,
a.ATTRIBUTE12,
a.ATTRIBUTE13,
a.ATTRIBUTE14,
a.ATTRIBUTE15,
a.PO_REQ_LINE_ID
FROM AHL_OSP_ORDER_LINES a
WHERE a.osp_order_id = p_osp_id
AND a.osp_order_line_id = p_osp_line_id;
AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,
P_LAST_UPDATE_LOGIN => l_osp_line_rec.LAST_UPDATE_LOGIN,
P_OSP_ORDER_ID => l_osp_line_rec.OSP_ORDER_ID,
P_OSP_LINE_NUMBER => l_osp_line_rec.OSP_LINE_NUMBER,
P_STATUS_CODE => l_osp_line_rec.STATUS_CODE,
P_PO_LINE_TYPE_ID => l_osp_line_rec.PO_LINE_TYPE_ID,
P_SERVICE_ITEM_ID => l_osp_line_rec.SERVICE_ITEM_ID,
P_SERVICE_ITEM_DESCRIPTION => l_osp_line_rec.SERVICE_ITEM_DESCRIPTION,
P_SERVICE_ITEM_UOM_CODE => l_osp_line_rec.SERVICE_ITEM_UOM_CODE,
P_NEED_BY_DATE => l_osp_line_rec.NEED_BY_DATE,
P_SHIP_BY_DATE => l_osp_line_rec.SHIP_BY_DATE,
P_PO_LINE_ID => l_osp_line_rec.PO_LINE_ID,
P_OE_SHIP_LINE_ID => l_oe_ship_line_id,
P_OE_RETURN_LINE_ID => l_oe_return_line_id,
P_WORKORDER_ID => l_osp_line_rec.WORKORDER_ID,
P_OPERATION_ID => l_osp_line_rec.OPERATION_ID,
P_QUANTITY => l_osp_line_rec.QUANTITY,
P_EXCHANGE_INSTANCE_ID => l_osp_line_rec.EXCHANGE_INSTANCE_ID,
P_INVENTORY_ITEM_ID => l_osp_line_rec.INVENTORY_ITEM_ID,
P_INVENTORY_ORG_ID => l_osp_line_rec.INVENTORY_ORG_ID,
P_INVENTORY_ITEM_UOM => l_osp_line_rec.INVENTORY_ITEM_UOM,
P_INVENTORY_ITEM_QUANTITY => l_osp_line_rec.INVENTORY_ITEM_QUANTITY,
P_SUB_INVENTORY => l_osp_line_rec.SUB_INVENTORY,
P_LOT_NUMBER => l_osp_line_rec.LOT_NUMBER,
P_SERIAL_NUMBER => l_osp_line_rec.SERIAL_NUMBER,
P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
P_ATTRIBUTE_CATEGORY => l_osp_line_rec.ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => l_osp_line_rec.ATTRIBUTE1,
P_ATTRIBUTE2 => l_osp_line_rec.ATTRIBUTE2,
P_ATTRIBUTE3 => l_osp_line_rec.ATTRIBUTE3,
P_ATTRIBUTE4 => l_osp_line_rec.ATTRIBUTE4,
P_ATTRIBUTE5 => l_osp_line_rec.ATTRIBUTE5,
P_ATTRIBUTE6 => l_osp_line_rec.ATTRIBUTE6,
P_ATTRIBUTE7 => l_osp_line_rec.ATTRIBUTE7,
P_ATTRIBUTE8 => l_osp_line_rec.ATTRIBUTE8,
P_ATTRIBUTE9 => l_osp_line_rec.ATTRIBUTE9,
P_ATTRIBUTE10 => l_osp_line_rec.ATTRIBUTE10,
P_ATTRIBUTE11 => l_osp_line_rec.ATTRIBUTE11,
P_ATTRIBUTE12 => l_osp_line_rec.ATTRIBUTE12,
P_ATTRIBUTE13 => l_osp_line_rec.ATTRIBUTE13,
P_ATTRIBUTE14 => l_osp_line_rec.ATTRIBUTE14,
P_ATTRIBUTE15 => l_osp_line_rec.ATTRIBUTE15 );
END Update_OSP_Order_Lines;
SELECT MIL.INVENTORY_LOCATION_ID
,MIL_kfv.CONCATENATED_SEGMENTS
,MIL.SUBINVENTORY_CODE
FROM MTL_ITEM_LOCATIONS_KFV MIL_kfv
,MTL_ITEM_LOCATIONS MIL
WHERE MIL_kfv.INVENTORY_LOCATION_ID = MIL.INVENTORY_LOCATION_ID AND
MIL_kfv.ORGANIZATION_ID = MIL.ORGANIZATION_ID AND
MIL_kfv.ORGANIZATION_ID = c_org_id
AND UPPER(DECODE(MIL.segment19, NULL, MIL_kfv.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MIL_kfv.concatenated_segments)
|| fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_project_number(MIL.segment19)
|| FND_FLEX_EXT.GET_DELIMITER('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_task_number(MIL.segment20))) = UPPER(c_locator_name)
AND ((MIL.START_DATE_ACTIVE IS NULL) OR (MIL.START_DATE_ACTIVE <= SYSDATE))
AND ((MIL.END_DATE_ACTIVE IS NULL) OR (MIL.END_DATE_ACTIVE > SYSDATE));
SELECT 'X' FROM MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = c_org_id
AND SECONDARY_INVENTORY_NAME like c_sub_inv
AND LOCATOR_TYPE = 3;