[Home] [Help]
PACKAGE BODY: APPS.AMS_PARTY_SEG_LOADER_PVT
Source
1 PACKAGE BODY AMS_Party_Seg_Loader_PVT AS
2 /* $Header: amsvcecb.pls 120.2 2005/07/28 00:36:17 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_Party_Seg_Loader_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvldrb.pls';
6
7 /*****************************************************************************/
8 -- Procedure
9 -- Expire_Inactive_Party_Dbms
10 --
11 -- Purpose
12 -- expire parties that no longer belong to the given mkt_seg_id
13 --
14 -- Note :
15 -- This Procedure will expire the party using DBMS SQL
16 --
17 -- History
18 -- 05/03/2000 ptendulk created
19 -- 02/02/2001 yxliu Modified. Removed market_seg_flag since this is
20 -- no longer valid in Hornet.
21 --
22 -- 08/30/2001 yxliu Modified, use buck update
23 -------------------------------------------------------------------------------
24 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
25 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
26 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
27
28 PROCEDURE Get_Party_Tab
29 (
30 p_cell_id IN NUMBER,
31 x_party_tab OUT NOCOPY jtf_number_table,
32 x_party_count OUT NOCOPY NUMBER,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2
36 )
37 IS
38 l_api_name CONSTANT VARCHAR2(30) := 'Get_Party_Tab';
39 l_sql_tbl DBMS_SQL.varchar2s ;
40 l_cell_id NUMBER := p_cell_id;
41 l_cell_name VARCHAR2 (120);
42 -- Define the table type to store the party ids
43 l_temp NUMBER ;
44 l_party_cur NUMBER ;
45 l_dummy NUMBER ;
46 l_count NUMBER ;
47 l_party_tab jtf_number_table := jtf_number_table();
48 l_party_count NUMBER;
49
50 BEGIN
51 IF (AMS_DEBUG_HIGH_ON) THEN
52 AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
53 END IF;
54
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 -- Create the Savepoint
58 SAVEPOINT Get_Party_Tab;
59
60 IF (AMS_DEBUG_HIGH_ON) THEN
61 AMS_Utility_PVT.Debug_Message(l_api_name||' get comp sql for cell ');
62 END IF;
63
64 AMS_CELL_PVT.get_comp_sql(
65 p_api_version => 1,
66 p_init_msg_list => NULL,
67 p_validation_level => NULL,
68 x_return_status => x_return_status,
69 x_msg_count => x_msg_count,
70 x_msg_data => x_msg_data,
71 p_cell_id => l_cell_id,
72 p_party_id_only => FND_API.g_true,
73 x_sql_tbl => l_sql_tbl
74 );
75
76 IF x_return_status = FND_API.g_ret_sts_error THEN
77 IF (AMS_DEBUG_HIGH_ON) THEN
78 AMS_Utility_PVT.Debug_Message(l_api_name||' error on getting cell sql statement, please check if the workbook or sql string is valid or not');
79 END IF;
80
81 RAISE FND_API.g_exc_error;
82 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
83 IF (AMS_DEBUG_HIGH_ON) THEN
84 AMS_Utility_PVT.Debug_Message(l_api_name||' unexpected error on get cell sql statement, please check if the workbook or sql string is valid or not');
85 END IF;
86
87 RAISE FND_API.g_exc_unexpected_error;
88 END IF;
89
90 l_count := 1 ;
91 -- Open the cursor and parse it
92 IF (AMS_DEBUG_HIGH_ON) THEN
93
94 AMS_Utility_PVT.Debug_Message(l_api_name||' Parse the comp sql ');
95 END IF;
96 IF (DBMS_SQL.Is_Open(l_party_cur) = FALSE) THEN
97 l_party_cur := DBMS_SQL.Open_Cursor ;
98 END IF;
99 DBMS_SQL.Parse(l_party_cur ,
100 l_sql_tbl,
101 l_sql_tbl.first,
102 l_sql_tbl.last,
103 FALSE,
104 DBMS_SQL.Native) ;
105
106 DBMS_SQL.DEFINE_COLUMN(l_party_cur,1,l_temp);
107 l_dummy := DBMS_SQL.Execute(l_party_cur);
108 LOOP
109 IF DBMS_SQL.FETCH_ROWS(l_party_cur)>0 THEN
110 -- get column values of the row
111 DBMS_SQL.COLUMN_VALUE(l_party_cur,1, l_temp);
112 l_party_tab.extend;
113 l_party_tab(l_count) := l_temp ;
114 l_count := l_count + 1 ;
115 ELSE
116 -- No more rows to copy:
117 EXIT;
118 END IF;
119 END LOOP;
120
121 l_party_count := l_count - 1;
122
123 DBMS_SQL.Close_Cursor(l_party_cur);
124
125 IF (AMS_DEBUG_HIGH_ON) THEN
126 AMS_Utility_PVT.Debug_Message(l_api_name||' party_count = ' || l_party_count);
127 END IF;
128
129 x_party_tab := l_party_tab;
130 x_party_count := l_party_count;
131
132 EXCEPTION
133 WHEN OTHERS THEN
134 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
135 DBMS_SQL.Close_Cursor(l_party_cur) ;
136 END IF;
137
138 IF (AMS_DEBUG_HIGH_ON) THEN
139 AMS_Utility_PVT.Debug_Message('Error in Get_Party_Tab '||sqlerrm);
140 END IF;
141
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
143
144 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
145 THEN
146 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
147 END IF;
148
149 FND_MSG_PUB.Count_AND_Get
150 ( p_count => x_msg_count,
151 p_data => x_msg_data,
152 p_encoded => FND_API.G_FALSE
153 );
154
155 END Get_Party_Tab;
156
157 PROCEDURE Expire_Inactive_Party_Dbms
158 (
159 p_mkt_seg_id IN NUMBER
160 , p_party_tbl IN jtf_number_table
161 , x_return_status OUT NOCOPY VARCHAR2
162 , x_msg_count OUT NOCOPY NUMBER
163 , x_msg_data OUT NOCOPY VARCHAR2
164 )
165 IS
166 l_api_name CONSTANT VARCHAR2(30) := 'expire_inactive_party_dbms';
167 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
168
169 l_party_tbl jtf_number_table;
170
171 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
172 l_exp_party_tbl num_tab;
173
174 --Cursor to select expired parties which are active in segments table
175 CURSOR C_expired_party_ids IS
176 SELECT party_id FROM AMS_PARTY_MARKET_SEGMENTS
177 WHERE market_segment_id = p_mkt_seg_id
178 AND end_date_active IS NULL
179 MINUS
180 SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table));
181
182 BEGIN
183
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185
186 IF (AMS_DEBUG_HIGH_ON) THEN
187 AMS_Utility_PVT.Debug_Message(l_full_name||': Start');
188 END IF;
189
190 -- l_party_tbl := p_party_tbl;
191
192 OPEN C_expired_party_ids;
193 FETCH C_expired_party_ids BULK COLLECT INTO l_exp_party_tbl;
194 CLOSE C_expired_party_ids;
195
196 IF (AMS_DEBUG_HIGH_ON) THEN
197 AMS_Utility_PVT.Debug_Message(l_full_name||': Total record expired: '||l_exp_party_tbl.count);
198 END IF;
199
200 IF l_exp_party_tbl.count > 0 THEN
201
202 FORALL i in l_exp_party_tbl.FIRST..l_exp_party_tbl.LAST
203 UPDATE AMS_PARTY_MARKET_SEGMENTS
204 SET end_date_active = SYSDATE
205 , last_update_date = SYSDATE
206 , last_updated_by = FND_GLOBAL.user_id
207 , last_update_login = FND_GLOBAL.conc_login_id
208 WHERE market_segment_id = p_mkt_seg_id
209 AND party_id = l_exp_party_tbl(i);
210 END IF;
211
212 IF (AMS_DEBUG_HIGH_ON) THEN
213 AMS_Utility_PVT.Debug_Message(l_full_name||': End');
214 END IF;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 IF (AMS_DEBUG_HIGH_ON) THEN
219
220 AMS_Utility_PVT.Debug_Message('Error in expire_inactive_party_dbms');
221 END IF;
222
223 IF C_expired_party_ids%ISOPEN THEN
224 CLOSE C_expired_party_ids;
225 END IF;
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
227
228 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
229 THEN
230 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
231 END IF;
232
233 FND_MSG_PUB.Count_AND_Get
234 ( p_count => x_msg_count,
235 p_data => x_msg_data,
236 p_encoded => FND_API.G_FALSE
237 );
238
239 END Expire_Inactive_Party_Dbms;
240
241
242 /*****************************************************************************/
243 -- Procedure
244 -- expire_changed_party_Dbms
245 --
246 -- Purpose
247 -- expire parties that originally belong to other mkt_seg_id and currently
248 -- belong to the give mkt_seg_id
249 --
250 -- Note :
251 -- This Procedure will expire the party using DBMS SQL
252 --
253 -- History
254 -- 05/03/2000 ptendulk created
255 -- 02/02/2001 yxliu Modified. Removed market_segment_flag
256 -- 08/30/2001 yxliu Modified, use bulk update
257 -------------------------------------------------------------------------------
258 PROCEDURE Expire_Changed_Party_Dbms
259 (
260 p_mkt_seg_id IN NUMBER
261 , p_sql_tbl IN t_party_tab
262 , x_return_status OUT NOCOPY VARCHAR2
263 , x_msg_count OUT NOCOPY NUMBER
264 , x_msg_data OUT NOCOPY VARCHAR2
265 )
266 IS
267 l_api_name CONSTANT VARCHAR2(30) := 'expire_changed_party_dbms';
268
269 CURSOR c_old_party_rec IS -- party_id and mkt_seg_id of exsiting party
270 SELECT market_segment_id, party_id
271 FROM AMS_PARTY_MARKET_SEGMENTS
272 WHERE market_segment_id <> p_mkt_seg_id
273 AND end_date_active IS NULL
274 ORDER BY party_id;
275
276 l_old_party_id NUMBER;
277 l_old_mkt_seg_id NUMBER;
278 l_expire_flag VARCHAR2(1);
279 l_sql_tbl t_party_tab;
280 l_iterator NUMBER := 1;
281
282 BEGIN
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284 OPEN c_old_party_rec;
285
286 LOOP
287
288 FETCH c_old_party_rec INTO l_old_mkt_seg_id, l_old_party_id;
289 EXIT WHEN c_old_party_rec%NOTFOUND;
290
291 l_expire_flag := 'N';
292
293 FOR i IN p_sql_tbl.FIRST..p_sql_tbl.last
294 LOOP
295 IF l_old_party_id = p_sql_tbl(i) THEN
296 l_expire_flag := 'Y';
297 EXIT;
298 END IF;
299 END LOOP;
300
301 IF l_expire_flag = 'Y' THEN
302 l_sql_tbl(l_iterator) := l_old_party_id;
303 l_iterator := l_iterator + 1;
304 END IF;
305
306 END LOOP;
307 CLOSE c_old_party_rec ;
308
309 -- Do bulk update
310 IF (AMS_DEBUG_HIGH_ON) THEN
311
312 AMS_Utility_PVT.Debug_Message(l_api_name||' expire changed parties ');
313 END IF;
314 IF l_iterator > 1 THEN
315 FORALL i in l_sql_tbl.first .. l_sql_tbl.last
316 UPDATE AMS_PARTY_MARKET_SEGMENTS
317 SET end_date_active = SYSDATE
318 , last_update_date = SYSDATE
319 , last_updated_by = FND_GLOBAL.user_id
320 , last_update_login = FND_GLOBAL.conc_login_id
321 WHERE market_segment_id = l_old_mkt_seg_id
322 AND party_id = l_sql_tbl(i);
323 END IF;
324
325 EXCEPTION
326 WHEN OTHERS THEN
327 IF (AMS_DEBUG_HIGH_ON) THEN
328
329 AMS_Utility_Pvt.Debug_Message('Error in expire_changed_party_dbms');
330 END IF;
331
332 IF(c_old_party_rec%ISOPEN)then
333 CLOSE c_old_party_rec;
334 END IF;
335
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
337
338 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
339 THEN
340 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
341 END IF;
342
343 FND_MSG_PUB.Count_AND_Get
344 ( p_count => x_msg_count,
345 p_data => x_msg_data,
346 p_encoded => FND_API.G_FALSE
347 );
348
349 END Expire_Changed_Party_Dbms;
350
351
352 /*****************************************************************************/
353 -- Procedure
354 -- insert_new_party_dbms
355 --
356 -- Purpose
357 -- insert a new party if it is not there, update it if it is expired
358 -- do nothing if it is active
359 --
360 -- Note :
361 -- This Procedure will expire the party using DBMS sql
362 --
363 -- History
364 -- 05/03/2000 ptendulk created
365 -- 02/02/2001 yxliu modified, removed market_segment_flag.
366 -- 06/21/2001 yxliu modified, populate org_id.
367 -------------------------------------------------------------------------------
368 PROCEDURE Insert_New_Party_Dbms
369 (
370 p_mkt_seg_id IN NUMBER
371 , p_party_tbl IN jtf_number_table
372 , x_return_status OUT NOCOPY VARCHAR2
373 , x_msg_count OUT NOCOPY NUMBER
374 , x_msg_data OUT NOCOPY VARCHAR2
375 )
376 IS
377 l_api_name CONSTANT VARCHAR2(30) := 'insert_new_party';
378
379 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
380 l_party_tbl num_tab;
381
382 --Cursor to select new parties which only available in with the seg query, not in segments table
383 CURSOR C_new_parties IS
384 SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table))
385 MINUS
386 SELECT party_id FROM AMS_PARTY_MARKET_SEGMENTS
387 WHERE market_segment_id = p_mkt_seg_id;
388
389 --Cursor to select inactive parties from segments table which are also returned by seg query
390 CURSOR C_activate_parties IS
391 SELECT seg.party_id FROM AMS_PARTY_MARKET_SEGMENTS seg,
392 (SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table))) tbl
393 WHERE seg.party_id = tbl.party_id
394 AND seg.end_date_active IS NOT NULL; --Inactive parties
395
396 BEGIN
397
398 IF (AMS_DEBUG_HIGH_ON) THEN
399 AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
400 END IF;
401
402 x_return_status := FND_API.G_RET_STS_SUCCESS;
403
404 OPEN C_new_parties;
405 FETCH C_new_parties BULK COLLECT INTO l_party_tbl;
406 CLOSE C_new_parties;
407
408 IF (AMS_DEBUG_HIGH_ON) THEN
409 AMS_Utility_PVT.Debug_Message(l_api_name||' Bulk insert '||l_party_tbl.count);
410 END IF;
411
412 IF l_party_tbl.count > 0 then
413
414 FORALL i IN l_party_tbl.FIRST..l_party_tbl.LAST
415 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
416 (
417 ams_party_market_segment_id
418 , last_update_date
419 , last_updated_by
420 , creation_date
421 , created_by
422 , last_update_login
423 , object_version_number
424 , market_segment_id
425 , market_segment_flag
426 , party_id
427 , start_date_active
428 , end_date_active
429 , org_id
430 )
431 select
432 AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
433 , SYSDATE
434 , FND_GLOBAL.user_id
435 , SYSDATE
436 , FND_GLOBAL.user_id
437 , FND_GLOBAL.conc_login_id
438 , 1
439 , p_mkt_seg_id
440 , 'Y' -- always put true for market_segment_flag
441 , l_party_tbl(i)
442 , SYSDATE
443 , NULL
444 , TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10))
445 from dual;
446 END IF;
447
448 IF (AMS_DEBUG_HIGH_ON) THEN
449 AMS_Utility_PVT.Debug_Message(l_api_name||' Get inactive parties ');
450 END IF;
451
452 OPEN C_activate_parties;
453 FETCH C_activate_parties BULK COLLECT INTO l_party_tbl;
454 CLOSE C_activate_parties;
455
456 IF (AMS_DEBUG_HIGH_ON) THEN
457 AMS_Utility_PVT.Debug_Message(l_api_name||' Bulk update '||l_party_tbl.count);
458 END IF;
459
460 IF l_party_tbl.count > 0 THEN
461 FORALL i IN l_party_tbl.FIRST..l_party_tbl.LAST
462 UPDATE AMS_PARTY_MARKET_SEGMENTS SET
463 last_update_date = SYSDATE
464 , last_updated_by = FND_GLOBAL.user_id
465 , last_update_login = FND_GLOBAL.conc_login_id
466 , object_version_number = object_version_number + 1
467 , market_segment_id = p_mkt_seg_id
468 , party_id = l_party_tbl(i)
469 , start_date_active =SYSDATE
470 , end_date_active = NULL
471 WHERE market_segment_id = p_mkt_seg_id
472 AND party_id = l_party_tbl(i);
473 END IF;
474
475 IF (AMS_DEBUG_HIGH_ON) THEN
476 AMS_Utility_PVT.Debug_Message(l_api_name||' End');
477 END IF;
478
479 EXCEPTION
480 WHEN OTHERS THEN
481 IF (AMS_DEBUG_HIGH_ON) THEN
482
483 AMS_Utility_PVT.Debug_Message('Error in insert_new_party'||sqlerrm);
484 END IF;
485
486 IF(C_new_parties%ISOPEN)then
487 CLOSE C_new_parties;
488 END IF;
489 IF(C_activate_parties%ISOPEN)then
490 CLOSE C_activate_parties;
491 END IF;
492
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
494
495 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
496 THEN
497 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
498 END IF;
499
500 FND_MSG_PUB.Count_AND_Get
501 ( p_count => x_msg_count,
502 p_data => x_msg_data,
503 p_encoded => FND_API.G_FALSE
504 );
505
506 END Insert_New_Party_Dbms;
507
508 /*****************************************************************************/
509 -- Procedure
510 -- load_party_seg_one
511 -- Purpose
512 -- load ams_party_market_segments for one segment
513 --
514 -- Note
515 -- 1. The process will execute the ams_cell_pvt.get_comp_sql for a given
516 -- cell_id to get its sql and its ancestors, then use DBMS SQL to excute
517 -- the returned SQL table to get parties that belong to that sql.
518 -- 2. If cell_id is passed into, then only that cell will be refreshed or
519 -- else all the cells will be refreshed.
520 -- History
521 -- 01/26/2001 yxliu created
522 -- 06/22/2001 yxliu modified, add logic to update segment size
523 -------------------------------------------------------------------------------
524 PROCEDURE Load_Party_Seg_One
525 (
526 p_cell_id IN NUMBER ,--DEFAULT NULL,
527 x_return_status OUT NOCOPY VARCHAR2,
528 x_msg_count OUT NOCOPY NUMBER,
529 x_msg_data OUT NOCOPY VARCHAR2
530 )
531 IS
532 l_api_name CONSTANT VARCHAR2(30) := 'Load_Party_Seg_One';
533
534 l_cell_id NUMBER := p_cell_id;
535 l_cell_name VARCHAR2 (120);
536 -- Define the table type to store the party ids
537 l_party_tab jtf_number_table ;
538 l_party_count NUMBER;
539 l_last_size NUMBER;
540
541 CURSOR c_last_size (p_cell_id IN number)
542 IS
543 select act_size
544 from ams_act_sizes
545 where arc_act_size_used_by = 'CELL'
546 and act_size_used_by_id = p_cell_id
547 order by last_update_date desc, activity_size_id desc;
548
549
550 BEGIN
551 IF (AMS_DEBUG_HIGH_ON) THEN
552 AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
553 END IF;
554
555 x_return_status := FND_API.G_RET_STS_SUCCESS;
556
557 -- Create the Savepoint
558 SAVEPOINT Load_Party_Seg;
559
560 -- execute the segment sql and get parties in pl/sql table
561 IF (AMS_DEBUG_HIGH_ON) THEN
562 AMS_Utility_PVT.Debug_Message(l_api_name||' Get_Party_Tab ');
563 END IF;
564 Get_Party_Tab
565 (
566 l_cell_id,
567 l_party_tab,
568 l_party_count,
569 x_return_status,
570 x_msg_count,
571 x_msg_data
572 );
573 IF x_return_status = FND_API.g_ret_sts_error THEN
574 RAISE FND_API.g_exc_error;
575 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
576 RAISE FND_API.g_exc_unexpected_error;
577 END IF;
578
579 -- expires parties that no longer belong to the given market segment
580 IF (AMS_DEBUG_HIGH_ON) THEN
581 AMS_Utility_PVT.Debug_Message(l_api_name||' Expire_Inactive_Party_Dbms ');
582 END IF;
583 Expire_Inactive_Party_Dbms
584 (
585 l_cell_id,
586 l_party_tab,
587 x_return_status,
588 x_msg_count,
589 x_msg_data
590 );
591 IF x_return_status = FND_API.g_ret_sts_error THEN
592 RAISE FND_API.g_exc_error;
593 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
594 RAISE FND_API.g_exc_unexpected_error;
595 END IF;
596
597 -- expires parties that originally belong to other marekt segments
598 -- and currently belong to the given market segment
599 --IF (AMS_DEBUG_HIGH_ON) THENAMS_Utility_PVT.Debug_Message(l_api_name||' Expire_Changed_Party_Dbms ');END IF;
600 --Expire_Changed_Party_Dbms
601 -- (
602 -- l_cell_id,
603 -- l_party_tab,
604 -- x_return_status,
605 -- x_msg_count,
606 -- x_msg_data
607 -- );
608 --IF x_return_status = FND_API.g_ret_sts_error THEN
609 -- RAISE FND_API.g_exc_error;
610 --ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
611 -- RAISE FND_API.g_exc_unexpected_error;
612 --END IF;
613
614 IF l_party_count > 0 THEN
615 -- insert new parties that do not exist in the table
616 IF (AMS_DEBUG_HIGH_ON) THEN
617 AMS_Utility_PVT.Debug_Message(l_api_name||' Insert_New_Party_Dbms ');
618 END IF;
619 Insert_New_Party_Dbms
620 (
621 l_cell_id,
622 l_party_tab,
623 x_return_status,
624 x_msg_count,
625 x_msg_data
626 );
627 IF x_return_status = FND_API.g_ret_sts_error THEN
628 RAISE FND_API.g_exc_error;
629 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
630 RAISE FND_API.g_exc_unexpected_error;
631 END IF;
632 END IF;
633
634 -- Update size for this cell
635 UPDATE ams_cells_all_b
636 SET original_size = l_party_count,
637 object_version_number = object_version_number + 1,
638 last_update_date = SYSDATE,
639 last_updated_by = FND_GLOBAL.user_id,
640 last_update_login = FND_GLOBAL.conc_login_id
641 WHERE cell_id = l_cell_id;
642
643 -- Keep size history
644 OPEN C_last_size(l_cell_id);
645 FETCH C_last_size INTO l_last_size;
646 CLOSE C_last_size;
647
648 IF l_last_size is null THEN
649 l_last_size := 0;
650 END IF;
651
652 INSERT INTO ams_act_sizes
653 (
654 activity_size_id,
655 last_update_date,
656 last_updated_by,
657 creation_date,
658 created_by,
659 last_update_login,
660 act_size_used_by_id,
661 arc_act_size_used_by,
662 act_size,
663 description,
664 size_delta
665 )
666 VALUES
667 (
668 ams_act_sizes_s.nextval,
669 SYSDATE,
670 fnd_global.user_id,
671 SYSDATE,
672 fnd_global.user_id,
673 fnd_global.conc_login_id,
674 l_cell_id,
675 'CELL',
676 l_party_count,
677 'SUCCESSED',
678 l_party_count - l_last_size
679 );
680
681 -- If no errors, commit the work
682 COMMIT WORK;
683
684 EXCEPTION
685 WHEN FND_API.g_exc_unexpected_error THEN
686 ROLLBACK TO Load_Party_Seg;
687 x_return_status := FND_API.g_ret_sts_unexp_error ;
688 FND_MSG_PUB.count_and_get (
689 p_encoded => FND_API.g_false,
690 p_count => x_msg_count,
691 p_data => x_msg_data
692 );
693 WHEN OTHERS THEN
694 ROLLBACK TO Load_Party_Seg;
695 x_return_status := FND_API.g_ret_sts_unexp_error ;
696
697 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
698 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
699 END IF;
700
701 FND_MSG_PUB.count_and_get (
702 p_encoded => FND_API.g_false,
703 p_count => x_msg_count,
704 p_data => x_msg_data
705 );
706
707 END Load_Party_Seg_One;
708
709 /*****************************************************************************/
710 -- Procedure
711 -- load_party_seg
712 -- Purpose
713 -- load ams_party_market_segments
714 --
715 -- Note
716 -- 1. If cell_id is passed into, then only that cell will be refreshed or
717 -- else all the cells will be refreshed.
718 -- History
719 -- 01/26/2001 yxliu created
720 -- 06/22/2001 yxliu modified. Call load_party_seg_one.
721 -------------------------------------------------------------------------------
722 PROCEDURE Load_Party_Seg
723 (
724 p_cell_id IN NUMBER ,--DEFAULT NULL,
725 x_return_status OUT NOCOPY VARCHAR2,
726 x_msg_count OUT NOCOPY NUMBER,
727 x_msg_data OUT NOCOPY VARCHAR2
728 )
729 IS
730 l_api_name CONSTANT VARCHAR2(30) := 'Load_Party_Seg';
731
732 l_cell_id NUMBER := p_cell_id;
733 l_cell_name VARCHAR2(120);
734
735 CURSOR c_all_cell_rec IS
736 SELECT cell_id, cell_name
737 FROM ams_cells_vl;
738
739 failed_flag VARCHAR2(1) := 'N';
740
741 BEGIN
742 IF (AMS_DEBUG_HIGH_ON) THEN
743
744 AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
745 END IF;
746
747 x_return_status := FND_API.G_RET_STS_SUCCESS;
748
749 -- Check the cells if the p_cell_id is null then refresh all the cells
750 -- Else refresh only the given cell
751 IF p_cell_id IS NOT NULL
752 THEN
753 IF (AMS_DEBUG_HIGH_ON) THEN
754
755 AMS_Utility_PVT.Debug_Message(l_api_name||': Refresh party segment for cell_id ' || p_cell_id);
756 END IF;
757 Load_Party_Seg_One
758 (
759 p_cell_id => l_cell_id,
760 x_return_status => x_return_status,
761 x_msg_count => x_msg_count,
762 x_msg_data => x_msg_data
763 );
764
765 IF x_return_status = FND_API.g_ret_sts_error THEN
766 x_return_status := FND_API.g_ret_sts_error ;
767 FND_MSG_PUB.count_and_get (
768 p_encoded => FND_API.g_false,
769 p_count => x_msg_count,
770 p_data => x_msg_data
771 );
772 failed_flag := 'Y';
773 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
774 x_return_status := FND_API.g_ret_sts_unexp_error ;
775 FND_MSG_PUB.count_and_get (
776 p_encoded => FND_API.g_false,
777 p_count => x_msg_count,
778 p_data => x_msg_data
779 );
780 failed_flag := 'Y';
781 END IF;
782 ELSE
783 -- Get all the cells
784 OPEN c_all_cell_rec;
785 LOOP -- the loop for all CELL_IDs
786 FETCH c_all_cell_rec INTO l_cell_id, l_cell_name;
787 EXIT WHEN c_all_cell_rec%NOTFOUND;
788
789 IF (AMS_DEBUG_HIGH_ON) THEN
790
791
792
793 AMS_Utility_PVT.Debug_Message(l_api_name||': Refresh party segment for cell_id ' || l_cell_id);
794
795 END IF;
796 Load_Party_Seg_One
797 (
798 p_cell_id => l_cell_id,
799 x_return_status => x_return_status,
800 x_msg_count => x_msg_count,
801 x_msg_data => x_msg_data
802 );
803
804 IF x_return_status = FND_API.g_ret_sts_error THEN
805 x_return_status := FND_API.g_ret_sts_error ;
806 FND_MSG_PUB.count_and_get (
807 p_encoded => FND_API.g_false,
808 p_count => x_msg_count,
809 p_data => x_msg_data
810 );
811 failed_flag := 'Y';
812 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
813 x_return_status := FND_API.g_ret_sts_unexp_error ;
814 FND_MSG_PUB.count_and_get (
815 p_encoded => FND_API.g_false,
816 p_count => x_msg_count,
817 p_data => x_msg_data
818 );
819 failed_flag := 'Y';
820 END IF;
821 END LOOP; -- end: the loop for all CELL_IDs
822
823 CLOSE c_all_cell_rec;
824
825 IF failed_flag = 'Y' THEN
826 x_return_status := FND_API.g_ret_sts_unexp_error ;
827 END IF;
828 END IF;
829
830 END Load_Party_Seg;
831
832 /*****************************************************************************/
833 -- Procedure
834 -- Refresh_Party_Segment
835 --
836 -- Purpose
837 -- This procedure is created to as a concurrent program which
838 -- will call the load_party_mkt_seg and will return errors if any
839 --
840 -- Notes
841 --
842 --
843 -- History
844 -- 01/26/2001 yxliu created
845 ------------------------------------------------------------------------------
846
847 PROCEDURE Refresh_Party_Segment
848 ( errbuf OUT NOCOPY VARCHAR2,
849 retcode OUT NOCOPY NUMBER,
850 p_cell_id IN NUMBER --DEFAULT NULL
851 )
852 IS
853 l_return_status VARCHAR2(1) ;
854 l_msg_count NUMBER ;
855 l_msg_data VARCHAR2(2000);
856 BEGIN
857 FND_MSG_PUB.initialize;
858 -- Call the procedure to refresh Segment
859 Load_Party_Seg
860 ( p_cell_id => p_cell_id,
861 x_return_status => l_return_status,
862 x_msg_count => l_msg_count,
863 x_msg_data => l_msg_data);
864
865 -- Write_log ;
866 Ams_Utility_Pvt.Write_Conc_log ;
867 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
868 retcode :=0;
869 ELSE
870 retcode :=1;
871 errbuf := l_msg_data ;
872 END IF;
873 END Refresh_Party_Segment ;
874
875 END AMS_Party_Seg_Loader_PVT;