[Home] [Help]
32: where ciea.instance_id = ii.instance_id
33: AND ciea.attribute_code = 'AHL_MFG_DATE'
34: AND ciea.attribute_level = 'GLOBAL') mfg_date,
35: 'm' manufacturer_id, 'c' country_code
36: FROM csi_item_instances ii
37: WHERE ii.instance_id = c_item_instance_id;
38:
39: --Check whether the given item instance exists
40: CURSOR check_instance_exists(c_item_instance_id NUMBER) IS
38:
39: --Check whether the given item instance exists
40: CURSOR check_instance_exists(c_item_instance_id NUMBER) IS
41: SELECT instance_id
42: FROM csi_item_instances
43: WHERE instance_id = c_item_instance_id
44: AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
45:
46:
46:
47: --Get Inventory Item ID for a given item instance
48: CURSOR get_inventory_item(c_item_instance_id NUMBER) IS
49: SELECT inventory_item_id
50: FROM csi_item_instances
51: WHERE instance_id = c_item_instance_id
52: AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
53:
54: -- Declare Local Function CHECK_SN_INSIDE --
183: --only inventory item defined in MR effectivity definition, just top node
184: /*CURSOR get_top_inst1(c_inventory_item_id NUMBER)
185: IS
186: SELECT instance_id
187: FROM csi_item_instances A
188: WHERE inventory_item_id = c_inventory_item_id
189: AND SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
190: AND NOT EXISTS (SELECT 'X'
191: FROM csi_ii_relationships B
197:
198: CURSOR get_inst1(c_inventory_item_id NUMBER)
199: IS
200: SELECT instance_id
201: FROM csi_item_instances
202: WHERE inventory_item_id = c_inventory_item_id
203: AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(nvl(active_end_date,sysdate+1));
204:
205: --only position in MC defined in MR effectivity definition, just UC top node
247: AND api.position_id= C_RELATIONSHIP_ID
248: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
249: AND trunc(nvl(a.active_end_date,sysdate+1))
250: AND EXISTS (SELECT 'X'
251: FROM csi_item_instances B
252: WHERE B.instance_id = api.csi_item_instance_id
253: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
254: AND inventory_item_id = c_inventory_item_id);*/
255:
262: AND api.position_id=c_relationship_id
263: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
264: and trunc(nvl(a.active_end_date,sysdate+1))
265: AND EXISTS (SELECT 'X'
266: FROM csi_item_instances B
267: WHERE B.instance_id = api.csi_item_instance_id
268: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate))
269: and trunc(nvl(b.active_end_date,sysdate+1))
270: AND inventory_item_id = c_inventory_item_id)
276: AND relationship_type_code = 'COMPONENT-OF'
277: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
278: and trunc(nvl(a.active_end_date,sysdate+1))
279: AND EXISTS (SELECT 'X'
280: FROM csi_item_instances B
281: WHERE B.instance_id = api.csi_item_instance_id
282: AND sysdate between trunc(nvl(b.active_start_date,sysdate))
283: and trunc(nvl(b.active_end_date,sysdate+1))
284: AND inventory_item_id = c_inventory_item_id);
287: IS
288: SELECT a.csi_item_instance_id instance_id
289: FROM ahl_unit_config_headers A
290: WHERE EXISTS (SELECT 'X'
291: FROM csi_item_instances B
292: WHERE B.instance_id = A.csi_item_instance_id
293: AND sysdate between trunc(nvl(b.active_start_date,sysdate))
294: and trunc(nvl(b.active_end_date,sysdate+1))
295: AND B.inventory_item_id = c_inventory_item_id)
303: START WITH D.pc_node_id = c_pc_node_id
304: CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
305: UNION
306: SELECT instance_id
307: FROM csi_item_instances A
308: WHERE A.inventory_item_id = c_inventory_item_id
309: AND sysdate between trunc(nvl(a.active_start_date,sysdate))
310: and trunc(nvl(a.active_end_date,sysdate+1))
311: AND NOT EXISTS (SELECT 'X'
327: /*CURSOR get_inst4(c_inventory_item_id NUMBER, c_pc_node_id NUMBER) IS
328: SELECT a.csi_item_instance_id instance_id
329: FROM ahl_unit_config_headers A
330: WHERE EXISTS (SELECT 'X'
331: FROM csi_item_instances B
332: WHERE B.instance_id = A.csi_item_instance_id
333: AND sysdate between trunc(nvl(b.active_start_date,sysdate))
334: and trunc(nvl(b.active_end_date,sysdate+1))
335: AND B.inventory_item_id = c_inventory_item_id)
343: START WITH D.pc_node_id = c_pc_node_id
344: CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
345: UNION
346: SELECT instance_id
347: FROM csi_item_instances A
348: WHERE A.inventory_item_id = c_inventory_item_id
349: AND sysdate between trunc(nvl(a.active_start_date,sysdate))
350: and trunc(nvl(a.active_end_date,sysdate+1))
351: AND NOT EXISTS (SELECT 'X'
367: FROM ahl_unit_config_headers A,ahl_applicable_instances api
368: WHERE SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
369: AND a.csi_item_instance_id=api.csi_item_instance_id
370: AND EXISTS (SELECT 'X'
371: FROM csi_item_instances B
372: WHERE B.instance_id = Api.csi_item_instance_id
373: AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
374: AND B.inventory_item_id = c_inventory_item_id)
375: AND EXISTS (SELECT 'X'
388: AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
389: AND A.SUBJECT_id IN (SELECT csi_item_instance_id from ahl_applicable_instances
390: where csi_item_instance_id=a.subject_id )
391: AND EXISTS (SELECT 'X'
392: FROM csi_item_instances B
393: WHERE B.instance_id = A.subject_id
394: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
395: AND B.inventory_item_id = c_inventory_item_id)
396: START WITH object_id IN (SELECT csi_item_instance_id
409: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
410: AND a.relationship_type_code = 'COMPONENT-OF'
411: UNION
412: SELECT a.instance_id
413: FROM csi_item_instances A
414: WHERE A.inventory_item_id = c_inventory_item_id
415: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
416: AND EXISTS (SELECT 'X'
417: FROM ahl_pc_associations B
427: FROM csi_ii_relationships A
428: WHERE relationship_type_code = 'COMPONENT-OF'
429: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
430: AND EXISTS (SELECT 'X'
431: FROM csi_item_instances B
432: WHERE B.instance_id = A.subject_id
433: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
434: AND B.inventory_item_id = c_inventory_item_id
435: )
433: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
434: AND B.inventory_item_id = c_inventory_item_id
435: )
436: START WITH object_id IN (SELECT C.instance_id
437: FROM csi_item_instances C
438: WHERE EXISTS (SELECT 'X'
439: FROM ahl_pc_associations D
440: WHERE D.unit_item_id = C.inventory_item_id
441: AND D.association_type_flag = 'I'
472: --and A.master_config_id=api.position_id
473: and api.csi_item_instance_id=a.csi_item_instance_id
474: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
475: AND EXISTS (SELECT 'X'
476: FROM csi_item_instances B
477: WHERE B.instance_id = A.csi_item_instance_id
478: AND EXISTS (SELECT 'X'
479: FROM ahl_pc_associations C
480: WHERE C.unit_item_id = B.inventory_item_id
533: WHERE api.position_id = c_relationship_id
534: and api.csi_item_instance_id= a.csi_item_instance_id
535: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
536: AND EXISTS (SELECT 'X'
537: FROM csi_item_instances B
538: WHERE B.instance_id = api.csi_item_instance_id
539: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
540: AND EXISTS (SELECT 'X'
541: FROM ahl_pc_associations C
553: AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
554: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
555: AND subject_id in (select csi_item_instance_id from ahl_applicable_instances)
556: START WITH object_id IN (SELECT ax.instance_id
557: FROM csi_item_instances Ax
558: WHERE SYSDATE between trunc(nvl(ax.active_start_date,sysdate)) and trunc(nvl(ax.active_end_date,sysdate+1))
559: AND instance_id=a.subject_id
560: AND EXISTS (SELECT 'X'
561: FROM ahl_pc_associations B
581: AND api.csi_item_instance_id=A.csi_item_instance_id
582: --and A.master_config_id=api.position_id
583: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
584: AND EXISTS (SELECT 'X'
585: FROM csi_item_instances B
586: WHERE B.instance_id = Api.csi_item_instance_id
587: AND B.inventory_item_id = c_inventory_item_id)
588: AND EXISTS (SELECT 'X'
589: FROM ahl_pc_associations C
601: AND A.csi_item_instance_id=API.csi_item_instance_id
602: --and A.master_config_id=api.position_id
603: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
604: AND EXISTS (SELECT 'X'
605: FROM csi_item_instances B
606: WHERE B.instance_id = A.csi_item_instance_id
607: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
608: AND B.inventory_item_id = c_inventory_item_id
609: AND EXISTS (SELECT 'X'
623: AND api.csi_item_instance_id=A.csi_item_instance_id
624: -- AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
625: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
626: AND EXISTS (SELECT 'X'
627: FROM csi_item_instances B
628: WHERE B.instance_id = api.csi_item_instance_id
629: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
630: AND B.inventory_item_id = c_inventory_item_id)
631: AND EXISTS (SELECT 'X'
643: WHERE relationship_type_code = 'COMPONENT-OF'
644: AND a.relationship_id in (Select position_id from AHL_APPLICABLE_INSTANCES)
645: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
646: AND EXISTS (SELECT 'X'
647: FROM csi_item_instances B
648: WHERE B.instance_id = A.subject_id
649: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
650: AND B.inventory_item_id = c_inventory_item_id)
651: START WITH object_id IN (SELECT c.csi_item_instance_id
671: WHERE api.position_id = c_relationship_id
672: AND A.csi_item_instance_id=API.csi_item_instance_id
673: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
674: AND EXISTS (SELECT 'X'
675: FROM csi_item_instances B
676: WHERE B.instance_id = api.csi_item_instance_id
677: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
678: AND B.inventory_item_id = c_inventory_item_id
679: AND EXISTS (SELECT 'X'
691: WHERE relationship_type_code = 'COMPONENT-OF'
692: AND a.subject_id in (Select csi_item_instance_id from AHL_APPLICABLE_INSTANCES )
693: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
694: AND EXISTS (SELECT 'X'
695: FROM csi_item_instances B
696: WHERE B.instance_id = A.subject_id
697: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
698: AND B.inventory_item_id = c_inventory_item_id)
699: START WITH object_id IN (SELECT instance_id
696: WHERE B.instance_id = A.subject_id
697: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
698: AND B.inventory_item_id = c_inventory_item_id)
699: START WITH object_id IN (SELECT instance_id
700: FROM csi_item_instances C
701: WHERE SYSDATE between trunc(nvl(c.active_start_date,sysdate)) and trunc(nvl(C.active_end_date,sysdate+1))
702: AND EXISTS (SELECT 'X'
703: FROM ahl_pc_associations D
704: WHERE D.unit_item_id = C.inventory_item_id
716: AND a.relationship_type_code = 'COMPONENT-OF';*/
717:
718: CURSOR get_inst4(c_inventory_item_id NUMBER, c_pc_node_id NUMBER) IS
719: SELECT A.instance_id
720: FROM csi_item_instances A
721: WHERE A.inventory_item_id = c_inventory_item_id
722: AND sysdate between trunc(nvl(A.active_start_date,sysdate))
723: and trunc(nvl(A.active_end_date,sysdate+1))
724: AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
724: AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
725:
726: CURSOR get_inst5(c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
727: SELECT A.instance_id
728: FROM csi_item_instances A,ahl_applicable_instances api
729: WHERE A.instance_id = api.csi_item_instance_id
730: AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
731: AND api.position_id=c_relationship_id
732: AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
732: AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
733:
734: CURSOR get_inst6(c_inventory_item_id NUMBER, c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
735: SELECT A.instance_id
736: FROM csi_item_instances A,ahl_applicable_instances api
737: WHERE A.instance_id = api.csi_item_instance_id
738: AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
739: AND api.position_id=c_relationship_id
740: AND A.inventory_item_id = c_inventory_item_id
1280: SELECT pc_node_id --amsriniv
1281: FROM ahl_pc_nodes_b B
1282: --WHERE B.pc_node_id = c_pc_node_id
1283: START WITH B.pc_node_id IN (select pc_node_id
1284: from ahl_pc_associations itm, csi_item_instances csi,
1285: (SELECT object_id
1286: FROM csi_ii_relationships E
1287: START WITH E.subject_id = c_instance_id
1288: AND E.relationship_type_code = 'COMPONENT-OF'
1335: SELECT 'x' --pc_node_id --amsriniv
1336: FROM ahl_pc_nodes_b B
1337: WHERE B.pc_node_id = c_pc_node_id
1338: START WITH B.pc_node_id IN (select pc_node_id
1339: from ahl_pc_associations itm, csi_item_instances csi,ii
1340: where itm.association_type_flag = 'I'
1341: and itm.unit_item_id = csi.inventory_item_id
1342: and csi.instance_id = ii.object_id
1343: UNION ALL
1359: SELECT /*+ CONNECT_BY_FILTERING */ 'x'
1360: FROM ahl_pc_nodes_b B
1361: WHERE B.pc_node_id = c_pc_node_id
1362: START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
1363: from ahl_pc_associations itm, csi_item_instances csi,
1364: (select /*+ INDEX(E) */ object_id
1365: FROM ahl_config_components E
1366: START WITH E.subject_id = c_instance_id
1367: CONNECT BY E.subject_id = PRIOR E.object_id
1461: (select cir2.object_id,
1462: cii2.instance_id subject_id,
1463: nvl(uc.master_config_id, cir2.position_reference) position_reference,
1464: 0 depth
1465: from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
1466: where cii2.instance_id = c_instance_id
1467: and cii2.instance_id = cir2.subject_id(+)
1468: and cii2.instance_id = uc.csi_item_instance_id(+)
1469: and uc.parent_uc_header_id(+) is null
1479: AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))
1480: CONNECT BY object_id = PRIOR subject_id
1481: AND relationship_type_code = 'COMPONENT-OF'
1482: AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))) cir,
1483: csi_item_instances cii
1484: WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1485: AND MR.mr_header_id = A.mr_header_id
1486: AND cir.subject_id = cii.instance_id
1487: AND MR.mr_status_code = 'COMPLETE'
1500: cii2.instance_id subject_id,
1501: --nvl(uc.master_config_id, cir2.position_reference) position_reference,
1502: 0 depth,
1503: cii2.inventory_item_id
1504: from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
1505: where cii2.instance_id = c_instance_id
1506: --and cii2.instance_id = cir2.subject_id(+)
1507: --and cii2.instance_id = uc.csi_item_instance_id(+)
1508: --and uc.parent_uc_header_id(+) is null
1513: a.subject_id,
1514: --to_number(a.position_reference),
1515: level depth,
1516: (select inventory_item_id
1517: from csi_item_instances
1518: where instance_id = a.subject_id) inventory_item_id
1519: FROM csi_ii_relationships a
1520: WHERE c_components_flag = 'Y'
1521: START WITH object_id = c_instance_id
1613: CONNECT BY object_id = PRIOR subject_id
1614: --AND relationship_type_code = 'COMPONENT-OF'
1615: --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1616: --AND trunc(NVL(active_end_date,sysdate+1))
1617: ) cir, csi_item_instances ii,
1618: ahl_mr_effectivities A, ahl_mr_headers_b MR
1619: WHERE --A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1620: ii.instance_id = cir.subject_id
1621: AND A.inventory_item_id = ii.inventory_item_id
1652: FROM ( SELECT a.subject_id
1653: FROM ahl_config_components a
1654: START WITH object_id = c_instance_id
1655: CONNECT BY object_id = PRIOR subject_id
1656: ) cir, csi_item_instances ii,
1657: ahl_mr_effectivities A, ahl_mr_headers_b MR
1658: WHERE A.mr_header_id = c_mr_header_id
1659: AND ii.instance_id = cir.subject_id
1660: AND A.inventory_item_id = ii.inventory_item_id
1688: WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1689: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1690: ii.serial_number --,
1691: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1692: FROM csi_item_instances ii,
1693: ahl_mr_effectivities A, ahl_mr_headers_b MR
1694: WHERE ii.instance_id = c_instance_id
1695: --AND A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1696: AND A.inventory_item_id = ii.inventory_item_id
1723: (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1724: WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1725: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1726: ii.serial_number --,
1727: FROM csi_item_instances ii,
1728: ahl_mr_effectivities A
1729: WHERE ii.instance_id = c_instance_id
1730: AND A.mr_header_id = c_mr_header_id
1731: AND A.inventory_item_id = ii.inventory_item_id
1760: WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1761: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1762: cii.serial_number --,
1763: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1764: FROM ahl_applicable_instances aai,csi_item_instances cii,
1765: ahl_mr_effectivities A
1766: WHERE
1767: --A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1768: --AND
1798: WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1799: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1800: cii.serial_number --,
1801: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1802: FROM ahl_applicable_instances aai,csi_item_instances cii,
1803: ahl_mr_effectivities A
1804: WHERE A.mr_header_id = c_mr_header_id
1805: AND A.relationship_id IS NOT NULL
1806: AND aai.position_id = A.relationship_id
1830: (select cir2.object_id,
1831: cii2.instance_id subject_id,
1832: nvl(uc.master_config_id, cir2.position_reference) position_reference,
1833: 0 depth
1834: from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
1835: where cii2.instance_id = c_instance_id
1836: and cii2.instance_id = cir2.subject_id(+)
1837: and cii2.instance_id = uc.csi_item_instance_id(+)
1838: and uc.parent_uc_header_id(+) is null
1837: and cii2.instance_id = uc.csi_item_instance_id(+)
1838: and uc.parent_uc_header_id(+) is null
1839: and SYSDATE between trunc(nvl(uc.active_start_date,sysdate)) and trunc(NVL(uc.active_end_date,sysdate+1))
1840: ) cir,
1841: csi_item_instances cii
1842: WHERE MR.mr_header_id = A.mr_header_id
1843: AND A.mr_header_id = vis.mr_header_id
1844: AND vis.mr_visit_type_code = c_visit_type_code
1845: AND cir.subject_id = cii.instance_id
1859: cii2.instance_id subject_id,
1860: --nvl(uc.master_config_id, cir2.position_reference) position_reference,
1861: 0 depth,
1862: cii2.inventory_item_id
1863: from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
1864: where cii2.instance_id = c_instance_id
1865: --and cii2.instance_id = cir2.subject_id(+)
1866: --and cii2.instance_id = uc.csi_item_instance_id(+)
1867: --and uc.parent_uc_header_id(+) is null
1954: CONNECT BY object_id = PRIOR subject_id
1955: --AND relationship_type_code = 'COMPONENT-OF'
1956: --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1957: --AND trunc(NVL(active_end_date,sysdate+1))
1958: ) cir, csi_item_instances cii, ahl_mr_visit_types vis,
1959: ahl_mr_effectivities A
1960: WHERE A.mr_header_id = vis.mr_header_id
1961: AND vis.mr_visit_type_code = c_visit_type_code
1962: AND cir.subject_id = cii.instance_id
1988: WHERE cii.instance_id = ciea1.instance_id(+) AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
1989: AND ciea1.attribute_level(+) = 'GLOBAL') mfg_date,
1990: cii.serial_number --,
1991: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1992: FROM csi_item_instances cii,ahl_mr_visit_types vis,
1993: ahl_mr_effectivities A
1994: WHERE A.mr_header_id = vis.mr_header_id
1995: AND vis.mr_visit_type_code = c_visit_type_code
1996: AND cii.instance_id = c_instance_id
2021: WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
2022: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
2023: cii.serial_number --,
2024: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
2025: FROM ahl_applicable_instances aai,csi_item_instances cii,ahl_mr_visit_types vis,
2026: ahl_mr_effectivities A
2027: WHERE A.mr_header_id = vis.mr_header_id
2028: AND vis.mr_visit_type_code = c_visit_type_code
2029: AND A.relationship_id IS NOT NULL
2139: SELECT csi.serial_number serial_number ,
2140: to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
2141: 'm' manufacturer_id ,
2142: 'c' country_code
2143: FROM csi_item_instances csi,
2144: csi_inst_extend_attrib_v ciea1
2145: WHERE csi.instance_id = ciea1.instance_id(+)
2146: AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
2147: AND ciea1.attribute_level(+) = 'GLOBAL'
3124: IS
3125: -- Get top instance for top component
3126: CURSOR get_instance_top (p_item_instance_id IN NUMBER) IS
3127: SELECT A.instance_id
3128: FROM csi_item_instances A
3129: WHERE A.instance_id IN ( SELECT object_id
3130: FROM csi_ii_relationships
3131: START WITH subject_id = p_item_instance_id
3132: AND relationship_type_code = 'COMPONENT-OF'
3148:
3149: -- Get instance if it is a top component
3150: CURSOR get_instance_com (p_item_instance_id IN NUMBER) IS
3151: SELECT instance_id
3152: FROM csi_item_instances
3153: WHERE instance_id = p_item_instance_id
3154: AND sysdate between trunc(nvl(active_start_date,sysdate))
3155: AND trunc(nvl(active_end_date, SYSDATE+1));
3156:
3430: SELECT csi.serial_number serial_number ,
3431: to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
3432: 'm' manufacturer_id ,
3433: 'c' country_code
3434: FROM csi_item_instances csi,
3435: csi_inst_extend_attrib_v ciea1
3436: WHERE csi.instance_id = ciea1.instance_id(+)
3437: AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
3438: AND ciea1.attribute_level(+) = 'GLOBAL'
3526: fetch_inst_ext_attr BOOLEAN;
3527:
3528:
3529: CURSOR get_inst_attributes(c_item_instance_id NUMBER) IS
3530: select CSI.OWNER_PARTY_ID,location_type_code from csi_item_instances CSI
3531: where CSI.instance_id = c_item_instance_id;
3532: l_inst_attr_rec get_inst_attributes%ROWTYPE;
3533:
3534: CURSOR get_inst_ext_attributes(c_item_instance_id NUMBER) IS
3532: l_inst_attr_rec get_inst_attributes%ROWTYPE;
3533:
3534: CURSOR get_inst_ext_attributes(c_item_instance_id NUMBER) IS
3535: SELECT ciea1.attribute_code,ciea1.attribute_value
3536: FROM csi_item_instances csi,
3537: csi_inst_extend_attrib_v ciea1
3538: WHERE csi.instance_id = ciea1.instance_id(+)
3539: AND csi.instance_id = c_item_instance_id;
3540:
3940:
3941: -- validate instance and get its attributes.
3942: CURSOR check_instance_exists_csr(c_item_instance_id NUMBER) IS
3943: SELECT instance_id, inventory_item_id
3944: FROM csi_item_instances
3945: WHERE instance_id = c_item_instance_id
3946: AND nvl(active_start_date,sysdate) <= sysdate and
3947: sysdate < NVL(active_end_date,sysdate+1);
3948:
4351: SELECT /*+ CONNECT_BY_FILTERING */ 'x'
4352: FROM ahl_pc_nodes_b B
4353: WHERE B.pc_node_id = c_pc_node_id
4354: START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
4355: from ahl_pc_associations itm, csi_item_instances csi,
4356: (select /*+ INDEX(E) */ object_id
4357: FROM csi_ii_relationships E
4358: START WITH E.subject_id = c_instance_id
4359: AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
4548: SELECT 'x'
4549: FROM ahl_pc_nodes_b B
4550: WHERE B.pc_node_id = c_pc_node_id
4551: START WITH B.pc_node_id IN (select pc_node_id
4552: from ahl_pc_associations itm, csi_item_instances csi,
4553: (select object_id
4554: FROM ahl_config_components E
4555: START WITH E.subject_id = c_instance_id
4556: CONNECT BY E.subject_id = PRIOR E.object_id
4616: CONNECT BY object_id = PRIOR subject_id
4617: --AND relationship_type_code = 'COMPONENT-OF'
4618: --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
4619: --AND trunc(NVL(active_end_date,sysdate+1))
4620: ) cir, csi_item_instances ii,
4621: ahl_mr_effectivities A
4622: WHERE A.mr_header_id = c_mr_header_id
4623: AND ii.instance_id = cir.subject_id
4624: AND A.inventory_item_id = ii.inventory_item_id
4638: (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
4639: WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
4640: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
4641: ii.serial_number
4642: FROM csi_item_instances ii,
4643: ahl_mr_effectivities A
4644: WHERE ii.instance_id = c_instance_id
4645: AND A.mr_header_id = c_mr_header_id
4646: AND A.inventory_item_id = ii.inventory_item_id
4661: (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
4662: WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
4663: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
4664: cii.serial_number
4665: FROM ahl_applicable_instances aai,csi_item_instances cii,
4666: ahl_mr_effectivities A
4667: WHERE A.mr_header_id = c_mr_header_id
4668: AND A.relationship_id IS NOT NULL
4669: AND aai.position_id = A.relationship_id