ICX Stores, Catalogues in Oracle Apps

-- stores v1

SELECT   icst.store_name
       , icsb.store_type
       , icst.creation_date
       , SUBSTR(fu.description, 0, 20) created_by
       , icst.short_description
       , icst.image_location
       , icsb.sequence_number
       , haout.NAME hr_org
    FROM icx.icx_cat_stores_tl icst
       , icx.icx_cat_stores_b icsb
       , applsys.fnd_user fu
       , hr.hr_all_organization_units_tl haout
       , icx.icx_cat_store_org_assignments icsoa
   WHERE icst.store_id = icsb.store_id
     AND icst.created_by = fu.user_id
     AND icsoa.store_id = icst.store_id
     AND icsoa.org_id = haout.organization_id(+)
ORDER BY icsb.sequence_number;

-- stores v2
SELECT icst.store_name
     , icsb.store_type
     , icst.short_description
     , icst.image_location
  FROM icx.icx_cat_stores_tl icst
     , icx.icx_cat_stores_b icsb
     , applsys.fnd_user fu
     , icx.icx_cat_store_org_assignments icsoa
 WHERE icst.store_id = icsb.store_id
   AND icst.created_by = fu.user_id
   AND icsoa.store_id = icst.store_id;

-- stores v3

SELECT   icsoa.org_id
       , icst.creation_date
       , icst.store_name
       , icsb.store_type
       , icst.short_description
       , icst.image_location
       , ipist.item_source_name catalog_name
    FROM icx.icx_cat_stores_tl icst
       , icx.icx_cat_stores_b icsb
       , applsys.fnd_user fu
       , icx.icx_cat_store_org_assignments icsoa
       , icx.icx_cat_store_catalogs icsc
       , icx.icx_por_item_sources ipis
       , icx.icx_por_item_sources_tl ipist
   WHERE icst.store_id = icsb.store_id
     AND icst.created_by = fu.user_id
     AND icsoa.store_id = icst.store_id
     AND icst.store_id = icsc.store_id
     AND ipist.item_source_id = icsc.item_source_id(+)
     AND ipis.item_source_id = ipist.item_source_id(+)
ORDER BY icst.creation_date DESC;

Stores linked to Catalogues

-- STORES LINKED TO CATALOGUES v1

SELECT   haout.NAME hr_org
       , icst.store_name
       , icsb.store_type
       , icst.short_description
       , icst.image_location
       , icsb.sequence_number
       , ipist.item_source_name catalog_name
       , icst.creation_date
       , SUBSTR(fu.description, 0, 20) created_by
       , icst.short_description
       , icsb.store_type
FROM     icx.icx_cat_stores_tl icst
       , icx.icx_cat_stores_b icsb
       , applsys.fnd_user fu
       , hr.hr_all_organization_units_tl haout
       , icx.icx_cat_store_org_assignments icsoa
       , icx.icx_cat_store_catalogs icsc
       , icx.icx_por_item_sources_tl ipist
   WHERE icst.store_id = icsb.store_id
     AND icst.created_by = fu.user_id
     AND icsoa.store_id = icst.store_id
     AND icsc.store_id = icst.store_id
     AND icsoa.org_id = haout.organization_id(+)
     AND ipist.item_source_id = icsc.item_source_id(+)
ORDER BY haout.NAME
       , icst.store_name
       , icsb.sequence_number;

-- STORES LINKED TO CATALOGUES v2

SELECT   haout.NAME hr_org
       , icst.store_name
       , icsb.store_type
       , icst.short_description
       , icst.image_location
       , ipist.item_source_name catalog_name
    FROM icx.icx_cat_stores_tl icst
       , icx.icx_cat_stores_b icsb
       , hr.hr_all_organization_units_tl haout
       , icx.icx_cat_store_org_assignments icsoa
       , icx.icx_cat_store_catalogs icsc
       , icx.icx_por_item_sources_tl ipist
   WHERE icst.store_id = icsb.store_id
     AND icsoa.store_id = icst.store_id
     AND icsc.store_id = icst.store_id
     AND icsoa.org_id = haout.organization_id(+)
     AND ipist.item_source_id = icsc.item_source_id(+)
ORDER BY haout.NAME
       , icst.store_name
       , icsb.sequence_number;

Catalogues

--CATALOGUES

SELECT ipist.item_source_name catalog_name
     , pv.vendor_name supplier
  FROM icx.icx_por_item_sources_tl ipist
     , icx.icx_cat_item_src_details icisd
     , po.po_vendors pv
 WHERE ipist.item_source_id = icisd.item_source_id
   AND icisd.supplier_id = pv.vendor_id;

-- CATALOGUES LINKED TO STORES
SELECT   haout.NAME hr_org
       , ipist.item_source_name catalog_name
       , DECODE(
            ipis.TYPE
          , 'LOCAL', 'Local'
          , 'INFO', 'Informational'
         ) TYPE
       , ipist.description cat_description
       , ipis.url catalog_url
       , icst.store_name linked_to_store
    FROM icx.icx_cat_stores_tl icst
       , icx.icx_cat_stores_b icsb
       , applsys.fnd_user fu
       , hr.hr_all_organization_units_tl haout
       , icx.icx_cat_store_org_assignments icsoa
       , icx.icx_cat_store_catalogs icsc
       , icx.icx_por_item_sources ipis
       , icx.icx_por_item_sources_tl ipist
   WHERE icst.store_id = icsb.store_id
     AND icst.created_by = fu.user_id
     AND icsoa.store_id = icst.store_id
     AND icsc.store_id = icst.store_id
     AND icsoa.org_id = haout.organization_id(+)
     AND ipist.item_source_id = icsc.item_source_id(+)
     AND ipis.item_source_id = ipist.item_source_id(+)
ORDER BY haout.NAME
       , icst.store_name
       , icsb.sequence_number;

-- CATALOGUES LINKED TO SUPPLIERS

SELECT DISTINCT haout.NAME hr_org
              , ipist.item_source_name catalog_name
              , DECODE(
                   ipis.TYPE
                 , 'LOCAL', 'Local'
                 , 'INFO', 'Informational'
                ) TYPE
              , ipist.description cat_description
              , ipis.url catalog_url
              , icst.store_name linked_to_store
              , pv.vendor_name linked_supplier
           FROM icx.icx_cat_stores_tl icst
              , icx.icx_cat_stores_b icsb
              , applsys.fnd_user fu
              , hr.hr_all_organization_units_tl haout
              , icx.icx_cat_store_org_assignments icsoa
              , icx.icx_cat_store_catalogs icsc
              , icx.icx_por_item_sources ipis
              , icx.icx_por_item_sources_tl ipist
              , icx.icx_cat_item_src_details icisd
              , apps.po_vendors pv
          WHERE icst.store_id = icsb.store_id
            AND icst.created_by = fu.user_id
            AND icsoa.store_id = icst.store_id
            AND icsc.store_id = icst.store_id
            AND icsoa.org_id = haout.organization_id(+)
            AND ipist.item_source_id = icsc.item_source_id(+)
            AND ipis.item_source_id = ipist.item_source_id
            AND ipist.item_source_id = icisd.item_source_id(+)
            AND icisd.supplier_id = pv.vendor_id(+)
            AND ipis.TYPE IN('INFO', 'LOCAL')
       ORDER BY 1
              , 2
              , pv.vendor_name;

-- STORES LINKED TO CATALOGUES LINKED TO SUPPLIERS

SELECT   haout.NAME org
       , ipist.item_source_name catalog
       , icst.store_name STORE
       , pv.vendor_name supplier
       , icst.creation_date
       , icst.short_description
    FROM icx.icx_cat_stores_tl icst
       , icx.icx_cat_stores_b icsb
       , hr.hr_all_organization_units_tl haout
       , icx.icx_cat_store_org_assignments icsoa
       , icx.icx_cat_store_catalogs icsc
       , icx.icx_por_item_sources_tl ipist
       , icx.icx_cat_item_src_details icisd
       , apps.po_vendors pv
   WHERE icst.store_id = icsb.store_id
     AND icsoa.store_id = icst.store_id
     AND icsc.store_id = icst.store_id
     AND icsoa.org_id = haout.organization_id(+)
     AND ipist.item_source_id = icsc.item_source_id(+)
     AND ipist.item_source_id = icisd.item_source_id
     AND icisd.supplier_id = pv.vendor_id
ORDER BY icsoa.creation_date DESC;

-- STORES LINKED TO CATALOGUES LINKED TO SUPPLIERS LINKED TO ITEMS DISTINCT
SELECT DISTINCT haout.NAME org
              , ipist.item_source_name catalog
              , icst.store_name STORE
              , pv.vendor_name supplier
              , icst.creation_date store_create_date
              , icst.short_description store_desc
           FROM icx.icx_cat_stores_tl icst
              , icx.icx_cat_stores_b icsb
              , hr.hr_all_organization_units_tl haout
              , icx.icx_cat_store_org_assignments icsoa
              , icx.icx_cat_store_catalogs icsc
              , icx.icx_por_item_sources_tl ipist
              , icx.icx_cat_item_src_details icisd
              , apps.po_vendors pv
              , apps.po_vendor_sites_all pvsa
              , icx.icx_cat_items_tlp iccit
              , icx.icx_cat_item_prices iccip
          WHERE icst.store_id = icsb.store_id
            AND icsoa.store_id = icst.store_id
            AND icsc.store_id = icst.store_id
            AND icsoa.org_id = haout.organization_id(+)
            AND ipist.item_source_id = icsc.item_source_id(+)
            AND ipist.item_source_id = icisd.item_source_id
            AND iccit.rt_item_id = iccip.rt_item_id
            AND iccit.supplier_id = pv.vendor_id
            AND iccip.supplier_site_id = pvsa.vendor_site_id
            AND icisd.supplier_id = pv.vendor_id
       ORDER BY 1
              , 2;

-- STORES LINKED TO CATALOGUES LINKED TO SUPPLIERS LINKED TO ITEMS
SELECT   haout.NAME org
       , ipist.item_source_name catalog
       , icst.store_name STORE
       , pv.vendor_name supplier
       , icst.creation_date store_create_date
       , icst.short_description store_desc
       , ' CATALOG DATA TO THE RIGHT -------> ' label
       , iccit.primary_category_name "Category"
       , iccit.supplier_part_num "Supplier Item"
       , iccit.description "Description"
       , iccip.unit_price "Unit Price"
    FROM icx.icx_cat_stores_tl icst
       , icx.icx_cat_stores_b icsb
       , hr.hr_all_organization_units_tl haout
       , icx.icx_cat_store_org_assignments icsoa
       , icx.icx_cat_store_catalogs icsc
       , icx.icx_por_item_sources_tl ipist
       , icx.icx_cat_item_src_details icisd
       , apps.po_vendors pv
       , apps.po_vendor_sites_all pvsa
       , icx.icx_cat_items_tlp iccit
       , icx.icx_cat_item_prices iccip
   WHERE icst.store_id = icsb.store_id
     AND icsoa.store_id = icst.store_id
     AND icsc.store_id = icst.store_id
     AND icsoa.org_id = haout.organization_id(+)
     AND ipist.item_source_id = icsc.item_source_id(+)
     AND ipist.item_source_id = icisd.item_source_id
     AND iccit.rt_item_id = iccip.rt_item_id
     AND iccit.supplier_id = pv.vendor_id
     AND iccip.supplier_site_id = pvsa.vendor_site_id
     AND icisd.supplier_id = pv.vendor_id
ORDER BY icsoa.creation_date DESC;

--NON CAT REQUEST TEMPLATES

SELECT pntab.org_id
     , pntat.template_name
     , pntab.item_type
     , pv.vendor_name
     , pvsa.vendor_site_code site
  FROM icx.por_noncat_templates_all_b pntab
     , icx.por_noncat_templates_all_tl pntat
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
 WHERE pntab.template_id = pntat.template_id
   AND pntab.supplier_id = pv.vendor_id(+)
   AND pntab.supplier_site_id = pvsa.vendor_site_id(+);
                

Functional DOC:

http://docs.oracle.com/cd/A60725_05/html/comnls/us/aic/icxarch.htm