[Home] [Help]
PACKAGE BODY: APPS.CN_SALES_HIER_PUB
Source
1 PACKAGE BODY cn_sales_hier_pub AS
2 --$Header: cnphierb.pls 115.3 2002/11/21 21:04:01 hlchen ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SALES_HIER_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnphierb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12
13
14
15
16 PROCEDURE get_sales_hier
17 (
18 p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
20 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
21
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 x_loading_status OUT NOCOPY VARCHAR2,
26
27 p_salesrep_id IN NUMBER ,
28 p_comp_group_id IN NUMBER,
29 p_date IN DATE,
30 p_start_record IN NUMBER := 1,
31 p_increment_count IN NUMBER,
32 p_start_record_grp IN NUMBER := 1,
33 p_increment_count_grp IN NUMBER,
34
35 x_mgr_tbl OUT NOCOPY hier_tbl_type,
36 x_mgr_count OUT NOCOPY NUMBER,
37 x_srp_tbl OUT NOCOPY hier_tbl_type,
38 x_srp_count OUT NOCOPY NUMBER,
39 x_grp_tbl OUT NOCOPY grp_tbl_type,
40 x_grp_count OUT NOCOPY NUMBER
41 )
42
43
44 IS
45
46 l_api_name CONSTANT VARCHAR2(30) := 'get_sales_hier';
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_comp_group_name VARCHAR2(100);
49 l_comp_group_id NUMBER;
50 l_counter NUMBER;
51
52 -- Comp Group Query
53 -- Get comp group id and name based on the given
54 -- P_SALESREP_ID
55 -- P_COMP_GROUP_ID
56 -- P_DATE
57
58 CURSOR comp_group_cur(
59 l_salesrep_id IN NUMBER,
60 l_comp_group_id IN NUMBER,
61 l_date IN DATE
62 ) IS
63
64 SELECT distinct cg.name comp_group_name, cscg.comp_group_id comp_group_id
65 FROM
66 cn_srp_comp_groups_v cscg,
67 cn_comp_groups cg
68 WHERE
69 ((l_salesrep_id = -9999 ) OR (cscg.salesrep_id = l_salesrep_id)) AND
70 cscg.comp_group_id = l_comp_group_id AND
71 cscg.start_date_active <= l_date and
72 ((cscg.end_date_active is null) OR
73 (cscg.end_date_active >= l_date)) AND
74 cg.comp_group_id = cscg.comp_group_id ;
75
76 --+
77 -- Sub Query 1
78 -- To get the manager name, role name based on
79 -- the comp group id from Main Query
80 --+
81
82 CURSOR mgr_cur(
83 l_comp_group_id IN NUMBER,
84 l_date IN DATE
85 )IS
86
87 SELECT
88 cs.name mgr_name,
89 cs.employee_number mgr_number,
90 cscg.role_name mgr_role,
91 cscg.start_date_active mgr_start_date,
92 cscg.end_date_active mgr_end_date
93
94 FROM
95 cn_srp_comp_groups_v cscg,
96 cn_salesreps cs
97 WHERE
98 cscg.comp_group_id = l_comp_group_id AND -- (master-detail passed in value)
99 ((cscg.end_date_active is null) OR
100 (cscg.end_date_active >= l_date)) AND
101 cscg.manager_flag = 'Y' AND
102 cscg.salesrep_id = cs.salesrep_id;
103
104 -- +
105 -- Sub Query 2
106 -- To get the salesrep name, role name based on
107 -- the comp group id from Main Query
108 --+
109 CURSOR srp_cur(
110 l_comp_group_id IN NUMBER,
111 l_date IN DATE
112 )IS
113 SELECT
114 cs.name srp_name ,
115 cs.employee_number srp_number,
116 cscg.role_name srp_role,
117 cscg.start_date_active srp_start_date,
118 cscg.end_date_active srp_end_date
119 FROM
120 cn_srp_comp_groups_v cscg,
121 cn_salesreps cs
122 WHERE
123 cscg.comp_group_id = l_comp_group_id AND -- (master-detail passed value)
124 cscg.salesrep_id = cs.salesrep_id AND
125 cscg.start_date_active <= l_date AND
126 ((cscg.end_date_active is null) OR
127 (cscg.end_date_active >= l_date));
128
129
130 -- Sub Query 3
131 -- To get the direct comp group(s)
132
133
134 CURSOR sub_grp_cur (
135 l_comp_group_id IN NUMBER,
136 l_date IN DATE
137 ) IS
138 SELECT
139 hier.comp_group_id group_id, cg.name group_name
140 FROM
141 cn_comp_group_hier hier,
142 cn_comp_groups cg
143 WHERE
144 hier.parent_comp_group_id = l_comp_group_id and
145 hier.comp_group_id = cg.comp_group_id and
146 delete_flag <> 'Y' and
147 hier.start_date_active <= l_date and
148 ((hier.end_date_active is null) or
149 (hier.end_date_active >= l_date));
150
151
152
153
154
155 BEGIN
156
157 --+
158 -- Standard call to check for call compatibility.
159 --+
160 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
161 p_api_version ,
162 l_api_name,
163 G_PKG_NAME )
164 THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167
168 --+
169 -- Initialize message list if p_init_msg_list is set to TRUE.
170 --+
171 IF FND_API.to_Boolean( p_init_msg_list ) THEN
172 FND_MSG_PUB.initialize;
173 END IF;
174
175 --+
176 -- Initialize API return status to success
177 --+
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179 x_loading_status := 'CN_INSERTED';
180
181 --+
182 -- API body
183 --+
184
185
186 x_mgr_count := 0;
187 x_srp_count := 0;
188 x_grp_count := 0;
189 l_counter := 0;
190
191 -- get comp_group_id and comp_group_name first
192 FOR comp_group IN comp_group_cur(p_salesrep_id,p_comp_group_id,p_date)
193 LOOP
194 -- get manager_id and manager name
195 FOR mgr IN mgr_cur(comp_group.comp_group_id,p_date)
196 LOOP
197 x_mgr_count := x_mgr_count+1;
198 l_counter := l_counter +1;
199 IF (( p_increment_count = -9999) OR (l_counter BETWEEN p_start_record
200 AND (p_start_record + p_increment_count -1)))
201 THEN
202 x_mgr_tbl(x_mgr_count).name := mgr.mgr_name;
203 x_mgr_tbl(x_mgr_count).number :=mgr.mgr_number;
204 x_mgr_tbl(x_mgr_count).role :=mgr.mgr_role;
205 x_mgr_tbl(x_mgr_count).start_date :=mgr.mgr_start_date;
206 x_mgr_tbl(x_mgr_count).end_date :=mgr.mgr_end_date;
207 END IF;
208 END LOOP;
209
210 FOR srp IN srp_cur(comp_group.comp_group_id,p_date)
211 LOOP
212 x_srp_count := x_srp_count+1;
213 l_counter := l_counter +1;
214 IF (( p_increment_count = -9999) OR (l_counter BETWEEN p_start_record
215 AND (p_start_record + p_increment_count -1)))
216 THEN
217 x_srp_tbl(x_srp_count).name := srp.srp_name;
218 x_srp_tbl(x_srp_count).number :=srp.srp_number;
219 x_srp_tbl(x_srp_count).role :=srp.srp_role;
220 x_srp_tbl(x_srp_count).start_date :=srp.srp_start_date;
221 x_srp_tbl(x_srp_count).end_date :=srp.srp_end_date;
222 END IF;
223 END LOOP;
224
225 END LOOP; -- for the main(comp group) cursor
226
227 -- now get the sub group information
228
229 FOR sub_grp IN sub_grp_cur(p_comp_group_id,p_date)
230 LOOP
231 x_grp_count := x_grp_count +1;
232 x_grp_tbl(x_grp_count).grp_id := sub_grp.group_id;
233 x_grp_tbl(x_grp_count).grp_name :=sub_grp.group_name;
234
235 IF (( p_increment_count_grp = -9999) OR (x_grp_count BETWEEN p_start_record_grp
236 AND (p_start_record_grp + p_increment_count_grp -1)))
237 THEN
238 FOR mgr IN mgr_cur(sub_grp.group_id,p_date) -- get manager name
239 LOOP
240 x_grp_tbl(x_grp_count).mgr_name := mgr.mgr_name;
241 x_grp_tbl(x_grp_count).mgr_number := mgr.mgr_number;
242 END LOOP;
243 END IF;
244 END LOOP; -- for the comp groups cursor
245
246
247 EXCEPTION
248 WHEN FND_API.G_EXC_ERROR THEN
249 x_return_status := FND_API.G_RET_STS_ERROR ;
250 FND_MSG_PUB.Count_And_Get
251 (
252 p_count => x_msg_count ,
253 p_data => x_msg_data ,
254 p_encoded => FND_API.G_FALSE
255 );
256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
257 x_loading_status := 'UNEXPECTED_ERR';
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
259 FND_MSG_PUB.Count_And_Get
260 (
261 p_count => x_msg_count ,
262 p_data => x_msg_data ,
263 p_encoded => FND_API.G_FALSE
264 );
265 WHEN OTHERS THEN
266 x_loading_status := 'UNEXPECTED_ERR';
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
268 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
269 THEN
270 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
271 END IF;
272 FND_MSG_PUB.Count_And_Get
273 (
274 p_count => x_msg_count ,
275 p_data => x_msg_data ,
276 p_encoded => FND_API.G_FALSE
277 );
278
279 END;
280 END cn_sales_hier_pub;