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