[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_WF_PKG
Source
1 PACKAGE BODY CN_SCA_WF_PKG AS
2 /* $Header: cnpscawb.pls 120.2 2005/09/23 15:22:32 rchenna noship $ */
3
4 -- PRIVATE PROCEDURES
5 -- ==================
6
7 -- Start of comments
8 -- API name : BATCH_POST_DIST_UPDATE
9 -- Description : Update revenue allocation if rounding occured.
10 -- Type : Private.
11 -- Function :
12 -- Pre-reqs : BATCH_EVEN_REV_DIST or BATCH_WTD_REV_DIST completed.
13 -- Parameters :
14 -- IN : p_start_header_id IN NUMBER Required
15 -- : p_end_header_id IN NUMBER Required
16 -- : p_user_id IN NUMBER Required
17 -- : p_login_id IN NUMBER Required
18 -- OUT :
19 -- Version : Current version 1.0
20 -- Previous version
21 -- Initial version 1.0
22 -- Notes :
23 -- End of comments
24 PROCEDURE BATCH_POST_DIST_UPDATE (
25 p_start_header_id IN number,
26 p_end_header_id IN number,
27 p_user_id IN number,
28 p_login_id IN number)
29 IS
30 BEGIN
31
32 UPDATE cn_sca_lines_output lines
33 SET allocation_percentage =
34 (SELECT ROUND(lines.allocation_percentage +
35 (100 - SUM(g_lines.allocation_percentage)),4)
36 FROM cn_sca_headers_interface g_headers,
37 cn_sca_lines_output g_lines
38 WHERE g_headers.sca_headers_interface_id =
39 lines.sca_headers_interface_id
40 AND g_headers.sca_headers_interface_id =
41 g_lines.sca_headers_interface_id
42 AND g_lines.revenue_type = G_REVENUE
43 AND g_headers.process_status = G_REV_NOT_100
44 GROUP BY g_headers.sca_headers_interface_id),
45 last_updated_by = p_user_id,
46 last_update_date = SYSDATE,
47 last_update_login = p_login_id
48 WHERE lines.revenue_type = G_REVENUE
49 AND lines.sca_headers_interface_id
50 BETWEEN p_start_header_id AND p_end_header_id
51 AND EXISTS (SELECT 1
52 FROM cn_sca_headers_interface headers
53 WHERE headers.process_status = G_REV_NOT_100
54 AND headers.sca_headers_interface_id =
55 lines.sca_headers_interface_id)
56 AND lines.sca_lines_output_id = (SELECT MIN(sca_lines_output_id)
57 FROM cn_sca_lines_output g_lines
58 WHERE lines.sca_headers_interface_id =
59 g_lines.sca_headers_interface_id
60 AND g_lines.revenue_type = G_REVENUE);
61
62 END BATCH_POST_DIST_UPDATE;
63
64 -- Start of comments
65 -- API name : ONLINE_POST_DIST_UPDATE
66 -- Description : Update revenue allocation if rounding occured.
67 -- Type : Private.
68 -- Function :
69 -- Pre-reqs : ONLINE_EVEN_REV_DIST or ONLINE_WTD_REV_DIST completed.
70 -- Parameters :
71 -- IN : p_sca_batch_id IN NUMBER Required
72 -- : p_user_id IN NUMBER Required
73 -- : p_login_id IN NUMBER Required
74 -- OUT :
75 -- Version : Current version 1.0
76 -- Previous version
77 -- Initial version 1.0
78 -- Notes :
79 -- End of comments
80 PROCEDURE ONLINE_POST_DIST_UPDATE (
81 p_sca_batch_id IN number)
82 IS
83 BEGIN
84
85 UPDATE cn_sca_lines_output_gtt lines
86 SET allocation_percentage =
87 (SELECT ROUND(lines.allocation_percentage +
88 (100 - SUM(g_lines.allocation_percentage)),4)
89 FROM cn_sca_headers_interface_gtt g_headers,
90 cn_sca_lines_output_gtt g_lines
91 WHERE g_headers.sca_headers_interface_id =
92 lines.sca_headers_interface_id
93 AND g_headers.sca_headers_interface_id =
94 g_lines.sca_headers_interface_id
95 AND g_lines.revenue_type = G_REVENUE
96 AND g_headers.process_status = G_REV_NOT_100
97 GROUP BY g_headers.sca_headers_interface_id)
98 WHERE lines.revenue_type = G_REVENUE
99 AND lines.sca_batch_id = p_sca_batch_id
100 AND EXISTS (SELECT 1
101 FROM cn_sca_headers_interface_gtt headers
102 WHERE headers.process_status = G_REV_NOT_100
103 AND headers.sca_headers_interface_id =
104 lines.sca_headers_interface_id)
105 AND lines.sca_lines_output_id = (SELECT MIN(sca_lines_output_id)
106 FROM cn_sca_lines_output_gtt g_lines
107 WHERE lines.sca_headers_interface_id =
108 g_lines.sca_headers_interface_id
109 AND g_lines.revenue_type = G_REVENUE);
110
111 END ONLINE_POST_DIST_UPDATE;
112
113 -- Start of comments
114 -- API name : ONLINE_EVEN_REV_DIST
115 -- Description : Online Even Revenue Distribution
116 -- Type : Private.
117 -- Function :
118 -- Pre-reqs : None.
119 -- Parameters :
120 -- IN : p_sca_batch_id IN NUMBER Required
121 -- OUT :
122 -- Version : Current version 1.0
123 -- Previous version
124 -- Initial version 1.0
125 -- Notes :
126 -- End of comments
127 PROCEDURE ONLINE_EVEN_REV_DIST (
128 p_sca_batch_id IN number,
129 x_return_status OUT NOCOPY varchar2)
130 IS
131 BEGIN
132
133 -- Initialize API return status to success
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136 -- Start of API body.
137
138 -- calculate and set new revenue allocation
139 -- *******************************************
140 -- complement_pct
141 -- new rev pct = curr_rev_pct + --------------
142 -- num_of_res
143 -- *******************************************
144 UPDATE cn_sca_lines_output_gtt lines
145 SET allocation_percentage =
146 (SELECT ROUND(lines.allocation_percentage +
147 (100 - SUM(g_lines.allocation_percentage))
148 / COUNT(*),4)
149 FROM cn_sca_headers_interface_gtt g_headers,
150 cn_sca_lines_output_gtt g_lines
151 WHERE g_headers.sca_headers_interface_id =
152 lines.sca_headers_interface_id
153 AND g_headers.sca_headers_interface_id =
154 g_lines.sca_headers_interface_id
155 AND g_lines.revenue_type = G_REVENUE
156 AND g_headers.process_status = G_REV_NOT_100
157 GROUP BY g_headers.sca_headers_interface_id)
158 WHERE lines.revenue_type = G_REVENUE
159 AND lines.sca_batch_id = p_sca_batch_id
160 AND EXISTS (SELECT 1
161 FROM cn_sca_headers_interface_gtt headers
162 WHERE headers.process_status = G_REV_NOT_100
163 AND headers.sca_headers_interface_id =
164 lines.sca_headers_interface_id);
165
166 -- second update to address possible rounding error
167 ONLINE_POST_DIST_UPDATE(p_sca_batch_id => p_sca_batch_id);
168
169 -- update header status to ALLOCATED
170 UPDATE cn_sca_headers_interface_gtt headers
171 SET process_status = G_ALLOCATED
172 WHERE headers.process_status = G_REV_NOT_100
173 AND headers.sca_batch_id = p_sca_batch_id;
174
175 -- End of API body.
176
177 EXCEPTION
178
179 WHEN OTHERS THEN
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
181
182 END ONLINE_EVEN_REV_DIST;
183
184 -- Start of comments
185 -- API name : BATCH_EVEN_REV_DIST
186 -- Description : Batch Even Revenue Distribution
187 -- Type : Private.
188 -- Function :
189 -- Pre-reqs : None.
190 -- Parameters :
191 -- IN : p_start_header_id IN NUMBER Required
192 -- : p_end_header_id IN NUMBER Required
193 -- OUT :
194 -- Version : Current version 1.0
195 -- Previous version
196 -- Initial version 1.0
197 -- Notes :
198 -- End of comments
199 PROCEDURE BATCH_EVEN_REV_DIST (
200 p_start_header_id IN number,
201 p_end_header_id IN number,
202 x_return_status OUT NOCOPY varchar2)
203 IS
204 l_user_id NUMBER := fnd_global.user_id;
205 l_login_id NUMBER := fnd_global.login_id;
206 BEGIN
207
208 -- Initialize API return status to success
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210
211 -- Start of API body.
212
213 -- calculate and set new revenue allocation
214 -- *******************************************
215 -- complement_pct
216 -- new rev pct = curr_rev_pct + --------------
217 -- num_of_res
218 -- *******************************************
219 UPDATE cn_sca_lines_output lines
220 SET allocation_percentage =
221 (SELECT ROUND(lines.allocation_percentage +
222 (100 - SUM(g_lines.allocation_percentage))
223 / COUNT(*),4)
224 FROM cn_sca_headers_interface g_headers,
225 cn_sca_lines_output g_lines
226 WHERE g_headers.sca_headers_interface_id =
227 lines.sca_headers_interface_id
228 AND g_headers.sca_headers_interface_id =
229 g_lines.sca_headers_interface_id
230 AND g_lines.revenue_type = G_REVENUE
231 AND g_headers.process_status = G_REV_NOT_100
232 GROUP BY g_headers.sca_headers_interface_id),
233 last_updated_by = l_user_id,
234 last_update_date = SYSDATE,
235 last_update_login = l_login_id
236 WHERE lines.revenue_type = G_REVENUE
237 AND lines.sca_headers_interface_id
238 BETWEEN p_start_header_id AND p_end_header_id
239 AND EXISTS (SELECT 1
240 FROM cn_sca_headers_interface headers
241 WHERE headers.process_status = G_REV_NOT_100
242 AND headers.sca_headers_interface_id =
243 lines.sca_headers_interface_id);
244
245 -- second update to address possible rounding error
246 BATCH_POST_DIST_UPDATE(p_start_header_id => p_start_header_id,
247 p_end_header_id => p_end_header_id,
248 p_user_id => l_user_id,
249 p_login_id => l_login_id);
250
251 -- update header status to ALLOCATED
252 UPDATE cn_sca_headers_interface headers
253 SET process_status = G_ALLOCATED,
254 last_updated_by = l_user_id,
255 last_update_date = SYSDATE,
256 last_update_login = l_login_id
257 WHERE headers.process_status = G_REV_NOT_100
258 AND headers.sca_headers_interface_id
259 BETWEEN p_start_header_id AND p_end_header_id;
260
261 -- End of API body.
262
263 EXCEPTION
264 WHEN OTHERS THEN
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266
267 END BATCH_EVEN_REV_DIST;
268
269 -- Start of comments
270 -- API name : ONLINE_WTD_REV_DIST
271 -- Description : Online Weighted Average Revenue Distribution
272 -- Type : Private.
273 -- Function :
274 -- Pre-reqs : None.
275 -- Parameters :
276 -- IN : p_sca_batch_id IN NUMBER Required
277 -- OUT :
278 -- Version : Current version 1.0
279 -- Previous version
280 -- Initial version 1.0
281 -- Notes :
282 -- End of comments
283 PROCEDURE ONLINE_WTD_REV_DIST (
284 p_sca_batch_id IN number,
285 x_return_status OUT NOCOPY varchar2)
286 IS
287 BEGIN
288
289 -- Initialize API return status to success
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 -- Start of API body.
293
294 -- calculate and set new revenue allocation
295 -- **********************************************************
296 -- curr_rev_pct * complement_pct
297 -- new rev pct = curr_rev_pct + -----------------------------
298 -- curr_total_rev_pct
299 -- **********************************************************
300 UPDATE cn_sca_lines_output_gtt lines
301 SET allocation_percentage =
302 (SELECT ROUND(
303 lines.allocation_percentage + lines.allocation_percentage *
304 (100 - SUM(g_lines.allocation_percentage)) /
305 SUM(g_lines.allocation_percentage),4)
306 FROM cn_sca_headers_interface_gtt g_headers,
307 cn_sca_lines_output_gtt g_lines
308 WHERE g_headers.sca_headers_interface_id =
309 lines.sca_headers_interface_id
310 AND g_headers.sca_headers_interface_id =
311 g_lines.sca_headers_interface_id
312 AND g_lines.revenue_type = G_REVENUE
313 AND g_headers.process_status = G_REV_NOT_100
314 GROUP BY g_headers.sca_headers_interface_id)
315 WHERE lines.revenue_type = G_REVENUE
316 AND lines.sca_batch_id = p_sca_batch_id
317 AND EXISTS (SELECT 1
318 FROM cn_sca_headers_interface_gtt headers
319 WHERE headers.process_status = G_REV_NOT_100
320 AND headers.sca_headers_interface_id =
321 lines.sca_headers_interface_id);
322
323 -- second update to address possible rounding error
324 ONLINE_POST_DIST_UPDATE(p_sca_batch_id => p_sca_batch_id);
325
326 -- update header status to ALLOCATED
327 UPDATE cn_sca_headers_interface_gtt headers
328 SET process_status = G_ALLOCATED
329 WHERE headers.process_status = G_REV_NOT_100
330 AND headers.sca_batch_id = p_sca_batch_id;
331
332 -- End of API body.
333
334 EXCEPTION
335
336 WHEN OTHERS THEN
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
338
339 END ONLINE_WTD_REV_DIST;
340
341 -- Start of comments
342 -- API name : BATCH_WTD_REV_DIST
343 -- Description : Batch Weighted Average Revenue Distribution
344 -- Type : Private.
345 -- Function :
346 -- Pre-reqs : None.
347 -- Parameters :
348 -- IN : p_sca_batch_id IN NUMBER Required
349 -- OUT :
350 -- Version : Current version 1.0
351 -- Previous version
352 -- Initial version 1.0
353 -- Notes :
354 -- End of comments
355 PROCEDURE BATCH_WTD_REV_DIST (
356 p_start_header_id IN number,
357 p_end_header_id IN number,
358 x_return_status OUT NOCOPY varchar2)
359 IS
360 l_user_id NUMBER := fnd_global.user_id;
361 l_login_id NUMBER := fnd_global.login_id;
362 BEGIN
363
364 -- Initialize API return status to success
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366
367 -- Start of API body.
368
369 -- calculate and set new revenue allocation
370 -- **********************************************************
371 -- curr_rev_pct * complement_pct
372 -- new rev pct = curr_rev_pct + -----------------------------
373 -- curr_total_rev_pct
374 -- **********************************************************
375 UPDATE cn_sca_lines_output lines
376 SET allocation_percentage =
377 (SELECT ROUND(
378 lines.allocation_percentage + lines.allocation_percentage *
379 (100 - SUM(g_lines.allocation_percentage)) /
380 SUM(g_lines.allocation_percentage),4)
381 FROM cn_sca_headers_interface g_headers,
382 cn_sca_lines_output g_lines
383 WHERE g_headers.sca_headers_interface_id =
384 lines.sca_headers_interface_id
385 AND g_headers.sca_headers_interface_id =
386 g_lines.sca_headers_interface_id
387 AND g_lines.revenue_type = G_REVENUE
388 AND g_headers.process_status = G_REV_NOT_100
389 GROUP BY g_headers.sca_headers_interface_id),
390 last_updated_by = l_user_id,
391 last_update_date = SYSDATE,
392 last_update_login = l_login_id
393 WHERE lines.revenue_type = G_REVENUE
394 AND lines.sca_headers_interface_id
395 BETWEEN p_start_header_id AND p_end_header_id
396 AND EXISTS (SELECT 1
397 FROM cn_sca_headers_interface headers
398 WHERE headers.process_status = G_REV_NOT_100
399 AND headers.sca_headers_interface_id =
400 lines.sca_headers_interface_id);
401
402 -- second update to address possible rounding error
403 BATCH_POST_DIST_UPDATE(p_start_header_id => p_start_header_id,
404 p_end_header_id => p_end_header_id,
405 p_user_id => l_user_id,
406 p_login_id => l_login_id);
407
408 -- update header status to ALLOCATED
409 UPDATE cn_sca_headers_interface headers
410 SET process_status = G_ALLOCATED,
411 last_updated_by = l_user_id,
412 last_update_date = SYSDATE,
413 last_update_login = l_login_id
414 WHERE headers.process_status = G_REV_NOT_100
415 AND headers.sca_headers_interface_id
416 BETWEEN p_start_header_id AND p_end_header_id;
417
418 -- End of API body.
419
420 EXCEPTION
421
422 WHEN OTHERS THEN
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
424
425 END BATCH_WTD_REV_DIST;
426
427 -- Start of comments
428 -- API name : CONC_CN_TRX_LOAD
429 -- Description : Online Even Revenue Distribution
430 -- Type : Private.
431 -- Function :
432 -- Pre-reqs : None.
433 -- Parameters :
434 -- IN :
435 -- OUT :
436 -- Version : Current version 1.0
437 -- Previous version
438 -- Initial version 1.0
439 -- Notes : Autonomous Transaction.
440 -- End of comments
441 PROCEDURE CONC_CN_TRX_LOAD (
442 p_org_id IN varchar2,
443 p_start_date IN date,
444 p_end_date IN date,
445 x_return_status OUT NOCOPY varchar2,
446 x_msg_count OUT NOCOPY number,
447 x_msg_data OUT NOCOPY varchar2,
448 x_process_audit_id OUT NOCOPY number)
449 IS
450 PRAGMA AUTONOMOUS_TRANSACTION;
451 BEGIN
452
453 -- Start of API body.
454
455 -- set org context
456 fnd_client_info.set_org_context(p_org_id);
457
458 -- call procedure cn_sca_trx_proc_pvt.call_populate_results
459 cn_sca_trx_proc_pvt.call_populate_results(
460 p_api_version => 1.0,
461 p_start_date => p_start_date,
462 p_end_date => p_end_date,
463 p_org_id => p_org_id,
464 x_return_status => x_return_status,
465 x_msg_count => x_msg_count,
466 x_msg_data => x_msg_data,
467 x_process_audit_id => x_process_audit_id);
468
469 -- End of API body.
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474 FND_MSG_PUB.Count_And_Get(
475 p_count => x_msg_count,
476 p_data => x_msg_data,
477 p_encoded => FND_API.G_FALSE);
478
479 END CONC_CN_TRX_LOAD;
480
481 -- PUBLIC PROCEDURES
482 -- =================
483
484 -- Start of comments
485 -- API name : START_PROCESS
486 -- Description : Starts the WF process, for ONLINE Revenue Distribution.
487 -- Type : Public.
488 -- Function :
489 -- Pre-reqs : None.
490 -- Parameters :
491 -- IN : p_sca_batch_id IN NUMBER Required
492 -- p_wf_process IN VARCHAR2 Required
493 -- p_wf_item_type IN VARCHAR2 Required
494 -- OUT :
495 -- Version : Current version 1.0
496 -- Previous version
497 -- Initial version 1.0
498 -- Notes : p_sca_batch_id - sca_batch_id processed by SCA Engine
499 -- p_wf_process - 'CN_SCA_REV_DIST_PR'
500 -- p_wf_item_type - 'CNSCARPR'
501 -- Exception Msgs. : CN_SCA_WF_INVLD_BATCH_ID - Invalid batch identifier.
502 -- CN_SCA_WF_INVLD_PR_NAME - Invalid process name sqlplus apps/apps@cnxd1r11 @cnvscaps.pls
503 -- CN_SCA_WF_INVLD_ITEM_TYPE - Invalid item type sqlplus apps/apps@cnxd1r11 @cnvscapb.pls
504 -- CN_SCA_WF_NO_TRX_SRC - Incomplete transaction data.
505 -- CN_SCA_WF_NO_PROFILE_VAL - Profile value is undefined.
506 --
507 -- End of comments
508 PROCEDURE START_PROCESS (
509 p_sca_batch_id IN number,
510 p_wf_process IN varchar2,
511 p_wf_item_type IN varchar2
512 )
513 AS
514 -- Local Variables
515 l_process varchar2(30);
516 l_itemType varchar2(8);
517 l_itemKey varchar2(240);
518
519 l_trxSrc varchar2(30);
520 l_appId number;
521 l_orgId varchar2(30);
522 l_revFunc varchar2(30);
523
524 BEGIN
525
526 -- Start of API body
527
528 -- Validate input
529 -- ==============
530
531 -- 1. p_sca_batch_id
532 IF (p_sca_batch_id = NULL) THEN
533 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_BATCH_ID'));
534 END IF;
535
536 l_itemKey := p_sca_batch_id || '.' || wf_core.random;
537
538 -- 2. p_wf_process
539 IF UPPER(p_wf_process) <> 'CN_SCA_REV_DIST_PR' THEN
540 wf_core.token('NAME',p_wf_process);
541 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_PR_NAME'));
542 END IF;
543
544 l_process := p_wf_process;
545
546 -- 3. p_wf_item_type
547 IF UPPER(p_wf_item_type) <> 'CNSCARPR' THEN
548 wf_core.token('NAME',p_wf_item_type);
549 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_ITEM_TYPE'));
550 END IF;
551
552 l_itemType := p_wf_item_type;
553
554 -- Create WF Process
555 -- =================
556 wf_engine.createprocess(itemtype => l_itemType,
557 itemkey => l_itemKey,
558 process => l_process);
559
560 -- Set Item Attributes
561 -- ===================
562
563 -- 1. sca_batch_id (NUMBER)
564 -- ------------------------
565 wf_engine.setitemattrnumber(itemtype => l_itemType,
566 itemkey => l_itemKey,
567 aname => 'SCA_BATCH_ID',
568 avalue => p_sca_batch_id);
569
570 -- 2. org_id (TEXT)
571 -- ----------------
572 -- get org_id
573 l_orgId := fnd_profile.value('ORG_ID');
574
575 -- set item attribute
576 wf_engine.setitemattrtext(itemtype => l_itemType,
577 itemkey => l_itemKey,
578 aname => 'ORG_ID',
579 avalue => l_orgId);
580
581 -- 3. trx_source (TEXT)
582 -- --------------------
583 BEGIN
584 -- get trx_source
585 SELECT shig.transaction_source
586 INTO l_trxSrc
587 FROM cn_sca_headers_interface_gtt shig
588 WHERE shig.sca_batch_id = p_sca_batch_id
589 AND ROWNUM = 1;
590
591 -- if not found, raise exception
592 EXCEPTION
593 WHEN NO_DATA_FOUND THEN
594 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_NO_TRX_SRC'));
595 END;
596
597 -- set item attribute
598 wf_engine.setitemattrtext(itemtype => l_itemType,
599 itemkey => l_itemKey,
600 aname => 'TRX_SOURCE',
601 avalue => l_trxSrc);
602
603 -- 4. rev_dist_func (TEXT)
604 -- -----------------------
605 BEGIN
606 -- get application_id based on transaction source
607 SELECT fa.application_id
608 INTO l_appId
609 FROM fnd_application fa
610 WHERE fa.application_short_name = l_trxSrc;
611
612 -- if not found, then set to -1 (custom/noop)
613 EXCEPTION
614 WHEN NO_DATA_FOUND THEN
615 l_appId := -1;
616 END;
617
618 -- get profile CN_SCA_REV_NOT_100 value (app/site level only)
619 l_revFunc := fnd_profile.value_specific
620 (name => G_PROFILE,
621 application_id => l_appId,
622 org_id => l_orgId,
623 server_id => fnd_global.server_id);
624
625 -- if not found, raise exception
626 IF (l_revFunc = NULL) THEN
627 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_NO_PROFILE_VAL'));
628 END IF;
629
630 -- set item attribute
631 wf_engine.setitemattrtext(itemtype => l_itemType,
632 itemkey => l_itemKey,
633 aname => 'REV_DIST_FUNC',
634 avalue => l_revFunc);
635
636 -- 5. online_flag (TEXT)
637 -- ---------------------
638 -- defauls to 'Y', so no need to set
639
640 -- Start WF Process
641 -- ================
642 wf_engine.startprocess(itemtype => l_itemType,
643 itemkey => l_itemKey);
644
645 -- End of API body
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 wf_core.context('CN_SCA_WF_PKG',
650 'CN_SCA_REV_DIST_PR',
651 l_itemKey);
652 RAISE;
653
654 END START_PROCESS;
655
656 -- Start of comments
657 -- API name : START_PROCESS
658 -- Description : Starts the WF process, for BATCH Revenue Distribution.
659 -- Type : Public.
660 -- Function :
661 -- Pre-reqs : None.
662 -- Parameters :
663 -- IN : p_start_header_id IN NUMBER Required
664 -- p_end_header_id IN NUMBER Required
665 -- p_trx_source IN VARCHAR2 Required
666 -- p_wf_process IN VARCHAR2 Required
667 -- p_wf_item_type IN VARCHAR2 Required
668 -- OUT :
669 -- Version : Current version 1.0
670 -- Previous version
671 -- Initial version 1.0
672 -- Notes : p_start_header_id - lowest sca_headers_interface_id
673 -- p_end_header_id - highest sca_header_interface_id
674 -- p_trx_source - lookup code of type SCA_TRX_SOURCES
675 -- p_wf_process - 'CN_SCA_REV_DIST_PR'
676 -- p_wf_item_type - 'CNSCARPR'
677 -- Exception Msgs. : CN_SCA_WF_INVLD_ST_HEADER_ID - Invalid start header identifier.
678 -- CN_SCA_WF_INVLD_END_HEADER_ID - Invalid end header identifier.
679 -- CN_SCA_WF_INVLD_TRX_SRC - Invalid transaction source.
680 -- CN_SCA_WF_INVLD_PR_NAME - Invalid process name sqlplus apps/apps@cnxd1r11 @cnvscads.pls
681 -- CN_SCA_WF_INVLD_ITEM_TYPE - Invalid item type sqlplus apps/apps@cnxd1r11 @cnvscadb.pls
682 -- CN_SCA_WF_NO_PROFILE_VAL - Profile value is undefined.
683 -- End of comments
684 PROCEDURE START_PROCESS (
685 p_start_header_id IN number,
686 p_end_header_id IN number,
687 p_trx_source IN varchar2,
688 p_wf_process IN varchar2,
689 p_wf_item_type IN varchar2
690 )
691 AS
692 -- Local Variables
693 l_process varchar2(30);
694 l_itemType varchar2(8);
695 l_itemKey varchar2(240);
696
697 l_appId number;
698 l_orgId varchar2(30);
699 l_revFunc varchar2(30);
700
701 BEGIN
702
703 -- Start of API body
704
705 -- Validate input
706 -- ==============
707
708 -- 1. p_start_header_id
709 IF (p_start_header_id = NULL) THEN
710 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_ST_HEADER_ID'));
711 END IF;
712
713 -- 2. p_end_header_id
714 IF (p_end_header_id = NULL) THEN
715 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_END_HEADER_ID'));
716 END IF;
717
718 -- 3. p_trx_source
719 IF (p_trx_source = NULL) THEN
720 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_TRX_SRC'));
721 END IF;
722
723 -- 4. p_wf_process
724 IF (UPPER(p_wf_process) <> 'CN_SCA_REV_DIST_PR') THEN
725 wf_core.token('NAME',p_wf_process);
726 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_PR_NAME'));
727 END IF;
728
729 l_process := p_wf_process;
730
731 -- 5. p_wf_item_type
732 IF UPPER(p_wf_item_type) <> 'CNSCARPR' THEN
733 wf_core.token('NAME',p_wf_item_type);
734 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_ITEM_TYPE'));
735 END IF;
736
737 l_itemType := p_wf_item_type;
738
739 -- Create WF Process
740 -- =================
741 l_itemKey := p_start_header_id || '-' || p_end_header_id || '.' ||
742 wf_core.random;
743
744 wf_engine.createprocess(itemtype => p_wf_item_type,
745 itemkey => l_itemKey,
746 process => p_wf_process);
747
748 -- Set Item Attributes
749 -- ===================
750
751 -- 1. start_header_id (NUMBER)
752 -- ---------------------------
753 wf_engine.setitemattrnumber(itemtype => l_itemType,
754 itemkey => l_itemKey,
755 aname => 'START_HEADER_ID',
756 avalue => p_start_header_id);
757
758 -- 2. end_header_id (NUMBER)
759 -- -------------------------
760 wf_engine.setitemattrnumber(itemtype => l_itemType,
761 itemkey => l_itemKey,
762 aname => 'END_HEADER_ID',
763 avalue => p_end_header_id);
764
765 -- 3. org_id (TEXT)
766 -- ----------------
767 -- get org_id
768 l_orgId := fnd_profile.value('ORG_ID');
769
770 -- set item attribute
771 wf_engine.setitemattrtext(itemtype => l_itemType,
772 itemkey => l_itemKey,
773 aname => 'ORG_ID',
774 avalue => l_orgId);
775
776 -- 4. trx_source (TEXT)
777 -- --------------------
778 -- set item attribute
779 wf_engine.setitemattrtext(itemtype => l_itemType,
780 itemkey => l_itemKey,
781 aname => 'TRX_SOURCE',
782 avalue => p_trx_source);
783
784 -- 5. rev_dist_func (TEXT)
785 -- -----------------------
786 BEGIN
787 -- get application_id based on transaction source
788 SELECT fa.application_id
789 INTO l_appId
790 FROM fnd_application fa
791 WHERE fa.application_short_name = UPPER(TRIM(p_trx_source));
792
793 -- if not found, then set to -1 (custom/noop)
794 EXCEPTION
795 WHEN NO_DATA_FOUND THEN
796 l_appId := -1;
797 END;
798
799 -- get profile CN_SCA_REV_NOT_100 value (app/site level only)
800 l_revFunc := fnd_profile.value_specific
801 (name => G_PROFILE,
802 application_id => l_appId,
803 org_id => l_orgId,
804 server_id => fnd_global.server_id);
805
806 -- if not found, raise exception
807 IF (l_revFunc = NULL) THEN
808 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_NO_PROFILE_VAL'));
809 END IF;
810
811 -- set item attribute
812 wf_engine.setitemattrtext(itemtype => l_itemType,
813 itemkey => l_itemKey,
814 aname => 'REV_DIST_FUNC',
815 avalue => l_revFunc);
816
817 -- 6. online_flag (TEXT)
818 -- ---------------------
819 -- set item attribute
820 wf_engine.setitemattrtext(itemtype => l_itemType,
821 itemkey => l_itemKey,
822 aname => 'ONLINE_FLAG',
823 avalue => 'N');
824
825 -- Start WF Process
826 -- ================
827 wf_engine.startprocess(itemtype => l_itemType,
828 itemkey => l_itemKey);
829
830 -- End of API body
831
832 EXCEPTION
833 WHEN OTHERS THEN
834 wf_core.context('CN_SCA_WF_PKG',
835 'CN_SCA_REV_DIST_PR',
836 l_itemKey);
837 RAISE;
838
839 END START_PROCESS;
840
841 -- Start of comments
842 -- API name : START_PROCESS
843 -- Description : Starts the WF process, for BATCH Transaction Loading.
844 -- Type : Public.
845 -- Function :
846 -- Pre-reqs : None.
847 -- Parameters :
848 -- IN : p_start_date IN DATE Required
849 -- p_end_date IN DATE Required
850 -- p_trx_source IN VARCHAR2 Required
851 -- p_wf_process IN VARCHAR2 Required
852 -- p_wf_item_type IN VARCHAR2 Required
853 -- OUT : x_wf_item_key OUT VARCHAR2
854 -- Version : Current version 1.0
855 -- Previous version
856 -- Initial version 1.0
857 -- Notes : p_start_date - lowest processed_date
858 -- p_end_date - highest processed_date
859 -- p_trx_source - lookup code of type SCA_TRX_SOURCES
860 -- p_wf_process - 'CN_SCA_TRX_LOAD_PR'
861 -- p_wf_item_type - 'CNSCARPR'
862 -- x_wf_item_key - workflow item key
863 -- Includes programmatic deferal of process to WF background
864 -- process.
865 -- Exception Msgs. : CN_SCA_WF_INVLD_TRX_SRC - Invalid transaction source.
866 -- CN_SCA_WF_INVLD_PR_NAME - Invalid process name sqlplus apps/apps@cnxd1r11 @cnvscabs.pls
867 -- CN_SCA_WF_INVLD_ITEM_TYPE - Invalid item type sqlplus apps/apps@cnxd1r11 @cnvscabb.plsname
868 -- End of comments
869 PROCEDURE START_PROCESS (
870 p_start_date IN date,
871 p_end_date IN date,
872 p_trx_source IN varchar2,
873 p_org_id IN number,
874 p_wf_process IN varchar2,
875 p_wf_item_type IN varchar2,
876 x_wf_item_key OUT NOCOPY varchar2
877 )
878 IS
879 -- Local Variables
880 l_process varchar2(30);
881 l_itemType varchar2(8);
882 l_itemKey varchar2(240);
883 l_save_threshold number;
884
885 --l_orgId varchar2(30);
886
887 BEGIN
888
889 -- Start of API body
890
891 -- Validate input
892 -- ==============
893
894 -- Start/End Date validation is handled by caller
895
896 -- 1. p_trx_source
897 IF (p_trx_source = NULL) THEN
898 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_TRX_SRC'));
899 END IF;
900
901 -- 2. p_wf_process
902 IF (UPPER(p_wf_process) <> 'CN_SCA_TRX_LOAD_PR') THEN
903 wf_core.token('NAME',p_wf_process);
904 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_PR_NAME'));
905 END IF;
906
907 l_process := p_wf_process;
908
909 -- 3. p_wf_item_type
910 IF UPPER(p_wf_item_type) <> 'CNSCARPR' THEN
911 wf_core.token('NAME',p_wf_item_type);
912 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_ITEM_TYPE'));
913 END IF;
914
915 l_itemType := p_wf_item_type;
916
917 -- Modify WF Engine threshold to force deferal of process
918 -- ======================================================
919 l_save_threshold := wf_engine.threshold;
920 wf_engine.threshold := -1;
921
922 -- Create WF Process
923 -- =================
924 l_itemKey := TO_CHAR(p_start_date,'YYYY/MM/DD') || '-' ||
925 TO_CHAR(p_end_date,'YYYY/MM/DD') || '.' ||
926 wf_core.random;
927
928 wf_engine.createprocess(itemtype => p_wf_item_type,
929 itemkey => l_itemKey,
930 process => p_wf_process);
931
932 -- Set Item Attributes
933 -- ===================
934
935 -- 1. start_date (DATE)
936 -- --------------------
937 wf_engine.setitemattrdate(itemtype => l_itemType,
938 itemkey => l_itemKey,
939 aname => 'START_DATE',
940 avalue => p_start_date);
941
942 -- 2. end_date (DATE)
943 -- ------------------
944 wf_engine.setitemattrdate(itemtype => l_itemType,
945 itemkey => l_itemKey,
946 aname => 'END_DATE',
947 avalue => p_end_date);
948
949 -- 3. org_id (TEXT)
950 -- ----------------
951 -- get org_id
952 -- l_orgId := fnd_profile.value('ORG_ID');
953 --l_orgId := p_org_id;
954
955 -- set item attribute
956 wf_engine.setitemattrtext(itemtype => l_itemType,
957 itemkey => l_itemKey,
958 aname => 'ORG_ID',
959 avalue => p_org_id);
960
961 -- 4. trx_source (TEXT)
962 -- --------------------
963 -- set item attribute
964 wf_engine.setitemattrtext(itemtype => l_itemType,
965 itemkey => l_itemKey,
966 aname => 'TRX_SOURCE',
967 avalue => p_trx_source);
968
969 -- Start WF Process
970 -- ================
971 wf_engine.startprocess(itemtype => l_itemType,
972 itemkey => l_itemKey);
973
974 -- Restore WF Engine threshold
975 -- ===========================
976 wf_engine.threshold := l_save_threshold;
977
978 -- Populate out param
979 -- ==================
980 x_wf_item_key := l_itemKey;
981
982 -- End of API body
983
984 EXCEPTION
985
986 WHEN OTHERS THEN
987 wf_core.context('CN_SCA_WF_PKG',
988 'CN_SCA_TRX_LOAD_PR',
989 l_itemKey);
990 RAISE;
991
992 END START_PROCESS;
993
994 -- Start of comments
995 -- API name : SELECTOR
996 -- Description : Determines which WF process to run by default.
997 -- Type : Private.
998 -- Function :
999 -- Pre-reqs : None.
1000 -- Parameters :
1001 -- IN : itemType IN VARCHAR2 Required
1002 -- itemKey IN VARCHAR2 Required
1003 -- actId IN NUMBER Required
1004 -- funcMode IN VARCHAR2 Required
1005 -- OUT : resoultOut OUT VARCHAR2(30)
1006 -- Version : Current version 1.0
1007 -- Previous version
1008 -- Initial version 1.0
1009 -- Notes : itemType - A valid item type from WF_ITEM_TYPES table.
1010 -- itemKey - A string generated from application object's
1011 -- PRIMARY key.
1012 -- actId - The function activity (instance ID)
1013 -- funcMode - Run/Cancel.
1014 -- resultOut - Name of default workflow process to run,
1015 -- 'CN_SCA_REV_DIST_PR' (Revenue Distribution)
1016 -- End of comments
1017 PROCEDURE SELECTOR (
1018 itemType IN varchar2,
1019 itemKey IN varchar2,
1020 actId IN number,
1021 funcMode IN varchar2,
1022 resultOut OUT NOCOPY varchar2)
1023 IS
1024 BEGIN
1025
1026 -- RUN mode: Normal process execution
1027 IF (funcMode = 'RUN') THEN
1028 IF UPPER(itemType) = 'CNSCARPR' THEN
1029 resultOut := 'CN_SCA_REV_DIST_PR';
1030 RETURN;
1031 ELSE
1032 resultOut := wf_engine.eng_error || ':' || wf_engine.eng_null;
1033 -- we do NOT return control to WF, but rather propagate an exception
1034 wf_core.token('NAME',itemType);
1035 wf_core.raise(FND_MESSAGE.get_string('CN','CN_SCA_WF_INVLD_ITEM_TYPE'));
1036 END IF;
1037 END IF;
1038
1039 -- ANY OTHER mode (NOT implemented)
1040 resultOut := wf_engine.eng_null;
1041 RETURN;
1042
1043 EXCEPTION
1044 -- Any other mode but RUN
1045 WHEN OTHERS THEN
1046 wf_core.context('CN_SCA_WF_PKG',
1047 'CN_SCA_REV_DIST_PR',
1048 'SELECTOR',
1049 itemType,
1050 itemKey,
1051 TO_CHAR(actId),
1052 funcMode);
1053 RAISE;
1054
1055 END SELECTOR;
1056
1057 -- Start of comments
1058 -- API name : TRX_LOAD_SELECT
1059 -- Description : Determines which Revenue Distribution function to run.
1060 -- Type : Private.
1061 -- Function :
1062 -- Pre-reqs : None.
1063 -- Parameters :
1064 -- IN : itemType IN VARCHAR2 Required
1065 -- itemKey IN VARCHAR2 Required
1066 -- actId IN NUMBER Required
1067 -- funcMode IN VARCHAR2 Required
1068 -- OUT : resoultOut OUT VARCHAR2(30)
1069 -- Version : Current version 1.0
1070 -- Previous version
1071 -- Initial version 1.0
1072 -- Notes : itemType - A valid item type from WF_ITEM_TYPES table
1073 -- itemKey - A string generated from application object's
1074 -- PRIMARY key
1075 -- actId - The function activity (instance ID)
1076 -- funcMode - Run/Cancel
1077 -- resultOut - CN_SCA_REV_FUNC lookup code:
1078 -- 'COMPLETE:EVEN'
1079 -- 'COMPLETE:WTD'
1080 -- 'COMPLETE:CUSTOM'
1081 -- End of comments
1082 PROCEDURE REV_DIST_SELECT (
1083 itemType IN varchar2,
1084 itemKey IN varchar2,
1085 actId IN number,
1086 funcMode IN varchar2,
1087 resultOut OUT NOCOPY varchar2)
1088 IS
1089 -- Local Variables
1090 l_revFunc varchar2(30);
1091
1092 BEGIN
1093
1094 -- RUN mode
1095 IF (funcMode = 'RUN') THEN
1096
1097 -- get rev_dist_func from item
1098 l_revFunc := wf_engine.getitemattrtext(itemtype => itemType,
1099 itemkey => itemKey,
1100 aname => 'REV_DIST_FUNC');
1101
1102 -- return resultOut
1103 resultOut := wf_engine.eng_completed || ':' || l_revFunc;
1104 RETURN;
1105
1106 END IF; -- funcMode = 'RUN'
1107
1108 -- CANCEL mode
1109 IF (funcMode = 'CANCEL') THEN
1110
1111 resultOut := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1112 RETURN;
1113
1114 END IF;
1115
1116 -- ANY OTHER mode (NOT implemented)
1117 resultOut := wf_engine.eng_null;
1118 RETURN;
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 wf_core.context('CN_SCA_WF_PKG',
1123 'CN_SCA_REV_DIST_PR',
1124 'REV_DIST_SELECT',
1125 itemType,
1126 itemKey,
1127 TO_CHAR(actId),
1128 funcMode);
1129 RAISE;
1130
1131 END REV_DIST_SELECT;
1132
1133 -- Start of comments
1134 -- API name : EVEN_REV_DIST
1135 -- Description : Executes EVEN revenue distribution.
1136 -- Type : Private.
1137 -- Function :
1138 -- Pre-reqs : None.
1139 -- Parameters :
1140 -- IN : itemType IN VARCHAR2 Required
1141 -- itemKey IN VARCHAR2 Required
1142 -- actId IN NUMBER Required
1143 -- funcMode IN VARCHAR2 Required
1144 -- OUT : resoultOut OUT VARCHAR2(30)
1145 -- Version : Current version 1.0
1146 -- Previous version
1147 -- Initial version 1.0
1148 -- Notes : itemType - A valid item type from WF_ITEM_TYPES table
1149 -- itemKey - A string generated from application object's
1150 -- PRIMARY key
1151 -- actId - The function activity (instance ID)
1152 -- funcMode - Run/Cancel
1153 -- resultOut - 'COMPLETE:'
1154 -- End of comments
1155 PROCEDURE EVEN_REV_DIST (
1156 itemType IN varchar2,
1157 itemKey IN varchar2,
1158 actId IN number,
1159 funcMode IN varchar2,
1160 resultOut OUT NOCOPY varchar2)
1161 IS
1162 -- Local Variables
1163 l_orgId varchar2(30);
1164 l_revFunc varchar2(30);
1165 l_scaBatchId number;
1166 l_startHeaderId number;
1167 l_endHeaderId number;
1168 l_onlineFlag varchar2(1);
1169 l_return_status varchar2(1);
1170
1171 BEGIN
1172
1173 IF (funcMode = 'RUN') THEN
1174
1175 -- get attr ORG_ID
1176 l_orgId := wf_engine.getitemattrtext(itemtype => itemType,
1177 itemkey => itemKey ,
1178 aname => 'ORG_ID');
1179
1180 -- set org context
1181 fnd_client_info.set_org_context(l_orgId);
1182
1183 -- get attr online_flag
1184 l_onlineFlag := wf_engine.getitemattrtext(itemtype => itemType,
1185 itemkey => itemKey ,
1186 aname => 'ONLINE_FLAG');
1187
1188 -- get attr and execute even distribution process according to online flag
1189 IF (l_onlineFlag = 'Y') THEN
1190 -- get attr sca_batch_id
1191 l_scaBatchId := wf_engine.getitemattrnumber(itemtype => itemType,
1192 itemkey => itemKey ,
1193 aname => 'SCA_BATCH_ID');
1194
1195 -- call private procedure
1196 ONLINE_EVEN_REV_DIST (
1197 p_sca_batch_id => l_scaBatchId,
1198 x_return_status => l_return_status);
1199
1200 ELSIF (l_onlineFlag = 'N') THEN
1201 -- get attr start_header_id
1202 l_startHeaderId := wf_engine.getitemattrnumber(itemtype => itemType,
1203 itemkey => itemKey ,
1204 aname => 'START_HEADER_ID');
1205
1206 -- get attr end_header_id
1207 l_endHeaderId := wf_engine.getitemattrnumber(itemtype => itemType,
1208 itemkey => itemKey ,
1209 aname => 'END_HEADER_ID');
1210
1211 -- call private procedure
1212 BATCH_EVEN_REV_DIST (
1213 p_start_header_id => l_startHeaderId,
1214 p_end_header_id => l_endHeaderId,
1215 x_return_status => l_return_status);
1216
1217 END IF;
1218
1219 -- if not successful, raise exception
1220 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1221 RAISE FND_API.G_EXC_ERROR;
1222 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1224 END IF;
1225
1226 resultOut := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1227 RETURN;
1228
1229 END IF;
1230
1231 -- CANCEL mode
1232 IF (funcMode = 'CANCEL') THEN
1233
1234 resultOut := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1235 RETURN;
1236
1237 END IF;
1238
1239 -- ANY OTHER mode (NOT implemented)
1240 resultOut := wf_engine.eng_null;
1241 RETURN;
1242
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 wf_core.context('CN_SCA_WF_PKG',
1246 'CN_SCA_REV_DIST_PR',
1247 'EVEN_REV_DIST',
1248 itemType,
1249 itemKey,
1250 TO_CHAR(actId),
1251 funcMode);
1252 RAISE;
1253
1254 END EVEN_REV_DIST;
1255
1256 -- Start of comments
1257 -- API name : WTD_REV_DIST
1258 -- Description : Executes WEIGHTED AVERAGE revenue distribution.
1259 -- Type : Private.
1260 -- Function :
1261 -- Pre-reqs : None.
1262 -- Parameters :
1263 -- IN : itemType IN VARCHAR2 Required
1264 -- itemKey IN VARCHAR2 Required
1265 -- actId IN NUMBER Required
1266 -- funcMode IN VARCHAR2 Required
1267 -- OUT : resoultOut OUT VARCHAR2(30)
1268 -- Version : Current version 1.0
1269 -- Previous version
1270 -- Initial version 1.0
1271 -- Notes : itemType - A valid item type from WF_ITEM_TYPES table
1272 -- itemKey - A string generated from application object's
1273 -- PRIMARY key
1274 -- actId - The function activity (instance ID)
1275 -- funcMode - Run/Cancel
1276 -- resultOut - 'COMPLETE:'
1277 -- End of comments
1278 PROCEDURE WTD_REV_DIST (
1279 itemType IN varchar2,
1280 itemKey IN varchar2,
1281 actId IN number,
1282 funcMode IN varchar2,
1283 resultOut OUT NOCOPY varchar2)
1284 IS
1285 -- Local Variables
1286 l_orgId varchar2(30);
1287 l_revFunc varchar2(30);
1288 l_scaBatchId number;
1289 l_startHeaderId number;
1290 l_endHeaderId number;
1291 l_onlineFlag varchar2(1);
1292 l_return_status varchar2(1);
1293
1294 BEGIN
1295
1296 IF (funcMode = 'RUN') THEN
1297
1298 -- get attr ORG_ID
1299 l_orgId := wf_engine.getitemattrtext(itemtype => itemType,
1300 itemkey => itemKey ,
1301 aname => 'ORG_ID');
1302
1303 -- set org context
1304 fnd_client_info.set_org_context(l_orgId);
1305
1306 -- get attr online_flag
1307 l_onlineFlag := wf_engine.getitemattrtext(itemtype => itemType,
1308 itemkey => itemKey ,
1309 aname => 'ONLINE_FLAG');
1310
1311 -- get attr and execute weighted average distribution process according to online flag
1312 IF (l_onlineFlag = 'Y') THEN
1313 -- get attr sca_batch_id
1314 l_scaBatchId := wf_engine.getitemattrnumber(itemtype => itemType,
1315 itemkey => itemKey ,
1316 aname => 'SCA_BATCH_ID');
1317
1318 -- call private procedure
1319 ONLINE_WTD_REV_DIST (
1320 p_sca_batch_id => l_scaBatchId,
1321 x_return_status => l_return_status);
1322
1323 ELSIF (l_onlineFlag = 'N') THEN
1324
1325 -- get attr start_header_id
1326 l_startHeaderId := wf_engine.getitemattrnumber(itemtype => itemType,
1327 itemkey => itemKey ,
1328 aname => 'START_HEADER_ID');
1329
1330 -- get attr end_header_id
1331 l_endHeaderId := wf_engine.getitemattrnumber(itemtype => itemType,
1332 itemkey => itemKey ,
1333 aname => 'END_HEADER_ID');
1334
1335 -- call private procedure
1336 BATCH_WTD_REV_DIST (
1337 p_start_header_id => l_startHeaderId,
1338 p_end_header_id => l_endHeaderId,
1339 x_return_status => l_return_status);
1340
1341 END IF;
1342
1343 -- if not successful, raise exception
1344 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1345 RAISE FND_API.G_EXC_ERROR;
1346 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1348 END IF;
1349
1350 resultOut := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1351 RETURN;
1352
1353 END IF;
1354
1355 -- CANCEL mode
1356 IF (funcMode = 'CANCEL') THEN
1357
1358 resultOut := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1359 RETURN;
1360
1361 END IF;
1362
1363 -- ANY OTHER mode (NOT implemented)
1364 resultOut := wf_engine.eng_null;
1365 RETURN;
1366
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 wf_core.context('CN_SCA_WF_PKG',
1370 'CN_SCA_REV_DIST_PR',
1371 'WTD_REV_DIST',
1372 itemType,
1373 itemKey,
1374 TO_CHAR(actId),
1375 funcMode);
1376 RAISE;
1377
1378 END WTD_REV_DIST;
1379
1380 -- Start of comments
1381 -- API name : TRX_LOAD_SELECT
1382 -- Description : Determines which Transaction Load function to run.
1383 -- Type : Private.
1384 -- Function :
1385 -- Pre-reqs : None.
1386 -- Parameters :
1387 -- IN : itemType IN VARCHAR2 Required
1388 -- itemKey IN VARCHAR2 Required
1389 -- actId IN NUMBER Required
1390 -- funcMode IN VARCHAR2 Required
1391 -- OUT : resoultOut OUT VARCHAR2(30)
1392 -- Version : Current version 1.0
1393 -- Previous version
1394 -- Initial version 1.0
1395 -- Notes : itemType - A valid item type from WF_ITEM_TYPES table
1396 -- itemKey - A string generated from application object's
1397 -- PRIMARY key.
1398 -- actId - The function activity (instance ID)
1399 -- funcMode - Run/Cancel
1400 -- resultOut - CN_SCA_TRX_LOAD_FUNC lookup code:
1401 -- 'COMPLETE:CN'
1402 -- 'COMPLETE:CUSTOM'
1403 -- End of comments
1404 PROCEDURE TRX_LOAD_SELECT (
1405 itemType IN varchar2,
1406 itemKey IN varchar2,
1407 actId IN number,
1408 funcMode IN varchar2,
1409 resultOut OUT NOCOPY varchar2)
1410 IS
1411 -- Local Variables
1412 l_trxSrc varchar2(30);
1413
1414 BEGIN
1415
1416 -- RUN mode
1417 IF (funcMode = 'RUN') THEN
1418
1419 -- get rev_dist_func from item
1420 l_trxSrc := wf_engine.getitemattrtext(itemtype => itemType,
1421 itemkey => itemKey,
1422 aname => 'TRX_SOURCE');
1423
1424 -- if l_trxSrc is NOT CN, then set to 'CUSTOM'
1425 IF (l_trxSrc <> 'CN') THEN
1426 l_trxSrc := 'CUSTOM';
1427 END IF;
1428
1429 -- return resultOut
1430 resultOut := wf_engine.eng_completed || ':' || l_trxSrc;
1431 RETURN;
1432
1433 END IF; -- funcMode = 'RUN'
1434
1435 -- CANCEL mode
1436 IF (funcMode = 'CANCEL') THEN
1437
1438 resultOut := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1439 RETURN;
1440
1441 END IF;
1442
1443 -- ANY OTHER mode (NOT implemented)
1444 resultOut := wf_engine.eng_null;
1445 RETURN;
1446
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 wf_core.context('CN_SCA_WF_PKG',
1450 'CN_SCA_TRX_LOAD_PR',
1451 'TRX_LOAD_SELECT',
1452 itemType,
1453 itemKey,
1454 TO_CHAR(actId),
1455 funcMode);
1456 RAISE;
1457
1458 END TRX_LOAD_SELECT;
1459
1460 -- Start of comments
1461 -- API name : CN_TRX_LOAD
1462 -- Description : Executes Oracle Incentive Compensation Transaction Load.
1463 -- Type : Private.
1464 -- Function :
1465 -- Pre-reqs : None.
1466 -- Parameters :
1467 -- IN : itemType IN VARCHAR2 Required
1468 -- itemKey IN VARCHAR2 Required
1469 -- actId IN NUMBER Required
1470 -- funcMode IN VARCHAR2 Required
1471 -- OUT : resoultOut OUT VARCHAR2(30)
1472 -- Version : Current version 1.0
1473 -- Previous version
1474 -- Initial version 1.0
1475 -- Notes : itemType - A valid item type from WF_ITEM_TYPES table
1476 -- itemKey - A string generated from application object's
1477 -- PRIMARY key
1478 -- actId - The function activity (instance ID)
1479 -- funcMode - Run/Cancel
1480 -- resultOut - 'COMPLETE:'
1481 -- End of comments
1482 PROCEDURE CN_TRX_LOAD (
1483 itemType IN varchar2,
1484 itemKey IN varchar2,
1485 actId IN number,
1486 funcMode IN varchar2,
1487 resultOut OUT NOCOPY varchar2)
1488 IS
1489 -- Local Variables
1490 l_startDate date;
1491 l_endDate date;
1492 l_orgId varchar2(30);
1493 l_return_status varchar2(1);
1494 l_msg_count number;
1495 l_msg_data varchar2(2000);
1496 l_process_audit_id number;
1497
1498 BEGIN
1499
1500 -- RUN mode
1501 IF (funcMode = 'RUN') THEN
1502
1503 -- Get Item Attributes
1504 -- ===================
1505
1506 -- get start_date
1507 l_startDate := wf_engine.getitemattrdate(itemtype => itemType,
1508 itemkey => itemKey,
1509 aname => 'START_DATE');
1510
1511 -- get end_date
1512 l_endDate := wf_engine.getitemattrdate(itemtype => itemType,
1513 itemkey => itemKey,
1514 aname => 'END_DATE');
1515
1516 -- get attr ORG_ID
1517 l_orgId := wf_engine.getitemattrtext(itemtype => itemType,
1518 itemkey => itemKey ,
1519 aname => 'ORG_ID');
1520
1521 -- set org context
1522 fnd_client_info.set_org_context(l_orgId);
1523
1524 -- call private autonomous procedure
1525 CONC_CN_TRX_LOAD(
1526 p_org_id => l_orgId,
1527 p_start_date => l_startDate,
1528 p_end_date => l_endDate,
1529 x_return_status => l_return_status,
1530 x_msg_count => l_msg_count,
1531 x_msg_data => l_msg_data,
1532 x_process_audit_id => l_process_audit_id);
1533
1534 -- set attr PROCESS_AUDIT_ID (if not NULL, regardless of return status)
1535 IF (l_process_audit_id <> NULL) THEN
1536 wf_engine.setitemattrnumber(itemtype => itemType,
1537 itemkey => itemKey,
1538 aname => 'PROCESS_AUDIT_ID',
1539 avalue => l_process_audit_id);
1540 END IF;
1541
1542 -- if not successful, raise exception
1543 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1544 RAISE FND_API.G_EXC_ERROR;
1545 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1547 END IF;
1548
1549 -- return resultOut
1550 resultOut := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1551 RETURN;
1552
1553 END IF; -- funcMode = 'RUN'
1554
1555 -- CANCEL mode
1556 IF (funcMode = 'CANCEL') THEN
1557
1558 resultOut := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1559 RETURN;
1560
1561 END IF;
1562
1563 -- ANY OTHER mode (NOT implemented)
1564 resultOut := wf_engine.eng_null;
1565 RETURN;
1566
1567 EXCEPTION
1568 WHEN OTHERS THEN
1569 wf_core.context('CN_SCA_WF_PKG',
1570 'CN_SCA_TRX_LOAD_PR',
1571 'CN_TRX_LOAD',
1572 itemType,
1573 itemKey,
1574 TO_CHAR(actId),
1575 funcMode,
1576 l_msg_data,
1577 l_process_audit_id);
1578 RAISE;
1579
1580 END CN_TRX_LOAD;
1581
1582 END CN_SCA_WF_PKG;