1 PACKAGE BODY CN_SCA_CREDITS_ONLINE_PUB AS
2 -- $Header: cnpscaob.pls 120.2 2005/09/07 17:54:29 rchenna noship $
3 -- +=========================================================================+
4 -- + Procedure get_sales_credits +
5 -- + Procedure Added for 11.5.10 SCA Enhancment +
6 -- + For the transaction passed in identify winning rule using dynamic pkg +
7 -- + get allocation percentages and distribute and return the sales credit +
8 -- + +
9 -- + Based on the p_batch_id, API will get the data from +
10 -- + cn_sca_headers_interface_GTT and cn_sca_lines_interface_GTT Global +
11 -- + Temporary tables. After processing, this API will keep the data in +
12 -- + cn_sca_lines_output_GTT table. +
13 -- +=========================================================================+
14 PROCEDURE get_sales_credits(
15 p_api_version IN number,
16 p_init_msg_list IN varchar2 := fnd_api.g_false,
17 x_batch_id IN number,
18 p_org_id IN number,
19 x_return_status OUT NOCOPY varchar2,
20 x_msg_count OUT NOCOPY number,
21 x_msg_data OUT NOCOPY varchar2)IS
22 l_api_name CONSTANT VARCHAR2(30) := 'get_sales_credits';
23 l_api_version CONSTANT NUMBER :=1.0;
24 l_win_rule_id cn_sca_credit_rules.SCA_CREDIT_RULE_ID%TYPE;
25 l_package_name VARCHAR2(100);
26 l_stmt VARCHAR2(4000);
27 l_org_id NUMBER := NULL;
28 l_found_rule_flag VARCHAR2(1);
29 l_limit_rows NUMBER := 1 ;
30 l_count number;
31 l_trx_source cn_sca_headers_interface_gtt.transaction_source%TYPE;
32 l_rev_not_100_flag VARCHAR2(1) := 'N';
33
34 --+
35 --+ PL/SQL Tables and Records
36 --+
37
38 TYPE interface_id_tbl_type
39 IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%type;
40
41 TYPE credit_rule_id_tbl_type
42 IS TABLE OF cn_sca_credit_rules.sca_credit_rule_id%type;
43
44 TYPE process_status_tbl_type
45 IS TABLE OF cn_sca_headers_interface.process_status%type;
46
47 TYPE rounding_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
48
49 TYPE lines_output_id_tbl_type
50 IS TABLE OF cn_sca_lines_output.sca_lines_output_id%type;
51
52 TYPE rounding_tbl_rec_type IS RECORD (
53 rounding_tbl rounding_tbl_type,
54 lines_output_id_tbl lines_output_id_tbl_type,
55 interface_id_tbl interface_id_tbl_type);
56
57 l_rounding_tbl_rec rounding_tbl_rec_type;
58
59 --+
60 --+ Cursors Section
61 --+
62
63 --+
64 --+ Quote creation is always within an operating unit. At the same time, this
65 --+ table is a Global Temporary Table which has session specific data. So we
66 --+ need notadd org_id as filter condition for the statement.
67 --+
68 CURSOR get_trans_src_cr IS
69 SELECT DISTINCT transaction_source
70 FROM cn_sca_headers_interface_gtt
71 WHERE sca_batch_id = x_batch_id;
72
73 -- codeCheck: Though cn_sca_winning_rules_gtt has processed_date column,
74 -- dynamic SQL is not populating this column. That is why I had to refer
75 -- cn_sca_headers_interface_gtt table here. In future this table reference
76 -- need to be eliminated.
77
78 CURSOR rounding_cur IS
79 SELECT ROUND(MAX(NVL(csad.rev_split_pct,0)) - SUM(NVL(l.allocation_percentage,0)),4),
80 MIN(l.sca_lines_output_id) sca_lines_output_id,
81 w.sca_headers_interface_id
82 FROM cn_sca_headers_interface_gtt cshi,
83 cn_sca_winning_rules_gtt w,
84 cn_sca_lines_output_gtt l,
85 cn_sca_alloc_details csad,
86 cn_sca_allocations csa
87 WHERE cshi.sca_headers_interface_id = w.sca_headers_interface_id
88 AND w.sca_headers_interface_id = l.sca_headers_interface_id
89 AND w.sca_credit_rule_id = csa.sca_credit_rule_id
90 AND csad.sca_allocation_id = csa.sca_allocation_id
91 AND w.sca_batch_id = x_batch_id
92 AND csad.role_id = l.role_id
93 AND l.revenue_type = 'REVENUE'
94 AND cshi.processed_date BETWEEN csa.start_date AND NVL(end_date,cshi.processed_date)
95 HAVING ROUND(MAX(NVL(csad.rev_split_pct,0)) - SUM(NVL(l.allocation_percentage,0)),4) <> 0
96 GROUP BY w.sca_headers_interface_id,l.role_id;
97
98 CURSOR rounding1_cur IS
99 SELECT ROUND(MAX(NVL(csad.nonrev_split_pct,0)) - SUM(NVL(l.allocation_percentage,0)),4),
100 MIN(l.sca_lines_output_id) sca_lines_output_id,
101 w.sca_headers_interface_id
102 FROM cn_sca_headers_interface_gtt cshi,
103 cn_sca_winning_rules_gtt w,
104 cn_sca_lines_output_gtt l,
105 cn_sca_alloc_details csad,
106 cn_sca_allocations csa
107 WHERE cshi.sca_headers_interface_id = w.sca_headers_interface_id
108 AND w.sca_headers_interface_id = l.sca_headers_interface_id
109 AND w.sca_credit_rule_id = csa.sca_credit_rule_id
110 AND csad.sca_allocation_id = csa.sca_allocation_id
111 AND w.sca_batch_id = x_batch_id
112 AND csad.role_id = l.role_id
113 AND l.revenue_type = 'NONREVENUE'
114 AND NVL(csad.nrev_credit_split,'N') = 'Y'
115 AND cshi.processed_date BETWEEN csa.start_date AND NVL(end_date,cshi.processed_date)
116 HAVING ROUND(MAX(NVL(csad.nonrev_split_pct,0)) - SUM(NVL(l.allocation_percentage,0)),4) <> 0
117 GROUP BY w.sca_headers_interface_id,l.role_id;
118
119 BEGIN
120 -- Standard call to check for call compatibility.
121 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
122 p_api_version ,
123 l_api_name ,
124 G_PKG_NAME )
125 THEN
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128
129 -- Initialize message list if p_init_msg_list is set to TRUE.
130 IF FND_API.to_Boolean( p_init_msg_list ) THEN
131 FND_MSG_PUB.initialize;
132 END IF;
133
134 -- Initialize API return status to success
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137 --+
138 --+ codeCheck: Eventually this logic will be after MOAC patch is applied.
139 --+ We will validate the org_id using MO_GLOBAL.VALIDATE_ORGID_PUB_API
140 --+
141
142 BEGIN
143 SELECT org_id INTO l_org_id FROM cn_repositories;
144 IF l_org_id IS NULL then
145 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
146 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_ORG');
147 FND_MSG_PUB.ADD;
148 END IF;
149 RAISE FND_API.G_EXC_ERROR;
150 END IF;
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
154 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_ORG');
155 FND_MSG_PUB.ADD;
156 END IF;
157 RAISE FND_API.G_EXC_ERROR;
158 END ;
159
160
161 OPEN get_trans_src_cr;
162 FETCH get_trans_src_cr INTO l_trx_source;
163 IF get_trans_src_cr%NOTFOUND THEN
164 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
165 FND_MESSAGE.SET_NAME ('CN','CN_SCA_NO_ROWS_TO_PROCESS');
166 FND_MSG_PUB.Add;
167 END IF;
168 CLOSE get_trans_src_cr;
169 RAISE FND_API.G_EXC_ERROR ;
170 END IF;
171 CLOSE get_trans_src_cr;
172
173 -- Construct the name of the dynamic package to be called to get the
174 -- the winning rule
175 l_package_name := 'cn_sca_rodyn_'|| substr(lower(l_trx_source),1,8) || '_' || abs(l_org_id) || '_pkg';
176 l_stmt := 'BEGIN ' || l_package_name ||'.get_winning_rule(:x_batch_id,:p_org_id,:x_return_status,:x_msg_count,:x_msg_data); END;';
177 -- Execute the dyanmic package to get all the winning rules
178 -- inserted into cn_sca_winning_rules_gtt.
179
180 --dbms_output.put_line('BEFORE CALLING ');
181 --dbms_output.put_line(l_stmt);
182
183 EXECUTE IMMEDIATE l_stmt USING IN x_batch_id ,IN p_org_id, OUT x_return_status,OUT x_msg_count,OUT x_msg_data;
184
185 --dbms_output.put_line('status is INTERNAL '||x_return_status||' '||x_msg_data);
186
187 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
188 RETURN;
189 END IF;
190
191 l_stmt :=
192 'INSERT INTO cn_sca_lines_output_gtt
193 (
194 sca_lines_output_id ,
195 sca_batch_id,
196 sca_headers_interface_id ,
197 processed_date ,
198 status ,
199 source_trx_id ,
200 resource_id ,
201 role_id ,
202 revenue_type ,
203 allocation_percentage
204 )
205 SELECT cn_sca_lines_output_gtt_s.nextval,
206 batch_id,
207 interface_id,
208 processed_date,
209 status,
210 src_trx_id,
211 resource_id,
212 role_id,
213 revenue_type,
214 allocation
215 FROM
216 (select
217 x.batch_id,
218 x.interface_id,
219 x.processed_date,
220 x.status,
221 x.src_trx_id,
222 x.resource_id,
223 x.role_id,
224 y.revenue_type,
225 decode(y.revenue_type, ''REVENUE'', x.rev_value, x.non_rev_value) allocation
226 from
227 (SELECT :x_batch_id1 batch_id,
228 csli.sca_headers_interface_id interface_id,
229 cshig.processed_date processed_date,
230 ''ALLOCATED'' status,
231 csli.source_trx_id src_trx_id,
232 csli.resource_id resource_id,
233 csli.role_id role_id,
234 ROUND(csad.rev_split_pct/nvl(crc.count_of_resources,1),4) rev_value,
235 DECODE(csad.nrev_credit_split,''Y'',
236 ROUND(csad.nonrev_split_pct/NVL(crc.count_of_resources,1),4),
237 csad.nonrev_split_pct) non_rev_value
238 FROM cn_sca_alloc_details csad,
239 cn_sca_allocations csa,
240 (SELECT min( sca_credit_rule_id) sca_credit_rule_id,
241 sca_headers_interface_id
242 FROM cn_sca_winning_rules_gtt
243 WHERE sca_batch_id = :x_batch_id2
244 GROUP BY SCA_HEADERS_INTERFACE_ID ) cswrg,
245 cn_sca_headers_interface_gtt cshig,
246 (SELECT count(distinct RESOURCE_ID) count_of_resources ,
247 role_id,
248 sca_headers_interface_id
249 FROM cn_sca_lines_interface_gtt cslig
250 WHERE cslig.sca_batch_id =:x_batch_id3
251 GROUP BY sca_headers_interface_id,
252 role_id) crc,
253 cn_sca_lines_interface_gtt csli
254 WHERE cshig.sca_batch_id = :x_batch_id4
255 AND csli.sca_batch_id =cshig.sca_batch_id
256 AND cswrg.sca_headers_interface_id = cshig.sca_headers_interface_id
257 AND crc.sca_headers_interface_id = cshig.sca_headers_interface_id
258 AND csli.sca_headers_interface_id = cshig.sca_headers_interface_id
259 AND csa.sca_credit_rule_id = cswrg.sca_credit_rule_id
260 AND csad.ROLE_ID = csli.role_id
261 AND crc.ROLE_ID = csli.role_id
262 AND csad.sca_allocation_id = csa.sca_allocation_id
263 AND cshig.processed_date
264 BETWEEN csa.start_date AND NVL(end_date,cshig.processed_date)
265 ) x,
266 (select ''REVENUE'' revenue_type from dual
267 union all
268 select ''NONREVENUE'' revenue_type from dual) y) result1 WHERE allocation > 0';
269
270 EXECUTE IMMEDIATE l_stmt USING IN x_batch_id,IN x_batch_id,IN x_batch_id,IN x_batch_id;
271
272 --+
273 --+ This code will eliminate the rounding issue for Revenue split percentages.
274 --+
275
276 OPEN rounding_cur;
277 FETCH rounding_cur
278 BULK COLLECT INTO l_rounding_tbl_rec.rounding_tbl,
279 l_rounding_tbl_rec.lines_output_id_tbl,
280 l_rounding_tbl_rec.interface_id_tbl;
281 CLOSE rounding_cur;
282
283 IF (l_rounding_tbl_rec.interface_id_tbl.COUNT > 0) THEN
284 FORALL indx IN l_rounding_tbl_rec.interface_id_tbl.FIRST .. l_rounding_tbl_rec.interface_id_tbl.LAST
285 UPDATE cn_sca_lines_output_gtt l
286 SET l.allocation_percentage = l.allocation_percentage +
287 l_rounding_tbl_rec.rounding_tbl(indx)
288 WHERE l.sca_headers_interface_id = l_rounding_tbl_rec.interface_id_tbl(indx)
289 AND l.sca_lines_output_id = l_rounding_tbl_rec.lines_output_id_tbl(indx);
290 END IF;
291
292 --+
293 --+ This code will eliminate the rounding issue for Non-revenue split percentages.
294 --+
295
296 OPEN rounding1_cur;
297 FETCH rounding1_cur
298 BULK COLLECT INTO l_rounding_tbl_rec.rounding_tbl,
299 l_rounding_tbl_rec.lines_output_id_tbl,
300 l_rounding_tbl_rec.interface_id_tbl;
301 CLOSE rounding1_cur;
302
303 IF (l_rounding_tbl_rec.interface_id_tbl.COUNT > 0) THEN
304 FORALL indx IN l_rounding_tbl_rec.interface_id_tbl.FIRST .. l_rounding_tbl_rec.interface_id_tbl.LAST
305 UPDATE cn_sca_lines_output_gtt l
306 SET l.allocation_percentage = l.allocation_percentage +
307 l_rounding_tbl_rec.rounding_tbl(indx)
311
308 WHERE l.sca_headers_interface_id = l_rounding_tbl_rec.interface_id_tbl(indx)
309 AND l.sca_lines_output_id = l_rounding_tbl_rec.lines_output_id_tbl(indx);
310 END IF;
312
313 -- update the status to rev not 100 where sum
314 -- of allocated revenues across roles is not 100
315 UPDATE cn_sca_lines_output_gtt set status = 'REV NOT 100'
316 WHERE sca_batch_id =x_batch_id
317 AND revenue_type = 'REVENUE'
318 AND sca_headers_interface_id in
319 (SELECT sca_headers_interface_id
320 FROM cn_sca_lines_output_gtt
321 WHERE sca_batch_id = x_batch_id
322 AND revenue_type = 'REVENUE'
323 GROUP BY sca_headers_interface_id
324 HAVING SUM(allocation_percentage) <> 100);
325 IF SQL%ROWCOUNT > 0 THEN
326 l_rev_not_100_flag := 'Y';
327 END IF;
328
329
330
331
332 -- copy the status from output to headers table
333 UPDATE cn_sca_headers_interface_gtt cshig set
334 PROCESS_STATUS = (SELECT distinct status
335 FROM cn_sca_lines_output_gtt cslog
336 WHERE cslog.sca_headers_interface_id = cshig.sca_headers_interface_id
337 AND sca_batch_id =x_batch_id
338 AND revenue_type = 'REVENUE'),
339 CREDIT_RULE_ID = (SELECT min( sca_credit_rule_id)
340 FROM cn_sca_winning_rules_gtt cswr
341 WHERE sca_batch_id = x_batch_id
342 and cswr.sca_headers_interface_id = cshig.sca_headers_interface_id)
343 WHERE sca_batch_id = x_batch_id ;
344 --AND EXISTS (SELECT 'X'
345 -- FROM cn_sca_lines_output_gtt cslog
346 -- WHERE cslog.sca_headers_interface_id = cshig.sca_headers_interface_id
347 -- AND revenue_type = 'REVENUE');
348
349 -- if no output was created then
350 -- update headers to unallocated
351 UPDATE cn_sca_headers_interface_gtt cshig set
352 PROCESS_STATUS = 'NOT ALLOCATED'
353 WHERE sca_batch_id = x_batch_id
354 AND NOT EXISTS (SELECT 'X'
355 FROM cn_sca_lines_output_gtt cslog
356 WHERE cslog.sca_headers_interface_id = cshig.sca_headers_interface_id
357 AND revenue_type = 'REVENUE');
358
359 -- if no output was created then
360 -- update headers to unallocated
361 UPDATE cn_sca_headers_interface_gtt cshig set
362 PROCESS_STATUS = 'NO RULE'
363 WHERE sca_batch_id = x_batch_id
364 AND NOT EXISTS (SELECT 'X'
365 FROM cn_sca_winning_rules_gtt cswr
366 WHERE sca_batch_id = x_batch_id
367 AND cswr.sca_headers_interface_id = cshig.sca_headers_interface_id);
368
369
370
371 IF l_rev_not_100_flag = 'Y' THEN
372
373 /* codeCheck: We may need p_org_id while calling this procedure from
374 batch program */
375 cn_sca_wf_pkg.start_process(p_sca_batch_id => x_batch_id,
376 p_wf_process => 'CN_SCA_REV_DIST_PR',
377 p_wf_item_type => 'CNSCARPR');
378 END IF;
379
380
381 EXCEPTION
382 WHEN FND_API.G_EXC_ERROR THEN
383 x_return_status := FND_API.G_RET_STS_ERROR ;
384 FND_MSG_PUB.Count_And_Get
385 (p_count => x_msg_count ,
386 p_data => x_msg_data ,
387 p_encoded => FND_API.G_FALSE
388 );
389 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
391 FND_MSG_PUB.Count_And_Get
392 (p_count => x_msg_count ,
393 p_data => x_msg_data ,
394 p_encoded => FND_API.G_FALSE
395 );
396 WHEN OTHERS THEN
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
398 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
399 THEN
400 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
401 END IF;
402 FND_MSG_PUB.Count_And_Get
403 (p_count => x_msg_count ,
404 p_data => x_msg_data ,
405 p_encoded => FND_API.G_FALSE
406 );
407
408 END get_sales_credits;
409 END cn_sca_credits_online_pub;
[Home] [Help]