OKL Transfer Journal Entries to GL - Lease and Finance Management

SELECT
  AEH.LEDGER_ID                              LEDGER_ID
, GLL.NAME                                   LEDGER_NAME
-- the following two columns are intentionally kept null as there is a
-- possibility that the following query will not perform and then
-- we will have to rely on this information coming from transfer package
-- In that case we will not be able to provide this information.
-- If this query performs okay, the two null columns can be replaced by
-- actual values.
, NULL                                                   BALANCE_TYPE_CODE
, NULL                                                   BALANCE_TYPE
, COUNT(DECODE(AEH.GL_TRANSFER_STATUS_CODE,'Y',1,NULL))  JE_TRANSFERRED_COUNT
, COUNT(DECODE(AEH.GL_TRANSFER_STATUS_CODE,'Y',NULL,1))  JE_NOT_TRANSFERRED_COUNT
FROM
  XLA_AE_HEADERS                            AEH
, GL_LEDGERS                                GLL
--, XLA_LOOKUPS                             XLK
WHERE
    AEH.APPLICATION_ID                    = :APPLICATION_ID
AND AEH.REQUEST_ID                        = &P_REQ_ID
AND GLL.LEDGER_ID                         = AEH.LEDGER_ID
AND AEH.GROUP_ID                          IN (&P_GROUP_ID_STR)
--AND XLK.LOOKUP_TYPE                     = 'XLA_BALANCE_TYPE'
--AND XLK.LOOKUP_CODE                     = AEH.BALANCE_TYPE_CODE
GROUP BY
 AEH.LEDGER_ID
,GLL.NAME
--,AEH.BALANCE_TYPE_CODE
--,XLK.MEANING