[Home] [Help]
1489: -- create child orgs category assignment
1490:
1491: For Cat_Assign_Rec in Cat_Assign
1492: Loop
1493: INSERT INTO mtl_item_categories
1494: ( inventory_item_id,
1495: category_set_id,
1496: category_id,
1497: last_update_date,
1518: prg_id, -- program_id
1519: l_sysdate, -- program_update_date
1520: req_id, -- request_id
1521: Cat_Assign_Rec.ORGID
1522: FROM mtl_item_categories s,
1523: mtl_category_sets_b d
1524: WHERE s.inventory_item_id = Cat_Assign_Rec.ITEMID
1525: AND s.category_set_id = d.category_set_id
1526: AND s.organization_id = Cat_Assign_Rec.MORG
1555: OR d.functional_area_id = DECODE( Cat_Assign_Rec.CUSTFLAG, 'Y', 11, 0 )
1556: OR d.functional_area_id = DECODE( Cat_Assign_Rec.INTFLAG, 'Y', 11, 0 )) ))
1557: AND NOT EXISTS
1558: (SELECT 'already_exists'
1559: FROM mtl_item_categories mic
1560: WHERE mic.inventory_item_id = Cat_Assign_Rec.ITEMID
1561: AND mic.organization_id = Cat_Assign_Rec.ORGID
1562: AND mic.category_set_id = s.category_set_id);
1563:
1578: /*
1579: Bug: 5050604 Added distinct
1580: */
1581:
1582: INSERT INTO mtl_item_categories
1583: (
1584: inventory_item_id
1585: ,organization_id
1586: ,category_set_id
1646: OR d.functional_area_id = DECODE( m.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
1647: )
1648: AND NOT EXISTS
1649: (SELECT 'x'
1650: FROM mtl_item_categories mic
1651: WHERE mic.inventory_item_id = m.inventory_item_id
1652: AND mic.organization_id = m.organization_id
1653: AND mic.category_set_id = d.category_set_id );
1654:
1655: --Bug: 5344163 Added this query for upward propogation
1656: -- of functional area Product Reporting
1657: /*If either the CO or IO flags are set to Org control then the Category Set too should be set to
1658: Org level. In that case we do not need this insert statement. Bug 9833451*/
1659: /*INSERT INTO mtl_item_categories
1660: (
1661: inventory_item_id
1662: ,organization_id
1663: ,category_set_id
1706: OR mdcs.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
1707: )
1708: AND NOT EXISTS
1709: (SELECT 'x'
1710: FROM mtl_item_categories mic
1711: WHERE mic.inventory_item_id = msi.inventory_item_id
1712: AND mic.organization_id = mp.organization_id
1713: AND mic.category_set_id = mdcs.category_set_id )
1714: AND EXISTS
1766: and I.transaction_type = l_transaction_type;
1767:
1768: /*
1769: ** For child records that are NOT orphans,
1770: ** we must insert child category records in mtl_item_categories
1771: ** for each category that its master is in.
1772: ** Default categories are assigned earlier..but for child records we also
1773: ** need to assign the categories that
1774: ** the parent has..and also make sure that we do not assign duplicate values
1782: **NP 06MAY96 Note that IOI does not support categories
1783: ** assignment in 10.4, 10.5, 10.6 anyway.
1784: ** Only default category assignment for mandatory catg set is done by IOI in INVPPROB.pls.
1785: **
1786: ** insert into MTL_ITEM_CATEGORIES
1787: ** ( inventory_item_id,
1788: ** category_set_id,
1789: ** category_id,
1790: ** last_update_date,
1812: ** prg_id,
1813: ** sysdate,
1814: ** msii.organization_id
1815: ** from mtl_system_items_interface msii,
1816: ** mtl_item_categories mic,
1817: ** MTL_PARAMETERS MP
1818: ** where msii.process_flag = 4
1819: ** and msii.transaction_type = 'CREATE'
1820: ** and msii.created_by in (1,2)
1821: ** and mic.organization_id = msii.last_updated_by
1822: ** and mic.inventory_item_id = msii.inventory_item_id
1823: ** and msii.inventory_item_id not in
1824: ** (select mic2.inventory_item_id
1825: ** from mtl_item_categories mic2
1826: ** where mic2.category_set_id = mic.category_set_id
1827: ** and mic2.category_id = category_id
1828: ** and mic2.organization_id = msii.organization_id
1829: ** and mic2.inventory_item_id = msii.inventory_item_id);
1888: request_id,
1889: cr.TRANSACTION_ID,
1890: error_msg,
1891: 'CAT_ID',
1892: 'MTL_ITEM_CATEGORIES_INTERFACE',
1893: 'BOM_OP_VALIDATION_ERR',
1894: err_text);
1895:
1896: To be explored as an option**/