| reporting against aphold.release name [message #554016] |
Wed, 09 May 2012 15:15  |
 |
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 #554406 is a reply to message #554017] |
Mon, 14 May 2012 04:11  |
|
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 
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
;
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
