[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_CREDITS_BATCH_PVT
Source
1 PACKAGE BODY CN_SCA_CREDITS_BATCH_PVT AS
2 -- $Header: cnvscapb.pls 120.3 2005/11/17 04:44:17 raramasa noship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- CN_SCA_CREDITS_BATCH_PVT
11 -- Purpose
12 -- Package Body to process the Sales Credit Allocations
13 -- Add the flow diagram here.
14 -- History
15 -- 11/10/03 Rao.Chenna Created
16 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SCA_CREDITS_BATCH_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvscapb.pls';
18 no_trx EXCEPTION;
19 conc_fail EXCEPTION;
20 api_call_failed EXCEPTION;
21 g_cn_debug VARCHAR2(1) := fnd_profile.value('CN_DEBUG');
22 --
23 PROCEDURE debugmsg(msg VARCHAR2) IS
24 BEGIN
25
26 IF g_cn_debug = 'Y' THEN
27 cn_message_pkg.debug(SUBSTR(msg,1,254));
28 END IF;
29 END debugmsg;
30 --
31 --
32 PROCEDURE process_batch_rules(
33 errbuf OUT NOCOPY VARCHAR2,
34 retcode OUT NOCOPY VARCHAR2,
35 p_parent_proc_audit_id IN NUMBER,
36 p_physical_batch_id IN NUMBER,
37 p_transaction_source IN VARCHAR2,
38 p_start_date IN DATE,
39 p_end_date IN DATE := NULL,
40 p_org_id IN NUMBER) IS
41 --+
42 --+ Variable Declaration
43 --+
44
45 l_request_id NUMBER(15) := NULL;
46 l_process_audit_id NUMBER(15);
47 l_msg_count NUMBER;
48 l_msg_data VARCHAR2(2000);
49 l_return_status VARCHAR2(30);
50 l_start_id cn_sca_process_batches.start_id%TYPE;
51 l_end_id cn_sca_process_batches.end_id%TYPE;
52 l_org_id INTEGER;
53 l_package_name VARCHAR2(100);
54 l_stmt VARCHAR2(1000);
55 l_winners_sql VARCHAR2(4000);
56 l_not_allocated_sql VARCHAR2(4000);
57 l_output_sql VARCHAR2(4000);
58 l_rec_count NUMBER;
59 l_user_id NUMBER(15) := fnd_global.user_id;
60 l_login_id NUMBER(15) := fnd_global.login_id;
61 l_date DATE := SYSDATE;
62
63 index_ex EXCEPTION;
64
65 --+
66 --+ PL/SQL Tables and Records
67 --+
68
69 TYPE interface_id_tbl_type
70 IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
71
72 TYPE credit_rule_id_tbl_type
73 IS TABLE OF cn_sca_credit_rules.sca_credit_rule_id%TYPE;
74
75 TYPE process_status_tbl_type
76 IS TABLE OF cn_sca_headers_interface.process_status%TYPE;
77
78 TYPE rounding_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
79
80 TYPE lines_output_id_tbl_type
81 IS TABLE OF cn_sca_lines_output.sca_lines_output_id%TYPE;
82
83 TYPE sca_winners_tbl_rec_type IS RECORD (
84 interface_id_tbl interface_id_tbl_type,
85 credit_rule_id_tbl credit_rule_id_tbl_type,
86 process_status_tbl process_status_tbl_type);
87
88 l_sca_winners_tbl_rec sca_winners_tbl_rec_type;
89
90 TYPE rounding_tbl_rec_type IS RECORD (
91 rounding_tbl rounding_tbl_type,
92 lines_output_id_tbl lines_output_id_tbl_type,
93 interface_id_tbl interface_id_tbl_type);
94
95 l_rounding_tbl_rec rounding_tbl_rec_type;
96
97 --+
98 --+ Cursors Section
99 --+
100
101 CURSOR ps_cur IS
102 SELECT MAX(w.sca_headers_interface_id) sca_headers_interface_id,
103 MAX(w.sca_credit_rule_id) sca_credit_rule_id,
104 DECODE(SUM(NVL(l.allocation_percentage,0)),100,'ALLOCATED','REV NOT 100')
105 process_status
106 FROM cn_sca_winners w,
107 cn_sca_lines_output l
108 WHERE w.sca_headers_interface_id = l.sca_headers_interface_id
109 AND w.sca_headers_interface_id BETWEEN l_start_id AND l_end_id
110 AND w.role_id = l.role_id
111 AND l.revenue_type = 'REVENUE'
112 AND w.org_id = l.org_id
113 and w.org_id = p_org_id
114 GROUP BY w.sca_headers_interface_id,w.sca_credit_rule_id;
115
116 BEGIN
117 --
118 l_request_id := fnd_global.conc_request_id;
119
120 cn_message_pkg.begin_batch(
121 x_process_type => 'Batch Mode SCA',
122 x_parent_proc_audit_id => p_parent_proc_audit_id,
123 x_process_audit_id => l_process_audit_id,
124 x_request_id => l_request_id,
125 p_org_id => p_org_id);
126
127 debugmsg('Process Batch Rules: Batch Mode SCA Start');
128 --dbms_output.put_line('Process Batch Rules: l_process_audit_id : '||l_process_audit_id);
129
130 BEGIN
131 SELECT start_id, end_id
132 INTO l_start_id, l_end_id
133 FROM cn_sca_process_batches
134 WHERE sca_process_batch_id = p_physical_batch_id;
135 EXCEPTION
136 WHEN OTHERS THEN
137 debugmsg('Process Batch Rules: Invalid Physical Batch ID');
138 RAISE;
139 END;
140
141 debugmsg('Process Batch Rules: l_start_id - '||l_start_id);
142 debugmsg('Process Batch Rules: l_end_id - '||l_end_id);
143
144 ----+
145 -- Execute the dynamic package and insert data into cn_sca_matches table.
146 ----+
147 /*
148 SELECT org_id
149 INTO l_org_id
150 FROM cn_repositories; */
151
152 --+
153 --+ Construct the name of the dynamic package to be called to get the
154 --+ the winning rule
155 --+
156
157 debugmsg('Process Batch Rules: Begin Dynamic Package Call');
158
159 l_package_name := 'cn_sca_batch_'||LOWER(p_transaction_source)||'_'||
160 ABS(p_org_id)||'_pkg';
161
162 l_stmt := 'BEGIN ' ||l_package_name||'.populate_matches(:p_start_date,'||
163 ':p_end_date,:p_start_id,:p_end_id,:p_physical_batch_id,'||
164 ':p_transaction_source,:p_org_id,:x_return_status,:x_msg_count,'||
165 ':x_msg_data); END;';
166
167 BEGIN
168 EXECUTE IMMEDIATE l_stmt
169 USING IN p_start_date,
170 IN p_end_date,
171 IN l_start_id,
172 IN l_end_id,
173 IN p_physical_batch_id,
174 IN p_transaction_source,
175 IN p_org_id,
176 OUT l_return_status,
177 OUT l_msg_count,
178 OUT l_msg_data;
179
180 debugmsg('Process Batch Rules: End Executing dynamic Package Call');
181 debugmsg('Process Batch Rules: Records in cn_sca_matches :'||SQL%ROWCOUNT);
182
183 COMMIT WORK;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 debugmsg('Process Batch Rules: Error while executing Dynamic Package :'||SQLERRM);
188 RAISE;
189 END;
190
191 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
192 RETURN;
193 END IF;
194
195 ----+
196 -- Populate the data from cn_sca_matches to cn_sca_winners. During this
197 -- process, eliminate duplicate records and identify the unique transaction
198 -- and rule combination. Get the resources, roles and allocation
199 -- for this credit rule.
200 ----+
201
202 l_winners_sql :=
203 'INSERT /*+ APPEND */ INTO cn_sca_winners( '||
204 ' sca_credit_rule_id, '||
205 ' sca_headers_interface_id, '||
206 ' process_date, '||
207 ' rank, '||
208 ' calculated_rank, '||
209 ' role_id, '||
210 ' role_count, '||
211 ' rev_split_pct, '||
212 ' adj_rev_split_pct, '||
213 ' nonrev_split_pct, '||
214 ' adj_nonrev_split_pct, '||
215 ' nrev_credit_split, '||
216 ' created_by, '||
217 ' creation_date, '||
218 ' last_updated_by, '||
219 ' last_update_date, '||
220 ' last_update_login, '||
221 ' org_id) '||
222 'SELECT '||
223 ' m.sca_credit_rule_id, '||
224 ' l.sca_headers_interface_id, '||
225 ' m.process_date, '||
226 ' m.rank, '||
227 ' m.calculated_rank, '||
228 ' l.role_id, '||
229 ' l.role_count, '||
230 ' a.rev_split_pct, '||
231 ' ROUND(NVL(a.rev_split_pct,0)/NVL(l.role_count,1),4) rev_net_split, '||
232 ' a.nonrev_split_pct, '||
233 ' DECODE(NVL(a.nrev_credit_split,''N''),''Y'', '||
234 ' ROUND(NVL(a.nonrev_split_pct,0)/NVL(l.role_count,1),4), '||
235 ' a.nonrev_split_pct) nrev_net_split, '||
236 ' a.nrev_credit_split, :l_user_id, :l_created_date, :l_user_id, '||
237 ' :l_last_update_date, :l_login_id, m.org_id '||
238 ' FROM '||
239 ' (SELECT sca_headers_interface_id, '||
240 ' role_id, org_id, '|| -- added org_id here by raramasa
241 ' count(1) role_count '||
242 ' FROM cn_sca_lines_interface a '||
243 ' WHERE a.org_id = :p_org_id and '||
244 ' a.sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id '||
245 ' GROUP BY sca_headers_interface_id,role_id,org_id) l, '|| -- added org_id here
246 ' (SELECT sca_headers_interface_id,process_date,sca_credit_rule_id, '||
247 ' rank,calculated_rank, '||
248 ' rule_rank,org_id '||
249 ' FROM '||
250 ' (SELECT sca_headers_interface_id, '||
251 ' process_date, '||
252 ' sca_credit_rule_id, '||
253 ' calculated_rank, '||
254 ' rank, org_id, '|| -- added org_id here
255 ' rank() over(partition by sca_headers_interface_id '||
256 ' order by calculated_rank desc, '||
257 ' sca_credit_rule_id desc) as rule_rank '||
258 ' FROM cn_sca_matches '||
259 ' WHERE org_id = :p_org_id and '|| -- added org_id here
260 ' sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id) '||
261 ' WHERE rule_rank = 1 '||
262 ' ) m, '||
263 ' (SELECT a.sca_credit_rule_id,b.role_id, '||
264 ' b.rev_split_pct,b.nonrev_split_pct, '||
265 ' b.nrev_credit_split, '||
266 ' a.start_date,a.end_date '||
267 ' FROM cn_sca_allocations a, '||
268 ' cn_sca_alloc_details b '||
269 ' WHERE a.org_id = :p_org_id and '|| -- added org_id here by raramasa
270 ' a.sca_allocation_id = b.sca_allocation_id) a '||
271 'WHERE l.sca_headers_interface_id = m.sca_headers_interface_id '||
272 ' AND m.sca_credit_rule_id = a.sca_credit_rule_id '||
273 ' AND l.role_id = a.role_id '||
274 ' AND m.process_date BETWEEN a.start_date AND NVL(a.end_date,m.process_date) ';
275
276 BEGIN
277
278 EXECUTE IMMEDIATE l_winners_sql -- added org_id here by raramasa
279 USING IN l_user_id,
280 IN SYSDATE,
281 IN l_user_id,
282 IN SYSDATE,
283 IN l_login_id,
284 IN p_org_id,
285 IN l_start_id,
286 IN l_end_id,
287 IN p_org_id,
288 IN l_start_id,
289 IN l_end_id,
290 IN p_org_id;
291
292 debugmsg('Process Batch Rules: Executing Winners SQL ');
293 debugmsg('Process Batch Rules: Records in cn_sca_winners :'||SQL%ROWCOUNT);
294
295 COMMIT WORK;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 debugmsg('Process Batch Rules: Executing Winners SQL :'||SQLERRM);
300 RAISE;
301 END;
302
303 --+
304 --+ Before populating records into cn_sca_lines_output table check whether
305 --+ transactions exists with same header_id and delete them.
306 --+
307
308 DELETE cn_sca_lines_output a
309 WHERE a.sca_headers_interface_id BETWEEN l_start_id AND l_end_id;
310
311 debugmsg('Process Batch Rules: Trx deleted from cn_sca_lines_output :'||SQL%ROWCOUNT);
312
313 COMMIT WORK;
314
315 --+
316 --+ Populate the data into cn_sca_lines_output table based on the rev and
317 --+ non-revenue type.
318 --+
319
320 debugmsg('Process Batch Rules: Inserting Records Into cn_sca_lines_output');
321
322 l_output_sql :=
323 'INSERT /*+ APPEND */ INTO cn_sca_lines_output( '||
324 ' sca_lines_output_id, '||
325 ' sca_headers_interface_id, '||
326 ' source_trx_id, '||
327 ' resource_id, '||
328 ' role_id, '||
329 ' revenue_type, '||
330 ' allocation_percentage, '||
331 ' object_version_number, '||
332 ' created_by, '||
333 ' creation_date, '||
334 ' last_updated_by, '||
335 ' last_update_date, '||
336 ' last_update_login, '||
337 ' org_id) '||
338 'SELECT cn_sca_lines_output_s.NEXTVAL, '||
339 ' sca_headers_interface_id, '||
340 ' source_trx_id, '||
341 ' resource_id, '||
342 ' role_id, '||
343 ' revenue_type, '||
344 ' DECODE(revenue_type,''REVENUE'', '||
345 ' (alloc_pct - '||
346 ' LAG(alloc_pct, 1, 0) OVER ( '||
347 ' PARTITION BY sca_headers_interface_id, role_id, revenue_type '||
348 ' ORDER BY rn)), '||
349 ' ''NONREVENUE'', '||
350 ' DECODE(nrev_credit_split,''Y'', '||
351 ' (alloc_pct - '||
352 ' LAG(alloc_pct, 1, 0) OVER ( '||
353 ' PARTITION BY sca_headers_interface_id, role_id, revenue_type '||
354 ' ORDER BY rn)),alloc_pct)) allocation_percentage, '||
355 ' 1, :l_user_id, :l_created_date, :l_user_id, :l_last_updated_date, :l_login_id, '||
356 ' org_id '||
357 ' FROM (SELECT a.sca_headers_interface_id, '||
358 ' b.source_trx_id, '||
359 ' b.resource_id, '||
360 ' b.role_id, '||
361 ' c.revenue_type, '||
362 ' a.nrev_credit_split, '||
363 ' DECODE(c.revenue_type,''REVENUE'', '||
364 ' ROUND(a.rev_split_pct * '||
365 ' CUME_DIST() OVER ( '||
366 ' PARTITION BY a.sca_headers_interface_id, b.role_id, '||
367 ' c.revenue_type '||
368 ' ORDER BY b.resource_id), 4), '||
369 ' ''NONREVENUE'', '||
370 ' DECODE(a.nrev_credit_split,''Y'', '||
371 ' ROUND(a.nonrev_split_pct * '||
372 ' CUME_DIST() OVER ( '||
373 ' PARTITION BY a.sca_headers_interface_id, b.role_id, '||
374 ' c.revenue_type '||
375 ' ORDER BY b.resource_id), 4), '||
379 ' c.revenue_type '||
376 ' ''N'',a.nonrev_split_pct)) alloc_pct, '||
377 ' ROW_NUMBER() OVER ( '||
378 ' PARTITION BY a.sca_headers_interface_id, b.role_id, '||
380 ' ORDER BY b.resource_id) rn, '||
381 ' a.ORG_ID '||
382 ' FROM cn_sca_winners a, '||
383 ' cn_sca_lines_interface b, '||
384 ' (SELECT ''REVENUE'' revenue_type FROM dual '||
385 ' UNION ALL '||
386 ' SELECT ''NONREVENUE'' revenue_type FROM dual)c '||
387 ' WHERE a.org_id = :p_org_id and a.org_id = b.org_id AND '||
388 ' a.sca_headers_interface_id = b.sca_headers_interface_id '||
389 ' AND a.sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id '||
390 ' AND a.role_id = b.role_id ) result '||
391 ' WHERE result.alloc_pct > 0 ';
392
393 BEGIN
394 EXECUTE IMMEDIATE l_output_sql
395 USING IN l_user_id,
396 IN SYSDATE,
397 IN l_user_id,
398 IN SYSDATE,
399 IN l_login_id,
400 IN p_org_id,
401 IN l_start_id,
402 IN l_end_id;
403
404 debugmsg('Process Batch Rules: Executed cn_sca_lines_output dynamic SQL');
405 debugmsg('Process Batch Rules: Records in cn_sca_lines_output :'||SQL%ROWCOUNT);
406
407 COMMIT WORK;
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 debugmsg('Process Batch Rules: Error while inserting into cn_sca_lines_output'||SQLERRM);
412 RAISE;
413 END;
414
415 --+
416 --+ Update STATUS flag in the cn_sca_headers_interface table. First update
417 --+ 'ALLOCATED' and 'REV NOT 100' Flags.
418 --+
419
420 debugmsg('Process Batch Rules: Updating ALLOCATED and REV NOT 100 flag');
421
422 OPEN ps_cur;
423 FETCH ps_cur
424 BULK COLLECT INTO l_sca_winners_tbl_rec.interface_id_tbl,
425 l_sca_winners_tbl_rec.credit_rule_id_tbl,
426 l_sca_winners_tbl_rec.process_status_tbl;
427 CLOSE ps_cur;
428
429 IF (l_sca_winners_tbl_rec.interface_id_tbl.COUNT > 0) THEN
430 FORALL indx IN l_sca_winners_tbl_rec.interface_id_tbl.FIRST .. l_sca_winners_tbl_rec.interface_id_tbl.LAST
431 UPDATE cn_sca_headers_interface h
432 SET credit_rule_id = l_sca_winners_tbl_rec.credit_rule_id_tbl(indx),
433 process_status = l_sca_winners_tbl_rec.process_status_tbl(indx)
434 WHERE h.sca_headers_interface_id = l_sca_winners_tbl_rec.interface_id_tbl(indx);
435
436 debugmsg('Process Batch Rules: ALLOCATED and REV NOT 100 records :'||
437 l_sca_winners_tbl_rec.interface_id_tbl.COUNT);
438 END IF;
439
440 COMMIT WORK;
441
442 --+
443 --+ Update STATUS flag to 'NOT ALLOCATED' if record is available in
444 --+ CN_SCA_MATCHES table but not availble in CN_SCA_WINNERS table. We need
445 --+ to use dynamic SQL since PL/SQL does not support RANK() function in
446 --+ 8.1.7
447 --+
448
449 l_not_allocated_sql :=
450 'UPDATE cn_sca_headers_interface h '||
451 ' SET (credit_rule_id,process_status) = ( '||
452 ' SELECT b.sca_credit_rule_id, '||
453 ' ''NOT ALLOCATED'' '||
454 ' FROM (SELECT sca_headers_interface_id,sca_credit_rule_id, '||
455 ' rank, '||
456 ' calculated_rank, '||
457 ' rule_rank '||
458 ' FROM (SELECT sca_headers_interface_id, '||
459 ' sca_credit_rule_id, '||
460 ' calculated_rank, '||
461 ' rank, '||
462 ' rank() over(partition by sca_headers_interface_id '||
463 ' order by calculated_rank desc, '||
464 ' sca_credit_rule_id desc) as rule_rank '||
465 ' FROM cn_sca_matches '||
466 ' WHERE org_id = :p_org_id AND '|| -- added org_id here
467 ' sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id) '||
468 ' WHERE rule_rank = 1 '||
469 ' ) b '||
470 ' WHERE h.sca_headers_interface_id = b.sca_headers_interface_id '||
471 ' AND NOT EXISTS ( '||
472 ' SELECT ''X'' '||
473 ' FROM cn_sca_winners c '||
474 ' WHERE h.sca_headers_interface_id = c.sca_headers_interface_id)) '||
475 ' WHERE h.credit_rule_id IS NULL '||
476 ' AND h.process_status = ''SCA_UNPROCESSED'' '||
477 ' AND h.org_id = :p_org_id AND '|| -- added org_id here
478 ' h.sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id '||
479 -- Perf: Do I need to add this condition
480 ' AND h.processed_date BETWEEN :p_start_date AND NVL(:p_end_date,h.processed_date) ';
481 BEGIN
482 EXECUTE IMMEDIATE l_not_allocated_sql
483 USING IN p_org_id,
484 IN l_start_id,
485 IN l_end_id,
486 IN p_org_id,
487 IN l_start_id,
488 IN l_end_id,
489 IN p_start_date,
490 IN p_end_date;
491 EXCEPTION
492 WHEN OTHERS THEN
493 debugmsg('SCA Batch: Error While Updating Process_Status');
494 RAISE;
495 END;
496
497 COMMIT WORK;
498 --
499 UPDATE cn_sca_headers_interface h
500 SET process_status = 'NO RULE'
501 WHERE h.credit_rule_id IS NULL
502 AND h.process_status IS NULL
503 AND h.org_id = p_org_id -- added org_id here
504 AND h.sca_headers_interface_id BETWEEN l_start_id AND l_end_id
505 AND h.processed_date BETWEEN p_start_date AND NVL(p_end_date,h.processed_date);
506
507 COMMIT WORK;
508 --+
509 --+ Call workflow to process REV NOT 100
510 --+
511
512 BEGIN
513
514 cn_sca_wf_pkg.start_process(
515 p_start_header_id => l_start_id,
516 p_end_header_id => l_end_id,
517 p_trx_source => p_transaction_source,
518 p_wf_process => 'CN_SCA_REV_DIST_PR',
519 p_wf_item_type => 'CNSCARPR');
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 debugmsg('Process Batch Rules: Error occured during in REV NOT 100 workflow');
524 RAISE;
525 END;
526
527 --+
528 --+ Delete the records corresponding to each physical batch from
529 --+ cn_sca_matches table and cn_sca_winners table.
530 --+
531
532 debugmsg('Completed for the physical batch : ' || p_physical_batch_id);
533
534 cn_message_pkg.set_name('CN','ALL_PROCESS_DONE_OK');
535 cn_message_pkg.end_batch(l_process_audit_id);
536
537 retcode := 0;
538 errbuf := 'Program completed successfully';
539 debugmsg('Batch Mode SCA End');
540
541 EXCEPTION
542 WHEN others THEN
543 ROLLBACK;
544 retcode := 2;
545 errbuf := 'Failed';
546 debugmsg('Batch Mode SCA End with errors');
547 cn_message_pkg.end_batch(l_process_audit_id);
548 END;
549 --
550 END;