[Home] [Help]
1187:
1188: -- Check foreign key column
1189: cnt := 0;
1190: select count(*) into cnt
1191: from fnd_foreign_key_columns
1192: where application_id = appl_id
1193: and table_id = tab_id
1194: and column_id = col_id;
1195: if (cnt > 0) then
1366: end RemovePrimaryKey;
1367:
1368: --
1369: -- RemoveForeignKey (PUBLIC)
1370: -- Remove foreign key from FND_FOREIGN_KEYS and FND_FOREIGN_KEY_COLUMNS table.
1371: --
1372: procedure RemoveForeignKey(
1373: x_application_short_name in varchar2,
1374: x_table_name in varchar2,
1419: return;
1420: end;
1421:
1422: -- Nothing pointing to foreign key so is safe to delete
1423: delete from fnd_foreign_key_columns
1424: where application_id = appl_id
1425: and table_id = tab_id
1426: and foreign_key_id = fk_id;
1427:
2989: if (P_NZDT_MODE ='Y')
2990: then
2991: begin
2992: AD_ZD_SEED.PREPARE ('FND_FOREIGN_KEYS');
2993: AD_ZD_SEED.PREPARE ('FND_FOREIGN_KEY_COLUMNS');
2994: end;
2995: end if;
2996: select A.APPLICATION_ID
2997: into appl_id
3064:
3065: -- Bug3230044 Delete any child records with a negative
3066: -- value for COLUMN_ID.
3067:
3068: delete from FND_FOREIGN_KEY_COLUMNS
3069: where APPLICATION_ID = appl_id
3070: and TABLE_ID = tab_id
3071: and FOREIGN_KEY_ID = fk_id
3072: and COLUMN_ID < 0;
3076: -- and COLUMN_ID values to a negative value in order to
3077: -- prevent unique constraints while processing the
3078: -- PARENT/CHILD entity.
3079:
3080: update FND_FOREIGN_KEY_COLUMNS
3081: set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3082: COLUMN_ID = -1 * COLUMN_ID
3083: where APPLICATION_ID = appl_id
3084: and TABLE_ID = tab_id
3085: and FOREIGN_KEY_ID = fk_id;
3086:
3087: /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3088:
3089: update FND_FOREIGN_KEY_COLUMNS
3090: set FOREIGN_KEY_SEQUENCE = -1000
3091: where APPLICATION_ID = appl_id
3092: and TABLE_ID = tab_id
3093: and FOREIGN_KEY_ID = fk_id
3128: -- and the file entity.
3129:
3130: select max(last_update_date)
3131: into child_db_ludate
3132: from fnd_foreign_key_columns
3133: where application_id = appl_id
3134: and table_id = tab_id
3135: and foreign_key_id = fk_id
3136: and foreign_key_sequence < 0
3139: -- Bug3139883 changed select to also include value if foreign_key_sequence =0
3140:
3141: select max(last_update_date)
3142: into child_file_ludate
3143: from fnd_foreign_key_columns
3144: where application_id = appl_id
3145: and table_id = tab_id
3146: and foreign_key_id = fk_id
3147: and foreign_key_sequence >= 0
3162:
3163: -- If any non-seed owners, set owner to user
3164: select max(-1)
3165: into child_db_luby
3166: from fnd_foreign_key_columns
3167: where application_id = appl_id
3168: and table_id = tab_id
3169: and foreign_key_id = fk_id
3170: and foreign_key_sequence < 0
3177:
3178: -- If any non-seed owners, set owner to user
3179: select max(-1)
3180: into child_file_luby
3181: from fnd_foreign_key_columns
3182: where application_id = appl_id
3183: and table_id = tab_id
3184: and foreign_key_id = fk_id
3185: and foreign_key_sequence > 0
3196:
3197: -- The new child entity rows from the data file are kept so
3198: -- delete the existing db child entity rows.
3199:
3200: delete from fnd_foreign_key_columns
3201: where application_id = appl_id
3202: and table_id = tab_id
3203: and foreign_key_id = fk_id
3204: and foreign_key_sequence < 0
3209: -- The existing db child entity rows are kept so delete the new child
3210: -- entity rows from the data file
3211: -- Bug3139883 - Modified delete to include the value column_sequence = 0
3212:
3213: delete from fnd_foreign_key_columns
3214: where application_id = appl_id
3215: and table_id = tab_id
3216: and foreign_key_id = fk_id
3217: and foreign_key_sequence >= 0
3220: -- Rename the existing db entity rows back to normal since
3221: -- it was not replaced by the new child entity rows
3222: -- from the data file.
3223:
3224: update FND_FOREIGN_KEY_COLUMNS
3225: set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3226: COLUMN_ID = -1 * COLUMN_ID
3227: where APPLICATION_ID = appl_id
3228: and TABLE_ID = tab_id
3229: and FOREIGN_KEY_ID = fk_id;
3230:
3231: /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3232:
3233: update FND_FOREIGN_KEY_COLUMNS
3234: set FOREIGN_KEY_SEQUENCE = 0
3235: where APPLICATION_ID = appl_id
3236: and TABLE_ID = tab_id
3237: and FOREIGN_KEY_ID = fk_id
3249: -- Rename the existing db entity rows back to normal since
3250: -- it was not replaced by the new child entity rows
3251: -- from the data file.
3252:
3253: update FND_FOREIGN_KEY_COLUMNS
3254: set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3255: COLUMN_ID = -1 * COLUMN_ID
3256: where APPLICATION_ID = appl_id
3257: and TABLE_ID = tab_id
3258: and FOREIGN_KEY_ID = fk_id;
3259:
3260: /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3261:
3262: update FND_FOREIGN_KEY_COLUMNS
3263: set FOREIGN_KEY_SEQUENCE = 0
3264: where APPLICATION_ID = appl_id
3265: and TABLE_ID = tab_id
3266: and FOREIGN_KEY_ID = fk_id
3298: begin
3299: if (P_NZDT_MODE ='Y')
3300: then
3301: begin
3302: AD_ZD_SEED.PREPARE ('FND_FOREIGN_KEY_COLUMNS');
3303: end;
3304: end if;
3305: -- No need to validate/check Application, Table or Foreign Key.
3306: -- Within the same entity.
3343: -- updated into the database based on the date and custom factirs.
3344:
3345:
3346: begin
3347: insert into FND_FOREIGN_KEY_COLUMNS(
3348: APPLICATION_ID,
3349: TABLE_ID,
3350: FOREIGN_KEY_ID,
3351: COLUMN_ID,