0 Replies Latest reply on Aug 15, 2008 11:18 AM by Tapas Banerjee

    iProcurement : Relation between STORE_ID and INVENTORY_ITEM_ID

    Tapas Banerjee
      Hi Friends,

      I am trying to create a custom search page in iProcurement module which will do the similar search as of Shop->Stores->Search option. Only difference is this search result region will contain one addition item 'PO Description'.

      I have added this addition field in the VO query, but facing issue while Joining STORE_ID with ICX_CAT_ITEMS_CTX_HDRS_TLP table. I am not finding any direct relationship between ICX_CAT_SHOP_STORES_VL.STORE_ID and ICX_CAT_ITEMS_CTX_HDRS_TLP.INVENTORY_ITEM_ID.

      We are using the following query for fetching the items from Main Stores (STORE_ID = 1).

      SELECT distinct nvl(avtlp.description, bq.description) DESCRIPTION,
      bq.ip_category_name SHOPPING_CATEGORY,
      pvs.vendor_site_code SUPPLIER_SITE,
      msikfv.concatenated_segments INTERNAL_ITEM_NUM,
      avtlp.manufacturer MANUFACTURER,
      bq.unit_price PRICE,
      bq.supplier SUPPLIER,
      DECODE(bq.source_type,'TEMPLATE',decode(least(length(bq.req_template_name),20),20,substr(bq.req_template_name,1,17) ||'...',bq.req_template_name),'INTERNAL_TEMPLATE',decode(least(length(bq.req_template_name) , 20),20,substr(bq.req_template_name,1,17) ||'...',bq.req_template_name),'QUOTATION',ICX_CAT_UTIL_PVT.get_message('ICX_CAT_QUOTATION_SOURCE','NUMBER',bq.document_number) ,'BLANKET',ICX_CAT_UTIL_PVT.get_message('ICX_CAT_BLANKET_SOURCE','NUMBER',bq.document_number),'GLOBAL_BLANKET',ICX_CAT_UTIL_PVT.get_message('ICX_CAT_BLANKET_SOURCE','NUMBER',bq.document_number), null) SOURCE,
      bq.inventory_item_id ItemId,
      nvl(bq.suggested_quantity, 1) Quantity,
      pha.COMMENTS, msikfv.organization_id
      , get_store.store_id
      FROM
      icx_cat_items_ctx_hdrs_tlp bq,
      po_system_parameters_all psp,
      mtl_units_of_measure_tl muomtl,
      icx_cat_attribute_values av,
      icx_cat_attribute_values_tlp avtlp,
      po_vendor_sites_all pvs,
      mtl_categories_kfv mck,
      mtl_system_items_kfv msikfv,
      po_headers_all pha
      ,
      (SELECT 1 store_id -- for Main Store
      , NULL supplier_id
      , NULL supplier_site_id
      , NULL category_id

      FROM DUAL) get_store
      WHERE (bq.source_type <> 'MASTER_ITEM')
      AND psp.org_id = :ORG_ID_KEY2
      AND bq.unit_meas_lookup_code = muomtl.unit_of_measure(+)
      AND bq.language = muomtl.language(+)
      AND bq.inventory_item_id = av.inventory_item_id(+)
      AND bq.owning_org_id = av.org_id(+)
      AND bq.po_line_id = av.po_line_id(+)
      AND bq.req_template_name = av.req_template_name(+)
      AND bq.req_template_line_num = av.req_template_line_num(+)
      AND bq.inventory_item_id = avtlp.inventory_item_id(+)
      AND bq.owning_org_id = avtlp.org_id(+)
      AND bq.po_line_id = avtlp.po_line_id(+)
      AND bq.req_template_name = avtlp.req_template_name(+)
      AND bq.req_template_line_num = avtlp.req_template_line_num(+)
      AND bq.language = avtlp.language(+)
      AND bq.supplier_site_id = pvs.vendor_site_id(+)
      AND bq.po_category_id = mck.category_id(+)
      AND (bq.supplier_site_id = get_store.supplier_site_id OR get_store.supplier_site_id is NULL )
      AND (bq.po_category_id = get_store.category_id OR get_store.category_id is NULL)
      AND (get_store.supplier_id is null or get_store.supplier_id =bq.supplier_id)
      AND bq.inventory_item_id = msikfv.inventory_item_id(+)
      AND msikfv.organization_id(+) = :INV_ORG_ID_KEY1
      and bq.po_header_id=pha.po_header_id(+)
      and get_store.store_id = :STORE_ID
      AND msikfv.concatenated_segments like '1235%'

      But this query is not returning the correct no of records.

      Any kind of new thought will be truly appreciated.

      Message was edited by:
      Tapas Banerjee