[Home] [Help]
1759: , p_organization_id NUMBER
1760: )
1761: IS
1762: SELECT kfv.CONCATENATED_SEGMENTS, COUNT(bic.COMPONENT_SEQUENCE_ID)
1763: FROM BOM_INVENTORY_COMPONENTS bic
1764: , BOM_BILL_OF_MATERIALS bom
1765: , MTL_SYSTEM_ITEMS_KFV kfv
1766: WHERE bom.ORGANIZATION_ID = p_model_org_id
1767: AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1769: AND kfv.ORGANIZATION_ID = bom.ORGANIZATION_ID
1770: AND NOT EXISTS
1771: (
1772: SELECT 'x'
1773: FROM BOM_INVENTORY_COMPONENTS bic1
1774: , BOM_BILL_OF_MATERIALS bom1
1775: WHERE bom1.ORGANIZATION_ID = p_organization_id
1776: AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
1777: AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1783:
1784: CURSOR l_child_1_cnt (p_org_id NUMBER)
1785: IS
1786: SELECT COUNT(bic.COMPONENT_SEQUENCE_ID)
1787: FROM BOM_INVENTORY_COMPONENTS bic
1788: , BOM_BILL_OF_MATERIALS bom
1789: WHERE bom.ORGANIZATION_ID = p_org_id
1790: AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID;
1791:
1794: )
1795: IS
1796: SELECT msi.CONCATENATED_SEGMENTS, COUNT(bsc.COMPONENT_SEQUENCE_ID)
1797: FROM BOM_SUBSTITUTE_COMPONENTS bsc
1798: , BOM_INVENTORY_COMPONENTS bic
1799: , BOM_BILL_OF_MATERIALS bom
1800: , MTL_SYSTEM_ITEMS_KFV msi
1801: WHERE bom.ORGANIZATION_ID = p_model_org_id
1802: AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
1805: AND bsc.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
1806: AND NOT EXISTS
1807: (
1808: SELECT 'x'
1809: FROM BOM_INVENTORY_COMPONENTS bic1
1810: , BOM_BILL_OF_MATERIALS bom1
1811: , BOM_SUBSTITUTE_COMPONENTS bsc1
1812: WHERE bom1.ORGANIZATION_ID = p_organization_id
1813: AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
1823:
1824: CURSOR l_child_2_cnt (p_org_id NUMBER)
1825: IS
1826: SELECT COUNT(bsc.SUBSTITUTE_COMPONENT_ID)
1827: FROM BOM_INVENTORY_COMPONENTS bic
1828: , BOM_BILL_OF_MATERIALS bom
1829: , BOM_SUBSTITUTE_COMPONENTS bsc
1830: WHERE bom.ORGANIZATION_ID = p_org_id
1831: AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1836: )
1837: IS
1838: SELECT msi.CONCATENATED_SEGMENTS, COUNT(brd.COMPONENT_SEQUENCE_ID)
1839: FROM BOM_REFERENCE_DESIGNATORS brd
1840: , BOM_INVENTORY_COMPONENTS bic
1841: , BOM_BILL_OF_MATERIALS bom
1842: , MTL_SYSTEM_ITEMS_KFV msi
1843: WHERE bom.ORGANIZATION_ID = p_model_org_id
1844: AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
1847: AND brd.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
1848: AND NOT EXISTS
1849: (
1850: SELECT 'x'
1851: FROM BOM_INVENTORY_COMPONENTS bic1
1852: , BOM_BILL_OF_MATERIALS bom1
1853: , BOM_REFERENCE_DESIGNATORS brd1
1854: WHERE bom1.ORGANIZATION_ID = p_organization_id
1855: AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
1865:
1866: CURSOR l_child_3_cnt (p_org_id NUMBER)
1867: IS
1868: SELECT COUNT(brd.COMPONENT_REFERENCE_DESIGNATOR)
1869: FROM BOM_INVENTORY_COMPONENTS bic
1870: , BOM_BILL_OF_MATERIALS bom
1871: , BOM_REFERENCE_DESIGNATORS brd
1872: WHERE bom.ORGANIZATION_ID = p_org_id
1873: AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1878: )
1879: IS
1880: SELECT msi.CONCATENATED_SEGMENTS, COUNT(bco.COMPONENT_SEQUENCE_ID)
1881: FROM BOM_COMPONENT_OPERATIONS bco
1882: , BOM_INVENTORY_COMPONENTS bic
1883: , BOM_BILL_OF_MATERIALS bom
1884: , MTL_SYSTEM_ITEMS_KFV msi
1885: WHERE bom.ORGANIZATION_ID = p_model_org_id
1886: AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
1889: AND bco.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
1890: AND NOT EXISTS
1891: (
1892: SELECT 'x'
1893: FROM BOM_INVENTORY_COMPONENTS bic1
1894: , BOM_BILL_OF_MATERIALS bom1
1895: , BOM_COMPONENT_OPERATIONS bco1
1896: WHERE bom1.ORGANIZATION_ID = p_organization_id
1897: AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
1905:
1906: CURSOR l_child_4_cnt (p_org_id NUMBER)
1907: IS
1908: SELECT COUNT(bco.COMP_OPERATION_SEQ_ID)
1909: FROM BOM_INVENTORY_COMPONENTS bic
1910: , BOM_BILL_OF_MATERIALS bom
1911: , BOM_COMPONENT_OPERATIONS bco
1912: WHERE bom.ORGANIZATION_ID = p_org_id
1913: AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID