Discoverer Query
Posted: Mon May 18, 2009 8:50 am
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.
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.