Thursday, 31 August 2017

Enabling Link to access Excel file in iProcurement

Enabling Link to access Excel file in iProcurement  
                        Oracle iprocurement module is widely used by oracle users to raise catalogue and non-catalogue Purchase Requisitions. IProcurment module is very user friendly screen to raise requisitions and hence it is most used module. 
                        We have a requirement from one of our customer to show the Approved Supplier List in Oracle iProcurement Page itself when the user Clicks a link under “Purchasing News” Area.  The customer also wants to show below details along with the approved Suppliers list.
·         iProcurement Store Name
·         iProcurement Smart Form Name
·         Shopping Category mapped to the Smart Form
·         Supplier Name mapped to the Smart form
·         Operating unit associated to the Store

                        This document describes the Steps to get the above mentioned details and the steps to create a link in iProcurement Page under “Purchasing News” Area.    The excel sheet which is stored in the database will open when the user clicks the link in iprocurement Page.

Benefit of this enhancement:


·         Helps the user
o   to identify the appropriate Smart form to use for a Specific category
o   to check the approved Suppliers for a specific category
o   to identify the required Smart Form name to select for the category  
·         It reduces the wrong category and Supplier combinations to raise requisitions

Getting the required data & Create a Link

Step1: Write a Query and execute it to get the required data


                Write a SQL query to get the required data from the database which need to be stored in the database and to retrive when the users click the link. 

For examble, below query is used to obtain the above data

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 IN (210, 2616)           
            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


The Sample output will be as below.


Step2:   Store the File in $OA_HTML location

Copy the ASL.xls file to $OA_HTML path in the instance.

Step3:   Forms Personalization in iProc Home Page.

Please follow the instructions below to get the forms personalization’s done.

Step 3A: Set the following Profile Options using System Administrator Responsibility at User Level.

User: ABCDE



Step 3B: Open iProcurement Home Page

Navigation: iProcurement > iProcurement Home Page

FND: Personalization Region Link Enabled
Yes
FND: Personalization Seeding Mode
Yes
Personalize Self-Service Defn
Yes
Utilities:Diagnostics
Yes

Open the Personalize Stack Layout (Purchasing News)



Step 3C: Add Approved Supplier List to the Purchasing News Section

Create Item under the Purchasing News (Content Container: Purchasing News)




Create the Link with the following attributes:
Level
Item Style
Site
Link

Property
Value
ID
UK_Approved_Supplier_List
Admin Personialization
True
Destination URI
Disable Server Side Validation
False
Popup Enabled
True
Popup Render Event
Onclick
Rendered
True
Text
UK – Approved Supplier List
User personalization
False
Warn about changes
True


Approved Supplier List Link is added.



Step 3D: Personalize the Link to restrict to XX_OU.




2 comments:

  1. DreamHost is definitely the best hosting provider for any hosting services you require.

    ReplyDelete
  2. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (no matter why you broke up) you gotta watch this video
    right away...

    (VIDEO) Get your ex back with TEXT messages?

    ReplyDelete