[Home] [Help]
PACKAGE BODY: APPS.AMS_PARTY_MKT_SEG_LOADER_PVT
Source
1 PACKAGE BODY AMS_Party_Mkt_Seg_Loader_PVT AS
2 /* $Header: amsvldrb.pls 120.2 2005/10/11 00:04:06 aanjaria ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_Party_Mkt_Seg_Loader_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvldrb.pls';
6
7
8 /* variable to on-off the debug messages of the programe */
9 G_DEBUG_LEVEL BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
10
11
12 -- yzhao: type definition for load_party_market... used internally
13 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
14 TYPE VARCHAR2_TBL_TYPE IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
15 -- yzhao: 05/07/2003 SQL bind variable compliance
16 TYPE BIND_VAR_TYPE IS RECORD (
17 BIND_INDEX NUMBER,
18 BIND_TYPE VARCHAR2(1),
19 BIND_CHAR VARCHAR2(2000),
20 BIND_NUMBER NUMBER
21 );
22 TYPE BIND_TBL_TYPE IS TABLE OF BIND_VAR_TYPE INDEX BY BINARY_INTEGER;
23 G_BIND_TYPE_NUMBER CONSTANT VARCHAR2(1) := 'N';
24 G_BIND_TYPE_CHAR CONSTANT VARCHAR2(1) := 'C';
25 G_BIND_VAR_STRING CONSTANT VARCHAR2(9) := ':AMS_BIND';
26
27
28 /*****************************************************************************/
29 -- Procedure
30 -- Write_Log
31 -- Purpose
32 -- writes the Messages for the Concurrent Program
33 --
34 -- History
35 -- 05/05/2000 ptendulk created
36 -- 08/07/2000 ptendulk Commented the procedure as this one is moved to
37 -- AMS_Utility_Pvt .
38 -------------------------------------------------------------------------------
39 --PROCEDURE Write_Log
40 --( p_text IN VARCHAR2 := NULL)
41 --IS
42 -- l_count NUMBER;
43 -- l_msg VARCHAR2(2000);
44 -- l_cnt NUMBER ;
45 --BEGIN
46 -- IF p_text IS NULL THEN
47 -- l_count := FND_MSG_PUB.count_msg;
48 -- FOR l_cnt IN 1 .. l_count LOOP
49 -- l_msg := FND_MSG_PUB.get(l_cnt, FND_API.g_false);
50 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '(' || l_cnt || ') ' || l_msg);
51 -- END LOOP;
52 -- ELSE
53 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_text );
54 -- END IF;
55 --
56 --END Write_Log ;
57
58
59 /*****************************************************************************/
60 -- Procedure
61 -- expire_inactive_party
62 -- Purpose
63 -- expire parties that no longer belong to the given mkt_seg_id
64 --
65 -- Note :
66 -- This Procedure will expire the party using Native SQL
67 --
68 -- History
69 -- 01/21/2000 julou created
70 --
71 -------------------------------------------------------------------------------
72 PROCEDURE Expire_Inactive_Party
73 (
74 p_mkt_seg_id IN NUMBER
75 , p_mkt_seg_flag IN VARCHAR2
76 , p_sql_str IN VARCHAR2
77 , x_return_status OUT NOCOPY VARCHAR2
78 , x_msg_count OUT NOCOPY NUMBER
79 , x_msg_data OUT NOCOPY VARCHAR2
80 )
81 IS
82 l_api_name CONSTANT VARCHAR2(30) := 'Expire_Inactive_Party';
83 TYPE dyna_cur_type IS REF CURSOR;
84
85 CURSOR c_old_party_id IS -- parties already in the table
86 SELECT party_id
87 FROM ams_party_market_segments
88 WHERE market_segment_id = p_mkt_seg_id
89 AND market_segment_flag = p_mkt_seg_flag
90 AND end_date_active IS NULL
91 ORDER BY party_id;
92
93 c_new_party_id dyna_cur_type; -- parties from execution of sql string
94
95 l_old_party_id NUMBER;
96 l_new_party_id NUMBER;
97 l_expire_flag VARCHAR2(1);
98
99 BEGIN
100 x_return_status := FND_API.G_RET_STS_SUCCESS;
101 OPEN c_old_party_id;
102
103 LOOP
104
105 FETCH c_old_party_id INTO l_old_party_id;
106 EXIT WHEN c_old_party_id%NOTFOUND;
107 l_expire_flag := 'Y';
108
109 OPEN c_new_party_id FOR p_sql_str;
110 LOOP
111
112 FETCH c_new_party_id INTO l_new_party_id;
113 EXIT WHEN c_new_party_id%NOTFOUND OR l_expire_flag = 'N';
114
115 IF l_old_party_id = l_new_party_id THEN -- this party will be still active
116
117 l_expire_flag := 'N';
118
119 END IF;
120
121 END LOOP;
122 CLOSE c_new_party_id ;
123
124 IF l_expire_flag = 'Y' THEN -- this party is expired
125
126 UPDATE AMS_PARTY_MARKET_SEGMENTS
127 SET end_date_active = SYSDATE
128 WHERE market_segment_flag = p_mkt_seg_flag
129 AND market_segment_id = p_mkt_seg_id
130 AND party_id = l_old_party_id;
131 END IF;
132
133 END LOOP;
134 CLOSE c_old_party_id ;
135 -- =============================================================================================
136 -- Following Exception block is added by ptendulk on May02-2000 to handle Update Exception
137 -- =============================================================================================
138 EXCEPTION
139 WHEN OTHERS THEN
140 AMS_Utility_Pvt.Debug_Message('Error in expire_inactive_party');
141 --
142 IF(c_old_party_id%ISOPEN)then
143 CLOSE c_old_party_id;
144 END IF;
145 IF(c_new_party_id%ISOPEN)then
146 CLOSE c_new_party_id;
147 END IF;
148
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
150
151 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
152 THEN
153 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
154 END IF;
155
156 FND_MSG_PUB.Count_AND_Get
157 ( p_count => x_msg_count,
158 p_data => x_msg_data,
159 p_encoded => FND_API.G_FALSE
160 );
161
162 END Expire_Inactive_Party;
163
164
165 /*****************************************************************************/
166 -- Procedure
167 -- expire_changed_party
168 -- Purpose
169 -- expire parties that originally belong to other mkt_seg_id and currently
170 -- belong to the give mkt_seg_id
171 --
172 -- Note :
173 -- This Procedure will expire the changed party using Native SQL
174 --
175 -- History
176 -- 01/21/2000 julou created
177 -------------------------------------------------------------------------------
178 PROCEDURE Expire_Changed_Party
179 (
180 p_mkt_seg_id IN NUMBER
181 , p_mkt_seg_flag IN VARCHAR2
182 , p_sql_str IN VARCHAR2
183 , x_return_status OUT NOCOPY VARCHAR2
184 , x_msg_count OUT NOCOPY NUMBER
185 , x_msg_data OUT NOCOPY VARCHAR2
186 )
187 IS
188 l_api_name CONSTANT VARCHAR2(30) := 'Expire_Changed_Party';
189 TYPE dyna_cur_type IS REF CURSOR;
190
191 CURSOR c_old_party_rec IS -- party_id and mkt_seg_id of exsiting party
192 SELECT market_segment_id, party_id FROM AMS_PARTY_MARKET_SEGMENTS
193 WHERE market_segment_id <> p_mkt_seg_id
194 AND market_segment_flag = p_mkt_seg_flag
195 AND end_date_active IS NULL
196 ORDER BY party_id;
197
198 c_new_party_id dyna_cur_type; -- parties from execution of sql string
199
200 l_old_party_id NUMBER;
201 l_new_party_id NUMBER;
202 l_old_mkt_seg_id NUMBER;
203 l_expire_flag VARCHAR2(1);
204
205 BEGIN
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207 OPEN c_old_party_rec;
208
209 LOOP
210
211 FETCH c_old_party_rec INTO l_old_mkt_seg_id, l_old_party_id;
212 EXIT WHEN c_old_party_rec%NOTFOUND;
213
214 l_expire_flag := 'N';
215
216 OPEN c_new_party_id FOR p_sql_str;
217
218 LOOP
219
220 FETCH c_new_party_id INTO l_new_party_id;
221 EXIT WHEN c_new_party_id%NOTFOUND OR l_expire_flag = 'Y';
222
223 IF l_old_party_id = l_new_party_id THEN -- party belongs to new market segment
224
225 l_expire_flag := 'Y';
226
227 END IF;
228
229 END LOOP;
230 CLOSE c_new_party_id ;
231
232 IF l_expire_flag = 'Y' THEN
233
234 UPDATE ams_party_market_segments
235 SET end_date_active = SYSDATE
236 WHERE market_segment_flag = p_mkt_seg_flag
237 AND market_segment_id = l_old_mkt_seg_id
238 AND party_id = l_old_party_id;
239 END IF;
240
241 END LOOP;
242 CLOSE c_old_party_rec ;
243 -- =============================================================================================
244 -- Following Exception block is added by ptendulk on May02-2000 to handle Update Exception
245 -- =============================================================================================
246 EXCEPTION
247 WHEN OTHERS THEN
248 AMS_Utility_Pvt.Debug_Message('Error in expire_changed_party');
249 --
250 IF(c_old_party_rec%ISOPEN)then
251 CLOSE c_old_party_rec;
252 END IF;
253 IF(c_new_party_id%ISOPEN)then
254 CLOSE c_new_party_id;
255 END IF;
256
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
258
259 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
260 THEN
261 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
262 END IF;
263
264 FND_MSG_PUB.Count_AND_Get
265 ( p_count => x_msg_count,
266 p_data => x_msg_data,
267 p_encoded => FND_API.G_FALSE
268 );
269
270 END Expire_Changed_Party;
271
272
273 /*****************************************************************************/
274 -- Procedure
275 -- insert_new_party
276 -- Purpose
277 -- insert a new party if it is not there, update it if it is expired
278 -- do nothing if it is active
279 --
280 -- Note :
281 -- This Procedure will Insert the party using Native SQL
282 --
283 -- History
284 -- 01/21/2000 julou created
285 -------------------------------------------------------------------------------
286 PROCEDURE Insert_New_Party
287 (
288 p_mkt_seg_id IN NUMBER
289 , p_mkt_seg_flag IN VARCHAR2
290 , p_sql_str IN VARCHAR2
291 , x_return_status OUT NOCOPY VARCHAR2
292 , x_msg_count OUT NOCOPY NUMBER
293 , x_msg_data OUT NOCOPY VARCHAR2
294 )
295 IS
296 l_api_name CONSTANT VARCHAR2(30) := 'insert_new_party';
297 TYPE dyna_cur_type IS REF CURSOR;
298
299 CURSOR c_party_count(id IN NUMBER) IS -- check if party is already in table
300 SELECT count(*) FROM AMS_PARTY_MARKET_SEGMENTS
301 WHERE market_segment_flag = p_mkt_seg_flag
302 AND market_segment_id = p_mkt_seg_id
303 AND party_id = id;
304
305 CURSOR c_expire_party_count(id IN NUMBER) IS -- check if party expired
306 SELECT count(*) FROM AMS_PARTY_MARKET_SEGMENTS
307 WHERE market_segment_flag = p_mkt_seg_flag
308 AND market_segment_id = p_mkt_seg_id
309 AND party_id = id
310 AND end_date_active IS NOT NULL;
311
312 CURSOR c_party_mkt_seg_seq IS -- generate an ID
313 SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
314 FROM DUAL;
315
316 CURSOR c_party_mkt_seg_count(party_mkt_seg_id IN NUMBER) IS -- check if ID is unique
317 SELECT count(*)
318 FROM AMS_PARTY_MARKET_SEGMENTS
319 WHERE ams_party_market_segment_id = party_mkt_seg_id;
320
321 c_party_id dyna_cur_type;
322
323 l_party_id NUMBER;
324 l_party_count NUMBER;
325 l_expire_party_count NUMBER;
326 l_party_mkt_seg_id NUMBER;
327 l_count NUMBER;
328
329 BEGIN
330
331 AMS_Utility_Pvt.Debug_Message('String : '||p_sql_str) ;
332 --
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334 OPEN c_party_id FOR p_sql_str;
335
336 LOOP
337
338 FETCH c_party_id INTO l_party_id;
339 EXIT WHEN c_party_id%NOTFOUND;
340
341 OPEN c_party_count(l_party_id);
342 FETCH c_party_count INTO l_party_count;
343 CLOSE c_party_count;
344 AMS_Utility_Pvt.Debug_Message('Insert ');
345 --
346 IF l_party_count = 0 THEN -- new party is not in the table
347
348 LOOP -- generate an unique ID for the record
349
350 OPEN c_party_mkt_seg_seq;
351 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id;
352 CLOSE c_party_mkt_seg_seq;
353
354 OPEN c_party_mkt_seg_count(l_party_mkt_seg_id);
355 FETCH c_party_mkt_seg_count INTO l_count;
356 CLOSE c_party_mkt_seg_count;
357
358 EXIT WHEN l_count = 0;
359
360 END LOOP;
361 AMS_Utility_Pvt.Debug_Message('Insert ');
362 --
363 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
364 (
365 ams_party_market_segment_id
366 , last_update_date
367 , last_updated_by
368 , creation_date
369 , created_by
370 , last_update_login
371 , object_version_number
372 , market_segment_id
373 , market_segment_flag
374 , party_id
375 , start_date_active
376 , end_date_active
377 )
378
379 VALUES
380
381 (
382 l_party_mkt_seg_id
383 , SYSDATE
384 , FND_GLOBAL.user_id
385 , SYSDATE
386 , FND_GLOBAL.user_id
387 , FND_GLOBAL.conc_login_id
388 , 1
389 , p_mkt_seg_id
390 , p_mkt_seg_flag
391 , l_party_id
392 , SYSDATE
393 , NULL
394 );
395
396
397 ELSE
398 AMS_Utility_Pvt.Debug_Message('Update ');
399 --
400 OPEN c_expire_party_count(l_party_id);
401 FETCH c_expire_party_count INTO l_expire_party_count;
402 CLOSE c_expire_party_count;
403
404 IF l_expire_party_count > 0 THEN -- party expired
405
406 UPDATE AMS_PARTY_MARKET_SEGMENTS SET
407 last_update_date = SYSDATE
408 , last_updated_by = FND_GLOBAL.user_id
409 , last_update_login = FND_GLOBAL.conc_login_id
410 , object_version_number = object_version_number + 1
411 , market_segment_id = p_mkt_seg_id
412 , market_segment_flag = p_mkt_seg_flag
413 , party_id = l_party_id
414 , start_date_active =SYSDATE
415 , end_date_active = NULL
416 WHERE market_segment_id = p_mkt_seg_id
417 AND market_segment_flag = p_mkt_seg_flag
418 AND party_id = l_party_id;
419
420
421 END IF;
422
423 END IF;
424
425 END LOOP;
426 -- =============================================================================================
427 -- Following Exception block is added by ptendulk on May02-2000 to handle Insert/Update Exception
428 -- =============================================================================================
429 EXCEPTION
430 WHEN OTHERS THEN
431 AMS_Utility_Pvt.Debug_Message('Error in insert_new_party'||sqlerrm);
432 --
433 IF(c_party_count%ISOPEN)then
434 CLOSE c_party_count;
435 END IF;
436 IF(c_expire_party_count%ISOPEN)then
437 CLOSE c_expire_party_count;
438 END IF;
439 IF(c_party_mkt_seg_seq%ISOPEN)then
440 CLOSE c_party_mkt_seg_seq;
441 END IF;
442 IF(c_party_mkt_seg_count%ISOPEN)then
443 CLOSE c_party_mkt_seg_count;
444 END IF;
445
446 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
447
448 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
449 THEN
450 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
451 END IF;
452
453 FND_MSG_PUB.Count_AND_Get
454 ( p_count => x_msg_count,
455 p_data => x_msg_data,
456 p_encoded => FND_API.G_FALSE
457 );
458
459 END Insert_New_Party;
460
461
462 /*****************************************************************************/
463 -- Procedure
464 -- Expire_Inactive_Party_Dbms
465 -- Purpose
466 -- expire parties that no longer belong to the given mkt_seg_id
467 --
468 -- Note :
469 -- This Procedure will expire the party using DBMS SQL
470 --
471 -- History
472 -- 05/03/2000 ptendulk created
473 --
474 -------------------------------------------------------------------------------
475 PROCEDURE Expire_Inactive_Party_Dbms
476 (
477 p_mkt_seg_id IN NUMBER
478 , p_mkt_seg_flag IN VARCHAR2
479 , p_sql_tbl IN t_party_tab
480 , x_return_status OUT NOCOPY VARCHAR2
481 , x_msg_count OUT NOCOPY NUMBER
482 , x_msg_data OUT NOCOPY VARCHAR2
483 )
484 IS
485 l_api_name CONSTANT VARCHAR2(30) := 'expire_inactive_party';
486 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
487 CURSOR c_old_party_id IS -- parties already in the table
488 SELECT party_id FROM AMS_PARTY_MARKET_SEGMENTS
489 WHERE market_segment_id = p_mkt_seg_id
490 AND market_segment_flag = p_mkt_seg_flag
491 AND end_date_active IS NULL
492 ORDER BY party_id;
493
494 l_old_party_id NUMBER;
495 l_expire_flag VARCHAR2(1);
496
497 BEGIN
498 x_return_status := FND_API.G_RET_STS_SUCCESS;
499
500
501
502 AMS_Utility_PVT.debug_message(l_full_name||': PARSE SQL start');
503 --
504
505 OPEN c_old_party_id;
506 LOOP
507 FETCH c_old_party_id INTO l_old_party_id;
508 EXIT WHEN c_old_party_id%NOTFOUND;
509 l_expire_flag := 'Y';
510
511 FOR i IN p_sql_tbl.FIRST..p_sql_tbl.last
512 LOOP
513 IF l_old_party_id = p_sql_tbl(i) THEN
514 l_expire_flag := 'N';
515 EXIT;
516 END IF;
517 END LOOP;
518
519 IF l_expire_flag = 'Y' THEN -- this party is expired
520
521 UPDATE AMS_PARTY_MARKET_SEGMENTS
522 SET end_date_active = SYSDATE
523 WHERE market_segment_flag = p_mkt_seg_flag
524 AND market_segment_id = p_mkt_seg_id
525 AND party_id = l_old_party_id;
526
527 END IF;
528
529 END LOOP;
530 CLOSE c_old_party_id ;
531 -- =============================================================================================
532 -- Following Exception block is added by ptendulk on May02-2000 to handle Update Exception
533 -- =============================================================================================
534 EXCEPTION
535 WHEN OTHERS THEN
536 AMS_Utility_Pvt.Debug_Message('Error in expire_inactive_party');
537 --
538 IF(c_old_party_id%ISOPEN)then
539 CLOSE c_old_party_id;
540 END IF;
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
542
543 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
544 THEN
545 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
546 END IF;
547
548 FND_MSG_PUB.Count_AND_Get
549 ( p_count => x_msg_count,
550 p_data => x_msg_data,
551 p_encoded => FND_API.G_FALSE
552 );
553
554 END Expire_Inactive_Party_Dbms;
555
556
557 /*****************************************************************************/
558 -- Procedure
559 -- expire_changed_party
560 -- Purpose
561 -- expire parties that originally belong to other mkt_seg_id and currently
562 -- belong to the give mkt_seg_id
563 --
564 -- Note :
565 -- This Procedure will expire the party using DBMS SQL
566 --
567 -- History
568 -- 05/03/2000 ptendulk created
569 -------------------------------------------------------------------------------
570 PROCEDURE Expire_Changed_Party_Dbms
571 (
572 p_mkt_seg_id IN NUMBER
573 , p_mkt_seg_flag IN VARCHAR2
574 , p_sql_tbl IN t_party_tab
575 , x_return_status OUT NOCOPY VARCHAR2
576 , x_msg_count OUT NOCOPY NUMBER
577 , x_msg_data OUT NOCOPY VARCHAR2
578 )
579 IS
580 l_api_name CONSTANT VARCHAR2(30) := 'expire_changed_party';
581
582 CURSOR c_old_party_rec IS -- party_id and mkt_seg_id of exsiting party
583 SELECT market_segment_id, party_id
584 FROM AMS_PARTY_MARKET_SEGMENTS
585 WHERE market_segment_id <> p_mkt_seg_id
586 AND market_segment_flag = p_mkt_seg_flag
587 AND end_date_active IS NULL
588 ORDER BY party_id;
589
590 l_old_party_id NUMBER;
591 l_old_mkt_seg_id NUMBER;
592 l_expire_flag VARCHAR2(1);
593
594 BEGIN
595 x_return_status := FND_API.G_RET_STS_SUCCESS;
596 OPEN c_old_party_rec;
597
598 LOOP
599
600 FETCH c_old_party_rec INTO l_old_mkt_seg_id, l_old_party_id;
601 EXIT WHEN c_old_party_rec%NOTFOUND;
602
603 l_expire_flag := 'N';
604
605 FOR i IN p_sql_tbl.FIRST..p_sql_tbl.last
606 LOOP
607 IF l_old_party_id = p_sql_tbl(i) THEN
608 l_expire_flag := 'Y';
609 EXIT;
610 END IF;
611 END LOOP;
612
613 IF l_expire_flag = 'Y' THEN
614
615 UPDATE AMS_PARTY_MARKET_SEGMENTS
616 SET end_date_active = SYSDATE
617 WHERE market_segment_flag = p_mkt_seg_flag
618 AND market_segment_id = l_old_mkt_seg_id
619 AND party_id = l_old_party_id;
620
621
622 END IF;
623
624 END LOOP;
625 CLOSE c_old_party_rec ;
626 -- =============================================================================================
627 -- Following Exception block is added by ptendulk on May02-2000 to handle Update Exception
628 -- =============================================================================================
629 EXCEPTION
630 WHEN OTHERS THEN
631 AMS_Utility_Pvt.Debug_Message('Error in expire_changed_party');
632 --
633 IF(c_old_party_rec%ISOPEN)then
634 CLOSE c_old_party_rec;
635 END IF;
636
637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
638
639 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
640 THEN
641 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
642 END IF;
643
644 FND_MSG_PUB.Count_AND_Get
645 ( p_count => x_msg_count,
646 p_data => x_msg_data,
647 p_encoded => FND_API.G_FALSE
648 );
649
650 END Expire_Changed_Party_Dbms;
651
652
653 /*****************************************************************************/
654 -- Procedure
655 -- insert_new_party
656 -- Purpose
657 -- insert a new party if it is not there, update it if it is expired
658 -- do nothing if it is active
659 --
660 -- Note :
661 -- This Procedure will expire the party using Native SQL
662 --
663 -- History
664 -- 05/03/2000 ptendulk created
665 -------------------------------------------------------------------------------
666 PROCEDURE Insert_New_Party_Dbms
667 (
668 p_mkt_seg_id IN NUMBER
669 , p_mkt_seg_flag IN VARCHAR2
670 , p_sql_tbl IN t_party_tab
671 , x_return_status OUT NOCOPY VARCHAR2
672 , x_msg_count OUT NOCOPY NUMBER
673 , x_msg_data OUT NOCOPY VARCHAR2
674 )
675 IS
676 l_api_name CONSTANT VARCHAR2(30) := 'insert_new_party';
677
678 CURSOR c_party_count(id IN NUMBER) IS -- check if party is already in table
679 SELECT count(*) FROM AMS_PARTY_MARKET_SEGMENTS
680 WHERE market_segment_flag = p_mkt_seg_flag
681 AND market_segment_id = p_mkt_seg_id
682 AND party_id = id;
683
684 CURSOR c_expire_party_count(id IN NUMBER) IS -- check if party expired
685 SELECT count(*) FROM AMS_PARTY_MARKET_SEGMENTS
686 WHERE market_segment_flag = p_mkt_seg_flag
687 AND market_segment_id = p_mkt_seg_id
688 AND party_id = id
689 AND end_date_active IS NOT NULL;
690
691 CURSOR c_party_mkt_seg_seq IS -- generate an ID
692 SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
693 FROM DUAL;
694
695 CURSOR c_party_mkt_seg_count(party_mkt_seg_id IN NUMBER) IS -- check if ID is unique
696 SELECT count(*)
697 FROM AMS_PARTY_MARKET_SEGMENTS
698 WHERE ams_party_market_segment_id = party_mkt_seg_id;
699
700
701 l_party_id NUMBER;
702 l_party_count NUMBER;
703 l_expire_party_count NUMBER;
704 l_party_mkt_seg_id NUMBER;
705 l_count NUMBER;
706
707 BEGIN
708
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710
711 FOR i IN p_sql_tbl.FIRST..p_sql_tbl.last
712 LOOP
713 l_party_id := p_sql_tbl(i) ;
714
715 OPEN c_party_count(l_party_id);
716 FETCH c_party_count INTO l_party_count;
717 CLOSE c_party_count;
718
719 AMS_Utility_Pvt.Debug_Message('Insert ');
720 --
721 IF l_party_count = 0 THEN -- new party is not in the table
722
723 LOOP -- generate an unique ID for the record
724
725 OPEN c_party_mkt_seg_seq;
726 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id;
727 CLOSE c_party_mkt_seg_seq;
728
729 OPEN c_party_mkt_seg_count(l_party_mkt_seg_id);
730 FETCH c_party_mkt_seg_count INTO l_count;
731 CLOSE c_party_mkt_seg_count;
732
733 EXIT WHEN l_count = 0;
734
735 END LOOP;
736
737 AMS_Utility_Pvt.Debug_Message('Insert ');
738 --
739 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
740 (
741 ams_party_market_segment_id
742 , last_update_date
743 , last_updated_by
744 , creation_date
745 , created_by
746 , last_update_login
747 , object_version_number
748 , market_segment_id
749 , market_segment_flag
750 , party_id
751 , start_date_active
752 , end_date_active
753 )
754 VALUES
755 (
756 l_party_mkt_seg_id
757 , SYSDATE
758 , FND_GLOBAL.user_id
759 , SYSDATE
760 , FND_GLOBAL.user_id
761 , FND_GLOBAL.conc_login_id
762 , 1
763 , p_mkt_seg_id
764 , p_mkt_seg_flag
765 , l_party_id
766 , SYSDATE
767 , NULL
768 );
769
770
771 ELSE
772 AMS_Utility_Pvt.Debug_Message('Update ');
773 --
774 OPEN c_expire_party_count(l_party_id);
775 FETCH c_expire_party_count INTO l_expire_party_count;
776 CLOSE c_expire_party_count;
777
778 IF l_expire_party_count > 0 THEN -- party expired
779
780 UPDATE AMS_PARTY_MARKET_SEGMENTS SET
781 last_update_date = SYSDATE
782 , last_updated_by = FND_GLOBAL.user_id
783 , last_update_login = FND_GLOBAL.conc_login_id
784 , object_version_number = object_version_number + 1
785 , market_segment_id = p_mkt_seg_id
786 , market_segment_flag = p_mkt_seg_flag
787 , party_id = l_party_id
788 , start_date_active =SYSDATE
789 , end_date_active = NULL
790 WHERE market_segment_id = p_mkt_seg_id
791 AND market_segment_flag = p_mkt_seg_flag
792 AND party_id = l_party_id;
793
794
795 END IF;
796
797 END IF;
798
799 END LOOP;
800
801 -- =============================================================================================
802 -- Following Exception block is added by ptendulk on May02-2000 to handle Insert/Update Exception
803 -- =============================================================================================
804 EXCEPTION
805 WHEN OTHERS THEN
806 AMS_Utility_Pvt.Debug_Message('Error in insert_new_party'||sqlerrm);
807 --
808 IF(c_party_count%ISOPEN)then
809 CLOSE c_party_count;
810 END IF;
811 IF(c_expire_party_count%ISOPEN)then
812 CLOSE c_expire_party_count;
813 END IF;
814 IF(c_party_mkt_seg_seq%ISOPEN)then
815 CLOSE c_party_mkt_seg_seq;
816 END IF;
817 IF(c_party_mkt_seg_count%ISOPEN)then
818 CLOSE c_party_mkt_seg_count;
819 END IF;
820
821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
822
823 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
824 THEN
825 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
826 END IF;
827
828 FND_MSG_PUB.Count_AND_Get
829 ( p_count => x_msg_count,
830 p_data => x_msg_data,
831 p_encoded => FND_API.G_FALSE
832 );
833
834 END Insert_New_Party_Dbms;
835
836
837 /*****************************************************************************/
838 -- Procedure
839 -- load_mkt_seg
840 -- Purpose
841 -- handle parties belonging to market segment, i.e., flag = 'Y'
842 --
843 -- Note
844 -- The process will take the sql query in to variable if it is Native SQL
845 -- Or it will take the sql query into table to use DBMS_SQL to execute it
846 -- History
847 -- 01/21/2000 julou created
848 -- 05/02/2000 ptendulk Modified , 1. Added Routines to execute sql as
849 -- Native SQL or DBMS sql
850 -------------------------------------------------------------------------------
851 PROCEDURE Load_Mkt_Seg
852 (
853 p_mkt_seg_id IN NUMBER
854 , p_query IN sql_rec_type
855 , p_type IN VARCHAR2
856 , x_return_status OUT NOCOPY VARCHAR2
857 , x_msg_count OUT NOCOPY NUMBER
858 , x_msg_data OUT NOCOPY VARCHAR2
859 )
860 IS
861 l_api_name CONSTANT VARCHAR2(30) := 'Load_Mkt_Seg';
862
863 l_sql_str VARCHAR2(32767) := '' ;
864 l_sql_tbl DBMS_SQL.varchar2s ;
865 l_tmp_str VARCHAR2(2000) := '' ;
866
867 l_count NUMBER ;
868 l_str_copy VARCHAR2(2000);
869
870 l_length NUMBER ;
871
872 -- Define the table type to store the party ids if the sql is DBMS_SQL
873 l_party_tab t_party_tab ;
874 l_temp NUMBER;
875 l_party_cur NUMBER ;
876 l_dummy NUMBER ;
877 BEGIN
878 AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
879 --
880 IF p_type = 'NATIVE' THEN
881 -- Get the query in to VAriable
882 FOR i IN p_query.FIRST..p_query.LAST
883 LOOP
884 l_tmp_str := p_query(i) ;
885 l_sql_str := l_sql_str || l_tmp_str ;
886 END LOOP;
887
888 -- expires parties that no longer belong to the given market segment
889 Expire_Inactive_Party
890 (
891 p_mkt_seg_id,
892 'Y',
893 l_sql_str,
894 x_return_status,
895 x_msg_count ,
896 x_msg_data
897 );
898 IF x_return_status = FND_API.g_ret_sts_error THEN
899 RAISE FND_API.g_exc_error;
900 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
901 RAISE FND_API.g_exc_unexpected_error;
902 END IF;
903
904
905 -- expires parties that originally belong to other marekt segments
906 -- and currently belong to the given market segment
907 Expire_Changed_Party
908 (
909 p_mkt_seg_id,
910 'Y',
911 l_sql_str,
912 x_return_status,
913 x_msg_count ,
914 x_msg_data
915 );
916 IF x_return_status = FND_API.g_ret_sts_error THEN
917 RAISE FND_API.g_exc_error;
918 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
919 RAISE FND_API.g_exc_unexpected_error;
920 END IF;
921
922 -- insert new parties that do not exist in the table
923 Insert_New_Party
924 (
925 p_mkt_seg_id,
926 'Y',
927 l_sql_str,
928 x_return_status,
929 x_msg_count ,
930 x_msg_data
931 );
932 IF x_return_status = FND_API.g_ret_sts_error THEN
933 RAISE FND_API.g_exc_error;
934 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
935 RAISE FND_API.g_exc_unexpected_error;
936 END IF;
937
938 ELSIF p_type = 'DBMS' THEN
939 --
940 -- Get the query in to Table
941 l_count := 0 ;
942 FOR j IN p_query.FIRST..p_query.LAST
943 LOOP
944 -- Copy Current String
945 l_str_copy := p_query(j) ;
946 LOOP
947 -- Get the length of the current string
948 l_length := length(l_str_copy) ;
949 l_count := l_count + 1 ;
950 IF l_length < 255 THEN
951 -- If length is < 255 char we can exit loop after copying
952 -- current contents into DBMS_SQL PL/SQL table
953 l_sql_tbl(l_count):= l_str_copy ;
954 EXIT;
955 ELSE
956 -- Copy 255 Characters and copy next 255 to the next row
957 l_sql_tbl(l_count):= substr(l_str_copy,1,255) ;
958 l_str_copy := substr(l_str_copy,256) ;
959 END IF;
960
961 END LOOP ;
962 END LOOP ;
963
964
965 l_count := 1 ;
966 -- Open the cursor and parse it
967 IF (DBMS_SQL.Is_Open(l_party_cur) = FALSE) THEN
968 l_party_cur := DBMS_SQL.Open_Cursor ;
969 END IF;
970 DBMS_SQL.Parse(l_party_cur ,
971 l_sql_tbl,
972 l_sql_tbl.first,
973 l_sql_tbl.last,
974 FALSE,
975 DBMS_SQL.Native) ;
976
977 DBMS_SQL.DEFINE_COLUMN(l_party_cur,1,l_temp);
978 l_dummy := DBMS_SQL.Execute(l_party_cur);
979 LOOP
980 IF DBMS_SQL.FETCH_ROWS(l_party_cur)>0 THEN
981 -- get column values of the row
982 DBMS_SQL.COLUMN_VALUE(l_party_cur,1, l_temp);
983 l_party_tab(l_count) := l_temp ;
984 l_count := l_count + 1 ;
985 ELSE
986 -- No more rows to copy:
987 EXIT;
988 END IF;
989 END LOOP;
990
991 DBMS_SQL.Close_Cursor(l_party_cur);
992
993 --
994
995 -- expires parties that no longer belong to the given market segment
996 Expire_Inactive_Party_Dbms
997 (
998 p_mkt_seg_id,
999 'Y',
1000 l_party_tab,
1001 x_return_status,
1002 x_msg_count ,
1003 x_msg_data
1004 );
1005 IF x_return_status = FND_API.g_ret_sts_error THEN
1006 RAISE FND_API.g_exc_error;
1007 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1008 RAISE FND_API.g_exc_unexpected_error;
1009 END IF;
1010
1011 -- expires parties that originally belong to other marekt segments
1012 -- and currently belong to the given market segment
1013 Expire_Changed_Party_Dbms
1014 (
1015 p_mkt_seg_id,
1016 'Y',
1017 l_party_tab,
1018 x_return_status,
1019 x_msg_count ,
1020 x_msg_data
1021 );
1022 IF x_return_status = FND_API.g_ret_sts_error THEN
1023 RAISE FND_API.g_exc_error;
1024 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1025 RAISE FND_API.g_exc_unexpected_error;
1026 END IF;
1027
1028 -- insert new parties that do not exist in the table
1029 Insert_New_Party_Dbms
1030 (
1031 p_mkt_seg_id,
1032 'Y',
1033 l_party_tab,
1034 x_return_status,
1035 x_msg_count ,
1036 x_msg_data
1037 );
1038 IF x_return_status = FND_API.g_ret_sts_error THEN
1039 RAISE FND_API.g_exc_error;
1040 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1041 RAISE FND_API.g_exc_unexpected_error;
1042 END IF;
1043
1044
1045 END IF;
1046
1047 EXCEPTION
1048 WHEN FND_API.g_exc_error THEN
1049 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
1050 DBMS_SQL.Close_Cursor(l_party_cur) ;
1051 END IF;
1052 x_return_status := FND_API.g_ret_sts_error;
1053 FND_MSG_PUB.count_and_get(
1054 p_encoded => FND_API.g_false,
1055 p_count => x_msg_count,
1056 p_data => x_msg_data
1057 );
1058 WHEN FND_API.g_exc_unexpected_error THEN
1059 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
1060 DBMS_SQL.Close_Cursor(l_party_cur) ;
1061 END IF;
1062 x_return_status := FND_API.g_ret_sts_unexp_error ;
1063 FND_MSG_PUB.count_and_get (
1064 p_encoded => FND_API.g_false,
1065 p_count => x_msg_count,
1066 p_data => x_msg_data
1067 );
1068 WHEN OTHERS THEN
1069 --
1070 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
1071 DBMS_SQL.Close_Cursor(l_party_cur) ;
1072 END IF;
1073 x_return_status := FND_API.g_ret_sts_unexp_error ;
1074
1075 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1076 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1077 END IF;
1078
1079 FND_MSG_PUB.count_and_get (
1080 p_encoded => FND_API.g_false,
1081 p_count => x_msg_count,
1082 p_data => x_msg_data
1083 );
1084
1085 END Load_Mkt_Seg;
1086
1087
1088 /*****************************************************************************/
1089 -- Procedure
1090 -- Load_Tgt_Seg
1091 -- Purpose
1092 -- handle parties belonging to target segment, i.e., flag = 'N'
1093 -- History
1094 -- 01/21/2000 julou created
1095 -- 05/02/2000 ptendulk Modified , 1. Added Routines to execute sql as
1096 -- Native SQL or DBMS sql
1097 -------------------------------------------------------------------------------
1098 PROCEDURE Load_Tgt_Seg
1099 (
1100 p_mkt_seg_id IN NUMBER
1101 , p_query IN sql_rec_type
1102 , p_type IN VARCHAR2
1103 , x_return_status OUT NOCOPY VARCHAR2
1104 , x_msg_count OUT NOCOPY NUMBER
1105 , x_msg_data OUT NOCOPY VARCHAR2
1106 )
1107 IS
1108 l_api_name CONSTANT VARCHAR2(30) := 'load_tgt_seg';
1109
1110 l_sql_str VARCHAR2(32767) := '' ;
1111 l_sql_tbl DBMS_SQL.varchar2s ;
1112 l_tmp_str VARCHAR2(2000) := '' ;
1113
1114 l_count NUMBER ;
1115 l_str_copy VARCHAR2(2000);
1116 -- Define the table type to store the party ids if the sql is DBMS_SQL
1117 l_party_tab t_party_tab ;
1118 l_length NUMBER ;
1119 l_party_cur NUMBER ;
1120 l_dummy NUMBER ;
1121 BEGIN
1122
1123 AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
1124 --
1125 IF p_type = 'NATIVE' THEN
1126 -- Get the query in to VAriable
1127 FOR i IN p_query.first..p_query.last
1128 LOOP
1129 l_tmp_str := p_query(i) ;
1130 l_sql_str := l_sql_str || l_tmp_str ;
1131 END LOOP;
1132
1133 -- expires parties that no longer belong to the given target segment
1134 Expire_Inactive_Party
1135 (
1136 p_mkt_seg_id,
1137 'N',
1138 l_sql_str,
1139 x_return_status,
1140 x_msg_count ,
1141 x_msg_data
1142 );
1143 IF x_return_status = FND_API.g_ret_sts_error THEN
1144 RAISE FND_API.g_exc_error;
1145 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1146 RAISE FND_API.g_exc_unexpected_error;
1147 END IF;
1148
1149 -- insert new parties that do not exist in the table
1150 Insert_New_Party
1151 (
1152 p_mkt_seg_id,
1153 'N',
1154 l_sql_str,
1155 x_return_status,
1156 x_msg_count ,
1157 x_msg_data
1158 );
1159 IF x_return_status = FND_API.g_ret_sts_error THEN
1160 RAISE FND_API.g_exc_error;
1161 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1162 RAISE FND_API.g_exc_unexpected_error;
1163 END IF;
1164
1165 ELSIF p_type = 'DBMS' THEN
1166 -- Get the query in to Table
1167 l_count := 0 ;
1168 FOR j IN p_query.first..p_query.last
1169 LOOP
1170 -- Copy Current String
1171 l_str_copy := p_query(j) ;
1172 LOOP
1173 -- Get the length of the current string
1174 l_length := length(l_str_copy) ;
1175 l_count := l_count + 1 ;
1176 IF l_length < 255 THEN
1177 -- If length is < 255 char we can exit loop after copying
1178 -- current contents into DBMS_SQL PL/SQL table
1179 l_sql_tbl(l_count):= l_str_copy ;
1180 EXIT;
1181 ELSE
1182 -- Copy 255 Characters and copy next 255 to the next row
1183 l_sql_tbl(l_count):= substr(l_str_copy,1,255) ;
1184 l_str_copy := substr(l_str_copy,256) ;
1185 END IF;
1186
1187 END LOOP ;
1188 END LOOP ;
1189
1190 l_count := 0 ;
1191 -- Open the cursor and parse it
1192 IF (DBMS_SQL.Is_Open(l_party_cur) = FALSE) THEN
1193 l_party_cur := DBMS_SQL.Open_Cursor ;
1194 END IF;
1195 DBMS_SQL.Parse(l_party_cur ,
1196 l_sql_tbl,
1197 l_sql_tbl.first,
1198 l_sql_tbl.last,
1199 FALSE,
1200 DBMS_SQL.Native) ;
1201
1202 l_dummy := DBMS_SQL.Execute(l_party_cur);
1203
1204 LOOP
1205 IF DBMS_SQL.FETCH_ROWS(l_party_cur)>0 THEN
1206 -- get column values of the row
1207 DBMS_SQL.COLUMN_VALUE(l_party_cur, 1, l_party_tab(l_count));
1208 l_count := l_count + 1 ;
1209 ELSE
1210 -- No more rows to copy:
1211 EXIT;
1212 END IF;
1213 END LOOP;
1214
1215 DBMS_SQL.Close_Cursor(l_party_cur);
1216
1217 -- expires parties that no longer belong to the given market segment
1218 Expire_Inactive_Party_Dbms
1219 (
1220 p_mkt_seg_id,
1221 'N',
1222 l_party_tab,
1223 x_return_status,
1224 x_msg_count ,
1225 x_msg_data
1226 );
1227 IF x_return_status = FND_API.g_ret_sts_error THEN
1228 RAISE FND_API.g_exc_error;
1229 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1230 RAISE FND_API.g_exc_unexpected_error;
1231 END IF;
1232
1233 -- insert new parties that do not exist in the table
1234 Insert_New_Party_Dbms
1235 (
1236 p_mkt_seg_id,
1237 'N',
1238 l_party_tab,
1239 x_return_status,
1240 x_msg_count ,
1241 x_msg_data
1242 );
1243 IF x_return_status = FND_API.g_ret_sts_error THEN
1244 RAISE FND_API.g_exc_error;
1245 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1246 RAISE FND_API.g_exc_unexpected_error;
1247 END IF;
1248
1249
1250 END IF;
1251
1252 EXCEPTION
1253 WHEN FND_API.g_exc_error THEN
1254 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
1255 DBMS_SQL.Close_Cursor(l_party_cur) ;
1256 END IF;
1257 x_return_status := FND_API.g_ret_sts_error;
1258 FND_MSG_PUB.count_and_get(
1259 p_encoded => FND_API.g_false,
1260 p_count => x_msg_count,
1261 p_data => x_msg_data
1262 );
1263 WHEN FND_API.g_exc_unexpected_error THEN
1264 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
1265 DBMS_SQL.Close_Cursor(l_party_cur) ;
1266 END IF;
1267 x_return_status := FND_API.g_ret_sts_unexp_error ;
1268 FND_MSG_PUB.count_and_get (
1269 p_encoded => FND_API.g_false,
1270 p_count => x_msg_count,
1271 p_data => x_msg_data
1272 );
1273 WHEN OTHERS THEN
1274 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
1275 DBMS_SQL.Close_Cursor(l_party_cur) ;
1276 END IF;
1277 x_return_status := FND_API.g_ret_sts_unexp_error ;
1278
1279 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1280 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1281 END IF;
1282
1283 FND_MSG_PUB.count_and_get (
1284 p_encoded => FND_API.g_false,
1285 p_count => x_msg_count,
1286 p_data => x_msg_data
1287 );
1288
1289
1290
1291
1292 END Load_Tgt_Seg;
1293
1294
1295
1296
1297
1298 /*****************************************************************************/
1299 -- Procedure
1300 -- load_party_mkt_seg
1301 -- Purpose
1302 -- load ams_party_market_segments
1303 -- History
1304 -- 01/16/2000 julou created
1305 -- 05/05/2000 ptendulk Modified 1.Added out parameter to capture error
1306 -- 2. Added input parameter to get the Cell id
1307 -- If the cell id is sent then Only that cell and
1308 -- all the Target child cells of that cell will be
1309 -- refreshed or else all the cells will be refreshed.
1310 --
1311 -- Note :
1312 -- If the cell is is passed to the program then if the error occurs it will
1313 -- rollback everything
1314 -- If the cell id is not passed it will do commmit per cell.
1315 -------------------------------------------------------------------------------
1316 PROCEDURE Load_Party_Mkt_Seg
1317 ( p_cell_id IN NUMBER DEFAULT NULL,
1318 x_return_status OUT NOCOPY VARCHAR2,
1319 x_msg_count OUT NOCOPY NUMBER,
1320 x_msg_data OUT NOCOPY VARCHAR2
1321 )
1322 IS
1323 l_api_name CONSTANT VARCHAR2(30) := 'Load_Party_Mkt_Seg';
1324 TYPE dyna_cur_type IS REF CURSOR; -- cursor for dynamic SQL
1325 l_type VARCHAR2(10);
1326 l_query sql_rec_type ;
1327 l_cell_id NUMBER;
1328 l_mkt_seg_flag VARCHAR2(1);
1329 l_sql_str VARCHAR2(2000);
1330 l_wb_owner VARCHAR2(15) ;
1331 l_wb_name VARCHAR2(254);
1332 l_ws_name VARCHAR2(254);
1333 l_cell_name VARCHAR2(120);
1334
1335 CURSOR c_cell_rec(l_cell_id NUMBER) IS
1336 SELECT cell_id, market_segment_flag,cell_name
1337 FROM ams_cells_vl
1338 WHERE cell_id = l_cell_id
1339 OR (parent_cell_id = l_cell_id
1340 AND market_segment_flag = 'N' );
1341
1342 CURSOR c_all_cell_rec IS
1343 SELECT cell_id, market_segment_flag ,cell_name
1344 FROM ams_cells_vl ;
1345
1346 CURSOR c_sql_id(cell_id IN NUMBER) IS
1347 SELECT workbook_name,workbook_owner,worksheet_name
1348 FROM ams_act_discoverer_all
1349 WHERE act_discoverer_used_by_id = cell_id
1350 AND arc_act_discoverer_used_by = 'CELL';
1351
1352 -- CURSOR c_sql_str(WB_NAME IN VARCHAR2,WB_OWNER IN VARCHAR2) IS
1353 -- SELECT sql_string FROM AMS_DISCOVERER_SQL
1354 -- WHERE workbook_name = wb_name
1355 -- AND workbook_owner_name = wb_owner ;
1356
1357 BEGIN
1358 AMS_Utility_Pvt.Debug_Message('Start Loading ');
1359 --
1360 x_return_status := FND_API.G_RET_STS_SUCCESS;
1361
1362 -- Check the cells If the p_cell_id is null then Refresh all the cells
1363 -- Else refresh only the given cell and the Target sells which are
1364 -- children of the current cells
1365 IF p_cell_id IS NOT NULL
1366 THEN
1367 -- Create the Savepoint
1368 SAVEPOINT Load_Party_Mkt_Seg;
1369
1370 -- Refresh only particular Cell
1371 OPEN c_cell_rec(p_cell_id);
1372 LOOP -- the loop for all CELL_IDs
1373
1374 FETCH c_cell_rec INTO l_cell_id, l_mkt_seg_flag,l_cell_name;
1375 EXIT WHEN c_cell_rec%NOTFOUND;
1376 -- initalize
1377 l_sql_str := NULL;
1378
1379 -- Get the Workbook sqls attached to that cell
1380 OPEN c_sql_id(l_cell_id);
1381 FETCH c_sql_id INTO l_wb_name,l_wb_owner,l_ws_name;
1382 CLOSE c_sql_id;
1383
1384 AMS_Utility_Pvt.Debug_Message('WB : '||l_wb_name||'Owner : '||l_wb_owner);
1385 --
1386
1387 Validate_Sql
1388 (p_workbook_name => l_wb_name ,
1389 p_workbook_owner => l_wb_owner,
1390 p_worksheet_name => l_ws_name,
1391 p_cell_name => l_cell_name ,
1392 x_query => l_query,
1393 x_sql_type => l_type,
1394
1395 x_return_status => x_return_status,
1396 x_msg_count => x_msg_count ,
1397 x_msg_data => x_msg_data ) ;
1398
1399 IF x_return_status = FND_API.g_ret_sts_error THEN
1400 RAISE FND_API.g_exc_error;
1401 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1402 RAISE FND_API.g_exc_unexpected_error;
1403 END IF;
1404
1405
1406 IF l_mkt_seg_flag = 'Y' THEN
1407
1408 AMS_Utility_Pvt.Debug_Message('Load MKt ');
1409 --
1410
1411 Load_Mkt_Seg(l_cell_id,
1412 l_query,
1413 l_type,
1414 x_return_status ,
1415 x_msg_count ,
1416 x_msg_data);
1417
1418 ELSE
1419 AMS_Utility_Pvt.Debug_Message('Load Target ');
1420 --
1421 Load_Tgt_Seg(l_cell_id,
1422 l_query,
1423 l_type,
1424 x_return_status ,
1425 x_msg_count ,
1426 x_msg_data);
1427
1428 END IF;
1429
1430 END LOOP; -- end: the loop for all CELL_IDs
1431 CLOSE c_cell_rec;
1432
1433 IF x_return_status = FND_API.g_ret_sts_error THEN
1434 RAISE FND_API.g_exc_error;
1435 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1436 RAISE FND_API.g_exc_unexpected_error;
1437 END IF;
1438
1439 -- If No Errors , Commit the work
1440 COMMIT WORK;
1441
1442 ELSE
1443 -- Get All The cells
1444 OPEN c_all_cell_rec;
1445 LOOP -- the loop for all CELL_IDs
1446 FETCH c_all_cell_rec INTO l_cell_id, l_mkt_seg_flag,l_cell_name;
1447 EXIT WHEN c_all_cell_rec%NOTFOUND;
1448 -- Create the Savepoint
1449 SAVEPOINT Load_Party_Mkt_Seg;
1450
1451 -- initalize
1452 l_sql_str := NULL;
1453
1454 -- Get the Workbook sqls attached to that cell
1455 OPEN c_sql_id(l_cell_id);
1456 FETCH c_sql_id INTO l_wb_name,l_wb_owner,l_ws_name;
1457 CLOSE c_sql_id;
1458
1459 AMS_Utility_Pvt.Debug_Message('WB : '||l_wb_name||'Owner : '||l_wb_owner);
1460 --
1461
1462 Validate_Sql
1463 (p_workbook_name => l_wb_name ,
1464 p_workbook_owner => l_wb_owner,
1465 p_worksheet_name => l_ws_name,
1466 p_cell_name => l_cell_name,
1467 x_query => l_query,
1468 x_sql_type => l_type,
1469
1470 x_return_status => x_return_status,
1471 x_msg_count => x_msg_count ,
1472 x_msg_data => x_msg_data ) ;
1473
1474
1475 IF x_return_status = FND_API.g_ret_sts_success THEN
1476 -- Load the Segments
1477
1478 IF l_mkt_seg_flag = 'Y' THEN
1479
1480 AMS_Utility_Pvt.Debug_Message('Load MKt ');
1481 --
1482
1483 Load_Mkt_Seg(l_cell_id,
1484 l_query,
1485 l_type,
1486 x_return_status ,
1487 x_msg_count ,
1488 x_msg_data);
1489
1490 ELSE
1491 AMS_Utility_Pvt.Debug_Message('Load Target ');
1492 --
1493 Load_Tgt_Seg(l_cell_id,
1494 l_query,
1495 l_type,
1496 x_return_status ,
1497 x_msg_count ,
1498 x_msg_data);
1499
1500 END IF;
1501
1502 IF x_return_status = FND_API.g_ret_sts_success THEN
1503 COMMIT WORK ;
1504 END IF;
1505 END IF;
1506 END LOOP; -- end: the loop for all CELL_IDs
1507 CLOSE c_cell_rec;
1508 END IF;
1509
1510
1511
1512 EXCEPTION
1513 WHEN FND_API.g_exc_error THEN
1514 IF(c_cell_rec%ISOPEN)then
1515 CLOSE c_cell_rec;
1516 END IF;
1517 IF(c_sql_id%ISOPEN)then
1518 CLOSE c_sql_id;
1519 END IF;
1520 IF(c_all_cell_rec%ISOPEN)then
1521 CLOSE c_all_cell_rec;
1522 END IF;
1523 ROLLBACK TO Load_Party_Mkt_Seg;
1524 x_return_status := FND_API.g_ret_sts_error;
1525 FND_MSG_PUB.count_and_get (
1526 p_encoded => FND_API.g_false,
1527 p_count => x_msg_count,
1528 p_data => x_msg_data
1529 );
1530 WHEN FND_API.g_exc_unexpected_error THEN
1531 IF(c_cell_rec%ISOPEN)then
1532 CLOSE c_cell_rec;
1533 END IF;
1534 IF(c_sql_id%ISOPEN)then
1535 CLOSE c_sql_id;
1536 END IF;
1537 IF(c_all_cell_rec%ISOPEN)then
1538 CLOSE c_all_cell_rec;
1539 END IF;
1540 ROLLBACK TO Load_Party_Mkt_Seg;
1541 x_return_status := FND_API.g_ret_sts_unexp_error ;
1542 FND_MSG_PUB.count_and_get (
1543 p_encoded => FND_API.g_false,
1544 p_count => x_msg_count,
1545 p_data => x_msg_data
1546 );
1547 WHEN OTHERS THEN
1548 IF(c_cell_rec%ISOPEN)then
1549 CLOSE c_cell_rec;
1550 END IF;
1551 IF(c_sql_id%ISOPEN)then
1552 CLOSE c_sql_id;
1553 END IF;
1554 IF(c_all_cell_rec%ISOPEN)then
1555 CLOSE c_all_cell_rec;
1556 END IF;
1557 ROLLBACK TO Load_Party_Mkt_Seg;
1558 x_return_status := FND_API.g_ret_sts_unexp_error ;
1559
1560 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1561 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1562 END IF;
1563 FND_MSG_PUB.count_and_get (
1564 p_encoded => FND_API.g_false,
1565 p_count => x_msg_count,
1566 p_data => x_msg_data
1567 );
1568
1569 END load_party_mkt_seg;
1570
1571
1572 -- Start of Comments
1573 --
1574 -- NAME
1575 -- Refresh_Party_Market_Segment
1576 --
1577 -- PURPOSE
1578 -- This procedure is created to as a concurrent program which
1579 -- will call the load_party_mkt_seg and will return errors if any
1580 --
1581 -- NOTES
1582 --
1583 --
1584 -- HISTORY
1585 -- 05/02/1999 ptendulk created
1586 -- End of Comments
1587
1588 PROCEDURE Refresh_Party_Market_Segment
1589 (errbuf OUT NOCOPY VARCHAR2,
1590 retcode OUT NOCOPY NUMBER,
1591 p_cell_id IN NUMBER DEFAULT NULL)
1592 IS
1593 l_return_status VARCHAR2(1) ;
1594 l_msg_count NUMBER ;
1595 l_msg_data VARCHAR2(2000);
1596 BEGIN
1597 FND_MSG_PUB.initialize;
1598 -- Call the procedure to refresh the Market Segment
1599 -- Call procedure in new package to refresh the segments
1600
1601 AMS_Party_Seg_Loader_PVT.Load_Party_Seg
1602 ( p_cell_id => p_cell_id,
1603 x_return_status => l_return_status,
1604 x_msg_count => l_msg_count,
1605 x_msg_data => l_msg_data);
1606
1607 --Load_Party_Mkt_Seg
1608 --( p_cell_id => p_cell_id ,
1609 -- x_return_status => l_return_status ,
1610 -- x_msg_count => l_msg_count ,
1611 -- x_msg_data => l_msg_data ) ;
1612
1613 -- Write_log ;
1614 Ams_Utility_Pvt.Write_Conc_log ;
1615 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
1616 retcode :=0;
1617 ELSE
1618 retcode :=1;
1619 errbuf := l_msg_data ;
1620 END IF;
1621 END Refresh_Party_Market_Segment ;
1622
1623
1624
1625
1626
1627 -- Start of Comments
1628 --
1629 -- NAME
1630 -- Format_Party_Id
1631 --
1632 -- PURPOSE
1633 -- This procedure is created to contruct the party id with the alias
1634 -- present in the Discoverer workbook
1635 -- It will get the SQL String input which will be from SELECT to FROM in
1636 -- the sql query and will return the party Id with the alias.
1637 --
1638 -- NOTES
1639 -- This parsing is based on the assumption that discoverer always
1640 -- creates the Alias
1641 --
1642 -- HISTORY
1643 -- 05/02/1999 ptendulk created
1644 -- End of Comments
1645
1646 PROCEDURE Format_Party_Id
1647 (P_sql_str IN VARCHAR2,
1648 x_party_str OUT NOCOPY VARCHAR2)
1649 IS
1650 l_sql_str VARCHAR2(32767);
1651 l_party_str VARCHAR2(2000);
1652 -- l_tmp NUMBER;
1653 BEGIN
1654
1655 l_party_str := SUBSTR(P_sql_str,INSTR(p_sql_str,' ',-1,1) + 1) ;
1656
1657 l_party_str := 'SELECT '||SUBSTR(l_party_str,INSTR(l_party_str,',',-1,1) +1 )||' FROM ' ;
1658
1659
1660 x_party_str := l_party_str ;
1661
1662
1663 END Format_Party_Id ;
1664
1665 -- Start of Comments
1666 --
1667 -- NAME
1668 -- Validate_Sql
1669 --
1670 -- PURPOSE
1671 -- This procedure is created to validate the discoverer sql created for
1672 -- the Cells . It will follow the following steps :
1673 -- 1. Check If the sql length is less than 32k , If it's less than 32k
1674 -- process and execute it as native sql or use dbms sql
1675 -- 2. Check for the party id between SELECT and FROM of the SQL string
1676 -- 3. Substitue the party id for every thing between select and from
1677 -- 4. Execute the query
1678 --
1679 -- It will return the Parameters as
1680 -- 1. x_query : This table will have the discoverer sql query
1681 -- 2. x_sql_type : It will return 'NATIVE' if the sql is Native SQL
1682 -- or it will return 'DBMS'
1683 -- NOTES
1684 --
1685 --
1686 -- HISTORY
1687 -- 05/02/1999 ptendulk created
1688 -- End of Comments
1689
1690 PROCEDURE Validate_Sql
1691 (p_workbook_name IN VARCHAR2,
1692 p_workbook_owner IN VARCHAR2,
1693 p_worksheet_name IN VARCHAR2,
1694 p_cell_name IN VARCHAR2,
1695 x_query OUT NOCOPY sql_rec_type,
1696 x_sql_type OUT NOCOPY VARCHAR2,
1697
1698 x_return_status OUT NOCOPY VARCHAR2,
1699 x_msg_count OUT NOCOPY NUMBER,
1700 x_msg_data OUT NOCOPY VARCHAR2)
1701 IS
1702 l_return_status VARCHAR2(1) ;
1703 l_msg_count NUMBER ;
1704 l_msg_data VARCHAR2(2000);
1705 l_workbook_sql sql_rec_type ;
1706 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Sql';
1707 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1708
1709 -- Define the cursor to find the first worksheet
1710 CURSOR c_ws_name IS
1711 SELECT 1
1712 FROM ams_discoverer_sql
1713 WHERE workbook_name = p_workbook_name
1714 AND workbook_owner_name = p_workbook_owner
1715 AND worksheet_name = p_worksheet_name ;
1716
1717 -- Define the cursor to get the sql between SELECT and first Party ID
1718 CURSOR c_till_from_sql(l_sequence_order NUMBER ) IS
1719 SELECT sql_string
1720 FROM ams_discoverer_sql
1721 WHERE workbook_name = p_workbook_name
1722 AND worksheet_name = p_worksheet_name
1723 AND workbook_owner_name = p_workbook_owner
1724 AND sequence_order <= l_sequence_order
1725 ORDER BY Sequence_Order;
1726
1727 -- Define the cursor to get the sql string after from
1728 CURSOR c_sql_str(l_sequence_order NUMBER ) IS
1729 SELECT sql_string
1730 FROM ams_discoverer_sql
1731 WHERE workbook_name = p_workbook_name
1732 AND worksheet_name = p_worksheet_name
1733 AND workbook_owner_name = p_workbook_owner
1734 AND sequence_order >= l_sequence_order
1735 ORDER BY sequence_order;
1736
1737
1738 l_count NUMBER := 0 ;
1739 l_size NUMBER := 0 ;
1740 l_tmp_size NUMBER := 0 ;
1741 -- Size constraint to Use Native dynamic sql
1742 l_dbms_size NUMBER := 32767 ;
1743 -- The PL/SQL table which stores 255 character length strings to be passed
1744 -- to DBMS_SQL package
1745 -- l_sql_str DBMS_SQL.varchar2s ;
1746
1747 l_found VARCHAR2(1) := FND_API.G_FALSE;
1748 l_found_in_str NUMBER := 0 ;
1749 l_position NUMBER := 0 ;
1750 l_overflow NUMBER := 0 ;
1751
1752 l_max_search_len NUMBER := 0 ;
1753 l_from_found_in_str NUMBER := 0 ;
1754 l_from_position NUMBER := 0 ;
1755 l_from_overflow NUMBER := 0 ;
1756
1757 l_str VARCHAR2(32767) := '' ;
1758 l_tmp_str VARCHAR2(2000) := '' ;
1759 l_counter NUMBER ;
1760 l_dummy NUMBER ;
1761 BEGIN
1762
1763 AMS_Utility_PVT.debug_message(l_full_name||': start');
1764 --
1765
1766 -- Initialize API return status to success
1767 l_return_status := FND_API.G_RET_STS_SUCCESS;
1768
1769 --
1770 -- API body
1771 -- Take the sql query into PLSQL table
1772 -- Check the Size of the query depending on the Size Execute
1773 -- the query as Native SQL or DBMS_SQL
1774 l_count := 0 ;
1775 --
1776 OPEN c_ws_name ;
1777 FETCH c_ws_name INTO l_dummy ;
1778 IF c_ws_name%NOTFOUND THEN
1779 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1780 -- Invalid SQL for the Discoverer
1781 FND_MESSAGE.set_name('AMS', 'AMS_MKS_NO_WB');
1782 FND_MESSAGE.Set_Token('WORKBOOK', p_workbook_name);
1783 FND_MESSAGE.Set_Token('CELL', p_cell_name);
1784 FND_MSG_PUB.Add;
1785 CLOSE c_ws_name ;
1786 RAISE FND_API.G_EXC_ERROR;
1787 END IF;
1788 END IF ;
1789 CLOSE c_ws_name ;
1790
1791
1792
1793 -- Search for the from in the discoverer SQL
1794 AMS_DiscovererSQL_PVT.search_sql_string(p_search_string => 'FROM',
1795 p_workbook_name => p_workbook_name,
1796 p_worksheet_name => p_worksheet_name,
1797 x_found => l_found,
1798 x_found_in_str => l_found_in_str,
1799 x_position => l_position,
1800 x_overflow => l_overflow);
1801
1802
1803 IF (l_found = FND_API.G_FALSE) THEN
1804 AMS_Utility_PVT.debug_message(l_full_name||': Invalid SQL');
1805 --
1806 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1807 -- Invalid SQL for the Discoverer
1808 FND_MESSAGE.set_name('AMS', 'AMS_MKS_BAD_WB');
1809 FND_MESSAGE.Set_Token('CELL', p_cell_name);
1810 FND_MSG_PUB.Add;
1811 CLOSE c_ws_name ;
1812 RAISE FND_API.G_EXC_ERROR;
1813 END IF;
1814 ELSIF(l_found = FND_API.G_TRUE)THEN
1815
1816 --calculating the max. number of chars to be searched when searching for master and subtypes.
1817 l_max_search_len := (l_found_in_str ) * 2000 + l_position;
1818
1819 --recording the sql string where the first character of FROM was found.
1820 l_from_found_in_str := l_found_in_str;
1821
1822 --recording the position where the first character of FROM was found.
1823 l_from_position := l_position;
1824
1825 --recording the overflow amount into the next string if this occured.
1826 l_from_overflow := nvl(l_overflow,0);
1827
1828 --
1829 -- Now Find out the position of the first party_id from the SELECT to FROM
1830 -- ===================Assumption ====================
1831 -- The Discoverer generates the alias for the column name
1832 -- ==================================================
1833 AMS_DiscovererSQL_PVT.search_sql_string(p_search_string => '.PARTY_ID',
1834 p_workbook_name => p_workbook_name,
1835 p_worksheet_name => p_worksheet_name,
1836 p_max_search_len => l_max_search_len,
1837 x_found => l_found,
1838 x_found_in_str => l_found_in_str,
1839 x_position => l_position,
1840 x_overflow => l_overflow);
1841
1842 IF (l_found = FND_API.G_FALSE) THEN
1843 AMS_Utility_PVT.debug_message(l_full_name||': No party ID in the SQL');
1844 --
1845 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1846 -- Invalid SQL for the Discoverer
1847 FND_MESSAGE.set_name('AMS', 'AMS_MKS_BAD_WB');
1848 FND_MESSAGE.Set_Token('CELL', p_cell_name);
1849 FND_MSG_PUB.Add;
1850 CLOSE c_ws_name ;
1851 RAISE FND_API.G_EXC_ERROR;
1852 END IF;
1853 ELSIF(l_found = FND_API.G_TRUE)THEN
1854 AMS_Utility_PVT.debug_message(l_full_name||': Party ID in the SQL');
1855 --
1856
1857 -- Get the sql string form "SELECT" to "FROM" into the string and
1858 OPEN c_till_from_sql(l_found_in_str) ;
1859 LOOP
1860 FETCH c_till_from_sql INTO l_tmp_str ;
1861 EXIT WHEN c_till_from_sql%NOTFOUND ;
1862 l_str := l_str ||l_tmp_str ;
1863 END LOOP ;
1864 CLOSE c_till_from_sql ;
1865 -- We Have the String like 'select ........ AMS.Party_id' or 'select ........ AMS.Pa'
1866 -- Now Get the string before party Id and concatenate it with party Id
1867 -- to format the String (It will format e.g2 to e.g1)
1868 l_str := SUBSTR(l_str,1,((l_found_in_str ) * 2000 + l_position) ) ;
1869 l_str := SUBSTR(l_str,1,INSTR(l_str,'.',-1,1)-1)||'.PARTY_ID' ;
1870
1871 -- We Have String like 'select ........ AMS.Party_id'
1872
1873 --Pass it to get the formated select clause till form
1874 Format_Party_Id(l_str,l_str) ;
1875
1876
1877 AMS_UTILITY_PVT.Debug_Message('Sql String : '||l_str);
1878 --
1879 -- So We Have String like 'Select AMS.Party_id FROM'
1880 -- Store it in PLSQL table as first row and then Store the other
1881 -- Rows of the sql as neft rows
1882 l_workbook_sql(1) := l_str ;
1883
1884 l_counter := 3 ;
1885 l_tmp_str := '' ;
1886
1887
1888
1889 OPEN c_sql_str(l_from_found_in_str) ;
1890 FETCH c_sql_str INTO l_tmp_str ;
1891
1892 IF l_from_overflow = 0 THEN
1893 l_workbook_sql(2) := SUBSTR(l_tmp_str,l_from_position+5) ;
1894 ELSE
1895 FETCH c_sql_str INTO l_tmp_str ;
1896 l_workbook_sql(2) := SUBSTR(l_tmp_str,l_from_overflow+1);
1897 END IF;
1898
1899
1900
1901 LOOP
1902 FETCH c_sql_str INTO l_tmp_str ;
1903 EXIT WHEN c_sql_str%NOTFOUND ;
1904 l_workbook_sql(l_counter) := l_tmp_str ;
1905 l_counter := l_counter + 1 ;
1906 END LOOP;
1907 CLOSE c_sql_str ;
1908
1909 END IF;
1910 END IF ;
1911
1912 -- We Have the query in PLSQL table now Find out the length of the query
1913 -- And decide if it is Native sql or DBMS SQL
1914 FOR i in l_workbook_sql.first..l_workbook_sql.last
1915 LOOP
1916 l_tmp_size := lengthb(l_workbook_sql(i)) ;
1917 l_size := l_size + l_tmp_size ;
1918 END LOOP ;
1919
1920 IF l_size < l_dbms_size THEN
1921 AMS_Utility_PVT.debug_message(l_full_name||': DBMS_SQL');
1922
1923 x_sql_type := 'DBMS' ;
1924 ELSE
1925 AMS_Utility_PVT.debug_message(l_full_name||': NATIVE_SQL');
1926
1927 x_sql_type := 'NATIVE' ;
1928 END IF;
1929 x_query := l_workbook_sql ;
1930
1931 AMS_Utility_PVT.debug_message(l_full_name||': End');
1932
1933 EXCEPTION
1934 WHEN FND_API.G_EXC_ERROR THEN
1935
1936 x_return_status := FND_API.G_RET_STS_ERROR ;
1937
1938 FND_MSG_PUB.Count_AND_Get
1939 ( p_count => x_msg_count,
1940 p_data => x_msg_data,
1941 p_encoded => FND_API.G_FALSE
1942 );
1943
1944
1945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1946
1947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1948
1949 FND_MSG_PUB.Count_AND_Get
1950 ( p_count => x_msg_count,
1951 p_data => x_msg_data,
1952 p_encoded => FND_API.G_FALSE
1953 );
1954
1955
1956 WHEN OTHERS THEN
1957
1958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1959
1960 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1961 THEN
1962 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1963 END IF;
1964
1965 FND_MSG_PUB.Count_AND_Get
1966 ( p_count => x_msg_count,
1967 p_data => x_msg_data,
1968 p_encoded => FND_API.G_FALSE
1969 );
1970
1971 END Validate_Sql ;
1972
1973 /*****************************************************************************/
1974 -- Procedure
1975 -- Refresh_Segment_Size
1976 --
1977 -- Purpose
1978 -- This procedure is created to as a concurrent program which
1979 -- will call the update_segment_size and will return errors if any
1980 --
1981 -- Notes
1982 --
1983 --
1984 -- History
1985 -- 04/09/2001 yxliu created
1986 -- 06/20/2001 yxliu moved from package AMS_Cell_PVT
1987 ------------------------------------------------------------------------------
1988
1989 PROCEDURE Refresh_Segment_Size
1990 ( errbuf OUT NOCOPY VARCHAR2,
1991 retcode OUT NOCOPY NUMBER,
1992 p_cell_id IN NUMBER DEFAULT NULL
1993 )
1994 IS
1995 l_return_status VARCHAR2(1) ;
1996 l_msg_count NUMBER ;
1997 l_msg_data VARCHAR2(2000);
1998 BEGIN
1999 FND_MSG_PUB.initialize;
2000 -- Call the procedure to refresh Segment size
2001 AMS_Cell_PVT.Update_Segment_Size
2002 ( p_cell_id => p_cell_id,
2003 x_return_status => l_return_status,
2004 x_msg_count => l_msg_count,
2005 x_msg_data => l_msg_data);
2006
2007 -- Write_log ;
2008 Ams_Utility_Pvt.Write_Conc_log ;
2009 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
2010 retcode :=0;
2011 ELSE
2012 retcode :=1;
2013 errbuf := l_msg_data ;
2014 END IF;
2015 END Refresh_Segment_Size ;
2016
2017 PROCEDURE write_conc_log
2018 (
2019 p_text IN VARCHAR2
2020 ) IS
2021 BEGIN
2022 IF G_DEBUG_LEVEL THEN
2023 Ams_Utility_pvt.Write_Conc_log (p_text);
2024 END IF;
2025 END write_conc_log;
2026
2027 /*
2028 PROCEDURE write_conc_log(p_text VARCHAR2)
2029 IS
2030 i number := 1;
2031 j number;
2032 BEGIN
2033 ams_utility_pvt.write_conc_log(p_text);
2034
2035 j := length(p_text);
2036 while (i <= j) loop
2037 dbms_output.put_line(substr(p_text, i, 200));
2038 i := i + 200;
2039 end loop;
2040
2041 END;
2042 */
2043
2044
2045 /*****************************************************************************
2046 * NAME
2047 * compose_qualifier_values
2048 *
2049 * PURPOSE
2050 * This procedure is a private procedure used by get_territory_qualifiers
2051 * to compose qualifier expression
2052 *
2053 * NOTES
2054 *
2055 * HISTORY
2056 * 10/14/2001 yzhao created
2057 * 05/07/2003 yzhao SQL bind variable project
2058 *****************************************************************************/
2059
2060 PROCEDURE compose_qualifier_values
2061 (
2062 p_value_rec IN JTF_TERRITORY_GET_PUB.Terr_Values_Rec_Type,
2063 p_bindvar_index IN NUMBER,
2064 p_bind_vars IN BIND_TBL_TYPE,
2065 x_cond_str OUT NOCOPY VARCHAR2,
2066 x_bind_vars OUT NOCOPY BIND_TBL_TYPE
2067 ) IS
2068 l_temp_index NUMBER;
2069 l_index NUMBER;
2070 l_value_str VARCHAR2(2000);
2071 l_bind_vars BIND_TBL_TYPE;
2072 BEGIN
2073 l_bind_vars := p_bind_vars;
2074 l_index := p_bindvar_index + p_bind_vars.COUNT;
2075 write_conc_log('D: compose_qualifier_values: bindvar_index=' || l_index);
2076 IF p_value_rec.COMPARISON_OPERATOR = '=' OR
2077 p_value_rec.COMPARISON_OPERATOR = '<>' OR
2078 p_value_rec.COMPARISON_OPERATOR = '<' OR
2079 p_value_rec.COMPARISON_OPERATOR = '>' OR
2080 p_value_rec.COMPARISON_OPERATOR = 'LIKE' OR
2081 p_value_rec.COMPARISON_OPERATOR = 'NOT LIKE' THEN
2082 IF p_value_rec.ID_USED_FLAG = 'Y' THEN
2083 -- l_value_str := p_value_rec.COMPARISON_OPERATOR || ' ' || p_value_rec.LOW_VALUE_CHAR_ID;
2084 l_value_str := p_value_rec.COMPARISON_OPERATOR || G_BIND_VAR_STRING || l_index;
2085 l_bind_vars(l_index).bind_index := l_index;
2086 l_bind_vars(l_index).bind_type := G_BIND_TYPE_NUMBER;
2087 l_bind_vars(l_index).bind_number := p_value_rec.LOW_VALUE_CHAR_ID;
2088 l_index := l_index + 1;
2089 ELSE
2090 -- l_value_str := p_value_rec.COMPARISON_OPERATOR || ' ''' || p_value_rec.LOW_VALUE_CHAR || '''';
2091 l_value_str := p_value_rec.COMPARISON_OPERATOR || G_BIND_VAR_STRING || l_index;
2092 l_bind_vars(l_index).bind_index := l_index;
2093 l_bind_vars(l_index).bind_type := G_BIND_TYPE_CHAR;
2094 l_bind_vars(l_index).bind_char := p_value_rec.LOW_VALUE_CHAR;
2095 l_index := l_index + 1;
2096 END IF;
2097 ELSIF p_value_rec.COMPARISON_OPERATOR = 'BETWEEN' OR
2098 p_value_rec.COMPARISON_OPERATOR = 'NOT BETWEEN' THEN
2099 IF p_value_rec.ID_USED_FLAG = 'N' THEN
2100 -- l_value_str := p_value_rec.COMPARISON_OPERATOR || ' ''' || p_value_rec.LOW_VALUE_CHAR || ''' AND ''' || p_value_rec.HIGH_VALUE_CHAR || '''';
2101
2102 l_temp_index := l_index + 1;
2103 /*
2104 l_value_str := p_value_rec.COMPARISON_OPERATOR || G_BIND_VAR_STRING || l_index
2105 || ' AND ' || G_BIND_VAR_STRING || l_index + 1;
2106 */
2107 l_value_str := p_value_rec.COMPARISON_OPERATOR || G_BIND_VAR_STRING || l_index
2108 || ' AND ' || G_BIND_VAR_STRING || l_temp_index ;
2109 l_bind_vars(l_index).bind_index := l_index;
2110 l_bind_vars(l_index).bind_type := G_BIND_TYPE_CHAR;
2111 l_bind_vars(l_index).bind_char := p_value_rec.LOW_VALUE_CHAR;
2112 l_index := l_index + 1;
2113 -- l_bind_vars(l_index).bind_index := l_index + 1;
2114 l_bind_vars(l_index).bind_index := l_index ;
2115 l_bind_vars(l_index).bind_type := G_BIND_TYPE_CHAR;
2116 l_bind_vars(l_index).bind_char := p_value_rec.HIGH_VALUE_CHAR;
2117 l_index := l_index + 1;
2118 /* yzhao: between numbers is not supported? or use LOW_VALUE_NUMBER, HIGH_VALUE_NUMBER?
2119 ELSE
2120 l_value_str := p_value_rec.COMPARISON_OPERATOR || ' ' || p_value_rec.LOW_VALUE_CHAR_ID || ' AND ' || p_value_rec.HIGH_VALUE_CHAR_ID;
2121 */
2122 END IF;
2123 END IF;
2124 x_cond_str := l_value_str;
2125 x_bind_vars := l_bind_vars;
2126 END compose_qualifier_values;
2127
2128
2129 /*****************************************************************************
2130 * NAME
2131 * get_territory_qualifiers
2132 *
2133 * PURPOSE
2134 * This procedure is a private procedure called by generate_party_for_territory
2135 * to get qualifier information of a territory
2136 *
2137 * NOTES
2138 * 1. currently JTF territory has no public api for getting territory detail information
2139 * JTF_TERRITORY_GET_PUB.Get_Territory_Details() is not publicly supported. Change it when api is public
2140 * 2. I'm concerned about the sql buffer size. As territory qualifier combination grows,
2141 * it may exceed the limit?
2142 *
2143 * HISTORY
2144 * 10/14/2001 yzhao created
2145 * 04/09/2003 niprakas Fixed the bug#2833114.
2146 *****************************************************************************/
2147
2148 PROCEDURE get_territory_qualifiers
2149 (
2150 p_terr_id IN NUMBER,
2151 p_bindvar_index IN NUMBER,
2152 x_terr_pid OUT NOCOPY NUMBER,
2153 x_terr_child_table OUT NOCOPY NUMBER_TBL_TYPE,
2154 x_hzsql_table OUT NOCOPY VARCHAR2_TBL_TYPE,
2155 x_bind_vars OUT NOCOPY BIND_TBL_TYPE
2156 ) IS
2157 l_api_version CONSTANT NUMBER := 1.0;
2158 l_return_status VARCHAR2(1);
2159 l_msg_count NUMBER;
2160 l_msg_data VARCHAR2(2000);
2161 l_tmp_str VARCHAR2(2000);
2162 J NUMBER;
2163 l_hzsql_table VARCHAR2_TBL_TYPE;
2164 l_terr_qual_id NUMBER;
2165 l_terr_rec JTF_TERRITORY_GET_PUB.Terr_Rec_Type;
2166 l_terr_type_rec JTF_TERRITORY_GET_PUB.Terr_Type_Rec_Type;
2167 l_terr_child_table JTF_TERRITORY_GET_PUB.Terr_Tbl_Type;
2168 l_terr_usgs_table JTF_TERRITORY_GET_PUB.Terr_Usgs_Tbl_Type;
2169 l_terr_qtype_usgs_table JTF_TERRITORY_GET_PUB.Terr_QType_Usgs_Tbl_Type;
2170 l_terr_qual_table JTF_TERRITORY_GET_PUB.Terr_Qual_Tbl_Type;
2171 l_terr_values_table JTF_TERRITORY_GET_PUB.Terr_Values_Tbl_Type;
2172 l_terr_rsc_table JTF_TERRITORY_GET_PUB.Terr_Rsc_Tbl_Type;
2173 -- This one is required ....
2174 l_hzparty_sql VARCHAR2(32000) := null;
2175 l_hzpartyacc_sql VARCHAR2(32000) := null;
2176 -- This is required ....
2177 l_hzpartyrel_sql VARCHAR2(32000) := null;
2178 -- This is no more required ...
2179 -- l_hzpartysite_sql VARCHAR2(2000) := null;
2180 l_hzpartysiteuse_sql VARCHAR2(32000) := null;
2181 -- This is required ..
2182 l_hzcustprof_sql VARCHAR2(32000) := null;
2183 -- This is new field ...
2184 l_hzlocations_sql VARCHAR2(32000) := null;
2185 /* -- l_hzcustname_sql handles customer name
2186 l_hzcustname_sql VARCHAR2(2000) := null;
2187 -- l_hzcustcat_sql handles the customer category
2188 l_hzcustcat_sql VARCHAR2(2000) := null;
2189 -- l_saleschannel_sql handles the sales channel
2190 l_hzsaleschannel_sql VARCHAR2(2000) := null; */
2191 l_out_child_table NUMBER_TBL_TYPE;
2192 l_out_hzsql_table VARCHAR2_TBL_TYPE;
2193 l_bind_vars BIND_TBL_TYPE;
2194 l_child_bind_vars BIND_TBL_TYPE;
2195 l_index NUMBER;
2196 BEGIN
2197 JTF_TERRITORY_GET_PUB.Get_Territory_Details(
2198 p_Api_Version => l_api_version,
2199 p_Init_Msg_List => FND_API.G_FALSE,
2200 x_return_status => l_return_status,
2201 x_msg_count => l_msg_count,
2202 x_msg_data => l_msg_data,
2203 p_terr_id => p_terr_id,
2204 x_terr_rec => l_terr_rec,
2205 x_terr_type_rec => l_terr_type_rec,
2206 x_terr_sub_terr_tbl => l_terr_child_table,
2207 x_terr_usgs_tbl => l_terr_usgs_table,
2208 x_terr_qtype_usgs_tbl => l_terr_qtype_usgs_table,
2209 x_terr_qual_tbl => l_terr_qual_table,
2210 x_terr_values_tbl => l_terr_values_table,
2211 x_terr_rsc_tbl => l_terr_rsc_table);
2212 -- dbms_output.put_line('get_territory_details(terr_id=' || p_terr_id || ') returns ' || l_return_status);
2213 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2214 RAISE FND_API.g_exc_error;
2215 END IF;
2216
2217 J := l_terr_values_table.FIRST;
2218 l_index := p_bindvar_index;
2219 write_conc_log('D: territory=' || p_terr_id || ' qualifier count=' || l_terr_qual_table.COUNT
2220 || ' first=' || NVL(l_terr_qual_table.FIRST, -100) || ' LAST=' || NVL(l_terr_qual_table.LAST, -200));
2221 FOR I IN NVL(l_terr_qual_table.FIRST, 1) .. NVL(l_terr_qual_table.LAST, 0) LOOP
2222 /* only processing OFFER's qualifiers at this time
2223 one qualifier may have multiple values. The relationship is 'OR' between these values
2224 it is assumed that qualifier table and qualifier value table are of the same order
2225 for example, qualifier table qualifier value table
2226 q1 value1 for q1
2227 q2 value1 for q2
2228 value2 for q2
2229 q3 value1 for q3
2230 */
2231 l_terr_qual_id := l_terr_qual_table(I).TERR_QUAL_ID;
2232 IF l_terr_qual_table(I).QUALIFIER_TYPE_NAME = 'OFFER' AND
2233 J <= l_terr_values_table.LAST AND
2234 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id THEN
2235
2236 write_conc_log('D: before compose_qualifier_values(' || I || ') index=' || l_index);
2237 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2238 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2239 , p_bindvar_index => l_index
2240 , p_bind_vars => l_bind_vars
2241 , x_cond_str => l_tmp_str
2242 , x_bind_vars => l_child_bind_vars
2243 );
2244 l_bind_vars := l_child_bind_vars;
2245
2246 IF l_terr_qual_table(I).QUALIFIER_NAME = 'City' THEN
2247 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.CITY ' || l_tmp_str;
2248 J := J + 1;
2249 write_conc_log('D: In the City ' || l_hzlocations_sql);
2250 write_conc_log('D: before compose_qualifier_values(' || I || ') index=' || l_index);
2251 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2252 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2253 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2254 , p_bindvar_index => l_index
2255 , p_bind_vars => l_bind_vars
2256 , x_cond_str => l_tmp_str
2257 , x_bind_vars => l_child_bind_vars
2258 );
2259 l_bind_vars := l_child_bind_vars;
2260 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.CITY ' || l_tmp_str;
2261 J := J + 1;
2262 END LOOP;
2263 write_conc_log('D: After the City ' || l_hzlocations_sql);
2264 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
2265
2266 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Country' THEN
2267 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.COUNTRY ' || l_tmp_str;
2268 J := J + 1;
2269 write_conc_log('D: In the country ' || l_hzlocations_sql);
2270 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2271 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2272 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2273 , p_bindvar_index => l_index
2274 , p_bind_vars => l_bind_vars
2275 , x_cond_str => l_tmp_str
2276 , x_bind_vars => l_child_bind_vars
2277 );
2278 l_bind_vars := l_child_bind_vars;
2279 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.COUNTRY ' || l_tmp_str;
2280 J := J + 1;
2281 END LOOP;
2282 write_conc_log('D: After the country ' || l_hzlocations_sql);
2283 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
2284
2285 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'County' THEN
2286 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.COUNTY ' || l_tmp_str;
2287 J := J + 1;
2288 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2289 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2290 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2291 , p_bindvar_index => l_index
2292 , p_bind_vars => l_bind_vars
2293 , x_cond_str => l_tmp_str
2294 , x_bind_vars => l_child_bind_vars
2295 );
2296 l_bind_vars := l_child_bind_vars;
2297 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.COUNTY ' || l_tmp_str;
2298 J := J + 1;
2299 END LOOP;
2300 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
2301
2302
2303 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Customer Category' THEN
2304 l_hzparty_sql := l_hzparty_sql || '(hzp.CATEGORY_CODE ' || l_tmp_str;
2305 J := J + 1;
2306 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2307 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2308 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2309 , p_bindvar_index => l_index
2310 , p_bind_vars => l_bind_vars
2311 , x_cond_str => l_tmp_str
2312 , x_bind_vars => l_child_bind_vars
2313 );
2314 l_bind_vars := l_child_bind_vars;
2315 l_hzparty_sql := l_hzparty_sql || ' OR hzp.CATEGORY_CODE ' || l_tmp_str;
2316 J := J + 1;
2317 END LOOP;
2318 l_hzparty_sql := l_hzparty_sql || ') AND ';
2319
2320 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Customer Name' THEN
2321 l_hzparty_sql := l_hzparty_sql || '(hzp.PARTY_ID ' || l_tmp_str;
2322 J := J + 1;
2323 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2324 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2325 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2326 , p_bindvar_index => l_index
2327 , p_bind_vars => l_bind_vars
2328 , x_cond_str => l_tmp_str
2329 , x_bind_vars => l_child_bind_vars
2330 );
2331 l_bind_vars := l_child_bind_vars;
2332 l_hzparty_sql := l_hzparty_sql || ' OR hzp.PARTY_ID ' || l_tmp_str;
2333 J := J + 1;
2334 END LOOP;
2335 l_hzparty_sql := l_hzparty_sql || ') AND ';
2336
2337
2338 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Postal Code' THEN
2339 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.POSTAL_CODE ' || l_tmp_str;
2340 J := J + 1;
2341 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2342 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2343 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2344 , p_bindvar_index => l_index
2345 , p_bind_vars => l_bind_vars
2346 , x_cond_str => l_tmp_str
2347 , x_bind_vars => l_child_bind_vars
2348 );
2349 l_bind_vars := l_child_bind_vars;
2350 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.POSTAL_CODE ' || l_tmp_str;
2351 J := J + 1;
2352 END LOOP;
2353 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
2354
2355 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Province' THEN
2356 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.PROVINCE ' || l_tmp_str;
2357 J := J + 1;
2358 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2359 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2360 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2361 , p_bindvar_index => l_index
2362 , p_bind_vars => l_bind_vars
2363 , x_cond_str => l_tmp_str
2364 , x_bind_vars => l_child_bind_vars
2365 );
2366 l_bind_vars := l_child_bind_vars;
2367 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.PROVINCE ' || l_terr_values_table(J).COMPARISON_OPERATOR || '''' || l_tmp_str;
2368 J := J + 1;
2369 END LOOP;
2370 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
2371
2372 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'State' THEN
2373 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.STATE ' || l_tmp_str;
2374 J := J + 1;
2375 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2376 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2377 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2378 , p_bindvar_index => l_index
2379 , p_bind_vars => l_bind_vars
2380 , x_cond_str => l_tmp_str
2381 , x_bind_vars => l_child_bind_vars
2382 );
2383 l_bind_vars := l_child_bind_vars;
2384 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.STATE ' || l_tmp_str;
2385 J := J + 1;
2386 END LOOP;
2387 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
2388
2389 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Account Classification' THEN
2390 l_hzpartyacc_sql := l_hzpartyacc_sql || '(hzca.CUSTOMER_CLASS_CODE ' || l_tmp_str;
2391 J := J + 1;
2392 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2393 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2394 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2395 , p_bindvar_index => l_index
2396 , p_bind_vars => l_bind_vars
2397 , x_cond_str => l_tmp_str
2398 , x_bind_vars => l_child_bind_vars
2399 );
2400 l_bind_vars := l_child_bind_vars;
2401 l_hzpartyacc_sql := l_hzpartyacc_sql || ' OR hzca.CUSTOMER_CLASS_CODE ' || l_tmp_str;
2402 J := J + 1;
2403 END LOOP;
2404 l_hzpartyacc_sql := l_hzpartyacc_sql || ') AND ';
2405
2406 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Sales Channel' THEN
2407 l_hzpartyacc_sql := l_hzpartyacc_sql || '(hzca.SALES_CHANNEL_CODE ' || l_tmp_str;
2408 J := J + 1;
2409 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2410 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2411 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2412 , p_bindvar_index => l_index
2413 , p_bind_vars => l_bind_vars
2414 , x_cond_str => l_tmp_str
2415 , x_bind_vars => l_child_bind_vars
2416 );
2417 l_bind_vars := l_child_bind_vars;
2418 l_hzpartyacc_sql := l_hzpartyacc_sql || ' OR hzca.SALES_CHANNEL_CODE ' || l_tmp_str;
2419 J := J + 1;
2420 END LOOP;
2421 l_hzpartyacc_sql := l_hzpartyacc_sql || ') AND ';
2422
2423 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Party Relation' THEN
2424 l_hzpartyrel_sql := l_hzpartyrel_sql || '(hzpr.RELATIONSHIP_CODE ' || l_tmp_str;
2425 J := J + 1;
2426 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2427 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2428 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2429 , p_bindvar_index => l_index
2430 , p_bind_vars => l_bind_vars
2431 , x_cond_str => l_tmp_str
2432 , x_bind_vars => l_child_bind_vars
2433 );
2434 l_bind_vars := l_child_bind_vars;
2435 l_hzpartyrel_sql := l_hzpartyrel_sql || ' OR hzpr.RELATIONSHIP_CODE ' || l_tmp_str;
2436 J := J + 1;
2437 END LOOP;
2438 l_hzpartyrel_sql := l_hzpartyrel_sql || ') AND ';
2439 -- 10/25 newly added
2440
2441 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Account Hierarchy' THEN
2442
2443 l_hzpartyrel_sql := l_hzpartyrel_sql || '(hzpr.OBJECT_ID ' || l_tmp_str;
2444 J := J + 1;
2445 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2446 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2447 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2448 , p_bindvar_index => l_index
2449 , p_bind_vars => l_bind_vars
2450 , x_cond_str => l_tmp_str
2451 , x_bind_vars => l_child_bind_vars
2452 );
2453 l_bind_vars := l_child_bind_vars;
2454 l_hzpartyrel_sql := l_hzpartyrel_sql || ' OR hzpr.OBJECT_ID ' || l_tmp_str;
2455 J := J + 1;
2456 END LOOP;
2457 l_hzpartyrel_sql := l_hzpartyrel_sql || ') AND ';
2458
2459 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Site Classification' THEN
2460 l_hzpartysiteuse_sql := l_hzpartysiteuse_sql || '(hzcsua.SITE_USE_CODE ' || l_tmp_str;
2461 J := J + 1;
2462 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2463 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2464 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2465 , p_bindvar_index => l_index
2466 , p_bind_vars => l_bind_vars
2467 , x_cond_str => l_tmp_str
2468 , x_bind_vars => l_child_bind_vars
2469 );
2470 l_bind_vars := l_child_bind_vars;
2471 l_hzpartysiteuse_sql := l_hzpartysiteuse_sql || ' OR hzcsua.SITE_USE_CODE ' || l_tmp_str;
2472 J := J + 1;
2473 END LOOP;
2474 l_hzpartysiteuse_sql := l_hzpartysiteuse_sql || ') AND ';
2475
2476
2477 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Account Code' THEN
2478 l_hzpartyacc_sql := l_hzpartyacc_sql || '(hzps.PARTY_SITE_ID ' || l_tmp_str;
2479 J := J + 1;
2480 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2481 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2482 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2483 , p_bindvar_index => l_index
2484 , p_bind_vars => l_bind_vars
2485 , x_cond_str => l_tmp_str
2486 , x_bind_vars => l_child_bind_vars
2487 );
2488 l_bind_vars := l_child_bind_vars;
2489 l_hzpartyacc_sql := l_hzpartyacc_sql || ' OR hzps.PARTY_SITE_ID ' || l_tmp_str;
2490 J := J + 1;
2491 END LOOP;
2492 l_hzpartyacc_sql := l_hzpartyacc_sql || ') AND ';
2493
2494 ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Customer Profile' THEN
2495 l_hzcustprof_sql := l_hzcustprof_sql || '(hzcp.PROFILE_CLASS_ID ' || l_tmp_str;
2496 J := J + 1;
2497 WHILE (J <= l_terr_values_table.LAST AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
2498 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
2499 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
2500 , p_bindvar_index => l_index
2501 , p_bind_vars => l_bind_vars
2502 , x_cond_str => l_tmp_str
2503 , x_bind_vars => l_child_bind_vars
2504 );
2505 l_bind_vars := l_child_bind_vars;
2506 l_hzcustprof_sql := l_hzcustprof_sql || ' OR hzcp.PROFILE_CLASS_ID ' || l_tmp_str;
2507 J := J + 1;
2508 END LOOP;
2509 l_hzcustprof_sql := l_hzcustprof_sql || ') AND ';
2510 END IF;
2511 END IF; -- IF qualifier_type_name='OFFER'
2512 /* to do claim qualifiers: 'Claim Type' 'Claim Class' 'Reasons' 'Vendor'
2513 add ' AND VENDOR=' to PO_VENDORS, AMS_TRADE_PROFILE sql
2514 */
2515 END LOOP; -- FOR I IN l_terr_qual_table.FIRST .. (l_terr_qual_table.LAST-1) LOOP
2516 /* It's important to maintain the same order as get_territory_qualifiers() returns */
2517 J := 1;
2518 l_out_hzsql_table(J) := l_hzparty_sql;
2519 --l_out_hzsql_table(J+1) := l_hzpartyacc_sql;
2520 l_out_hzsql_table(J+1) := l_hzpartyrel_sql;
2521 -- l_out_hzsql_table(J+3) := l_hzpartysite_sql;
2522 -- l_out_hzsql_table(J+4) := l_hzpartysiteuse_sql;
2523 l_out_hzsql_table(J+2) := l_hzcustprof_sql;
2524 -- l_out_hzsql_table(J+6) := l_hzcustname_sql;
2525 -- l_out_hzsql_table(J+7) := l_hzcustcat_sql;
2526 -- l_out_hzsql_table(J+8) := l_hzsaleschannel_sql;
2527 l_out_hzsql_table(J+3) := l_hzlocations_sql;
2528 l_out_hzsql_table(J+4) := l_hzpartyacc_sql;
2529 l_out_hzsql_table(J+5) := l_hzpartysiteuse_sql;
2530 x_hzsql_table := l_out_hzsql_table;
2531 FOR J IN l_terr_child_table.FIRST .. (l_terr_child_table.LAST-1) LOOP
2532 l_out_child_table(J) := l_terr_child_table(J).terr_id;
2533 END LOOP;
2534 x_terr_child_table := l_out_child_table;
2535 x_terr_pid := l_terr_rec.parent_territory_id;
2536 x_bind_vars := l_bind_vars;
2537 write_conc_log('get_territory_qualifiers(' || p_terr_id || '): ends binds=' || l_bind_vars.COUNT);
2538 END get_territory_qualifiers;
2539
2540
2541 /*****************************************************************************
2542 * NAME
2543 * generate_party_for_territory
2544 *
2545 * PURPOSE
2546 * This procedure is a private procedure used by LOAD_PARTY_MARKET_QUALIFIER
2547 * to generate party list for a territory and its children
2548 * recusive call
2549 *
2550 * NOTES
2551 *
2552 * HISTORY
2553 * 10/14/2001 yzhao created
2554 * 04/09/2003 niprakas Fix for the bug#2833114. The dynamic SQL are
2555 * changed. The insert statement for AMS_PARTY_MARKET_SEGMENTS
2556 * is changed. It now inserts cust_account_id,cust_acct_site_id
2557 * and cust_site_use_code.
2558 ******************************************************************************/
2559 PROCEDURE generate_party_for_territory
2560 ( p_errbuf OUT NOCOPY VARCHAR2,
2561 p_retcode OUT NOCOPY NUMBER,
2562 p_terr_id IN NUMBER,
2563 p_getparent_flag IN VARCHAR2 := 'N',
2564 p_bind_vars IN BIND_TBL_TYPE,
2565 p_hzparty_sql IN VARCHAR2 := null,
2566 p_hzpartyacc_sql IN VARCHAR2 := null,
2567 p_hzpartyrel_sql IN VARCHAR2 := null,
2568 -- p_hzpartysite_sql IN VARCHAR2 := null,
2569 p_hzpartysiteuse_sql IN VARCHAR2 := null,
2570 p_hzcustprof_sql IN VARCHAR2 := null,
2571 p_hzlocations_sql IN VARCHAR2 := null
2572 -- p_hzcustname_sql IN VARCHAR2 := null,
2573 -- p_hzcustcat_sql IN VARCHAR2 := null,
2574 -- p_hzsaleschannel_sql IN VARCHAR2 := null
2575 )
2576 IS
2577 l_full_name CONSTANT VARCHAR2(60) := 'GENERATE_PARTY_FOR_TERRITORY';
2578 l_err_msg VARCHAR2(2000);
2579 /* redefine these buffer sizes so they can fit all qualifier combinations */
2580 l_final_sql VARCHAR2(32000);
2581 l_party_select_sql VARCHAR2(32000) := null;
2582 l_party_where_sql VARCHAR2(32000) := null;
2583 l_party_join_sql VARCHAR2(32000) := null;
2584 l_hzparty_sql VARCHAR2(32000) := p_hzparty_sql;
2585 l_hzpartyacc_sql VARCHAR2(32000) := p_hzpartyacc_sql;
2586 l_hzpartyrel_sql VARCHAR2(32000) := p_hzpartyrel_sql;
2587 -- l_hzpartysite_sql VARCHAR2(10000) := p_hzpartysite_sql;
2588 l_hzpartysiteuse_sql VARCHAR2(32000) := p_hzpartysiteuse_sql;
2589 l_hzcustprof_sql VARCHAR2(32000) := p_hzcustprof_sql;
2590 l_hzlocations_sql VARCHAR2(32000) := p_hzlocations_sql;
2591 -- l_hzcustname_sql VARCHAR2(10000) := p_hzcustname_sql;
2592 -- l_hzcustcat_sql VARCHAR2(10000) := p_hzcustcat_sql;
2593 -- l_hzsaleschannel_sql VARCHAR2(10000) := p_hzsaleschannel_sql;
2594 l_hzsql_table VARCHAR2_TBL_TYPE;
2595 l_terr_id NUMBER;
2596 l_terr_pid NUMBER;
2597 l_terr_child_table NUMBER_TBL_TYPE;
2598 l_tmp_child_table NUMBER_TBL_TYPE;
2599 -- TYPE PartyCurTyp IS REF CURSOR; -- define weak REF CURSOR type
2600 -- l_party_cv PartyCurTyp; -- declare cursor variable
2601 l_party_mkt_seg_id NUMBER;
2602 l_party_id NUMBER;
2603 l_index NUMBER;
2604 l_client_info NUMBER;
2605 l_cust_account_id NUMBER;
2606 l_cust_acct_site_id NUMBER;
2607 l_cust_site_use_code VARCHAR2(30);
2608 flag VARCHAR2(2) := 'F';
2609 l_bindvar_index NUMBER;
2610 l_bind_vars BIND_TBL_TYPE;
2611 l_final_bind_vars BIND_TBL_TYPE;
2612 l_denorm_csr INTEGER;
2613
2614 /*
2615 CURSOR c_party_mkt_seg_seq IS -- generate an ID for INSERT
2616 SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
2617 FROM DUAL;
2618 */
2619 BEGIN
2620 p_retcode := 0;
2621 FND_MSG_PUB.initialize;
2622 Ams_Utility_pvt.Write_Conc_log(l_full_name || ': START for territory ' || p_terr_id);
2623 l_terr_id := p_terr_id;
2624 l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'), 1, 10));
2625 l_final_bind_vars := p_bind_vars;
2626 LOOP
2627 l_bindvar_index := l_final_bind_vars.COUNT + 1;
2628 get_territory_qualifiers
2629 (
2630 p_terr_id => l_terr_id,
2631 p_bindvar_index => l_bindvar_index,
2632 x_terr_pid => l_terr_pid,
2633 x_terr_child_table => l_tmp_child_table,
2634 x_hzsql_table => l_hzsql_table,
2635 x_bind_vars => l_bind_vars
2636 );
2637 write_conc_log(l_full_name || ' after get_territory_qualifiers(terr_id=' || l_terr_id || ') bindvar_count=' || l_bind_vars.count);
2638 /* it's important to be of exactly the same order as get_territory_qualifiers() returns */
2639
2640 l_index := 1;
2641 l_hzparty_sql := l_hzparty_sql || l_hzsql_table(l_index);
2642
2643 l_hzpartyrel_sql := l_hzpartyrel_sql || l_hzsql_table(l_index+1);
2644 -- l_hzpartysite_sql := l_hzpartysite_sql || l_hzsql_table(l_index+3);
2645
2646 l_hzcustprof_sql := l_hzcustprof_sql || l_hzsql_table(l_index+2);
2647 l_hzlocations_sql := l_hzlocations_sql || l_hzsql_table(l_index+3);
2648 l_hzpartyacc_sql := l_hzpartyacc_sql || l_hzsql_table(l_index+4);
2649 l_hzpartysiteuse_sql := l_hzpartysiteuse_sql || l_hzsql_table(l_index+5);
2650
2651
2652 write_conc_log(' l_hzparty_sql ' || l_hzparty_sql);
2653 -- write_conc_log('l_hzcustname_sql ' || l_hzcustname_sql);
2654 -- write_conc_log(' l_hzcustcat_sql ' ||l_hzcustcat_sql);
2655 write_conc_log(' l_hzpartyacc_sql ' ||l_hzpartyacc_sql);
2656 -- write_conc_log(' l_hzpartyacc_sql ' || l_hzpartyacc_sql);
2657 -- write_conc_log(' l_hzsaleschannel_sql ' || l_hzsaleschannel_sql);
2658 write_conc_log(' l_hzpartyrel_sql ' || l_hzpartyrel_sql);
2659 -- write_conc_log(' l_hzpartysite_sql ' || l_hzpartysite_sql);
2660 write_conc_log(' l_hzpartysiteuse_sql ' || l_hzpartysiteuse_sql);
2661 write_conc_log(' l_hzcustprof_sql ' || l_hzcustprof_sql);
2662 write_conc_log(' l_hzlocations_sql ' || l_hzlocations_sql);
2663
2664 -- l_hzcustname_sql := l_hzcustname_sql || l_hzsql_table(l_index+6);
2665 -- l_hzcustcat_sql := l_hzcustcat_sql || l_hzsql_table(l_index+7) ;
2666 -- l_hzsaleschannel_sql := l_hzsaleschannel_sql || l_hzsql_table(l_index+8);
2667
2668 -- yzhao: 05/08/2003 append this node's bind variable
2669 l_index := l_final_bind_vars.COUNT + 1;
2670 FOR i IN NVL(l_bind_vars.FIRST, 1) .. NVL(l_bind_vars.LAST, 0) LOOP
2671 l_final_bind_vars(l_index) := l_bind_vars(i);
2672 l_index := l_index + 1;
2673 END LOOP;
2674 l_bindvar_index := l_index;
2675
2676 -- remember the current node's children for later recursion
2677 IF (p_terr_id = l_terr_id) THEN
2678 l_terr_child_table := l_tmp_child_table;
2679 END IF;
2680 -- get the territory ancestors's qualifier information if it's required and if it is not root territory
2681 IF (p_getparent_flag = 'N' OR l_terr_pid = 1) THEN
2682 EXIT;
2683 END IF;
2684 l_terr_id := l_terr_pid;
2685 END LOOP;
2686
2687
2688
2689 IF l_hzparty_sql IS NOT NULL THEN
2690 l_party_select_sql := 'select DISTINCT hzca.party_id, hzca.cust_account_id, hzcsua.cust_acct_site_id,hzcsua.site_use_code';
2691 l_party_select_sql := l_party_select_sql || ' from hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa,hz_cust_accounts hzca, ';
2692 l_party_select_sql := l_party_select_sql || ' hz_party_sites hzps, hz_locations hzloc, hz_parties hzp ' ;
2693 l_party_where_sql := ' WHERE ' ;
2694 l_party_where_sql := l_party_where_sql || ' hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'')';
2695 l_party_where_sql := l_party_where_sql || ' AND hzcsua.status = ''A'' and hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id';
2696 l_party_where_sql := l_party_where_sql || ' AND hzcasa.cust_account_id = hzca.cust_account_id AND ' ;
2697 l_party_where_sql := l_party_where_sql || ' hzcasa.party_site_id = hzps.party_site_id AND hzps.location_id = hzloc.location_id ';
2698
2699 l_party_where_sql := l_party_where_sql || ' AND hzcasa.cust_account_id = hzca.cust_account_id ' ;
2700 l_party_where_sql := l_party_where_sql || ' AND hzca.party_id = hzp.party_id and ' || l_hzparty_sql;
2701 write_conc_log('l_hzparty_sql ' || l_party_select_sql || l_party_where_sql);
2702 flag := 'T';
2703
2704 END IF;
2705
2706
2707
2708
2709 IF l_hzpartysiteuse_sql IS NOT NULL THEN
2710 IF l_party_select_sql IS NULL THEN
2711 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, hzca.cust_account_id, hzcsua.cust_acct_site_id, hzcsua.site_use_code ' ;
2712 l_party_select_sql := l_party_select_sql || ' FROM hz_cust_accounts hzca, hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa ';
2713 l_party_where_sql := ' WHERE ' ;
2714 l_party_where_sql := l_party_where_sql || '( hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') OR ';
2715 l_party_where_sql := l_party_where_sql || substr(l_hzpartysiteuse_sql, 1, length(l_hzpartysiteuse_sql)-4) || ') AND ' ;
2716 l_party_where_sql := l_party_where_sql || 'hzcsua.status = ''A'' AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id AND ' ;
2717 l_party_where_sql := l_party_where_sql || ' hzcasa.cust_account_id = hzca.cust_account_id AND ';
2718 write_conc_log('IF l_hzpartysiteuse_sql ' || l_party_select_sql || l_party_where_sql);
2719 ELSE
2720 l_party_where_sql := null;
2721 l_party_where_sql := ' WHERE ' ;
2722 l_party_where_sql := l_party_where_sql || ' ( hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') OR ';
2723 l_party_where_sql := l_party_where_sql || substr(l_hzpartysiteuse_sql, 1, length(l_hzpartysiteuse_sql)-4) || ') AND ' ;
2724 l_party_where_sql := l_party_where_sql || ' hzcsua.status = ''A'' AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
2725 l_party_where_sql := l_party_where_sql || 'AND hzcasa.cust_account_id = hzca.cust_account_id AND ';
2726 l_party_where_sql := l_party_where_sql || 'hzcasa.party_site_id = hzps.party_site_id AND hzps.location_id = hzloc.location_id ';
2727 l_party_where_sql := l_party_where_sql || ' AND hzcasa.cust_account_id = hzca.cust_account_id ' ;
2728 l_party_where_sql := l_party_where_sql || ' AND hzca.party_id = hzp.party_id AND ' || l_hzparty_sql;
2729 write_conc_log('Else l_hzpartysiteuse_sql ' || l_party_select_sql || l_party_where_sql);
2730
2731
2732 END IF;
2733 END IF;
2734
2735
2736
2737 IF l_hzpartyacc_sql IS NOT NULL THEN
2738 IF l_party_select_sql IS NULL THEN
2739 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, hzca.cust_account_id, hzcsua.cust_acct_site_id, hzcsua.site_use_code FROM ';
2740 l_party_select_sql := l_party_select_sql || 'hz_cust_accounts hzca, hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa, ';
2741 l_party_select_sql := l_party_select_sql || ' hz_party_sites hzps ';
2742 l_party_where_sql := ' WHERE ' ;
2743 l_party_where_sql := l_party_where_sql || ' hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'')';
2744 -- For the Account Classification
2745 l_party_where_sql := l_party_where_sql || ' AND hzcsua.status = ''A'' AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
2746 l_party_where_sql := l_party_where_sql || 'AND hzcasa.cust_account_id = hzca.cust_account_id AND ' || l_hzpartyacc_sql;
2747 write_conc_log('IF l_hzpartyacc_sql ' || l_party_select_sql || l_party_where_sql);
2748
2749 ELSE
2750 -- l_party_select_sql := l_party_select_sql || ', hz_cust_accounts hzca';
2751 -- l_party_where_sql := l_party_where_sql || 'hzca.party_id AND ' || l_hzpartyacc_sql;
2752 IF (flag = 'F') THEN
2753 l_party_select_sql := l_party_select_sql || ' ,hz_party_sites hzps ';
2754 END IF;
2755 l_party_where_sql := l_party_where_sql || l_hzpartyacc_sql;
2756 write_conc_log('ELSE l_hzpartyacc_sql ' || l_party_select_sql || l_party_where_sql);
2757 END IF;
2758 END IF;
2759
2760
2761
2762
2763 IF l_hzpartyrel_sql IS NOT NULL THEN
2764 IF l_party_select_sql IS NULL THEN
2765 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, hzca.cust_account_id, hzcsua.cust_acct_site_id,hzcsua.site_use_code from ';
2766 l_party_select_sql := l_party_select_sql || ' hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa, hz_cust_accounts hzca, ';
2767 l_party_select_sql := l_party_select_sql || ' hz_relationships hzpr ';
2768 l_party_where_sql := ' WHERE ' ;
2769 l_party_where_sql := l_party_where_sql || ' hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'')';
2770 l_party_where_sql := l_party_where_sql || ' AND hzcsua.status = ''A'' AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
2771 l_party_where_sql := l_party_where_sql || ' AND hzcasa.cust_account_id = hzca.cust_account_id AND hzpr.subject_id = hzca.party_id ';
2772 l_party_where_sql := l_party_where_sql || ' AND hzpr.start_date <= SYSDATE AND NVL(hzpr.end_date, SYSDATE) >= SYSDATE ';
2773 l_party_where_sql := l_party_where_sql || ' AND hzpr.relationship_code = ''SUBSIDIARY_OF'' ' ;
2774 l_party_where_sql := l_party_where_sql || ' AND hzpr.status = ''A'' AND ' || l_hzpartyrel_sql;
2775 write_conc_log('IF l_hzpartyrel_sql ' || l_party_select_sql || l_party_where_sql);
2776
2777 ELSE
2778 l_party_select_sql := l_party_select_sql || ', hz_relationships hzpr';
2779 l_party_where_sql := l_party_where_sql || ' hzpr.subject_id = hzca.party_id AND hzpr.start_date <= SYSDATE ' ;
2780 l_party_where_sql := l_party_where_sql || ' AND NVL(hzpr.end_date, SYSDATE) >= SYSDATE ' ;
2781 l_party_where_sql := l_party_where_sql || ' AND hzpr.relationship_code = ''SUBSIDIARY_OFF'' ';
2782 l_party_where_sql := l_party_where_sql || ' AND hzpr.status = ''A'' AND ' || l_hzpartyrel_sql;
2783 write_conc_log('Else l_hzpartyrel_sql ' || l_party_select_sql || l_party_where_sql);
2784
2785 END IF;
2786 END IF;
2787
2788
2789
2790 /* it is important to check l_hzcustprof_sql AFTER l_hzpartyacc_sql
2791 so table hz_cust_accounts does not show twice
2792 */
2793 IF l_hzcustprof_sql IS NOT NULL THEN
2794 IF l_party_select_sql IS NULL THEN
2795 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, hzca.cust_account_id, hzcsua.cust_acct_site_id, ';
2796 l_party_select_sql := l_party_select_sql || ' hzcsua.site_use_code FROM hz_cust_accounts hzca, hz_cust_site_uses_all hzcsua, ';
2797 l_party_select_sql := l_party_select_sql || ' hz_cust_acct_sites_all hzcasa, hz_customer_profiles hzcp';
2798 l_party_where_sql := ' WHERE ' || l_hzcustprof_sql;
2799 l_party_where_sql := l_party_where_sql || ' hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'')';
2800 l_party_where_sql := l_party_where_sql || 'AND hzcsua.status = ''A'' AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
2801 l_party_where_sql := l_party_where_sql || ' AND hzcasa.cust_account_id = hzca.cust_account_id AND ';
2802 l_party_where_sql := l_party_where_sql || ' hzca.cust_account_id = hzcp.cust_account_id AND ';
2803 write_conc_log(' If l_hzcustprof_sql ' || l_party_select_sql || l_party_where_sql);
2804 ELSE
2805 IF l_hzpartyacc_sql IS NOT NULL THEN
2806 l_party_select_sql := l_party_select_sql || ', hz_customer_profiles hzcp';
2807 l_party_where_sql := l_party_where_sql || ' hzca.cust_account_id = hzcp.cust_account_id AND ' || l_hzcustprof_sql;
2808 write_conc_log(' If Else If l_hzcustprof_sql ' || l_party_select_sql || l_party_where_sql);
2809 ELSE
2810 l_party_select_sql := l_party_select_sql || ', hz_customer_profiles hzcp ';
2811 -- l_party_where_sql := l_party_where_sql || l_party_join_sql || 'hzca.party_id AND hzca.cust_account_id = hzcp.cust_account_id AND ' || l_hzcustprof_sql;
2812 l_party_where_sql := l_party_where_sql || ' hzca.cust_account_id = hzcp.cust_account_id AND ' || l_hzcustprof_sql;
2813 write_conc_log(' If Else else l_hzcustprof_sql ' || l_party_select_sql || l_party_where_sql);
2814 END IF;
2815 END IF;
2816 END IF;
2817
2818
2819 IF l_hzlocations_sql IS NOT NULL THEN
2820
2821 IF l_party_select_sql IS NULL THEN
2822 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, hzca.cust_account_id, hzcsua.cust_acct_site_id,hzcsua.site_use_code ';
2823 l_party_select_sql := l_party_select_sql || ' from hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa, hz_cust_accounts hzca, ';
2824 l_party_select_sql := l_party_select_sql || ' hz_relationships hzpr, hz_party_sites hzps, hz_locations hzloc ';
2825 l_party_where_sql := ' WHERE ' || l_hzlocations_sql;
2826 l_party_where_sql := l_party_where_sql || ' hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') and hzcsua.status = ''A'' ';
2827 l_party_where_sql := l_party_where_sql || ' AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
2828 l_party_where_sql := l_party_where_sql || ' AND hzcasa.cust_account_id = hzca.cust_account_id AND ' ;
2829 l_party_where_sql := l_party_where_sql || ' hzcasa.party_site_id = hzps.party_site_id AND hzps.location_id = hzloc.location_id AND' ;
2830 write_conc_log(' If l_hzloactions_sql ' || l_party_select_sql || l_party_where_sql);
2831
2832 ELSE
2833 -- l_party_select_sql := l_party_select_sql || ', hz_party_sites hzps, hz_locations hzloc';
2834 IF(flag = 'F') THEN
2835 l_party_select_sql := l_party_select_sql || ', hz_locations hzloc, hz_party_sites hzps ';
2836 END IF;
2837 l_party_where_sql := l_party_where_sql || ' hzcasa.party_site_id = hzps.party_site_id AND hzps.location_id = hzloc.location_id AND ';
2838 l_party_where_sql := l_party_where_sql || l_hzlocations_sql;
2839 write_conc_log(' Else l_hzloactions_sql ' || l_party_select_sql || l_party_where_sql);
2840
2841 END IF;
2842 END IF;
2843 /*
2844 DBMS_OUTPUT.PUT_LINE(' final from sql(' || length(l_party_select_sql) || '): ' || l_party_select_sql);
2845 DBMS_OUTPUT.PUT_LINE(' final where sql length=' || length(l_party_where_sql));
2846 l_index := 1;
2847 WHILE l_index < (length(l_party_where_sql)-4) LOOP
2848 DBMS_OUTPUT.PUT_LINE( substr(l_party_where_sql, l_index, 240));
2849 l_index := l_index + 240;
2850 END LOOP;
2851 */
2852 DELETE FROM AMS_PARTY_MARKET_SEGMENTS
2853 WHERE market_qualifier_type = 'TERRITORY'
2854 AND market_qualifier_reference = p_terr_id;
2855 -- remove 'AND ' at the end of the where clause
2856 -- write_conc_log('Before opening the cursor ');
2857 write_conc_log('D: The dynamic SQL ' || l_party_select_sql ||substr(l_party_where_sql, 1, length(l_party_where_sql)-4));
2858
2859
2860 IF l_party_select_sql IS NOT NULL THEN
2861 -- yzhao: 05/08/2003 SQL bind variable project
2862 l_final_sql := 'INSERT INTO AMS_PARTY_MARKET_SEGMENTS(ams_party_market_segment_id, last_update_date, last_updated_by';
2863 l_final_sql := l_final_sql || ', creation_date, created_by, last_update_login, object_version_number, market_segment_id';
2864 l_final_sql := l_final_sql || ', market_segment_flag, party_id, start_date_active, end_date_active, org_id';
2865 l_final_sql := l_final_sql || ', market_qualifier_type, market_qualifier_reference, cust_account_id, cust_acct_site_id, site_use_code)';
2866 l_final_sql := l_final_sql || ' SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL, SYSDATE, FND_GLOBAL.user_id';
2867 l_final_sql := l_final_sql || ', SYSDATE, FND_GLOBAL.user_id, FND_GLOBAL.conc_login_id, 1, 0';
2868 l_final_sql := l_final_sql || ', ''N'', party_id, SYSDATE, NULL, :org_id org_id';
2869 l_final_sql := l_final_sql || ', ''TERRITORY'', :terr_id market_qualifier_reference, cust_account_id, cust_acct_site_id, site_use_code FROM (';
2870 l_final_sql := l_final_sql || l_party_select_sql || substr(l_party_where_sql, 1, length(l_party_where_sql)-4) || ')';
2871 l_denorm_csr := DBMS_SQL.open_cursor;
2872
2873 DBMS_SQL.parse(l_denorm_csr, l_final_sql, DBMS_SQL.native);
2874 DBMS_SQL.BIND_VARIABLE (l_denorm_csr, ':org_id', l_client_info);
2875 DBMS_SQL.BIND_VARIABLE (l_denorm_csr, ':terr_id', p_terr_id);
2876 FOR i IN NVL(l_final_bind_vars.FIRST, 1) .. NVL(l_final_bind_vars.LAST, 0) LOOP
2877 write_conc_log('D: bind vars ' || i || ' index=' || l_final_bind_vars(i).bind_index
2878 || ' type=' || l_final_bind_vars(i).bind_type );
2879 IF l_final_bind_vars(i).bind_type = G_BIND_TYPE_CHAR THEN
2880 write_conc_log('D: bind vars ' || i || ' char=' || l_final_bind_vars(i).bind_char);
2881 DBMS_SQL.BIND_VARIABLE (l_denorm_csr, G_BIND_VAR_STRING || l_final_bind_vars(i).bind_index, l_final_bind_vars(i).bind_char);
2882 ELSIF l_final_bind_vars(i).bind_type = G_BIND_TYPE_NUMBER THEN
2883 write_conc_log('D: bind vars ' || i || ' number=' || l_final_bind_vars(i).bind_number);
2884 DBMS_SQL.BIND_VARIABLE (l_denorm_csr, G_BIND_VAR_STRING || l_final_bind_vars(i).bind_index, l_final_bind_vars(i).bind_number);
2885 END IF;
2886 END LOOP;
2887
2888 l_index := dbms_sql.execute(l_denorm_csr);
2889 write_conc_log('D: After executing ');
2890 dbms_sql.close_cursor(l_denorm_csr);
2891 /*
2892 OPEN l_party_cv FOR l_party_select_sql || substr(l_party_where_sql, 1, length(l_party_where_sql)-4);
2893 LOOP
2894 FETCH l_party_cv INTO l_party_id,l_cust_account_id,l_cust_acct_site_id,l_cust_site_use_code;
2895 write_conc_log('l_party_id ' || l_party_id);
2896 write_conc_log('l_cust_account_id ' || l_cust_account_id);
2897 write_conc_log('l_cust_acct_site_id ' || l_cust_acct_site_id);
2898 write_conc_log('l_cust_site_use_code ' || l_cust_site_use_code);
2899 EXIT WHEN l_party_cv%NOTFOUND;
2900 -- dbms_output.put_line(l_full_name || ': INSERT: party_id=' || l_party_id || ' territory_id=' || p_terr_id);
2901 OPEN c_party_mkt_seg_seq;
2902 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id;
2903 CLOSE c_party_mkt_seg_seq;
2904
2905 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
2906 (
2907 ams_party_market_segment_id
2908 , last_update_date
2909 , last_updated_by
2910 , creation_date
2911 , created_by
2912 , last_update_login
2913 , object_version_number
2914 , market_segment_id
2915 , market_segment_flag
2916 , party_id
2917 , start_date_active
2918 , end_date_active
2919 , org_id
2920 , market_qualifier_type
2921 , market_qualifier_reference
2922 , cust_account_id
2923 , cust_acct_site_id
2924 , site_use_code
2925 )
2926 VALUES
2927 (
2928 l_party_mkt_seg_id
2929 , SYSDATE
2930 , FND_GLOBAL.user_id
2931 , SYSDATE
2932 , FND_GLOBAL.user_id
2933 , FND_GLOBAL.conc_login_id
2934 , 1
2935 , 0
2936 , 'N'
2937 , l_party_id
2938 , SYSDATE
2939 , NULL
2940 , l_client_info
2941 , 'TERRITORY'
2942 , p_terr_id
2943 ,l_cust_account_id
2944 ,l_cust_acct_site_id
2945 ,l_cust_site_use_code
2946 );
2947 END LOOP;
2948 CLOSE l_party_cv;
2949 */
2950
2951 END IF;
2952
2953
2954 Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Success for territory ' || p_terr_id);
2955
2956 /* recursively generate party list for the territory's children
2957 passing in parent's qualifier directly so don't need to calculate again
2958 */
2959 l_index := l_terr_child_table.FIRST;
2960 WHILE l_index IS NOT NULL LOOP
2961 generate_party_for_territory
2962 ( p_errbuf => p_errbuf
2963 , p_retcode => p_retcode
2964 , p_terr_id => l_terr_child_table(l_index)
2965 , p_getparent_flag => 'N'
2966 , p_bind_vars => l_final_bind_vars
2967 , p_hzparty_sql => l_hzparty_sql
2968 , p_hzpartyacc_sql => l_hzpartyacc_sql
2969 , p_hzpartyrel_sql => l_hzpartyrel_sql
2970 -- , p_hzpartysite_sql => l_hzpartysite_sql
2971 , p_hzpartysiteuse_sql => l_hzpartysiteuse_sql
2972 , p_hzcustprof_sql => l_hzcustprof_sql
2973 --, p_hzcustname_sql => l_hzcustname_sql
2974 --, p_hzcustcat_sql => l_hzcustcat_sql
2975 --, p_hzsaleschannel_sql => l_hzsaleschannel_sql
2976 , p_hzlocations_sql => l_hzlocations_sql
2977 );
2978 l_index := l_terr_child_table.NEXT(l_index);
2979 END LOOP;
2980 EXCEPTION
2981 WHEN OTHERS THEN
2982 /* Let the master procdure handle exception */
2983 Ams_Utility_pvt.Write_Conc_log('Exception in get_party_territory ' || sqlerrm);
2984 p_retcode := 1;
2985 l_err_msg := 'Exception while generating parties for territory id=' || p_terr_id || ' - ' || sqlerrm;
2986 p_errbuf := l_err_msg;
2987 raise;
2988 END generate_party_for_territory;
2989
2990
2991 /*****************************************************************************
2992 * NAME
2993 * generate_party_for_buyinggroup
2994 *
2995 * PURPOSE
2996 * This procedure is a private procedure used by LOAD_PARTY_MARKET_QUALIFIER
2997 * to generate buying groups information
2998 *
2999 * NOTES
3000 *
3001 * HISTORY
3002 * 11/09/2001 yzhao created
3003 * 02/07/2003 yzhao to handle non-directional relationship like 'PARTNER_OF',
3004 * add directional_flag in c_get_object_ids
3005 ******************************************************************************/
3006 PROCEDURE generate_party_for_buyinggroup
3007 ( p_errbuf OUT NOCOPY VARCHAR2,
3008 p_retcode OUT NOCOPY NUMBER,
3009 p_bg_id IN NUMBER,
3010 p_direction IN VARCHAR2 := NULL,
3011 p_obj_list OUT NOCOPY NUMBER_TBL_TYPE
3012 )
3013 IS
3014 l_full_name CONSTANT VARCHAR2(60) := 'generate_party_for_buyinggroup';
3015 l_err_msg VARCHAR2(2000);
3016 l_obj_list NUMBER_TBL_TYPE;
3017 l_child_obj_list NUMBER_TBL_TYPE;
3018 l_all_obj_list NUMBER_TBL_TYPE;
3019 l_party_mkt_seg_id NUMBER_TBL_TYPE;
3020 l_client_info NUMBER;
3021 l_index NUMBER;
3022
3023 CURSOR c_get_object_ids IS
3024 SELECT subject_id
3025 FROM hz_relationships
3026 WHERE relationship_code = fnd_profile.VALUE('AMS_PARTY_RELATIONS_TYPE')
3027 AND subject_type = 'ORGANIZATION'
3028 AND subject_table_name = 'HZ_PARTIES'
3029 AND object_type = 'ORGANIZATION'
3030 AND object_table_name = 'HZ_PARTIES'
3031 AND start_date <= SYSDATE AND NVL(end_date, SYSDATE) >= SYSDATE
3032 AND status = 'A'
3033 AND object_id = p_bg_id
3034 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI */
3035 AND directional_flag = NVL(p_direction, directional_flag);
3036
3037 CURSOR c_party_mkt_seg_seq IS -- generate an ID
3038 SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
3039 FROM DUAL;
3040
3041 BEGIN
3042 Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Start buyinggroup_id=' || p_bg_id);
3043 p_errbuf := null;
3044 p_retcode := 0;
3045
3046 -- delete all buying group records for this subject_id
3047 DELETE FROM AMS_PARTY_MARKET_SEGMENTS
3048 WHERE market_qualifier_type = 'BG'
3049 AND market_qualifier_reference = p_bg_id;
3050
3051 l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10));
3052 OPEN c_party_mkt_seg_seq;
3053 FETCH c_party_mkt_seg_seq INTO l_index;
3054 CLOSE c_party_mkt_seg_seq;
3055
3056 -- 03/26/2002 always return the party itself as part of the buying group
3057 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
3058 (
3059 ams_party_market_segment_id
3060 , last_update_date
3061 , last_updated_by
3062 , creation_date
3063 , created_by
3064 , last_update_login
3065 , object_version_number
3066 , market_segment_id
3067 , market_segment_flag
3068 , party_id
3069 , start_date_active
3070 , end_date_active
3071 , org_id
3072 , market_qualifier_type
3073 , market_qualifier_reference
3074 )
3075 VALUES
3076 (
3077 l_index
3078 , SYSDATE
3079 , FND_GLOBAL.user_id
3080 , SYSDATE
3081 , FND_GLOBAL.user_id
3082 , FND_GLOBAL.conc_login_id
3083 , 1
3084 , 0
3085 , 'N'
3086 , p_bg_id
3087 , SYSDATE
3088 , NULL
3089 , l_client_info
3090 , 'BG'
3091 , p_bg_id
3092 );
3093
3094 OPEN c_get_object_ids;
3095 FETCH c_get_object_ids BULK COLLECT INTO l_obj_list;
3096 CLOSE c_get_object_ids;
3097
3098 -- dbms_output.put_line('buy(' || p_bg_id || '): object count=' || l_obj_list.count);
3099
3100 IF l_obj_list.count = 0 THEN
3101 -- return. Leaf node.
3102 p_obj_list := l_obj_list;
3103 Ams_Utility_pvt.Write_Conc_log(l_full_name || ': END buyinggroup_id=' || p_bg_id);
3104 return;
3105 END IF;
3106
3107 FOR I IN l_obj_list.FIRST .. l_obj_list.LAST LOOP
3108 OPEN c_party_mkt_seg_seq;
3109 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id(I);
3110 CLOSE c_party_mkt_seg_seq;
3111 END LOOP;
3112
3113 l_all_obj_list := l_obj_list;
3114 l_index := l_all_obj_list.LAST;
3115 -- get buying groups for all subject_ids of p_bg_id
3116 FOR I IN l_obj_list.FIRST .. l_obj_list.LAST LOOP
3117 generate_party_for_buyinggroup
3118 ( p_errbuf => p_errbuf,
3119 p_retcode => p_retcode,
3120 p_bg_id => l_obj_list(I),
3121 p_direction => p_direction,
3122 p_obj_list => l_child_obj_list
3123 );
3124
3125 -- append l_child_obj_list to l_all_obj_list
3126 IF l_child_obj_list.COUNT > 0 THEN
3127 FOR J IN l_child_obj_list.FIRST .. l_child_obj_list.LAST LOOP
3128 l_index := l_index + 1;
3129 l_all_obj_list(l_index) := l_child_obj_list(J);
3130 OPEN c_party_mkt_seg_seq;
3131 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id(l_index);
3132 CLOSE c_party_mkt_seg_seq;
3133 END LOOP;
3134 END IF;
3135 END LOOP;
3136 -- DBMS_OUTPUT.PUT_LINE(l_full_name || ': INSERT buying group: buyinggroup_id='
3137 -- || p_bg_id || ' count=' || l_all_obj_list.COUNT);
3138
3139 FORALL I IN l_all_obj_list.FIRST .. l_all_obj_list.LAST
3140 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
3141 (
3142 ams_party_market_segment_id
3143 , last_update_date
3144 , last_updated_by
3145 , creation_date
3146 , created_by
3147 , last_update_login
3148 , object_version_number
3149 , market_segment_id
3150 , market_segment_flag
3151 , party_id
3152 , start_date_active
3153 , end_date_active
3154 , org_id
3155 , market_qualifier_type
3156 , market_qualifier_reference
3157 )
3158 VALUES
3159 (
3160 l_party_mkt_seg_id(I)
3161 , SYSDATE
3162 , FND_GLOBAL.user_id
3163 , SYSDATE
3164 , FND_GLOBAL.user_id
3165 , FND_GLOBAL.conc_login_id
3166 , 1
3167 , 0
3168 , 'N'
3169 , l_all_obj_list(I)
3170 , SYSDATE
3171 , NULL
3172 , l_client_info
3173 , 'BG'
3174 , p_bg_id
3175 );
3176
3177 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
3178 for non-directional records, always insert a row pair of (A, B) and (B, A) */
3179 IF (p_direction IS NOT NULL AND l_all_obj_list.FIRST IS NOT NULL) THEN
3180
3181 FOR I IN l_all_obj_list.FIRST .. l_all_obj_list.LAST LOOP
3182 OPEN c_party_mkt_seg_seq;
3183 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id(I);
3184 CLOSE c_party_mkt_seg_seq;
3185 END LOOP;
3186
3187 FORALL I IN l_all_obj_list.FIRST .. l_all_obj_list.LAST
3188 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
3189 (
3190 ams_party_market_segment_id
3191 , last_update_date
3192 , last_updated_by
3193 , creation_date
3194 , created_by
3195 , last_update_login
3196 , object_version_number
3197 , market_segment_id
3198 , market_segment_flag
3199 , party_id
3200 , start_date_active
3201 , end_date_active
3202 , org_id
3203 , market_qualifier_type
3204 , market_qualifier_reference
3205 )
3206 VALUES
3207 (
3208 l_party_mkt_seg_id(I)
3209 , SYSDATE
3210 , FND_GLOBAL.user_id
3211 , SYSDATE
3212 , FND_GLOBAL.user_id
3213 , FND_GLOBAL.conc_login_id
3214 , 1
3215 , 0
3216 , 'N'
3217 , p_bg_id
3218 , SYSDATE
3219 , NULL
3220 , l_client_info
3221 , 'BG'
3222 , l_all_obj_list(I)
3223 );
3224 END IF;
3225
3226 p_obj_list := l_all_obj_list;
3227 Ams_Utility_pvt.Write_Conc_log(l_full_name || ': END buyinggroup_id=' || p_bg_id);
3228
3229 EXCEPTION
3230 WHEN OTHERS THEN
3231 /* Let the master procdure handle exception */
3232 p_retcode := 1;
3233 l_err_msg := 'Exception while generating buying group buyinggroup_id=' || p_bg_id || ' - ' || sqlerrm;
3234 p_errbuf := l_err_msg;
3235 -- dbms_output.put_line('Exception: ' || substr(l_err_msg, 1, 220));
3236 RAISE;
3237 END;
3238
3239
3240 /*****************************************************************************
3241 * NAME
3242 * LOAD_PARTY_MARKET_QUALIFIER
3243 *
3244 * PURPOSE
3245 * This procedure is a concurrent program to
3246 * generate buying groups recursively
3247 * generate party list that matches a given territory's qualifiers
3248 * it also recursively generates party list for the territory's children
3249 *
3250 * NOTES
3251 *
3252 * HISTORY
3253 * 10/04/2001 yzhao created
3254 * 11/14/2001 yzhao add buying group
3255 ******************************************************************************/
3256
3257 PROCEDURE LOAD_PARTY_MARKET_QUALIFIER
3258 ( errbuf OUT NOCOPY VARCHAR2,
3259 retcode OUT NOCOPY NUMBER,
3260 p_terr_id IN NUMBER := NULL,
3261 p_bg_id IN NUMBER := NULL
3262 )
3263 IS
3264 l_full_name CONSTANT VARCHAR2(60) := 'LOAD_PARTY_FOR_MARKET_QUALIFIERS';
3265 l_terr_id NUMBER;
3266 l_bg_id NUMBER;
3267 l_rel_profile VARCHAR2(30);
3268 l_rel_type VARCHAR2(30);
3269 l_obj_list NUMBER_TBL_TYPE;
3270 l_direction_code VARCHAR2(1);
3271 l_bind_vars BIND_TBL_TYPE;
3272
3273 CURSOR c_get_all_territories IS -- get all root territories of trade management
3274 /*
3275 SELECT distinct terr_id
3276 FROM jtf_terr_overview_v jtov
3277 WHERE jtov.source_id = -1003
3278 AND parent_territory_id = 1;
3279 */
3280 -- Fix for the bug#3158378
3281 select distinct JTR.terr_id
3282 FROM JTF_TERR_ALL JTR ,
3283 JTF_TERR_USGS_ALL JTU ,
3284 JTF_SOURCES_ALL JSE
3285 WHERE
3286 JTU.TERR_ID = JTR.TERR_ID
3287 AND JTU.SOURCE_ID = JSE.SOURCE_ID
3288 AND JTU.SOURCE_ID = -1003
3289 AND JTR.PARENT_TERRITORY_ID = 1
3290 AND NVL(JTR.ORG_ID, -99) = NVL(JTU.ORG_ID, NVL(JTR.ORG_ID, -99))
3291 AND JSE.ORG_ID IS NULL
3292 AND NVL(JTR.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ' ,
3293 NULL, SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99)) =
3294 NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',
3295 NULL, SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99);
3296
3297 CURSOR c_get_relationship_type(p_relationship_code VARCHAR2) IS
3298 SELECT relationship_type, direction_code
3299 FROM hz_relationship_types
3300 WHERE (forward_rel_code = p_relationship_code
3301 OR backward_rel_code = p_relationship_code)
3302 AND subject_type = 'ORGANIZATION'
3303 AND object_type = 'ORGANIZATION'
3304 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
3305 P - Parent C - Child N - non-directional
3306 e.g. 'PARTNER_OF' is non-directional relationship
3307 AND direction_code = 'P'
3308 */
3309 AND direction_code IN ('P', 'N')
3310 AND status = 'A';
3311
3312 /* yzhao: 08/07/2002 fix performance issue. Use index on relationship_type */
3313 CURSOR c_get_all_bgroots(p_relationship_code VARCHAR2, p_relationship_type VARCHAR2, p_direction_code VARCHAR2) IS
3314 -- get all root object_ids
3315 SELECT distinct r1.object_id
3316 FROM hz_relationships r1
3317 WHERE r1.relationship_type = p_relationship_type
3318 AND r1.relationship_code = p_relationship_code
3319 AND r1.subject_type = 'ORGANIZATION'
3320 AND r1.subject_table_name = 'HZ_PARTIES'
3321 AND r1.object_type = 'ORGANIZATION'
3322 AND r1.object_table_name = 'HZ_PARTIES'
3323 AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
3324 AND r1.status = 'A'
3325 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
3326 handle non-directional relationship e.g. PARTNER_OF
3327 */
3328 AND r1.directional_flag = NVL(p_direction_code, r1.directional_flag)
3329 AND NOT EXISTS
3330 (SELECT 1
3331 FROM hz_relationships r2
3332 WHERE r1.object_id = r2.subject_id
3333 AND r2.relationship_type = p_relationship_type
3334 AND r2.relationship_code = p_relationship_code
3335 AND r2.subject_type = 'ORGANIZATION'
3336 AND r2.subject_table_name = 'HZ_PARTIES'
3337 AND r2.object_type = 'ORGANIZATION'
3338 AND r2.object_table_name = 'HZ_PARTIES'
3339 AND r2.start_date <= SYSDATE AND NVL(r2.end_date, SYSDATE) >= SYSDATE
3340 AND r2.status = 'A'
3341 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
3342 handle non-directional relationship e.g. PARTNER_OF
3343 */
3344 AND r2.directional_flag = NVL(p_direction_code, r2.directional_flag)
3345 );
3346
3347 BEGIN
3348 Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Start ');
3349 -- SAVEPOINT LOAD_PARTY_MARKET_QUALIFIER;
3350
3351 errbuf := null;
3352 retcode := 0;
3353
3354 /* yzhao: 08/07/2002 fix bug 2503141 performance issue. Use index on relationship_type */
3355 l_direction_code := NULL;
3356 l_rel_profile := fnd_profile.VALUE('AMS_PARTY_RELATIONS_TYPE');
3357 OPEN c_get_relationship_type(l_rel_profile);
3358 FETCH c_get_relationship_type INTO l_rel_type, l_direction_code;
3359 CLOSE c_get_relationship_type;
3360
3361 IF p_bg_id IS NOT NULL THEN
3362 IF (l_direction_code = 'N') THEN
3363 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
3364 handle non-directional relationship e.g. PARTNER_OF, so search forward and backward relationship
3365 */
3366 generate_party_for_buyinggroup(p_errbuf => errbuf,
3367 p_retcode => retcode,
3368 p_bg_id => p_bg_id,
3369 p_direction => 'F',
3370 p_obj_list => l_obj_list);
3371 generate_party_for_buyinggroup(p_errbuf => errbuf,
3372 p_retcode => retcode,
3373 p_bg_id => p_bg_id,
3374 p_direction => 'B',
3375 p_obj_list => l_obj_list);
3376 ELSE
3377 generate_party_for_buyinggroup(p_errbuf => errbuf,
3378 p_retcode => retcode,
3379 p_bg_id => p_bg_id,
3380 p_direction => NULL,
3381 p_obj_list => l_obj_list);
3382 END IF;
3383 ELSE
3384 -- no buying group id parameter means generate party pair list for all buying groups
3385 DELETE FROM AMS_PARTY_MARKET_SEGMENTS
3386 WHERE market_qualifier_type = 'BG';
3387
3388 IF (l_direction_code = 'N') THEN
3389 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
3390 handle non-directional relationship e.g. PARTNER_OF, so search forward and backward relationship
3391 */
3392 l_direction_code := 'F';
3393 OPEN c_get_all_bgroots(l_rel_profile, l_rel_type, l_direction_code);
3394 LOOP
3395 FETCH c_get_all_bgroots INTO l_bg_id;
3396 EXIT WHEN c_get_all_bgroots%NOTFOUND;
3397 generate_party_for_buyinggroup(p_errbuf => errbuf,
3398 p_retcode => retcode,
3399 p_bg_id => l_bg_id,
3400 p_direction => l_direction_code,
3401 p_obj_list => l_obj_list);
3402 -- dbms_output.put_line('root: id= ' || l_bg_id || ' forward count=' || l_obj_list.count);
3403 END LOOP;
3404 CLOSE c_get_all_bgroots;
3405
3406 l_direction_code := 'B';
3407 OPEN c_get_all_bgroots(l_rel_profile, l_rel_type, l_direction_code);
3408 LOOP
3409 FETCH c_get_all_bgroots INTO l_bg_id;
3410 EXIT WHEN c_get_all_bgroots%NOTFOUND;
3411 generate_party_for_buyinggroup(p_errbuf => errbuf,
3412 p_retcode => retcode,
3413 p_bg_id => l_bg_id,
3414 p_direction => l_direction_code,
3415 p_obj_list => l_obj_list);
3416 -- dbms_output.put_line('root: id= ' || l_bg_id || ' backward count=' || l_obj_list.count);
3417 END LOOP;
3418 CLOSE c_get_all_bgroots;
3419 ELSE
3420 l_direction_code := NULL;
3421 OPEN c_get_all_bgroots(l_rel_profile, l_rel_type, l_direction_code);
3422 LOOP
3423 FETCH c_get_all_bgroots INTO l_bg_id;
3424 EXIT WHEN c_get_all_bgroots%NOTFOUND;
3425 generate_party_for_buyinggroup(p_errbuf => errbuf,
3426 p_retcode => retcode,
3427 p_bg_id => l_bg_id,
3428 p_direction => l_direction_code,
3429 p_obj_list => l_obj_list);
3430 END LOOP;
3431 CLOSE c_get_all_bgroots;
3432 END IF;
3433 END IF;
3434
3435 --
3436 COMMIT;
3437 --
3438 Ams_Utility_pvt.Write_Conc_log(' ----- ');
3439 Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Committed Buying Groups');
3440 Ams_Utility_pvt.Write_Conc_log(' ----- ');
3441
3442 IF p_terr_id IS NOT NULL THEN
3443 generate_party_for_territory(errbuf, retcode, p_terr_id, 'Y', l_bind_vars);
3444 ELSE
3445 -- no territory id parameter means generate party list for all territories
3446 DELETE FROM AMS_PARTY_MARKET_SEGMENTS
3447 WHERE market_qualifier_type = 'TERRITORY';
3448 OPEN c_get_all_territories;
3449 LOOP
3450 FETCH c_get_all_territories INTO l_terr_id;
3451 EXIT WHEN c_get_all_territories%NOTFOUND;
3452 generate_party_for_territory(errbuf, retcode, l_terr_id, 'N', l_bind_vars);
3453 END LOOP;
3454 CLOSE c_get_all_territories;
3455 END IF;
3456
3457 Ams_Utility_pvt.Write_Conc_log;
3458 COMMIT;
3459 EXCEPTION
3460 WHEN OTHERS THEN
3461 -- ROLLBACK TO LOAD_PARTY_MARKET_QUALIFIER;
3462 retcode := 1;
3463 Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Exception ' || sqlerrm);
3464 Ams_Utility_pvt.Write_Conc_log;
3465 END LOAD_PARTY_MARKET_QUALIFIER;
3466
3467
3468 END AMS_Party_Mkt_Seg_Loader_PVT;