ECC Procurement, Indirect Procurement, SQL2

Description
Categories: Enterprise Command Center
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
Run ECC Procurement, Indirect Procurement, SQL2 and other Oracle EBS reports with Blitz Report™ on our demo environment
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('2020/04/17', '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
Blitz Report™