ECC Receivables, AR Closing, SQL5

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, Cash Receipt 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,
 operating_unit,
 org_id,
 CASH_RECEIPT_ID,
 AMOUNT,
 accounted_amount,
 transaction_number,
 transaction_date,
 receipt_status,
 receipt_status_desc,
 transaction_type,
 transaction_currency,
 CONFIRMED_FLAG,
 CASH_RECEIPT_HISTORY_ID,
 rcpt_hstry_status,
 rcpt_hstry_status_desc,
 CREATED_FROM,
 REVERSAL_CREATED_FROM,
 gl_date,
 event_id,
 event_number,
 event_date,
 EVENT_STATUS_CODE,
 EVENT_STATUS_DESC,
 PROCESS_STATUS_CODE ,
 PROCESS_STATUS_DESC,
 EVENT_TYPE_CODE,
 EVENT_TYPE_DESC,
 entity_id,
 entity_code,
 source_id_int_1,
 accounting_line_code,
 accounting_line_desc,
 source_distribution_type,
 event_class_code,
 event_class_desc,
 AE_HEADER_ID,
 line_definition_code,
 line_definition_desc,
 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,
 XLA_ACC_CODE ,
 XLA_ACC_DESCRIPTION,
 CURRENCY_CODE,
 ENTERED_DR,
 ENTERED_CR,
 ACCOUNTED_DR,
 ACCOUNTED_CR,
 LINES_DESCRIPTION,
 ecc_last_update_date,
 language
 FROM  (  select /*+ leading(xah) push_pred(xdl) */
   acra.cash_receipt_id||'-'||acrha.CASH_RECEIPT_HISTORY_ID||'-'||xe.EVENT_ID||'-'||xe.event_number||'-'||XAH.AE_HEADER_ID||'-'||xal.AE_LINE_NUM 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.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.currency_code transaction_currency,
   acra.CONFIRMED_FLAG,
   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,
   acrha.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,
    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,
    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,
   ar_cash_receipts_all acra,
   ar_cash_receipt_history_all acrha,
     ( 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_events xe,
   xla_transaction_entities xte,
   xla_ae_headers xah,
   xla_ae_lines xal,
   gl_ledgers led,
   GL_CODE_COMBINATIONS cc,
   hr_all_organization_units_tl org,
   ( 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
  acra.STATUS <> 'APP'
  AND ps.APPLICATION_ID = 222
  AND ps.SET_OF_BOOKS_ID = acra.SET_OF_BOOKS_ID
  AND acrha.GL_DATE between ps.START_DATE and ps.END_DATE
  AND ps.CLOSING_STATUS = 'O'
  AND acra.cash_receipt_id = acrha.cash_receipt_id
  and xe.event_id = acrha.event_id
  and xe.APPLICATION_ID=222
  and Xah.APPLICATION_ID=xal.APPLICATION_ID
  and xe.PROCESS_STATUS_CODE <> 'U'
  AND xah.ZERO_AMOUNT_FLAG = 'N'
  and xah.GL_TRANSFER_STATUS_CODE <> 'Y'
  AND xal.displayed_line_number >= 0
  and XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
  AND XAH.EVENT_ID = XE.EVENT_ID
  AND XAH.APPLICATION_ID = XE.APPLICATION_ID
  AND xte.ENTITY_ID = xe.ENTITY_ID
  AND xte.APPLICATION_ID = xe.APPLICATION_ID
  AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and xdl.EVENT_ID = acrha.event_id
   and xdl.application_id  = xah.application_id
   AND XDL.AE_HEADER_ID = XAH.AE_HEADER_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 Led.Ledger_Id  = acra.Set_Of_Books_Id
  AND acra.org_id =org.Organization_Id
   AND cc.code_combination_id (+) = xal.code_combination_id
   AND account_segments.code_combination_id (+) = cc.code_combination_id   ) acc_unappld_rcpt where acc_unappld_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(receipt_status_desc) as receipt_status_desc, max(rcpt_hstry_status_desc) as rcpt_hstry_status_desc, max(EVENT_TYPE_DESC) as  EVENT_TYPE_DESC,MAX ( accounting_line_desc ) AS accounting_line_desc,
   max(GL_TRANSFER_STATUS_DESC) as GL_TRANSFER_STATUS_DESC, max(ACC_ENTRY_STATUS_DESC) as ACC_ENTRY_STATUS_DESC, max(EVENT_CLASS_DESC) as EVENT_CLASS_DESC,MAX ( line_definition_desc ) AS line_definition_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
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: