Query F.A.AA

select 

dp.period_name,

dp.fiscal_year,

dp.period_open_date,

dp.period_close_date,

bks.date_effective,

bks.date_ineffective,

bks.adjustment_required_status,

fab.asset_number,

fab.tag_number,

fat.description,

bks.date_placed_in_service,

cata.segment1,

cata.segment2,

cata.segment3,

fab.attribute1  Color,

fab.attribute2  Capacity,

fab.attribute3  EnginNo,

fab.attribute4  ChasisNo,

fab.serial_number,

fab.current_units,

bks.prorate_convention_code,

bks.deprn_method_code,

bks.life_in_months,

bks.cost,

ds.ytd_deprn       YTD,

ds.deprn_reserve   ACC_Deprn,

bks.cost-ds.deprn_reserve WVD

from

Fa_Additions_b fab,

Fa_Additions_Tl fat,

FA_Books        bks,

Fa_Categories_b cata,

Fa_Deprn_Summary  ds,

Fa_Deprn_Periods  dp

where

fab.asset_id=fat.asset_id       --relation link between  Fa_Additions_b fab and Fa_Additions_Tl fat,

and bks.asset_id=fab.asset_id   --relation link between  FA_Books bks and Fa_Additions_Tl fat

and cata.category_id=fab.asset_category_id --relation link between  Fa_Additions_b fab and Fa_Categories_b fab

and dp.period_counter=ds.period_counter --relation between Fa_Deprn_Summary  ds and Fa_Deprn_Periods  dp

and ds.asset_id=fab.asset_id            --relation betweeen Fa_Additions_b fab and Fa_Deprn_Summary  ds

and dp.period_name=&peridname--'NOV-14' --financial Period

and bks.adjustment_required_status='NONE'

--and fab.asset_number=21546

and cata.segment1=nvl(&MajorClassification,cata.segment1) --in(select distinct segment1 from Fa_Categories_b) --='OPR FA TAN FH'

and cata.segment2=nvl(&MajorCatagory,cata.segment2)-- in(select distinct segment2 from Fa_Categories_b)--='COMPUTER HARDWARE'--OR 'VEHICLES' --Major Category

and cata.segment3=nvl(&MinorCatagory,cata.segment3)-- in (select distinct segment3 from Fa_Categories_b)--'Note Books'       --OR'Motorcars' --Minor Category

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

How make project in Jdeveloper

WHTSTATEMETQ

select
c.check_number ,
c.check_date Payment_Date,
c.amount     Check_Amount,
c.bank_account_name,
c.doc_sequence_value BPV_NO,
pov.VENDOR_NAME_ALT,
pov.VAT_REGISTRATION_NUM  TAXFILE_NUM,

(select vv.description  from ap_invoices_all vv where vv.invoice_id
in(d.awt_invoice_id)
) WHT_INOVICE_DESCRIPTION,


(select vv.invoice_num  from ap_invoices_all vv where vv.invoice_id
in(d.awt_invoice_id)
) WHT_INOVICE_NUM,

v.invoice_id,
v.invoice_num,
v.invoice_date,
c.vendor_name,
c.address_line1 Vendor_Address,
pov.STANDARD_INDUSTRY_CLASS Vendor_NIC,
pov.NUM_1099 Vendor_NTN,
substr(tr.comments,1,instr(tr.comments,'-')-1) UNDER_SECTION,
substr(tr.comments,instr(tr.comments,'-')+1)   NATURE ,
v.invoice_amount,
tr.tax_rate,
v.invoice_amount-v.amount_paid TAX_AMOUNT,
v.amount_paid

--d.invoice_line_number,
--d.line_type_lookup_code,
--d.amount,

from
ap_invoices_all v,
ap_invoice_distributions_all d,
ap_awt_tax_rates_all tr,
po_vendors  pov,
ap_checks_all c,
ap_invoice_payments_all p

where
v.invoice_id=d.invoice_id
and tr.tax_rate_id=d.awt_tax_rate_id
and v.vendor_id=pov.VENDOR_ID
and p.invoice_id=v.invoice_id
and c.check_id=p.check_id
and pov.SEGMENT1=&Vendor_NO
and  d.line_type_lookup_code='AWT'
and nvl(d.reversal_flag,'N')='N'
and nvl(p.reversal_flag,'N')='N'
and c.check_date between &FDate and &TDate
-----------------------------------------------------------------------------------------------------------

selectc.doc_sequence_value BPV_NO,
v.invoice_id, 
v.invoice_num,
v.description,
' ' "WHT_INOVICE_NUM",
pov.vendor_name,
pov.vendor_name_alt,
c.address_line1 Vendor_Address,
c.city,
pov.num_1099 NTN,
pov.standard_industry_class NIC,
pov.vat_registration_num SaleTAX_REG_No,
' ' "Nature", 
 ' ' "SEC",
to_char(c.check_date, 'yyyymmdd') Payment_Date,
v.invoice_amount,
' ' "TAX_RATE",
 0 TAX_AMOUNT,
 sum(p.amount) AMOUNT_PAID,
to_char(c.check_date, 'yyyymmdd') Check_Date,
c.check_number,
p.reversal_flag
from 
ap_checks_all c,
ap_invoices_all v,
ap_invoice_payments_all p,
po_vendors pov
where 
c.check_id=p.check_id
and v.invoice_id=p.invoice_id
 and pov.vendor_id=v.vendor_id
 and pov.SEGMENT1=&VendorNo
 and c.check_date between &FromDate and &ToDate
 and p.reversal_flag='N'
 
and v.invoice_amount=v.amount_paid
 and v.cancelled_date is null
 
Group by v.invoice_id,c.doc_sequence_value,v.invoice_num,pov.vendor_name,pov.vendor_name_alt,c.city,
pov.num_1099,pov.standard_industry_class,pov.vat_registration_num,c.check_date,v.invoice_amount,c.check_number,v.description,c.address_line1,p.reversal_flagUNION ALL
 select 
r.BPV_NO,
r.invoice_id,
r.invoice_num,
r.WHT_INOVICE_DESCRIPTION,
r.WHT_INOVICE_NUM,
r.Vendor_Name,
r.VENDOR_NAME_ALT, 
r.Vendor_Address,
r.City,
r.Vendor_NTN,
r.Vendor_NIC,
r.TAXFILE_NUM,
r.NATURE,
r.UNDER_SECTION,
to_char(r.Payment_Date,'yyyymmdd') Payment_Date,
r.INVOICE_AMOUNT,
r.TAX_RATE,
r.TAX_AMOUNT,
r.AMOUNT_PAID,
to_char(r.Check_Date,'yyyymmdd')Check_Date,
r.check_No,
r.Payment_Reversal_Flagfrom(
 select 
　
(select cc.doc_sequence_value from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))BPV_NO, 
 --v.invoice_id ,--v.invoice_num,--(select vv.description from ap_invoices_all vv where vv.invoice_id
 in(d.awt_invoice_id)
) WHT_INOVICE_DESCRIPTION,--(select vv.invoice_num from ap_invoices_all vv where vv.invoice_id
 in(d.awt_invoice_id)
) WHT_INOVICE_NUM,--(select cc.vendor_name from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))Vendor_Name,
 --pov.VENDOR_NAME,pov.VENDOR_NAME_ALT,--(select cc.address_line1 from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))Vendor_Address, 
 --(select cc.city from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))City, 
 ---pov.NUM_1099 Vendor_NTN,
pov.STANDARD_INDUSTRY_CLASS Vendor_NIC,
pov.VAT_REGISTRATION_NUM TAXFILE_NUM,
substr(tr.comments,instr(tr.comments,'-')+1) NATURE ,
substr(tr.comments,1,instr(tr.comments,'-')-1) UNDER_SECTION,
 --(select cc.check_date from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))Payment_Date, 
 ---1*d.amount/(tr.tax_rate/100) INVOICE_AMOUNT,
to_char(tr.tax_rate) TAX_RATE,--v.invoice_amount-v.amount_paid TAX_AMOUNT,to_number(-1*d.amount) TAX_AMOUNT,
 --(select pp.amount from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id)
) AMOUNT_PAID, --Payment of AWT_Inovice
-- 　
(select cc.check_date from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))Check_Date,
 --(select cc.check_number from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))check_No,
　
　
(select pp.reversal_flag from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))Payment_Reversal_Flag 
 --to_char(c.check_date, 'yyyymmdd'), fromap_invoices_all v,
ap_invoice_distributions_all d,
ap_awt_tax_rates_all tr,
po_vendors povwhere 
v.invoice_id=d.invoice_idandtr.tax_rate_id=d.awt_tax_rate_id
 andv.vendor_id=pov.VENDOR_ID
 andpov.SEGMENT1=&VendorNo
 --and tr.tax_name=&Tax_Name --comment for tax exemptionandd.line_type_lookup_code='AWT' 
 andnvl(d.reversal_flag,'N')='N'
 )rwhereCheck_Date between &FromDate and &ToDate
 andnvl(Payment_Reversal_Flag,'N') ='N'