/* EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS Written by Daniel North, ORAFINAPPS Limited, Copyright 2007 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