[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;