ECC Receivables, AR Closing, SQL2
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, Set Of Books Id ...
Columns: Ecc Spec Id, Record Type, Period Name, Period Year, Closing Status, Ledger Id, Ledger Name, Ledger Currency, Org Id, Set Of Books Id ...
Imported from Enterprise Command Center
Dataset Key: ar-period-close
Query Procedure: AR_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
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, 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 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 |