[Home] [Help]
PACKAGE BODY: APPS.CN_WEBSERVICE_PUB
Source
1 PACKAGE BODY cn_webservice_pub AS
2 -- $Header: cnwebsrb.pls 120.3 2009/07/07 16:12:52 ipananil noship $ --+
3 g_pkg_name CONSTANT VARCHAR2(30) := 'CN_WEBSERVICE_PUB';
4 g_format_mask VARCHAR2(1000) := '999999999999999999.';
5 g_currency_code VARCHAR2(100);
6 g_currency_symb VARCHAR2(100);
7 g_precision NUMBER;
8 g_ext_precision NUMBER;
9 g_min_acct_unit NUMBER;
10
11 /*
12 * get_currency_info
13 */
14 PROCEDURE get_currency_info IS
15 CURSOR c_get_curr_code IS
16 SELECT s.currency_code
17 FROM cn_periods p
18 , cn_repositories r
19 , gl_sets_of_books s
20 WHERE r.current_period_id = p.period_id(+)
21 AND r.application_id = 283
22 AND r.set_of_books_id = s.set_of_books_id;
23
24 CURSOR c_curr_symbol IS
25 SELECT NVL(symbol, '') AS symbol
26 FROM fnd_currencies
27 WHERE currency_code = g_currency_code;
28 BEGIN
29 FOR l_cur_tran IN c_get_curr_code LOOP
30 g_currency_code := l_cur_tran.currency_code;
31 END LOOP;
32
33 FND_CURRENCY.get_info(g_currency_code, g_precision, g_ext_precision, g_min_acct_unit);
34
35 FOR l_cur_sym IN c_curr_symbol LOOP
36 g_currency_symb := l_cur_sym.symbol;
37 END LOOP;
38
39 IF INSTR(g_format_mask, '.99') = 0 THEN
40 FOR i IN 1 .. NVL(g_precision, 2) LOOP
41 g_format_mask := g_format_mask || '9';
42 END LOOP;
43 END IF;
44 END get_currency_info;
45
46 /*
47 * recent_trans_for_salesrep
48 */
49 PROCEDURE recent_trans_for_salesrep(
50 usr_name IN VARCHAR,
51 x_recent_transaction_tbl OUT NOCOPY recent_transaction_tbl_type
52 ) IS
53 CURSOR get_salesrep_dtls(p_org_id NUMBER) IS
54 SELECT fu.user_id
55 , fu.user_name
56 , jrs.resource_id
57 , jrs.NAME
58 , jrs.salesrep_id
59 , jrs.org_id
60 FROM fnd_user fu
61 , jtf_rs_defresources_v jrd
62 , jtf_rs_salesreps jrs
63 WHERE fu.user_name = usr_name
64 AND jrd.user_id = fu.user_id
65 AND jrs.resource_id = jrd.resource_id
66 AND jrs.org_id = p_org_id;
67
68 CURSOR open_period IS
69 SELECT MIN(start_date) AS min_date
70 , MAX(end_date) AS max_date
71 FROM cn_periods
72 WHERE closing_status = 'O';
73
74 CURSOR recent_transation(p_from_date DATE, p_to_date DATE, p_salesrep_id NUMBER) IS
75 SELECT TO_CHAR(cl.processed_date, 'MM/DD') AS processed_date
76 , ch.customer_id AS customer_id
77 , NVL(hp.party_name, 'CUSTOMER NOT PRESENT') AS customer_name
78 , NVL(ch.attribute1, inventory_item_id) AS product_name
79 , NVL(ch.transaction_amount, 0) AS transaction_amount
80 , NVL(cl.perf_achieved, 0) AS credit
81 , NVL(cl.commission_amount, 0) AS earnings
82 FROM cn_commission_headers ch
83 , cn_commission_lines cl
84 , cn_salesreps rep
85 , hz_parties hp
86 , hz_cust_accounts hca
87 WHERE cl.org_id = ch.org_id
88 AND rep.org_id = ch.org_id
89 AND cl.status = 'CALC'
90 AND cl.commission_header_id = ch.commission_header_id
91 AND ch.direct_salesrep_id = rep.salesrep_id
92 AND cl.credited_salesrep_id = ch.direct_salesrep_id
93 AND rep.salesrep_id = p_salesrep_id
94 AND cl.processed_date BETWEEN p_from_date AND p_to_date
95 AND hca.cust_account_id(+) = ch.customer_id
96 AND hp.party_id = hca.party_id
97 AND ch.customer_id IS NOT NULL
98 ORDER BY cl.processed_date DESC;
99
100 c_rec_tran recent_transation%ROWTYPE;
101 l_recent_transaction_type recent_transaction_type;
102 l_row_cnt NUMBER := 0;
103 l_salesrp_id NUMBER;
104 l_org_id NUMBER;
105 l_from_date DATE;
106 l_end_date DATE;
107 l_profile_val NUMBER;
108 BEGIN
109 l_profile_val := fnd_profile.VALUE('ORG_ID');
110
111 FOR l_salesrep_dtls IN get_salesrep_dtls(l_profile_val) LOOP
112 l_salesrp_id := l_salesrep_dtls.salesrep_id;
113 l_org_id := l_salesrep_dtls.org_id;
114 END LOOP;
115
116 mo_global.set_policy_context('S', l_org_id);
117 x_recent_transaction_tbl := g_miss_rt_rec_tb;
118
119 FOR l_rec_trans IN open_period LOOP
120 l_from_date := l_rec_trans.min_date;
121 l_end_date := l_rec_trans.max_date;
122 END LOOP;
123
124 get_currency_info;
125
126 FOR l_rec_trans IN recent_transation(l_from_date, l_end_date, l_salesrp_id) LOOP
127 l_row_cnt := l_row_cnt + 1;
128
129 x_recent_transaction_tbl(l_row_cnt).processed_date := l_rec_trans.processed_date;
130 x_recent_transaction_tbl(l_row_cnt).customer_name := l_rec_trans.customer_name;
131 x_recent_transaction_tbl(l_row_cnt).product_name := l_rec_trans.product_name;
132 x_recent_transaction_tbl(l_row_cnt).transaction_amount :=
133 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.transaction_amount, 0), g_precision), g_format_mask));
134 x_recent_transaction_tbl(l_row_cnt).credit :=
135 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.credit, 0), g_precision), g_format_mask));
136 x_recent_transaction_tbl(l_row_cnt).earnings :=
137 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.earnings, 0), g_precision), g_format_mask));
138 x_recent_transaction_tbl(l_row_cnt).currency_symb := g_currency_symb;
139
140 IF l_row_cnt = 100 THEN
141 EXIT;
142 END IF;
143 END LOOP;
144 END recent_trans_for_salesrep;
145
146 PROCEDURE top_customer_for_salesrep(
147 usr_name IN VARCHAR,
148 x_top_customer_tbl OUT NOCOPY top_customer_tbl_type
149 ) IS
150 CURSOR get_salesrep_dtls(p_org_id NUMBER) IS
151 SELECT fu.user_id
152 , fu.user_name
153 , jrs.resource_id
154 , jrs.NAME
155 , jrs.salesrep_id
156 , jrs.org_id
157 FROM fnd_user fu
158 , jtf_rs_defresources_v jrd
159 , jtf_rs_salesreps jrs
160 WHERE fu.user_name = usr_name
161 AND jrd.user_id = fu.user_id
162 AND jrs.resource_id = jrd.resource_id
163 AND jrs.org_id = p_org_id;
164
165 CURSOR open_period IS
166 SELECT MIN(start_date) AS min_date
167 , MAX(end_date) AS max_date
168 FROM cn_periods
169 WHERE closing_status = 'O';
170
171 CURSOR top_customer(p_from_date DATE, p_to_date DATE, p_salesrep_id NUMBER) IS
172 SELECT customer_name
173 , earnings
174 , (comm_rate * 100) comm_rate
175 , credit
176 , transaction_amount
177 FROM (SELECT NVL (hp.party_name, 'CUSTOMER NOT PRESENT') AS customer_name
178 , hca.cust_account_id customer_id
179 , MAX (NVL (cl.commission_amount, 0)) AS earnings
180 , MAX (NVL (cl.commission_rate, 0)) AS comm_rate
181 , MAX (NVL (cl.perf_achieved, 0)) AS credit
182 , MAX (NVL (ch.transaction_amount, 0)) AS transaction_amount
183 , RANK () OVER (PARTITION BY hca.cust_account_id ORDER BY NVL
184 (cl.commission_amount,
185 0
186 ) DESC NULLS LAST) AS comp_rank
187 FROM cn_commission_headers ch
188 , cn_commission_lines cl
189 , cn_salesreps rep
190 , hz_parties hp
191 , hz_cust_accounts hca
192 WHERE cl.org_id = ch.org_id
193 AND rep.org_id = ch.org_id
194 AND cl.status = 'CALC'
195 AND cl.commission_header_id = ch.commission_header_id
196 AND cl.credited_salesrep_id = ch.direct_salesrep_id
197 AND ch.direct_salesrep_id = rep.salesrep_id
198 AND rep.salesrep_id = p_salesrep_id
199 AND cl.processed_date BETWEEN p_from_date AND p_to_date
200 AND ch.customer_id IS NOT NULL
201 AND hca.cust_account_id(+) = ch.customer_id
202 AND hp.party_id = hca.party_id
203 GROUP BY hca.cust_account_id
204 , party_name
205 , NVL (cl.commission_amount, 0))
206 WHERE comp_rank = 1
207 ORDER BY earnings DESC;
208
209 c_tc_tran top_customer%ROWTYPE;
210 l_tc_transaction_type top_customer_type;
211 l_salesrp_id NUMBER;
212 l_org_id NUMBER;
213 l_row_cnt NUMBER := 0;
214 l_from_date DATE;
215 l_end_date DATE;
216 l_profile_val NUMBER;
217 BEGIN
218 l_profile_val := fnd_profile.VALUE('ORG_ID');
219
220 FOR l_salesrep_dtls IN get_salesrep_dtls(l_profile_val) LOOP
221 l_salesrp_id := l_salesrep_dtls.salesrep_id;
222 l_org_id := l_salesrep_dtls.org_id;
223 END LOOP;
224
225 mo_global.set_policy_context('S', l_profile_val);
226 x_top_customer_tbl := g_miss_ct_rec_tb;
227
228 FOR l_rec_trans IN open_period LOOP
229 l_from_date := l_rec_trans.min_date;
230 l_end_date := l_rec_trans.max_date;
231 END LOOP;
232
233 get_currency_info;
234
235 FOR l_rec_trans IN top_customer(l_from_date, l_end_date, l_salesrp_id) LOOP
236 l_row_cnt := l_row_cnt + 1;
237
238 x_top_customer_tbl(l_row_cnt).customer_name := l_rec_trans.customer_name;
239 x_top_customer_tbl(l_row_cnt).earnings :=
240 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.earnings, 0), g_precision), g_format_mask));
241 x_top_customer_tbl(l_row_cnt).rate :=
242 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.comm_rate, 0), g_precision), g_format_mask));
243 x_top_customer_tbl(l_row_cnt).credit :=
244 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.credit, 0), g_precision), g_format_mask));
245 x_top_customer_tbl(l_row_cnt).transaction_amount :=
246 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.transaction_amount, 0), g_precision), g_format_mask));
247 x_top_customer_tbl(l_row_cnt).currency_symb := g_currency_symb;
248
249 IF l_row_cnt = 100 THEN
250 EXIT;
251 END IF;
252 END LOOP;
253 END top_customer_for_salesrep;
254
255 PROCEDURE top_product_for_salesrep(
256 usr_name IN VARCHAR,
257 x_top_cust_prod_tbl OUT NOCOPY top_cus_prod_tbl_type
258 ) IS
259 CURSOR get_salesrep_dtls(p_org_id NUMBER) IS
260 SELECT fu.user_id
261 , fu.user_name
262 , jrs.resource_id
263 , jrs.NAME
264 , jrs.salesrep_id
265 , jrs.org_id
266 FROM fnd_user fu
267 , jtf_rs_defresources_v jrd
268 , jtf_rs_salesreps jrs
269 WHERE fu.user_name = usr_name
270 AND jrd.user_id = fu.user_id
271 AND jrs.resource_id = jrd.resource_id
272 AND jrs.org_id = p_org_id;
273
274 CURSOR open_period IS
275 SELECT MIN(start_date) AS min_date
276 , MAX(end_date) AS max_date
277 FROM cn_periods
278 WHERE closing_status = 'O';
279
280 CURSOR top_product(p_from_date DATE, p_to_date DATE, p_salesrep_id NUMBER) IS
281 SELECT product_name
282 , earnings
283 , (comm_rate * 100) comm_rate
284 , credit
285 , transaction_amount
286 FROM (SELECT NVL(ch.attribute1, inventory_item_id) AS product_name,
287 MAX(NVL(cl.commission_amount, 0)) AS earnings,
288 MAX(NVL(cl.commission_rate, 0)) AS comm_rate,
289 MAX(NVL(cl.perf_achieved, 0)) AS credit,
290 MAX(NVL(ch.transaction_amount, 0)) AS transaction_amount
291 FROM cn_commission_headers ch,
292 cn_commission_lines cl,
293 cn_salesreps rep
294 WHERE cl.org_id = ch.org_id
295 AND rep.org_id = ch.org_id
296 AND cl.status = 'CALC'
297 AND cl.commission_header_id = ch.commission_header_id
298 AND cl.credited_salesrep_id = ch.direct_salesrep_id
299 AND ch.direct_salesrep_id = rep.salesrep_id
300 AND rep.salesrep_id = p_salesrep_id
301 AND cl.processed_date BETWEEN p_from_date AND p_to_date
302 AND ch.customer_id IS NOT NULL
303 GROUP BY NVL(ch.attribute1, inventory_item_id))
304 ORDER BY earnings DESC;
305
306 c_tc_tran top_product%ROWTYPE;
307 l_tc_transaction_type top_cust_prod_type;
308 l_row_cnt NUMBER := 0;
309 l_salesrp_id NUMBER;
310 l_org_id NUMBER;
311 l_from_date DATE;
312 l_end_date DATE;
313 l_profile_val NUMBER;
314 BEGIN
315 l_profile_val := fnd_profile.VALUE('ORG_ID');
316
317 FOR l_salesrep_dtls IN get_salesrep_dtls(l_profile_val) LOOP
318 l_salesrp_id := l_salesrep_dtls.salesrep_id;
319 l_org_id := l_salesrep_dtls.org_id;
320 END LOOP;
321
322 mo_global.set_policy_context('S', l_profile_val);
323 x_top_cust_prod_tbl := g_miss_ct_prod_rec_tb;
324
325 FOR l_rec_trans IN open_period LOOP
326 l_from_date := l_rec_trans.min_date;
327 l_end_date := l_rec_trans.max_date;
328 END LOOP;
329
330 get_currency_info;
331
332 FOR l_rec_trans IN top_product(l_from_date, l_end_date, l_salesrp_id) LOOP
333 l_row_cnt := l_row_cnt + 1;
334
335 x_top_cust_prod_tbl(l_row_cnt).product_name := l_rec_trans.product_name;
336 x_top_cust_prod_tbl(l_row_cnt).earnings :=
337 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.earnings, 0), g_precision), g_format_mask));
338 x_top_cust_prod_tbl(l_row_cnt).rate :=
339 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.comm_rate, 0), g_precision), g_format_mask));
340 x_top_cust_prod_tbl(l_row_cnt).credit :=
341 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.credit, 0), g_precision), g_format_mask));
342 x_top_cust_prod_tbl(l_row_cnt).transaction_amount :=
343 LTRIM(TO_CHAR(ROUND(NVL(l_rec_trans.transaction_amount, 0), g_precision), g_format_mask));
344 x_top_cust_prod_tbl(l_row_cnt).currency_symb := g_currency_symb;
345
346 IF l_row_cnt = 100 THEN
347 EXIT;
348 END IF;
349 END LOOP;
350 END top_product_for_salesrep;
351
352 PROCEDURE top_escalted_task_salesrep(
353 usr_name IN VARCHAR,
354 x_top_esc_task_tbl OUT NOCOPY top_esc_task_tbl_type
355 ) IS
356 CURSOR c_esc_tasks IS
357 SELECT task_number
358 , task_id
359 , assignment_status
360 , task_name
361 , customer_name
362 , priority_name
363 , resource_name
364 , resource_id
365 , resource_type_code
366 , planned_start_date
367 , scheduled_start_date
368 , sla_esc
369 , calc_date
370 FROM (SELECT b.task_number, b.task_id, s.NAME assignment_status, b.task_name,
371 pi.party_name customer_name, jtp.NAME priority_name,
372 csf_resource_pub.get_resource_name(resource_id,
373 resource_type_code
374 ) resource_name,
375 resource_id, resource_type_code, b.planned_start_date,
376 b.scheduled_start_date, 'ESC' sla_esc, NULL calc_date
377 FROM jtf_tasks_b esc_t,
378 jtf_task_statuses_vl s,
379 jtf_tasks_vl b,
380 jtf_task_references_b r,
381 hz_party_sites ps,
382 hz_locations hl,
383 hz_parties pi,
384 jtf_task_assignments jtb,
385 jtf_task_priorities_vl jtp
386 WHERE esc_t.task_id = r.task_id
387 AND esc_t.task_type_id = 22
388 AND s.task_status_id = jtb.assignment_status_id
389 AND r.reference_code = 'ESC'
390 AND r.object_type_code = 'TASK'
391 AND r.object_id = b.task_id
392 AND ps.party_site_id(+) = b.address_id
393 AND hl.location_id(+) = ps.location_id
394 AND pi.party_id(+) = b.customer_id
395 AND NVL(esc_t.deleted_flag, 'N') <> 'Y'
396 AND NVL(s.cancelled_flag, 'N') <> 'Y'
397 AND jtb.task_id = b.task_id
398 AND jtp.task_priority_id = b.task_priority_id
399 UNION
400 SELECT b.task_number, b.task_id, s.NAME assignment_status, b.task_name,
401 pi.party_name customer_name, jtp.NAME priority_name,
402 csf_resource_pub.get_resource_name(resource_id,
403 resource_type_code
404 ) resource_name,
405 resource_id, resource_type_code, b.planned_start_date,
406 b.scheduled_start_date, 'SLA' sla_esc,
407 planned_start_date - scheduled_start_date calc_date
408 FROM jtf_task_statuses_vl s,
409 jtf_tasks_vl b,
410 hz_party_sites ps,
411 hz_locations hl,
412 hz_parties pi,
413 jtf_task_assignments jtb,
414 jtf_task_priorities_vl jtp
415 WHERE jtb.task_id = b.task_id
416 AND s.task_status_id = jtb.assignment_status_id
417 AND ps.party_site_id(+) = b.address_id
418 AND hl.location_id(+) = ps.location_id
419 AND pi.party_id(+) = b.customer_id
420 AND NVL(s.closed_flag, 'N') <> 'Y'
421 AND jtp.task_priority_id = b.task_priority_id
422 AND NVL(s.cancelled_flag, 'N') <> 'Y'
423 AND planned_start_date - scheduled_start_date > 0)
424 ORDER BY calc_date ASC;
425
426 l_row_cnt NUMBER := 0;
427 BEGIN
428 x_top_esc_task_tbl := g_miss_ec_task_rec_tb;
429
430 FOR l_esc_rec IN c_esc_tasks LOOP
431 l_row_cnt := l_row_cnt + 1;
432
433 x_top_esc_task_tbl(l_row_cnt).task_number := l_esc_rec.task_number;
434 x_top_esc_task_tbl(l_row_cnt).task_id := l_esc_rec.task_id;
435 x_top_esc_task_tbl(l_row_cnt).assignment_status := l_esc_rec.assignment_status;
436 x_top_esc_task_tbl(l_row_cnt).task_name := l_esc_rec.task_name;
437 x_top_esc_task_tbl(l_row_cnt).customer_name := l_esc_rec.customer_name;
438 x_top_esc_task_tbl(l_row_cnt).priority_name := l_esc_rec.priority_name;
439 x_top_esc_task_tbl(l_row_cnt).resource_name := l_esc_rec.resource_name;
440 x_top_esc_task_tbl(l_row_cnt).resource_id := l_esc_rec.resource_id;
441 x_top_esc_task_tbl(l_row_cnt).resource_type_code := l_esc_rec.resource_type_code;
442 x_top_esc_task_tbl(l_row_cnt).planned_start_date := l_esc_rec.planned_start_date;
443 x_top_esc_task_tbl(l_row_cnt).scheduled_start_date := l_esc_rec.scheduled_start_date;
444 x_top_esc_task_tbl(l_row_cnt).sla_esc := l_esc_rec.sla_esc;
445 x_top_esc_task_tbl(l_row_cnt).calc_date := l_esc_rec.calc_date;
446
447 IF l_row_cnt = 100 THEN
448 EXIT;
449 END IF;
450 END LOOP;
451 END top_escalted_task_salesrep;
452
453 PROCEDURE test_webservice(user_id IN VARCHAR2, x_commission_rec OUT NOCOPY test_webser_tbl_type) IS
454 BEGIN
455 x_commission_rec := g_miss_webser_rec_tb;
456
457 x_commission_rec(1).commission := 100;
458 x_commission_rec(1).userid := user_id;
459 x_commission_rec(2).commission := 200;
460 x_commission_rec(2).userid := user_id;
461 END test_webservice;
462 END cn_webservice_pub;