1 PACKAGE BODY AHL_REPORT_UTILS_PVT AS
2 /* $Header: AHLVRPTB.pls 120.3.12020000.2 2012/12/07 14:02:56 sareepar ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_REPORT_UTILS_PVT';
4 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
5
6 ------------------------------------
7 -- Common constants and variables --
8 ------------------------------------
9 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
10 l_log_statement NUMBER := fnd_log.level_statement;
11 l_log_procedure NUMBER := fnd_log.level_procedure;
12 l_log_error NUMBER := fnd_log.level_error;
13 l_log_unexpected NUMBER := fnd_log.level_unexpected;
14 l_remaining_counter NUMBER := 3;
15 l_since_repair_counter NUMBER := 3;
16 l_spec_limit_counter NUMBER := 3;
17 l_program_subtype NUMBER := 10;
18 l_quality_plan NUMBER := 1;
19
20
21
22 --------------------------------------------------------------------
23 -- FUNCTION
24 -- Get_Restrictive_UE
25
26 -- Returns the Id of the UE whose due date is nearest in the future to sysdate.
27 --------------------------------------------------------------------
28 FUNCTION Get_Restrictive_UE(P_INSTANCE_ID IN NUMBER, P_MR_PGM_SUB_TYPE IN VARCHAR2)
29 RETURN NUMBER IS
30
31 l_get_ue_id NUMBER;
32
33 CURSOR c_get_ue_id(c_instance_id NUMBER,
34 c_mr_pgm_sub_type VARCHAR2) IS
35 SELECT ue.unit_effectivity_id
36 FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
37 WHERE ue.object_type = 'MR'
38 AND ue.MANUALLY_PLANNED_FLAG = 'N'
39 AND ue.csi_item_instance_id = C_INSTANCE_ID
40 AND ue.due_date >= sysdate
41 AND ue.status_code IS NULL
42 AND (((NVL(c_mr_pgm_sub_type, 'ALL') = 'ALL')
43 OR((mr.program_subtype_code = c_mr_pgm_sub_type)
44 AND mr.mr_header_id = ue.mr_header_id
45 AND trunc(nvl(mr.effective_from,sysdate)) <= trunc(sysdate)
46 AND trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1))))
47 AND mr.mr_status_code = 'COMPLETE')
48 AND ROWNUM < 2
49 ORDER BY ue.due_date;
50
51 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Restrictive_UE';
52 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
53 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
54
55
56
57 BEGIN
58 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
59 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function');
60 END IF;
61
62 IF (l_log_statement >= l_log_current_level) THEN
63 fnd_log.string(l_log_statement,
64 L_DEBUG,
65 'Instance Id = ' || P_INSTANCE_ID ||'Program subtype = ' || P_MR_PGM_SUB_TYPE);
66 END IF;
67 OPEN c_get_ue_id(p_instance_id, p_mr_pgm_sub_type);
68 FETCH c_get_ue_id INTO l_get_ue_id;
69 CLOSE c_get_ue_id;
70
71 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
72 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
73 END IF;
74
75 RETURN l_get_ue_id;
76 END Get_Restrictive_UE;
77
78
79 --------------------------------------------------------------------
80 -- FUNCTION
81 -- Get_Restrictive_UE_CRF
82
83 -- Returns the Id of the UE whose due date is nearest in the future to sysdate.
84 -- when MR Program subtype is fetched from the ahl parameters table
85 --------------------------------------------------------------------
86 FUNCTION Get_Restrictive_UE_CRF(p_instance_id IN NUMBER)
87 RETURN NUMBER IS
88
89 l_get_ue_id NUMBER;
90
91 CURSOR c_get_ue_id(c_instance_id NUMBER) IS
92 SELECT ue.unit_effectivity_id
93 FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
94 WHERE ue.object_type = 'MR'
95 AND ue.manually_planned_flag = 'N'
96 AND ue.csi_item_instance_id = c_instance_id
97 AND ue.due_date >= sysdate
98 AND ue.status_code IS NULL
99 AND mr.program_subtype_code in (
100 Select parameter_value
101 From ahl_parameters
102 Where parameter_source_type = 'Reports'
103 And parameter_source_sub_type = 'CRF'
104 And parameter_name like 'PROGRAM_SUBTYPE%')
105 AND mr.mr_header_id = ue.mr_header_id
106 AND trunc(nvl(mr.effective_from,sysdate)) <= trunc(sysdate)
107 AND trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1))
108 AND mr.mr_status_code = 'COMPLETE'
109 AND ROWNUM < 2
110 ORDER BY ue.due_date;
111
112 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Restrictive_UE_CRF';
113 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
114 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
115
116 BEGIN
117 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
118 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function');
119 END IF;
120
121 IF (l_log_statement >= l_log_current_level) THEN
122 fnd_log.string(l_log_statement,
123 L_DEBUG,'Instance Id = ' || P_INSTANCE_ID);
124 END IF;
125
126 OPEN c_get_ue_id(p_instance_id);
127 FETCH c_get_ue_id INTO l_get_ue_id;
128 CLOSE c_get_ue_id;
129
130 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
131 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
132 END IF;
133
134 RETURN l_get_ue_id;
135 END Get_Restrictive_UE_CRF;
136
137
138
139 --------------------------------------------------------------------
140 -- FUNCTION
141 -- Get_Remaining_Counter_UOM
142 --
143 -- To return the remaining counter value for the appropriate counter
144 --------------------------------------------------------------------
145 FUNCTION Get_Remaining_Counter_UOM(P_UE_ID IN NUMBER, P_COUNTER_INDEX IN VARCHAR2, P_REPORT_NAME IN VARCHAR2)
146 RETURN NUMBER IS
147
148 l_counter_id NUMBER;
149 --l_name VARCHAR2(80);
150 l_due_counter_value NUMBER;
151 l_item_instance_id NUMBER;
152 l_net_reading NUMBER;
153 l_get_rem_uom NUMBER;
154 --l_uom_code VARCHAR2(3);
155
156 CURSOR c_get_counter_id(c_counter_index VARCHAR2,c_report_name VARCHAR2 ) IS
157 SELECT TO_NUMBER(parameter_value)
158 FROM AHL_PARAMETERS
159 WHERE parameter_name = c_counter_index
160 AND parameter_source_type = 'Reports'
161 AND parameter_source_sub_type = c_report_name;
162
163
164 CURSOR c_get_due_val(c_ue_id NUMBER, c_counter_id VARCHAR2) IS
165 SELECT UE.DUE_COUNTER_VALUE, UE.CSI_ITEM_INSTANCE_ID
166 FROM AHL_MR_INTERVALS AMI, AHL_UNIT_EFFECTIVITIES_B UE
167 WHERE AMI.COUNTER_ID = c_counter_id
168 AND AMI.MR_INTERVAL_ID = UE.MR_INTERVAL_ID
169 AND UE.unit_effectivity_id = c_ue_id;
170
171 CURSOR c_get_net_reading(c_item_instance_id NUMBER,c_counter_id VARCHAR2 ) IS
172 SELECT ccr.net_reading
173 FROM csi_counter_readings ccr, CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
174 WHERE C.CREATED_FROM_COUNTER_TMPL_ID = c_counter_id and
175 C.COUNTER_ID = CCA.COUNTER_ID(+) and
176 CCA.SOURCE_OBJECT_CODE = 'CP' and
177 CCA.SOURCE_OBJECT_ID = c_item_instance_id and
178 C.CTR_VAL_MAX_SEQ_NO = CCR.COUNTER_VALUE_ID;
179
180 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Restrictive_UE';
181 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
182 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
183
184
185 BEGIN
186 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
187 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function' || 'UE id is:'||P_UE_ID || 'counter_index is: '||p_counter_index);
188 END IF;
189
190 OPEN c_get_counter_id(p_counter_index, p_report_name);
191 FETCH c_get_counter_id INTO l_counter_id;
192 IF(c_get_counter_id%NOTFOUND) THEN
193 CLOSE c_get_counter_id;
194 --When fetched uom doesnot match with passed uom, return null
195 RETURN null;
196 ELSE
197 CLOSE c_get_counter_id;
198 END IF;
199 OPEN c_get_due_val(p_ue_id, l_counter_id );
200 FETCH c_get_due_val INTO l_due_counter_value,l_item_instance_id;
201 IF(c_get_due_val%NOTFOUND) THEN
202 CLOSE c_get_due_val;
203 --When fetched uom doesnot match with passed uom, return null
204 RETURN null;
205 ELSE
206 CLOSE c_get_due_val;
207 OPEN c_get_net_reading(l_item_instance_id,l_counter_id);
208 FETCH c_get_net_reading INTO l_net_reading;
209 CLOSE c_get_net_reading;
210
211 --When fetched uom matches with passed uom, get the difference of due counter value and net reading
212 l_get_rem_uom := l_due_counter_value - l_net_reading;
213 END IF;
214
215 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
216 fnd_log.string(fnd_log.level_procedure,L_DEBUG,'Remaining UOM is '||l_get_rem_uom);
217 END IF;
218
219 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
220 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
221 END IF;
222 RETURN l_get_rem_uom;
223
224 END Get_Remaining_Counter_UOM;
225
226
227 --------------------------------
228 -- Start of Comments --
229 -- Function name : Get_SpecLimit_Counter_Value
230 -- Type : Public
231 -- Function : Returns the spec limit counter value for the appropriate counter.
232 -- Pre-reqs :
233 -- Parameters :
234 -- p_ue_id IN NUMBER Required
235 -- p_counter_index IN NUMBER Required
236 --
237 -- End of Comments.
238 FUNCTION Get_SpecLimit_Counter_Value
239 (
240 p_ue_id IN NUMBER,
241 p_counter_index IN VARCHAR2
242 )
243 RETURN NUMBER IS
244 --
245 CURSOR c_get_counter_id (c_counter_index VARCHAR2) IS
246 SELECT TO_NUMBER(parameter_value)
247
248 FROM AHL_PARAMETERS
249
250 WHERE parameter_source_type = 'Reports'
251 AND parameter_source_sub_type = 'USN'
252 AND parameter_name = c_counter_index;
253 --
254 CURSOR c_get_speclimit_counter_value
255 ( c_ue_id NUMBER,
256 c_counter_id VARCHAR2
257 ) IS
258 SELECT AMIV.interval_value
259
260 FROM AHL_MR_INTERVALS_V AMIV,
261 AHL_UNIT_EFFECTIVITIES_B AUEB
262
263 WHERE AMIV.mr_interval_id = AUEB.mr_interval_id
264 AND AUEB.unit_effectivity_id = c_ue_id
265 AND AMIV.counter_id = c_counter_id;
266 --
267 l_api_name CONSTANT VARCHAR2(30) := 'Get_SpecLimit_Counter_Value';
268 l_full_name CONSTANT VARCHAR2(80) := G_PKG_NAME || '.' || l_api_name;
269 l_debug CONSTANT VARCHAR2(80) := 'ahl.plsql.' || l_full_name;
270
271 l_counter_id NUMBER;
272 l_speclimit_counter_value NUMBER;
273 --
274 BEGIN
275
276 IF( fnd_log.level_procedure >= fnd_log.g_current_runtime_level ) THEN
277 fnd_log.string(fnd_log.level_procedure, l_debug || '.begin', 'At the start of function');
278 fnd_log.string(fnd_log.level_procedure, l_debug, 'UE id is:' || p_ue_id || ' Counter_index is: ' || p_counter_index);
279 END IF;
280
281 -- get the counter id from AHL_PARAMETERS
282 OPEN c_get_counter_id (p_counter_index);
283 FETCH c_get_counter_id INTO l_counter_id;
284 IF( c_get_counter_id%NOTFOUND ) THEN
285 CLOSE c_get_counter_id;
286 RETURN null;
287 ELSE
288 CLOSE c_get_counter_id;
289 END IF;
290
291 -- get the interval value corresponding to the above fetched counter_id
292 OPEN c_get_speclimit_counter_value (p_ue_id, l_counter_id);
293 FETCH c_get_speclimit_counter_value INTO l_speclimit_counter_value;
294 IF( c_get_speclimit_counter_value%NOTFOUND ) THEN
295 CLOSE c_get_speclimit_counter_value;
296 RETURN null;
297 ELSE
298 CLOSE c_get_speclimit_counter_value;
299 END IF;
300
301 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
302 fnd_log.string(fnd_log.level_procedure, l_debug, 'Spec Limit Counter value is ' || l_speclimit_counter_value);
303 END IF;
304
305 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
306 fnd_log.string(fnd_log.level_procedure, l_debug || '.end', 'At the end of function');
307 END IF;
308
309 RETURN l_speclimit_counter_value;
310 END Get_SpecLimit_Counter_Value;
311
312
313 --------------------------------
314 -- Start of Comments --
315 -- Function name : Get_Since_Repair_Reading
316 -- Type : Public
317 -- Function : Returns the Since Repair counter value for the appropriate counter.
318 -- Purpose : It finds out the difference in current counter value and the counter
319 -- value of the last accomplished UE for a given item instance
320 -- Pre-reqs :
321 -- Parameters :
322 -- : p_item_instance_id IN NUMBER Required
323 -- : p_counter_index IN VARCHAR2 Required
324 -- End of Comments.
325
326 FUNCTION Get_Since_Repair_Reading(p_item_instance_id IN NUMBER,p_counter_index IN VARCHAR2)
327 RETURN NUMBER IS
328
329 l_counter_id NUMBER;
330 l_current_reading NUMBER;
331 l_last_reading NUMBER;
332 l_get_since_repair_reading NUMBER;
333 l_accomplished_date DATE;
334 l_accomplished_UE NUMBER;
335
336 CURSOR c_get_counter_id(c_counter_index VARCHAR2) IS
337 SELECT TO_NUMBER(PARAMETER_VALUE)
338 FROM AHL_PARAMETERS
339 WHERE PARAMETER_NAME = c_counter_index
340 AND PARAMETER_SOURCE_TYPE = 'Reports'
341 AND PARAMETER_SOURCE_SUB_TYPE = 'CRF';
342
343 CURSOR c_get_accomplished_UE(c_instance_id NUMBER) IS
344 SELECT * FROM (
345 SELECT AUEB.UNIT_EFFECTIVITY_ID,AUEB.ACCOMPLISHED_DATE
346 FROM AHL_UNIT_EFFECTIVITIES_B AUEB,
347 AHL_MR_HEADERS_B MRHB
348 WHERE AUEB.STATUS_CODE = 'MR-TERMINATE'
349 AND AUEB.CSI_ITEM_INSTANCE_ID = c_instance_id
350 AND MRHB.PROGRAM_SUBTYPE_CODE IN (
351 Select parameter_value
352 From ahl_parameters
353 Where parameter_source_type = 'Reports'
354 And parameter_source_sub_type = 'CRF'
355 And parameter_name like 'PROGRAM_SUBTYPE%')
356 AND AUEB.MR_HEADER_ID = MRHB.MR_HEADER_ID
357 AND trunc(nvl(MRHB.effective_from,sysdate)) <= trunc(sysdate)
358 AND trunc(sysdate) <= trunc(nvl(MRHB.effective_to,sysdate+1))
359 AND MRHB.mr_status_code = 'COMPLETE'
360 ORDER BY AUEB.ACCOMPLISHED_DATE DESC)
361 WHERE ROWNUM < 2;
362
363 CURSOR c_get_accomplished_reading( c_ue_id NUMBER, c_counter_id VARCHAR2) IS
364 SELECT AUA.COUNTER_VALUE
365 FROM AHL_UNIT_ACCOMPLISHMNTS AUA,
366 AHL_UNIT_EFFECTIVITIES_B AUEB,
367 AHL_MR_INTERVALS AMI
368 WHERE AMI.COUNTER_ID = c_counter_id
369 AND AUEB.UNIT_EFFECTIVITY_ID = c_ue_id
370 AND AUEB.MR_INTERVAL_ID = AMI.MR_INTERVAL_ID
371 AND AUA.UNIT_EFFECTIVITY_ID = AUEB.UNIT_EFFECTIVITY_ID;
372
373 CURSOR c_get_net_reading(c_item_instance_id NUMBER,c_counter_id VARCHAR2 ) IS
374 SELECT CCR.NET_READING
375 FROM CSI_COUNTER_READINGS CCR, CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
376 WHERE C.CREATED_FROM_COUNTER_TMPL_ID = c_counter_id
377 AND C.COUNTER_ID = CCA.COUNTER_ID(+)
378 AND CCA.SOURCE_OBJECT_CODE = 'CP'
379 AND CCA.SOURCE_OBJECT_ID = c_item_instance_id
380 AND C.CTR_VAL_MAX_SEQ_NO = CCR.COUNTER_VALUE_ID;
381
382 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Since_Repair_Reading';
383 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
384 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
385
386
387 BEGIN
388 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
389 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function' || 'Instance id:'||p_item_instance_id ||'counter_index is:' ||p_counter_index);
390 END IF;
391
392 OPEN c_get_counter_id(p_counter_index);
393 FETCH c_get_counter_id INTO l_counter_id;
394 IF(c_get_counter_id%NOTFOUND) THEN
395 CLOSE c_get_counter_id;
396 --When fetched uom doesnot match with passed uom, return null
397 RETURN null;
398 ELSE
399 CLOSE c_get_counter_id;
400 END IF;
401
402 OPEN c_get_accomplished_UE(p_item_instance_id);
403 FETCH c_get_accomplished_UE INTO l_accomplished_UE,l_accomplished_date;
404 IF(c_get_accomplished_UE%NOTFOUND) THEN
405 CLOSE c_get_accomplished_UE;
406 --when there is no accomlished UE for the given instance and progaram subtype return null
407 RETURN null;
408 ELSE
409 CLOSE c_get_accomplished_UE;
410 END IF;
411
412 OPEN c_get_accomplished_reading(l_accomplished_UE,l_counter_id);
413 FETCH c_get_accomplished_reading INTO l_last_reading;
414 IF(c_get_accomplished_reading%NOTFOUND) THEN
415 CLOSE c_get_accomplished_reading;
416 -- when there is no counter value found for the given UE and counter_id return null
417 RETURN null;
418 ELSE
419 CLOSE c_get_accomplished_reading;
420 OPEN c_get_net_reading(p_item_instance_id,l_counter_id);
421 FETCH c_get_net_reading INTO l_current_reading;
422 CLOSE c_get_net_reading;
423
424 --When fetched uom matches with passed uom, get the difference of accomplished counter value and net reading
425 l_get_since_repair_reading := l_current_reading - l_last_reading;
426 END IF;
427
428 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
429 fnd_log.string(fnd_log.level_procedure,L_DEBUG,'Since Repair Reading is '|| l_get_since_repair_reading);
430 END IF;
431
432 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
433 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
434 END IF;
435 RETURN l_get_since_repair_reading;
436
437 END Get_Since_Repair_Reading;
438
439 --------------------------------------------------------------------
440 -- FUNCTION
441 -- Get_Item_Group_Count
442
443 -- Returns the count of the item groups to which the given instance's item is associated
444 --------------------------------------------------------------------
445 FUNCTION Get_Item_Group_Count(P_INSTANCE_ID IN NUMBER)
446 RETURN NUMBER IS
447
448 l_get_count NUMBER;
449
450 CURSOR c_get_count(c_instance_id NUMBER) IS
451 SELECT COUNT(distinct aig.name)
452 FROM AHL_ITEM_ASSOCIATIONS_B ata,
453 AHL_ITEM_GROUPS_B aig,
454 CSI_ITEM_INSTANCES inst
455 WHERE inst.instance_id = c_instance_id
456 AND ata.inventory_item_id = inst.inventory_item_id
457 AND ata.inventory_org_id = inst.inv_master_organization_id --AJPRASAN:: Added this where clause
458 AND ata.item_group_id = aig.item_group_id
459 AND aig.status_code = 'COMPLETE'
460 AND ata.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE');
461
462 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Item_Group_Count';
463 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
464 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
465
466
467 BEGIN
468 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
469 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function');
470 END IF;
471
472 IF (l_log_statement >= l_log_current_level) THEN
473 fnd_log.string(l_log_statement,
474 L_DEBUG,
475 'Instance Id = ' || P_INSTANCE_ID);
476 END IF;
477 OPEN c_get_count(p_instance_id);
478 FETCH c_get_count INTO l_get_count;
479 CLOSE c_get_count;
480
481 IF (l_log_statement >= l_log_current_level) THEN
482 fnd_log.string(l_log_statement,L_DEBUG,'Count is = ' || l_get_count);
483 END IF;
484 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
485 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
486 END IF;
487
488 RETURN l_get_count;
489 END Get_Item_Group_Count;
490
491 --------------------------------------------------------------------
492 -- FUNCTION
493 -- Get_First_Instance
494
495 -- Returns the first instance that happens to be a unit for a given instance
496 --------------------------------------------------------------------
497 FUNCTION Get_First_Instance(P_INSTANCE_ID IN NUMBER)
498 RETURN NUMBER IS
499
500 l_get_first_instance NUMBER;
501
502 CURSOR c_get_first_instance(c_instance_id NUMBER) IS
503 SELECT UNIQUE cir.object_id INST_ID
504 FROM csi_ii_relationships cir,
505 ahl_unit_config_headers unit
506 WHERE cir.object_id IN
507 (SELECT csi_item_instance_id FROM ahl_unit_config_headers
508 )
509 AND c_instance_id NOT IN
510 (SELECT csi_item_instance_id FROM ahl_unit_config_headers
511 )
512 AND ROWNUM = 1
513 START WITH cir.subject_id = c_instance_id
514 AND cir.relationship_type_code = 'COMPONENT-OF'
515 AND NVL(cir.active_end_date, sysdate + 1) > sysdate
516 CONNECT BY cir.subject_id = PRIOR cir.object_id
517 AND cir.relationship_type_code = 'COMPONENT-OF'
518 AND NVL(cir.active_end_date, sysdate + 1) > sysdate
519
520 UNION ALL
521
522 SELECT UNIQUE cirl.object_id
523 FROM csi_ii_relationships cirl,
524 ahl_unit_config_headers auc
525 WHERE cirl.object_id = c_instance_id
526 AND cirl.relationship_type_code = 'COMPONENT-OF'
527 AND NVL(cirl.active_end_date, sysdate + 1) > sysdate
528 AND auc.csi_item_instance_id = cirl.object_id;
529
530 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_First_Instance';
531 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
532 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
533
534
535 BEGIN
536 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
537 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function');
538 END IF;
539
540 IF (l_log_statement >= l_log_current_level) THEN
541 fnd_log.string(l_log_statement,
542 L_DEBUG,
543 'Instance Id = ' || P_INSTANCE_ID);
544 END IF;
545 OPEN c_get_first_instance(p_instance_id);
546 FETCH c_get_first_instance INTO l_get_first_instance;
547 CLOSE c_get_first_instance;
548
549 IF (l_log_statement >= l_log_current_level) THEN
550 fnd_log.string(l_log_statement,L_DEBUG,'instance id is = ' || l_get_first_instance);
551 END IF;
552 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
553 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
554 END IF;
555
556 RETURN l_get_first_instance;
557 END Get_First_Instance;
558
559 --------------------------------------------------------------------
560 -- FUNCTION
561 -- Get_Root_Instance
562
563 -- Returns the root instance up the hierarchy for a given instance
564 --------------------------------------------------------------------
565 FUNCTION Get_Root_Instance(P_INSTANCE_ID IN NUMBER)
566 RETURN NUMBER IS
567
568 l_get_root_instance NUMBER;
569
570 CURSOR c_get_root_instance(c_instance_id NUMBER) IS
571 SELECT UNIQUE cir.object_id INST_ID
572 FROM csi_ii_relationships cir,
573 ahl_unit_config_headers unit
574 /*WHERE cir.object_id IN (SELECT csi_item_instance_id FROM ahl_unit_config_headers)
575 AND cir.object_id NOT IN (SELECT subject_id FROM csi_ii_relationships)*/
576 WHERE EXISTS(SELECT csi_item_instance_id FROM ahl_unit_config_headers where csi_item_instance_id = cir.object_id)
577 AND NOT EXISTS (SELECT subject_id FROM csi_ii_relationships where subject_id = cir.object_id)
578 AND ROWNUM =1
579 START WITH cir.object_id = c_instance_id
580 AND cir.relationship_type_code = 'COMPONENT-OF'
581 AND NVL(cir.active_end_date, sysdate + 1) > sysdate
582 CONNECT BY cir.subject_id = PRIOR cir.object_id
583 AND cir.relationship_type_code = 'COMPONENT-OF'
584 AND NVL(cir.active_end_date, sysdate + 1) > sysdate;
585
586 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Root_Instance';
587 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
588 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
589
590
591 BEGIN
592 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
593 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function');
594 END IF;
595
596 IF (l_log_statement >= l_log_current_level) THEN
597 fnd_log.string(l_log_statement,
598 L_DEBUG,
599 'Instance Id = ' || P_INSTANCE_ID);
600 END IF;
601 OPEN c_get_root_instance(p_instance_id);
602 FETCH c_get_root_instance INTO l_get_root_instance;
603 CLOSE c_get_root_instance;
604
605 IF (l_log_statement >= l_log_current_level) THEN
606 fnd_log.string(l_log_statement,L_DEBUG,'Instance id returned is = ' || l_get_root_instance);
607 END IF;
608 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
609 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
610 END IF;
611
612 RETURN l_get_root_instance;
613 END Get_Root_Instance;
614
615 --------------------------------------------------------------------
616 -- FUNCTION
617 -- Get_Fnd_Message
618 --------------------------------------------------------------------
619
620 FUNCTION Get_Fnd_Message(P_MESSAGE_CODE IN VARCHAR2)
621 RETURN VARCHAR2 IS
622 BEGIN
623
624 FND_MESSAGE.set_name('AHL', P_MESSAGE_CODE);
625 RETURN FND_MESSAGE.get;
626
627 END Get_Fnd_Message;
628
629 --------------------------------------------------------------------
630 -- PROCEDURE
631 -- Insert_Rem_Counters
632 --
633 -- PURPOSE
634 -- To insert/update counters values.
635 --------------------------------------------------------------------
636 PROCEDURE Insert_Rem_Counters (
637 p_api_version IN NUMBER,
638 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
639 p_commit IN VARCHAR2 := Fnd_Api.g_false,
640 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
641 p_rem_counter_tbl IN OUT NOCOPY Rem_Counter_Tbl_Type,
642 x_return_status OUT NOCOPY VARCHAR2,
643 x_msg_count OUT NOCOPY NUMBER,
644 x_msg_data OUT NOCOPY VARCHAR2
645 )
646 IS
647 -- Define local Variables
648 L_API_VERSION CONSTANT NUMBER := 1.0;
649 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Rem_Counters';
650 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
651 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
652
653 l_msg_data VARCHAR2(2000);
654 l_return_status VARCHAR2(1);
655 l_msg_count NUMBER;
656 l_counter_assoc_id Number;
657 l_parameter_desc VARCHAR2(200);
658 l_dummy NUMBER;
659
660 -- To find the next id value from counter sequence
661
662 -- To find whether id already exists
663 CURSOR c_id_exists (x_id IN NUMBER) IS
664 SELECT 1 FROM Ahl_Parameters
665 WHERE PARAMETER_ID = x_id;
666
667 -- To validate records
668 -- Duplicate counter ids not allowed for a given counter
669 CURSOR c_check_for_dup(x_parameter_name IN VARCHAR2) IS
670 SELECT count(parameter_value)
671 FROM ahl_parameters
672 WHERE parameter_source_type = 'Reports'
673 AND parameter_name like x_parameter_name
674 GROUP BY parameter_value, parameter_source_sub_type
675 HAVING ( COUNT(parameter_value) > 1 );
676
677 BEGIN
678
679 --------------------- Initialize -----------------------
680 SAVEPOINT Insert_Rem_Counters;
681
682 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
683 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
684 END IF;
685
686 -- Initialize message list if p_init_msg_list is set to TRUE.
687 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
688 Fnd_Msg_Pub.initialize;
689 END IF;
690
691 -- Initialize API return status to success
692 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
693
694 -- Standard call to check for call compatibility.
695 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
696 p_api_version,
697 l_api_name,G_PKG_NAME)
698 THEN
699 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
700 END IF;
701
702 ------------------------Start of API Body------------------------------------
703
704 IF p_rem_counter_tbl.COUNT > 0 THEN
705 FOR i IN p_rem_counter_tbl.first..p_rem_counter_tbl.LAST
706 LOOP
707 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
708 fnd_log.string(fnd_log.level_statement,L_DEBUG,'p_rem_counter_tbl(i).PARAMETER_ID : '||p_rem_counter_tbl(i).PARAMETER_ID);
709 fnd_log.string(fnd_log.level_statement,L_DEBUG,'p_rem_counter_tbl(i).counter_parameter_value : '||p_rem_counter_tbl(i).counter_parameter_value);
710 END IF;
711
712 IF p_rem_counter_tbl(i).PARAMETER_ID IS NOT NULL THEN
713
714 -- If the ID is not null, validate the ID
715 OPEN c_id_exists (p_rem_counter_tbl(i).PARAMETER_ID);
716 FETCH c_id_exists INTO l_dummy;
717 IF c_id_exists%NOTFOUND THEN
718 CLOSE c_id_exists;
719 Fnd_Message.SET_NAME('AHL','AHL_RPT_CNTR_NOT_EXT');
720 Fnd_Msg_Pub.ADD;
721 RAISE Fnd_Api.G_EXC_ERROR;
722 END IF;
723 CLOSE c_id_exists;
724 --Update the table with the new counter values
725 UPDATE ahl_parameters
726 SET --parameter_name = p_rem_counter_tbl(i).counter_parameter_name,
727 parameter_value = p_rem_counter_tbl(i).counter_parameter_value,
728 OBJECT_VERSION_NUMBER = object_version_number + 1,
729 LAST_UPDATE_DATE = SYSDATE,
730 LAST_UPDATED_BY = Fnd_Global.USER_ID,
731 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
732 WHERE parameter_id = p_rem_counter_tbl(i).PARAMETER_ID;
733 END IF;
734 END LOOP;
735
736 -- checking for duplicate entries for Remaining Counters
737 OPEN c_check_for_dup('REM_%');
738 FETCH c_check_for_dup INTO l_dummy;
739 IF c_check_for_dup%FOUND THEN
740 Fnd_Message.SET_NAME('AHL','AHL_RPT_REM_DUP_CNTR');
741 Fnd_Msg_Pub.ADD;
742 RAISE Fnd_Api.G_EXC_ERROR;
743 END IF;
744 CLOSE c_check_for_dup;
745
746 -- checking for duplicate entries for Since Repair Counters
747 OPEN c_check_for_dup('SINCE_REP_%');
748 FETCH c_check_for_dup INTO l_dummy;
749 IF c_check_for_dup%FOUND THEN
750 Fnd_Message.SET_NAME('AHL','AHL_RPT_SINCEREP_DUP_CNTR');
751 Fnd_Msg_Pub.ADD;
752 RAISE Fnd_Api.G_EXC_ERROR;
753 END IF;
754 CLOSE c_check_for_dup;
755
756 -- checking for duplicate entries for Spec Limit Counters
757 OPEN c_check_for_dup('SPECLIMIT_%');
758 FETCH c_check_for_dup INTO l_dummy;
759 IF c_check_for_dup%FOUND THEN
760 Fnd_Message.SET_NAME('AHL','AHL_RPT_SPECLIMIT_DUP_CNTR');
761 Fnd_Msg_Pub.ADD;
762 RAISE Fnd_Api.G_EXC_ERROR;
763 END IF;
764 CLOSE c_check_for_dup;
765
766
767 -- checking for duplicate entries for Program Subtype Entries
768 OPEN c_check_for_dup('PROGRAM_SUBTYPE%');
769 FETCH c_check_for_dup INTO l_dummy;
770 IF c_check_for_dup%FOUND THEN
771 Fnd_Message.SET_NAME('AHL','AHL_RPT_PST_DUP');
772 Fnd_Msg_Pub.ADD;
773 RAISE Fnd_Api.G_EXC_ERROR;
774 END IF;
775 CLOSE c_check_for_dup;
776
777 COMMIT WORK;
778 END IF;
779
780
781 --Standard check for commit
782 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
783 COMMIT;
784 END IF;
785
786 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
787 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
788 END IF;
789 EXCEPTION
790 WHEN Fnd_Api.g_exc_error THEN
791 ROLLBACK TO Insert_Rem_Counters;
792 x_return_status := Fnd_Api.g_ret_sts_error;
793 Fnd_Msg_Pub.count_and_get (
794 p_encoded => Fnd_Api.g_false,
795 p_count => x_msg_count,
796 p_data => x_msg_data
797 );
798 WHEN Fnd_Api.g_exc_unexpected_error THEN
799 ROLLBACK TO Insert_Rem_Counters;
800 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
801 Fnd_Msg_Pub.count_and_get (
802 p_encoded => Fnd_Api.g_false,
803 p_count => x_msg_count,
804 p_data => x_msg_data
805 );
806 WHEN OTHERS THEN
807 ROLLBACK TO Insert_Rem_Counters;
808 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
809 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
810 THEN
811 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
812 END IF;
813 Fnd_Msg_Pub.count_and_get (
814 p_encoded => Fnd_Api.g_false,
815 p_count => x_msg_count,
816 p_data => x_msg_data
817 );
818 END Insert_Rem_Counters;
819
820 --------------------------------------------------------------------
821 -- Procedure
822 -- Insert_Records
823 --
824 -- PURPOSE
825 -- To insert records in AHL_PARAMETERS table.
826 --------------------------------------------------------------------
827
828 PROCEDURE Insert_Records(p_report_name IN VARCHAR,p_parameter_name IN VARCHAR,p_description IN VARCHAR2,p_count IN NUMBER)
829 IS
830
831 l_parameter_name VARCHAR2(200);
832 l_description VARCHAR2(200);
833
834 BEGIN
835 -- Delete records if any exists for the given parameter name and report
836 DELETE FROM AHL_PARAMETERS
837 WHERE Parameter_Source_type = 'Reports'
838 AND Parameter_Source_Sub_Type = p_report_name
839 AND parameter_name like p_parameter_name;
840
841 -- insert records for the given parameter name
842 for i in 1..p_count loop
843 l_parameter_name := p_parameter_name || i;
844 l_description := p_description || i;
845
846 Insert into AHL_PARAMETERS (PARAMETER_ID,PARAMETER_SOURCE_TYPE,PARAMETER_SOURCE_SUB_TYPE,PARAMETER_NAME,PARAMETER_VALUE,PARAMETER_DESCRIPTION,OBJECT_VERSION_NUMBER,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
847 values (AHL_PARAMETERS_S.nextval,'Reports',p_report_name,l_parameter_name,null,l_description,1,sysdate,1,sysdate,1,1);
848
849 end loop;
850
851 END Insert_Records;
852
853 --------------------------------------------------------------------
854 -- Procedure
855 -- Insert_Ahl_Parameters
856 --
857 -- PURPOSE
858 -- To insert into AHL_PARAMETERS table.
859 --------------------------------------------------------------------
860
861 PROCEDURE Insert_Ahl_Parameters
862 IS
863 l_get_count NUMBER;
864
865 -- To find whether record already exists
866 CURSOR c_record_exists(c_report_name VARCHAR2, c_parameter_name VARCHAR2) IS
867 SELECT count(*) FROM Ahl_Parameters
868 WHERE Parameter_Source_type = 'Reports'
869 AND Parameter_Source_Sub_Type = c_report_name
870 AND parameter_name like c_parameter_name;
871
872 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Ahl_Parameters';
873 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
874 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
875
876 BEGIN
877 --------------------- Initialize -----------------------
878
879 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
880 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
881 END IF;
882
883 OPEN c_record_exists('CRF','SINCE_REP_COUNTER_%');
884 FETCH c_record_exists INTO l_get_count;
885 CLOSE c_record_exists;
886
887 IF ( l_get_count <> l_since_repair_counter) THEN
888 insert_records('CRF','SINCE_REP_COUNTER_','Since Repair Counter ',l_since_repair_counter);
889 END IF;
890
891 OPEN c_record_exists('CRF','REM_COUNTER_%');
892 FETCH c_record_exists INTO l_get_count;
893 CLOSE c_record_exists;
894
895 IF ( l_get_count <> l_remaining_counter) THEN
896 insert_records('CRF','REM_COUNTER_','Remaining Counter ',l_remaining_counter);
897 END IF;
898
899 OPEN c_record_exists('CRF','PROGRAM_SUBTYPE_%');
900 FETCH c_record_exists INTO l_get_count;
901 CLOSE c_record_exists;
902
903 IF ( l_get_count <> l_program_subtype) THEN
904 insert_records('CRF','PROGRAM_SUBTYPE_','Program Subtype ',l_program_subtype);
905 END IF;
906
907 OPEN c_record_exists('PNR','PROGRAM_SUBTYPE_%');
908 FETCH c_record_exists INTO l_get_count;
909 CLOSE c_record_exists;
910
911 IF ( l_get_count <> l_program_subtype) THEN
912 insert_records('PNR','PROGRAM_SUBTYPE_','Program Subtype ',l_program_subtype);
913 END IF;
914
915 OPEN c_record_exists('ABOM','REM_COUNTER_%');
916 FETCH c_record_exists INTO l_get_count;
917 CLOSE c_record_exists;
918
919 IF ( l_get_count <> l_remaining_counter) THEN
920 insert_records('ABOM','REM_COUNTER_','Remaining Counter ',l_remaining_counter);
921 END IF;
922
923 OPEN c_record_exists('USN','SPECLIMIT_COUNTER_%');
924 FETCH c_record_exists INTO l_get_count;
925 CLOSE c_record_exists;
926
927 IF ( l_get_count <> l_spec_limit_counter) THEN
928 insert_records('USN','SPECLIMIT_COUNTER_','Spec Limit Counter ',l_spec_limit_counter);
929 END IF;
930
931 OPEN c_record_exists('CRIH','COLLECTION_PLAN_NAME_%');
932 FETCH c_record_exists INTO l_get_count;
933 CLOSE c_record_exists;
934
935 IF ( l_get_count <> l_quality_plan) THEN
936 insert_records('CRIH','COLLECTION_PLAN_NAME_','Collection Plan Name ',l_quality_plan);
937 END IF;
938
939 END Insert_Ahl_Parameters;
940
941
942 FUNCTION Get_Date
943 RETURN VARCHAR2 AS
944 begin
945 return to_char(trunc(sysdate),fnd_date.output_mask);
946 end Get_Date;
947
948
949 --Get_Parent_UC : To retrieve the Next Highest SC.
950 --If the instance is a SC, then return the immediate parent.
951 -- else return the parent of the immediate parent.
952 --Logic: if hierarchy level is 1, Check whether the instance is a parent,
953 -- if yes, then return the immediate parent, else return the next parent.
954
955
956 FUNCTION Get_Parent_UC
957 (
958 P_INSTANCE_ID IN NUMBER)
959 RETURN VARCHAR2
960 IS
961 x_parent_mc_header_id NUMBER;
962 x_parent_uc_instance_id NUMBER;
963 l_parent_uc ahl_unit_config_headers.name%TYPE;
964 l_uc_header_id NUMBER;
965 l_instance_id NUMBER;
966 l_masterConfig NUMBER;
967
968 CURSOR get_parent_uc
969 IS
970 SELECT object_id,
971 level "HR_LEVEL"
972 FROM csi_ii_relationships
973 WHERE object_id IN
974 (SELECT csi_item_instance_id
975 FROM ahl_unit_config_headers
976 WHERE TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
977 )
978 START WITH subject_id = p_instance_id
979 AND relationship_type_code = 'COMPONENT-OF'
980 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
981 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
982 CONNECT BY subject_id = PRIOR object_id
983 AND relationship_type_code = 'COMPONENT-OF'
984 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
985 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
986
987 CURSOR get_uc_header_id(c_instance_id NUMBER)
988 IS
989 SELECT name, master_config_id
990 FROM ahl_unit_config_headers
991 WHERE csi_item_instance_id = c_instance_id
992 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
993
994 l_configHeader get_uc_header_id%ROWTYPE;
995
996 BEGIN
997 x_parent_mc_header_id := NULL;
998 x_parent_uc_instance_id := NULL;
999
1000 FOR parent_uc_rec IN get_parent_uc
1001 LOOP
1002
1003 l_instance_id := parent_uc_rec.object_id;
1004 OPEN get_uc_header_id(l_instance_id);
1005 FETCH get_uc_header_id INTO l_configHeader;
1006
1007 IF get_uc_header_id%NOTFOUND THEN
1008 x_parent_mc_header_id := NULL;
1009 x_parent_uc_instance_id := l_instance_id;
1010 l_parent_uc := NULL;
1011 ELSE
1012 x_parent_mc_header_id := l_configHeader.master_config_id;
1013 x_parent_uc_instance_id := l_instance_id;
1014 l_parent_uc := l_configHeader.name;
1015 END IF;
1016
1017 IF(parent_uc_rec.HR_LEVEL = 1) THEN
1018 IF(ahl_report_utils_pvt.get_root_instance(P_INSTANCE_ID) = x_parent_uc_instance_id) THEN
1019 x_parent_uc_instance_id := NULL;
1020 l_parent_uc := NULL;
1021 END IF;
1022 SELECT COUNT(object_id) INTO l_masterConfig FROM csi_ii_relationships WHERE object_id = P_INSTANCE_ID AND relationship_type_code = 'COMPONENT-OF';
1023 EXIT WHEN (l_masterConfig > 0);
1024 END IF;
1025
1026 CLOSE get_uc_header_id;
1027
1028 EXIT WHEN (parent_uc_rec.HR_LEVEL = 2);
1029
1030 END LOOP;
1031
1032 RETURN l_parent_uc;
1033
1034 END Get_Parent_UC;
1035
1036 FUNCTION Get_Position
1037 (
1038 P_INSTANCE_ID IN NUMBER,
1039 P_ITEM_GRP_ID IN NUMBER)
1040 RETURN VARCHAR2
1041 IS
1042 x_position fnd_lookups.Meaning%TYPE;
1043 l_position_ref_cd ahl_mc_relationships.position_ref_code%TYPE;
1044 l_master_config_id ahl_unit_config_headers.master_config_id%TYPE;
1045 l_child_inst NUMBER;
1046
1047 BEGIN
1048 x_position := NULL;
1049
1050 SELECT COUNT(subject_id) INTO l_child_inst FROM csi_ii_relationships WHERE subject_id = p_instance_id;
1051
1052 IF(l_child_inst > 0) THEN
1053 SELECT position_ref_code INTO l_position_ref_cd FROM ahl_mc_relationships WHERE relationship_id IN (SELECT position_reference FROM csi_ii_relationships WHERE subject_id = p_instance_id AND relationship_type_code = 'COMPONENT-OF') AND ROWNUM < 2;
1054 ELSE
1055 SELECT position_ref_code INTO l_position_ref_cd FROM ahl_mc_relationships WHERE mc_header_id IN (SELECT master_config_id FROM ahl_unit_config_headers WHERE csi_item_instance_id = P_INSTANCE_ID) AND item_group_id = P_ITEM_GRP_ID AND ROWNUM < 2;
1056 END IF;
1057
1058 IF(l_position_ref_cd IS NOT NULL) THEN
1059 SELECT meaning INTO x_position FROM fnd_lookups fnd WHERE fnd.lookup_type = 'AHL_POSITION_REFERENCE' AND fnd.lookup_code = l_position_ref_cd;
1060 END IF;
1061 IF(x_position IS NULL) THEN
1062 SELECT concatenated_segments INTO x_position FROM mtl_item_locations_kfv k WHERE inventory_location_id = (SELECT INV_LOCATOR_ID FROM csi_item_instances WHERE instance_id = P_INSTANCE_ID);
1063 END IF;
1064 RETURN x_position;
1065 END Get_Position;
1066
1067 END AHL_REPORT_UTILS_PVT;
1068
[Home] [Help]