Home » SQL & PL/SQL » SQL & PL/SQL » Discoverer Query
Discoverer Query [message #403747] Mon, 18 May 2009 07:57 Go to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
Hi,

I have created a custom folder in discoverer by using the following query:

SELECT  -- 2173 records  
TO_NUMBER(fv.BUDGET) as Budget  
,gab.ACTIVITY_50274 as Project 
,gad.ACCOUNT_50297 as Category        
,fv.DESCRIPTION as DESCRIPTION   
,fv.CONTRACT_NUMBER as CONT_NUM   
,fv.CR_NUMBER as CEAR_NO       
,fv.PROJECT_TYPE as Project_TYPE  
,fv.SUPPLIER_NAME as Supplier_Name,  
,NVL(( TO_NUMBER(fv.BUDGET) ),0)-NVL(( DECODE(gab.PROJECT_50297,80067,
NVL(DECODE(SUBSTR(gab.ACCOUNT_50297,1,3),123,( SUM(gab.PERIOD_TO_DATE_CR) ),0),0)
*0,NVL(DECODE(SUBSTR(gab.ACCOUNT_50297,1,3),123,( SUM(gab.YEAR_TO_DATE_CR) ),0),0)) ),0) as Budget_Balance, 
DECODE(gab.PROJECT_50297,80067,NVL(DECODE(SUBSTR(gab.ACCOUNT_50297,1,3),123,
( SUM(gab.YEAR_TO_DATE_CR) ),0),0)*0,NVL(DECODE(SUBSTR(gab.ACCOUNT_50297,1,3),123,
( SUM(gab.YEAR_TO_DATE_CR) ),0),0)) as Capitalized, 
NVL(( SUM(DECODE(gab.CURRENCY,'SDG',gab.YEAR_TO_DATE_DR,gab.converted_YEAR_TO_DATE_DR)) ),0)
-NVL(( SUM((DECODE(gab.CURRENCY,'SDG',gab.YEAR_TO_DATE_cR,gab.converted_YEAR_TO_DATE_cR))) ),0) as Actual 
FROM 
        GLFG_ACTUAL_BALANCES gab ,
        GLFG_GL_ACCOUNTS_DESCR  gad,  
      ( select flex_value_set_id,Flex_value_id,flex_value Project,
      description,Attribute2 Budget,Attribute3 Contract_Number,Attribute4 CR_Number,
        Attribute5 Project_type,Attribute6 Supplier_name,Attribute7 Target_date 
        ,Attribute8 PO_Amount,Attribute9 Currency_code 
        From FND_FLEX_VALUES_VL  where   FLEX_VALUE_SET_ID= 1009618
        )fv --o408896,
     ,( SELECT ggp.PERIOD_NAME AS pname 
        , MAX(ggp.PERIOD_NUMBER) AS pnumber 
        FROM GLFG_GL_PERIODS ggp 
        GROUP BY ggp.PERIOD_NAME)
WHERE 
    ( (gad.ACCOUNT_ID = gab.ACCOUNT_ID)
   and (fv.PROJECT = gab.PROJECT_50297(+))
  -- and fv.project= '080162'
   and (gab.PERIOD_NAME = pname(+)))
      AND (gab.ACTIVITY_50274(+) NOT LIKE '0000%') 
   and gab.currency='USD'
GROUP BY
  TO_NUMBER(fv.BUDGET),pnumber
 ,gab.ACTIVITY_50274,gab.COMPANY_50274,gab.COST_CENTER_50274,
 gab.PERIOD_NAME,gab.SET_OF_BOOKS_NAME,gad.ACCOUNT_50297
 ,gab.ACCOUNT_50297,gab.PROJECT_50297,fv.DESCRIPTION
 ,fv.CONTRACT_NUMBER,fv.CR_NUMBER,fv.PROJECT_TYPE,fv.SUPPLIER_NAME
 ,fv.TARGET_DATE,fv.PO_AMOUNT,fv.CURRENCY_CODE
   UNION
 SELECT DISTINCT 
  to_number(ffv.ATTRIBUTE2)--"Budget" 
 ,ffv.FLEX_VALUE-- "Project"     
  ,'Blank'-- "Category", 
  ,ffv.DESCRIPTION, 
  ffv.ATTRIBUTE3 --"Cont_Num"
  , ffv.ATTRIBUTE4  
  , ffv.ATTRIBUTE5 --"Project Type"  
  , ffv.ATTRIBUTE6 --"Supplier Name" --12
   ,to_number(ffv.ATTRIBUTE2)-- "Budget Balance"  
  ,0 "Capitalized"--18
  ,0 "Actual"--19
FROM 
FND_FLEX_VALUES_VL      ffv
WHERE  ffv.FLEX_VALUE not in (select PROJECT_50297 from glfg_actual_balances )
 and ffv.FLEX_VALUE_SET_ID = 1009618


the discoverer report results are grouped by gl periods. For the blank category (the second query in the union), i need the record of any blank category project to continue showing up in the next gl period in the blank category until the gl date that it shows up with in the first query.

For example if a project x was in blank for the first time at gl date May 08 and is in second query for first time in Sep 08. Then project x record should be in the blank category for the GL periods June08,July08, and August08.

Any ideas are appreciated.
Re: Discoverer Query [message #403749 is a reply to message #403747] Mon, 18 May 2009 08:07 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Abeer30,

Please post a test case along with the actual results you are getting. Also post the desired results.

Please don't multipost.

Regards,
Jo
Previous Topic: 2 foreign keys reference
Next Topic: Stored Procedure too slow
Goto Forum:
  


Current Time: Wed Dec 07 10:45:19 CST 2016

Total time taken to generate the page: 0.75037 seconds