Home » Developer & Programmer » Reports & Discoverer » reporting against aphold.release name (Oracle FMS 11.5.10, Discoverer)
reporting against aphold.release name [message #554016] Wed, 09 May 2012 15:15 Go to next message
cvtweavee1
Messages: 6
Registered: May 2011
Location: Cardiff
Junior Member
Does any one have any idea why we seem to be unable to report against the ap invoice hold release date using Discoverer in Oracle 11.5.10? the person who wrote our current report used a decode statement to look at the last update date of the release lookup code to create a release date, but i am trying to recreate this in a different tool (Qlikview) and just wanted to understand why we seem to be unable to report on the field as is!
Re: reporting against aphold.release name [message #554017 is a reply to message #554016] Wed, 09 May 2012 15:31 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No idea.

Could you share the DECODE statement? Maybe someone would be able to assist.
Re: reporting against aphold.release name [message #554406 is a reply to message #554017] Mon, 14 May 2012 04:11 Go to previous messageGo to next message
cvtweavee1
Messages: 6
Registered: May 2011
Location: Cardiff
Junior Member
Hiya, have discovered the answer! in short, while there is a field called 'release date' in applications, in the table ap_holds_v, there is no such field! only the last update date of the lookup code! the decode statement for anyone who would like it is:

thanks anyway Smile
SQL SELECT
ALC1.MEANING HOLD_NAME
, AH.INVOICE_ID
, AH.HOLD_REASON HOLD_REASON
, DECODE(AH.HELD_BY, 5,'System', FU.USER_NAME) HELD_BY_USER_NAME
, AH.HOLD_DATE HOLD_DATE
, ALC3.MEANING RELEASE_NAME
, AH.RELEASE_REASON RELEASE_REASON
, DECODE(AH.RELEASE_LOOKUP_CODE, NULL, NULL, DECODE(AH.LAST_UPDATED_BY,5, 'System', FU2.USER_NAME)) RELEASE_BY_USER_NAME
, TO_DATE(DECODE(AH.RELEASE_LOOKUP_CODE, NULL, NULL, AH.LAST_UPDATE_DATE)) RELEASE_DATE
, AH.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM AP_HOLDS_ALL AH
, AP_HOLD_CODES AHC
, FND_LOOKUP_VALUES ALC1
, FND_LOOKUP_VALUES ALC3
, FND_USER FU
, FND_USER FU2
WHERE AH.HOLD_LOOKUP_CODE = AHC.HOLD_LOOKUP_CODE (+)
AND ALC1.LOOKUP_TYPE (+) = 'HOLD CODE'
AND ALC1.LOOKUP_CODE (+) = AH.HOLD_LOOKUP_CODE
AND ALC3.LOOKUP_TYPE (+) = 'HOLD CODE'
AND ALC3.LOOKUP_CODE (+) = AH.RELEASE_LOOKUP_CODE
AND AH.HELD_BY = FU.USER_ID (+)
AND AH.LAST_UPDATED_BY = FU2.USER_ID
;
Re: reporting against aphold.release name [message #554409 is a reply to message #554406] Mon, 14 May 2012 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for the feedback; I'm glad you found the solution!

If it is not a problem, spend not more than 10 seconds in viewing this page - it will show you how to use [code] tags which will preserve formatting and make your code easier to read.
Re: reporting against aphold.release name [message #554410 is a reply to message #554409] Mon, 14 May 2012 04:29 Go to previous message
cvtweavee1
Messages: 6
Registered: May 2011
Location: Cardiff
Junior Member
great, thanks for this, will bear in mind next time i need help Smile
Previous Topic: What is the difference between ......
Next Topic: MATRIX REPORT TO EXCEL FILE
Goto Forum:
  


Current Time: Fri Mar 29 09:02:12 CDT 2024