Hi

I am trying to use the view pay_gb_balances_v to extract all balances that have a balance value of >0. The view seems to be the ideal solution. As usual big BUT coming up. If I do a select * from this view for 1 assignment I am getting the following errors:

ORA-2001 :Dynamic SQL for DB item failed : ORA-2001: Database Item requires context SOURCE_TEXT to be set

APPS.PAY_BALANCE_PKG

APPS.HR_GBBAL ( This is the API that is used to make the calculation run_results)

Any ideas, help, drinks appreciated.

Kind Regards

Romesh

Hello Romesh - there's a first, you actually taking some advice from me! Welcome to the forum....

I suspect that message is coming about because you aren't specifying a balance/dimension. The source text context is to do with element(entry)-level balance dimensions. My guess is that if you tried querying the view for, say, a _ASG_TD_YTD dimension you wouldn't get that message. That may or may not help you, in that I'm guessing you'll still need to do something to extract element-level balances. But one step at a time, eh?

Hi Clive


Nice photo u are looking much better these days.

Think u have hit on something here. When I remove all the court orders from the query it seems to work. Then when I try and further restrict the selection by saying result_value <> 0 it all goes horrible again.

So basically select all from the view where I exclude Court Orders and all works. Add the restriction about results_value <> 0 and all fails again with above error.

Regards

Romesh

Too kind young man, as you will appreciate I have lost a deal of weight recently. The cycle shorts still chafe though, it fair does my head right in I kid you not.

Might be that the addition of the value<>0 is upsetting the optimiser in some way, so that it tries to resolve the balance value bit before filtering out the balance dimension. Sounds like we need an optimiser hint in the query to force it into going to the balance dimensions table first. Please don't embarrass me by actually asking what to put in order to actually do that!