1 PACKAGE BODY AHL_ENIGMA_UTIL_PKG AS
2 /* $Header: AHLUENGB.pls 120.2.12020000.2 2012/12/10 16:47:07 prakkum ship $ */
3
4 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
5 l_log_statement NUMBER := fnd_log.level_statement;
6 l_log_procedure NUMBER := fnd_log.level_procedure;
7 l_log_error NUMBER := fnd_log.level_error;
8 l_log_unexpected NUMBER := fnd_log.level_unexpected;
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_ENIGMA_UTIL_PKG';
11
12 ----------------------------------------------------------------------------------------------------------
13 -- Declare Procedures --
14 ---------------------------------------------------------------------------------------------------------
15 -- Start of Comments --
16 -- Procedure name : get_enigma_url_params
17 -- Function : Returns the Model of MC header, ATA Code of the position , Tail Number of the
18 -- unit, User Name , User lang and Doc Id
19 -- get_enigma_url_params Parameters:
20 -- p_object_type : Indicates whether the call is from MC or UC context
21 -- p_primary_object_id : Incase of MC this will be the Relationship Id, For UC this will be the
22 -- instance Id
23 -- p_secondary_object_id : Incase of MC this will be null, incase of UC if p_primary_object_id if
24 -- is null this will be
25 -- uc header id
26 -- x_model : The model of the corresponding MC
27 -- x_ata_code : The ATA Code of the corresponding position
28 -- x_tail_number : The tail number of the corresponding UC
29 -- x_user_name : The User logged in
30 -- x_user_lang : The User lang
31 -- x_doc_id : The Doc Id
32 -- End of Comments.
33 ---------------------------------------------------------------------------------------------------------
34 PROCEDURE get_enigma_url_params(
35 p_object_type IN VARCHAR2,
36 p_primary_object_id IN NUMBER,
37 p_secondary_object_id IN NUMBER,
38 x_model OUT NOCOPY VARCHAR2,
39 x_ata_code OUT NOCOPY VARCHAR2,
40 x_tail_number OUT NOCOPY VARCHAR2,
41 x_user_name OUT NOCOPY VARCHAR2,
42 x_user_lang OUT NOCOPY VARCHAR2,
43 x_doc_id OUT NOCOPY VARCHAR2
44 )
45 IS
46
47 l_api_name CONSTANT VARCHAR2(25) := 'get_enigma_url_params';
48
49 CURSOR get_model_and_ata_csr(c_relationship_id NUMBER) IS
50 SELECT model_code,
51 ata_code
52 FROM ahl_mc_headers_b hdr,
53 ahl_mc_relationships rel
54 WHERE rel.relationship_id = c_relationship_id
55 AND rel.mc_header_id = hdr.mc_header_id;
56
57 CURSOR get_model_and_tail_csr(c_uc_header_id NUMBER) IS
58 SELECT uc.name,model_code
59 FROM ahl_unit_config_headers uc,ahl_mc_headers_b mc
60 WHERE uc.master_config_id = mc.mc_header_id
61 AND unit_config_header_id = c_uc_header_id;
62
63 CURSOR get_user_and_lang IS
64 SELECT FND_GLOBAL.USER_NAME,
65 userenv('LANG')
66 FROM dual;
67
68 CURSOR get_doc_id (c_workorder_id NUMBER)IS
69 SELECT enigma_doc_id
70 FROM ahl_workorders wo,ahl_routes_b rt
71 WHERE wo.workorder_id = c_workorder_id
72 AND wo.route_id = rt.route_id;
73
74 CURSOR get_wo_model_tail_csr (c_workorder NUMBER)
75 IS
76 SELECT model_code, UCH.name, NVL(TSK.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
77 FROM ahl_workorders AWO,
78 ahl_visits_b VST,
79 ahl_visit_tasks_b TSK,
80 ahl_unit_config_headers UCH,
81 ahl_mc_headers_b MCH
82 WHERE AWO.VISIT_TASK_ID=TSK.VISIT_TASK_ID
83 AND VST.VISIT_ID = TSK.VISIT_ID
84 AND AHL_UTIL_UC_PKG.GET_UC_HEADER_ID(NVL(TSK.INSTANCE_ID,VST.ITEM_INSTANCE_ID)) = UCH.unit_config_header_id
85 AND MCH.mc_header_id = UCH.master_config_id
86 AND workorder_id = c_workorder;
87
88 CURSOR c_get_route_details(c_route_id NUMBER)
89 IS
90 SELECT enigma_doc_id,model_code
91 FROM ahl_routes_b
92 WHERE route_id = c_route_id;
93 --pekambar Enigma Phase II changes -- start
94 CURSOR c_get_operation_details(c_operation_id NUMBER)
95 IS
96 SELECT enigma_doc_id,model_code
97 FROM ahl_operations_b
98 WHERE operation_id = c_operation_id;
99
100 CURSOR c_get_document_details(c_doc_revision_id NUMBER)
101 IS
102 SELECT enigma_doc_id,enigma_model_code
103 FROM AHL_DOC_REVISIONS_B
104 WHERE doc_revision_id = c_doc_revision_id;
105
106 --apattark added for bug #9901534
107 CURSOR get_wo_model_csr (c_workorder NUMBER)
108 IS
109 SELECT ro.model_code
110 FROM ahl_routes_b ro, ahl_workorders wo
111 WHERE wo.route_id = ro.route_id
112 AND WO.workorder_id = c_workorder;
113 --pekambar Enigma Phase II changes -- end
114
115 p_relationship_id NUMBER;
116 l_instance_id NUMBER;
117 l_rel_id NUMBER;
118 l_dummy VARCHAR2(40);
119
120 BEGIN
121 IF (l_log_statement >= l_log_current_level) THEN
122 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Entered');
123 END IF;
124
125 -- log the input
126 IF (l_log_statement >= l_log_current_level) THEN
127 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'p_primary_object_id:' || p_primary_object_id);
128 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'p_secondary_object_id:'||p_secondary_object_id);
129 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'p_object_type:'||p_object_type);
130 END IF;
131
132
133 -- Object Type is MC and the object id is relationship id
134 IF (p_object_type = 'MC') THEN
135 IF (l_log_statement >= l_log_current_level) THEN
136 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Object Type is MC');
137 END IF;
138
139 OPEN get_model_and_ata_csr(p_primary_object_id);
140 FETCH get_model_and_ata_csr INTO x_model,x_ata_code;
141 CLOSE get_model_and_ata_csr;
142
143 x_tail_number := '';
144 x_doc_id := replace(x_ata_code,'-');
145 IF (l_log_statement >= l_log_current_level) THEN
146 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Model->' || x_model);
147 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'ATA Code->' || x_ata_code);
148 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Tail Number->' || x_tail_number);
149 END IF;
150
151 -- SATHAPLI::Enigma code changes, 19-Sep-2008 - UC handling is done in the class AhlEnigmaDocHelper itself.
152 /*
153 -- object type is UC
154 ELSIF (p_object_type = 'UC') THEN
155 -- Use instance id to get the details
156 IF (l_log_statement >= l_log_current_level) THEN
157 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Object Type is UC');
158 END IF;
159 IF (p_primary_object_id IS NOT NULL AND p_primary_object_id <> 0) THEN
160 IF (l_log_statement >= l_log_current_level) THEN
161 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Instance ID will be used to get the details');
162 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'p_primary_object_id->' || p_primary_object_id);
163 END IF;
164 x_tail_number := AHL_UTIL_UC_PKG.get_unit_name(p_primary_object_id);
165 p_relationship_id := AHL_UTIL_UC_PKG.Map_Instance_to_RelID(p_primary_object_id);
166 OPEN get_model_and_ata_csr(p_relationship_id);
167 FETCH get_model_and_ata_csr INTO x_model,x_ata_code;
168 CLOSE get_model_and_ata_csr;
169 x_doc_id := replace(x_ata_code,'-');
170 IF (l_log_statement >= l_log_current_level) THEN
171 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Relationship Id->' || p_relationship_id);
172 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Model->' || x_model);
173 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'ATA Code->' || x_ata_code);
174 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Tail Number->' || x_tail_number);
175 END IF;
176 ELSE
177 -- There is no instance at this node, which means we will have to show the details for the complete UC
178 IF (l_log_statement >= l_log_current_level) THEN
179 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Node has no instance,UC header id will be used');
180 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'p_secondary_object_id->' || p_secondary_object_id);
181 END IF;
182 --x_doc_id := replace(x_ata_code,'-');
183 --*******************************
184 --mpothuku changed on 29-Dec-06
185 --For now if the instance is not present, we represent the ATA Code as 11, we essentially need to retrieve the
186 --relationship_id in this case as well. But we are planning to do this at a later point
187 x_doc_id := 11;
188 OPEN get_model_and_tail_csr(p_secondary_object_id);
189 FETCH get_model_and_tail_csr INTO x_tail_number,x_model;
190 CLOSE get_model_and_tail_csr;
191 IF (l_log_statement >= l_log_current_level) THEN
192 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Model->' || x_model);
193 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'ATA Code->' || x_ata_code);
194 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Tail Number->' || x_tail_number);
195 END IF;
196 END IF;
197 */
198
199 ELSIF (p_object_type = 'WO' OR p_object_type = 'WO_AMM') THEN
200 IF (l_log_statement >= l_log_current_level) THEN
201 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Object Type is WO');
202 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Workorder Id is ->' || p_primary_object_id);
203 END IF;
204
205 -- get the doc id
206 OPEN get_doc_id(p_primary_object_id);
207 FETCH get_doc_id INTO x_doc_id;
208 IF get_doc_id%NOTFOUND THEN
209 x_doc_id := NULL;
210 END IF;
211 IF (l_log_statement >= l_log_current_level) THEN
212 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Document Id is->' || x_doc_id);
213 END IF;
214
215 -- get the model
216 OPEN get_wo_model_tail_csr(p_primary_object_id);
217 FETCH get_wo_model_tail_csr INTO x_model,x_tail_number,l_instance_id;
218 IF get_wo_model_tail_csr%NOTFOUND THEN
219 x_model := NULL;
220 x_tail_number := NULL;
221 l_instance_id := NULL;
222 END IF;
223 IF l_instance_id IS NOT NULL THEN
224 l_rel_id := AHL_UTIL_UC_PKG.Map_Instance_to_RelID(l_instance_id);
225 OPEN get_model_and_ata_csr(l_rel_id);
226 FETCH get_model_and_ata_csr INTO x_model,x_ata_code;
227 CLOSE get_model_and_ata_csr;
228 END IF;
229
230 --apattark added for bug #9901534
231 IF (p_object_type = 'WO_AMM') THEN
232 OPEN get_wo_model_csr(p_primary_object_id);
233 FETCH get_wo_model_csr INTO x_model;
234 IF get_wo_model_csr%NOTFOUND THEN
235 x_model := NULL;
236 END IF;
237 CLOSE get_wo_model_tail_csr;
238 END IF;
239
240 IF (l_log_statement >= l_log_current_level) THEN
241 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'x_model ->' || x_model);
242 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'x_tail_number ->' || x_tail_number);
243 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'l_instance_id ->' || l_instance_id);
244 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'x_ata_code ->' || x_ata_code);
245 END IF;
246
247 ELSIF (p_object_type = 'WO_MC_DOC') THEN
248 IF (l_log_statement >= l_log_current_level) THEN
249 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Object Type is WO');
250 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Workorder Id is ->' || p_primary_object_id);
251 END IF;
252
253 -- get the model
254 OPEN get_wo_model_tail_csr(p_primary_object_id);
255 FETCH get_wo_model_tail_csr INTO x_model,x_tail_number,l_instance_id;
256 IF get_wo_model_tail_csr%NOTFOUND THEN
257 x_model := NULL;
258 x_tail_number := NULL;
259 l_instance_id := NULL;
260 END IF;
261 IF l_instance_id IS NOT NULL THEN
262 l_rel_id := AHL_UTIL_UC_PKG.Map_Instance_to_RelID(l_instance_id);
263 OPEN get_model_and_ata_csr(l_rel_id);
264 FETCH get_model_and_ata_csr INTO x_model,x_ata_code;
265 CLOSE get_model_and_ata_csr;
266 END IF;
267
268 x_doc_id := replace(x_ata_code,'-');
269
270 IF (l_log_statement >= l_log_current_level) THEN
271 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'x_model ->' || x_model);
272 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'x_doc_id ->' || x_doc_id);
273 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'l_rel_id ->' || l_rel_id);
274 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'x_tail_number ->' || x_tail_number);
275 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'l_instance_id ->' || l_instance_id);
276 END IF;
277
278 ELSIF (p_object_type = 'RT') THEN
279 IF (l_log_statement >= l_log_current_level) THEN
280 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Object Type is RT');
281 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Route Id is ->' || p_primary_object_id);
282 END IF;
283 OPEN c_get_route_details(p_primary_object_id);
284 FETCH c_get_route_details INTO x_doc_id,x_model;
285 CLOSE c_get_route_details;
286 IF (l_log_statement >= l_log_current_level) THEN
287 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Doc Id is:' || x_doc_id);
288 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Model is :' || x_model);
289 END IF;
290
291 --pekambar Enigma Phase II changes -- start
292 ELSIF (p_object_type = 'OP') THEN
293 IF (l_log_statement >= l_log_current_level) THEN
294 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Object Type is OP');
295 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Operation id is ->' || p_primary_object_id);
296 END IF;
297 OPEN c_get_operation_details(p_primary_object_id);
298 FETCH c_get_operation_details INTO x_doc_id,x_model;
299 CLOSE c_get_operation_details;
300 IF (l_log_statement >= l_log_current_level) THEN
301 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Doc Id is:' || x_doc_id);
302 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Model is :' || x_model);
303 END IF;
304 ELSIF (p_object_type = 'DI') THEN
305 IF (l_log_statement >= l_log_current_level) THEN
306 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Object Type is DI');
307 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Document Revision id is ->' || p_primary_object_id);
308 END IF;
309 OPEN c_get_document_details(p_primary_object_id);
313 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Doc Id is:' || x_doc_id);
310 FETCH c_get_document_details INTO x_doc_id,x_model;
311 CLOSE c_get_document_details;
312 IF (l_log_statement >= l_log_current_level) THEN
314 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'Model is :' || x_model);
315 END IF;
316 END IF;
317 --pekambar Enigma Phase II changes -- end
318
319 OPEN get_user_and_lang;
320 FETCH get_user_and_lang INTO x_user_name,x_user_lang;
321 CLOSE get_user_and_lang;
322 IF (l_log_statement >= l_log_current_level) THEN
323 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'user name->' || x_user_name);
324 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,'user lang->' || x_user_lang);
325 END IF;
326 END get_enigma_url_params;
327
328
329 ----------------------------------------------------------------------------------------------------------
330 -- Declare Procedures --
331 ---------------------------------------------------------------------------------------------------------
332 -- Start of Comments --
333 -- Procedure name : is_task_card_enabled
334 -- Function : Returns a boolean, depending on which task card icon is shown
335 -- get_enigma_url_params Parameters:
336 -- p_workorder_id : Workorder Id
337 -- End of Comments.
338 ---------------------------------------------------------------------------------------------------------
339 FUNCTION IS_TASK_CARD_ENABLED (
340 p_workorder_id IN NUMBER)
341 RETURN VARCHAR2 IS
342 l_doc_id VARCHAR2(80);
343 l_show_task_card VARCHAR2(1) := 'Y';
344 l_dont_show_task_card VARCHAR2(1) := 'N';
345 l_amm_doc_avail VARCHAR2(80);
346 BEGIN
347 l_doc_id := NULL;
348 BEGIN
349 SELECT enigma_doc_id
350 INTO l_doc_id
351 FROM ahl_routes_b RT,ahl_workorders WO
352 WHERE WO.workorder_id = p_workorder_id
353 AND WO.route_id = RT.route_id;
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 RETURN l_dont_show_task_card;
357 WHEN OTHERS THEN
358 RETURN l_dont_show_task_card;
359 END;
360
361 IF l_doc_id IS NULL THEN
362 RETURN l_dont_show_task_card;
363 END IF;
364
365 SELECT trim(fnd_profile.value('AHL_ENIGMA_AMM_DOC_AVLBL')) INTO l_amm_doc_avail FROM dual;
366 IF l_amm_doc_avail = 'Y' THEN
367 RETURN l_show_task_card;
368 ELSE
369 RETURN
370 l_dont_show_task_card;
371 END IF;
372 END;
373 END AHL_ENIGMA_UTIL_PKG;
[Home] [Help]