ECC Procurement, Indirect Procurement, SQL2

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Invoice Id, Invoice Number, Invoice Amount, Invoice Line Number, Invoice Source, Invoice Description, Invoice Justification, Inv Just Split Words Tag, Invoice Line Amount ...
Imported from Enterprise Command Center
Description: Indirect Procurement for Procurement Command Center

Dataset Key: po-pcc-ind-proc
Query Procedure: PO_PCC_IND_PROC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesInd
select
x.*
from
(
select * from (select NULL
       || '-'
       || NULL
       || '-'
       || NULL
       || '-'
       || NULL
       || '-'
       || NULL
       || '-'
       || NULL
       || '-'
       || NULL
       || '-'
       || ai.invoice_id
       || '-'
       || ail.line_number
       || '-'
       || ai.source
       || '-'
       || NULL
       || '-'
       || NULL
       || '-'
       || NULL
       || '-'
       || 'iEXPENSE' ecc_spec_id,
       /*catalog items*/
       /*iExpense*/
       ai.invoice_id,
       ai.invoice_num invoice_number,
       ai.invoice_amount,
       ail.line_number invoice_line_number,
       ai.source invoice_source,
       ail.description invoice_description,
       ail.justification invoice_justification,
       replace(PO_PCC_IND_PROC_UTIL_PVT.remove_stop_words(ail.justification) ,' ',UNISTR('|')) inv_just_split_words_tag,
       nvl(ail.base_amount, ail.amount) invoice_line_amount,
       ail.creation_date invoice_date,
       ail.merchant_name invoice_merchant_name,
       /*standard po*/
       nvl(ail.base_amount, ail.amount) AS spend_amount,
       'iExpense' AS spend_type,
       /*action items*/
       /*common*/
       ail.creation_date spend_date,
       PO_PCC_IND_PROC_UTIL_PVT.get_cost_center_for_expense(ail.invoice_id, ail.line_number) cost_center,
       ail.org_id,
       houtl.name org_name,
       (select currency_code from gl_sets_of_books where ail.set_of_books_id = set_of_books_id) functional_currency,
       ai.invoice_currency_code transaction_currency,
    houtl.language,
    extract(YEAR from ail.creation_date) spend_year,
    'NULL' DOC_AUTHORIZED_USER_IDS
  from ap_invoices_all ai,
       ap_invoice_lines_all ail,
       hr_all_organization_units_tl houtl
 where ai.source IN ('SelfService', 'XpenseXpress')
   and ai.invoice_id                                                = ail.invoice_id
   AND houtl.organization_id (+) = ail.org_id
   and houtl.language IN ('US')
   /*Load CutOff Condition*/
   and ail.last_update_date >= to_date('2019/10/25', 'YYYY/MM/DD'))
   PIVOT (max(org_name) as Org_name
 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