Saturday, 9 November 2013

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

No comments:

Post a Comment