Mass Allocation Formula review script

GL : Mass Allocation Formula review script:-
---------------------------------------------------

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.

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

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 ('HB')
order by 1,3,4,6

Read More

Period Status script

GL : Period Status script:-
-----------------------------

GL PERIOD STATUSES

Two small scripts for reviewing open periods across multipel books. ( Month end close checking or automated alerts )
and period status for a given year and book.

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

select sob.short_name
, ps.period_name
, ps.show_status
, ps.start_date||' to '||ps.end_date
, ps.period_year
, ps.period_num
from GL_PERIOD_STATUSES_V ps, GL_SETS_OF_BOOKS sob
where ps.set_of_Books_id = sob.SET_OF_BOOKS_ID
and application_id = 101
--and period_year = 2008
--and substr(sob.short_name,1,2) in ('hb')
and ps.show_status not in ('Never Opened')
order by 1,5,6 desc

Second Script

select sob.short_name
, ps.period_name
, ps.start_date
, ps.end_date
, ps.period_year
, ps.period_num
, ps.show_status
from GL_PERIOD_STATUSES_V ps, GL_SETS_OF_BOOKS sob
where ps.set_of_Books_id = sob.SET_OF_BOOKS_ID
and application_id = 101
and period_year = 2006
--and substr(sob.short_name,1,2) in ('hb')
order by 1,5,6 desc

Read More