ECC Receivables, AR Closing, SQL3

select
x.*
from
(
select * from (SELECT   ECC_SPEC_ID,
 RECORD_TYPE,
 PERIOD_NAME,
 PERIOD_YEAR,
 CLOSING_STATUS,
 ledger_id,
 ledger_name,
 ledger_currency,
 org_id,
 operating_unit,
 set_of_books_id,
 CASH_RECEIPT_ID,
 AMOUNT,
 accounted_amount,
 transaction_number,
 transaction_date,
 receipt_status,
 receipt_status_desc,
  transaction_type,
 transaction_currency,
 CONFIRMED_FLAG,
 GL_DATE,
 cash_receipt_history_id,
 rcpt_hstry_status,
 rcpt_hstry_status_desc,
 CREATED_FROM,
 REVERSAL_CREATED_FROM,
 event_id,
 EVENT_NUMBER,
 EVENT_DATE,
 EVENT_TYPE_CODE,
 EVENT_TYPE_DESC,
 EVENT_STATUS_CODE,
 EVENT_STATUS_DESC,
 PROCESS_STATUS_CODE ,
 PROCESS_STATUS_DESC,
 ENTITY_ID,
 ENTITY_CODE,
 source_id_int_1,
 ecc_last_update_date,
 language
 FROM  (  select
   acra.cash_receipt_id||'-'||acrha.CASH_RECEIPT_HISTORY_ID||'-'||xe.EVENT_ID||'-'||xe.EVENT_NUMBER as ECC_SPEC_ID,
   'RCPT' RECORD_TYPE,
   ps.PERIOD_NAME,
   ps.PERIOD_YEAR,
   ps.CLOSING_STATUS,
   led.ledger_id,
   led.name ledger_name,
   led.currency_code ledger_currency,
   acra.org_id,
   org.name operating_unit,
   acra.set_of_books_id,
   acra.CASH_RECEIPT_ID,
   acra.AMOUNT,
   acra.amount * Nvl(acra.exchange_rate, 1) accounted_amount,
   acra.RECEIPT_NUMBER transaction_number,
   acra.currency_code transaction_currency,
   acra.receipt_date transaction_date,
   acra.STATUS receipt_status,
       (select meaning from fnd_lookup_values where view_application_id =222 and lookup_type = 'PAYMENT_TYPE'  and language=org.language and lookup_code = acra.STATUS)   receipt_status_desc,
   acra.TYPE transaction_type,
   acra.CONFIRMED_FLAG,
   acrha.GL_DATE,
   acrha.cash_receipt_history_id,
   acrha.STATUS RCPT_HSTRY_STATUS,
       (select meaning from fnd_lookup_values where view_application_id =222 and lookup_type = 'RECEIPT_CREATION_STATUS'  and language=org.language and lookup_code = acrha.STATUS)   rcpt_hstry_status_desc,
   acrha.CREATED_FROM,
   acrha.REVERSAL_CREATED_FROM,
   xe.event_id,
   xe.EVENT_NUMBER,
   xe.EVENT_DATE,
   xe.EVENT_TYPE_CODE,
   	   (select NAME from XLA_EVENT_TYPES_TL ett where application_id =222 and language=org.language AND xe.EVENT_TYPE_CODE = ett. EVENT_TYPE_CODE AND xte.ENTITY_CODE = ett.ENTITY_CODE)   EVENT_TYPE_DESC,
   xe.EVENT_STATUS_CODE,
      (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_EVENT_STATUS' and language=org.language and lookup_code = xe.EVENT_STATUS_CODE)   EVENT_STATUS_DESC,
   xe.PROCESS_STATUS_CODE,
      (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_EVENT_PROCESS_STATUS'  and language=org.language and lookup_code = xe.PROCESS_STATUS_CODE )   PROCESS_STATUS_DESC ,
   xte.ENTITY_ID,
   xte.ENTITY_CODE,
   xte.SOURCE_ID_INT_1,
   xe.last_update_date ecc_last_update_date,
   org.language
from
   gl_period_statuses ps,
   ar_cash_receipts_all acra,
   ar_cash_receipt_history_all acrha,
   xla_events xe,
   xla_transaction_entities_upg xte,
   gl_ledgers led,
   hr_all_organization_units_tl org
where
    ps.APPLICATION_ID = 222
    AND ps.SET_OF_BOOKS_ID = acra.SET_OF_BOOKS_ID
    AND xe.EVENT_DATE between ps.START_DATE and ps.END_DATE
	AND ps.closing_status = 'O'
    AND acra.cash_receipt_id = acrha.cash_receipt_id(+)
    and acrha.current_record_flag = 'Y'
    and acrha.postable_flag = 'Y'
    and xe.application_id = 222
    AND acra.receipt_number = xte.transaction_number
	AND xe.event_status_code = 'U'
    and xe.PROCESS_STATUS_CODE = 'U'
    AND xte.ENTITY_ID = xe.ENTITY_ID
    AND Led.Ledger_Id  = acra.Set_Of_Books_Id
    and acra.org_id =org.Organization_Id
	and xe.application_id  = xte.application_id  )  unacc_rcpt where unacc_rcpt.language in ('US')  )
	 PIVOT(max(OPERATING_UNIT) as OPERATING_UNIT, max(EVENT_STATUS_DESC) as EVENT_STATUS_DESC, max(PROCESS_STATUS_DESC) as PROCESS_STATUS_DESC,max(EVENT_TYPE_DESC) as EVENT_TYPE_DESC,
  max(receipt_status_desc) as receipt_status_desc, max(rcpt_hstry_status_desc) as rcpt_hstry_status_desc
  for LANGUAGE in ('US' "US"))
) x
where
2=2