ECC Payables, AP Closing, SQL4
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: ap-period-close
Query Procedure: AP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: AP_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Dataset Key: ap-period-close
Query Procedure: AP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: AP_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
select x.* from ( select * from ( SELECT ECC_SPEC_ID, RECORD_TYPE, EVENT_ID, ORG_ID, APPLICATION_ID, INVOICE_ID, INVOICE_TYPE_LOOKUP_CODE, INVOICE_NUMBER, TRANSACTION_CURRENCY, INVOICE_DATE, GL_DATE, ACCOUNTED_AMOUNT, PAYMENT_TYPE_FLAG, PAYMENT_TYPE_DESC, INSTALLMENT, AMOUNT, INVOICE_PAYMENT_ID, CHECK_ID, TRANSACTION_NUMBER, TRANSACTION_DATE, BANK_ACCOUNT_NAME, VENDOR_ID, SUPPLIER_NAME, SUPPLIER_NUMBER, SITE_CODE, EVENT_STATUS_CODE, EVENT_STATUS_DESC, PROCESS_STATUS_CODE, PROCESS_STATUS_DESC, EVENT_TYPE_CODE, EVENT_TYPE_DESC, EVENT_NUMBER, LEDGER_ID, LEDGER_NAME, CURRENCY_CODE, PERIOD_NAME, PERIOD_YEAR, CLOSING_STATUS, OPERATING_UNIT, LANGUAGE FROM ( SELECT ip.invoice_payment_id || '-' || xe.event_id || '-' || xe.event_number ecc_spec_id , 'PAY' AS record_type, xe.event_id, ai.org_id, xe.APPLICATION_ID, ai.invoice_id invoice_id, ai.invoice_type_lookup_code invoice_type_lookup_code, ai.invoice_num invoice_number, ai.invoice_currency_code TRANSACTION_CURRENCY, ai.invoice_date AS invoice_date, ai.GL_DATE, ( ip.amount * nvl(ip.exchange_rate, 1) ) AS ACCOUNTED_AMOUNT, c.payment_type_flag, ( SELECT meaning FROM fnd_lookup_values WHERE view_application_id = 200 AND lookup_type = 'PAYMENT TYPE' AND language = outl.language AND lookup_code = c.payment_type_flag ) PAYMENT_TYPE_DESC, ip.payment_num installment, ip.amount AMOUNT, ip.invoice_payment_id, c.check_id, c.check_number TRANSACTION_NUMBER, c.check_date TRANSACTION_DATE, c.bank_account_name, ai.vendor_id, hp.party_name supplier_name, pav.segment1 supplier_number, pos.vendor_site_code site_code, xe.event_status_code, ( SELECT meaning FROM fnd_lookup_values WHERE view_application_id = 602 AND lookup_type = 'XLA_EVENT_STATUS' AND language = outl.language AND lookup_code = xe.event_status_code ) event_status_desc, xe.process_status_code, ( SELECT meaning FROM fnd_lookup_values WHERE view_application_id = 602 AND lookup_type = 'XLA_EVENT_PROCESS_STATUS' AND language = outl.language AND lookup_code = xe.process_status_code ) process_status_desc, xe.event_type_code, (select ett.description from XLA_EVENT_TYPES_TL ett where xe.EVENT_TYPE_CODE = ett. EVENT_TYPE_CODE and xe.application_id = ett.application_id and ett.language = outl.language) as EVENT_TYPE_DESC, xe.event_number, gl.ledger_id, gl.name ledger_name, gl.currency_code, ps.period_name, ps.period_year, ps.closing_status, outl.name operating_unit, outl.language FROM gl_period_statuses ps, ap_invoices_all ai, gl_ledgers gl, ap_invoice_payments_all ip, ap_checks_all c, ap_suppliers pav, ap_supplier_sites_all pos, hr_all_organization_units ou, hr_all_organization_units_tl outl, hz_parties hp, xla_events xe WHERE xe.application_id = 200 AND ps.adjustment_period_flag = 'N' AND ps.set_of_books_id = ai.set_of_books_id AND ai.gl_date BETWEEN ps.start_date AND ps.end_date AND ps.closing_status = 'O' AND ps.application_id = xe.application_id AND ai.invoice_id = ip.invoice_id AND ip.check_id = c.check_id AND ps.set_of_books_id = ip.set_of_books_id AND ip.accounting_date BETWEEN ps.start_date AND ps.end_date AND ai.set_of_books_id = gl.ledger_id AND ou.organization_id = ai.org_id AND ou.organization_id = outl.organization_id AND ai.vendor_id = pav.vendor_id AND ai.vendor_site_id = pos.vendor_site_id AND hp.party_id = ai.party_id AND ai.payment_status_flag = 'Y' AND ip.posted_flag = 'N' AND ip.accounting_event_id = xe.event_id AND xe.event_status_code = 'U' AND xe.process_status_code = 'U' )temp where temp.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(EVENT_TYPE_DESC) as EVENT_TYPE_DESC, max(PAYMENT_TYPE_DESC) as PAYMENT_TYPE_DESC For LANGUAGE in ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
| LOV |