Example Query to Parent_Child_BC

select  B.*,C.* from

(select

gcc.template_id, 

gcc.code_combination_id,

gcc.segment5 Parent_Account_Names,

(select t.description from gl_summary_templates t

where  t.template_id=gcc.template_id)Roll_UP_NAMES,

(select t.template_name from gl_summary_templates t

where  t.template_id=gcc.template_id) Template_Name

from gl_code_combinations gcc 

where gcc.template_id in(select t.template_id from gl_summary_templates t

where  t.template_name like 'ABC%'))B,

(select

glcc.template_id, 

glcc.code_combination_id,

w_dev_pkg.GET_PRODUCT_DESC(glcc.code_combination_id),

nvl((select jel.accounted_dr  BudgetAmount

from 

gl_je_lines     jel, 

gl_je_headers   jeh

where

jel.je_header_id=jeh.je_header_id  

and jel.CODE_COMBINATION_ID=glcc.code_combination_id 

and jel.PERIOD_NAME=&PeriodName

and jeh.je_source='Budget Journal'),0)BudgetAmount,

--

(select 

sum(l.entered_dr)-sum(l.entered_cr) 

from 

gl_je_lines l,

gl_je_headers h                                    

where 

l.je_header_id=h.je_header_id

and l.code_combination_id in  (select gca.code_combination_id from

gl_code_combinations      gca,

fnd_flex_value_children_v fvc

where

gca.segment5=fvc.flex_value

and fvc.flex_value in (select ccc.flex_value from fnd_flex_value_children_v  ccc  where ccc.Parent_flex_value=&Account_Seg)) 

--=glcc.code_combination_id 

and l.status='P'

and l.period_name=&PeriodName

and h.je_source!='Budget Journal' 

group by l.period_name)Actual_Balance,

----

sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

  where bc.code_combination_id =glcc.code_combination_id --14864

   and bc.period_name =&PeriodName

   and bc.status_code = 'A'))/(count(1)) Total_Encumbrance,

----------

sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

  where bc.code_combination_id =glcc.code_combination_id --14864

   and bc.period_name =&PeriodName

   and bc.je_category_name = 'Purchase Invoices'

   and bc.status_code = 'A'))/(count(1)) Invoice_Encumberance,

----------

sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

  where bc.code_combination_id =glcc.code_combination_id --14864

   and bc.period_name =&PeriodName-- 'JUL-13'

   and (bc.je_category_name = 'Purchases' or  bc.je_category_name = 'Purchase Invoices')

   and bc.status_code = 'A'))/(count(1)) Purchase_Order_Encumberance,

-------------   

 sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

where bc.code_combination_id =glcc.code_combination_id --14864

   and bc.period_name =&PeriodName-- 'JUL-13'

   and (bc.je_category_name ='Requisitions')

   and bc.status_code = 'A'))/(count(1)) Requisition_Encumberance,

----------------------

(nvl((select jel.accounted_dr  

from 

gl_je_lines     jel, 

gl_je_headers   jeh

where

jel.je_header_id=jeh.je_header_id  

and jel.CODE_COMBINATION_ID=glcc.code_combination_id 

and jel.PERIOD_NAME=&PeriodName--'JUL-13'

and jeh.je_source='Budget Journal'),0)-(select 

sum(l.entered_dr)-sum(l.entered_cr) 

from 

gl_je_lines l,

gl_je_headers h                                    

where 

l.je_header_id=h.je_header_id

and l.code_combination_id in  (select gca.code_combination_id from

gl_code_combinations      gca,

fnd_flex_value_children_v fvc

where

gca.segment5=fvc.flex_value

and fvc.flex_value in (select ccc.flex_value from fnd_flex_value_children_v  ccc  where ccc.Parent_flex_value=&Account_Seg))

--=glcc.code_combination_id 

and l.status='P'

and l.period_name=&PeriodName

and h.je_source!='Budget Journal' 

group by l.period_name)-sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

  where bc.code_combination_id =glcc.code_combination_id --14864

   and bc.period_name =&PeriodName

   and bc.status_code = 'A'))/(count(1)))Fund_Available,

glcc.segment5,

glcc.segment2,

glcc.segment3,

glcc.segment4,

glcc.segment1,

glcc.segment6,

glcc.segment7,

glcc.segment8,

glcc.segment9

from gl_code_combinations  glcc

where

glcc.code_combination_id in (select  gcc.code_combination_id from 

           gl_code_combinations gcc

                             where

                        gcc.segment1='T'--&Company_Seg         

                        and gcc.segment2='T'--&Business_Seg      

                        and gcc.segment3='T'--&CostCenter_Seg        

                        and gcc.segment4='T'--&Department_Seg      

                        and gcc.segment5=&Account_Seg--Child accounts no required

                        and gcc.segment6='T'

                        and gcc.segment7='T'

                        and gcc.segment8='T'

                          and gcc.segment9='T')

                            

                            

 group by glcc.code_combination_id,w_dev_pkg.GET_PRODUCT_DESC(glcc.code_combination_id),

 glcc.segment5,

glcc.segment2,

glcc.segment3,

glcc.segment4,

glcc.segment1,

glcc.segment6,

glcc.segment7,

glcc.segment8,

glcc.segment9,

glcc.template_id                           

) C 

where   

C.code_combination_id=B.code_combination_id--A.detail_code_combination_id
-------------------------------------------------------------------------------------------------------
new One

select  

B.*,

C.*,

C.Total_Encumbrance,

bal.period_net_dr-bal.period_net_cr Actual, 

C.BudgetAmount-(C.Total_Encumbrance +(bal.period_net_dr-bal.period_net_cr)) Funds_Available 

from

gl_balances bal, --code_combination present in this Table is equal to Code_combination in Table B and Table C used in joins

--Table B

(select

gcc.template_id, 

gcc.code_combination_id,

gcc.segment5 Parent_Account_Names,

(select t.description from gl_summary_templates t

where  t.template_id=gcc.template_id)Roll_UP_NAMES,

(select t.template_name from gl_summary_templates t

where  t.template_id=gcc.template_id) Template_Name

from gl_code_combinations gcc 

where gcc.template_id in(select t.template_id from gl_summary_templates t

where  t.template_name like &departments))B,

--Table C

(select

glcc.template_id, 

glcc.code_combination_id,--This glcc.code_combination_id is used column queries of table C

w_dev_pkg.GET_PRODUCT_DESC(glcc.code_combination_id),

(select b.period_net_dr 

from gl_balances  b 

where b.code_combination_id=glcc.code_combination_id  and b.period_name='JUL-12' and b.actual_flag='B') BudgetAmount,

----

nvl(sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

  where bc.code_combination_id =glcc.code_combination_id 

   and bc.period_name =&PeriodName-- 'JUL-13'

   and bc.status_code = 'A')),0)/(count(1)) Total_Encumbrance,

----------

nvl(sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

  where bc.code_combination_id =glcc.code_combination_id 

   and bc.period_name =&PeriodName-- 'JUL-13'

   and bc.je_category_name = 'Purchase Invoices'

   and bc.status_code = 'A')),0)/(count(1)) Invoice_Encumberance,

----------

nvl(sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

  where bc.code_combination_id =glcc.code_combination_id 

   and bc.period_name =&PeriodName-- 'JUL-13'

   and (bc.je_category_name = 'Purchases' or  bc.je_category_name = 'Purchase Invoices')

   and bc.status_code = 'A')),0)/(count(1)) Purchase_Order_Encumberance,

-------------   

 nvl(sum((select sum(bc.entered_dr)-sum(bc.entered_cr)

  from gl_bc_packets bc

where bc.code_combination_id =glcc.code_combination_id 

   and bc.period_name =&PeriodName-- 'JUL-13'

   and (bc.je_category_name ='Requisitions')

   and bc.status_code = 'A')),0)/(count(1)) Requisition_Encumberance,

----------------------

glcc.segment5,

glcc.segment2,

glcc.segment3,

glcc.segment4,

glcc.segment1,

glcc.segment6,

glcc.segment7,

glcc.segment8,

glcc.segment9

from

gl_code_combinations  glcc --This glcc.code_combination_id is used in above column Quries of table C

where                      --glcc.code_combination_id is the main code combination that can be used in select statments to make

                           --link with tables which has code combiation column with the main upper select statment to make columns

                           --for example gl_bc_packets as show above.

glcc.code_combination_id in (select  gcc.code_combination_id from 

           gl_code_combinations gcc

                             where

                        gcc.segment1='T'        

                        and gcc.segment2='T'       

                        and gcc.segment3='T'         

                        and gcc.segment4='T'       

                        and gcc.segment5 in(select gcc.segment5 Parent_Account_Names from gl_code_combinations gcc where gcc.template_id in(select t.template_id from gl_summary_templates t where  t.template_name like &departments))         

                        and gcc.segment6='T'--'0000'

                        and gcc.segment7='T'--'00'

                        and gcc.segment8='T'--'000'

                          and gcc.segment9='T')

                            

                            

group by glcc.code_combination_id,w_dev_pkg.GET_PRODUCT_DESC(glcc.code_combination_id),

glcc.segment5,

glcc.segment2,

glcc.segment3,

glcc.segment4,

glcc.segment1,

glcc.segment6,

glcc.segment7,

glcc.segment8,

glcc.segment9,

glcc.template_id                           

) C

where   

C.code_combination_id=B.code_combination_id  

and bal.code_combination_id=C.code_combination_id

and bal.period_name=&PeriodName

and bal.actual_flag='A'

order by C.code_combination_id