[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X' FROM BOM_DEPARTMENTS where DEPARTMENT_ID = dept_id AND DEPARTMENT_CODE = dept_code;
l_sql_string := 'SELECT DISTINCT OSP.OSP_ORDER_ID ';
l_search_criteria := l_search_criteria || and_str || ' EXISTS (SELECT OL.OSP_ORDER_LINE_ID FROM AHL_WORKORDERS_OSP_V WO, '
|| ' AHL_OSP_ORDER_LINES OL WHERE '
|| ' OL.OSP_ORDER_ID = OSP.OSP_ORDER_ID AND OL.WORKORDER_ID = WO.WORKORDER_ID ';
l_count_query := 'SELECT COUNT(*) FROM (' || l_sql_string || ')';
SELECT 'X' FROM BOM_DEPARTMENTS where DEPARTMENT_ID = dept_id AND DEPARTMENT_CODE = dept_code;
l_sql_string := 'SELECT WORKORDER_ID';
l_search_criteria := l_search_criteria || ' AND NOT EXISTS (SELECT OL1.WORKORDER_ID FROM AHL_OSP_ORDER_LINES OL1 WHERE OL1.WORKORDER_ID = WO.WORKORDER_ID AND OL1.STATUS_CODE IS NULL) ';
l_count_query := 'SELECT COUNT(*) FROM (' ||l_sql_string || ')';
SELECT FND.MEANING FROM FND_LOOKUP_VALUES_VL FND
WHERE FND.LOOKUP_TYPE = 'AHL_OSP_STATUS_TYPE'
AND FND.LOOKUP_CODE = 'ENTERED';
SELECT buyer_id, full_name FROM PO_AGENTS_NAME_V, fnd_user fnd
where buyer_id = fnd.employee_id and fnd.user_id = fnd_global.user_id ;
l_header_queries := 'SELECT OSP.OSP_ORDER_ID ';
l_line_queries := 'SELECT OSP_ORDER_LINE_ID ';
l_line_queries := 'SELECT WO.WORKORDER_ID ';
SELECT inventory_item_id,
organization_id,
service_item_id
FROM ahl_workorders_osp_v
WHERE workorder_id = p_work_order_id;
SELECT vts.inventory_item_id,
vst.organization_id,
arb.service_item_id
FROM ahl_workorders wo,
ahl_visits_b vst,
ahl_visit_tasks_b vts,
ahl_routes_b arb
WHERE workorder_id = p_work_order_id
AND wo.visit_task_id = vts.visit_task_id
AND vts.visit_id = vst.visit_id
AND wo.route_id = arb.route_id(+);
SELECT IV.vendor_certification_id,
IV.rank
FROM ahl_inv_service_item_rels SI,
ahl_item_vendor_rels IV
WHERE SI.inv_service_item_rel_id = IV.inv_service_item_rel_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(IV.active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(IV.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(SI.active_start_date) <= trunc(SYSDATE)
AND trunc(nvl(SI.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
ORDER BY IV.rank;
SELECT vendor_name
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 nvl(sum(transaction_quantity),0) into l_apparent_quantity from mtl_onhand_quantities where
organization_id = p_org_id and
inventory_item_id = p_inventory_item_id and
subinventory_code = p_subinventory_code and
--Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
(p_lot_number is null or lot_number = p_lot_number);
3. We need to retain the ospl.status_code clause as, if the order does not have shipments and PO is deleted, we consider it released */
/*
Select
nvl(sum(nvl(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty
(ospl.inventory_item_id,ospl.inventory_item_uom,ospl.inventory_item_quantity),0)
),0) into l_quant_withoutship
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
--Added by mpothuku on 23-Aug-06 to exclude the quantity involved in PO_CANCELLED or PO_DELETED Lines for Bug 5252627
ospl.status_code is null and
--mpothuku End
ospl.oe_ship_line_id is null and
ospl.inventory_org_id = p_org_id and
ospl.inventory_item_id = p_inventory_item_id and
ospl.sub_inventory = p_subinventory_code and
--Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
(p_lot_number is null or ospl.lot_number = p_lot_number);
1. Need to consider the clause for the cases where OE Lines or Orders are deleted from OM forms. For such cases
we still hold the reference in AHL tables.
2. Need to consider the case where the Shipments are cancelled
3. Need to remove the ospl.status code clause as the items are not considered released till the shipments are deleted
and PO deletion no more enables the items to be released for Osp.
*/
Select
nvl(sum(nvl(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty
(ospl.inventory_item_id,ospl.inventory_item_uom,ospl.inventory_item_quantity),0)
),0) into l_quant_ship_notbooked
from ahl_osp_order_lines ospl, ahl_osp_orders_b osp,
/* Fix for the AE Bug 5673279 (Release/Holding of inventory items) */
oe_order_lines_all oel
where
ospl.osp_order_id = osp.osp_order_id and
osp.status_code <> 'CLOSED' and
/* Fix for the AE Bug 5673279 (Release/Holding of inventory items)
--Added by mpothuku on 23-Aug-06 to exclude the quantity involved in PO_CANCELLED or PO_DELETED Lines for Bug 5252627
ospl.status_code is null and
--mpothuku End
*/
ospl.oe_ship_line_id is not null and
ospl.inventory_org_id = p_org_id and
ospl.inventory_item_id = p_inventory_item_id and
ospl.sub_inventory = p_subinventory_code and
/* Fix for the AE Bug 5673279 (Release/Holding of inventory items) */
--This join ensure that if the OE ship lines are deleted from the OM forms the quantity is not reserved.
oel.line_id = ospl.OE_SHIP_LINE_ID and
--The order line should not be closed and should not be cancelled to be considered here
--mpothuku 16-Nov-06, following two checks may be redundant as, the order cannot be closed if there are no deliveries
--and unless the order is booked, it cannot be in the cancelled status
(nvl(oel.cancelled_flag, 'N') <> 'Y' OR nvl(oel.flow_status_code, 'XXX') <> 'CANCELLED') and
(oel.open_flag <> 'N' OR nvl(oel.flow_status_code, 'XXX') <> 'CLOSED') and
--Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
(p_lot_number is null or ospl.lot_number = p_lot_number) and
not exists
(select 1 from wsh_delivery_details where SOURCE_CODE = 'OE' and SOURCE_LINE_ID = OSPL.oe_ship_line_id);
Select
nvl(sum(nvl(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty
(inventory_item_id, requested_quantity_uom, requested_quantity),0)
),0) into l_quant_notshippedout
from wsh_delivery_details where
organization_id = p_org_id and
inventory_item_id = p_inventory_item_id and
subinventory = p_subinventory_code and
released_status in ('R','S','Y','C','B') and
--Added by mpothuku on 17th May, 06 to fix the Bug 5231358
(p_lot_number is null or lot_number = p_lot_number);
SELECT VENDOR_NAME
from PO_VENDORS_VIEW VEN, PO_ASL_STATUSES AST, PO_APPROVED_SUPPLIER_LIST ASL, AHL_WORKORDERS_OSP_V WO
WHERE WO.WORKORDER_ID = l_workorder_id AND
ASL.ITEM_ID = WO.SERVICE_ITEM_ID AND
(ASL.USING_ORGANIZATION_ID = l_org_id OR
(ASL.OWNING_ORGANIZATION_ID = l_org_id AND ASL.USING_ORGANIZATION_ID = -1)) AND
ASL.ASL_STATUS_ID = AST.STATUS_ID AND
AST.STATUS = 'Approved' AND
VEN.VENDOR_ID = ASL.VENDOR_ID AND
VEN.ENABLED_FLAG = 'Y' AND
NVL(VENDOR_START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE AND
NVL(VENDOR_END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;
SELECT VENDOR_NAME
from PO_VENDORS_VIEW VEN,
PO_ASL_STATUSES AST,
PO_APPROVED_SUPPLIER_LIST ASL,
AHL_WORKORDERS WO,
AHL_ROUTES_B arb
WHERE WO.WORKORDER_ID = l_workorder_id
AND arb.route_id = wo.route_id
AND ASL.ITEM_ID = arb.SERVICE_ITEM_ID
AND (ASL.USING_ORGANIZATION_ID = l_org_id
OR (ASL.OWNING_ORGANIZATION_ID = l_org_id
AND ASL.USING_ORGANIZATION_ID = -1))
AND ASL.ASL_STATUS_ID = AST.STATUS_ID
AND AST.STATUS = 'Approved'
AND VEN.VENDOR_ID = ASL.VENDOR_ID
AND VEN.ENABLED_FLAG = 'Y'
AND NVL(VENDOR_START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
AND NVL(VENDOR_END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;