DBA Data[Home] [Help]

PACKAGE BODY: APPS.AST_UWQ_PLISTS_CONTS_ENUMS_PVT

Source


1 PACKAGE BODY AST_UWQ_PLISTS_CONTS_ENUMS_PVT as
2 /* $Header: ASTENPCB.pls 115.9 2004/08/10 06:41:17 rkumares noship $ */
3 
4 -- Sub-Program Unit
5 PROCEDURE ENUMERATE_PLISTS_CONTACTS_NODE
6   (P_RESOURCE_ID      IN NUMBER
7   ,P_LANGUAGE         IN VARCHAR2
8   ,P_SOURCE_LANG      IN VARCHAR2
9   ,P_SEL_ENUM_ID      IN NUMBER
10   )
11   AS
12   l_node_counter           		NUMBER;
13   l_node_label             		VARCHAR2(100);
14   l_node_pid               		NUMBER := 3100;
15   l_tk_list                		IEU_PUB.EnumeratorDataRecordList;
16   l_def_where              		VARCHAR2(20000);
17   l_bind_list 					IEU_PUB.BindVariableRecordList ;
18 
19   l_OrgID    number;
20   l_view_name	VARCHAR2(50);
21   l_ds_name	VARCHAR2(50);
22 
23 
24   CURSOR c_contact_lists(c_resource_id IN number)  IS
25   SELECT
26   alh.list_name,
27   alh.list_header_id,
28   alh.list_source_type
29   from ams_list_headers_vl alh
30   where
31   owner_user_id = c_resource_id
32   and list_type='MANUAL' and
33   list_source_type in ('CONTACT', 'CONSUMER')
34   and enabled_flag = 'Y' and
35   list_source='UWQ'
36   order by creation_date desc;
37 
38   l_Access  varchar2(10) ;
39 
40   lkp_type VARCHAR2(30) := 'AST_UWQ_LABELS';
41   lkp_code VARCHAR2(30) := 'PLIST_CONTS_WORK_CLASS_LABEL';
42 BEGIN
43 /*
44 --Set node counter
45 -- Obtain label of nodes from ast_lookup to be attached to node/sub-node.
46 -- choice of label, view, where clause to be taken from enum node registered table
47 */
48 
49      		l_node_counter := 0;
50 
51      		Select meaning into l_node_label
52           		from ast_lookups
53           		where lookup_type = lkp_type
54                 and lookup_code = lkp_code;
55 
56    l_Access := NVL(fnd_profile.value('AS_CUST_ACCESS'), 'T');
57 
58 --Build root node with common definitions given such as node label obtained, view name, data source, etc.
59   l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
60   l_bind_list(1).bind_var_value := P_RESOURCE_ID;
61   l_bind_list(1).bind_var_data_type := 'NUMBER';
62 
63   l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
64   l_tk_list(l_node_counter).VIEW_NAME := 'AST_PLIST_NAMES_UWQ_V';
65   l_tk_list(l_node_counter).DATA_SOURCE := 'AST_PLIST_NAMES_UWQ_DS';
66   l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
67   l_tk_list(l_node_counter).WHERE_CLAUSE := 'list_type = ''MANUAL'' and list_source_type in (''CONTACT'', ''CONSUMER'')' ||
68 'and list_source = ''UWQ'' AND enabled_flag = ''Y'' and resource_id  =  :resource_id';
69   l_tk_list(l_node_counter).NODE_TYPE := 0;
70   l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
71   l_tk_list(l_node_counter).NODE_DEPTH := 1;
72   l_tk_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list);
73   l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
74 
75   l_node_counter := l_node_counter + 1;
76 
77  SAVEPOINT start_pclist_node_enumeration;
78   --Now loop through and build the subnodes based on the cursor that contains the names of the lists.
79 
80     FOR cur_rec IN c_contact_lists(P_RESOURCE_ID) LOOP
81 
82 -- insert the bind variable names and values into l_bind_list
83 --and then call the function ieu_pub.setBindVar to get the String
84 
85       l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
86       l_bind_list(1).bind_var_value := P_RESOURCE_ID;
87       l_bind_list(1).bind_var_data_type := 'NUMBER';
88       l_bind_list(2).bind_var_name  := ':LIST_HEADER_ID';
89       l_bind_list(2).bind_var_value  := cur_rec.list_header_id;
90       l_bind_list(2).bind_var_data_type := 'NUMBER';
91 
92       l_tk_list(l_node_counter).NODE_LABEL := cur_rec.LIST_NAME;
93 
94 	  l_tk_list(l_node_counter).WHERE_CLAUSE :=  'resource_id = :resource_id ' ||
95 				'and list_header_id = :list_header_id and list_type = ''MANUAL''' ||
96                 'and list_source = ''UWQ'' and enabled_flag = ''Y''';
97 
98 	  --Now taking refresh view into account for list type views..
99         l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_PLIST_REF_UWQ_V';
100 
101    	  if cur_rec.list_source_type = 'CONTACT' then
102 
103       	 l_tk_list(l_node_counter).VIEW_NAME :=  'AST_PLIST_REL_UWQ_V';
104      	 l_tk_list(l_node_counter).DATA_SOURCE := 'AST_PLIST_REL_UWQ_DS';
105 		 l_tk_list(l_node_counter).WHERE_CLAUSE :=   l_tk_list(l_node_counter).WHERE_CLAUSE ||
106 		 ' and list_source_type in (''CONTACT'')';
107 
108       elsif  cur_rec.list_source_type = 'CONSUMER' then
109 
110       	 l_tk_list(l_node_counter).VIEW_NAME :=  'AST_PLIST_PERS_UWQ_V';
111       	 l_tk_list(l_node_counter).DATA_SOURCE := 'AST_PLIST_PERS_UWQ_DS';
112 		 l_tk_list(l_node_counter).WHERE_CLAUSE :=   l_tk_list(l_node_counter).WHERE_CLAUSE ||
113 		 ' and list_source_type in (''CONSUMER'')';
114 
115       end if;
116 
117 
118       /* Need to check for security */
119       if l_access = 'T' then
120 	  l_tk_list(l_node_counter).WHERE_CLAUSE :=  l_tk_list(l_node_counter).WHERE_CLAUSE ||
121 		 ' and EXISTS (SELECT /*+ no_unnest */ 1 ' ||
122             			' FROM AS_ACCESSES_ALL ASS ' ||
123             			' WHERE ASS.SALESFORCE_ID = :resource_id ' ||
124 				        ' AND CUSTOMER_ID = ASS.CUSTOMER_ID) ';
125       end if;
126 
127       l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
128       l_tk_list(l_node_counter).NODE_TYPE := 0;
129       l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
130       l_tk_list(l_node_counter).NODE_DEPTH := 2;
131       l_tk_list(l_node_counter).BIND_VARS  := ieu_pub.set_bind_var_data(l_bind_list);
132       l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
133 
134       l_node_counter := l_node_counter + 1;
135 
136     END LOOP;
137 
138   IEU_PUB.ADD_UWQ_NODE_DATA
139   (P_RESOURCE_ID,
140    P_SEL_ENUM_ID,
141    l_tk_list
142   );
143 
144 EXCEPTION
145   WHEN OTHERS THEN
146     ROLLBACK TO start_pclist_node_enumeration;
147     RAISE;
148 
149 END ENUMERATE_PLISTS_CONTACTS_NODE;
150 
151 -- PL/SQL Block
152 END AST_UWQ_PLISTS_CONTS_ENUMS_PVT;