ECC Receivables, AR Closing, SQL5

Description

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

ECC Receivables, AR Closing, SQL5 and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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
      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,
    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_upg 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 acra.receipt_number = xte.transaction_number
  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.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

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 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, 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_upg 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 acra.receipt_number = xte.transaction_number 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.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™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes