[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_DOC_ASSO_PVT
Source
1 PACKAGE BODY AHL_FMP_MR_DOC_ASSO_PVT AS
2 /* $Header: AHLVMRDB.pls 120.1.12020000.2 2012/12/14 09:30:29 shnatu ship $ */
3
4 G_PKG_NAME VARCHAR2(30):= 'AHL_FMP_MR_DOC_ASSO_PVT';
5 G_APPLN_USAGE VARCHAR2(30):=RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
6
7 -- Populate doc table from RM table info.
8 PROCEDURE Populate_Doc_Tbl(
9 p_x_association_tbl IN OUT NOCOPY doc_association_tbl,
10 p_x_doc_assos_tbl IN OUT NOCOPY AHL_DI_ASSO_DOC_GEN_PUB.association_tbl
11 )
12 IS
13
14 BEGIN
15
16 IF p_x_association_tbl.count >0 THEN
17 FOR i in p_x_association_tbl.FIRST .. p_x_association_tbl.LAST
18 LOOP
19 p_x_doc_assos_tbl(i).DOC_TITLE_ASSO_ID := p_x_association_tbl(i).DOC_TITLE_ASSO_ID;
20 p_x_doc_assos_tbl(i).DOCUMENT_ID := p_x_association_tbl(i).DOCUMENT_ID;
21 p_x_doc_assos_tbl(i).DOCUMENT_NO := p_x_association_tbl(i).DOCUMENT_NO;
22 p_x_doc_assos_tbl(i).DOC_REVISION_ID := p_x_association_tbl(i).DOC_REVISION_ID;
23 p_x_doc_assos_tbl(i).REVISION_NO := p_x_association_tbl(i).REVISION_NO;
24 p_x_doc_assos_tbl(i).USE_LATEST_REV_FLAG := p_x_association_tbl(i).USE_LATEST_REV_FLAG;
25 p_x_doc_assos_tbl(i).ASO_OBJECT_TYPE_CODE := p_x_association_tbl(i).OBJECT_TYPE_CODE;
26 p_x_doc_assos_tbl(i).ASO_OBJECT_DESC := p_x_association_tbl(i).OBJECT_TYPE_DESC;
27 p_x_doc_assos_tbl(i).ASO_OBJECT_ID := p_x_association_tbl(i).MR_HEADER_ID;
28 p_x_doc_assos_tbl(i).SERIAL_NO := p_x_association_tbl(i).SERIAL_NO;
29 p_x_doc_assos_tbl(i).SOURCE_LANG := p_x_association_tbl(i).SOURCE_LANG;
30 p_x_doc_assos_tbl(i).CHAPTER := p_x_association_tbl(i).CHAPTER;
31 p_x_doc_assos_tbl(i).SECTION := p_x_association_tbl(i).SECTION;
32 p_x_doc_assos_tbl(i).SUBJECT := p_x_association_tbl(i).SUBJECT;
33 p_x_doc_assos_tbl(i).PAGE := p_x_association_tbl(i).PAGE;
34 p_x_doc_assos_tbl(i).FIGURE := p_x_association_tbl(i).FIGURE;
35 p_x_doc_assos_tbl(i).NOTE := p_x_association_tbl(i).NOTE;
36 p_x_doc_assos_tbl(i).SOURCE_REF_CODE := p_x_association_tbl(i).SOURCE_REF_CODE;
37 p_x_doc_assos_tbl(i).SOURCE_REF_MEAN := p_x_association_tbl(i).SOURCE_REF_MEAN;
38 p_x_doc_assos_tbl(i).OBJECT_VERSION_NUMBER := p_x_association_tbl(i).OBJECT_VERSION_NUMBER;
39 p_x_doc_assos_tbl(i).ATTRIBUTE_CATEGORY := p_x_association_tbl(i).ATTRIBUTE_CATEGORY;
40 p_x_doc_assos_tbl(i).ATTRIBUTE1 := p_x_association_tbl(i).ATTRIBUTE1;
41 p_x_doc_assos_tbl(i).ATTRIBUTE2 := p_x_association_tbl(i).ATTRIBUTE2;
42 p_x_doc_assos_tbl(i).ATTRIBUTE3 := p_x_association_tbl(i).ATTRIBUTE3;
43 p_x_doc_assos_tbl(i).ATTRIBUTE4 := p_x_association_tbl(i).ATTRIBUTE4;
44 p_x_doc_assos_tbl(i).ATTRIBUTE5 := p_x_association_tbl(i).ATTRIBUTE5;
45 p_x_doc_assos_tbl(i).ATTRIBUTE6 := p_x_association_tbl(i).ATTRIBUTE6;
46 p_x_doc_assos_tbl(i).ATTRIBUTE7 := p_x_association_tbl(i).ATTRIBUTE7;
47 p_x_doc_assos_tbl(i).ATTRIBUTE8 := p_x_association_tbl(i).ATTRIBUTE8;
48 p_x_doc_assos_tbl(i).ATTRIBUTE9 := p_x_association_tbl(i).ATTRIBUTE9;
49 p_x_doc_assos_tbl(i).ATTRIBUTE10 := p_x_association_tbl(i).ATTRIBUTE10;
50 p_x_doc_assos_tbl(i).ATTRIBUTE11 := p_x_association_tbl(i).ATTRIBUTE11;
51 p_x_doc_assos_tbl(i).ATTRIBUTE12 := p_x_association_tbl(i).ATTRIBUTE12;
52 p_x_doc_assos_tbl(i).ATTRIBUTE13 := p_x_association_tbl(i).ATTRIBUTE13;
53 p_x_doc_assos_tbl(i).ATTRIBUTE14 := p_x_association_tbl(i).ATTRIBUTE14;
54 p_x_doc_assos_tbl(i).ATTRIBUTE15 := p_x_association_tbl(i).ATTRIBUTE15;
55 p_x_doc_assos_tbl(i).DML_OPERATION := p_x_association_tbl(i).DML_OPERATION;
56 END LOOP;
57 END IF;
58
59 END Populate_Doc_Tbl;
60
61
62 PROCEDURE Process_Doc_Association
63 (
64 p_api_version IN NUMBER := 1.0,
65 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
66 p_commit IN VARCHAR2 := FND_API.G_FALSE,
67 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
68 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
69 p_default IN VARCHAR2 := FND_API.G_FALSE,
70 p_module_type IN VARCHAR2,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2,
74 p_x_association_tbl IN OUT NOCOPY doc_association_tbl
75 )
76 IS
77
78 l_api_name VARCHAR2(30) := 'PROCESS_DOC_ASSOCIATION';
79 l_api_version NUMBER := 1.0;
80 l_x_doc_assos_tbl AHL_DI_ASSO_DOC_GEN_PUB.association_tbl;
81
82 -- FP #8410484
83 CURSOR mr_details_csr_type(p_mr_header_id NUMBER)
84 IS
85 SELECT mr_status_code,type_code
86 From AHL_MR_HEADERS_APP_V
87 Where mr_header_id=p_mr_header_id
88 And mr_status_code IN('DRAFT','APPROVAL_REJECTED','COMPLETE');
89
90 l_mr_rec mr_details_csr_type%rowtype;
91
92 BEGIN
93 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
94 fnd_log.string
95 (
96 fnd_log.level_procedure,
97 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
98 'At the start of PLSQL procedure'
99 );
100 END IF;
101
102 -- Initialize API return status to success
103 x_return_status:=FND_API.G_RET_STS_SUCCESS ;
104
105 -- Standard Start of API savepoint
106 SAVEPOINT process_association_pvt;
107
108 -- Standard call to check for call compatibility.
109 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
110 p_api_version,
111 l_api_name,G_PKG_NAME)
112 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)
118 THEN
119 FND_MSG_PUB.initialize;
120 END IF;
121
122 --Check application usage code is not null
123 IF g_appln_usage is null
124 THEN
125 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
126 FND_MSG_PUB.ADD;
127 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
128 fnd_log.string
129 (
130 fnd_log.level_error,
131 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
132 'Error -- Application usage code is null'
133 );
134 END IF;
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
139 fnd_log.string
140 (
141 fnd_log.level_statement,
142 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
143 'Before doing value to id conversion.....'
144 );
145 END IF;
146
147 IF p_x_association_tbl.count >0 THEN
148 FOR i in p_x_association_tbl.FIRST .. p_x_association_tbl.LAST
149 LOOP
150 -- Convert(if required) and validate the object_type lookup
151 AHL_RM_ROUTE_UTIL.validate_lookup(
152 x_return_status => x_return_status,
153 x_msg_data => x_msg_data,
154 p_lookup_type => 'AHL_OBJECT_TYPE',
155 p_lookup_meaning => p_x_association_tbl(i).object_type_desc,
156 p_x_lookup_code => p_x_association_tbl(i).object_type_code
157 );
158 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
159 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
160 fnd_log.string
161 (
162 fnd_log.level_statement,
163 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
164 'Error -- Invalid lookup specified'
165 );
166 END IF;
167 RAISE FND_API.G_EXC_ERROR;
168 END IF;
169
170 IF p_x_association_tbl(i).mr_header_id IS NULL THEN
171 -- Function to convert mr_title,mr_version_number to id
172 AHL_FMP_COMMON_PVT.mr_title_version_to_id(
173 p_mr_title => p_x_association_tbl(i).mr_title,
174 p_mr_version_number => p_x_association_tbl(i).mr_version_number,
175 x_mr_header_id => p_x_association_tbl(i).mr_header_id,
176 x_return_status => x_return_status
177 );
178 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
179 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
180 fnd_log.string
181 (
182 fnd_log.level_error,
183 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
184 'Invalid MR Title, Version Number provided'
185 );
186 END IF;
187 RAISE FND_API.G_EXC_ERROR;
188 END IF;
189 END IF;
190
191 --App usage code related code
192 IF p_x_association_tbl(i).mr_header_id IS NOT NULL OR
193 p_x_association_tbl(i).mr_header_id <> FND_API.G_MISS_NUM
194 THEN
195 OPEN mr_details_csr_type(p_x_association_tbl(i).mr_header_id);
196
197 FETCH mr_details_csr_type into l_mr_rec;
198
199 IF mr_details_csr_type%NOTFOUND
200 THEN
201 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASSO_UPDATE_ERROR');
202 FND_MSG_PUB.ADD;
203 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
204 fnd_log.string
205 (
206 fnd_log.level_error,
207 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
208 'Invalid MR Info..'
209 );
210 END IF;
211 RAISE FND_API.G_EXC_ERROR;
212 ELSE
213 -- Preventive Maintenance Code
214 IF G_APPLN_USAGE='PM'
215 THEN
216 IF l_mr_rec.type_code='PROGRAM'
217 THEN
218 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TYPE_CODE_PROGRAM');
219 FND_MSG_PUB.ADD;
220 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
221 fnd_log.string
222 (
223 fnd_log.level_error,
224 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
225 'Application usage code PM is invalid for MR with program type'||
226 l_mr_rec.type_code
227 );
228 END IF;
229 RAISE FND_API.G_EXC_ERROR;
230 END IF;
231 END IF;
232 END IF;
233 CLOSE mr_details_csr_type;
234 END IF;
235
236 END LOOP;
237 END IF;
238
239 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
240 fnd_log.string
241 (
242 fnd_log.level_statement,
243 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
244 'Before copying mr table of records into doc table of records'
245 );
246 END IF;
247
248 -- Populate doc table from RM table info. procedure added in 11.5.10
249 populate_doc_tbl(
250 p_x_association_tbl => p_x_association_tbl,
251 p_x_doc_assos_tbl => l_x_doc_assos_tbl
252 );
253
254 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
255 fnd_log.string
256 (
257 fnd_log.level_statement,
258 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
259 'Before calling AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION..'
260 );
261 END IF;
262
263 -- Call DI Public API for doc association.
264
265 AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION
266 (
267 p_api_version => p_api_version ,
268 p_init_msg_list => p_init_msg_list ,
269 p_commit => FND_API.G_FALSE ,
270 p_validation_level => p_validation_level ,
271 p_validate_only => p_validate_only ,
272 p_module_type => p_module_type ,
273 x_return_status => x_return_status ,
274 x_msg_count => x_msg_count ,
275 x_msg_data => x_msg_data ,
276 p_x_association_tbl => l_x_doc_assos_tbl
277 ) ;
278
279 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
280 fnd_log.string
281 (
282 fnd_log.level_statement,
283 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
284 'After calling AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION'
285 );
286 END IF;
287
288 -- If any severe error occurs, then, abort API.
289 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
290 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
291 fnd_log.string
292 (
293 fnd_log.level_statement,
294 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
295 'Invalid MR Title, Version Number provided'
296 );
297 END IF;
298 RAISE FND_API.G_EXC_ERROR;
299 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
300 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
301 fnd_log.string
302 (
303 fnd_log.level_statement,
304 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
305 'Invalid MR Title, Version Number provided'
306 );
307 END IF;
308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
309 END IF;
310
311 -- Standard check for p_commit
312 IF FND_API.To_Boolean (p_commit)
313 THEN
314 COMMIT;
315 END IF;
316
317 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
318 fnd_log.string
319 (
320 fnd_log.level_procedure,
321 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
322 'At the end of PLSQL procedure'
323 );
324 END IF;
325
326 EXCEPTION
327 WHEN FND_API.G_EXC_ERROR THEN
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 Rollback to process_association_pvt;
330 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
331 p_data => x_msg_data,
332 p_encoded => fnd_api.g_false);
333 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
334 fnd_log.string
335 (
336 fnd_log.level_statement,
337 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
338 'FND_API.G_EXC_ERROR!!'
339 );
340 END IF;
341
342
343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
345 Rollback to process_association_pvt;
346 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
347 p_data => x_msg_data,
348 p_encoded => fnd_api.g_false);
349 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
350 fnd_log.string
351 (
352 fnd_log.level_statement,
353 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
354 'FND_API.G_EXC_UNEXPECTED_ERROR!!'
355 );
356 END IF;
357
358 WHEN OTHERS THEN
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360 Rollback to process_association_pvt;
361 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
362 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
363 p_procedure_name => 'Process_Doc_Association',
364 p_error_text => SQLERRM);
365 END IF;
366 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
367 p_data => x_msg_data,
368 p_encoded => fnd_api.g_false);
369 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
370 fnd_log.string
371 (
372 fnd_log.level_statement,
373 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
374 'Un expected error(other errors)!!'
375 );
376 END IF;
377
378 END Process_Doc_Association;
379
380 END AHL_FMP_MR_DOC_ASSO_PVT;
381