[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_CONTRACTS
Source
1 PACKAGE BODY OKS_IMPORT_CONTRACTS AS
2 -- $Header: OKSPKIMPB.pls 120.2 2007/09/06 11:18:38 vmutyala noship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPB.pls Created By Vamshi Mutyala |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import Package |
13 --| |
14 --+========================================================================
15
16 --===================
17 -- GLOBALS
18 --===================
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_CONTRACTS';
21
22 --========================================================================
23 -- PRIVATE CONSTANTS AND VARIABLES
24 --========================================================================
25 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'oks.plsql.import.' || G_PKG_NAME;
26 G_WORKER_REQ_ID CONSTANT NUMBER := FND_GLOBAL.conc_request_id;
27 G_MODULE_HEAD CONSTANT VARCHAR2(200) := G_MODULE_NAME || '(Req Id = '||G_WORKER_REQ_ID||').';
28 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
29 G_UNEXPECTED_LOG CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
30 fnd_log.TEST(fnd_log.level_unexpected, G_MODULE_HEAD);
31 G_ERROR_LOG CONSTANT BOOLEAN := G_UNEXPECTED_LOG AND fnd_log.level_error >= G_LOG_LEVEL;
32 G_EXCEPTION_LOG CONSTANT BOOLEAN := G_ERROR_LOG AND fnd_log.level_exception >= G_LOG_LEVEL;
33 G_EVENT_LOG CONSTANT BOOLEAN := G_EXCEPTION_LOG AND fnd_log.level_event >= G_LOG_LEVEL;
34 G_PROCEDURE_LOG CONSTANT BOOLEAN := G_EVENT_LOG AND fnd_log.level_procedure >= G_LOG_LEVEL;
35 G_STMT_LOG CONSTANT BOOLEAN := G_PROCEDURE_LOG AND fnd_log.level_statement >= G_LOG_LEVEL;
36
37 --=========================
38 -- PROCEDURES AND FUNCTIONS
39 --=========================
40
41 --========================================================================
42 -- PROCEDURE : clear_ad_cache PRIVATE
43 -- PARAMETERS: P_batch_id in Batch Number
44 -- COMMENT : This procedure will clear the AD Cache for current batch id
45 --=========================================================================
46 PROCEDURE clear_ad_cache ( P_batch_id IN NUMBER)
47 IS
48 l_stmt_num NUMBER := 0;
49 l_routine CONSTANT VARCHAR2(30) := 'clear_ad_cache';
50 l_product varchar2(30) := 'OKS';
51 l_table_name varchar2(30) := 'OKS_HEADERS_INTERFACE';
52 l_update_name varchar2(30);
53 l_status varchar2(30);
54 l_industry varchar2(30);
55 l_retstatus boolean;
56 l_table_owner varchar2(30);
57 BEGIN
58 --
59 -- Clear AD cache for the current batch id
60 --
61 IF G_PROCEDURE_LOG THEN
62 fnd_log.string(fnd_log.level_procedure,
63 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
64 'Entering with ' ||
65 'P_batch_id = ' || P_batch_id);
66 END IF;
67
68 l_retstatus := fnd_installation.get_app_info(l_product,
69 l_status,
70 l_industry,
71 l_table_owner);
72
73 IF ((l_retstatus = FALSE) OR (l_table_owner is null)) then
74 IF G_ERROR_LOG THEN
75 fnd_log.string(fnd_log.level_error,
76 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
77 'Cannot get schema name for product : '||l_product);
78 END IF;
79 raise_application_error(-20001,'Cannot get schema name for product : '||l_product);
80 END IF;
81
82 l_update_name := 'Import'||P_batch_id;
83 ad_parallel_updates_pkg.purge_processed_units(l_table_owner,
84 l_table_name,
85 l_update_name);
86
87 DELETE FROM AD_PARALLEL_UPDATES
88 WHERE OWNER = l_table_owner AND TABLE_NAME = l_table_name AND script_name = l_update_name;
89
90
91 IF G_PROCEDURE_LOG THEN
92 fnd_log.string(fnd_log.level_procedure,
93 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
94 'Exit.');
95 END IF;
96 EXCEPTION
97 WHEN FND_API.G_EXC_ERROR THEN
98 RAISE FND_API.G_EXC_ERROR;
99 WHEN OTHERS THEN
100 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
101 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
102 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
103 FND_MSG_PUB.Add;
104 RAISE FND_API.G_EXC_ERROR;
105 END clear_ad_cache;
106
107 --========================================================================
108 -- PROCEDURE : Import_contracts PRIVATE
109 -- PARAMETERS: X_errbuf out Error message buffer
110 -- X_retcode out Return status code
111 -- P_mode in Validate Only, Import flag
112 -- P_batch_id in Batch Number
113 -- P_Num_Workers in Number of workers
114 -- P_commit_size in Work unit size
115 -- COMMENT : This procedure is the manager in AD parallel framework
116 -- to trigger workers for import process
117 --=========================================================================
118 PROCEDURE Import_contracts (X_errbuf OUT NOCOPY VARCHAR2,
119 X_retcode OUT NOCOPY VARCHAR2,
120 P_mode IN VARCHAR2,
121 P_batch_id IN NUMBER,
122 P_Num_Workers IN NUMBER,
123 P_commit_size IN NUMBER)
124 IS
125
126 l_stmt_num NUMBER := 0;
127 l_routine CONSTANT VARCHAR2(30) := 'Import_contracts';
128 l_msg_data VARCHAR2(2000);
129 l_msg_count NUMBER;
130 l_row_count NUMBER := 0;
131 l_worker_count NUMBER := 0;
132 l_report_req_id NUMBER;
133 l_message VARCHAR2(2000);
134 BEGIN
135 --
136 -- Manager processing for OKS_HEADERS_INTERFACE table
137 --
138 IF G_PROCEDURE_LOG THEN
139 fnd_log.string(fnd_log.level_procedure,
140 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
141 'Entering with ' ||
142 'P_mode = ' || P_mode ||','||
143 'P_batch_id = ' || P_batch_id ||','||
144 'P_Num_Workers = ' || P_Num_Workers ||','||
145 'P_commit_size = ' || P_commit_size );
146 END IF;
147
148 l_stmt_num := 10;
149 SELECT count(1) INTO l_row_count FROM OKS_HEADERS_INTERFACE
150 WHERE BATCH_ID = P_batch_id
151 AND (INTERFACE_STATUS IS NULL OR INTERFACE_STATUS = 'R')
152 AND rownum = 1;
153
154 l_stmt_num := 15;
155 SELECT count(1) INTO l_worker_count FROM fnd_concurrent_requests
156 WHERE parent_request_id = G_WORKER_REQ_ID
157 AND rownum = 1;
158
159 IF l_row_count > 0 AND l_worker_count = 0 THEN
160 l_stmt_num := 20;
161 clear_ad_cache(P_batch_id);
162
163 l_stmt_num := 50;
164 AD_CONC_UTILS_PKG.submit_subrequests(X_errbuf,
165 X_retcode,
166 'OKS',
167 'OKSIMPWRKR',
168 P_commit_size,
169 P_Num_Workers,
170 P_mode,
171 P_batch_id,
172 G_WORKER_REQ_ID
173 );
174 ELSE
175 l_stmt_num := 30;
176
177 /* Trigger error report */
178 l_report_req_id := FND_REQUEST.SUBMIT_REQUEST( 'OKS'
179 ,'OKS_IMP_REPORT'
180 ,NULL
181 ,NULL
182 ,FALSE
183 ,P_batch_id
184 ,G_WORKER_REQ_ID
185 ,P_mode
186 ,P_Num_Workers
187 ,P_commit_size
188 );
189 COMMIT;
190 IF (l_report_req_id = 0) THEN
191 l_message := fnd_message.get;
192 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
193 FND_MESSAGE.set_token('ROUTINE', G_MODULE_HEAD ||l_routine);
194 FND_MESSAGE.set_token('MESSAGE', 'Unable to submit request for error report '||l_message);
195 FND_MSG_PUB.Add;
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198 END IF;
199
200 IF G_PROCEDURE_LOG THEN
201 fnd_log.string(fnd_log.level_procedure,
202 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
203 'Exit with ' ||
204 'X_errbuf = ' || X_errbuf ||','||
205 'X_retcode = ' || X_retcode);
206 END IF;
207 EXCEPTION
208 WHEN FND_API.G_EXC_ERROR THEN
209
210 FND_MSG_PUB.Count_And_Get
211 (p_encoded => FND_API.G_FALSE
212 ,p_count => l_msg_count
213 ,p_data => l_msg_data
214 );
215 X_retcode := '2';
216 X_errbuf := l_msg_data;
217
218 IF G_EXCEPTION_LOG THEN
219 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data);
220 END IF;
221
222 WHEN OTHERS THEN
223 X_errbuf := 'Stmt no '||l_stmt_num||' '||SQLCODE || substr(SQLERRM, 1, 200);
224 X_retcode := '2';
225 FND_MSG_PUB.Count_And_Get
226 (p_encoded => FND_API.G_FALSE
227 ,p_count => l_msg_count
228 ,p_data => l_msg_data
229 );
230
231 IF G_EXCEPTION_LOG THEN
232 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
233 , G_MODULE_HEAD || l_routine ||'.others_exc'
234 , 'others: ' || X_errbuf || ' ' || substr(l_msg_data, 1,250)
235 );
236 END IF;
237
238 END Import_contracts;
239
240 END OKS_IMPORT_CONTRACTS;