ECC Receivables, AR Closing, SQL2

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,
 customer_trx_id,
 amount,
 accounted_amount,
 transaction_number,
 transaction_date,
 transaction_currency,
 transaction_type,
 invoicing_rule_id,
 invoicing_rule,
 XLA_ACC_CODE ,
 XLA_ACC_DESCRIPTION,
 CUST_TRX_LINE_GL_DIST_ID,
 bill_to_customer_id,
 account_number,
 bill_to_customer,
 bill_to_location,
 payment_schedule_id,
 gl_date,
 event_id,
 event_number,
 event_date,
 EVENT_STATUS_CODE,
 EVENT_STATUS_DESC,
 PROCESS_STATUS_CODE,
 PROCESS_STATUS_DESC,
 ACCOUNTING_LINE_CODE,
 ACCOUNTING_LINE_DESC,
 SOURCE_DISTRIBUTION_TYPE,
 EVENT_CLASS_CODE,
 EVENT_CLASS_DESC,
 LINE_DEFINITION_CODE,
 LINE_DEFINITION_DESC,
 EVENT_TYPE_CODE,
 EVENT_TYPE_DESC,
 AE_HEADER_ID,
 GL_TRANSFER_STATUS_CODE,
 GL_TRANSFER_STATUS_DESC,
 ACC_ENTRY_STATUS_CODE,
 ACC_ENTRY_STATUS_DESC,
 ACCOUNTING_ENTRY_TYPE_code,
 ACCOUNTING_ENTRY_TYPE_DESC,
 JE_CATEGORY_NAME,
 JE_CATEGORY_NAME_DESC,
 HEADER_DESCRIPTION,
 BALANCE_TYPE_CODE,
 BALANCE_TYPE_DESC,
 gl_period_name,
 ACCOUNTING_DATE,
 AE_LINE_NUM,
 DISPLAYED_LINE_NUMBER,
 ACCOUNTING_CLASS_CODE,
 ACCOUNTING_CLASS_DESC,
 BUSINESS_CLASS_CODE,
 BUSINESS_CLASS_DESC,
 CODE_COMBINATION_ID,
 CURRENCY_CODE,
 ENTERED_DR,
 ENTERED_CR,
 ACCOUNTED_DR,
 ACCOUNTED_CR,
 LINES_DESCRIPTION,
 ENTITY_ID,
 ENTITY_CODE,
 SOURCE_ID_INT_1,
 ecc_last_update_date,
 language
 FROM ( select   /*+ leading ( xah xdl xal ragd rct xe) push_pred ( xdl )  */
       rct.customer_trx_id||'-'||ragd.CUST_TRX_LINE_GL_DIST_ID||'-'||xe.EVENT_ID||'-'||xe.EVENT_NUMBER||'-'||XAL.AE_HEADER_ID||'-'||XAL.AE_LINE_NUM 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.set_of_books_id,
       rct.customer_trx_id,
       arp.AMOUNT_DUE_ORIGINAL amount,
       arp.amount_due_original*nvl(arp.exchange_rate,1) ACCOUNTED_AMOUNT,
       rct.TRX_NUMBER transaction_number,
       rct.TRX_DATE transaction_date,
       rctt.name transaction_type,
       rct.INVOICE_CURRENCY_CODE transaction_currency,
       rct.INVOICING_RULE_ID,
	   rr.NAME invoicing_rule,
      rct.bill_to_customer_id,
      b_bill.account_number account_number,
      b_bill_party.party_name bill_to_customer,
      u_bill.location bill_to_location,
      arp.PAYMENT_SCHEDULE_ID,
    DECODE (xal.CODE_COMBINATION_ID, NULL, NULL,-1,NULL,FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1
                (P_LEXICAL_NAME => 'ACCOUNT CODE'
                , P_APPLICATION_SHORT_NAME => 'SQLGL'
                , P_ID_FLEX_CODE => 'GL#'
                , P_ID_FLEX_NUM => led.CHART_OF_ACCOUNTS_ID
                , P_DATA_SET => led.CHART_OF_ACCOUNTS_ID
                , P_CCID => xal.CODE_COMBINATION_ID
                , P_SEGMENTS => 'GL_ACCOUNT'
                , P_SHOW_PARENT_SEGMENTS => 'Y'
                , P_OUTPUT_TYPE=>'VALUE')) AS XLA_ACC_CODE ,
   DECODE(xal.CODE_COMBINATION_ID, NULL, NULL,-1,NULL,FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1
                (P_LEXICAL_NAME => 'ACCOUNT CODE'
                , P_APPLICATION_SHORT_NAME => 'SQLGL'
                , P_ID_FLEX_CODE => 'GL#'
                , P_ID_FLEX_NUM => led.CHART_OF_ACCOUNTS_ID
                , P_DATA_SET => led.CHART_OF_ACCOUNTS_ID
                , P_CCID => xal.CODE_COMBINATION_ID
                , P_SEGMENTS => 'GL_ACCOUNT'
                , P_SHOW_PARENT_SEGMENTS => 'Y'
                , P_OUTPUT_TYPE=>'DESCRIPTION')) AS XLA_ACC_DESCRIPTION,
      ragd.CUST_TRX_LINE_GL_DIST_ID,
      arp.gl_date,
      xe.event_id,
    xe.EVENT_NUMBER,
    xe.EVENT_DATE,
    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,
    DECODE(xe.PROCESS_STATUS_CODE,'Z','I',xe.PROCESS_STATUS_CODE) 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 = DECODE(xe.PROCESS_STATUS_CODE,'Z','I',xe.PROCESS_STATUS_CODE))
    	  PROCESS_STATUS_DESC ,
    xah.EVENT_TYPE_CODE,
      (select NAME from XLA_EVENT_TYPES_TL ett where application_id =222 and language=org.language AND xah.EVENT_TYPE_CODE = ett. EVENT_TYPE_CODE AND xte.ENTITY_CODE = ett.ENTITY_CODE)   EVENT_TYPE_DESC,
    xte.ENTITY_ID,
    xte.ENTITY_CODE,
    xte.SOURCE_ID_INT_1,
    xdl.ACCOUNTING_LINE_CODE,
	  (select NAME from XLA_ACCT_LINE_TYPES_TL alt where application_id =222 and alt.AMB_CONTEXT_CODE = 'DEFAULT' and language=org.language AND xdl.ACCOUNTING_LINE_CODE = alt.ACCOUNTING_LINE_CODE)   ACCOUNTING_LINE_DESC,
      xdl.SOURCE_DISTRIBUTION_TYPE,
    xdl.EVENT_CLASS_CODE,
	  (select NAME from XLA_EVENT_CLASSES_TL ect where application_id =222 and language=org.language AND xdl.EVENT_CLASS_CODE = ect.EVENT_CLASS_CODE  AND xte.ENTITY_CODE = ect.ENTITY_CODE)   EVENT_CLASS_DESC,
    xdl.LINE_DEFINITION_CODE,
 (select NAME from XLA_LINE_DEFINITIONS_TL ldt where application_id =222 and ldt.AMB_CONTEXT_CODE = 'DEFAULT' and language=org.language AND xdl.EVENT_CLASS_CODE = ldt.EVENT_CLASS_CODE  AND xdl.LINE_DEFINITION_CODE = ldt.LINE_DEFINITION_CODE)
     LINE_DEFINITION_DESC,
    xah.AE_HEADER_ID,
    xah.GL_TRANSFER_STATUS_CODE,
       (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'GL_TRANSFER_FLAG' and language=org.language and lookup_code = xah.GL_TRANSFER_STATUS_CODE) GL_TRANSFER_STATUS_DESC,
    xah.ACCOUNTING_ENTRY_STATUS_CODE ACC_ENTRY_STATUS_CODE,
       (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_ACCOUNTING_ENTRY_STATUS' and language=org.language and lookup_code = xah.ACCOUNTING_ENTRY_STATUS_CODE) ACC_ENTRY_STATUS_DESC,
    xah.ACCOUNTING_ENTRY_TYPE_CODE,
       (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_ACCOUNTING_ENTRY_TYPE' and language=org.language and lookup_code = xah.ACCOUNTING_ENTRY_TYPE_CODE) ACCOUNTING_ENTRY_TYPE_DESC,
    xah.JE_CATEGORY_NAME,
       (select DESCRIPTION from GL_JE_CATEGORIES_TL jct where language=org.language and xah.JE_CATEGORY_NAME = jct.JE_CATEGORY_NAME) JE_CATEGORY_NAME_DESC,
    xah.DESCRIPTION HEADER_DESCRIPTION,
    xah.BALANCE_TYPE_CODE,
       (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_BALANCE_TYPE'   and language=org.language and lookup_code = xah.BALANCE_TYPE_CODE) BALANCE_TYPE_DESC,
    xah.PERIOD_NAME gl_period_name,
    xal.ACCOUNTING_DATE,
    xal.AE_LINE_NUM,
    xal.DISPLAYED_LINE_NUMBER,
    xal.ACCOUNTING_CLASS_CODE,
        (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_ACCOUNTING_CLASS'  and language=org.language and lookup_code = xal.ACCOUNTING_CLASS_CODE) ACCOUNTING_CLASS_DESC,
    xal.BUSINESS_CLASS_CODE,
        (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_BUSINESS_FLOW_CLASS' and language=org.language and lookup_code = xal.BUSINESS_CLASS_CODE) BUSINESS_CLASS_DESC,
    xal.CODE_COMBINATION_ID,
    xal.CURRENCY_CODE,
    xal.ENTERED_DR,
    xal.ENTERED_CR,
    xal.ACCOUNTED_DR,
    xal.ACCOUNTED_CR,
    xal.DESCRIPTION LINES_DESCRIPTION,
    xe.last_update_date ecc_last_update_date,
    org.language
from
    gl_period_statuses ps,
    ra_customer_trx_all rct,
    ra_cust_trx_line_gl_dist_all ragd,
	ra_cust_trx_types_all rctt,
    AR_PAYMENT_SCHEDULES_ALL arp,
    xla_transaction_entities_upg xte,
    xla_events xe,
  ( SELECT /*+ push_pred */ *  FROM  xla_distribution_links xdl0  WHERE
         xdl0.ref_ae_header_id || xdl0.temp_line_num = (  SELECT /*+ push_subq  */  xdl.ref_ae_header_id  || xdl.temp_line_num
                              FROM  xla_distribution_links xdl  WHERE application_id = 222 AND xdl.application_id = xdl0.application_id
                               AND   xdl.ae_header_id = xdl0.ae_header_id
                               AND   xdl.ae_line_num = xdl0.ae_line_num
                               AND   ROWNUM = 1  ) ) xdl,
    xla_ae_headers xah,
    xla_ae_lines xal,
    GL_CODE_COMBINATIONS cc,
    gl_ledgers led,
    hr_all_organization_units_tl org,
    RA_RULES rr  ,
    hz_cust_accounts   b_bill,
    hz_parties         b_bill_party ,
    HZ_CUST_SITE_USES_ALL    u_bill,
	( SELECT ccid.code_combination_id,  sav.id_flex_num chart_of_accounts_id
                      FROM
                            fnd_id_flex_segments s,
                            fnd_segment_attribute_values sav,
                            gl_code_combinations ccid
                      WHERE s.application_id = 101
                      AND   s.id_flex_code = 'GL#'
                      AND   s.enabled_flag = 'Y'
                      AND   sav.application_column_name = s.application_column_name
                      AND   sav.application_id = 101
                      AND   sav.id_flex_code = s.id_flex_code
                      AND   sav.id_flex_num = s.id_flex_num
                      AND   sav.attribute_value = 'Y'
                      AND   sav.segment_attribute_type = 'GL_ACCOUNT'
                      AND   ccid.chart_of_accounts_id = s.id_flex_num  ) account_segments
where
    ps.application_id=222
    AND ps.SET_OF_BOOKS_ID = rct.SET_OF_BOOKS_ID
    AND ragd.GL_DATE between ps.START_DATE and ps.END_DATE
    and rct.CUSTOMER_TRX_ID = ragd.CUSTOMER_TRX_ID
    AND ps.CLOSING_STATUS = 'O'
    AND rct.CUSTOMER_TRX_ID = xte.SOURCE_ID_INT_1
	and rr.RULE_ID (+)= rct.INVOICING_RULE_ID
	AND rct.cust_trx_type_id = rctt.cust_trx_type_id
    and rct.CUSTOMER_TRX_ID = arp.CUSTOMER_TRX_ID
    AND rct.org_id = rctt.org_id
    and xe.application_id = 222
	AND xe.PROCESS_STATUS_CODE <> 'U'
    AND xte.ENTITY_ID = xe.ENTITY_ID
    and xe.application_id = xte.application_id
    AND ragd.CUST_TRX_LINE_GL_DIST_ID = xdl.SOURCE_DISTRIBUTION_ID_NUM_1
    AND xdl.EVENT_ID = ragd.EVENT_ID
    AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
    AND xdl.application_id = XAH.APPLICATION_ID
	AND xdl.application_id = xal.APPLICATION_ID
    AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
    AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
    AND xah.ZERO_AMOUNT_FLAG = 'N'
    AND xah.GL_TRANSFER_STATUS_CODE <> 'Y'
	and xah.ACCOUNTING_ENTRY_STATUS_CODE <> 'N'
    and xal.DISPLAYED_LINE_NUMBER >=0
	AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = xah.application_id
    AND XE.EVENT_ID = XAH.EVENT_ID
    AND XAH.APPLICATION_ID = XE.APPLICATION_ID
    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 cc.code_combination_id (+) = xal.code_combination_id
    AND account_segments.code_combination_id (+) = cc.code_combination_id   )  acc_trx where acc_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(ACCOUNTING_LINE_DESC) as ACCOUNTING_LINE_DESC, max(EVENT_CLASS_DESC) as EVENT_CLASS_DESC, max(LINE_DEFINITION_DESC) as LINE_DEFINITION_DESC,
   max(EVENT_TYPE_DESC) as EVENT_TYPE_DESC, max(GL_TRANSFER_STATUS_DESC) as GL_TRANSFER_STATUS_DESC, max(ACC_ENTRY_STATUS_DESC) as ACC_ENTRY_STATUS_DESC,
   max(ACCOUNTING_ENTRY_TYPE_DESC) as ACCOUNTING_ENTRY_TYPE_DESC, max(JE_CATEGORY_NAME_DESC) as JE_CATEGORY_NAME_DESC, max(BALANCE_TYPE_DESC) as BALANCE_TYPE_DESC,
   max(ACCOUNTING_CLASS_DESC) as ACCOUNTING_CLASS_DESC, max(BUSINESS_CLASS_DESC) as BUSINESS_CLASS_DESC
  for LANGUAGE in ('US' "US"))
) x
where
2=2