1 PACKAGE BODY IEO_SUM_AVB_RECORD AS
2 /* $Header: IEOSRECB.pls 115.10 2003/01/02 17:08:26 dolee ship $ */
3
4 ------------------------------------------------------------------------------
5 -- Function : TOTAL_RECORD_FOR_IEO_CAMP
6 ------------------------------------------------------------------------------
7 function TOTAL_RECORD_FOR_IEO_CAMP(LIST_SRV_NAME_Value IN VARCHAR2)
8 RETURN NUMBER
9 IS
10 ieototal NUMBER(15) := 0;
11 BEGIN
12 select sum(A.REC_AVAILABLE) into ieototal
13 from IEO_CP_LIST_STATS_ALL A, IEO_CP_SVC_LISTS_ALL B
14 where A.LIST_SRV_NAME = LIST_SRV_NAME_Value
15 and A.LIST_SRV_NAME = B.LIST_SRV_NAME
16 and A.LIST_NAME = B.LIST_NAME
17 and B.LIST_STATUS ='O';
18 if(ieototal is NULL) then return 0;
19 else return(ieototal);
20 end if;
21 END TOTAL_RECORD_FOR_IEO_CAMP;
22
23 ------------------------------------------------------------------------------
24 -- Function : TOTAL_RECORD_FOR_AMS_CAMP
25 ------------------------------------------------------------------------------
26
27 function TOTAL_RECORD_FOR_AMS_CAMP(CAMPAIGN_ID_Value IN NUMBER)
28 RETURN NUMBER
29 IS
30 amstotal NUMBER(15):= 0;
31 CURSOR c_srvname IS
32 select LIST_SRV_NAME from IEO_CP_SERVICES_ALL
33 where CAMPAIGN_LIST_ID in
34 (select DISTINCT(A.LIST_HEADER_ID)
35 from AMS_LIST_HEADERS_ALL A,
36 AMS_CAMPAIGN_SCHEDULES B
37 where A.LIST_USED_BY_ID = B.CAMPAIGN_SCHEDULE_ID
38 and B.CAMPAIGN_ID = CAMPAIGN_ID_Value);
39
40 BEGIN
41 FOR v_srvname IN c_srvname LOOP
42 amstotal := amstotal + TOTAL_RECORD_FOR_IEO_CAMP(v_srvname.LIST_SRV_NAME);
43 END LOOP;
44 return(amstotal);
45 END TOTAL_RECORD_FOR_AMS_CAMP;
46
47 END IEO_SUM_AVB_RECORD;
[Home] [Help]