[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_OSP_Order(
p_osp_order_id IN NUMBER,
p_oe_header_id IN NUMBER
);
PROCEDURE Delete_OSP_Order(
p_oe_header_id IN NUMBER
);
PROCEDURE Update_OSP_Order_Lines(
p_osp_order_id IN NUMBER,
p_item_instance_id IN NUMBER,
p_oe_ship_line_id IN NUMBER,
p_oe_return_line_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
);
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 Delete_OE_Lines(p_oe_line_id IN NUMBER);
PROCEDURE Delete_IB_Transaction(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_oe_line_id IN NUMBER);
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND meaning = p_lookup_meaning
AND TRUNC(SYSDATE) >= TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(end_date_active, SYSDATE+1));
SELECT 1
FROM mtl_serial_numbers
WHERE inventory_item_id = c_Inventory_id
AND Serial_Number = c_Serial_Number;
Select osp_order_line_id,
OSP_LINE_NUMBER,
inventory_item_id,
inventory_org_id,
serial_number
from AHL_OSP_ORDER_LINES a
where OSP_ORDER_ID = p_osp_order_id
/* Modified by jaramana on January 11, 2008 to fix the Bug 5688387/5842229
changed the "and" operation in the line below to "Or" */
and (oe_ship_line_id is null or oe_return_line_id is null)
and serial_number is not null
and exists (select 1
from ahl_osp_order_lines
where osp_order_id = p_osp_order_id
and inventory_item_id = a.inventory_item_id
and inventory_org_id = a.inventory_org_id
and serial_number = a.serial_number
and (oe_ship_line_id is not null
or oe_return_line_id is not null)
/* Modified by jaramana on January 11, 2008 to fix the Bug 5688387/5842229. Apart from a.osp_order_line_id we need
another line that exists with the same item and serial */
and osp_order_line_id <> a.osp_order_line_id);
Select distinct nvl(oe_ship_line_id,-1) oe_ship_line_id, nvl(oe_return_line_id,-1) oe_return_line_id
from AHL_OSP_ORDER_LINES a
where OSP_ORDER_ID = p_osp_order_id
and (oe_ship_line_id is not null
or oe_return_line_id is not null)
and inventory_item_id = p_inventory_item_id
and inventory_org_id = p_inventory_org_id
and serial_number = p_serial_number
order by 1, 2 desc;
SELECT 1
FROM mtl_system_items_b mtl,
oe_order_lines_all oel
WHERE oel.line_id = c_oe_line_id
AND mtl.inventory_item_id = oel.inventory_item_id
AND mtl.organization_id = c_oe_org_id
AND nvl(mtl.comms_nl_trackable_flag,'N') = 'Y' ;
SELECT 1
FROM ahl_osp_order_lines
WHERE osp_order_line_id = p_osp_line_id
AND osp_order_id = p_osp_order_id;
x_del_oe_lines_tbl => l_del_oe_lines_tbl -- Additional Parameter to get the lines to be deleted
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Update_OSP_Order');
Update_OSP_Order(p_osp_order_id => p_x_header_rec.osp_order_id,
p_oe_header_id => p_x_header_rec.header_id );
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Delete_OSP_Order');
Delete_OSP_Order(p_oe_header_id => p_x_header_rec.header_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Delete_Cancel_Order');
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 => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from Delete_Cancel_Order, x_return_status = ' || x_return_status);
END IF; -- Delete Line Count > 0
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Update_OSP_Order_Lines');
Update_OSP_Order_Lines(
p_osp_order_id => p_x_lines_tbl(i).osp_order_id,
p_item_instance_id => p_x_lines_tbl(i).csi_item_instance_id,
p_oe_ship_line_id => p_x_lines_tbl(i).line_id,
p_oe_return_line_id => FND_API.G_MISS_NUM);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Calling Update_OSP_Order_Lines with p_oe_return_line_id = ' || p_x_lines_tbl(i).line_id );
Update_OSP_Order_Lines(
p_osp_order_id => p_x_lines_tbl(i).osp_order_id,
p_item_instance_id => p_x_lines_tbl(i).csi_item_instance_id,
p_oe_ship_line_id => FND_API.G_MISS_NUM ,
p_oe_return_line_id => p_x_lines_tbl(i).line_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Update_OSP_Order_Lines');
Update_OSP_Order_Lines(
p_osp_order_id => p_x_lines_tbl(i).osp_order_id,
p_osp_line_id => p_x_lines_tbl(i).osp_line_id,
p_oe_ship_line_id => p_x_lines_tbl(i).line_id,
p_oe_return_line_id => FND_API.G_MISS_NUM);
the instance is not updated as part of the order line's exchange instance. Otherwise OM creates it with
the default sub-transction and not use the AHL IB profile.
We check whether the OE Line item is IB tracked on the receiving org and, if so create the
Installation details with the instance as null.
*/
OPEN is_oe_item_IB_tracked(p_x_lines_tbl(i).line_id, p_x_lines_tbl(i).ship_from_org_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Calling Update_OSP_Order_Lines with p_oe_return_line_id = ' || p_x_lines_tbl(i).line_id );
Update_OSP_Order_Lines(
p_osp_order_id => p_x_lines_tbl(i).osp_order_id,
p_osp_line_id => p_x_lines_tbl(i).osp_line_id,
p_oe_ship_line_id => FND_API.G_MISS_NUM ,
p_oe_return_line_id => p_x_lines_tbl(i).line_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Delete_IB_Transaction');
Delete_IB_Transaction(
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
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_x_lines_tbl(i).line_id );
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Return status from Delete_IB_Transaction: ' || x_return_status);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Delete_OE_Lines');
Delete_OE_Lines(p_oe_line_id => p_x_lines_tbl(i).line_id);
END LOOP; -- For all Create and Update Lines lines
END IF; -- If Create/Update Line Count > 0
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Calling Update_OSP_Order_Lines with p_oe_ship_line_id = ' || l_osp_line_ship_id_type.oe_ship_line_id );
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Calling Update_OSP_Order_Lines with p_oe_return_line_id = ' || l_osp_line_ship_id_type.oe_return_line_id );
Update_OSP_Order_Lines(
p_osp_order_id => l_osp_order_id,
p_osp_line_id => l_osp_line_no_ship_type.osp_order_line_id,
p_oe_ship_line_id => l_osp_line_ship_id_type.oe_ship_line_id,
p_oe_return_line_id => l_osp_line_ship_id_type.oe_return_line_id);
PROCEDURE Delete_Cancel_Order (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_oe_header_id IN NUMBER,
p_oe_lines_tbl IN SHIP_ID_TBL_TYPE,
p_cancel_flag IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
--
CURSOR ahl_is_header_deleteable_csr(p_header_id IN NUMBER) IS
SELECT order_type_id
FROM oe_order_headers_all
WHERE header_id = p_header_id
AND booked_flag = 'N';
CURSOR ahl_is_line_deleteable_csr(p_line_id IN NUMBER) IS
SELECT 1
FROM oe_order_lines_all
WHERE line_id = p_line_id
AND shipped_quantity IS NULL
AND booked_flag = 'N';
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Delete_Cancel_Order';
SAVEPOINT Delete_Cancel_Order_Pub;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Delete_OSP_Order with p_oe_header_id = ' || p_oe_header_id);
Delete_OSP_Order(p_oe_header_id => p_oe_header_id );
OPEN ahl_is_header_deleteable_csr(p_oe_header_id);
FETCH ahl_is_header_deleteable_csr INTO l_order_type_id;
IF (ahl_is_header_deleteable_csr%FOUND
AND l_type <> 'G') THEN
l_header_rec := OE_HEADER_UTIL.QUERY_ROW(p_header_id => p_oe_header_id);
l_header_rec.operation := OE_GLOBALS.G_OPR_DELETE;
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
CLOSE ahl_is_header_deleteable_csr;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Delete_OE_Lines with p_oe_line_id = ' || p_oe_lines_tbl(i));
Delete_OE_Lines(p_oe_line_id => p_oe_lines_tbl(i));
OPEN ahl_is_line_deleteable_csr(p_oe_lines_tbl(i));
FETCH ahl_is_line_deleteable_csr INTO l_dummy;
IF (ahl_is_line_deleteable_csr%FOUND) THEN
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Line is deletable: Deleting line');
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
CLOSE ahl_is_line_deleteable_csr;
p_procedure_name => 'deleteCancelOrder',
p_error_text => substr(l_msg_data,1,240));
Delete_IB_Transaction(
-- Changed by jaramana on January 11, 2008 for the Requisition ER 6034236.
p_init_msg_list => FND_API.G_FALSE, --p_init_msg_list,
p_commit => p_commit,
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 => l_line_tbl(i).line_id );
Rollback to Delete_Cancel_Order_Pub;
Rollback to Delete_Cancel_Order_Pub;
Rollback to Delete_Cancel_Order_Pub;
p_procedure_name => 'Delete_Cancel_Order',
p_error_text => SQLERRM);
END Delete_Cancel_Order;
SELECT 1
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT open_flag, nvl(flow_status_code,'XXX') flow_status_code, nvl(cancelled_flag,'N') cancelled_flag
FROM oe_order_headers_all
WHERE header_id = p_header_id;
ELSE --This may mean that the Sales Order has been deleted from the OM Forms and Synch has not been done
CLOSE ahl_osp_oe_closed_csr;
SELECT USER_PROFILE_OPTION_NAME
FROM fnd_profile_options_vl
WHERE profile_option_name = c_prf_opt_name;
SELECT salesrep_id
FROM ra_salesreps
WHERE commissionable_flag = 'N';
SELECT GSB.currency_code
FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
GL_SETS_OF_BOOKS GSB
WHERE FSP.set_of_books_id = GSB.set_of_books_id;
SELECT org.organization_id
FROM OE_SHIP_FROM_ORGS_V org, inv_organization_info_v def
WHERE org.organization_id = def.organization_id
-- Changed by jaramana on Sep 9, 2005 for MOAC Uptake
-- AND def.operating_unit = FND_PROFILE.VALUE('DEFAULT_ORG_ID')
AND def.operating_unit = MO_GLOBAL.get_current_org_id()
AND org.name = p_name;
SELECT organization_id
FROM OE_SOLD_TO_ORGS_V
WHERE customer_number = p_cust_number;
SELECT organization_id
FROM OE_SHIP_TO_ORGS_V
WHERE customer_id = p_sold_to_org_id
AND name = p_name;
SELECT COUNT(CONTACT_ID)
FROM OE_CONTACTS_V
WHERE NAME = p_sold_to_contact
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT osp.vendor_id,
osp.vendor_site_id,
osp.vendor_contact_id,
osp.osp_order_number,
cust.customer_site_id,
cust.customer_id
FROM ahl_osp_orders_b osp,
ahl_vendor_customer_rels_v cust
WHERE osp.osp_order_id = p_osp_order_id
AND osp.vendor_site_id = cust.vendor_site_id;
SELECT cust_account_id
FROM hz_cust_acct_sites_all acc,
HZ_CUST_SITE_USES_ALL site
WHERE site.cust_acct_site_id = acc.cust_acct_site_id
and SITE_USE_ID = p_customer_site_id;*/
select customer_id from OE_SHIP_TO_ORGS_V
where organization_id = p_customer_site_id;
SELECT inventory_org_id
FROM ahl_osp_order_lines
WHERE osp_order_id = p_osp_order_id
ORDER BY osp_order_line_id;
x_header_rec.operation := OE_GLOBALS.G_OPR_DELETE;
x_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT wo.inventory_item_id,
wo.serial_number,
wo.item_instance_uom,
wo.quantity,
wo.item_instance_id,
wo.project_id,
wo.project_task_id,
wo.ORGANIZATION_ID,
ospl.osp_order_id,
ospl.osp_order_line_id,
ospl.osp_line_number,
ospl.exchange_instance_id
FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl
WHERE ospl.workorder_id = wo.workorder_id
AND wo.job_number = p_wo_name
AND ospl.osp_order_id = p_osp_id
AND ospl.status_code IS NULL;
select max(service_duration) from ahl_item_vendor_rels
where vendor_certification_id = (select vendor_certification_id from ahl_vendor_certifications_v
where vendor_id =p_vendor_id and vendor_site_id =p_vendor_site_id)
and active_start_date <= sysdate
and (active_end_date is null or active_end_date > sysdate)
and inv_service_item_rel_id in (select inv_service_item_rel_id
from ahl_inv_service_item_rels
where service_item_id in (select service_item_id
from ahl_osp_order_lines
where osp_order_id = p_osp_order_id
and inventory_item_id = p_inventory_item_id
and inventory_org_id = p_inventory_org_id
and serial_number = p_serial_number
and status_code is null)
AND inv_item_id = p_inventory_item_id
AND inv_org_id = p_inventory_org_id);
SELECT wo.inventory_item_id,
wo.serial_number,
wo.item_instance_uom,
wo.quantity,
wo.item_instance_id,
wo.project_id,
wo.project_task_id,
ospl.osp_order_id,
ospl.osp_order_line_id,
ospl.osp_line_number,
ospl.exchange_instance_id
FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl
WHERE ospl.workorder_id = wo.workorder_id
AND wo.item_instance_id = p_instance_id
AND ospl.osp_order_id = p_osp_id
AND ospl.status_code IS NULL;
SELECT inventory_item_id,
serial_number,
quantity,
unit_of_measure,
lot_number --jeli 12/01/05
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT 1
FROM AHL_OSP_ORDER_LINES
WHERE osp_order_id = p_osp_id
AND osp_order_line_id = p_osp_line_id
AND oe_ship_line_id IS NOT NULL;
SELECT 1
FROM AHL_OSP_ORDER_LINES
WHERE osp_order_id = p_osp_id
AND osp_order_line_id = p_osp_line_id
AND oe_return_line_id IS NOT NULL;
SELECT 1
FROM AHL_OSP_ORDER_LINES ospl,
AHL_WORKORDERS wo,
ahl_visit_tasks_b vts
WHERE ospl.workorder_id = wo.workorder_id
AND ospl.osp_order_id = p_osp_id
AND wo.visit_task_id = vts.visit_task_id
AND vts.instance_id = p_csi_ii_id
AND ospl.oe_ship_line_id IS NOT NULL;
SELECT 1
FROM AHL_OSP_ORDER_LINES a
WHERE a.osp_order_id = p_osp_id
AND a.inventory_item_id = p_inv_item_id
AND a.inventory_org_id= p_inv_org_id
AND NVL(a.sub_inventory, 'X') = NVL(p_sub_inventory, 'X')
AND NVL(a.serial_number, 'X') = NVL(p_serial_number, 'X')
AND NVL(a.lot_number, 'X') = NVL(p_lot_number, 'X')
AND a.oe_ship_line_id IS NOT NULL;
SELECT 1
FROM AHL_OSP_ORDER_LINES ospl,
AHL_WORKORDERS wo,
ahl_visit_tasks_b vts
WHERE ospl.workorder_id = wo.workorder_id
AND ospl.osp_order_id = p_osp_id
AND wo.visit_task_id = vts.visit_task_id
AND vts.instance_id = p_csi_ii_id
AND ospl.oe_return_line_id IS NOT NULL;
SELECT 1
FROM AHL_OSP_ORDER_LINES a
WHERE a.osp_order_id = p_osp_id
AND a.inventory_item_id = p_inv_item_id
AND a.inventory_org_id= p_inv_org_id
AND NVL(a.sub_inventory, 'X') = NVL(p_sub_inventory, 'X')
AND NVL(a.serial_number, 'X') = NVL(p_serial_number, 'X')
AND NVL(a.lot_number, 'X') = NVL(p_lot_number, 'X')
AND a.oe_return_line_id IS NOT NULL;
SELECT 1
FROM CSI_ITEM_INSTANCES CII
WHERE CII.INSTANCE_ID = p_csi_ii_id AND
NOT EXISTS
(SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID AND
CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF' AND
NVL(CIR.ACTIVE_START_DATE, SYSDATE - 1) < SYSDATE AND
NVL(CIR.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE);
SELECT distinct oe1.line_id, oe1.schedule_ship_date,
oe2.line_id, oe2.schedule_ship_date
FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,
OE_ORDER_LINES_ALL oe2, AHL_WORKORDERS_OSP_V b
WHERE a.oe_ship_line_id = oe1.line_id (+)
AND a.oe_return_line_id = oe2.line_id (+)
AND a.workorder_id = b.workorder_id
AND a.osp_order_id = p_osp_id
AND b.item_instance_id = p_csi_ii_id;
SELECT distinct oe1.line_id, oe1.schedule_ship_date,
oe2.line_id, oe2.schedule_ship_date
FROM AHL_OSP_ORDER_LINES ospl,
OE_ORDER_LINES_ALL oe1,
OE_ORDER_LINES_ALL oe2,
AHL_WORKORDERS wo,
ahl_visit_tasks_b vts
WHERE ospl.oe_ship_line_id = oe1.line_id (+)
AND ospl.oe_return_line_id = oe2.line_id (+)
AND ospl.workorder_id = wo.workorder_id
AND ospl.osp_order_id = p_osp_id
AND wo.visit_task_id = vts.visit_task_id
AND vts.instance_id = p_csi_ii_id;
SELECT oe1.line_id, oe1.schedule_ship_date,
oe2.line_id, oe2.schedule_ship_date
FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,
OE_ORDER_LINES_ALL oe2
WHERE a.oe_ship_line_id = oe1.line_id (+)
AND a.oe_return_line_id = oe2.line_id (+)
AND a.osp_order_id = p_osp_id
AND a.inventory_item_id = p_inv_item_id
AND a.inventory_org_id = p_inv_org_id
AND NVL(a.sub_inventory, 'X') = NVL(p_sub_inventory, 'X')
AND NVL(a.serial_number, 'X') = NVL(p_serial_number, 'X')
AND NVL(a.lot_number, 'X') = NVL(p_lot_number, 'X');
SELECT order_type_code from ahl_osp_orders_b
where osp_order_id = c_osp_id;
SELECT *
from ahl_osp_orders_b
where osp_order_id = p_osp_order_id;
select osp_order_line_id,
object_version_number,
osp_order_id,
osp_line_number,
workorder_id,
status_code,
service_item_id,
service_item_description,
service_item_uom_code,
need_by_date,
ship_by_date,
po_line_id,
po_line_type_id,
oe_ship_line_id,
oe_return_line_id,
operation_id,
quantity,
exchange_instance_id,
inventory_item_id,
inventory_org_id,
sub_inventory,
serial_number,
lot_number,
inventory_item_uom,
inventory_item_quantity
from ahl_osp_order_lines
where osp_order_id = p_osp_order_id
and osp_order_line_id = p_osp_line_id;
select osp_order_line_id ,
object_version_number ,
osp_order_id ,
osp_line_number ,
workorder_id ,
status_code ,
service_item_id ,
service_item_description,
service_item_uom_code ,
need_by_date ,
ship_by_date ,
po_line_id ,
po_line_type_id ,
oe_ship_line_id ,
oe_return_line_id ,
operation_id ,
quantity ,
exchange_instance_id ,
inventory_item_id ,
inventory_org_id ,
sub_inventory ,
serial_number ,
lot_number ,
inventory_item_uom ,
inventory_item_quantity
from ahl_osp_order_lines a
where osp_order_id = p_osp_order_id
AND ((osp_line_number = (select min(osp_line_number)
from ahl_osp_order_lines
where osp_order_id = p_osp_order_id
and inventory_item_id = a.inventory_item_id
and inventory_org_id = a.inventory_org_id
and serial_number = a.serial_number))
-- Added by jaramana on January 11, 2008 as without this the non-serialized items are not getting picked for shipments
--for the Bug 5688387/5842229
OR serial_number is null)
AND oe_ship_line_id IS NULL
AND oe_return_line_id IS NULL;
select osp_order_line_id ,
object_version_number ,
osp_order_id ,
osp_line_number ,
workorder_id ,
status_code ,
service_item_id ,
service_item_description,
service_item_uom_code ,
need_by_date ,
ship_by_date ,
po_line_id ,
po_line_type_id ,
oe_ship_line_id ,
oe_return_line_id ,
operation_id ,
quantity ,
exchange_instance_id ,
inventory_item_id ,
inventory_org_id ,
sub_inventory ,
serial_number ,
lot_number ,
inventory_item_uom ,
inventory_item_quantity
from ahl_osp_order_lines
where osp_order_id = p_osp_order_id
AND (NVL(oe_ship_line_id, -9) = NVL(p_line_id, -8)
OR NVL(oe_return_line_id, -9) = NVL(p_line_id, -8));
line itself. Please note that because of this, if someone chages the part from the OM UIs and then try to update
other details from Shipment Line Details UI, the information on the osp order lines will over-write this.
But this seem to be the existing behavior.
*/
CURSOR osp_line_details_csr3(p_osp_order_id IN NUMBER,
p_line_id IN NUMBER) IS
select ospl.osp_order_line_id ,
ospl.object_version_number ,
ospl.osp_order_id ,
ospl.osp_line_number ,
ospl.workorder_id ,
ospl.status_code ,
ospl.service_item_id ,
ospl.service_item_description,
ospl.service_item_uom_code ,
ospl.need_by_date ,
ospl.ship_by_date ,
ospl.po_line_id ,
ospl.po_line_type_id ,
ospl.oe_ship_line_id ,
ospl.oe_return_line_id ,
ospl.operation_id ,
ospl.quantity ,
ospl.exchange_instance_id ,
decode(csi.instance_id, null, ospl.inventory_item_id, csi.inventory_item_id) inventory_item_id,
ospl.inventory_org_id ,
ospl.sub_inventory ,
decode(csi.instance_id, null, ospl.serial_number, csi.serial_number) serial_number,
decode(csi.instance_id, null, ospl.lot_number, csi.lot_number) lot_number,
ospl.inventory_item_uom ,
ospl.inventory_item_quantity
from ahl_osp_order_lines ospl,
oe_order_lines_all oel,
csi_t_transaction_lines tl,
csi_t_txn_line_details tld,
csi_item_instances csi
where ospl.osp_order_id = p_osp_order_id
AND (NVL(oe_ship_line_id, -9) = NVL(p_line_id, -8)
OR NVL(oe_return_line_id, -9) = NVL(p_line_id, -8))
AND oel.line_id = p_line_id
AND oel.source_document_line_id = ospl.osp_order_line_id
AND tl.source_transaction_id (+)= oel.line_id
AND tl.source_transaction_table (+) = G_TRANSACTION_TABLE
AND tl.transaction_line_id = tld.transaction_line_id(+)
AND tld.instance_id = csi.instance_id(+);
SELECT wo.inventory_item_id,
wo.serial_number,
wo.item_instance_uom,
wo.quantity,
wo.item_instance_id,
wo.project_id,
wo.project_task_id,
wo.ORGANIZATION_ID,
wo.lot_number --jeli 12/01/05
FROM AHL_WORKORDERS_OSP_V wo
WHERE wo.workorder_id = p_workorder_id ;
SELECT vts.inventory_item_id,
csii.serial_number,
csii.unit_of_measure item_instance_uom,
csii.quantity,
vts.instance_id item_instance_id,
vst.project_id,
vts.project_task_id,
vst.ORGANIZATION_ID,
csii.lot_number
FROM AHL_WORKORDERS wo,
ahl_visits_b vst,
ahl_visit_tasks_b vts,
csi_item_instances csii
WHERE wo.workorder_id = p_workorder_id
AND wo.visit_task_id = vts.visit_task_id(+)
AND vts.visit_id = vst.visit_id(+)
AND vts.instance_id = csii.instance_id(+)
AND wo.master_workorder_flag = 'N';
SELECT INSTANCE_ID
FROM csi_item_instances csi
WHERE p_inv_item_id = csi.inventory_item_id
and p_inv_org_id = csi.last_vld_organization_id
and p_serial_number = csi.serial_number;
SELECT INSTANCE_ID
FROM csi_item_instances csi
WHERE p_inv_item_id = csi.inventory_item_id
and p_serial_number = csi.serial_number;
SELECT INSTANCE_ID
FROM csi_item_instances csi
WHERE csi.inventory_item_id = p_inv_item_id
and csi.lot_number = p_lot_number;
SELECT 1
FROM AHL_OSP_INV_ITEMS_V
WHERE ORGANIZATION_ID = p_inventory_org_id
AND INV_ITEM_ID = p_inventory_item_id
AND NVL(SERIAL_NUMBER, 'X') = NVL(p_serial_number, 'X')
AND NVL(LOT_NUMBER, 'X') = NVL(p_lot_number, 'X');
SELECT 1
FROM MTL_SYSTEM_ITEMS_B
WHERE ORGANIZATION_ID = p_inventory_org_id
AND INVENTORY_ITEM_ID = p_inventory_item_id;
select ospl.exchange_instance_id,
csi.inventory_item_id,
csi.serial_number,
csi.lot_number,
csi.quantity,
csi.unit_of_measure,
csi.last_vld_organization_id
FROM ahl_osp_order_lines ospl, csi_item_instances csi
WHERE ospl.osp_order_line_id = p_osp_order_line_id and
csi.instance_id = ospl.exchange_instance_id;
SELECT open_flag,
flow_status_code,
ordered_quantity,
cancelled_quantity
FROM oe_order_lines_all
WHERE line_id = c_line_id;
mpothuku updated on 06-Mar-06 to remove the ref to l_wo_rec as its not being used
l_wo_rec ahl_osp_wo_csr%ROWTYPE;
One an SO is booked, it will not be possible to delete any of its lines. But the quantity can be set to 0.
Once this is done, the status of the SO line becomes cancelled. We still show such lines on the
Shipment Lines UI (The VO uses the source_document_id on the cancelled lines to get the osp_line_id.
This association we do not delete when cancelling a booked line) and when user tries to update/delete
such lines, the error reported in the Bug 6185894 is being ensued. It is because, the following cursor
does not retrieve any records for such lines.
OM does not happen to anyway allow deletes on booked lines and updates on cancelled lines.
So we can check the status and throw an error up-front instead of changing the logic to make such records
hit OM.
*/
--Modified on 16-Jul-2007
--Get the Shipment Line status.
OPEN get_so_line_details(p_x_line_tbl(i).line_id);
-- update the Osp Order Line's Exchange Instance column
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Update_OSP_Line_Exch_Instance');
Update_OSP_Line_Exch_Instance(
p_osp_order_id => p_x_line_tbl(i).osp_order_id,
p_osp_line_id => p_x_line_tbl(i).osp_line_id,
p_exchange_instance_id => p_x_line_tbl(i).csi_item_instance_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Not a Shipment Delete Operation');
-- update the Osp Order Line's Exchange Instance column
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Update_OSP_Line_Exch_Instance');
Update_OSP_Line_Exch_Instance(
p_osp_order_id => p_x_line_tbl(i).osp_order_id,
p_osp_line_id => p_x_line_tbl(i).osp_line_id,
p_exchange_instance_id => p_x_line_tbl(i).csi_item_instance_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Not a Shipment Delete Operation');
SELECT ship_to_org_id,
sold_to_org_id,
sold_from_org_id,
price_list_id,
payment_term_id
FROM oe_order_headers_all
WHERE header_id = p_oe_header_id;
SELECT org.organization_id
FROM OE_SHIP_FROM_ORGS_V org, inv_organization_info_v def
WHERE org.organization_id = def.organization_id
-- Changed by jaramana on Sep 9, 2005 for MOAC Uptake
-- AND def.operating_unit = FND_PROFILE.VALUE('DEFAULT_ORG_ID')
AND def.operating_unit = MO_GLOBAL.get_current_org_id()
AND org.name = p_name;
SELECT lot_serial_id
FROM oe_lot_serial_numbers
WHERE line_id = p_oe_line_id;
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_lot_serial_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
PROCEDURE Delete_OSP_Order(
p_oe_header_id IN NUMBER
) IS
--
CURSOR ahl_osp_order_id_csr(p_header_id IN NUMBER) IS
SELECT osp_order_id
FROM ahl_osp_orders_b
WHERE oe_header_id = p_header_id;
SELECT *
FROM AHL_OSP_ORDER_LINES
WHERE osp_order_id = p_order_id;
Update_OSP_Order(p_osp_order_id => l_osp_order_id,
p_oe_header_id => NULL );
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 => NULL,
P_OE_RETURN_LINE_ID => NULL,
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_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,
-- Begin Changes by jaramana on January 11, 2008 for the Requisition ER 6034236
P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
-- End Changes by jaramana on January 11, 2008 for the Requisition ER 6034236
P_EXCHANGE_INSTANCE_ID => l_osp_line_rec.EXCHANGE_INSTANCE_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 Delete_OSP_Order;
PROCEDURE Update_OSP_Order(
p_osp_order_id IN NUMBER,
p_oe_header_id IN NUMBER
) IS
--
CURSOR ahl_osp_order_csr(p_osp_id IN NUMBER) IS
SELECT *
FROM AHL_OSP_ORDERS_VL
WHERE osp_order_id = p_osp_id;
AHL_OSP_ORDERS_PKG.UPDATE_ROW(
X_OSP_ORDER_ID => l_osp_order.OSP_ORDER_ID,
X_OBJECT_VERSION_NUMBER => l_osp_order.OBJECT_VERSION_NUMBER+1,
X_OSP_ORDER_NUMBER => l_osp_order.OSP_ORDER_NUMBER,
X_ORDER_TYPE_CODE => l_osp_order.ORDER_TYPE_CODE,
X_SINGLE_INSTANCE_FLAG => l_osp_order.SINGLE_INSTANCE_FLAG,
X_PO_HEADER_ID => l_osp_order.PO_HEADER_ID,
X_OE_HEADER_ID => p_oe_header_id,
X_VENDOR_ID => l_osp_order.VENDOR_ID,
X_VENDOR_SITE_ID => l_osp_order.VENDOR_SITE_ID,
X_VENDOR_CONTACT_ID => l_osp_order.VENDOR_CONTACT_ID,
X_CUSTOMER_ID => l_osp_order.CUSTOMER_ID,
X_ORDER_DATE => l_osp_order.ORDER_DATE,
X_CONTRACT_ID => l_osp_order.CONTRACT_ID,
X_CONTRACT_TERMS => l_osp_order.CONTRACT_TERMS,
X_OPERATING_UNIT_ID => l_osp_order.OPERATING_UNIT_ID,
X_PO_SYNCH_FLAG => l_osp_order.PO_SYNCH_FLAG,
X_STATUS_CODE => l_osp_order.STATUS_CODE,
X_PO_BATCH_ID => l_osp_order.PO_BATCH_ID,
X_PO_REQUEST_ID => l_osp_order.PO_REQUEST_ID,
X_PO_AGENT_ID => l_osp_order.PO_AGENT_ID,
X_PO_INTERFACE_HEADER_ID => l_osp_order.PO_INTERFACE_HEADER_ID,
X_ATTRIBUTE_CATEGORY => l_osp_order.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_osp_order.ATTRIBUTE1,
X_ATTRIBUTE2 => l_osp_order.ATTRIBUTE2,
X_ATTRIBUTE3 => l_osp_order.ATTRIBUTE3,
X_ATTRIBUTE4 => l_osp_order.ATTRIBUTE4,
X_ATTRIBUTE5 => l_osp_order.ATTRIBUTE5,
X_ATTRIBUTE6 => l_osp_order.ATTRIBUTE6,
X_ATTRIBUTE7 => l_osp_order.ATTRIBUTE7,
X_ATTRIBUTE8 => l_osp_order.ATTRIBUTE8,
X_ATTRIBUTE9 => l_osp_order.ATTRIBUTE9,
X_ATTRIBUTE10 => l_osp_order.ATTRIBUTE10,
X_ATTRIBUTE11 => l_osp_order.ATTRIBUTE11,
X_ATTRIBUTE12 => l_osp_order.ATTRIBUTE12,
X_ATTRIBUTE13 => l_osp_order.ATTRIBUTE13,
X_ATTRIBUTE14 => l_osp_order.ATTRIBUTE14,
X_ATTRIBUTE15 => l_osp_order.ATTRIBUTE15,
X_DESCRIPTION => l_osp_order.DESCRIPTION,
X_PO_REQ_HEADER_ID => l_osp_order.PO_REQ_HEADER_ID, -- Added by jaramana on January 11, 2008 for the Requisition ER 6034236
X_LAST_UPDATE_DATE => l_osp_order.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_osp_order.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => l_osp_order.LAST_UPDATE_LOGIN
);
END Update_OSP_Order;
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
--
--Since one item instance can not be in multiple ship lines for given
--osp order, fetch all the ship/return lines for item instance
--
CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_osp_line_id IN NUMBER) IS
--mpothuku removed the usage of AHL_OSP_ORDER_LINES_V usage for fixing the perf Bug# 4919255 on 21-Feb-06
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.ITEM_NUMBER,
--a.ITEM_DESCRIPTION,
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,
-- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
a.PO_REQ_LINE_ID
-- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
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,
-- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
-- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
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;
PROCEDURE Update_OSP_Order_Lines(
p_osp_order_id IN NUMBER,
p_item_instance_id IN NUMBER,
p_oe_ship_line_id IN NUMBER,
p_oe_return_line_id IN NUMBER
) IS
--
--Since one item instance can not be in multiple ship lines for given
--osp order, fetch all the ship/return lines for item instance
--
CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_csi_ii_id IN NUMBER) IS
--mpothuku removed the usage of AHL_OSP_ORDER_LINES_V usage for fixing the perf Bug# 4919255 on 21-Feb-06
/*
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.ITEM_NUMBER,
a.ITEM_DESCRIPTION,
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
-- FROM AHL_OSP_ORDER_LINES a, AHL_WORKORDERS_V b
-- WHERE a.workorder_id = b.workorder_id
-- AND a.osp_order_id = p_osp_id
-- AND b.item_instance_id = p_csi_ii_id;
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,
-- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
a.PO_REQ_LINE_ID
-- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
FROM AHL_OSP_ORDER_LINES a,
ahl_workorders wo,
csi_item_instances csii,
ahl_visit_tasks_b vts
WHERE a.osp_order_id = p_osp_id
AND wo.workorder_id(+) = a.workorder_id
AND wo.visit_task_id = vts.visit_task_id(+)
AND csii.last_vld_organization_id(+) = a.inventory_org_id
AND csii.inventory_item_id(+) = a.inventory_item_id
AND csii.serial_number(+) = a.serial_number
AND (decode(a.workorder_id, NULL, csii.instance_id, vts.instance_id) = p_csi_ii_id OR a.exchange_instance_id = p_csi_ii_id)
--BEGIN changes by jrotich for bug # 14223880
UNION
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,
-- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
a.PO_REQ_LINE_ID
-- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
FROM AHL_OSP_ORDER_LINES a,
ahl_workorders wo,
csi_item_instances csii,
ahl_visit_tasks_b vts
WHERE a.osp_order_id = p_osp_id
AND wo.workorder_id(+) = a.workorder_id
AND wo.visit_task_id = vts.visit_task_id(+)
AND csii.last_vld_organization_id(+) = a.inventory_org_id
AND csii.inventory_item_id(+) = a.inventory_item_id
AND csii.lot_number(+) = a.lot_number
AND (decode(a.workorder_id, NULL, csii.instance_id, vts.instance_id) = p_csi_ii_id OR a.exchange_instance_id = p_csi_ii_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,
-- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
-- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
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;
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,
-- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
a.PO_REQ_LINE_ID
-- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
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,
-- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
-- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
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 Delete_OE_Lines(p_oe_line_id IN NUMBER) IS
--
--Fetch all osp lines with reference to p_oe_line_id
CURSOR ahl_osp_lines_csr(p_line_id IN NUMBER) IS
SELECT *
FROM AHL_OSP_ORDER_LINES
WHERE oe_ship_line_id = p_line_id
OR oe_return_line_id = p_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_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,
-- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
-- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
P_EXCHANGE_INSTANCE_ID => l_osp_line_rec.EXCHANGE_INSTANCE_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 Delete_OE_Lines;
SELECT transaction_type_id
FROM csi_txn_types
WHERE source_transaction_type = p_source_txn_type;
SELECT sub_type_id
FROM csi_txn_sub_types
WHERE transaction_type_id = p_transaction_type_id
AND name = p_sub_type_name;
SELECT inventory_item_id, ordered_quantity, ship_from_org_id, order_quantity_uom
FROM oe_order_lines_all
WHERE line_id = p_oe_line_id;
SELECT serial_number, inventory_revision, lot_number, mfg_serial_number_flag
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT nvl(need_by_date, ship_by_date)
FROM ahl_osp_order_lines
WHERE oe_ship_line_id = p_oe_line_id;
SELECT internal_party_id from csi_install_parameters;
SELECT HZ.PARTY_ID, HZ.CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS HZ,
OE_ORDER_HEADERS_ALL OE,
oe_order_lines_all OEL
WHERE OEL.line_id = p_oe_line_id AND
OE.HEADER_ID = OEL.HEADER_ID AND
HZ.CUST_ACCOUNT_ID = OE.SOLD_TO_ORG_ID;
SELECT INSTANCE_PARTY_ID, PARTY_ID from csi_i_parties
WHERE INSTANCE_ID = p_instance_id AND
RELATIONSHIP_TYPE_CODE = 'OWNER' AND
NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
NVL(ACTIVE_END_DATE, SYSDATE + 1) >= SYSDATE;
PROCEDURE Delete_IB_Transaction(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_oe_line_id IN NUMBER)
IS
CURSOR csi_txn_lines_csr (p_oe_line_id IN NUMBER) IS
SELECT transaction_line_id
FROM csi_t_transaction_lines
WHERE SOURCE_TRANSACTION_ID = p_oe_line_id
-- 3/3/03: Corrected to include txn table
AND SOURCE_TRANSACTION_TABLE = G_TRANSACTION_TABLE;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Delete_IB_Transaction';
FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call csi_t_txn_details_grp.delete_transaction_dtls.');
csi_t_txn_details_grp.delete_transaction_dtls
( p_api_version => 1.0,
p_commit => p_commit,
-- Changed by jaramana on January 14, 2008 for the Requisition ER 6034236
p_init_msg_list => FND_API.G_FALSE,--p_init_msg_list,
p_validation_level => p_validation_level,
p_transaction_line_id => l_transaction_line_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Returned from call to csi_t_txn_details_grp.delete_transaction_dtls. x_return_status = ' || x_return_status);
END Delete_IB_Transaction;
SELECT 'X' from ahl_osp_orders_b where
OSP_ORDER_ID = p_osp_order_id and
status_code <> AHL_OSP_ORDERS_PVT.G_OSP_CLOSED_STATUS and
ORDER_TYPE_CODE IN (AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_SERVICE,
AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_EXCHANGE);
SELECT DISTINCT OSPL.OE_RETURN_LINE_ID, OE.shipped_quantity, OE.booked_flag
from ahl_osp_order_lines OSPL, oe_order_lines_all OE
where OSPL.osp_order_id = p_osp_order_id AND
OSPL.OE_RETURN_LINE_ID IS NOT NULL AND
OE.line_id = OSPL.OE_RETURN_LINE_ID;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Delete_Cancel_Order.');
Delete_Cancel_Order (p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_oe_header_id => NULL, -- Don't delete the shipment header!
p_oe_lines_tbl => l_oe_line_ids_tbl, -- Delete only the return lines
p_cancel_flag => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Delete_Cancel_Order: x_return_status = ' || x_return_status);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Not calling Delete_Cancel_Order since there are no return shipment lines');
SELECT OSPL.OE_SHIP_LINE_ID,
ospl.item_instance_id,
--wo.item_instance_id, --Jeli on 01/24/2006 for ER 4746426
NVL(OE.shipped_quantity, 0)
from ahl_osp_order_lines_v OSPL, oe_order_lines_all OE --, AHL_WORKORDERS_OSP_V wo
where OSPL.osp_order_id = p_osp_order_id AND
--ospl.workorder_id = wo.workorder_id AND
OSPL.OE_SHIP_LINE_ID IS NOT NULL AND
OE.line_id = OSPL.OE_SHIP_LINE_ID;
SELECT OSPL.OE_SHIP_LINE_ID,
decode(wo.workorder_id, null, csii.instance_id, vts.instance_id) item_instance_id,
NVL(OE.shipped_quantity, 0)
from ahl_osp_order_lines OSPL,
oe_order_lines_all OE,
ahl_workorders wo,
ahl_visit_tasks_b vts,
csi_item_instances csii
where OSPL.osp_order_id = p_osp_order_id
AND OSPL.OE_SHIP_LINE_ID IS NOT NULL
AND OE.line_id = OSPL.OE_SHIP_LINE_ID
AND wo.workorder_id(+) = ospl.workorder_id
AND wo.visit_task_id = vts.visit_task_id(+)
AND csii.last_vld_organization_id(+) = ospl.inventory_org_id
AND csii.inventory_item_id(+) = ospl.inventory_item_id
AND csii.serial_number(+) = ospl.serial_number;
SELECT internal_party_id from csi_install_parameters;
SELECT INSTANCE_PARTY_ID, PARTY_ID, OBJECT_VERSION_NUMBER from csi_i_parties where
INSTANCE_ID = p_instance_id AND
relationship_type_code = 'OWNER' AND
NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
NVL(ACTIVE_END_DATE, SYSDATE + 1) >= SYSDATE;
SELECT HZ.PARTY_ID, HZ.CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS HZ,
OE_ORDER_HEADERS_ALL OE,
oe_order_lines_all OEL
WHERE OEL.line_id = p_oe_line_id AND
OE.HEADER_ID = OEL.HEADER_ID AND
HZ.CUST_ACCOUNT_ID = OE.SOLD_TO_ORG_ID;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call CSI_ITEM_INSTANCE_PUB.UPDATE_ITEM_INSTANCE');
CSI_ITEM_INSTANCE_PUB.UPDATE_ITEM_INSTANCE(
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_party_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_asset_assignment_tbl => l_asset_assignment_tbl,
p_txn_rec => l_transaction_rec,
x_instance_id_lst => l_instance_id_lst,
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 CSI_ITEM_INSTANCE_PUB.UPDATE_ITEM_INSTANCE, x_return_status = ' || l_return_status);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Since l_shipped_quantity = 0, No need to change owner. Calling Delete_IB_Transaction.');
Delete_IB_Transaction(
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oe_line_id => l_oe_ship_line_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from Delete_IB_Transaction, x_return_status = ' || l_return_status);
SELECT INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = p_instance_id;
SELECT OE_HEADER_ID
FROM AHL_OSP_ORDERS_B
WHERE OSP_ORDER_ID = p_osp_order_id;
SELECT 1
FROM po_vendor_sites_all
WHERE VENDOR_SITE_ID = p_vendor_loc_id
AND VENDOR_ID = p_vendor_id;
SELECT CUSTOMER_SITE_ID
FROM AHL_VENDOR_CUSTOMER_RELS
WHERE VENDOR_SITE_ID = p_vendor_loc_id;
SELECT osp.vendor_id,
osp.vendor_site_id,
osp.vendor_contact_id,
osp.osp_order_number,
cust.customer_site_id,
cust.customer_id
FROM ahl_osp_orders_b osp,
ahl_vendor_customer_rels_v cust
WHERE osp.osp_order_id = p_osp_order_id
AND osp.vendor_site_id = cust.vendor_site_id;
select line_id from oe_order_lines_all
where header_id = c_oe_header_id;
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_oe_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT serial_number_control_code,
comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE inventory_item_id = c_inv_item_id
AND organization_id = c_inv_org_id;
SELECT csi.instance_id,
csi.inventory_item_id,
csi.serial_number,
csi.last_vld_organization_id
FROM csi_t_transaction_lines tl,
csi_t_txn_line_details tld,
ahl_osp_order_lines ospl,
csi_item_instances csi
WHERE tl.source_transaction_id = ospl.oe_ship_line_id
AND tl.source_transaction_table = G_TRANSACTION_TABLE
AND tl.transaction_line_id = tld.transaction_line_id
AND ospl.osp_order_line_id = c_osp_order_line_id
AND tld.instance_id = csi.instance_id;
SELECT ospl.inventory_item_id,
ospl.inventory_org_id,
ospl.serial_number,
ospl.oe_ship_line_id,
nvl(oesh.shipped_quantity,0) shipped_quantity,
ospl.oe_return_line_id,
nvl(oert.shipped_quantity,0) returned_quantity,
ospl.status_code osp_line_status_code,
osph.status_code osp_header_status_code,
osph.order_type_code
FROM ahl_osp_order_lines ospl,
ahl_osp_orders_b osph,
oe_order_lines_all oesh,
oe_order_lines_all oert
WHERE ospl.osp_order_line_id = c_osp_order_line_id
AND ospl.osp_order_id = osph.osp_order_id
AND ospl.oe_ship_line_id = oesh.line_id(+)
AND ospl.oe_return_line_id = oert.line_id(+);
Check that the Order Type is Service and the status of the order is not 'CLOSED' and the status of the order line is not populated, which would mean that the related purchase line details have been cancelled or deleted.
*/
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'order_type_code -> ' ||l_get_order_details.order_type_code ||
'osp_header_status_code -> ' ||l_get_order_details.osp_header_status_code ||
'osp_line_status_code -> ' ||l_get_order_details.osp_line_status_code);
We are assuming that this instance_id details are not manually updated/deleted by the user from
OM forms or elsewhere. If the item is IB tracked, at the time of ship line creation, we create the IB installation
details as well. Since the item and serial on the osp order line can undergo multiple part number/serial number changes,
and we are not storing the instance information we are retrieving it from IB transactions.
We need this instance to be present, otherwise we will not be able to pass the same to the PartNumber/Serial Number
change UI. If there is an issue with this approach, we may need to retrieve the instance details from
the history/consider storing the instance_id in the ahl_osp_order_lines table.
*/
OPEN get_ship_line_instance(p_osp_order_line_id);
SELECT ospl.inventory_item_id,
ospl.serial_number,
ospl.inventory_org_id,
ospl.oe_ship_line_id,
ospl.oe_return_line_id,
retl.ship_from_org_id receiving_org_id,
ospl.osp_order_id
FROM ahl_osp_order_lines ospl,
oe_order_lines_all retl
WHERE osp_order_line_id = c_osp_order_line_id
AND ospl.oe_return_line_id = retl.line_id(+);
SELECT tld.instance_id
FROM csi_t_transaction_lines tl,
csi_t_txn_line_details tld,
ahl_osp_order_lines ospl
WHERE tl.source_transaction_id = ospl.oe_ship_line_id
AND tl.source_transaction_table = G_TRANSACTION_TABLE
AND tl.transaction_line_id = tld.transaction_line_id
AND ospl.osp_order_line_id = c_osp_order_line_id;
SELECT instance_number,
instance_id,
object_version_number,
inventory_item_id,
serial_number,
lot_number,
inventory_revision
FROM csi_item_instances
WHERE instance_id = c_instance_id;
SELECT serial_number_control_code,
comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE inventory_item_id = c_inv_item_id
AND organization_id = c_inv_org_id;
SELECT lot_serial_id
FROM oe_lot_serial_numbers
WHERE line_id = p_oe_line_id;
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
-- Added by jaramana on 12-APR-2010 for bug 9229301
-- Check only within the current OSP Order
AND passed_ol.osp_order_id = matched_ol.osp_order_id;
SELECT serial_number_control_code,
lot_control_code,
comms_nl_trackable_flag,
concatenated_segments
FROM mtl_system_items_vl
WHERE inventory_item_id = c_inventory_id
AND organization_id = c_organization_id;
SELECT inventory_item_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE CONCATENATED_SEGMENTS = c_item_number;
We are assuming that this instance_id details are not manually updated/deleted by the user from
OM forms or elsewhere. If the item is IB tracked, at the time of ship line creation, we create the IB installation
details as well. Since the item and serial on the osp order line can undergo multiple part number/serial number changes,
and we are not storing the instance information we are retrieving it from IB transactions.
We need this instance to be present, otherwise we will not be able to pass the same to the PartNumber/Serial Number
change UI. If there is an issue with this approach, we may need to retrieve the instance details from
the history/consider storing the instance_id in the ahl_osp_order_lines table.
*/
--If passed instance_id is null, derive it from the osp line, else check that its the one present on the osp line.
OPEN get_ship_line_instance(l_serialnum_change_rec.osp_line_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before calling Delete_IB_Transaction' );
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 => l_osp_order_line_dtls.oe_return_line_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After calling Delete_IB_Transaction: x_return_status =>'||x_return_status );
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Delete_Cancel_Order');
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 => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from Delete_Cancel_Order, x_return_status = ' || x_return_status);
CSI_Item_Instance_PUB.Update_Item_Instance(
p_api_version => 1.0,
p_instance_rec => l_csi_instance_rec,
p_txn_rec => l_csi_transaction_rec,
p_ext_attrib_values_tbl => l_csi_ext_attrib_values_tbl,
p_party_tbl => l_csi_party_tbl,
p_account_tbl => l_csi_account_tbl,
p_pricing_attrib_tbl => l_csi_pricing_attrib_tbl,
p_org_assignments_tbl => l_csi_org_assignments_tbl,
p_asset_assignment_tbl => l_csi_asset_assignment_tbl,
x_instance_id_lst => l_csi_instance_id_lst,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
we are currently going through the update of the oe_lot_serial table, though this table does not store
the revision number, so that the logic is kept simple. The serial change page ideally is not meant for
isolated revision number changes. We are still cateting to it, just that we will be updating the record below
though it will effectively not have any changes.
*/
l_oe_lot_serial_rec.from_serial_number := l_serialnum_change_rec.new_serial_number;
l_oe_lot_serial_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
We need to update the osp_order_line with the new return_line_id, if part number change has happened.
If multiple services are being performed on the same item, we create only one shipment/return line.
So we need to identify all such lines and update the oe_return_line_id of all such lines.
How do we identify such lines? They should have the same Item and Serial Number on all the order lines.
Even if a part number change has been done before, it would have been anyway applicable to all the
order lines. So this logic of looking at the old item/serial numbers should be sufficient to retrieve all
the order lines having the same physical item.
*/
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Getting order lines with same physical item for Osp Order Id: ' ||l_osp_order_line_dtls.osp_order_id);
Update_OSP_Order_Lines(
p_osp_order_id => l_osp_order_line_dtls.osp_order_id,
p_osp_line_id => l_order_line_id,
p_oe_ship_line_id => FND_API.G_MISS_NUM ,
p_oe_return_line_id => l_osp_order_line_dtls.oe_return_line_id);