OraFAQ Forum: Reports & Discoverer » how to implement in the oracle reports just create Data Model

Home » Developer & Programmer » Reports & Discoverer » how to implement in the oracle reports just create Data Model

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

how to implement in the oracle reports just create Data Model [message #618134]

Tue, 08 July 2014 06:37

mist598
Messages: 1195
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

Report message to a moderator

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

Michel Cadot
Messages: 68641
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".

Report message to a moderator

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

mist598
Messages: 1195
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

Smile

Please move to the Report Forum.

Report message to a moderator

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

Littlefoot
Messages: 21807
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.

Report message to a moderator

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

mist598
Messages: 1195
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

Report message to a moderator

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

mist598
Messages: 1195
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;

Report message to a moderator

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

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You shouldn't need formula columns. Just call the functions directly in the main query.

Report message to a moderator

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

mist598
Messages: 1195
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

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

Report message to a moderator

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

mist598
Messages: 1195
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

Report message to a moderator

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

Littlefoot
Messages: 21807
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.

Report message to a moderator

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

mist598
Messages: 1195
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

Report message to a moderator

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

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

mist598
Messages: 1195
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;

Report message to a moderator

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

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

So pass it, what's stopping you?

Report message to a moderator

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

mist598
Messages: 1195
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

Report message to a moderator

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

cookiemonster
Messages: 13920
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?

Report message to a moderator

Previous Topic:	How to run sql query
Next Topic:	how to write CF in the Report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 08:00:20 CDT 2024