[Home] [Help]
1170:
1171: -- Check primary key column
1172: cnt := 0;
1173: select count(*) into cnt
1174: from fnd_primary_key_columns
1175: where application_id = appl_id
1176: and table_id = tab_id
1177: and column_id = col_id;
1178: if (cnt > 0) then
1277: end RemoveIndex;
1278:
1279: --
1280: -- RemovePrimaryKey (PUBLIC)
1281: -- Remove primary key from FND_PRIMARY_KEYS and FND_PRIMARY_KEY_COLUMNS table.
1282: -- Before deleting primary key, make sure that there is no foreign key
1283: -- pointing to this primary key
1284: --
1285: procedure RemovePrimaryKey(
1343: and primary_key_table_id = tab_id
1344: and primary_key_id = pk_id;
1345:
1346: if (cnt = 0) then
1347: delete from fnd_primary_key_columns
1348: where application_id = appl_id
1349: and table_id = tab_id
1350: and primary_key_id = pk_id;
1351:
2535:
2536: if (x_primary_key_type = 'D' and
2537: MultipleDeveloperKeys(appl_id, tab_id, x_primary_key_name)) then
2538: if(x_overwrite_PK = 'Y') then
2539: delete from FND_PRIMARY_KEY_COLUMNS
2540: where APPLICATION_ID = appl_id
2541: and TABLE_ID = tab_id
2542: and PRIMARY_KEY_ID IN (select PRIMARY_KEY_ID from FND_PRIMARY_KEYS
2543: where APPLICATION_ID = appl_id
2596:
2597: -- Bug3230044 Delete any child records with a negative
2598: -- value for COLUMN_ID.
2599:
2600: delete 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 COLUMN_ID < 0;
2608: -- and COLUMN_ID values to a negative value in order to
2609: -- prevent unique constraints while processing the
2610: -- PARENT/CHILD entity.
2611:
2612: update FND_PRIMARY_KEY_COLUMNS
2613: set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2614: COLUMN_ID = -1 * COLUMN_ID
2615: where APPLICATION_ID = appl_id
2616: and TABLE_ID = tab_id
2617: and PRIMARY_KEY_ID = pk_id;
2618:
2619: /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2620:
2621: update FND_PRIMARY_KEY_COLUMNS
2622: set PRIMARY_KEY_SEQUENCE = -1000
2623: where APPLICATION_ID = appl_id
2624: and TABLE_ID = tab_id
2625: and PRIMARY_KEY_ID = pk_id
2665: -- and the file entity.
2666:
2667: select max(last_update_date)
2668: into child_db_ludate
2669: from fnd_primary_key_columns
2670: where application_id = appl_id
2671: and table_id = tab_id
2672: and primary_key_id = pk_id
2673: and primary_key_sequence < 0
2676: -- Bug3139883 changed select to also include value if primary_key_sequence =0
2677:
2678: select max(last_update_date)
2679: into child_file_ludate
2680: from fnd_primary_key_columns
2681: where application_id = appl_id
2682: and table_id = tab_id
2683: and primary_key_id = pk_id
2684: and PRIMARY_KEY_SEQUENCE >= 0
2699:
2700: -- If any non-seed owners, set owner to user
2701: select max(-1)
2702: into child_db_luby
2703: from fnd_primary_key_columns
2704: where application_id = appl_id
2705: and table_id = tab_id
2706: and primary_key_id = pk_id
2707: and PRIMARY_KEY_SEQUENCE < 0
2714:
2715: -- If any non-seed owners, set owner to user
2716: select max(-1)
2717: into child_file_luby
2718: from fnd_primary_key_columns
2719: where application_id = appl_id
2720: and table_id = tab_id
2721: and primary_key_id = pk_id
2722: and PRIMARY_KEY_SEQUENCE > 0
2733:
2734: -- The new child entity rows from the data file are kept so
2735: -- delete the existing db child entity rows.
2736:
2737: delete from fnd_primary_key_columns
2738: where application_id = appl_id
2739: and table_id = tab_id
2740: and primary_key_id = pk_id
2741: and PRIMARY_KEY_SEQUENCE < 0
2746: -- The existing db child entity rows are kept so delete the new child
2747: -- entity rows from the data file
2748: -- Bug3139883 - Modified delete to include the value column_sequence = 0
2749:
2750: delete from fnd_primary_key_columns
2751: where application_id = appl_id
2752: and table_id = tab_id
2753: and primary_key_id = pk_id
2754: and PRIMARY_KEY_SEQUENCE >= 0
2757: -- Rename the existing db entity rows back to normal since
2758: -- it was not replaced by the new child entity rows
2759: -- from the data file.
2760:
2761: update FND_PRIMARY_KEY_COLUMNS
2762: set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2763: COLUMN_ID = -1 * COLUMN_ID
2764: where APPLICATION_ID = appl_id
2765: and TABLE_ID = tab_id
2766: and PRIMARY_KEY_ID = pk_id;
2767:
2768: /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2769:
2770: update FND_PRIMARY_KEY_COLUMNS
2771: set PRIMARY_KEY_SEQUENCE = 0
2772: where APPLICATION_ID = appl_id
2773: and TABLE_ID = tab_id
2774: and PRIMARY_KEY_ID = pk_id
2786: -- Rename the existing db entity rows back to normal since
2787: -- it was not replaced by the new child entity rows
2788: -- from the data file.
2789:
2790: update FND_PRIMARY_KEY_COLUMNS
2791: set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2792: COLUMN_ID = -1 * COLUMN_ID
2793: where APPLICATION_ID = appl_id
2794: and TABLE_ID = tab_id
2795: and PRIMARY_KEY_ID = pk_id;
2796:
2797: /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2798:
2799: update FND_PRIMARY_KEY_COLUMNS
2800: set PRIMARY_KEY_SEQUENCE = 0
2801: where APPLICATION_ID = appl_id
2802: and TABLE_ID = tab_id
2803: and PRIMARY_KEY_ID = pk_id
2873: -- primary key columns from the data file to determine which will be
2874: -- updated into the database based on the date and custom factirs.
2875:
2876: begin
2877: insert into FND_PRIMARY_KEY_COLUMNS(
2878: APPLICATION_ID,
2879: TABLE_ID,
2880: PRIMARY_KEY_ID,
2881: COLUMN_ID,