[Home] [Help]
203: /* Script for Unique Index suggestion for Item Number segments in MSIB */
204:
205: /* Get the application installation info. References to Data Dictionary Objects without schema name
206: included in WHERE predicate are not allowed (GSCC Check: file.sql.47). For accessing all_indexes
207: in cursor list_of_unique_indexes and all_ind_columns in the query below we need to pass the schema name*/
208:
209: l_ret_status := fnd_installation.get_app_info ('INV'
210: , l_status
211: , l_industry
270: where ID_FLEX_CODE = 'MSTK'
271: and enabled_flag = 'Y'
272: MINUS
273: select column_name "Column_Name"
274: from all_ind_columns aic
275: where table_name = 'MTL_SYSTEM_ITEMS_B'
276: and aic.index_name =l_index.index_name
277: and index_owner=l_oracle_schema
278: and COLUMN_NAME LIKE 'SEGMENT%')
278: and COLUMN_NAME LIKE 'SEGMENT%')
279: UNION
280: (
281: select column_name "Column_Name"
282: from all_ind_columns aic
283: where table_name = 'MTL_SYSTEM_ITEMS_B'
284: and aic.index_name =l_index.index_name
285: and index_owner=l_oracle_schema
286: and COLUMN_NAME LIKE 'SEGMENT%'
293:
294: If l_seg_count = 1 Then /* Segment Match Found*/
295: Begin
296: Select 1 into l_org_col_exists
297: from all_ind_columns aic
298: where table_name = 'MTL_SYSTEM_ITEMS_B'
299: and aic.index_name = l_index.index_name
300: and index_owner=l_oracle_schema
301: and COLUMN_NAME ='ORGANIZATION_ID';