SQL Instance code: Take the column tree, filter user rights and invalid columns

The article by Netizen WHL feeds, hereby thanks!
/**
* Desc: Take the column tree, filter user rights and invalid columns
* AUTHOR:WHL
* Date:2009-05-31 15:17
*/


/** 1. A tree in which a user has permission (np_cms_column_security table has records and t.action_1 = ' 1′ ') **/ Createorreplace viewv_np_ctree_bs as Selectb.* from ( Selecta.*, Lag (a.column_id) over (partition by a.column_id ORDER by 0) RK From ( Select/*+choose/* t.* From np_cms_column t wheret.is_active =' 1 ' " Connect by priort.column_id = t.parent_id start with t.column_id in ( Selectt.column_id From np_cms_column_security t wheret.subject_id =' mazj ' 12./* Add role Filter here/* t.action_1 = ' 1 ') A) B whereNOT exists ( select0 From ( Select distinctd.column_id From np_cms_column D Connect by priord.column_id = d.parent_id start with d.column_id in ( selectt.column_id From np_cms_column_security t wheret.subject_id =' mazj ' 23./* Add role Filter here/* t.action_1 = ' 0 ' 25./* Excluding the permission tree under the authorization ID, both action_1=0 the * * exists. ( select0 From ( Select distinctd.column_id From np_cms_column D Connect by priord.column_id = d.parent_id start with d.column_id in ( selectt.column_id From np_cms_column_security t wheret.subject_id = ' mazj ' 37./* Add role Filter here/* t.action_1 = ' 1 ') C1 wherec1.column_id = t.column_id)) d.is_active = ' 1 ') C wherec.column_id = b.column_id and B.rkis null) and B.rkis null Union All Selectc.*, 0 RK from Np_cms_column c wherec.parent_id = 0;

————————————————————————
/** 2. Get the virtual Father ID of the column (taking into account the node of the fault) **/

Createorreplace viewv_np_ctree_pa as Selectb.*, (case b.column_id 1 then0 elsenvl (b.father, 1) end) VFA A. from ( selectv.*, ( selectvv.column_id modified from v_np_ctree_bs vv wherevv.column_id = v.parent_id) FATHER A . from V_np_ctree_bs V) B;

————————————————————————
/** 3. Remove the column tree **/the portal needs

1.--create or replace view v_np_ctree_rs as 2. Select 3. d.*, Lpad (', 2 * level -1) sys_connect_by_path (d.column_name,'/') "PATH" 4. from ( selectc.* 5. from V_np_ctree_pa c 6. ORDER by C.VFA, C.disorder desc, c.column_id desc) D 7. Connect by priord.column_id = D.VFA 8. Start with d.column_id = 1;