Oracle Mass allocation tables

Oracle Mass allocation tables

In this post , we will be discuss about the oracle mass allocation tables which helps to store the mass allocation informations in oracle apps. In these tables we do gets the , Oracle mass allocation batch informations , Mass allocation formulas which we have created for the automate journals. We can use these tables to create the sql scripts to extract and verify the mass allocation formulas for the automate journals creations. Here below i will share all these Oracle mass allocation tables and detail sql script using the mass allocation tables.

3 Important Oracle Mass allocation tables in R12

1.GL_ALLOC_BATCHES
2.GL_ALLOC_FORMULAS
3.GL_ALLOC_FORMULA_LINES

Detail SQL Script using the Oracle Mass allocation tables in r12.

Here below is the complete sql script , which helps to extract all GL Mass allocations informations in oracle apps r12. Using this below script , we can verify the Mass allocation formulas in oracle apps r12.

select fifsv.ID_FLEX_STRUCTURE_NAME "COA"
, gl_alloc_batch.VALIDATION_STATUS
, gl_alloc_batch.name "Allocation Name"
, gl_alloc_f.name "Formula Name"
, gl_alloc_f_lines.line_number"Line #"
, DECODE(gl_alloc_f_lines.line_number,1,'A',2,'B',3,'C',4,'T',5,'O','XXX')"Line"
, gl_alloc_f_lines.AMOUNT"Amount"
, gl_alloc_f_lines.currency_code "Curr"
, gl_alloc_f_lines.SEGMENT1||'-'||gl_alloc_f_lines.SEGMENT2||'-'||gl_alloc_f_lines.SEGMENT3||'-'||gl_alloc_f_lines.SEGMENT4||'-'||gl_alloc_f_lines.SEGMENT5||'-'||gl_alloc_f_lines.SEGMENT6
||'-'||gl_alloc_f_lines.SEGMENT7||'-'||gl_alloc_f_lines.SEGMENT8||'-'||gl_alloc_f_lines.SEGMENT9||'-'||gl_alloc_f_lines.SEGMENT10 "Account"
, gl_alloc_f_lines.segment_types_key "Segment"
, gl_alloc_f_lines.relative_period"Period"
, gl_alloc_f_lines.actual_flag"Actual?"
, gl_alloc_f_lines.amount_type"Amt Type"
from GL_ALLOC_BATCHES gl_alloc_batch, GL_ALLOC_FORMULAS gl_alloc_f, GL_ALLOC_FORMULA_LINES gl_alloc_f_lines
, FND_ID_FLEX_STRUCTURES_VL fifsv
where gl_alloc_batch.allocation_batch_id = gl_alloc_f.allocation_batch_id
and gl_alloc_batch.CHART_OF_ACCOUNTS_ID = fifsv.id_flex_num
and gl_alloc_f.allocation_formula_id = gl_alloc_f_lines.allocation_formula_id
order by 1,3,4,6