OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » PLS-00049: bad bind variable 'INVENTORY_ITEM

Home » Applications » Oracle Fusion Apps & E-Business Suite » PLS-00049: bad bind variable 'INVENTORY_ITEM_ID'

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589197]

Wed, 03 July 2013 05:03

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi,

In my Function i used "where INVENTORY_ITEM_ID=: INVENTORY_ITEM_ID", Error--PLS-00049: bad bind variable 'INVENTORY_ITEM_ID', Actually based on INVENTORY_ITEM_ID i want to display ONHANDQUANTITY Details..So,can any onr please suggest me?

Thanks&Regards,

Siva

Report message to a moderator

Re: PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589202 is a reply to message #589197]

Wed, 03 July 2013 05:26

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

How Can i call multiple values in a Function Call?

Report message to a moderator

Re: PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589204 is a reply to message #589202]

Wed, 03 July 2013 05:30

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As of "bad bind variable": where is that function? Is it a stored one, or in a form, or ...?

As of "calling multiple values in a function call": what does that mean? I can't imagine what would that be. Could you explain it (possibly with an example)?

Report message to a moderator

Re: PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589206 is a reply to message #589197]

Wed, 03 July 2013 05:37

rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait

Junior Member

Hi
You cannot call a bind variable inside a function! You have to either pass it to the function or assign to a local variable through a Select statement or constant value, please see below

onhand_qty := myfunction(item_id);

Create or replace function myfunction(p_item_id NUMBER) return number is
l_item_id number;
oh_qty NUMBER
Begin
l_item_id := p_item_id; --this is unnecessary as you can straight away refer to p_item_id

Select quantity into onhand_qty from my table
where item_id = p_item_id; -- (or l_item_id) as you wish

return onhand_qty;

Exception
when no_data_found then
return 0;
End;

[Updated on: Wed, 03 July 2013 05:39]

Report message to a moderator

Re: PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589207 is a reply to message #589204]

Wed, 03 July 2013 05:41

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

CREATE OR REPLACE FUNCTION APPS.Xxc_onhand_quantity
RETURN NUMBER
IS
x_onhand_qty NUMBER;
x_iface_qty NUMBER;
x_temp_qty NUMBER;
x_reserv_qty NUMBER;
x_total NUMBER;
CURSOR c_onhand_quantity IS
SELECT SUM(transaction_quantity) onhand_qty
--INTO v1
FROM mtl_onhand_quantities
WHERE inventory_item_id = inventory_item_id
AND organization_id = 1381;
--AND subinventory_code = 'Vessel';
CURSOR c_iface_qty IS
SELECT SUM(primary_quantity) iface_qty
--INTO v2
FROM mtl_transactions_interface
WHERE inventory_item_id = inventory_item_id
AND organization_id = 1381;
--AND subinventory_code = 'Vessel';
CURSOR c_temp_qty IS
SELECT SUM(primary_quantity) temp_qty
--INTO v3
FROM mtl_material_transactions_temp
WHERE inventory_item_id = inventory_item_id
AND organization_id = 1381
AND Nvl(process_flag, 'E') <> 'E';
--AND subinventory_code = 'Vessel';
CURSOR c_reserv_qty IS
SELECT SUM(reservation_quantity) reserv_qty
--INTO v4
FROM mtl_reservations
WHERE inventory_item_id = inventory_item_id
AND organization_id = 1381;
--AND subinventory_code = 'Vessel';
BEGIN
FOR crec IN c_onhand_quantity LOOP
x_onhand_qty := crec.onhand_qty;
END LOOP;

FOR crec IN c_iface_qty LOOP
x_iface_qty := crec.iface_qty;
END LOOP;

FOR crec IN c_temp_qty LOOP
x_temp_qty := crec.temp_qty;
END LOOP;

FOR crec IN c_reserv_qty LOOP
x_reserv_qty := crec.reserv_qty;
END LOOP;

x_total := Nvl(x_onhand_qty, 0) - ( Nvl(x_iface_qty, 0) + Nvl(x_temp_qty, 0) + Nvl(x_reserv_qty, 0) );
RETURN x_total;
END;

In my Table so many "inventory_item_id" so, how can i called these inventory_item_id's..

Thanks,

Report message to a moderator

Re: PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589208 is a reply to message #589207]

Wed, 03 July 2013 05:45

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Aha, you probably need to create a function with a parameter, such as

CREATE OR REPLACE FUNCTION APPS.Xxc_onhand_quantity (par_inventory_item_id in number)
  return number
is
begin
  ...
  select blabla into xyz
    from some_table
    where inventory_item_id = par_inventory_item_id         --> use it that way
      and ...
end;

@rajthampi suggests the same approach, so I guess that it might be what you are looking for.

rajthampi

You cannot call a bind variable inside a function!

... but if it is a function that is created in a form, then you can reference a form item, so - in that case - it is a valid option (though, it is not really a "bind variable" but an "item").

Report message to a moderator

Re: PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589209 is a reply to message #589207]

Wed, 03 July 2013 05:46

rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait

Junior Member

There is one API returns the ATT, ATR quantities. Start using it! Sample code as below

CREATE OR REPLACE FUNCTION APPS.xx_retrive_quantity_f (item_id NUMBER,
org_id NUMBER,
subinv VARCHAR2
)
return number
IS
v_api_return_status VARCHAR2 (1);
v_qty_oh NUMBER;
v_qty_res_oh NUMBER;
v_qty_res NUMBER;
v_qty_sug NUMBER;
v_qty_att NUMBER;
v_qty_atr NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2 (1000);
v_inventory_item_id VARCHAR2 (250) := item_id;
v_organization_id VARCHAR2 (10) := org_id;
v_subinventory_code VARCHAR2 (20) := subinv;
qattrns number := 0;
BEGIN
inv_quantity_tree_grp.clear_quantity_cache;

apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES (
p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => v_api_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_organization_id => v_organization_id,
p_inventory_item_id => v_inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NVL(v_subinventory_code,NULL),
p_locator_id => NULL,
x_qoh => v_qty_oh,
x_rqoh => v_qty_res_oh,
x_qr => v_qty_res,
x_qs => v_qty_sug,
x_att => v_qty_att,
x_atr => v_qty_atr
);
qattrns := v_qty_att;
return(nvl(qattrns,0));
END xx_retrive_quantity_f;

Once compiled you should call the function like following

my_item_qty := APPS.xx_retrive_quantity_f (item_id, org_id, subinv);

eg: my_item_qty := APPS.xx_retrive_quantity_f (1222122, 405, 'MYSUBINV');

leaving the subinv NULL will generate the quantity for entire org, otherwise subinventory quantity will be calculated

regards,

raj

[Updated on: Wed, 03 July 2013 05:47]

Report message to a moderator

Re: PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589210 is a reply to message #589208]

Wed, 03 July 2013 05:48

rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait

Junior Member

You are absolutely right! I missed the forms part entirely Smile

Thanks

Report message to a moderator

Re: PLS-00049: bad bind variable 'INVENTORY_ITEM_ID' [message #589219 is a reply to message #589208]

Wed, 03 July 2013 06:43

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Thanks to rajthampi,littlefoot..

As said by Littlefoot was worked

... but if it is a function that is created in a form, then you can reference a form item, so - in that case - it is a valid option

Thanks,
Siva.. Razz

Report message to a moderator

Previous Topic:	OAF Page update capabilities
Next Topic:	Concurent Request error's

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 04:51:24 CST 2025