[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_EXP_NTF_PVT
Source
1 PACKAGE BODY OKC_REP_EXP_NTF_PVT AS
2 /* $Header: OKCVREPEXNTFB.pls 120.1 2006/05/15 20:42:45 vamuru noship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ---------------------------------------------------------------------------
7 G_PARTY_TYPE_INTERNAL CONSTANT VARCHAR2(12) := 'INTERNAL_ORG';
8 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_REP_EXP_NTF_PVT';
9 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKC';
10 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
11
12 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
13 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
14 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
15
16 G_RETURN_CODE_SUCCESS CONSTANT NUMBER := 0;
17 G_RETURN_CODE_WARNING CONSTANT NUMBER := 1;
18 G_RETURN_CODE_ERROR CONSTANT NUMBER := 2;
19
20 ---------------------------------------------------------------------------
21 -- START: Procedures and Functions
22 ---------------------------------------------------------------------------
23
24 -- Start of comments
25 --API name : contract_expiration_notifier
26 --Type : Private.
27 --Function : Iterates through contracts that are about to expire
28 -- and calls repository_notifier() procedure
29 -- for all of them. This procedure will send notifications
30 -- to all contract contacts.
31 --Pre-reqs : None.
32 --Parameters :
33 --IN : p_api_version IN NUMBER Required
34 -- : p_init_msg_list IN VARCHAR2 Optional
35 -- Default = FND_API.G_FALSE
36 --OUT : x_return_status OUT VARCHAR2(1)
37 -- : x_msg_count OUT NUMBER
38 -- : x_msg_data OUT VARCHAR2(2000)
39 --Note :
40 -- End of comments
41 PROCEDURE contract_expiration_notifier(
42 p_api_version IN NUMBER,
43 p_init_msg_list IN VARCHAR2,
44 x_msg_data OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2)
47
48 IS
49
50 l_api_version NUMBER;
51 l_api_name VARCHAR2(32);
52
53 CURSOR EXPIRING_CONTRACTS IS
54 SELECT
55 contract_id,
56 contract_number,
57 contract_version_num,
58 DECODE(SIGN(MONTHS_BETWEEN(contract_expiration_date, trunc(sysdate))),
59 1, 'N', 'Y') AS expired_flag,
60 notify_contact_role_id
61 FROM okc_rep_contracts_all
62 WHERE contract_expiration_date is not null
63 AND contract_status_code = 'SIGNED'
64 AND expire_ntf_flag = 'Y'
65 AND contract_expiration_date <= trunc(sysdate) + expire_ntf_period
66 AND wf_exp_ntf_item_key IS NULL
67 UNION ALL
68 SELECT
69 v.contract_id,
70 v.contract_number,
71 v.contract_version_num,
72 DECODE(SIGN(MONTHS_BETWEEN(contract_expiration_date, trunc(sysdate))),
73 1, 'N', 'Y') AS expired_flag,
74 notify_contact_role_id
75 FROM okc_rep_contract_vers v
76 WHERE v.contract_status_code = 'SIGNED'
77 AND v.expire_ntf_flag = 'Y'
78 AND v.contract_expiration_date <= trunc(sysdate) + v.expire_ntf_period
79 AND v.wf_exp_ntf_item_key IS NULL
80 AND v.contract_version_num = (
81 SELECT DISTINCT MAX(v1.contract_version_num)
82 OVER (PARTITION BY v1.contract_id)
83 FROM okc_rep_contract_vers v1
84 WHERE v1.contract_id = v.contract_id)
85 AND NOT EXISTS(
86 SELECT 1
87 FROM okc_rep_contracts_all c1
88 WHERE c1.contract_id = v.contract_id
89 AND c1.contract_status_code = 'SIGNED'
90 AND c1.expire_ntf_flag = 'Y'
91 AND c1.contract_expiration_date <= TRUNC(SYSDATE) + c1.expire_ntf_period
92 AND c1.wf_exp_ntf_item_key IS NULL
93 );
94 BEGIN
95 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
96 FND_LOG.STRING(
97 FND_LOG.LEVEL_PROCEDURE,
98 G_MODULE||l_api_name,
99 'Entering OKC_REP_EXP_NTF_PVT.contract_expiration_notifier');
100 END IF;
101
102 l_api_name := 'contract_expiration_notifier';
103 l_api_version := 1.0;
104
105 -- Standard call to check for call compatibility.
106 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109 -- Initialize message list if p_init_msg_list is set to TRUE.
110 IF FND_API.to_Boolean( p_init_msg_list ) THEN
111 FND_MSG_PUB.initialize;
112 END IF;
113
114 FOR expiring_contracts_rec IN EXPIRING_CONTRACTS LOOP
115 OKC_REP_CONTRACT_PROCESS_PVT.repository_notifier(
116 p_contract_id => expiring_contracts_rec.contract_id,
117 p_contract_number => expiring_contracts_rec.contract_number,
118 p_contract_version => expiring_contracts_rec.contract_version_num,
119 p_expired_flag => expiring_contracts_rec.expired_flag,
120 p_notify_contact_role_id => expiring_contracts_rec.notify_contact_role_id,
121 p_api_version => p_api_version,
122 p_init_msg_list => p_init_msg_list,
123 x_msg_data => x_msg_data,
124 x_msg_count => x_msg_count,
125 x_return_status => x_return_status
126 );
127 END LOOP;
128
129 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
130 FND_LOG.STRING(
131 FND_LOG.LEVEL_PROCEDURE,
132 G_MODULE||l_api_name,
133 'Leaving OKC_REP_EXP_NTF_PVT.contract_expiration_notifier');
134 END IF;
135
136 EXCEPTION
137 WHEN FND_API.G_EXC_ERROR THEN
138 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
139 fnd_log.string(
140 FND_LOG.LEVEL_EXCEPTION,
141 G_MODULE || l_api_name,
142 'Leaving contract_expiration_notifier:FND_API.G_EXC_ERROR Exception');
143 END IF;
144 --close cursors
145 IF (EXPIRING_CONTRACTS%ISOPEN) THEN
146 CLOSE EXPIRING_CONTRACTS ;
147 END IF;
148
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 FND_MSG_PUB.Count_And_Get(
151 p_count => x_msg_count,
152 p_data => x_msg_data
153 );
154
155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
156 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
157 fnd_log.string(
158 FND_LOG.LEVEL_EXCEPTION,
159 G_MODULE || l_api_name,
160 'Leaving contract_expiration_notifier:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
161 END IF;
162 --close cursors
163 IF (EXPIRING_CONTRACTS%ISOPEN) THEN
164 CLOSE EXPIRING_CONTRACTS ;
165 END IF;
166
167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
168 FND_MSG_PUB.Count_And_Get(
169 p_count => x_msg_count,
170 p_data => x_msg_data
171 );
172
173 WHEN OTHERS THEN
174 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
175 fnd_log.string(
176 FND_LOG.LEVEL_EXCEPTION,
177 G_MODULE || l_api_name,
178 'Leaving contract_expiration_notifier because of EXCEPTION: ' || sqlerrm);
179 END IF;
180 Okc_Api.Set_Message(
181 p_app_name => G_APP_NAME,
182 p_msg_name => G_UNEXPECTED_ERROR,
183 p_token1 => G_SQLCODE_TOKEN,
184 p_token1_value => SQLCODE,
185 p_token2 => G_SQLERRM_TOKEN,
186 p_token2_value => SQLERRM);
187
188 --close cursors
189 IF (EXPIRING_CONTRACTS%ISOPEN) THEN
190 CLOSE EXPIRING_CONTRACTS ;
191 END IF;
192
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194 FND_MSG_PUB.Count_And_Get(
195 p_count => x_msg_count,
196 p_data => x_msg_data
197 );
198
199 END contract_expiration_notifier;
200
201 -- Start of comments
202 --API name : contract_expiration_manager
203 --Type : Private.
204 --Function : Called from Concurrent Manager to send
205 -- notifications to contract contacts for
206 -- contracts that are about to expire
207 --Pre-reqs : None.
208 --Parameters :
209 --OUT : errbuf OUT NOCOPY VARCHAR2
210 -- : retcode OUT NOCOPY VARCHAR2
211 --Note :
212 -- End of comments
213
214 PROCEDURE contract_expiration_manager(
215 errbuf OUT NOCOPY VARCHAR2,
216 retcode OUT NOCOPY VARCHAR2)
217 IS
218 l_api_version NUMBER;
219 l_api_name VARCHAR2(32);
220 l_init_msg_list VARCHAR2(2000);
221 l_msg_data VARCHAR2(2000);
222 l_msg_count NUMBER;
223 l_return_status VARCHAR2(2000);
224 BEGIN
225 retcode := G_RETURN_CODE_ERROR;
226
227 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
228 FND_LOG.STRING(
229 FND_LOG.LEVEL_PROCEDURE,
230 G_MODULE||l_api_name,
231 'Entering OKC_REP_EXP_NTF_PVT.contract_expiration_manager');
232 END IF;
233
234 l_api_name := 'contract_expiration_manager';
235 l_api_version := 1.0;
236 l_init_msg_list := FND_API.G_FALSE;
237
238 contract_expiration_notifier(
239 p_api_version => l_api_version,
240 p_init_msg_list => l_init_msg_list,
241 x_msg_data => l_msg_data,
242 x_msg_count => l_msg_count,
243 x_return_status => l_return_status
244 );
245
246 retcode := G_RETURN_CODE_SUCCESS;
247
248 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249 FND_LOG.STRING(
250 FND_LOG.LEVEL_PROCEDURE,
251 G_MODULE||l_api_name,
252 'Leaving OKC_REP_EXP_NTF_PVT.contract_expiration_manager');
253 END IF;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
258 fnd_log.string(
259 FND_LOG.LEVEL_EXCEPTION,
260 G_MODULE || l_api_name,
261 'Leaving contract_expiration_manager because of EXCEPTION: ' || SQLERRM);
262 errbuf := substr(SQLERRM, 1, 200);
263 END IF;
264
265 END contract_expiration_manager;
266
267 END OKC_REP_EXP_NTF_PVT;