[Home] [Help]
1175:
1176: -- Check primary key column
1177: cnt := 0;
1178: select count(*) into cnt
1179: from fnd_primary_key_columns
1180: where application_id = appl_id
1181: and table_id = tab_id
1182: and column_id = col_id;
1183: if (cnt > 0) then
1288: end RemoveIndex;
1289:
1290: --
1291: -- RemovePrimaryKey (PUBLIC)
1292: -- Remove primary key from FND_PRIMARY_KEYS and FND_PRIMARY_KEY_COLUMNS table.
1293: -- Before deleting primary key, make sure that there is no foreign key
1294: -- pointing to this primary key
1295: --
1296: procedure RemovePrimaryKey(
1360: and primary_key_table_id = tab_id
1361: and primary_key_id = pk_id;
1362:
1363: if (cnt = 0) then
1364: delete from fnd_primary_key_columns
1365: where application_id = appl_id
1366: and table_id = tab_id
1367: and primary_key_id = pk_id;
1368:
2516:
2517: -- Bug3230044 Delete any child records with a negative
2518: -- value for COLUMN_ID.
2519:
2520: delete from FND_PRIMARY_KEY_COLUMNS
2521: where APPLICATION_ID = appl_id
2522: and TABLE_ID = tab_id
2523: and PRIMARY_KEY_ID = pk_id
2524: and COLUMN_ID < 0;
2528: -- and COLUMN_ID values to a negative value in order to
2529: -- prevent unique constraints while processing the
2530: -- PARENT/CHILD entity.
2531:
2532: update FND_PRIMARY_KEY_COLUMNS
2533: set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2534: COLUMN_ID = -1 * COLUMN_ID
2535: where APPLICATION_ID = appl_id
2536: and TABLE_ID = tab_id
2537: and PRIMARY_KEY_ID = pk_id;
2538:
2539: /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2540:
2541: update FND_PRIMARY_KEY_COLUMNS
2542: set PRIMARY_KEY_SEQUENCE = -1000
2543: where APPLICATION_ID = appl_id
2544: and TABLE_ID = tab_id
2545: and PRIMARY_KEY_ID = pk_id
2585: -- and the file entity.
2586:
2587: select max(last_update_date)
2588: into child_db_ludate
2589: from fnd_primary_key_columns
2590: where application_id = appl_id
2591: and table_id = tab_id
2592: and primary_key_id = pk_id
2593: and primary_key_sequence < 0
2596: -- Bug3139883 changed select to also include value if primary_key_sequence =0
2597:
2598: select max(last_update_date)
2599: into child_file_ludate
2600: from fnd_primary_key_columns
2601: where application_id = appl_id
2602: and table_id = tab_id
2603: and primary_key_id = pk_id
2604: and PRIMARY_KEY_SEQUENCE >= 0
2619:
2620: -- If any non-seed owners, set owner to user
2621: select max(-1)
2622: into child_db_luby
2623: from fnd_primary_key_columns
2624: where application_id = appl_id
2625: and table_id = tab_id
2626: and primary_key_id = pk_id
2627: and PRIMARY_KEY_SEQUENCE < 0
2634:
2635: -- If any non-seed owners, set owner to user
2636: select max(-1)
2637: into child_file_luby
2638: from fnd_primary_key_columns
2639: where application_id = appl_id
2640: and table_id = tab_id
2641: and primary_key_id = pk_id
2642: and PRIMARY_KEY_SEQUENCE > 0
2653:
2654: -- The new child entity rows from the data file are kept so
2655: -- delete the existing db child entity rows.
2656:
2657: delete from fnd_primary_key_columns
2658: where application_id = appl_id
2659: and table_id = tab_id
2660: and primary_key_id = pk_id
2661: and PRIMARY_KEY_SEQUENCE < 0
2666: -- The existing db child entity rows are kept so delete the new child
2667: -- entity rows from the data file
2668: -- Bug3139883 - Modified delete to include the value column_sequence = 0
2669:
2670: delete from fnd_primary_key_columns
2671: where application_id = appl_id
2672: and table_id = tab_id
2673: and primary_key_id = pk_id
2674: and PRIMARY_KEY_SEQUENCE >= 0
2677: -- Rename the existing db entity rows back to normal since
2678: -- it was not replaced by the new child entity rows
2679: -- from the data file.
2680:
2681: update FND_PRIMARY_KEY_COLUMNS
2682: set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2683: COLUMN_ID = -1 * COLUMN_ID
2684: where APPLICATION_ID = appl_id
2685: and TABLE_ID = tab_id
2686: and PRIMARY_KEY_ID = pk_id;
2687:
2688: /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2689:
2690: update FND_PRIMARY_KEY_COLUMNS
2691: set PRIMARY_KEY_SEQUENCE = 0
2692: where APPLICATION_ID = appl_id
2693: and TABLE_ID = tab_id
2694: and PRIMARY_KEY_ID = pk_id
2706: -- Rename the existing db entity rows back to normal since
2707: -- it was not replaced by the new child entity rows
2708: -- from the data file.
2709:
2710: update FND_PRIMARY_KEY_COLUMNS
2711: set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2712: COLUMN_ID = -1 * COLUMN_ID
2713: where APPLICATION_ID = appl_id
2714: and TABLE_ID = tab_id
2715: and PRIMARY_KEY_ID = pk_id;
2716:
2717: /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2718:
2719: update FND_PRIMARY_KEY_COLUMNS
2720: set PRIMARY_KEY_SEQUENCE = 0
2721: where APPLICATION_ID = appl_id
2722: and TABLE_ID = tab_id
2723: and PRIMARY_KEY_ID = pk_id
2794: -- primary key columns from the data file to determine which will be
2795: -- updated into the database based on the date and custom factirs.
2796:
2797: begin
2798: insert into FND_PRIMARY_KEY_COLUMNS(
2799: APPLICATION_ID,
2800: TABLE_ID,
2801: PRIMARY_KEY_ID,
2802: COLUMN_ID,
4038:
4039: -- Validate primary key column name
4040: cursor pkcols(aid number, tid number, pid number) is
4041: select c.column_name
4042: from fnd_columns c, fnd_primary_key_columns pc
4043: where pc.application_id = aid
4044: and pc.table_id = tid
4045: and pc.primary_key_id = pid
4046: and pc.column_id = c.column_id