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.




  • August 31, 2017 | 25 views