Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Thursday, November 17, 2011

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
Best Blogger TemplatesBest Blogger Tips
Best Blogger Gadgets
Posted by Raju Chinthapatla at 11:11:00 PM
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect