[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_order_status_for_update VARCHAR2(30) := NULL;
PROCEDURE update_osp_order_header(
p_x_osp_order_rec IN OUT NOCOPY osp_order_rec_type);
PROCEDURE update_osp_order_line(
p_x_osp_order_line_rec IN OUT NOCOPY osp_order_line_rec_type);
PROCEDURE validate_order_line_update(
p_x_osp_order_line_rec IN OUT NOCOPY osp_order_line_rec_type);
PROCEDURE delete_cancel_so(
p_oe_header_id IN NUMBER,
p_del_cancel_so_lines_tbl IN del_cancel_so_lines_tbl_type,
p_cancel_flag IN VARCHAR2 := FND_API.G_FALSE
);
PROCEDURE validate_osp_updates(
p_osp_order_rec IN osp_order_rec_type
);
PROCEDURE Get_Warranty_Rec_for_Update(
p_x_osp_order_line_rec IN osp_order_line_rec_type,
x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type,
x_call_process_entl OUT NOCOPY VARCHAR2
);
PROCEDURE Get_Entl_rec_for_Delete(
p_osp_order_id IN NUMBER := null,
p_osp_order_line_id IN NUMBER := null,
x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
);
SELECT vendor_id FROM po_vendors_view
WHERE vendor_name = p_vendor_name
AND enabled_flag = G_YES_FLAG
AND NVL(vendor_start_date_active, SYSDATE - 1) <= SYSDATE
AND NVL(vendor_end_date_active, SYSDATE + 1) > SYSDATE;
SELECT vendor_site_id FROM po_vendor_sites
WHERE vendor_site_code = p_vendor_site_code
AND NVL(inactive_date, SYSDATE + 1) > SYSDATE
AND purchasing_site_flag = G_YES_FLAG;
SELECT buyer_id FROM po_agents_name_v
WHERE full_name = p_buyer_name;
SELECT party_id from hz_parties
where party_name = p_customer_name and party_type = 'ORGANIZATION';
SELECT chr.id FROM okc_k_headers_b chr, okc_statuses_b sts
WHERE chr.contract_number = p_contract_number
AND chr.sts_code = sts.code AND sts.ste_code in ('ACTIVE', 'SIGNED');
SELECT osp_order_id, object_version_number from ahl_osp_orders_b
WHERE osp_order_number = p_osp_order_number;
SELECT osp_order_id, object_version_number from ahl_osp_orders_b
WHERE po_header_id = p_po_header_id;
SELECT osp_order_id, object_version_number from ahl_osp_orders_b
WHERE oe_header_id = p_oe_header_id;
IF (p_x_osp_order_rec.operation_flag IS NOT NULL AND p_x_osp_order_rec.operation_flag NOT IN(G_OP_CREATE, G_OP_UPDATE, G_OP_DELETE)) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
SELECT 'X'
FROM po_vendors_view
WHERE vendor_id = p_vendor_id
AND enabled_flag = G_YES_FLAG
AND NVL(vendor_start_date_active, SYSDATE - 1) <= SYSDATE
AND NVL(vendor_end_date_active, SYSDATE + 1) > SYSDATE;
SELECT 'X'
FROM ahl_vendor_certifications_v
WHERE vendor_id = p_vendor_id
AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
AND TRUNC(nvl(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT 'x' FROM po_vendor_sites
WHERE vendor_site_id = p_vendor_site_id
AND vendor_id = p_vendor_id
AND NVL(inactive_date, SYSDATE + 1) > SYSDATE
AND purchasing_site_flag = G_YES_FLAG
AND NVL(RFQ_ONLY_SITE_FLAG, G_NO_FLAG) = G_NO_FLAG;
SELECT 'X'
FROM ahl_vendor_certifications_v
WHERE vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id
AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
AND TRUNC(nvl(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT 'x' from hz_parties
where party_id = p_customer_id and party_type = 'ORGANIZATION';
SELECT 'x' FROM po_agents_name_v
WHERE buyer_id = p_po_agent_id;
SELECT 'x' FROM okc_k_headers_b chr, okc_k_party_roles_b cpl, okc_statuses_b sts
WHERE chr.id = p_contract_id
AND cpl.object1_id1 = p_party_vendor_id
AND chr.authoring_org_id = p_authoring_org_id
AND chr.id = cpl.chr_id AND chr.buy_or_sell = p_buy_or_sell AND chr.sts_code = sts.code AND sts.ste_code in ('ACTIVE', 'SIGNED')
AND cpl.jtot_object1_code = p_object_code;
SELECT 'x' FROM po_headers_all
WHERE po_header_id = p_po_header_id
AND reference_num = p_osp_order_id
AND interface_source_code = G_APP_NAME;
SELECT osp_order_number, order_type_code, single_instance_flag, po_header_id, oe_header_id,vendor_id, vendor_site_id,
customer_id,order_date,contract_id,contract_terms,operating_unit_id, po_synch_flag, status_code,
po_batch_id, po_request_id,po_agent_id, po_interface_header_id, po_req_header_id , description, vendor_contact_id
-- jaramana End
FROM ahl_osp_orders_vl
WHERE osp_order_id = p_osp_order_id
AND object_version_number = p_object_version_number;
SELECT POREQ.SEGMENT1
FROM PO_REQUISITION_HEADERS_ALL POREQ, AHL_OSP_ORDERS_B OSP
WHERE POREQ.INTERFACE_SOURCE_LINE_ID = c_osp_order_id
AND OSP.OSP_ORDER_ID = c_osp_order_id
AND OSP.OPERATING_UNIT_ID = POREQ.ORG_ID
AND POREQ.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME
AND NVL(POREQ.CLOSED_CODE, 'X') NOT IN ('CANCELLED', 'CLOSED', 'CLOSED FOR INVOICE', 'CLOSED FOR RECEIVING',
'FINALLY CLOSED', 'REJECTED', 'RETURNED');
--g_order_status_for_update := l_osp_order_rec.status_code;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.type', 'Update Order type is Service or Exchange');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.type', 'Update Order type is Loan or Borrow');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.type.oper', 'Update Order type is Service or Exchange Operation is Create');
IF(p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.srvExch.update', 'Update Order type is Service or Exchange Operation is Update ');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.srvExch.update.', 'Update Order type is Service or Exchange Operation is Update'
|| 'g_old_status_code' || g_old_status_code);
it can be changed to G_OSP_ENTERED_STATUS (No update)
2. If the current status is G_OSP_SUB_FAILED_STATUS, the status cannot be changed to G_OSP_ENTERED_STATUS or
G_OSP_REQ_SUB_FAILED_STATUS. It can only be changed to G_OSP_SUBMITTED_STATUS or G_OSP_REQ_SUBMITTED_STATUS or
it can be changed to G_OSP_SUB_FAILED_STATUS (No update)
3. If the current status is G_OSP_REQ_SUB_FAILED_STATUS, the status cannot be changed to G_OSP_ENTERED_STATUS or
G_OSP_SUB_FAILED_STATUS. It can only be changed to G_OSP_SUBMITTED_STATUS or G_OSP_REQ_SUBMITTED_STATUS or
it can be changed to G_OSP_REQ_SUB_FAILED_STATUS (No update)
In all the above the changes status is either itself or G_OSP_SUBMITTED_STATUS or G_OSP_REQ_SUBMITTED_STATUS
*/
IF(p_x_osp_order_rec.status_code IS NOT NULL AND p_x_osp_order_rec.status_code <> g_old_status_code AND p_x_osp_order_rec.status_code NOT IN (G_OSP_SUBMITTED_STATUS,G_OSP_REQ_SUBMITTED_STATUS )) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_REQ_NOT_DELETED');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.srvExch.update.', 'Update Order type is Service or Exchange Operation is Update'
|| 'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
g_order_status_for_update := G_OSP_SUB_FAILED_STATUS;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.srvExch.update.', 'Update Order type is Service or Exchange Operation is Update'
|| 'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.srvExch.update.', 'Update Order type is Service or Exchange Operation is Update'
|| 'g_old_status_code: ' || g_old_status_code);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.srvExch.update.', 'Update Order type is Service or Exchange Operation is Update'||
'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.srvExch.update.', 'Update Order type is Service or Exchange Operation is Update'
|| 'g_old_status_code: ' || g_old_status_code);
END IF; --IF(p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.loan', 'Update order type is Loan');
ELSIF(p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
IF(g_old_status_code = G_OSP_ENTERED_STATUS) THEN
-- Changed by jaramana on January 9, 2008 for the Requisition ER 6034236
-- status_code should be null,'ENTERED', or 'SUBMITTED' or 'REQ_SUBMITTED'
IF(p_x_osp_order_rec.status_code IS NOT NULL AND
p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUBMITTED_STATUS, G_OSP_REQ_SUBMITTED_STATUS)) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
ELSIF(p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
IF(g_old_status_code = G_OSP_ENTERED_STATUS) THEN
-- status_code should be null,'ENTERED', or 'SUBMITTED'
IF(p_x_osp_order_rec.status_code IS NOT NULL AND
p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUBMITTED_STATUS)) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
IF(p_x_osp_order_rec.operation_flag = G_OP_DELETE) THEN
-- Changed by jaramana on January 9, 2008 for the Requisition ER 6034236 (Added G_OSP_REQ_SUB_FAILED_STATUS)
IF(g_old_status_code NOT IN( G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_REQ_SUB_FAILED_STATUS ))THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
delete_cancel_so(
p_oe_header_id => l_osp_order_rec.oe_header_id,
p_del_cancel_so_lines_tbl =>l_del_cancel_so_lines_tbl
);
SELECT osp_order_number, order_type_code, single_instance_flag, po_header_id, oe_header_id,vendor_id, vendor_site_id,
customer_id,order_date,contract_id,contract_terms,operating_unit_id, po_synch_flag, status_code,
po_batch_id, po_request_id,po_agent_id, po_interface_header_id, po_req_header_id, description,attribute_category,
attribute1,attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, attribute11, attribute12, attribute13, attribute14, attribute15, vendor_contact_id
-- jaramana End
FROM ahl_osp_orders_vl
WHERE osp_order_id = p_osp_order_id
AND object_version_number= p_object_version_number;
IF(p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
OPEN osp_order_csr(p_x_osp_order_rec.osp_order_id, p_x_osp_order_rec.object_version_number);
SELECT 'x' FROM ahl_workorders_osp_v
WHERE workorder_id = p_workorder_id
AND upper(department_class_code) = 'VENDOR'
AND job_status_code = G_OSP_WO_RELEASED;
SELECT 'x'
FROM ahl_workorders wo,
wip_discrete_jobs wdj,
bom_departments bmd,
ahl_visits_b vst,
ahl_visit_tasks_b vts,
inv_organization_info_v org
WHERE wo.workorder_id = p_workorder_id
AND wo.master_workorder_flag = 'N'
AND wo.visit_task_id = vts.visit_task_id
AND vst.visit_id = vts.visit_id
AND wdj.organization_id = vst.organization_id
AND wdj.wip_entity_id = wo.wip_entity_id
AND wdj.owning_department = bmd.department_id
AND upper(bmd.department_class_code) = 'VENDOR'
AND org.organization_id = vst.organization_id
AND NVL (org.operating_unit, mo_global.get_current_org_id ()) = mo_global.get_current_org_id()
AND wo.status_code = G_OSP_WO_RELEASED;
SELECT 'x' FROM mtl_system_items_kfv MTL, ahl_workorders_osp_v WO
WHERE MTL.inventory_item_id = p_service_item_id
AND MTL.enabled_flag = G_YES_FLAG
AND MTL.inventory_item_flag = G_NO_FLAG
AND MTL.stock_enabled_flag = G_NO_FLAG
AND NVL(MTL.start_date_active, SYSDATE - 1) <= SYSDATE
AND NVL(MTL.end_date_active, SYSDATE + 1) > SYSDATE
AND MTL.purchasing_enabled_flag = G_YES_FLAG
AND NVL(outside_operation_flag, G_NO_FLAG) = G_NO_FLAG
AND MTL.organization_id = WO.organization_id
AND WO.workorder_id = p_workorder_id;
SELECT 'x' from ahl_workorders_osp_v
WHERE workorder_id = p_workorder_id
AND NVL(service_item_id,p_service_item_id) = p_service_item_id;
SELECT 'x' from ahl_workorders_osp_v
WHERE workorder_id = p_workorder_id
AND service_item_id IS NULL;
SELECT 'x'
from ahl_workorders_osp_v wo, ahl_mtl_items_ou_v mtl
where wo.workorder_id = p_workorder_id and
wo.service_item_id = mtl.inventory_item_id and
mtl.inventory_org_id = wo.organization_id;
SELECT 'x' FROM mtl_system_items_kfv MTL
WHERE MTL.inventory_item_id = p_service_item_id
AND MTL.DESCRIPTION = p_service_item_description;
SELECT 'x' FROM mtl_system_items_kfv MTL, ahl_workorders WO, AHL_VISITS_B VST
WHERE MTL.inventory_item_id = p_service_item_id
AND MTL.enabled_flag = G_YES_FLAG
AND MTL.inventory_item_flag = G_NO_FLAG
AND MTL.stock_enabled_flag = G_NO_FLAG
AND NVL(MTL.start_date_active, SYSDATE - 1) <= SYSDATE
AND NVL(MTL.end_date_active, SYSDATE + 1) > SYSDATE
AND MTL.purchasing_enabled_flag = G_YES_FLAG
AND NVL(outside_operation_flag, G_NO_FLAG) = G_NO_FLAG
AND MTL.organization_id = VST.organization_id
AND VST.visit_id = WO.visit_id
AND WO.workorder_id = p_workorder_id;
SELECT 'x' from ahl_workorders WO, AHL_ROUTES_B ARB
WHERE WO.workorder_id = p_workorder_id
AND WO.ROUTE_ID = ARB.ROUTE_ID (+)
AND NVL(ARB.service_item_id, p_service_item_id) = p_service_item_id;
SELECT 'x' from ahl_workorders WO, AHL_ROUTES_B ARB
WHERE WO.workorder_id = p_workorder_id
AND WO.ROUTE_ID = ARB.ROUTE_ID (+)
AND ARB.service_item_id IS NULL;
SELECT 'x'
from ahl_workorders WO, AHL_ROUTES_B ARB, AHL_VISITS_B VST, ahl_mtl_items_ou_v mtl
where wo.workorder_id = p_workorder_id and
WO.ROUTE_ID = ARB.ROUTE_ID (+) and
ARB.service_item_id = mtl.inventory_item_id and
VST.visit_id = WO.visit_id and
mtl.inventory_org_id = VST.organization_id;
SELECT 'x' FROM ahl_item_class_uom_v
WHERE inventory_item_id = p_service_item_id
AND uom_code = p_service_item_uom_code
AND inventory_org_id = p_org_id;
SELECT 'x' from mtl_units_of_measure_vl
WHERE uom_code = p_service_item_uom_code;
SELECT 'x' FROM po_line_types
WHERE line_type_id = p_po_line_type_id and
ORDER_TYPE_LOOKUP_CODE = 'QUANTITY' and
NVL(OUTSIDE_OPERATION_FLAG, G_NO_FLAG) = G_NO_FLAG;
SELECT 'x' FROM po_lines_all POL, ahl_osp_orders_b OO
WHERE POL.po_line_id = p_po_line_id
AND OO.osp_order_id = p_osp_order_id
AND POL.PO_HEADER_ID = OO.po_header_id
-- Added by jaramana on January 9, 2008 to fix the Bug 5358438/5967633
AND NVL(POL.CANCEL_FLAG, 'N') <> 'Y'
--the line_id should not have already been associated to the same osp order
AND NOT EXISTS (select 1 from ahl_osp_order_lines
where osp_order_id = p_osp_order_id
and po_line_id = p_po_line_id );
SELECT 'x' FROM csi_item_instances csi
WHERE instance_id = p_instance_id
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
AND NOT EXISTS (select subject_id from csi_ii_relationships where
subject_id = p_instance_id and
relationship_type_code = 'COMPONENT-OF' and
trunc(sysdate) >= trunc(nvl(active_start_date,sysdate)) and
trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
) ;
SELECT 'x' FROM ahl_osp_order_lines
WHERE osp_order_id = p_osp_order_id;
SELECT oe_ship_line_id, oe_return_line_id, osp_line_number FROM ahl_osp_order_lines
WHERE osp_order_id = p_osp_order_id;
SELECT 'x' from ahl_osp_order_lines OL, ahl_workorders WO
WHERE OL.osp_order_id = p_osp_order_id
AND OL.workorder_id = WO.workorder_id
AND OL.status_code IS NULL
AND WO.status_code NOT IN(G_OSP_WO_CANCELLED,G_OSP_WO_CLOSED);
SELECT osp_line_number, service_item_id, service_item_description, service_item_uom_code, quantity, need_by_date, po_line_type_id
FROM AHL_OSP_ORDER_LINES
WHERE osp_order_id = p_osp_order_id
ORDER BY osp_line_number;
SELECT status_code, object_version_number
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_rec.osp_order_id;
SELECT 'x' from ahl_osp_order_lines OL, ahl_osp_accomplishments AOA
WHERE OL.osp_order_id = p_osp_order_id
AND ol.osp_order_line_id = AOA.OSP_ORDER_LINE_ID
AND AOA.STATUS_CODE = G_OSP_AOA_OPEN;
IF(p_x_osp_order_rec.order_type_code IN (G_OSP_ORDER_TYPE_SERVICE, G_OSP_ORDER_TYPE_EXCHANGE) AND p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
--IF(g_old_status_code IN (G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS) AND
-- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
IF(l_old_status_code IN (G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_REQ_SUB_FAILED_STATUS) AND
p_x_osp_order_rec.status_code IN (G_OSP_SUBMITTED_STATUS, G_OSP_REQ_SUBMITTED_STATUS)) THEN
-- jaramana End
-- validate fields and submit for PO creation
IF(p_x_osp_order_rec.vendor_id IS NULL) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VEN_NLL');
ELSIF(p_x_osp_order_rec.order_type_code IN(G_OSP_ORDER_TYPE_LOAN, G_OSP_ORDER_TYPE_BORROW) AND p_x_osp_order_rec.operation_flag = G_OP_UPDATE)THEN
--IF(g_old_status_code = G_OSP_ENTERED_STATUS AND
IF(l_old_status_code = G_OSP_ENTERED_STATUS AND
p_x_osp_order_rec.status_code = G_OSP_SUBMITTED_STATUS) THEN
IF(p_x_osp_order_rec.customer_id IS NULL AND p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
--only loan type requires customer id for borrow it's optional (bug fix).
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_CUST_NLL');
PROCEDURE delete_cancel_so(
p_oe_header_id IN NUMBER,
p_del_cancel_so_lines_tbl IN del_cancel_so_lines_tbl_type,
p_cancel_flag IN VARCHAR2 := FND_API.G_FALSE
) IS
l_Ship_ID_Tbl AHL_OSP_SHIPMENT_PUB.Ship_ID_Tbl_Type;
SELECT 'x' FROM ahl_osp_order_lines
WHERE osp_order_id = p_osp_order_id
AND oe_ship_line_id = p_oe_ship_line_id
AND status_code IS NULL;
SELECT 'x' FROM ahl_osp_order_lines
WHERE osp_order_id = p_osp_order_id
AND oe_return_line_id = p_oe_return_line_id
AND status_code IS NULL;
SELECT 'x' from oe_order_lines_all
where line_id = p_oe_line_id AND
shipped_quantity > 0;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.delete_cancel_so';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Calling AHL_OSP_SHIPMENT_PUB.Delete_Cancel_Order with p_oe_header_id = ' || p_oe_header_id);
AHL_OSP_SHIPMENT_PUB.Delete_Cancel_Order (
p_api_version => 1.0,
p_oe_header_id => p_oe_header_id,
p_oe_lines_tbl => l_Ship_ID_Tbl,
p_cancel_flag => p_cancel_flag,
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, 'Calling AHL_OSP_SHIPMENT_PUB.Delete_Cancel_Order with l_Ship_ID_Tbl.COUNT = ' || l_Ship_ID_Tbl.COUNT);
AHL_OSP_SHIPMENT_PUB.Delete_Cancel_Order (
p_api_version => 1.0,
p_oe_header_id => NULL,
p_oe_lines_tbl => l_Ship_ID_Tbl,
p_cancel_flag => p_cancel_flag,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
END delete_cancel_so;
SELECT 'x' FROM ahl_osp_order_lines ol --, oe_order_lines_all oel
WHERE ol.osp_order_id = p_osp_order_id
AND ol.oe_return_line_id IS NOT NULL; --= oel.line_id;
SELECT status_code FROM ahl_osp_orders_b
WHERE osp_order_id = p_osp_order_id;
SELECT 'x' FROM po_headers_all
WHERE po_header_id = p_po_header_id
AND vendor_id = p_vendor_id;
SELECT 'x' FROM po_headers_all
WHERE po_header_id = p_po_header_id
AND vendor_site_id = p_vendor_site_id;
SELECT osp_order_line_id
FROM ahl_osp_order_lines
WHERE osp_order_id = c_osp_order_id;
p_x_osp_order_rec.operation_flag NOT IN (G_OP_CREATE, G_OP_UPDATE, G_OP_DELETE)) THEN
FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVOP');
IF (p_x_osp_order_lines_tbl(i).operation_flag NOT IN (G_OP_CREATE, G_OP_UPDATE, G_OP_DELETE)) THEN
FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVOP');
IF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_DELETE) THEN
--Front end just passes osp_order_line_id and operation_flag
BEGIN
SELECT osp_order_id, oe_ship_line_id, oe_return_line_id
INTO l_osp_order_id, l_oe_ship_line_id, l_oe_return_line_id
FROM ahl_osp_order_lines
WHERE osp_order_line_id = p_x_osp_order_lines_tbl(i).osp_order_line_id;
SELECT status_code, oe_header_id INTO l_status_code, l_oe_header_id
FROM ahl_osp_orders_b
WHERE osp_order_id = l_osp_order_id;
Get_Entl_rec_for_Delete(
p_osp_order_line_id => p_x_osp_order_lines_tbl(i).osp_order_line_id,
x_warranty_entl_tbl => l_entl_rec_tbl);
AHL_OSP_ORDER_LINES_PKG.delete_row(p_x_osp_order_lines_tbl(i).osp_order_line_id);
delete_cancel_so(
p_oe_header_id => NULL,
p_del_cancel_so_lines_tbl => l_del_cancel_so_lines_tbl);
ELSIF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_UPDATE) THEN
--Update line record
update_osp_order_line(p_x_osp_order_lines_tbl(i));
Get_Warranty_Rec_for_Update(
p_x_osp_order_line_rec => p_x_osp_order_lines_tbl(i),
x_warranty_entl_tbl => l_entl_rec_tbl,
x_call_process_entl => l_call_process_entl);
IF p_x_osp_order_lines_tbl(i).operation_flag <> G_OP_DELETE AND
(NVL(p_x_osp_order_lines_tbl(i).status_code, 'ENTERED') NOT IN (G_OL_PO_CANCELLED_STATUS, G_OL_PO_DELETED_STATUS)) THEN
l_vendor_validate_flag := TRUE;
SELECT vendor_id, vendor_site_id INTO l_header_vendor_id, l_header_site_id
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_lines_tbl(l_first_index).osp_order_id;
ELSIF (p_x_osp_order_rec.operation_flag = G_OP_DELETE) THEN
IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
G_LOG_PREFIX||l_api_name, --module
'Within API: Delete OSP Header/Lines');
SELECT status_code, oe_header_id INTO l_status_code, l_oe_header_id
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_rec.osp_order_id;
SELECT oe_ship_line_id, oe_return_line_id
INTO l_oe_ship_line_id, l_oe_return_line_id
FROM ahl_osp_order_lines
WHERE osp_order_line_id = l_get_order_lines.osp_order_line_id;
'Before calling: delete_cancel_so and table_count='||to_char(i-1));
delete_cancel_so(
p_oe_header_id => l_oe_header_id,
p_del_cancel_so_lines_tbl => l_dummy_dc_so_lines_tbl);
Get_Entl_rec_for_Delete(
p_osp_order_id => p_x_osp_order_rec.osp_order_id,
x_warranty_entl_tbl => l_entl_rec_tbl);
AHL_OSP_ORDER_LINES_PKG.delete_row(l_get_order_lines.osp_order_line_id);
AHL_OSP_ORDERS_PKG.delete_row(x_osp_order_id => p_x_osp_order_rec.osp_order_id);
ELSIF (p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
G_LOG_PREFIX||l_api_name, --module
'Within API: Update OSP Header/Lines');
update_osp_order_header(p_x_osp_order_rec);
IF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_DELETE) THEN
--Front end just passes osp_order_line_id and operation_flag
BEGIN
SELECT osp_order_id, oe_ship_line_id, oe_return_line_id
INTO l_osp_order_id, l_oe_ship_line_id, l_oe_return_line_id
FROM ahl_osp_order_lines
WHERE osp_order_line_id = p_x_osp_order_lines_tbl(i).osp_order_line_id;
SELECT status_code, oe_header_id INTO l_status_code, l_oe_header_id
FROM ahl_osp_orders_b
WHERE osp_order_id = l_osp_order_id;
Get_Entl_rec_for_Delete(
p_osp_order_line_id => p_x_osp_order_lines_tbl(i).osp_order_line_id,
x_warranty_entl_tbl => l_entl_rec_tbl);
AHL_OSP_ORDER_LINES_PKG.delete_row(p_x_osp_order_lines_tbl(i).osp_order_line_id);
delete_cancel_so(
p_oe_header_id => NULL,
p_del_cancel_so_lines_tbl => l_del_cancel_so_lines_tbl);
ELSIF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_UPDATE) THEN
--update osp order line
update_osp_order_line(p_x_osp_order_lines_tbl(i));
Get_Warranty_Rec_for_Update(
p_x_osp_order_line_rec => p_x_osp_order_lines_tbl(i),
x_warranty_entl_tbl => l_entl_rec_tbl,
x_call_process_entl => l_call_process_entl);
SELECT vendor_id, vendor_site_id INTO l_header_vendor_id, l_header_site_id
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_rec.osp_order_id;
SELECT PA.buyer_id
FROM po_agents_name_v PA,
fnd_user FU
WHERE FU.user_id = fnd_global.user_id
AND PA.buyer_id = FU.employee_id;
SELECT ahl_osp_orders_b_s.NEXTVAL
INTO l_osp_order_id
FROM sys.dual;
SELECT organization_id, inventory_item_id, service_item_id
INTO l_organization_id, l_inventory_item_id, l_service_item_id
FROM ahl_workorders_osp_v
WHERE workorder_id = p_x_osp_order_lines_tbl(i).workorder_id;
SELECT vst.organization_id, vts.inventory_item_id, arb.service_item_id
INTO l_organization_id, l_inventory_item_id, l_service_item_id
FROM ahl_workorders wo,
ahl_visits_b vst,
ahl_visit_tasks_b vts,
ahl_routes_b arb,
inv_organization_info_v org
WHERE wo.workorder_id = p_x_osp_order_lines_tbl (i).workorder_id
AND wo.route_id = arb.route_id(+)
AND wo.master_workorder_flag = 'N'
AND wo.visit_id = vst.visit_id
AND wo.visit_task_id = vts.visit_task_id
AND vst.visit_id = vts.visit_id
AND vst.organization_id = org.organization_id
AND NVL (org.operating_unit, mo_global.get_current_org_id ())= mo_global.get_current_org_id();
AHL_OSP_ORDERS_PKG.insert_row(
x_rowid => l_rowid_dummy,
x_osp_order_id => l_osp_order_id,
x_object_version_number => 1,
x_created_by => FND_GLOBAL.user_id,
x_creation_date => SYSDATE,
x_last_updated_by => FND_GLOBAL.user_id,
x_last_update_date => SYSDATE,
x_last_update_login => FND_GLOBAL.login_id,
x_osp_order_number => l_osp_order_number,
x_order_type_code => p_x_osp_order_rec.order_type_code, --User entered
x_single_instance_flag => G_NO_FLAG,
x_po_header_id => NULL, --p_x_osp_order_rec.po_header_id,
x_oe_header_id => NULL, --p_x_osp_order_rec.oe_header_id,
x_vendor_id => l_vendor_id,
x_vendor_site_id => l_vendor_site_id,
x_vendor_contact_id => l_vendor_contact_id,
x_customer_id => p_x_osp_order_rec.customer_id,
x_order_date => TRUNC(SYSDATE),
x_contract_id => p_x_osp_order_rec.contract_id,
x_contract_terms => p_x_osp_order_rec.contract_terms,
x_operating_unit_id => l_operating_unit_id,
x_po_synch_flag => NULL, --p_x_osp_order_rec.po_synch_flag,
x_status_code => G_OSP_ENTERED_STATUS,
x_po_batch_id => NULL, --p_x_osp_order_rec.po_batch_id,
x_po_request_id => NULL, --p_x_osp_order_rec.po_request_id,
x_po_agent_id => l_buyer_id,
x_po_interface_header_id => NULL, --p_x_osp_order_rec.po_interface_header_id,
-- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
x_po_req_header_id => NULL,
-- jaramana End
x_description => p_x_osp_order_rec.description,
x_attribute_category => p_x_osp_order_rec.attribute_category,
x_attribute1 => p_x_osp_order_rec.attribute1,
x_attribute2 => p_x_osp_order_rec.attribute2,
x_attribute3 => p_x_osp_order_rec.attribute3,
x_attribute4 => p_x_osp_order_rec.attribute4,
x_attribute5 => p_x_osp_order_rec.attribute5,
x_attribute6 => p_x_osp_order_rec.attribute6,
x_attribute7 => p_x_osp_order_rec.attribute7,
x_attribute8 => p_x_osp_order_rec.attribute8,
x_attribute9 => p_x_osp_order_rec.attribute9,
x_attribute10 => p_x_osp_order_rec.attribute10,
x_attribute11 => p_x_osp_order_rec.attribute11,
x_attribute12 => p_x_osp_order_rec.attribute12,
x_attribute13 => p_x_osp_order_rec.attribute13,
x_attribute14 => p_x_osp_order_rec.attribute14,
x_attribute15 => p_x_osp_order_rec.attribute15);
PROCEDURE update_osp_order_header(p_x_osp_order_rec IN OUT NOCOPY osp_order_rec_type)
IS
l_operating_unit_id NUMBER;
SELECT osp_order_line_id, inventory_item_id,
inventory_org_id, service_item_id, workorder_id
FROM ahl_osp_order_lines
WHERE osp_order_id = c_osp_order_id;
SELECT vendor_id, vendor_site_id, oe_header_id
FROM ahl_osp_orders_b
WHERE osp_order_id = c_osp_order_id;
G_LOG_PREFIX || '.update_osp_order_header',
'Procedure begins and osp_order_id='||p_x_osp_order_rec.osp_order_id);
AHL_OSP_ORDERS_PKG.update_row(
x_osp_order_id => p_x_osp_order_rec.osp_order_id,
x_object_version_number => p_x_osp_order_rec.object_version_number + 1,
x_osp_order_number => p_x_osp_order_rec.osp_order_number,
x_order_type_code => p_x_osp_order_rec.order_type_code,
x_single_instance_flag => p_x_osp_order_rec.single_instance_flag,
x_po_header_id => p_x_osp_order_rec.po_header_id,
x_oe_header_id => p_x_osp_order_rec.oe_header_id,
x_vendor_id => p_x_osp_order_rec.vendor_id,
x_vendor_site_id => p_x_osp_order_rec.vendor_site_id,
x_vendor_contact_id => p_x_osp_order_rec.vendor_contact_id,
x_customer_id => p_x_osp_order_rec.customer_id,
x_order_date => TRUNC(p_x_osp_order_rec.order_date),
x_contract_id => p_x_osp_order_rec.contract_id,
x_contract_terms => p_x_osp_order_rec.contract_terms,
x_operating_unit_id => p_x_osp_order_rec.operating_unit_id,
x_po_synch_flag => p_x_osp_order_rec.po_synch_flag,
x_status_code => p_x_osp_order_rec.status_code,
x_po_batch_id => p_x_osp_order_rec.po_batch_id,
x_po_request_id => p_x_osp_order_rec.po_request_id,
x_po_agent_id => p_x_osp_order_rec.po_agent_id,
x_po_interface_header_id => p_x_osp_order_rec.po_interface_header_id,
-- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
x_po_req_header_id => p_x_osp_order_rec.po_req_header_id,
-- jaramana End
x_description => p_x_osp_order_rec.description,
x_attribute_category => p_x_osp_order_rec.attribute_category,
x_attribute1 => p_x_osp_order_rec.attribute1,
x_attribute2 => p_x_osp_order_rec.attribute2,
x_attribute3 => p_x_osp_order_rec.attribute3,
x_attribute4 => p_x_osp_order_rec.attribute4,
x_attribute5 => p_x_osp_order_rec.attribute5,
x_attribute6 => p_x_osp_order_rec.attribute6,
x_attribute7 => p_x_osp_order_rec.attribute7,
x_attribute8 => p_x_osp_order_rec.attribute8,
x_attribute9 => p_x_osp_order_rec.attribute9,
x_attribute10 => p_x_osp_order_rec.attribute10,
x_attribute11 => p_x_osp_order_rec.attribute11,
x_attribute12 => p_x_osp_order_rec.attribute12,
x_attribute13 => p_x_osp_order_rec.attribute13,
x_attribute14 => p_x_osp_order_rec.attribute14,
x_attribute15 => p_x_osp_order_rec.attribute15,
x_last_updated_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_update_login => fnd_global.login_id
);
G_LOG_PREFIX || '.update_osp_order_header',
'new_vendor='||p_x_osp_order_rec.vendor_id||
'old_vendor='||l_vendor_id);
G_LOG_PREFIX || '.update_osp_order_header',
'Before calling AHL_OSP_SHIPMENT_PUB.handle_vendor_change');
G_LOG_PREFIX || '.update_osp_order_header',
'Normally exit after calling AHL_OSP_SHIPMENT_PUB.handle_vendor_change'||
'x_return_status='||l_return_status);
END update_osp_order_header;
l_desc_update_flag VARCHAR2(1);
SELECT inventory_item_id,
organization_id,
lot_number,
serial_number,
quantity, --This quantity is from csi, so it means instance quantity
item_instance_uom,
service_item_id,
service_item_description,
service_item_uom
FROM ahl_workorders_osp_v
WHERE workorder_id = c_workorder_id;
SELECT vts.inventory_item_id,
vst.organization_id,
csii.lot_number,
csii.serial_number,
csii.quantity, --This quantity is from csi, so it means instance quantity
csii.unit_of_measure item_instance_uom,
arb.service_item_id,
mtls.description service_item_description,
mtls.primary_uom_code service_item_uom
FROM ahl_workorders wo,
ahl_visits_b vst,
ahl_visit_tasks_b vts,
csi_item_instances csii,
mtl_system_items_kfv mtls,
ahl_routes_b arb
WHERE wo.workorder_id = c_workorder_id
AND wo.visit_task_id = vts.visit_task_id
AND vst.visit_id = vts.visit_id
AND wo.route_id = arb.route_id(+)
AND arb.service_item_org_id = mtls.organization_id(+)
AND arb.service_item_id = mtls.inventory_item_id(+)
AND vts.instance_id = csii.instance_id;
SELECT ahl_osp_order_lines_s.NEXTVAL
INTO l_osp_line_id
FROM sys.dual;
SELECT NVL(MAX(osp_line_number), 0)+1
INTO l_osp_line_number
FROM ahl_osp_order_lines
WHERE osp_order_id = p_x_osp_order_line_rec.osp_order_id;
happens in the validate_order_creation, we only need to call the insert_row method here.
*/
IF(p_x_osp_order_line_rec.po_line_id is null) THEN
--For work order based lines, it is better to populate the physical item/service item related attributes
--And these attributes don't have to be validated.
IF (p_x_osp_order_line_rec.workorder_id IS NOT NULL) THEN
OPEN get_wo_item_attrs(p_x_osp_order_line_rec.workorder_id);
'After line validation and before insert operation:'||
'inv_item_id='||p_x_osp_order_line_rec.inventory_item_id||' service_item_id='||p_x_osp_order_line_rec.service_item_id);
SELECT primary_uom_code, description,concatenated_segments, allow_item_desc_update_flag INTO
l_temp_uom_code, l_item_description, l_svc_item_number, l_desc_update_flag
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_x_osp_order_line_rec.service_item_id
AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_SVC_DESC_NOCHNG');
SELECT concatenated_segments INTO l_inv_item_number
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_x_osp_order_line_rec.inventory_item_id
AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
SELECT vendor_id, vendor_site_id
INTO l_vendor_id, l_vendor_site_id
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_line_rec.osp_order_id;
SELECT IV.service_duration INTO l_service_duration
FROM ahl_inv_service_item_rels SI,
ahl_item_vendor_rels IV,
ahl_vendor_certifications_v VC
WHERE SI.inv_item_id = p_x_osp_order_line_rec.inventory_item_id
AND SI.inv_org_id = p_x_osp_order_line_rec.inventory_org_id
AND SI.service_item_id = p_x_osp_order_line_rec.service_item_id
AND VC.vendor_id = l_vendor_id
AND VC.vendor_site_id = l_vendor_site_id
AND SI.inv_service_item_rel_id = IV.inv_service_item_rel_id
AND IV.vendor_certification_id = VC.vendor_certification_id
AND trunc(SI.active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(SI.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(IV.active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(IV.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(VC.active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(VC.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
AHL_OSP_ORDER_LINES_PKG.insert_row(
p_x_osp_order_line_id => l_osp_line_id,
p_object_version_number => 1,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.login_id,
p_osp_order_id => p_x_osp_order_line_rec.osp_order_id,
p_osp_line_number => l_osp_line_number,
p_status_code => NULL, --Derived from header status when displaying
p_po_line_type_id => to_number(FND_PROFILE.VALUE('AHL_OSP_PO_LINE_TYPE_ID')),
p_service_item_id => p_x_osp_order_line_rec.service_item_id,
p_service_item_description => l_item_description,
p_service_item_uom_code => l_service_item_uom_code,
p_need_by_date => TRUNC(SYSDATE+l_service_duration),
p_ship_by_date => TRUNC(nvl(p_x_osp_order_line_rec.ship_by_date, SYSDATE)),
p_po_line_id => NULL,
-- by jaramana on January 10, 2008 to fix the Bug 5358438/5967633/5417460
--p_po_line_id => p_x_osp_order_line_rec.po_line_id, --yazhou 28-jul-2006 bug#5417460
p_oe_ship_line_id => NULL,
p_oe_return_line_id => NULL,
p_workorder_id => p_x_osp_order_line_rec.workorder_id,
p_operation_id => NULL,
-- p_quantity => l_quantity,
p_quantity => p_x_osp_order_line_rec.inventory_item_quantity,
p_exchange_instance_id => NULL,
p_inventory_item_id => p_x_osp_order_line_rec.inventory_item_id,
p_inventory_org_id => p_x_osp_order_line_rec.inventory_org_id,
p_sub_inventory => p_x_osp_order_line_rec.sub_inventory,
p_lot_number => p_x_osp_order_line_rec.lot_number,
p_serial_number => p_x_osp_order_line_rec.serial_number,
p_inventory_item_uom => p_x_osp_order_line_rec.inventory_item_uom,
p_inventory_item_quantity => p_x_osp_order_line_rec.inventory_item_quantity,
-- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
p_po_req_line_id => NULL,
-- jaramana End
p_attribute_category => p_x_osp_order_line_rec.attribute_category,
p_attribute1 => p_x_osp_order_line_rec.attribute1,
p_attribute2 => p_x_osp_order_line_rec.attribute2,
p_attribute3 => p_x_osp_order_line_rec.attribute3,
p_attribute4 => p_x_osp_order_line_rec.attribute4,
p_attribute5 => p_x_osp_order_line_rec.attribute5,
p_attribute6 => p_x_osp_order_line_rec.attribute6,
p_attribute7 => p_x_osp_order_line_rec.attribute7,
p_attribute8 => p_x_osp_order_line_rec.attribute8,
p_attribute9 => p_x_osp_order_line_rec.attribute9,
p_attribute10 => p_x_osp_order_line_rec.attribute10,
p_attribute11 => p_x_osp_order_line_rec.attribute11,
p_attribute12 => p_x_osp_order_line_rec.attribute12,
p_attribute13 => p_x_osp_order_line_rec.attribute13,
p_attribute14 => p_x_osp_order_line_rec.attribute14,
p_attribute15 => p_x_osp_order_line_rec.attribute15
);
AHL_OSP_ORDER_LINES_PKG.insert_row(
p_x_osp_order_line_id => l_osp_line_id,
p_object_version_number => 1,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.login_id,
p_osp_order_id => p_x_osp_order_line_rec.osp_order_id,
p_osp_line_number => l_osp_line_number,
p_status_code => NULL, --Derived from header status when displaying
p_po_line_type_id => p_x_osp_order_line_rec.po_line_type_id, --derived from PO Line
p_service_item_id => p_x_osp_order_line_rec.service_item_id, --derived from PO Line
p_service_item_description => p_x_osp_order_line_rec.service_item_description, --derived from PO Line
p_service_item_uom_code => p_x_osp_order_line_rec.service_item_uom_code, --derived from PO Line
p_need_by_date => p_x_osp_order_line_rec.need_by_date, --derived from PO Line
p_ship_by_date => p_x_osp_order_line_rec.ship_by_date,
p_po_line_id => p_x_osp_order_line_rec.po_line_id,
p_oe_ship_line_id => NULL,
p_oe_return_line_id => NULL,
p_workorder_id => p_x_osp_order_line_rec.workorder_id,
p_operation_id => NULL,
p_quantity => p_x_osp_order_line_rec.quantity, --derived from PO Line
p_exchange_instance_id => NULL,
p_inventory_item_id => p_x_osp_order_line_rec.inventory_item_id,
p_inventory_org_id => p_x_osp_order_line_rec.inventory_org_id,
p_sub_inventory => p_x_osp_order_line_rec.sub_inventory,
p_lot_number => p_x_osp_order_line_rec.lot_number,
p_serial_number => p_x_osp_order_line_rec.serial_number,
p_inventory_item_uom => p_x_osp_order_line_rec.inventory_item_uom,
p_inventory_item_quantity => p_x_osp_order_line_rec.inventory_item_quantity,
p_po_req_line_id => NULL,
p_attribute_category => p_x_osp_order_line_rec.attribute_category,
p_attribute1 => p_x_osp_order_line_rec.attribute1,
p_attribute2 => p_x_osp_order_line_rec.attribute2,
p_attribute3 => p_x_osp_order_line_rec.attribute3,
p_attribute4 => p_x_osp_order_line_rec.attribute4,
p_attribute5 => p_x_osp_order_line_rec.attribute5,
p_attribute6 => p_x_osp_order_line_rec.attribute6,
p_attribute7 => p_x_osp_order_line_rec.attribute7,
p_attribute8 => p_x_osp_order_line_rec.attribute8,
p_attribute9 => p_x_osp_order_line_rec.attribute9,
p_attribute10 => p_x_osp_order_line_rec.attribute10,
p_attribute11 => p_x_osp_order_line_rec.attribute11,
p_attribute12 => p_x_osp_order_line_rec.attribute12,
p_attribute13 => p_x_osp_order_line_rec.attribute13,
p_attribute14 => p_x_osp_order_line_rec.attribute14,
p_attribute15 => p_x_osp_order_line_rec.attribute15
);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_LOG_PREFIX || '.create_osp_order_line', 'After insert operation:');
SELECT 'X'
FROM ahl_osp_order_lines
WHERE inventory_item_id = c_inventory_item_id
AND inventory_org_id = c_inventory_org_id
AND serial_number = c_serial_number
AND osp_order_id = c_osp_order_id
AND (oe_ship_line_id IS NOT NULL OR oe_return_line_id IS NOT NULL);
SELECT oe_header_id INTO l_oe_header_id
FROM ahl_osp_orders_b
WHERE osp_order_id = l_osp_order_id;
SELECT organization_id,
inventory_item_id,
concatenated_segments,
primary_uom_code,
serial_number_control_code,
lot_control_code,
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 'X'
FROM mtl_secondary_inventories
WHERE organization_id = c_organization_id
AND secondary_inventory_name = c_sub_inventory;
SELECT 'X'
FROM mtl_serial_numbers
WHERE inventory_item_id = c_inv_item_id
AND serial_number = c_serial_number;
SELECT 'X'
FROM mtl_lot_numbers
WHERE inventory_item_id = c_inv_item_id
AND organization_id = c_org_id
AND lot_number = c_lot_number;
SELECT ahl_osp_queries_pvt.get_onhand_quantity(c_inv_org_id, c_subinv, c_inv_item_id, c_lot_number) onhand_quantity, primary_uom_code uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = c_inv_item_id
AND organization_id = c_inv_org_id;
SELECT 'X'
FROM ahl_osp_order_lines
WHERE osp_order_id = c_osp_order_id
AND ((service_item_id = c_service_item_id) OR
(service_item_id IS NULL AND c_service_item_id IS NULL))
AND inventory_item_id = c_inv_item_id
AND inventory_org_id = c_inv_org_id
AND ((lot_number IS NULL AND c_lot_number IS NULL) OR (lot_number = c_lot_number))
AND ((serial_number IS NULL AND c_serial_number IS NULL) OR (serial_number = c_serial_number));
SELECT 'X'
FROM ahl_osp_order_lines
WHERE inventory_item_id = c_inv_item_id
AND osp_order_id = c_osp_order_id;
SELECT 'X'
FROM ahl_osp_order_lines ospl, ahl_osp_orders_b osp
WHERE ospl.osp_order_id = osp.osp_order_id
AND osp.status_code <> 'CLOSED'
AND ospl.status_code is null
AND ospl.inventory_item_id = c_inventory_item_id
AND ospl.inventory_org_id = c_inventory_org_id
AND NVL (ospl.serial_number, 'XXX') = NVL (c_serial_number, 'XXX')
AND osp.osp_order_id <> c_osp_order_id;
SELECT 'X'
FROM ahl_osp_order_lines ospl, ahl_osp_orders_b osp, oe_order_lines_all oelship
WHERE ospl.osp_order_id = osp.osp_order_id
AND osp.status_code <> 'CLOSED'
AND oelship.LINE_ID = ospl.OE_SHIP_LINE_ID
--The order line should not be closed and should not be cancelled to be considered in the uniqueness check
AND (nvl(oelship.cancelled_flag, 'N') <> 'Y' OR nvl(oelship.flow_status_code, 'XXX') <> 'CANCELLED')
AND (oelship.open_flag <> 'N' OR nvl(oelship.flow_status_code, 'XXX') <> 'CLOSED')
AND ospl.inventory_item_id = c_inventory_item_id
AND ospl.inventory_org_id = c_inventory_org_id
AND NVL (ospl.serial_number, 'XXX') = NVL (c_serial_number, 'XXX')
AND osp.osp_order_id <> c_osp_order_id;
SELECT vts.inventory_item_id,
vst.organization_id,
csii.lot_number,
csii.serial_number,
csii.quantity,
csii.unit_of_measure item_instance_uom,
arb.service_item_id,
mtls.description service_item_description,
mtls.primary_uom_code service_item_uom
FROM ahl_workorders wo,
ahl_visits_b vst,
ahl_visit_tasks_b vts,
csi_item_instances csii,
mtl_system_items_kfv mtls,
ahl_routes_b arb
WHERE workorder_id = c_workorder_id
AND wo.visit_task_id = vts.visit_task_id
AND vst.visit_id = vts.visit_id
AND wo.route_id = arb.route_id(+)
AND arb.service_item_org_id = mtls.organization_id(+)
AND arb.service_item_id = mtls.inventory_item_id(+)
AND vts.instance_id = csii.instance_id;
SELECT pol.line_num,
pol.item_id,
pol.item_description,
pol.line_type_id,
uom.uom_code,
pol.quantity,
(select min(need_by_date)
from po_line_locations_all
where po_line_id = pol.po_line_id
and po_header_id = pol.po_header_id) need_by_date
FROM po_lines_all pol,
mtl_units_of_measure_vl uom
WHERE pol.po_line_id = c_po_line_id
AND uom.unit_of_measure = pol.unit_meas_lookup_code;
SELECT osp_order_id INTO g_dummy_num
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_line_rec.osp_order_id
AND status_code = G_OSP_PO_CREATED_STATUS
-- Added by jaramana on January 10, 2008 for the Bug 5358438/5967633/5417460
--Pos are not applying to the other order types.
AND order_type_code IN (G_OSP_ORDER_TYPE_SERVICE, G_OSP_ORDER_TYPE_EXCHANGE)
-- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
--If we are creating an osp order line based on PO line that is created from the Purchasing forms, then
--the work order selected for the order Line, should not be associated with any open order lines
--Note that instead of status_code not in, we could have use status_code is null as well.
AND not exists (select 1
from ahl_osp_order_lines
where workorder_id =p_x_osp_order_line_rec.workorder_id
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));
SELECT osp_order_id INTO g_dummy_num
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_line_rec.osp_order_id
-- jaramana modified on January 10, 2008 for the Requisition ER 6034236 (Added G_OSP_REQ_SUB_FAILED_STATUS)
AND status_code IN (G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_REQ_SUB_FAILED_STATUS);
PROCEDURE update_osp_order_line(p_x_osp_order_line_rec IN OUT NOCOPY osp_order_line_rec_type)
IS
l_osp_line_id NUMBER;
SELECT
EXCHANGE_INSTANCE_ID
FROM AHL_OSP_ORDER_LINES
WHERE OSP_ORDER_LINE_ID=c_osp_order_line_id;
L_DEBUG_KEY VARCHAR2(150) := G_LOG_PREFIX || '.update_osp_order_line';
validate_order_line_update(p_x_osp_order_line_rec);
(l_exchange_instance_id is not null AND p_x_osp_order_line_rec.exchange_instance_id is null) OR --exchange instance deleted
(l_exchange_instance_id is not null AND l_exchange_instance_id <> p_x_osp_order_line_rec.exchange_instance_id)) AND--exchange instance modified
(p_x_osp_order_line_rec.oe_return_line_id is not null)) THEN --we can't get to the transaction tables without the return_line_id
--Delete IB Transactions for these cases
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before calling Delete_IB_Transaction ' );
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 => 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 => p_x_osp_order_line_rec.oe_return_line_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After calling Delete_IB_Transaction: Return status' || l_return_status );
AHL_OSP_ORDER_LINES_PKG.update_row(
p_osp_order_line_id => p_x_osp_order_line_rec.osp_order_line_id,
p_object_version_number => p_x_osp_order_line_rec.object_version_number +1,
p_osp_order_id => p_x_osp_order_line_rec.osp_order_id,
p_osp_line_number => p_x_osp_order_line_rec.osp_line_number,
p_status_code => p_x_osp_order_line_rec.status_code,
p_po_line_type_id => p_x_osp_order_line_rec.po_line_type_id,
p_service_item_id => p_x_osp_order_line_rec.service_item_id,
p_service_item_description => p_x_osp_order_line_rec.service_item_description,
p_service_item_uom_code => p_x_osp_order_line_rec.service_item_uom_code,
p_need_by_date => TRUNC(p_x_osp_order_line_rec.need_by_date),
p_ship_by_date => TRUNC(p_x_osp_order_line_rec.ship_by_date),
p_po_line_id => p_x_osp_order_line_rec.po_line_id,
p_oe_ship_line_id => p_x_osp_order_line_rec.oe_ship_line_id,
p_oe_return_line_id => p_x_osp_order_line_rec.oe_return_line_id,
p_workorder_id => p_x_osp_order_line_rec.workorder_id,
p_operation_id => p_x_osp_order_line_rec.operation_id,
p_quantity => p_x_osp_order_line_rec.quantity,
p_exchange_instance_id => p_x_osp_order_line_rec.exchange_instance_id,
p_inventory_item_id => p_x_osp_order_line_rec.inventory_item_id,
p_inventory_org_id => p_x_osp_order_line_rec.inventory_org_id,
p_inventory_item_uom => p_x_osp_order_line_rec.inventory_item_uom,
p_inventory_item_quantity => p_x_osp_order_line_rec.inventory_item_quantity,
p_sub_inventory => p_x_osp_order_line_rec.sub_inventory,
p_lot_number => p_x_osp_order_line_rec.lot_number,
p_serial_number => p_x_osp_order_line_rec.serial_number,
-- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
p_po_req_line_id => p_x_osp_order_line_rec.po_req_line_id,
-- jaramana End
p_attribute_category => p_x_osp_order_line_rec.attribute_category,
p_attribute1 => p_x_osp_order_line_rec.attribute1,
p_attribute2 => p_x_osp_order_line_rec.attribute2,
p_attribute3 => p_x_osp_order_line_rec.attribute3,
p_attribute4 => p_x_osp_order_line_rec.attribute4,
p_attribute5 => p_x_osp_order_line_rec.attribute5,
p_attribute6 => p_x_osp_order_line_rec.attribute6,
p_attribute7 => p_x_osp_order_line_rec.attribute7,
p_attribute8 => p_x_osp_order_line_rec.attribute8,
p_attribute9 => p_x_osp_order_line_rec.attribute9,
p_attribute10 => p_x_osp_order_line_rec.attribute10,
p_attribute11 => p_x_osp_order_line_rec.attribute11,
p_attribute12 => p_x_osp_order_line_rec.attribute12,
p_attribute13 => p_x_osp_order_line_rec.attribute13,
p_attribute14 => p_x_osp_order_line_rec.attribute14,
p_attribute15 => p_x_osp_order_line_rec.attribute15,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => FND_GLOBAL.login_id
);
END update_osp_order_line;
PROCEDURE validate_order_line_update(p_x_osp_order_line_rec IN OUT NOCOPY osp_order_line_rec_type)
IS
l_osp_line_id NUMBER;
l_desc_update_flag VARCHAR2(1);
SELECT *
FROM ahl_osp_order_lines
WHERE osp_order_line_id = p_x_osp_order_line_rec.osp_order_line_id
AND object_version_number= p_x_osp_order_line_rec.object_version_number;
SELECT 'X'
FROM ahl_osp_order_lines
WHERE osp_order_id = c_osp_order_id
AND ((service_item_id = c_service_item_id) OR
(service_item_id IS NULL AND c_service_item_id IS NULL))
AND inventory_item_id = c_inv_item_id
AND inventory_org_id = c_inv_org_id
AND ((lot_number IS NULL AND c_lot_number IS NULL) OR (lot_number = c_lot_number))
AND ((serial_number IS NULL AND c_serial_number IS NULL) OR (serial_number = c_serial_number));
SELECT 'X'
FROM ahl_osp_order_lines
WHERE inventory_item_id = c_inv_item_id
AND osp_order_id = c_osp_order_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.validate_order_line_update';
SELECT status_code INTO l_header_status_code
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_line_rec.osp_order_id;
SELECT description, allow_item_desc_update_flag, concatenated_segments INTO l_item_description, l_desc_update_flag, l_svc_item_number
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_x_osp_order_line_rec.service_item_id
AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
IF(l_desc_update_flag <> 'Y' AND
((p_x_osp_order_line_rec.service_item_description <> l_item_description) OR
(p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_item_description IS NULL) OR
(p_x_osp_order_line_rec.service_item_description IS NULL AND l_item_description IS NOT NULL))) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
SELECT concatenated_segments INTO l_inv_item_number
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_x_osp_order_line_rec.inventory_item_id
AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_LOG_PREFIX || 'validate_order_line_update',
'Item description : BEFORE : '|| l_item_description);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_LOG_PREFIX || 'validate_order_line_update',
'Item description : AFTER : '|| l_item_description);
(NVL(p_x_osp_order_line_rec.status_code, 'ENTERED') NOT IN (G_OL_PO_CANCELLED_STATUS, G_OL_PO_DELETED_STATUS,G_OL_REQ_CANCELLED_STATUS, G_OL_REQ_DELETED_STATUS))) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NEED_DT');
SELECT status_code INTO l_header_status_code
FROM ahl_osp_orders_b
WHERE osp_order_id = p_x_osp_order_line_rec.osp_order_id;
SELECT comms_nl_trackable_flag INTO l_trackable_flag
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_x_osp_order_line_rec.inventory_item_id
AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
END validate_order_line_update;
SELECT IV.vendor_certification_id,
IV.rank
FROM ahl_inv_service_item_rels SI,
ahl_item_vendor_rels IV,
ahl_vendor_certifications_v VC
WHERE SI.inv_service_item_rel_id = IV.inv_service_item_rel_id
AND IV.vendor_certification_id = VC.vendor_certification_id
AND SI.inv_item_id = c_inv_item_id
AND SI.inv_org_id = c_inv_org_id
AND SI.service_item_id = c_service_item_id
AND trunc(SI.active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(SI.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(IV.active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(IV.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(VC.active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(VC.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT vendor_id,
vendor_site_id,
vendor_contact_id
FROM ahl_vendor_certifications_v
WHERE vendor_certification_id = c_vendor_cert_id
AND trunc(active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT INVENTORY_ORG_ID, INVENTORY_ITEM_ID, SERVICE_ITEM_ID FROM ahl_osp_order_lines
WHERE osp_order_id = p_osp_order_id AND
service_item_id IS NOT NULL;
SELECT 1
FROM ahl_inv_service_item_rels isr,
ahl_item_vendor_rels ivr
WHERE isr.inv_item_id = c_inventory_item_id AND
isr.service_item_id = c_service_item_id AND
isr.inv_service_item_rel_id = ivr.inv_service_item_rel_id;
SELECT 'X'
FROM po_vendors_view
WHERE vendor_id = p_vendor_id
AND enabled_flag = G_YES_FLAG
AND TRUNC(NVL(vendor_start_date_active, SYSDATE - 1)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(vendor_end_date_active, SYSDATE + 1)) > TRUNC(SYSDATE);
SELECT 'X'
FROM ahl_vendor_certifications_v
WHERE vendor_id = p_vendor_id
AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
AND TRUNC(nvl(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT 'X'
FROM po_vendor_sites
WHERE vendor_site_id = p_vendor_site_id
AND vendor_id = p_vendor_id
AND TRUNC(NVL(inactive_date, SYSDATE + 1)) > TRUNC(SYSDATE)
AND purchasing_site_flag = G_YES_FLAG
AND NVL(RFQ_ONLY_SITE_FLAG, G_NO_FLAG) =G_NO_FLAG ;
SELECT 'X'
FROM ahl_vendor_certifications_v
WHERE vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id
AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
AND TRUNC(nvl(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT 'X'
FROM po_vendor_contacts
WHERE vendor_site_id = p_vendor_site_id
AND vendor_contact_id = p_vendor_contact_id
AND TRUNC(NVL(inactive_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT 'X'
FROM ahl_vendor_certifications
WHERE vendor_contact_id = p_vendor_contact_id
AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT osp_order_id,
osp_line_number,
status_code, --status is not defined in the record because user can't directly change the status
po_line_type_id, --no po_line_type here because it is not changeable once it is created(from a profile option)
service_item_id,
service_item_number,
service_item_description,
service_item_uom_code,
quantity,
need_by_date,
ship_by_date,
po_line_id,
oe_ship_line_id,
oe_return_line_id,
workorder_id,
job_number,
operation_id, --this attribute should be deleted from the table, so ignore it
exchange_instance_id,
exchange_instance_number,
inventory_item_id,
inventory_org_id,
item_number,
inventory_item_uom,
inventory_item_quantity,
sub_inventory,
lot_number,
serial_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM ahl_osp_order_lines_v
WHERE osp_order_line_id = p_x_osp_order_line_rec.osp_order_line_id
AND object_version_number= p_x_osp_order_line_rec.object_version_number;
SELECT ospl.osp_order_id,
ospl.osp_line_number,
ospl.status_code, --status is not defined in the record because user can't directly change the status
ospl.po_line_type_id, --no po_line_type here because it is not changeable once it is created(from a profile option)
ospl.service_item_id,
(select mtlsvc.concatenated_segments
from mtl_system_items_kfv mtlsvc
where mtlsvc.inventory_item_id = ospl.service_item_id
and mtlsvc.organization_id = decode(ospl.workorder_id, null, ospl.inventory_org_id, vst.organization_id)
)service_item_number,
ospl.service_item_description,
--modified by mpothuku on 14-mar-2008 to fix the Bug 6885513
/*
decode(ospl.po_line_id, null, ospl.service_item_uom_code, mtluom.uom_code )service_item_uom_code,
decode(ospl.po_line_id, null, ospl.quantity, pl.quantity )quantity,
*/
ospl.service_item_uom_code,
ospl.quantity,
--mpothuku End
ospl.need_by_date,
ospl.ship_by_date,
ospl.po_line_id,
ospl.oe_ship_line_id,
ospl.oe_return_line_id,
ospl.workorder_id,
wo.workorder_name job_number,
ospl.operation_id, --this attribute should be deleted from the table, so ignore it
ospl.exchange_instance_id,
csiex.instance_number exchange_instance_number,
decode(ospl.workorder_id, null, ospl.inventory_item_id, vts.inventory_item_id)inventory_item_id,
--Fix for the regression issue by mpothuku on 28th August 2006, changed the item_id to org_id in decode below
decode(ospl.workorder_id, null, ospl.inventory_org_id, vst.organization_id) inventory_org_id,
(select mtli.concatenated_segments
from mtl_system_items_kfv mtli
where mtli.inventory_item_id = ospl.inventory_item_id
and mtli.organization_id = decode(ospl.workorder_id, null, ospl.inventory_org_id, vst.organization_id)
)item_number,
decode(ospl.workorder_id, null, ospl.inventory_item_uom, csiwo.unit_of_measure)inventory_item_uom,
decode(ospl.workorder_id, null, ospl.inventory_item_quantity, csiwo.quantity)inventory_item_quantity,
decode(ospl.workorder_id, null, ospl.sub_inventory, null)sub_inventory,
decode(ospl.workorder_id, null, ospl.lot_number, csiwo.lot_number)lot_number,
decode(ospl.workorder_id, null, ospl.serial_number, csiwo.serial_number)serial_number,
-- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
po_req_line_id,
-- jaramana End
ospl.attribute_category,
ospl.attribute1,
ospl.attribute2,
ospl.attribute3,
ospl.attribute4,
ospl.attribute5,
ospl.attribute6,
ospl.attribute7,
ospl.attribute8,
ospl.attribute9,
ospl.attribute10,
ospl.attribute11,
ospl.attribute12,
ospl.attribute13,
ospl.attribute14,
ospl.attribute15
FROM ahl_osp_order_lines ospl,
ahl_workorders wo,
ahl_visits_b vst,
ahl_visit_tasks_b vts,
csi_item_instances csiwo,
csi_item_instances csiex,
--po_lines_all pl,
mtl_units_of_measure_tl mtluom
WHERE ospl.osp_order_line_id = p_x_osp_order_line_rec.osp_order_line_id
AND ospl.object_version_number= p_x_osp_order_line_rec.object_version_number
AND ospl.workorder_id = wo.workorder_id(+)
AND wo.visit_task_id = vts.visit_task_id(+)
AND vts.visit_id = vst.visit_id(+)
AND vts.instance_id = csiwo.instance_id(+)
AND NVL (ospl.status_code, 'ENTERED') <> 'PO_DELETED'
--modified by mpothuku on 14-mar-2008 to fix the Bug 6885513
/*
AND ospl.po_line_id = pl.po_line_id(+)
AND pl.unit_meas_lookup_code = mtluom.unit_of_measure(+)
AND mtluom.language(+) = USERENV('LANG')
*/
--mpothuku End
AND csiex.instance_id(+) = ospl.exchange_instance_id;
IF(p_x_osp_order_line_rec.operation_flag = G_OP_UPDATE) THEN
OPEN osp_order_lines_csr;
SELECT workorder_id
FROM ahl_workorders_osp_v
WHERE job_number = p_job_number;
SELECT workorder_id from ahl_workorders
WHERE workorder_name = p_job_number;
SELECT MTL.inventory_item_id
FROM mtl_system_items_kfv MTL
WHERE MTL.concatenated_segments = p_service_item_number
AND MTL.organization_id = p_inventory_org_id;
SELECT line_type_id
FROM po_line_types
WHERE line_type = p_po_line_type
AND order_type_lookup_code = 'QUANTITY'
AND NVL(outside_operation_flag, G_NO_FLAG) = G_NO_FLAG;
SELECT osp_order_line_id
FROM ahl_osp_order_lines
WHERE osp_order_id = p_osp_order_id
and osp_line_number = p_osp_line_number;
SELECT osp_order_line_id
FROM ahl_osp_order_lines
WHERE po_line_id = p_po_line_id;
SELECT instance_id
FROM csi_item_instances
WHERE instance_number = p_exchange_instance_number;
SELECT MTL.inventory_item_id
FROM mtl_system_items_kfv MTL
WHERE MTL.concatenated_segments = p_item_number
AND MTL.organization_id = p_inventory_org_id;
SELECT 'X'
FROM mtl_system_items_kfv MTL
WHERE MTL.inventory_item_id = p_service_item_id
AND MTL.enabled_flag = G_YES_FLAG
AND MTL.inventory_item_flag = G_NO_FLAG
AND MTL.stock_enabled_flag = G_NO_FLAG
AND NVL(MTL.start_date_active, SYSDATE - 1) <= SYSDATE
AND NVL(MTL.end_date_active, SYSDATE + 1) > SYSDATE
AND MTL.purchasing_enabled_flag = G_YES_FLAG
AND NVL(MTL.outside_operation_flag, G_NO_FLAG) = G_NO_FLAG
AND MTL.organization_id = p_organization_id;
SELECT A.inv_service_item_rel_id
FROM ahl_inv_service_item_rels A,
ahl_item_vendor_rels_v B
WHERE A.inv_service_item_rel_id = B.inv_service_item_rel_id
AND A.inv_item_id = p_osp_order_line_rec.inventory_item_id
AND A.inv_org_id = p_osp_order_line_rec.inventory_org_id
AND A.service_item_id = p_osp_order_line_rec.service_item_id
AND TRUNC(A.active_start_date) <= TRUNC(SYSDATE)
AND TRUNC(NVL(A.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
AND TRUNC(B.active_start_date) <= TRUNC(SYSDATE)
AND TRUNC(NVL(B.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
AND B.vendor_id = c_vendor_id
AND B.vendor_site_id = c_vendor_site_id;
/*(From Jay said on 06/07/2005) service_item_id is not mandatory even for update
IF p_osp_order_line_rec.service_item_id IS NULL THEN
FND_MESSAGE.set_name('AHL', 'AHL_OSP_SERVICE_ITEM_NULL');
SELECT decode(osp.po_header_id, null, osp.vendor_id, po.vendor_id) vendor_id,
decode(osp.po_header_id, null, osp.vendor_site_id, po.vendor_site_id) vendor_site_id --, vendor_contact_id
INTO l_vendor_id, l_vendor_site_id --, l_vendor_contact_id
FROM ahl_osp_orders_b osp,
po_headers_all po
WHERE osp.osp_order_id = p_osp_order_line_rec.osp_order_id
AND osp.po_header_id = po.po_header_id(+);
SELECT vendor_id,
vendor_site_id
INTO l_vendor_id, l_vendor_site_id --, l_vendor_contact_id
FROM ahl_osp_orders_b osp
WHERE osp.osp_order_id = p_osp_order_line_rec.osp_order_id;
SELECT 'X' INTO g_dummy_char
FROM ahl_vendor_certifications_v
WHERE vendor_id = l_vendor_id
AND vendor_site_id = l_vendor_site_id
AND trunc(active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT order_type_code INTO l_old_type_code
FROM ahl_osp_orders_b
WHERE osp_order_id = p_osp_order_rec.osp_order_id;
UPDATE ahl_osp_order_lines
SET exchange_instance_id = NULL,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number+1
WHERE osp_order_id = p_osp_order_rec.osp_order_id;
SELECT 'X'
FROM AHL_WARRANTY_ENTITLEMENTS AWSE,
AHL_WARRANTY_CONTRACTS_B AWCB,
AHL_OSP_ORDERS_B AOOB,
AHL_OSP_ORDER_LINES AOOL
WHERE AOOB.OSP_ORDER_ID = AOOL.OSP_ORDER_ID
AND AOOL.OSP_ORDER_LINE_ID = AWSE.OSP_ORDER_LINE_ID
AND AWCB.WARRANTY_CONTRACT_ID = AWSE.WARRANTY_CONTRACT_ID
AND AOOB.STATUS_CODE = 'SUBMITTED'
AND AWSE.ENTITLEMENT_STATUS_CODE = 'APPROVED'
AND AWCB.OSP_CLAIM_FLAG = 'Y'
AND AOOL.OSP_ORDER_LINE_ID = c_osp_order_line_id;
SELECT warranty_contract_id
FROM ahl_warranty_contracts_b
WHERE item_instance_id = c_instance_id
AND contract_status_code = 'ACTIVE';
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 ASWE.WARRANTY_ENTITLEMENT_ID, ASWE.OBJECT_VERSION_NUMBER
FROM AHL_WARRANTY_ENTITLEMENTS ASWE
WHERE ASWE.VISIT_TASK_ID = (SELECT wo.visit_task_id
FROM ahl_workorders wo
WHERE wo.workorder_id = c_workorder_id);
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
PROCEDURE Get_Warranty_Rec_for_Update(
p_x_osp_order_line_rec IN osp_order_line_rec_type,
x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type,
x_call_process_entl OUT NOCOPY VARCHAR2
) IS
-- Standard variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Warranty_Rec_for_Update';
SELECT AWCB.WARRANTY_CONTRACT_ID, AWSE.ENTITLEMENT_STATUS_CODE, AWSE.OBJECT_VERSION_NUMBER
FROM AHL_WARRANTY_ENTITLEMENTS AWSE, AHL_WARRANTY_CONTRACTS_B AWCB
WHERE AWSE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID(+)
AND AWSE.OSP_ORDER_LINE_ID = c_osp_order_line_id;
SELECT WARRANTY_CONTRACT_ID
FROM AHL_WARRANTY_CONTRACTS_B
WHERE CONTRACT_NUMBER = c_contract_num
AND CONTRACT_STATUS_CODE = 'ACTIVE';
IF(p_x_osp_order_line_rec.warranty_entitlement_id is not null) THEN -- Entitlements Record Already Exists UPDATE
l_entitlement_rec.warranty_entitlement_id := p_x_osp_order_line_rec.warranty_entitlement_id;
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
END Get_Warranty_Rec_for_Update;
PROCEDURE Get_Entl_rec_for_Delete(
p_osp_order_id IN NUMBER := null,
p_osp_order_line_id IN NUMBER := null,
x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
) IS
-- Standard variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Entl_rec_for_Delete';
SELECT AWSE.WARRANTY_ENTITLEMENT_ID, AWSE.VISIT_TASK_ID, AWSE.OBJECT_VERSION_NUMBER
FROM AHL_WARRANTY_ENTITLEMENTS AWSE
WHERE AWSE.OSP_ORDER_LINE_ID = c_osp_order_line_id;
SELECT OSP_ORDER_LINE_ID
FROM AHL_OSP_ORDER_LINES
WHERE OSP_ORDER_ID = c_osp_order_id;
IF(p_osp_order_id IS NULL) THEN -- if the order id passed is null then its line delete
OPEN c_get_entl_details(p_osp_order_line_id);
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_DELETE;
ELSE -- Osp Order delete
l_count := 0;
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_DELETE;
END Get_Entl_rec_for_Delete;
SELECT osp_order_line_id, object_version_number, last_update_date, last_updated_by , last_update_login,
osp_order_id, osp_line_number, status_code , po_line_type_id, service_item_id, service_item_description , service_item_uom_code,
need_by_date, ship_by_date, po_line_id, oe_ship_line_id , oe_return_line_id , workorder_id, operation_id,
quantity, exchange_instance_id, attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
FROM ahl_osp_order_lines
WHERE osp_order_id = p_osp_order_id;
SELECT object_version_number FROM ahl_osp_order_lines
WHERE osp_order_line_id = p_osp_order_line_id;
l_osp_order_lines_rec.last_update_date ,
l_osp_order_lines_rec.last_updated_by ,
l_osp_order_lines_rec.last_update_login ,
l_osp_order_lines_rec.osp_order_id ,
l_osp_order_lines_rec.osp_line_number ,
l_osp_order_lines_rec.status_code ,
l_osp_order_lines_rec.po_line_type_id ,
l_osp_order_lines_rec.service_item_id ,
l_osp_order_lines_rec.service_item_description ,
l_osp_order_lines_rec.service_item_uom_code ,
l_osp_order_lines_rec.need_by_date ,
l_osp_order_lines_rec.ship_by_date ,
l_osp_order_lines_rec.po_line_id ,
l_osp_order_lines_rec.oe_ship_line_id ,
l_osp_order_lines_rec.oe_return_line_id ,
l_osp_order_lines_rec.workorder_id ,
l_osp_order_lines_rec.operation_id ,
l_osp_order_lines_rec.quantity ,
l_osp_order_lines_rec.exchange_instance_id ,
l_osp_order_lines_rec.attribute_category ,
l_osp_order_lines_rec.attribute1 ,
l_osp_order_lines_rec.attribute2 ,
l_osp_order_lines_rec.attribute3 ,
l_osp_order_lines_rec.attribute4 ,
l_osp_order_lines_rec.attribute5 ,
l_osp_order_lines_rec.attribute6 ,
l_osp_order_lines_rec.attribute7 ,
l_osp_order_lines_rec.attribute8 ,
l_osp_order_lines_rec.attribute9 ,
l_osp_order_lines_rec.attribute10 ,
l_osp_order_lines_rec.attribute11 ,
l_osp_order_lines_rec.attribute12 ,
l_osp_order_lines_rec.attribute13 ,
l_osp_order_lines_rec.attribute14 ,
l_osp_order_lines_rec.attribute15 ;
AHL_OSP_ORDER_LINES_PKG.update_row(
p_osp_order_line_id => l_osp_order_lines_rec.osp_order_line_id,
p_object_version_number => l_osp_order_lines_rec.object_version_number + 1,
p_last_update_date => l_osp_order_lines_rec.last_update_date,
p_last_updated_by => l_osp_order_lines_rec.last_updated_by,
p_last_update_login => l_osp_order_lines_rec.last_update_login,
p_osp_order_id => l_osp_order_lines_rec.osp_order_id,
p_osp_line_number => l_osp_order_lines_rec.osp_line_number,
p_status_code => l_osp_order_lines_rec.status_code,
p_po_line_type_id => l_osp_order_lines_rec.po_line_type_id,
p_service_item_id => l_osp_order_lines_rec.service_item_id,
p_service_item_description => l_osp_order_lines_rec.service_item_description,
p_service_item_uom_code => l_osp_order_lines_rec.service_item_uom_code,
p_need_by_date => l_osp_order_lines_rec.need_by_date,
p_ship_by_date => l_osp_order_lines_rec.ship_by_date,
p_po_line_id => l_osp_order_lines_rec.po_line_id,
p_oe_ship_line_id => l_osp_order_lines_rec.oe_ship_line_id,
p_oe_return_line_id => l_osp_order_lines_rec.oe_return_line_id,
p_workorder_id => l_osp_order_lines_rec.workorder_id,
p_operation_id => l_osp_order_lines_rec.operation_id,
p_quantity => l_osp_order_lines_rec.quantity,
p_exchange_instance_id => NULL,
p_inventory_item_id => l_osp_order_lines_rec.inventory_item_id,
p_inventory_org_id => l_osp_order_lines_rec.inventory_org_id,
p_inventory_item_uom => l_osp_order_lines_rec.inventory_item_uom,
p_inventory_item_quantity => l_osp_order_lines_rec.inventory_item_quantity,
p_sub_inventory => l_osp_order_lines_rec.sub_inventory,
p_lot_number => l_osp_order_lines_rec.lot_number,
p_serial_number => l_osp_order_lines_rec.serial_number,
p_attribute_category => l_osp_order_lines_rec.attribute_category,
p_attribute1 => l_osp_order_lines_rec.attribute1,
p_attribute2 => l_osp_order_lines_rec.attribute2,
p_attribute3 => l_osp_order_lines_rec.attribute3,
p_attribute4 => l_osp_order_lines_rec.attribute4,
p_attribute5 => l_osp_order_lines_rec.attribute5,
p_attribute6 => l_osp_order_lines_rec.attribute6,
p_attribute7 => l_osp_order_lines_rec.attribute7,
p_attribute8 => l_osp_order_lines_rec.attribute8,
p_attribute9 => l_osp_order_lines_rec.attribute9,
p_attribute10 => l_osp_order_lines_rec.attribute10,
p_attribute11 => l_osp_order_lines_rec.attribute11,
p_attribute12 => l_osp_order_lines_rec.attribute12,
p_attribute13 => l_osp_order_lines_rec.attribute13,
p_attribute14 => l_osp_order_lines_rec.attribute14,
p_attribute15 => l_osp_order_lines_rec.attribute15
);