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!!
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “Query to fetch Direct Punchout configuration details in R12”
February 11, 2021 at 4:21 AM
Dear Sir
Thanks a lot.
Post a Comment