3 Replies Latest reply: Aug 29, 2013 8:06 AM by 969483 RSS

    how to select iProcurement Stores?

    Ahmed_Samir

      hi there

       

      I need to select the stores(shop) that shown on iProcurement  home page .

       

      I need to know the tables and if there is a SQL statement to get it

       

      thank you

        • 1. Re: how to select iProcurement Stores?
          PranitSaha

          Hi,

           

          You will get the store details using the below script.

           

          Thanks,

           

          PS.

          • 2. Re: how to select iProcurement Stores?
            Ahmed_Samir

            where is the code ??

            • 3. Re: how to select iProcurement Stores?
              969483

              Here is some SQL I have used on 11i to find that out, it probably doesn't work in R12. I've added one for R12 right at the bottom:

               

              -- stores
              SELECT   icst.store_name
                     , icsb.store_type
                     , icst.creation_date
                     , fu.description 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
              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
              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
              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
                     , 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;
              
              --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(+);
              
              -- CATALOGUES
              SELECT   ipist.item_source_name catalog_name
                     , ipist.creation_date catalog_date
                     , pv.vendor_name supplier
                     , SUBSTR(
                          fu.description
                        , 0
                        , 30
                       ) created_by
                  FROM icx.icx_por_item_sources_tl ipist
                     , icx.icx_cat_item_src_details icisd
                     , po.po_vendors pv
                     , applsys.fnd_user fu
                 WHERE ipist.item_source_id = icisd.item_source_id
                   AND icisd.supplier_id = pv.vendor_id
                   AND ipist.created_by = fu.user_id
              ORDER BY 1
                     , 3;
              

               

               

               

              This returns data in R12:

               

              SELECT iccczb.TYPE
                   , iccczb.security_assignment_flag
                   , iccczb.creation_date
                   , iccczt.NAME
                   , iccczt.description
                   , pv.vendor_name supplier
                   , pvsa.vendor_site_code site
                FROM icx.icx_cat_content_zones_b iccczb
                   , icx.icx_cat_content_zones_tl iccczt
                   , icx.ICX_CAT_ZONE_SECURE_ATTRIBUTES iczsa
                   , ap.ap_suppliers pv
                   , ap.ap_supplier_sites_all pvsa
               WHERE iccczb.zone_id = iccczt.zone_id
                 AND iczsa.ZONE_ID = iccczb.ZONE_ID
                 AND iczsa.SUPPLIER_ID = pv.VENDOR_ID
                 and iczsa.SUPPLIER_SITE_ID = pvsa.VENDOR_SITE_ID
                 and pv.VENDOR_ID = pvsa.VENDOR_ID;