SQL & PL/SQL

Hi

We are trying to build a Order Management related workbench for this I have created below query as per the client requirement.
And I have to use one package function to get Available_qty based on client defination. Package Function name is "xx_eg_calculated_onhand".

User will query this view based on Customer_id and Salesperson_id. When this view executed with these two values its taking long time.
But, if user queries with order_number it is getting executed fastly. Could somebody let me know what should I do to get this query execute fast when
user query with either customer_id or salesrep_id OR both.



CREATE OR REPLACE VIEW xxrelease_sales_orders_v (row_id,
sales_oder,
cust_po_number,
line_id,
carrier,
segment1,
customer_item_number,
description,
ship_from,
ship_to,
request_date,
order_quality,
available_qty,
shipped_quality,
uom,
customer_name,
sales_person_name,
delivery_detail_id,
cust_hold,
release_check,
line_status,
organization_code,
org_id,
order_number,
tolerance,
delivery_id,
organization_id,
salesrep_id,
party_id,
inventory_item_id,
header_id,
cust_account_id,
location_id,
cust_acct_site_id
)
AS
SELECT a.row_id,
a.Sales_Order,
a.Cust_Po_Number,
a.Line_ID,
a.Carrier,
a.segment1,
a.Customer_Item_Number,
a.description,
a.Ship_From,
a.ship_to,
a.request_date,
a.order_quality,
DECODE(SIGN(a.Available_qty),-1, 0, a.available_qty) Available_Qty,
DECODE(SIGN(LEAST(a.order_quality, a.Available_qty)), -1,0, LEAST(a.order_quality, a.Available_qty)) Shipped_Qty,
a.uom,
a.Customer_Name,
a.Sales_Person_Name,
a.delivery_detail_id,
a.cust_hold,
a.release_check,
a.Line_status,
a.organization_code,
a.org_id,
a.order_number,
a.tolerance,
a.delivery_id,
a.organization_id,
a.salesrep_id,
a.party_id,
a.inventory_item_id,
a.header_id,
a.cust_account_id,
a.location_id,
a.cust_acct_site_id
From (

SELECT ooh.ROWID row_id,
ooh.order_number||'-'||ool.line_number||'.'||ool.shipment_number Sales_Order,
ool.cust_po_number,
ool.line_id,
NVL (ool.freight_carrier_code, 'NA') carrier,
msi.segment1,
ool.ordered_item Customer_Item_Number,
msi.description,
haou.name Ship_From,
hl.city || ' ' || hl.state || ' ' || hl.postal_code ship_to,
NVL (ool.request_date, SYSDATE) request_date,
NVL (ool.ordered_quantity, 0) order_quality,
xxeg_releaseworkbench_form_pkg.xx_eg_calculated_onhand(ool.line_id) Available_qty,
ool.order_quantity_uom uom,
hp.party_name Customer_Name,
hca.account_number Customer_Number,
jrs.name Sales_Person_Name,
wdd.delivery_detail_id,
0 cust_hold,
0 release_check,
oe_line_status_pub.Get_Line_Status(ool.line_id, ool.flow_status_code) Line_status,
ool.org_id,
ooh.order_number,
NVL (ool.ship_tolerance_above, 0) tolerance,
wda.delivery_id,
ool.ship_from_org_id organization_id,
jrs.salesrep_id,
hp.party_id,
msi.inventory_item_id,
ooh.header_id,
hca.cust_account_id,
hps.location_id,
hcsu.cust_acct_site_id,
ooh.ship_to_org_id site_use_id,
hcas.party_site_id,
'0' organization_code
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
mtl_system_items_b msi,
hz_cust_accounts hca,
hz_parties hp,
jtf_rs_salesreps jrs,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl,
hr_all_organization_units haou
WHERE ooh.header_id = ool.header_id
AND ooh.header_id = wdd.source_header_id
AND ool.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND ool.inventory_item_id = msi.inventory_item_id
AND ooh.ship_from_org_id = msi.organization_id
AND ooh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND ool.salesrep_id = jrs.salesrep_id
AND ool.org_id = jrs.org_id
AND ooh.ship_to_org_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND ool.ship_from_org_id = haou.organization_id
AND wdd.released_status = 'R'
AND hcsu.site_use_code = 'SHIP_TO'
AND ool.flow_status_code = 'AWAITING_SHIPPING'
--and hca.cust_account_id = 1531 and jrs.salesrep_id = 100000060
--AND ooh.order_number = '361130'
) a


----------------------------------------------------

Function

FUNCTION xx_eg_calculated_onhand (p_so_line_id IN NUMBER) RETURN NUMBER IS
--CREATE OR REPLACE PROCEDURE xx_eg_calculated_onhand (p_so_line_id IN NUMBER) IS
-- Steps Involved to calculate the onhand availability
-- 1. Get Inventory Item ID and Warehouse from given sales order line ID
-------------------------------------------------------------
-- Local Variable Declration
-------------------------------------------------------------
l_inventory_item_id apps.oe_order_lines_all.inventory_item_id%TYPE := 0;
l_organization_id apps.oe_order_lines_all.ship_from_org_id%TYPE := 0;
l_order_quantity_uom apps.oe_order_lines_all.order_quantity_uom%TYPE := '';
l_primary_uom_code apps.mtl_system_items_b.primary_uom_code%TYPE := '';
l_onhand_reserve NUMBER(15,2) := 0;
l_hard_reservation NUMBER(15,2) := 0;
l_so_soft_reservation NUMBER(15,2) := 0;
l_wo_soft_reservation NUMBER(15,2) := 0;
l_so_released_ware_qty NUMBER(15,2) := 0;
-------------------------------------------------------------
-- Cursor declrations
-------------------------------------------------------------
-- Get inventory item, warehouse and order UOM Details
CURSOR Get_inv_and_org_details IS
SELECT ool.inventory_item_id,
ool.ship_from_org_id,
ool.order_quantity_uom,
msi.primary_uom_code
FROM apps.oe_order_lines_all ool,
apps.mtl_system_items_b msi
WHERE ool.line_id = NVL(p_so_line_id,0)
AND ool.inventory_item_id = msi.inventory_item_id
AND ool.ship_from_org_id = msi.organization_id;
-- Get Hard Reservation details for given item and warehouse
CURSOR Get_Hard_reservation(p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER) IS
SELECT SUM(reservation_quantity)
FROM apps.mtl_reservations
WHERE inventory_item_id = NVL(p_inventory_item_id,0)
AND organization_id = NVL(p_organization_id, 0)
AND demand_source_line_id <> p_so_line_id;
-- Get Soft reservation details for given item and warehouse for Sales order
CURSOR Get_so_soft_reservation (p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER) IS
SELECT SUM(mlqv.onhand_qty)
FROM xxapps.xx_eg_lot_reservations xelr,
xxapps.mtl_lot_qty_vw mlqv,
apps.oe_order_lines_all ool
WHERE xelr.lot_no = mlqv.lot_no
AND xelr.so_line_id = ool.line_id
AND mlqv.org_id = ool.ship_from_org_id
AND mlqv.item_id = ool.inventory_item_id
AND mlqv.org_id = p_organization_id
AND mlqv.item_id = p_inventory_item_id;
-- Get Soft reservation details for given item and warehouse for work orders
CURSOR Get_wo_soft_reservation (p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER) IS
SELECT SUM(mlqv.onhand_qty)
FROM xxapps.xx_eg_lot_reservations xelr,
xxapps.mtl_lot_qty_vw mlqv,
wip.wip_requirement_operations wro
WHERE xelr.lot_no = mlqv.lot_no
AND xelr.wip_entity_id = wro.wip_entity_id
AND mlqv.org_id = wro.organization_id
AND mlqv.item_id = wro.inventory_item_id
AND mlqv.org_id = p_organization_id
AND mlqv.item_id = p_inventory_item_id;
-- Get Sales order lines quantity for the shipment status 'Released to Wareshoue'
CURSOR Get_so_qty (p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER) IS
SELECT SUM(requested_quantity)
FROM apps.wsh_deliverables_v
WHERE released_status='S'
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
BEGIN
Dbms_output.put_line('Function Started with so_line_id = '||p_so_line_id);
---------------------------------------------------------
-- Get the inventory_item_id and warehouse id and
-- ordered quantity uom details
-- By opening the cursor Get_inv_and_org_details
---------------------------------------------------------
OPEN Get_inv_and_org_details;
FETCH Get_inv_and_org_details INTO l_inventory_item_id,l_organization_id,l_order_quantity_uom,l_primary_uom_code;
CLOSE Get_inv_and_org_details;
Dbms_output.put_line('Inventory item id = '||l_inventory_item_id||' and warehouse id = '||l_organization_id);
IF (l_inventory_item_id = 0 OR l_inventory_item_id IS NULL) OR
(l_organization_id = 0 OR l_organization_id IS NULL) THEN
dbms_output.put_line('No inventory/wareshouse details available for given sales order line');
RETURN 0;
END IF;
-----------------------------------------------------------
-- Get onhand available to reserve quantity using API
-- passing inventory item id and organization id as
-- a parameters.
-----------------------------------------------------------
l_onhand_reserve := apps.xx_utility_pkg.Get_Available_to_Reserve_org(l_inventory_item_id,l_organization_id);
Dbms_output.put_line('Onhand availability to Reserve quantity = '||l_onhand_reserve);
-----------------------------------------------------------
-- Get hard reservation quantity by
-- passing inventory item id and organization id as
-- a parameters to cursor Get_Hard_reservation
-----------------------------------------------------------
OPEN Get_Hard_reservation(l_inventory_item_id,l_organization_id);
FETCH Get_Hard_reservation INTO l_hard_reservation;
CLOSE Get_Hard_reservation;
Dbms_output.put_line('Hard Reserve quantity = '||l_hard_reservation);
-----------------------------------------------------------
-- Get Soft reservation quantity by
-- passing inventory item id and organization id as
-- a parameters to cursor
-- Get_so_soft_reservation for sales lines
-----------------------------------------------------------
OPEN Get_so_soft_reservation(l_inventory_item_id,l_organization_id);
FETCH Get_so_soft_reservation INTO l_so_soft_reservation;
CLOSE Get_so_soft_reservation;
-----------------------------------------------------------
-- Get Soft reservation quantity by
-- passing inventory item id and organization id as
-- a parameters to cursor
-- Get_wo_soft_reservation for work orders
-----------------------------------------------------------
OPEN Get_wo_soft_reservation(l_inventory_item_id,l_organization_id);
FETCH Get_wo_soft_reservation INTO l_wo_soft_reservation;
CLOSE Get_wo_soft_reservation;
-----------------------------------------------------------
-- Get Sales order line sum quantity for those
-- lines which has a status of 'Released to Warehouse'
-- at their shipment level for given parameters
-----------------------------------------------------------
OPEN Get_so_qty(l_inventory_item_id,l_organization_id);
FETCH Get_so_qty INTO l_so_released_ware_qty;
CLOSE Get_so_qty;
/*
INSERT INTO xxapps.xxeg_calculated_onhand_tbl
(
order_line_id,
inventory_item_id,
organization_id,
available_to_reserve,
hard_reservation,
soft_reservation_so,
soft_reservation_wo,
other_so_lines_qty,
run_date
)
VALUES
(
p_so_line_id,
l_inventory_item_id,
l_organization_id,
NVL(l_onhand_reserve,0),
NVL(l_hard_reservation,0),
NVL(l_so_soft_reservation,0),
NVL(l_wo_soft_reservation,0),
NVL(l_so_released_ware_qty,0),
sysdate
);
Commit Work;
Create table xxapps.xxeg_calculated_onhand_tbl
(
order_line_id NUMBER,
inventory_item_id NUMBER,
organization_id NUMBER,
available_to_reserve NUMBER,
hard_reservation NUMBER,
soft_reservation_so NUMBER,
soft_reservation_wo NUMBER,
other_so_lines_qty NUMBER,
run_date DATE
)
*/
RETURN (NVL(l_onhand_reserve,0) + NVL(l_hard_reservation,0) - (NVL(l_so_soft_reservation,0) + NVL(l_wo_soft_reservation,0)) - NVL(l_so_released_ware_qty,0));
EXCEPTION
WHEN others THEN
RETURN (0);
END xx_eg_calculated_onhand;

--------------------------------------------
Kiran