[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.3 2011/05/18 17:08:31 harchand ship $ */
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(v.contract_expiration_date, trunc(sysdate))),
73 1, 'N', 'Y') AS expired_flag,
74 v.notify_contact_role_id
75 FROM okc_rep_contract_vers v ,okc_rep_contracts_all v2
76 WHERE v.contract_status_code = 'SIGNED'
77 AND v2.contract_id = v.contract_id
78 AND v2.contract_status_code NOT IN('SIGNED','TERMINATED','APPROVED')
79 AND v.expire_ntf_flag = 'Y'
80 AND v.contract_expiration_date <= trunc(sysdate) + v.expire_ntf_period
81 AND v.wf_exp_ntf_item_key IS NULL
82 AND v.contract_version_num = (
83 SELECT DISTINCT MAX(v1.contract_version_num)
84 OVER (PARTITION BY v1.contract_id)
85 FROM okc_rep_contract_vers v1
86 WHERE v1.contract_id = v.contract_id)
87 AND NOT EXISTS(
88 SELECT 1
89 FROM okc_rep_contracts_all c1
90 WHERE c1.contract_id = v.contract_id
91 AND c1.contract_status_code = 'SIGNED'
92 AND c1.expire_ntf_flag = 'Y'
93 AND c1.contract_expiration_date <= TRUNC(SYSDATE) + c1.expire_ntf_period
94 AND c1.wf_exp_ntf_item_key IS NULL
95 );
96 BEGIN
97 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
98 FND_LOG.STRING(
99 FND_LOG.LEVEL_PROCEDURE,
100 G_MODULE||l_api_name,
101 'Entering OKC_REP_EXP_NTF_PVT.contract_expiration_notifier');
102 END IF;
103
104 l_api_name := 'contract_expiration_notifier';
105 l_api_version := 1.0;
106
107 -- Standard call to check for call compatibility.
108 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END IF;
111 -- Initialize message list if p_init_msg_list is set to TRUE.
112 IF FND_API.to_Boolean( p_init_msg_list ) THEN
113 FND_MSG_PUB.initialize;
114 END IF;
115
116 FOR expiring_contracts_rec IN EXPIRING_CONTRACTS LOOP
117 OKC_REP_CONTRACT_PROCESS_PVT.repository_notifier(
118 p_contract_id => expiring_contracts_rec.contract_id,
119 p_contract_number => expiring_contracts_rec.contract_number,
120 p_contract_version => expiring_contracts_rec.contract_version_num,
121 p_expired_flag => expiring_contracts_rec.expired_flag,
122 p_notify_contact_role_id => expiring_contracts_rec.notify_contact_role_id,
123 p_api_version => p_api_version,
124 p_init_msg_list => p_init_msg_list,
125 x_msg_data => x_msg_data,
126 x_msg_count => x_msg_count,
127 x_return_status => x_return_status
128 );
129 END LOOP;
130
131 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
132 FND_LOG.STRING(
133 FND_LOG.LEVEL_PROCEDURE,
134 G_MODULE||l_api_name,
135 'Leaving OKC_REP_EXP_NTF_PVT.contract_expiration_notifier');
136 END IF;
137
138 EXCEPTION
139 WHEN FND_API.G_EXC_ERROR THEN
140 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 fnd_log.string(
142 FND_LOG.LEVEL_EXCEPTION,
143 G_MODULE || l_api_name,
144 'Leaving contract_expiration_notifier:FND_API.G_EXC_ERROR Exception');
145 END IF;
146 --close cursors
147 IF (EXPIRING_CONTRACTS%ISOPEN) THEN
148 CLOSE EXPIRING_CONTRACTS ;
149 END IF;
150
151 x_return_status := FND_API.G_RET_STS_ERROR;
152 FND_MSG_PUB.Count_And_Get(
153 p_count => x_msg_count,
154 p_data => x_msg_data
155 );
156
157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
158 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
159 fnd_log.string(
160 FND_LOG.LEVEL_EXCEPTION,
161 G_MODULE || l_api_name,
162 'Leaving contract_expiration_notifier:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
163 END IF;
164 --close cursors
165 IF (EXPIRING_CONTRACTS%ISOPEN) THEN
166 CLOSE EXPIRING_CONTRACTS ;
167 END IF;
168
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 FND_MSG_PUB.Count_And_Get(
171 p_count => x_msg_count,
172 p_data => x_msg_data
173 );
174
175 WHEN OTHERS THEN
176 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
177 fnd_log.string(
178 FND_LOG.LEVEL_EXCEPTION,
179 G_MODULE || l_api_name,
180 'Leaving contract_expiration_notifier because of EXCEPTION: ' || sqlerrm);
181 END IF;
182 Okc_Api.Set_Message(
183 p_app_name => G_APP_NAME,
184 p_msg_name => G_UNEXPECTED_ERROR,
185 p_token1 => G_SQLCODE_TOKEN,
186 p_token1_value => SQLCODE,
187 p_token2 => G_SQLERRM_TOKEN,
188 p_token2_value => SQLERRM);
189
190 --close cursors
191 IF (EXPIRING_CONTRACTS%ISOPEN) THEN
192 CLOSE EXPIRING_CONTRACTS ;
193 END IF;
194
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 FND_MSG_PUB.Count_And_Get(
197 p_count => x_msg_count,
198 p_data => x_msg_data
199 );
200
201 END contract_expiration_notifier;
202
203 -- Start of comments
204 --API name : contract_expiration_manager
205 --Type : Private.
206 --Function : Called from Concurrent Manager to send
207 -- notifications to contract contacts for
208 -- contracts that are about to expire
209 --Pre-reqs : None.
210 --Parameters :
211 --OUT : errbuf OUT NOCOPY VARCHAR2
212 -- : retcode OUT NOCOPY VARCHAR2
213 --Note :
214 -- End of comments
215
216 PROCEDURE contract_expiration_manager(
217 errbuf OUT NOCOPY VARCHAR2,
218 retcode OUT NOCOPY VARCHAR2)
219 IS
220 l_api_version NUMBER;
221 l_api_name VARCHAR2(32);
222 l_init_msg_list VARCHAR2(2000);
223 l_msg_data VARCHAR2(2000);
224 l_msg_count NUMBER;
225 l_return_status VARCHAR2(2000);
226 BEGIN
227 retcode := G_RETURN_CODE_ERROR;
228
229 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
230 FND_LOG.STRING(
231 FND_LOG.LEVEL_PROCEDURE,
232 G_MODULE||l_api_name,
233 'Entering OKC_REP_EXP_NTF_PVT.contract_expiration_manager');
234 END IF;
235
236 l_api_name := 'contract_expiration_manager';
237 l_api_version := 1.0;
238 l_init_msg_list := FND_API.G_FALSE;
239
240 contract_expiration_notifier(
241 p_api_version => l_api_version,
242 p_init_msg_list => l_init_msg_list,
243 x_msg_data => l_msg_data,
244 x_msg_count => l_msg_count,
245 x_return_status => l_return_status
246 );
247
248 retcode := G_RETURN_CODE_SUCCESS;
249
250 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
251 FND_LOG.STRING(
252 FND_LOG.LEVEL_PROCEDURE,
253 G_MODULE||l_api_name,
254 'Leaving OKC_REP_EXP_NTF_PVT.contract_expiration_manager');
255 END IF;
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
260 fnd_log.string(
261 FND_LOG.LEVEL_EXCEPTION,
262 G_MODULE || l_api_name,
263 'Leaving contract_expiration_manager because of EXCEPTION: ' || SQLERRM);
264 errbuf := substr(SQLERRM, 1, 200);
265 END IF;
266
267 END contract_expiration_manager;
268
269 END OKC_REP_EXP_NTF_PVT;