[Home] [Help]
1493: SELECT DISTINCT
1494: mscs.Category_Set_Name
1495: FROM MSC_ST_CATEGORY_SETS mscs
1496: WHERE NOT EXISTS ( select 1
1497: from MSC_Category_Sets mcs
1498: where mscs.Category_Set_Name= mcs.Category_Set_Name)
1499: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1500: ORDER BY mscs.Category_Set_Name; -- using ORDER BY to avoid dead lock
1501:
1768: FOR c_rec IN c8 LOOP
1769:
1770: BEGIN
1771:
1772: INSERT INTO MSC_CATEGORY_SETS
1773: ( CATEGORY_SET_ID,
1774: CATEGORY_SET_NAME,
1775: CONTROL_LEVEL,
1776: SR_CATEGORY_SET_ID, -- using ORDER BY to avoid dead lock
1780: LAST_UPDATED_BY,
1781: CREATION_DATE,
1782: CREATED_BY)
1783: VALUES
1784: ( MSC_Category_Sets_S.NEXTVAL,
1785: c_rec.Category_Set_Name,
1786: -1,
1787: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1788: -1,
1783: VALUES
1784: ( MSC_Category_Sets_S.NEXTVAL,
1785: c_rec.Category_Set_Name,
1786: -1,
1787: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1788: -1,
1789: MSC_CL_COLLECTION.v_last_collection_id,
1790: MSC_CL_COLLECTION.v_current_date,
1791: MSC_CL_COLLECTION.v_current_user,
1802:
1803: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1804: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1805: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1806: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1807: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1808:
1809: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1810: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1831: mscs.SR_Category_Set_ID,
1832: mscs.SR_INSTANCE_ID,
1833: mcs.Category_Set_ID
1834: FROM MSC_ST_CATEGORY_SETS mscs,
1835: MSC_CATEGORY_SETS mcs
1836: WHERE NOT EXISTS( select 1
1837: from MSC_CATEGORY_SET_ID_LID mcsil
1838: where mscs.SR_Category_Set_ID= mcsil.SR_Category_Set_ID
1839: and mscs.SR_INSTANCE_ID= mcsil.SR_INSTANCE_ID)
1849: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1850: END IF;
1851:
1852: /* in order to set default flag, access the lock on the table first*/
1853: LOCK TABLE MSC_CATEGORY_SETS IN EXCLUSIVE MODE;
1854:
1855: lv_default_category_set_id:= NULL;
1856:
1857: FOR c_rec IN c3 LOOP
1857: FOR c_rec IN c3 LOOP
1858:
1859: BEGIN
1860:
1861: UPDATE MSC_Category_Sets mcs
1862: SET mcs.DESCRIPTION= c_rec.Description,
1863: mcs.CONTROL_LEVEL= c_rec.Control_Level,
1864: mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1865: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1882:
1883: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1884: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1885: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1886: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1887: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1888:
1889: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1890: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1903:
1904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1905: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1906: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1907: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1909:
1910: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1911: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1924: END LOOP;
1925:
1926: IF lv_default_category_set_id IS NOT NULL THEN
1927:
1928: UPDATE MSC_CATEGORY_SETS
1929: SET DEFAULT_FLAG= 2
1930: WHERE DEFAULT_FLAG= 1
1931: AND CATEGORY_SET_ID <> lv_default_category_set_id;
1932: