[Home] [Help]
396: end InsertPrimaryKey;
397:
398: --
399: -- InsertForeignKey (PRIVATE))
400: -- Add a new foreign key into FND_FOREIGN_KEYS.
401: -- This is only called after checking
402: -- there is no such foreign key exists in UploadForeignKey().
403: --
404: procedure InsertForeignKey(
421: ) is
422:
423: begin
424:
425: insert into FND_FOREIGN_KEYS(
426: APPLICATION_ID,
427: TABLE_ID,
428: FOREIGN_KEY_ID,
429: FOREIGN_KEY_NAME,
442: CREATED_BY)
443: values (
444: x_application_id,
445: x_table_id,
446: FND_FOREIGN_KEYS_S.NEXTVAL,
447: x_foreign_key_name,
448: x_primary_key_application_id,
449: x_primary_key_table_id,
450: x_primary_key_id,
1354: -- pointing to this primary key
1355:
1356: cnt := 0;
1357: select count(*) into cnt
1358: from fnd_foreign_keys
1359: where primary_key_application_id = appl_id
1360: and primary_key_table_id = tab_id
1361: and primary_key_id = pk_id;
1362:
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,
1427:
1428: begin
1429: select foreign_key_id
1430: into fk_id
1431: from fnd_foreign_keys
1432: where application_id = appl_id
1433: and table_id = tab_id
1434: and foreign_key_name = upper(x_foreign_key_name);
1435: exception
1434: and foreign_key_name = upper(x_foreign_key_name);
1435: exception
1436: when no_data_found then
1437: fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1438: fnd_message.set_token('TABLE', 'FND_FOREIGN_KEYS');
1439: fnd_message.set_token('COLUMN', 'FOREIGN_KEY_NAME');
1440: fnd_message.set_token('VALUE', x_foreign_key_name);
1441: app_exception.raise_exception;
1442: return;
1447: where application_id = appl_id
1448: and table_id = tab_id
1449: and foreign_key_id = fk_id;
1450:
1451: delete from fnd_foreign_keys
1452: where application_id = appl_id
1453: and table_id = tab_id
1454: and foreign_key_id = fk_id;
1455:
1562: and table_id = tab_id;
1563:
1564: cursor fk is
1565: select foreign_key_name
1566: from fnd_foreign_keys
1567: where application_id = appl_id
1568: and table_id = tab_id;
1569:
1570: cursor pk is
2911: begin
2912:
2913: select FOREIGN_KEY_ID
2914: into fk_id
2915: from FND_FOREIGN_KEYS
2916: where APPLICATION_ID = appl_id
2917: and TABLE_ID = tab_id
2918: and FOREIGN_KEY_NAME = x_foreign_key_name;
2919:
2918: and FOREIGN_KEY_NAME = x_foreign_key_name;
2919:
2920: select LAST_UPDATED_BY, LAST_UPDATE_DATE
2921: into db_luby, db_ludate
2922: from FND_FOREIGN_KEYS
2923: where APPLICATION_ID = appl_id
2924: and TABLE_ID = tab_id
2925: and FOREIGN_KEY_NAME = x_foreign_key_name;
2926:
2926:
2927: if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2928: db_ludate, X_CUSTOM_MODE)) then
2929:
2930: update FND_FOREIGN_KEYS set
2931: PRIMARY_KEY_APPLICATION_ID = pk_appl_id,
2932: PRIMARY_KEY_TABLE_ID = pk_tab_id,
2933: PRIMARY_KEY_ID = pk_id,
2934: CASCADE_BEHAVIOR = x_cascade_behavior,
3000: else -- phase_mode = 'END'
3001:
3002: select FOREIGN_KEY_ID
3003: into fk_id
3004: from FND_FOREIGN_KEYS
3005: where APPLICATION_ID = appl_id
3006: and TABLE_ID = tab_id
3007: and FOREIGN_KEY_NAME = x_foreign_key_name;
3008:
3189: select T.TABLE_ID into tab_id from FND_TABLES T
3190: where T.APPLICATION_ID = appl_id
3191: and T.TABLE_NAME = x_table_name;
3192:
3193: select F.FOREIGN_KEY_ID into fk_id from FND_FOREIGN_KEYS F
3194: where F.APPLICATION_ID = appl_id
3195: and F.TABLE_ID = tab_id
3196: and F.FOREIGN_KEY_NAME = x_foreign_key_name;
3197:
3704: cursor fks is
3705: select a.application_id, a.application_short_name, ft.table_name,
3706: ft.table_id, f.foreign_key_id,
3707: f.foreign_key_name, f.condition
3708: from fnd_tables ft, fnd_foreign_keys f, fnd_application a
3709: where f.primary_key_application_id = x_application_id
3710: and f.primary_key_table_id = x_primary_table_id
3711: and f.primary_key_id = x_primary_key_id
3712: and ft.table_id = f.table_id