Wednesday, June 29, 2016

Favorite List or Public List Query in iProcurement Oracle Applications

SELECT   fu.user_name, fav_hdrs.favorite_list_id, primary_flag, fav_hdrs.favorite_list_name,
         fav_lines.favorite_list_line_id, ctx_hdrs.po_header_id,
         fav_lines.po_line_id, fav_lines.inventory_item_id,
         fav_lines.req_template_name, fav_lines.req_template_line_num,msi.segment1 Item_name, msi.description item_description,
         fav_lines.org_id, fav_lines.LANGUAGE, sup.VENDOR_NAME supplier_name,sups.VENDOR_SITE_CODE
    FROM icx_cat_fav_list_headers fav_hdrs,
         icx_cat_fav_list_lines_tlp fav_lines,
         icx_cat_items_ctx_hdrs_tlp ctx_hdrs,
         fnd_user fu,
         po_headers_all ph,
         AP_SUPPLIERS sup,
         AP_SUPPLIER_SITES_ALL sups,
         mtl_system_items_b msi
   WHERE fav_hdrs.employee_id = fu.EMPLOYEE_ID --&employeeid
     AND fav_lines.org_id = 82
     AND fav_lines.LANGUAGE = userenv('LANG')
     AND fav_hdrs.favorite_list_id = fav_lines.favorite_list_id
     AND fav_lines.po_line_id = ctx_hdrs.po_line_id(+)
     AND fav_lines.inventory_item_id = ctx_hdrs.inventory_item_id(+)
     AND fav_lines.req_template_name = ctx_hdrs.req_template_name(+)
     AND fav_lines.req_template_line_num = ctx_hdrs.req_template_line_num(+)
     AND fav_lines.org_id = ctx_hdrs.org_id(+)
     AND fav_lines.LANGUAGE = ctx_hdrs.LANGUAGE(+)
     and fu.user_name = nvl(:B, fu.user_name)
     AND ctx_hdrs.po_header_id = ph.po_header_id(+)
     and ph.vendor_id = sup.vendor_id (+)
     and ph.VENDOR_SITE_ID = sups.VENDOR_SITE_ID(+)
     and fav_lines.inventory_item_id = msi.inventory_item_id (+)
     AND  msi.organization_id = 84
ORDER BY fav_hdrs.favorite_list_id, fav_lines.favorite_list_line_id;

No comments:

Post a Comment