| Invoice status for Discoverer [message #219468] |
Wed, 14 February 2007 09:35  |
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  |
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 #219510 is a reply to message #219482] |
Wed, 14 February 2007 12:43 |
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 |
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 #220288 is a reply to message #219655] |
Tue, 20 February 2007 01:27 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
