MASS ALLOCATION MIGRATION - DATALOAD CLASSIC LAYOUT

/*
MASS ALLOCATION MIGRATION - DATALOAD CLASSIC LAYOUT

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 */

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