[Home] [Help]
57: * 1. Get all distinct processes of the given phase from az_processes_all_v and
58: * az_flow_phases_v into the intermediate table.
59: * 2. Find all distinct parent ids for the processes found in Step 1.
60: * 3. For each parent id in Step 2, get all distinct hierarchy ancestors
61: * in az_groups_v into the intermediate table.
62: *-----------------------------------------------------------------------*/
63: PROCEDURE processes IS
64: CURSOR all_groups_cursor IS
65: SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id
67: ,TO_CHAR(ag.display_order, '0000')||'.'||
68: agv.hierarchy_parent_id parent_node_id
69: ,agv.status
70: ,fl.meaning status_display_name
71: FROM az_groups_v agv
72: ,az_groups ag
73: ,fnd_lookups fl
74: WHERE agv.hierarchy_parent_id = ag.group_id
75: AND agv.status = fl.lookup_code
79: ,agv.display_name
80: ,agv.hierarchy_parent_id parent_node_id
81: ,agv.status
82: ,fl.meaning status_display_name
83: FROM az_groups_v agv
84: ,fnd_lookups fl
85: WHERE agv.hierarchy_parent_id is NULL
86: AND agv.status = fl.lookup_code
87: AND fl.lookup_type = 'AZ_PROCESS_STATUS';