1 PACKAGE BODY CSD_MIGRATE_FROM_12X_PKG7 AS
2 /* $Header: csdmig7b.pls 120.2 2008/04/25 09:53:32 subhat noship $ */
3
4 /* Procedure Name: csd_isupport_ssearch_mig7 */
5 /* This procedure updates the existing csd_ro_savedsearches table to */
6 /* support the iSupport multiparty enhancement. The party_id for the */
7 /* existing records will be updated to default party_id corresponding to*/
8 /* user_id. */
9 /* @param. None */
10
11 procedure CSD_ISUPPORT_SSEARCH_MIG7 is
12
13 -- collection to hold the user_id, party_id
14 --usr_party_tbl user_party_id_tbl;
15 -- bug#6993441 subhat, use two seperate arrays.
16 l_user_id_tbl user_id_tbl;
17 l_party_id_tbl party_id_tbl;
18
19 -- end bug#6993441 subhat
20
21 begin
22 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
23 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
24 'CSD.PLSQL.CSD_Migrate_From_12X_PKG7.csd_isupport_ssearch_mig7',
25 'Before fetching the party for user_id');
26 end if;
27
28 -- make use of an implicit cursor to get the customer_id's for the user_ids
29 -- in csd_ro_saved_searches table.
30 -- The use of implicit cursor may not be a great idea if the savedsearches table
31 -- has very huge data (eg. more than 10000 rows, which is highly unlikely)
32 -- bug#6993441 subhat
33 begin
34 select user_id,customer_id
35 bulk collect into l_user_id_tbl,l_party_id_tbl
36 from fnd_user
37 where user_id in ( select distinct user_id from
38 csd_ro_savedsearches where party_id is null )
39 and customer_id is not null;
40 -- important: Select bulk collect into will never raise no_data_found
41 -- no_data_found is raised only when select into is done. We need to explicitly
42 -- raise no_data_found if we really want it.
43 if l_user_id_tbl.COUNT <= 0 then
44 -- there is possibly no user_id's which have party_id as null in
45 -- csd_ro_savedsearches table. Literally no need for the update sql to run.
46 -- return control to the sql script.
47 return;
48 end if;
49 end;
50 -- update all the null party_id's
51
52 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
53 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
54 'CSD.PLSQL.CSD_Migrate_From_12X_PKG7.csd_isupport_ssearch_mig7',
55 'Before bulk update of CSD_RO_SAVEDSEARCHES table');
56 end if;
57
58 --forall i in 1 ..usr_party_tbl.COUNT
59 FORALL i IN 1 ..l_user_id_tbl.COUNT
60 update csd_ro_savedsearches set party_id = l_party_id_tbl(i) --usr_party_tbl(i).party_id
61 where user_id = l_user_id_tbl(i) --usr_party_tbl(i).user_id
62 and party_id is null;
63
64 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
65 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
66 'CSD.PLSQL.CSD_Migrate_From_12X_PKG7.csd_isupport_ssearch_mig7',
67 'After bulk update of CSD_RO_SAVEDSEARCHES table');
68 end if;
69
70 commit work;
71
72 end CSD_ISUPPORT_SSEARCH_MIG7;
73
74 end CSD_MIGRATE_FROM_12X_PKG7;
[Home] [Help]