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.AHL_RM_ASSO_DOCASO_PVT

Source


1 PACKAGE BODY AHL_RM_ASSO_DOCASO_PVT AS
2 /* $Header: AHLVRODB.pls 120.1.12020000.2 2012/12/07 13:41:23 sareepar ship $ */
3 
4 G_PKG_NAME    VARCHAR2(30):= 'AHL_RM_ASSO_DOCASO_PVT';
5 G_DEBUG  VARCHAR2(1)   := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
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).OBJECT_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_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 
77 IS
78 
79 cursor get_route_status (p_route_id in number)
80 is
81 select revision_status_code
82 from ahl_routes_app_v
83 where route_id = p_route_id;
84 
85 l_obj_status 			VARCHAR2(30);
86 
87 cursor get_oper_status (p_operation_id in number)
88 is
89 select revision_status_code
90 from ahl_operations_b
91 where operation_id = p_operation_id;
92 
93 l_api_name                  VARCHAR2(30)   := 'process_association';
94 l_api_version               NUMBER         := 1.0;
95 l_return_status             VARCHAR2(1);
96 l_msg_count                 NUMBER	       := NULL;
97 l_msg_data			VARCHAR2(2000);
98 l_x_operation_rec           AHL_RM_OPERATION_PVT.operation_rec_type ;
99 l_x_route_rec               AHL_RM_ROUTE_PVT.route_rec_type ;
100 l_x_doc_assos_tbl           AHL_DI_ASSO_DOC_GEN_PUB.association_tbl;
101 l_object_status		   VARCHAR2(30)		:=NULL;
102 BEGIN
103 
104 
105 	-- Standard call to check for call compatibility.
106 	IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
107 				       p_api_version,
108 				       l_api_name,G_PKG_NAME)
109 	THEN
110 	      RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 	END IF;
112 
113 	-- Standard Start of API savepoint
114 	SAVEPOINT process_association;
115 
116 	-- Check if API is called in debug mode. If yes, enable debug.
117 
118 	IF G_DEBUG='Y' THEN
119 	AHL_DEBUG_PUB.enable_debug;
120 	END IF;
121 
122 	-- Debug info.
123 
124 	IF G_DEBUG='Y' THEN
125 	AHL_DEBUG_PUB.debug( 'enter ahl_rm_asso_doc_aso_pub.Process Association');
126 	END IF;
127 
128 	-- Initialize message list if p_init_msg_list is set to TRUE.
129 	IF FND_API.to_boolean(p_init_msg_list)
130 	THEN
131 		FND_MSG_PUB.initialize;
132 	END IF;
133 
134 	--  Initialize API return status to success
135 
136 	x_return_status:=FND_API.G_RET_STS_SUCCESS ;
137 
138         IF p_x_association_tbl.count >0 THEN
139          FOR i in p_x_association_tbl.FIRST .. p_x_association_tbl.LAST
140          LOOP
141           AHL_RM_ROUTE_UTIL.validate_lookup(
142 		x_return_status 	=>	x_return_status,
143 		x_msg_data		=>	x_msg_data,
144 		p_lookup_type		=>	'AHL_OBJECT_TYPE',
145 		p_lookup_meaning	=>	p_x_association_tbl(i).object_type_desc,
146 		p_x_lookup_code		=>	p_x_association_tbl(i).object_type_code
147 	   );
148 	  IF x_return_status = FND_API.G_RET_STS_ERROR THEN
149 	     IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
150 		 fnd_log.string
151 		 (
152 		     fnd_log.level_statement,
153 		    'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
154 		     'Error -- Invalid lookup specified'
155 		 );
156 	     END IF;
157 	     RAISE FND_API.G_EXC_ERROR;
158 	  END IF;
159 
160 	  -- If Id is null derive object id from object Number and revision
161 	  -- This will fail if no record are passed to this API or if records with different association types are passed. This needs to be fixed sooner or later. - Balaji
162 	  IF p_x_association_tbl(i).object_id IS NULL THEN
163 		IF p_x_association_tbl(i).object_type_code = 'ROUTE' THEN
164 		  -- Function to convert Operation number, operation revision to id
165 		  AHL_RM_ROUTE_UTIL.Route_Number_To_Id
166 		  (
167 		   p_route_number		=>	p_x_association_tbl(i).object_number,
168 		   p_route_revision		=>	p_x_association_tbl(i).object_revision,
169 		   x_route_id			=>	p_x_association_tbl(i).object_id,
170 		   x_return_status		=>	x_return_status
171 		  );
172 		ELSIF p_x_association_tbl(i).object_type_code = 'OPERATION' THEN
173 		  -- Function to convert Operation number, operation revision to id
174 		  AHL_RM_ROUTE_UTIL.Operation_Number_To_Id
175 		  (
176 		   p_operation_number		=>	p_x_association_tbl(i).object_number,
177 		   p_operation_revision		=>	p_x_association_tbl(i).object_revision,
178 		   x_operation_id		=>	p_x_association_tbl(i).object_id,
179 		   x_return_status		=>	x_return_status
180 		  );
181 		END IF;
182 		IF x_return_status = FND_API.G_RET_STS_ERROR THEN
183 		     IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
184 			 fnd_log.string
185 			 (
186 			     fnd_log.level_statement,
187 			    'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
188 			     'Error in converting Object Number, Object Revision to ID'
189 			 );
190 		     END IF;
191 		     RAISE FND_API.G_EXC_ERROR;
192 		END IF;
193 	   END IF;
194 	  END LOOP;
195 	END IF;
196 
197 	--l_x_association_tbl  := p_x_association_tbl  ;
198 
199 	--This is to be added before calling   AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION ()
200 	-- Validate Application Usage
201 	IF (p_x_association_tbl(1).object_type_code = 'ROUTE')
202 	THEN
203 	  AHL_RM_ROUTE_UTIL.validate_ApplnUsage
204 	  (
205 	     p_object_id              => p_x_association_tbl(1).OBJECT_ID,
206 	     p_association_type       => p_x_association_tbl(1).OBJECT_TYPE_CODE,
207 	     x_return_status          => x_return_status,
208 	     x_msg_data               => x_msg_data
209 	  );
210 
211 	-- If any severe error occurs, then, abort API.
212 	  IF x_return_status = FND_API.G_RET_STS_ERROR THEN
213 	    RAISE FND_API.G_EXC_ERROR;
214 	  ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
215 	    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 	  END IF;
217 	END IF;
218 
219 	-- Populate doc table from RM table info. procedure added in 11.5.10
220 	populate_doc_tbl(
221 	p_x_association_tbl	=> p_x_association_tbl,
222 	p_x_doc_assos_tbl	=> l_x_doc_assos_tbl
223 	);
224 
225 
226 	 AHL_DEBUG_PUB.debug( 'before call on AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION');
227 
228 	AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION
229 	(
230 	 p_api_version               => p_api_version ,
231 	 p_init_msg_list             => p_init_msg_list ,
232 	 p_commit                    => FND_API.G_FALSE ,
233 	 p_validation_level          => p_validation_level ,
234 	 p_validate_only             => p_validate_only ,
235 	 p_module_type               => p_module_type ,
236 	 x_return_status             => x_return_status ,
237 	 x_msg_count                 => x_msg_count ,
238 	 x_msg_data                  => x_msg_data ,
239 	 p_x_association_tbl         => l_x_doc_assos_tbl
240 	) ;
241 
242 	l_msg_count := FND_MSG_PUB.count_msg;
243 
244 	IF l_msg_count > 0 THEN
245 	   X_msg_count := l_msg_count;
246 	   X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 	   RAISE FND_API.G_EXC_ERROR;
248 	END IF;
249 
250 	-- call is success so copy the DOC_TITLE_ASSO_ID and Object_version_number back into RM record structure. Balaji added in 11510+ as a part of code cleanup.
251 	FOR i IN l_x_doc_assos_tbl.FIRST..l_x_doc_assos_tbl.LAST
252 	LOOP
253 		p_x_association_tbl(i).DOC_TITLE_ASSO_ID := l_x_doc_assos_tbl(i).DOC_TITLE_ASSO_ID;
254 		p_x_association_tbl(i).OBJECT_VERSION_NUMBER := l_x_doc_assos_tbl(i).OBJECT_VERSION_NUMBER;
255 	END LOOP;
256 
257 	IF G_DEBUG='Y' THEN
258 	    AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION is called');
259 	    AHL_DEBUG_PUB.debug( 'PRITHWI the aso object type code is '||  p_x_association_tbl(1).object_type_code );
260 	END IF;
261 
262 	IF ( p_x_association_tbl(1).object_type_code = 'OPERATION')
263 	THEN
264 		  IF G_DEBUG='Y' THEN
265 			AHL_DEBUG_PUB.debug( 'aso_object_type_code = OPERATION');
266 		  END IF;
267 
268 		-- Check if the Route is existing and in Draft status
269 		--FP #8410484
270 		--AHL_RM_ROUTE_UTIL.validate_operation_status
271 		--(
272 			--p_x_association_tbl(1).OBJECT_ID,
273 			--l_msg_data,
274 			--l_return_status
275 		--);
276 
277 		OPEN get_oper_status( p_x_association_tbl(1).OBJECT_ID);
278 		FETCH get_oper_status INTO l_object_status;
279 		IF get_oper_status%NOTFOUND THEN
280 			l_return_status := FND_API.G_RET_STS_ERROR;
281 			l_msg_data := 'AHL_RM_INVALID_OPERATION';
282 		END IF;
283 		IF ( l_object_status <> 'DRAFT' AND
284 			l_object_status <> 'APPROVAL_REJECTED' AND
285 			l_object_status <> 'COMPLETE') THEN
286 			l_return_status := FND_API.G_RET_STS_ERROR;
287 			l_msg_data := 'AHL_RM_INVALID_OPER_STATUS';
288 		END IF;
289 		CLOSE get_oper_status;
290 		-- End of FP #8410484
291 		IF l_return_status = FND_API.G_RET_STS_ERROR THEN
292 			FND_MESSAGE.SET_NAME('AHL',l_msg_data);
293 			FND_MSG_PUB.ADD;
294 			x_return_status := l_return_status;
295 			RETURN;
296 		END IF;
297 
298 		-- Update route status from APPROVAL_REJECTED to DRAFT
299 		OPEN get_oper_status (p_x_association_tbl(1).OBJECT_ID);
300 		FETCH get_oper_status INTO l_obj_status;
301 		IF (get_oper_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
302 		THEN
303 			UPDATE ahl_operations_b
304 			SET revision_status_code = 'DRAFT'
305 			WHERE operation_id = p_x_association_tbl(1).OBJECT_ID;
306 		END IF;
307 		CLOSE get_oper_status;
308 
309 
310 	ELSIF ( p_x_association_tbl(1).object_type_code = 'ROUTE')
311 	THEN
312 		IF G_DEBUG='Y' THEN
313 			AHL_DEBUG_PUB.debug( 'aso_object_type_code = ROUTE');
314 		END IF;
315 
316 		-- Check if the Route is existing and in Draft status
317 		-- FP #8410484
318 		--AHL_RM_ROUTE_UTIL.validate_route_status
319 		--(
320 			--p_x_association_tbl(1).OBJECT_ID,
321 			--l_msg_data,
322 			--l_return_status
323 		--);
324 		OPEN get_route_status( p_x_association_tbl(1).OBJECT_ID);
325 		FETCH get_route_status INTO l_object_status;
326 		IF get_route_status%NOTFOUND THEN
327 			l_return_status := FND_API.G_RET_STS_ERROR;
328 			l_msg_data := 'AHL_RM_INVALID_ROUTE';
329 		END IF;
330 		IF ( l_object_status <> 'DRAFT' AND
331 			l_object_status <> 'APPROVAL_REJECTED' AND
332 			l_object_status <> 'COMPLETE') THEN
333 			l_return_status := FND_API.G_RET_STS_ERROR;
334 			l_msg_data := 'AHL_RM_INVALID_ROUTE_STATUS';
335 		END IF;
336 		CLOSE get_route_status;
337 		--End of FP #8410484
338 		IF l_return_status = FND_API.G_RET_STS_ERROR THEN
339 			FND_MESSAGE.SET_NAME('AHL',l_msg_data);
340 			FND_MSG_PUB.ADD;
341 			x_return_status := l_return_status;
342 			RETURN;
343 		END IF;
344 
345 		-- Update route status from APPROVAL_REJECTED to DRAFT
346   		OPEN get_route_status (p_x_association_tbl(1).OBJECT_ID);
347 		FETCH get_route_status INTO l_obj_status;
348 		IF (get_route_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
349 		THEN
350 			UPDATE ahl_routes_b
351 			SET revision_status_code = 'DRAFT'
352 			WHERE route_id = p_x_association_tbl(1).OBJECT_ID;
353 		END IF;
354 		CLOSE get_route_status;
355 
356 	END IF ;
357 	l_msg_count := FND_MSG_PUB.count_msg;
358 
359 	IF l_msg_count > 0 THEN
360 	   X_msg_count := l_msg_count;
361 	   X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362 	   RAISE FND_API.G_EXC_ERROR;
363 	END IF;
364 
365 	 IF FND_API.TO_BOOLEAN(p_commit) THEN
366 	    COMMIT;
367 	 END IF;
368 
369 	-- Check if API is called in debug mode. If yes, disable debug.
370 
371 	IF G_DEBUG='Y' THEN
372 		  AHL_DEBUG_PUB.disable_debug;
373 	END IF;
374 
375 	EXCEPTION
376 	 WHEN FND_API.G_EXC_ERROR THEN
377 	    ROLLBACK TO process_association;
378 	    x_return_status := FND_API.G_RET_STS_ERROR;
379 	    FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
380 				       p_count => x_msg_count,
381 				       p_data  => X_msg_data);
382 		-- Debug info.
383 		IF G_DEBUG='Y' THEN
384 			  AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
385 			  AHL_DEBUG_PUB.debug( 'ahl_rm_asso_doc_aso_pub.Process Association');
386 			  AHL_DEBUG_PUB.disable_debug;
387 		END IF;
388 
389 	 WHEN OTHERS THEN
390 	    ROLLBACK TO process_association;
391 	    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392 	    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
393 	    THEN
394 	    fnd_msg_pub.add_exc_msg(p_pkg_name        =>G_PKG_NAME ,
395 				    p_procedure_name  => 'PROCESS_ASSOCIATION',
396 				    p_error_text      => SUBSTR(SQLERRM,1,240));
397 	    END IF;
398 	    FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
399 				       p_count => x_msg_count,
400 				       p_data  => X_msg_data);
401 
402 
403 
404 END process_association;
405 
406 END AHL_RM_ASSO_DOCASO_PVT;