Home » Developer & Programmer » Reports & Discoverer » how to implement in the oracle reports just create Data Model
how to implement in the oracle reports just create Data Model [message #618134] Tue, 08 July 2014 06:37 Go to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

I have package and having 5 Functions.I need to create XML report on this.

create or replace xxc_pkg
as
procedure xxc_proc(p_id in varchar2)
is
CURSOR c_dd(p_num IN VARCHAR2)
IS
select ....
...............
...............

where some column = v_num;

FOR c_rec IN c_dd(v_num) LOOP
I am calling all the 5 Functions.
end loop;
Fuction a(x_id in number)
Fuction b(x_id in number)
Fuction c(x_id in number)
Fuction e(x_id in number)
Fuction f(x_id in number)
END xxc_pkg;

Can you please help me on this ?

How to implement on this in the report.

Thanks
Re: how to implement in the oracle reports just create Data Model [message #618136 is a reply to message #618134] Tue, 08 July 2014 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 66682
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

By "report" do you mean Oracle Reports? in this case the topic is in the wrong forum and we have to move.
Otherwise, the question is not clear and you have to explain what you mean by "report".

Re: how to implement in the oracle reports just create Data Model [message #618137 is a reply to message #618136] Tue, 08 July 2014 06:45 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
By "report" do you mean Oracle Reports? in this case the topic is in the wrong forum and we have to move.
Otherwise, the question is not clear and you have to explain what you mean by "report".


Yes , I forgot it Thank U Michel... Smile Please move to the Report Forum.
Re: how to implement in the oracle reports just create Data Model [message #618149 is a reply to message #618137] Tue, 08 July 2014 09:39 Go to previous messageGo to next message
Littlefoot
Messages: 21509
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I never created an XML report, but - generally speaking - I'd try with a (report) query which calls packaged functions. Its SELECT statement would be an exact copy of the C_DD cursor. Report would have one parameter: P_NUM.

Another option is to use your package and prepare report data in a (global temporary?) table; report would then simply fetch that data.

Maybe it is possible to use a packaged procedure as a report source, but I don't know anything about it.
Re: how to implement in the oracle reports just create Data Model [message #618170 is a reply to message #618149] Wed, 09 July 2014 01:07 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Thank u Littlefoot, can you please design sample report ,package with 2 functions call into the cursor for loop?


FUNCTION XX_NUMBER(p_id NUMBER,p_cust_id VARCHAR2)
RETURN NUMBER
IS
    v_number NUMBER:=0;
BEGIN
   BEGIN
    SELECT number
      INTO v_number
      FROM x,y,z
     WHERE x.custom_value = To_char(p_id)
       AND y.cust_id = p_cust_id;
    EXCEPTION
        WHEN OTHERS THEN
           v_number:= NULL;
    END;
	  RETURN v_number;
END XX_NUMBER;


I have the above function, i need to pass both p_id,p_cust_id , I need to write separate function and call that Function into the Select Statement means in the DATA MODEL..


Thanks
Re: how to implement in the oracle reports just create Data Model [message #618173 is a reply to message #618170] Wed, 09 July 2014 01:35 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
FUNCTION XX_NUMBER(p_id NUMBER,p_cust_id VARCHAR2)
RETURN NUMBER
IS
    v_number NUMBER:=0;
BEGIN
   BEGIN
    SELECT number
      INTO v_number
      FROM x,y,z
     WHERE x.custom_value = To_char(p_id)
       AND y.cust_id = p_cust_id;
    EXCEPTION
        WHEN OTHERS THEN
           v_number:= NULL;
    END;
	  RETURN v_number;
END XX_NUMBER;


I created the above Function at the Program Units and created the CF_1 in the Data Model, is this correct process ? please let me know?
function CF_1Formula return Number is
begin
  return XX_NUMBER(:INVENTORY_ITEM_ID,:p_cust_id );
end;
Re: how to implement in the oracle reports just create Data Model [message #618212 is a reply to message #618173] Wed, 09 July 2014 07:44 Go to previous messageGo to next message
cookiemonster
Messages: 13715
Registered: September 2008
Location: Rainy Manchester
Senior Member
You shouldn't need formula columns. Just call the functions directly in the main query.
Re: how to implement in the oracle reports just create Data Model [message #618214 is a reply to message #618212] Wed, 09 July 2014 07:51 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
You shouldn't need formula columns. Just call the functions directly in the main query.

I have function created in the Program Units FUNCTION XX_NUMBER(p_id NUMBER,p_cust_id VARCHAR2)

Thanks for reply cookiemonster.. Smile
I have to call the above function in the select query like below

V_NUMBER:=XX_NUMBER(p_id ,p_cust_id);

select name,V_NUMBER from table name;

Like that above right?

No need to pass parameter's in the V_NUMBER right?

Thanks
Re: how to implement in the oracle reports just create Data Model [message #618219 is a reply to message #618212] Wed, 09 July 2014 08:37 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi cookiemonster,

SELECT a.inventory_item_id,
       XX_NUMBER(p_id,p_cust_id)
  FROM   mtl_system_items_b a,
         ar_customers x, 
WHERE x.customer_id  = p_cust_id


(or)
SELECT a.inventory_item_id,
       XX_NUMBER(a.inventory_item_id,x.customer_id)
  FROM   mtl_system_items_b a,
         ar_customers x, 
WHERE x.customer_id  = p_cust_id



How can i call the Function which are created in the Program Units. Which is one correct? Please correct my issue.

Thanks
Re: how to implement in the oracle reports just create Data Model [message #618241 is a reply to message #618219] Wed, 09 July 2014 16:09 Go to previous messageGo to next message
Littlefoot
Messages: 21509
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
None of them is correct, because - if those P_xxx are parameters, the should be referenced with a colon (i.e. :P_CUST_ID or :P_ID).

Once you fix that, both statements are correct, but result they return might (and most probably will) differ.
Re: how to implement in the oracle reports just create Data Model [message #618243 is a reply to message #618241] Wed, 09 July 2014 22:59 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
the should be referenced with a colon (i.e. :P_CUST_ID or :P_ID).


Suppose if i p_cust_id(1234) then i will get 270 records, but if i take :P_CUST_ID ,:P_ID(both) in the select stmt , should pass both parameter's right?

I am getting below while calling the XX_NUMBER(a.inventory_item_id,x.customer_id) (or) XX_NUMBER(p_id,p_cust_id) format.
ORA-00904: "XXC_NUMBER":invalid identifier
==>XXC_NUMBER(a.inventory_item_id,x.customer_id)

I have create only one user parameter p_cust_id

Please help.

Note: The function is not created in the DB.

How to call the Function in the select statement ? Please give me neat steps
Re: how to implement in the oracle reports just create Data Model [message #618259 is a reply to message #618243] Thu, 10 July 2014 02:41 Go to previous messageGo to next message
cookiemonster
Messages: 13715
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want to call the function in the select statement then the function must be created in the DB.
Re: how to implement in the oracle reports just create Data Model [message #618267 is a reply to message #618259] Thu, 10 July 2014 03:34 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
If you want to call the function in the select statement then the function must be created in the DB.

Hi cookiemonster,in the place of 3488 i need to pass p_cust_id

SELECT 
       xxc_inventory.Xxc_qty(msi.item_id)       QTY, 
       xxc_inventory.Xxc_price(msi.item_id, [b]3488[/b]) PRICE,
       msi.item_id, 
FROM   mtl_system_items_b msi
where ...
.....
....

BEGIN
     SELECT SUBSTR(customer_number,1,3),customer_name
       FROM ar_customers
      WHERE customer_id=p_cust_id;
END;



Re: how to implement in the oracle reports just create Data Model [message #618274 is a reply to message #618267] Thu, 10 July 2014 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 13715
Registered: September 2008
Location: Rainy Manchester
Senior Member
So pass it, what's stopping you?
Re: how to implement in the oracle reports just create Data Model [message #618276 is a reply to message #618274] Thu, 10 July 2014 04:28 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
SELECT SUBSTR(customer_number,1,3),customer_name
       FROM ar_customers
      WHERE customer_id=p_cust_id;


where to write the above query in the select or take different data model?

Thanks
Re: how to implement in the oracle reports just create Data Model [message #618277 is a reply to message #618276] Thu, 10 July 2014 04:37 Go to previous message
cookiemonster
Messages: 13715
Registered: September 2008
Location: Rainy Manchester
Senior Member
No idea.
As usual you have not supplied any details of what you're trying to achieve.
We don't know what the data model is.
We don't know what the report is supposed to show.
So how are we supposed to know exactly where that query should go?
Previous Topic: How to run sql query
Next Topic: how to write CF in the Report
Goto Forum:
  


Current Time: Wed Nov 13 06:04:45 CST 2019