1. Tables and Views for Procurement
  2. POI_ALL_SPEND_DATA_V

POI_ALL_SPEND_DATA_V

Details

  • Schema: FUSION

  • Object owner: POI

  • Object type: VIEW

Columns

Name

SOURCE_TABLE

DATASOURCE_NUM_ID

TXN_ID

SOURCE_TXN_ID

SPEND_TXN_TYPE

TXN_NUM

LINE_NUM

TXN_DATE

TXN_DESC

LINE_DESC

ITEM_CODE

ITEM_DESCRIPTION

SUPPLIER_NAME

SUPPLIER_SITE

OPERATING_UNIT

UOM

LINE_QUANTITY

CURRENCY

UNIT_PRICE

LINE_AMOUNT

COST_CENTER

EBS_CATEGORY_CODE

UNSPSC_CATEGORY_CODE

CUSTOM_CATEGORY_CODE1

CUSTOM_CATEGORY_CODE2

CUSTOM_CATEGORY_CODE3

EBS_AUTO_CODE

UNSPSC_AUTO_CODE

CUSTOM_AUTO_CODE1

CUSTOM_AUTO_CODE2

CUSTOM_AUTO_CODE3

W_UPDATE_DT

FACT_INTEGRATION_ID

FACT_TABLE

BU_ID

Query

SQL_Statement

SELECT

'POI_SPEND_DATA' source_table,

50 datasource_num_id,

txn_id,

source_transaction_id source_txn_id,

spend_txn_type,

txn_num,

line_num,

txn_date,

txn_desc,

line_desc,

item_code,

item_description,

supplier_name,

supplier_site,

operating_unit,

uom,

line_quantity,

currency,

unit_price,

line_amount,

cost_center,

ebs_category_code,

unspsc_category_code,

custom_category_code1,

custom_category_code2,

custom_category_code3,

ebs_auto_code,

unspsc_auto_code,

custom_auto_code1,

custom_auto_code2,

custom_auto_code3,

w_update_dt,

FACT_INTEGRATION_ID,

FACT_TABLE,

-1 bu_id

FROM

poi_spend_data

union all

select

'PO_DISTRIBUTIONS_ALL' source_table,

10 datasource_num_id,

po_distribution_id txn_id,

po_distribution_id source_txn_id,

'ORA_POI_ORDERS' spend_txn_type,

POH.segment1 txn_num,

pol.LINE_NUM line_num,

poh.approved_date txn_date,

poh.comments txn_desc,

pol.ITEM_DESCRIPTION line_desc,

esib.item_number item_code,

pol.item_description item_description,

suppliername.party_name supplier_name,

suppliersites.vendor_site_code,

bu.name operating_unit,

pol.UOM_CODE UOM,

decode(pol.matching_basis, 'QUANTITY',pod.quantity_ordered-nvl(pod.quantity_cancelled,0)) line_quantity,

psp.currency_code currency,

pol.unit_price*nvl(pod.rate,1) unit_price,

decode(POL.MATCHING_BASIS, 'QUANTITY', (POD.QUANTITY_ORDERED - nvl(pod.QUANTITY_CANCELLED,0))* POL.UNIT_PRICE*nvl(pod.rate,1), 'AMOUNT', ((pod.amount_ordered - nvl(pod.amount_cancelled,0))*nvl(pod.rate,1))) line_amount,

'CC:'||pod.code_combination_id cost_center,

pol.category_id purchasing_category_code,

to_number(null) unspsc_category_code,

to_number(null) custom_category_code1,

to_number(null) custom_category_code2,

to_number(null) custom_category_code3,

to_number(null) purchasing_auto_code,

to_number(null) unspsc_auto_code,

to_number(null) custom_auto_code1,

to_number(null) custom_auto_code2,

to_number(null) custom_auto_code3,

poh.approved_date w_update_dt,

to_char(pod.po_distribution_id) fact_integration_id,

'PO_DISTRIBUTIONS_ALL' fact_table,

poh.prc_bu_id

from po_distributions_all pod,

po_headers_all poh,

po_lines_all pol,

EGP_SYSTEM_ITEMS_VL esib,

poz_suppliers supplier,

hz_parties suppliername,

poz_supplier_sites_all_m suppliersites,

hr_all_organization_units_f_vl bu,

po_system_parameters_all psp

where poh.po_header_id = pol.po_header_id

and pol.po_line_id = pod.po_line_id

and pol.item_id = esib.inventory_item_id (+)

and esib.organization_id(+) = pod.destination_organization_id

and poh.vendor_id = supplier.vendor_id

and supplier.party_id = suppliername.party_id

and poh.vendor_site_id= suppliersites.vendor_site_id

and poh.prc_bu_id = bu.organization_id

and poh.prc_bu_id = psp.prc_bu_id

and poh.approved_flag = 'Y'

and trunc(sysdate) between bu.effective_start_date and bu.effective_end_date

union all

select

'POR_REQ_DISTRIBUTIONS_ALL' source_table,

20 datasource_num_id,

distribution_id txn_id,

distribution_id source_txn_id,

'ORA_POI_REQUISITIONS' spend_txn_type,

prh.requisition_number txn_num,

prl.LINE_NUMber line_num,

prh.approved_date txn_date,

prh.description || ' ' || prh.justification txn_desc,

prl.ITEM_DESCRIPTION line_desc,

esib.item_number item_code,

prl.item_description item_description,

suppliername.party_name supplier_name,

suppliersites.vendor_site_code,

bu.name operating_unit,

prl.UOM_CODE UOM,

decode(prl.matching_basis, 'QUANTITY', prd.distribution_quantity) line_quantity,

psp.currency_code currency,

prL.UNIT_PRICE unit_price,

decode(prl.MATCHING_BASIS, 'QUANTITY', prd.distribution_quantity * prl.unit_price, 'AMOUNT', prd.distribution_amount) line_amount,

'CC:'||prd.code_combination_id cost_center,

prl.category_id purchasing_category_code,

to_number(null) unspsc_category_code,

to_number(null) custom_category_code1,

to_number(null) custom_category_code2,

to_number(null) custom_category_code3,

to_number(null) purchasing_auto_code,

to_number(null) unspsc_auto_code,

to_number(null) custom_auto_code1,

to_number(null) custom_auto_code2,

to_number(null) custom_auto_code3,

prh.approved_date w_update_dt,

to_char(prd.distribution_id) fact_integration_id,

'POR_REQ_DISTRIBUTIONS_ALL' fact_table,

prh.req_bu_id bu_id

from por_req_distributions_all prd,

por_requisition_headers_all prh,

por_requisition_lines_all prl,

EGP_SYSTEM_ITEMS_VL esib,

poz_suppliers supplier,

hz_parties suppliername,

poz_supplier_sites_all_m suppliersites,

hr_all_organization_units_f_vl bu,

po_system_parameters_all psp

where prh.requisition_header_id = prl.requisition_header_id

and prl.requisition_line_id = prd.requisition_line_id

and prh.req_bu_id = psp.prc_bu_id

and prl.item_id = esib.inventory_item_id (+)

and esib.organization_id(+) = prl.destination_organization_id

and prl.vendor_id = supplier.vendor_id (+)

and supplier.party_id = suppliername.party_id(+)

and prl.vendor_site_id= suppliersites.vendor_site_id (+)

and prh.req_bu_id = bu.organization_id

and prh.document_status = 'APPROVED'

and prl.line_status = 'APPROVED'

and trunc(sysdate) between bu.effective_start_date and bu.effective_end_date

union all

select

'AP_INVOICE_DISTRIBUTIONS_ALL' source_table,

30 datasource_num_id,

invoice_distribution_id txn_id,

invoice_distribution_id source_txn_id,

'ORA_POI_INVOICES' spend_txn_type,

ai.invoice_num txn_num,

aid.invoice_line_number line_number,

aid.accounting_date txn_date,

ai.description txn_desc,

aid.description line_desc,

esib.item_number item_code,

pol.item_description item_description,

suppliername.party_name supplier_name,

suppliersites.vendor_site_code,

bu.name operating_unit,

pol.UOM_CODE UOM,

aid.quantity_invoiced line_quantity,

asp.base_currency_code currency,

aid.unit_price unit_price,

NVL(aid.base_amount,aid.amount) line_amount,

'CC:' || aid.dist_code_combination_id cost_center,

pol.category_id purchasing_category_code,

to_number(null) unspsc_category_code,

to_number(null) custom_category_code1,

to_number(null) custom_category_code2,

to_number(null) custom_category_code3,

to_number(null) purchasing_auto_code,

to_number(null) unspsc_auto_code,

to_number(null) custom_auto_code1,

to_number(null) custom_auto_code2,

to_number(null) custom_auto_code3,

aid.accounting_date w_update_dt,

to_char(aid.invoice_distribution_id) fact_integration_id,

'AP_INVOICE_DISTRIBUTIONS_ALL' fact_table,

ai.org_id bu_id

from ap_invoices_all ai,

ap_invoice_distributions_all aid,

po_lines_all pol,

po_distributions_all pod,

egp_system_items_vl esib,

poz_suppliers supplier,

hz_parties suppliername,

poz_supplier_sites_all_m suppliersites,

hr_all_organization_units_f_vl bu,

ap_system_parameters_all asp

where ai.invoice_id = aid.invoice_id

and aid.po_distribution_id = pod.po_distribution_id(+)

and pod.po_line_id = pol.po_line_id (+)

and pol.item_id = esib.inventory_item_id (+)

and pod.destination_organization_id = esib.organization_id(+)

and ai.vendor_site_id = suppliersites.vendor_site_id

AND ai.vendor_id = supplier.vendor_id

AND suppliersites.VENDOR_ID = supplier.VENDOR_ID

AND supplier.party_id = suppliername.party_id

and ai.org_id = bu.organization_id

and ai.org_id = asp.org_id

and trunc(sysdate) between bu.effective_start_date and bu.effective_end_date

and aid.posted_flag = 'Y'

union all

select

'EXM_EXPENSE_DISTS' source_table,

40 datasource_num_id,

eed.expense_dist_id txn_id,

eed.expense_dist_id source_txn_id,

'ORA_POI_EXPENSES' spend_txn_type,

eer.expense_report_num txn_num,

to_number(null) line_num,

eer.final_approval_date txn_date,

eer.purpose txn_desc,

ee.description || ' ' || ee.justification line_desc,

flv.lookup_code item_code,

flv.meaning item_description,

merchant_name supplier_name,

to_char(null) vendor_site_code,

bu.bu_name operating_unit,

to_char(null) uom,

to_number(null) line_quantity,

lgr.currency_code currency,

to_number(null) unit_price,

case

when lgr.currency_code = ee.reimbursement_currency_code then eed.reimbursable_amount

ELSE

(select eed.reimbursable_amount*gdr.conversion_rate from gl_daily_rates gdr

where gdr.conversion_type = eer.exchange_rate_type

and gdr.conversion_date = eer.final_approval_date

and gdr.from_currency = ee.reimbursement_currency_code

and gdr.to_currency = lgr.currency_code)

end line_amount,

'CC:' || eed.Code_Combination_Id cost_center,

to_number(null) purchasing_category_code,

to_number(null) unspsc_category_code,

to_number(null) custom_category_code1,

to_number(null) custom_category_code2,

to_number(null) custom_category_code3,

to_number(null) purchasing_auto_code,

to_number(null) unspsc_auto_code,

to_number(null) custom_auto_code1,

to_number(null) custom_auto_code2,

to_number(null) custom_auto_code3,

eer.final_approval_date w_update_dt,

to_char(eed.expense_dist_id) fact_integration_id,

'EXM_EXPENSE_DISTS' fact_table,

eer.org_id bu_id

from

exm_expense_reports eer,

exm_expenses ee,

exm_expense_dists eed,

fun_all_business_units_v bu,

gl_ledgers lgr,

fnd_lookup_values_vl flv

where eer.expense_report_id = ee.expense_report_id

and ee.expense_id = eed.expense_id

and eer.org_id = bu.bu_id

and bu.primary_ledger_id = lgr.ledger_id

and eer.expense_status_code in ('APPROVAL_COMPLETE', 'PARTIAL_PAID','PAID','INVOICED')

and nvl(bothpay_flag,'N') = 'N'

and flv.lookup_type = 'EXM_EXPENSE_TYPE_CATEGORY'

and flv.lookup_code = ee.expense_type_category_code

and (ee.itemization_parent_expense_id is null or ee.itemization_parent_expense_id != -1)

union all

select

'AP_INVOICE_DISTRIBUTIONS_ALL' source_table,

60 datasource_num_id,

invoice_distribution_id txn_id,

invoice_distribution_id source_txn_id,

'ORA_POI_ESG_INVOICES' spend_txn_type,

ai.invoice_num txn_num,

aid.invoice_line_number line_number,

aid.accounting_date txn_date,

ai.description txn_desc,

aid.description line_desc,

esib.item_number item_code,

pol.item_description item_description,

suppliername.party_name supplier_name,

suppliersites.vendor_site_code,

bu.name operating_unit,

pol.UOM_CODE UOM,

aid.quantity_invoiced line_quantity,

asp.base_currency_code currency,

aid.unit_price unit_price,

NVL(aid.base_amount,aid.amount) line_amount,

'CC:' || aid.dist_code_combination_id cost_center,

pol.category_id purchasing_category_code,

to_number(null) unspsc_category_code,

to_number(null) custom_category_code1,

to_number(null) custom_category_code2,

to_number(null) custom_category_code3,

to_number(null) purchasing_auto_code,

to_number(null) unspsc_auto_code,

to_number(null) custom_auto_code1,

to_number(null) custom_auto_code2,

to_number(null) custom_auto_code3,

aid.accounting_date w_update_dt,

to_char(aid.invoice_distribution_id) fact_integration_id,

'AP_INVOICE_DISTRIBUTIONS_ALL' fact_table,

ai.org_id bu_id

from ap_invoices_all ai,

ap_invoice_distributions_all aid,

po_lines_all pol,

po_distributions_all pod,

egp_system_items_vl esib,

poz_suppliers supplier,

hz_parties suppliername,

poz_supplier_sites_all_m suppliersites,

hr_all_organization_units_f_vl bu,

ap_system_parameters_all asp,

gl_code_combinations GCC,

hr_locations_all_vl HRL,

ap_invoice_lines_all AIL,

por_item_cat_parent_levels PIC,

por_browse_categories_tl PC1,

por_browse_categories_tl PC2,

por_browse_categories_tl PC3,

por_browse_categories_tl PC4,

por_browse_categories_tl PC5,

por_browse_categories_tl PC6,

por_browse_categories_tl PC7,

por_browse_categories_tl PC8,

por_browse_categories_tl PC9,

por_browse_categories_tl PC10,

xle_entity_profiles XEP

where ai.invoice_id = aid.invoice_id

and aid.po_distribution_id = pod.po_distribution_id(+)

and pod.po_line_id = pol.po_line_id (+)

and pol.item_id = esib.inventory_item_id (+)

and pod.destination_organization_id = esib.organization_id(+)

and ai.vendor_site_id = suppliersites.vendor_site_id

AND ai.vendor_id = supplier.vendor_id

AND suppliersites.VENDOR_ID = supplier.VENDOR_ID

AND supplier.party_id = suppliername.party_id

and ai.org_id = bu.organization_id

and ai.org_id = asp.org_id

and trunc(sysdate) between bu.effective_start_date and bu.effective_end_date

and aid.posted_flag = 'Y'

AND POL.category_id = PIC.purchasing_cat_id (+)

AND PIC.type (+) = 'SOURCING'

AND AID.dist_code_combination_id = GCC.code_combination_id (+)

AND AID.invoice_id = AIL.invoice_id

AND AID.invoice_line_number = AIL.line_number

AND AIL.ship_to_location_id = HRL.location_id (+)

AND PIC.level1_cat_id = PC1.category_id (+)

AND PC1.language (+) = userenv('LANG')

AND PIC.level2_cat_id = PC2.category_id (+)

AND PC2.language (+) = userenv('LANG')

AND PIC.level3_cat_id = PC3.category_id (+)

AND PC3.language (+) = userenv('LANG')

AND PIC.level4_cat_id = PC4.category_id (+)

AND PC4.language (+) = userenv('LANG')

AND PIC.level5_cat_id = PC5.category_id (+)

AND PC5.language (+) = userenv('LANG')

AND PIC.level6_cat_id = PC6.category_id (+)

AND PC6.language (+) = userenv('LANG')

AND PIC.level7_cat_id = PC7.category_id (+)

AND PC7.language (+) = userenv('LANG')

AND PIC.level8_cat_id = PC8.category_id (+)

AND PC8.language (+) = userenv('LANG')

AND PIC.level9_cat_id = PC9.category_id (+)

AND PC9.language (+) = userenv('LANG')

AND PIC.level10_cat_id = PC10.category_id (+)

AND PC10.language (+) = userenv('LANG')

AND AI.legal_entity_id = XEP.legal_entity_id

AND HRL.ACTIVE_STATUS (+) = 'A'

AND HRL.SHIP_TO_SITE_FLAG (+) = 'Y'