ECC Receivables, AR Closing, SQL1

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,
BILL_TO_CUSTOMER_ID,
ACCOUNT_NUMBER,
BILL_TO_CUSTOMER,
BILL_TO_LOCATION,
CUSTOMER_TRX_ID,
PAYMENT_SCHEDULE_ID,
TRANSACTION_TYPE,
transaction_number,
transaction_date,
transaction_currency,
INVOICING_RULE_ID,
INVOICING_RULE,
AMOUNT,
ACCOUNTED_AMOUNT,
GL_DATE,
EVENT_ID,
EVENT_NUMBER,
EVENT_DATE,
EVENT_TYPE_CODE,
EVENT_TYPE_DESC,
EVENT_STATUS_DESC,
EVENT_STATUS_CODE,
PROCESS_STATUS_CODE,
PROCESS_STATUS_DESC,
ENTITY_ID,
ENTITY_CODE,
SOURCE_ID_INT_1,
ECC_LAST_UPDATE_DATE,
LANGUAGE
FROM (  select  /*+  use_nl(xe event_status) */
    rct.customer_trx_id||'-'||arp.PAYMENT_SCHEDULE_ID||'-'||xe.event_id||'-'||xe.EVENT_NUMBER as ECC_SPEC_ID,
	'TRX' RECORD_TYPE,
    ps.PERIOD_NAME,
    ps.PERIOD_YEAR,
    ps.CLOSING_STATUS,
    led.ledger_id,
    led.name ledger_name,
    led.currency_code ledger_currency,
    rct.org_id,
    ORG.name operating_unit,
    rct.bill_to_customer_id,
    b_bill.account_number,
    b_bill_party.party_name bill_to_customer,
    u_bill.location bill_to_location,
    rct.customer_trx_id,
    arp.PAYMENT_SCHEDULE_ID,
    rctt.name transaction_type,
    rct.set_of_books_id,
    rct.TRX_NUMBER transaction_number,
    rct.TRX_DATE transaction_date,
    rct.INVOICE_CURRENCY_CODE transaction_currency,
    rct.INVOICING_RULE_ID,
    rr.NAME invoicing_rule,
    arp.AMOUNT_DUE_ORIGINAL amount,
    arp.amount_due_original*nvl(arp.exchange_rate,1) ACCOUNTED_AMOUNT,
    arp.gl_date,
    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,
    EVENT_STATUS.meaning EVENT_STATUS_DESC,
	xe.PROCESS_STATUS_CODE,
    PROCESS_STATUS.meaning 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,
    ra_customer_trx_all rct,
    ra_cust_trx_types_all rctt,
    AR_PAYMENT_SCHEDULES_ALL arp,
    xla_transaction_entities_upg xte,
    xla_events xe,
    gl_ledgers led,
    hr_all_organization_units_tl org,
    hz_cust_accounts b_bill,
    hz_parties b_bill_party,
    HZ_CUST_SITE_USES_ALL u_bill,
    RA_RULES rr  ,
    FND_LOOKUP_VALUES    EVENT_STATUS,
    FND_LOOKUP_VALUES    PROCESS_STATUS
where
    ps.application_id=222
    AND ps.SET_OF_BOOKS_ID = rct.SET_OF_BOOKS_ID
    AND xe.EVENT_DATE between ps.START_DATE and ps.END_DATE
    AND ps.CLOSING_STATUS = 'O'
    and rct.complete_flag = 'Y'
    and rct.CUSTOMER_TRX_ID = arp.CUSTOMER_TRX_ID
    AND rct.cust_trx_type_id = rctt.cust_trx_type_id
    and rr.RULE_ID (+)= rct.INVOICING_RULE_ID
    AND rct.org_id = rctt.org_id
    and xe.application_id = 222
    AND xte.ENTITY_ID = xe.ENTITY_ID
    and xe.application_id = xte.application_id
    AND xe.EVENT_STATUS_CODE  = 'U'
    AND xe.PROCESS_STATUS_CODE = 'U'
    and xte.source_application_id = 222
    AND rct.CUSTOMER_TRX_ID = xte.SOURCE_ID_INT_1
    AND Led.Ledger_Id  = rct.Set_Of_Books_Id
    and rct.org_id = ORG.Organization_Id
    AND rct.bill_to_customer_id = b_bill.cust_account_id
    AND b_bill.party_id = b_bill_party.party_id
    AND rct.bill_to_site_use_id = u_bill.site_use_id
    AND rct.org_id = u_bill.org_id
    AND EVENT_STATUS.lookup_code = XE.EVENT_STATUS_CODE
    and EVENT_STATUS.language = ORG.language
    and EVENT_STATUS.VIEW_APPLICATION_ID  = 602
    and EVENT_STATUS.lookup_type = 'XLA_EVENT_STATUS'
    and process_status.lookup_code = xe.PROCESS_STATUS_CODE
    and PROCESS_STATUS.language = ORG.language
    and PROCESS_STATUS.VIEW_APPLICATION_ID  = 602
    and process_status.lookup_type = 'XLA_EVENT_PROCESS_STATUS'
    and EVENT_STATUS.language= PROCESS_STATUS.language  ) unacc_trx where unacc_trx.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
  for LANGUAGE in ('US' "US")) 
) x
where
2=2