Query to extract iProc Smart Forms

SELECT DISTINCT STORE.NAME
store_name, smartforms.template_name request_name,
pas.commodity_id,
pas.vendor_name supplier,
(SELECT NAME
FROM apps.hr_operating_units
WHERE organization_id = smartforms.org_id) operating_unit,
(SELECT segment1 || ‘.’ || segment2
FROM apps.mtl_categories_b
WHERE category_id = smartforms.category_id) CATEGORY
FROM apps.icx_cat_shop_stores_vl STORE,
apps.icx_cat_store_contents store_contents,
apps.por_noncat_templates_all_vl smartforms,
apps.po_asl_suppliers_v pas
WHERE store_contents.store_id = STORE.store_id(+)
AND smartforms.template_id = store_contents.content_id(+)
AND smartforms.org_id = :org_id
AND pas.commodity_id = smartforms.category_id
AND pas.owning_organization_id IN (
SELECT organization_id
FROM apps.org_organization_definitions
WHERE operating_unit =
smartforms.org_id)
ORDER BY CATEGORY
  • December 21, 2016 | 24 views