I have following oracle query which need to be used as a Data Source in OBIEE Physical Layer. I guess I have to create stored proc. How do I implement this in OBIEE RPD and how do I implement the respective Dashboard prompts for the parameters.

Where do SITE_NAME and CARD_SLOT exist in your physical model? The way I would do this is by creating a view that exposes SITE_NAME and CARD_SLOT so that you can join it in a another view or the physical layer. It looks like this is a hierarchy table so I would suggest you prepopulate a hierarchy table to avoid performance issues.

This query is from Business Object. I am trying to convert BO Report to OBIEE solution. The SITE_NAME and CARD_SLOT exists in PORTS table but I can not join to other outer tables. What do you mean by prepopulate a hier. table? The hier. table CIRC_PATH_ELEMENT is already populated. The INNER table PORTS having parameter is used only for feeding START BY clause. Please clarify

That's probably a question for the PL/SQL forum although Google should help you. But you basically pass in the parameter of the function to the SP. Functions must return a value so you will need an output parameter on the SP to get the data out of the SP and then you can return it at the end of the function.

Check out here to the end of the article for the pipelined function.
http://www.akadia.com/services/ora_pipe_functions.html

But are you sure that OBI is the good tool for what you want.
Have you a fact table ? Which kind of report do you want with a flat bill of material ?
If you only want to run a report based on two parameters, I will work with BI Publisher.

If I assume that you don't need all bill of material, an other work around will be to store all your data in a flat table by procedure.