eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OKC_REP_STATUS_UPDATE_PVT

Source


1 PACKAGE BODY OKC_REP_STATUS_UPDATE_PVT AS
2 /* $Header: OKCVREPSTATCHB.pls 120.1 2006/03/31 11:34:00 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_CONTRACT_SEARCH_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 VARCHAR2(1) :='0';
17   G_RETURN_CODE_WARNING     CONSTANT VARCHAR2(1) := '1';
18   G_RETURN_CODE_ERROR       CONSTANT VARCHAR2(1) := '2';
19 
20   -- Contract status codes
21   G_REP_CON_STATUS_SIGNED CONSTANT VARCHAR2(200) := 'SIGNED';
22   G_REP_CON_STATUS_TERMINATED CONSTANT VARCHAR2(200) := 'TERMINATED';
23 
24   ---------------------------------------------------------------------------
25   -- START: Procedures and Functions
26   ---------------------------------------------------------------------------
27 
28 -- Start of comments
29 --API name      : contract_status_updater
30 --Type          : Private.
31 --Function      : Updates status for contracts
32 --                reaching their termination date
33 --Pre-reqs      : None.
34 --Parameters    :
35 --IN            : p_api_version         IN NUMBER       Required
36 --              : p_init_msg_list       IN VARCHAR2     Required
37 --              : p_status              IN VARCHAR2     Required
38 --OUT           : x_return_status       OUT  VARCHAR2(1)
39 --              : x_msg_count           OUT  NUMBER
40 --              : x_msg_data            OUT  VARCHAR2(2000)
41 --Note          :
42 -- End of comments
43 PROCEDURE contract_status_updater(
44   p_api_version   IN          NUMBER,
45   p_init_msg_list IN          VARCHAR2,
46   p_status        IN          VARCHAR2,
47   x_msg_data      OUT NOCOPY  VARCHAR2,
48   x_msg_count     OUT NOCOPY  NUMBER,
49   x_return_status OUT NOCOPY  VARCHAR2)
50 
51   IS
52 
53     l_api_version NUMBER;
54     l_api_name    VARCHAR2(32);
55 
56     CURSOR contracts_cur IS
57     SELECT
58       contract_id, contract_version_num,
59       contract_number,
60       contract_name
61     FROM  okc_rep_contracts_all
62     WHERE termination_date is not null
63     AND   contract_status_code = G_REP_CON_STATUS_SIGNED
64     AND   trunc(termination_date) <= TRUNC(SYSDATE);
65 
66     CURSOR contract_vers_cur IS
67     SELECT
68       contract_id, contract_version_num,
69       contract_number,
70       contract_name
71     FROM  okc_rep_contract_vers v
72     WHERE termination_date IS NOT NULL
73     AND   contract_status_code = G_REP_CON_STATUS_SIGNED
74     AND   trunc(termination_date) <= trunc(SYSDATE);
75 
76     TYPE selected_contracts_tbl IS TABLE OF contracts_cur%ROWTYPE;
77     TYPE selected_vers_contracts_tbl IS TABLE OF contract_vers_cur%ROWTYPE;
78     TYPE NumList IS TABLE OF okc_rep_contracts_all.contract_id%TYPE NOT NULL
79           INDEX BY PLS_INTEGER;
80     TYPE VersionNumList IS TABLE OF okc_rep_contracts_all.contract_version_num%TYPE NOT NULL
81           INDEX BY PLS_INTEGER;
82 
83     selected_contracts selected_contracts_tbl;
84     selected_vers_contracts selected_vers_contracts_tbl;
85     selected_contract_ids NumList;
86     selected_vers_contract_ids NumList;
87     selected_vers_contract_ver VersionNumList;
88 
89     l_batch_size number(4) := 1000;
90     l_first_iteration VARCHAR2(1);
91     l_count number;
92 
93 
94   BEGIN
95     FND_FILE.PUT_LINE(FND_FILE.LOG, '***** BEGIN contract_status_updater *****');
96 
97     FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_status = ' || p_status);
98 
99     l_api_name    := 'contract_status_updater';
100     l_api_version := 1.0;
101     l_first_iteration := 'Y';
102     l_count := 0;
103 
104     -- Standard call to check for call compatibility.
105     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
106       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107     END IF;
108 
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     -- Update the status of the terminated contracts in the active contracts table
115     OPEN contracts_cur;
116     LOOP -- the following statement fetches 1000 rows or less in each iteration
117 
118       FETCH contracts_cur BULK COLLECT INTO selected_contracts
119       LIMIT l_batch_size;
120 
121       EXIT WHEN selected_contracts.COUNT = 0;
122 
123       -- Show the text Contract Details only once
124       IF (l_first_iteration = 'Y') THEN
125 
126         l_first_iteration := 'N';
127 
128         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
129         FND_FILE.PUT_LINE(FND_FILE.LOG, '');
130         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
131         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '================');
132         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
133         FND_FILE.PUT_LINE(FND_FILE.LOG, '================');
134 
135       END IF;
136 
137 
138       FOR i IN 1..NVL(selected_contracts.LAST, -1) LOOP
139         l_count := l_count + 1;
140 
141         -- Populate the current contract details into concurrent output and log files
142 
143         -- Add Contract Name
144         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_ATTR_CON_NAME') || '               : '|| selected_contracts(i).contract_name);
145         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_ATTR_CON_NAME') || '               : '|| selected_contracts(i).contract_name);
146 
147         -- Add Contract Number
148         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_NUMBER') || '             : '|| selected_contracts(i).contract_number);
149         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_NUMBER') || '             : '|| selected_contracts(i).contract_number);
150 
151         -- Add Contract Version Number
152 	FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_VER_NUM') || '            : '|| selected_contracts(i).contract_version_num);
153         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_VER_NUM') || '            : '|| selected_contracts(i).contract_version_num);
154 
155         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
156         FND_FILE.PUT_LINE(FND_FILE.LOG, '');
157 
158         -- Prepare a number array of contract ids, this is required by the UPDATE
159         -- statement under FORALL as it will not take selected_contracts(i).contract_id in the WHERE clause
160         -- Getting the following compilation error
161         -- PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
162         selected_contract_ids(i) := selected_contracts(i).contract_id;
163 
164       END LOOP;
165 
166       FORALL j IN NVL(selected_contract_ids.FIRST,0)..NVL(selected_contract_ids.LAST,-1)
167         UPDATE okc_rep_contracts_all
168         SET    contract_status_code = p_status,
169                last_update_date = sysdate,
170                last_updated_by = Fnd_Global.User_Id,
171                last_update_login = Fnd_Global.Login_Id
172         WHERE  contract_id = selected_contract_ids(j);
173 
174     END LOOP;
175 
176     IF contracts_cur%ISOPEN THEN
177       CLOSE contracts_cur ;
178     END IF;
179 
180     -- Update the status of the terminated contracts in the archived contracts table
181     OPEN contract_vers_cur;
182     LOOP -- the following statement fetches 1000 rows or less in each iteration
183 
184       FETCH contract_vers_cur BULK COLLECT INTO selected_vers_contracts
185       LIMIT l_batch_size;
186 
187       EXIT WHEN selected_vers_contracts.COUNT = 0;
188 
189       -- Show the text Contract Details only once
190       IF (l_first_iteration = 'Y') THEN
191 
192         l_first_iteration := 'N';
193 
194         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
195         FND_FILE.PUT_LINE(FND_FILE.LOG, '');
196         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
197         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '================');
198         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
199         FND_FILE.PUT_LINE(FND_FILE.LOG, '================');
200 
201       END IF;
202 
203       FOR i IN 1..NVL(selected_vers_contracts.LAST, -1) LOOP
204 
205         l_count := l_count + 1;
206 
207         -- Populate the current contract details into concurrent output and log files
208 
209         -- Add Contract Name
210         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_ATTR_CON_NAME') || '               : '|| selected_vers_contracts(i).contract_name);
211         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_ATTR_CON_NAME') || '               : '|| selected_vers_contracts(i).contract_name);
212 
213         -- Add Contract Number
214         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_NUMBER') || '             : '|| selected_vers_contracts(i).contract_number);
215         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_NUMBER') || '             : '|| selected_vers_contracts(i).contract_number);
216 
217         -- Add Contract Version Number
218 	FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_VER_NUM') || '            : '|| selected_vers_contracts(i).contract_version_num);
219         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_VER_NUM') || '            : '|| selected_vers_contracts(i).contract_version_num);
220 
221         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
222         FND_FILE.PUT_LINE(FND_FILE.LOG, '');
223 
224         -- Prepare a number array of contract ids and version numbers, this is required by the UPDATE
225         -- statement under FORALL as it will not take selected_vers_contracts(i).contract_id in the WHERE clause
226         -- Getting the following compilation error
227         -- PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
228         selected_vers_contract_ids(i) := selected_vers_contracts(i).contract_id;
229         selected_vers_contract_ver(i) := selected_vers_contracts(i).contract_version_num;
230 
231       END LOOP;
232 
233       FORALL j IN NVL(selected_vers_contract_ids.FIRST,0)..NVL(selected_vers_contract_ids.LAST,-1)
234         UPDATE okc_rep_contract_vers
235         SET    contract_status_code = p_status,
236                last_update_date = sysdate,
237                last_updated_by = Fnd_Global.User_Id,
238                last_update_login = Fnd_Global.Login_Id
239         WHERE  contract_id = selected_vers_contract_ids(j)
240         AND    contract_version_num = selected_vers_contract_ver(j);
241 
242     END LOOP;
243 
244     IF contract_vers_cur%ISOPEN THEN
245       CLOSE contract_vers_cur ;
246     END IF;
247 
248     FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_UPD_STS_SUMMARY') || ' : ' || l_count);
249     FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_UPD_STS_SUMMARY') || ' : ' || l_count);
250 
251     COMMIT;
252 
253   EXCEPTION
254     WHEN OTHERS THEN
255 
256       FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_UPD_STS_ERROR'));
257       FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_UPD_STS_ERROR'));
258 
259       FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_UPD_STS_SYS_ERR') || ' ' || sqlerrm);
260       FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_UPD_STS_SYS_ERR') || ' ' || sqlerrm);
261 
262       ROLLBACK;
263 
264       --close cursors
265       IF contracts_cur%ISOPEN THEN
266         CLOSE contracts_cur ;
267       END IF;
268       IF contract_vers_cur%ISOPEN THEN
269         CLOSE contract_vers_cur ;
270       END IF;
271 
272       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 
274       x_msg_count := 1;
275       x_msg_data := substr(SQLERRM, 1, 200);
276 
277       Okc_Api.Set_Message(
278         p_app_name     => G_APP_NAME,
279         p_msg_name     => G_UNEXPECTED_ERROR,
280         p_token1       => G_SQLCODE_TOKEN,
281         p_token1_value => SQLCODE,
282         p_token2       => G_SQLERRM_TOKEN,
283         p_token2_value => SQLERRM);
284 
285       FND_MSG_PUB.Count_And_Get(
286         p_count =>  x_msg_count,
287         p_data  =>  x_msg_data
288       );
289 
290   END contract_status_updater;
291 
292 -- Start of comments
293 --API name      : contract_status_update_manager
294 --Type          : Private.
295 --Function      : Called from Concurrent Manager to update
296 --                status for contract reaching their
297 --                termination date
298 --Pre-reqs      : None.
299 --Parameters    :
300 --IN            : p_status IN VARCHAR2 Required
301 --OUT           : errbuf  OUT NOCOPY VARCHAR2
302 --              : retcode OUT NOCOPY VARCHAR2
303 --Note          :
304 -- End of comments
305 
306   PROCEDURE contract_status_update_manager(
307     p_status IN VARCHAR2,
308     errbuf  OUT NOCOPY VARCHAR2,
309     retcode OUT NOCOPY VARCHAR2)
310   IS
311     l_api_version   NUMBER;
312     l_api_name      VARCHAR2(32);
313     l_init_msg_list VARCHAR2(2000);
314     l_msg_data      VARCHAR2(2000);
315     l_msg_count     NUMBER;
316     l_return_status VARCHAR2(2000);
317     l_status        VARCHAR2(32);
318 
319   BEGIN
320 
321     FND_FILE.PUT_LINE(FND_FILE.LOG, '***** BEGIN contract_status_update_manager *****');
322 
323     l_api_name    := 'contract_status_update_manager';
324     l_api_version   := 1.0;
325     l_init_msg_list := FND_API.G_FALSE;
326     l_status := 'NONE';
327 
328     FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_status = (' || p_status || ')');
329     FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_status = (' || l_status || ')');
330 
331     IF p_status IS NULL OR LENGTH(TRIM(p_status)) = 0 THEN
332       l_status := G_REP_CON_STATUS_TERMINATED;
333     END IF;
334 
335     FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_status = (' || l_status || ')');
336 
337     contract_status_updater(
338       p_api_version   => l_api_version,
339       p_init_msg_list => l_init_msg_list,
340       p_status        => l_status,
341       x_msg_data      => l_msg_data,
342       x_msg_count     => l_msg_count,
343       x_return_status => l_return_status
344     );
345 
346     IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
347       retcode := G_RETURN_CODE_ERROR;
348       errbuf := substr(FND_MSG_PUB.Get(), 1, 200);
349     END IF;
350 
351     FND_FILE.PUT_LINE(FND_FILE.LOG, '***** END contract_status_update_manager() *****');
352 
353   EXCEPTION
354     WHEN OTHERS THEN
355       FND_FILE.PUT_LINE(FND_FILE.LOG, '***** EXCEPTION OTHERS*****');
356       FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
357 
358       retcode := G_RETURN_CODE_ERROR;
359       errbuf := substr(SQLERRM, 1, 200);
360 
361   END contract_status_update_manager;
362 
363 END OKC_REP_STATUS_UPDATE_PVT;