Discoverer Report With Example MD70

MD.070

Application Functional &

 Technical Design (Reports)

Bank Of XXXX – Budget Performance

  

MD70_BOG_BUDGET_PEFORMANCE_MONTHLY_REPORT

(XXBOG Budget Performance Monthly Report)

Author:                         Jyothi Bodapati

Creation Date:            28 – Sep - 2007

Last Updated:             28 –Sep - 2007

Document Ref:           No Previous Document

Version:                        Draft 1a

Note: 

Note: 

Note: 

Note: 

Note:Title, Subject, Last Updated Date, Reference Number, and Version are marked by a Word Bookmark so that they can be easily reproduced in the header and footer of documents.  When you change any of these values, be careful not to accidentally delete the bookmark.  You can make bookmarks visible by selecting Tools->Options…View and checking the Bookmarks option in the Show region.

Approvals:

Ganesh Trivedi	Team Lead

Document Control

Change Record

3

Date	Author	Version	Change Reference
28-Aug-07	Jyothi Bodapati	Draft 1a	No Previous Document

Reviewers

Name	Position
Sailaja Basina	Peer

Distribution

Copy No.	Name	Location
1	Library Master	Project Library
2
3

Note: 

Note: 

Note: 

Note: 

Note:The copy numbers referenced above should be written into the Copy Number space on the cover of each distributed copy.  If the document is not controlled, you can delete this table, the Note To Holders, and the Copy Number label from the cover page.

Note To Holders:

If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.

Contents

Contents. 3

Basic Business Needs. 3

Report Layout Template. 3

Report Parameters. 3

Report Headers. 3

Column Contents and Headings. 3

Report Submission Process. 3

Technical Overview... 3

Open and Closed Issues. 3

Basic Business Needs

           

Report Name	XXBOG CHEQUE PRINT REPORT
Description	The Budget Performance Monthly Report gives detail listing of all Actual Account balances against projected monthly budget balances. .
Reports Used Today
Business Purpose	The Budget Performance Monthly Report gives detail listing of all Actual Account balances against projected monthly budget balances. .
Grouping	GL Segment
Scheduling	As and when required
Data Source	Oracle Standard Base Table
Target Location	Standard Database Server location
Security Requirement
Sort By	GL Account Values
Page Orientation	Dynamic Portrait
Paper Size	A4
Output Mode	Excel
Languages	English Only
Page Break

Assumptions/ Base Line:

1.       Budgets are allocated against each Account.

2.       There should be actual amount against budgeted accounts.

Prerequisite:

·         Actual Transactions should exist.

Report Layout Template

Report Header (On All Pages):

BANK OF GHANA ANALYSIS OF BUDGETED AND ACTUAL EXPENDITURE FOR &Parameters

BANK OF GHANA ANALYSIS OF BUDGETED AND ACTUAL EXPENDITURE FOR  MONTH ‘Aug 07’

Report Layout: Page-Detail Crosstab

Page Item:

(1)

Segment_name

BOG_ORG_TYPE

Top-axis:

(1)	(2)
Year	Flag
2007	ACTUAL/BUDGET

Left-axis

(1)	(2)	(3)
Flex_name	Account	Account Indetail
GH Operations	ADMINISTRATION	50th Anniversary Celebration expense

Datapoint

(1)
Balance
ACTUAL	BUDGET
7309.20	12305892.00

Report Parameters

Name	Mandatory – Yes / No	Condition Based?	Particulars
PERIOD MONTH	YES	No	Period Month

                               

 OUTPUT :

  

Report Headers

                Header

Header Name	Description
BANK OF GHANA ANALYSIS OF BUDGETED AND ACTUAL EXPENDITURE FOR &Parameters

Footer

Footer Name	Description
N/A

Column Contents and Headings

Column Number	Column Name	Description
1	Segment Name	This column displays the segment name.
Top-axis
1	Period Year	This column displays the period year.
2	Flag	This column displays the flag(BUDGET/ACTUAL)
Left-axis
1	Flex name	This column displays the Flex name.
2	Account	This column displays the Account.
3	Account Indetail	This column displays the Account Indetail

      

      

     

     

Report Submission Process

Standard Report Submission.

Technical Overview

Modules Referred	Oracle General Ledger
Business Area	GLBusinessArea
Folder	Custom Folder-XXBOG_BUDGET_PERFORMNCE_MONTHLY
Tables accessed	gl_code_combinations, gl_balances, fnd_flex_values_vl, fnd_id_flex_segments_vl

Detail Data Mapping

Report Header

               

Column Name	Table / Column mapping	Data Type	Remarks
N/A

Page Item

Label Name	Meaning	Table / Column mapping	Data Type	Remarks
Segment Name	Segment Name	fnd_id_flex_segments_vl/ segment_name	Varchar2

Top-axis
Label Name	Meaning	Table / Column mapping	Data Type	Remarks
Period Year	Period Year	gl_balances/ period_year	Number
Flag	Flag	gl_balances/ actual_flag	Varchar2

Left-axis
Label Name	Meaning	Table / Column mapping	Data Type	Remarks
Flex Name	Flex Name	fnd_flex_values_vl/ description	Varchar2
Account	Account	fnd_flex_values_vl / description	Varchar2
Account Indetail	Account Indetail	fnd_flex_values_vl / description	Varchar2

Data-point
Label Name	Meaning	Table / Column mapping	Data Type	Remarks
Balance	Balance	gl_balances/ (begin_balance_dr - begin_balance_cr) + ((period_net_dr -  period_net_cr))	Number

Report Parameters

Name	Type	Table / Column mapping	Data Type (Length)	Remarks
Period Month	Parameter	gl_balances / period_month	Varchar2

Item Class

Name	Table / Column mapping
PERIOD MONTH	gl_balances/ period_month

Hierarchies

Name	Label Name/Table / Column mapping
Account Hierarchy	Segment Name/ fnd_id_flex_segments_vl/ segment_name                  -
	Flex Name/ fnd_flex_values_vl/ description
	Account/ fnd_flex_values_vl / description
	Account Indetail/ fnd_flex_values_vl / description

Additional Security Requirements:

               

Security

Bussiness Area - > GLBussinessArea Users/responsibilties -> Apps,BOGSETUO

Discoverer Report Custom Folder Query:

SELECT FFVV.DESCRIPTION ACCONT,FFV.FLEX_VALUE,FFV.DESCRIPTION ACCOUNT_INDETAILS,TEMP_HALF.BALANCE,

--TO_CHAR(SUM(TEMP_HALF.BALANCE))  BALANCE,

TEMP_HALF.FLAG,

TEMP_HALF.PERIOD_NAME,

TEMP_HALF.PERIOD_YEAR,

TEMP_HALF.SEGMENT1,

FVS.SEGMENT_NAME,

FVS.DESCRIPTION FLEX_NAME

FROM

(SELECT SEGMENT6, DECODE (GB.ACTUAL_FLAG,'A','ACTUAL','BUDGET') FLAG,

 (SUM(BEGIN_BALANCE_DR) - SUM(BEGIN_BALANCE_CR)) + (SUM(PERIOD_NET_DR) -  SUM(PERIOD_NET_CR) ) BALANCE ,

  PERIOD_YEAR,

  PERIOD_NAME,

  SEGMENT1

 FROM GL_CODE_COMBINATIONS  GCC, GL_BALANCES GB

 WHERE --SEGMENT6 like '53%' AND

 GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

-- AND PERIOD_YEAR = TO_CHAR(SYSDATE,'YYYY') and period_name='Jul-07'

GROUP BY  PERIOD_YEAR,PERIOD_NAME,SEGMENT6,ACTUAL_FLAG,SEGMENT1--,SEGMENT2,SEGMENT3--,SEGMENT2,SEGMENT1

ORDER BY SEGMENT6 DESC)TEMP_HALF,

fnd_flex_values_vl  FFVV,

(SELECT F_SEG.FLEX_VALUE_SET_ID, F_SEG.SEGMENT_NAME SEGMENT_NAME,

        F_SEG.APPLICATION_COLUMN_NAME,FFV.FLEX_VALUE FLEX_VALUE,FFV.DESCRIPTION DESCRIPTION

 FROM fnd_flex_values_vl FFV,

     (select FLEX_VALUE_SET_ID,SEGMENT_NAME,APPLICATION_COLUMN_NAME

      from FND_ID_FLEX_SEGMENTS_VL where SEGMENT_NAME like 'BOG%'

      AND (ID_FLEX_NUM=50468) and (ID_FLEX_CODE='GL#') and (APPLICATION_ID=101)and (APPLICATION_COLUMN_NAME='SEGMENT1')

      order by application_id,id_flex_code,id_flex_num, decode(enabled_flag, 'Y', 1, 'N', 2),segment_num) F_SEG

WHERE           F_SEG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID) FVS,

(select  FFV.FLEX_VALUE ,FFV.DESCRIPTIO,FFV.SUMMARY_FLAG,FND_TEMP.FLEX(FLEX

&.bsp;from fnd_flex_values_vl FFV,

      (select SUBSTR(FLEX_VALUE,1,2)||'_____' FV, --replace(FLEX_VALUE,0,'_') FV,

o

                      FLEX_VALUE flex   from fnd_flex_values_vl

       where FLEX_VALUE like '__00000' and  SUMMARY_FLAG like 'Y') FND_TEMP

              --   GL_CODE_COMBINATIONS  GCC

 where FFV.FLEX_VALUE like FND_TEMP.FV

  and  FFV.SUMMARY_FLAG = 'N' ) FFV

WHERE  FFV.flex_value=TEMP_HALF.SEGMENT6

and FFVV.FLEX_VALUE = FFV.FLEX and  FFVV.SUMMARY_FLAG like 'Y'

and FVS.flex_value=TEMP_HALF.SEGMENT1

UNION

SELECT FFVV.DESCRIPTION ACCONT,FFV.FLEX_VALUE,FFV.DESCRIPTION ACCOUNT_INDETAILS,TEMP_HALF.BALANCE,

--TO_CHAR(SUM(TEMP_HALF.BALANCE))  BALANCE,

TEMP_HALF.FLAG,

TEMP_HALF.PERIOD_NAME,

TEMP_HALF.PERIOD_YEAR,

TEMP_HALF.SEGMENT2,

FVS.SEGMENT_NAME,

FVS.DESCRIPTION

FROM

(SELECT SEGMENT6, DECODE (GB.ACTUAL_FLAG,'A','ACTUAL','BUDGET') FLAG,

 (SUM(BEGIN_BALANCE_DR) - SUM(BEGIN_BALANCE_CR)) + (SUM(PERIOD_NET_DR) -  SUM(PERIOD_NET_CR) ) BALANCE ,

  PERIOD_YEAR,

 PERIOD_NAME,

  SEGMENT2

 FROM GL_CODE_COMBINATIONS  GCC, GL_BALANCES GB

 WHERE --SEGMENT6 like '53%' AND

 GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

-- AND PERIOD_YEAR = TO_CHAR(SYSDATE,'YYYY') and period_name='Jul-07'

GROUP BY  PERIOD_YEAR,PERIOD_NAME,SEGMENT6,ACTUAL_FLAG,SEGMENT2

ORDER BY SEGMENT6 DESC)TEMP_HALF,

fnd_flex_values_vl  FFVV,

(SELECT F_SEG.FLEX_VALUE_SET_ID, F_SEG.SEGMENT_NAME SEGMENT_NAME,

        F_SEG.APPLICATION_COLUMN_NAME,FFV.FLEX_VALUE FLEX_VALUE,FFV.DESCRIPTION DESCRIPTION

 FROM fnd_flex_values_vl FFV,

     (select FLEX_VALUE_SET_ID,SEGMENT_NAME,APPLICATION_COLUMN_NAME

      from FND_ID_FLEX_SEGMENTS_VL where SEGMENT_NAME like 'BOG%'

      AND (ID_FLEX_NUM=50468) and (ID_FLEX_CODE='GL#') and (APPLICATION_ID=101)and (APPLICATION_COLUMN_NAME='SEGMENT2')

      order by application_id,id_flex_code,id_flex_num, decode(enabled_flag, 'Y', 1, 'N', 2),segment_num) F_SEG

WHERE           F_SEG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID) FVS,

(select  FFV.FLEX_VALUE ,FFV.DESCRIPTION,FFV.SUMMARY_FLAG,FND_TEMP.FLEX FLEX

 from fnd_flex_values_vl FFV,

      (select SUBSTR(FLEX_VALUE,1,2)||'_____' FV, --replace(FLEX_VALUE,0,'_') FV,

                      FLEX_VALUE flex   from fnd_flex_values_vl

       where FLEX_VALUE like '__00000' and  SUMMARY_FLAG like 'Y') FND_TEMP

              --   GL_CODE_COMBINATIONS  GCC

 where FFV.FLEX_VALUE like FND_TEMP.FV

  and  FFV.SUMMARY_FLAG = 'N' ) FFV

WHERE  FFV.flex_value=TEMP_HALF.SEGMENT6

and FFVV.FLEX_VALUE = FFV.FLEX and  FFVV.SUMMARY_FLAG like 'Y'

and FVS.flex_value=TEMP_HALF.SEGMENT2 

UNION

SELECT FFVV.DESCRIPTION ACCONT,FFV.FLEX_VALUE,FFV.DESCRIPTION ACCOUNT_INDETAILS,TEMP_HALF.BALANCE,

--TO_CHAR(SUM(TEMP_HALF.BALANCE))  BALANCE,

TEMP_HALF.FLAG,

TEMP_HALF.PERIOD_NAME,

TEMP_HALF.PERIOD_YEAR,

TEMP_HALF.SEGMENT3,

FVS.SEGMENT_NAME,

FVS.DESCRIPTION

FROM

(SELECT SEGMENT6, DECODE (GB.ACTUAL_FLAG,'A','ACTUAL','BUDGET') FLAG,

 (SUM(BEGIN_BALANCE_DR) - SUM(BEGIN_BALANCE_CR)) + (SUM(PERIOD_NET_DR) -  SUM(PERIOD_NET_CR) ) BALANCE ,

  PERIOD_YEAR,

 PERIOD_NAME,

  SEGMENT3

 FROM GL_CODE_COMBINATIONS  GCC, GL_BALANCES GB

 WHERE --SEGMENT6 like '53%' AND

 GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

 -- AND PERIOD_YEAR = TO_CHAR(SYSDATE,'YYYY') and period_name='Jul-07'

GROUP BY  PERIOD_YEAR,PERIOD_NAME,SEGMENT6,ACTUAL_FLAG,SEGMENT3

ORDER BY SEGMENT6 DESC)TEMP_HALF,

fnd_flex_values_vl  FFVV,

(SELECT F_SEG.FLEX_VALUE_SET_ID, F_SEG.SEGMENT_NAME SEGMENT_NAME,

        F_SEG.APPLICATION_COLUMN_NAME,FFV.FLEX_VALUE FLEX_VALUE,FFV.DESCRIPTION DESCRIPTION

 FROM fnd_flex_values_vl FFV,

     (select FLEX_VALUE_SET_ID,SEGMENT_NAME,APPLICATION_COLUMN_NAME

      from FND_ID_FLEX_SEGMENTS_VL where SEGMENT_NAME like 'BOG%'

      AND (ID_FLEX_NUM=50468) and (ID_FLEX_CODE='GL#') and (APPLICATION_ID=101)and (APPLICATION_COLUMN_NAME='SEGMENT3')

      order by application_id,id_flex_code,id_flex_num, decode(enabled_flag, 'Y', 1, 'N', 2),segment_num) F_SEG

WHERE           F_SEG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID) FVS,

(select  FFV.FLEX_VALUE ,FFV.DESCRIPTION,FFV.SUMMARY_FLAG,FND_TEMP.FLEX FLEX

 from fnd_flex_values_vl FFV,

      (select SUBSTR(FLEX_VALUE,1,2)||'_____' FV, --replace(FLEX_VALUE,0,'_') FV,

                      FLEX_VALUE flex   from fnd_flex_values_vl

       where FLEX_VALUE like '__00000' and  SUMMARY_FLAG like 'Y') FND_TEMP

              --   GL_CODE_COMBINATIONS  GCC

 where FFV.FLEX_VALUE like FND_TEMP.FV

  and  FFV.SUMMARY_FLAG = 'N' ) FFV

WHERE  FFV.flex_value=TEMP_HALF.SEGMENT6

and FFVV.FLEX_VALUE = FFV.FLEX and  FFVV.SUMMARY_FLAG like 'Y'

and FVS.flex_value=TEMP_HALF.SEGMENT3

UNION

 SELECT FFVV.DESCRIPTION ACCONT,FFV.FLEX_VALUE,FFV.DESCRIPTION ACCOUNT_INDETAILS,TEMP_HALF.BALANCE,

--TO_CHAR(SUM(TEMP_HALF.BALANCE))  BALANCE,

TEMP_HALF.FLAG,

TEMP_HALF.PERIOD_NAME,

TEMP_HALF.PERIOD_YEAR,

TEMP_HALF.SEGMENT4,

FVS.SEGMENT_NAME,

FVS.DESCRIPTION FLEX_NAME

FROM

(SELECT SEGMENT6, DECODE (GB.ACTUAL_FLAG,'A','ACTUAL','BUDGET') FLAG,

 (SUM(BEGIN_BALANCE_DR) - SUM(BEGIN_BALANCE_CR)) + (SUM(PERIOD_NET_DR) -  SUM(PERIOD_NET_CR) ) BALANCE ,

  PERIOD_YEAR,

 PERIOD_NAME,

  SEGMENT4

 FROM GL_CODE_COMBINATIONS  GCC, GL_BALANCES GB

 WHERE --SEGMENT6 like '53%' AND

 GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

--  AND PERIOD_YEAR = TO_CHAR(SYSDATE,'YYYY') and period_name='Jul-07'

GROUP BY  PERIOD_YEAR,PERIOD_NAME,SEGMENT6,ACTUAL_FLAG,SEGMENT4--,SEGMENT2,SEGMENT3--,SEGMENT2,SEGMENT1

ORDER BY SEGMENT6 DESC)TEMP_HALF,

fnd_flex_values_vl  FFVV,

(SELECT F_SEG.FLEX_VALUE_SET_ID, F_SEG.SEGMENT_NAME SEGMENT_NAME,

        F_SEG.APPLICATION_COLUMN_NAME,FFV.FLEX_VALUE FLEX_VALUE,FFV.DESCRIPTION DESCRIPTION

 FROM fnd_flex_values_vl FFV,

     (select FLEX_VALUE_SET_ID,SEGMENT_NAME,APPLICATION_COLUMN_NAME

      from FND_ID_FLEX_SEGMENTS_VL where SEGMENT_NAME like 'BOG%'

      AND (ID_FLEX_NUM=50468) and (ID_FLEX_CODE='GL#') and (APPLICATION_ID=101)and (APPLICATION_COLUMN_NAME='SEGMENT4')

      order by application_id,id_flex_code,id_flex_num, decode(enabled_flag, 'Y', 1, 'N', 2),segment_num) F_SEG

WHERE           F_SEG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID) FVS,

(select  FFV.FLEX_VALUE ,FFV.DESCRIPTION,FFV.SUMMARY_FLAG,FND_TEMP.FLEX FLEX

 from fnd_flex_values_vl FFV,

      (select SUBSTR(FLEX_VALUE,1,2)||'_____' FV, --replace(FLEX_VALUE,0,'_') FV,

                      FLEX_VALUE flex   from fnd_flex_values_vl

       where FLEX_VALUE like '__00000' and  SUMMARY_FLAG like 'Y') FND_TEMP

              --   GL_CODE_COMBINATIONS  GCC

 where FFV.FLEX_VALUE like FND_TEMP.FV

  and  FFV.SUMMARY_FLAG = 'N' ) FFV

WHERE  FFV.flex_value=TEMP_HALF.SEGMENT6

and FFVV.FLEX_VALUE = FFV.FLEX and  FFVV.SUMMARY_FLAG like 'Y'

and FVS.flex_value=TEMP_HALF.SEGMENT4 

UNION

 SELECT FFVV.DESCRIPTION ACCONT,FFV.FLEX_VALUE,FFV.DESCRIPTION ACCOUNT_INDETAILS,TEMP_HALF.BALANCE,

--TO_CHAR(SUM(TEMP_HALF.BALANCE))  BALANCE,

TEMP_HALF.FLAG,

TEMP_HALF.PERIOD_NAME,

TEMP_HALF.PERIOD_YEAR,

TEMP_HALF.SEGMENT5,

FVS.SEGMENT_NAME,

FVS.DESCRIPTION FLEX_NAME

FROM

(SELECT SEGMENT6, DECODE (GB.ACTUAL_FLAG,'A','ACTUAL','BUDGET') FLAG,

 (SUM(BEGIN_BALANCE_DR) - SUM(BEGIN_BALANCE_CR)) + (SUM(PERIOD_NET_DR) -  SUM(PERIOD_NET_CR) ) BALANCE ,

  PERIOD_NAME,

  PERIOD_YEAR,

  SEGMENT5

 FROM GL_CODE_COMBINATIONS  GCC, GL_BALANCES GB

 WHERE --SEGMENT6 like '53%' AND

 GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

--  AND PERIOD_YEAR = TO_CHAR(SYSDATE,'YYYY') and period_name='Jul-07'

GROUP BY  PERIOD_YEAR,PERIOD_NAME,SEGMENT6,ACTUAL_FLAG,SEGMENT5--,SEGMENT2,SEGMENT3--,SEGMENT2,SEGMENT1

ORDER BY SEGMENT6 DESC)TEMP_HALF,

fnd_flex_values_vl  FFVV,

(SELECT F_SEG.FLEX_VALUE_SET_ID, F_SEG.SEGMENT_NAME SEGMENT_NAME,

        F_SEG.APPLICATION_COLUMN_NAME,FFV.FLEX_VALUE FLEX_VALUE,FFV.DESCRIPTION DESCRIPTION

 FROM fnd_flex_values_vl FFV,

     (select FLEX_VALUE_SET_ID,SEGMENT_NAME,APPLICATION_COLUMN_NAME

      from FND_ID_FLEX_SEGMENTS_VL where SEGMENT_NAME like 'BOG%'

      AND (ID_FLEX_NUM=50468) and (ID_FLEX_CODE='GL#') and (APPLICATION_ID=101)and (APPLICATION_COLUMN_NAME='SEGMENT5')

      order by application_id,id_flex_code,id_flex_num, decode(enabled_flag, 'Y', 1, 'N', 2),segment_num) F_SEG

WHERE           F_SEG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID) FVS,

(select  FFV.FLEX_VALUE ,FFV.DESCRIPTION,FFV.SUMMARY_FLAG,FND_TEMP.FLEX FLEX

 from fnd_flex_values_vl FFV,

      (select SUBSTR(FLEX_VALUE,1,2)||'_____' FV, --replace(FLEX_VALUE,0,'_') FV,

                      FLEX_VALUE flex   from fnd_flex_values_vl

       where FLEX_VALUE like '__00000' and  SUMMARY_FLAG like 'Y') FND_TEMP

              --   GL_CODE_COMBINATIONS  GCC

 where FFV.FLEX_VALUE like FND_TEMP.FV

  and  FFV.SUMMARY_FLAG = 'N' ) FFV

WHERE  FFV.flex_value=TEMP_HALF.SEGMENT6

and FFVV.FLEX_VALUE = FFV.FLEX and  FFVV.SUMMARY_FLAG like 'Y'

and FVS.flex_value=TEMP_HALF.SEGMENT5 

UNION

 SELECT FFVV.DESCRIPTION ACCONT,FFV.FLEX_VALUE,FFV.DESCRIPTION ACCOUNT_INDETAILS,TEMP_HALF.BALANCE,

--TO_CHAR(SUM(TEMP_HALF.BALANCE))  BALANCE,

TEMP_HALF.FLAG,

TEMP_HALF.PERIOD_NAME,

TEMP_HALF.PERIOD_YEAR,

TEMP_HALF.SEGMENT7,

FVS.SEGMENT_NAME,

FVS.DESCRIPTION FLEX_NAME

FROM

(SELECT SEGMENT6, DECODE (GB.ACTUAL_FLAG,'A','ACTUAL','BUDGET') FLAG,

 (SUM(BEGIN_BALANCE_DR) - SUM(BEGIN_BALANCE_CR)) + (SUM(PERIOD_NET_DR) -  SUM(PERIOD_NET_CR) ) BALANCE ,

PERIOD_NAME, 

PERIOD_YEAR,

  SEGMENT7

 FROM GL_CODE_COMBINATIONS  GCC, GL_BALANCES GB

 WHERE --SEGMENT6 like '53%' AND

 GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

--  AND PERIOD_YEAR = TO_CHAR(SYSDATE,'YYYY') and period_name='Jul-07'

GROUP BY  PERIOD_YEAR,PERIOD_NAME, SEGMENT6,ACTUAL_FLAG,SEGMENT7--,SEGMENT2,SEGMENT3--,SEGMENT2,SEGMENT1

ORDER BY SEGMENT6 DESC)TEMP_HALF,

fnd_flex_values_vl  FFVV,

(SELECT F_SEG.FLEX_VALUE_SET_ID, F_SEG.SEGMENT_NAME SEGMENT_NAME,

        F_SEG.APPLICATION_COLUMN_NAME,FFV.FLEX_VALUE FLEX_VALUE,FFV.DESCRIPTION DESCRIPTION

 FROM fnd_flex_values_vl FFV,

     (select FLEX_VALUE_SET_ID,SEGMENT_NAME,APPLICATION_COLUMN_NAME

      from FND_ID_FLEX_SEGMENTS_VL where SEGMENT_NAME like 'BOG%'

      AND (ID_FLEX_NUM=50468) and (ID_FLEX_CODE='GL#') and (APPLICATION_ID=101)and (APPLICATION_COLUMN_NAME='SEGMENT7')

      order by application_id,id_flex_code,id_flex_num, decode(enabled_flag, 'Y', 1, 'N', 2),segment_num) F_SEG

WHERE           F_SEG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID) FVS,

(select  FFV.FLEX_VALUE ,FFV.DESCRIPTION,FFV.SUMMARY_FLAG,FND_TEMP.FLEX FLEX

 from fnd_flex_values_vl FFV,

      (select SUBSTR(FLEX_VALUE,1,2)||'_____' FV, --replace(FLEX_VALUE,0,'_') FV,

                      FLEX_VALUE flex   from fnd_flex_values_vl

       where FLEX_VALUE like '__00000' and  SUMMARY_FLAG like 'Y') FND_TEMP

              --   GL_CODE_COMBINATIONS  GCC

 where FFV.FLEX_VALUE like FND_TEMP.FV

  and  FFV.SUMMARY_FLAG = 'N' ) FFV

WHERE  FFV.flex_value=TEMP_HALF.SEGMENT6

and FFVV.FLEX_VALUE = FFV.FLEX and  FFVV.SUMMARY_FLAG like 'Y'

and FVS.flex_value=TEMP_HALF.SEGMENT7

Open and Closed Issues

S. No	Issue	Description	Closed Date	Remarks
01.	N/A