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.12020000.3 2013/02/26 06:02:42 kkolukul 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_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     l_doc_type varchar2(30);
94     l_resolved_msg_name VARCHAR2(30);
95     l_resolved_token VARCHAR2(100);
96 
97     l_contract_id number;
98 
99 
100 
101   BEGIN
102     FND_FILE.PUT_LINE(FND_FILE.LOG, '***** BEGIN contract_status_updater *****');
103 
104     FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_status = ' || p_status);
105 
106     l_api_name    := 'contract_status_updater';
107     l_api_version := 1.0;
108     l_first_iteration := 'Y';
109     l_count := 0;
110 
111     -- Standard call to check for call compatibility.
112     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
113       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114     END IF;
115 
116     -- Initialize message list if p_init_msg_list is set to TRUE.
117     IF FND_API.to_Boolean( p_init_msg_list ) THEN
118       FND_MSG_PUB.initialize;
119     END IF;
120 
121     -- Update the status of the terminated contracts in the active contracts table
122     OPEN contracts_cur;
123     LOOP -- the following statement fetches 1000 rows or less in each iteration
124 
125       FETCH contracts_cur BULK COLLECT INTO selected_contracts
126       LIMIT l_batch_size;
127 
128       EXIT WHEN selected_contracts.COUNT = 0;
129 
130       -- Show the text Contract Details only once
131       IF (l_first_iteration = 'Y') THEN
132 
133         l_first_iteration := 'N';
134 
135         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
136         FND_FILE.PUT_LINE(FND_FILE.LOG, '');
137         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
138         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '================');
139         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
140         FND_FILE.PUT_LINE(FND_FILE.LOG, '================');
141 
142       END IF;
143 
144 
145       FOR i IN 1..NVL(selected_contracts.LAST, -1) LOOP
146         l_count := l_count + 1;
147         l_contract_id := selected_contracts(i).contract_id;
148 
149         -- Populate the current contract details into concurrent output and log files
150 
151         -- Add Contract Name
152        l_doc_type := OKC_API.get_contract_type(selected_contracts(i).contract_id);
153        l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_ATTR_CON_NAME',l_doc_type);
154        l_resolved_token := OKC_API.resolve_hdr_token(l_doc_type);
155 
156         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, l_resolved_msg_name,'HDR_TOKEN',l_resolved_token) || '               : '|| selected_contracts(i).contract_name);
157         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, l_resolved_msg_name) || '               : '|| selected_contracts(i).contract_name);
158 
159         -- Add Contract Number
160         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);
161         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);
162 
163         -- Add Contract Version Number
164 	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);
165         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);
166 
167         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
168         FND_FILE.PUT_LINE(FND_FILE.LOG, '');
169 
170         -- Prepare a number array of contract ids, this is required by the UPDATE
171         -- statement under FORALL as it will not take selected_contracts(i).contract_id in the WHERE clause
172         -- Getting the following compilation error
173         -- PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
174         selected_contract_ids(i) := selected_contracts(i).contract_id;
175 
176       END LOOP;
177 
178       FORALL j IN NVL(selected_contract_ids.FIRST,0)..NVL(selected_contract_ids.LAST,-1)
179         UPDATE okc_rep_contracts_all
180         SET    contract_status_code = p_status,
181                last_update_date = sysdate,
182                last_updated_by = Fnd_Global.User_Id,
183                last_update_login = Fnd_Global.Login_Id
184         WHERE  contract_id = selected_contract_ids(j);
185 
186     END LOOP;
187 
188     IF contracts_cur%ISOPEN THEN
189       CLOSE contracts_cur ;
190     END IF;
191 
192     -- Update the status of the terminated contracts in the archived contracts table
193     OPEN contract_vers_cur;
194     LOOP -- the following statement fetches 1000 rows or less in each iteration
195 
196       FETCH contract_vers_cur BULK COLLECT INTO selected_vers_contracts
197       LIMIT l_batch_size;
198 
199       EXIT WHEN selected_vers_contracts.COUNT = 0;
200 
201       -- Show the text Contract Details only once
202       IF (l_first_iteration = 'Y') THEN
203 
204         l_first_iteration := 'N';
205 
206         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
207         FND_FILE.PUT_LINE(FND_FILE.LOG, '');
208         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
209         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '================');
210         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
211         FND_FILE.PUT_LINE(FND_FILE.LOG, '================');
212 
213       END IF;
214 
215       FOR i IN 1..NVL(selected_vers_contracts.LAST, -1) LOOP
216 
217         l_count := l_count + 1;
218         l_contract_id := selected_vers_contracts(i).contract_id;
219 
220         -- Populate the current contract details into concurrent output and log files
221        l_doc_type := OKC_API.get_contract_type(selected_vers_contracts(i).contract_id);
222        l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_ATTR_CON_NAME',l_doc_type);
223        l_resolved_token := OKC_API.resolve_hdr_token(l_doc_type);
224         -- Add Contract Name
225 
226         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, l_resolved_msg_name,'HDR_TOKEN',l_resolved_token) || '               : '|| selected_vers_contracts(i).contract_name);
227         FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, l_resolved_msg_name,'HDR_TOKEN',l_resolved_token) || '               : '|| selected_vers_contracts(i).contract_name);
228 
229         -- Add Contract Number
230         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);
231         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);
232 
233         -- Add Contract Version Number
234 	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);
235         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);
236 
237         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
238         FND_FILE.PUT_LINE(FND_FILE.LOG, '');
239 
240         -- Prepare a number array of contract ids and version numbers, this is required by the UPDATE
241         -- statement under FORALL as it will not take selected_vers_contracts(i).contract_id in the WHERE clause
242         -- Getting the following compilation error
243         -- PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
244         selected_vers_contract_ids(i) := selected_vers_contracts(i).contract_id;
245         selected_vers_contract_ver(i) := selected_vers_contracts(i).contract_version_num;
246 
247       END LOOP;
248 
249       FORALL j IN NVL(selected_vers_contract_ids.FIRST,0)..NVL(selected_vers_contract_ids.LAST,-1)
250         UPDATE okc_rep_contract_vers
251         SET    contract_status_code = p_status,
252                last_update_date = sysdate,
253                last_updated_by = Fnd_Global.User_Id,
254                last_update_login = Fnd_Global.Login_Id
255         WHERE  contract_id = selected_vers_contract_ids(j)
256         AND    contract_version_num = selected_vers_contract_ver(j);
257 
258     END LOOP;
259 
260     IF contract_vers_cur%ISOPEN THEN
261       CLOSE contract_vers_cur ;
262     END IF;
263 
264        l_doc_type := OKC_API.get_contract_type(l_contract_id);
265        l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_UPD_STS_SUMMARY',l_doc_type);
266        l_resolved_token := OKC_API.resolve_hdr_token(l_doc_type);
267 
268     FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, l_resolved_msg_name,'HDR_TOKEN',l_resolved_token) || ' : ' || l_count);
269     FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, l_resolved_msg_name,'HDR_TOKEN',l_resolved_token) || ' : ' || l_count);
270 
271     COMMIT;
272 
273   EXCEPTION
274     WHEN OTHERS THEN
275 
276        l_doc_type := OKC_API.get_contract_type(l_contract_id);
277        l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_UPD_STS_ERROR',l_doc_type);
278        l_resolved_token := OKC_API.resolve_hdr_token(l_doc_type);
279 
280 
281       FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, l_resolved_msg_name,'HDR_TOKEN',l_resolved_token));
282       FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, l_resolved_msg_name,'HDR_TOKEN',l_resolved_token));
283 
284       FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_UPD_STS_SYS_ERR') || ' ' || sqlerrm);
285       FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_UPD_STS_SYS_ERR') || ' ' || sqlerrm);
286 
287       ROLLBACK;
288 
289       --close cursors
290       IF contracts_cur%ISOPEN THEN
291         CLOSE contracts_cur ;
292       END IF;
293       IF contract_vers_cur%ISOPEN THEN
294         CLOSE contract_vers_cur ;
295       END IF;
296 
297       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 
299       x_msg_count := 1;
300       x_msg_data := substr(SQLERRM, 1, 200);
301 
302       Okc_Api.Set_Message(
303         p_app_name     => G_APP_NAME,
304         p_msg_name     => G_UNEXPECTED_ERROR,
305         p_token1       => G_SQLCODE_TOKEN,
306         p_token1_value => SQLCODE,
307         p_token2       => G_SQLERRM_TOKEN,
308         p_token2_value => SQLERRM);
309 
310       FND_MSG_PUB.Count_And_Get(
311         p_count =>  x_msg_count,
312         p_data  =>  x_msg_data
313       );
314 
315   END contract_status_updater;
316 
317 -- Start of comments
318 --API name      : contract_status_update_manager
319 --Type          : Private.
320 --Function      : Called from Concurrent Manager to update
321 --                status for contract reaching their
322 --                termination date
323 --Pre-reqs      : None.
324 --Parameters    :
325 --IN            : p_status IN VARCHAR2 Required
326 --OUT           : errbuf  OUT NOCOPY VARCHAR2
327 --              : retcode OUT NOCOPY VARCHAR2
328 --Note          :
329 -- End of comments
330 
331   PROCEDURE contract_status_update_manager(
332     p_status IN VARCHAR2,
333     errbuf  OUT NOCOPY VARCHAR2,
334     retcode OUT NOCOPY VARCHAR2)
335   IS
336     l_api_version   NUMBER;
337     l_api_name      VARCHAR2(32);
338     l_init_msg_list VARCHAR2(2000);
339     l_msg_data      VARCHAR2(2000);
340     l_msg_count     NUMBER;
341     l_return_status VARCHAR2(2000);
342     l_status        VARCHAR2(32);
343 
344   BEGIN
345 
346     FND_FILE.PUT_LINE(FND_FILE.LOG, '***** BEGIN contract_status_update_manager *****');
347 
348     l_api_name    := 'contract_status_update_manager';
349     l_api_version   := 1.0;
350     l_init_msg_list := FND_API.G_FALSE;
351     l_status := 'NONE';
352 
353     FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_status = (' || p_status || ')');
354     FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_status = (' || l_status || ')');
355 
356     IF p_status IS NULL OR LENGTH(TRIM(p_status)) = 0 THEN
357       l_status := G_REP_CON_STATUS_TERMINATED;
358     END IF;
359 
360     FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_status = (' || l_status || ')');
361 
362     contract_status_updater(
363       p_api_version   => l_api_version,
364       p_init_msg_list => l_init_msg_list,
365       p_status        => l_status,
366       x_msg_data      => l_msg_data,
367       x_msg_count     => l_msg_count,
368       x_return_status => l_return_status
369     );
370 
371     IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
372       retcode := G_RETURN_CODE_ERROR;
373       errbuf := substr(FND_MSG_PUB.Get(), 1, 200);
374     END IF;
375 
376     FND_FILE.PUT_LINE(FND_FILE.LOG, '***** END contract_status_update_manager() *****');
377 
378   EXCEPTION
379     WHEN OTHERS THEN
380       FND_FILE.PUT_LINE(FND_FILE.LOG, '***** EXCEPTION OTHERS*****');
381       FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
382 
383       retcode := G_RETURN_CODE_ERROR;
384       errbuf := substr(SQLERRM, 1, 200);
385 
386   END contract_status_update_manager;
387 
388 END OKC_REP_STATUS_UPDATE_PVT;