/* MASS ALLOCATION MIGRATION - DATALOAD CLASSIC LAYOUT
Written by Daniel North,  ORAFINAPPS Limited, Copyright 2007 
Creates a pre-formated spreadsheet layout to migrate mass allocations between environments and/or books using dataload classic.
It has been written for a 10 segment CoA but can be modified to suit different structures.
(Tested on Vision 11.5.9 Jan-2007 ) */
select substr(fst.ID_FLEX_STRUCTURE_CODE,1,2)"Book"
,	   gab.name "Allocation Name"
/*,	(case when gafl.line_number = 1 then  gab.name  else NULL end )"Allocation Name"
,	(case when gafl.line_number = 1 then  'TAB' else NULL end )"TAB"
,	(case when gafl.line_number = 1 then 'A' else NULL end )"A"
,	(case when gafl.line_number = 1 then   'TAB' else NULL end )"TAB"
,	(case when gafl.line_number = 1 then  gab.description  else NULL end )"Alloc Description"
,	(case when gafl.line_number = 1 then   '*AR' else NULL end )"TAB"
,*/	
,	(case when gafl.line_number = 1 then  '\'||gaf.name  else NULL end )"Formula Name"
,	(case when gafl.line_number = 1 then 'TAB' else NULL end )"TAB"
,	(case when gafl.line_number = 1 then 'Allocation' else NULL end )"Allocation"
,	(case when gafl.line_number = 1 then 'TAB' else NULL end )"TAB"
,	(case when gafl.line_number = 1 then  gaf.description  else NULL end )"Formula Desc"
,	(case when gafl.line_number = 1 then 'TAB' else NULL end )"TAB"
,	(case when gafl.line_number = 1 then 'TAB' else NULL end )"TAB"
,	(case when gafl.line_number = 1 then '*SB' else NULL end )"FCP"
,	(case when gafl.line_number = 1 then 'TAB' else NULL end )"TAB"
,	   (case when gafl.amount is null then (case when gafl.line_number in (1,2,3,4) then 'TAB' else NULL end )else null end )"TAB"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT1 else '\'||to_char(gafl.amount) end )"1"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,0,1) else null end )"1t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT2 else null end )"2"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,3,1)else null end )"2t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT3 else null end )"3"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,5,1)else null end )"3t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT4 else null end )"4"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,7,1)else null end )"4t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT5 else null end )"5"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,9,1)else null end )"5t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT6 else null end )"6"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,11,1)else null end )"6t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT7 else null end )"7"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,13,1)else null end )"7t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT8 else null end )"8"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,15,1)else null end )"8t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT9 else null end )"9"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,17,1)else null end )"9t"
,	   (case when gafl.amount is null then '\'||gafl.SEGMENT10 else null end )"10"
,	   (case when gafl.amount is null then '\'||substr(gafl.segment_types_key,19,1)else null end )"10t"
,	(case when gafl.amount is null then(case when gafl.line_number in (1,2,3,4,5) then 'ENT' else NULL end )else null end )"TAB1"
,	(case when gafl.amount is null then(case when gafl.line_number in (1,2,3,4) then gafl.CURRENCY_CODE else NULL end )else null end )"Curr"
,	(case when gafl.amount is null then(case when gafl.line_number in (1,2,3,4) then 'TAB' else null end )else null end)"TAB2"
,	(case when gafl.amount is not null then(case when gafl.line_number in (2) then 'TAB' else null end )else null end)"TAB2"
,	(case when gafl.amount is null then(case when gafl.line_number in (1,2,3) then gafl.AMOUNT_TYPE else null end )else null end )"PTD/YTD"
,	(case when gafl.amount is null then(case when gafl.line_number in (1,2) then '\{TAB 3}' else (case when gafl.line_number in (3) then '\{TAB 2}' else null end) end)else null end )"TAB3"
,	(case when gafl.amount is null then(case when gafl.line_number in (5) then '*SAVE' else null end )else null end )"*SAVE"
,	(case when gafl.amount is null then(case when gafl.line_number in (5) then '*PB' else null end )else null end )"*PB"
,	(case when gafl.amount is null then(case when gafl.line_number in (5) then '*NR' else null end )else null end )"*NR"
from GL_ALLOC_BATCHES gab, GL_ALLOC_FORMULAS gaf, GL_ALLOC_FORMULA_LINES gafl, FND_ID_FLEX_STRUCTURES_VL fst
where gab.allocation_batch_id = gaf.allocation_batch_id
and gab.CHART_OF_ACCOUNTS_ID = fst.id_flex_num
and gaf.allocation_formula_id = gafl.allocation_formula_id
--and substr(fst.ID_FLEX_STRUCTURE_CODE,1,2) in ('DE')
order by 1,gab.name, gaf.name, gafl.line_number