SELECT
CASE
WHEN row_number() over(PARTITION BY wit.display_name
|| ' ('
|| wit.name
|| ')' ORDER BY pro.name,wpa.instance_label,wpa.activity_name nulls LAST) = 1
THEN wit.display_name
|| ' ('
|| wit.name
|| ')'
ELSE NULL
END "Item Type" ,
CASE
WHEN row_number() over(PARTITION BY wit.display_name
|| ' ('
|| wit.name
|| ')' ,pro.name ORDER BY wpa.instance_label,wpa.activity_name nulls LAST) = 1
THEN pro.name
ELSE NULL
END "Process" ,
wpa.instance_label
|| ' ('
|| wpa.activity_name
|| ')' "Process Activity" ,
CASE
WHEN act.FUNCTION IS NOT NULL
THEN 'Function: '
|| act.function
WHEN act.message IS NOT NULL
THEN 'Message: '
|| act.message
ELSE NULL
END "Activity Detail"
FROM wf_item_types_tl wit ,
wf_activities pro ,
wf_process_activities wpa ,
wf_activities act
WHERE wit.name = pro.item_type
AND pro.TYPE = 'PROCESS'
AND pro.end_date IS NULL
AND wit.language = 'US'
AND pro.item_type = wpa.process_item_type
AND pro.VERSION = wpa.process_version
AND wpa.process_name = pro.name
AND wpa.activity_name = act.name
AND wpa.process_item_type = act.item_type
AND act.end_date IS NULL
--AND UPPER(wit.name) = :1
ORDER BY wit.display_name,
wit.name,
pro.name,
wpa.instance_label
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete