1 PACKAGE BODY AHL_UTIL_UC_PKG AS
2 /* $Header: AHLUUCB.pls 120.9 2008/03/11 05:52:10 jaramana ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UTIL_UC_PKG';
5
6 G_STATUS_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
7 G_STATUS_INCOMPLETE CONSTANT VARCHAR2(30) := 'INCOMPLETE';
8 G_STATUS_EXPIRED CONSTANT VARCHAR2(30) := 'EXPIRED';
9
10 -- ACL :: Added for R12
11 G_STATUS_QUARANTINE CONSTANT VARCHAR2(30) := 'QUARANTINE';
12 G_STATUS_D_QUARANTINE CONSTANT VARCHAR2(30) := 'DEACTIVATE_QUARANTINE';
13
14
15 ----------------------------------------
16 -- Begin Local Procedures Declaration--
17 ----------------------------------------
18
19 PROCEDURE update_csi_ii_relationships(
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 p_subject_id IN NUMBER
24 );
25
26 -------------------------------------
27 -- End Local Procedures Declaration--
28 -------------------------------------
29
30 -----------------------------------------------------------
31 -- Function to get location description for csi instance --
32 -----------------------------------------------------------
33
34 FUNCTION GetCSI_LocationDesc(p_location_id IN NUMBER,
35 p_location_type_code IN VARCHAR2,
36 p_inventory_org_id IN NUMBER,
37 p_subinventory_name IN VARCHAR2,
38 p_inventory_locator_id IN NUMBER,
39 p_wip_job_id IN NUMBER)
40 RETURN VARCHAR2 IS
41
42 CURSOR mtl_item_locations_csr(p_inventory_org_id IN NUMBER,
43 p_inventory_locator_id IN NUMBER) IS
44 SELECT concatenated_segments
45 FROM mtl_item_locations_kfv
46 WHERE inventory_location_id = p_inventory_locator_id
47 AND organization_id = p_inventory_org_id;
48
49 -- Bug# 4902980 SQL id: 14398234
50 -- Commenting out Cursor ahl_owner_loc_csr and spliting it into
51 -- ahl_owner_loc_prty_csr and ahl_owner_loc_vndr_csr
52 /*
53 CURSOR ahl_owner_loc_csr (p_location_id IN NUMBER,
54 p_party_type IN VARCHAR2) IS
55 SELECT address
56 FROM ahl_owner_locations_v
57 WHERE owner_site_id = p_location_id
58 AND party_type = p_party_type;
59 */
60
61 CURSOR ahl_owner_loc_prty_csr (p_location_id NUMBER) IS
62 SELECT hzloc.address1 ||
63 decode(hzloc.address2,null,null,';'||hzloc.address2) ||
64 decode(hzloc.address3,null,null,';'||hzloc.address3) ||
65 decode(hzloc.address4,null,null,';'||hzloc.address4) ||
66 decode(hzloc.city,null,null,';'|| hzloc.city) ||
67 decode(hzloc.postal_code, null,null,';'||hzloc.postal_code) ||
68 decode(hzloc.state,null,null,';'||hzloc.state) ||
69 decode(hzloc.province,null,null,';'||hzloc.province) ||
70 hzloc.country Address
71 FROM hz_party_sites hzsite, hz_locations hzloc
72 WHERE hzsite.location_id = hzloc.location_id
73 AND hzsite.party_site_id = p_location_id
74 AND hzsite.status <> 'I';
75
76 CURSOR ahl_owner_loc_vndr_csr (p_location_id NUMBER) IS
77 SELECT decode(address_line1,null,null,address_line1) ||
78 decode(address_line2,null,null,';'||address_line2) ||
79 decode(address_line3,null,null,';'||address_line3) ||
80 decode(city,null,null, ';'||city) ||
81 decode(state,null,null,';'||state) ||
82 decode(zip,null,null,';'||zip) ||
83 decode(province,null,null,';'||province) ||
84 decode(country,null,null,';'||country) Address
85 FROM po_vendor_sites_all
86 WHERE vendor_site_id = p_location_id;
87
88 CURSOR hr_locations_csr (p_location_id IN NUMBER) IS
89 SELECT decode(address_line_1,null,null,address_line_1) ||
90 decode(address_line_2,null,null,';'||address_line_2) ||
91 decode(address_line_3, null,null,';'||address_line_3) ||
92 decode(town_or_city, null,null,';'||town_or_city) ||
93 decode(country,null,null,';'||country) Location
94 FROM hr_locations_all
95 WHERE location_id = p_location_id;
96
97 CURSOR hz_locations_csr(p_location_id IN NUMBER) IS
98 SELECT hzloc.address1 ||
99 decode(hzloc.address2,null,null,';'||hzloc.address2) ||
100 decode(hzloc.address3,null,null,';'||hzloc.address3) ||
101 decode(hzloc.address4,null,null,';'||hzloc.address4) ||
102 decode(hzloc.city,null,null,';'||hzloc.city) ||
103 decode(hzloc.postal_code, null,null,';'||hzloc.postal_code) ||
104 decode(hzloc.state,null,null,';'||hzloc.state) ||
105 decode(hzloc.province,null,null,';'||hzloc.province) ||
106 hzloc.country Address
107 FROM hz_locations hzloc
108 WHERE location_id = p_location_id;
109
110 -- if location is WIP.
111 CURSOR wip_entity_csr(p_wip_job_id IN NUMBER) IS
112 SELECT f.meaning || ';' || hou.name
113 FROM csi_lookups f, hr_all_organization_units hou, wip_entities wip_ent
114 WHERE wip_ent.organization_id = hou.organization_id
115 AND wip_ent.wip_entity_id = p_wip_job_id
116 AND f.lookup_code = 'WIP'
117 AND f.lookup_type = 'CSI_INST_LOCATION_SOURCE_CODE';
118
119 -- Get Organization name.
120 CURSOR get_org_name_csr (p_organization_id IN NUMBER) IS
121 SELECT name
122 FROM hr_all_organization_units
123 WHERE organization_id = p_organization_id;
124
125 l_concatenated_segments mtl_item_locations_kfv.concatenated_segments%TYPE;
126 l_location VARCHAR2(2000);
127 l_organization_name hr_all_organization_units.name%TYPE;
128
129
130 BEGIN
131 -- Check location type code.
132 IF (p_location_type_code = 'INVENTORY') THEN
133 -- get organization name.
134 OPEN get_org_name_csr(p_inventory_org_id);
135 FETCH get_org_name_csr INTO l_organization_name;
136 IF (get_org_name_csr%FOUND) THEN
137 IF (p_inventory_locator_id IS NOT NULL) THEN
138 OPEN mtl_item_locations_csr(p_inventory_org_id, p_inventory_locator_id);
139
140 FETCH mtl_item_locations_csr INTO l_concatenated_segments;
141 IF (mtl_item_locations_csr%FOUND) THEN
142 l_location := l_concatenated_segments;
143 END IF;
144 CLOSE mtl_item_locations_csr;
145 END IF;
146 IF (l_location IS NOT NULL) THEN
147 l_location := l_location || ';' || p_subinventory_name || ';' || l_organization_name;
148 ELSE
149 l_location := p_subinventory_name || ';' || l_organization_name;
150 END IF;
151 ELSE
152 l_location := null;
153 END IF;
154 CLOSE get_org_name_csr;
155 ELSIF (p_location_type_code = 'HZ_PARTY_SITES' OR p_location_type_code = 'VENDOR_SITE') THEN
156
157 -- Bug# 4902980 SQL id: 14398234
158 -- Commenting out Cursor usage ahl_owner_loc_csr and spliting it into
159 -- ahl_owner_loc_prty_csr and ahl_owner_loc_vndr_csr based on p_location_type_code
160 /*
161 IF (p_location_type_code = 'VENDOR_SITE') THEN
162 OPEN ahl_owner_loc_csr(p_location_id, 'VENDOR');
163 ELSE
164 OPEN ahl_owner_loc_csr(p_location_id, 'PARTY');
165 END IF;
166
167 FETCH ahl_owner_loc_csr INTO l_location;
168 IF (ahl_owner_loc_csr%NOTFOUND) THEN
169 l_location := null;
170 END IF;
171 CLOSE ahl_owner_loc_csr;
172 */
173
174 IF (p_location_type_code = 'VENDOR_SITE') THEN
175
176 OPEN ahl_owner_loc_vndr_csr(p_location_id);
177 FETCH ahl_owner_loc_vndr_csr INTO l_location;
178 IF (ahl_owner_loc_vndr_csr%NOTFOUND) THEN
179 l_location := null;
180 END IF;
181 CLOSE ahl_owner_loc_vndr_csr;
182
183 ELSE
184
185 OPEN ahl_owner_loc_prty_csr(p_location_id);
186 FETCH ahl_owner_loc_prty_csr INTO l_location;
187 IF (ahl_owner_loc_prty_csr%NOTFOUND) THEN
188 l_location := null;
189 END IF;
190 CLOSE ahl_owner_loc_prty_csr;
191
192 END IF;
193
194 ELSIF (p_location_type_code = 'HR_LOCATIONS' OR p_location_type_code = 'INTERNAL_SITE') THEN
195 OPEN hr_locations_csr(p_location_id);
196 FETCH hr_locations_csr INTO l_location;
197 IF (hr_locations_csr%NOTFOUND) THEN
198 l_location := null;
199 END IF;
200 CLOSE hr_locations_csr;
201 ELSIF (p_location_type_code = 'HZ_LOCATIONS') THEN
202 OPEN hz_locations_csr(p_location_id);
203 FETCH hz_locations_csr INTO l_location;
204 IF (hz_locations_csr%NOTFOUND) THEN
205 l_location := null;
206 END IF;
207 CLOSE hz_locations_csr;
208 ELSIF (p_location_type_code = 'WIP') THEN
209 OPEN wip_entity_csr(p_wip_job_id);
210 FETCH wip_entity_csr INTO l_location;
211 IF (wip_entity_csr%NOTFOUND) THEN
212 l_location := null;
213 END IF;
214 CLOSE wip_entity_csr;
215 ELSE
216 l_location := null;
217 END IF;
218
219 return l_location;
220
221 END GetCSI_LocationDesc;
222
223
224 ------------------------------------------------------
225 -- Function to get location code for a csi instance --
226 ------------------------------------------------------
227
228 FUNCTION GetCSI_LocationCode(p_location_id IN NUMBER,
229 p_location_type_code IN VARCHAR2)
230
231 RETURN VARCHAR2 IS
232
233 -- Bug# 4902980 SQL id: 14398234
234 -- Commenting out Cursor ahl_owner_loc_csr and spliting it into
235 -- ahl_owner_loc_prty_csr and ahl_owner_loc_vndr_csr
236 /*
237 CURSOR ahl_owner_loc_csr (p_location_id IN NUMBER,
238 p_party_type IN VARCHAR2) IS
239 SELECT owner_site_number
240 FROM ahl_owner_locations_v
241 WHERE owner_site_id = p_location_id
242 AND party_type = p_party_type;
243 */
244 CURSOR ahl_owner_loc_prty_csr (p_location_id NUMBER) IS
245 SELECT party_site_number
246 FROM hz_party_sites
247 WHERE party_site_id = p_location_id
248 AND status <> 'I';
249
250 CURSOR ahl_owner_loc_vndr_csr (p_location_id NUMBER) IS
251 SELECT vendor_site_code
252 FROM po_vendor_sites_all
253 WHERE vendor_site_id = p_location_id;
254
255 l_location VARCHAR2(2000);
256
257
258 BEGIN
259 -- Check location type code.
260 IF (p_location_type_code = 'INVENTORY') THEN
261 --l_location := p_location_type_code;
262 l_location := null;
263
264 ELSIF (p_location_type_code = 'HZ_PARTY_SITES' OR p_location_type_code = 'VENDOR_SITE') THEN
265 -- Bug# 4902980 SQL id: 14398234
266 -- Commenting out Cursor usage ahl_owner_loc_csr and spliting it into
267 -- ahl_owner_loc_prty_csr and ahl_owner_loc_vndr_csr based on p_location_type_code
268 /*
269 IF (p_location_type_code = 'VENDOR_SITE') THEN
270 OPEN ahl_owner_loc_csr(p_location_id, 'VENDOR');
271 ELSE
272 OPEN ahl_owner_loc_csr(p_location_id, 'PARTY');
273 END IF;
274
275 FETCH ahl_owner_loc_csr INTO l_location;
276 IF (ahl_owner_loc_csr%NOTFOUND) THEN
277 l_location := null;
278 END IF;
279 CLOSE ahl_owner_loc_csr;
280 */
281
282 IF (p_location_type_code = 'VENDOR_SITE') THEN
283 OPEN ahl_owner_loc_vndr_csr(p_location_id);
284 FETCH ahl_owner_loc_vndr_csr INTO l_location;
285 IF (ahl_owner_loc_vndr_csr%NOTFOUND) THEN
286 l_location := null;
287 END IF;
288 CLOSE ahl_owner_loc_vndr_csr;
289
290 ELSE
291 OPEN ahl_owner_loc_prty_csr(p_location_id);
292 FETCH ahl_owner_loc_prty_csr INTO l_location;
293 IF (ahl_owner_loc_prty_csr%NOTFOUND) THEN
294 l_location := null;
295 END IF;
296 CLOSE ahl_owner_loc_prty_csr;
297 END IF;
298
299
300
301 ELSE
302 --l_location := p_location_type_code;
303 l_location := null;
304
305 END IF;
306
307 return l_location;
308
309 END GetCSI_LocationCode;
310
311 ---------------------------------------------------------
312 -- Procedure to get CSI Transaction ID given the code --
313 ---------------------------------------------------------
314 PROCEDURE GetCSI_Transaction_ID(p_txn_code IN VARCHAR2,
315 x_txn_type_id OUT NOCOPY NUMBER,
316 x_return_val OUT NOCOPY BOOLEAN) IS
317
318 -- For transaction code.
319 CURSOR csi_txn_types_csr(p_txn_code IN VARCHAR2) IS
320 SELECT ctxn.transaction_type_id
321 FROM csi_txn_types ctxn, fnd_application app
322 WHERE ctxn.source_application_id = app.application_id
323 AND app.APPLICATION_SHORT_NAME = 'AHL'
324 AND ctxn.source_transaction_type = p_txn_code;
325
326 l_txn_type_id NUMBER;
327 l_return_val BOOLEAN DEFAULT TRUE;
328
329 BEGIN
330
331 -- get transaction_type_id .
332 OPEN csi_txn_types_csr(p_txn_code);
333 FETCH csi_txn_types_csr INTO l_txn_type_id;
334 IF (csi_txn_types_csr%NOTFOUND) THEN
335 FND_MESSAGE.Set_Name('AHL','AHL_UC_TXNCODE_INVALID');
336 FND_MESSAGE.Set_Token('CODE',p_txn_code);
337 FND_MSG_PUB.ADD;
338 --dbms_output.put_line('Transaction code not found');
339 l_return_val := FALSE;
340 END IF;
341 CLOSE csi_txn_types_csr;
342
343 -- assign out parameters.
344 x_return_val := l_return_val;
345 x_txn_type_id := l_txn_type_id;
346
347
348 END GetCSI_Transaction_ID;
349
350 ----------------------------------------------------------
351 -- Procedure to get CSI Status ID given the status-name --
352 ----------------------------------------------------------
353 PROCEDURE GetCSI_Status_ID (p_status_name IN VARCHAR2,
354 x_status_id OUT NOCOPY NUMBER,
355 x_return_val OUT NOCOPY BOOLEAN) IS
356
357 -- For instance status id.
358 CURSOR csi_instance_statuses_csr (p_status_name IN VARCHAR2) IS
359 SELECT instance_status_id
360 FROM csi_instance_statuses
361 WHERE name = p_status_name;
362
363 l_instance_status_id NUMBER;
364 l_return_val BOOLEAN DEFAULT TRUE;
365
366 BEGIN
367
368 OPEN csi_instance_statuses_csr(p_status_name);
369 FETCH csi_instance_statuses_csr INTO l_instance_status_id;
370 IF (csi_instance_statuses_csr%NOTFOUND) THEN
371 FND_MESSAGE.Set_Name('AHL','AHL_UC_INST_STATUS_MISSING');
372 FND_MESSAGE.Set_Token('CODE',p_status_name);
373 FND_MSG_PUB.ADD;
374 --dbms_output.put_line('Status code not found');
375 l_return_val := FALSE;
376 END IF;
377 CLOSE csi_instance_statuses_csr;
378
379 -- assign out parameters.
380 x_return_val := l_return_val;
381 x_status_id := l_instance_status_id;
382
383 END GetCSI_Status_ID;
384
385 ----------------------------------------------------------
386 -- Procedure to get CSI Status name given the status-id --
387 ----------------------------------------------------------
388 PROCEDURE GetCSI_Status_Name (p_status_id IN NUMBER,
389 x_status_name OUT NOCOPY VARCHAR2,
390 x_return_val OUT NOCOPY BOOLEAN) IS
391
392 -- For instance status name.
393 CURSOR csi_instance_statuses_csr (p_status_id IN NUMBER) IS
394 SELECT name
395 FROM csi_instance_statuses
396 WHERE instance_status_id = p_status_id;
397
398 l_status_name csi_instance_statuses.name%TYPE;
399 l_return_val BOOLEAN DEFAULT TRUE;
400
401 BEGIN
402
403 OPEN csi_instance_statuses_csr(p_status_id);
404 FETCH csi_instance_statuses_csr INTO l_status_name;
405 IF (csi_instance_statuses_csr%NOTFOUND) THEN
406 l_return_val := FALSE;
407 END IF;
408 CLOSE csi_instance_statuses_csr;
409
410 -- assign out parameters.
414 END GetCSI_Status_Name;
411 x_return_val := l_return_val;
412 x_status_name := l_status_name;
413
415
416 ---------------------------------------------------------------------
417 -- Procedure to get extended attribute ID given the attribute code --
418 ---------------------------------------------------------------------
419 PROCEDURE GetCSI_Attribute_ID (p_attribute_code IN VARCHAR2,
420 x_attribute_id OUT NOCOPY NUMBER,
421 x_return_val OUT NOCOPY BOOLEAN) IS
422
423
424 CURSOR csi_i_ext_attrib_csr(p_attribute_code IN VARCHAR2) IS
425 SELECT attribute_id
426 FROM csi_i_extended_attribs
427 WHERE attribute_level = 'GLOBAL'
428 AND attribute_code = p_attribute_code;
429
430 l_return_val BOOLEAN DEFAULT TRUE;
431 l_attribute_id NUMBER;
432
433 BEGIN
434
435 OPEN csi_i_ext_attrib_csr(p_attribute_code);
436 FETCH csi_i_ext_attrib_csr INTO l_attribute_id;
437 IF (csi_i_ext_attrib_csr%NOTFOUND) THEN
438 l_return_val := FALSE;
439 l_attribute_id := null;
440 END IF;
441 CLOSE csi_i_ext_attrib_csr;
442 x_attribute_id := l_attribute_id;
443 x_return_val := l_return_val;
444
445 END GetCSI_Attribute_ID;
446
447 ---------------------------------------------------------------------
448 -- Procedure to get extended attribute value given the attribute code --
449 ---------------------------------------------------------------------
450 PROCEDURE GetCSI_Attribute_Value (p_csi_instance_id IN NUMBER,
451 p_attribute_code IN VARCHAR2,
452 x_attribute_value OUT NOCOPY VARCHAR2,
453 x_attribute_value_id OUT NOCOPY NUMBER,
454 x_object_version_number OUT NOCOPY NUMBER,
455 x_return_val OUT NOCOPY BOOLEAN) IS
456
457
458 CURSOR csi_i_iea_csr(p_attribute_code IN VARCHAR2,
459 p_csi_instance_id IN NUMBER) IS
460
461 SELECT iea.attribute_value, iea.attribute_value_id, iea.object_version_number
462 FROM csi_i_extended_attribs attb, csi_iea_values iea
463 WHERE attb.attribute_id = iea.attribute_id
464 AND attb.attribute_code = p_attribute_code
465 AND iea.instance_id = p_csi_instance_id
466 AND trunc(sysdate) >= trunc(nvl(iea.active_start_date, sysdate))
467 AND trunc(sysdate) < trunc(nvl(iea.active_end_date, sysdate+1));
468
469 l_return_val BOOLEAN DEFAULT TRUE;
470 l_attribute_value csi_iea_values.attribute_value%TYPE;
471 l_attribute_value_id NUMBER;
472 l_object_version_number NUMBER;
473
474 BEGIN
475
476 OPEN csi_i_iea_csr(p_attribute_code, p_csi_instance_id);
477 FETCH csi_i_iea_csr INTO l_attribute_value, l_attribute_value_id,
478 l_object_version_number;
479 IF (csi_i_iea_csr%NOTFOUND) THEN
480 l_return_val := FALSE;
481 l_attribute_value := null;
482 l_attribute_value_id := null;
483 l_object_version_number := null;
484 END IF;
485
486 CLOSE csi_i_iea_csr;
487 x_attribute_value := l_attribute_value;
488 x_return_val := l_return_val;
489 x_attribute_value_id := l_attribute_value_id;
490 x_object_version_number := l_object_version_number;
491
492 END GetCSI_Attribute_Value;
493
494 --------------------------------------------------------------------------------
495 -- Procedure to validate csi_item_instance_id and if found return status name --
496 --------------------------------------------------------------------------------
497
498 PROCEDURE ValidateCSI_Item_Instance(p_instance_id IN NUMBER,
499 x_status_name OUT NOCOPY VARCHAR2,
500 x_location_type_code OUT NOCOPY VARCHAR2,
501 x_return_val OUT NOCOPY BOOLEAN) IS
502
503 -- For validation of csi_item_instance.
504 CURSOR csi_item_instance_csr(p_csi_item_instance_id IN NUMBER) IS
505 SELECT instance_status_id, location_type_code
506 FROM csi_item_instances csi
507 WHERE csi.instance_id = p_csi_item_instance_id;
508
509 CURSOR csi_inst_statuses_csr(p_instance_status_id IN NUMBER) IS
510 SELECT name
511 FROM csi_instance_statuses
512 WHERE instance_status_id = p_instance_status_id;
513
514 l_return_val BOOLEAN DEFAULT TRUE;
515 l_status_name csi_instance_statuses.name%TYPE DEFAULT NULL;
516 l_status_id NUMBER;
517
518 l_location_type_code csi_item_instances.location_type_code%TYPE;
519
520 BEGIN
521
522 OPEN csi_item_instance_csr(p_instance_id);
523 FETCH csi_item_instance_csr INTO l_status_id, l_location_type_code;
524 IF (csi_item_instance_csr%NOTFOUND) THEN
525 l_return_val := FALSE;
526 l_status_name := null;
527 l_location_type_code := null;
528 ELSE
529 OPEN csi_inst_statuses_csr(l_status_id);
530 FETCH csi_inst_statuses_csr INTO l_status_name;
531 IF (csi_inst_statuses_csr%NOTFOUND) THEN
532 l_status_name := null;
533 END IF;
534 CLOSE csi_inst_statuses_csr;
535 END IF;
536 CLOSE csi_item_instance_csr;
537
538 -- Set return parameters.
542
539 x_status_name := l_status_name;
540 x_return_val := l_return_val;
541 x_location_type_code := l_location_type_code;
543 END ValidateCSI_Item_Instance;
544
545 ------------------------------------------------------------------------
546 -- Procedure to return lookup meaning given the code from CSI_Lookups --
547 ------------------------------------------------------------------------
548 PROCEDURE Convert_To_CSIMeaning (p_lookup_type IN VARCHAR2,
549 p_lookup_code IN VARCHAR2,
550 x_lookup_meaning OUT NOCOPY VARCHAR2,
551 x_return_val OUT NOCOPY BOOLEAN) IS
552
553 CURSOR csi_lookup_csr (p_lookup_type IN VARCHAR2,
554 p_lookup_code IN VARCHAR2) IS
555 SELECT meaning
556 FROM csi_lookups
557 WHERE lookup_type = p_lookup_type
558 AND lookup_code = p_lookup_code
559 AND TRUNC(SYSDATE) >= TRUNC(NVL(start_date_active, SYSDATE))
560 AND TRUNC(SYSDATE) < TRUNC(NVL(end_date_active, SYSDATE+1));
561
562 l_lookup_meaning csi_lookups.meaning%TYPE DEFAULT NULL;
563 l_return_val BOOLEAN DEFAULT TRUE;
564
565 BEGIN
566
567 OPEN csi_lookup_csr(p_lookup_type, p_lookup_code);
568 FETCH csi_lookup_csr INTO l_lookup_meaning;
569 IF (csi_lookup_csr%NOTFOUND) THEN
570 l_return_val := FALSE;
571 l_lookup_meaning := NULL;
572 END IF;
573
574 CLOSE csi_lookup_csr;
575
576 x_lookup_meaning := l_lookup_meaning;
577 x_return_val := l_return_val;
578
579 END Convert_To_CSIMeaning;
580
581 ----------------------------------------------------
582 -- Procedure to check existence of a relationship --
583 -- and if found, returns the position_ref_code --
584 ----------------------------------------------------
585 Procedure ValidateMC_Relationship(p_relationship_id IN NUMBER,
586 x_position_ref_code OUT NOCOPY VARCHAR2,
587 x_return_val OUT NOCOPY BOOLEAN) IS
588
589 CURSOR l_ahl_relationship_csr(p_relationship_id IN NUMBER) IS
590 SELECT position_ref_code
591 FROM AHL_MC_RELATIONSHIPS
592 WHERE relationship_id = p_relationship_id
593 AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
594 AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1));
595
596
597 l_position_ref_code ahl_mc_relationships.position_ref_code%TYPE DEFAULT NULL;
598
599 l_return_val BOOLEAN DEFAULT TRUE;
600
601 BEGIN
602
603 OPEN l_ahl_relationship_csr(p_relationship_id);
604 FETCH l_ahl_relationship_csr INTO l_position_ref_code;
605
606 IF (l_ahl_relationship_csr%NOTFOUND) THEN
607 l_return_val := FALSE;
608 x_position_ref_code := NULL;
609 ELSE
610 x_position_ref_code := l_position_ref_code;
611 END IF;
612
613 CLOSE l_ahl_relationship_csr;
614
615 x_return_val := l_return_val;
616
617 END ValidateMC_Relationship;
618
619
620 ------------------------------------------------------------------------------
621 -- Procedure to validate if an inventory item can be assigned to a position --
622 -- Jerry made changes again to this procedure on 03/31/2005 on the basis of --
623 -- changes made on Jan. 2005
624 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
625 -- Added p_ignore_quant_vald parameter to allow the quantity checks to be ignored when called from Production.
626 -- Additionally, as non-serialized item instances with partial quantities can be assigned to MC positions now,
627 -- their quantity check with position/associated-item quantity should be relaxed from '=' to '<='.
628 --
629 -- When calling the API inv_convert.inv_um_convert, note that it returns -99999 if the UOM conversion is not possible.
630 -- We should not be considering this as item match found.
631 ------------------------------------------------------------------------------
632 PROCEDURE Validate_for_Position(p_mc_relationship_id IN NUMBER,
633 p_Inventory_id IN NUMBER,
634 p_Organization_id IN NUMBER,
635 p_quantity IN NUMBER,
636 p_revision IN VARCHAR2,
637 p_uom_code IN VARCHAR2,
638 p_position_ref_meaning IN VARCHAR2,
639 p_ignore_quant_vald IN VARCHAR2 := 'N',
640 x_item_assoc_id OUT NOCOPY NUMBER) IS
641
642 CURSOR ahl_relationships_csr (p_mc_relationship_id IN NUMBER,
643 p_Inventory_id IN NUMBER,
644 p_Organization_id IN NUMBER,
645 p_revision IN VARCHAR2) IS
646
647 SELECT iasso.quantity Itm_qty,
648 iasso.uom_code Itm_uom_code,
649 reln.quantity Posn_Qty,
650 reln.uom_code Posn_uom_code,
651 iasso.revision Itm_revision,
652 iasso.item_association_id
653 FROM ahl_mc_relationships reln, ahl_item_associations_b iasso
654 WHERE reln.item_group_id = iasso.item_group_id
655 AND reln.relationship_id = p_mc_relationship_id
656 AND iasso.inventory_item_id = p_Inventory_id
657 AND iasso.inventory_org_id = p_Organization_id
658 AND (iasso.revision IS NULL OR iasso.revision = p_revision)
659 AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
660 --Added by Jerry on 04/26/2005
661 AND trunc(sysdate) >= trunc(nvl(reln.active_start_date,sysdate))
662 AND trunc(sysdate) < trunc(nvl(reln.active_end_date, sysdate+1));
663
664 CURSOR ahl_relationships_csr1 (p_mc_relationship_id IN NUMBER,
665 p_Inventory_id IN NUMBER,
666 p_revision IN VARCHAR2) IS
667
668 SELECT iasso.quantity Itm_qty,
669 iasso.uom_code Itm_uom_code,
670 reln.quantity Posn_Qty,
671 reln.uom_code Posn_uom_code,
672 iasso.revision Itm_revision,
673 iasso.item_association_id
674 FROM ahl_mc_relationships reln, ahl_item_associations_b iasso
675 WHERE reln.item_group_id = iasso.item_group_id
676 AND reln.relationship_id = p_mc_relationship_id
677 AND iasso.inventory_item_id = p_Inventory_id
678 AND (iasso.revision IS NULL OR iasso.revision = p_revision)
679 AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
680 --Added by Jerry on 04/26/2005
681 AND trunc(sysdate) >= trunc(nvl(reln.active_start_date,sysdate))
682 AND trunc(sysdate) < trunc(nvl(reln.active_end_date, sysdate+1))
683 order by iasso.inventory_org_id;
684
685 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
686 -- Cursor to fetch the position lookup meaning for a given relationship id.
687 CURSOR get_pos_lkup_meaning(p_mc_relationship_id IN NUMBER) IS
688 SELECT fnd.lookup_code, fnd.meaning position_ref_meaning
689 FROM ahl_mc_relationships mcr, fnd_lookup_values_vl fnd
690 WHERE mcr.relationship_id = p_mc_relationship_id
691 AND mcr.position_ref_code = fnd.lookup_code
692 AND fnd.lookup_type = 'AHL_POSITION_REFERENCE';
693
694 l_Item_Posn_rec ahl_relationships_csr%ROWTYPE;
695 l_item_posn_rec1 ahl_relationships_csr1%ROWTYPE;
696 l_uom_rate NUMBER;
697 l_quantity NUMBER;
698 l_assoc_rec_found BOOLEAN DEFAULT FALSE;
699
700 l_pos_ref_code fnd_lookups.lookup_code%TYPE;
701 l_pos_ref_meaning fnd_lookups.meaning%TYPE;
702 l_debug_key VARCHAR2(60) := 'ahl.plsql.AHL_UTIL_UC_PKG.Validate_for_Position';
703
704 BEGIN
705 x_item_assoc_id := NULL;
706
707 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
708 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'p_ignore_quant_vald => '||p_ignore_quant_vald);
709 END IF;
710
711 OPEN ahl_relationships_csr(p_mc_relationship_id, p_Inventory_id, p_Organization_id, p_revision);
712 LOOP
713 FETCH ahl_relationships_csr into l_item_posn_rec;
714 EXIT WHEN (l_assoc_rec_found OR ahl_relationships_csr%NOTFOUND);
715
716 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
717 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Org check is done, p_Organization_id => '||p_Organization_id);
718 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Item Quantity => '||l_Item_Posn_rec.Itm_qty||
719 ', Position Quantity => '||l_Item_Posn_rec.posn_qty);
720 END IF;
721
722 IF (l_Item_Posn_rec.Itm_qty IS NULL OR l_Item_Posn_rec.Itm_qty = 0) THEN
723 -- position based validation.
724 IF (l_Item_Posn_rec.Posn_uom_code = p_uom_code) THEN
725 IF (nvl(p_quantity,0) <= l_Item_Posn_rec.posn_qty OR nvl(p_ignore_quant_vald,'N') = 'Y') THEN
726 l_assoc_rec_found := TRUE;
727 x_item_assoc_id := l_Item_Posn_rec.item_association_id;
728 RETURN;
729 END IF;
730 ELSE
731 -- convert quantity to position uom.
732 l_quantity := inv_convert.inv_um_convert
733 (item_id => p_Inventory_id,
734 precision => 6,
735 from_quantity => p_quantity,
736 from_unit => p_uom_code,
737 to_unit => l_Item_Posn_rec.posn_uom_code,
738 from_name => NULL,
739 to_name => NULL );
740
741 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'inv_convert.inv_um_convert returned l_quantity => '||l_quantity);
743 END IF;
744
745 IF (l_quantity >=0 AND (l_quantity <= l_Item_Posn_rec.posn_qty OR nvl(p_ignore_quant_vald,'N') = 'Y')) THEN
746 l_assoc_rec_found := TRUE;
747 x_item_assoc_id := l_Item_Posn_rec.item_association_id;
748 RETURN;
749 END IF;
750 END IF;
751 ELSE
752 -- item based validation.
753 IF (l_Item_Posn_rec.Itm_uom_code = p_uom_code) THEN
754 IF (p_quantity <= l_Item_Posn_rec.Itm_qty OR nvl(p_ignore_quant_vald,'N') = 'Y') THEN
755 l_assoc_rec_found := TRUE;
756 x_item_assoc_id := l_Item_Posn_rec.item_association_id;
757 RETURN;
758 END IF;
759 ELSE
760 -- convert quantity to Item uom.
761 l_quantity := inv_convert.inv_um_convert
762 (item_id => p_Inventory_id,
763 precision => 6,
764 from_quantity => p_quantity,
765 from_unit => p_uom_code,
766 to_unit => l_Item_Posn_rec.Itm_uom_code,
767 from_name => NULL,
768 to_name => NULL );
769
770 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
771 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'inv_convert.inv_um_convert returned l_quantity => '||l_quantity);
772 END IF;
773
774 IF (l_quantity >=0 AND (l_quantity <= l_Item_Posn_rec.Itm_qty OR nvl(p_ignore_quant_vald,'N') = 'Y' )) THEN
775 l_assoc_rec_found := TRUE;
776 x_item_assoc_id := l_Item_Posn_rec.item_association_id;
777 RETURN;
778 END IF;
779 END IF;
780 END IF;
781 END LOOP;
782 CLOSE ahl_relationships_csr;
783
784 -- Changed by jaramana for as CU2 front port for ADS bug 4414811 so that Org check will not be done.
785 -- IF (NOT l_assoc_rec_found AND (fnd_profile.value('AHL_VALIDATE_ALT_ITEM_ORG') = 'N')) THEN
786 IF (NOT l_assoc_rec_found) THEN
787 OPEN ahl_relationships_csr1(p_mc_relationship_id, p_Inventory_id, p_revision);
788 LOOP
789 FETCH ahl_relationships_csr1 into l_item_posn_rec1;
790 EXIT WHEN (l_assoc_rec_found OR ahl_relationships_csr1%NOTFOUND);
791
792 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
793 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'No Org check done');
794 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Item Quantity => '||l_Item_Posn_rec1.Itm_qty||
795 ', Position Quantity => '||l_Item_Posn_rec1.posn_qty);
796 END IF;
797
798 IF (l_Item_Posn_rec1.Itm_qty IS NULL OR l_Item_Posn_rec1.Itm_qty = 0) THEN
799 -- position based validation.
800 IF (l_Item_Posn_rec1.Posn_uom_code = p_uom_code) THEN
801 IF (nvl(p_quantity,0) <= l_Item_Posn_rec1.posn_qty OR nvl(p_ignore_quant_vald,'N') = 'Y' ) THEN
802 l_assoc_rec_found := TRUE;
803 x_item_assoc_id := l_Item_Posn_rec1.item_association_id;
804 RETURN;
805 END IF;
806 ELSE
807 -- convert quantity to position uom.
808 l_quantity := inv_convert.inv_um_convert
809 (item_id => p_Inventory_id,
810 precision => 6,
811 from_quantity => p_quantity,
812 from_unit => p_uom_code,
813 to_unit => l_Item_Posn_rec1.posn_uom_code,
814 from_name => NULL,
815 to_name => NULL );
816
817 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
818 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'inv_convert.inv_um_convert returned l_quantity => '||l_quantity);
819 END IF;
820
821 IF (l_quantity >=0 AND (l_quantity <= l_Item_Posn_rec1.posn_qty OR nvl(p_ignore_quant_vald,'N') = 'Y')) THEN
822 l_assoc_rec_found := TRUE;
823 x_item_assoc_id := l_Item_Posn_rec1.item_association_id;
824 RETURN;
825 END IF;
826 END IF;
827 ELSE
828 -- item based validation.
829 IF (l_Item_Posn_rec1.Itm_uom_code = p_uom_code) THEN
830 IF (p_quantity <= l_Item_Posn_rec1.Itm_qty OR nvl(p_ignore_quant_vald,'N') = 'Y') THEN
831 l_assoc_rec_found := TRUE;
832 x_item_assoc_id := l_Item_Posn_rec1.item_association_id;
833 RETURN;
834 END IF;
835 ELSE
836 -- convert quantity to Item uom.
837 l_quantity := inv_convert.inv_um_convert
838 (item_id => p_Inventory_id,
839 precision => 6,
840 from_quantity => p_quantity,
841 from_unit => p_uom_code,
842 to_unit => l_Item_Posn_rec1.Itm_uom_code,
843 from_name => NULL,
844 to_name => NULL );
845
846 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
847 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'inv_convert.inv_um_convert returned l_quantity => '||l_quantity);
848 END IF;
849
850 IF (l_quantity >=0 AND (l_quantity <= l_Item_Posn_rec1.Itm_qty OR nvl(p_ignore_quant_vald,'N') = 'Y')) THEN
851 l_assoc_rec_found := TRUE;
855 END IF;
852 x_item_assoc_id := l_Item_Posn_rec1.item_association_id;
853 RETURN;
854 END IF;
856 END IF;
857 END LOOP;
858 CLOSE ahl_relationships_csr1;
859 END IF;
860
861 IF NOT l_assoc_rec_found THEN
862 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
863 -- If the p_position_ref_meaning is NULL, then fetch it from the p_mc_relationship_id
864 l_pos_ref_meaning := p_position_ref_meaning;
865 IF l_pos_ref_meaning IS NULL THEN
866 OPEN get_pos_lkup_meaning(p_mc_relationship_id);
867 FETCH get_pos_lkup_meaning INTO l_pos_ref_code, l_pos_ref_meaning;
868 CLOSE get_pos_lkup_meaning;
869
870 IF(l_pos_ref_meaning IS NULL) THEN
871 l_pos_ref_meaning := l_pos_ref_code;
872 END IF;
873 END IF;
874
875 FND_MESSAGE.Set_Name('AHL','AHL_UC_INVGRP_MISMATCH');
876 FND_MESSAGE.Set_Token('POSN_REF',l_pos_ref_meaning);
877 FND_MSG_PUB.ADD;
878
879 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
880 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Item invalid for l_pos_ref_meaning => '||l_pos_ref_meaning);
881 END IF;
882 END IF;
883
884 END Validate_for_Position;
885
886
887 -------------------------------------------------------
888 -- Procedure to check if item assigned to a position --
889 -- Return TRUE if position assigned else FALSE --
890 -------------------------------------------------------
891 PROCEDURE Check_Position_Assigned (p_csi_item_instance_id IN NUMBER,
892 p_mc_relationship_id IN NUMBER,
893 x_subject_id OUT NOCOPY NUMBER,
894 x_return_val OUT NOCOPY BOOLEAN) IS
895
896 CURSOR csi_ii_relationships_csr(p_csi_item_instance_id IN NUMBER,
897 p_mc_relationship_id IN NUMBER) IS
898 SELECT subject_id
899 FROM csi_ii_relationships
900 WHERE position_reference = to_char(p_mc_relationship_id)
901 START WITH object_id = p_csi_item_instance_id
902 AND relationship_type_code = 'COMPONENT-OF'
903 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
904 CONNECT BY PRIOR subject_id = object_id
905 AND relationship_type_code = 'COMPONENT-OF'
906 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
907
908
909 l_subject_id NUMBER;
910 l_return_val BOOLEAN DEFAULT TRUE;
911
912 BEGIN
913
914 OPEN csi_ii_relationships_csr (p_csi_item_instance_id,
915 p_mc_relationship_id);
916 FETCH csi_ii_relationships_csr INTO l_subject_id;
917 IF (csi_ii_relationships_csr%NOTFOUND) THEN
918 l_return_val := FALSE;
919 l_subject_id := null;
920 END IF;
921
922 x_return_val := l_return_val;
923 x_subject_id := l_subject_id;
924
925 END Check_Position_Assigned;
926
927 -----------------------------------------------------------------------
928 -- Function will validate if an item is valid for a position or not. --
929 -- It is designed mainly to be used in SQL and views definitions. --
930 -- IT WILL IMPLICITLY INITIALIZE THE ERROR MESSAGE STACK. --
931 -- This will call Validate_for_Position procedure and will return : --
932 -- ahl_item_associations.item_association_id that has been matched --
933 -- else if no record matched, it will return 0(zero). --
934 -- OBSOLETED 10/24/2002.
935 -----------------------------------------------------------------------
936 FUNCTION Validate_Alternate_Item (p_mc_relationship_id IN NUMBER,
937 p_Inventory_id IN NUMBER,
938 p_Organization_id IN NUMBER,
939 p_quantity IN NUMBER,
940 p_revision IN VARCHAR2,
941 p_uom_code IN VARCHAR2) RETURN NUMBER IS
942
943 l_item_assoc_id NUMBER;
944 l_msg_count NUMBER;
945
946 BEGIN
947
948 -- Initialize the message stack.
949 FND_MSG_PUB.Initialize;
950
951 -- Call Validate for position procedure.
952 Validate_for_Position(p_mc_relationship_id => p_mc_relationship_id,
953 p_Inventory_id => p_Inventory_id,
954 p_Organization_id => p_Organization_id,
955 p_quantity => p_quantity,
956 p_revision => p_revision,
957 p_uom_code => p_uom_code,
958 p_position_ref_meaning => NULL,
959 x_item_assoc_id => l_item_assoc_id);
960
961 -- Check Error Message stack.
962 l_msg_count := FND_MSG_PUB.count_msg;
963 IF l_msg_count > 0 THEN
964 RETURN 0;
965 ELSE
966 RETURN l_item_assoc_id;
967 END IF;
968
969 END Validate_Alternate_Item;
970 -----------------------------
971
972 -- Procedure to match if the sub-tree below p_csi_item_instance_id matches with the
973 -- master config sub-tree below p_mc_relationship_id.
974 /* comment out by Jerry Li on 09/16/2004 for bug 3893965
978 x_match_flag OUT NOCOPY BOOLEAN)
975 PROCEDURE Match_Tree_Components (p_csi_item_instance_id IN NUMBER,
976 p_mc_relationship_id IN NUMBER,
977 x_match_part_posn_tbl OUT NOCOPY AHL_UTIL_UC_PKG.matched_tbl_type,
979 IS
980
981 -- Cursor to read the parts tree from IB.
982 CURSOR csi_part_tree_csr (p_csi_item_instance_id IN NUMBER) IS
983 SELECT object_id, subject_id, position_reference, level, relationship_id csi_ii_relationship_id,
984 object_version_number csi_ii_object_version_number
985 FROM csi_ii_relationships
986 START WITH object_id = p_csi_item_instance_id
987 AND relationship_type_code = 'COMPONENT-OF'
988 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
989 CONNECT BY PRIOR subject_id = object_id
990 AND relationship_type_code = 'COMPONENT-OF'
991 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
992 ORDER BY level;
993
994 -- Cursor to read the master config tree.
995 CURSOR ahl_relationships_csr (p_mc_relationship_id IN NUMBER) IS
996 SELECT relationship_id, position_ref_code, level
997 FROM ahl_mc_relationships
998 START WITH parent_relationship_id = p_mc_relationship_id
999 AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
1000 AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1))
1001 CONNECT BY PRIOR relationship_id = parent_relationship_id
1002 AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
1003 AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1))
1004 ORDER BY level;
1005
1006 -- Cursor to read instance details.
1007 CURSOR csi_item_instance_csr (p_csi_item_instance_id IN NUMBER) IS
1008 SELECT inventory_item_id, last_vld_organization_id, quantity, unit_of_measure,
1009 inventory_revision, instance_number
1010 FROM csi_item_instances csi
1011 WHERE instance_id = p_csi_item_instance_id;
1012
1013 -- Cursor to get position ref code.
1014 CURSOR ahl_relationships_csr1 (p_mc_relationship_id IN NUMBER) IS
1015 SELECT posn.position_ref_code, f.meaning
1016 FROM ahl_relationships_vl posn, fnd_lookups f
1017 WHERE posn.relationship_id = p_mc_relationship_id
1018 AND posn.position_ref_code = f.lookup_code;
1019
1020 -- Define part record structure.
1021 TYPE part_rec_type IS RECORD (
1022 object_id NUMBER,
1023 subject_id NUMBER,
1024 position_reference VARCHAR2(30),
1025 level NUMBER,
1026 csi_ii_relationship_id NUMBER,
1027 csi_ii_object_version NUMBER);
1028
1029 -- Define mc-position record structure.
1030 TYPE mc_posn_rec_type IS RECORD (
1031 relationship_id NUMBER,
1032 position_ref_code VARCHAR2(30),
1033 level NUMBER);
1034
1035
1036 -- Define table for part records.
1037 TYPE part_tbl_type IS TABLE OF part_rec_type INDEX BY BINARY_INTEGER;
1038
1039 -- Define table for mc-position records.
1040 TYPE mc_posn_tbl_type IS TABLE OF mc_posn_rec_type INDEX BY BINARY_INTEGER;
1041
1042 -- define variables to hold part-tree and mc-posn tree.
1043 l_part_tbl part_tbl_type;
1044 l_mc_posn_tbl mc_posn_tbl_type;
1045 l_matched_tbl AHL_UTIL_UC_PKG.matched_tbl_type;
1046
1047 l_index NUMBER;
1048
1049 l_Inventory_item_id NUMBER;
1050 l_inventory_org_id NUMBER;
1051 l_quantity NUMBER;
1052 l_inventory_revision csi_item_instances.inventory_revision%TYPE;
1053 l_uom_code csi_item_instances.unit_of_measure%TYPE;
1054 l_position_ref_meaning FND_LOOKUPS.meaning%TYPE;
1055 l_item_assoc_id NUMBER;
1056 l_part_posn_ref_code FND_LOOKUPS.lookup_code%TYPE;
1057 l_instance_number csi_item_instances.instance_number%TYPE;
1058
1059 --l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON'),'N');
1060 l_debug VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
1061
1062 BEGIN
1063
1064 -- Add debug mesg.
1065 IF (l_debug = 'Y') THEN
1066 AHL_DEBUG_PUB.debug('In Match Tree Components');
1067 END IF;
1068
1069 -- Initialize x_match_flag to true.
1070 x_match_flag := TRUE;
1071 x_match_part_posn_tbl := l_matched_tbl;
1072
1073 l_index := 0;
1074
1075 -- Build part tree array.
1076 FOR part_rec IN csi_part_tree_csr(p_csi_item_instance_id) LOOP
1077 l_index := l_index + 1;
1078 l_part_tbl(l_index) := part_rec;
1079 END LOOP; -- end part tree.
1080
1081 -- Add debug mesg.
1082 IF (l_debug = 'Y') THEN
1083 AHL_DEBUG_PUB.debug('Part tree:' || l_part_tbl.count);
1084 END IF;
1085
1086 -- Check if the instance has any children.
1087 -- If there are no children, return as there is nothing to match.
1088 IF (l_part_tbl.COUNT <= 0) THEN
1089 RETURN;
1090 END IF;
1091
1092 l_index := 0;
1093
1094 -- Build mc-tree array.
1095 FOR mc_posn_rec IN ahl_relationships_csr(p_mc_relationship_id) LOOP
1096 l_index := l_index + 1;
1097 l_mc_posn_tbl(l_index) := mc_posn_rec;
1098 END LOOP; -- end part tree.
1099
1100 -- Add debug mesg.
1104
1101 IF (l_debug = 'Y') THEN
1102 AHL_DEBUG_PUB.debug('mc tree:' || l_mc_posn_tbl.count);
1103 END IF;
1105 IF (l_mc_posn_tbl.COUNT = 0) THEN
1106 -- raise error if there are parts in the part-tree but the mc-tree
1107 -- has no children.
1108 -- Get instance number to display error message.
1109 OPEN csi_item_instance_csr (p_csi_item_instance_id);
1110 FETCH csi_item_instance_csr INTO l_inventory_item_id, l_inventory_org_id, l_quantity, l_uom_code,
1111 l_inventory_revision, l_instance_number;
1112 IF (csi_item_instance_csr%NOTFOUND) THEN
1113 l_instance_number := p_csi_item_instance_id;
1114 END IF;
1115 CLOSE csi_item_instance_csr;
1116 FND_MESSAGE.Set_Name('AHL','AHL_UC_SUBTREE_MISMATCH');
1117 FND_MESSAGE.Set_Token('INSTANCE',l_instance_number);
1118 FND_MSG_PUB.ADD;
1119 x_match_flag := FALSE;
1120 RETURN;
1121 END IF;
1122
1123 l_index := 0;
1124
1125 -- Match trees and build matched table with part and mc-position.
1126 FOR i IN l_part_tbl.FIRST..l_part_tbl.LAST LOOP
1127 -- find the part's position ref code.
1128 OPEN ahl_relationships_csr1 (to_number(l_part_tbl(i).position_reference));
1129 FETCH ahl_relationships_csr1 INTO l_part_posn_ref_code, l_position_ref_meaning;
1130 -- exit if the position is not found.
1131 IF (ahl_relationships_csr1%NOTFOUND) THEN
1132 CLOSE ahl_relationships_csr1;
1133 x_match_flag := FALSE;
1134 EXIT;
1135 END IF;
1136 CLOSE ahl_relationships_csr1;
1137
1138 -- Read MC tree for matching.
1139 FOR j IN l_mc_posn_tbl.FIRST..l_mc_posn_tbl.LAST LOOP
1140 -- if position ref code and level match then delete entries from the mc_posn table.
1141 IF (l_part_posn_ref_code = l_mc_posn_tbl(j).position_ref_code
1142 AND l_part_tbl(i).level = l_mc_posn_tbl(j).level) THEN
1143
1144 l_matched_tbl(i).object_id := l_part_tbl(i).object_id;
1145 l_matched_tbl(i).subject_id := l_part_tbl(i).subject_id;
1146 l_matched_tbl(i).mc_relationship_id := l_mc_posn_tbl(j).relationship_id;
1147 l_matched_tbl(i).csi_ii_relationship_id := l_part_tbl(i).csi_ii_relationship_id;
1148 l_matched_tbl(i).csi_ii_object_version := l_part_tbl(i).csi_ii_object_version;
1149
1150
1151 -- Check if this is a valid alternate item for this position.
1152 OPEN csi_item_instance_csr (l_part_tbl(i).subject_id);
1153 FETCH csi_item_instance_csr INTO l_inventory_item_id, l_inventory_org_id, l_quantity, l_uom_code,
1154 l_inventory_revision, l_instance_number;
1155 IF (csi_item_instance_csr%NOTFOUND) THEN
1156 FND_MESSAGE.Set_Name('AHL','AHL_UC_CHILD_DELETED');
1157 FND_MESSAGE.Set_Token('CHILD', l_part_tbl(i).subject_id);
1158 FND_MSG_PUB.ADD;
1159 CLOSE csi_item_instance_csr;
1160 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1161 END IF;
1162
1163 CLOSE csi_item_instance_csr;
1164
1165 -- Validate w.r.t positional attributes.
1166 AHL_UTIL_UC_PKG.Validate_for_Position(l_mc_posn_tbl(j).relationship_id,
1167 l_Inventory_Item_id,
1168 l_Inventory_Org_id,
1169 l_quantity,
1170 l_inventory_revision,
1171 l_uom_code,
1172 l_position_ref_meaning,
1173 l_item_assoc_id);
1174 IF (l_item_assoc_id IS NULL) THEN
1175 x_match_flag := FALSE;
1176 END IF;
1177
1178 -- delete.
1179 l_mc_posn_tbl.DELETE(j);
1180 l_part_tbl.DELETE(i);
1181 EXIT; -- exit mc loop as match was found.
1182
1183 ELSIF (l_part_tbl(i).level < l_mc_posn_tbl(j).level) THEN
1184 x_match_flag := FALSE;
1185 EXIT;
1186 END IF;
1187
1188 END LOOP;
1189
1190 -- Abort matching process if match has failed for a part.
1191 IF NOT(x_match_flag) THEN
1192 EXIT;
1193 END IF;
1194
1195 END LOOP;
1196
1197 -- the count on parts table should be zero else, add message to error stack.
1198 IF (l_part_tbl.COUNT <> 0) OR (x_match_flag = FALSE) THEN
1199 FND_MESSAGE.Set_Name('AHL','AHL_UC_SUBTREE_MISMATCH');
1200 -- Get instance number to display error message.
1201 OPEN csi_item_instance_csr (p_csi_item_instance_id);
1202 FETCH csi_item_instance_csr INTO l_inventory_item_id, l_inventory_org_id, l_quantity, l_uom_code,
1203 l_inventory_revision, l_instance_number;
1204 IF (csi_item_instance_csr%NOTFOUND) THEN
1205 l_instance_number := p_csi_item_instance_id;
1206 END IF;
1207 CLOSE csi_item_instance_csr;
1208 FND_MESSAGE.Set_Token('INSTANCE',l_instance_number);
1209 FND_MSG_PUB.ADD;
1210 x_match_flag := FALSE;
1211 END IF;
1212
1213 -- set return parameters.
1214 x_match_part_posn_tbl := l_matched_tbl;
1215 --dbms_output.put_line('matched table:' || x_match_part_posn_tbl.count);
1216
1217 -- Add debug mesg.
1218 IF (l_debug = 'Y') THEN
1222 for i in l_matched_tbl.first..l_matched_tbl.last loop
1219 AHL_DEBUG_PUB.debug('matched table:' || x_match_part_posn_tbl.count);
1220
1221 if (l_matched_tbl.count > 0) then
1223 AHL_DEBUG_PUB.debug(l_matched_tbl(i).subject_id);
1224 AHL_DEBUG_PUB.debug(l_matched_tbl(i).mc_relationship_id);
1225 AHL_DEBUG_PUB.debug(l_matched_tbl(i).csi_ii_relationship_id);
1226 AHL_DEBUG_PUB.debug(l_matched_tbl(i).csi_ii_object_version);
1227 end loop;
1228 end if;
1229
1230 END IF;
1231
1232 EXCEPTION
1233 -- Last position_reference not available for the part.
1234 WHEN NO_DATA_FOUND THEN
1235 OPEN csi_item_instance_csr (p_csi_item_instance_id);
1236 FETCH csi_item_instance_csr INTO l_inventory_item_id, l_inventory_org_id, l_quantity, l_uom_code,
1237 l_inventory_revision,l_instance_number;
1238 IF (csi_item_instance_csr%NOTFOUND) THEN
1239 l_instance_number := p_csi_item_instance_id;
1240 END IF;
1241 CLOSE csi_item_instance_csr;
1242 FND_MESSAGE.Set_Name('AHL','AHL_UC_SUBTREE_MISMATCH');
1243 FND_MESSAGE.Set_Token('INSTANCE',l_instance_number);
1244 FND_MSG_PUB.ADD;
1245 x_match_flag := FALSE;
1246
1247 WHEN VALUE_ERROR THEN
1248 OPEN csi_item_instance_csr (p_csi_item_instance_id);
1249 FETCH csi_item_instance_csr INTO l_inventory_item_id, l_inventory_org_id, l_quantity, l_uom_code,
1250 l_inventory_revision,l_instance_number;
1251 IF (csi_item_instance_csr%NOTFOUND) THEN
1252 l_instance_number := p_csi_item_instance_id;
1253 END IF;
1254 CLOSE csi_item_instance_csr;
1255 FND_MESSAGE.Set_Name('AHL','AHL_UC_SUBTREE_MISMATCH');
1256 FND_MESSAGE.Set_Token('INSTANCE',l_instance_number);
1257 FND_MSG_PUB.ADD;
1258 x_match_flag := FALSE;
1259
1260
1261 END Match_Tree_Components;
1262 */
1263 --------------------------------------------------------------------------------------------
1264 -- Start of Comments --
1265 --
1266 -- Procedure name : Invalidate_Instance
1267 -- Type : Private
1268 -- Function : Removes the reference to an Instance that has been deleted
1269 -- or referenced from an Item Group.
1270 -- Pre-reqs :
1271 --
1272 -- Standard IN Parameters :
1273 -- p_api_version IN NUMBER Required
1274 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
1275 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1276 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1277 -- Standard OUT Parameters :
1278 -- x_return_status OUT VARCHAR2 Required
1279 -- x_msg_count OUT NUMBER Required
1280 -- x_msg_data OUT VARCHAR2 Required
1281 --
1282 -- Invalidate_Instance parameters :
1283 -- p_instance_table IN Instance_Tbl_Type
1284 -- A table of inv item id, inv org id and item_group_id
1285 --
1286 -- History:
1287 -- 06/03/03 SBethi CREATED
1288 --
1289 -- Version :
1290 -- Initial Version 1.0
1291 --
1292 -- End of Comments.
1293 --------------------------------------------------------------------------------------------
1294
1295 PROCEDURE Invalidate_Instance(
1296 p_api_version IN NUMBER,
1297 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1298 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1299 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1300 x_return_status OUT NOCOPY VARCHAR2,
1301 x_msg_count OUT NOCOPY NUMBER,
1302 x_msg_data OUT NOCOPY VARCHAR2,
1303 p_instance_tbl IN Instance_Tbl_Type
1304 )
1305 IS
1306 --
1307 --Check if the item group, inventory and org combination are valid
1308 CURSOR check_item_group_csr(c_item_group_id IN NUMBER,
1309 c_inventory_item_id IN NUMBER,
1310 c_inventory_org_id IN NUMBER ) IS
1311 SELECT item_group_id
1312 FROM ahl_item_associations_b
1313 WHERE item_group_id = c_item_group_id
1314 AND inventory_item_id = c_inventory_item_id
1315 AND inventory_org_id = c_inventory_org_id;
1316
1317 -- Get all the positions associated to this item group.
1318 CURSOR get_associated_posns_csr(c_item_group_id IN NUMBER) IS
1319 SELECT relationship_id
1320 FROM ahl_mc_relationships rel, ahl_mc_headers_b hdr
1321 WHERE trunc(nvl(rel.active_end_date,sysdate+1)) > trunc(sysdate)
1322 AND trunc(nvl(rel.active_start_date,sysdate-1)) < trunc(sysdate)
1323 AND hdr.mc_header_id = rel.mc_header_id
1324 AND rel.item_group_id = c_item_group_id
1325 AND hdr.config_status_code not in ('EXPIRED','CLOSED');
1326
1327 -- Get item instances that match the position and inventory_item_id.
1328 CURSOR get_item_instances_csr(c_position_reference IN VARCHAR2,
1329 c_inventory_item_id IN NUMBER,
1330 c_inventory_org_id IN NUMBER) IS
1331 SELECT instance_id csi_item_instance_id, csi.object_version_number
1332 FROM csi_ii_relationships reln, csi_item_instances csi
1333 WHERE reln.subject_id = csi.instance_id
1337 AND reln.position_reference = c_position_reference
1334 AND TRUNC(SYSDATE) < TRUNC(NVL(reln.active_end_date, SYSDATE+1))
1335 AND trunc(nvl(reln.active_start_date,sysdate-1)) < trunc(sysdate)
1336 AND reln.relationship_type_code = 'COMPONENT-OF'
1338 AND csi.inventory_item_id = c_inventory_item_id
1339 AND csi.inv_master_organization_id = c_inventory_org_id;
1340
1341 -- Check top nodes of a unit or sub-unit that match.
1342 CURSOR chk_top_node_csr(c_relationship_id IN NUMBER,
1343 c_inventory_item_id IN NUMBER,
1344 c_inventory_org_id IN NUMBER) IS
1345 SELECT uc.csi_item_instance_id, uc.unit_config_header_id, uc.parent_uc_header_id, uc.unit_config_status_code
1346 FROM ahl_unit_config_headers uc, csi_item_instances csi, ahl_mc_relationships mc
1347 WHERE uc.csi_item_instance_id = csi.instance_id
1348 AND uc.master_config_id = mc.mc_header_id
1349 AND mc.relationship_id = c_relationship_id
1350 AND TRUNC(SYSDATE) < TRUNC(NVL(uc.active_end_date, SYSDATE+1))
1351 AND trunc(nvl(uc.active_start_date,sysdate-1)) < trunc(sysdate)
1352 AND csi.inventory_item_id = c_inventory_item_id
1353 AND csi.inv_master_organization_id = c_inventory_org_id;
1354 --
1355 -- Get UC header info
1356 CURSOR get_uc_header_info_csr(p_csi_item_instance_id IN NUMBER) IS
1357 SELECT uc.unit_config_header_id, uc.unit_config_status_code
1358 FROM ahl_unit_config_headers uc
1359 WHERE csi_item_instance_id in
1360 ( SELECT object_id
1361 FROM csi_ii_relationships
1362 START WITH subject_id = p_csi_item_instance_id
1363 AND relationship_type_code = 'COMPONENT-OF'
1364 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1365 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1366 CONNECT BY PRIOR object_id = subject_id
1367 AND relationship_type_code = 'COMPONENT-OF'
1368 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1369 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
1370 AND uc.parent_uc_header_id IS NULL
1371 AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
1372 AND trunc(sysdate) < trunc(nvl(uc.active_end_date, sysdate+1));
1373 --
1374 l_api_version CONSTANT NUMBER := 1.0;
1375 l_api_name CONSTANT VARCHAR2(30) := 'Invalidate_Instance';
1376
1377 l_instance_tbl Instance_Tbl_Type := p_instance_tbl;
1378 l_unit_config_header_id ahl_unit_config_headers.unit_config_header_id%TYPE;
1379 l_unit_config_status_code ahl_unit_config_headers.unit_config_status_code%TYPE;
1380 l_unitname ahl_unit_config_headers.name%TYPE;
1381
1382 l_chk_top_node_csr chk_top_node_csr%ROWTYPE;
1383 l_check_item_group_csr check_item_group_csr%ROWTYPE;
1384 l_return_status varchar2(1);
1385
1386
1387 BEGIN
1388
1389 -- Standard start of API savepoint
1390 SAVEPOINT Invalidate_Instance;
1391
1392 -- Standard call to check for call compatibility
1393 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1394 G_PKG_NAME) THEN
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1396 END IF;
1397
1398 -- Initialize message list if p_init_msg_list is set to TRUE
1399 IF FND_API.To_Boolean(p_init_msg_list) THEN
1400 FND_MSG_PUB.Initialize;
1401 END IF;
1402
1403 -- Initialize API return status to success
1404 x_return_status := FND_API.G_RET_STS_SUCCESS;
1405
1406 -- Begin Processing
1407
1408 -- Validating the instance table
1409 IF ( l_instance_tbl.count >0 ) THEN
1410
1411 --For all the records in the instance table
1412 FOR i in l_instance_tbl.FIRST..l_instance_tbl.LAST LOOP
1413
1414 OPEN check_item_group_csr(l_instance_tbl(i).item_group_id,
1415 l_instance_tbl(i).inventory_item_id,
1416 l_instance_tbl(i).inventory_org_id);
1417 FETCH check_item_group_csr into l_check_item_group_csr;
1418
1419 --Proceed if item group is found
1420 IF (check_item_group_csr%FOUND) THEN
1421
1422 --Get all the positions the item group is associated to
1423 FOR position_rec IN get_associated_posns_csr( l_instance_tbl(i).item_group_id ) LOOP
1424
1425 --Check if the item is assigned as a top node
1426 OPEN chk_top_node_csr(position_rec.relationship_id,
1427 l_instance_tbl(i).inventory_item_id,
1428 l_instance_tbl(i).inventory_org_id);
1429 FETCH chk_top_node_csr INTO l_chk_top_node_csr;
1430 IF (chk_top_node_csr%FOUND) THEN
1431
1432 -- updating active_end_date and incrementing object version number
1433 update ahl_unit_config_headers
1434 set active_end_date = sysdate,
1435 object_version_number=object_version_number+1
1436 where unit_config_header_id = l_chk_top_node_csr.unit_config_header_id;
1437
1438 --check if the unit is a sub-unit
1439 IF ( l_chk_top_node_csr.parent_uc_header_id is not null ) THEN
1440
1441 --update the csi_ii_relationships table and make it an extra node
1442 update_csi_ii_relationships(x_return_status =>x_return_status,
1443 x_msg_count => x_msg_count,
1444 x_msg_data => x_msg_data,
1448 END IF;
1445 p_subject_id=>l_chk_top_node_csr.csi_item_instance_id);
1446 IF x_msg_count > 0 THEN
1447 RAISE FND_API.G_EXC_ERROR;
1449 END IF; --parent_uc_header_id is not null
1450
1451 END IF;
1452 CLOSE chk_top_node_csr;
1453
1454
1455 --Check if the item is assigned as a component
1456 FOR item_instance_rec IN get_item_instances_csr(to_char(position_rec.relationship_id),
1457 l_instance_tbl(i).inventory_item_id,
1458 l_instance_tbl(i).inventory_org_id)
1459 LOOP
1460
1461 --update the csi_ii_relationships table and make it an extra node
1462
1463 update_csi_ii_relationships(x_return_status =>x_return_status,
1464 x_msg_count => x_msg_count,
1465 x_msg_data => x_msg_data,
1466 p_subject_id=>item_instance_rec.csi_item_instance_id);
1467 IF x_msg_count > 0 THEN
1468 RAISE FND_API.G_EXC_ERROR;
1469 END IF;
1470
1471 OPEN get_uc_header_info_csr(item_instance_rec.csi_item_instance_id );
1472 FETCH get_uc_header_info_csr into l_unit_config_header_id, l_unit_config_status_code;
1473
1474 --update status and object_version_number
1475 IF ( l_unit_config_status_code = 'APPROVAL_PENDING' ) THEN
1476 --modify UC status to APPROVAL_REJECTED
1477 update ahl_unit_config_headers
1478 set unit_config_status_code = 'APPROVAL_REJECTED',
1479 object_version_number=object_version_number+1
1480 where unit_config_header_id = l_unit_config_header_id;
1481
1482 ELSIF ( l_unit_config_status_code = 'COMPLETE' ) THEN
1483 --modify UC status to INCOMPLETE
1484 update ahl_unit_config_headers
1485 set unit_config_status_code = 'INCOMPLETE',
1486 object_version_number=object_version_number+1
1487 where unit_config_header_id = l_unit_config_header_id;
1488
1489 END IF;
1490 CLOSE get_uc_header_info_csr;
1491
1492 END LOOP; --item_instance_rec
1493
1494 END LOOP;
1495 END IF;
1496 CLOSE check_item_group_csr;
1497
1498 END LOOP;
1499 END IF; -- IF count > 0
1500
1501 --Completed Processing
1502
1503 -- Check Error Message stack.
1504 x_msg_count := FND_MSG_PUB.count_msg;
1505 IF x_msg_count > 0 THEN
1506 RAISE FND_API.G_EXC_ERROR;
1507 END IF;
1508
1509
1510 -- Standard call to get message count and if count is 1, get message info
1511 FND_MSG_PUB.Count_And_Get
1512 ( p_count => x_msg_count,
1513 p_data => x_msg_data,
1514 p_encoded => fnd_api.g_false
1515 );
1516
1517 --commit the updates
1518 IF FND_API.TO_BOOLEAN(p_commit) THEN
1519 COMMIT;
1520 END IF;
1521
1522
1523 EXCEPTION
1524 WHEN FND_API.G_EXC_ERROR THEN
1525 x_return_status := FND_API.G_RET_STS_ERROR;
1526 Rollback to Invalidate_Instance;
1527 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1528 p_data => x_msg_data,
1529 p_encoded => fnd_api.g_false);
1530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1532 Rollback to Invalidate_Instance;
1533 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1534 p_data => x_msg_data,
1535 p_encoded => fnd_api.g_false);
1536 WHEN OTHERS THEN
1537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1538 Rollback to Invalidate_Instance;
1539 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
1540 p_procedure_name => 'Invalidate_Instance',
1541 p_error_text => SQLERRM);
1542
1543 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1544 p_data => x_msg_data,
1545 p_encoded => fnd_api.g_false);
1546 END Invalidate_Instance;
1547
1548
1549
1550 PROCEDURE update_csi_ii_relationships(
1551 x_return_status OUT NOCOPY VARCHAR2,
1552 x_msg_count OUT NOCOPY NUMBER,
1553 x_msg_data OUT NOCOPY VARCHAR2,
1554 p_subject_id IN NUMBER
1555 )
1556 IS
1557 --
1558 CURSOR get_csi_record_csr(c_subject_id NUMBER) IS
1559 SELECT relationship_id, object_id, object_version_number
1560 FROM csi_ii_relationships
1561 WHERE subject_id = c_subject_id
1562 AND relationship_type_code = 'COMPONENT-OF'
1563 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1564 AND trunc(sysdate) > trunc(nvl(active_start_date, sysdate-1));
1565 --
1566 l_mc_header_count NUMBER;
1567 l_csi_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
1568 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
1569 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
1570 l_return_val boolean;
1571 l_transaction_type_id number;
1575 x_return_status := FND_API.G_RET_STS_SUCCESS;
1572 l_csi_record_csr get_csi_record_csr%ROWTYPE;
1573
1574 BEGIN
1576
1577 --Define the l_csi_transaction_rec
1578 l_csi_transaction_rec.source_transaction_date := sysdate;
1579
1580 AHL_Util_UC_Pkg.GetCSI_Transaction_ID('UC_UPDATE',l_transaction_type_id,
1581 l_return_val);
1582 IF NOT(l_return_val) THEN
1583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1584 END IF;
1585
1586 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
1587
1588 OPEN get_csi_record_csr( p_subject_id );
1589 FETCH get_csi_record_csr into l_csi_record_csr;
1590
1591 --Define the l_csi_relationship_rec
1592 l_csi_relationship_rec.relationship_id := l_csi_record_csr.relationship_id;
1593 l_csi_relationship_rec.subject_id := p_subject_id;
1594 l_csi_relationship_rec.object_id := l_csi_record_csr.object_id;
1595 l_csi_relationship_rec.object_version_number := l_csi_record_csr.object_version_number;
1596 l_csi_relationship_rec.position_reference := null;
1597 l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
1598 l_csi_relationship_tbl(1) := l_csi_relationship_rec;
1599 CLOSE get_csi_record_csr;
1600
1601 --Updating the csi_ii_relationships table
1602 CSI_II_Relationships_PUB.Update_Relationship(
1603 p_api_version => 1.0,
1604 p_init_msg_list => FND_API.G_TRUE,
1605 p_commit => FND_API.G_FALSE,
1606 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1607 p_relationship_tbl => l_csi_relationship_tbl,
1608 p_txn_rec => l_csi_transaction_rec,
1609 x_return_status => x_return_status,
1610 x_msg_count => x_msg_count,
1611 x_msg_data => x_msg_data );
1612
1613 END update_csi_ii_relationships;
1614
1615 ----------------------------
1616
1617
1618 -- Function to get the Status (Meaning) of a Unit Configuration
1619 -- This function considers if the unit is installed in another unit, if it is expired etc.
1620 -- It returns the concatenation of the status with the active status if the status
1621 -- ic Complete or Incomplete
1622 FUNCTION Get_UC_Status(p_uc_header_id IN NUMBER)
1623 RETURN VARCHAR2
1624 IS
1625
1626 -- Perf Bug Fix for - 4902980.
1627 -- Cursor uc_details_csr to be split.
1628 /*
1629 CURSOR uc_details_csr(p_uc_header_id IN NUMBER) IS
1630 SELECT UC.ROOT_UC_HEADER_ID,
1631 UC.UC_STATUS_CODE,
1632 UC.UC_STATUS,
1633 UC.ACTIVE_UC_STATUS,
1634 NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
1635 NVL(UC.INSTANCE_END_DATE, SYSDATE + 1),
1636 ROOT_UC.UNIT_CONFIG_STATUS_CODE,
1637 FL.MEANING,
1638 FLA.MEANING,
1639 NVL(ROOT_UC.ACTIVE_END_DATE, SYSDATE + 1),
1640 NVL(CSI.ACTIVE_END_DATE, SYSDATE + 1)
1641 FROM AHL_UNIT_CONFIG_HEADERS_V UC, AHL_UNIT_CONFIG_HEADERS ROOT_UC,
1642 FND_LOOKUP_VALUES_VL FL, FND_LOOKUP_VALUES_VL FLA,
1643 CSI_ITEM_INSTANCES CSI
1644 WHERE UC.UC_HEADER_ID = p_uc_header_id AND
1645 ROOT_UC.UNIT_CONFIG_HEADER_ID = UC.ROOT_UC_HEADER_ID AND
1646 ROOT_UC.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND
1647 FL.lookup_type = 'AHL_CONFIG_STATUS' AND
1648 ROOT_UC.unit_config_status_code = FL.lookup_code AND
1649 FLA.lookup_type (+) = 'AHL_CONFIG_STATUS' AND
1650 ROOT_UC.active_uc_status_code = FLA.lookup_code (+);
1651 */
1652
1653 CURSOR get_expired_value IS
1654 SELECT MEANING CsifutLookupMeaning
1655 FROM FND_LOOKUP_VALUES_VL
1656 WHERE LOOKUP_TYPE = 'AHL_CONFIG_STATUS' AND
1657 LOOKUP_CODE = G_STATUS_EXPIRED;
1658
1659 l_root_uc_header_id NUMBER;
1660 l_uc_status_code AHL_UNIT_CONFIG_HEADERS.UNIT_CONFIG_STATUS_CODE%TYPE;
1661 l_uc_status FND_LOOKUP_VALUES.MEANING%TYPE;
1662 l_uc_active_status FND_LOOKUP_VALUES.MEANING%TYPE;
1663 l_uc_end_date AHL_UNIT_CONFIG_HEADERS.ACTIVE_END_DATE%TYPE;
1664 l_uc_inst_end_date CSI_ITEM_INSTANCES.ACTIVE_END_DATE%TYPE;
1665 l_root_status_code AHL_UNIT_CONFIG_HEADERS.UNIT_CONFIG_STATUS_CODE%TYPE;
1666 l_root_status FND_LOOKUP_VALUES.MEANING%TYPE;
1667 l_root_active_status FND_LOOKUP_VALUES.MEANING%TYPE;
1668 l_root_end_date AHL_UNIT_CONFIG_HEADERS.ACTIVE_END_DATE%TYPE;
1669 l_root_inst_end_date CSI_ITEM_INSTANCES.ACTIVE_END_DATE%TYPE;
1670 l_return_value VARCHAR2(100) := null;
1671 L_DEBUG_KEY CONSTANT VARCHAR2(150) := 'ahl.plsql.AHL_UTIL_UC_PKG.Get_UC_Status';
1672
1673 BEGIN
1674
1675 -- Perf Bug Fix for - 4902980.
1676 -- Cursor uc_details_csr to be split below.
1677 /*
1678 OPEN uc_details_csr(p_uc_header_id);
1679 FETCH uc_details_csr INTO l_root_uc_header_id,
1680 l_uc_status_code,
1681 l_uc_status,
1682 l_uc_active_status,
1683 l_uc_end_date,
1684 l_uc_inst_end_date,
1685 l_root_status_code,
1686 l_root_status,
1687 l_root_active_status,
1688 l_root_end_date,
1692 RETURN l_return_value; -- Null
1689 l_root_inst_end_date;
1690 IF(uc_details_csr%NOTFOUND) THEN
1691 CLOSE uc_details_csr;
1693 END IF;
1694 CLOSE uc_details_csr;
1695 */
1696
1697 -- Fetching Root UC Header Id
1698 BEGIN
1699
1700 SELECT unit_config_header_id
1701 INTO l_root_uc_header_id
1702 FROM ahl_unit_config_headers
1703 WHERE parent_uc_header_id IS NULL
1704 START WITH unit_config_header_id = p_uc_header_id
1705 -- Commented out by jaramana on August 23, 2006 to show the status of Expired units correctly
1706 -- AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1707 CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id;
1708 -- AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1709
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 RETURN l_return_value; -- Null
1713 END;
1714
1715 -- Fetching Details for UC Id passed to the API.
1716 BEGIN
1717 SELECT UC.UNIT_CONFIG_STATUS_CODE,
1718 UCSC.MEANING,
1719 UASC.MEANING,
1720 NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
1721 NVL(CSI.active_end_date,SYSDATE + 1)
1722 INTO l_uc_status_code,
1723 l_uc_status,
1724 l_uc_active_status,
1725 l_uc_end_date,
1726 l_uc_inst_end_date
1727 FROM AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES UCSC,
1728 CSI_ITEM_INSTANCES CSI, FND_LOOKUP_VALUES UASC
1729 WHERE UC.UNIT_CONFIG_HEADER_ID = p_uc_header_id
1730 AND UC.csi_item_instance_id = CSI.instance_id
1731 AND UC.unit_config_status_code = UCSC.lookup_code
1732 AND 'AHL_CONFIG_STATUS' = UCSC.lookup_type
1733 AND UCSC.language = USERENV('LANG')
1734 AND UC.active_uc_status_code = UASC.lookup_code (+)
1735 AND 'AHL_CONFIG_STATUS' = UASC.lookup_type (+)
1736 AND UASC.language (+) = USERENV('LANG');
1737 EXCEPTION
1738 WHEN OTHERS THEN
1739 RETURN l_return_value; -- Null
1740 END;
1741
1742 IF (l_root_uc_header_id IS NOT NULL AND l_root_uc_header_id <> p_uc_header_id) THEN
1743 -- That is a Subconfig UC Header Id is passed to the API.
1744 -- Fetch Details for the Root Node.
1745 BEGIN
1746 SELECT UC.UNIT_CONFIG_STATUS_CODE,
1747 UCSC.MEANING,
1748 UASC.MEANING,
1749 NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
1750 NVL(CSI.active_end_date,SYSDATE + 1)
1751 INTO l_root_status_code,
1752 l_root_status,
1753 l_root_active_status,
1754 l_root_end_date,
1755 l_root_inst_end_date
1756 FROM AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES UCSC,
1757 CSI_ITEM_INSTANCES CSI, FND_LOOKUP_VALUES UASC
1758 WHERE UC.UNIT_CONFIG_HEADER_ID = l_root_uc_header_id
1759 AND UC.csi_item_instance_id = CSI.instance_id
1760 AND UC.unit_config_status_code = UCSC.lookup_code
1761 AND 'AHL_CONFIG_STATUS' = UCSC.lookup_type
1762 AND UCSC.language = USERENV('LANG')
1763 AND UC.active_uc_status_code = UASC.lookup_code (+)
1764 AND 'AHL_CONFIG_STATUS' = UASC.lookup_type (+)
1765 AND UASC.language (+) = USERENV('LANG');
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 RETURN l_return_value; -- Null
1769 END;
1770
1771 ELSE
1772 -- That is a Root UC Header Id is passed to the API.
1773 -- Defaulting l_root variables to l_uc values.
1774 l_root_status_code := l_uc_status_code;
1775 l_root_status := l_uc_status;
1776 l_root_active_status := l_uc_active_status;
1777 l_root_end_date := l_uc_end_date;
1778 l_root_inst_end_date := l_uc_inst_end_date;
1779 END IF;
1780
1781 IF(l_root_uc_header_id IS NOT NULL AND l_root_uc_header_id <> p_uc_header_id) THEN
1782 -- This unit is installed under another unit
1783 -- Use the details from the root unit except itself is expired either from UC or IB
1784 IF (l_uc_end_date < SYSDATE OR l_uc_inst_end_date < SYSDATE OR l_root_end_date < SYSDATE OR l_root_inst_end_date < SYSDATE) THEN
1785 -- Expired
1786 OPEN get_expired_value;
1787 FETCH get_expired_value INTO l_return_value;
1788 CLOSE get_expired_value;
1789 -- ACL :: Added Check for Quarantine and Deactivate Quarantine Status Below
1790 ELSIF (l_root_status_code IN (G_STATUS_COMPLETE, G_STATUS_INCOMPLETE,G_STATUS_QUARANTINE,G_STATUS_D_QUARANTINE) AND
1791 l_root_active_status IS NOT NULL) THEN
1792 -- Append active status value with status value
1793 l_return_value := l_root_status || ' ' || l_root_active_status;
1794 ELSE
1795 l_return_value := l_root_status;
1796 END IF;
1797 ELSE
1798 -- This is a stand-alone unit or is installed in an IB Tree
1799 -- Use the details directly from this unit itself
1800 IF (l_uc_end_date < SYSDATE OR l_uc_inst_end_date < SYSDATE) THEN
1804 CLOSE get_expired_value;
1801 -- Expired
1802 OPEN get_expired_value;
1803 FETCH get_expired_value INTO l_return_value;
1805 -- ACL :: Added Check for Quarantine and Deactivate Quarantine Status Below
1806 ELSIF (l_uc_status_code IN (G_STATUS_COMPLETE, G_STATUS_INCOMPLETE,G_STATUS_QUARANTINE,G_STATUS_D_QUARANTINE) AND
1807 l_uc_active_status IS NOT NULL) THEN
1808 -- Append active status value with status value
1809 l_return_value := l_uc_status || ' ' || l_uc_active_status;
1810 ELSE
1811 l_return_value := l_uc_status;
1812 END IF;
1813 END IF;
1814 RETURN l_return_value;
1815 END Get_UC_Status;
1816
1817 -- Added by Jerry on 03/29/2005 in order for fixing a VWP bug 4251688(Siberian)
1818 -- Function to get the Status (code) of a Unit Configuration
1819 -- This function considers if the unit is installed in another unit, if it is expired etc.
1820 -- This function is similar to the previous one but this one returns code instead of
1821 -- meaning. It doesn't check the active status.
1822 FUNCTION Get_UC_Status_code(p_uc_header_id IN NUMBER)
1823 RETURN VARCHAR2
1824 IS
1825 -- Perf Bug Fix for - 4902980.
1826 -- Cursor uc_details_csr to be split.
1827 /*
1828 CURSOR uc_details_csr(p_uc_header_id IN NUMBER) IS
1829 SELECT UC.ROOT_UC_HEADER_ID,
1830 UC.UC_STATUS_CODE,
1831 UC.UC_STATUS,
1832 NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
1833 NVL(UC.INSTANCE_END_DATE, SYSDATE + 1),
1834 ROOT_UC.UNIT_CONFIG_STATUS_CODE,
1835 FL.meaning,
1836 NVL(ROOT_UC.ACTIVE_END_DATE, SYSDATE + 1),
1837 NVL(CSI.ACTIVE_END_DATE, SYSDATE + 1)
1838 FROM AHL_UNIT_CONFIG_HEADERS_V UC, AHL_UNIT_CONFIG_HEADERS ROOT_UC,
1839 CSI_ITEM_INSTANCES CSI, FND_LOOKUP_VALUES_VL FL
1840 WHERE UC.UC_HEADER_ID = p_uc_header_id AND
1841 ROOT_UC.UNIT_CONFIG_HEADER_ID = UC.ROOT_UC_HEADER_ID AND
1842 ROOT_UC.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND
1843 ROOT_UC.UNIT_CONFIG_STATUS_CODE = FL.LOOKUP_CODE AND
1844 FL.LOOKUP_TYPE = 'AHL_CONFIG_STATUS';
1845 */
1846
1847 l_root_uc_header_id NUMBER;
1848 l_uc_status_code AHL_UNIT_CONFIG_HEADERS.UNIT_CONFIG_STATUS_CODE%TYPE;
1849 l_uc_status FND_LOOKUP_VALUES.MEANING%TYPE;
1850 l_uc_end_date AHL_UNIT_CONFIG_HEADERS.ACTIVE_END_DATE%TYPE;
1851 l_uc_inst_end_date CSI_ITEM_INSTANCES.ACTIVE_END_DATE%TYPE;
1852 l_root_status_code AHL_UNIT_CONFIG_HEADERS.UNIT_CONFIG_STATUS_CODE%TYPE;
1853 l_root_status FND_LOOKUP_VALUES.MEANING%TYPE;
1854 l_root_end_date AHL_UNIT_CONFIG_HEADERS.ACTIVE_END_DATE%TYPE;
1855 l_root_inst_end_date CSI_ITEM_INSTANCES.ACTIVE_END_DATE%TYPE;
1856 l_return_value VARCHAR2(100) := null;
1857 L_DEBUG_KEY CONSTANT VARCHAR2(150) := 'ahl.plsql.AHL_UTIL_UC_PKG.Get_UC_Status_code';
1858
1859 BEGIN
1860
1861 -- Perf Bug Fix for - 4902980.
1862 -- Cursor uc_details_csr to be split below.
1863 /*
1864 OPEN uc_details_csr(p_uc_header_id);
1865 FETCH uc_details_csr INTO l_root_uc_header_id,
1866 l_uc_status_code,
1867 l_uc_status,
1868 l_uc_end_date,
1869 l_uc_inst_end_date,
1870 l_root_status_code,
1871 l_root_status,
1872 l_root_end_date,
1873 l_root_inst_end_date;
1874 IF(uc_details_csr%NOTFOUND) THEN
1875 CLOSE uc_details_csr;
1876 RETURN l_return_value; -- Null
1877 END IF;
1878 CLOSE uc_details_csr;
1879 */
1880
1881
1882 -- Fetching Root UC Header Id
1883 BEGIN
1884
1885 SELECT unit_config_header_id
1886 INTO l_root_uc_header_id
1887 FROM ahl_unit_config_headers
1888 WHERE parent_uc_header_id IS NULL
1889 START WITH unit_config_header_id = p_uc_header_id
1890 -- Commented out by jaramana on August 23, 2006 to show the status of Expired units correctly
1891 -- AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1892 CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id;
1893 -- AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1894
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 RETURN l_return_value; -- Null
1898 END;
1899
1900 -- Fetching Details for UC Id passed to the API.
1901 BEGIN
1902 SELECT UC.UNIT_CONFIG_STATUS_CODE,
1903 UCSC.MEANING,
1904 NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
1905 NVL(CSI.active_end_date,SYSDATE + 1)
1906 INTO l_uc_status_code,
1907 l_uc_status,
1908 l_uc_end_date,
1909 l_uc_inst_end_date
1910 FROM AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES UCSC,
1911 CSI_ITEM_INSTANCES CSI
1912 WHERE UC.UNIT_CONFIG_HEADER_ID = p_uc_header_id
1913 AND UC.csi_item_instance_id = CSI.instance_id
1914 AND UC.unit_config_status_code = UCSC.lookup_code
1915 AND 'AHL_CONFIG_STATUS' = UCSC.lookup_type
1916 AND UCSC.language = USERENV('LANG');
1917 EXCEPTION
1918 WHEN OTHERS THEN
1919 RETURN l_return_value; -- Null
1920 END;
1921
1922 IF (l_root_uc_header_id IS NOT NULL AND l_root_uc_header_id <> p_uc_header_id) THEN
1926 SELECT UC.UNIT_CONFIG_STATUS_CODE,
1923 -- That is a Subconfig UC Header Id is passed to the API.
1924 -- Fetch Details for the Root Node.
1925 BEGIN
1927 UCSC.MEANING,
1928 NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
1929 NVL(CSI.active_end_date,SYSDATE + 1)
1930 INTO l_root_status_code,
1931 l_root_status,
1932 l_root_end_date,
1933 l_root_inst_end_date
1934 FROM AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES UCSC,
1935 CSI_ITEM_INSTANCES CSI
1936 WHERE UC.UNIT_CONFIG_HEADER_ID = l_root_uc_header_id
1937 AND UC.csi_item_instance_id = CSI.instance_id
1938 AND UC.unit_config_status_code = UCSC.lookup_code
1939 AND 'AHL_CONFIG_STATUS' = UCSC.lookup_type
1940 AND UCSC.language = USERENV('LANG');
1941 EXCEPTION
1942 WHEN OTHERS THEN
1943 RETURN l_return_value; -- Null
1944 END;
1945
1946 ELSE
1947 -- That is a Root UC Header Id is passed to the API.
1948 -- Defaulting l_root variables to l_uc values.
1949 l_root_status_code := l_uc_status_code;
1950 l_root_status := l_uc_status;
1951 l_root_end_date := l_uc_end_date;
1952 l_root_inst_end_date := l_uc_inst_end_date;
1953 END IF;
1954
1955 IF(l_root_uc_header_id IS NOT NULL AND l_root_uc_header_id <> p_uc_header_id) THEN
1956 -- This unit is installed under another unit
1957 -- Use the details from the root unit except itself is expired either from UC or IB
1958 IF (l_uc_end_date < SYSDATE OR l_uc_inst_end_date < SYSDATE OR l_root_end_date < SYSDATE OR l_root_inst_end_date < SYSDATE) THEN
1959 -- Expired
1960 l_return_value := G_STATUS_EXPIRED;
1961 ELSE
1962 l_return_value := l_root_status_code;
1963 END IF;
1964 ELSE
1965 -- This is a stand-alone unit or is installed in an IB Tree
1966 -- Use the details directly from this unit itself
1967 IF (l_uc_end_date < SYSDATE OR l_uc_inst_end_date < SYSDATE) THEN
1968 -- Expired
1969 l_return_value := G_STATUS_EXPIRED;
1970 ELSE
1971 l_return_value := l_uc_status_code;
1972 END IF;
1973 END IF;
1974 RETURN l_return_value;
1975 END get_uc_status_code;
1976
1977 -- Define Procedure copy_uc_header_to_history --
1978 -- This common utility API is used to copy a UC header to history table whenever this UC is just newly created
1979 -- or updated
1980 PROCEDURE copy_uc_header_to_history (
1981 p_uc_header_id IN NUMBER,
1982 x_return_status OUT NOCOPY VARCHAR2
1983 ) IS
1984 l_version_no NUMBER;
1985 CURSOR get_uc_header IS
1986 SELECT *
1987 FROM ahl_unit_config_headers
1988 WHERE unit_config_header_id = p_uc_header_id;
1989 --AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1990 --For expiration operation, when copying to history, the record has already been expired.
1991 l_uc_header_rec get_uc_header%ROWTYPE;
1992 BEGIN
1993 x_return_status := FND_API.G_RET_STS_SUCCESS;
1994 --Get the maximum version number of the particualr UC from history table
1995 SELECT nvl(max(version_no), 0) INTO l_version_no
1996 FROM ahl_uc_headers_h
1997 WHERE unit_config_header_id = p_uc_header_id;
1998
1999 -- Insert into the exactly same record into ahl_unit_config_headers_h
2000 OPEN get_uc_header;
2001 FETCH get_uc_header INTO l_uc_header_rec;
2002 IF get_uc_header%NOTFOUND THEN
2003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2004 ELSE
2005 INSERT INTO ahl_uc_headers_h(
2006 unit_config_header_id,
2007 version_no,
2008 object_version_number,
2009 creation_date,
2010 created_by,
2011 last_update_date,
2012 last_updated_by,
2013 last_update_login,
2014 name,
2015 master_config_id,
2016 csi_item_instance_id,
2017 unit_config_status_code,
2018 active_start_date,
2019 active_end_date,
2020 active_uc_status_code,
2021 parent_uc_header_id,
2022 attribute_category,
2023 attribute1,
2024 attribute2,
2025 attribute3,
2026 attribute4,
2027 attribute5,
2028 attribute6,
2029 attribute7,
2030 attribute8,
2031 attribute9,
2032 attribute10,
2033 attribute11,
2034 attribute12,
2035 attribute13,
2036 attribute14,
2037 attribute15)
2038 VALUES(
2039 l_uc_header_rec.unit_config_header_id,
2040 l_version_no+1,
2041 l_uc_header_rec.object_version_number,
2042 l_uc_header_rec.creation_date,
2043 l_uc_header_rec.created_by,
2044 l_uc_header_rec.last_update_date,
2045 l_uc_header_rec.last_updated_by,
2046 l_uc_header_rec.last_update_login,
2047 l_uc_header_rec.name,
2048 l_uc_header_rec.master_config_id,
2049 l_uc_header_rec.csi_item_instance_id,
2050 l_uc_header_rec.unit_config_status_code,
2051 l_uc_header_rec.active_start_date,
2052 l_uc_header_rec.active_end_date,
2053 l_uc_header_rec.active_uc_status_code,
2054 l_uc_header_rec.parent_uc_header_id,
2055 l_uc_header_rec.attribute_category,
2056 l_uc_header_rec.attribute1,
2057 l_uc_header_rec.attribute2,
2058 l_uc_header_rec.attribute3,
2059 l_uc_header_rec.attribute4,
2060 l_uc_header_rec.attribute5,
2061 l_uc_header_rec.attribute6,
2062 l_uc_header_rec.attribute7,
2063 l_uc_header_rec.attribute8,
2064 l_uc_header_rec.attribute9,
2065 l_uc_header_rec.attribute10,
2066 l_uc_header_rec.attribute11,
2067 l_uc_header_rec.attribute12,
2068 l_uc_header_rec.attribute13,
2069 l_uc_header_rec.attribute14,
2070 l_uc_header_rec.attribute15);
2071 END IF;
2072 CLOSE get_uc_header;
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2076 END;
2077
2078 PROCEDURE get_root_uc_attr(
2079 p_uc_header_id IN NUMBER,
2080 x_uc_header_id OUT NOCOPY NUMBER,
2081 x_instance_id OUT NOCOPY NUMBER,
2082 x_uc_status_code OUT NOCOPY VARCHAR2,
2083 x_active_uc_status_code OUT NOCOPY VARCHAR2,
2084 x_uc_header_ovn OUT NOCOPY NUMBER)
2085 IS
2086 l_uc_header_id NUMBER := NULL;
2087 l_instance_id NUMBER := NULL;
2088 l_uc_status_code fnd_lookup_values.lookup_code%TYPE := NULL;
2089 l_active_uc_status_code fnd_lookup_values.lookup_code%TYPE := NULL;
2090 l_uc_header_ovn NUMBER;
2091 CURSOR get_top_unit(c_uc_header_id NUMBER) IS
2092 SELECT unit_config_header_id,
2093 csi_item_instance_id,
2094 unit_config_status_code,
2095 active_uc_status_code,
2096 object_version_number
2097 FROM ahl_unit_config_headers
2098 WHERE parent_uc_header_id IS NULL
2099 START WITH unit_config_header_id = c_uc_header_id
2100 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2101 CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id
2102 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2103
2104 BEGIN
2105 OPEN get_top_unit(p_uc_header_id);
2106 FETCH get_top_unit INTO l_uc_header_id,
2107 l_instance_id,
2108 l_uc_status_code,
2109 l_active_uc_status_code,
2110 l_uc_header_ovn;
2111 IF get_top_unit%NOTFOUND THEN
2112 FND_MESSAGE.set_name( 'AHL','AHL_UC_API_PARAMETER_INVALID');
2113 FND_MESSAGE.set_token('NAME', 'uc_header_id');
2114 FND_MESSAGE.set_token('VALUE', p_uc_header_id);
2115 FND_MSG_PUB.add;
2116 END IF;
2117 CLOSE get_top_unit;
2118 x_uc_header_id := l_uc_header_id;
2119 x_instance_id := l_instance_id;
2120 x_uc_status_code := l_uc_status_code;
2121 x_active_uc_status_code := l_active_uc_status_code;
2122 x_uc_header_ovn := l_uc_header_ovn;
2123 END;
2124
2125 FUNCTION extra_node(p_instance_id IN NUMBER, p_top_instance_id NUMBER) RETURN BOOLEAN
2126 IS
2127 l_dummy_num NUMBER;
2128 CURSOR check_extra IS
2129 SELECT subject_id
2130 FROM csi_ii_relationships
2131 WHERE position_reference IS NULL
2132 START WITH subject_id = p_instance_id
2133 AND subject_id <> p_top_instance_id
2134 --And this one more condition just in order to avoid p_instance_id = p_top_instance_id
2135 --and it is a subunit or installed unit
2136 AND relationship_type_code = 'COMPONENT-OF'
2137 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2138 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2139 CONNECT BY subject_id = PRIOR object_id
2140 AND relationship_type_code = 'COMPONENT-OF'
2141 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2142 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2143 AND subject_id <> p_top_instance_id;
2144 BEGIN
2145 OPEN check_extra;
2146 FETCH check_extra INTO l_dummy_num;
2147 IF check_extra%FOUND THEN
2148 RETURN TRUE;
2149 ELSE
2150 RETURN FALSE;
2151 END IF;
2152 CLOSE check_extra;
2153 END;
2154
2155 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
2156 -- As non-serialized item instances with partial quantities can be assigned to MC positions now,
2157 -- their quantity check with position/associated-item quantity should be relaxed from '=' to '<='.
2158 --
2159 -- When calling the API inv_convert.inv_um_convert, note that it returns -99999 if the UOM conversion is not possible.
2160 -- We should not be considering this as item match found.
2161 FUNCTION item_match(p_mc_relationship_id IN NUMBER,
2162 p_inventory_item_id IN NUMBER,
2163 p_organization_id IN NUMBER,
2164 p_revision IN VARCHAR2,
2165 p_quantity IN NUMBER,
2166 p_uom_code IN VARCHAR2)
2167 RETURN BOOLEAN IS
2168 l_return_value BOOLEAN;
2169 CURSOR ahl_relationships_csr(c_mc_relationship_id IN NUMBER,
2170 c_inventory_item_id IN NUMBER,
2171 c_organization_id IN NUMBER) IS
2172 SELECT A.quantity item_quantity,
2173 A.uom_code item_uom_code,
2174 A.revision item_revision,
2175 R.quantity position_quantity,
2176 R.uom_code position_uom_code
2177 FROM ahl_mc_relationships R,
2178 ahl_item_associations_b A
2179 WHERE R.item_group_id = A.item_group_id
2180 AND R.relationship_id = c_mc_relationship_id
2181 AND A.inventory_item_id = c_inventory_item_id
2182 AND A.inventory_org_id = c_organization_id
2183 AND A.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
2184 --Added by Jerry on 04/26/2005
2185 AND trunc(nvl(R.active_start_date,sysdate)) <= trunc(sysdate)
2186 AND trunc(nvl(R.active_end_date, sysdate+1)) > trunc(sysdate);
2187 l_item_posn_rec ahl_relationships_csr%ROWTYPE;
2188 l_quantity NUMBER;
2189 BEGIN
2190 l_return_value := FALSE;
2191 OPEN ahl_relationships_csr(p_mc_relationship_id,
2192 p_inventory_item_id,
2193 p_organization_id);
2194 LOOP
2195 FETCH ahl_relationships_csr INTO l_item_posn_rec;
2196 EXIT WHEN ahl_relationships_csr%NOTFOUND OR l_return_value;
2197 --Validations for quantity, uom_code and revision.
2198 IF (l_item_posn_rec.item_quantity IS NULL OR l_item_posn_rec.item_quantity = 0) THEN
2199 -- position based validation.
2200 IF (l_item_posn_rec.position_uom_code = p_uom_code) THEN
2201 IF (p_quantity <= l_item_posn_rec.position_quantity) THEN
2202 l_return_value := TRUE;
2203 END IF;
2204 ELSE
2205 --Convert quantity to position uom.
2206 l_quantity := inv_convert.inv_um_convert
2207 (item_id => p_inventory_item_id,
2208 precision => 6,
2209 from_quantity => p_quantity,
2210 from_unit => p_uom_code,
2211 to_unit => l_item_posn_rec.position_uom_code,
2212 from_name => NULL,
2213 to_name => NULL );
2214 IF (l_quantity >=0 AND l_quantity <= l_item_posn_rec.position_quantity) THEN
2215 l_return_value := TRUE;
2216 END IF;
2217 END IF;
2218 ELSE
2219 --Item based validation
2220 IF (l_item_posn_rec.item_uom_code = p_uom_code) THEN
2221 IF (p_quantity <= l_item_posn_rec.item_quantity) THEN
2222 l_return_value := TRUE;
2223 END IF;
2224 ELSE
2225 --Convert quantity to item uom
2226 l_quantity := inv_convert.inv_um_convert
2227 (item_id => p_inventory_item_id,
2228 precision => 6,
2229 from_quantity => p_quantity,
2230 from_unit => p_uom_code,
2231 to_unit => l_item_posn_rec.item_uom_code,
2232 from_name => NULL,
2233 to_name => NULL);
2234 IF (l_quantity >=0 AND l_quantity <= l_item_posn_rec.item_quantity) THEN
2235 l_return_value := TRUE;
2236 END IF;
2237 END IF;
2238 END IF;
2239
2240 --Check for revision.
2241 IF (l_return_value AND l_item_posn_rec.item_revision IS NOT NULL) THEN
2242 IF (p_revision IS NULL OR (p_revision IS NOT NULL AND p_revision <> l_item_posn_rec.item_revision)) THEN
2243 l_return_value := FALSE;
2244 END IF;
2245 END IF;
2246 END LOOP;
2247
2248 RETURN l_return_value;
2249 END;
2250
2251 -- Define procedure get_parent_uc_header --
2252 -- This common utility is used to get the parent uc_header_id and parent instance_id
2253 -- for a given instance_id. This procedure always returns the parent uc_header_id and
2254 -- the instance_id of the parent_uc_header_id (not necessary to be the immediated parent
2255 -- instance_id of itself). If the given instance happens to be a standalone unit instance,
2256 -- then both the return variables will be null.
2257 PROCEDURE get_parent_uc_header(p_instance_id IN NUMBER,
2258 x_parent_uc_header_id OUT NOCOPY NUMBER,
2259 x_parent_uc_instance_id OUT NOCOPY NUMBER)
2260 IS
2261 l_uc_header_id NUMBER;
2262 l_instance_id NUMBER;
2263 CURSOR get_parent_uc IS
2264 SELECT object_id
2265 FROM csi_ii_relationships
2266 WHERE object_id IN (SELECT csi_item_instance_id
2267 FROM ahl_unit_config_headers
2268 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
2269 START WITH subject_id = p_instance_id
2270 AND relationship_type_code = 'COMPONENT-OF'
2271 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2272 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2273 CONNECT BY subject_id = PRIOR object_id
2274 AND relationship_type_code = 'COMPONENT-OF'
2275 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2276 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2277
2278 CURSOR get_uc_header_id(c_instance_id NUMBER) IS
2279 SELECT unit_config_header_id
2280 FROM ahl_unit_config_headers
2281 WHERE csi_item_instance_id = c_instance_id
2282 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2283
2284 BEGIN
2285 OPEN get_parent_uc;
2286 FETCH get_parent_uc INTO l_instance_id;
2287 IF get_parent_uc%NOTFOUND THEN
2288 x_parent_uc_header_id := NULL;
2289 x_parent_uc_instance_id := NULL;
2290 ELSE
2291 OPEN get_uc_header_id(l_instance_id);
2292 FETCH get_uc_header_id INTO l_uc_header_id;
2293 IF get_uc_header_id%NOTFOUND THEN
2294 x_parent_uc_header_id := NULL;
2295 x_parent_uc_instance_id := l_instance_id;
2296 ELSE
2297 x_parent_uc_header_id := l_uc_header_id;
2298 x_parent_uc_instance_id := l_instance_id;
2299 END IF;
2300 CLOSE get_uc_header_id;
2301 END IF;
2302 CLOSE get_parent_uc;
2303 END;
2304
2305 ------------------------------------------------------
2306 -- Function to Map the instance id to a relationship id
2307 ------------------------------------------------------
2308 FUNCTION Map_Instance_to_RelID(p_csi_ii_id IN NUMBER)
2309 RETURN NUMBER
2310 IS
2311 --
2312 CURSOR get_top_rel_id (csi_id IN NUMBER)
2313 IS
2314 SELECT rel.relationship_id
2315 FROM AHL_MC_RELATIONSHIPS rel,
2316 AHL_UNIT_CONFIG_HEADERS uch
2317 WHERE rel.mc_header_id = uch.master_config_id
2321 CURSOR get_relationship_id (csi_id IN NUMBER)
2318 AND rel.parent_relationship_id IS NULL
2319 AND uch.csi_item_instance_id = csi_id;
2320 --
2322 IS
2323 SELECT TO_NUMBER(position_reference)
2324 FROM csi_ii_relationships
2325 WHERE SUBJECT_ID = csi_id
2326 AND relationship_type_code = 'COMPONENT-OF'
2327 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
2328 --
2329 l_rel_id NUMBER;
2330 --
2331 BEGIN
2332 l_rel_id := null;
2333 OPEN get_relationship_id (p_csi_ii_id);
2334 FETCH get_relationship_id INTO l_rel_id;
2335 IF (get_relationship_id%NOTFOUND) THEN
2336 OPEN get_top_rel_id (p_csi_ii_id);
2337 FETCH get_top_rel_id INTO l_rel_id;
2338 CLOSE get_top_rel_id;
2339 END IF;
2340 CLOSE get_relationship_id;
2341
2342 RETURN l_rel_id;
2343 END Map_Instance_to_RelID;
2344
2345 -- Define procedure get_unit_name --
2346 -- This common utility is used to get the root unit name for a given instance_id
2347 -- The unit name is the highest standalone unit to which the instance belongs.
2348 -- IF the instance happens to be the root unit instance, then return the unit name
2349 -- of itself
2350 FUNCTION get_unit_name(p_instance_id IN NUMBER) RETURN VARCHAR2 IS
2351 l_unit_name VARCHAR2(80);
2352 l_instance_id NUMBER;
2353 CURSOR get_uc_instance_id IS
2354 SELECT object_id
2355 FROM csi_ii_relationships
2356 WHERE object_id IN (SELECT csi_item_instance_id
2357 FROM ahl_unit_config_headers
2358 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
2359 START WITH subject_id = p_instance_id
2360 AND relationship_type_code = 'COMPONENT-OF'
2361 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2362 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2363 CONNECT BY subject_id = PRIOR object_id
2364 AND relationship_type_code = 'COMPONENT-OF'
2365 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2366 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2367
2368 CURSOR get_uc_header_name(c_instance_id NUMBER) IS
2369 SELECT name
2370 FROM ahl_unit_config_headers
2371 WHERE csi_item_instance_id = c_instance_id
2372 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2373
2374 BEGIN
2375 OPEN get_uc_instance_id;
2376 LOOP
2377 FETCH get_uc_instance_id INTO l_instance_id;
2378 EXIT when get_uc_instance_id%NOTFOUND;
2379 END LOOP;
2380 CLOSE get_uc_instance_id;
2381
2382 IF l_instance_id IS NULL THEN
2383 l_instance_id := p_instance_id;
2384 END IF;
2385 OPEN get_uc_header_name(l_instance_id);
2386 FETCH get_uc_header_name INTO l_unit_name;
2387 CLOSE get_uc_header_name;
2388 RETURN l_unit_name;
2389 END;
2390
2391 -- Define procedure get_uc_header_id --
2392 -- This common utility is used to get the root uc_header_id for a given instance_id
2393 -- The uc_header_id is the highest standalone unit to which the instance belongs.
2394 -- IF the instance happens to be the root unit instance, then return the uc_header_id
2395 -- of itself
2396 FUNCTION get_uc_header_id(p_instance_id IN NUMBER) RETURN NUMBER IS
2397 l_uc_header_id VARCHAR2(80);
2398 l_instance_id NUMBER;
2399 CURSOR get_uc_instance_id IS
2400 SELECT object_id
2401 FROM csi_ii_relationships
2402 WHERE object_id IN (SELECT csi_item_instance_id
2403 FROM ahl_unit_config_headers
2404 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
2405 START WITH subject_id = p_instance_id
2406 AND relationship_type_code = 'COMPONENT-OF'
2407 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2408 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2409 CONNECT BY subject_id = PRIOR object_id
2410 AND relationship_type_code = 'COMPONENT-OF'
2411 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2412 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2413
2414 CURSOR get_uc_header_id(c_instance_id NUMBER) IS
2415 SELECT unit_config_header_id
2416 FROM ahl_unit_config_headers
2417 WHERE csi_item_instance_id = c_instance_id
2418 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2419
2420 BEGIN
2421 OPEN get_uc_instance_id;
2422 LOOP
2423 FETCH get_uc_instance_id INTO l_instance_id;
2424 EXIT when get_uc_instance_id%NOTFOUND;
2425 END LOOP;
2426 CLOSE get_uc_instance_id;
2427
2428 IF l_instance_id IS NULL THEN
2429 l_instance_id := p_instance_id;
2430 END IF;
2431 OPEN get_uc_header_id(l_instance_id);
2432 FETCH get_uc_header_id INTO l_uc_header_id;
2433 CLOSE get_uc_header_id;
2434 RETURN l_uc_header_id;
2435 END;
2436
2437 -- Define function get_sub_unit_name --
2438 -- This common utility is used to get the sub unit name for a given instance_id
2439 -- The unit name is the lowerest sub unit to which the instance belongs.
2440 -- IF the instance happens to be the sub unit instance, then return the sub unit name
2441 -- of itself
2442 FUNCTION get_sub_unit_name(p_instance_id IN NUMBER) RETURN VARCHAR2 IS
2443 l_unit_name VARCHAR2(80);
2444 l_instance_id NUMBER;
2445 CURSOR get_uc_instance_id IS
2446 SELECT object_id
2447 FROM csi_ii_relationships
2451 START WITH subject_id = p_instance_id
2448 WHERE object_id IN (SELECT csi_item_instance_id
2449 FROM ahl_unit_config_headers
2450 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
2452 AND relationship_type_code = 'COMPONENT-OF'
2453 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2454 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2455 CONNECT BY subject_id = PRIOR object_id
2456 AND relationship_type_code = 'COMPONENT-OF'
2457 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2458 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2459
2460 CURSOR get_uc_header_name(c_instance_id NUMBER) IS
2461 SELECT name
2462 FROM ahl_unit_config_headers
2463 WHERE csi_item_instance_id = c_instance_id
2464 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2465
2466 BEGIN
2467 OPEN get_uc_header_name(p_instance_id);
2468 FETCH get_uc_header_name INTO l_unit_name;
2469 IF get_uc_header_name%NOTFOUND THEN
2470 CLOSE get_uc_header_name;
2471 OPEN get_uc_instance_id;
2472 FETCH get_uc_instance_id INTO l_instance_id;
2473 IF get_uc_instance_id%FOUND THEN
2474 OPEN get_uc_header_name(l_instance_id);
2475 FETCH get_uc_header_name INTO l_unit_name;
2476 CLOSE get_uc_header_name;
2477 ELSE
2478 l_unit_name := NULL;
2479 END IF;
2480 ELSE
2481 CLOSE get_uc_header_name;
2482 END IF;
2483 RETURN l_unit_name;
2484 END;
2485
2486 -- Define function get_sub_uc_header_id --
2487 -- This common utility is used to get the sub uc_header_id for a given instance_id
2488 -- The uc_header_id is the lowest sub uc_header_id to which the instance_id belongs.
2489 -- IF the instance happens to be the sub unit top instance, then return the sub uc_header_id
2490 -- of itself
2491 FUNCTION get_sub_uc_header_id(p_instance_id IN NUMBER) RETURN VARCHAR2 IS
2492 l_uc_header_id NUMBER;
2493 l_instance_id NUMBER;
2494 CURSOR get_uc_instance_id IS
2495 SELECT object_id
2496 FROM csi_ii_relationships
2497 WHERE object_id IN (SELECT csi_item_instance_id
2498 FROM ahl_unit_config_headers
2499 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
2500 START WITH subject_id = p_instance_id
2501 AND relationship_type_code = 'COMPONENT-OF'
2502 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2503 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2504 CONNECT BY subject_id = PRIOR object_id
2505 AND relationship_type_code = 'COMPONENT-OF'
2506 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2507 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2508
2509 CURSOR get_uc_header_id(c_instance_id NUMBER) IS
2510 SELECT unit_config_header_id
2511 FROM ahl_unit_config_headers
2512 WHERE csi_item_instance_id = c_instance_id
2513 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2514
2515 BEGIN
2516 OPEN get_uc_header_id(p_instance_id);
2517 FETCH get_uc_header_id INTO l_uc_header_id;
2518 IF get_uc_header_id%NOTFOUND THEN
2519 CLOSE get_uc_header_id;
2520 OPEN get_uc_instance_id;
2521 FETCH get_uc_instance_id INTO l_instance_id;
2522 IF get_uc_instance_id%FOUND THEN
2523 OPEN get_uc_header_id(l_instance_id);
2524 FETCH get_uc_header_id INTO l_uc_header_id;
2525 CLOSE get_uc_header_id;
2526 ELSE
2527 l_uc_header_id := NULL;
2528 END IF;
2529 ELSE
2530 CLOSE get_uc_header_id;
2531 END IF;
2532 RETURN l_uc_header_id;
2533 END;
2534
2535 -- Function: This API will return FND_API.G_TRUE if a UC is in Quarantine or Deactivate
2536 -- Quarantine Status
2537 -- ACL :: Added for R12 changes.
2538 FUNCTION IS_UNIT_QUARANTINED(p_unit_header_id IN NUMBER,
2539 p_instance_id IN NUMBER) RETURN VARCHAR2 IS
2540 l_uc_header_id NUMBER;
2541 l_uc_status_code VARCHAR2(30);
2542 l_debug_key VARCHAR2(150) := 'ahl.plsql.AHL_UTIL_UC_PKG.IS_UNIT_QUARANTINED';
2543
2544 BEGIN
2545
2546 -- If unit header id is not passed then derived using the instance id
2547 IF p_unit_header_id IS NULL THEN
2548 l_uc_header_id := get_uc_header_id(p_instance_id);
2549 ELSE
2550 l_uc_header_id := p_unit_header_id;
2551 END IF;
2552
2553 -- if valid uc header id could not be derived return false.
2554 IF l_uc_header_id IS NULL THEN
2555 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2556 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, l_debug_key, 'UC Header Id Derived as NULL. p_unit_header_id :'||p_unit_header_id);
2557 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, l_debug_key, 'UC Header Id Derived as NULL. l_uc_header_id :'||l_uc_header_id );
2558 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, l_debug_key, 'UC Header Id Derived as NULL. p_instance_id :'||p_instance_id);
2559 END IF;
2560 RETURN FND_API.G_FALSE;
2561 ELSE
2562 -- else derive uc status code
2563 l_uc_status_code := GET_UC_STATUS_CODE(l_uc_header_id);
2564 -- if uc status code in quarantine / deactivate quarantine then return true
2565 IF l_uc_status_code in (G_STATUS_QUARANTINE,G_STATUS_D_QUARANTINE) THEN
2566 RETURN FND_API.G_TRUE;
2567 ELSE
2571 END IF;
2568 -- else return false
2569 RETURN FND_API.G_FALSE;
2570 END IF;
2572
2573 END IS_UNIT_QUARANTINED;
2574
2575 --------------------------------------------------------------------------------------------
2576 -- Start of Comments --
2577 --
2578 -- Procedure name : Check_Invalidate_Instance
2579 -- Type : Private
2580 -- Function : Validates the updation of interchange_type_code in an item group
2581 -- against active UCs where the item is installed.
2582 -- Pre-reqs :
2583 --
2584 -- Standard IN Parameters :
2585 -- p_api_version IN NUMBER Required
2586 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
2587 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2588 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2589 -- Standard OUT Parameters :
2590 -- x_return_status OUT VARCHAR2 Required
2591 -- x_msg_count OUT NUMBER Required
2592 -- x_msg_data OUT VARCHAR2 Required
2593 --
2594 -- Check_Invalidate_Instance parameters :
2595 -- p_instance_table IN Instance_Tbl_Type2
2596 -- A table of inv item id, inv org id, item_group_id, item name, item rev and
2597 -- item interchange type
2598 --
2599 -- History:
2600 -- 07-JUN-06 SATHAPLI CREATED
2601 --
2602 -- Version :
2603 -- Initial Version 1.0
2604 --
2605 -- End of Comments.
2606 --------------------------------------------------------------------------------------------
2607
2608 PROCEDURE Check_Invalidate_Instance
2609 (
2610 p_api_version IN NUMBER,
2611 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2612 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2613 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2614 p_instance_tbl IN Instance_Tbl_Type2,
2615 p_operator IN VARCHAR2,
2616 x_return_status OUT NOCOPY VARCHAR2,
2617 x_msg_count OUT NOCOPY NUMBER,
2618 x_msg_data OUT NOCOPY VARCHAR2
2619 ) IS
2620
2621 --Check if the item group, inventory and org combination are valid
2622 CURSOR c_check_item_group_csr(c_item_group_id NUMBER,
2623 c_inventory_item_id NUMBER,
2624 c_inventory_org_id NUMBER ) IS
2625 SELECT item_group_id
2626 FROM AHL_ITEM_ASSOCIATIONS_B
2627 WHERE item_group_id = c_item_group_id AND
2628 inventory_item_id = c_inventory_item_id AND
2629 inventory_org_id = c_inventory_org_id;
2630
2631 -- Get all the positions associated to this item group.
2632 CURSOR c_get_associated_posns_csr(c_item_group_id NUMBER) IS
2633 SELECT relationship_id
2634 FROM AHL_MC_RELATIONSHIPS REL, AHL_MC_HEADERS_B HDR
2635 WHERE HDR.mc_header_id = REL.mc_header_id AND
2636 REL.item_group_id = c_item_group_id AND
2637 HDR.config_status_code NOT IN ('EXPIRED','CLOSED') AND
2638 TRUNC(NVL(REL.active_end_date,SYSDATE+1)) > TRUNC(SYSDATE) AND
2639 TRUNC(NVL(REL.active_start_date,SYSDATE)) <= TRUNC(SYSDATE);
2640
2641 -- Get item instances that match the position and inventory_item_id.
2642 CURSOR c_get_item_instances_csr(c_position_reference VARCHAR2,
2643 c_inventory_item_id NUMBER,
2644 c_inventory_org_id NUMBER) IS
2645 SELECT CSI.instance_id
2646 FROM CSI_II_RELATIONSHIPS RELN, CSI_ITEM_INSTANCES CSI
2647 WHERE RELN.subject_id = CSI.instance_id AND
2648 RELN.relationship_type_code = 'COMPONENT-OF' AND
2649 RELN.position_reference = c_position_reference AND
2650 CSI.inventory_item_id = c_inventory_item_id AND
2651 CSI.inv_master_organization_id = c_inventory_org_id AND
2652 TRUNC(NVL(RELN.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE) AND
2653 TRUNC(NVL(RELN.active_start_date,SYSDATE)) <= TRUNC(SYSDATE);
2654
2655 -- Check top nodes of active unit or sub-unit that match.
2656 CURSOR c_chk_top_node_csr(c_relationship_id NUMBER,
2657 c_inventory_item_id NUMBER,
2658 c_inventory_org_id NUMBER) IS
2659 SELECT UC.name,
2660 UC.unit_config_header_id
2661 FROM AHL_UNIT_CONFIG_HEADERS UC, CSI_ITEM_INSTANCES CSI,
2662 AHL_MC_RELATIONSHIPS MC
2663 WHERE UC.csi_item_instance_id = CSI.instance_id AND
2664 UC.master_config_id = MC.mc_header_id AND
2665 UC.parent_uc_header_id IS NULL AND
2666 MC.parent_relationship_id IS NULL AND
2667 MC.relationship_id = c_relationship_id AND
2668 CSI.inventory_item_id = c_inventory_item_id AND
2669 CSI.inv_master_organization_id = c_inventory_org_id AND
2670 TRUNC(NVL(UC.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE) AND
2671 TRUNC(NVL(UC.active_start_date,SYSDATE)) <= TRUNC(SYSDATE) AND
2672 TRUNC(NVL(CSI.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
2673
2674 -- Get UC header info
2675 CURSOR c_get_uc_header_info_csr(p_csi_item_instance_id NUMBER) IS
2676 SELECT UC.name,
2677 UC.unit_config_header_id
2678 FROM AHL_UNIT_CONFIG_HEADERS UC, CSI_ITEM_INSTANCES CSI
2679 WHERE UC.csi_item_instance_id IN
2680 (SELECT object_id
2681 FROM CSI_II_RELATIONSHIPS
2682 START WITH
2683 subject_id = p_csi_item_instance_id AND
2684 relationship_type_code = 'COMPONENT-OF' AND
2685 TRUNC(NVL(active_start_date,SYSDATE)) <= TRUNC(SYSDATE) AND
2686 TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
2687 CONNECT BY
2688 PRIOR object_id = subject_id AND
2689 relationship_type_code = 'COMPONENT-OF' AND
2690 TRUNC(NVL(active_start_date,SYSDATE)) <= TRUNC(SYSDATE) AND
2691 TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
2692 ) AND
2693 UC.parent_uc_header_id IS NULL AND
2694 UC.csi_item_instance_id = CSI.instance_id AND
2695 TRUNC(NVL(UC.active_start_date,SYSDATE)) <= TRUNC(SYSDATE) AND
2696 TRUNC(NVL(UC.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE) AND
2697 TRUNC(NVL(CSI.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
2698
2699 l_api_version CONSTANT NUMBER := 1.0;
2700 l_api_name CONSTANT VARCHAR2(30) := 'Check_Invalidate_Instance';
2701 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2702
2703 l_instance_tbl Instance_Tbl_Type2 := p_instance_tbl;
2704 l_unitname ahl_unit_config_headers.name%TYPE;
2705 l_unitid ahl_unit_config_headers.unit_config_header_id%TYPE;
2706
2707 l_check_item_group_rec c_check_item_group_csr%ROWTYPE;
2708 l_valid_UC_lst VARCHAR2(2000) := NULL;
2709
2710 BEGIN
2711
2712 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2713 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
2714 END IF;
2715
2716 -- Standard call to check for call compatibility
2717 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
2718 l_api_name, G_PKG_NAME) THEN
2719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2720 END IF;
2721
2722 -- Initialize message list if p_init_msg_list is set to TRUE
2723 IF FND_API.To_Boolean(p_init_msg_list) THEN
2724 FND_MSG_PUB.Initialize;
2725 END IF;
2726
2727 -- Initialize API return status to success
2728 x_return_status := FND_API.G_RET_STS_SUCCESS;
2729
2730 -- Validating the instance table
2731 IF (l_instance_tbl.COUNT >0) THEN
2732 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2733 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Validating for '||
2734 l_instance_tbl.COUNT||' items with flag: '||p_operator);
2735 END IF;
2736
2737 --For all the records in the instance table
2738 FOR i in l_instance_tbl.FIRST..l_instance_tbl.LAST LOOP
2739 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2740 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Details of '||i||
2741 ' table item: IG id '||l_instance_tbl(i).item_group_id||
2742 ' INV id '||l_instance_tbl(i).inventory_item_id||
2743 ' ORG id '||l_instance_tbl(i).inventory_org_id);
2744 END IF;
2745
2746 l_valid_UC_lst := NULL;
2747
2748 OPEN c_check_item_group_csr(l_instance_tbl(i).item_group_id,
2749 l_instance_tbl(i).inventory_item_id,
2750 l_instance_tbl(i).inventory_org_id);
2751 FETCH c_check_item_group_csr INTO l_check_item_group_rec;
2752
2753 --Proceed if item group is found
2754 IF (c_check_item_group_csr%FOUND) THEN
2755 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2756 FND_LOG.string(FND_LOG.level_statement,l_full_name,i||' table item exists');
2757 END IF;
2758
2759 --Get all the positions the item group is associated to
2760 FOR position_rec IN c_get_associated_posns_csr(l_instance_tbl(i).item_group_id)
2761 LOOP
2762 --Check if the item is assigned as a top node
2763 FOR top_node_csr_rec IN c_chk_top_node_csr
2764 (position_rec.relationship_id,
2765 l_instance_tbl(i).inventory_item_id,
2766 l_instance_tbl(i).inventory_org_id)
2767 LOOP
2768 -- append the token with the UC name
2769 l_valid_UC_lst := l_valid_UC_lst || top_node_csr_rec.name || ' - '||
2770 AHL_MC_PATH_POSITION_PVT.get_posref_for_uc(
2771 top_node_csr_rec.unit_config_header_id,position_rec.relationship_id) || ', ';
2772
2773 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2774 FND_LOG.string(FND_LOG.level_statement,l_full_name,i||
2775 ' table item installed at root of '||
2776 top_node_csr_rec.name||' with rel id: '||
2777 position_rec.relationship_id);
2778 END IF;
2779 END LOOP;
2780
2781 --Check if the item is assigned as a component
2782 FOR item_instance_rec IN c_get_item_instances_csr
2783 (to_char(position_rec.relationship_id),
2784 l_instance_tbl(i).inventory_item_id,
2785 l_instance_tbl(i).inventory_org_id)
2786 LOOP
2787 OPEN c_get_uc_header_info_csr(item_instance_rec.instance_id);
2788 FETCH c_get_uc_header_info_csr INTO l_unitname, l_unitid;
2789
2790 IF (c_get_uc_header_info_csr%FOUND) THEN
2791 -- append the token with the UC name
2792 l_valid_UC_lst := l_valid_UC_lst || l_unitname || ' - '||
2793 AHL_MC_PATH_POSITION_PVT.get_posref_for_uc(
2794 l_unitid,position_rec.relationship_id) || ', ';
2795
2796 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2797 FND_LOG.string(FND_LOG.level_statement,l_full_name,i||
2801 END IF;
2798 ' table item installed in '||l_unitname||
2799 ' with rel id: '||position_rec.relationship_id);
2800 END IF;
2802
2803 CLOSE c_get_uc_header_info_csr;
2804 END LOOP; --item_instance_rec
2805 END LOOP; --position_rec
2806
2807 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2808 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Msg token formed: '||l_valid_UC_lst||
2809 ' for Item name: '||l_instance_tbl(i).concatenated_segments||
2810 ' revision: '||l_instance_tbl(i).revision);
2811 END IF;
2812
2813 IF (l_valid_UC_lst IS NOT NULL) THEN
2814 -- setting the return status to error
2815 x_return_status := FND_API.G_RET_STS_ERROR;
2816
2817 -- reducing the UCLIST token to appropriate length
2818 IF (length(l_valid_UC_lst) > 1750) THEN
2819 l_valid_UC_lst := substr(l_valid_UC_lst, 1, 1747);
2820 l_valid_UC_lst := l_valid_UC_lst||'...';
2821 END IF;
2822
2823 -- putting the error msg with the token in the stack
2824 IF (p_operator = 'D') THEN
2825 FND_MESSAGE.Set_Name('AHL','AHL_UC_DEL_INVALID');
2826 ELSIF (p_operator = 'U') THEN
2827 FND_MESSAGE.Set_Name('AHL','AHL_UC_UPD_INVALID');
2828 FND_MESSAGE.Set_Token('INTCHG',l_instance_tbl(i).interchange_type);
2829 END IF;
2830 FND_MESSAGE.Set_Token('UCLIST',l_valid_UC_lst);
2831 FND_MESSAGE.Set_Token('ITNAME',l_instance_tbl(i).concatenated_segments);
2832 FND_MESSAGE.Set_Token('ITREV',l_instance_tbl(i).revision);
2833 FND_MSG_PUB.ADD;
2834 END IF;
2835
2836 END IF; -- c_check_item_group_csr%FOUND
2837 CLOSE c_check_item_group_csr;
2838
2839 END LOOP;
2840 END IF; -- IF count > 0
2841
2842 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2843 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
2844 END IF;
2845
2846 EXCEPTION
2847 WHEN FND_API.G_EXC_ERROR THEN
2848 x_return_status := FND_API.G_RET_STS_ERROR;
2849 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2850 p_data => x_msg_data,
2851 p_encoded => fnd_api.g_false);
2852
2853 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2855 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2856 p_data => x_msg_data,
2857 p_encoded => fnd_api.g_false);
2858
2859 WHEN OTHERS THEN
2860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2861 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
2862 p_procedure_name => 'Check_Invalidate_Instance',
2863 p_error_text => SQLERRM);
2864
2865 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2866 p_data => x_msg_data,
2867 p_encoded => fnd_api.g_false);
2868
2869 END Check_Invalidate_Instance;
2870
2871 --------------------------------------------------------------------------------------------
2872 -- Added by jaramana on March 10, 2008 for fixing the Bug 6723950 (FP of 6720010)
2873 -- This API will validate if the instance can become the new item through part change.
2874 -- p_instance_id can be currently in an IB Tree or UC or it may be a stand alone instance.
2875 -- It may also be the root node of a unit.
2876 -- The return value x_matches_flag will be FND_API.G_TRUE or FND_API.G_FALSE.
2877 PROCEDURE Item_Matches_Instance_Pos(p_inventory_item_id IN NUMBER,
2878 p_item_revision IN VARCHAR2 default NULL,
2879 p_instance_id IN NUMBER,
2880 x_matches_flag OUT NOCOPY VARCHAR2) IS
2881
2882 CURSOR get_uc_header_id_csr IS
2883 SELECT unit_config_header_id, master_config_id
2884 FROM ahl_unit_config_headers
2885 WHERE csi_item_instance_id = p_instance_id
2886 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2887
2888 CURSOR get_root_mc_ig_csr (c_mc_header_id IN NUMBER) IS
2889 SELECT reln.relationship_id, reln.item_group_id
2890 FROM ahl_mc_relationships reln
2891 WHERE reln.mc_header_id = c_mc_header_id
2892 AND nvl(reln.active_start_date, sysdate - 1) <= sysdate
2893 AND nvl(reln.active_end_date, sysdate + 1) > sysdate
2894 AND reln.parent_relationship_id is null;
2895
2896 CURSOR get_pos_reference_csr IS
2897 SELECT position_reference
2898 FROM csi_ii_relationships
2899 WHERE subject_id = p_instance_id
2900 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2901 AND NVL(ACTIVE_START_DATE, sysdate - 1) <= sysdate
2902 AND NVL(ACTIVE_END_DATE, sysdate + 1) > sysdate;
2903
2904 CURSOR get_mc_ig_csr(c_pos_ref IN VARCHAR2) IS
2905 SELECT reln.item_group_id
2906 FROM ahl_mc_relationships reln
2907 WHERE relationship_id = TO_NUMBER(c_pos_ref);
2908
2909 CURSOR item_group_has_item_csr(c_item_group_id IN NUMBER) IS
2910 SELECT 1 from ahl_item_associations_b
2911 WHERE item_group_id = c_item_group_id
2912 AND inventory_item_id = p_inventory_item_id
2913 AND ((revision IS NULL) OR (revision = p_item_revision))
2914 AND interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE');
2915
2916
2917 L_DEBUG_KEY VARCHAR2(150) := 'ahl.plsql.AHL_UTIL_UC_PKG.Item_Matches_Instance_Pos';
2918
2919 l_uc_header_id NUMBER;
2920 l_master_config_id NUMBER;
2921 l_node_ig_id NUMBER;
2922 l_mc_root_rel_id NUMBER;
2923 l_pos_reference csi_ii_relationships.position_reference%TYPE;
2924 l_temp_num NUMBER;
2925
2926 BEGIN
2927 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2928 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2929 L_DEBUG_KEY || '.begin',
2930 'At the start of the procedure. p_inventory_item_id = ' || p_inventory_item_id ||
2931 ', p_item_revision = ' || p_item_revision ||
2932 ', p_instance_id = ' || p_instance_id);
2933 END IF;
2934 x_matches_flag := FND_API.G_TRUE;
2935 IF (p_instance_id IS NOT NULL AND p_inventory_item_id IS NOT NULL) THEN
2936 -- Check if the instance is a unit configuration
2937 OPEN get_uc_header_id_csr;
2938 FETCH get_uc_header_id_csr INTO l_uc_header_id, l_master_config_id;
2939 CLOSE get_uc_header_id_csr;
2940 IF (l_uc_header_id IS NOT NULL) THEN
2941 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2942 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2943 L_DEBUG_KEY,
2944 'Instance is a unit. Unit Id: ' || l_uc_header_id);
2945 END IF;
2946 -- Instance is a unit: Get the item group of the root node of the unit's MC
2947 OPEN get_root_mc_ig_csr(l_master_config_id);
2948 FETCH get_root_mc_ig_csr INTO l_mc_root_rel_id, l_node_ig_id;
2949 CLOSE get_root_mc_ig_csr;
2950 ELSE
2951 -- Instance is not a unit: Check if instance is installed or not
2952 OPEN get_pos_reference_csr;
2953 FETCH get_pos_reference_csr into l_pos_reference;
2954 CLOSE get_pos_reference_csr;
2955 IF (l_pos_reference IS NULL) THEN
2956 -- Instance is either not installed or does not belong to a UC
2957 -- Cannot validate item against item group - just return TRUE
2958 x_matches_flag := FND_API.G_TRUE;
2959 ELSE
2960 -- Get Item group of position reference
2961 OPEN get_mc_ig_csr(l_pos_reference);
2962 FETCH get_mc_ig_csr INTO l_node_ig_id;
2963 CLOSE get_mc_ig_csr;
2964 END IF;
2965 END IF;
2966 END IF;
2967
2968 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2969 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2970 L_DEBUG_KEY,
2971 'Item group id of instance: ' || l_node_ig_id);
2972 END IF;
2973
2974 IF l_node_ig_id IS NOT NULL THEN
2975 -- Check if the Item Group has the passed in item/revision
2976 OPEN item_group_has_item_csr(l_node_ig_id);
2977 FETCH item_group_has_item_csr INTO l_temp_num;
2978 IF(item_group_has_item_csr%FOUND) THEN
2979 x_matches_flag := FND_API.G_TRUE;
2980 ELSE
2981 x_matches_flag := FND_API.G_FALSE;
2982 END IF;
2983 CLOSE item_group_has_item_csr;
2984 END IF;
2985
2986 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2987 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2988 L_DEBUG_KEY || '.end',
2989 'At the end of the procedure. x_matches_flag = ' || x_matches_flag);
2990 END IF;
2991 END Item_Matches_Instance_Pos;
2992
2993 END AHL_UTIL_UC_PKG;
[Home] [Help]