Saturday, September 21, 2019

Query to fetch Direct Punchout configuration details in R12













Brief intro to Direct Punchout:

Direct punchout is a very useful out of box solution from Oracle apps.
Ø  A user can login into Oracle iProcurement and click a link which will re-direct them to Supplier website.
Ø  Requester can search for items and add to the cart in supplier webshop.
Ø  When checkout button is clicked, the cart is return back to Oracle iprocurement and Requisition screen comes with all items which requester chosen from supplier webshop.

Technically, it’s based on a simple HTTP post REST API’s. But, from user perspective, it creates delight.

Functionally, from Oracle application perspective, we configure content zones and store. Few cataegory and UOM mapping at XML gateway level. 

We have prepared the below query to fetch all details except password from backend. This we use it for setup review and debugging in case of any issues reported in production.

Query 1: Content Zone and Store setup details

SELECT icczv.zone_id,
       icczv.name   as zone_name,
       icczv.type   as zone_type_code,
       flv.meaning  as zone_type_meaning,
       DECODE(NVL(icpzd.protocol_supported, 'NON_EXCHANGE'),
              'EXCHANGE', 1,
              0) as is_exchange_punchout_zone,
       icx_cat_store_assignment_pkg.get_store_assignment
       ('CONTENT_ZONE', icczv.zone_id) as assigned_store,
       icczv.url,
       icczv.image,
       icczv.supplier_attribute_action_flag,
       icczv.category_attribute_action_flag,
       icczv.items_without_shop_catg_flag,
       icczv.name,
       icczv.description,
       icczv.keywords,
       icpzd.protocol_supported,
       icpzd.user_name,
       icpzd.company_name,
       icpzd.company_number,
       icpzd.supplier_name,
       icpzd.supplier_number,
       icpzd.vendor_id,
       icpzd.vendor_site_id,
       icpzd.encoding,
       icpzd.party_site_id,
       icpzd.parent_zone_id,
       icpzd.authenticated_key,
       icpzd.user_info_flag,
       icpzd.lock_item_flag,
       icpzd.retain_session_flag,
       icpzd.negotiated_flag,
       icpzd.ecgateway_map_key1,
       pv.vendor_name AS vendor_name,
       pvs.vendor_site_code AS vendor_site_code,
       icpzd.endeca_punchout_option,
       icpzd.user_to_be_notified,
       fnduser.user_name usernametobenotified,
       icpzd.image_directory,
       icpzd.tp_detail_id,
       etd.source_tp_location_code,
       (SELECT listagg(hou.short_code,',') within group (order by hou.organization_id desc)
          FROM hr_operating_units hou,
               icx_cat_secure_contents icsc
         WHERE hou.organization_id = icsc.org_id
           and secure_by  = 'OPERATING_UNIT'
           and icsc.CONTENT_ID = icpzd.zone_id
       ) mapped_ou    
  FROM icx_cat_punchout_zone_details icpzd,
       icx_cat_content_zones_vl      icczv,
       fnd_lookup_values             flv,
       po_vendors                    pv,
       po_vendor_sites_all           pvs,
       fnd_user                      fnduser,
       ecx_tp_details                etd
 WHERE icpzd.vendor_id           = pv.vendor_id(+)
   AND icpzd.vendor_site_id      = pvs.vendor_site_id(+)
   and icpzd.user_to_be_notified = fnduser.user_id(+)
   and icpzd.tp_detail_id        = etd.tp_detail_id(+)
   AND protocol_supported        = 'XML_SUPPLIER'
   AND icczv.ZONE_ID             = icpzd.ZONE_ID (+)
   AND flv.LOOKUP_TYPE           = 'ICX_CAT_CONTENT_ZONE_TYPES'
   AND flv.LOOKUP_CODE           = icczv.TYPE
   AND flv.LANGUAGE              = USERENV('LANG');

Query 2: Mapping setup details

SELECT xref_key1 KEY1,
       direction,
       xref_category_code category,
       xref_int_value internal_value,
       xref_ext_value1 external_value
  FROM ECE_XREF_DATA
 WHERE XREF_KEY1 = :KEY_NAME
;

Hope it will be useful!!


Saturday, September 21, 2019 by Team search · 0

Sunday, September 8, 2019

API to update user Mail preference (FND_PREFERENCE.put) (Table: FND_USER_PREFERENCS)













In this post, we have given you an API to update User preferences. This helps you update any preferences of User like country, Language, Display name, Mail preference etc.



Script:

BEGIN
  FND_PREFERENCE.put('SHAREORCLEAPPS', 'WF', 'MAILTYPE', 'DISABLED');
END;

Table which stores user preferences information


Name
Req
Comments
USER_NAME
Yes
Internal name of user who owns this preference setting
MODULE_NAME
Yes
Module that references this preference
PREFERENCE_NAME
Yes
Preference name
PREFERENCE_VALUE

Preference value



Hope it helps!!

Sunday, September 8, 2019 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.