[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_PLISTS_OPPS_ENUMS_PVT
Source
1 PACKAGE BODY AST_UWQ_PLISTS_OPPS_ENUMS_PVT as
2 /* $Header: ASTENPOB.pls 115.10 2004/08/10 06:41:27 rkumares noship $ */
3
4 -- Sub-Program Unit
5 PROCEDURE ENUMERATE_PLISTS_OPPS_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(300);
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
20 l_Access varchar2(10);
21 l_OrgID number;
22
23
24 CURSOR c_opp_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 = 'OPPORTUNITY'
34 and enabled_flag = 'Y' and
35 list_source='UWQ'
36 order by creation_date desc;
37
38 lkp_type VARCHAR2(30) := 'AST_UWQ_LABELS';
39 lkp_code VARCHAR2(30) := 'PLIST_OPPS_WORK_CLASS_LABEL';
40
41 BEGIN
42 /*
43 --Set node counter
44 -- Obtain label of nodes from ast_lookup to be attached to node/sub-node.
45 -- choice of label, view, where clause to be taken from enum node registered table
46 */
47
48 l_node_counter := 0;
49
50 Select meaning into l_node_label
51 from ast_lookups
52 where lookup_type = lkp_type
53 and lookup_code = lkp_code;
54
55 l_Access := NVL(FND_PROFILE.VALUE('AS_OPP_ACCESS'), 'T');
56 l_OrgID := FND_PROFILE.VALUE('ORG_ID');
57
58 --Build root node with common definitions given such as node label obtained, view name, data source, etc.
59
60 --Build root node with common definitions given such as node label obtained, view name, data source, etc.
61 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
62 l_bind_list(1).bind_var_value := P_RESOURCE_ID;
63 l_bind_list(1).bind_var_data_type := 'NUMBER';
64
65 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
66 l_tk_list(l_node_counter).VIEW_NAME := 'AST_PLIST_NAMES_UWQ_V';
67 l_tk_list(l_node_counter).DATA_SOURCE := 'AST_PLIST_NAMES_UWQ_DS';
68 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
69 l_tk_list(l_node_counter).WHERE_CLAUSE := 'list_type = ''MANUAL'' and list_source_type = ''OPPORTUNITY'' ' ||
70 'and list_source = ''UWQ'' AND enabled_flag = ''Y'' and resource_id = :resource_id';
71 l_tk_list(l_node_counter).NODE_TYPE := 0;
72 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
73 l_tk_list(l_node_counter).NODE_DEPTH := 1;
74 l_tk_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list);
75 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
76
77
78 l_node_counter := l_node_counter + 1;
79
80 SAVEPOINT start_opp_enumeration;
81
82 --Now loop through and build the subnodes based on the cursor that contains the names of the lists.
83
84 FOR cur_rec IN c_opp_lists(P_RESOURCE_ID) LOOP
85
86 -- insert the bind variable names and values into l_bind_list
87 --and then call the function ieu_pub.setBindVar to get the String
88
89 l_bind_list(1).bind_var_name := ':resource_id';
90 l_bind_list(1).bind_var_value := P_RESOURCE_ID;
91 l_bind_list(1).bind_var_data_type := 'NUMBER';
92 l_bind_list(2).bind_var_name := ':list_header_id';
93 l_bind_list(2).bind_var_value := cur_rec.list_header_id;
94 l_bind_list(2).bind_var_data_type := 'NUMBER';
95
96 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.LIST_NAME;
97
98 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and list_header_id = :list_header_id ';
99
100 l_tk_list(l_node_counter).VIEW_NAME := 'AST_PLIST_OPP_UWQ_V';
101 l_tk_list(l_node_counter).DATA_SOURCE := 'AST_PLIST_OPPS_UWQ_DS';
102
103 --
104
105
106
107 /* Need to check for security */
108
109 if l_access = 'O' then
110 l_bind_list(3).bind_var_name := ':ORG_ID' ;
111 l_bind_list(3).bind_var_value := l_OrgID ;
112 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
113 l_tk_list(l_node_counter).WHERE_CLAUSE :=
114 l_tk_list(l_node_counter).WHERE_CLAUSE ||
115 ' and org_id = :org_id ';
116 elsif l_access = 'T' then
117 l_tk_list(l_node_counter).WHERE_CLAUSE :=
118 l_tk_list(l_node_counter).WHERE_CLAUSE ||
119 ' and EXISTS (SELECT /*+ no_unnest */ 1 ' ||
120 ' FROM AS_ACCESSES_ALL ASS ' ||
121 ' WHERE ASS.SALESFORCE_ID = :resource_id ' ||
122 ' AND AST_PLIST_OPP_UWQ_V.CUSTOMER_ID = ASS.CUSTOMER_ID ' ||
123 ' AND AST_PLIST_OPP_UWQ_V.LEAD_ID = ASS.LEAD_ID) ';
124 end if;
125
126 /** added the view name AST_PLIST_OPP_UWQ_V above in line 135 and 136 for bug 3363154 magesh **/
127
128 if l_access <> 'O' THEN
129 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_PLIST_REF_UWQ_V';
130 else
131 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_PLIST_REF_OPP_UWQ_V';
132 end if;
133
134 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
135 l_tk_list(l_node_counter).NODE_TYPE := 0;
136 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
137 l_tk_list(l_node_counter).NODE_DEPTH := 2;
138 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_bind_list);
139 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
140
141 l_node_counter := l_node_counter + 1;
142
143 END LOOP;
144
145 IEU_PUB.ADD_UWQ_NODE_DATA
146 (P_RESOURCE_ID,
147 P_SEL_ENUM_ID,
148 l_tk_list
149 );
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 ROLLBACK TO start_opp_enumeration;
154 RAISE;
155
156 END ENUMERATE_PLISTS_OPPS_NODE;
157
158 -- PL/SQL Block
159 END AST_UWQ_PLISTS_OPPS_ENUMS_PVT;