add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, November 17

EXTRACT MASS ALLOCATIONS INTO A DATALOAD Format 

/*  EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS
 This is designed to work with a 10 segment chart of accounts, so will need to be modified to suit your structure
 This extract will only work with the following conditions
 1- That lines B&C are accounts rather than values.  If values are used then use the second extract below.
 2- That relative period is current
 3- That amount type is Actual
 */
select gab.NAME
,    gaf.name "Formula Name"
,    'Allocation'
,    gaf.description "Formula Desc"
,    gafl.SEGMENT1"S11"
,    substr(gafl.SEGMENT_TYPES_KEY,1,1)"T"
,    gafl.SEGMENT2"S12"
,    substr(gafl.SEGMENT_TYPES_KEY,3,1)"T"
,    gafl.SEGMENT3"S13"
,    substr(gafl.SEGMENT_TYPES_KEY,5,1)"T"
,    gafl.SEGMENT4"S14"
,    substr(gafl.SEGMENT_TYPES_KEY,7,1)"T"
,    gafl.SEGMENT5"S15"
,    substr(gafl.SEGMENT_TYPES_KEY,9,1)"T"
,    gafl.SEGMENT6"S16"
,    substr(gafl.SEGMENT_TYPES_KEY,11,1)"T"
,    gafl.SEGMENT7"S17"
,    substr(gafl.SEGMENT_TYPES_KEY,13,1)"T"
,    gafl.SEGMENT8"S18"
,    substr(gafl.SEGMENT_TYPES_KEY,15,1)"T"
,    gafl.SEGMENT9"S19"
,    substr(gafl.SEGMENT_TYPES_KEY,17,1)"T"
,    gafl.SEGMENT10"S110"
,    substr(gafl.SEGMENT_TYPES_KEY,19,1)"T"
,    gafl.CURRENCY_CODE"Curr"
,    gafl.amount_type"Amt Type"
,    gafl2.SEGMENT1"S21"
,    substr(gafl2.SEGMENT_TYPES_KEY,1,1)"T"
,    gafl2.SEGMENT2"S22"
,    substr(gafl2.SEGMENT_TYPES_KEY,3,1)"T"
,    gafl2.SEGMENT3"S23"
,    substr(gafl2.SEGMENT_TYPES_KEY,5,1)"T"
,    gafl2.SEGMENT4"S24"
,    substr(gafl2.SEGMENT_TYPES_KEY,7,1)"T"
,    gafl2.SEGMENT5"S25"
,    substr(gafl2.SEGMENT_TYPES_KEY,9,1)"T"
,    gafl2.SEGMENT6"S26"
,    substr(gafl2.SEGMENT_TYPES_KEY,11,1)"T"
,    gafl2.SEGMENT7"S27"
,    substr(gafl2.SEGMENT_TYPES_KEY,13,1)"T"
,    gafl2.SEGMENT8"S28"
,    substr(gafl2.SEGMENT_TYPES_KEY,15,1)"T"
,    gafl2.SEGMENT9"S29"
,    substr(gafl2.SEGMENT_TYPES_KEY,17,1)"T"
,    gafl2.SEGMENT10"S210"
,    substr(gafl2.SEGMENT_TYPES_KEY,19,1)"T"
,    gafl2.CURRENCY_CODE"Curr"
,    gafl2.amount_type"Amt Type"
,    gafl3.SEGMENT1"S31"
,    substr(gafl3.SEGMENT_TYPES_KEY,1,1)"T"
,    gafl3.SEGMENT2"s32"
,    substr(gafl3.SEGMENT_TYPES_KEY,3,1)"T"
,    gafl3.SEGMENT3"s33"
,    substr(gafl3.SEGMENT_TYPES_KEY,5,1)"T"
,    gafl3.SEGMENT4"s34"
,    substr(gafl3.SEGMENT_TYPES_KEY,7,1)"T"
,    gafl3.SEGMENT5"s35"
,    substr(gafl3.SEGMENT_TYPES_KEY,9,1)"T"
,    gafl3.SEGMENT6"s36"
,    substr(gafl3.SEGMENT_TYPES_KEY,11,1)"T"
,    gafl3.SEGMENT7"s37"
,    substr(gafl3.SEGMENT_TYPES_KEY,13,1)"T"
,    gafl3.SEGMENT8"s38"
,    substr(gafl3.SEGMENT_TYPES_KEY,15,1)"T"
,    gafl3.SEGMENT9"s39"
,    substr(gafl3.SEGMENT_TYPES_KEY,17,1)"T"
,    gafl3.SEGMENT10"s310"
,    substr(gafl3.SEGMENT_TYPES_KEY,19,1)"T"
,    gafl3.CURRENCY_CODE"Curr"
,    gafl3.amount_type"Amt Type"
,    gafl4.SEGMENT1"S41"
,    substr(gafl4.SEGMENT_TYPES_KEY,1,1)"T"
,    gafl4.SEGMENT2"S42"
,    substr(gafl4.SEGMENT_TYPES_KEY,3,1)"T"
,    gafl4.SEGMENT3"S43"
,    substr(gafl4.SEGMENT_TYPES_KEY,5,1)"T"
,    gafl4.SEGMENT4"S44"
,    substr(gafl4.SEGMENT_TYPES_KEY,7,1)"T"
,    gafl4.SEGMENT5"S45"
,    substr(gafl4.SEGMENT_TYPES_KEY,9,1)"T"
,    gafl4.SEGMENT6"S46"
,    substr(gafl4.SEGMENT_TYPES_KEY,11,1)"T"
,    gafl4.SEGMENT7"S47"
,    substr(gafl4.SEGMENT_TYPES_KEY,13,1)"T"
,    gafl4.SEGMENT8"S48"
,    substr(gafl4.SEGMENT_TYPES_KEY,15,1)"T"
,    gafl4.SEGMENT9"S49"
,    substr(gafl4.SEGMENT_TYPES_KEY,17,1)"T"
,    gafl4.SEGMENT10"S410"
,    substr(gafl4.SEGMENT_TYPES_KEY,19,1)"T"
,    gafl4.CURRENCY_CODE"Curr"
,    gafl5.SEGMENT1"S51"
,    substr(gafl5.SEGMENT_TYPES_KEY,1,1)"T"
,    gafl5.SEGMENT2"S52"
,    substr(gafl5.SEGMENT_TYPES_KEY,3,1)"T"
,    gafl5.SEGMENT3"S53"
,    substr(gafl5.SEGMENT_TYPES_KEY,5,1)"T"
,    gafl5.SEGMENT4"S54"
,    substr(gafl5.SEGMENT_TYPES_KEY,7,1)"T"
,    gafl5.SEGMENT5"S55"
,    substr(gafl5.SEGMENT_TYPES_KEY,9,1)"T"
,    gafl5.SEGMENT6"S56"
,    substr(gafl5.SEGMENT_TYPES_KEY,11,1)"T"
,    gafl5.SEGMENT7"S57"
,    substr(gafl5.SEGMENT_TYPES_KEY,13,1)"T"
,    gafl5.SEGMENT8"S58"
,    substr(gafl5.SEGMENT_TYPES_KEY,15,1)"T"
,    gafl5.SEGMENT9"S59"
,    substr(gafl5.SEGMENT_TYPES_KEY,17,1)"T"
,    gafl5.SEGMENT10"S510"
,    substr(gafl5.SEGMENT_TYPES_KEY,19,1)"T"
from GL_ALLOC_BATCHES gab, GL_ALLOC_FORMULAS gaf, GL_ALLOC_FORMULA_LINES gafl,GL_ALLOC_FORMULA_LINES gafl2,GL_ALLOC_FORMULA_LINES gafl3
  ,GL_ALLOC_FORMULA_LINES gafl4,GL_ALLOC_FORMULA_LINES gafl5
  , 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 gaf.allocation_formula_id = gafl2.allocation_formula_id
and gaf.allocation_formula_id = gafl3.allocation_formula_id
and gaf.allocation_formula_id = gafl4.allocation_formula_id
and gaf.allocation_formula_id = gafl5.allocation_formula_id
and gafl.LINE_NUMBER =1
and gafl2.LINE_NUMBER =2
and gafl3.LINE_NUMBER =3
and gafl4.LINE_NUMBER =4
and gafl5.LINE_NUMBER =5
--and substr(fst.ID_FLEX_STRUCTURE_CODE,1,2) in ('DE')
and gafl2.AMOUNT is null
--and gab.NAME like 'DE Main%'
order by 1,2



---==========================================================================================================================
/*  EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS
 Written by Daniel North,  ORAFINAPPS Limited, Copyright 2007 
 It is designed to work with a 10 segment chart of accounts, so will need to be modified to suit your structure
 This extract will only work with the following conditions
 1- That lines B&C are VALUES NOT ACCOUNTS  
 2- That relative period is current
 3- That amount type is Actual
 */
select gab.NAME
,    gaf.name "Formula Name"
,    'Allocation'
,    gaf.description "Formula Desc"
,    gafl.SEGMENT1"S11"
,    substr(gafl.SEGMENT_TYPES_KEY,1,1)"T"
,    gafl.SEGMENT2"S12"
,    substr(gafl.SEGMENT_TYPES_KEY,3,1)"T"
,    gafl.SEGMENT3"S13"
,    substr(gafl.SEGMENT_TYPES_KEY,5,1)"T"
,    gafl.SEGMENT4"S14"
,    substr(gafl.SEGMENT_TYPES_KEY,7,1)"T"
,    gafl.SEGMENT5"S15"
,    substr(gafl.SEGMENT_TYPES_KEY,9,1)"T"
,    gafl.SEGMENT6"S16"
,    substr(gafl.SEGMENT_TYPES_KEY,11,1)"T"
,    gafl.SEGMENT7"S17"
,    substr(gafl.SEGMENT_TYPES_KEY,13,1)"T"
,    gafl.SEGMENT8"S18"
,    substr(gafl.SEGMENT_TYPES_KEY,15,1)"T"
,    gafl.SEGMENT9"S19"
,    substr(gafl.SEGMENT_TYPES_KEY,17,1)"T"
,    gafl.SEGMENT10"S110"
,    substr(gafl.SEGMENT_TYPES_KEY,19,1)"T"
,    gafl.CURRENCY_CODE"Curr"
,    gafl.amount_type"Amt Type"
,    gafl2.AMOUNT "B-Amt"
,    gafl3.AMOUNT "C-Amt"
,    gafl4.SEGMENT1"S41"
,    substr(gafl4.SEGMENT_TYPES_KEY,1,1)"T"
,    gafl4.SEGMENT2"S42"
,    substr(gafl4.SEGMENT_TYPES_KEY,3,1)"T"
,    gafl4.SEGMENT3"S43"
,    substr(gafl4.SEGMENT_TYPES_KEY,5,1)"T"
,    gafl4.SEGMENT4"S44"
,    substr(gafl4.SEGMENT_TYPES_KEY,7,1)"T"
,    gafl4.SEGMENT5"S45"
,    substr(gafl4.SEGMENT_TYPES_KEY,9,1)"T"
,    gafl4.SEGMENT6"S46"
,    substr(gafl4.SEGMENT_TYPES_KEY,11,1)"T"
,    gafl4.SEGMENT7"S47"
,    substr(gafl4.SEGMENT_TYPES_KEY,13,1)"T"
,    gafl4.SEGMENT8"S48"
,    substr(gafl4.SEGMENT_TYPES_KEY,15,1)"T"
,    gafl4.SEGMENT9"S49"
,    substr(gafl4.SEGMENT_TYPES_KEY,17,1)"T"
,    gafl4.SEGMENT10"S410"
,    substr(gafl4.SEGMENT_TYPES_KEY,19,1)"T"
,    gafl4.CURRENCY_CODE"Curr"
,    gafl5.SEGMENT1"S51"
,    substr(gafl5.SEGMENT_TYPES_KEY,1,1)"T"
,    gafl5.SEGMENT2"S52"
,    substr(gafl5.SEGMENT_TYPES_KEY,3,1)"T"
,    gafl5.SEGMENT3"S53"
,    substr(gafl5.SEGMENT_TYPES_KEY,5,1)"T"
,    gafl5.SEGMENT4"S54"
,    substr(gafl5.SEGMENT_TYPES_KEY,7,1)"T"
,    gafl5.SEGMENT5"S55"
,    substr(gafl5.SEGMENT_TYPES_KEY,9,1)"T"
,    gafl5.SEGMENT6"S56"
,    substr(gafl5.SEGMENT_TYPES_KEY,11,1)"T"
,    gafl5.SEGMENT7"S57"
,    substr(gafl5.SEGMENT_TYPES_KEY,13,1)"T"
,    gafl5.SEGMENT8"S58"
,    substr(gafl5.SEGMENT_TYPES_KEY,15,1)"T"
,    gafl5.SEGMENT9"S59"
,    substr(gafl5.SEGMENT_TYPES_KEY,17,1)"T"
,    gafl5.SEGMENT10"S510"
,    substr(gafl5.SEGMENT_TYPES_KEY,19,1)"T"
from GL_ALLOC_BATCHES gab, GL_ALLOC_FORMULAS gaf, GL_ALLOC_FORMULA_LINES gafl,GL_ALLOC_FORMULA_LINES gafl2,GL_ALLOC_FORMULA_LINES gafl3
  ,GL_ALLOC_FORMULA_LINES gafl4,GL_ALLOC_FORMULA_LINES gafl5
  , 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 gaf.allocation_formula_id = gafl2.allocation_formula_id
and gaf.allocation_formula_id = gafl3.allocation_formula_id
and gaf.allocation_formula_id = gafl4.allocation_formula_id
and gaf.allocation_formula_id = gafl5.allocation_formula_id
and gafl.LINE_NUMBER =1
and gafl2.LINE_NUMBER =2
and gafl3.LINE_NUMBER =3
and gafl4.LINE_NUMBER =4
and gafl5.LINE_NUMBER =5
and substr(fst.ID_FLEX_STRUCTURE_CODE,1,2) in ('DE')
and gafl2.AMOUNT is not null
--and gab.NAME like 'DE Main%'
order by 1
Posted by Jithendra Kumar B
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)