[Home] [Help]
1192:
1193: -- Check foreign key column
1194: cnt := 0;
1195: select count(*) into cnt
1196: from fnd_foreign_key_columns
1197: where application_id = appl_id
1198: and table_id = tab_id
1199: and column_id = col_id;
1200: if (cnt > 0) then
1383: end RemovePrimaryKey;
1384:
1385: --
1386: -- RemoveForeignKey (PUBLIC)
1387: -- Remove foreign key from FND_FOREIGN_KEYS and FND_FOREIGN_KEY_COLUMNS table.
1388: --
1389: procedure RemoveForeignKey(
1390: x_application_short_name in varchar2,
1391: x_table_name in varchar2,
1442: return;
1443: end;
1444:
1445: -- Nothing pointing to foreign key so is safe to delete
1446: delete from fnd_foreign_key_columns
1447: where application_id = appl_id
1448: and table_id = tab_id
1449: and foreign_key_id = fk_id;
1450:
2947:
2948: -- Bug3230044 Delete any child records with a negative
2949: -- value for COLUMN_ID.
2950:
2951: delete from FND_FOREIGN_KEY_COLUMNS
2952: where APPLICATION_ID = appl_id
2953: and TABLE_ID = tab_id
2954: and FOREIGN_KEY_ID = fk_id
2955: and COLUMN_ID < 0;
2959: -- and COLUMN_ID values to a negative value in order to
2960: -- prevent unique constraints while processing the
2961: -- PARENT/CHILD entity.
2962:
2963: update FND_FOREIGN_KEY_COLUMNS
2964: set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
2965: COLUMN_ID = -1 * COLUMN_ID
2966: where APPLICATION_ID = appl_id
2967: and TABLE_ID = tab_id
2968: and FOREIGN_KEY_ID = fk_id;
2969:
2970: /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
2971:
2972: update FND_FOREIGN_KEY_COLUMNS
2973: set FOREIGN_KEY_SEQUENCE = -1000
2974: where APPLICATION_ID = appl_id
2975: and TABLE_ID = tab_id
2976: and FOREIGN_KEY_ID = fk_id
3011: -- and the file entity.
3012:
3013: select max(last_update_date)
3014: into child_db_ludate
3015: from fnd_foreign_key_columns
3016: where application_id = appl_id
3017: and table_id = tab_id
3018: and foreign_key_id = fk_id
3019: and foreign_key_sequence < 0
3022: -- Bug3139883 changed select to also include value if foreign_key_sequence =0
3023:
3024: select max(last_update_date)
3025: into child_file_ludate
3026: from fnd_foreign_key_columns
3027: where application_id = appl_id
3028: and table_id = tab_id
3029: and foreign_key_id = fk_id
3030: and foreign_key_sequence >= 0
3045:
3046: -- If any non-seed owners, set owner to user
3047: select max(-1)
3048: into child_db_luby
3049: from fnd_foreign_key_columns
3050: where application_id = appl_id
3051: and table_id = tab_id
3052: and foreign_key_id = fk_id
3053: and foreign_key_sequence < 0
3060:
3061: -- If any non-seed owners, set owner to user
3062: select max(-1)
3063: into child_file_luby
3064: from fnd_foreign_key_columns
3065: where application_id = appl_id
3066: and table_id = tab_id
3067: and foreign_key_id = fk_id
3068: and foreign_key_sequence > 0
3079:
3080: -- The new child entity rows from the data file are kept so
3081: -- delete the existing db child entity rows.
3082:
3083: delete from fnd_foreign_key_columns
3084: where application_id = appl_id
3085: and table_id = tab_id
3086: and foreign_key_id = fk_id
3087: and foreign_key_sequence < 0
3092: -- The existing db child entity rows are kept so delete the new child
3093: -- entity rows from the data file
3094: -- Bug3139883 - Modified delete to include the value column_sequence = 0
3095:
3096: delete from fnd_foreign_key_columns
3097: where application_id = appl_id
3098: and table_id = tab_id
3099: and foreign_key_id = fk_id
3100: and foreign_key_sequence >= 0
3103: -- Rename the existing db entity rows back to normal since
3104: -- it was not replaced by the new child entity rows
3105: -- from the data file.
3106:
3107: update FND_FOREIGN_KEY_COLUMNS
3108: set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3109: COLUMN_ID = -1 * COLUMN_ID
3110: where APPLICATION_ID = appl_id
3111: and TABLE_ID = tab_id
3112: and FOREIGN_KEY_ID = fk_id;
3113:
3114: /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3115:
3116: update FND_FOREIGN_KEY_COLUMNS
3117: set FOREIGN_KEY_SEQUENCE = 0
3118: where APPLICATION_ID = appl_id
3119: and TABLE_ID = tab_id
3120: and FOREIGN_KEY_ID = fk_id
3132: -- Rename the existing db entity rows back to normal since
3133: -- it was not replaced by the new child entity rows
3134: -- from the data file.
3135:
3136: update FND_FOREIGN_KEY_COLUMNS
3137: set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3138: COLUMN_ID = -1 * COLUMN_ID
3139: where APPLICATION_ID = appl_id
3140: and TABLE_ID = tab_id
3141: and FOREIGN_KEY_ID = fk_id;
3142:
3143: /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3144:
3145: update FND_FOREIGN_KEY_COLUMNS
3146: set FOREIGN_KEY_SEQUENCE = 0
3147: where APPLICATION_ID = appl_id
3148: and TABLE_ID = tab_id
3149: and FOREIGN_KEY_ID = fk_id
3221: -- updated into the database based on the date and custom factirs.
3222:
3223:
3224: begin
3225: insert into FND_FOREIGN_KEY_COLUMNS(
3226: APPLICATION_ID,
3227: TABLE_ID,
3228: FOREIGN_KEY_ID,
3229: COLUMN_ID,
3714: and ft.application_id = a.application_id;
3715:
3716: cursor fkcols(fkapplid number, fktabid number, fkid number) is
3717: select fc.column_name
3718: from fnd_columns fc, fnd_foreign_key_columns fcc
3719: where fcc.foreign_key_id = fkid
3720: and fcc.table_id = fktabid
3721: and fcc.application_id = fkapplid
3722: and fcc.column_id = fc.column_id