[Home] [Help]
1405: -- create child orgs category assignment
1406:
1407: For Cat_Assign_Rec in Cat_Assign
1408: Loop
1409: INSERT INTO mtl_item_categories
1410: ( inventory_item_id,
1411: category_set_id,
1412: category_id,
1413: last_update_date,
1434: prg_id, -- program_id
1435: l_sysdate, -- program_update_date
1436: req_id, -- request_id
1437: Cat_Assign_Rec.ORGID
1438: FROM mtl_item_categories s,
1439: mtl_category_sets_b d
1440: WHERE s.inventory_item_id = Cat_Assign_Rec.ITEMID
1441: AND s.category_set_id = d.category_set_id
1442: AND s.organization_id = Cat_Assign_Rec.MORG
1471: OR d.functional_area_id = DECODE( Cat_Assign_Rec.CUSTFLAG, 'Y', 11, 0 )
1472: OR d.functional_area_id = DECODE( Cat_Assign_Rec.INTFLAG, 'Y', 11, 0 )) ))
1473: AND NOT EXISTS
1474: (SELECT 'already_exists'
1475: FROM mtl_item_categories mic
1476: WHERE mic.inventory_item_id = Cat_Assign_Rec.ITEMID
1477: AND mic.organization_id = Cat_Assign_Rec.ORGID
1478: AND mic.category_set_id = s.category_set_id);
1479:
1494: /*
1495: Bug: 5050604 Added distinct
1496: */
1497:
1498: INSERT INTO mtl_item_categories
1499: (
1500: inventory_item_id
1501: ,organization_id
1502: ,category_set_id
1562: OR d.functional_area_id = DECODE( m.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
1563: )
1564: AND NOT EXISTS
1565: (SELECT 'x'
1566: FROM mtl_item_categories mic
1567: WHERE mic.inventory_item_id = m.inventory_item_id
1568: AND mic.organization_id = m.organization_id
1569: AND mic.category_set_id = d.category_set_id );
1570:
1569: AND mic.category_set_id = d.category_set_id );
1570:
1571: --Bug: 5344163 Added this query for upward propogation
1572: -- of functional area Product Reporting
1573: INSERT INTO mtl_item_categories
1574: (
1575: inventory_item_id
1576: ,organization_id
1577: ,category_set_id
1620: OR mdcs.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
1621: )
1622: AND NOT EXISTS
1623: (SELECT 'x'
1624: FROM mtl_item_categories mic
1625: WHERE mic.inventory_item_id = msi.inventory_item_id
1626: AND mic.organization_id = mp.organization_id
1627: AND mic.category_set_id = mdcs.category_set_id )
1628: AND EXISTS
1680: and I.transaction_type = l_transaction_type;
1681:
1682: /*
1683: ** For child records that are NOT orphans,
1684: ** we must insert child category records in mtl_item_categories
1685: ** for each category that its master is in.
1686: ** Default categories are assigned earlier..but for child records we also
1687: ** need to assign the categories that
1688: ** the parent has..and also make sure that we do not assign duplicate values
1696: **NP 06MAY96 Note that IOI does not support categories
1697: ** assignment in 10.4, 10.5, 10.6 anyway.
1698: ** Only default category assignment for mandatory catg set is done by IOI in INVPPROB.pls.
1699: **
1700: ** insert into MTL_ITEM_CATEGORIES
1701: ** ( inventory_item_id,
1702: ** category_set_id,
1703: ** category_id,
1704: ** last_update_date,
1726: ** prg_id,
1727: ** sysdate,
1728: ** msii.organization_id
1729: ** from mtl_system_items_interface msii,
1730: ** mtl_item_categories mic,
1731: ** MTL_PARAMETERS MP
1732: ** where msii.process_flag = 4
1733: ** and msii.transaction_type = 'CREATE'
1734: ** and msii.created_by in (1,2)
1735: ** and mic.organization_id = msii.last_updated_by
1736: ** and mic.inventory_item_id = msii.inventory_item_id
1737: ** and msii.inventory_item_id not in
1738: ** (select mic2.inventory_item_id
1739: ** from mtl_item_categories mic2
1740: ** where mic2.category_set_id = mic.category_set_id
1741: ** and mic2.category_id = category_id
1742: ** and mic2.organization_id = msii.organization_id
1743: ** and mic2.inventory_item_id = msii.inventory_item_id);
1802: request_id,
1803: cr.TRANSACTION_ID,
1804: error_msg,
1805: 'CAT_ID',
1806: 'MTL_ITEM_CATEGORIES_INTERFACE',
1807: 'BOM_OP_VALIDATION_ERR',
1808: err_text);
1809:
1810: To be explored as an option**/