[Home] [Help]
1496: SELECT DISTINCT
1497: mscs.Category_Set_Name
1498: FROM MSC_ST_CATEGORY_SETS mscs
1499: WHERE NOT EXISTS ( select 1
1500: from MSC_Category_Sets mcs
1501: where mscs.Category_Set_Name= mcs.Category_Set_Name)
1502: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1503: ORDER BY mscs.Category_Set_Name; -- using ORDER BY to avoid dead lock
1504:
1772: FOR c_rec IN c8 LOOP
1773:
1774: BEGIN
1775:
1776: INSERT INTO MSC_CATEGORY_SETS
1777: ( CATEGORY_SET_ID,
1778: CATEGORY_SET_NAME,
1779: CONTROL_LEVEL,
1780: SR_CATEGORY_SET_ID, -- using ORDER BY to avoid dead lock
1784: LAST_UPDATED_BY,
1785: CREATION_DATE,
1786: CREATED_BY)
1787: VALUES
1788: ( MSC_Category_Sets_S.NEXTVAL,
1789: c_rec.Category_Set_Name,
1790: -1,
1791: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1792: -1,
1787: VALUES
1788: ( MSC_Category_Sets_S.NEXTVAL,
1789: c_rec.Category_Set_Name,
1790: -1,
1791: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1792: -1,
1793: MSC_CL_COLLECTION.v_last_collection_id,
1794: MSC_CL_COLLECTION.v_current_date,
1795: MSC_CL_COLLECTION.v_current_user,
1806:
1807: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1808: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1809: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1810: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1812:
1813: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1814: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1835: mscs.SR_Category_Set_ID,
1836: mscs.SR_INSTANCE_ID,
1837: mcs.Category_Set_ID
1838: FROM MSC_ST_CATEGORY_SETS mscs,
1839: MSC_CATEGORY_SETS mcs
1840: WHERE NOT EXISTS( select 1
1841: from MSC_CATEGORY_SET_ID_LID mcsil
1842: where mscs.SR_Category_Set_ID= mcsil.SR_Category_Set_ID
1843: and mscs.SR_INSTANCE_ID= mcsil.SR_INSTANCE_ID)
1853: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1854: END IF;
1855:
1856: /* in order to set default flag, access the lock on the table first*/
1857: LOCK TABLE MSC_CATEGORY_SETS IN EXCLUSIVE MODE;
1858:
1859: lv_default_category_set_id:= NULL;
1860:
1861: FOR c_rec IN c3 LOOP
1861: FOR c_rec IN c3 LOOP
1862:
1863: BEGIN
1864:
1865: UPDATE MSC_Category_Sets mcs
1866: SET mcs.DESCRIPTION= c_rec.Description,
1867: mcs.CONTROL_LEVEL= c_rec.Control_Level,
1868: mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1869: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1886:
1887: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1888: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1889: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1890: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1891: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1892:
1893: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1894: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1907:
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1909: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1910: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1911: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1912: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1913:
1914: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1915: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1928: END LOOP;
1929:
1930: IF lv_default_category_set_id IS NOT NULL THEN
1931:
1932: UPDATE MSC_CATEGORY_SETS
1933: SET DEFAULT_FLAG= 2
1934: WHERE DEFAULT_FLAG= 1
1935: AND CATEGORY_SET_ID <> lv_default_category_set_id;
1936: