Discoverer Query

Here you can post the Business Intelligence (BI) related queries and replies.
Post Reply
AIMAM
Posts: 139
Joined: Thu May 17, 2007 6:04 am
Location: Egypt

Discoverer Query

Post by AIMAM »

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.
AIMAM
Posts: 139
Joined: Thu May 17, 2007 6:04 am
Location: Egypt

Post by AIMAM »

Hi,


I believe that my question above was a little bit confusing.
Let me try to simplify it by asking a number of questions.
The first one is:

In table GLFG_ACTUAL_BALANCES, I have a column PERIOD_NAME which is varchar2 (15 Byte) and stores dates in the format MON_YY. For example MAY-09, AUG-08.

How can I find the max date of PERIOD_NAME with respect to the second column PROJECT_50297 ?
MAX function does not work properly. It gives me the maximum of the period_name of a year to be SEP-08, whereas MAY-09 is there.

Any ideas please???
senthil72
Posts: 2
Joined: Wed Oct 29, 2008 4:57 am
Location: Bahrain

Post by senthil72 »

Hi,

You can reframe this query:

Code: Select all

Select Account_id, Max(To_date(Substr(period_name,-6),'MON-YY')) PERIOD from GLFV_ACTUAL_BALANCES
where account_id = 1100
group by account_id

Only thing you will have problem in Adj-Dec-07, Adj-Dec-08 values..

Regards,

Senthil K
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest