/* MASS ALLOCATION FORMULA REVIEW SCRIPT
Written by Daniel North,  ORAFINAPPS Limited, Copyright 2007 
Will show the definition of mass allocation batches and lines across multiple books in an easy to read format for review in excel
It has been written for a 10 segment CoA but can be modified to suit different structures.
(Tested on Vision 11.5.10.2  June 2007 ) */
select fst.ID_FLEX_STRUCTURE_NAME"Chart of Accounts"
,	   gab.VALIDATION_STATUS "Valid?"
,	   gab.name "Allocation Name"
,	   gaf.name "Formula Name"
,	   gaf.full_allocation_flag"Full?"
--,	   gaf.validation_status"Valid?"
,	   gafl.line_number"Line #"
,	   DECODE(gafl.line_number,1,'A',2,'B',3,'C',4,'T',5,'O','XXX')"Line"
,	   gafl.AMOUNT"Amount"
,	   gafl.currency_code "Curr"
,	   gafl.SEGMENT1||'-'||gafl.SEGMENT2||'-'||gafl.SEGMENT3||'-'||gafl.SEGMENT4||'-'||gafl.SEGMENT5||'-'||gafl.SEGMENT6 
            ||'-'||gafl.SEGMENT7||'-'||gafl.SEGMENT8||'-'||gafl.SEGMENT9||'-'||gafl.SEGMENT10 "Account"
,	   gafl.segment_types_key "Segment"
,	   gafl.relative_period"Period"
--,	   gafl.transaction_currency"Curr"
,	   gafl.actual_flag"Actual?"
,	   gafl.amount_type"Amt Type"
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 ('BE','LU','ES')
order by 1,3,4,6