Wednesday 21 December 2016

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

3 comments:

  1. Such a nice blog, I really like what you write in this blog, I also have some relevant information about if you want more information.

    Big Data and Hadoop Online Training

    ReplyDelete
  2. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Spark and Scala Online Training

    ReplyDelete