Tuesday 7 July 2015

How to find OPM Batch Close Variance for individual Batch


 Individual Batches with corresponding Close Variance Amount:

select batch_det.batch_no batch_no, batch_det.organization_id , gmf_cls.amount amount 
from
(Select gxel.accounted_amount amount,gxel.event_id,gxeh.event_id,gxel.header_id,gxeh.header_id,gxeh.source_document_id source_document_id,gxeh.source_line_id source_line_id
from gmf_xla_extract_headers gxeh,
gmf_xla_extract_lines gxel
where gxeh.TRANSACTION_DATE >=TO_DATE(:START_DT)
and gxeh.TRANSACTION_DATE <=TO_DATE(:END_DT)
and gxel.journal_line_type ='CLS'
and gxel.event_id = gxeh.event_id)gmf_cls,
(select gbh.batch_id batch_id,gbh.batch_no batch_no, gbh.ORGANIZATION_ID organization_id
from gme_batch_header gbh
where gbh.PLAN_START_DATE >=TO_DATE(:START_DT)) batch_det
where gmf_cls.source_document_id=batch_det.batch_id

No comments:

Post a Comment