[Home] [Help]
PACKAGE BODY: APPS.ASP_ALERTS_PUB
Source
1 PACKAGE BODY ASP_ALERTS_PUB as
2 /* $Header: asppaltb.pls 120.6 2005/10/10 17:17 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_ALERTS_PUB
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Public package for Sales Alerts Related Business logic.
8 --
9 -- Procedures:
10 -- (see the specification for details)
11 --
12 -- History:
13 -- 08-Aug-2005 axavier created.
14 ---------------------------------------------------------------------------
15
16 /*-------------------------------------------------------------------------*
17 | Private Constants
18 *-------------------------------------------------------------------------*/
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_ALERTS_PUB';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asppaltb.pls';
21
22 G_MAX_FETCHES CONSTANT NUMBER := 10000;
23
24
25 /*-------------------------------------------------------------------------*
26 | Private Datatypes
27 *-------------------------------------------------------------------------*/
28
29 /*-------------------------------------------------------------------------*
30 | Private Variables
31 *-------------------------------------------------------------------------*/
32
33 /*-------------------------------------------------------------------------*
34 | Private Routines Specification
35 *-------------------------------------------------------------------------*/
36
37 /*-------------------------------------------------------------------------*
38 | Public Routines
39 *-------------------------------------------------------------------------*/
40
41 --------------------------------------------------------------------------------
42 -- Procedure: Get_Matching_Subscriptions
43 -- This method returns all the subscribers of a given Alert.
44 --
45 --------------------------------------------------------------------------------
46
47 PROCEDURE Get_Matching_Subscriptions (
48 p_api_version_number IN NUMBER,
49 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
50 p_alert_code IN VARCHAR2,
51 p_customer_id IN NUMBER,
52 x_subscriber_list OUT NOCOPY SUBSCRIBER_TBL_TYPE,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2)
56 IS
57 l_errbuf VARCHAR2(4000);
58 l_errcode VARCHAR2(30);
59 l_debug_module VARCHAR2(100);
60 l_debug_level number;
61 l_debug_proc_level number;
62 l_debug_unexp_level number;
63
64 cursor get_subs(c_alert_code in VARCHAR2) is
65 select
66 sub.subscription_id,
67 sub.subscriber_name,
68 sub.delivery_channel,
69 sub.user_id
70 from
71 asp_alert_subscriptions sub
72 where sub.alert_code = c_alert_code;
73
74 cursor get_subs_secured(c_alert_code in VARCHAR2, c_customer_id in NUMBER) is
75 select
76 sub.subscription_id,
77 sub.subscriber_name,
78 sub.delivery_channel,
79 sub.user_id
80 from
81 asp_alert_subscriptions sub,
82 (select salesforce_id
83 from as_accesses_all
84 where customer_id = c_customer_id
85 and lead_id is null
86 and sales_lead_id is null
87 group by salesforce_id) acc
88 where sub.alert_code = c_alert_code
89 and sub.resource_id = acc.salesforce_id;
90
91
92 BEGIN
93 l_debug_module := 'asp.plsql.ASP_ALERTS_PUB.Get_Matching_Subscriptions.Begin';
94 l_debug_level :=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
95 l_debug_proc_level := FND_LOG.LEVEL_PROCEDURE;
96 l_debug_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
97 if(l_debug_proc_level >= l_debug_level) then
98 fnd_log.string(l_debug_proc_level, l_debug_module, 'Entering Get_Matching_Subscriptions' );
99 end if;
100
101
102 -- Initialize message list IF p_init_msg_list is set to TRUE.
103 IF FND_API.to_Boolean(p_init_msg_list)
104 THEN
105 FND_MSG_PUB.initialize;
106 END IF;
107
108 -- Initialize API return status to SUCCESS
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 OPEN get_subs_secured(p_alert_code, p_customer_id);
112 FETCH get_subs_secured BULK COLLECT INTO
113 x_subscriber_list
114 LIMIT G_MAX_FETCHES;
115 CLOSE get_subs_secured;
116
117 if(l_debug_proc_level >= l_debug_level) then
118 fnd_log.string(l_debug_proc_level, l_debug_module, 'After opening Cursor get_subs_secured' );
119 end if;
120
121 -- Standard call to get message count and IF count is 1, get message info.
122 FND_MSG_PUB.Count_And_Get(
123 p_count => x_msg_count,
124 p_data => x_msg_data );
125
126 l_debug_module := 'asp.plsql.ASP_ALERTS_PUB.Get_Matching_Subscriptions.End';
127 if(l_debug_proc_level >= l_debug_level) then
128 fnd_log.string(l_debug_proc_level, l_debug_module, 'End Get_Matching_Subscriptions' );
129 end if;
130
131
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134 l_debug_module := 'asp.plsql.ASP_ALERTS_PUB.Get_Matching_Subscriptions.NO_DATA_FOUND';
135 if(l_debug_proc_level >= l_debug_level) then
136 fnd_log.string(l_debug_proc_level, l_debug_module, 'Exception: NO_DATA_FOUND' );
137 end if;
138 if get_subs_secured%ISOPEN then CLOSE get_subs_secured; end if;
139 FND_MSG_PUB.Count_And_Get(
140 p_count => x_msg_count,
141 p_data => x_msg_data );
142 WHEN OTHERS THEN
143 --Print_Debug('Exception: others in ASP_ALERTS_PUB::Get_Matching_Subscriptions');
144 --Print_Debug();
145
146 l_errbuf := SQLERRM;
147 l_errcode := to_char(SQLCODE);
148
149 l_debug_module := 'asp.plsql.ASP_ALERTS_PUB.Get_Matching_Subscriptions.OTHERS';
150 if(l_debug_unexp_level >= l_debug_level) then
151 fnd_log.string(l_debug_unexp_level, l_debug_module, 'Exception: OTHERS - '||
152 'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM
153 );
154 end if;
155
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 if get_subs_secured%ISOPEN then CLOSE get_subs_secured; end if;
158 FND_MSG_PUB.Count_And_Get(
159 p_count => x_msg_count,
160 p_data => x_msg_data );
161
162 END Get_Matching_Subscriptions;
163
164
165 End ASP_ALERTS_PUB;