ECC Receivables, AR Closing, SQL2
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
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, SQL2 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, 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 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, 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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |