Home » Applications » Oracle Fusion Apps & E-Business Suite » Invoice status for Discoverer
Invoice status for Discoverer [message #219468] Wed, 14 February 2007 09:35 Go to next message
gaizka71
Messages: 10
Registered: May 2005
Location: Erandio
Junior Member
Hi,

I need to locate where the status of the invoice is stored in Oracle tables.

I would like to create a discoverer report with the status that appears in the invoice workbench that shows whether the invoice was validated, never validated, approved... etc.


Thanks
gaizka
Re: Invoice status for Discoverer [message #219476 is a reply to message #219468] Wed, 14 February 2007 10:38 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
put this in your select statement where you should have ap_invoices_all table
AP_INVOICES_PKG.GET_APPROVAL_STATUS
(AI.INVOICE_ID,AI.INVOICE_AMOUNT,AI.PAYMENT_STATUS_FLAG                                           AI.INVOICE_TYPE_LOOKUP_CODE) APPROVAL_STATUS_LOOKUP_CODE


Regards
Prashant Pathak
Re: Invoice status for Discoverer [message #219482 is a reply to message #219476] Wed, 14 February 2007 10:49 Go to previous messageGo to next message
gaizka71
Messages: 10
Registered: May 2005
Location: Erandio
Junior Member
Thanks for your answer, could you please put this into a select so i can see a basic example?

Thanks again.
Re: Invoice status for Discoverer [message #219510 is a reply to message #219482] Wed, 14 February 2007 12:43 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
select  ai.invoice_num,
AP_INVOICES_PKG.GET_APPROVAL_STATUS(AI.INVOICE_ID,
AI.INVOICE_AMOUNT,
AI.PAYMENT_STATUS_FLAG
,AI.INVOICE_TYPE_LOOKUP_CODE) APPROVAL_STATUS_LOOKUP_CODE
from ap_invoices_all ai where AI.CREATION_DATE between '1-NOV-2006' and '2-NOV-2006'
Re: Invoice status for Discoverer [message #219596 is a reply to message #219510] Thu, 15 February 2007 01:42 Go to previous messageGo to next message
gaizka71
Messages: 10
Registered: May 2005
Location: Erandio
Junior Member
Thanks for your select,

unfortunately the information that I need is not there, see output on attached file...

I tried metalink as well and this is the clue they gave me

********************************************
Use approval_status_lookup_code from view = ap_invoices_v.

execute dbms_application_info.set_client_info(<your org_id here>);

select distinct approval_status_lookup_code
from ap_invoices_v;

**********************************************

Unfortunately I can not used execute commands in discoverer can I?
Re: Invoice status for Discoverer [message #219655 is a reply to message #219596] Thu, 15 February 2007 05:31 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Have you executed the query in toad/sql session or in Discoverer?
Have you apps user or your own access?
Re: Invoice status for Discoverer [message #220288 is a reply to message #219655] Tue, 20 February 2007 01:27 Go to previous messageGo to next message
gaizka71
Messages: 10
Registered: May 2005
Location: Erandio
Junior Member
Have you executed the query in toad/sql session or in Discoverer?

I have executed the query in toad and it works, but how do I go about executing the query in discoverer? if I try with an sql folder it tells me it is not a valid sql string....


Have you apps user or your own access?
Users will have the user name accesss for applications and from it to Discoverer.

Thanks.
Re: Invoice status for Discoverer [message #220443 is a reply to message #220288] Tue, 20 February 2007 12:58 Go to previous message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
ask your DBA to give execute permission for that particular package for Discoverer user.
Previous Topic: Exception report
Next Topic: difference between PER and HR in tables
Goto Forum:
  


Current Time: Sat Dec 03 08:24:48 CST 2016

Total time taken to generate the page: 0.15315 seconds