Is it possible to use database PACKAGE in a Universe Object?

[MrHopkinson]

In TOAD I'm able to do the following:

select
......., AP_INVOICES_PKG.GET_POSTING_STATUS( AI.INVOICE_ID) POSTING_FLAG, .....
from AP_INVOICES_all AI, po_vendors v
where...
...and AP_INVOICES_PKG.GET_POSTING_STATUS( AI.INVOICE_ID) = 'N'

Is it possible to call this package somehow in a Universe Object within BO Designer?

Many thanks, Steven

 

[skrandel]

Sure. Build an object that has AP_INVOICES_PKG.GET_POSTING_STATUS( AI.INVOICE_ID) as it's statement. As long as the package is available to the id in the connection, you're good to go.

Steve Krandel
Intuit

 

[MrHopkinson]

The package must be available to the userID of the BO Connection as its the same one Im using in TOAD to run the same query. Although..

It doesn't seem to work. I created an object for a field taken from a package:
Select box: AP_INVOICES_PKG.GET_POSTING_STATUS( AI.INVOICE_ID)
with appropriate Name. WHERE box empty.

When parse is tried, 'Invalid TableName' is returned.

Similiarly with Approval Status:
Name: GET_APPROVAL_STATUS
Select Box: AP_INVOICES_PKG.GET_APPROVAL_STATUS( AI.INVOICE_ID, AI.INVOICE_AMOUNT, AI.PAYMENT_STATUS_FLAG, AI.INVOICE_TYPE_LOOKUP_CODE). where box empty.

I perhaps thought that this would be overlooked when I ran the report using the field, looking at the database, it would run the package in the object and return appropriate values.

am I going about this in the wrong way?

All replies much apppreciated.

regards, Steven

 

[blom0344]

Just a hint: parsing does sometimes fail, even if the object can be used (with other objects) in reports (queries)
When I create an object that is specified 'dummy' it will never parse, but when used with 1 other object the resulting SQL will parse..

Ties Blom

 

[MrHopkinson]

Yep apologies...school boy error.. It was the fact that the table alias I used in TOAD wasn't the same as what was in BO. I changed it and the package was generated fine.

regards, Steven.