ECC Receivables, AR Closing, SQL1 (1)

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Record Type, Period Name, Period Year, Closing Status, Ledger Id, Ledger Name, Ledger Currency, Org Id, Set Of Books Id ...
Imported from Enterprise Command Center
Dataset Key: ar-period-close
Query Procedure: AR_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
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 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