ECC Receivables, AR Closing, SQL4

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: ar-period-close
Query Procedure: AR_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: AR_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Run ECC Receivables, AR Closing, SQL4 and other Oracle EBS reports with Blitz Report™ on our demo environment
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,
 CASH_RECEIPT_HISTORY_ID,
 rcpt_hstry_status,
 rcpt_hstry_status_desc,
 CREATED_FROM,
 REVERSAL_CREATED_FROM,
 GL_DATE,
 XLA_ACC_CODE ,
 XLA_ACC_DESCRIPTION,
 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,
 LINE_DEFINITION_CODE,
 LINE_DEFINITION_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,
 ecc_last_update_date,
 language
 FROM (  select  /*+ leading(xah) */
       acra.cash_receipt_id||'-'||acrha.CASH_RECEIPT_HISTORY_ID||'-'||xe.EVENT_ID||'-'||xe.EVENT_NUMBER||'-'||XAl.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,
    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,
      xe.event_id,
      xe.EVENT_NUMBER,
      xe.EVENT_DATE,
     xte.ENTITY_ID,
     xte.ENTITY_CODE,
     xte.SOURCE_ID_INT_1,
      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,
     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
      AND xdl.ACCOUNTING_LINE_TYPE_CODE = alt.ACCOUNTING_LINE_TYPE_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,
   ar_cash_receipts_all acra,
   ar_cash_receipt_history_all acrha,
   GL_CODE_COMBINATIONS cc,
   xla_distribution_links xdl,
   xla_transaction_entities_upg xte,
   xla_events xe,
   xla_ae_headers xah,
   xla_ae_lines xal,
   gl_ledgers led,
   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
   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.STATUS = 'APP'
   AND acra.cash_receipt_id = acrha.cash_receipt_id
   and xe.application_id = 222
   AND xe.PROCESS_STATUS_CODE <> 'U'
   AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   AND acra.receipt_number = xte.transaction_number
   and xe.application_id  = xte.application_id
   AND xte.ENTITY_ID = xe.ENTITY_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 xah.ZERO_AMOUNT_FLAG = 'N'
   AND xah.ACCOUNTING_ENTRY_STATUS_CODE <> 'N'
   AND xah.GL_TRANSFER_STATUS_CODE <> 'Y'
   AND XAH.EVENT_ID = XE.EVENT_ID
   AND XAH.APPLICATION_ID = XE.APPLICATION_ID
   and xal.DISPLAYED_LINE_NUMBER >=0
   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_appld_rcpt where acc_appld_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(EVENT_CLASS_DESC) as EVENT_CLASS_DESC, max(LINE_DEFINITION_DESC) as LINE_DEFINITION_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
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™

Blitz Report™ provides multiple benefits: